mrp_qry_newinformaltotalmrp_sys.pas

一个MRPII系统源代码版本

                +' Employee.EmployeeCode+'''+' '+'''+Employee.EmployeeName As EmployeeFlag,'
                +iifstring(getPmCode(ItemCode)=0,' Dept.DeptCode+'' ''+Dept.DeptName As DeptFlag, ','Vendor.VendorCode+'' ''+Vendor.VendorName as Deptflag,')
                +' Item.ItemCode+'''+' '+'''+Item.ItemName As ItemFlag,'
                +' ConVert(varchAr,SysSsInfo.SSCode)+'''+' '+'''+SysSsInfo.SsName As SsName'
                +'  into #tMpssInfoResultQry '
                +' From #tMpssInfoQry '
                +' Join Item On #tMpssInfoQry.ItemCode=Item.ItemCode '
                +iifstring(getPmCode(ItemCode)=0,' Left Join Dept On  isnull(#tMpssInfoQry.DeptVendorCode,Item.DeptCode)=Dept.DeptCode','Left Join Vendor On  #tMpssInfoQry.DeptVendorCode=Vendor.VendorCode')
                +' Left Join Employee On '+iifstring(getPmCode(ItemCode)=0,' Item.Pla_EmployeeCode','Item.EmployeeCode')+'=Employee.EmployeeCode'
                +' Join Uom On Item.UomCode=Uom.UomCode'
                +' Join SysSsInfo On #tMpssInfoQry.SSSysInfoFlag=SysSsInfo.SSCode'
                      +' Order by #tMpssInfoQry.ItemCode,#tMpssInfoQry.ssdate '
                +' create nonclustered index reindex on #tMpssInfoResultQry(ssdate,EmployeeCode) ';
  //   showmessage(sqltext);
     Executesql(AdoQry_Main,sqltext,1);
     Executesql(AdoQry_Main,'select * from #tMpssInfoResultQry',0);
     selectfromsql:='select * from #tMpssInfoResultQry';
 end;
if TotalType=2 then
 begin
  sqltext:='select MrpResult.*,ssid=MrpResultid,ssdate=releasedate,ssSysInfoflag=1,MoLine.moqty,ssqty=MrpResult.onhand,Sysqty=MrpResult.onhand,ssreMainqty=reMainqty '
           +'  into #tMpssInfoQry from MrpResult                '
           +' left outer join MoLine on MrpResult.Orderno=MoLine.mono '
           +'    and MrpResult.Orderlineno=MoLine.MoLineno  '
           +' where (ordinal=3 )                    '
           +'  and MrpResult.ItemCode='+quotedstr(ItemCode)
           +'  and  PmCode=0   '
           +' union                                    '
           +' select MrpResult.*,ssid=MrpResultid,ssdate=releasedate,ssSysInfoflag=3,MoLine.moqty,ssqty=MrpResult.Orderqty,Sysqty=MrpResult.Orderqty,ssreMainqty=0 from MrpResult                         '
           +'  join MoLine on MrpResult.Orderno=MoLine.mono '
           +'    and MrpResult.Orderlineno=MoLine.MoLineno  '
           +' where ordinal=2                    '
           +'   and Orderlinestatus=6           '
           +'   and realOrderqty<=0           '
           +'  and MrpResult.ItemCode='+quotedstr(ItemCode)
           +'   and exists(select mono from MoLine where Orderno=MoLine.mono and Orderlineno=MoLine.MoLineno and MoLine.MoLinestatus<>7) '
           +' union                                    '
           +' select MrpResult.*,ssid=MrpResultid,ssdate=releasedate,ssSysInfoflag=4,MoLine.moqty,ssqty=MrpResult.Orderqty,Sysqty=MrpResult.Orderqty,ssreMainqty=0 from MrpResult                         '
           +'  join MoLine on MrpResult.Orderno=MoLine.mono '
           +'    and MrpResult.Orderlineno=MoLine.MoLineno  '
           +' where ordinal=2                    '
           +'   and Orderlinestatus=5 '
           +'   and onhand>0  '
           +'  and MrpResult.ItemCode='+quotedstr(ItemCode)
           +'   and (releasedate<dateName(yy,getdate())+''.''+dateName(mm,getdate())+''.''+dateName(dd,getdate()) or duedate<dateName(yy,getdate())+''.''+dateName(mm,getdate())+''.''+dateName(dd,getdate()))  '
           +'   and exists(select mono from MoLine where Orderno=MoLine.mono and Orderlineno=MoLine.MoLineno and MoLine.MoLinestatus<>7) '
           +' union                                    '
           +' select MrpResult.*,    '
           +'        ssid=MrpResultid,'
           +'        ssdate=releasedate, '
           +'        ssSysInfoflag=2, '
           +'        MoLine.moqty,   '
          +'         ssqty=Orderqty-onhand, '
           +'        Sysqty=Orderqty-onhand,  '
           +'        ssreMainqty=0       '
           +'   from MrpResult,MoLine           '
           +'  where (ordinal=2 )    '
           +'  and MrpResult.ItemCode='+quotedstr(ItemCode)
           +'  and Orderlinestatus<>7   '
            +'  and Orderqty>onhand      '
            +'  and exists(select mono from MoLine where Orderno=MoLine.mono) '
            +'  and MrpResult.Orderno=MoLine.mono          '
            +'  and MrpResult.Orderlineno=MoLine.MoLineno  ';

   Executesql(AdoQry_Main,sqltext,1);

   sqltext:='insert into #tMpssInfoQry '
           +'select MrpResult.*,ssid=MrpResultid,ssdate=releasedate,ssSysInfoflag=1,poline.poqty,ssqty=MrpResult.onhand,Sysqty=MrpResult.onhand,ssreMainqty=reMainqty '
           +'   from MrpResult                '
           +' left outer join poline on MrpResult.Orderno=poline.pono '
           +'    and MrpResult.Orderlineno=poline.polineno  '
           +' where (ordinal=3 )                    '
           +'  and MrpResult.ItemCode='+quotedstr(ItemCode)
           +'  and  PmCode in(1,2)   '
           +' union                                    '
           +' select MrpResult.*,ssid=MrpResultid,ssdate=releasedate,ssSysInfoflag=3,poline.poqty,ssqty=MrpResult.Orderqty,Sysqty=MrpResult.Orderqty,ssreMainqty=0 from MrpResult                         '
           +'  join poline on MrpResult.Orderno=poline.pono '
           +'    and MrpResult.Orderlineno=poline.polineno  '
           +' where ordinal=2                    '
           +'   and Orderlinestatus=6           '
           +'   and realOrderqty<=0           '
           +'  and MrpResult.ItemCode='+quotedstr(ItemCode)
           +'   and exists(select pono from poline where Orderno=poline.pono and Orderlineno=poline.polineno and poline.polinestatus<>7) '
           +' union                                    '
           +' select MrpResult.*,ssid=MrpResultid,ssdate=releasedate,ssSysInfoflag=4,poline.poqty,ssqty=MrpResult.Orderqty,Sysqty=MrpResult.Orderqty,ssreMainqty=0 from MrpResult                         '
           +'  join poline on MrpResult.Orderno=poline.pono '
           +'    and MrpResult.Orderlineno=poline.polineno  '
           +' where ordinal=2                    '
           +'   and Orderlinestatus=5 '
           +'   and onhand>0  '
           +'  and MrpResult.ItemCode='+quotedstr(ItemCode)
           +'   and (releasedate<dateName(yy,getdate())+''.''+dateName(mm,getdate())+''.''+dateName(dd,getdate()) or duedate<dateName(yy,getdate())+''.''+dateName(mm,getdate())+''.''+dateName(dd,getdate()))  '
           +'   and exists(select pono from poline where Orderno=poline.pono and Orderlineno=poline.polineno and poline.polinestatus<>7) '
           +' union                                    '
           +' select MrpResult.*,    '
           +'        ssid=MrpResultid,'
           +'        ssdate=releasedate, '
           +'        ssSysInfoflag=2, '
           +'        poline.poqty,   '
          +'         ssqty=Orderqty-onhand, '
           +'        Sysqty=Orderqty-onhand,  '
           +'        ssreMainqty=0       '
           +'   from MrpResult,poline           '
           +'  where (ordinal=2 )    '
           +'  and MrpResult.ItemCode='+quotedstr(ItemCode)
           +'  and Orderlinestatus<>7   '
            +'  and Orderqty>onhand      '
            +'  and exists(select pono from poline where Orderno=poline.pono) '
            +'  and MrpResult.Orderno=poline.pono          '
            +'  and MrpResult.Orderlineno=poline.polineno  ';
    Executesql(AdoQry_Main,sqltext,1);
    sqltext:='select #tMpssInfoQry.*,tMpssreMainqty=case #tMpssInfoQry.ssreMainqty when 0 then null else #tMpssInfoQry.ssreMainqty end,Item.mnldtime,Item.ItemName,Uom.UomName,'
            +iifstring(getPmCode(ItemCode)=0, ' Dept.DeptCode,Dept.DeptName,','Vendor.VendorCode as DeptCode,Vendor.VendorName as DeptName,')
            +'  Employee.EmployeeCode,Employee.EmployeeName,'
                +' ssflag=convert(varchAr,#tMpssInfoQry.ssSysInfoflag)+'' ''+SysssInfo.ssName,'
                +' Employee.EmployeeCode+'''+' '+'''+Employee.EmployeeName As EmployeeFlag,'
                +iifstring(getPmCode(ItemCode)=0,' Dept.DeptCode+'' ''+Dept.DeptName As DeptFlag, ','Vendor.VendorCode+'' ''+Vendor.VendorName as Deptflag,')
                +' Item.ItemCode+'''+' '+'''+Item.ItemName As ItemFlag,'
                +' ConVert(varchAr,SysSsInfo.SSCode)+'''+' '+'''+SysSsInfo.SsName As SsName'
                +'  into #tMpssInfoResultQry '
                +' From #tMpssInfoQry '
                +' Join Item On #tMpssInfoQry.ItemCode=Item.ItemCode '
                +iifstring(getPmCode(ItemCode)=0,' Left Join Dept On  isnull(#tMpssInfoQry.DeptVendorCode,Item.DeptCode)=Dept.DeptCode','Left Join Vendor On  #tMpssInfoQry.DeptVendorCode=Vendor.VendorCode')
                +' Left Join Employee On '+iifstring(getPmCode(ItemCode)=0,' Item.Pla_EmployeeCode','Item.EmployeeCode')+'=Employee.EmployeeCode'
                +' Join Uom On Item.UomCode=Uom.UomCode'
                +' Join SysSsInfo On #tMpssInfoQry.SSSysInfoFlag=SysSsInfo.SSCode'
                      +' Order by #tMpssInfoQry.ItemCode,#tMpssInfoQry.ssdate '
                +' create nonclustered index reindex on #tMpssInfoResultQry(ssdate,EmployeeCode) ';
  //   showmessage(sqltext);
     Executesql(AdoQry_Main,sqltext,1);
     Executesql(AdoQry_Main,'select * from #tMpssInfoResultQry',0);
     selectfromsql:='select * from #tMpssInfoResultQry';
 end;
  OrderByFields:='SSDate,EmployeeCode ';
  GetData;

end;

procedure TFrm_Mrp_Qry_NewInformalTotalMrp_Sys.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
  inherited;
  try
  Executesql(AdoQry_tmp,'drop table #tMpssInfoQry,#tMpssInfoResultQry',1);
  except
  end;
end;

function TFrm_Mrp_Qry_NewInformalTotalMrp_Sys.getPmCode(
  ItemCode: string): integer;
var Qry:TAdoQuery;
begin
   Result:=100;
   Qry:=TAdoQuery.Create(self);
   Qry.Connection:=dbconnect;
try
   Executesql(Qry,'select PmCode from Item where ItemCode='+quotedstr(ItemCode),0);
   Result:=Qry.fieldbyname('PmCode').asinteger;
finally
   Qry.Free;
end;
end;

end.