using Rhea.Common;
using Tiger.Model;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Threading.Tasks;
using System.Linq;
using Newtonsoft.Json;
using Rhea.Common.Authorization;
using Newtonsoft.Json.Linq;
using Tiger.Model.Minsun;
using Tiger.IBusiness;
using System.Data.Entity;

namespace Tiger.Business.WMS
{
    public partial class KanBan : IKanBan
    {
        /// <summary>
        /// warehousing看板全部数据对接
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<MaterialKBOutput> GetReceiptDetail(MaterialKBInput input)
        {
            try
            {
                DateTime dateTime = DateTime.Now.AddDays(-2);
                int nYear = DateTime.Now.Year;
                //var sql = $"SELECT SUM(INSTOCKQTY) as Total, convert(varchar(100),datepart(mm, LastModificationTime))+'月' as Month FROM WMS_RECEIPT_D WHERE LINESTATUS=8 and datepart(yy,LastModificationTime)=@nYear GROUP BY datepart(mm, LastModificationTime)";

                //var sList = Db.Queryable<BIZ_ERP_RECEIPT_DTL, V_USER_ORG>((d, r) =>
                //                        new JoinQueryInfos(
                //                            JoinType.Left, d.CREATE_USER == r.USER_ID
                //                        ))
                //             .Where((d, r) => d.LINESTATUS == 2 && d.UPDATE_TIME.Year == nYear && r.USERGROUP_CODE == "清溪结构仓")
                //             //.GroupBy((d, r) => d.UPDATE_TIME)
                //            .Select((d, r) => new TotalByMonth
                //             {
                //                 Total = SqlFunc.AggregateSum(d.INSTOCKQTY),
                //                 Month = $"{d.UPDATE_TIME.Month}月"
                //             })
                //.ToList();

                var sList = Biz.Db.Queryable<V_MONTH_IN_SUM>().ToList();

                //var sList = Db.Queryable<BIZ_ERP_RECEIPT_DTL>()
                //    .LeftJoin<V_USER_ORG>((d, r) => d.CREATE_USER==r.USER_ID)
                //    .GroupBy((d, r) => d.UPDATE_TIME.Month)
                //    .Where((d, r) => d.LINESTATUS == 1 && d.UPDATE_TIME.Year == nYear && r.USERGROUP_CODE == "清溪结构仓")
                //           //.GroupBy((d, r) => new { Month = d.UPDATE_TIME.Month })
                //            .Select((d, r) => new TotalByMonth
                //            {
                //                Total = SqlFunc.AggregateSum(d.INSTOCKQTY),
                //                Month = $"{d.UPDATE_TIME.Month}月"
                //            })
                //           .ToList();

                //SqlParameter[] nParams = new SqlParameter[]
                //{
                //    new SqlParameter("@nYear",System.Data.SqlDbType.Int),
                //};
                //nParams[0].Value = nYear;

                //var sList = SqlQuery<TotalByMonth>(sql, nParams).ToList();

                //var taskTotalSql = $"SELECT SUM(QTY-INSTOCKQTY) as Total,'' as Month FROM WMS_RECEIPT_D WHERE LINESTATUS = 4 and datepart(yy,LastModificationTime)=@nYear ";

                var sTaskTotal = Biz.Db.Queryable<BIZ_ERP_RECEIPT_DTL, V_USER_ORG>((d, r) =>
                                               new JoinQueryInfos(
                                                   JoinType.Left, d.CREATE_USER == r.USER_ID
                                        ))
                    .Where((d, r) => (d.LINESTATUS == 1) && d.UPDATE_TIME.Year == nYear && r.USERGROUP_CODE == "清溪结构仓")
                    .Select((d, r) => new TotalByMonth
                    {
                        Total = SqlFunc.RowCount(),
                        Month = ""
                    }).First();

                //SqlParameter[] nParams2 = new SqlParameter[]
                //{
                //new SqlParameter("@nYear",System.Data.SqlDbType.Int),
                //};
                //nParams2[0].Value = nYear;

                //var sTaskTotal = this._receiptDetailRepository.SqlQuery<TotalByMonth>(taskTotalSql, nParams2).FirstOrDefault();

                //var unChecklSql = $"SELECT SUM(QTY) as Total,'' as Month FROM WMS_RECEIPT_D WHERE LINESTATUS = 2 and datepart(yy,LastModificationTime)=@nYear ";

                var sUnCheckNumber = Biz.Db.Queryable<BIZ_ERP_RECEIPT_DTL, V_USER_ORG>((d, r) =>
                                               new JoinQueryInfos(
                                                   JoinType.Left, d.CREATE_USER == r.USER_ID
                                        ))
                   .Where((d, r) => d.LINESTATUS == 1 && d.UPDATE_TIME.Year == nYear && r.USERGROUP_CODE == "清溪结构仓")
                   .Select((d, r) => new TotalByMonth
                   {
                       Total = SqlFunc.AggregateSum(d.QTY),
                       Month = ""
                   }).First();

                //SqlParameter[] nParams3 = new SqlParameter[]
                //{
                //new SqlParameter("@nYear",System.Data.SqlDbType.Int),
                //};
                //nParams3[0].Value = nYear;

                //var sUnCheckNumber = this._receiptDetailRepository.SqlQuery<TotalByMonth>(unChecklSql, nParams3).FirstOrDefault();

                //var sTotalSql = $"SELECT SUM(INSTOCKQTY) as Total,'' as Month FROM WMS_RECEIPT_D WHERE LINESTATUS in (4, 8) and datepart(yy,LastModificationTime)=@nYear ";
                //结构仓数量
                var Structure = Biz.Db.Queryable<BIZ_ERP_RECEIPT_DTL, V_USER_ORG>((d, r) =>
                                               new JoinQueryInfos(
                                                   JoinType.Left, d.CREATE_USER == r.USER_ID
                                        ))
                   .Where((d, r) => (d.LINESTATUS == 2) && d.UPDATE_TIME.Year == nYear && r.USERGROUP_CODE == "清溪结构仓")
                   .Select((d, r) => new TotalByMonth
                   {
                       Total = SqlFunc.AggregateSum(d.INSTOCKQTY),
                       Month = ""
                   }).First();

                //电子仓数量
                var Electron = Biz.Db.Queryable<BIZ_ERP_RECEIPT_DTL, V_USER_ORG>((d, r) =>
                                               new JoinQueryInfos(
                                                   JoinType.Left, d.CREATE_USER == r.USER_ID
                                        ))
                   .Where((d, r) => (d.LINESTATUS == 2) && d.UPDATE_TIME.Year == nYear && r.USERGROUP_CODE == "清溪电子仓")
                   .Select((d, r) => new TotalByMonth
                   {
                       Total = SqlFunc.AggregateSum(d.INSTOCKQTY),
                       Month = ""
                   }).First();
                //SqlParameter[] nParams4 = new SqlParameter[]
                //{
                //new SqlParameter("@nYear",System.Data.SqlDbType.Int),
                //};
                //nParams4[0].Value = nYear;

                //var sTotal = this._receiptDetailRepository.SqlQuery<TotalByMonth>(sTotalSql, nParams4).FirstOrDefault();

                var nOutput = new MaterialKBOutput()
                {
                    Structure = Structure.Total,
                    Electron = Electron.Total,
                    UnCheckNumber = sUnCheckNumber.Total,
                    TaskTotal = sTaskTotal.Total,
                    TotalByMonthList = sList,
                    // IncomingRptList = nResult
                };
                return nOutput;
            }
            catch (Exception ex)
            {

                throw;
            }
        }
        /// <summary>
        /// warehousing入库明细信息
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<MaterialKBOutput> GetIncomingDetail(MaterialKBInput input)
        {

            int pagenumber = Convert.ToInt16(input.Barcode); // pagenumber是从1开始的不是从零开始的
            int pageSize = 10;
            int totalCount = 0;

            DateTime dateTime = DateTime.Now.AddDays(-2);
            //入库信息
            var nResult = Biz.Db.Queryable<BIZ_ERP_RECEIPT_DTL, BAS_ITEM, V_USER_ORG>((r, m, s) =>
                                new JoinQueryInfos(
                                    JoinType.Left, r.ITEM_CODE == m.ITEM_CODE,
                                    JoinType.Left, r.CREATE_USER == s.USER_ID
                                    ))
            .Where((r, m, s) => r.CREATE_TIME > dateTime && s.USERGROUP_CODE == "清溪结构仓")
            .OrderByDescending((r, m, s) => r.CREATE_TIME)
            .Select((r, m, s) => new IncomingRptOutput
            {
                CreationTime = r.CREATE_TIME,
                BillCode = r.SOURCECODE,
                WarehouseCode = r.WAREHOUSECODE,
                MaterialCode = r.ITEM_CODE,
                MaterialName = m.ITEM_NAME,
                Status = r.LINESTATUS,
                Qty = r.QTY,
                InstockQty = r.INSTOCKQTY,
                LeftQty = r.QTY - r.INSTOCKQTY
            })
            .ToPageList(pagenumber, pageSize, ref totalCount);
            //nResult = nResult.OrderByDescending(q=>q.CreationTime).ToList();
            //totalCount = 21;
            if (totalCount > pageSize * pagenumber)
            {
                pagenumber += 1;
            }
            else
            {
                pagenumber = 1;
            }
            var nOutput = new MaterialKBOutput()
            {
                Structure = pagenumber,
                IncomingRptList = nResult
            };
            return nOutput;
        }
    }
}