using System;
using System.Collections.Generic;
using System.Text;
using Bailun.DC.Models;
using Bailun.DC.DB;
using Dapper;
using MySql.Data.MySqlClient;

namespace Bailun.DC.Services
{
    public class AllotServices
    {
        /// <summary>
        /// 调拨单列表
        /// </summary>
        /// <param name="parameter">分页信息</param>
        /// <param name="warehouse">仓库编码</param>
        /// <param name="status">调拨单状态</param>
        /// <param name="orderno">调拨单号</param>
        /// <param name="sku">sku编码</param>
        /// <param name="total">符合条件的记录数</param>
        /// <returns></returns>
        public List<Models.Allot.mAllotOrder> ListAllot(BtTableParameter parameter, string warehouse, int? status, string orderno, string sku, ref int total)
        {
            var sqlparam = new DynamicParameters();
            var sql = @"select t1.transfer_order_id,t1.`status`,t1.bailun_sku,t2.sku_image,t2.category_name,t2.sku_title_en,t2.sku_title_cn,t1.warehouse_code,t1.warehouse_name,t1.count,t1.create_time,t1.gmt_modified from dc_base_transfer_bale t1
                                left join dc_base_sku t2 on t1.bailun_sku = t2.bailun_sku where 1=1 ";

            if (!string.IsNullOrWhiteSpace(warehouse))
            {
                sql += " and t1.warehouse_code=@warehouse_code";
                sqlparam.Add("warehouse_code", warehouse);
            }

            if (status.HasValue)
            {
                sql += " and t1.status="+status.Value;
            }

            if (!string.IsNullOrWhiteSpace(orderno))
            {
                sql += " and t1.transfer_order_id=@transfer_order_id";
                sqlparam.Add("transfer_order_id", orderno);
            }

            if (!string.IsNullOrWhiteSpace(sku))
            {
                sql += " and t1.bailun_sku=@bailun_sku";
                sqlparam.Add("bailun_sku", sku);
            }

            if (!string.IsNullOrWhiteSpace(parameter.sort))
            {
                sql += " order by t1." + parameter.sort;
                if (!string.IsNullOrWhiteSpace(parameter.order))
                {
                    sql += " " + parameter.order;
                }
            }
            else
            {
                sql += " order by t1.create_time desc";
            }

            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                var obj = cn.Page<Models.Allot.mAllotOrder>(parameter.pageIndex, parameter.limit, sql, ref total, sqlparam);

                return obj.AsList();
            }
        }

        /// <summary>
        /// 调拨单 汇总
        /// </summary>
        /// <param name="warehouse"></param>
        /// <param name="status"></param>
        /// <param name="orderno"></param>
        /// <param name="sku"></param>
        /// <returns></returns>
        public Models.Allot.mAllotOrder ListAllotCount(string warehouse, int? status, string orderno, string sku)
        {
            var sqlparam = new DynamicParameters();
            var sql = @"select sum(t1.count) count from dc_base_transfer_bale t1
                                left join dc_base_sku t2 on t1.bailun_sku = t2.bailun_sku where 1=1 ";

            if (!string.IsNullOrWhiteSpace(warehouse))
            {
                sql += " and t1.warehouse_code=@warehouse_code";
                sqlparam.Add("warehouse_code", warehouse);
            }

            if (status.HasValue)
            {
                sql += " and t1.status=" + status.Value;
            }

            if (!string.IsNullOrWhiteSpace(orderno))
            {
                sql += " and t1.transfer_order_id=@transfer_order_id";
                sqlparam.Add("transfer_order_id", orderno);
            }

            if (!string.IsNullOrWhiteSpace(sku))
            {
                sql += " and t1.bailun_sku=@bailun_sku";
                sqlparam.Add("bailun_sku", sku);
            }
            
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                var obj = cn.QueryFirst<Models.Allot.mAllotOrder>(sql, sqlparam);

                return obj;
            }
        }

        /// <summary>
        /// 获取调拨单详情接口
        /// </summary>
        /// <param name="page">页码</param>
        /// <param name="pagesize">每页记录数</param>
        /// <param name="start">开始时间</param>
        /// <param name="end">结束时间</param>
        /// <param name="total">符合条件的记录数</param>
        /// <returns></returns>
        public List<Models.Allot.dc_base_transfer_info> ListAllot(int page, int pagesize, DateTime start, DateTime end, ref int total)
        {
            var sql = $"select * from dc_base_transfer_info where dc_createtime>='{start.ToString("yyyy-MM-dd HH:mm:ss")}' and dc_createtime<='{end.ToString("yyyy-MM-dd HH:mm:ss")}'";

            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                var obj = cn.Page<Models.Allot.dc_base_transfer_info>(page,pagesize,sql,ref total);

                return obj.AsList();

            }

        }
    }
}
