using Dapper;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Threading.Tasks;
using VueWebCoreApi.Models;
using VueWebCoreApi.Tools;

namespace VueWebCoreApi.DLL.DAL
{
    public class MouldManagerDAL
    {
        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 MouldMangerSearch(string mouldstaus, string onstate, string mouldcode, string mouldname, string mouldspec, string createuser, string opendate, string closedate, int startNum, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
            try
            {
                if (mouldstaus != "" && mouldstaus != null)
                {
                    search += "and A.status=@mouldstaus ";
                    dynamicParams.Add("@mouldstaus", mouldstaus);
                }
                if (onstate != "" && onstate != null)
                {
                    search += "and A.usestaus=@onstate ";
                    dynamicParams.Add("@onstate", onstate);
                }
                if (mouldcode != "" && mouldcode != null)
                {
                    search += "and A.code like '%'+@mouldcode+'%' ";
                    dynamicParams.Add("@mouldcode", mouldcode);
                }
                if (mouldname != "" && mouldname != null)
                {
                    search += "and A.name like '%'+@mouldname+'%' ";
                    dynamicParams.Add("@mouldname", mouldname);
                }
                if (mouldspec != "" && mouldspec != null)
                {
                    search += "and A.spec like '%'+@mouldspec+'%' ";
                    dynamicParams.Add("@mouldspec", mouldspec);
                }
                if (createuser != "" && createuser != null)
                {
                    search += "and U.username like '%'+@createuser+'%' ";
                    dynamicParams.Add("@createuser", createuser);
                }
                if (opendate != "" && opendate != null)
                {
                    search += "and A.lm_date between @opendate and @closedate ";
                    dynamicParams.Add("@opendate", opendate + " 00:00:00");
                    dynamicParams.Add("@closedate", closedate + " 23:59:59");
                }
                if (search == "")
                {
                    search = "and 1=1 ";
                }
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select A.status,A.usestaus,A.code,A.name,A.spec,A.warehousecode,S.name as warehousename,
                            A.surp_life,'0' as serlife,A.resi_life,(case when isnull(M.mouldcode,'')='' then 'N' else 'Y' end) as is_part,
                            STUFF((SELECT ',' + B.partcode
                            from TMouldInfoPart B
                            where A.code = B.mouldcode
                            FOR XML PATH('')), 1, 1, '') AS partcode,
                            A.lm_user as usercode,U.username,A.lm_date   
                            from TMouldInfo A
                            left join TSecStck S on A.warehousecode=S.code
                            left join (
                            select distinct mouldcode from TMouldInfoPart 
                            )  M on A.code=M.mouldcode
                            left join TUser U on A.lm_user=U.usercode 
                            where 1=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 AddUpdateMouldManger(string mouldcode, string mouldname, string mouldspec, string warehousecode, string surplife, string resilife, string status, string mouldpart, string opertype, User us)
        {
            var sql = "";
            var dynamicParams = new DynamicParameters();
            List<object> list = new List<object>();
            try
            {
                if (opertype == "Add")
                {
                    //写入模具清单表
                    sql = @"insert into TMouldInfo(code,name,spec,status,usestatus,warehousecode,surp_life,resi_life,lm_user,lm_date) 
                               values(@code,@name,@spec,@status,@usestatus,@warehousecode,@surp_life,@resi_life,@lm_user,@lm_date)";
                    list.Add(new
                    {
                        str = sql,
                        parm = new
                        {
                            code = mouldcode,
                            name = mouldname,
                            spec = mouldspec,
                            status = status,
                            usestatus = "0",//使用状态：在库/出库/上机/下机/维修/外借(0,1,2,3,4,5)
                            warehousecode = warehousecode,
                            surp_life = surplife,
                            resi_life = resilife,
                            lm_user = us.usercode,
                            lm_date = DateTime.Now.ToString()
                        }
                    });
                    //写入模具关联产品表
                    List<string> mouldParts = JsonConvert.DeserializeObject<List<string>>(mouldpart);
                    for (int i = 0; i < mouldParts.Count; i++)
                    {
                        sql = @"insert into TMouldInfoPart(mouldcode,partcode,lm_user,lm_date) 
                               values(@mouldcode,@partcode,@lm_user,@lm_date)";
                        list.Add(new
                        {
                            str = sql,
                            parm = new
                            {
                                mouldcode = mouldcode,
                                partcode = mouldParts[i].ToString(),
                                lm_user = us.usercode,
                                lm_date = DateTime.Now.ToString()
                            }
                        });
                    }
                    bool aa = DapperHelper.DoTransaction(list);
                    if (aa)
                    {
                        //写入操作记录表
                        LogHelper.DbOperateLog(us.usercode, "新增", "模具:" + mouldcode, us.usertype);
                        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")
                {
                    //修改模具清单表
                    sql = @"update TMouldInfo set name=@name,spec=@spec,status=@status,warehousecode=@warehousecode,
                            surp_life=@surp_life,resi_life=@resi_life,lm_user=@lm_user,lm_date=@lm_date where code=@code";
                    list.Add(new
                    {
                        str = sql,
                        parm = new
                        {
                            code = mouldcode,
                            name = mouldname,
                            spec = mouldspec,
                            status = status,
                            warehousecode = warehousecode,
                            surp_life = surplife,
                            resi_life = resilife,
                            lm_user = us.usercode,
                            lm_date = DateTime.Now.ToString()
                        }
                    });
                    //删除模具关联产品表
                    sql = @"delete from TMouldInfoPart where mouldcode=@mouldcode";
                    list.Add(new
                    {
                        str = sql,
                        parm = new
                        {
                            mouldcode = mouldcode
                        }
                    });
                    //写入模具关联产品表
                    List<string> mouldParts = JsonConvert.DeserializeObject<List<string>>(mouldpart);
                    for (int i = 0; i < mouldParts.Count; i++)
                    {
                        sql = @"insert into TMouldInfoPart(mouldcode,partcode,lm_user,lm_date) 
                               values(@mouldcode,@partcode,@lm_user,@lm_date)";
                        list.Add(new
                        {
                            str = sql,
                            parm = new
                            {
                                mouldcode = mouldcode,
                                partcode = mouldParts[i].ToString(),
                                lm_user = us.usercode,
                                lm_date = DateTime.Now.ToString()
                            }
                        });
                    }
                    bool aa = DapperHelper.DoTransaction(list);
                    if (aa)
                    {
                        //写入操作记录表
                        LogHelper.DbOperateLog(us.usercode, "修改", "模具:" + mouldcode, us.usertype);
                        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 DeleteMouldManger(string mouldcode, User us)
        {
            var sql = "";
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            try
            {
                list.Clear();
                //判断模具是否存在点检记录、保养、维修、上下机、出入库记录,不允许删除
                sql = @"select mould_code   from TMouldchk_Proc_Main where mould_code=@devicecode
                        union all
                        select mould_code   from TMouldmaint_Proc_Main where mould_code=@devicecode
                        union all
                        select mould_code   from TMould_RepairRequest where mould_code=@devicecode";
                dynamicParams.Add("@devicecode", mouldcode);
                var data = DapperHelper.selectdata(sql, dynamicParams);
                if (data.Rows.Count > 0)
                {
                    mes.code = "300";
                    mes.count = 0;
                    mes.Message = "当前模具存在操作记录,不允许删除!";
                    mes.data = null;
                    return mes;
                }

                //删除模具清单表
                sql = @"delete TMouldInfo  where code=@mouldcode";
                list.Add(new { str = sql, parm = new { mouldcode = mouldcode } });
                //删除模具关联产品表
                sql = @"delete TMouldInfoPart  where mouldcode=@mouldcode";
                list.Add(new { str = sql, parm = new { mouldcode = mouldcode } });
                //删除模具绑定点检标准表
                sql = @"delete TMouldchk_Mould  where mould_code=@mouldcode";
                list.Add(new { str = sql, parm = new { mouldcode = mouldcode } });
                //删除模具绑定保养标准表
                sql = @"delete TMouldmai_Mould  where mould_code=@mouldcode";
                list.Add(new { str = sql, parm = new { mouldcode = mouldcode } });
                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 MouldMangerSearch(string checkitemcode, string checkitemname, string checkdescr, int startNum, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
            try
            {
                if (checkitemcode != "" && checkitemcode != null)
                {
                    search += "and A.code like '%'+@checkitemcode+'%' ";
                    dynamicParams.Add("@checkitemcode", checkitemcode);
                }
                if (checkitemname != "" && checkitemname != null)
                {
                    search += "and A.name like '%'+@checkitemname+'%' ";
                    dynamicParams.Add("@checkitemname", checkitemname);
                }
                if (checkdescr != "" && checkdescr != null)
                {
                    search += "and A.description like '%'+@checkdescr+'%' ";
                    dynamicParams.Add("@checkdescr", checkdescr);
                }
                if (search == "")
                {
                    search = "and 1=1 ";
                }
                //search = search.Substring(3);//截取索引2后面的字符
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select A.id,A.code,A.name,A.description,U.username as lm_user,A.lm_date 
                                from TMouldchk_Item A
                                left join TUser U on A.lm_user=U.usercode
                                where 1=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 AddUpdateMouldCheckItem(string checkitemid, string checkitemcode, string checkitemname, string checkitemdescr, User us, string opertype)
        {
            var dynamicParams = new DynamicParameters();
            try
            {
                if (opertype == "Add")
                {
                    var sql = @"insert into TMouldchk_Item(code,name,description,lm_user,lm_date) 
                              values(@checkitemcode,@checkitemname,@checkitemdescr,@usercode,@CreateDate)";
                    dynamicParams.Add("@checkitemcode", checkitemcode);
                    dynamicParams.Add("@checkitemname", checkitemname);
                    dynamicParams.Add("@checkitemdescr", checkitemdescr);
                    dynamicParams.Add("@usercode", us.usercode);
                    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 TMouldchk_Item set name=@checkitemname,description=@checkitemdescr,
                                lm_user=@usercode,lm_date=@CreateDate
                                where id=@checkitemid";
                    dynamicParams.Add("@checkitemid", checkitemid);
                    dynamicParams.Add("@checkitemname", checkitemname);
                    dynamicParams.Add("@checkitemdescr", checkitemdescr);
                    dynamicParams.Add("@usercode", us.usercode);
                    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 DeleteMouldCheckItem(string checkitemcode)
        {
            var sql = "";
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            try
            {
                list.Clear();
                //判断点检标准项目是否有关联点检标准,不允许删除
                sql = @"select *  from TMouldchk_Deta
                        where  code=@checkitemcode";
                dynamicParams.Add("@checkitemcode", checkitemcode);
                var data = DapperHelper.selectdata(sql, dynamicParams);
                if (data.Rows.Count > 0)
                {
                    mes.code = "300";
                    mes.count = 0;
                    mes.Message = "当前点检项目已关联标准,不允许删除!";
                    mes.data = null;
                    return mes;
                }

                //删除点检项目
                sql = @"delete TMouldchk_Item  where code=@checkitemcode";
                list.Add(new { str = sql, parm = new { checkitemcode = checkitemcode } });
                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

    }
}