/// <summary>
/// 例如,根据colls 插入到表，colls应与表字段一致
/// </summary>
/// <param name="qurService"></param>
/// <param name="businessTypeSrv"></param>
/// <param name="entityD"></param>
/// <param name="TableName"></param>
private void Insert(IQueryService qurService,
DependencyObjectCollection colls, string typeKey)
{
var ItemDependencyObjectType = colls.ItemDependencyObjectType;
//创建临时表
DependencyObjectType tmpType = RegiesterType(ItemDependencyObjectType, null);
qurService.CreateTempTable(tmpType);
//创建DataTable
var TempDt = new DataTable();
TempDt = CreateDt(ItemDependencyObjectType, TempDt);
DOCToDataTable(colls, TempDt);
//插入临时表
List <QueryProperty> propies = new List <QueryProperty>();
InsertTemp(qurService, TempDt, tmpType.Name);
if (TempDt == null)
{
return;
}
foreach (DataColumn row in TempDt.Columns)
{
propies.Add(OOQL.CreateProperty(row.ColumnName, row.ColumnName));
}
if (propies != null && propies.Count > 0)
{
QueryNode node = OOQL.Select(propies
).From(tmpType.Name);
node = OOQL.Insert(typeKey, node, propies.Select(c => c.Alias).ToArray());
qurService.ExecuteNoQueryWithManageProperties(node);
}
}
/// <summary>
/// 插入条码交易明细
/// </summary>
private void InsertBcLine()
{
List <QueryProperty> properties = new List <QueryProperty>();
properties.AddRange(new QueryProperty[] {
Formulas.NewId("BC_LINE_ID"),
OOQL.CreateProperty("TRANSACTION_DOC_D.TRANSACTION_DOC_D_ID", "SOURCE_ID_ROid"),
OOQL.CreateConstants("TRANSACTION_DOC.TRANSACTION_DOC_D", "SOURCE_ID_RTK"),
OOQL.CreateProperty("TEMP_SCAN_DETAIL.barcode_no", "BARCODE_NO"),
Formulas.Ext("UNIT_CONVERT", "QTY",
new object[] { OOQL.CreateProperty("TRANSACTION_DOC_D.ITEM_ID"),
Formulas.IsNull(OOQL.CreateProperty("UNIT.UNIT_ID"), OOQL.CreateConstants(Maths.GuidDefaultValue())),
OOQL.CreateProperty("TEMP_SCAN_DETAIL.picking_qty"),
OOQL.CreateProperty("ITEM.STOCK_UNIT_ID"),
OOQL.CreateConstants(0) }),//库存数量
Formulas.IsNull(OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_ID"), OOQL.CreateConstants(Maths.GuidDefaultValue()), "WAREHOUSE_ID"),
Formulas.IsNull(OOQL.CreateProperty("BIN.BIN_ID"), OOQL.CreateConstants(Maths.GuidDefaultValue()), "BIN_ID")
});
#region 查询
QueryNode groupNode = GroupNode(true); //子查询的节点
_queryNode =
OOQL.Select(properties)
.From(groupNode, "TEMP_SCAN_DETAIL")
.InnerJoin("TRANSACTION_DOC", "TRANSACTION_DOC")
.On(OOQL.CreateProperty("TRANSACTION_DOC.DOC_NO") == OOQL.CreateProperty("TEMP_SCAN_DETAIL.doc_no"))
.InnerJoin("TRANSACTION_DOC.TRANSACTION_DOC_D", "TRANSACTION_DOC_D")
.On((OOQL.CreateProperty("TRANSACTION_DOC_D.TRANSACTION_DOC_ID") == OOQL.CreateProperty("TRANSACTION_DOC.TRANSACTION_DOC_ID")) &
(OOQL.CreateProperty("TRANSACTION_DOC_D.SequenceNumber") == OOQL.CreateProperty("TEMP_SCAN_DETAIL.seq")))
.InnerJoin("ITEM", "ITEM")
.On((OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("TRANSACTION_DOC_D.ITEM_ID")) &
(OOQL.CreateProperty("ITEM.ITEM_CODE") == OOQL.CreateProperty("TEMP_SCAN_DETAIL.item_no")))
.LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE")
.On((OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID") == OOQL.CreateProperty("TRANSACTION_DOC_D.ITEM_FEATURE_ID")) &
(Formulas.IsNull(OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_CODE"), OOQL.CreateConstants(string.Empty)) == OOQL.CreateProperty("TEMP_SCAN_DETAIL.item_feature_no")))
.InnerJoin("PLANT", "PLANT")
.On(OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateProperty("TEMP_SCAN_DETAIL.site_no"))
.LeftJoin("UNIT", "UNIT")
.On(OOQL.CreateProperty("UNIT.UNIT_CODE") == OOQL.CreateProperty("TEMP_SCAN_DETAIL.picking_unit_no"))
.LeftJoin("WAREHOUSE", "WAREHOUSE")
.On((OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_CODE") == OOQL.CreateProperty("TEMP_SCAN_DETAIL.warehouse_no")) &
(OOQL.CreateProperty("WAREHOUSE.Owner_Org.ROid") == OOQL.CreateProperty("PLANT.PLANT_ID")))
.LeftJoin("WAREHOUSE.BIN", "BIN")
.On((OOQL.CreateProperty("BIN.BIN_CODE") == OOQL.CreateProperty("TEMP_SCAN_DETAIL.storage_spaces_no")) &
(OOQL.CreateProperty("BIN.WAREHOUSE_ID") == OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_ID")))
.Where(OOQL.CreateProperty("TEMP_SCAN_DETAIL.barcode_no") != OOQL.CreateConstants(string.Empty));
#endregion
QueryNode insertNode = OOQL.Insert("BC_LINE", _queryNode, properties.Select(c => c.Alias).ToArray());
_querySrv.ExecuteNoQuery(insertNode);
}
/// <summary>
/// 插入数据
/// 常量插入
/// </summary>
/// <param name="querySrv">查询服务</param>
/// <param name="typeKeyPath">实体名称路径</param>
/// <param name="dicPropertyValues">值</param>
/// <param name="isTmpTable">是否是临时表</param>
public static int InsertDocumentToDataBaseOrTmpTable(IQueryService querySrv, string typeKeyPath, Dictionary <string, QueryProperty> dicPropertyValues, bool isTmpTable)
{
QueryNode insertNode = OOQL.Insert(typeKeyPath, dicPropertyValues.Keys).Values(dicPropertyValues.Values);
if (!isTmpTable)
{
return(querySrv.ExecuteNoQueryWithManageProperties(insertNode));
}
else
{
return(querySrv.ExecuteNoQuery(insertNode));
}
}
private QueryNode QueryNodeForInsert(string countingNo, string countingType, QueryNode sumBarCodeNode)
{
QueryNode selectNode = OOQL.Select(Formulas.NewId("COUNTING_PLAN_BARCODE_ID"),
OOQL.CreateConstants(countingNo),
OOQL.CreateConstants(countingType),
OOQL.CreateProperty("sumBarCode.barcode_no"),
OOQL.CreateProperty("sumBarCode.ITEM_ID"),
OOQL.CreateProperty("sumBarCode.ITEM_FEATURE_ID"),
OOQL.CreateProperty("sumBarCode.WAREHOUSE_ID"),
OOQL.CreateProperty("sumBarCode.BIN_ID"),
OOQL.CreateProperty("sumBarCode.ITEM_LOT_ID"),
OOQL.CreateProperty("sumBarCode.QTY"),
OOQL.CreateProperty("sumBarCode.PLANT_ID"),
OOQL.CreateConstants("OTHER", GeneralDBType.String, "BO_ID_RTK"),
OOQL.CreateConstants(Maths.GuidDefaultValue(), GeneralDBType.Guid, "BO_ID_ROid")
)
.From(sumBarCodeNode, "sumBarCode")
.LeftJoin("COUNTING_PLAN_BARCODE", "COUNTING_PLAN_BARCODE")
.On(OOQL.CreateProperty("COUNTING_PLAN_BARCODE.DOC_NO") == OOQL.CreateConstants(countingNo) &
OOQL.CreateProperty("COUNTING_PLAN_BARCODE.BARCODE_NO") == OOQL.CreateProperty("sumBarCode.barcode_no") &
OOQL.CreateProperty("COUNTING_PLAN_BARCODE.PLANT_ID") == OOQL.CreateProperty("sumBarCode.PLANT_ID") &
OOQL.CreateProperty("COUNTING_PLAN_BARCODE.ITEM_ID") == OOQL.CreateProperty("sumBarCode.ITEM_ID") &
OOQL.CreateProperty("COUNTING_PLAN_BARCODE.ITEM_FEATURE_ID") == OOQL.CreateProperty("sumBarCode.ITEM_FEATURE_ID") &
OOQL.CreateProperty("COUNTING_PLAN_BARCODE.WAREHOUSE_ID") == OOQL.CreateProperty("sumBarCode.WAREHOUSE_ID") &
OOQL.CreateProperty("COUNTING_PLAN_BARCODE.BIN_ID") == OOQL.CreateProperty("sumBarCode.BIN_ID") &
OOQL.CreateProperty("COUNTING_PLAN_BARCODE.ITEM_LOT_ID") == OOQL.CreateProperty("sumBarCode.ITEM_LOT_ID")
//20170505 mark by shenbao for P001-170505001 ===begin===
//OOQL.CreateProperty("COUNTING_PLAN_BARCODE.BO_ID.ROid") == OOQL.CreateProperty("sumBarCode.BO_ID_ROid") &
//OOQL.CreateProperty("COUNTING_PLAN_BARCODE.BO_ID.RTK") == OOQL.CreateProperty("sumBarCode.BO_ID_RTK")
//20170505 mark by shenbao for P001-170505001 ===end===
)
.InnerJoin("ITEM", "ITEM")
.On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("sumBarCode.ITEM_ID"))
.Where(OOQL.CreateProperty("COUNTING_PLAN_BARCODE.COUNTING_PLAN_BARCODE_ID").IsNull());
List <string> insertString = new List <string>();
insertString.Add("COUNTING_PLAN_BARCODE_ID");
insertString.Add("DOC_NO");
insertString.Add("COUNTING_TYPE");
insertString.Add("BARCODE_NO");
insertString.Add("ITEM_ID");
insertString.Add("ITEM_FEATURE_ID");
insertString.Add("WAREHOUSE_ID");
insertString.Add("BIN_ID");
insertString.Add("ITEM_LOT_ID");
insertString.Add("COUNTING_QTY");
insertString.Add("PLANT_ID");
insertString.Add("BO_ID.RTK");
insertString.Add("BO_ID.ROid");
return(OOQL.Insert("COUNTING_PLAN_BARCODE", selectNode, insertString.ToArray()));
}
/// <summary>
/// 新增到数据库
/// </summary>
/// <param name="insertNode"></param>
/// <param name="qurService"></param>
private void InsertBcLineDB(QueryNode insertNode, IQueryService qurService)
{
List <string> insertList = new List <string>();
insertList.Add("BC_LINE_ID");
insertList.Add("BARCODE_NO");
insertList.Add("SOURCE_ID.RTK");
insertList.Add("SOURCE_ID.ROid");
insertList.Add("QTY");
insertList.Add("WAREHOUSE_ID");
insertList.Add("BIN_ID");
QueryNode node = OOQL.Insert("BC_LINE", insertNode, insertList.ToArray());
qurService.ExecuteNoQueryWithManageProperties(node);
}
/// <summary>
/// 插入数据
/// 根据查询node批量插入
/// </summary>
/// <param name="querySrv">查询服务</param>
/// <param name="typeKeyPath">实体名称路径</param>
/// <param name="node">节点</param>
/// <param name="isTmpTable">是否是临时表</param>
public static int InsertDocumentToDataBaseOrTmpTable(IQueryService querySrv, string typeKeyPath, QueryNode node, string[] properties, bool isTmpTable)
{
QueryNode insertNode = OOQL.Insert(typeKeyPath, node, properties);
try {
if (!isTmpTable)
{
return(querySrv.ExecuteNoQueryWithManageProperties(insertNode));
}
else
{
return(querySrv.ExecuteNoQuery(insertNode));
}
} catch (Exception ex) {
throw new CustomSqlException(ex.Message);
}
}
//20170106 add by wangyq for P001-161230002 增加批号的新增数据库逻辑 =============begin=============
/// <summary>
///
/// </summary>
/// <param name="itemLot"></param>
/// <param name="itemId"></param>
/// <param name="itemFeatureId"></param>
private void InsertItemLot(DependencyObject itemLot, object itemId, object itemFeatureId)
{
Dictionary <string, QueryProperty> insertList = new Dictionary <string, QueryProperty>();
insertList.Add("ITEM_LOT_ID", OOQL.CreateConstants(Guid.NewGuid()));
insertList.Add("LOT_CODE", OOQL.CreateConstants(itemLot["LOT_CODE"]));
//insertList.Add("LOT_DESCRIPTION", OOQL.CreateConstants(itemLot["LOT_CODE"]));//20170509 mark by liwei1 for B001-170505004
insertList.Add("LOT_DESCRIPTION", OOQL.CreateConstants(string.Empty));//20170509 add by liwei1 for B001-170505004
insertList.Add("ALLOW_ISSUE_DATE", OOQL.CreateConstants(itemLot["ALLOW_ISSUE_DATE"]));
insertList.Add("EFFECTIVE_DATE", OOQL.CreateConstants(itemLot["EFFECTIVE_DATE"]));
insertList.Add("INEFFECTIVE_DATE", OOQL.CreateConstants(itemLot["INEFFECTIVE_DATE"]));
insertList.Add("ITEM_ID", OOQL.CreateConstants(itemId));
insertList.Add("ITEM_FEATURE_ID", OOQL.CreateConstants(itemFeatureId));
QueryNode node = OOQL.Insert("ITEM_LOT", insertList.Keys.ToArray()).Values(insertList.Values.ToArray());
this.GetService <IQueryService>().ExecuteNoQueryWithManageProperties(node);
}
/// <summary>
/// 利用临时表关联实体表进行批量新增条码交易明细
/// </summary>
/// <param name="qrySrv"></param>
/// <param name="tmpBCLine"></param>
private void InsertBCLine(DateTime report_datetime) //20170413 modi by wangyq for P001-170412001 添加参数DateTime report_datetime
{
bool bcLintFlag = UtilsClass.IsBCLineManagement(_qurService);
if (!bcLintFlag)
{
return;
}
List <QueryProperty> selectList = new List <QueryProperty>();
#region 查询新增字段集合
selectList.Add(Formulas.NewId("BC_LINE_ID"));
selectList.Add(OOQL.CreateProperty("tmpTable.barcode_no", "BARCODE_NO"));
selectList.Add(OOQL.CreateConstants("TRANSACTION_DOC.TRANSACTION_DOC_D", "SOURCE_ID_RTK"));
selectList.Add(OOQL.CreateProperty("tmpTable.ID", "SOURCE_ID_ROid"));
selectList.Add(Formulas.Ext("UNIT_CONVERT", "QTY", new object[] { OOQL.CreateProperty("ITEM.ITEM_ID")
, OOQL.CreateProperty("UNIT.UNIT_ID")
, OOQL.CreateProperty("tmpTable.picking_qty")
, OOQL.CreateProperty("ITEM.STOCK_UNIT_ID")
, OOQL.CreateConstants(0) }));
selectList.Add(Formulas.IsNull(OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_ID"), OOQL.CreateConstants(Maths.GuidDefaultValue()), "WAREHOUSE_ID"));
selectList.Add(Formulas.IsNull(Formulas.Case(null, OOQL.CreateProperty("BIN.BIN_ID"), new CaseItem[] {
new CaseItem(OOQL.CreateProperty("tmpTable.storage_spaces_no") == OOQL.CreateConstants(string.Empty)
, OOQL.CreateConstants(Maths.GuidDefaultValue()))
}), OOQL.CreateConstants(Maths.GuidDefaultValue()), "BIN_ID"));
//20170413 modi by wangyq for P001-170412001 ==================begin=================
//20170330 add by wangrm for P001-170328001=====start=======
selectList.Add(OOQL.CreateConstants("PLANT", GeneralDBType.String, "Owner_Org_RTK"));
selectList.Add(OOQL.CreateProperty("PLANT.PLANT_ID", "Owner_Org_ROid"));
selectList.Add(OOQL.CreateProperty("Table_scan.ID", "SOURCE_DOC_ID"));
selectList.Add(OOQL.CreateConstants(report_datetime.Date, GeneralDBType.Date, "DOC_DATE"));
//selectList.Add(Formulas.IsNull(OOQL.CreateProperty("TRANSACTION_DOC.Owner_Org.RTK"), OOQL.CreateConstants(string.Empty), "Owner_Org_RTK"));
//selectList.Add(Formulas.IsNull(OOQL.CreateProperty("TRANSACTION_DOC.Owner_Org.ROid"), OOQL.CreateConstants(Maths.GuidDefaultValue()), "Owner_Org_ROid"));
//selectList.Add(Formulas.IsNull(OOQL.CreateProperty("TRANSACTION_DOC.TRANSACTION_DOC_ID"), OOQL.CreateConstants(Maths.GuidDefaultValue()), "SOURCE_DOC_ID"));
//selectList.Add(Formulas.IsNull(OOQL.CreateProperty("TRANSACTION_DOC.DOC_DATE"), OOQL.CreateConstants(OrmDataOption.EmptyDateTime), "DOC_DATE"));
//20170330 add by wangrm for P001-170328001=====end=======
//20170413 modi by wangyq for P001-170412001 ==================end=================
#endregion
QueryNode groupNode = GroupNode(true);
QueryNode insertNode = OOQL.Select(selectList.ToArray())
.From(groupNode, "tmpTable")
.InnerJoin(_tempScan.Name, "Table_scan")
.On(OOQL.CreateProperty("Table_scan.info_lot_no") == OOQL.CreateProperty("tmpTable.info_lot_no"))
.InnerJoin("PLANT", "PLANT")
.On(OOQL.CreateProperty("Table_scan.site_no") == OOQL.CreateProperty("PLANT.PLANT_CODE"))
.InnerJoin("ITEM", "ITEM")
.On(OOQL.CreateProperty("tmpTable.item_no") == OOQL.CreateProperty("ITEM.ITEM_CODE"))
.InnerJoin("UNIT", "UNIT")
.On(OOQL.CreateProperty("tmpTable.picking_unit_no") == OOQL.CreateProperty("UNIT.UNIT_CODE"))
.LeftJoin("WAREHOUSE", "WAREHOUSE")
.On(OOQL.CreateProperty("tmpTable.warehouse_no") == OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_CODE")
& OOQL.CreateProperty("PLANT.PLANT_ID") == OOQL.CreateProperty("WAREHOUSE.Owner_Org.ROid"))
.LeftJoin("WAREHOUSE.BIN", "BIN")
.On(OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_ID") == OOQL.CreateProperty("BIN.WAREHOUSE_ID")
& OOQL.CreateProperty("tmpTable.storage_spaces_no") == OOQL.CreateProperty("BIN.BIN_CODE"))
//20170413 mark by wangyq for P001-170412001 ==================begin================
//20170330 add by wangrm for P001-170328001=====start=======
//.LeftJoin("TRANSACTION_DOC", "TRANSACTION_DOC")
//.On(OOQL.CreateProperty("Table_scan.ID") == OOQL.CreateProperty("TRANSACTION_DOC.TRANSACTION_DOC_ID"))
//20170330 add by wangrm for P001-170328001=====end=======
//20170413 mark by wangyq for P001-170412001 ==================end================
.Where(OOQL.CreateProperty("tmpTable.barcode_no") != OOQL.CreateConstants(string.Empty));
List <string> insertStrList = new List <string>();
#region 新增字段集合
insertStrList.Add("BC_LINE_ID");
insertStrList.Add("BARCODE_NO");
insertStrList.Add("SOURCE_ID.RTK");
insertStrList.Add("SOURCE_ID.ROid");
insertStrList.Add("QTY");
insertStrList.Add("WAREHOUSE_ID");
insertStrList.Add("BIN_ID");
//20170330 add by wangrm for P001-170328001=====start=======
insertStrList.Add("Owner_Org_RTK");
insertStrList.Add("Owner_Org_ROid");
insertStrList.Add("SOURCE_DOC_ID");
insertStrList.Add("DOC_DATE");
//20170330 add by wangrm for P001-170328001=====end=======
#endregion
QueryNode node = OOQL.Insert("BC_LINE", insertNode, insertStrList.ToArray());
_qurService.ExecuteNoQueryWithManageProperties(node);
}
