public void InsertPipelineImport(ModelPipelineImport pModel)
{
if (pModel == null)
{
return;
}
string strCommand = @"INSERT INTO PIPELINE_IMPORT ( RC_NAME, FLOW, DIAMETER, LENGTH, EFFICIENCY,
ROUGHNESS, LOAD, VELOCITY, OUTSIDE_DIAMETER, WALL_THICKNESS, SERVICE_STATE,
MONTH, YEAR, UPLOAD_DATE, UPLOAD_BY, REGION)
VALUES ( '{0}' ,{1} ,{2} ,{3} ,{4} ,{5} ,{6} , {7} ,{8} ,{9} ,'{10}' ,{11} ,{12} , sysdate , '{13}' , {14} )";
strCommand = string.Format(
strCommand,
pModel.RC_NAME.Trim().Replace("'", "''"),
pModel.FLOW,
pModel.DIAMETER,
pModel.LENGTH,
pModel.EFFICIENCY,
pModel.ROUGHNESS,
pModel.LOAD,
pModel.VELOCITY,
pModel.OUTSIDE_DIAMETER,
pModel.WALL_THICKNESS,
pModel.SERVICE_STATE.Replace("'", "''"),
pModel.MONTH,
pModel.YEAR,
pModel.UPLOAD_BY.Trim().Replace("'", "''"),
pModel.REGION.Trim().Replace("'", "''")
);
var dal = new DAL.DAL();
dal.ExecuteNonQuery(strCommand);
dal = null;
}
public void UpdatePipelineImport(ModelPipelineImport pModel)
{
if (pModel == null)
{
return;
}
string strCommand = @"UPDATE PIPELINE_IMPORT SET
RC_NAME = '{0}',
FLOW = {1},
DIAMETER = {2},
LENGTH = {3},
EFFICIENCY = {4},
ROUGHNESS = {5},
LOAD = {6},
VELOCITY = {7},
OUTSIDE_DIAMETER = {8},
WALL_THICKNESS = {9},
SERVICE_STATE = '{10}',
UPLOAD_DATE = sysdate,
UPLOAD_BY = '{11}',
REGION = '{12}'
WHERE 1=1
AND PIPELINE_ID = {13}
AND MONTH = {14}
AND YEAR = {15}" ;
strCommand = string.Format(strCommand,
pModel.RC_NAME.Trim().Replace("'", "''"),
pModel.FLOW,
pModel.DIAMETER,
pModel.LENGTH,
pModel.EFFICIENCY,
pModel.ROUGHNESS,
pModel.LOAD,
pModel.VELOCITY,
pModel.OUTSIDE_DIAMETER,
pModel.WALL_THICKNESS,
pModel.SERVICE_STATE.Trim().Replace("'", "''"),
pModel.UPLOAD_BY.Trim().Replace("'", "''"),
pModel.REGION.Trim().Replace("'", "''"),
pModel.PIPELINE_ID,
pModel.MONTH,
pModel.YEAR
);
var dal = new DAL.DAL();
dal.ExecuteNonQuery(strCommand);
dal = null;
}
public bool IsPipelineImportDuplicate(ModelPipelineImport pModel)
{
if (pModel == null)
{
return(false);
}
string strCommand = @"select case when exists(
select 1 from pipeline_Import where rc_Name = '{0}' and month ={1} and year = {2}
) then 1 else 0 end xx from dual";
strCommand = string.Format(strCommand, pModel.RC_NAME.Replace("'", "''"), pModel.MONTH, pModel.YEAR);
var dal = new DAL.DAL();
var objExecute = dal.ExecuteScalar(strCommand);
return(1.Equals(objExecute));
}
public IEnumerable <ModelPipelineImport> ReadExcelPipelineImport(
System.IO.Stream pStreamExcel,
int pIntMonth,
string pStrRegionId,
string pStrUploadBy,
int pIntYear)
{
if (pStreamExcel == null || pStreamExcel.Length <= 0)
{
yield break;
}
using (var exel = new OfficeOpenXml.ExcelPackage(pStreamExcel))
{
var exWorkSheet = exel.Workbook.Worksheets[1];
int intColCount = exWorkSheet.Dimension.End.Column;
int intRowCount = exWorkSheet.Dimension.End.Row;
int intStartRow = 4;
int intEndRow = intRowCount - 6;
for (int intRow = intStartRow; intRow < intEndRow; ++intRow)
{
var pipImport = new ModelPipelineImport()
{
DIAMETER = getExcelPipelineValue(exWorkSheet, intRow, enumExcelPipelineColumn.DIAMETER).GetDecimal(),
EFFICIENCY = getExcelPipelineValue(exWorkSheet, intRow, enumExcelPipelineColumn.EFFICIENCY).GetDecimal(),
FLOW = getExcelPipelineValue(exWorkSheet, intRow, enumExcelPipelineColumn.FLOW).GetDecimal(),
LENGTH = getExcelPipelineValue(exWorkSheet, intRow, enumExcelPipelineColumn.LENGTH).GetDecimal(),
LOAD = getExcelPipelineValue(exWorkSheet, intRow, enumExcelPipelineColumn.LOAD).GetDecimal(),
MONTH = pIntMonth,
OUTSIDE_DIAMETER = getExcelPipelineValue(exWorkSheet, intRow, enumExcelPipelineColumn.OUTSIDE_DIAMETER).GetDecimal(),
PIPELINE_ID = 0,
RC_NAME = getExcelPipelineValue(exWorkSheet, intRow, enumExcelPipelineColumn.RC_NAME).GetString(),
REGION = pStrRegionId,
ROUGHNESS = getExcelPipelineValue(exWorkSheet, intRow, enumExcelPipelineColumn.ROUGHNESS).GetDecimal(),
SERVICE_STATE = getExcelPipelineValue(exWorkSheet, intRow, enumExcelPipelineColumn.SERVICE_STATE).GetString(),
UPLOAD_BY = pStrUploadBy,
UPLOAD_DATE = DateTime.Now,
VELOCITY = getExcelPipelineValue(exWorkSheet, intRow, enumExcelPipelineColumn.VELOCITY).GetDecimal(),
WALL_THICKNESS = getExcelPipelineValue(exWorkSheet, intRow, enumExcelPipelineColumn.WALL_THICKNESS).GetDecimal(),
YEAR = pIntYear
};
yield return(pipImport);
}
};
}
public IEnumerable <ModelPipelineImport> GetListPipelineImportDuplicate(IEnumerable <ModelPipelineImport> pListModel)
{
if (pListModel == null && !pListModel.Any(x => x != null))
{
return(null);
}
string strCommand = @"select * from PIPELINE_IMPORT
where year = {0} and month = {1}
and RC_NAME in ({2})";
ModelPipelineImport model1 = pListModel.Where(x => x != null).First();
DAL.DAL dal = new DAL.DAL();
IEnumerable <ModelPipelineImport> result = Enumerable.Empty <ModelPipelineImport>();
pListModel.Batch(1000).ForEach(list => {
string strAllRcName = list
.Where(x => x != null && !string.IsNullOrEmpty(x.RC_NAME))
.Select(x => "'" + x.RC_NAME.Replace("'", "''") + "'")
.Aggregate((x, y) => x + "," + y);
if (string.IsNullOrEmpty(strAllRcName))
{
return;
}
string _command = string.Format(strCommand, model1.YEAR, model1.MONTH, strAllRcName);
result = result.Concat(
dal.ReadData(
pStrCommand: _command,
pFuncReadData: r => new ModelPipelineImport(r)
)
);
});
dal = null;
return(result);
}
