MemberCenter/Member/MemberWeb/Controllers/ES_MemberController.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Common.Model;
using CoreEntity.DAL;
using CoreEntity.Entity;
using CoreEntity.ESEntity;
using JCSoft.WX.Framework.Api;
using MemberWeb.Commonss;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Caching.Memory;
using Newtonsoft.Json;
using Newtonsoft.Json.Converters;
using Newtonsoft.Json.Linq;
using PublicLibrary.Model;
using SupplierWeb.Commonss;
using ZcPeng.PublicLibrary;

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

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

        }

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

        /// <summary>
        /// 会员中心列表
        /// </summary>
        /// <param name="data"></param>
        /// <param name="filters"></param>
        /// <returns></returns>
        [HttpPost]
        [Route("index")]
        public ActionResult index([FromBody]dynamic data, QueryFilter[] filters)
        {
            int start = 0;
            string filter = "";
            Object query = null;
            List<String> phonesSearch = new List<String>(1);
            Dictionary<string, List<QueryFilter>> fdic = new Dictionary<string, List<QueryFilter>>();
            if (data != null)
            {
                filters = data.filters.ToObject<QueryFilter[]>();
                if(filters !=null && filters.Count() > 0)
                {
                    var j = 0;
                    foreach(QueryFilter f in filters)
                    {
                        f.Index = j;
                        if (f.field.Equals("MemUsualPhone")&&f.value!=null&&f.value!="null")
                        {
                            phonesSearch.Add(f.value);
                            var phoneCr = CommonDAL.getPhoneEncrypt(phonesSearch.ToArray<String>());
                            f.value = phoneCr[0];
                        }

                        if(f.field.IndexOf(".")>0 && f.value != null && f.value != null && f.value != "null")
                        { 
                            List<QueryFilter> listF = null;
                            fdic.TryGetValue(f.field,out listF);
                            if (listF == null)
                                listF = new List<QueryFilter>();
                            else
                                filters[j] = null;//空字段

                            if (f.rules != null && f.rules.Count() > 0)
                            {
                                if (f.rules[0].value != "")
                                    listF.Add(f);
                            }
                            fdic[f.field] = listF;
                        }
                        j++;
                    }
                }
                filter = QueryFilter.getFilterString(filters);

                if (data.pageIndex == 1)
                {
                    start = 0;
                }
                else
                {
                    start = ((data.pageIndex - 1) * data.pageSize) + 1;
                }
                query = new { query = "select * from mem_memberbase_p where 1=1 " +
                        filter +
                        " order by RegisterTime desc " +
                        " limit " + data.pageSize };
            }
            //将参数转换成JSON数据
            var json = JsonConvert.SerializeObject(query);
            //通过传来的SQL语句，转换成DSL语句
            string sql = Util.ElasticQuery("_sql/translate?format=json", "POST", json);
            string from = "\"from\":" + start + ",";
            sql = sql.Insert(1, from);

            int sourceIndex = sql.IndexOf("\"_source\":{");
            int sourceIndexEnd = sql.IndexOf("},", sourceIndex);
            string sourcestr = sql.Substring(sourceIndex, sourceIndexEnd - sourceIndex + 1);
            sql = sql.Replace(sourcestr, "\"_source\":{\"includes\":[\"*\"], \"excludes\": []}");

            int fieldIndex = sql.IndexOf("\"docvalue_fields\":[");
            int fieldIndexEnd = sql.IndexOf("],", fieldIndex);
            sql = sql.Remove(fieldIndex, fieldIndexEnd - fieldIndex + 2);

            if (fdic.Count > 0)
            {
                foreach(var di in fdic)
                {
                    if(fdic[di.Key]!=null && fdic[di.Key].Count > 0)
                    {
                        
                        string[] values = new string[fdic[di.Key].Count];
                        int k = 0;
                        foreach(var qfilter in fdic[di.Key])
                        {
                            if (qfilter.rules != null && qfilter.rules.Count() > 0)
                            {
                                if(qfilter.rules[0].value!="")
                                values[k] = qfilter.rules[0].value;
                            }
                            else
                            {
                                if (qfilter.value != "")
                                    values[k] = qfilter.value;
                            } 
                            k++;
                        }
                        //miniMatch
                        StringBuilder nestMinMatch = new StringBuilder(5);
                        nestMinMatch.Append(@"{ ""bool"": { ""should"": [");
                        foreach (var value in values) { 
                            var valuestr = "[" + string.Join(",", values) +"]";
                            var keytoken = di.Key.Split(".");
                            var path = keytoken[0];
                            //                    string tnest = @"{
                            //    ""nested"": {
                            //        ""query"": {
                            //            ""bool"":{
                            //                ""must"":[   
                            //                    {                
                            //                         ""script"": { 
                            //                          ""script"": ""def match = false; for (search_array in  [10,71] ) { for (sub_array in doc['Terminals.TerminalCode']){ match =   ( search_array == sub_array);if( search_array == sub_array){break;} }} return match;""
                            //                       }
                            //                    }
                            //                ]
                            //            }
                            //        },
                            //        ""path"": ""Terminals"",
                            //        ""ignore_unmapped"": false,
                            //        ""score_mode"": ""none"",
                            //        ""boost"": 1.0
                            //    }

                            //tnest = tnest.Replace("[10,71]", valuestr);
                            //}";
                            string tnest =
                    @"{
                        ""nested"": { 
                            ""query"": {
                                    ""bool"": {
                                        ""must"": [
                                        {
                                            ""term"": {  
                                                 ""Terminals.TerminalCode"": {
                                                    ""value"":""6"" 
                                                 }
                                            }
                                        }
                                        ]
                            }
                        },
                        ""path"": ""Terminals"",
                        ""ignore_unmapped"": false,
                        ""score_mode"": ""none"",
                        ""boost"": 1.0
                        }
                    },";
                            tnest = tnest.Replace(":\"6\"", ":\"" + value+ "\"");
                            tnest = tnest.Replace("Terminals.TerminalCode", di.Key);
                            tnest = tnest.Replace("Terminals", path);
                            nestMinMatch.Append(tnest);
                        }
                        nestMinMatch = nestMinMatch.Remove(nestMinMatch.Length-1,1);
                        nestMinMatch.Append(@"],""minimum_should_match"": "+ values .Length+ "");
                        nestMinMatch.Append(@"}}" );

                        string snest = "{\"nested\":{\"query\":{\"term\":{\"Terminals.MemSourcePlatforms\":{\"value\":\"2\",\"boost\":1.0}}},\"path\":\"Terminals\",\"ignore_unmapped\":false,\"score_mode\":\"none\",\"boost\":1.0}}";
                        var sValue = "";
                        if (fdic[di.Key][0].rules!=null&& fdic[di.Key][0].rules.Count()>0)
                        {
                            sValue = fdic[di.Key][0].rules[0].value;
                        }
                        else
                        { 
                            sValue = fdic[di.Key][0].value;
                        }
                        sql = sql.Replace(snest.Replace("Terminals.MemSourcePlatforms", di.Key).Replace("\"2\"", "\""+ sValue + "\""), nestMinMatch.ToString());


                    }
                }
            }
            //通过转换的DSL语句，获取数据
            var str = Util.ElasticQuery("mem_memberbase_p/_search?format=json", "POST", sql);

            //JSON转Root对象
            Root<mem_MemberBase> stobj = EsCommon.FromJSON<Root<mem_MemberBase>>(str);
            //给filed额外的属性赋值
            stobj.hits.hits.ForEach(x =>
            {
                x._source._id = x._id;
            });
            //将Root对象中的子对象取出来
            List<mem_MemberBase> _source = new List<mem_MemberBase>();
            _source = stobj.hits.hits.Select(x => x.entity()).ToList();

            List<String> phones = new List<String>(_source.Count());
            int i = 0;
            _source.ForEach(x =>
            { 
                if(x.MemUsualPhone!=null && x.MemUsualPhone.Length == 32) {
                    phones.Add(x.MemUsualPhone);
                    i++;
                }
            });
            i = 0;
            var phones1 = CommonDAL.getPhoneDecrypt(phones.ToArray<String>());
            _source.ForEach(x =>
            {
                if (x.MemUsualPhone != null && x.MemUsualPhone.Length == 32) {
                    x.MemUsualPhoneCr = x.MemUsualPhone; 
                     
                    string phoneStars = phones1[i];
                    if (phoneStars != null && phoneStars.Length > 8)
                    {
                        string replaced = phoneStars.Substring(3, 4);
                        phoneStars = phoneStars.Replace(replaced, "****");
                    }
                    x.MemUsualPhone = phoneStars;
                    x.MemUsualPhoneOr = phones1[i]; 

                    i++;
                }
            });
            //总数
            long totalcount = 0;
            data = new { query = "select count(1) from mem_memberbase_p where 1=1 " +
                        filter +
                "" };
            var jsons = JsonConvert.SerializeObject(data);
            string obj = Util.ElasticQuery("_sql?format=json", "POST", jsons);
            Num num = EsCommon.FromJSON<Num>(obj);
            totalcount =num.rows[0][0];
            var jsonData = JsonConvert.SerializeObject(_source);
            return Json(
                new
                {
                    items = JsonConvert.DeserializeObject(jsonData),
                    totalCount = totalcount
                }); 

        }
    }
}