csh830_01.pas.svn-base

开源的网站整站程序,功能强大带采集系统.

unit Csh830_01;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, BAS200_08, dxExEdtr, DB, ADODB, ActnList, Menus, dxCntner, dxTL,
  dxDBCtrl, dxDBGrid, ExtCtrls, ComCtrls, wwfltdlg,
  ToolWin, Buttons, StdCtrls, wwdbdatetimepicker, dxDBTLCl, dxGrClms, DateUtils,
  Grids, DBGrids, TeEngine, Series, TeeProcs, Chart, DbChart;

type
  TCsh830_01Form = class(TBas200_08Form)
    qryCsh550: TADOQuery;
    ADOQuery1: TADOQuery;
    lblAcct: TLabel;
    cbAcct: TComboBox;
    lblDate: TLabel;
    edtFromDate: TwwDBDateTimePicker;
    edtToDate: TwwDBDateTimePicker;
    ADODataSet1CURNM: TStringField;
    ADODataSet1DATE1: TDateTimeField;
    ADODataSet1DATE2: TDateTimeField;
    ADODataSet1MEMO: TStringField;
    ADODataSet1AMT01: TBCDField;
    ADODataSet1AMT02: TBCDField;
    ADODataSet1AMT03: TBCDField;
    dxDBGrid1CURNM: TdxDBGridMaskColumn;
    dxDBGrid1DATE1: TdxDBGridDateColumn;
    dxDBGrid1DATE2: TdxDBGridDateColumn;
    dxDBGrid1MEMO: TdxDBGridMaskColumn;
    dxDBGrid1AMT01: TdxDBGridCurrencyColumn;
    dxDBGrid1AMT02: TdxDBGridCurrencyColumn;
    dxDBGrid1AMT03: TdxDBGridCurrencyColumn;
    sbSearch: TSpeedButton;
    ActOpen: TAction;
    PopupMenu1: TPopupMenu;
    N1: TMenuItem;
    N2: TMenuItem;
    N4: TMenuItem;
    N5: TMenuItem;
    procedure FormCreate(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure sbSearchClick(Sender: TObject);
    procedure cbAcctChange(Sender: TObject);
    procedure dxDBGrid1AMT01DrawSummaryFooter(Sender: TObject;
      ACanvas: TCanvas; ARect: TRect; var AText: String;
      var AAlignment: TAlignment; AFont: TFont; var AColor: TColor;
      var ADone: Boolean);
    procedure dxDBGrid1AMT02DrawSummaryFooter(Sender: TObject;
      ACanvas: TCanvas; ARect: TRect; var AText: String;
      var AAlignment: TAlignment; AFont: TFont; var AColor: TColor;
      var ADone: Boolean);
    procedure ActPrintExecute(Sender: TObject);
    procedure ActOpenExecute(Sender: TObject);
  private
    Amount,ASum1,ASum2:Double;
    procedure GetWeekOfData(AccNo:Integer;AWeekFromDate,AWeekToDate:TDateTime);
    { Private declarations }
  public
    procedure SetInterface; override;
    procedure RefreshEvent; override;
    procedure ReportGetValue(const ParName: String; var ParValue: Variant); override;
    { Public declarations }
  end;

var
  Csh830_01Form: TCsh830_01Form;

implementation

uses SYSDATA, CommFun, CshComm, Csh510_01;

{$R *.dfm}

procedure TCsh830_01Form.SetInterface;
begin
  inherited;
  Caption:=GetDBString('CSH83001001');  //资金周报表
  lblAcct.Caption:=GetDBString('CSH83001002');  //帐户
  lblDate.Caption:=GetDBString('CSH83001003');  //日期
  sbSearch.Caption:=GetDBString('CSH83001004');  //查询
  ADODataSet1CURNM.DisplayLabel:=GetDBString('CSH83001005');  //币别
  ADODataSet1DATE1.DisplayLabel:=GetDBString('CSH83001006');  //起始日期
  ADODataSet1DATE2.DisplayLabel:=GetDBString('CSH83001007');  //结束日期
  ADODataSet1MEMO.DisplayLabel:=GetDBString('CSH83001008');  //摘要
  ADODataSet1AMT01.DisplayLabel:=GetDBString('CSH83001009');  //收入
  ADODataSet1AMT02.DisplayLabel:=GetDBString('CSH83001010');  //支出
  ADODataSet1AMT03.DisplayLabel:=GetDBString('CSH83001011');  //余额
end;

procedure TCsh830_01Form.FormCreate(Sender: TObject);
begin
  inherited;
  SetInterface;
  
  //取得所有帐户名称
  cbAcct.Items.Clear;
  cbAcct.Items.Add(GetDBString('CSH83001012'));  //所有帐户
  qryCsh550.Close;
  qryCsh550.SQL.Clear;
  if ASuper then
  begin
    qryCsh550.SQL.Add('select * from CSH550');
  end else
  begin
    qryCsh550.SQL.Add('select * from SYS500C A, CSH550 B');
    qryCsh550.SQL.Add('where A.S500C_002=B.C550_001 and A.S500C_003=''ACC'' and A.S500C_004='+GetBoolean(True)+' and A.S500C_001='+IntToStr(AUserID));
  end;
  qryCsh550.Open;
  while not qryCsh550.Eof do
  begin
    cbAcct.Items.Add(qryCsh550.FieldByName('C550_002').AsString);
    qryCsh550.Next;
  end;
  //取得当月起始＆结束日期
  edtFromDate.Date:=GetMonthFirstDate(Date);
  edtToDate.Date:=GetMonthEndDate(Date);
  cbAcct.ItemIndex:=0;
end;

procedure TCsh830_01Form.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
  inherited;
  qryCsh550.Close;
end;

//取得每周的数据
procedure TCsh830_01Form.GetWeekOfData(AccNo:Integer;AWeekFromDate,AWeekToDate:TDateTime);
var
  ACURR:string;
begin
  if AccNo<>0 then
  begin
    ADOQuery1.Close;
    ADOQuery1.SQL.Clear;
    ADOQuery1.SQL.Add('select C.G100_003, A.C510A_005, sum(A.C510A_009) as C510A_009');
    ADOQuery1.SQL.Add('from CSH510A A, CSH550 B, GLD100 C');
    ADOQuery1.SQL.Add('where A.C510A_003=B.C550_001 and B.C550_005=C.G100_001 and');
    ADOQuery1.SQL.Add('      A.C510A_004<>0 and A.C510A_003='+IntToStr(AccNo)+' and');
    ADOQuery1.SQL.Add('      A.C510A_010>='+GetDateString(AWeekFromDate)+' and A.C510A_010<='+GetDateString(AWeekToDate));
    ADOQuery1.SQL.Add('group by C.G100_003, A.C510A_005');
    ADOQuery1.SQL.Add('order by C.G100_003, A.C510A_005');
    ADOQuery1.Open;
  end else  //所有帐户
  begin
    ADOQuery1.Close;
    ADOQuery1.SQL.Clear;
    ADOQuery1.SQL.Add('select C.G100_003, A.C510A_005, sum(A.C510A_009) as C510A_009');
    ADOQuery1.SQL.Add('from CSH510A A, CSH550 B, GLD100 C');
    ADOQuery1.SQL.Add('where A.C510A_003=B.C550_001 and B.C550_005=C.G100_001 and');
    ADOQuery1.SQL.Add('      A.C510A_004<>0 and');
    ADOQuery1.SQL.Add('      A.C510A_010>='+GetDateString(AWeekFromDate)+' and A.C510A_010<='+GetDateString(AWeekToDate));
    ADOQuery1.SQL.Add('group by C.G100_003, A.C510A_005');
    ADOQuery1.SQL.Add('order by C.G100_003, A.C510A_005');
    ADOQuery1.Open;
  end;

  while not ADOQuery1.Eof do
  begin
    if ACURR<>ADOQuery1.FieldByName('G100_003').Value then
    begin
      ADODataSet1.Append;
      ADODataSet1.FieldByName('CURNM').Value:=ADOQuery1.FieldByName('G100_003').Value;
      ADODataSet1.FieldByName('DATE1').Value:=AWeekFromDate;
      ADODataSet1.FieldByName('DATE2').Value:=AWeekToDate;
      ADODataSet1.FieldByName('MEMO').Value:=GetDBString('CSH83001014');  //本周合计
      ADODataSet1.Post;
      ACURR:=ADOQuery1.FieldByName('G100_003').Value;
    end;

    ADODataSet1.Edit;
    if ADOQuery1.FieldByName('C510A_005').AsInteger=0 then
    begin
      ADODataSet1.FieldByName('AMT01').Value:=ADOQuery1.FieldByName('C510A_009').Value;  //收入
      ASum1:=ASum1+ADOQuery1.FieldByName('C510A_009').AsFloat;
      Amount:=Amount+ADOQuery1.FieldByName('C510A_009').AsFloat;
    end else if ADOQuery1.FieldByName('C510A_005').AsInteger=1 then
    begin
      ADODataSet1.FieldByName('AMT02').Value:=ADOQuery1.FieldByName('C510A_009').Value;  //支出
      ASum2:=ASum2+ADOQuery1.FieldByName('C510A_009').AsFloat;
      Amount:=Amount-ADOQuery1.FieldByName('C510A_009').AsFloat;
    end;
    ADODataSet1.FieldByName('AMT03').Value:=Amount;
    ADODataSet1.Post;

    ADOQuery1.Next;
  end;
end;

procedure TCsh830_01Form.sbSearchClick(Sender: TObject);
var
  AccNo:Integer;
  AFromDate,AToDate:TDateTime;
  AWeekFromDate,AWeekToDate:TDateTime;
begin
  inherited;
//查询
  if cbAcct.Text='' then
  begin
    ShowMsg('UMS10000157');  //帐户不能为空
    cbAcct.SetFocus;
    Abort;
  end;
  if (edtFromDate.Text='') or (edtToDate.Text='') then
  begin
    ShowMsg('UMS10000156');  //起始日期或结束日期不能为空
    if edtFromDate.Text='' then edtFromDate.SetFocus else edtToDate.SetFocus;
    Abort;
  end;
  AFromDate:=edtFromDate.Date;
  AToDate:=edtToDate.Date;
  if AFromDate>AToDate then
  begin
    ShowMsg('UMS10000044');  //起始日期不能大于结束日期
    edtFromDate.SetFocus;
    Abort;
  end;
  //建立临时表
  Screen.Cursor:=crHourGlass;
  ADODataSet1.DisableControls;
  ADODataSet1.Close;
  ADODataSet1.FieldDefs.Clear;
  ADODataSet1.FieldDefs.Add('CURNM',ftString,30);
  ADODataSet1.FieldDefs.Add('DATE1',ftDateTime,0);
  ADODataSet1.FieldDefs.Add('DATE2',ftDateTime,0);
  ADODataSet1.FieldDefs.Add('MEMO',ftString,30);
  ADODataSet1.FieldDefs.Add('AMT01',ftCurrency,0);
  ADODataSet1.FieldDefs.Add('AMT02',ftCurrency,0);
  ADODataSet1.FieldDefs.Add('AMT03',ftCurrency,0);
  ADODataSet1.CreateDataSet;

  //取得帐户编号
  if cbAcct.ItemIndex<>0 then
  begin
    SYSDM.qryQuery.Close;
    SYSDM.qryQuery.SQL.Clear;
    SYSDM.qryQuery.SQL.Add('select C550_001 from CSH550 where C550_002='+''''+cbAcct.Text+'''');
    SYSDM.qryQuery.Open;
    AccNo:=SYSDM.qryQuery.Fields[0].Value;
    //取得期初余额
    Amount:=GetAccAmount(AccNo, AFromDate, AToDate, 1);
  end else  //所有帐户
  begin
    //取得所有帐户的期初余额总和
    Amount:=0;
    qryCsh550.Open;
    qryCsh550.First;
    while not qryCsh550.Eof do
    begin
      AccNo:=qryCsh550.FieldByName('C550_001').Value;
      Amount:=Amount+GetAccAmount(AccNo, AFromDate, AToDate, 1);
      qryCsh550.Next;
    end;
    AccNo:=0;
  end;

  ADODataSet1.Append;
  ADODataSet1.FieldByName('MEMO').Value:=GetDBString('CSH83001013');  //期初余额
  ADODataSet1.FieldByName('AMT03').Value:=Amount;
  ADODataSet1.Post;

  ASum1:=0;
  ASum2:=0;
  AWeekFromDate:=AFromDate;
  while AFromDate<=AToDate do
  begin
    //取得每周的起始日期及结束日期
    if DayOfWeek(AFromDate)=1 then AWeekFromDate:=AFromDate;
    if DayOfWeek(AFromDate)=7 then
    begin
      AWeekToDate:=AFromDate;
      GetWeekOfData(AccNo,AWeekFromDate,AWeekToDate);  //取得每周的数据
    end;
    AFromDate:=AFromDate+1;
  end;
  ADODataSet1.EnableControls;
  Screen.Cursor:=crDefault;
end;

procedure TCsh830_01Form.ReportGetValue(const ParName: String;
  var ParValue: Variant);
begin
  inherited;
  if ParName='CURNM' then ParValue:=GetDBString('CSH83001005')  //'币别'
  else if ParName='BILL_DT1' then ParValue:=GetDBString('CSH83001006')  //'起始日期'
  else if ParName='BILL_DT2' then ParValue:=GetDBString('CSH83001007')  //'结束日期'
  else if ParName='MEMO_001' then ParValue:=GetDBString('CSH83001008')  //'摘要'
  else if ParName='AMOT_001' then ParValue:=GetDBString('CSH83001009')  //'收入'
  else if ParName='AMOT_002' then ParValue:=GetDBString('CSH83001010')  //'支出'
  else if ParName='AMOT_003' then ParValue:=GetDBString('CSH83001011')  //'余额'
  else if ParName='ACCT_NAM' then ParValue:=GetDBString('CSH83001015')+cbAcct.Text  //'帐户名称：'
  else if ParName='DATE_001' then ParValue:=GetDBString('CSH83001016')+edtFromDate.Text  //'起始日期：'
  else if ParName='DATE_002' then ParValue:=GetDBString('CSH83001017')+edtToDate.Text  //'结束日期：'
end;

procedure TCsh830_01Form.cbAcctChange(Sender: TObject);
begin
  inherited;
  sbSearch.Click;
end;

procedure TCsh830_01Form.dxDBGrid1AMT01DrawSummaryFooter(Sender: TObject;
  ACanvas: TCanvas; ARect: TRect; var AText: String;
  var AAlignment: TAlignment; AFont: TFont; var AColor: TColor;
  var ADone: Boolean);
begin
  inherited;
  if VarToStr(ASum1)<>'' then AText:=FloatToStrF(ASum1,ffCurrency,18,2);
  AColor := clWhite;
end;

procedure TCsh830_01Form.dxDBGrid1AMT02DrawSummaryFooter(Sender: TObject;
  ACanvas: TCanvas; ARect: TRect; var AText: String;
  var AAlignment: TAlignment; AFont: TFont; var AColor: TColor;
  var ADone: Boolean);
begin
  inherited;
  if VarToStr(ASum2)<>'' then AText:=FloatToStrF(ASum2,ffCurrency,18,2);
  AColor := clWhite;
end;

procedure TCsh830_01Form.ActPrintExecute(Sender: TObject);
begin
  try
    SYSDM.frReport2.OnGetValue:=ReportGetValue;
    inherited;
  finally
    SYSDM.frReport2.OnGetValue:=nil;
  end;
end;

procedure TCsh830_01Form.ActOpenExecute(Sender: TObject);
var
  AFieldInfo:TwwFieldInfo;
begin
  inherited;
//查看
  if ADODataSet1.IsEmpty then Exit;
  Screen.Cursor:=crHourGlass;
  Csh510_01Form:=TCsh510_01Form.Create(Application);
  Csh510_01Form.edtFromDate.Date:=edtFromDate.Date;
  Csh510_01Form.edtToDate.Date:=edtToDate.Date;
  Csh510_01Form.sbSearch.Click;
  AFieldInfo:=TwwFieldInfo.Create;
  FilterDialog(Csh510_01Form.DataSource1,False);
  AFieldInfo.FieldName:='I120_003';
//  AFieldInfo.DisplayLabel:=Csh510_01Form.ADOQuery1I120_003.DisplayLabel;
  AFieldInfo.MatchType:=fdMatchStart;
  AFieldInfo.CaseSensitive:=False;
  AFieldInfo.NonMatching:=False;
//  AFieldInfo.FilterValue:=ADODataSet1FName.Value;;
  SYSDM.wwFilterDialog1.FieldInfo.Add(AFieldInfo);
  SYSDM.wwFilterDialog1.ApplyFilter;
  Screen.Cursor:=crDefault;
  Csh510_01Form.ShowModal;
//  if Assigned(AFieldInfo) then AFieldInfo.Free;
end;

procedure TCsh830_01Form.RefreshEvent;
begin
  inherited;
  sbSearch.Click;
end;

initialization
  RegisterClass(TCsh830_01Form);

finalization
  UnRegisterClass(TCsh830_01Form);

end.