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> 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 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("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; 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 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("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; 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 sets = new List(); 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 sets = new List(); 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> 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> 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, 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 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, STATUS) VALUES(WEBSERVICE_SEQ.NEXTVAL, :ws_name, :ws_code, :wsdl, :msg_template, :error_tag, :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(":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 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, 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(":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; } } }