test/Member/SupplierWeb/Controllers/StockController.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Tasks;
using JCSoft.WX.Framework.Api;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Caching.Memory;
using Newtonsoft.Json;
using ZcPeng.PublicLibrary;
using Common.Wechat;
using System.Data.SqlClient;
using System.Linq.Dynamic.Core;
using CoreEntity.Entity;
using Common.Model;
using Newtonsoft.Json.Converters;
using PublicLibrary.Model;
using SupplierWeb.Codes.EF;
using SupplierWeb.Codes.mvc;

namespace SupplierWeb.Controllers
{
    [Route("web/stock")]
    [ApiController]
    public class StockController : BaseController
    {
        public StockController(IMemoryCache cache, IApiClient client) : base(cache, client)
        {

        }

        [AuthPermission]
        [HttpPost]
        [Route("getData")]
        public JsonResult GetData(string[] filters, Int32 pageIndex, Int32 pageSize,
            string sortField, Int32 sortDirection, string[] sumFields, [FromBody]dynamic data)
        {
            var userid = getStaff("userid");
            //判断参数是否合法
            if (string.IsNullOrEmpty(userid))
            {

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

            //var db = new LJHYBZKContext();
            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 ? "asc" : "desc";

            var queryCondition = "1=1 ";
            var param = new List<SqlParameter>();
            
            var start = (pageIndex - 1) * pageSize;
            var end = (start + 1 + pageSize);
            
            if (filterList != null)
            {
                queryCondition = QueryFilter.getFilterSqlParam(filterList.ToArray(),out param,new Egbalance());

            }

            //var result = db.Egbalance.Join(db.Goodsdoc, x => x.GoodsId, y => y.GoodsId, (x, y) => new
            //{
            //    x.PlacePack,
            //    x.PlaceNum,
            //    x.StorPack,
            //    x.StorNum,
            //    x.StorMax,
            //    x.StorMin,
            //    x.GoodsId,
            //    y.GoodsName
            //}).Where(queryCondition, param.ToArray()).OrderBy(sortField + (isAce ? " asc" : " desc")).Skip(pageSize * (pageIndex - 1)).Take(pageSize);

            var dt = new DataTable();
            var sql =
                $"select * from ";
            var sql1 = $"(select A.*," +
                $"B.GoodsName," +
                $"row_number() over (order by A.{sortField} {isAce}) as rowNum " +
                $"from  OgBalance A " +
                $" left join OrgDoc A1 on A1.OrgId = A.OwnerId and A1.EntId = A.EntId " +
                $" left join GoodsDoc B on B.GoodsId = A.GoodsId and B.EntId = A.EntId " +
                $" where 1=1 " +
                $" and A.EntId = 'E1WB67UEYPG' " +
                $" and A1.K_UserId = {userid} " +
                $"{queryCondition} " +
                $") R";
            var sql2 = $" where rowNum > {start} and rowNum < {end}";
            DataAccess.GetValues(sql + sql1 + sql2, ref dt, param.ToArray(), out var msg);

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


            var jsonData = JsonConvert.SerializeObject(result);

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

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