using Hh.Mes.Common;
using Hh.Mes.Common.config;
using Hh.Mes.Common.Http;
using Hh.Mes.Common.Infrastructure;
using Hh.Mes.Common.Json;
using Hh.Mes.Common.log;
using Hh.Mes.Common.Redis;
using Hh.Mes.Common.Request;
using Hh.Mes.Pojo.System;
using Hh.Mes.POJO.Entity;
using Hh.Mes.POJO.EnumEntitys;
using Hh.Mes.POJO.Response;
using Hh.Mes.Service.Logs;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Http;
using Newtonsoft.Json;
using Org.BouncyCastle.Crypto;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Linq.Expressions;
using System.Net;
using System.Reflection;
using System.Text;
namespace Hh.Mes.Service.Repository
{
/// <summary>
/// SqlSugar
/// http://www.codeisbug.com/Home/Doc?typeId=1228
/// C#拼接SQL中in条件 https://www.cnblogs.com/zhuyuchao/p/10412031.html
/// </summary>
/// <typeparam name="T"></typeparam>
public class RepositorySqlSugar<T> : SimpleClient<T> where T : class, new()
{
#region 属性
/// <summary>
/// 用户信息web（xxxServer注入的时候传入）
/// </summary>
public dynamic sysWebUser { get; set; }
/// <summary>
/// api接口身份信息(类混乱结果)
/// </summary>
public UserAuthSession sysUserApi { get; set; }
/// <summary>
/// 上传文件夹目录
/// </summary>
public string uploadFolder { get; set; }
/// <summary>
///动态值
/// </summary>
public dynamic ObjValue { get; set; }
#endregion
public RepositorySqlSugar(ISqlSugarClient context = null) : base(context)//注意这里要有默认值等于null
{
if (context == null)
{
var conStr = ConfigRead.GetInstance.GetAppsetConnection().BaseDBContext;
base.Context = new SqlSugarClient(new ConnectionConfig()
{
//数据库类型
DbType = DbType.SqlServer,
//从特性读取主键自增信息
InitKeyType = InitKeyType.Attribute,
//自动释放和关闭数据库连接，如果有事务事务结束时关闭，否则每次操作后关闭
IsAutoCloseConnection = true,
//连接符字串
ConnectionString = conStr,
//增加对C#自带的实体特性支持，比如实体的[Table]特性，字段的[Key]特性
ConfigureExternalServices = SetExternalServices(),
MoreSettings = new ConnMoreSettings()
{
IsWithNoLockQuery = true//全局 With(nolock)
},
});
//OnError 写日志
base.Context.Aop.OnError = ExceptionLog;
}
uploadFolder = "UploadFile";
}
/// <summary>
/// 设置自定义特性（增加对C#自带的实体特性支持，比如实体的[Table]特性，字段的[Key]特性等）
/// </summary>
/// <returns></returns>
private static ConfigureExternalServices SetExternalServices()
{
return new ConfigureExternalServices()
{
//设置实体的SqlSugar特性，从而把C#自带的[Table]特性变成SqlSugar特性
EntityNameService = (type, entity) =>
{
var attributes = type.GetCustomAttributes(true);
if (attributes.Any(it => it is TableAttribute))
{
entity.DbTableName = (attributes.First(it => it is TableAttribute) as TableAttribute).Name;
}
},
//设置字段的SqlSugar特性，从而把C#自带的字段特性变成SqlSugar特性
EntityService = (property, column) =>
{
//获取所有特性
var attributes = property.GetCustomAttributes(true);//get all attributes
// 如果特性含有字段的[Key]特性，就设置字段SqlSugar的IsPrimaryKey特性
if (attributes.Any(it => it is KeyAttribute))
{
column.IsPrimarykey = true;
}
// 如果特性含有字段的[NotMapped]特性，就设置字段SqlSugar的IsIgnore特性
if (attributes.Any(it => it is NotMappedAttribute))
{
column.IsIgnore = true;
}
// 如果特性含有字段的[Column]特性，就设置字段SqlSugar的DbColumnName特性
if (attributes.Any(it => it is ColumnAttribute))
{
column.DbColumnName = (attributes.First(it => it is ColumnAttribute) as ColumnAttribute).Name;
}
}
};
}
/// <summary>
/// 记录错误SQL日志 第一次启动失败判断 不写入数据库日志
/// </summary>
/// <param name="exp">SqlSugar异常实体</param>
private static void ExceptionLog(SqlSugarException exp)
{
if (exp.Message.IndexOf("连接数据库过程中发生错误") > -1) return;
StringBuilder sqlString = new StringBuilder();
StringBuilder parameterString = new StringBuilder();
sqlString.Append(exp.Sql);
SugarParameter[] sugarParameters = exp.Parametres as SugarParameter[];
if (sugarParameters != null && sugarParameters.Length > 0)
{
//参数从长到短来循环，这样替换sql中的参数的时候，避免@Parame11被@Parame给替换的情况
foreach (var item in sugarParameters.OrderByDescending(t => t.ParameterName.Length))
{
if (item.DbType.ToString().Contains("String") || item.DbType.ToString().Contains("Date") ||
item.DbType == System.Data.DbType.Guid || item.DbType == System.Data.DbType.Time ||
item.DbType == System.Data.DbType.Object || item.DbType == System.Data.DbType.Xml)
{
sqlString.Replace(item.ParameterName, "'" + item.Value.ToString() + "'");
parameterString.Append(item.ParameterName + " = '" + item.Value + "',");
}
else
{
sqlString.Replace(item.ParameterName, item.Value.ToString());
parameterString.Append(item.ParameterName + " = " + item.Value + ",");
}
}
//删除逗号
parameterString.Remove(parameterString.Length - 1, 1);
}
var errorMsg = $"【SqlSugar】执行SQL语句【{sqlString}】，参数【{parameterString}】,发生错误【{exp.Message}】";
QueueInterLog.GetInstance.EnqueueInterLog(null, null, EnumLog.AOP全局错误.ToString(), errorMsg, 0, user: "AOP", sysTitle: EnumLog.AOP全局错误.ToString());
Log4NetHelper.Instance.Error(errorMsg);
}
/// <summary>
/// 写入数据 封装方法 主子表写入参考 ：MateLoadTemplateService （打包提交默认带有事务：一些自定义的sql或者存储过程一起打包可能会不支持）
/// </summary>
/// <param name="obj">model，或者list，或者 Dictionary </param>
/// <param name="isTransaction">true开启事务</param>
public virtual bool Add(dynamic obj, bool isTransaction = false)
{
if (isTransaction)
{
Context.Insertable(obj).AddQueue();
return Context.SaveQueuesAsync().Result > 0;
}
return Context.Insertable(obj).ExecuteCommand() > 0;
}
/// <summary>
/// Update 封装方法
/// </summary>
public virtual bool Update<M>(M m, Expression<Func<M, bool>> expression) where M : class, new()
{
return Context.Updateable<M>(m).Where(expression).ExecuteCommand() > 0;
}
/// <summary>
/// 删除 封装方法
/// </summary>
public virtual bool Delete<M>(M m, Expression<Func<M, bool>> expression) where M : class, new()
{
return Context.Deleteable(m).Where(expression).ExecuteCommand() > 0;
}
/// <summary>
/// 上传文件,写入附件表sys_File targetTableName,targetId 必填
/// </summary>
public bool UploadFile(IFormFileCollection files, IHostingEnvironment environment, List<sys_File> sysFileModel)
{
if (files.Count != sysFileModel.Count || files.Count == 0) return false;
var upFloder = Path.Combine(environment.ContentRootPath, uploadFolder);
if (!Directory.Exists(upFloder)) Directory.CreateDirectory(upFloder);
for (int i = 0; i < files.Count; i++)
{
if (files[i].Length == 0) continue;
#region 文件保存磁盘
var suffix = Path.GetExtension(files[i].FileName);
var fileName = Path.GetFileName(files[i].FileName);
var newFileName = Guid.NewGuid().ToString("N") + suffix;
var filePath = Path.Combine(upFloder, newFileName);
using (var fs = File.Create(filePath))
{
files[i].CopyTo(fs);
fs.Flush();
}
#endregion
#region 保存文件记录到数据库
var relativePaths = $"/{uploadFolder}/" + newFileName;
var config = ConfigRead.GetInstance.GetAppsetConnection();
var ip = ComputerHelp.GetAddressIP();
var httpsPort = config.HttpPort == 0? config.HttpsPort : config.HttpPort;
sysFileModel[i].host = ip + ":" + httpsPort;
sysFileModel[i].suffix = suffix;
sysFileModel[i].createTime = DateTime.Now;
sysFileModel[i].createBy = sysWebUser == null ? sysUserApi.Account : sysWebUser?.Account;
sysFileModel[i].isDelete = 0;
sysFileModel[i].fileName = fileName;
sysFileModel[i].url = ObjValue + "://" + sysFileModel[i].host + relativePaths;
#endregion
}
Context.Insertable(sysFileModel).IgnoreColumns().AddQueue();
return Context.SaveQueues() > 0;
}
/// <summary>
/// 保存文件
/// </summary>
public void SaveFile(byte[] files, string path)
{
BinaryWriter bw = new BinaryWriter(System.IO.File.Open(path, FileMode.OpenOrCreate));
bw.Write(files);
bw.Close();
}
/// <summary>
/// 获取最大值
/// </summary>
/// <param name="maxField"></param>
/// <returns></returns>
public int GetTableMaxValue<M>(string maxField) where M : class, new()
{
var value = Context.Queryable<M>().Max<int>(maxField);
return value == 0 ? 1 : value + 1;
}
/// <summary>
/// 获取字典 select * from[dbo].[sys_dict_data] where dictType = 'sysCode'
/// </summary>
/// <param name="dictLabel">值</param>
/// <param name="dictType">类型</param>
/// <returns>返回dictValue 字段值</returns>
public virtual string GetDictionaryDictValue(string dictLabel, string dictType = "sysCode")
{
return Context.Queryable<sys_dict_data>().With(SqlWith.NoLock).Where(x => x.dictType == dictType && x.dictLabel == dictLabel).First()?.dictValue;
}
/// <summary>
/// 拼接Order条件
/// </summary>
/// <param name="pageReq">PageReq类型的分页实体</param>
/// <param name="entity">查询对象的实体</param>
/// <returns></returns>
/// <exception cref="Exception"></exception>
public Expression<Func<T, object>> LinqOrder(PageReq pageReq, T entity)
{
try
{
string propertyName = "id";
if (pageReq != null && pageReq.field != null)
{
PropertyInfo propertyInfo = entity.GetType().GetProperty(pageReq.field);
if (propertyInfo != null)
{
propertyName = pageReq.field;
}
}
var memberExpression = Expression.PropertyOrField(Expression.Constant(entity), propertyName);
var expression = Expression.Lambda<Func<T, object>>(memberExpression);
return expression;
}
catch (Exception ex)
{
throw new Exception($"{ex.Message}");
}
}
/// <summary>
/// 执行SqlSugar的队列 封装方法（取代打包的执行方法，因为打包的执行不能超过200个变量，很容易超出）
/// </summary>
public int ExecuteQueues(ISqlSugarClient context)
{
int count = 0;
try
{
if (context.Queues.Count == 0)
{
return count;
}
#region 将context.Queues中的sql语句和参数，一条一条取出来，然后再拼接成大SQL，减少发送次数。
//StringBuilder sb = new StringBuilder();
//List<SugarParameter> paramList = new List<SugarParameter>();
//List<Tuple<string, List<SugarParameter>>> sugarCommandList = new List<Tuple<string, List<SugarParameter>>>();
//for (var i = 0; i < context.Queues.Count; i++)
//{
// int sqlStringCount = sb.Length + context.Queues[i].Sql.Length;
// int paramCount = paramList.Count + context.Queues[i].Parameters.Length;
// //SQL Server数据库的SQL语句，参数不能多于2000个，字符几乎无限就暂定为200万字(4M的大小)
// if (sqlStringCount > 2 * 1024 * 1024 || paramCount > 1999)
// {
// //超出了一条SQL语句的最大范围，就把SQL放到sugarCommandList中，然后再新实例化新的对象
// sugarCommandList.Add(new Tuple<string, List<SugarParameter>>(sb.ToString(), paramList));
// sb = new StringBuilder();
// paramList = new List<SugarParameter>();
// }
// var row = i + 1;
// string sqlString = context.Queues[i].Sql;
// // 先排序是因为变量有id, id0等，要把字符短的在前，长的放在后面，不然先替换id0，再替换id，就会产生多次替换
// SugarParameter[] sugarParameters = context.Queues[i].Parameters;
// foreach (var item in sugarParameters)
// {
// //因为传入的有List类型，所以每个变量名都是一样的，拼接后改为"变量_#$行号"，这样每个变量名就唯一了。
// string ParameterNewName = item.ParameterName + "_#$" + row;
// //将 （"\b"表示非字符，包括空格，换行等）
// sqlString = sqlString.Replace(item.ParameterName + "\b" , ParameterNewName);
// item.ParameterName = ParameterNewName;
// }
// //将替换好的SQL和参数存放起来
// sb.AppendLine(sqlString);
// paramList.AddRange(sugarParameters);
// if (i == context.Queues.Count - 1)
// {
// sugarCommandList.Add(new Tuple<string, List<SugarParameter>>(sb.ToString(), paramList));
// }
//}
// 从sugarCommandList中取出拼接好的SQL，来执行。
//context.Ado.BeginTran();
//foreach (var item in sugarCommandList)
//{
// if (!string.IsNullOrWhiteSpace(item.Item1))
// {
// count += context.Ado.ExecuteCommand(item.Item1, item.Item2);
// }
//}
//context.Ado.CommitTran();
#endregion
context.Ado.BeginTran();
foreach (var item in context.Queues)
{
if (!string.IsNullOrWhiteSpace(item.Sql))
{
count += context.Ado.ExecuteCommand(item.Sql, item.Parameters);
}
}
context.Ado.CommitTran();
}
catch (Exception ex)
{
context.Ado.RollbackTran();
throw ex;
}
finally
{
context.Queues.Clear();
}
return count;
}
/// <summary>
/// 多个上下文调用次方法
/// 执行SqlSugar的队列 封装方法
/// </summary>
public int ExecuteQueues(List<ISqlSugarClient> contextList)
{
int count = 0;
try
{
if (contextList.Count == 0)
{
return count;
}
if (!contextList.Exists(t => t.Queues.Count > 0))
{
return count;
}
base.Context.Ado.BeginTran();
foreach (var context in contextList)
{
foreach (var item in context.Queues)
{
if (!string.IsNullOrWhiteSpace(item.Sql))
{
count += base.Context.Ado.ExecuteCommand(item.Sql, item.Parameters);
}
}
}
//base.Context.Ado.RollbackTran();
base.Context.Ado.CommitTran();
}
catch (Exception ex)
{
base.Context.Ado.RollbackTran();
throw ex;
}
finally
{
contextList.ForEach(x =>
{
x.Queues.Clear();
});
}
return count;
}
/// <summary>
/// 清空当前用户的redis授权缓存，这样用的时候就会自动从数据库更新一次
/// </summary>
public void ClearCurrentUserRedis()
{
var account = sysWebUser.Account;
var cl = new RedisBase();
if (cl.redisClient.Exists(account))
{
cl.redisClient.Del(account);
}
}
/// <summary>
/// 清空所有登录用户的redis授权缓存，这样用的时候就会自动从数据库更新一次
/// </summary>
public void ClearOnlineUserRedis()
{
//清空被删除用户的Redis缓存
var userOnlineList = Context.Queryable<sys_user_online>().Where(t => true).ToList();
var cl = new RedisBase();
foreach (var item in userOnlineList)
{
if (cl.redisClient.Exists(item.account))
{
cl.redisClient.Del(item.account);
}
}
}
/// <summary>
/// 根据ip找工位
/// </summary>
/// <param name="ip"></param>
/// <returns></returns>
public Response<base_work_station> GetStationByIP(string ip)
{
var result = new Response<base_work_station>();
if (ip == "127.0.0.1")
{
result.Status = false;
result.Code = 500;
result.Message = "ip【127.0.0.1】请检查本地ip,确认是否连接网络,请于工位中设置Ip地址！";
return result;
}
var stations = Context.Queryable<base_work_station>().Where(u => u.monitorIP == ip).First();
if (stations == null || string.IsNullOrEmpty(stations.lineCode))
{
result.Status = false;
result.Code = 500;
result.Message = $"根据ip【{ip}】找到对应工位和线体失败，请检查工位信息ip是否配置成功,请于工位中设置Ip地址！";
return result;
};
result.Status = true;
result.Code = 200;
result.Result = stations;
return result;
}
/// <summary>
/// http
/// </summary>
public Response HttpMan(string url, dynamic requestData, string title , string method = "post", string contentType = "application/json",string token=null)
{
var stopwatch = new Stopwatch();
stopwatch.Start();
var response = new Response(false);
var httpItem = new HttpItem
{
URL = url,
Method = method,
ContentType = contentType,
Postdata = JsonConvert.SerializeObject(requestData),
Timeout = 80000
};
if(!string.IsNullOrEmpty(token))
{
httpItem.Header.Add("Authorization", token);
}
var httpHelper = new HttpHelper();
var httpResult = httpHelper.GetHtml(httpItem);
response.Result = httpResult.Html;
stopwatch.Stop();
var resultMsg = title + ",失败原因：" + httpResult.Html;
var user = sysUserApi == null ? (sysWebUser == null ? "定时器" : sysWebUser?.Account) : sysUserApi.Account;
QueueInterLog.GetInstance.EnqueueInterLog(httpItem, httpResult, title, resultMsg, stopwatch.Elapsed.TotalMilliseconds, user: user, sysTitle: title);
if (httpResult.StatusCode == HttpStatusCode.OK)
{
//根据实际情况处理
var resultTemp = httpResult.Html.TrimStart('[').TrimEnd(']');
dynamic result = DynamicJson.Parse(resultTemp);
if (result == null)
{
QueueInterLog.GetInstance.EnqueueInterLog(httpItem, httpResult, title, "异常错误:" + resultMsg, stopwatch.Elapsed.TotalMilliseconds, user: user, sysTitle: title);
return response.ResponseError(resultMsg);
}
//请求返回成功
if (result.code == "0") return response.ResponseSuccess("请求返回成功");
}
if (httpResult.StatusCode == HttpStatusCode.InternalServerError)
{
response.Message = resultMsg;
QueueInterLog.GetInstance.EnqueueInterLog(httpItem, httpResult, title, "异常错误:"+ response.Message, stopwatch.Elapsed.TotalMilliseconds, user: user, sysTitle: title);
return response;
}
if (httpResult.StatusCode == HttpStatusCode.NotFound)
{
response.Message = "url:" + httpItem.URL + "错误、请求地址未找到404" + resultMsg;
QueueInterLog.GetInstance.EnqueueInterLog(httpItem, httpResult, title, "异常错误:" + response.Message, stopwatch.Elapsed.TotalMilliseconds, user: user, sysTitle: title);
return response;
}
QueueInterLog.GetInstance.EnqueueInterLog(httpItem, httpResult, title, "异常错误:" + resultMsg, stopwatch.Elapsed.TotalMilliseconds, user: user, sysTitle: title);
response.Message = resultMsg;
return response;
}
}
}