public List <R105_PlanTrace> GetPlanTraceList(QueryR103JobTxDetailView query) { StringBuilder sqlStr = new StringBuilder(); sqlStr.Append(@" select * from (select a.plNo,a.PartName,a.Qty as PlanQty,a.FQty as PlanFQty,a.AdJustQty,a.SumQty,b.TaskNo,b.JobNo,b.JobQty,b.FQty as TaskFQty ,c.ProdDt,c.ShiftCode,c.MacCode,c.InQty,c.OutQty,c.NGQty from MES_M201_Plan_Detail a Left Outer join MES_M202_Task_Detail b on a.plNo = b.plNo and a.PartNo = b.PartNo and a.CompCode = b.CompCode Left Outer join MES_M501_JobTx c on c.plNo = b.plNo and c.TaskNo = b.TaskNo and c.PartNo = b.PartNo and c.CompCode = b.CompCode ) as T where 1= 1"); if (query.ProdDt != null && query.ProdDt2 != null) { sqlStr.AppendFormat(" and ProdDt between '{0}' and '{1}' ", query.ProdDt, query.ProdDt2); } if (!string.IsNullOrWhiteSpace(query.PartNo)) { sqlStr.AppendFormat(" and PartNo = '{0}' ", query.PartNo); } if (!string.IsNullOrWhiteSpace(query.PLNO)) { sqlStr.AppendFormat(" and plNo = '{0}' ", query.PLNO); } if (!string.IsNullOrWhiteSpace(query.TaskNo)) { sqlStr.AppendFormat(" and TaskNo = '{0}' ", query.TaskNo); } if (!string.IsNullOrWhiteSpace(query.JobNo)) { sqlStr.AppendFormat(" and JobNo = '{0}' ", query.JobNo); } var q = this.dbfactory.Get().Database.SqlQuery <R105_PlanTrace>(sqlStr.ToString()); return(q.ToList()); }
public List <R103_JobTxDetail> GetJobTxDetailList(QueryR103JobTxDetailView query) { StringBuilder sqlStr = new StringBuilder(); sqlStr.Append(@" select * from (select PLNO,TaskNo,JobNo,ProcessCode,JobType,tx.PartNo,pro.PartName,ProdDt,ModelCode,Shift.Description as ShiftCode,MacCode,Sup.Name as Supervisor ,Op.Name as Operator,Qc.Name as QCOperator,tx.InQty,tx.OutQty,tx.NGQty,tx.Remarks,tx.CreateDt from MES_M501_JobTx tx LEFT OUTER JOIN ERP_M001_Product pro on pro.PartNo = tx.PartNo and pro.CompCode = tx.CompCode LEFT OUTER JOIN ERP_C001_Code Shift on Shift.Code = tx.ShiftCode and Shift.CompCode = tx.CompCode and Shift.CodeID = 'SHIFT' LEFT OUTER JOIN ERP_C005_Employee Sup ON Sup.EmpNo = tx.Supervisor and Sup.CompCode = tx.CompCode LEFT OUTER JOIN ERP_C005_Employee Op ON Op.EmpNo = tx.Supervisor and Op.CompCode = tx.CompCode LEFT OUTER JOIN ERP_C005_Employee Qc ON Qc.EmpNo = tx.Supervisor and Qc.CompCode = tx.CompCode ) as T where 1= 1"); if (query.ProdDt != null && query.ProdDt2 != null) { sqlStr.AppendFormat(" and ProdDt between '{0}' and '{1}' ", query.ProdDt, query.ProdDt2); } if (!string.IsNullOrWhiteSpace(query.PartNo)) { sqlStr.AppendFormat(" and PartNo = '{0}' ", query.PartNo); } if (!string.IsNullOrWhiteSpace(query.PLNO)) { sqlStr.AppendFormat(" and plNo = '{0}' ", query.PLNO); } if (!string.IsNullOrWhiteSpace(query.TaskNo)) { sqlStr.AppendFormat(" and TaskNo = '{0}' ", query.TaskNo); } if (!string.IsNullOrWhiteSpace(query.JobNo)) { sqlStr.AppendFormat(" and JobNo = '{0}' ", query.JobNo); } var q = this.dbfactory.Get().Database.SqlQuery <R103_JobTxDetail>(sqlStr.ToString()); return(q.ToList()); }