public MBInfo GetMBInfo(string DBConnection, string MBSN, out List<NextStation> NextStationList)
{
NextStationList = new List<NextStation>();
MBInfo mb = null;
string strSQL = @"SELECT a.PCBNo , a.CUSTSN, a.MAC, a.ECR, a.SMTMO, a.IECVER,a.CVSN,
a.PCBModelID , b.Station, c.Descr,
b.Line, b.Status, b.TestFailCount, CONVERT(nvarchar(20) , b.Udt, 120) AS Udt,
(case when d.InfoValue is null then 'Normal'
else d.InfoValue end) as ShipMode
FROM PCB a (NOLOCK)
INNER JOIN PCBStatus b (NOLOCK) on ( a.PCBNo = b.PCBNo)
INNER JOIN Station c (NOLOCK) on (b.Station = c.Station)
LEFT JOIN PCBInfo d (NOLOCK) on (a.PCBNo= d.PCBNo and d.InfoType=@ShipMode)
WHERE a.PCBNo = @PCBNo or a.MAC = @PCBNo or a.CVSN= @PCBNo ";
SqlParameter paraName = new SqlParameter("@PCBNo", SqlDbType.VarChar, 32);
paraName.Direction = ParameterDirection.Input;
paraName.Value = MBSN;
SqlParameter paraName1 = new SqlParameter("@ShipMode", SqlDbType.VarChar, 32);
paraName1.Direction = ParameterDirection.Input;
paraName1.Value = "ShipMode";
DataTable tb = SQLHelper.ExecuteDataFill(DBConnection,
System.Data.CommandType.Text,
strSQL,
paraName,
paraName1);
if (tb.Rows.Count == 1)
{
mb = new MBInfo();
mb.MBSN = tb.Rows[0]["PCBNo"].ToString().Trim();
mb.MAC = tb.Rows[0]["MAC"].ToString().Trim();
mb.CustomSN = tb.Rows[0]["CUSTSN"].ToString().Trim();
mb.ECR = tb.Rows[0]["ECR"].ToString().Trim();
mb.Line = tb.Rows[0]["Line"].ToString().Trim();
mb.PartNo = tb.Rows[0]["PCBModelID"].ToString().Trim();
mb.SMTMO = tb.Rows[0]["SMTMO"].ToString().Trim();
mb.Station = tb.Rows[0]["Station"].ToString().Trim();
mb.StationDescr = tb.Rows[0]["Descr"].ToString().Trim();
mb.Status = (int)tb.Rows[0]["Status"];
mb.TestFailCount = (int)tb.Rows[0]["TestFailCount"];
mb.Udt = DateTime.Parse(tb.Rows[0]["Udt"].ToString());
mb.IECVER = tb.Rows[0]["IECVER"].ToString().Trim();
mb.CPU = tb.Rows[0]["CVSN"].ToString().Trim();
string ShipMode = tb.Rows[0]["ShipMode"].ToString().Trim();
strSQL = @"SELECT b.Station,c.Descr
FROM PartProcess a (NOLOCK), Process_Station b (NOLOCK) , Station c (NOLOCK)
WHERE (a.MBFamily IN
(SELECT Descr FROM Part WHERE PartNo= @PartNo)
OR a.MBFamily = @PartNo)
AND a.Process = b.Process
AND b.PreStation =@Station
AND b.Status=@Status AND b.Station =c.Station";
SqlParameter para1 = new SqlParameter("@PartNo", SqlDbType.VarChar, 32);
para1.Direction = ParameterDirection.Input;
para1.Value = mb.PartNo;
SqlParameter para2 = new SqlParameter("@Station ", SqlDbType.VarChar, 32);
para2.Direction = ParameterDirection.Input;
para2.Value = mb.Station;
SqlParameter para3 = new SqlParameter("@Status ", SqlDbType.Int);
para3.Direction = ParameterDirection.Input;
para3.Value = mb.Status;
SqlParameter para4 = new SqlParameter("@IsFRU ", SqlDbType.VarChar, 32);
para4.Direction = ParameterDirection.Input;
if (ShipMode == "FRU")
{
para4.Value = "Y";
}
else
{
para4.Value = "N";
}
DataTable tb1 = SQLHelper.ExecuteDataFill(DBConnection,
System.Data.CommandType.Text,
strSQL,
para1,
para2,
para3,
para4);
foreach (DataRow dr in tb1.Rows)
{
NextStation nextStation = new NextStation();
nextStation.Station = dr["Station"].ToString().Trim();
nextStation.Description = dr["Descr"].ToString().Trim();
NextStationList.Add(nextStation);
}
}
return mb;
}
ProdutData IFA_ProductInfo.ProductInfo(string Connection, string ID, out List<NextStation> NextStationList)
{
string methodName = MethodBase.GetCurrentMethod().Name;
BaseLog.LoggingBegin(logger, methodName);
try
{
NextStationList = new List<NextStation>();
ProdutData product = null;
string strSQL = @"
IF EXISTS(SELECT ProductID FROM Product_Part (NOLOCK) WHERE PartSn=@Product)
BEGIN
SELECT a.ProductID , a.CUSTSN, a.MAC, a.MBECR as ECR, a.MO, a.UnitWeight,
a.PCBID, a.PCBModel, a.Model,d.Family,
a.CartonSN, a.PalletNo, a.DeliveryNo,
a.MO , b.Station, c.Descr,
b.Line, b.Status, b.TestFailCount, CONVERT (nvarchar(20),b.Udt,120) AS Udt,
e.SnoId as [WHLocation],
f.ShipDate
FROM Product a (NOLOCK)
LEFT JOIN ProductStatus b (NOLOCK) ON a.ProductID = b.ProductID
LEFT JOIN Station c (NOLOCK) ON b.Station = c.Station
LEFT JOIN Model d (NOLOCK) ON a.Model = d.Model
LEFT JOIN (Select * from PAK_LocMas (NOLOCK)
where Pno<>'' and Pno is not null) e on a.PalletNo=e.Pno
LEFT JOIN Delivery f ON a.DeliveryNo=f.DeliveryNo
WHERE a.ProductID IN
(SELECT TOP 1 ProductID FROM Product_Part (NOLOCK) WHERE PartSn=@Product)
END
ELSE IF EXISTS(
SELECT TOP 1 ProductID FROM Product
WHERE ProductID = @Product OR CUSTSN = @Product OR PCBID = @Product OR MAC=@Product
)
BEGIN
SELECT a.ProductID , a.CUSTSN, a.MAC, a.MBECR as ECR, a.MO, a.UnitWeight,
a.PCBID, a.PCBModel, a.Model,d.Family,
a.CartonSN, a.PalletNo, a.DeliveryNo,
a.MO , b.Station, c.Descr,
b.Line, b.Status, b.TestFailCount, CONVERT (nvarchar(20),b.Udt,120) AS Udt ,
e.SnoId as [WHLocation],
f.ShipDate
FROM Product a (NOLOCK)
LEFT JOIN ProductStatus b (NOLOCK) ON a.ProductID = b.ProductID
LEFT JOIN Station c (NOLOCK) ON b.Station = c.Station
LEFT JOIN Model d (NOLOCK) ON a.Model = d.Model
LEFT JOIN (Select * from PAK_LocMas (NOLOCK)
where Pno<>'' and Pno is not null) e on a.PalletNo=e.Pno
LEFT JOIN Delivery f ON a.DeliveryNo=f.DeliveryNo
WHERE a.ProductID = @Product OR a.CUSTSN = @Product OR a.PCBID = @Product OR MAC=@Product
END
ELSE IF EXISTS(
SELECT PCBNo FROM PCBInfo WHERE InfoValue=@Product
)
BEGIN
SELECT a.ProductID , a.CUSTSN, a.MAC, a.MBECR as ECR, a.MO, a.UnitWeight,
a.PCBID, a.PCBModel, a.Model,d.Family,
a.CartonSN, a.PalletNo, a.DeliveryNo,
a.MO , b.Station, c.Descr,
b.Line, b.Status, b.TestFailCount, CONVERT (nvarchar(20),b.Udt,120) AS Udt ,
e.SnoId as [WHLocation],
f.ShipDate
FROM Product a (NOLOCK)
LEFT JOIN ProductStatus b (NOLOCK) ON a.ProductID = b.ProductID
LEFT JOIN Station c (NOLOCK) ON b.Station = c.Station
LEFT JOIN Model d (NOLOCK) ON a.Model = d.Model
LEFT JOIN (Select * from PAK_LocMas (NOLOCK)
where Pno<>'' and Pno is not null) e on a.PalletNo=e.Pno
LEFT JOIN Delivery f ON a.DeliveryNo=f.DeliveryNo
WHERE a.ProductID = (
select ProductID from Product where PCBID
= (select PCBNo from PCBInfo where InfoValue=@Product)
)
END
ELSE
BEGIN
SELECT TOP 1 a.ProductID , a.CUSTSN, a.MAC, a.MBECR as ECR, a.MO, a.UnitWeight,
a.PCBID, a.PCBModel, a.Model,d.Family,
a.CartonSN, a.PalletNo, a.DeliveryNo,
a.MO , b.Station, c.Descr,
b.Line, b.Status, b.TestFailCount, CONVERT (nvarchar(20),b.Udt,120) AS Udt ,
e.SnoId as [WHLocation],
f.ShipDate
FROM UnpackProduct up (NOLOCK)
LEFT JOIN Product a (NOLOCK) ON a.ProductID = up.ProductID
LEFT JOIN ProductStatus b (NOLOCK) ON a.ProductID = b.ProductID
LEFT JOIN Station c (NOLOCK) ON b.Station = c.Station
LEFT JOIN Model d (NOLOCK) ON a.Model = d.Model
LEFT JOIN (Select * from PAK_LocMas (NOLOCK)
where Pno<>'' and Pno is not null) e on a.PalletNo=e.Pno
LEFT JOIN Delivery f ON a.DeliveryNo=f.DeliveryNo
WHERE up.ProductID = @Product OR up.CUSTSN = @Product
ORDER BY up.Cdt DESC
END";
SqlParameter paraName = new SqlParameter("@Product", SqlDbType.VarChar, 32);
paraName.Direction = ParameterDirection.Input;
paraName.Value = ID;
DataTable tb = SQLHelper.ExecuteDataFill(Connection,
System.Data.CommandType.Text,
strSQL,
paraName);
if (tb.Rows.Count == 1)
{
product = new ProdutData();
product.CartonSN = tb.Rows[0]["CartonSN"].ToString().Trim();
product.CustomSN = tb.Rows[0]["CUSTSN"].ToString().Trim();
product.DeliveryNo = tb.Rows[0]["DeliveryNo"].ToString().Trim();
product.ECR = tb.Rows[0]["ECR"].ToString().Trim();
product.Family = tb.Rows[0]["Family"].ToString().Trim();
product.Line = tb.Rows[0]["Line"].ToString().Trim();
product.MAC = tb.Rows[0]["MAC"].ToString().Trim();
product.MBPartNo = tb.Rows[0]["PCBModel"].ToString().Trim();
product.MBSN = tb.Rows[0]["PCBID"].ToString().Trim();
product.MO = tb.Rows[0]["MO"].ToString().Trim();
product.Model = tb.Rows[0]["Model"].ToString().Trim();
product.PalletNo = tb.Rows[0]["PalletNo"].ToString().Trim();
product.ProductID = tb.Rows[0]["ProductID"].ToString().Trim();
product.WHLocation = tb.Rows[0]["WHLocation"].ToString().Trim();
product.UnitWeight = Double.Parse(tb.Rows[0]["UnitWeight"].ToString().Trim()) == 0 ? "" : tb.Rows[0]["UnitWeight"].ToString().Trim();
product.Station = tb.Rows[0]["Station"].ToString().Trim();
product.StationDescr = tb.Rows[0]["Descr"].ToString().Trim();
product.Status = (int)tb.Rows[0]["Status"];
product.TestFailCount = (int)tb.Rows[0]["TestFailCount"];
product.Udt = DateTime.Parse(tb.Rows[0]["Udt"].ToString());
product.ShipDate = tb.Rows[0]["ShipDate"].ToString(); //shipdate
strSQL = @"IF ((SELECT COUNT('X') FROM ForceNWC WHERE ProductID = @ProductID )= 0 )
OR
((SELECT COUNT('X') FROM ForceNWC a (NOLOCK),
ProductLog b (NOLOCK)
WHERE a.ProductID = @ProductID
AND a.ProductID = b.ProductID
AND a.Udt < b.Cdt) > 0)
BEGIN
If EXISTS (SELECT * FROM Model_Process (NOLOCK) WHERE Model = @Model )
BEGIN
SELECT b.Station,c.Descr
FROM Model_Process a (NOLOCK), Process_Station b (NOLOCK) , Station c (NOLOCK)
WHERE a.Model = @Model
AND a.Process = b.Process
AND b.PreStation =@Station
AND b.Status=@Status
AND b.Station =c.Station
RETURN
END
ELSE IF EXISTS(SELECT * FROM ProcessRule (NOLOCK) WHERE Value1 = @Family)
BEGIN
SELECT b.Station,c.Descr
FROM ProcessRule a (NOLOCK), Process_Station b (NOLOCK) , Station c (NOLOCK)
WHERE a.Value1 = @Family
AND a.Process = b.Process
AND b.PreStation =@Station
AND b.Status=@Status
AND b.Station =c.Station
RETURN
END
SELECT '' AS Station,'' AS Descr
END
ELSE
BEGIN
SELECT b.Station ,b.Descr FROM ForceNWC a (NOLOCK),Station b (NOLOCK)
WHERE a.ProductID = @ProductID AND a.ForceNWC = b.Station
END"
;
SqlParameter para1 = new SqlParameter("@Model", SqlDbType.VarChar, 32);
para1.Direction = ParameterDirection.Input;
para1.Value = product.Model;
SqlParameter para2 = new SqlParameter("@Station ", SqlDbType.VarChar, 32);
para2.Direction = ParameterDirection.Input;
para2.Value = product.Station;
SqlParameter para3 = new SqlParameter("@Status ", SqlDbType.Int);
para3.Direction = ParameterDirection.Input;
para3.Value = product.Status;
SqlParameter para4 = new SqlParameter("@ProductID ", SqlDbType.VarChar, 32);
para4.Direction = ParameterDirection.Input;
para4.Value = product.ProductID;
SqlParameter para5 = new SqlParameter("@Family", SqlDbType.VarChar, 32);
para5.Direction = ParameterDirection.Input;
para5.Value = product.Family;
DataTable tb1 = SQLHelper.ExecuteDataFill(Connection,
System.Data.CommandType.Text,
strSQL,
para1,
para2,
para3,
para4,
para5);
foreach (DataRow dr in tb1.Rows)
{
NextStation nextStation = new NextStation();
nextStation.Station = dr["Station"].ToString().Trim();
nextStation.Description = dr["Descr"].ToString().Trim();
NextStationList.Add(nextStation);
}
}
else if (tb.Rows.Count == 0)
{
product = new ProdutData();
product.CartonSN = "";
product.CustomSN = "";
product.DeliveryNo = "";
product.ECR = "";
product.Family = "";
product.Line = "";
product.MAC = "";
product.MBPartNo = "";
product.MBSN = "";
product.MO = "";
product.Model = "";
product.PalletNo = "";
product.ProductID = "";
product.WHLocation = "";
product.UnitWeight = "";
product.Station = "";
product.StationDescr = "";
product.Status = 0;
product.TestFailCount = 0;
product.Udt = DateTime.Parse("1900-01-01");
product.ShipDate = "";
}
return product;
}
catch (Exception e)
{
BaseLog.LoggingError(logger, MethodBase.GetCurrentMethod(), e);
throw;
}
finally
{
BaseLog.LoggingEnd(logger, methodName);
}
}