mrp_qry_newinformaltotalmrp_sys.pas

一个MRPII系统源代码版本

unit Mrp_Qry_NewInformalTotalMrp_Sys;

Interface

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

Type
  TFrm_Mrp_Qry_NewInformalTotalMrp_Sys = Class(TFrm_Base_Qry)
    AdoQry_MainSSId: TAutoIncField;
    AdoQry_MainItemCode: TStringField;
    AdoQry_MainSSSysInfoFlag: TIntegerField;
    AdoQry_MaInOrderNo: TStringField;
    AdoQry_MaInOrderLineNo: TIntegerField;
    AdoQry_MainSSDate: TDateTimeField;
    AdoQry_MainSSQty: TFloatField;
    AdoQry_MaInOrderLineStatus: TIntegerField;
    AdoQry_MainDeptVendorCode: TStringField;
    AdoQry_Mainduedate: TDateTimeField;
    AdoQry_MainReMainQty: TFloatField;
    AdoQry_MainItemName: TStringField;
    AdoQry_MainUomName: TStringField;
    AdoQry_MainDeptName: TStringField;
    AdoQry_MainDeptCode: TStringField;
    AdoQry_MainItemFlag: TStringField;
    AdoQry_MainDeptFlag: TStringField;
    AdoQry_MainEmployeeCode: TStringField;
    AdoQry_MainEmployeeName: TStringField;
    AdoQry_MainEmployeeflag: TStringField;
    AdoQry_MainssName: TStringField;
    procedure FormDestroy(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
  private
    function getPmCode(ItemCode:string):integer;
    { Private declarations }
  public
    ItemCode:string;
    TotalType:integer;
    procedure InitForm(AdOConnection:TAdOConnection;ShowExtendColumn:Boolean);Override;
    { Public declarations }
  end;

var
  Frm_Mrp_Qry_NewInformalTotalMrp_Sys: TFrm_Mrp_Qry_NewInformalTotalMrp_Sys;

implementation
uses Sys_Global;
{$R *.DFM}

procedure TFrm_Mrp_Qry_NewInformalTotalMrp_Sys.FormDestroy(Sender: TObject);
begin
  inherited;
  Frm_Mrp_Qry_NewInformalTotalMrp_Sys:=nil;
end;

procedure TFrm_Mrp_Qry_NewInformalTotalMrp_Sys.InitForm(
  AdOConnection: TAdOConnection; ShowExtendColumn: Boolean);
var sqltext:string;
begin
Application.ProcessMessages;
  inherited;
if getPmCode(ItemCode)<>0  then
 begin
  dbgrideh.Columns[2].Title.Caption:='采购员标识';
  dbgrideh.Columns[5].Title.Caption:='供应商标识';
 end;
if TotalType=1 then
 begin
  sqltext:='select InformalMrpResult.*,ssid=MrpResultid,ssdate=releasedate,ssSysInfoflag=1,MoLine.moqty,ssqty=InformalMrpResult.onhand,Sysqty=InformalMrpResult.onhand,ssreMainqty=reMainqty '
           +'  into #tMpssInfoQry from InformalMrpResult                '
           +' left outer join MoLine on InformalMrpResult.Orderno=MoLine.mono '
           +'    and InformalMrpResult.Orderlineno=MoLine.MoLineno  '
           +' where (ordinal=3 )                    '
           +'  and InformalMrpResult.ItemCode='+quotedstr(ItemCode)
           +'  and  PmCode=0   '
           +' union                                    '
           +' select InformalMrpResult.*,ssid=MrpResultid,ssdate=releasedate,ssSysInfoflag=3,MoLine.moqty,ssqty=InformalMrpResult.Orderqty,Sysqty=InformalMrpResult.Orderqty,ssreMainqty=0 from InformalMrpResult                         '
           +'  join MoLine on InformalMrpResult.Orderno=MoLine.mono '
           +'    and InformalMrpResult.Orderlineno=MoLine.MoLineno  '
           +' where ordinal=2                    '
           +'   and Orderlinestatus=6           '
           +'   and realOrderqty<=0           '
           +'  and InformalMrpResult.ItemCode='+quotedstr(ItemCode)
           +'   and exists(select mono from MoLine where Orderno=MoLine.mono and Orderlineno=MoLine.MoLineno and MoLine.MoLinestatus<>7) '
           +' union                                    '
           +' select InformalMrpResult.*,ssid=MrpResultid,ssdate=releasedate,ssSysInfoflag=4,MoLine.moqty,ssqty=InformalMrpResult.Orderqty,Sysqty=InformalMrpResult.Orderqty,ssreMainqty=0 from InformalMrpResult                         '
           +'  join MoLine on InformalMrpResult.Orderno=MoLine.mono '
           +'    and InformalMrpResult.Orderlineno=MoLine.MoLineno  '
           +' where ordinal=2                    '
           +'   and Orderlinestatus=5 '
           +'   and onhand>0  '
           +'  and InformalMrpResult.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 InformalMrpResult.*,    '
           +'        ssid=MrpResultid,'
           +'        ssdate=releasedate, '
           +'        ssSysInfoflag=2, '
           +'        MoLine.moqty,   '
          +'         ssqty=Orderqty-onhand, '
           +'        Sysqty=Orderqty-onhand,  '
           +'        ssreMainqty=0       '
           +'   from InformalMrpResult,MoLine           '
           +'  where (ordinal=2 )    '
           +'  and InformalMrpResult.ItemCode='+quotedstr(ItemCode)
           +'  and Orderlinestatus<>7   '
            +'  and Orderqty>onhand      '
            +'  and exists(select mono from MoLine where Orderno=MoLine.mono) '
            +'  and InformalMrpResult.Orderno=MoLine.mono          '
            +'  and InformalMrpResult.Orderlineno=MoLine.MoLineno  ';

   Executesql(AdoQry_Main,sqltext,1);

   sqltext:='insert into #tMpssInfoQry '
           +'select InformalMrpResult.*,ssid=MrpResultid,ssdate=releasedate,ssSysInfoflag=1,poline.poqty,ssqty=InformalMrpResult.onhand,Sysqty=InformalMrpResult.onhand,ssreMainqty=reMainqty '
           +'   from InformalMrpResult                '
           +' left outer join poline on InformalMrpResult.Orderno=poline.pono '
           +'    and InformalMrpResult.Orderlineno=poline.polineno  '
           +' where (ordinal=3 )                    '
           +'  and InformalMrpResult.ItemCode='+quotedstr(ItemCode)
           +'  and  PmCode in(1,2)   '
           +' union                                    '
           +' select InformalMrpResult.*,ssid=MrpResultid,ssdate=releasedate,ssSysInfoflag=3,poline.poqty,ssqty=InformalMrpResult.Orderqty,Sysqty=InformalMrpResult.Orderqty,ssreMainqty=0 from InformalMrpResult                         '
           +'  join poline on InformalMrpResult.Orderno=poline.pono '
           +'    and InformalMrpResult.Orderlineno=poline.polineno  '
           +' where ordinal=2                    '
           +'   and Orderlinestatus=6           '
           +'   and realOrderqty<=0           '
           +'  and InformalMrpResult.ItemCode='+quotedstr(ItemCode)
           +'   and exists(select pono from poline where Orderno=poline.pono and Orderlineno=poline.polineno and poline.polinestatus<>7) '
           +' union                                    '
           +' select InformalMrpResult.*,ssid=MrpResultid,ssdate=releasedate,ssSysInfoflag=4,poline.poqty,ssqty=InformalMrpResult.Orderqty,Sysqty=InformalMrpResult.Orderqty,ssreMainqty=0 from InformalMrpResult                         '
           +'  join poline on InformalMrpResult.Orderno=poline.pono '
           +'    and InformalMrpResult.Orderlineno=poline.polineno  '
           +' where ordinal=2                    '
           +'   and Orderlinestatus=5 '
           +'   and onhand>0  '
           +'  and InformalMrpResult.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 InformalMrpResult.*,    '
           +'        ssid=MrpResultid,'
           +'        ssdate=releasedate, '
           +'        ssSysInfoflag=2, '
           +'        poline.poqty,   '
          +'         ssqty=Orderqty-onhand, '
           +'        Sysqty=Orderqty-onhand,  '
           +'        ssreMainqty=0       '
           +'   from InformalMrpResult,poline           '
           +'  where (ordinal=2 )    '
           +'  and InformalMrpResult.ItemCode='+quotedstr(ItemCode)
           +'  and Orderlinestatus<>7   '
            +'  and Orderqty>onhand      '
            +'  and exists(select pono from poline where Orderno=poline.pono) '
            +'  and InformalMrpResult.Orderno=poline.pono          '
            +'  and InformalMrpResult.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,'