using Common;
using Model;
using Oracle.DataAccess.Client;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Text;

namespace Dao
{
    public class GwClientDao : IDisposable
    {

        /// <summary>
        /// 加载代理商客户列表
        /// </summary>
        /// <param name="recordCount">总计</param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSizestring"></param>
        /// <param name="clientID">客户账号名称</param>
        /// <param name="clientName">联系人</param>
        /// <param name="telphone">联系电话</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 telphone, string agent, string company, string salesman, string supportStaff, string productId, string permissionsSQL )
        {

            List<GwClient> list = new List<GwClient>();
            recordCount = 0;
            try
            {
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append("from GW_CLIENT where 1=1");
                OracleParameter[] oracleParameterArray = new OracleParameter[7];
                if (clientID != null && clientID != "")
                {
                    oracleParameterArray[0] = new OracleParameter(":ClientID", (OracleDbType)126);
                    ((DbParameter)oracleParameterArray[0]).Value = (object)('%' + clientID + '%');
                    stringBuilder.Append(" and 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");
                }
                if (telphone != null && telphone != "")
                {
                    oracleParameterArray[2] = new OracleParameter(":Telphone", (OracleDbType)126);
                    ((DbParameter)oracleParameterArray[2]).Value = (object)('%' + telphone + '%');
                    stringBuilder.Append(" and Telphone like :Telphone");
                }
                if (company != null && company != "")
                {
                    oracleParameterArray[3] = new OracleParameter(":Company", (OracleDbType)126);
                    ((DbParameter)oracleParameterArray[3]).Value = (object)('%' + company + '%');
                    stringBuilder.Append(" and Company like :Company");
                }
                if (salesman != null && salesman != "")
                {
                    oracleParameterArray[4] = new OracleParameter(":Salesman", (OracleDbType)126);
                    ((DbParameter)oracleParameterArray[4]).Value = (object)( salesman );
                    stringBuilder.Append(" and Salesman = :Salesman");
                }
                if (agent != null && agent != "")
                {
                    oracleParameterArray[5] = new OracleParameter(":Agent", (OracleDbType)126);
                    ((DbParameter)oracleParameterArray[5]).Value = (object)("%" + agent + "%");
                    stringBuilder.Append(" and Agent like :Agent");
                }
                if (supportStaff != null && supportStaff != "")
                {
                    oracleParameterArray[6] = new OracleParameter(":SupportStaff", (OracleDbType)126);
                    ((DbParameter)oracleParameterArray[6]).Value = (object)(supportStaff);
                    stringBuilder.Append(" and SUPPORT_STAFF = :SupportStaff");
                }

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

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

                stringBuilder.Append(" order by CREATE_TIME DESC,CLIENT_ID DESC");
                using (OracleDataReader reader = OracleHelper.ExecuteReader("select count(*) as count " + stringBuilder.ToString(), OracleHelper.Connection, oracleParameterArray))
                {
                    while (((DbDataReader)reader).Read())
                        recordCount = this.ReadCount(reader);
                }
                using (OracleDataReader reader = OracleHelper.ExecuteReader(PubConstant.doOracleSql(pageIndex, pageSizestring, recordCount, "select * " + stringBuilder.ToString()).ToString(), OracleHelper.Connection, oracleParameterArray))
                {
                    while (((DbDataReader)reader).Read())
                    {
                        GwClient o = new GwClient();
                        if (this.ReadInfo(reader, o))
                            list.Add(o);
                    }
                }
            }
            catch (Exception ex)
            {
                LogHelper.Error(ex);
                return list;
            }
            return list;
        }

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

            List<GwClient> list = new List<GwClient>();
            try
            {
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append("from GW_CLIENT where 1=1");
                OracleParameter[] oracleParameterArray = new OracleParameter[4];
                if (clientID != null && clientID != "")
                {
                    oracleParameterArray[0] = new OracleParameter(":ClientID", (OracleDbType)126);
                    ((DbParameter)oracleParameterArray[0]).Value = (object)('%' + clientID + '%');
                    stringBuilder.Append(" and 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");
                }
                if (company != null && company != "")
                {
                    oracleParameterArray[2] = new OracleParameter(":Company", (OracleDbType)126);
                    ((DbParameter)oracleParameterArray[2]).Value = (object)('%' + company + '%');
                    stringBuilder.Append(" and COMPANY like :Company");
                }
                if (agent != null && agent != "")
                {
                    oracleParameterArray[3] = new OracleParameter(":Agent", (OracleDbType)126);
                    ((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))
                {
                    while (((DbDataReader)reader).Read())
                    {
                        GwClient o = new GwClient();
                        if (this.ReadInfo(reader, o))
                            list.Add(o);
                    }
                }
            }
            catch (Exception ex)
            {
                LogHelper.Error(ex);
                return list;
            }
            return list;
        }

        public bool UpdatePassword(string clientID, string password)
        {
            return OracleHelper.ExecuteSql("UPDATE GW_CLIENT SET PASSWORD=:PASSWORD WHERE CLIENT_ID=:CLIENT_ID", OracleHelper.Connection, new OracleParameter(":CLIENT_ID", (object)clientID), new OracleParameter(":PASSWORD", (object)password)) > 0;
        }

        public bool UpdatePassword(string clientID, string oldPassword, string newPassword)
        {
            string str = DataHelper.MD5Hex(oldPassword);
            return OracleHelper.ExecuteSql("update GW_CLIENT SET PASSWORD=:NEWPASSWORD WHERE CLIENT_ID=:CLIENT_ID AND ((PASSWORD=:OLDPASSWORD) OR (PASSWORD=:MD5Password) )", OracleHelper.Connection, new OracleParameter(":NEWPASSWORD", (object)DataHelper.MD5Hex(newPassword)), new OracleParameter(":OLDPASSWORD", (object)oldPassword), new OracleParameter(":MD5Password", (object)str), new OracleParameter(":CLIENT_ID", (object)clientID)) >= 1;
        }

        public bool UpdateClientInfo(GwClient client)
        {
            return OracleHelper.ExecuteSql("UPDATE GW_CLIENT SET CLIENT_NAME=:CLIENTNAME,TELEPHONE=:TELEPHONE,ADDRESS=:ADDRESS,COMPANY=:COMPANY,REMARK=:REMARK,ALARM_MOBILE=:ALARM_MOBILE,BALANCE_THRESHOLD=:BALANCE_THRESHOLD WHERE CLIENT_ID=:CLIENT_ID", OracleHelper.Connection, new OracleParameter(":CLIENTNAME", (object)client.ClientName), new OracleParameter(":TELEPHONE", (object)client.Telephone), new OracleParameter(":ADDRESS", (object)client.Address), new OracleParameter(":COMPANY", (object)client.Company), new OracleParameter(":REMARK", (object)client.Remark), new OracleParameter(":ALARM_MOBILE", (object)client.AlarmMobile), new OracleParameter(":BALANCE_THRESHOLD", (object)client.BalanceThreshold), new OracleParameter(":CLIENT_ID", (object)client.ClientID)) > 0 && this.UpdateClientSp(client);
        }

        public bool UpdateClientSp(GwClient client)
        {
            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(int AddBalance, int 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;
        }

        /// <summary>
        /// 更新账号的信息
        /// </summary>
        /// <param name="item"></param>
        /// <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,SALESMAN=:SALESMAN,CUSTOMER_MANAGER=:CUSTOMER_MANAGER,SUPPORT_STAFF=:SUPPORT_STAFF,FINANCIAL_STAFF=:FINANCIAL_STAFF,CHARGE_TYPE=:CHARGE_TYPE,SMS_STATUS_REPORT=:SMS_STATUS_REPORT,MMS_STATUS_REPORT=:MMS_STATUS_REPORT,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(":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(":MMS_STATUS_REPORT", (object)item.MmsStatusReport), new OracleParameter(":PERMISSION_DATA", (object)item.PermissionData)) > 0;
        }

        public bool Add(GwClient item)
        {
            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,MMS_STATUS_REPORT,COMPANY,REMARK,PASSWORD,PERMISSION_DATA,CREATE_TIME,PARENTID,Certification_Status) VALUES(:CLIENTID,:CLIENTNAME,:TELEPHONE,:ADDRESS,:AGENT,:SALESMAN,:CUSTOMER_MANAGER,:SUPPORT_STAFF,:FINANCIAL_STAFF,:CHARGE_TYPE,:SMS_STATUS_REPORT,:MMS_STATUS_REPORT,: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(":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(":MMS_STATUS_REPORT", (object)item.MmsStatusReport), 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;
        }

        public GwClient Get(string clientID)
        {
            GwClient o = new GwClient();
            if (string.IsNullOrEmpty(clientID) || string.Equals("0", clientID))
                return o;
            using (OracleDataReader reader = OracleHelper.ExecuteReader(string.Format("select * from GW_CLIENT where CLIENT_ID=:CLIENT_ID"), OracleHelper.Connection, new OracleParameter(":CLIENT_ID", (object)clientID)))
            {
                if (((DbDataReader)reader).Read())
                {
                    this.ReadInfo(reader, o);
                    return o;
                }
            }
            return o;
        }

        public GwClient GetClientName(string clientName)
        {
            GwClient o = new GwClient();
            if (string.IsNullOrEmpty(clientName) || string.Equals("0", clientName))
                return o;
            using (OracleDataReader reader = OracleHelper.ExecuteReader(string.Format("select * from GW_CLIENT where CLIENT_NAME=:CLIENT_NAME"), OracleHelper.Connection, new OracleParameter(":CLIENT_NAME", (object)clientName)))
            {
                if (((DbDataReader)reader).Read())
                {
                    this.ReadInfo(reader, o);
                    return o;
                }
            }
            return o;
        }

        public List<GwClient> Clientlist()
        {
            List<GwClient> list = new List<GwClient>();
            using (OracleDataReader reader = OracleHelper.ExecuteReader("SELECT * FROM GW_CLIENT", OracleHelper.Connection))
            {
                while (((DbDataReader)reader).Read())
                {
                    GwClient o = new GwClient();
                    if (this.ReadInfo(reader, o))
                        list.Add(o);
                }
            }
            return list;
        }

        /// <summary>
        /// 判断账户是否存在
        /// </summary>
        /// <param name="clientID">账户</param>
        /// <returns></returns>
        public bool IsIDExists(string clientID)
        {
            if (string.IsNullOrEmpty(clientID) || string.Equals("0", clientID))
                return true;
            using (OracleDataReader oracleDataReader = OracleHelper.ExecuteReader(string.Format("select * from GW_CLIENT where CLIENT_ID=:CLIENT_ID"), OracleHelper.Connection, new OracleParameter(":CLIENT_ID", (object)clientID)))
            {
                if (((DbDataReader)oracleDataReader).Read())
                    return true;
            }
            return false;
        }

        public bool IsNameExists(string clientName)
        {
            if (string.IsNullOrEmpty(clientName) || string.Equals("0", clientName))
                return true;
            using (OracleDataReader oracleDataReader = OracleHelper.ExecuteReader(string.Format("select * from GW_CLIENT where CLIENT_NAME=:CLIENT_NAME"), OracleHelper.Connection, new OracleParameter(":CLIENT_NAME", (object)clientName)))
            {
                if (((DbDataReader)oracleDataReader).Read())
                    return true;
            }
            return false;
        }

        public bool Delete(string clientID)
        {
            if (string.IsNullOrEmpty(clientID) || string.Equals("0", clientID))
                return false;
            return OracleHelper.ExecuteSql(string.Format("delete from GW_CLIENT where CLIENT_ID=:CLIENT_ID"), OracleHelper.Connection, new OracleParameter(":CLIENT_ID", (object)clientID)) > 0;
        }

        private bool ReadInfo(OracleDataReader reader, GwClient o)
        {
            OracleReaderWrapper oracleReaderWrapper = new OracleReaderWrapper(reader);
            o.ClientID = oracleReaderWrapper.GetString("CLIENT_ID", "");
            o.ClientName = oracleReaderWrapper.GetString("CLIENT_NAME", "");
            o.Telephone = oracleReaderWrapper.GetString("TELEPHONE", "");
            o.Address = oracleReaderWrapper.GetString("ADDRESS", "");
            o.Company = oracleReaderWrapper.GetString("COMPANY", "");
            o.Remark = oracleReaderWrapper.GetString("REMARK", "");
            o.Password = oracleReaderWrapper.GetString("PASSWORD", "");
            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.Id = oracleReaderWrapper.GetInt("ID", 0);
            o.Certification_Status = oracleReaderWrapper.GetInt("CERTIFICATION_STATUS", 0);
            o.Client_Classes = oracleReaderWrapper.GetInt("CLIENT_CLASSES",0);
            o.Client_Parentid = oracleReaderWrapper.GetString("CLIENT_PARENTID", "");
            o.Client_Type = oracleReaderWrapper.GetInt("CLIENT_TYPE", 0);
            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.SmsStatusReport = oracleReaderWrapper.GetInt("SMS_STATUS_REPORT", 0);
            o.MmsStatusReport = oracleReaderWrapper.GetInt("MMS_STATUS_REPORT", 0);
            return true;
        }

        private int ReadCount(OracleDataReader reader)
        {
            return new OracleReaderWrapper(reader).GetInt("count", 0);
        }

        public void Dispose()
        {
        }

        public bool UpdateWhiteList(string clientID, string mobileList)
        {
            OracleHelper.ExecuteSql("BEGIN\r\n    UPDATE GW_CLIENT_WHITE SET MOBILE_LIST=:MOBILE_LIST WHERE CLIENT_ID=:CLIENT_ID;\r\n    IF SQL%ROWCOUNT =0 THEN\r\n        INSERT INTO GW_CLIENT_WHITE(CLIENT_ID,MOBILE_LIST) VALUES(:CLIENT_ID,:MOBILE_LIST);\r\n    END IF;\r\nEND;", OracleHelper.Connection, new OracleParameter(":CLIENT_ID", (object)clientID), new OracleParameter(":MOBILE_LIST", (OracleDbType)105, (object)mobileList, ParameterDirection.Input));
            return true;
        }

        public bool UpdateBlackList(string clientID, string mobileList)
        {
            OracleHelper.ExecuteSql("BEGIN\r\n    UPDATE GW_CLIENT_BLACK SET MOBILE_LIST=:MOBILE_LIST WHERE CLIENT_ID=:CLIENT_ID;\r\n    IF SQL%ROWCOUNT =0 THEN\r\n        INSERT INTO GW_CLIENT_BLACK(CLIENT_ID,MOBILE_LIST) VALUES(:CLIENT_ID,:MOBILE_LIST);\r\n    END IF;\r\nEND;", OracleHelper.Connection, new OracleParameter(":CLIENT_ID", (object)clientID), new OracleParameter(":MOBILE_LIST", (OracleDbType)105, (object)mobileList, ParameterDirection.Input));
            return true;
        }

        public string GetBlackList(string clientId)
        {
            using (OracleDataReader reader = OracleHelper.ExecuteReader("SELECT * FROM GW_CLIENT_BLACK WHERE CLIENT_ID=:CLIENT_ID", OracleHelper.Connection, new OracleParameter(":CLIENT_ID", (object)clientId)))
            {
                if (reader != null && ((DbDataReader)reader).Read())
                    return new OracleReaderWrapper(reader).GetString("MOBILE_LIST", string.Empty);
            }
            return string.Empty;
        }

        public string GetWhiteList(string clientId)
        {
            using (OracleDataReader reader = OracleHelper.ExecuteReader("SELECT * FROM GW_CLIENT_WHITE WHERE CLIENT_ID=:CLIENT_ID", OracleHelper.Connection, new OracleParameter(":CLIENT_ID", (object)clientId)))
            {
                if (reader != null && ((DbDataReader)reader).Read())
                    return new OracleReaderWrapper(reader).GetString("MOBILE_LIST", string.Empty);
            }
            return string.Empty;
        }

        public bool Login(string account, string password, out GwClient client)
        {
            client = new GwClient();
            string str = DataHelper.MD5Hex(password);
            using (OracleDataReader reader = OracleHelper.ExecuteReader("SELECT * FROM GW_CLIENT WHERE CLIENT_ID=:ACCOUNT AND ((PASSWORD=:PASSWORD) OR (PASSWORD=:MD5Password)) ", OracleHelper.Connection, new OracleParameter(":ACCOUNT", (object)account), new OracleParameter(":PASSWORD", (object)password), new OracleParameter(":MD5Password", (object)str)))
            {
                if (reader == null || !((DbDataReader)reader).Read())
                    return false;
                this.ReadInfo(reader, client);
            }
            return true;
        }


        /**
         * 函数描述： 根据系统用户获取有权限客户
         * userId: 用户ID
         * userType: 用户类型：1-管理员;2-业务员--全员;3-业务主管;4-业务总监;5-客服人员;6-财务人员;99-其他
         * return: SQL字符串
         * */
        public string GetClientPermissions(int userId, int userType, string alias)
        {
            if (!string.IsNullOrEmpty(alias))
            {
                alias += ".";
            }

            string sqlStr = "";
            //业务员和其他
            if(userType==2 || userType == 99)
            {
                sqlStr  += " AND " + alias + "CLIENT_ID IN (SELECT CLIENT_ID FROM GW_CLIENT WHERE IS_ENABLE=1 AND (SALESMAN='" + userId + "' ) ) ";
            }
            //业务主管
            else if (userType == 3)
            {
                sqlStr += " AND " + alias + "CLIENT_ID IN (SELECT CLIENT_ID FROM GW_CLIENT WHERE IS_ENABLE=1 AND (SALESMAN='" + userId + "' or CUSTOMER_MANAGER='" + userId + "' ) ) ";
            }
            else
            {
                //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-客服人员;6-财务人员;99-其他
         * return: SQL字符串
         * */
        public string GetSpPermissions(int userId, int userType, string alias)
        {
            if (!string.IsNullOrEmpty(alias))
            {
                alias += ".";
            }

            string sqlStr = "";
            //业务员和其他
            if (userType == 2 || userType == 99)
            {
                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 (userType == 3)
            {
                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
            {
                //sqlStr += " AND CLIENT_ID IN (SELECT CLIENT_ID FROM GW_CLIENT WHERE IS_ENABLE=1 ) ";
            }

            return sqlStr;
        }
    }
}