sy_jyywsrdj.pas

省级集邮品管理ERP

{*******************************************************}
{                                                       }
{                    集邮业务收入统计表                 }
{                                                       }
{            中软金马公司版权所有。2002.12前            }
{                                                       }
{            编制：中软金马邮资票品项目开发组           }
{                                                       }
{                                                       }
{*******************************************************}
(*
省级集邮票管理 ，地市集邮票

*)
unit SY_JYYWSRDJ;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Grids, DBGrids, StdCtrls, Buttons, ExtCtrls, Db, DBTables, Mask, DBCtrls,
  ToolEdit, CurrEdit, RXDBCtrl, ComCtrls, RXCtrls, FieldComboBox;

type
  TState = (stInsert, stEdit, stDelete, stNone);
  TfrmP_JYYWSRDJ = class(TForm)
    Panel3: TPanel;
    BBt_Add: TBitBtn;
    BBt_Modify: TBitBtn;
    BBt_Delete: TBitBtn;
    BBt_Save: TBitBtn;
    BBt_Cancel: TBitBtn;
    BBt_Quit: TBitBtn;
    Pnl_Edit: TPanel;
    DBGrid1: TDBGrid;
    Qry_YWSR: TQuery;
    Dso_YWSR: TDataSource;
    Label2: TLabel;
    Label4: TLabel;
    Label5: TLabel;
    Label6: TLabel;
    Label7: TLabel;
    DED_BYYWSR: TDBEdit;
    DED_BYMLR: TDBEdit;
    DED_YWSRLJ: TDBEdit;
    DED_MLRLJ: TDBEdit;
    Qry_YWSRDWDM: TStringField;
    Qry_YWSRND: TStringField;
    Qry_YWSRYF: TStringField;
    Qry_YWSRJYPSR: TFloatField;
    Qry_YWSRYPSR: TFloatField;
    Qry_YWSRQTSR: TFloatField;
    Qry_YWSRML: TFloatField;
    Qry_YWSRSPR: TStringField;
    Qry_YWSRZBR: TStringField;
    Qry_YWSRZT: TStringField;
    Qry_YWSRSDATE: TDateTimeField;
    DataSource1: TDataSource;
    Qry_GXDWSJBG: TQuery;
    Label8: TLabel;
    Label9: TLabel;
    Label10: TLabel;
    Label11: TLabel;
    Qry_YWSRdwmc: TStringField;
    Qry_Static: TQuery;
    DBEdit1: TDBEdit;
    CB_DWMC: TFieldComboBox;
    Panel2: TPanel;
    Panel1: TRxLabel;
    Bevel1: TBevel;
    procedure BBt_QuitClick(Sender: TObject);
    procedure BBt_AddClick(Sender: TObject);
    procedure BBt_DeleteClick(Sender: TObject);
    procedure BBt_ModifyClick(Sender: TObject);
    procedure BBt_SaveClick(Sender: TObject);
    procedure BBt_CancelClick(Sender: TObject);
    procedure DED_MonthKeyPress(Sender: TObject; var Key: Char);
    procedure Qry_YWSRYFChange(Sender: TField);
    procedure Qry_YWSRJYPSRGetText(Sender: TField; var Text: string;
      DisplayText: Boolean);
    procedure Qry_YWSRJYPSRSetText(Sender: TField; const Text: string);
    procedure DBGrid1TitleClick(Column: TColumn);
    procedure CB_DWMCChange(Sender: TObject);
    procedure FormKeyPress(Sender: TObject; var Key: Char);
  private
    { Private declarations }
    FChangeStatus: TState;
    FReadOnly: boolean;
    function GetExistField(FQuery: TQuery; FieldName, SnapStr: string): string;
    procedure SetChangeStatus(Value: TState);
    procedure SetReadOnly(Value: boolean);
  protected
    CurYear, CurMonth: Integer;                             //当前年、月
    V_BJ: boolean;
    property ChangeStatus: TState read FChangeStatus write SetChangeStatus;
    property ReadOnly: boolean read FReadOnly write SetReadOnly;
  public
    { Public declarations }
    procedure Init(nYear, nMonth: Integer; IsBJ: Boolean = False);
  end;

var
  frmP_JYYWSRDJ: TfrmP_JYYWSRDJ;

implementation
uses Datas, pub;
{$R *.DFM}

procedure TfrmP_JYYWSRDJ.SetChangeStatus(Value: TState);
begin
  case Value of
    stInsert:
      begin
        with Qry_YWSR do
        begin
          Append;
          FieldByName('ND').AsInteger := CurYear;
          FieldByName('YF').AsInteger := CurMonth;
//        FieldByName('DWDM').AsString:=VG_UnitID;
          FieldByName('ZT').AsString := '0';
        end;
      end;
    stDelete:
      begin
        if not (Qry_YWSR.Eof and Qry_YWSR.Bof) then
          Qry_YWSR.Delete;
        ChangeStatus := stNone;
        Exit;
      end;
    stEdit:
      begin
        Qry_YWSR.Edit;
      end;
  end;
  bbt_Add.Enabled := Value in [stNone];
  bbt_Delete.Enabled := Value in [stNone];
  bbt_Delete.Enabled := bbt_Delete.Enabled and not (Qry_YWSR.Eof and Qry_YWSR.Bof);
  bbt_Modify.Enabled := Value in [stNone];
  bbt_Modify.Enabled := bbt_Modify.Enabled and not (Qry_YWSR.Eof and Qry_YWSR.Bof);
  bbt_Save.Enabled := Value in [stInsert, stEdit];
  bbt_Cancel.Enabled := Value in [stInsert, stEdit];
  ReadOnly := Value in [stNone];
  FChangeStatus := Value;
  CB_DWMC.Visible := FChangeStatus = stInsert;
  DBEdit1.Visible := not CB_DWMC.Visible;
end;

procedure TfrmP_JYYWSRDJ.SetReadOnly(Value: boolean);
var
  I: Integer;
begin
  DBGrid1.Enabled := Value;
  for I := 0 to Pnl_Edit.ControlCount - 1 do
  begin
    if Pnl_Edit.Controls[I] is TDBEdit then
    begin
      if TDBEdit(Pnl_Edit.Controls[I]).Tag = -1 then
        Continue;
      TDBEdit(Pnl_Edit.Controls[I]).ReadOnly := Value;
    end;
    if Pnl_Edit.Controls[I] is TDBLookupComboBox then
      TDBLookupComboBox(Pnl_Edit.Controls[I]).ReadOnly := Value;
  end;
end;

procedure TfrmP_JYYWSRDJ.BBt_QuitClick(Sender: TObject);
begin
  ModalResult := mrOK;
end;

procedure TfrmP_JYYWSRDJ.BBt_AddClick(Sender: TObject);
  function formatRQ(AMonth: string): string;
  begin
    Result := AMonth;
    if Length(AMonth) = 1 then
      Result := '0' + AMonth;
  end;
begin
  if v_BJ then
  begin
    if not Qry_YWSR.IsEmpty then
      if CHQMsgBox('已有数据存在，是否重新生成汇总数据吗？', 2) = IDNO then Exit;
    VG_SQLWhere := ' select a.DWDM from TYS_JYYWSRTJB a, TGS_GXDWSJB b'
      + ' where a.DWDM <> ''' + VG_UnitID + ''' and  a.ND = ''' + IntToStr(CurYear) + ''' and a.YF = ''' + formatRQ(IntToStr(CurMonth)) + ''' order by PXM';
    if not PY_IsExist(VG_SQLWhere) then
    begin
      CHQMsgBox('本月没有下级数据!');
      Exit;
    end;
    with Qry_Static do
    begin
      Close;
      SQL.Text := ' delete from TYS_JYYWSRTJB '
        + ' where DWDM = ''' + VG_UnitID + ''' and  ND = ''' + IntToStr(CurYear) + ''' and YF = ''' + formatRQ(IntToStr(CurMonth)) + ''' ';
      ExecSQL;
      SQL.Text := ' insert  into TYS_JYYWSRTJB( DWDM,                          ND,                           YF,       ZT,            JYPSR,           QTSR,           YPSR,         ML ) '
        + ' (select ''' + VG_UnitID + '''       DWDM,''' + IntToStr(CurYear) + ''' ND,''' + formatRQ(IntToStr(CurMonth)) + ''' YF, ''0'' ZT, SUM(JYPSR) JYPSR, SUM(QTSR) QTSR, SUM(YPSR) YPSR, SUM(ML) ML from TYS_JYYWSRTJB'
        + ' where DWDM <> ''' + VG_UnitID + ''' and  ND = ''' + IntToStr(CurYear) + ''' and YF = ''' + formatRQ(IntToStr(CurMonth)) + ''') ';
      ExecSQL;
    end;
    Qry_YWSR.Close;
    Qry_YWSR.Open;
    ChangeStatus := stNone;
  end
  else
  begin
    CB_DWMC.ClearAll;
    with Qry_Static do
    begin
      Close;
      SQL.Clear;
      SQL.Add(' select DWDM,DWJC DWMC FROM TGS_GXDWSJB');
      SQL.Add(' where ZJXJ=''1'' and JYYW=''1'' and FHDX=''1'' AND DWDM not in (''' + GetExistField(Qry_YWSR, 'DWDM', ''',''') + ''') order by PXM');
      Open;
      if Eof then
      begin
        Close;
        Exit;
      end;
      while not Eof do
      begin
        CB_DWMC.Items.Add(FieldByName('DWMC').AsString);
        CB_DWMC.FieldItems.Add(FieldByName('DWDM').AsString);
        Next;
      end;
    end;
    ChangeStatus := stInsert;
    CB_DWMC.ItemIndex := 0;
    CB_DWMCChange(nil);
  end;
end;

procedure TfrmP_JYYWSRDJ.BBt_DeleteClick(Sender: TObject);
begin
  if CHQMsgBox('确实要删除吗？', 2) = IDNO then Exit;
  ChangeStatus := stDelete;
end;

procedure TfrmP_JYYWSRDJ.BBt_ModifyClick(Sender: TObject);
begin
  ChangeStatus := stEdit;
end;

procedure TfrmP_JYYWSRDJ.BBt_SaveClick(Sender: TObject);
begin
  if (ChangeStatus = stInsert) and (CB_DWMC.Text = '') then
  begin
    CHQMsgBox('请选择单位！');
    CB_DWMC.SetFocus;
    Exit;
  end;
  try
    Qry_YWSR.Post;
  except
    CHQMsgBox('数据保存失败！');
  end;
  Qry_YWSR.Close;
  Qry_YWSR.Open;
  ChangeStatus := stNone;
end;

procedure TfrmP_JYYWSRDJ.BBt_CancelClick(Sender: TObject);
begin
  Qry_YWSR.Cancel;
  ChangeStatus := stNone;
end;

procedure TfrmP_JYYWSRDJ.Init(nYear, nMonth: Integer; IsBJ: Boolean = False);
  function formatRQ(AMon: string): string;
  begin
    Result := AMon;
    if Length(AMon) = 1 then
      Result := '0' + AMon;
  end;

begin
{  with Qry_GXDWSJBG do
  begin
    Open;
    While Not Eof do
    begin
      ComBoBox1.Items.Add ( Fieldby)
    end;
  end;}
  CurYear := nYear;
  CurMonth := nMonth;
  V_BJ := IsBJ;
  with Qry_YWSR do
  begin
    Close;
    SQL.Text := ' select * from TYS_JYYWSRTJB '
      + ' Where ND= ''' + IntToStr(CurYear) + ''' and YF=''' + formatRQ(IntToStr(CurMonth)) + '''';
    if V_BJ then
      SQL.Text := SQL.Text + ' and DWDM = ''' + VG_UnitID + ''''
    else
      SQL.Text := SQL.Text + ' and DWDM <> ''' + VG_UnitID + '''';
    Open;
  end;
  if V_BJ then
  begin
    Panel1.Caption := '本级' + IntToStr(CurYear) + '年' + IntToStr(CurMonth) + '月集邮业务收入统计制定';
    BBt_Add.Caption := '汇总(%A)';
  end
  else
  begin
    Panel1.Caption := '下级' + IntToStr(CurYear) + '年' + IntToStr(CurMonth) + '月集邮业务收入统计补录';
    BBt_Add.Caption := '添加(%A)';
  end;
  Panel1.Left := (Self.Width - Panel1.Width) div 2;
  ChangeStatus := stNone;
end;

procedure TfrmP_JYYWSRDJ.DED_MonthKeyPress(Sender: TObject; var Key: Char);
begin
  if not (Key in ['0'..'9']) then
  begin
    Key := chr(13);
    Exit;
  end;
end;

procedure TfrmP_JYYWSRDJ.Qry_YWSRYFChange(Sender: TField);
begin
  if Sender = nil then
    Exit;
  if Comparetext(Sender.FieldName, 'YF') <> 0 then
    Exit;
  if Sender.AsInteger > 12 then
    Sender.AsInteger := 12;
end;

procedure TfrmP_JYYWSRDJ.Qry_YWSRJYPSRGetText(Sender: TField;
  var Text: string; DisplayText: Boolean);
begin
  Text := FloatToStr(Sender.AsFloat / 10000);
end;

procedure TfrmP_JYYWSRDJ.Qry_YWSRJYPSRSetText(Sender: TField;
  const Text: string);
begin
  Sender.AsFloat := StrToFloat(Text) * 10000;
end;

procedure TfrmP_JYYWSRDJ.DBGrid1TitleClick(Column: TColumn);
var
  SQLStr, OrderFieldName, TempStr: string;
  i, OrderPos: integer;
  SavedParams: TParams;
  AV_Column: TColumn;
begin
  AV_Column := Column;
  for i := 0 to AV_Column.Grid.FieldCount - 1 do
    TDBGrid(AV_Column.Grid).Columns[i].Font.Color := clBlack;
  if not (AV_Column.Field.FieldKind in [fkData, fkLookup]) then Exit;
  if AV_Column.Field.FieldKind = fkData
    then
    OrderFieldName := UpperCase(AV_Column.Field.FieldName)
  else
    OrderFieldName := UpperCase(AV_Column.Field.KeyFields);
  while Pos(OrderFieldName, ';') <> 0 do
    OrderFieldName := copy(OrderFieldName, 1, Pos(OrderFieldName, ';') - 1) + ',' + copy(OrderFieldName, Pos(OrderFieldName, ';') + 1, 100);
  with TQuery(TDBGrid(AV_Column.Grid).DataSource.DataSet) do
  begin
    SQLStr := UpperCase(SQL.Text);
    if ParamCount > 0 then
    begin
      SavedParams := TParams.Create;
      SavedParams.Assign(Params);
    end;
    OrderPos := pos('ORDER', SQLStr);
    if (OrderPos = 0) or (pos(OrderFieldName, copy(SQLStr, OrderPos, 100)) = 0) then
    begin
      TempStr := ' Order By ' + OrderFieldName + ' Asc';
      AV_Column.font.Color := clRed;
    end
    else if pos('ASC', SQLStr) = 0 then
    begin
      TempStr := ' Order By ' + OrderFieldName + ' Asc';
      AV_Column.font.Color := clRed;
    end
    else
    begin
      TempStr := ' Order By ' + OrderFieldName + ' Desc';
      AV_Column.font.Color := clGreen;
    end;
    if OrderPos <> 0 then SQLStr := Copy(SQLStr, 1, OrderPos - 1);
    SQLStr := SQLStr + TempStr;
    Active := False;
    SQL.Clear;
    SQL.Text := SQLStr;
    if ParamCount > 0 then
    begin
      Params.AssignValues(SavedParams);
      SavedParams.Free;
    end;
    Prepare;
    Open;
  end;
end;

procedure TfrmP_JYYWSRDJ.CB_DWMCChange(Sender: TObject);
begin
  Qry_YWSR.Edit;
  Qry_YWSR.FieldByName('DWDM').AsString := CB_DWMC.FieldString;
end;

procedure TfrmP_JYYWSRDJ.FormKeyPress(Sender: TObject; var Key: Char);
begin
  if Key = #13 then                                         {回车键}
    if (ActiveControl is TDBEdit) then                      {if not is a TDBGrid}
    begin
      Key := #0;                                            {eat enter key}
      Perform(WM_NEXTDLGCTL, 0, 0);                         {move to next control}
    end;
end;

function TfrmP_JYYWSRDJ.GetExistField(FQuery: TQuery; FieldName,
  SnapStr: string): string;
var
  FBookMark: TBookMark;
begin
  Result := 'ZRJM';
  if not FQuery.Active then Exit;
  if FQuery.FindField(FieldName) = nil then Exit;
  if FQuery.IsEmpty then Exit;
  try
    with FQuery do
    begin
      FBookMark := GetBookmark;
      DisableControls;
      First;
      while not Eof do
      begin
        Result := Result + SnapStr + FindField(FieldName).AsString;
        Next;
      end;
      EnableControls;
      GotoBookMark(FBookMark);
    end;
  except
    FQuery.EnableControls;
  end;
end;

end.