mrp_qry_mrpinfo.pas

一个MRPII系统源代码版本

unit Mrp_Qry_MrpInfo;

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_MrpInfo = 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_MainPmType: TIntegerField;
    AdoQry_MainSScheck: TIntegerField;
    AdoQry_MainGrossRequireQty: TFloatField;
    AdoQry_MaInPlanInvQty: TFloatField;
    AdoQry_MainItemName: TStringField;
    AdoQry_MainUomName: TStringField;
    AdoQry_MainDeptName: TStringField;
    AdoQry_MaInOrderLineStatus: TIntegerField;
    AdoQry_MainItemFlag: TStringField;
    AdoQry_MainDeptFlag: TStringField;
    AdoQry_MainDeptVendorCode: TStringField;
    Label2: TLabel;
    DBText2: TDBText;
    Label3: TLabel;
    DBText3: TDBText;
    Label1: TLabel;
    DBText1: TDBText;
    AdoQry_MainSSName: TStringField;
    Label4: TLabel;
    DBText4: TDBText;
    AdoQry_MainEmployeeCode: TStringField;
    AdoQry_MainEmployeeName: TStringField;
    AdoQry_MainEmployeeFlag: TStringField;
    AdoQry_MainDueDate: TDateTimeField;
    AdoQry_MainMnldTime: TFloatField;
    AdoQry_MainReMainQty: TFloatField;
    AdoQry_MaintMpssreMainqty: TFloatField;
    AdoQry_MainPmCode: TIntegerField;
    AdoQry_MainSysqty: TFloatField;
    AdoQry_Mainmoqty: TFloatField;
    btn_Item: TButton;
    btn_ite_Item: TButton;
    btn_Order: TButton;
    Button1: TButton;
    btn_uPMrp: TButton;
    btn_OblongMrp: TButton;
    Button2: TButton;
    Label5: TLabel;
    AdOCommand: TAdOCommand;
    procedure FormDestroy(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure FormCreate(Sender: TObject);
    procedure Act_LookExecute(Sender: TObject);
    procedure btn_ItemClick(Sender: TObject);
    procedure btn_ite_ItemClick(Sender: TObject);
    procedure btn_OrderClick(Sender: TObject);
    procedure Button1Click(Sender: TObject);
    procedure btn_uPMrpClick(Sender: TObject);
    procedure btn_OblongMrpClick(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure DataSourceDataChange(Sender: TObject; Field: TField);
    procedure DBGridEhGetCellParams(Sender: TObject; Column: TColumnEh;
      AFont: TFont; var Background: TColor; State: TGridDrawState);
  private
    function ismorethanzero(fieldName:string;tableName:string;conditionfield:string):boolean;
    { Private declarations }
  public
    procedure InitForm(AdOConnection:TAdOConnection;ShowExtendColumn:Boolean);Override;
   function getQclt(ItemCode:string):string;{ Public declarations }
    { Public declarations }
  end;

var
  Frm_Mrp_Qry_MrpInfo: TFrm_Mrp_Qry_MrpInfo;

implementation

uses Public_Show, Sys_Global, Mrp_Enter_MoSsParent,
  Mrp_Qry_InformalTotalMrp_Item, Mrp_Qry_InformalTotalMrp_Mo,
  Mrp_Qry_UpRightMrp_D, Mrp_Qry_OblongMrp_D, Mrp_Enter_CloseMo1;

{$R *.DFM}
procedure TFrm_Mrp_Qry_MrpInfo.InitForm(AdOConnection:TAdOConnection;ShowExtendColumn:Boolean);
var sqltext:string;
begin
  Application.ProcessMessages;
  Inherited;
    Adocommand.Connection:=AdoQry_Main.Connection;
    sqltext:='select ss.*,MoLine.moqty,Sysqty=ss.ssqty,ssreMainqty=reMainqty  into #tMpssInfoQry from ss                         '
           +'  left outer  join MoLine on ss.Orderno=MoLine.mono     '
           +'        and ss.Orderlineno=MoLine.MoLineno  '
           +' where (ssSysInfoflag=1)                    '
           +'  and  PmType=0   '
           +' union                                    '
           +' select ss.*,MoLine.moqty,Sysqty=ss.ssqty,ssreMainqty=0 from ss                         '
           +'      join MoLine with (index(MoLine_idx_Mono)) on ss.Orderno=MoLine.mono     '
           +'        and ss.Orderlineno=MoLine.MoLineno '
           +' where ssSysInfoflag=3                    '
           +'   and Orderlinestatus<>7                 '
           +'   and exists(select mono from MoLine where Orderno=MoLine.mono and Orderlineno=MoLine.MoLineno and MoLine.MoLinestatus<>7) '
           +' union                                    '
           +' select ss.*,MoLine.moqty,Sysqty=ss.ssqty,ssreMainqty=0 from ss                         '
           +'  join MoLine on ss.Orderno=MoLine.mono '
           +'    and ss.Orderlineno=MoLine.MoLineno  '
           +' where ssSysInfoflag=4                    '
          // +'   and Orderlinestatus<>7                 '
           +'   and exists(select mono from MoLine where Orderno=MoLine.mono and Orderlineno=MoLine.MoLineno ) '
           +' union                                    '
           +' select ss.*,    '
           +'       MoLine.moqty, '
           +'       Sysqty=case  when ss.reMainqty<MoLine.moqty then MoLine.moqty-ss.reMainqty '
           +'             else 0  '
           + '             end,          '
           +'        ssreMainqty=0       '
           +'   from ss,MoLine   with (index(MoLine_idx_Mono))        '
           +'  where ssSysInfoflag=2     '
            +'  and Orderlinestatus<>7   '
            +'  and exists(select mono from MoLine where Orderno=MoLine.mono) '
            +'  and ss.Orderno=MoLine.mono          '
            +'  and ss.Orderlineno=MoLine.MoLineno  '
            +'  create  nonclustered index ssindex on #tMpssInfoQry(ssdate)';
 //  Executesql(AdoQry_Main,sqltext,1);
   with Adocommand do
    begin
     commandtext:=sqltext;
     Execute;
    end;
    sqltext:='select #tMpssInfoQry.*,tMpssreMainqty=case #tMpssInfoQry.ssreMainqty when 0 then null else #tMpssInfoQry.ssreMainqty end,Item.mnldtime,Item.ItemName,Uom.UomName,Dept.DeptCode,Dept.DeptName,'
            +'  Employee.EmployeeCode,Employee.EmployeeName,'
                +' ssflag=convert(varchAr,#tMpssInfoQry.ssSysInfoflag)+'' ''+SysssInfo.ssName,'
                +' Employee.EmployeeCode+'''+' '+'''+Employee.EmployeeName As EmployeeFlag,'
                +' Dept.DeptCode+'''+' '+'''+Dept.DeptName As DeptFlag, '
                +' Item.ItemCode+'''+' '+'''+Item.ItemName As ItemFlag,'
                +' Item.PmCode,'
                +' ConVert(varchAr,SysSsInfo.SSCode)+'''+' '+'''+SysSsInfo.SsName As SsName'
                +'  into #tMpssInfoResultQry '
                +' From #tMpssInfoQry '
                +' Join Item On #tMpssInfoQry.ItemCode=Item.ItemCode '
                +' And PmType=0 '
                +' Left Join Dept On  isnull(#tMpssInfoQry.DeptVendorCode,Item.DeptCode)=Dept.DeptCode'
//                +' Left Join Dept On (Case When #tMpssInfoQry.SsSysInfoFlag<>1 Then #tMpssInfoQry.DeptVendorCode Else Item.DeptCode end)=Dept.DeptCode'
                +' Left Join Employee On Item.Pla_EmployeeCode=Employee.EmployeeCode'
                +' Join Uom On Item.UomCode=Uom.UomCode'
                +' Join SysSsInfo On #tMpssInfoQry.SSSysInfoFlag=SysSsInfo.SSCode'
                +' where (ReMainQty>0 '
                      +' And (#tMpssInfoQry.SsSysInfoFlag=1 '
                      +' And Item.LongPurchLT=0 And #tMpssInfoQry.DueDate-(select MrpParamValueN from MrpParam '
                      +' where MrpParamCode='''+'SSForwArdDay'+''')<='''+DateTimeToStr(Date())+''')'
                      +' Or (#tMpssInfoQry.SsSysInfoFlag=1 And Item.LongPurchLT=1) Or (#tMpssInfoQry.SsSysInfoFlag<>1 and #tMpssInfoQry.Sysqty<>0)) '
                      +' Order by #tMpssInfoQry.ItemCode,ssdate '
                +' create nonclustered index reindex on #tMpssInfoResultQry(ssdate,EmployeeCode) ';
//     Executesql(AdoQry_Main,sqltext,1);
  with Adocommand do
    begin
     commandtext:=sqltext;
     Execute;
    end;
     Executesql(AdoQry_Main,'select * from #tMpssInfoResultQry',0);
    selectfromsql:='select * from #tMpssInfoResultQry';
//---------------------------------------------------------------------------
  {SelectFromSql:=' Select SS.*,Item.MnldTime,Item.ItemName,UomName,Dept.DeptCode,Dept.DeptName,'
                +' ConVert(varchAr(10),SysSsInfo.SSCode)+'''+' '+'''+SysSsInfo.SsName As SsName,'
                +' Ss.DeptVendorCode+'''+' '+'''+DeptName As DeptFlag, '
                +' Item.ItemCode+'''+' '+'''+Item.ItemName As ItemFlag, '
                +' Employee.EmployeeCode,Employee.EmployeeName,'
                +' Employee.EmployeeCode+'''+' '+'''+Employee.EmployeeName As EmployeeFlag'
                +' From SS '
                +' Join Item On SS.ItemCode=Item.ItemCode '
                +' And  PmType=0 '
                +' Join Uom On Item.UomCode=Uom.UomCode '
                +' Left Join Dept On (Case When Ss.SsSysInfoFlag<>1 Then Ss.DeptVendorCode Else Item.DeptCode end)=Dept.DeptCode '
                +' Left Join Employee On Item.Pla_EmployeeCode=Employee.EmployeeCode '
                +' Join SysSsInfo On Ss.SSSysInfoFlag=SysSsInfo.SSCode ';
  ConditionUserDefine:=' (ReMainQty>0 '
                      +' And (Ss.SsSysInfoFlag=1 '
                      +' And Item.LongPurchLT=0 And Ss.DueDate-(select MrpParamValueN from MrpParam '
                      +' where MrpParamCode='''+'SSForwArdDay'+''')<='''+DateTimeToStr(Date())+''')'
                      +' Or (Ss.SsSysInfoFlag=1 And Item.LongPurchLT=1) Or (Ss.SsSysInfoFlag<>1)) ';  OrderByfields:='SsDate,EmployeeCode ';
  OrderByFields:='SSDate,EmployeeCode ';
  GetData;  }
  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:='Select MrpParamValueN from MrpParam '
                      +' where MrpParamCode='''+'SSForwArdDay'+'''';
  AdoQry_Tmp.Open;
  Lbl_Condition.Caption:='系统建议下达(约定开工日-'+FormatDateTime('yyyy.mm.dd',Date())+')'
                        +'<=建议可见提前天数('+IntToStr(AdoQry_Tmp.fieldbyname('MrpParamValueN').AsInteger)+')   其它(全部)';
  AdoQry_Tmp.Close;
end;

procedure TFrm_Mrp_Qry_MrpInfo.FormDestroy(Sender: TObject);
begin
  inherited;
  Frm_Mrp_Qry_MrpInfo:=Nil;
end;

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

procedure TFrm_Mrp_Qry_MrpInfo.FormCreate(Sender: TObject);
begin
  inherited;
  activecontrol:=dbgrideh;
  Frm_Public_Show:=TFrm_Public_Show.Create(Application);
end;

procedure TFrm_Mrp_Qry_MrpInfo.Act_LookExecute(Sender: TObject);
begin
  inherited;
   If (AdoQry_Main.RecordCount>0) And (AdoQry_Main.fieldbyname('SsSysInfoFlag').AsInteger=1) Then
   // MoSsInitForm(DbConnect,AdoQry_Main.fieldbyname('ItemCode').AsString,
     //          DateTimeToStr(AdoQry_Main.fieldbyname('SsDate').AsDateTime))
 if  Frm_Mrp_Enter_MoSsParent=nil then
 begin
  Frm_Mrp_Enter_MoSsParent:=TFrm_Mrp_Enter_MoSsParent.Create(Self);
  Frm_Mrp_Enter_MoSsParent.SetSysParam(userCode,ModuleCode,menuid,formatdatetime('yyyy.mm.dd',now));
  Frm_Mrp_Enter_MoSsParent.ItemCode:=AdoQry_Main.fieldbyname('ItemCode').asstring;
  tmplowPmCode:=AdoQry_Main.fieldbyname('PmCode').asinteger;
  Frm_Mrp_Enter_MoSsParent.Qclt:=getQclt(AdoQry_Main.fieldbyname('ItemCode').asstring);
  Frm_Mrp_Enter_MoSsParent.duedate:=datetimetostr(AdoQry_Main.fieldbyname('ssdate').asdatetime);
  Frm_Mrp_Enter_MoSsParent.strpath:=AdoQry_Main.fieldbyname('ItemCode').asstring+',';
  Frm_Mrp_Enter_MoSsParent.InitForm(dbconnect,True);
 end
 else  Frm_Mrp_Enter_MoSsParent.Show;
end;

function TFrm_Mrp_Qry_MrpInfo.getQclt(ItemCode: string): string;
begin
try
 Executesql(AdoQry_tmp,'select Qclt from Item where ItemCode='+quotedstr(ItemCode),0);
 Result:=inttostr(AdoQry_tmp.fieldbyname('Qclt').asinteger);
except
 Result:='9999999';
end;
end;

procedure TFrm_Mrp_Qry_MrpInfo.btn_ItemClick(Sender: TObject);
begin
  inherited;
  Qry_Item(dbconnect,AdoQry_Main.fieldbyname('ItemCode').asstring);
  activecontrol:=dbgrideh;

end;

procedure TFrm_Mrp_Qry_MrpInfo.btn_ite_ItemClick(Sender: TObject);
begin
  inherited;
  try
    if  Frm_Mrp_Qry_InformalTotalMrp_Item=nil then
      begin
        Frm_Mrp_Qry_InformalTotalMrp_Item:=TFrm_Mrp_Qry_InformalTotalMrp_Item.Create(Self);
        Frm_Mrp_Qry_InformalTotalMrp_Item.SetSysParam(userCode,ModuleCode,menuid,formatdatetime('yyyy.mm.dd',now));
        Frm_Mrp_Qry_InformalTotalMrp_Item.ItemCode:=AdoQry_Main.fieldbyname('ItemCode').asstring;
        Frm_Mrp_Qry_InformalTotalMrp_Item.InitForm(dbconnect,True);
      end
      else  Frm_Mrp_Qry_InformalTotalMrp_Item.Show;
  finally
    activecontrol:=dbgrideh;
  end;