public static bool GetHaveSame(int wId, WarningType warningType, WarningDataType dataType, IEnumerable <string> sames, IEnumerable <int> ids)
{
var args = new List <Tuple <string, string, dynamic> >
{
new Tuple <string, string, dynamic>("WorkshopId", "=", wId),
new Tuple <string, string, dynamic>("WarningType", "=", warningType),
new Tuple <string, string, dynamic>("DataType", "=", dataType),
new Tuple <string, string, dynamic>("Name", "IN", sames),
new Tuple <string, string, dynamic>("Id", "NOT IN", ids)
};
return(Instance.CommonHaveSame(args));
}
/// <summary>
///
/// </summary>
/// <param name="workshopId"></param>
/// <param name="startTime"></param>
/// <param name="endTime"></param>
/// <param name="setId"></param>
/// <param name="itemId"></param>
/// <param name="warningType"></param>
/// <param name="dataType"></param>
/// <param name="deviceIds"></param>
/// <param name="itemTypes"></param>
/// <param name="isWarning">-1all 0 1 </param>
/// <param name="setIds"></param>
/// <param name="itemIds"></param>
/// <returns></returns>
public static IEnumerable <WarningLog> GetWarningLogs(int workshopId, DateTime startTime, DateTime endTime, int setId, int itemId,
WarningType warningType, WarningDataType dataType, IEnumerable <int> deviceIds, IEnumerable <WarningItemType> itemTypes, IEnumerable <int> setIds, IEnumerable <int> itemIds, int isWarning = -1)
{
var param = new List <string> {
"a.WorkshopId = @workshopId"
};
if (startTime != default(DateTime))
{
param.Add("a.WarningTime >= @startTime");
}
if (endTime != default(DateTime))
{
param.Add("a.WarningTime <= @endTime");
}
if (setId != 0)
{
param.Add("a.SetId = @setId");
}
if (setIds != null && setIds.Any())
{
param.Add("a.SetId IN @setIds");
}
if (itemId != 0)
{
param.Add("a.ItemId = @itemId");
}
if (warningType != WarningType.默认)
{
param.Add("a.WarningType = @warningType");
}
if (dataType != WarningDataType.默认)
{
param.Add("a.DataType = @dataType");
}
if (deviceIds != null && deviceIds.Any())
{
param.Add("a.DeviceId IN @deviceIds");
}
if (isWarning != -1)
{
param.Add("a.IsWarning = @isWarning");
}
if (itemTypes != null && itemTypes.Any())
{
param.Add("b.ItemType IN @itemTypes");
//var args = new List<Tuple<string, string, dynamic>>
//{
// new Tuple<string, string, dynamic>("ItemType", "IN", itemTypes)
//};
//if (setId != 0)
//{
// args.Add(new Tuple<string, string, dynamic>("SetId", "=", setId));
//}
//var tItemIds = WarningSetItemHelper.Instance.CommonGet<WarningSetItem>(args).Select(x => x.Id);
//if (!tItemIds.Any())
//{
// return new List<WarningLog>();
//}
//param.Add("a.ItemId IN @itemIds");
//if (itemIds.Any())
//{
// itemIds = tItemIds.Intersect(itemIds);
//}
}
if (itemIds != null && itemIds.Any())
{
param.Add("a.ItemId IN @itemIds");
}
var r = ServerConfig.ApiDb.Query <WarningLog>(
$"SELECT a.*, b.ItemType FROM `warning_log` a " +
$"JOIN warning_set_item_final b ON a.ItemId = b.Id " +
$"{(param.Any() ? $" WHERE {param.Join(" AND ")}" : "")} ORDER BY a.WarningTime DESC;",
new { workshopId, startTime, endTime, setId, itemId, warningType, dataType, deviceIds, setIds, itemIds, itemTypes, isWarning });
if (r != null && r.Any())
{
var sets = WarningSetHelper.GetMenus(workshopId, r.Select(x => x.SetId).Distinct()).ToDictionary(x => x.Id);
var devices = DeviceHelper.GetMenus(workshopId, r.Select(x => x.DeviceId).Distinct()).ToDictionary(x => x.Id);
var categories = DeviceCategoryHelper.GetMenus(workshopId, r.Select(x => x.CategoryId).Distinct()).ToDictionary(x => x.Id);
foreach (var d in r)
{
//var d = ClassExtension.ParentCopyToChild<WarningCurrent, WarningLog>(current);
d.SetName = sets.ContainsKey(d.SetId) ? sets[d.SetId].Name : "";
d.Code = devices.ContainsKey(d.DeviceId) ? devices[d.DeviceId].Code : "";
//d.Class = classes.FirstOrDefault(x => x.Id == d.ClassId)?.Class ?? "";
d.CategoryName = categories.ContainsKey(d.CategoryId) ? categories[d.CategoryId].Name : "";
}
}
return(r);
}
public static IEnumerable <WarningClearDetail> GetWarningClears(int wId, DateTime startTime, DateTime endTime, int setId,
WarningType warningType, WarningDataType dataType, IEnumerable <int> deviceIds)
{
var clears = new List <WarningClearDetail>();
var param = new List <string> {
"DealTime >= @startTime", "DealTime <= @endTime"
};
if (wId != 0)
{
param.Add("WorkshopId = @wId");
}
if (warningType != WarningType.默认)
{
param.Add("b.WarningType = @warningType");
}
if (dataType != WarningDataType.默认)
{
param.Add("b.DataType = @dataType");
}
if (setId != 0)
{
param.Add("SetId = @setId");
}
if (deviceIds != null && deviceIds.Any())
{
var t = new List <WarningClearDetail>();
param.Add("FIND_IN_SET(@deviceId, a.DeviceIds)");
foreach (var deviceId in deviceIds)
{
t.AddRange(ServerConfig.ApiDb.Query <WarningClearDetail>(
$"SELECT a.* FROM `warning_clear` a " +
$"JOIN `warning_set` b ON a.SetId = b.Id {(param.Any() ? $" WHERE {param.Join(" AND ")}" : "")} ORDER BY DealTime DESC;",
new { startTime, endTime, setId, deviceId }));
}
clears.AddRange(t.GroupBy(x => x.SetId).Select(x => x.First()));
}
else
{
clears.AddRange(ServerConfig.ApiDb.Query <WarningClearDetail>(
$"SELECT a.* FROM `warning_clear` a " +
$"JOIN `warning_set` b ON a.SetId = b.Id {(param.Any() ? $" WHERE {param.Join(" AND ")}" : "")} ORDER BY DealTime DESC;",
new { startTime, endTime, setId, warningType, dataType }));
}
if (clears.Any())
{
var sets = WarningSetHelper.GetMenus(wId, clears.Select(x => x.SetId).Distinct()).ToDictionary(x => x.Id);
var devices = DeviceHelper.GetMenus(wId, clears.SelectMany(x => x.DeviceIdList).Distinct()).ToDictionary(x => x.Id);
var createUserIds = AccountInfoHelper.GetAccountInfoByAccounts(clears.Select(x => x.CreateUserId).Distinct()).ToDictionary(x => x.Account);
foreach (var d in clears)
{
d.Name = createUserIds.ContainsKey(d.CreateUserId) ? createUserIds[d.CreateUserId].Name : "";
d.SetName = sets.ContainsKey(d.SetId) ? sets[d.SetId].Name : "";
d.DeviceList.AddRange(d.DeviceIdList.Select(x => devices.ContainsKey(x) ? devices[x].Code : x.ToString()));
}
}
return(clears);
}
public DataResult GetWarningDealLog([FromQuery] int sId, DateTime startTime, DateTime endTime, WarningType type, WarningDataType dataType, string deviceIds = "")
{
var result = new DataResult();
if (sId != 0)
{
var set = WarningSetHelper.Instance.Get <WarningSet>(sId);
if (set == null)
{
return(Result.GenError <DataResult>(Error.WarningSetNotExist));
}
if (set.WarningType != WarningType.设备 || set.DataType != WarningDataType.设备数据)
{
return(Result.GenError <DataResult>(Error.WarningSetItemDataTypeError));
}
}
var markedDateTime = DateTime.Now;
if (startTime == default(DateTime))
{
startTime = markedDateTime;
}
if (endTime == default(DateTime))
{
endTime = markedDateTime;
}
startTime = startTime.DayBeginTime();
endTime = endTime.DayEndTime();
var deviceIdList = deviceIds.IsNullOrEmpty() ? null : deviceIds.Split(",").Select(x => int.TryParse(x, out var a) ? a : 0).Where(y => y != 0).ToList();
result.datas.AddRange(WarningClearHelper.GetWarningClears(1, startTime, endTime, sId, type, dataType, deviceIdList));
return(result);
}
public DataResult GetWarningLog([FromQuery] int sId, DateTime startTime, DateTime endTime, WarningType type, WarningDataType dataType, string deviceIds = "")
{
var result = new DataResult();
IEnumerable <int> deviceIdList = null;
if (sId != 0)
{
var set = WarningSetHelper.Instance.Get <WarningSet>(sId);
if (set == null)
{
return(Result.GenError <DataResult>(Error.WarningSetNotExist));
}
deviceIdList = deviceIds.IsNullOrEmpty() ? null : deviceIds.Split(",").Select(x => int.TryParse(x, out var a) ? a : 0).Where(y => y != 0).ToList();
}
if (startTime != default(DateTime))
{
startTime = startTime.DayBeginTime();
}
if (endTime != default(DateTime))
{
endTime = endTime.DayEndTime();
}
result.datas.AddRange(WarningLogHelper.GetWarningLogs(1, startTime, endTime, sId, 0, type, dataType, deviceIdList, null, null, null, 1));
return(result);
}
public DataResult GetCurrentWarning([FromQuery] int wId, int sId, WarningType type, WarningDataType dataType)
{
wId = wId == 0 ? 1 : wId;
var result = new DataResult();
if (HWarningHelper.CurrentData != null && HWarningHelper.CurrentData.Any())
{
var r = sId == 0 ? HWarningHelper.CurrentData.Where(x => x.Key.Item3 == dataType && x.Key.Item4 == type).Select(y => y.Value)
: HWarningHelper.CurrentData.Where(x => x.Key.Item3 == dataType && x.Key.Item4 == type).Where(y => y.Value.SetId == sId).Select(z => z.Value);
if (r.Any())
{
var sets = WarningSetHelper.GetMenus(wId, r.Select(x => x.SetId));
var devices = DeviceHelper.GetMenus(wId, r.Select(x => x.DeviceId));
var categories = DeviceCategoryHelper.GetMenus(wId, r.Select(x => x.CategoryId));
//var classes = ServerConfig.ApiDb.Query<DeviceClass>("SELECT `Id`, `Class` FROM `device_class` WHERE `MarkedDelete` = 0 AND Id IN @Id;", new
//{
// Id = data.Select(x => x.ClassId)
//});
foreach (var current in r.OrderBy(x => x.ItemId).ThenBy(x => x.DeviceId))
{
var d = ClassExtension.ParentCopyToChild <WarningCurrent, WarningCurrentDetail>(current);
d.SetName = sets.FirstOrDefault(x => x.Id == d.SetId)?.Name ?? "";
d.Code = devices.FirstOrDefault(x => x.Id == d.DeviceId)?.Code ?? "";
//d.Class = classes.FirstOrDefault(x => x.Id == d.ClassId)?.Class ?? "";
d.CategoryName = categories.FirstOrDefault(x => x.Id == d.CategoryId)?.Name ?? "";
result.datas.Add(d);
}
}
}
return(result);
}
public DataResult GetSet([FromQuery] int wId, int qId, WarningType type, WarningDataType dataType, bool first = false)
{
wId = wId == 0 ? 1 : wId;
var result = new DataResult();
var r = ServerConfig.ApiDb.Query <WarningSetWithItems>(
$"SELECT a.*, IFNULL(b.`Class`, '') `Class`, IFNULL(c.`StepName`, '') `StepName` FROM `warning_set` a " +
$"LEFT JOIN `device_class` b ON a.ClassId = b.Id " +
$"LEFT JOIN `flow_step` c ON a.StepId = c.Id " +
$"WHERE a.`MarkedDelete` = 0 AND a.`WarningType` = @type AND a.`DataType` = @dataType{(qId == 0 ? "" : " AND a.Id = @qId")};", new { qId, type, dataType });
var d = new List <WarningSetItemDetail>();
if (first && r.Any())
{
var setId = r.FirstOrDefault().Id;
var sql = "";
List <WarningSetItemDetail> s;
switch (dataType)
{
case WarningDataType.设备数据:
var scriptId = r.FirstNonDefault().ScriptId;
sql = "SELECT b.*, a.VariableName Item, a.PointerAddress, a.VariableTypeId, a.Id DictionaryId " +
"FROM (SELECT * FROM `data_name_dictionary` WHERE ScriptId = @scriptId AND MarkedDelete = 0) a " +
"LEFT JOIN (SELECT * FROM `warning_set_item` WHERE SetId = @setId AND MarkedDelete = 0) b " +
"ON a.Id = b.DictionaryId ORDER BY a.VariableTypeId, a.PointerAddress;";
d.AddRange(ServerConfig.ApiDb.Query <WarningSetItemDetail>(sql, new { setId, scriptId }));
break;
case WarningDataType.生产数据:
sql = "SELECT * FROM `warning_set_item` WHERE MarkedDelete = 0 AND SetId = @setId;";
s = ServerConfig.ApiDb.Query <WarningSetItemDetail>(sql, new { setId }).ToList();
foreach (var val in HWarningHelper.生产数据字段汇总)
{
if (s.All(x => x.ItemType != val.Item2))
{
d.Add(new WarningSetItemDetail
{
Item = val.Item1,
ItemType = val.Item2,
ValueType = HWarningHelper.GetItemValueType(val.Item2)
});
}
else
{
var dd = s.First(x => x.Item == val.Item1);
dd.ValueType = HWarningHelper.GetItemValueType(val.Item2);
d.Add(dd);
}
d = d.OrderBy(x => HWarningHelper.生产数据字段汇总.FirstOrDefault(y => y.Item2 == x.ItemType)?.Item2).ToList();
}
break;
case WarningDataType.故障数据:
//sql = "SELECT * FROM `warning_set_item` WHERE MarkedDelete = 0 AND SetId = @setId;";
break;
case WarningDataType.流程卡数据:
sql = "SELECT * FROM `warning_set_item` WHERE MarkedDelete = 0 AND SetId = @setId;";
s = ServerConfig.ApiDb.Query <WarningSetItemDetail>(sql, new { setId }).ToList();
foreach (var val in HWarningHelper.流程卡数据字段汇总)
{
if (s.All(x => x.ItemType != val.Item2))
{
s.Add(new WarningSetItemDetail
{
Item = val.Item1,
ItemType = val.Item2,
ValueType = HWarningHelper.GetItemValueType(val.Item2),
IsRepeat = val.Item3,
IsConfig = true
});
}
else
{
foreach (var dd in s.Where(x => x.ItemType == val.Item2))
{
dd.Item = val.Item1;
dd.ValueType = HWarningHelper.GetItemValueType(val.Item2);
dd.IsRepeat = val.Item3;
}
}
}
s = s.OrderBy(x => x.IsRepeat).ThenBy(x => x.ItemType).ThenBy(x => x.Id).ToList();
d.AddRange(s);
break;
}
if (!sql.IsNullOrEmpty())
{
r.FirstOrDefault().Items.AddRange(d);
}
}
if (r.Any())
{
var device = DeviceHelper.GetMenus(wId, r.SelectMany(x => x.DeviceList).Distinct()).OrderBy(x => x.Id);
var categories = DeviceCategoryHelper.GetMenus(wId, r.Select(x => x.CategoryId).Distinct()).OrderBy(x => x.Id);
var script = ScriptVersionHelper.GetMenus(wId, r.Select(x => x.ScriptId).Distinct()).OrderBy(x => x.Id);
foreach (var dd in r)
{
dd.CodeList = device.Where(x => dd.DeviceList.Contains(x.Id)).Select(y => new Tuple <int, string>(y.Id, y.Code));
dd.Script = script.FirstOrDefault(x => x.Id == dd.ScriptId)?.Name ?? "";
dd.CategoryName = categories.FirstOrDefault(x => x.Id == dd.CategoryId)?.Name ?? "";
}
}
if (qId != 0 && !r.Any())
{
return(Result.GenError <DataResult>(Error.WarningSetNotExist));
}
result.datas.AddRange(r);
return(result);
}
public DataResult GetSetItem([FromQuery] int sId, WarningDataType dataType, bool isNew = false)
{
var result = new DataResult();
WarningSetWithItems set = null;
if (!isNew)
{
set = WarningSetHelper.Instance.Get <WarningSetWithItems>(sId);
if (set == null)
{
return(Result.GenError <DataResult>(Error.WarningSetNotExist));
}
}
var scriptId = -1;
var sql = "";
switch (dataType)
{
case WarningDataType.设备数据:
sql = "SELECT ScriptId, VariableName Item, PointerAddress, VariableTypeId, Id DictionaryId FROM `data_name_dictionary` WHERE ScriptId = @sId AND MarkedDelete = 0 ORDER BY VariableTypeId, PointerAddress;";
if (!isNew)
{
scriptId = set.ScriptId;
sql = "SELECT a.VariableName Item, a.PointerAddress, a.VariableTypeId, b.*, a.Id DictionaryId " +
"FROM (SELECT * FROM `data_name_dictionary` WHERE ScriptId = @scriptId AND MarkedDelete = 0) a " +
"LEFT JOIN (SELECT * FROM `warning_set_item` WHERE SetId = @setId AND MarkedDelete = 0) b " +
"ON a.Id = b.DictionaryId ORDER BY a.VariableTypeId, a.PointerAddress;";
}
result.datas.AddRange(ServerConfig.ApiDb.Query <WarningSetItemDetail>(sql, new { sId, scriptId }));
break;
case WarningDataType.生产数据:
if (isNew)
{
result.datas.AddRange(HWarningHelper.生产数据字段汇总.Select(x => new WarningSetItem
{
Item = x.Item1,
ItemType = x.Item2
}));
}
else
{
var d = WarningSetItemHelper.GetWarningSetItemsBySetId(sId).ToList();
foreach (var val in HWarningHelper.生产数据字段汇总)
{
if (d.All(x => x.Item != val.Item1))
{
result.datas.Add(new WarningSetItem
{
Item = val.Item1,
ItemType = val.Item2
});
}
else
{
result.datas.Add(d.First(x => x.Item == val.Item1));
}
}
}
break;
case WarningDataType.故障数据:
//sql = "SELECT * FROM `warning_set_item` WHERE MarkedDelete = 0 AND SetId = @setId;";
break;
case WarningDataType.流程卡数据:
if (isNew)
{
result.datas.AddRange(HWarningHelper.流程卡数据字段汇总.Select(x => new WarningSetItem
{
Item = x.Item1,
ItemType = x.Item2,
IsRepeat = x.Item3,
IsConfig = true
}));
}
else
{
var d = WarningSetItemHelper.GetWarningSetItemsBySetId(sId).ToList();
foreach (var val in HWarningHelper.流程卡数据字段汇总)
{
if (d.All(x => x.Item != val.Item1))
{
d.Add(new WarningSetItem
{
Item = val.Item1,
ItemType = val.Item2,
IsRepeat = val.Item3,
IsConfig = true
});
}
else
{
foreach (var dd in d.Where(x => x.ItemType == val.Item2))
{
dd.Item = val.Item1;
dd.IsRepeat = val.Item3;
}
}
}
d = d.OrderBy(x => x.IsRepeat).ThenBy(x => x.ItemType).ThenBy(x => x.Id).ToList();
result.datas.AddRange(d);
}
break;
}
return(result);
}
