public void UpdateRiskManagementImport(ModelRiskManagementImport pModel)
{
if (pModel == null)
{
return;
}
string strCommand = "UPDATE RISK_IMPORT SET " +
"RISK_SCORE = '{0}'," +
"INTERNAL_CORROSION = '{1}'," +
"EXTERNAL_CORROSION = '{2}'," +
"THIRD_PARTY_INTERFERENCE = '{3}'," +
"LOSS_OF_GROUND_SUPPORT = '{4}'," +
"WHERE 1=1 " +
"AND RC = {5} " +
"AND YEAR = {6}";
strCommand = string.Format(
strCommand,
pModel.RISK_SCORE,
pModel.INTERNAL_CORROSION,
pModel.EXTERNAL_CORROSION,
pModel.THIRD_PARTY_INTERFERENCE,
pModel.LOSS_OF_GROUND_SUPPORT,
pModel.RC,
pModel.YEAR);
var dal = new DAL.DAL();
dal.ExecuteNonQuery(strCommand);
dal = null;
}
public void InsertRiskManagementImport(ModelRiskManagementImport pModel)
{
if (pModel == null)
{
return;
}
string strCommand = @"INSERT INTO RISK_IMPORT
(
REGION,
RC,
RISK_SCORE,
INTERNAL_CORROSION,
EXTERNAL_CORROSION,
THIRD_PARTY_INTERFERENCE,
LOSS_OF_GROUND_SUPPORT,
YEAR
)
VALUES ( '{0}' ,{1} ,{2} ,{3} ,{4} ,{5} ,{6} , {7})";
strCommand = string.Format(
strCommand,
pModel.REGION,
pModel.RC,
pModel.RISK_SCORE,
pModel.INTERNAL_CORROSION,
pModel.EXTERNAL_CORROSION,
pModel.THIRD_PARTY_INTERFERENCE,
pModel.LOSS_OF_GROUND_SUPPORT,
pModel.YEAR
);
var dal = new DAL.DAL();
dal.ExecuteNonQuery(strCommand);
dal = null;
}
public IEnumerable <ModelRiskManagementImport> ReadExcelRiskManagementImport(
System.IO.Stream pStreamExcel,
int pIntYear,
string pStrUploadBy)
{
if (pStreamExcel == null || pStreamExcel.Length <= 0)
{
yield break;
}
using (var exel = new OfficeOpenXml.ExcelPackage(pStreamExcel))
{
var exWorkSheet = exel.Workbook.Worksheets.FirstOrDefault(x => x.Name.Equals(WorksheetName));
int intColCount = exWorkSheet.Dimension.End.Column;
int intRowCount = exWorkSheet.Dimension.End.Row;
int intEndRow = intRowCount - 6;
//var q = exWorkSheet.Cells.Where(x => excelRiskManagementColumn.ContainsKey(x.Value?.ToString())).ToList();
//q.ForEach(x => {
// excelRiskManagementColumn[x.Value.ToString()] = x.Address;
//});
for (int intRow = StartRow; intRow < intEndRow; ++intRow)
{
var pipImport = new ModelRiskManagementImport()
{
REGION = getExcelRiskManagementValue(exWorkSheet, intRow, enumExcelRiskManagementColumn.REGION_ID).GetInt(),
RC = getExcelRiskManagementValue(exWorkSheet, intRow, enumExcelRiskManagementColumn.RC).GetString(),
RISK_SCORE = getExcelRiskManagementValue(exWorkSheet, intRow, enumExcelRiskManagementColumn.RISK_SCORE).GetDecimal(),
INTERNAL_CORROSION = getExcelRiskManagementValue(exWorkSheet, intRow, enumExcelRiskManagementColumn.INTERNAL_CORROSION).GetDecimal(),
EXTERNAL_CORROSION = getExcelRiskManagementValue(exWorkSheet, intRow, enumExcelRiskManagementColumn.EXTERNAL_CORROSION).GetDecimal(),
THIRD_PARTY_INTERFERENCE = getExcelRiskManagementValue(exWorkSheet, intRow, enumExcelRiskManagementColumn.THIRD_PARTY_INTERFERENCE).GetDecimal(),
LOSS_OF_GROUND_SUPPORT = getExcelRiskManagementValue(exWorkSheet, intRow, enumExcelRiskManagementColumn.LOSS_OF_GROUND_SUPPORT).GetDecimal(),
YEAR = pIntYear
};
yield return(pipImport);
}
};
}
