public static string DownloadShareFile(string srcfile, Controller ctrl)
{
try
{
if (ExternalDataCollector.FileExist(ctrl, srcfile))
{
var filename = System.IO.Path.GetFileName(srcfile);
var descfolder = ctrl.Server.MapPath("~/userfiles") + "\\docs\\ShareFile\\";
if (!ExternalDataCollector.DirectoryExists(ctrl, descfolder))
{
ExternalDataCollector.CreateDirectory(ctrl, descfolder);
}
var descfile = descfolder + System.IO.Path.GetFileNameWithoutExtension(srcfile) + DateTime.Now.ToString("yyyy-MM-dd") + System.IO.Path.GetExtension(srcfile);
ExternalDataCollector.FileCopy(ctrl, srcfile, descfile, true);
return(descfile);
}
return(null);
}
catch (Exception ex)
{
return(null);
}
}
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);
}
}
}
}