using AUTS.Domain.Application;
using AUTS.Domain.ViewModels;
using AUTS.Services.DBUtility.Custom;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Dynamic;
using AUTS.Services.DBUtility.Common;
using AUTS.Domain.Entities;
using System.Diagnostics;
using AUTS.Domain.ViewModels.AutsChart;
using System.Data;
using Google.Protobuf.WellKnownTypes;
using SqlSugar;
using Org.BouncyCastle.Tls;
using System.Data.Entity.Infrastructure;
using System.Web.Mvc;
using System.Xml.Linq;
using StackExchange.Redis;


namespace AUTS.Services.Manager
{
    public static class SqlConnect
    {

        private static readonly string connectionString = "server=blv-rd.tech;database=uts_manage;user id=uts_BoMei;password=*&Y8ug*gFx6;port=3307;pooling=True";

        public static readonly string connectionSrt = "Server =blv-cloud-db.mysql.rds.aliyuncs.com;Database=uts_db;Uid=blv_rcu;Pwd=fnadiaJDIJ7546;charset=utf8;port=3307";

        public static List<Uts_Importinfo_Log> SqlseleteImportLog(int page = 1, int pagesize = 10, string Search = "", int Property = -1, string startTime = "", string EndTime = "")
        {
            List<Uts_Importinfo_Log> _UserOperationList = new List<Uts_Importinfo_Log>();
            try
            {

                string connectionString = Users.connectionSrt;
                using (MySqlConnection connection = new MySqlConnection(connectionString))
                {
                    using (MySqlCommand cmd = new MySqlCommand())
                    {
                        connection.Open();
                        cmd.Connection = connection;
                        //string strsqlcnt = "select * from Uts_Importinfo_Log where 1=1 ";
                        var strsql = "select * from Uts_Importinfo_Log where 1=1 ";
                        if (!string.IsNullOrEmpty(Search) && Search != "0")
                        {
                            strsql += " and Operationtpye= '" + Search + "'";
                            //strsqlcnt += " and Operationtpye= '" + Search + "'";
                        }
                        if (Property!=-1)
                        {
                            strsql += " and Result= " + Property;
                            //strsqlcnt += " and Result= " + Property;
                        }
                        if (!string.IsNullOrEmpty(startTime) && !string.IsNullOrEmpty(EndTime))
                        {
                            DateTime startTimea = DateTime.Parse(startTime);
                            DateTime endTimea = DateTime.Parse(EndTime);
                            strsql += " and ImportDateTime>= '" + startTimea + "' and ImportDateTime<= '" + endTimea + "'";
                            //strsqlcnt += " and ImportDateTime>= '" + startTimea + "' and ImportDateTime<= '" + endTimea + "'";
                        }
                        strsql += " ORDER BY   ImportDateTime DESC ";
                        //strsqlcnt += " ORDER BY   ImportDateTime DESC ";
                        //System.Data.DataSet dataSet1 = new System.Data.DataSet();
                        //MySqlDataAdapter command1 = new MySqlDataAdapter(strsqlcnt, connection);
                        //command1.Fill(dataSet1, "ds");
                        strsql += " LIMIT " + (page - 1) * pagesize + "," + pagesize + "; ";
                        System.Data.DataSet dataSet = new System.Data.DataSet();
                        MySqlDataAdapter command = new MySqlDataAdapter(strsql, connection);
                        command.Fill(dataSet, "ds");
                        var dalHelperCustom = new DALHelperCustom<Uts_Importinfo_Log>("Uts_Importinfo_Log");
                        var UserlogList = dalHelperCustom.DataRowToModels(dataSet.Tables[0]);

                        if (UserlogList != null && UserlogList.Count > 0)
                        {
                            foreach (var item in UserlogList)
                            {
                                Uts_Importinfo_Log tBL_UTS_UserOperation = new Uts_Importinfo_Log();
                                tBL_UTS_UserOperation.ID = item.ID;
                                tBL_UTS_UserOperation.ProjectID = item.ProjectID;
                                tBL_UTS_UserOperation.Dbname = item.Dbname;
                                tBL_UTS_UserOperation.Operationtpye = item.Operationtpye;
                                tBL_UTS_UserOperation.ImportDateTime = item.ImportDateTime;
                                tBL_UTS_UserOperation.PartData = item.PartData;
                                tBL_UTS_UserOperation.Result = item.Result;
                                tBL_UTS_UserOperation.ReturnResult = item.ReturnResult;
                                _UserOperationList.Add(tBL_UTS_UserOperation);
                            }
                        }
                    }
                }

                return _UserOperationList;

            }
            catch (Exception ex)
            {
                LogHelp.WriteExceptionLog(ex);
                return null;
              
            }
        }



    /// <summary>
    /// 获取用户日志
    /// </summary>
    /// <param name="name"></param>
    /// <returns></returns>
    public static List<TBL_UTS_UserOperation> SqlseleteUserLog(string name = "", int page = 1, int pagesize = 10)
        {

            List<TBL_UTS_UserOperation> _UserOperationList = new List<TBL_UTS_UserOperation>();
            try
            {
                using (MySqlConnection connection = new MySqlConnection(connectionString))
                {
                    using (MySqlCommand cmd = new MySqlCommand())
                    {
                        connection.Open();
                        cmd.Connection = connection;

                        var strsql = "select * from TBL_UTS_UserOperation";
                        if (!string.IsNullOrEmpty(name) && name != "0")
                        {
                            strsql += " where UserName= '" + name + "'";
                        }
                        strsql += " GROUP BY   CreationTime DESC ";
                        strsql += " LIMIT " + (page - 1) * pagesize + "," + pagesize + "; ";
                        System.Data.DataSet dataSet = new System.Data.DataSet();
                        MySqlDataAdapter command = new MySqlDataAdapter(strsql, connection);
                        command.Fill(dataSet, "ds");

                        var dalHelperCustom = new DALHelperCustom<TBL_UTS_UserOperation>("TBL_UTS_UserOperation");
                        var UserlogList = dalHelperCustom.DataRowToModels(dataSet.Tables[0]);

                        if (UserlogList != null && UserlogList.Count > 0)
                        {
                            foreach (var item in UserlogList)
                            {
                                TBL_UTS_UserOperation tBL_UTS_UserOperation = new TBL_UTS_UserOperation();
                                tBL_UTS_UserOperation.Ip = item.Ip;
                                tBL_UTS_UserOperation.UserName = item.UserName;
                                tBL_UTS_UserOperation.Browser = item.Browser;
                                tBL_UTS_UserOperation.CreationTime = item.CreationTime;
                                tBL_UTS_UserOperation.Database = item.Database;
                                tBL_UTS_UserOperation.Operation = item.Operation;
                                tBL_UTS_UserOperation.ID = item.ID;
                                tBL_UTS_UserOperation.Location = item.Location;
                                tBL_UTS_UserOperation.Device = item.Device;
                                _UserOperationList.Add(tBL_UTS_UserOperation);
                            }
                        }
                    }
                }
                return _UserOperationList;
            }
            catch (Exception)
            {

                return null;
            }

        }


        public static bool SqlInsertUserLog(TBL_UTS_UserOperation tBL_UTS)
        {
            try
            {
                using (MySqlConnection connection = new MySqlConnection(connectionString))
                {
                    using (MySqlCommand cmd = new MySqlCommand())
                    {
                        connection.Open();

                        cmd.Connection = connection;
                        var strSql = "insert into `TBL_UTS_UserOperation` (CreationTime,UserName,Ip,Device,Browser,Operation,`Database`,`Location`) VALUES(" + "'" + tBL_UTS.CreationTime + "','" + tBL_UTS.UserName + "','" + tBL_UTS.Ip + "','" + tBL_UTS.Device + "','" + tBL_UTS.Browser + "','" + tBL_UTS.Operation + "','" + tBL_UTS.Database + "','" + tBL_UTS.Location + "');";
                        cmd.CommandText = strSql;
                        if (cmd.ExecuteNonQuery() > 0)
                        {
                            return true;
                        }
                    }
                }
            }
            catch (Exception)
            {
                return false;
            }
            return true;
        }
        public static ReturnResults Sqlimportinfo(string code, string dbName, int ProjectID)
        {
            ReturnResults oj = new ReturnResults();
            //2024/5/11修改，查询数据库表名前缀变成从数据库查询
            //List<string> dblist = new List<string>() { "uts_bomei", "uts_inhaos", "uts_johao", "uts_zongqing" };
            //if (!dblist.Contains(dbName))
            //{
            //    oj.Status = 100;
            //    oj.Message = "查询不到合法的dbname";
            //    return oj;
            //}
            try
            {
                TBL_UTS_Manage_DBList dblist = SqlSugarBase.Db.Queryable<TBL_UTS_Manage_DBList>().First(x => x.DatabaseName == dbName);
                List<TBL_ImportInfo> importInfolist = new List<TBL_ImportInfo>();

                if (dblist == null)
                {
                    oj.Status = 100;
                    oj.Message = "查询不到合法的表名" + dbName;
                    return oj;
                }

                using (MySqlConnection connection = new MySqlConnection(connectionSrt))
                {
                    using (MySqlCommand cmd = new MySqlCommand())
                    {
                        connection.Open();

                        cmd.Connection = connection;
                        string dataName = dbName;
                        string strSql = "select * from " + dataName + "_tbl_importinfo where Barcode='" + code + "' and  ProjectID=" + ProjectID + "";
                        System.Data.DataSet dataSet = new System.Data.DataSet();
                        MySqlDataAdapter command = new MySqlDataAdapter(strSql, connection);
                        command.Fill(dataSet, "ds");

                        var dalHelperCustom = new DALHelperCustom<TBL_ImportInfo>("TBL_ImportInfo");
                        var UserlogList = dalHelperCustom.DataRowToModels(dataSet.Tables[0]);
                        if (UserlogList != null && UserlogList.Count != 0)
                        {
                            oj.ColorBox_BarCode = UserlogList[0].ColorBox_BarCode;
                            oj.Message = "已分配";
                            oj.Status = 200;
                            return oj;
                        }
                        else
                        {
                            string seletSql = string.Format("select *  from {0}_tbl_importinfo where ProjectID={1} and (Barcode is NULL or Barcode='') LIMIT 1", dataName, ProjectID);
                            MySqlDataAdapter command1 = new MySqlDataAdapter(seletSql, connection);
                            command1.Fill(dataSet, "ds");
                            var dalHelperCustom1 = new DALHelperCustom<TBL_ImportInfo>("TBL_ImportInfo");
                            var UserlogList1 = dalHelperCustom.DataRowToModels(dataSet.Tables[0]);
                            if (UserlogList1.Count != 0)
                            {
                                string insertsql = string.Format("UPDATE  {0}_tbl_importinfo SET Barcode = '{1}', UpdateTime=now()  WHERE  ID = {2} and ProjectID={3} ;", dataName, code, UserlogList1[0].ID, ProjectID);
                                cmd.CommandText = insertsql;
                                if (cmd.ExecuteNonQuery() > 0)
                                {

                                    oj.ColorBox_BarCode = UserlogList1[0].ColorBox_BarCode;
                                    oj.Message = "成功";
                                    oj.Status = 200;
                                    return oj;
                                }
                            }
                            else
                            {
                                oj.Status = 102;
                                oj.ColorBox_BarCode = "";
                                oj.Message = "Barcode已满";
                                return oj;
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                oj.ColorBox_BarCode = "";
                oj.Message = "报错信息:" + ex.Message;
                oj.Status = 50;
                return oj;
            }
            return null;
        }

        /// <summary>
        /// 绑定Barcode(新)
        /// </summary>
        /// <param name="Pwd"></param>
        /// <param name="Barcode"></param>
        /// <param name="DBName"></param>
        /// <param name="ProjectID"></param>
        /// <returns></returns>

        public static ReturnResult<object> Sqlcolorboxlist(string code, string dbName, int ProjectID, string Mo)
        {
            ReturnResult<object> oj = new ReturnResult<object>();
            Dictionary<string, string> dic = new Dictionary<string, string>();
            //List<string> dblist = new List<string>() { "uts_bomei", "uts_inhaos", "uts_johao", "uts_zongqing" };

            try
            {
                TBL_UTS_Manage_DBList dblist = SqlSugarBase.Db.Queryable<TBL_UTS_Manage_DBList>().First(x => x.DatabaseName == dbName);
                if (dblist==null)
                {
                    oj.Status = 100;
                    oj.Message = "查询不到合法的表名"+dbName;
                    return oj;
                }
                List<string> column = new List<string>();//数据库字段名称
                ///获取数据库字段
                string SQL = $@"SELECT COLUMN_NAME FROM information_schema.COLUMNS
                        WHERE TABLE_SCHEMA = 'uts_db' AND TABLE_NAME = '{dbName}_tbl_importinfo'; ";
                var cul = DbHelperMySqlCustom.NoQuery(SQL).Tables[0];


                for (int I = 0; I < cul.Rows.Count; I++)
                {
                    column.Add(cul.Rows[I][0].ToString().ToUpper());
                }
                using (MySqlConnection connection = new MySqlConnection(connectionSrt))
                {
                    using (MySqlCommand cmd = new MySqlCommand())
                    {
                        connection.Open();
                        cmd.Connection = connection;
                        string dataName = dbName;
                        string strSql = "select * from " + dataName + "_tbl_importinfo where Barcode='" + code + "'";
                        System.Data.DataSet dataSet = new System.Data.DataSet();
                        MySqlDataAdapter command = new MySqlDataAdapter(strSql, connection);
                        command.Fill(dataSet, "ds");
                        var dalHelperCustom = new DALHelperCustom<TBL_ColorBoxList>("TBL_ColorBoxList");
                        var UserlogList = dalHelperCustom.DataRowToModels(dataSet.Tables[0]);
                        if (UserlogList != null && UserlogList.Count != 0)
                        {
                            string strSqlcs = "select * from " + dataName + "_tbl_colorboxlist where Barcode='" + code + "'";
                            System.Data.DataSet dataSets = new System.Data.DataSet();
                            MySqlDataAdapter commands = new MySqlDataAdapter(strSqlcs, connection);
                            commands.Fill(dataSets, "ds");
                            var dalHelperCustoms = new DALHelperCustom<TBL_ColorBoxList>("TBL_ColorBoxList");
                            var UserlogLists = dalHelperCustom.DataRowToModels(dataSets.Tables[0]);
                            if (UserlogLists != null && UserlogLists.Count != 0)
                            {

                                DbHelperMySQL.NoExecuteReader(strSql, dr =>
                                {
                                    while (dr.Read())
                                    {
                                        foreach (var item in column)
                                        {
                                            dic.Add(item, dr[item].ToString());
                                        }
                                        break;
                                    }
                                });
                                oj.Data = dic;
                                oj.Status = 200;
                                oj.Message = "Barcode已分配";
                                return oj;
                            }
                            string seletSql = "";
                            if (Mo != "" && Mo != null)
                            {
                                seletSql = string.Format("select *  from {0}_tbl_colorboxlist where MO='{1}' and (Barcode is NULL or Barcode='') and Binding_Status=0  and ProjectID={2} LIMIT 1", dataName, Mo, ProjectID);
                            }
                            else
                            {
                                seletSql = string.Format("select *  from {0}_tbl_colorboxlist where  Barcode is NULL or Barcode='' and Binding_Status=0  and  ProjectID={1}  LIMIT 1", dataName, ProjectID);
                            }
                            System.Data.DataSet dataSetes = new System.Data.DataSet();
                            MySqlDataAdapter command1 = new MySqlDataAdapter(seletSql, connection);
                            command1.Fill(dataSetes, "ds");
                            var UserlogList1 = dalHelperCustom.DataRowToModels(dataSetes.Tables[0]);
                            //bool isok = false;
                            //var aa = dataSetes.Tables[0].Columns;

                            //foreach (var item in aa)
                            //{
                            //    Console.WriteLine(item.ToString());
                            //    string e = item.ToString();
                            //    if (e == "DeviceName" || e == "DevicePSK")
                            //    {
                            //        string sql = $"alter table " + dataName + "_tbl_importinfo Add column " + e + " varchar(255) not null default '' comment '可以为空值';";
                            //        cmd.CommandText = sql;
                            //    }
                            //}
                            if (UserlogList1.Count != 0)
                            {
                                string insertsql = string.Format("UPDATE  {0}_tbl_colorboxlist SET  Barcode = '{1}', Binding_DateTime=now(), Binding_Status=1  WHERE  ID = {2}  ;", dataName, code, UserlogList1[0].ID);
                                cmd.CommandText = insertsql;
                                if (cmd.ExecuteNonQuery() > 0)
                                {
                                    string sety = UserlogList1[0].ColorBox_BarCode;
                                    string barcodesql = string.Format("UPDATE  {0}_tbl_importinfo SET  ColorBox_BarCode= '{1}',  UpdateTime=now(), Mo='{2}' WHERE Barcode = '{3}'  ;", dataName, UserlogList1[0].ColorBox_BarCode, UserlogList1[0].Mo, code);
                                    cmd.CommandText = barcodesql;
                                    if (cmd.ExecuteNonQuery() > 0)
                                    {
                                        string returnsql = "select * from " + dataName + "_tbl_importinfo where ColorBox_BarCode='" + UserlogList1[0].ColorBox_BarCode + "'";
                                        DbHelperMySQL.NoExecuteReader(returnsql, dr =>
                                        {
                                            while (dr.Read())
                                            {
                                                foreach (var item in column)
                                                {
                                                    dic.Add(item, dr[item].ToString());
                                                }
                                                break;
                                            }
                                        });


                                        oj.Data = dic;
                                        oj.Message = "成功";
                                        oj.Status = 200;
                                        return oj;
                                    }
                                }
                            }
                            else
                            {
                                oj.Message = "无彩盒码使用";
                                oj.Status = 50;
                                return oj;
                            }
                        }
                        else
                        {
                            oj.Message = "未在importinfo找到该BarCode";
                            oj.Status = 50;
                            return oj;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                oj.Message = "报错信息:" + ex.Message;
                oj.Status = 50;
                return oj;
            }
            return null;
        }


        /// <summary>
        /// 绑定Barcode(最新)
        /// </summary>
        /// <param name="Pwd">密令</param>
        /// <param name="Barcode"></param>
        /// <param name="DBName">数据库名称</param>
        /// <param name="Mo"></param>
        /// <param name="ProjectID">机型</param>
        /// <returns></returns>

        public static ReturnResult<object> newestSqlcolorboxlist(string code, string dbName, int ProjectID, string Mo)
        {
            ReturnResult<object> oj = new ReturnResult<object>();
            Dictionary<string, string> dic = new Dictionary<string, string>();
            //List<string> dblist = new List<string>() { "uts_bomei", "uts_inhaos", "uts_johao", "uts_zongqing" };
          
            try
            {
                TBL_UTS_Manage_DBList dblist = SqlSugarBase.Db.Queryable<TBL_UTS_Manage_DBList>().First(x => x.DatabaseName == dbName);
                if (dblist == null)
                {
                    oj.Status = 100;
                    oj.Message = "查询不到合法的表名" + dbName;
                    return oj;
                }
                List<string> column = new List<string>();//数据库字段名称
                ///获取数据库字段
                string SQL = $@"SELECT COLUMN_NAME FROM information_schema.COLUMNS
                        WHERE TABLE_SCHEMA = 'uts_db' AND TABLE_NAME = '{dbName}_tbl_importinfo'; ";
                var cul = DbHelperMySqlCustom.NoQuery(SQL).Tables[0];


                for (int I = 0; I < cul.Rows.Count; I++)
                {
                    column.Add(cul.Rows[I][0].ToString().ToUpper());
                }
                using (MySqlConnection connection = new MySqlConnection(connectionSrt))
                {
                    using (MySqlCommand cmd = new MySqlCommand())
                    {
                        connection.Open();
                        cmd.Connection = connection;
                        string dataName = dbName;
                        string strSql = "select * from " + dataName + "_tbl_importinfo where Barcode='" + code + "'";
                        System.Data.DataSet dataSet = new System.Data.DataSet();
                        MySqlDataAdapter command = new MySqlDataAdapter(strSql, connection);
                        command.Fill(dataSet, "ds");
                        var dalHelperCustom = new DALHelperCustom<TBL_ColorBoxList>("TBL_ColorBoxList");
                        var UserlogList = dalHelperCustom.DataRowToModels(dataSet.Tables[0]);
                        if (UserlogList != null && UserlogList.Count != 0)
                        {
                            string strSqlcs = "select * from " + dataName + "_tbl_colorboxlist where Barcode='" + code + "'";
                            System.Data.DataSet dataSets = new System.Data.DataSet();
                            MySqlDataAdapter commands = new MySqlDataAdapter(strSqlcs, connection);
                            commands.Fill(dataSets, "ds");
                            var dalHelperCustoms = new DALHelperCustom<TBL_ColorBoxList>("TBL_ColorBoxList");
                            var UserlogLists = dalHelperCustom.DataRowToModels(dataSets.Tables[0]);
                            if (UserlogLists != null && UserlogLists.Count != 0)
                            {

                                DbHelperMySQL.NoExecuteReader(strSql, dr =>
                                {
                                    while (dr.Read())
                                    {
                                        foreach (var item in column)
                                        {
                                            dic.Add(item, dr[item].ToString());
                                        }
                                        break;
                                    }
                                });
                                oj.Data = dic;
                                oj.Status = 200;
                                oj.Message = "Barcode已分配";
                                return oj;
                            }
                            string seletSql = "";
                            if (Mo != "" && Mo != null)
                            {
                                seletSql = string.Format("select *  from {0}_tbl_colorboxlist where MO='{1}' and (Barcode is NULL or Barcode='') and Binding_Status=0  and ProjectID={2} LIMIT 1", dataName, Mo, ProjectID);
                            }
                            else
                            {
                                seletSql = string.Format("select *  from {0}_tbl_colorboxlist where  Barcode is NULL or Barcode='' and Binding_Status=0  and  ProjectID={1}  LIMIT 1", dataName, ProjectID);
                            }
                            System.Data.DataSet dataSetes = new System.Data.DataSet();
                            MySqlDataAdapter command1 = new MySqlDataAdapter(seletSql, connection);
                            command1.Fill(dataSetes, "ds");
                            var UserlogList1 = dalHelperCustom.DataRowToModels(dataSetes.Tables[0]);
                            string strsql = string.Format("select *  from {0}_tbl_importinfo  LIMIT 1", dataName);
                            System.Data.DataSet dataSetes1 = new System.Data.DataSet();
                            MySqlDataAdapter command2 = new MySqlDataAdapter(strsql, connection);
                            command2.Fill(dataSetes1, "ds");



                            List<string> list = new List<string>();
                            List<string> list1 = new List<string>();
                          
                            //初始表格所有列
                            var aa = dataSetes.Tables[0].Columns;
                            //转移到的表格所有列
                            var bb = dataSetes1.Tables[0].Columns;
                            List<string> bbNames = new List<string>();
                            //初始表格里的一行数据
                            var asa = dataSetes.Tables[0].Rows[0].ItemArray;
                            for (int i = 10; i < asa.Length; i++)
                            {
                                list.Add(asa[i].ToString());
                            }
                         
                            for (int i = 0; i < bb.Count; i++)
                            {
                                bbNames.Add(bb[i].ColumnName);

                            }
                            if (aa.Count > 10)
                            {
                                for (int i = 10; i < aa.Count; i++)//起始表10--结束
                                {
                                    list1.Add(aa[i].ToString());
                               
                                    //aa[i]  // DataColumn
                                    if (bbNames.Contains(aa[i].ColumnName))
                                    {
                                        continue;
                                    }
                                    else
                                    {

                                        //我要追加 使用aa[i]列的数据   到bb的末尾
                                        string sql = $"alter table " + dataName + "_tbl_importinfo Add column " + aa[i].ColumnName + " varchar(255) not null default '' comment '可以为空值';";
                                        cmd.CommandText = sql;
                                        cmd.ExecuteNonQuery();
                                    }
                                }
                            }
                            //拼接sql语句
                            string newrows = "";
                            for (int i = 0; i < list.Count; i++)
                            {
                               
                                string colname = list1[i];
                                string colval = list[i];
                                newrows += colname + "='"+colval+"'" + ",";
                            }
                            if (UserlogList1.Count != 0)
                            {
                                string insertsql = string.Format("UPDATE  {0}_tbl_colorboxlist SET  Barcode = '{1}', Binding_DateTime=now(), Binding_Status=1  WHERE  ID = {2}  ;", dataName, code, UserlogList1[0].ID);
                                cmd.CommandText = insertsql;
                                if (cmd.ExecuteNonQuery() > 0)
                                {
                                    string sety = UserlogList1[0].ColorBox_BarCode;
                                    string barcodesql = string.Format("UPDATE  {0}_tbl_importinfo SET  ColorBox_BarCode= '{1}',"
                                        + newrows
                                        + "  UpdateTime=now(), Mo='{2}' WHERE Barcode = '{3}'  ;",
                                        dataName,
                                        UserlogList1[0].ColorBox_BarCode,
                                        UserlogList1[0].Mo,
                                        code);
                                    cmd.CommandText = barcodesql;
                                    if (cmd.ExecuteNonQuery() > 0)
                                    {
                                        string returnsql = "select * from " + dataName + "_tbl_importinfo where ColorBox_BarCode='" + UserlogList1[0].ColorBox_BarCode + "'";
                                        DbHelperMySQL.NoExecuteReader(returnsql, dr =>
                                        {
                                            while (dr.Read())
                                            {
                                                foreach (var item in column)
                                                {
                                                    dic.Add(item, dr[item].ToString());
                                                }
                                                break;
                                            }
                                        });


                                        oj.Data = dic;
                                        oj.Message = "成功";
                                        oj.Status = 200;
                                        return oj;
                                    }
                                }
                            }
                            else
                            {
                                oj.Message = "无彩盒码使用";
                                oj.Status = 50;
                                return oj;
                            }
                        }
                        else
                        {
                            oj.Message = "未在importinfo找到该BarCode";
                            oj.Status = 50;
                            return oj;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                oj.Message = "报错信息:" + ex.Message;
                oj.Status = 50;
                return oj;
            }
            return null;
        }



        public static ReturnResult<object> APiNewcolorboxlist(string code, string dbName, int ProjectID, string Mo, string specifyField, int sortid)
        {
            ReturnResult<object> oj = new ReturnResult<object>();
            Dictionary<string, string> dic = new Dictionary<string, string>();
            //List<string> dblist = new List<string>() { "uts_bomei", "uts_inhaos", "uts_johao", "uts_zongqing" };

            try
            {
                TBL_UTS_Manage_DBList dblist = SqlSugarBase.Db.Queryable<TBL_UTS_Manage_DBList>().First(x => x.DatabaseName == dbName);
                if (dblist == null)
                {
                    oj.Status = 100;
                    oj.Message = "查询不到合法的表名" + dbName;
                    return oj;
                }
                List<string> column = new List<string>();//数据库字段名称
                ///获取数据库字段
                string SQL = $@"SELECT COLUMN_NAME FROM information_schema.COLUMNS
                        WHERE TABLE_SCHEMA = 'uts_db' AND TABLE_NAME = '{dbName}_tbl_importinfo'; ";
                var cul = DbHelperMySqlCustom.NoQuery(SQL).Tables[0];


                for (int I = 0; I < cul.Rows.Count; I++)
                {
                    column.Add(cul.Rows[I][0].ToString().ToUpper());
                }
                using (MySqlConnection connection = new MySqlConnection(connectionSrt))
                {
                    using (MySqlCommand cmd = new MySqlCommand())
                    {
                        connection.Open();
                        cmd.Connection = connection;
                        string dataName = dbName;
                        string strSql = "select * from " + dataName + "_tbl_importinfo where Barcode='" + code + "'";
                        System.Data.DataSet dataSet = new System.Data.DataSet();
                        MySqlDataAdapter command = new MySqlDataAdapter(strSql, connection);
                        command.Fill(dataSet, "ds");
                        var dalHelperCustom = new DALHelperCustom<TBL_ColorBoxList>("TBL_ColorBoxList");
                        var UserlogList = dalHelperCustom.DataRowToModels(dataSet.Tables[0]);
                        if (UserlogList != null && UserlogList.Count != 0)
                        {
                            string strSqlcs = "select * from " + dataName + "_tbl_colorboxlist where Barcode='" + code + "'";
                            System.Data.DataSet dataSets = new System.Data.DataSet();
                            MySqlDataAdapter commands = new MySqlDataAdapter(strSqlcs, connection);
                            commands.Fill(dataSets, "ds");
                            var dalHelperCustoms = new DALHelperCustom<TBL_ColorBoxList>("TBL_ColorBoxList");
                            var UserlogLists = dalHelperCustom.DataRowToModels(dataSets.Tables[0]);
                            if (UserlogLists != null && UserlogLists.Count != 0)
                            {

                                DbHelperMySQL.NoExecuteReader(strSql, dr =>
                                {
                                    while (dr.Read())
                                    {
                                        foreach (var item in column)
                                        {
                                            dic.Add(item, dr[item].ToString());
                                        }
                                        break;
                                    }
                                });
                                oj.Data = dic;
                                oj.Status = 200;
                                oj.Message = "Barcode已分配";
                                return oj;
                            }
                            string seletSql = "";
                            if (Mo != "" && Mo != null)
                            {
                                if (sortid == 1)
                                {
                                    seletSql = string.Format("select *  from {0}_tbl_colorboxlist where MO='{1}' and (Barcode is NULL or Barcode='') and Binding_Status=0  and ProjectID={2}   ORDER BY {3} LIMIT 1 ", dataName, Mo, ProjectID, specifyField);
                                }
                                else
                                {
                                    seletSql = string.Format("select *  from {0}_tbl_colorboxlist where MO='{1}' and (Barcode is NULL or Barcode='') and Binding_Status=0  and ProjectID={2}   ORDER BY {3} desc LIMIT 1 ", dataName, Mo, ProjectID, specifyField);
                                }
                                //seletSql = string.Format("select *  from {0}_tbl_colorboxlist where MO='{1}' and (Barcode is NULL or Barcode='') and Binding_Status=0  and ProjectID={2} LIMIT 1", dataName, Mo, ProjectID);
                            }
                            else
                            {
                                if (sortid == 1)
                                {
                                    seletSql = string.Format("select * from {0}_tbl_colorboxlist where  Barcode is NULL or Barcode = '' and Binding_Status = 0  and ProjectID = {1}  ORDER BY {2}  LIMIT 1", dataName, ProjectID, specifyField);
                                }
                                else
                                {
                                    seletSql = string.Format("select * from {0}_tbl_colorboxlist where  Barcode is NULL or Barcode = '' and Binding_Status = 0  and ProjectID = {1}  ORDER BY {2} desc   LIMIT 1", dataName, ProjectID, specifyField);
                                }
                                //seletSql = string.Format("select *  from {0}_tbl_colorboxlist where  Barcode is NULL or Barcode='' and Binding_Status=0  and  ProjectID={1}  LIMIT 1", dataName, ProjectID);
                            }
                            System.Data.DataSet dataSetes = new System.Data.DataSet();
                            MySqlDataAdapter command1 = new MySqlDataAdapter(seletSql, connection);
                            command1.Fill(dataSetes, "ds");
                            var UserlogList1 = dalHelperCustom.DataRowToModels(dataSetes.Tables[0]);
                            string strsql = string.Format("select *  from {0}_tbl_importinfo  LIMIT 1", dataName);
                            System.Data.DataSet dataSetes1 = new System.Data.DataSet();
                            MySqlDataAdapter command2 = new MySqlDataAdapter(strsql, connection);
                            command2.Fill(dataSetes1, "ds");



                            List<string> list = new List<string>();
                            List<string> list1 = new List<string>();

                            //初始表格所有列
                            var aa = dataSetes.Tables[0].Columns;
                            //转移到的表格所有列
                            var bb = dataSetes1.Tables[0].Columns;
                            List<string> bbNames = new List<string>();
                            //初始表格里的一行数据
                            var asa = dataSetes.Tables[0].Rows[0].ItemArray;
                            for (int i = 10; i < asa.Length; i++)
                            {
                                list.Add(asa[i].ToString());
                            }

                            for (int i = 0; i < bb.Count; i++)
                            {
                                bbNames.Add(bb[i].ColumnName);

                            }
                            if (aa.Count > 10)
                            {
                                for (int i = 10; i < aa.Count; i++)//起始表10--结束
                                {
                                    list1.Add(aa[i].ToString());

                                    //aa[i]  // DataColumn
                                    if (bbNames.Contains(aa[i].ColumnName))
                                    {
                                        continue;
                                    }
                                    else
                                    {

                                        //我要追加 使用aa[i]列的数据   到bb的末尾
                                        string sql = $"alter table " + dataName + "_tbl_importinfo Add column " + aa[i].ColumnName + " varchar(255) not null default '' comment '可以为空值';";
                                        cmd.CommandText = sql;
                                        cmd.ExecuteNonQuery();
                                    }
                                }
                            }
                            //拼接sql语句
                            string newrows = "";
                            for (int i = 0; i < list.Count; i++)
                            {

                                string colname = list1[i];
                                string colval = list[i];
                                newrows += colname + "='" + colval + "'" + ",";
                            }
                            if (UserlogList1.Count != 0)
                            {
                                string insertsql = string.Format("UPDATE  {0}_tbl_colorboxlist SET  Barcode = '{1}', Binding_DateTime=now(), Binding_Status=1  WHERE  ID = {2}  ;", dataName, code, UserlogList1[0].ID);
                                cmd.CommandText = insertsql;
                                if (cmd.ExecuteNonQuery() > 0)
                                {
                                    string sety = UserlogList1[0].ColorBox_BarCode;
                                    string barcodesql = string.Format("UPDATE  {0}_tbl_importinfo SET  ColorBox_BarCode= '{1}',"
                                        + newrows
                                        + "  UpdateTime=now(), Mo='{2}' WHERE Barcode = '{3}'  ;",
                                        dataName,
                                        UserlogList1[0].ColorBox_BarCode,
                                        UserlogList1[0].Mo,
                                        code);
                                    cmd.CommandText = barcodesql;
                                    if (cmd.ExecuteNonQuery() > 0)
                                    {
                                        string returnsql = "select * from " + dataName + "_tbl_importinfo where ColorBox_BarCode='" + UserlogList1[0].ColorBox_BarCode + "'";
                                        DbHelperMySQL.NoExecuteReader(returnsql, dr =>
                                        {
                                            while (dr.Read())
                                            {
                                                foreach (var item in column)
                                                {
                                                    dic.Add(item, dr[item].ToString());
                                                }
                                                break;
                                            }
                                        });


                                        oj.Data = dic;
                                        oj.Message = "成功";
                                        oj.Status = 200;
                                        return oj;
                                    }
                                }
                            }
                            else
                            {
                                oj.Message = "无彩盒码使用";
                                oj.Status = 50;
                                return oj;
                            }
                        }
                        else
                        {
                            oj.Message = "未在importinfo找到该BarCode";
                            oj.Status = 50;
                            return oj;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                oj.Message = "报错信息:" + ex.Message;
                oj.Status = 50;
                return oj;
            }
            return null;

            #region

            //ReturnResult<object> oj = new ReturnResult<object>();
            //Dictionary<string, string> dic = new Dictionary<string, string>();
            ////List<string> dblist = new List<string>() { "uts_bomei", "uts_inhaos", "uts_johao", "uts_zongqing" };

            //try
            //{
            //    TBL_UTS_Manage_DBList dblist = SqlSugarBase.Db.Queryable<TBL_UTS_Manage_DBList>().First(x => x.DatabaseName == dbName);
            //    if (dblist == null)
            //    {
            //        oj.Status = 100;
            //        oj.Message = "查询不到合法的表名" + dbName;
            //        return oj;
            //    }
            //    List<string> column = new List<string>();//数据库字段名称
            //    ///获取数据库字段
            //    string SQL = $@"SELECT COLUMN_NAME FROM information_schema.COLUMNS
            //            WHERE TABLE_SCHEMA = 'uts_db' AND TABLE_NAME = '{dbName}_tbl_importinfo'; ";
            //    var cul = DbHelperMySqlCustom.NoQuery(SQL).Tables[0];


            //    for (int I = 0; I < cul.Rows.Count; I++)
            //    {
            //        column.Add(cul.Rows[I][0].ToString().ToUpper());
            //    }
            //    using (MySqlConnection connection = new MySqlConnection(connectionSrt))
            //    {
            //        using (MySqlCommand cmd = new MySqlCommand())
            //        {
            //            connection.Open();
            //            cmd.Connection = connection;
            //            string dataName = dbName;
            //            string strSql = "select * from " + dataName + "_tbl_importinfo where Barcode='" + code + "'";
            //            System.Data.DataSet dataSet = new System.Data.DataSet();
            //            MySqlDataAdapter command = new MySqlDataAdapter(strSql, connection);
            //            command.Fill(dataSet, "ds");
            //            var dalHelperCustom = new DALHelperCustom<TBL_ColorBoxList>("TBL_ColorBoxList");
            //            var UserlogList = dalHelperCustom.DataRowToModels(dataSet.Tables[0]);
            //            if (UserlogList != null && UserlogList.Count != 0)
            //            {
            //                string strSqlcs = "select * from " + dataName + "_tbl_colorboxlist where Barcode='" + code + "'";
            //                System.Data.DataSet dataSets = new System.Data.DataSet();
            //                MySqlDataAdapter commands = new MySqlDataAdapter(strSqlcs, connection);
            //                commands.Fill(dataSets, "ds");
            //                var dalHelperCustoms = new DALHelperCustom<TBL_ColorBoxList>("TBL_ColorBoxList");
            //                var UserlogLists = dalHelperCustom.DataRowToModels(dataSets.Tables[0]);
            //                if (UserlogLists != null && UserlogLists.Count != 0)
            //                {

            //                    DbHelperMySQL.NoExecuteReader(strSql, dr =>
            //                    {
            //                        while (dr.Read())
            //                        {
            //                            foreach (var item in column)
            //                            {
            //                                dic.Add(item, dr[item].ToString());
            //                            }
            //                            break;
            //                        }
            //                    });
            //                    oj.Data = dic;
            //                    oj.Status = 200;
            //                    oj.Message = "Barcode已分配";
            //                    return oj;
            //                }






            //                string seletSql = "";
            //                if (Mo != "" && Mo != null)
            //                {
            //                    if (sortid == 1)
            //                    {
            //                        seletSql = string.Format("select *  from {0}_tbl_colorboxlist where MO='{1}' and (Barcode is NULL or Barcode='') and Binding_Status=0  and ProjectID={2}   ORDER BY {3} LIMIT 1 ", dataName, Mo, ProjectID, specifyField);
            //                    }
            //                    else
            //                    {
            //                        seletSql = string.Format("select *  from {0}_tbl_colorboxlist where MO='{1}' and (Barcode is NULL or Barcode='') and Binding_Status=0  and ProjectID={2}   ORDER BY {3} desc LIMIT 1 ", dataName, Mo, ProjectID, specifyField);
            //                    }


            //                    //seletSql = string.Format("select *  from {0}_tbl_colorboxlist where MO='{1}' and (Barcode is NULL or Barcode='') and Binding_Status=0  and ProjectID={2} LIMIT 1", dataName, Mo, ProjectID);
            //                }
            //                else
            //                {
            //                    if (sortid==1)
            //                    {
            //                        seletSql = string.Format("select * from {0}_tbl_colorboxlist where  Barcode is NULL or Barcode = '' and Binding_Status = 0  and ProjectID = {1}  ORDER BY {2}  LIMIT 1", dataName, ProjectID, specifyField);
            //                    }
            //                    else
            //                    {
            //                        seletSql = string.Format("select * from {0}_tbl_colorboxlist where  Barcode is NULL or Barcode = '' and Binding_Status = 0  and ProjectID = {1}  ORDER BY {2} desc   LIMIT 1", dataName, ProjectID, specifyField);
            //                    }



            //                    //seletSql = string.Format("select *  from {0}_tbl_colorboxlist where  Barcode is NULL or Barcode='' and Binding_Status=0  and  ProjectID={1}  LIMIT 1", dataName, ProjectID);
            //                }
            //                System.Data.DataSet dataSetes = new System.Data.DataSet();
            //                MySqlDataAdapter command1 = new MySqlDataAdapter(seletSql, connection);
            //                command1.Fill(dataSetes, "ds");
            //                var UserlogList1 = dalHelperCustom.DataRowToModels(dataSetes.Tables[0]);
            //                //bool isok = false;
            //                //var aa = dataSetes.Tables[0].Columns;

            //                //foreach (var item in aa)
            //                //{
            //                //    Console.WriteLine(item.ToString());
            //                //    string e = item.ToString();
            //                //    if (e == "DeviceName" || e == "DevicePSK")
            //                //    {
            //                //        string sql = $"alter table " + dataName + "_tbl_importinfo Add column " + e + " varchar(255) not null default '' comment '可以为空值';";
            //                //        cmd.CommandText = sql;
            //                //    }
            //                //}
            //                if (UserlogList1.Count != 0)
            //                {
            //                    string insertsql = string.Format("UPDATE  {0}_tbl_colorboxlist SET  Barcode = '{1}', Binding_DateTime=now(), Binding_Status=1  WHERE  ID = {2}  ;", dataName, code, UserlogList1[0].ID);
            //                    cmd.CommandText = insertsql;
            //                    if (cmd.ExecuteNonQuery() > 0)
            //                    {
            //                        string sety = UserlogList1[0].ColorBox_BarCode;
            //                        string barcodesql = string.Format("UPDATE  {0}_tbl_importinfo SET  ColorBox_BarCode= '{1}',  UpdateTime=now(), Mo='{2}' WHERE Barcode = '{3}'  ;", dataName, UserlogList1[0].ColorBox_BarCode, UserlogList1[0].Mo, code);
            //                        cmd.CommandText = barcodesql;
            //                        if (cmd.ExecuteNonQuery() > 0)
            //                        {
            //                            string returnsql = "select * from " + dataName + "_tbl_importinfo where ColorBox_BarCode='" + UserlogList1[0].ColorBox_BarCode + "'";
            //                            DbHelperMySQL.NoExecuteReader(returnsql, dr =>
            //                            {
            //                                while (dr.Read())
            //                                {
            //                                    foreach (var item in column)
            //                                    {
            //                                        dic.Add(item, dr[item].ToString());
            //                                    }
            //                                    break;
            //                                }
            //                            });


            //                            oj.Data = dic;
            //                            oj.Message = "成功";
            //                            oj.Status = 200;
            //                            return oj;
            //                        }
            //                    }
            //                }
            //                else
            //                {
            //                    oj.Message = "无彩盒码使用";
            //                    oj.Status = 50;
            //                    return oj;
            //                }
            //            }
            //            else
            //            {
            //                oj.Message = "未在importinfo找到该BarCode";
            //                oj.Status = 50;
            //                return oj;
            //            }
            //        }
            //    }
            //}
            //catch (Exception ex)
            //{
            //    oj.Message = "报错信息:" + ex.Message;
            //    oj.Status = 50;
            //    return oj;
            //}
            //return null;
            #endregion
        }

        public static ReturnResult<object> SelectDatSn(string dbname, string Key, int ProjectID)
        {
            //List<string> dblist = new List<string>() { "uts_bomei", "uts_inhaos", "uts_johao", "uts_zongqing" };//数据库名称
            ReturnResult<object> result = new ReturnResult<object>();
            try
            {
                //2024/5/11修改，查询数据库表名前缀变成从数据库查询

                TBL_UTS_Manage_DBList dblist = SqlSugarBase.Db.Queryable<TBL_UTS_Manage_DBList>().First(x => x.DatabaseName == dbname);
                List<TBL_ImportInfo> importInfolist = new List<TBL_ImportInfo>();

                //if (dblist == null)
                //{
                //    oj.Status = 100;
                //    oj.Message = "查询不到合法的表名" + dbName;
                //    return oj;
                //}

                if (dblist == null)
                {
                    result.Status = 100;
                    result.Message = "查询不到合法的表名" + dbname;
                    return result;
                }
                List<string> column = new List<string>();//数据库字段名称
                ///获取数据库字段
                string SQL = $@"SELECT COLUMN_NAME FROM information_schema.COLUMNS
                        WHERE TABLE_SCHEMA = 'uts_db' AND TABLE_NAME = '{dbname}_tbl_importinfo'; ";
                var cul = DbHelperMySqlCustom.NoQuery(SQL).Tables[0];


                for (int I = 0; I < cul.Rows.Count; I++)
                {
                    column.Add(cul.Rows[I][0].ToString().ToUpper());
                }
                //查询语句
                string sql = $"select *  from {dbname}_tbl_importinfo  where ProjectID=" + ProjectID + "  and (Barcode='" + Key + "' or ColorBox_BarCode='" + Key + "' or OutBoxCode='" + Key + "')";
                //foreach (var item in column)
                //{
                //    if (item != "IMPORTDATETIME" && item != "UPDATETIME" && item != "PROJECTID" && item != "S1"
                //        && item != "S2" && item != "S3" && item != "S4" && item != "S5" && item != "S6" && item != "S6"
                //        && item != "S7" && item != "S8" && item != "S9" && item != "S10" && item != "S11" && item != "S12"
                //        && item != "RESULT1" && item != "RESULT2" && item != "RESULT3" && item != "RESULT4" && item != "RESULT5" && item != "RESULT6" && item != "RESULT7" && item != "RESULT8" && item != "RESULT9" && item != "RESULT10" && item != "RESULT11" && item != "RESULT12"&& item!= "SHIPPINGDATETIME")
                //    {
                //        sql += item + "='" + Key + "' or ";
                //    }
                //}
                //sql = sql.Remove(sql.Length - 3) + ")";


                //List<Dictionary<string, string>> diclist = new List<Dictionary<string, string>>();
                Dictionary<string, string> dic = new Dictionary<string, string>();
                DbHelperMySQL.NoExecuteReader(sql, dr =>
                {
                    while (dr.Read())
                    {
                        foreach (var item in column)
                        {
                            dic.Add(item, dr[item].ToString());
                        }
                        //diclist.Add(dic);
                        break;
                    }
                });
                if (dic.Count != 0)
                {
                    result.Data = dic;
                    result.Message = "查询成功";
                    result.Status = 200;
                }
                else
                {
                    result.Status = 404;
                    result.Message = "未找到未找到这条记录";
                }
            }
            catch (Exception ex)
            {
                result.Message = ex.Message;
            }
            return result;
        }

        public static ReturnResult<object> SelectDatSnValue(string dbname, string Key, string value, int ProjectID)
        {
            string sql = "";
            //List<string> dblist = new List<string>() { "uts_bomei", "uts_inhaos", "uts_johao", "uts_zongqing" };//数据库名称
            ReturnResult<object> result = new ReturnResult<object>();
            List<TBL_ImportInfo> importInfolist = new List<TBL_ImportInfo>();
            try
            {
                TBL_UTS_Manage_DBList dblist = SqlSugarBase.Db.Queryable<TBL_UTS_Manage_DBList>().First(x => x.DatabaseName == dbname);

                if (dblist == null)
                {
                    result.Status = 100;
                    result.Message = "查询不到合法的表名"+dbname;
                    return result;
                }
                List<string> column = new List<string>();//数据库字段名称
                ///获取数据库字段
                string SQL = $@"SELECT COLUMN_NAME FROM information_schema.COLUMNS
                        WHERE TABLE_SCHEMA = 'uts_db' AND TABLE_NAME = '{dbname}_tbl_importinfo'; ";
                var cul = DbHelperMySqlCustom.NoQuery(SQL).Tables[0];


                for (int I = 0; I < cul.Rows.Count; I++)
                {
                    column.Add(cul.Rows[I][0].ToString().ToUpper());
                }
                //查询语句
                if (ProjectID != 0)
                {
                    sql = $"select * from {dbname}_tbl_importinfo where ProjectID={ProjectID} and {Key} ='{value}'";

                }
                else
                {
                    sql = $"select * from {dbname}_tbl_importinfo where {Key} ='{value}'";

                }
                List<Dictionary<string, string>> dicList = new List<Dictionary<string, string>>(); // 创建一个字典列表来存储多条数据

                DbHelperMySQL.NoExecuteReader(sql, dr =>
                {
                    while (dr.Read())
                    {
                        Dictionary<string, string> dic = new Dictionary<string, string>(); // 在循环内部创建一个新的字典来存储每一行的数据
                        foreach (var item in column)
                        {
                            dic.Add(item, dr[item].ToString());
                        }
                        dicList.Add(dic); // 将每一行的字典添加到字典列表中
                    }
                });
                if (dicList.Count != 0)
                {
                    result.Data = dicList;
                    result.Message = "查询成功";
                    result.Status = 200;
                }
                else
                {
                    result.Status = 404;
                    result.Message = "未找到未找到这条记录";
                }
            }
            catch (Exception ex)
            {
                result.Message = ex.Message;
            }
            return result;
        }


        public static ReturnResult<object> SelectDatSnAppoint(string dbname, string Key, string value, int ProjectID)
        {
            //List<string> dblist = new List<string>() { "uts_bomei", "uts_inhaos", "uts_johao", "uts_zongqing" };//数据库名称
            ReturnResult<object> result = new ReturnResult<object>();
            List<TBL_ImportInfo> importInfolist = new List<TBL_ImportInfo>();
            try
            {
                TBL_UTS_Manage_DBList dblist = SqlSugarBase.Db.Queryable<TBL_UTS_Manage_DBList>().First(x => x.DatabaseName == dbname);
                if (dblist == null)
                {
                    result.Status = 100;
                    result.Message = "查询不到合法的表名"+dbname;
                    return result;
                }
                List<string> column = new List<string>();//数据库字段名称
                ///获取数据库字段
                string SQL = $@"SELECT COLUMN_NAME FROM information_schema.COLUMNS
                        WHERE TABLE_SCHEMA = 'uts_db' AND TABLE_NAME = '{dbname}_tbl_importinfo'; ";
                var cul = DbHelperMySqlCustom.NoQuery(SQL).Tables[0];


                for (int I = 0; I < cul.Rows.Count; I++)
                {
                    column.Add(cul.Rows[I][0].ToString().ToUpper());
                }
                //查询语句
                string sql = $"select *  from {dbname}_tbl_importinfo  where ProjectID=" + ProjectID + "  and  " + Key + "=" + "'" + value + "'";
                //foreach (var item in column)
                //{
                //    if (item != "IMPORTDATETIME" && item != "UPDATETIME" && item != "PROJECTID" && item != "S1"
                //        && item != "S2" && item != "S3" && item != "S4" && item != "S5" && item != "S6" && item != "S6"
                //        && item != "S7" && item != "S8" && item != "S9" && item != "S10" && item != "S11" && item != "S12"
                //        && item != "RESULT1" && item != "RESULT2" && item != "RESULT3" && item != "RESULT4" && item != "RESULT5" && item != "RESULT6" && item != "RESULT7" && item != "RESULT8" && item != "RESULT9" && item != "RESULT10" && item != "RESULT11" && item != "RESULT12"&& item!= "SHIPPINGDATETIME")
                //    {
                //        sql += item + "='" + Key + "' or ";
                //    }
                //}
                //sql = sql.Remove(sql.Length - 3) + ")";


                //List<Dictionary<string, string>> diclist = new List<Dictionary<string, string>>();
                Dictionary<string, string> dic = new Dictionary<string, string>();
                DbHelperMySQL.NoExecuteReader(sql, dr =>
                {
                    while (dr.Read())
                    {
                        foreach (var item in column)
                        {
                            dic.Add(item, dr[item].ToString());
                        }
                        //diclist.Add(dic);
                        break;
                    }
                });
                if (dic.Count != 0)
                {
                    result.Data = dic;
                    result.Message = "查询成功";
                    result.Status = 200;
                }
                else
                {
                    result.Status = 404;
                    result.Message = "未找到未找到这条记录";
                }
            }
            catch (Exception ex)
            {
                result.Message = ex.Message;
            }
            return result;
        }

        public static ReturnResult<object> SelectDatSnList(string dbname, string Key)
        {
            //List<string> dblist = new List<string>() { "uts_bomei", "uts_inhaos", "uts_johao", "uts_zongqing" };//数据库名称
            ReturnResult<object> result = new ReturnResult<object>();
            List<TBL_ImportInfo> importInfolist = new List<TBL_ImportInfo>();
            try
            {
                TBL_UTS_Manage_DBList dblist = SqlSugarBase.Db.Queryable<TBL_UTS_Manage_DBList>().First(x => x.DatabaseName == dbname);
                if (dblist==null)
                {
                    result.Status = 100;
                    result.Message = "查询不到合法的表名"+dbname;
                    return result;
                }
                List<string> column = new List<string>();//数据库字段名称
                ///获取数据库字段
                string SQL = $@"SELECT COLUMN_NAME FROM information_schema.COLUMNS
                        WHERE TABLE_SCHEMA = 'uts_db' AND TABLE_NAME = '{dbname}_tbl_importinfo'; ";
                var cul = DbHelperMySqlCustom.NoQuery(SQL).Tables[0];


                for (int I = 0; I < cul.Rows.Count; I++)
                {
                    column.Add(cul.Rows[I][0].ToString().ToUpper());
                }
                //查询语句
                string sql = $"select *  from {dbname}_tbl_importinfo  where   OutBoxCode='" + Key + "'";
                List<Dictionary<string, string>> diclist = new List<Dictionary<string, string>>();
                DbHelperMySQL.NoExecuteReader(sql, dr =>
                {
                    while (dr.Read())
                    {
                        Dictionary<string, string> dic = new Dictionary<string, string>();
                        foreach (var item in column)
                        {
                            dic.Add(item, dr[item].ToString());
                        }
                        diclist.Add(dic);
                    }
                });
                if (diclist.Count != 0)
                {
                    result.Data = diclist;
                    result.Message = "查询成功";
                    result.Status = 200;
                }
                else
                {
                    result.Status = 404;
                    result.Message = "未找到未找到这条记录";
                }
            }
            catch (Exception ex)
            {
                result.Message = ex.Message;
            }
            return result;
        }

        public static ReturnResult<object> UpdateDatSn(string dbname, string key, Dictionary<string, string> dic, int ProjectID)
        {
            string insertsql = "";
            ReturnResult<object> result = new ReturnResult<object>();
            List<string> column = new List<string>();//数据库字段名称
            string sun = "";
            if (dic.Keys.Contains("Barcode") && dic.Keys.Contains("ColorBox_BarCode"))
            {
                result.Message = "有敏感字段不可修改";
                result.Status = 101;
                return result;
            }
            if (key == "Barcode")
            {
                if (dic.Keys.Contains("ColorBox_BarCode"))
                {
                    result.Message = "有敏感字段不可修改";
                    result.Status = 101;
                    return result;
                }
            }

            if (key == "ColorBox_BarCode")
            {
                if (dic.Keys.Contains("Barcode"))
                {
                    result.Message = "有敏感字段不可修改";
                    result.Status = 101;
                    return result;
                }
            }

            if (key != "ColorBox_BarCode" && key != "Barcode")
            {
                if (dic.Keys.Contains("Barcode") || dic.Keys.Contains("ColorBox_BarCode"))
                {
                    result.Message = "有敏感字段不可修改";
                    result.Status = 101;
                    return result;
                }
            }


            foreach (var item in dic.Keys)
            {
                if (item == key)
                {
                    sun = dic[item];
                }
            }
            if (string.IsNullOrEmpty(sun))
            {
                result.Status = 404;
                result.Message = "未找到匹配字段";
                return result;
            }
            //List<string> dblist = new List<string>() { "uts_bomei", "uts_inhaos", "uts_johao", "uts_zongqing" };
          
            try
            {
                TBL_UTS_Manage_DBList dblist = SqlSugarBase.Db.Queryable<TBL_UTS_Manage_DBList>().First(x => x.DatabaseName == dbname);

                List<TBL_ImportInfo> importInfolist = new List<TBL_ImportInfo>();
                if (dblist == null)
                {
                    result.Status = 100;
                    result.Message = "查询不到合法的表名" + dbname;
                    return result;
                }
                ///获取数据库字段
                string SQL = $@"SELECT COLUMN_NAME FROM information_schema.COLUMNS
                        WHERE TABLE_SCHEMA = 'uts_db' AND TABLE_NAME = '{dbname}_tbl_importinfo'; ";
                var cul = DbHelperMySqlCustom.NoQuery(SQL).Tables[0];

                for (int I = 0; I < cul.Rows.Count; I++)
                {
                    column.Add(cul.Rows[I][0].ToString().ToUpper());
                }


                using (MySqlConnection connection = new MySqlConnection(connectionSrt))
                {
                    using (MySqlCommand cmd = new MySqlCommand())
                    {
                        var dalHelperCustom = new DALHelperCustom<TBL_ImportInfo>("TBL_ImportInfo");
                        connection.Open();
                        cmd.Connection = connection;
                        System.Data.DataSet dataSet = new System.Data.DataSet();
                        string seletSql = string.Format("select *  from {0}_tbl_importinfo where {1} ='{2}' and ProjectID={3} ", dbname, key, sun, ProjectID);
                        MySqlDataAdapter command1 = new MySqlDataAdapter(seletSql, connection);
                        command1.Fill(dataSet, "ds");
                        var dalHelperCustom1 = new DALHelperCustom<TBL_ImportInfo>("TBL_ImportInfo");
                        var UserlogList1 = dalHelperCustom.DataRowToModels(dataSet.Tables[0]);
                        if (UserlogList1.Count >= 2)
                        {
                            //foreach (var item in UserlogList1)
                            //{
                            //    TBL_ImportInfo _ImportInfo = new TBL_ImportInfo();
                            //    _ImportInfo.Barcode = item.Barcode;
                            //    _ImportInfo.ImportDateTime = item.ImportDateTime;
                            //    _ImportInfo.UpdateTime = item.UpdateTime;
                            //    _ImportInfo.ID = item.ID;
                            //    _ImportInfo.ProjectID = item.ProjectID;
                            //    _ImportInfo.Mars_ColorBox_BarCode = item.Mars_ColorBox_BarCode;
                            //    _ImportInfo.Mars_MacCode = item.Mars_MacCode;
                            //    _ImportInfo.Mars_UnitWeigth = item.Mars_UnitWeigth;
                            //    _ImportInfo.Mars_OutBoxCode = item.Mars_OutBoxCode;
                            //    importInfolist.Add(_ImportInfo);
                            //}
                            result.Message = "有重复数据绑定错误";
                            result.Status = 150;
                            //result.Data = importInfolist;
                            return result;
                        }
                        else if (UserlogList1.Count == 0)
                        {
                            result.Status = 404;
                            result.Message = "未找到对应数据";
                            return result;
                        }
                        insertsql = String.Format("UPDATE  {0}_tbl_importinfo SET ", dbname);
                        foreach (var item in dic.Keys)
                        {

                            insertsql += string.Format(" {0} = '{1}',", item, dic[item]);
                        }
                        //insertsql = insertsql.Remove(insertsql.Length - 1, 1);
                        insertsql += "UpdateTime=now()   WHERE  " + key + "= '" + sun + "' and ProjectID=" + ProjectID + "; ";
                        cmd.CommandText = insertsql;
                        if (cmd.ExecuteNonQuery() > 0)
                        {
                            Dictionary<string, string> dicData = new Dictionary<string, string>();
                            string seletSq2 = string.Format("select *  from {0}_tbl_importinfo where {1} ='{2}' and ProjectID={3} ", dbname, key, sun, ProjectID);
                            DbHelperMySQL.NoExecuteReader(seletSq2, dr =>
                            {
                                while (dr.Read())
                                {
                                    foreach (var item in column)
                                    {
                                        dicData.Add(item, dr[item].ToString());
                                    }
                                }
                            });
                            result.Message = "成功";
                            result.Status = 200;
                            result.Data = dicData;
                            return result;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                result.Status = 50;
                result.Message = ex.Message;
            }
            return result;
        }

        public static ReturnResult<object> UpdateDatSn2(string dbname, string key, Dictionary<string, string> dic, int ProjectID)
        {

            string insertsql = "";
            ReturnResult<object> result = new ReturnResult<object>();
            List<string> column = new List<string>();//数据库字段名称
            string sun = "";
            if (dic.Keys.Contains("Barcode") && dic.Keys.Contains("ColorBox_BarCode"))
            {
                result.Data = dic;
                result.Status = 50;
                result.Message = "不可修改";
                return result;
            }
            if (key == "Barcode")
            {
                if (dic.Keys.Contains("ColorBox_BarCode"))
                {
                    result.Data = dic;
                    result.Status = 50;
                    result.Message = "不可修改";
                    return result;
                }
            }
            if (key == "ColorBox_BarCode")
            {
                if (dic.Keys.Contains("Barcode"))
                {
                    result.Data = dic;
                    result.Status = 50;
                    result.Message = "不可修改";
                    return result;
                }
            }
            if (key != "ColorBox_BarCode" && key != "Barcode")
            {
                if (dic.Keys.Contains("Barcode") || dic.Keys.Contains("ColorBox_BarCode"))
                {
                    result.Data = dic;
                    result.Status = 50;
                    result.Message = "不可修改";
                    return result;
                }
            }
            foreach (var item in dic.Keys)
            {
                if (item == key)
                {
                    sun = dic[item];
                }
            }
            if (string.IsNullOrEmpty(sun))
            {
                result.Data = dic;
                result.Status = 50;
                result.Message = "未找到匹配字段";
                return result;
            }
            //List<string> dblist = new List<string>() { "uts_bomei", "uts_inhaos", "uts_johao", "uts_zongqing" };
            //List<TBL_ImportInfo> importInfolist = new List<TBL_ImportInfo>();
            //if (!dblist.Contains(dbname))
            //{
            //    result.Data = dic;
            //    result.Status = 50;
            //    result.Message = "查询不到合法的dbname";
            //    return result;
            //}
            try
            {
                TBL_UTS_Manage_DBList dblist = SqlSugarBase.Db.Queryable<TBL_UTS_Manage_DBList>().First(x => x.DatabaseName == dbname);

                List<TBL_ImportInfo> importInfolist = new List<TBL_ImportInfo>();
                if (dblist == null)
                {
                    result.Status = 100;
                    result.Message = "查询不到合法的表名" + dbname;
                    return result;
                }
                ///获取数据库字段
                string SQL = $@"SELECT COLUMN_NAME FROM information_schema.COLUMNS
                        WHERE TABLE_SCHEMA = 'uts_db' AND TABLE_NAME = '{dbname}_tbl_importinfo'; ";
                var cul = DbHelperMySqlCustom.NoQuery(SQL).Tables[0];

                for (int I = 0; I < cul.Rows.Count; I++)
                {
                    column.Add(cul.Rows[I][0].ToString().ToUpper());
                }
                using (MySqlConnection connection = new MySqlConnection(connectionSrt))
                {
                    using (MySqlCommand cmd = new MySqlCommand())
                    {
                        var dalHelperCustom = new DALHelperCustom<TBL_ImportInfo>("TBL_ImportInfo");
                        connection.Open();
                        cmd.Connection = connection;
                        System.Data.DataSet dataSet = new System.Data.DataSet();
                        string seletSql = string.Format("select *  from {0}_tbl_importinfo where {1} ='{2}' and ProjectID={3} ", dbname, key, sun, ProjectID);
                        MySqlDataAdapter command1 = new MySqlDataAdapter(seletSql, connection);
                        command1.Fill(dataSet, "ds");
                        var dalHelperCustom1 = new DALHelperCustom<TBL_ImportInfo>("TBL_ImportInfo");
                        var UserlogList1 = dalHelperCustom.DataRowToModels(dataSet.Tables[0]);
                        if (UserlogList1.Count >= 2)
                        {
                            result.Data = dic;
                            result.Status = 50;
                            result.Message = "有重复数据绑定错误";
                            //result.Data = importInfolist;
                            return result;
                        }
                        else if (UserlogList1.Count == 0)
                        {
                            result.Data = dic;
                            result.Status = 50;
                            result.Message = "未找到对应数据";
                            return result;
                        }
                        insertsql = String.Format("UPDATE  {0}_tbl_importinfo SET ", dbname);
                        foreach (var item in dic.Keys)
                        {

                            insertsql += string.Format(" {0} = '{1}',", item, dic[item]);
                        }
                        //insertsql = insertsql.Remove(insertsql.Length - 1, 1);
                        insertsql += "UpdateTime=now()   WHERE  " + key + "= '" + sun + "' and ProjectID=" + ProjectID + "; ";
                        cmd.CommandText = insertsql;
                        if (cmd.ExecuteNonQuery() > 0)
                        {
                            Dictionary<string, string> dicData = new Dictionary<string, string>();
                            string seletSq2 = string.Format("select *  from {0}_tbl_importinfo where {1} ='{2}' and ProjectID={3} ", dbname, key, sun, ProjectID);
                            DbHelperMySQL.NoExecuteReader(seletSq2, dr =>
                            {
                                while (dr.Read())
                                {
                                    foreach (var item in column)
                                    {
                                        dicData.Add(item, dr[item].ToString());
                                    }
                                }
                            });
                            result.Message = "成功";
                            result.Status = 200;
                            result.Data = dicData;
                            return result;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                result.Message = ex.Message;
            }
            return result;
        }


        public static Dictionary<string, string> InquireField()
        {
            string dataName = Users.GerOnUserCustomer().DatabaseName.ToLower();
            if (dataName == "dtl" || dataName == "demo")
            {
                dataName = "uts_" + dataName;
            }
            List<string> result = new List<string>();
            Dictionary<string, string> dic = new Dictionary<string, string>();
            ///获取数据库字段
            //string SQL = $@"SELECT COLUMN_NAME FROM information_schema.COLUMNS
            //            WHERE TABLE_SCHEMA = 'uts_db' AND TABLE_NAME = 'uts_zongqing_tbl_importinfo'; ";
            string SQL = $"show full columns from {dataName}_tbl_importinfo;";
            var cul = DbHelperMySqlCustom.NoQuery(SQL).Tables[0];

            for (int I = 0; I < cul.Rows.Count; I++)
            {
                //result.Add(cul.Rows[I][0].ToString().ToUpper());
                dic.Add(cul.Rows[I][0].ToString().ToUpper(), cul.Rows[I][8].ToString().ToUpper());
            }
            return dic;
        }

        public static Dictionary<string, string> colorboxlistField()
        {
            string dataName = Users.GerOnUserCustomer().DatabaseName.ToLower();
            if (dataName == "dtl" || dataName == "demo")
            {
                dataName = "uts_" + dataName;
            }
            List<string> result = new List<string>();
            Dictionary<string, string> dic = new Dictionary<string, string>();
            ///获取数据库字段
            //string SQL = $@"SELECT COLUMN_NAME FROM information_schema.COLUMNS
            //            WHERE TABLE_SCHEMA = 'uts_db' AND TABLE_NAME = 'uts_zongqing_tbl_importinfo'; ";
            string SQL = $"show full columns from {dataName}_tbl_colorboxlist;";
            var cul = DbHelperMySqlCustom.NoQuery(SQL).Tables[0];

            for (int I = 0; I < cul.Rows.Count; I++)
            {
                //result.Add(cul.Rows[I][0].ToString().ToUpper());
                dic.Add(cul.Rows[I][0].ToString().ToUpper(), cul.Rows[I][8].ToString().ToUpper());
            }
            return dic;
        }

        public static ReturnResult<object> insertField(string Field, string remark)
        {
            string dataName = Users.GerOnUserCustomer().DatabaseName.ToLower();
            List<string> dblist = new List<string>() { "uts_bomei", "uts_inhaos", "uts_johao", "uts_zongqing","uts_dtl" };//数据库名称
            ReturnResult<object> result = new ReturnResult<object>();
            List<TBL_ImportInfo> importInfolist = new List<TBL_ImportInfo>();
            string sql = "";
            try
            {

                List<string> column = new List<string>();//数据库字段名称
                ///获取数据库字段

                using (MySqlConnection connection = new MySqlConnection(connectionSrt))
                {
                    using (MySqlCommand cmd = new MySqlCommand())
                    {
                        connection.Open();
                        cmd.Connection = connection;
                        //查询语句
                        foreach (var item in dblist)
                        {
                            if (item.Contains(dataName))
                            {
                                sql += $"alter table {item}_tbl_importinfo Add column {Field} varchar(255) not null default '' comment '{remark}';";
                            }
                        }
                        cmd.CommandText = sql;
                        if (cmd.ExecuteNonQuery() > 0)
                        {
                            result.Status = 200;
                            result.Message = "成功";
                        }
                        result.Status = 200;
                        result.Message = "成功";
                    }
                }
            }
            catch (Exception ex)
            {
                result.Status = 50;
                result.Message = ex.Message;
            }
            return result;
        }

        public static ReturnResult<object> colorboxlistField(string Field, string remark)
        {
            string dataName = Users.GerOnUserCustomer().DatabaseName.ToLower();
            List<string> dblist = new List<string>() { "uts_bomei", "uts_inhaos", "uts_johao", "uts_zongqing", "uts_dtl" };//数据库名称
            ReturnResult<object> result = new ReturnResult<object>();
            List<TBL_ImportInfo> importInfolist = new List<TBL_ImportInfo>();
            string sql = "";
            try
            {

                List<string> column = new List<string>();//数据库字段名称
                ///获取数据库字段

                using (MySqlConnection connection = new MySqlConnection(connectionSrt))
                {
                    using (MySqlCommand cmd = new MySqlCommand())
                    {
                        connection.Open();
                        cmd.Connection = connection;
                        //查询语句
                        foreach (var item in dblist)
                        {
                            if (item.Contains(dataName))
                            {
                                sql += $"alter table {item}_tbl_colorboxlist Add column {Field} varchar(255) not null default '' comment '{remark}';";
                            }
                        }
                        cmd.CommandText = sql;
                        cmd.ExecuteNonQuery();
                        result.Status = 200;
                        result.Message = "成功";
                    }
                }
            }
            catch (Exception ex)
            {
                result.Status = 50;
                result.Message = ex.Message;
            }
            return result;
        }

        public static void CreateData()
        {
            string SqlData = "";
            try
            {
                //获取所有厂家
                var list = new List<TBL_UTS_Manage_Company>();
                using (var db = new Uts_ManageEntities())
                {
                    //取所有
                    list = db.TBL_UTS_Manage_Company.ToList();
                }
                List<string> column = new List<string>();//数据库表名称
                string sql = "select table_name  from information_schema.tables where table_schema = 'uts_db';";
                var cul = DbHelperMySqlCustom.NoQuery(sql).Tables[0];

                using (MySqlConnection connection = new MySqlConnection(connectionSrt))
                {
                    using (MySqlCommand cmd = new MySqlCommand())
                    {
                        connection.Open();
                        cmd.Connection = connection;
                        for (int I = 0; I < cul.Rows.Count; I++)
                        {
                            column.Add(cul.Rows[I][0].ToString().ToUpper());
                        }
                        foreach (var item in list)
                        {
                            var sety = "uts_" + item.CustomerName + "_tbl_importinfo";
                            sety = sety.ToUpper();
                            if (!column.Contains(sety))
                            {
                                SqlData = $"CREATE TABLE IF NOT EXISTS `uts_{item.CustomerName}_tbl_importinfo`(`ID` INT UNSIGNED AUTO_INCREMENT COMMENT '编号', `ProjectID` INT NOT NULL COMMENT '项目唯一标识', `Barcode` VARCHAR(40) COMMENT '产品序号唯一标识',  `ImportDateTime` datetime NOT NULL COMMENT '录入时间',	 `UpdateTime` datetime NOT NULL COMMENT '更新时间',   PRIMARY KEY( `ID` ))ENGINE = InnoDB DEFAULT CHARSET = utf8; ";
                            }
                        }
                        if (!string.IsNullOrEmpty(SqlData))
                        {
                            cmd.CommandText = SqlData;
                            cmd.ExecuteNonQuery();
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        public static string ProjectData(int userid, int dbid)
        {
            string sirlit = string.Empty;
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                using (MySqlCommand cmd = new MySqlCommand())
                {
                    connection.Open();
                    cmd.Connection = connection;
                    var strsql = $"select * from TBL_UTS_Manage_UserAuth_Operation where UserID={userid} and DatabaseID={dbid} ";
                    System.Data.DataSet dataSet = new System.Data.DataSet();
                    MySqlDataAdapter command = new MySqlDataAdapter(strsql, connection);
                    command.Fill(dataSet, "ds");
                    for (int i = 0; i < dataSet.Tables.Count; i++)
                    {
                        if (dataSet.Tables[i].Rows.Count > 0)
                        {
                            for (int row = 0; row < dataSet.Tables[i].Rows.Count; row++)
                            {
                                for (int j = 0; j < dataSet.Tables[i].Columns.Count; j++)
                                {
                                    if (dataSet.Tables[i].Columns[j].ToString() == "ReadProject")
                                    {
                                        if (dataSet.Tables[i].Rows[row][j] != null && dataSet.Tables[i].Rows[row][j].ToString() != "")
                                        {
                                            sirlit = dataSet.Tables[i].Rows[row][j].ToString();
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
            return sirlit;
        }


        public static ReturnResultsOutBox SqlUnbindOutboxAndColorbox(string OutBoxCode, string ColorBoxCodeList, string dbName, int ProjectID)
        {
            ReturnResultsOutBox oj = new ReturnResultsOutBox();

            string[] seps = { "|" };
            string[] colorCodeList = { };
            if (!String.IsNullOrWhiteSpace(ColorBoxCodeList))
                colorCodeList = ColorBoxCodeList.Trim().Split(seps, StringSplitOptions.RemoveEmptyEntries);

            //List<string> dblist = new List<string>() { "uts_bomei", "uts_inhaos", "uts_johao", "uts_zongqing" };
         

            try
            {
                TBL_UTS_Manage_DBList dblist = SqlSugarBase.Db.Queryable<TBL_UTS_Manage_DBList>().First(x => x.DatabaseName == dbName);
                if (dblist == null)
                {
                    // return values
                    //oj.ColorBoxList = colorCodeList.ToList();
                    oj.CorBarPairList = new List<ColorBarPair>();
                    oj.ProjectID = 0;
                    oj.OutBoxCode = OutBoxCode;
                    oj.ShippingCode = "";
                    oj.Message = "查询不到合法的表名"+dbName;
                    oj.Status = 100;
                    return oj;
                }
                using (MySqlConnection connection = new MySqlConnection(connectionSrt))
                {
                    using (MySqlCommand cmd = new MySqlCommand())
                    {
                        connection.Open();

                        cmd.Connection = connection;
                        string dataName = dbName;

                        //先确定有这样的外箱码纪录，并顺便取到这样纪录的信息
                        List<int> tmpListKey = new List<int>();//站控表的主键ID
                        List<string> tmpListColorCode = new List<string>();
                        List<string> tmpListProductBarCode = new List<string>();
                        List<ColorBarPair> tmpColorBarList = new List<ColorBarPair>();
                        Dictionary<string, string> tmpDicBar2Color = new Dictionary<string, string>();

                        string strSql = "select * from " + dataName + "_tbl_importinfo where OutBoxCode = '" + OutBoxCode + "' and  ProjectID=" + ProjectID + "";
                        System.Data.DataSet dataSet = new System.Data.DataSet();
                        MySqlDataAdapter command = new MySqlDataAdapter(strSql, connection);
                        command.Fill(dataSet, "ds");

                        // 没有这样的外箱，所以不用解绑。
                        if (dataSet.Tables.Count <= 0 || dataSet.Tables[0].Rows.Count <= 0)
                        {
                            // return values
                            //oj.BarcodeList = new List<String>();
                            //oj.ColorBoxList = new List<string>();
                            oj.CorBarPairList = new List<ColorBarPair>();
                            oj.ProjectID = 0;
                            oj.OutBoxCode = OutBoxCode;
                            oj.ShippingCode = "";
                            oj.Message = "没有指定的外箱";
                            oj.Status = 100;
                            return oj;
                        }

                        string shippingcode = "";
                        int tmpProjectID = 0;


                        // 有外箱，取到外箱包含的彩箱，产品条码信息
                        foreach (DataRow dr in dataSet.Tables[0].Rows)
                        {
                            int tmpKey = dr[0].ToInt();
                            string tmpColor = dr["ColorBox_BarCode"].ToString();
                            string tmpBar = dr["Barcode"].ToString();


                            if (dr["ShippingCode"] != DBNull.Value && dr["ShippingCode"] != null)
                            {
                                shippingcode = dr["ShippingCode"].ToString();
                            }

                            if (dr["ProjectID"] != DBNull.Value && dr["ProjectID"] != null)
                            {
                                bool beConverSucc = int.TryParse(dr["ProjectID"].ToString(), out tmpProjectID);
                                if (!beConverSucc)
                                    tmpProjectID = 0;
                            }
                            /*if (!String.IsNullOrWhiteSpace( shippingcode ))
                            {
                                oj.CorBarPairList = new List<ColorBarPair>();
                                oj.ProjectID = tmpProjectID;
                                oj.OutBoxCode = OutBoxCode;
                                oj.ShippingCode = shippingcode;
                                oj.Message = "指定的外箱已经出货";
                                oj.Status = 100;
                                return oj;
                            }*/

                            tmpListKey.Add(dr[0].ToInt());
                            tmpListColorCode.Add(tmpColor);
                            tmpListProductBarCode.Add(tmpBar);

                            ColorBarPair tmpPair = new ColorBarPair();
                            tmpPair.ColorBox = tmpColor;
                            tmpPair.BarCode = tmpBar;
                            tmpColorBarList.Add(tmpPair);
                            tmpDicBar2Color[tmpBar] = tmpColor;
                        }

                        // 看用户输入是否有彩箱码
                        if (colorCodeList.Length > 0) // 部分解绑 （有外箱码，有彩盒码列表）
                        {
                            //0. 验证用户输入彩箱码列表是否正确。
                            bool beAllOK = true;
                            for (int i = 0; i < colorCodeList.Length; i++)
                            {
                                if (!tmpListColorCode.Contains(colorCodeList[i]))
                                {
                                    beAllOK = false;
                                    break;
                                }
                            }
                            if (!beAllOK)
                            {
                                // return values
                                //oj.BarcodeList = new List<string>();
                                //oj.ColorBoxList = new List<string>();
                                oj.CorBarPairList = new List<ColorBarPair>();
                                oj.ProjectID = tmpProjectID;
                                oj.OutBoxCode = OutBoxCode;
                                oj.ShippingCode = shippingcode;
                                oj.Message = "输入的彩箱码并没有全部绑定在指定外箱码上";
                                oj.Status = 100;
                                return oj;
                            }

                            // 1. 验证通过，部分解绑
                            string actionSql = "UPDATE  " + dataName + "_tbl_importinfo SET OutBoxCode = '', UpdateTime=now() ";
                            actionSql += "WHERE ProjectID=" + ProjectID + " ";
                            actionSql += " AND ColorBox_BarCode in (";
                            foreach (var cod in colorCodeList)
                            {
                                actionSql += ("'" + cod + "',");
                            }
                            actionSql = actionSql.Substring(0, actionSql.Length - 1);
                            actionSql += ")";
                            cmd.CommandText = actionSql;
                            if (cmd.ExecuteNonQuery() > 0)
                            {
                                // return values
                                //oj.BarcodeList = tmpListProductBarCode.Where(a => colorCodeList.Contains(tmpDicBar2Color[a])).ToList();
                                //oj.ColorBoxList = colorCodeList.ToList();
                                oj.CorBarPairList = tmpColorBarList.Where(a => colorCodeList.Contains(a.ColorBox)).ToList();
                                oj.ProjectID = tmpProjectID;
                                oj.OutBoxCode = OutBoxCode;
                                oj.ShippingCode = shippingcode;
                                oj.Message = "完成指定彩箱和外箱的解绑";
                                oj.Status = 200;
                                return oj;
                            }
                        }
                        else // 全部解绑  (用户输入外箱码，不输入彩盒码)
                        {
                            //全部解绑
                            string actionSql = "UPDATE  " + dataName + "_tbl_importinfo SET OutBoxCode = '', UpdateTime=now() ";
                            actionSql += "WHERE ProjectID=" + ProjectID + " ";
                            actionSql += " AND ID in (";
                            foreach (var p in tmpListKey)
                            {
                                actionSql += (p + ",");
                            }
                            actionSql = actionSql.Substring(0, actionSql.Length - 1);
                            actionSql += ")";

                            cmd.CommandText = actionSql;
                            if (cmd.ExecuteNonQuery() > 0)
                            {
                                // return values
                                //oj.BarcodeList = tmpListProductBarCode;
                                //oj.ColorBoxList = tmpListColorCode;
                                oj.CorBarPairList = tmpColorBarList;
                                oj.ProjectID = tmpProjectID;
                                oj.OutBoxCode = OutBoxCode;
                                oj.ShippingCode = shippingcode;
                                oj.Message = "完成全部解绑";
                                oj.Status = 200;
                                return oj;
                            }
                        }

                    }
                }
            }
            catch (Exception ex)
            {
                // return values
                //oj.BarcodeList = new List<string>();
                //oj.ColorBoxList = new List<string>();
                oj.CorBarPairList = new List<ColorBarPair>();
                oj.ProjectID = 0;
                oj.OutBoxCode = OutBoxCode;
                oj.ShippingCode = "";
                oj.Message = "解绑过程异常：" + ex.ToString();
                oj.Status = 50;
                return oj;
            }
            return null;
        }

     
    }
}