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

namespace VueWebCoreApi.DLL.DAL
{
    public class SopManageMentDAL
    {
        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[系统设置,设备SOP查询]
        public static ToMessage DeviceSopSearch(string search, string check, int startNum, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            string sech = "";
            try
            {
                if (search != "" && search != null)
                {
                    sech += "and concat(isnull(AA.eqptypecode,''),isnull(AA.eqptypename,''),isnull(AA.eqpcode,''),isnull(AA.eqpname,''),isnull(AA.filetypecode,''),isnull(AA.filetypename,''),isnull(AA.filename,''),isnull(AA.version,''),isnull(AA.username,'')) like '%'+@search+'%' ";
                    dynamicParams.Add("@search", search);
                }
                if (check == "Y")
                {
                    sech += "and AA.rn=1 ";
                    dynamicParams.Add("@rn", 1);
                }

                if (sech == "")
                {
                    sech = "and 1=1 ";
                }
                sech = sech.Substring(3);//截取索引2后面的字符
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select AA.id,AA.eqptypecode,AA.eqptypename,AA.eqpcode,AA.eqpname,AA.filetypecode,AA.filetypename,AA.filename,AA.version,AA.filepath,
                            AA.username,AA.lm_date,AA.rn  from(
                            select top 100  A.id,A.eqptypecode,T.name as eqptypename,A.eqpcode,E.name as eqpname,A.filetypecode,
                            (case when A.filetypecode='P001' then '作业指导书' when A.filetypecode='P002' then '检验指导书' when A.filetypecode='P003' then '图纸'  end) as filetypename,
                            A.filename,A.version,A.filepath,U.username,A.lm_date,
                            row_number() over(partition by A.eqptypecode,A.eqpcode,A.filetypecode order by replace(A.version,'V','') desc) rn  
                            from  TDeviceSop A
                            left  join TEqpInfo E on A.eqpcode=E.code
                            left  join TEqpType T on E.eqptype_code=T.code
                            left  join TUser U on A.lm_user=U.usercode
                            ) as AA  where  " + sech + "";
                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[系统设置,设备SOP新增]
        public static ToMessage DeviceSopAddSeave(string eqptypecode, string device, string filename, string filetypecode, IFormFile file, User us)
        {
            string sql = "";
            var dynamicParams = new DynamicParameters();
            List<object> list = new List<object>();
            try
            {
                list.Clear();
                if (file == null || file.Length <= 0)
                {
                    mes.code = "300";
                    mes.message = "文件不能为空！";
                }
                else
                {
                    //获取上传文件,组合存储路径
                    string FileName = "SOP" + DateTime.Now.ToString("yyyyMMddhhmmss");
                    string filePath = "wwwroot/DeviceSopFile/" + FileName + file.FileName;     //通过此对象获取文件名(存文件地址)
                    string filePath1 = "DeviceSopFile/" + FileName + file.FileName;     //通过此对象获取文件名(存数据表地址)

                    // 保存文件var stream = new FileStream(filePath, FileMode.Create)
                    using (var stream = new FileStream(filePath, FileMode.Create))
                    {
                        file.CopyTo(stream);
                    }

                    //获取设备集合
                    string[] devicecode = Array.ConvertAll<string, string>(device.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries), s => s.ToString()); //string分割转string[]
                    for (int i = 0; i < devicecode.Length; i++)
                    {
                        string version = "";
                        //查询当前设备是否存在同文件类型的SOP文件,存在就增加版本号
                        sql = @"select isnull(max(substring(version,charindex('V',version)+1,len(version)-charindex('V',version))),0)+1 as version    
                                from TDeviceSop 
                                where eqptypecode=@eqptypecode and eqpcode=@eqpcode and filetypecode=@filetypecode";
                        dynamicParams.Add("@eqptypecode", eqptypecode);
                        dynamicParams.Add("@eqpcode", devicecode[i].ToString());
                        dynamicParams.Add("@filetypecode", filetypecode);
                        var data = DapperHelper.selectdata(sql, dynamicParams);
                        if (data.Rows.Count > 0)
                        {
                            version = "V" + data.Rows[0]["version"].ToString();
                        }
                        else
                        {
                            version = "V1";
                        }
                        //写入设备SOP表
                        sql = @"insert into  TDeviceSop(eqptypecode,eqpcode,filename,filetypecode,filepath,version,lm_user,lm_date) 
                                values(@eqptypecode,@eqpcode,@filename,@filetypecode,@filepath,@version,@lm_user,@lm_date)";
                        list.Add(new
                        {
                            str = sql,
                            parm = new
                            {
                                eqptypecode = eqptypecode,
                                eqpcode = devicecode[i].ToString(),
                                filename = file.FileName,
                                filetypecode = filetypecode,
                                filepath = filePath1,
                                version = version,
                                lm_user = us.usercode,
                                lm_date = DateTime.Now.ToString()
                            }
                        });
                    }
                    bool aa = DapperHelper.DoTransaction(list);
                    if (aa)
                    {
                        //写入操作记录表
                        LogHelper.DbOperateLog(us.usercode, "新增设备SOP", "SOP文件:" + file.FileName + "设备:" + device.ToString(), us.usertype);
                        mes.code = "200";
                        mes.count = 0;
                        mes.message = "新增设备SOP成功!";
                        mes.data = null;
                    }
                    else
                    {
                        mes.code = "300";
                        mes.count = 0;
                        mes.message = "新增设备SOP失败!";
                        mes.data = null;
                    }
                }
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[系统设置,设备SOP删除]
        public static ToMessage DeviceSopDeleteSeave(string id, string eqpcode, string filepath, User us)
        {
            var sql = "";
            var dynamicParams = new DynamicParameters();
            List<object> list = new List<object>();
            try
            {
                list.Clear();
                //查询当前文件出去当前删除的,是否还被其他引用
                sql = @"select *  from TDeviceSop  where id<>@id  and filepath=@filepath";
                dynamicParams.Add("@id", id);
                dynamicParams.Add("@filepath", filepath);
                var data = DapperHelper.selectdata(sql, dynamicParams);
                if (data.Rows.Count <= 0)
                {
                    // 构建完整的文件路径  
                    var filePath = Path.Combine("wwwroot", filepath);
                    // 删除文件  
                    System.IO.File.Delete(filePath);
                }
                sql = @"delete TDeviceSop where id=@id";
                list.Add(new { str = sql, parm = new { id = id } });
                bool aa = DapperHelper.DoTransaction(list);
                if (aa)
                {
                    //写入操作记录表
                    LogHelper.DbOperateLog(us.usercode, "删除设备SOP", "设备:" + eqpcode, 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[系统设置,工艺SOP查询]
        public static ToMessage ProcessSopSearch(string search, string check, int startNum, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            string sech = "";
            try
            {
                if (search != "" && search != null)
                {
                    sech += "and concat(isnull(AA.materielcode,''),isnull(AA.materielname,''),isnull(AA.routecode,''),isnull(AA.routename,''),isnull(AA.stepcode,''),isnull(AA.stepname,''),isnull(AA.filetypecode,''),isnull(AA.filetypename,''),isnull(AA.filename,''),isnull(AA.version,''),isnull(AA.username,'')) like '%'+@search+'%' ";
                    dynamicParams.Add("@search", search);
                }
                if (check == "Y")
                {
                    sech += "and AA.rn=1 ";
                    dynamicParams.Add("@rn", 1);
                }

                if (sech == "")
                {
                    sech = "and 1=1 ";
                }
                sech = sech.Substring(3);//截取索引2后面的字符
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select AA.id,AA.materielcode,AA.materielname,AA.routecode,AA.routename,AA.stepcode,AA.stepname,AA.filetypecode,AA.filetypename,AA.filename,AA.version,AA.filepath,
                            AA.username,AA.lm_date,AA.rn  from(
                            select top 100  A.id,A.materielcode,M.partname as materielname,A.routecode,R.name as routename,A.stepcode,S.stepname,A.filetypecode,
                            (case when A.filetypecode='P001' then '作业指导书' when A.filetypecode='P002' then '检验指导书' when A.filetypecode='P003' then '图纸'  end) as filetypename,
                            A.filename,A.version,A.filepath,U.username,A.lm_date,
                            row_number() over(partition by A.materielcode,A.routecode,A.stepcode,A.filetypecode order by replace(A.version,'V','') desc) rn  
                            from  TProcessSop A
                            left  join TMateriel_Info M on A.materielcode=M.partcode
                            left  join TFlw_Rout R on A.routecode=R.code
                            left  join TStep S on A.stepcode=S.stepcode
                            left  join TUser U on A.lm_user=U.usercode
                            ) as AA  where  " + sech + "";
                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[系统设置,工艺SOP上传获取最大版本号]
        public static ToMessage ProcessSopMaxVersion(string partcode, string routecode, string stepcode, string filetypecode, User us)
        {
            string sql = "";
            var dynamicParams = new DynamicParameters();
            string sech = "";
            try
            {
                dynamic dynObj = JObject.Parse(us.mesSetting);
                bool route = dynObj.route;
                if (route) //按工艺路线模式
                {
                    sech += " and routecode=@routecode and stepcode=@stepcode";
                    dynamicParams.Add("@routecode", routecode);
                    dynamicParams.Add("@stepcode", stepcode);
                }
                else //工序模式
                {
                    sech += " and stepcode=@stepcode";
                    dynamicParams.Add("@stepcode", stepcode);
                }
                //获取SOP文件作业文件上传获取最大版本号
                sql = @"select isnull(max(substring(version,charindex('V',version)+1,len(version)-charindex('V',version))),0)+1 as file_version    
                        from TProcessSop where materielcode=@materielcode and filetypecode=@filetypecode "+sech;
                dynamicParams.Add("@materielcode", partcode);
                dynamicParams.Add("@filetypecode", filetypecode);
                var data = DapperHelper.selectdata(sql, dynamicParams);
                if (data.Rows.Count > 0)
                {
                    mes.code = "200";
                    mes.message = "获取版本号成功!";
                    mes.data = "V" + data.Rows[0]["file_version"].ToString();
                }
                else
                {
                    mes.code = "300";
                    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[系统设置,工艺SOP新增]
        public static ToMessage ProcessSopAddSeave(string partcode, string routecode, string stepcode, string filename, string filetypecode, string version, IFormFile file, User us)
        {
            string sql = "";
            var dynamicParams = new DynamicParameters();
            List<object> list = new List<object>();
            try
            {
                list.Clear();
                if (file == null || file.Length <= 0)
                {
                    mes.code = "300";
                    mes.message = "文件不能为空！";
                }
                else
                {
                    //获取上传文件,组合存储路径
                    string FileName = "SOP" + DateTime.Now.ToString("yyyyMMddhhmmss");
                    string filePath = "wwwroot/ProcessSopFile/" + FileName + file.FileName;     //通过此对象获取文件名(存文件地址)
                    string filePath1 = "ProcessSopFile/" + FileName + file.FileName;     //通过此对象获取文件名(存数据表地址)

                    // 保存文件var stream = new FileStream(filePath, FileMode.Create)
                    using (var stream = new FileStream(filePath, FileMode.Create))
                    {
                        file.CopyTo(stream);
                    }
                    //写入设备SOP表
                    sql = @"insert into  TProcessSop(materielcode,routecode,stepcode,filetypecode,filename,filepath,version,lm_user,lm_date) 
                                values(@materielcode,@routecode,@stepcode,@filetypecode,@filename,@filepath,@version,@lm_user,@lm_date)";
                    list.Add(new
                    {
                        str = sql,
                        parm = new
                        {
                            materielcode = partcode,
                            routecode = routecode,
                            stepcode = stepcode,
                            filetypecode = filetypecode,
                            filename = file.FileName,
                            filepath = filePath1,
                            version = version,
                            lm_user = us.usercode,
                            lm_date = DateTime.Now.ToString()
                        }
                    });

                    bool aa = DapperHelper.DoTransaction(list);
                    if (aa)
                    {
                        //写入操作记录表
                        LogHelper.DbOperateLog(us.usercode, "新增工艺SOP", "产品："+partcode+" SOP文件:" + file.FileName + " 工艺路线:" + routecode ?? "" + " 工序:" + stepcode.ToString(), us.usertype);
                        mes.code = "200";
                        mes.count = 0;
                        mes.message = "新增工艺SOP成功!";
                        mes.data = null;
                    }
                    else
                    {
                        mes.code = "300";
                        mes.count = 0;
                        mes.message = "新增工艺SOP失败!";
                        mes.data = null;
                    }
                }
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[系统设置,工艺SOP删除]
        public static ToMessage ProcessSopDeleteSeave(string id, string partcode, string routecode, string stepcode, string filepath, User us)
        {
            var sql = "";
            var dynamicParams = new DynamicParameters();
            List<object> list = new List<object>();
            try
            {
                list.Clear();
                //查询当前文件出去当前删除的,是否还被其他引用
                sql = @"select *  from TProcessSop  where id<>@id  and filepath=@filepath";
                dynamicParams.Add("@id", id);
                dynamicParams.Add("@filepath", filepath);
                var data = DapperHelper.selectdata(sql, dynamicParams);
                if (data.Rows.Count <= 0)
                {
                    // 构建完整的文件路径  
                    var filePath = Path.Combine("wwwroot", filepath);
                    // 删除文件  
                    System.IO.File.Delete(filePath);
                }
                sql = @"delete TProcessSop where id=@id";
                list.Add(new { str = sql, parm = new { id = id } });
                bool aa = DapperHelper.DoTransaction(list);
                if (aa)
                {
                    //写入操作记录表
                    LogHelper.DbOperateLog(us.usercode, "删除工艺SOP", "产品:" + partcode+" SOP文件地址："+filepath+" 工艺路线:"+ routecode ?? "" + " 工序:"+stepcode, 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[系统设置,工单SOP查询]
        public static ToMessage WrkOrderSearch(string search, string check, int startNum, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            string sech = "";
            try
            {
                if (search != "" && search != null)
                {
                    sech += "and concat(isnull(AA.wo,''),isnull(AA.materielcode,''),isnull(AA.materielname,''),isnull(AA.filetypecode,''),isnull(AA.filetypename,''),isnull(AA.filename,''),isnull(AA.version,''),isnull(AA.username,'')) like '%'+@search+'%' ";
                    dynamicParams.Add("@search", search);
                }
                if (check == "Y")
                {
                    sech += "and AA.rn=1 ";
                    dynamicParams.Add("@rn", 1);
                }

                if (sech == "")
                {
                    sech = "and 1=1 ";
                }
                sech = sech.Substring(3);//截取索引2后面的字符
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select AA.id,AA.wo,AA.materielcode,AA.materielname,AA.filetypecode,AA.filetypename,AA.filename,AA.version,AA.filepath,
                            AA.username,AA.lm_date,AA.rn  from(
                            select top 100  A.id,A.wo,A.materielcode,M.partname as materielname,A.filetypecode,
                            (case when A.filetypecode='P001' then '作业指导书' when A.filetypecode='P002' then '检验指导书' when A.filetypecode='P003' then '图纸'  end) as filetypename,
                            A.filename,A.version,A.filepath,U.username,A.lm_date,
                            row_number() over(partition by A.wo,A.materielcode,A.filetypecode order by replace(A.version,'V','') desc) rn  
                            from  TWrkOrderSop A
                            left  join TMateriel_Info M on A.materielcode=M.partcode
                            left  join TUser U on A.lm_user=U.usercode
                            ) as AA  where  " + sech + "";
                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[系统设置,工单SOP上传获取最大版本号]
        public static ToMessage WrkOrderSopMaxVersion(string wocode, string partcode, string filetypecode, User us)
        {
            string sql = "";
            var dynamicParams = new DynamicParameters();
            try
            {
                //获取SOP文件作业文件上传获取最大版本号
                sql = @"select isnull(max(substring(version,charindex('V',version)+1,len(version)-charindex('V',version))),0)+1 as file_version    
                        from TWrkOrderSop where wo=@wo and materielcode=@materielcode and filetypecode=@filetypecode ";
                dynamicParams.Add("@wo", wocode);
                dynamicParams.Add("@materielcode", partcode);
                dynamicParams.Add("@filetypecode", filetypecode);
                var data = DapperHelper.selectdata(sql, dynamicParams);
                if (data.Rows.Count > 0)
                {
                    mes.code = "200";
                    mes.message = "获取版本号成功!";
                    mes.data = "V" + data.Rows[0]["file_version"].ToString();
                }
                else
                {
                    mes.code = "300";
                    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[系统设置,工单SOP新增]
        public static ToMessage WrkOrderSopAddSeave(string wocode, string partcode, string filename, string filetypecode, string version, IFormFile file, User us)
        {
            string sql = "";
            var dynamicParams = new DynamicParameters();
            List<object> list = new List<object>();
            try
            {
                list.Clear();
                if (file == null || file.Length <= 0)
                {
                    mes.code = "300";
                    mes.message = "文件不能为空！";
                }
                else
                {
                    //获取上传文件,组合存储路径
                    string FileName = "SOP" + DateTime.Now.ToString("yyyyMMddhhmmss");
                    string filePath = "wwwroot/WrkOrderSopFile/" + FileName + file.FileName;     //通过此对象获取文件名(存文件地址)
                    string filePath1 = "WrkOrderSopFile/" + FileName + file.FileName;     //通过此对象获取文件名(存数据表地址)

                    // 保存文件var stream = new FileStream(filePath, FileMode.Create)
                    using (var stream = new FileStream(filePath, FileMode.Create))
                    {
                        file.CopyTo(stream);
                    }
                    //写入设备SOP表
                    sql = @"insert into  TWrkOrderSop(wo,materielcode,filetypecode,filename,filepath,version,lm_user,lm_date) 
                                values(@wo,@materielcode,@filetypecode,@filename,@filepath,@version,@lm_user,@lm_date)";
                    list.Add(new
                    {
                        str = sql,
                        parm = new
                        {
                            wo=wocode,
                            materielcode = partcode,
                            filetypecode = filetypecode,
                            filename = file.FileName,
                            filepath = filePath1,
                            version = version,
                            lm_user = us.usercode,
                            lm_date = DateTime.Now.ToString()
                        }
                    });

                    bool aa = DapperHelper.DoTransaction(list);
                    if (aa)
                    {
                        //写入操作记录表
                        LogHelper.DbOperateLog(us.usercode, "新增工单SOP", "工单：" + wocode + "产品：" + partcode + " SOP文件:" + file.FileName, us.usertype);
                        mes.code = "200";
                        mes.count = 0;
                        mes.message = "新增工单SOP成功!";
                        mes.data = null;
                    }
                    else
                    {
                        mes.code = "300";
                        mes.count = 0;
                        mes.message = "新增工单SOP失败!";
                        mes.data = null;
                    }
                }
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[系统设置,工单SOP删除]
        public static ToMessage WrkOrderSopDeleteSeave(string id, string wocode, string partcode, string filepath, User us)
        {
            var sql = "";
            var dynamicParams = new DynamicParameters();
            List<object> list = new List<object>();
            try
            {
                list.Clear();
                //查询当前文件出去当前删除的,是否还被其他引用
                sql = @"select *  from TWrkOrderSop  where id<>@id  and filepath=@filepath";
                dynamicParams.Add("@id", id);
                dynamicParams.Add("@filepath", filepath);
                var data = DapperHelper.selectdata(sql, dynamicParams);
                if (data.Rows.Count <= 0)
                {
                    // 构建完整的文件路径  
                    var filePath = Path.Combine("wwwroot", filepath);
                    // 删除文件  
                    System.IO.File.Delete(filePath);
                }
                sql = @"delete TWrkOrderSop where id=@id";
                list.Add(new { str = sql, parm = new { id = id } });
                bool aa = DapperHelper.DoTransaction(list);
                if (aa)
                {
                    //写入操作记录表
                    LogHelper.DbOperateLog(us.usercode, "删除工艺SOP", "工单:" + wocode + "产品:" + partcode + " SOP文件地址：" + filepath, 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

    }
}