public void EditWorksheet(string WorksheetNumber)
{
if ((_worksheetTable = 工作單TableAdapter.Instance.GetBy單號(WorksheetNumber)).Count > 0)
{
bsWorksheet.DataSource = _worksheetTable[0];
tbxWorksheetNumber.ReadOnly = true;
tbxWorksheetNumber.DataBindings.Add("Text", bsWorksheet, "單號", true, DataSourceUpdateMode.Never);
//tbxCustomerName.DataBindings.Add("Text", bsWorksheet, "客戶名稱", true, DataSourceUpdateMode.OnPropertyChanged);
dtpBegin.DataBindings.Add("Value", bsWorksheet, "單據日期", true, DataSourceUpdateMode.OnPropertyChanged);
dtpEnd.DataBindings.Add("Value", bsWorksheet, "實際完成日", true, DataSourceUpdateMode.Never, DateTime.Today);
//dtpCustomerNeed.DataBindings.Add("Value", bsWorksheet, "客戶需貨日", true, DataSourceUpdateMode.OnPropertyChanged);
//dtpEstimate.DataBindings.Add("Value", bsWorksheet, "預計完成日", true, DataSourceUpdateMode.OnPropertyChanged);
if (_worksheetTable[0]["實際完成日"] != DBNull.Value)
{
ckbEnd.Checked = true;
}
_worksheetPartTable = 工作單品號TableAdapter.Instance.GetBy單號(_worksheetTable[0].單號);
bsWorksheetPart.DataSource = _worksheetPartTable;
//判斷目前工作單所屬產線
if (_worksheetPartTable.Count > 0)
{
DatabaseSet.產品品號DataTable tmpTable = 產品品號TableAdapter.Instance.GetBy品號(_worksheetPartTable[0].品號);
if (tmpTable.Count > 0)
{
_curWorkshhetLine = tmpTable[0].產線;
}
else
{
throw new SWLHMSException("找不到品號 " + _worksheetPartTable[0].品號 + " 的資料");
}
}
btnAddWorksheet.Visible = false;
}
else
{
throw new SWLHMSException("找不到單號 " + WorksheetNumber + " 的資料");
}
}
public virtual DatabaseSet.產品品號DataTable GetBy品號(string 品號) {
this.Adapter.SelectCommand = this.CommandCollection[1];
if ((品號 == null)) {
throw new global::System.ArgumentNullException("品號");
}
else {
this.Adapter.SelectCommand.Parameters[0].Value = ((string)(品號));
}
DatabaseSet.產品品號DataTable dataTable = new DatabaseSet.產品品號DataTable();
this.Adapter.Fill(dataTable);
return dataTable;
}
public virtual DatabaseSet.產品品號DataTable GetData() {
this.Adapter.SelectCommand = this.CommandCollection[0];
DatabaseSet.產品品號DataTable dataTable = new DatabaseSet.產品品號DataTable();
this.Adapter.Fill(dataTable);
return dataTable;
}
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 btnAddPart_Click(object sender, EventArgs e)
{
try
{
if (tbxWorksheetNumber.Text == string.Empty)
{
throw new SWLHMSException("請先填寫工作單號");
}
if (dgvPart.CurrentRow != null && bsWorksheetPart.DataSource != null)
{
string partNumber = tbxPartNumber.Text;
string line;
if (partNumber != dgvPart.CurrentRow.Cells["col品號2"].Value.ToString())
{
DatabaseSet.產品品號DataTable tmpTable = 產品品號TableAdapter.Instance.GetBy品號(partNumber);
if (tmpTable.Rows.Count == 0)
{
throw new SWLHMSException("品號 " + partNumber + " 不存在");
}
line = tmpTable[0].產線;
}
else
{
line = dgvPart.CurrentRow.Cells["col產線"].Value.ToString();
}
DatabaseSet.工作單品號DataTable table = bsWorksheetPart.DataSource as DatabaseSet.工作單品號DataTable;
//檢查產線一致性
if (table.Count > 0)
{
if (line != _curWorkshhetLine)
{
throw new SWLHMSException("目前工作單產線為 " + _curWorkshhetLine + "，品號 " + partNumber + " 產線為 " + line + "，請選擇同一產線品號");
}
}
else
{
_curWorkshhetLine = line;
}
DatabaseSet.工作單品號Row row = table.New工作單品號Row();
row.FillRow(tbxWorksheetNumber.Text, partNumber, 0);
// For tmp value
int id = 0;
object tmp = table.Compute("MAX(編號)", null);
if (tmp != DBNull.Value)
{
id = Convert.ToInt32(tmp);
}
row.編號 = id + 1;
table.Rows.Add(row);
}
}
catch (ConstraintException)
{
MessageBox.Show("品號 " + dgvPart.CurrentRow.Cells["col品號2"].Value.ToString() + " 已經存在");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
