mrp_qry_informaluprightmrp_d.pas

一个MRPII系统源代码版本

unit Mrp_Qry_InformalUpRightMrp_D;

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_InformalUpRightMrp_D = Class(TFrm_Base_Qry)
    AdoQry_MainItemCode: TStringField;
    AdoQry_MainSysInfo: TStringField;
    AdoQry_Mainreleasedate: TDateTimeField;
    AdoQry_MaInOrdernoandlineno: TStringField;
    AdoQry_Maingrossqty: TFloatField;
    AdoQry_MaInOrderqty: TFloatField;
    AdoQry_Mainonhand: TFloatField;
    AdoQry_Mainnetqty: TFloatField;
    AdoQry_Mainrequiredate: TDateTimeField;
    AdoQry_Mainordinal: TIntegerField;
    AdoQry_Maintrace: TStringField;
    AdoQry_MaInPlanqty: TFloatField;
    AdoQry_MainPmCode: TIntegerField;
    Label1: TLabel;
    lbl_ItemCode: TLabel;
    Label2: TLabel;
    lbl_UomName: TLabel;
    Label3: TLabel;
    lbl_ldtime: TLabel;
    Label4: TLabel;
    lbl_Preparetime: TLabel;
    Label5: TLabel;
    lbl_runlt: TLabel;
    Label6: TLabel;
    lbl_Qclt: TLabel;
    Label12: TLabel;
    lbl_oncehck: TLabel;
    Label13: TLabel;
    lbl_minqty: TLabel;
    Label11: TLabel;
    lbl_onhand: TLabel;
    Label10: TLabel;
    lbl_Batchstrat: TLabel;
    Label9: TLabel;
    lbl_PmBatch: TLabel;
    Label8: TLabel;
    lbl_PmCode: TLabel;
    Label7: TLabel;
    lbl_ItemName: TLabel;
    Label14: TLabel;
    lbl_Assignedqty: TLabel;
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure FormDestroy(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure Act_LookExecute(Sender: TObject);
    procedure Act_ShowGridExecute(Sender: TObject);
  private
    { Private declarations }
  public
    procedure InitForm(AdOConnection:TAdOConnection;ShowExtendColumn:Boolean);Override;
    function  existsMpsandmo(ItemCode:string;requiredate:string):integer;
    function ifexistsiteItemCode(ItemCode:string;date,Qclt:string):boolean;{ Private declarations }
    function existsfArItemCode(ItemCode:string):boolean;
    function getlt(ItemCode:string;ltType:integer):integer;
    function existsMps(ItemCode:string;date,Qclt:string):boolean;
    { Public declarations }
  end;

var
  Frm_Mrp_Qry_InformalUpRightMrp_D: TFrm_Mrp_Qry_InformalUpRightMrp_D;
       ItemCode,ItemName,Orderno,Preparelt,runlt,Qclt,Orderlineno,PmCode,minqty,Batchstrat,requiredate:string;
  traceType:integer;ItemPmCode:integer;
implementation
uses  Mrp_Qry_InformalUpRightMrp, Mrp_Qry_InformalUpRightMrp_D_Po,
  Mrp_Qry_InformalUpRightMrp_D_vd,Sys_Global,
  Mrp_Qry_InformalUpRightMrp_D_Mo, Mrp_Qry_InformalUpRightMrp_D_Mps,
  Mrp_Qry_InformalUpRightMrp_D_ParentMo,Mrp_Enter_MoSsParent,Mrp_Global,
  Mrp_Qry_UpRightMrp_D_MpsandMo;
{$R *.DFM}

{ TFrm_Mrp_Qry_InformalUpRightMrp_D }

procedure TFrm_Mrp_Qry_InformalUpRightMrp_D.InitForm(
  AdOConnection: TAdOConnection; ShowExtendColumn: Boolean);
var sqltext1,sqltext2,sqltext3,sqltext4,sqltext5,tMpstr,tmpreleasedate,trace:string;
    tmpQry:TAdoQuery;
    tmponhand,tmpgrossqty:real; tmpordinal:integer;
    success:integer;
begin
  inherited;
 ItemCode:=Frm_Mrp_Qry_InformalUpRightMrp.AdoQry_Head.fieldbyname('ItemCode').asstring;
 ItemName:=Frm_Mrp_Qry_InformalUpRightMrp.AdoQry_Head.fieldbyname('ItemName').asstring;
 Uom:=Frm_Mrp_Qry_InformalUpRightMrp.AdoQry_Head.fieldbyname('UomName').asstring;
 ldtime:=floattostr(Frm_Mrp_Qry_InformalUpRightMrp.AdoQry_Head.fieldbyname('ldtime').asfloat);
 Preparelt:=floattostr(Frm_Mrp_Qry_InformalUpRightMrp.AdoQry_Head.fieldbyname('Preparelt').asfloat);
 runlt:=floattostr(Frm_Mrp_Qry_InformalUpRightMrp.AdoQry_Head.fieldbyname('runlt').asfloat);
 Qclt:=floattostr(Frm_Mrp_Qry_InformalUpRightMrp.AdoQry_Head.fieldbyname('Qclt').asfloat);
 PmBatch:=floattostr(Frm_Mrp_Qry_InformalUpRightMrp.AdoQry_Head.fieldbyname('PmBatch').asfloat);
 ItemPmCode:=Frm_Mrp_Qry_InformalUpRightMrp.AdoQry_Head.fieldbyname('PmCode').asinteger;
 CurrentInv:=floattostr(Frm_Mrp_Qry_InformalUpRightMrp.AdoQry_Head.fieldbyname('CurrentonhandInv').asfloat);
 Assignedqty:=floattostr(Frm_Mrp_Qry_InformalUpRightMrp.AdoQry_Head.fieldbyname('Assignedqty').asfloat);
  oncheckqty:=floattostr(Frm_Mrp_Qry_InformalUpRightMrp.AdoQry_Head.fieldbyname('oncheckqty').asfloat);
 if Frm_Mrp_Qry_InformalUpRightMrp.AdoQry_Head.fieldbyname('PmCode').asinteger=0 then
  PmCode:='0 制造';
 if Frm_Mrp_Qry_InformalUpRightMrp.AdoQry_Head.fieldbyname('PmCode').asinteger=1 then
  PmCode:='1 采购';
 if Frm_Mrp_Qry_InformalUpRightMrp.AdoQry_Head.fieldbyname('PmCode').asinteger=2     then
  PmCode:='2 委外加工';
 if Frm_Mrp_Qry_InformalUpRightMrp.AdoQry_Head.fieldbyname('PmCode').asinteger=3     then
  PmCode:='3 既制造又委外';
 minqty:=floattostr(Frm_Mrp_Qry_InformalUpRightMrp.AdoQry_Head.fieldbyname('minqty').asfloat);
 if Frm_Mrp_Qry_InformalUpRightMrp.AdoQry_Head.fieldbyname('Batchstrat').asinteger=0  then
  Batchstrat:='直接批量法';
 if Frm_Mrp_Qry_InformalUpRightMrp.AdoQry_Head.fieldbyname('Batchstrat').asinteger=1   then
  Batchstrat:='固定批量法';

// lbl_Condition.Caption:=' 物料代码：'+ItemCode+' 单  位：'+Uom+'   提前期：'+ldtime+' 准备提前期：'+Preparelt+' 运行提前期：'+runlt+' 质检提前期：'+Qclt+#13+' 物料描述：'+ItemName+' 制购码：'+PmCode+' 批量：'+PmBatch+' 批量策略：'+Batchstrat+#13+' 当前可用库存：'+CurrentInv+' 当前待检库存：'+oncheckqty+' 安全库存：'+minqty;
   lbl_ItemCode.Caption:=ItemCode;
  lbl_UomName.Caption:=Uom;
  lbl_ldtime.Caption:=ldtime;
  lbl_Preparetime.Caption:=Preparelt;
  lbl_runlt.Caption:=runlt;
  lbl_Qclt.Caption:=Qclt;
    lbl_ItemName.Caption:=ItemName;
  lbl_PmCode.Caption:=PmCode;
  lbl_PmBatch.Caption:=PmBatch;
    lbl_ItemCode.Caption:=ItemCode;
  lbl_ItemCode.Caption:=ItemCode;
  lbl_Batchstrat.Caption:=Batchstrat;
  lbl_oncehck.Caption:=oncheckqty;
  lbl_minqty.Caption:=minqty;


  tmpQry:=TAdoQuery.Create(dbconnect);
  tmpQry.EnableBCD:=False;
  tmpQry.Connection:=dbconnect;
  success:=TemporArilyRunMrp(dbconnect,'select ItemCode from InformalMrpResult where ItemCode='+quotedstr(ItemCode)+' and ordinal=1 ',0);
  sqltext1:='select ItemCode,                                                  '
            +#13+'       PmCode,'
            +#13+'       ''建议下达'' as SysInfo,                                     '
            +#13+'        releasedate,                                              '
            +#13+'        Ordernoandlineno=Orderno+''-''+convert(varchAr,Orderlineno),'
            +#13+'        grossqty,                                                 '
            +#13+'        Orderqty,                                                 '
            +#13+'        onhand,                                                   '
            +#13+'        netqty,                                                   '
            +#13+'        getdate() as requiredate,                                 '
            +#13+'        ordinal,                                                  '
            +#13+'        pegging as trace,                                    '
            +#13+'        convert(float(8),0) as Planqty,                               '
            +'            Assignedqty          '
            +#13+' into #tmpInformalMrpResult                                               '
            +#13+' from InformalMrpResult'
            +#13+' where ItemCode='''+ItemCode+''''
            +'     Order by InformalMrpResult.MrpResultid,InformalMrpResult.ItemCode ';

  sqltext2:='create table #tmpResult '
            +'  (ItemCode  varchAr(16) null,           '
            +'    PmCode   int         null,'
            +'    SysInfo  varchAr(10) null,           '
            +'    releasedate datetime null,           '
            +'    Ordernoandlineno varchAr(50) null,   '
            +'    grossqty float(8)    null,           '
            +'    Orderqty float(8)    null,           '
            +'    onhand   float(8)    null,           '
            +'    netqty   float(8)    null,           '
            +'    requiredate datetime  null,          '
            +'    ordinal   int        null,           '
            +'    trace     varchAr(20) null,          '
            +'    Planqty   float(8)   null,'
            +'    Assignedqty float(8) null)           ';


  with AdoQry_tmp do
    begin
      Close;
      sql.clear;
      sql.Add(sqltext2);
      Prepared;
      try
      execsql;
      except
      end;
    end;


 selectfromsql:='select * from #tmpResult';
  with AdoQry_Main do
    begin
      Close;
      sql.clear;
      sql.Add(sqltext1);
      Prepared;
      try
      execsql;
      except
      end;
        tmpQry.Close;
        tmpQry.SQL.clear;
        tmpQry.sql.Add('select onhand as onhand,Assignedqty from #tmpInformalMrpResult where  ordinal=3 and PmCode='+inttostr(ItemPmCode));
        tmpQry.Open;
        tmpQry.First;
        tmponhand:=tmpQry.fieldbyname('onhand').asfloat;
        lbl_Assignedqty.Caption:=floattostr(tmpQry.fieldbyname('Assignedqty').asfloat);
        lbl_onhand.Caption:=floattostr(tmpQry.fieldbyname('onhand').asfloat-tmpQry.fieldbyname('Assignedqty').asfloat);

      Close;
      sql.clear;
      sql.Add('select * from #tmpInformalMrpResult');
      Prepared;
      try
      open;
      except
      end;

      First;
      while not eof do
       begin
         tmpordinal:=fieldbyname('ordinal').asinteger;
         case tmpordinal of
            2:   with AdoQry_tmp do
                  begin
                    sqltext3:='insert into #tmpResult '
                             +#13+' Values('+quotedstr(AdoQry_Main.fieldbyname('ItemCode').asstring)+','+quotedstr(inttostr(AdoQry_Main.fieldbyname('PmCode').asinteger))+','
                             +#13+'null,'+quotedstr(AdoQry_Main.fieldbyname('releasedate').asstring)+','
                             +#13+quotedstr(AdoQry_Main.fieldbyname('Ordernoandlineno').asstring)+','
                             +#13+'null,'+floattostr(AdoQry_Main.fieldbyname('Orderqty').asfloat)+','
                             +#13+floattostr(AdoQry_Main.fieldbyname('onhand').asfloat)+','
                             +#13+'null,null,'+inttostr(AdoQry_Main.fieldbyname('ordinal').asinteger)+','
                             +#13+'null,'+floattostr(AdoQry_Main.fieldbyname('Orderqty').asfloat+tmponhand)+',0)';
 
                    Close;
                    sql.clear;
                    sql.Add(sqltext3);
                    try
                    execsql;
                    except
                    end;
                    tmponhand:=tmponhand+AdoQry_Main.fieldbyname('Orderqty').asfloat;
                  end;
            1:     with AdoQry_tmp do
                  begin
                   if  AdoQry_Main.fieldbyname('PmCode').asinteger<>ItemPmCode then
                     begin
                     AdoQry_Main.next;
                      while ((not AdoQry_Main.eof) and (AdoQry_Main.fieldbyname('ordinal').asinteger<>1)) do
                         AdoQry_Main.next;
                      continue;
                     end;

                  tmpgrossqty:=AdoQry_Main.fieldbyname('grossqty').asfloat;
                  tmpreleasedate:=datetostr(AdoQry_Main.fieldbyname('releasedate').asdatetime);
                  trace:=AdoQry_Main.fieldbyname('trace').asstring;
                   end;
            5:  begin
                 if AdoQry_Main.fieldbyname('netqty').asinteger>0 then
                     with AdoQry_tmp do
                  begin
                   sqltext5:='insert into #tmpResult '
                             +#13+' Values('+quotedstr(AdoQry_Main.fieldbyname('ItemCode').asstring)+','+quotedstr(inttostr(AdoQry_Main.fieldbyname('PmCode').asinteger))+','
                             +#13+'''建议下达'','+quotedstr(AdoQry_Main.fieldbyname('releasedate').asstring)+','
                             +#13+'null,'