public static Dictionary <string, string> GetJOFromSN(List <string> snlist)
{
var ret = new Dictionary <string, string>();
var sql = @"select distinct c.ContainerName,jo.MfgOrderName from InsiteDB.insite.Container (nolock) c
left join InsiteDB.insite.MfgOrder (nolock) jo on c.MfgOrderId = jo.MfgOrderId
where c.ContainerName in <sncond>";
var sncond = "('" + string.Join("','", snlist) + "')";
sql = sql.Replace("<sncond>", sncond);
var dbret = DBUtility.ExeRealMESSqlWithRes(sql);
foreach (var line in dbret)
{
var sn = UT.O2S(line[0]).ToUpper();
var jo = UT.O2S(line[1]).ToUpper();
if (!ret.ContainsKey(sn))
{
ret.Add(sn, jo);
}
}
foreach (var sn in snlist)
{
if (!ret.ContainsKey(sn))
{
ret.Add(sn.ToUpper(), "");
}
}
return(ret);
}
public static List <PNSNFR4Binning> GetPNSNData(List <string> pnlist, string sdate, string edate)
{
var ret = new List <PNSNFR4Binning>();
var sndict = new Dictionary <string, bool>();
var pncond = "('" + string.Join("','", pnlist) + "')";
var sql = @"select top 100000 dc.[ModuleSerialNum],dc.AssemblyPartNum,dc.ModulePartNum,dc.TestTimeStamp,dc.ErrAbbr,dce.[ProductGrade],dce.ProdBinPwrConsumption,dce.ModuleTemp_C,ws.WorkflowStepName from [InsiteDB].[insite].[dce_QuickTest_main] dce (nolock)
left join [InsiteDB].[insite].[dc_QuickTest] dc (nolock) on dc.dc_QuickTestHistoryId = dce.ParentHistoryID
left join InsiteDB.insite.Container c (nolock) on c.containername = dc.[ModuleSerialNum]
left join InsiteDB.insite.currentStatus cs (nolock) on c.currentStatusId = cs.currentStatusId
left join InsiteDB.insite.workflowstep ws(nolock) on cs.WorkflowStepId = ws.WorkflowStepId
where dc.ErrAbbr = 'pass' and dce.ChannelNumber = '0' and dc.[ModuleSerialNum] is not null and dce.CornerID like '1H'
and dce.[ProductGrade] is not null and C.[Status] = 1 and c.HoldReasonId is null
and ( (dc.AssemblyPartNum in <pncond> and dc.TestTimeStamp > '<sdate>' and dc.TestTimeStamp < '<edate>' ) or dc.[ModuleSerialNum] in <pncond> )
order by dc.[ModuleSerialNum],dc.TestTimeStamp desc";
sql = sql.Replace("<pncond>", pncond).Replace("<sdate>", sdate).Replace("<edate>", edate);
var dbret = DBUtility.ExeRealMESSqlWithRes(sql);
foreach (var line in dbret)
{
var sn = UT.O2S(line[0]).ToUpper().Trim();
if (sndict.ContainsKey(sn))
{
continue;
}
sndict.Add(sn, true);
ret.Add(new PNSNFR4Binning(UT.O2S(line[0]), UT.O2S(line[1]), UT.O2S(line[2]), UT.O2T(line[3]), UT.O2S(line[4]), UT.O2S(line[5]), UT.O2S(line[6]), UT.O2S(line[7]), UT.O2S(line[8])));
}
return(ret);
}
public static List <SNProVM> RetrieveWorkFlowData(List <string> SNList)
{
var ret = new List <SNProVM>();
var sncond = "('" + string.Join("','", SNList) + "')";
var sql = @"SELECT distinct c.ContainerName as SerialName,pb.productname,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
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 in <sncond> and mv.MoveInTime is not null order by SerialName,hml.MfgDate asc";
sql = sql.Replace("<sncond>", sncond);
var dbret = DBUtility.ExeRealMESSqlWithRes(sql);
foreach (var line in dbret)
{
try
{
ret.Add(new SNProVM(Convert.ToString(line[0]), Convert.ToString(line[1])
, Convert.ToString(line[2]), Convert.ToDateTime(line[3]).ToString("yyyy-MM-dd HH:mm:ss")));
}
catch (Exception ex) { }
}
return(ret);
}
public static Dictionary <string, string> GetWaferFromSN(List <string> snlist)
{
var ret = new Dictionary <string, string>();
var sncond = "('" + string.Join("','", snlist) + "')";
var sql = @"SELECT distinct c.ContainerName as SerialName,isnull(dc.[ParamValueString],'') as WaferLot
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> ";
sql = sql.Replace("<SNCOND>", sncond);
var dbret = DBUtility.ExeRealMESSqlWithRes(sql);
foreach (var line in dbret)
{
var sn = UT.O2S(line[0]).ToUpper();
var wafer = UT.O2S(line[1]).Replace("A", "");
if (wafer.Length == 12)
{
wafer = wafer.Substring(0, 9);
}
if (!ret.ContainsKey(sn))
{
ret.Add(sn, wafer);
}
}
return(ret);
}
public static void RetrieveTestData(List <ScrapTableItem> scraptable)
{
foreach (var stab in scraptable)
{
var mestablist = RetrieveDCTableFromSn(stab.SN);
var ret = new List <SnTestDataVM>();
foreach (var tabname in mestablist)
{
var sql = "select top 1 dc_<DCTABLE>HistoryId,ModuleSerialNum, ErrAbbr, TestTimeStamp,assemblypartnum,WhichTest,TestStation from "
+ " insite.dc_<DCTABLE> (nolock) where ModuleSerialNum = '<modulesn>'";
sql = sql.Replace("<modulesn>", stab.SN).Replace("<DCTABLE>", tabname);
var dbret = DBUtility.ExeRealMESSqlWithRes(sql);
foreach (var line in dbret)
{
var data = new SnTestDataVM();
data.DataID = Convert.ToString(line[0]);
data.ModuleSerialNum = Convert.ToString(line[1]);
data.ErrAbbr = Convert.ToString(line[2]);
data.TestTime = Convert.ToDateTime(line[3]);
data.PN = Convert.ToString(line[4]);
data.WhichTest = Convert.ToString(line[5]);
data.TestStation = Convert.ToString(line[6]);
data.MESTab = tabname.ToUpper().Trim();
ret.Add(data);
}
}
ret.Sort(delegate(SnTestDataVM d1, SnTestDataVM d2)
{
return(DateTime.Compare(d2.TestTime, d1.TestTime));
});
if (ret.Count > 0)
{
stab.TestData = ret[0];
}
else
{
stab.TestData = new SnTestDataVM();
}
}
}
private static List <string> RetrieveDCTableFromSn(string sn)
{
var ret = new List <string>();
var dctabledict = new Dictionary <string, bool>();
var sql = @" select ddr.DataCollectionDefName from insitedb.insite.DataCollectionDefBase ddr (nolock)
inner join insitedb.insite.TxnMap tm with(noloCK) ON tm.DataCollectionDefinitionBaseId = ddr.DataCollectionDefBaseId
inner join insitedb.insite.spec sp with(nolock) on sp.specid = tm.specid
inner join InsiteDB.insite.WorkflowStep ws (nolock)on ws.specbaseid = sp.specbaseid
inner join InsiteDB.insite.Workflow w (nolock)on w.WorkflowID = ws.WorkflowID
inner join InsiteDB.insite.Product p(nolock) on w.WorkflowBaseId = p.WorkflowBaseId
inner join [InsiteDB].[insite].[Container] c(nolock) on c.ProductId = p.ProductId
where c.ContainerName = '<ContainerName>' and ddr.DataCollectionDefName is not null";
sql = sql.Replace("<ContainerName>", sn);
var dbret = DBUtility.ExeRealMESSqlWithRes(sql);
foreach (var line in dbret)
{
var dc = Convert2Str(line[0]).ToUpper();
if (dc.Length > 4 && dc.Substring(0, 4).Contains("DCD_"))
{
var realdc = "";
if (dc.Contains("DCD_Module_Initialization_0811".ToUpper()))
{
realdc = "initial";
}
else
{
realdc = dc.Substring(4);
}
if (!dctabledict.ContainsKey(realdc))
{
dctabledict.Add(realdc, true);
}
} //end if
} //end foreach
ret.AddRange(dctabledict.Keys);
return(ret);
}
public static List <SnTestDataVM> RetrieveLatestSNTestResult(string sn)
{
var dctablelist = RetrieveDCTableFromSn(sn);
var testdatalist = new List <SnTestDataVM>();
foreach (var dctable in dctablelist)
{
if (dctable.Contains("OQCPARALLEL") ||
dctable.Contains("AOC_MANUALINSPECTION"))
{
continue;
}
var sql = @"select top 1 a.<DCTABLE>HistoryId,a.ModuleSerialNum, a.WhichTest, a.ModuleType, a.ErrAbbr, a.TestTimeStamp, a.TestStation,a.assemblypartnum
from insite.<DCTABLE> a (nolock) where a.ModuleSerialNum = '<ModuleSerialNum>' order by testtimestamp DESC";
sql = sql.Replace("<DCTABLE>", dctable).Replace("<ModuleSerialNum>", sn);
var dbret = DBUtility.ExeRealMESSqlWithRes(sql);
foreach (var item in dbret)
{
var tempdata = new SnTestDataVM(Convert.ToString(item[0]), Convert.ToString(item[1]), Convert.ToString(item[7]), Convert.ToString(item[2])
, Convert.ToString(item[4]), dctable, Convert.ToDateTime(item[5]), Convert.ToString(item[6]));
testdatalist.Add(tempdata);
}
}
testdatalist.Sort(delegate(SnTestDataVM obj1, SnTestDataVM obj2)
{
return(obj2.TestTime.CompareTo(obj1.TestTime));
});
var ret = new List <SnTestDataVM>();
ret.Add(testdatalist[0]);
return(ret);
}
public static List <JODetailVM> LoadSNData(List <string> snlist)
{
var ret = new List <JODetailVM>();
var sncond = "('" + string.Join("','", snlist) + "')";
var sql = @"select distinct c.ContainerName
,case when c.CustomerSerialNum is not null then c.CustomerSerialNum else '' end CustomerSerialNum
,jo.MfgOrderName
,case when c.DateCode is not null then c.DateCode else '' end LotNum
,case when pc.ContainerName is not null then pc.ContainerName else '' end PackageNum
,pb.ProductName,wb.WorkflowName CRTWFName,wf.WorkflowRevision CRTWFRev,ws.WorkflowStepName CRTWFStepName
, 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
,case when hr.HoldReasonName is not null then hr.HoldReasonName else '' end HoldReason
,awb.WorkflowName OrgWFName,awf.WorkflowRevision OrgWFRev,aws.WorkflowStepName OrgWFStepName,hml.MfgDate
,case when lr.LossReasonName is not null then lr.LossReasonName else '' end ScrapReason
,pd.Description
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.Container (nolock) pc on pc.ContainerId = c.ParentContainerId
left join [InsiteDB].[insite].[Product] (nolock) pd on pd.ProductId = c.ProductId
left join [InsiteDB].[insite].[ProductBase] (nolock) pb on pb.ProductBaseId = pd.ProductBaseId
left join InsiteDB.insite.HoldReason (nolock) hr on hr.HoldReasonId = c.HoldReasonId
left join (
select hml.ContainerId,MAX(hml.MfgDate) as MAXTIME from InsiteDB.insite.Container (nolock) c
left join InsiteDB.insite.MfgOrder (nolock) jo on c.MfgOrderId = jo.MfgOrderId
left join InsiteDB.insite.HistoryMainline (nolock) hml on c.ContainerId = hml.ContainerId
where c.ContainerName in <sncond> group by hml.ContainerId
) LatestHML on LatestHML.ContainerId = c.ContainerId
left join InsiteDB.insite.historyMainline hml with (nolock) on hml.ContainerId = LatestHML.ContainerId and hml.MfgDate = LatestHML.MAXTIME
left join InsiteDB.insite.workflowstep aws(nolock) on aws.WorkflowStepId = hml.WorkflowStepId
left join InsiteDB.insite.Workflow (nolock) awf on aws.WorkflowId = awf.WorkflowId
left join InsiteDB.insite.WorkflowBase (nolock) awb on awf.WorkflowBaseId = awb.WorkflowBaseId
left join InsiteDB.insite.ScrapHistoryDetails (nolock) shd on shd.ContainerId= c.ContainerId
left join [InsiteDB].[insite].[LossReason] (nolock) lr on lr.LossReasonId = shd.ReasonCodeId
where c.ContainerName in <sncond> and Len(c.ContainerName) = 7 order by jo.MfgOrderName,c.ContainerName";
sql = sql.Replace("<sncond>", sncond);
var dbret = DBUtility.ExeRealMESSqlWithRes(sql);
foreach (var l in dbret)
{
try
{
ret.Add(new JODetailVM(ts(l[0]), ts(l[1]), ts(l[2]), ts(l[3]), ts(l[4]), ts(l[5]), ts(l[6])
, ts(l[7]), ts(l[8]), ts(l[9]), ts(l[10]), ts(l[11])
, ts(l[12]), ts(l[13]), Convert.ToDateTime(l[14]).ToString("yyyy-MM-dd HH:mm:ss"), ts(l[15]), ts(l[16])));
}
catch (Exception ex) { }
}
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 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 <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);
}
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
}
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 <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 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);
}
