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


namespace AutoTurnOver.DB
{
    public class dc_auto_turnover: connectionHelper
    {
        public dc_auto_turnover()
        {
            Dapper.SimpleCRUD.SetDialect(SimpleCRUD.Dialect.MySQL);
        }

        /// <summary>
        /// 获取自动周转分页列表
        /// </summary>
        /// <param name="page"></param>
        /// <param name="pagesize"></param>
        /// <param name="total"></param>
        /// <param name="order">排序类型</param>
        /// <param name="sort">排序字段</param>
        /// <returns></returns>
        public static List<Models.dc_auto_turnover> List(Condition_AutoTurnOver m,int offset, int limit, ref int total,string order="",string sort="")
        {
            try
            {
                var sql = "select dat.* from dc_auto_turnover dat where 1=1 ";

                if (!string.IsNullOrEmpty(m.bailun_sku))
                {
                    sql += " and dat.bailun_sku like '%" + m.bailun_sku+"%'";
                }

                if (!string.IsNullOrEmpty(m.warehouse_code))
                {
                    sql += " and dat.warehouse_code='" + m.warehouse_code+"'";
                }

                if (m.out_of_stock.HasValue)
                {
                    sql += " and dat.status=1";
                }

                
                total = _connection.ExecuteScalar<int>("select count(0) from ("+sql+") tb1");

                if (!string.IsNullOrEmpty(sort))
                {
                    sql += " order by dat." + sort;

                    if (!string.IsNullOrEmpty(order))
                    {
                        sql += " " + order;
                    }
                    else
                    {
                        sql += " asc";
                    }
                }
                

                var obj = _connection.Query<Models.dc_auto_turnover>(sql+ " limit " + offset + "," + limit);
                
                return obj.AsList();
            }
            catch (Exception ex)
            {
                return new List<Models.dc_auto_turnover>();
            }
            
        }

        #region 特殊销售设置

        /// <summary>
        /// 获取特殊销售设置分页列表
        /// </summary>
        /// <param name="m"></param>
        /// <param name="offset"></param>
        /// <param name="limit"></param>
        /// <param name="total"></param>
        /// <returns></returns>
        public static List<dc_auto_config_promotion> ListConfigPromotion(Condition_ConfigPromotion m, int offset, int limit,ref int total)
        {
            var list = new List<dc_auto_config_promotion>();

            try
            {
                var sql = "select dacp.id,dacp.bailun_sku,dacp.warehouse_code,dbw.warehouse_name,dacp.promotion_time,dacp.count,dacp.bl_auditor,dacp.remark,dacp.status from dc_auto_config_promotion dacp " +
                            "left join dc_base_warehouse_oa dbw on dacp.warehouse_code=dbw.warehouse_code where 1 =1 ";

                if (!string.IsNullOrWhiteSpace(m.sku))
                {
                    sql += " and dacp.bailun_sku like '%" + m.sku+"%'";
                }

                if (!string.IsNullOrWhiteSpace(m.warehousecode))
                {
                    sql += " dacp.and warehouse_code='" + m.warehousecode+"'";
                }

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

                total = _connection.ExecuteScalar<int>("select count(0) from (" + sql + ") tb1");

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

                return obj.AsList();

            }
            catch (Exception ex)
            {
                return list;
            }
        }


        /// <summary>
        /// 保存销售设置
        /// </summary>
        /// <param name="m">销售设置model</param>
        /// <param name="username">当前操作人名称</param>
        /// <returns></returns>
        public static string SaveSaleSetting(Models.dc_auto_config_promotion m, string username)
        {
            var obj = _connection.QueryFirstOrDefault<Models.dc_auto_config_promotion>("select * from dc_auto_config_promotion where id=" + m.id);
            if (obj == null)
            {
                obj = new dc_auto_config_promotion
                {
                    gmt_create = DateTime.Now,
                };
            }
            else
            {
                if (obj.status != 0)
                {
                    return "该销售设置不能修改，目前状态为："+Enum.GetName(typeof(EnumSaleSettingStatus),obj.status);
                }
            }

            obj.id = m.id;
            obj.bailun_sku = m.bailun_sku;
            obj.bl_operator = username??"";
            obj.bl_auditor = "";
            obj.gmt_modified = DateTime.Now;
            obj.promotion_time = m.promotion_time;
            obj.remark = m.remark;
            obj.status = m.status;
            obj.warehouse_code = m.warehouse_code;
            obj.warehouse_name = m.warehouse_name;
            obj.count = m.count;

            try
            {
                if (obj.id > 0)
                {
                    var result = _connection.Update<Models.dc_auto_config_promotion>(obj);

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

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

        /// <summary>
        /// 获取特殊销售设置详情
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static dc_auto_config_promotion GetSaleSettingById(int id)
        {
            return _connection.QueryFirstOrDefault<dc_auto_config_promotion>("select * from dc_auto_config_promotion where id="+id);
        }




        #endregion

        #region 预估库存

        /// <summary>
        /// 获取预估库存详情
        /// </summary>
        /// <param name="sku"></param>
        /// <param name="warehousecode"></param>
        /// <returns></returns>
        public static dc_auto_inventory GetAutoInventoryBySkuWH(string sku,string warehousecode)
        {
            var sql = "select * from dc_auto_inventory where warehouse_code='"+warehousecode+ "' and bailun_sku='"+sku+"'";

            return _connection.QueryFirstOrDefault<dc_auto_inventory>(sql);
        }

        #endregion

        #region 预测入库

        /// <summary>
        /// 获取预测入库数详情
        /// </summary>
        /// <param name="sku">sku编码</param>
        /// <param name="warehousecode">库存编码</param>
        /// <param name="type">入库类型，1是采购入库, 2是调拨入库 </param>
        /// <returns></returns>
        public static dc_auto_inbound GetAutoInboundBySkuWH(string sku, string warehousecode,int type)
        {
            var sql = "select * from dc_auto_inbound where bailun_sku='" + sku+ "' and warehouse_code='" +warehousecode+"'";
            return _connection.QueryFirstOrDefault<dc_auto_inbound>(sql);
        }

        #endregion

        #region 预测断货

        /// <summary>
        /// 获取预测断货数详情
        /// </summary>
        /// <param name="sku"></param>
        /// <param name="warehousecode"></param>
        /// <returns></returns>
        public static dc_auto_short_supply GetAutoShortSupplyBySkuWH(string sku, string warehousecode)
        {
            var sql = "select * from dc_auto_short_supply where bailun_sku='" + sku + "' and warehouse_code='" + warehousecode + "'";

            return _connection.QueryFirstOrDefault<dc_auto_short_supply>(sql);
        }

        #endregion

    }
}
