public static int UpdateInspectList(待驗清單DataTable table, out int ok)
{
OleDbConnection conn = new OleDbConnection(Properties.Settings.Default.dbConnectionString);
//string cmdText = "UPDATE 工時 SET 數量 = 數量 + ? WHERE 編號 = ?";
string cmdText = "UPDATE 工時 SET 數量 = ? WHERE 編號 = ?";
OleDbCommand cmd = new OleDbCommand(cmdText, conn);
cmd.Parameters.Add(new OleDbParameter("數量", OleDbType.Integer, -1, "待驗數量"));
cmd.Parameters.Add(new OleDbParameter("編號", OleDbType.VarWChar, 255, "工時資料編號"));
OleDbDataAdapter adapter = new OleDbDataAdapter();
adapter.AcceptChangesDuringUpdate = false;
adapter.UpdateCommand = cmd;
//更新每一筆檢驗通過的工作單完成狀態
DataRow[] rows = table.Select("(檢驗 = True AND (檢驗結果 = True OR 特許 = True)) ");
ok = adapter.Update(rows);
foreach (DataRow row in rows)
{
string worksheet = (string)row["工作單號"];
int wpid = (int)row["工品編號"];
//CheckFinish(worksheet, wpid, (DateTime)row["日期"]);
DateTime finishDate;
if (CheckFinish(worksheet, wpid, out finishDate))
SetFinishDate(worksheet, wpid, DateTime.Today);
//檢查並更新工作單完成狀態
UpdateWorksheetFinishDate(worksheet);
}
cmdText = "UPDATE 產品檢驗 SET 最後檢驗紀錄=False WHERE 最後送檢編號=?";
cmd = new OleDbCommand(cmdText, conn);
//cmd.Parameters.Add(new OleDbParameter("最後送檢編號", OleDbType.VarWChar, 255, ParameterDirection.Input, false, 0, 0, "工時資料編號", DataRowVersion.Original, null));
cmd.Parameters.Add(new OleDbParameter("最後送檢編號", OleDbType.VarWChar, 255, "工時資料編號"));
adapter.UpdateCommand = cmd;
rows = table.Select("檢驗 = True");
//cmd.UpdatedRowSource = UpdateRowSource.None;
adapter.Update(rows);
//更新已檢驗的資料
cmdText = "UPDATE 產品檢驗 SET 檢驗=?, 檢驗結果=?, 日期=?, 特許=?, 最後檢驗紀錄=True WHERE 工時資料編號=?";
cmd = new OleDbCommand(cmdText, conn);
cmd.Parameters.Add(new OleDbParameter("檢驗", OleDbType.Boolean, -1, "檢驗"));
cmd.Parameters.Add(new OleDbParameter("檢驗結果", OleDbType.Boolean, -1, "檢驗結果"));
//cmd.Parameters.Add(new OleDbParameter("品質原因", OleDbType.Integer, -1, "品質原因"));
//cmd.Parameters.Add(new OleDbParameter("日期", DateTime.Now.ToString("s")));
OleDbParameter paramDate = new OleDbParameter();
paramDate.OleDbType = OleDbType.DBTimeStamp;
paramDate.Value = DateTime.Now.ToString("s");
cmd.Parameters.Add(paramDate);
cmd.Parameters.Add(new OleDbParameter("特許", OleDbType.Boolean,-1, "特許"));
cmd.Parameters.Add(new OleDbParameter("工時資料編號", OleDbType.VarWChar, 255, "工時資料編號"));
adapter.UpdateCommand = cmd;
adapter.AcceptChangesDuringUpdate = true;
DataRow[] inspectedRows = table.Select("檢驗 = True");
int update = adapter.Update(inspectedRows);
//更新NG原因
cmdText = "INSERT INTO NG原因 (工時資料編號, 原因, 來源編號) VALUES (?,?,?)";
cmd = new OleDbCommand(cmdText, conn);
cmd.Parameters.Add(new OleDbParameter("工時資料編號", OleDbType.VarWChar, 255, "工時資料編號"));
cmd.Parameters.Add(new OleDbParameter("原因", OleDbType.LongVarWChar, -1, "NG原因"));
cmd.Parameters.Add(new OleDbParameter("來源編號", OleDbType.VarWChar, 255, "工時資料編號"));
adapter.InsertCommand = cmd;
foreach (DataRow row in inspectedRows)
{
if (!string.IsNullOrEmpty(row["NG原因"] as string))
row.SetAdded();
}
adapter.Update(inspectedRows);
return update;
}
public static 待驗清單DataTable GetUnreinspectList(string line, string name)
{
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 AND 檢驗結果=False AND 特許=False AND 重驗=False");
if (line != null && line.Trim() != string.Empty)
{
cmd.Parameters.Add(new OleDbParameter("產線", line));
whereList.Add("產線 = ?");
}
if (name != null && name.Trim() != string.Empty)
{
cmd.Parameters.Add(new OleDbParameter("姓名", name));
whereList.Add("姓名 = ?");
}
string cmdText = "SELECT 產線, 單據日期, 工作單號, P.品號, WP.數量 as 總數量, 待驗數量, 預計完成日, QCN, 送檢次數, 檢驗, 檢驗結果, 工時資料編號, 送檢日期 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 P.品號 = WP.品號)";
if (whereList.Count > 0)
cmdText += " WHERE " + string.Join(" AND ", whereList.ToArray());
cmdText += " ORDER BY 送檢日期, 單據日期, 工作單號, 工品編號";
cmd.CommandText = cmdText;
OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
待驗清單DataTable table = new 待驗清單DataTable();
adapter.Fill(table);
//檢查是否立即待驗及取得完成數量
table.Columns.Add("序號", typeof(int));
table.Columns.Add("待驗提醒", typeof(string));
table.Columns.Add("已完成", typeof(decimal)).DefaultValue = 0;
table.Columns.Add("NG原因", typeof(string)).DefaultValue = null;
table.Columns.Add("舊NG原因", typeof(string));
table.Columns.Add("特許", typeof(bool));
cmd = new OleDbCommand("SELECT SUM(待驗數量) + SUM(H.數量) >= WP.數量, SUM(H.數量) as 已完成" +
" FROM ((工時 as H LEFT JOIN (SELECT * FROM 產品檢驗 WHERE 檢驗 = FALSE) as Q on H.編號 = Q.工時資料編號) " +
" INNER JOIN 工作單品號 as WP ON H.工作單號 = WP.單號 AND H.工品編號 = WP.編號)" +
" WHERE WP.單號=? AND WP.編號=? " +
" GROUP BY 單號, WP.編號, WP.數量", conn);
cmd.Parameters.Add("單號", OleDbType.VarWChar);
cmd.Parameters.Add("編號", OleDbType.Integer);
conn.Open();
string curWorksheet = null;
int curWpid = -1;
foreach (DataRow row in table)
{
string worksheet = row["工作單號"].ToString();
int wpid = (int)row["工品編號"];
if (worksheet != curWorksheet || wpid != curWpid)
{
curWorksheet = worksheet;
curWpid = wpid;
DataRow[] rows = table.Select("工作單號='" + worksheet + "' AND 工品編號=" + wpid);
int sn = 1;
foreach (DataRow r in rows)
r["序號"] = sn++;
}
cmd.Parameters[0].Value = worksheet;
cmd.Parameters[1].Value = wpid;
//bool inspectImm = false;
OleDbDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
//object result = dr[0];
//if (result != DBNull.Value)
// inspectImm = Convert.ToInt32(result) != 0;
object complete = dr[1];
if (complete != DBNull.Value)
row["已完成"] = complete;
}
dr.Close();
//if (inspectImm)
// row["待驗提醒"] = "待驗";
//取得舊NG原因
string id = row["工時資料編號"].ToString();
string[] ngReasons = GetNGReason(id);
row["舊NG原因"] = string.Join("\n", ngReasons);
}
conn.Close();
return table;
}
public static int UpdateInspectDeleteList(待驗清單DataTable table, out int delete)
{
OleDbConnection conn = new OleDbConnection(Properties.Settings.Default.dbConnectionString);
//減去刪除的數量
string cmdText = "UPDATE 工時 SET 數量 = 0 WHERE 編號 = ?";
OleDbCommand cmd = new OleDbCommand(cmdText, conn);
//cmd.Parameters.Add(new OleDbParameter("數量", OleDbType.Integer, -1, "待驗數量"));
cmd.Parameters.Add(new OleDbParameter("編號", OleDbType.VarWChar, 255, "工時資料編號"));
OleDbDataAdapter adapter = new OleDbDataAdapter();
adapter.UpdateCommand = cmd;
DataRow[] rows = table.Copy().Select("刪除=True");
delete = adapter.Update(rows);
//判斷是否要回復未完成狀態
foreach (DataRow row in rows)
{
string worksheet = (string)row["工作單號"];
int wpid = (int)row["工品編號"];
//若未完成則恢復成未完成狀態
if (!CheckFinish(worksheet, wpid))
SetFinishDate(worksheet, wpid, null);
//取得工作單資料
//工作單DataTable wsTable = 工作單TableAdapter.Instance.GetBy單號(worksheet);
////如果設定了實際完成日，則檢查是否恢復成未完成狀態
//if (wsTable[0][wsTable.實際完成日Column] != DBNull.Value)
//{
// //檢查是否全部已完成
// DatabaseSet.工作單品號DataTable wpTable = 工作單品號TableAdapter.Instance.GetBy單號(worksheet);
// bool allFinished = true;
// foreach (DatabaseSet.工作單品號Row wsRow in wpTable)
// {
// if (wsRow["實際完成日"] == DBNull.Value)
// {
// allFinished = false;
// break;
// }
// }
// if (!allFinished)
// 工作單TableAdapter.Instance.SetFinishDate(null, worksheet);
//}
UpdateWorksheetFinishDate(worksheet);
}
//更新已刪除的資料
cmdText = "UPDATE 產品檢驗 SET 檢驗=False, 檢驗結果=False, 日期=NULL, 特許=False WHERE 工時資料編號=?";
cmd = new OleDbCommand(cmdText, conn);
cmd.Parameters.Add(new OleDbParameter("工時資料編號", OleDbType.VarWChar, 255, "工時資料編號"));
adapter.UpdateCommand = cmd;
DataRow[] inspectedRows = table.Select("刪除 = True");
int update = adapter.Update(inspectedRows);
return update;
}
public static 待驗清單DataTable GetInspectCompleteList(string qcn, string partnumber, DateTime date)
{
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 AND 檢驗結果 = 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("品號 LIKE ?");
}
if (date != DateTime.MinValue)
{
OleDbParameter param = new OleDbParameter("日期", OleDbType.Date);
param.Value = date;
cmd.Parameters.Add(param);
whereList.Add("datediff(\"d\", ?, Q.日期)=0");
//whereList.Add("Q.日期 = ?");
}
string cmdText = "SELECT 單據日期, 工作單號, 品號, 客戶, WP.數量 as 總數量, 待驗數量, QCN, 檢驗結果, Q.日期 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.編號)";
if (whereList.Count > 0)
cmdText += " WHERE " + string.Join(" AND ", whereList.ToArray());
cmdText += " ORDER BY 單據日期, 工作單號, 工品編號";
cmd.CommandText = cmdText;
OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
待驗清單DataTable table = new 待驗清單DataTable();
adapter.Fill(table);
//取得完成數量
table.Columns.Add("序號", typeof(int));
table.Columns.Add("已完成", typeof(decimal)).DefaultValue = 0;
cmd = new OleDbCommand("SELECT SUM(H.數量) as 已完成" +
" FROM ((工時 as H LEFT JOIN (SELECT * FROM 產品檢驗 WHERE 檢驗 = FALSE) as Q on H.編號 = Q.工時資料編號) " +
" INNER JOIN 工作單品號 as WP ON H.工作單號 = WP.單號 AND H.工品編號 = WP.編號)" +
" WHERE WP.單號=? AND WP.編號=? " +
" GROUP BY 單號, WP.編號, WP.數量", conn);
cmd.Parameters.Add("單號", OleDbType.VarWChar);
cmd.Parameters.Add("編號", OleDbType.Integer);
conn.Open();
string curWorksheet = null;
int curWpid = -1;
foreach (DataRow row in table)
{
string worksheet = row["工作單號"].ToString();
int wpid = (int)row["工品編號"];
if (worksheet != curWorksheet || wpid != curWpid)
{
curWorksheet = worksheet;
curWpid = wpid;
DataRow[] rows = table.Select("工作單號='" + worksheet + "' AND 工品編號=" + wpid);
int sn = 1;
foreach (DataRow r in rows)
r["序號"] = sn++;
}
cmd.Parameters[0].Value = worksheet;
cmd.Parameters[1].Value = wpid;
OleDbDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
object complete = dr[0];
if (complete != DBNull.Value)
row["已完成"] = complete;
}
dr.Close();
}
conn.Close();
return table;
}