private void btnApprove_Click(object sender, EventArgs e)
        {
            string now = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss");

            string text = _status == "科責承認中" ? string.Format("update TB_ACC_OUTSTANDING set o_divapproval = 'Yes', o_divapprovaldate = '{0}', o_status = N'會計處理中' where o_invoice = '{1}'", now, _invoice)
                : _status == "會計處理中" ? string.Format("update TB_ACC_OUTSTANDING set o_staffapproval = 'Yes', o_staffapprovaldate = '{0}', o_status = N'會計承認中' where o_invoice = '{1}'", now, _invoice)
                : string.Format("update TB_ACC_OUTSTANDING set o_accapproval = 'Yes', o_accapprovaldate = '{0}', o_status = N'申請處理完成' where o_invoice = '{1}'", now, _invoice);

            DataServiceCM.GetInstance().ExecuteNonQuery(text);

            string applicant = AccUtil.GetApplicant(_invoice);
            string div       = AccUtil.GetDivisionApprover(_invoice);
            string staff     = AccUtil.GetAccStaff(_invoice);
            string acc       = AccUtil.GetAccApprover(_invoice);

            if (_status == "科責承認中")
            {
                EformUtil.SendApprovalEmail(_invoice, applicant, AdUtil.GetEmailByUsername(applicant, "kmhk.local"), AdUtil.GetEmailByUsername(staff, "kmhk.local"), "", "Outstanding Slip - " + _invoice);
            }

            if (_status == "會計處理中")
            {
                EformUtil.SendApprovalEmail(_invoice, applicant, AdUtil.GetEmailByUsername(applicant, "kmhk.local"), AdUtil.GetEmailByUsername(acc, "kmhk.local"), "", "Outstanding Slip - " + _invoice);
            }

            if (_status == "會計承認中")
            {
                EformUtil.SendFinishedEmail(_invoice, acc, AdUtil.GetEmailByUsername(acc, "kmhk.local"), AdUtil.GetEmailByUsername(applicant, "kmhk.local"), "Outstanding Slip Application Finished - " + _invoice, "You Outstanding Slip Application has been finished.");
            }

            DialogResult = DialogResult.OK;
        }
        private void btnReject_Click(object sender, EventArgs e)
        {
            string now = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss");

            string text = _status == "科責承認中" ? string.Format("update TB_ACC_OUTSTANDING set o_divapproval = 'No', o_divapprovaldate = '{0}', o_status = N'科責已拒絕' where o_invoice = '{1}'", now, _invoice)
                : _status == "會計處理中" ? string.Format("update TB_ACC_OUTSTANDING set o_staffapproval = 'No', o_staffapprovaldate = '{0}', o_status = N'會計已拒絕' where o_invoice = '{1}'", now, _invoice)
                : string.Format("update TB_ACC_OUTSTANDING set o_accapproval = 'No', o_accapprovaldate = '{0}', o_status = N'會計已拒絕' where o_invoice = '{1}'", now, _invoice);

            DataServiceCM.GetInstance().ExecuteNonQuery(text);

            string applicant = AccUtil.GetApplicant(_invoice);

            EformUtil.SendRejectEmail(_invoice, GlobalService.User, AdUtil.GetEmailByUsername(GlobalService.User, "kmhk.local"), AdUtil.GetEmailByUsername(applicant, "kmhk.local"), "Outstanding Slip Rejected - " + _invoice, "Your Outstanding Slip has been rejected by " + GlobalService.User);

            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 <int> invoiceIdList = new List <int>();

                foreach (DataRow row in table.Rows)
                {
                    string invoice    = row.ItemArray[0].ToString().Trim();
                    string vendor     = row.ItemArray[1].ToString().Trim();
                    string acccode    = row.ItemArray[3].ToString().Trim();
                    string costcentre = row.ItemArray[4].ToString().Trim();
                    string amount     = row.ItemArray[10].ToString().Trim();
                    string approval   = row.ItemArray[11].ToString().Trim();

                    if (approval == "Yes")
                    {
                        int    id     = GetInvoiceId(invoice);
                        string status = GetInvoiceStatus(invoice);

                        string query = status == "會計處理中" ? string.Format("update TB_ACC_OUTSTANDING_DETAIL set od_staffapproval = 'Yes' where od_o_id = '{0}' and od_accountcode = '{1}'" +
                                                                         " and od_costcentre = '{2}' and od_amount = '{3}'", id, acccode, costcentre, amount) : string.Format("update TB_ACC_OUTSTANDING_DETAIL set od_divapproval = 'Yes'" +
                                                                                                                                                                              " where od_o_id = '{0}' and od_accountcode = '{1}' and od_costcentre = '{2}' and od_amount = '{3}'", id, acccode, costcentre, amount);
                        DataServiceCM.GetInstance().ExecuteNonQuery(query);

                        invoiceIdList.Add(id);
                    }
                }

                invoiceIdList = invoiceIdList.Distinct().ToList();

                string now = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss");

                foreach (int item in invoiceIdList)
                {
                    string status  = GetInvoiceStatusById(item);
                    string invoice = GetInvoiceById(item);

                    string applicant = AccUtil.GetApplicant(invoice);
                    string div       = AccUtil.GetDivisionApprover(invoice);
                    string staff     = AccUtil.GetAccStaff(invoice);
                    string acc       = AccUtil.GetAccApprover(invoice);

                    if (status == "會計處理中")
                    {
                        if (IsAllItemApprovedByStaff(item))
                        {
                            string query = string.Format("update TB_ACC_OUTSTANDING set o_status = N'會計承認中', o_staffapproval = 'Yes', o_staffapprovaldate = '{0}' where o_id = '{1}'", now, item);
                            DataServiceCM.GetInstance().ExecuteNonQuery(query);

                            EformUtil.SendApprovalEmail(_invoice, applicant, AdUtil.GetEmailByUsername(applicant, "kmhk.local"), AdUtil.GetEmailByUsername(acc, "kmhk.local"), "", "Outstanding Slip - " + invoice);
                        }
                    }

                    if (status == "會計承認中")
                    {
                        if (IsAllItemApprovedByAcc(item))
                        {
                            string query = string.Format("update TB_ACC_OUTSTANDING set o_status = N'申請處理完成', o_accapproval = 'Yes', o_accapprovaldate = '{0}' where o_id = '{1}'", now, item);
                            DataServiceCM.GetInstance().ExecuteNonQuery(query);

                            EformUtil.SendFinishedEmail(_invoice, acc, AdUtil.GetEmailByUsername(acc, "kmhk.local"), AdUtil.GetEmailByUsername(applicant, "kmhk.local"), "Outstanding Slip Application Finished - " + invoice, "You Outstanding Slip Application has been finished.");
                        }
                    }
                }

                MessageBox.Show("Record has been uploaded.");
            }
        }