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); } } }