public static List<DCDetail> QueryDCDetailByBatchID(string connectionStr, string DBName, string BatchID)
{
string connectStr = string.Format(connectionStr, DBName);
List<DCDetail> retList = new List<DCDetail>();
string strSQL = @"select a.BatchID, a.Family, a.IECPn, a.PartType, a.Vendor, a.DefectCode, a.DefectDescr,
a.PartSn, (b.Value+dbo.fn_AddZero(convert(varchar(10), a.ID), 'L', 9)) as PartSerialNo
from DefectComponent a
inner join SysSetting b on Name='CustomerCode'
where a.BatchID = @BatchID
order by a.Family, a.IECPn, a.Vendor, a.DefectCode";
DataTable dt = SQLHelper.ExecuteDataFill(connectStr,
//DataTable dt = SQLHelper.ExecuteDataFill(SQLHelper.GetDBConnectionString(connectStr, 0),
System.Data.CommandType.Text,
strSQL,
SQLHelper.CreateSqlParameter("@BatchID", 20, BatchID));
foreach (DataRow dr in dt.Rows)
{
DCDetail item = new DCDetail();
item.BatchID = dr["BatchID"].ToString().Trim();
item.Family = dr["Family"].ToString().Trim();
item.IECPn = dr["IECPn"].ToString().Trim();
item.PartType = dr["PartType"].ToString().Trim();
item.Vendor = dr["Vendor"].ToString().Trim();
item.DefectCode = dr["DefectCode"].ToString().Trim();
item.DefectDescr = dr["DefectDescr"].ToString().Trim();
item.PartSn = dr["PartSn"].ToString().Trim();
item.PartSerialNo = dr["PartSerialNo"].ToString().Trim();
retList.Add(item);
}
return retList;
}
//3.Query Result and Build Response message structure
public static ResponseDC BuildResponseMsg(string ConnectionStr, string DBName, DCItem dcItem, bool isOK, string errMsg)
{
string ErrMsg;
string connectionDB = "SD_DBServer";
string methodName = MethodBase.GetCurrentMethod().Name;
BaseLog.LoggingBegin(logger, methodName);
string fTxnId = string.IsNullOrEmpty(dcItem.TxnId) ? "" : dcItem.TxnId;
string fBatchID = string.IsNullOrEmpty(dcItem.BatchID) ? "" : dcItem.BatchID;
string fType = string.IsNullOrEmpty(dcItem.Type) ? "" : dcItem.Type;
string fFamily = string.IsNullOrEmpty(dcItem.Family) ? "" : dcItem.Family;
string fIECPn = string.IsNullOrEmpty(dcItem.IECPn) ? "" : dcItem.IECPn;
string fVendor = string.IsNullOrEmpty(dcItem.Vendor) ? "" : dcItem.Vendor;
string fDefectCode = string.IsNullOrEmpty(dcItem.DefectCode) ? "" : dcItem.DefectCode;
string fStatus ="";
DCHeader dcHeader = new DCHeader();
List<DCDetail> dcDetail = new List<DCDetail>();
BaseLog.LoggingInfo(logger, "TxnId: \r\n{0}", fTxnId);
try
{
if (isOK)
{
//Update BatchID Status:10 (Request by IQS) when BatchID status is '00'(Print)
// SQL.UpdateDCStatusByBatchID(ConnectionStr, DBName, fBatchID, Status, Editor);
SQL.UpdateDCBstchStatusByBatchID(ConnectionStr, DBName, fBatchID, "10", "IQS");
//Get BatchID Status
fStatus = SQL.CheckDCBatchID(ConnectionStr, DBName, fBatchID);
List<DCDetail> QueryResult = new List<DCDetail>();
if (fType == "0")
QueryResult = SQL.QueryDCDetailByBatchID(ConnectionStr, DBName, fBatchID);
else
QueryResult = SQL.QueryDCDetailByCondition(ConnectionStr, DBName, fBatchID, fFamily, fIECPn, fVendor, fDefectCode);
if (QueryResult.Count == 0)
{
ErrMsg = "The BatchID : " + dcItem.BatchID + " no data! ";
throw new Exception(ErrMsg);
}
foreach (DCDetail item in QueryResult)
{
DCDetail DetailItem = new DCDetail
{
TxnId = fTxnId,
BatchID = item.BatchID,
Family = item.Family,
IECPn = item.IECPn,
PartType = item.PartType,
Vendor = item.Vendor,
DefectCode = item.DefectCode,
DefectDescr = item.DefectDescr,
PartSn = item.PartSn,
PartSerialNo = item.PartSerialNo
};
dcDetail.Add(DetailItem);
//log Send data
IMES.WS.Common.SQL.InsertSendData_DB(connectionDB, 0,
"SendDefectComponentDetail",
item.BatchID,
//item.PartSerialNo,
item.PartSn,
fTxnId,
//item.Family+","+item.IECPn+","+item.PartType+","+item.Vendor+","+item.DefectCode+","+item.PartSn,
item.Family + "," + item.IECPn + "," + item.PartType + "," + item.Vendor + "," + item.DefectCode + "," + item.PartSerialNo,
EnumMsgState.Sending,
DateTime.Now);
}
IMES.WS.Common.SQL.InsertTxnDataLog_DB(connectionDB, 0,
EnumMsgCategory.Response,
"ResponseDefectComponentDetail",
fBatchID,
fType,
fTxnId,
"T",
"",
EnumMsgState.Success,
"");
dcHeader.TxnId = fTxnId;
dcHeader.BatchID = fBatchID;
dcHeader.Status = fStatus;
dcHeader.Result = "T";
dcHeader.ErrorText = "";
BaseLog.LoggingInfo(logger, "dcHeader: \r\n{0}", ObjectTool.ObjectTostring(dcHeader));
}
else {
BaseLog.LoggingInfo(logger, "TxnId: \r\n{0}", fTxnId);
IMES.WS.Common.SQL.InsertTxnDataLog_DB(connectionDB, 0,
EnumMsgCategory.Response,
"ResponseDefectComponentDetail",
fBatchID,
fType,
fTxnId,
"F",
errMsg,
EnumMsgState.Fail,
"");
//if (fBatchID != "")
//{
// //Get BatchID Status
// fStatus = SQL.CheckDCBatchID(ConnectionStr, DBName, fBatchID);
//}
dcHeader.TxnId = fTxnId;
dcHeader.BatchID = fBatchID;
dcHeader.Status = fStatus;
dcHeader.Result = "F";
dcHeader.ErrorText = errMsg;
BaseLog.LoggingInfo(logger, "dcHeader: \r\n{0}", ObjectTool.ObjectTostring(dcHeader));
}
ResponseDC response = new ResponseDC();
response.dcHeader = dcHeader;
response.dcDetail = dcDetail.ToArray();
return response;
}
catch (Exception e)
{
BaseLog.LoggingError(logger, MethodBase.GetCurrentMethod(), e);
throw e;
}
finally
{
BaseLog.LoggingEnd(logger, methodName);
}
}