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

namespace SupplierWeb.Controllers
{
    /// <summary>
    /// 订单物流汇总，明细，
    /// </summary>
    [Route("web/purchaseorder")]
    public class PurchaseOrderController : BaseController
    {
        public PurchaseOrderController(IMemoryCache cache, IApiClient client) : base(cache, client)
        {

        }

        ////查看采购单的发货状态，查看物流进度  已发货
        [AuthPermission]
        [HttpPost, Route("ordershipmentlist/{staffId}")]
        public JsonResult ordershipmentlist(QueryFilter[] filters, Int32 pageIndex, Int32 pageSize,
            string sortField, Int32 sortDirection, string[] sumFields,
            string staffId,
            [FromBody]dynamic data)
        {
            string userids;

            userids = getStaff("userid");
            string RoleId = getStaff("roleid");
            //判断参数是否合法
            if (string.IsNullOrEmpty(userids))
            {

                return Json(new
                {
                    success = 0,
                    msg = "没有登陆"
                });
            }
            int PushAccountId = Convert.ToInt32(userids);

            if (data != null)
            {
                //Newtonsoft.Json.Linq.JArray
                filters = data.filters.ToObject<QueryFilter[]>();
                pageIndex = data.pageIndex;
                pageSize = data.pageSize;
                sortField = data.sortField;
                sortDirection = data.sortDirection;
                sumFields = data.sumFields.ToObject<string[]>();
            }

            List<SqlParameter> parameters1 = new List<SqlParameter>();
            string filterstring = QueryFilter.getFilterSqlParam(filters, out parameters1, new PurOrderMTEx(), "A.");
            filterstring += " and exists (select PurOrderBillNo from " + Config.TablePrefix + "PurOrderShipment where PurOrderBillNo = A.BillNo) ";
            filterstring = filterstring.Replace("and A.StaffName", "and C.StaffName");
            filterstring = filterstring.Replace("and A.Contact", "and F.Contact");
            filterstring = filterstring.Replace("and A.BusinessName", "and E.BusinessName");

            string StaffDocId = StaffDocDAL.GetStaffId(userids);
            string purroleFilter = FilterRuleByPur.getRolePermFilter(RoleId, " and K.SaleManId = '" + StaffDocId + "'  ");

            #region 获取订单
            DataTable dt = new DataTable();
            string result = string.Empty;

            string direct = " desc ";
            if (sortDirection != 1)
                direct = " asc";
            int start = (pageIndex - 1) * pageSize;
            int end = (start + 1 + pageSize);
            string commandText0 = "select * from (";
            string commandText1 =
                "select A.*,B.AccountRealName,C.StaffName,E.BusinessName,F.Contact,row_number() over" +
                "(  order by A." + sortField + " " + direct + " )  as rownum" +
                "  from  PurOrderMT A " +
                " left join " + Config.TablePrefix + "Account B on A.SaleManId=B.PurStaffId" +
                " left join StaffDoc C on A.SaleManId=C.StaffId " +
                " left join supplyDoc D on A.SuppliersId=D.SuppliersId" +
                " left join BusinessDoc E on E.BusinessId=D.SuppliersId and E.Is_Supp = 'Y' " +
                " left join ContactDoc F on F.ContactId=A.K_ContactId" +
                //" left join ContactDoc F1 on F1.ContactId=A.OppContId" +
                " where 1=1 " +
                " and exists (select K.ContactId from K_contactsp K  where K.ContactId = A.K_ContactId  and K.EntId = A.EntId   " +
                purroleFilter +
                ")" +
                filterstring +
                ")AAA";
            string commandText2 =
                " where AAA.rownum>" + start + " and AAA.rownum<" + end
                ;
            string commandText = commandText0 + commandText1 + commandText2;

            bool result1 = DataAccess.GetValues(commandText, ref dt, parameters1.ToArray(), out result);
            IList<PurOrderMTEx> users = new List<PurOrderMTEx>();
            if (dt != null && dt.Rows.Count > 0)
            {
                // 把DataTable转换为IList<UserInfo>  
                users = ModelConvertHelper<PurOrderMTEx>.ConvertToModel(dt);
            }
            #endregion

            string resultrow;
            string commandTextCount = "select count(*) from (" + commandText1;
            long totalcount = DataAccess.GetRowCountDefine(commandTextCount, parameters1.ToArray(), out resultrow);

            IsoDateTimeConverter timejson = new IsoDateTimeConverter
            {
                DateTimeFormat = "yyyy'-'MM'-'dd' 'HH':'mm':'ss"
            };
            var jsonData = JsonConvert.SerializeObject(users, timejson);

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

        //订单明细 
        [AuthPermission]
        [HttpPost, Route("orderdtshipmentlist/{staffId}")]
        public JsonResult orderdtshipmentlist(QueryFilter[] filters, Int32 pageIndex, Int32 pageSize,
            string sortField, Int32 sortDirection, string[] sumFields,
            string staffId,
            [FromBody]dynamic data)
        {
            string userids;

            userids = getStaffUserid(staffId);
            string RoleId = getStaff("roleid");
            //判断参数是否合法
            if (string.IsNullOrEmpty(userids))
            {

                return Json(new
                {
                    items = new string[] { },
                    sum = new { },
                    totalCount = 0
                });
            }

            if (data != null)
            {
                //Newtonsoft.Json.Linq.JArray
                filters = data.filters.ToObject<QueryFilter[]>();
                pageIndex = data.pageIndex;
                pageSize = data.pageSize;
                sortField = data.sortField;
                sortDirection = data.sortDirection;
                sumFields = data.sumFields.ToObject<string[]>();
            }
            int PushAccountId = Convert.ToInt32(userids);
            //if (filters.Length == 0)
            //{
            //    return Json(new
            //    {
            //        items = new string[] {  },
            //        sum = new { },
            //        totalCount = 0
            //    });
            //}

            List<SqlParameter> parameters1 = new List<SqlParameter>();
            string filterstring = QueryFilter.getFilterSqlParam(filters, out parameters1, new PurOrderDTEx(), "G.");
            string StaffDocId = StaffDocDAL.GetStaffId(userids);
            string purroleFilter = FilterRuleByPur.getRolePermFilter(RoleId, " and K.SaleManId = '" + StaffDocId + "'  ");

            filterstring = filterstring.Replace("and G.GoodsName", "and H.GoodsName");
            filterstring = filterstring.Replace("and G.GoodsCode", "and H.GoodsCode");
            filterstring = filterstring.Replace("and G.StaffName", "and C.StaffName");
            filterstring = filterstring.Replace("and G.Contact", "and F.Contact");
            filterstring = filterstring.Replace("and G.BusinessName", "and E.BusinessName");


            #region 获取订单
            DataTable dt = new DataTable();
            string result = string.Empty;

            string direct = " desc ";
            if (sortDirection != 1)
                direct = " asc";
            int start = (pageIndex - 1) * pageSize;
            int end = (start + 1 + pageSize);
            string commandText0 = "select * from (";
            string commandText1 =
               "select G.*,A.OppContId,A.SuppliersId,B.AccountRealName,C.StaffName," +
               "E.BusinessName,F.Contact," +
               "H.GoodsName,H.GoodsCode,H.GoodsSpec,H.Manufacturer,X.k_kdcode," +
               "row_number() over" +
                "(  order by G." + sortField + " " + direct + " )  as rownum" +
                "  from  PurOrderDT G " +
                " left join PurOrderMT A on G.BillNo = A.BillNo and A.EntId = G.EntId " +
                " left join GoodsDoc H on H.GoodsId=G.GoodsId and H.EntId = G.EntId " +
                " left join " + Config.TablePrefix + "Account B on A.SaleManId=B.PurStaffId" +
                " left join StaffDoc C on A.SaleManId=C.StaffId  and C.EntId = A.EntId " +
                //" left join supplydoc D on A.SuppliersId=D.SuppliersId and D.EntId = A.EntId " +
                " left join BusinessDoc E on E.BusinessId=A.SuppliersId and E.Is_Supp = 'Y'  and E.EntId = A.EntId  " +
                " left join ContactDoc F on F.ContactId=A.K_ContactId and F.EntId = A.EntId " +
                //" left join ContactDoc F1 on F1.ContactId=A.OppContId" +
                "left join sup_PurShipmentOrderDT Z on G.BillNo = Z.BillNo and g.BillSn=z.BillSn left join sup_PurOrderShipment X on Z.ShipmentId=X.Id " +
                " where 1=1 " +
                "and exists (select K.ContactId from K_contactsp K where K.ContactId = A.K_ContactId  and K.EntId = A.EntId " +
                purroleFilter +
                ")" + 
                filterstring +
                ")AAA";
            string commandText2 =
                " where AAA.rownum>" + start + " and AAA.rownum<" + end
                ;
            string commandText = commandText0 + commandText1 + commandText2;
            

            if (!parameters1.Any())
            {
                return Json(new
                {
                    items = new string[] { "" },
                    sum = new { },
                    totalCount = 0
                });
            }
            bool result1 = DataAccess.GetValues(commandText, ref dt, parameters1.ToArray(), out result);
            IList<PurOrderDTEx> users = new List<PurOrderDTEx>();
            if (dt != null && dt.Rows.Count > 0)
            {
                // 把DataTable转换为IList<UserInfo>  
                users = ModelConvertHelper<PurOrderDTEx>.ConvertToModel(dt);
            }
            #endregion

            string resultrow;
            string commandTextCount = "select count(*) from (" + commandText1;
            long totalcount = DataAccess.GetRowCountDefine(commandTextCount, parameters1.ToArray(), out resultrow);

            IsoDateTimeConverter timejson = new IsoDateTimeConverter
            {
                DateTimeFormat = "yyyy'-'MM'-'dd' 'HH':'mm':'ss"
            };
            var jsonData = JsonConvert.SerializeObject(users, timejson);

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

        ////查询采购订单（生成的，提交的）未发货
        [AuthPermission]
        [HttpPost, Route("purchaseorderlist")]
        public JsonResult purchaseorderlist(QueryFilter[] filters, Int32 pageIndex, Int32 pageSize,
            string sortField, Int32 sortDirection, string[] sumFields,
            string staffId,
            [FromBody]dynamic data)
        {
            string userids;

            userids = getStaff("userid");
            string RoleId = getStaff("roleid");
            //判断参数是否合法
            if (string.IsNullOrEmpty(userids))
            {

                return Json(new
                {
                    success = 0,
                    msg = "没有登陆"
                });
            }
            int PushAccountId = Convert.ToInt32(userids);


            if (data != null)
            {
                //Newtonsoft.Json.Linq.JArray
                filters = data.filters.ToObject<QueryFilter[]>();
                pageIndex = data.pageIndex;
                pageSize = data.pageSize;
                sortField = data.sortField;
                sortDirection = data.sortDirection;
                sumFields = data.sumFields.ToObject<string[]>();
            }
            List<SqlParameter> parameters1 = new List<SqlParameter>();
            string filterstring = QueryFilter.getFilterSqlParam(filters, out parameters1, new PurOrderMTEx(), "A.");
            filterstring += " and not exists (select PurOrderBillNo from " + Config.TablePrefix + "PurOrderShipment where PurOrderBillNo = A.BillNo) ";
            filterstring = filterstring.Replace("and A.StaffName", "and C.StaffName");
            filterstring = filterstring.Replace("and A.Contact", "and F.Contact");
            filterstring = filterstring.Replace("and A.BusinessName", "and E.BusinessName");
            string StaffDocId = StaffDocDAL.GetStaffId(userids);
            string purroleFilter = FilterRuleByPur.getRolePermFilter(RoleId, " and K.SaleManId = '" + StaffDocId + "'  ");

            #region 获取订单
            DataTable dt = new DataTable();
            string result = string.Empty;

            string direct = " desc ";
            if (sortDirection != 1)
                direct = " asc";
            int start = (pageIndex - 1) * pageSize;
            int end = (start + 1 + pageSize);
            string commandText0 = "select * from (";
            string commandText1 =
               "select A.*,B.AccountRealName,C.StaffName,E.BusinessName,F.Contact,row_number() over" +
                "(  order by A." + sortField + " " + direct + " )  as rownum" +
                "  from  PurOrderMT A " +
                " left join " + Config.TablePrefix + "Account B on A.SaleManId=B.PurStaffId" +
                " left join StaffDoc C on A.SaleManId=C.StaffId  and A.EntId = C.EntId " +
                //" left join supplyDoc D on A.SuppliersId=D.SuppliersId  and A.EntId = D.EntId " +
                " left join BusinessDoc E on E.BusinessId=A.SuppliersId and E.Is_Supp = 'Y'  and E.EntId = A.EntId  " +
                " left join ContactDoc F on F.ContactId=A.K_ContactId  and A.EntId = F.EntId " +
                //" left join ContactDoc F1 on F1.ContactId=A.OppContId" +
                " where 1=1 " +
                "and exists (select K.ContactId from K_contactsp K where A.K_ContactId = K.ContactId and K.EntId = A.EntId  " +
                purroleFilter +
                ")" +
                filterstring +
                ")AAA";
            string commandText2 =
                " where AAA.rownum>" + start + " and AAA.rownum<" + end
                ;
            string commandText = commandText0 + commandText1 + commandText2;

            bool result1 = DataAccess.GetValues(commandText, ref dt, parameters1.ToArray(), out result);
            IList<PurOrderMTEx> users = new List<PurOrderMTEx>();
            if (dt != null && dt.Rows.Count > 0)
            {
                // 把DataTable转换为IList<UserInfo>  
                users = ModelConvertHelper<PurOrderMTEx>.ConvertToModel(dt);
            }
            #endregion

            string resultrow;
            string commandTextCount = "select count(*) from (" + commandText1;
            long totalcount = DataAccess.GetRowCountDefine(commandTextCount, parameters1.ToArray(), out resultrow);

            IsoDateTimeConverter timejson = new IsoDateTimeConverter
            {
                DateTimeFormat = "yyyy'-'MM'-'dd' 'HH':'mm':'ss"
            };
            var jsonData = JsonConvert.SerializeObject(users, timejson);

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

        ////查询采购订单明细 
        [AuthPermission]
        [HttpPost, Route("purchaseorderdtlist/{staffId}")]
        public JsonResult purchaseorderdtlist(QueryFilter[] filters, Int32 pageIndex, Int32 pageSize,
            string sortField, Int32 sortDirection, string[] sumFields,
            string staffId,
            [FromBody]dynamic data)
        {
            string userids;

            userids = getStaffUserid(staffId);
            string RoleId = getStaff("roleid");
            //判断参数是否合法
            if (string.IsNullOrEmpty(userids))
            {

                return Json(new
                {
                    items = new string[] { },
                    sum = new { },
                    totalCount = 0
                });
            }

            if (data != null)
            {
                //Newtonsoft.Json.Linq.JArray
                filters = data.filters.ToObject<QueryFilter[]>();
                pageIndex = data.pageIndex;
                pageSize = data.pageSize;
                sortField = data.sortField;
                sortDirection = data.sortDirection;
                sumFields = data.sumFields.ToObject<string[]>();
            }
            int PushAccountId = Convert.ToInt32(userids);
            if (filters.Length == 0)
            {
                return Json(new
                {
                    items = new string[] { },
                    sum = new { },
                    totalCount = 0
                });
            }

            List<SqlParameter> parameters1 = new List<SqlParameter>();
            string filterstring = QueryFilter.getFilterSqlParam(filters, out parameters1, new PurOrderDTEx(), "G.");
            filterstring = filterstring.Replace("and G.GoodsName", "and H.GoodsName");
            filterstring = filterstring.Replace("and G.GoodsCode", "and H.GoodsCode");
            filterstring = filterstring.Replace("and G.StaffName", "and C.StaffName");
            filterstring = filterstring.Replace("and G.Contact", "and F.Contact");
            filterstring = filterstring.Replace("and G.BusinessName", "and E.BusinessName");
            string StaffDocId = StaffDocDAL.GetStaffId(userids);
            string purroleFilter = FilterRuleByPur.getRolePermFilter(RoleId, " and K.SaleManId = '" + StaffDocId + "'  ");

            #region 获取订单
            DataTable dt = new DataTable();
            string result = string.Empty;

            string direct = " desc ";
            if (sortDirection != 1)
                direct = " asc";
            int start = (pageIndex - 1) * pageSize;
            int end = (start + 1 + pageSize);
            string commandText0 = "select * from (";
            string commandText1 =
               "select G.*,A.OppContId,A.SuppliersId,B.AccountRealName,C.StaffName," +
               "E.BusinessName,F.Contact," +
               "H.GoodsName,H.GoodsCode,H.GoodsSpec,H.Manufacturer," +
               "row_number() over" +
                "(  order by G." + sortField + " " + direct + " )  as rownum" +
                "  from  PurOrderDT G left join PurOrderMT A on G.BillNo = A.BillNo  and A.EntId = G.EntId " +
                " left join GoodsDoc H on H.GoodsId=G.GoodsId  and H.EntId = G.EntId " +
                " left join " + Config.TablePrefix + "Account B on A.SaleManId=B.PurStaffId" +
                " left join StaffDoc C on A.SaleManId=C.StaffId and A.EntId = C.EntId " +
                " left join supplydoc D on A.SuppliersId=D.SuppliersId  and A.EntId = D.EntId " +
                " left join BusinessDoc E on E.BusinessId=D.SuppliersId and E.Is_Supp = 'Y'  and E.EntId = D.EntId " +
                " left join ContactDoc F on F.ContactId=A.K_ContactId  and A.EntId = F.EntId " +
                //" left join ContactDoc F1 on F1.ContactId=A.OppContId" +
                "and exists (select K.ContactId from K_contactsp K where A.K_ContactId = K.ContactId and K.EntId = A.EntId  " +
                purroleFilter +
                ")" +
                " where 1=1 " +
                filterstring +
                ")AAA";
            string commandText2 =
                " where AAA.rownum>" + start + " and AAA.rownum<" + end
                ;
            string commandText = commandText0 + commandText1 + commandText2;

            bool result1 = DataAccess.GetValues(commandText, ref dt, parameters1.ToArray(), out result);
            IList<PurOrderDTEx> users = new List<PurOrderDTEx>();
            if (dt != null && dt.Rows.Count > 0)
            {
                // 把DataTable转换为IList<UserInfo>  
                users = ModelConvertHelper<PurOrderDTEx>.ConvertToModel(dt);
            }
            #endregion

            string resultrow;
            string commandTextCount = "select count(*) from (" + commandText1;
            long totalcount = DataAccess.GetRowCountDefine(commandTextCount, parameters1.ToArray(), out resultrow);

            IsoDateTimeConverter timejson = new IsoDateTimeConverter
            {
                DateTimeFormat = "yyyy'-'MM'-'dd' 'HH':'mm':'ss"
            };
            var jsonData = JsonConvert.SerializeObject(users, timejson);

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

        ////查看采购单的退货汇总
        [AuthPermission]
        [HttpPost, Route("refund")]
        public ActionResult Refund(QueryFilter[] filters, Int32 pageIndex, Int32 pageSize,
            string sortField, Int32 sortDirection, string[] sumFields,
            [FromBody]dynamic data)
        {
            string userids;

            userids = getStaff("userid");
            string RoleId = getStaff("roleid");
            //判断参数是否合法
            if (string.IsNullOrEmpty(userids))
            {

                return Json(new
                {
                    items = new string[] { },
                    sum = new { },
                    totalCount = 0
                });
            }

            if (data != null)
            {
                //Newtonsoft.Json.Linq.JArray
                filters = data.filters.ToObject<QueryFilter[]>();
                pageIndex = data.pageIndex;
                pageSize = data.pageSize;
                sortField = data.sortField;
                sortDirection = data.sortDirection;
                sumFields = data.sumFields.ToObject<string[]>();
            }

            List<SqlParameter> param = new List<SqlParameter>();
            string filterstring = QueryFilter.getFilterSqlParam(filters, out param, new PurOrderDTEx(), "A.");
            string ruleFilter = FilterTranslator.ruleSql(ref param);

            #region 获取订单
            DataTable dt = new DataTable();
            string result = string.Empty;

            string direct = " desc ";
            if (sortDirection != 1)
                direct = " asc";
            int start = (pageIndex - 1) * pageSize;
            int end = (start + 1 + pageSize);

            sortField = "A." + sortField;
            string commandText0 = "select * from (";
            string commandText1 =
               "select C.Contact as OppContact,B.OppContId,D.StaffName,B.SaleManId,A.*,row_number() over" +
                "(  order by " + sortField + " " + direct + " )  as rownum" + 
               " from GspOutMT A "+
               " left join PurNotesMT B on B.BillCode = A.K_BillCode and B.EntId = A.EntId" +
               " and A.RuleId = '5tx65yncahy1l91x'" +
               " left join ContactDoc C on B.OppContId = C.ContactId  and B.EntId = C.EntId" +
               " left join StaffDoc D on D.STAFFID = B.SaleManId  and D.EntId = B.EntId" +
               " where 1 = 1" +
               " and A.RuleId = @RuleId " +//5tx65yncahy1l91x
               filterstring +
                ruleFilter +
                ")AAA";
            string commandText2 =
                " where AAA.rownum>" + start + " and AAA.rownum<" + end
                ;
            string commandText = commandText0 + commandText1 + commandText2;
            param.Add(new SqlParameter("@RuleId", "5tx65yncahy1l91x"));

            bool result1 = DataAccess.GetValues(commandText, ref dt, param.ToArray(), out result);
            IList<GspOutMT> users = new List<GspOutMT>();
            if (dt != null && dt.Rows.Count > 0)
            {
                // 把DataTable转换为IList<UserInfo>  
                users = ModelConvertHelper<GspOutMT>.ConvertToModel(dt);
            }
            #endregion

            string resultrow;
            string commandTextCount = "select count(*) from (" + commandText1;
            long totalcount = DataAccess.GetRowCountDefine(commandTextCount, param.ToArray(), out resultrow);

            IsoDateTimeConverter timejson = new IsoDateTimeConverter
            {
                DateTimeFormat = "yyyy'-'MM'-'dd' 'HH':'mm':'ss"
            };
            var jsonData = JsonConvert.SerializeObject(users, timejson);

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

        ////查看采购单的退回明细
        [AuthPermission]
        [HttpPost,Route("refunddt")]
        public ActionResult RefundDT(QueryFilter[] filters, Int32 pageIndex, Int32 pageSize,
            string sortField, Int32 sortDirection, string[] sumFields,
            [FromBody]dynamic data)
        {
            string userids;

            userids = getStaff("userid");
            string RoleId = getStaff("roleid");
            //判断参数是否合法
            if (string.IsNullOrEmpty(userids))
            {

                return Json(new
                {
                    items = new string[] { },
                    sum = new { },
                    totalCount = 0
                });
            }

            if (data != null)
            {
                //Newtonsoft.Json.Linq.JArray
                filters = data.filters.ToObject<QueryFilter[]>();
                pageIndex = data.pageIndex;
                pageSize = data.pageSize;
                sortField = data.sortField;
                sortDirection = data.sortDirection;
                sumFields = data.sumFields.ToObject<string[]>();
            }

            List<SqlParameter> param = new List<SqlParameter>();
            string filterstring = QueryFilter.getFilterSqlParam(filters, out param, new PurOrderDTEx(), "A.");
            filterstring = filterstring.Replace("and A.GoodsName", "and E.GoodsName");
            filterstring = filterstring.Replace("and A.ApprovalNo", "and E1.ApprovalNo");
            

            string ruleFilter = FilterTranslator.ruleSql(ref param);

            #region 获取订单
            DataTable dt = new DataTable();
            string result = string.Empty;

            string direct = " desc ";
            if (sortDirection != 1)
                direct = " asc";
            int start = (pageIndex - 1) * pageSize;
            int end = (start + 1 + pageSize);

            sortField = "A." + sortField;
            sortField = sortField.Replace("A.PK", "concat(A.BillNo, '-', A.BillSn)");
            sortField = sortField.Replace("A.GoodsName", "E.GoodsName");
            sortField = sortField.Replace("A.ApprovalNo", "E1.ApprovalNo");
            string commandText0 = "select * from (";
            string commandText1 =
               "select concat(A.Billno,'-',A.BillSn) as PK," +
               //"C.Contact as OppContact,D.StaffName," +
               "B1.SaleManId," +
               "B1.OppContId," +
               "E.GoodsName," +
               "E1.ApprovalNo," +
               "A.*,row_number() over" +
                "(  order by " + sortField + " " + direct + " )  as rownum" +
               " from GspOutDT A" +
               " left join GspOutMT A1 on A.BillNo = A1.BillNo and A.EntId = A1.EntId" +
               " and A1.RuleId = '5tx65yncahy1l91x' left join PurNotesMT B1 on B1.BillCode = A.RfBillCode  and B1.EntId = A.EntId" +
               //" left join ContactDoc C on C.ContactId = B1.OppContId and C.EntId = B1.EntId" +
               //" left join StaffDoc D on D.STAFFID = B1.SaleManId and D.EntId = B1.EntId" +
               " left join GoodsDoc E on E.GoodsId = A.GoodsId and E.EntId = A.EntId" +
               " left join GoodsAttr E1 on E1.GoodsId = E.GoodsId and E1.EntId = E.EntId" +
               " where 1 = 1" +
               " and A1.RuleId = @RuleId " +//5tx65yncahy1l91x
               filterstring +
                ruleFilter +
                ")AAA";
            string commandText2 =
                " where AAA.rownum>" + start + " and AAA.rownum<" + end
                ;
            string commandText = commandText0 + commandText1 + commandText2;
            param.Add(new SqlParameter("@RuleId", "5tx65yncahy1l91x"));

            bool result1 = DataAccess.GetValues(commandText, ref dt, param.ToArray(), out result);
            IList<GspOutDT> users = new List<GspOutDT>();
            if (dt != null && dt.Rows.Count > 0)
            {
                // 把DataTable转换为IList<UserInfo>  
                users = ModelConvertHelper<GspOutDT>.ConvertToModel(dt);
            }
            #endregion

            string resultrow;
            string commandTextCount = "select count(*) from (" + commandText1;
            long totalcount = DataAccess.GetRowCountDefine(commandTextCount, param.ToArray(), out resultrow);

            IsoDateTimeConverter timejson = new IsoDateTimeConverter
            {
                DateTimeFormat = "yyyy'-'MM'-'dd' 'HH':'mm':'ss"
            };
            var jsonData = JsonConvert.SerializeObject(users, timejson);

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

}