mrp_qry_analyzermo6_c.pas

一个MRPII系统源代码版本

unit Mrp_Qry_AnalyzerMo6_C;
  
Interface

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

Type
  TFrm_Mrp_Qry_AnalyzerMo6_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;
    Edt_MoLineNoStatus: TEdit;
    Label6: TLabel;
    Edt_MoLineNoend: TEdit;
    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_AnalyzerMo6_C: TFrm_Mrp_Qry_AnalyzerMo6_C;

implementation

uses Mrp_Qry_AnalyzerMo6, Sys_Global;

{$R *.DFM}

procedure TFrm_Mrp_Qry_AnalyzerMo6_C.btn_okClick(Sender: TObject);
var
  SqlText,SqlText1:String;
  AdoQuery:TAdoQuery;
  I :integer;
begin
  inherited;
  Mrp_Qry_AnalyzerMo6.Datestate := Mak_OrderDateStArt.Text;
  Mrp_Qry_AnalyzerMo6.Dateend := Mak_OrderDateend.Text;
  try
    ProgressBar1.Position:=0;
    SqlText:=' Drop Table  #tmp17,#TmpInvOutBillLine6,#TmPMoLine6,#TmpOpenBom6Last,#TmPmdlOpenBom6,#TmpOpenBom6,#TmpComponent6' ;  //#TmPMoLine6Last,
    ExecuteSql(AdoQry_tmp,SqlText,1);
  Except

  end;
// 找出符合条件的poLine,MoLine
  Sqltext:=' Select MoLine.MoNo,MoLine.MoLineNo,MoLine.ItemCode,MoRealInQty, falg=0 Into #TmPMoLine6  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+''' '
          +'                     And  MoLine.MoLineStatus=7 '
          +' 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+''' '
          +'                     And PoLine.PoLineStatus=7 '
          +'                     And PoType=3';
  ExecuteSql(AdoQry_tmp,SqlText,1);
//从-InvOutBillLine-找出所有的-mo ,po 出库-------
  SqlText:=' Select InvOutBillLine.MoNo,InvOutBillLine.MoLineNo,InvOutBillLine.ItemCode,InvOutBill.InvBillStkChck,InvOutBillLine.InvBillQty,InvOutBillLine.InvBillNoTaxPrice,InvOutBill.OverPlan '
          +'                      Into #TmpInvOutBillLine6 '
          +'                      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.InvBillNoTaxPrice,InvOutBill.OverPlan '
          +'                      From InvOutBillLine '
          +'                           Join InvOutBill On InvOutBillLine.InvBillId=InvOutBill.InvBillId'
          +'                          where  InvOutBill.BillTypeCode=''0202''';
  ExecuteSql(AdoQry_tmp,SqlText,1);
//
{  SqlText:=' Select * Into #TmPMoLine6Last From #TmPMoLine6 Where Not Exists(Select * From #TmpInvOutBillLine6 '
          +'                                          where #TmpInvOutBillLine6.InvBillStkChck=0 '
          +'                                          And #TmPMoLine6.Mono=#TmpInvOutBillLine6.MoNo '
          +'                                          And #TmPMoLine6.MoLineNo=#TmpInvOutBillLine6.MoLineNo)'
          +'                             And Exists(Select * from #TmpInvOutBillLine6 '
          +'                                          Where #TmpInvOutBillLine6.InvBillStkChck=1 '
          +'                                          And #TmPMoLine6.MoNo=#TmpInvOutBillLine6.MoNo '
          +'                                          And #TmPMoLine6.MoLineNo=#TmpInvOutBillLine6.MoLineNo) ';

  ExecuteSql(AdoQry_tmp,SqlText,1); }
//
  SqlText:=' Create Table #TmPmdlOpenBom6(Ite_ItemCode varChAr(16),ItemCode varChAr(16),Bomqty float,BomScrAprate float,isvir tinyint) '
          +' Create Table #TmpOpenBom6(Ite_ItemCode varChAr(16),ItemCode varChAr(16),Bomqty float,BomScrAprate float) '
          +' Create Table #TmpComponent6(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  Ite_ItemCode in (Select Distinct ItemCode From #TmPMoLine6)' ; //#TmPMoLine6Last
  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 #TmpComponent6  (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 #TmpComponent6) '
            +' begin '
            +'   Delete From #TmPmdlOpenBom6 '

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

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

            +'       Delete From #TmpComponent6 '
            +'                       Insert #TmpComponent6 '
            +'                          Select Ite_ItemCode,ItemCode,Bomqty,BomScrAprate,isvir From #TmPmdlOpenBom6 '
            +'  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 #TmpOpenBom6Last  from #TmpOpenBom6 '
          +'  Group By Ite_ItemCode,ItemCode ';
  ExecuteSql(AdoQry_tmp,SqlText,1);
//-----
  SqlText:=' Select ItemCode As Ite_ItemCode,Sum(IsNull(MoRealInQty,0)) As MoQty From #TmPMoLine6' //#TmPMoLine6Last
          +'       Group By ItemCode';
  ExecuteSql(AdoQry_tmp,SqlText,1);
//-----
  SqlText1:=' Select Ite_ItemCode,ItemCode,MOQty,TotalQty,Sjdg,bzdg,sjdgcb,bzdgcb,'
          +' Case when Isnull(bzdgcb,0) <>0 then'
          +'                   Convert(varchAr,round((sjdgcb-bzdgcb)/bzdgcb*100,1))+''%'''
          +'                    else ''0%'''
          +'                  end Dgbl into #tmp17 '
          +' from  '
          +' (Select Tmp3.Ite_ItemCode,Tmp3.ItemCode,Tmp3.MoQty,Tmp3.TotalQty,Tmp3.Sjdg,#TmpOpenBom6Last.BomQty As Bzdg, '
          +'     Tmp3.Sjdgcb,Case when Isnull(Tmp3.Sjdg,0)<>0 Then #TmpOpenBom6Last.BomQty/Tmp3.Sjdg*Tmp3.Sjdgcb '
          +'                 Else 0 end Bzdgcb '
          +' From '

          +' (Select Tmp1.Ite_ItemCode,Tmp1.ItemCode,Tmp2.MoQty,Tmp1.TotalQty,case when isnull(Tmp2.MoQty,0)<>0 then round(convert(float,isNull(Tmp1.TotalQty,0))/IsNull(Tmp2.MoQty,0),4) '
          +'                                                                 else 0 end Sjdg, '
          +'                                                Tmp1.Sjdgcb from '
          +'   (Select #TmPMoLine6.ItemCode as Ite_ItemCode,#TmpInvOutBillLine6.ItemCode, Sum(isnull(#TmpInvOutBillLine6.InvBillQty,0)) As TotalQty,'
          +'           Sum(isNull(#TmpInvOutBillLine6.InvBillQty*#TmpInvOutBillLine6.InvBillNoTaxPrice,0)) As SjdgCb '
          +'             From  #TmPMoLine6 Join #TmpInvOutBillLine6 '
          +'            On #TmPMoLine6.MoNo+Convert(ChAr,#TmPMoLine6.MoLineNO)=#TmpInvOutBillLine6.MoNo+Convert(chAr,#TmpInvOutBillLine6.MoLineNo)'
          +'   Group By #TmPMoLine6.ItemCode,#TmpInvOutBillLine6.ItemCode ) Tmp1 '
          +' join '
          +'  (Select ItemCode As Ite_ItemCode,Sum(IsNull(MoRealInQty,0)) As MoQty From #TmPMoLine6 '
          +'                 Group By ItemCode)Tmp2 '
          +'  On Tmp1.Ite_ItemCode=Tmp2.Ite_ItemCode ) Tmp3 '
          +'  Join #TmpOpenBom6Last On Tmp3.Ite_ItemCode=#TmpOpenBom6Last.Ite_ItemCode '
          +'                      and Tmp3.ItemCode=#TmpOpenBom6Last.ItemCode) tmp4'
          +'  Order By Ite_ItemCode ,ItemCode ';
  ExecuteSql(AdoQry_tmp,SqlText1,1);
 { with Frm_Mrp_Qry_AnalyzerMo6.AdoQry_Main do
  begin
    Close;
    Sql.clear;
    Sql.Text := SqlText1;
    Open;
  end;}
  {AdoQry_tmp.Close;
  AdoQry_tmp.SQL.clear;
  AdoQry_tmp.SQL.Text := ' exec  PMrp_Qry_AnalyzerMo6 :MoNoStArt,:MoNoend,:ItemCodeStArt,:ItemCodeend,:OrderDateStArt,:OrderDateend ';
  AdoQry_Tmp.Parameters.ParamByName('MoNoStArt').Value:=Edt_MoLineNoStatus.Text;
  AdoQry_Tmp.Parameters.ParamByName('MoNoend').Value:=Edt_MoLineNoend.Text;
  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:= '物料代码从：'+Edt_ItemCodeStArt.Text +'到'+Edt_ItemCodeend.Text +'/定单号从：'+Edt_MoLineNoStatus.Text +'到'+Edt_MoLineNoend.Text+'/约定交货日从：'+Mak_OrderDateStArt.Text +'到'+Mak_OrderDateend.Text;

  self.ModalResult:=mrOk;

end;

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

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

procedure TFrm_Mrp_Qry_AnalyzerMo6_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_AnalyzerMo6_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.