using System; using System.Collections.Generic; using System.Linq; using System.Web; using Oracle.ManagedDataAccess.Client; using System.Data; namespace ResfullApi.Models { public class systemDataAccess { public systemDataAccess() { } public static DataSet SYS_GET_SERVICE_BY_ID(string v_SV_ID) { string str; str = ""; str = "api_pkg.SYS_GET_SERVICE_BY_ID"; OracleParameter[] parms; parms = new OracleParameter[] { new OracleParameter("v_sv_id", OracleDbType.NVarchar2), new OracleParameter("P_RESULT",OracleDbType.RefCursor,ParameterDirection.Output), }; parms[0].Value = v_SV_ID; return DataAccess.getDataFromProcedure(str, "", parms); } public static DataSet SYS_PUSH_USSD_DETAIL_GET_LIST( string v_users, string v_campaignId, string v_serviceId, string v_msisdn, string v_sendStatus, string v_isSuccess, string v_fromDate, string v_toDate, string v_order, string v_rowsOnPage, string v_seqPage) { DataSet ds = new DataSet(); OracleConnection dbConnection = DataAccess.getPoolingConnection(); try { dbConnection.Open(); int rowsOnPage = int.TryParse(v_rowsOnPage, out int r) ? r : 50; int seqPage = int.TryParse(v_seqPage, out int s) ? s : 1; string baseSql = @"SELECT PUD.ID, PUD.REQUEST_ID, PUD.CAMPAIGN_ID, CAMP.NAME AS CAMPAIGN_NAME, PUD.SERVICE_ID, PUD.MSISDN, PUD.SEND_TIME, PUD.SEND_STATUS, PUD.TOTAL_STEP, PUD.IS_STEP_1, PUD.STEP_1_TIME, PUD.IS_STEP_2, PUD.STEP_2_TIME, PUD.ERROR_CODE, PUD.IS_SUCCESS, PUD.INSERT_TIME, PUD.LAST_UPDATE,PUD.MSG_CONTENT FROM PUSH_USSD_DETAIL PUD LEFT JOIN B_CAMPAIGN CAMP ON PUD.CAMPAIGN_ID = CAMP.ID WHERE 1=1"; string where = ""; if (!string.IsNullOrEmpty(v_campaignId) && v_campaignId != "-1") { where += " AND PUD.CAMPAIGN_ID = :v_campaignId"; } if (!string.IsNullOrEmpty(v_serviceId) && v_serviceId != "-1") { where += " AND PUD.SERVICE_ID = :v_serviceId"; } if (!string.IsNullOrEmpty(v_msisdn) && v_msisdn != "-1") { where += " AND PUD.MSISDN = :v_msisdn"; } if (!string.IsNullOrEmpty(v_sendStatus) && v_sendStatus != "-1") { where += " AND PUD.SEND_STATUS = :v_sendStatus"; } if (!string.IsNullOrEmpty(v_isSuccess) && v_isSuccess != "-1") { where += " AND PUD.IS_SUCCESS = :v_isSuccess"; } if (!string.IsNullOrEmpty(v_fromDate) && v_fromDate != "-1") { where += " AND TRUNC(PUD.SEND_TIME) >= TRUNC(TO_DATE(:v_fromDate, 'DD/MM/YYYY'))"; } if (!string.IsNullOrEmpty(v_toDate) && v_toDate != "-1") { where += " AND TRUNC(PUD.SEND_TIME) <= TRUNC(TO_DATE(:v_toDate, 'DD/MM/YYYY'))"; } string orderBy = " ORDER BY PUD.SEND_TIME " + (v_order == "asc" ? "ASC" : "DESC") + ", PUD.ID " + (v_order == "asc" ? "ASC" : "DESC"); string countSql = "SELECT COUNT(*) FROM (" + baseSql + where + ")"; OracleCommand countCmd = new OracleCommand(countSql, dbConnection); if (!string.IsNullOrEmpty(v_campaignId) && v_campaignId != "-1") countCmd.Parameters.Add(":v_campaignId", OracleDbType.NVarchar2).Value = v_campaignId; if (!string.IsNullOrEmpty(v_serviceId) && v_serviceId != "-1") countCmd.Parameters.Add(":v_serviceId", OracleDbType.NVarchar2).Value = v_serviceId; if (!string.IsNullOrEmpty(v_msisdn) && v_msisdn != "-1") countCmd.Parameters.Add(":v_msisdn", OracleDbType.NVarchar2).Value = v_msisdn; if (!string.IsNullOrEmpty(v_sendStatus) && v_sendStatus != "-1") countCmd.Parameters.Add(":v_sendStatus", OracleDbType.NVarchar2).Value = v_sendStatus; if (!string.IsNullOrEmpty(v_isSuccess) && v_isSuccess != "-1") countCmd.Parameters.Add(":v_isSuccess", OracleDbType.NVarchar2).Value = v_isSuccess; if (!string.IsNullOrEmpty(v_fromDate) && v_fromDate != "-1") countCmd.Parameters.Add(":v_fromDate", OracleDbType.NVarchar2).Value = v_fromDate; if (!string.IsNullOrEmpty(v_toDate) && v_toDate != "-1") countCmd.Parameters.Add(":v_toDate", OracleDbType.NVarchar2).Value = v_toDate; int totalRows = Convert.ToInt32(countCmd.ExecuteScalar()); int minRow = (seqPage - 1) * rowsOnPage; string dataSql = string.Format(@"SELECT * FROM ( SELECT A.*, ROWNUM rnum FROM ({0}{1}{2}) A WHERE ROWNUM <= {3} ) WHERE rnum > {4}", baseSql, where, orderBy, minRow + rowsOnPage, minRow); OracleCommand dataCmd = new OracleCommand(dataSql, dbConnection); if (!string.IsNullOrEmpty(v_campaignId) && v_campaignId != "-1") dataCmd.Parameters.Add(":v_campaignId", OracleDbType.NVarchar2).Value = v_campaignId; if (!string.IsNullOrEmpty(v_serviceId) && v_serviceId != "-1") dataCmd.Parameters.Add(":v_serviceId", OracleDbType.NVarchar2).Value = v_serviceId; if (!string.IsNullOrEmpty(v_msisdn) && v_msisdn != "-1") dataCmd.Parameters.Add(":v_msisdn", OracleDbType.NVarchar2).Value = v_msisdn; if (!string.IsNullOrEmpty(v_sendStatus) && v_sendStatus != "-1") dataCmd.Parameters.Add(":v_sendStatus", OracleDbType.NVarchar2).Value = v_sendStatus; if (!string.IsNullOrEmpty(v_isSuccess) && v_isSuccess != "-1") dataCmd.Parameters.Add(":v_isSuccess", OracleDbType.NVarchar2).Value = v_isSuccess; if (!string.IsNullOrEmpty(v_fromDate) && v_fromDate != "-1") dataCmd.Parameters.Add(":v_fromDate", OracleDbType.NVarchar2).Value = v_fromDate; if (!string.IsNullOrEmpty(v_toDate) && v_toDate != "-1") dataCmd.Parameters.Add(":v_toDate", OracleDbType.NVarchar2).Value = v_toDate; OracleDataAdapter da = new OracleDataAdapter(dataCmd); da.Fill(ds); // Append page info DataTable meta = new DataTable("META"); meta.Columns.Add("TOTAL_PAGE"); meta.Columns.Add("ROW_ON_PAGE"); meta.Columns.Add("SEQ_PAGE"); var rowMeta = meta.NewRow(); rowMeta["TOTAL_PAGE"] = Math.Ceiling((double)totalRows / rowsOnPage).ToString(); rowMeta["ROW_ON_PAGE"] = rowsOnPage.ToString(); rowMeta["SEQ_PAGE"] = seqPage.ToString(); meta.Rows.Add(rowMeta); ds.Tables.Add(meta); } catch (Exception ex) { throw ex; } finally { dbConnection.Close(); } return ds; } public static DataSet SYS_UPDATE_LOG_CHARGE(string msisdn, string sv_id, string money, string cmd_code, string cmd_msg, string err_code, string err_msg, string isRenew) { string str; str = ""; str = "api_pkg.SYS_UPDATE_LOG_CHARGE"; OracleParameter[] parms; parms = new OracleParameter[] { new OracleParameter("v_msisdn", OracleDbType.NVarchar2), new OracleParameter("v_sv_id", OracleDbType.NVarchar2), new OracleParameter("v_money", OracleDbType.NVarchar2), new OracleParameter("v_cmd_code", OracleDbType.NVarchar2), new OracleParameter("v_cmd_msg", OracleDbType.NVarchar2), new OracleParameter("v_err_code", OracleDbType.NVarchar2), new OracleParameter("v_err_msg", OracleDbType.NVarchar2), new OracleParameter("v_isrenew", OracleDbType.NVarchar2), new OracleParameter("P_RESULT",OracleDbType.RefCursor,ParameterDirection.Output), }; parms[0].Value = msisdn; parms[2].Value = sv_id; parms[3].Value = money; parms[4].Value = cmd_code; parms[5].Value = cmd_msg; parms[6].Value = err_code; parms[7].Value = err_msg; parms[8].Value = isRenew; return DataAccess.getDataFromProcedure(str, "", parms); } public static DataSet SYS_REPORT_COUNT_DAILY_GET_LIST(string v_users, string v_id, string v_reportDate, string v_campaignId, string v_serviceId, string v_fromDate, string v_toDate, string v_order, string v_rowsOnPage, string v_seqPage) { DataSet ds = new DataSet(); OracleConnection dbConnection = DataAccess.getPoolingConnection(); try { dbConnection.Open(); // Parse pagination parameters int rowsOnPage = int.TryParse(v_rowsOnPage, out int r) ? r : 10; int seqPage = int.TryParse(v_seqPage, out int s) ? s : 1; // Build base query for counting total records string countSql = @"SELECT COUNT(*) FROM REPORT_COUNT_DAILY rcd LEFT JOIN B_CAMPAIGN c ON rcd.CAMPAIGN_ID = c.ID LEFT JOIN B_CAMPAIGN_ADD ca ON c.ID = ca.CAMPAIGN_ID AND rcd.SERVICE_ID = ca.SERVICE_ADD_ID AND ca.IS_DELETE = 0 LEFT JOIN B_SERVICE s ON (ca.SERVICE_ADD_ID = s.ID OR (ca.SERVICE_ADD_ID IS NULL AND rcd.SERVICE_ID = s.ID)) WHERE 1=1"; string dataSql = @"SELECT rcd.ID, rcd.REPORT_DATE, rcd.CAMPAIGN_ID, c.NAME AS CAMPAIGN_NAME, c.PRIORITY, c.IS_DEFAULT, c.IS_MYSERVICE, c.ADD_TYPE, rcd.SERVICE_ID, s.NAME AS SERVICE_NAME, rcd.COUNT_SEND_1, rcd.COUNT_SUCCESS_1, rcd.COUNT_FAIL_1, rcd.COUNT_PRESS_1, rcd.COUNT_SEND_2, rcd.COUNT_PRESS_2, rcd.COUNT_REG_SUCCESS, rcd.COUNT_REG_FAIL, rcd.INSERT_TIME FROM REPORT_COUNT_DAILY rcd LEFT JOIN B_CAMPAIGN c ON rcd.CAMPAIGN_ID = c.ID LEFT JOIN B_CAMPAIGN_ADD ca ON c.ID = ca.CAMPAIGN_ID AND rcd.SERVICE_ID = ca.SERVICE_ADD_ID AND ca.IS_DELETE = 0 LEFT JOIN B_SERVICE s ON (ca.SERVICE_ADD_ID = s.ID OR (ca.SERVICE_ADD_ID IS NULL AND rcd.SERVICE_ID = s.ID)) WHERE 1=1"; // Add filters if (v_id != null && v_id != "-1") { countSql += " AND rcd.ID = :v_id"; dataSql += " AND rcd.ID = :v_id"; } if (v_reportDate != null && v_reportDate != "-1") { countSql += " AND TRUNC(rcd.REPORT_DATE) = TRUNC(TO_DATE(:v_reportDate, 'DD/MM/YYYY'))"; dataSql += " AND TRUNC(rcd.REPORT_DATE) = TRUNC(TO_DATE(:v_reportDate, 'DD/MM/YYYY'))"; } if (v_campaignId != null && v_campaignId != "-1") { countSql += " AND rcd.CAMPAIGN_ID = :v_campaignId"; dataSql += " AND rcd.CAMPAIGN_ID = :v_campaignId"; } if (v_serviceId != null && v_serviceId != "-1") { countSql += " AND rcd.SERVICE_ID = :v_serviceId"; dataSql += " AND rcd.SERVICE_ID = :v_serviceId"; } if (v_fromDate != null && v_fromDate != "-1") { countSql += " AND TRUNC(rcd.REPORT_DATE) >= TRUNC(TO_DATE(:v_fromDate, 'DD/MM/YYYY'))"; dataSql += " AND TRUNC(rcd.REPORT_DATE) >= TRUNC(TO_DATE(:v_fromDate, 'DD/MM/YYYY'))"; } if (v_toDate != null && v_toDate != "-1") { countSql += " AND TRUNC(rcd.REPORT_DATE) <= TRUNC(TO_DATE(:v_toDate, 'DD/MM/YYYY'))"; dataSql += " AND TRUNC(rcd.REPORT_DATE) <= TRUNC(TO_DATE(:v_toDate, 'DD/MM/YYYY'))"; } // Add ordering dataSql += " ORDER BY rcd.REPORT_DATE " + (v_order == "desc" ? "DESC" : "ASC") + ", rcd.ID " + (v_order == "desc" ? "DESC" : "ASC"); // Calculate pagination OracleCommand countCmd = new OracleCommand(countSql, dbConnection); countCmd.CommandType = CommandType.Text; if (v_id != null && v_id != "-1") { countCmd.Parameters.Add(":v_id", OracleDbType.NVarchar2).Value = v_id; } if (v_reportDate != null && v_reportDate != "-1") { countCmd.Parameters.Add(":v_reportDate", OracleDbType.NVarchar2).Value = v_reportDate; } if (v_campaignId != null && v_campaignId != "-1") { countCmd.Parameters.Add(":v_campaignId", OracleDbType.NVarchar2).Value = v_campaignId; } if (v_serviceId != null && v_serviceId != "-1") { countCmd.Parameters.Add(":v_serviceId", OracleDbType.NVarchar2).Value = v_serviceId; } if (v_fromDate != null && v_fromDate != "-1") { countCmd.Parameters.Add(":v_fromDate", OracleDbType.NVarchar2).Value = v_fromDate; } if (v_toDate != null && v_toDate != "-1") { countCmd.Parameters.Add(":v_toDate", OracleDbType.NVarchar2).Value = v_toDate; } int totalRows = Convert.ToInt32(countCmd.ExecuteScalar()); int totalPage = (int)Math.Ceiling((double)totalRows / rowsOnPage); // Get paginated data int minRow = (seqPage - 1) * rowsOnPage; dataSql = string.Format(@"SELECT * FROM ( SELECT A.*, ROWNUM rnum FROM ({0}) A WHERE ROWNUM <= {1} ) WHERE rnum > {2}", dataSql, minRow + rowsOnPage, minRow); OracleCommand dataCmd = new OracleCommand(dataSql, dbConnection); dataCmd.CommandType = CommandType.Text; if (v_id != null && v_id != "-1") { dataCmd.Parameters.Add(":v_id", OracleDbType.NVarchar2).Value = v_id; } if (v_reportDate != null && v_reportDate != "-1") { dataCmd.Parameters.Add(":v_reportDate", OracleDbType.NVarchar2).Value = v_reportDate; } if (v_campaignId != null && v_campaignId != "-1") { dataCmd.Parameters.Add(":v_campaignId", OracleDbType.NVarchar2).Value = v_campaignId; } if (v_serviceId != null && v_serviceId != "-1") { dataCmd.Parameters.Add(":v_serviceId", OracleDbType.NVarchar2).Value = v_serviceId; } if (v_fromDate != null && v_fromDate != "-1") { dataCmd.Parameters.Add(":v_fromDate", OracleDbType.NVarchar2).Value = v_fromDate; } if (v_toDate != null && v_toDate != "-1") { dataCmd.Parameters.Add(":v_toDate", OracleDbType.NVarchar2).Value = v_toDate; } OracleDataAdapter dataAdapter = new OracleDataAdapter(dataCmd); dataAdapter.Fill(ds); // Add pagination metadata to each row if (ds.Tables.Count > 0) { if (ds.Tables[0].Columns.Contains("ROW_ON_PAGE")) { ds.Tables[0].Columns.Remove("ROW_ON_PAGE"); } if (ds.Tables[0].Columns.Contains("SEQ_PAGE")) { ds.Tables[0].Columns.Remove("SEQ_PAGE"); } if (ds.Tables[0].Columns.Contains("TOTAL_PAGE")) { ds.Tables[0].Columns.Remove("TOTAL_PAGE"); } ds.Tables[0].Columns.Add("ROW_ON_PAGE", typeof(string)); ds.Tables[0].Columns.Add("SEQ_PAGE", typeof(string)); ds.Tables[0].Columns.Add("TOTAL_PAGE", typeof(string)); ds.Tables[0].Columns.Add("STATUS", typeof(string)); ds.Tables[0].Columns.Add("MSG", typeof(string)); foreach (DataRow row in ds.Tables[0].Rows) { row["ROW_ON_PAGE"] = rowsOnPage.ToString(); row["SEQ_PAGE"] = seqPage.ToString(); row["TOTAL_PAGE"] = totalPage.ToString(); row["STATUS"] = "0"; row["MSG"] = "Success"; } } } catch (OracleException ex) { throw ex; } catch (Exception ex) { throw ex; } finally { dbConnection.Close(); } return ds; } public static DataSet SYS_REPORT_ERROR_DAILY_GET_LIST(string v_users, string v_reportDate, string v_campaignId, string v_serviceId) { DataSet ds = new DataSet(); OracleConnection dbConnection = DataAccess.getPoolingConnection(); try { dbConnection.Open(); string dataSql = @"SELECT ID, REPORT_DATE, CAMPAIGN_ID, SERVICE_ID, ERROR_CODE, COUNT_NUM, INSERT_TIME FROM REPORT_ERROR_DAILY WHERE 1=1"; // Add filters based on foreign keys if (v_reportDate != null && v_reportDate != "-1" && !string.IsNullOrEmpty(v_reportDate)) { dataSql += " AND TRUNC(REPORT_DATE) = TRUNC(TO_DATE(:v_reportDate, 'DD/MM/YYYY'))"; } if (v_campaignId != null && v_campaignId != "-1" && !string.IsNullOrEmpty(v_campaignId)) { dataSql += " AND CAMPAIGN_ID = :v_campaignId"; } if (v_serviceId != null && v_serviceId != "-1" && !string.IsNullOrEmpty(v_serviceId)) { dataSql += " AND SERVICE_ID = :v_serviceId"; } // Order by error code dataSql += " ORDER BY ERROR_CODE, ID"; OracleCommand dataCmd = new OracleCommand(dataSql, dbConnection); dataCmd.CommandType = CommandType.Text; if (v_reportDate != null && v_reportDate != "-1" && !string.IsNullOrEmpty(v_reportDate)) { dataCmd.Parameters.Add(":v_reportDate", OracleDbType.NVarchar2).Value = v_reportDate; } if (v_campaignId != null && v_campaignId != "-1" && !string.IsNullOrEmpty(v_campaignId)) { dataCmd.Parameters.Add(":v_campaignId", OracleDbType.NVarchar2).Value = v_campaignId; } if (v_serviceId != null && v_serviceId != "-1" && !string.IsNullOrEmpty(v_serviceId)) { dataCmd.Parameters.Add(":v_serviceId", OracleDbType.NVarchar2).Value = v_serviceId; } OracleDataAdapter dataAdapter = new OracleDataAdapter(dataCmd); dataAdapter.Fill(ds); // Add status columns if (ds.Tables.Count > 0) { ds.Tables[0].Columns.Add("STATUS", typeof(string)); ds.Tables[0].Columns.Add("MSG", typeof(string)); foreach (DataRow row in ds.Tables[0].Rows) { row["STATUS"] = "0"; row["MSG"] = "Success"; } } } catch (OracleException ex) { throw ex; } catch (Exception ex) { throw ex; } finally { dbConnection.Close(); } return ds; } } }