pcpurchaseforecast.pas

详细的ERP设计资料

  Button2Click(Sender);
end;

procedure TPCPurchaseForeCastForm.Button2Click(Sender: TObject);
var I,J :integer;
    ExcWhereStr,GroupStr:String;
begin
  inherited;
  Panel8.Visible :=true;
  Panel8.Repaint;
  I:=strtoint(ComboBox1.Text);
  if I<=0 then I :=7;

  ADOQuery.Close;
  ADOQuery.SQL.Text :=' TRUNCATE TABLE #PCPurchasePeriodDM';
  ADOQuery.ExecSQL;
//第一时段
  if RadioButton4.Checked then
     ExcWhereStr :=WhereStr + 'AND  DATEDIFF(Month,Date,'+Quotedstr(Datetostr(DateTimePicker1.date))+' )=5'
    else
     ExcWhereStr :=WhereStr + 'AND  DATEDIFF(day,Date,'+Quotedstr(Datetostr(DateTimePicker1.date))+' )>='
       +InttoStr(I*5)+' and '
       +' DATEDIFF(day,Date,'+Quotedstr(Datetostr(DateTimePicker1.date))+' )<'
       +InttoStr(I*6);
  ADOQuery.Close;  //
  ADOQuery.SQL.Text :=' INSERT INTO #PCPurchasePeriodDM ('
    +' [Client],[Employee],[Goods],'
    +' [QttA],[AmtA], [PftA])'
    +' SELECT [Client],[Employee],[Goods], '
    +' [GoalQuantity],[Amount],[QuantityPcs]'
    +' FROM #PCPurchaseDM'+ExcWhereStr;
  ADOQuery.ExecSQL;
//第二时段
  if RadioButton4.Checked then
     ExcWhereStr :=WhereStr + 'AND DATEDIFF(Month,Date,'+Quotedstr(Datetostr(DateTimePicker1.date))+' )=4'
    else
     ExcWhereStr :=WhereStr + 'AND DATEDIFF(day,Date,'+Quotedstr(Datetostr(DateTimePicker1.date))+' )>='
       +InttoStr(I*4)+' and '
       +' DATEDIFF(day,Date,'+Quotedstr(Datetostr(DateTimePicker1.date))+' )<'
       +InttoStr(I*5);
  ADOQuery.Close;  //
  ADOQuery.SQL.Text :=' INSERT INTO #PCPurchasePeriodDM ('
    +' [Client],[Employee],[Goods],'
    +' [QttB],[AmtB], [PftB])'
    +' SELECT [Client],[Employee],[Goods], '
    +' [GoalQuantity],[Amount],[QuantityPcs]'
    +' FROM #PCPurchaseDM'+ExcWhereStr;
  ADOQuery.ExecSQL;
//第三时段
  if RadioButton4.Checked then
     ExcWhereStr :=WhereStr + 'AND  DATEDIFF(Month,Date,'+Quotedstr(Datetostr(DateTimePicker1.date))+' )=3'
    else
     ExcWhereStr :=WhereStr + 'AND  DATEDIFF(day,Date,'+Quotedstr(Datetostr(DateTimePicker1.date))+' )>='
       +InttoStr(I*3)+' and '
       +' DATEDIFF(day,Date,'+Quotedstr(Datetostr(DateTimePicker1.date))+' )<'
       +InttoStr(I*4);
  ADOQuery.Close;  //
  ADOQuery.SQL.Text :=' INSERT INTO #PCPurchasePeriodDM ('
    +' [Client],[Employee],[Goods],'
    +' [QttC],[AmtC], [PftC])'
    +' SELECT [Client],[Employee],[Goods], '
    +' [GoalQuantity],[Amount],[QuantityPcs]'
    +' FROM #PCPurchaseDM'+ExcWhereStr;
  ADOQuery.ExecSQL;
//第四时段
  if RadioButton4.Checked then
     ExcWhereStr :=WhereStr + 'AND  DATEDIFF(Month,Date,'+Quotedstr(Datetostr(DateTimePicker1.date))+' )=2'
    else
     ExcWhereStr :=WhereStr + 'AND  DATEDIFF(day,Date,'+Quotedstr(Datetostr(DateTimePicker1.date))+' )>='
       +InttoStr(I*2)+' and '
       +' DATEDIFF(day,Date,'+Quotedstr(Datetostr(DateTimePicker1.date))+' )<'
       +InttoStr(I*3);
  ADOQuery.Close;  //
  ADOQuery.SQL.Text :=' INSERT INTO #PCPurchasePeriodDM ('
    +' [Client],[Employee],[Goods],'
    +' [QttD],[AmtD], [PftD] )'
    +' SELECT [Client],[Employee],[Goods], '
    +' [GoalQuantity],[Amount],[QuantityPcs]'
    +' FROM #PCPurchaseDM'+ExcWhereStr;
  ADOQuery.ExecSQL;
//第五时段
  if RadioButton4.Checked then
     ExcWhereStr :=WhereStr + 'AND  DATEDIFF(Month,Date,'+Quotedstr(Datetostr(DateTimePicker1.date))+' )=1'
    else
     ExcWhereStr :=WhereStr + 'AND  DATEDIFF(day,Date,'+Quotedstr(Datetostr(DateTimePicker1.date))+' )>='
       +InttoStr(I*1)+' and '
       +' DATEDIFF(day,Date,'+Quotedstr(Datetostr(DateTimePicker1.date))+' )<'
       +InttoStr(I*2);
  ADOQuery.Close;  //
  ADOQuery.SQL.Text :=' INSERT INTO #PCPurchasePeriodDM ('
    +' [Client],[Employee],[Goods],'
    +' [QttE],[AmtE], [PftE])'
    +' SELECT [Client],[Employee],[Goods], '
    +' [GoalQuantity],[Amount],[QuantityPcs]'
    +' FROM #PCPurchaseDM'+ExcWhereStr;
  ADOQuery.ExecSQL;
//第六时段
  if RadioButton4.Checked then
     ExcWhereStr :=WhereStr + 'AND  DATEDIFF(Month,Date,'+Quotedstr(Datetostr(DateTimePicker1.date))+' )=0'
    else
     ExcWhereStr :=WhereStr + 'AND  DATEDIFF(day,Date,'+Quotedstr(Datetostr(DateTimePicker1.date))+' )>='
       +InttoStr(I*0)+' and '
       +' DATEDIFF(day,Date,'+Quotedstr(Datetostr(DateTimePicker1.date))+' )<'
       +InttoStr(I*1);
  ADOQuery.Close;  //
  ADOQuery.SQL.Text :=' INSERT INTO #PCPurchasePeriodDM ('
    +' [Client],[Employee],[Goods],'
    +' [QttF],[AmtF], [PftF])'
    +' SELECT [Client],[Employee],[Goods], '
    +' [GoalQuantity],[Amount],[QuantityPcs]'
    +' FROM #PCPurchaseDM'+ExcWhereStr;
//  showmessage();
  ADOQuery.ExecSQL;

  ADOQuery.Close;  //
  ADOQuery.SQL.Text :=' Update #PCPurchasePeriodDM Set '
    +' [QttG]=(ISNULL(QttA,0)+ISNULL(QttB,0)+ISNULL(QttC,0)'
    +' +ISNULL(QttD,0)+ISNULL(QttE,0)+ISNULL(QttF,0))/6 , '
    +' [AmtG]=(ISNULL(AmtA,0)+ISNULL(AmtB,0)+ISNULL(AmtC,0)'
    +' +ISNULL(AmtD,0)+ISNULL(AmtE,0)+ISNULL(AmtF,0))/6' ;
  ADOQuery.ExecSQL;
  Panel8.Visible :=False;
  ShowReslut;
  J:=-1;
  with adsMaster do
  begin
    for I :=0 to FieldList.Count -1 do
    begin
      if Fields[i].DataType = TFieldType(ftFloat) then
        begin
          SetStrProp(Fields[I], 'DisplayFormat','#,#.00') ;
          Fields[i].DisplayWidth :=20;
          if J=-1 then J:=I ;
        end;
    end;
    if CheckBox4.Checked then Fields[J].Visible :=True else Fields[J].Visible :=False;
    if CheckBox4.Checked then Fields[J+3].Visible :=True else Fields[J+3].Visible :=False;
    if CheckBox4.Checked then Fields[J+6].Visible :=True else Fields[J+6].Visible :=False;
    if CheckBox4.Checked then Fields[J+9].Visible :=True else Fields[J+9].Visible :=False;
    if CheckBox4.Checked then Fields[J+12].Visible :=True else Fields[J+12].Visible :=False;
    if CheckBox4.Checked then Fields[J+15].Visible :=True else Fields[J+15].Visible :=False;
    if CheckBox4.Checked then Fields[J+18].Visible :=True else Fields[J+18].Visible :=False;

    if CheckBox5.Checked then Fields[J+1].Visible :=True else Fields[J+1].Visible :=False;
    if CheckBox5.Checked then Fields[J+4].Visible :=True else Fields[J+4].Visible :=False;
    if CheckBox5.Checked then Fields[J+7].Visible :=True else Fields[J+7].Visible :=False;
    if CheckBox5.Checked then Fields[J+10].Visible :=True else Fields[J+10].Visible :=False;
    if CheckBox5.Checked then Fields[J+13].Visible :=True else Fields[J+13].Visible :=False;
    if CheckBox5.Checked then Fields[J+16].Visible :=True else Fields[J+16].Visible :=False;
    if CheckBox5.Checked then Fields[J+19].Visible :=True else Fields[J+19].Visible :=False;

    Fields[J+2].Visible :=False;
    Fields[J+5].Visible :=False;
    Fields[J+8].Visible :=False;
    Fields[J+11].Visible :=False;
    Fields[J+14].Visible :=False;
    Fields[J+17].Visible :=False;
  end;
  UpdateDBGrid
end;

procedure TPCPurchaseForeCastForm.RadioGroup2Click(Sender: TObject);
begin
  inherited;
  ComboBox1.Enabled :=RadioButton5.Checked;
end;

procedure TPCPurchaseForeCastForm.RadioButton5Click(Sender: TObject);
begin
  inherited;
  ComboBox1.Enabled :=RadioButton5.Checked;
end;

procedure TPCPurchaseForeCastForm.CheckBox1Click(Sender: TObject);
begin
  inherited;
  if not CheckBox1.Checked and
     not CheckBox3.Checked and not CheckBox2.Checked  then
     CheckBox1.Checked:=not CheckBox1.Checked;
end;

procedure TPCPurchaseForeCastForm.FormShow(Sender: TObject);
begin
  inherited;
  DateTimePicker1.Date :=date;
  WhereStr :=' Where 1=1 ' ;
end;

procedure TPCPurchaseForeCastForm.ShowReslut;
var  SelectStr,SumStr,GroupStr:String;
begin
  GroupStr :='';
  SelectStr :='';
  if CheckBox1.Checked then
  begin
    SelectStr :=SelectStr+' Client [客户名称] ,';
    GroupStr :=GroupStr+' Client, ';
  end;
  if CheckBox2.Checked then
  begin
    SelectStr :=SelectStr+' Goods [商品名称] ,' ;
    GroupStr :=GroupStr+' Goods , '             ;
  end;
  if CheckBox3.Checked then
  begin
    SelectStr :=SelectStr+' Employee [业务经办],' ;
    GroupStr :=GroupStr+' Employee , '            ;
  end;
  IF  CheckBox7.Checked then
  begin
    if RadioButton4.Checked then  SumStr :=''
    +' sum(isnull(QttA,0)) [前五月(数量)],sum(isnull(AmtA,0)) [前五月(金额)], sum(isnull(PftA,0)) [前六月(毛利)],'
    +' sum(isnull(QttB,0)) [前四月(数量)],sum(isnull(AmtB,0)) [前四月(金额)], sum(isnull(PftB,0)) [前五月(毛利)],'
    +' sum(isnull(QttC,0)) [前三月(数量)],sum(isnull(AmtC,0)) [前三月(金额)], sum(isnull(PftC,0)) [前四月(毛利)],'
    +' sum(isnull(QttD,0)) [前二月(数量)],sum(isnull(AmtD,0)) [前二月(金额)], sum(isnull(PftD,0)) [前三月(毛利)],'
    +' sum(isnull(QttE,0)) [前一月(数量)],sum(isnull(AmtE,0)) [前一月(金额)], sum(isnull(PftE,0)) [前二月(毛利)],'
    +' sum(isnull(QttF,0)) [当  月(数量)],sum(isnull(AmtF,0)) [当  月(金额)], sum(isnull(PftF,0)) [当  月(毛利)],'
    +' sum(isnull(QttG,0)) [预计销售数量],sum(isnull(AmtG,0)) [预计销量金额],  '
    +' sum(isnull(QTTH,0)) [当前库存数量],sum(isnull(QTTI,0)) [订购结余数量],  '
    +' sum(isnull(QTTJ,0)) [订销结余数量], sum(isnull(QTTK,0)) [预计订货数量]'
    else   SumStr :=''
    +' sum(isnull(QttA,0)) [前五时段(数量)],sum(isnull(AmtA,0)) [前五时段(金额)], sum(isnull(PftA,0)) [前六时段(毛利)],'
    +' sum(isnull(QttB,0)) [前四时段(数量)],sum(isnull(AmtB,0)) [前四时段(金额)], sum(isnull(PftB,0)) [前五时段(毛利)],'
    +' sum(isnull(QttC,0)) [前三时段(数量)],sum(isnull(AmtC,0)) [前三时段(金额)], sum(isnull(PftC,0)) [前四时段(毛利)],'
    +' sum(isnull(QttD,0)) [前二时段(数量)],sum(isnull(AmtD,0)) [前二时段(金额)], sum(isnull(PftD,0)) [前三时段(毛利)],'
    +' sum(isnull(QttE,0)) [前一时段(数量)],sum(isnull(AmtE,0)) [前一时段(金额)], sum(isnull(PftE,0)) [前二时段(毛利)],'
    +' sum(isnull(QttF,0)) [当前时段(数量)],sum(isnull(AmtF,0)) [当前时段(金额)], sum(isnull(PftF,0)) [当  时段(毛利)],'
    +' sum(isnull(QttG,0)) [预计销售数量],sum(isnull(AmtG,0)) [预计销售金额],  '
    +' sum(isnull(QTTH,0)) [当前库存数量],sum(isnull(QTTI,0)) [订购结余数量],  '
    +' sum(isnull(QTTJ,0)) [订销结余数量], sum(isnull(QTTK,0)) [预计订货数量]'
  end
  else
  begin
  if RadioButton4.Checked then  SumStr :=''
  +' sum(isnull(QttA,0)) [前五月(数量)],sum(isnull(AmtA,0)) [前五月(金额)], sum(isnull(PftA,0)) [前五月(毛利)],'
  +' sum(isnull(QttB,0)) [前四月(数量)],sum(isnull(AmtB,0)) [前四月(金额)], sum(isnull(PftB,0)) [前四月(毛利)],'
  +' sum(isnull(QttC,0)) [前三月(数量)],sum(isnull(AmtC,0)) [前三月(金额)], sum(isnull(PftC,0)) [前三月(毛利)],'
  +' sum(isnull(QttD,0)) [前二月(数量)],sum(isnull(AmtD,0)) [前二月(金额)], sum(isnull(PftD,0)) [前二月(毛利)],'
  +' sum(isnull(QttE,0)) [前一月(数量)],sum(isnull(AmtE,0)) [前一月(金额)], sum(isnull(PftE,0)) [前一月(毛利)],'
  +' sum(isnull(QttF,0)) [当  月(数量)],sum(isnull(AmtF,0)) [当  月(金额)], sum(isnull(PftF,0)) [当  月(毛利)],'
  +' sum(isnull(QttG,0)) [预计销售数量],sum(isnull(AmtG,0)) [预计销售金额]'
  else SumStr :=''
  +' sum(isnull(QttA,0)) [前五时段(数量)],sum(isnull(AmtA,0)) [前五时段(金额)], sum(isnull(PftA,0)) [前五时段(毛利)],'
  +' sum(isnull(QttB,0)) [前四时段(数量)],sum(isnull(AmtB,0)) [前四时段(金额)], sum(isnull(PftB,0)) [前四时段(毛利)],'
  +' sum(isnull(QttC,0)) [前三时段(数量)],sum(isnull(AmtC,0)) [前三时段(金额)], sum(isnull(PftC,0)) [前三时段(毛利)],'
  +' sum(isnull(QttD,0)) [前二时段(数量)],sum(isnull(AmtD,0)) [前二时段(金额)], sum(isnull(PftD,0)) [前二时段(毛利)],'
  +' sum(isnull(QttE,0)) [前一时段(数量)],sum(isnull(AmtE,0)) [前一时段(金额)], sum(isnull(PftE,0)) [前一时段(毛利)],'
  +' sum(isnull(QttF,0)) [当前时段(数量)],sum(isnull(AmtF,0)) [当前时段(金额)], sum(isnull(PftF,0)) [当  月(毛利)]  ,'
  +' sum(isnull(QttG,0)) [预计销售数量],sum(isnull(AmtG,0)) [预计销售金额]'
  end;
  adsMaster.Close;
  adsMaster.CommandText:=' select  ' +SelectStr +SumStr
     +' from #PCPurchasePeriodDM  Where 1=1 '
     +' group by '+Copy( Trim(GroupStr),1,Length(Trim(GroupStr))-1 );
  adsMaster.open;
end;

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

procedure TPCPurchaseForeCastForm.CheckBox4Click(Sender: TObject);
begin
  inherited;
  if not CheckBox4.Checked and
     not CheckBox5.Checked and not CheckBox6.Checked  then
     CheckBox4.Checked:=not CheckBox4.Checked;
end;

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

end.