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.
267 lines
12 KiB
267 lines
12 KiB
using System;
|
|
using System.Collections.Generic;
|
|
using System.Data;
|
|
using System.Data.SqlClient;
|
|
using System.Linq;
|
|
using System.Reflection;
|
|
using System.Text;
|
|
using MESClassLibrary.BLL.Log;
|
|
using MESClassLibrary.EFModel;
|
|
using MESClassLibrary.Model;
|
|
|
|
namespace MESClassLibrary.DAL.ZPPlan
|
|
{
|
|
public class ZPBomDAL
|
|
{
|
|
/// <summary>
|
|
/// 工位总成和半成品是否匹配
|
|
/// </summary>
|
|
/// <param name="stationNo"></param>
|
|
/// <param name="PartNo1"></param>
|
|
/// <param name="partNo2"></param>
|
|
/// <returns></returns>
|
|
public DataTable IsMath(string stationNo, string partNo1, string partNo2,int step)
|
|
{
|
|
try
|
|
{
|
|
string sql = @"SELECT dbo.tb_Station.StationNo, dbo.tb_Product.PartNo AS PartNo1, tb_Product_1.PartNo AS PartNo2,
|
|
dbo.tb_ZPBom.Step,dbo.tb_ZPBom.MouldNo,dbo.tb_ZPBom.IsScanCode
|
|
FROM dbo.tb_Station RIGHT OUTER JOIN
|
|
dbo.tb_Product RIGHT OUTER JOIN
|
|
dbo.tb_Product AS tb_Product_1 RIGHT OUTER JOIN
|
|
dbo.tb_ZPBom ON tb_Product_1.ProductID = dbo.tb_ZPBom.ProductID2 ON
|
|
dbo.tb_Product.ProductID = dbo.tb_ZPBom.ProductID1 ON dbo.tb_Station.StationID = dbo.tb_ZPBom.StationID
|
|
where dbo.tb_Station.StationNo=@stationNo and dbo.tb_Product.PartNo=@partNo1 and tb_Product_1.PartNo=@partNo2 and dbo.tb_ZPBom.Step=@step";
|
|
SqlParameter[] param = new SqlParameter[4];
|
|
|
|
param[0] = new SqlParameter("@stationNo", SqlDbType.VarChar);
|
|
param[0].Value = stationNo;
|
|
|
|
param[1] = new SqlParameter("@partNo1", SqlDbType.VarChar);
|
|
param[1].Value = partNo1;
|
|
|
|
param[2] = new SqlParameter("@partNo2", SqlDbType.VarChar);
|
|
param[2].Value = partNo2;
|
|
|
|
param[3] = new SqlParameter("@step", SqlDbType.Int);
|
|
param[3].Value = step;
|
|
|
|
return SqlHelper.ExecuteDataset(SqlHelper.GetConnSting(), CommandType.Text, sql, param).Tables[0];
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
LogErrBLL.AddInfo(ex.ToString(), MethodBase.GetCurrentMethod());
|
|
return null;
|
|
}
|
|
}
|
|
|
|
public DataTable SearchPartAndMould(string stationNo, string partNo1, string mould)
|
|
{
|
|
try
|
|
{
|
|
// string sql = @"SELECT dbo.tb_Station.StationNo, dbo.tb_Product.PartNo AS PartNo1, tb_Product_1.PartNo AS PartNo2,
|
|
// dbo.tb_ZPBom.IsHigh, dbo.tb_ZPBom.MouldNo
|
|
// FROM dbo.tb_Station RIGHT OUTER JOIN
|
|
// dbo.tb_Product RIGHT OUTER JOIN
|
|
// dbo.tb_Product AS tb_Product_1 RIGHT OUTER JOIN
|
|
// dbo.tb_ZPBom ON tb_Product_1.ProductID = dbo.tb_ZPBom.ProductID2 ON
|
|
// dbo.tb_Product.ProductID = dbo.tb_ZPBom.ProductID1 ON dbo.tb_Station.StationID = dbo.tb_ZPBom.StationID
|
|
// where dbo.tb_Station.StationNo=@stationNo and dbo.tb_Product.PartNo=@partNo1 and dbo.tb_ZPBom.MouldNo=@mould";
|
|
|
|
string sql =
|
|
@"SELECT b.StationID, p1.StockNo, b.MouldNo, b.ProductID1, b.ProductID2, p2.PartNo AS p2, p1.PartNo AS p1,
|
|
s.StationNo,p1.IsHigh
|
|
FROM dbo.tb_Station s RIGHT OUTER JOIN
|
|
dbo.tb_ZPBom AS b ON s.StationID = b.StationID LEFT OUTER JOIN
|
|
dbo.tb_Product AS p1 ON b.ProductID1 = p1.ProductID LEFT OUTER JOIN
|
|
dbo.tb_Product AS p2 ON b.ProductID2 = p2.ProductID
|
|
where s.StationNo=@stationNo and p1.PartNo=@partNo1 and b.MouldNo=@mould and isnull(b.ProductID2,'') <> '' and isnull(b.MouldNo,'')<>''";
|
|
|
|
SqlParameter[] param = new SqlParameter[3];
|
|
|
|
param[0] = new SqlParameter("@stationNo", SqlDbType.VarChar);
|
|
param[0].Value = stationNo;
|
|
|
|
param[1] = new SqlParameter("@partNo1", SqlDbType.VarChar);
|
|
param[1].Value = partNo1;
|
|
|
|
param[2] = new SqlParameter("@mould", SqlDbType.VarChar);
|
|
param[2].Value = mould;
|
|
|
|
return SqlHelper.ExecuteDataset(SqlHelper.GetConnSting(), CommandType.Text, sql, param).Tables[0];
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
LogErrBLL.AddInfo(ex.ToString(), MethodBase.GetCurrentMethod());
|
|
return null;
|
|
}
|
|
}
|
|
|
|
public string SearchStepInfo(string page, string pagesize, string partNo1, string partNo2)
|
|
{
|
|
try
|
|
{
|
|
string jsonStr = "[]";
|
|
int total = 0;//总行数
|
|
List<ZPBomModel> list = new List<ZPBomModel>();
|
|
string sql =
|
|
@"SELECT dbo.tb_ZPBom.StationID, dbo.tb_Station.StationNo, dbo.tb_Station.Des, dbo.tb_Product.StockNo AS StockNo1,
|
|
tb_Product_1.ProductName AS ProductName1, tb_Product_1.PartNo AS PartNo1, dbo.tb_Product.StockNo AS StockNo2,
|
|
dbo.tb_Product.ProductName AS ProductName2, dbo.tb_Product.PartNo AS PartNo2, dbo.tb_ZPBom.ID,
|
|
dbo.tb_ZPBom.ProductID1, dbo.tb_ZPBom.ProductID2, dbo.tb_ZPBom.MouldNo,
|
|
dbo.tb_ZPBom.Step, dbo.tb_ZPBom.IsScanCode, dbo.tb_ZPBom.IsPhoto
|
|
FROM dbo.tb_Station RIGHT OUTER JOIN
|
|
dbo.tb_Product RIGHT OUTER JOIN
|
|
dbo.tb_ZPBom ON dbo.tb_Product.ProductID = dbo.tb_ZPBom.ProductID2 LEFT OUTER JOIN
|
|
dbo.tb_Product AS tb_Product_1 ON dbo.tb_ZPBom.ProductID1 = tb_Product_1.ProductID ON
|
|
dbo.tb_Station.StationID = dbo.tb_ZPBom.StationID where 1=1";
|
|
|
|
if (!string.IsNullOrEmpty(partNo1))
|
|
{
|
|
sql += @" and tb_Product_1.PartNo like '%" + partNo1 + @"%'";
|
|
}
|
|
if (!string.IsNullOrEmpty(partNo2))
|
|
{
|
|
sql += @" and tb_Product.PartNo like '%" + partNo2 + @"%'";
|
|
}
|
|
|
|
sql += @" order by dbo.tb_Station.StationNo,dbo.tb_ZPBom.Step";
|
|
DataTable dt = SqlHelper.ExecuteDataset(SqlHelper.GetConnSting(), CommandType.Text, sql).Tables[0];
|
|
if (dt != null && dt.Rows.Count > 0)
|
|
{
|
|
list = CommonTools.ConvertTo<ZPBomModel>(dt).ToList();
|
|
}
|
|
|
|
if (list.Count > 0)
|
|
{
|
|
total = list.Count;
|
|
int Skipcount = (Convert.ToInt32(page) - 1) * Convert.ToInt32(pagesize);
|
|
list = list.Skip(Skipcount).Take(Convert.ToInt32(pagesize)).ToList();
|
|
|
|
JsonDataModel<ZPBomModel> md = new JsonDataModel<ZPBomModel>();
|
|
md.total = total.ToString();
|
|
md.rows = list;
|
|
jsonStr = JSONTools.ScriptSerialize<JsonDataModel<ZPBomModel>>(md);
|
|
}
|
|
return jsonStr;
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
LogErrBLL.AddInfo(ex.ToString(), MethodBase.GetCurrentMethod());
|
|
return "";
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 判断产品在本工位是不是最后一步
|
|
/// </summary>
|
|
/// <param name="partNo"></param>
|
|
/// <param name="step"></param>
|
|
/// <returns></returns>
|
|
public bool IsLast(string partNo, int step)
|
|
{
|
|
try
|
|
{
|
|
string sql = @"SELECT s.StationNo, p.PartNo, b.Step, b.MouldNo, b.IsScanCode, b.IsPhoto
|
|
FROM dbo.tb_Product INNER JOIN
|
|
dbo.tb_Product AS p ON dbo.tb_Product.ProductID = p.ProductID RIGHT OUTER JOIN
|
|
dbo.tb_ZPBom AS b ON p.ProductID = b.ProductID1 LEFT OUTER JOIN
|
|
dbo.tb_Station AS s ON b.StationID = s.StationID
|
|
where p.PartNo ='" + partNo + @"' and b.Step>" + step;
|
|
DataTable dt = SqlHelper.ExecuteDataset(SqlHelper.GetConnSting(), CommandType.Text, sql, null).Tables[0];
|
|
if (dt != null && dt.Rows.Count > 0)
|
|
{
|
|
return false;
|
|
}
|
|
|
|
return true;
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
LogErrBLL.AddInfo(ex.ToString(),MethodBase.GetCurrentMethod());
|
|
return false;
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 产品在工位上有几步
|
|
/// </summary>
|
|
/// <param name="partNo"></param>
|
|
/// <param name="station"></param>
|
|
/// <returns></returns>
|
|
public DataTable SearchStep(string partNo, string station)
|
|
{
|
|
try
|
|
{
|
|
string sql = @"SELECT s.StationNo, p.PartNo, b.Step, b.MouldNo, b.IsScanCode, b.IsPhoto
|
|
FROM dbo.tb_Product INNER JOIN
|
|
dbo.tb_Product AS p ON dbo.tb_Product.ProductID = p.ProductID RIGHT OUTER JOIN
|
|
dbo.tb_ZPBom AS b ON p.ProductID = b.ProductID1 LEFT OUTER JOIN
|
|
dbo.tb_Station AS s ON b.StationID = s.StationID
|
|
where p.PartNo ='" + partNo + @"' and s.StationNo = '" + station + @"'
|
|
order by b.Step ";
|
|
return SqlHelper.ExecuteDataset(SqlHelper.GetConnSting(), CommandType.Text, sql, null).Tables[0];
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
LogErrBLL.AddInfo(ex.ToString(),MethodBase.GetCurrentMethod());
|
|
return null;
|
|
}
|
|
}
|
|
|
|
public DataTable SearchInfoByStep(string station, string partNo, int step)
|
|
{
|
|
try
|
|
{
|
|
string sql =
|
|
@"SELECT dbo.tb_Station.StationNo, dbo.tb_ZPBom.Step, dbo.tb_ZPBom.IsScanCode, dbo.tb_ZPBom.IsPhoto,
|
|
tb_Product_1.StockNo, tb_Product_1.PartNo, tb_Product_1.ProductName, dbo.tb_ZPBom.ProductID2,
|
|
dbo.tb_Product.ProductName AS MaterialName, dbo.tb_Product.PartNo AS MaterialNo, tb_Product_1.ProgramNo
|
|
FROM dbo.tb_ZPBom LEFT OUTER JOIN
|
|
dbo.tb_Product ON dbo.tb_ZPBom.ProductID2 = dbo.tb_Product.ProductID LEFT OUTER JOIN
|
|
dbo.tb_Station ON dbo.tb_ZPBom.StationID = dbo.tb_Station.StationID LEFT OUTER JOIN
|
|
dbo.tb_Product AS tb_Product_1 ON dbo.tb_ZPBom.ProductID1 = tb_Product_1.ProductID
|
|
where dbo.tb_Station.StationNo='" + station + @"' and tb_Product_1.PartNo='" + partNo + @"' and dbo.tb_ZPBom.Step=" + step + @"";
|
|
|
|
return SqlHelper.ExecuteDataset(SqlHelper.GetConnSting(), CommandType.Text, sql, null).Tables[0];
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
LogErrBLL.AddInfo(ex.ToString(),MethodBase.GetCurrentMethod());
|
|
return null;
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 获取步骤之前有几步
|
|
/// </summary>
|
|
/// <param name="partNo"></param>
|
|
/// <param name="step"></param>
|
|
/// <returns></returns>
|
|
public int SearchBeforeStepCount(string partNo, int step)
|
|
{
|
|
try
|
|
{
|
|
string sql = @"SELECT p.PartNo, s.StationNo, m.ID, m.StationID, m.ProductID1, m.ProductID2, m.MouldNo, m.Step, m.IsScanCode, m.IsPhoto,
|
|
m.UserID, m.CreateTime
|
|
FROM dbo.tb_ZPBom AS m LEFT OUTER JOIN
|
|
dbo.tb_Product AS p ON m.ProductID1 = p.ProductID LEFT OUTER JOIN
|
|
dbo.tb_Station AS s ON m.StationID = s.StationID
|
|
where p.PartNo ='" + partNo + @"' and b.Step< " + step;
|
|
DataTable dt = SqlHelper.ExecuteDataset(SqlHelper.GetConnSting(), CommandType.Text, sql, null).Tables[0];
|
|
if (dt != null && dt.Rows.Count > 0)
|
|
{
|
|
return dt.Rows.Count;
|
|
}
|
|
|
|
return 0;
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
LogErrBLL.AddInfo(ex.ToString(),MethodBase.GetCurrentMethod());
|
|
return 0;
|
|
}
|
|
}
|
|
|
|
}
|
|
}
|
|
|