private void tsbtnUpload_Click(object sender, EventArgs e)
{
OpenFileDialog ofd = new OpenFileDialog();
ofd.Filter = "Excel 97-2003 Workbook|*.xls|Excel Workbook|*.xlsx|" +
"All Excel Types|*.xls;*.xlsx";
if (ofd.ShowDialog() == DialogResult.OK)
{
DataTable table = ofd.FileName.EndsWith(".xls") ? ImportExcel2003.TranslateToTable(ofd.FileName) : ImportExcel2007.TranslateToTable(ofd.FileName);
foreach (DataRow row in table.Rows)
{
string code = row.ItemArray[0].ToString();
string content = row.ItemArray[1].ToString();
string accountCode = row.ItemArray[2].ToString();
string costCentre = row.ItemArray[3].ToString();
string remarks = row.ItemArray[4].ToString();
string query = string.Format("if not exists (select * from TB_MASTER_OEM where mo_code = '{0}')" +
" insert into TB_MASTER_OEM (mo_code, mo_content, mo_accountcode, mo_costcentre, mo_remarks)" +
" values ('{0}', N'{1}', '{2}', '{3}', N'{4}') else update TB_MASTER_OEM set mo_content = N'{1}', mo_accountcode = '{2}'" +
", mo_costcentre = '{3}', mo_remarks = N'{4}' where mo_code = '{0}'", code, content, accountCode, costCentre, remarks);
DataService.GetInstance().ExecuteNonQuery(query);
}
}
this.LoadData(txtSearch.Text);
}
private void tsbtnUpload_Click(object sender, EventArgs e)
{
OpenFileDialog ofd = new OpenFileDialog();
ofd.Filter = "Excel 97-2003 Workbook|*.xls|Excel Workbook|*.xlsx|" +
"All Excel Types|*.xls;*.xlsx";
if (ofd.ShowDialog() == DialogResult.OK)
{
DataTable table = ofd.FileName.EndsWith(".xls") ? ImportExcel2003.TranslateToTable(ofd.FileName) : ImportExcel2007.TranslateToTable(ofd.FileName);
foreach (DataRow row in table.Rows)
{
string chaseNo = row.ItemArray[0].ToString();
string requestNo = row.ItemArray[6].ToString();
string po = row.ItemArray[7].ToString();
if (po != "")
{
string today = DateTime.Today.ToString("yyyy/MM/dd");
string query = string.Format("update TB_MOULD_MAIN set mm_requestno = '{0}', mm_po = '{1}', mm_poissued = '{2}', mm_status_code = 'P' where mm_chaseno = '{3}'", requestNo, po, today, chaseNo);
DataService.GetInstance().ExecuteNonQuery(query);
}
}
MessageBox.Show("Record has been saved.");
this.LoadData(txtSearch.Text);
}
}
private void btnUpload_Click(object sender, EventArgs e)
{
OpenFileDialog ofd = new OpenFileDialog();
if (ofd.ShowDialog() == DialogResult.OK)
{
DataTable table = ofd.FileName.EndsWith(".xls") ? ImportExcel2003.TranslateToTable(ofd.FileName) : ImportExcel2007.TranslateToTable(ofd.FileName);
string text = "delete from tb_mould_cost dbcc checkident ('tb_mould_cost', reseed, 0)";
DataService.GetInstance().ExecuteNonQuery(text);
foreach (DataRow row in table.Rows)
{
string itemcode = row.ItemArray[0].ToString().Trim();
string rev = row.ItemArray[1].ToString().Trim();
if (rev.Length == 1)
{
rev = "0" + rev;
}
string currency = row.ItemArray[2].ToString().Trim();
string cost = Math.Round(Convert.ToDecimal(row.ItemArray[3]), 0).ToString();
string remarks = row.ItemArray[4].ToString().Trim();
string query = string.Format("insert into tb_mould_cost (c_itemcode, c_rev, c_currency, c_cost, c_remarks) values ('{0}', '{1}', '{2}', '{3}', N'{4}')", itemcode, rev, currency, cost, remarks);
DataService.GetInstance().ExecuteNonQuery(query);
}
MessageBox.Show("Record has been saved.");
}
LoadData("");
}
private void tsbtnUpload_Click(object sender, EventArgs e)
{
OpenFileDialog ofd = new OpenFileDialog();
ofd.Filter = "Excel 97-2003 Workbook|*.xls|Excel Workbook|*.xlsx|" +
"All Excel Types|*.xls;*.xlsx";
if (ofd.ShowDialog() == DialogResult.OK)
{
DataTable table = ofd.FileName.EndsWith(".xls") ? ImportExcel2003.TranslateToTable(ofd.FileName) : ImportExcel2007.TranslateToTable(ofd.FileName);
foreach (DataRow row in table.Rows)
{
string vendor = row.ItemArray[0].ToString();
string name = row.ItemArray[1].ToString();
string pGroup = row.ItemArray[2].ToString();
string currency = row.ItemArray[3].ToString();
string payTerms = row.ItemArray[4].ToString();
string region = row.ItemArray[5].ToString();
string request = row.ItemArray[6].ToString();
string edi = row.ItemArray[7].ToString();
string remarks = row.ItemArray[8].ToString();
string query = string.Format("if not exists (select * from TB_MASTER_VENDOR where mv_code = '{0}')" +
" insert into TB_MASTER_VENDOR (mv_code, mv_name, mv_group, mv_detail, mv_paycurr" +
", mv_payterms, mv_request, mv_edi, mv_remarks) values ('{0}', N'{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', N'{8}')" +
" else update TB_MASTER_VENDOR set mv_name = N'{1}', mv_group = '{2}', mv_detail = '{3}'" +
", mv_paycurr = '{4}', mv_payterms = '{5}', mv_request = '{6}', mv_edi = '{7}', mv_remarks = N'{8}'" +
" where mv_code = '{0}'", vendor, name, pGroup, region, currency, payTerms, request, edi, remarks);
DataService.GetInstance().ExecuteNonQuery(query);
}
this.LoadData(txtSearch.Text);
}
}
private void tsbtnUpload_Click(object sender, EventArgs e)
{
OpenFileDialog ofd = new OpenFileDialog();
ofd.Filter = "Excel 97-2003 Workbook|*.xls|Excel Workbook|*.xlsx|" +
"All Excel Types|*.xls;*.xlsx";
if (ofd.ShowDialog() == DialogResult.OK)
{
DataTable table = ofd.FileName.EndsWith(".xls") ? ImportExcel2003.TranslateToTable(ofd.FileName) : ImportExcel2007.TranslateToTable(ofd.FileName);
foreach (DataRow row in table.Rows)
{
string mouldCode = row.ItemArray[0].ToString();
string type = row.ItemArray[1].ToString();
string jp = row.ItemArray[2].ToString();
string eng = row.ItemArray[3].ToString();
string chin = row.ItemArray[4].ToString();
string itemGroup = row.ItemArray[5].ToString();
string query = string.Format("if not exists (select * from TB_MASTER_MOULDCODE where mc_code = '{0}')" +
" insert into TB_MASTER_MOULDCODE (mc_code, mc_type, mc_contentjp, mc_contenteng, mc_contentchin, mc_itemgroup)" +
" values ('{0}', N'{1}', N'{2}', N'{3}', N'{4}', '{5}') else update TB_MASTER_MOULDCODE set mc_type = N'{1}'" +
", mc_contentjp = N'{2}', mc_contenteng = N'{3}', mc_contentchin = N'{4}', mc_itemgroup = '{5}' where mc_code = '{0}'",
mouldCode, type, jp, eng, chin, itemGroup);
DataService.GetInstance().ExecuteNonQuery(query);
}
this.LoadData(txtSearch.Text);
}
}
private void tsbtnUpload_Click(object sender, EventArgs e)
{
OpenFileDialog ofd = new OpenFileDialog();
ofd.Filter = "Excel 97-2003 Workbook|*.xls|Excel Workbook|*.xlsx|" +
"All Excel Types|*.xls;*.xlsx";
if (ofd.ShowDialog() == DialogResult.OK)
{
DataTable table = ofd.FileName.EndsWith(".xls") ? ImportExcel2003.TranslateToTable(ofd.FileName) : ImportExcel2007.TranslateToTable(ofd.FileName);
foreach (DataRow row in table.Rows)
{
}
}
}
private void btnUpload_Click(object sender, EventArgs e)
{
OpenFileDialog ofd = new OpenFileDialog();
if (ofd.ShowDialog() == DialogResult.OK)
{
DataTable table = ofd.FileName.EndsWith("xlsx") ? ImportExcel2007.TranslateToTable(ofd.FileName) : ImportExcel2003.TranslateToTable(ofd.FileName);
foreach (DataRow row in table.Rows)
{
string type = row.ItemArray[0].ToString().Trim();
string mould = row.ItemArray[1].ToString().Trim();
string partno = row.ItemArray[2].ToString().Trim();
string rev = row.ItemArray[3].ToString().Trim();
string asset = row.ItemArray[4].ToString().Trim();
string vendor = row.ItemArray[5].ToString().Trim();
if (vendor.Length == 9)
{
vendor = "0" + vendor;
}
string vendorname = row.ItemArray[6].ToString().Trim();
if (vendorname.Contains("'"))
{
vendorname = vendorname.Replace("'", "''");
}
string p2003no = row.ItemArray[7].ToString().Trim();
if (p2003no.Contains("'"))
{
p2003no = p2003no.Replace("'", "''");
}
string p2003ans = row.ItemArray[8].ToString().Trim();
try
{
p2003ans = ImportExcel2007.ParseDateTime(p2003ans).ToString("yyyy/MM/dd");
if (p2003ans.StartsWith("00"))
{
p2003ans = row.ItemArray[8].ToString().Trim();
}
}
catch
{
}
if (p2003ans.Contains("'"))
{
p2003ans = p2003ans.Replace("'", "''");
}
string p2003result = row.ItemArray[9].ToString().Trim();
try
{
p2003result = ImportExcel2007.ParseDateTime(p2003result).ToString("yyyy/MM/dd");
if (p2003result.StartsWith("00"))
{
p2003result = row.ItemArray[9].ToString().Trim();
}
}
catch
{
}
if (p2003result.Contains("'"))
{
p2003result = p2003result.Replace("'", "''");
}
string p2003date = row.ItemArray[10].ToString().Trim();
try
{
p2003date = ImportExcel2007.ParseDateTime(p2003date).ToString("yyyy/MM/dd");
if (p2003date.StartsWith("00"))
{
p2003date = row.ItemArray[10].ToString().Trim();
}
}
catch
{
}
string p2004no = row.ItemArray[11].ToString().Trim();
if (p2004no.Contains("'"))
{
p2004no = p2004no.Replace("'", "''");
}
string p2004ans = row.ItemArray[12].ToString().Trim();
try
{
p2004ans = ImportExcel2007.ParseDateTime(p2004ans).ToString("yyyy/MM/dd");
if (p2004ans.StartsWith("00"))
{
p2004ans = row.ItemArray[12].ToString().Trim();
}
}
catch
{
}
if (p2004ans.Contains("'"))
{
p2004ans = p2004ans.Replace("'", "''");
}
string p2004result = row.ItemArray[13].ToString().Trim();
try
{
p2004result = ImportExcel2007.ParseDateTime(p2004result).ToString("yyyy/MM/dd");
if (p2004result.StartsWith("00"))
{
p2004result = row.ItemArray[13].ToString().Trim();
}
}
catch
{
}
if (p2004result.Contains("'"))
{
p2004result = p2004result.Replace("'", "''");
}
string p2004date = row.ItemArray[14].ToString().Trim();
try
{
p2004date = ImportExcel2007.ParseDateTime(p2004date).ToString("yyyy/MM/dd");
if (p2004date.StartsWith("00"))
{
p2004date = row.ItemArray[14].ToString().Trim();
}
}
catch
{
}
string kdcno = row.ItemArray[15].ToString().Trim();
if (kdcno.Contains("'"))
{
kdcno = kdcno.Replace("'", "''");
}
string kdcdate = row.ItemArray[16].ToString().Trim();
try
{
kdcdate = ImportExcel2007.ParseDateTime(kdcdate).ToString("yyyy/MM/dd");
if (kdcdate.StartsWith("00"))
{
kdcdate = row.ItemArray[16].ToString().Trim();
}
}
catch
{
}
string kdcrps = row.ItemArray[17].ToString().Trim();
try
{
kdcrps = ImportExcel2007.ParseDateTime(kdcrps).ToString("yyyy/MM/dd");
if (kdcrps.StartsWith("00"))
{
kdcrps = row.ItemArray[17].ToString().Trim();
}
}
catch
{
}
if (kdcrps.Contains("'"))
{
kdcrps = kdcrps.Replace("'", "''");
}
string kdcans = row.ItemArray[18].ToString().Trim();
try
{
kdcans = ImportExcel2007.ParseDateTime(kdcans).ToString("yyyy/MM/dd");
if (kdcans.StartsWith("00"))
{
kdcans = row.ItemArray[18].ToString().Trim();
}
}
catch
{
}
if (kdcans.Contains("'"))
{
kdcans = kdcans.Replace("'", "''");
}
string kdcresult = row.ItemArray[19].ToString().Trim();
try
{
kdcresult = ImportExcel2007.ParseDateTime(kdcresult).ToString("yyyy/MM/dd");
if (kdcresult.StartsWith("00"))
{
kdcresult = row.ItemArray[19].ToString().Trim();
}
}
catch
{
}
if (kdcresult.Contains("'"))
{
kdcresult.Replace("'", "''");
}
string kdcupdate = row.ItemArray[20].ToString().Trim();
try
{
kdcupdate = ImportExcel2007.ParseDateTime(kdcupdate).ToString("yyyy/MM/dd");
if (kdcupdate.StartsWith("00"))
{
kdcupdate = row.ItemArray[20].ToString().Trim();
}
}
catch
{
}
string assetdesc = row.ItemArray[21].ToString().Trim();
if (assetdesc.Contains("'"))
{
assetdesc = assetdesc.Replace("'", "''");
}
string capdate = row.ItemArray[22].ToString().Trim();
try
{
capdate = ImportExcel2007.ParseDateTime(capdate).ToString("yyyy/MM/dd");
if (capdate.StartsWith("00"))
{
capdate = row.ItemArray[22].ToString().Trim();
}
}
catch
{
}
string acquis = row.ItemArray[23].ToString().Trim();
string accum = row.ItemArray[24].ToString().Trim();
string closing = row.ItemArray[25].ToString().Trim();
try
{
closing = ImportExcel2007.ParseDateTime(closing).ToString("yyyy/MM/dd");
if (closing.StartsWith("00"))
{
closing = row.ItemArray[25].ToString().Trim();
}
}
catch
{
}
string bookval = row.ItemArray[26].ToString().Trim();
string year = row.ItemArray[27].ToString().Trim();
try
{
year = ImportExcel2007.ParseDateTime(year).ToString("yyyy/MM/dd");
if (year.StartsWith("00"))
{
year = row.ItemArray[27].ToString().Trim();
}
}
catch
{
}
string bookval2 = row.ItemArray[28].ToString().Trim();
string ringi = row.ItemArray[29].ToString().Trim();
string reportno = row.ItemArray[30].ToString().Trim();
if (reportno.Contains("''"))
{
reportno = reportno.Replace("'", "''");
}
string reportissued = row.ItemArray[31].ToString().Trim();
try
{
reportissued = ImportExcel2007.ParseDateTime(reportissued).ToString("yyyy/MM/dd");
if (reportissued.StartsWith("00"))
{
reportissued = row.ItemArray[31].ToString().Trim();
}
}
catch
{
}
string reportdate = row.ItemArray[32].ToString().Trim();
try
{
reportdate = ImportExcel2007.ParseDateTime(reportdate).ToString("yyyy/MM/dd");
if (reportdate.StartsWith("00"))
{
reportdate = row.ItemArray[32].ToString().Trim();
}
}
catch
{
}
string vendorans = row.ItemArray[33].ToString().Trim();
string assetdisposal = row.ItemArray[34].ToString().Trim();
try
{
assetdisposal = ImportExcel2007.ParseDateTime(assetdisposal).ToString("yyyy/MM/dd");
if (assetdisposal.StartsWith("00"))
{
assetdisposal = row.ItemArray[34].ToString().Trim();
}
}
catch
{
}
string remarks = row.ItemArray[35].ToString().Trim();
string status = row.ItemArray[36].ToString().Trim();
if (closing.Contains("'"))
{
closing = closing.Replace("'", "''");
}
if (year.Contains("'"))
{
year = year.Replace("'", "''");
}
string query = string.Format("if not exists (select * from tb_disposaldetail where dd_mould = '{2}' and dd_partno = '{3}' and dd_asset = '{5}')" +
" insert into tb_disposaldetail (dd_type, dd_status, dd_mould, dd_partno, dd_rev, dd_asset, dd_vendor, dd_vendorname" +
", dd_2003no, dd_2003answer, dd_2003result, dd_2003updated, dd_2004no, dd_2004answer, dd_2004result, dd_2004updated" +
", dd_kdcno, dd_kdcissued, dd_kdcrps, dd_kdcanswer, dd_kdcresult, dd_kdcupdated, dd_assetdesc, dd_capdate, dd_acquishkd" +
", dd_accumhkd, dd_closing, dd_bookhkd, dd_fy, dd_bookhkd2, dd_disposalringi, dd_reportno, dd_reportissued, dd_reportreceived" +
", dd_vendorresult, dd_fadisposaldate, dd_disposalremarks) values (N'{0}', N'{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', '{10}'" +
", '{11}', '{12}', '{13}', '{14}', '{15}', '{16}', '{17}', '{18}', '{19}', '{20}', '{21}', '{22}', '{23}', '{24}', '{25}', '{26}', '{27}', '{28}', '{29}', '{30}'" +
", N'{31}', '{32}', '{33}', '{34}', '{35}', N'{36}') else update tb_disposaldetail set dd_type = N'{0}', dd_rev = '{4}', dd_asset = '{5}', dd_vendor = '{6}'" +
", dd_vendorname = N'{7}', dd_2003no = '{8}', dd_2003answer = '{9}', dd_2003result = '{10}', dd_2003updated = '{11}', dd_2004no = '{12}'" +
", dd_2004answer = '{13}', dd_2004result = '{14}', dd_2004updated = '{15}', dd_kdcno = '{16}', dd_kdcissued = '{17}', dd_kdcrps = '{18}'" +
", dd_kdcanswer = '{19}', dd_kdcresult = '{20}', dd_kdcupdated = '{21}', dd_assetdesc = '{22}', dd_capdate = '{23}', dd_acquishkd = '{24}'" +
", dd_accumhkd = '{25}', dd_closing = '{26}', dd_bookhkd = '{27}', dd_fy = '{28}', dd_bookhkd2 = '{29}', dd_disposalringi = '{30}', dd_reportno = N'{31}'" +
", dd_reportissued = '{32}', dd_reportreceived = '{33}', dd_vendorresult = '{34}', dd_fadisposaldate = '{35}', dd_disposalremarks = N'{36}', dd_status = N'{1}' where dd_mould = '{2}' and dd_partno = '{3}'"
, type, status, mould, partno, rev, asset, vendor, vendorname, p2003no, p2003ans,
p2003result, p2003date, p2004no, p2004ans, p2004result, p2004date, kdcno, kdcdate, kdcrps, kdcans, kdcresult, kdcupdate, assetdesc,
capdate, acquis, accum, closing, bookval, year, bookval2, ringi, reportno, reportissued, reportdate, vendorans, assetdisposal, remarks);
DataService.GetInstance().ExecuteNonQuery(query);
UpdateMouldData(mould, partno, reportno, status);
}
SearchData("");
MessageBox.Show("Record has been uploaded.");
}
}
private void btnInsert_Click(object sender, EventArgs e)
{
OpenFileDialog ofd = new OpenFileDialog();
if (ofd.ShowDialog() == DialogResult.OK)
{
DataTable table = ofd.FileName.EndsWith(".xls") ? ImportExcel2003.TranslateToTable(ofd.FileName) : ImportExcel2007.TranslateToTable(ofd.FileName);
foreach (DataRow row in table.Rows)
{
string chaseno = row.ItemArray[0].ToString().Trim();
string partno = row.ItemArray[1].ToString().Trim();
string rev = row.ItemArray[2].ToString().Trim();
if (rev.Length == 1)
{
rev = "0" + rev;
}
string mouldno = row.ItemArray[3].ToString().Trim();
string div = row.ItemArray[4].ToString().Trim();
string curr = row.ItemArray[5].ToString().Trim();
string amt = row.ItemArray[6].ToString().Trim();
string amthkd = row.ItemArray[7].ToString().Trim();
string mpa = row.ItemArray[8].ToString().Trim();
string fa = row.ItemArray[9].ToString().Trim();
string tmpfa = row.ItemArray[10].ToString().Trim();
string qty = row.ItemArray[11].ToString().Trim();
string model = row.ItemArray[12].ToString().Trim();
string po = row.ItemArray[13].ToString().Trim();
string porev = row.ItemArray[14].ToString().Trim();
string issued = ImportExcel2003.ParseDateTime(row.ItemArray[15].ToString().Trim()).ToString("yyyy/MM/dd");
if (issued == "0001/01/01")
{
issued = row.ItemArray[15].ToString().Trim();
}
string category = row.ItemArray[16].ToString().Trim();
string ringi = row.ItemArray[17].ToString().Trim();
string vendor = row.ItemArray[18].ToString().Trim();
if (vendor.Length == 9)
{
vendor = "0" + vendor;
}
string mouldcode = row.ItemArray[19].ToString().Trim();
string status = row.ItemArray[20].ToString().Trim();
string oem = row.ItemArray[21].ToString().Trim();
string remarks = row.ItemArray[22].ToString().Trim();
string instock = ImportExcel2003.ParseDateTime(row.ItemArray[23].ToString().Trim()).ToString("yyyy/MM/dd");
if (instock == "0001/01/01")
{
instock = row.ItemArray[23].ToString().Trim();
}
string instock50 = ImportExcel2003.ParseDateTime(row.ItemArray[24].ToString().Trim()).ToString("yyyy/MM/dd");
if (instock50 == "0001/01/01")
{
instock50 = row.ItemArray[24].ToString().Trim();
}
string pgroup = row.ItemArray[25].ToString().Trim();
string ac = row.ItemArray[26].ToString().Trim();
string cc = row.ItemArray[27].ToString().Trim();
string checkdate = ImportExcel2003.ParseDateTime(row.ItemArray[28].ToString().Trim()).ToString("yyyy/MM/dd");
if (checkdate == "0001/01/01")
{
checkdate = row.ItemArray[28].ToString().Trim();
}
string cav = row.ItemArray[29].ToString().Trim();
string weight = row.ItemArray[30].ToString().Trim();
string equipment = row.ItemArray[31].ToString().Trim();
string shot = row.ItemArray[32].ToString().Trim();
string vertical = row.ItemArray[33].ToString().Trim();
string horizontal = row.ItemArray[34].ToString().Trim();
string height = row.ItemArray[35].ToString().Trim();
string collectdate = ImportExcel2003.ParseDateTime(row.ItemArray[36].ToString().Trim()).ToString("yyyy/MM/dd");
if (collectdate == "0001/01/01")
{
collectdate = row.ItemArray[36].ToString().Trim();
}
string itemtext = mouldno + "MP+" + mouldcode + "+" + div;
string projecttext = partno + "-" + rev;
string type = "Single";
string owner = "KDTHK";
string indate = DateTime.Today.ToString("yyyy/MM/dd");
string request = partno + rev;
if (chaseno == "")
{
continue;
}
string st = status == "入庫濟" ? "S" : "K";
string query = string.Format("if not exists (select * from tb_betamould where tm_chaseno = '{0}') insert into tb_betamould (tm_chaseno, tm_itemcode, tm_rev, tm_mouldno" +
", tm_status, tm_type, tm_currency, tm_amount, tm_amounthkd, tm_mpa, tm_fixedassetcode, tm_tmpfixedassetcode, tm_qty, tm_itemtext, tm_request, tm_indate, tm_projecttext" +
", tm_model, tm_po, tm_porev, tm_poissued, tm_category, tm_ringi_code, tm_vendor_code, tm_mouldcode_code, tm_st_code, tm_owner, tm_rm, tm_instockdate" +
", tm_instockdate50, tm_group, tm_accountcode, tm_costcentre, tm_checkdate, tm_cav, tm_weight, tm_accessory, tm_camera, tm_vertical, tm_horizontal, tm_height, tm_collectdate)" +
" values ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', '{10}', '{11}', '{12}', '{13}', '{14}', '{15}', '{16}', '{17}', '{18}', '{19}', '{20}', '{21}', '{22}', '{23}', '{24}', '{25}', '{26}', N'{27}'" +
", '{28}', '{29}', '{30}', '{31}', '{32}', '{33}', '{34}', '{35}', '{36}', '{37}', '{38}', '{39}', '{40}', '{41}')", chaseno, partno, rev, mouldno, div, type, curr, amt, amthkd, mpa, fa, tmpfa,
qty, itemtext, request, indate, projecttext, model, po, porev, issued, category, ringi, vendor, mouldcode, st, owner, remarks, instock, instock50, pgroup, ac, cc, checkdate,
cav, weight, equipment, shot, vertical, horizontal, height, collectdate);
DataService.GetInstance().ExecuteNonQuery(query);
}
MessageBox.Show("Record has been saved.");
}
}
private void btnUpload_Click(object sender, EventArgs e)
{
OpenFileDialog ofd = new OpenFileDialog();
if (ofd.ShowDialog() == DialogResult.OK)
{
DataTable table = ofd.FileName.EndsWith("xlsx") ? ImportExcel2007.TranslateToTable(ofd.FileName) : ImportExcel2003.TranslateToTable(ofd.FileName);
foreach (DataRow row in table.Rows)
{
string model = row.ItemArray[0].ToString().Trim();
string code = row.ItemArray[1].ToString().Trim();
string query = string.Format("if not exists (select * from tb_model where m_model = '{0}' and m_code = '{1}') insert into tb_model (m_model, m_code) values ('{0}', '{1}')", model, code);
DataService.GetInstance().ExecuteNonQuery(query);
}
MessageBox.Show("Record has been uploaded.");
}
if (cbSearch.SelectedIndex == 0)
SearchData("");
else
SearchData(txtSearch.Text.Trim());
}
private void UploadData2()
{
OpenFileDialog ofd = new OpenFileDialog();
DataTable tbUpload = new DataTable();
string[] simHeaders = { "code", "name", "invoice", "total", "detail" };
foreach (string header in simHeaders)
{
tbUpload.Columns.Add(header);
}
if (ofd.ShowDialog() == DialogResult.OK)
{
DataTable table = ofd.FileName.EndsWith(".xlsx") ? ImportExcel2007.TranslateToTable(ofd.FileName, "Template") : ImportExcel2003.TranslateToTable(ofd.FileName, "Template");
bool isValid = true;
int index = 1;
List <OutstandingError> errorList = new List <OutstandingError>();
List <Outstanding> list = new List <Outstanding>();
foreach (DataRow row in table.Rows)
{
string code = row.ItemArray[0].ToString().Trim();
if (code.Length == 9 && code.StartsWith("23"))
{
code = "0" + code;
}
string invoice = row.ItemArray[1].ToString().Trim();
string acccode = row.ItemArray[2].ToString().Trim();
string costcentre = row.ItemArray[3].ToString().Trim();
string currency = row.ItemArray[4].ToString().Trim().ToUpper();
string amount = row.ItemArray[5].ToString().Trim();
string desc1 = row.ItemArray[6].ToString().Trim();
string desc2 = row.ItemArray[7].ToString().Trim();
string desc3 = row.ItemArray[8].ToString().Trim();
string desc4 = row.ItemArray[9].ToString().Trim();
string desc5 = row.ItemArray[10].ToString().Trim();
string vcurr = AccUtil.GetVendorCurrency(code);
double amt;
//double tmpAmt = Convert.ToDouble(amount.Replace("-", ""));
bool isNumeric = double.TryParse(amount, out amt);
if (AccUtil.IsInvoiceExists(invoice, code))
{
errorList.Add(new OutstandingError {
index = index, Message = "Invoice: " + invoice + " has been used"
});
isValid = false;
}
if (!AccUtil.IsVendorExists(code))
{
errorList.Add(new OutstandingError {
index = index, Message = "Vendor code: " + code + " does not exist"
});
isValid = false;
}
if (!AccUtil.IsAccountCodeExists(acccode))
{
errorList.Add(new OutstandingError {
index = index, Message = "Account Code: " + acccode + " does not exist"
});
isValid = false;
}
if (!AccUtil.IsCostCentreExists(costcentre))
{
errorList.Add(new OutstandingError {
index = index, Message = "CostCentre: " + costcentre + " does not exist"
});
isValid = false;
}
if (currency != "HKD" && currency != "USD" && currency != "JPY" && currency != "EUR" && currency != "RMB")
{
errorList.Add(new OutstandingError {
index = index, Message = "Invalid currency format"
});
isValid = false;
}
if (currency != vcurr)
{
errorList.Add(new OutstandingError {
index = index, Message = "Mismatched currency. The correct currency is " + vcurr
});
isValid = false;
}
if (!isNumeric)
{
errorList.Add(new OutstandingError {
index = index, Message = "Amount: " + amount + " is not valid"
});
isValid = false;
}
amount = string.Format("{0:0.00}", Convert.ToDouble(amount));
var regex = new Regex(@"^\d+\.\d{2}?$");
if (!regex.IsMatch(amount) && !amount.StartsWith("-"))
{
errorList.Add(new OutstandingError {
index = index, Message = "Amount: " + amount + " is not valid"
});
isValid = false;
}
if (desc1.Length == 0)
{
errorList.Add(new OutstandingError {
index = index, Message = "Remarks 1 must be input"
});
isValid = false;
}
if (desc1.Length > 50 || desc2.Length > 50 || desc3.Length > 50 || desc4.Length > 50 || desc5.Length > 50)
{
errorList.Add(new OutstandingError {
index = index, Message = "Each remarks field length cannot over 50 characters"
});
isValid = false;
}
if (isValid)
{
list.Add(new Outstanding {
VendorCode = code, Invoice = invoice, AccountCode = acccode, CostCentre = costcentre, Amount = amount, Desc1 = desc1, Desc2 = desc2, Desc3 = desc3, Desc4 = desc4, Desc5 = desc5
});
}
index++;
}
if (errorList.Count > 0)
{
OutstandingErrorBox form = new OutstandingErrorBox(errorList);
form.ShowDialog();
}
else
{
var groupedList = from x in list
group x by new
{
x.Invoice,
x.VendorCode,
} into g
select new
{
Invoice = g.Key.Invoice,
VendorCode = g.Key.VendorCode,
Value = g.Sum(y => Convert.ToDecimal(y.Amount))
};
foreach (var item in groupedList)
{
string query = string.Format("select v_name from TB_ACC_MASTER_VENDOR where v_code = '{0}'", item.VendorCode);
string name = DataServiceCM.GetInstance().ExecuteScalar(query).ToString().Trim();
//Debug.WriteLine(item.Value.ToString());
tbUpload.Rows.Add(item.VendorCode, name, item.Invoice, item.Value.ToString(), "Detail");
}
OutstandingPreviewForm form = new OutstandingPreviewForm(tbUpload, list);
form.ShowDialog();
}
}
}
private void btnUpload_Click(object sender, EventArgs e)
{
DataTable tmptable = new DataTable();
string[] headers = { "リランフラグ", "伝票タイプ", "勘定設定カテゴリ", "品目", "品目テキスト", "数量",
"納期", "正味價格", "価格単位", "単位", "品目グループ", "購買グループ","希望仕入先",
"購買依頼者", "保管場所", "プラント", "購買組織", "勘定コード", "原価センタ", "固定資産番号",
"稟議番号", "納入先住所コード", "通貨コード", "追跡番号", "項目テキスト", "WBS要素" };
foreach (string header in headers)
{
tmptable.Columns.Add(header);
}
string flag = "", document = "", category = "", item = "", itemtext = "", qty = "";
string deliverydate = "", amount = "", priceunit = "", level = "", itemgroup = "";
string group = "", vendor = "", request = "", store = "", workspace = "", organization = "";
string accountcode = "", costcentre = "", fixedassetcode = "", ringi = "", address = "";
string currency = "", chaseno = "", projecttext = "", wbs = "", tmpfac = "", mpa = "", oemasset = "";
string delivery = DateTime.Today.AddMonths(3).ToString("yyyy/MM/dd");
string fixedValueText = "select fv_flag, fv_document, fv_item, fv_priceunit, fv_level, fv_store, fv_workspace, fv_organization, fv_address, fv_wbs from tb_betafixedvalue";
GlobalService.reader = DataService.GetInstance().ExecuteReader(fixedValueText);
while (GlobalService.reader.Read())
{
flag = GlobalService.reader.GetString(0);
document = GlobalService.reader.GetString(1);
item = GlobalService.reader.GetString(2);
priceunit = GlobalService.reader.GetString(3);
level = GlobalService.reader.GetString(4);
store = GlobalService.reader.GetString(5);
workspace = GlobalService.reader.GetString(6);
organization = GlobalService.reader.GetString(7);
address = GlobalService.reader.GetString(8);
wbs = GlobalService.reader.GetString(9);
}
GlobalService.reader.Close();
OpenFileDialog ofd = new OpenFileDialog()
{
Filter = "Excel Files |*.xls"
};
if (ofd.ShowDialog() == DialogResult.OK)
{
DataTable caltable = ImportExcel2003.TranslateToTable(ofd.FileName);
List <string> tmplist = new List <string>();
foreach (DataRow row in caltable.Rows)
{
string rowchaseno = row.ItemArray[0].ToString();
string rowcurrency = row.ItemArray[8].ToString();
string rowamount = row.ItemArray[9].ToString();
string updateText = string.Format("update tb_betamould set tm_deliverydate = '{0}' where tm_chaseno = '{1}'", delivery, rowchaseno);
DataService.GetInstance().ExecuteNonQuery(updateText);
string text = string.Format("select t.tm_category, t.tm_itemtext, t.tm_qty, t.tm_deliverydate, t.tm_amount" +
", m.mc_itemgroup, t.tm_group, t.tm_vendor_code, t.tm_request" +
", t.tm_accountcode, t.tm_costcentre, t.tm_fixedassetcode, t.tm_ringi_code, t.tm_currency, t.tm_chaseno, t.tm_projecttext, t.tm_tmpfixedassetcode, t.tm_mpa, t.tm_oemasset" +
" from tb_betamould as t, tbm_mouldcode as m where t.tm_mouldcode_code = m.mc_mouldcode and t.tm_chaseno = '{0}'", rowchaseno);
GlobalService.reader = DataService.GetInstance().ExecuteReader(text);
while (GlobalService.reader.Read())
{
category = GlobalService.reader.GetString(0);
itemtext = GlobalService.reader.GetString(1);
qty = GlobalService.reader.GetString(2);
deliverydate = GlobalService.reader.GetString(3);
amount = GlobalService.reader.GetString(4);
itemgroup = GlobalService.reader.GetString(5);
group = GlobalService.reader.GetString(6);
vendor = GlobalService.reader.GetString(7);
request = GlobalService.reader.GetString(8);
accountcode = GlobalService.reader.GetString(9);
costcentre = GlobalService.reader.GetString(10);
fixedassetcode = GlobalService.reader.GetString(11);
ringi = GlobalService.reader.GetString(12);
currency = GlobalService.reader.GetString(13);
chaseno = GlobalService.reader.GetString(14);
projecttext = GlobalService.reader.GetString(15);
tmpfac = GlobalService.reader.GetString(16);
mpa = GlobalService.reader.GetString(17);
oemasset = GlobalService.reader.GetString(18);
if (vendor.Length == 9)
{
vendor = "0" + vendor;
}
if (mpa == "True" && tmpfac == "" && oemasset == "")
{
MessageBox.Show("Temp Fixed Asset Code not saved yet. Please contact Account Division.");
return;
}
if (mpa == "True")
{
decimal decamount = Convert.ToDecimal(amount) / 2;
amount = decamount.ToString();
tmptable.Rows.Add(new object[] { flag, document, category, item, itemtext, qty, deliverydate, amount,
priceunit, level, itemgroup, group, vendor, request, store, workspace, organization,
accountcode, costcentre, tmpfac, ringi, address, currency, chaseno, projecttext, wbs });
}
else
{
tmptable.Rows.Add(new object[] { flag, document, category, item, itemtext, qty, deliverydate, amount,
priceunit, level, itemgroup, group, vendor, request, store, workspace, organization,
accountcode, costcentre, fixedassetcode, ringi, address, currency, chaseno, projecttext, wbs });
}
tmplist.Add(rowchaseno);
}
GlobalService.reader.Close();
}
for (int i = 0; i < tmplist.Count; i++)
{
string tmpchaseno = tmplist[i].ToString();
string commandText = string.Format("update tb_betamould set tm_st_code = 'D', tm_modify = 'Yes', tm_moulditemviewer = 'Yes' where tm_chaseno = '{0}'", tmpchaseno);
DataService.GetInstance().ExecuteNonQuery(commandText);
}
if (tmptable.Rows.Count == 0)
{
MessageBox.Show("No Record can be downloaded");
}
else
{
DataView dv = tmptable.DefaultView;
dv.Sort = "追跡番号 ASC";
DataTable sorted = dv.ToTable();
ExportXlsUtil.XlsUtil(sorted);
}
}
}
