mrp_qry_newmrpinfo.pas

一个MRPII系统源代码版本

           +'   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=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<>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  ';

//   Executesql(AdoQry_Main,sqltext,1);
   with Adocommand do
    begin
     commandtext:=sqltext;
     Execute;
    end;
    sqltext:='select #tMpssInfoQryQry.*,tMpssreMainqty=case #tMpssInfoQryQry.ssreMainqty when 0 then null else #tMpssInfoQryQry.ssreMainqty end,Item.mnldtime,Item.ItemName,Uom.UomName,Dept.DeptCode,Dept.DeptName,'
            +'  Employee.EmployeeCode,Employee.EmployeeName,'
                +' ssflag=convert(varchAr,#tMpssInfoQryQry.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 #tMpssInfoQryResultQry '
                +' From #tMpssInfoQryQry '
                +' Join Item On #tMpssInfoQryQry.ItemCode=Item.ItemCode '
                +' And #tMpssInfoQryQry.PmCode=0 '
                +' Left Join Dept On  isnull(#tMpssInfoQryQry.DeptVendorCode,Item.DeptCode)=Dept.DeptCode'
//                +' Left Join Dept On (Case When #tMpssInfoQryQry.SsSysInfoFlag<>1 Then #tMpssInfoQryQry.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 #tMpssInfoQryQry.SSSysInfoFlag=SysSsInfo.SSCode'
                +' where (ReMainQty>0 '
                      +' And (#tMpssInfoQryQry.SsSysInfoFlag=1 '
                      +' And Item.LongPurchLT=0 And #tMpssInfoQryQry.DueDate-(select MrpParamValueN from MrpParam '
                      +' where MrpParamCode='''+'SSForwArdDay'+''')<='''+DateTimeToStr(Date())+''')'
                      +' Or (#tMpssInfoQryQry.SsSysInfoFlag=1 And Item.LongPurchLT=1) Or (#tMpssInfoQryQry.SsSysInfoFlag<>1 and #tMpssInfoQryQry.Sysqty<>0)) '
                      +' Order by #tMpssInfoQryQry.ItemCode,#tMpssInfoQryQry.ssdate '
                +' create nonclustered index reindex on #tMpssInfoQryResultQry(ssdate,EmployeeCode) ';

//     Executesql(AdoQry_Main,sqltext,1);
   with Adocommand do
    begin
     commandtext:=sqltext;
     Execute;
    end;
     Executesql(AdoQry_Main,'select * from #tMpssInfoQryResultQry',0);
    selectfromsql:='select * from #tMpssInfoQryResultQry';
//---------------------------------------------------------------------------
  {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;
    if ShowAnimate<>nil then ShowAnimate.Free;
  if ShowPanel<>nil then ShowPanel.Free;
 }
end;

procedure TFrm_Mrp_Qry_NewMrpInfo.FormDestroy(Sender: TObject);
begin
  inherited;
  Frm_Mrp_Qry_NewMrpInfo:=Nil;
end;

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

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

procedure TFrm_Mrp_Qry_NewMrpInfo.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_NewMoSsParent=nil then
 begin
  Frm_Mrp_Enter_NewMoSsParent:=TFrm_Mrp_Enter_NewMoSsParent.Create(Self);
  Frm_Mrp_Enter_NewMoSsParent.SetSysParam(userCode,ModuleCode,menuid,formatdatetime('yyyy.mm.dd',now));
  Frm_Mrp_Enter_NewMoSsParent.ItemCode:=AdoQry_Main.fieldbyname('ItemCode').asstring;
  tmplowPmCode:=AdoQry_Main.fieldbyname('PmCode').asinteger;
  Frm_Mrp_Enter_NewMoSsParent.Qclt:=getQclt(AdoQry_Main.fieldbyname('ItemCode').asstring);
  Frm_Mrp_Enter_NewMoSsParent.duedate:=datetimetostr(AdoQry_Main.fieldbyname('ssdate').asdatetime);
  Frm_Mrp_Enter_NewMoSsParent.strpath:=AdoQry_Main.fieldbyname('ItemCode').asstring+',';
  Frm_Mrp_Enter_NewMoSsParent.InitForm(dbconnect,True);
 end
 else  Frm_Mrp_Enter_NewMoSsParent.Show;
end;

function TFrm_Mrp_Qry_NewMrpInfo.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_NewMrpInfo.btn_ItemClick(Sender: TObject);
begin
  inherited;
  Qry_Item(dbconnect,AdoQry_Main.fieldbyname('ItemCode').asstring);
  activecontrol:=dbgrideh;

end;

procedure TFrm_Mrp_Qry_NewMrpInfo.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;

end;

procedure TFrm_Mrp_Qry_NewMrpInfo.btn_OrderClick(Sender: TObject);
begin
  inherited;
  try
    if ismorethanzero('moqty','MoLine','ItemCode')=True then
   if Frm_Mrp_Qry_InformalTotalMrp_Mo=nil then
          begin
           Frm_Mrp_Qry_InformalTotalMrp_Mo:=TFrm_Mrp_Qry_InformalTotalMrp_Mo.Create(Self);
           Frm_Mrp_Qry_InformalTotalMrp_Mo.ItemCode:=AdoQry_Main.fieldbyname('ItemCode').asstring;
           Frm_Mrp_Qry_InformalTotalMrp_Mo.SetSysParam(userCode,ModuleCode,menuid,formatdatetime('yyyy.mm.dd',now));
           Frm_Mrp_Qry_InformalTotalMrp_Mo.InitForm(AdoQry_Main.Connection,True);
          end
        else Frm_Mrp_Qry_InformalTotalMrp_Mo.Show;
 finally
 activecontrol:=dbgrideh;
 end;

end;

procedure TFrm_Mrp_Qry_NewMrpInfo.Button1Click(Sender: TObject);
begin
  inherited;
try
 If (AdoQry_Main.RecordCount>0) And (AdoQry_Main.fieldbyname('SsSysInfoFlag').AsInteger=1) Then
 if  Frm_Mrp_Enter_NewMoSsParent=nil then
 begin
  Frm_Mrp_Enter_NewMoSsParent:=TFrm_Mrp_Enter_NewMoSsParent.Create(Self);
  Frm_Mrp_Enter_NewMoSsParent.SetSysParam(userCode,ModuleCode,menuid,formatdatetime('yyyy.mm.dd',now));
  Frm_Mrp_Enter_NewMoSsParent.ItemCode:=AdoQry_Main.fieldbyname('ItemCode').asstring;
  tmplowPmCode:=AdoQry_Main.fieldbyname('PmCode').asinteger;
  Frm_Mrp_Enter_NewMoSsParent.peggingType:=2;
  Frm_Mrp_Enter_NewMoSsParent.Qclt:=getQclt(AdoQry_Main.fieldbyname('ItemCode').asstring);
  Frm_Mrp_Enter_NewMoSsParent.duedate:=datetimetostr(AdoQry_Main.fieldbyname('realreleasedate').asdatetime);
  Frm_Mrp_Enter_NewMoSsParent.strpath:=AdoQry_Main.fieldbyname('ItemCode').asstring+',';
  Frm_Mrp_Enter_NewMoSsParent.InitForm(dbconnect,True);
 end
 else  Frm_Mrp_Enter_NewMoSsParent.Show;
finally
activecontrol:=dbgrideh;
end;


end;

procedure TFrm_Mrp_Qry_NewMrpInfo.btn_uPMrpClick(Sender: TObject);
var AdoQry:TAdoQuery;
    sqltext:string;
begin
  inherited;
  AdoQry:=TAdoQuery.Create(self);
  AdoQry.Connection:=dbconnect;

try
  sqltext:=' Select distinct Item.ItemCode,Item.ItemName,Item.minqty,MrpResult.PmCode,Item.Batchstrat,'+
              ' Itemflag=Item.ItemCode+'' ''+Item.ItemName,'+
             ' UomName=Uom.UomName,'+
             ' ldtime=case MrpResult.PmCode '+
             '     when 0 then Item.mnldtime '+
             '     else Item.purchldtime '+
             '   end,'+
             ' Item.Preparelt,'+
             ' Item.runlt,'+
             ' Item.Qclt,'+
             ' Item.PmBatch, '+