客户信息导出

yzh

2022-06-08 9c98f5d15e35587b7bcd9d7c8a506c467e758632

客户信息导出

 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);
        }

        /**
         * 查询统计功能
         */

 web/Dao/obj/Debug/Dao.dll

Binary files differ

 web/Lib/Dao.dll

Binary files differ

 web/Lib/Dao.pdb

Binary files differ

 web/web/Bin/Dao.dll

Binary files differ

 web/web/Bin/Dao.pdb

Binary files differ

 web/web/ExportDocument.aspx.cs

@@ -32,6 +32,9 @@
            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);
        }
    }
}

 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" />&nbsp;&nbsp;
        <table class="table table-striped table-bordered table-hover" id="gwclientTable">
            <thead>
                <tr class="header">