MemberCenter/Member/MemberWeb/Controllers/mem_orderproductsController.cs

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Threading.Tasks;
using CoreEntity.Entity;
using CoreEntity.ESEntity;
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/mem_orderproducts")]
    public class mem_orderproductsController : BaseController
    {

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

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

        /// <summary>
        /// all订单产品列表
        /// </summary>
        /// <param name="data"></param>
        /// <param name="filters"></param>
        /// <returns></returns>
        [HttpPost]
        [Route("OrderproductsList")]
        public ActionResult OrderproductsList([FromBody]dynamic data, QueryFilter[] filters)
        {
            int start = 0;
            string filter = "";
            Object query = null;
            if (data != null)
            {
                filters = data.filters.ToObject<QueryFilter[]>();
                filter = QueryFilter.getFilterString(filters);
                if (data.pageIndex == 1)
                {
                    start = 0;
                }
                else
                {
                    start = ((data.pageIndex - 1) * data.pageSize) + 1;
                }
                //根据会员电话号码，先找到他的所有订单
                //找到属于处方的订单
                query = new { query = "select * from mem_orderproducts where 1=1"+
                    filter +
                    " order by CreationDate " +
                    "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);
            //通过转换的DSL语句，获取数据
            var str = Util.ElasticQuery("mem_orderproducts/_search?format=json", "POST", sql);
            //JSON转Root对象
            Root<OrderProducts> stobj = EsCommon.FromJSON<Root<OrderProducts>>(str);

            //总数
            List<OrderProducts> _source = new List<OrderProducts>();
            long totalcount = 0;
            if (stobj.hits.hits.Count > 0) {
                //给filed额外的属性赋值
                stobj.hits.hits.ForEach(x =>
                {                
                    x._source._id = x._id;
                    //x._source.ProductCode = x.fields.ProductCode[0].ToString();

                    x.fields.ActualPrice[0] = x.fields.ActualPrice[0] > 0 ?
                    x.fields.ActualPrice[0] / 100 : 0;
                
                });
                //将Root对象中的子对象取出来
                _source = stobj.hits.hits.Select(x => x.entity()).ToList();

                var countdata = new { query = "select count(1) from mem_orderproducts  where 1=1"+
                    filter
                };
                var jsons = JsonConvert.SerializeObject(countdata);
                //ES获取SQL执行结果
                string obj = Util.ElasticQuery("_sql?format=json", "POST", jsons);
                Num num = FromJSON<Num>(obj);
                totalcount = num.rows[0][0];
            }
            var jsonData = JsonConvert.SerializeObject(_source);
            return Json(
                new
                {
                    items = JsonConvert.DeserializeObject(jsonData),
                    totalCount = totalcount
                });

        }



        /// <summary>
        /// 订单产品列表ByOderCode
        /// </summary>
        /// <param name="data"></param>
        /// <param name="filters"></param>
        /// <returns></returns>
        [HttpPost]
        [Route("ProductsListByOderCode")]
        public ActionResult ProductsListByOderCode([FromBody]dynamic data, QueryFilter[] filters)
        {
            int start = 0;
            //string OrdersCode = "JD84666969985"; 
            string OrdersCode = "";
            if (data != null)
            {
                OrdersCode = data.query[0];
                data = new { query = "select * from mem_orderproducts where   OrdersCode='" + OrdersCode + "' " };
            }
            //将参数转换成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_orderproducts/_search?format=json", "POST", sql);
            //JSON转Root对象
            Root<OrderProducts> stobj = FromJSON<Root<OrderProducts>>(str);
            //List<Fields> Fields = new List<Fields>();
            //Fields = stobj.hits.hits.Select(x => x.fields).ToList();
            //给filed额外的属性赋值
            stobj.hits.hits.ForEach(x =>
            {
                x._source._id = x._id;
                //x._source.ProductCode = x.fields.ProductCode[0].ToString();
                x.fields.ActualPrice[0] = x.fields.ActualPrice[0] > 0 ?
                x.fields.ActualPrice[0] / 100 : 0;

            });
            //将Root对象中的子对象取出来
            List<OrderProducts> _source = new List<OrderProducts>();
            _source = stobj.hits.hits.Select(x => x.entity()).ToList();
            //总数
            long totalcount = 0;
            data = new { query = "select count(1) from mem_orderproducts  where  OrdersCode='"+ OrdersCode + "'" };
            var jsons = JsonConvert.SerializeObject(data);
            //ES获取SQL执行结果
            string obj = Util.ElasticQuery("_sql?format=json", "POST", jsons);
            Num num = FromJSON<Num>(obj);
            totalcount = num.rows[0][0];
            var jsonData = JsonConvert.SerializeObject(_source);
            return Json(
                new
                {
                    items = JsonConvert.DeserializeObject(jsonData),
                    totalCount = totalcount,
                    success = true
                });

        }


        /// <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 _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
        //{
        //    public string ProductCode { get; set; }
        //    /// <summary>
        //    /// 
        //    /// </summary>
        //    public string CreationDate { get; set; }
        //    /// <summary>
        //    /// 仁和 痔速宁片 0.31g*12片*3板
        //    /// </summary>
        //    public string ProductName { get; set; }
        //    /// <summary>
        //    /// 
        //    /// </summary>
        //    public string ProductPromotionSet { get; set; }
        //    /// <summary>
        //    /// 
        //    /// </summary>
        //    public string AssociateProudctCode { get; set; }
        //    /// <summary>
        //    /// 盒
        //    /// </summary>
        //    public string Unit { get; set; }
        //    /// <summary>
        //    /// 
        //    /// </summary>
        //    public string ProfitLevel { get; set; }
        //    /// <summary>
        //    /// 
        //    /// </summary>
        //    public string LastModified { get; set; }
        //    /// <summary>
        //    /// 0.31g*12片*3板（仁和）
        //    /// </summary>
        //    public string Packing { get; set; }
        //    /// <summary>
        //    /// 
        //    /// </summary>
        //    public string Batch { get; set; }
        //    /// <summary>
        //    /// 
        //    /// </summary>
        //    public string Picture { get; set; }
        //    /// <summary>
        //    /// 
        //    /// </summary>
        //    public string Barcode { get; set; }
        //    /// <summary>
        //    /// 江西药都仁和制药有限公司
        //    /// </summary>
        //    public string Manufacturer { get; set; }
        //    /// <summary>
        //    /// 
        //    /// </summary>
        //    public string OrdersCode { get; set; }
        //    /// <summary>
        //    /// 
        //    /// </summary>
        //    public string DamagedRemark { get; set; }
        //    /// <summary>
        //    /// 
        //    /// </summary>
        //    public string MemberPhone { get; set; }
        //    /// <summary>
        //    /// 
        //    /// </summary>
        //    public string Id { get; set; }
        //}

        //public class Fields
        //{
        //    /// <summary>
        //    /// 
        //    /// </summary>
        //    public List<int> ActualPrice { get; set; }
        //    /// <summary>
        //    /// 
        //    /// </summary>
        //    public List<int> AvgDiscount { get; set; }
        //    /// <summary>
        //    /// 
        //    /// </summary>
        //    public List<int> MarketPrice { get; set; }
        //    /// <summary>
        //    /// 
        //    /// </summary>
        //    public List<int> ProductCode { get; set; }
        //    /// <summary>
        //    /// 
        //    /// </summary>
        //    public List<int> AvgFreight { get; set; }
        //    /// <summary>
        //    /// 
        //    /// </summary>
        //    public List<int> OurPrice { get; set; }
        //    /// <summary>
        //    /// 
        //    /// </summary>
        //    public List<int> Amount { get; set; }
        //    /// <summary>
        //    /// 
        //    /// </summary>
        //    public List<string> IntegralAmount { get; set; }
        //    /// <summary>
        //    /// 
        //    /// </summary>
        //    public List<int> PurchasePrice { 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 string _routing { 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

        //#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; }
        //}
        //#endregion
        
    }
}