public void StoreUpgradeCode(string filePath, String fcoNumber) { DataTable dt = ImportSpreadSheet(filePath); using (var dbContext = new FcoDBEntities()) { long id = GetDBCurMaxId(dbContext, (int)Constants.DBTABLE.UPGRADERESULT); foreach (DataRow dr in dt.Rows) { UpgradeResult item = new UpgradeResult(); item.Id = ++id; item.SystemSerialNo = dr[UCColHeaderSN].ToString(); item.UpgradeCode = dr[UCColHeaderUpgradeCode].ToString(); item.FcoNo = fcoNumber; dbContext.UpgradeResults.Add(item); } dbContext.SaveChanges(); } }
public bool StoreUAL(string filepath, String fcoNumber) { /*Import .csv to datatable*/ DataTable dt = ImportSpreadSheet(filepath); /*Check if the ual has already imported*/ if (FCOList.Contains(fcoNumber)) { return(false); } /*Store datatable to database;*/ using (var dbContext = new FcoDBEntities()) { long id = GetDBCurMaxId(dbContext, (int)Constants.DBTABLE.MAINUAL); foreach (DataRow dr in dt.Rows) { //Insert new item where no duplicates for specific fco MainUAL newItem = new MainUAL(); newItem.Id = ++id; newItem.ShippedSystemSerialNo = dr[UALColHeaderShippedSN].ToString(); newItem.MaintainedSystemSerialNo = dr[UALColHeaderMaintainedSN].ToString(); newItem.ShippedCountryName = dr[UALColHeaderCountryName].ToString(); newItem.UpgradeCode = dr[UALColHeaderUpgradeCode].ToString(); newItem.FCONo = fcoNumber; dbContext.MainUALs.Add(newItem); } //Add FCO number to databse if not exists long fcoId = GetDBCurMaxId(dbContext, (int)Constants.DBTABLE.FCO); FCO item = new FCO(); item.Id = ++fcoId; item.FcoNumber = fcoNumber; dbContext.FCOes.Add(item); dbContext.SaveChanges(); } return(true); }
public void Import(string upgradeCodeDir) { //foreach spread sheet under the dir, import it to database DirectoryInfo dirUpgradeCode = new DirectoryInfo(upgradeCodeDir); FileInfo[] files = dirUpgradeCode.GetFiles("*.csv"); foreach (FileInfo fInfo in files) { Dictionary <string, int> mapHeaderIndex = new Dictionary <string, int>(); string fileName = fInfo.FullName; LogHelper.Instance.Info("START importing UpgradeCode spreadsheet " + fileName); Encoding curEncoding = GetEncoding(fileName); try { using (var reader = new StreamReader(fileName, curEncoding)) { var header = reader.ReadLine().Split(','); int i = 0; while (i < header.Length) { if (!mapHeaderIndex.ContainsKey(header[i].Trim())) { mapHeaderIndex.Add(header[i].Trim(), i); i++; } else { LogHelper.Instance.Error("Duplicate column header " + header[i] + "in " + fileName); break; } } if (i != header.Length) // break the loop due to duplicate column header name { continue; } if (!mapHeaderIndex.ContainsKey(UCColHeaderSN)) { LogHelper.Instance.Error("Unable to find column" + UCColHeaderSN + "in " + fileName); continue; } if (!mapHeaderIndex.ContainsKey(UCColHeaderUpgradeCode)) { LogHelper.Instance.Error("Unable to find column" + UCColHeaderUpgradeCode + "in " + fileName); continue; } using (var dbContext = new FcoDBEntities()) { while (!reader.EndOfStream) { UpgradeResult item = new UpgradeResult(); var line = reader.ReadLine(); var values = line.Split(','); if (string.IsNullOrWhiteSpace(values[mapHeaderIndex[UCColHeaderSN]]) && string.IsNullOrWhiteSpace(values[mapHeaderIndex[UCColHeaderUpgradeCode]])) { continue; //skip the empty line } item.Id = Guid.NewGuid().ToString(); item.SystemSerialNo = values[mapHeaderIndex[UCColHeaderSN]].Trim().ToUpper(); item.UpgradeCode = values[mapHeaderIndex[UCColHeaderUpgradeCode]].Trim().ToUpper(); if (mapHeaderIndex.ContainsKey(UCColHeaderModelNumber)) //TC upgrade code { item.ModelNumber = values[mapHeaderIndex[UCColHeaderModelNumber]].Trim().ToUpper(); } dbContext.UpgradeResults.Add(item); } dbContext.SaveChanges(); } //End dbcontext } //End stream reader FCOPath fcoPath = new FCOPath(m_dt); string archiveFilePath = fcoPath.GetArchiveFilePath(); ArchiveTo(fileName, archiveFilePath); LogHelper.Instance.Info(string.Format("SUCCESSFULLY imported {0} to database.", fileName)); } catch (Exception e1) { LogHelper.Instance.Error(string.Format("FAIL to import upgradecode file {0} due to {1}.", fileName, e1.Message)); continue; } } }
private bool ProcessData() { using (var dbContext = new FcoDBEntities()) { try { /*** Find those serial number exists in main UAL ***/ var query = from r in dbContext.UpgradeResults join a in dbContext.MainUALs on r.UpgradeCode equals a.UpgradeCode select new { UAL = a, UR = r }; foreach (var g in query) { //Match if (string.Compare(g.UR.SystemSerialNo, g.UAL.ShippedSystemSerialNo, true) == 0 && string.Compare(g.UR.ModelNumber, g.UAL.ModelNumber, true) == 0) { if (g.UAL.CompletionDate != null) { g.UR.CompletionStat = (int)Constants.COMPLETIONSTAT.SUCCESS; continue; } g.UAL.ProcessedDate = m_dt; g.UAL.CompletionDate = m_dt; g.UAL.CompletionStat = (int)Constants.COMPLETIONSTAT.SUCCESS; g.UR.CompletionStat = (int)Constants.COMPLETIONSTAT.SUCCESS; } //Unmatch else { //never matched before if (g.UAL.CompletionDate == null) { g.UAL.ProcessedDate = m_dt; g.UAL.CompletionStat = (int)Constants.COMPLETIONSTAT.UNMATCH; } //never matched this time if (g.UR.CompletionStat == null) { g.UR.CompletionStat = (int)Constants.COMPLETIONSTAT.UNMATCH; } } } dbContext.SaveChanges(); //Not found var notFoundQuery = from r in dbContext.UpgradeResults where r.CompletionStat == null select new { UR = r }; foreach (var g in notFoundQuery) { g.UR.CompletionStat = (int)Constants.COMPLETIONSTAT.NOTFOUND; } dbContext.SaveChanges(); return(true); } catch (Exception e) { LogHelper.Instance.Error("Fail to process data due to " + e.Message); return(false); } finally { dbContext.Dispose(); } } }
public void ProcessUpgradeCode() { using (var dbContext = new FcoDBEntities()) { /*** Find those serial number exists in main UAL ***/ var query = from a in dbContext.MainUALs.AsQueryable() join r in dbContext.UpgradeResults on a.ShippedSystemSerialNo equals r.SystemSerialNo where a.FCONo == r.FcoNo select new { UAL = a, UR = r }; foreach (var o in query) { //if completion date is not empty, do not update UAL table if (o.UAL.CompletionDate != null) { o.UR.CompletionStat = o.UAL.CompletionStat; continue; } if (o.UAL.UpgradeCode.Equals(o.UR.UpgradeCode)) { o.UAL.ProcessedDate = ReportMgr.today; o.UAL.CompletionDate = Today; o.UAL.CompletionStat = (int)Constants.COMPLETIONSTAT.SUCCESS; o.UR.CompletionStat = (int)Constants.COMPLETIONSTAT.SUCCESS; } else { o.UAL.ProcessedDate = ReportMgr.today; o.UAL.CompletionStat = (int)Constants.COMPLETIONSTAT.UNMATCHUPGRADECODE; o.UR.CompletionStat = (int)Constants.COMPLETIONSTAT.UNMATCHUPGRADECODE; } } dbContext.SaveChanges(); /*** Find those serial number not exists in main UAL ***/ List <MainUAL> emptyUALs = (from a in dbContext.MainUALs where a.CompletionStat != (int)Constants.COMPLETIONSTAT.SUCCESS && a.CompletionStat != (int)Constants.COMPLETIONSTAT.UNMATCHUPGRADECODE select a).ToList(); List <UpgradeResult> emptyURs = (from r in dbContext.UpgradeResults where r.CompletionStat == null select r).ToList(); foreach (UpgradeResult ur in emptyURs) { foreach (MainUAL ua in emptyUALs) { if (ua.FCONo == ur.FcoNo) { if (ua.UpgradeCode == ur.UpgradeCode) { ur.CompletionStat = (int)Constants.COMPLETIONSTAT.NOTFOUND | (int)Constants.COMPLETIONSTAT.UNMATCHUPGRADECODE; ua.CompletionStat = ur.CompletionStat; } else { ur.CompletionStat = (int)Constants.COMPLETIONSTAT.NOTFOUND; ua.CompletionStat = ur.CompletionStat; } ua.ProcessedDate = ReportMgr.today; } } } dbContext.SaveChanges(); } }
/* 2019-03-14 Change StoreUAL() function to use SQL for Store UAL bulk insertion, which improves the performance a lot, * when insertion 33982 data into database using FCOEntity, it cost around 1-2 minutes, * while using insertion command directly, it cost 3-4 seconds. * And add error handling. */ public async Task <bool> StoreUAL(string ualFilePath, string fcoNumber, string fcoRev) { LogHelper.Instance.Info(string.Format("START importing UAL file of FCONumber {0} from {1} to database.", fcoNumber, ualFilePath)); /*Import .csv to datatable*/ //DataTable dt = DailyReport.ImportSpreadSheet(ualFilePath); /*Check if the ual has already imported*/ string uniqueFcoNumber = fcoNumber + fcoRev; if (UniqueFCONumber.Contains(uniqueFcoNumber)) { LogHelper.Instance.Warn("Terminate importing as fco was already in database."); return(false); } try { Encoding curEncoding = DailyReport.GetEncoding(ualFilePath); using (StreamReader reader = new StreamReader(ualFilePath, curEncoding)) { //Map header and index, to make sure the contents are correctly mapped. var headers = reader.ReadLine().Split(','); Dictionary <string, int> mapHeaderIndex = new Dictionary <string, int>(); for (int i = 0; i < headers.Length; i++) { if (mapHeaderIndex.ContainsKey(headers[i].Trim())) { LogHelper.Instance.Error(string.Format("Duplicate column header {0}, please check the UAL file.", headers[i])); return(false); } else { mapHeaderIndex.Add(headers[i].Trim(), i); } } //Check if all the required columns are contained in the file. if (!mapHeaderIndex.ContainsKey(UALColHeaderShippedSN)) { LogHelper.Instance.Error("Fail to import UAL due to unable to find column " + UALColHeaderShippedSN); return(false); } if (!mapHeaderIndex.ContainsKey(UALColHeaderMaintainedSN)) { LogHelper.Instance.Error("Fail to import UAL due to unable to find column " + UALColHeaderMaintainedSN); return(false); } if (!mapHeaderIndex.ContainsKey(UALColHeaderUpgradeCode)) { LogHelper.Instance.Error("Fail to import UAL due to unable to find column " + UALColHeaderUpgradeCode); return(false); } if (!mapHeaderIndex.ContainsKey(UALColHeaderCountryName)) { LogHelper.Instance.Error("Fail to import UAL due to unable to find column " + UALColHeaderCountryName); return(false); } if (mapHeaderIndex.ContainsKey(UALColHeaderModelNumber)) { LogHelper.Instance.Info("Model Number column in the UAL table."); } //Connect database and insert all the items from UAL file. var connectionString = "Data Source=" + System.AppDomain.CurrentDomain.BaseDirectory + "db\\FcoDB.db"; using (var conn = new SQLiteConnection(connectionString)) { conn.Open(); var command = conn.CreateCommand(); var transaction = conn.BeginTransaction(); command.Connection = conn; command.Transaction = transaction; command.CommandText = "INSERT INTO MainUAL(Id, ShippedSystemSerialNo, MaintainedSystemSerialNo, CountryName, UpgradeCode, ModelNumber, FCONo, FCORev) " + "VALUES($Id, $shippedSN, $maintainedSN, $countryName,$upgradeCode, $modelNumber, $fcoNumber, $fcoRev);"; var idParameter = command.CreateParameter(); idParameter.ParameterName = "$Id"; command.Parameters.Add(idParameter); var shippedSNParameter = command.CreateParameter(); shippedSNParameter.ParameterName = "$shippedSN"; command.Parameters.Add(shippedSNParameter); var maintainedSNParameter = command.CreateParameter(); maintainedSNParameter.ParameterName = "$maintainedSN"; command.Parameters.Add(maintainedSNParameter); var countryNameParameter = command.CreateParameter(); countryNameParameter.ParameterName = "$countryName"; command.Parameters.Add(countryNameParameter); var upgradeCodeParameter = command.CreateParameter(); upgradeCodeParameter.ParameterName = "$upgradeCode"; command.Parameters.Add(upgradeCodeParameter); var modelNumberParameter = command.CreateParameter(); modelNumberParameter.ParameterName = "$modelNumber"; command.Parameters.Add(modelNumberParameter); var fcoNumberParameter = command.CreateParameter(); fcoNumberParameter.ParameterName = "$fcoNumber"; command.Parameters.Add(fcoNumberParameter); var fcoRevParameter = command.CreateParameter(); fcoRevParameter.ParameterName = "$fcoRev"; command.Parameters.Add(fcoRevParameter); while (!reader.EndOfStream) { var line = reader.ReadLine(); var values = line.Split(','); idParameter.Value = Guid.NewGuid().ToString(); shippedSNParameter.Value = values[mapHeaderIndex[UALColHeaderShippedSN]].Trim(); maintainedSNParameter.Value = values[mapHeaderIndex[UALColHeaderMaintainedSN]].Trim(); countryNameParameter.Value = values[mapHeaderIndex[UALColHeaderCountryName]].Trim(); upgradeCodeParameter.Value = values[mapHeaderIndex[UALColHeaderUpgradeCode]].Trim(); if (mapHeaderIndex.ContainsKey(UALColHeaderModelNumber)) //TC ual { modelNumberParameter.Value = values[mapHeaderIndex[UALColHeaderModelNumber]].Trim(); } fcoNumberParameter.Value = fcoNumber; fcoRevParameter.Value = fcoRev; command.ExecuteNonQuery(); } try { transaction.Commit(); } catch (Exception e) { LogHelper.Instance.Error("Fail to import UAL table due to " + e.Message); //Attempt to roll back the transaction try { transaction.Rollback(); } catch (Exception e2) { LogHelper.Instance.Error("Fail to rollback transaction due to " + e2.Message); } return(false); } //Catch the exception and rollback transaction LogHelper.Instance.Info("COMPLETE importing UAL table of FCO number" + fcoNumber); } //End using db connection } //End using stream reader /* * 2019-05-01 - Modify FCO template and store the total number of each country to save the * multiple calculation time in weekly report generating. */ using (var dbContext = new FcoDBEntities()) { var queryAll = from a in dbContext.MainUALs where a.FCONo.Equals(fcoNumber) group a by a.CountryName into gp select new { country = gp.Key, countAll = gp.Select(q => q.ShippedSystemSerialNo).Distinct().Count(), }; foreach (var q in queryAll) { var queryCurrent = from b in dbContext.FCOes where b.FcoNumber.Equals(fcoNumber) && b.CountryName.Equals(q.country) select b; if (!queryCurrent.Any()) { FCO item = new FCO(); item.Id = Guid.NewGuid().ToString(); item.FcoNumber = fcoNumber; item.CountryName = q.country; item.TotalCount = q.countAll; dbContext.FCOes.Add(item); } else { queryCurrent.ToList().ForEach(x => x.TotalCount = q.countAll); } } FcoList fcoListItem = new FcoList(); fcoListItem.uniqueFcoNumber = uniqueFcoNumber; dbContext.FcoLists.Add(fcoListItem); dbContext.SaveChanges(); LogHelper.Instance.Info("Completed insertion into FCO table."); } //End using db context return(true); } catch (Exception e3) { LogHelper.Instance.Error("Fail to Store UAL or FCO table due to " + e3.Message); return(false); } //catch all the exceptions }