MemberCenter/Member/MemberWeb/Controllers/PushRecordController.cs

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;
using ZcPeng.PublicLibrary;
using CoreEntity.Entity;
using Common.Model;
using Newtonsoft.Json.Converters;
using Newtonsoft.Json;
using System.Data.SqlClient;
using CoreEntity.TimedTask;
using ZcPeng.weixin.PublicAccount;
using CoreEntity.DAL;
using CoreEntity.TimeJob;
using PublicLibrary.Model;
using Microsoft.Extensions.Caching.Memory;
using PublicLibrary.Common;
using CoreEntity.BAL;
using SupplierWeb.Codes.mvc;
using SupplierWeb.Codes.Push;
using Npoi.Mapper;
using System.IO;
using Microsoft.AspNetCore.Hosting;
using LigerRM.Common;

namespace SupplierWeb.Controllers
{
    [Route("web/push")]
    public class PushRecordController : BaseController
    {
        public PushRecordController(IMemoryCache cache, IApiClient client) : base(cache, client)
        {
        }

        //查看有缺口的药品给关注的供应商业务员推送记录
        [AuthPermission]
        [HttpPost, Route("pushrecord/{staffId}")]
        public ActionResult PushRecord(QueryFilter[] filters, Int32 pageIndex, Int32 pageSize,
            string sortField, Int32 sortDirection, string[] sumFields,
            string staffId,
            [FromBody]dynamic data,
            bool timeout = false,
            bool belowlimit = false,
            bool all = false)
        {
            string userids = getStaff("userid");
            string RoleId = getStaff("roleid");
            //判断参数是否合法
            if (string.IsNullOrEmpty(userids))
            {

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

            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[]>();
            }
            #region 获取推送记录
            DataTable dt = new DataTable();
            string result;

            //filter参数
            string filterstr = "";
            List<SqlParameter> parameters = new List<SqlParameter>();
            filterstr += QueryFilter.getFilterSqlParam(filters, out parameters, new PushRecordExt(), "B.");
            filterstr = filterstr.Replace("B.AccountRealName", "isnull(C.AccountRealName,D.Contact)");
            if (timeout)
                filterstr += " and datediff( hour, B.CreationTime,  getdate() ) >= 6";
            if (belowlimit)
                filterstr += " and exists (select A1.PushRecordId from " + Config.TablePrefix + "PushFeedback A1 where A1.PushRecordId = B.Id and A1.State=2)";
            if (all)
                filterstr += "";

            //string filterPurrole = FilterRuleByPur.getRolePermFilter(RoleId, " and B.CreationPerson = '" + userids + "'  ");
            string StaffDocId = StaffDocDAL.GetStaffId(userids);
            //string filterPurrole = FilterRuleByPur.getRolePermFilter(RoleId, " and B.ContactId in " +
            //    "(select K1.ContactId from K_contactsp K1 " +//left join ContactDoc A1 on K1.ContactId = A1.ContactId and A1.EntId = K1.EntId 
            //    " where K1.SaleManId = '" + StaffDocId + "')");

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

            string filterPurrole = FilterTranslator.ruleSql(ref parameters);

            string commandText0 = "select * from ";
            string commandText1 = "(" +
                "select B.*,isnull(C.AccountRealName,D.Contact) as AccountRealName," +
                "row_number() over" +
                "(  order by " + sortField + " " + direct + " )  as rownum from " +
                Config.TablePrefix + "PushRecord as B " +
                " left join sup_Account C on B.PushAccountId = C.Id " +
                " left join ContactDoc D on D.EntId = B.EntId and  D.ContactId = B.ContactId" +
                " where  1=1 " +
                    //"exists(" + " select * from " + Config.TablePrefix + "PushFeedback A " +
                    //    "  where A.PushRecordId = B.Id)" +
                    filterstr +
                    //"and K1.SaleManId = 'K2L78RVHTD7'" +
                    filterPurrole +
            ")AAA ";
            string commandText = commandText0 + commandText1 + " where AAA.rownum>" + start + " and AAA.rownum<" + end;

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

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

            string result1;

            string commandTextCount = "select count(Id) from " + commandText1;

            long totalcount = DataAccess.GetRowCountDefine(commandTextCount, parameters.ToArray(), out result1);

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

        //查看供应商业务员提交的供货情况--供应商业务员未提交的
        [AuthPermission]
        [HttpPost, Route("pushfeedback/{state}/{staffId}")]
        public ActionResult PushFeedback(QueryFilter[] filters, Int32 pageIndex, Int32 pageSize,
            string sortField, Int32 sortDirection, string[] sumFields,
            String state,
            string staffId,
            [FromBody]dynamic data)
        {
            string userids = getStaff("userid");
            string RoleId = getStaff("roleid");
            //判断参数是否合法
            if (string.IsNullOrEmpty(userids))
            {

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

            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[]>();
            }
            #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 filter = string.Empty;
            if (state == null || state == "")
                filter = " and " + " A.State IS NULL OR A.State in (0,5) ";
            else if (state == "0,5")
                filter = " and " + " A.State IS NULL OR A.State in (0,5)";
            else if (state == "5,6")
                filter = " R A.State in (5,6)";
            else if (state == "0,5,6")
                filter = " and " + " A.State IS NULL OR A.State in (0,5,6)";
            else if (state == "999")
                filter = " and " + " A.State in (0,5,6) and  datediff( hour, A.CreationTime,  getdate() ) >= 6";
            else if (state == "1")
                filter = " and " + " A.State = 1";
            else if (state == "1,2")
                filter = " and " + " A.State in (1,2)";
            else if (state == "3")
                filter = " and " + " A.State = 3";
            else if (state == "4")
                filter = " and " + " A.State = 4";
            else if (state == "all")
                filter = "";
            else
                filter = " and " + " A.State = " + state;

            //string filterPurrole = FilterRuleByPur.getRolePermFilter(RoleId, " and A.CreationPerson = '" + userids + "'  ");
            string StaffDocId = StaffDocDAL.GetStaffId(userids);
            //string filterPurrole = FilterRuleByPur.getRolePermFilter(RoleId, " and B.ContactId in " +
            //    "(select K1.ContactId from K_contactsp K1 " +// left join ContactDoc A1 on A1.EntId=K1.EntId and K1.ContactId = A1.ContactId
            //    " where  K1.SaleManId = '" + StaffDocId + "')");


            //filter参数
            List<SqlParameter> parameters1 = new List<SqlParameter>();
            string filterstr = QueryFilter.getFilterSqlParam(filters, out parameters1, new PushFeedbackExt(), "A.");
            if (filterstr.IndexOf("AccountRealName") > 0)
            {
                filterstr = filterstr.Replace("and A.AccountRealName", "and isnull(C.AccountRealName,D0.Contact)");
            }
            else if (filterstr.IndexOf("DiffHour") > 0)
            {
                filterstr = filterstr.Replace("and A.DiffHour", "and CAST(datediff( hour, A.CreationTime,  getdate() ) as decimal)");
            }

            filterstr = filterstr.Replace("and A.CentPayMd", "and D.CentPayMd ");
            filterstr = filterstr.Replace("and A.ReceiptMd", "and D.ReceiptMd ");
            filterstr = filterstr.Replace("and A.RequestDate", "and CONVERT(datetime,D.EndDate,101) ");
            filter += filterstr;
            filter = filter.Replace("A.IsPrepayI", " Case D.IsPrepay When 'Y' Then 1 Else 0 End ");
            filter = filter.Replace("A.PrepayAmt", "D.PrepayAmt");

            string filterPurrole = FilterTranslator.ruleSql(ref parameters1);

            string commandText0 = "select * from (";
            string commandText1 = "select A.*,'' as OrgId,'' as DepId," +
                "isnull(C.AccountRealName,D0.Contact) as AccountRealName," +
                " CAST(datediff( hour, A.CreationTime,  getdate() ) as decimal) as DiffHour, " +
                (state != "3" && state != "4" ? "" : "D.CentPayMd,D.ReceiptMd," +
                "CASE D.EndDate WHEN '0001-01-01' THEN  null ELSE  CONVERT(datetime,D.EndDate,101) END as RequestDate," +
                "Case D.IsPrepay When 'Y' Then 1 Else 0 End IsPrepayI,D.PrepayAmt,"
                ) +
                "'盒' as Unit,F.WmsMeas," +
                "row_number() over" +
            "(  order by A." + sortField + direct + " )  as rownum";
            string commandText2 = "  from " + Config.TablePrefix + "PushFeedback A left join " + Config.TablePrefix + "PushRecord B on A.PushRecordId = B.Id " +
            " left join " + Config.TablePrefix + "Account C on C.Id = A.PushAccountId " +
                " left join ContactDoc D0 on D0.EntId = A.EntId and  D0.ContactId = A.ContactId " +
            (state != "3" && state != "4" ? "" : " left join PurPlanMT D on D.EntId = B.EntId and D.PushRecordId = B.Id  and D.K_ContactId = A.ContactId  and D.BillState=0 ") +
            //" left join PGPrice E on E.EntId = A.EntId  and E.GoodsId = A.GoodsId " +
            " left join GoodsAttr F on F.EntId = A.EntId  and F.GoodsId = A.GoodsId " +
            " where 1 = 1 " +
            (state != "3" && state != "4" ? "" : "  and D.BillState=0 ") +
            filter +
            filterPurrole +
            "";
            string commandText = commandText0 + commandText1 + commandText2 + ")AAA  where AAA.rownum>" + start + " and AAA.rownum<" + end
                 ;


            //string connectstr = "Data Source =192.168.50.30; Initial Catalog = LJHYBZK; Persist Security Info = True; User ID = sa; Password = xq!@#2014;Pooling=true;MAX Pool Size=512;Min Pool Size=50;Connection Lifetime=30";

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

            #endregion
            // 把DataTable转换为IList<UserInfo>  
            IList<PushFeedbackExt> users = new List<PushFeedbackExt>();
            if (dt != null && dt.Rows.Count > 0)
            {
                // 把DataTable转换为IList<UserInfo>  
                users = ModelConvertHelper<PushFeedbackExt>.ConvertToModel(dt);
                foreach (PushFeedbackExt pfbe in users)
                {
                    pfbe.ContactId = pfbe.ContactId + "_" + pfbe.SuppliersId + "_" + pfbe.PushAccountId;
                }
            }

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

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

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

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

            string userids;
            string RoleId = getStaff("roleid");

            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";
            }

            string StaffDocId = StaffDocDAL.GetStaffId(userids);
            string filterPurrole = FilterRuleByPur.getRolePermFilter(RoleId, " and B.PushAccountId in (select userid from K_contactsp K1 left join ContactDoc A1 on K1.ContactId = A1.ContactId " +
                " and K1.SaleManId = '" + StaffDocId + "')");

            List<SqlParameter> parameters2;
            //filter参数
            QueryFilter[] filters = JsonConvert.DeserializeObject<QueryFilter[]>(filter);
            String filterstr1 = QueryFilter.getFilterSqlParam(filters, out parameters2, new Temp());
            //String filterstr = "((State != 0 OR State IS NOT NULL) OR ( State NOT in(5,6)))" +
            //    " AND PushAccountId != " + userids +
            //" AND IsCancel = 0 " + filterstr1;//不包含取消的

            List<List<Object>> parameters = new List<List<Object>>();
            //parameters.Add(new List<Object>() { "PushFbAccountId", Convert.ToInt32(userids) });
            //添加查询条件
            if (!string.IsNullOrEmpty(filter)&&filter!= "[]")
            { 
                parameters.Add(new List<Object>() { filters[0].field+"_0", "%"+filters[0].value+"%" });
            }

            List<SqlParameter> parameters1 = DataAccess.ToParameters(parameters);

            string commandText0 = "select * from (";
            string commandText1 = "select A.*,'' as OrgId,'' as DepId, C.AccountRealName, E.Unit," +
                "row_number() over (order by A.Id " + direct + ")  as rownum" +
            " from " +
                Config.TablePrefix + "PushFeedback A " +
                " left join " + Config.TablePrefix + "PushRecord B on A.PushRecordId = B.Id" +
                " left join " + Config.TablePrefix + "Account C on A.PushAccountId = C.Id" +
                " left join PGPrice E on E.EntId = A.EntId  and E.GoodsId = A.GoodsId" +
            " where 1 = 1 and A.state = '2'" + filterstr1 + ")AAA ";
            //对查询结果进行分页
            string commandText2= "";
            if (!string.IsNullOrEmpty(size)&& !string.IsNullOrEmpty(index))
            {
                if (index == "1")
                {
                    commandText2 = " WHERE rownum>=1 and rownum<="+size;
                }
                else
                { 
                    commandText2 = " WHERE rownum>="+ Convert.ToInt32(index) * Convert.ToInt32(size) +"and rownum<"+Convert.ToInt32(size) * (Convert.ToInt32(index)+1);
                }
            }
            string commandText = commandText0 + commandText1+ commandText2;

            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<PushRecordExcel> users = new List<PushRecordExcel>();
            if (dt != null && dt.Rows.Count > 0)
            {
                // 把DataTable转换为IList<UserInfo>  
                users = ModelConvertHelper<PushRecordExcel>.ConvertToModel(dt);

            }
            #endregion
            var mapper = new Mapper();

            //var fileName = Path.Combine("excel", RecordId + "_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx");
            var fileName = Path.Combine("excel", "_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls");
            mapper
                .Map<PushRecordExcel>("推送时间", o => o.CreationTime)
                .Map<PushRecordExcel>("业务员", o => o.AccountRealName)
                .Map<PushRecordExcel>("商品编码", o => o.GoodsCode)
                .Map<PushRecordExcel>("商品名称", o => o.GoodsName)
                .Map<PushRecordExcel>("业务员备注", o => o.Feedback)
                .Map<PushRecordExcel>("订货数量", o => o.StoreGapNum)
                .Map<PushRecordExcel>("库存缺口总数", o => o.PurchaseTotalNum)
                .Map<PushRecordExcel>("标准进价", o => o.ActPrice)
                .Map<PushRecordExcel>("实际进价", o => o.PurPrice)
                .Map<PushRecordExcel>("供应商编码", o => o.BusinessCode)
                .Map<PushRecordExcel>("品牌名称", o => o.BrandName)
                .Map<PushRecordExcel>("商品规格", o => o.GoodsSpec)
                .Map<PushRecordExcel>("生产厂家", o => o.Manufacturer)
                .Map<PushRecordExcel>("备注", o => o.Remark)
                //.Save(Path.Combine(env.WebRootPath, fileName), users, "newSheet", overwrite: true);
                .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(env.WebRootPath + Path.DirectorySeparatorChar + fileName);

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

        ////手动保存并推送：
        //[HttpPost, Route("saveandpush")]
        //public ActionResult SaveAndPush(
        //     string Id,
        //    string PushAccountId, 
        //    string ContactId,
        //    string GoodsId, string GoodsCode
        //    , string GoodsName, decimal StoreGapNum
        //    , string GoodsSpec, string Manufacturer
        //    , string BusinessId, string BusinessCode, string BusinessName
        //    , string SuppliersId, string BrandName, string BrandId
        //    , decimal PurchaseTotalNum, Decimal PurPrice
        //    , string EntId, string Remark
        //    , string LoginAccountId,
        //    [FromBody]dynamic data)
        //{

        //    string FeedbackId = null;
        //    if (data != null)
        //    {
        //        //Newtonsoft.Json.Linq.JArray
        //        Id = data.Id;
        //        if (Id != null)
        //            FeedbackId = Id;
        //        PushAccountId = data.PushAccountId == null ? null : data.PushAccountId;
        //        if (PushAccountId == null)
        //            PushAccountId = data.UserId == null ? null :data.UserId;
        //        PushAccountId = PushAccountId.Trim();

        //        GoodsId = data.GoodsId;
        //        GoodsCode = data.GoodsCode;
        //        GoodsName = data.GoodsName;
        //        StoreGapNum = data.StoreGapNum == null ? 0 : data.StoreGapNum;

        //        GoodsSpec = data.GoodsSpec;
        //        Manufacturer = data.Manufacturer;
        //        BusinessId = data.BusinessId;
        //        BusinessCode = data.BusinessCode;
        //        BusinessName = data.BusinessName;

        //        SuppliersId = data.SuppliersId;
        //        BrandName = data.BrandName;
        //        BrandId = data.BrandId;

        //        PurchaseTotalNum = data.PurchaseTotalNum == null ? 0 : data.PurchaseTotalNum;
        //        PurPrice = data.PurPrice ==null?0 : data.PurPrice;
        //        BrandId = data.BrandId;
        //        if (PurPrice.Equals(0))
        //            PurPrice = data.LastPrice;

        //        EntId = data.EntId;
        //        Remark = data.Remark;

        //        LoginAccountId = data.LoginAccountId;

        //    }
        //    if (PushAccountId == null || PushAccountId == "")
        //    {

        //        return Json(new
        //        {
        //            success = 0,
        //            msg = "不能推送，请先建立账户!"
        //        });
        //    }


        //    //添加推送记录 
        //    string PushCode = RandHelper.GetTimeRandId().ToString();
        //    IDictionary<string, Object> successs =
        //        PushRecordDAL.AddPushRecord(PushCode,
        //            Convert.ToInt32(PushAccountId),
        //            ContactId,
        //        Remark,LoginAccountId);

        //    Object successRecord = 0;
        //    successs.TryGetValue("successRecord", out successRecord);
        //    Object resultRecord = "";
        //    successs.TryGetValue("resultRecord", out resultRecord);

        //    Object successRecord1 = 0;
        //    Object resultRecord1 = "";
        //    Message msg = null;
        //    if ((int)successRecord > 0)
        //    {
        //        Object recordId;
        //        successs.TryGetValue("PushRecordId", out recordId);
        //        string PushRecordId = ((Guid)recordId).ToString();

        //        //添加或修改推送明细 
        //        IDictionary<string, Object> successs1 =
        //            PushRecordDAL.AddOrUpdatePushFeedBack(
        //                PushCode,
        //                PushRecordId,
        //            FeedbackId,
        //            Convert.ToInt32(PushAccountId),
        //            ContactId,
        //            GoodsId, GoodsCode
        //            , GoodsName, StoreGapNum
        //            , GoodsSpec, Manufacturer
        //            , BusinessId, BusinessCode, BusinessName
        //            , SuppliersId, BrandName, BrandId
        //            , EntId, Remark
        //            , LoginAccountId, PurPrice, PurchaseTotalNum);

        //        successs1.TryGetValue("successRecord", out successRecord1);
        //        successs1.TryGetValue("resultRecord", out resultRecord1);

        //        //手动推送
        //        DateTime EndTime = DateTime.Now.AddHours((double)24);
        //        msg = TimeTask.sendSupplyMassMesage(_client,
        //             Convert.ToInt32(PushAccountId),
        //                            PushRecordId,
        //                    GoodsName,
        //                 //StoreGapNum,
        //                 //GoodsSpec,
        //                 //BrandName,
        //                 //PurPrice,
        //                 EndTime);
        //    }


        //    return Json(new
        //    {
        //        success = 1,
        //        successRecord = successRecord,
        //        resultRecord = resultRecord,
        //        successRecord1 = successRecord1,
        //        resultRecord1 = resultRecord1,
        //        pushmsg = msg,
        //    });
        //}


        //查看商品存储上下限，缺口数量
        [AuthPermission]
        [HttpPost, Route("storlimit")]
        public ActionResult StorLimit(QueryFilter[] filters, Int32 pageIndex, Int32 pageSize,
            string sortField, Int32 sortDirection, string[] sumFields, [FromBody]dynamic data)
        {

            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[]>();
            }
            #region 获取库存低于下限记录
            DataTable dt = new DataTable();
            string result;

            string connectstr = "Data Source =192.168.50.30; Initial Catalog = LJHYBZK; Persist Security Info = True; User ID = sa; Password = xq!@#2014;Pooling=true;MAX Pool Size=512;Min Pool Size=50;Connection Lifetime=30";


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

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

            List<SqlParameter> parameters1 = new List<SqlParameter>();
            string filter = QueryFilter.getFilterSqlParam(filters, out parameters1,new PushStorLimit());

                string commandText0 = "select * from ";
            string commandText1 = "(select *, row_number() over" +
                "(  order by "+ sortField + direct + " )  as rownum" +
                "  from " + Config.TablePrefix + "PushStorLimit " +
                " where 1=1 "+ filter +
                ")AAA";
            string commandText = commandText0 + commandText1 + " where AAA.rownum>" + start + " and AAA.rownum<" + end +
                " ";
            //result = DataAccess.(connectstr,Config.TablePrefix + "PushStorLimit", "GoodsId", "*", " ", "", sortField + direct, pageIndex, pageSize != 0 ? pageSize : Config.PageSize);

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

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

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

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

        //供应商业务员修改过的记录：手动保存并重新推送 
        //[HttpPost, Route("saveandrepush")]
        //public ActionResult SaveAndRePush(
        //     string Id,
        //     string PushRecordId,
        //     string PushCode,
        //    string PushAccountId,
        //    string ContactId,
        //    string GoodsId, string GoodsCode
        //    , string GoodsName, Decimal StoreGapNum
        //    , string GoodsSpec, string Manufacturer
        //    , string BusinessId, string BusinessCode, string BusinessName
        //    , string SuppliersId, string BrandName, string BrandId
        //    , Decimal PurchaseTotalNum, Decimal PurPrice
        //    , string EntId, string Remark, string LoginAccountId,
        //    [FromBody]dynamic data,
        //     int ispush = 1)
        //{
        //    string FeedbackId = string.Empty;
        //    if (data != null)
        //    {
        //        //Newtonsoft.Json.Linq.JArray
        //        Id = data.Id;
        //        if (Id != null)
        //            FeedbackId = Id;

        //        PushRecordId = data.PushRecordId;

        //        PushAccountId = data.PushAccountId;
        //        if (PushAccountId == null)
        //            PushAccountId = data.UserId;

        //        GoodsId = data.GoodsId;
        //        GoodsCode = data.GoodsCode;
        //        GoodsName = data.GoodsName;
        //        StoreGapNum = data.StoreGapNum;

        //        GoodsSpec = data.GoodsSpec;
        //        Manufacturer = data.Manufacturer;
        //        BusinessId = data.BusinessId;
        //        BusinessCode = data.BusinessCode;
        //        BusinessName = data.BusinessName;

        //        SuppliersId = data.SuppliersId;
        //        BrandName = data.BrandName;
        //        BrandId = data.BrandId;

        //        PurchaseTotalNum = data.PurchaseTotalNum;
        //        PurPrice = data.PurPrice;
        //        BrandId = data.BrandId;
        //        if (PurPrice.Equals(0))
        //            PurPrice = data.LastPrice!=null?data.LastPrice:0;

        //        EntId = data.EntId;
        //        Remark = data.Remark;

        //        LoginAccountId = data.LoginAccountId;
        //        ContactId = data.ContactId;
        //        PushCode = data.PushCode;

        //        ispush = data.ispush == null ? 1 : data.ispush;//默认推送
        //    }
        //    if (PushAccountId == null || PushAccountId == "")
        //    {

        //        return Json(new
        //        {
        //            success = 0,
        //            msg = "不能推送，请先建立账户!"
        //        });
        //    }
        //    //else
        //    //{

        //    //    return Json(new
        //    //    {
        //    //        success = 0,
        //    //        msg = "不能推送，请先提醒对方业务员绑定!"
        //    //    });
        //    //}
        //    //手动推送
        //    Message msg = null;
        //    Object recordId;
        //    Object successRecord = 0;
        //    Object resultRecord = "";

        //    //根据userid 初始化PushAccountId
        //    //ContactDoc cdoc = AccountDAL.getContactor(ContactId);
        //    Dictionary<string, PushStorLimit> gapnum;
        //    string[] ContactIds = ContactId.Split("_");
        //    if (ContactIds.Length > 1)
        //    {
        //        ContactId = ContactIds[0];
        //        SuppliersId = ContactIds[1];
        //        PushAccountId = ContactIds[2];
        //    }

        //    if (PushAccountId == null || PushAccountId == "")
        //    {

        //        return Json(new
        //        {
        //            success = 0,
        //            msg = "不能推送，请先建立业务员账户!"
        //        });
        //    }

        //    if (ispush > 0 && (PushRecordId == null || PushRecordId == "")) { 
        //        DateTime EndTime = DateTime.Now.AddHours((double)24);

        //        //手动推送
        //        msg = TimeTask.sendSupplyMassMesage(_client,
        //             Convert.ToInt32(PushAccountId),
        //                       PushRecordId,
        //         GoodsName,
        //         //StoreGapNum,
        //         //GoodsSpec,
        //         //BrandName,
        //         //PurPrice,
        //         EndTime);

        //        //添加推送记录 
        //        PushCode = RandHelper.GetTimeRandId().ToString();
        //        IDictionary<string, Object> successs =
        //            PushRecordDAL.AddPushRecord(PushCode,
        //                Convert.ToInt32(PushAccountId), ContactId, Remark
        //        , LoginAccountId);

        //        successs.TryGetValue("successRecord", out successRecord);
        //        successs.TryGetValue("resultRecord", out resultRecord);
        //        successs.TryGetValue("PushRecordId", out recordId);
        //    }
        //    else
        //    {
        //        recordId = Guid.Parse(PushRecordId);
        //        successRecord = 1;
        //    }


        //    Object successRecord1 = 0;
        //    Object resultRecord1 = "";

        //    if ((int)successRecord > 0) { 
        //        PushRecordId = ((Guid)recordId).ToString();


        //        //添加或修改推送明细 
        //        IDictionary<string, Object> successs1 =
        //                PushRecordDAL.AddOrUpdatePushFeedBack(
        //                    PushCode,
        //                    PushRecordId,
        //                FeedbackId,
        //                Convert.ToInt32(PushAccountId),
        //                ContactId,
        //                GoodsId, GoodsCode
        //                , GoodsName, StoreGapNum
        //                , GoodsSpec, Manufacturer
        //                , BusinessId, BusinessCode, BusinessName
        //                , SuppliersId, BrandName, BrandId
        //                , EntId, Remark
        //                , LoginAccountId, PurPrice, PurchaseTotalNum);

        //        successs1.TryGetValue("successRecord", out successRecord1);
        //        successs1.TryGetValue("resultRecord", out resultRecord1);

        //    }

        //    return Json(new
        //    {
        //        success = 1,
        //        successRecord = successRecord,
        //        resultRecord = resultRecord,
        //        successRecord1 = successRecord1,
        //        resultRecord1 = resultRecord1,
        //        pushmsg = msg,
        //    });
        //}

        ////查看供应商业务员提交的供货情况--业务员已修改的
        //[HttpPost, Route("pushfeedbackconfirm")]
        //public ActionResult PushFeedbackConfirm(QueryFilter[] filters, Int32 pageIndex, Int32 pageSize,
        //    string sortField, Int32 sortDirection, string[] sumFields,
        //    [FromBody]dynamic data)
        //{
        //    return this.PushFeedback(filters, pageIndex, pageSize,
        //     sortField, sortDirection, sumFields,
        //     "2",
        //     data);
        //}

        ////查看供应商业务员提交的供货情况-业务员已提交的
        //[HttpPost, Route("pushfeedbackconfirmed")]
        //public ActionResult PushFeedbackConfirmed(QueryFilter[] filters, Int32 pageIndex, Int32 pageSize,
        //    string sortField, Int32 sortDirection, string[] sumFields,
        //    [FromBody]dynamic data)
        //{
        //    return this.PushFeedback(filters, pageIndex, pageSize,
        //     sortField, sortDirection, sumFields,
        //     "1,2",
        //     data);
        //}

        ///保存到采购计划
        //[HttpPost, Route("savetoplan")]
        //public ActionResult SaveToPlan(
        //    string Id,string PushRecordId,
        //    string PushAccountId,
        //    string EntId, int ReceiptMd, int CentPayMd
        //    , string OrgId, string DeptId
        //    , string FeedBack, string Remark
        //    , string GoodsId, string SuppliersId
        //    , string BusinessId, string BusinessCode
        //    , string LoginAccountId,
        //    string GoodsSpec, Int32 GoodsGapNum,
        //    Decimal PurPrice, string Remark1,
        //    Int32 PurchaseTotalNum, Int32 SafeDays
        //    , DateTime RequestDate,
        //    [FromBody]dynamic data)
        //{
        //    if (data != null)
        //    {
        //        //Newtonsoft.Json.Linq.JArray
        //        Id = data.Id;
        //        PushRecordId = data.PushRecordId;

        //        PushAccountId = data.PushAccountId;
        //        EntId = data.EntId;
        //        ReceiptMd = data.ReceiptMd;
        //        CentPayMd = data.CentPayMd;
        //        OrgId = data.OrgId;
        //        DeptId = data.DeptId;

        //        FeedBack = data.FeedBack;
        //        Remark = data.Remark;
        //        GoodsId = data.GoodsId;
        //        SuppliersId = data.SuppliersId;
        //        BusinessId = data.BusinessId;
        //        BusinessCode = data.BusinessCode;

        //        LoginAccountId = data.LoginAccountId;

        //        GoodsSpec = data.GoodsSpec;
        //        GoodsGapNum = data.StoreGapNum;
        //        PurPrice = data.PurPrice;
        //        Remark1 = data.Remark1;
        //        PurchaseTotalNum = data.PurchaseTotalNum;

        //        SafeDays = data.SafeDays;
        //    }

        //    PushRecord rec = PushRecordDAL.GetRecord(PushRecordId);
        //    if (rec!=null &&rec.IsComplete == 1)

        //        return Json(new
        //        {
        //            success = 0,
        //            msg = "已经生成采购计划!"
        //        });

        //    Decimal LastPrice = PurPrice;
        //    string Summaries = FeedBack;

        //    DateTime EndTime = DateTime.Now.AddHours((double)24);

        //    string LoginStaffId = StaffDocDAL.GetStaffId(LoginAccountId);
        //    //添加采购计划
        //    IDictionary<string, Object> successs =
        //        PushRecordDAL.AddPlan(rec.Id.ToString(),Id, Convert.ToInt32(PushAccountId), 
        //        EntId,  ReceiptMd,  CentPayMd
        //    , OrgId,  DeptId//depid
        //    ,  "采购计划", Remark //Summaries
        //    ,  GoodsId,  SuppliersId
        //    ,  BusinessId,  BusinessCode
        //    , LoginStaffId
        //    ,  RequestDate);

        //    int successAll = 0;
        //    Object successPlan;
        //    successs.TryGetValue("successRecord", out successPlan);
        //    if ((int)successPlan > 0) { 
        //        Object PlanBillNo0;
        //        successs.TryGetValue("PlanBillNo", out PlanBillNo0);
        //        int PlanBillNo = (int)PlanBillNo0;

        //        //添加采购计划明细
        //        IDictionary<string, Object> successs1 =
        //            PushRecordDAL.AddPlanDT(Id,PlanBillNo, 0,
        //                GoodsId, GoodsSpec, GoodsGapNum,
        //         LastPrice,  Remark,
        //         EntId,
        //         BusinessId,  BusinessCode,
        //         SuppliersId,
        //         PurchaseTotalNum,  SafeDays
        //         , LoginStaffId
        //        , RequestDate);

        //        Object successPlan1;
        //        successs1.TryGetValue("successRecord", out successPlan1);
        //        if ((int)successPlan1>0)
        //        {
        //            //更新推送状态 生成计划  
        //            string resultState;
        //            var successState = PurPlanDAL.UpdatePlanState(Id, PushRecordId, LoginAccountId,out resultState);
        //            if (resultState.StartsWith("在执行数据操作时发生错误"))
        //                successAll = 1;
        //        }
        //    }
        //    Object successRecord;
        //    Object resultRecord;
        //    Object successRecordRelate;
        //    Object resultRecordRelate;
        //    successs.TryGetValue("successRecord", out successRecord);
        //    successs.TryGetValue("resultRecord", out resultRecord);
        //    successs.TryGetValue("successRecordRelate", out successRecordRelate);
        //    successs.TryGetValue("resultRecordRelate", out resultRecordRelate);


        //    Object successRecord1;
        //    Object resultRecord1;
        //    Object successRecordRelate1;
        //    Object resultRecordRelate1;
        //    successs.TryGetValue("successRecord", out successRecord1);
        //    successs.TryGetValue("resultRecord", out resultRecord1);
        //    successs.TryGetValue("successRecordRelate", out successRecordRelate1);
        //    successs.TryGetValue("resultRecordRelate", out resultRecordRelate1);

        //    if(successAll > 0) { 
        //        return Json(new
        //        {
        //            success = 1,
        //            successRecord = successRecord,
        //            resultRecord = resultRecord,
        //            successRecord1 = successRecord1,
        //            resultRecord1 = resultRecord1,
        //        });
        //    }
        //    else
        //    {
        //        return Json(new
        //        {
        //            success = 0,
        //            successRecord = successRecord,
        //            resultRecord = resultRecord,
        //            successRecord1 = successRecord1,
        //            resultRecord1 = resultRecord1,
        //        });
        //    }
        //}

        //////待推送联系人--所有推送的业务员，查询业务员的推送记录
        //[HttpPost, Route("pendingcontacts")]
        //public JsonResult PendingContacts(QueryFilter[] filters, Int32 pageIndex, Int32 pageSize,
        //    string sortField, Int32 sortDirection, string[] sumFields, [FromBody]dynamic data)
        //{

        //    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[]>();
        //    }
        //    #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);

        //    List<SqlParameter> parameters1 = new List<SqlParameter>();
        //    string filterstr = QueryFilter.getFilterSqlParam(filters, out parameters1, new PushContactWithGoods());

        //    IList<PushContactWithGoods> users = new List<PushContactWithGoods>();
        //    Int64 totalcount = 0;
        //    //跨库 获取存储上下限
        //    IList<PushStorLimit> limits = PushRecordDAL.GetPushLimit();
        //    if (limits.Count > 0)
        //    {

        //        List<string> superior_list = new List<string>(0);
        //        Dictionary<string, PushStorLimit> gapnum = new Dictionary<string, PushStorLimit>();
        //        foreach (PushStorLimit limit in limits)
        //        {
        //            //Role role;
        //            //if (roleMap.TryGetValue(permsr.RoleId, out role))
        //            //{
        //            //    listRoles.Add(role);
        //            //}
        //            superior_list.Add(limit.GoodsId);
        //            gapnum.Add(limit.GoodsId, limit);
        //        }
        //        //perms.RoleList = listRoles;
        //        string limitss = string.Join("','", superior_list.ToArray());

        //        string commandText0 = "select * from (";
        //        string commandText1 = "select *, row_number() over" +
        //         "(order by ContactCode " + direct + " )  as rownum " +
        //         " FROM(SELECT    ROW_NUMBER() OVER(PARTITION  BY t1.GoodsId,t1.ContactId ORDER BY  t1.SysDates1 desc,t1.LastPrice asc) AS RNUM," +
        //                    "   * " +
        //                   "FROM      sup_PushContactWithGoods t1" +
        //                   " WHERE exists(" +
        //                   " select 1 from LJHYBZK.dbo.sup_PushStorLimit A " +
        //                   "  where A.GoodsId = t1.GoodsId)" +
        //                   //" WHERE t1.GoodsId IN ('" +
        //                   //limitss +
        //                   //" ')" +
        //                   filterstr+
        //                ") AS T " +
        //         " WHERE T.RNUM = 1" +
        //        ")AAA";
        //            string commandText2 = commandText0 + commandText1+" where AAA.rownum>" + start + " and AAA.rownum<" + end +
        //            " ";
        //        //string connectstr = "Data Source =192.168.50.30; Initial Catalog = LJHYBZK; Persist Security Info = True; User ID = sa; Password = xq!@#2014;Pooling=true;MAX Pool Size=512;Min Pool Size=50;Connection Lifetime=30";

        //        //bool result1 = DataAccess.GetValues(connectstr, commandText, ref dt, parameters1.ToArray(), out result);
        //        bool result1 = DataAccess.GetValues(commandText2, ref dt, parameters1.ToArray(), out result);

        //        #endregion
        //        // 把DataTable转换为IList<UserInfo>  
        //        if (dt != null && dt.Rows.Count > 0)
        //        {
        //            // 把DataTable转换为IList<UserInfo>  
        //            users = ModelConvertHelper<PushContactWithGoods>.ConvertToModel(dt);
        //            foreach (PushContactWithGoods user in users)
        //            {
        //                //Role role;
        //                //if (roleMap.TryGetValue(permsr.RoleId, out role))
        //                //{
        //                //    listRoles.Add(role);
        //                //}
        //                PushStorLimit StorLimit;
        //                gapnum.TryGetValue(user.GoodsId, out StorLimit);
        //                user.StoreGapNum = StorLimit.StoreGapNum;
        //                user.EntId = StorLimit.EntId;
        //                user.PurchaseTotalNum = StorLimit.PurchaseTotalNum;//StoreGapNum
        //            }
        //        }


        //        string resultrow;

        //        string commandTextRowCount = "select count(*) from ("+ commandText1;
        //        //totalcount = DataAccess.GetRowCountDefine(connectstr, commandTextRowCount, out resultrow);
        //        totalcount = DataAccess.GetRowCountDefine( commandTextRowCount, 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
        //    });
        //}

        ///取预付款订单总金额
        [HttpGet, Route("PrepayAmt")]
        public JsonResult PrepayAmt(string PushRecordId,string BusinessId)
        {
            string sql = "select sum(ActPrice*StoreGapNum) as PrepayAmt from " + Config.TablePrefix+ "PushFeedback" +
                " where PushRecordId = @PushRecordId " +
                " and BusinessId = @BusinessId " +
                " group by PushRecordId,BusinessId";
            string result = string.Empty;
            List<SqlParameter> param = new List<SqlParameter>(2);
            param.Add(new SqlParameter("PushRecordId", PushRecordId));
            param.Add(new SqlParameter("BusinessId", BusinessId));
            DataAccess.GetOneValue(sql, param,out var PrepayAmt, out result);
            return Json(new
            {
                success = 1,
                sum = PrepayAmt,
                msg = result
            });
        }
    }
}