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