fnactualcash.pas

详细的ERP设计资料

      +' 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(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 :=' insert into #ExpenseList  ( '
      +'[日期],[编号],[业务类别],[摘要],[帐户名称],[币种名称], '
      +'[支出本币金额],[经手人],[备注],[凭单状态] ) '
      +' Select a.Date [日期], '
      +' a.Code [编号],                       '
      +' a.BillMode [业务类别],               '
      +' Brief [摘要],                        '
      +' b.name [帐户名称],                   '
      +' d.name [币种名称],                   '
      +' 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(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(AmountC,0)*Isnull(ModeDC,1)*Isnull(ModeC,1) [支出本币金额]  ,      '
      +' c.name [经手人],                                                      '
      +' a.memo [备注]  ,                                                      '
      +' a.RecordState [凭单状态]                                              '
      +' from FNExpenseMaster 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(AmountD,0)*Isnull(ModeDC,1)*Isnull(ModeC,1) [支出本币金额]  ,      '
      +' c.name [经手人],                                                      '
      +' a.memo [备注]  ,                                                      '
      +' a.RecordState [凭单状态]                                              '
      +' from FNCashOutInMaster a                                           '
      +' left outer join  FNAccounts b on b.id=a.ClientID                    '
      +' 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 b.AccountType not like '+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(AmountD,0)*Isnull(ModeDC,1)*Isnull(ModeC,1) [收入本币金额]  ,      '
      +' c.name [经手人],                                                      '
      +' a.memo [备注]  ,                                                      '
      +' a.RecordState [凭单状态]                                              '
      +' from FNCashOutInMaster 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 b.AccountType not like '+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)) ';
  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 TFNActualCashFrom.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 TFNActualCashFrom.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 TFNActualCashFrom.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) ';
  ADOQuery.ExecSQL;

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

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

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

procedure TFNActualCashFrom.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 TFNActualCashFrom.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 TFNActualCashFrom.N4Click(Sender: TObject);
var I:integer;
begin