public int GetGateImportID(ModelGateStationImport pModel)
{
if (pModel == null)
{
return(0);
}
string strSql = @"select Gate_Id from gateStation_Import
where rownum = 1 and Gate_Name = '{0}' and Month={1} and year = {2} ";
string.Format(strSql, pModel.GATE_NAME, pModel.MONTH, pModel.YEAR);
var dal = new DAL.DAL();
var objExecute = dal.ExecuteScalar(strSql);
return(0);
}
public bool IsGateStationImportDuplicate(ModelGateStationImport pModel)
{
if (pModel == null)
{
return(false);
}
string strCommand = @"select case when exists(
select 1 from GateStation_Import where gate_Name = '{0}' and month ={1} and year = {2}
) then 1 else 0 end xx from dual";
strCommand = string.Format(strCommand, pModel.GATE_NAME.Replace("'", "''"), pModel.MONTH, pModel.YEAR);
var dal = new DAL.DAL();
var objExecute = dal.ExecuteScalar(strCommand);
return(1.Equals(objExecute));
}
public void InsertGateImport(ModelGateStationImport pModel)
{
if (pModel == null)
{
return;
}
string strCommand = "INSERT INTO GATESTATION_IMPORT ( GATE_NAME,PRESSURE,FLOW, MONTH, YEAR,UPLOAD_DATE, UPLOAD_BY,REGION) VALUES ('{0}'," + (pModel.PRESSURE != null ? pModel.PRESSURE.ToString() : "NULL") + "," + (pModel.FLOW != null ? pModel.FLOW.ToString() : "NULL") + ",{1},{2},SYSDATE,'{3}', NULL)";
strCommand = string.Format(strCommand,
pModel.GATE_NAME.Trim().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 UpdateGateImport(ModelGateStationImport pModel)
{
if (pModel == null)
{
return;
}
string strCommand = "UPDATE GATESTATION_IMPORT SET GATE_NAME = '{0}',PRESSURE = " + (pModel.PRESSURE != null ? pModel.PRESSURE.ToString() : "NULL") + ",FLOW = " + (pModel.FLOW != null ? pModel.FLOW.ToString() : "NULL") + ",UPLOAD_DATE = sysdate,UPLOAD_BY = '{1}',REGION = NULL WHERE 1=1 AND GATE_ID = {2} AND MONTH = {3} AND YEAR = {4}";
strCommand = string.Format(strCommand,
pModel.GATE_NAME.Trim().Replace("'", "''"),
pModel.UPLOAD_BY.Trim().Replace("'", "''"),
//pModel.REGION.Trim().Replace("'", "''"),
pModel.GATE_ID,
pModel.MONTH,
pModel.YEAR);
var dal = new DAL.DAL();
dal.ExecuteNonQuery(strCommand);
dal = null;
}
public IEnumerable <ModelGateStationImport> ReadExcelGateStationImport(
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 GateFlow = getExcelGateValue(exWorkSheet, intRow, enumExcelGateColumn.GateFlow);
var GatePressure = getExcelGateValue(exWorkSheet, intRow, enumExcelGateColumn.GatePressure);
var gateImport = new ModelGateStationImport()
{
FLOW = (GateFlow != null && GateFlow.ToString().ToLower() != "n/a") ? GateFlow.GetDecimal() : default(decimal?),
GATE_NAME = getExcelGateValue(exWorkSheet, intRow, enumExcelGateColumn.GateName).GetString(),
MONTH = pIntMonth,
PRESSURE = GatePressure != null?GatePressure.GetDecimal() : default(decimal?),
REGION = pStrRegionId,
UPLOAD_BY = pStrUploadBy,
UPLOAD_DATE = DateTime.Now,
YEAR = pIntYear
};
yield return(gateImport);
}
}
}
