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