Bplus/ApiWeb/ApiProcessToken/Models/systemUserDataAccess.cs

using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;

namespace ResfullApi.Models
{
    public class systemUserDataAccess
    {
        public systemUserDataAccess()
        {

        }

        public static DataSet SYS_USER_WEB_CMS_GET_LIST(string v_users, string v_id, string v_username, string v_role, string v_isLock, string v_order, string v_rowsOnPage, string v_seqPage)
        {
            DataSet ds = new DataSet();
            OracleConnection dbConnection = DataAccess.getPoolingConnection();
            try
            {
                dbConnection.Open();
                
                // Parse pagination parameters
                int rowsOnPage = int.TryParse(v_rowsOnPage, out int r) ? r : 10;
                int seqPage = int.TryParse(v_seqPage, out int s) ? s : 1;
                
                // Build base query for counting total records
                string countSql = "SELECT COUNT(*) FROM USER_WEB_CMS WHERE 1=1";
                string dataSql = @"SELECT ID, USERNAME, PASSWORD, ROLE, COUNTRY_CODE, IS_LOCK, TOTAL_FALSE, TIME_LOCK, NOTE 
                                   FROM USER_WEB_CMS WHERE 1=1";
                
                // Add filters
                if (v_id != null && v_id != "-1")
                {
                    countSql += " AND ID = :v_id";
                    dataSql += " AND ID = :v_id";
                }
                
                if (v_username != null && v_username != "-1")
                {
                    countSql += " AND UPPER(USERNAME) LIKE UPPER(:v_username)";
                    dataSql += " AND UPPER(USERNAME) LIKE UPPER(:v_username)";
                }
                
                if (v_role != null && v_role != "-1")
                {
                    countSql += " AND UPPER(ROLE) LIKE UPPER(:v_role)";
                    dataSql += " AND UPPER(ROLE) LIKE UPPER(:v_role)";
                }
                
                if (v_isLock != null && v_isLock != "-1")
                {
                    countSql += " AND IS_LOCK = :v_isLock";
                    dataSql += " AND IS_LOCK = :v_isLock";
                }
                
                // Add ordering
                dataSql += " ORDER BY 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_username != null && v_username != "-1")
                {
                    countCmd.Parameters.Add(":v_username", OracleDbType.NVarchar2).Value = "%" + v_username + "%";
                }
                
                if (v_role != null && v_role != "-1")
                {
                    countCmd.Parameters.Add(":v_role", OracleDbType.NVarchar2).Value = "%" + v_role + "%";
                }
                
                if (v_isLock != null && v_isLock != "-1")
                {
                    countCmd.Parameters.Add(":v_isLock", OracleDbType.Int32).Value = int.Parse(v_isLock);
                }
                
                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_username != null && v_username != "-1")
                {
                    dataCmd.Parameters.Add(":v_username", OracleDbType.NVarchar2).Value = "%" + v_username + "%";
                }
                
                if (v_role != null && v_role != "-1")
                {
                    dataCmd.Parameters.Add(":v_role", OracleDbType.NVarchar2).Value = "%" + v_role + "%";
                }
                
                if (v_isLock != null && v_isLock != "-1")
                {
                    dataCmd.Parameters.Add(":v_isLock", OracleDbType.Int32).Value = int.Parse(v_isLock);
                }
                
                OracleDataAdapter dataAdapter = new OracleDataAdapter(dataCmd);
                dataAdapter.Fill(ds);
                
                // Add pagination metadata to each row
                if (ds.Tables.Count > 0)
                {
                    if (ds.Tables[0].Columns.Contains("ROW_ON_PAGE"))
                    {
                        ds.Tables[0].Columns.Remove("ROW_ON_PAGE");
                    }
                    if (ds.Tables[0].Columns.Contains("SEQ_PAGE"))
                    {
                        ds.Tables[0].Columns.Remove("SEQ_PAGE");
                    }
                    if (ds.Tables[0].Columns.Contains("TOTAL_PAGE"))
                    {
                        ds.Tables[0].Columns.Remove("TOTAL_PAGE");
                    }
                    
                    ds.Tables[0].Columns.Add("ROW_ON_PAGE", typeof(string));
                    ds.Tables[0].Columns.Add("SEQ_PAGE", typeof(string));
                    ds.Tables[0].Columns.Add("TOTAL_PAGE", typeof(string));
                    ds.Tables[0].Columns.Add("STATUS", typeof(string));
                    ds.Tables[0].Columns.Add("MSG", typeof(string));
                    
                    foreach (DataRow row in ds.Tables[0].Rows)
                    {
                        row["ROW_ON_PAGE"] = rowsOnPage.ToString();
                        row["SEQ_PAGE"] = seqPage.ToString();
                        row["TOTAL_PAGE"] = totalPage.ToString();
                        row["STATUS"] = "0";
                        row["MSG"] = "Success";
                    }
                }
            }
            catch (OracleException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbConnection.Close();
            }
            
            return ds;
        }

        public static DataSet SYS_USER_WEB_CMS_INSERT(string V_USERNAME, string V_PASSWORD, string V_ROLE, string V_COUNTRY_CODE, string V_NOTE, string V_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 USER_WEB_CMS(ID, USERNAME, PASSWORD, ROLE, COUNTRY_CODE, NOTE)
                               VALUES(USER_WEB_CMS_SEQ.NEXTVAL, :username, :password, :role, :countryCode, :note)";
                using (OracleCommand cmd = new OracleCommand(sql, dbConnection))
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add(":username", OracleDbType.NVarchar2).Value = V_USERNAME ?? "";
                    cmd.Parameters.Add(":password", OracleDbType.NVarchar2).Value = V_PASSWORD ?? "";
                    cmd.Parameters.Add(":role", OracleDbType.NVarchar2).Value = V_ROLE ?? "";
                    cmd.Parameters.Add(":countryCode", OracleDbType.NVarchar2).Value = V_COUNTRY_CODE ?? "";
                    cmd.Parameters.Add(":note", OracleDbType.NVarchar2).Value = V_NOTE ?? "";
                    int affected = cmd.ExecuteNonQuery();
                    
                    // get generated id in this session
                    string newId = "";
                    try
                    {
                        using (OracleCommand idCmd = new OracleCommand("SELECT USER_WEB_CMS_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 SYS_USER_WEB_CMS_UPDATE(string V_ID, string V_USERNAME, string V_PASSWORD, string V_ROLE, string V_COUNTRY_CODE, string V_IS_LOCK, string V_TIME_LOCK, string V_TOTAL_FALSE, string V_NOTE, string V_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();
                
                // Build dynamic update SQL based on provided fields
                List<string> updates = new List<string>();
                if (!string.IsNullOrEmpty(V_USERNAME) && V_USERNAME != "-1")
                {
                    updates.Add("USERNAME = :username");
                }
                if (!string.IsNullOrEmpty(V_PASSWORD) && V_PASSWORD != "-1")
                {
                    updates.Add("PASSWORD = :password");
                }
                if (!string.IsNullOrEmpty(V_ROLE) && V_ROLE != "-1")
                {
                    updates.Add("ROLE = :role");
                }
                if (!string.IsNullOrEmpty(V_COUNTRY_CODE) && V_COUNTRY_CODE != "-1")
                {
                    updates.Add("COUNTRY_CODE = :countryCode");
                }
                if (!string.IsNullOrEmpty(V_NOTE) && V_NOTE != "-1")
                {
                    updates.Add("NOTE = :note");
                }
                if (!string.IsNullOrEmpty(V_IS_LOCK) && V_IS_LOCK != "-1")
                {
                    updates.Add("IS_LOCK = :isLock");
                }
                if (!string.IsNullOrEmpty(V_TIME_LOCK) && V_TIME_LOCK != "-1")
                {
                    updates.Add("TIME_LOCK = :timeLock");
                }
                if (!string.IsNullOrEmpty(V_TOTAL_FALSE) && V_TOTAL_FALSE != "-1")
                {
                    updates.Add("TOTAL_FALSE = :totalFalse");
                }
                
                if (updates.Count == 0)
                {
                    var row = tb.NewRow();
                    row["status"] = "-1";
                    row["msg"] = "No fields to update";
                    tb.Rows.Add(row);
                    ds.Tables.Add(tb);
                    return ds;
                }
                
                string sql = @"UPDATE USER_WEB_CMS SET " + string.Join(", ", updates) + " WHERE ID = :id";
                
                using (OracleCommand cmd = new OracleCommand(sql, dbConnection))
                {
                    cmd.CommandType = CommandType.Text;
                    
                    if (!string.IsNullOrEmpty(V_USERNAME) && V_USERNAME != "-1")
                    {
                        cmd.Parameters.Add(":username", OracleDbType.NVarchar2).Value = V_USERNAME;
                    }
                    if (!string.IsNullOrEmpty(V_PASSWORD) && V_PASSWORD != "-1")
                    {
                        cmd.Parameters.Add(":password", OracleDbType.NVarchar2).Value = V_PASSWORD;
                    }
                    if (!string.IsNullOrEmpty(V_ROLE) && V_ROLE != "-1")
                    {
                        cmd.Parameters.Add(":role", OracleDbType.NVarchar2).Value = V_ROLE;
                    }
                    if (!string.IsNullOrEmpty(V_COUNTRY_CODE) && V_COUNTRY_CODE != "-1")
                    {
                        cmd.Parameters.Add(":countryCode", OracleDbType.NVarchar2).Value = V_COUNTRY_CODE;
                    }
                    if (!string.IsNullOrEmpty(V_NOTE) && V_NOTE != "-1")
                    {
                        cmd.Parameters.Add(":note", OracleDbType.NVarchar2).Value = V_NOTE;
                    }
                    if (!string.IsNullOrEmpty(V_IS_LOCK) && V_IS_LOCK != "-1")
                    {
                        cmd.Parameters.Add(":isLock", OracleDbType.Int32).Value = int.Parse(V_IS_LOCK);
                    }
                    if (!string.IsNullOrEmpty(V_TIME_LOCK) && V_TIME_LOCK != "-1")
                    {
                        cmd.Parameters.Add(":timeLock", OracleDbType.Date).Value = DateTime.Parse(V_TIME_LOCK);
                    }
                    if (!string.IsNullOrEmpty(V_TOTAL_FALSE) && V_TOTAL_FALSE != "-1")
                    {
                        cmd.Parameters.Add(":totalFalse", OracleDbType.Int32).Value = int.Parse(V_TOTAL_FALSE);
                    }
                    
                    cmd.Parameters.Add(":id", OracleDbType.NVarchar2).Value = V_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 SYS_FUNCTION_WEB_CMS_GET_LIST(string v_users, string v_id, string v_role, string v_name, string v_link, string v_order, string v_rowsOnPage, string v_seqPage)
        {
            DataSet ds = new DataSet();
            OracleConnection dbConnection = DataAccess.getPoolingConnection();
            try
            {
                dbConnection.Open();
                
                // Parse pagination parameters
                int rowsOnPage = int.TryParse(v_rowsOnPage, out int r) ? r : 10;
                int seqPage = int.TryParse(v_seqPage, out int s) ? s : 1;
                
                // Build base query for counting total records
                string countSql = "SELECT COUNT(*) FROM USER_WEB_CMS_FUNCTION WHERE 1=1";
                string dataSql = @"SELECT ID, ROLE, NAME, LINK, NOTE 
                                   FROM USER_WEB_CMS_FUNCTION WHERE 1=1";
                
                // Add filters
                if (v_id != null && v_id != "-1")
                {
                    countSql += " AND ID = :v_id";
                    dataSql += " AND ID = :v_id";
                }
                
                if (v_role != null && v_role != "-1")
                {
                    countSql += " AND UPPER(ROLE) LIKE UPPER(:v_role)";
                    dataSql += " AND UPPER(ROLE) LIKE UPPER(:v_role)";
                }
                
                if (v_name != null && v_name != "-1")
                {
                    countSql += " AND UPPER(NAME) LIKE UPPER(:v_name)";
                    dataSql += " AND UPPER(NAME) LIKE UPPER(:v_name)";
                }
                
                if (v_link != null && v_link != "-1")
                {
                    countSql += " AND UPPER(LINK) LIKE UPPER(:v_link)";
                    dataSql += " AND UPPER(LINK) LIKE UPPER(:v_link)";
                }
                
                // Add ordering
                dataSql += " ORDER BY 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_role != null && v_role != "-1")
                {
                    countCmd.Parameters.Add(":v_role", OracleDbType.NVarchar2).Value = "%" + v_role + "%";
                }
                
                if (v_name != null && v_name != "-1")
                {
                    countCmd.Parameters.Add(":v_name", OracleDbType.NVarchar2).Value = "%" + v_name + "%";
                }
                
                if (v_link != null && v_link != "-1")
                {
                    countCmd.Parameters.Add(":v_link", OracleDbType.NVarchar2).Value = "%" + v_link + "%";
                }
                
                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_role != null && v_role != "-1")
                {
                    dataCmd.Parameters.Add(":v_role", OracleDbType.NVarchar2).Value = "%" + v_role + "%";
                }
                
                if (v_name != null && v_name != "-1")
                {
                    dataCmd.Parameters.Add(":v_name", OracleDbType.NVarchar2).Value = "%" + v_name + "%";
                }
                
                if (v_link != null && v_link != "-1")
                {
                    dataCmd.Parameters.Add(":v_link", OracleDbType.NVarchar2).Value = "%" + v_link + "%";
                }
                
                OracleDataAdapter dataAdapter = new OracleDataAdapter(dataCmd);
                dataAdapter.Fill(ds);
                
                // Add pagination metadata to each row
                if (ds.Tables.Count > 0)
                {
                    if (ds.Tables[0].Columns.Contains("ROW_ON_PAGE"))
                    {
                        ds.Tables[0].Columns.Remove("ROW_ON_PAGE");
                    }
                    if (ds.Tables[0].Columns.Contains("SEQ_PAGE"))
                    {
                        ds.Tables[0].Columns.Remove("SEQ_PAGE");
                    }
                    if (ds.Tables[0].Columns.Contains("TOTAL_PAGE"))
                    {
                        ds.Tables[0].Columns.Remove("TOTAL_PAGE");
                    }
                    
                    ds.Tables[0].Columns.Add("ROW_ON_PAGE", typeof(string));
                    ds.Tables[0].Columns.Add("SEQ_PAGE", typeof(string));
                    ds.Tables[0].Columns.Add("TOTAL_PAGE", typeof(string));
                    ds.Tables[0].Columns.Add("STATUS", typeof(string));
                    ds.Tables[0].Columns.Add("MSG", typeof(string));
                    
                    foreach (DataRow row in ds.Tables[0].Rows)
                    {
                        row["ROW_ON_PAGE"] = rowsOnPage.ToString();
                        row["SEQ_PAGE"] = seqPage.ToString();
                        row["TOTAL_PAGE"] = totalPage.ToString();
                        row["STATUS"] = "0";
                        row["MSG"] = "Success";
                    }
                }
            }
            catch (OracleException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbConnection.Close();
            }
            
            return ds;
        }

        public static DataSet SYS_FUNCTION_WEB_CMS_INSERT(string V_ROLE, string V_NAME, string V_LINK, string V_NOTE, string V_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 USER_WEB_CMS_FUNCTION(ID, ROLE, NAME, LINK, NOTE)
                               VALUES(USER_WEB_CMS_FUNCTION_SEQ.NEXTVAL, :role, :name, :link, :note)";
                using (OracleCommand cmd = new OracleCommand(sql, dbConnection))
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add(":role", OracleDbType.NVarchar2).Value = V_ROLE ?? "";
                    cmd.Parameters.Add(":name", OracleDbType.NVarchar2).Value = V_NAME ?? "";
                    cmd.Parameters.Add(":link", OracleDbType.NVarchar2).Value = V_LINK ?? "";
                    cmd.Parameters.Add(":note", OracleDbType.NVarchar2).Value = V_NOTE ?? "";
                    int affected = cmd.ExecuteNonQuery();
                    
                    // get generated id in this session
                    string newId = "";
                    try
                    {
                        using (OracleCommand idCmd = new OracleCommand("SELECT USER_WEB_CMS_FUNCTION_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 SYS_FUNCTION_WEB_CMS_UPDATE(string V_ID, string V_ROLE, string V_NAME, string V_LINK, string V_NOTE, string V_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 USER_WEB_CMS_FUNCTION
                               SET ROLE = :role,
                                   NAME = :name,
                                   LINK = :link,
                                   NOTE = :note
                               WHERE ID = :id";
                using (OracleCommand cmd = new OracleCommand(sql, dbConnection))
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add(":role", OracleDbType.NVarchar2).Value = V_ROLE ?? "";
                    cmd.Parameters.Add(":name", OracleDbType.NVarchar2).Value = V_NAME ?? "";
                    cmd.Parameters.Add(":link", OracleDbType.NVarchar2).Value = V_LINK ?? "";
                    cmd.Parameters.Add(":note", OracleDbType.NVarchar2).Value = V_NOTE ?? "";
                    cmd.Parameters.Add(":id", OracleDbType.NVarchar2).Value = V_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;
        }
    }
}