public static InspectListReportDataTable GetHistoryData(string lastID) { OleDbConnection conn = new OleDbConnection(Properties.Settings.Default.dbConnectionString); OleDbCommand cmd = new OleDbCommand(); cmd.Connection = conn; string cmdText = "SELECT Q.日期 as 檢驗日期, 產線, P.品號, QCN, 待驗數量 as 檢驗數量, W.單號 as 工作單號,送檢次數, IIF(檢驗結果,'OK',IIF(特許,'Concession','NG')) as 檢驗狀態, 客戶 as 客戶名稱, 工時資料編號" + " FROM ((((工時 as H INNER JOIN 產品檢驗 as Q on H.編號 = Q.工時資料編號)" + " INNER JOIN 工作單 as W ON H.工作單號 = W.單號)" + " INNER JOIN 工作單品號 as WP ON H.工作單號 = WP.單號 AND H.工品編號 = WP.編號)" + " INNER JOIN 產品品號 as P ON WP.品號 = P.品號)" + " WHERE 重驗=True AND 最後送檢編號=?" + " ORDER BY 單據日期, W.單號, P.品號, WP.編號, 送檢次數"; cmd.CommandText = cmdText; cmd.Parameters.Add(new OleDbParameter("最後送檢編號", lastID)); OleDbDataAdapter adapter = new OleDbDataAdapter(cmd); InspectListReportDataTable table = new InspectListReportDataTable(); adapter.Fill(table); //取得品質履歷 foreach (DataRow row in table) { //取得舊NG原因 string id = row["工時資料編號"].ToString(); string[] ngReasons = DatabaseSet.GetNGReason(id); row["品質履歷"] = string.Join("\n", ngReasons); } return(table); }
public static InspectListReportDataTable GetData(DateTime from, DateTime to, string partNumber, string qcn, string worksheetFrom, string worksheetTo, bool group, string line, bool onlyNg) { OleDbConnection conn = new OleDbConnection(Properties.Settings.Default.dbConnectionString); OleDbCommand cmd = new OleDbCommand(); cmd.Connection = conn; System.Collections.Generic.List <string> whereList = new System.Collections.Generic.List <string>(); whereList.Add("檢驗 = True"); //處理條件 if (!string.IsNullOrEmpty(qcn)) { cmd.Parameters.Add(new OleDbParameter("QCN", qcn + "%")); whereList.Add("QCN LIKE ?"); } if (!string.IsNullOrEmpty(partNumber)) { cmd.Parameters.Add(new OleDbParameter("品號", partNumber + "%")); whereList.Add("P.品號 LIKE ?"); } if (!string.IsNullOrEmpty(worksheetFrom)) { cmd.Parameters.Add(new OleDbParameter("工作單號1", worksheetFrom)); whereList.Add("W.單號 >= ?"); } if (!string.IsNullOrEmpty(worksheetTo)) { cmd.Parameters.Add(new OleDbParameter("工作單號2", worksheetTo)); whereList.Add("W.單號 <= ?"); } if (from != DateTime.MinValue) { OleDbParameter param = new OleDbParameter("日期1", OleDbType.Date); param.Value = from; cmd.Parameters.Add(param); whereList.Add("Q.日期 >= ?"); } if (to != DateTime.MaxValue) { OleDbParameter param = new OleDbParameter("日期2", OleDbType.Date); param.Value = to; cmd.Parameters.Add(param); whereList.Add("Q.日期 <= ?"); } if (!string.IsNullOrEmpty(line)) { cmd.Parameters.Add(new OleDbParameter("產線", line)); whereList.Add("產線 = ?"); } if (group) { whereList.Add("最後檢驗紀錄 = True"); } if (onlyNg) { whereList.Add("檢驗結果 = False"); } string cmdText = "SELECT Q.日期 as 檢驗日期, 產線, W.單號 as 工作單號, P.品號, 客戶 as 客戶名稱, WP.數量 as 總數量, 待驗數量 as 檢驗數量, 送檢次數, QCN, IIF(檢驗結果,'OK',IIF(特許,'Concession','NG')) as 檢驗狀態, 工時資料編號, 最後送檢編號, 最後檢驗紀錄, WP.編號 as 工品編號" + " FROM ((((工時 as H INNER JOIN 產品檢驗 as Q on H.編號 = Q.工時資料編號)" + " INNER JOIN 工作單 as W ON H.工作單號 = W.單號)" + " INNER JOIN 工作單品號 as WP ON H.工作單號 = WP.單號 AND H.工品編號 = WP.編號)" + " INNER JOIN 產品品號 as P ON WP.品號 = P.品號)"; if (whereList.Count > 0) { cmdText += " WHERE " + string.Join(" AND ", whereList.ToArray()); } cmdText += " ORDER BY 單據日期, W.單號, P.品號, WP.編號, 最後送檢編號, 送檢次數"; cmd.CommandText = cmdText; OleDbDataAdapter adapter = new OleDbDataAdapter(cmd); InspectListReportDataTable table = new InspectListReportDataTable(); adapter.Fill(table); //取得品質履歷 foreach (DataRow row in table) { //取得舊NG原因 string id = row["工時資料編號"].ToString(); string[] ngReasons = DatabaseSet.GetNGReason(id); row["品質履歷"] = string.Join("\n", ngReasons); } return(table); }