private void btnUpload_Click(object sender, EventArgs e)
{
string filename = "";
OpenFileDialog ofd = new OpenFileDialog()
{
Filter = "Excel Files |*.xls"
};
uploadtable = new DataTable();
if (ofd.ShowDialog() == DialogResult.OK)
{
filename = ofd.FileName;
uploadtable = ImportXlsUtil.TranslateToTable(filename);
frmUploadConfirm form = new frmUploadConfirm(uploadtable);
form.ShowDialog();
if (form.DialogResult == DialogResult.OK)
{
if (!worker.IsBusy)
{
this.Cursor = Cursors.WaitCursor;
worker.RunWorkerAsync();
}
else
{
MessageBox.Show("File is using by other process");
}
}
}
}
private void worker_DoWork(object sender, DoWorkEventArgs e)
{
try
{
DataTable tmptable = ImportXlsUtil.TranslateToTable(filename);
foreach (DataRow row in tmptable.Rows)
{
string date = row.ItemArray[0].ToString();
string managementno = row.ItemArray[1].ToString();
string incharge = row.ItemArray[2].ToString();
string vendorcode = row.ItemArray[3].ToString();
string vendorname = row.ItemArray[4].ToString();
string group = row.ItemArray[5].ToString();
string partno = row.ItemArray[6].ToString();
string rev = row.ItemArray[7].ToString();
string currency = row.ItemArray[8].ToString();
string amountbefore = row.ItemArray[9].ToString();
string amountafter = row.ItemArray[10].ToString();
string amounthkdbefore = row.ItemArray[11].ToString();
string amounthkdafter = row.ItemArray[12].ToString();
string cdtotal = row.ItemArray[13].ToString();
string cdpercent = row.ItemArray[14].ToString();
if (rev.Length == 1)
{
rev = "0" + rev;
}
vendorname = vendorname.Replace("'", "''");
date = ImportXlsUtil.ParseDateTime(date).ToString("yyyy/MM/dd");
if (managementno == "")
{
continue;
}
string query = string.Format("if not exists (select * from tb_costdownreport where cd_reportno = '{0}') insert into tb_costdownreport (cd_inputdate, cd_reportno" +
", cd_itemcode, cd_rev, cd_pricebefore, cd_priceafter, cd_amount, cd_currency, cd_vendor_code, cd_vendor_name, cd_group, cd_purchaser" +
", cd_pricehkdbefore, cd_pricehkdafter, cd_percent) values ('{1}', '{0}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', '{10}', '{11}', '{12}', '{13}', '{14}')" +
" else update tb_costdownreport set cd_inputdate = '{1}', cd_itemcode = '{2}', cd_rev = '{3}', cd_pricebefore = '{4}', cd_priceafter = '{5}', cd_amount = '{6}'" +
", cd_currency = '{7}', cd_vendor_code = '{8}', cd_vendor_name = '{9}', cd_group = '{10}', cd_purchaser = '{11}', cd_pricehkdbefore = '{12}'" +
", cd_pricehkdafter = '{13}', cd_percent = '{14}' where cd_reportno = '{0}'", managementno, date, partno, rev, amountbefore, amountafter, cdtotal, currency, vendorcode,
vendorname, group, incharge, amounthkdbefore, amounthkdafter, cdpercent);
Debug.WriteLine("Query: " + query);
DataService.GetInstance().ExecuteNonQuery(query);
}
MessageBox.Show("Data uploaded");
}
catch (Exception ex)
{
MessageBox.Show("Invalid data format");
Debug.WriteLine(ex.Message + ex.StackTrace);
}
}
private void btnUpload_Click(object sender, EventArgs e)
{
OpenFileDialog ofd = new OpenFileDialog()
{
Filter = "Excel Files |*.xls"
};
if (ofd.ShowDialog() == DialogResult.OK)
{
filename = ofd.FileName;
uploadTable = new DataTable();
uploadTable = ImportXlsUtil.TranslateToTable(filename);
for (int i = 0; i < uploadTable.Rows.Count; i++)
{
string issued = uploadTable.Rows[i][14].ToString();
string instock50 = uploadTable.Rows[i][15].ToString();
string instock = uploadTable.Rows[i][16].ToString();
issued = ImportXlsUtil.ParseDateTime(issued).ToString("yyyy/MM/dd");
if (instock50 != "-----" && instock50 != "" && instock50 != "一回合格入庫" && instock50 != "Received" && instock50 != "已拆單、以合格同時入庫")
{
instock50 = ImportXlsUtil.ParseDateTime(instock50).ToString("yyyy/MM/dd");
}
if (instock != "" && instock != "Received" && instock != "#N/A")
{
instock = ImportXlsUtil.ParseDateTime(instock).ToString("yyyy/MM/dd");
}
uploadTable.Rows[i][14] = issued;
uploadTable.Rows[i][15] = instock50;
uploadTable.Rows[i][16] = instock;
}
frmUploadConfirm form = new frmUploadConfirm(uploadTable);
form.ShowDialog();
if (form.DialogResult == DialogResult.OK)
{
if (!worker.IsBusy)
{
this.Cursor = Cursors.WaitCursor;
dgvPreview.Cursor = Cursors.WaitCursor;
dgvResult.Cursor = Cursors.WaitCursor;
worker.RunWorkerAsync();
}
else
{
MessageBox.Show("Using by other process");
}
}
}
}
private void btnUpload_Click(object sender, EventArgs e)
{
string filename = "";
potable = new DataTable();
OpenFileDialog ofd = new OpenFileDialog()
{
Filter = "Excel Files |*.xls"
};
if (ofd.ShowDialog() == DialogResult.OK)
{
filename = ofd.FileName;
try
{
potable = ImportXlsUtil.TranslateToTable(filename);
frmUploadConfirm form = new frmUploadConfirm(potable);
form.ShowDialog();
if (form.DialogResult == DialogResult.OK)
{
if (!worker.IsBusy)
{
this.Cursor = Cursors.WaitCursor;
dgvIssuePo.Cursor = Cursors.WaitCursor;
worker.RunWorkerAsync();
}
else
{
MessageBox.Show("File is using by other process");
}
}
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message + ex.StackTrace);
validData = false;
}
}
}
private void btnUploadCn_Click(object sender, EventArgs e)
{
OpenFileDialog ofd = new OpenFileDialog();
if (ofd.ShowDialog() == DialogResult.OK)
{
DataTable table = ImportXlsUtil.TranslateToTable(ofd.FileName);
foreach (DataRow row in table.Rows)
{
string organization = row.ItemArray[14].ToString().Trim();
string accountcode = row.ItemArray[17].ToString().Trim();
string costcenter = row.ItemArray[18].ToString().Trim();
string fixedasset = row.ItemArray[19].ToString().Trim();
string location = row.ItemArray[21].ToString().Trim();
string chaseno = row.ItemArray[23].ToString().Trim();
string request = row.ItemArray[26].ToString().Trim();
string po = row.ItemArray[27].ToString().Trim();
string porev = row.ItemArray[28].ToString().Trim();
string today = DateTime.Today.ToString("yyyy/MM/dd");
if (porev.Length == 2)
{
po = po + "000" + porev;
}
else
{
po = po + "00" + porev;
}
string query = string.Format("update tb_betamould set tm_accountcode = '{0}', tm_costcentre = '{1}', tm_fixedassetcode = '{2}', tm_request = '{3}'" +
", tm_po = '{4}', tm_porev = '{5}', tm_poissued = '{6}', tm_cnsendtime = '{7}', tm_st_code = 'K' where tm_chaseno = '{8}'", accountcode, costcenter, fixedasset, request,
po, porev, today, today, chaseno);
Debug.WriteLine(query);
DataService.GetInstance().ExecuteNonQuery(query);
}
MessageBox.Show("Record has been uploaded.");
}
loadData();
}
public frmProgressBar(string filename)
{
InitializeComponent();
DataTable tmptable = ImportXlsUtil.TranslateToTable(filename);
//customProgressBar1.Start();
int count = 0;
foreach (DataRow row in tmptable.Rows)
{
string rowChaseno = row.ItemArray[15].ToString();
string rowPassremarks = row.ItemArray[20].ToString();
string rowPassdate = row.ItemArray[21].ToString();
string rowCollectdate = row.ItemArray[22].ToString();
if (rowPassdate != "")
{
rowPassdate = ImportXlsUtil.ParseDateTime(rowPassdate).ToString("yyyy/MM/dd");
}
string query = string.Format("update tb_betamould set tm_passdate = '{0}', tm_collectdate = '{1}', tm_passremarks = '{2}' where tm_chaseno = '{3}'",
rowPassdate, rowCollectdate, rowPassremarks, rowChaseno);
//DataService.GetInstance().ExecuteNonQuery(query);
string query2 = string.Format("update tb_betamould set tm_facremarks = tm_collectdate where tm_chaseno = '{0}' and tm_fixedAssetCode != ''", rowChaseno);
//DataService.GetInstance().ExecuteNonQuery(query2);
count += 1;
System.Diagnostics.Debug.WriteLine("" + count);
}
Thread.Sleep(10);
this.DialogResult = DialogResult.OK;
}
private void worker_DoWork(object sender, DoWorkEventArgs e)
{
DataTable tmptable = ImportXlsUtil.TranslateToTable(filename);
int count = 0;
foreach (DataRow row in tmptable.Rows)
{
string rowGroup = row.ItemArray[2].ToString();
string rowChaseno = row.ItemArray[15].ToString();
string rowPassremarks = row.ItemArray[20].ToString();
string rowPassdate = row.ItemArray[21].ToString();
string rowCollectdate = row.ItemArray[22].ToString();
//if (rowPassdate.Contains("4"))
//rowPassdate = ImportXlsUtil.ParseDateTime(rowPassdate).ToString("yyyy/MM/dd");
rowPassremarks = rowPassremarks.Replace("'", "''");
string query = string.Format("update tb_betamould set tm_passdate = '{0}', tm_collectdate = '{1}', tm_passremarks = '{2}', tm_checkdate2 = '{4}' where tm_chaseno = '{3}'",
rowPassdate, rowCollectdate, rowPassremarks, rowChaseno, rowGroup);
DataService.GetInstance().ExecuteNonQuery(query);
string query2 = string.Format("update tb_betamould set tm_facremarks = tm_collectdate where tm_chaseno = '{0}' and tm_fixedAssetCode != ''", rowChaseno);
DataService.GetInstance().ExecuteNonQuery(query2);
if (rowCollectdate != "")
{
string query3 = string.Format("update TB_FA_APPROVAL set f_remarks = N'{0}' where f_mould = '{1}'", rowCollectdate, rowChaseno);
DataServiceNew.GetInstance().ExecuteNonQuery(query3);
}
count += 1;
}
}
private void worker_DoWork(object sender, DoWorkEventArgs e)
{
DataTable vendorTable = new DataTable();
vendorTable = ImportXlsUtil.TranslateToTable(filename);
foreach (DataRow row in vendorTable.Rows)
{
string vendorcode = row.ItemArray[0].ToString();
string vendorname = row.ItemArray[1].ToString();
string pgroup = row.ItemArray[2].ToString();
string detail = row.ItemArray[3].ToString();
string paycurr = row.ItemArray[4].ToString();
string payterm = row.ItemArray[5].ToString();
string request = row.ItemArray[6].ToString();
string edi = row.ItemArray[7].ToString();
string remarks = row.ItemArray[8].ToString();
if (vendorcode.Length == 9)
{
vendorcode = "0" + vendorcode;
}
vendorname = vendorname.Replace("'", "");
string query = string.Format("if exists (select * from tb_vendor where v_code = '{0}') update tb_vendor set v_name = '{1}', v_group = '{2}'" +
", v_detail = '{3}', v_paycurr = '{4}', v_payterm = '{5}', v_request = '{6}', v_edi = '{7}', v_remarks = '{8}' where v_code = '{0}'" +
" else insert into tb_vendor (v_code, v_name, v_group, v_detail, v_paycurr, v_payterm, v_request, v_edi, v_remarks) values" +
" ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}')", vendorcode, vendorname, pgroup, detail, paycurr, payterm, request, edi, remarks);
DataService.GetInstance().ExecuteNonQuery(query);
}
string logText = string.Format("insert into tb_log (log_module, log_fromvalue, log_tovalue, log_user, log_datetime, log_chaseno, log_message) values" +
" ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}')", "Vendor", "-", "-", AdUtil.getUsername("kmhk.local"), DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss"), "-", "Master Uploaded");
DataService.GetInstance().ExecuteNonQuery(logText);
MessageBox.Show("Data uploaded");
}
private void workerTM_DoWork(object sender, DoWorkEventArgs e)
{
foreach (DataRow row in uploadTable.Rows)
{
string chaseno = row.ItemArray[0].ToString();
if (chaseno.StartsWith("CM"))
{
string instockdate = ImportXls2007Util.ParseDateTime(row.ItemArray[3].ToString()).ToString("yyyy/MM/dd");
string query = string.Format("update tb_betamould set tm_instockdate = '{0}', tm_st_code = 'S' where tm_chaseno = '{1}'", instockdate, chaseno);
DataService.GetInstance().ExecuteNonQuery(query);
}
else
{
string checkdate = row.ItemArray[1].ToString();
string instockremarks = row.ItemArray[2].ToString();
string instockdate = row.ItemArray[3].ToString();
string datetime = DateTime.Today.ToString("yyyy/MM/dd");
checkdate = ImportXlsUtil.ParseDateTime(checkdate).ToString("yyyy/MM/dd");
if (checkdate == "0001/01/01")
{
checkdate = "";
}
instockdate = ImportXlsUtil.ParseDateTime(instockdate).ToString("yyyy/MM/dd");
if (instockdate == "0001/01/01")
{
instockdate = "";
}
string query = string.Format("update tb_betamould set tm_instockdate = '{0}', tm_st_code = 'S', tm_checkdate = '{1}', tm_instockremarks = '{2}', tm_moulditemviewer = 'Yes' where tm_chaseno = '{3}'", instockdate, checkdate, instockremarks, chaseno);
DataService.GetInstance().ExecuteNonQuery(query);
}
}
}
private void btnUpload_Click(object sender, EventArgs e)
{
OpenFileDialog ofd = new OpenFileDialog()
{
Filter = "Excel Files |*.xls"
};
DataTable tmptable = new DataTable();
if (ofd.ShowDialog() == DialogResult.OK)
{
tmptable = ImportXlsUtil.TranslateToTable(ofd.FileName);
if (!worker.IsBusy)
{
worker.RunWorkerAsync(tmptable);
}
else
{
MessageBox.Show("Using by other process");
}
}
}
private void customButton2_Click(object sender, EventArgs e)
{
OpenFileDialog ofd = new OpenFileDialog();
ofd.Filter = "Excel Files |*.xls";
DataTable relationTable = new DataTable();
string filename = "";
if (ofd.ShowDialog() == DialogResult.OK)
{
filename = ofd.FileName;
relationTable = new DataTable();
relationTable = ImportXlsUtil.TranslateToTable(filename);
foreach (DataRow row in relationTable.Rows)
{
string chaseno = row[0].ToString();
string qstatus = row[1].ToString();
string remarks = row[2].ToString();
string ringi = row[3].ToString();
string query = string.Format("update tb_betamould set tm_oemasset = '', tm_accountcode = '', tm_costcentre = '', tm_category = 'A'" +
", tm_st_code = '{0}', tm_rm = '{1}', tm_ringi_code = '{2}' where tm_chaseno = '{3}'", qstatus, remarks, ringi, chaseno);
/*string query = string.Format("update tb_betamould set tm_oemasset = '', tm_accountcode = '5005020002', tm_costcentre = '1404000029'" +
* ", tm_st_code = '{0}', tm_rm = '{1}' where tm_chaseno = '{2}'", qstatus, remarks, chaseno);*/
DataService.GetInstance().ExecuteNonQuery(query);
}
}
//MessageBox.Show("Finished");
}
private void worker_DoWork(object sender, DoWorkEventArgs e)
{
try
{
foreach (DataRow row in uploadTable.Rows)
{
string chaseno = row.ItemArray[0].ToString();
string ulInStock = row.ItemArray[16].ToString();
string ulInStock50 = row.ItemArray[15].ToString();
if (ulInStock50 != "" && !ulInStock50.Contains("---") && ulInStock50 != "Received" && ulInStock50 != "一回合格入庫" && ulInStock50 != "已拆單、以合格同時入庫")
{
string vendorcode = DataChecking.getVendorCodeByChaseNo(chaseno);
string payterm = DataChecking.getPayTerm(vendorcode);
ulInStock50 = ImportXlsUtil.ParseDateTime(ulInStock50).ToString("yyyy/MM/dd");
string paymonth = "";
if (payterm == "HK01")
{
paymonth = ImportXlsUtil.ParseDateTime(ulInStock50).AddMonths(1).ToString("yyyy/MM/dd");
}
else if (payterm == "HK02")
{
paymonth = ImportXlsUtil.ParseDateTime(ulInStock50).AddMonths(2).ToString("yyyy/MM/dd");
}
string query = string.Format("update tb_betamould set tm_st_code = 'HS', tm_instockdate50 = '{0}', tm_moulditemviewer = 'Yes', tm_paydate = '{1}' where tm_chaseno = '{2}'", ulInStock50, paymonth, chaseno);
DataService.GetInstance().ExecuteNonQuery(query);
}
if (ulInStock != "" && ulInStock != "Received" && ulInStock != "#N/A")
{
string vendorcode = DataChecking.getVendorCodeByChaseNo(chaseno);
string payterm = DataChecking.getPayTerm(vendorcode);
string paymonth = "";
if (payterm == "HK01")
{
paymonth = ImportXlsUtil.ParseDateTime(ulInStock).AddMonths(1).ToString("yyyy/MM/dd");
}
else if (payterm == "HK02")
{
paymonth = ImportXlsUtil.ParseDateTime(ulInStock).AddMonths(2).ToString("yyyy/MM/dd");
}
ulInStock = ImportXlsUtil.ParseDateTime(ulInStock).ToString("yyyy/MM/dd");
if (ulInStock == "0001/03/02")
{
ulInStock = "";
}
string query = string.Format("update tb_betamould set tm_st_code = 'S', tm_instockdate = '{0}', tm_paydate = '{1}', tm_nofac = 'False', tm_moulditemviewer = 'Yes' where tm_chaseno = '{2}'", ulInStock, paymonth, chaseno);
DataService.GetInstance().ExecuteNonQuery(query);
string expenseText = string.Format("update tb_expensetransfer set et_poinstock = '{0}' where et_chaseno = '{1}'", ulInStock, chaseno);
DataService.GetInstance().ExecuteNonQuery(expenseText);
string query2 = string.Format("update TB_FA_APPROVAL set f_remarks = N'已入庫' where f_mould = '{0}'", chaseno);
DataServiceNew.GetInstance().ExecuteNonQuery(query2);
}
}
MessageBox.Show("Data uploaded");
}
catch
{
MessageBox.Show("Invalid Data Format");
}
}
private void btnUpload_Click(object sender, EventArgs e)
{
OpenFileDialog ofd = new OpenFileDialog()
{
Filter = "Excel Files |*.xls"
};
if (ofd.ShowDialog() == DialogResult.OK)
{
filename = ofd.FileName;
ulTable = new DataTable();
ulTable = ImportXlsUtil.TranslateToTable(filename);
for (int i = 0; i < ulTable.Rows.Count; i++)
{
string date = ulTable.Rows[i][0].ToString();
string issued = ulTable.Rows[i][15].ToString();
string instock = ulTable.Rows[i][16].ToString();
string debit = ulTable.Rows[i][17].ToString();
string sales = ulTable.Rows[i][18].ToString();
date = ImportXlsUtil.ParseDateTime(date).ToString("yyyy/MM/dd");
if (issued != "")
{
issued = ImportXlsUtil.ParseDateTime(issued).ToString("yyyy/MM/dd");
}
if (instock != "")
{
instock = ImportXlsUtil.ParseDateTime(instock).ToString("yyyy/MM/dd");
}
if (debit != "no need" && debit != "")
{
debit = ImportXlsUtil.ParseDateTime(debit).ToString("yyyy/MM/dd");
}
if (sales != "no need" && sales != "")
{
sales = ImportXlsUtil.ParseDateTime(sales).ToString("yyyy/MM/dd");
}
ulTable.Rows[i][0] = date;
ulTable.Rows[i][15] = issued;
ulTable.Rows[i][16] = instock;
ulTable.Rows[i][17] = debit;
ulTable.Rows[i][18] = sales;
}
frmUploadConfirm form = new frmUploadConfirm(ulTable);
form.ShowDialog();
if (form.DialogResult == DialogResult.OK)
{
if (!worker.IsBusy)
{
this.Cursor = Cursors.WaitCursor;
dgvExpenseTransfer.Cursor = Cursors.WaitCursor;
worker.RunWorkerAsync();
}
else
{
MessageBox.Show("File is using by other process");
}
}
}
}
private void btnUpload_Click(object sender, EventArgs e)
{
OpenFileDialog ofd = new OpenFileDialog()
{
Filter = "Excel Files |*.xls"
};
DataTable resulttable = new DataTable();
string[] headers = { "狀態", "Chase No.", "Part No.", "Rev", "Mould No.", "Div.", "Type", "Pcs",
"Currency", "Amount", "AmountHkd", "Mpa", "Fixedassetcode", "TmpFixedassetcode",
"Quantity", "Ringi", "Model", "PO", "PO Issued", "Category", "ItemText", "Project Text",
"Vendor", "Vendor Name", "Group", "Mould Code", "Remarks", "In Stock 50", "In Stock Date",
"Check Date", "Cav", "Weight", "Equipment", "Shot", "Vertical", "Horizontal", "Height",
"In Stock Remarks", "Collect Date", "Pass Remarks", "OEM Asset", "Account Code",
"Cost Centre", "Created By" };
foreach (string header in headers)
{
resulttable.Columns.Add(header);
}
if (ofd.ShowDialog() == DialogResult.OK)
{
string filename = ofd.FileName;
DataTable caltable = ImportXlsUtil.TranslateToTable(filename);
Debug.WriteLine(caltable.Columns[0].ColumnName);
if (caltable.Columns.Count != 1)
{
MessageBox.Show("Error format");
}
else
{
foreach (DataRow row in caltable.Rows)
{
string chaseno = row.ItemArray[0].ToString();
string query = string.Format("select st_status, tm_chaseno, tm_itemcode, tm_rev, tm_mouldno" +
", tm_status, tm_type, tm_pcs, tm_currency, tm_amount, tm_amounthkd, tm_mpa" +
", tm_fixedassetcode, tm_tmpfixedassetcode, tm_qty, tm_ringi_code, tm_model, tm_po" +
", tm_poissued, tm_category, tm_itemtext, tm_projecttext, tm_vendor_code, v_name" +
", v_group, tm_mouldcode_code, tm_rm, tm_instockdate50, tm_instockdate, tm_checkdate" +
", tm_cav, tm_weight, tm_accessory, tm_camera, tm_vertical, tm_horizontal, tm_height" +
", tm_instockremarks, tm_collectdate, tm_passremarks, tm_oemasset, tm_accountcode" +
", tm_costcentre, tm_createdby from TB_BETAMOULD, TB_BETAQSTATUS, TB_VENDOR" +
" where tm_vendor_code = v_code and tm_st_code = st_code and tm_chaseno = '{0}'", chaseno);
GlobalService.reader = DataService.GetInstance().ExecuteReader(query);
while (GlobalService.reader.Read())
{
string status = GlobalService.reader.GetString(0);
string chaseNo = GlobalService.reader.GetString(1);
string itemcode = GlobalService.reader.GetString(2);
string rev = GlobalService.reader.GetString(3);
string mouldno = GlobalService.reader.GetString(4);
string div = GlobalService.reader.GetString(5);
string type = GlobalService.reader.GetString(6);
string pcs = GlobalService.reader.GetString(7);
string currency = GlobalService.reader.GetString(8);
string amount = GlobalService.reader.GetString(9);
string amounthkd = GlobalService.reader.GetString(10);
string mpa = GlobalService.reader.GetString(11);
string fa = GlobalService.reader.GetString(12);
string tmpfa = GlobalService.reader.GetString(13);
string qty = GlobalService.reader.GetString(14);
string ringi = GlobalService.reader.GetString(15);
string model = GlobalService.reader.GetString(16);
string po = GlobalService.reader.GetString(17);
string poissued = GlobalService.reader.GetString(18);
string category = GlobalService.reader.GetString(19);
string itemtext = GlobalService.reader.GetString(20);
string projecttext = GlobalService.reader.GetString(21);
string vendor = GlobalService.reader.GetString(22);
string vendorname = GlobalService.reader.GetString(23);
string vgroup = GlobalService.reader.GetString(24);
string mouldcode = GlobalService.reader.GetString(25);
string remarks = GlobalService.reader.GetString(26);
string instock50 = GlobalService.reader.GetString(27);
string instock = GlobalService.reader.GetString(28);
string checkdate = GlobalService.reader.GetString(29);
string cav = GlobalService.reader.GetString(30);
string weight = GlobalService.reader.GetString(31);
string equipment = GlobalService.reader.GetString(32);
string shot = GlobalService.reader.GetString(33);
string vertical = GlobalService.reader.GetString(34);
string horizontal = GlobalService.reader.GetString(35);
string height = GlobalService.reader.GetString(36);
string instockremarks = GlobalService.reader.GetString(37);
string collectdate = GlobalService.reader.GetString(38);
string passremarks = GlobalService.reader.GetString(39);
string oemasset = GlobalService.reader.GetString(40);
string accountcode = GlobalService.reader.GetString(41);
string costcentre = GlobalService.reader.GetString(42);
string createdby = GlobalService.reader.GetString(43);
resulttable.Rows.Add(new object[] { status, chaseNo, itemcode, rev, mouldno, div, type,
pcs, currency, amount, amounthkd, mpa, fa, tmpfa, qty, ringi, model, po, poissued,
category, itemtext, projecttext, vendor, vendorname, vgroup, mouldcode, remarks,
instock50, instock, checkdate, cav, weight, equipment, shot, vertical, horizontal,
height, instockremarks, collectdate, passremarks, oemasset, accountcode, costcentre,
createdby });
}
GlobalService.reader.Close();
}
ExportCsvUtil.ExportCsv(resulttable, "", "DLRECORD");
}
}
}
private void bgworker_DoWork(object sender, DoWorkEventArgs e)
{
try
{
DataTable tmptable = new DataTable();
tmptable = ImportXlsUtil.TranslateToTable(filename);
int errorcount = 1;
List <int> indexlist = new List <int>();
foreach (DataRow row in tmptable.Rows)
{
errorcount += 1;
string chaseno = row.ItemArray[2].ToString();
Debug.WriteLine("CHASE NO" + chaseno + "ROW: " + errorcount);
string status = DataChecking.getStatusByChaseno(chaseno);
Debug.WriteLine("STATUS: " + status);
string p2003no = row.ItemArray[12].ToString();
string p2003ans = row.ItemArray[13].ToString();
string p2003result = row.ItemArray[14].ToString();
string p2003updated = row.ItemArray[15].ToString();
string p2004no = row.ItemArray[16].ToString();
string p2004ans = row.ItemArray[17].ToString();
string p2004result = row.ItemArray[18].ToString();
string p2004updated = row.ItemArray[19].ToString();
string kdcno = row.ItemArray[20].ToString();
string kdcissued = row.ItemArray[21].ToString();
string kdcrps = row.ItemArray[22].ToString();
string kdcseisan = row.ItemArray[23].ToString();
string kdcresult = row.ItemArray[24].ToString();
string kdcupdated = row.ItemArray[25].ToString();
string assetdesc = row.ItemArray[26].ToString();
string capdate = row.ItemArray[27].ToString();
string acquishkd = row.ItemArray[28].ToString();
string accumhkd = row.ItemArray[29].ToString();
string closingmonth = row.ItemArray[30].ToString();
string bookhkd1 = row.ItemArray[31].ToString();
string fy = row.ItemArray[32].ToString();
string bookhkd2 = row.ItemArray[33].ToString();
//string nav = row.ItemArray[33].ToString();
string disposalringi = row.ItemArray[34].ToString();
string disposalreportno = row.ItemArray[35].ToString();
string disposalreportissued = row.ItemArray[36].ToString();
string disposalreportrecieved = row.ItemArray[37].ToString();
string vendorresult = row.ItemArray[38].ToString();
string fadisposal = row.ItemArray[39].ToString();
string disposalremarks = row.ItemArray[40].ToString();
string query = "";
string disposalText = "";
if (status == "D1")//待廃棄 - 調整依賴中
{
if ((p2003result == "OK" || p2003result == "可") && (p2004result == "OK" || p2004result == "可"))
{
if (check2003IsValid(p2003no, p2003ans, p2003updated) && check2004IsValid(p2004no, p2004ans, p2004updated))
{
p2003updated = ImportXlsUtil.ParseDateTime(p2003updated).ToString("yyyy/MM/dd");
p2004updated = ImportXlsUtil.ParseDateTime(p2004updated).ToString("yyyy/MM/dd");
query = string.Format("update tb_betamould set tm_st_code = 'D2' where tm_chaseno = '{0}'", chaseno);
disposalText = string.Format("update tb_disposaldetail set dd_2003no = '{0}', dd_2003answer = '{1}', dd_2003result = '{2}'" +
", dd_2003updated = '{3}', dd_2004no = '{4}', dd_2004answer = '{5}', dd_2004result = '{6}', dd_2004updated = '{7}', dd_disposalremarks = '{8}' where" +
" dd_chaseno = '{9}'", p2003no, p2003ans, p2003result, p2003updated, p2004no, p2004ans, p2004result, p2004updated, disposalremarks,
chaseno);
}
else
{
indexlist.Add(errorcount);
}
}
if (p2003result == "不可" || p2004result == "不可")
{
query = RollBackQuery(chaseno);
disposalText = DeleteQuery(chaseno);
}
}
else if (status == "D2")//待廃棄 - 日本生産本部確認中
{
if (kdcresult == "OK" || kdcresult == "可")
{
if (checkKdcIsValid(kdcno, kdcrps, kdcseisan, kdcupdated))
{
kdcupdated = ImportXlsUtil.ParseDateTime(kdcupdated).ToString("yyyy/MM/dd");
string fac = DataChecking.isFixedAssetExists(chaseno) ? DataChecking.getFixedAssetCode(chaseno) : "";
if (fac != "")
{
query = string.Format("update tb_betamould set tm_st_code = 'D4' where tm_chaseno = '{0}'", chaseno);
}
else
{
query = string.Format("update tb_betamould set tm_st_code = 'D3' where tm_chaseno = '{0}'", chaseno);
}
/*disposalText = string.Format("update tb_disposaldetail set dd_kdcno = '{0}', dd_kdcrps = '{1}', dd_kdcseisan = '{2}'" +
* ", dd_kdcupdated = '{3}', dd_kdcresult = '{4}', dd_kdcissued = '{6}', dd_disposalremarks = '{7}' where dd_chaseno = '{5}'", kdcno, kdcrps, kdcseisan, kdcupdated, kdcresult, chaseno, kdcissued, disposalremarks);*/
disposalText = string.Format("update tb_disposaldetail set dd_kdcrps = '{0}', dd_kdcseisan = '{1}'" +
", dd_kdcupdated = '{2}', dd_kdcresult = '{3}', dd_kdcissued = '{5}', dd_disposalremarks = '{6}' where dd_chaseno = '{4}'", kdcrps, kdcseisan, kdcupdated, kdcresult, chaseno, kdcissued, disposalremarks);
}
else
{
indexlist.Add(errorcount);
}
}
if (kdcresult == "不可")
{
query = RollBackQuery(chaseno);
disposalText = DeleteQuery(chaseno);
}
}
else if (status == "D3")//待廃棄 - 日本生産本部確認完了
{
disposalreportissued = ImportXlsUtil.ParseDateTime(disposalreportissued).ToString("yyyy/MM/dd");
//disposalreportrecieved = ImportXlsUtil.ParseDateTime(disposalreportrecieved).ToString("yyyy/MM/dd");
if (checkDisposalReportValid(disposalreportno, disposalreportissued))
{
query = string.Format("update tb_betamould set tm_st_code = 'D6' where tm_chaseno = '{0}'", chaseno);
disposalText = string.Format("update tb_disposaldetail set dd_reportno = '{0}', dd_reportissued = '{1}', dd_disposalremarks = '{3}'" +
" where dd_chaseno = '{2}'", disposalreportno, disposalreportissued, chaseno, disposalremarks);
}
else
{
indexlist.Add(errorcount);
}
}
else if (status == "D4")//待廃棄 - 固定資産確認
{
if (checkFaValid(assetdesc, capdate, acquishkd, accumhkd, closingmonth, bookhkd1, fy, bookhkd2))
{
capdate = ImportXlsUtil.ParseDateTime(capdate).ToString("yyyy/MM/dd");
closingmonth = ImportXlsUtil.ParseDateTime(closingmonth).ToString("yyyy/MM/dd");
fy = ImportXlsUtil.ParseDateTime(fy).ToString("yyyy/MM/dd");
if (bookhkd1 != "0" && bookhkd1 != "")
{
query = string.Format("update tb_betamould set tm_st_code = 'D5' where tm_chaseno = '{0}'", chaseno);
disposalText = string.Format("update tb_disposaldetail set dd_assetdesc = '{0}', dd_capdate = '{1}', dd_acquishkd = '{2}'" +
", dd_accumhkd = '{3}', dd_closing = '{4}', dd_bookhkd = '{5}', dd_fy = '{6}', dd_bookhkd2 = '{7}', dd_disposalremarks = '{9}' where dd_chaseno = '{8}'",
assetdesc, capdate, acquishkd, accumhkd, closingmonth, bookhkd1, fy, bookhkd2, chaseno, disposalremarks);
}
else
{
if (checkDisposalReportValid(disposalreportno, disposalreportissued))
{
disposalreportissued = ImportXlsUtil.ParseDateTime(disposalreportissued).ToString("yyyy/MM/dd");
//disposalreportrecieved = ImportXlsUtil.ParseDateTime(disposalreportrecieved).ToString("yyyy/MM/dd");
query = string.Format("update tb_betamould set tm_st_code = 'D6' where tm_chaseno = '{0}'", chaseno);
disposalText = string.Format("update tb_disposaldetail set dd_reportno = '{0}', dd_reportissued = '{1}', dd_disposalremarks = '{3}'" +
" where dd_chaseno = '{2}'", disposalreportno, disposalreportissued,
chaseno, disposalremarks);
}
else
{
indexlist.Add(errorcount);
}
}
}
else
{
indexlist.Add(errorcount);
}
}
else if (status == "D5")//待廃棄 - 廃棄稟議書申請
{
if (disposalringi != "")
{
if (checkRingiValid(disposalringi))
{
//disposalreportrecieved = ImportXlsUtil.ParseDateTime(disposalreportrecieved).ToString("yyyy/MM/dd");
//query = string.Format("update tb_betamould set tm_st_code = 'D6' where tm_chaseno = '{0}'", chaseno);
disposalText = string.Format("update tb_disposaldetail set dd_disposalringi = '{0}' where dd_chaseno = '{1}'", disposalringi, chaseno);
Debug.WriteLine("Disposal Text: " + disposalText);
if (checkDisposalReportValid(disposalreportno, disposalreportissued))
{
query = string.Format("update tb_betamould set tm_st_code = 'D6' where tm_chaseno = '{0}'", chaseno);
disposalText = string.Format("update tb_disposaldetail set dd_reportno = '{0}', dd_reportissued = '{1}'" +
", dd_disposalringi = '{2}', dd_disposalremarks = '{4}' where dd_chaseno = '{3}'", disposalreportno, disposalreportissued,
disposalringi, chaseno, disposalremarks);
}
}
else
{
indexlist.Add(errorcount);
}
}
else
{
query = RollBackQuery(chaseno);
disposalText = DeleteQuery(chaseno);
}
}
else if (status == "D6")//待廃棄 - 廃棄報告書発行済
{
if (vendorresult == "OK" || vendorresult == "可")
{
disposalreportissued = ImportXlsUtil.ParseDateTime(disposalreportissued).ToString("yyyy/MM/dd");
disposalreportrecieved = ImportXlsUtil.ParseDateTime(disposalreportrecieved).ToString("yyyy/MM/dd");
query = string.Format("update tb_betamould set tm_st_code = 'D7' where tm_chaseno = '{0}'", chaseno);
disposalText = string.Format("update tb_disposaldetail set dd_reportreceived = '{0}', dd_vendorresult = '{1}', dd_disposalremarks = '{3}' where dd_chaseno = '{2}'", disposalreportrecieved, vendorresult, chaseno, disposalremarks);
}
else
{
query = RollBackQuery(chaseno);
disposalText = DeleteQuery(chaseno);
}
}
else if (status == "D7")//待廃棄 - 廃棄報告書回収済
{
string fac = DataChecking.isFixedAssetExists(chaseno) ? DataChecking.getFixedAssetCode(chaseno) : "";
if (fac != "")
{
if (checkFaDisposalValid(fadisposal))
{
fadisposal = ImportXlsUtil.ParseDateTime(fadisposal).ToString("yyyy/MM/dd");
query = string.Format("update tb_betamould set tm_st_code = 'D8' where tm_chaseno = '{0}'", chaseno);
disposalText = string.Format("update tb_disposaldetail set dd_fadisposaldate = '{0}', dd_disposalremarks = '{1}'" +
" where dd_chaseno = '{2}'", fadisposal, disposalremarks, chaseno);
}
else
{
indexlist.Add(errorcount);
}
}
}
else if (status == "D8")//待廃棄 - 固定資産廃棄申請済
{
}
else
{
indexlist.Add(errorcount);
}
if (query != "")
{
DataService.GetInstance().ExecuteNonQuery(query);
}
if (disposalText != "")
{
DataService.GetInstance().ExecuteNonQuery(disposalText);
}
}
if (indexlist.Count != 0)
{
StringBuilder builder = new StringBuilder();
builder.AppendLine("Following rows contain invalid data");
for (int i = 0; i < indexlist.Count; i++)
{
int rowindex = indexlist[i];
builder.Append("Row: " + rowindex).AppendLine();
}
MessageBox.Show(builder + "");
}
else
{
MessageBox.Show("Record has been saved");
}
//this.loadData();
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message + ex.StackTrace);
MessageBox.Show("Invalid data format in upload file");
}
}
private void worker_DoWork(object sender, DoWorkEventArgs e)
{
try
{
foreach (DataRow row in ulTable.Rows)
{
string date = row.ItemArray[0].ToString();
string incharge = row.ItemArray[1].ToString();
string asset = row.ItemArray[2].ToString();
string partno = row.ItemArray[3].ToString();
string rev = row.ItemArray[4].ToString();
string mouldno = row.ItemArray[5].ToString();
string type = row.ItemArray[6].ToString();
string mouldcode = row.ItemArray[7].ToString();
string currency = row.ItemArray[8].ToString();
string amount = row.ItemArray[9].ToString();
string vendorcode = row.ItemArray[10].ToString();
string vendorname = row.ItemArray[11].ToString();
string chaseno = row.ItemArray[12].ToString();
string remarks = row.ItemArray[13].ToString();
string po = row.ItemArray[14].ToString();
string poissued = row.ItemArray[15].ToString();
string poinstock = row.ItemArray[16].ToString();
string debit = row.ItemArray[17].ToString();
string sales = row.ItemArray[18].ToString();
string ringi = row.ItemArray[19].ToString();
string finish = row.ItemArray[20].ToString();
string epsonno = row.ItemArray[21].ToString();
if (rev.Length == 1)
{
rev = "0" + rev;
}
date = ImportXlsUtil.ParseDateTime(date).ToString("yyyy/MM/dd");
poissued = ImportXlsUtil.ParseDateTime(poissued).ToString("yyyy/MM/dd");
poinstock = ImportXlsUtil.ParseDateTime(poinstock).ToString("yyyy/MM/dd");
debit = ImportXlsUtil.ParseDateTime(debit).ToString("yyyy/MM/dd");
string query = string.Format("if exists (select * from tb_expensetransfer where et_partno = '{0}' and et_rev = '{1}' and et_mouldno = '{2}') update tb_expensetransfer set et_date = '{3}', et_incharge = '{4}'" +
", et_asset = '{5}', et_type = '{6}', et_mouldcode = '{7}', et_cur = '{8}', et_amount = '{9}', et_vendorcode = '{10}', et_vendor = '{11}'" +
", et_chaseno = '{12}', et_remarks = '{13}', et_pono = '{14}', et_poissued = '{15}', et_poinstock = '{16}', et_debit = '{17}', et_fasales = '{18}', et_ringino = '{19}', et_finish = '{20}'" +
", et_epsonno = '{21}' else insert into tb_expensetransfer (et_date, et_incharge, et_asset, et_partno, et_rev, et_mouldno, et_type, et_mouldcode, et_cur, et_amount" +
", et_vendorcode, et_vendor, et_chaseno, et_remarks, et_pono, et_poissued, et_poinstock, et_debit, et_fasales, et_ringino, et_finish, et_epsonno) values" +
" ('{3}', '{4}', '{5}', '{0}', '{1}', '{2}', '{6}', '{7}', '{8}', '{9}', '{10}', '{11}', '{12}', '{13}', '{14}', '{15}', '{16}', '{17}', '{18}', '{19}', '{20}', '{21}')", partno, rev, mouldno, date, incharge, asset,
type, mouldcode, currency, amount, vendorcode, vendorname, chaseno, remarks, po, poissued, poinstock, debit, sales, ringi, finish, epsonno);
/*string query = string.Format("if exists (select * from tb_expensetransfer where et_ = '{0}') update tb_expensetransfer set et_date = '{1}', et_incharge = '{2}'" +
* ", et_asset = '{3}', et_mouldno = '{4}', et_type = '{5}', et_mouldcode = '{6}', et_currency = '{7}', et_amount = '{8}', et_vendorcode = '{9}', et_vendorname = '{10}'" +
* ", et_chaseno = '{0}', et_remarks = '{11}', et_po = '{12}', et_poissued = '{13}', et_poinstock = '{14}', et_debit = '{15}', et_sales = '{16}', et_ringi = '{17}', et_finish = '{18}'" +
* ", et_epsonno = '{19}' else insert into tb_expensetransfer (et_date, et_incharge, et_asset, et_mouldno, et_type, et_mouldcode, et_currency, et_amount" +
* ", et_vendorcode, et_vendorname, et_chaseno, et_remarks, et_po, et_poissued, et_poinstock, et_debit, et_sales, et_ringi, et_finish, et_epsonno) values" +
* " ('{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', '{10}', '{0}', '{11}', '{12}', '{13}', '{14}', '{15}', '{16}', '{17}', '{18}', '{19}')", chaseno, date, incharge, asset,
* mouldno, type, mouldcode, currency, amount, vendorcode, vendorname, chaseno, remarks, po, poissued, poinstock, debit, sales, ringi, finish, epsonno);*/
DataService.GetInstance().ExecuteNonQuery(query);
}
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message + ex.StackTrace);
validData = false;
}
}
private void btnUploadTM_Click(object sender, EventArgs e)
{
OpenFileDialog ofd = new OpenFileDialog()
{
Filter = "Excel Files |*.xls;*.xlsx"
};
if (ofd.ShowDialog() == DialogResult.OK)
{
filename = ofd.FileName;
uploadTable = new DataTable();
uploadTable = ofd.FileName.EndsWith(".xls") ? ImportXlsUtil.TranslateToTable(ofd.FileName) : ImportXls2007Util.TranslateToTable(ofd.FileName);
DataTable tmptable = new DataTable();
tmptable.Columns.Add("Chase No.");
tmptable.Columns.Add("Check Date");
tmptable.Columns.Add("In Stock Remarks");
tmptable.Columns.Add("In Stock Date");
foreach (DataRow row in uploadTable.Rows)
{
string chaseno = row.ItemArray[0].ToString();
if (chaseno.StartsWith("CM"))
{
string instockdate = ImportXls2007Util.ParseDateTime(row.ItemArray[3].ToString()).ToString("yyyy/MM/dd");
tmptable.Rows.Add(new object[] { chaseno, "", "", instockdate });
}
else
{
string checkdate = ImportXlsUtil.ParseDateTime(row.ItemArray[1].ToString()).ToString("yyyy/MM/dd");
if (checkdate == "0001/01/01")
{
checkdate = "";
}
string instockremarks = row.ItemArray[2].ToString();
string instockdate = ImportXlsUtil.ParseDateTime(row.ItemArray[3].ToString()).ToString("yyyy/MM/dd");
tmptable.Rows.Add(new object[] { chaseno, checkdate, instockremarks, instockdate });
}
}
frmUploadConfirm form = new frmUploadConfirm(tmptable);
form.ShowDialog();
if (form.DialogResult == DialogResult.OK)
{
if (!workerTM.IsBusy)
{
this.Cursor = Cursors.WaitCursor;
dgvPreview.Cursor = Cursors.WaitCursor;
dgvResult.Cursor = Cursors.WaitCursor;
workerTM.RunWorkerAsync();
}
else
{
MessageBox.Show("Using by other process");
}
}
}
}