MemberCenter/Member/MemberWeb/Controllers/mem_ProductController.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Net.Http.Headers;
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.Converters;
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/memProduct")]
    public class mem_ProductController : BaseController
    {

        private readonly IHostingEnvironment _hostingEnvironment;
        public mem_ProductController(IHostingEnvironment hostingEnvironmen,IMemoryCache cache, IApiClient client) : base(cache, client)
        {
            _hostingEnvironment = hostingEnvironmen;
        }
        public IActionResult Index()
        {
            return View();
        }


        /// <summary>
        /// 产品列表
        /// </summary>
        /// <param name="data"></param>
        /// <param name="filters"></param>
        /// <returns></returns>
        [HttpPost]
        [Route("AllproductList")]
        public ActionResult AllTagList(QueryFilter[] filters, Int32 pageIndex, Int32 pageSize,
            string sortField, Int32 sortDirection, string[] sumFields, [FromBody]dynamic data)
        {

            if (data != null)
            {
                filters = data.filters.ToObject<QueryFilter[]>();
                pageIndex = data.pageIndex;
                pageSize = data.pageSize;
                sortField = data.sortField;
                sortDirection = data.sortDirection;
                sumFields = data.sumFields.ToObject<string[]>();
            }
            DataTable dt = new DataTable();
            string result;
            IList<Mem_Product> permss = new List<Mem_Product>(0);

            List<SqlParameter> parameters = new List<SqlParameter>();
            string filterstr = QueryFilter.getFilterSqlParam(filters, out parameters, new Mem_Product(), "p.");

            string direct = " desc ";
            if (sortDirection != 1)
            {
                direct = " asc";
            }
            if (sortField==null|| sortField=="") {
                sortField = " A.LastModified ";
            }

            int start = (pageIndex - 1) * pageSize;
            int end = (start + 1 + pageSize);

            string commandText0 = "select * from ";
            string commandText1 = "(" +
                "select A.*,row_number() over(  order by " + sortField + direct + "  )  as rownum from (SELECT p.*,t.TypeName FROM mem_ingProduct p LEFT JOIN mem_ingProductType t ON p.ProductType=t.TypeId WHERE p.IsDelete=1 " + filterstr +
                ")  as A   where  1=1 )AAA  ";
            string commandText2 = " where AAA.rownum > "+ start + " and AAA.rownum < "+ end + "  ORDER BY AAA.LastModified desc ";
            string commandText3 = commandText0 + commandText1+commandText2;
            bool success = DataAccess.GetValues(commandText3, ref dt, parameters.ToArray(), out result);


            if (dt != null && dt.Rows.Count > 0)
            {
                // 把DataTable转换为IList<T>  
                permss = ModelConvertHelper<Mem_Product>.ConvertToModel(dt);
            }
            foreach (Mem_Product pd in permss) {
                pd.dt=getProductPic(pd.ProductCode);
            }
            string result1;
            long totalcount = DataAccess.GetRowCountDefine("select count(1) from " + commandText1, parameters.ToArray(), out result1);
            IsoDateTimeConverter timejson = new IsoDateTimeConverter
            {
                DateTimeFormat = "yyyy'-'MM'-'dd' 'HH':'mm':'ss"
            };
            //IList<Menu> menus = Permission.Convert(permss);
            var jsonData = JsonConvert.SerializeObject(permss, timejson);

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

        }


        /// <summary>
        /// 商品等级定价列表
        /// </summary>
        /// <param name="filters"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="sortField"></param>
        /// <param name="sortDirection"></param>
        /// <param name="sumFields"></param>
        /// <param name="data"></param>
        /// <returns></returns>
        [HttpPost]
        [Route("AllproductLevel")]
        public ActionResult AllproductLevel(QueryFilter[] filters, Int32 pageIndex, Int32 pageSize,
            string sortField, Int32 sortDirection, string[] sumFields, [FromBody]dynamic data)
        {

            if (data != null)
            {
                filters = data.filters.ToObject<QueryFilter[]>();
                pageIndex = data.pageIndex;
                pageSize = data.pageSize;
                sortField = data.sortField;
                sortDirection = data.sortDirection;
                sumFields = data.sumFields.ToObject<string[]>();
            }
            DataTable dt = new DataTable();
            string result;
            IList<Mem_Product> permss = new List<Mem_Product>(0);

            List<SqlParameter> parameters = new List<SqlParameter>();
            QueryFilter[] jArray = JsonConvert.DeserializeObject<QueryFilter[]>(data.filters.ToString());
            List<QueryFilter> filterList = new List<QueryFilter>();
            string LevelId = "";
            foreach (QueryFilter filter in jArray)
            {
                if (filter.field == "LevelId")
                    LevelId = filter.value.ToString();
                else
                {
                    filterList.Add(filter);
                }
            }

            //string filterstr = QueryFilter.getFilterSqlParam(filterList.ToArray(), out parameters, new Mem_Product(), "A.");

            string direct = " desc ";
            if (sortDirection != 1)
            {
                direct = " asc";
            }
            if (sortField == null || sortField == "")
            {
                sortField = " A.LastModified ";
            }

            int start = (pageIndex - 1) * pageSize;
            int end = (start + 1 + pageSize);

            string commandText0 = "select * from ";
            string commandText1 = "(" +
                "select A.*,row_number() over(  order by " + sortField + direct + "  )  as rownum from ( "+
                "SELECT p.ProductCode,p.ProductObject,p.ProductName,p.ProductDesc,p.ProductType,p.Manufacturer,p.PurchasePrice,p.Amount,p.State,p.ProductText,p.CreationDate,p.LastModified,t.TypeName,l.MarketIng,l.MarketPrice,l.LevelId from mem_ingProductLevel l " +
                "LEFT JOIN mem_ingProduct p ON l.ProductCode = p.ProductCode LEFT JOIN mem_ingProductType t ON p.ProductType = t.TypeId WHERE p.IsDelete = 1"+
                ")  as A   where  1=1 and A.LevelId='"+ LevelId + "')AAA  ";
            string commandText2 = " where AAA.rownum > " + start + " and AAA.rownum < " + end + "  ORDER BY AAA.LastModified desc ";
            string commandText3 = commandText0 + commandText1 + commandText2;
            bool success = DataAccess.GetValues(commandText3, ref dt, parameters.ToArray(), out result);


            if (dt != null && dt.Rows.Count > 0)
            {
                // 把DataTable转换为IList<T>  
                permss = ModelConvertHelper<Mem_Product>.ConvertToModel(dt);
            }
            foreach (Mem_Product pd in permss)
            {
                pd.dt = getProductPic(pd.ProductCode);
            }
            string result1;
            long totalcount = DataAccess.GetRowCountDefine("select count(1) from " + commandText1, parameters.ToArray(), out result1);
            IsoDateTimeConverter timejson = new IsoDateTimeConverter
            {
                DateTimeFormat = "yyyy'-'MM'-'dd' 'HH':'mm':'ss"
            };
            //IList<Menu> menus = Permission.Convert(permss);
            var jsonData = JsonConvert.SerializeObject(permss, timejson);

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

        }

        [HttpGet]
        [Route("getlevel")]
        public JsonResult getrole()
        {
            var sql = $"SELECT id as value,'0' as parentValue,name as label from mem_user_level";

            DataTable dt = new DataTable();
            var param = new List<SqlParameter>();

            DataAccess.GetValues(sql, ref dt, param.ToArray(), out _);
            IList<mem_userLevel> result = new List<mem_userLevel>();
            if (dt != null && dt.Rows.Count > 0)
            {
                result = ModelConvertHelper<mem_userLevel>.ConvertToModel(dt);
            }
            return Json(new
            {
                result
            });
        }

        /// <summary>
        /// 上传图片
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        [Route("uploadprofile")]
        public JsonResult UploadPrfile()
        {
            var files = Request.Form.Files;

            foreach (var file in files)
            {
                var fileName = ContentDispositionHeaderValue.Parse(file.ContentDisposition).FileName.Trim('"');


                string filePath = _hostingEnvironment.WebRootPath;
                if (!Directory.Exists(filePath + Path.DirectorySeparatorChar + "UploadFiles" + Path.DirectorySeparatorChar))
                {
                    Directory.CreateDirectory(filePath + Path.DirectorySeparatorChar + "UploadFiles" + Path.DirectorySeparatorChar);
                }
                var fileType = Path.GetExtension(fileName).Substring(1);
                fileName = Guid.NewGuid() + Path.GetExtension(fileName);
                var vpath = "/UploadFiles/" + fileName;

                string fileFullName = filePath + vpath;

                using (FileStream fs = System.IO.File.Create(fileFullName))
                {
                    file.CopyTo(fs);
                    fs.Flush();
                }

                return new JsonResult(new
                {
                    success = true,
                    //path = result
                    path = vpath
                });
            }
            return new JsonResult(new
            {
                success = false,
                path = string.Empty
            });
        }




        /// <summary>
        /// 修改添加商品
        /// </summary>
        /// <param name="data"></param>
        /// <param name="staffId"></param>
        /// <returns></returns>
        [HttpPost]
        [Route("addMemProduct")]
        public JsonResult addMemProduct( [FromBody]dynamic data, string staffId)
        {
            string accountId = getStaff(staffId, "userid");
            List<Dictionary<string, string>> list = new List<Dictionary<string, string>>();
            Mem_Product product = new Mem_Product();
            //判断参数是否合法
            if (string.IsNullOrEmpty(accountId))
            {
                return Json(new
                {
                    timeout = false,
                    msg = "没有登陆"
                });
            }
            string productCode = Convert.ToString(Guid.NewGuid());

            if (data != null)
            {
                product.MarketPrice = data.MarketPrice;//默认价格
                product.MarketIng = data.MarketIng;//默认积分
                product.ProductCode = data.ProductCode;//商品编号
                product.ProductName = data.ProductName;//商品名称
                product.ProductObject = data.ProductObject;//产品属性
                product.ProductDesc = data.ProductDesc;//产品概要
                product.Manufacturer = data.Mnufacturer;//供应商
                product.PurchasePrice = data.PurchasePrice;//成本价
                product.Amount = data.Amount;//库存
                product.ProductText = data.ProductText;//详细
            }
            int success = 0;
            string msg = "";
            //查询商品是否存在
            string result1;
            long totalcount = -1;
            if (product.ProductCode != null && product.ProductCode!="") {
                List<SqlParameter> parameters = new List<SqlParameter>();
                totalcount = DataAccess.GetRowCountDefine("select count(1) from mem_ingProduct where ProductCode='"+ product.ProductCode + "'", parameters.ToArray(), out result1);
            }
            string logStr = "";
            if (totalcount<=0)
            {
                //添加
                var sql = "INSERT INTO mem_ingProduct(ProductCode,ProductObject,ProductName,ProductDesc,Manufacturer,PurchasePrice,Amount,ProductText,MarketPrice,MarketIng,CreationDate) Values(" +
                   "@ProductCode,@ProductObject,@ProductName,@ProductDesc,@Manufacturer,@PurchasePrice,@Amount,@ProductText,@MarketPrice,@MarketIng,@CreationDate)";
                var param = new List<SqlParameter>();
                product.ProductCode = productCode;
                param.Add(new SqlParameter("ProductCode", productCode));
                param.Add(new SqlParameter("ProductObject", product.ProductObject));
                param.Add(new SqlParameter("ProductName", product.ProductName));
                param.Add(new SqlParameter("ProductDesc", product.ProductDesc));
                param.Add(new SqlParameter("Manufacturer", product.Manufacturer));
                param.Add(new SqlParameter("PurchasePrice", product.PurchasePrice));
                param.Add(new SqlParameter("Amount", product.Amount));
                param.Add(new SqlParameter("ProductText", product.ProductText));
                param.Add(new SqlParameter("MarketPrice", product.MarketPrice));
                param.Add(new SqlParameter("MarketIng", product.MarketIng));

                param.Add(new SqlParameter("CreationDate", DateTime.Now));
                success = DataAccess.ExecuteCommand(sql, param, out msg);
                logStr = "添加商品："+ product.ProductName;
            }
            else
            {               
                string sql = "UPDATE mem_ingProduct  SET  ProductObject=@ProductObject,ProductName=@ProductName,ProductDesc=@ProductDesc,Manufacturer=@Manufacturer ,PurchasePrice=@PurchasePrice,Amount=@Amount,ProductText=@ProductText,MarketPrice=@MarketPrice,MarketIng=@MarketIng,LastModified=@LastModified WHERE ProductCode = @ProductCode";
                //准备参数
                List<List<Object>> parametersC = new List<List<Object>>();
                parametersC.Add(new List<Object>() { "ProductObject", product.ProductObject });
                parametersC.Add(new List<Object>() { "ProductName", product.ProductName });
                parametersC.Add(new List<Object>() { "ProductDesc", product.ProductDesc });
                parametersC.Add(new List<Object>() { "Manufacturer", product.Manufacturer });
                parametersC.Add(new List<Object>() { "PurchasePrice", product.PurchasePrice });
                parametersC.Add(new List<Object>() { "Amount", product.Amount });
                parametersC.Add(new List<Object>() { "ProductText", product.ProductText });
                parametersC.Add(new List<Object>() { "MarketPrice", product.MarketPrice });
                parametersC.Add(new List<Object>() { "MarketIng", product.MarketIng });
                parametersC.Add(new List<Object>() { "LastModified", DateTime.Now });
                parametersC.Add(new List<Object>() { "ProductCode", product.ProductCode });
                List<SqlParameter> parametersC1 = DataAccess.ToParameters(parametersC);
                success = DataAccess.ExecuteCommand(sql, parametersC1, out msg);
                logStr = "修改商品：" + product.ProductName;
            }
            if (success>0) {
                //商品图片
                string fileimg = JsonConvert.SerializeObject(data.fileImg);
                addProductPic(product.ProductCode,fileimg);
                //记录日志
                addProductLog(accountId,"1", logStr, product.ProductCode);           
            }
            return Json(new
            {
                success = success,
                msg = msg
            });
        }

        /// <summary>
        /// 上下架
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        [HttpPost]
        [Route("updaState")]
        public JsonResult updaState([FromBody]dynamic data) {
            string msg = "";
            if (data!=null && data.State!=null && data.ProductCode!=null) {
                string sql = "UPDATE mem_ingProduct  SET State='"+ data.State + "' WHERE ProductCode = '" + data.ProductCode + "'";
                //准备参数
                List<List<Object>> parametersC = new List<List<Object>>();
                List<SqlParameter> parametersC1 = DataAccess.ToParameters(parametersC);
                int success = DataAccess.ExecuteCommand(sql, parametersC1, out msg);
            }
            return Json(new
            {
                obj = msg
            });
        }
        /// <summary>
        /// 获取商品日志
        /// </summary>
        /// <param name="data"></param>
        /// <param name="staffId"></param>
        /// <returns></returns>
        [HttpPost]
        [Route("getProductLog")]
        public JsonResult getProductLog([FromBody]dynamic data, string staffId)
        {

            string accountId = getStaff(staffId, "userid");
            //判断参数是否合法
            if (string.IsNullOrEmpty(accountId))
            {
                return Json(new
                {
                    timeout = false,
                    msg = "没有登陆"
                });
            }
            DataTable dt = new DataTable();
            if (data!=null && data.productCode!=null) {
                List<SqlParameter> parameters = new List<SqlParameter>();
                string commandText = "SELECT l.*,a.AccountRealName FROM mem_ingProductLog l LEFT JOIN mem_Account a ON l.MemberId=a.Id WHERE l.logCode=1 AND l.Value='" + data.productCode + "' ORDER BY CreationDate DESC ";
                string result;
                bool success = DataAccess.GetValues(commandText, ref dt, parameters.ToArray(), out result);
            }
            return Json(new
            {
                obj = dt
            }) ;
        }




        /// <summary>
        /// 获取商品等级价格
        /// </summary>
        /// <param name="data"></param>
        /// <param name="staffId"></param>
        /// <returns></returns>
        [HttpPost]
        [Route("getProductLevel")]
        public JsonResult getProductLevel([FromBody]dynamic data, string staffId)
        {
            string accountId = getStaff(staffId, "userid");
            //判断参数是否合法
            if (string.IsNullOrEmpty(accountId))
            {
                return Json(new
                {
                    timeout = false,
                    msg = "没有登陆"
                });
            }
            DataTable dt = new DataTable();
            if (data != null && data.productCode != null)
            {
                List<SqlParameter> parameters = new List<SqlParameter>();
                string commandText = "SELECT l.name,l.[level],l.id,p.ProductCode,m.MarketIng,m.MarketPrice FROM [mem_user_level] l LEFT JOIN mem_ingProductLevel m ON m.levelId=l.id AND m.ProductCode='"+ data.productCode + "' LEFT JOIN mem_ingProduct p ON p.ProductCode=M.ProductCode";
                string result;
                bool success = DataAccess.GetValues(commandText, ref dt, parameters.ToArray(), out result);
            }
            return Json(new
            {
                obj = dt
            });
        }


        [HttpPost]
        [Route("addMemProductLevel")]
        public JsonResult addMemProductLevel([FromBody]dynamic data, string staffId,string ProductCode,string levelProduct)
        {
            string accountId = getStaff(staffId, "userid");
            List<Dictionary<string, string>> list = new List<Dictionary<string, string>>();
            //判断参数是否合法
            if (string.IsNullOrEmpty(accountId))
            {
                return Json(new
                {
                    timeout = false,
                    msg = "没有登陆"
                });
            }
            int success = 0;
            string msg = "";
            if (data!=null) {
                ProductCode = data.ProductCode;//商品ID
                levelProduct = JsonConvert.SerializeObject(data.levelProduct);//定价
                JArray jArray = JArray.Parse(levelProduct);
                int success1 = 0;
                if (jArray.Count > 0)
                {
                    string imgStr = "";
                    foreach (var jj in jArray)
                    {
                        string PlId = "";
                        JObject job = (JObject)jj;
                        string MarketIng = job["MarketIng"].ToString();
                        string MarketPrice = job["MarketPrice"].ToString();
                        string LevelId = job["id"].ToString();

                        if (MarketIng!=null&& MarketPrice!=null&& MarketIng != "" && MarketPrice != "") {
                            string plId = Convert.ToString(Guid.NewGuid());

                            //查询等级是否存在
                            string result1;
                            long totalcount = -1;
                            if (ProductCode != null && ProductCode != "" && LevelId != null && LevelId != "")
                            {
                                List<SqlParameter> parameters = new List<SqlParameter>();
                                totalcount = DataAccess.GetRowCountDefine("SELECT count(1) from mem_ingProductLevel WHERE ProductCode='" + ProductCode + "' AND levelId='" + LevelId + "'", parameters.ToArray(), out result1);
                            }
                            string logStr = "";
                            if (totalcount <= 0)
                            {
                                //添加
                                var sql = "INSERT INTO mem_ingProductLevel(PlId,ProductCode,LevelId,MarketIng,MarketPrice,CreationDate) Values(" +
                                   "@PlId,@ProductCode,@LevelId,@MarketIng,@MarketPrice,@CreationDate)";
                                var param = new List<SqlParameter>();
                                PlId = plId;
                                param.Add(new SqlParameter("PlId", PlId));
                                param.Add(new SqlParameter("ProductCode", ProductCode));
                                param.Add(new SqlParameter("LevelId", LevelId));
                                param.Add(new SqlParameter("MarketIng", MarketIng));
                                param.Add(new SqlParameter("MarketPrice", MarketPrice));
                                param.Add(new SqlParameter("CreationDate", DateTime.Now));
                                success = DataAccess.ExecuteCommand(sql, param, out msg);
                                logStr = "产品等级定价：" + ProductCode + "levelId:" + LevelId;
                            }
                            else
                            {
                                string sql = "UPDATE mem_ingProductLevel  SET  MarketIng=@MarketIng,MarketPrice=@MarketPrice WHERE ProductCode = @ProductCode and LevelId = @LevelId";
                                //准备参数
                                List<List<Object>> parametersC = new List<List<Object>>();
                                parametersC.Add(new List<Object>() { "MarketIng", MarketIng });
                                parametersC.Add(new List<Object>() { "MarketPrice", MarketPrice });
                                parametersC.Add(new List<Object>() { "ProductCode", ProductCode });
                                parametersC.Add(new List<Object>() { "LevelId", LevelId });
                                List<SqlParameter> parametersC1 = DataAccess.ToParameters(parametersC);
                                success = DataAccess.ExecuteCommand(sql, parametersC1, out msg);
                                logStr = "产品等级定价：" + ProductCode + "levelId:" + LevelId;
                            }
                            if (success > 0)
                            {
                                //记录日志
                                addProductLog(accountId, "5", logStr, ProductCode);
                            }
                        }
                    }
                }
            }








            /*if (data != null)
            {
                ProductCode = data.ProductCode;
                MarketIng = data.MarketIng;
                MarketPrice = data.MarketPrice;
                LevelId = data.LevelId;
            }*/


            return Json(new
            {
                success = success,
                msg = msg
            });
        }


        public DataTable getProductPic(string productCode)
        {
            DataTable dt = new DataTable();
            if (productCode != null)
            {
                List<SqlParameter> parameters = new List<SqlParameter>();
                string commandText = "SELECT top 8 * FROM mem_ingProductPic WHERE IsDelete='1' AND ProductCode='" + productCode + "' ORDER BY CreationDate DESC ";
                string result;
                bool success = DataAccess.GetValues(commandText, ref dt, parameters.ToArray(), out result);
            }
            return dt;
        }
        /// <summary>
        /// 记录日志
        /// </summary>
        /// <returns></returns>
        public static int addProductLog(string MemberId,string logCode,string key="",string Value="", string Text = "") {
            int success = 0;
            if (MemberId!=null&&  logCode!=null) {
                var sql = "INSERT INTO [dbo].[mem_ingProductLog] ([MemberId], [logCode], [CreationDate], [key], [Text], [Value]) VALUES ( @MemberId, @logCode, @CreationDate, @key, @Text, @Value)";
                var param = new List<SqlParameter>();
                param.Add(new SqlParameter("MemberId", MemberId));
                param.Add(new SqlParameter("logCode", logCode));
                param.Add(new SqlParameter("CreationDate", DateTime.Now));
                param.Add(new SqlParameter("key", key));
                param.Add(new SqlParameter("Text", Text));
                param.Add(new SqlParameter("Value", Value));
                string msg = "";
                success = DataAccess.ExecuteCommand(sql, param, out msg);
            }
            return success;
        }


        /// <summary>
        /// 商品类目
        /// </summary>
        /// <param name="filters"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="sortField"></param>
        /// <param name="sortDirection"></param>
        /// <param name="sumFields"></param>
        /// <param name="data"></param>
        /// <returns></returns>
        [HttpPost]
        [Route("AllproductType")]
        public ActionResult AllproductType(QueryFilter[] filters, Int32 pageIndex, Int32 pageSize,
            string sortField, Int32 sortDirection, string[] sumFields, [FromBody]dynamic data)
        {

            if (data != null)
            {
                filters = data.filters.ToObject<QueryFilter[]>();
                pageIndex = data.pageIndex;
                pageSize = data.pageSize;
                sortField = data.sortField;
                sortDirection = data.sortDirection;
                sumFields = data.sumFields.ToObject<string[]>();
            }
            DataTable dt = new DataTable();
            string result;
            IList<mem_productType> permss = new List<mem_productType>(0);

            List<SqlParameter> parameters = new List<SqlParameter>();
            string filterstr = QueryFilter.getFilterSqlParam(filters, out parameters, new mem_productType(), "t.");

            int start = (pageIndex - 1) * pageSize;
            int end = (start + 1 + pageSize);

            string commandText0 = "select * from ";
            string commandText1 = "( select * ,row_number() over(order by t.CreationDate desc )  as rownum,(SELECT count(1) FROM mem_ingProduct WHERE ProductType=t.TypeId) as ProductCn from mem_ingProductType t where 1=1 "+ filterstr + " )AAA  ";
            string commandText2 = " where AAA.rownum > " + start + " and AAA.rownum < " + end + "  ORDER BY AAA.CreationDate desc ";
            string commandText3 = commandText0 + commandText1 + commandText2;
            bool success = DataAccess.GetValues(commandText3, ref dt, parameters.ToArray(), out result);


            if (dt != null && dt.Rows.Count > 0)
            {
                // 把DataTable转换为IList<T>  
                permss = ModelConvertHelper<mem_productType>.ConvertToModel(dt);
            }
            string result1;
            long totalcount = DataAccess.GetRowCountDefine("select count(1) from " + commandText1, parameters.ToArray(), out result1);
            IsoDateTimeConverter timejson = new IsoDateTimeConverter
            {
                DateTimeFormat = "yyyy'-'MM'-'dd' 'HH':'mm':'ss"
            };
            var jsonData = JsonConvert.SerializeObject(permss, timejson);

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

        }



        /// <summary>
        /// 修改商品类目
        /// </summary>
        /// <param name="data"></param>
        /// <param name="staffId"></param>
        /// <returns></returns>
        [HttpPost]
        [Route("editProductType")]
        public JsonResult editProductType([FromBody]dynamic data, string staffId,string typeId,string productCode,string productName)
        {
            string accountId = getStaff(staffId, "userid");
            //判断参数是否合法
            if (string.IsNullOrEmpty(accountId))
            {
                return Json(new
                {
                    timeout = false,
                    msg = "没有登陆"
                });
            }
            if (data != null)
            {
                typeId = data.typeId;
                productCode = data.productCode;
                productName = data.productName;
            }
            int success = 0;
            string msg = "";
            string logStr = "";
            if (productCode!=null && productCode!="" && typeId!=null && typeId!="") {
                var productCodes = productCode.Split(",");//多选
                foreach (string id in productCodes) {
                    string sql = "UPDATE mem_ingProduct  SET  ProductType=@ProductType WHERE ProductCode = @ProductCode";
                    //准备参数
                    List<List<Object>> parametersC = new List<List<Object>>();
                    parametersC.Add(new List<Object>() { "ProductType", typeId });
                    parametersC.Add(new List<Object>() { "ProductCode", id });
                    List<SqlParameter> parametersC1 = DataAccess.ToParameters(parametersC);
                    success = DataAccess.ExecuteCommand(sql, parametersC1, out msg);
                }
                logStr = "商品修改分类:" +productCode + productName;
            }

            if (success>0) {
                //记录日志
                addProductLog(accountId, "4", logStr);
            }
            return Json(new
            {
                success = success,
                msg = msg
            });
        }



        [HttpPost]
        [Route("addMemProductType")]
        public JsonResult addMemProductType([FromBody]dynamic data, string staffId, string typeId, string typeName, string code)
        {
            string accountId = getStaff(staffId, "userid");
            //判断参数是否合法
            if (string.IsNullOrEmpty(accountId))
            {
                return Json(new
                {
                    timeout = false,
                    msg = "没有登陆"
                });
            }
            if (data != null)
            {
                typeId = data.typeId;
                typeName = data.typeName;
                code = data.code;
            }
            int success = 0;
            string msg = "";
            string logStr = "";
            if (code == "1")
            {
                //添加
                var sql = "INSERT INTO mem_ingProductType (TypeId,TypeName,CreationDate)VALUES(@TypeId,@TypeName,@CreationDate)";
                var param = new List<SqlParameter>();
                param.Add(new SqlParameter("TypeId", Guid.NewGuid()));
                param.Add(new SqlParameter("typeName", typeName));
                param.Add(new SqlParameter("CreationDate", DateTime.Now));
                success = DataAccess.ExecuteCommand(sql, param, out msg);
                logStr = "新增分类:" + typeName;
            }
            else
            {
                string sql = "UPDATE mem_ingProductType  SET  TypeName=@TypeName WHERE TypeId = @TypeId";
                //准备参数
                List<List<Object>> parametersC = new List<List<Object>>();
                parametersC.Add(new List<Object>() { "TypeName", typeName });
                parametersC.Add(new List<Object>() { "TypeId", typeId });
                List<SqlParameter> parametersC1 = DataAccess.ToParameters(parametersC);
                success = DataAccess.ExecuteCommand(sql, parametersC1, out msg);
                logStr = "修改分类:" + typeName;
            }
            if (success > 0)
            {
                //记录日志
                addProductLog(accountId, "4", logStr);
            }
            return Json(new
            {
                success = success,
                msg = msg
            });
        }



        public static int addProductPic(string ProductCode, string fileimg) {
            JArray jArray = JArray.Parse(fileimg);
            int success1 = 0;
            if (jArray.Count > 0)
            {
                string imgStr = "";
                foreach (var jj in jArray)
                {
                    JObject job = (JObject)jj;
                    string url = job["url"].ToString();//图片地址
                    List<SqlParameter> parameters = new List<SqlParameter>();
                    if (ProductCode != null && ProductCode != "")
                    {
                        imgStr += "('" + ProductCode + "','" + url + "'),";
                    }
                }
                imgStr = "DELETE FROM  mem_ingProductPic WHERE ProductCode='" + ProductCode + "';INSERT INTO mem_ingProductPic(ProductCode,PicUrl)Values" + imgStr.Substring(0, imgStr.Length - 1);
                //imgStr = "UPDATE mem_ingProductPic SET IsDelete=0 WHERE ProductCode='" + ProductCode + "';INSERT INTO mem_ingProductPic(ProductCode,PicUrl)Values" + imgStr.Substring(0, imgStr.Length - 1);
                var param = new List<SqlParameter>();
                string msg1 = "";
                success1 = DataAccess.ExecuteCommand(imgStr, param, out msg1);             
            }
            return success1;
        }

    }
}