mrp_qry_informaltotalmrp.pas

一个MRPII系统源代码版本

unit Mrp_Qry_InformalTotalMrp;
//
Interface

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

Type
  TFrm_Mrp_Qry_InformalTotalMrp = Class(TFrm_Base_Qry)
    AdoQry_MainItemCode: TStringField;
    AdoQry_MainItemName: TStringField;
    AdoQry_MainItemflag: TStringField;
    AdoQry_MainUomName: TStringField;
    AdoQry_MainCurrentonCheckInv: TFloatField;
    AdoQry_MainCurrentonhandInv: TFloatField;
    AdoQry_MainMrpInv: TFloatField;
    AdoQry_Maingrossqty: TFloatField;
    AdoQry_Mainlessqty: TFloatField;
    AdoQry_Mainminqty: TFloatField;
    AdoQry_MainSysqty: TFloatField;
    AdoQry_MaInOrderpreqty: TFloatField;
    AdoQry_MaInOrderxdqty: TFloatField;
    AdoQry_MaInOrderFinishqty: TFloatField;
    AdoQry_MaInOrdernoFinishqty: TFloatField;
    AdoQry_MainPmCode: TIntegerField;
    btn_Item: TButton;
    btn_Vendor: TButton;
    btn_ite_Item: TButton;
    btn_iteItem_Pegging: TButton;
    btn_Sys: TButton;
    btn_Order: TButton;
    Label1: TLabel;
    lbl_DayCount: TLabel;
    Label2: TLabel;
    lbl_Sedate: TLabel;
    AdoQry_MainBatchstrat: TIntegerField;
    Label3: TLabel;
    lbl_rundate: TLabel;
    AdoQry_MainQclt: TIntegerField;
    AdoQry_MainPmBatch: TIntegerField;
    AdoQry_MainAssignedqty: TFloatField;
    procedure FormDestroy(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure Act_LookExecute(Sender: TObject);
    procedure btn_ItemClick(Sender: TObject);
    procedure DBGridEhGetCellParams(Sender: TObject; Column: TColumnEh;
      AFont: TFont; var Background: TColor; State: TGridDrawState);
    procedure btn_OrderClick(Sender: TObject);
    procedure btn_SySclick(Sender: TObject);
    procedure btn_VendorClick(Sender: TObject);
    procedure btn_iteItem_PeggingClick(Sender: TObject);
    procedure btn_ite_ItemClick(Sender: TObject);
    procedure FormActivate(Sender: TObject);
  private
  function getValue(Code:string;tableName:string;ValueType:integer):variant;
  function getenddate(sdate:string;dayCount:integer):string;
  function ismorethanzero(fieldName:string;tableName:string;conditionfield:string):boolean;
        { Private declarations }
  public
    procedure InitForm(AdOConnection:TAdOConnection;ShowExtendColumn:Boolean);Override;
    { Public declarations }
  end;

var
  Frm_Mrp_Qry_InformalTotalMrp: TFrm_Mrp_Qry_InformalTotalMrp;
  ItemCode:string;

implementation
uses Mrp_Qry_InformalTotalMrp_D,Mrp_Qry_UpRightMrp_D_Item,Sys_Global,
  Mrp_Qry_InformalTotalMrp_Po, Mrp_Qry_InformalTotalMrp_Mo,Mrp_Qry_InformalTotalMrp_Sys,
  Mrp_Qry_InformalTotalMrp_vd, Mrp_Qry_InformalUpRightMrp_D_ParentMo,
  Mrp_Qry_InformalTotalMrp_Item;
{$R *.DFM}

{ TFrm_Mrp_Qry_InformalTotalMrp }

procedure TFrm_Mrp_Qry_InformalTotalMrp.InitForm(
  AdOConnection: TAdOConnection; ShowExtendColumn: Boolean);
var sqltext0,sqltext1,sqltext2,sqltext22,sqltext33,sqltext3,sqltext4,sqltext5,sqltext6,sqltext7,sqltext8,sqltext9:string;
begin
  inherited;
  Application.ProcessMessages;
  lbl_DayCount.Caption:=getValue('InformalRunMrpdate','MrpParam',1);
  lbl_Sedate.Caption:=copy((getValue('InformalRunMrpdate','MrpParam',0)),1,10);
  lbl_rundate.Caption:=getValue('InformalRunMrpdate','MrpParam',0);
  lbl_Sedate.Caption:=lbl_Sedate.Caption+'--'+getenddate(lbl_Sedate.Caption,strtoint(lbl_DayCount.Caption));
//sqltext0:从InformalMrpResult表产生“物料代码”及“毛需求量”
  sqltext0:='Select distinct InformalMrpResult.ItemCode,Item.PmCode,Sum(GrossQty) As GrossQty '
            +' into #tmPMrp        '
            +' From InformalMrpResult '
            +' join Item on InformalMrpResult.ItemCode=Item.ItemCode'
            +' Where Ordinal=1   '
            +' Group By InformalMrpResult.ItemCode,Item.PmCode ';
//sqltext1:从Item中取出“在检库存、当前库存、安全库存、物料描述、计量单位”
  sqltext1:='Select   #TmPMrp.ItemCode,              '
           +'            Item.ItemName,               '
           +'            Item.Qclt,'
           +'             Uom.UomName,               '
           +'             #tmPMrp.PmCode,             '
           +'             Item.Batchstrat,'
           +'             Item.PmBatch,'
           +'            Item.CurrentOnCheckInv,     '
           +'            Item.CurrentOnHandInv,      '
           +'         #TmPMrp.GrossQty,              '
           +'            Item.MinQty                 '
           +'    into #tmPMrp1                       '
           +' From #TmPMrp                           '
           +'  Join Item on #TmPMrp.ItemCode=Item.ItemCode '
          +' Left Join Uom On Item.UomCode=Uom.UomCode';
//从MoLine、PoLine表中取出物料的“生产准备量、生产下达量、生产已结量、生产未结量”
   //生产准备量
  sqltext2:='Select ItemCode,                          '
                 +' Sum(MoQty) As Qty1     '
            +'into #tmPMoLine1                          '
            +'  From MoLine                             '
           +' Where MoLineStatus=5                      '
           +'   And ItemCode In (Select Distinct ItemCode '
           +'                    From InformalMrpResult   '
           +'                    Where Ordinal=1)         '
           +' Group By ItemCode                           ';
  //生产下达量、生产完成量、生产未结量
  sqltext3:='Select ItemCode,                            '
            +'        Sum(MoQty)               As Qty2,   '
            +'        Sum(MoQty-MoNoFinishQty) As Qty3,   '
            +'        Sum(MoNoFinishQty)       As Qty4    '
            +' into #tmPMoLine2                           '
           +' From MoLine                                 '
           +' Where MoLineStatus=6 And ItemCode In        '
           + '                           (Select Distinct ItemCode  '
           + '                           From InformalMrpResult Where Ordinal=1)'
           +' Group By ItemCode  ';
 //采购准备量
  sqltext4:='Select ItemCode,                            '
            +'        Sum(PoQty) As Qty1                  '
            +' into #tmppoline1                           '
            +' From PoLine                                '
            +' Where PoLineStatus=5                       '
            +'   And ItemCode In (Select Distinct ItemCode '
            +'                      From InformalMrpResult '
            +'                     Where Ordinal=1)       '
            +' Group By ItemCode';
  //采购下达量、采购完成量、采购未结量
  sqltext5:='Select ItemCode,                            '
            +'        Sum(PoQty)               As Qty2,   '
            +'        Sum(PoQty-PoNoFinishQty) As Qty3,   '
            +'        Sum(PoNoFinishQty)       As Qty4    '
            +' into #tmppoline2                           '
            +' From PoLine                                '
            +' Where PoLineStatus=6                       '
            +'   And ItemCode In (Select Distinct ItemCode'
            +'                     From InformalMrpResult '
            +'                    Where Ordinal=1)        '
            +' Group By ItemCode';
 //从MoLine、PoLine及InvOutBillLine表中更新“可用库存”：即统计车间可用库存
   //MO单
  sqltext6:='Select InvOutBillLine.ItemCode,  '
            +'        sum(InvBillQty*MoLine.MoNoFinishQty/MoLine.MoQty) As CurrentOnHandInv '
           + ' into #tmpInvOutBillline1        '
           + ' From InvOutBillLine             '
           +' Join MoLine                       '
           +' on InvOutBillLine.MONo=MoLine.MoNo   '
            +'        And InvOutBillLine.MoLineNo=MoLine.MoLineNo  '
            +' join InvOutBill on InvOutBillline.InvBillid=InvOutBill.InvBillid  '
            +'   and InvOutBill.BillTypeCode in (''0201'',''0202'')  '
            +' Where InvOutBillLine.MONo Is Not Null            '
           + '   And MoLine.MoLineStatus=6                    '
           + '   And MoLine.MoQty>0                           '
           + '   And InvOutBillLine.ItemCode In (Select Distinct ItemCode  '
           + '                                   From InformalMrpResult    '
           + '                                   Where Ordinal=1)          '
           +'  group by InvOutBillLine.ItemCode';
   //PO单
   sqltext7:='Select InvOutBillLine.ItemCode,  '
             +'        sum(InvBillQty*PoLine.PoNoFinishQty/PoLine.PoQty) As CurrentOnHandInv '
             +' into #TmpInvOutBillLine2                                             '
             +' From InvOutBillLine                                                  '
             +' Join PoLine on InvOutBillLine.PONo=PoLine.PoNo                       '
             +'            And InvOutBillLine.PoLineNo=PoLine.PoLineNo               '
            +' join InvOutBill on InvOutBillline.InvBillid=InvOutBill.InvBillid  '
            +'   and InvOutBill.BillTypeCode in (''0201'',''0202'')  '
             +' Where InvOutBillLine.PoNo Is Not Null                                '
             +'   And PoLine.PoLineStatus=6                                         '
             +'   And PoLine.PoQty>0                                                 '
             +'   And InvOutBillLine.ItemCode In (Select Distinct ItemCode          '
             +'                                   From InformalMrpResult            '
             +'                                   Where Ordinal=1)'
             +'  group by InvOutBillLine.ItemCode';
   sqltext22:='select mono as Orderno,         '
             +'       MoLineno as Orderlineno, '
            + '        ItemCode,                '
             +'        moqty as Orderqty,       '
             +'        MoNoFinishqty as OrdernoFinishqty  '
             +'   into #tmpOrder                      '
             +'   from  MoLine                        '
             +'  where MoNoFinishqty>0                '
             +'    and MoLinestatus=6                  '
            + ' union                                   '
             +' select poline.pono as Orderno,           '
             +'        polineno as Orderlineno,          '
             +'        ItemCode,                         '
             +'        poqty as Orderqty,                '
             +'        ponoFinishqty as OrdernoFinishqty '
             +'  from  poline                      '
             +'  join  po on poline.pono=po.pono   '
             +'           and po.poType=1    '
             +' where ponoFinishqty>0       '
              +'  and polinestatus=6        '
             + '   select ItemCode,         '
              + '        InvOutBill.mono as Orderno,    '
              + '        InvOutBill.MoLineno as Orderlineno,   '
              + '        sum(isnull(InvOutBillline.InvBillqty,0)) as Billqty  '
              + '   into  #tmpInvBill                     '
              +'   from  InvOutBillline  '
              + '   join  InvOutBill                       '
              + '     on  InvOutBillline.InvBillid=InvOutBill.InvBillid  '
              + '     and InvOutBill.BillTypeCode in (''0201'',''0202'')    '
              + '  where exists(select * from #tmpOrder                    '
              + '               where InvOutBill.mono=#tmpOrder.Orderno    '
              + '                  and  InvOutBill.MoLineno=#tmpOrder.Orderlineno  '
              + '                )                                              '
              + ' group by ItemCode,InvOutBill.mono,InvOutBill.MoLineno         '
              + '   union                                                        '
              + '  select ItemCode,                                             '
              + '         InvOutBill.pono as Orderno,                           '
               +'         InvOutBill.polineno as Orderlineno,                  '
             +  '         sum(isnull(InvOutBillline.InvBillqty,0)) as Billqty             '
              +'    from InvOutBillline                                       '
              + '    join  InvOutBill                                          '
              + '      on  InvOutBillline.InvBillid=InvOutBill.InvBillid       '
              + '     and InvOutBill.BillTypeCode in (''0201'',''0202'')           '
              + '  where exists(select * from #tmpOrder                      '
              + '                  where InvOutBill.pono=#tmpOrder.Orderno    '