mrp_qry_analyzermo5_c.pas

一个MRPII系统源代码版本

unit Mrp_Qry_AnalyzerMo5_C;
  
Interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Base_Condition, Db, AdODB, StdCtrls, Mask, ComCtrls;

Type
  TFrm_Mrp_Qry_AnalyzerMo5_C = Class(TFrm_Base_Condition)
    Label1: TLabel;
    Label2: TLabel;
    Edt_ItemCodeStArt: TEdit;
    Edt_ItemCodeend: TEdit;
    Label3: TLabel;
    Label4: TLabel;
    Mak_OrderDateStArt: TMaskEdit;
    Mak_OrderDateend: TMaskEdit;
    ProgressBar1: TProgressBar;
    Label5: TLabel;
    ComboBox1: TComboBox;
    procedure btn_okClick(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure Edt_ItemCodeStArtKeyDown(Sender: TObject; var Key: Word;
      Shift: TShiftState);
    procedure Edt_ItemCodeendKeyDown(Sender: TObject; var Key: Word;
      Shift: TShiftState);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Frm_Mrp_Qry_AnalyzerMo5_C: TFrm_Mrp_Qry_AnalyzerMo5_C;

implementation

uses Mrp_Qry_AnalyzerMo5, Sys_Global;

{$R *.DFM}

procedure TFrm_Mrp_Qry_AnalyzerMo5_C.btn_okClick(Sender: TObject);
var
  SqlText,SqlText1:String;
  AdoQuery:TAdoQuery;
  I :integer;
  MoLineStatus1,MoLineStatus2 :String;
begin
  inherited;
  Mrp_Qry_AnalyzerMo5.Datestate := Mak_OrderDateStArt.Text;
  Mrp_Qry_AnalyzerMo5.Dateend := Mak_OrderDateend.Text;
  try
    ProgressBar1.Position:=0;
    SqlText:=' Drop Table #Tmp7,#TmpInvOutBillLine,#TmPMoLine,#TmpOpenBomLast,#TmPmdlOpenBom,#TmpOpenBom,#TmpComponent' ;
     ExecuteSql(AdoQry_tmp,SqlText,1);
  Except

  end;
  case ComboBox1.ItemIndex  of
    0:begin
        MoLineStatus1:=' ' ;
        MoLineStatus2:=' ';
      end;
    1: begin
         MoLineStatus1:=' And  MoLine.MoLineStatus=6' ;
         MoLineStatus2:=' And  PoLine.PolineStatus=6' ;
       end;
    2:begin
        MoLineStatus1:=' And  MoLine.MoLineStatus=7' ;
        MoLineStatus2:=' And  poLine.polineStatus=7' ;
      end;
  end;


// 找出符合条件的poLine,MoLine
  Sqltext:=' Select MoLine.MoNo,MoLine.MoLineNo,MoLine.ItemCode,MoRealInQty, falg=0 Into #TmPMoLine  From MoLine '
          +' Join Mo '
          +' On MoLine.MoNo=Mo.MoNo '
          +' and MoLine.ItemCode Between '''+Edt_ItemCodeStArt.Text +''' And '''+Edt_ItemCodeend.Text +''' '
          +'                             And MoLine.MoLineDate Between '''+Mak_OrderDateStArt.Text +''' And '''+Mak_OrderDateend.Text+''' '
         // +'                             And Mo.MoNo Between '''+Edt_MoLineNoStatus.Text +''' And '''+Edt_MoLineNoend.Text+''' '
          +''+MoLineStatus1+' '
          +' union '
          +' Select PoLine.PoNo,PoLine.PolineNo,PoLine.ItemCode,PoRealInQty,falg=1  from PoLine '
          +'                     Join Po '
          +'                     On PoLine.PoNo=Po.PoNo '
          +' And PoLine.ItemCode Between '''+Edt_ItemCodeStArt.Text +''' And '''+Edt_ItemCodeend.Text +''' '
          +'                     And Poline.PolineDate Between '''+Mak_OrderDateStArt.Text +''' And '''+Mak_OrderDateend.Text+''' '
        //  +'                     And Po.PoNo Between '''+Edt_MoLineNoStatus.Text +''' And '''+Edt_MoLineNoend.Text+''' '
          +''+MoLineStatus2+' '
          +'                     And PoType=3';
  ExecuteSql(AdoQry_tmp,SqlText,1);
//从-InvOutBillLine-找出所有的-mo ,po 出库-------
  SqlText:=' Select InvOutBillLine.MoNo,InvOutBillLine.MoLineNo,InvOutBillLine.ItemCode,InvOutBill.InvBillStkChck,InvOutBillLine.InvBillQty,InvOutBillLine.InvBillPrice,InvOutBill.OverPlan '
          +'                      Into #TmpInvOutBillLine '
          +'                      From InvOutBillLine '
          +'                           Join InvOutBill On InvOutBillLine.InvBillId=InvOutBill.InvBillId'
          +'                          where InvOutBill.BillTypeCode=''0201'''
          +' union '
          +' Select InvOutBillLine.PoNo,InvOutBillLine.PoLineNo,InvOutBillLine.ItemCode,InvOutBill.InvBillStkChck,InvOutBillLine.InvBillQty,InvOutBillLine.InvBillPrice,InvOutBill.OverPlan '
          +'                      From InvOutBillLine '
          +'                           Join InvOutBill On InvOutBillLine.InvBillId=InvOutBill.InvBillId'
          +'                          where  InvOutBill.BillTypeCode=''0202''';
  ExecuteSql(AdoQry_tmp,SqlText,1);
//
 { SqlText:=' Select * Into #TmPMoLineLast From #TmPMoLine Where Not Exists(Select * From #TmpInvOutBillLine '
          +'                                          where #TmpInvOutBillLine.InvBillStkChck=0 '
          +'                                          And #TmPMoLine.Mono=#TmpInvOutBillLine.MoNo '
          +'                                          And #TmPMoLine.MoLineNo=#TmpInvOutBillLine.MoLineNo)'
          +'                             And Exists(Select * from #TmpInvOutBillLine '
          +'                                          Where #TmpInvOutBillLine.InvBillStkChck=1 '
          +'                                          And #TmPMoLine.MoNo=#TmpInvOutBillLine.MoNo '
          +'                                          And #TmPMoLine.MoLineNo=#TmpInvOutBillLine.MoLineNo) ';

  ExecuteSql(AdoQry_tmp,SqlText,1);}
//
  SqlText:=' Create Table #TmPmdlOpenBom(Ite_ItemCode varChAr(16),ItemCode varChAr(16),Bomqty float,BomScrAprate float,isvir tinyint) '
          +' Create Table #TmpOpenBom(Ite_ItemCode varChAr(16),ItemCode varChAr(16),Bomqty float,BomScrAprate float) '
          +' Create Table #TmpComponent(Ite_ItemCode varChAr(16),ItemCode varChAr(16),Bomqty float,BomScrAprate float,isvir tinyint) ';

  ExecuteSql(AdoQry_tmp,SqlText,1);

  SqlText:=' select Ite_ItemCode,ItemCode,BomQty,BomItemType,BomScrAp_Percent From Bom '
          +' Where  exists(Select Distinct ItemCode From #TmPMoLine Where #TmPMoLine.ItemCode=Bom.Ite_ItemCode)' ;
  AdoQry_tmp.Close;
  AdoQry_tmp.SQL.clear;
  AdoQry_tmp.SQL.Text := SqlText;
  AdoQry_tmp.Open ;
  ProgressBar1.Max := AdoQry_Tmp.RecordCount;
  I:=0;
  AdoQuery:=TAdoQuery.Create(self);
  AdoQuery.EnableBCD := False;
  AdoQuery.Connection :=DBconnect;
  While not AdoQry_Tmp.Eof  do
  begin
    SqlText:=' insert #TmpComponent  (Ite_ItemCode,ItemCode,Bomqty,BomScrAprate,isvir) '
            +' Values('''+AdoQry_Tmp.fieldbyname('Ite_ItemCode').Asstring+''',  '
            +''''+AdoQry_Tmp.fieldbyname('ItemCode').Asstring+''','
            +''''+AdoQry_Tmp.fieldbyname('BomQty').Asstring+''','
            +''''+AdoQry_Tmp.fieldbyname('BomScrAp_Percent').Asstring+''','
            +''''+AdoQry_Tmp.fieldbyname('BomItemType').Asstring+''') '

            +' while Exists(select * from #TmpComponent) '
            +' begin '
            +'   Delete From #TmPmdlOpenBom '

            +'    Insert #TmPmdlOpenBom '
            +'      select Bom.Ite_ItemCode,Bom.ItemCode, '
          //  +'      Bomqty=Bom.Bomqty*(#tmpComponent.Bomqty+#tmpComponent.BomScrAprate/100)+Bom.BomScrAp_Percent/100, '
            +'  Bomqty=#tmpComponent.Bomqty*(1+#tmpComponent.BomScrAprate/100)*Bom.Bomqty*(1+Bom.BomScrAp_Percent/100), '
            +'        BomScrAprate=0 ,'
            +'   BomItemType '
            +'       From Bom ,#tmpComponent '
            +'           where #TmpComponent.isvir=3 and '
            +'            Bom.Ite_ItemCode=#TmpComponent.ItemCode '

            +'   insert #TmpOpenBom '
            +'        select '''+AdoQry_Tmp.fieldbyname('Ite_ItemCode').Asstring+''','
          //  +' ItemCode,Bomqty=#tmpComponent.Bomqty+#tmpComponent.BomScrAprate/100,BomScrAprate'
            +' ItemCode,Bomqty=#tmpComponent.Bomqty*(1+#tmpComponent.BomScrAprate/100),0 '
            +'   from  #tmpComponent '
            +'                 where #tmpComponent.isvir<>3 '

            +'       Delete From #TmpComponent '
            +'                       Insert #TmpComponent '
            +'                          Select Ite_ItemCode,ItemCode,Bomqty,BomScrAprate,isvir From #TmPmdlOpenBom '
            +'  end ';
    AdoQuery.Close;
    AdoQuery.SQL.clear;
    AdoQuery.SQL.Text:=SqlText;
    AdoQuery.ExecSQL;

    AdoQry_Tmp.Next;
    I:=I+1;
    ProgressBar1.Position :=I;
  end;
//----------
  SqlText:=' Select Ite_ItemCode,ItemCode,Sum(ISnull(BomQty,0)) As BomQty Into #TmpOpenBomLast  from #TmpOpenBom '
          +'  Group By Ite_ItemCode,ItemCode ';
  ExecuteSql(AdoQry_tmp,SqlText,1);
//-----
  SqlText:=' Select ItemCode As Ite_ItemCode,Sum(IsNull(MoRealInQty,0)) As MoQty From #TmPMoLine'
          +'       Group By ItemCode';
  ExecuteSql(AdoQry_tmp,SqlText,1);
//-----


   SqlText1:=' Select Tmp6.Ite_ItemCode,Tmp6.ItemCode,Tmp6.MoQty,Tmp6.RationQty,Tmp6.OverAgeQty,Tmp6.Sjdg,Tmp6.Bzdg,'
            +' Case When IsNull(Tmp6.Bzdg,0)<>0 Then Convert(varChAr,round((Tmp6.Sjdg-Tmp6.Bzdg)/Tmp6.Bzdg*100,1))+''%'''
            +' else ''0%'''
            +' end dgbl into #tmp7'
            +' From'
            +' (Select Tmp5.Ite_ItemCode,Tmp5.ItemCode,convert(float,Tmp5.MoQty) as MoQty,convert(float,Tmp5.RationQty) as RationQty,convert(float,Tmp5.OverAgeQty) as OverAgeQty,'
            +' Case When IsNull(Tmp5.MoQty,0)<> 0 Then round(convert(float,(IsNull(Tmp5.RationQty,0)+IsNull(Tmp5.OverAgeQty,0)))/Tmp5.Moqty,4)'
            +'      Else 0.0 end Sjdg,round(convert(float,#TmpOpenBomLast.BomQty),4) as Bzdg'
            +' From'
            +' (Select Tmp3.Ite_ItemCode,Tmp4.ItemCode,IsNull(Tmp3.MoQty,0) As MoQty,IsNull(Tmp4.RationQty,0) as RationQty ,isNull(Tmp4.OverAgeQty,0) as  OverAgeQty'
            +' From'
            +' (Select #TmPMoLine.ItemCode as Ite_ItemCode,Sum(isnull(#TmPMoLine.MoRealInQty,0)) As MoQty From #TmPMoLine'
            +'   Group By #TmPMoLine.ItemCode) Tmp3'
            +' Left Join'
            +' (Select Tmp1.Ite_ItemCode,Tmp1.ItemCode,Tmp1.RationQty,Tmp2.OverAgeQty From'
            +' (Select #TmPMoLine.ItemCode As Ite_ItemCode,#TmpInvOutBillline.ItemCode As ItemCode,'
            +'     Sum(Isnull(#TmpInvOutBillline.InvBillQty,0)) As RationQty'
            +'  From   #TmpInvOutBillline Join  #TmPMoLine'
            +'       On  #TmpInvOutBillline.MoNo+Convert(ChAr,#TmpInvOutBillline.MoLineNo)=#TmPMoLine.MoNo+Convert(chAr,#TmPMoLine.MoLineNo)'
            +'       and #TmpInvOutBillline.OverPlan=0'
            +'  Group By   #TmPMoLine.ItemCode ,#TmpInvOutBillline.ItemCode)Tmp1'
            +'  Left Join'
            +' (Select #TmPMoLine.ItemCode As Ite_ItemCode,#TmpInvOutBillline.ItemCode As ItemCode,'
            +'     Sum(Isnull(#TmpInvOutBillline.InvBillQty,0)) As OverAgeQty'
            +' From   #TmpInvOutBillline Join  #TmPMoLine'
            +'       On  #TmpInvOutBillline.MoNo+Convert(ChAr,#TmpInvOutBillline.MoLineNo)=#TmPMoLine.MoNo+Convert(chAr,#TmPMoLine.MoLineNo)'
            +'       and #TmpInvOutBillline.OverPlan=1'
            +'  Group By   #TmPMoLine.ItemCode ,#TmpInvOutBillline.ItemCode)tmp2'
            +' On Tmp1.Ite_ItemCode=Tmp2.Ite_ItemCode'
            +' and Tmp1.ItemCode=Tmp2.ItemCode) Tmp4'
            +' On Tmp3.Ite_ItemCode=Tmp4.Ite_ItemCode) Tmp5'
            +' join #TmpOpenBomLast on #TmpOpenBomLast.ItemCode=Tmp5.ItemCode'
            +'   And #TmpOpenBomLast.Ite_ItemCode=Tmp5.Ite_ItemCode) Tmp6'
           +'  Order By Ite_ItemCode ,ItemCode ';
           ExecuteSql(AdoQry_tmp,SqlText1,1);

{  AdoQry_tmp.Close;
  AdoQry_tmp.SQL.clear;
  AdoQry_tmp.SQL.Text := ' exec  PMrp_Qry_AnalyzerMo5 :linestatus1,:linestatus2,:ItemCodeStArt,:ItemCodeend,:OrderDateStArt,:OrderDateend ';
  AdoQry_Tmp.Parameters.ParamByName('linestatus1').Value:=MoLineStatus1;
  AdoQry_Tmp.Parameters.ParamByName('linestatus2').Value:=MoLineStatus2;
  AdoQry_Tmp.Parameters.ParamByName('ItemCodeStArt').Value:=Edt_ItemCodeStArt.Text;
  AdoQry_Tmp.Parameters.ParamByName('ItemCodeend').Value:=Edt_ItemCodeend.Text;
  AdoQry_Tmp.Parameters.ParamByName('OrderDateStArt').Value:=Mak_OrderDateStArt.Text;
  AdoQry_Tmp.Parameters.ParamByName('OrderDateend').Value:= Mak_OrderDateend.Text;
  AdoQry_tmp.ExecSQL ;}


  conditionHint:='行状态：'+ComboBox1.Text +'/物料代码从：'+Edt_ItemCodeStArt.Text +'到'+Edt_ItemCodeend.Text+ '/约定交货日从：'+Mak_OrderDateStArt.Text +'到'+Mak_OrderDateend.Text;

  self.ModalResult:=mrOk;

end;

procedure TFrm_Mrp_Qry_AnalyzerMo5_C.FormCreate(Sender: TObject);
begin
  inherited;
  Edt_ItemCodeend.Text := 'ZZZZZZ';
  ComboBox1.ItemIndex :=2;
  Mak_OrderDateStArt.Text := formatdatetime('yyyy.mm.dd',date()-30);
  Mak_OrderDateend.Text :=formatdatetime('yyyy.mm.dd',Date());
end;

procedure TFrm_Mrp_Qry_AnalyzerMo5_C.FormShow(Sender: TObject);
begin
  inherited;
  Edt_ItemCodeStArt.SetFocus;
end;

procedure TFrm_Mrp_Qry_AnalyzerMo5_C.Edt_ItemCodeStArtKeyDown(
  Sender: TObject; var Key: Word; Shift: TShiftState);
begin
  inherited;
  If (Key=120) Then
    CommonHint(Sender,AdoQry_Tmp,'ItemName','物料描述','ItemCode', '物料代码','Item');
end;

procedure TFrm_Mrp_Qry_AnalyzerMo5_C.Edt_ItemCodeendKeyDown(
  Sender: TObject; var Key: Word; Shift: TShiftState);
begin
  inherited;
  If (Key=120) Then
    CommonHint(Sender,AdoQry_Tmp,'ItemName','物料描述','ItemCode', '物料代码','Item');
end;

end.