test/Member/SupplierWeb/Controllers/NewGoodsController.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using Common.Model;
using Common.Wechat;
using CoreEntity.DAL;
using CoreEntity.Entity;
using JCSoft.WX.Framework.Api;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Caching.Memory;
using Newtonsoft.Json;
using Newtonsoft.Json.Serialization;
using Npoi.Mapper;
using PublicLibrary.Model;
using SupplierWeb.Codes.mvc;
using ZcPeng.PublicLibrary;

namespace SupplierWeb.Controllers
{
    [Route("web/newgoods")]
    [ApiController]
    public class NewGoodsController : BaseController
    {

        [AuthPermission]
        [HttpPost]
        [Route("getData")]
        public JsonResult GetData(string[] filters, int pageIndex, int pageSize,
            string sortField, Int32 sortDirection, string[] sumFields, [FromBody] dynamic data)
        {
            List<QueryFilter> filterList = new List<QueryFilter>();
            if (data != null)
            {
                if (data.filters.Count > 0)
                {
                    var list = JsonConvert.SerializeObject(data.filters);
                    filterList = JsonConvert.DeserializeObject<List<QueryFilter>>(list);
                }
                pageIndex = data.pageIndex;
                pageSize = data.pageSize;
                sortField = data.sortField;
                sortDirection = data.sortDirection;
                sumFields = data.sumFields.ToObject<string[]>();
            }
            var isAce = sortDirection == 0;
            var direction = " asc ";
            if (isAce)
                direction = " desc ";
            var queryCondition = string.Empty;
            var param = new List<SqlParameter>();
            var start = (pageIndex - 1) * pageSize;
            var end = start + 1 + pageSize;
            var dt = new DataTable();

            if (filterList != null)
            {
                queryCondition = QueryFilter.getFilterSqlParam(filterList.ToArray(),out param,new Temp(),"");
            }




            //var sql =
            //    $"select * from (select row_number() over (order by A.GoodsId {(isAce ? "asc" : "desc")}) as rowNum,A.*,B.StdCode,B.FirstSupp,B.ApprovalNo from GOODSDOC A left join GOODSATTR B on A.GoodsId = B.GoodsId) C where c.rowNum > {start} and c.rowNum < {end} {queryCondition}";
            var sql =
                $"select * from " +
                $"(select *," +
                $"row_number() over (order by C.{sortField} {direction}) as rowNum from (" +
                $"select A.goodsId,A.goodsName,'有编码' as goodsCode,A.goodsSpec,A.manufacturer," +
                $"B.StdCode,B.FirstSupp,B.ApprovalNo " +
                $"from GOODSDOC A " +
                $"left join GOODSATTR B on A.GoodsId = B.GoodsId and B.EntId = A.EntId " +
                $"where 1 = 1 {queryCondition}) C " +
                $") R where rowNum > {start} and rowNum < {end}";


            DataAccess.GetValues(sql, ref dt, param.ToArray(), out var msg);


            IList<Temp> result = new List<Temp>();
            if (dt != null && dt.Rows.Count > 0)
            {
                result = ModelConvertHelper<Temp>.ConvertToModel(dt);
            }

            var jsonData = JsonConvert.SerializeObject(result);

            var countSql =
                $"select count(1) from (select * from (select A.*,B.StdCode,B.FirstSupp,B.ApprovalNo " +
                $"from GOODSDOC A " +
                $"left join GOODSATTR B on A.GoodsId = B.GoodsId  and B.EntId = A.EntId " +
                $"where 1 = 1 {queryCondition}) C ) R";

            var count = DataAccess.GetRowCountDefine(countSql, param.ToArray(), out _);

            return Json(new
            {
                items = result,
                sum = new { },
                totalCount = count,
                msg = msg
            });
        }

        [AuthPermission]
        [HttpPost]
        [Route("add")]
        public JsonResult Add(string staffid, [FromBody] Temp entity)
        {
            var userid = getStaffUserid(staffid);
            //判断参数是否合法
            if (string.IsNullOrEmpty(userid))
            {

                return Json(new
                {
                    success = 0,
                    msg = "没有登陆"
                });
            }

            var sql0 = "select * from sup_Account where id = @userid";
            var param0 = new List<SqlParameter>();
            param0.Add(new SqlParameter("userid", int.Parse(userid)));
            var dt0 = new DataTable();
            DataAccess.GetValues(sql0, ref dt0, param0.ToArray(), out _);
            var userName = string.Empty;
            if (dt0 != null && dt0.Rows.Count > 0)
            {
                userName = dt0.Rows[0]["AccountRealName"].ToString();
            }

            //Object GoodsCode = null;
            //var param11 = new List<SqlParameter>();
            //param11.Add(new SqlParameter("GoodsId", entity.goodsId));
            //DataAccess.GetOneValue("select GoodsCode from GoodsDoc where GoodsId = @GoodsId", param11.ToArray(), out GoodsCode, out _);

            var sql1 = "select * from NEWGOOD where GoodsId=@GoodsId and GoodsSpec=@GoodsSpec and State = 1 and userid = @userid";
            var param1 = new List<SqlParameter>();
            var oneparam = entity.goodsId == null ? new SqlParameter("GoodsId", DBNull.Value ): new SqlParameter("GoodsId", entity.goodsId.ToString());
            param1.Add(oneparam);
            param1.Add(new SqlParameter("GoodsName", entity.goodsName));
            param1.Add(new SqlParameter("GoodsSpec", entity.goodsSpec));
            param1.Add(new SqlParameter("userid", int.Parse(userid)));
            var dt = new DataTable();
            DataAccess.GetValues(sql1, ref dt, param1.ToArray(), out _);
            if (dt != null && dt.Rows.Count > 0)
            {
                return Json(new
                {
                    success = false,
                    msg = "已提交过该商品"
                });
            }
            if (!string.IsNullOrEmpty(entity.goodsId))
            {
                var sql3 = "select * from k_contactsp " +
                    "left join CONTACTDOC on k_contactsp.ContactId=CONTACTDOC.ContactId  and k_contactsp.EntId = CONTACTDOC.EntId " +
                    "where UserId= @UserId and GoodsId=@GoodsId";
                var param3 = new List<SqlParameter>();
                param3.Add(new SqlParameter("GoodsId", entity.goodsId));
                param3.Add(new SqlParameter("UserId", int.Parse(userid)));
                var dt3 = new DataTable();
                DataAccess.GetValues(sql3, ref dt3, param3.ToArray(), out _);
                if (dt3 != null && dt3.Rows.Count > 0)
                {
                    return Json(new
                    {
                        success = false,
                        msg = "已提交过该商品"
                    });
                }
            }

            var sql2 = "select GoodsCode from GoodsDoc where GoodsId = @GoodsId ";
            var param2 = new List<SqlParameter>();
            param2.Add(new SqlParameter("GoodsId", entity.goodsId));
            var result2 = "";
            DataAccess.GetOneValue(sql2, param2.ToArray(), out var obj,out result2);
            if (obj != null)
            {
                string goodscode = obj.ToString();
                entity.goodsCode = goodscode;
            }
            var sql =
                $"insert into NEWGOOD (GoodsCode,GoodsName, GoodsSpec, Manufacturer, ApprovalNo, Cnum, Price , UserID, UserName, CreateDate ,State ,Comment4sup,SuppliersId,GoodsId,EntId" +
                $") values (@GoodsCode,@GoodsName, @GoodsSpec, @Manufacturer, @ApprovalNo, @Cnum, @Price,@UserID,@UserName,@CreateDate,@State,@Comment4sup,@SuppliersId,@GoodsId,@EntId)";
            var param = new List<SqlParameter>();
            if (string.IsNullOrEmpty(entity.goodsCode))
            {
                entity.goodsCode = "";
            }
            string comment = entity.Comment4sup == null ? "" : entity.Comment4sup;
            param.Add(new SqlParameter("GoodsCode", entity.goodsCode));
            param.Add(new SqlParameter("GoodsName", entity.goodsName));
            param.Add(new SqlParameter("GoodsSpec", entity.goodsSpec));
            param.Add(new SqlParameter("Manufacturer", entity.manufacturer));
            param.Add(new SqlParameter("ApprovalNo", entity.approvalNo));
            param.Add(new SqlParameter("Cnum", entity.stockNum));
            param.Add(new SqlParameter("Price", entity.SaleP));
            param.Add(new SqlParameter("UserID", int.Parse(userid)));
            param.Add(new SqlParameter("UserName", userName));
            param.Add(new SqlParameter("CreateDate", DateTime.Now));
            param.Add(new SqlParameter("State", 1));
            param.Add(new SqlParameter("Comment4sup", comment));
            param.Add(new SqlParameter("SuppliersId", entity.SuppliersId == null ? "" : entity.SuppliersId));
            var oneparam1 = entity.goodsId == null ? new SqlParameter("GoodsId", DBNull.Value) : new SqlParameter("GoodsId", entity.goodsId.ToString());
            param.Add(oneparam1);
            param.Add(new SqlParameter("EntId", "E1WB67UEYPG"));
            int success = DataAccess.ExecuteCommand(sql, param, out var msg);
            return Json(new
            {
                success = success,
                msg = msg
            });
        }

        [AuthPermission]
        [HttpPost]
        [Route("edit")]
        public JsonResult Edit(string staffid, [FromBody] Temp entity)
        {
            var userid = getStaffUserid(staffid);
            //判断参数是否合法
            if (string.IsNullOrEmpty(userid))
            {
                return Json(new
                {
                    success = 0,
                    msg = "没有登陆"
                });
            }


            var sql =
                $"update NEWGOOD set Cnum=@Cnum,Price=@Price where id = @id";
            var param = new List<SqlParameter>();
            param.Add(new SqlParameter("Cnum", entity.Cnum));
            param.Add(new SqlParameter("Price", entity.Price));
            param.Add(new SqlParameter("id", entity.Id));

            var success = DataAccess.ExecuteCommand(sql, param, out var msg);
            return Json(new
            {
                success = true,
                msg
            });
        }

        [AuthPermission]
        [HttpPost]
        [Route("getNewGoodsList")]
        public JsonResult GetNewGoodsList(string staffid, [FromBody] dynamic data)
        {
            var userid = getStaffUserid(staffid);
            //判断参数是否合法
            if (string.IsNullOrEmpty(userid))
            {

                return Json(new
                {
                    success = 0,
                    msg = "没有登陆"
                });
            }
            List<QueryFilter> filterList = new List<QueryFilter>();

            int pageIndex = 1;
            int pageSize = 15;
            string sortField = " Id ";
            int sortDirection = 0;
            string[] sumFields;
            if (data != null)
            {
                if (data.filters.Count > 0)
                {
                    var list = JsonConvert.SerializeObject(data.filters);
                    filterList = JsonConvert.DeserializeObject<List<QueryFilter>>(list);
                }
                pageIndex = data.pageIndex;
                pageSize = data.pageSize;
                sortField = data.sortField;
                sortDirection = data.sortDirection;
                sumFields = data.sumFields.ToObject<string[]>();
            }
            var isAce = sortDirection == 0;
            string direction = "asc";
            if (isAce)
                direction = "desc";
            var queryCondition = string.Empty;
            var param = new List<SqlParameter>();
            var start = (pageIndex - 1) * pageSize;
            var end = (start + 1 + pageSize);
            var dt = new DataTable();

            if (filterList != null)
            {
                queryCondition = QueryFilter.getFilterSqlParam(filterList.ToArray(), out param,new Temp2(),"A.");
                queryCondition = queryCondition.Replace("A.Suppliers", "B.BusinessName");
            }
            sortField = "A." + sortField;
            sortField = sortField.Replace("A.Suppliers", "B.BusinessName");

            var sql =
                $"select  *  from ";
            var sql1 = $"(select A.Id,A.approvalNo,A.goodsName,case A.GoodsCode when 0 then '0' else '有编码'end as goodsCode1,A.goodsSpec,A.manufacturer,A.SuppliersId,A.Price,A.Cnum,A.Comment4sup,A.CreateDate," +
                $"B.BusinessName  as Suppliers,row_number() over (order by {sortField} {direction}) as rowNum " +
                $"from NEWGOOD A " +
                $"left join BUSINESSDOC B on B.BusinessId=A.SuppliersId  " +
                $"where 1 = 1 and A.userid = @userid and A.state = 1 {queryCondition}" +
                $") R";
            var sql2 = $" where rowNum > {start} and rowNum < {end}";
            param.Add(new SqlParameter("userid", int.Parse(userid)));

            DataAccess.GetValues(sql + sql1 + sql2, ref dt, param.ToArray(), out var msg);


            IList<Temp2> result = new List<Temp2>();
            if (dt != null && dt.Rows.Count > 0)
            {
                result = ModelConvertHelper<Temp2>.ConvertToModel(dt);
            }

            var countSql =
                $"select count(1) from "+ sql1;

            var count = DataAccess.GetRowCountDefine(countSql, param.ToArray(), out _);

            var setting = new JsonSerializerSettings
            {
                ContractResolver = new DefaultContractResolver()
            };
            //JsonConvert.DefaultSettings = new Func<JsonSerializerSettings>(() =>
            //{
            //    //日期类型默认格式化处理
            //    //setting.DateFormatHandling = Newtonsoft.Json.DateFormatHandling.MicrosoftDateFormat;
            //    //setting.DateFormatString = "yyyy-MM-dd HH:mm:ss";

            //    //空值处理
            //    //setting.NullValueHandling = NullValueHandling.Ignore;

            //    //高级用法九中的Bool类型转换 设置
            //    //setting.Converters.Add(new BoolConvert("是,否"));
            //    IsoDateTimeConverter timejson = new IsoDateTimeConverter
            //    {
            //        DateTimeFormat = "yyyy'-'MM'-'dd' 'HH':'mm':'ss"
            //    };
            //    setting.Converters.Add(timejson);

            //    return setting;
            //});
            var jsonData = JsonConvert.SerializeObject(result, setting);
            return Json(new
            {
                items = JsonConvert.DeserializeObject(jsonData),
                sum = new { },
                totalCount = count
            });
        }

        [AuthPermission]
        [HttpPost]
        [Route("getPurNewGoodsList")]
        public JsonResult GetPurNewGoodsList(string staffid, [FromBody] dynamic data)
        {
            var userid = getStaffUserid(staffid);
            var RoleId = getStaff("roleid");
            //判断参数是否合法
            if (string.IsNullOrEmpty(userid))
            {

                return Json(new
                {
                    success = 0,
                    msg = "没有登陆",
                    timeout = 1
                });
            }
            List<QueryFilter> filterList = new List<QueryFilter>();

            int pageIndex = 1;
            int pageSize = 15;
            string sortField;
            int sortDirection = 0;
            string[] sumFields;
            if (data != null)
            {
                if (data.filters.Count > 0)
                {
                    var list = JsonConvert.SerializeObject(data.filters);
                    filterList = JsonConvert.DeserializeObject<List<QueryFilter>>(list);
                }
                pageIndex = data.pageIndex;
                pageSize = data.pageSize;
                sortField = data.sortField;
                sortDirection = data.sortDirection;
                sumFields = data.sumFields.ToObject<string[]>();
            }
            var isAce = sortDirection == 0;
            var queryCondition = string.Empty;
            var param = new List<SqlParameter>();
            var start = (pageIndex - 1) * pageSize;
            var end = (start + 1 + pageSize);
            var dt = new DataTable();

            if (filterList != null)
            {
                queryCondition = QueryFilter.getFilterSqlParam(filterList.ToArray(),out param,new Temp2(),"B.");
            }
            queryCondition = queryCondition.Replace("B.Suppliers", "B1.BusinessName");

            string StaffDocId = StaffDocDAL.GetStaffId(userid);
            string filterPurRole = ((RoleId == "1" || RoleId == "6" || RoleId == "7" || RoleId == "8") ? "" :
                (" and B.userid in" +
                " (SELECT A1.userid FROM K_contactsp K1 LEFT JOIN ContactDoc A1 ON K1.ContactId = A1.ContactId " +
                "where K1.SaleManId = '" + StaffDocId + "' and A1.Beactive = 'Y')  "));

            var sql =
                $"select * from ";
            var sql1 = $"(select B1.BusinessName " +
                $" as Suppliers ," +
                $" case when B.PurPrice is not null " +
                $" then B.PurPrice else  D.PurP end as LastPrice," +
                $" ID ,B.GoodsName ,B.GoodsSpec ,B.Manufacturer ,B.ApprovalNo ,  Cnum ,  Price,  UserId,UserName,  B.CreateDate,  State,  " +
                $"B.GoodsCode,  LastModifyDate,LastModifyUser,Comment4sup,Comment4pur,SuppliersId," +
                $" row_number() over (order by id desc) as rowNum " +
                $"from NEWGOOD B " +
                $" left join BUSINESSDOC B1 on B1.BusinessId=B.SuppliersId and B1.EntId='E1WB67UEYPG'" +
                $" left join GoodsDoc C on convert(varchar,B.GoodsCode)=C.GoodsCode  and C.EntId='E1WB67UEYPG'" +
                $" left join GOODSATTR D on C.GoodsId=D.GoodsId  and D.EntId = C.EntId " +
                $" where 1 = 1 and state = 1 {queryCondition} " + filterPurRole + ") R";
            var sql2 = $" where rowNum > {start} and rowNum < {end}";
            string result1 = "";
            DataAccess.GetValues(sql + sql1 + sql2, ref dt, param.ToArray(), out result1);


            IList<Temp2> result = new List<Temp2>();
            if (dt != null && dt.Rows.Count > 0)
            {
                result = ModelConvertHelper<Temp2>.ConvertToModel(dt);
            }

            var countSql =
                $"select count(1) from " +sql1 ;
            string result2 = "";
            var count = DataAccess.GetRowCountDefine(countSql, param.ToArray(), out result2);

            return Json(new
            {
                items = result,
                sum = new { },
                totalCount = count
            });
        }

        [AuthPermission]
        [HttpPost]
        [Route("updateprice")]
        public JsonResult updateprice(string staffid, [FromBody] dynamic data)
        {
            var userid = getStaffUserid(staffid);
            //判断参数是否合法
            if (string.IsNullOrEmpty(userid))
            {

                return Json(new
                {
                    success = 0,
                    msg = "没有登陆"
                });
            }

            var sql0 = "select * from sup_Account where id = @userid";
            var param0 = new List<SqlParameter>();
            param0.Add(new SqlParameter("userid", int.Parse(userid)));
            var dt0 = new DataTable();
            DataAccess.GetValues(sql0, ref dt0, param0.ToArray(), out _);
            var userName = string.Empty;
            if (dt0 != null && dt0.Rows.Count > 0)
            {
                userName = dt0.Rows[0]["AccountRealName"].ToString();
            }

            var sql = "update newgood set PurPrice=@PurPrice , LastModifyDate=@LastModifyDate ,LastModifyUser=@LastModifyUser ,Comment4pur=@Comment4pur ,SuppliersId=@SuppliersId where id=@id";
            var param = new List<SqlParameter>();
            int id = data.id;
            string purPrice = data.purPrice;
            string comment = data.comment4pur==null? "":data.comment4pur;
            string suppliersId = data.suppliersId;
            param.Add(new SqlParameter("id", id));
            param.Add(new SqlParameter("LastModifyDate", DateTime.Now));
            param.Add(new SqlParameter("LastModifyUser", userName));
            param.Add(new SqlParameter("PurPrice", purPrice));
            param.Add(new SqlParameter("Comment4pur", comment));
            param.Add(new SqlParameter("SuppliersId", suppliersId));
            var dt = new DataTable();
            string result = "";
            var infectRow = DataAccess.ExecuteCommand(sql, param.ToArray(), out result);
            if (infectRow > 0)
            {
                return Json(new
                {
                    success = true
                });
            }

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

        [AuthPermission]
        [HttpGet]
        [Route("getsup")]
        public string getsup(string staffid)
        {
            var userid = getStaffUserid(staffid);
            //判断参数是否合法
            if (string.IsNullOrEmpty(userid))
            {
                return "";
            }

            var sql =
              $"select userid,b.BusinessName as lab,b.BusinessId as value  from contactdoc A " +
              $"left join BUSINESSDOC B on a.BusinessId=b.BusinessId " +
              //$"left join supplydoc C on b.BusinessCode=c.gysbh " +
              $"where B.BusinessId is not null and userid=@userid";
            var param = new List<SqlParameter>();
            var dt = new DataTable();
            var id = int.Parse(userid);
            param.Add(new SqlParameter("userid", id));
            DataAccess.GetValues(sql, ref dt, param.ToArray(), out _);


            IList<SupDrow> result = new List<SupDrow>();
            if (dt != null && dt.Rows.Count > 0)
            {
                result = ModelConvertHelper<SupDrow>.ConvertToModel(dt);
            }
            return JsonConvert.SerializeObject(result);
        }

        [AuthPermission]
        [HttpGet]
        [Route("getallsup")]
        public string getallsup(string staffid)
        {
            var userid = getStaffUserid(staffid);
            //判断参数是否合法
            if (string.IsNullOrEmpty(userid))
            {
                return "";
            }

            var sql =
              $"select distinct userid,b.BusinessName as lab,b.BusinessId as value  from contactdoc A " +
              $"left join BUSINESSDOC B on a.BusinessId=b.BusinessId " +
              //$"left join supplydoc C on b.BusinessCode=c.gysbh " +
              $"where b.BusinessId is not null";
            var param = new List<SqlParameter>();
            var dt = new DataTable();
            var id = int.Parse(userid);
            param.Add(new SqlParameter("userid", id));
            DataAccess.GetValues(sql, ref dt, param.ToArray(), out _);


            IList<SupDrow> result = new List<SupDrow>();
            if (dt != null && dt.Rows.Count > 0)
            {
                result = ModelConvertHelper<SupDrow>.ConvertToModel(dt);
            }
            return JsonConvert.SerializeObject(result);
        }

        public class SupDrow
        {
            public string lab { get; set; }
            public string label { get => lab; }
            public string value { get; set; }
            public bool disabled { get; set; }
        }


        public NewGoodsController(IMemoryCache cache, IApiClient client) : base(cache, client)
        {
        }

        public class Temp
        {
            public int Id { get; set; }
            public string goodsCode { get; set; }
            public string goodsId { get; set; }
            public string approvalNo { get; set; }
            public string goodsName { get; set; }
            public string goodsSpec { get; set; }
            public string manufacturer { get; set; }
            public string stdCode { get; set; }
            public decimal SaleP { get; set; }
            public int stockNum { get; set; }
            public string FirstSupp { get; set; }
            public decimal Price { get; set; }
            public int Cnum { get; set; }
            public decimal PurPrice { get; set; }
            public DateTime LastModifyDate { get; set; }
            public string LastModifyUser { get; set; }
            public DateTime CreateDate { get; set; }
            public string UserName { get; set; }
            public string Comment4sup { get; set; }
            public string Comment4pur { get; set; }
            public string SuppliersId { get; set; }
            public string Suppliers { get; set; }
            public string EntId { get; set; }
            public string A_EntId { get; set; }
            public string C_EntId { get; set; }
        }
        public class Temp2
        {
            public int Id { get; set; }
            public int goodsCode { get; set; }
            public string goodsCode1 { get; set; }
            public string goodsId { get; set; }
            public string approvalNo { get; set; }
            public string goodsName { get; set; }
            public string goodsSpec { get; set; }
            public string manufacturer { get; set; }
            public string stdCode { get; set; }
            public decimal SaleP { get; set; }
            public int stockNum { get; set; }
            public string FirstSupp { get; set; }
            public decimal Price { get; set; }
            public int Cnum { get; set; }
            public decimal PurPrice { get; set; }
            public DateTime LastModifyDate { get; set; }
            public string LastModifyUser { get; set; }
            public DateTime CreateDate { get; set; }
            public string UserName { get; set; }
            public string Comment4sup { get; set; }
            public string Comment4pur { get; set; }
            public string SuppliersId { get; set; }
            public string Suppliers { get; set; }
            public decimal LastPrice { get; set; }
            public string C_EntId { get; set; }
        }
        public class NewGoodsExcel
        {
            private string _Contact;
            private string _GoodsCode;
            private string _GoodsName;
            private string _GoodsSpec;
            private string _Unit;
            private decimal _PurPrice;
            private decimal _ActPrice;
            private string _manufacturer;
            private string _suppliers;
            private DateTime _createDate;
            //private string _BusinessName;
            //private string _ApprovalNo;

            public string contact { get => _Contact; set => _Contact = value; }
            public string goodsCode { get => _GoodsCode; set => _GoodsCode = value; }
            public string goodsName { get => _GoodsName; set => _GoodsName = value; }
            public string goodsSpec { get => _GoodsSpec; set => _GoodsSpec = value; }
            public string unit { get => _Unit; set => _Unit = value; }
            public decimal purPrice { get => _PurPrice; set => _PurPrice = value; }
            public decimal actPrice { get => _ActPrice; set => _ActPrice = value; }
            public string manufacturer { get => _manufacturer; set => _manufacturer = value; }
            public string ApprovalNo { get; set; }

            public string Suppliers { get => _suppliers; set => _suppliers = value; }
            public DateTime createDate { get => _createDate; set => _createDate = value; }
        }

        [HttpPost, Route("exportexcel")]
        public ActionResult exportExcel([FromServices]IHostingEnvironment env, string staffId, string filter)
        {

            string userids;

            userids = getStaffUserid(staffId);
            //判断参数是否合法
            if (string.IsNullOrEmpty(userids))
            {
                return Json(new
                {
                    success = false,
                    msg = "没有登陆"
                });
            }

            #region 获取新品
            DataTable dt = new DataTable();
            string result;

            string direct = " desc ";
            if (0 != 1)
                direct = " asc";


            List<SqlParameter> parameters1;
            //filter参数
            QueryFilter[] filters = JsonConvert.DeserializeObject<QueryFilter[]>(filter);
            String filterstr = QueryFilter.getFilterSqlParam(filters, out parameters1, new Temp(), "A.");
            filterstr = filterstr.Replace("A.Suppliers", "B.BusinessName");
            filterstr = filterstr.Replace("A.unit", "C.unit");
            filterstr = filterstr.Replace("A.EntId", "C.EntId");
            //filterstr += " and C.GoodsCode is NOT NULL ";

            string commandText0 = "select * from (";
            string commandText1 = "select A.UserName as contact,convert(varchar(20),isnull(A.goodsCode,0)) as goodsCode," +
                "A.goodsName,A.goodsSpec,A.manufacturer," +
                "'盒' as unit ," +
                "A.Price as purPrice,A.purPrice as actPrice,A.approvalNo," +
                //"C.GoodsCode," +
                "B.BusinessName as Suppliers," +
                "A.createDate as createDate," +
                " row_number() over" +
                "(  order by SuppliersId " + direct + " )  as rownum from "
                 + "NewGood A " +
                " left join BusinessDoc B on A.SuppliersId = B.BusinessId " +
                " left join GoodsDoc C on CONVERT(varchar(50), A.GoodsCode) = C.GoodsCode " +
                " where 1=1 " +
                    filterstr +
            ")AAA ";
            string commandText = commandText0 + commandText1;

            bool success = DataAccess.GetValues(commandText, ref dt, parameters1.ToArray(), out result);

            //result = DataAccess.GetDataTable(Config.TablePrefix + "PushFeedback", "Id", "*", fieldFilter, "", sortField + direct, pageIndex, pageSize != 0 ? pageSize : Config.PageSize);

            // 把DataTable转换为IList<UserInfo>  
            IList<NewGoodsExcel> users = new List<NewGoodsExcel>();
            if (dt != null && dt.Rows.Count > 0)
            {
                // 把DataTable转换为IList<UserInfo>  
                users = ModelConvertHelper<NewGoodsExcel>.ConvertToModel(dt);

            }
            #endregion
            var mapper = new Mapper();

            var fileName = Path.Combine("excel", "_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls");
            mapper
                .Map<NewGoodsExcel>("联系人", o => o.contact)
                .Map<NewGoodsExcel>("商品编号", o => o.goodsCode)
                .Map<NewGoodsExcel>("商品名称", o => o.goodsName)
                .Map<NewGoodsExcel>("商品规格", o => o.goodsSpec)
                .Map<NewGoodsExcel>("单位", o => o.unit)
                .Map<NewGoodsExcel>("标准进价", o => o.purPrice/*, null,(column, source) => // tryPut resolver : Custom logic to put property value into cell.
                {
                    // Custom logic to set the cell value.
                    var sample = (NewGoodsExcel)source;

                    var index = column.Attribute.Index;
                    if ((index == 6))
                    {
                        column.CurrentValue = sample.purPrice.ToString();
                        return true;
                    }
                    return true;

                }*/)
                .Map<NewGoodsExcel>("实际进价", o => o.actPrice/*, null, (column, source) => // tryPut resolver : Custom logic to put property value into cell.
                {
                    // Custom logic to set the cell value.
                    var sample = (NewGoodsExcel)source;

                    var index = column.Attribute.Index;
                    if ((index == 7))
                    {
                        column.CurrentValue = sample.actPrice.ToString();
                        return true;
                    }
                    return true;

                }*/)
                .Map<NewGoodsExcel>("生产厂家", o => o.manufacturer)
                .Map<NewGoodsExcel>("批准文号", o => o.ApprovalNo)
                .Map<NewGoodsExcel>("供应商", o => o.Suppliers)
                .Map<NewGoodsExcel>("创建时间", o => o.createDate)
                .Save(Path.Combine(env.WebRootPath, fileName), users, "newSheet", overwrite: true,xlsx:false);

            byte[] fileBytes = System.IO.File.ReadAllBytes(Path.Combine(env.WebRootPath, fileName));
            System.IO.File.Delete(Path.Combine(env.WebRootPath, fileName));

            return File(fileBytes, "application/ms-excel", DateTime.Now.ToString("MMddHHmmss") + ".xls");
        }


        [AuthPermission]
        [HttpPost]
        [Route("editgoods")]
        public JsonResult EditGoods(string staffid, [FromBody] Temp entity)
        {
            var userid = getStaffUserid(staffid);
            //判断参数是否合法
            if (string.IsNullOrEmpty(userid))
            {
                return Json(new
                {
                    success = 0,
                    msg = "没有登陆"
                });
            }
            var sql = "";
            var param = new List<SqlParameter>();
            if (entity.goodsCode == "0")
            {
                sql =
               $"update NEWGOOD set goodsName=@goodsName,approvalNo=@approvalNo,goodsSpec=@goodsSpec,manufacturer=@manufacturer," +
               $"Cnum=@Cnum,Price=@Price,suppliersId=@suppliersId,comment4sup=@comment4sup where id = @id";
                param.Add(new SqlParameter("goodsName", entity.goodsName));
                param.Add(new SqlParameter("approvalNo", entity.approvalNo));
                param.Add(new SqlParameter("goodsSpec", entity.goodsSpec));
                param.Add(new SqlParameter("manufacturer", entity.manufacturer));

                param.Add(new SqlParameter("Cnum", entity.Cnum));
                param.Add(new SqlParameter("Price", entity.Price));
                param.Add(new SqlParameter("suppliersId", entity.SuppliersId));
                param.Add(new SqlParameter("comment4sup", entity.Comment4sup));

                param.Add(new SqlParameter("id", entity.Id));
            }
            else {
                sql =
                $"update NEWGOOD set Cnum=@Cnum,Price=@Price,suppliersId=@suppliersId,comment4sup=@comment4sup where id = @id";
                param.Add(new SqlParameter("Cnum", entity.Cnum));
                param.Add(new SqlParameter("Price", entity.Price));
                param.Add(new SqlParameter("suppliersId", entity.SuppliersId));
                param.Add(new SqlParameter("comment4sup", entity.Comment4sup));
                param.Add(new SqlParameter("id", entity.Id));
            }

            //DataAccess.ExecuteCommand(sql, param, out var msg);
            List<DataAccessCommand> list = new List<DataAccessCommand>(1);
            list.Add(new DataAccessCommand(sql, param,CommandType.Text,false));
            bool success = DataAccess.ExecuteBatchCommands(list, out var msg);
            return Json(new
            {
                success = success,
                msg
            });
        }

    }
}