public QueryNode GetFILPurchaseArrivalListNode(string programJobNo, DependencyObjectCollection condition)
{
QueryConditionGroup group = (OOQL.AuthFilter("FIL_ARRIVAL", "FIL_ARRIVAL"))
& (OOQL.CreateProperty("FIL_ARRIVAL.STATUS") == OOQL.CreateConstants("2"));
if (condition != null)
{
ConditionPropertyNameEntity conPropertyEntity = new ConditionPropertyNameEntity("FIL_ARRIVAL.DOC_NO", "FIL_ARRIVAL.DOC_DATE", new string[] { "1", "2", "5" });
group = UtilsClass.CreateNewConditionByParameter(group, condition, conPropertyEntity);
}
QueryNode node = OOQL.Select(true,
OOQL.CreateProperty("FIL_ARRIVAL.DOC_NO", "doc_no"),
OOQL.CreateProperty("FIL_ARRIVAL.DOC_DATE", "create_date"),
OOQL.CreateProperty("PURCHASE_ORDER.SUPPLIER_FULL_NAME", "customer_name"),
OOQL.CreateConstants(programJobNo, "program_job_no"),
OOQL.CreateConstants(string.Empty, "employee_name"))
.From("FIL_ARRIVAL", "FIL_ARRIVAL")
.LeftJoin("FIL_ARRIVAL.FIL_ARRIVAL_D", "FIL_ARRIVAL_D")
.On(OOQL.CreateProperty("FIL_ARRIVAL_D.FIL_ARRIVAL_ID") == OOQL.CreateProperty("FIL_ARRIVAL.FIL_ARRIVAL_ID"))
.LeftJoin("PURCHASE_ORDER", "PURCHASE_ORDER")
.On(OOQL.CreateProperty("FIL_ARRIVAL_D.ORDER_NO") == OOQL.CreateProperty("PURCHASE_ORDER.DOC_NO"))
.LeftJoin("SUPPLIER", "SUPPLIER")
.On(OOQL.CreateProperty("PURCHASE_ORDER.SUPPLIER_ID") == OOQL.CreateProperty("SUPPLIER.SUPPLIER_ID"))
.Where(group);
return(node);
}
/// <summary>
/// 获取qc_list数据
/// </summary>
/// <param name="plantId"></param>
/// <returns></returns>
private DependencyObjectCollection GetQcList(object plantId)
{
QueryNode node =
OOQL.Select(
OOQL.CreateProperty("INSPECTION_PLAN_D.SEQUENCE", "qc_seq"),
Formulas.IsNull(OOQL.CreateProperty("INSPECTION_ITEM.INSPECTION_ITEM_ID"),
OOQL.CreateConstants(Maths.GuidDefaultValue()), "INSPECTION_ITEM_ID"),
Formulas.IsNull(OOQL.CreateProperty("INSPECTION_ITEM.INSPECTION_ITEM_CODE"),
OOQL.CreateConstants(string.Empty), "test_no"),
Formulas.IsNull(OOQL.CreateProperty("INSPECTION_ITEM.INSPECTION_ITEM_NAME"),
OOQL.CreateConstants(string.Empty), "test_name"),
OOQL.CreateProperty("INSPECTION_PLAN_D.DEFECT_CLASS", "defect_level"),
OOQL.CreateConstants(0, GeneralDBType.Decimal, "reject_qty"),
OOQL.CreateConstants(0, GeneralDBType.Decimal, "test_qty"),
OOQL.CreateConstants(0, GeneralDBType.Decimal, "reason_qty"),
OOQL.CreateConstants(0, GeneralDBType.Decimal, "return_qty"),
OOQL.CreateConstants(0, GeneralDBType.Decimal, "acceptable_qty"), //20170801 add by liwei1 for P001-170717001
OOQL.CreateConstants(0, GeneralDBType.Decimal, "rejected_qty"), //20170801 add by liwei1 for P001-170717001
OOQL.CreateConstants(0, GeneralDBType.Decimal, "measure_max"),
OOQL.CreateConstants(0, GeneralDBType.Decimal, "measure_min"),
OOQL.CreateConstants("Y", GeneralDBType.String, "result_type"))
.From("INSPECTION_PLAN", "INSPECTION_PLAN")
.InnerJoin("INSPECTION_PLAN.INSPECTION_PLAN_D", "INSPECTION_PLAN_D")
.On((OOQL.CreateProperty("INSPECTION_PLAN.INSPECTION_PLAN_ID") == OOQL.CreateProperty("INSPECTION_PLAN_D.INSPECTION_PLAN_ID")))
.LeftJoin("INSPECTION_ITEM", "INSPECTION_ITEM")
.On((OOQL.CreateProperty("INSPECTION_PLAN_D.INSPECTION_ITEM_ID") == OOQL.CreateProperty("INSPECTION_ITEM.INSPECTION_ITEM_ID")))
.Where(OOQL.AuthFilter("INSPECTION_PLAN", "INSPECTION_PLAN")
& (OOQL.CreateProperty("INSPECTION_PLAN.INSPECTION_PLAN_ID") == OOQL.CreateConstants(plantId)));
return(GetService <IQueryService>().ExecuteDependencyObject(node));
}
//20170329 add by wangrm for P001-170316001======start=======
private DependencyObjectCollection QueryBcFrozen(string scanBarcode)
{
QueryNode node = OOQL.Select(OOQL.CreateProperty("BC_RECORD.BARCODE_NO", "barcode_no"),
OOQL.CreateProperty("ITEM.ITEM_CODE", "item_no"),
OOQL.CreateProperty("ITEM.ITEM_NAME", "item_name"),
OOQL.CreateProperty("ITEM.ITEM_SPECIFICATION", "item_spec"),
OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_CODE", "item_feature_no"),
OOQL.CreateProperty("ITEM_FEATURE.ITEM_SPECIFICATION", "item_feature_name"),
Formulas.Case(null, OOQL.CreateConstants("N"), OOQL.CreateCaseArray(
OOQL.CreateCaseItem(((OOQL.CreateProperty("BC_RECORD.FROZEN_STATUS") != OOQL.CreateEmptyConstants())
& (OOQL.CreateProperty("BC_RECORD.FROZEN_STATUS") != OOQL.CreateNullConstant())),
OOQL.CreateProperty("BC_RECORD.FROZEN_STATUS"))), "frozen"),
OOQL.CreateConstants(string.Empty, "warehouse_no"),
OOQL.CreateConstants(string.Empty, "storage_spaces_no"),
OOQL.CreateConstants(string.Empty, "lot_no"),
OOQL.CreateConstants(0m, "inventory_qty"),
OOQL.CreateConstants(string.Empty, "inventory_unit"))
.From("BC_RECORD", "BC_RECORD")
.LeftJoin("ITEM", "ITEM")
.On(OOQL.CreateProperty("BC_RECORD.ITEM_ID") == OOQL.CreateProperty("ITEM.ITEM_ID"))
.LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE")
.On(OOQL.CreateProperty("BC_RECORD.ITEM_FEATURE_ID") == OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID"))
.Where(OOQL.CreateProperty("BC_RECORD.BARCODE_NO") == OOQL.CreateConstants(scanBarcode));
return(GetService <IQueryService>().ExecuteDependencyObject(node));
}
//20170209 add by liwei1 for P001-170203001 ===begin===
/// <summary>
/// 查询单据性质ID
/// </summary>
/// <param name="category">单据性质</param>
/// <param name="scanColl">单身数据集</param>
/// <returns>单据性质ID</returns>
private object QueryDocId(string category, string recommendedOperations, DependencyObjectCollection scanColl)
{
QueryNode node = null;
if (scanColl.Count > 0)
{
string infoLotNo = scanColl[0]["info_lot_no"].ToStringExtension(); //信息批号
string siteNo = scanColl[0]["site_no"].ToStringExtension(); //工厂
node = OOQL.Select(1, OOQL.CreateProperty("MO.DOC_ID"))
.From("MO", "MO")
.Where((OOQL.AuthFilter("MO", "MO"))
& (OOQL.CreateProperty("MO.DOC_NO") == OOQL.CreateConstants(infoLotNo)));
//根据条件查询满足条件的DOC_ID
node = OOQL.Select(1, OOQL.CreateProperty("PARA_DOC_FIL.DOC_ID"))
.From("PARA_DOC_FIL", "PARA_DOC_FIL")
.InnerJoin("PLANT", "PLANT")
.On((OOQL.CreateProperty("PLANT.PLANT_ID") == OOQL.CreateProperty("PARA_DOC_FIL.Owner_Org.ROid")))
.Where((OOQL.AuthFilter("PARA_DOC_FIL", "PARA_DOC_FIL"))
& ((OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo))
& (OOQL.CreateProperty("PARA_DOC_FIL.CATEGORY") == OOQL.CreateConstants(category))
& ((OOQL.CreateProperty("PARA_DOC_FIL.SOURCE_DOC_ID") == OOQL.CreateConstants(Maths.GuidDefaultValue()))
| (OOQL.CreateProperty("PARA_DOC_FIL.SOURCE_DOC_ID") == node))))
.OrderBy(
OOQL.CreateOrderByItem(
OOQL.CreateProperty("PARA_DOC_FIL.SOURCE_DOC_ID"), SortType.Desc));
}
return(GetService <IQueryService>().ExecuteScalar(node));
}
/// <summary>
/// 根据品号获取品号工厂信息
/// </summary>
/// <param name="itemID">品号ID集合</param>
/// <param name="sitNo">工厂ID</param>
/// <returns></returns>
private DependencyObjectCollection GetItemForPlant(DependencyObjectCollection itemID, string siteNo, string programJobNo)
{
List <QueryProperty> properties = GetQueryProperty(siteNo);
properties.Add(Formulas.IsNull(Formulas.Case(null, OOQL.CreateProperty("ITEM_PLANT.RECEIPT_OVERRUN_RATE"), new CaseItem[] {
new CaseItem(OOQL.CreateConstants(programJobNo).Like(OOQL.CreateConstants("7%"))
, OOQL.CreateProperty("ITEM_PLANT.ISSUE_OVERRUN_RATE")),
new CaseItem(OOQL.CreateConstants(programJobNo).Like(OOQL.CreateConstants("8%"))
, OOQL.CreateProperty("ITEM_PLANT.ISSUE_SHORTAGE_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_PLANT")
.On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("ITEM_PLANT.ITEM_ID"))
.LeftJoin("PLANT")
.On(OOQL.CreateProperty("ITEM_PLANT.Owner_Org.ROid") == OOQL.CreateProperty("PLANT.PLANT_ID"))
.Where(OOQL.AuthFilter("ITEM", "ITEM")
& OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo)
& 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>
/// <param name="reasonCode">入参理由码</param>
/// <param name="siteNo"></param>
/// <returns></returns>
private DependencyObjectCollection GetDefectiveReasons(string reasonCode,string siteNo)
{
//拼接where条件
QueryConditionGroup conditionGroup = (OOQL.AuthFilter("DEFECTIVE_REASONS", "DEFECTIVE_REASONS"))
& (OOQL.CreateProperty("DEFECTIVE_REASONS.DEFECTIVE_TYPE").In(
OOQL.CreateConstants("1"),
OOQL.CreateConstants("2"),
OOQL.CreateConstants("6")))
& (OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo));
//IF 【入参理由码reason_code】不为空 THEN
if (!Maths.IsEmpty(reasonCode)){
conditionGroup &= (OOQL.CreateProperty("DEFECTIVE_REASONS.DEFECTIVE_REASONS_CODE") ==OOQL.CreateConstants(reasonCode));
}
//组合node
QueryNode node =
OOQL.Select(
OOQL.CreateProperty("DEFECTIVE_REASONS.DEFECTIVE_REASONS_CODE", "reason_code"),
OOQL.CreateProperty("DEFECTIVE_REASONS.DESCRIPTION", "reason_code_name"))
.From("DEFECTIVE_REASONS", "DEFECTIVE_REASONS")
.InnerJoin("PLANT","PLANT")
.On(OOQL.CreateProperty("DEFECTIVE_REASONS.Owner_Org.ROid") == OOQL.CreateProperty("PLANT.PLANT_ID"))
.Where(conditionGroup);
//查询数据并返回
return GetService<IQueryService>().ExecuteDependencyObject(node);
}
/// <summary>
/// 根据传入的工厂编号，查询工厂的其他相关信息
/// </summary>
/// <param name="siteNo">门店编号</param>
/// <returns></returns>
private QueryNode GetSiteDetail(string siteNo)
{
QueryNode node = null;
if (!Maths.IsEmpty(siteNo))
{
node = OOQL.Select(
OOQL.CreateProperty("PLANT.PLANT_CODE", "site_no"),
OOQL.CreateProperty("PLANT.PLANT_NAME", "site_name"),
OOQL.CreateProperty("PLANT.ADDRESS", "address"),
OOQL.CreateProperty("PLANT.TELEPHONE", "telephone"))
.From("PLANT", "PLANT")
.Where(OOQL.AuthFilter("PLANT", "PLANT")
& (OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo)));
}
else
{
node = OOQL.Select(
OOQL.CreateProperty("PLANT.PLANT_CODE", "site_no"),
OOQL.CreateProperty("PLANT.PLANT_NAME", "site_name"),
OOQL.CreateProperty("PLANT.ADDRESS", "address"),
OOQL.CreateProperty("PLANT.TELEPHONE", "telephone"))
.From("PLANT", "PLANT")
.Where(OOQL.AuthFilter("PLANT", "PLANT"));
}
return(node);
}
private void TextOrm()
{
//_hepler.VeryfiyRepair();
DependencyObject entity = (DependencyObject)this._helper.Create(Product.DefaultType);
entity[0] = Guid.NewGuid().ToString();
entity[1] = "001";
entity[2] = "CPU";
//this._helper.Save(entity);
QueryNode node = OOQL.Select("CODE", "NAME")
.From("Product")
.Where(OOQL.CreateProperty("ID") == OOQL.CreateConstants("3DE1E070-241C-42BE-8704-AC46140CCE09"));
IQueryBuilder builder = OrmEntry.CreateQuery(_helper.DBSource, _helper.DBDriver);
string str = builder.Select("Product", new string[] { "CODE", "NAME" }, null, null);
Digiwin.Common.Torridity.Query.QueryEntity queryEn = new Digiwin.Common.Torridity.Query.QueryEntity("Product");
//DataSet ds = builder.ExecuteSelect(new Digiwin.Common.Torridity.Query.QueryEntity[] { queryEn });
Product p = new Product(((DependencyObject)Product.DefaultType.CreateInstance()));
p.ID = Guid.NewGuid();
p.CODE = "222";
this._helper.Save(p.DependencyObject);
}
/// <summary>
/// 单头新增查询
/// </summary>
/// <param name="report_datetime"></param>
/// <param name="category"></param>
/// <returns></returns>
private DataTable QueryForMoReceiptReq(DateTime report_datetime, string category, string recommended_operations, object docId) //20170104 modi by wangyq for P001-161215001 添加参数recommended_operations //20170209 modi by liwei1 for P001-170124001 添加参数：docId
{
QueryNode node = OOQL.Select(true,
Formulas.Cast(OOQL.CreateConstants(docId), GeneralDBType.Guid, "DOC_ID"),//20170209 add by liwei1 for P001-170124001
OOQL.CreateConstants(report_datetime, GeneralDBType.Date, "DOC_DATE"),
OOQL.CreateConstants(string.Empty, GeneralDBType.String, "DOC_NO"),
OOQL.CreateConstants(report_datetime, GeneralDBType.Date, "TRANSACTION_DATE"),
OOQL.CreateConstants("PLANT", GeneralDBType.String, "Owner_Org_RTK"),
OOQL.CreateProperty("PLANT.PLANT_ID", "Owner_Org_ROid"),
OOQL.CreateProperty("MO.SOURCE_ID.ROid", "WORK_CENTER_ID"),
Formulas.IsNull(OOQL.CreateProperty("ADMIN_UNIT.ADMIN_UNIT_ID"), OOQL.CreateConstants(Maths.GuidDefaultValue()), "Owner_Dept"),//20170328 modi by wangyq for P001-170327001 添加null判断
OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_ID", "Owner_Emp"),
OOQL.CreateProperty("Table_scan.ID", "MO_RECEIPT_REQUISTION_ID"),
OOQL.CreateConstants(false, GeneralDBType.Boolean, "CLOSE"),
OOQL.CreateProperty("DOC.QC_RESULT_INPUT_TYPE", "temp_QC_RESULT_INPUT_TYPE")
)
.From(_Table_scan.Name, "Table_scan")
.InnerJoin("PLANT", "PLANT")
.On(OOQL.CreateProperty("Table_scan.site_no") == OOQL.CreateProperty("PLANT.PLANT_CODE"))
.InnerJoin("EMPLOYEE", "EMPLOYEE")
.On(OOQL.CreateProperty("Table_scan.employee_no") == OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_CODE"))
.LeftJoin("ADMIN_UNIT", "ADMIN_UNIT") //20170328 modi by wangyq for P001-170327001 old:InnerJoin
.On(OOQL.CreateProperty("Table_scan.picking_department_no") == OOQL.CreateProperty("ADMIN_UNIT.ADMIN_UNIT_CODE"))
.InnerJoin("DOC", "DOC")
.On(OOQL.CreateProperty("DOC.DOC_ID") == OOQL.CreateConstants(docId)) //20170209 add by liwei1 for P001-170124001 old:OOQL.CreateProperty("DOC.DOC_ID"))
.InnerJoin(_Table_scan_detail.Name, "Table_scan_detail")
.On(OOQL.CreateProperty("Table_scan.info_lot_no") == OOQL.CreateProperty("Table_scan_detail.info_lot_no"))
.InnerJoin("MO", "MO")
.On(OOQL.CreateProperty("Table_scan_detail.SequenceNumber") == OOQL.CreateConstants(1, GeneralDBType.Int32)
& OOQL.CreateProperty("Table_scan_detail.doc_no") == OOQL.CreateProperty("MO.DOC_NO"));
return(_qurService.Execute(node));
}
/// <summary>
/// 查询到货单信息
/// </summary>
/// <param name="receiptNo"></param>
/// <returns></returns>
private DependencyObjectCollection GetPurchaseArrival(string receiptNo)
{
QueryNode node =
OOQL.Select(
OOQL.CreateProperty("pa.RECEIVE_Owner_Org.ROid", "RECEIVE_Owner_Org_ROid"),
OOQL.CreateProperty("pad.PURCHASE_ARRIVAL_D_ID", "PURCHASE_ARRIVAL_D_ID"),
OOQL.CreateProperty("pad.DEDUCT_ARRIVED_QTY", "DEDUCT_ARRIVED_QTY"),
OOQL.CreateProperty("pad.PURCHASE_TYPE", "PURCHASE_TYPE"),
OOQL.CreateProperty("mrd.INSPECT_MODE", "INSPECT_MODE_MRD"),
OOQL.CreateProperty("ip.INSPECT_MODE", "INSPECT_MODE_IP"),
OOQL.CreateProperty("pa.DOC_NO", "DOC_NO"),
OOQL.CreateProperty("pad.SequenceNumber", "SequenceNumber"))
.From("PURCHASE_ARRIVAL", "pa")
.InnerJoin("PURCHASE_ARRIVAL.PURCHASE_ARRIVAL_D", "pad")
.On((OOQL.CreateProperty("pad.PURCHASE_ARRIVAL_ID") == OOQL.CreateProperty("pa.PURCHASE_ARRIVAL_ID")))
.LeftJoin("ITEM_PLANT", "ip")
.On((OOQL.CreateProperty("ip.ITEM_ID") == OOQL.CreateProperty("pad.ITEM_ID"))
& (OOQL.CreateProperty("ip.Owner_Org.ROid") == OOQL.CreateProperty("pa.RECEIVE_Owner_Org.ROid")))
.LeftJoin("MO_ROUTING.MO_ROUTING_D", "mrd")
.On((OOQL.CreateProperty("mrd.MO_ROUTING_D_ID") == OOQL.CreateProperty("pad.REFERENCE_SOURCE_ID.ROid")))
.Where((OOQL.CreateProperty("pa.DOC_NO") == OOQL.CreateConstants(receiptNo)));
return(GetService <IQueryService>().ExecuteDependencyObject(node));
}
//20170629 add by zhangcn for P001-170606002 ===end===
//20170829 add by shenbao for P001-170717001 ===begin===
/// <summary>
/// 查询寄售调拨单
/// </summary>
/// <param name="site_no"></param>
/// <param name="docNos"></param>
/// <param name="warseHouseControl"></param>
/// <returns></returns>
private QueryNode GetSalesOrderDocNode(string site_no, List <ConstantsQueryProperty> docNos, string warseHouseControl)
{
QueryNode node = OOQL.Select(OOQL.CreateProperty("PLANT.PLANT_CODE"),
OOQL.CreateProperty("ITEM.ITEM_CODE"),
OOQL.CreateProperty("ITEM.ITEM_NAME"),
OOQL.CreateProperty("ITEM.ITEM_SPECIFICATION"),
OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_CODE"),
OOQL.CreateProperty("ITEM_FEATURE.ITEM_SPECIFICATION", "ITEM_FEATURE_SPECIFICATION"),
OOQL.CreateProperty("UNIT.UNIT_CODE"),
OOQL.CreateProperty("STOCK_UNIT.UNIT_CODE", "STOCK_UNIT_CODE"),
OOQL.CreateProperty("ITEM_PLANT.WAREHOUSE_CONTROL"),
OOQL.CreateProperty("SALES_ORDER_DOC_D.ITEM_ID"),
OOQL.CreateProperty("UNIT.UNIT_ID"),
OOQL.CreateProperty("STOCK_UNIT.UNIT_ID", "STOCK_UNIT_ID"),
OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID"),
Formulas.IsNull(Formulas.Sum(OOQL.CreateProperty("SALES_ORDER_DOC_SD.BUSINESS_QTY") - OOQL.CreateProperty("SALES_ORDER_DOC_SD.DELIVERED_BUSINESS_QTY"))
, OOQL.CreateConstants(0), "SOURCE_QTY")
)
.From("SALES_ORDER_DOC", "SALES_ORDER_DOC")
.InnerJoin("SALES_ORDER_DOC.SALES_ORDER_DOC_D", "SALES_ORDER_DOC_D")
.On(OOQL.CreateProperty("SALES_ORDER_DOC.SALES_ORDER_DOC_ID") == OOQL.CreateProperty("SALES_ORDER_DOC_D.SALES_ORDER_DOC_ID"))
.InnerJoin("SALES_ORDER_DOC.SALES_ORDER_DOC_D.SALES_ORDER_DOC_SD", "SALES_ORDER_DOC_SD")
.On(OOQL.CreateProperty("SALES_ORDER_DOC_D.SALES_ORDER_DOC_D_ID") == OOQL.CreateProperty("SALES_ORDER_DOC_SD.SALES_ORDER_DOC_D_ID"))
.InnerJoin("PLANT")
.On(OOQL.CreateProperty("SALES_ORDER_DOC_SD.DELIVERY_PLANT_ID") == OOQL.CreateProperty("PLANT.PLANT_ID"))
.InnerJoin("ITEM")
.On(OOQL.CreateProperty("SALES_ORDER_DOC_D.ITEM_ID") == OOQL.CreateProperty("ITEM.ITEM_ID"))
.LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE")
.On(OOQL.CreateProperty("SALES_ORDER_DOC_D.ITEM_FEATURE_ID") == OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID"))
.LeftJoin("UNIT")
.On(OOQL.CreateProperty("SALES_ORDER_DOC_D.BUSINESS_UNIT_ID") == OOQL.CreateProperty("UNIT.UNIT_ID"))
.LeftJoin("UNIT", "STOCK_UNIT")
.On(OOQL.CreateProperty("ITEM.STOCK_UNIT_ID") == OOQL.CreateProperty("STOCK_UNIT.UNIT_ID"))
.LeftJoin("ITEM_PLANT")
.On(OOQL.CreateProperty("SALES_ORDER_DOC_D.ITEM_ID") == OOQL.CreateProperty("ITEM_PLANT.ITEM_ID")
& OOQL.CreateProperty("SALES_ORDER_DOC_SD.DELIVERY_PLANT_ID") == OOQL.CreateProperty("ITEM_PLANT.Owner_Org.ROid"))
.Where((OOQL.AuthFilter("SALES_ORDER_DOC", "SALES_ORDER_DOC"))
& (OOQL.CreateProperty("SALES_ORDER_DOC.DOC_NO").In(docNos.ToArray())
& OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(site_no)
& OOQL.CreateProperty("SALES_ORDER_DOC.ApproveStatus") == OOQL.CreateConstants("Y")
& OOQL.CreateProperty("SALES_ORDER_DOC.CATEGORY") == OOQL.CreateConstants("2B")
& OOQL.CreateProperty("ITEM_PLANT.WAREHOUSE_CONTROL") == OOQL.CreateConstants(warseHouseControl)
))
.GroupBy(OOQL.CreateProperty("PLANT.PLANT_CODE"),
OOQL.CreateProperty("ITEM.ITEM_CODE"),
OOQL.CreateProperty("ITEM.ITEM_NAME"),
OOQL.CreateProperty("ITEM.ITEM_SPECIFICATION"),
OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_CODE"),
OOQL.CreateProperty("ITEM_FEATURE.ITEM_SPECIFICATION"),
OOQL.CreateProperty("UNIT.UNIT_CODE"),
OOQL.CreateProperty("STOCK_UNIT.UNIT_CODE"),
OOQL.CreateProperty("ITEM_PLANT.WAREHOUSE_CONTROL"),
OOQL.CreateProperty("SALES_ORDER_DOC_D.ITEM_ID"),
OOQL.CreateProperty("UNIT.UNIT_ID"),
OOQL.CreateProperty("STOCK_UNIT.UNIT_ID"),
OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID"));
return(node);
}
/// <summary>
/// 获取工单信息查询信息
/// </summary>
/// <param name="siteNo">工厂编号</param>
/// <param name="programJobNo">作业编号</param>
/// <returns></returns>
private QueryNode GetMOQueryNode(string siteNo, string programJobNo
, DependencyObjectCollection condition//20170328 add by wangyq for P001-170327001
)
{
QueryNode queryNode =
OOQL.Select(true, OOQL.CreateProperty("MO.DOC_NO", "doc_no"),
OOQL.CreateProperty("MO.DOC_DATE", "create_date"),
Formulas.Case(null,
OOQL.CreateProperty("SUPPLIER.SUPPLIER_NAME"),
OOQL.CreateCaseArray(OOQL.CreateCaseItem((OOQL.CreateProperty("MO.SOURCE_ID.RTK") == OOQL.CreateConstants("WORK_CENTER")),
OOQL.CreateProperty("WORK_CENTER.WORK_CENTER_NAME"))),
"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("MO", "MO")
.LeftJoin("MO.MO_D", "MO_D")
.On(OOQL.CreateProperty("MO_D.MO_ID") == OOQL.CreateProperty("MO.MO_ID"))
.LeftJoin("PLANT", "PLANT")
.On(OOQL.CreateProperty("PLANT.PLANT_ID") == OOQL.CreateProperty("MO.Owner_Org.ROid"))
.LeftJoin("WORK_CENTER", "WORK_CENTER")
.On(OOQL.CreateProperty("WORK_CENTER.WORK_CENTER_ID") == OOQL.CreateProperty("MO.SOURCE_ID.ROid"))
.LeftJoin("SUPPLIER", "SUPPLIER")
.On(OOQL.CreateProperty("SUPPLIER.SUPPLIER_ID") == OOQL.CreateProperty("MO.SOURCE_ID.ROid"))
.LeftJoin("EMPLOYEE", "EMPLOYEE")
.On(OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_ID") == OOQL.CreateProperty("MO.Owner_Emp"))
.LeftJoin("DOC", "DOC")
.On(OOQL.CreateProperty("DOC.DOC_ID") == OOQL.CreateProperty("MO.DOC_ID"))
//20170328 add by wangyq for P001-170327001 ================begin==============
.LeftJoin("ADMIN_UNIT", "ADMIN_UNIT")
.On(OOQL.CreateProperty("MO.Owner_Dept") == OOQL.CreateProperty("ADMIN_UNIT.ADMIN_UNIT_ID"));
//20170328 add by wangyq for P001-170327001 ================end==============
//.Where(//20170328 mark by wangyq for P001-170327001
QueryConditionGroup group = //20170328 add by wangyq for P001-170327001
//(OOQL.AuthFilter("MO", "MO")) & (Where(//20170328 mark by wangyq for P001-170327001
(OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo)) &
(OOQL.CreateProperty("MO.ApproveStatus") == OOQL.CreateConstants("Y")) &
(OOQL.CreateProperty("DOC.CATEGORY").In(OOQL.CreateConstants("51"),
OOQL.CreateConstants("52"),
OOQL.CreateConstants("53"),
OOQL.CreateConstants("54"))) &
(OOQL.CreateProperty("MO.LOT_MO_FLAG") == OOQL.CreateConstants(false)) &
(OOQL.CreateProperty("MO.STATUS").In(OOQL.CreateConstants("1"),
OOQL.CreateConstants("2"),
OOQL.CreateConstants("3"))) &
((OOQL.CreateConstants(programJobNo) == OOQL.CreateConstants("7") & OOQL.CreateProperty("MO_D.REQUIRED_QTY") > OOQL.CreateProperty("MO_D.ISSUED_QTY")) |
(OOQL.CreateConstants(programJobNo) == OOQL.CreateConstants("8") & OOQL.CreateProperty("MO_D.ISSUED_QTY") > OOQL.CreateConstants(0)));
//));//20170328 mark by wangyq for P001-170327001
//20170328 add by wangyq for P001-170327001 ================begin==============
if (condition != null)
{
ConditionPropertyNameEntity conPropertyEntity = new ConditionPropertyNameEntity("MO.DOC_NO", "MO.DOC_DATE", new string[] { "1", "2", "3", "4", "5" });
group = UtilsClass.CreateNewConditionByParameter(group, condition, conPropertyEntity);
}
queryNode = ((JoinOnNode)queryNode).Where(OOQL.AuthFilter("MO", "MO") & (group));
//20170328 add by wangyq for P001-170327001 ================end==============
return(queryNode);
}
/// <summary>
/// 根据单号获取最大序号
/// </summary>
/// <param name="countingNo"></param>
/// <returns></returns>
private int GetMaxSeq(string countingNo)
{
QueryNode node = OOQL.Select(Formulas.Max(OOQL.CreateProperty("SequenceNumber"), "SequenceNumber"))
.From("COUNTING_PLAN", "COUNTING_PLAN")
.Where(OOQL.CreateProperty("DOC_NO") == OOQL.CreateConstants(countingNo));
return(this.GetService <IQueryService>().ExecuteScalar(node).ToInt32());
}
//20170629 add by zhangcn for P001-170606002 ===begin===
/// <summary>
/// 查询采购退货单
/// </summary>
/// <param name="site_no"></param>
/// <param name="docNos"></param>
/// <returns></returns>
private QueryNode GePurchaseReturnNode(string site_no, List <ConstantsQueryProperty> docNos, string warseHouseControl)
{
QueryNode node = OOQL.Select(OOQL.CreateProperty("PLANT.PLANT_CODE"),
OOQL.CreateProperty("ITEM.ITEM_CODE"),
OOQL.CreateProperty("ITEM.ITEM_NAME"),
OOQL.CreateProperty("ITEM.ITEM_SPECIFICATION"),
OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_CODE"),
OOQL.CreateProperty("ITEM_FEATURE.ITEM_SPECIFICATION", "ITEM_FEATURE_SPECIFICATION"),
OOQL.CreateProperty("UNIT.UNIT_CODE"),
OOQL.CreateProperty("STOCK_UNIT.UNIT_CODE", "STOCK_UNIT_CODE"),
OOQL.CreateProperty("ITEM_PLANT.WAREHOUSE_CONTROL"),
OOQL.CreateProperty("PURCHASE_RETURN_D.ITEM_ID"),
OOQL.CreateProperty("UNIT.UNIT_ID"),
OOQL.CreateProperty("STOCK_UNIT.UNIT_ID", "STOCK_UNIT_ID"),
OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID"),
Formulas.IsNull(Formulas.Sum(OOQL.CreateProperty("PURCHASE_RETURN_D.BUSINESS_QTY")) -
Formulas.Sum(OOQL.CreateProperty("PURCHASE_RETURN_D.ISSUED_BUSINESS_QTY")),
OOQL.CreateConstants(0), "SOURCE_QTY")
)
.From("PURCHASE_RETURN", "PURCHASE_RETURN")
.InnerJoin("PURCHASE_RETURN.PURCHASE_RETURN_D", "PURCHASE_RETURN_D")
.On(OOQL.CreateProperty("PURCHASE_RETURN.PURCHASE_RETURN_ID") == OOQL.CreateProperty("PURCHASE_RETURN_D.PURCHASE_RETURN_ID"))
.InnerJoin("PLANT")
.On(OOQL.CreateProperty("PURCHASE_RETURN.PLANT_ID") == OOQL.CreateProperty("PLANT.PLANT_ID"))
.InnerJoin("ITEM")
.On(OOQL.CreateProperty("PURCHASE_RETURN_D.ITEM_ID") == OOQL.CreateProperty("ITEM.ITEM_ID"))
.LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE")
.On(OOQL.CreateProperty("PURCHASE_RETURN_D.ITEM_FEATURE_ID") == OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID"))
.LeftJoin("UNIT", "UNIT")
.On(OOQL.CreateProperty("PURCHASE_RETURN_D.BUSINESS_UNIT_ID") == OOQL.CreateProperty("UNIT.UNIT_ID"))
.LeftJoin("UNIT", "STOCK_UNIT")
.On(OOQL.CreateProperty("ITEM.STOCK_UNIT_ID") == OOQL.CreateProperty("STOCK_UNIT.UNIT_ID"))
.LeftJoin("ITEM_PLANT")
.On(OOQL.CreateProperty("PURCHASE_RETURN_D.ITEM_ID") == OOQL.CreateProperty("ITEM_PLANT.ITEM_ID")
& OOQL.CreateProperty("PURCHASE_RETURN.PLANT_ID") == OOQL.CreateProperty("ITEM_PLANT.Owner_Org.ROid"))
.Where((OOQL.AuthFilter("PURCHASE_RETURN", "PURCHASE_RETURN"))
& (OOQL.CreateProperty("PURCHASE_RETURN.DOC_NO").In(docNos.ToArray())
& OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(site_no)
& OOQL.CreateProperty("PURCHASE_RETURN.ApproveStatus") == OOQL.CreateConstants("Y")
& OOQL.CreateProperty("PURCHASE_RETURN.CATEGORY") == OOQL.CreateConstants("39")
& OOQL.CreateProperty("ITEM_PLANT.WAREHOUSE_CONTROL") == OOQL.CreateConstants(warseHouseControl)
))
.GroupBy(OOQL.CreateProperty("PLANT.PLANT_CODE"),
OOQL.CreateProperty("ITEM.ITEM_CODE"),
OOQL.CreateProperty("ITEM.ITEM_NAME"),
OOQL.CreateProperty("ITEM.ITEM_SPECIFICATION"),
OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_CODE"),
OOQL.CreateProperty("ITEM_FEATURE.ITEM_SPECIFICATION"),
OOQL.CreateProperty("UNIT.UNIT_CODE"),
OOQL.CreateProperty("STOCK_UNIT.UNIT_CODE"),
OOQL.CreateProperty("ITEM_PLANT.WAREHOUSE_CONTROL"),
OOQL.CreateProperty("PURCHASE_RETURN_D.ITEM_ID"),
OOQL.CreateProperty("UNIT.UNIT_ID"),
OOQL.CreateProperty("STOCK_UNIT.UNIT_ID"),
OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID"));
return(node);
}
//20170328 add by wangyq for P001-170327001
/// <summary>
///
/// </summary>
/// <param name="group">原来查询的条件,必须传入，避免拼接半天太麻烦</param>
/// <param name="condition"></param>
/// <returns></returns>
public static QueryConditionGroup CreateNewConditionByParameter(QueryConditionGroup group, DependencyObjectCollection condition, ConditionPropertyNameEntity conPropertyEntity)
{
if (condition.ItemDependencyObjectType.Properties.Contains("seq") &&
condition.ItemDependencyObjectType.Properties.Contains("value")) //都没有设置的时候会没有传入这个字段
{
foreach (DependencyObject conObj in condition)
{
string leftName = string.Empty;
bool isEmptyValue;
if (conObj["seq"].ToStringExtension() == "2") //日期类型要转换为日期后判断
{
isEmptyValue = Maths.IsEmpty(conObj["value"].ToDate());
}
else
{
isEmptyValue = Maths.IsEmpty(conObj["value"]);
}
if (!isEmptyValue && conPropertyEntity.ContainProperty.Length > 0 && conPropertyEntity.ContainProperty.Contains(conObj["seq"].ToStringExtension()))
{
switch (conObj["seq"].ToStringExtension())
{
case "1": //单号
leftName = conPropertyEntity.DocNoName;
break;
case "2": //日期
group &= OOQL.CreateProperty(conPropertyEntity.DocDateName) >= OOQL.CreateConstants(conObj["value"].ToDate(), GeneralDBType.Date);
break;
case "3": //人员
leftName = conPropertyEntity.EmployeeName;
break;
case "4": //部门
leftName = conPropertyEntity.AdminUnitName;
break;
case "5": //供应商
leftName = conPropertyEntity.SupplierName;
break;
case "6": //客户
leftName = conPropertyEntity.CustomerName;
break;
default:
break;
}
if (!string.IsNullOrEmpty(leftName))
{
group &= OOQL.CreateProperty(leftName).Like(OOQL.CreateConstants("%" + conObj["value"].ToString() + "%", GeneralDBType.String));
}
}
}
}
return(group);
}
/// <summary>
/// 查询供应商信息
/// </summary>
/// <param name="supplierNo">供应商编号</param>
/// <returns>供应商编号\供应商名称</returns>
private QueryNode GetSupplier(string supplierNo)
{
return(OOQL.Select(
OOQL.CreateProperty("SUPPLIER.SUPPLIER_CODE", "SUPPLIER_CODE"),
OOQL.CreateProperty("SUPPLIER.SUPPLIER_NAME", "SUPPLIER_NAME"))
.From("SUPPLIER", "SUPPLIER")
.Where((OOQL.AuthFilter("SUPPLIER", "SUPPLIER"))
& (OOQL.CreateProperty("SUPPLIER.SUPPLIER_CODE") == OOQL.CreateConstants(supplierNo))));
}
private int QueryMO(string docNo)
{
QueryNode node = OOQL.Select("MO.MO_ID")
.From("MO", "MO")
.Where((OOQL.AuthFilter("MO", "MO"))
& (OOQL.CreateProperty("MO.DOC_NO") == OOQL.CreateConstants(docNo)));
return(this.GetService <IQueryService>().ExecuteDependencyObject(node).Count);
}
public DependencyObjectCollection QueryItemInvQty(object itemId, object itemFeatureId, string itemLotCode, string siteNo, string scanWarehouseNo,//20170331 modi by wangyq for P001-170327001 old:public DependencyObjectCollection QueryItemInvQty(DependencyObject itemInfo, string siteNo, string scan_warehouse_no,
string scanStorageSpacesNo, string showZeroInventory)
{
QueryNode node = OOQL.Select(OOQL.CreateProperty("ITEM.ITEM_CODE", "item_no"),
OOQL.CreateProperty("ITEM.ITEM_NAME", "item_name"),
OOQL.CreateProperty("ITEM.ITEM_SPECIFICATION", "item_spec"),
OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_CODE", "item_feature_no"), //wangrm
OOQL.CreateProperty("ITEM_FEATURE.ITEM_SPECIFICATION", "item_feature_name"), //wangrm
Formulas.IsNull(OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_CODE"), OOQL.CreateConstants(""), "warehouse_no"),
Formulas.IsNull(OOQL.CreateProperty("BIN.BIN_CODE"), OOQL.CreateConstants(""), "storage_spaces_no"),
Formulas.IsNull(OOQL.CreateProperty("ITEM_LOT.LOT_CODE"), OOQL.CreateConstants(""), "lot_no"),
Formulas.IsNull(OOQL.CreateProperty("UNIT.UNIT_CODE"), OOQL.CreateConstants(""), "inventory_unit"),
Formulas.IsNull(Formulas.Sum(OOQL.CreateProperty("ITEM_WAREHOUSE_BIN.INVENTORY_QTY")), OOQL.CreateConstants(0), "inventory_qty")
)
.From("ITEM_WAREHOUSE_BIN", "ITEM_WAREHOUSE_BIN")
.InnerJoin("ITEM")
.On(OOQL.CreateProperty("ITEM_WAREHOUSE_BIN.ITEM_ID") == OOQL.CreateProperty("ITEM.ITEM_ID"))
.LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE")
.On(OOQL.CreateProperty("ITEM_WAREHOUSE_BIN.ITEM_FEATURE_ID") == OOQL.CreateProperty("ITEM_FEATURE.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")
.On(OOQL.CreateProperty("ITEM_WAREHOUSE_BIN.Owner_Org.ROid") == OOQL.CreateProperty("PLANT.PLANT_ID"))
.LeftJoin("UNIT")
.On(OOQL.CreateProperty("ITEM.STOCK_UNIT_ID") == OOQL.CreateProperty("UNIT.UNIT_ID"))
.Where(OOQL.AuthFilter("ITEM_WAREHOUSE_BIN", "ITEM_WAREHOUSE_BIN")
& (OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo)
& (OOQL.CreateProperty("ITEM_WAREHOUSE_BIN.ITEM_ID") == OOQL.CreateConstants(itemId) //20170331 modi by wangyq for P001-170327001 old:itemInfo["ITEM_ID"]
| (OOQL.CreateConstants(itemId) == OOQL.CreateConstants(Maths.GuidDefaultValue()))) //20170331 modi by wangyq for P001-170327001 old:itemInfo["ITEM_ID"]
& (OOQL.CreateProperty("ITEM_WAREHOUSE_BIN.ITEM_FEATURE_ID") == OOQL.CreateConstants(itemFeatureId) //20170331 modi by wangyq for P001-170327001 old:itemInfo["ITEM_FEATURE_ID"]
| (OOQL.CreateConstants(itemFeatureId) == OOQL.CreateConstants(Maths.GuidDefaultValue()))) //20170331 modi by wangyq for P001-170327001 old:itemInfo["ITEM_FEATURE_ID"]
& (OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_CODE") == OOQL.CreateConstants(scanWarehouseNo)
| (OOQL.CreateConstants(scanWarehouseNo) == OOQL.CreateConstants("")))
& (OOQL.CreateProperty("BIN.BIN_CODE") == OOQL.CreateConstants(scanStorageSpacesNo)
| (OOQL.CreateConstants(scanStorageSpacesNo) == OOQL.CreateConstants("")))
& (OOQL.CreateProperty("ITEM_LOT.LOT_CODE") == OOQL.CreateConstants(itemLotCode) //20170331 modi by wangyq for P001-170327001 old:itemInfo["ITEM_LOT_CODE"]
| (OOQL.CreateConstants(itemLotCode) == OOQL.CreateConstants(""))) //20170331 modi by wangyq for P001-170327001 old:itemInfo["ITEM_LOT_CODE"]
))
.GroupBy(OOQL.CreateProperty("ITEM.ITEM_CODE"), OOQL.CreateProperty("ITEM.ITEM_NAME"),
OOQL.CreateProperty("ITEM.ITEM_SPECIFICATION"),
OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_CODE"), OOQL.CreateProperty("ITEM_FEATURE.ITEM_SPECIFICATION"),//wangrm
OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_CODE"),
OOQL.CreateProperty("BIN.BIN_CODE"), OOQL.CreateProperty("ITEM_LOT.LOT_CODE"),
OOQL.CreateProperty("UNIT.UNIT_CODE")
)
.Having((OOQL.CreateConstants(showZeroInventory) == OOQL.CreateConstants("N")
& Formulas.IsNull(Formulas.Sum(OOQL.CreateProperty("ITEM_WAREHOUSE_BIN.INVENTORY_QTY")), OOQL.CreateConstants(0)) != OOQL.CreateConstants(0))
| (OOQL.CreateConstants(showZeroInventory) == OOQL.CreateConstants("Y"))
);
return(GetService <IQueryService>().ExecuteDependencyObject(node));
}
/// <summary>
/// 获取登录人员的员工信息
/// </summary>
/// <param name="userId">用户ID</param>
/// <returns></returns>
public object GetEmployeeInfo(object userId)
{
QueryNode node = OOQL.Select(OOQL.CreateProperty("USER.EMPLOYEE_ID"))
.From("USER", "USER")
.Where((OOQL.AuthFilter("USER", "USER"))
& (OOQL.CreateProperty("USER.USER_ID") == OOQL.CreateConstants(userId)));
object adminUnitId = GetService <IQueryService>().ExecuteScalar(node);
return(Maths.IsEmpty(adminUnitId) ? Maths.GuidDefaultValue() : adminUnitId);
}
public DependencyObjectCollection QueryBarCodeInvQty(string barCodeNo, string siteNo, string scanWarehouseNo,
string scanStorageSpacesNo, string showZeroInventory)
{
QueryNode node = OOQL.Select(OOQL.CreateProperty("BC_INVENTORY.BARCODE_NO", "barcode_no"),
OOQL.CreateProperty("ITEM.ITEM_CODE", "item_no"),
OOQL.CreateProperty("ITEM.ITEM_NAME", "item_name"), //20170329 add by wangrm for P001-170316001
OOQL.CreateProperty("ITEM.ITEM_SPECIFICATION", "item_spec"), //20170329 add by wangrm for P001-170316001
OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_CODE", "item_feature_no"), //20170329 add by wangrm for P001-170316001
OOQL.CreateProperty("ITEM_FEATURE.ITEM_SPECIFICATION", "item_feature_name"), //20170329 add by wangrm for P001-170316001
Formulas.IsNull(OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_CODE"), OOQL.CreateConstants(""), "warehouse_no"),
Formulas.IsNull(OOQL.CreateProperty("BIN.BIN_CODE"), OOQL.CreateConstants(""), "storage_spaces_no"),
Formulas.IsNull(OOQL.CreateProperty("ITEM_LOT.LOT_CODE"), OOQL.CreateConstants(""), "lot_no"),
Formulas.IsNull(OOQL.CreateProperty("UNIT.UNIT_CODE"), OOQL.CreateConstants(""), "inventory_unit"),
Formulas.IsNull(Formulas.Sum(OOQL.CreateProperty("BC_INVENTORY.QTY")), OOQL.CreateConstants(0), "inventory_qty")
)
.From("BC_INVENTORY", "BC_INVENTORY")
.InnerJoin("ITEM")
.On(OOQL.CreateProperty("BC_INVENTORY.ITEM_ID") == OOQL.CreateProperty("ITEM.ITEM_ID"))
.LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE")
.On(OOQL.CreateProperty("BC_INVENTORY.ITEM_FEATURE_ID") == OOQL.CreateProperty("ITEM_FEATURE.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")
.On(OOQL.CreateProperty("BC_INVENTORY.Owner_Org.ROid") == OOQL.CreateProperty("PLANT.PLANT_ID"))
.LeftJoin("UNIT")
.On(OOQL.CreateProperty("ITEM.STOCK_UNIT_ID") == OOQL.CreateProperty("UNIT.UNIT_ID"))
.Where(OOQL.AuthFilter("BC_INVENTORY", "BC_INVENTORY")
& ((OOQL.CreateProperty("BC_INVENTORY.BARCODE_NO") == OOQL.CreateConstants(barCodeNo) |
OOQL.CreateConstants(barCodeNo) == OOQL.CreateConstants(string.Empty)) //20170331 add by wangyq for P001-170327001
& OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo)
& (OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_CODE") == OOQL.CreateConstants(scanWarehouseNo)
| (OOQL.CreateConstants(scanWarehouseNo) == OOQL.CreateConstants("")))
& (OOQL.CreateProperty("BIN.BIN_CODE") == OOQL.CreateConstants(scanStorageSpacesNo)
| (OOQL.CreateConstants(scanStorageSpacesNo) == OOQL.CreateConstants("")))
))
.GroupBy(OOQL.CreateProperty("ITEM.ITEM_CODE"), OOQL.CreateProperty("BC_INVENTORY.BARCODE_NO"),
OOQL.CreateProperty("ITEM.ITEM_NAME"), OOQL.CreateProperty("ITEM.ITEM_SPECIFICATION"), //wangrm
OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_CODE"), OOQL.CreateProperty("ITEM_FEATURE.ITEM_SPECIFICATION"), //wangrm
OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_CODE"),
OOQL.CreateProperty("BIN.BIN_CODE"), OOQL.CreateProperty("ITEM_LOT.LOT_CODE"),
OOQL.CreateProperty("UNIT.UNIT_CODE")
)
.Having((OOQL.CreateConstants(showZeroInventory) == OOQL.CreateConstants("N")
& Formulas.IsNull(Formulas.Sum(OOQL.CreateProperty("BC_INVENTORY.QTY")), OOQL.CreateConstants(0)) != OOQL.CreateConstants(0))
| (OOQL.CreateConstants(showZeroInventory) == OOQL.CreateConstants("Y"))
);
return(GetService <IQueryService>().ExecuteDependencyObject(node));
}
//20170302 add by shenbao for B001-170221021
private DependencyObjectCollection QueryBCRecord(string siteNo, string barcode_no)
{
object propertyID = QueryItemLot();
object qtPropertyID = QueryItemQty(); //20170302 add by shenbao for P001-170302002
QueryNode node = OOQL.Select(true, OOQL.CreateConstants("99", "enterprise_no") //企业编号
, OOQL.CreateProperty("PLANT.PLANT_CODE", "site_no") //营运据点
, OOQL.CreateProperty("BC_RECORD.BARCODE_NO", "barcode_no") //条码编号
, OOQL.CreateProperty("ITEM.ITEM_CODE", "item_no") //料件编号
,
OOQL.CreateArithmetic(OOQL.CreateProperty("ITEM.ITEM_NAME"),
OOQL.CreateProperty("ITEM.ITEM_SPECIFICATION")
, ArithmeticOperators.Plus, "item_name_spec") //品名
,
Formulas.IsNull(OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_CODE"),
OOQL.CreateConstants(string.Empty), "item_feature_no") //产品特征码
, OOQL.CreateConstants(string.Empty, "warehouse_no") //库位
, OOQL.CreateConstants(string.Empty, "storage_spaces_no") //储位
,
Formulas.IsNull(OOQL.CreateProperty("BC_RECORD_D.BC_PROPERTY_VALUE"), OOQL.CreateConstants(string.Empty),
"lot_no") //批号
//, OOQL.CreateConstants(0m, "inventory_qty") //数量 //20170302 mark by shenbao for P001-170302002
,
Formulas.IsNull(OOQL.CreateProperty("BC_RECORD_D_QTY.BC_PROPERTY_VALUE"), OOQL.CreateConstants(0),
"barcode_qty") //20170302 ADD by shenbao for P001-170302002
)
.From("BC_RECORD", "BC_RECORD")
.InnerJoin("ITEM")
.On(OOQL.CreateProperty("BC_RECORD.ITEM_ID") == OOQL.CreateProperty("ITEM.ITEM_ID"))
.LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE")
.On(OOQL.CreateProperty("BC_RECORD.ITEM_FEATURE_ID") ==
OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID"))
.LeftJoin("BC_RECORD.BC_RECORD_D", "BC_RECORD_D")
.On(OOQL.CreateProperty("BC_RECORD.BC_RECORD_ID") == OOQL.CreateProperty("BC_RECORD_D.BC_RECORD_ID")
& OOQL.CreateProperty("BC_RECORD_D.BC_PROPERTY_ID") == OOQL.CreateConstants(propertyID))
//20170302 add by shenbao for P001-170302002 ===begin===
.LeftJoin("BC_RECORD.BC_RECORD_D", "BC_RECORD_D_QTY")
.On(OOQL.CreateProperty("BC_RECORD.BC_RECORD_ID") == OOQL.CreateProperty("BC_RECORD_D_QTY.BC_RECORD_ID")
& OOQL.CreateProperty("BC_RECORD_D_QTY.BC_PROPERTY_ID") == OOQL.CreateConstants(qtPropertyID))
//20170302 add by shenbao for P001-1703020002 ===end===
.LeftJoin("ITEM_PLANT")
.On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("ITEM_PLANT.ITEM_ID"))
.InnerJoin("PLANT")
.On(OOQL.CreateProperty("ITEM_PLANT.Owner_Org.ROid") == OOQL.CreateProperty("PLANT.PLANT_ID"))
.Where((OOQL.AuthFilter("BC_RECORD", "BC_RECORD")
& (OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo)
| OOQL.CreateConstants(siteNo) == OOQL.CreateConstants(string.Empty))
// 20171023 add by 08628 for P001-171023001 ↓
& (OOQL.CreateProperty("BC_RECORD.BARCODE_NO") == OOQL.CreateConstants(barcode_no)
| OOQL.CreateConstants(barcode_no) == OOQL.CreateConstants(string.Empty))
// 20171023 add by 08628 for P001-171023001 ↑
));
return(this.GetService <IQueryService>().ExecuteDependencyObject(node));
}
/// <summary>
/// 查询条码规则属性项
/// </summary>
/// <param name="queryService">查询服务</param>
/// <param name="bcPropertyCode">属性项编码</param>
/// <returns></returns>
private object GetBcPropertyId(IQueryService queryService, string bcPropertyCode)
{
QueryNode node =
OOQL.Select(OOQL.CreateProperty("BC_PROPERTY_ID"))
.From("BC_PROPERTY")
.Where(OOQL.CreateProperty("BC_PROPERTY_CODE") == OOQL.CreateConstants(bcPropertyCode));
object result = queryService.ExecuteScalar(node); //20161229 add by shenbao for P001-161215001
return(result);
}
//20170302 add by shenbao for P001-170302002
private object QueryItemQty()
{
QueryNode node = OOQL.Select("BC_PROPERTY.BC_PROPERTY_ID")
.From("BC_PROPERTY")
.Where(OOQL.CreateProperty("BC_PROPERTY_CODE") == OOQL.CreateConstants("Sys_INV_Qty"));
object result = this.GetService <IQueryService>().ExecuteScalar(node);
result = result == null?Maths.GuidDefaultValue() : result;
return(result);
}
/// <summary>
/// 获取检验项目Id
/// </summary>
/// <param name="testNo"></param>
/// <param name="plantId"></param>
/// <returns></returns>
private object GetInspectionItem(object testNo, object plantId)
{
QueryNode node =
OOQL.Select(
OOQL.CreateProperty("ii.INSPECTION_ITEM_ID", "INSPECTION_ITEM_ID"))
.From("INSPECTION_ITEM", "ii")
.Where((OOQL.AuthFilter("INSPECTION_ITEM", "ii"))
& (OOQL.CreateProperty("ii.INSPECTION_ITEM_CODE") == OOQL.CreateConstants(testNo))
& (OOQL.CreateProperty("ii.Owner_Org.ROid") == OOQL.CreateConstants(plantId)));
return(GetService <IQueryService>().ExecuteScalar(node) ?? Maths.GuidDefaultValue());
}
/// <summary>
/// 获取销货单QueryNode
/// </summary>
/// <param name="siteNo">工厂</param>
/// <param name="programJobNo">作业编号</param>
/// <returns></returns>
private QueryNode GetSalesDeliveryQueryNode(string siteNo, string programJobNo
, DependencyObjectCollection condition//20170328 add by wangyq for P001-170327001
)
{
QueryNode queryNode = //20170328 modi by wangyq for P001-170327001 old: return
OOQL.Select(true, //20170104 add by wangyq for P001-161215001
OOQL.CreateProperty("SALES_DELIVERY.DOC_NO", "doc_no"),
OOQL.CreateProperty("SALES_DELIVERY.DOC_DATE", "create_date"),
OOQL.CreateProperty("CUSTOMER.CUSTOMER_NAME", "customer_name"),
OOQL.CreateConstants(programJobNo, GeneralDBType.String, "program_job_no"),
Formulas.IsNull(
OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_NAME"),
OOQL.CreateConstants(string.Empty, GeneralDBType.String), "employee_name"),
OOQL.CreateProperty("SALES_CENTER.SALES_CENTER_CODE", "main_organization"))
.From("SALES_DELIVERY", "SALES_DELIVERY")
.InnerJoin("SALES_CENTER", "SALES_CENTER")
.On((OOQL.CreateProperty("SALES_CENTER.SALES_CENTER_ID") == OOQL.CreateProperty("SALES_DELIVERY.Owner_Org.ROid")))
.InnerJoin("PLANT", "PLANT")
.On((OOQL.CreateProperty("PLANT.PLANT_ID") == OOQL.CreateProperty("SALES_DELIVERY.PLANT_ID")))
.LeftJoin("CUSTOMER", "CUSTOMER")
.On((OOQL.CreateProperty("CUSTOMER.CUSTOMER_ID") == OOQL.CreateProperty("SALES_DELIVERY.CUSTOMER_ID")))
.LeftJoin("EMPLOYEE", "EMPLOYEE")
.On((OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_ID") == OOQL.CreateProperty("SALES_DELIVERY.Owner_Emp")))
//20170104 add by wangyq for P001-161215001 ================begin=====================
.LeftJoin("SALES_DELIVERY.SALES_DELIVERY_D", "SALES_DELIVERY_D")
.On(OOQL.CreateProperty("SALES_DELIVERY.SALES_DELIVERY_ID") == OOQL.CreateProperty("SALES_DELIVERY_D.SALES_DELIVERY_ID"))
//20170328 add by wangyq for P001-170327001 ================begin==============
.LeftJoin("ADMIN_UNIT", "ADMIN_UNIT")
.On(OOQL.CreateProperty("SALES_DELIVERY.Owner_Dept") == OOQL.CreateProperty("ADMIN_UNIT.ADMIN_UNIT_ID"));
//20170328 add by wangyq for P001-170327001 ================end==============
//20170104 add by wangyq for P001-161215001 ================end=====================
//.Where( //20170328 mark by wangyq for P001-170327001
//20170104 add by wangyq for P001-161215001 ================begin=====================
//OOQL.AuthFilter("SALES_DELIVERY", "SALES_DELIVERY") &//20170328 mark by wangyq for P001-170327001
QueryConditionGroup group = //20170328 add by wangyq for P001-170327001
(OOQL.CreateProperty("SALES_DELIVERY_D.ISSUED") == OOQL.CreateConstants("0", GeneralDBType.String) &
//20170104 add by wangyq for P001-161215001 ================end=====================
(OOQL.CreateProperty("SALES_DELIVERY.ApproveStatus") == OOQL.CreateConstants("Y", GeneralDBType.String))
& (OOQL.CreateProperty("SALES_DELIVERY.CATEGORY") == OOQL.CreateConstants("24", GeneralDBType.String))
& (OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo, GeneralDBType.String)));
//); //20170328 mark by wangyq for P001-170327001
//20170328 add by wangyq for P001-170327001 ================begin==============
if (condition != null)
{
ConditionPropertyNameEntity conPropertyEntity = new ConditionPropertyNameEntity("SALES_DELIVERY.DOC_NO", "SALES_DELIVERY.DOC_DATE", new string[] { "1", "2", "3", "4", "6" });
group = UtilsClass.CreateNewConditionByParameter(group, condition, conPropertyEntity);
}
queryNode = ((JoinOnNode)queryNode).Where(OOQL.AuthFilter("SALES_DELIVERY", "SALES_DELIVERY") & (group));
return(queryNode);
//20170328 add by wangyq for P001-170327001 ================end==============
}
/// <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);
}
//20170508 add by liwei1 for P001-161209002 ---begin---
/// <summary>
/// 获取送货单单身存在到货单主键记录数
/// </summary>
/// <param name="deliveryNo"></param>
/// <returns></returns>
private int GetCountNum(string deliveryNo)
{
QueryNode node =
OOQL.Select(
Formulas.Count(OOQL.CreateProperty("FIL_ARRIVAL_D.FIL_ARRIVAL_D_ID"), "COUNT_NUM"))
.From("FIL_ARRIVAL.FIL_ARRIVAL_D", "FIL_ARRIVAL_D")
.Where((OOQL.AuthFilter("FIL_ARRIVAL.FIL_ARRIVAL_D", "FIL_ARRIVAL_D"))
& ((OOQL.CreateProperty("FIL_ARRIVAL_D.DOC_NO") == OOQL.CreateConstants(deliveryNo))
& (OOQL.CreateProperty("FIL_ARRIVAL_D.PURCHASE_ARRIVAL_ID") != OOQL.CreateConstants(Maths.GuidDefaultValue()))
& (OOQL.CreateProperty("FIL_ARRIVAL_D.PURCHASE_ARRIVAL_D_ID") != OOQL.CreateConstants(Maths.GuidDefaultValue()))));
return(GetService <IQueryService>().ExecuteScalar(node).ToInt32());
}
/// <summary>
/// 拼接查询条件
/// </summary>
/// <param name="siteNo"></param>
/// <param name="condition"></param>
/// <returns></returns>
private QueryConditionGroup CreateCondition(string siteNo, DependencyObjectCollection condition)
{
QueryConditionGroup group = OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo)
& OOQL.CreateProperty("SALES_ISSUE.ApproveStatus") == OOQL.CreateConstants("N", GeneralDBType.String)
& OOQL.CreateProperty("SALES_ISSUE_D.BC_CHECK_STATUS") == OOQL.CreateConstants("1", GeneralDBType.String);
if (condition != null)
{
ConditionPropertyNameEntity conPropertyEntity = new ConditionPropertyNameEntity("SALES_ISSUE.DOC_NO", "SALES_ISSUE.DOC_DATE", new string[] { "1", "2", "3", "4", "6" });
group = UtilsClass.CreateNewConditionByParameter(group, condition, conPropertyEntity);
}
return(group);
}
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="logonName">用户名</param>
/// <returns></returns>
private QueryNode GetUserInfomation(string logonName)
{
//20170331 modi by wangyq for P001-170327001 ===============begin==============
string[] paraList = GetParaFIL();
////20161216 add by shenbao for P001-161215001 ===begin===
////是否启用条码库存管理
//bool isInvertory = UtilsClass.IsBCInventoryManagement(this.GetService<IQueryService>());
//string manageBarCode = isInvertory ? "Y" : "N";
////20161216 add by shenbao for P001-161215001 ===end===
//20170331 modi by wangyq for P001-170327001 ===============end==============
return(OOQL.Select(true,
OOQL.CreateConstants(0, GeneralDBType.Int32, "code"),
OOQL.CreateConstants(string.Empty, GeneralDBType.String, "sql_code"),
OOQL.CreateConstants(string.Empty, GeneralDBType.String, "description"),
OOQL.CreateConstants("99", GeneralDBType.String, "enterprise_no"),
OOQL.CreateProperty("PLANT.PLANT_CODE", "site_no"),
OOQL.CreateProperty("USER.LOGONNAME", "account"),
OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_CODE", "employee_no"), //20161215 add by liwei1 for P001-161215001
//OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_CODE", "employee_name"),//20161215 mark by liwei1 for P001-161215001
OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_NAME", "employee_name"),
OOQL.CreateConstants(string.Empty, GeneralDBType.String, "language "),
//OOQL.CreateProperty("ADMIN_UNIT.ADMIN_UNIT_CODE", "department_no"),//20161215 mark by liwei1 for B001-161215015
//OOQL.CreateProperty("ADMIN_UNIT.ADMIN_UNIT_NAME", "department_name")//20161215 mark by liwei1 for B001-161215015
//20161215 add by liwei1 for B001-161215015 ===begin===
Formulas.IsNull(
OOQL.CreateProperty("ADMIN_UNIT.ADMIN_UNIT_CODE"),
OOQL.CreateConstants(string.Empty), "department_no"),
Formulas.IsNull(
OOQL.CreateProperty("ADMIN_UNIT.ADMIN_UNIT_NAME"),
OOQL.CreateConstants(string.Empty), "department_name"),
//20161215 add by liwei1 for B001-161215015 ===begin===
OOQL.CreateConstants(paraList[0], "manage_barcode_inventory") //20161216 add by shenbao for P001-161215001//20170331 modi by wangyq for P001-170327001 old:manageBarCode
, OOQL.CreateConstants(paraList[1], "warehouse_separator") //20170331 add by wangyq for P001-170327001
, OOQL.CreateConstants(paraList[2], "feature") //20170331 add by wangyq for P001-170327001
)
.From("USER", "USER")
.InnerJoin("EMPLOYEE", "EMPLOYEE")
.On((OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_ID") == OOQL.CreateProperty("USER.EMPLOYEE_ID")))
.LeftJoin("EMPLOYEE.EMPLOYEE_D", "EMPLOYEE_D") //20161215 modi by liwei1 for B001-161215015 old:InnerJoin
.On((OOQL.CreateProperty("EMPLOYEE_D.EMPLOYEE_ID") == OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_ID"))
& (OOQL.CreateProperty("EMPLOYEE_D.IS_PRIMARY") == OOQL.CreateConstants(true, GeneralDBType.Boolean)))
.LeftJoin("ADMIN_UNIT", "ADMIN_UNIT") //20161215 modi by liwei1 for B001-161215015 old:InnerJoin
.On((OOQL.CreateProperty("ADMIN_UNIT.ADMIN_UNIT_ID") == OOQL.CreateProperty("EMPLOYEE_D.ADMIN_UNIT_ID")))
.InnerJoin("USER.USER_ORG", "USER_ORG")
.On((OOQL.CreateProperty("USER_ORG.USER_ID") == OOQL.CreateProperty("USER.USER_ID")))
.InnerJoin("PLANT", "PLANT")
.On((OOQL.CreateProperty("PLANT.PLANT_ID") == OOQL.CreateProperty("USER_ORG.ORG.ROid")))
.Where((OOQL.AuthFilter("USER", "USER"))
& ((OOQL.CreateProperty("USER.LOGONNAME") == OOQL.CreateConstants(logonName))
& (OOQL.CreateProperty("USER_ORG.ORG.RTK") == OOQL.CreateConstants("PLANT")))));
}
