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 ++++++++++++++++++++++++++++
web/Lib/Dao.dll | 0
web/web/Bin/Dao.dll | 0
web/Dao/obj/Debug/Dao.dll | 0
web/web/ExportDocument.aspx.cs | 62 ++++++++++++++++++++
web/web/GwClient.aspx | 21 ++++++
web/Lib/Dao.pdb | 0
web/web/Bin/Dao.pdb | 0
8 files changed, 165 insertions(+), 2 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);
+ }
+
/**
* 鏌ヨ缁熻鍔熻兘
*/
diff --git a/web/Dao/obj/Debug/Dao.dll b/web/Dao/obj/Debug/Dao.dll
index a7b22ce..3164e3c 100644
--- a/web/Dao/obj/Debug/Dao.dll
+++ b/web/Dao/obj/Debug/Dao.dll
Binary files differ
diff --git a/web/Lib/Dao.dll b/web/Lib/Dao.dll
index 969a278..3164e3c 100644
--- a/web/Lib/Dao.dll
+++ b/web/Lib/Dao.dll
Binary files differ
diff --git a/web/Lib/Dao.pdb b/web/Lib/Dao.pdb
index 4ea1d28..4ab63fc 100644
--- a/web/Lib/Dao.pdb
+++ b/web/Lib/Dao.pdb
Binary files differ
diff --git a/web/web/Bin/Dao.dll b/web/web/Bin/Dao.dll
index 969a278..3164e3c 100644
--- a/web/web/Bin/Dao.dll
+++ b/web/web/Bin/Dao.dll
Binary files differ
diff --git a/web/web/Bin/Dao.pdb b/web/web/Bin/Dao.pdb
index 4ea1d28..4ab63fc 100644
--- a/web/web/Bin/Dao.pdb
+++ b/web/web/Bin/Dao.pdb
Binary files differ
diff --git a/web/web/ExportDocument.aspx.cs b/web/web/ExportDocument.aspx.cs
index 6ad04c7..79b9573 100644
--- a/web/web/ExportDocument.aspx.cs
+++ b/web/web/ExportDocument.aspx.cs
@@ -24,13 +24,16 @@
switch (this.AppContext.GetString("action"))
{
case "exPortGwSp":
- this.ExPortGwSp();
+ this.ExPortGwSp();
break;
case "exPortGwSm":
this.ExportGwSmData();
break;
case "exportGwStatisV3"://瀵煎嚭缁熻鎶ヨ〃
this.ExprortGwStatisV3();
+ break;
+ case "exportGwClient": //瀵煎嚭瀹㈡埛淇℃伅
+ this.ExportGwClientData();
break;
}
}
@@ -343,4 +346,61 @@
webTool.Export(dataTable, dictionary, "鏃ュ織_" + sDate, this.Context.Response);
}
}
+
+ //瀵煎嚭瀹㈡埛淇℃伅
+ private void ExportGwClientData()
+ {
+
+ string ClientID = this.AppContext.GetString("ClientID");
+ string Company = this.AppContext.GetString("Company");
+ string ClientName = this.AppContext.GetString("ClientName");
+ string Telephone = this.AppContext.GetString("Telephone");
+ string Salesman = this.AppContext.GetString("Salesman");
+ string SupportStaff = this.AppContext.GetString("SupportStaff");
+ string ProductId = this.AppContext.GetString("ProductId");
+ string Agent = this.AppContext.GetString("Agent");
+ int IsEnable = this.AppContext.GetInt("IsEnable", -1);
+
+ int pageIndex = this.AppContext.GetInt("pageIndex", 1);
+ int recordCount = 0;
+ int pageSize = this.AppContext.GetInt("pageSize", 999999999);
+
+ string str = "";
+ using (GwClientDao dao = new GwClientDao())
+ {
+ string permissionsSQL = dao.GetClientPermissions(_userId, _userType, "gwc");
+ var list = dao.LoadInfoList(out recordCount, pageIndex, pageSize, ClientID, ClientName, Telephone, Agent, Company, Salesman, SupportStaff, ProductId, IsEnable, permissionsSQL);
+ if (recordCount> 1000000)
+ {
+ this.Response.Write("<script type='text/javascript'>alert('瀵煎嚭鏁版嵁涓嶈兘澶т簬100涓囨潯锛�');location.href='GwClient.aspx?IsEnable=" + IsEnable + "';</script>");
+ this.Response.End();
+
+ }
+
+ DataTable dataTable = dao.LoadClientDataTable(ClientID, ClientName, Telephone, Agent, Company, Salesman, SupportStaff, ProductId, IsEnable, permissionsSQL);
+
+ WebTool webTool = new WebTool();
+ Dictionary<string, string> dictionary = new Dictionary<string, string>();
+ dictionary["瀹㈡埛璐﹀彿"] = "CLIENT_ID";
+ dictionary["鍏徃鍚嶇О"] = "COMPANY";
+ dictionary["鑱旂郴浜�"] = "CLIENT_NAME";
+ dictionary["鑱旂郴鐢佃瘽"] = "TELEPHONE";
+ dictionary["璐︽埛浣欓(鍏�)"] = "BALANCE"; //宸查櫎浠�1000
+ dictionary["绱鍏呭��(鍏�)"] = "TOP_UP_AMOUNT_TOTAL"; //宸查櫎浠�1000
+ dictionary["涓氬姟鍛�"] = "SALESMAN_NAME";
+ dictionary["宸插垎閰嶄釜鎬т骇鍝�(澶氫釜鎸夆��,鈥濆垎闅�) "] = "PRODUCT_IDS";
+ dictionary["鐘舵�� "] = "Is_Enable";
+
+ string isEnableStr = "";
+ if (IsEnable == 1)
+ {
+ isEnableStr = "姝ｅ父";
+ }
+ else
+ {
+ isEnableStr = "鍋滅敤";
+ }
+ webTool.Export(dataTable, dictionary, isEnableStr + "瀹㈡埛淇℃伅_" + DateTime.Now, this.Context.Response);
+ }
+ }
}
diff --git a/web/web/GwClient.aspx b/web/web/GwClient.aspx
index 735e510..b424b71 100644
--- a/web/web/GwClient.aspx
+++ b/web/web/GwClient.aspx
@@ -18,7 +18,8 @@
var Company = $("#Company").val();
var ClientName = $("#ClientName").val();
var Telephone = $("#Telephone").val();
- var Agent = $("#Agent").val();
+ //var Agent = $("#Agent").val();
+ var Agent = "";
var Salesman = $("#Salesman").val();
var SupportStaff = $("#SupportStaff").val();
var ProductId = $("#ProductId").val();
@@ -402,6 +403,23 @@
});
}
});
+ });
+
+ //瀵煎嚭鏁版嵁
+ $(document).on("click", ".action-export", function () {
+ var ClientID = $("#ClientID").val();
+ var Company = $("#Company").val();
+ var ClientName = $("#ClientName").val();
+ var Telephone = $("#Telephone").val();
+ //var Agent = $("#Agent").val();
+ var Agent = "";
+ var Salesman = $("#Salesman").val();
+ var SupportStaff = $("#SupportStaff").val();
+ var ProductId = $("#ProductId").val();
+ var IsEnable = <%=IsEnable%>;
+ var url = "ExportDocument.aspx?action=exportGwClient&ClientID=" + ClientID + "&Company=" + Company + "&ClientName=" + ClientName + "&Telephone=" + Telephone + "&Agent=" + Agent + "&Salesman=" + Salesman + "&SupportStaff=" + SupportStaff + "&ProductId=" + ProductId + "&IsEnable=" + IsEnable + "";
+
+ window.location.href = url;
});
@@ -941,6 +959,7 @@
<% if(IsEnable==1) { %>
<input class="btn btn-danger action-modal-setProduct" value="鎵归噺鍒嗛厤涓�т骇鍝�" type="button" />
<%} %>
+ <input class="btn btn-info action-export" value="瀵煎嚭鏁版嵁" type="button" />
<table class="table table-striped table-bordered table-hover" id="gwclientTable">
<thead>
<tr class="header">
--
Gitblit v1.9.1