private void SaveData()
{
dgvOutstanding.EndEdit();
string vendor = txtVendorCode.Text.Trim();
string vendorname = txtVendorName.Text.Trim();
string invoice = txtInvoice.Text.Trim();
string currency = cbCurr.Text;
string indate = dtpInput.Value.ToString("yyyy/MM/dd");
string paydate = dtpPaymentDate.Value.ToString("yyyy/MM/dd");
string total = txtAmount.Text.Trim();
if (!AccUtil.IsVendorExists(vendor))
{
MessageBox.Show("Vendor: " + vendor + " does not exist. Please check your data.");
return;
}
if (AccUtil.IsInvoiceExists(invoice, vendor))
{
MessageBox.Show("Invoice: " + invoice + " has been used.");
return;
}
string signal = dtpInput.Value.Month == DateTime.Today.Month ? "True" : "False";
string sectHead = UserUtil.GetSectionHead(UserUtil.GetSect(GlobalService.User));
string divHead = UserUtil.GetDivisionHead(UserUtil.GetDivision(GlobalService.User));
string dept = UserUtil.GetDept(GlobalService.User);
string deptHead = dept.Contains("RPS") ? UserUtil.GetDepartmentHead(dept) : "";
string cm1st = "Lee Suk Ha(李淑霞,Zoe)";
string cm2nd = "Li Yuen Yan(李婉茵,Sharon)";
bool isValid = true;
List <Outstanding> list = new List <Outstanding>();
foreach (DataGridViewRow row in dgvOutstanding.Rows)
{
if (row.IsNewRow)
{
continue;
}
if (row.Cells[0].Value == null && row.Cells[2].Value == null && row.Cells[4].Value == null && row.Cells[5].Value == null && row.Cells[6].Value == null && row.Cells[7].Value == null && row.Cells[8].Value == null && row.Cells[9].Value == null)
{
continue;
}
if (row.Cells[0].Value == null || row.Cells[2].Value == null || row.Cells[4].Value == null || row.Cells[5].Value == null)
{
isValid = false;
}
string desc1 = "", desc2 = "", desc3 = "", desc4 = "", desc5 = "";
try
{
desc1 = row.Cells[5].Value.ToString().Trim();
desc2 = row.Cells[6].Value == null ? "" : row.Cells[6].Value.ToString().Trim();
desc3 = row.Cells[7].Value == null ? "" : row.Cells[7].Value.ToString().Trim();
desc4 = row.Cells[8].Value == null ? "" : row.Cells[8].Value.ToString().Trim();
desc5 = row.Cells[9].Value == null ? "" : row.Cells[9].Value.ToString().Trim();
}
catch
{
isValid = false;
}
string acc = row.Cells[0].Value.ToString().Trim();
string accountcode = acc.Substring(0, 10);
string accname = acc.Substring(12);
accname = accname.Substring(0, accname.Length - 1);
string cc = row.Cells[2].Value.ToString().Trim();
string costcentre = cc.Substring(0, 10);
string costcentrename = cc.Substring(12);
costcentrename = costcentrename.Substring(0, costcentrename.Length - 1);
string inputAmount = row.Cells[4].Value.ToString().Trim();
list.Add(new Outstanding {
VendorCode = vendor, Invoice = invoice, AccountCode = accountcode, CostCentre = costcentre, Amount = inputAmount, Desc1 = desc1, Desc2 = desc2, Desc3 = desc3, Desc4 = desc4, Desc5 = desc5
});
}
if (!isValid)
{
MessageBox.Show("Please check the following:\n1. Select AccountCode & CostCentre.\n2. Input Amount.\n3. Remarks 1 must be input.", "Error found", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
string invoiceText = string.Format("insert into TB_ACC_MASTER_INVOICE (i_invoice, i_vendor) values ('{0}', '{1}')", invoice, vendor);
DataServiceCM.GetInstance().ExecuteNonQuery(invoiceText);
string name = AccUtil.GetVendorName(vendor);
string curr = AccUtil.GetVendorCurrency(vendor);
string payterm = AccUtil.GetVendorPayTerm(vendor);
string query = string.Format("insert into TB_ACC_OUTSTANDING (o_invoice, o_vendor, o_vendorname, o_inputdate, o_paymentdate, o_currency, o_amount, o_createdby" +
", o_created, o_div, o_staff, o_acc, o_sect, o_dept) values ('{0}', '{1}', N'{2}', '{3}', '{4}', '{5}', '{6}', N'{7}', '{8}', N'{9}', N'{10}', N'{11}', N'{12}', N'{13}')", invoice, vendor, name, indate, paydate, currency, total,
GlobalService.User, DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss"), divHead, cm1st, cm2nd, sectHead, deptHead);
DataServiceCM.GetInstance().ExecuteNonQuery(query);
foreach (Outstanding item in list)
{
int id = AccUtil.GetOutstandingIdByInvoice(item.Invoice);
string accname = AccUtil.GetAccountName(item.AccountCode);
string costcentrename = AccUtil.GetCostCentreName(item.CostCentre);
string desc = (item.Desc1 + item.Desc2 + item.Desc3 + item.Desc4 + item.Desc5);
desc = desc.Substring(0, Math.Min(desc.Length, 50));
string text = string.Format("insert into TB_ACC_OUTSTANDING_DETAIL (od_o_id, od_accountcode, od_accountname, od_costcentre, od_costcentrename, od_amount" +
", od_desc1, od_desc2, od_desc3, od_desc4, od_desc5, od_display) values ('{0}', '{1}', N'{2}', '{3}', N'{4}', '{5}', N'{6}', N'{7}', N'{8}', N'{9}', N'{10}', N'{11}')", id, item.AccountCode,
accname, item.CostCentre, costcentrename, DoFormat(item.Amount), item.Desc1, item.Desc2, item.Desc3, item.Desc4, item.Desc5, desc);
DataServiceCM.GetInstance().ExecuteNonQuery(text);
}
string content = "Application Approval required. Please click <a href=\"\\\\kdthk-dm1\\project\\it system\\MyCloud Beta\\KDTHK-DM-SP.application\">HERE</a> to approval process.";
string body = "<p><span style=\"font-family: Calibri;\">" + content + "</span></p>";
KDTHK_DM_SP.utils.EformUtil.SendApprovalEmail(invoice, GlobalService.User, AdUtil.GetEmailByUsername(GlobalService.User, "kmhk.local"), AdUtil.GetEmailByUsername(sectHead, "kmhk.local"), body, "Outstanding Slip");
MessageBox.Show("Record has been saved.");
DialogResult = DialogResult.OK;
}
}
private void btnUpload_Click(object sender, EventArgs e)
{
OpenFileDialog ofd = new OpenFileDialog();
if (ofd.ShowDialog() == DialogResult.OK)
{
DataTable table = ImportExcel2007.TranslateToTable(ofd.FileName);
List <Outstanding> list = new List <Outstanding>();
bool isValid = true;
foreach (DataRow row in table.Rows)
{
string accountcode = row.ItemArray[0].ToString().Trim();
string costcentre = row.ItemArray[1].ToString().Trim();
string amount = row.ItemArray[2].ToString().Trim();
string desc1 = row.ItemArray[3].ToString().Trim();
string desc2 = row.ItemArray[4].ToString().Trim();
string desc3 = row.ItemArray[5].ToString().Trim();
string desc4 = row.ItemArray[6].ToString().Trim();
string desc5 = row.ItemArray[7].ToString().Trim();
if (!AccUtil.IsAccountCodeExists(accountcode))
{
isValid = false;
}
if (!AccUtil.IsCostCentreExists(costcentre))
{
isValid = false;
}
if (!IsAmountValid(amount))
{
isValid = false;
}
if (desc1.Length > 50 || desc2.Length > 50 || desc3.Length > 50 || desc4.Length > 50 || desc5.Length > 50)
{
isValid = false;
}
//list.Add(new Outstanding { AccountCode = accountcode, AccountName = "", CostCentre = costcentre, CostCentreName = "", Amount = amount, Desc1 = desc1, Desc2 = desc2, Desc3 = desc3, Desc4 = desc4, Desc5 = desc5 });
}
if (isValid)
{
foreach (Outstanding item in list)
{
dgvOutstanding.Rows.Add(item.AccountCode, AccUtil.GetAccountName(item.AccountCode), item.CostCentre, AccUtil.GetCostCentreName(item.CostCentre), item.Amount, item.Desc1, item.Desc2, item.Desc3, item.Desc4, item.Desc5);
}
GetTotalAmount();
}
else
{
MessageBox.Show("Invalid AccountCode / CostCentre / Amount input. Please revise data.");
}
}
}
