using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using VueWebApi.Tools;

namespace VueWebApi.DLL.DAL
{
    public class MaterialManagerDAL
    {
        public static DataTable dt;    //定义全局变量dt
        public static bool res;       //定义全局变量dt
        public static ToMessage mes = new ToMessage(); //定义全局返回信息对象
        public static string strProcName = ""; //定义全局sql变量
        public static List<SqlParameter> listStr = new List<SqlParameter>(); //定义全局参数集合
        public static SqlParameter[] parameters; //定义全局SqlParameter参数数组


        #region[仓库查询]
        public static ToMessage WareHouseDefSearch(string warehousecode, string warehousename, string description, string createuser, int startNum, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
            try
            {
                if (warehousecode != "" && warehousecode != null)
                {
                    search += "and code like '%'+@warehousecode+'%' ";
                    dynamicParams.Add("@warehousecode", warehousecode);
                }
                if (warehousename != "" && warehousename != null)
                {
                    search += "and name like '%'+@warehousename+'%' ";
                    dynamicParams.Add("@warehousename", warehousename);
                }
                if (description != "" && description != null)
                {
                    search += "and description like '%'+@description+'%' ";
                    dynamicParams.Add("@description", description);
                }
                if (createuser != "" && createuser != null)
                {
                    search += "and lm_user like '%'+@createuser+'%' ";
                    dynamicParams.Add("@createuser", createuser);
                }
                if (search == "")
                {
                    search = "and 1=1 ";
                }
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select id,code,name,description,lm_user,lm_date from T_Sec_Stck
                            where  is_delete<>'1' " + search;
                var data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
                mes.code = "200";
                mes.Message = "查询成功!";
                mes.count = total;
                mes.data = data.ToList();
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[仓库新增编辑]
        public static ToMessage AddUpdateWareHouseDef(string warehouseid, string warehousecode, string warehousename, string description, string username, string operType)
        {
            var dynamicParams = new DynamicParameters();
            try
            {
                if (operType == "Add")
                {
                    var sql = @"insert into T_Sec_Stck(code,name,description,lm_user,lm_date) 
                            values(@warehousecode,@warehousename,@description,@username,@CreateDate)";
                    dynamicParams.Add("@warehousecode", warehousecode);
                    dynamicParams.Add("@warehousename", warehousename);
                    dynamicParams.Add("@description", description);
                    dynamicParams.Add("@username", username);
                    dynamicParams.Add("@CreateDate", DateTime.Now.ToString());
                    int cont = DapperHelper.SQL(sql, dynamicParams);
                    if (cont > 0)
                    {
                        mes.code = "200";
                        mes.count = 0;
                        mes.Message = "新增操作成功!";
                        mes.data = null;
                    }
                    else
                    {
                        mes.code = "300";
                        mes.count = 0;
                        mes.Message = "新增操作失败!";
                        mes.data = null;
                    }
                }
                if (operType == "Update")
                {
                    var sql = @"update T_Sec_Stck set name=@warehousename,description=@description,lm_user=@username,lm_date=@CreateDate where id=@warehouseid";
                    dynamicParams.Add("@warehouseid", warehouseid);
                    dynamicParams.Add("@warehousename", warehousename);
                    dynamicParams.Add("@description", description);
                    dynamicParams.Add("@username", username);
                    dynamicParams.Add("@CreateDate", DateTime.Now.ToString());
                    int cont = DapperHelper.SQL(sql, dynamicParams);
                    if (cont > 0)
                    {
                        mes.code = "200";
                        mes.count = 0;
                        mes.Message = "修改操作成功!";
                        mes.data = null;
                    }
                    else
                    {
                        mes.code = "300";
                        mes.count = 0;
                        mes.Message = "修改操作失败!";
                        mes.data = null;
                    }
                }
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region [仓库删除]
        public static ToMessage DeleteWareHouseDef(string warehousecode)
        {
            var sql = "";
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            try
            {
                list.Clear();
                //判断仓库下是否有关联库位
                sql = @"select B.code,B.name  from T_Sec_Stck A
                        inner join T_Sec_Loca B on A.code=B.stock_code and B.is_delete<>'1'
                        where A.code=@warehousecode and A.is_delete<>'1'";
                dynamicParams.Add("@warehousecode", warehousecode);
                var data = DapperHelper.selectdata(sql, dynamicParams);
                if (data.Rows.Count > 0)
                {
                    mes.code = "300";
                    mes.count = 0;
                    mes.Message = "当前仓库下有关联库位,不允许删除!";
                    mes.data = null;
                }
                else
                {
                    //删除仓库
                    sql = @"delete T_Sec_Stck where code=@warehousecode";
                    list.Add(new { str = sql, parm = new { warehousecode = warehousecode } });
                    bool aa = DapperHelper.DoTransaction(list);
                    if (aa)
                    {
                        mes.code = "200";
                        mes.count = 0;
                        mes.Message = "删除成功!";
                        mes.data = null;
                    }
                    else
                    {
                        mes.code = "300";
                        mes.count = 0;
                        mes.Message = "删除失败!";
                        mes.data = null;
                    }
                }
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion




        #region[所属仓库接口]
        public static ToMessage WareHouseSelect()
        {
            string sql = "";
            try
            {
                //获取设备类型数据
                sql = @"select code,name from T_Sec_Stck where is_delete<>'1'";
                var data = DapperHelper.selecttable(sql);
                mes.code = "200";
                mes.Message = "查询成功!";
                mes.data = data;
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[库位定义查询]
        public static ToMessage StorageDefSearch(string storagecode, string storagename, string description, string createuser, string stockcode, int startNum, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
            try
            {
                if (storagecode != "" && storagecode != null)
                {
                    search += "and A.code like '%'+@storagecode+'%' ";
                    dynamicParams.Add("@storagecode", storagecode);
                }
                if (storagename != "" && storagename != null)
                {
                    search += "and A.name like '%'+@storagename+'%' ";
                    dynamicParams.Add("@storagename", storagename);
                }
                if (description != "" && description != null)
                {
                    search += "and A.description like '%'+@description+'%' ";
                    dynamicParams.Add("@description", description);
                }
                if (createuser != "" && createuser != null)
                {
                    search += "and A.lm_user like '%'+@createuser+'%' ";
                    dynamicParams.Add("@createuser", createuser);
                }
                if (stockcode != "" && stockcode != null)
                {
                    search += "and A.stock_code=@stockcode ";
                    dynamicParams.Add("@stockcode", stockcode);
                }
                if (search == "")
                {
                    search = "and 1=1 ";
                }
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select A.id,A.code,A.name,A.description,A.stock_code,B.name as stock_name,A.lm_user,A.lm_date  
                            from T_Sec_Loca A
                            left join T_Sec_Stck B on A.stock_code=B.code
                            where A.is_delete<>'1' " + search;
                var data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
                mes.code = "200";
                mes.Message = "查询成功!";
                mes.count = total;
                mes.data = data.ToList();
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[库位定义新增编辑]
        public static ToMessage AddUpdateStorageDef(string storageid, string storagecode, string storagename, string description, string stockcode, string username, string operType)
        {
            var dynamicParams = new DynamicParameters();
            try
            {
                if (operType == "Add")
                {
                    var sql = @"insert into T_Sec_Loca(code,name,description,stock_code,lm_user,lm_date) 
                            values(@storagecode,@storagename,@description,@stockcode,@username,@CreateDate)";
                    dynamicParams.Add("@storagecode", storagecode);
                    dynamicParams.Add("@storagename", storagename);
                    dynamicParams.Add("@description", description);
                    dynamicParams.Add("@stockcode", stockcode);
                    dynamicParams.Add("@username", username);
                    dynamicParams.Add("@CreateDate", DateTime.Now.ToString());
                    int cont = DapperHelper.SQL(sql, dynamicParams);
                    if (cont > 0)
                    {
                        mes.code = "200";
                        mes.count = 0;
                        mes.Message = "新增操作成功!";
                        mes.data = null;
                    }
                    else
                    {
                        mes.code = "300";
                        mes.count = 0;
                        mes.Message = "新增操作失败!";
                        mes.data = null;
                    }
                }
                if (operType == "Update")
                {
                    var sql = @"update T_Sec_Loca set name=@storagename,description=@description,stock_code=@stockcode,lm_user=@username,lm_date=@CreateDate where id=@storageid";
                    dynamicParams.Add("@storageid", storageid);
                    dynamicParams.Add("@storagename", storagename);
                    dynamicParams.Add("@description", description);
                    dynamicParams.Add("@stockcode", stockcode);
                    dynamicParams.Add("@username", username);
                    dynamicParams.Add("@CreateDate", DateTime.Now.ToString());
                    int cont = DapperHelper.SQL(sql, dynamicParams);
                    if (cont > 0)
                    {
                        mes.code = "200";
                        mes.count = 0;
                        mes.Message = "修改操作成功!";
                        mes.data = null;
                    }
                    else
                    {
                        mes.code = "300";
                        mes.count = 0;
                        mes.Message = "修改操作失败!";
                        mes.data = null;
                    }
                }
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[库位定义删除]
        public static ToMessage DeleteStorageDef(string storagecode)
        {
            var sql = "";
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            try
            {
                list.Clear();
                //删除仓位
                sql = @"delete T_Sec_Loca where code=@storagecode";
                list.Add(new { str = sql, parm = new { storagecode = storagecode } });
                bool aa = DapperHelper.DoTransaction(list);
                if (aa)
                {
                    mes.code = "200";
                    mes.count = 0;
                    mes.Message = "删除成功!";
                    mes.data = null;
                }
                else
                {
                    mes.code = "300";
                    mes.count = 0;
                    mes.Message = "删除失败!";
                    mes.data = null;
                }
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion




    }
}