private void dgvPreview_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
if (e.ColumnIndex == 4)
{
string invoice = dgvPreview.CurrentRow.Cells[2].Value.ToString().Trim();
string code = dgvPreview.CurrentRow.Cells[0].Value.ToString().Trim();
string name = dgvPreview.CurrentRow.Cells[1].Value.ToString().Trim();
string currency = AccUtil.GetVendorCurrency(code);
string total = dgvPreview.CurrentRow.Cells[3].Value.ToString().Trim();
OutstandingPreviewDetailForm form = new OutstandingPreviewDetailForm(invoice, code, name, currency, total, dataList);
form.ShowDialog();
}
}
private void btnConfirm_Click(object sender, EventArgs e)
{
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)";
foreach (DataGridViewRow row in dgvPreview.Rows)
{
string invoice = row.Cells[2].Value.ToString().Trim();
string code = row.Cells[0].Value.ToString().Trim();
string name = row.Cells[1].Value.ToString().Trim();
string currency = AccUtil.GetVendorCurrency(code);
string payterm = AccUtil.GetVendorPayTerm(code);
string paydate = AccUtil.PayDate(Convert.ToDateTime(DateTime.Today.ToString("yyyy/MM/dd")), payterm);
string total = row.Cells[3].Value.ToString().Trim();
string invoiceText = string.Format("insert into TB_ACC_MASTER_INVOICE (i_invoice, i_vendor) values ('{0}', '{1}')", invoice, code);
DataServiceCM.GetInstance().ExecuteNonQuery(invoiceText);
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, code, name, DateTime.Today.ToString("yyyy/MM/dd"), paydate, currency, total,
GlobalService.User, DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss"), divHead, cm1st, cm2nd, sectHead, deptHead);
DataServiceCM.GetInstance().ExecuteNonQuery(query);
//KDTHK_DM_SP.utils.EformUtil.SendApprovalEmail(invoice, GlobalService.User, AdUtil.GetEmailByUsername(GlobalService.User, "kmhk.local"), AdUtil.GetEmailByUsername(sectHead, "kmhk.local"), "", "Outstanding Slip");
}
foreach (Outstanding item in dataList)
{
int id = AccUtil.GetOutstandingIdByInvoice(item.Invoice);
string acName = AccUtil.GetAccountName(item.AccountCode);
string ccName = 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,
acName, item.CostCentre, ccName, 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>";
bool isSent = false;
if (!isSent)
{
isSent = true;
KDTHK_DM_SP.utils.EformUtil.SendApprovalEmail("", 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 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();
}
}
}