test/Member/SupplierWeb/Controllers/IncoiceSpController.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 Common.Model;
using ZcPeng.PublicLibrary;
using CoreEntity.Entity;
using CoreEntity.DAL;
using System.Collections.Concurrent;
using Newtonsoft.Json.Converters;
using Newtonsoft.Json;
using System.Data.SqlClient;
using Microsoft.Extensions.Primitives;
using Microsoft.Extensions.Caching.Memory;
using Jwt;
using PublicLibrary.Model;
using Newtonsoft.Json.Linq;
using SupplierWeb.Codes.mvc;
using SupplierWeb.Codes.Auth;
using Microsoft.AspNetCore.Hosting;
using Npoi.Mapper;
using System.IO;
using LigerRM.Common;

namespace SupplierWeb.Controllers
{
    /// <summary>
    /// 供应商查询采购明细
    /// </summary>
    [AuthPermission]
    [Route("web/invoicesp")]
    public class IncoiceSpController : BaseController
    {
        public IncoiceSpController(IMemoryCache cache, IApiClient client) : base(cache, client)
        {

        }
        private ConcurrentDictionary<int, Role> roleMap = new ConcurrentDictionary<int, Role>();

        /// 查询发票订单
        [AuthPermission]
        [HttpPost, Route("bill")]
        public JsonResult bill(QueryFilter[] filters, Int32 pageIndex, Int32 pageSize,
            string sortField, Int32 sortDirection, string[] sumFields, [FromBody]dynamic data)
        {
            string LoginAccountId = getStaff("userid");
            string RoleId = getStaff("roleid");
            //判断参数是否合法
            if (string.IsNullOrEmpty(LoginAccountId))
            {
                return Json(new
                {
                    items = new string[] { },
                    sum = new { },
                    totalCount = 0
                });
            }

            //string StaffDocId = StaffDocDAL.GetStaffId(LoginAccountId);
            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;
            IList<InvoiceBill> permss = new List<InvoiceBill>(0);

            List<SqlParameter> parameters = new List<SqlParameter>();
            string filterstr = QueryFilter.getFilterSqlParam(filters, out parameters, new InvoiceMT(), "A.");
            filterstr = filterstr.Replace("A.K_BillCode", "C.K_BillCode");
            filterstr = filterstr.Replace("A.Contact", "D.Contact");
            filterstr = filterstr.Replace("A.StaffName", "E.StaffName");
            sortField = sortField.Replace("Pk","BillNo");
            if(!parameters.Exists(v1 => v1.ParameterName == "@BillNo"))
                parameters.Add(new SqlParameter("@BillNo", ""));

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

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

            string filter = FilterTranslator.ruleSql(ref parameters);
            string commandText0 = "select * from ";
            string commandText1 = "(" +
                "select concat(A.BillNo,A.BillSn) as Pk,A.*,C.K_BillCode,C.OppContId,C1.K_ContactId,D.Contact,E.StaffName,row_number() over" +
                "(  order by A." + sortField + " " + direct + " )  as rownum" +
                " from InvoiceBill  A " +
                " left join PURINMT C on C.BillNo = A.rfBillNo " +
                " left join PurOrderMt C1 on C1.BillCode = C.K_BillCode " +
                " left join ContactDoc D on D.ContactId = C1.K_ContactId " +
                //" left join ContactDoc D1 on D1.ContactId = C.OppContId " +
                " left join StaffDoc E on A.SaleManId = E.StaffId " +
                " WHERE 1=1 " +
                    filterstr +
                filter +
            ")AAA ";
            string commandText2 = " where AAA.rownum>" + start + " and AAA.rownum<" + end;

            bool success = DataAccess.GetValues(commandText0 + commandText1 + commandText2, ref dt, parameters.ToArray(), out result);
                
            #endregion
            if (dt != null && dt.Rows.Count > 0)
            {
                // 把DataTable转换为IList
                permss = ModelConvertHelper<InvoiceBill>.ConvertToModel(dt);
            }
            string result1;
            long totalcount = DataAccess.GetRowCountDefine("select count(1) from " + commandText1, parameters.ToArray(), out result1);
            IsoDateTimeConverter timejson = new IsoDateTimeConverter
            {
                DateTimeFormat = "yyyy'-'MM'-'dd' 'HH':'mm':'ss"
            };
            var jsonData = JsonConvert.SerializeObject(permss, timejson);

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


        /// 
        /// 查询发票明细 
        [AuthPermission]
        [HttpPost, Route("index")]
        public JsonResult index(QueryFilter[] filters, Int32 pageIndex, Int32 pageSize,
            string sortField, Int32 sortDirection, string[] sumFields, [FromBody]dynamic data)
        {
            string LoginAccountId = getStaff("userid");
            string RoleId = getStaff("roleid");
            //判断参数是否合法
            if (string.IsNullOrEmpty(LoginAccountId))
            {
                return Json(new
                {
                    items = new string[] { },
                    sum = new { },
                    totalCount = 0
                });
            }

            //string StaffDocId = StaffDocDAL.GetStaffId(LoginAccountId);
            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;
            IList<InvoiceDT> permss = new List<InvoiceDT>(0);

            List<SqlParameter> parameters = new List<SqlParameter>();
            string filterstr = QueryFilter.getFilterSqlParam(filters, out parameters, new InvoiceMT(),"A.");
            sortField = sortField.Replace("Pk", "BillNo");
            //parameters.Add(new SqlParameter("@userids", LoginAccountId));

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

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

            string rulefilter = FilterTranslator.ruleSql(ref parameters);
            string commandText0 = "select * from ";
            string commandText1 = "(" +
                "select  concat(A.BillNo,'_',A.BillSn) as Pk,A.*,row_number() over" +
                "(  order by A." + sortField + " " + direct + " )  as rownum" +
                " from InvoiceDT  A " +
                " where  1=1 " +
                " and A.BillNo in (select  B.BillNo FROM INVOICEBILL B   " +
                " left join PURINMT C on C.BillNo = B.rfBillNo " +
                " left join PurOrderMt C1 on C1.BillCode = C.K_BillCode " + rulefilter +
                " WHERE 1=1 " + rulefilter +
                    filterstr +
                 ")" +
            ")AAA ";
            string commandText2 = " where AAA.rownum>" + start + " and AAA.rownum<" + end;

            bool success = DataAccess.GetValues(commandText0 + commandText1 + commandText2, ref dt, parameters.ToArray(), out result);

            #endregion
            if (dt != null && dt.Rows.Count > 0)
            {
                // 把DataTable转换为IList<K_Contactsp>  
                permss = ModelConvertHelper<InvoiceDT>.ConvertToModel(dt);
            }
            string result1;
            long totalcount = DataAccess.GetRowCountDefine("select count(1) from " + commandText1, parameters.ToArray(), out result1);
            IsoDateTimeConverter timejson = new IsoDateTimeConverter
            {
                DateTimeFormat = "yyyy'-'MM'-'dd' 'HH':'mm':'ss"
            };
            //IList<Menu> menus = K_Contactsp.Convert(permss);
            var jsonData = JsonConvert.SerializeObject(permss, timejson);

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

        
    }

}