using System;
|
using System.Collections.Generic;
|
using System.Configuration;
|
using System.Data;
|
using System.Data.SqlClient;
|
using System.IO;
|
using System.Linq;
|
using System.Text;
|
|
namespace M423project
|
{
|
public class SpotCheckData
|
{
|
private SqlConnection connection;
|
|
public SpotCheckData()
|
{
|
string connStr = ConfigurationManager.AppSettings["connectionString"];
|
connection = new SqlConnection(connStr);
|
try
|
{
|
connection.Open();
|
}
|
catch (Exception)
|
{
|
CommonUtil.WriteLog(LogType.Err, "连接数据库失败！");
|
}
|
}
|
|
public DataTable GetTodayFinishedSpot()
|
{
|
DataTable dt = new DataTable();
|
string sql = @"select * from SpotCheckDetail
|
where DATEDIFF(DAY, PickTime, GETDATE())= 0";
|
SqlDataReader dr = null;
|
using (SqlCommand cmd = new SqlCommand(sql, connection))
|
{
|
try
|
{
|
dr = cmd.ExecuteReader();
|
dt.Load(dr);
|
}
|
catch (Exception e)
|
{
|
CommonUtil.WriteLog(LogType.Inf, string.Format("获取点检数据数据时出现异常：{0}", e.Message));
|
}
|
finally
|
{
|
if (dr != null)
|
dr.Close();
|
}
|
}
|
return dt;
|
}
|
|
public DataTable GetTodayCheckPlan()
|
{
|
DataTable dt = new DataTable();
|
string sql = @"select * from SpotCheck
|
where DATEDIFF(DAY,BeginTime,GETDATE())=0";
|
SqlDataReader dr = null;
|
using (SqlCommand cmd = new SqlCommand(sql, connection))
|
{
|
try
|
{
|
dr = cmd.ExecuteReader();
|
dt.Load(dr);
|
}
|
catch (Exception e)
|
{
|
CommonUtil.WriteLog(LogType.Inf, string.Format("获取执行中的点检计划数据时出现异常：{0}", e.Message));
|
}
|
finally
|
{
|
if (dr != null)
|
dr.Close();
|
}
|
}
|
return dt;
|
}
|
|
public bool IsSpotCheck()
|
{
|
try
|
{
|
int count = 0;
|
StringBuilder sb = new StringBuilder();
|
sb.AppendFormat(@"select COUNT(ID) from SpotCheck
|
where DATEDIFF(DAY,BeginTime,GETDATE())=0");
|
using (SqlCommand cmd = new SqlCommand(sb.ToString(), connection))
|
{
|
count = Convert.ToInt32(cmd.ExecuteScalar());
|
}
|
return count > 0;
|
}
|
catch (Exception ex)
|
{
|
return false;
|
}
|
}
|
|
public DataTable GetTodaySpotCheck()
|
{
|
DataTable dt = new DataTable();
|
StringBuilder sb = new StringBuilder();
|
sb.AppendFormat(@"select * from SpotCheck
|
where DATEDIFF(DAY,BeginTime,GETDATE())=0");
|
SqlDataReader dr = null;
|
using (SqlCommand cmd = new SqlCommand(sb.ToString(), connection))
|
{
|
try
|
{
|
dr = cmd.ExecuteReader();
|
dt.Load(dr);
|
}
|
catch (Exception e)
|
{
|
CommonUtil.WriteLog(LogType.Inf, string.Format("获取抽檢数据时出现异常：{0}", e.Message));
|
}
|
finally
|
{
|
if (dr != null)
|
dr.Close();
|
}
|
}
|
return dt;
|
}
|
|
public void SaveSpotCheck(SpotCheck spotCheck)
|
{
|
try
|
{
|
StringBuilder sb = new StringBuilder();
|
sb.AppendFormat(@"insert into SpotCheck
|
(BeginTime,TotalQty,FinishedQty,PickCount,LastBreakProductQty,LastProductQty)
|
values(GETDATE(),{0},{1},{2},{3},{4})", spotCheck.TotalQty, spotCheck.FinishedQty, spotCheck.PickCount,spotCheck.LastBreakProductQty,spotCheck.LastProductQty);
|
using (SqlCommand cmd = new SqlCommand(sb.ToString(), connection))
|
{
|
cmd.ExecuteNonQuery();
|
}
|
}
|
catch (Exception ex)
|
{
|
CommonUtil.WriteLog(LogType.Err, ex.ToString());
|
}
|
}
|
|
public void SaveSpotCheckDetail(SpotCheckDetail spotCheckDetail)
|
{
|
try
|
{
|
StringBuilder sb = new StringBuilder();
|
sb.AppendFormat(@"insert into SpotCheckDetail
|
(PickTime,ProductNo,ProductHeight,ProductLength,ProductWidth,Type)
|
values(GETDATE(),'{0}','{1}','{2}','{3}',N'{4}')", spotCheckDetail.ProductNo,
|
spotCheckDetail.ProductHeight, spotCheckDetail.ProductLength,
|
spotCheckDetail.ProductWidth, spotCheckDetail.Type);
|
using (SqlCommand cmd = new SqlCommand(sb.ToString(), connection))
|
{
|
cmd.ExecuteNonQuery();
|
}
|
}
|
catch (Exception ex)
|
{
|
CommonUtil.WriteLog(LogType.Err, ex.ToString());
|
}
|
}
|
|
public void UpdateFinishedQty()
|
{
|
try
|
{
|
StringBuilder sb = new StringBuilder();
|
sb.AppendFormat(@"update SpotCheck
|
set FinishedQty=FinishedQty+1
|
where DATEDIFF(DAY,BeginTime,GETDATE())=0");
|
using (SqlCommand cmd = new SqlCommand(sb.ToString(), connection))
|
{
|
cmd.ExecuteNonQuery();
|
}
|
}
|
catch (Exception ex)
|
{
|
CommonUtil.WriteLog(LogType.Err, ex.ToString());
|
}
|
}
|
|
public void UpdateLastBreakProductQty(int qty)
|
{
|
try
|
{
|
StringBuilder sb = new StringBuilder();
|
sb.AppendFormat(@"update SpotCheck
|
set LastBreakProductQty={0}
|
where DATEDIFF(DAY,BeginTime,GETDATE())=0",qty);
|
using (SqlCommand cmd = new SqlCommand(sb.ToString(), connection))
|
{
|
cmd.ExecuteNonQuery();
|
}
|
}
|
catch (Exception ex)
|
{
|
CommonUtil.WriteLog(LogType.Err, ex.ToString());
|
}
|
}
|
|
public void UpdateLastProductQty(int qty)
|
{
|
try
|
{
|
StringBuilder sb = new StringBuilder();
|
sb.AppendFormat(@"update SpotCheck
|
set LastProductQty={0}
|
where DATEDIFF(DAY,BeginTime,GETDATE())=0", qty);
|
using (SqlCommand cmd = new SqlCommand(sb.ToString(), connection))
|
{
|
cmd.ExecuteNonQuery();
|
}
|
}
|
catch (Exception ex)
|
{
|
CommonUtil.WriteLog(LogType.Err, ex.ToString());
|
}
|
}
|
|
public void SaveCheckToCsv(SpotCheckDetail detail)
|
{
|
string fileName = @"E:\CheckReport\CheckReport" + DateTime.Now.ToString("yyyyMMdd") + ".csv";
|
bool isNew = !File.Exists(fileName);
|
using (FileStream myStream = new FileStream(fileName, FileMode.OpenOrCreate))
|
{
|
myStream.Seek(0, SeekOrigin.End);
|
using (StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.UTF8))
|
{
|
try
|
{
|
if (isNew)
|
{
|
sw.WriteLine("產品條碼,拾取時間,長度,寬度,高度");
|
}
|
sw.WriteLine(
|
string.Format("{0},{1},{2},{3},{4}",
|
detail.ProductNo, detail.PickTime, detail.ProductLength, detail.ProductWidth, detail.ProductHeight
|
));
|
}
|
catch (Exception e)
|
{
|
CommonUtil.WriteLog(LogType.Inf, string.Format("记录抽检结果出现异常：{0}", e.Message));
|
}
|
}
|
}
|
}
|
|
public void ClearTodayCheck()
|
{
|
try
|
{
|
StringBuilder sb = new StringBuilder();
|
sb.AppendFormat(@"delete from SpotCheck
|
where DATEDIFF(DAY,BeginTime,GETDATE())=0");
|
sb.AppendFormat(@"delete from SpotCheckDetail
|
where DATEDIFF(DAY,PickTime,GETDATE())=0");
|
using (SqlCommand cmd = new SqlCommand(sb.ToString(), connection))
|
{
|
cmd.ExecuteNonQuery();
|
}
|
}
|
catch (Exception ex)
|
{
|
CommonUtil.WriteLog(LogType.Err, ex.ToString());
|
}
|
}
|
}
|
|
public class SpotCheck
|
{
|
/// <summary>
|
/// 抽检开始时间
|
/// </summary>
|
public DateTime BeginTime { get; set; }
|
|
/// <summary>
|
/// 总抽检数
|
/// </summary>
|
public int TotalQty { get; set; }
|
|
/// <summary>
|
/// 已完成抽检数
|
/// </summary>
|
public int FinishedQty { get; set; }
|
|
/// <summary>
|
/// 抽检跨度(每隔多少个抽检下一个)
|
/// </summary>
|
public int PickCount { get; set; }
|
|
/// <summary>
|
/// 上一次程序关闭时记录下的界面生产数量
|
/// </summary>
|
public int LastBreakProductQty { get; set; }
|
|
/// <summary>
|
/// 上一次拾取时记录下的界面生产数量
|
/// </summary>
|
public int LastProductQty { get; set; }
|
}
|
|
public class SpotCheckDetail
|
{
|
/// <summary>
|
/// 产品条码
|
/// </summary>
|
public string ProductNo { get; set; }
|
|
/// <summary>
|
/// 拾取时间
|
/// </summary>
|
public DateTime PickTime { get; set; }
|
|
public double ProductHeight { get; set; }
|
|
public double ProductLength { get; set; }
|
|
public double ProductWidth { get; set; }
|
|
/// <summary>
|
/// 拾取类型(自动拾取、手动拾取)
|
/// </summary>
|
public string Type { get; set; }
|
|
}
|
}
|
