mrp_qry_mrpinfooblongmrp_d.pas

一个MRPII系统源代码版本

unit Mrp_Qry_MrpInfoOblongMrp_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;

Type
  TFrm_Mrp_Qry_MrpInfoOblongMrp_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
    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_MrpInfoOblongMrp_D: TFrm_Mrp_Qry_MrpInfoOblongMrp_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;
{$R *.DFM}

{ TFrm_Mrp_Qry_OblongMrp }

procedure TFrm_Mrp_Qry_MrpInfoOblongMrp_D.InitForm(AdOConnection: TAdOConnection;
  ShowExtendColumn: Boolean);
var sqltext1,sqltext2,sqltext3,sqltext4,sqltext5,sqltext20,sqltext6,sqltext7,sqltext8:string;
    i:integer;
    sqltext0: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;
//--------------------------------------------------------------------\\
  sqltext0:='select ItemCode,PmCode, min(releasedate) as releasedate  '
            +'   into #tmPMrpOblongm1                        '
            +'   from MrpResult                    '
            +' where ordinal=1 '
            +'   and ItemCode='+quotedstr(ItemCode)
            +' group by ItemCode,PmCode   '
            +' select #tmPMrpOblongm1.ItemCode,#tmPMrpOblongm1.PmCode,min(MrpResult.releasedate) as releasedate'
            +'   into #tmPMrpOblongm '
            +'  from MrpResult,#tmPMrpOblongm1 '
            +' where MrpResult.ItemCode=#tmPMrpOblongm1.ItemCode and MrpResult.releasedate>=#tmPMrpOblongm1.releasedate'
            +'   and MrpResult.ordinal=3 '
            +'  group by #tmPMrpOblongm1.ItemCode,#tmPMrpOblongm1.PmCode'
            +' select distinct t1.ItemCode,t1.onhand,Assignedqty=isnull(t1.Assignedqty,0),t1.oncheck,t1.releasedate,t1.PmCode,t1.ordinal '
            +' into #tmponhand    '
            +' from MrpResult t1, #tmPMrpOblongm t2             '
            +' where t1.ItemCode=t2.ItemCode            '
            +'  and  t1.releasedate=t2.releasedate      '
            +'  and  t1.ordinal=3                       ';
  with AdoQry_tmp do
    begin
      Close;
      sql.clear;
      sql.Add(sqltext0);
      Prepared;
      try
      execsql;
      except
      end;
    end;
 //----------------------------------------------------------------------------------------------------------------------------\\
    sqltext1:='Select distinct MrpResult.ItemCode,sum(isnull(MrpResult.GrossQty,0)) As SSQty,MrpResult.releasedate as SSDate,'+
                'Item.ItemName,Itemflag=MrpResult.ItemCode+'' ''+Item.ItemName,Item.PmBatch,Item.CurrentOnHandInv,Item.minqty,MrpResult.PmCode,Item.Batchstrat,Item.Preparelt,Item.runlt,Item.Qclt,'+
               '(Case MrpResult.PmCode'+
                '  When 1 Then '+
                   ' Item. PurchLDTime '+
                 ' Else '+
                 ' Item.MNLDTime '+
                ' end) As LdTime '+
              ' into #tmPMrpOblongMrp1  '+
             ' From MrpResult(nolock) '+
               'left outer Join Item On MrpResult.ItemCode=Item.ItemCode '+
           ' Where grossqty>0 and  ordinal=1  '+
           '   and MrpResult.ItemCode='+quotedstr(ItemCode)+
           '   group by MrpResult.ItemCode,MrpResult.releasedate,Item.ItemName,Item.PmBatch,Item.CurrentonhandInv,Item.minqty,MrpResult.PmCode,Item.Batchstrat,Item.Preparelt,Item.runlt,Item.Qclt,Item.purchldtime,Item.mnldtime'+
            ' Order by MrpResult.ItemCode,MrpResult.releasedate ';
 //-------------------------------------------------------------------------------------------------------------------------------------------------------------------\\
   sqltext20:='declAre @PmCode tinyint,     '
              +'         @tmpPmCode tinyint,  '
              +'         @ordinal   tinyint,  '
              +'         @Count     int      '
              +' select top 0 *,Pmflag=0 into #tmPMrpOblongmdMrpResult1 from MrpResult '
              +' select * into #tmPMrpOblongMrpResult1 from MrpResult  where ItemCode='+quotedstr(ItemCode)
              +' select @Count=(select Count(*) from #tmPMrpOblongMrpResult1) '
              +' set rowCount 1             '
              +' insert into #tmPMrpOblongmdMrpResult1     '
              +'  select *,Pmflag=PmCode  from #tmPMrpOblongMrpResult1 '
              +' delete from #tmPMrpOblongMrpResult1                '
              +' select @PmCode=PmCode from #tmPMrpOblongmdMrpResult1  '
              +' while @Count>=1         '
               +'   begin                '
               +'    select @ordinal=ordinal from #tmPMrpOblongMrpResult1  '
                +'     insert into #tmPMrpOblongmdMrpResult1   '
                +'   select *,Pmflag=case when @ordinal=1 then PmCode else @PmCode end  from #tmPMrpOblongMrpResult1  '
                +'   if @ordinal=1                       '
                +'   select @PmCode=PmCode from #tmPMrpOblongMrpResult1   '
                +'   delete from #tmPMrpOblongMrpResult1  '
                +'   select @Count=@Count-1 '
                +'  end                   '
              + '  set rowCount 999999999';
   sqltext2:='Select distinct #tmPMrpOblongmdMrpResult1.ItemCode,ssqty=case #tmPMrpOblongmdMrpResult1.ordinal when 2 then sum(isnull(#tmPMrpOblongmdMrpResult1.OrderQty,0)) else  sum(isnull(#tmPMrpOblongmdMrpResult1.netqty,0)) end  ,'
            +' #tmPMrpOblongmdMrpResult1.releaseDate as ssdate,'+
                ' Item.ItemName,Itemflag=#tmPMrpOblongmdMrpResult1.ItemCode+'' ''+Item.ItemName,Item.PmBatch,Item.CurrentOnHandInv,Item.minqty,#tmPMrpOblongmdMrpResult1.PmCode,Item.Batchstrat,Item.Preparelt,Item.runlt,Item.Qclt, '+
                '  (Case #tmPMrpOblongmdMrpResult1.PmCode '+
                 '   When 1 Then   '+
                '     Item. PurchLDTime '+
                '   Else  '+
                '      Item.MNLDTime  '+
                '    end) As LdTime,  '+
                '           flag=case isnull(#tmPMrpOblongmdMrpResult1.ordinal,''/\\'')  '+
                              '    when 5 then ''s''    '+
                                '     else ''o''    '+
                                '     end,     '+
                '   Pmflag=#tmPMrpOblongmdMrpResult1.Pmflag '+
           '  into #tmPMrpOblongMrp2 '+
          '  From #tmPMrpOblongmdMrpResult1(nolock)  '+
           '   left outer  Join Item On #tmPMrpOblongmdMrpResult1.ItemCode=Item.ItemCode '+
          '  Where ordinal in(2,5)     '+
          '  group by #tmPMrpOblongmdMrpResult1.ItemCode,#tmPMrpOblongmdMrpResult1.Pmflag,#tmPMrpOblongmdMrpResult1.releasedate,#tmPMrpOblongmdMrpResult1.ordinal,Item.purchldtime,Item.mnldtime,Item.ItemName,Item.PmBatch,Item.CurrentonhandInv,'
          +' Item.minqty,#tmPMrpOblongmdMrpResult1.PmCode,Item.Batchstrat,'
          +' Item.Preparelt,Item.runlt,Item.Qclt'+
           '   Order by #tmPMrpOblongmdMrpResult1.ItemCode,#tmPMrpOblongmdMrpResult1.releasedate   ';
   //-------------------------------------------------------------------------------------------------------------------------------\\
  sqltext3:='  Select distinct MrpResult.ItemCode,MrpResult.onhand  AS SSQty,MrpResult.releaseDate as ssdate,'+
             '  Item.ItemName,Itemflag=MrpResult.ItemCode+'' ''+Item.ItemName,Item.PmBatch,#tmponhand.onhand-#tmponhand.Assignedqty as CurrentonhandInv,#tmponhand.Assignedqty,#tmponhand.oncheck as CurrentonCheckInv,Item.minqty,MrpResult.PmCode,'+
             '  Item.Batchstrat,Item.Preparelt,Item.runlt,Item.Qclt,'+
              '  (Case MrpResult.PmCode '+
             '     When 1 Then   '+
             '        Item. PurchLDTime  '+
            '       Else      '+
             '         Item.MNLDTime  '+
             '     end) As LdTime  '+
            '  into #tmPMrpOblongMrp3   '+
           '  From MrpResult(nolock)      '+
            '       Join Item On MrpResult.ItemCode=Item.ItemCode '+
            '     join #tmponhand on MrpResult.ItemCode=#tmponhand.ItemCode and MrpResult.PmCode=#tmponhand.PmCode'+
           '  Order by MrpResult.ItemCode,MrpResult.releasedate ';
//-------------------------------------------------------------------------------------------------------------------------------------------------------------------------\\
  sqltext4:='Select Top 0  物料标识=Itemflag,物料代码=ItemCode,物料描述=ItemName,供需项目=''0'',期初=null,批量=PmBatch,提前期=LdTime,当前库存=CurrentonhandInv,MO已领量=Assignedqty,当前待检库存=CurrentonCheckInv,minqty,PmCode,Batchstrat,Preparelt,runlt,Qclt '+
            '  Into #tmPMrpOblongMrp4  '+
          '  From #tmPMrpOblongMrp3  '+
          '  alter table #tmPMrpOblongMrp4 Add stArtqty   float  '+
          '  alter table #tmPMrpOblongMrp4 Add SupplyType varchAr(50)  ';
//-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\\

  sqltext5:= 'select 物料标识,物料代码,物料描述,供需项目=SupplyType,期初=stArtqty,批量,提前期,当前库存,MO已领量,当前待检库存,minqty,PmCode,Batchstrat,Preparelt,runlt,Qclt'+
             ' into #tmPMrpOblongMrp '+
             '  from #tmPMrpOblongMrp4 ';
//------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\\
  sqltext6:='insert into #tmPMrpOblongMrp  '+
            '  select distinct Itemflag,ItemCode,ItemName,SupplyType=''0'',null,PmBatch,ldtime, CurrentonhandInv=0,0,0,minqty,PmCode,Batchstrat,Preparelt,runlt,Qclt from #tmPMrpOblongMrp1 '+
          '  insert into #tmPMrpOblongMrp  '+
             '  select distinct Itemflag,ItemCode,ItemName,SupplyType=''1'',null,PmBatch,ldtime,CurrentonhandInv=0,0,0,minqty,PmCode,Batchstrat,Preparelt,runlt,Qclt from #tmPMrpOblongMrp2 '+
          '  insert into #tmPMrpOblongMrp  '+
          '  select distinct Itemflag,ItemCode,ItemName,SupplyType=''12'',null,PmBatch,ldtime,CurrentonhandInv=0,0,0,minqty,PmCode,Batchstrat,Preparelt,runlt,Qclt from #tmPMrpOblongMrp1 '+
          '  insert into #tmPMrpOblongMrp   '+
               '  select distinct Itemflag,ItemCode,ItemName,SupplyType=''2'',null,PmBatch,ldtime,CurrentonhandInv,Assignedqty,CurrentonCheckInv,minqty,PmCode,Batchstrat,Preparelt,runlt,Qclt from #tmPMrpOblongMrp3';
//----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\\
   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'''+
             ' select @selectType='+inttostr(cmbselectType.Itemindex)+
             ' select @maxdate=(select max(releasedate) from MrpResult where ItemCode='+quotedstr(ItemCode)+') '+
             ' select @mindate=(select min(releasedate) from MrpResult where ItemCode='+quotedstr(ItemCode)+') '+
             ' while @mindate<=@maxdate  '+
             '   begin                   '+
             '     if datepArt(mm,@mindate)<10  '+
             '        select @mm=''0''+convert(varchAr,datepArt(mm,@mindate))  '+