public static List <object> RetrieveTestData(string prodline, Dictionary <string, bool> sndict)
{
var r = new List <object>();
var dict = new Dictionary <string, string>();
dict.Add("@DMRProdLine", prodline);
var snlist = sndict.Keys.ToList();
var sncond = "('" + string.Join("','", snlist) + "')";
var sql = "select WhichTest,Failure,SN,TestTime from DMRSNTestData where DMRProdLine = @DMRProdLine and SN in <sncond> order by SN,TestTime desc";
sql = sql.Replace("<sncond>", sncond);
var dbret = DBUtility.ExeLocalSqlWithRes(sql, dict);
var whichcntdict = new Dictionary <string, DMRSNTestData>();
var faillist = new List <DMRSNTestData>();
foreach (var l in dbret)
{
var ts = O2S(l[0]);
var fail = O2S(l[1]);
var sn = O2S(l[2]);
var tt = O2T(l[3]);
var pass = true;
if (string.Compare(fail, "pass", true) != 0)
{
pass = false;
var tempvm = new DMRSNTestData();
tempvm.SN = sn;
tempvm.WhichTest = ts;
tempvm.Failure = fail;
tempvm.TestTime = tt;
faillist.Add(tempvm);
}
if (whichcntdict.ContainsKey(ts))
{
if (pass)
{
whichcntdict[ts].PassCnt += 1;
}
else
{
whichcntdict[ts].FailCnt += 1;
}
}
else
{
var tempvm = new DMRSNTestData();
tempvm.WhichTest = ts;
if (pass)
{
tempvm.PassCnt += 1;
}
else
{
tempvm.FailCnt += 1;
}
whichcntdict.Add(ts, tempvm);
}
}
r.Add(whichcntdict.Values.ToList());
r.Add(faillist);
return(r);
}
public static List <ModuleAssemblyVM> RetrieveData(List <string> sns)
{
var ret = new List <ModuleAssemblyVM>();
var sncond = "('" + string.Join("','", sns) + "')";
//var sql = @"select ToContainer,ToProductName,ToPNDescription,FromContainer,FromProductName,FromPNDescription
// , ToWorkflowStepId,IssueDate FROM[PDMS].[dbo].[ComponentIssueSummary] where ToContainer in <sncond> order by ToContainer,IssueDate desc";
var sql = @"select co.ContainerName,tpb.ProductName,tp.Description,fc.ContainerName,pb.ProductName, p.Description,ws.WorkflowStepName,hml.MfgDate from insitedb.insite.ComponentIssueHistory cih with(nolock)
inner join insitedb.insite.Historymainline hml with(nolock) on hml.HistoryMainlineId = cih.historymainlineid
inner join insitedb.insite.IssueHistoryDetail ihd with(nolock) on ihd.ComponentIssueHistoryId= cih.ComponentIssueHistoryId
inner join insitedb.insite.IssueActualsHistory iah with(nolock) on iah.IssueHistoryDetailId=ihd.IssueHistoryDetailId
inner join insitedb.insite.Product p with(nolock) on p.ProductId = iah.ProductId
inner join insitedb.insite.ProductBase pb with(nolock) on pb.ProductBaseId = p.ProductBaseId
inner join insitedb.insite.WorkflowStep ws with(nolock) on ws.WorkflowStepId = hml.WorkflowStepId
inner join InsiteDB.insite.container co (nolock) on co.containerid=hml.HistoryId
inner join InsiteDB.insite.container fc (nolock) on fc.ContainerId=iah.FromContainerId
inner join insitedb.insite.Product tp with(nolock) on tp.ProductId = co.ProductId
inner join insitedb.insite.ProductBase tpb with(nolock) on tp.ProductBaseId = tpb.ProductBaseId
where co.ContainerName in <sncond> order by co.ContainerName,hml.MfgDate desc";
sql = sql.Replace("<sncond>", sncond);
//var stepiddict = new Dictionary<string, bool>();
var dbret = DBUtility.ExeRealMESSqlWithRes(sql);
foreach (var line in dbret)
{
var tempvm = new ModuleAssemblyVM();
tempvm.SN = Convert2Str(line[0]);
tempvm.PN = Convert2Str(line[1]);
tempvm.Desc = Convert2Str(line[2]);
tempvm.AssemblySN = Convert2Str(line[3]);
tempvm.AssemblyPN = Convert2Str(line[4]);
tempvm.AssemblyDesc = Convert2Str(line[5]);
tempvm.StepName = Convert2Str(line[6]);
tempvm.IssueDate = Convert2Date(line[7]);
ret.Add(tempvm);
//if (!string.IsNullOrEmpty(tempvm.StepID) && !stepiddict.ContainsKey(tempvm.StepID))
//{ stepiddict.Add(tempvm.StepID,true); }
}
//var stepnamedict = new Dictionary<string, string>();
//if (stepiddict.Count > 0)
//{
// var idlist = stepiddict.Keys.ToList();
// var idcond = "('" + string.Join("','", idlist) + "')";
// sql = "select WorkflowStepId,WorkflowStepName from InsiteDB.insite.WorkflowStep where WorkflowStepId in <idcond>";
// sql = sql.Replace("<idcond>", idcond);
// dbret = DBUtility.ExeRealMESSqlWithRes(sql);
// foreach (var line in dbret)
// {
// var id = Convert2Str(line[0]);
// var name = Convert2Str(line[1]);
// if (!stepnamedict.ContainsKey(id))
// { stepnamedict.Add(id, name); }
// }
//}
//foreach (var item in ret)
//{
// if (stepnamedict.ContainsKey(item.StepID))
// {
// item.StepName = stepnamedict[item.StepID];
// }
//}
return(ret);
}
public static List <WaferStatusVM> RetrieveData(List <string> waferlist)
{
var ret = new List <WaferStatusVM>();
if (waferlist.Count == 0)
{
return(ret);
}
var wafercond = "";
foreach (var w in waferlist)
{
wafercond = wafercond + " or dc.[ParamValueString] like '" + w + "%'";
}
wafercond = wafercond.Substring(3);
var sql = @"SELECT distinct left(dc.[ParamValueString],9) WaferNum, c.ContainerName,ws.WorkflowStepName,pbb.ProductName ProductPN,pf.ProductFamilyName,hml.MfgDate
FROM InsiteDB.insite.container c with (nolock)
left join InsiteDB.insite.currentStatus cs (nolock) on c.currentStatusId = cs.currentStatusId
left join InsiteDB.insite.workflowstep ws(nolock) on cs.WorkflowStepId = ws.WorkflowStepId
left join InsiteDB.insite.componentRemoveHistory crh with (nolock) on crh.historyId = c.containerId
left join InsiteDB.insite.removeHistoryDetail rhd on rhd.componentRemoveHistoryId = crh.componentRemoveHistoryId
left join InsiteDB.insite.starthistorydetail shd(nolock) on c.containerid=shd.containerId and shd.historyId <> shd.containerId
left join InsiteDB.insite.container co (nolock) on co.containerid=shd.historyId
left join InsiteDB.insite.historyMainline hml with (nolock) on c.containerId = hml.containerId
left join InsiteDB.insite.componentIssueHistory cih with (nolock) on hml.historyMainlineId=cih.historyMainlineId
left join InsiteDB.insite.issueHistoryDetail ihd with (nolock) on cih.componentIssueHistoryId = ihd.componentIssueHistoryId
left join InsiteDB.insite.issueActualsHistory iah with (nolock) on ihd.issueHistoryDetailId = iah.issueHistoryDetailId
left join InsiteDB.insite.RemoveHistoryDetail rem with (nolock) on iah.IssueActualsHistoryId = rem.IssueActualsHistoryId
left join InsiteDB.insite.RemovalReason re with (nolock) on rem.RemovalReasonId = re.RemovalReasonId
left join InsiteDB.insite.container cFrom with (nolock) on iah.fromContainerId = cFrom.containerId
left join InsiteDB.insite.product p with (nolock) on cFrom.productId = p.productId
left join InsiteDB.insite.productBase pb with (nolock) on p.productBaseId = pb.productBaseId
left join InsiteDB.insite.historyMainline hmll with (nolock)on cFrom.OriginalcontainerId=hmll.historyid
left join InsiteDB.insite.product pp with (nolock) on c.productid=pp.productid
left join InsiteDB.insite.productfamily pf (nolock) on pp.productFamilyId = pf.productFamilyId
left join InsiteDB.insite.productbase pbb with (nolock) on pp.productbaseid=pbb.productbaseid
left join InsiteDB.insite.dc_AOC_ManualInspection dc (nolock) on hmll.[HistoryMainlineId]=dc.[HistoryMainlineId]
WHERE dc.parametername='Trace_ID' and p.description like '%VCSEL%' and (<wafercond>)
and Len(c.ContainerName) = 7" ;
sql = sql.Replace("<wafercond>", wafercond);
var dbret = DBUtility.ExeRealMESSqlWithRes(sql);
foreach (var line in dbret)
{
try
{
var tempvm = new WaferStatusVM();
tempvm.WaferNum = Convert.ToString(line[0]);
tempvm.SN = Convert.ToString(line[1]);
tempvm.WorkFlowStep = Convert.ToString(line[2]);
tempvm.PN = Convert.ToString(line[3]);
tempvm.ProductFamily = Convert.ToString(line[4]);
tempvm.UpdateTime = Convert.ToDateTime(line[5]).ToString("yyyy-MM-dd HH:mm:ss");
ret.Add(tempvm);
}
catch (Exception ex) { }
}
return(ret);
}
public static void RetrieveWaferBySN(List <WaferTableItem> desdata)
{
//var sncond = " ('";
//foreach (var item in desdata)
//{
// sncond = sncond + item.SN + "','";
//}
//sncond = sncond.Substring(0, sncond.Length - 2);
//sncond = sncond + ") ";
StringBuilder sb = new StringBuilder(10 * (desdata.Count + 5));
sb.Append("('");
foreach (var item in desdata)
{
sb.Append(item.SN + "','");
}
var tempstr = sb.ToString();
var sncond = tempstr.Substring(0, tempstr.Length - 2) + ")";
var queryedsndict = new Dictionary <string, bool>();
var tetmpres = new List <WaferTableItem>();
var sql = "select ToContainer,Wafer,FromProductName,FromPNDescription from [PDMS].[dbo].[ComponentIssueSummary] where ToContainer in <SNCOND> and Wafer is not null and FromPNDescription is not null order by Wafer";
sql = sql.Replace("<SNCOND>", sncond);
var dbret = DBUtility.ExeMESReportSqlWithRes(sql);
foreach (var line in dbret)
{
var pndesc = Convert.ToString(line[3]);
if ((pndesc.ToUpper().Contains("LD,") && pndesc.ToUpper().Contains("VCSEL,")) ||
(pndesc.ToUpper().Contains("CSG") && (pndesc.ToUpper().Contains("INGAAS VCSEL") || pndesc.ToUpper().Contains("VCSEL ARRAY"))))
{
var tempvm = new WaferTableItem();
tempvm.SN = Convert.ToString(line[0]);
tempvm.WaferNum = Convert.ToString(line[1]);
if (tempvm.WaferNum.Length > 3)
{
var fidx = tempvm.WaferNum.IndexOf("-");
if (fidx != -1 && tempvm.WaferNum.Length >= (fidx + 3))
{
tempvm.WaferNum = tempvm.WaferNum.Substring(0, fidx + 3);
}
}
tempvm.PN = Convert.ToString(line[2]);
tetmpres.Add(tempvm);
if (!queryedsndict.ContainsKey(tempvm.SN.Trim().ToUpper()))
{
queryedsndict.Add(tempvm.SN.Trim().ToUpper(), true);
}
}
}
var leftsnlist = new List <string>();
foreach (var item in desdata)
{
if (!string.IsNullOrEmpty(item.SN.Trim()) &&
!queryedsndict.ContainsKey(item.SN.Trim().ToUpper()))
{
leftsnlist.Add(item.SN.Trim());
}
}
if (leftsnlist.Count > 0)
{
var sncond2 = "('" + string.Join("','", leftsnlist) + "')";
sql = @"select tco.ContainerName,left(dc.ParamValueString,9) as wafer,pb.ProductName
from InsiteDB.insite.container fco (nolock)
inner join insitedb.insite.IssueActualsHistory iah with(nolock) on iah.FromContainerId = fco.ContainerId
inner join InsiteDB.insite.container tco (nolock) on iah.ToContainerId = tco.ContainerId
inner join insitedb.insite.Product p (nolock) on p.ProductId = fco.ProductId
inner join insitedb.insite.ProductBase pb (nolock) on pb.ProductBaseId = p.ProductBaseId
inner join InsiteDB.insite.container orgco (nolock) on orgco.ContainerName = fco.DateCode
inner join insitedb.insite.Historymainline hml with(nolock) on hml.HistoryId = orgco.ContainerId
inner join InsiteDB.insite.dc_AOC_ManualInspection dc (nolock) on dc.historymainlineid = hml.historymainlineid
where tco.ContainerName in <SNCOND> and p.description like '%VCSEL%' and dc.ParameterName = 'Trace_ID' and dc.ParamValueString is not null" ;
sql = sql.Replace("<SNCOND>", sncond2);
dbret = DBUtility.ExeRealMESSqlWithRes(sql);
foreach (var line in dbret)
{
var tempvm = new WaferTableItem();
tempvm.SN = Convert.ToString(line[0]);
tempvm.WaferNum = Convert.ToString(line[1]);
tempvm.PN = Convert.ToString(line[2]);
tetmpres.Add(tempvm);
if (!queryedsndict.ContainsKey(tempvm.SN.Trim().ToUpper()))
{
queryedsndict.Add(tempvm.SN.Trim().ToUpper(), true);
}
}
}
leftsnlist = new List <string>();
foreach (var item in desdata)
{
if (!string.IsNullOrEmpty(item.SN.Trim()) &&
!queryedsndict.ContainsKey(item.SN.Trim().ToUpper()))
{
leftsnlist.Add(item.SN.Trim());
}
}
if (leftsnlist.Count > 0)
{
StringBuilder sb1 = new StringBuilder(10 * (leftsnlist.Count + 5));
sb1.Append("('");
foreach (var line in leftsnlist)
{
sb1.Append(line + "','");
}
var tempstr1 = sb1.ToString();
var sncond1 = tempstr1.Substring(0, tempstr1.Length - 2) + ")";
sql = @"SELECT distinct c.ContainerName as SerialName,isnull(dc.[ParamValueString],'') as WaferLot,pb.productname MaterialPN ,hml.MfgDate
FROM InsiteDB.insite.container c with (nolock)
left join InsiteDB.insite.currentStatus cs (nolock) on c.currentStatusId = cs.currentStatusId
left join InsiteDB.insite.workflowstep ws(nolock) on cs.WorkflowStepId = ws.WorkflowStepId
left join InsiteDB.insite.componentRemoveHistory crh with (nolock) on crh.historyId = c.containerId
left join InsiteDB.insite.removeHistoryDetail rhd on rhd.componentRemoveHistoryId = crh.componentRemoveHistoryId
left join InsiteDB.insite.starthistorydetail shd(nolock) on c.containerid=shd.containerId and shd.historyId <> shd.containerId
left join InsiteDB.insite.container co (nolock) on co.containerid=shd.historyId
left join InsiteDB.insite.historyMainline hml with (nolock) on c.containerId = hml.containerId
left join InsiteDB.insite.componentIssueHistory cih with (nolock) on hml.historyMainlineId=cih.historyMainlineId
left join InsiteDB.insite.issueHistoryDetail ihd with (nolock) on cih.componentIssueHistoryId = ihd.componentIssueHistoryId
left join InsiteDB.insite.issueActualsHistory iah with (nolock) on ihd.issueHistoryDetailId = iah.issueHistoryDetailId
left join InsiteDB.insite.RemoveHistoryDetail rem with (nolock) on iah.IssueActualsHistoryId = rem.IssueActualsHistoryId
left join InsiteDB.insite.RemovalReason re with (nolock) on rem.RemovalReasonId = re.RemovalReasonId
left join InsiteDB.insite.container cFrom with (nolock) on iah.fromContainerId = cFrom.containerId
left join InsiteDB.insite.product p with (nolock) on cFrom.productId = p.productId
left join InsiteDB.insite.productBase pb with (nolock) on p.productBaseId = pb.productBaseId
left join InsiteDB.insite.historyMainline hmll with (nolock)on cFrom.OriginalcontainerId=hmll.historyid
left join InsiteDB.insite.product pp with (nolock) on c.productid=pp.productid
left join InsiteDB.insite.productfamily pf (nolock) on pp.productFamilyId = pf.productFamilyId
left join InsiteDB.insite.productbase pbb with (nolock) on pp.productbaseid=pbb.productbaseid
left join InsiteDB.insite.dc_AOC_ManualInspection dc (nolock) on hmll.[HistoryMainlineId]=dc.[HistoryMainlineId]
WHERE dc.parametername='Trace_ID' and p.description like '%VCSEL%' and dc.[ParamValueString] like '%-%'and c.containername in <SNCOND> order by pb.productname,c.ContainerName,hml.MfgDate DESC";
sql = sql.Replace("<SNCOND>", sncond1);
dbret = DBUtility.ExeRealMESSqlWithRes(sql);
foreach (var line in dbret)
{
var tempvm = new WaferTableItem();
tempvm.SN = Convert.ToString(line[0]);
tempvm.WaferNum = Convert.ToString(line[1]);
if (tempvm.WaferNum.Length > 3)
{
var fidx = tempvm.WaferNum.IndexOf("-");
if (fidx != -1 && tempvm.WaferNum.Length >= (fidx + 3))
{
tempvm.WaferNum = tempvm.WaferNum.Substring(0, fidx + 3);
}
}
tempvm.PN = Convert.ToString(line[2]);
tetmpres.Add(tempvm);
}
}
foreach (var des in desdata)
{
foreach (var src in tetmpres)
{
if (string.Compare(des.SN, src.SN, true) == 0)
{
des.WaferNum = src.WaferNum;
des.PN = src.PN;
break;
}
} //end foreach
} //end foreach
}
public static List <WaferTableItem> RetrieveSNByDateCode(List <string> datecodelist, Dictionary <string, string> appenddict)
{
var ret = new List <WaferTableItem>();
var datecond = " ('";
foreach (var item in datecodelist)
{
datecond = datecond + item + "','";
}
datecond = datecond.Substring(0, datecond.Length - 2);
datecond = datecond + ") ";
//var sql = "select ContainerName,DateCode,CustomerSerialNum,ContainerId FROM [InsiteDB].[insite].[Container] where DateCode in <datecond> or CustomerSerialNum in <datecond>";
var sql = @"select ContainerName,DateCode,CustomerSerialNum,ContainerId FROM [InsiteDB].[insite].[Container]
where ParentContainerId in (SELECT ContainerId FROM [InsiteDB].[insite].[Container] where ContainerName in <datecond>)
or CustomerSerialNum in <datecond>";
sql = sql.Replace("<datecond>", datecond);
var excludsndict = new Dictionary <string, bool>();
var pkgdict = new Dictionary <string, string>();
var dbret = DBUtility.ExeRealMESSqlWithRes(sql);
foreach (var line in dbret)
{
try {
var sn = Convert.ToString(line[0]);
if (sn.Length > 7
//&& !line[1].Equals(null)
&& !pkgdict.ContainsKey(sn))
{
pkgdict.Add(Convert.ToString(line[3]), Convert2Str(line[1]));
}
else
{
var tempvm = new WaferTableItem();
tempvm.SN = Convert.ToString(line[0]);
if (!excludsndict.ContainsKey(tempvm.SN))
{
excludsndict.Add(tempvm.SN, true);
var appendinfo = "";
if (line[1].Equals(null))
{
tempvm.DateCode = "";
}
else
{
tempvm.DateCode = Convert.ToString(line[1]);
appendinfo = tempvm.DateCode;
}
if (!line[2].Equals(null))
{
appendinfo = Convert.ToString(line[2]);
}
if (!string.IsNullOrEmpty(tempvm.SN))
{
if (!appenddict.ContainsKey(tempvm.SN))
{
appenddict.Add(tempvm.SN, appendinfo);
}
ret.Add(tempvm);
}
} //check sn
}
} catch (Exception ex) { }
}
if (pkgdict.Count > 0)
{
try
{
datecond = " ('";
foreach (var item in pkgdict)
{
datecond = datecond + item.Key + "','";
}
datecond = datecond.Substring(0, datecond.Length - 2);
datecond = datecond + ") ";
sql = "select ContainerName,ParentContainerId FROM [InsiteDB].[insite].[Container] where ParentContainerId in <datecond>";
sql = sql.Replace("<datecond>", datecond);
dbret = DBUtility.ExeRealMESSqlWithRes(sql, null);
foreach (var line in dbret)
{
var sn = Convert.ToString(line[0]);
var pid = Convert.ToString(line[1]);
if (sn.Length <= 7 && !excludsndict.ContainsKey(sn) && pkgdict.ContainsKey(pid))
{
excludsndict.Add(sn, true);
var tempvm = new WaferTableItem();
tempvm.SN = Convert.ToString(line[0]);
tempvm.DateCode = pkgdict[pid];
ret.Add(tempvm);
} //end if
} //end foreach
} catch (Exception ex) { }
} //end if
return(ret);
}
private static Dictionary <string, DMRSNVM> UpdateSNStatus(string prodline)
{
var ret = new Dictionary <string, DMRSNVM>();
var dict = new Dictionary <string, string>();
dict.Add("@DMRProdLine", prodline);
var sql = "select distinct SN FROM DMRSNVM where SNStatus <> 'SCRAP' and SNStatus <> 'CLOSED' and DMROAStatus <> 'X' and DMRProdLine=@DMRProdLine";
var dbret = DBUtility.ExeLocalSqlWithRes(sql, dict);
var snlist = new List <string>();
foreach (var line in dbret)
{
snlist.Add(Convert.ToString(line[0]));
}
if (snlist.Count > 0)
{
var sninfo = new List <DMRSNVM>();
var snlistlist = UT.SplitList(snlist, 5000);
foreach (var tempsnlist in snlistlist)
{
if (tempsnlist.Count == 0)
{
break;
}
var sncond = "('" + string.Join("','", tempsnlist) + "')";
sql = @"select distinct c.ContainerName, CASE WHEN (c.STATUS IS NULL) THEN 'NONEXIST'
ELSE CASE WHEN (c.STATUS = 1 AND c.holdReasonId IS NULL) THEN 'ACTIVE'
ELSE CASE WHEN CHARINDEX('scrap',ws.WorkflowStepName) > 0 THEN 'SCRAP'
ELSE CASE WHEN (c.STATUS = 1 AND c.holdReasonId IS NOT NULL) THEN 'HOLD'
ELSE CASE WHEN (c.STATUS = 2 AND c.qty > 0 AND ws.workflowstepname <> 'SHIPPING') THEN 'CLOSED'
ELSE CASE WHEN (c.STATUS = 2 AND c.qty = 0) THEN 'SCRAP'
ELSE CASE WHEN (c.STATUS = 4) THEN 'ISSUED'
ELSE CASE WHEN (ws.workflowstepname = 'SHIPPING' AND c.qty > 0 AND c.STATUS = 2) THEN 'SHIPPING' ELSE 'UNKNOW'
END
END
END
END
END
END
END
END SNStatus ,jo.MfgOrderName
,pb.ProductName,wb.WorkflowName CRTWFName,ws.WorkflowStepName CRTWFStepName,c.LastActivityDateGMT
from InsiteDB.insite.Container (nolock) c
left join InsiteDB.insite.MfgOrder (nolock) jo on c.MfgOrderId = jo.MfgOrderId
left join InsiteDB.insite.CurrentStatus (nolock) cs on c.CurrentStatusId = cs.CurrentStatusId
left join InsiteDB.insite.WorkflowStep (nolock) ws on cs.WorkflowStepId = ws.WorkflowStepId
left join InsiteDB.insite.Workflow (nolock) wf on ws.WorkflowId = wf.WorkflowId
left join InsiteDB.insite.WorkflowBase (nolock) wb on wf.WorkflowBaseId = wb.WorkflowBaseId
left join [InsiteDB].[insite].[Product] (nolock) pd on pd.ProductId = c.ProductId
left join [InsiteDB].[insite].[ProductBase] (nolock) pb on pb.ProductBaseId = pd.ProductBaseId
where c.ContainerName in <sncond> and Len(c.ContainerName) = 7";
sql = sql.Replace("<sncond>", sncond);
dbret = DBUtility.ExeRealMESSqlWithRes(sql);
foreach (var l in dbret)
{
var tempvm = new DMRSNVM();
tempvm.SN = O2S(l[0]).ToUpper().Trim();
tempvm.SNStatus = O2S(l[1]);
tempvm.JO = O2S(l[2]);
tempvm.PN = O2S(l[3]);
tempvm.WorkFlow = O2S(l[4]);
tempvm.WorkFlowStep = O2S(l[5]);
tempvm.DMRDate = O2T(l[6]);
sninfo.Add(tempvm);
if (!ret.ContainsKey(tempvm.SN))
{
ret.Add(tempvm.SN, tempvm);
}
} //end foreach
} //end foreach
dict = new Dictionary <string, string>();
sql = @"update DMRSNVM set SNStatus=@SNStatus,JO=@JO,PN=@PN,WorkFlow=@WorkFlow,WorkFlowStep=@WorkFlowStep where SN=@SN and DMRProdLine=@DMRProdLine";
foreach (var s in sninfo)
{
dict = new Dictionary <string, string>();
dict.Add("@SNStatus", s.SNStatus);
dict.Add("@JO", s.JO);
dict.Add("@PN", s.PN);
dict.Add("@WorkFlow", s.WorkFlow);
dict.Add("@WorkFlowStep", s.WorkFlowStep);
dict.Add("@SN", s.SN);
dict.Add("@DMRProdLine", prodline);
DBUtility.ExeLocalSqlNoRes(sql, dict);
}
}
return(ret);
}
public static List <WaferTableItem> SolveCableSN(List <WaferTableItem> desdata, Dictionary <string, bool> cablesndict)
{
var excludsndict = new Dictionary <string, bool>();
var ret = new List <WaferTableItem>();
var tempres = new List <WaferTableItem>();
StringBuilder sb = new StringBuilder(10 * (desdata.Count + 5));
sb.Append("('");
foreach (var item in desdata)
{
sb.Append(item.SN + "','");
}
var tempstr = sb.ToString();
var sncond = tempstr.Substring(0, tempstr.Length - 2) + ")";
var sql = @"select a.ToContainer,a.FromContainer,a.FromPNDescription from [PDMS].[dbo].[ComponentIssueSummary] a
inner join (SELECT COUNT(*) as tcnt,ToContainer FROM [PDMS].[dbo].[ComponentIssueSummary] where ToContainer in <sncond> and LEN(FromContainer) = 7 group by ToContainer) b on a.ToContainer = b.ToContainer
where b.tcnt >= 2 and LEN(a.FromContainer) = 7 and a.ToContainer in <sncond> order by a.ToContainer";
sql = sql.Replace("<sncond>", sncond);
var dbret = DBUtility.ExeMESReportSqlWithRes(sql);
foreach (var line in dbret)
{
var tempvm = new WaferTableItem();
tempvm.SN = Convert.ToString(line[1]);
tempvm.DateCode = Convert.ToString(line[0]);
var fromdesc = Convert.ToString(line[2]);
if (fromdesc.ToUpper().Contains("DIE,LF,ASIC"))
{
continue;
}
if (!excludsndict.ContainsKey(tempvm.SN))
{
excludsndict.Add(tempvm.SN, true);
tempres.Add(tempvm);
if (!cablesndict.ContainsKey(tempvm.SN))
{
cablesndict.Add(tempvm.SN, true);
}
if (!cablesndict.ContainsKey(tempvm.DateCode))
{
cablesndict.Add(tempvm.DateCode, true);
}
}//check sn
}
foreach (var item in desdata)
{
if (!cablesndict.ContainsKey(item.SN))
{
ret.Add(item);
}
}
ret.AddRange(tempres);
return(ret);
}
private static void UpdateDMRSN(string prodline, Controller ctrl)
{
var dict = new Dictionary <string, string>();
var sql = "SELECT distinct [DMR_ID],[Prod_Line],[Created_at],[Created_By],[File_URL] FROM [eDMR].[dbo].[DMR_Detail_List_View] where Prod_Line = @Prod_Line and File_URL is not null order by [Created_at] asc";
dict.Add("@Prod_Line", prodline);
var latestdate = RetrieveLatestDMRDate(prodline);
if (!string.IsNullOrEmpty(latestdate))
{
sql = @"SELECT distinct [DMR_ID],[Prod_Line],[Created_at],[Created_By],[File_URL] FROM [eDMR].[dbo].[DMR_Detail_List_View]
where Prod_Line = @Prod_Line and Created_at > @StartDate and Created_at <= @EndDate and File_URL is not null";
dict.Add("@StartDate", latestdate);
dict.Add("@EndDate", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
}
var dmrlist = new List <DMRSNVM>();
var dbret = DBUtility.ExeDMRSqlWithRes(sql, dict);
foreach (var line in dbret)
{
try
{
var tempvm = new DMRSNVM();
tempvm.DMRID = Convert.ToString(line[0]);
tempvm.DMRProdLine = Convert.ToString(line[1]);
tempvm.DMRDate = Convert.ToDateTime(line[2]).ToString("yyyy-MM-dd HH:mm:ss");
tempvm.DMRCreater = Convert.ToString(line[3]);
tempvm.DMRFileURL = Convert.ToString(line[4]);
dmrlist.Add(tempvm);
}
catch (Exception ex) { }
}
sql = @"insert into DMRSNVM(DMRID,DMRProdLine,DMRDate,DMRCreater,SN,SNFailure) values(@DMRID,@DMRProdLine,@DMRDate,@DMRCreater,@SN,@SNFailure)";
foreach (var vm in dmrlist)
{
var lfs = ExternalDataCollector.DownloadShareFile(vm.DMRFileURL, ctrl);
if (!string.IsNullOrEmpty(lfs) && ExternalDataCollector.FileExist(ctrl, lfs))
{
var sndatalist = ExternalDataCollector.RetrieveDataFromExcelWithAuth(ctrl, lfs, null, 5);
foreach (var l in sndatalist)
{
var sn = l[0];
if (sn.Length != 7)
{
continue;
}
var failure = "N/A";
if (l.Count >= 3)
{
failure = l[2];
}
dict = new Dictionary <string, string>();
dict.Add("@DMRID", vm.DMRID);
dict.Add("@DMRProdLine", vm.DMRProdLine);
dict.Add("@DMRDate", vm.DMRDate);
dict.Add("@DMRCreater", vm.DMRCreater);
dict.Add("@SN", sn);
dict.Add("@SNFailure", failure);
DBUtility.ExeLocalSqlNoRes(sql, dict);
}
}
}
}
public static List <DMRSNVM> RetrieveSNWorkFlow(string sn)
{
var ret = new List <DMRSNVM>();
var dict = new Dictionary <string, string>();
dict.Add("@ContainerName", sn);
var sql = @"SELECT distinct c.ContainerName as SerialName,pb.ProductName,wb.WorkflowName,ws.WorkflowStepName,jo.MfgOrderName ,hml.MfgDate
FROM InsiteDB.insite.container c with (nolock)
left join InsiteDB.insite.historyMainline hml with (nolock) on c.containerId = hml.containerId
left join InsiteDB.insite.MoveHistory mv with (nolock) on mv.HistoryMainlineId= hml.HistoryMainlineId
left join InsiteDB.insite.workflowstep ws(nolock) on ws.WorkflowStepId = hml.WorkflowStepId
left join InsiteDB.insite.Workflow (nolock) wf on ws.WorkflowId = wf.WorkflowId
left join InsiteDB.insite.WorkflowBase (nolock) wb on wf.WorkflowBaseId = wb.WorkflowBaseId
left join InsiteDB.insite.MfgOrder (nolock) jo on jo.MfgOrderId = mv.MfgOrderId
left join InsiteDB.insite.product p with (nolock) on hml.productId = p.productId
left join InsiteDB.insite.productBase pb with (nolock) on p.productBaseId = pb.productBaseId
where c.ContainerName = @ContainerName and mv.MoveInTime is not null order by hml.MfgDate asc" ;
var dbret = DBUtility.ExeRealMESSqlWithRes(sql, dict);
foreach (var l in dbret)
{
var tempvm = new DMRSNVM();
tempvm.SN = O2S(l[0]);
tempvm.PN = O2S(l[1]);
tempvm.WorkFlow = O2S(l[2]);
tempvm.WorkFlowStep = O2S(l[3]);
tempvm.JO = O2S(l[4]);
tempvm.DMRDate = O2T(l[5]);
ret.Add(tempvm);
}
sql = @"select distinct c.ContainerName,pb.ProductName,wb.WorkflowName CRTWFName,ws.WorkflowStepName CRTWFStepName, jo.MfgOrderName,c.LastActivityDateGMT
from InsiteDB.insite.Container (nolock) c
left join InsiteDB.insite.MfgOrder (nolock) jo on c.MfgOrderId = jo.MfgOrderId
left join InsiteDB.insite.CurrentStatus (nolock) cs on c.CurrentStatusId = cs.CurrentStatusId
left join InsiteDB.insite.WorkflowStep (nolock) ws on cs.WorkflowStepId = ws.WorkflowStepId
left join InsiteDB.insite.Workflow (nolock) wf on ws.WorkflowId = wf.WorkflowId
left join InsiteDB.insite.WorkflowBase (nolock) wb on wf.WorkflowBaseId = wb.WorkflowBaseId
left join InsiteDB.insite.product p with (nolock) on c.ProductId = p.productId
left join InsiteDB.insite.productBase pb with (nolock) on p.productBaseId = pb.productBaseId
where c.ContainerName = @ContainerName";
dbret = DBUtility.ExeRealMESSqlWithRes(sql, dict);
foreach (var l in dbret)
{
var tempvm = new DMRSNVM();
tempvm.SN = O2S(l[0]);
tempvm.PN = O2S(l[1]);
tempvm.WorkFlow = O2S(l[2]);
tempvm.WorkFlowStep = O2S(l[3]);
tempvm.JO = O2S(l[4]);
tempvm.DMRDate = O2T(l[5]);
if (string.Compare(ret[ret.Count - 1].WorkFlowStep, tempvm.WorkFlowStep, true) != 0)
{
ret.Add(tempvm);
}
}
return(ret);
}
private static bool UpdateDMRStep(string prodline, Dictionary <string, DMRSNVM> snlaststep)
{
var dict = new Dictionary <string, string>();
dict.Add("@DMRProdLine", prodline);
var sql = "select distinct SN FROM DMRSNVM where DMRProdLine=@DMRProdLine and DMROAStatus <> 'X'";
var dbret = DBUtility.ExeLocalSqlWithRes(sql, dict);
var snlist = new List <string>();
foreach (var line in dbret)
{
snlist.Add(Convert.ToString(line[0]).ToUpper().Trim());
}
if (snlist.Count > 0)
{
var snstepdict = new Dictionary <string, DMRSNVM>();
foreach (var s in snlist)
{
snstepdict.Add(s, new DMRSNVM());
}
var snworkflowdict = new Dictionary <string, List <DMRSNVM> >();
var snlistlist = UT.SplitList(snlist, 5000);
foreach (var tempsnlist in snlistlist)
{
if (tempsnlist.Count == 0)
{
break;
}
var sncond = "('" + string.Join("','", tempsnlist) + "')";
sql = @"SELECT distinct c.ContainerName as SerialName,ws.WorkflowStepName ,hml.MfgDate
FROM InsiteDB.insite.container c with (nolock)
left join InsiteDB.insite.historyMainline hml with (nolock) on c.containerId = hml.containerId
left join InsiteDB.insite.MoveHistory mv with (nolock) on mv.HistoryMainlineId= hml.HistoryMainlineId
left join InsiteDB.insite.workflowstep ws(nolock) on ws.WorkflowStepId = hml.WorkflowStepId
where c.ContainerName in <sncond> and mv.MoveInTime is not null and ws.WorkflowStepName is not null
and hml.MfgDate is not null order by SerialName,hml.MfgDate asc";
sql = sql.Replace("<sncond>", sncond);
dbret = DBUtility.ExeRealMESSqlWithRes(sql);
if (dbret.Count == 0)
{
return(false);
}
//split sn history workflowstep
foreach (var l in dbret)
{
try
{
var sn = O2S(l[0]).ToUpper().Trim();
var step = O2S(l[1]);
var ustep = step.ToUpper();
var dt = O2T(l[2]);
var tempvm = new DMRSNVM();
tempvm.SN = sn;
tempvm.WorkFlowStep = step;
tempvm.DMRDate = dt;
if (snworkflowdict.ContainsKey(sn))
{
snworkflowdict[sn].Add(tempvm);
}
else
{
var templist = new List <DMRSNVM>();
templist.Add(tempvm);
snworkflowdict.Add(sn, templist);
}
}
catch (Exception ex) { }
} //end foreach
} //end foreach
//append current workflowstep
foreach (var wkv in snworkflowdict)
{
if (snlaststep.ContainsKey(wkv.Key))
{
if (string.Compare(wkv.Value[wkv.Value.Count - 1].WorkFlowStep, snlaststep[wkv.Key].WorkFlowStep, true) != 0)
{
wkv.Value.Add(snlaststep[wkv.Key]);
}
}
}
//scan workflow step
foreach (var wkv in snworkflowdict)
{
var previousstep = new DMRSNVM();
foreach (var item in wkv.Value)
{
var sn = item.SN;
var step = item.WorkFlowStep;
var ustep = step.ToUpper();
var dt = item.DMRDate;
if (!string.IsNullOrEmpty(snstepdict[sn].DMRRepairStep) &&
snstepdict[sn].DMRRepairStep.ToUpper().Contains("COMPONENTS") &&
snstepdict[sn].DMRRepairStep.ToUpper().Contains("REMOVE"))
{
snstepdict[sn].DMRRepairStep = step;
//snstepdict[sn].DMRRepairTime = dt;
}
if (!string.IsNullOrEmpty(snstepdict[sn].DMRStoreStep) &&
string.IsNullOrEmpty(snstepdict[sn].DMRRepairStep))
{
snstepdict[sn].DMRRepairStep = step;
snstepdict[sn].DMRRepairTime = dt;
}
if (!string.IsNullOrEmpty(snstepdict[sn].DMRStoreStep) &&
ustep.Contains("MAIN") && ustep.Contains("STORE") &&
string.IsNullOrEmpty(snstepdict[sn].DMRReturnStep))
{
snstepdict[sn].DMRReturnStep = step;
snstepdict[sn].DMRReturnTime = dt;
}
if (ustep.Contains("EQ") && ustep.Contains("INVENTORY"))
{
snstepdict[sn].DMRStoreStep = step;
snstepdict[sn].DMRStoreTime = dt;
snstepdict[sn].DMRRepairStep = "";
snstepdict[sn].DMRRepairTime = "";
snstepdict[sn].DMRReturnStep = "";
snstepdict[sn].DMRReturnTime = "";
if (string.Compare(previousstep.SN, sn) == 0 && !string.IsNullOrEmpty(previousstep.DMRStartStep))
{
snstepdict[sn].DMRStartStep = previousstep.DMRStartStep;
snstepdict[sn].DMRStartTime = previousstep.DMRStartTime;
}
}
previousstep.SN = sn;
previousstep.DMRStartStep = step;
previousstep.DMRStartTime = dt;
} //end foreach
} //end foreach
dict = new Dictionary <string, string>();
sql = @"update DMRSNVM set DMRStartStep=@DMRStartStep,DMRStartTime=@DMRStartTime,DMRStoreStep=@DMRStoreStep,DMRStoreTime=@DMRStoreTime
,DMRRepairStep=@DMRRepairStep,DMRRepairTime=@DMRRepairTime,DMRReturnStep=@DMRReturnStep,DMRReturnTime=@DMRReturnTime where SN=@SN and DMRProdLine=@DMRProdLine";
foreach (var kv in snstepdict)
{
dict = new Dictionary <string, string>();
dict.Add("@DMRStartStep", kv.Value.DMRStartStep);
dict.Add("@DMRStartTime", kv.Value.DMRStartTime);
dict.Add("@DMRStoreStep", kv.Value.DMRStoreStep);
dict.Add("@DMRStoreTime", kv.Value.DMRStoreTime);
dict.Add("@DMRRepairStep", kv.Value.DMRRepairStep);
dict.Add("@DMRRepairTime", kv.Value.DMRRepairTime);
dict.Add("@DMRReturnStep", kv.Value.DMRReturnStep);
dict.Add("@DMRReturnTime", kv.Value.DMRReturnTime);
dict.Add("@SN", kv.Key);
dict.Add("@DMRProdLine", prodline);
DBUtility.ExeLocalSqlNoRes(sql, dict);
}
}
return(true);
}
public static void UpdateBuyRequest(AssetBuyInfoVM buy)
{
var sql = "update AssetBuyInfo set ";
var param = new Dictionary <string, string>();
if (!string.IsNullOrEmpty(buy.EngName))
{
sql += " EngName = @EngName, ";
param.Add("@EngName", buy.EngName);
}
if (!string.IsNullOrEmpty(buy.ChName))
{
sql += " ChName = @ChName, ";
param.Add("@ChName", buy.ChName);
}
if (!string.IsNullOrEmpty(buy.UnitPrice))
{
sql += " UnitPrice = @UnitPrice, ";
param.Add("@UnitPrice", buy.UnitPrice);
}
if (!string.IsNullOrEmpty(buy.Brand))
{
sql += " Brand = @Brand, ";
param.Add("@Brand", buy.Brand);
}
if (!string.IsNullOrEmpty(buy.Model))
{
sql += " Model = @Model, ";
param.Add("@Model", buy.Model);
}
if (!string.IsNullOrEmpty(buy.OriginCountry))
{
sql += " OriginCountry = @OriginCountry, ";
param.Add("@OriginCountry", buy.OriginCountry);
}
if (!string.IsNullOrEmpty(buy.Picture))
{
sql += " Picture = @Picture, ";
param.Add("@Picture", buy.Picture);
}
if (!string.IsNullOrEmpty(buy.Purpose))
{
sql += " Purpose = @Purpose, ";
param.Add("@Purpose", buy.Purpose);
}
if (!string.IsNullOrEmpty(buy.Functions))
{
sql += " Functions = @Functions, ";
param.Add("@Functions", buy.Functions);
}
if (!string.IsNullOrEmpty(buy.WorkPrinciple))
{
sql += " WorkPrinciple = @WorkPrinciple, ";
param.Add("@WorkPrinciple", buy.WorkPrinciple);
}
if (!string.IsNullOrEmpty(buy.CorporatePurposes))
{
sql += " CorporatePurposes = @CorporatePurposes, ";
param.Add("@CorporatePurposes", buy.CorporatePurposes);
}
if (!string.IsNullOrEmpty(buy.Status))
{
sql += " Status = @Status, ";
param.Add("@Status", buy.Status);
}
sql += " UpdateAt = @UpdateAt where ID = @ID";
param.Add("@UpdateAt", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
param.Add("@ID", buy.ID);
DBUtility.ExeLocalSqlNoRes(sql, param);
}
public static Dictionary <string, AssetBorrowHistoryVM> GetBorrowList(string key = "", string status = "")
{
var sql = @"select abh.*, ai.CN, ai.EnglishName, ai.ChineseName
from AssetBorrowHistory as abh
left join AssetInfo as ai on abh.AssetID = ai.ID
where abh.IsReturn <> 0";
var param = new Dictionary <string, string>();
if (!string.IsNullOrEmpty(key))
{
sql += " and (ai.EnglishName like @key or ai.ChineseName like @key or ai.CN like @key)";
param.Add("@key", "%" + key + "%");
}
if (!string.IsNullOrEmpty(status))
{
sql += " and abh.IsReturn = @Status ";
param.Add("@Status", status);
}
sql += " Order by abh.IsReturn, abh.UpdateAt Desc";
var dbret = DBUtility.ExeLocalSqlWithRes(sql, param);
var res = new Dictionary <string, AssetBorrowHistoryVM>();
if (dbret.Count > 0)
{
foreach (var item in dbret)
{
var asset_tmp = new AssetVM();
asset_tmp.ID = Convert.ToString(item[2]);
asset_tmp.CN = Convert.ToString(item[15]);
asset_tmp.EnglishName = Convert.ToString(item[16]);
asset_tmp.ChineseName = Convert.ToString(item[17]);
if (res.ContainsKey(Convert.ToString(item[1])))
{
res[Convert.ToString(item[1])].AssetList.Add(asset_tmp);
}
else
{
var tmp = new AssetBorrowHistoryVM();
tmp.ID = Convert.ToString(item[0]);
tmp.RequestID = Convert.ToString(item[1]);
tmp.BorrowUser = Convert.ToString(item[3]);
tmp.ProjectNo = Convert.ToString(item[4]);
tmp.IsLong = Convert.ToString(item[5]);
tmp.StartDate = Convert.ToString(item[6]);
tmp.EndDate = Convert.ToString(item[7]);
tmp.AdminConfirm = Convert.ToString(item[8]);
tmp.BorrowComment = Convert.ToString(item[9]);
tmp.IsReturn = Convert.ToString(item[10]);
tmp.ReturnComment = Convert.ToString(item[11]);
tmp.ReturnAt = Convert.ToString(item[12]);
tmp.CreateAt = Convert.ToString(item[13]);
tmp.UpdateAt = Convert.ToString(item[14]);
var asset_list = new List <AssetVM>();
asset_list.Add(asset_tmp);
tmp.AssetList = asset_list;
res.Add(Convert.ToString(item[0]), tmp);
}
}
}
return(res);
}
public static Dictionary <string, AssetVM> GetAssetList(string cn = "", string name = "", string status = "", string ids = "")
{
var sql = @"select ai.*, abh.*
from AssetInfo as ai
left join AssetBorrowHistory as abh on ai.ID = abh.AssetID
where 1 = 1";
var param = new Dictionary <string, string>();
if (!string.IsNullOrEmpty(name))
{
sql += " and (ai.EnglishName like @name or ai.ChineseName like @name) ";
param.Add("@name", "%" + name + "%");
}
if (!string.IsNullOrEmpty(cn))
{
sql += " and ai.CN like @CN ";
param.Add("@CN", "%" + cn + "%");
}
if (!string.IsNullOrEmpty(status))
{
sql += " and ai.EquipmentStatus = @Status ";
param.Add("@Status", status);
}
if (!string.IsNullOrEmpty(ids))
{
sql += " and ai.ID in <#ids>";
sql = sql.Replace("<#ids>", ids);
}
sql += " order by ai.ID Desc";
var dbret = DBUtility.ExeLocalSqlWithRes(sql, param);
var res = new Dictionary <string, AssetVM>();
var title_info = AssetVMProperty();
if (dbret.Count > 0)
{
foreach (var item in dbret)
{
var tmp = new AssetBorrowHistoryVM();
tmp.ID = Convert.ToString(item[61]);
tmp.RequestID = Convert.ToString(item[62]);
tmp.AssetID = Convert.ToString(item[63]);
tmp.BorrowUser = Convert.ToString(item[64]);
tmp.ProjectNo = Convert.ToString(item[65]);
tmp.IsLong = Convert.ToString(item[66]);
tmp.StartDate = Convert.ToString(item[67]);
tmp.EndDate = Convert.ToString(item[68]);
tmp.AdminConfirm = Convert.ToString(item[69]);
tmp.BorrowComment = Convert.ToString(item[70]);
tmp.IsReturn = Convert.ToString(item[71]);
tmp.ReturnComment = Convert.ToString(item[72]);
tmp.ReturnAt = Convert.ToString(item[73]);
tmp.CreateAt = Convert.ToString(item[74]);
tmp.UpdateAt = Convert.ToString(item[75]);
if (res.ContainsKey(Convert.ToString(item[0])))
{
res[Convert.ToString(item[0])].BorrowHistory.Add(tmp);
}
else
{
var asset_tmp = new AssetVM();
asset_tmp.ID = Convert.ToString(item[0]);
PropertyInfo[] properties = typeof(AssetVM).GetProperties();
var idx = 1;
foreach (var title in title_info)
{
SetPropertyValue(title, Convert.ToString(item[idx]), asset_tmp);
idx++;
}
asset_tmp.BorrowHistory.Add(tmp);
res.Add(Convert.ToString(item[0]), asset_tmp);
}
}
var check_history = AssetCheckHistoryVM.GetAssetCheckHistory(cn, name, status);
if (check_history.Count > 0)
{
foreach (var item in res)
{
if (check_history.ContainsKey(item.Key))
{
item.Value.CheckHistory = check_history[item.Key];
}
}
}
}
return(res);
}
public static List <ScrapTableItem> RetrievePNBySNDC(List <ScrapTableItem> inputdata)
{
var pnpnkeymap = PnMainVM.PNPNKeyMap();
var hasdatecode = false;
var hassn = false;
var ret = new List <ScrapTableItem>();
var datecond = " ('";
foreach (var item in inputdata)
{
if (!string.IsNullOrEmpty(item.DateCode))
{
datecond = datecond + item.DateCode + "','";
hasdatecode = true;
}
}
datecond = datecond.Substring(0, datecond.Length - 2);
datecond = datecond + ") ";
var sncond = " ('";
foreach (var item in inputdata)
{
if (!string.IsNullOrEmpty(item.SN))
{
sncond = sncond + item.SN + "','";
hassn = true;
}
}
sncond = sncond.Substring(0, sncond.Length - 2);
sncond = sncond + ") ";
var sql = "";
if (hasdatecode && hassn)
{
sql = "select c.ContainerName,pb.ProductName,c.DateCode from [InsiteDB].[insite].[Container] c (nolock)"
+ " left join[InsiteDB].[insite].[Product] p(nolock) on p.ProductId = c.ProductId"
+ " left join[InsiteDB].[insite].[ProductBase] pb (nolock) on pb.ProductBaseId = p.ProductBaseId"
+ " where c.ContainerName in <sncond> or c.DateCode in <datecond>";
}
else if (hasdatecode)
{
sql = "select c.ContainerName,pb.ProductName,c.DateCode from [InsiteDB].[insite].[Container] c (nolock)"
+ " left join[InsiteDB].[insite].[Product] p(nolock) on p.ProductId = c.ProductId"
+ " left join[InsiteDB].[insite].[ProductBase] pb (nolock) on pb.ProductBaseId = p.ProductBaseId"
+ " where c.DateCode in <datecond>";
}
else
{
sql = "select c.ContainerName,pb.ProductName,c.DateCode from [InsiteDB].[insite].[Container] c (nolock)"
+ " left join[InsiteDB].[insite].[Product] p(nolock) on p.ProductId = c.ProductId"
+ " left join[InsiteDB].[insite].[ProductBase] pb (nolock) on pb.ProductBaseId = p.ProductBaseId"
+ " where c.ContainerName in <sncond>";
}
sql = sql.Replace("<sncond>", sncond).Replace("<datecond>", datecond);
var dbret = DBUtility.ExeRealMESSqlWithRes(sql);
foreach (var line in dbret)
{
var tempvm = new ScrapTableItem();
tempvm.SN = Convert.ToString(line[0]);
tempvm.PN = Convert.ToString(line[1]);
if (!string.IsNullOrEmpty(tempvm.SN) && !string.IsNullOrEmpty(tempvm.PN))
{
if (line[2] == null)
{
tempvm.DateCode = "";
}
else
{
tempvm.DateCode = Convert.ToString(line[2]);
}
if (pnpnkeymap.ContainsKey(tempvm.PN))
{
tempvm.PNKey = pnpnkeymap[tempvm.PN];
}
ret.Add(tempvm);
}//end if
}
return(ret);
}