private static void UpdateDMROAStatus(string prodline)
{
var dict = new Dictionary <string, string>();
dict.Add("@DMRProdLine", prodline);
var sql = "select distinct DMRID from DMRSNVM where DMRProdLine=@DMRProdLine and DMROAStatus <> 'C' and DMROAStatus <> 'X'";
var dbret = DBUtility.ExeLocalSqlWithRes(sql, dict);
var dmridlist = new List <string>();
foreach (var l in dbret)
{
dmridlist.Add(O2S(l[0]));
}
if (dmridlist.Count > 0)
{
var oastatlist = new List <DMRSNVM>();
var dmridcond = "('" + string.Join("','", dmridlist) + "')";
sql = "SELECT distinct [DMR_ID],[Step_ID] ,[Status] FROM [eDMR].[dbo].[DMR_View_bk] where DMR_ID in <dmridcond>";
sql = sql.Replace("<dmridcond>", dmridcond);
dbret = DBUtility.ExeDMRSqlWithRes(sql);
foreach (var l in dbret)
{
var tempvm = new DMRSNVM();
tempvm.DMRID = O2S(l[0]);
tempvm.DMROAStep = O2S(l[1]);
tempvm.DMROAStatus = O2S(l[2]);
oastatlist.Add(tempvm);
}
dict = new Dictionary <string, string>();
sql = @"update DMRSNVM set DMROAStep=@DMROAStep,DMROAStatus=@DMROAStatus where DMRID=@DMRID";
foreach (var oa in oastatlist)
{
dict = new Dictionary <string, string>();
dict.Add("@DMRID", oa.DMRID);
dict.Add("@DMROAStep", oa.DMROAStep);
dict.Add("@DMROAStatus", oa.DMROAStatus);
DBUtility.ExeLocalSqlNoRes(sql, dict);
}
}
}
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);
}
}
}
}
