You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
299 lines
13 KiB
299 lines
13 KiB
using System;
|
|
using System.Collections.Generic;
|
|
using System.Linq;
|
|
using System.Text;
|
|
using System.Threading.Tasks;
|
|
using System.Data;
|
|
using System.Data.SqlClient;
|
|
using MESClassLibrary.BLL.Log;
|
|
using MESClassLibrary.EFModel;
|
|
using MESClassLibrary.Model;
|
|
using System.Reflection;
|
|
|
|
namespace MESClassLibrary.DAL.BasicInfo
|
|
{
|
|
public class InjectionPlanDAL
|
|
{
|
|
public static string TableName = "tb_InjectionPlan";
|
|
|
|
|
|
/// <summary>
|
|
/// 获取当前计划
|
|
/// </summary>
|
|
/// <param name="StationID"></param>
|
|
/// <returns></returns>
|
|
public DataTable SearchPlanByStation(string StationID)
|
|
{
|
|
try
|
|
{
|
|
string sql = @"SELECT top 1 dbo.tb_InjectionPlan.InjectionPlanID, dbo.tb_InjectionPlan.PartNo, dbo.tb_Station.StationNo,
|
|
dbo.tb_InjectionPlan.BeginTime, dbo.tb_InjectionPlan.PlanCount, dbo.tb_InjectionPlan.EndTime,
|
|
dbo.tb_InjectionPlan.StockNo, dbo.tb_InjectionPlan.PlanDate, dbo.tb_InjectionPlan.CompleteCount,
|
|
dbo.tb_InjectionPlan.ProductNature, dbo.tb_Product.ColorName, dbo.tb_Product.Rows, dbo.tb_Product.Cols, dbo.tb_Product.ProductID,
|
|
dbo.tb_Product.Layers, dbo.tb_Product.PicturePath, dbo.tb_Station.StationID
|
|
FROM dbo.tb_InjectionPlan with (NOLOCK) LEFT OUTER JOIN
|
|
dbo.tb_Station ON dbo.tb_InjectionPlan.StationID = dbo.tb_Station.StationID LEFT OUTER JOIN
|
|
dbo.tb_Product ON dbo.tb_InjectionPlan.StockNo = dbo.tb_Product.StockNo
|
|
WHERE (dbo.tb_Station.StationNo = @StationID) AND ((dbo.tb_InjectionPlan.IsFinish IS NULL OR dbo.tb_InjectionPlan.IsFinish=0))
|
|
ORDER BY dbo.tb_InjectionPlan.BeginTime";
|
|
|
|
// string sql = @"SELECT top 1 dbo.tb_InjectionPlan.InjectionPlanID, dbo.tb_InjectionPlan.PartNo, dbo.tb_Station.StationNo,
|
|
// dbo.tb_InjectionPlan.BeginTime, dbo.tb_InjectionPlan.PlanCount, dbo.tb_InjectionPlan.EndTime,
|
|
// dbo.tb_InjectionPlan.StockNo, dbo.tb_InjectionPlan.PlanDate, dbo.tb_InjectionPlan.CompleteCount,
|
|
// dbo.tb_InjectionPlan.ProductNature, dbo.tb_Product.ColorName, dbo.tb_Product.Rows, dbo.tb_Product.Cols,
|
|
// dbo.tb_Product.Layers, dbo.tb_Product.PicturePath, dbo.tb_Station.StationID,
|
|
// dbo.tb_Manufacturer.ManufacturerName
|
|
// FROM dbo.tb_InjectionPlan LEFT OUTER JOIN
|
|
// dbo.tb_Station ON dbo.tb_InjectionPlan.StationID = dbo.tb_Station.StationID LEFT OUTER JOIN
|
|
// dbo.tb_Product LEFT OUTER JOIN
|
|
// dbo.tb_Manufacturer RIGHT OUTER JOIN
|
|
// dbo.tb_CarType ON dbo.tb_Manufacturer.ID = dbo.tb_CarType.ManufacturerID ON
|
|
// dbo.tb_Product.CarTypeID = dbo.tb_CarType.ID ON dbo.tb_InjectionPlan.StockNo = dbo.tb_Product.StockNo
|
|
// WHERE (dbo.tb_Station.StationNo = @StationID) AND ((dbo.tb_InjectionPlan.IsFinish IS NULL OR dbo.tb_InjectionPlan.IsFinish=0))
|
|
// ORDER BY dbo.tb_InjectionPlan.BeginTime";
|
|
|
|
SqlParameter[] param = new SqlParameter[1];
|
|
param[0] = new SqlParameter("@StationID", SqlDbType.VarChar);
|
|
param[0].Value = StationID;
|
|
|
|
return SqlHelper.ExecuteDataset(SqlHelper.GetConnSting(), CommandType.Text, sql, param).Tables[0];
|
|
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
LogErrBLL.AddInfo(ex.ToString(), MethodBase.GetCurrentMethod());
|
|
return null;
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 获取下一计划
|
|
/// </summary>
|
|
/// <param name="StationID"></param>
|
|
/// <returns></returns>
|
|
public DataTable NextSearchPlanByStation(string StationID)
|
|
{
|
|
try
|
|
{
|
|
string sql = @"SELECT top 2 dbo.tb_Station.StationNo, dbo.tb_InjectionPlan.BeginTime, dbo.tb_InjectionPlan.PlanCount, dbo.tb_Product.ProductID,
|
|
dbo.tb_InjectionPlan.InjectionPlanID, dbo.tb_InjectionPlan.EndTime, dbo.tb_InjectionPlan.PlanDate,
|
|
dbo.tb_InjectionPlan.CompleteCount, dbo.tb_Product.ColorName, dbo.tb_Product.Rows, dbo.tb_Product.Cols,
|
|
dbo.tb_Product.Layers, dbo.tb_Product.PicturePath, dbo.tb_Station.StationID, dbo.tb_InjectionPlan.PartNo,
|
|
dbo.tb_Product.ProductName
|
|
FROM dbo.tb_InjectionPlan LEFT OUTER JOIN
|
|
dbo.tb_Product ON dbo.tb_InjectionPlan.PartNo = dbo.tb_Product.PartNo LEFT OUTER JOIN
|
|
dbo.tb_Station ON dbo.tb_InjectionPlan.StationID = dbo.tb_Station.StationID
|
|
WHERE (dbo.tb_Station.StationNo = @StationID) AND ((dbo.tb_InjectionPlan.IsFinish IS NULL OR dbo.tb_InjectionPlan.IsFinish=0))
|
|
ORDER BY dbo.tb_InjectionPlan.BeginTime";
|
|
|
|
SqlParameter[] param = new SqlParameter[1];
|
|
param[0] = new SqlParameter("@StationID", SqlDbType.VarChar);
|
|
param[0].Value = StationID;
|
|
|
|
return SqlHelper.ExecuteDataset(SqlHelper.GetConnSting(), CommandType.Text, sql, param).Tables[0];
|
|
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
LogErrBLL.AddInfo(ex.ToString(), MethodBase.GetCurrentMethod());
|
|
return null;
|
|
}
|
|
}
|
|
|
|
public bool UpdateFinish(InjectionPlanModel md)
|
|
{
|
|
try
|
|
{
|
|
#region 添加数据
|
|
string sql = "";
|
|
SqlParameter[] param = null;
|
|
// sql = @"update tb_InjectionPlan set IsFinish=1 ,FinishTime=getdate()
|
|
// where [InjectionPlanID]=( select top 1 [InjectionPlanID] from [dbo].[tb_InjectionPlan] where (IsFinish is null or IsFinish=0 )and StationID=@StationID order by BeginTime asc)";
|
|
sql = @"update tb_InjectionPlan set IsFinish=1 ,FinishTime=getdate()
|
|
where [InjectionPlanID]=@planID";
|
|
#region 添加参数
|
|
param = new SqlParameter[1];
|
|
param[0] = new SqlParameter("@planID", SqlDbType.VarChar);
|
|
param[0].Value = md.InjectionPlanID;
|
|
#endregion
|
|
|
|
SqlHelper.ExecuteNonQuery(SqlHelper.GetConnSting(), CommandType.Text, sql, param);
|
|
#endregion
|
|
return true;
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
LogErrBLL.AddInfo(ex.ToString(), MethodBase.GetCurrentMethod());
|
|
return false;
|
|
}
|
|
}
|
|
|
|
public DataTable SearchByPage(int pageIndex, int pageSize, string stationID, string stockNo)
|
|
{
|
|
try
|
|
{
|
|
|
|
string sql = "select * FROM[dbo].[tb_InjectionPlan] ";
|
|
sql += " where 1=1 ";
|
|
if (!string.IsNullOrEmpty(stationID))
|
|
{
|
|
sql += " and StationID = '" + stationID + "' ";
|
|
}
|
|
if (!string.IsNullOrEmpty(stockNo))
|
|
{
|
|
sql += " and StockNo like '%" + stockNo + "%' ";
|
|
}
|
|
sql += " order by BeginTime ";
|
|
sql += " offset " + ((pageIndex - 1) * pageSize) + " rows ";
|
|
sql += " fetch next " + pageSize + " rows only ";
|
|
|
|
return SqlHelper.ExecuteDataset(SqlHelper.GetConnSting(), CommandType.Text, sql, null).Tables[0];
|
|
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
LogErrBLL.AddInfo(ex.ToString(), MethodBase.GetCurrentMethod());
|
|
return null;
|
|
}
|
|
}
|
|
public DataTable SearchByTime(int pageIndex, int pageSize, string stationID, string stockNo, string StartTime, string EndTime)
|
|
{
|
|
try
|
|
{
|
|
|
|
string sql = "select * FROM[dbo].[tb_InjectionPlan] where BeginTime>='" + StartTime + "' and BeginTime<='" + EndTime + "' ";
|
|
if (!string.IsNullOrEmpty(stationID))
|
|
{
|
|
sql += " and StationID = '" + stationID + "' ";
|
|
}
|
|
if (!string.IsNullOrEmpty(stockNo))
|
|
{
|
|
sql += " and StockNo like '%" + stockNo + "%' ";
|
|
}
|
|
sql += " order by BeginTime ";
|
|
sql += " offset " + ((pageIndex - 1) * pageSize) + " rows ";
|
|
sql += " fetch next " + pageSize + " rows only ";
|
|
|
|
return SqlHelper.ExecuteDataset(SqlHelper.GetConnSting(), CommandType.Text, sql, null).Tables[0];
|
|
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
LogErrBLL.AddInfo(ex.ToString(), MethodBase.GetCurrentMethod());
|
|
return null;
|
|
}
|
|
}
|
|
|
|
public int SearchByTimeCount(string stationID, string stockNo, string StartTime, string EndTime)
|
|
{
|
|
try
|
|
{
|
|
|
|
string sql = "select * FROM[dbo].[tb_InjectionPlan] where BeginTime>='" + StartTime + "' and EndTime<='" + EndTime + "' ";
|
|
if (!string.IsNullOrEmpty(stationID))
|
|
{
|
|
sql += " and StationID = '" + stationID + "' ";
|
|
}
|
|
if (!string.IsNullOrEmpty(stockNo))
|
|
{
|
|
sql += " and StockNo like '%" + stockNo + "%' ";
|
|
}
|
|
|
|
return SqlHelper.ExecuteNonQuery(SqlHelper.GetConnSting(), CommandType.Text, sql);
|
|
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
LogErrBLL.AddInfo(ex.ToString(), MethodBase.GetCurrentMethod());
|
|
return 0;
|
|
}
|
|
}
|
|
|
|
public DataTable searchPlanInfo(string id)
|
|
{
|
|
try
|
|
{
|
|
string sql = @"select * from tb_InjectionPlan where InjectionPlanID='" + id + "'";
|
|
|
|
return SqlHelper.ExecuteDataset(SqlHelper.GetConnSting(), CommandType.Text, sql, null).Tables[0];
|
|
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
LogErrBLL.AddInfo(ex.ToString(), MethodBase.GetCurrentMethod());
|
|
return null;
|
|
}
|
|
}
|
|
|
|
public bool updateFinishCount(string partNo, string stationID,string stockNo,string planID)
|
|
{
|
|
try
|
|
{
|
|
SqlParameter[] param = null;
|
|
|
|
#region 添加参数
|
|
|
|
param = new SqlParameter[4];
|
|
|
|
param[0] = new SqlParameter("@partNo", SqlDbType.VarChar, 50);
|
|
param[0].Value = partNo;
|
|
|
|
param[1] = new SqlParameter("@planID", SqlDbType.VarChar, 36);
|
|
param[1].Value = planID;
|
|
|
|
param[2] = new SqlParameter("@stationID", SqlDbType.VarChar, 36);
|
|
param[2].Value = stationID;
|
|
|
|
param[3] = new SqlParameter("@StockNo", SqlDbType.VarChar, 36);
|
|
param[3].Value = stockNo;
|
|
|
|
#endregion
|
|
|
|
SqlHelper.ExecuteNonQuery(SqlHelper.GetConnSting(), CommandType.StoredProcedure, "update_Product_Injection", param);
|
|
return true;
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
LogErrBLL.AddInfo(ex.ToString(), MethodBase.GetCurrentMethod());
|
|
return false;
|
|
}
|
|
}
|
|
|
|
public DataTable SearchQty(string planID)
|
|
{
|
|
try
|
|
{
|
|
string sql= @"SELECT ISNULL(SUM(ISNULL(ProductCount,0)),0) ProductCount, ISNULL(SUM(ISNULL(BadCount,0)),0) BadCount
|
|
FROM tb_Product_Injection WITH (NOLOCK)
|
|
WHERE PlanID = '" + planID + @"'";
|
|
return SqlHelper.ExecuteDataset(SqlHelper.GetConnSting(), CommandType.Text, sql, null).Tables[0];
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
LogErrBLL.AddInfo(ex.ToString(),MethodBase.GetCurrentMethod());
|
|
return null;
|
|
}
|
|
}
|
|
|
|
public int GetQtyByPlan(string plan)
|
|
{
|
|
int res = 0;
|
|
try
|
|
{
|
|
string sql = @"select count(OneBarCode) as Qty from v_Code with (noLock) where PlanID='" + plan + @"' and PrintType<>2";
|
|
|
|
DataTable dt = SqlHelper.ExecuteDataset(SqlHelper.GetConnSting(), CommandType.Text, sql, null).Tables[0];
|
|
if (dt != null && dt.Rows.Count > 0)
|
|
{
|
|
res = int.Parse(dt.Rows[0]["Qty"].ToString());
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
LogErrBLL.AddInfo(ex.ToString(), MethodBase.GetCurrentMethod());
|
|
}
|
|
|
|
return res;
|
|
}
|
|
}
|
|
}
|
|
|