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