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");
}
}
}
}
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 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 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 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 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");
}
}
}
}
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 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");
}
}
}
}