using Dapper;
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.Models.UnitMaterial;
using VueWebCoreApi.Tools;

namespace VueWebCoreApi.DLL.DAL
{
    public class ProductModelDAL
    {
        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 TUnitGroup()
        {
            string sql = "";
            try
            {
                //获取计量单位组信息
                sql = @"select code,name,'1' as isGroup,data_sources from TUnitGroup ";
                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 TUnitData(string tunittype, string tunitgroupcode, int startNum, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            string sql = "";
            IEnumerable<object> data;
            try
            {
                if (tunittype == "" || tunittype == null)
                {
                    sql = @"select code,name,lm_user,lm_date,isGroup,idunitgroup,data_sources   
                             from TUnit  T where T.isGroup='0'
                             union all
                             select code,name,lm_user,lm_date,'1' as isGroup,code as idunitgroup,data_sources         
                             from TUnitGroup ";
                }
                if (tunittype == "S") //单计量
                {
                    sql = @"select code,name,lm_user,lm_date,isGroup,idunitgroup,data_sources  from TUnit where isSingleUnit='1' and isGroup='0'";
                }
                if (tunittype == "M" && (tunitgroupcode == "" || tunitgroupcode == null)) //多计量,且多计量组编码为空
                {
                    sql = @"select code,name,lm_user,lm_date,'1' as isGroup,code as idunitgroup,data_sources   from TUnitGroup";
                }
                if (tunittype == "M" && (tunitgroupcode != "" && tunitgroupcode != null)) //多计量,且多计量组编码不为空
                {
                    sql = @"select code,name,isMainUnit,changeRate,rateDescription,lm_user,lm_date,isGroup,idunitgroup,data_sources  
                            from TUnit where isSingleUnit='0' and isGroup='1' and idunitgroup=@tunitgroupcode";
                    dynamicParams.Add("@tunitgroupcode", tunitgroupcode);
                }
                // --------------查询指定数据--------------
                var total = 0; //总条数
                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 TUnitGroupProject(string unitcode, string isgroup, User us)
        {
            var dynamicParams = new DynamicParameters();
            string sql = "";
            try
            {
                //获取计量单位组对应的计量单位信息
                sql = @"select code,name,isMainUnit,changeRate,rateDescription,lm_user,lm_date,data_sources  
                        from TUnit where isSingleUnit='0' and isGroup=@isgroup and idunitgroup=@tunitgroupcode ";
                dynamicParams.Add("@isGroup", isgroup);
                dynamicParams.Add("@tunitgroupcode", unitcode);
                var data = DapperHelper.selectdata(sql, dynamicParams);
                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 AddUpdateTUnit(string menucode, string data_sources, string tunittype, string tunitgroupcode, string OperType, List<Unit> listjson, User us)
        {
            string sql = "";
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            try
            {
                if (OperType == "Add")
                {
                    //获取计量单位编码
                    mes = SeachEncodeJob.EncodingSeach(menucode);
                    if (mes.code == "300")
                    {
                        return mes;
                    }
                    if (tunittype == "S") //单计量
                    {
                        sql = @"select *  from TUnit where isGroup='0' and name=@name";
                        dynamicParams.Add("@name", listjson[0].unitname);
                        var data = DapperHelper.selectdata(sql, dynamicParams);
                        if (data.Rows.Count > 0)
                        {
                            mes.code = "300";
                            mes.count = 0;
                            mes.Message = "计量单位的名称不能重复,请重新输入!";
                            mes.data = null;
                            return mes;
                        }
                    }
                    if (tunittype == "M")//多计量
                    {
                        sql = @"select *  from TUnitGroup where name=@name";
                        dynamicParams.Add("@name", listjson[0].unitname);
                        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 = @"insert into TUnitGroup(code,name,lm_user,lm_date,data_sources) 
                            values(@code,@name,@lm_user,@lm_date,@data_sources)";
                        list.Add(new
                        {
                            str = sql,
                            parm = new
                            {
                                code = ((List<string>)mes.data)[0].ToString(),
                                name = listjson[0].unitname,
                                data_sources = data_sources,
                                lm_user = us.usercode,
                                lm_date = DateTime.Now.ToString()
                            }
                        });
                    }
                    for (int i = 0; i < listjson[0].children.Count; i++)
                    {
                        //写入计量单位
                        sql = @"insert into TUnit(code,name,isMainUnit,changeRate,rateDescription,isSingleUnit,disabled,idunitgroup,isGroup,data_sources,lm_user,lm_date) 
                            values(@code,@name,@isMainUnit,@changeRate,@rateDescription,@isSingleUnit,@disabled,@idunitgroup,@isGroup,@data_sources,@lm_user,@lm_date)";
                        list.Add(new
                        {
                            str = sql,
                            parm = new
                            {
                                code = tunittype == "S" ? ((List<string>)mes.data)[0].ToString() : listjson[0].children[i].unitcode,
                                name = listjson[0].children[i].unitname,
                                isMainUnit = listjson[0].children[i].isMainUnit,
                                changeRate = listjson[0].children[i].changeRate,
                                rateDescription = listjson[0].children[i].rateDescription,
                                isSingleUnit = listjson[0].children[i].isSingleUnit,
                                disabled = listjson[0].children[i].disabled,
                                idunitgroup = ((List<string>)mes.data)[0].ToString(),
                                isGroup = listjson[0].children[i].isGroup,
                                data_sources = data_sources,
                                lm_user = us.usercode,
                                lm_date = DateTime.Now.ToString()
                            }
                        });
                    }
                    //回写编码规则表
                    list.AddRange(SeachEncodeJob.StrEncodingUpdate(menucode, ((List<string>)mes.data)[1].ToString()));
                    bool aa = DapperHelper.DoTransaction(list);
                    if (aa)
                    {
                        //写入操作记录表
                        LogHelper.DbOperateLog(us.usercode, "新增", "新增了计量单位", 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")
                {
                    if (tunittype == "S") //单计量
                    {
                        sql = @"select *  from TUnit where isGroup='0' and name=@name";
                        dynamicParams.Add("@name", listjson[0].unitname);
                        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 = @"update TUnit set name=@name,lm_user=@lm_user,lm_date=@lm_date
                                where code=@code and isSingleUnit=@isSingleUnit and isGroup=@isGroup";
                        list.Add(new
                        {
                            str = sql,
                            parm = new
                            {
                                code = listjson[0].children[0].unitcode,
                                name = listjson[0].children[0].unitname,
                                isSingleUnit = listjson[0].children[0].isSingleUnit,
                                isGroup = listjson[0].children[0].isGroup,
                                lm_user = us.usercode,
                                lm_date = DateTime.Now.ToString()
                            }
                        });

                    }
                    if (tunittype == "M")//多计量
                    {
                        sql = @"select *  from TUnitGroup where name=@name";
                        dynamicParams.Add("@name", listjson[0].unitname);
                        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 = @"update TUnitGroup set name=@name,lm_user=@lm_user,lm_date=@lm_date where code=@code";
                        list.Add(new
                        {
                            str = sql,
                            parm = new
                            {
                                code = listjson[0].unitcode,
                                name = listjson[0].unitname,
                                lm_user = us.usercode,
                                lm_date = DateTime.Now.ToString()
                            }
                        });
                        //删除计量单位组明细(从计量单位表删除)
                        sql = @"delete TUnit where idunitgroup=@idunitgroup";
                        list.Add(new
                        {
                            str = sql,
                            parm = new
                            {
                                idunitgroup = tunitgroupcode
                            }
                        });
                        for (int i = 0; i < listjson[0].children.Count; i++)
                        {
                            //写入计量单位
                            sql = @"insert into TUnit(code,name,isMainUnit,changeRate,rateDescription,isSingleUnit,disabled,idunitgroup,isGroup,data_sources,lm_user,lm_date) 
                            values(@code,@name,@isMainUnit,@changeRate,@rateDescription,@isSingleUnit,@disabled,@idunitgroup,@isGroup,@data_sources,@lm_user,@lm_date)";
                            list.Add(new
                            {
                                str = sql,
                                parm = new
                                {
                                    code = listjson[0].children[i].unitcode,
                                    name = listjson[0].children[i].unitname,
                                    isMainUnit = listjson[0].children[i].isMainUnit,
                                    changeRate = listjson[0].children[i].changeRate,
                                    rateDescription = listjson[0].children[i].rateDescription,
                                    isSingleUnit = listjson[0].children[i].isSingleUnit,
                                    disabled = listjson[0].children[i].disabled,
                                    idunitgroup = listjson[0].children[i].idunitgroup,
                                    isGroup = listjson[0].children[i].isGroup,
                                    data_sources = data_sources,
                                    lm_user = us.usercode,
                                    lm_date = DateTime.Now.ToString()
                                }
                            });
                        }
                    }

                    bool aa = DapperHelper.DoTransaction(list);
                    if (aa)
                    {
                        //写入操作记录表
                        LogHelper.DbOperateLog(us.usercode, "修改", "修改了计量单位", 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 DeleteTUnit(string data_sources, string tunittype, string tunitgroupcode, string tunitcode, User us)
        {
            string sql = "";
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            try
            {
                if (tunittype == "S") //单计量
                {
                    sql = @"select *  from TMateriel_Info where isSingleUnit='1' and idunit=@idunit";
                    dynamicParams.Add("@idunit", tunitgroupcode);
                    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 TUnit where isSingleUnit='1' and isGroup='0' and code=@code and data_sources=@data_sources";
                    list.Add(new
                    {
                        str = sql,
                        parm = new
                        {
                            code = tunitcode,
                            data_sources = data_sources
                        }
                    });
                }
                if (tunittype == "M" && (tunitgroupcode == "" || tunitgroupcode == null)) //多计量,且多计量组编码为空
                {
                    sql = @"select *   from TUnit T
                            inner join TMateriel_Info M on T.idunitgroup=M.idunitgroup and T.code=M.idunit
                            where T.isSingleUnit='0'  and T.idunitgroup=@idunitgroup";
                    dynamicParams.Add("@idunitgroup", tunitgroupcode);
                    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 TUnitGroup where code=@code and data_sources=@data_sources";
                    list.Add(new
                    {
                        str = sql,
                        parm = new
                        {
                            code = tunitgroupcode,
                            data_sources = data_sources
                        }
                    });
                    //删除多计量单位组下所有计量单位
                    sql = @"delete TUnit where isSingleUnit='0' and isGroup='1' where idunitgroup=@idunitgroup and data_sources=@data_sources";
                    list.Add(new
                    {
                        str = sql,
                        parm = new
                        {
                            idunitgroup= tunitgroupcode,
                            data_sources = data_sources
                        }
                    });
                }
                if (tunittype == "M" && (tunitgroupcode != "" && tunitgroupcode != null)) //多计量,且多计量组编码不为空
                {
                    sql = @"select *   from TUnit T
                            inner join TMateriel_Info M on T.idunitgroup=M.idunitgroup and T.code=M.idunit
                            where T.isSingleUnit='0' and M.isSingleUnit='0' and T.idunitgroup=@idunitgroup and T.code=@idunit";
                    dynamicParams.Add("@idunitgroup", tunitgroupcode);
                    dynamicParams.Add("@idunit", tunitcode);
                    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 TUnit where isSingleUnit='0' and isGroup='1' where idunitgroup=@idunitgroup and code=@code and data_sources=@data_sources";
                    list.Add(new
                    {
                        str = sql,
                        parm = new
                        {
                            idunitgroup = tunitgroupcode,
                            code = tunitcode,
                            data_sources = data_sources
                        }
                    });
                }
                bool aa = DapperHelper.DoTransaction(list);
                if (aa)
                {
                    //写入操作记录表
                    LogHelper.DbOperateLog(us.usercode, "删除", "删除了计量单位", 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
    }
}