MemberCenter/Member/MemberWeb/Controllers/ES_Controller.cs

using System;
using System.Collections.Generic;
using System.IO;
using System.Threading;
using System.Threading.Tasks;
using CoreEntity.Entity;
using Elasticsearch.Net;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Nest;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using SupplierWeb.Commonss;
using SupplierWeb.Service;

namespace Elasticsearch.Controllers
{

    public class MyPostData : PostData
    {
        public override void Write(Stream writableStream, IConnectionConfigurationValues settings)
        {
            writableStream.Write(WrittenBytes);
        }

        public override Task WriteAsync(Stream writableStream, IConnectionConfigurationValues settings, CancellationToken cancellationToken)
        {
            return null;
        }
    }

    [Route("web/value")]
    public class ES_Controller : ControllerBase
    {
        private readonly ElasticClient _esclient;
        private readonly ElasticLowLevelClient _LowLevelclient;

        public ES_Controller(IEsClientProvider clientProvider)
        {
            _esclient = clientProvider.GetClient();
            _LowLevelclient = clientProvider.GetLowLevelClient();
        }

        [HttpPost]
        [Route("search")]
        public QuerySqlResponse Seaarch(string sql,string index)//IReadOnlyCollection<Post>
        {
            //var result = _client.Search<Post>(s => s
            //    .Index(index)
            //    .From(0)
            //    .Size(10)
            //    .Query(q => q.Match(m => m.Field(f => f.Type).Query(type)))
            //    ).Documents;

            QuerySqlRequest isql = new QuerySqlRequest();
            isql.Query = sql;
            isql.FetchSize = 10;
            isql.Format = "json"; 

            var result = _esclient.Sql.Query(isql);
             


            return result;
        }

        [HttpPost]
        [Route("searchd")]
        public dynamic SeaarchD([FromBody]dynamic data,string dsl, string index)//IReadOnlyCollection<Post>
        { 
            dsl = JsonConvert.SerializeObject(data.dsl); 
            PostData fromString = dsl;
            var result = _LowLevelclient.Search<StringResponse>("mem_memberbase_p",dsl);
             

            var DynamicObject = JsonConvert.DeserializeObject<dynamic>(result.Body);
            return result.Body;
        }

        [HttpGet]
        [Route("test")]
        public IReadOnlyCollection<mem_MemberBase> Test()
        {

            int pageSize = 10;
            int pageIndex = 1;
            var result = _esclient.Search<mem_MemberBase>(s => s.Index("mem_memberbase_p").Query(
                //q => q.MatchAll()
                q => q
                    .Bool(r => r.Filter(m => m.Term(t => t.Name("MemUsualPhone").Field("MemUsualPhone").Value("CEDB61EEE275213EB4881CC502660EFD")))
                    .Must(r1 => r1.Nested(
                        n => n.Query(q1 => q1.MatchAll()).Path("Terminals").ScoreMode(NestedScoreMode.None).InnerHits(
                                h => h.From(0).Size(1).Source(k => k.Includes( k1=>k1.Field("Terminals.*")))
                    //.DocValueFields(k1 => k1.Field(k2 => k2.Terminals))
                    )
                                )
                            )
                    )

                )
                .Source(k => k.Includes(k1 => k1.Field("Terminals.*").Field("MemUsualPhone")))
                //.StoredFields("_none_")
                .Size(pageSize)
                .From((pageIndex - 1) * pageSize)
                //.Sort(st => st.Descending(d => d.RegisterTime))
                ).Documents;

            return result; 
        }


        [HttpPost]
        [Route("sqllow")]
        public QuerySqlResponse SqlLow([FromBody]dynamic data)
        {
            var json = JsonConvert.SerializeObject(data);


            //var post = new MyPostData();
            //post.Write(new System.IO.MemoryStream(System.Text.Encoding.Default.GetBytes(data)), _LowLevelclient.Settings);
            var result = _LowLevelclient.Sql.Query<QuerySqlResponse>(PostData.String(json));
            return result;
        }

        [HttpPost]
        [Route("sql")]
        public ActionResult Sql([FromBody]dynamic data)
        {
            var json = JsonConvert.SerializeObject(data);
            var str = Util.ElasticQuery("_sql?format=json", "POST", json);
            var result = new JSONNetResult((JObject)JsonConvert.DeserializeObject(str));

            return result;
        }


        [HttpPost]
        [Route("sqlQuery")]
        public ActionResult SqlQuery(string query)
        {
            var json = "{\"query\":\"" + query +
                "\"}";
            var str = Util.ElasticQuery("_sql?format=json", "POST", json);
            var result = new JSONNetResult((JObject)JsonConvert.DeserializeObject(str));
            return result;
        }


        [HttpPost]
        [Route("productClass")]
        public List<string[]> productClass()
        {
            var json = "{\"query\":\"" + "select SUBSTRING(ClassCode,0,2),count(1) from product_parent group by SUBSTRING(ClassCode,0,2)" +
                "\"}";
            var str = Util.ElasticQuery("_sql?format=json", "POST", json);
            var jo = (JObject)JsonConvert.DeserializeObject(str);

            var resultCate = Util.SqlQuery("select * from productcategory ");
            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[3].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 result = new JSONNetResult(jo);
            return listRow;
        }

        [HttpPost]
        [Route("memberProvince")]
        public List<string[]> memberProvince()
        {
            var json = "{\"query\":\"" + "select MemUsualProvinceCode,count(distinct MemUsualPhone) from mem_memberbase group by MemUsualProvinceCode " +
                "\"}";
            var str = Util.ElasticQuery("_sql?format=json", "POST", json);
            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 result = new JSONNetResult(jo);
            return listRow;
        }


        [HttpPost]
        [Route("memberRegistYM")]
        public ActionResult memberRegistYM()
        {
            var json = "{\"query\":\"" + "select CONCAT(CONVERT(year(RegisterTime),text),CONVERT(month(RegisterTime),text)) as YearMonth,count(1) " +
                " from mem_memberbase group by CONCAT(CONVERT(year(RegisterTime),text),CONVERT(month(RegisterTime),text)) " +
                "\"}";
            var str = Util.ElasticQuery("_sql?format=json", "POST", json);
            var result = new JSONNetResult((JObject)JsonConvert.DeserializeObject(str));
            return result;
        }


        [HttpPost]
        [Route("memberRegistY")]
        public ActionResult memberRegistY()
        {
            var json = "{\"query\":\"" + "select year(RegisterTime) as YearMonth,count(1) from mem_memberbase group by year(RegisterTime)" +
                "\"}";
            var str = Util.ElasticQuery("_sql?format=json", "POST", json);
            var result = new JSONNetResult((JObject)JsonConvert.DeserializeObject(str));
            return result;
        }

    }
}