mrp_qry_oblongmrp_d.pas

一个MRPII系统源代码版本

unit Mrp_Qry_OblongMrp_D;

Interface

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

Type
  TFrm_Mrp_Qry_OblongMrp_D = Class(TFrm_Base_Qry)
    lbl_ItemCode: TLabel;
    Label1: TLabel;
    lbl_ItemName: TLabel;
    Label2: TLabel;
    lbl_ldtime: TLabel;
    lbl_PmBatch: TLabel;
    Label3: TLabel;
    lbl_CurrentInv: TLabel;
    Label4: TLabel;
    Label5: TLabel;
    cmbselectType: TComboBox;
    Adodataset_Main: TAdODataSet;
    AdOCommand: TAdOCommand;
    Label6: TLabel;
    lbl_CurrentonCheckInv: TLabel;
    Label7: TLabel;
    lbl_PmCode: TLabel;
    Label9: TLabel;
    lbl_minqty: TLabel;
    Label11: TLabel;
    lbl_Batchstrat: TLabel;
    Label8: TLabel;
    lbl_Mrprundate: TLabel;
    Label10: TLabel;
    lbl_Preparelt: TLabel;
    Label13: TLabel;
    lbl_runlt: TLabel;
    lbl_Qclt: TLabel;
    Label16: TLabel;
    Label12: TLabel;
    lbl_Assignedqty: TLabel;
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure FormDestroy(Sender: TObject);
    procedure DBGridEhGetCellParams(Sender: TObject; Column: TColumnEh;
      AFont: TFont; var Background: TColor; State: TGridDrawState);
    procedure cmbselectTypeChange(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure AdoQry_MainAfterScroll(DataSet: TDataSet);
    procedure Act_LookExecute(Sender: TObject);
    procedure Act_ShowGridExecute(Sender: TObject);
    procedure FormShow(Sender: TObject);
  private
  function getValue(Code:string;tableName:string):variant;
  public
    mRunMrp:integer;
    ItemCode:string;
    procedure InitForm(AdOConnection:TAdOConnection;ShowExtendColumn:Boolean);Override;
  function ismorethanzero(fieldName:string;tableName:string;conditionfield:string):boolean;
    { Public declarations }
  end;

var
  Frm_Mrp_Qry_OblongMrp_D: TFrm_Mrp_Qry_OblongMrp_D;

implementation

 uses Mrp_Qry_OblongMrp_m,Sys_Global, Mrp_Qry_InformalUpRightMrp_D_ParentMo,
  Mrp_Qry_InformalTotalMrp_Po, Mrp_Qry_InformalTotalMrp_Mo,
  Mrp_Qry_InformalTotalMrp_vd,Mrp_Global;
{$R *.DFM}

{ TFrm_Mrp_Qry_OblongMrp }

procedure TFrm_Mrp_Qry_OblongMrp_D.InitForm(AdOConnection: TAdOConnection;
  ShowExtendColumn: Boolean);
var sqltext1,sqltext2,sqltext3,sqltext4,sqltext5,sqltext20,sqltext6,sqltext7,sqltext8:string;
    i:integer;
    sqltext0:string;
    success:integer;
    tMpsql:string;
begin
  inherited;
  lbl_Mrprundate.Caption:=getValue('RunMrpdate','MrpParam');
  Frm_Mrp_Qry_OblongMrp_m:=TFrm_Mrp_Qry_OblongMrp_m.Create(self);
  Adocommand.Connection:=AdoQry_Main.Connection;
  if mRunMrp=1 then
  TemporArilyRunMrp(dbconnect,'select ItemCode from MrpResult where ordinal=1 and ItemCode='+quotedstr(ItemCode),1);
  tMpsql:='select * into #tttMrpResult from MrpResult'
         +' where ItemCode='+quotedstr(ItemCode);
  Executesql(AdoQry_tmp,tMpsql,1);
//--------------------------------------------------------------------\\
  Application.ProcessMessages;
  sqltext0:='select ItemCode,PmCode, min(releasedate) as releasedate  '
            +'   into #TmpOblongm1                        '
            +'   from #tttMrpResult                    '
            +' where ordinal=1 '
            +'   and ItemCode='+quotedstr(ItemCode)
            +' group by ItemCode,PmCode   '
            +' select #TmpOblongm1.ItemCode,#TmpOblongm1.PmCode,min(#tttMrpResult.releasedate) as releasedate'
            +'   into #TmpOblongm '
            +'  from #tttMrpResult,#TmpOblongm1 '
            +' where #tttMrpResult.ItemCode=#TmpOblongm1.ItemCode and #tttMrpResult.releasedate>=#TmpOblongm1.releasedate'
            +'   and #tttMrpResult.ordinal=3 '
            +'  group by #TmpOblongm1.ItemCode,#TmpOblongm1.PmCode'
            +' select distinct t1.ItemCode,t1.onhand,Assignedqty=isnull(t1.Assignedqty,0),t1.oncheck,t1.releasedate,t1.PmCode,t1.ordinal '
            +' into #tmponhand    '
            +' from #tttMrpResult t1, #TmpOblongm t2             '
            +' where t1.ItemCode=t2.ItemCode            '
            +'  and  t1.releasedate=t2.releasedate      '
            +'  and  t1.ordinal=3                       ';
  Application.ProcessMessages;
   with AdoQry_tmp do
    begin
      Close;
      sql.clear;
      sql.Add(sqltext0);
      Prepared;
      try
      execsql;
      except
      end;
    end;
 //----------------------------------------------------------------------------------------------------------------------------\\
    sqltext1:='Select distinct #tttMrpResult.ItemCode,sum(isnull(#tttMrpResult.GrossQty,0)) As SSQty,#tttMrpResult.releasedate as SSDate,'+
                'Item.ItemName,Itemflag=#tttMrpResult.ItemCode+'' ''+Item.ItemName,Item.PmBatch,Item.CurrentOnHandInv,Item.minqty,#tttMrpResult.PmCode,Item.Batchstrat,Item.Preparelt,Item.runlt,Item.Qclt,'+
               '(Case #tttMrpResult.PmCode'+
                '  When 1 Then '+
                   ' Item. PurchLDTime '+
                 ' Else '+
                 ' Item.MNLDTime '+
                ' end) As LdTime '+
              ' into #TmpOblongMrp1  '+
             ' From #tttMrpResult(nolock) '+
             'left outer Join Item On #tttMrpResult.ItemCode=Item.ItemCode '+
           ' Where grossqty>0 and  ordinal=1  '+
           '   and #tttMrpResult.ItemCode='+quotedstr(ItemCode)+
           '   group by #tttMrpResult.ItemCode,#tttMrpResult.releasedate,Item.ItemName,Item.PmBatch,Item.CurrentonhandInv,Item.minqty,#tttMrpResult.PmCode,Item.Batchstrat,Item.Preparelt,Item.runlt,Item.Qclt,Item.purchldtime,Item.mnldtime'+
            ' Order by #tttMrpResult.ItemCode,#tttMrpResult.releasedate ';
 //-------------------------------------------------------------------------------------------------------------------------------------------------------------------\\
  Application.ProcessMessages;
     sqltext20:='declAre @PmCode tinyint,     '
              +'         @tmpPmCode tinyint,  '
              +'         @ordinal   tinyint,  '
              +'         @Count     int      '
              +' select top 0 *,Pmflag=0 into #TmpOblongmdMrpResult1 from #tttMrpResult '
              +' select * into #TmpOblongMrpResult1 from #tttMrpResult  where ItemCode='+quotedstr(ItemCode)
              +' select @Count=(select Count(*) from #TmpOblongMrpResult1) '
              +' set rowCount 1             '
              +' insert into #TmpOblongmdMrpResult1     '
              +'  select *,Pmflag=PmCode  from #TmpOblongMrpResult1 '
              +' delete from #TmpOblongMrpResult1                '
              +' select @PmCode=PmCode from #TmpOblongmdMrpResult1  '
              +' while @Count>=1         '
               +'   begin                '
               +'    select @ordinal=ordinal from #TmpOblongMrpResult1  '
                +'     insert into #TmpOblongmdMrpResult1   '
                +'   select *,Pmflag=case when @ordinal=1 then PmCode else @PmCode end  from #TmpOblongMrpResult1  '
                +'   if @ordinal=1                       '
                +'   select @PmCode=PmCode from #TmpOblongMrpResult1   '
                +'   delete from #TmpOblongMrpResult1  '
                +'   select @Count=@Count-1 '
                +'  end                   '
              + '  set rowCount 999999999';
   sqltext2:='Select distinct #TmpOblongmdMrpResult1.ItemCode,ssqty=case #TmpOblongmdMrpResult1.ordinal when 2 then sum(isnull(#TmpOblongmdMrpResult1.OrderQty,0)) else  sum(isnull(#TmpOblongmdMrpResult1.netqty,0)) end  ,#TmpOblongmdMrpResult1.releaseDate as ssdate,'+
                ' Item.ItemName,Itemflag=#TmpOblongmdMrpResult1.ItemCode+'' ''+Item.ItemName,Item.PmBatch,Item.CurrentOnHandInv,Item.minqty,#TmpOblongmdMrpResult1.PmCode,Item.Batchstrat,Item.Preparelt,Item.runlt,Item.Qclt, '+
                '  (Case #TmpOblongmdMrpResult1.PmCode '+
                 '   When 1 Then   '+
                '     Item. PurchLDTime '+
                '   Else  '+
                '      Item.MNLDTime  '+
                '    end) As LdTime,  '+
                '           flag=case isnull(#TmpOblongmdMrpResult1.ordinal,''/\\'')  '+
                              '    when 5 then ''s''    '+
                                '     else ''o''    '+
                                '     end,     '+
                '   Pmflag=#TmpOblongmdMrpResult1.Pmflag '+
           '  into #TmpOblongMrp2 '+
          '  From #TmpOblongmdMrpResult1(nolock)  '+
           '   left outer  Join Item On #TmpOblongmdMrpResult1.ItemCode=Item.ItemCode '+
          '  Where ordinal in(2,5)     '+
          '  group by #TmpOblongmdMrpResult1.ItemCode,#TmpOblongmdMrpResult1.Pmflag,#TmpOblongmdMrpResult1.releasedate,#TmpOblongmdMrpResult1.ordinal,Item.purchldtime,Item.mnldtime,Item.ItemName,Item.PmBatch,Item.CurrentonhandInv,'
          +' Item.minqty,#TmpOblongmdMrpResult1.PmCode,Item.Batchstrat,'
          +' Item.Preparelt,Item.runlt,Item.Qclt'+
           '   Order by #TmpOblongmdMrpResult1.ItemCode,#TmpOblongmdMrpResult1.releasedate   ';
   //-------------------------------------------------------------------------------------------------------------------------------\\
  Application.ProcessMessages;
    sqltext3:='  Select distinct #tttMrpResult.ItemCode,#tttMrpResult.onhand  AS SSQty,#tttMrpResult.releaseDate as ssdate,'+
             '  Item.ItemName,Itemflag=#tttMrpResult.ItemCode+'' ''+Item.ItemName,Item.PmBatch,#tmponhand.onhand-#tmponhand.Assignedqty as CurrentonhandInv,#tmponhand.Assignedqty,#tmponhand.oncheck as CurrentonCheckInv,Item.minqty,#tttMrpResult.PmCode,'+
             '  Item.Batchstrat,Item.Preparelt,Item.runlt,Item.Qclt,'+
              '  (Case #tttMrpResult.PmCode '+
             '     When 1 Then   '+
             '        Item.PurchLDTime  '+
            '       Else      '+
             '         Item.MNLDTime  '+
             '     end) As LdTime  '+
            '  into #TmpOblongMrp3   '+
           '  From #tttMrpResult(nolock)      '+
            '       Join Item On #tttMrpResult.ItemCode=Item.ItemCode '+
            '     join #tmponhand on #tttMrpResult.ItemCode=#tmponhand.ItemCode and #tttMrpResult.PmCode=#tmponhand.PmCode'+
           '  Order by #tttMrpResult.ItemCode,#tttMrpResult.releasedate ';
//-------------------------------------------------------------------------------------------------------------------------------------------------------------------------\\
  sqltext4:='Select Top 0  物料标识=Itemflag,物料代码=ItemCode,物料描述=ItemName,供需项目=''0'',期初=null,批量=PmBatch,提前期=LdTime,当前可用库存=CurrentonhandInv,订单已领量=Assignedqty,当前待检库存=CurrentonCheckInv,minqty,PmCode,Batchstrat,Preparelt,runlt,Qclt '+
          '  Into #TmpOblongMrp4  '+
          '  From #TmpOblongMrp3  '+
          '  alter table #TmpOblongMrp4 Add stArtqty   float  '+
          '  alter table #TmpOblongMrp4 Add SupplyType varchAr(50)  ';
//-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\\
  Application.ProcessMessages;
  sqltext5:= 'select 物料标识,物料代码,物料描述,供需项目=SupplyType,期初=stArtqty,批量,提前期,当前可用库存,订单已领量,当前待检库存,minqty,PmCode,Batchstrat,Preparelt,runlt,Qclt'+
             ' into #TmpOblongMrp '+
             '  from #TmpOblongMrp4 ';
//------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\\
  sqltext6:='insert into #TmpOblongMrp  '+
            '  select distinct Itemflag,ItemCode,ItemName,SupplyType=''0'',null,PmBatch,ldtime, CurrentonhandInv=0,0,0,minqty,PmCode,Batchstrat,Preparelt,runlt,Qclt from #TmpOblongMrp1 '+
          '  insert into #TmpOblongMrp  '+
             '  select distinct Itemflag,ItemCode,ItemName,SupplyType=''1'',null,PmBatch,ldtime,CurrentonhandInv=0,0,0,minqty,PmCode,Batchstrat,Preparelt,runlt,Qclt from #TmpOblongMrp2 '+
          '  insert into #TmpOblongMrp  '+
          '  select distinct Itemflag,ItemCode,ItemName,SupplyType=''12'',null,PmBatch,ldtime,CurrentonhandInv=0,0,0,minqty,PmCode,Batchstrat,Preparelt,runlt,Qclt from #TmpOblongMrp1 '+
          '  insert into #TmpOblongMrp   '+
               '  select distinct Itemflag,ItemCode,ItemName,SupplyType=''2'',null,PmBatch,ldtime,CurrentonhandInv,Assignedqty,CurrentonCheckInv,minqty,PmCode,Batchstrat,Preparelt,runlt,Qclt from #TmpOblongMrp3';
//----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\\
  Application.ProcessMessages;
     sqltext7:='declAre @maxdate datetime, '+
                   '  @mindate datetime, '+
                   '  @Monthbegin datetime,'+
                   '  @outputweek tinyint,'+
                   '  @s       varchAr(80), '+
                   '  @update  varchAr(300), '+
                   '  @update1 varchAr(900),  '+
                   '  @update2 varchAr(900), '+
                   '  @update22 varchAr(900), '+
                   '  @update3 varchAr(900), '+
                   '  @update4 varchAr(200), '+
                   '  @mm      varchAr(2),  '+
                   '  @day     varchAr(2),   '+
                   '  @selectType tinyint,   '+
                   '  @sameweek   varchAr(18), '+
                   '  @sameMonth  varchAr(18)  '+
             ' select @sameweek=''ddf'''+
             ' select @sameMonth=''dfd'''+