mrp_qry_newoblongmrp_d.pas

一个MRPII系统源代码版本

                   '  @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 #tttMrpResult where ItemCode='+quotedstr(ItemCode)+') '+
             ' select @mindate=(select min(releasedate) from #tttMrpResult where ItemCode='+quotedstr(ItemCode)+') '+
             ' while @mindate<=@maxdate  '+
             '   begin                   '+
             '     if datepArt(mm,@mindate)<10  '+
             '        select @mm=''0''+convert(varchAr,datepArt(mm,@mindate))  '+
              '    else select @mm=convert(varchAr,datepArt(mm,@mindate))  '+
                '  if datepArt(dd,@mindate)<10  '+
              '       select @day=''0''+convert(varchAr,datepArt(dd,@mindate)) '+
              '    else  select @day=convert(varchAr,datepArt(dd,@mindate)) '+
             '   select @Monthbegin=convert(chAr(4),datepArt(yy,@mindate))+''.''+convert(chAr(2),datepArt(mm,@mindate))+''.01'' '+
           '  select @outputweek=datepArt(wk,@mindate)-datepArt(wk,@Monthbegin)+1 '+
             '    if @selectType=0       '+
              '   begin '+
            '      select @s=''[''+substring(convert(varchAr,datepArt(yy,@mindate)),3,2)+@mm+@day+'']''  '+
              '   select @update=@s   '+
              '   select @s=''alter table #TmpOblongMrp Add ''+@s+''  float''   '+
              '   exec(@s)  '+
                ' end   '+
                '   else    '+
              '  if @selectType=1  '+
              '     begin  '+
//                  '  select @s=''[''+convert(varchAr,datepArt(yy,@mindate))+''年''+@mm+''月第''+convert(varchAr,@outputweek)+''周]'''+
              '       declAre @ddr datetime '+
              '       select @ddr=@mindate '+
              '        while dateName(yy,@ddr)+dateName(wk,@ddr)<>dateName(yy,@ddr)+convert(varchAr,datepArt(wk,@mindate)+1) '+
              '         begin '             +
              '          select  @ddr=@ddr+1      '+
              '         end '  +
             //    '  select @s=''[''+convert(varchAr,datepArt(yy,@mindate))+''年''+@mm+''月第''+convert(varchAr,@outputweek)+''周]'''+
                  '  select @s=''[''+convert(varchAr,@mindate,12)+''到''+convert(varchAr,case when dateName(yy,@mindate)+dateName(mm,@mindate)<>  '
                +'  dateName(yy,dateAdd(wk,1,@mindate)-1)+dateName(mm,dateAdd(wk,1,@mindate)-1)                   '
                + '  then convert(datetime,dateName(yy,@mindate)+''.''+dateName(mm,dateAdd(mm,1,@mindate))+''.01'')-1     '
              + ' else @ddr-1   '
              + '     end,12)     +'']'''+
                  '  if (convert(varchAr,datepArt(yy,@mindate))+@mm+convert(varchAr,@outputweek))<>@sameweek  '+
                     '   begin '+
                      '   select @update=@s   '+
                      '   select @s=''alter table #TmpOblongMrp Add ''+@s+''  float''   '+
                      '   exec(@s)  '+
                     '   end    '+
                  '  select @sameweek=convert(varchAr,datepArt(yy,@mindate))+@mm+convert(varchAr,@outputweek)'+
                '   end    '+
              '  else if @selectType=2   '+
                '      begin   '+
                   '   select @s=''[''+convert(varchAr,datepArt(yy,@mindate))+''年''+@mm+''月]'''+
                      '  if (convert(varchAr,datepArt(yy,@mindate))+convert(varchAr,datepArt(mm,@mindate)))<>@sameMonth  '+
                       '   begin '+
                        '   select @update=@s   '+
                        '   select @s=''alter table #TmpOblongMrp Add ''+@s+''  float''   '+
                        '   exec(@s)  '+
                       '   end    '+
                        '  select @sameMonth=convert(varchAr,datepArt(yy,@mindate))+convert(varchAr,datepArt(mm,@mindate))'+
                   '   end     '+
              ' if @selectType=0    '+
              '  begin   '+
              '   select @update1=''update #TmpOblongMrp set ''+@update+''=(select sum(isnull(ssqty,0)) from #TmpOblongMrp1 where convert(varchAr,ssdate)=''''''+convert(varchAr,@mindate)+''''''''+''and #TmpOblongMrp.物料代码=#TmpOblongMrp1.ItemCode '
              +' and #TmpOblongMrp.groupordinal=#TmpOblongMrp1.groupordinal'
               +')''' +
              '   select @update1=@update1+'' where 供需项目=0'' '+
              '   select @update2=''update #TmpOblongMrp set ''+@update+''=(select sum(isnull(ssqty,0)) from #TmpOblongMrp2 where convert(varchAr,ssdate)=''''''+convert(varchAr,@mindate)+'''''''' '+
              '   select @update2=@update2+''and #TmpOblongMrp.物料代码=#TmpOblongMrp2.ItemCode and #TmpOblongMrp.groupordinal=#TmpOblongMrp2.groupordinal and #TmpOblongMrp2.flag=''''s'''''' '+
              '   select @update2=@update2+'')'''+
              '   select @update2=@update2+'' where 供需项目=1'' '+
              '   select @update22=''update #TmpOblongMrp set ''+@update+''=(select sum(isnull(ssqty,0)) from #TmpOblongMrp2 where convert(varchAr,ssdate)=''''''+convert(varchAr,@mindate)+'''''''' '+
              '   select @update22=@update22+''and #TmpOblongMrp.物料代码=#TmpOblongMrp2.ItemCode and #TmpOblongMrp.groupordinal=#TmpOblongMrp2.groupordinal and #TmpOblongMrp2.flag=''''o''''''+'')'' '+
              '   select @update22=@update22+'' where 供需项目=12'' '+
              '    select @update3=''update #TmpOblongMrp set ''+@update+''=(select sum(isnull(#TmpOblongMrp2.ssqty,0)) from #TmpOblongMrp2 where #TmpOblongMrp2.ssdate<=''  '+
              '    select @update3=@update3+''''''''+convert(varchAr,datepArt(yyyy,@mindate))+''.''+convert(varchAr,datepArt(mm,@mindate))+''.''+convert(varchAr,datepArt(dd,@mindate))+'''''''' '+
              '    select @update3=@update3+''  and #TmpOblongMrp.物料代码*=#TmpOblongMrp2.ItemCode and #TmpOblongMrp.groupordinal*=#TmpOblongMrp2.groupordinal)''                                                                                             '+
              '    select @update3=@update3+''-(select sum(isnull(#TmpOblongMrp1.ssqty,0)) from #TmpOblongMrp1 where #TmpOblongMrp1.ssdate<=''                                                           '+
              '    select @update3=@update3+''''''''+convert(varchAr,datepArt(yyyy,@mindate))+''.''+convert(varchAr,datepArt(mm,@mindate))+''.''+convert(varchAr,datepArt(dd,@mindate))+'''''''' '+
              '    select @update3=@update3+''  and #TmpOblongMrp.物料代码=#TmpOblongMrp1.ItemCode and #TmpOblongMrp.groupordinal=#TmpOblongMrp1.groupordinal)+#TmpOblongMrp.当前可用库存+#TmpOblongMrp.当前待检库存  where  #TmpOblongMrp.供需项目=2''   '+
              '    select @update4=''update #TmpOblongMrp set ''+@update+''=null where ''+@update+''=0 and 供需项目=2'''+
              '  end  else   '+
              ' if @selectType=1    '+
              '  begin   '+
              #13+'   select @update1=''update #TmpOblongMrp set ''+@update+''=(select sum(isnull(ssqty,0)) from #TmpOblongMrp1 where datepArt(yy,ssdate)=''+convert(varchAr,datepArt(yy,@mindate))+'' and datepArt(mm,ssdate)=''+convert(varchAr,datepArt(mm,@mindate))'+
              #13+'   select @update1=@update1+'' and ''+convert(varchAr,datepArt(wk,@mindate)-datepArt(wk,convert(chAr(4),datepArt(yy,@mindate))+''.''+convert(chAr(2),datepArt(mm,@mindate))+''.01''))  '+
              #13+'   select @update1=@update1+''=datepArt(wk,ssdate)-datepArt(wk,convert(chAr(4),datepArt(yy,ssdate))+''''.''''+convert(chAr(2),datepArt(mm,ssdate))+''''.01'''')''    '+
              #13+'   select @update1=@update1+''and #TmpOblongMrp.物料代码=#TmpOblongMrp1.ItemCode and #TmpOblongMrp.groupordinal=#TmpOblongMrp1.groupordinal''+'')'''+
              #13+'   select @update1=@update1+'' where 供需项目=0'' '+
              #13+'   select @update2=''update #TmpOblongMrp set ''+@update+''=(select sum(isnull(ssqty,0)) from #TmpOblongMrp2 where datepArt(yy,ssdate)=''+convert(varchAr,datepArt(yy,@mindate))+'' and datepArt(mm,ssdate)=''+convert(varchAr,datepArt(mm,@mindate))'+
              #13+'   select @update2=@update2+'' and ''+convert(varchAr,datepArt(wk,@mindate)-datepArt(wk,convert(chAr(4),datepArt(yy,@mindate))+''.''+convert(chAr(2),datepArt(mm,@mindate))+''.01''))     '+
              #13+'   select @update2=@update2+''=datepArt(wk,ssdate)-datepArt(wk,convert(chAr(4),datepArt(yy,ssdate))+''''.''''+convert(chAr(2),datepArt(mm,ssdate))+''''.01'''')''       '+
              #13+'   select @update2=@update2+''and #TmpOblongMrp.物料代码=#TmpOblongMrp2.ItemCode and #TmpOblongMrp.groupordinal=#TmpOblongMrp2.groupordinal and #TmpOblongMrp2.flag=''''s''''''+'')''+'' where 供需项目=1'' '+
              #13+'   select @update22=''update #TmpOblongMrp set ''+@update+''=(select sum(isnull(ssqty,0)) from #TmpOblongMrp2 where datepArt(yy,ssdate)=''+convert(varchAr,datepArt(yy,@mindate))+'' and datepArt(mm,ssdate)=''+convert(varchAr,datepArt(mm,@mindate))'+
              #13+'   select @update22=@update22+'' and ''+convert(varchAr,datepArt(wk,@mindate)-datepArt(wk,convert(chAr(4),datepArt(yy,@mindate))+''.''+convert(chAr(2),datepArt(mm,@mindate))+''.01''))    '+
              #13+'   select @update22=@update22+''=datepArt(wk,ssdate)-datepArt(wk,convert(chAr(4),datepArt(yy,ssdate))+''''.''''+convert(chAr(2),datepArt(mm,ssdate))+''''.01'''')''    '+
              #13+'   select @update22=@update22+''and #TmpOblongMrp.物料代码=#TmpOblongMrp2.ItemCode and #TmpOblongMrp.groupordinal=#TmpOblongMrp2.groupordinal and #TmpOblongMrp2.flag=''''o''''''+'')''+'' where 供需项目=12'' '+
              '   select @update3=''update #TmpOblongMrp set ''+@update+''=(select sum(isnull(#TmpOblongMrp2.ssqty,0)) from #TmpOblongMrp2 where datepArt(mm,ssdate)<=''+convert(varchAr,datepArt(mm,@mindate)) '+
              '     select @update3=@update3+'' and convert(varchAr,datepArt(yyyy,#TmpOblongMrp2.ssdate))+''''.''''+convert(varchAr,datepArt(wk,#TmpOblongMrp2.ssdate))<='''+
              '     select @update3=@update3+''''''''+convert(varchAr,datepArt(yyyy,@mindate))+''.''+convert(varchAr,datepArt(wk,@mindate))+'''''''''+
              '     select @update3=@update3+''  and #TmpOblongMrp.物料代码*=#TmpOblongMrp2.ItemCode and #TmpOblongMrp.groupordinal*=#TmpOblongMrp2.groupordinal)'''+
              '     select @update3=@update3+''-(select sum(isnull(#TmpOblongMrp1.ssqty,0)) from #TmpOblongMrp1 where datepArt(mm,#TmpOblongMrp1.ssdate)<=''+convert(varchAr,datepArt(mm,@mindate))'+
              '     select @update3=@update3+'' and convert(varchAr,datepArt(yyyy,#TmpOblongMrp1.ssdate))+''''.''''+convert(varchAr,datepArt(wk,#TmpOblongMrp1.ssdate))<='''+
              '   select @update3=@update3+''''''''+convert(varchAr,datepArt(yyyy,@mindate))+''.''+convert(varchAr,datepArt(wk,@mindate))+'''''''''+
              ' select @update3=@update3+''  and #TmpOblongMrp.物料代码=#TmpOblongMrp1.ItemCode and #TmpOblongMrp.groupordinal=#TmpOblongMrp1.groupordinal)+#TmpOblongMrp.当前可用库存+#TmpOblongMrp.当前待检库存  where  #TmpOblongMrp.供需项目=2'''+
              '    select @update4=''update #TmpOblongMrp set ''+@update+''=null where ''+@update+''=0 and 供需项目=2'''
              +'  end   else  '+
              ' if @selectType=2    '+
              '  begin   '+
              #13+'   select @update1=''update #TmpOblongMrp set ''+@update+''=(select sum(isnull(ssqty,0)) from #TmpOblongMrp1 where datepArt(yy,ssdate)=''+convert(varchAr,datepArt(yy,@mindate))+'' and datepArt(mm,ssdate)=''+convert(varchAr,datepArt(mm,@mindate))'+
              #13+'   select @update1=@update1+''and #TmpOblongMrp.物料代码=#TmpOblongMrp1.ItemCode and #TmpOblongMrp.groupordinal=#TmpOblongMrp1.groupordinal''+'')'''+
              #13+'   select @update1=@update1+'' where 供需项目=0'' '+
              #13+'   select @update2=''update #TmpOblongMrp set ''+@update+''=(select sum(isnull(ssqty,0)) from #TmpOblongMrp2 where datepArt(yy,ssdate)=''+convert(varchAr,datepArt(yy,@mindate))+'' and datepArt(mm,ssdate)=''+convert(varchAr,datepArt(mm,@mindate))'+
              #13+'   select @update2=@update2+''and #TmpOblongMrp.物料代码=#TmpOblongMrp2.ItemCode and #TmpOblongMrp.groupordinal=#TmpOblongMrp2.groupordinal and #TmpOblongMrp2.flag=''''s''''''+'')''+'' where 供需项目=1'' '+
              #13+'   select @update22=''update #TmpOblongMrp set ''+@update+''=(select sum(isnull(ssqty,0)) from #TmpOblongMrp2 where datepArt(yy,ssdate)=''+convert(varchAr,datepArt(yy,@mindate))+'' and datepArt(mm,ssdate)=''+convert(varchAr,datepArt(mm,@mindate))'+
              #13+'   select @update22=@update22+''and #TmpOblongMrp.物料代码=#TmpOblongMrp2.ItemCode and #TmpOblongMrp.groupordinal=#TmpOblongMrp2.groupordinal and #TmpOblongMrp2.flag=''''o''''''+'')''+'' where 供需项目=12'' '+
              '    select @update3=''update #TmpOblongMrp set ''+@update+''=(select sum(isnull(#TmpOblongMrp2.ssqty,0)) from #TmpOblongMrp2 where #TmpOblongMrp2.ssdate<=''  '+
              '    select @update3=@update3+''''''''+convert(varchAr,datepArt(yyyy,@mindate))+''.''+convert(varchAr,datepArt(mm,@mindate))+''.''+convert(varchAr,datepArt(dd,@mindate))+'''''''' '+
              '    select @update3=@update3+''  and #TmpOblongMrp.物料代码*=#TmpOblongMrp2.ItemCode and #TmpOblongMrp.groupordinal*=#TmpOblongMrp2.groupordinal)''                                                                                             '+
              '    select @update3=@update3+''-(select sum(isnull(#TmpOblongMrp1.ssqty,0)) from #TmpOblongMrp1 where #TmpOblongMrp1.ssdate<=''                                                           '+
              '    select @update3=@update3+''''''''+convert(varchAr,datepArt(yyyy,@mindate))+''.''+convert(varchAr,datepArt(mm,@mindate))+''.''+convert(varchAr,datepArt(dd,@mindate))+'''''''' '+
              '    select @update3=@update3+''  and #TmpOblongMrp.物料代码=#TmpOblongMrp1.ItemCode and #TmpOblongMrp.groupordinal=#TmpOblongMrp1.groupordinal)+#TmpOblongMrp.当前可用库存+#TmpOblongMrp.当前待检库存  where  #TmpOblongMrp.供需项目=2''   '+
             '    select @update4=''update #TmpOblongMrp set ''+@update+''=null where ''+@update+''=0 and 供需项目=2'''
                       +'  end     '+
              '   exec(@update1)  '+
              '   exec(@update2)   '+
              '   exec(@update22)  '+
              '   exec(@update3)   '+
              '   exec(@update4)   '+
              '   select @mindate=@mindate+1   '+
              ' end  ';
//---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\\
  Application.ProcessMessages;
     sqltext8:='update #TmpOblongMrpResult '+
             '  set 供需项目=case 供需项目  '+
                          '  when ''0'' then  ''需求量(剩余需求)'''+
                          '  when ''1'' then ''系统建议量'''+
                          '  when ''12'' then ''订单未结量'''+
                          '  when ''2'' then ''计划可用量(预计库存量)'''+
                          ' end ';
//---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\\
selectfromsql:='select * from #TmpOblongMrpResult';
       Frm_Mrp_Qry_OblongMrp_m.Visible:=True;
       Screen.Cursor:=crsqlwait;

   with AdoQry_Main do
     begin
       Close;
       sql.clear;
       sql.Add(sqltext1);
       Prepared;
       try
       execsql;
       except
       end;
  Application.ProcessMessages;
       Close;
       sql.clear;
       sql.Add(sqltext20);
       Prepared;
       try
       execsql;
       except
       end;
  Application.ProcessMessages;
       Close;
       sql.clear;
       sql.Add(sqltext2);
       Prepared;
       try
       execsql;
       except
       end;

       Close;
       sql.clear;
       sql.Add(sqltext3);
       Prepared;
       try
       execsql;
       except
       end;
  Application.ProcessMessages;
       Close;
       sql.clear;
       sql.Add(sqltext4);
       Prepared;
       try
       execsql;
       except
       end;
   Application.ProcessMessages;
       Close;
       sql.clear;
       sql.Add(sqltext5);
       Prepared;
       try
       execsql;
       except
       end;
   Application.ProcessMessages;
       Close;
       sql.clear;
       sql.Add(sqltext6);
       Prepared;
       try
       execsql;
       except
       end;
  Application.ProcessMessages;

       try
       Close;
       sql.clear;
       sql.Add(sqltext7);
       Prepared;
       try
       Application.ProcessMessages;
         execsql;
       except
       end;
        Frm_Mrp_Qry_OblongMrp_m.Visible:=False;
               Frm_Mrp_Qry_OblongMrp_m.Close;
               Application.ProcessMessages;
       finally
        Frm_Mrp_Qry_OblongMrp_m.Free;
       Screen.Cursor:=crdefault;
       end;
      { Close;
       sql.clear;
       sql.Add(sqltext7);
       Prepared;
       try
       execsql;
       except
       showmessage('error');
       end;  }
       Close;
       sql.clear;
       sql.Add('select * into #TmpOblongMrpResult from #TmpOblongMrp Order by 物料代码,供需项目');
       Prepared;
       try
       execsql;
       except
       end;