mrp_qry_analyzermrpmo.pas

一个MRPII系统源代码版本

unit Mrp_Qry_AnalyzerMrpMo;

Interface

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

Type
  TFrm_Mrp_Qry_AnalyzerMrpMo = Class(TFrm_Base_Qry)
    AdoQry_MainItemCode: TStringField;
    AdoQry_MainBomItemType: TIntegerField;
    AdoQry_MainPmCode: TIntegerField;
    AdoQry_MainRequireDate: TDateTimeField;
    AdoQry_Mainrequireqty: TFloatField;
    AdoQry_MainItemName: TStringField;
    AdoQry_MainCurrentonhandInv: TFloatField;
    AdoQry_Mainlessqty: TFloatField;
    AdoQry_Mainwipqty: TFloatField;
    AdoQry_MainItemflag: TStringField;
    Label1: TLabel;
    dbtxtItemflag: TDBText;
    AdoQry_MainCurrentonCheckInv: TFloatField;
    AdoQry_MainAssignqty: TFloatField;
    AdoQry_Mainnetqty: TFloatField;
    AdoQry_MaInPlanqty: TFloatField;
    procedure FormDestroy(Sender: TObject);
    procedure Act_LookExecute(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure Act_ShowGridExecute(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
  private
    { Private declarations }
  public
    Orderno:string;
    Orderlineno:integer;
    procedure InitForm(AdOConnection:TAdOConnection;ReadOnly:Boolean);Override;
    { Public declarations }
  end;

var
  Frm_Mrp_Qry_AnalyzerMrpMo: TFrm_Mrp_Qry_AnalyzerMrpMo;
  ItemCodemo,requiredatemo:string;
implementation
  uses Mrp_Qry_AnalyzerMrpMo_H, Mrp_Qry_AnalyzerMrpMo_Mo,
  Mrp_Qry_AnalyzerMrpMo_Po,Sys_Global,Mrp_Qry_MaterialMo;
{$R *.DFM}

{ TFrm_Mrp_Qry_AnalyzerMrpMo }

procedure TFrm_Mrp_Qry_AnalyzerMrpMo.InitForm(
  AdOConnection: TAdOConnection; ReadOnly: Boolean);
var sqltext1,sqltext2,sqltext3,sqltext4,tMpsqltext:string;
begin
  inherited;
  Application.ProcessMessages;
{  try
   Executesql(AdoQry_tmp,'drop table #tmpBom',1);
  except
  end;
 //   freefields:='requireqty,CurrentonhandInv,Currentoncheckqty,lessqty,wipqty,';
  tMpsqltext:='Select top 0 Bom.ItemCode,Bom.BomItemType,Item.PmCode,  '+#13+
            '        dateAdd(dd,(Item.MnLdTime)*(-1),'''+requiredate+''''+') As RequireDate, '+#13+
                    floattostr(requireqty)+'*Bom.BomQty*(1+ BomScrAp_Percent/100) as requireqty,'
            +' Item.ItemName,Item.CurrentonhandInv,Item.CurrentonCheckInv, '+#13+
            '       Itemflag=Bom.ItemCode+'' ''+Item.ItemName'+#13+
            ' into #tmpBom  '+#13+
            ' From Bom   '+#13+
            ' Join Item On Bom.ItemCode=Item.ItemCode  '+#13+
            ' where Bom.ite_ItemCode='''+ItemCode+'''';
  Executesql(AdoQry_tmp,tMpsqltext,1);
  Application.ProcessMessages;
 // openBom(ItemCode,requireqty,requiredate);
  sqltext2:=' declAre @Finishrate float(8)         '
            +' select @Finishrate=MoNoFinishqty/moqty from MoLine   '
            +'  where mono='+quotedstr(Orderno)
            +'    and MoLineno='+inttostr(Orderlineno)
            +' select InvOutBillline.*            '
            +' into #tmpInvOutBill                '
            +'      from InvOutBillline          '
            +'   join InvOutBill on InvOutBill.InvBillid=InvOutBillline.InvBillid '
            +'         and  InvOutBill.mono='+quotedstr(Orderno)
            +'         and  InvOutBill.MoLineno='+inttostr(Orderlineno)
            +'         and InvOutBill.BillTypeCode in (''0201'',''0202'')    '
            +' select #tmpBom.ItemCode,sum(isnull(MoNoFinishqty,0)+isnull(ponoFinishqty,0)) as wipqty,sum(isnull(#tmpInvOutBill.InvBillqty,0)*@Finishrate) as Assignqty '
            +' into #tmpBom1                                             '
            +'  from #tmpBom                                              '
           +' left outer join #tmpInvOutBill on  #tmpBom.ItemCode=#tmpInvOutBill.ItemCode  '
           +' left outer join MoLine on #tmpBom.ItemCode=MoLine.ItemCode  '
           +'         and     MoLine.MoLinedate<=#tmpBom.requiredate     '
           +'         and     MoLine.MoLinestatus=6                     '
           +' left outer join poline on #tmpBom.ItemCode=poline.ItemCode '
           +'         and     poline.polinedate<=#tmpBom.requiredate   '
           +'         and     poline.polinestatus=6     '
           +'   group by #tmpBom.ItemCode   '
           +'   drop table #tmpInvOutBill ';

   Executesql(AdoQry_tmp,sqltext2,1);
   Application.ProcessMessages;
  sqltext3:=' select #tmpBom.*,lessqty=convert(decimal(14,2),#tmpBom.requireqty-#tmpBom.CurrentonhandInv-#tmpBom.CurrentonCheckInv-isnull(#tmpBom1.Assignqty,0)),wipqty=isnull(#tmpBom1.wipqty,0),Assignqty=isnull(#tmpBom1.Assignqty,0) '+#13+
            ' into #tmpBomResult'+
            ' from #tmpBom,#tmpBom1'+#13+
            ' where #tmpBom.ItemCode*=#tmpBom1.ItemCode'+
            ' Order by #tmpBom.ItemCode,#tmpBom.requiredate';
   Executesql(AdoQry_tmp,sqltext3,1);     }
  tMpsqltext:='insert into #tmpBomResult(ItemCode,BomItemType,PmCode,requiredate,requireqty,netqty,  '
              +'                          ItemName,CurrentonhandInv,CurrentonCheckInv,Itemflag,'
              +'                          lessqty,wipqty,Planqty,Assignqty)    '   
              +' select  ItemCode,BomItemType,PmCode,max(requiredate),sum(requireqty),sum(netqty),ItemName,sum(case when minflag=1 then isnull(CurrentonhandInv,0) else 0 end),sum(case when minflag=1 then isnull(CurrentonCheckInv,0) else 0 end),'
              +' ItemCode+'' ''+ItemName+''  合计'' as Itemflag,'
              +'         sum(lessqty),sum(wipqty),sum(case when minflag=1 then Planqty else 0 end),sum(Assignqty) '
              +' from #tmpBomResult  '
              +' where ItemCode in (select ItemCode from (select ItemCode,Count(*) as CountItem from #tmpBomResult group by ItemCode) tmp where tmp.CountItem>=2)  '
              +' group by ItemCode,BomItemType,PmCode,ItemName ';
  Executesql(AdoQry_tmp,tMpsqltext,1)            ;

  sqltext4:='update #tmpBomResult set lessqty=null where lessqty<=0';
  Executesql(AdoQry_tmp,sqltext4,1);
  Application.ProcessMessages;
  selectfromsql:='select * from #tmpBomResult';
    with AdoQry_Main do
      begin
        Close;
        sql.clear;
        sql.Add('select * from #tmpBomResult Order by ItemCode,requiredate,Itemflag');
        Prepared;
        try
        open;
        except
        end;
      end;
  //  lbl_Condition.Caption:='父项物料代码：'+ItemCode;
end;

procedure TFrm_Mrp_Qry_AnalyzerMrpMo.FormDestroy(Sender: TObject);
begin
  inherited;
  Frm_Mrp_Qry_AnalyzerMrpMo:=nil;
end;

procedure TFrm_Mrp_Qry_AnalyzerMrpMo.Act_LookExecute(Sender: TObject);
begin
  inherited;
  if AdoQry_Main.fieldbyname('wipqty').asfloat<=0 then
    exit;
  ItemCodemo:=AdoQry_Main.fieldbyname('ItemCode').asstring;
  requiredatemo:=AdoQry_Main.fieldbyname('requiredate').asstring;
  if ((AdoQry_Main.fieldbyname('PmCode').asinteger=0) or (AdoQry_Main.fieldbyname('PmCode').asinteger=3)) then
    if Frm_Mrp_Qry_AnalyzerMrpMo_Mo=nil then
      begin
       Frm_Mrp_Qry_AnalyzerMrpMo_Mo:=TFrm_Mrp_Qry_AnalyzerMrpMo_Mo.Create(Self);
       Frm_Mrp_Qry_AnalyzerMrpMo_Mo.SetSysParam(userCode,ModuleCode,menuid,formatdatetime('yyyy.mm.dd',now));
       Frm_Mrp_Qry_AnalyzerMrpMo_Mo.InitForm(AdoQry_Main.Connection,True);
      end
    else Frm_Mrp_Qry_AnalyzerMrpMo_Mo.Show
  else
      if Frm_Mrp_Qry_AnalyzerMrpMo_Po=nil then
      begin
       Frm_Mrp_Qry_AnalyzerMrpMo_Po:=TFrm_Mrp_Qry_AnalyzerMrpMo_Po.Create(Self);
       Frm_Mrp_Qry_AnalyzerMrpMo_Po.SetSysParam(userCode,ModuleCode,menuid,formatdatetime('yyyy.mm.dd',now));
       Frm_Mrp_Qry_AnalyzerMrpMo_Po.InitForm(AdoQry_Main.Connection,True);
      end
    else Frm_Mrp_Qry_AnalyzerMrpMo_Po.Show

end;

procedure TFrm_Mrp_Qry_AnalyzerMrpMo.FormCreate(Sender: TObject);
begin
  inherited;
  tlbtn_Sum.Action:=act_Showgrid;
end;

procedure TFrm_Mrp_Qry_AnalyzerMrpMo.Act_ShowGridExecute(Sender: TObject);
begin
  inherited;
//showmessage('hello');
  if (AdoQry_Main.fieldbyname('PmCode').asinteger=0) or (AdoQry_Main.fieldbyname('PmCode').asinteger=3) then exit;
    if Frm_Mrp_Qry_MaterialMo=nil then
    begin
    Frm_Mrp_Qry_MaterialMo:=TFrm_Mrp_Qry_MaterialMo.Create(self);
    Frm_Mrp_Qry_MaterialMo.SetSysParam('vgfdg','vgf','1','2001.01.10');
    Frm_Mrp_Qry_MaterialMo.edtItemCode.Text:=AdoQry_Main.fieldbyname('ItemCode').asstring;
    Frm_Mrp_Qry_MaterialMo.InitForm(AdoQry_Main.Connection,True);
  end
  else Frm_Mrp_Qry_MaterialMo.Show;


end;

procedure TFrm_Mrp_Qry_AnalyzerMrpMo.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
  inherited;
  try
    Executesql(AdoQry_tmp,'drop table #tmpBom,#tmpBom1,#tmpBomResult',1);
  except
  end;
end;

end.