public void RemoveAllFromUpgradeResult()
{
using (var dbContext = new FcoDBEntities())
{
dbContext.Database.ExecuteSqlCommand("delete from UpgradeResult");
}
}
private void ClearUpgradResultTable()
{
//Clear database data
using (var dbContext = new FcoDBEntities())
{
dbContext.Database.ExecuteSqlCommand("Delete from UpgradeResult");
LogHelper.Instance.Info("Clear Upgrade Result Table.");
}
}
private long GetDBCurMaxId(FcoDBEntities dbContext, int table)
{
switch (table)
{
case (int)Constants.DBTABLE.MAINUAL:
return(dbContext.MainUALs.Count() == 0 ? 0 : dbContext.MainUALs.Max(record => record.Id));
case (int)Constants.DBTABLE.UPGRADERESULT:
return(dbContext.UpgradeResults.Count() == 0 ? 0 : dbContext.UpgradeResults.Max(record => record.Id));
case (int)Constants.DBTABLE.FCO:
return(dbContext.FCOes.Count() == 0 ? 0 : dbContext.FCOes.Max(record => record.Id));
}
return(0);
}
public void ExportFailureReport(string filePath)
{
DataTable dt = new DataTable();
using (var dbContext = new FcoDBEntities())
{
var query = from r in dbContext.UpgradeResults
where r.CompletionStat != 0
select new
{
SystemSerialNo = r.SystemSerialNo,
UpgradeCode = r.UpgradeCode,
CompletionStat = r.CompletionStat
};
DataColumn dtcol = dt.Columns.Add(rptDailyColHeaderNo, typeof(Int32));
dtcol.AllowDBNull = false;
dtcol.Unique = true;
dt.Columns.Add(rptDailyColHeaderSN, typeof(String));
dt.Columns.Add(rptDailyColHeaderUpgradeCode, typeof(String));
dt.Columns.Add(rptDailyColHeaderCompletionStat, typeof(Int32));
dt.Columns.Add(rptDailyColHeaderProcessedDate, typeof(DateTime));
foreach (var q in query)
{
DataRow dr = dt.NewRow();
dr[rptDailyColHeaderNo] = dt.Rows.Count + 1;
dr[rptDailyColHeaderSN] = q.SystemSerialNo;
dr[rptDailyColHeaderUpgradeCode] = q.UpgradeCode;
dr[rptDailyColHeaderCompletionStat] = q.CompletionStat;
dr[rptDailyColHeaderProcessedDate] = Today.ToShortDateString();
dt.Rows.Add(dr);
}
}
var lines = new List <string>();
string[] columnNames = dt.Columns.Cast <DataColumn>().Select(column => column.ColumnName).ToArray();
var header = string.Join(",", columnNames);
lines.Add(header);
var valueLines = dt.AsEnumerable().Select(row => string.Join(",", row.ItemArray));
lines.AddRange(valueLines);
//if (File.Exists(filePath))
// Console.WriteLine("Fail to generate daily report due to file exists");
File.WriteAllLines(filePath, lines);
}
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 int GetDBVersion()
{
int dbVersion = 0;
//should check fcodb in future
try
{
using (var dbContext = new FcoDBEntities())
{
dbVersion = dbContext.Versions
.Where(record => record.Id == "MainUAL")
.Select(record => record.TableVersion)
.FirstOrDefault();
}
}
catch
{
LogHelper.Instance.Warn("No db file found");
return(dbVersion);
}
return(dbVersion);
}
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;
}
}
}
//Generate weekly report from ual table
public void GenerateReportTo(string filePath)
{
LogHelper.Instance.Info("START generating weekly report to " + filePath);
DataTable dt = new DataTable();
try
{
using (var dbContext = new FcoDBEntities())
{
/********* Add Header to stream lines ********/
List <string> lines = new List <string>();
string header = rptWeeklyColHeaderNo + "," + rptWeeklyColHeaderSN + "," +
rptWeeklyColHeaderUpgradeCode + "," + rptWeeklyColHeaderModelNumber + "," +
rptWeeklyColHeaderCompletionDate + "," + rptWeeklyColHeaderCountryName + "," +
rptWeeklyColHeaderFcoNo + "," + rptWeeklyColHeaderFcoRev;
lines.Add(header);
/******* Part I - List all completed items ********/
/*select the completed items from UAL*/
var queryCompleted = from a in dbContext.MainUALs
where a.FCONo.Equals(m_fco) && a.CompletionStat == (int)Constants.COMPLETIONSTAT.SUCCESS
select new
{
SystemSerialNo = a.ShippedSystemSerialNo,
UpgradeCode = a.UpgradeCode,
ModelNumber = a.ModelNumber,
CompletionDate = a.CompletionDate,
Country = a.CountryName,
Fco = a.FCONo,
FcoRev = a.FCORev,
};
int count = 1;
Dictionary <string, int> mapCountry = new Dictionary <string, int>();
foreach (var q in queryCompleted)
{
string line = count.ToString();
line += "," + q.SystemSerialNo;
line += "," + q.UpgradeCode;
line += "," + q.ModelNumber;
line += "," + q.CompletionDate;
line += "," + q.Country;
line += "," + q.Fco;
line += "," + q.FcoRev;
lines.Add(line);
//if (!mapCountry.ContainsKey(q.Country))
// mapCountry.Add(q.Country, 1);
//else
// mapCountry[q.Country]++;
count++;
}
/******** Part II - Query total number of each country from FCO table ********/
var queryAll = from f in dbContext.FCOes
where f.FcoNumber.Equals(m_fco)
select new
{
country = f.CountryName,
countAll = f.TotalCount
};
int worldWideTotal = 0;
int worldWideCompleted = 0;
List <string> summaryLines = new List <string>();
foreach (var q in queryAll)
{
int completed = queryCompleted.ToList().Where(x => x.Country.Equals(q.country)).Select(x => x.SystemSerialNo).Distinct().Count();
worldWideCompleted += completed;
worldWideTotal += q.countAll;
double percentage = (double)completed / (double)q.countAll;
//complted/total
string line = string.Format("Country: {0}, Completed Number: {1}, Completed Percentage: {2:P1}, InCompleted Percentage: {3:P1}", q.country, completed, percentage, 1 - percentage);
summaryLines.Add(line);
}
string worldWide = string.Format("WorldWide, Completed Number: {0}, Completed Percentage: {1:P1}, InCompleted Percentage: {2:P1}", worldWideCompleted, (double)worldWideCompleted / (double)worldWideTotal, 1 - (double)worldWideCompleted / (double)worldWideTotal);
summaryLines.Add(worldWide);
/******** PartIII - Write To File********/
summaryLines.AddRange(lines);
File.WriteAllLines(filePath, summaryLines);
LogHelper.Instance.Info("SUCCESSFULLY generating weekly report to " + filePath);
}
}
catch (Exception e)
{
LogHelper.Instance.Error("Fail to generate weekly report due to " + e.Message);
}
}
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 GenerateReportTo(string filePath)
{
LogHelper.Instance.Info("START generating daily report to" + filePath);
if (!ProcessData())
{
return;
}
string[] strCompletionStatus = new string[]
{
"Match",
"UnMatch",
"Not Found",
"Unknown"
};
//generate .csv file to file stream
DataTable dt = new DataTable();
try
{
using (var dbContext = new FcoDBEntities())
{
var query = from r in dbContext.UpgradeResults
where r.CompletionStat != 0
select new
{
SystemSerialNo = r.SystemSerialNo,
UpgradeCode = r.UpgradeCode,
ModelNumber = r.ModelNumber,
CompletionStat = r.CompletionStat
};
//if (query.Count() == 0)
// return;
DataColumn dtcol = dt.Columns.Add(rptDailyColHeaderNo, typeof(Int32));
dtcol.AllowDBNull = false;
dtcol.Unique = true;
dt.Columns.Add(rptDailyColHeaderSN, typeof(String));
dt.Columns.Add(rptDailyColHeaderUpgradeCode, typeof(String));
dt.Columns.Add(rptDailyColHeaderModelNumber, typeof(String));
dt.Columns.Add(rptDailyColHeaderCompletionStat, typeof(String));
dt.Columns.Add(rptDailyColHeaderProcessedDate, typeof(DateTime));
foreach (var q in query)
{
DataRow dr = dt.NewRow();
dr[rptDailyColHeaderNo] = dt.Rows.Count + 1;
dr[rptDailyColHeaderSN] = q.SystemSerialNo;
dr[rptDailyColHeaderUpgradeCode] = q.UpgradeCode;
dr[rptDailyColHeaderModelNumber] = q.ModelNumber;
dr[rptDailyColHeaderCompletionStat] = strCompletionStatus[(int)q.CompletionStat]; //Required to display string other than number
dr[rptDailyColHeaderProcessedDate] = m_dt;
dt.Rows.Add(dr);
}
}
if (dt.Rows.Count > 0)
{
var lines = new List <string>();
string[] columnNames = dt.Columns.Cast <DataColumn>().Select(column => column.ColumnName).ToArray();
var header = string.Join(",", columnNames);
lines.Add(header);
var valueLines = dt.AsEnumerable().Select(row => string.Join(",", row.ItemArray));
lines.AddRange(valueLines);
File.WriteAllLines(filePath, lines);
LogHelper.Instance.Info("SUCCESSFULLY generated daily report to " + filePath);
}
/*Clear the database for next report*/
ClearUpgradResultTable();
}
catch (Exception e)
{
LogHelper.Instance.Error("Fail to generated daily report due to " + e.Message);
}
}
public void ExportWeeklyReport(string fcoNumber, string filePath)
{
DataTable dt = new DataTable();
using (var dbContext = new FcoDBEntities())
{
var query = from a in dbContext.MainUALs
where a.FCONo.Equals(fcoNumber) && a.CompletionStat == 0
select new
{
SystemSerialNo = a.ShippedSystemSerialNo,
CompletionDate = a.CompletionDate,
Country = a.ShippedCountryName
};
DataColumn dtcol = dt.Columns.Add(rptWeeklyColHeaderNo, typeof(Int32));
dtcol.AllowDBNull = false;
dtcol.Unique = true;
dt.Columns.Add(rptWeeklyColHeaderSN, typeof(String));
dt.Columns.Add(rptWeeklyColHeaderCompletionDate, typeof(DateTime));
dt.Columns.Add(rptWeeklyColHeaderCountryName, typeof(String));
foreach (var q in query)
{
DataRow dr = dt.NewRow();
dr[rptWeeklyColHeaderNo] = dt.Rows.Count + 1;
dr[rptWeeklyColHeaderSN] = q.SystemSerialNo;
dr[rptWeeklyColHeaderCompletionDate] = q.CompletionDate;
dr[rptWeeklyColHeaderCountryName] = q.Country;
dt.Rows.Add(dr);
}
List <string> lines = new List <string>();
string[] columnNames = dt.Columns.Cast <DataColumn>().Select(column => column.ColumnName).ToArray();
string header = string.Join(",", columnNames);
lines.Add(header);
var valueLines = dt.AsEnumerable().Select(row => string.Join(",", row.ItemArray));
lines.AddRange(valueLines);
Dictionary <string, double> dic = new Dictionary <string, double>();
var query2 = from a in dbContext.MainUALs
where a.FCONo.Equals(fcoNumber)
group a by a.ShippedCountryName into countryGroup
select new
{
CountryName = countryGroup.Key,
RequestCount = countryGroup.Count()
};
foreach (var q in query2)
{
dic.Add(q.CountryName, q.RequestCount);
}
var query3 = from a in dbContext.MainUALs
where a.CompletionStat == 0 && a.FCONo.Equals(fcoNumber)
group a by a.ShippedCountryName into coutryGroup
select new
{
CountryName = coutryGroup.Key,
CompletedCount = coutryGroup.Count()
};
foreach (var q in query3)
{
dic[q.CountryName] = (double)q.CompletedCount / (double)dic[q.CountryName]; //percentage of each country;
}
foreach (KeyValuePair <string, double> kvp in dic)
{
string line = string.Format("Country: {0}, Completed Percentage: {1:P1}", kvp.Key, kvp.Value);
lines.Add(line);
}
if (!File.Exists(filePath))
{
File.WriteAllLines(filePath, lines);
}
}
}
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
}