/// <summary>
/// 创建批量修改所需要的DataTable和Mapping
/// </summary>
private void CreateRelateTable(ref DataTable issueReceiptD
, ref List <BulkCopyColumnMapping> issueReceiptDMap)
{
#region 创建领料单身表
string[] issueReceiptDColumns = new string[] {
"DOC_NO", //单号
"SequenceNumber", //序号
"ISSUE_RECEIPT_QTY", //领退料数量
"PLANT_CODE", //工厂编号
"ITEM_CODE", //品号
"ITEM_FEATURE_CODE", //特征码
"UNIT_CODE", //单位编号
"WAREHOUSE_CODE", //仓库编号
"BIN_CODE", //库位编号
"LOT_CODE", //批号
"BARCODE_NO" //条码
};
issueReceiptD = UtilsClass.CreateDataTable("ISSUE_RECEIPT_D", issueReceiptDColumns,
new Type[] {
typeof(string), //单号
typeof(int), //序号
typeof(decimal), //领退料数量
typeof(string), //工厂编号
typeof(string), //品号
typeof(string), //特征码
typeof(string), //单位编号
typeof(string), //仓库编号
typeof(string), //库位编号
typeof(string), //批号
typeof(string) //条码
});
//创建map对照表
Dictionary <string, string> dicIssueReceiptD = new Dictionary <string, string>();
foreach (string key in issueReceiptDColumns)
{
dicIssueReceiptD.Add(key, key);
}
issueReceiptDMap = UtilsClass.CreateBulkMapping(dicIssueReceiptD);
#endregion
}
/// <summary>
/// 获取调出单查询信息
/// </summary>
/// <param name="siteNo">工厂编号</param>
/// <param name="programJobNo">作业编号</param>
/// <returns></returns>
private QueryNode GetTransferDocQueryNode(string siteNo, string programJobNo
, DependencyObjectCollection condition//20170328 add by wangyq for P001-170327001
)
{
QueryNode queryNode =
OOQL.Select(true,
OOQL.CreateProperty("TRANSFER_DOC.DOC_NO", "doc_no"),
OOQL.CreateProperty("TRANSFER_DOC.DOC_DATE", "create_date"),
Formulas.IsNull(OOQL.CreateProperty("ADMIN_UNIT.ADMIN_UNIT_NAME"), OOQL.CreateConstants(string.Empty), "customer_name"),
OOQL.CreateProperty("PLANT.PLANT_CODE", "main_organization"),
OOQL.CreateConstants(programJobNo, "program_job_no"),
Formulas.IsNull(OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_NAME"), OOQL.CreateConstants(string.Empty), "employee_name"))
.From("TRANSFER_DOC", "TRANSFER_DOC")
.InnerJoin("TRANSFER_DOC.TRANSFER_DOC_D", "TRANSFER_DOC_D")
.On(OOQL.CreateProperty("TRANSFER_DOC_D.TRANSFER_DOC_ID") == OOQL.CreateProperty("TRANSFER_DOC.TRANSFER_DOC_ID"))
.InnerJoin("PLANT", "PLANT")
.On(OOQL.CreateProperty("PLANT.PLANT_ID") == OOQL.CreateProperty("TRANSFER_DOC.Owner_Org.ROid"))
.LeftJoin("ADMIN_UNIT", "ADMIN_UNIT")
.On(OOQL.CreateProperty("ADMIN_UNIT.ADMIN_UNIT_ID") == OOQL.CreateProperty("TRANSFER_DOC.Owner_Dept"))
.LeftJoin("EMPLOYEE", "EMPLOYEE")
.On(OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_ID") == OOQL.CreateProperty("TRANSFER_DOC.Owner_Emp"));
QueryConditionGroup group = //20170328 add by wangyq for P001-170327001
//.Where((OOQL.AuthFilter("TRANSFER_DOC", "TRANSFER_DOC")) &//20170328 mark by wangyq for P001-170327001
((OOQL.CreateProperty("TRANSFER_DOC.CATEGORY") == OOQL.CreateConstants("17")) &
(OOQL.CreateProperty("TRANSFER_DOC.ApproveStatus") == OOQL.CreateConstants("Y")) &
(OOQL.CreateProperty("TRANSFER_DOC_D.CLOSE") == OOQL.CreateConstants("0")) &
(OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo))
);
//);//20170328 mark by wangyq for P001-170327001
//20170328 add by wangyq for P001-170327001 ================begin==============
if (condition != null)
{
ConditionPropertyNameEntity conPropertyEntity = new ConditionPropertyNameEntity("TRANSFER_DOC.DOC_NO", "TRANSFER_DOC.DOC_DATE", new string[] { "1", "2", "3", "4" });
group = UtilsClass.CreateNewConditionByParameter(group, condition, conPropertyEntity);
}
queryNode = ((JoinOnNode)queryNode).Where(OOQL.AuthFilter("TRANSFER_DOC", "TRANSFER_DOC") & (group));
//20170328 add by wangyq for P001-170327001 ================end==============
return(queryNode);
}
private QueryNode GetMOReceiptReqListNode(string siteNo, string programJobNo
, DependencyObjectCollection condition//20170328 add by wangyq for P001-170327001
)
{
QueryNode node = OOQL.Select(
OOQL.CreateProperty("MO_RECEIPT_REQUISTION.DOC_NO", "doc_no"),
OOQL.CreateProperty("MO_RECEIPT_REQUISTION.DOC_DATE", "create_date"),
Formulas.IsNull(OOQL.CreateProperty("WORK_CENTER.WORK_CENTER_NAME"), OOQL.CreateConstants(string.Empty), "customer_name"),
OOQL.CreateConstants(programJobNo, "program_job_no"),
Formulas.IsNull(OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_NAME"), OOQL.CreateConstants(string.Empty), "employee_name"),
OOQL.CreateProperty("PLANT.PLANT_CODE", "main_organization"))
.From("MO_RECEIPT_REQUISTION", "MO_RECEIPT_REQUISTION")
.InnerJoin("PLANT", "PLANT")
.On((OOQL.CreateProperty("MO_RECEIPT_REQUISTION.Owner_Org.ROid") == OOQL.CreateProperty("PLANT.PLANT_ID")))
.LeftJoin("WORK_CENTER")
.On((OOQL.CreateProperty("MO_RECEIPT_REQUISTION.Owner_Dept") == OOQL.CreateProperty("WORK_CENTER.WORK_CENTER_ID")))
.LeftJoin("EMPLOYEE", "EMPLOYEE")
.On((OOQL.CreateProperty("MO_RECEIPT_REQUISTION.Owner_Emp") == OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_ID")))
//20170328 add by wangyq for P001-170327001 ================begin==============
.LeftJoin("ADMIN_UNIT", "ADMIN_UNIT")
.On(OOQL.CreateProperty("MO_RECEIPT_REQUISTION.Owner_Dept") == OOQL.CreateProperty("ADMIN_UNIT.ADMIN_UNIT_ID"));
//20170328 add by wangyq for P001-170327001 ================end==============
//.Where((OOQL.AuthFilter("MO_RECEIPT_REQUISTION", "MO_RECEIPT_REQUISTION")) &//20170328 mark by wangyq for P001-170327001
QueryConditionGroup group =//20170328 add by wangyq for P001-170327001
(OOQL.CreateProperty("MO_RECEIPT_REQUISTION.ApproveStatus") == OOQL.CreateConstants("Y")
& OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo)
& OOQL.CreateProperty("MO_RECEIPT_REQUISTION.CLOSE") == OOQL.CreateConstants("0", GeneralDBType.String)//20170104 add by wangyq for P001-161215001
);
//);//20170328 mark by wangyq for P001-170327001
//20170328 add by wangyq for P001-170327001 ================begin==============
if (condition != null)
{
ConditionPropertyNameEntity conPropertyEntity = new ConditionPropertyNameEntity("MO_RECEIPT_REQUISTION.DOC_NO", "MO_RECEIPT_REQUISTION.DOC_DATE", new string[] { "1", "2", "3", "4" });
group = UtilsClass.CreateNewConditionByParameter(group, condition, conPropertyEntity);
}
node = ((JoinOnNode)node).Where(OOQL.AuthFilter("MO_RECEIPT_REQUISTION", "MO_RECEIPT_REQUISTION") & (group));
//20170328 add by wangyq for P001-170327001 ================end==============
return(node);
}
/// <summary>
/// 根据品号获取品号采购信息
/// </summary>
/// <param name="itemID">品号ID集合</param>
/// <param name="mainOrganization">采购域</param>
/// <returns></returns>
private DependencyObjectCollection GetItemForSupplyCenter(DependencyObjectCollection itemID, string siteNo, object mainOrganization, string programJobNo)
{
List <QueryProperty> properties = GetQueryProperty(siteNo);
properties.Add(Formulas.IsNull(OOQL.CreateProperty("ITEM_PURCHASE.RECEIPT_OVER_RATE"), OOQL.CreateConstants(0m, GeneralDBType.Decimal)
, "over_deliver_rate")); //超交率
QueryNode node = OOQL.Select(
properties
)
.From("ITEM", "ITEM")
.LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE")
.On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("ITEM_FEATURE.ITEM_ID"))
.LeftJoin("UNIT")
.On(OOQL.CreateProperty("ITEM.STOCK_UNIT_ID") == OOQL.CreateProperty("UNIT.UNIT_ID"))
.LeftJoin("ITEM_PURCHASE")
.On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("ITEM_PURCHASE.ITEM_ID"))
.Where(OOQL.AuthFilter("ITEM", "ITEM")
& OOQL.CreateProperty("ITEM_PURCHASE.Owner_Org.ROid") == OOQL.CreateConstants(mainOrganization)
& OOQL.CreateProperty("ITEM.ITEM_ID").In(OOQL.CreateDyncParameter("IDS", itemID.Select(c => c["item_id"]).ToArray())));
return(UtilsClass.ExecuteDependencyObject(this.GetService <IQueryService>(), node));
}
/// <summary>
///
/// </summary>
/// <returns></returns>
private string[] GetParaFIL()
{
ICreateService createService = this.GetService <ICreateService>("PARA_FIL");
DependencyObject filEntity = createService.Create() as DependencyObject;
string[] result = new string[] { string.Empty, string.Empty, string.Empty };
if (filEntity.DependencyObjectType.Properties.Contains("BARCODE_SYMBOL_FOR_WH_BIN"))
{
QueryNode node = OOQL.Select(OOQL.CreateProperty("PARA_FIL.BC_INVENTORY_MANAGEMENT"),
OOQL.CreateProperty("PARA_FIL.BARCODE_SYMBOL_FOR_WH_BIN"),
OOQL.CreateProperty("PARA_FIL.ITEM_FEATURE_CONTROL"))
.From("PARA_FIL", "PARA_FIL")
.Where(OOQL.AuthFilter("PARA_FIL", "PARA_FIL"));
DependencyObjectCollection filColl = this.GetService <IQueryService>().ExecuteDependencyObject(node);
foreach (DependencyObject filObj in filColl)
{
result[0] = filObj["BC_INVENTORY_MANAGEMENT"].ToBoolean() ? "Y" : "N";
if (string.IsNullOrEmpty(filObj["BARCODE_SYMBOL_FOR_WH_BIN"].ToStringExtension()))
{
result[1] = "@";
}
else
{
result[1] = filObj["BARCODE_SYMBOL_FOR_WH_BIN"].ToStringExtension();
}
result[2] = filObj["ITEM_FEATURE_CONTROL"].ToBoolean() ? "Y" : "N";
break;
}
}
else
{
bool isInvertory = UtilsClass.IsBCInventoryManagement(this.GetService <IQueryService>());
result[0] = isInvertory ? "Y" : "N";
result[1] = "@";
result[2] = "Y";
}
return(result);
}
/// <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);
}
private QueryNode QueryData(string program_job_no, string status,
DependencyObjectCollection doc_no, string site_no, string warehouse_no, string warseHouseControl)
{
OrderByItem[] orderByItem = null;
QueryProperty property = null;
bool bcInvControl = UtilsClass.IsBCInventoryManagement(this.GetService <IQueryService>());
if (bcInvControl)
{
property = OOQL.CreateProperty("BC_INVENTORY.QTY");
}
else
{
property = OOQL.CreateProperty("ITEM_WAREHOUSE_BIN.INVENTORY_QTY");
}
//策略
if (warseHouseControl == "1") //1.生效日期先进先出
{
orderByItem = new OrderByItem[] { OOQL.CreateOrderByItem("ITEM_LOT.EFFECTIVE_DATE", SortType.Asc) }
}
;
else if (warseHouseControl == "2") //2.生效日期后进先出
{
orderByItem = new OrderByItem[] { OOQL.CreateOrderByItem("ITEM_LOT.EFFECTIVE_DATE", SortType.Desc) }
}
;
else if (warseHouseControl == "3") //3.不限定
{
orderByItem = new OrderByItem[] { OOQL.CreateOrderByItem("ITEM_LOT.LOT_CODE", SortType.Asc) }
}
;
else if (warseHouseControl == "4") //4.先到期先出
{
orderByItem = new OrderByItem[] { OOQL.CreateOrderByItem("ITEM_LOT.INEFFECTIVE_DATE", SortType.Asc) }
}
;
else if (warseHouseControl == "5") //5.允许出库日早者先出
{
orderByItem = new OrderByItem[] { OOQL.CreateOrderByItem("ITEM_LOT.ALLOW_ISSUE_DATE", SortType.Asc) }
}
;
else if (warseHouseControl == "6") //6.可用量少的先出
{
orderByItem = new OrderByItem[] { OOQL.CreateOrderByItem(property, SortType.Asc) };
}
else if (warseHouseControl == "7") //6.可用量少的先出
{
orderByItem = new OrderByItem[] { OOQL.CreateOrderByItem(property, SortType.Desc) };
}
//组织单号
List <ConstantsQueryProperty> docNos = new List <ConstantsQueryProperty>();
foreach (DependencyObject item in doc_no)
{
docNos.Add(OOQL.CreateConstants(item["doc_no"].ToStringExtension()));
}
QueryNode node = null;
if (program_job_no == "5")//20170328 modi by wangyq for P001-170327001去掉&& status == "A"
{
node = GetSalesDeliveryNode(site_no, docNos, warseHouseControl);
}
else if (program_job_no.StartsWith("7") && status == "A")
{
node = GetMONode(site_no, program_job_no, docNos, warseHouseControl);
}
else if (program_job_no.StartsWith("7") && status == "S")
{
node = GetIssueReceiptNode(site_no, docNos, warseHouseControl);
}
else if (program_job_no.StartsWith("11") && status == "S")
{
node = GeTransferDocNode(site_no, docNos, warseHouseControl);
}
else if (program_job_no.StartsWith("4")) //20170629 add by zhangcn for P001-170606002 //20170719 modi by shenbao for 拿掉status == "A"
{
node = GePurchaseReturnNode(site_no, docNos, warseHouseControl); //20170629 add by zhangcn for P001-170606002
}
else if (program_job_no.StartsWith("5-1")) //20170829 add by shenbao for P001-170717001
{
node = GetSalesOrderDocNode(site_no, docNos, warseHouseControl);
}
List <QueryProperty> pubProperties = new List <QueryProperty>()
{
OOQL.CreateConstants("99", "enterprise_no"),
OOQL.CreateProperty("QuerySource.PLANT_CODE", "site_no"),
OOQL.CreateProperty("QuerySource.ITEM_CODE", "item_no"),
OOQL.CreateProperty("QuerySource.ITEM_NAME", "item_name"),
Formulas.IsNull(OOQL.CreateProperty("QuerySource.ITEM_SPECIFICATION"), OOQL.CreateConstants(string.Empty), "item_spec"),
Formulas.IsNull(OOQL.CreateProperty("QuerySource.ITEM_FEATURE_CODE"), OOQL.CreateConstants(string.Empty), "item_feature_no"),
Formulas.IsNull(OOQL.CreateProperty("QuerySource.ITEM_FEATURE_SPECIFICATION"), OOQL.CreateConstants(string.Empty), "item_feature_name"),
Formulas.IsNull(OOQL.CreateProperty("QuerySource.UNIT_CODE"), OOQL.CreateConstants(string.Empty), "unit_no"),
Formulas.IsNull(OOQL.CreateProperty("QuerySource.STOCK_UNIT_CODE"), OOQL.CreateConstants(string.Empty), "inventory_unit"),
Formulas.IsNull(OOQL.CreateProperty("QuerySource.SOURCE_QTY"), OOQL.CreateConstants(0), "SOURCE_QTY")
};
//查询
if (bcInvControl)
{
pubProperties.AddRange(new QueryProperty[] {
OOQL.CreateProperty("BC_INVENTORY.BARCODE_NO", "barcode_no"),
Formulas.IsNull(OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_CODE"), OOQL.CreateConstants(string.Empty), "warehouse_no"),
Formulas.IsNull(OOQL.CreateProperty("BIN.BIN_CODE"), OOQL.CreateConstants(string.Empty), "storage_spaces_no"),
Formulas.IsNull(OOQL.CreateProperty("ITEM_LOT.LOT_CODE"), OOQL.CreateConstants(string.Empty), "lot_no"),
OOQL.CreateConstants(UtilsClass.SpaceValue, "inventory_management_features"),//20170328 modi by wangyq for P001-170327001 old:string.Empty
Formulas.IsNull(OOQL.CreateProperty("BC_INVENTORY.CreateDate"), OOQL.CreateConstants(OrmDataOption.EmptyDateTime.Date), "first_storage_date"),
Formulas.IsNull(OOQL.CreateProperty("BC_INVENTORY.QTY"), OOQL.CreateConstants(0), "inventory_qty"),
Formulas.IsNull(Formulas.Ext("UNIT_CONVERT", new object[] { OOQL.CreateProperty("QuerySource.ITEM_ID")
, OOQL.CreateProperty("QuerySource.STOCK_UNIT_ID")
, OOQL.CreateProperty("BC_INVENTORY.QTY")
, OOQL.CreateProperty("QuerySource.UNIT_ID")
, OOQL.CreateConstants(0) }), OOQL.CreateConstants(0), "conversion_qty"),
Formulas.RowNumber("sort_no", OOQL.Over(new QueryProperty[] { OOQL.CreateProperty("QuerySource.ITEM_CODE") },
orderByItem
))
});
node = OOQL.Select(
pubProperties
)
.From(node, "QuerySource")
.LeftJoin("BC_INVENTORY")
.On(OOQL.CreateProperty("QuerySource.ITEM_ID") == OOQL.CreateProperty("BC_INVENTORY.ITEM_ID")
& Formulas.IsNull(OOQL.CreateProperty("QuerySource.ITEM_FEATURE_ID"), OOQL.CreateConstants(Maths.GuidDefaultValue())) == OOQL.CreateProperty("BC_INVENTORY.ITEM_FEATURE_ID"))
.LeftJoin("WAREHOUSE")
.On(OOQL.CreateProperty("BC_INVENTORY.WAREHOUSE_ID") == OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_ID"))
.LeftJoin("WAREHOUSE.BIN", "BIN")
.On(OOQL.CreateProperty("BC_INVENTORY.BIN_ID") == OOQL.CreateProperty("BIN.BIN_ID"))
.LeftJoin("ITEM_LOT")
.On(OOQL.CreateProperty("BC_INVENTORY.ITEM_LOT_ID") == OOQL.CreateProperty("ITEM_LOT.ITEM_LOT_ID"))
.LeftJoin("PLANT") //20170719 add by shenbao for P001-170717001
.On(OOQL.CreateProperty("WAREHOUSE.Owner_Org.ROid") == OOQL.CreateProperty("PLANT.PLANT_ID"))
.Where(((OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_CODE") == OOQL.CreateConstants(warehouse_no))
| OOQL.CreateConstants(warehouse_no) == OOQL.CreateConstants(""))
& OOQL.CreateProperty("BC_INVENTORY.QTY") > OOQL.CreateConstants(0)
& OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_PROPERTY") == OOQL.CreateConstants("1") //20170717 add by shenbao for P001-170717001
& OOQL.CreateProperty("WAREHOUSE.INCLUDED_AVAILABLE_QTY") == OOQL.CreateConstants(true) //20170717 add by shenbao for P001-170717001
& OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(site_no)); //20170719 add by shenbao for P001-170717001
}
else
{
pubProperties.AddRange(new QueryProperty[] {
OOQL.CreateConstants("", "barcode_no"),
Formulas.IsNull(OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_CODE"), OOQL.CreateConstants(string.Empty), "warehouse_no"),
Formulas.IsNull(OOQL.CreateProperty("BIN.BIN_CODE"), OOQL.CreateConstants(string.Empty), "storage_spaces_no"),
Formulas.IsNull(OOQL.CreateProperty("ITEM_LOT.LOT_CODE"), OOQL.CreateConstants(string.Empty), "lot_no"),
Formulas.IsNull(OOQL.CreateProperty("ITEM_WAREHOUSE_BIN.LAST_RECEIPT_DATE"), OOQL.CreateConstants(OrmDataOption.EmptyDateTime.Date), "first_storage_date"),
Formulas.IsNull(OOQL.CreateProperty("ITEM_WAREHOUSE_BIN.INVENTORY_QTY"), OOQL.CreateConstants(0), "inventory_qty"),
Formulas.IsNull(Formulas.Ext("UNIT_CONVERT", new object[] { OOQL.CreateProperty("QuerySource.ITEM_ID")
, OOQL.CreateProperty("QuerySource.STOCK_UNIT_ID")
, OOQL.CreateProperty("ITEM_WAREHOUSE_BIN.INVENTORY_QTY")
, OOQL.CreateProperty("QuerySource.UNIT_ID")
, OOQL.CreateConstants(0) }), OOQL.CreateConstants(0), "conversion_qty"),
Formulas.RowNumber("sort_no", OOQL.Over(new QueryProperty[] { OOQL.CreateProperty("QuerySource.ITEM_CODE") },
orderByItem
))
});
node = OOQL.Select(
pubProperties
)
.From(node, "QuerySource")
.LeftJoin("ITEM_WAREHOUSE_BIN")
.On(OOQL.CreateProperty("QuerySource.ITEM_ID") == OOQL.CreateProperty("ITEM_WAREHOUSE_BIN.ITEM_ID")
& Formulas.IsNull(OOQL.CreateProperty("QuerySource.ITEM_FEATURE_ID"), OOQL.CreateConstants(Maths.GuidDefaultValue())) == OOQL.CreateProperty("ITEM_WAREHOUSE_BIN.ITEM_FEATURE_ID"))
.LeftJoin("WAREHOUSE")
.On(OOQL.CreateProperty("ITEM_WAREHOUSE_BIN.WAREHOUSE_ID") == OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_ID"))
.LeftJoin("WAREHOUSE.BIN", "BIN")
.On(OOQL.CreateProperty("ITEM_WAREHOUSE_BIN.BIN_ID") == OOQL.CreateProperty("BIN.BIN_ID"))
.LeftJoin("ITEM_LOT")
.On(OOQL.CreateProperty("ITEM_WAREHOUSE_BIN.ITEM_LOT_ID") == OOQL.CreateProperty("ITEM_LOT.ITEM_LOT_ID"))
.LeftJoin("PLANT") //20170719 add by shenbao for P001-170717001
.On(OOQL.CreateProperty("WAREHOUSE.Owner_Org.ROid") == OOQL.CreateProperty("PLANT.PLANT_ID"))
.Where(((OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_CODE") == OOQL.CreateConstants(warehouse_no))
| OOQL.CreateConstants(warehouse_no) == OOQL.CreateConstants(""))
& OOQL.CreateProperty("ITEM_WAREHOUSE_BIN.INVENTORY_QTY") > OOQL.CreateConstants(0)
& OOQL.CreateProperty("ITEM_WAREHOUSE_BIN.BO_ID.RTK") == OOQL.CreateConstants("OTHER")
& OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_PROPERTY") == OOQL.CreateConstants("1") //20170717 add by shenbao for P001-170717001
& OOQL.CreateProperty("WAREHOUSE.INCLUDED_AVAILABLE_QTY") == OOQL.CreateConstants(true) //20170717 add by shenbao for P001-170717001
& OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(site_no)); //20170719 add by shenbao for P001-170717001
}
return(node);
}
/// <summary>
/// 更新领退料单
/// </summary>
/// <param name="employeeNo">扫描人员</param>
/// <param name="scanType">扫描类型 1.有箱条码 2.无箱条码</param>
/// <param name="reportDatetime">上传时间</param>
/// <param name="pickingDepartmentNo">领料部门</param>
/// <param name="recommendedOperations">建议执行作业</param>
/// <param name="recommendedFunction">A.新增 S.过帐</param>
/// <param name="scanDocNo">扫描单号</param>
/// <param name="collection">接口传入的领料单单身数据集合</param>
public DependencyObjectCollection UpdateIssueReceipt(string employeeNo, string scanType, DateTime reportDatetime, string pickingDepartmentNo
, string recommendedOperations, string recommendedFunction, string scanDocNo, DependencyObjectCollection collection)
{
DependencyObjectCollection rtnColl = CreateReturnCollection();
#region 参数检查
if (Maths.IsEmpty(recommendedOperations))
{
throw new BusinessRuleException(EncodeSrv.GetMessage("A111201", new object[] { "recommended_operations" }));
}
if (Maths.IsEmpty(recommendedFunction))
{
throw new BusinessRuleException(EncodeSrv.GetMessage("A111201", new object[] { "recommended_function" }));
}
#endregion
//创建临时表需要的DataTable和Mapping信息
DataTable issueReceiptD = null;
List <BulkCopyColumnMapping> issueReceiptDMap = null;
this.CreateRelateTable(ref issueReceiptD,
ref issueReceiptDMap);
List <string> docNos = new List <string>(); //记录单身的单号，保存时需要，可以避免后续再查
//组织数据BulkCopy需要的DataTable数据
InsertDataTable(issueReceiptD, collection, docNos);
#region 更新逻辑
using (ITransactionService trans = this.GetService <ITransactionService>()) {
IQueryService querySrv = this.GetService <IQueryService>();
//新增临时表
IDataEntityType issueReceiptDTmp = CreateIssueReceiptDTmpTable(querySrv);
//批量新增到临时表
querySrv.BulkCopy(issueReceiptD, issueReceiptDTmp.Name, issueReceiptDMap.ToArray());
if (issueReceiptD.Rows.Count <= 00 || issueReceiptD.Rows.Count <= 0) //没有数据值不再往下执行
{
return(rtnColl);
}
DataRow[] drs = issueReceiptD.Select("BARCODE_NO<>\'\'");
//利用临时表批量更新相关数据
//20170905 add by wangyq for P001-170717001 =================begin===================
if (recommendedOperations == "7-5")
{
UpdateBcCheckStatus(querySrv, issueReceiptDTmp);
}
else
{
//20170905 add by wangyq for P001-170717001 =================end===================
UpdateIssueReceiptD(querySrv, issueReceiptDTmp);
}//20170905 add by wangyq for P001-170717001
if (drs.Length > 0) //条码不为""的记录大于0时，才执行如下更新，避免多余的性能损失
{
bool bcLintFlag = UtilsClass.IsBCLineManagement(querySrv);
if (bcLintFlag)
{
DeleteBCLine(querySrv, issueReceiptDTmp); //先删除
InsertBCLine(querySrv, issueReceiptDTmp); //再重新生成
}
}
//保存
DependencyObjectCollection ids = GetIssueReceipt(docNos);
IReadService readSrv = this.GetService <IReadService>("ISSUE_RECEIPT");
object[] entities = readSrv.Read(ids.Select(c => c["ISSUE_RECEIPT_ID"]).ToArray());
if (entities != null && entities.Length > 0)
{
ISaveService saveSrv = this.GetService <ISaveService>("ISSUE_RECEIPT");
saveSrv.Save(entities);
}
//保存时没有自动审核的，需要重新审核
entities = readSrv.Read(ids.Where(c => !c["AUTO_APPROVE"].ToBoolean()).Select(c => c["ISSUE_RECEIPT_ID"]).ToArray());
IConfirmService confirmService = this.GetService <IConfirmService>("ISSUE_RECEIPT");
ILogOnService logOnSrv = this.GetService <ILogOnService>();
foreach (DependencyObject obj in entities)
{
ConfirmContext context = new ConfirmContext(obj.Oid, logOnSrv.CurrentUserId, reportDatetime.ToDate());
confirmService.Execute(context);
}
trans.Complete();
}
#endregion
#region 组织返回结果
foreach (string item in docNos)
{
DependencyObject obj = rtnColl.AddNew();
obj["doc_no"] = item;
}
#endregion
return(rtnColl);
}
public DependencyObjectCollection UpdateSalesIssue(string employeeNo, string scanType, DateTime reportDatetime, string pickingDepartmentNo
, string recommendedOperations, string recommendedFunction, string scanDocNo, DependencyObjectCollection collection)
{
DependencyObjectCollection rtnColl = CreateReturnCollection();
#region 参数检查
IInfoEncodeContainer encodeSrv = this.GetService <IInfoEncodeContainer>();
if (Maths.IsEmpty(recommendedOperations))
{
throw new BusinessRuleException(encodeSrv.GetMessage("A111201", new object[] { "recommended_operations" }));
}
if (Maths.IsEmpty(recommendedFunction))
{
throw new BusinessRuleException(encodeSrv.GetMessage("A111201", new object[] { "recommended_function" }));
}
#endregion
//创建临时表需要的DataTable和Mapping信息
DataTable dtEntityD = new DataTable();
List <BulkCopyColumnMapping> entityDMap = new List <BulkCopyColumnMapping>();
CreateRelateTable(dtEntityD, entityDMap);
List <string> docNos = new List <string>(); //记录单身的单号，保存时需要，可以避免后续再查
//组织数据BulkCopy需要的DataTable数据
InsertDataTable(dtEntityD, collection, docNos);
if (dtEntityD.Rows.Count <= 0)
{
return(rtnColl);
}
#region 更新逻辑
using (ITransactionService trans = this.GetService <ITransactionService>()) {
using (IConnectionService connService = this.GetService <IConnectionService>()) {
IQueryService querySrv = this.GetService <IQueryService>();
//新增临时表
IDataEntityType dtTemp = CreateDTmpTable(querySrv);
//批量新增到临时表
querySrv.BulkCopy(dtEntityD, dtTemp.Name, entityDMap.ToArray());
//利用临时表批量更新相关数据
UpdateSalesIssueD(querySrv, dtTemp);
DataRow[] drs = dtEntityD.Select("BARCODE_NO<>\'\'");
if (drs.Length > 0) //条码不为""的记录大于0时，才执行如下更新，避免多余的性能损失
{
ICreateService createService = this.GetService <ICreateService>("PARA_FIL");
if (createService != null) //PARA_FIL实体存在,服务端可以这么判断
{
bool bcLintFlag = UtilsClass.IsBCLineManagement(querySrv);
if (bcLintFlag)
{
DeleteBCLine(querySrv, dtTemp); //先删除
InsertBCLine(querySrv, dtTemp); //再重新生成
}
}
}
}
//保存
DependencyObjectCollection ids = GetSalesIssue(docNos);
IReadService readSrv = this.GetService <IReadService>("SALES_ISSUE");
object[] entities = readSrv.Read(ids.Select(c => c["SALES_ISSUE_ID"]).ToArray());
if (entities != null && entities.Length > 0)
{
ISaveService saveSrv = this.GetService <ISaveService>("SALES_ISSUE");
saveSrv.Save(entities);
}
//保存时没有自动审核的，需要重新审核
entities = readSrv.Read(ids.Where(c => !c["AUTO_APPROVE"].ToBoolean()).Select(c => c["SALES_ISSUE_ID"]).ToArray());
IConfirmService confirmService = this.GetService <IConfirmService>("SALES_ISSUE");
ILogOnService logOnSrv = this.GetService <ILogOnService>();
foreach (DependencyObject obj in entities)
{
ConfirmContext context = new ConfirmContext(obj.Oid, logOnSrv.CurrentUserId, reportDatetime.ToDate());
confirmService.Execute(context);
}
trans.Complete();
}
#endregion
#region 组织返回结果
foreach (string item in docNos)
{
DependencyObject obj = rtnColl.AddNew();
obj["doc_no"] = item;
}
#endregion
return(rtnColl);
}
/// <summary>
/// 获取领料出库单查询信息
/// </summary>
/// <param name="siteNo">工厂编号</param>
/// <param name="programJobNo">作业编号</param>
/// <returns></returns>
private QueryNode GetIssueReceiptQueryNode(string siteNo, string programJobNo
, DependencyObjectCollection condition//20170328 add by wangyq for P001-170327001
)
{
QueryNode queryNode =
OOQL.Select(true, OOQL.CreateProperty("ISSUE_RECEIPT.DOC_NO", "doc_no"),//20170925 modi by wangyq for P001-170717001 添加distinct因为后面关联加了单身
OOQL.CreateProperty("ISSUE_RECEIPT.DOC_DATE", "create_date"),
OOQL.CreateProperty("PLANT.PLANT_CODE", "main_organization"),
Formulas.Case(null,
OOQL.CreateProperty("SUPPLIER.SUPPLIER_NAME"),
OOQL.CreateCaseArray(OOQL.CreateCaseItem((OOQL.CreateProperty("ISSUE_RECEIPT.SOURCE_ID.RTK") == OOQL.CreateConstants("WORK_CENTER")),
OOQL.CreateProperty("WORK_CENTER.WORK_CENTER_NAME"))),
"customer_name"),
OOQL.CreateConstants(programJobNo, "program_job_no"),
Formulas.IsNull(OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_NAME"), OOQL.CreateConstants(string.Empty), "employee_name"))
.From("ISSUE_RECEIPT", "ISSUE_RECEIPT")
//20170925 add by wangyq for P001-170717001 ================begin==============
.InnerJoin("ISSUE_RECEIPT.ISSUE_RECEIPT_D", "ISSUE_RECEIPT_D")
.On(OOQL.CreateProperty("ISSUE_RECEIPT.ISSUE_RECEIPT_ID") == OOQL.CreateProperty("ISSUE_RECEIPT_D.ISSUE_RECEIPT_ID"))
//20170925 add by wangyq for P001-170717001 ================end==============
.LeftJoin("PLANT", "PLANT")
.On(OOQL.CreateProperty("PLANT.PLANT_ID") == OOQL.CreateProperty("ISSUE_RECEIPT.Owner_Org.ROid"))
.LeftJoin("WORK_CENTER", "WORK_CENTER")
.On(OOQL.CreateProperty("WORK_CENTER.WORK_CENTER_ID") == OOQL.CreateProperty("ISSUE_RECEIPT.SOURCE_ID.ROid"))
.LeftJoin("SUPPLIER", "SUPPLIER")
.On(OOQL.CreateProperty("SUPPLIER.SUPPLIER_ID") == OOQL.CreateProperty("ISSUE_RECEIPT.SOURCE_ID.ROid"))
.LeftJoin("EMPLOYEE", "EMPLOYEE")
.On(OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_ID") == OOQL.CreateProperty("ISSUE_RECEIPT.Owner_Emp"))
//20170328 add by wangyq for P001-170327001 ================begin==============
.LeftJoin("ADMIN_UNIT", "ADMIN_UNIT")
.On(OOQL.CreateProperty("ISSUE_RECEIPT.Owner_Dept") == OOQL.CreateProperty("ADMIN_UNIT.ADMIN_UNIT_ID"));
//20170328 add by wangyq for P001-170327001 ================end==============
//.Where((OOQL.AuthFilter("ISSUE_RECEIPT", "ISSUE_RECEIPT")) &//20170328 mark by wangyq for P001-170327001
//20170925 modi by wangyq for P001-170717001 =============begin===============
//QueryConditionGroup group =//20170328 add by wangyq for P001-170327001
// (OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo)) &
// //(OOQL.CreateProperty("ISSUE_RECEIPT.ApproveStatus") == OOQL.CreateConstants("N")) &//20170925 mark by wangyq for P001-170717001
// ((OOQL.CreateConstants(programJobNo) == OOQL.CreateConstants("7") & OOQL.CreateProperty("ISSUE_RECEIPT.CATEGORY") == OOQL.CreateConstants("56")) |
// (OOQL.CreateConstants(programJobNo) == OOQL.CreateConstants("8") & OOQL.CreateProperty("ISSUE_RECEIPT.CATEGORY") == OOQL.CreateConstants("57")));
////);//20170328 mark by wangyq for P001-170327001
QueryConditionGroup group = (OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo)) &
OOQL.CreateProperty("ISSUE_RECEIPT.ApproveStatus") == OOQL.CreateConstants("N");
switch (programJobNo)
{
case "7-5":
group &= OOQL.CreateProperty("ISSUE_RECEIPT_D.BC_CHECK_STATUS") == OOQL.CreateConstants("1")
& OOQL.CreateProperty("ISSUE_RECEIPT.CATEGORY") == OOQL.CreateConstants("56");
break;
case "7":
group &= OOQL.CreateProperty("ISSUE_RECEIPT.CATEGORY") == OOQL.CreateConstants("56");
break;
case "8":
group &= OOQL.CreateProperty("ISSUE_RECEIPT.CATEGORY") == OOQL.CreateConstants("57");
break;
default:
break;
}
//20170925 modi by wangyq for P001-170717001 =============end===============
//20170328 add by wangyq for P001-170327001 ================begin==============
if (condition != null)
{
ConditionPropertyNameEntity conPropertyEntity = new ConditionPropertyNameEntity("ISSUE_RECEIPT.DOC_NO", "ISSUE_RECEIPT.DOC_DATE", new string[] { "1", "2", "3", "4", "5" });
group = UtilsClass.CreateNewConditionByParameter(group, condition, conPropertyEntity);
}
queryNode = ((JoinOnNode)queryNode).Where(OOQL.AuthFilter("ISSUE_RECEIPT", "ISSUE_RECEIPT") & (group));
//20170328 add by wangyq for P001-170327001 ================end==============
return(queryNode);
}