fncashexchstatistic.pas

详细的ERP设计资料

  if DateTimePicker1.Date>DateTimePicker2.Date then
    DateTimePicker2.Date :=DateTimePicker1.Date;
end;

procedure TFNCashExchStatisticForm.Button4Click(Sender: TObject);
var year,month,day:word;
begin
  inherited;
  Panel2.Enabled :=False;
  DBGrid.Enabled :=False;
  VipsetPanel.Visible :=True;
  VipsetPanel.Repaint;
//  DateTimePicker1.Date :=date;
//  DateTimePicker2.Date :=EndoftheMonth(date);
  DecodeDate(date,year,month,day)  ;
  DateTimePicker1.Date :=Encodedate(year,month,1);
  DateTimePicker2.Date :=date;
end;

procedure TFNCashExchStatisticForm.Button6Click(Sender: TObject);
begin
  Panel2.Enabled :=True;
  DBGrid.Enabled :=True;
  VipsetPanel.Visible :=False;
end;

procedure TFNCashExchStatisticForm.Button5Click(Sender: TObject);
begin
  Panel2.Enabled :=True;
  DBGrid.Enabled :=True;
  VipsetPanel.Visible :=False;
  ADOQuery.Close;
  ADOQuery.SQL.Text :=' IF EXISTS(  SELECT * FROM tempdb..sysobjects '
        +' WHERE ID = OBJECT_ID('+Quotedstr('tempdb..#ExpenseList0')
        +' )) DROP TABLE #ExpenseList0' ;
  ADOQuery.ExecSQL;

  ADOQuery.Close;
  ADOQuery.SQL.Text :=' select * into #ExpenseList0 from #ExpenseList'
    +' where [日期] >='+Quotedstr(Datetostr(DateTimePicker1.Date))
    +' and [日期] <='+Quotedstr(Datetostr(DateTimePicker2.Date));
  ADOQuery.ExecSQL;

  ADOQuery.Close;
  ADOQuery.SQL.Text :=' Insert into  #ExpenseList0 ( '
    +' [日期],[编号],[业务类别],[帐户名称],[币种名称],[原币余额],'
    +' [本币余额])'
    +' select '+Quotedstr(Datetostr(DateTimePicker1.Date-1))
    +' as [日期],'+Quotedstr('----')+' , '+Quotedstr('期初结转')+' , '
    +' [帐户名称],[币种名称],'
    +' (sum(isnull([收入原币金额],0))-sum(isnull([支出原币金额],0)) ) [原币余额] , '
    +' (sum(isnull([收入本币金额],0))-sum(isnull([收入本币金额],0)) ) [本币余额]  '
    +' from #ExpenseList '
    +' where [日期] <'+Quotedstr(Datetostr(DateTimePicker1.Date))
    +' Group by [帐户名称],[币种名称] ' ;
  ADOQuery.ExecSQL ;       //有期初余额的帐户

  ADOQuery.Close;
  ADOQuery.SQL.Text :=' Insert into  #ExpenseList0 '
    +' ([帐户名称],[币种名称] ,[日期],[编号],[业务类别] )'
    +' select a.name [帐户名称], b.Name [币种名称] , '
    + Quotedstr(Datetostr(DateTimePicker1.Date-1))
    +' as [日期],'+Quotedstr('----')+' , '+Quotedstr('期初结转')
    +' from FNAccounts a '
    +' left outer join MSCurrency b on b.ID=a.CurrencyID '
    +' where a.Name not in ( select distinct [帐户名称] from #ExpenseList0  '
    +' where [业务类别]='+ Quotedstr('期初结转')+' ) '
    +' and a.Name in ( select distinct [帐户名称] from #ExpenseList ) ';
  ADOQuery.ExecSQL ;  //无期初余额的帐户

  ADOQuery.Close;
  ADOQuery.SQL.Text :=' IF EXISTS(  SELECT * FROM tempdb..sysobjects '
    +' WHERE ID = OBJECT_ID('+Quotedstr('tempdb..#ExpenseList')
    +' )) DROP TABLE #ExpenseList' ;
  ADOQuery.ExecSQL;
  ADOQuery.Close;
  ADOQuery.SQL.Text :=' select * into #ExpenseList from #ExpenseList0'
    +' where [日期] is not null '
    +' order by [日期]  ' ;
  ADOQuery.ExecSQL;
  if Button1.Tag=1 then Button1Click(sender)
    else Button2Click(sender);
end;

procedure TFNCashExchStatisticForm.ExpSttcCheckListBoxClickCheck(
  Sender: TObject);
var CheckStr :string;
begin
  CheckStr :=','+Trim(ExpSttcCheckListBox.Items[ExpSttcCheckListBox.ItemIndex]);
  if (ExpSttcCheckListBox.Checked[ExpSttcCheckListBox.ItemIndex]) and
     (pos(CheckStr,SelectStr)<=0) then  SelectStr :=Trim(SelectStr)+Trim(CheckStr);

  if (not ExpSttcCheckListBox.Checked[ExpSttcCheckListBox.ItemIndex]) and
     (pos(CheckStr,SelectStr)>0) then
     SelectStr :=StringReplace(SelectStr,CheckStr,'',[rfReplaceAll, rfIgnoreCase]);
end;

procedure TFNCashExchStatisticForm.ClientAccountLiastClick(Sender: TObject);
var LocalCurrencyBalance,ForeignCurrencyBalance:real;
    I:Integer;
begin
  ADOQuery.Close;
  ADOQuery.SQL.Text :=' select * from #ExpenseList where [业务类别]='+Quotedstr('期初结转');
  ADOQuery.Open;
  if ADOQuery.IsEmpty then
  begin
    ShowMessage('请在“高级..”设置对帐日期，“确定”后，再执行此功能!');
//    Button4.OnClick(sneder);
    Exit;
  end;
  I :=0;
  ADOQuery.Close;
  ADOQuery.SQL.Text :=' select a.name [帐户名称],b.name 币种名称 '
    +' from FNAccounts a '
    +' Left Outer join MSCurrency b on b.ID=a.CurrencyID '
    +' where a.name in (select [帐户名称] from #ExpenseList '
    + WhereStr +' ) ';
  ADOQuery.Open;
  ADOQuery.First;
  while not ADOQuery.Eof do
  begin
    ADOQuery2.Close;
    ADOQuery2.SQL.Text :=' IF EXISTS(  SELECT * FROM tempdb..sysobjects '
          +' WHERE ID = OBJECT_ID('+Quotedstr('tempdb..#ClientAccountList')
          +' )) DROP TABLE #ClientAccountList' ;
    ADOQuery2.ExecSQL;
    ADOQuery2.Close;
    ADOQuery2.SQL.Text :=' CREATE TABLE #ClientAccountList ( '
	+' [ID] [int] IDENTITY (1, 1) NOT NULL , '
	+' [日期] [datetime] NULL ,              '
	+' [编号] [varchar] (20)  NULL ,         '
	+' [业务类别] [varchar] (16) NULL ,      '
	+' [收入本币金额] [Float] NULL ,      '
	+' [收入汇率] [Float] NULL ,      '
	+' [收入原币金额] [Float] NULL ,      '
	+' [支出本币金额] [Float] NULL ,      '
	+' [支出汇率] [Float] NULL ,      '
	+' [支出原币金额] [Float] NULL ,      '
	+' [本币余额] [Float] NULL ,      '
	+' [结存汇率] [Float] NULL ,      '
	+' [原币余额] [Float] NULL ,      '
	+' [备注] [varchar] (30)  NULL     '
	+' )  ';
    ADOQuery2.ExecSQL;
    ADOQuery2.Close;
//    ADOQuery2.SQL.Text :=' select * from  #ClientAccountList ';
//     ADOQuery2.open;
    ADOQuery2.SQL.Text :=' Truncate TABLE #ClientAccountList ';
    ADOQuery2.ExecSQL;

    ADOQuery2.Close;
    ADOQuery2.SQL.Text :=' Insert Into #ClientAccountList ( '
      +' [日期],[编号],[业务类别],[收入本币金额],[收入汇率],'
      +' [收入原币金额],[支出本币金额],[支出汇率],[支出原币金额],'
      +' [本币余额],[结存汇率],[原币余额],[备注] ) '
      +' select [日期],[编号],[业务类别],[收入本币金额],[收入汇率],'
      +' [收入原币金额],[支出本币金额],[支出汇率],[支出原币金额],'
      +' [本币余额],[结存汇率],[原币余额],[备注]  '
      +' from #ExpenseList '
      +' where [帐户名称]='+Quotedstr(ADOQuery.FieldByName('帐户名称').AsString)
      +' order by [日期],[编号],[业务类别] ';
    ADOQuery2.ExecSQL;

    ADOQuery2.Close;
    ADOQuery2.SQL.Text :=' select * from  #ClientAccountList order by [日期],[ID]';
    ADOQuery2.open;
    ADOQuery2.First;
    LocalCurrencyBalance := 0;
    LocalCurrencyBalance := LocalCurrencyBalance+ ADOQuery2.FieldByName('收入本币金额').AsFloat-
         ADOQuery2.FieldByName('支出本币金额').AsFloat;
    ForeignCurrencyBalance := 0;
    ForeignCurrencyBalance := ForeignCurrencyBalance+ ADOQuery2.FieldByName('收入原币金额').AsFloat-
         ADOQuery2.FieldByName('支出原币金额').AsFloat;
    ADOQuery2.Edit;
    ADOQuery2.FieldByName('收入本币金额').Value :=Null ;
    ADOQuery2.FieldByName('支出本币金额').Value :=Null ;
    ADOQuery2.FieldByName('收入原币金额').Value :=Null ;
    ADOQuery2.FieldByName('支出原币金额').Value :=Null ;
    while not ADOQuery2.Eof do
    begin
      ADOQuery2.Edit;
      ADOQuery2.FieldByName('本币余额').AsFloat :=LocalCurrencyBalance;
      ADOQuery2.FieldByName('原币余额').AsFloat :=ForeignCurrencyBalance;
      ADOQuery2.Next;

      LocalCurrencyBalance := LocalCurrencyBalance+ ADOQuery2.FieldByName('收入本币金额').AsFloat-
         ADOQuery2.FieldByName('支出本币金额').AsFloat;
      ForeignCurrencyBalance := ForeignCurrencyBalance+ ADOQuery2.FieldByName('收入原币金额').AsFloat-
         ADOQuery2.FieldByName('支出原币金额').AsFloat;
    end;

    AccountListQry.Close;
    AccountListQry.SQL.Text :=' select  '
      +' [日期],[编号],[业务类别],[收入本币金额],[收入汇率],'
      +' [收入原币金额],[支出本币金额],[支出汇率],[支出原币金额],'
      +' [本币余额],[结存汇率],[原币余额],[备注]  '
      +' from  #ClientAccountList order by [ID] ';
    AccountListQry.open;

    {adsMaster.Close;
    adsMaster.CommandText :=' select  '
      +' [日期],[编号],[业务类别],[收入本币金额],[收入汇率],'
      +' [收入原币金额],[支出本币金额],[支出汇率],[支出原币金额],'
      +' [本币余额],[结存汇率],[原币余额],[备注]  '
      +' from  #ClientAccountList order by [ID] ';
    adsMaster.open;  }

    DataSource1.DataSet := AccountListQry;
    DBGrid1.DataSource :=  DataSource1;
//    DBGrid1.Visible :=True;

    if Guarder.ExportCashACReckoningFlag='是' then
    begin
      ExportDBGridToExcel(DBGrid1, GetKeyState(VK_SHIFT) and $80000 = $80000,
        '外币帐户对帐单','币种：'+ADOQuery.FieldByName('币种名称').AsString
         , '帐户名称：'+ADOQuery.FieldByName('帐户名称').AsString);
    end;

    if (Guarder.PrintCashACReckoningFlag='是') and (Guarder.ExportCashACReckoningFlag<>'是') then
    begin
      with TQLDBGridReportBuilder.Create(Self) do
      try
        DBGrid := DBGrid1;
        AutoWidth := True;
        Report := TSLCheckReckoningReport.Create(Self);
        TSLCheckReckoningReport(Report).qrdbtClientName.DataSet := ADOQuery;
        TSLCheckReckoningReport(Report).qrlCheckCourse.Caption :=
          '对帐期间: ' + DateToStr(DateTimePicker1.Date) + '至' +
          DateToStr(DateTimePicker2.Date);
        SummaryFields.Add('本币收入金额=SUM([本币收入金额])');
        SummaryFields.Add('本币支出金额=SUM([本币支出金额])');
        SummaryFields.Add('原币收入金额=SUM([原币收入金额])');
        SummaryFields.Add('原币支出金额=SUM([原币支出金额])');
        SummaryFields.Add('本币余额='+floattostr(LocalCurrencyBalance/2));
        SummaryFields.Add('原币余额='+floattostr(ForeignCurrencyBalance/2));
        AutoOrientation := False;
        Active := True;
      if I<1 then Report.PreviewModal
        else  Report.Print;
      finally
        Free;
      end;
    end;
    ADOQuery.Next;
    I :=I+1;
  end;

end;

procedure TFNCashExchStatisticForm.ListClientSaleActExecute(Sender: TObject);
var I:Integer;
  Goods :string;
begin
  for I:=0 to adsMaster.FieldCount-1 do
  begin
    if Pos('帐户名称',adsMaster.Fields[I].FieldName)>0 then
    begin
      Goods := adsMaster.FieldByName('帐户名称').AsString;
      if Trim(Goods)='' then Exit;
      adsMaster.Close;
      adsMaster.CommandText :='select * from #ExpenseList  where  [帐户名称]='+Quotedstr(Goods);
      adsMaster.Open;
      UpdateDBGrid;
      Exit;
    end;
  end;
end;

procedure TFNCashExchStatisticForm.ListGoodsSaleActExecute(Sender: TObject);
var I:Integer;
  Goods :string;
begin
  for I:=0 to adsMaster.FieldCount-1 do
  begin
    if Pos('币种名称',adsMaster.Fields[I].FieldName)>0 then
    begin
      Goods := adsMaster.FieldByName('币种名称').AsString;
      if Trim(Goods)='' then Exit;
      adsMaster.Close;
      adsMaster.CommandText :='select * from #ExpenseList  where  [币种名称]='
        +Quotedstr(Goods)+' order by [日期]';
      adsMaster.Open;
      UpdateDBGrid;
      Exit;
    end;
  end;
end;

procedure TFNCashExchStatisticForm.adsMasterAfterOpen(DataSet: TDataSet);
begin
  inherited;
  RefreshAvailableFields;
end;

end.