金蝶云星空批量插入单据到数据库

##****************************服务插件*******************
#引入clr运行库
import clr
#添加对cloud插件开发的常用组件的引用
clr.AddReference('System')
clr.AddReference('System.Data')
clr.AddReference('Kingdee.BOS')
clr.AddReference('Kingdee.BOS.Core')
clr.AddReference('Kingdee.BOS.App')
clr.AddReference('Kingdee.BOS.App.Core')
clr.AddReference('Kingdee.BOS.DataEntity')
clr.AddReference('Kingdee.BOS.Contracts')
clr.AddReference('Kingdee.BOS.ServiceHelper')
#导入cloud基础库中的常用实体对象（分命名空间导入，不会递归导入）
from Kingdee.BOS import *
from Kingdee.BOS.Core import *
from Kingdee.BOS.Contracts import *
from Kingdee.BOS.Orm.DataEntity import *
from Kingdee.BOS.DataEntity import *
from Kingdee.BOS.Core.Bill import *
from Kingdee.BOS.Core.DynamicForm.PlugIn import *
from Kingdee.BOS.Core.DynamicForm.PlugIn.ControlModel import *
from System import *
from System.Data import *
from Kingdee.BOS.App.Data import *
from Kingdee.BOS.App.Core import *
from System.Collections.Generic import List
from Kingdee.BOS.ServiceHelper import *
from Kingdee.BOS.Util import*

ObjList=None;
def OnPreparePropertys(e):
e.FieldKeys.Add("FBillTypeID");
e.FieldKeys.Add("FPAYORGID");
e.FieldKeys.Add("F_DFYHZH");
e.FieldKeys.Add("F_DFZHMC");

def CreateBills(ObjType,dr):
billId=dr["FID"];
entryId=str(dr["FEntryID"]);
seq=str(dr["FSeq"]);
orgID=dr["F_BPW_ORGID"];
XMId=str(dr["F_BPW_XMID"]);
GJBH=str(dr["F_BPW_GJBH"]);
GJName=("{0}").format(dr["F_BPW_GJNAME"]);
GXZYId=str(dr["F_BPW_GXZY"]);
BJKZ=str(dr["F_BPW_BJKZ"]);
fullSN=str(dr["F_BPW_GJSN"]);
SN=str(dr["F_BPW_SN"]);
seq=str(dr["FSeq"]);
GJType=str(dr["F_BPW_GJType"]);
#saveService = ServiceFactory.GetService[ISaveService](this.Context);
newBillObj=DynamicObject(ObjType);
newBillObj["Number"]=fullSN;
newBillObj["Name"]=SN;
#OrgMeta = MetaDataServiceHelper.Load(this.Context, "ORG_Organizations");
#dynamicObjects = BusinessDataServiceHelper.Load(this.Context, pkIds.ToArray(), OrgMeta.BusinessInfo.GetDynamicObjectType());
#orgObj = dynamicObjects[0];
#QTWLBillObj["CreateOrgId"]=orgObj;
newBillObj["CreateOrgId_Id"]=orgID;
#QTWLBillObj["UseOrgId"]=orgObj;
newBillObj["UseOrgId_Id"]=orgID;
newBillObj["F_BPW_XMId_Id"]=XMId;
newBillObj["F_BPW_GJBH"]=GJBH;
newBillObj["F_BPW_GJName"]=GJName;
newBillObj["F_BPW_GXZY_Id"]=GXZYId;
newBillObj["F_BPW_BJKZ"]=BJKZ;
newBillObj["F_BPW_GXJHId"]=billId;
newBillObj["F_BPW_GXJHEntryID"]=entryId;
newBillObj["F_BPW_GXJHSeq"]=seq;
newBillObj["DocumentStatus"]="C";
newBillObj["ForbidStatus"]="A";
newBillObj["F_BPW_SNDetailId"]=str(dr["FDetailID"]);
newBillObj["F_BPW_GJEntryId"]=int(dr["F_BPW_XMENTRYID"]);
newBillObj["F_BPW_GXJHBillNo"]=dr["FBILLNO"];
newBillObj["F_BPW_GXStatus"]="2" if(seq=="1") else "1";
newBillObj["F_BPW_GJType"]=GJType;
return newBillObj;
#数据库批量插入单据
def CreateBillByBulkInsert(GXJHLst):
sql=("""/*dialect*/select
FID=Cast(0 as int),
FMasterId=Cast(0 as int),
FDOCUMENTSTATUS=Cast('C' as varchar(1)),
FFORBIDSTATUS=Cast('A' as varchar(1)),
FCREATEDATE=cast(getdate() as datetime),
FCREATORID=Cast({1} as int),
FMODIFIERID=Cast({1} as int),
FMODIFYDATE=cast(getdate() as datetime),
FCREATEORGID=Cast(t.F_BPW_ORGID as int),
FUSEORGID=Cast(t.F_BPW_ORGID as int),
FNUMBER=Cast(t.F_BPW_GJSN as nvarchar(255)),
SN=t.F_BPW_SN,
F_BPW_XMID=Cast(t.F_BPW_XMID as int),
F_BPW_GXJHID=Cast(t.FID as nvarchar(50)),
F_BPW_GJBH=Cast(t.F_BPW_GJBH as nvarchar(50)),
F_BPW_GXJHENTRYID=Cast(t.FEntryID as nvarchar(50)),
F_BPW_GXZY=Cast(t.F_BPW_GXZY as int),
F_BPW_GXJHSEQ=Cast(t.FSeq as int),
F_BPW_SNDETAILID=Cast(t.FDetailID as nvarchar(50)),
F_BPW_GJNAME=Cast(t.F_BPW_GJNAME as nvarchar(200)),
F_BPW_BJKZ=Cast(t.F_BPW_BJKZ as varchar(20)),
F_BPW_GXJHBILLNO=Cast(t.FBILLNO as nvarchar(50)),
F_BPW_GXSTATUS=Cast(case when t.FSeq=1 then '2' else '1' end as char(1)),
F_BPW_GJENTRYID=Cast(t.F_BPW_XMENTRYID as int),
F_BPW_GJTYPE=Cast(t.F_BPW_GJType as varchar(20)),
F_BPW_ISSN=Cast(t.F_BPW_ISSN as char(1)),
F_BPW_QTY=Cast(t.F_BPW_GJQTY as decimal(23, 10)),
F_BPW_KHBQTY=Cast(CASE when t.FSeq=1 then (Case when t.F_BPW_ISSN<>'1' then t.F_BPW_GJQTY else 1 end) else 0 end as decimal(23, 10))
from
(
select h.FBILLNO,h.FID,en.FEntryID,en.FSeq,h.F_BPW_ORGID,h.F_BPW_XMID,h.F_BPW_GJBH,h.F_BPW_XMENTRYID,
en.F_BPW_GXZY,h.F_BPW_GJNAME,en.F_BPW_BJKZ,xmEN.F_BPW_IsSN,h.F_BPW_XMSEQ,h.F_BPW_GJQTY,
xmSubEn.F_BPW_GJSN,xmSubEn.F_BPW_SN,xmSubEn.FDetailID,xmSubEn.FSeq AS FXMSubEnSeq,xmEN.F_BPW_GJType
from BPW_t_QH_GXPLan h
inner join BPW_t_QH_XMEntry xmEN on xmEN.FEntryID=h.F_BPW_XMENTRYID
inner join BPW_t_QH_XMSNSubEntry xmSubEn on xmSubEn.FEntryID=xmEN.FEntryID
inner join BPW_t_QH_GXJHEntry en on h.FID=en.FID
where xmEN.F_BPW_IsSN='1' and h.FID in ({0})
union all
select h.FBILLNO,h.FID,en.FEntryID,en.FSeq,h.F_BPW_ORGID,h.F_BPW_XMID,h.F_BPW_GJBH,h.F_BPW_XMENTRYID,
en.F_BPW_GXZY,h.F_BPW_GJNAME,en.F_BPW_BJKZ,xmEN.F_BPW_IsSN,h.F_BPW_XMSEQ,h.F_BPW_GJQTY,
xmH.F_BPW_GCDM+'-'+xmH.F_BPW_XMLOT+'-'+xmEN.F_BPW_GJBH as F_BPW_GJSN,xmEN.F_BPW_GJBH as F_BPW_SN,FDetailID=0,FXMSubEnSeq=1,xmEN.F_BPW_GJType
from BPW_t_QH_GXPLan h
inner join BPW_t_QH_XMEntry xmEN on xmEN.FEntryID=h.F_BPW_XMENTRYID
inner join BPW_t_QH_XM xmH on xmH.FID=xmEN.FID
inner join BPW_t_QH_GXJHEntry en on h.FID=en.FID
where xmEN.F_BPW_IsSN<>'1' and h.FID in ({0})
) t
order by Cast(t.F_BPW_XMID as int),cast(t.F_BPW_XMSEQ as int),t.FXMSubEnSeq,t.FSeq """).format(",".join(GXJHLst),this.Context.UserId);
#raise Exception(",".join(GXJHLst));
ds = DBServiceHelper.ExecuteDataSet(this.Context,sql);
tab = ds.Tables[0];
dataCount=tab.Rows.Count;
if(dataCount<=0):
return;
tab.TableName="BPW_t_GJXLHGXJH";
tabLName="BPW_t_GJXLHGXJH_L";
newFIDs = DBServiceHelper.GetSequenceInt64(this.Context, tab.TableName,dataCount);
newLFIDs=DBServiceHelper.GetSequenceInt64(this.Context, tabLName,dataCount);
dtL=DataTable();#多语言表
dtL.TableName = tabLName;
dtL.Columns.Add("FID", Int64().GetType());
dtL.Columns.Add("FPKID", Int64().GetType());
dtL.Columns.Add("FLocaleID", Int32().GetType());
dtL.Columns.Add("FNAME", "".ToString().GetType());
dtL.Columns.Add("FDESCRIPTION", "".ToString().GetType());
dtL.BeginLoadData();
i=0;
for dr in tab.Rows:
FID=newFIDs[i];
dr["FID"]=FID;
dr["FMasterId"]=FID;
rowDatas=List[object]();
rowDatas.Add(FID);
rowDatas.Add(newLFIDs[i]);
rowDatas.Add(2052);
rowDatas.Add(("{0}").format(dr["SN"]));
rowDatas.Add("数据库插入");
dtL.LoadDataRow(rowDatas.ToArray(), True);
i=i+1;
dtL.EndLoadData();
tab.Columns.Remove("SN");
DBUtils.BulkInserts(this.Context, tab);
DBUtils.BulkInserts(this.Context, dtL);
#msg=("{0}\r\n---{1}").format(tab.Rows.Count,dtL.Rows.Count);
#raise Exception(msg);
def EndOperationTransaction(e):
global ObjList;
formID="BPW_QH_GJXLHGXJH";
Meta=MetaDataServiceHelper.GetFormMetaData(this.Context, formID);
ObjType=Meta.BusinessInfo.GetDynamicObjectType();
ObjList=List[DynamicObject]();
GXJHLst=list(str(billObj["Id"]) for billObj in e.DataEntitys);
if(len(GXJHLst)<=0):
return;
CreateBillByBulkInsert(GXJHLst);
return;
#msg=("工序计划[{0}]").format(len(GXJHLst));
#raise Exception(msg);
sql=("""select h.FBILLNO,h.FID,en.FEntryID,en.FSeq,h.F_BPW_ORGID,h.F_BPW_XMID,h.F_BPW_GJBH,h.F_BPW_XMENTRYID,
en.F_BPW_GXZY,xmSubEn.F_BPW_GJSN,xmSubEn.F_BPW_SN,xmSubEn.FDetailID,h.F_BPW_GJNAME,en.F_BPW_BJKZ,xmEN.F_BPW_GJType
from BPW_t_QH_GXPLan h
inner join BPW_t_QH_XMSNSubEntry xmSubEn on xmSubEn.FEntryID=h.F_BPW_XMENTRYID
inner join BPW_t_QH_XMEntry xmEN on xmEN.FEntryID=xmSubEn.FEntryID
inner join BPW_t_QH_GXJHEntry en on h.FID=en.FID
where xmEN.F_BPW_GJType='1' AND h.FID in ({0}) """).format(",".join(GXJHLst));
tabRows=DBUtils.ExecuteDynamicObject(this.Context,sql);
if(tabRows.Count<=0):
return;
for dr in tabRows:
newBillObj=CreateBills(ObjType,dr);
ObjList.Add(newBillObj);
DBServiceHelper.LoadReferenceObject(this.Context, ObjList.ToArray(), ObjType, True);
#msg=("{0}").format(JsonUtil.Serialize(ObjList));
#DataWriterObj=BusinessDataWriter(this.Context);
#DataWriterObj.Save(ObjList.ToArray());
#saveResult=BusinessDataServiceHelper.Draft(this.Context, Meta.BusinessInfo,ObjList.ToArray(), None, "OnlySaveAudit");