pm_enter_freedelpo.pas

一个MRPII系统源代码版本

unit Pm_Enter_FreeDelPo;

Interface

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

Type
  TFrm_Pm_Enter_FreeDelPo = Class(TFrm_Base_Check)
    AdoQry_HeadPONO: TStringField;
    AdoQry_HeadPOLINENo: TIntegerField;
    AdoQry_HeadItemCode: TStringField;
    AdoQry_HeadPOLineDATE: TDateTimeField;
    AdoQry_HeadPOREFERENCEDPRICE: TIntegerField;
    AdoQry_HeadPOLINESTATUS: TIntegerField;
    AdoQry_HeadPONoTaxAmount: TFloatField;
    AdoQry_HeadPoTaxAmount: TFloatField;
    AdoQry_HeadPOStArtWorkDate: TDateTimeField;
    AdoQry_HeadCloseRemArk: TStringField;
    AdoQry_Headpolinecheck: TIntegerField;
    AdoQry_HeadPOQty: TFloatField;
    AdoQry_HeadPONoFinishQty: TFloatField;
    AdoQry_HeadPORealInQty: TFloatField;
    AdoQry_HeadPOInQty: TFloatField;
    AdoQry_HeadSSQty: TFloatField;
    AdoQry_HeadPOTaxPrice: TFloatField;
    AdoQry_HeadPONoTaxPrice: TFloatField;
    AdoQry_HeadRealPOTaxPrice: TFloatField;
    AdoQry_HeadRealPOTaxAmount: TFloatField;
    AdoQry_HeadRealPONoTaxPrice: TFloatField;
    AdoQry_HeadRealPONoTaxAmount: TFloatField;
    AdoQry_HeadVendorCode: TStringField;
    AdoQry_HeadPOType: TIntegerField;
    AdoQry_HeadPODate: TDateTimeField;
    AdoQry_HeadPOTAXRATE_PERCENT: TIntegerField;
    AdoQry_HeadCURRENCYCode: TStringField;
    AdoQry_HeadEmployeeCode: TStringField;
    AdoQry_HeadPORemArk: TStringField;
    AdoQry_HeadPoSpecial: TIntegerField;
    AdoQry_HeadPcNo: TStringField;
    AdoQry_HeadPOTmpFlag: TIntegerField;
    AdoQry_HeadCreateEmpolyeeCode: TStringField;
    AdoQry_HeadVendorName: TStringField;
    AdoQry_HeadItemName: TStringField;
    AdoQry_HeadPmCode: TIntegerField;
    AdoQry_HeadQclt: TIntegerField;
    AdoQry_HeadEmployeeName: TStringField;
    AdoQry_HeadUomName: TStringField;
    AdoQry_HeadEmployeeFlag: TStringField;
    AdoQry_HeadItemFlag: TStringField;
    AdoQry_HeadVendorFlag: TStringField;
    AdoQry_HeadReferenceNo: TStringField;
    procedure DBGridEhTitleClick(Column: TColumnEh);
    procedure Act_DeleteExecute(Sender: TObject);
  private
    Flag:Boolean;
    Function SavePoLineHistory:Boolean;//保存历史记录
    procedure DeleteRecord;
    { Private declarations }
  public
    Edt_VendorCode,Edt_End_VendorCode ,Edt_PoNo ,Edt_End_PoNo,MEdt_Month,Medt_End_Month:String;
    procedure InitForm(AdOConnection:TAdOConnection;ReadOnly:Boolean);Override;
    { Public declarations }
  end;

var
  Frm_Pm_Enter_FreeDelPo: TFrm_Pm_Enter_FreeDelPo;

implementation

uses Pm_Enter_FreeDelPo_C, Sys_Global;

{$R *.DFM}

{ TFrm_Pm_Enter_DelPo }

procedure TFrm_Pm_Enter_FreeDelPo.InitForm(AdOConnection: TAdOConnection;
  ReadOnly: Boolean);
begin
  inherited;
  Frm_Sys_Condition:= TFrm_Pm_Enter_FreeDelPo_C.Create(self);
  Frm_Sys_Condition.ShowModal;
  Frm_Sys_Condition:=nil ;
  SelectFromSql:=' Select Distinct PoLine.*,Po.*,Vendor.VendorName,Item.ItemName,Item.PmCode,Item.QcLt,Employee.EmployeeName,Uom.UomName,'
                +' Employee.EmployeeCode+'''+' '+'''+Employee.EmployeeName As EmployeeFlag,'
                +' Item.ItemCode+'''+' '+'''+Item.ItemName As ItemFlag, '
                +' Vendor.VendorCode+'''+' '+'''+Vendor.VendorName As VendorFlag '
                +' From PoLine '
                +' Join Po On Po.PoNo=PoLine.PoNo '
                +' Join Item On PoLine.ItemCode=Item.ItemCode '
                +' Left Join Vendor On Po.VendorCode=Vendor.VendorCode '
                +' Left Join Employee On Employee.EmployeeCode=Po.EmployeeCode '
                +' Join Uom On Uom.UomCode=Item.UomCode ' ;
  Condition :='  Po.VendorCode Between '+''''+Trim(Edt_VendorCode)+''''
             +' And '+''''+Trim(Edt_End_VendorCode)+''''
             +' And '+'  Po.PoNo Between '+''''+Trim(Edt_PoNo)+''''
             +' And '+''''+Trim(Edt_End_PoNo)+''''
             +' And Po.PoDate Between '+''''+MEdt_Month+''''
             +' And '+''''+Medt_End_Month+'''';
  ConditionUserDefine:=' PoLine.PoLinestatus='''+'8'+''' And  Po.PoNo Not In(Select PoNo From Po Where PoSpecial=0) ';
  OrderByFields:='EmployeeCode,PoNo';
  GetData;
  Application.ProcessMessages;
  Flag:=False;
  DBGridEhTitleClick(DBGridEh.Columns.Items[0]);
end;

procedure TFrm_Pm_Enter_FreeDelPo.DBGridEhTitleClick(Column: TColumnEh);
var
  BookMArk:String;
begin
  inherited;
  If Trim(Column.Title.Caption)<>'标记' Then
    Abort;
  If AdoQry_Head.RecordCount=0 Then
    Abort;
  BookMArk:=AdoQry_Head.BookmArk;
  If Not Flag Then
  begin
    AdoQry_Head.First;
    While Not AdoQry_Head.Eof Do
    begin
      AdoQry_Head.Edit;
      AdoQry_Head.fieldbyname('PoLineCheck').AsInteger:=1;
      AdoQry_Head.Post;
      AdoQry_Head.Next;
    end;
  end
  Else
  begin
    AdoQry_Head.First;
    While Not AdoQry_Head.Eof Do
    begin
      AdoQry_Head.Edit;
      AdoQry_Head.fieldbyname('PoLineCheck').AsInteger:=0;
      AdoQry_Head.Post;
      AdoQry_Head.Next;
    end;
  end;
  AdoQry_Head.BookmArk:=BookMArk;
  Flag:=Not Flag;
end;

procedure TFrm_Pm_Enter_FreeDelPo.Act_DeleteExecute(Sender: TObject);
begin
  inherited; If (AdoQry_Head.Eof) And (AdoQry_Head.Bof) Then
    Exit;
  If (DispInfo('确认真的要删除选定采购订单吗',2)='y') Then
    DeleteRecord;





 { If AdoQry_Tmp.Eof Then
  begin
    DispInfo('没有适合条件的已结算的采购订单，请重新输入!',3);
    Edt_VendorCode.SetFocus;
    Abort;
  end;
 If DispInfo('符合条件的记录有'+InttoStr(AdoQry_Tmp.RecordCount)+'条，确认真的要清除这些记录吗?',2)='y' Then
 begin
   Ado_Qry:=TAdoQuery.Create(Nil);
   Ado_Qry.Connection:=DbConnect;
   AdoQuery:=TAdoQuery.Create(Nil);
   AdoQuery.Connection:=DbConnect;
   AdoQry_Tmp.First;
   DbConnect.beginTrans;
   Try
     While Not AdoQry_Tmp.Eof Do
     begin
       If Not SavePoLineHistory Then
       begin
         If DbConnect.InTransaction Then
           DbConnect.RollBackTrans;
         DispInfo('保存历史数据失败，数据未删除!',1);
         Abort;
       end;
       SqlText:=' Delete PoLine '
               +' Where PoLine.PoNo='+''''+AdoQry_Tmp.fieldbyname('PoNo').AsString+''''
               +' And PoLine.PoLineStatus='''+'8'+'''';
       Ado_Qry.Close;
       Ado_Qry.SQL.Text:=SqlText;
       Ado_Qry.ExecSQL;
       SqlText:='Select * From Po '
               +' Where PoNo='''+AdoQry_Tmp.fieldbyname('PoNo').AsString+''''
               +' And Po.PoNo Not In (Select PoNo From PoLine)';
       Ado_Qry.Close;
       Ado_Qry.SQL.Text:=SqlText;
       Ado_Qry.Open;        //提前将符合条件的记录选出
       SqlText:=' Delete Po '
            +' Where PoNo='+''''+AdoQry_Tmp.fieldbyname('PoNo').AsString+''''
            +' And Po.PoNo Not In (Select PoNo From PoLine)';
       AdoQuery.Close;
       AdoQuery.SQL.Text:=SqlText;
       AdoQuery.ExecSQL;
       AdoQuery.Close;
       AdoQuery.SQL.Text:='Select @@RowCount As xx From Po';
       AdoQuery.Open;
       If AdoQuery.fieldbyname('Xx').AsInteger>0 Then
       begin
         SqlText:='Insert PoHistory '
                +' (PoNo,VendorCode,PcNO,POType,PODate,'
                +'  POTaxRate_Percent,CurrencyCode,EmployeeCode, PoSpecial,PORemArk,PoChgEmployeeCode,PoChgTime,PoChgType)'
                +'  Values('
                +''''+Ado_Qry.fieldbyname('PoNo').AsString+''','
                +''''+Ado_Qry.fieldbyname('VendorCode').AsString+''','
                +''''+Ado_Qry.fieldbyname('PcNo').AsString+''','
                +''''+IntToStr(Ado_Qry.fieldbyname('POType').AsInteger)+''','
                +''''+DateTimeToStr(Ado_Qry.fieldbyname('PODate').AsDateTime)+''','
                +''''+FloatToStr(Ado_Qry.fieldbyname('POTaxRate_Percent').AsFloat)+''','
                +''''+IntToStr(Ado_Qry.fieldbyname('CurrencyCode').AsInteger)+''','
                +''''+Ado_Qry.fieldbyname('EmployeeCode').AsString+''','
                +''''+IntToStr(Ado_Qry.fieldbyname('PoSpecial').AsInteger)+''','
                +''''+Ado_Qry.fieldbyname('VendorCode').AsString+''','
                +''''+UserCode+''','
                +'GetDate(),'
                +''''+'清除记录'+''')';
         AdoQuery.Close;
         AdoQuery.SQL.Text:=SqlText;
         AdoQuery.ExecSQL;
       end;
       AdoQry_Tmp.Next;
     end;
   DbConnect.CommitTrans;
   DispInfo('数据已清除，要继续清除请重新输入条件，要退出请按"取消"!',3);
   Edt_VendorCode.SetFocus;
   Except
      If DbConnect.InTransaction Then
        DbConnect.RollBackTrans;
     DispInfo('由于数据受到约束，不能清除!',1);
     Edt_VendorCode.SetFocus;
     Abort;
   end;
   Ado_Qry.Free;
 end;}
end;

function TFrm_Pm_Enter_FreeDelPo.SavePoLineHistory: Boolean;
var
  SqlText:String;
begin
  Try
    SqlText:='Insert PoLineHistory '
            +' (PoNo,PoLineNo,ItemCode,POLineDate,PoStArtWorkDate,POQty,POTaxPrice,POTaxAmount,PONoTaxPrice,'
            +' PONoTaxAmount,PONoFinishQty,POReferencedPrice,POLineStatus,PolChgEmployeeCode,PolChgTime,PolChgType)'
            +'Values('
            +''''+AdoQry_Head.fieldbyname('PoNo').AsString+''''+','
            +''''+IntToStr(AdoQry_Head.fieldbyname('PoLineNo').AsInteger)+''''+','
            +''''+AdoQry_Head.fieldbyname('ItemCode').AsString+''''+','
            +''''+DatetimeToStr(AdoQry_Head.fieldbyname('PoLineDate').AsDateTime)+''''+','
            +''''+DatetimeToStr(AdoQry_Head.fieldbyname('PoStArtWorkDate').AsDateTime)+''''+','
            +''''+FloatToStr(AdoQry_Head.fieldbyname('PoQty').AsFloat)+''''+','
            +''''+FloatToStr(AdoQry_Head.fieldbyname('POTaxPrice').AsFloat)+''''+','
            +''''+FloatToStr(AdoQry_Head.fieldbyname('POTaxAmount').AsFloat)+''''+','
            +''''+FloatToStr(AdoQry_Head.fieldbyname('PONoTaxPrice').AsFloat)+''''+','
            +''''+FloatToStr(AdoQry_Head.fieldbyname('PONoTaxAmount').AsFloat)+''''+','
            +''''+FloatToStr(AdoQry_Head.fieldbyname('PONoFinishQty').AsFloat)+''''+','
            +''''+IntToStr(AdoQry_Head.fieldbyname('POReferencedPrice').AsInteger)+''''+','
            +''''+IntToStr(AdoQry_Head.fieldbyname('POLineStatus').AsInteger)+''''+','
            +''''+UserCode+''''+','
            +'GetDate(),'
            +''''+'清除记录'+''''+')';
     AdoQry_Tmp.Close;
     AdoQry_Tmp.SQL.Text:=SqlText;
     AdoQry_Tmp.ExecSQL;
     Result:=True;
  Except
    Result:=False;
  end;
end;

procedure TFrm_Pm_Enter_FreeDelPo.DeleteRecord;
var
  SqlText,SqlText1,SqlText2,sqlTxt3,BookMArk: String;
  falg:integer;
  Ado_Qry:TAdoQuery;
begin
  faLg:=0;
  Ado_Qry:=TAdoQuery.Create(self);
  Ado_Qry.Connection :=Dbconnect;
  if AdoQry_Head.RecordCount>AdoQry_Head.RecNo   then
  begin
    BookMArk:=AdoQry_Head.BookmArk;
    falg:=1;
  end;
  AdoQry_Head.First;
  While Not AdoQry_Head.Eof Do
  begin
    If (AdoQry_Head.fieldbyname('PoLineCheck').AsInteger=1)  then  //and (AdoQry_Head.fieldbyname('PoLineStatus').AsInteger=5)
    begin
      sqlTxt3:=' Select * from poline where pono='''+AdoQry_Head.fieldbyname('PONO').AsString+''''
             +' and polineno ='''+AdoQry_Head.fieldbyname('POlineno').AsString+''''
             +' and PoLineStatus=8 ' ;
      AdoQry_Tmp.Close;
      AdoQry_Tmp.SQL.Text :=sqlTxt3 ;
      AdoQry_Tmp.Open;
      if  AdoQry_Tmp.fieldbyname('PoLineStatus').asinteger= 8 then
      begin
        try
          DbConnect.beginTrans ;
          If Not SavePoLineHistory Then
          begin
            If DbConnect.InTransaction Then
              DbConnect.RollBackTrans;
              DispInfo('保存历史数据失败，数据未删除!',1);
              Abort;
           end;

           SqlText:=' Delete PoLine Where PoNo='''+AdoQry_Head.fieldbyname('PoNo').AsSTring+''''
                  +' And PoLineNo='''+AdoQry_Head.fieldbyname('PoLineNo').AsSTring+''''
                  +' And PoLineStatus = 8';
          AdoQry_Tmp.Close;
          AdoQry_Tmp.SQL.Text := SqlText;
          AdoQry_Tmp.ExecSQL;

          SqlText:='Select * From Po '
                   +' Where PoNo='''+AdoQry_Head.fieldbyname('PoNo').AsString+''''
                   +' And Po.PoNo Not In (Select PoNo From PoLine)';
          Ado_Qry.Close;
          Ado_Qry.SQL.Text:=SqlText;
          Ado_Qry.Open;        //提前将符合条件的记录选出
          if  not Ado_Qry.Eof then
          begin
            SqlText:='Insert PoHistory '
                    +' (PoNo,VendorCode,PcNO,POType,PODate,'
                    +'  POTaxRate_Percent,CurrencyCode,EmployeeCode, PoSpecial,PORemArk,PoChgEmployeeCode,PoChgTime,PoChgType)'
                    +'  Values('
                    +''''+Ado_Qry.fieldbyname('PoNo').AsString+''','
                    +''''+Ado_Qry.fieldbyname('VendorCode').AsString+''','
                    +''''+Ado_Qry.fieldbyname('PcNo').AsString+''','
                    +''''+IntToStr(Ado_Qry.fieldbyname('POType').AsInteger)+''','
                    +''''+DateTimeToStr(Ado_Qry.fieldbyname('PODate').AsDateTime)+''','
                    +''''+FloatToStr(Ado_Qry.fieldbyname('POTaxRate_Percent').AsFloat)+''','
                    +''''+IntToStr(Ado_Qry.fieldbyname('CurrencyCode').AsInteger)+''','
                    +''''+Ado_Qry.fieldbyname('EmployeeCode').AsString+''','
                    +''''+IntToStr(Ado_Qry.fieldbyname('PoSpecial').AsInteger)+''','
                    +''''+Ado_Qry.fieldbyname('VendorCode').AsString+''','
                    +''''+UserCode+''','
                    +'GetDate(),'
                    +''''+'清除记录'+''')';
             AdoQry_Tmp.Close;
             AdoQry_Tmp.SQL.Text:=SqlText;
             AdoQry_Tmp.ExecSQL;

            SqlText1:=' Delete Po Where PoNo='''+AdoQry_Head.fieldbyname('PoNo').AsSTring+''''
                     +' and  Po.PoNo Not in (Select PoNo From PoLine )';
            AdoQry_Tmp.Close;
            AdoQry_Tmp.SQL.Text := SqlText1;
            AdoQry_Tmp.ExecSQL;

          end;
         DbConnect.CommitTrans ;
     except
       DbConnect.RollBackTrans;
     end;
    end;
    end;
    AdoQry_Head.Next;
  end;
  getdata;
  if falg=1 then
   AdoQry_Head.BookmArk:=BookMArk;
end;

end.