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);
}
internal InspectListReportRow(global::System.Data.DataRowBuilder rb) :
base(rb) {
this.tableInspectListReport = ((InspectListReportDataTable)(this.Table));
}
private void InitClass() {
this.DataSetName = "ReportDataSet";
this.Prefix = "";
this.Namespace = "http://tempuri.org/ReportDataSet.xsd";
this.EnforceConstraints = true;
this.SchemaSerializationMode = global::System.Data.SchemaSerializationMode.IncludeSchema;
this.tableFinishedWorksheetReportSource = new FinishedWorksheetReportSourceDataTable(false);
base.Tables.Add(this.tableFinishedWorksheetReportSource);
this.tableUnfinishedWorksheetReportSource = new UnfinishedWorksheetReportSourceDataTable(false);
base.Tables.Add(this.tableUnfinishedWorksheetReportSource);
this.tableUnitPriceReport = new UnitPriceReportDataTable(false);
base.Tables.Add(this.tableUnitPriceReport);
this.tableLineLaborHourReportSource = new LineLaborHourReportSourceDataTable();
base.Tables.Add(this.tableLineLaborHourReportSource);
this.tableInspectListReport = new InspectListReportDataTable();
base.Tables.Add(this.tableInspectListReport);
}
internal void InitVars(bool initTable) {
this.tableFinishedWorksheetReportSource = ((FinishedWorksheetReportSourceDataTable)(base.Tables["FinishedWorksheetReportSource"]));
if ((initTable == true)) {
if ((this.tableFinishedWorksheetReportSource != null)) {
this.tableFinishedWorksheetReportSource.InitVars();
}
}
this.tableUnfinishedWorksheetReportSource = ((UnfinishedWorksheetReportSourceDataTable)(base.Tables["UnfinishedWorksheetReportSource"]));
if ((initTable == true)) {
if ((this.tableUnfinishedWorksheetReportSource != null)) {
this.tableUnfinishedWorksheetReportSource.InitVars();
}
}
this.tableUnitPriceReport = ((UnitPriceReportDataTable)(base.Tables["UnitPriceReport"]));
if ((initTable == true)) {
if ((this.tableUnitPriceReport != null)) {
this.tableUnitPriceReport.InitVars();
}
}
this.tableLineLaborHourReportSource = ((LineLaborHourReportSourceDataTable)(base.Tables["LineLaborHourReportSource"]));
if ((initTable == true)) {
if ((this.tableLineLaborHourReportSource != null)) {
this.tableLineLaborHourReportSource.InitVars();
}
}
this.tableInspectListReport = ((InspectListReportDataTable)(base.Tables["InspectListReport"]));
if ((initTable == true)) {
if ((this.tableInspectListReport != null)) {
this.tableInspectListReport.InitVars();
}
}
}
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);
}
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;
}
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;
}
