From 9c98f5d15e35587b7bcd9d7c8a506c467e758632 Mon Sep 17 00:00:00 2001
From: yzh <snbbt@21cn.com>
Date: 星期三, 08 六月 2022 00:54:15 +0800
Subject: [PATCH] 客户信息导出
---
web/Dao/GwClientDao.cs | 84 ++++++++++++++++++++++++++++++++++++++++++
1 files changed, 84 insertions(+), 0 deletions(-)
diff --git a/web/Dao/GwClientDao.cs b/web/Dao/GwClientDao.cs
index 4a7c3c8..91326a7 100644
--- a/web/Dao/GwClientDao.cs
+++ b/web/Dao/GwClientDao.cs
@@ -131,6 +131,90 @@
return list;
}
+ //瀵煎嚭瀹㈡埛淇℃伅
+ /// <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);
+ }
+
/**
* 鏌ヨ缁熻鍔熻兘
*/
--
Gitblit v1.9.1