zhihuidanao/DataSendApi.Program/BLL/BusinessProcess.cs

using DataSendApi.Program.BLL.Token;
using DataSendApi.Program.CustomizeAttribute;
using DataSendApi.Program.Model;
using DataSendApi.Program.Oracle;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
namespace DataSendApi.Program.BLL
{
    class DataTableModel
    {
        /// <summary>
        /// 当前类型
        /// </summary>
        public Type Type { get; set; }
        /// <summary>
        /// 当前表
        /// </summary>
        public CustomizeTableAttribute CustomizeTable { get; set; } = new CustomizeTableAttribute();
        /// <summary>
        /// 当前列集合
        /// </summary>
        public List<CustomizeFieldAttribute> CustomizeFieldLst { get; set; } = new List<CustomizeFieldAttribute>();
        /// <summary>
        /// 当前数据集合
        /// </summary>
        public List<Hashtable> CustomizeData { get; set; } = new List<Hashtable>();
    }
    public class BusinessProcess
    {
        /// <summary>
        /// 通过excel地址, 处理数据到数据库
        /// </summary>
        public ReturnEntity HandleByDatabase(string path)
        {
            var retObj = new ReturnEntity();
            //获取Excel的数据模型
            var data = new HandleExcel().ReadFromExcelFile(path);
            //表名
            string TableName = string.Empty;
            //列名
            List<string> ColoumnsName = new List<string>();
            //数据集合
            List<List<string>> DataLst = new List<List<string>>();


            #region 效验数据 并获取数据集合
            //校验数据模型
            if (data.Count <= 4)
            {
                retObj.Code = 1;
                retObj.Message = "数据导入失败,表格行数异常或无数据";
                return retObj;
            }
            if (!data[1][0].Contains("$"))
            {
                retObj.Code = 1;
                retObj.Message = "数据导入失败,模板异常,请重新下载模板";
                return retObj;
            }
            //获取表名
            TableName = data[1][0].Split('$')[1].ToUpper();
            if (!Common.AllTableName.Contains(TableName))
            {
                retObj.Code = 1;
                retObj.Message = "数据导入失败,该表不存在,模板异常,请重新下载模板";
                return retObj;
            }
            //获取列名
            foreach (var item in data[2])
            {
                if (!item.Contains("$"))
                {
                    retObj.Code = 1;
                    retObj.Message = "数据导入失败,模板异常,请重新下载模板";
                    return retObj;
                }
                string coloumnName = item.Split('$')[1].ToUpper();
                if (!Common.AllColoumnName.Contains(TableName + "." + coloumnName))
                {
                    retObj.Code = 1;
                    retObj.Message = "数据导入失败,模板异常,请重新下载模板";
                    return retObj;
                }
                ColoumnsName.Add(coloumnName);
            }

            for (int i = 4; i < data.Count; i++)
            {
                var _tempData = new List<string>();
                for (int j = 0; j < ColoumnsName.Count; j++)
                {
                    if (j + 1 <= data[i].Count())
                    {
                        _tempData.Add(data[i][j]);
                    }
                    else
                    {
                        _tempData.Add("");
                    }
                }
                DataLst.Add(_tempData);
            }
            #endregion

            #region 获取表 列属性
            //获取表列属性
            var curType = Type.GetType($"DataSendApi.Program.Model.{TableName}Entity");
            List<CustomizeFieldAttribute> FieldAttrLst = new List<CustomizeFieldAttribute>();
            CustomizeTableAttribute TableAttr = curType.GetCustomAttributes(typeof(CustomizeTableAttribute), false)[0] as CustomizeTableAttribute;
            foreach (var item in ColoumnsName)
            {
                var curPType = curType.GetProperty(item).GetCustomAttributes(typeof(CustomizeFieldAttribute), false)[0] as CustomizeFieldAttribute;
                FieldAttrLst.Add(curPType);
            }
            #endregion

            #region 验证数据
            //循环每行数据
            for (int i = 0; i < DataLst.Count; i++)
            {
                var rowCount = i + 1;
                var rowData = DataLst[i];
                //循环每列数据
                for (int j = 0; j < FieldAttrLst.Count; j++)
                {
                    var rowAttr = FieldAttrLst[j];
                    //数据校验

                    //验证效验列
                    if (rowAttr.IsExcelVerify && rowData[j] == "")
                    {
                        retObj.Code = 2;
                        retObj.Message = $"数据导入失败,第[{rowCount}]行,[{rowAttr.ChineseColumnName}]列为必填项！";
                        return retObj;
                    }
                    //验证填写基础数据是否正确
                    if (rowAttr.IsJson)
                    {
                        if (rowAttr.TsVerify == "1" && rowData[j].Replace("|", "").Length > 0)
                        {
                            string _t = string.Empty;
                            foreach (var ts in rowData[j].Split('|'))
                            {
                                if (string.IsNullOrEmpty(ts)) continue;

                                var _temp = Common.GGSJZDLst.FirstOrDefault(P => P.ZDLX == rowAttr.JsonName && P.MC == ts);
                                if (_temp == null || string.IsNullOrEmpty(_temp.DM))
                                {
                                    retObj.Code = 2;
                                    retObj.Message = $"数据导入失败,第[{rowCount}]行,[{rowAttr.ChineseColumnName}]请参照对应的字段表填写名称！";
                                    return retObj;
                                }
                                _t += _temp.DM + "||";
                            }
                            rowData[j] = _t.Substring(0, _t.Length - 2);
                        }
                        else
                        {
                            var _temp = Common.GGSJZDLst.FirstOrDefault(P => P.ZDLX == rowAttr.JsonName && P.MC == rowData[j]);
                            if (_temp == null || string.IsNullOrEmpty(_temp.DM))
                            {
                                retObj.Code = 2;
                                retObj.Message = $"数据导入失败,第[{rowCount}]行,[{rowAttr.ChineseColumnName}]请参照对应的字段表填写名称！";
                                return retObj;
                            }
                            rowData[j] = _temp.DM;
                        }
                    }
                    //验证值是否为数字
                    if (rowAttr.ColumnType == "decimal")
                    {
                        try
                        {
                            Convert.ToDecimal(rowData[j]);
                        }
                        catch
                        {
                            retObj.Code = 2;
                            retObj.Message = $"数据导入失败,第[{rowCount}]行,[{rowAttr.ChineseColumnName}]列只能填写数字！";
                            return retObj;
                        }
                    }
                    //数据格式验证
                    if (rowAttr.ColumnFormat != "" && rowAttr.ColumnFormat.Length > 0)
                    {
                        try
                        {
                            if (rowData[j] != null && rowData[j] != "")
                            {
                                if (rowData[j].Length == rowAttr.ColumnFormat.Length)
                                {
                                    Convert.ToDateTime(rowData[j]);
                                }
                                else
                                {
                                    retObj.Code = 2;
                                    retObj.Message = $"数据导入失败,第[{rowCount}]行,[{rowAttr.ChineseColumnName}]列未按照格式填写,参考格式{rowAttr.ColumnFormat}！";
                                    return retObj;
                                }
                            }
                        }
                        catch
                        {
                            retObj.Code = 2;
                            retObj.Message = $"数据导入失败,第[{rowCount}]行,[{rowAttr.ChineseColumnName}]列未按照格式填写,参考格式{rowAttr.ColumnFormat}！";
                            return retObj;
                        }
                    }
                    //数据长度验证
                    if (rowAttr.ColumnType == "string")
                    {
                        if (!rowAttr.IsJson && rowData[j].Length > rowAttr.ColumnLength)
                        {

                            retObj.Code = 2;
                            retObj.Message = $"数据导入失败,第[{rowCount}]行,[{rowAttr.ChineseColumnName}]列字符过多,不能超过{rowAttr.ColumnLength}个字符！";
                            return retObj;
                        }
                    }
                    //换行验证
                    if (rowData[j] != null && rowData[j] != "")
                    {
                        if (rowData[j].IndexOf('\n') >= 0)
                        {
                            retObj.Code = 2;
                            retObj.Message = $"数据导入失败,第[{rowCount}]行,[{rowAttr.ChineseColumnName}]列不允许换行！";
                            return retObj;
                        }
                    }


                }
            }
            #endregion


            #region 封装数据
            //封装数据
            DataTableModel dataTableModel = new DataTableModel();
            dataTableModel.Type = curType;
            dataTableModel.CustomizeTable = TableAttr;

            foreach (var item in dataTableModel.Type.GetProperties())
            {
                var _temp = (item.GetCustomAttributes(typeof(CustomizeFieldAttribute), false)[0] as CustomizeFieldAttribute);
                if (_temp.IsDatabase)
                    dataTableModel.CustomizeFieldLst.Add(_temp);
            }
            for (int i = 0; i < DataLst.Count; i++)
            {
                var _data = DataLst[i];
                Hashtable _ht = new Hashtable();

                foreach (var item in dataTableModel.CustomizeFieldLst)
                {
                    if (item.IsPrimaryKey)
                    {
                        _ht.Add(item.DatabaseColumnName, Guid.NewGuid().ToString().Replace("-", ""));
                        continue;
                    }
                    if (item.DatabaseColumnName == "SJCJSJ")
                    {
                        _ht.Add("SJCJSJ", DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss"));
                        continue;
                    }
                    if (item.DatabaseColumnName == "IsPush")
                    {
                        _ht.Add("IsPush", "0");
                        continue;
                    }

                    for (int j = 0; j < FieldAttrLst.Count; j++)
                    {
                        if (FieldAttrLst[j].DatabaseColumnName == item.DatabaseColumnName)
                        {
                            _ht.Add(item.DatabaseColumnName, _data[j]);
                        }
                    }
                }
                dataTableModel.CustomizeData.Add(_ht);
            }
            #endregion



            return ConnentionDataByDatabase(dataTableModel);
        }
        /// <summary>
        /// 获取推送数据
        /// </summary>
        public ReturnEntity GetDataPush(string table)
        {
            var retObj = new ReturnEntity();

            #region 获取表 列属性
            //获取表列属性
            var curType = Type.GetType($"DataSendApi.Program.Model.{table.ToUpper()}Entity");
            List<CustomizeFieldAttribute> FieldAttrLst = new List<CustomizeFieldAttribute>();
            CustomizeTableAttribute TableAttr = curType.GetCustomAttributes(typeof(CustomizeTableAttribute), false)[0] as CustomizeTableAttribute;
            foreach (var item in curType.GetProperties())
            {
                var curPType = item.GetCustomAttributes(typeof(CustomizeFieldAttribute), false)[0] as CustomizeFieldAttribute;
                FieldAttrLst.Add(curPType);
            }
            #endregion

            #region 获取数据
            string _sql = $"SELECT * FROM {table.ToUpper()} WHERE  IsPush = '0' ";
            var _tempLst = DbContext.Query<dynamic>(_sql);
            if (_tempLst == null || _tempLst.Count() <= 0)
            {
                retObj.Code = 4;
                retObj.Message = "该表无可推送数据";
                return retObj;
            }
            string retrunJson = $@"
{{
    ""dataObjName"" : ""{table.ToLower()}"",
    ""fileds"" : [";

            foreach (var item in _tempLst)
            {
                var fields = item as IDictionary<string, object>;
                retrunJson += $@"
        {{";
                foreach (var val in FieldAttrLst)
                {
                    if (val.IsExcel)
                    {
                        var value = string.Empty;
                        if (fields[val.DatabaseColumnName] != null)
                            value = fields[val.DatabaseColumnName].ToString();
                        //获取特殊数据库数据
                        if (val.TsVerify == "2")
                        {
                            value = DbContext.ExecuteScalar<string>(" SELECT COUNT(*) FROM ods_dyfzqkjcsj");
                        }

                        var num = value.IndexOf('\n');
                        if ((num) >= 0)
                        {
                            value = value.Replace("\n", "，");
                        }


                        retrunJson += $"\"{val.DatabaseColumnName.ToLower()}\" : \"{value}\",";
                    }
                }
                retrunJson = retrunJson.Substring(0, retrunJson.Length - 1);
                retrunJson += $@"
        }},";
            }
            retrunJson = retrunJson.Substring(0, retrunJson.Length - 1);
            retrunJson += @"
    ]
}";
            retObj.Data = retrunJson;

            return retObj;
            #endregion
        }

        /// <summary>
        /// 执行推送
        /// </summary>
        public ReturnEntity ExecDataPush(string tablename)
        {
            var ret = GetDataPush(tablename);

            if (ret.Code != 0) return ret;



            return new DataCollectionService().PushData(ret.Data as string);
        }

        /// <summary>
        /// 数据增量查询
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="startTime">开始时间(yyyy-MM-dd hh:mm:ss)</param>
        /// <param name="endTime">结束时间(yyyy-MM-dd hh:mm:ss)</param>
        /// <param name="page">页码</param>
        /// <param name="limit">每页大小</param>
        /// <returns></returns>
        public ReturnEntity GetPushDataAddCount(
            string tableName,
            string startTime,
           string endTime,
           int page = 1,
           int limit = 100
           )
        {
            return new DataCollectionService().GetPushDataAddCount(tableName, startTime, endTime, page, limit);
        }

        public ReturnEntity GetTableCount(string tableName)
        {
            string _sql = "";
            foreach (var item in tableName.Split(','))
            {
                if (string.IsNullOrEmpty(item)) continue;
                _sql += $@"
SELECT '{item}' as tablename ,COUNT(*) as count FROM {item.ToUpper()} WHERE  IsPush = '0' 
";
                _sql += "    UNION ALL";
            }
            var retObj = new ReturnEntity();

            _sql = _sql.Length > 0 ? _sql.Substring(0, _sql.Length - 10) : _sql;
            if (!string.IsNullOrEmpty(_sql))
            {

                retObj.Data = DbContext.Query<TableNameModel>(_sql);
             
            }


            return retObj;
        }

        public ReturnEntity UpdatePushStatus(string tableName)
        {

            var retObj = new ReturnEntity();
            string _sql = $"UPDATE  {tableName.ToUpper()} SET IsPush = '1'  WHERE  IsPush = '0' ";

            retObj.Data = DbContext.Execute(_sql);

            return retObj;
        }

        /// <summary>
        /// 将数据写入数据库
        /// </summary>
        private ReturnEntity ConnentionDataByDatabase(DataTableModel dm)
        {
            var retObj = new ReturnEntity();

            var tran = DapperHelper.OpenCurrentDbConnection().BeginTransaction();
            int row = 1;
            int addCount = 0;
            int updateCount = 0;
            try
            {
                foreach (var item in dm.CustomizeData)
                {
                    string _sqlJoinVerify = JoinVerify(dm.CustomizeTable, dm.CustomizeFieldLst, item);
                    var wyzj = DbContext.ExecuteScalar<string>(_sqlJoinVerify, null, tran);
                    if (!string.IsNullOrEmpty(wyzj))
                    {
                        //更新
                        string _sqlJoinuUpdate = JoinuUpdate(dm.CustomizeTable, dm.CustomizeFieldLst, item, wyzj);
                        DbContext.Execute(_sqlJoinuUpdate, null, tran);
                        updateCount++;
                    }
                    else
                    {
                        //插入
                        string _sqlJoinInsert = JoinInsert(dm.CustomizeTable, dm.CustomizeFieldLst, item);
                        DbContext.Execute(_sqlJoinInsert, null, tran);
                        addCount++;
                    }
                    row++;
                }
                tran.Commit();
            }
            catch (Exception ex)
            {
                tran.Rollback();
                retObj.Code = 2;
                retObj.Message = $"第{row}行数据写入数据库时异常,数据已回滚,异常原因:" + ex.Message;
            }
            retObj.Data = new { addCount = addCount, updateCount = updateCount };
            return retObj;
        }

        /// <summary>
        /// 拼接查询验证SQL
        /// </summary>
        private string JoinVerify(CustomizeTableAttribute customizeTable, List<CustomizeFieldAttribute> fieldAttributes, Hashtable hashtable)
        {
            string _sql = string.Empty;

            _sql = $" SELECT * FROM {customizeTable.DatabaseTableName} WHERE 1 = 1 ";
            foreach (var item in fieldAttributes)
            {
                if (item.IsExcelVerify)
                    _sql += $" AND {item.DatabaseColumnName} = '{hashtable[item.DatabaseColumnName].ToString()}' ";
            }
            return _sql;
        }

        /// <summary>
        /// 拼接插入SQL
        /// </summary>
        private string JoinInsert(CustomizeTableAttribute customizeTable, List<CustomizeFieldAttribute> fieldAttributes, Hashtable hashtable)
        {
            string _sql = string.Empty;
            _sql = $" INSERT INTO {customizeTable.DatabaseTableName} (";

            foreach (var item in fieldAttributes)
            {
                _sql += $"{item.DatabaseColumnName},";
            }
            _sql = _sql.Substring(0, _sql.Length - 1);
            _sql += ") VALUES (";
            foreach (var item in fieldAttributes)
            {
                _sql += $"'{hashtable[item.DatabaseColumnName]}',";
            }

            _sql = _sql.Substring(0, _sql.Length - 1);
            return _sql += ")";
        }

        /// <summary>
        /// 拼接更新SQL
        /// </summary>
        private string JoinuUpdate(CustomizeTableAttribute customizeTable, List<CustomizeFieldAttribute> fieldAttributes, Hashtable hashtable, string wyzj)
        {
            string _sql = string.Empty;
            _sql = $" UPDATE {customizeTable.DatabaseTableName} SET ";
            string _tempSql = string.Empty;
            foreach (var item in fieldAttributes)
            {

                if (!item.IsPrimaryKey && !item.IsExcelVerify)
                    _sql += $"{item.DatabaseColumnName} = '{hashtable[item.DatabaseColumnName]}',";
                if (item.IsPrimaryKey)
                    _tempSql += $" WHERE {item.DatabaseColumnName} = '{wyzj}'";
            }
            return _sql.Substring(0, _sql.Length - 1) + _tempSql;
        }

    }

    public class TableNameModel
    {
        public string TABLENAME { get; set; }
        public string COUNT { get; set; }
    }
}