/// <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); }