using System;
|
using System.Collections.Generic;
|
using System.ComponentModel;
|
using System.Configuration;
|
using System.Data;
|
using System.Data.SqlClient;
|
using System.Linq;
|
using System.Text;
|
|
namespace M423project
|
{
|
|
public class DownTimeData : IDisposable
|
{
|
private SqlConnection connection;
|
|
public DownTimeData()
|
{
|
string connStr = ConfigurationManager.AppSettings["connectionString"];
|
|
connection = new SqlConnection(connStr);
|
try
|
{
|
connection.Open();
|
}
|
catch (Exception)
|
{
|
CommonUtil.WriteLog(LogType.Err, "连接数据库失败！");
|
}
|
}
|
|
public void Dispose()
|
{
|
connection.Close();
|
}
|
|
public void SaveDownTime(DownTime downTime)
|
{
|
try
|
{
|
StringBuilder sb = new StringBuilder();
|
if (downTime.EndTime != null)
|
{
|
sb.AppendFormat(@"insert into DownTime
|
(BeginTime,EndTime,Type)
|
values('{0}','{1}','{2}')", downTime.BeginTime.Value.ToString("yyyy/MM/dd HH:mm:ss"), downTime.EndTime.Value.ToString("yyyy/MM/dd HH:mm:ss"), (int)downTime.Type);
|
}
|
else
|
{
|
sb.AppendFormat(@"insert into DownTime
|
(BeginTime,EndTime,Type)
|
values('{0}',null,'{1}')", downTime.BeginTime.Value.ToString("yyyy/MM/dd HH:mm:ss"), (int)downTime.Type);
|
}
|
using (SqlCommand cmd = new SqlCommand(sb.ToString(), connection))
|
{
|
cmd.ExecuteNonQuery();
|
}
|
}
|
catch (Exception ex)
|
{
|
CommonUtil.WriteLog(LogType.Err, ex.ToString());
|
}
|
}
|
|
public void UpdateDownTime(DownTime downTime)
|
{
|
try
|
{
|
StringBuilder sb = new StringBuilder();
|
sb.AppendFormat(@"update DownTime
|
set EndTime='{0}'
|
where ID=(select top 1 ID from DownTime where Type={1} and EndTime is null order by BeginTime desc)", downTime.EndTime.Value.ToString("yyyy/MM/dd HH:mm:ss"), (int)downTime.Type);
|
using (SqlCommand cmd = new SqlCommand(sb.ToString(), connection))
|
{
|
cmd.ExecuteNonQuery();
|
}
|
}
|
catch (Exception ex)
|
{
|
CommonUtil.WriteLog(LogType.Err, ex.ToString());
|
}
|
}
|
|
public void CleanData()
|
{
|
StringBuilder sb = new StringBuilder();
|
sb.AppendFormat(@"delete from DownTime
|
where DATEDIFF(DAY,BeginTime,GETDATE())>40");
|
using (SqlCommand cmd = new SqlCommand(sb.ToString(), connection))
|
{
|
cmd.ExecuteNonQuery();
|
}
|
|
}
|
|
public DataTable GetDownTimeList(int type, DateTime beginTime, DateTime endTime)
|
{
|
DataTable dt = new DataTable();
|
try
|
{
|
StringBuilder sb = new StringBuilder();
|
sb.AppendFormat(@"SELECT *,TimeNeedSec/60.00 TimeNeedMin FROM
|
(SELECT ROW_NUMBER() over(order by ID) Row,BeginTime,EndTime,DateDiff(SECOND,BeginTime,EndTime) TimeNeedSec,case when Type=1 then N'报警' when Type=2 then N'软件关闭' when Type=3 then N'上下料不及时' End TypeTxt
|
FROM DownTime WHERE EndTime IS NOT NULL
|
AND BeginTime Between '{0}' and '{1}'", beginTime.ToString("yyyy/MM/dd HH:mm:ss"), endTime.ToString("yyyy/MM/dd HH:mm:ss"));
|
|
if (type != 0)
|
{
|
sb.AppendFormat(@" AND Type={0}", type);
|
}
|
else
|
{
|
sb.AppendFormat(@" AND Type IN ({0},{1})", (int)DownTimeType.Warning, (int)DownTimeType.Closed);
|
}
|
sb.AppendFormat(") T");
|
|
SqlDataReader dr = null;
|
using (SqlCommand cmd = new SqlCommand(sb.ToString(), connection))
|
{
|
dr = cmd.ExecuteReader();
|
dt.Load(dr);
|
}
|
}
|
catch (Exception ex)
|
{
|
CommonUtil.WriteLog(LogType.Err, ex.ToString());
|
}
|
return dt;
|
}
|
|
public List<EnumEntity> GetEnumList(Type enumType)
|
{
|
var pairs = new List<EnumEntity>();
|
var values = Enum.GetValues(enumType);
|
foreach (var value in values)
|
{
|
var name = Enum.GetName(enumType, value);
|
var field = enumType.GetField(name);
|
var attribute = Attribute.GetCustomAttribute(field, typeof(DescriptionAttribute)) as DescriptionAttribute;
|
pairs.Add(new EnumEntity()
|
{
|
Name = name,
|
Value = Convert.ToInt32(value),
|
Description = attribute == null ? null : attribute.Description
|
});
|
}
|
|
return pairs;
|
}
|
}
|
|
public class DownTime
|
{
|
public int ID { get; set; }
|
|
public DateTime? BeginTime { get; set; }
|
|
public DateTime? EndTime { get; set; }
|
|
public DownTimeType Type { get; set; }
|
}
|
|
public enum DownTimeType
|
{
|
/// <summary>
|
/// 报警
|
/// </summary>
|
[Description("报警")]
|
Warning = 1,
|
/// <summary>
|
/// 软件关闭
|
/// </summary>
|
[Description("软件关闭")]
|
Closed,
|
/// <summary>
|
/// 上料、下料、NG料盘清空的等待
|
/// </summary>
|
[Description("上下料不及时")]
|
NoAction
|
}
|
}
|
