pm_enter_newsspoinfo3.pas

一个MRPII系统源代码版本

unit Pm_Enter_NewSsPoInfo3;
 
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_Enter_NewSsPoInfo3 = 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_MainItemName: TStringField;
    AdoQry_MainUomCode: TStringField;
    AdoQry_MainUomName: TStringField;
    AdoQry_MaInOrderLineStatus: TIntegerField;
    AdoQry_MainItemflag: TStringField;
    AdoQry_MainSsName: TStringField;
    DBText2: TDBText;
    Label2: TLabel;
    Label3: TLabel;
    DBText3: TDBText;
    AdoQry_MainDeptVendorCode: TStringField;
    AdoQry_Loop: TAdoQuery;
    AdoQry_MainPcNo: TStringField;
    AdoQry_MainPmCode: TIntegerField;
    AdoQry_MaInvendorCode: TStringField;
    AdoQry_MaInvendorName: TStringField;
    AdoQry_MaInvendorFlag: TStringField;
    AdoQry_MainEmployeeCode: TStringField;
    AdoQry_MainEmployeeName: TStringField;
    AdoQry_MainEmployeeFlag: TStringField;
    AdoQry_MainDueDate: TDateTimeField;
    AdoQry_MainReMainQty: TFloatField;
    Label6: TLabel;
    Lab_RunMrpDate: TLabel;
    AdoQry_MainQclt: TIntegerField;
    AdoQry_MainReMainQty2: TFloatField;
    btn_Item: TButton;
    btn_Vendor: TButton;
    btn_ite_Item: TButton;
    btn_Order: TButton;
    Button1: TButton;
    Label4: TLabel;
    DBText4: TDBText;
    Label1: TLabel;
    DBText1: TDBText;
    Label5: TLabel;
    DBText5: TDBText;
    Button4: TButton;
    AdoQry_MainSSDate2: TDateTimeField;
    Button2: TButton;
    procedure FormDestroy(Sender: TObject);
    procedure Act_NewExecute(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure Act_CheckExecute(Sender: TObject);
    procedure AdoQry_MainBeforeInsert(DataSet: TDataSet);
    procedure Act_DeleteExecute(Sender: TObject);
    procedure AdoQry_MainBeforeEdit(DataSet: TDataSet);
    procedure AdoQry_MainAfterPost(DataSet: TDataSet);
    procedure Act_autoExecute(Sender: TObject);
    procedure DBGridEhTitleClick(Column: TColumnEh);
    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 Button1Click(Sender: TObject);
    procedure Button4Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure FormKeyDown(Sender: TObject; var Key: Word;
      Shift: TShiftState);
  private
    Flag:Integer;
    Checked:Boolean;
    PcNo:String;
    PmCode:Integer;
    Selected:Integer;//选定条数
    function ismorethanzero(fieldName:string;tableName:string;conditionfield:string):boolean;
    { Private declarations }
  public
    frentform,falg:integer;
    procedure InitForm(AdOConnection:TAdOConnection;ReadOnly:Boolean);Override;
    procedure Initselect;
    function getQclt(ItemCode:string):string;{ Public declarations }
    { Public declarations }
  end;

var
  Frm_Pm_Enter_NewSsPoInfo3: TFrm_Pm_Enter_NewSsPoInfo3;

implementation
uses Sys_Global, Pm_Enter_AutoPo, Public_Show, Pm_Enter_NewSsPoInfo,
  Pm_Enter_PoSsParent, Pm_Enter_SsPoInfo2, Mrp_Qry_UpRightMrp_D_Item,
  Mrp_Qry_InformalTotalMrp_vd, Mrp_Qry_InformalTotalMrp_Item,
  Mrp_Qry_InformalTotalMrp_Po, Mrp_Qry_InformalTotalMrp_Mo,
  Mrp_Enter_MoSsParent, Pm_Enter_NewAutoPo,
  Mrp_Enter_CommonAssignAlterNative, Mrp_Enter_NewMoSsParent,
  Mrp_Enter_CommonAssignAlterNative1,Pm_Enter_VendorItemAssign;
{$R *.DFM}
Function VendorAssign(AdoConnect:TAdoConnection;SsId,ItemCode,PmType,GrossRequireQty,
       OrderLineStatus,SsDate,SsQty,ReMainQty,DueDate:String):Boolean;//按供应商比例分配
var
  SqlText:String;
  AdoQry_Tmp,AdoQuery:TAdoQuery;
  Rate:Double;
begin
  AdoQry_Tmp:=TAdoQuery.Create(Nil);
  AdoQry_Tmp.EnableBCD := False;
  AdoQuery:=TAdoQuery.Create(Nil);
  AdoQuery.EnableBCD := False;
  AdoQry_Tmp.Connection:=AdoConnect;
  AdoQuery.Connection:=AdoConnect;
  SqlText:=' Select Pc.VendorCode,Pc.PcNo,PcLine.PcTaxPrice,PcLine.PcNoTaxPrice,PcLine.PcReferencedPrice '
          +' Into #TmpPc From PcLine Join Pc On Pc.PcNo=PcLine.PcNo'
          +' Where ('''+SsDate +'''>=PcLine.PcStArtDate And '''+SsDate +'''<PcLine.PcendDate ) '
          +' And (('''+SsQty+''' >=PcLine.PcStArtQty And '''+SsQty+'''<PcLine.PcendQty) Or '
          +' (PcLine.PcStArtQty=0 And PcLine.PcendQty=0))'
          +' And PcLine.ItemCode='''+ItemCode+''''
          +' And PcLine.PcLineStatus=1';
  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:=SqlText;
  AdoQry_Tmp.ExecSQL;   //找出所有包含该物料的采购合号
  SqlText:=' Select * '
          +' Into #TmpVendor From VendorItemAssign '
          +' Where ItemCode='''+ItemCode+'''';
  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:=SqlText;
  AdoQry_Tmp.ExecSql;     //选出所有该物料的供应商
  SqlText:=' Select Sum(AssignRATE) As AssignRate From #TmpVendor '
          +' Where #TmpVendor.VendorCode Not In (Select VendorCode From #TmpPc)';
  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:=SqlText;
  AdoQry_Tmp.Open;
  AdoConnect.beginTrans;
  Try
    If AdoQry_Tmp.fieldbyname('AssignRate').AsFloat=0 Then
    begin
      SqlText:=' Select * From #TmpVendor ';
      AdoQry_Tmp.Close;
      AdoQry_Tmp.SQL.Text:=SQLText;
      AdoQry_Tmp.Open;
      If AdoQry_Tmp.RecordCount<=1 Then //在VendorItemAssign表中找不能该物料对应的供应商
        Result:=False
      Else
      begin
        While Not AdoQry_Tmp.Eof  Do
        begin
          SqlText:=' Insert MrpResult (ItemCode, DeptVendorCode,ordinal,releasedate,onhand, '
                  +' PmCode,SScheck,GrossQty,OrderLineStatus,ReMainQty,DueDate) '
                  +' Values ('
                  +''''+ ItemCode+''','
                  +''''+ AdoQry_Tmp.fieldbyname('VendorCode').AsString+''','
                  +''''+ inttostr(3)+''','
                  +''''+ SsDate +''','
                  +''''+FloatToStr(StrToFloat(SSQty)*AdoQry_Tmp.fieldbyname('AssignRATE').AsFloat/100)+''','
                  +''''+PmType+''','
                  +'0,'
                  +''''+GrossRequireQty+''','
                  +''''+OrderLineStatus+''','
                  +''''+FloatToStr(StrToFloat(ReMainQty)*AdoQry_Tmp.fieldbyname('AssignRATE').AsFloat/100)+''','
                  +''''+DueDate+''')';
          AdoQuery.Close;
          AdoQuery.SQL.Text:=SqlText;
          AdoQuery.ExecSQL;
          AdoQry_Tmp.Next;
        end;
        SqlText:=' Delete MrpResult '
            +' Where MrpResultId='''+SsId+'''';
        AdoQuery.Close;
        AdoQuery.SQL.Text:=SqlText;
        AdoQuery.ExecSQL; //删除当前行并刷新
        SqlText:=' Delete #Ss '
            +' Where SsId='''+SsId+'''';
        AdoQuery.Close;
        AdoQuery.SQL.Text:=SqlText;
        AdoQuery.ExecSQL; //删除当前行并刷新
        Result:=True;
      end; //end IF
    end
    Else
    begin
      Rate:=AdoQry_Tmp.fieldbyname('AssignRate').AsFloat;
      SqlText:=' Select #TmpPc.VendorCode,#TmpVendor.AssignRate From #TmpPc,#tmpVendor '
              +' Where #TmpPc.VendorCode=#TmpVendor.VendorCode Group By #TmpPc.VendorCode,#TmpVendor.AssignRate';
      AdoQry_Tmp.Close;
      AdoQry_Tmp.SQL.Text:=SQLText;
      AdoQry_Tmp.Open;
      If AdoQry_Tmp.RecordCount<=1 Then
        Result:=False
      Else
      begin
        While Not AdoQry_Tmp.Eof Do
        begin
          SqlText:=' Insert MrpResult (ItemCode, DeptVendorCode,ordinal,releasedate,onhand, '
                  +' PmCode,SScheck,GrossQty,OrderLineStatus,ReMainQty,DueDate) '
                  +' Values ('
                  +''''+ ItemCode+''','
                  +''''+ AdoQry_Tmp.fieldbyname('VendorCode').AsString+''','
                  +''''+ inttostr(3)+''','
                  +''''+ SsDate +''','
                  +''''+FloatToStr(StrToFloat(SSQty)*(AdoQry_Tmp.fieldbyname('AssignRATE').AsFloat/(100-Rate)))+''','
                  +''''+PmType+''','
                  +'0,'
                  +''''+GrossRequireQty+''','
                  +''''+OrderLineStatus+''','
                  +''''+FloatToStr(StrToFloat(ReMainQty)*(AdoQry_Tmp.fieldbyname('AssignRATE').AsFloat/(100-Rate)))+''','
                  +''''+DueDate+''')';
          AdoQuery.Close;
          AdoQuery.SQL.Text:=SqlText;
          AdoQuery.ExecSQL;
          AdoQry_Tmp.Next;
        end;
        SqlText:=' Delete MrpResult'
            +' Where MrpResultId='''+SsId+'''';
        AdoQuery.Close;
        AdoQuery.SQL.Text:=SqlText;
        AdoQuery.ExecSQL; //删除当前行并刷新
        SqlText:=' Delete #Ss '
            +' Where SsId='''+SsId+'''';
        AdoQuery.Close;
        AdoQuery.SQL.Text:=SqlText;
        AdoQuery.ExecSQL; //删除当前行并刷新
        Result:=True;
      end;
    end;  //分配完毕
  AdoConnect.CommitTrans;
  Except
    AdoConnect.RollBackTrans;
    Result:=False;
  end;
  Try
    AdoQry_Tmp.Close;
    AdoQry_Tmp.SQL.Text:='Drop Table #TmpPc ';
    AdoQry_Tmp.ExecSQL;
  Except
  end;
  Try
    AdoQry_Tmp.Close;
    AdoQry_Tmp.SQL.Text:='Drop Table #TmpVendor ';
    AdoQry_Tmp.ExecSQL;
  Except
  end;    //后期处理
end;


procedure TFrm_Pm_Enter_NewSsPoInfo3.InitForm(AdOConnection:TAdOConnection;ReadOnly:Boolean);
var
  SqlText:string;
  AdOCmd_Mrp:TAdOcommand;
begin
  Application.ProcessMessages;
  inherited;
  CreatePanel(1,DBGridEh);
  AdOCmd_Mrp:=TAdOcommand.Create(nil);
  AdOCmd_Mrp.CommandTimeout:=0;
  AdOCmd_Mrp.Connection :=DbConnect;
  SqlText:=' select MrpResult.*, '
          +' DeptVendorCode1=(select  VendorCode from pc with(index(Pk_Pc))  where pcno= Case When ((Select Count(distinct PcLine.PcNo) As ICount From PcLine '
          +' Where MrpResult.ItemCode=PcLine.ItemCode '
          +' And MrpResult.ReleaseDate>=PcStArtDate And MrpResult.ReleaseDate<=PcendDate '
          +' And ((MrpResult.OnHand>=PcStArtQty And MrpResult.OnHand<PcendQty) Or '
          +' (PcStArtQty=0 And PcendQty=0))'
          +' And PcLine.PcLineStatus=1)>=1) And (MrpResult.Ordinal=3) And (MrpResult.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 MrpResult.ItemCode=PcLine.ItemCode '
          +' And MrpResult.ReleaseDate>=PcStArtDate And MrpResult.ReleaseDate<=PcendDate '
          +' And ((MrpResult.OnHand>=PcStArtQty And MrpResult.OnHand<PcendQty) '
          +' Or (PcStArtQty=0 And PcendQty=0))'
          +' And (PcLine.PcLineStatus=1) And Pc.VendorCode=MrpResult.DeptVendorCode) '