MemberCenter/Member/MemberWeb/Controllers/ES_MemproductController.cs

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

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

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

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

        [HttpPost]
        [Route("index")]
        public ActionResult index([FromBody]dynamic data, QueryFilter[] filters)
        {
            int start = 0;
            string field = "";
            string fieldvalue = "";
            if (data != null)
            {
                if (data.pageIndex == 1)
                {
                    start = 0;
                }
                else
                {
                    start = ((data.pageIndex - 1) * data.pageSize) + 1;
                }
                if (data.filters.Count != 0)
                {
                    field = data.filters[0].field;
                    fieldvalue = data.filters[0].value;
                    data = new { query = "select * from mem_product1 where " + field + " like  '%" + fieldvalue + "%' limit " + data.pageSize };
                }
                else
                {
                    data = new { query = "select * from mem_product1 a limit " + data.pageSize };
                }
            }
            //将参数转换成JSON数据
            var json = JsonConvert.SerializeObject(data);
            //通过传来的SQL语句，转换成DSL语句
            string sql = Util.ElasticQuery("_sql/translate?format=json", "POST", json);
            string from = "\"from\":" + start + ",";
            sql = sql.Insert(1, from);
            //通过转换的DSL语句，获取数据
            var str = Util.ElasticQuery("mem_product1/_search?format=json", "POST", sql);
            #region

            //dynamic dyn = new System.Dynamic.ExpandoObject();
            //var obj = Newtonsoft.Json.JsonConvert.DeserializeAnonymousType(str, new
            //{
            //    hits = new
            //    {
            //        hits = new[]
            //      {
            //        new {
            //            _id =string.Empty,
            //            _source = new
            //            {
            //                Manufacturer =String.Empty,

            //            },
            //            fields = dyn

            //        }
            //    }
            //    }

            //});

            //foreach(var item in obj.hits.hits)
            //{

            //}
            #endregion
            //JSON转Root对象
            Root stobj = FromJSON<Root>(str);
            //将Root对象中的子对象取出来
            List<_source> _source = new List<_source>();
            _source = stobj.hits.hits.Select(x => x._source).ToList();
            List<Fields> Fields = new List<Fields>();
            Fields = stobj.hits.hits.Select(x => x.fields).ToList();
            //给filed额外的属性赋值
            stobj.hits.hits.ForEach(x =>
            {
                x.fields._id = x._id;
                x.fields.Manufacturer = x._source.Manufacturer;
            });
            //总数
            long totalcount = 0;
            data = new { query = "select count(1) from mem_product1" };
            var jsons = JsonConvert.SerializeObject(data);
            string obj = Util.ElasticQuery("_sql?format=json", "POST", jsons);
            Num num = FromJSON<Num>(obj);
            totalcount =num.rows[0][0];
            var jsonData = JsonConvert.SerializeObject(Fields);
            return Json(
                new
                {
                    items = JsonConvert.DeserializeObject(jsonData),
                    totalCount = totalcount
                }); 

        }





        /// <summary>
        /// JSON转对象
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="input"></param>
        /// <returns></returns>
        public static T FromJSON<T>(string input)
        {
            try
            {
                return JsonConvert.DeserializeObject<T>(input);
            }
            catch (Exception ex)
            {
                string a = ex.Message;
                return default(T);
            }
        }



        #region 实体类
        public class ColumnsItem
        {
            /// <summary>
            /// 
            /// </summary>
            public string name { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public string type { get; set; }
        }

        public class Num
        {
            /// <summary>
            /// 
            /// </summary>
            public List<ColumnsItem> columns { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<List<int>> rows { get; set; }
        }

        public class Filters
        {

            /// <summary>
            /// 
            /// </summary>
            public int field { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public int value { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public int operate { get; set; }

        }
        public class _shards
        {
            /// <summary>
            /// 
            /// </summary>
            public int total { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public int successful { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public int skipped { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public int failed { get; set; }
        }

        public class Total
        {
            /// <summary>
            /// 
            /// </summary>
            public int value { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public string relation { get; set; }
        }

        public class _source
        {
            /// <summary>
            /// 
            /// </summary>
            public string ProudctDescription { get; set; }
            /// <summary>
            /// 如与其他药物同时使用可能会发生药物相互作用，详情请咨询医师或药师。
            /// </summary>
            public string DrugInteractions { get; set; }
            /// <summary>
            /// 药用PVC硬片，药品包装用PTP铝箔。10粒x2板/盒。
            /// </summary>
            public string PackingProduct { get; set; }
            /// <summary>
            /// 辅仁 糖尿乐胶囊 0.3g*20粒
            /// </summary>
            public string ProductName { get; set; }
            /// <summary>
            /// 贪食症 糖尿病
            /// </summary>
            public string ProductTag { get; set; }
            /// <summary>
            /// 本品为胶囊剂，内容物为灰褐色粉末；味辛、微苦。
            /// </summary>
            public string Characters { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public string Manual { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public string PregnantWoman { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public string Pharmaco { get; set; }
            /// <summary>
            /// 口服，一次3—4粒，一日3次。
            /// </summary>
            public string Dosage { get; set; }
            /// <summary>
            /// 部颁标准中药成方制剂第十三册
            /// </summary>
            public string Standards { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public string OlderPatients { get; set; }
            /// <summary>
            /// 密封。
            /// </summary>
            public string Storage { get; set; }
            /// <summary>
            /// 滋阴补肾，益气润肺，和胃生津，调节代谢机能。用于消渴症引起的多食、多饮、多尿、四肢无力等症，降低血糖、尿糖。
            /// </summary>
            public string Introduction { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public string ChemicalName { get; set; }
            /// <summary>
            /// 河南辅仁堂制药有限公司
            /// </summary>
            public string Manufacturer { get; set; }
            /// <summary>
            /// 尚不明确。
            /// </summary>
            public string Taboo { get; set; }
            /// <summary>
            /// 尚不明确。
            /// </summary>
            public string Adverse { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public string Overdose { get; set; }
            /// <summary>
            /// 严忌含糖食物及烟酒。
            /// </summary>
            public string Precautions { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public string Pharmacokinetics { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public string Child { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public string EnglishName { get; set; }
        }

        public class Fields
        {
            /// <summary>
            /// 
            /// </summary>
            public string _id { get; set; }

            /// <summary>
            /// 
            /// </summary>
            public string Manufacturer { get; set; }

            /// <summary>
            /// 
            /// </summary>
            public List<string> CreationDate { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<string> SubProductLine { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<string> IsRecommend { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<string> FinancialType { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<string> LastModified { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<string> WapManual { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<string> GmpCode { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<int> AppreciationPrice { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<string> Packing { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<string> ProductImageUrl { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<string> IsLimitPrice { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<string> ToErp { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<string> CommonTitle { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<int> recordState { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<string> ProductLine { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<string> CommonTitlePinyin { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<int> MerchantManageCode { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<int> PackingNumber { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<string> ProductType { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<string> IsControl { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<string> PinyinFullCode { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<int> ControlNumber { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<string> OutOfStockRecommendCodes { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<string> PostageLogo { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<int> ManufacturerCode { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<int> ProductCodeForOutOfStock { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<int> PlaceCode { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<string> ApprovalNumber { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<int> PurchasePrice { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<int> CategoryType { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<int> MassDate { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<string> ProductAttribute { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<string> DivisionCode { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<string> Molecular { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<string> ProductRecommend { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<string> GiftCategory { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<string> Structure { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<int> HealthInsuranceType { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<string> IsDrugRecord { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<string> ShowMall { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<int> ConsumeDays { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<int> PlureProductStatusType { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<string> IsSuit { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<int> CheckPendingPrice { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<string> IsVisible { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<int> UsageCode { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<string> ThumbnailUrl { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<string> Productusecrowd { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<string> BtManual { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<int> BrandCode { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<string> ProductusecrowdCode { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<string> SFDAGmpCertificateDate { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<string> Unit { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<string> Formula { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<int> ProductHotType { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<string> Formulation { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<int> Views { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<string> PinyinCode { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<int> OurPrice { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<string> Img180 { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<string> Inspection { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<string> SyncStatus { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<string> SFDACode { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<int> Weight { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<string> Img100 { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<string> GmpCertificateUrl { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<string> ClassCode { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<int> MinMarketPrice { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<string> StandardCode { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<int> BranchCode { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<int> PriceSectorPricing { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<int> FormulationCode { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<int> CQuantity { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<string> BarCode { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<string> ExternalID { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<string> Productmainmaterial { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<string> BusinessIds { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<string> ControlAreaRegion { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<int> XQuantity { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<int> PrescriptionType { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<int> QcClass { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<string> ProfitLevel { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<string> PurchaseWhile { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<string> SuitDescription { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<int> MarketPrice { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<int> ProductStatusType { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<int> SFDAStatus { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<string> Composition { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<string> UserName { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<string> IsGifts { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<int> Quantity { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<string> Img320 { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<string> SFDAApprovalValidDate { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<string> Displaytab { get; set; }
        }

        public class HitsItem
        {
            /// <summary>
            /// 
            /// </summary>
            public string _index { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public string _type { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public string _id { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public string _score { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public _source _source { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public Fields fields { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<int> sort { get; set; }
        }

        public class Hits
        {
            /// <summary>
            /// 
            /// </summary>
            public Total total { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public string max_score { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public List<HitsItem> hits { get; set; }
        }

        public class Root
        {
            /// <summary>
            /// 
            /// </summary>
            public int took { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public string timed_out { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public _shards _shards { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public Hits hits { get; set; }
        }

        #endregion
    }
}