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

namespace VueWebApi.DLL.DAL
{
    public class QualityManagementDAL
    {
        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 DedectSearch(string defectcode, string defectname, string defectdescr, int startNum, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
            try
            {
                if (defectcode != "" && defectcode != null)
                {
                    search += "and code=@defectcode ";
                    dynamicParams.Add("@defectcode", defectcode);
                }
                if (defectname != "" && defectname != null)
                {
                    search += "and name like '%'+@defectname+'%' ";
                    dynamicParams.Add("@defectname", defectname);
                }
                if (defectdescr != "" && defectdescr != null)
                {
                    search += "and descr like '%'+@defectdescr+'%' ";
                    dynamicParams.Add("@defectdescr", defectdescr);
                }

                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select code,name,descr,lm_user,lm_date  from TDefect 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 AddUpdateDedect(string defectcode, string defectname, string defectdescr, string opertype, string username)
        {
            var sql = "";
            var dynamicParams = new DynamicParameters();
            List<object> list = new List<object>();
            try
            {
                if (opertype == "Add")
                {
                    //写入缺陷定义定义表
                    sql = @"insert into TDefect(code,name,descr,lm_user,lm_date)
                                values(@defectcode,@defectname,@defectdescr,@username,@CreateDate)";
                    list.Add(new
                    {
                        str = sql,
                        parm = new
                        {
                            defectcode = defectcode,
                            defectname = defectname,
                            defectdescr = defectdescr,
                            username = username,
                            CreateDate = DateTime.Now.ToString()
                        }
                    });
                    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;
                    }
                }
                if (opertype == "Update")
                {
                    sql = @"update TDefect set name=@defectname,descr=@defectdescr,lm_user=@username,lm_date=@CreateDate where code=@defectcode";
                    list.Add(new
                    {
                        str = sql,
                        parm = new
                        {
                            defectcode = defectcode,
                            defectname = defectname,
                            defectdescr = defectdescr,
                            username = username,
                            CreateDate = DateTime.Now.ToString()
                        }
                    });
                    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 DeleteDedect(string defectcode)
        {
            var sql = "";
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            try
            {
                sql = @"select * from  CSR_WorkRecord_Defect  where defect_code=@defectcode";
                dynamicParams.Add("@defectcode", defectcode);
                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 TDefect  where code=@defectcode";
                list.Add(new { str = sql, parm = new { defectcode = defectcode } });

                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 StepCheckStanedSearch(string stanedcode, string stanedname, string staneddescr, int startNum, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
            try
            {
                if (stanedcode != "" && stanedcode != null)
                {
                    search += "and code=@stanedcode ";
                    dynamicParams.Add("@stanedcode", stanedcode);
                }
                if (stanedname != "" && stanedname != null)
                {
                    search += "and name like '%'+@stanedname+'%' ";
                    dynamicParams.Add("@stanedname", stanedname);
                }
                if (staneddescr != "" && staneddescr != null)
                {
                    search += "and descr like '%'+@staneddescr+'%' ";
                    dynamicParams.Add("@staneddescr", staneddescr);
                }

                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select code,name,descr,lm_user,lm_date  from TStepCheckStandard 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 EditStepCheckStanedSearch(string defectcode)
        {
            string sql = "";
            var dynamicParams = new DynamicParameters();
            try
            {
                //获取工序检验标准信息
                sql = @"select code,name,descr
                        from TStepCheckStandard
                        where code=@defectcode and is_delete<>'1'";
                dynamicParams.Add("@defectcode", defectcode);
                var data = DapperHelper.selectdata(sql, dynamicParams);

                RoutEdit rout = new RoutEdit();
                rout.code = data.Rows[0]["CODE"].ToString();
                rout.name = data.Rows[0]["NAME"].ToString();
                rout.description = data.Rows[0]["DESCR"].ToString();

                //根据工序检验标准编码获取关联的工序检验项目信息
                sql = @"select B.code,B.name,A.stepcheckitem_desc  from TStepCheckStandardSub A
                        left join TStepCheckItem  B on A.stepcheckitem_code=B.code
                        where B.is_delete<>'1' and A.stepstaned_code=@stepstaned_code order by A.stepcheckitem_seq asc";
                dynamicParams.Add("@stepstaned_code", rout.code);
                var data0 = DapperHelper.selectdata(sql, dynamicParams);
                rout.Data = data0;

                mes.code = "200";
                mes.Message = "查询成功!";
                mes.data = rout;
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[工序检验标准新增、编辑获取检验项目下拉列表]
        public static ToMessage StepCheckItemSelect()
        {
            string sql = "";
            try
            {
                //获取工序点检项目数据
                sql = @"select code,name,descr from TStepCheckItem 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 AddUpdateStepCheckStaned(string opertype, RoutEdit json, string username)
        {
            var sql = "";
            var dynamicParams = new DynamicParameters();
            List<object> list = new List<object>();
            try
            {
                if (opertype == "Add")
                {
                    //新增工序检验标准主表
                    sql = @"insert into TStepCheckStandard(code,name,descr,lm_user,lm_date) values(@code,@name,@descr,@lm_user,@lm_date)";
                    list.Add(new { str = sql, parm = new { code = json.code, name = json.name, descr = json.description, lm_user = username, lm_date = DateTime.Now.ToString() } });
                    for (int i = 0; i < json.Data.Rows.Count; i++)
                    {
                        //新增工序检验标准子表
                        sql = @"insert TStepCheckStandardSub (stepstaned_code,stepcheckitem_seq,stepcheckitem_code,stepcheckitem_desc,lm_user,lm_date) 
                                values(@stepstaned_code,@stepcheckitem_seq,@stepcheckitem_code,@stepcheckitem_desc,@lm_user,@lm_date)";
                        list.Add(new { str = sql, parm = new { stepstaned_code = json.code, stepcheckitem_seq = Convert.ToInt32(json.Data.Rows[i]["STEPCHECKITEM_SEQ"].ToString()), stepcheckitem_code = json.Data.Rows[i]["STEPCHECKITEM_CODE"].ToString(), stepcheckitem_desc= json.Data.Rows[i]["STEPCHECKITEM_DESC"].ToString(), lm_user = username, lm_date = DateTime.Now.ToString() } });
                    }
                    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;
                    }
                }
                if (opertype == "Update") 
                {
                    //删除工序检验标准子表
                    sql = @"delete from TStepCheckStandard where stepstaned_code=@stepstaned_code";
                    list.Add(new { str = sql, parm = new { stepstaned_code = json.code} });
                    //修改工序检验标准主表
                    sql = @"update  TStepCheckStandard set name=@stepstaned_name,descr=@stepstaned_desc where stepstaned_code=@stepstaned_code";
                    list.Add(new { str = sql, parm = new { stepstaned_name = json.code, stepstaned_desc=json.description } });
                    //新增工序检验标准子表
                    for (int i = 0; i < json.Data.Rows.Count; i++)
                    {
                        //新增工序检验标准子表
                        sql = @"insert TStepCheckStandardSub (stepstaned_code,stepcheckitem_seq,stepcheckitem_code,stepcheckitem_desc,lm_user,lm_date) 
                                values(@stepstaned_code,@stepcheckitem_seq,@stepcheckitem_code,@stepcheckitem_desc,@lm_user,@lm_date)";
                        list.Add(new { str = sql, parm = new { stepstaned_code = json.code, stepcheckitem_seq = Convert.ToInt32(json.Data.Rows[i]["STEPCHECKITEM_SEQ"].ToString()), stepcheckitem_code = json.Data.Rows[i]["STEPCHECKITEM_CODE"].ToString(), stepcheckitem_desc = json.Data.Rows[i]["STEPCHECKITEM_DESC"].ToString(), lm_user = username, lm_date = DateTime.Now.ToString() } });
                    }

                    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 StepCheckItemSearch(string itemcode, string itemname, string itemdescr, int startNum, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
            try
            {
                if (itemcode != "" && itemcode != null)
                {
                    search += "and code=@itemcode ";
                    dynamicParams.Add("@itemcode", itemcode);
                }
                if (itemname != "" && itemname != null)
                {
                    search += "and name like '%'+@itemname+'%' ";
                    dynamicParams.Add("@itemname", itemname);
                }
                if (itemdescr != "" && itemdescr != null)
                {
                    search += "and descr like '%'+@itemdescr+'%' ";
                    dynamicParams.Add("@itemdescr", itemdescr);
                }

                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select code,name,descr,lm_user,lm_date  from TStepCheckItem 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 AddUpdateStepCheckItem(string checkitemcode, string checkitemname, string checkitemdescr, string opertype, string username)
        {
            var sql = "";
            var dynamicParams = new DynamicParameters();
            List<object> list = new List<object>();
            try
            {
                if (opertype == "Add")
                {
                    //写入缺陷定义定义表
                    sql = @"insert into TStepCheckItem(code,name,descr,lm_user,lm_date)
                                values(@checkitemcode,@checkitemname,@checkitemdescr,@username,@CreateDate)";
                    list.Add(new
                    {
                        str = sql,
                        parm = new
                        {
                            checkitemcode = checkitemcode,
                            checkitemname = checkitemname,
                            checkitemdescr = checkitemdescr,
                            username = username,
                            CreateDate = DateTime.Now.ToString()
                        }
                    });
                    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;
                    }
                }
                if (opertype == "Update")
                {
                    sql = @"update TStepCheckItem set name=@checkitemname,descr=@checkitemdescr,lm_user=@username,lm_date=@CreateDate where code=@checkitemcode";
                    list.Add(new
                    {
                        str = sql,
                        parm = new
                        {
                            checkitemcode = checkitemcode,
                            checkitemname = checkitemname,
                            checkitemdescr = checkitemdescr,
                            username = username,
                            CreateDate = DateTime.Now.ToString()
                        }
                    });
                    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 DeleteStepCheckItem(string checkitemcode)
        {
            var sql = "";
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            try
            {
                sql = @"select * from  TStepCheckRecordSub  where checkitem_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 = @"update TStepCheckItem set is_delete='1'  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
    }
}