📄 pm_enter_purchaseapplytoorder.pas
字号:
unit Pm_Enter_PurchaseApplyToOrder;
Interface
uses
Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
Base_Outer, Menus, ExtPrintReport, Db, ActnList, AdODB, Grids, DBGridEh,
StdCtrls, ExtCtrls, ComCtrls, ToolWin, jpeg, variants;
Type
TFrm_Pm_Enter_PurchaseApplyToOrder = Class(TFrm_Base_Outer)
AdoQry_MaInPlanQty: TFloatField;
AdoQry_MaInPlanReleaseDate: TDateTimeField;
AdoQry_MainPmCode: TIntegerField;
AdoQry_MainEmployeeCode: TStringField;
AdoQry_MainEmployeeName: TStringField;
AdoQry_MainiScreatePo: TIntegerField;
AdoQry_MainTmpField: TIntegerField;
AdoQry_MainPcNo: TStringField;
AdoQry_MaInvendorCodeO: TStringField;
AdoQry_MaInvendorCode: TStringField;
AdoQry_MainItemCodeO: TStringField;
AdoQry_MainItemCode: TStringField;
AdoQry_MainPcTaxRate_Percent: TIntegerField;
AdoQry_MainCurrencyCode: TStringField;
AdoQry_MainReferencePricePass: TIntegerField;
AdoQry_MainformalPricePass: TIntegerField;
AdoQry_MainSSReleaseDate: TDateTimeField;
AdoQry_MaInPlanDueDate: TDateTimeField;
AdoQry_MainTabFlag: TIntegerField;
AdoQry_MainPoTaxPrice: TFloatField;
AdoQry_MainPoNoTaxPrice: TFloatField;
AdoQry_MainToDate: TStringField;
procedure FormShow(Sender: TObject);
procedure AdoQry_MainCalcFields(DataSet: TDataSet);
procedure Act_autoExecute(Sender: TObject);
procedure FormDestroy(Sender: TObject);
procedure DBGridEhCellClick(Column: TColumnEh);
procedure FormClose(Sender: TObject; var Action: TCloseAction);
procedure DBGridEhTitleClick(Column: TColumnEh);
procedure FormCreate(Sender: TObject);
procedure AdoQry_MainBeforeEdit(DataSet: TDataSet);
procedure DBGridEhGetCellParams(Sender: TObject; Column: TColumnEh;
AFont: TFont; var Background: TColor; State: TGridDrawState);
private
flag,EditFlag:boolean;
RunDate:String;
procedure AutoPrdPo;//自动生成采购单
{ Private declarations }
public
procedure initform(AdOConnection:TAdOConnection;ReadOnly:boolean);
{ Public declarations }
end;
var
Frm_Pm_Enter_PurchaseApplyToOrder: TFrm_Pm_Enter_PurchaseApplyToOrder;
implementation
uses Sys_Global;
{$R *.DFM}
procedure TFrm_Pm_Enter_PurchaseApplyToOrder.initform(AdOConnection:TAdOConnection;ReadOnly:boolean);
var SqlText : String;
begin
inherited;
EditFlag:=False;
AdoQry_Tmp.Close;
AdoQry_Tmp.SQL.Text:='select MrpParamValueC as RunDate'+
' From MrpParam '+
' Where MrpParamCode=''RunMrpDate''';
AdoQry_Tmp.Open;
if not AdoQry_Tmp.isempty then
RunDate:=AdoQry_Tmp.fieldbyname('RunDate').asstring
else
RunDate:='';
ExecuteSql(AdoQry_Tmp,' select pcline.ItemCode,pcline.FormalPricePass,PcLine.ReferencePricePass,pcline.PcendDate,Pcline.PcStArtDate, '
+' pcline.PcTaxPrice as PoTaxPrice,pcline.PcNoTaxPrice as PoNoTaxPrice,pc.* into #PCTmp '
+' From pcline left join pc on pc.pcno=pcline.pcno '
,1);
SqlText :=' select VendorCodeO=PurchaseApply.VendorCode,VendorCode=PurchaseApply.VendorCode+'' ''+Vendor.VendorName,ItemCodeO=PurChaseApply.ItemCode,ItemCode=PurChaseApply.ItemCode+'' ''+Item.ItemName,PurChaseApply.PlanQty,'
+' #PcTmp.PoTaxPrice, #PcTmp.PoNoTaxPrice, '
+' #PCTmp.CurrencyCode,PurChaseApply.SSReleaseDate,PurChaseApply.ToDate,PurChaseApply.PlanReleaseDate,Item.PmCode,Item.EmployeeCode, '
+' EmployeeName=Employee.EmployeeName,PurChaseApply.iScreatePo,PurChaseApply.TmpField,#PCTmp.PcNo,#PCTmp.PcTaxRate_Percent,#PCTmp.ReferencePricePass,#PCTmp.FormalPricePass, '
+' PurChaseApply.PlanDueDate,TabFlag=0 '
+' Into #TmpPurchaseApply '
+' from PurchaseApply '
+' left join #PCTmp on purchaseApply.ItemCode=#PCTmp.ItemCode and purchaseApply.VendorCode=#PCTmp.VendorCode '
// +' left join VendorItemAssign on PurChaseApply.VendorCode=VendorItemAssign.VendorCode and PurChaseApply.ItemCode=VendorItemAssign.ItemCode '
+' left join Vendor on Vendor.VendorCode=PurchaseApply.VendorCode '
+' left join Item on Item.ItemCode=PurchaseApply.ItemCode '
+' left join Employee on Employee.EmployeeCode=Item.EmployeeCode '
+' where PurchaseApply.IsMultiVendor<>1 and PurchaseApply.ISRefill<>1 '
+' and PurchaseApply.IsActive=1 '
+' and PurchaseApply.ToDate='''+RunDate+''''
+' union '
+' select VendorCodeO=PurchaseApplyAssign.VendorCode,VendorCode=PurchaseApplyAssign.VendorCode+'' ''+Vendor.VendorName,ItemCodeO=PurchaseApplyAssign.ItemCode,ItemCode=PurchaseApplyAssign.ItemCode+'' ''+Item.ItemName,PurchaseApplyAssign.PlanQty,'
+' #PcTmp.PoTaxPrice , #PcTmp.PoNoTaxPrice, '
+' #PCTmp.CurrencyCode,PurchaseApplyAssign.SSReleaseDate,PurchaseApplyAssign.ToDate,PurchaseApplyAssign.PlanReleaseDate,Item.PmCode,Item.EmployeeCode, '
+' EmployeeName=Employee.EmployeeName,PurchaseApplyAssign.iScreatePo,PurchaseApplyAssign.TmpField,#PCTmp.PcNo,#PCTmp.PcTaxRate_Percent,#PCTmp.ReferencePricePass,#PCTmp.FormalPricePass,PurchaseApplyAssign.PlanDueDate,TabFlag=1 '
+' from PurchaseApplyAssign '
+' left join #PCTmp on PurchaseApplyAssign.ItemCode=#PCTmp.ItemCode and PurchaseApplyAssign.VendorCode=#PCTmp.VendorCode '
// +' left join VendorItemAssign on PurchaseApplyAssign.VendorCode=VendorItemAssign.VendorCode and PurchaseApplyAssign.ItemCode=VendorItemAssign.ItemCode '
+' left join Vendor on Vendor.VendorCode=PurchaseApplyAssign.VendorCode '
+' left join Item on Item.ItemCode=PurchaseApplyAssign.ItemCode '
+' left join Employee on Employee.EmployeeCode=Item.EmployeeCode'
+' where PurchaseApplyAssign.ToDate='''+RunDate+'''';
//条件
//当前排序字段 +'
ExecuteSql(AdoQry_Tmp,SqlText,1);
SelectFromSql := 'select * from #TmpPurchaseApply';
OrderByFields:='VendorCode,PmCode,EmployeeCode,PlanReleaseDate,ItemCode';
lbl_Order.Caption:='物料代码/制购码/计划约定交货日/物料代码';
GetData;
end;
procedure TFrm_Pm_Enter_PurchaseApplyToOrder.FormShow(Sender: TObject);
begin
inherited;
Pnl_Title.Caption:='生成采购订单';
Tlbtn_New.Action := Act_Auto;
end;
procedure TFrm_Pm_Enter_PurchaseApplyToOrder.AdoQry_MainCalcFields(
DataSet: TDataSet);
begin
inherited;
DataSet.fieldbyname('IDFlag').asinteger:=1;
end;
procedure TFrm_Pm_Enter_PurchaseApplyToOrder.Act_autoExecute(
Sender: TObject);
begin
inherited;
if AdoQry_Main.IsEmpty then
exit;
EditFlag:=True;
If (DispInfo('确认真的要生成采购订单吗',2)='y') Then
AutoPrdPo;
EditFlag:=False;
end;
{生成采购单}
procedure TFrm_Pm_Enter_PurchaseApplyToOrder.AutoPrdPo;
var
VendorCode,EmployeeCode,PcNo,PoNo,AutoPoNo,PoTaxRate_Percent,CurrencyCode:string;
ItemCode,FormalPP,PQtyStr,OldOrderstr:string;
PQty:Double;
PmCode,PoType,iRecord,PRefPrc,i:integer;//i记录数
FirstCheck:boolean; //判别有没有第一次初始化判断变量
SucTrans:boolean;//判断事务执行是否成功
MAdOpen:Boolean; //判断AdoQry_Main是否被重新打开
begin
MAdOpen:=False;
VendorCode:='';
EmployeeCode:='';
PQty:=0;
iRecord:=0;
FirstCheck:=False;
{临时表保存生成的采购单数据，以判断是否该生成新的采购单}
try
AdoQry_Tmp.Close;
AdoQry_Tmp.Sql.Text:='create Table #TmpPo123456'+
' (PoNo varchAr(16),'+
'VendorCode varchAr(12),'+
'PmCode int,'+
'EmployeeCode varchAr(30),'+
'ID int)';
AdoQry_Tmp.execSql;
except
end;
with AdoQry_Main do
begin
First;
while not eof do
begin
try
AdoQry_Tmp.Connection.beginTrans;
SucTrans:=True;
{第一次初始化判断变量和主表}
if (not FirstCheck)and(fieldbyname('TmpField').asinteger=1) then
begin
PmCode:=fieldbyname('PmCode').asinteger;
PmCode:=fieldbyname('PmCode').asinteger;
PcNo:=Trim(fieldbyname('PcNo').asstring);
{插入第一条笔采购单表头}
AutoPoNo:='P'+Trim(Copy(FormatDateTime('yyyy.mm.dd',Now),3,2))+Trim(Copy(FormatDateTime('yyyy.mm.dd',Now),6,2));
PoNo:=GetNo(DBConnect,AutoPoNo,'PO');
if PmCode=2 then
PoType:=1
else
PoType:=0;
{生成采购单}
AdoQry_Tmp.Close;
AdoQry_Tmp.Sql.Text:='insert Po('+
'PoNo,'+
'VendorCode,'+
'PoType,'+
'Podate,'+
'PoTaxRate_Percent,'+
'CurrencyCode,'+
'EmployeeCode,'+
'PoRemArk,'+
'PcNo,'+
'CreateEmpolyeeCode'+
')'+
'Values('+
''''+PoNo+''','+
''''+fieldbyname('VendorCodeO').asstring+''','+
inttostr(PoType)+','+
'Convert(varchAr,GetDate(),102),'+
fieldbyname('PcTaxRate_Percent').asstring+','+
''''+fieldbyname('CurrencyCode').asstring+''','+
''''+fieldbyname('EmployeeCode').asstring+''','+
''''','+
''''+PcNo+''','+
''''+UserCode+''')';
AdoQry_Tmp.execsql;
AdoQry_Tmp.Close;
{临时表中保存生成采购单条件}
AdoQry_Tmp.SQL.Text:='insert #TmpPo123456 ('+
'Pono,'+
'VendorCode,'+
'PmCode,'+
'EmployeeCode,'+
'ID)'+
'Values('+
''''+PoNo+''','+
''''+fieldbyname('VendorCodeO').asstring+''','+
fieldbyname('PmCode').asstring+','+
''''+fieldbyname('EmployeeCode').asstring+''','+
'0)';
AdoQry_Tmp.ExecSQL;
FirstCheck:=True;
end;
{表PoLine插入相应数据}
if fieldbyname('TmpField').asinteger=1 then
begin
{判断是否生成了采购单}
AdoQry_Tmp.Close;
AdoQry_Tmp.SQL.Text:='select * from #TmpPo123456'+
' where VendorCode='''+fieldbyname('VendorCodeO').asstring+''''+
' and PmCode='+fieldbyname('PmCode').asstring+
' and EmployeeCode='''+fieldbyname('EmployeeCode').asstring+''''+
' Order by ID';
AdoQry_Tmp.Open;
AdoQry_Tmp.Last;
iRecord:=AdoQry_Tmp.fieldbyname('ID').asinteger;
if not AdoQry_Tmp.IsEmpty then
begin
PoNo:=AdoQry_Tmp.fieldbyname('PoNo').asstring;
if fieldbyname('FormalPricePass').asstring='1' then
PRefPrc:=0
else
PRefPrc:=1;
iRecord:=iRecord+1;
PQty:=fieldbyname('PlanQty').asfloat;
if PQty=null then
PQtyStr:='Null'
else
PQtyStr:=floattostr(PQty);
AdoQry_Tmp.Close;
AdoQry_Tmp.Sql.Text:='Insert PoLine('+
'PoNo,'+
'PoLineNo,'+
'ItemCode,'+
'PoLineDate,'+
'PoReferencedPrice,'+
'PoLineStatus,'+
'PoNoTaxPrice, '+
'PoTaxPrice, ' +
'PoNoTaxAmount, '+
'PoTaxAmount , '+
'PoQty,'+
'PoNoFinishQty,'+
'POStArtWorkDate)'+
'Values('+
''''+PoNo+''','+
inttostr(iRecord)+','+
''''+fieldbyname('ItemCodeO').asstring+''','+
QuotedStr(fieldbyname('PlanReleaseDate').AsString)+','+
inttostr(PRefPrc)+','+
'5,'+
fieldbyname('PoNoTaxPrice').AsString+','
+fieldbyname('PoTaxPrice').AsString+','
+fieldbyname('PoNoTaxPrice').AsString+'*'+PQtyStr+','
+fieldbyname('PoTaxPrice').AsString+'*'+PQtyStr+','+
PQtyStr+','+
PQtyStr+','+
''''+fieldbyname('PlanDueDate').asstring+''')';
AdoQry_Tmp.ExecSQL;
AdoQry_Tmp.Close;
AdoQry_Tmp.SQL.Text:='insert #TmpPo123456('+
'Pono,'+
'VendorCode,'+
'PmCode,'+
'EmployeeCode,'+
'ID)'+
'Values('+
''''+PoNo+''','+
''''+fieldbyname('VendorCodeO').asstring+''','+
fieldbyname('PmCode').asstring+','+
''''+fieldbyname('EmployeeCode').asstring+''','+
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -