| | |
| | | /// <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, string company, string salesman, string productId) |
| | | 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>(); |
| | |
| | | { |
| | | 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); |
| | |
| | | } |
| | | if (salesman != null && salesman != "") |
| | | { |
| | | oracleParameterArray[4] = new OracleParameter(":Salesman", (OracleDbType)112); |
| | | oracleParameterArray[4] = new OracleParameter(":Salesman", (OracleDbType)126); |
| | | ((DbParameter)oracleParameterArray[4]).Value = (object)( salesman ); |
| | | stringBuilder.Append(" and Salesman = :Salesman"); |
| | | } |
| | |
| | | { |
| | | 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)) |
| | |
| | | } |
| | | return list; |
| | | } |
| | | |
| | | /** |
| | | * 查询统计功能 |
| | | */ |
| | | public List<GwClient> LoadInfoList(string clientID, string clientName, string company, string agent) |
| | | { |
| | | |
| | |
| | | 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> |
| | |
| | | } |
| | | 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; |
| | | } |
| | | } |
| | | } |
| | | |