SMGW_NewWeb.git

			@@ -24,7 +24,8 @@
			/// <param name="telphone">联系电话</param>
			/// <param name="agent">业务员</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)
			public List<GwClient> LoadInfoList(out int recordCount, int pageIndex, int pageSizestring, string clientID, string clientName, string telphone, string agent, string company, string salesman, string productId, string permissionsSQL )
			{

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

			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))
			{
			@@ -81,7 +103,11 @@
			}
			return list;
			}
			public List<GwClient> LoadInfoList(string clientID, string clientName, string company, string agent)

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


			@@ -115,6 +141,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,10 +185,33 @@
			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;
			}

			//更新充值金额AMOUNT
			public bool UpdateClientAmount(GwClient client)
			//更新客户充值数据
			/**
			* payAmount:充值金额
			* AddBalance:累计充值余额
			* clientId：客户ID
			*
			*/
			public bool UpdateClientAmount(int AddBalance, int payAmount, string clientId )
			{
			return OracleHelper.ExecuteSql("UPDATE GW_CLIENT SET BALANCE=:BALANCE, TOP_UP_AMOUNT_TOTAL=:TOP_UP_AMOUNT_TOTAL WHERE CLIENT_ID=:CLIENT_ID", OracleHelper.Connection, new OracleParameter(":BALANCE", (object)client.Balance), new OracleParameter(":TOP_UP_AMOUNT_TOTAL", (object)client.Top_up_Amount_Total), new OracleParameter(":CLIENT_ID", (object)client.ClientID)) > 0;
			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>
			@@ -168,12 +221,12 @@
			/// <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,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,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,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)
			@@ -282,6 +335,12 @@
			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;
			}

			@@ -338,6 +397,71 @@
			}
			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;
			}
			}
			}