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.
256 lines
10 KiB
256 lines
10 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 System.Reflection;
|
|
using MESClassLibrary.Model;
|
|
using MESClassLibrary.BLL.Log;
|
|
|
|
namespace MESClassLibrary.DAL.BasicInfo
|
|
{
|
|
public class ProductDAL
|
|
{
|
|
public static string TableName = "tb_ProductType";
|
|
|
|
public DataTable SearchInfoByType(string type,string stationNo)
|
|
{
|
|
try
|
|
{
|
|
string sql =
|
|
@"SELECT dbo.tb_Product.PartName, dbo.tb_Product.ProductName, dbo.tb_ProductType.ProductTypeName,
|
|
dbo.tb_ProductType.ProductTypeNo, dbo.tb_Product.PartNo, dbo.tb_Station.StationNo, dbo.tb_Plastic.StockNo
|
|
FROM dbo.tb_Plastic LEFT OUTER JOIN
|
|
dbo.tb_Station ON dbo.tb_Plastic.StationID = dbo.tb_Station.StationID LEFT OUTER JOIN
|
|
dbo.tb_Product ON dbo.tb_Plastic.StockNo = dbo.tb_Product.StockNo LEFT OUTER JOIN
|
|
dbo.tb_ProductType ON dbo.tb_Product.ProductTypeID = dbo.tb_ProductType.ProductTypeID
|
|
WHERE dbo.tb_ProductType.ProductTypeNo=@ProductTypeNo and StationNo=@StationNo";
|
|
|
|
SqlParameter[] param = new SqlParameter[2];
|
|
|
|
param[0] = new SqlParameter("@ProductTypeNo", SqlDbType.VarChar);
|
|
param[0].Value = type;
|
|
|
|
param[1] = new SqlParameter("@StationNo", SqlDbType.VarChar);
|
|
param[1].Value = stationNo;
|
|
|
|
return SqlHelper.ExecuteDataset(SqlHelper.GetConnSting(), CommandType.Text, sql, param).Tables[0];
|
|
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
LogErrBLL.AddInfo(ex.ToString(), MethodBase.GetCurrentMethod());
|
|
return null;
|
|
}
|
|
}
|
|
|
|
public DataTable SearchInfoAllByType(string type)
|
|
{
|
|
try
|
|
{
|
|
string sql =
|
|
@"SELECT DISTINCT dbo.tb_Product.ProductName, dbo.tb_ProductType.ProductTypeName, dbo.tb_ProductType.ProductTypeNo,
|
|
dbo.tb_Product.PartNo, dbo.tb_Bom.PartNo2, dbo.tb_Product.StockNo
|
|
FROM dbo.tb_Bom RIGHT OUTER JOIN
|
|
dbo.tb_Product ON dbo.tb_Bom.PartNo1 = dbo.tb_Product.PartNo LEFT OUTER JOIN
|
|
dbo.tb_ProductType ON dbo.tb_Product.ProductTypeID = dbo.tb_ProductType.ProductTypeID
|
|
WHERE dbo.tb_ProductType.ProductTypeNo=@ProductTypeNo ORDER BY StockNo";
|
|
|
|
SqlParameter[] param = new SqlParameter[1];
|
|
|
|
param[0] = new SqlParameter("@ProductTypeNo", SqlDbType.VarChar);
|
|
param[0].Value = type;
|
|
return SqlHelper.ExecuteDataset(SqlHelper.GetConnSting(), CommandType.Text, sql, param).Tables[0];
|
|
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
LogErrBLL.AddInfo(ex.ToString(), MethodBase.GetCurrentMethod());
|
|
return null;
|
|
}
|
|
}
|
|
|
|
public DataTable SearchInfoByName(string ProductName, string ProductTypeNo)
|
|
{
|
|
try
|
|
{
|
|
// string sql =
|
|
// @"SELECT dbo.tb_Product.PartName, dbo.tb_Product.ProductName, dbo.tb_ProductType.ProductTypeName,
|
|
// dbo.tb_ProductType.ProductTypeNo, dbo.tb_Product.PartNo,dbo.tb_Product.StockNo
|
|
// FROM dbo.tb_ProductType RIGHT OUTER JOIN
|
|
// dbo.tb_Product ON dbo.tb_ProductType.ProductTypeID = dbo.tb_Product.ProductTypeID
|
|
// where dbo.tb_Product.ProductName=@ProductName and dbo.tb_ProductType.ProductTypeNo=@ProductTypeNo
|
|
// ORDER BY SUBSTRING(ProductName,0,4)";
|
|
string sql =
|
|
@"SELECT dbo.tb_Product.PartName, dbo.tb_Product.ProductName, dbo.tb_ProductType.ProductTypeName,
|
|
dbo.tb_ProductType.ProductTypeNo, dbo.tb_Product.PartNo, dbo.tb_Product.StockNo,
|
|
dbo.tb_Manufacturer.ManufacturerName
|
|
FROM dbo.tb_CarType LEFT OUTER JOIN
|
|
dbo.tb_Manufacturer ON dbo.tb_CarType.ManufacturerID = dbo.tb_Manufacturer.ID RIGHT OUTER JOIN
|
|
dbo.tb_Product ON dbo.tb_CarType.ID = dbo.tb_Product.CarTypeID LEFT OUTER JOIN
|
|
dbo.tb_ProductType ON dbo.tb_Product.ProductTypeID = dbo.tb_ProductType.ProductTypeID
|
|
where dbo.tb_Product.ProductName=@ProductName and dbo.tb_ProductType.ProductTypeNo=@ProductTypeNo
|
|
ORDER BY SUBSTRING(ProductName,0,4)";
|
|
|
|
SqlParameter[] param = new SqlParameter[2];
|
|
param[0] = new SqlParameter("@ProductName", SqlDbType.VarChar);
|
|
param[0].Value = ProductName;
|
|
|
|
param[1] = new SqlParameter("@ProductTypeNo", SqlDbType.VarChar);
|
|
param[1].Value = ProductTypeNo;
|
|
return SqlHelper.ExecuteDataset(SqlHelper.GetConnSting(), CommandType.Text, sql, param).Tables[0];
|
|
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
LogErrBLL.AddInfo(ex.ToString(), MethodBase.GetCurrentMethod());
|
|
return null;
|
|
}
|
|
}
|
|
|
|
public DataTable SearchInfoByPartNo(string partNo)
|
|
{
|
|
try
|
|
{
|
|
//string sql = @"select * from [tb_Product] where [PartNo]=@partNo";
|
|
string sql = @"SELECT dbo.tb_Product.*, dbo.tb_Manufacturer.ManufacturerName
|
|
FROM dbo.tb_Manufacturer RIGHT OUTER JOIN
|
|
dbo.tb_CarType ON dbo.tb_Manufacturer.ID = dbo.tb_CarType.ManufacturerID RIGHT OUTER JOIN
|
|
dbo.tb_Product ON dbo.tb_CarType.ID = dbo.tb_Product.CarTypeID where [PartNo]=@partNo";
|
|
|
|
SqlParameter[] param = new SqlParameter[1];
|
|
param[0] = new SqlParameter("@partNo", SqlDbType.VarChar);
|
|
param[0].Value = partNo;
|
|
|
|
return SqlHelper.ExecuteDataset(SqlHelper.GetConnSting(), CommandType.Text, sql, param).Tables[0];
|
|
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
LogErrBLL.AddInfo(ex.ToString(), MethodBase.GetCurrentMethod());
|
|
return null;
|
|
|
|
}
|
|
}
|
|
|
|
public DataTable SearchIsImportByStockNo(string StockNo)
|
|
{
|
|
try
|
|
{
|
|
string sql = @"select * from [tb_Product] where [StockNo]=@StockNo";
|
|
|
|
SqlParameter[] param = new SqlParameter[1];
|
|
param[0] = new SqlParameter("@StockNo", SqlDbType.VarChar);
|
|
param[0].Value = StockNo;
|
|
|
|
return SqlHelper.ExecuteDataset(SqlHelper.GetConnSting(), CommandType.Text, sql, param).Tables[0];
|
|
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
LogErrBLL.AddInfo(ex.ToString(), MethodBase.GetCurrentMethod());
|
|
return null;
|
|
|
|
}
|
|
}
|
|
|
|
public DataTable SearchIsImportByPartNo(string partNo)
|
|
{
|
|
try
|
|
{
|
|
string sql = @"select * from [tb_Product] where [PartNo]=@PartNo";
|
|
|
|
SqlParameter[] param = new SqlParameter[1];
|
|
param[0] = new SqlParameter("@PartNo", SqlDbType.VarChar);
|
|
param[0].Value = partNo;
|
|
|
|
return SqlHelper.ExecuteDataset(SqlHelper.GetConnSting(), CommandType.Text, sql, param).Tables[0];
|
|
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
LogErrBLL.AddInfo(ex.ToString(), MethodBase.GetCurrentMethod());
|
|
return null;
|
|
|
|
}
|
|
}
|
|
|
|
public DataTable SearchInfoByProductName(string ProductName)
|
|
{
|
|
try
|
|
{
|
|
string sql = @"select * from [tb_Product] where [ProductName]=@ProductName";
|
|
|
|
SqlParameter[] param = new SqlParameter[1];
|
|
param[0] = new SqlParameter("@ProductName", SqlDbType.VarChar);
|
|
param[0].Value = ProductName;
|
|
|
|
return SqlHelper.ExecuteDataset(SqlHelper.GetConnSting(), CommandType.Text, sql, param).Tables[0];
|
|
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
LogErrBLL.AddInfo(ex.ToString(), MethodBase.GetCurrentMethod());
|
|
return null;
|
|
}
|
|
}
|
|
|
|
public DataTable SearchInfoAll()
|
|
{
|
|
try
|
|
{
|
|
string sql = @"select * from [tb_Product] where ProductTypeID='f3bbde11-7188-4910-855d-806d92d142b9'";
|
|
|
|
return SqlHelper.ExecuteDataset(SqlHelper.GetConnSting(), CommandType.Text, sql, null).Tables[0];
|
|
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
LogErrBLL.AddInfo(ex.ToString(), MethodBase.GetCurrentMethod());
|
|
return null;
|
|
}
|
|
}
|
|
|
|
//public DataTable SearchInfoByType(string list)
|
|
//{
|
|
// try
|
|
// {
|
|
// string sql = @"select StockNo+'-'+ProductName AS aa from [tb_Product] where StockNo+'-'+ProductName like '%' + @list + '%' and [StockNo] like '2%'";
|
|
|
|
// SqlParameter[] param = new SqlParameter[1];
|
|
// param[0] = new SqlParameter("@list", SqlDbType.VarChar);
|
|
// param[0].Value = list;
|
|
|
|
// return SqlHelper.ExecuteDataset(SqlHelper.GetConnSting(), CommandType.Text, sql, param).Tables[0];
|
|
|
|
// }
|
|
// catch (Exception ex)
|
|
// {
|
|
// LogErrBLL.AddInfo(ex.ToString(), MethodBase.GetCurrentMethod());
|
|
// return null;
|
|
// }
|
|
//}
|
|
|
|
public DataTable SearchInfoByStock(string StockNo)
|
|
{
|
|
try
|
|
{
|
|
string sql = @"select * from [tb_Product] where [StockNo]=@StockNo";
|
|
|
|
SqlParameter[] param = new SqlParameter[1];
|
|
param[0] = new SqlParameter("@StockNo", SqlDbType.VarChar);
|
|
param[0].Value = StockNo;
|
|
|
|
return SqlHelper.ExecuteDataset(SqlHelper.GetConnSting(), CommandType.Text, sql, param).Tables[0];
|
|
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
LogErrBLL.AddInfo(ex.ToString(), MethodBase.GetCurrentMethod());
|
|
return null;
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|