/// <summary>
/// 对单身分组
/// 单身和条码明显的分组依据不一样
/// </summary>
/// <param name="tmpIssueReceiptD"></param>
/// <returns></returns>
public QueryNode GetGroupForInsert(IDataEntityType tempEntityD)
{
List <QueryProperty> selectOrGroupList = new List <QueryProperty>();
selectOrGroupList.Add(OOQL.CreateProperty("item_no"));
selectOrGroupList.Add(OOQL.CreateProperty("item_feature_no"));
selectOrGroupList.Add(OOQL.CreateProperty("picking_unit_no"));
selectOrGroupList.Add(OOQL.CreateProperty("doc_no"));
selectOrGroupList.Add(OOQL.CreateProperty("seq"));
selectOrGroupList.Add(OOQL.CreateProperty("warehouse_no"));
selectOrGroupList.Add(OOQL.CreateProperty("storage_spaces_no"));
selectOrGroupList.Add(OOQL.CreateProperty("lot_no"));
selectOrGroupList.Add(OOQL.CreateProperty("barcode_no"));
List <QueryProperty> selectList = new List <QueryProperty>();
foreach (QueryProperty selectOrGroupObj in selectOrGroupList)
{
selectList.Add(selectOrGroupObj);
}
selectList.Add(Formulas.Sum(OOQL.CreateProperty("picking_qty"), "sum_picking_qty"));
QueryNode selectNode = OOQL.Select(selectList.ToArray())
.From(tempEntityD.Name, "TEMP")
.GroupBy(selectOrGroupList.ToArray());
return(selectNode);
}
/// <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));
}
/// <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>
/// <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));
}
/// <summary>
/// 根据传入的单号信息删除送货单
/// </summary>
/// <param name="delivery_no">送货单号</param>
public void DeleteFilArrival(string delivery_no)
{
try {
//20170508 add by liwei1 for P001-161209002 ---begin---
//检查该送货单是否已生成到货单，若已生成到货单则不能审核
if (GetCountNum(delivery_no) > 0)
{
IInfoEncodeContainer infoEnCode = GetService <IInfoEncodeContainer>();
throw new BusinessRuleException(infoEnCode.GetMessage("A111446"));//该送货单已生成到货单，不能删除！
}
//20170508 add by liwei1 for P001-161209002 ---end---
using (ITransactionService transActionService = GetService <ITransactionService>()) {
//删除送货单
QueryNode queryNode = OOQL.Delete("FIL_ARRIVAL")
.Where((OOQL.CreateProperty("FIL_ARRIVAL.DOC_NO") == OOQL.CreateConstants(delivery_no)));
GetService <IQueryService>().ExecuteNoQuery(queryNode);
//删除送货单单身
queryNode = OOQL.Delete("FIL_ARRIVAL.FIL_ARRIVAL_D")
.Where(OOQL.CreateProperty("FIL_ARRIVAL.FIL_ARRIVAL_D.DOC_NO") == OOQL.CreateConstants(delivery_no));
GetService <IQueryService>().ExecuteNoQuery(queryNode);
transActionService.Complete();
}
} catch (Exception) {
throw;
}
}
/// <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);
}
//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="tmpIssueReceiptD"></param>
/// <returns></returns>
public QueryNode GroupNode(bool isEntityLine)
{
List <QueryProperty> properties = new List <QueryProperty>();
properties.Add(OOQL.CreateProperty("TMP.ID"));
properties.Add(OOQL.CreateProperty("TMP.SequenceNumber"));
properties.Add(OOQL.CreateProperty("TMP.item_no"));
properties.Add(OOQL.CreateProperty("TMP.item_feature_no"));
properties.Add(OOQL.CreateProperty("TMP.picking_unit_no"));
properties.Add(OOQL.CreateProperty("TMP.warehouse_no"));
properties.Add(OOQL.CreateProperty("TMP.storage_spaces_no"));
properties.Add(OOQL.CreateProperty("TMP.lot_no"));
properties.Add(OOQL.CreateProperty("TMP.info_lot_no"));
if (isEntityLine)
{
properties.Add(OOQL.CreateProperty("TMP.barcode_no"));
}
List <QueryProperty> groupProperties = new List <QueryProperty>();
groupProperties = new List <QueryProperty>();
groupProperties.AddRange(properties);
properties.Add(Formulas.IsNull(Formulas.Sum(OOQL.CreateProperty("TMP.picking_qty")), OOQL.CreateConstants(0), "picking_qty"));
QueryNode node = OOQL.Select(properties.ToArray())
.From(_tempScanDetail.Name, "TMP")
.GroupBy(groupProperties);
return(node);
}
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>
/// 对单身分组
/// 单身和条码明显的分组依据不一样
/// </summary>
/// <param name="tmpIssueReceiptD"></param>
/// <returns></returns>
public QueryNode GroupNode(IDataEntityType tmpIssueReceiptD, bool isEntityLine)
{
List <QueryProperty> properties = new List <QueryProperty> {
OOQL.CreateProperty("TMP.DOC_NO")
, OOQL.CreateProperty("TMP.SequenceNumber")
, OOQL.CreateProperty("TMP.PLANT_CODE")
, OOQL.CreateProperty("TMP.ITEM_CODE")
, OOQL.CreateProperty("TMP.ITEM_FEATURE_CODE")
, OOQL.CreateProperty("TMP.UNIT_CODE")
, OOQL.CreateProperty("TMP.WAREHOUSE_CODE")
, OOQL.CreateProperty("TMP.BIN_CODE")
, OOQL.CreateProperty("TMP.LOT_CODE")
};
List <QueryProperty> groupProperties = new List <QueryProperty>();
groupProperties = new List <QueryProperty>();
groupProperties.AddRange(properties);
if (!isEntityLine)
{
properties.Add(OOQL.CreateProperty("TMP.BARCODE_NO"));
groupProperties.Add(OOQL.CreateProperty("TMP.BARCODE_NO"));
}
properties.Add(Formulas.IsNull(Formulas.Sum(OOQL.CreateProperty("TMP.ISSUE_RECEIPT_QTY")), OOQL.CreateConstants(0), "ISSUE_RECEIPT_QTY"));
QueryNode node = OOQL.Select(properties
)
.From(tmpIssueReceiptD.Name, "TMP")
.GroupBy(groupProperties);
return(node);
}
/// <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="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="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);
}
/// <summary>
/// 对单身分组
/// 单身和条码明显的分组依据不一样
/// </summary>
/// <param name="isEntityLine"></param>
/// <returns></returns>
public QueryNode GroupNode(bool isEntityLine)
{
List <QueryProperty> properties = new List <QueryProperty> {
// OOQL.CreateProperty("TMP.info_lot_no")
OOQL.CreateProperty("TMP.item_no")
, OOQL.CreateProperty("TMP.item_feature_no")
, OOQL.CreateProperty("TMP.picking_unit_no")
, OOQL.CreateProperty("TMP.doc_no")
, OOQL.CreateProperty("TMP.seq")
, OOQL.CreateProperty("TMP.warehouse_no")
, OOQL.CreateProperty("TMP.storage_spaces_no")
, OOQL.CreateProperty("TMP.lot_no")
, OOQL.CreateProperty("TMP.site_no")
};
List <QueryProperty> groupProperties = new List <QueryProperty>();
groupProperties = new List <QueryProperty>();
groupProperties.AddRange(properties);
if (isEntityLine)
{
properties.Add(OOQL.CreateProperty("TMP.barcode_no"));
groupProperties.Add(OOQL.CreateProperty("TMP.barcode_no"));
}
properties.Add(Formulas.IsNull(Formulas.Sum(OOQL.CreateProperty("TMP.picking_qty")), OOQL.CreateConstants(0), "picking_qty"));
QueryNode node = OOQL.Select(properties)
.From(_TEMP_SCAN_DETAIL.Name, "TMP")
.GroupBy(groupProperties);
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>
/// <returns></returns>
private bool GetBcInventoryManagement()
{
QueryNode node = OOQL.Select(OOQL.CreateProperty("PARA_FIL.BC_INVENTORY_MANAGEMENT"))
.From("PARA_FIL", "PARA_FIL")
.Where(OOQL.AuthFilter("PARA_FIL", "PARA_FIL"));
return(this.GetService <IQueryService>().ExecuteScalar(node).ToBoolean());
}
/// <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());
}
/// <summary>
/// 查询FIL参数
/// </summary>
/// <returns></returns>
private DependencyObjectCollection QueryParaFil()
{
_queryNode =
OOQL.Select(OOQL.CreateProperty("PARA_FIL.BC_LINE_MANAGEMENT", "BC_LINE_MANAGEMENT"))
.From("PARA_FIL", "PARA_FIL");
return(_querySrv.ExecuteDependencyObject(_queryNode));
}
//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);
}
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);
}
/// <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))));
}
/// <summary>
/// 查询工厂
/// </summary>
/// <returns></returns>
private DependencyObjectCollection QueryPlant()
{
_queryNode =
OOQL.Select(1, OOQL.CreateProperty("PLANT.COMPANY_ID", "COMPANY_ID"))
.From("PLANT", "PLANT")
.Where((OOQL.AuthFilter("PLANT", "PLANT")) &
(OOQL.CreateProperty("PLANT.PLANT_CODE").In(_lstQueryProperties.ToArray())));
return(_querySrv.ExecuteDependencyObject(_queryNode));
}
/// <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);
}
/// <summary>
/// 查询单号
/// </summary>
/// <param name="docNos">单号集合</param>
/// <returns></returns>
private DependencyObjectCollection QueryDoc(List <string> docNos)
{
_queryNode = OOQL.Select(OOQL.CreateProperty("TRANSACTION_DOC.TRANSACTION_DOC_ID"),
OOQL.CreateProperty("DOC.AUTO_APPROVE"))
.From("TRANSACTION_DOC", "TRANSACTION_DOC")
.InnerJoin("DOC", "DOC")
.On(OOQL.CreateProperty("DOC.DOC_ID") == OOQL.CreateProperty("TRANSACTION_DOC.DOC_ID"))
.Where(OOQL.CreateProperty("TRANSACTION_DOC.DOC_NO").In(OOQL.CreateDyncParameter("docnos", docNos.ToArray())));
return(_querySrv.ExecuteDependencyObject(_queryNode));
}
/// <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 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="employeeId">员工ID</param>
/// <returns></returns>
public object GetAdminUnitInfo(object employeeId)
{
QueryNode node = OOQL.Select(OOQL.CreateProperty("EMPLOYEE_D.ADMIN_UNIT_ID"))
.From("EMPLOYEE.EMPLOYEE_D", "EMPLOYEE_D")
.Where((OOQL.AuthFilter("EMPLOYEE.EMPLOYEE_D", "EMPLOYEE_D"))
& OOQL.CreateProperty("EMPLOYEE_D.EMPLOYEE_ID") == OOQL.CreateConstants(employeeId)
& (OOQL.CreateProperty("EMPLOYEE_D.IS_PRIMARY") == OOQL.CreateConstants(true)));
object adminUnitId = GetService <IQueryService>().ExecuteScalar(node);
return(Maths.IsEmpty(adminUnitId) ? Maths.GuidDefaultValue():adminUnitId);
}
//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>
/// 获取新插入的领料单，重新保存
/// </summary>
/// <param name="docNos">单号集合</param>
/// <returns></returns>
private DependencyObjectCollection GetSalesIssue(List <string> docNos)
{
QueryNode node = OOQL.Select(OOQL.CreateProperty("SALES_ISSUE.SALES_ISSUE_ID"),
OOQL.CreateProperty("DOC.AUTO_APPROVE"))
.From("SALES_ISSUE", "SALES_ISSUE")
.InnerJoin("DOC", "DOC")
.On(OOQL.CreateProperty("SALES_ISSUE.DOC_ID") == OOQL.CreateProperty("DOC.DOC_ID"))
.Where(OOQL.AuthFilter("SALES_ISSUE", "SALES_ISSUE") &
OOQL.CreateProperty("SALES_ISSUE.DOC_NO").In(OOQL.CreateDyncParameter("docnos", docNos.ToArray())));
return(this.GetService <IQueryService>().ExecuteDependencyObject(node));
}
/// <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());
}
