Пример #1
0
        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);
        }
Пример #2
0
        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);
        }
Пример #3
0
        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);
        }
Пример #4
0
        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);
        }
Пример #5
0
        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();
                }
            }
        }
Пример #6
0
        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);
        }
Пример #7
0
        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);
        }
Пример #8
0
        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);
        }
Пример #9
0
        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);
        }
Пример #10
0
        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);
        }
Пример #11
0
        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);
        }
Пример #12
0
        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
        }
Пример #13
0
        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);
        }
Пример #14
0
        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);
        }
Пример #15
0
        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);
        }
Пример #16
0
        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);
        }