test/Member/SupplierWeb/Controllers/PayController.cs

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

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


        [AuthPermission]
        [HttpPost]
        [Route("getapplygoods")]
        public JsonResult ApplyGoods(string[] filters, Int32 pageIndex, Int32 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 direct = isAce ? " asc" : " desc";

            var queryCondition = string.Empty;
            var param = new List<SqlParameter>(0);
            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 APGoodsMT(), "A.");
            }
            queryCondition = queryCondition.Replace("A.StaffName", "C.StaffName");
            queryCondition = queryCondition.Replace("A.Contact", "D.Contact");
            queryCondition = queryCondition.Replace("A.K_djpjzt", "C1.K_djpjzt");
            queryCondition = queryCondition.Replace("A.IsPrepay", "B.IsPrepay");
            queryCondition = queryCondition.Replace("A.PrepayAmt", "B.PrepayAmt");
            queryCondition = queryCondition.Replace("A.BusinessName", "E.BusinessName");
            queryCondition = queryCondition.Replace("A.K_IsApplyP", "IsNull(K_IsApplyP,'N')");
            if (sortField.IndexOf(",") > 0)
                ;
            else
            sortField = "A."+sortField;
            sortField = sortField.Replace("A.StaffName", "C.StaffName");
            sortField = sortField.Replace("A.Contact", "D.Contact");
            sortField = sortField.Replace("A.K_djpjzt", "C1.K_djpjzt");
            sortField = sortField.Replace("A.IsPrepay", "B.IsPrepay");
            sortField = sortField.Replace("A.PrepayAmt", "B.PrepayAmt");
            sortField = sortField.Replace("A.BusinessName", "E.BusinessName");

            var filter = FilterTranslator.ruleSql(ref param);
            var sql0 =
                $" select * from ";
            var sql1 = $" (select A.*," +
                $"B2.K_ContactId,B.OppContId,B.PayType,B.K_BillCode,B2.Source,B.IsPrepay,B.PrepayAmt," +
                $"B31.BillNo PBillNo,B4.BillCode as PBillCode,E.BusinessName," +
                $" C1.K_djpjzt as K_djpjzt,C.StaffName,D.Contact,row_number() over (order by " + sortField + " " + direct + ") as rowNum " +
                $" from APGoodsMT A " +
                $" left join PurInMT B on A.BillNo = B.BillNo and A.entid = B.entid " +
                $" left join PurOrderMT B2 on B.K_BillCode = B2.BillCode and B.entid = B2.entid " +
                $" left join (select distinct RfBillNo,K_djpjzt,a1.EntId from K_PurFP a1 " +
                $" left join InvoiceBill b1 on a1.billno = b1.billno and a1.entid = b1.entid " +
                $" where a1.K_djpjzt='已签收')C1 on B.BillNo = C1.RfBillNo and B.entid = C1.entid " +
                $" left join(select B3.entid, B3.RfBillNo, min(b3.BillNo) as BillNo  from PlanBillDT B3 " +
                    $" group by B3.entid,B3.RfBillNo " +
                $") B31 on B31.RfBillNo = A.BillNo and B31.entid = A.entid " +
                    $" left join PlanMT B4 on B4.BillNo = B31.BillNo and B4.entid = B31.entid " +
                //$" left join PlanBillDT B3 on B3.RfBillNo = A.BillNo and B3.entid = A.entid " +
                //$" left join PlanMT B4 on B4.BillNo = B3.BillNo and B4.entid = B3.entid " +
                $" left join StaffDoc C on A.SaleManId = C.StaffId  and A.entid = C.entid " +
                $" left join ContactDoc D on D.ContactId = B2.K_ContactId and D.entid = B2.entid " +
                //$" left join ContactDoc D1 on B.OppContId = D1.ContactId  and A.entid = D.entid " +
                $" left join BusinessDoc E on A.RfId = E.BusinessId  and A.entid = E.entid " +
                $" where 1 = 1 {queryCondition}" +
                $"" + filter +
                $" ) R";
            var sql2 = $" where rowNum > {start} and rowNum < {end}";


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


            IList<APGoodsMT> result = new List<APGoodsMT>();
            if (dt != null && dt.Rows.Count > 0)
            {
                result = ModelConvertHelper<APGoodsMT>.ConvertToModel(dt);
            }
            
            //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);


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

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

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

        [AuthPermission]
        [HttpPost]
        [Route("getapplygoodsdt")]
        public JsonResult ApplyGoodsDT(string[] filters, Int32 pageIndex, Int32 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 == 1;
            var direct = isAce ? " asc" : " desc";

            var queryCondition = string.Empty;
            var param = new List<SqlParameter>(0);
            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 APGoodsDT(), "A.");
            }
            queryCondition = queryCondition.Replace("A.PK", "Concat(A.BillNo,'_',A.BillSn)");
            queryCondition = queryCondition.Replace("A.GoodsName", "C.GoodsName");
            queryCondition = queryCondition.Replace("A.GoodsCode", "C.GoodsCode");

            sortField = "A." + sortField;
            sortField = sortField.Replace("A.PK", "Concat(A.BillNo,'_',A.BillSn)");
            sortField = sortField.Replace("A.GoodsName", "C.GoodsName");
            sortField = sortField.Replace("A.GoodsCode", "C.GoodsCode");

            var filter = FilterTranslator.ruleSql(ref param);
            var sql0 =
                $" select * from ";
            var sql1 = $" (select Concat(A.BillNo,'_',A.BillSn) as PK,A.*,B.Unit,C.GoodsName,C.GoodsCode," +
                $" row_number() over (order by " + sortField + " " + direct + ") as rowNum " +
                $" from APGoodsDT A " +
                $" left join PurInDT B on A.BillNo = B.BillNo and A.BillSn = B.BillSn  and A.entid = B.entid " +
                $" left join PurInMT B0 on B.BillNo = B0.BillNo  and B.entid = B0.entid " +
                $" left join PurOrderMT B2 on B0.K_BillCode = B2.BillCode and B0.entid = B2.entid " + filter +
                $" left join GoodsDoc C on C.GoodsId = B.GoodsId  and C.entid = B.entid " +
                $" where 1 = 1 {queryCondition}" +
                $"" + filter +
                $" ) R";
            var sql2 = $" where rowNum > {start} and rowNum < {end}";


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


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

            //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);

            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,
                msg
            });
        }


    }
}