protected override void BeforeExport()
{
Sheets = new Worksheet[this.Workbook.Sheets.Count];
SheetAdapters = new WorksheetAdapter[Sheets.Length];
for (int i = 0; i < Sheets.Length; i++)
{
this.Sheets[i] = this.Workbook.Worksheets[i+1] as Worksheet;
this.SheetAdapters[i] = new WorksheetAdapter();
this.SheetAdapters[i].Worksheet = Sheets[i];
}
base.BeforeExport();
}
void ImportProductsData()
{
WorksheetAdapter sheetAdapter = new WorksheetAdapter(_sheet);
Dictionary<string, DataRow> dic = new Dictionary<string, DataRow>();
DataTable dstTable = (DataTable)dgvDstCol.DataSource;
int serialNoCol = (int)_dstRowList["�t�C�s��"]["����"];
int serialCodeNameCol = (int)_dstRowList["�t�C�N��"]["����"];
//int serialNameCol = (int)_dstRowList["�t�C�W��"]["����"];
int partNoCol = (int)_dstRowList["�~��"]["����"];
int partNameCol = (int)_dstRowList["�~�W"]["����"];
int lineCol = (int)_dstRowList["���u"]["����"];
int laborHoursCol = (int)_dstRowList["�u��"]["����"];
int laborCostCol = (int)_dstRowList["�t���~"]["����"];
//int wageCol = (int)_dstRowList["�зǤu��"]["����"];
this.BeginInvoke(new Action<string>(UpdateImportMessage),"�q�u�@���^�����~���...");
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("�t�C�s�����榡���~"); }
string serialCodeName;
try { serialCodeName = sheetAdapter.GetValue<string>(i, serialCodeNameCol); }
catch (Exception) { throw new SWLHMSException("�t�C�N�����榡���~"); }
//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("���u���榡���~"); }
if (!lineList.Contains(line))
lineList.Add(line);
decimal laborHours;
try { laborHours = Convert.ToDecimal(sheetAdapter.GetValue<double>(i, laborHoursCol)); }
catch (Exception) { throw new SWLHMSException("�u�����榡���~"); }
//decimal wage;
//try { wage = Convert.ToDecimal(sheetAdapter.GetValue<double>(i, wageCol)); }
//catch (Exception) { throw new Exception("�зǤu�����榡���~"); }
decimal laborCost;
try { laborCost = Convert.ToDecimal(sheetAdapter.GetValue<double>(i, laborCostCol)); }
catch (Exception) { throw new SWLHMSException("���зǤu�����榡���~"); }
DatabaseSet.���~�~��Row row;
// Make sure the serial exists
if (DatabaseSet.���~�t�CTable.Select("�s�� = " + serialNo).Length == 0)
{
DatabaseSet.���~�t�CRow serialRow = DatabaseSet.���~�t�CTable.New���~�t�CRow();
serialRow.FillRow(serialNo,serialCodeName);
DatabaseSet.���~�t�CTable.Add���~�t�CRow(serialRow);
���~�t�CTableAdapter.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.�t�C�s�� = serialNo;
row.�~�W = partName;
row.���u = line;
row.�u�� = laborHours;
//row.�зǤu�� = wage;
row.�зǤu�� = laborHours * laborCost;
row.���зǤu�� = laborCost;
// If part number is duplicated, use the latter
if (dic.ContainsKey(partNubmer))
{
sbEx.AppendLine("�� " + i + ": �~�� '" + partNubmer + "' ���ơA�ĥΫ�̸���x�s");
dic[partNubmer] = row;
}
else
dic.Add(partNubmer, row);
}
}
catch (Exception ex)
{
if (errorCount++ > errorMax)
errorBreak = true;
sbEx.AppendLine("�� " + i + ": �o�Ϳ��~ " + ex.Message);
}
this.BeginInvoke(new MethodInvoker(progressBar1.PerformStep));
if (errorBreak)
break;
}
StringBuilder sb = new StringBuilder();
if (!errorBreak)
{
this.BeginInvoke(new Action<string>(UpdateImportMessage), "��s��Ʈw...");
//�ˬd���L�s�W���u
List<string> newLine = new List<string>();
foreach (string l in lineList)
{
if (���uTableAdapter.Instance.CheckLineAndCreate(l))
newLine.Add(l);
}
int updateCount = ���~�~��TableAdapter.Instance.UpdateEx(updateTable);
int insertCount = ���~�~��TableAdapter.Instance.UpdateEx(insertTable);
//���ͳ��i
sb.AppendLine("�ӷ��ɮ�: " + dic.Count + " ���~�����");
sb.AppendLine("��Ʈw : " + ���~�~��TableAdapter.Instance.GetCount() + " ���~�����");
sb.AppendLine("�w��s : " + updateCount + " �����");
sb.AppendLine("�w�s�W : " + insertCount + " �����");
if (newSerial.Count > 0)
{
List<string> list = new List<string>();
foreach (int serial in newSerial)
list.Add(serial.ToString());
sb.AppendLine("�إߤF�s�t�C : " + string.Join(",", list.ToArray()));
}
if (newLine.Count > 0)
sb.AppendLine("�إߤF�s���u : " + string.Join(",", newLine.ToArray()));
}
else
{
sb.AppendLine("�o�Ϳ��~�Ӧh�A����פJ");
}
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));
}
