MBInfo IPCAQuery.GetMBInfo(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.PCBModelID , b.Station, c.Descr,
b.Line, b.Status, b.TestFailCount, b.Udt,
(case when d.InfoValue is null then 'Normal'
else d.InfoValue end) as ShipMode
from PCB a
inner join PCBStatus b on ( a.PCBNo = b.PCBNo)
inner join Station c on (b.Station = c.Station)
left join PCBInfo d on (a.PCBNo= d.PCBNo and d.InfoType=@ShipMode)
where a.PCBNo = @PCBNo or a.MAC = @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(SqlHelper.ConnectionString_GetData,
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)tb.Rows[0]["Udt"];
string ShipMode = tb.Rows[0]["ShipMode"].ToString().Trim();
strSQL = @"select b.Station,c.Descr
from PartProcess a , Process_Station b, Station c
where a.MBFamily = @PartNo and
a.PilotRun= @IsFRU 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(SqlHelper.ConnectionString_GetData,
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 IFAQuery.ProductInfo(string ID, out List<NextStation> NextStationList)
{
NextStationList = new List<NextStation>();
ProdutData product = null;
/*string strSQL = @" select a.ProductID , a.CUSTSN, a.MAC, a.MBECR as ECR, a.MO,
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, b.Udt
from Product a ,ProductStatus b, Station c, Model d
where (a.ProductID = @Product or
a.CUSTSN = @Product) and
a.ProductID = b.ProductID and
a.Model = d.Model and
b.Station = c.Station ";*/
string strSQL = @"
IF EXISTS(SELECT ProductID FROM Product_Part WHERE Value=@Product)
BEGIN
SELECT a.ProductID , a.CUSTSN, a.MAC, a.MBECR as ECR, a.MO,
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, b.Udt
FROM Product a ,ProductStatus b, Station c, Model d
WHERE a.ProductID IN
(SELECT ProductID FROM Product_Part WHERE Value=@Product)
AND a.ProductID = b.ProductID
AND a.Model = d.Model
AND b.Station = c.Station
END
ELSE
BEGIN
SELECT a.ProductID , a.CUSTSN, a.MAC, a.MBECR as ECR, a.MO,
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, b.Udt
FROM Product a ,ProductStatus b, Station c, Model d
WHERE (a.ProductID = @Product
OR a.CUSTSN = @Product
OR a.PCBID = @Product )
AND a.ProductID = b.ProductID
AND a.Model = d.Model
AND b.Station = c.Station
END ";
SqlParameter paraName = new SqlParameter("@Product", SqlDbType.VarChar, 32);
paraName.Direction = ParameterDirection.Input;
paraName.Value = ID;
DataTable tb = SqlHelper.ExecuteDataFill(SqlHelper.ConnectionString_GetData,
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.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)tb.Rows[0]["Udt"];
strSQL = @"select b.Station,c.Descr
from Model_Process a , Process_Station b, Station c
where a.Model = @Model and
a.Process = b.Process and
b.PreStation =@Station and
b.Status=@Status and
b.Station =c.Station";
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;
DataTable tb1 = SqlHelper.ExecuteDataFill(SqlHelper.ConnectionString_GetData,
System.Data.CommandType.Text,
strSQL,
para1,
para2,
para3);
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 product;
}