mrp_qry_informaluprightmrp_d_mps.pas

一个MRPII系统源代码版本

unit Mrp_Qry_InformalUpRightMrp_D_Mps;

Interface

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

Type
  TFrm_Mrp_Qry_InformalUpRightMrp_D_Mps = Class(TFrm_Base_Qry)
    AdoQry_MainItemCode: TStringField;
    AdoQry_MainDueDate: TDateTimeField;
    AdoQry_MainReleaseDate: TDateTimeField;
    AdoQry_MaInOrderNo: TStringField;
    AdoQry_MainGrossQty: TFloatField;
    AdoQry_MaInOrderQty: TFloatField;
    AdoQry_MainOnHand: TFloatField;
    AdoQry_MainCanUseOnHand: TFloatField;
    AdoQry_MainNetQty: TFloatField;
    AdoQry_MainOrdinal: TIntegerField;
    AdoQry_MainSSCode: TIntegerField;
    AdoQry_MainPmCode: TIntegerField;
    AdoQry_MaInOrderLineStatus: TIntegerField;
    AdoQry_MaInOrderLineno: TIntegerField;
    AdoQry_Mainpegging: TStringField;
    AdoQry_Mainoncheck: TFloatField;
    AdoQry_MainUomName: TStringField;
    AdoQry_MainItemflag: TStringField;
    AdoQry_MainBomqty: TFloatField;
    AdoQry_MainScrAprate: TStringField;
    AdoQry_MainMpsstatus: TIntegerField;
    AdoQry_MainMpsremArk: TStringField;
    AdoQry_MainMpsMonth: TStringField;
    AdoQry_MainMpsdate: TDateTimeField;
    procedure FormDestroy(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
  private
    { Private declarations }
  public
    lowItemPmCode:integer;
    requiredate:string;
    ItemCode:string;
    traceType:integer;
    Qclt:string;
    procedure InitForm(AdOConnection:TAdOConnection;ShowExtendColumn:Boolean);Override;
    function getlt(ItemCode:string;ltType:integer):integer;
    { Public declarations }
  end;

var
  Frm_Mrp_Qry_InformalUpRightMrp_D_Mps: TFrm_Mrp_Qry_InformalUpRightMrp_D_Mps;

implementation
uses Mrp_Qry_InformalUpRightMrp_D,Sys_Global;
{$R *.DFM}

{ TFrm_Mrp_Qry_InformalUpRightMrp_D_Mps }

procedure TFrm_Mrp_Qry_InformalUpRightMrp_D_Mps.InitForm(
  AdOConnection: TAdOConnection; ShowExtendColumn: Boolean);
var sqltext,tMpsqltext,Tmpcondition:string;
    i:integer;
begin
  inherited;
if traceType=1 then
 begin
   sqltext:='Select InformalMrpResult.*,Mps.MpsMonth,Mps.Mpsstatus,Mps.MpsremArk,Mps.Mpsdate,   '
            +' Uom.UomName, Bomqty=convert(float,0),ScrAprate=''ddffdsdsfdf'',  '
            +' InformalMrpResult.ItemCode+'' ''+Item.ItemName As ItemFlag  '
            +' From InformalMrpResult,Item,Uom,Mps '
            +' Where Item.ItemCode=InformalMrpResult.ItemCode '
            +'   and Item.UomCode*=Uom.UomCode'
            +'   and InformalMrpResult.ItemCode=Mps.ItemCode '
     //       +'   and InformalMrpResult.releasedate=Mps.Mpsdate '
            +'   and InformalMrpResult.ItemCode='+quotedstr(ItemCode)
            +'   and Mps.Mpsdate='+quotedstr(slCalendar(dbconnect,requiredate,-getlt(ItemCode,2)))
            +'   and InformalMrpResult.ordinal=1';
       with AdoQry_Main do
    begin
      Close;
      sql.clear;
      sql.Add(sqltext);
      Prepared;
      try
      open;
      except
      end;

    end;
 end
else
if traceType=2 then
 begin
 self.Caption:='父项需求对应表';
 pnl_title.Caption:=self.Caption;
 try
Executesql(AdoQry_tmp,'drop table #tmpopenBom,#tmpopenBomResult',1);
except
end;
   Bomopen(dbconnect,ItemCode);
{   sqltext0:='select #tmpopenBom.ItemCode,'
            +'       ldtime=case Item.PmCode '
            +'                when 1 then purchldtime'
            +'                when 2 then purchldtime '
            +'                else mnldtime'
            +'              end, '
            +'       Item.Qclt,'
            +'       Item.Preparelt'
            +' from #tmpopenBom,Item'
            +' where #tmpopenBom.ItemCode=Item.ItemCode';   }
   tMpsqltext:='select distinct Item.ItemCode,Item.Preparelt,Item.runlt,Item.PmCode,Item.purchldtime,Bomqty=sum(Bomqty),BomScrAprate=sum(BomScrAprate) '
             +' into #tmpopenBomResult  '
             +'  From #TmpOpenBom,Item       '
             +' where #tmpopenBom.ItemCode=Item.ItemCode  '
             +' group by Item.ItemCode,Item.Preparelt,Item.runlt,Item.purchldtime,Item.PmCode';
   Executesql(AdoQry_tmp,tMpsqltext,1);
   Executesql(AdoQry_tmp,'select * from #tmpopenBomResult',0);

     with AdoQry_tmp do
    begin
      if eof then
        exit;
      i:=0;
      While NOT Eof Do
         begin
           If I=0 Then
               begin
                if lowItemPmCode=0 then
                Tmpcondition:='(InformalMrpResult.ItemCode='''+AdoQry_Tmp.fieldbyname('ItemCode').AsString +''' And '
                   +' InformalMrpResult.releaseDate= case InformalMrpResult.PmCode when 0 then '''+SlCalendar(AdoConnection,requireDate,-(strtoint(Qclt)+AdoQry_Tmp.fieldbyname('Preparelt').AsInteger+getlt(AdoQry_tmp.fieldbyname('ItemCode').asstring,3)))+''''
                   +'                         else   '''+SlCalendar(AdoConnection,requireDate,-(strtoint(Qclt)+AdoQry_tmp.fieldbyname('purchldtime').asinteger))+''''
                   +'                        end   '
                   +' and Mps.Mpsdate= case InformalMrpResult.PmCode when 0 then '+quotedstr(slCalendar(dbconnect,SlCalendar(AdoConnection,requireDate,-(strtoint(Qclt)+AdoQry_Tmp.fieldbyname('Preparelt').AsInteger+getlt(AdoQry_tmp.fieldbyname('ItemCode').asstring,3))),-getlt(fieldbyname('ItemCode').asstring,2)))
                   +'                    else '+quotedstr(slCalendar(dbconnect,SlCalendar(AdoConnection,requireDate,-(strtoint(Qclt)+AdoQry_tmp.fieldbyname('purchldtime').asinteger)),-getlt(fieldbyname('ItemCode').asstring,2)))
                   +'                  end   )'
                else
                 Tmpcondition:='(InformalMrpResult.ItemCode='''+AdoQry_Tmp.fieldbyname('ItemCode').AsString +''' And '
                   +' InformalMrpResult.releaseDate= case InformalMrpResult.PmCode when 0 then '''+SlCalendar(AdoConnection,requireDate,-(AdoQry_Tmp.fieldbyname('Preparelt').AsInteger+getlt(AdoQry_tmp.fieldbyname('ItemCode').asstring,3)))+''''
                   +'                         else   '''+SlCalendar(AdoConnection,requireDate,-(AdoQry_tmp.fieldbyname('purchldtime').asinteger))+''''
                   +'                        end   '
                   +' and Mps.Mpsdate= case InformalMrpResult.PmCode when 0 then '+quotedstr(slCalendar(dbconnect,SlCalendar(AdoConnection,requireDate,-(AdoQry_Tmp.fieldbyname('Preparelt').AsInteger+getlt(AdoQry_tmp.fieldbyname('ItemCode').asstring,3))),-getlt(fieldbyname('ItemCode').asstring,2)))
                   +'                    else '+quotedstr(slCalendar(dbconnect,SlCalendar(AdoConnection,requireDate,-(AdoQry_tmp.fieldbyname('purchldtime').asinteger)),-getlt(fieldbyname('ItemCode').asstring,2)))
                   +'                  end   )'
               end
            Else
               begin
                 if lowItemPmCode=0 then
                 Tmpcondition:=Tmpcondition+' Or (InformalMrpResult.ItemCode='''+AdoQry_Tmp.fieldbyname('ItemCode').AsString +''' And '
                   +' InformalMrpResult.releaseDate= case InformalMrpResult.PmCode when 0 then '''+SlCalendar(AdoConnection,requireDate,-(strtoint(Qclt)+AdoQry_Tmp.fieldbyname('Preparelt').AsInteger+getlt(AdoQry_tmp.fieldbyname('ItemCode').asstring,3)))+''''
                   +'                         else   '''+SlCalendar(AdoConnection,requireDate,-(strtoint(Qclt)+AdoQry_tmp.fieldbyname('purchldtime').asinteger))+''''
                   +'                        end   '
                   +' and Mps.Mpsdate= case InformalMrpResult.PmCode when 0 then '+quotedstr(slCalendar(dbconnect,SlCalendar(AdoConnection,requireDate,-(strtoint(Qclt)+AdoQry_Tmp.fieldbyname('Preparelt').AsInteger+getlt(AdoQry_tmp.fieldbyname('ItemCode').asstring,3))),-getlt(fieldbyname('ItemCode').asstring,2)))
                   +'                    else '+quotedstr(slCalendar(dbconnect,SlCalendar(AdoConnection,requireDate,-(strtoint(Qclt)+AdoQry_tmp.fieldbyname('purchldtime').asinteger)),-getlt(fieldbyname('ItemCode').asstring,2)))
                   +'                  end   )'
                 else
                 Tmpcondition:=Tmpcondition+' Or (InformalMrpResult.ItemCode='''+AdoQry_Tmp.fieldbyname('ItemCode').AsString +''' And '
                   +' InformalMrpResult.releaseDate= case InformalMrpResult.PmCode when 0 then '''+SlCalendar(AdoConnection,requireDate,-(AdoQry_Tmp.fieldbyname('Preparelt').AsInteger+getlt(AdoQry_tmp.fieldbyname('ItemCode').asstring,3)))+''''
                   +'                         else   '''+SlCalendar(AdoConnection,requireDate,-(AdoQry_tmp.fieldbyname('purchldtime').asinteger))+''''
                   +'                        end   '
                   +' and Mps.Mpsdate= case InformalMrpResult.PmCode when 0 then '+quotedstr(slCalendar(dbconnect,SlCalendar(AdoConnection,requireDate,-(AdoQry_Tmp.fieldbyname('Preparelt').AsInteger+getlt(AdoQry_tmp.fieldbyname('ItemCode').asstring,3))),-getlt(fieldbyname('ItemCode').asstring,2)))
                   +'                    else '+quotedstr(slCalendar(dbconnect,SlCalendar(AdoConnection,requireDate,-(AdoQry_tmp.fieldbyname('purchldtime').asinteger)),-getlt(fieldbyname('ItemCode').asstring,2)))
                   +'                  end   )'
               end;
      I:=I+1;
      AdoQry_Tmp.Next;
      end;
    SelectFromSql:=' select distinct InformalMrpResult.*,#tmpopenBomResult.Bomqty,Mrpqty=grossqty,  '
               +'Mps.MpsMonth,Mps.Mpsdate,Mps.Mpsstatus,Mps.MpsremArk, '
               +'  ScrAprate=convert(varchAr,#tmpopenBomResult.BomScrAprate)+''%'','
               +'  Itemflag=InformalMrpResult.ItemCode+'' ''+Item.ItemName,  '
               +'   Uom.UomName                           '
               +'   from InformalMrpResult,Item,Uom,Mps,#tmpopenBomResult               '
               +' where InformalMrpResult.ItemCode=Item.ItemCode  '
               +' and InformalMrpResult.ItemCode=Mps.ItemCode '
               +' and InformalMrpResult.grossqty>0   '
             //  +' and  InformalMrpResult.releasedate=Mps.Mpsdate '
               +' and  Item.UomCode*=Uom.UomCode'
               +' and  InformalMrpResult.ItemCode=#tmpopenBomResult.ItemCode'
               +' and   (ordinal=1) '+' and ('+tmpcondition+')';
   OrderByFields:='ItemCode,dueDate ';
   GetData;

    end;
 end;

if traceType=2 then
 begin
 dbgrideh.Columns[2].Visible:=True;
 dbgrideh.Columns[3].Visible:=True;
 end;
end;


procedure TFrm_Mrp_Qry_InformalUpRightMrp_D_Mps.FormDestroy(
  Sender: TObject);
begin
  inherited;
  Frm_Mrp_Qry_InformalUpRightMrp_D_Mps:=nil;
end;

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

end;

function TFrm_Mrp_Qry_InformalUpRightMrp_D_Mps.getlt(ItemCode: string;
  ltType: integer): integer;
var AdoQry:TAdoQuery;
begin
  Result:=0;
  AdoQry:=TAdoQuery.Create(self);
  AdoQry.EnableBCD:=False;
  try
    with AdoQry do
      begin
        Close;
        Connection:=dbconnect;
        sql.clear;
        sql.Add('select Preparelt,Qclt,runlt from Item where ItemCode='
                +quotedstr(ItemCode));
      open;
      case  ltType of
       1:  Result:=fieldbyname('Preparelt').asinteger;
       2:  Result:=fieldbyname('Qclt').asinteger;
       3:  Result:=fieldbyname('runlt').asinteger;
      end;
      end;
  finally
    AdoQry.Free;
  end;
end;


end.