using System;
using System.Collections.Generic;
using System.Text;
using Dapper;
using AutoTurnOver.Models;

namespace AutoTurnOver.DB
{
    /// <summary>
    ///  jit 备货规则
    /// </summary>
   public class dc_auto_jit_tag_dao : connectionHelper
    {
        /// <summary>
        /// 获取 jit 备货规则分页列表
        /// </summary>
        /// <param name="m"></param>
        /// <param name="offset"></param>
        /// <param name="limit"></param>
        /// <param name="total"></param>
        /// <returns></returns>
        public static List<dc_auto_jit_tag_dto> JitTagList(int group_id,int offset, int limit, ref int total)
        {
            var list = new List<dc_auto_jit_tag_dto>();

            try
            {
                var sql = @"select t1.*,t2.`name` as 'group_name' from dc_auto_jit_tag as t1 
left join dc_auto_jit_tag_group as t2 on t1.group_id = t2.id
where 1 = 1 ";

                if (group_id > 0)
                {
                    sql += " and t1.group_id=" + group_id;
                }
                sql += " order by t1.level asc  ";
               
                total = _connection.ExecuteScalar<int>("select count(0) from (" + sql + ") tb1");

                var obj = _connection.Query<dc_auto_jit_tag_dto>(sql + " limit " + offset + "," + limit);

                return obj.AsList();

            }
            catch (Exception)
            {
                return list;
            }
        }

        public static List<dc_auto_jit_tag_log_dto> JitTagLogList(dc_auto_jit_tag_log_search_dto data,int offset, int limit, ref int total)
        {
            var list = new List<dc_auto_jit_tag_log_dto>();

            try
            {
                var sql = @"select t1.*,t2.tag_name,t2.quantity_safe_inventory from dc_auto_jit_tag_log as t1 left join dc_auto_jit_tag as t2 on t1.tag_id = t2.id where 1 = 1  ";
                DynamicParameters parameters = new DynamicParameters();
                if (data!=null)
                {
                    if (data.tag_id > 0)
                    {
                        sql += " and t1.tag_id = @tag_id ";
                        parameters.Add("tag_id", data.tag_id);
                    }
                    if (!string.IsNullOrWhiteSpace(data.bailun_sku))
                    {
                        sql += " and t1.bailun_sku = @bailun_sku ";
                        parameters.Add("bailun_sku", data.bailun_sku);
                    }
                    if (data.b_date!=null){
                        sql += " and t1.date >= @b_date ";
                        parameters.Add("b_date", data.b_date.Value.ToString("yyyy-MM-dd 00:00:00"));
                    }
                    if (data.e_date != null)
                    {
                        sql += " and t1.date <= @e_date ";
                        parameters.Add("e_date", data.b_date.Value.ToString("yyyy-MM-dd 23:59:59"));
                    }

                }
                sql += " order by t1.date desc ";
                total = _connection.ExecuteScalar<int>("select count(0) from (" + sql + ") tb1", parameters);

                var obj = _connection.Query<dc_auto_jit_tag_log_dto>(sql + " limit " + offset + "," + limit, parameters);

                return obj.AsList();

            }
            catch (Exception)
            {
                return list;
            }
        }

        public static List<dc_auto_jit_tag> All()
        {
            return _connection.Query<dc_auto_jit_tag>(" select * from dc_auto_jit_tag ").AsList();
        }

        /// <summary>
        /// 保存安全库存规则
        /// </summary>
        /// <param name="m">销售设置model</param>
        /// <param name="username">当前操作人名称</param>
        /// <returns></returns>
        public static string SaveJitTag(dc_auto_jit_tag m, string username)
        {
            if (m.group_id==null || m.group_id <= 0 )
            {
                throw new Exception("所属规则组必选");
            }
           m.gmt_update = DateTime.Now;
            if (m.is_eliminate == 1)
            {
                m.quantity_safe_inventory = 0;
            }
            try
            {
                if (m.id > 0)
                {
                    var result = _connection.Update<dc_auto_jit_tag>(m);

                    return result > 0 ? "" : "保存异常，请重试！";
                }
                else
                {
                    var result = _connection.Insert<dc_auto_jit_tag>(m);

                    return result.HasValue && result.Value > 0 ? "" : "提交异常，请重试！";
                }
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }

        /// <summary>
        /// 获取安全库存规则设置详情
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static dc_auto_jit_tag GetJitTagById(int id)
        {
            return _connection.QueryFirstOrDefault<dc_auto_jit_tag>("select * from dc_auto_jit_tag where id=" + id);
        }
    }
}
