fncashexchstatistic.pas

详细的ERP设计资料

      +' order by  a.Date ,a.Code,a.ID ';
  ADOQuery.ExecSQL;

  ADOQuery.Close;//付款申请外币支出
  ADOQuery.SQL.Text :=' insert into #ExpenseList  ( '
      +' [日期],[编号],[业务类别],[摘要],[帐户名称],[币种名称], '
      +' [支出原币金额],'
      +' [支出汇率],[支出本币金额],[经手人],[备注],[凭单状态] ) '
      +' Select a.Date [日期], '
      +' a.Code [编号],                       '
      +' a.BillMode [业务类别],               '
      +' Brief [摘要],                        '
      +' b.name [帐户名称],                   '
      +' d.name [币种名称],                   '
      +' Isnull(AmountOrigin,0)*Isnull(ModeDC,1)*Isnull(ModeC,1) [支出原币金额]  , '
      +' Isnull(AmountRate,0) [支出汇率]  ,                                    '
      +' Isnull(AmountC,0)*Isnull(ModeDC,1)*Isnull(ModeC,1) [支出本币金额]  ,      '
      +' c.name [经手人],                                                      '
      +' a.memo [备注]  ,                                                      '
      +' a.RecordState [凭单状态]                                              '
      +' from FNCashOutMaster a                                           '
      +' left outer join  FNAccounts b on b.id=a.AccountsID                    '
      +' left outer join  MSEmployee c on c.id=a.EmployeeID                    '
      +' left outer join  MSCurrency d on d.id=b.CurrencyID                    '
      +' where a.RecordState<>'+ Quotedstr('删除')
      +' and d.IsLocation<>1 '
      +' order by  a.Date ,a.Code,a.ID ';
  ADOQuery.ExecSQL;

  ADOQuery.Close;//结算收款外币收入
  ADOQuery.SQL.Text :=' insert into #ExpenseList  ( '
      +' [日期],[编号],[业务类别],[摘要],[帐户名称],[币种名称], '
      +' [收入原币金额],'
      +' [收入汇率],[收入本币金额],[经手人],[备注],[凭单状态] ) '
      +' Select a.Date [日期], '
      +' a.Code [编号],                       '
      +' a.BillMode [业务类别],               '
      +' Brief [摘要],                        '
      +' b.name [帐户名称],                   '
      +' d.name [币种名称],                   '
      +' Isnull(AmountOrigin,0)*Isnull(ModeDC,1)*Isnull(ModeC,1) [收入原币金额]  , '
      +' Isnull(AmountRate,0) [收入汇率]  ,                                    '
      +' Isnull(AmountD,0)*Isnull(ModeDC,1)*Isnull(ModeC,1) [收入本币金额]  ,      '
      +' c.name [经手人],                                                      '
      +' a.memo [备注]  ,                                                      '
      +' a.RecordState [凭单状态]                                              '
      +' from FNClearSLMaster a                                           '
      +' left outer join  FNAccounts b on b.id=a.AccountsID                    '
      +' left outer join  MSEmployee c on c.id=a.EmployeeID                    '
      +' left outer join  MSCurrency d on d.id=b.CurrencyID                    '
      +' where a.RecordState<>'+ Quotedstr('删除')
      +' and d.IsLocation<>1 '
      +' order by  a.Date ,a.Code,a.ID ';
  ADOQuery.ExecSQL;

  ADOQuery.Close;//结算付款外币支出
  ADOQuery.SQL.Text :=' insert into #ExpenseList  ( '
      +' [日期],[编号],[业务类别],[摘要],[帐户名称],[币种名称], '
      +' [支出原币金额],'
      +' [支出汇率],[支出本币金额],[经手人],[备注],[凭单状态] ) '
      +' Select a.Date [日期], '
      +' a.Code [编号],                       '
      +' a.BillMode [业务类别],               '
      +' Brief [摘要],                        '
      +' b.name [帐户名称],                   '
      +' d.name [币种名称],                   '
      +' Isnull(AmountOrigin,0)*Isnull(ModeDC,1)*Isnull(ModeC,1) [支出原币金额]  , '
      +' Isnull(AmountRate,0) [支出汇率]  ,                                    '
      +' Isnull(AmountC,0)*Isnull(ModeDC,1)*Isnull(ModeC,1) [支出本币金额]  ,      '
      +' c.name [经手人],                                                      '
      +' a.memo [备注]  ,                                                      '
      +' a.RecordState [凭单状态]                                              '
      +' from FNClearPCMaster a                                           '
      +' left outer join  FNAccounts b on b.id=a.AccountsID                    '
      +' left outer join  MSEmployee c on c.id=a.EmployeeID                    '
      +' left outer join  MSCurrency d on d.id=b.CurrencyID                    '
      +' where a.RecordState<>'+ Quotedstr('删除')
      +' and d.IsLocation<>1 '
      +' order by  a.Date ,a.Code,a.ID ';
  ADOQuery.ExecSQL;

  ADOQuery.Close;//工程收款外币收入
  ADOQuery.SQL.Text :=' insert into #ExpenseList  ( '
      +' [日期],[编号],[业务类别],[摘要],[帐户名称],[币种名称], '
      +' [收入原币金额],'
      +' [收入汇率],[收入本币金额],[经手人],[备注],[凭单状态] ) '
      +' Select a.Date [日期], '
      +' a.Code [编号],                       '
      +' a.BillMode [业务类别],               '
      +' Brief [摘要],                        '
      +' b.name [帐户名称],                   '
      +' d.name [币种名称],                   '
      +' Isnull(AmountOrigin,0)*Isnull(ModeDC,1)*Isnull(ModeC,1) [收入原币金额]  , '
      +' Isnull(AmountRate,0) [收入汇率]  ,                                    '
      +' Isnull(AmountD,0)*Isnull(ModeDC,1)*Isnull(ModeC,1) [收入本币金额]  ,      '
      +' c.name [经手人],                                                      '
      +' a.memo [备注]  ,                                                      '
      +' a.RecordState [凭单状态]                                              '
      +' from FNClearEGMaster a                                           '
      +' left outer join  FNAccounts b on b.id=a.AccountsID                    '
      +' left outer join  MSEmployee c on c.id=a.EmployeeID                    '
      +' left outer join  MSCurrency d on d.id=b.CurrencyID                    '
      +' where a.RecordState<>'+ Quotedstr('删除')
      +' and d.IsLocation<>1 '
      +' order by  a.Date ,a.Code,a.ID ';
  ADOQuery.ExecSQL;


















  ADOQuery.Close;
  ADOQuery.SQL.Text :=' Update  #ExpenseList set '
      +' [本币余额]=(ISNull([收入本币金额],0)-ISNull([支出本币金额],0)) ,'
      +' [原币余额]=(ISNull([收入原币金额],0)-ISNull([支出原币金额],0)) ';
  ADOQuery.ExecSQL;
  ADOQuery.Close;
  ADOQuery.SQL.Text :=' Update  #ExpenseList set '
      +' [结存汇率]=(ISNull([本币余额],0)/ISNull([原币余额],0))'
      +' where ISNull([原币余额],0)<>0' ;
  ADOQuery.ExecSQL;

  ADOQuery.Close;
  ADOQuery.SQL.Text :=' if exists (select * from dbo.sysobjects where '
      +' id = object_id '
      +' (' +Quotedstr('[dbo].[TempExpenseList]')+') and OBJECTPROPERTY(id,'
      +Quotedstr('IsUserTable')+' ) = 1) drop table [dbo].[TempExpenseList] ';
  ADOQuery.ExecSQL;
  ADOQuery.Close;
  ADOQuery.SQL.Text :=' select top 1 * into TempExpenseList from #ExpenseList' ;
  ADOQuery.ExecSQL;
  ADOQuery.Close;
  ADOQuery.SQL.Text :=' select top 1 * from TempExpenseList' ;
  ADOQuery.open;
  ExpSttcCheckListBox.Columns :=4;
  ExpSttcCheckListBox.Items.Clear;
  ExpSttcCheckListBox.Hint :='';
  WNADOCQuery1.Field.Clear;
  for I := 0 to ADOQuery.Fields.Count - 1 do
  begin
    if not (ADOQuery.Fields[i] is TNumericField)  then
       ExpSttcCheckListBox.Items.Add(Trim(ADOQuery.Fields[i].FieldName))
     else
       ExpSttcCheckListBox.Hint :=ExpSttcCheckListBox.Hint
         +', sum(isnull('+Trim(ADOQuery.Fields[i].FieldName)+',0)) as ['
         +Trim(ADOQuery.Fields[i].FieldName)+'] ';
     WNADOCQuery1.Field.Add(Trim(ADOQuery.Fields[i].FieldName));
  end;
  WhereStr :=' where  1=1 ';
  Memo1.Text :='不限制条件!';
  SelectStr :='';
end;

procedure TFNCashExchStatisticForm.Button1Click(Sender: TObject);
begin
  Panel6.Visible :=True;
  Panel6.Repaint;
  Button1.Tag:=1 ;
  adsMaster.Close;
  adsMaster.CommandText:=' select * from #ExpenseList'+WhereStr
      +' order by [日期], [编号]' ;
//  showmessage(adsMaster.CommandText);
  adsMaster.open;
  Panel6.Visible :=False;
  UpdateDBGrid;
  DBGrid.hint :='';
end;

procedure TFNCashExchStatisticForm.FormActivate(Sender: TObject);
begin
  inherited;
  if Trim(SelectStr) ='' then
    begin
      ExpSttcCheckListBox.Checked[4] :=true;
      ExpSttcCheckListBox.ItemIndex := 4;
      ExpSttcCheckListBox.OnClickCheck(ExpSttcCheckListBox);
      ExpSttcCheckListBox.Checked[5] :=true;
      ExpSttcCheckListBox.ItemIndex := 5;
      ExpSttcCheckListBox.OnClickCheck(ExpSttcCheckListBox);
    end;
  Button2Click(sender);
end;

procedure TFNCashExchStatisticForm.Button2Click(Sender: TObject);
var I :integer;
    SelectStr1,ExpenseStr,ClientCostStr:String;
begin
  inherited;
  Panel6.Visible :=True;
  Panel6.Repaint;
  Button1.Tag:=0;

  if Trim(SelectStr) ='' then
    begin
      ExpSttcCheckListBox.Checked[1] :=true;
      ExpSttcCheckListBox.ItemIndex := 1;
      ExpSttcCheckListBox.OnClickCheck(ExpSttcCheckListBox);
    end;
  SelectStr1 :=Trim(SelectStr);
  while Pos(',', SelectStr1)=1 do  SelectStr1[Pos(',', SelectStr1)] :=' ';

  ADOQuery.Close;
  ADOQuery.SQL.Text :=' IF EXISTS(  SELECT * FROM tempdb..sysobjects '
        +' WHERE ID = OBJECT_ID('+Quotedstr('tempdb..#ExpenseListTtl')
        +' )) DROP TABLE #ExpenseListTtl ' ;
  ADOQuery.ExecSQL;

  ADOQuery.Close;
  ADOQuery.SQL.Text:=' select ' + SelectStr1+ ExpSttcCheckListBox.hint
    +' into #ExpenseListTtl from #ExpenseList  '
    +WhereStr +' group by '+SelectStr1;
  ADOQuery.ExecSQL;

  ADOQuery.Close;
  ADOQuery.SQL.Text:=' update #ExpenseListTtl set '
    +'[本币余额]=Isnull([收入本币金额],0)-Isnull([支出本币金额],0), '
    +'[原币余额]=Isnull([收入原币金额],0)-Isnull([支出原币金额],0)  ' ;
  ADOQuery.ExecSQL;
  ADOQuery.Close;
  ADOQuery.SQL.Text:=' update #ExpenseListTtl set [结存汇率]= '
     +' Isnull([本币余额],0)/Isnull([原币余额],0) where Isnull([原币余额],0)<>0 ';
  ADOQuery.ExecSQL;

  adsMaster.Close;
  adsMaster.CommandText:=' select * from #ExpenseListTtl';
  adsMaster.open;

  Panel6.Visible :=False;
  UpdateDBGrid;
  DBGrid.hint :='汇总项目:'+SelectStr1;
end;

procedure TFNCashExchStatisticForm.N1Click(Sender: TObject);
begin
  ExpSttcCheckListBox.Sorted :=not ExpSttcCheckListBox.Sorted;
end;

procedure TFNCashExchStatisticForm.N2Click(Sender: TObject);
var I:integer;
begin
  for I := 0 to ExpSttcCheckListBox.Items.Count - 1 do
  begin
    ExpSttcCheckListBox.Checked[I] :=not ExpSttcCheckListBox.Checked[I];
    ExpSttcCheckListBox.ItemIndex := I;
    ExpSttcCheckListBox.OnClickCheck(ExpSttcCheckListBox);
  end;
end;

procedure TFNCashExchStatisticForm.N3Click(Sender: TObject);
var I:integer;
begin
  for I := 0 to ExpSttcCheckListBox.Items.Count - 1 do
  begin
    ExpSttcCheckListBox.Checked[I] :=True;
    ExpSttcCheckListBox.ItemIndex := I;
    ExpSttcCheckListBox.OnClickCheck(ExpSttcCheckListBox);
  end;
end;

procedure TFNCashExchStatisticForm.N4Click(Sender: TObject);
var I:integer;
begin
  for I := 0 to ExpSttcCheckListBox.Items.Count - 1 do
  begin
    ExpSttcCheckListBox.Checked[I] :=False;
    ExpSttcCheckListBox.ItemIndex := I;
    ExpSttcCheckListBox.OnClickCheck(ExpSttcCheckListBox);
  end;
end;

procedure TFNCashExchStatisticForm.adsMasterBeforeOpen(DataSet: TDataSet);
begin
  inherited;
  adsMaster.IndexFieldNames := '';
end;

procedure TFNCashExchStatisticForm.DateTimePicker2Exit(Sender: TObject);
begin
  if DateTimePicker1.Date>DateTimePicker2.Date then
    DateTimePicker1.Date :=DateTimePicker2.Date;
end;

procedure TFNCashExchStatisticForm.DateTimePicker1Exit(Sender: TObject);
begin