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); } }