pm_qry_normalsspoinfo.pas

一个MRPII系统源代码版本

unit Pm_Qry_NormalSsPoInfo;
 
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_Pm_Qry_NormalSsPoInfo = Class(TFrm_Base_Outer)
    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_MainUomCode: TStringField;
    AdoQry_MainUomName: TStringField;
    AdoQry_MaInOrderLineStatus: TIntegerField;
    AdoQry_MainItemflag: TStringField;
    AdoQry_MainSsName: TStringField;
    Label1: TLabel;
    DBText1: TDBText;
    DBText2: TDBText;
    Label2: TLabel;
    Label3: TLabel;
    DBText3: TDBText;
    AdoQry_MainDeptVendorCode: TStringField;
    AdoQry_Loop: TAdoQuery;
    AdoQry_MainPcNo: TStringField;
    AdoQry_MainPmCode: TIntegerField;
    Label4: TLabel;
    DBText4: TDBText;
    AdoQry_MainEmployeeCode: TStringField;
    AdoQry_MainEmployeeName: TStringField;
    AdoQry_MainEmployeeFlag: TStringField;
    AdoQry_Mainduedate: TDateTimeField;
    AdoQry_MainReMainQty: TFloatField;
    Label6: TLabel;
    Lab_RunMrpDate: TLabel;
    AdoQry_MainReMainQty2: TFloatField;
    AdoQry_MainQclt: TIntegerField;
    btn_Order: TButton;
    btn_ite_Item: TButton;
    btn_Vendor: TButton;
    btn_Item: TButton;
    AdoQry_MainSSQty2: TFloatField;
    btn_uPMrp: TButton;
    btn_OblongMrp: TButton;
    AdoQry_Mainpoinqty: TFloatField;
    Button1: TButton;
    AdoQry_MainDeptVendorCode2: TStringField;
    Label5: TLabel;
    DBText5: TDBText;
    Label7: TLabel;
    Button3: TButton;
    procedure FormDestroy(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure Act_CheckExecute(Sender: TObject);
    procedure AdoQry_MainBeforeInsert(DataSet: TDataSet);
    procedure AdoQry_MainBeforeEdit(DataSet: TDataSet);
    procedure AdoQry_MainAfterPost(DataSet: TDataSet);
    procedure Act_LookExecute(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure btn_ItemClick(Sender: TObject);
    procedure btn_VendorClick(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 DataSourceDataChange(Sender: TObject; Field: TField);
    procedure Button3Click(Sender: TObject);
    procedure DBGridEhGetCellParams(Sender: TObject; Column: TColumnEh;
      AFont: TFont; var Background: TColor; State: TGridDrawState);
  private
 //  Flag:Integer;
    Checked:Boolean;
    PcNo:String;
    PmCode:Integer;
    Selected:Integer;//选定个数
    function ismorethanzero(fieldName:string;tableName:string;conditionfield:string):boolean;

    { Private declarations }
  public
    untionfalg:integer;
    frentform,falg:integer;
    procedure InitForm(AdOConnection:TAdOConnection;ReadOnly:Boolean);Override;
    procedure Initselect;
    procedure GetData;Override;
    function getQclt(ItemCode:string):string;{ Public declarations }
    { Public declarations }
  end;

var
  Frm_Pm_Qry_NormalSsPoInfo: TFrm_Pm_Qry_NormalSsPoInfo;
  ItemCode:string;

implementation
uses Sys_Global, Public_Show, Pm_Enter_PoSsParent, Mrp_Qry_UpRightMrp_D_Item,
  Mrp_Qry_InformalTotalMrp_vd, Mrp_Qry_InformalTotalMrp_Item,
  Mrp_Qry_InformalUpRightMrp_D_ParentMo, Mrp_Qry_InformalTotalMrp_Mo,
  Mrp_Qry_InformalTotalMrp_Po, Mrp_Qry_UpRightMrp_D, Mrp_Qry_OblongMrp_D,
  Mrp_Enter_MoSsParent, Bas_Qry_MultiBomSViewMaster;
{$R *.DFM}
procedure TFrm_Pm_Qry_NormalSsPoInfo.InitForm(AdOConnection:TAdOConnection;ReadOnly:Boolean);
var
  SqlText:string;
  AdOCmd_Mrp:TAdOCommand;
begin
  Application.ProcessMessages;
  inherited;
  AdOCmd_Mrp:=TAdOCommand.Create(Nil);
  AdOCmd_Mrp.CommandTimeout:=0;
  AdOCmd_Mrp.Connection:=DBConnect;
  SqlText:=' select ssid,ss.ItemCode,ssSysInfoflag, '
          +'       Orderno,Orderlineno,ssdate, '
          +'        PmType,             '
          +'        sScheck,            '
          +'        OrderLineStatus,    '
          +'        DeptVendorCode=(select  VendorCode from pc with(index(Pk_Pc))  where pcno= Case When ((Select Count(distinct PcLine.PcNo) As ICount From PcLine '
                +' Where Ss.ItemCode=PcLine.ItemCode '
                +' And Ss.SsDate>=PcStArtDate And Ss.SsDate<=PcendDate '
                +' And ((Ss.SsQty>=PcStArtQty And Ss.SsQty<PcendQty) Or '
                +' (PcStArtQty=0 And PcendQty=0))'
                +' And PcLine.PcLineStatus=1)>=1) And (Ss.SsSysInfoFlag=1) And (Ss.DeptVendorCode is Not Null) Then '
                +' (Select Top 1 PcLine.PcNo From PcLine with(index(Pk_PcLine)),Pc with(index(Pk_Pc)) '
                +' Where PcLine.PcNo=Pc.PcNo '
                +' And Ss.ItemCode=PcLine.ItemCode '
                +' And Ss.SsDate>=PcStArtDate And Ss.SsDate<=PcendDate '
                +' And ((Ss.SsQty>=PcStArtQty And Ss.SsQty<PcendQty) '
                +' Or (PcStArtQty=0 And PcendQty=0))'
                +' And (PcLine.PcLineStatus=1) And Pc.VendorCode=Ss.DeptVendorCode) '

                +' When ((Select Count(distinct PcLine.PcNo) As ICount From PcLine '
                +' Where Ss.ItemCode=PcLine.ItemCode '
                +' And Ss.SsDate>=PcStArtDate And Ss.SsDate<=PcendDate '
                +' And ((Ss.SsQty>=PcStArtQty And Ss.SsQty<PcendQty) Or '
                +' (PcStArtQty=0 And PcendQty=0))'
                +' And PcLine.PcLineStatus=1)=1) And (Ss.SsSysInfoFlag=1) And (Ss.DeptVendorCode is Null) Then '
                +' (Select PcLine.PcNo From PcLine '
                +' Where Ss.ItemCode=PcLine.ItemCode '
                +' And Ss.SsDate>=PcStArtDate And Ss.SsDate<=PcendDate '
                +' And ((Ss.SsQty>=PcStArtQty And Ss.SsQty<PcendQty) '
                +' Or (PcStArtQty=0 And PcendQty=0))'
                +' And (PcLine.PcLineStatus=1)) '

                +' When  ((Select Count(PcLine.PcNo) As ICount From PcLine '
                +' Where Ss.ItemCode=PcLine.ItemCode '
                +' And Ss.SsDate >=PcStArtDate And Ss.SsDate< PcendDate '
                +' And ((Ss.SsQty>=PcStArtQty And Ss.SsQty< PcendQty ) Or '
                +' (PcStArtQty=0 And PcendQty=0))'
                +' And PcLine.PcLineStatus=1)>1) '
                +' And (Ss.DeptVendorCode is Null) '
                +' And (Ss.SsSysInfoFlag=1) '
                +' Then '
                +''''+'待分配'+''''
                +' Else '
                +' Null '
                +' end '
            +'),     '
          +'        duedate,            '
          +'        ssqty,              '
          +'        GrossRequireQty,    '
          +'        PlanInvQty,         '
          +'        ReMainQty,          '
          +'   ssreMainqty=reMainqty  into #ss1 from ss with(index(ss_idx_PmType ))  '
          +' where ssSysInfoflag=1 '
          +' and ((PmType=1) or (PmType=2) or (PmType=3)) '
          +' union '
          +' select ssid,ss.ItemCode,ssSysInfoflag, '
          +'       Orderno,Orderlineno,ssdate, '
          +'        PmType,             '
          +'        sScheck,            '
          +'        OrderLineStatus,    '
          +'        DeptVendorCode=(select  VendorCode from pc with(index(Pk_Pc))  where pcno= Case When ((Select Count(distinct PcLine.PcNo) As ICount From PcLine '
                +' Where Ss.ItemCode=PcLine.ItemCode '
                +' And Ss.SsDate>=PcStArtDate And Ss.SsDate<=PcendDate '
                +' And ((Ss.SsQty>=PcStArtQty And Ss.SsQty<PcendQty) Or '
                +' (PcStArtQty=0 And PcendQty=0))'
                +' And PcLine.PcLineStatus=1)>=1) And (Ss.SsSysInfoFlag=1) And (Ss.DeptVendorCode is Not Null) Then '
                +' (Select Top 1 PcLine.PcNo From PcLine with(index(Pk_PcLine)),Pc with(index(Pk_Pc)) '
                +' Where PcLine.PcNo=Pc.PcNo '
                +' And Ss.ItemCode=PcLine.ItemCode '
                +' And Ss.SsDate>=PcStArtDate And Ss.SsDate<=PcendDate '
                +' And ((Ss.SsQty>=PcStArtQty And Ss.SsQty<PcendQty) '
                +' Or (PcStArtQty=0 And PcendQty=0))'
                +' And (PcLine.PcLineStatus=1) And Pc.VendorCode=Ss.DeptVendorCode) '

                +' When ((Select Count(distinct PcLine.PcNo) As ICount From PcLine '
                +' Where Ss.ItemCode=PcLine.ItemCode '
                +' And Ss.SsDate>=PcStArtDate And Ss.SsDate<=PcendDate '
                +' And ((Ss.SsQty>=PcStArtQty And Ss.SsQty<PcendQty) Or '
                +' (PcStArtQty=0 And PcendQty=0))'
                +' And PcLine.PcLineStatus=1)=1) And (Ss.SsSysInfoFlag=1) And (Ss.DeptVendorCode is Null) Then '
                +' (Select PcLine.PcNo From PcLine '
                +' Where Ss.ItemCode=PcLine.ItemCode '
                +' And Ss.SsDate>=PcStArtDate And Ss.SsDate<=PcendDate '
                +' And ((Ss.SsQty>=PcStArtQty And Ss.SsQty<PcendQty) '
                +' Or (PcStArtQty=0 And PcendQty=0))'
                +' And (PcLine.PcLineStatus=1)) '

                +' When  ((Select Count(PcLine.PcNo) As ICount From PcLine '
                +' Where Ss.ItemCode=PcLine.ItemCode '
                +' And Ss.SsDate >=PcStArtDate And Ss.SsDate< PcendDate '
                +' And ((Ss.SsQty>=PcStArtQty And Ss.SsQty< PcendQty ) Or '
                +' (PcStArtQty=0 And PcendQty=0))'
                +' And PcLine.PcLineStatus=1)>1) '
                +' And (Ss.DeptVendorCode is Null) '
                +' And (Ss.SsSysInfoFlag=1) '
                +' Then '
                +''''+'替代件待分配'+''''
                +' Else '
                +' Null '
                +' end '
            +'),     '
          +'        duedate,            '
          +'        ssqty,              '
          +'        GrossRequireQty,    '
          +'        PlanInvQty,         '
          +'        ReMainQty,          '
          +'   ssreMainqty=reMainqty   from ss with(index(ss_idx_PmType ))  '
          +' where ssSysInfoflag=1 '
          +' and (PmType=9) '
          +' And ReMainQty>0 '
          +' union '

          +' select  ssid,ss.ItemCode,ssSysInfoflag, '
          +'       Orderno,Orderlineno,ssdate, '
          +'        PmType,             '
          +'        sScheck,            '
          +'        OrderLineStatus,    '
          +'        DeptVendorCode,     '
          +'        duedate,            '
          +'        ssqty,  '
          +'        GrossRequireQty,    '
          +'        PlanInvQty,         '
          +'        ReMainQty,          '
          +' ssreMainqty=0 from ss with(index(ss_idx_PmType )) '
          +' where ssSysInfoflag=3 '
          +' and Orderlinestatus<>7 '
          +' and exists(select pono from  poline  where Orderno=poline.pono) '//with(index(RELATION_1999_FK))

          +' union '
          +' select  ssid,ss.ItemCode,ssSysInfoflag, '
          +'       Orderno,Orderlineno,ssdate, '
          +'        PmType,             '
          +'        sScheck,            '
          +'        OrderLineStatus,    '
          +'        DeptVendorCode,     '
          +'        duedate,            '
          +'        ssqty,  '
          +'        GrossRequireQty,    '
          +'        PlanInvQty,         '
          +'        ReMainQty,          '
          +' ssreMainqty=0 from ss with(index(ss_idx_PmType )) '
          +' where ssSysInfoflag=4 '
          +' and exists(select pono from  poline  where Orderno=poline.pono) '

          +' union '
          +' select ssid,ss.ItemCode,ssSysInfoflag, '
          +'       Orderno,Orderlineno,ssdate, '
          +'        PmType,             '
          +'        sScheck,            '
          +'        OrderLineStatus,    '
          +'        DeptVendorCode,     '
          +'        duedate,            '
          +'        ssqty=case  when ss.reMainqty<poline.poqty then poline.poqty-ss.reMainqty '
          //+'             else ss.ssqty  '
          +'             else 0  '
          + '             end,          '
          +'        GrossRequireQty,    '
          +'        PlanInvQty,         '
          +'        ReMainQty,          '
          +'        ssreMainqty=0       '
          +'  from ss with(index(ss_idx_PmType )),poline with(index(poline_idx_Pono))            '