fnexpensesttc.pas

详细的ERP设计资料

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

procedure TFNExpenseSttcForm.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 TFNExpenseSttcForm.Button6Click(Sender: TObject);
begin
  inherited;
  Panel2.Enabled :=True;
  DBGrid.Enabled :=True;
  VipsetPanel.Visible :=False;
end;

procedure TFNExpenseSttcForm.Button5Click(Sender: TObject);
begin
  inherited;
  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('期初结转')+' as [业务类别],'
    +' [费用名称],sum(isnull([费用金额],0)), '
    +' sum(isnull([参考数量],0)) from #ExpenseList '
    +' where [日期] <'+Quotedstr(Datetostr(DateTimePicker1.Date))
    +' Group by [日期],[业务类别],[费用名称]';
  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);
  if Button1.Tag=0 then Button2Click(sender);
  if Button1.Tag=2 then Button7Click(sender);
end;

procedure TFNExpenseSttcForm.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 TFNExpenseSttcForm.adsMasterAfterOpen(DataSet: TDataSet);
begin
  inherited;
  RefreshAvailableFields;
end;

procedure TFNExpenseSttcForm.Button7Click(Sender: TObject);
var I :integer;
    SelectStr1 :string;
begin
  inherited;
  PeriodDataUpdate;
//  showmessage(Button7.Hint)  ;
  Panel6.Visible :=True;
  Panel6.Repaint;
  Button1.Tag:=2;
  if Trim(SelectStr) ='' then
    begin
      ExpSttcCheckListBox.Checked[4] :=true;
      ExpSttcCheckListBox.ItemIndex := 4;
      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+ Button7.hint
    +'  into #ExpenseListTtl from #ExpenseList0 '
    +WhereStr +' group by '+SelectStr1;
  ADOQuery.ExecSQL;

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

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

procedure TFNExpenseSttcForm.PeriodMonthRadioButtonClick(Sender: TObject);
begin
  inherited;
  PeriodMonthComboBox.Enabled :=PeriodMonthRadioButton.Checked;
  PeriodDaysComboBox.Enabled  :=not PeriodMonthRadioButton.Checked;
end;

procedure TFNExpenseSttcForm.PeriodDataUpdate;
var I :integer;
    SelectStr1,StrDate1,StrDate2,StrDate3,StrDate4,StrDate5,StrDate6,
    StrFeildName0,StrFeildName1,StrFeildName2,StrFeildName3,StrFeildName4,
    StrFeildName5,StrFeildName6 :string;
    year,month,day :word;
begin
  inherited;
  if PeriodMonthRadioButton.Checked then
  begin
    I:=strtoint(PeriodMonthComboBox.Text);
    DecodeDate(Date,year, month, day);
    EnCodeDate(year,month,1);
    StrDate1 :=Datetostr(EnCodeDate(year,month,1));
    StrFeildName1 :='['+Inttostr(year)+'年'+Inttostr(month)+'月]';
    month :=month-I;
    if month<0 then
    begin
      month :=12+month;
      year :=year-1;
    end;

    StrDate2 :=Datetostr(EnCodeDate(year,month,1));
    StrFeildName2 :='['+Inttostr(year)+'年'+Inttostr(month)+'月]';
    month :=month-I;
    if month<0 then
    begin
      month :=12+month;
      year :=year-1;
    end;

    StrDate3 :=Datetostr(EnCodeDate(year,month,1));
    StrFeildName3 :='['+Inttostr(year)+'年'+Inttostr(month)+'月]';
    month :=month-I;
    if month<0 then
    begin
      month :=12+month;
      year :=year-1;
    end;

    StrDate4 :=Datetostr(EnCodeDate(year,month,1));
    StrFeildName4 :='['+Inttostr(year)+'年'+Inttostr(month)+'月]';
    month :=month-I;
    if month<0 then
    begin
      month :=12+month;
      year :=year-1;
    end;

    StrDate5 :=Datetostr(EnCodeDate(year,month,1));
    StrFeildName5 :='['+Inttostr(year)+'年'+Inttostr(month)+'月]';
    month :=month-I;
    if month<0 then
    begin
      month :=12+month;
      year :=year-1;
    end;

    StrDate6 :=Datetostr(EnCodeDate(year,month,1));
    StrFeildName6 :='['+Inttostr(year)+'年'+Inttostr(month)+'月]';
    month :=month-I;
    if month<0 then
    begin
      month :=12+month;
      year :=year-1;
    end;
    StrFeildName0 :='[下月预计]';
  end else
  begin
    I:=strtoint(PeriodDaysComboBox.Text);
    StrDate1 :=Datetostr(Date-I);
    StrFeildName1 :='[小于等于'+Inttostr(I*1)+'天]';

    StrDate2 :=Datetostr(Date-I*2);
    StrFeildName2 :='[早于'+Inttostr(I*1)+'至'+Inttostr(I*2)+'天]';

    StrDate3 :=Datetostr(Date-I*3);
    StrFeildName3 :='[早于'+Inttostr(I*2)+'至'+Inttostr(I*3)+'天]';

    StrDate4 :=Datetostr(Date-I*4);
    StrFeildName4 :='[早于'+Inttostr(I*3)+'至'+Inttostr(I*4)+'天]';

    StrDate5 :=Datetostr(Date-I*5);
    StrFeildName5 :='[早于'+Inttostr(I*4)+'至'+Inttostr(I*5)+'天]';

    StrDate6 :=Datetostr(Date-I*6);
    StrFeildName6 :='[早于'+Inttostr(I*5)+'天]';
    StrFeildName0 :='[下期预计]';
  end;

  Panel6.Visible :=True;
  Panel6.Repaint;

  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.Close;
  ADOQuery.SQL.Text :=' select *  '
     +' ,[费用金额] as '+StrFeildName6
     +' ,[费用金额] as '+StrFeildName5
     +' ,[费用金额] as '+StrFeildName4
     +' ,[费用金额] as '+StrFeildName3
     +' ,[费用金额] as '+StrFeildName2
     +' ,[费用金额] as '+StrFeildName1
     +' ,[费用金额] as [费用合计] '
     +' ,[费用金额] as '+StrFeildName0
     +' into #ExpenseList0 from #ExpenseList '
     +' where 1=2 ' ;
  ADOQuery.ExecSQL;

  ADOQuery.Close;
  ADOQuery.SQL.Text :=' ALTER TABLE #ExpenseList0 ALTER COLUMN [参考数量] Float NULL  ';
  ADOQuery.ExecSQL;
  ADOQuery.Close;
  ADOQuery.SQL.Text :=' ALTER TABLE #ExpenseList0 ALTER COLUMN [费用金额] Float NULL  ';
  ADOQuery.ExecSQL;
  ADOQuery.Close;
  ADOQuery.SQL.Text :=' ALTER TABLE #ExpenseList0 ALTER COLUMN [费用合计] Float NULL  ';
  ADOQuery.ExecSQL;
  ADOQuery.Close;
  ADOQuery.SQL.Text :=' ALTER TABLE #ExpenseList0 ALTER COLUMN '+StrFeildName6+' Float NULL  ';
  ADOQuery.ExecSQL;
  ADOQuery.Close;
  ADOQuery.SQL.Text :=' ALTER TABLE #ExpenseList0 ALTER COLUMN '+StrFeildName5+' Float NULL  ';
  ADOQuery.ExecSQL;
  ADOQuery.Close;
  ADOQuery.SQL.Text :=' ALTER TABLE #ExpenseList0 ALTER COLUMN '+StrFeildName4+' Float NULL  ';
  ADOQuery.ExecSQL;
  ADOQuery.Close;
  ADOQuery.SQL.Text :=' ALTER TABLE #ExpenseList0 ALTER COLUMN '+StrFeildName3+' Float NULL  ';
  ADOQuery.ExecSQL;
  ADOQuery.Close;
  ADOQuery.SQL.Text :=' ALTER TABLE #ExpenseList0 ALTER COLUMN '+StrFeildName2+' Float NULL  ';
  ADOQuery.ExecSQL;
  ADOQuery.Close;
  ADOQuery.SQL.Text :=' ALTER TABLE #ExpenseList0 ALTER COLUMN '+StrFeildName1+' Float NULL  ';
  ADOQuery.ExecSQL;
  ADOQuery.Close;
  ADOQuery.SQL.Text :=' ALTER TABLE #ExpenseList0 ALTER COLUMN '+StrFeildName0+' Float NULL  ';
  ADOQuery.ExecSQL;

  { ADOQuery.Close;
  ADOQuery.SQL.Text:=' Truncate Table #ExpenseList0 ';
  ADOQuery.ExecSQL;
  ADOQuery.Close;
  ADOQuery.SQL.Text:=' ALTER TABLE ABC3 DROP COLUMN [费用金额] ';
  ADOQuery.ExecSQL;}

  ADOQuery.Close;
  ADOQuery.SQL.Text:=' Insert into #ExpenseList0 ( '
    +' [日期],[编号],[业务类别],[摘要],[费用名称],[受款人],                 '
    +' [经手人],[客户厂商],[资产名称],[项目名称],[部门名称],[地区名称],           '
    +' [资产类别],[项目类别],[付款帐户],[票据号码],[业务备注],[附注]               '
    +' , '+StrFeildName1
    +' ) select [日期],[编号],[业务类别],[摘要],[费用名称],[受款人],'
    +' [经手人],[客户厂商],[资产名称],[项目名称],[部门名称],[地区名称],'
    +' [资产类别],[项目类别],[付款帐户],[票据号码],[业务备注],[附注],[费用金额]'
    +' from #ExpenseList  '
    +' where [日期]>='+Quotedstr(StrDate1);
  ADOQuery.ExecSQL;

  ADOQuery.Close;
  ADOQuery.SQL.Text:=' Insert into #ExpenseList0 ( '
    +' [日期],[编号],[业务类别],[摘要],[费用名称],[受款人],                 '
    +' [经手人],[客户厂商],[资产名称],[项目名称],[部门名称],[地区名称],           '
    +' [资产类别],[项目类别],[付款帐户],[票据号码],[业务备注],[附注]               '
    +' , '+StrFeildName2
    +' ) select [日期],[编号],[业务类别],[摘要],[费用名称],[受款人],'
    +' [经手人],[客户厂商],[资产名称],[项目名称],[部门名称],[地区名称],'
    +' [资产类别],[项目类别],[付款帐户],[票据号码],[业务备注],[附注],[费用金额]'
    +' from #ExpenseList  '
    +' where [日期]>='+Quotedstr(StrDate2)
    +' and  [日期]<'+Quotedstr(StrDate1);
  ADOQuery.ExecSQL;

  ADOQuery.Close;
  ADOQuery.SQL.Text:=' Insert into #ExpenseList0 ( '
    +' [日期],[编号],[业务类别],[摘要],[费用名称],[受款人],                 '
    +' [经手人],[客户厂商],[资产名称],[项目名称],[部门名称],[地区名称],           '
    +' [资产类别],[项目类别],[付款帐户],[票据号码],[业务备注],[附注]               '
    +' , '+StrFeildName3
    +' ) select [日期],[编号],[业务类别],[摘要],[费用名称],[受款人],'
    +' [经手人],[客户厂商],[资产名称],[项目名称],[部门名称],[地区名称],'
    +' [资产类别],[项目类别],[付款帐户],[票据号码],[业务备注],[附注],[费用金额]'
    +' from #ExpenseList  '
    +' where [日期]>='+Quotedstr(StrDate3)
    +' and  [日期]<'+Quotedstr(StrDate2);
  ADOQuery.ExecSQL;

  ADOQuery.Close;
  ADOQuery.SQL.Text:=' Insert into #ExpenseList0 ( '
    +' [日期],[编号],[业务类别],[摘要],[费用名称],[受款人],                 '
    +' [经手人],[客户厂商],[资产名称],[项目名称],[部门名称],[地区名称],           '
    +' [资产类别],[项目类别],[付款帐户],[票据号码],[业务备注],[附注]               '
    +' , '+StrFeildName4
    +' ) select [日期],[编号],[业务类别],[摘要],[费用名称],[受款人],'
    +' [经手人],[客户厂商],[资产名称],[项目名称],[部门名称],[地区名称],'
    +' [资产类别],[项目类别],[付款帐户],[票据号码],[业务备注],[附注],[费用金额]'
    +' from #ExpenseList  '
    +' where [日期]>='+Quotedstr(StrDate4)
    +' and  [日期]<'+Quotedstr(StrDate3);
  ADOQuery.ExecSQL;

  ADOQuery.Close;
  ADOQuery.SQL.Text:=' Insert into #ExpenseList0 ( '
    +' [日期],[编号],[业务类别],[摘要],[费用名称],[受款人],                 '
    +' [经手人],[客户厂商],[资产名称],[项目名称],[部门名称],[地区名称],           '
    +' [资产类别],[项目类别],[付款帐户],[票据号码],[业务备注],[附注]               '
    +' , '+StrFeildName5
    +' ) select [日期],[编号],[业务类别],[摘要],[费用名称],[受款人],'
    +' [经手人],[客户厂商],[资产名称],[项目名称],[部门名称],[地区名称],'
    +' [资产类别],[项目类别],[付款帐户],[票据号码],[业务备注],[附注],[费用金额]'
    +' from #ExpenseList  '
    +' where [日期]>='+Quotedstr(StrDate5)
    +' and  [日期]<'+Quotedstr(StrDate4);
  ADOQuery.ExecSQL;

  ADOQuery.Close;
  ADOQuery.SQL.Text:=' Insert into #ExpenseList0 ( '
    +' [日期],[编号],[业务类别],[摘要],[费用名称],[受款人],                 '
    +' [经手人],[客户厂商],[资产名称],[项目名称],[部门名称],[地区名称],           '
    +' [资产类别],[项目类别],[付款帐户],[票据号码],[业务备注],[附注]               '
    +' , '+StrFeildName6
    +' ) select [日期],[编号],[业务类别],[摘要],[费用名称],[受款人],'
    +' [经手人],[客户厂商],[资产名称],[项目名称],[部门名称],[地区名称],'
    +' [资产类别],[项目类别],[付款帐户],[票据号码],[业务备注],[附注],[费用金额]'
    +' from #ExpenseList  '
    +' where [日期]<'+Quotedstr(StrDate5);
//    +' and  [日期]<'+Quotedstr(StrDate5);
  ADOQuery.ExecSQL;

  ADOQuery.Close;
  ADOQuery.SQL.Text:=' Update #ExpenseList0 set [费用合计]=( '
    +' Isnull('+StrFeildName6+',0) + '
    +' Isnull('+StrFeildName5+',0) + '
    +' Isnull('+StrFeildName4+',0) + '
    +' Isnull('+StrFeildName3+',0) + '
    +' Isnull('+StrFeildName2+',0) + '
    +' Isnull('+StrFeildName1+',0) ) '  ;
  ADOQuery.ExecSQL;
  Button7.Hint :='';
  Button7.Hint :=Button7.Hint+', '+'Sum(Isnull('+StrFeildName6+',0)) as '+StrFeildName6;
  Button7.Hint :=Button7.Hint+', '+'Sum(Isnull('+StrFeildName5+',0)) as '+StrFeildName5;
  Button7.Hint :=Button7.Hint+', '+'Sum(Isnull('+StrFeildName4+',0)) as '+StrFeildName4;
  Button7.Hint :=Button7.Hint+', '+'Sum(Isnull('+StrFeildName3+',0)) as '+StrFeildName3;
  Button7.Hint :=Button7.Hint+', '+'Sum(Isnull('+StrFeildName2+',0)) as '+StrFeildName2;
  Button7.Hint :=Button7.Hint+', '+'Sum(Isnull('+StrFeildName1+',0)) as '+StrFeildName1;
  Button7.Hint :=Button7.Hint+', '+'Sum(Isnull('+StrFeildName0+',0)) as '+StrFeildName0;
  Button7.Hint :=Button7.Hint+', '+'Sum(Isnull([费用合计],0)) as [费用合计]';
end;

end.