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