增加分销商配置

wzp

2023-03-02 adee7a71ba7ab1b63cab63381dfe1846437853d6

 web/Dao/GwClientDao.cs

@@ -1,7 +1,7 @@

using Common;
using Model;
using Oracle.DataAccess.Client;
using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.Data;
@@ -21,10 +21,14 @@
        /// <param name="pageSizestring"></param>
        /// <param name="clientID">客户账号名称</param>
        /// <param name="clientName">联系人</param>
        /// <param name="telphone">联系电话</param>
        /// <param name="telephone">联系电话</param>
        /// <param name="agent">业务员</param>
        /// <param name="company">公司名称</param>
        /// <param name="salesman">归属业务员</param>
        /// <param name="supportStaff">归属客服人员</param>
        /// <param name="productId">产品ID</param>
        /// <returns></returns>
        public List<GwClient> LoadInfoList(out int recordCount, int pageIndex, int pageSizestring, string clientID, string clientName, string telphone, string agent)
        public List<GwClient> LoadInfoList(out int recordCount, int pageIndex, int pageSizestring, string clientID, string clientName, string telephone, string agent, string company, string salesman, string supportStaff, string productId, int isEnable,int isSub, string permissionsSQL )
        {

            List<GwClient> list = new List<GwClient>();
@@ -32,32 +36,92 @@
            try
            {
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append("from GW_CLIENT where 1=1");
                OracleParameter[] oracleParameterArray = new OracleParameter[4];
                stringBuilder.Append(" FROM ( ");
                stringBuilder.Append("SELECT gwc.*, t.PRODUCT_IDS  FROM GW_CLIENT gwc ");
                stringBuilder.Append(" LEFT JOIN ( ");
                stringBuilder.Append(" select gc.client_id, wm_concat( gp.name) PRODUCT_IDS from GW_CLIENT gc ");
                stringBuilder.Append(" LEFT JOIN gw_product gp on INSTR (CLIENT_IDS, gc.client_id) > 0 ");
                stringBuilder.Append(" GROUP BY gc.client_id ");
                stringBuilder.Append(" ) t on t.client_id = gwc.client_id ");
                //stringBuilder.Append("from GW_CLIENT where 1=1");
                stringBuilder.Append(" WHERE 1=1");
                OracleParameter[] oracleParameterArray = new OracleParameter[9];
                if (clientID != null && clientID != "")
                {
                    oracleParameterArray[0] = new OracleParameter(":ClientID", (OracleDbType)126);
                    ((DbParameter)oracleParameterArray[0]).Value = (object)('%' + clientID + '%');
                    stringBuilder.Append(" and CLIENT_ID like :ClientID");
                    stringBuilder.Append(" and gwc.CLIENT_ID like :ClientID");
                }
                if (clientName != null && clientName != "")
                {
                    oracleParameterArray[1] = new OracleParameter(":ClientName", (OracleDbType)126);
                    ((DbParameter)oracleParameterArray[1]).Value = (object)('%' + clientName + '%');
                    stringBuilder.Append(" and CLIENT_NAME like :ClientName");
                    stringBuilder.Append(" and gwc.CLIENT_NAME like :ClientName");
                }
                if (telphone != null && telphone != "")
                if (telephone != null && telephone != "")
                {
                    oracleParameterArray[2] = new OracleParameter(":Telphone", (OracleDbType)126);
                    ((DbParameter)oracleParameterArray[2]).Value = (object)('%' + telphone + '%');
                    stringBuilder.Append(" and Telphone like :Company");
                    oracleParameterArray[2] = new OracleParameter(":Telephone", (OracleDbType)126);
                    ((DbParameter)oracleParameterArray[2]).Value = (object)('%' + telephone + '%');
                    stringBuilder.Append(" and gwc.TELEPHONE like :Telephone");
                }
                if (company != null && company != "")
                {
                    oracleParameterArray[3] = new OracleParameter(":Company", (OracleDbType)126);
                    ((DbParameter)oracleParameterArray[3]).Value = (object)('%' + company + '%');
                    stringBuilder.Append(" and gwc.Company like :Company");
                }
                if (salesman != null && salesman != "")
                {
                    oracleParameterArray[4] = new OracleParameter(":Salesman", (OracleDbType)126);
                    ((DbParameter)oracleParameterArray[4]).Value = (object)( salesman );
                    stringBuilder.Append(" and gwc.Salesman = :Salesman");
                }
                if (agent != null && agent != "")
                {
                    oracleParameterArray[3] = new OracleParameter(":Agent", (OracleDbType)126);
                    ((DbParameter)oracleParameterArray[3]).Value = (object)("%" + agent + "%");
                    stringBuilder.Append(" and Agent like :Agent");
                    oracleParameterArray[5] = new OracleParameter(":Agent", (OracleDbType)126);
                    ((DbParameter)oracleParameterArray[5]).Value = (object)("%" + agent + "%");
                    stringBuilder.Append(" and gwc.Agent like :Agent");
                }
                
                if (supportStaff != null && supportStaff != "")
                {
                    oracleParameterArray[6] = new OracleParameter(":SupportStaff", (OracleDbType)126);
                    ((DbParameter)oracleParameterArray[6]).Value = (object)(supportStaff);
                    stringBuilder.Append(" and gwc.SUPPORT_STAFF = :SupportStaff");
                }
                if (isEnable != -1)
                {
                    oracleParameterArray[7] = new OracleParameter(":IS_ENABLE", (OracleDbType)112);
                    ((DbParameter)oracleParameterArray[7]).Value = (object)(isEnable);
                    stringBuilder.Append(" and gwc.IS_ENABLE = :IS_ENABLE");
                }

                //是否一级账户。0为一级
                if (isSub == 0)
                {
                    oracleParameterArray[8] = new OracleParameter(":CLIENT_PARENTID", OracleDbType.Int32);
                    ((DbParameter)oracleParameterArray[8]).Value = (object)(isSub);
                    stringBuilder.Append(" and gwc.CLIENT_PARENTID = :CLIENT_PARENTID");
                }
                else
                {
                    oracleParameterArray[8] = new OracleParameter(":CLIENT_PARENTID", OracleDbType.Int32);
                    ((DbParameter)oracleParameterArray[8]).Value = (object)(isSub);
                    stringBuilder.Append(" and gwc.CLIENT_PARENTID != 0");
                }


                if (productId != null && productId != "")
                {
                    stringBuilder.Append(" and gwc.CLIENT_ID IN ( SELECT distinct CLIENT_ID  FROM GW_SP WHERE PRODUCT_ID = '" + productId + "' )");
                }


                //客户权限SQL脚本
                stringBuilder.Append(permissionsSQL);

                stringBuilder.Append(" ) T ");

                stringBuilder.Append(" order by CREATE_TIME DESC,CLIENT_ID DESC");
                using (OracleDataReader reader = OracleHelper.ExecuteReader("select count(*) as count " + stringBuilder.ToString(), OracleHelper.Connection, oracleParameterArray))
                {
@@ -81,7 +145,95 @@
            }
            return list;
        }
        public List<GwClient> LoadInfoList(string clientID, string clientName, string company, string agent)

        //导出客户信息
        /// <returns></returns>
        public DataTable LoadClientDataTable(string clientID, string clientName, string telephone, string agent, string company, string salesman, string supportStaff, string productId, int isEnable, string permissionsSQL)
        {

            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append(" SELECT CLIENT_ID, COMPANY, CLIENT_NAME, TELEPHONE, Balance/1000 BALANCE, Top_up_Amount_Total/1000 TOP_UP_AMOUNT_TOTAL, SALESMAN_NAME, PRODUCT_IDS ");
            stringBuilder.Append(" , CASE WHEN (Is_Enable=1) then '启用' WHEN(Is_Enable=0) then '停用' end as Is_Enable ");
            stringBuilder.Append(" FROM ( ");
            stringBuilder.Append("SELECT gwc.*, t.PRODUCT_IDS, su.user_name SALESMAN_NAME  FROM GW_CLIENT gwc ");
            stringBuilder.Append(" LEFT JOIN ( ");
            stringBuilder.Append(" select gc.client_id, wm_concat( gp.name) PRODUCT_IDS from GW_CLIENT gc ");
            stringBuilder.Append(" LEFT JOIN gw_product gp on INSTR (CLIENT_IDS, gc.client_id) > 0 ");
            stringBuilder.Append(" GROUP BY gc.client_id ");
            stringBuilder.Append(" ) t on t.client_id = gwc.client_id ");
            stringBuilder.Append(" LEFT JOIN sys_user su on su.user_id = gwc.salesman ");
            //stringBuilder.Append("from GW_CLIENT where 1=1");
            stringBuilder.Append(" WHERE 1=1");
            OracleParameter[] oracleParameterArray = new OracleParameter[8];
            if (clientID != null && clientID != "")
            {
                oracleParameterArray[0] = new OracleParameter(":ClientID", (OracleDbType)126);
                ((DbParameter)oracleParameterArray[0]).Value = (object)('%' + clientID + '%');
                stringBuilder.Append(" and gwc.CLIENT_ID like :ClientID");
            }
            if (clientName != null && clientName != "")
            {
                oracleParameterArray[1] = new OracleParameter(":ClientName", (OracleDbType)126);
                ((DbParameter)oracleParameterArray[1]).Value = (object)('%' + clientName + '%');
                stringBuilder.Append(" and gwc.CLIENT_NAME like :ClientName");
            }
            if (telephone != null && telephone != "")
            {
                oracleParameterArray[2] = new OracleParameter(":Telephone", (OracleDbType)126);
                ((DbParameter)oracleParameterArray[2]).Value = (object)('%' + telephone + '%');
                stringBuilder.Append(" and gwc.TELEPHONE like :Telephone");
            }
            if (company != null && company != "")
            {
                oracleParameterArray[3] = new OracleParameter(":Company", (OracleDbType)126);
                ((DbParameter)oracleParameterArray[3]).Value = (object)('%' + company + '%');
                stringBuilder.Append(" and gwc.Company like :Company");
            }
            if (salesman != null && salesman != "")
            {
                oracleParameterArray[4] = new OracleParameter(":Salesman", (OracleDbType)126);
                ((DbParameter)oracleParameterArray[4]).Value = (object)(salesman);
                stringBuilder.Append(" and gwc.Salesman = :Salesman");
            }
            if (agent != null && agent != "")
            {
                oracleParameterArray[5] = new OracleParameter(":Agent", (OracleDbType)126);
                ((DbParameter)oracleParameterArray[5]).Value = (object)("%" + agent + "%");
                stringBuilder.Append(" and gwc.Agent like :Agent");
            }

            if (supportStaff != null && supportStaff != "")
            {
                oracleParameterArray[6] = new OracleParameter(":SupportStaff", (OracleDbType)126);
                ((DbParameter)oracleParameterArray[6]).Value = (object)(supportStaff);
                stringBuilder.Append(" and gwc.SUPPORT_STAFF = :SupportStaff");
            }
            if (isEnable != -1)
            {
                oracleParameterArray[7] = new OracleParameter(":IS_ENABLE", (OracleDbType)112);
                ((DbParameter)oracleParameterArray[7]).Value = (object)(isEnable);
                stringBuilder.Append(" and gwc.IS_ENABLE = :IS_ENABLE");
            }

            if (productId != null && productId != "")
            {
                stringBuilder.Append(" and gwc.CLIENT_ID IN ( SELECT distinct CLIENT_ID  FROM GW_SP WHERE PRODUCT_ID = '" + productId + "' )");
            }

            //客户权限SQL脚本
            stringBuilder.Append(permissionsSQL);

            stringBuilder.Append(" ) T ");

            stringBuilder.Append(" order by CREATE_TIME DESC,CLIENT_ID DESC");

            return OracleHelper.Execute(PubConstant.doOracleSql(100000, stringBuilder.ToString()).ToString(), OracleHelper.Connection, oracleParameterArray);
        }

        /**
         * 查询统计功能
         */
        public List<GwClient> LoadInfoList(string clientID, string clientName, string company, string agent, string permissionsSQL)
        {
            

@@ -89,7 +241,7 @@
            try
            {
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append("from GW_CLIENT where 1=1");
                stringBuilder.Append("from GW_CLIENT where 1=1 and IS_ENABLE=1 ");
                OracleParameter[] oracleParameterArray = new OracleParameter[4];
                if (clientID != null && clientID != "")
                {
@@ -115,6 +267,10 @@
                    ((DbParameter)oracleParameterArray[3]).Value = (object)("%" + agent + "%");
                    stringBuilder.Append(" and Agent like :Agent");
                }

                //客户权限SQL脚本
                stringBuilder.Append(permissionsSQL);

                stringBuilder.Append(" order by CREATE_TIME DESC,CLIENT_ID DESC");
                using (OracleDataReader reader = OracleHelper.ExecuteReader("select * " + stringBuilder.ToString(), OracleHelper.Connection, oracleParameterArray))
                {
@@ -155,6 +311,56 @@
            return OracleHelper.ExecuteSql("UPDATE GW_SP SET ALARM_MOBILE=:ALARM_MOBILE,BALANCE_THRESHOLD=:BALANCE_THRESHOLD WHERE CLIENT_ID=:CLIENT_ID", OracleHelper.Connection, new OracleParameter(":ALARM_MOBILE", (object)client.AlarmMobile), new OracleParameter(":BALANCE_THRESHOLD", (object)client.BalanceThreshold), new OracleParameter(":CLIENT_ID", (object)client.ClientID)) > 0;
        }

        //更新客户充值数据
        /**
         * payAmount:充值金额
         * AddBalance:累计充值余额
         * clientId：客户ID
         * 
         */
        public bool UpdateClientAmount(Int64 AddBalance, Int64 payAmount, string clientId )
        {
            return OracleHelper.ExecuteSql("UPDATE GW_CLIENT SET BALANCE=(BALANCE + :BALANCE)," +
                " TOP_UP_AMOUNT_TOTAL=(TOP_UP_AMOUNT_TOTAL + :TOP_UP_AMOUNT_TOTAL) WHERE CLIENT_ID=:CLIENT_ID", 
                OracleHelper.Connection, new OracleParameter(":BALANCE", (object)AddBalance), 
                new OracleParameter(":TOP_UP_AMOUNT_TOTAL", (object)payAmount), new OracleParameter(":CLIENT_ID", (object)clientId)) > 0;
        }

        public bool UpdateClientProduct(string clientId, string productId)
        {
            List<KeyValuePair<string, OracleParameter[]>> list = new List<KeyValuePair<string, OracleParameter[]>>();
            if (string.IsNullOrEmpty(productId) )
            {
                string key = "UPDATE GW_CLIENT_PRODUCT SET PRODUCT_ID=:PRODUCT_ID WHERE CLIENT_ID=:CLIENT_ID";
                OracleParameter[] oracleParameterArray = new OracleParameter[2]
                {
                  new OracleParameter(":PRODUCT_ID", (object) productId),
                  new OracleParameter(":CLIENT_ID", (object) clientId)
                };
                list.Add(new KeyValuePair<string, OracleParameter[]>(key, oracleParameterArray));
            }
            OracleHelper.ExecuteSqlTran(list, OracleHelper.Connection);
            return true;
        }

        //启用或停用
        public bool UpdateStatus(string clientId, string isEnable)
        {
            List<KeyValuePair<string, OracleParameter[]>> list = new List<KeyValuePair<string, OracleParameter[]>>();
            if (!string.IsNullOrEmpty(isEnable))
            {
                string key = "UPDATE GW_CLIENT SET IS_ENABLE=:IS_ENABLE WHERE CLIENT_ID=:CLIENT_ID";
                OracleParameter[] oracleParameterArray = new OracleParameter[2]
                {
                  new OracleParameter(":IS_ENABLE", (object) isEnable),
                  new OracleParameter(":CLIENT_ID", (object) clientId)
                };
                list.Add(new KeyValuePair<string, OracleParameter[]>(key, oracleParameterArray));
            }
            OracleHelper.ExecuteSqlTran(list, OracleHelper.Connection);
            return true;
        }

        /// <summary>
        /// 更新账号的信息
        /// </summary>
@@ -162,12 +368,53 @@
        /// <returns></returns>
        public bool Update(GwClient item)
        {
            return OracleHelper.ExecuteSql("UPDATE GW_CLIENT SET CLIENT_ID=:CLIENT_ID,CLIENT_NAME=:CLIENTNAME,TELEPHONE=:TELEPHONE,ADDRESS=:ADDRESS,COMPANY=:COMPANY,REMARK=:REMARK,PASSWORD=:PASSWORD,AGENT=:AGENT,PERMISSION_DATA=:PERMISSION_DATA,CREATE_TIME=SYSDATE WHERE CLIENT_ID=:Id", OracleHelper.Connection, new OracleParameter(":Id",(object)item.ClientID), new OracleParameter(":CLIENT_ID", (object)item.ClientID), new OracleParameter(":CLIENTNAME", (object)item.ClientName), new OracleParameter(":TELEPHONE", (object)item.Telephone), new OracleParameter(":ADDRESS", (object)item.Address), new OracleParameter(":COMPANY", (object)item.Company), new OracleParameter(":REMARK", (object)item.Remark), new OracleParameter(":PASSWORD", (object)item.Password), new OracleParameter(":AGENT", (object)item.Agent), new OracleParameter(":PERMISSION_DATA", (object)item.PermissionData)) > 0;
            return OracleHelper.ExecuteSql("UPDATE GW_CLIENT SET CLIENT_ID=:CLIENT_ID,CLIENT_NAME=:CLIENTNAME," +
                "TELEPHONE=:TELEPHONE,ADDRESS=:ADDRESS,COMPANY=:COMPANY,REMARK=:REMARK,AGENT=:AGENT,SALESMAN=:SALESMAN," +
                "CUSTOMER_MANAGER=:CUSTOMER_MANAGER,SUPPORT_STAFF=:SUPPORT_STAFF,FINANCIAL_STAFF=:FINANCIAL_STAFF," +
                "CHARGE_TYPE=:CHARGE_TYPE,SMS_STATUS_REPORT=:SMS_STATUS_REPORT,PERMISSION_DATA=:PERMISSION_DATA, " +
                "IS_ENABLE_WHITE=:IS_ENABLE_WHITE, IS_ENABLE_BLACK=:IS_ENABLE_BLACK, IS_REMOTE_LOGIN_NOTICE=:IS_REMOTE_LOGIN_NOTICE, " +
                "IS_MOBILE_CAPTCHA=:IS_MOBILE_CAPTCHA, IS_HIDE_MOBILE=:IS_HIDE_MOBILE, IS_DISPLAY_STATUS=:IS_DISPLAY_STATUS," +
                " IS_LOGIN_IP_RESTRICT=:IS_LOGIN_IP_RESTRICT, IS_LOGIN_IP_ALLOW_GATHER=:IS_LOGIN_IP_ALLOW_GATHER, IS_LOGIN_CITY_RESTRICT=:IS_LOGIN_CITY_RESTRICT," +
                " IS_LOGIN_CITY_ALLOW_GATHER=:IS_LOGIN_CITY_ALLOW_GATHER,CREATE_TIME=SYSDATE WHERE CLIENT_ID=:Id", 
                OracleHelper.Connection, new OracleParameter(":Id",(object)item.ClientID), new OracleParameter(":CLIENT_ID", (object)item.ClientID), 
                new OracleParameter(":CLIENTNAME", (object)item.ClientName), new OracleParameter(":TELEPHONE", (object)item.Telephone), 
                new OracleParameter(":ADDRESS", (object)item.Address), new OracleParameter(":COMPANY", (object)item.Company), 
                new OracleParameter(":REMARK", (object)item.Remark), new OracleParameter(":AGENT", (object)item.Agent),
                new OracleParameter(":SALESMAN", (object)item.Salesman), new OracleParameter(":CUSTOMER_MANAGER", (object)item.CustomerManager), 
                new OracleParameter(":SUPPORT_STAFF", (object)item.SupportStaff), new OracleParameter(":FINANCIAL_STAFF", (object)item.FinancialStaff),
                new OracleParameter(":CHARGE_TYPE", (object)item.ChargeType), new OracleParameter(":SMS_STATUS_REPORT", (object)item.SmsStatusReport), 
                new OracleParameter(":PERMISSION_DATA", (object)item.PermissionData), 
                new OracleParameter(":IS_ENABLE_WHITE", (object)item.IsEnableWhite), new OracleParameter(":IS_ENABLE_BLACK", (object)item.IsEnableBlack), 
                new OracleParameter(":IS_REMOTE_LOGIN_NOTICE", (object)item.IsRemoteLoginNotice),
                new OracleParameter(":IS_MOBILE_CAPTCHA", (object)item.IsMobileCaptcha), new OracleParameter(":IS_HIDE_MOBILE", (object)item.IsHideMobile), 
                new OracleParameter(":IS_DISPLAY_STATUS", (object)item.IsDisplayStatus), new OracleParameter(":IS_LOGIN_IP_RESTRICT", 
                (object)item.IsLoginIpRestrict), new OracleParameter(":IS_LOGIN_IP_ALLOW_GATHER", (object)item.IsLoginIpAllowGather), 
                new OracleParameter(":IS_LOGIN_CITY_RESTRICT", (object)item.IsLoginCityRestrict),
                new OracleParameter(":IS_LOGIN_CITY_ALLOW_GATHER", (object)item.IsLoginCityAllowGather) ) > 0;
        }

        public bool Add(GwClient item)
        {
            return OracleHelper.ExecuteSql("INSERT INTO GW_CLIENT(CLIENT_ID,CLIENT_NAME,TELEPHONE,ADDRESS,AGENT,COMPANY,REMARK,PASSWORD,PERMISSION_DATA,CREATE_TIME,PARENTID,Certification_Status) VALUES(:CLIENTID,:CLIENTNAME,:TELEPHONE,:ADDRESS,:AGENT,:COMPANY,:REMARK,:PASSWORD,:PERMISSION_DATA,SYSDATE,:PARENTID,:Certification_Status)", OracleHelper.Connection, new OracleParameter(":CLIENTID", (object)item.ClientID), new OracleParameter(":CLIENTNAME", (object)item.ClientName), new OracleParameter(":TELEPHONE", (object)item.Telephone), new OracleParameter(":ADDRESS", (object)item.Address), new OracleParameter(":AGENT", (object)item.Agent), new OracleParameter(":COMPANY", (object)item.Company), new OracleParameter(":REMARK", (object)item.Remark), new OracleParameter(":PASSWORD", (object)item.Password), new OracleParameter(":PERMISSION_DATA", (object)item.PermissionData), new OracleParameter(":PARENTID", (object)item.ParentId), new OracleParameter(":Certification_Status", (object)item.Certification_Status)) > 0;
            return OracleHelper.ExecuteSql("INSERT INTO GW_CLIENT(CLIENT_ID,CLIENT_NAME,TELEPHONE,ADDRESS,AGENT,SALESMAN,CUSTOMER_MANAGER,SUPPORT_STAFF,FINANCIAL_STAFF," +
                "CHARGE_TYPE,SMS_STATUS_REPORT,COMPANY,REMARK,PASSWORD, SALT, PERMISSION_DATA, IS_ENABLE_WHITE, IS_ENABLE_BLACK, IS_REMOTE_LOGIN_NOTICE, " +
                "IS_MOBILE_CAPTCHA, IS_HIDE_MOBILE, IS_DISPLAY_STATUS, IS_LOGIN_IP_RESTRICT, IS_LOGIN_IP_ALLOW_GATHER, IS_LOGIN_CITY_RESTRICT, IS_LOGIN_CITY_ALLOW_GATHER," +
                "CREATE_TIME,CLIENT_PARENTID,Certification_Status) VALUES (:CLIENTID,:CLIENTNAME,:TELEPHONE,:ADDRESS,:AGENT,:SALESMAN,:CUSTOMER_MANAGER,:SUPPORT_STAFF,:FINANCIAL_STAFF," +
                ":CHARGE_TYPE,:SMS_STATUS_REPORT,:COMPANY,:REMARK,:PASSWORD, :SALT,:PERMISSION_DATA, :IS_ENABLE_WHITE, :IS_ENABLE_BLACK, :IS_REMOTE_LOGIN_NOTICE, " +
                ":IS_MOBILE_CAPTCHA, :IS_HIDE_MOBILE, :IS_DISPLAY_STATUS, :IS_LOGIN_IP_RESTRICT, :IS_LOGIN_IP_ALLOW_GATHER, :IS_LOGIN_CITY_RESTRICT, :IS_LOGIN_CITY_ALLOW_GATHER,SYSDATE," +
                ":CLIENT_PARENTID,:Certification_Status)", OracleHelper.Connection, new OracleParameter(":CLIENTID", (object)item.ClientID), new OracleParameter(":CLIENTNAME", 
                (object)item.ClientName), new OracleParameter(":TELEPHONE", (object)item.Telephone), new OracleParameter(":ADDRESS", (object)item.Address),
                new OracleParameter(":AGENT", (object)item.Agent), new OracleParameter(":SALESMAN", (object)item.Salesman),
                new OracleParameter(":CUSTOMER_MANAGER", (object)item.CustomerManager), new OracleParameter(":SUPPORT_STAFF", (object)item.SupportStaff),
                new OracleParameter(":FINANCIAL_STAFF", (object)item.FinancialStaff), new OracleParameter(":CHARGE_TYPE", (object)item.ChargeType), 
                new OracleParameter(":SMS_STATUS_REPORT", (object)item.SmsStatusReport),
                new OracleParameter(":COMPANY", (object)item.Company), new OracleParameter(":REMARK", (object)item.Remark), new OracleParameter(":PASSWORD", (object)item.Password), 
                new OracleParameter(":SALT", (object)item.Salt), new OracleParameter(":PERMISSION_DATA", (object)item.PermissionData), new OracleParameter(":CLIENT_PARENTID", (object)item.Client_Parentid),
                new OracleParameter(":Certification_Status", (object)item.Certification_Status), new OracleParameter(":IS_ENABLE_WHITE", (object)item.IsEnableWhite),
                new OracleParameter(":IS_ENABLE_BLACK", (object)item.IsEnableBlack), new OracleParameter(":IS_REMOTE_LOGIN_NOTICE", (object)item.IsRemoteLoginNotice), 
                new OracleParameter(":IS_MOBILE_CAPTCHA", (object)item.IsMobileCaptcha), new OracleParameter(":IS_HIDE_MOBILE", (object)item.IsHideMobile), 
                new OracleParameter(":IS_DISPLAY_STATUS", (object)item.IsDisplayStatus), new OracleParameter(":IS_LOGIN_IP_RESTRICT", (object)item.IsLoginIpRestrict),
                new OracleParameter(":IS_LOGIN_IP_ALLOW_GATHER", (object)item.IsLoginIpAllowGather), new OracleParameter(":IS_LOGIN_CITY_RESTRICT", (object)item.IsLoginCityRestrict),
                new OracleParameter(":IS_LOGIN_CITY_ALLOW_GATHER", (object)item.IsLoginCityAllowGather) ) > 0;
        }

        public GwClient Get(string clientID)
@@ -263,10 +510,13 @@
            o.Company = oracleReaderWrapper.GetString("COMPANY", "");
            o.Remark = oracleReaderWrapper.GetString("REMARK", "");
            o.Password = oracleReaderWrapper.GetString("PASSWORD", "");
            o.Salt = oracleReaderWrapper.GetString("SALT", "");
            o.Agent = oracleReaderWrapper.GetString("AGENT", "");
            o.AlarmMobile = oracleReaderWrapper.GetString("ALARM_MOBILE", "");
            o.BalanceThreshold = oracleReaderWrapper.GetInt("BALANCE_THRESHOLD", 0);
            o.Balance = oracleReaderWrapper.GetInt("BALANCE", 0);
            o.PermissionData = oracleReaderWrapper.GetString("PERMISSION_DATA", "");
            o.ProductIds = oracleReaderWrapper.GetString("PRODUCT_IDS", ""); ;    //已分配的个性产品集，多个以“,”分隔
            o.Id = oracleReaderWrapper.GetInt("ID", 0);
            o.Certification_Status = oracleReaderWrapper.GetInt("CERTIFICATION_STATUS", 0);
            o.Client_Classes = oracleReaderWrapper.GetInt("CLIENT_CLASSES",0);
@@ -275,6 +525,24 @@
            o.Is_Enable = oracleReaderWrapper.GetInt("IS_ENABLE", 0);
            o.Top_up_Amount_Total = (decimal)oracleReaderWrapper.GetInt("TOP_UP_AMOUNT_TOTAL", 0);
            o.Salesman = oracleReaderWrapper.GetString("SALESMAN", "");
            o.CustomerManager = oracleReaderWrapper.GetString("CUSTOMER_MANAGER", "");
            o.SupportStaff = oracleReaderWrapper.GetString("SUPPORT_STAFF", "");
            o.FinancialStaff = oracleReaderWrapper.GetString("FINANCIAL_STAFF", "");
            o.ChargeType = oracleReaderWrapper.GetInt("CHARGE_TYPE", 0);
            o.SmsBillType = oracleReaderWrapper.GetInt("SMS_BILLTYPE",0);
            o.SmsStatusReport = oracleReaderWrapper.GetInt("SMS_STATUS_REPORT", 0);
            o.MmsStatusReport = oracleReaderWrapper.GetInt("MMS_STATUS_REPORT", 0);

            o.IsEnableWhite = oracleReaderWrapper.GetInt("IS_ENABLE_WHITE", 0);
            o.IsEnableBlack = oracleReaderWrapper.GetInt("IS_ENABLE_BLACK", 0);
            o.IsRemoteLoginNotice = oracleReaderWrapper.GetInt("IS_REMOTE_LOGIN_NOTICE", 0);
            o.IsMobileCaptcha = oracleReaderWrapper.GetInt("IS_MOBILE_CAPTCHA", 0);
            o.IsHideMobile = oracleReaderWrapper.GetInt("IS_HIDE_MOBILE", 0);
            o.IsDisplayStatus = oracleReaderWrapper.GetInt("IS_DISPLAY_STATUS", 0);
            o.IsLoginIpRestrict = oracleReaderWrapper.GetInt("IS_LOGIN_IP_RESTRICT", 0);
            o.IsLoginIpAllowGather = oracleReaderWrapper.GetString("IS_LOGIN_IP_ALLOW_GATHER", "");
            o.IsLoginCityRestrict = oracleReaderWrapper.GetInt("IS_LOGIN_CITY_RESTRICT", 0);
            o.IsLoginCityAllowGather = oracleReaderWrapper.GetString("IS_LOGIN_CITY_ALLOW_GATHER", "");
            return true;
        }

@@ -331,6 +599,152 @@
            }
            return true;
        }

        /**
         * 函数描述： 根据客户ID获取分配的个性产品
         * clientId: 客户户ID
         * userType: 用户类型，多个时以“,”分隔：1-管理员;2-业务员--全员;3-客户经理;4-客服人员;5-财务人员
         * return: SQL字符串
         * */

        public Dictionary<string, GwProduct> GetPersonalityProduct(string clientId)
        {
            Dictionary<string, GwProduct> dictionary = new Dictionary<string, GwProduct>();
            using (OracleDataReader reader = OracleHelper.ExecuteReader("SELECT * FROM GW_PRODUCT WHERE INSTR (CLIENT_IDS, :CLIENT_ID) > 0 ", OracleHelper.Connection, new OracleParameter(":CLIENT_ID", (object)clientId)))
            {
                while (reader != null && ((DbDataReader)reader).Read())
                {
                    OracleReaderWrapper oracleReaderWrapper = new OracleReaderWrapper(reader);
                    string string1 = oracleReaderWrapper.GetString("ID", "");
                    string string2 = oracleReaderWrapper.GetString("CLIENT_IDS", "");

                    GwProduct o = new GwProduct();
                    o.Id = string1;
                    o.ClientIds = string2;

                    dictionary[string1] = o;
                }
            }
            return dictionary;
        }

        //修改分配个性产品：修改确认
        public void UpdateProduct(string clientId, string[] productIdArray)
        {
            List<KeyValuePair<string, OracleParameter[]>> list = new List<KeyValuePair<string, OracleParameter[]>>();
            //更新个性分配客户集去除包含本客户
            string key1 = " UPDATE GW_PRODUCT SET CLIENT_IDS=(REPLACE(REPLACE(REPLACE(CLIENT_IDS, ','||:CLIENT_ID), :CLIENT_ID||',' ), :CLIENT_ID ) )  WHERE INSTR (CLIENT_IDS, :CLIENT_ID) > 0 ";
            OracleParameter[] oracleParameterArray1 = new OracleParameter[1]
              {
                new OracleParameter(":CLIENT_ID", (object) clientId)
              };

            list.Add(new KeyValuePair<string, OracleParameter[]>(key1, oracleParameterArray1));
            OracleHelper.ExecuteSqlTran(list, OracleHelper.Connection);
            if (productIdArray == null || productIdArray.Length <= 0)
                return;

            //更新个性分配客户集长度大于0时
            string key2 = "UPDATE GW_PRODUCT SET CLIENT_IDS=:CLIENT_ID || ',' || CLIENT_IDS  WHERE (CLIENT_IDS IS NOT NULL AND LENGTH(CLIENT_IDS) > 0) AND ID=:PRODUCT_ID ";

            list = new List<KeyValuePair<string, OracleParameter[]>>();
            foreach (string str in productIdArray)
            {
                OracleParameter[] oracleParameterArray2 = new OracleParameter[2]
                {
                  new OracleParameter(":CLIENT_ID", (object) clientId),
                  new OracleParameter(":PRODUCT_ID", (object) str)
                };

                list.Add(new KeyValuePair<string, OracleParameter[]>(key2, oracleParameterArray2));
            }
            OracleHelper.ExecuteSqlTran(list, OracleHelper.Connection);


            //更新个性分配客户集长度为0时
            string key3 = "UPDATE GW_PRODUCT SET CLIENT_IDS=:CLIENT_ID WHERE (CLIENT_IDS IS NULL OR LENGTH(CLIENT_IDS) <= 0) AND ID=:PRODUCT_ID ";

            list = new List<KeyValuePair<string, OracleParameter[]>>();
            foreach (string str in productIdArray)
            {
                OracleParameter[] oracleParameterArray2 = new OracleParameter[2]
                {
                  new OracleParameter(":CLIENT_ID", (object) clientId),
                  new OracleParameter(":PRODUCT_ID", (object) str)
                };
                
                list.Add(new KeyValuePair<string, OracleParameter[]>(key3, oracleParameterArray2));
            }
            OracleHelper.ExecuteSqlTran(list, OracleHelper.Connection);
        }


        /**
         * 函数描述： 根据系统用户获取有权限客户
         * userId: 用户ID
         * userType: 用户类型，多个时以“,”分隔：1-管理员;2-业务员--全员;3-客户经理;4-客服人员;5-财务人员
         * return: SQL字符串
         * */
        public string GetClientPermissions(int userId, string userType, string alias)
        {
            if (!string.IsNullOrEmpty(alias))
            {
                alias += ".";
            }

            string sqlStr = "";
            //业务员
            if(DataConverter.checkStrForArrayStr(2, userType))
            {
                sqlStr  += " AND " + alias + "CLIENT_ID IN (SELECT CLIENT_ID FROM GW_CLIENT WHERE IS_ENABLE=1 AND (SALESMAN='" + userId + "' ) ) ";
            }
            //客户经理
            else if (DataConverter.checkStrForArrayStr(3, userType) )
            {
                sqlStr += " AND " + alias + "CLIENT_ID IN (SELECT CLIENT_ID FROM GW_CLIENT WHERE IS_ENABLE=1 AND (SALESMAN='" + userId + "' or CUSTOMER_MANAGER='" + userId + "' ) ) ";
            }
            //管理员
            else if (DataConverter.checkStrForArrayStr(1, userType))
            {
                //sqlStr += " AND CLIENT_ID IN (SELECT CLIENT_ID FROM GW_CLIENT WHERE IS_ENABLE=1 ) ";
            }

            return sqlStr ;
        }

        /**
         * 函数描述： 根据系统用户获取有权限客户账号
         * userId: 用户ID
         * userType: 用户类型，多个时以“,”分隔：1-管理员;2-业务员--全员;3-客户经理;4-客服人员;5-财务人员
         * return: SQL字符串
         * */
        public string GetSpPermissions(int userId, string userType, string alias)
        {
            if (!string.IsNullOrEmpty(alias))
            {
                alias += ".";
            }

            string sqlStr = "";
            //业务员
            if (DataConverter.checkStrForArrayStr(2, userType))
            {
                sqlStr += " AND " + alias + "SP_ID IN (SELECT SP_ID from GW_SP where DEL_FLAG = 0 and CLIENT_ID IN (SELECT CLIENT_ID FROM GW_CLIENT WHERE IS_ENABLE=1 AND (SALESMAN='" + userId + "' ) ) ) ";
            }
            //客户经理
            else if (DataConverter.checkStrForArrayStr(3, userType))
            {
                sqlStr += " AND " + alias + "SP_ID IN (SELECT SP_ID from GW_SP where DEL_FLAG = 0 and CLIENT_ID IN (SELECT CLIENT_ID FROM GW_CLIENT WHERE IS_ENABLE=1 AND (SALESMAN='" + userId + "' or CUSTOMER_MANAGER='" + userId + "' ) ) ) ";
            }
            //管理员
            else if (DataConverter.checkStrForArrayStr(1, userType))
            {
                //sqlStr += " AND CLIENT_ID IN (SELECT CLIENT_ID FROM GW_CLIENT WHERE IS_ENABLE=1 ) ";
            }

            return sqlStr;
        }

    }
}