mrp_enter_newmrpinfo.pas

一个MRPII系统源代码版本

unit Mrp_Enter_NewMrpInfo;

Interface

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

Type
  TFrm_Mrp_Enter_NewMrpInfo = Class(TFrm_Base_Outer)
    AdoQry_MainItemCode: TStringField;
    AdoQry_MainSSSysInfoFlag: TIntegerField;
    AdoQry_MaInOrderNo: TStringField;
    AdoQry_MaInOrderLineNo: TIntegerField;
    AdoQry_MainSSDate: TDateTimeField;
    AdoQry_MainItemName: TStringField;
    AdoQry_MainDeptName: TStringField;
    AdoQry_MainUomName: TStringField;
    AdoQry_MainSScheck: TIntegerField;
    AdoQry_MaInOrderLineStatus: TIntegerField;
    AdoQry_MainDeptFlag: TStringField;
    AdoQry_MainItemFlag: TStringField;
    AdoQry_MainDeptVendorCode: TStringField;
    AdoQry_MainDeptCode: TStringField;
    Label1: TLabel;
    DBText1: TDBText;
    AdoQry_MainSSName: TStringField;
    AdoQry_MainEmployeeName: TStringField;
    AdoQry_MainEmployeeFlag: TStringField;
    Label4: TLabel;
    DBText4: TDBText;
    AdoQry_MaindueDate: TDateTimeField;
    AdoQry_MainMnldTime: TFloatField;
    AdoQry_MainEmployeeCode: TStringField;
    AdoQry_MainReMainQty: TFloatField;
    AdoQry_Mainssflag: TStringField;
    lbl_RunMrpdate: TLabel;
    Label5: TLabel;
    AdoQry_MaintMpssreMainqty: TFloatField;
    AdOCommand: TAdOCommand;
    AdoQry_MainPmCode: TIntegerField;
    AdoQry_MainSysqty: TFloatField;
    Label2: TLabel;
    DBText2: TDBText;
    btn_Item: TButton;
    btn_ite_Item: TButton;
    btn_Order: TButton;
    AdoQry_Mainmoqty: TFloatField;
    btn_uPMrp: TButton;
    btn_OblongMrp: TButton;
    Button1: TButton;
    Button2: TButton;
    AdoQry_MainRealReleaseDate: TDateTimeField;
    Label3: TLabel;
    Button3: TButton;
    AdoQry_MainSSId: TAutoIncField;
    AdoQry_MainSSQty: TFloatField;
    AdoQry_MainMrpResultid: TIntegerField;
    AdoQry_Maincanuseonhand: TFloatField;
    Button4: TButton;
    AdoQry_Mainhaslocated: TIntegerField;
    AdoQry_Mainwcflag: TStringField;
    Label6: TLabel;
    DBText3: TDBText;
    Button5: TButton;
    AdoQry_MainPeggingNo: TStringField;
    procedure FormDestroy(Sender: TObject);
    procedure Act_NewExecute(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure Act_CheckExecute(Sender: TObject);
    procedure AdoQry_MainBeforeInsert(DataSet: TDataSet);
    procedure DBGridEhTitleClick(Column: TColumnEh);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure AdoQry_MainBeforeEdit(DataSet: TDataSet);
    procedure btn_ItemClick(Sender: TObject);
    procedure btn_ite_ItemClick(Sender: TObject);
    procedure btn_OrderClick(Sender: TObject);
    procedure btn_uPMrpClick(Sender: TObject);
    procedure btn_OblongMrpClick(Sender: TObject);
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure DataSourceDataChange(Sender: TObject; Field: TField);
    procedure Button3Click(Sender: TObject);
    procedure DBGridEhGetCellParams(Sender: TObject; Column: TColumnEh;
      AFont: TFont; var Background: TColor; State: TGridDrawState);
    procedure Button4Click(Sender: TObject);
    procedure Act_CopyExecute(Sender: TObject);
    procedure Act_SumExecute(Sender: TObject);
    procedure AdoQry_MainAfterPost(DataSet: TDataSet);
    procedure Button5Click(Sender: TObject);
    procedure FormKeyDown(Sender: TObject; var Key: Word;
      Shift: TShiftState);
  private
    Checked:Boolean;
    function ismorethanzero(fieldName:string;tableName:string;conditionfield:string):boolean;
    { Private declarations }
  public
    procedure InitForm(AdOConnection:TAdOConnection;ReadOnly:Boolean);Override;
    function getMrpdate:string;
   function getQclt(ItemCode:string):string;{ Public declarations }
    function getstatus(mono:string;MoLineno:integer):integer;
    procedure updateMoLine(mono:string;MoLineno:integer;MoLinedate,mostArtworkdate:string;moqty,MoNoFinishqty:double;isupdate:boolean);
    { Public declarations }
  end;

var
  Frm_Mrp_Enter_NewMrpInfo: TFrm_Mrp_Enter_NewMrpInfo;

implementation
uses Sys_Global,Mrp_Global, Mrp_Enter_NewAutoMo, Public_Show, Mrp_Enter_NewMoSsParent,
  Mrp_Qry_UpRightMrp_D_Item, Mrp_Qry_InformalTotalMrp_Item,
  Mrp_Qry_InformalTotalMrp_Po, Mrp_Qry_InformalTotalMrp_Mo,
  Mrp_Qry_NewUpRightMrp_D, Mrp_Enter_CloseMo1,Bas_Qry_MultiBomSViewMaster,
  Mrp_Qry_NewOblongMrp_D,Mrp_Enter_NewEditPassMo1, Mrp_Enter_NewAutoMo1,
  Mrp_Enter_NewMrpInfo3, Mrp_Enter_NewMrpInfo3_C, Mrp_Enter_PassMo_D,
  Bas_BomQry;
{$R *.DFM}

procedure TFrm_Mrp_Enter_NewMrpInfo.updateMoLine(mono: string;
  MoLineno: integer;MoLinedate,mostArtworkdate:string;moqty,MoNoFinishqty:double;isupdate:boolean);
var AdoQry:TAdoQuery;
    sqltext:string;
begin
  AdoQry:=TAdoQuery.Create(self);
  AdoQry.Connection:=dbconnect;
  sqltext:='update MoLine  '
           +' set MoLinedate= '+quotedstr(MoLinedate)+','
           +'     MoLinestatus='+iifstring(isupdate,'5',inttostr(getstatus(mono,MoLineno)))+','
           +'     mostArtworkdate='+quotedstr(mostArtworkdate)+' '
           +iifstring((getstatus(mono,MoLineno)=6) and (not isupdate),' ',   ',     moqty='+floattostr(moqty)+','
           +'     MoNoFinishqty='+floattostr(MoNoFinishqty) )
           +' where mono='+quotedstr(mono)
           +'   and MoLineno='+inttostr(MoLineno);
  try
    Executesql(AdoQry,sqltext,1);
  finally
    AdoQry.Free;
  end;
end;

procedure TFrm_Mrp_Enter_NewMrpInfo.InitForm(AdOConnection:TAdOConnection;ReadOnly:Boolean);
var sqltext:string;
begin                                           
  Application.ProcessMessages;
  createpanel(1,dbgrideh);
  inherited;
  If ReadOnly=True Then
  begin
    TlBtn_Copy.Visible:=False;
  end;
  lbl_RunMrpdate.Caption:=getMrpdate;
  Adocommand.Connection:=AdoQry_Main.Connection;
{  sqltext:='select MrpResult.*,ssid=MrpResultid,ssdate=releasedate,ssSysInfoflag=1,MoLine.moqty,ssqty=MrpResult.onhand,Sysqty=MrpResult.onhand,ssreMainqty=reMainqty '
           +'  into #tMpssInfo from MrpResult                '
           +' left outer join MoLine on MrpResult.Orderno=MoLine.mono '
           +'    and MrpResult.Orderlineno=MoLine.MoLineno  '
           +' where (ordinal=3 )                    '
           +'  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 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 (releasedate<dateName(yy,getdate())+''.''+dateName(mm,getdate())+''.''+dateName(dd,getdate()) or duedate<dateName(yy,getdate())+''.''+dateName(mm,getdate())+''.''+dateName(dd,getdate()))  '
          // +'   and Orderlinestatus<>7                 '
           +'   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=6,MoLine.moqty,ssqty=MrpResult.onhand,Sysqty=MrpResult.onhand,ssreMainqty=0 from MrpResult                         '
           +'  join MoLine on MrpResult.Orderno=MoLine.mono '
           +'    and MrpResult.Orderlineno=MoLine.MoLineno  '
           +' where ordinal=2                    '
           +'   and Orderlinestatus<>7 ' 
           +'   and canuseonhand=1  '
          // +'   and Orderlinestatus<>7                 '
           +'   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,   '
          // +'       Sysqty=case  when ss.reMainqty<MoLine.moqty then MoLine.moqty-ss.reMainqty '
          // +'             else 0  '
          // +'             end,          '
          +'         ssqty=Orderqty-onhand, '
           +'        Sysqty=Orderqty-onhand,  '
           +'        ssreMainqty=0       '
           +'   from MrpResult,MoLine           '
           +'  where (ordinal=2 )    '
            +'  and Orderlinestatus<>7   '
            +'  and Orderqty>onhand      '
            +'  and MrpResult.ItemCode not  in (select ItemCode from MrpResult with(index(ordinal)) where ordinal=4 )'
//            +'  and exists(select mono from MoLine where Orderno=MoLine.mono) '
            +'  and MrpResult.Orderno=MoLine.mono          '
            +'  and MrpResult.Orderlineno=MoLine.MoLineno  '
           +' union                                    '
           +' select MrpResult.*,    '
           +'        ssid=MrpResultid,'
           +'        ssdate=releasedate, '
           +'        ssSysInfoflag=5, '
           +'        MoLine.moqty,   '
          // +'       Sysqty=case  when ss.reMainqty<MoLine.moqty then MoLine.moqty-ss.reMainqty '
          // +'             else 0  '
          // +'             end,          '
          +'         ssqty=Orderqty-onhand, '
           +'        Sysqty=Orderqty-onhand,  '
           +'        ssreMainqty=0       '
           +'   from MrpResult,MoLine           '
           +'  where (ordinal=2 )    '
            +'  and Orderlinestatus<>7   '
            +'  and Orderqty>onhand      '
            +'  and MrpResult.ItemCode in (select ItemCode from MrpResult   with(index(ordinal)) where ordinal=4)'
//            +'  and exists(select mono from MoLine where Orderno=MoLine.mono) '
            +'  and MrpResult.Orderno=MoLine.mono          '
            +'  and MrpResult.Orderlineno=MoLine.MoLineno  ';

  with Adocommand do
    begin
     commandtext:=sqltext;
     Execute;
    end;
   //Executesql(AdoQry_Main,sqltext,1);
    sqltext:='select #tMpssInfo.*,tMpssreMainqty=case #tMpssInfo.ssreMainqty when 0 then null else #tMpssInfo.ssreMainqty end,Item.mnldtime,Item.ItemName,Uom.UomName,Dept.DeptCode,Dept.DeptName,'
            +'  Employee.EmployeeCode,Employee.EmployeeName,'
                +' haslocated=0, '
                +' ssflag=convert(varchAr,#tMpssInfo.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 #tMpssInfoResult '
                +' From #tMpssInfo '
                +' Join Item On #tMpssInfo.ItemCode=Item.ItemCode '
                +' And #tMpssInfo.PmCode=0 '
                +' Left Join Dept On  isnull(#tMpssInfo.DeptVendorCode,Item.DeptCode)=Dept.DeptCode'
                //+' Left Join Dept On (Case When #tMpssInfo.SsSysInfoFlag<>1 Then #tMpssInfo.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 #tMpssInfo.SSSysInfoFlag=SysSsInfo.SSCode'
                +' where  ((#tMpssInfo.SsSysInfoFlag=1 '
                      +' And Item.LongPurchLT=0 And #tMpssInfo.DueDate-(select MrpParamValueN from MrpParam '
                      +' where MrpParamCode='''+'SSForwArdDay'+''')<='''+DateTimeToStr(Date())+''')'
                      +' Or (#tMpssInfo.SsSysInfoFlag=1 And Item.LongPurchLT=1) Or (#tMpssInfo.SsSysInfoFlag<>1 and #tMpssInfo.Sysqty<>0)) '
                      +' Order by #tMpssInfo.ItemCode,releasedate ';
}
  sqltext:=' exec sp_MrpInfo '''+userCode+''',0 ';
  with Adocommand do
    begin
     commandtext:=sqltext;
     Execute;
    end;
  sqltext:=' select * into #tMpssInfoResult from SSProductionInfoResult ';
  Executesql(AdoQry_tmp,sqltext,1);
    // Executesql(AdoQry_Main,sqltext,1);
  SqlText :=' update #tMpssInfoResult       '
           + '   set peggingNo=case when #tMpssInfoResult.pegging=''系统建议下达'' then IsNull(Mps.MpsBatchNo,#TMpssInfoResult.PeggingNo)                  '
           +'                     else '''' '
           +'                  end '
           + '  from #tMpssInfoResult                     '
           +'   join  MrpResult on #TMpsSInfoResult.ItemCode=MrpResult.ItemCode '
           +'          and #TMpsSInfoResult.RealReleaseDate=MrpResult.RealReleaseDate '
           +'          and MrpResult.Pegging=''主计划需求'' '
           + '  join Mps on   MrpResult.peggingNo=convert(varchAr,Mps.MpsId)'
           +'   where #TMpsSInfoResult.Pegging=''系统建议下达''';
  ExecuteSql(AdoQry_Tmp,SqlText,1);
  Executesql(AdoQry_Main,'select * from #tMpssInfoResult Order by ItemCode,releasedate',0);
  selectfromsql:='select * from #tMpssInfoResult';
//  OrderByFields := 'ItemCode,ReleaseDate';
  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;
  Checked:=False;
  if ShowAnimate<>nil then ShowAnimate.Free;
  if ShowPanel<>nil then ShowPanel.Free;

  Application.ProcessMessages;
end;

procedure TFrm_Mrp_Enter_NewMrpInfo.FormDestroy(Sender: TObject);
begin
  inherited;
  Frm_Mrp_Enter_NewMrpInfo:=Nil;
end;

procedure TFrm_Mrp_Enter_NewMrpInfo.Act_NewExecute(Sender: TObject);
var
  I:Boolean;
  BookmArk:String;
begin
  inherited;
  If AdoQry_Main.RecordCount=0   Then
  begin
    Abort;
  end;
  BookmArk:=AdoQry_Main.BookmArk;
//  I:=False;
{  AdoQry_Main.First;
  While Not AdoQry_Main.Eof do
  begin
    If AdoQry_Main.fieldbyname('sScheck').AsInteger>0 Then
    begin
     I:=True;
     Break;
    end;
    AdoQry_Main.Next;
  end;        }
  if AdoQry_Main.State=dsedit then AdoQry_Main.Post;
  Executesql(AdoQry_tmp,'select * from #tMpssInfoResult where sScheck=1',0);
  if AdoQry_tmp.RecordCount=0 then abort;
  try
//  If I=True Then
  begin
    Frm_Mrp_Enter_NewAutoMo:=TFrm_Mrp_Enter_NewAutoMo.Create(Application);
    Frm_Mrp_Enter_NewAutoMo.SetSysParam(UserCode,ModuleCode,MenuId,DateTimeToStr(Now));
    Frm_Mrp_Enter_NewAutoMo.edt_DeptCode.Text:=AdoQry_Main.fieldbyname('DeptCode').asstring;
    Frm_Mrp_Enter_NewAutoMo.edt_DeptName.Text:=AdoQry_Main.fieldbyname('DeptName').asstring;
    reMainqty:=AdoQry_Main.fieldbyname('reMainqty').asfloat;
    Frm_Mrp_Enter_NewAutoMo.flag:=0;
    Frm_Mrp_Enter_NewAutoMo.GetConnect(AdoQry_Main);
    Frm_Mrp_Enter_NewAutoMo.ShowModal;
  end;
  finally
    AdoQry_Main.BookmArk:=BookmArk;
  end;
end;

procedure TFrm_Mrp_Enter_NewMrpInfo.FormCreate(Sender: TObject);
begin
  inherited;
  ExtendCaption:=False;
  activecontrol:=dbgrideh;
  toolbutton6.Action:=act_Sum;
//  TlBtn_Copy.Action:=Act_Check;
end;

procedure TFrm_Mrp_Enter_NewMrpInfo.Act_CheckExecute(Sender: TObject);
var
  BookmArk:String;
begin
  inherited;
  If AdoQry_Main.RecordCount=0 Then
    Abort;
  BookmArk:=AdoQry_Main.BookmArk;
  AdoQry_Main.First;
  While Not AdoQry_Main.Eof Do
  begin
    AdoQry_Main.Edit;
    AdoQry_Main.fieldbyname('SScheck').AsInteger:=1;
    AdoQry_Main.Post;
    AdoQry_Main.Next;
  end;
  AdoQry_Main.BookmArk:=BookmArk;
end;

procedure TFrm_Mrp_Enter_NewMrpInfo.AdoQry_MainBeforeInsert(
  DataSet: TDataSet);
begin
  inherited;
  Abort;
end;

procedure TFrm_Mrp_Enter_NewMrpInfo.DBGridEhTitleClick(Column: TColumnEh);
var
  BookMArk:String;
begin
  inherited;