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