Exemplo n.º 1
0
        public static List <object> RetrieveTestData(string prodline, Dictionary <string, bool> sndict)
        {
            var r = new List <object>();

            var dict = new Dictionary <string, string>();

            dict.Add("@DMRProdLine", prodline);

            var snlist = sndict.Keys.ToList();
            var sncond = "('" + string.Join("','", snlist) + "')";
            var sql    = "select WhichTest,Failure,SN,TestTime from DMRSNTestData where DMRProdLine = @DMRProdLine and SN in <sncond> order by SN,TestTime desc";

            sql = sql.Replace("<sncond>", sncond);
            var dbret = DBUtility.ExeLocalSqlWithRes(sql, dict);

            var whichcntdict = new Dictionary <string, DMRSNTestData>();
            var faillist     = new List <DMRSNTestData>();

            foreach (var l in dbret)
            {
                var ts   = O2S(l[0]);
                var fail = O2S(l[1]);
                var sn   = O2S(l[2]);
                var tt   = O2T(l[3]);

                var pass = true;
                if (string.Compare(fail, "pass", true) != 0)
                {
                    pass = false;

                    var tempvm = new DMRSNTestData();
                    tempvm.SN        = sn;
                    tempvm.WhichTest = ts;
                    tempvm.Failure   = fail;
                    tempvm.TestTime  = tt;
                    faillist.Add(tempvm);
                }

                if (whichcntdict.ContainsKey(ts))
                {
                    if (pass)
                    {
                        whichcntdict[ts].PassCnt += 1;
                    }
                    else
                    {
                        whichcntdict[ts].FailCnt += 1;
                    }
                }
                else
                {
                    var tempvm = new DMRSNTestData();
                    tempvm.WhichTest = ts;
                    if (pass)
                    {
                        tempvm.PassCnt += 1;
                    }
                    else
                    {
                        tempvm.FailCnt += 1;
                    }
                    whichcntdict.Add(ts, tempvm);
                }
            }

            r.Add(whichcntdict.Values.ToList());
            r.Add(faillist);

            return(r);
        }
Exemplo n.º 2
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);
        }
Exemplo n.º 3
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);
        }
Exemplo n.º 4
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
        }
Exemplo n.º 5
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);
        }
Exemplo n.º 6
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);
        }
Exemplo n.º 7
0
        public static List <WaferTableItem> SolveCableSN(List <WaferTableItem> desdata, Dictionary <string, bool> cablesndict)
        {
            var excludsndict = new Dictionary <string, bool>();

            var ret = new List <WaferTableItem>();

            var tempres = new List <WaferTableItem>();

            StringBuilder sb = new StringBuilder(10 * (desdata.Count + 5));

            sb.Append("('");
            foreach (var item in desdata)
            {
                sb.Append(item.SN + "','");
            }
            var tempstr = sb.ToString();
            var sncond  = tempstr.Substring(0, tempstr.Length - 2) + ")";

            var sql = @"select a.ToContainer,a.FromContainer,a.FromPNDescription from [PDMS].[dbo].[ComponentIssueSummary] a 
                  inner join (SELECT COUNT(*) as tcnt,ToContainer FROM [PDMS].[dbo].[ComponentIssueSummary] where ToContainer  in <sncond> and LEN(FromContainer) = 7 group by ToContainer) b on a.ToContainer = b.ToContainer
                  where b.tcnt >= 2 and LEN(a.FromContainer) = 7 and a.ToContainer in <sncond> order by a.ToContainer";

            sql = sql.Replace("<sncond>", sncond);
            var dbret = DBUtility.ExeMESReportSqlWithRes(sql);

            foreach (var line in dbret)
            {
                var tempvm = new WaferTableItem();
                tempvm.SN       = Convert.ToString(line[1]);
                tempvm.DateCode = Convert.ToString(line[0]);

                var fromdesc = Convert.ToString(line[2]);
                if (fromdesc.ToUpper().Contains("DIE,LF,ASIC"))
                {
                    continue;
                }

                if (!excludsndict.ContainsKey(tempvm.SN))
                {
                    excludsndict.Add(tempvm.SN, true);

                    tempres.Add(tempvm);

                    if (!cablesndict.ContainsKey(tempvm.SN))
                    {
                        cablesndict.Add(tempvm.SN, true);
                    }
                    if (!cablesndict.ContainsKey(tempvm.DateCode))
                    {
                        cablesndict.Add(tempvm.DateCode, true);
                    }
                }//check sn
            }

            foreach (var item in desdata)
            {
                if (!cablesndict.ContainsKey(item.SN))
                {
                    ret.Add(item);
                }
            }
            ret.AddRange(tempres);

            return(ret);
        }
Exemplo n.º 8
0
        private static void UpdateDMRSN(string prodline, Controller ctrl)
        {
            var dict = new Dictionary <string, string>();
            var sql  = "SELECT distinct [DMR_ID],[Prod_Line],[Created_at],[Created_By],[File_URL]  FROM [eDMR].[dbo].[DMR_Detail_List_View] where Prod_Line =  @Prod_Line and File_URL is not null order by [Created_at] asc";

            dict.Add("@Prod_Line", prodline);
            var latestdate = RetrieveLatestDMRDate(prodline);

            if (!string.IsNullOrEmpty(latestdate))
            {
                sql = @"SELECT distinct [DMR_ID],[Prod_Line],[Created_at],[Created_By],[File_URL]  FROM [eDMR].[dbo].[DMR_Detail_List_View] 
                        where Prod_Line =  @Prod_Line and Created_at > @StartDate and Created_at <= @EndDate and File_URL is not null";
                dict.Add("@StartDate", latestdate);
                dict.Add("@EndDate", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
            }

            var dmrlist = new List <DMRSNVM>();

            var dbret = DBUtility.ExeDMRSqlWithRes(sql, dict);

            foreach (var line in dbret)
            {
                try
                {
                    var tempvm = new DMRSNVM();
                    tempvm.DMRID       = Convert.ToString(line[0]);
                    tempvm.DMRProdLine = Convert.ToString(line[1]);
                    tempvm.DMRDate     = Convert.ToDateTime(line[2]).ToString("yyyy-MM-dd HH:mm:ss");
                    tempvm.DMRCreater  = Convert.ToString(line[3]);
                    tempvm.DMRFileURL  = Convert.ToString(line[4]);
                    dmrlist.Add(tempvm);
                }
                catch (Exception ex) { }
            }

            sql = @"insert into DMRSNVM(DMRID,DMRProdLine,DMRDate,DMRCreater,SN,SNFailure) values(@DMRID,@DMRProdLine,@DMRDate,@DMRCreater,@SN,@SNFailure)";

            foreach (var vm in dmrlist)
            {
                var lfs = ExternalDataCollector.DownloadShareFile(vm.DMRFileURL, ctrl);
                if (!string.IsNullOrEmpty(lfs) && ExternalDataCollector.FileExist(ctrl, lfs))
                {
                    var sndatalist = ExternalDataCollector.RetrieveDataFromExcelWithAuth(ctrl, lfs, null, 5);
                    foreach (var l in sndatalist)
                    {
                        var sn = l[0];
                        if (sn.Length != 7)
                        {
                            continue;
                        }
                        var failure = "N/A";
                        if (l.Count >= 3)
                        {
                            failure = l[2];
                        }

                        dict = new Dictionary <string, string>();
                        dict.Add("@DMRID", vm.DMRID);
                        dict.Add("@DMRProdLine", vm.DMRProdLine);
                        dict.Add("@DMRDate", vm.DMRDate);
                        dict.Add("@DMRCreater", vm.DMRCreater);
                        dict.Add("@SN", sn);
                        dict.Add("@SNFailure", failure);
                        DBUtility.ExeLocalSqlNoRes(sql, dict);
                    }
                }
            }
        }
Exemplo n.º 9
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);
        }
Exemplo n.º 10
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);
        }
Exemplo n.º 11
0
        public static void UpdateBuyRequest(AssetBuyInfoVM buy)
        {
            var sql   = "update AssetBuyInfo set ";
            var param = new Dictionary <string, string>();

            if (!string.IsNullOrEmpty(buy.EngName))
            {
                sql += " EngName = @EngName, ";
                param.Add("@EngName", buy.EngName);
            }
            if (!string.IsNullOrEmpty(buy.ChName))
            {
                sql += " ChName = @ChName, ";
                param.Add("@ChName", buy.ChName);
            }
            if (!string.IsNullOrEmpty(buy.UnitPrice))
            {
                sql += " UnitPrice = @UnitPrice, ";
                param.Add("@UnitPrice", buy.UnitPrice);
            }
            if (!string.IsNullOrEmpty(buy.Brand))
            {
                sql += " Brand = @Brand, ";
                param.Add("@Brand", buy.Brand);
            }
            if (!string.IsNullOrEmpty(buy.Model))
            {
                sql += " Model = @Model, ";
                param.Add("@Model", buy.Model);
            }
            if (!string.IsNullOrEmpty(buy.OriginCountry))
            {
                sql += " OriginCountry = @OriginCountry, ";
                param.Add("@OriginCountry", buy.OriginCountry);
            }
            if (!string.IsNullOrEmpty(buy.Picture))
            {
                sql += " Picture = @Picture, ";
                param.Add("@Picture", buy.Picture);
            }
            if (!string.IsNullOrEmpty(buy.Purpose))
            {
                sql += " Purpose = @Purpose, ";
                param.Add("@Purpose", buy.Purpose);
            }
            if (!string.IsNullOrEmpty(buy.Functions))
            {
                sql += " Functions = @Functions, ";
                param.Add("@Functions", buy.Functions);
            }
            if (!string.IsNullOrEmpty(buy.WorkPrinciple))
            {
                sql += " WorkPrinciple = @WorkPrinciple, ";
                param.Add("@WorkPrinciple", buy.WorkPrinciple);
            }
            if (!string.IsNullOrEmpty(buy.CorporatePurposes))
            {
                sql += " CorporatePurposes = @CorporatePurposes, ";
                param.Add("@CorporatePurposes", buy.CorporatePurposes);
            }
            if (!string.IsNullOrEmpty(buy.Status))
            {
                sql += " Status = @Status, ";
                param.Add("@Status", buy.Status);
            }

            sql += " UpdateAt = @UpdateAt where ID = @ID";
            param.Add("@UpdateAt", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
            param.Add("@ID", buy.ID);

            DBUtility.ExeLocalSqlNoRes(sql, param);
        }
Exemplo n.º 12
0
        public static Dictionary <string, AssetBorrowHistoryVM> GetBorrowList(string key = "", string status = "")
        {
            var sql   = @"select abh.*, ai.CN, ai.EnglishName, ai.ChineseName
                    from AssetBorrowHistory as abh 
                    left join AssetInfo as ai on abh.AssetID = ai.ID
                    where abh.IsReturn <> 0";
            var param = new Dictionary <string, string>();

            if (!string.IsNullOrEmpty(key))
            {
                sql += " and (ai.EnglishName like @key or ai.ChineseName like @key or ai.CN like @key)";
                param.Add("@key", "%" + key + "%");
            }

            if (!string.IsNullOrEmpty(status))
            {
                sql += " and abh.IsReturn = @Status ";
                param.Add("@Status", status);
            }

            sql += " Order by abh.IsReturn, abh.UpdateAt Desc";

            var dbret = DBUtility.ExeLocalSqlWithRes(sql, param);
            var res   = new Dictionary <string, AssetBorrowHistoryVM>();

            if (dbret.Count > 0)
            {
                foreach (var item in dbret)
                {
                    var asset_tmp = new AssetVM();
                    asset_tmp.ID          = Convert.ToString(item[2]);
                    asset_tmp.CN          = Convert.ToString(item[15]);
                    asset_tmp.EnglishName = Convert.ToString(item[16]);
                    asset_tmp.ChineseName = Convert.ToString(item[17]);
                    if (res.ContainsKey(Convert.ToString(item[1])))
                    {
                        res[Convert.ToString(item[1])].AssetList.Add(asset_tmp);
                    }
                    else
                    {
                        var tmp = new AssetBorrowHistoryVM();
                        tmp.ID            = Convert.ToString(item[0]);
                        tmp.RequestID     = Convert.ToString(item[1]);
                        tmp.BorrowUser    = Convert.ToString(item[3]);
                        tmp.ProjectNo     = Convert.ToString(item[4]);
                        tmp.IsLong        = Convert.ToString(item[5]);
                        tmp.StartDate     = Convert.ToString(item[6]);
                        tmp.EndDate       = Convert.ToString(item[7]);
                        tmp.AdminConfirm  = Convert.ToString(item[8]);
                        tmp.BorrowComment = Convert.ToString(item[9]);
                        tmp.IsReturn      = Convert.ToString(item[10]);
                        tmp.ReturnComment = Convert.ToString(item[11]);
                        tmp.ReturnAt      = Convert.ToString(item[12]);
                        tmp.CreateAt      = Convert.ToString(item[13]);
                        tmp.UpdateAt      = Convert.ToString(item[14]);
                        var asset_list = new List <AssetVM>();
                        asset_list.Add(asset_tmp);
                        tmp.AssetList = asset_list;
                        res.Add(Convert.ToString(item[0]), tmp);
                    }
                }
            }

            return(res);
        }
Exemplo n.º 13
0
        public static Dictionary <string, AssetVM> GetAssetList(string cn = "", string name = "", string status = "", string ids = "")
        {
            var sql   = @"select ai.*, abh.*
                    from AssetInfo as ai 
                    left join AssetBorrowHistory as abh on ai.ID = abh.AssetID
                    where 1 = 1";
            var param = new Dictionary <string, string>();

            if (!string.IsNullOrEmpty(name))
            {
                sql += " and (ai.EnglishName like @name or ai.ChineseName like @name) ";
                param.Add("@name", "%" + name + "%");
            }
            if (!string.IsNullOrEmpty(cn))
            {
                sql += " and ai.CN like @CN ";
                param.Add("@CN", "%" + cn + "%");
            }
            if (!string.IsNullOrEmpty(status))
            {
                sql += " and ai.EquipmentStatus = @Status ";
                param.Add("@Status", status);
            }
            if (!string.IsNullOrEmpty(ids))
            {
                sql += " and ai.ID in <#ids>";
                sql  = sql.Replace("<#ids>", ids);
            }

            sql += " order by ai.ID Desc";

            var dbret      = DBUtility.ExeLocalSqlWithRes(sql, param);
            var res        = new Dictionary <string, AssetVM>();
            var title_info = AssetVMProperty();

            if (dbret.Count > 0)
            {
                foreach (var item in dbret)
                {
                    var tmp = new AssetBorrowHistoryVM();
                    tmp.ID            = Convert.ToString(item[61]);
                    tmp.RequestID     = Convert.ToString(item[62]);
                    tmp.AssetID       = Convert.ToString(item[63]);
                    tmp.BorrowUser    = Convert.ToString(item[64]);
                    tmp.ProjectNo     = Convert.ToString(item[65]);
                    tmp.IsLong        = Convert.ToString(item[66]);
                    tmp.StartDate     = Convert.ToString(item[67]);
                    tmp.EndDate       = Convert.ToString(item[68]);
                    tmp.AdminConfirm  = Convert.ToString(item[69]);
                    tmp.BorrowComment = Convert.ToString(item[70]);
                    tmp.IsReturn      = Convert.ToString(item[71]);
                    tmp.ReturnComment = Convert.ToString(item[72]);
                    tmp.ReturnAt      = Convert.ToString(item[73]);
                    tmp.CreateAt      = Convert.ToString(item[74]);
                    tmp.UpdateAt      = Convert.ToString(item[75]);
                    if (res.ContainsKey(Convert.ToString(item[0])))
                    {
                        res[Convert.ToString(item[0])].BorrowHistory.Add(tmp);
                    }
                    else
                    {
                        var asset_tmp = new AssetVM();
                        asset_tmp.ID = Convert.ToString(item[0]);
                        PropertyInfo[] properties = typeof(AssetVM).GetProperties();

                        var idx = 1;
                        foreach (var title in title_info)
                        {
                            SetPropertyValue(title, Convert.ToString(item[idx]), asset_tmp);
                            idx++;
                        }
                        asset_tmp.BorrowHistory.Add(tmp);
                        res.Add(Convert.ToString(item[0]), asset_tmp);
                    }
                }
                var check_history = AssetCheckHistoryVM.GetAssetCheckHistory(cn, name, status);
                if (check_history.Count > 0)
                {
                    foreach (var item in res)
                    {
                        if (check_history.ContainsKey(item.Key))
                        {
                            item.Value.CheckHistory = check_history[item.Key];
                        }
                    }
                }
            }

            return(res);
        }
Exemplo n.º 14
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);
        }