pm_qry_analyzerpoenteritem.pas

一个MRPII系统源代码版本

unit Pm_Qry_AnalyzerPoEnterItem;

Interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Base_Qry, Db, Menus, ExtPrintReport, ActnList, AdODB, Grids, DBGridEh,
  StdCtrls, ExtCtrls, ComCtrls, ToolWin, DBCtrls, jpeg;

Type
  TFrm_Pm_Qry_AnalyzerPoEnterItem = Class(TFrm_Base_Qry)
    AdoQry_MainItemCode: TStringField;
    AdoQry_MainItemName: TStringField;
    AdoQry_MainUomName: TStringField;
    AdoQry_Mainordinal: TIntegerField;
    AdoQry_MainDSDesigner1: TBCDField;
    AdoQry_MainDSDesigner2: TBCDField;
    AdoQry_MainDSDesigner3: TBCDField;
    AdoQry_MainDSDesigner4: TBCDField;
    AdoQry_MainDSDesigner5: TBCDField;
    AdoQry_MainDSDesigner6: TBCDField;
    AdoQry_MainDSDesigner7: TBCDField;
    AdoQry_MainDSDesigner8: TBCDField;
    AdoQry_MainDSDesigner9: TBCDField;
    AdoQry_MainDSDesigner10: TBCDField;
    AdoQry_MainDSDesigner11: TBCDField;
    AdoQry_MainDSDesigner12: TBCDField;
    AdoQry_MainYearCount: TBCDField;
    AdoQry_MainItemflag: TStringField;
    Label3: TLabel;
    dbtxtfYearCount: TDBText;
    dbtxtItemflag: TDBText;
    Label1: TLabel;
    procedure FormDestroy(Sender: TObject);
    procedure DBGridEhGetCellParams(Sender: TObject; Column: TColumnEh;
      AFont: TFont; var Background: TColor; State: TGridDrawState);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure Act_LookExecute(Sender: TObject);
  private
    { Private declarations }
  public
    procedure InitForm(AdOConnection:TAdOConnection;ShowExtendColumn:Boolean);Override;
    { Public declarations }
  end;

var
  Frm_Pm_Qry_AnalyzerPoEnterItem: TFrm_Pm_Qry_AnalyzerPoEnterItem;

implementation
uses Pm_Qry_AnalyzerPoEnterItem_C,Pm_Qry_AnalyzerPoEnterItem_D;
{$R *.DFM}

{ TFrm_Pm_Qry_AnalyzerPoEnterItem }

procedure TFrm_Pm_Qry_AnalyzerPoEnterItem.InitForm(
  AdOConnection: TAdOConnection; ShowExtendColumn: Boolean);
var sqltext1,sqltext2,sqltext3,sqltext4,sqltext5:string;
     Year:string;
begin
Application.ProcessMessages;
  inherited;
//Pricefields:='5月,';
  with TFrm_Pm_Qry_AnalyzerPoEnterItem_C.Create(self) do
    begin
      showmodal;
      if modalResult<>mrok then exit;
       Year:=edit1.Text;
    end;
  sqltext1:=' select t1.InvBilldate,     '
             +'       t1.InvBillMonth,    '
             +'       t2.InvBillid,       '
             +'       t2.InvBilllineno,    '
             +'       t2.ItemCode,         '
             +'       t2.InvBillqty,       '
             +'       t2.InvBillPrice,     '
             +'       t2.InvBillAmount      '
             +' into #tmpInvBill           '
             +' from InvInBill t1(nolock),InvInBillline t2(nolock)   '
             +' where t1.InvBillid=t2.InvBillid   ' 
             +'  and  t1.InvBillStkchck=1'
             +'  and  t1.realBillflag=1';
 sqltext2:='select ItemCode,       '
            +'         InvBillMonth,   '
            +'         Monthqty=sum(isnull(InvBillqty,0)),  '
            +'           MonthAmount=sum(InvBillqty*InvBillPrice), '
            +'         MonthPrice=case sum(isnull(InvBillqty,0))    '
            +'                     when 0 then null                '
            +'                    else sum(InvBillqty*InvBillPrice)/sum(isnull(InvBillqty,0))  '
            +'                    end      '
            +' into #tmpItemBill       '
            +'  from #tmpInvBill       '
            +' group by ItemCode,InvBillMonth   '
           +'  Order by ItemCode    ';
 sqltext3:='select ItemCode,ItemName,Itemflag=ItemCode+'' ''+ItemName,UomName,ordinal=0,YearCount=convert(float,0.0)    '
          +'       into #tmpItem                                       '
         +'   from Item,Uom                                           '
          +'  where Item.UomCode*=Uom.UomCode                           '
          +'      and  exists(select * from InvInBillline where Item.ItemCode=InvInBillline.ItemCode)  '
          +'   insert into #tmpItem                                     '
          +'     select ItemCode,ItemName,Itemflag=ItemCode+'' ''+ItemName,UomName,ordinal=1,YearCount=convert(float,0.0) '
          +'        from Item,Uom                                       '
          +'     where Item.UomCode=Uom.UomCode                         '
          +'      and  exists(select * from InvInBillline where Item.ItemCode=InvInBillline.ItemCode)'
          +'    insert into #tmpItem                                    '
          +'     select ItemCode,ItemName,Itemflag=ItemCode+'' ''+ItemName,UomName,ordinal=2,YearCount=convert(float,0.0) '
          +'       from Item,Uom                                        '
          +'    where Item.UomCode=Uom.UomCode                          '
          +'       and  exists(select * from InvInBillline where Item.ItemCode=InvInBillline.ItemCode) ';
 sqltext4:='declAre @Count tinyint,          '
           +#13+'         @s     varchAr(200),    '
           +#13+'         @update varchAr(6),     '
           +#13+'         @updates1 varchAr(400), '
           +#13+'         @updates2 varchAr(400), '
           +#13+'         @updates3 varchAr(400)  '
           +#13+'     select @Count=1             '
           +#13+'     while @Count<13             '
           +#13+'      begin                      '
           +#13+' select @s=''alter table #tmpItem Add ''+''[''+convert(varchAr,@Count)+''月] float''    '
           +#13+' select @update=''[''+convert(varchAr,@Count)+''月]''                                 '
           +#13+' select @updates1=''update #tmpItem set ''+@update+''=#tmpItemBill.Monthqty from #tmpItem,#tmpItemBill where substring(#tmpItemBill.InvBillMonth,1,4)='''+''''+Year+''''+''' and #tmpItemBill.ItemCode=#tmpItem.ItemCode and convert(int,substring(InvBillMonth,6,2))=''+convert(varchAr,@Count)'
           +#13+' select @updates1=@updates1+'' and ordinal=0'''
           +#13+' select @updates1   '
           +#13+' select @updates2=''update #tmpItem set ''+@update+''=#tmpItemBill.MonthPrice from #tmpItem,#tmpItemBill where substring(#tmpItemBill.InvBillMonth,1,4)='''+''''+Year+''''+''' and #tmpItemBill.ItemCode=#tmpItem.ItemCode and convert(int,substring(InvBillMonth,6,2))=''+convert(varchAr,@Count)   '
           +#13+' select @updates2=@updates2+''and ordinal=1'''
           +#13+' select @updates3=''update #tmpItem set ''+@update+''=#tmpItemBill.MonthAmount from #tmpItem,#tmpItemBill where substring(#tmpItemBill.InvBillMonth,1,4)='''+''''+Year+''''+''' and #tmpItemBill.ItemCode=#tmpItem.ItemCode and convert(int,substring(InvBillMonth,6,2))=''+convert(varchAr,@Count)  '
           +#13+' select @updates3=@updates3+''and ordinal=2'''
           +#13+'      exec(@s)                '
           +#13+'      exec(@updates1)         '
           +#13+'      exec(@updates2)         '
           +#13+'      exec(@updates3)         '
           +#13+'      select @Count=@Count+1  '
           +#13+'       end                    ';
 sqltext5:=' declAre @cc int,        '
           +#13+'          @ss varchAr(300)    '
           +#13+'         select @cc=1  '
           +#13+'    while @cc<13   '
           +#13+'    begin    '
           +#13+'      select @ss='' update #tmpItem set YearCount=YearCount+''+''isnull([''+convert(varchAr,@cc)+''月],0) where ordinal<>1''   '
           +#13+'      exec(@ss)     '
           +#13+'      select @cc=@cc+1   '
           +#13+'      end       '
           +#13+'select @ss='' update  t1 set t1.YearCount=case t2.YearCount when 0 then null else t3.YearCount/t2.YearCount end '
           +#13+'from #tmpItem t1,#tmpItem t2,#tmpItem t3  where  t1.ItemCode=t2.ItemCode and t1.ItemCode=t3.ItemCode and t3.ordinal=2 and t2.ordinal=0 and t1.ordinal=1'''
           +#13+'      exec(@ss)        ';
selectfromsql:='select * from #tmpItem ';
 with AdoQry_Main do
   begin
     Close;
     sql.clear;
     sql.Add(sqltext1);
     Prepared;
     try
     execsql;
     except
     end;

     Close;
     sql.clear;
     sql.Add(sqltext2);
     Prepared;
     try
     execsql;
     except
     end;

     Close;
     sql.clear;
     sql.Add(sqltext3);
     Prepared;
     try
     execsql;
     except
     end;

     Close;
     sql.clear;
     sql.Add(sqltext4);
     Prepared;
    // showmessage(sqltext4);
     try
     execsql;
     except
     end;

     Close;
     sql.clear;
     sql.Add(sqltext5);
     Prepared;
 //    showmessage(sqltext5);
     try
     execsql;
   //  showmessage(sql.text);
     except
     end;

     Close;
     sql.clear;
     sql.Add('select * from #tmpItem Order by ItemCode,ordinal');
     Prepared;
     try
     open;
     except
     end;

   end;

end;

procedure TFrm_Pm_Qry_AnalyzerPoEnterItem.FormDestroy(Sender: TObject);
begin
  inherited;
      Frm_Pm_Qry_AnalyzerPoEnterItem:=nil;
end;


procedure TFrm_Pm_Qry_AnalyzerPoEnterItem.DBGridEhGetCellParams(
  Sender: TObject; Column: TColumnEh; AFont: TFont; var Background: TColor;
  State: TGridDrawState);
begin
  inherited;
   if AdoQry_Main.fieldbyname('ordinal').asinteger=0 then
        begin
        Background:=$ffccee;
        afont.Color:=clblack;
       end;
end;

procedure TFrm_Pm_Qry_AnalyzerPoEnterItem.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
  inherited;
  with AdoQry_tmp do
    begin
     Close;
     sql.clear;
     sql.Add('drop table #tmpItem,#tmpItemBill,#tmpInvBill');
     try
     execsql;
     except
     end;
    end;
end;

procedure TFrm_Pm_Qry_AnalyzerPoEnterItem.Act_LookExecute(Sender: TObject);
begin
  inherited;
try
with TFrm_Pm_Qry_AnalyzerPoEnterItem_D.Create(self) do
  begin
    edit1.Text:=AdoQry_Main.fieldbyname('Itemflag').asstring;
    edit2.Text:=AdoQry_Main.fieldbyname('UomName').asstring;
     case AdoQry_Main.fieldbyname('ordinal').asinteger of
      0: edit3.Text:='数量';
      1: edit3.Text:='价格';
      2: edit3.Text:='金额';
     end;
    edit4.Text:=floattostr(AdoQry_Main.fieldbyname('YearCount').asfloat);
    edit5.Text:=floattostr(AdoQry_Main.fieldbyname('1月').asfloat);
    edit6.Text:=floattostr(AdoQry_Main.fieldbyname('2月').asfloat);
    edit7.Text:=floattostr(AdoQry_Main.fieldbyname('3月').asfloat);
    edit8.Text:=floattostr(AdoQry_Main.fieldbyname('4月').asfloat);
    edit9.Text:=floattostr(AdoQry_Main.fieldbyname('5月').asfloat);
    edit10.Text:=floattostr(AdoQry_Main.fieldbyname('6月').asfloat);
    edit11.Text:=floattostr(AdoQry_Main.fieldbyname('7月').asfloat);
    edit12.Text:=floattostr(AdoQry_Main.fieldbyname('8月').asfloat);
    edit13.Text:=floattostr(AdoQry_Main.fieldbyname('9月').asfloat);
    edit14.Text:=floattostr(AdoQry_Main.fieldbyname('10月').asfloat);
    edit15.Text:=floattostr(AdoQry_Main.fieldbyname('11月').asfloat);
    edit16.Text:=floattostr(AdoQry_Main.fieldbyname('12月').asfloat);
    showmodal;
  end;
finally
 Frm_Pm_Qry_AnalyzerPoEnterItem_D.Free;
end;
end;

end.