Bplus/ApiWeb/ApiProcessToken/Models/banlance/balanceDataAccess.cs

using Oracle.ManagedDataAccess.Client;
using StackExchange.Redis;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Channels;
using System.Web;

namespace ResfullApi.Models.balance
{
    public class balanceDataAccess
    {
        public balanceDataAccess()
        {

        }




        public static void InsertBulkBlackList(string sSQL, List<List<string>> dataColumn)
        {
            OracleConnection connection = null;
            try
            {
                connection = DataAccess.getPoolingConnection();
                connection.Open();
                OracleTransaction transaction;

                // Start a local transaction
                transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);
                using (var command = connection.CreateCommand())
                {
                    command.CommandText = sSQL;
                    command.CommandType = CommandType.StoredProcedure;
                    command.ArrayBindCount = dataColumn[0].Count;
                    command.Transaction = transaction;




                    var v_msisdn = dataColumn[0].ToArray();
                    OracleParameter param0 = new OracleParameter("v_msisdn", OracleDbType.NVarchar2, ParameterDirection.Input);
                    param0.Value = v_msisdn;
                    param0.Size = v_msisdn.Length;
                    command.Parameters.Add(param0);

                    var v_type = dataColumn[1].ToArray();
                    OracleParameter param1 = new OracleParameter("v_type", OracleDbType.NVarchar2, ParameterDirection.Input);
                    param1.Value = v_type;
                    param1.Size = v_type.Length;
                    command.Parameters.Add(param1);

                    var v_sv_id = dataColumn[2].ToArray();
                    OracleParameter param2 = new OracleParameter("v_sv_id", OracleDbType.NVarchar2, ParameterDirection.Input);
                    param2.Value = v_sv_id;
                    param2.Size = v_sv_id.Length;
                    command.Parameters.Add(param2);

                    var v_action = dataColumn[3].ToArray();
                    OracleParameter param3 = new OracleParameter("v_action", OracleDbType.NVarchar2, ParameterDirection.Input);
                    param3.Value = v_action;
                    param3.Size = v_action.Length;
                    command.Parameters.Add(param3);


                    var v_users = dataColumn[4].ToArray();
                    OracleParameter param4 = new OracleParameter("v_users", OracleDbType.NVarchar2, ParameterDirection.Input);
                    param4.Value = v_users;
                    param4.Size = v_users.Length;
                    command.Parameters.Add(param4);

                    

                    command.ExecuteNonQuery();



                    transaction.Commit();

                }

            }
            catch (OracleException ex)
            {
                Console.WriteLine(ex.ToString());
                throw;
            }
            finally
            {
                connection.Close();
            }
        }






        public static DataSet reportByCampaign(string v_users, string status,string v_fromDate, string v_toDate)
        {

            string str;
            str = "";
            str = "BALANCE_PKG.REPORT_BY_CAMPAIGN";
            OracleParameter[] parms;
            parms = new OracleParameter[]
                            {

                                new OracleParameter("v_users", OracleDbType.NVarchar2),
                                new OracleParameter("v_status", OracleDbType.NVarchar2),

                                new OracleParameter("v_FROMDATE", OracleDbType.NVarchar2),
                                new OracleParameter("v_TODATE", OracleDbType.NVarchar2),
                                
                                new OracleParameter("P_RESULT",OracleDbType.RefCursor,ParameterDirection.Output),
            };
            parms[0].Value = v_users;
            parms[1].Value = status;

            parms[2].Value = v_fromDate;
            parms[3].Value = v_toDate;
           

            return DataAccess.getDataFromProcedure(str, "", parms);
        }



        public static DataSet reportTotalByCampaign(string v_users,  string v_fromDate, string v_toDate,string status,string isDefault)
        {

            string str;
            str = "";
            str = "BALANCE_PKG.CAM_GET_REPORT_TOTAL";
            OracleParameter[] parms;
            parms = new OracleParameter[]
                            {

                                new OracleParameter("v_users", OracleDbType.NVarchar2),
                                new OracleParameter("v_fromDate", OracleDbType.NVarchar2),

                                new OracleParameter("v_toDate", OracleDbType.NVarchar2),
                                new OracleParameter("v_status", OracleDbType.NVarchar2),
                                new OracleParameter("v_isDefault", OracleDbType.NVarchar2),
                                new OracleParameter("P_RESULT",OracleDbType.RefCursor,ParameterDirection.Output),
            };
            parms[0].Value = v_users;
            parms[1].Value = v_fromDate;

            parms[2].Value = v_toDate;
            parms[3].Value = status;
            parms[4].Value = isDefault;


            return DataAccess.getDataFromProcedure(str, "", parms);
        }








        public static DataSet comGetList(string v_id,string v_users, string v_name, string v_code, string v_fromDate, string v_toDate
            , string v_order, string v_rowsOnPage, string v_seqPage)
        {

            string str;
            str = "";
            str = "BALANCE_PKG.COM_GET";
            OracleParameter[] parms;
            parms = new OracleParameter[]
                            {

                                new OracleParameter("v_id", OracleDbType.NVarchar2),
                                new OracleParameter("v_users", OracleDbType.NVarchar2),

                                new OracleParameter("v_name", OracleDbType.NVarchar2),
                                new OracleParameter("v_code", OracleDbType.NVarchar2),
                                new OracleParameter("v_fromDate", OracleDbType.NVarchar2),
                                new OracleParameter("v_toDate", OracleDbType.NVarchar2),

                                new OracleParameter("v_order", OracleDbType.NVarchar2),
                                new OracleParameter("v_rowsOnPage", OracleDbType.NVarchar2),
                                new OracleParameter("v_seqPage", OracleDbType.NVarchar2),


                                new OracleParameter("P_RESULT",OracleDbType.RefCursor,ParameterDirection.Output),
            };
            parms[0].Value = v_id;
            parms[1].Value = v_users;

            parms[2].Value = v_name;
            parms[3].Value = v_code;
            parms[4].Value = v_fromDate;
            parms[5].Value = v_toDate;
            parms[6].Value = v_order;
            parms[7].Value = v_rowsOnPage;
            parms[8].Value = v_seqPage;

            return DataAccess.getDataFromProcedure(str, "", parms);
        }


        public static DataSet comInsert(string V_CODE, string V_NAME,string V_PHONE,string V_EMAIL,string V_ADDRESS,string V_DESCRIPTION,
            string V_NOTE,string V_USERS)
        {

            string str;
            str = "";
            str = "BALANCE_PKG.COM_INSERT";
            OracleParameter[] parms;
            parms = new OracleParameter[]
                            {

                                new OracleParameter("V_CODE", OracleDbType.NVarchar2),
                                new OracleParameter("V_NAME", OracleDbType.NVarchar2),
                                new OracleParameter("V_PHONE", OracleDbType.NVarchar2),
                                new OracleParameter("V_EMAIL", OracleDbType.NVarchar2),
                                new OracleParameter("V_ADDRESS", OracleDbType.NVarchar2),
                                new OracleParameter("V_DESCRIPTION", OracleDbType.NVarchar2),

                                new OracleParameter("V_NOTE", OracleDbType.NVarchar2),
                                new OracleParameter("V_USERS", OracleDbType.NVarchar2),

                                new OracleParameter("P_RESULT",OracleDbType.RefCursor,ParameterDirection.Output),
            };
            parms[0].Value = V_CODE;
            parms[1].Value = V_NAME;
            parms[2].Value = V_PHONE;
            parms[3].Value = V_EMAIL;
            parms[4].Value = V_ADDRESS;
            parms[5].Value = V_DESCRIPTION;

            parms[6].Value = V_NOTE;
            parms[7].Value = V_USERS;

            return DataAccess.getDataFromProcedure(str, "", parms);
        }



        public static DataSet comUpdate(string V_ID,string V_CODE, string V_NAME, string V_PHONE, string V_EMAIL, string V_ADDRESS, string V_DESCRIPTION,
            string V_NOTE, string V_USERS,string V_TYPE)
        {

            string str;
            str = "";
            str = "BALANCE_PKG.COM_UPDATE";
            OracleParameter[] parms;
            parms = new OracleParameter[]
                            {
                                new OracleParameter("V_ID", OracleDbType.NVarchar2),
                                new OracleParameter("V_CODE", OracleDbType.NVarchar2),
                                new OracleParameter("V_NAME", OracleDbType.NVarchar2),
                                new OracleParameter("V_PHONE", OracleDbType.NVarchar2),
                                new OracleParameter("V_EMAIL", OracleDbType.NVarchar2),
                                new OracleParameter("V_ADDRESS", OracleDbType.NVarchar2),
                                new OracleParameter("V_DESCRIPTION", OracleDbType.NVarchar2),

                                new OracleParameter("V_NOTE", OracleDbType.NVarchar2),
                                new OracleParameter("V_USERS", OracleDbType.NVarchar2),
                                new OracleParameter("V_TYPE", OracleDbType.NVarchar2),

                                new OracleParameter("P_RESULT",OracleDbType.RefCursor,ParameterDirection.Output),
            };
            parms[0].Value = V_ID;
            parms[1].Value = V_CODE;
            parms[2].Value = V_NAME;
            parms[3].Value = V_PHONE;
            parms[4].Value = V_EMAIL;
            parms[5].Value = V_ADDRESS;
            parms[6].Value = V_DESCRIPTION;

            parms[7].Value = V_NOTE;
            parms[8].Value = V_USERS;
            parms[9].Value = V_TYPE;
            

            return DataAccess.getDataFromProcedure(str, "", parms);
        }

        public static DataSet listSubGetList(string v_id, string v_users,string V_sub_type, string v_name,string v_code,string v_fromDate,string v_toDate
            , string v_order,string v_rowsOnPage,string v_seqPage,string v_isActive)
        {

            string str;
            str = "";
            str = "BALANCE_PKG.LIST_SUB_GET";
            OracleParameter[] parms;
            parms = new OracleParameter[]
                            {

                                new OracleParameter("v_id", OracleDbType.NVarchar2),
                                new OracleParameter("v_users", OracleDbType.NVarchar2),
                                new OracleParameter("V_sub_type", OracleDbType.NVarchar2),
                                new OracleParameter("v_name", OracleDbType.NVarchar2),
                                new OracleParameter("v_code", OracleDbType.NVarchar2),
                                new OracleParameter("v_fromDate", OracleDbType.NVarchar2),
                                new OracleParameter("v_toDate", OracleDbType.NVarchar2),

                                new OracleParameter("v_order", OracleDbType.NVarchar2),
                                new OracleParameter("v_rowsOnPage", OracleDbType.NVarchar2),
                                new OracleParameter("v_seqPage", OracleDbType.NVarchar2),

                                new OracleParameter("v_isActive", OracleDbType.NVarchar2),

                                new OracleParameter("P_RESULT",OracleDbType.RefCursor,ParameterDirection.Output),
            };
            parms[0].Value = v_id;
            parms[1].Value = v_users;
            parms[2].Value = V_sub_type;
            parms[3].Value = v_name;
            parms[4].Value = v_code;
            parms[5].Value = v_fromDate;
            parms[6].Value = v_toDate;
            parms[7].Value = v_order;
            parms[8].Value = v_rowsOnPage;
            parms[9].Value = v_seqPage;
            parms[10].Value = v_isActive;

            return DataAccess.getDataFromProcedure(str, "", parms);
        }


        public static DataSet blistSubInsert(string V_CODE, string V_NAME,
            string V_NOTE, string V_USERS,string V_SUB_TYPE)
        {

            string str;
            str = "";
            str = "BALANCE_PKG.LIST_SUB_INSERT";
            OracleParameter[] parms;
            parms = new OracleParameter[]
                            {

                                new OracleParameter("V_CODE", OracleDbType.NVarchar2),
                                new OracleParameter("V_NAME", OracleDbType.NVarchar2),
                               
                                new OracleParameter("V_NOTE", OracleDbType.NVarchar2),
                                new OracleParameter("V_USERS", OracleDbType.NVarchar2),
                                new OracleParameter("V_SUB_TYPE", OracleDbType.NVarchar2),
                                
                                new OracleParameter("P_RESULT",OracleDbType.RefCursor,ParameterDirection.Output),
            };
            parms[0].Value = V_CODE;
            parms[1].Value = V_NAME;
           

            parms[2].Value = V_NOTE;
            parms[3].Value = V_USERS;
            parms[4].Value = V_SUB_TYPE;

            return DataAccess.getDataFromProcedure(str, "", parms);
        }



        public static DataSet blistSubUpdate(string V_ID, string V_CODE, string V_NAME,string V_SUB_TYPE,
            string V_NOTE, string V_USERS, string V_TYPE,string status)
        {

            string str;
            str = "";
            str = "BALANCE_PKG.LIST_SUB_UPDATE";
            OracleParameter[] parms;
            parms = new OracleParameter[]
                            {
                                new OracleParameter("V_ID", OracleDbType.NVarchar2),
                                new OracleParameter("V_CODE", OracleDbType.NVarchar2),
                                new OracleParameter("V_NAME", OracleDbType.NVarchar2),
                                new OracleParameter("V_SUB_TYPE", OracleDbType.NVarchar2),
                                

                                new OracleParameter("V_NOTE", OracleDbType.NVarchar2),
                                new OracleParameter("V_USERS", OracleDbType.NVarchar2),
                                new OracleParameter("V_TYPE", OracleDbType.NVarchar2),
                                new OracleParameter("V_STATUS", OracleDbType.NVarchar2),
                                new OracleParameter("P_RESULT",OracleDbType.RefCursor,ParameterDirection.Output),
            };
            parms[0].Value = V_ID;
            parms[1].Value = V_CODE;
            parms[2].Value = V_NAME;
            parms[3].Value = V_SUB_TYPE;
           


            parms[4].Value = V_NOTE;
            parms[5].Value = V_USERS;
            parms[6].Value = V_TYPE;
            parms[7].Value = status;

            return DataAccess.getDataFromProcedure(str, "", parms);
        }






        public static DataSet listSubFileGetList(string v_id, string v_users, string V_list_sub_id, string v_name, string v_code, string v_fromDate, string v_toDate
            , string v_order, string v_rowsOnPage, string v_seqPage)
        {

            string str;
            str = "";
            str = "BALANCE_PKG.LIST_SUB_FILE_GET";
            OracleParameter[] parms;
            parms = new OracleParameter[]
                            {

                                new OracleParameter("v_id", OracleDbType.NVarchar2),
                                new OracleParameter("v_users", OracleDbType.NVarchar2),
                                new OracleParameter("V_list_sub_id", OracleDbType.NVarchar2),
                                new OracleParameter("v_name", OracleDbType.NVarchar2),
                                new OracleParameter("v_code", OracleDbType.NVarchar2),
                                new OracleParameter("v_fromDate", OracleDbType.NVarchar2),
                                new OracleParameter("v_toDate", OracleDbType.NVarchar2),

                                new OracleParameter("v_order", OracleDbType.NVarchar2),
                                new OracleParameter("v_rowsOnPage", OracleDbType.NVarchar2),
                                new OracleParameter("v_seqPage", OracleDbType.NVarchar2),



                                new OracleParameter("P_RESULT",OracleDbType.RefCursor,ParameterDirection.Output),
            };
            parms[0].Value = v_id;
            parms[1].Value = v_users;
            parms[2].Value = V_list_sub_id;
            parms[3].Value = v_name;
            parms[4].Value = v_code;
            parms[5].Value = v_fromDate;
            parms[6].Value = v_toDate;
            parms[7].Value = v_order;
            parms[8].Value = v_rowsOnPage;
            parms[9].Value = v_seqPage;

            return DataAccess.getDataFromProcedure(str, "", parms);
        }


        public static DataSet blistSubFileInsert(string V_CODE, string V_NAME,
            string V_NOTE, string V_USERS, string V_LIST_SUB_ID, string V_FILE_ID, string V_FILE_NAME)
        {

            string str;
            str = "";
            str = "BALANCE_PKG.LIST_SUB_FILE_INSERT";
            OracleParameter[] parms;
            parms = new OracleParameter[]
                            {

                                new OracleParameter("V_CODE", OracleDbType.NVarchar2),
                                new OracleParameter("V_NAME", OracleDbType.NVarchar2),

                                new OracleParameter("V_NOTE", OracleDbType.NVarchar2),
                                new OracleParameter("V_USERS", OracleDbType.NVarchar2),
                                new OracleParameter("V_LIST_SUB_ID", OracleDbType.NVarchar2),
                                new OracleParameter("V_FILE_ID", OracleDbType.NVarchar2),
                                new OracleParameter("V_FILE_NAME", OracleDbType.NVarchar2),
                                new OracleParameter("P_RESULT",OracleDbType.RefCursor,ParameterDirection.Output),
            };
            parms[0].Value = V_CODE;
            parms[1].Value = V_NAME;


            parms[2].Value = V_NOTE;
            parms[3].Value = V_USERS;
            parms[4].Value = V_LIST_SUB_ID;
            parms[5].Value = V_FILE_ID;
            parms[6].Value = V_FILE_NAME;

            return DataAccess.getDataFromProcedure(str, "", parms);
        }



        public static DataSet blistSubFileUpdate(string V_ID, string V_CODE, string V_NAME, string V_LIST_SUB_ID,
            string V_NOTE, string V_USERS, string V_TYPE,string status)
        {

            string str;
            str = "";
            str = "BALANCE_PKG.LIST_SUB_FILE_UPDATE";
            OracleParameter[] parms;
            parms = new OracleParameter[]
                            {
                                new OracleParameter("V_ID", OracleDbType.NVarchar2),
                                new OracleParameter("V_CODE", OracleDbType.NVarchar2),
                                new OracleParameter("V_NAME", OracleDbType.NVarchar2),
                                new OracleParameter("V_LIST_SUB_ID", OracleDbType.NVarchar2),


                                new OracleParameter("V_NOTE", OracleDbType.NVarchar2),
                                new OracleParameter("V_USERS", OracleDbType.NVarchar2),
                                new OracleParameter("V_TYPE", OracleDbType.NVarchar2),
                                new OracleParameter("V_STATUS", OracleDbType.NVarchar2),

                                new OracleParameter("P_RESULT",OracleDbType.RefCursor,ParameterDirection.Output),
            };
            parms[0].Value = V_ID;
            parms[1].Value = V_CODE;
            parms[2].Value = V_NAME;
            parms[3].Value = V_LIST_SUB_ID;



            parms[4].Value = V_NOTE;
            parms[5].Value = V_USERS;
            parms[6].Value = V_TYPE;
            parms[7].Value = status;


            return DataAccess.getDataFromProcedure(str, "", parms);
        }



        public static DataSet sortCodeGetList(  string v_type, string v_users)
        {

            string str;
            str = "";
            str = "BALANCE_PKG.SORT_CODE_GET_LIST";
            OracleParameter[] parms;
            parms = new OracleParameter[]
                            {

                                new OracleParameter("v_type", OracleDbType.NVarchar2),
                                new OracleParameter("v_users", OracleDbType.NVarchar2),

                                

                                new OracleParameter("P_RESULT",OracleDbType.RefCursor,ParameterDirection.Output),
            };
            parms[0].Value = v_type;
            parms[1].Value = v_users;

           
            return DataAccess.getDataFromProcedure(str, "", parms);
        }



        public static DataSet changeStatus(string v_type,string v_id, string v_status, string v_users)
        {

            string str;
            str = "";
            str = "BALANCE_PKG.CHANGE_STATUS";
            OracleParameter[] parms;
            parms = new OracleParameter[]
                            {

                                new OracleParameter("v_type", OracleDbType.NVarchar2),
                                new OracleParameter("v_id", OracleDbType.NVarchar2),
                                new OracleParameter("v_status", OracleDbType.NVarchar2),
                                new OracleParameter("v_users", OracleDbType.NVarchar2),



                                new OracleParameter("P_RESULT",OracleDbType.RefCursor,ParameterDirection.Output),
            };
            parms[0].Value = v_type;
            parms[1].Value = v_id;
            parms[2].Value = v_status;
            parms[3].Value = v_users;


            return DataAccess.getDataFromProcedure(str, "", parms);
        }




        public static DataSet balGetList(string v_id, string v_users,string v_name,string v_code,string v_fromDate,string v_toDate
            , string v_order,string v_rowsOnPage,string v_seqPage,string isActive)
        {

            string str;
            str = "";
            str = "BALANCE_PKG.BL_GET";
            OracleParameter[] parms;
            parms = new OracleParameter[]
                            {

                                new OracleParameter("v_id", OracleDbType.NVarchar2),
                                new OracleParameter("v_users", OracleDbType.NVarchar2),

                                new OracleParameter("v_name", OracleDbType.NVarchar2),
                                new OracleParameter("v_code", OracleDbType.NVarchar2),
                                new OracleParameter("v_fromDate", OracleDbType.NVarchar2),
                                new OracleParameter("v_toDate", OracleDbType.NVarchar2),

                                new OracleParameter("v_order", OracleDbType.NVarchar2),
                                new OracleParameter("v_rowsOnPage", OracleDbType.NVarchar2),
                                new OracleParameter("v_seqPage", OracleDbType.NVarchar2),
                                new OracleParameter("v_isActive", OracleDbType.NVarchar2),


                                new OracleParameter("P_RESULT",OracleDbType.RefCursor,ParameterDirection.Output),
            };
            parms[0].Value = v_id;
            parms[1].Value = v_users;

            parms[2].Value = v_name;
            parms[3].Value = v_code;
            parms[4].Value = v_fromDate;
            parms[5].Value = v_toDate;
            parms[6].Value = v_order;
            parms[7].Value = v_rowsOnPage;
            parms[8].Value = v_seqPage;
            parms[9].Value = isActive;
            return DataAccess.getDataFromProcedure(str, "", parms);
        }


        public static DataSet balInsert(string V_CODE, string V_NAME, string V_MIN, string V_MAX,
            string V_NOTE, string V_USERS)
        {

            string str;
            str = "";
            str = "BALANCE_PKG.BL_INSERT";
            OracleParameter[] parms;
            parms = new OracleParameter[]
                            {

                                new OracleParameter("V_CODE", OracleDbType.NVarchar2),
                                new OracleParameter("V_NAME", OracleDbType.NVarchar2),
                                new OracleParameter("V_MIN", OracleDbType.NVarchar2),
                                new OracleParameter("V_MAX", OracleDbType.NVarchar2),
                               

                                new OracleParameter("V_NOTE", OracleDbType.NVarchar2),
                                new OracleParameter("V_USERS", OracleDbType.NVarchar2),

                                new OracleParameter("P_RESULT",OracleDbType.RefCursor,ParameterDirection.Output),
            };
            parms[0].Value = V_CODE;
            parms[1].Value = V_NAME;
            parms[2].Value = V_MIN;
            parms[3].Value = V_MAX;
          

            parms[4].Value = V_NOTE;
            parms[5].Value = V_USERS;

            return DataAccess.getDataFromProcedure(str, "", parms);
        }



        public static DataSet balUpdate(string V_ID,string V_CODE, string V_NAME, string V_MIN, string V_MAX,
            string V_NOTE, string V_USERS,string V_TYPE)
        {

            string str;
            str = "";
            str = "BALANCE_PKG.BL_UPDATE";
            OracleParameter[] parms;
            parms = new OracleParameter[]
                            {
                                new OracleParameter("V_ID", OracleDbType.NVarchar2),
                                new OracleParameter("V_CODE", OracleDbType.NVarchar2),
                                new OracleParameter("V_NAME", OracleDbType.NVarchar2),
                                new OracleParameter("V_MIN", OracleDbType.NVarchar2),
                                new OracleParameter("V_MAX", OracleDbType.NVarchar2),


                                new OracleParameter("V_NOTE", OracleDbType.NVarchar2),
                                new OracleParameter("V_USERS", OracleDbType.NVarchar2),
                                new OracleParameter("V_TYPE", OracleDbType.NVarchar2),

                                new OracleParameter("P_RESULT",OracleDbType.RefCursor,ParameterDirection.Output),
            };
            parms[0].Value = V_ID;
            parms[1].Value = V_CODE;
            parms[2].Value = V_NAME;
            parms[3].Value = V_MIN;
            parms[4].Value = V_MAX;
            

            parms[5].Value = V_NOTE;
            parms[6].Value = V_USERS;
            parms[7].Value = V_TYPE;


            return DataAccess.getDataFromProcedure(str, "", parms);
        }





        public static DataSet expGetList(string v_id, string v_users, string v_name, string v_code, string v_fromDate, string v_toDate
            , string v_order, string v_rowsOnPage, string v_seqPage,string v_isActive)
        {

            string str;
            str = "";
            str = "BALANCE_PKG.EP_GET";
            OracleParameter[] parms;
            parms = new OracleParameter[]
                            {

                                new OracleParameter("v_id", OracleDbType.NVarchar2),
                                new OracleParameter("v_users", OracleDbType.NVarchar2),

                                new OracleParameter("v_name", OracleDbType.NVarchar2),
                                new OracleParameter("v_code", OracleDbType.NVarchar2),
                                new OracleParameter("v_fromDate", OracleDbType.NVarchar2),
                                new OracleParameter("v_toDate", OracleDbType.NVarchar2),

                                new OracleParameter("v_order", OracleDbType.NVarchar2),
                                new OracleParameter("v_rowsOnPage", OracleDbType.NVarchar2),
                                new OracleParameter("v_seqPage", OracleDbType.NVarchar2),
                                new OracleParameter("v_isActive", OracleDbType.NVarchar2),
                                new OracleParameter("P_RESULT",OracleDbType.RefCursor,ParameterDirection.Output),
            };
            parms[0].Value = v_id;
            parms[1].Value = v_users;

            parms[2].Value = v_name;
            parms[3].Value = v_code;
            parms[4].Value = v_fromDate;
            parms[5].Value = v_toDate;
            parms[6].Value = v_order;
            parms[7].Value = v_rowsOnPage;
            parms[8].Value = v_seqPage;
            parms[9].Value = v_isActive;
            return DataAccess.getDataFromProcedure(str, "", parms);
        }


        public static DataSet expInsert(string V_CODE, string V_NAME, string V_TOTAL_DAY_USE,
            string V_NOTE, string V_USERS,string min,string max)
        {

            string str;
            str = "";
            str = "BALANCE_PKG.EP_INSERT";
            OracleParameter[] parms;
            parms = new OracleParameter[]
                            {

                                new OracleParameter("V_CODE", OracleDbType.NVarchar2),
                                new OracleParameter("V_NAME", OracleDbType.NVarchar2),
                                new OracleParameter("V_TOTAL_DAY_USE", OracleDbType.NVarchar2),
                               
                                new OracleParameter("V_NOTE", OracleDbType.NVarchar2),
                                new OracleParameter("V_USERS", OracleDbType.NVarchar2),

                                new OracleParameter("V_MIN", OracleDbType.NVarchar2),
                                new OracleParameter("V_MAX", OracleDbType.NVarchar2),

                                new OracleParameter("P_RESULT",OracleDbType.RefCursor,ParameterDirection.Output),
            };
            parms[0].Value = V_CODE;
            parms[1].Value = V_NAME;
            parms[2].Value = V_TOTAL_DAY_USE;
           

            parms[3].Value = V_NOTE;
            parms[4].Value = V_USERS;

            parms[5].Value = min;
            parms[6].Value = max;

            return DataAccess.getDataFromProcedure(str, "", parms);
        }



        public static DataSet expUpdate(string V_ID, string V_CODE, string V_NAME, string V_TOTAL_DAY_USE,
            string V_NOTE, string V_USERS, string V_TYPE,string min,string max)
        {

            string str;
            str = "";
            str = "BALANCE_PKG.EP_UPDATE";
            OracleParameter[] parms;
            parms = new OracleParameter[]
                            {
                                new OracleParameter("V_ID", OracleDbType.NVarchar2),
                                new OracleParameter("V_CODE", OracleDbType.NVarchar2),
                                new OracleParameter("V_NAME", OracleDbType.NVarchar2),
                                new OracleParameter("V_TOTAL_DAY_USE", OracleDbType.NVarchar2),
                              
                                new OracleParameter("V_NOTE", OracleDbType.NVarchar2),
                                new OracleParameter("V_USERS", OracleDbType.NVarchar2),
                                new OracleParameter("V_TYPE", OracleDbType.NVarchar2),

                                new OracleParameter("V_MIN", OracleDbType.NVarchar2),
                                new OracleParameter("V_MAX", OracleDbType.NVarchar2),

                                new OracleParameter("P_RESULT",OracleDbType.RefCursor,ParameterDirection.Output),
            };
            parms[0].Value = V_ID;
            parms[1].Value = V_CODE;
            parms[2].Value = V_NAME;
            parms[3].Value = V_TOTAL_DAY_USE;
            
            parms[4].Value = V_NOTE;
            parms[5].Value = V_USERS;
            parms[6].Value = V_TYPE;

            parms[7].Value = min;
            parms[8].Value = max;
            return DataAccess.getDataFromProcedure(str, "", parms);
        }








        public static DataSet paramGetList(string v_id, string v_users)
        {

            string str;
            str = "";
            str = "BALANCE_PKG.PAM_GET";
            OracleParameter[] parms;
            parms = new OracleParameter[]
                            {

                                new OracleParameter("v_id", OracleDbType.NVarchar2),
                                new OracleParameter("v_users", OracleDbType.NVarchar2),

                                new OracleParameter("P_RESULT",OracleDbType.RefCursor,ParameterDirection.Output),
            };
            parms[0].Value = v_id;
            parms[1].Value = v_users;

            return DataAccess.getDataFromProcedure(str, "", parms);
        }


        public static DataSet paramInsert(string V_CODE, string V_SVALUES, string V_NOTE,
            string V_STATUS, string V_CODE_GROUP, string V_DISPLAY0, string V_DISPLAY1, string V_DISPLAY2, string V_DISPLAY3,
            string V_DISPLAY4, string V_DISPLAY5, string V_GAME_ID, string V_CHANNEL, string V_USERS)
        {

            string str;
            str = "";
            str = "BALANCE_PKG.PAM_INSERT";
            OracleParameter[] parms;
            parms = new OracleParameter[]
                            {

                                new OracleParameter("V_CODE", OracleDbType.NVarchar2),
                                new OracleParameter("V_SVALUES", OracleDbType.NVarchar2),
                                new OracleParameter("V_NOTE", OracleDbType.NVarchar2),

                                new OracleParameter("V_STATUS", OracleDbType.NVarchar2),
                                new OracleParameter("V_CODE_GROUP", OracleDbType.NVarchar2),

                                new OracleParameter("V_DISPLAY0", OracleDbType.NVarchar2),
                                new OracleParameter("V_DISPLAY1", OracleDbType.NVarchar2),
                                new OracleParameter("V_DISPLAY2", OracleDbType.NVarchar2),
                                new OracleParameter("V_DISPLAY3", OracleDbType.NVarchar2),
                                new OracleParameter("V_DISPLAY4", OracleDbType.NVarchar2),
                                new OracleParameter("V_DISPLAY5", OracleDbType.NVarchar2),
                                new OracleParameter("V_GAME_ID", OracleDbType.NVarchar2),
                                new OracleParameter("V_CHANNEL", OracleDbType.NVarchar2),
                                new OracleParameter("V_USERS", OracleDbType.NVarchar2),
                                new OracleParameter("P_RESULT",OracleDbType.RefCursor,ParameterDirection.Output),
            };
            parms[0].Value = V_CODE;
            parms[1].Value = V_SVALUES;
            parms[2].Value = V_NOTE;
            parms[3].Value = V_STATUS;
            parms[4].Value = V_CODE_GROUP;

            parms[5].Value = V_DISPLAY0;
            parms[6].Value = V_DISPLAY1;
            parms[7].Value = V_DISPLAY2;
            parms[8].Value = V_DISPLAY3;
            parms[9].Value = V_DISPLAY4;
            parms[10].Value = V_DISPLAY5;

            parms[11].Value = V_GAME_ID;
            parms[12].Value = V_CHANNEL;
            parms[13].Value = V_USERS;

            return DataAccess.getDataFromProcedure(str, "", parms);
        }



        public static DataSet paramUpdate(string V_ID, string V_CODE, string V_SVALUES, string V_NOTE,
            string V_STATUS, string V_CODE_GROUP, string V_DISPLAY0, string V_DISPLAY1, string V_DISPLAY2, string V_DISPLAY3,
            string V_DISPLAY4, string V_DISPLAY5, string V_GAME_ID, string V_CHANNEL, string V_USERS, string V_TYPE)
        {

            string str;
            str = "";
            str = "BALANCE_PKG.PAM_UPDATE";
            OracleParameter[] parms;
            parms = new OracleParameter[]
                            {
                                new OracleParameter("V_ID", OracleDbType.NVarchar2),
                                new OracleParameter("V_CODE", OracleDbType.NVarchar2),
                                new OracleParameter("V_SVALUES", OracleDbType.NVarchar2),
                                new OracleParameter("V_NOTE", OracleDbType.NVarchar2),
                                new OracleParameter("V_STATUS", OracleDbType.NVarchar2),
                                new OracleParameter("V_CODE_GROUP", OracleDbType.NVarchar2),

                                new OracleParameter("V_DISPLAY0", OracleDbType.NVarchar2),
                                new OracleParameter("V_DISPLAY1", OracleDbType.NVarchar2),
                                new OracleParameter("V_DISPLAY2", OracleDbType.NVarchar2),
                                new OracleParameter("V_DISPLAY3", OracleDbType.NVarchar2),
                                new OracleParameter("V_DISPLAY4", OracleDbType.NVarchar2),
                                new OracleParameter("V_DISPLAY5", OracleDbType.NVarchar2),

                                new OracleParameter("V_GAME_ID", OracleDbType.NVarchar2),
                                new OracleParameter("V_CHANNEL", OracleDbType.NVarchar2),
                                new OracleParameter("V_USERS", OracleDbType.NVarchar2),
                                new OracleParameter("V_TYPE", OracleDbType.NVarchar2),

                                new OracleParameter("P_RESULT",OracleDbType.RefCursor,ParameterDirection.Output),
            };
            parms[0].Value = V_ID;
            parms[1].Value = V_CODE;
            parms[2].Value = V_SVALUES;
            parms[3].Value = V_NOTE;
            parms[4].Value = V_STATUS;
            parms[5].Value = V_CODE_GROUP;

            parms[6].Value = V_DISPLAY0;
            parms[7].Value = V_DISPLAY1;
            parms[8].Value = V_DISPLAY2;
            parms[9].Value = V_DISPLAY3;
            parms[10].Value = V_DISPLAY4;
            parms[11].Value = V_DISPLAY5;

            parms[12].Value = V_GAME_ID;
            parms[13].Value = V_CHANNEL;
            parms[14].Value = V_USERS;
            parms[15].Value = V_TYPE;



            return DataAccess.getDataFromProcedure(str, "", parms);
        }










        public static DataSet usersGetList(string v_id, string v_users)
        {

            string str;
            str = "";
            str = "BALANCE_PKG.USERS_GET";
            OracleParameter[] parms;
            parms = new OracleParameter[]
                            {

                                new OracleParameter("v_id", OracleDbType.NVarchar2),
                                new OracleParameter("v_users", OracleDbType.NVarchar2),

                                new OracleParameter("P_RESULT",OracleDbType.RefCursor,ParameterDirection.Output),
            };
            parms[0].Value = v_id;
            parms[1].Value = v_users;

            return DataAccess.getDataFromProcedure(str, "", parms);
        }


        public static DataSet usersInsert(string V_USERNAME, string V_PASSWORD, string V_ROLE,
            string V_COUNTRY_CODE, string V_IS_LOCK, string V_TOTAL_FALSE, string V_TIME_LOCK, string V_NOTE, string V_USERS)
        {

            string str;
            str = "";
            str = "BALANCE_PKG.USERS_INSERT";
            OracleParameter[] parms;
            parms = new OracleParameter[]
                            {

                                new OracleParameter("V_USERNAME", OracleDbType.NVarchar2),
                                new OracleParameter("V_PASSWORD", OracleDbType.NVarchar2),
                                new OracleParameter("V_ROLE", OracleDbType.NVarchar2),

                                new OracleParameter("V_COUNTRY_CODE", OracleDbType.NVarchar2),
                                new OracleParameter("V_IS_LOCK", OracleDbType.NVarchar2),

                                new OracleParameter("V_TOTAL_FALSE", OracleDbType.NVarchar2),
                                new OracleParameter("V_TIME_LOCK", OracleDbType.NVarchar2),
                                new OracleParameter("V_NOTE", OracleDbType.NVarchar2),
                                new OracleParameter("V_USERS", OracleDbType.NVarchar2),
                                
                                new OracleParameter("P_RESULT",OracleDbType.RefCursor,ParameterDirection.Output),
            };
            parms[0].Value = V_USERNAME;
            parms[1].Value = V_PASSWORD;
            parms[2].Value = V_ROLE;
            parms[3].Value = V_COUNTRY_CODE;
            parms[4].Value = V_IS_LOCK;

            parms[5].Value = V_TOTAL_FALSE;
            parms[6].Value = V_TIME_LOCK;
            parms[7].Value = V_NOTE;
            parms[8].Value = V_USERS;
            
            return DataAccess.getDataFromProcedure(str, "", parms);
        }



        public static DataSet usersUpdate(string V_ID, string V_USERNAME, string V_PASSWORD, string V_ROLE,
            string V_COUNTRY_CODE, string V_IS_LOCK, string V_TOTAL_FALSE, string V_TIME_LOCK, string V_NOTE, string V_USERS, string V_TYPE)
        {

            string str;
            str = "";
            str = "BALANCE_PKG.USERS_UPDATE";
            OracleParameter[] parms;
            parms = new OracleParameter[]
                            {
                                new OracleParameter("V_ID", OracleDbType.NVarchar2),
                                new OracleParameter("V_USERNAME", OracleDbType.NVarchar2),
                                new OracleParameter("V_PASSWORD", OracleDbType.NVarchar2),
                                new OracleParameter("V_ROLE", OracleDbType.NVarchar2),

                                new OracleParameter("V_COUNTRY_CODE", OracleDbType.NVarchar2),
                                new OracleParameter("V_IS_LOCK", OracleDbType.NVarchar2),

                                new OracleParameter("V_TOTAL_FALSE", OracleDbType.NVarchar2),
                                new OracleParameter("V_TIME_LOCK", OracleDbType.NVarchar2),
                                new OracleParameter("V_NOTE", OracleDbType.NVarchar2),
                                new OracleParameter("V_USERS", OracleDbType.NVarchar2),
                                new OracleParameter("V_TYPE", OracleDbType.NVarchar2),

                                new OracleParameter("P_RESULT",OracleDbType.RefCursor,ParameterDirection.Output),
            };
            parms[0].Value = V_ID;
            parms[1].Value = V_USERNAME;
            parms[2].Value = V_PASSWORD;
            parms[3].Value = V_ROLE;
            parms[4].Value = V_COUNTRY_CODE;
            parms[5].Value = V_IS_LOCK;

            parms[6].Value = V_TOTAL_FALSE;
            parms[7].Value = V_TIME_LOCK;
            parms[8].Value = V_NOTE;
            parms[9].Value = V_USERS;
            parms[10].Value = V_TYPE;



            return DataAccess.getDataFromProcedure(str, "", parms);
        }












        public static DataSet svGetList(string v_id, string v_users, string v_name, string v_code, string v_fromDate, string v_toDate
            , string v_order, string v_rowsOnPage, string v_seqPage,string isActive)
        {

            string str;
            str = "";
            str = "BALANCE_PKG.SV_GET";
            OracleParameter[] parms;
            parms = new OracleParameter[]
                            {

                                new OracleParameter("v_id", OracleDbType.NVarchar2),
                                new OracleParameter("v_users", OracleDbType.NVarchar2),

                                 new OracleParameter("v_name", OracleDbType.NVarchar2),
                                new OracleParameter("v_code", OracleDbType.NVarchar2),
                                new OracleParameter("v_fromDate", OracleDbType.NVarchar2),
                                new OracleParameter("v_toDate", OracleDbType.NVarchar2),

                                new OracleParameter("v_order", OracleDbType.NVarchar2),
                                new OracleParameter("v_rowsOnPage", OracleDbType.NVarchar2),
                                new OracleParameter("v_seqPage", OracleDbType.NVarchar2),
                                new OracleParameter("v_isActive", OracleDbType.NVarchar2),

                                new OracleParameter("P_RESULT",OracleDbType.RefCursor,ParameterDirection.Output),
            };
            parms[0].Value = v_id;
            parms[1].Value = v_users;

            parms[2].Value = v_name;
            parms[3].Value = v_code;
            parms[4].Value = v_fromDate;
            parms[5].Value = v_toDate;
            parms[6].Value = v_order;
            parms[7].Value = v_rowsOnPage;
            parms[8].Value = v_seqPage;
            parms[9].Value = isActive;

            return DataAccess.getDataFromProcedure(str, "", parms);
        }
        public static DataSet svGroupGetList(string v_id, string v_users)
        {

            string str;
            str = "";
            str = "BALANCE_PKG.SVGROUP_GET";
            OracleParameter[] parms;
            parms = new OracleParameter[]
                            {
                                new OracleParameter("v_id", OracleDbType.NVarchar2),
                                new OracleParameter("v_users", OracleDbType.NVarchar2),
                                new OracleParameter("P_RESULT",OracleDbType.RefCursor,ParameterDirection.Output),
            };
            parms[0].Value = v_id;
            parms[1].Value = v_users;

            return DataAccess.getDataFromProcedure(str, "", parms);
        }


        public static DataSet svInsert(string V_CODE, string V_NAME, string V_DESCRIPTION,
            string V_SHORT_CODE, string V_COMMAND_REGISTER, string V_CONTENT_EN, string V_CONTENT_FR, string V_CONTENT_LC, string V_NOTE,
            string V_COMPANY_ID, string V_USERS, string msgRegisterSuccess, string msgRegisterFlase,string msgConfirm,string serviceGroupId,string apiServiceId, string isMyservice
            )
        {

            string str;
            str = "";
            str = "BALANCE_PKG.SV_INSERT";
            OracleParameter[] parms;
            parms = new OracleParameter[]
                            {

                                new OracleParameter("V_CODE", OracleDbType.NVarchar2),
                                new OracleParameter("V_NAME", OracleDbType.NVarchar2),
                                new OracleParameter("V_DESCRIPTION", OracleDbType.NVarchar2),

                                new OracleParameter("V_SHORT_CODE", OracleDbType.NVarchar2),
                                new OracleParameter("V_COMMAND_REGISTER", OracleDbType.NVarchar2),

                                new OracleParameter("V_CONTENT_EN", OracleDbType.NVarchar2),
                                new OracleParameter("V_CONTENT_FR", OracleDbType.NVarchar2),
                                new OracleParameter("V_CONTENT_LC", OracleDbType.NVarchar2),
                                new OracleParameter("V_NOTE", OracleDbType.NVarchar2),
                                new OracleParameter("V_COMPANY_ID", OracleDbType.NVarchar2),
                               
                                new OracleParameter("V_USERS", OracleDbType.NVarchar2),

                                new OracleParameter("V_USSD_MSG_REGISTER_SUC", OracleDbType.NVarchar2),
                                new OracleParameter("V_USSD_MSG_REGISTER_FAIL", OracleDbType.NVarchar2),
                                new OracleParameter("V_USSD_CONFIRM", OracleDbType.NVarchar2),
                                new OracleParameter("V_SERVICE_GROUP_ID", OracleDbType.NVarchar2),
                                new OracleParameter("V_API_SERVICE_ID", OracleDbType.NVarchar2),
                                new OracleParameter("v_isMyservice", OracleDbType.NVarchar2),

                                new OracleParameter("P_RESULT",OracleDbType.RefCursor,ParameterDirection.Output),
            };
            parms[0].Value = V_CODE;
            parms[1].Value = V_NAME;
            parms[2].Value = V_DESCRIPTION;
            parms[3].Value = V_SHORT_CODE;
            parms[4].Value = V_COMMAND_REGISTER;

            parms[5].Value = V_CONTENT_EN;
            parms[6].Value = V_CONTENT_FR;
            parms[7].Value = V_CONTENT_LC;
            parms[8].Value = V_NOTE;
            parms[9].Value = V_COMPANY_ID;
            parms[10].Value = V_USERS;

            parms[11].Value = msgRegisterSuccess;
            parms[12].Value = msgRegisterFlase;
            parms[13].Value = msgConfirm;
            parms[14].Value = serviceGroupId;
            parms[15].Value = apiServiceId;
            parms[16].Value = isMyservice;
            return DataAccess.getDataFromProcedure(str, "", parms);
        }



        public static DataSet svUpdate(string V_ID,string V_CODE, string V_NAME, string V_DESCRIPTION,
            string V_SHORT_CODE, string V_COMMAND_REGISTER, string V_CONTENT_EN, string V_CONTENT_FR, string V_CONTENT_LC, string V_NOTE,
            string V_COMPANY_ID, string V_USERS, string V_TYPE, string msgRegisterSuccess, string msgRegisterFlase,string msgConfirm, string serviceGroupId, string apiServiceId, string isMyservice)
        {

            string str;
            str = "";
            str = "BALANCE_PKG.SV_UPDATE";
            OracleParameter[] parms;
            parms = new OracleParameter[]
                            {
                                new OracleParameter("V_ID", OracleDbType.NVarchar2),

                                 new OracleParameter("V_CODE", OracleDbType.NVarchar2),
                                new OracleParameter("V_NAME", OracleDbType.NVarchar2),
                                new OracleParameter("V_DESCRIPTION", OracleDbType.NVarchar2),

                                new OracleParameter("V_SHORT_CODE", OracleDbType.NVarchar2),
                                new OracleParameter("V_COMMAND_REGISTER", OracleDbType.NVarchar2),

                                new OracleParameter("V_CONTENT_EN", OracleDbType.NVarchar2),
                                new OracleParameter("V_CONTENT_FR", OracleDbType.NVarchar2),
                                new OracleParameter("V_CONTENT_LC", OracleDbType.NVarchar2),
                                new OracleParameter("V_NOTE", OracleDbType.NVarchar2),
                                new OracleParameter("V_COMPANY_ID", OracleDbType.NVarchar2),

                                new OracleParameter("V_USERS", OracleDbType.NVarchar2),
                                new OracleParameter("V_TYPE", OracleDbType.NVarchar2),
                                new OracleParameter("V_USSD_MSG_REGISTER_SUC", OracleDbType.NVarchar2),
                                new OracleParameter("V_USSD_MSG_REGISTER_FAIL", OracleDbType.NVarchar2),
                                new OracleParameter("V_USSD_CONFIRM", OracleDbType.NVarchar2),

                                new OracleParameter("V_SERVICE_GROUP_ID", OracleDbType.NVarchar2),
                                new OracleParameter("V_API_SERVICE_ID", OracleDbType.NVarchar2),
                                new OracleParameter("v_isMyservice", OracleDbType.NVarchar2),

                                new OracleParameter("P_RESULT",OracleDbType.RefCursor,ParameterDirection.Output),
            };
            parms[0].Value = V_ID;
            parms[1].Value = V_CODE;
            parms[2].Value = V_NAME;
            parms[3].Value = V_DESCRIPTION;
            parms[4].Value = V_SHORT_CODE;
            parms[5].Value = V_COMMAND_REGISTER;

            parms[6].Value = V_CONTENT_EN;
            parms[7].Value = V_CONTENT_FR;
            parms[8].Value = V_CONTENT_LC;
            parms[9].Value = V_NOTE;
            parms[10].Value = V_COMPANY_ID;
          
            parms[11].Value = V_USERS;
            parms[12].Value = V_TYPE;

            parms[13].Value = msgRegisterSuccess;
            parms[14].Value = msgRegisterFlase;
            parms[15].Value = msgConfirm;
            parms[16].Value = serviceGroupId;
            parms[17].Value = apiServiceId;

            parms[18].Value = isMyservice;

            return DataAccess.getDataFromProcedure(str, "", parms);
        }



        public static void serviceUpdateGroupAndApiById(string id, string serviceGroupId, string apiServiceId)
        {
            if (string.IsNullOrEmpty(id)) return;
            if (string.IsNullOrEmpty(serviceGroupId) && string.IsNullOrEmpty(apiServiceId)) return;
            OracleConnection dbConnection = DataAccess.getPoolingConnection();
            try
            {
                dbConnection.Open();
                List<string> sets = new List<string>();
                if (!string.IsNullOrEmpty(serviceGroupId)) sets.Add("SERVICE_GROUP_ID = :groupId");
                if (!string.IsNullOrEmpty(apiServiceId)) sets.Add("API_SERVICE_ID = :apiId");
                if (sets.Count == 0) return;
                string sql = "UPDATE SERVICE SET " + string.Join(",", sets) + " WHERE ID = :id";
                using (OracleCommand cmd = new OracleCommand(sql, dbConnection))
                {
                    if (!string.IsNullOrEmpty(serviceGroupId)) cmd.Parameters.Add(":groupId", OracleDbType.NVarchar2).Value = serviceGroupId;
                    if (!string.IsNullOrEmpty(apiServiceId)) cmd.Parameters.Add(":apiId", OracleDbType.NVarchar2).Value = apiServiceId;
                    cmd.Parameters.Add(":id", OracleDbType.NVarchar2).Value = id;
                    cmd.ExecuteNonQuery();
                }
            }
            finally
            {
                dbConnection.Close();
            }
        }

        public static void serviceUpdateGroupAndApiByCode(string code, string serviceGroupId, string apiServiceId)
        {
            if (string.IsNullOrEmpty(code)) return;
            if (string.IsNullOrEmpty(serviceGroupId) && string.IsNullOrEmpty(apiServiceId)) return;
            OracleConnection dbConnection = DataAccess.getPoolingConnection();
            try
            {
                dbConnection.Open();
                List<string> sets = new List<string>();
                if (!string.IsNullOrEmpty(serviceGroupId)) sets.Add("SERVICE_GROUP_ID = :groupId");
                if (!string.IsNullOrEmpty(apiServiceId)) sets.Add("API_SERVICE_ID = :apiId");
                if (sets.Count == 0) return;
                string sql = "UPDATE SERVICE SET " + string.Join(",", sets) + " WHERE CODE = :code";
                using (OracleCommand cmd = new OracleCommand(sql, dbConnection))
                {
                    if (!string.IsNullOrEmpty(serviceGroupId)) cmd.Parameters.Add(":groupId", OracleDbType.NVarchar2).Value = serviceGroupId;
                    if (!string.IsNullOrEmpty(apiServiceId)) cmd.Parameters.Add(":apiId", OracleDbType.NVarchar2).Value = apiServiceId;
                    cmd.Parameters.Add(":code", OracleDbType.NVarchar2).Value = code;
                    cmd.ExecuteNonQuery();
                }
            }
            finally
            {
                dbConnection.Close();
            }
        }



        public static DataSet svAddGetList(string v_id, string v_users, string v_name, string v_code, string v_fromDate, string v_toDate
            , string v_order, string v_rowsOnPage, string v_seqPage)
        {

            string str;
            str = "";
            str = "BALANCE_PKG.SVADD_GET";
            OracleParameter[] parms;
            parms = new OracleParameter[]
                            {

                                new OracleParameter("v_id", OracleDbType.NVarchar2),
                                new OracleParameter("v_users", OracleDbType.NVarchar2),

                                new OracleParameter("v_name", OracleDbType.NVarchar2),
                                new OracleParameter("v_code", OracleDbType.NVarchar2),
                                new OracleParameter("v_fromDate", OracleDbType.NVarchar2),
                                new OracleParameter("v_toDate", OracleDbType.NVarchar2),

                                new OracleParameter("v_order", OracleDbType.NVarchar2),
                                new OracleParameter("v_rowsOnPage", OracleDbType.NVarchar2),
                                new OracleParameter("v_seqPage", OracleDbType.NVarchar2),


                                new OracleParameter("P_RESULT",OracleDbType.RefCursor,ParameterDirection.Output),
            };
            parms[0].Value = v_id;
            parms[1].Value = v_users;

            parms[2].Value = v_name;
            parms[3].Value = v_code;
            parms[4].Value = v_fromDate;
            parms[5].Value = v_toDate;
            parms[6].Value = v_order;
            parms[7].Value = v_rowsOnPage;
            parms[8].Value = v_seqPage;

            return DataAccess.getDataFromProcedure(str, "", parms);
        }


        public static DataSet svAddInsert(string V_CODE, string V_NAME, string V_ADD_TYPE,
            string V_NUMBER_DISPLAY, string V_PRIORITY, string V_SERVICE_ID, string V_NOTE,
             string V_USERS)
        {

            string str;
            str = "";
            str = "BALANCE_PKG.SVADD_INSERT";
            OracleParameter[] parms;
            parms = new OracleParameter[]
                            {

                                new OracleParameter("V_CODE", OracleDbType.NVarchar2),
                                new OracleParameter("V_NAME", OracleDbType.NVarchar2),
                                new OracleParameter("V_ADD_TYPE", OracleDbType.NVarchar2),

                                new OracleParameter("V_NUMBER_DISPLAY", OracleDbType.NVarchar2),
                                new OracleParameter("V_PRIORITY", OracleDbType.NVarchar2),

                                new OracleParameter("V_SERVICE_ID", OracleDbType.NVarchar2),
                                new OracleParameter("V_NOTE", OracleDbType.NVarchar2),
                               

                                new OracleParameter("V_USERS", OracleDbType.NVarchar2),
                                new OracleParameter("P_RESULT",OracleDbType.RefCursor,ParameterDirection.Output),
            };
            parms[0].Value = V_CODE;
            parms[1].Value = V_NAME;
            parms[2].Value = V_ADD_TYPE;
            parms[3].Value = V_NUMBER_DISPLAY;
            parms[4].Value = V_PRIORITY;

            parms[5].Value = V_SERVICE_ID;
            parms[6].Value = V_NOTE;
            parms[7].Value = V_USERS;


            return DataAccess.getDataFromProcedure(str, "", parms);
        }



        public static DataSet svAddUpdate(string V_ID, string V_CODE, string V_NAME, string V_ADD_TYPE,
            string V_NUMBER_DISPLAY, string V_PRIORITY, string V_SERVICE_ID, string V_NOTE, string V_USERS, string V_TYPE)
        {

            string str;
            str = "";
            str = "BALANCE_PKG.SVADD_UPDATE";
            OracleParameter[] parms;
            parms = new OracleParameter[]
                            {
                                new OracleParameter("V_ID", OracleDbType.NVarchar2),

                                new OracleParameter("V_CODE", OracleDbType.NVarchar2),
                                new OracleParameter("V_NAME", OracleDbType.NVarchar2),
                                new OracleParameter("V_ADD_TYPE", OracleDbType.NVarchar2),

                                new OracleParameter("V_NUMBER_DISPLAY", OracleDbType.NVarchar2),
                                new OracleParameter("V_PRIORITY", OracleDbType.NVarchar2),

                                new OracleParameter("V_SERVICE_ID", OracleDbType.NVarchar2),
                                new OracleParameter("V_NOTE", OracleDbType.NVarchar2),


                                new OracleParameter("V_USERS", OracleDbType.NVarchar2),
                                new OracleParameter("V_TYPE", OracleDbType.NVarchar2),

                                new OracleParameter("P_RESULT",OracleDbType.RefCursor,ParameterDirection.Output),
            };
            parms[0].Value = V_ID;
            parms[1].Value = V_CODE;
            parms[2].Value = V_NAME;
            parms[3].Value = V_ADD_TYPE;
            parms[4].Value = V_NUMBER_DISPLAY;
            parms[5].Value = V_PRIORITY;

            parms[6].Value = V_SERVICE_ID;
            parms[7].Value = V_NOTE;

            parms[8].Value = V_USERS;
            parms[9].Value = V_TYPE;



            return DataAccess.getDataFromProcedure(str, "", parms);
        }








        public static DataSet camGetList(string v_id, string v_users, string v_name, string v_code, string v_fromDate, string v_toDate
            , string v_order, string v_rowsOnPage, string v_seqPage,string v_status,string isDefault,string isMyservice)
        {

            string str;
            str = "";
            str = "BALANCE_PKG.CAM_GET";
            OracleParameter[] parms;
            parms = new OracleParameter[]
                            {

                                new OracleParameter("v_id", OracleDbType.NVarchar2),
                                new OracleParameter("v_users", OracleDbType.NVarchar2),
                                new OracleParameter("v_name", OracleDbType.NVarchar2),
                                new OracleParameter("v_code", OracleDbType.NVarchar2),
                                new OracleParameter("v_fromDate", OracleDbType.NVarchar2),
                                new OracleParameter("v_toDate", OracleDbType.NVarchar2),

                                new OracleParameter("v_order", OracleDbType.NVarchar2),
                                new OracleParameter("v_rowsOnPage", OracleDbType.NVarchar2),
                                new OracleParameter("v_seqPage", OracleDbType.NVarchar2),
                                new OracleParameter("v_status", OracleDbType.NVarchar2),
                                new OracleParameter("v_isDefault", OracleDbType.NVarchar2),
                                new OracleParameter("v_isMyservice", OracleDbType.NVarchar2),
                                new OracleParameter("P_RESULT",OracleDbType.RefCursor,ParameterDirection.Output),
            };
            parms[0].Value = v_id;
            parms[1].Value = v_users;

            parms[2].Value = v_name;
            parms[3].Value = v_code;
            parms[4].Value = v_fromDate;
            parms[5].Value = v_toDate;
            parms[6].Value = v_order;
            parms[7].Value = v_rowsOnPage;
            parms[8].Value = v_seqPage;
            parms[9].Value = v_status;
            parms[10].Value = isDefault;
            parms[11].Value = isMyservice;
            return DataAccess.getDataFromProcedure(str, "", parms);
        }

        public static DataSet camGetListCalendar( string v_users, string v_fromDate, string v_toDate,string status)
        {

            string str;
            str = "";
            str = "BALANCE_PKG.CAM_GET_CALENDAR";
            OracleParameter[] parms;
            parms = new OracleParameter[]
                            {

                               
                                new OracleParameter("v_users", OracleDbType.NVarchar2),
                                new OracleParameter("v_fromDate", OracleDbType.NVarchar2),
                                new OracleParameter("v_toDate", OracleDbType.NVarchar2),
                                new OracleParameter("v_status", OracleDbType.NVarchar2),
                                new OracleParameter("P_RESULT",OracleDbType.RefCursor,ParameterDirection.Output),
            };
           
            parms[0].Value = v_users;

          
            parms[1].Value = v_fromDate;
            parms[2].Value = v_toDate;
            parms[3].Value = status;

            return DataAccess.getDataFromProcedure(str, "", parms);
        }




        public static DataSet camInsert(string V_CODE, string V_NAME, string V_FROM_DATE,
            string V_TO_DATE, string V_FROM_HOUR, string V_TO_HOUR, string V_RULE_ADD,
             string V_PRIORITY, string V_BALANCE_ID, string V_EXPIRE_DATE_ID, string V_STATUS, string V_NOTE, string V_USERS,string V_NUMBERDIS,string addType,string title,string isDefault,string isMyservice)
        {

            string str;
            str = "";
            str = "BALANCE_PKG.CAM_INSERT";
            OracleParameter[] parms;
            parms = new OracleParameter[]
                            {

                                new OracleParameter("V_CODE", OracleDbType.NVarchar2),
                                new OracleParameter("V_NAME", OracleDbType.NVarchar2),
                                new OracleParameter("V_FROM_DATE", OracleDbType.NVarchar2),

                                new OracleParameter("V_TO_DATE", OracleDbType.NVarchar2),
                                new OracleParameter("V_FROM_HOUR", OracleDbType.NVarchar2),

                                new OracleParameter("V_TO_HOUR", OracleDbType.NVarchar2),
                                new OracleParameter("V_RULE_ADD", OracleDbType.NVarchar2),

                                new OracleParameter("V_PRIORITY", OracleDbType.NVarchar2),
                                new OracleParameter("V_BALANCE_ID", OracleDbType.NVarchar2),
                                new OracleParameter("V_EXPIRE_DATE_ID", OracleDbType.NVarchar2),
                                new OracleParameter("V_STATUS", OracleDbType.NVarchar2),
                                new OracleParameter("V_NOTE", OracleDbType.NVarchar2),
                                
                                new OracleParameter("V_USERS", OracleDbType.NVarchar2),
                                new OracleParameter("V_DISPLAY", OracleDbType.NVarchar2),
                                new OracleParameter("V_ADD_TYPE", OracleDbType.NVarchar2),
                                new OracleParameter("V_TITLE", OracleDbType.NVarchar2),
                                new OracleParameter("V_DEFAULT", OracleDbType.NVarchar2),
                                new OracleParameter("V_MYSERVICE", OracleDbType.NVarchar2),
                                new OracleParameter("P_RESULT",OracleDbType.RefCursor,ParameterDirection.Output),
            };
            parms[0].Value = V_CODE;
            parms[1].Value = V_NAME;
            parms[2].Value = V_FROM_DATE;
            parms[3].Value = V_TO_DATE;
            parms[4].Value = V_FROM_HOUR;

            parms[5].Value = V_TO_HOUR;
            parms[6].Value = V_RULE_ADD;
            parms[7].Value = V_PRIORITY;
            parms[8].Value = V_BALANCE_ID;
            parms[9].Value = V_EXPIRE_DATE_ID;
            parms[10].Value = V_STATUS;
            parms[11].Value = V_NOTE;
            parms[12].Value = V_USERS;
            parms[13].Value = V_NUMBERDIS;
            parms[14].Value = addType;
            parms[15].Value = title;
            parms[16].Value = isDefault;
            parms[17].Value = isMyservice;


            return DataAccess.getDataFromProcedure(str, "", parms);
        }



        public static DataSet camUpdate(string V_ID, string V_CODE, string V_NAME, string V_FROM_DATE,
            string V_TO_DATE, string V_FROM_HOUR, string V_TO_HOUR, string V_RULE_ADD,
             string V_PRIORITY, string V_BALANCE_ID, string V_EXPIRE_DATE_ID, string V_STATUS, string V_NOTE, string V_USERS, string V_TYPE,string DISPLAY,string addType,string title,string isDefault,string isMyService)
        {

            string str;
            str = "";
            str = "BALANCE_PKG.CAM_UPDATE";
            OracleParameter[] parms;
            parms = new OracleParameter[]
                            {
                                new OracleParameter("V_ID", OracleDbType.NVarchar2),

                                new OracleParameter("V_CODE", OracleDbType.NVarchar2),
                                new OracleParameter("V_NAME", OracleDbType.NVarchar2),
                                new OracleParameter("V_FROM_DATE", OracleDbType.NVarchar2),

                                new OracleParameter("V_TO_DATE", OracleDbType.NVarchar2),
                                new OracleParameter("V_FROM_HOUR", OracleDbType.NVarchar2),

                                new OracleParameter("V_TO_HOUR", OracleDbType.NVarchar2),
                                new OracleParameter("V_RULE_ADD", OracleDbType.NVarchar2),

                                new OracleParameter("V_PRIORITY", OracleDbType.NVarchar2),
                                new OracleParameter("V_BALANCE_ID", OracleDbType.NVarchar2),
                                new OracleParameter("V_EXPIRE_DATE_ID", OracleDbType.NVarchar2),
                                new OracleParameter("V_STATUS", OracleDbType.NVarchar2),
                                new OracleParameter("V_NOTE", OracleDbType.NVarchar2),


                                new OracleParameter("V_USERS", OracleDbType.NVarchar2),
                                new OracleParameter("V_TYPE", OracleDbType.NVarchar2),
                                new OracleParameter("V_DISPLAY", OracleDbType.NVarchar2),
                                new OracleParameter("V_ADD_TYPE", OracleDbType.NVarchar2),
                                new OracleParameter("V_TITLE", OracleDbType.NVarchar2),
                                new OracleParameter("V_DEFAULT", OracleDbType.NVarchar2),
                                new OracleParameter("V_MYSERVICE", OracleDbType.NVarchar2),
                                new OracleParameter("P_RESULT",OracleDbType.RefCursor,ParameterDirection.Output),
            };
            parms[0].Value = V_ID;
            parms[1].Value = V_CODE;
            parms[2].Value = V_NAME;
            parms[3].Value = V_FROM_DATE;
            parms[4].Value = V_TO_DATE;
            parms[5].Value = V_FROM_HOUR;

            parms[6].Value = V_TO_HOUR;
            parms[7].Value = V_RULE_ADD;
            parms[8].Value = V_PRIORITY;
            parms[9].Value = V_BALANCE_ID;
            parms[10].Value = V_EXPIRE_DATE_ID;
            parms[11].Value = V_STATUS;
            parms[12].Value = V_NOTE;

            parms[13].Value = V_USERS;
            parms[14].Value = V_TYPE;
            parms[15].Value = DISPLAY;
            parms[16].Value = addType;
            parms[17].Value = title;
            parms[18].Value = isDefault;
            parms[19].Value = isMyService;

            return DataAccess.getDataFromProcedure(str, "", parms);
        }



        public static DataSet camCopy(string id)
        {

            string str;
            str = "";
            str = "BALANCE_PKG.CAM_COPPY";
            OracleParameter[] parms;
            parms = new OracleParameter[]
                            {

                                new OracleParameter("v_id", OracleDbType.NVarchar2),
                               
                                new OracleParameter("P_RESULT",OracleDbType.RefCursor,ParameterDirection.Output),
            };
            parms[0].Value = id;
            

            return DataAccess.getDataFromProcedure(str, "", parms);
        }


        public static DataSet camBalGetList(string v_id, string v_users, string v_campaignId, string v_fromDate, string v_toDate
            , string v_order, string v_rowsOnPage, string v_seqPage,string  channel,string language)
        {

            string str;
            str = "";
            str = "BALANCE_PKG.CAM_BAL_GET";
            OracleParameter[] parms;
            parms = new OracleParameter[]
                            {

                                new OracleParameter("v_id", OracleDbType.NVarchar2),
                                new OracleParameter("v_users", OracleDbType.NVarchar2),

                                new OracleParameter("v_campaignId", OracleDbType.NVarchar2),

                                new OracleParameter("v_fromDate", OracleDbType.NVarchar2),
                                new OracleParameter("v_toDate", OracleDbType.NVarchar2),

                                new OracleParameter("v_order", OracleDbType.NVarchar2),
                                new OracleParameter("v_rowsOnPage", OracleDbType.NVarchar2),
                                new OracleParameter("v_seqPage", OracleDbType.NVarchar2),

                                new OracleParameter("v_channel", OracleDbType.Varchar2),
                                new OracleParameter("v_language", OracleDbType.Varchar2),

                                new OracleParameter("P_RESULT",OracleDbType.RefCursor,ParameterDirection.Output),
            };
            parms[0].Value = v_id;
            parms[1].Value = v_users;

            parms[2].Value = v_campaignId;

            parms[3].Value = v_fromDate;
            parms[4].Value = v_toDate;
            parms[5].Value = v_order;
            parms[6].Value = v_rowsOnPage;
            parms[7].Value = v_seqPage;
            parms[8].Value = channel;
            parms[9].Value = language;

            return DataAccess.getDataFromProcedure(str, "", parms);
        }


        public static DataSet camBalInsert(string V_CAMPAING_ID, string V_REF_ID, string V_NOTE, string V_USERS, string v_channel
            , string v_language)
        {

            string str;
            str = "";
            str = "BALANCE_PKG.CAM_BAL_INSERT";
            OracleParameter[] parms;
            parms = new OracleParameter[]
                            {

                                new OracleParameter("V_CAMPAING_ID", OracleDbType.NVarchar2),
                                new OracleParameter("V_REF_ID", OracleDbType.NVarchar2),

                                new OracleParameter("V_NOTE", OracleDbType.NVarchar2),

                                new OracleParameter("V_USERS", OracleDbType.NVarchar2),
                                new OracleParameter("v_channel", OracleDbType.NVarchar2),

                                new OracleParameter("v_language", OracleDbType.NVarchar2),
                                
                                new OracleParameter("P_RESULT",OracleDbType.RefCursor,ParameterDirection.Output),
            };
            parms[0].Value = V_CAMPAING_ID;
            parms[1].Value = V_REF_ID;

            parms[2].Value = V_NOTE;

            parms[3].Value = V_USERS;
            parms[4].Value = v_channel;
            parms[5].Value = v_language;
            

            return DataAccess.getDataFromProcedure(str, "", parms);
        }


        public static DataSet camBalUpdate(string V_CAMPAING_ID, string V_REF_ID, string V_NOTE, string V_USERS, string v_channel
            , string v_language,string id,string type)
        {

            string str;
            str = "";
            str = "BALANCE_PKG.CAM_BAL_UPDATE";
            OracleParameter[] parms;
            parms = new OracleParameter[]
                            {

                                new OracleParameter("V_CAMPAING_ID", OracleDbType.NVarchar2),
                                new OracleParameter("V_REF_ID", OracleDbType.NVarchar2),

                                new OracleParameter("V_NOTE", OracleDbType.NVarchar2),

                                new OracleParameter("V_USERS", OracleDbType.NVarchar2),
                                new OracleParameter("v_channel", OracleDbType.NVarchar2),

                                new OracleParameter("v_language", OracleDbType.NVarchar2),

                                new OracleParameter("V_ID", OracleDbType.NVarchar2),
                                new OracleParameter("V_TYPE", OracleDbType.NVarchar2),

                                new OracleParameter("P_RESULT",OracleDbType.RefCursor,ParameterDirection.Output),
            };
            parms[0].Value = V_CAMPAING_ID;
            parms[1].Value = V_REF_ID;

            parms[2].Value = V_NOTE;

            parms[3].Value = V_USERS;
            parms[4].Value = v_channel;
            parms[5].Value = v_language;
            parms[6].Value = id;
            parms[7].Value = type;

            return DataAccess.getDataFromProcedure(str, "", parms);
        }






        public static DataSet camSubGetList(string v_id, string v_users, string v_campaignId, string v_fromDate, string v_toDate
            , string v_order, string v_rowsOnPage, string v_seqPage, string channel, string language)
        {

            string str;
            str = "";
            str = "BALANCE_PKG.CAM_SUB_GET";
            OracleParameter[] parms;
            parms = new OracleParameter[]
                            {

                                new OracleParameter("v_id", OracleDbType.NVarchar2),
                                new OracleParameter("v_users", OracleDbType.NVarchar2),

                                new OracleParameter("v_campaignId", OracleDbType.NVarchar2),

                                new OracleParameter("v_fromDate", OracleDbType.NVarchar2),
                                new OracleParameter("v_toDate", OracleDbType.NVarchar2),

                                new OracleParameter("v_order", OracleDbType.NVarchar2),
                                new OracleParameter("v_rowsOnPage", OracleDbType.NVarchar2),
                                new OracleParameter("v_seqPage", OracleDbType.NVarchar2),

                                new OracleParameter("v_channel", OracleDbType.Varchar2),
                                new OracleParameter("v_language", OracleDbType.Varchar2),

                                new OracleParameter("P_RESULT",OracleDbType.RefCursor,ParameterDirection.Output),
            };
            parms[0].Value = v_id;
            parms[1].Value = v_users;

            parms[2].Value = v_campaignId;

            parms[3].Value = v_fromDate;
            parms[4].Value = v_toDate;
            parms[5].Value = v_order;
            parms[6].Value = v_rowsOnPage;
            parms[7].Value = v_seqPage;
            parms[8].Value = channel;
            parms[9].Value = language;

            return DataAccess.getDataFromProcedure(str, "", parms);
        }


        public static DataSet camSubInsert(string V_CAMPAING_ID, string V_REF_ID, string V_NOTE, string V_USERS, string v_channel
            , string v_language)
        {

            string str;
            str = "";
            str = "BALANCE_PKG.CAM_SUB_INSERT";
            OracleParameter[] parms;
            parms = new OracleParameter[]
                            {

                                new OracleParameter("V_CAMPAING_ID", OracleDbType.NVarchar2),
                                new OracleParameter("V_REF_ID", OracleDbType.NVarchar2),

                                new OracleParameter("V_NOTE", OracleDbType.NVarchar2),

                                new OracleParameter("V_USERS", OracleDbType.NVarchar2),
                                new OracleParameter("v_channel", OracleDbType.NVarchar2),

                                new OracleParameter("v_language", OracleDbType.NVarchar2),

                                new OracleParameter("P_RESULT",OracleDbType.RefCursor,ParameterDirection.Output),
            };
            parms[0].Value = V_CAMPAING_ID;
            parms[1].Value = V_REF_ID;

            parms[2].Value = V_NOTE;

            parms[3].Value = V_USERS;
            parms[4].Value = v_channel;
            parms[5].Value = v_language;


            return DataAccess.getDataFromProcedure(str, "", parms);
        }


        public static DataSet camSubUpdate(string V_CAMPAING_ID, string V_REF_ID, string V_NOTE, string V_USERS, string v_channel
            , string v_language, string id, string type)
        {

            string str;
            str = "";
            str = "BALANCE_PKG.CAM_SUB_UPDATE";
            OracleParameter[] parms;
            parms = new OracleParameter[]
                            {

                                new OracleParameter("V_CAMPAING_ID", OracleDbType.NVarchar2),
                                new OracleParameter("V_REF_ID", OracleDbType.NVarchar2),

                                new OracleParameter("V_NOTE", OracleDbType.NVarchar2),

                                new OracleParameter("V_USERS", OracleDbType.NVarchar2),
                                new OracleParameter("v_channel", OracleDbType.NVarchar2),

                                new OracleParameter("v_language", OracleDbType.NVarchar2),

                                new OracleParameter("V_ID", OracleDbType.NVarchar2),
                                new OracleParameter("V_TYPE", OracleDbType.NVarchar2),

                                new OracleParameter("P_RESULT",OracleDbType.RefCursor,ParameterDirection.Output),
            };
            parms[0].Value = V_CAMPAING_ID;
            parms[1].Value = V_REF_ID;

            parms[2].Value = V_NOTE;

            parms[3].Value = V_USERS;
            parms[4].Value = v_channel;
            parms[5].Value = v_language;
            parms[6].Value = id;
            parms[7].Value = type;

            return DataAccess.getDataFromProcedure(str, "", parms);
        }



        public static DataSet camExpGetList(string v_id, string v_users, string v_campaignId, string v_fromDate, string v_toDate
            , string v_order, string v_rowsOnPage, string v_seqPage, string channel, string language)
        {

            string str;
            str = "";
            str = "BALANCE_PKG.CAM_EXP_GET";
            OracleParameter[] parms;
            parms = new OracleParameter[]
                            {

                                new OracleParameter("v_id", OracleDbType.NVarchar2),
                                new OracleParameter("v_users", OracleDbType.NVarchar2),

                                new OracleParameter("v_campaignId", OracleDbType.NVarchar2),

                                new OracleParameter("v_fromDate", OracleDbType.NVarchar2),
                                new OracleParameter("v_toDate", OracleDbType.NVarchar2),

                                new OracleParameter("v_order", OracleDbType.NVarchar2),
                                new OracleParameter("v_rowsOnPage", OracleDbType.NVarchar2),
                                new OracleParameter("v_seqPage", OracleDbType.NVarchar2),

                                new OracleParameter("v_channel", OracleDbType.Varchar2),
                                new OracleParameter("v_language", OracleDbType.Varchar2),

                                new OracleParameter("P_RESULT",OracleDbType.RefCursor,ParameterDirection.Output),
            };
            parms[0].Value = v_id;
            parms[1].Value = v_users;

            parms[2].Value = v_campaignId;

            parms[3].Value = v_fromDate;
            parms[4].Value = v_toDate;
            parms[5].Value = v_order;
            parms[6].Value = v_rowsOnPage;
            parms[7].Value = v_seqPage;
            parms[8].Value = channel;
            parms[9].Value = language;

            return DataAccess.getDataFromProcedure(str, "", parms);
        }


        public static DataSet camExpInsert(string V_CAMPAING_ID, string V_REF_ID, string V_NOTE, string V_USERS, string v_channel
            , string v_language)
        {

            string str;
            str = "";
            str = "BALANCE_PKG.CAM_EXP_INSERT";
            OracleParameter[] parms;
            parms = new OracleParameter[]
                            {

                                new OracleParameter("V_CAMPAING_ID", OracleDbType.NVarchar2),
                                new OracleParameter("V_REF_ID", OracleDbType.NVarchar2),

                                new OracleParameter("V_NOTE", OracleDbType.NVarchar2),

                                new OracleParameter("V_USERS", OracleDbType.NVarchar2),
                                new OracleParameter("v_channel", OracleDbType.NVarchar2),

                                new OracleParameter("v_language", OracleDbType.NVarchar2),

                                new OracleParameter("P_RESULT",OracleDbType.RefCursor,ParameterDirection.Output),
            };
            parms[0].Value = V_CAMPAING_ID;
            parms[1].Value = V_REF_ID;

            parms[2].Value = V_NOTE;

            parms[3].Value = V_USERS;
            parms[4].Value = v_channel;
            parms[5].Value = v_language;


            return DataAccess.getDataFromProcedure(str, "", parms);
        }


        public static DataSet camExpUpdate(string V_CAMPAING_ID, string V_REF_ID, string V_NOTE, string V_USERS, string v_channel
            , string v_language, string id, string type)
        {

            string str;
            str = "";
            str = "BALANCE_PKG.CAM_EXP_UPDATE";
            OracleParameter[] parms;
            parms = new OracleParameter[]
                            {

                                new OracleParameter("V_CAMPAING_ID", OracleDbType.NVarchar2),
                                new OracleParameter("V_REF_ID", OracleDbType.NVarchar2),

                                new OracleParameter("V_NOTE", OracleDbType.NVarchar2),

                                new OracleParameter("V_USERS", OracleDbType.NVarchar2),
                                new OracleParameter("v_channel", OracleDbType.NVarchar2),

                                new OracleParameter("v_language", OracleDbType.NVarchar2),

                                new OracleParameter("V_ID", OracleDbType.NVarchar2),
                                new OracleParameter("V_TYPE", OracleDbType.NVarchar2),

                                new OracleParameter("P_RESULT",OracleDbType.RefCursor,ParameterDirection.Output),
            };
            parms[0].Value = V_CAMPAING_ID;
            parms[1].Value = V_REF_ID;

            parms[2].Value = V_NOTE;

            parms[3].Value = V_USERS;
            parms[4].Value = v_channel;
            parms[5].Value = v_language;
            parms[6].Value = id;
            parms[7].Value = type;

            return DataAccess.getDataFromProcedure(str, "", parms);
        }









        public static DataSet camScGetList(string v_id, string v_users, string v_campaignId, string v_fromDate, string v_toDate
            , string v_order, string v_rowsOnPage, string v_seqPage, string channel, string language)
        {

            string str;
            str = "";
            str = "BALANCE_PKG.CAM_SC_GET";
            OracleParameter[] parms;
            parms = new OracleParameter[]
                            {

                                new OracleParameter("v_id", OracleDbType.NVarchar2),
                                new OracleParameter("v_users", OracleDbType.NVarchar2),

                                new OracleParameter("v_campaignId", OracleDbType.NVarchar2),

                                new OracleParameter("v_fromDate", OracleDbType.NVarchar2),
                                new OracleParameter("v_toDate", OracleDbType.NVarchar2),

                                new OracleParameter("v_order", OracleDbType.NVarchar2),
                                new OracleParameter("v_rowsOnPage", OracleDbType.NVarchar2),
                                new OracleParameter("v_seqPage", OracleDbType.NVarchar2),

                                new OracleParameter("v_channel", OracleDbType.Varchar2),
                                new OracleParameter("v_language", OracleDbType.Varchar2),

                                new OracleParameter("P_RESULT",OracleDbType.RefCursor,ParameterDirection.Output),
            };
            parms[0].Value = v_id;
            parms[1].Value = v_users;

            parms[2].Value = v_campaignId;

            parms[3].Value = v_fromDate;
            parms[4].Value = v_toDate;
            parms[5].Value = v_order;
            parms[6].Value = v_rowsOnPage;
            parms[7].Value = v_seqPage;
            parms[8].Value = channel;
            parms[9].Value = language;

            return DataAccess.getDataFromProcedure(str, "", parms);
        }


        public static DataSet camScInsert(string V_CAMPAING_ID, string V_REF_ID, string V_NOTE, string V_USERS, string v_channel
            , string v_language)
        {

            string str;
            str = "";
            str = "BALANCE_PKG.CAM_SC_INSERT";
            OracleParameter[] parms;
            parms = new OracleParameter[]
                            {

                                new OracleParameter("V_CAMPAING_ID", OracleDbType.NVarchar2),
                                new OracleParameter("V_REF_ID", OracleDbType.NVarchar2),

                                new OracleParameter("V_NOTE", OracleDbType.NVarchar2),

                                new OracleParameter("V_USERS", OracleDbType.NVarchar2),
                                new OracleParameter("v_channel", OracleDbType.NVarchar2),

                                new OracleParameter("v_language", OracleDbType.NVarchar2),

                                new OracleParameter("P_RESULT",OracleDbType.RefCursor,ParameterDirection.Output),
            };
            parms[0].Value = V_CAMPAING_ID;
            parms[1].Value = V_REF_ID;

            parms[2].Value = V_NOTE;

            parms[3].Value = V_USERS;
            parms[4].Value = v_channel;
            parms[5].Value = v_language;


            return DataAccess.getDataFromProcedure(str, "", parms);
        }


        public static DataSet camScUpdate(string V_CAMPAING_ID, string V_REF_ID, string V_NOTE, string V_USERS, string v_channel
            , string v_language, string id, string type)
        {

            string str;
            str = "";
            str = "BALANCE_PKG.CAM_SC_UPDATE";
            OracleParameter[] parms;
            parms = new OracleParameter[]
                            {

                                new OracleParameter("V_CAMPAING_ID", OracleDbType.NVarchar2),
                                new OracleParameter("V_REF_ID", OracleDbType.NVarchar2),

                                new OracleParameter("V_NOTE", OracleDbType.NVarchar2),

                                new OracleParameter("V_USERS", OracleDbType.NVarchar2),
                                new OracleParameter("v_channel", OracleDbType.NVarchar2),

                                new OracleParameter("v_language", OracleDbType.NVarchar2),

                                new OracleParameter("V_ID", OracleDbType.NVarchar2),
                                new OracleParameter("V_TYPE", OracleDbType.NVarchar2),

                                new OracleParameter("P_RESULT",OracleDbType.RefCursor,ParameterDirection.Output),
            };
            parms[0].Value = V_CAMPAING_ID;
            parms[1].Value = V_REF_ID;

            parms[2].Value = V_NOTE;

            parms[3].Value = V_USERS;
            parms[4].Value = v_channel;
            parms[5].Value = v_language;
            parms[6].Value = id;
            parms[7].Value = type;

            return DataAccess.getDataFromProcedure(str, "", parms);
        }








        public static DataSet camAddGetList(string v_id, string v_users, string v_campaignId, string v_fromDate, string v_toDate
            , string v_order, string v_rowsOnPage, string v_seqPage)
        {

            string str;
            str = "";
            str = "BALANCE_PKG.CAMADD_GET";
            OracleParameter[] parms;
            parms = new OracleParameter[]
                            {

                                new OracleParameter("v_id", OracleDbType.NVarchar2),
                                new OracleParameter("v_users", OracleDbType.NVarchar2),

                                new OracleParameter("v_campaignId", OracleDbType.NVarchar2),
                                
                                new OracleParameter("v_fromDate", OracleDbType.NVarchar2),
                                new OracleParameter("v_toDate", OracleDbType.NVarchar2),

                                new OracleParameter("v_order", OracleDbType.NVarchar2),
                                new OracleParameter("v_rowsOnPage", OracleDbType.NVarchar2),
                                new OracleParameter("v_seqPage", OracleDbType.NVarchar2),

                                new OracleParameter("P_RESULT",OracleDbType.RefCursor,ParameterDirection.Output),
            };
            parms[0].Value = v_id;
            parms[1].Value = v_users;

            parms[2].Value = v_campaignId;
          
            parms[3].Value = v_fromDate;
            parms[4].Value = v_toDate;
            parms[5].Value = v_order;
            parms[6].Value = v_rowsOnPage;
            parms[7].Value = v_seqPage;

            return DataAccess.getDataFromProcedure(str, "", parms);
        }


        public static DataSet camAddInsert(string V_CAMPAING_ID, string V_SERVICE_ADD_ID, string V_NOTE, string V_USERS,
            string ussdDisplay,string keyRegister,string msgConfirm)
        {

            string str;
            str = "";
            str = "BALANCE_PKG.CAMADD_INSERT";
            OracleParameter[] parms;
            parms = new OracleParameter[]
                            {

                                new OracleParameter("V_CAMPAING_ID", OracleDbType.NVarchar2),
                                new OracleParameter("V_SERVICE_ADD_ID", OracleDbType.NVarchar2),
                             
                                new OracleParameter("V_NOTE", OracleDbType.NVarchar2),

                                new OracleParameter("V_USERS", OracleDbType.NVarchar2),
                                new OracleParameter("V_USSD_DIS", OracleDbType.NVarchar2),
                                new OracleParameter("V_KEY_REGIS", OracleDbType.NVarchar2),
                                new OracleParameter("V_MSG_CONFIRM", OracleDbType.NVarchar2),
                                
                                new OracleParameter("P_RESULT",OracleDbType.RefCursor,ParameterDirection.Output),
            };
            parms[0].Value = V_CAMPAING_ID;
            parms[1].Value = V_SERVICE_ADD_ID;
            parms[2].Value = V_NOTE;
            parms[3].Value = V_USERS;
            parms[4].Value = ussdDisplay;
            parms[5].Value = keyRegister;
            parms[6].Value = msgConfirm;

          

            return DataAccess.getDataFromProcedure(str, "", parms);
        }



        public static DataSet camAddUpdate(string V_ID, string V_CAMPAING_ID, string V_SERVICE_ADD_ID, string V_NOTE, 
            string V_USERS, string V_TYPE,string ussdDisplay,string keyRegister, string msgConfirm)
        {

            string str;
            str = "";
            str = "BALANCE_PKG.CAMADD_UPDATE";
            OracleParameter[] parms;
            parms = new OracleParameter[]
                            {
                                new OracleParameter("V_ID", OracleDbType.NVarchar2),

                                new OracleParameter("V_CAMPAING_ID", OracleDbType.NVarchar2),
                                new OracleParameter("V_SERVICE_ADD_ID", OracleDbType.NVarchar2),

                                new OracleParameter("V_NOTE", OracleDbType.NVarchar2),


                                new OracleParameter("V_USERS", OracleDbType.NVarchar2),
                                new OracleParameter("V_TYPE", OracleDbType.NVarchar2),
                                new OracleParameter("V_USSD_DIS", OracleDbType.NVarchar2),
                                new OracleParameter("V_KEY_REGIS", OracleDbType.NVarchar2),
                                new OracleParameter("V_MSG_CONFIRM", OracleDbType.NVarchar2),


                                new OracleParameter("P_RESULT",OracleDbType.RefCursor,ParameterDirection.Output),
            };
            parms[0].Value = V_ID;
            parms[1].Value = V_CAMPAING_ID;
            parms[2].Value = V_SERVICE_ADD_ID;
            
            parms[3].Value = V_NOTE;

            parms[4].Value = V_USERS;
            parms[5].Value = V_TYPE;
            parms[6].Value = ussdDisplay;
            parms[7].Value = keyRegister;
            parms[8].Value = msgConfirm;


            return DataAccess.getDataFromProcedure(str, "", parms);
        }

        public static DataSet ADMIN_USERS_FUNCTION(string role)
        {
            // {"msisdn":"50940227941","users":"123","pass":"123","serviceId":"23","type":"DK","channel":"WEB"}
            string str;
            str = "";
            str = "BALANCE_PKG.ADMIN_GET_ROLE";
            OracleParameter[] parms;
            parms = new OracleParameter[]
                            {

                                new OracleParameter("v_role", OracleDbType.NVarchar2),

                                new OracleParameter("P_RESULT",OracleDbType.RefCursor,ParameterDirection.Output),
            };
            parms[0].Value = role;


            return DataAccess.getDataFromProcedure(str, "", parms);
        }

        public static DataSet ADMIN_USERS_INFO(string v_users, string v_pass)
        {
            // {"msisdn":"50940227941","users":"123","pass":"123","serviceId":"23","type":"DK","channel":"WEB"}
            string str;
            str = "";
            str = "BALANCE_PKG.ADMIN_LOGIN";
            OracleParameter[] parms;
            parms = new OracleParameter[]
                            {

                                new OracleParameter("v_user", OracleDbType.NVarchar2),
                                new OracleParameter("v_pass", OracleDbType.NVarchar2),

                                new OracleParameter("P_RESULT",OracleDbType.RefCursor,ParameterDirection.Output),
            };
            parms[0].Value = v_users;
            parms[1].Value = v_pass;

            return DataAccess.getDataFromProcedure(str, "", parms);
        }






        public static DataSet ngamGetList(string v_id, string v_users)
        {

            string str;
            str = "";
            str = "BALANCE_PKG.TMP_GET";
            OracleParameter[] parms;
            parms = new OracleParameter[]
                            {

                                new OracleParameter("v_id", OracleDbType.NVarchar2),
                                new OracleParameter("v_users", OracleDbType.NVarchar2),

                                new OracleParameter("P_RESULT",OracleDbType.RefCursor,ParameterDirection.Output),
            };
            parms[0].Value = v_id;
            parms[1].Value = v_users;

            return DataAccess.getDataFromProcedure(str, "", parms);
        }


        public static DataSet ngamInsert(string V_NAME, string V_SV, string V_MSG,
            string V_FD, string V_TD, string V_PC, string V_ADD_TYPE, string V_USERS)
        {

            string str;
            str = "";
            str = "BALANCE_PKG.TMP_INSERT";
            OracleParameter[] parms;
            parms = new OracleParameter[]
                            {

                                new OracleParameter("V_NAME", OracleDbType.NVarchar2),
                                new OracleParameter("V_SV", OracleDbType.NVarchar2),
                                new OracleParameter("V_MSG", OracleDbType.NVarchar2),

                                new OracleParameter("V_FD", OracleDbType.NVarchar2),
                                new OracleParameter("V_TD", OracleDbType.NVarchar2),

                                new OracleParameter("V_PC", OracleDbType.NVarchar2),
                                new OracleParameter("V_ADD_TYPE", OracleDbType.NVarchar2),
                                
                                new OracleParameter("V_USERS", OracleDbType.NVarchar2),

                                new OracleParameter("P_RESULT",OracleDbType.RefCursor,ParameterDirection.Output),
            };
            parms[0].Value = V_NAME;
            parms[1].Value = V_SV;
            parms[2].Value = V_MSG;
            parms[3].Value = V_FD;
            parms[4].Value = V_TD;
            parms[5].Value = V_PC;
            parms[6].Value = V_ADD_TYPE;
            parms[7].Value = V_USERS;
           

            return DataAccess.getDataFromProcedure(str, "", parms);
        }



        public static DataSet ngamUpdate(string V_ID, string V_NAME, string V_SV, string V_MSG,
            string V_FD, string V_TD, string V_PC, string V_ADD_TYPE, string V_USERS, string V_TYPE)
        {

            string str;
            str = "";
            str = "BALANCE_PKG.TMP_UPDATE";
            OracleParameter[] parms;
            parms = new OracleParameter[]
                            {
                                new OracleParameter("V_ID", OracleDbType.NVarchar2),
                                 new OracleParameter("V_NAME", OracleDbType.NVarchar2),
                                new OracleParameter("V_SV", OracleDbType.NVarchar2),
                                new OracleParameter("V_MSG", OracleDbType.NVarchar2),

                                new OracleParameter("V_FD", OracleDbType.NVarchar2),
                                new OracleParameter("V_TD", OracleDbType.NVarchar2),

                                new OracleParameter("V_PC", OracleDbType.NVarchar2),
                                new OracleParameter("V_ADD_TYPE", OracleDbType.NVarchar2),
                                new OracleParameter("V_USERS", OracleDbType.NVarchar2),
                                new OracleParameter("V_TYPE", OracleDbType.NVarchar2),

                                new OracleParameter("P_RESULT",OracleDbType.RefCursor,ParameterDirection.Output),
            };
            parms[0].Value = V_ID;
            parms[1].Value = V_NAME;
            parms[2].Value = V_SV;
            parms[3].Value = V_MSG;
            parms[4].Value = V_FD;
            parms[5].Value = V_TD;
            parms[6].Value = V_PC;
            parms[7].Value = V_ADD_TYPE;
            parms[8].Value = V_USERS;
            parms[9].Value = V_TYPE;



            return DataAccess.getDataFromProcedure(str, "", parms);
        }




        public static DataSet blackGettList(string msisdn,string type,string  svid, string v_users)
        {

            string str;
            str = "";
            str = "BALANCE_PKG.BLACK_GET";
            OracleParameter[] parms;
            parms = new OracleParameter[]
                            {

                                new OracleParameter("v_msisdn", OracleDbType.NVarchar2),
                                new OracleParameter("v_type", OracleDbType.NVarchar2),
                                new OracleParameter("v_sv_id", OracleDbType.NVarchar2),
                                new OracleParameter("v_users", OracleDbType.NVarchar2),

                                new OracleParameter("P_RESULT",OracleDbType.RefCursor,ParameterDirection.Output),
            };
            parms[0].Value = msisdn;
            parms[1].Value = type;
            parms[2].Value = svid;
            parms[3].Value = v_users;


            return DataAccess.getDataFromProcedure(str, "", parms);
        }






        public static DataSet lkpw_result_update(string v_randomId, string v_users, string v_result, string v_status, string v_note)
        {

            string str;
            str = "";
            str = "LOTO_CMS_PKG.lkpw_result_update";
            OracleParameter[] parms;
            parms = new OracleParameter[]
                            {

                                new OracleParameter("v_randomId", OracleDbType.NVarchar2),
                                new OracleParameter("v_users", OracleDbType.NVarchar2),
                                new OracleParameter("v_result", OracleDbType.NVarchar2),
                                new OracleParameter("v_status", OracleDbType.NVarchar2),
                                new OracleParameter("v_note", OracleDbType.NVarchar2),
                                
                                new OracleParameter("P_RESULT",OracleDbType.RefCursor,ParameterDirection.Output),
            };
            parms[0].Value = v_randomId;
            parms[1].Value = v_users;
            parms[2].Value = v_result;
            parms[3].Value = v_status;
            parms[4].Value = v_note;
           

            return DataAccess.getDataFromProcedure(str, "", parms);
        }








        public static void InsertBulkExport(string sSQL, List<List<string>> dataColumn)
        {
            OracleConnection connection = null;
            try
            {
                connection =DataAccess.getPoolingConnection();
                connection.Open();
                OracleTransaction transaction;

                // Start a local transaction
                transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);
                using (var command = connection.CreateCommand())
                {
                    command.CommandText = sSQL;
                    command.CommandType = CommandType.StoredProcedure;
                    command.ArrayBindCount = dataColumn[0].Count;
                    command.Transaction = transaction;




                    var v_export_id = dataColumn[0].ToArray();
                    OracleParameter param0 = new OracleParameter("v_export_id", OracleDbType.NVarchar2, ParameterDirection.Input);
                    param0.Value = v_export_id;
                    param0.Size = v_export_id.Length;
                    command.Parameters.Add(param0);

                    var v_winner_id = dataColumn[1].ToArray();
                    OracleParameter param1 = new OracleParameter("v_winner_id", OracleDbType.NVarchar2, ParameterDirection.Input);
                    param1.Value = v_winner_id;
                    param1.Size = v_winner_id.Length;
                    command.Parameters.Add(param1);

                    var v_msisdn = dataColumn[2].ToArray();
                    OracleParameter param2 = new OracleParameter("v_msisdn", OracleDbType.NVarchar2, ParameterDirection.Input);
                    param2.Value = v_msisdn;
                    param2.Size = v_msisdn.Length;
                    command.Parameters.Add(param2);

                    var v_money = dataColumn[3].ToArray();
                    OracleParameter param3 = new OracleParameter("v_money", OracleDbType.NVarchar2, ParameterDirection.Input);
                    param3.Value = v_money;
                    param3.Size = v_money.Length;
                    command.Parameters.Add(param3);


                    var v_random_id = dataColumn[4].ToArray();
                    OracleParameter param4 = new OracleParameter("v_random_id", OracleDbType.NVarchar2, ParameterDirection.Input);
                    param4.Value = v_random_id;
                    param4.Size = v_random_id.Length;
                    command.Parameters.Add(param4);

                    var v_bet_id = dataColumn[5].ToArray();
                    OracleParameter param5 = new OracleParameter("v_bet_id", OracleDbType.NVarchar2, ParameterDirection.Input);
                    param5.Value = v_bet_id;
                    param5.Size = v_bet_id.Length;
                    command.Parameters.Add(param5);


                    var v_winner_code = dataColumn[6].ToArray();
                    OracleParameter param6 = new OracleParameter("v_winner_code", OracleDbType.NVarchar2, ParameterDirection.Input);
                    param6.Value = v_winner_code;
                    param6.Size = v_winner_code.Length;
                    command.Parameters.Add(param6);




                    command.ExecuteNonQuery();



                    transaction.Commit();

                }

            }
            catch (OracleException ex)
            {
                Console.WriteLine(ex.ToString());
                throw;
            }
            finally
            {
                connection.Close();
            }
        }



        public static void InsertBulkBlack(string sSQL, List<List<string>> dataColumn)
        {
            OracleConnection connection = null;
            try
            {
                connection = DataAccess.getPoolingConnection();
                connection.Open();
                OracleTransaction transaction;

                // Start a local transaction
                transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);
                using (var command = connection.CreateCommand())
                {
                    command.CommandText = sSQL;
                    command.CommandType = CommandType.StoredProcedure;
                    command.ArrayBindCount = dataColumn[0].Count;
                    command.Transaction = transaction;




                    var v_users = dataColumn[0].ToArray();
                    OracleParameter param0 = new OracleParameter("v_users", OracleDbType.NVarchar2, ParameterDirection.Input);
                    param0.Value = v_users;
                    param0.Size = v_users.Length;
                    command.Parameters.Add(param0);

                    var v_msisdn = dataColumn[1].ToArray();
                    OracleParameter param1 = new OracleParameter("v_msisdn", OracleDbType.NVarchar2, ParameterDirection.Input);
                    param1.Value = v_msisdn;
                    param1.Size = v_msisdn.Length;
                    command.Parameters.Add(param1);

                    var v_isLock = dataColumn[2].ToArray();
                    OracleParameter param2 = new OracleParameter("v_isLock", OracleDbType.NVarchar2, ParameterDirection.Input);
                    param2.Value = v_isLock;
                    param2.Size = v_isLock.Length;
                    command.Parameters.Add(param2);


                    var v_note = dataColumn[3].ToArray();
                    OracleParameter param3 = new OracleParameter("v_note", OracleDbType.NVarchar2, ParameterDirection.Input);
                    param3.Value = v_note;
                    param3.Size = v_note.Length;
                    command.Parameters.Add(param3);


                    command.ExecuteNonQuery();



                    transaction.Commit();

                }

            }
            catch (OracleException ex)
            {
                Console.WriteLine(ex.ToString());
                throw;
            }
            finally
            {
                connection.Close();
            }
        }






        public static DataSet reload(  string V_TYPE, string V_USERS)
        {

            string str;
            str = "";
            str = "BALANCE_PKG.pro_get_update";
            OracleParameter[] parms;
            parms = new OracleParameter[]
                            {
                                new OracleParameter("v_prId", OracleDbType.NVarchar2),
                                new OracleParameter("v_user", OracleDbType.NVarchar2),


                                new OracleParameter("P_RESULT",OracleDbType.RefCursor,ParameterDirection.Output),
            };
            parms[0].Value = V_TYPE;
            parms[1].Value = V_USERS;
            



            return DataAccess.getDataFromProcedure(str, "", parms);
        }



        public static DataSet apiServiceLoad(string v_id, string v_users,string v_order, string v_rowsOnPage, string v_seqPage,string v_isactive)
        {
            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 WEBSERVICE WHERE 1=1";
                string dataSql = "SELECT WS_ID, WS_NAME, WS_CODE, WSDL, MSG_TEMPLATE, ERROR_TAG, SUCCESS_CODE, STATUS FROM WEBSERVICE WHERE 1=1";
                
                // Add filters
                if (v_id != null && v_id != "-1")
                {
                    countSql += " AND WS_ID = :v_id";
                    dataSql += " AND WS_ID = :v_id";
                }
                
                if (v_isactive != null && v_isactive != "-1")
                {
                    countSql += " AND STATUS = :v_isactive";
                    dataSql += " AND STATUS = :v_isactive";
                }
                
                // Add ordering
                dataSql += " ORDER BY WS_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_isactive != null && v_isactive != "-1")
                {
                    countCmd.Parameters.Add(":v_isactive", OracleDbType.NVarchar2).Value = v_isactive;
                }
                
                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_isactive != null && v_isactive != "-1")
                {
                    dataCmd.Parameters.Add(":v_isactive", OracleDbType.NVarchar2).Value = v_isactive;
                }
                
                OracleDataAdapter dataAdapter = new OracleDataAdapter(dataCmd);
                dataAdapter.Fill(ds);
                
                // Add pagination metadata to each row
                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("IS_ACTIVE", 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["IS_ACTIVE"] = row["STATUS"].ToString();
                }
            }
            catch (OracleException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbConnection.Close();
            }
            
            return ds;
        }



		public static DataSet apiServiceInsert(string ws_name, string ws_code, string wsdl, string msg_template, string error_tag, string success_code, string isActive, string users)
		{
			DataSet ds = new DataSet();
			DataTable tb = new DataTable();
			tb.Columns.Add("status", typeof(string));
			tb.Columns.Add("msg", typeof(string));
			OracleConnection dbConnection = DataAccess.getPoolingConnection();
			try
			{
				dbConnection.Open();
				string sql = @"INSERT INTO WEBSERVICE(WS_ID, WS_NAME, WS_CODE, WSDL, MSG_TEMPLATE, ERROR_TAG, SUCCESS_CODE, STATUS)
							 VALUES(WEBSERVICE_SEQ.NEXTVAL, :ws_name, :ws_code, :wsdl, :msg_template, :error_tag, :success_code, :status)";
				using (OracleCommand cmd = new OracleCommand(sql, dbConnection))
				{
					cmd.CommandType = CommandType.Text;
					cmd.Parameters.Add(":ws_name", OracleDbType.NVarchar2).Value = ws_name ?? "";
					cmd.Parameters.Add(":ws_code", OracleDbType.NVarchar2).Value = ws_code ?? "";
					cmd.Parameters.Add(":wsdl", OracleDbType.NVarchar2).Value = wsdl ?? "";
					cmd.Parameters.Add(":msg_template", OracleDbType.NVarchar2).Value = msg_template ?? "";
					cmd.Parameters.Add(":error_tag", OracleDbType.NVarchar2).Value = error_tag ?? "";
					cmd.Parameters.Add(":success_code", OracleDbType.NVarchar2).Value = success_code ?? "";
					cmd.Parameters.Add(":status", OracleDbType.Int32).Value = (isActive == "0" ? 0 : 1);
					int affected = cmd.ExecuteNonQuery();
					// get generated id in this session
					string newId = "";
					try
					{
						using (OracleCommand idCmd = new OracleCommand("SELECT WEBSERVICE_SEQ.CURRVAL FROM DUAL", dbConnection))
						{
							object val = idCmd.ExecuteScalar();
							newId = val == null ? "" : Convert.ToString(val);
						}
					}
					catch { }
					var row = tb.NewRow();
					row["status"] = affected > 0 ? "0" : "-1";
					row["msg"] = affected > 0 ? ("Success" + (newId!=""? ("|"+newId):"")) : "Insert failed";
					tb.Rows.Add(row);
				}
			}
			catch (Exception ex)
			{
				var row = tb.NewRow();
				row["status"] = "-1";
				row["msg"] = ex.Message;
				tb.Rows.Add(row);
			}
			finally
			{
				dbConnection.Close();
			}
			ds.Tables.Add(tb);
			return ds;
		}

		public static DataSet apiServiceUpdate(string id, string ws_name, string ws_code, string wsdl, string msg_template, string error_tag, string success_code, string isActive, string users)
		{
			DataSet ds = new DataSet();
			DataTable tb = new DataTable();
			tb.Columns.Add("status", typeof(string));
			tb.Columns.Add("msg", typeof(string));
			OracleConnection dbConnection = DataAccess.getPoolingConnection();
			try
			{
				dbConnection.Open();
				string sql = @"UPDATE WEBSERVICE
							SET WS_NAME = :ws_name,
								WS_CODE = :ws_code,
								WSDL = :wsdl,
								MSG_TEMPLATE = :msg_template,
								ERROR_TAG = :error_tag,
								SUCCESS_CODE = :success_code,
								STATUS = :status
							WHERE WS_ID = :id";
				using (OracleCommand cmd = new OracleCommand(sql, dbConnection))
				{
					cmd.CommandType = CommandType.Text;
					cmd.Parameters.Add(":ws_name", OracleDbType.NVarchar2).Value = ws_name ?? "";
					cmd.Parameters.Add(":ws_code", OracleDbType.NVarchar2).Value = ws_code ?? "";
					cmd.Parameters.Add(":wsdl", OracleDbType.NVarchar2).Value = wsdl ?? "";
					cmd.Parameters.Add(":msg_template", OracleDbType.NVarchar2).Value = msg_template ?? "";
					cmd.Parameters.Add(":error_tag", OracleDbType.NVarchar2).Value = error_tag ?? "";
					cmd.Parameters.Add(":success_code", OracleDbType.NVarchar2).Value = success_code ?? "";
					cmd.Parameters.Add(":status", OracleDbType.Int32).Value = (isActive == "0" ? 0 : 1);
					cmd.Parameters.Add(":id", OracleDbType.NVarchar2).Value = id ?? "";
					int affected = cmd.ExecuteNonQuery();
					var row = tb.NewRow();
					row["status"] = affected > 0 ? "0" : "-1";
					row["msg"] = affected > 0 ? "Success" : "Update failed";
					tb.Rows.Add(row);
				}
			}
			catch (Exception ex)
			{
				var row = tb.NewRow();
				row["status"] = "-1";
				row["msg"] = ex.Message;
				tb.Rows.Add(row);
			}
			finally
			{
				dbConnection.Close();
			}
			ds.Tables.Add(tb);
			return ds;
		}

		public static DataSet GET_HOURLY_IMPRESSIONS(string v_campaignId, string v_serviceId, string v_hours, string v_quickJump)
		{
			DataSet ds = new DataSet();
			OracleConnection dbConnection = DataAccess.getPoolingConnection();
			try
			{
				dbConnection.Open();
				
				// Calculate date range based on hours parameter (default 72 hours)
				int hours = 72;
				if (!string.IsNullOrEmpty(v_hours) && v_hours != "-1" && v_hours != "All")
				{
					hours = int.TryParse(v_hours.Replace("h", ""), out int h) ? h : 72;
				}
				
				DateTime endDate = DateTime.Now;
				DateTime startDate = endDate.AddHours(-hours);
				
				// If quickJump is provided, use it as the center point
				if (!string.IsNullOrEmpty(v_quickJump) && v_quickJump != "-1")
				{
					if (DateTime.TryParseExact(v_quickJump, "yyyy-MM-dd HH", null, System.Globalization.DateTimeStyles.None, out DateTime jumpDate))
					{
						startDate = jumpDate.AddHours(-hours / 2);
						endDate = jumpDate.AddHours(hours / 2);
					}
				}
				
				// Build WHERE clause
				string whereClause = "WHERE TRUNC(REPORT_DATE, 'HH') >= TRUNC(:startDate, 'HH') AND TRUNC(REPORT_DATE, 'HH') <= TRUNC(:endDate, 'HH')";
				
				if (!string.IsNullOrEmpty(v_campaignId) && v_campaignId != "-1" && v_campaignId != "All")
				{
					whereClause += " AND CAMPAIGN_ID = :campaignId";
				}
				
				if (!string.IsNullOrEmpty(v_serviceId) && v_serviceId != "-1" && v_serviceId != "All")
				{
					whereClause += " AND SERVICE_ID = :serviceId";
				}
				
				// Build SQL with subquery first, then LEFT JOIN
				// Always group by CAMPAIGN_ID to show each campaign separately, even when "All" is selected
				string sql = @"SELECT 
					base.HOUR_LABEL,
					base.HOUR_VALUE,
					base.CAMPAIGN_ID,
					c.NAME AS CAMPAIGN_NAME,
					base.COUNT_IMPRESSED
				FROM (
					SELECT 
						TO_CHAR(TRUNC(REPORT_DATE, 'HH'), 'YYYY-MM-DD HH24') AS HOUR_LABEL,
						TO_CHAR(TRUNC(REPORT_DATE, 'HH'), 'YYYY-MM-DD HH24:MI:SS') AS HOUR_VALUE,
						CAMPAIGN_ID,
						SUM(NVL(COUNT_SEND_1, 0)) AS COUNT_IMPRESSED
					FROM REPORT_COUNT_HOURLY
					" + whereClause + @"
					GROUP BY 
						TRUNC(REPORT_DATE, 'HH'),
						CAMPAIGN_ID
				) base
				LEFT JOIN B_CAMPAIGN c ON base.CAMPAIGN_ID = c.ID
				ORDER BY base.HOUR_VALUE, base.CAMPAIGN_ID";
				
				using (OracleCommand cmd = new OracleCommand(sql, dbConnection))
				{
					cmd.CommandType = CommandType.Text;
					cmd.Parameters.Add(":startDate", OracleDbType.Date).Value = startDate;
					cmd.Parameters.Add(":endDate", OracleDbType.Date).Value = endDate;
					
					if (!string.IsNullOrEmpty(v_campaignId) && v_campaignId != "-1" && v_campaignId != "All")
					{
						cmd.Parameters.Add(":campaignId", OracleDbType.NVarchar2).Value = v_campaignId;
					}
					
					if (!string.IsNullOrEmpty(v_serviceId) && v_serviceId != "-1" && v_serviceId != "All")
					{
						cmd.Parameters.Add(":serviceId", OracleDbType.NVarchar2).Value = v_serviceId;
					}
					
					OracleDataAdapter adapter = new OracleDataAdapter(cmd);
					adapter.Fill(ds);
				}
			}
			catch (Exception ex)
			{
				throw ex;
			}
			finally
			{
				dbConnection.Close();
			}
			
			return ds;
		}

		public static DataSet GET_DAILY_IMPRESSIONS(string v_campaignId, string v_fromDate, string v_toDate)
		{
			DataSet ds = new DataSet();
			OracleConnection dbConnection = DataAccess.getPoolingConnection();
			try
			{
				dbConnection.Open();
				
				// Build WHERE clause
				string whereClause = "WHERE 1=1";
				
				if (!string.IsNullOrEmpty(v_fromDate) && v_fromDate != "-1")
				{
					whereClause += " AND TRUNC(REPORT_DATE) >= TRUNC(TO_DATE(:fromDate, 'DD/MM/YYYY'))";
				}
				
				if (!string.IsNullOrEmpty(v_toDate) && v_toDate != "-1")
				{
					whereClause += " AND TRUNC(REPORT_DATE) <= TRUNC(TO_DATE(:toDate, 'DD/MM/YYYY'))";
				}
				
				if (!string.IsNullOrEmpty(v_campaignId) && v_campaignId != "-1" && v_campaignId != "All")
				{
					whereClause += " AND CAMPAIGN_ID = :campaignId";
				}
				
				// Build SQL with subquery first, then LEFT JOIN
				// Group by date and campaign to show each campaign separately
				// Using REPORT_COUNT_DAILY table with REPORT_DATE and COUNT_SEND_1
				string sql = @"SELECT 
					base.DAY_LABEL,
					base.DAY_VALUE,
					base.CAMPAIGN_ID,
					c.NAME AS CAMPAIGN_NAME,
					base.COUNT_IMPRESSED
				FROM (
					SELECT 
						TO_CHAR(TRUNC(REPORT_DATE), 'YYYY-MM-DD') AS DAY_LABEL,
						TRUNC(REPORT_DATE) AS DAY_VALUE,
						CAMPAIGN_ID,
						SUM(COUNT_SEND_1) AS COUNT_IMPRESSED
					FROM REPORT_COUNT_DAILY
					" + whereClause + @"
					GROUP BY 
						TRUNC(REPORT_DATE),
						CAMPAIGN_ID
				) base
				LEFT JOIN B_CAMPAIGN c ON base.CAMPAIGN_ID = c.ID
				ORDER BY base.DAY_VALUE, base.CAMPAIGN_ID";
				
				using (OracleCommand cmd = new OracleCommand(sql, dbConnection))
				{
					cmd.CommandType = CommandType.Text;
					
					if (!string.IsNullOrEmpty(v_fromDate) && v_fromDate != "-1")
					{
						cmd.Parameters.Add(":fromDate", OracleDbType.NVarchar2).Value = v_fromDate;
					}
					
					if (!string.IsNullOrEmpty(v_toDate) && v_toDate != "-1")
					{
						cmd.Parameters.Add(":toDate", OracleDbType.NVarchar2).Value = v_toDate;
					}
					
					if (!string.IsNullOrEmpty(v_campaignId) && v_campaignId != "-1" && v_campaignId != "All")
					{
						cmd.Parameters.Add(":campaignId", OracleDbType.NVarchar2).Value = v_campaignId;
					}
					
					OracleDataAdapter adapter = new OracleDataAdapter(cmd);
					adapter.Fill(ds);
				}
			}
			catch (Exception ex)
			{
				throw ex;
			}
			finally
			{
				dbConnection.Close();
			}
			
			return ds;
		}

		public static DataSet GET_DAILY_UNIQUE_IMPRESSIONS(string v_campaignId, string v_serviceId, string v_fromDate, string v_toDate)
		{
			DataSet ds = new DataSet();
			OracleConnection dbConnection = DataAccess.getPoolingConnection();
			try
			{
				dbConnection.Open();

				string whereClause = "WHERE 1=1";

				if (!string.IsNullOrEmpty(v_fromDate) && v_fromDate != "-1")
				{
					whereClause += " AND TRUNC(REPORT_DATE) >= TRUNC(TO_DATE(:fromDate, 'DD/MM/YYYY'))";
				}

				if (!string.IsNullOrEmpty(v_toDate) && v_toDate != "-1")
				{
					whereClause += " AND TRUNC(REPORT_DATE) <= TRUNC(TO_DATE(:toDate, 'DD/MM/YYYY'))";
				}

				if (!string.IsNullOrEmpty(v_campaignId) && v_campaignId != "-1" && v_campaignId != "All")
				{
					whereClause += " AND NVL(CAMPAIGN_ID, -1) = :campaignId";
				}

				if (!string.IsNullOrEmpty(v_serviceId) && v_serviceId != "-1" && v_serviceId != "All")
				{
					whereClause += " AND NVL(SERVICE_ID, -1) = :serviceId";
				}

				string sql = @"SELECT 
					base.DAY_LABEL,
					base.DAY_VALUE,
					base.CAMPAIGN_ID,
					c.NAME AS CAMPAIGN_NAME,
					base.COUNT_IMPRESSED
				FROM (
					SELECT 
						TO_CHAR(TRUNC(REPORT_DATE), 'YYYY-MM-DD') AS DAY_LABEL,
						TRUNC(REPORT_DATE) AS DAY_VALUE,
						NVL(CAMPAIGN_ID, -1) AS CAMPAIGN_ID,
						SUM(NVL(COUNT_IMPRESSED, 0)) AS COUNT_IMPRESSED
					FROM REPORT_USER_DAILY
					" + whereClause + @"
					GROUP BY 
						TRUNC(REPORT_DATE),
						NVL(CAMPAIGN_ID, -1)
				) base
				LEFT JOIN B_CAMPAIGN c ON base.CAMPAIGN_ID = c.ID
				ORDER BY base.DAY_VALUE, base.CAMPAIGN_ID";

				using (OracleCommand cmd = new OracleCommand(sql, dbConnection))
				{
					cmd.CommandType = CommandType.Text;

					if (!string.IsNullOrEmpty(v_fromDate) && v_fromDate != "-1")
					{
						cmd.Parameters.Add(":fromDate", OracleDbType.NVarchar2).Value = v_fromDate;
					}

					if (!string.IsNullOrEmpty(v_toDate) && v_toDate != "-1")
					{
						cmd.Parameters.Add(":toDate", OracleDbType.NVarchar2).Value = v_toDate;
					}

					if (!string.IsNullOrEmpty(v_campaignId) && v_campaignId != "-1" && v_campaignId != "All")
					{
						cmd.Parameters.Add(":campaignId", OracleDbType.NVarchar2).Value = v_campaignId;
					}

					if (!string.IsNullOrEmpty(v_serviceId) && v_serviceId != "-1" && v_serviceId != "All")
					{
						cmd.Parameters.Add(":serviceId", OracleDbType.NVarchar2).Value = v_serviceId;
					}

					OracleDataAdapter adapter = new OracleDataAdapter(cmd);
					adapter.Fill(ds);
				}
			}
			catch (Exception ex)
			{
				throw ex;
			}
			finally
			{
				dbConnection.Close();
			}

			return ds;
		}

		public static DataSet GET_DAILY_ENGAGED_USERS(string v_campaignId, string v_serviceId, string v_fromDate, string v_toDate)
		{
			DataSet ds = new DataSet();
			OracleConnection dbConnection = DataAccess.getPoolingConnection();
			try
			{
				dbConnection.Open();

				string whereClause = "WHERE 1=1";

				if (!string.IsNullOrEmpty(v_fromDate) && v_fromDate != "-1")
				{
					whereClause += " AND TRUNC(REPORT_DATE) >= TRUNC(TO_DATE(:fromDate, 'DD/MM/YYYY'))";
				}

				if (!string.IsNullOrEmpty(v_toDate) && v_toDate != "-1")
				{
					whereClause += " AND TRUNC(REPORT_DATE) <= TRUNC(TO_DATE(:toDate, 'DD/MM/YYYY'))";
				}

				if (!string.IsNullOrEmpty(v_campaignId) && v_campaignId != "-1" && v_campaignId != "All")
				{
					whereClause += " AND NVL(CAMPAIGN_ID, -1) = :campaignId";
				}

				if (!string.IsNullOrEmpty(v_serviceId) && v_serviceId != "-1" && v_serviceId != "All")
				{
					whereClause += " AND NVL(SERVICE_ID, -1) = :serviceId";
				}

				string sql = @"SELECT 
					base.DAY_LABEL,
					base.DAY_VALUE,
					base.CAMPAIGN_ID,
					c.NAME AS CAMPAIGN_NAME,
					base.COUNT_ENGAGED
				FROM (
					SELECT 
						TO_CHAR(TRUNC(REPORT_DATE), 'YYYY-MM-DD') AS DAY_LABEL,
						TRUNC(REPORT_DATE) AS DAY_VALUE,
						NVL(CAMPAIGN_ID, -1) AS CAMPAIGN_ID,
						SUM(NVL(COUNT_ENGAGED, 0)) AS COUNT_ENGAGED
					FROM REPORT_USER_DAILY
					" + whereClause + @"
					GROUP BY 
						TRUNC(REPORT_DATE),
						NVL(CAMPAIGN_ID, -1)
				) base
				LEFT JOIN B_CAMPAIGN c ON base.CAMPAIGN_ID = c.ID
				ORDER BY base.DAY_VALUE, base.CAMPAIGN_ID";

				using (OracleCommand cmd = new OracleCommand(sql, dbConnection))
				{
					cmd.CommandType = CommandType.Text;

					if (!string.IsNullOrEmpty(v_fromDate) && v_fromDate != "-1")
					{
						cmd.Parameters.Add(":fromDate", OracleDbType.NVarchar2).Value = v_fromDate;
					}

					if (!string.IsNullOrEmpty(v_toDate) && v_toDate != "-1")
					{
						cmd.Parameters.Add(":toDate", OracleDbType.NVarchar2).Value = v_toDate;
					}

					if (!string.IsNullOrEmpty(v_campaignId) && v_campaignId != "-1" && v_campaignId != "All")
					{
						cmd.Parameters.Add(":campaignId", OracleDbType.NVarchar2).Value = v_campaignId;
					}

					if (!string.IsNullOrEmpty(v_serviceId) && v_serviceId != "-1" && v_serviceId != "All")
					{
						cmd.Parameters.Add(":serviceId", OracleDbType.NVarchar2).Value = v_serviceId;
					}

					OracleDataAdapter adapter = new OracleDataAdapter(cmd);
					adapter.Fill(ds);
				}
			}
			catch (Exception ex)
			{
				throw ex;
			}
			finally
			{
				dbConnection.Close();
			}

			return ds;
		}



    }
}