mrp_qry_mrpinfooblongmrp_d.pas

一个MRPII系统源代码版本

              '    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 #tmPMrpOblongMrp Add ''+@s+''  float''   '+
              '   exec(@s)  '+
                ' end   '+
                '   else    '+
              '  if @selectType=1  '+
              '     begin  '+
                  '  select @s=''[''+convert(varchAr,datepArt(yy,@mindate))+''年''+@mm+''月第''+convert(varchAr,@outputweek)+''周]'''+
                  '  if (convert(varchAr,datepArt(yy,@mindate))+@mm+convert(varchAr,@outputweek))<>@sameweek  '+
                     '   begin '+
                      '   select @update=@s   '+
                      '   select @s=''alter table #tmPMrpOblongMrp 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 #tmPMrpOblongMrp 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 #tmPMrpOblongMrp set ''+@update+''=(select sum(isnull(ssqty,0)) from #tmPMrpOblongMrp1 where convert(varchAr,ssdate)=''''''+convert(varchAr,@mindate)+''''''''+''and #tmPMrpOblongMrp.物料代码=#tmPMrpOblongMrp1.ItemCode '
              +' and #tmPMrpOblongMrp.PmCode=#tmPMrpOblongMrp1.PmCode'
               +')''' +
              '   select @update1=@update1+'' where 供需项目=0'' '+
              '   select @update2=''update #tmPMrpOblongMrp set ''+@update+''=(select sum(isnull(ssqty,0)) from #tmPMrpOblongMrp2 where convert(varchAr,ssdate)=''''''+convert(varchAr,@mindate)+'''''''' '+
              '   select @update2=@update2+''and #tmPMrpOblongMrp.物料代码=#tmPMrpOblongMrp2.ItemCode and #tmPMrpOblongMrp.PmCode=#tmPMrpOblongMrp2.Pmflag and #tmPMrpOblongMrp2.flag=''''s'''''' '+
              '   select @update2=@update2+'')'''+
              '   select @update2=@update2+'' where 供需项目=1'' '+
              '   select @update22=''update #tmPMrpOblongMrp set ''+@update+''=(select sum(isnull(ssqty,0)) from #tmPMrpOblongMrp2 where convert(varchAr,ssdate)=''''''+convert(varchAr,@mindate)+'''''''' '+
              '   select @update22=@update22+''and #tmPMrpOblongMrp.物料代码=#tmPMrpOblongMrp2.ItemCode and #tmPMrpOblongMrp.PmCode=#tmPMrpOblongMrp2.Pmflag and #tmPMrpOblongMrp2.flag=''''o''''''+'')'' '+
              '   select @update22=@update22+'' where 供需项目=12'' '+
              '    select @update3=''update #tmPMrpOblongMrp set ''+@update+''=(select sum(isnull(#tmPMrpOblongMrp2.ssqty,0)) from #tmPMrpOblongMrp2 where #tmPMrpOblongMrp2.ssdate<=''  '+
              '    select @update3=@update3+''''''''+convert(varchAr,datepArt(yyyy,@mindate))+''.''+convert(varchAr,datepArt(mm,@mindate))+''.''+convert(varchAr,datepArt(dd,@mindate))+'''''''' '+
              '    select @update3=@update3+''  and #tmPMrpOblongMrp.物料代码=#tmPMrpOblongMrp2.ItemCode and #tmPMrpOblongMrp.PmCode=#tmPMrpOblongMrp2.Pmflag)''                                                                                             '+
              '    select @update3=@update3+''-(select sum(isnull(#tmPMrpOblongMrp1.ssqty,0)) from #tmPMrpOblongMrp1 where #tmPMrpOblongMrp1.ssdate<=''                                                           '+
              '    select @update3=@update3+''''''''+convert(varchAr,datepArt(yyyy,@mindate))+''.''+convert(varchAr,datepArt(mm,@mindate))+''.''+convert(varchAr,datepArt(dd,@mindate))+'''''''' '+
              '    select @update3=@update3+''  and #tmPMrpOblongMrp.物料代码=#tmPMrpOblongMrp1.ItemCode and #tmPMrpOblongMrp.PmCode=#tmPMrpOblongMrp1.PmCode)+#tmPMrpOblongMrp.当前库存  where  #tmPMrpOblongMrp.供需项目=2''   '+
              '    select @update4=''update #tmPMrpOblongMrp set ''+@update+''=null where ''+@update+''=0 and 供需项目=2'''+
              '  end  else   '+
              ' if @selectType=1    '+
              '  begin   '+
              #13+'   select @update1=''update #tmPMrpOblongMrp set ''+@update+''=(select sum(isnull(ssqty,0)) from #tmPMrpOblongMrp1 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 #tmPMrpOblongMrp.物料代码=#tmPMrpOblongMrp1.ItemCode and #tmPMrpOblongMrp.PmCode=#tmPMrpOblongMrp1.PmCode''+'')'''+
              #13+'   select @update1=@update1+'' where 供需项目=0'' '+
              #13+'   select @update2=''update #tmPMrpOblongMrp set ''+@update+''=(select sum(isnull(ssqty,0)) from #tmPMrpOblongMrp2 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 #tmPMrpOblongMrp.物料代码=#tmPMrpOblongMrp2.ItemCode and #tmPMrpOblongMrp.PmCode=#tmPMrpOblongMrp2.Pmflag and #tmPMrpOblongMrp2.flag=''''s''''''+'')''+'' where 供需项目=1'' '+
              #13+'   select @update22=''update #tmPMrpOblongMrp set ''+@update+''=(select sum(isnull(ssqty,0)) from #tmPMrpOblongMrp2 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 #tmPMrpOblongMrp.物料代码=#tmPMrpOblongMrp2.ItemCode and #tmPMrpOblongMrp.PmCode=#tmPMrpOblongMrp2.Pmflag and #tmPMrpOblongMrp2.flag=''''o''''''+'')''+'' where 供需项目=12'' '+
              '   select @update3=''update #tmPMrpOblongMrp set ''+@update+''=(select sum(isnull(#tmPMrpOblongMrp2.ssqty,0)) from #tmPMrpOblongMrp2 where datepArt(mm,ssdate)<=''+convert(varchAr,datepArt(mm,@mindate)) '+
              '     select @update3=@update3+'' and convert(varchAr,datepArt(yyyy,#tmPMrpOblongMrp2.ssdate))+''''.''''+convert(varchAr,datepArt(wk,#tmPMrpOblongMrp2.ssdate))<='''+
              '     select @update3=@update3+''''''''+convert(varchAr,datepArt(yyyy,@mindate))+''.''+convert(varchAr,datepArt(wk,@mindate))+'''''''''+
              '     select @update3=@update3+''  and #tmPMrpOblongMrp.物料代码=#tmPMrpOblongMrp2.ItemCode and #tmPMrpOblongMrp.PmCode=#tmPMrpOblongMrp2.Pmflag)'''+
              '     select @update3=@update3+''-(select sum(isnull(#tmPMrpOblongMrp1.ssqty,0)) from #tmPMrpOblongMrp1 where datepArt(mm,#tmPMrpOblongMrp1.ssdate)<=''+convert(varchAr,datepArt(mm,@mindate))'+
              '     select @update3=@update3+'' and convert(varchAr,datepArt(yyyy,#tmPMrpOblongMrp1.ssdate))+''''.''''+convert(varchAr,datepArt(wk,#tmPMrpOblongMrp1.ssdate))<='''+
              '   select @update3=@update3+''''''''+convert(varchAr,datepArt(yyyy,@mindate))+''.''+convert(varchAr,datepArt(wk,@mindate))+'''''''''+
              ' select @update3=@update3+''  and #tmPMrpOblongMrp.物料代码=#tmPMrpOblongMrp1.ItemCode and #tmPMrpOblongMrp.PmCode=#tmPMrpOblongMrp1.PmCode)+#tmPMrpOblongMrp.当前库存  where  #tmPMrpOblongMrp.供需项目=2'''+
              '    select @update4=''update #tmPMrpOblongMrp set ''+@update+''=null where ''+@update+''=0 and 供需项目=2'''
              +'  end   else  '+
              ' if @selectType=2    '+
              '  begin   '+
              #13+'   select @update1=''update #tmPMrpOblongMrp set ''+@update+''=(select sum(isnull(ssqty,0)) from #tmPMrpOblongMrp1 where datepArt(yy,ssdate)=''+convert(varchAr,datepArt(yy,@mindate))+'' and datepArt(mm,ssdate)=''+convert(varchAr,datepArt(mm,@mindate))'+
              #13+'   select @update1=@update1+''and #tmPMrpOblongMrp.物料代码=#tmPMrpOblongMrp1.ItemCode and #tmPMrpOblongMrp.PmCode=#tmPMrpOblongMrp1.PmCode''+'')'''+
              #13+'   select @update1=@update1+'' where 供需项目=0'' '+
              #13+'   select @update2=''update #tmPMrpOblongMrp set ''+@update+''=(select sum(isnull(ssqty,0)) from #tmPMrpOblongMrp2 where datepArt(yy,ssdate)=''+convert(varchAr,datepArt(yy,@mindate))+'' and datepArt(mm,ssdate)=''+convert(varchAr,datepArt(mm,@mindate))'+
              #13+'   select @update2=@update2+''and #tmPMrpOblongMrp.物料代码=#tmPMrpOblongMrp2.ItemCode and #tmPMrpOblongMrp.PmCode=#tmPMrpOblongMrp2.Pmflag and #tmPMrpOblongMrp2.flag=''''s''''''+'')''+'' where 供需项目=1'' '+
              #13+'   select @update22=''update #tmPMrpOblongMrp set ''+@update+''=(select sum(isnull(ssqty,0)) from #tmPMrpOblongMrp2 where datepArt(yy,ssdate)=''+convert(varchAr,datepArt(yy,@mindate))+'' and datepArt(mm,ssdate)=''+convert(varchAr,datepArt(mm,@mindate))'+
              #13+'   select @update22=@update22+''and #tmPMrpOblongMrp.物料代码=#tmPMrpOblongMrp2.ItemCode and #tmPMrpOblongMrp.PmCode=#tmPMrpOblongMrp2.Pmflag and #tmPMrpOblongMrp2.flag=''''o''''''+'')''+'' where 供需项目=12'' '+
              '    select @update3=''update #tmPMrpOblongMrp set ''+@update+''=(select sum(isnull(#tmPMrpOblongMrp2.ssqty,0)) from #tmPMrpOblongMrp2 where #tmPMrpOblongMrp2.ssdate<=''  '+
              '    select @update3=@update3+''''''''+convert(varchAr,datepArt(yyyy,@mindate))+''.''+convert(varchAr,datepArt(mm,@mindate))+''.''+convert(varchAr,datepArt(dd,@mindate))+'''''''' '+
              '    select @update3=@update3+''  and #tmPMrpOblongMrp.物料代码=#tmPMrpOblongMrp2.ItemCode and #tmPMrpOblongMrp.PmCode=#tmPMrpOblongMrp2.Pmflag)''                                                                                             '+
              '    select @update3=@update3+''-(select sum(isnull(#tmPMrpOblongMrp1.ssqty,0)) from #tmPMrpOblongMrp1 where #tmPMrpOblongMrp1.ssdate<=''                                                           '+
              '    select @update3=@update3+''''''''+convert(varchAr,datepArt(yyyy,@mindate))+''.''+convert(varchAr,datepArt(mm,@mindate))+''.''+convert(varchAr,datepArt(dd,@mindate))+'''''''' '+
              '    select @update3=@update3+''  and #tmPMrpOblongMrp.物料代码=#tmPMrpOblongMrp1.ItemCode and #tmPMrpOblongMrp.PmCode=#tmPMrpOblongMrp1.PmCode)+#tmPMrpOblongMrp.当前库存  where  #tmPMrpOblongMrp.供需项目=2''   '+
             '    select @update4=''update #tmPMrpOblongMrp set ''+@update+''=null where ''+@update+''=0 and 供需项目=2'''
                       +'  end     '+
              '   exec(@update1)  '+
              '   exec(@update2)   '+
              '   exec(@update22)  '+
              '   exec(@update3)   '+
              '   exec(@update4)   '+
              '   select @mindate=@mindate+1   '+
              ' end  ';
//---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\\
   sqltext8:='update #tmPMrpOblongMrpResult '+
             '  set 供需项目=case 供需项目  '+
                          '  when ''0'' then  ''需求量(剩余需求)'''+
                          '  when ''1'' then ''系统建议量'''+
                          '  when ''12'' then ''订单未结量'''+
                          '  when ''2'' then ''计划可用量(预计库存量)'''+
                          ' end ';
//---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\\
selectfromsql:='select * from #tmPMrpOblongMrpResult';
       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;

       Close;
       sql.clear;
       sql.Add(sqltext20);
       Prepared;
       try
       execsql;
       except
       end;

       Close;
       sql.clear;
       sql.Add(sqltext2);
       Prepared;
       try
       execsql;
       except
       end;

       Close;
       sql.clear;
       sql.Add(sqltext3);
       Prepared;
       try
       execsql;
       except
       end;

       Close;
       sql.clear;
       sql.Add(sqltext4);
       Prepared;
       try
       execsql;
       except
       end;

       Close;
       sql.clear;
       sql.Add(sqltext5);
       Prepared;
       try
       execsql;
       except
       end;

       Close;
       sql.clear;
       sql.Add(sqltext6);
       Prepared;
       try
       execsql;
       except
       end;


      with Adocommand do
     begin
       Close;
       try
       commandtext:=sqltext7;
       Execute;
               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;
     end;
      { Close;
       sql.clear;
       sql.Add(sqltext7);
       Prepared;
       try
       execsql;
       except
       showmessage('error');
       end;  }
       Close;
       sql.clear;
       sql.Add('select * into #tmPMrpOblongMrpResult from #tmPMrpOblongMrp Order by 物料代码,供需项目');
       Prepared;
       try
       execsql;
       except
       end;


       Close;
       sql.clear;
       sql.Add(sqltext8);
       Prepared;
       try
       execsql;
       except
       end;

       Close;
       sql.clear;
       sql.Add('select * from #tmPMrpOblongMrpResult Order by 物料代码,PmCode');
       Prepared;
       try
       open;
       except
       end;