web.config下連接數據庫加載的代碼 <configSections> <section name="dataConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=null"/> </configSections> <appSettings> <add key="CrystalImageCleaner-AutoStart" value="true"/> <add key="CrystalImageCleaner-Sleep" value="60000"/> <add key="CrystalImageCleaner-Age" value="120000"/> </appSettings> <dataConfiguration defaultDatabase="OracleConnection"/> <connectionStrings> <add name="OracleConnection" connectionString="user id=rms;data source=RMSTEST;password=rms;" providerName="System.Data.OracleClient"/> <add name="HrmConnection" connectionString="user id=HRM;data source=YTHRM;password=HRM;" providerName="System.Data.OracleClient"/> </connectionStrings> 以下是自己寫的一個類提供各種解決問題的方法 using System;using System.Data;using System.Data.Common;using System.Configuration;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;using System.Text;using System.Collections;using Microsoft.Practices.EnterpriseLibrary.Common;using Microsoft.Practices.EnterpriseLibrary.Configuration;using Microsoft.Practices.EnterpriseLibrary.Data;/**//// <summary>/// 名稱: Requirements/// 描述: 配件相關信息/// </summary>public class UnRequirements...{ ...#region private string fittingNo = string.Empty; // 配件編碼 private string category = string.Empty; // 種類 private string typeCode = string.Empty; // 類別代碼 private Hashtable propertyName = new Hashtable(); // 屬性名稱 private Hashtable propertyValue = new Hashtable(); // 屬性值 private DateTime fittingEnableDate = new DateTime(); // 配件生效日期 private DateTime fittingDisableDate = new DateTime(); // 配件失效日期 private string remark1 = string.Empty; // 配件規格備註 private string modifyStaff = string.Empty; // 維護人 private DateTime modifyDate = new DateTime(); // 維護日 private string manufacturer = string.Empty; // 供應商 private string mfPartNo = string.Empty; // 廠商料件編碼 private string spec = string.Empty; // 規格 private string reqBillNo = string.Empty; // 需求單號 private string workshopSection = string.Empty; // 工段 private string stationNo = string.Empty; // 機台編號 private string moldNo = string.Empty; // 模號 private string subMoldNo = string.Empty; // 件號 private string status = string.Empty; // 狀態 private int totalItemQty; // 總項數 private DateTime requestTime = new DateTime(); // 需求時間 private string requestMan = string.Empty; // 需求人 private string reqManName = string.Empty; // 需求人姓名 private string userNo = string.Empty; // 倉管員 private string taker = string.Empty; // 領用人 private string remark2 = string.Empty; // 領用備註 #endregion Property#region Property /**//// <summary> /// 配件編碼 /// </summary> public string FittingNo ...{ get ...{ return fittingNo; } set ...{ fittingNo = value; } } /**//// <summary> /// 種類 /// </summary> public string Category ...{ get ...{ return category; } set ...{ category = value; } } /**//// <summary> /// 類別代碼 /// </summary> public string TypeCode ...{ get ...{ return typeCode; } set ...{ typeCode = value; } } /**//// <summary> /// 屬性名稱 /// </summary> public Hashtable PropertyName ...{ get ...{ return propertyName; } set ...{ propertyName = value; } } /**//// <summary> /// 屬性值 /// </summary> public Hashtable PropertyValue ...{ get ...{ return propertyValue; } set ...{ propertyValue = value; } } /**//// <summary> /// 配件生效日期 /// </summary> public DateTime FittingEnableDate ...{ get ...{ return fittingEnableDate; } set ...{ fittingEnableDate = value; } } /**//// <summary> /// 配件失效日期 /// </summary> public DateTime FittingDisableDate ...{ get ...{ return fittingDisableDate; } set ...{ fittingDisableDate = value; } } /**//// <summary> /// 配件規格備註 /// </summary> public string Remark1 ...{ get ...{ return remark1; } set ...{ remark1 = value; } } /**//// <summary> /// 維護人 /// </summary> public string ModifyStaff ...{ get ...{ return modifyStaff; } set ...{ modifyStaff = value; } } /**//// <summary> /// 維護日 /// </summary> public DateTime ModifyDate ...{ get ...{ return modifyDate; } set ...{ modifyDate = value; } } /**//// <summary> /// 供應商 /// </summary> public string Manufacturer ...{ get ...{ return manufacturer; } set ...{ manufacturer = value; } } /**//// <summary> /// 廠商料件編碼 /// </summary> public string MfPartNo ...{ get ...{ return mfPartNo; } set ...{ mfPartNo = value; } } /**//// <summary> /// 規格 /// </summary> public string Spec ...{ get ...{ return spec; } set ...{ spec = value; } } /**//// <summary> /// 需求單號 /// </summary> public string ReqBillNo ...{ get ...{ return reqBillNo; } set ...{ reqBillNo = value; } } /**//// <summary> /// 工段 /// </summary> public string WorkshopSection ...{ get ...{ return workshopSection; } set ...{ workshopSection = value; } } /**//// <summary> /// 機台編號 /// </summary> public string StationNo ...{ get ...{ return stationNo; } set ...{ stationNo = value; } } /**//// <summary> /// 模號 /// </summary> public string MoldNo ...{ get ...{ return moldNo; } set ...{ moldNo = value; } } /**//// <summary> /// 件號 /// </summary> public string SubMoldNo ...{ get ...{ return subMoldNo; } set ...{ subMoldNo = value; } } /**//// <summary> /// 狀態 /// </summary> public string Status ...{ get ...{ return status; } set ...{ status = value; } } /**//// <summary> /// 總項數 /// </summary> public int TotalItemQty ...{ get ...{ return totalItemQty; } set ...{ totalItemQty = value; } } /**//// <summary> /// 需求時間 /// </summary> public DateTime RequestTime ...{ get ...{ return requestTime; } set ...{ requestTime = value; } } /**//// <summary> /// 需求人 /// </summary> public string RequestMan ...{ get ...{ return requestMan; } set ...{ requestMan = value; } } /**//// <summary> /// 需求人姓名 /// </summary> public string ReqManName ...{ get ...{ return reqManName; } set ...{ reqManName = value; } } /**//// <summary> /// 倉管員 /// </summary> public string UserNo ...{ get ...{ return userNo; } set ...{ userNo = value; } } /**//// <summary> /// 領用人 /// </summary> public string Taker ...{ get ...{ return taker; } set ...{ taker = value; } } /**//// <summary> /// 領用備註 /// </summary> public string Remark2 ...{ get ...{ return remark2; } set ...{ remark2 = value; } } #endregion public UnRequirements() ...{ } /**//// <summary> /// 名稱: InsertReq /// 描述: 插入新需求單 /// </summary> public void InsertReq() ...{ StringBuilder sql = new StringBuilder(""); sql.Append("INSERT INTO REQ_BILL_HEAD (REQ_BILL_NO, TOTAL_ITEM_QTY,MODIFYSTAFF,MODIFYDATE)"); sql.Append(" VALUES ("); sql.Append("'"); sql.Append(reqBillNo); sql.Append("',"); sql.Append(0); sql.Append(",'"); sql.Append(modifyStaff); sql.Append("',"); sql.Append("SYSDATE)"); try ...{ Database db = DatabaseFactory.CreateDatabase(); DbCommand cmd = db.GetSqlStringCommand(sql.ToString()); db.ExecuteNonQuery(cmd); } catch (System.Exception ex) ...{ throw new Exception(ex.Message); } } /**//// <summary> /// 名稱: UpdateReq /// 描述: 修改需求單 /// </summary> /// <param name="intScore">問題點原類別對應分數</param> public void UpdateReq(DataTable dtReqBody) ...{ // 事務是否成功完成標誌,Y:提交,N:回滾 string isCommit = "Y"; StringBuilder sql = new StringBuilder(""); StringBuilder sqlReqHead = new StringBuilder(""); StringBuilder sqlReqBody = new StringBuilder(""); StringBuilder sqlInventory = new StringBuilder(""); StringBuilder sqlTransaction = new StringBuilder(""); IDataReader idr = null; int seqNo = 0; Database tdb = DatabaseFactory.CreateDatabase(); DbConnection tconn = tdb.CreateConnection(); tconn.Open(); //事務開始 DbTransaction ttran = tconn.BeginTransaction(); DbCommand tcmd = null; try ...{ sql.Append("SELECT "); sql.Append(" REQ_BILL_NO"); sql.Append(" FROM "); sql.Append(" REQ_BILL_HEAD"); sql.Append(" WHERE"); sql.Append(" REQ_BILL_NO = '"); sql.Append(reqBillNo); sql.Append("'"); tcmd = tdb.GetSqlStringCommand(sql.ToString()); idr = tdb.ExecuteReader(tcmd, ttran); if (idr.Read()) ...{ sqlReqHead.Append("UPDATE "); sqlReqHead.Append(" REQ_BILL_HEAD"); sqlReqHead.Append(" SET WORKSHOP_SECTION = '"); sqlReqHead.Append(workshopSection); sqlReqHead.Append("',"); sqlReqHead.Append(" STATION_NO = '"); sqlReqHead.Append(stationNo); sqlReqHead.Append("',"); sqlReqHead.Append(" MOLD_NO = '"); sqlReqHead.Append(moldNo); sqlReqHead.Append("',"); sqlReqHead.Append(" SUB_MOLD_NO = '"); sqlReqHead.Append(subMoldNo); sqlReqHead.Append("',"); if (requestTime.Year != 1) ...{ sqlReqHead.Append(" REQUEST_TIME = TO_DATE('"); sqlReqHead.Append(requestTime.ToString("yyyy/MM/dd")); sqlReqHead.Append("','yyyy/MM/dd'),"); } sqlReqHead.Append(" REQUEST_MAN = '"); sqlReqHead.Append(requestMan); sqlReqHead.Append("',"); sqlReqHead.Append(" REQ_MAN_NAME = '"); sqlReqHead.Append(reqManName); sqlReqHead.Append("',"); sqlReqHead.Append(" REMARK1 = '"); sqlReqHead.Append(remark1); sqlReqHead.Append("',"); sqlReqHead.Append(" TOTAL_ITEM_QTY = "); sqlReqHead.Append(totalItemQty); sqlReqHead.Append(","); sqlReqHead.Append(" STATUS = '"); sqlReqHead.Append(status); sqlReqHead.Append("',"); sqlReqHead.Append(" MODIFYSTAFF = '"); sqlReqHead.Append(modifyStaff); sqlReqHead.Append("',"); sqlReqHead.Append(" MODIFYDATE = SYSDATE"); sqlReqHead.Append(" WHERE"); sqlReqHead.Append(" REQ_BILL_NO = '"); sqlReqHead.Append(reqBillNo); sqlReqHead.Append("'"); tcmd = tdb.GetSqlStringCommand(sqlReqHead.ToString()); tdb.ExecuteNonQuery(tcmd, ttran); } else ...{ sql.Remove(0, sql.Length); sql.Append("INSERT "); sql.Append(" INTO "); sql.Append(" REQ_BILL_HEAD ("); sql.Append(" REQ_BILL_NO,"); sql.Append(" WORKSHOP_SECTION,"); sql.Append(" STATION_NO,"); sql.Append(" MOLD_NO,"); sql.Append(" SUB_MOLD_NO,"); if (requestTime.Year != 1) ...{ sql.Append(" REQUEST_TIME,"); } sql.Append(" REQUEST_MAN,"); sql.Append(" REQ_MAN_NAME,"); sql.Append(" REMARK1,"); sql.Append(" STATUS,"); sql.Append(" TOTAL_ITEM_QTY,"); sql.Append(" MODIFYSTAFF,"); sql.Append(" MODIFYDATE)"); sql.Append(" VALUES ("); sql.Append("'"); sql.Append(reqBillNo); sql.Append("',"); sql.Append("'"); sql.Append(workshopSection); sql.Append("',"); sql.Append("'"); sql.Append(stationNo); sql.Append("',"); sql.Append("'"); sql.Append(moldNo); sql.Append("',"); sql.Append("'"); sql.Append(subMoldNo); sql.Append("',"); if (requestTime.Year != 1) ...{ sql.Append("TO_DATE('"); sql.Append(requestTime.ToString("yyyy/MM/dd")); sql.Append("','yyyy/MM/dd'),"); } sql.Append("'"); sql.Append(requestMan); sql.Append("',"); sql.Append("'"); sql.Append(reqManName); sql.Append("',"); sql.Append("'"); sql.Append(remark1); sql.Append("',"); sql.Append("'"); sql.Append(status); sql.Append("',"); sql.Append(totalItemQty); sql.Append(",'"); sql.Append(modifyStaff); sql.Append("',"); sql.Append("SYSDATE)"); tcmd = tdb.GetSqlStringCommand(sql.ToString()); tdb.ExecuteNonQuery(tcmd, ttran); } // 刪除需求單身 sqlReqBody.Append("DELETE"); sqlReqBody.Append(" FROM "); sqlReqBody.Append(" REQ_BILL_BODY"); sqlReqBody.Append(" WHERE REQ_BILL_NO = '"); sqlReqBody.Append(reqBillNo); sqlReqBody.Append("'"); tcmd = tdb.GetSqlStringCommand(sqlReqBody.ToString()); tdb.ExecuteNonQuery(tcmd, ttran); // 修改需求單身 if (dtReqBody.Rows.Count > 0) ...{ sqlReqBody.Remove(0, sqlReqBody.Length); // 邊歷哈希表 foreach (DataRow dr in dtReqBody.Rows) ...{ seqNo++; // 新增需求單身 sqlReqBody.Remove(0, sqlReqBody.Length); sqlReqBody.Append("INSERT"); sqlReqBody.Append(" INTO"); sqlReqBody.Append(" REQ_BILL_BODY ("); sqlReqBody.Append(" REQ_BILL_NO,"); sqlReqBody.Append(" PART_CODE,"); sqlReqBody.Append(" GRADE,"); sqlReqBody.Append(" SEQ_NO,"); sqlReqBody.Append(" QTY,"); sqlReqBody.Append(" PICKER,"); sqlReqBody.Append(" PICK_TIME,"); sqlReqBody.Append(" REMARK1,"); sqlReqBody.Append(" MODIFYSTAFF,"); sqlReqBody.Append(" MODIFYDATE)"); sqlReqBody.Append(" VALUES ( "); sqlReqBody.Append("'"); sqlReqBody.Append(reqBillNo); sqlReqBody.Append("',"); sqlReqBody.Append("'"); sqlReqBody.Append(dr[1].ToString()); sqlReqBody.Append("',"); sqlReqBody.Append("'"); sqlReqBody.Append(dr[3].ToString()); sqlReqBody.Append("',"); sqlReqBody.Append("'"); sqlReqBody.Append(seqNo); sqlReqBody.Append("',"); sqlReqBody.Append(dr[8].ToString()); sqlReqBody.Append(","); sqlReqBody.Append("'"); sqlReqBody.Append(modifyStaff); sqlReqBody.Append("',"); sqlReqBody.Append("SYSDATE,"); sqlReqBody.Append("'"); sqlReqBody.Append(dr[9].ToString()); sqlReqBody.Append("',"); sqlReqBody.Append("'"); sqlReqBody.Append(modifyStaff); sqlReqBody.Append("',"); sqlReqBody.Append("SYSDATE)"); tcmd = tdb.GetSqlStringCommand(sqlReqBody.ToString()); tdb.ExecuteNonQuery(tcmd, ttran); if (status == "F") ...{ idr = null; sqlInventory.Remove(0, sqlInventory.Length); sqlInventory.Append("SELECT "); sqlInventory.Append(" REAMER_NO "); sqlInventory.Append(" FROM "); sqlInventory.Append(" REAMER_INVENTORY"); sqlInventory.Append(" WHERE"); sqlInventory.Append(" REAMER_NO = '"); sqlInventory.Append(dr[1].ToString()); sqlInventory.Append("'"); tcmd = tdb.GetSqlStringCommand(sqlInventory.ToString()); idr = tdb.ExecuteReader(tcmd, ttran); sqlInventory.Remove(0, sqlInventory.Length); if (idr.Read()) ...{ sqlInventory.Append("UPDATE "); sqlInventory.Append(" REAMER_INVENTORY"); sqlInventory.Append(" SET REAMER_NO = '"); sqlInventory.Append(dr[1].ToString()); sqlInventory.Append("',"); sqlInventory.Append(" REQUIRE_QTY = NVL(REQUIRE_QTY,0) +"); sqlInventory.Append(dr[8].ToString()); sqlInventory.Append(","); sqlInventory.Append(" MODIFYSTAFF = '"); sqlInventory.Append(modifyStaff); sqlInventory.Append("',"); sqlInventory.Append(" MODIFYDATE = SYSDATE"); sqlInventory.Append(" WHERE"); sqlInventory.Append(" REAMER_NO = '"); sqlInventory.Append(dr[1].ToString()); sqlInventory.Append("'"); } else ...{ sqlInventory.Append("INSERT"); sqlInventory.Append(" INTO REAMER_INVENTORY ("); sqlInventory.Append(" REAMER_NO,"); sqlInventory.Append(" REQUIRE_QTY,"); sqlInventory.Append(" MODIFYSTAFF,"); sqlInventory.Append(" MODIFYDATE)"); sqlInventory.Append(" VALUES("); sqlInventory.Append("'"); sqlInventory.Append(dr[1].ToString()); sqlInventory.Append("',"); sqlInventory.Append(dr[8].ToString()); sqlInventory.Append(",'"); sqlInventory.Append(modifyStaff); sqlInventory.Append("',"); sqlInventory.Append("SYSDATE)"); } tcmd = tdb.GetSqlStringCommand(sqlInventory.ToString()); tdb.ExecuteNonQuery(tcmd, ttran); sqlTransaction.Remove(0, sqlTransaction.Length); sqlTransaction.Append("INSERT "); sqlTransaction.Append(" INTO INV_TRANSACTION ("); sqlTransaction.Append(" TRA_SEQ_NO,"); sqlTransaction.Append(" PART_NO,"); sqlTransaction.Append(" GRADE,"); sqlTransaction.Append(" WAREHOUSE,"); sqlTransaction.Append(" TRANS_TYPE,"); sqlTransaction.Append(" BILL_NO,"); sqlTransaction.Append(" SEQ_NO,"); sqlTransaction.Append(" TRANS_TIME,"); sqlTransaction.Append(" QTY,"); sqlTransaction.Append(" STATE,"); sqlTransaction.Append(" WORK_NO,"); sqlTransaction.Append(" WORK_NAME,"); sqlTransaction.Append(" CHANGE_TYPE,"); sqlTransaction.Append(" REMARK1,"); sqlTransaction.Append(" MODIFYSTAFF,"); sqlTransaction.Append(" MODIFYDATE)"); sqlTransaction.Append(" VALUES ( "); sqlTransaction.Append("(SELECT BILL_NO"); sqlTransaction.Append(" FROM BILL_GENERATE"); sqlTransaction.Append(" WHERE BILL_TYPE = 'TRA')"); sqlTransaction.Append("||'-'||'"); sqlTransaction.Append(System.DateTime.Today.Year.ToString().Substring(3, 1)); sqlTransaction.Append("'||'"); if (System.DateTime.Today.Month.ToString() == "10") ...{ sqlTransaction.Append("A"); } else if (System.DateTime.Today.Month.ToString() == "11") ...{ sqlTransaction.Append("B"); } else if (System.DateTime.Today.Month.ToString() == "12") ...{ sqlTransaction.Append("C"); } else ...{ sqlTransaction.Append(System.DateTime.Today.Month.ToString()); } sqlTransaction.Append("'||"); sqlTransaction.Append("(SELECT"); sqlTransaction.Append(" NVL(LPAD(MAX(SUBSTR(TRA_SEQ_NO,LENGTH(TRA_SEQ_NO)-4,5))+1,5,'0'),'00001')"); sqlTransaction.Append(" FROM"); sqlTransaction.Append(" INV_TRANSACTION),"); sqlTransaction.Append("'"); sqlTransaction.Append(dr[1].ToString()); sqlTransaction.Append("',"); sqlTransaction.Append("'"); sqlTransaction.Append(dr[3].ToString()); sqlTransaction.Append("',"); sqlTransaction.Append("'V','V','"); sqlTransaction.Append(reqBillNo); sqlTransaction.Append("',"); sqlTransaction.Append("'"); sqlTransaction.Append(seqNo); sqlTransaction.Append("',"); sqlTransaction.Append("SYSDATE,"); sqlTransaction.Append(dr[8].ToString()); sqlTransaction.Append(","); sqlTransaction.Append("'-',"); sqlTransaction.Append("'"); sqlTransaction.Append(modifyStaff); sqlTransaction.Append("',"); sqlTransaction.Append("'"); sqlTransaction.Append(modifyStaff); sqlTransaction.Append("',"); //sqlTransaction.Append("(SELECT USERNAME"); //sqlTransaction.Append(" FROM USERINFORMATION"); //sqlTransaction.Append(" WHERE USERID = '"); //sqlTransaction.Append(modifyStaff); //sqlTransaction.Append("'),"); sqlTransaction.Append("'B',"); sqlTransaction.Append("'"); sqlTransaction.Append(dr[9].ToString()); sqlTransaction.Append("',"); sqlTransaction.Append("'"); sqlTransaction.Append(modifyStaff); sqlTransaction.Append("',"); sqlTransaction.Append("SYSDATE)"); tcmd = tdb.GetSqlStringCommand(sqlTransaction.ToString()); tdb.ExecuteNonQuery(tcmd, ttran); } } } } catch (Exception ex) ...{ isCommit = "N"; } // 如所有操作未能全部成功完成,則回滾,否則提交 if (isCommit == "N") ...{ ttran.Rollback(); throw new Exception("操作失敗!"); } else ...{ //事務提交 ttran.Commit(); } tconn.Close(); } /**//// <summary> /// 名稱: DeleteReq /// 描述: 刪除需求單 /// </summary> /// <param name="intScore">問題點原類別對應分數</param> public void DeleteReq() ...{ StringBuilder sql = new StringBuilder(""); sql.Append("UPDATE "); sql.Append(" REQ_BILL_HEAD"); sql.Append(" SET ISLEAGL = 'N'"); sql.Append(" WHERE"); sql.Append(" REQ_BILL_NO = '"); sql.Append(reqBillNo); sql.Append("'"); try ...{ Database db = DatabaseFactory.CreateDatabase(); DbCommand cmd = db.GetSqlStringCommand(sql.ToString()); db.ExecuteNonQuery(cmd); } catch (System.Exception ex) ...{ throw new Exception(ex.Message); } } /**//// <summary> /// 名稱: GetType /// 描述: 查詢配件類型信息 /// </summary> /// <param name="ht"></param> /// <returns></returns> public IDataReader GetReamerType(Hashtable ht) ...{ StringBuilder sql = new StringBuilder(""); IDataReader dr = null; sql.Append("SELECT "); sql.Append(" DISTINCT "); sql.Append(" TYPE_CODE,"); sql.Append(" TYPE_NAME"); sql.Append(" FROM"); sql.Append(" REAMER_TYPE"); sql.Append(" WHERE "); if (ht.Count > 0) ...{ // 邊歷哈希表 foreach (DictionaryEntry de in ht) ...{ sql.Append(de.Key.ToString()); sql.Append(" LIKE '"); sql.Append(de.Value.ToString()); sql.Append("__'"); } } try ...{ // 連接數據庫進行查詢,得到信息數據表 Database db = DatabaseFactory.CreateDatabase(); DbCommand cmd = db.GetSqlStringCommand(sql.ToString()); dr = db.ExecuteReader(cmd); // 返回信息數據表 return dr; } catch (Exception ex) ...{ throw new System.Exception(ex.Message); } } /**//// <summary> /// 名稱: GetSelList /// 描述: 查詢配件信息列表 /// </summary> /// <param name="queryType">查詢條件區分</param> /// <returns>配件信息表</returns> public DataTable GetSelList(int queryType) ...{ StringBuilder sql = new StringBuilder(""); GetPropertyName(); sql.Append("SELECT "); sql.Append(" ROWNUM,"); sql.Append(" FSC.*"); sql.Append(" FROM "); sql.Append(" (SELECT"); if (typeCode.Substring(0, 1) == "A") ...{ sql.Append(" FS.REAMER_NO,"); } else ...{ sql.Append(" FS.FITTING_NO,"); } sql.Append(" (SELECT RT.TYPE_NAME"); sql.Append(" FROM REAMER_TYPE RT"); sql.Append(" WHERE RT.TYPE_CODE = SUBSTR(FS.TYPE_CODE,1,1)) AS TOP_TYPE,"); sql.Append(" (SELECT RT.TYPE_NAME"); sql.Append(" FROM REAMER_TYPE RT"); sql.Append(" WHERE RT.TYPE_CODE = SUBSTR(FS.TYPE_CODE,1,3)) AS MIDDLE_TYPE,"); //sql.Append("||'/'"); sql.Append(" R.TYPE_NAME AS TYPE_NAME,"); sql.Append(" RSS.SPEC,"); foreach (DictionaryEntry de in propertyName) ...{ sql.Append(de.Key.ToString()); sql.Append(","); } sql.Append(" RWI.GRADE,"); sql.Append(" RWI.STOCK_QTY"); sql.Append(" FROM "); if (typeCode.Substring(0, 1) == "A") ...{ sql.Append(" REAMER_SPEC FS,"); } else ...{ sql.Append(" FITTING_SPEC FS,"); } sql.Append(" REAMER_SPEC_SUPPLY RSS,"); sql.Append(" REAMER_WAREHOUSE_INVENTORY RWI,"); sql.Append(" REAMER_TYPE R"); sql.Append(" WHERE "); sql.Append(" FS.TYPE_CODE = R.TYPE_CODE"); sql.Append(" AND RWI.WAREHOUSE IN ('NEW','GRADE')"); if (typeCode.Substring(0, 1) == "A") ...{ sql.Append(" AND FS.REAMER_NO = RSS.SPEC_NO"); sql.Append(" AND RWI.REAMER_NO = FS.REAMER_NO"); } else ...{ sql.Append(" AND FS.FITTING_NO = RSS.SPEC_NO"); sql.Append(" AND RWI.REAMER_NO = FS.FITTING_NO"); } sql.Append(" AND FS.TYPE_CODE = '"); sql.Append(typeCode); sql.Append("'"); // 設定查詢條件 if (queryType == 0) ...{ sql.Append(" AND 1 <> 1"); } if (typeCode.Substring(0, 1) == "A") ...{ sql.Append(" ORDER BY FS.REAMER_NO"); } else ...{ sql.Append(" ORDER BY FS.FITTING_NO"); } sql.Append(") FSC"); try ...{ // 連接數據庫進行查詢,得到信息數據表 Database db = DatabaseFactory.CreateDatabase(); DbCommand cmd = db.GetSqlStringCommand(sql.ToString()); DataTable dt = db.ExecuteDataSet(cmd).Tables[0]; // 返回信息數據表 return dt; } catch (Exception ex) ...{ throw new System.Exception(ex.Message); } } /**//// <summary> /// 名稱: GetSelList /// 描述: 查詢配件信息列表 /// </summary> /// <param name="queryType">查詢條件區分</param> /// <returns>配件信息表</returns> public DataTable GetSelList(int queryType, string middleType, string bottomType) ...{ StringBuilder sql = new StringBuilder(""); GetPropertyName(middleType, bottomType); sql.Append("SELECT "); sql.Append(" ROWNUM,"); sql.Append(" FSC.*"); sql.Append(" FROM "); sql.Append(" (SELECT"); if (typeCode.Substring(0, 1) == "A") ...{ sql.Append(" FS.REAMER_NO,"); } else ...{ sql.Append(" FS.FITTING_NO,"); } sql.Append(" (SELECT RT.TYPE_NAME"); sql.Append(" FROM REAMER_TYPE RT"); sql.Append(" WHERE RT.TYPE_CODE = SUBSTR(FS.TYPE_CODE,1,1)) AS TOP_TYPE,"); sql.Append(" (SELECT RT.TYPE_NAME"); sql.Append(" FROM REAMER_TYPE RT"); sql.Append(" WHERE RT.TYPE_CODE = SUBSTR(FS.TYPE_CODE,1,3)) AS MIDDLE_TYPE,"); sql.Append(" R.TYPE_NAME AS TYPE_NAME,"); if (typeCode.Substring(0, 1) == "A") ...{ sql.Append(" A,"); sql.Append(" RSS.SPEC,"); foreach (DictionaryEntry de in propertyName) ...{ if (de.Key.ToString() != "A") ...{ sql.Append(de.Key.ToString()); sql.Append(","); } } } else ...{ sql.Append(" RSS.SPEC,"); sql.Append(" A,"); foreach (DictionaryEntry de in propertyName) ...{ if (de.Key.ToString() != "A") ...{ sql.Append(de.Key.ToString()); sql.Append(","); } } } sql.Append(" RWI.GRADE,"); sql.Append(" RWI.STOCK_QTY"); sql.Append(" FROM "); if (typeCode.Substring(0, 1) == "A") ...{ sql.Append(" REAMER_SPEC FS,"); } else ...{ sql.Append(" FITTING_SPEC FS,"); } sql.Append(" REAMER_SPEC_SUPPLY RSS,"); sql.Append(" REAMER_WAREHOUSE_INVENTORY RWI,"); sql.Append(" REAMER_TYPE R"); sql.Append(" WHERE "); sql.Append(" FS.TYPE_CODE = R.TYPE_CODE"); sql.Append(" AND RWI.WAREHOUSE IN ('NEW','GRADE')"); if (typeCode.Substring(0, 1) == "A") ...{ sql.Append(" AND FS.REAMER_NO = RSS.SPEC_NO"); sql.Append(" AND RWI.REAMER_NO = FS.REAMER_NO"); } else ...{ sql.Append(" AND FS.FITTING_NO = RSS.SPEC_NO"); sql.Append(" AND RWI.REAMER_NO = FS.FITTING_NO"); } sql.Append(" AND FS.TYPE_CODE LIKE '"); sql.Append(typeCode); sql.Append("%'"); // 設定查詢條件 if (queryType == 0) ...{ sql.Append(" AND 1 <> 1"); } if (typeCode.Substring(0, 1) == "A") ...{ sql.Append(" ORDER BY FS.REAMER_NO"); } else ...{ sql.Append(" ORDER BY FS.FITTING_NO"); } sql.Append(") FSC"); try ...{ // 連接數據庫進行查詢,得到信息數據表 Database db = DatabaseFactory.CreateDatabase(); DbCommand cmd = db.GetSqlStringCommand(sql.ToString()); DataTable dt = db.ExecuteDataSet(cmd).Tables[0]; // 返回信息數據表 return dt; } catch (Exception ex) ...{ throw new System.Exception(ex.Message); } } /**//// <summary> /// 名稱: GetPropertyName /// 描述: 查詢配件詳細信息 /// </summary> public void GetPropertyName() ...{ IDataReader dr = null; StringBuilder sql = new StringBuilder(""); string[] abcdefghij = ...{ "A", "B", "C", "D", "E", "F", "G", "H", "I", "J" }; if (propertyName.Count > 0) ...{ propertyName.Clear(); } sql.Append("SELECT "); sql.Append(" SN.A_NAME,"); sql.Append(" SN.B_NAME,"); sql.Append(" SN.C_NAME,"); sql.Append(" SN.D_NAME,"); sql.Append(" SN.E_NAME,"); sql.Append(" SN.F_NAME,"); sql.Append(" SN.G_NAME,"); sql.Append(" SN.H_NAME,"); sql.Append(" SN.I_NAME,"); sql.Append(" SN.J_NAME"); sql.Append(" FROM "); sql.Append(" SPEC_NAME SN"); sql.Append(" WHERE "); sql.Append(" SN.TYPE_CODE = '"); sql.Append(typeCode); sql.Append("'"); try ...{ Database db = DatabaseFactory.CreateDatabase(); DbCommand cmd = db.GetSqlStringCommand(sql.ToString()); dr = db.ExecuteReader(cmd); // 將讀取出的數據存入類成員變量 if (dr.Read()) ...{ for (int iLoop = 0; iLoop < 10; iLoop++) ...{ if (dr[abcdefghij[iLoop] + "_NAME"].ToString().Length > 0) ...{ propertyName.Add(abcdefghij[iLoop], dr[abcdefghij[iLoop] + "_NAME"].ToString()); } } } else ...{ sql.Remove(0, sql.Length); sql.Append("SELECT "); sql.Append(" SN.A_NAME,"); sql.Append(" SN.B_NAME,"); sql.Append(" SN.C_NAME,"); sql.Append(" SN.D_NAME,"); sql.Append(" SN.E_NAME,"); sql.Append(" SN.F_NAME,"); sql.Append(" SN.G_NAME,"); sql.Append(" SN.H_NAME,"); sql.Append(" SN.I_NAME,"); sql.Append(" SN.J_NAME"); sql.Append(" FROM "); sql.Append(" SPEC_NAME SN"); sql.Append(" WHERE "); sql.Append(" SN.TYPE_CODE = '"); sql.Append(typeCode.Substring(0, 3)); sql.Append("'"); cmd = db.GetSqlStringCommand(sql.ToString()); dr = db.ExecuteReader(cmd); // 將讀取出的數據存入類成員變量 if (dr.Read()) ...{ for (int iLoop = 0; iLoop < 10; iLoop++) ...{ if (dr[abcdefghij[iLoop] + "_NAME"].ToString().Length > 0) ...{ propertyName.Add(abcdefghij[iLoop], dr[abcdefghij[iLoop] + "_NAME"].ToString()); } } } } } catch (Exception ex) ...{ throw new System.Exception(ex.Message); } finally ...{ if (dr != null) ...{ dr.Close(); } } } /**//// <summary> /// 名稱: GetPropertyName /// 描述: 查詢耗材/配件詳細信息 /// </summary> /// <param name="middleType"></param> /// <param name="bottomType"></param> public void GetPropertyName(string middleType, string bottomType) ...{ IDataReader dr = null; StringBuilder sql = new StringBuilder(""); string[] abcdefghij = ...{ "A", "B", "C", "D", "E", "F", "G", "H", "I", "J" }; Database db = DatabaseFactory.CreateDatabase(); DbCommand cmd = null; if (propertyName.Count > 0) ...{ propertyName.Clear(); } try ...{ if (bottomType != "ALL") ...{ sql.Append("SELECT "); sql.Append(" SN.A_NAME,"); sql.Append(" SN.B_NAME,"); sql.Append(" SN.C_NAME,"); sql.Append(" SN.D_NAME,"); sql.Append(" SN.E_NAME,"); sql.Append(" SN.F_NAME,"); sql.Append(" SN.G_NAME,"); sql.Append(" SN.H_NAME,"); sql.Append(" SN.I_NAME,"); sql.Append(" SN.J_NAME"); sql.Append(" FROM "); sql.Append(" SPEC_NAME SN"); sql.Append(" WHERE "); sql.Append(" SN.TYPE_CODE = '"); sql.Append(bottomType); sql.Append("'"); cmd = db.GetSqlStringCommand(sql.ToString()); dr = db.ExecuteReader(cmd); // 將讀取出的數據存入類成員變量 if (dr.Read()) ...{ for (int iLoop = 0; iLoop < 10; iLoop++) ...{ if (dr[abcdefghij[iLoop] + "_NAME"].ToString().Length > 0) ...{ propertyName.Add(abcdefghij[iLoop], dr[abcdefghij[iLoop] + "_NAME"].ToString()); } } } else ...{ sql.Remove(0, sql.Length); sql.Append("SELECT "); sql.Append(" SN.A_NAME,"); sql.Append(" SN.B_NAME,"); sql.Append(" SN.C_NAME,"); sql.Append(" SN.D_NAME,"); sql.Append(" SN.E_NAME,"); sql.Append(" SN.F_NAME,"); sql.Append(" SN.G_NAME,"); sql.Append(" SN.H_NAME,"); sql.Append(" SN.I_NAME,"); sql.Append(" SN.J_NAME"); sql.Append(" FROM "); sql.Append(" SPEC_NAME SN"); sql.Append(" WHERE "); sql.Append(" SN.TYPE_CODE = '"); sql.Append(bottomType.Substring(0, 3)); sql.Append("'"); cmd = db.GetSqlStringCommand(sql.ToString()); dr = db.ExecuteReader(cmd); // 將讀取出的數據存入類成員變量 if (dr.Read()) ...{ for (int iLoop = 0; iLoop < 10; iLoop++) ...{ if (dr[abcdefghij[iLoop] + "_NAME"].ToString().Length > 0) ...{ propertyName.Add(abcdefghij[iLoop], dr[abcdefghij[iLoop] + "_NAME"].ToString()); } } } } } else ...{ sql.Append("SELECT "); sql.Append(" SN.A_NAME,"); sql.Append(" SN.B_NAME,"); sql.Append(" SN.C_NAME,"); sql.Append(" SN.D_NAME,"); sql.Append(" SN.E_NAME,"); sql.Append(" SN.F_NAME,"); sql.Append(" SN.G_NAME,"); sql.Append(" SN.H_NAME,"); sql.Append(" SN.I_NAME,"); sql.Append(" SN.J_NAME"); sql.Append(" FROM "); sql.Append(" SPEC_NAME SN"); sql.Append(" WHERE "); sql.Append(" SN.TYPE_CODE LIKE '"); sql.Append(middleType); sql.Append("%'"); cmd = db.GetSqlStringCommand(sql.ToString()); dr = db.ExecuteReader(cmd); while (dr.Read()) ...{ for (int iLoop = 0; iLoop < 10; iLoop++) ...{ if (dr[abcdefghij[iLoop] + "_NAME"].ToString().Length > 0) ...{ if (propertyName[abcdefghij[iLoop]] == null) ...{ propertyName.Add(abcdefghij[iLoop], dr[abcdefghij[iLoop] + "_NAME"].ToString()); } } } } } } catch (Exception ex) ...{ throw new System.Exception(ex.Message); } finally ...{ if (dr != null) ...{ dr.Close(); } } } /**//// <summary> /// 名稱: GetReqBodyList /// 描述: 加載需求單身信息 /// </summary> public DataTable GetReqBodyList(int queryType) ...{ StringBuilder sql = new StringBuilder(""); string[] abcdefghij = ...{ "A", "B", "C", "D", "E", "F", "G", "H", "I", "J" }; sql.Append("SELECT "); sql.Append(" ROWNUM,"); sql.Append(" FRR.*"); sql.Append(" FROM "); sql.Append(" (SELECT FR.*"); sql.Append(" FROM "); sql.Append(" (SELECT "); sql.Append(" RS.REAMER_NO AS FR_NO,"); sql.Append(" RS.TYPE_CODE,"); sql.Append(" RBB.GRADE,"); sql.Append(" (SELECT RT.TYPE_NAME"); sql.Append(" FROM REAMER_TYPE RT"); sql.Append(" WHERE RT.TYPE_CODE = SUBSTR(RS.TYPE_CODE,1,1)) AS TOP_TYPE,"); sql.Append(" (SELECT RT.TYPE_NAME"); sql.Append(" FROM REAMER_TYPE RT"); sql.Append(" WHERE RT.TYPE_CODE = SUBSTR(RS.TYPE_CODE,1,3)) AS MIDDLE_TYPE,"); sql.Append(" R.TYPE_NAME AS TYPE_NAME,"); sql.Append(" '<span style=''color: #006600''>'||SN.A_NAME||':</span><span style=''color: #0000ff''>['||RS.A||']</span> '||"); sql.Append(" '<span style=''color: #006600''>規格:</span><span style=''color: #0000ff''>['||RSS.SPEC||']</span> '||"); for (int iLoop = 1; iLoop < 10; iLoop++) ...{ sql.Append(" CASE WHEN RS."); sql.Append(abcdefghij[iLoop]); sql.Append(" IS NOT NULL THEN '<span style=''color: #006600''>'||SN."); sql.Append(abcdefghij[iLoop]); sql.Append("_NAME||':</span><span style=''color: #0000ff''>['"); sql.Append(" ELSE '' END"); sql.Append(" || CASE WHEN RS."); sql.Append(abcdefghij[iLoop]); sql.Append(" IS NOT NULL THEN RS."); sql.Append(abcdefghij[iLoop]); sql.Append("||']</span> ' ELSE '' END ||"); } sql.Append(" '<span style=''color: #006600''>等級:</span><span style=''color: #0000ff''>['||RBB.GRADE||']</span>'"); sql.Append(" AS ATTR,"); sql.Append(" RBB.QTY,"); sql.Append(" RBB.REMARK1"); sql.Append(" FROM "); sql.Append(" REAMER_SPEC RS,"); sql.Append(" REQ_BILL_BODY RBB,"); sql.Append(" REAMER_SPEC_SUPPLY RSS,"); sql.Append(" REAMER_TYPE R,"); sql.Append(" SPEC_NAME SN"); sql.Append(" WHERE "); sql.Append(" RS.REAMER_NO = RBB.PART_CODE"); sql.Append(" AND RS.REAMER_NO = RSS.SPEC_NO"); sql.Append(" AND RS.TYPE_CODE = R.TYPE_CODE"); sql.Append(" AND ((RS.TYPE_CODE = SN.TYPE_CODE) OR (SUBSTR(RS.TYPE_CODE,1,3) = SN.TYPE_CODE))"); sql.Append(" AND RBB.REQ_BILL_NO = '"); sql.Append(reqBillNo); sql.Append("'"); sql.Append(" UNION"); sql.Append(" SELECT "); sql.Append(" FS.FITTING_NO AS FR_NO,"); sql.Append(" FS.TYPE_CODE,"); sql.Append(" RBB.GRADE,"); sql.Append(" (SELECT RT.TYPE_NAME"); sql.Append(" FROM REAMER_TYPE RT"); sql.Append(" WHERE RT.TYPE_CODE = SUBSTR(FS.TYPE_CODE,1,1)) AS TOP_TYPE,"); sql.Append(" (SELECT RT.TYPE_NAME"); sql.Append(" FROM REAMER_TYPE RT"); sql.Append(" WHERE RT.TYPE_CODE = SUBSTR(FS.TYPE_CODE,1,3)) AS MIDDLE_TYPE,"); sql.Append(" R.TYPE_NAME AS TYPE_NAME,"); sql.Append(" '<span style=''color: #006600''>規格:</span><span style=''color: #0000ff''>['||RSS.SPEC||']</span> '||"); for (int iLoop = 0; iLoop < 8; iLoop++) ...{ sql.Append(" CASE WHEN FS."); sql.Append(abcdefghij[iLoop]); sql.Append(" IS NOT NULL THEN '<span style=''color: #006600''>'||SN."); sql.Append(abcdefghij[iLoop]); sql.Append("_NAME||':</span><span style=''color: #0000ff''>['"); sql.Append(" ELSE '' END"); sql.Append(" || CASE WHEN FS."); sql.Append(abcdefghij[iLoop]); sql.Append(" IS NOT NULL THEN FS."); sql.Append(abcdefghij[iLoop]); sql.Append("||']</span> ' ELSE '' END ||"); } sql.Append(" '' AS ATTR,"); sql.Append(" RBB.QTY,"); sql.Append(" RBB.REMARK1"); sql.Append(" RBB.REVERT_QTY"); sql.Append(" FROM "); sql.Append(" FITTING_SPEC FS,"); sql.Append(" REQ_BILL_BODY RBB,"); sql.Append(" REAMER_SPEC_SUPPLY RSS,"); sql.Append(" REAMER_TYPE R,"); sql.Append(" SPEC_NAME SN"); sql.Append(" WHERE "); sql.Append(" FS.FITTING_NO = RBB.PART_CODE"); sql.Append(" AND FS.FITTING_NO = RSS.SPEC_NO"); sql.Append(" AND FS.TYPE_CODE = R.TYPE_CODE"); sql.Append(" AND ((FS.TYPE_CODE = SN.TYPE_CODE) OR (SUBSTR(FS.TYPE_CODE,1,3) = SN.TYPE_CODE))"); sql.Append(" AND RBB.REQ_BILL_NO = '"); sql.Append(reqBillNo); sql.Append("'"); sql.Append(" ) FR"); sql.Append(" ORDER BY FR.TYPE_CODE,FR.FR_NO) FRR"); try ...{ // 連接數據庫進行查詢,得到信息數據表 Database db = DatabaseFactory.CreateDatabase(); DbCommand cmd = db.GetSqlStringCommand(sql.ToString()); DataTable dt = db.ExecuteDataSet(cmd).Tables[0]; // 返回信息數據表 return dt; } catch (Exception ex) ...{ throw new System.Exception(ex.Message); } } /**//// <summary> /// 名稱: GetReqBodyList /// 描述: 加載需求單身信息 /// </summary> /// <param name="ht"></param> /// <returns></returns> public DataTable GetReqBodyList(Hashtable ht) ...{ StringBuilder sql = new StringBuilder(""); int htCount = 0; string[] abcdefghij = ...{ "A", "B", "C", "D", "E", "F", "G", "H", "I", "J" }; sql.Append("SELECT "); sql.Append(" ROWNUM,"); sql.Append(" FRR.*"); sql.Append(" FROM "); sql.Append(" (SELECT FR.*"); sql.Append(" FROM "); sql.Append(" ("); foreach (DictionaryEntry de in ht) ...{ sql.Append(" SELECT "); sql.Append(" RS.REAMER_NO AS FR_NO,"); sql.Append(" RS.TYPE_CODE,"); sql.Append(" RWI.GRADE,"); sql.Append(" (SELECT RT.TYPE_NAME"); sql.Append(" FROM REAMER_TYPE RT"); sql.Append(" WHERE RT.TYPE_CODE = SUBSTR(RS.TYPE_CODE,1,1)) AS TOP_TYPE,"); sql.Append(" (SELECT RT.TYPE_NAME"); sql.Append(" FROM REAMER_TYPE RT"); sql.Append(" WHERE RT.TYPE_CODE = SUBSTR(RS.TYPE_CODE,1,3)) AS MIDDLE_TYPE,"); sql.Append(" R.TYPE_NAME AS TYPE_NAME,"); sql.Append(" '<span style=''color: #006600''>'||SN.A_NAME||':</span><span style=''color: #0000ff''>['||RS.A||']</span> '||"); sql.Append(" '<span style=''color: #006600''>規格:</span><span style=''color: #0000ff''>['||RSS.SPEC||']</span> '||"); for (int iLoop = 1; iLoop < 10; iLoop++) ...{ sql.Append(" CASE WHEN RS."); sql.Append(abcdefghij[iLoop]); sql.Append(" IS NOT NULL THEN '<span style=''color: #006600''>'||SN."); sql.Append(abcdefghij[iLoop]); sql.Append("_NAME||':</span><span style=''color: #0000ff''>['"); sql.Append(" ELSE '' END"); sql.Append(" || CASE WHEN RS."); sql.Append(abcdefghij[iLoop]); sql.Append(" IS NOT NULL THEN RS."); sql.Append(abcdefghij[iLoop]); sql.Append("||']</span> ' ELSE '' END ||"); } sql.Append(" '<span style=''color: #006600''>等級:</span><span style=''color: #0000ff''>['||RWI.GRADE||']</span>'"); sql.Append(" AS ATTR,"); sql.Append(" 0,"); sql.Append(" ''"); sql.Append(" FROM "); sql.Append(" REAMER_SPEC RS,"); sql.Append(" REAMER_WAREHOUSE_INVENTORY RWI,"); sql.Append(" REAMER_SPEC_SUPPLY RSS,"); sql.Append(" REAMER_TYPE R,"); sql.Append(" SPEC_NAME SN"); sql.Append(" WHERE "); sql.Append(" RS.REAMER_NO = RWI.REAMER_NO"); sql.Append(" AND RS.REAMER_NO = RSS.SPEC_NO"); sql.Append(" AND RS.TYPE_CODE = R.TYPE_CODE"); sql.Append(" AND ((RS.TYPE_CODE = SN.TYPE_CODE) OR (SUBSTR(RS.TYPE_CODE,1,3) = SN.TYPE_CODE))"); sql.Append(" AND RWI.WAREHOUSE IN ('NEW','GRADE')"); sql.Append(" AND RS.REAMER_NO = '"); sql.Append(de.Key.ToString()); sql.Append("'"); sql.Append(" AND RWI.GRADE = '"); sql.Append(de.Value.ToString()); sql.Append("'"); sql.Append(" UNION"); sql.Append(" SELECT "); sql.Append(" FS.FITTING_NO AS FR_NO,"); sql.Append(" FS.TYPE_CODE,"); sql.Append(" RWI.GRADE,"); sql.Append(" (SELECT RT.TYPE_NAME"); sql.Append(" FROM REAMER_TYPE RT"); sql.Append(" WHERE RT.TYPE_CODE = SUBSTR(FS.TYPE_CODE,1,1)) AS TOP_TYPE,"); sql.Append(" (SELECT RT.TYPE_NAME"); sql.Append(" FROM REAMER_TYPE RT"); sql.Append(" WHERE RT.TYPE_CODE = SUBSTR(FS.TYPE_CODE,1,3)) AS MIDDLE_TYPE,"); sql.Append(" R.TYPE_NAME AS TYPE_NAME,"); sql.Append(" '<span style=''color: #006600''>規格:</span><span style=''color: #0000ff''>['||RSS.SPEC||']</span> '||"); for (int iLoop = 0; iLoop < 8; iLoop++) ...{ sql.Append(" CASE WHEN FS."); sql.Append(abcdefghij[iLoop]); sql.Append(" IS NOT NULL THEN '<span style=''color: #006600''>'||SN."); sql.Append(abcdefghij[iLoop]); sql.Append("_NAME||':</span><span style=''color: #0000ff''>['"); sql.Append(" ELSE '' END"); sql.Append(" || CASE WHEN FS."); sql.Append(abcdefghij[iLoop]); sql.Append(" IS NOT NULL THEN FS."); sql.Append(abcdefghij[iLoop]); sql.Append("||']</span> ' ELSE '' END ||"); } sql.Append(" '' AS ATTR,"); sql.Append(" 0,"); sql.Append(" ''"); sql.Append(" FROM "); sql.Append(" FITTING_SPEC FS,"); sql.Append(" REAMER_WAREHOUSE_INVENTORY RWI,"); sql.Append(" REAMER_SPEC_SUPPLY RSS,"); sql.Append(" REAMER_TYPE R,"); sql.Append(" SPEC_NAME SN"); sql.Append(" WHERE "); sql.Append(" FS.FITTING_NO = RWI.REAMER_NO"); sql.Append(" AND FS.FITTING_NO = RSS.SPEC_NO"); sql.Append(" AND FS.TYPE_CODE = R.TYPE_CODE"); sql.Append(" AND ((FS.TYPE_CODE = SN.TYPE_CODE) OR (SUBSTR(FS.TYPE_CODE,1,3) = SN.TYPE_CODE))"); sql.Append(" AND RWI.WAREHOUSE IN ('NEW','GRADE')"); sql.Append(" AND FS.FITTING_NO = '"); sql.Append(de.Key.ToString()); sql.Append("'"); sql.Append(" AND RWI.GRADE = '"); sql.Append(de.Value.ToString()); sql.Append("'"); if (htCount < ht.Count - 1) ...{ sql.Append(" UNION "); htCount++; } } sql.Append(" ) FR"); sql.Append(" ORDER BY FR.TYPE_CODE,FR_NO) FRR"); try ...{ // 連接數據庫進行查詢,得到信息數據表 Database db = DatabaseFactory.CreateDatabase(); DbCommand cmd = db.GetSqlStringCommand(sql.ToString()); DataTable dt = db.ExecuteDataSet(cmd).Tables[0]; // 返回信息數據表 return dt; } catch (Exception ex) ...{ throw new System.Exception(ex.Message); } } /**//// <summary> /// 名稱: GetReqBodyList /// 描述: 加載需求單身信息 /// </summary> /// <param name="ht"></param> /// <returns></returns> public DataTable GetReqBodyList(ArrayList alst) ...{ StringBuilder sql = new StringBuilder(""); //int htCount = 0; int alstCount = 0; string[] abcdefghij = ...{ "A", "B", "C", "D", "E", "F", "G", "H", "I", "J" }; sql.Append("SELECT "); sql.Append(" ROWNUM,"); sql.Append(" FRR.*"); sql.Append(" FROM "); sql.Append(" (SELECT FR.*"); sql.Append(" FROM "); sql.Append(" ("); for (int iLoop = 0; iLoop < alst.Count; iLoop++) ...{ Hashtable ht = (Hashtable)alst[iLoop]; foreach (DictionaryEntry de in ht) ...{ sql.Append(" SELECT "); sql.Append(" RS.REAMER_NO AS FR_NO,"); sql.Append(" RS.TYPE_CODE,"); sql.Append(" RWI.GRADE,"); sql.Append(" (SELECT RT.TYPE_NAME"); sql.Append(" FROM REAMER_TYPE RT"); sql.Append(" WHERE RT.TYPE_CODE = SUBSTR(RS.TYPE_CODE,1,1)) AS TOP_TYPE,"); sql.Append(" (SELECT RT.TYPE_NAME"); sql.Append(" FROM REAMER_TYPE RT"); sql.Append(" WHERE RT.TYPE_CODE = SUBSTR(RS.TYPE_CODE,1,3)) AS MIDDLE_TYPE,"); sql.Append(" R.TYPE_NAME AS TYPE_NAME,"); sql.Append(" '<span style=''color: #006600''>'||SN.A_NAME||':</span><span style=''color: #0000ff''>['||RS.A||']</span> '||"); sql.Append(" '<span style=''color: #006600''>規格:</span><span style=''color: #0000ff''>['||RSS.SPEC||']</span> '||"); for (int jLoop = 1; jLoop < 10; jLoop++) ...{ sql.Append(" CASE WHEN RS."); sql.Append(abcdefghij[jLoop]); sql.Append(" IS NOT NULL THEN '<span style=''color: #006600''>'||SN."); sql.Append(abcdefghij[jLoop]); sql.Append("_NAME||':</span><span style=''color: #0000ff''>['"); sql.Append(" ELSE '' END"); sql.Append(" || CASE WHEN RS."); sql.Append(abcdefghij[jLoop]); sql.Append(" IS NOT NULL THEN RS."); sql.Append(abcdefghij[jLoop]); sql.Append("||']</span> ' ELSE '' END ||"); } sql.Append(" '<span style=''color: #006600''>等級:</span><span style=''color: #0000ff''>['||RWI.GRADE||']</span>'"); sql.Append(" AS ATTR,"); sql.Append(" 0,"); sql.Append(" ''"); sql.Append(" FROM "); sql.Append(" REAMER_SPEC RS,"); sql.Append(" REAMER_WAREHOUSE_INVENTORY RWI,"); sql.Append(" REAMER_SPEC_SUPPLY RSS,"); sql.Append(" REAMER_TYPE R,"); sql.Append(" SPEC_NAME SN"); sql.Append(" WHERE "); sql.Append(" RS.REAMER_NO = RWI.REAMER_NO"); sql.Append(" AND RS.REAMER_NO = RSS.SPEC_NO"); sql.Append(" AND RS.TYPE_CODE = R.TYPE_CODE"); sql.Append(" AND ((RS.TYPE_CODE = SN.TYPE_CODE) OR (SUBSTR(RS.TYPE_CODE,1,3) = SN.TYPE_CODE))"); sql.Append(" AND RWI.WAREHOUSE IN ('NEW','GRADE')"); sql.Append(" AND RS.REAMER_NO = '"); sql.Append(de.Key.ToString()); sql.Append("'"); sql.Append(" AND RWI.GRADE = '"); sql.Append(de.Value.ToString()); sql.Append("'"); sql.Append(" UNION"); sql.Append(" SELECT "); sql.Append(" FS.FITTING_NO AS FR_NO,"); sql.Append(" FS.TYPE_CODE,"); sql.Append(" RWI.GRADE,"); sql.Append(" (SELECT RT.TYPE_NAME"); sql.Append(" FROM REAMER_TYPE RT"); sql.Append(" WHERE RT.TYPE_CODE = SUBSTR(FS.TYPE_CODE,1,1)) AS TOP_TYPE,"); sql.Append(" (SELECT RT.TYPE_NAME"); sql.Append(" FROM REAMER_TYPE RT"); sql.Append(" WHERE RT.TYPE_CODE = SUBSTR(FS.TYPE_CODE,1,3)) AS MIDDLE_TYPE,"); sql.Append(" R.TYPE_NAME AS TYPE_NAME,"); sql.Append(" '<span style=''color: #006600''>規格:</span><span style=''color: #0000ff''>['||RSS.SPEC||']</span> '||"); for (int jLoop = 0; jLoop < 8; jLoop++) ...{ sql.Append(" CASE WHEN FS."); sql.Append(abcdefghij[jLoop]); sql.Append(" IS NOT NULL THEN '<span style=''color: #006600''>'||SN."); sql.Append(abcdefghij[jLoop]); sql.Append("_NAME||':</span><span style=''color: #0000ff''>['"); sql.Append(" ELSE '' END"); sql.Append(" || CASE WHEN FS."); sql.Append(abcdefghij[jLoop]); sql.Append(" IS NOT NULL THEN FS."); sql.Append(abcdefghij[jLoop]); sql.Append("||']</span> ' ELSE '' END ||"); } sql.Append(" '' AS ATTR,"); sql.Append(" 0,"); sql.Append(" ''"); sql.Append(" FROM "); sql.Append(" FITTING_SPEC FS,"); sql.Append(" REAMER_WAREHOUSE_INVENTORY RWI,"); sql.Append(" REAMER_SPEC_SUPPLY RSS,"); sql.Append(" REAMER_TYPE R,"); sql.Append(" SPEC_NAME SN"); sql.Append(" WHERE "); sql.Append(" FS.FITTING_NO = RWI.REAMER_NO"); sql.Append(" AND FS.FITTING_NO = RSS.SPEC_NO"); sql.Append(" AND FS.TYPE_CODE = R.TYPE_CODE"); sql.Append(" AND ((FS.TYPE_CODE = SN.TYPE_CODE) OR (SUBSTR(FS.TYPE_CODE,1,3) = SN.TYPE_CODE))"); sql.Append(" AND RWI.WAREHOUSE IN ('NEW','GRADE')"); sql.Append(" AND FS.FITTING_NO = '"); sql.Append(de.Key.ToString()); sql.Append("'"); sql.Append(" AND RWI.GRADE = '"); sql.Append(de.Value.ToString()); sql.Append("'"); } if (alstCount < alst.Count - 1) ...{ sql.Append(" UNION "); alstCount++; } } sql.Append(" ) FR"); sql.Append(" ORDER BY FR.TYPE_CODE,FR_NO) FRR"); try ...{ // 連接數據庫進行查詢,得到信息數據表 Database db = DatabaseFactory.CreateDatabase(); DbCommand cmd = db.GetSqlStringCommand(sql.ToString()); DataTable dt = db.ExecuteDataSet(cmd).Tables[0]; // 返回信息數據表 return dt; } catch (Exception ex) ...{ throw new System.Exception(ex.Message); } } /**//// <summary> /// 名稱: GetRequirementNo /// 描述: 取得需求單號 /// </summary> public string GetRequirementNo() ...{ StringBuilder sql = new StringBuilder(""); StringBuilder sbRequirementNo = new StringBuilder(""); IDataReader idr = null; sql.Append("SELECT"); sql.Append(" BILL_NO"); sql.Append(" FROM"); sql.Append(" BILL_GENERATE"); sql.Append(" WHERE"); sql.Append(" BILL_TYPE = 'REQ'"); try ...{ Database db = DatabaseFactory.CreateDatabase(); DbCommand cmd = db.GetSqlStringCommand(sql.ToString()); idr = db.ExecuteReader(cmd); if (idr.Read()) ...{ sbRequirementNo.Append(idr["BILL_NO"].ToString()); } sbRequirementNo.Append('-'); sbRequirementNo.Append(System.DateTime.Today.Year.ToString().Substring(3, 1)); if (System.DateTime.Today.Month.ToString() == "10") ...{ sbRequirementNo.Append("A"); } else if (System.DateTime.Today.Month.ToString() == "11") ...{ sbRequirementNo.Append("B"); } else if (System.DateTime.Today.Month.ToString() == "12") ...{ sbRequirementNo.Append("C"); } else ...{ sbRequirementNo.Append(System.DateTime.Today.Month.ToString()); } sql.Remove(0, sql.Length); sql.Append("SELECT"); sql.Append(" NVL(LPAD(MAX(SUBSTR(REQ_BILL_NO,LENGTH(REQ_BILL_NO)-3,4))+1,4,'0'),'0001')"); sql.Append(" AS NO"); sql.Append(" FROM"); sql.Append(" REQ_BILL_HEAD"); cmd = db.GetSqlStringCommand(sql.ToString()); idr = db.ExecuteReader(cmd); if (idr.Read()) ...{ sbRequirementNo.Append(idr["NO"].ToString()); } return sbRequirementNo.ToString(); } catch (Exception ex) ...{ throw new System.Exception(ex.Message); } finally ...{ idr.Close(); } } /**//// <summary> /// 名稱: GetReqList /// 描述: 查詢 /// </summary> /// <returns>string,查詢條件</returns> public DataTable GetReqList(int queryType) ...{ StringBuilder sql = new StringBuilder(""); sql.Append("SELECT "); sql.Append(" ROWNUM,"); sql.Append(" REQ.*"); sql.Append(" FROM "); sql.Append(" (SELECT"); sql.Append(" RBH.REQ_BILL_NO,"); sql.Append(" RBH.MOLD_NO,"); sql.Append(" RBH.SUB_MOLD_NO,"); sql.Append(" (SELECT GD.DEFINITION_VALUE"); sql.Append(" FROM GENERAL_DEFINITION GD"); sql.Append(" WHERE GD.DEFINITION_KEY = RBH.WORKSHOP_SECTION) AS WORKSHOP_NAME,"); sql.Append(" (SELECT GD.DEFINITION_VALUE"); sql.Append(" FROM GENERAL_DEFINITION GD"); sql.Append(" WHERE GD.DEFINITION_KEY = RBH.STATION_NO) AS STATION_NAME,"); sql.Append(" RBH.TOTAL_ITEM_QTY,"); sql.Append(" DECODE(RBH.STATUS,'O','暫存','F','確認','A','審核','C','結案','X','取消') AS STATUS_D,"); sql.Append(" REQ_MAN_NAME,"); sql.Append(" TO_CHAR(REQUEST_TIME,'MM/dd') AS REQUEST_TIME,"); sql.Append(" RBH.APPROVER,"); sql.Append(" TO_CHAR(RBH.APPROVE_TIME,'MM/dd') AS APPROVE_TIME,"); sql.Append(" RBH.TAKER,"); sql.Append(" TO_CHAR(RBH.TAKE_TIME,'MM/dd') AS TAKE_TIME,"); sql.Append(" RBH.STATUS,"); sql.Append(" RBH.WORKSHOP_SECTION,"); sql.Append(" RBH.STATION_NO,"); sql.Append(" RBH.REMARK1"); sql.Append(" FROM "); sql.Append(" REQ_BILL_HEAD RBH"); sql.Append(" WHERE "); sql.Append(" RBH.ISLEAGL = 'Y'"); // 設定查詢條件 sql.Append(GetCondition()); sql.Append(" ORDER BY RBH.MODIFYDATE DESC) REQ"); try ...{ // 連接數據庫進行查詢,得到信息數據表 Database db = DatabaseFactory.CreateDatabase(); DbCommand cmd = db.GetSqlStringCommand(sql.ToString()); DataTable dt = db.ExecuteDataSet(cmd).Tables[0]; // 返回信息數據表 return dt; } catch (Exception ex) ...{ throw new System.Exception(ex.Message); } } /**//// <summary> /// 名稱: GetCondition /// 描述: 根據輸入值組合得到查詢條件 /// </summary> /// <returns>string,查詢條件</returns> private string GetCondition() ...{ StringBuilder sqlCondition = new StringBuilder(""); if (reqBillNo.Length > 0) ...{ sqlCondition.Append(" AND RBH.REQ_BILL_NO LIKE '%"); sqlCondition.Append(reqBillNo.Replace("'", "''")); sqlCondition.Append("%'"); } if (workshopSection.Length > 0) ...{ sqlCondition.Append(" AND RBH.WORKSHOP_SECTION LIKE '"); sqlCondition.Append(workshopSection); sqlCondition.Append("%'"); } if (stationNo.Length > 0) ...{ sqlCondition.Append(" AND RBH.STATION_NO LIKE '"); sqlCondition.Append(stationNo); sqlCondition.Append("%'"); } if (moldNo.Length > 0) ...{ sqlCondition.Append(" AND RBH.MOLD_NO LIKE '%"); sqlCondition.Append(moldNo.Replace("'", "''")); sqlCondition.Append("'"); } if (subMoldNo.Length > 0) ...{ sqlCondition.Append(" AND RBH.SUB_MOLD_NO LIKE '%"); sqlCondition.Append(subMoldNo.Replace("'", "''")); sqlCondition.Append("%'"); } if (status != "ALL") ...{ sqlCondition.Append(" AND RBH.STATUS = '"); sqlCondition.Append(status.Replace("'", "''")); sqlCondition.Append("'"); } // 返回查詢條件 return sqlCondition.ToString(); } public static string GetUserName(string userId) ...{ IDataReader dr = null; string strUserName = string.Empty; StringBuilder sql = new StringBuilder(""); sql.Append("SELECT "); sql.Append(" USERNAME"); sql.Append(" FROM "); sql.Append(" USERINFORMATION"); sql.Append(" WHERE "); sql.Append(" USERID = '"); sql.Append(userId); sql.Append("'"); try ...{ Database db = DatabaseFactory.CreateDatabase(); DbCommand cmd = db.GetSqlStringCommand(sql.ToString()); dr = db.ExecuteReader(cmd); // 將讀取出的數據存入類成員變量 if (dr.Read()) ...{ strUserName = dr["USERNAME"].ToString(); } return strUserName; } catch (Exception ex) ...{ throw new System.Exception(ex.Message); } finally ...{ if (dr != null) ...{ dr.Close(); } } } /**//// <summary> /// 名稱: GetColumnValue /// 描述: 得到表中某列的非空不重複列值 /// </summary> /// <returns>DataTable,列值</returns> public static IDataReader GetGenDefinition(string strDefType) ...{ StringBuilder sql = new StringBuilder(""); IDataReader idr = null; sql.Append("SELECT "); sql.Append(" DISTINCT "); sql.Append(" DEFINITION_KEY,"); sql.Append(" DEFINITION_VALUE"); sql.Append(" FROM "); sql.Append(" GENERAL_DEFINITION"); sql.Append(" WHERE"); sql.Append(" DEFINITION_TYPE_NAME = '"); sql.Append(strDefType); sql.Append("'"); try ...{ // 連接數據庫進行查詢,得到信息數據表 Database db = DatabaseFactory.CreateDatabase(); DbCommand cmd = db.GetSqlStringCommand(sql.ToString()); idr = db.ExecuteReader(cmd); // 返回信息數據表 return idr; } catch (Exception ex) ...{ throw new System.Exception(ex.Message); } } /**//// <summary> /// 名稱: GetBorrowList /// 描述: 加載需求單身信息 /// </summary> /// <param name="queryType"></param> /// <returns></returns> public DataTable GetBorrowList(int queryType) ...{ StringBuilder sql = new StringBuilder(""); string[] abcdefghij = ...{ "A", "B", "C", "D", "E", "F", "G", "H", "I", "J" }; sql.Append("SELECT "); sql.Append(" ROWNUM,"); sql.Append(" FRR.*"); sql.Append(" FROM "); sql.Append(" (SELECT FR.*"); sql.Append(" FROM "); sql.Append(" (SELECT "); sql.Append(" RS.REAMER_NO AS FR_NO,"); sql.Append(" RS.TYPE_CODE,"); sql.Append(" RBB.GRADE,"); sql.Append(" RBB.SEQ_NO,A,B,C,D,E,F,G,H,ACTUAL_PART_NO,ACTUAL_GRADE,ACTUAL_TAKE_TYPE,"); sql.Append(" '<span style=''color: #006600''>'||R.TYPE_NAME||'</span>' AS TYPE_NAME,"); sql.Append(" '<span style=''color: #006600''>'||SN.A_NAME||':</span><span style=''color: #0000ff''>['||RS.A||']</span> '||"); sql.Append(" '<span style=''color: #006600''>規格:</span><span style=''color: #0000ff''>['||RSS.SPEC||']</span> '||"); for (int iLoop = 1; iLoop < 10; iLoop++) ...{ sql.Append(" CASE WHEN RS."); sql.Append(abcdefghij[iLoop]); sql.Append(" IS NOT NULL THEN '<span style=''color: #006600''>'||SN."); sql.Append(abcdefghij[iLoop]); sql.Append("_NAME||':</span><span style=''color: #0000ff''>['"); sql.Append(" ELSE '' END"); sql.Append(" || CASE WHEN RS."); sql.Append(abcdefghij[iLoop]); sql.Append(" IS NOT NULL THEN RS."); sql.Append(abcdefghij[iLoop]); sql.Append("||']</span> ' ELSE '' END ||"); } sql.Append(" '<span style=''color: #006600''>等級:</span><span style=''color: #0000ff''>['||RBB.GRADE||']</span>'"); sql.Append(" AS ATTR,"); sql.Append(" CASE WHEN RBB.GRADE <> '/' THEN '<span style=''color: #ff0000''>等級:</span><span style=''color: #cc0000''>['||RBB.GRADE||']</span> <span style=''color: #ff0000''>首下長:</span><span style=''color: #cc0000''>['||F||']</span>' ELSE '' END AS FACT_ATTR,"); sql.Append(" RBB.QTY,"); sql.Append(" NVL(RBB.TAKE_QTY,0),"); sql.Append(" RWI.STOCK_QTY,"); sql.Append(" CASE WHEN NVL(RWI.STOCK_QTY,0)-NVL(RBB.QTY,0)-NVL(RBB.TAKE_QTY,0) > 0 THEN NVL(RBB.QTY,0)-NVL(RBB.TAKE_QTY,0) ELSE NVL(RWI.STOCK_QTY,0) END AS LAST_TAKE_QTY,"); sql.Append(" RBB.REQ_APPROVER,"); sql.Append(" RBB.REMARK1"); sql.Append(" FROM "); sql.Append(" REAMER_SPEC RS,"); sql.Append(" REQ_BILL_BODY RBB,"); sql.Append(" REAMER_SPEC_SUPPLY RSS,"); sql.Append(" REAMER_TYPE R,"); sql.Append(" REAMER_WAREHOUSE_INVENTORY RWI,"); sql.Append(" SPEC_NAME SN"); sql.Append(" WHERE "); sql.Append(" RS.REAMER_NO = RBB.PART_CODE"); sql.Append(" AND RS.REAMER_NO = RSS.SPEC_NO"); sql.Append(" AND RS.TYPE_CODE = R.TYPE_CODE"); sql.Append(" AND RS.REAMER_NO = RWI.REAMER_NO"); sql.Append(" AND RBB.GRADE = RWI.GRADE"); sql.Append(" AND RWI.WAREHOUSE IN ('NEW','GRADE')"); sql.Append(" AND ((RS.TYPE_CODE = SN.TYPE_CODE) OR (SUBSTR(RS.TYPE_CODE,1,3) = SN.TYPE_CODE))"); sql.Append(" AND RS.TYPE_CODE LIKE 'A01%'"); sql.Append(" AND RBB.REQ_BILL_NO = '"); sql.Append(reqBillNo); sql.Append("'"); sql.Append(" UNION"); sql.Append(" SELECT "); sql.Append(" RS.REAMER_NO AS FR_NO,"); sql.Append(" RS.TYPE_CODE,"); sql.Append(" RBB.GRADE,"); sql.Append(" RBB.SEQ_NO,A,B,C,D,E,F,G,H,'','','',"); sql.Append(" '<span style=''color: #006600''>'||R.TYPE_NAME||'</span>' AS TYPE_NAME,"); sql.Append(" '<span style=''color: #006600''>'||SN.A_NAME||':</span><span style=''color: #0000ff''>['||RS.A||']</span> '||"); sql.Append(" '<span style=''color: #006600''>規格:</span><span style=''color: #0000ff''>['||RSS.SPEC||']</span> '||"); for (int iLoop = 1; iLoop < 10; iLoop++) ...{ sql.Append(" CASE WHEN RS."); sql.Append(abcdefghij[iLoop]); sql.Append(" IS NOT NULL THEN '<span style=''color: #006600''>'||SN."); sql.Append(abcdefghij[iLoop]); sql.Append("_NAME||':</span><span style=''color: #0000ff''>['"); sql.Append(" ELSE '' END"); sql.Append(" || CASE WHEN RS."); sql.Append(abcdefghij[iLoop]); sql.Append(" IS NOT NULL THEN RS."); sql.Append(abcdefghij[iLoop]); sql.Append("||']</span> ' ELSE '' END ||"); } sql.Append(" '' AS ATTR,"); sql.Append(" '' AS FACT_ATTR,"); sql.Append(" RBB.QTY,"); sql.Append(" NVL(RBB.TAKE_QTY,0),"); sql.Append(" RWI.STOCK_QTY,"); sql.Append(" CASE WHEN NVL(RWI.STOCK_QTY,0)-NVL(RBB.QTY,0)-NVL(RBB.TAKE_QTY,0) > 0 THEN NVL(RBB.QTY,0)-NVL(RBB.TAKE_QTY,0) ELSE NVL(RWI.STOCK_QTY,0) END AS LAST_TAKE_QTY,"); sql.Append(" '',"); sql.Append(" RBB.REMARK1"); sql.Append(" FROM "); sql.Append(" REAMER_SPEC RS,"); sql.Append(" REQ_BILL_BODY RBB,"); sql.Append(" REAMER_SPEC_SUPPLY RSS,"); sql.Append(" REAMER_TYPE R,"); sql.Append(" REAMER_WAREHOUSE_INVENTORY RWI,"); sql.Append(" SPEC_NAME SN"); sql.Append(" WHERE "); sql.Append(" RS.REAMER_NO = RBB.PART_CODE"); sql.Append(" AND RS.REAMER_NO = RSS.SPEC_NO"); sql.Append(" AND RS.TYPE_CODE = R.TYPE_CODE"); sql.Append(" AND RS.REAMER_NO = RWI.REAMER_NO"); sql.Append(" AND RBB.GRADE = RWI.GRADE"); sql.Append(" AND RWI.WAREHOUSE IN ('NEW','GRADE')"); sql.Append(" AND ((RS.TYPE_CODE = SN.TYPE_CODE) OR (SUBSTR(RS.TYPE_CODE,1,3) = SN.TYPE_CODE))"); sql.Append(" AND RS.TYPE_CODE NOT LIKE 'A01%'"); sql.Append(" AND RBB.REQ_BILL_NO = '"); sql.Append(reqBillNo); sql.Append("'"); sql.Append(" UNION"); sql.Append(" SELECT "); sql.Append(" FS.FITTING_NO AS FR_NO,"); sql.Append(" FS.TYPE_CODE,"); sql.Append(" RBB.GRADE,"); sql.Append(" RBB.SEQ_NO,'','','','','','','','','','','',"); sql.Append(" '<span style=''color: #0000ff''>'||R.TYPE_NAME||'</span>' AS TYPE_NAME,"); sql.Append(" '<span style=''color: #006600''>規格:</span><span style=''color: #0000ff''>['||RSS.SPEC||']</span> '||"); for (int iLoop = 0; iLoop < 8; iLoop++) ...{ sql.Append(" CASE WHEN FS."); sql.Append(abcdefghij[iLoop]); sql.Append(" IS NOT NULL THEN '<span style=''color: #006600''>'||SN."); sql.Append(abcdefghij[iLoop]); sql.Append("_NAME||':</span><span style=''color: #0000ff''>['"); sql.Append(" ELSE '' END"); sql.Append(" || CASE WHEN FS."); sql.Append(abcdefghij[iLoop]); sql.Append(" IS NOT NULL THEN FS."); sql.Append(abcdefghij[iLoop]); sql.Append("||']</span> ' ELSE '' END ||"); } sql.Append(" '' AS ATTR,"); sql.Append(" '' AS FACT_ATTR,"); sql.Append(" RBB.QTY,"); sql.Append(" NVL(RBB.TAKE_QTY,0),"); sql.Append(" RWI.STOCK_QTY,"); sql.Append(" CASE WHEN NVL(RWI.STOCK_QTY,0)-NVL(RBB.QTY,0)-NVL(RBB.TAKE_QTY,0) > 0 THEN NVL(RBB.QTY,0)-NVL(RBB.TAKE_QTY,0) ELSE NVL(RWI.STOCK_QTY,0) END AS LAST_TAKE_QTY,"); sql.Append(" '',"); sql.Append(" RBB.REMARK1"); sql.Append(" FROM "); sql.Append(" FITTING_SPEC FS,"); sql.Append(" REQ_BILL_BODY RBB,"); sql.Append(" REAMER_SPEC_SUPPLY RSS,"); sql.Append(" REAMER_TYPE R,"); sql.Append(" REAMER_WAREHOUSE_INVENTORY RWI,"); sql.Append(" SPEC_NAME SN"); sql.Append(" WHERE "); sql.Append(" FS.FITTING_NO = RBB.PART_CODE"); sql.Append(" AND FS.FITTING_NO = RSS.SPEC_NO"); sql.Append(" AND FS.TYPE_CODE = R.TYPE_CODE"); sql.Append(" AND FS.FITTING_NO = RWI.REAMER_NO"); sql.Append(" AND RBB.GRADE = RWI.GRADE"); sql.Append(" AND RWI.WAREHOUSE IN ('NEW','GRADE')"); sql.Append(" AND ((FS.TYPE_CODE = SN.TYPE_CODE) OR (SUBSTR(FS.TYPE_CODE,1,3) = SN.TYPE_CODE))"); sql.Append(" AND RBB.REQ_BILL_NO = '"); sql.Append(reqBillNo); sql.Append("'"); sql.Append(" ) FR"); sql.Append(" ORDER BY FR.TYPE_CODE,FR.FR_NO) FRR"); try ...{ // 連接數據庫進行查詢,得到信息數據表 Database db = DatabaseFactory.CreateDatabase(); DbCommand cmd = db.GetSqlStringCommand(sql.ToString()); DataTable dt = db.ExecuteDataSet(cmd).Tables[0]; // 返回信息數據表 return dt; } catch (Exception ex) ...{ throw new System.Exception(ex.Message); } } /**//// <summary> /// 名稱: Borrow /// 描述: 領刀 /// </summary> /// <param name="intScore">問題點原類別對應分數</param> public void Borrow(DataTable dtReqBody) ...{ // 事務是否成功完成標誌,Y:提交,N:回滾 string isCommit = "Y"; StringBuilder sql = new StringBuilder(""); StringBuilder sqlReqHead = new StringBuilder(""); StringBuilder sqlReqBody = new StringBuilder(""); StringBuilder sqlInventory = new StringBuilder(""); StringBuilder sqlTransaction = new StringBuilder(""); IDataReader idr = null; int seqNo = 0; Database tdb = DatabaseFactory.CreateDatabase(); DbConnection tconn = tdb.CreateConnection(); tconn.Open(); DbTransaction ttran = tconn.BeginTransaction(); DbCommand tcmd = null; try ...{ sqlReqHead.Append("UPDATE "); sqlReqHead.Append(" REQ_BILL_HEAD"); sqlReqHead.Append(" SET TAKER = '"); sqlReqHead.Append(taker); sqlReqHead.Append("',"); sqlReqHead.Append(" TAKE_TIME = SYSDATE,"); sqlReqHead.Append(" REMARK2 = '"); sqlReqHead.Append(remark2); sqlReqHead.Append("',"); sqlReqHead.Append(" MODIFYSTAFF = '"); sqlReqHead.Append(modifyStaff); sqlReqHead.Append("',"); sqlReqHead.Append(" MODIFYDATE = SYSDATE"); sqlReqHead.Append(" WHERE"); sqlReqHead.Append(" REQ_BILL_NO = '"); sqlReqHead.Append(reqBillNo); sqlReqHead.Append("'"); tcmd = tdb.GetSqlStringCommand(sqlReqHead.ToString()); tdb.ExecuteNonQuery(tcmd, ttran); // 修改需求單身 if (dtReqBody.Rows.Count > 0) ...{ // 邊歷哈希表 foreach (DataRow dr in dtReqBody.Rows) ...{ seqNo++; // 新增需求單身 sqlReqBody.Remove(0, sqlReqBody.Length); sqlReqBody.Append("UPDATE"); sqlReqBody.Append(" REQ_BILL_BODY"); sqlReqBody.Append(" SET REQ_BILL_NO,"); sqlReqBody.Append(" PART_CODE,"); sqlReqBody.Append(" GRADE,"); sqlReqBody.Append(" SEQ_NO,"); sqlReqBody.Append(" QTY,"); sqlReqBody.Append(" PICKER,"); sqlReqBody.Append(" PICK_TIME,"); sqlReqBody.Append(" REMARK1,"); sqlReqBody.Append(" MODIFYSTAFF,"); sqlReqBody.Append(" MODIFYDATE)"); sqlReqBody.Append(" VALUES ( "); sqlReqBody.Append("'"); sqlReqBody.Append(reqBillNo); sqlReqBody.Append("',"); sqlReqBody.Append("'"); sqlReqBody.Append(dr[1].ToString()); sqlReqBody.Append("',"); sqlReqBody.Append("'"); sqlReqBody.Append(dr[3].ToString()); sqlReqBody.Append("',"); sqlReqBody.Append("'"); sqlReqBody.Append(seqNo); sqlReqBody.Append("',"); sqlReqBody.Append(dr[8].ToString()); sqlReqBody.Append(","); sqlReqBody.Append("'"); sqlReqBody.Append(modifyStaff); sqlReqBody.Append("',"); sqlReqBody.Append("SYSDATE,"); sqlReqBody.Append("'"); sqlReqBody.Append(dr[9].ToString()); sqlReqBody.Append("',"); sqlReqBody.Append("'"); sqlReqBody.Append(modifyStaff); sqlReqBody.Append("',"); sqlReqBody.Append("SYSDATE)"); tcmd = tdb.GetSqlStringCommand(sqlReqBody.ToString()); tdb.ExecuteNonQuery(tcmd, ttran); if (status == "F") ...{ idr = null; sqlInventory.Remove(0, sqlInventory.Length); sqlInventory.Append("SELECT "); sqlInventory.Append(" REAMER_NO "); sqlInventory.Append(" FROM "); sqlInventory.Append(" REAMER_INVENTORY"); sqlInventory.Append(" WHERE"); sqlInventory.Append(" REAMER_NO = '"); sqlInventory.Append(dr[1].ToString()); sqlInventory.Append("'"); tcmd = tdb.GetSqlStringCommand(sqlInventory.ToString()); idr = tdb.ExecuteReader(tcmd, ttran); sqlInventory.Remove(0, sqlInventory.Length); if (idr.Read()) ...{ sqlInventory.Append("UPDATE "); sqlInventory.Append(" REAMER_INVENTORY"); sqlInventory.Append(" SET REAMER_NO = '"); sqlInventory.Append(dr[1].ToString()); sqlInventory.Append("',"); sqlInventory.Append(" REQUIRE_QTY = NVL(REQUIRE_QTY,0) +"); sqlInventory.Append(dr[8].ToString()); sqlInventory.Append(","); sqlInventory.Append(" MODIFYSTAFF = '"); sqlInventory.Append(modifyStaff); sqlInventory.Append("',"); sqlInventory.Append(" MODIFYDATE = SYSDATE"); sqlInventory.Append(" WHERE"); sqlInventory.Append(" REAMER_NO = '"); sqlInventory.Append(dr[1].ToString()); sqlInventory.Append("'"); } else ...{ sqlInventory.Append("INSERT"); sqlInventory.Append(" INTO REAMER_INVENTORY ("); sqlInventory.Append(" REAMER_NO,"); sqlInventory.Append(" REQUIRE_QTY,"); sqlInventory.Append(" MODIFYSTAFF,"); sqlInventory.Append(" MODIFYDATE)"); sqlInventory.Append(" VALUES("); sqlInventory.Append("'"); sqlInventory.Append(dr[1].ToString()); sqlInventory.Append("',"); sqlInventory.Append(dr[8].ToString()); sqlInventory.Append(",'"); sqlInventory.Append(modifyStaff); sqlInventory.Append("',"); sqlInventory.Append("SYSDATE)"); } tcmd = tdb.GetSqlStringCommand(sqlInventory.ToString()); tdb.ExecuteNonQuery(tcmd, ttran); sqlTransaction.Remove(0, sqlTransaction.Length); sqlTransaction.Append("INSERT "); sqlTransaction.Append(" INTO INV_TRANSACTION ("); sqlTransaction.Append(" TRA_SEQ_NO,"); sqlTransaction.Append(" PART_NO,"); sqlTransaction.Append(" GRADE,"); sqlTransaction.Append(" WAREHOUSE,"); sqlTransaction.Append(" TRANS_TYPE,"); sqlTransaction.Append(" BILL_NO,"); sqlTransaction.Append(" SEQ_NO,"); sqlTransaction.Append(" TRANS_TIME,"); sqlTransaction.Append(" QTY,"); sqlTransaction.Append(" STATE,"); sqlTransaction.Append(" WORK_NO,"); sqlTransaction.Append(" WORK_NAME,"); sqlTransaction.Append(" CHANGE_TYPE,"); sqlTransaction.Append(" REMARK1,"); sqlTransaction.Append(" MODIFYSTAFF,"); sqlTransaction.Append(" MODIFYDATE)"); sqlTransaction.Append(" VALUES ( "); sqlTransaction.Append("(SELECT BILL_NO"); sqlTransaction.Append(" FROM BILL_GENERATE"); sqlTransaction.Append(" WHERE BILL_TYPE = 'TRA')"); sqlTransaction.Append("||'-'||'"); sqlTransaction.Append(System.DateTime.Today.Year.ToString().Substring(3, 1)); sqlTransaction.Append("'||'"); if (System.DateTime.Today.Month.ToString() == "10") ...{ sqlTransaction.Append("A"); } else if (System.DateTime.Today.Month.ToString() == "11") ...{ sqlTransaction.Append("B"); } else if (System.DateTime.Today.Month.ToString() == "12") ...{ sqlTransaction.Append("C"); } else ...{ sqlTransaction.Append(System.DateTime.Today.Month.ToString()); } sqlTransaction.Append("'||"); sqlTransaction.Append("(SELECT"); sqlTransaction.Append(" NVL(LPAD(MAX(SUBSTR(TRA_SEQ_NO,LENGTH(TRA_SEQ_NO)-4,5))+1,5,'0'),'00001')"); sqlTransaction.Append(" FROM"); sqlTransaction.Append(" INV_TRANSACTION),"); sqlTransaction.Append("'"); sqlTransaction.Append(dr[1].ToString()); sqlTransaction.Append("',"); sqlTransaction.Append("'"); sqlTransaction.Append(dr[3].ToString()); sqlTransaction.Append("',"); sqlTransaction.Append("'V','V','"); sqlTransaction.Append(reqBillNo); sqlTransaction.Append("',"); sqlTransaction.Append("'"); sqlTransaction.Append(seqNo); sqlTransaction.Append("',"); sqlTransaction.Append("SYSDATE,"); sqlTransaction.Append(dr[8].ToString()); sqlTransaction.Append(","); sqlTransaction.Append("'-',"); sqlTransaction.Append("'"); sqlTransaction.Append(modifyStaff); sqlTransaction.Append("',"); sqlTransaction.Append("'"); sqlTransaction.Append(modifyStaff); sqlTransaction.Append("',"); //sqlTransaction.Append("(SELECT USERNAME"); //sqlTransaction.Append(" FROM USERINFORMATION"); //sqlTransaction.Append(" WHERE USERID = '"); //sqlTransaction.Append(modifyStaff); //sqlTransaction.Append("'),"); sqlTransaction.Append("'B',"); sqlTransaction.Append("'"); sqlTransaction.Append(dr[9].ToString()); sqlTransaction.Append("',"); sqlTransaction.Append("'"); sqlTransaction.Append(modifyStaff); sqlTransaction.Append("',"); sqlTransaction.Append("SYSDATE)"); tcmd = tdb.GetSqlStringCommand(sqlTransaction.ToString()); tdb.ExecuteNonQuery(tcmd, ttran); } } } } catch (Exception ex) ...{ isCommit = "N"; } // 如所有操作未能全部成功完成,則回滾,否則提交 if (isCommit == "N") ...{ ttran.Rollback(); throw new Exception("操作失敗!"); } else ...{ ttran.Commit(); } tconn.Close(); }}