Bplus/ApiWeb/ApiProcessToken/Models/systemDataAccess.cs

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
                                     AND (PUD.CAMPAIGN_ID IS NULL OR PUD.CAMPAIGN_ID < 2000000 OR PUD.CAMPAIGN_ID > 2999999)";

                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
                                     AND (rcd.CAMPAIGN_ID IS NULL OR rcd.CAMPAIGN_ID < 2000000 OR rcd.CAMPAIGN_ID > 2999999)";
                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
                                     AND (rcd.CAMPAIGN_ID IS NULL OR rcd.CAMPAIGN_ID < 2000000 OR rcd.CAMPAIGN_ID > 2999999)";
                
                // 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;
        }






    }
}