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 <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);
}
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 <SNApertureSizeVM> LoadData(List <string> snlist, Controller ctrl)
{
var syscfg = CfgUtility.GetSysConfig(ctrl);
var bifolder = syscfg["BIITHFOLDER"];
var allfiles = GetAllFiles(bifolder, ctrl);
var snwfdict = UT.GetWaferFromSN(snlist);
var ret = new List <SNApertureSizeVM>();
foreach (var sn in snlist)
{
SNApertureSizeVM tempvm = null;
var fs = "";
foreach (var f in allfiles)
{
if (f.ToUpper().Contains(sn.ToUpper()) && f.ToUpper().Contains("_PRE.TXT"))
{
fs = f;
break;
}
}
if (string.IsNullOrEmpty(fs))
{
continue;
}
var bifile = ExternalDataCollector.DownloadShareFile(fs, ctrl);
if (!string.IsNullOrEmpty(bifile))
{
var alline = System.IO.File.ReadAllLines(bifile);
var idx = 1;
foreach (var line in alline)
{
if (line.ToUpper().Contains("CHANNEL"))
{
tempvm = new SNApertureSizeVM();
tempvm.SN = sn.ToUpper();
tempvm.CH = "CH" + idx;
if (snwfdict.ContainsKey(tempvm.SN))
{
tempvm.Wafer = snwfdict[tempvm.SN];
}
ret.Add(tempvm);
idx++;
continue;
}
if (tempvm != null && tempvm.IthList.Count < 14)
{
var items = line.Split(new string[] { " ", "\t" }, StringSplitOptions.RemoveEmptyEntries);
tempvm.IthList.Add(UT.O2D(items[1]));
tempvm.PwrList.Add(UT.O2D(items[2]));
}
} //end foreach
} //end if
foreach (var item in ret)
{
if (item.IthList.Count == 14 && item.PwrList.Count == 14)
{
var rest = Fit.Line(item.IthList.ToArray(), item.PwrList.ToArray());
item.Intercept = rest.Item1.ToString();
item.IthSlope = rest.Item2.ToString();
item.Ith = (Math.Abs(rest.Item1 / rest.Item2) / 1000.0).ToString();
}
}
var wflist = new List <string>();
foreach (var kv in snwfdict)
{
var wf = kv.Value.ToUpper().Trim();
if (!wflist.Contains(wf))
{
wflist.Add(wf);
}
}
var wfapdict = new Dictionary <string, string>();
foreach (var w in wflist)
{
var apconst = ProbeTestData.PrepareAPConst2162(w);
wfapdict.Add(w, apconst);
}
foreach (var item in ret)
{
if (!string.IsNullOrEmpty(item.Wafer) && wfapdict.ContainsKey(item.Wafer))
{
item.ApertureConst = wfapdict[item.Wafer];
if (!string.IsNullOrEmpty(item.ApertureConst) && !string.IsNullOrEmpty(item.Ith))
{
var apconst = UT.O2D(item.ApertureConst);
var ith = UT.O2D(item.Ith);
item.ApertureSize = (ith * 7996.8 + apconst).ToString();
}
}
}
}
return(ret);
}