private void btnStoreSeries_Click(object sender, EventArgs e)
{
int newSeries = int.Parse(tbxSeries.Text);
string newSeriesName = tbxSeriesName.Text;
try
{
if (this.SerialState == EditStateType.Edit)
{
if (bsSeries.Current != null)
{
DatabaseSet.產品系列Row row = this.SelectedSerialRow;
row.FillRow(newSeries, newSeriesName);
產品系列TableAdapter.Instance.Update(row);
LoadPart(false);
}
}
else if (this.SerialState == EditStateType.New)
{
DatabaseSet.產品系列Row newRow = DatabaseSet.產品系列Table.New產品系列Row();
newRow.FillRow(newSeries, newSeriesName);
DatabaseSet.產品系列Table.Rows.Add(newRow);
產品系列TableAdapter.Instance.Update(newRow);
}
this.SerialState = EditStateType.None;
}
catch (ConstraintException)
{
MessageBox.Show("已存在系列號 " + newSeries + "，請指定其他系列號");
tbxSeries.SelectAll();
}
catch (Exception ex)
{
Global.ShowError(ex);
}
finally
{
if (this.SerialState == EditStateType.Edit)
{
tbxSeries.DataBindings[0].ReadValue();
tbxSeriesName.DataBindings[0].ReadValue();
}
}
}
void ImportProductsData()
{
WorksheetAdapter sheetAdapter = new WorksheetAdapter(_sheet);
Dictionary <string, DataRow> dic = new Dictionary <string, DataRow>();
DataTable dstTable = (DataTable)dgvDstCol.DataSource;
int serialNoCol = (int)_dstRowList["系列編號"]["索引"];
int serialCodeNameCol = (int)_dstRowList["系列代號"]["索引"];
//int serialNameCol = (int)_dstRowList["系列名稱"]["索引"];
int partNoCol = (int)_dstRowList["品號"]["索引"];
int partNameCol = (int)_dstRowList["品名"]["索引"];
int lineCol = (int)_dstRowList["產線"]["索引"];
int laborHoursCol = (int)_dstRowList["工時"]["索引"];
int laborCostCol = (int)_dstRowList["系統時薪"]["索引"];
//int wageCol = (int)_dstRowList["標準工資"]["索引"];
this.BeginInvoke(new Action <string>(UpdateImportMessage), "從工作表擷取產品資料...");
this.BeginInvoke(new Action <int>(InitProgressBar), _sheet.UsedRange.Rows.Count - 1);
StringBuilder sbEx = new StringBuilder();
DatabaseSet.產品品號DataTable updateTable = new DatabaseSet.產品品號DataTable();
DatabaseSet.產品品號DataTable insertTable = new DatabaseSet.產品品號DataTable();
List <string> lineList = new List <string>();
List <int> newSerial = new List <int>();
bool errorBreak = false;
int errorCount = 0;
int errorMax = 50;
for (int i = 2; i <= _sheet.UsedRange.Rows.Count; i++)
{
try
{
string partNubmer = sheetAdapter.GetNotEmptyString(i, partNoCol);
if (partNubmer != null)
{
int serialNo;
try { serialNo = sheetAdapter.GetValue <int>(i, serialNoCol); }
catch (Exception) { throw new SWLHMSException("系列編號欄位格式錯誤"); }
string serialCodeName;
try { serialCodeName = sheetAdapter.GetValue <string>(i, serialCodeNameCol); }
catch (Exception) { throw new SWLHMSException("系列代號欄位格式錯誤"); }
//string serialName = sheetAdapter.GetValue<string>(i, serialNameCol, false);
string partName = sheetAdapter.GetValue <string>(i, partNameCol, false);
string line;
try{ line = sheetAdapter.GetValue <string>(i, lineCol); }
catch (Exception) { throw new SWLHMSException("產線欄位格式錯誤"); }
if (!lineList.Contains(line))
{
lineList.Add(line);
}
decimal laborHours;
try { laborHours = Convert.ToDecimal(sheetAdapter.GetValue <double>(i, laborHoursCol)); }
catch (Exception) { throw new SWLHMSException("工時欄位格式錯誤"); }
//decimal wage;
//try { wage = Convert.ToDecimal(sheetAdapter.GetValue<double>(i, wageCol)); }
//catch (Exception) { throw new Exception("標準工資欄位格式錯誤"); }
decimal laborCost;
try { laborCost = Convert.ToDecimal(sheetAdapter.GetValue <double>(i, laborCostCol)); }
catch (Exception) { throw new SWLHMSException("單位標準工資欄位格式錯誤"); }
DatabaseSet.產品品號Row row;
// Make sure the serial exists
if (DatabaseSet.產品系列Table.Select("編號 = " + serialNo).Length == 0)
{
DatabaseSet.產品系列Row serialRow = DatabaseSet.產品系列Table.New產品系列Row();
serialRow.FillRow(serialNo, serialCodeName);
DatabaseSet.產品系列Table.Add產品系列Row(serialRow);
產品系列TableAdapter.Instance.Update(serialRow);
newSerial.Add(serialNo);
}
// Get the conut of the part number from database
int?num = 產品品號TableAdapter.Instance.GetCountBy品號(partNubmer);
// Exists same part number
if (num != null && num > 0)
{
row = updateTable.New產品品號Row();
row.品號 = partNubmer;
updateTable.Add產品品號Row(row);
row.AcceptChanges();
}
else
{
row = insertTable.New產品品號Row();
row.品號 = partNubmer;
insertTable.Add產品品號Row(row);
}
row.系列編號 = serialNo;
row.品名 = partName;
row.產線 = line;
row.工時 = laborHours;
//row.標準工資 = wage;
row.標準工資 = laborHours * laborCost;
row.單位標準工資 = laborCost;
// If part number is duplicated, use the latter
if (dic.ContainsKey(partNubmer))
{
sbEx.AppendLine("行 " + i + ": 品號 '" + partNubmer + "' 重複，採用後者資料儲存");
dic[partNubmer] = row;
}
else
{
dic.Add(partNubmer, row);
}
}
}
catch (Exception ex)
{
if (errorCount++ > errorMax)
{
errorBreak = true;
}
sbEx.AppendLine("行 " + i + ": 發生錯誤 " + ex.Message);
}
this.BeginInvoke(new MethodInvoker(progressBar1.PerformStep));
if (errorBreak)
{
break;
}
}
StringBuilder sb = new StringBuilder();
if (!errorBreak)
{
this.BeginInvoke(new Action <string>(UpdateImportMessage), "更新資料庫...");
//檢查有無新增產線
List <string> newLine = new List <string>();
foreach (string l in lineList)
{
if (產線TableAdapter.Instance.CheckLineAndCreate(l))
{
newLine.Add(l);
}
}
int updateCount = 產品品號TableAdapter.Instance.UpdateEx(updateTable);
int insertCount = 產品品號TableAdapter.Instance.UpdateEx(insertTable);
//產生報告
sb.AppendLine("來源檔案: " + dic.Count + " 筆品號資料");
sb.AppendLine("資料庫 : " + 產品品號TableAdapter.Instance.GetCount() + " 筆品號資料");
sb.AppendLine("已更新 : " + updateCount + " 筆資料");
sb.AppendLine("已新增 : " + insertCount + " 筆資料");
if (newSerial.Count > 0)
{
List <string> list = new List <string>();
foreach (int serial in newSerial)
{
list.Add(serial.ToString());
}
sb.AppendLine("建立了新系列 : " + string.Join(",", list.ToArray()));
}
if (newLine.Count > 0)
{
sb.AppendLine("建立了新產線 : " + string.Join(",", newLine.ToArray()));
}
}
else
{
sb.AppendLine("發生錯誤太多，停止匯入");
}
if (sbEx.Length > 0)
{
sb.AppendLine("===錯誤===");
sb.AppendLine(sbEx.ToString());
}
_report = sb.ToString();
if (!errorBreak)
{
this.BeginInvoke(new MethodInvoker(ImportDone));
}
else
{
this.BeginInvoke(new MethodInvoker(ImportError));
}
}
private void btnDelSeries_Click(object sender, EventArgs e)
{
try
{
if (bsSeries.Current != null)
{
DatabaseSet.產品系列Row row = (bsSeries.Current as DataRowView).Row as DatabaseSet.產品系列Row;
int series = row.編號;
string seriesName = row.代號;
// Check whether all partnubmer are not in any worksheet
OleDbConnection conn = 工作單品號TableAdapter.Instance.Connection;
string cmdTxt = "SELECT COUNT(*) FROM 工作單品號 INNER JOIN 產品品號 ON 工作單品號.品號 = 產品品號.品號 WHERE 系列編號 = " + series;
OleDbCommand cmd = new OleDbCommand(cmdTxt, conn);
conn.Open();
object o = cmd.ExecuteScalar();
conn.Close();
if (o != null && (int)o != 0)
{
if (MessageBox.Show("系列" + series + "(" + seriesName + ")部分品號已建立工作單無法刪除，確定刪除其他品號?", "刪除提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning) == DialogResult.OK)
{
conn.Open();
cmd = new OleDbCommand(
"SELECT 產品品號.品號 FROM 產品品號 LEFT JOIN 工作單品號 ON 工作單品號.品號 = 產品品號.品號 WHERE 系列編號 = " + series + " AND 單號 IS NULL"
, conn);
OleDbDataReader dr = cmd.ExecuteReader();
string delCmdTxt = "DELETE FROM 產品品號 WHERE 品號 = ?";
OleDbCommand delCmd = new OleDbCommand(delCmdTxt, conn);
OleDbParameter param = new OleDbParameter();
delCmd.Parameters.Add(param);
int delCount = 0;
while (dr.Read())
{
string pn = (string)dr.GetValue(0);
param.Value = pn;
delCount += delCmd.ExecuteNonQuery();
}
conn.Close();
MessageBox.Show("刪除了 " + delCount + " 筆品號");
}
}
else
{
if (MessageBox.Show("確定刪除 系列" + series + "(" + seriesName + ") 及其所有品號?", "刪除提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning) == DialogResult.OK)
{
// delete all series
row.Delete();
產品系列TableAdapter.Instance.Update(row);
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
