MemberCenter/Member/MemberWeb/Controllers/ES_DataStaisController.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Threading.Tasks;
using Common.Model;
using CoreEntity.Entity;
using CoreEntity.ESEntity;
using JCSoft.WX.Framework.Api;
using MemberWeb.Commonss;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Caching.Memory;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using NPOI.HSSF.UserModel;
using PublicLibrary.Model;
using SupplierWeb.Commonss;
using ZcPeng.PublicLibrary;
using Num = CoreEntity.ESEntity.Num;

namespace SupplierWeb.Controllers
{
    [Route("web/ES_DataStais")]
    public class ES_DataStaisController : BaseController
    {

        public ES_DataStaisController(IMemoryCache cache, IApiClient client) : base(cache, client)
        {

        }
        public IActionResult Index()
        {
            return View();
        }

        [HttpPost]
        [Route("index")]
        public ActionResult index([FromBody]dynamic data, QueryFilter[] filters,string sortField, Int32 sortDirection, Int32 pageIndex, Int32 pageSize,string scrollId)
        {
            int start = 0;
            string filter = "";
            string redisKey = "1_redis_stais";//缓存Key
            Object query = null;
            DateTime dateTime = DateTime.Parse(DateTime.Now.ToString());
            string orderStr = "{\"ym"+ dateTime.AddDays(-60).ToString("yyyyMM") + ".RM\":{\"order\":\"desc\"}}";
                if (data != null)
                {
                    filters = data.filters.ToObject<QueryFilter[]>();
                    pageIndex = data.pageIndex;
                    pageSize = data.pageSize;
                    sortField = data.sortField;
                    sortDirection = data.sortDirection;
                }
            string dslStr = "\"query\":{ \"bool\": { \"must\": { \"exists\": {\"field\": \"F1*\"}}}}";
            string dslCount = "\"_source\":{\"include\":[\" * \"]}";
            string totalStr = "";//按选择月份.统计
            string totalRing = "";//用户选择平台
            //拼接组装dsl条件
            if (filters != null && filters.Length > 0)
            {
                string str = "";
                foreach (var item in filters) {
                    if (item.field.Contains("tagKey"))
                    {
                        if (item.field == "tagKey1" || item.field == "tagKey3")
                        {
                            if (item.field == "tagKey1")
                            {
                                item.value = "ym" + item.value.Replace("-", "");
                                str += "{ \"exists\": {\"field\": \"" + item.value + "\"}},";
                                redisKey += item.value;//时间
                                totalStr = item.value;
                            }
                            else
                            {
                                var key1 = item.value.Split(",");
                                foreach (string tag in key1)
                                {
                                    str += "{ \"exists\": {\"field\": \"OriginType-" + tag + "\"}},";
                                    redisKey += tag;
                                }
                                totalRing = item.value;//平台
                            }
                        }
                        else
                        {
                            var key0 = item.value.Split(",");
                            foreach (string tag in key0)
                            {
                                str += "{\"nested\": {\"path\":\"Tags\",\"query\": {\"bool\": {\"must\": [{\"match\": {\"Tags.TagId\":" + tag + "}}]}}}},";
                                redisKey += tag;
                            }
;
                        }

                    }
                    else {
                        if (item.value!=""&&item.value.Length>0) {
                            str += "{\"match_phrase\": { \""+item.field+"\": \""+item.value+"\"}},";
                            redisKey +=item.value;
                        }
                    }

                }
                if (str != "" && str.Length > 0) {
                    dslStr = "\"query\": {\"bool\": {\"should\": [" + str.Substring(0, str.Length - 1) + "]}}";
                    dslCount = "\"query\": {\"bool\": {\"should\": [" + str.Substring(0, str.Length - 1) + "]}}";
                }
            }
            if (filters != null && filters.Length > 0 && filters[0].field.Equals("scrollId"))
            {
                scrollId = filters[0].value;
            }

            string redisKey1 = redisKey;
            //排序
            string direct = "desc";
                if (data.sortDirection != 1)
                {
                    direct = "asc";
                }

                if (sortField != null && (sortField.Equals("M1")|| sortField.Equals("F1"))) {
                    orderStr = "{\"" + sortField + "\":{\"order\":\"" + direct + "\"}}";
                    redisKey1 += sortField + direct;
                }

                if (data.pageIndex == 1)
                {
                    start = 0;
                }
                else
                {
                    start = ((data.pageIndex - 1) * data.pageSize) + 1;
                }
            //数据列表
            redisKey1 += Convert.ToString(start);
            string jsonTag = "";
            string sqlTag = RedisUtil.getRedisEsUtils(redisKey1 + "_6");//获取缓存
            if (start >= 10000)
            {
                if (scrollId != null && scrollId.Length > 0)
                {//游标继续获取
                    jsonTag = "{\"scroll_id\": \""+ scrollId + "\"}";
                    sqlTag = Util.ElasticQuery("mem_repurchaseinfo/_search?scroll=2m", "POST", jsonTag);
                }
                else
                {//初始获取数据scroll_id
                    jsonTag = "{" + dslStr + ",\"sort\":[" + orderStr + "],\"size\": " + data.pageSize + "}";

                    sqlTag = Util.ElasticQuery("mem_repurchaseinfo/_search?scroll=2m", "POST", jsonTag);
                }

            }
            else {
                
                if (sqlTag == null || sqlTag.Length <= 0)
                {
                    jsonTag = "{" + dslStr + ",\"sort\":[" + orderStr + "],\"from\":" + start + ",\"size\": " + data.pageSize + "}";

                    //没有缓存，加入缓存
                    sqlTag = Util.ElasticQuery("mem_repurchaseinfo/_search?format=json", "POST", jsonTag);
                    RedisUtil.addRedisEsUtils(sqlTag, redisKey1 + "_6");//加入缓存
                }
            }

                
            Root<Mem_dataStais> stobj = EsCommon.FromJSON<Root<Mem_dataStais>>(sqlTag);
            //屏蔽4位
            stobj.hits.hits.ForEach(x =>
            {
                string MemPhone = getPhoneDesEncrypt(x._id.Trim());
                x._source._id = MemPhone;
                x._source.AConsigneePhone2 = MemPhone.Substring(0, 3)+"****"+ MemPhone.Substring(MemPhone.Length-4, 4);
                x._source.M1 = Convert.ToDouble(x._source.M1)/100;
            });
            List<dynamic> _source = new List<dynamic>();
            _source = stobj.hits.hits.Select(x => x._source).ToList();
            DateTime endTime = Convert.ToDateTime(DateTime.Now.ToLocalTime().ToString());
            _source.ForEach(x =>
            {
                dynamic age = x.age;
               string age1 =JsonConvert.SerializeObject(age);

                if (age1 != null&& !age1.Contains("null"))
                {
                    DateTime startTime = Convert.ToDateTime(age1.Replace("\"", ""));
                    x.age = CalculateAge(startTime, endTime);
                }
                else {
                    x.age = "0";
                }
            });

            string sqlTag2=RedisUtil.getRedisEsUtils(redisKey);//获取缓存
            if (sqlTag2==null || sqlTag2.Length<=0) {//没有缓存，加入缓存
                string F = "F1";
                string M = "M1";
                if (totalStr.Length > 0)
                {
                    F = totalStr + ".RN";
                    M = totalStr + ".RM";
                }
                string msum = ",\"M_COUNT\": {\"cardinality\": {\"field\": \"_id\"}},\"M_COUNTS\": {\"filter\":{ \"range\":{ \"" + F + "\":{\"gte\": \"2\"} }},\"aggs\":{\"M_COUNTS\":{\"cardinality\":{\"field\":\"_id\"}} }}";
                //获取统计数据
                string sumStr = "\"F_SUMS\": {\"filter\":{ \"range\":{ \"" + F + "\":{\"gte\": \"2\"} }},\"aggs\":{\"F_SUMS\":{\"sum\":{\"field\":\"" + F + "\"}} }},\"M_SUMS\": {\"filter\":{ \"range\":{ \"" + F + "\":{\"gte\": \"2\"} }},\"aggs\":{\"M_SUMS\":{\"sum\":{\"field\":\"" + M + "\"}} }}";
                jsonTag = "{" + dslCount + ",\"aggs\": {\"F_SUM\": {\"sum\": {\"field\": \"" + F + "\"}},\"M_SUM\": {\"sum\": {\"field\": \"" + M + "\"}}," + sumStr + msum + "},\"size\": 0}";
                System.Diagnostics.Debug.WriteLine("--" + jsonTag);
                sqlTag2 = Util.ElasticQuery("mem_repurchaseinfo/_search?format=json", "POST", jsonTag);
                RedisUtil.addRedisEsUtils(sqlTag2, redisKey);//加入缓存
            }

            Root<Mem_dataStais> stobj2 = EsCommon.FromJSON<Root<Mem_dataStais>>(sqlTag2);
            
            //总数
            long totalcount = stobj.hits.total.value;
            var jsonData = JsonConvert.SerializeObject(_source);
            var jsonSum= JsonConvert.SerializeObject(stobj2.aggregations);
            dynamic agger = JsonConvert.DeserializeObject(jsonSum);
            dynamic agerTotle = agger.M_COUNT.value;
            string agertl = JsonConvert.SerializeObject(agerTotle);
            if (agertl != null && !agertl.Contains("null"))
            {
                totalcount = Convert.ToInt64(agertl);
            }
            

            return Json(
                new
                {
                    items = JsonConvert.DeserializeObject(jsonData),
                    totalCount = totalcount,
                    jsonSum= JsonConvert.DeserializeObject(jsonSum),
                    _scroll_id = stobj._scroll_id
        });

        }
        //计算年龄
        public static int CalculateAge(DateTime birthDate, DateTime now)
        {
            int age = now.Year - birthDate.Year;
            if (now.Month < birthDate.Month || (now.Month == birthDate.Month && now.Day < birthDate.Day))
            {
                age--;
            }
            if (age >= 100) { age = 0; } else if (age<=0) { age = 0; }
            return age;
        }

        //解密
        public static string getPhoneDesEncrypt(string MemPhone) {
            DataTable dt = new DataTable();
            string result;
            List<SqlParameter> parameters = new List<SqlParameter>();
            bool success = DataAccess.GetValuesV2("select (dbo.DesDecryptFixKey('123456','123456e10adc3949ba59abbe56e057f20f883e','"+ MemPhone + "')) as phone  ", ref dt, parameters.ToArray(), out result);
            if (dt.Rows.Count>0) {
                DataRow dr = dt.Rows[0];
                MemPhone = Convert.ToString(dr["phone"]);
            }
            return MemPhone;
        }

        //加密
        public static string getPhoneEncDesrypt(string MemPhone)
        {
            DataTable dt = new DataTable();
            string result;
            List<SqlParameter> parameters = new List<SqlParameter>();
            bool success = DataAccess.GetValuesV2("select (dbo.DesEncryptFixKey('123456','123456e10adc3949ba59abbe56e057f20f883e','" + MemPhone + "')) as phone  ", ref dt, parameters.ToArray(), out result);
            if (dt.Rows.Count > 0)
            {
                DataRow dr = dt.Rows[0];
                MemPhone = Convert.ToString(dr["phone"]);
            }
            return MemPhone;
        }


        /// <summary>
        /// 会员统计
        /// </summary>
        /// <param name="data"></param>
        /// <param name=""></param>
        /// <returns></returns>
        [HttpGet]
        [Route("memCount")]
        public ActionResult memCount(string tagKey,string zKey,string sexkey) {
            string sqlStr = "";
            string memKey = "redis_mem";//性别缓存Key
            if (tagKey!=null && !tagKey .Equals("0")) {
                List<mem_MemberTag> _sourceTag=ES_UsageMemberController.TagIDList(tagKey);//获取标签会员
                string countstr = "";
                foreach (mem_MemberTag memtag in _sourceTag)
                {
                    countstr += "'"+memtag.MemberPhone + "',";
                    memKey += memtag.MemberPhone;
                }
                sqlStr = sqlStr + " and MemPhone2 IN(" + countstr.Substring(0, countstr.Length - 1) + ")";
            }
            if (zKey!=null && !zKey.Equals("0")) {
                sqlStr = sqlStr + " and OriginType IN(" + zKey + ")";
                memKey += zKey;
            }
            if (sexkey!=null) {
                sqlStr = sqlStr + " and MemberGender ="+ sexkey;
                memKey += sexkey;
            }

            //性别
            var jsonStr = RedisUtil.getRedisEsUtils("2_sex"+memKey);
            if (jsonStr==null|| jsonStr.Length<=0) {//缓存
                var sexStr = "{\"query\":\"" + "select MemberGender,count(MemUsualPhone) as cn from crm_memberbase_phoneid where 1=1 " + sqlStr + " group by MemberGender\"}";
                jsonStr = Util.ElasticQuery("_xpack/sql?format=json", "POST", sexStr);
                RedisUtil.addRedisEsUtils(jsonStr, "2_sex" + memKey);
            }

            //根据地区获取会员
            var str= RedisUtil.getRedisEsUtils("2_are" + memKey);
            if (str==null||str.Length<=0) {//缓存
                var json = "{\"query\":\"" + "select MemUsualProvinceCode,count(MemUsualPhone) as cn from crm_memberbase_phoneid where 1=1 " + sqlStr + " group by MemUsualProvinceCode ORDER BY cn desc\"}";
                str = Util.ElasticQuery("_xpack/sql?format=json", "POST", json);
                RedisUtil.addRedisEsUtils(str, "2_are" + memKey);
            }
            var sexJson = (JObject)JsonConvert.DeserializeObject(jsonStr);
            var jo = (JObject)JsonConvert.DeserializeObject(str);

            //获取省级
            var resultCate = Util.SqlQuery("select* from areamap where Level = 1 ");
            Dictionary<string, string> map = new Dictionary<string, string>();
            var listRow = new List<string[]>();

            foreach (var cate in resultCate.GetValue("rows"))
            {
                if (map.ContainsKey(cate[2].ToString()) == false)
                {
                    map.Add(cate[2].ToString(), cate[0].ToString());
                }
            }
            foreach (var prod in jo.GetValue("rows"))
            {
                var row = new string[3];
                if (map.ContainsKey(prod[0].ToString()))
                {
                    row[2] = prod[0].ToString();
                    row[1] = prod[1].ToString();
                    row[0] = map[prod[0].ToString()];
                    listRow.Add(row);
                }
            }
            var listJson = JsonConvert.SerializeObject(listRow);
            return Json(
                new
                {
                    itmes = JsonConvert.DeserializeObject(listJson),
                    sexjson = sexJson.GetValue("rows")
                }); 

        }
        //获取新增会员统计
        [HttpGet]
        [Route("memDay")]
        public ActionResult memDay(string mDay)
        {          
            DateTime dateTime = DateTime.Parse(DateTime.Now.ToString());
            DateTime date1 = dateTime.AddDays(-1);
            string sqlStr =" and RegisterTime <='" + date1.ToString("yyyy-MM-dd hh:mm:ss") + "'";         
            //指定几天前的时间
            if (mDay != null)
            {              
                DateTime date = dateTime.AddDays(-Convert.ToDouble(mDay));
                sqlStr += " and RegisterTime >='" + date.ToString("yyyy-MM-dd hh:mm:ss") + "'";
            }
            else {
                DateTime date = dateTime.AddDays(-15);
                sqlStr += " and RegisterTime >='" + date.ToString("yyyy-MM-dd hh:mm:ss") + "'";
            }
            var dayStr = "{\"query\":\"" + "select HISTOGRAM(RegisterTime, INTERVAL 1 DAYS) AS h,count(*) as cn from crm_memberbase_phoneid where 1=1 "+sqlStr+" group by h ORDER BY h desc\"}";
            var jsonStr = Util.ElasticQuery("_xpack/sql?format=json", "POST", dayStr);
            var dayJson = (JObject)JsonConvert.DeserializeObject(jsonStr);
            return Json(
           new
            {
               dayJson = dayJson.GetValue("rows")
           });
        }

        [HttpGet]
        [Route("orderProvCount")]
        public ActionResult orderProvCount(string tagKey, string zKey)
        {
            string str1 = "";
            string redisKey = "3_order_prov";
            if (zKey != null)//日期
            {
                if (!zKey.Equals("0"))
                {
                    zKey = "ym" + zKey.Replace("-", "");
                    str1 = "{ \"exists\": {\"field\": \"" + zKey + "\"}}";
                    redisKey += zKey;
                }
            }
            var jsonStr = RedisUtil.getRedisEsUtils(redisKey);
            if (jsonStr == null || jsonStr.Length <= 0)
            {//缓存
                if (str1 != null && str1.Length > 0)
                {
                    str1 = ",\"filter\":" + str1;
                }
                var dayStr = "{\"query\":\"select LEFT(RegionCode,2) AS c, count(distinct id) as cn from mem_repurchaseinfo group by c\"" + str1 + "}";

                jsonStr = Util.ElasticQuery("_xpack/sql?format=json", "POST", dayStr);
                RedisUtil.addRedisEsUtils(jsonStr, redisKey);
            }
            var jo = (JObject)JsonConvert.DeserializeObject(jsonStr);
            //获取省级
            var resultCate = Util.SqlQuery("select* from areamap where Level = 1 ");
            Dictionary<string, string> map = new Dictionary<string, string>();
            var listRow = new List<string[]>();

            foreach (var cate in resultCate.GetValue("rows"))
            {
                if (map.ContainsKey(cate[2].ToString()) == false)
                {
                    map.Add(cate[2].ToString(), cate[0].ToString());
                }
            }
            foreach (var prod in jo.GetValue("rows"))
            {
                var row = new string[3];
                if (map.ContainsKey(prod[0].ToString() + "0000"))
                {
                    row[2] = prod[0].ToString() + "0000";
                    row[1] = prod[1].ToString();
                    row[0] = map[prod[0].ToString() + "0000"];
                    listRow.Add(row);
                }
            }
            var listJson = JsonConvert.SerializeObject(listRow);
            return Json(
                new
                {
                    listJson = JsonConvert.DeserializeObject(listJson)
                });
        }

        /// <summary>
        /// 订单统计
        /// </summary>
        /// <param name="data"></param>
        /// <param name=""></param>
        /// <returns></returns>
        [HttpGet]
        [Route("orderCount")]
        public ActionResult orderCount(string tagKey,string zKey) 
        {

                string str = "";
                string str1 = "";
                string dateStr = "";
            if (tagKey != null)//平台
                    {
                        var key1 = tagKey.Split(",");
                        if (!key1[0].Equals("0"))
                        {
                            foreach (string tag in key1)
                            {
                        str +=  tag + ",";
                            }
                            str = " and OriginType in ("+ str .Substring(0,str.Length-1)+ ") ";

                }
            }
                    if (zKey != null)//日期
                    {
                        if (!zKey.Equals("0"))
                        {
                    var datasp = zKey.Split("-");
                    int dayCount = getSurplusDays(Convert.ToInt32(datasp[0]), Convert.ToInt32(datasp[1]));//本月天数
                    dateStr = " and OrderTime>= '"+ zKey + "-01 00:00:00' and OrderTime<='"+ zKey + "-"+Convert.ToString(dayCount) +" 23:59:59'";
                   
                     }
                    }

            string commandText = " select a.fsm,a.fcn,b.sm,b.cn from ( "+
            " select SUM(Sum/ 100.0) as fsm,toInt64(COUNT(1)) as fcn ,1 as cid from Orders WHERE AConsigneePhone2 global in " +
            " (select AConsigneePhone2 from  Orders WHERE  OrderStatus not in(0, 100, 200) and IsReturn = 0 "+
            " AND RIGHT(OrdersCode,2) <> 'BF' group by AConsigneePhone2 having count(AConsigneePhone2) > 1) "+
            dateStr+ str+
            " ) a FULL JOIN " +
            "(SELECT SUM(Sum / 100) as sm, toInt64(COUNT(1)) as cn, 1 as cid FROM Orders WHERE OrderStatus not in (0, 100, 200) and IsReturn = 0 " + dateStr + str + " )b " +
             "  ON a.cid = b.cid FORMAT JSON ";
            var dugObj = ClickHouseUtil.HttpClickHouseGet(commandText);
            var obj = (JObject)JsonConvert.DeserializeObject(dugObj);
            dynamic data = JsonConvert.DeserializeObject(Convert.ToString(obj.GetValue("data")));
            dynamic sm = data[0].sm;
            string sm1 = JsonConvert.SerializeObject(sm);
            dynamic cn = data[0].cn;
            string cn1 = JsonConvert.SerializeObject(cn).Replace("\"","");
            dynamic fsm = data[0].fsm;
            string fsm1 = JsonConvert.SerializeObject(fsm);
            dynamic fcn = data[0].fcn;
            string fcn1 = JsonConvert.SerializeObject(fcn).Replace("\"", "");
            Object query = new
            {
                sm = sm1,
                cn=cn1,
                fsm=fsm1,
                fcn=fcn1
            };

            //更改es数据

            return Json(
                new
                {
                    jsonSum = query
                });
        }


        /// <summary>
        /// 获取最近销售
        /// </summary>
        /// <param name="mDay"></param>
        /// <returns></returns>
        [HttpGet]
        [Route("orderDay")]
        public ActionResult orderDay(string mDay)
        {
            DateTime dateTime = DateTime.Parse(DateTime.Now.ToString());
            DateTime date = dateTime.AddDays(-16);
            DateTime date1 = dateTime.AddDays(-1);
            //指定几天前的时间
            if (mDay != null)
            {
                date = dateTime.AddDays(-Convert.ToDouble(mDay));
            }
            DataTable dt = new DataTable();
            string result;
            List<SqlParameter> parameters = new List<SqlParameter>();
            IList<order_Count> permss = new List<order_Count>(0);
            //终端
            string commandText3 = " select COUNT(DISTINCT o.OrdersCode) AS orCnt,CONVERT(varchar(100), o.CreationDate, 23) AS orDate,Sum(CONVERT(bigint, p.MarketPrice) *  CONVERT(bigint, p.Amount)) AS orSum FROM Orders o LEFT JOIN OrderProducts p ON p.OrdersCode=o.OrdersCode WHERE" +
                " o.CreationDate>='"+ date.ToString("yyyy-MM-dd") + "' AND o.CreationDate<='" + date1.ToString("yyyy-MM-dd") + "' AND p.CreationDate>='"+ date.ToString("yyyy-MM-dd") + "' AND p.CreationDate<='" + date1.ToString("yyyy-MM-dd") + "' GROUP BY CONVERT(varchar(100), o.CreationDate, 23) order by CONVERT(varchar(100), o.CreationDate, 23)";
            bool success = DataAccess.GetValuesV2(commandText3, ref dt, parameters.ToArray(), out result);
            if (dt != null && dt.Rows.Count > 0)
            {
                permss = ModelConvertHelper<order_Count>.ConvertToModel(dt);
            }
            var jsonSum = JsonConvert.SerializeObject(permss);
            return Json(
                new
                {
                    jsonSum = JsonConvert.DeserializeObject(jsonSum)
                });
        }

        /// <summary>
        /// 复购月份导出
        /// </summary>
        /// <param name="mDay"></param>
        /// <returns></returns>
        [HttpGet]
        [Route("memorderExport")]
        public ActionResult memorderExport([FromServices]IHostingEnvironment env, string staffId)
        {
            IList<Dictionary<string, string>> _sourceCount = new List<Dictionary<string, string>>();
            string sqlEs = "";
            Int32 monthFirstday = Convert.ToInt32(DateTime.Now.ToString("MM"));

            MemoryStream m = new MemoryStream();//创建表
            var wor = new HSSFWorkbook();
            var sheet = wor.CreateSheet();
            var shea = sheet.CreateRow(0);//1行
            int Index = 1;
            int staprice = 0;//金额区间
            int endprice = 1000;
            shea.CreateCell(0).SetCellValue("下单用户/复购用户");

            for (int j = 0; j < 11; j++) {//首行金额
                shea.CreateCell(j + 1).SetCellValue(staprice + "＜￥≤" + endprice);
                staprice += 1000;
                if (staprice >= 10000)
                {
                    endprice += 1000000;
                }
                else
                {
                    endprice += 1000;
                }
            }
            for (int i = 1; i < monthFirstday; i++)
            {
                staprice = 0;//金额区间
                endprice = 1000;
                var newRow = sheet.CreateRow(i);//月度行
                newRow.CreateCell(0).SetCellValue(DateTime.Now.ToString("yyyy") + Convert.ToString(i));//列
                for (int j = 0; j < 11; j++)
                {
                    newRow.CreateCell(j+1).SetCellValue(j);//列
                    string dayStr = Convert.ToString(i);
                    if (dayStr.Length < 2)
                    {
                        dayStr = "0" + dayStr;
                    }
                    sqlEs = "{\"query\":{\"range\":{\"ym" + DateTime.Now.ToString("yyyy") + dayStr + ".RM\":{\"gt\":" + (staprice * 100) + ",\"lte\":" + (endprice * 100) + "}}},\"aggs\":{\"mCount\":{\"cardinality\":{\"field\":\"_id\"}},\"count\":{\"filter\":{\"range\":{\"ym" + DateTime.Now.ToString("yyyy") + dayStr + ".RN\":{\"gte\":\"2\"}}},\"aggs\":{\"fCount\":{\"cardinality\":{\"field\":\"_id\"}}}}},\"size\":0}";
                    staprice += 1000;
                    if (staprice>=10000)
                    {
                        endprice += 1000000;
                    }
                    else
                    {
                        endprice += 1000;
                    }
                    var memstr = Util.ElasticQuery("mem_repurchaseinfo/_search?format=json", "POST", sqlEs);

                    Root<mem_Export> stobj2 = EsCommon.FromJSON<Root<mem_Export>>(memstr);

                    //总数
                    var jsonSum = JsonConvert.SerializeObject(stobj2.aggregations);
                    dynamic agger = JsonConvert.DeserializeObject(jsonSum);
                    dynamic mCount = agger.mCount.value;
                    dynamic fCount = agger.count.doc_count;
                    string mCount1 = JsonConvert.SerializeObject(mCount);
                    string fCount1 = JsonConvert.SerializeObject(fCount);
                    newRow.CreateCell(j+1).SetCellValue(mCount1 + "/" + fCount1);
                }
            }
                wor.Write(m);
                m.Flush();
                m.Position = 0;
                sheet = null;
                shea = null;
                wor = null;
                return File(m, "application/ms-excel", "下单统计"+DateTime.Now.ToString("MMddHHmmss") + ".xls");
        }

        [HttpGet]
        [Route("getPhoneencDes")]
        public string getPhoneencDes(string staffId,string desPhone) {
            if (desPhone!=null) {
                desPhone = getPhoneEncDesrypt(desPhone);
            }
            return desPhone;
        }

        public static int getSurplusDays(int year, int month)
        {
            int days = 0;
            if (month != 2)
            {
                switch (month)
                {
                    case 1:
                    case 3:
                    case 5:
                    case 7:
                    case 8:
                    case 10:
                    case 12:
                        days = 31;
                        break;
                    case 4:
                    case 6:
                    case 9:
                    case 11:
                        days = 30;
                        break;

                }
            }
            else
            {
                // 闰年
                if (year % 4 == 0 && year % 100 != 0 || year % 400 == 0)
                    days = 29;
                else
                    days = 28;
            }
            return days;
        }
    }
}