private void btnContinue_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                MessageBoxResult mr = MessageBox.Show("Are you sure you want to proceed?", "Question", MessageBoxButton.YesNo, MessageBoxImage.Question);
                if (mr == MessageBoxResult.Yes)
                {
                    if (rdDaif.IsChecked == true)
                    {
                        using (var ctx = new finalContext())
                        {
                            FPaymentInfo fp = ctx.FPaymentInfo.Find(fId);
                            ReturnedCheque rc = new ReturnedCheque { DateReturned = DateTime.Today.Date, Fee = DaifFee, FPaymentInfoID = fId, Remarks = "DAIF", isPaid = false };
                            fp.PaymentStatus = "Returned";
                            ctx.ReturnedCheques.Add(rc);

                            AuditTrail at = new AuditTrail { EmployeeID = UserID, DateAndTime = DateTime.Now, Action = "Processed returned cheque " + fp.ChequeInfo };
                            ctx.AuditTrails.Add(at);

                            ctx.SaveChanges();
                            MessageBox.Show("Okay", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                            this.Close();
                        }
                    }
                    else
                    {
                        using (var ctx = new finalContext())
                        {
                            FPaymentInfo fp = ctx.FPaymentInfo.Find(fId);
                            ClosedAccount cc = new ClosedAccount { DateClosed = DateTime.Today.Date, Fee = ClosedFee, LoanID = fp.LoanID, isPaid = false };
                            //fp.PaymentStatus = "Returned";
                            fp.Loan.Status = "Closed Account";
                            ctx.ClosedAccounts.Add(cc);

                            var chq = from c in ctx.FPaymentInfo
                                      where c.PaymentStatus != "Cleared" && c.LoanID==fp.LoanID
                                      select c;

                            foreach (var item in chq)
                            {
                                item.PaymentStatus = "Void";
                            }
                            AuditTrail at = new AuditTrail { EmployeeID = UserID, DateAndTime = DateTime.Now, Action = "Processed returned cheque " + fp.ChequeInfo };
                            ctx.AuditTrails.Add(at);

                            ctx.SaveChanges();
                            MessageBox.Show("Transaction has been successfully processed", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                            this.Close();
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                System.Windows.MessageBox.Show("Runtime Error: " + ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
                return;
            }
        }
        private void btnSave_Click(object sender, RoutedEventArgs e)
        {
            //try
            //{
                if (lblName.Content == "?" || lblDesc.Content == "?"
                    || String.IsNullOrWhiteSpace(txtName.Text)|| dt.SelectedDate.Value == null)
                {
                    System.Windows.MessageBox.Show("Please input correct format and/or fill all required fields", "Error", MessageBoxButton.OK, MessageBoxImage.Exclamation);
                    return;
                }

                if (status == "Add")
                {
                    using (var ctx = new finalContext())
                    {
                        //if (isYearly.IsChecked == true)
                        //{
                            var mC = ctx.MPaymentInfoes.Where(x => x.DueDate.Month == dt.SelectedDate.Value.Month && x.DueDate.Day == dt.SelectedDate.Value.Day).Count();
                            if (mC > 0)
                            {
                                System.Windows.MessageBox.Show("Payments on the given day will be automatically adjusted", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                                var ps = from x in ctx.MPaymentInfoes
                                         select x;

                                DateTime idt1 = dt.SelectedDate.Value;
                                DateTime idt = DateAndTime.DateAdd(DateInterval.Day, 1, idt1);

                                bool isHoliday = true;
                                while (isHoliday == true || idt.Date.DayOfWeek.ToString() == "Saturday" || idt.Date.DayOfWeek.ToString() == "Sunday")
                                {
                                    if (idt.Date.DayOfWeek.ToString() == "Saturday")
                                    {
                                        idt = DateAndTime.DateAdd(DateInterval.Day, 2, idt);
                                    }
                                    else if (idt.Date.DayOfWeek.ToString() == "Sunday")
                                    {
                                        idt = DateAndTime.DateAdd(DateInterval.Day, 1, idt);
                                    }
                                    var myC = ctx.Holidays.Where(x => x.Date.Month == idt.Date.Month && x.Date.Day == idt.Date.Day && x.isYearly == true).Count();
                                    if (myC > 0)
                                    {
                                        idt = DateAndTime.DateAdd(DateInterval.Day, 1, idt);
                                        isHoliday = true;
                                    }
                                    else
                                    {
                                        myC = ctx.Holidays.Where(x => x.Date.Month == idt.Date.Month && x.Date.Day == idt.Date.Day && x.Date.Year == idt.Date.Year && x.isYearly == !true).Count();
                                        if (myC > 0)
                                        {
                                            idt = DateAndTime.DateAdd(DateInterval.Day, 1, idt);
                                            isHoliday = true;
                                        }
                                        else
                                        {
                                            isHoliday = false;
                                        }
                                    }
                                }
                                foreach (var x in ps)
                                {
                                    if (isYearly.IsChecked == true)
                                    {
                                        if (x.DueDate.Month == idt1.Date.Month && x.DueDate.Day == idt1.Date.Day)
                                        {
                                            x.DueDate = idt;
                                        }
                                    }
                                    else
                                    {
                                        if (x.DueDate.Month == idt1.Date.Month && x.DueDate.Day == idt1.Date.Day && x.DueDate.Year == idt1.Date.Year)
                                        {
                                            x.DueDate = idt;
                                        }
                                    }
                                }
                            }
                       // }


                        var num = ctx.Holidays.Where(x => x.HolidayName == txtName.Text).Count();
                        if (num > 0)
                        {
                            System.Windows.MessageBox.Show("Holiday already exists", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                            return;
                        }

                        num = ctx.Holidays.Where(x => x.Date == dt.SelectedDate.Value).Count();
                        if (num > 0)
                        {
                            System.Windows.MessageBox.Show("Holiday already exists", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                            return;
                        }

                        Holiday h = new Holiday { HolidayName = txtName.Text, Date = dt.SelectedDate.Value, isYearly = Convert.ToBoolean(isYearly.IsChecked), Description = txtDesc.Text };

                        AuditTrail at = new AuditTrail { EmployeeID = UserID, DateAndTime = DateTime.Now, Action = "Added Holiday " + txtName.Text };
                        ctx.AuditTrails.Add(at);

                        ctx.Holidays.Add(h);
                        ctx.SaveChanges();
                        System.Windows.MessageBox.Show("Holiday has been successfully Added", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                        this.Close();
                    }
                }
                else
                {
                    using (var ctx = new finalContext())
                    {
                        var h = ctx.Holidays.Find(hId);
                        h.HolidayName = txtName.Text;
                        h.Date = dt.SelectedDate.Value;
                        h.isYearly = Convert.ToBoolean(isYearly.IsChecked);
                        h.Description = txtDesc.Text;

                        var mC = ctx.MPaymentInfoes.Where(x => x.DueDate.Month == dt.SelectedDate.Value.Month && x.DueDate.Day == dt.SelectedDate.Value.Day).Count();
                        if (mC > 0)
                        {
                            System.Windows.MessageBox.Show("Payments on the given day will be automatically adjusted", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                            var ps = from x in ctx.MPaymentInfoes
                                     select x;

                            DateTime idt1 = dt.SelectedDate.Value;
                            DateTime idt = DateAndTime.DateAdd(DateInterval.Day, 1, idt1);

                            bool isHoliday = true;
                            while (isHoliday == true || idt.Date.DayOfWeek.ToString() == "Saturday" || idt.Date.DayOfWeek.ToString() == "Sunday")
                            {
                                if (idt.Date.DayOfWeek.ToString() == "Saturday")
                                {
                                    idt = DateAndTime.DateAdd(DateInterval.Day, 2, idt);
                                }
                                else if (idt.Date.DayOfWeek.ToString() == "Sunday")
                                {
                                    idt = DateAndTime.DateAdd(DateInterval.Day, 1, idt);
                                }
                                var myC = ctx.Holidays.Where(x => x.Date.Month == idt.Date.Month && x.Date.Day == idt.Date.Day && x.isYearly == true).Count();
                                if (myC > 0)
                                {
                                    idt = DateAndTime.DateAdd(DateInterval.Day, 1, idt);
                                    isHoliday = true;
                                }
                                else
                                {
                                    myC = ctx.Holidays.Where(x => x.Date.Month == idt.Date.Month && x.Date.Day == idt.Date.Day && x.Date.Year == idt.Date.Year && x.isYearly == !true).Count();
                                    if (myC > 0)
                                    {
                                        idt = DateAndTime.DateAdd(DateInterval.Day, 1, idt);
                                        isHoliday = true;
                                    }
                                    else
                                    {
                                        isHoliday = false;
                                    }
                                }
                            }
                            foreach (var x in ps)
                            {
                                if (isYearly.IsChecked == true)
                                {
                                    if (x.DueDate.Month == idt1.Date.Month && x.DueDate.Day == idt1.Date.Day)
                                    {
                                        x.DueDate = idt;
                                    }
                                }
                                else
                                {
                                    if (x.DueDate.Month == idt1.Date.Month && x.DueDate.Day == idt1.Date.Day && x.DueDate.Year == idt1.Date.Year)
                                    {
                                        x.DueDate = idt;
                                    }
                                }
                            }
                        }

                        AuditTrail at = new AuditTrail { EmployeeID = UserID, DateAndTime = DateTime.Now, Action = "Updated Holiday " + txtName.Text };
                        ctx.AuditTrails.Add(at);
                        ctx.SaveChanges();
                        System.Windows.MessageBox.Show("Holiday Successfully Updated", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                        this.Close();
                    }
                }

            //}
            //catch (Exception ex)
            //{
            //    System.Windows.MessageBox.Show("Runtime Error: " + ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
            //    return;
            //}
        }
        private void btnSave_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                if (lblName.Content == "?" || lblDesc.Content == "?"
                    || String.IsNullOrWhiteSpace(txtName.Text))
                {
                    System.Windows.MessageBox.Show("Please input correct format and/or fill all required fields", "Error", MessageBoxButton.OK, MessageBoxImage.Exclamation);
                    return;
                }

                if (status == "Add")
                {
                    using (var ctx = new finalContext())
                    {
                        var num = ctx.Banks.Where(x => x.BankName == txtName.Text).Count();
                        if (num > 0)
                        {
                            return;
                        }

                        Bank bank = new Bank { BankName = txtName.Text, Description = txtDesc.Text, Active = true };


                        var add = from ad in ctx.TempAdresses
                                  select ad;
                        int ctr = 1;
                        foreach (var item in add)
                        {
                            BankAddress bAd = new BankAddress { BankNum = ctr, City = item.City, Province = item.Province, Street = item.Street };
                            ctx.BankAdresses.Add(bAd);
                            ctr++;
                        }

                        AuditTrail at = new AuditTrail { EmployeeID = UserID, DateAndTime = DateTime.Now, Action = "Added new Bank " + txtName.Text };
                        ctx.AuditTrails.Add(at);

                        ctx.Banks.Add(bank);
                        ctx.SaveChanges();
                        System.Windows.MessageBox.Show("New Bank Added", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                        this.Close();
                    }
                }
                else
                {
                    using (var ctx = new finalContext())
                    {
                        var bank = ctx.Banks.Find(bId);
                        bank.BankName = txtName.Text;
                        bank.Description = txtDesc.Text;
                        AuditTrail at = new AuditTrail { EmployeeID = UserID, DateAndTime = DateTime.Now, Action = "Updated Bank " + txtName.Text };
                        ctx.AuditTrails.Add(at);
                        ctx.SaveChanges();
                        System.Windows.MessageBox.Show("Bank Successfully Updated", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                        this.Close();
                    }
                }

            }
            catch (Exception ex)
            {
                System.Windows.MessageBox.Show("Runtime Error: " + ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
                return;
            }
        }
        private void btnDep_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                if (status == "Deposit")
                {
                    if (state == "Dep")
                    {
                        MessageBoxResult mr = MessageBox.Show("Are you sure you want to deposit the selected cheque(s)?", "Question", MessageBoxButton.YesNo, MessageBoxImage.Question);
                        if (mr == MessageBoxResult.Yes)
                        {
                            using (var ctx = new finalContext())
                            {
                                var chq = from ch in ctx.FPaymentInfo
                                          where ch.PaymentStatus == "Due"
                                          select ch;
                                foreach (var item in chq)
                                {
                                    /*var ctr = ctx.FPaymentInfo.Where(x => x.LoanID == item.LoanID && x.PaymentStatus == "Due/Pending").Count();
                                    if (ctr != 0)
                                    {
                                        var hq = ctx.FPaymentInfo.Where(x => x.LoanID == item.LoanID && x.PaymentStatus == "Due/Pending").First();
                                        hq.PaymentStatus = "Due";
                                    }*/
                                    var ch = ctx.DepositedCheques.Where(x => x.FPaymentInfoID == item.FPaymentInfoID).Count();
                                    if (ch > 0)
                                    {
                                        item.DepositedCheque.DepositDate = DateTime.Today.Date;
                                        item.PaymentStatus = "Deposited";
                                    }
                                    else
                                    {
                                        item.PaymentStatus = "Deposited";
                                        DepositedCheque dc = new DepositedCheque { DepositDate = DateTime.Today.Date, FPaymentInfoID = item.FPaymentInfoID };
                                        ctx.DepositedCheques.Add(dc);
                                    }
                                }
                                AuditTrail at = new AuditTrail { EmployeeID = UserID, DateAndTime = DateTime.Now, Action = "Deposited Cheque(s)" };
                                ctx.AuditTrails.Add(at);

                                ctx.SaveChanges();
                                MessageBox.Show("Transaction has been successfully processed", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                                rg();
                            }
                        }
                    }
                    else if (state == "Undep")
                    {
                        using (var ctx = new finalContext())
                        {
                            int id = Convert.ToInt32(getRow(dg, 0));
                            int num = Convert.ToInt32(getRow(dg, 1));
                            FPaymentInfo fp = ctx.FPaymentInfo.Where(x => x.LoanID == id && x.PaymentNumber == num).First();
                            DepositedCheque dp = ctx.DepositedCheques.Find(fp.FPaymentInfoID);
                            MessageBoxResult mr = MessageBox.Show("Are you sure you want to void this transaction?", "Question", MessageBoxButton.YesNo, MessageBoxImage.Question);
                            if (mr == MessageBoxResult.Yes)
                            {
                                fp.PaymentStatus = "Due";
                                ctx.DepositedCheques.Remove(dp);
                                ctx.SaveChanges();
                                MessageBox.Show("Transaction has been successfully processed", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                                checkDue();
                                rg();
                                rdDue.IsChecked = true;
                            }
                        }
                    }
                    else if (state == "Redep")
                    {
                        using (var ctx = new finalContext())
                        {
                            int id = Convert.ToInt32(getRow(dg, 0));
                            int num = Convert.ToInt32(getRow(dg, 1));
                            FPaymentInfo fp = ctx.FPaymentInfo.Where(x => x.LoanID == id && x.PaymentNumber == num).First();
                            DepositedCheque dp = ctx.DepositedCheques.Find(fp.FPaymentInfoID);
                            MessageBoxResult mr = MessageBox.Show("Are you sure you want to redeposit this cheque?", "Question", MessageBoxButton.YesNo, MessageBoxImage.Question);
                            if (mr == MessageBoxResult.Yes)
                            {
                                wpfCheckout frm = new wpfCheckout();
                                frm.status = "Daif";
                                frm.fId = fp.FPaymentInfoID;
                                frm.ShowDialog();

                                if (cont == false)
                                {
                                    System.Windows.MessageBox.Show("Please pay the DAIF fee first", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
                                    return;
                                }

                                fp.PaymentStatus = "Deposited";
                                dp.DepositDate = DateTime.Today.Date;
                                AuditTrail at = new AuditTrail { EmployeeID = UserID, DateAndTime = DateTime.Now, Action = "Redeposit cheque " + fp.ChequeInfo };
                                ctx.AuditTrails.Add(at);
                                ctx.SaveChanges();
                                MessageBox.Show("Transaction has been successfully processed.", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                                checkDue();
                                rg();
                                //rdDue.IsChecked = true;
                            }
                        }
                    }
                }
                else if(status == "Returning")
                {
                    if (state == "Undep")//for returning
                    {
                        int id = Convert.ToInt32(getRow(dg, 0));
                        int num = Convert.ToInt32(getRow(dg, 1));
                        using (var ctx = new finalContext())
                        {
                            FPaymentInfo fp = ctx.FPaymentInfo.Where(x => x.LoanID == id && x.PaymentNumber == num).First();

                            var ctr = ctx.ReturnedCheques.Where(x => x.FPaymentInfoID==fp.FPaymentInfoID).Count();

                            if (ctr > 0)
                            {
                                MessageBox.Show("Cheque has already been returned once", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                                return;
                            }

                            wpfChequeReturning frm = new wpfChequeReturning();
                            frm.fId = fp.FPaymentInfoID;
                            frm.UserID = UserID;
                            frm.ShowDialog();
                        }
                    }
                    else if (state == "Redep")//for voiding
                    {
                        int id = Convert.ToInt32(getRow(dg, 0));
                        int num = Convert.ToInt32(getRow(dg, 1));
                        using (var ctx = new finalContext())
                        {
                            FPaymentInfo fp = ctx.FPaymentInfo.Where(x => x.LoanID == id && x.PaymentNumber == num).First();
                            ReturnedCheque rc = ctx.ReturnedCheques.Find(fp.FPaymentInfoID);
                            fp.PaymentStatus = "Deposited";
                            ctx.ReturnedCheques.Remove(rc);
                            ctx.SaveChanges();
                            MessageBox.Show("Transaction has been successfully processed", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                //System.Windows.MessageBox.Show("Runtime Error: " + ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
                return;
            }
        }
        private void btnNew_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                using (var ctx = new finalContext())
                {
                    FPaymentInfo fp = ctx.FPaymentInfo.Find(fId);
                    DepositedCheque dp = ctx.DepositedCheques.Find(fId);
                    MessageBoxResult mr = MessageBox.Show("Are you sure you want to process this transaction?", "Question", MessageBoxButton.YesNo, MessageBoxImage.Question);
                    if (mr == MessageBoxResult.Yes)
                    {

                        if (status == "Full")
                        {
                            var fp2 = from f in ctx.FPaymentInfo
                                     where f.LoanID == lID && f.PaymentStatus != "Cleared"
                                     select f;
                            double tot = 0;
                            foreach (var itm in fp2)
                            {
                                itm.PaymentStatus = "Cleared";
                                ClearedCheque cc = new ClearedCheque { FPaymentInfoID = itm.FPaymentInfoID, DateCleared = DateTime.Now };
                            }

                            FPaymentInfo f2 = new FPaymentInfo { Amount = double.Parse(txtAmt.Text), ChequeDueDate = DateTime.Now.Date, ChequeInfo = txtId.Text, LoanID = lID, PaymentNumber = fp2.Count() + 1, PaymentStatus = "Cleared", PaymentDate = DateTime.Now, RemainingBalance = 0 };
                            ctx.FPaymentInfo.Add(f2);
                            var lon = ctx.Loans.Find(lID);
                            lon.Status = "Paid";
                            PaidLoan pl = new PaidLoan { DateFinished = DateTime.Now, LoanID = lID };
                            ctx.PaidLoans.Add(pl);
                            ctx.SaveChanges();
                            ClearedCheque cc2 = new ClearedCheque { DateCleared = DateTime.Now, FPaymentInfoID = f2.FPaymentInfoID };
                            MessageBox.Show("Transaction has been successfully processed", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                            this.Close();
                            return;
                        }


                        if (chDaif.IsChecked == false)
                        {
                            wpfCheckout frm = new wpfCheckout();
                            frm.status = "Daif";
                            frm.fId = fId;
                            frm.ShowDialog();

                            if (cont == false)
                            {
                                System.Windows.MessageBox.Show("Please pay the DAIF fee first", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
                                return;
                            }
                        }

                        AuditTrail at = new AuditTrail { EmployeeID = UserID, DateAndTime = DateTime.Now, Action = "Changed Cheque " + fp.ChequeInfo + " to " + txtId.Text + "" };
                        ctx.AuditTrails.Add(at);
                        fp.PaymentStatus = "Deposited";
                        fp.ChequeInfo = txtId.Text;
                        dp.DepositDate = DateTime.Today.Date;
                        

                        fp.ReturnedCheque.isPaid = true;


                        if (chDaif.IsChecked == true)
                        {
                            fp.Amount = fp.Amount + fp.ReturnedCheque.Fee;
                        }
                        ctx.SaveChanges();
                        MessageBox.Show("Transaction has been successfully processed", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                        this.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                System.Windows.MessageBox.Show("Runtime Error: " + ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
                return;
            }
        }
        private void btnRestructure_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                double max = 0;
                double min = 0;
                using (var ctx = new finalContext())
                {
                    var lon = ctx.Loans.Find(lId);
                    var ser = ctx.Services.Find(lon.ServiceID);
                    max = ser.MaxTerm;
                    min = ser.MinTerm;


                    if (Convert.ToDouble(txtTerm.Text) > max || Convert.ToDouble(txtTerm.Text) < min)
                    {
                        System.Windows.MessageBox.Show("Term must not be greater than the maximum term(" + ser.MaxTerm + " mo.) OR less than the minimum term(" + ser.MinTerm + " mo.)", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
                        return;
                    }

                    max = ser.MaxValue;
                    min = ser.MinValue;

                    //if (Convert.ToDouble(txtAmt.Text) > max || Convert.ToDouble(txtAmt.Text) < min)
                    //{
                    //    System.Windows.MessageBox.Show("Principal amount must not be greater than the maximum loanable amount OR less than the minimum loanable amount", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
                    //    return;
                    //}
                }

                if (Convert.ToDouble(txtAmt.Text) > Convert.ToDouble(lblPrincipal.Content))
                {
                    MessageBox.Show("Principal amount must not be greater than the maximum loanable amount", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
                    return;
                }

                foreach (var i in textarray)
                {
                    if (i.Text.Length != 6)
                    {
                        System.Windows.MessageBox.Show("Please input all cheque numbers", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                        return;
                    }
                    bool err;
                    int res;
                    String str = i.Text;
                    err = int.TryParse(str, out res);
                    if (err == false)
                    {
                        System.Windows.MessageBox.Show("Please input the correct format for cheque numbers(Strictly numbers only.)");
                        return;
                    }
                }

                for (int x = 0; x < textarray.Length; x++)
                {
                    for (int y = x + 1; y < textarray.Length; y++)
                    {
                        if (textarray[x].Text == textarray[y].Text)
                        {
                            System.Windows.MessageBox.Show("No duplications of cheque numbers", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                            return;
                        }
                    }
                }

                MessageBoxResult mr = MessageBox.Show("Are you sure you want to process this transaction?", "Question", MessageBoxButton.YesNo);
                if (mr == MessageBoxResult.Yes)
                {
                    using (var ctx = new finalContext())
                    {
                        wpfCheckout frm = new wpfCheckout();
                        var lon = ctx.Loans.Find(lId);
                        frm.lId = lId;
                        frm.status = "Restructure";
                        frm.lbl2.Content = (Convert.ToDouble(txtAmt.Text) * (lon.Service.RestructureFee / 100)).ToString("N2");
                        if(lon.Status == "Closed Account")
                        {
                            var cc = ctx.ClosedAccounts.Where(x=> x.LoanID==lId && x.isPaid == false).First();
                            frm.lbl2.Content = (Double.Parse(frm.lbl2.Content.ToString()) + cc.Fee).ToString("N2");
                        }
                        frm.ShowDialog();
                    }
                    if (cont != true)
                    {
                        MessageBox.Show("Please pay restructure fee and/or closed account fee first", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                        return;
                    }

                    using (var ctx = new finalContext())
                    {
                        var bk = ctx.Banks.Where(x => x.BankName == cmbBank.Text).First();
                        int bId = bk.BankID;
                        var lon = ctx.Loans.Find(lId);
                        if (lon.Status == "Closed Account")
                        {
                            var cc = ctx.ClosedAccounts.Where(x => x.LoanID == lId && x.isPaid == false).First();
                            cc.isPaid = true;
                        }
                        lon.Status = "Resturctured";
                        Loan l = new Loan { AgentID = lon.AgentID, CI = 0, Term = Convert.ToInt32(txtTerm.Text), Status = "Released", ServiceID = lon.ServiceID, Mode = cmbMode.Text, CoBorrower = lon.CoBorrower, ClientID = lon.ClientID, BankID = bId };
                        ReleasedLoan rl = new ReleasedLoan { LoanID = l.LoanID, AgentsCommission = 0, DateReleased = DateTime.Today.Date, MonthlyPayment = Convert.ToDouble(lblMonthly.Content), NetProceed = 0, Principal = 0, TotalLoan = Convert.ToDouble(lblInt.Content) };
                        //RestructuredLoan rln = new RestructuredLoan { LoanID = lId, NewLoanID = l.LoanID, DateRestructured = DateTime.Today, Fee = Convert.ToDouble(txtAmt.Text) * (lon.Service.RestructureFee / 100) };
                        var fp = from f in ctx.FPaymentInfo
                                 where f.PaymentStatus != "Cleared" && f.LoanID == lId
                                 select f;
                        foreach (var item in fp)
                        {
                            item.PaymentStatus = "Void";
                        }

                        var lo = from ly in ctx.GenSOA
                                 select ly;
                        int y = 0;
                        foreach (var item in lo)
                        {
                            FPaymentInfo fpy = new FPaymentInfo { PaymentNumber = item.PaymentNumber, Amount = Convert.ToDouble(item.Amount), ChequeInfo = textarray[y].Text, LoanID = l.LoanID, ChequeDueDate = item.PaymentDate, PaymentDate = item.PaymentDate, PaymentStatus = "Pending", RemainingBalance = Convert.ToDouble(item.RemainingBalance) };
                            ctx.FPaymentInfo.Add(fpy);
                            y++;
                        }

                        AuditTrail at = new AuditTrail { EmployeeID = UserID, DateAndTime = DateTime.Now, Action = "Processed Restructure for Loan " + lId + "" };
                        ctx.AuditTrails.Add(at);

                        ctx.Loans.Add(l);
                        ctx.ReleasedLoans.Add(rl);
                        ctx.SaveChanges();
                        RestructuredLoan rln = new RestructuredLoan { LoanID = lId, NewLoanID = l.LoanID, DateRestructured = DateTime.Today, Fee = Convert.ToDouble(txtAmt.Text) * (lon.Service.RestructureFee / 100) };
                        ctx.RestructuredLoans.Add(rln);

                        

                        ctx.SaveChanges();
                        printSOA();
                        MessageBox.Show("Transaction has been successfully processed", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                        this.Close();
                    }
                }
                

            }
            catch (Exception ex)
            {
                System.Windows.MessageBox.Show("Incorrect Format on some Fields / Incomplete Input(s)", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
                return;
            }
        }
        private void btnSave_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                if (txtPosition.Text == "Administrator")
                {
                    System.Windows.MessageBox.Show("Administrator cannot be used as position name", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
                    return;
                }


                if (status == "Add")
                {
                    using (var ctx = new finalContext())
                    {
                        var ctr = ctx.Positions.Where(x => x.PositionName == txtPosition.Text).Count();
                        if (ctr > 0)
                        {
                            System.Windows.MessageBox.Show("Position already exists.", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                            return;
                        }
                        Domain.Position pos = new Domain.Position { PositionName = txtPosition.Text, Description = txtDesc.Text };
                        PositionScope scp = new PositionScope { MAgent = false, MClient = false, MHoliday = false, MBank = false, MEmployee = false, MPosition = false, MRegistration = false, MService = false, TApplication = false, TApproval = false, TCollection = false, TManageClosed = false, TOnlineConfirmation = false, TPaymentAdjustment = false, TPayments = false, TReleasing = false, TResturcture = false, UArchive = false, UBackUp = false, UOnlineSettings = false, UReports = false, UScopes = false, UStatistics = false, UUserAccounts = false };
                        ctx.Positions.Add(pos);


                        AuditTrail at = new AuditTrail { EmployeeID = UserID, DateAndTime = DateTime.Now, Action = "Added new Position " + txtPosition.Text };
                        ctx.AuditTrails.Add(at);

                        ctx.SaveChanges();
                        scp.PositionID = pos.PositionID;
                        ctx.PositionScopes.Add(scp);
                        ctx.SaveChanges();
                        System.Windows.MessageBox.Show("Record has been successfully added", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                        this.Close();
                    }
                }
                else
                {
                    using (var ctx = new finalContext())
                    {
                        Domain.Position pos = ctx.Positions.Find(pID);
                        pos.PositionName = txtPosition.Text;
                        pos.Description = txtDesc.Text;
                        AuditTrail at = new AuditTrail { EmployeeID = UserID, DateAndTime = DateTime.Now, Action = "Updated Position " + txtPosition.Text };
                        ctx.AuditTrails.Add(at);
                        ctx.SaveChanges();
                        System.Windows.MessageBox.Show("Record has been successfully updated", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                        this.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                System.Windows.MessageBox.Show("Runtime Error: " + ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
                return;
            }
        }
 private void btnRet_Click(object sender, RoutedEventArgs e)
 {
     try
     {
         int n= Convert.ToInt32(getRow(dgEmp, 0));
         MessageBoxResult mr = System.Windows.MessageBox.Show("Are you sure you want to retrive this record?","Question",MessageBoxButton.YesNo);
         if (mr == MessageBoxResult.Yes)
         {
             using (var ctx = new finalContext())
             {
                 var agt = ctx.Agents.Find(n);
                 agt.Active = true;
                 AuditTrail at = new AuditTrail { EmployeeID = UserID, DateAndTime = DateTime.Now, Action = "Retrieved Agent " + agt.FirstName + " " + agt.MI + " " + agt.LastName + " " + agt.Suffix };
                 ctx.AuditTrails.Add(at);
                 ctx.SaveChanges();
                 System.Windows.MessageBox.Show("Record has been successfully Retrieved", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                 resetGrid();
             }
         }
     }
     catch (Exception ex)
     {
         System.Windows.MessageBox.Show("Runtime Error: " + ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
         return;
     }
 }
        private void btnNew_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                if (status == "PBC")
                {

                }
                else if (status == "Holding")
                {
                    if (Convert.ToDouble(txtCash.Text) < Convert.ToDouble(lbl2.Content))
                    {
                        MessageBox.Show("Invalid Cash Amount");
                        return;
                    }

                    using (var ctx = new finalContext())
                    {
                        MessageBoxResult mr = System.Windows.MessageBox.Show("Are you sure you want to process this transaction?", "Question", MessageBoxButton.YesNo, MessageBoxImage.Question);
                        if (mr == MessageBoxResult.Yes)
                        {
                            var dts = ctx.FPaymentInfo.Where(x => x.LoanID == lId && (x.PaymentStatus == "Pending" || x.PaymentStatus == "On Hold")).First();
                            double hFee = dts.Amount * (dts.Loan.Service.Holding / 100);
                            hFee = Convert.ToDouble(hFee.ToString("N2"));
                            HeldCheque hc = new HeldCheque { DateHeld = DateTime.Today.Date, LoanID = lId, NewPaymentDate = dts.PaymentDate.AddDays(7), OriginalPaymentDate = dts.PaymentDate, PaymentNumber = dts.PaymentNumber, HoldingFee = hFee };
                            dts.PaymentDate = dts.PaymentDate.AddDays(7);
                            dts.PaymentStatus = "On Hold";
                            ctx.HeldCheques.Add(hc);
                            ctx.SaveChanges();
                            //reset();
                            AuditTrail at = new AuditTrail { EmployeeID = UserID, DateAndTime = DateTime.Now, Action = "Processed Holding for cheque "+ dts.ChequeInfo +""};
                            ctx.AuditTrails.Add(at);
                            System.Windows.MessageBox.Show("Transaction has been successfully processed", "Information", MessageBoxButton.OK, MessageBoxImage.Information);

                            this.Close();
                        }
                    }
                }
                else if (status == "Adjustment")
                {
                    MessageBoxResult mr = MessageBox.Show("Are you sure you want to process this transaction?", "Question", MessageBoxButton.YesNo);
                    if (mr == MessageBoxResult.Yes)
                    {
                        using (var ctx = new finalContext())
                        {
                            AdjustedLoan al = new AdjustedLoan { DateAdjusted = DateTime.Today.Date, Days = Convert.ToInt32(days), Fee = Convert.ToDouble(lbl2.Content), LoanID = lId };
                            var py = from p in ctx.FPaymentInfo
                                     where p.PaymentStatus != "Cleared" && p.LoanID == lId
                                     select p;
                            foreach (var item in py)
                            {
                                item.PaymentDate = item.PaymentDate.AddDays(days);
                            }
                            ctx.AdjustedLoans.Add(al);
                            ctx.SaveChanges();
                            AuditTrail at = new AuditTrail { EmployeeID = UserID, DateAndTime = DateTime.Now, Action = "Processed Adjustment for Loan " + lId + "" };
                            ctx.AuditTrails.Add(at);

                            System.Windows.MessageBox.Show("Transaction has been successfully processed", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                            this.Close();
                        }
                    }
                }
                else if (status == "Restructure")
                {
                    MessageBoxResult mr = MessageBox.Show("Are you sure you want to process this transaction?", "Question", MessageBoxButton.YesNo);
                    if (mr == MessageBoxResult.Yes)
                    {
                        var ctr = Application.Current.Windows.Count;
                        var frm = Application.Current.Windows[ctr - 2] as wpfLoanRestructure;
                        frm.cont=true;
                        MessageBox.Show("Transaction has been successfully processed. Restructure will now be processed.", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                        this.Close();
                    }
                }
                else if (status == "Daif")
                {
                    var ctr = Application.Current.Windows.Count;
                    var frm = Application.Current.Windows[ctr - 2] as wpfNewCheque;
                    frm.cont = true;
                    MessageBox.Show("Transaction has been successfully processed. Renewal of Cheque will now be processed.", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                    this.Close();
                }
                else if (status == "RenewClosed")
                {
                    var ctr = Application.Current.Windows.Count;
                    var frm = Application.Current.Windows[ctr - 2] as wpfRenewClosed;
                    frm.cont = true;
                    MessageBox.Show("Transaction has been successfully processed. Renewal of Account will now be processed.", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                    this.Close();
                }
            }
            catch (Exception ex)
            {
                System.Windows.MessageBox.Show("Runtime Error: " + ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
                return;
            }
        }
        private void btnSave_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                if (cmbDept.Text == "Financing")
                {
                    if (lblName.Content == "?" || lblMinTerm.Content == "?" || lblMaxTerm.Content == "?" || lblMinVal.Content == "?" || lblMaxVal.Content == "?" || lblInterest.Content == "?" || lblDesc.Content == "?" || lblCom.Content == "?" || lblPenAdj.Content == "?" || lblPenCA.Content == "?" || lblPenDaif.Content == "?" || lblPenHolding.Content == "?" || lblPenIn.Content == "?"
                        || String.IsNullOrWhiteSpace(txtCom.Text) || String.IsNullOrWhiteSpace(txtHolding.Text) || String.IsNullOrWhiteSpace(txtDaif.Text) || String.IsNullOrWhiteSpace(txtClosed.Text) || String.IsNullOrWhiteSpace(txtResFee.Text) || String.IsNullOrWhiteSpace(txtResInt.Text) 
                        || String.IsNullOrWhiteSpace(txtName.Text) || String.IsNullOrWhiteSpace(txtMinTerm.Text) || String.IsNullOrWhiteSpace(txtMaxTerm.Text) || String.IsNullOrWhiteSpace(txtMinVal.Text) || String.IsNullOrWhiteSpace(txtMaxVal.Text) || String.IsNullOrWhiteSpace(txtInterest.Text) || String.IsNullOrWhiteSpace(txtDesc.Text))
                    {
                        System.Windows.MessageBox.Show("Please input correct format and/or fill all required fields", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                        return;
                    }
                }
                else
                {
                    if (lblName.Content == "?" || lblMinTerm.Content == "?" || lblMaxTerm.Content == "?" || lblMinVal.Content == "?" || lblMaxVal.Content == "?" || lblInterest.Content == "?" || lblDesc.Content == "?" || lblCom.Content == "?" || lblPenLate.Content == "?"
                        || String.IsNullOrWhiteSpace(txtLtPen.Text) || String.IsNullOrWhiteSpace(txtName.Text) || String.IsNullOrWhiteSpace(txtMinTerm.Text) || String.IsNullOrWhiteSpace(txtMaxTerm.Text) || String.IsNullOrWhiteSpace(txtMinVal.Text) || String.IsNullOrWhiteSpace(txtMaxVal.Text) || String.IsNullOrWhiteSpace(txtInterest.Text) || String.IsNullOrWhiteSpace(txtDesc.Text))
                    {
                        System.Windows.MessageBox.Show("Please input correct format and/or fill all required fields", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                        return;
                    }
                }

                if (txtName.Text == "" || txtDesc.Text == "" || txtInterest.Text == "" || txtMaxTerm.Text == "" || txtMinVal.Text == "" || txtMinTerm.Text == "" || cmbDept.Text == "" || cmbType.Text == "")
                {
                    return;
                }

                if (Convert.ToDouble(txtMaxVal.Text) < 1 || Convert.ToDouble(txtMinVal.Text) < 1 || Convert.ToDouble(txtMaxTerm.Text) < 1 || Convert.ToDouble(txtMinTerm.Text) < 0)
                {
                    System.Windows.MessageBox.Show("Terms and Values must be greater thant one(1)", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                    return;
                }


                if (status == "Add")
                {


                    using (var ctx = new finalContext())
                    {
                        Service ser = null;
                        if (cmbDept.Text == "Financing")
                        {
                            ser = new Service { Name = txtName.Text, Department = cmbDept.Text, Description = txtDesc.Text, Type = cmbType.Text, Active = true, Interest = Convert.ToDouble(txtInterest.Text), MinTerm = Convert.ToInt32(txtMinTerm.Text), MaxTerm = Convert.ToInt32(txtMaxTerm.Text), MinValue = Convert.ToDouble(txtMinVal.Text), MaxValue = Convert.ToDouble(txtMaxVal.Text), AgentCommission = Convert.ToDouble(txtCom.Text), Holding = Convert.ToDouble(txtHolding.Text), ClosedAccountPenalty = Convert.ToDouble(txtClosed.Text), DaifPenalty = Convert.ToDouble(txtDaif.Text), RestructureFee = Convert.ToDouble(txtResFee.Text), RestructureInterest = Convert.ToDouble(txtResInt.Text), AdjustmentFee = Convert.ToDouble(txtAdjust.Text), LatePaymentPenalty=0 };
                        }
                        else
                        {
                            ser = new Service { Name = txtName.Text, Department = cmbDept.Text, Description = txtDesc.Text, Type = cmbType.Text, Active = true, Interest = Convert.ToDouble(txtInterest.Text), MinTerm = Convert.ToInt32(txtMinTerm.Text), MaxTerm = Convert.ToInt32(txtMaxTerm.Text), MinValue = Convert.ToDouble(txtMinVal.Text), MaxValue = Convert.ToDouble(txtMaxVal.Text), AgentCommission = Convert.ToDouble(txtCom.Text), Holding = 0, ClosedAccountPenalty = 0, DaifPenalty = 0, RestructureFee = 0, RestructureInterest = 0, AdjustmentFee = 0, LatePaymentPenalty=Convert.ToDouble(txtLtPen.Text) };
                        }
                        var deds = from dd in ctx.TempoDeductions
                                   select new { DedNumber = dd.DeductionNum, Name = dd.Name, Percentage = dd.Percentage };
                        var rqs = from rq in ctx.TempoRequirements
                                  select new { ReqNumber = rq.RequirementNum, Name = rq.Name, Description = rq.Description };

                        foreach (var item in deds)
                        {
                            Deduction dd = new Deduction { DeductionNum = item.DedNumber, Name = item.Name, Percentage = item.Percentage };
                            ctx.Deductions.Add(dd);
                        }

                        foreach (var item in rqs)
                        {
                            Requirement rr = new Requirement { RequirementNum = item.ReqNumber, Name = item.Name, Description = item.Description };
                            ctx.Requirements.Add(rr);
                        }

                        ComboBoxItem typeItem = (ComboBoxItem)cmbType.SelectedItem;
                        string value = typeItem.Content.ToString();
                        if (value == "Collateral")
                        {
                            var ci = from c in ctx.TempCollateralInformations
                                     select c;

                            foreach (var itm in ci)
                            {
                                CollateralInformation cl = new CollateralInformation { CollateralInformationNum = itm.TempCollateralInformationNum, Field = itm.Field };
                                ctx.CollateralInformations.Add(cl);
                            }
                        }

                        AuditTrail at = new AuditTrail { EmployeeID = UserID, DateAndTime = DateTime.Now, Action = "Added new Service " + txtName.Text };
                        ctx.AuditTrails.Add(at);

                        ctx.Services.Add(ser);
                        ctx.SaveChanges();
                        MessageBox.Show("Service has been successfully added", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                        this.Close();
                    }
                }
                else
                {
                    using (var ctx = new finalContext())
                    {
                        var ser = ctx.Services.Find(sId);
                        ser.Name = txtName.Text;
                        ser.Department = cmbDept.Text;
                        ser.Description = txtDesc.Text;
                        ser.Type = cmbType.Text;
                        ser.Interest = Convert.ToDouble(txtInterest.Text);
                        ser.MinTerm = Convert.ToInt32(txtMinTerm.Text);
                        ser.MaxTerm = Convert.ToInt32(txtMaxTerm.Text);
                        ser.MinValue = Convert.ToDouble(txtMinVal.Text);
                        ser.MaxValue = Convert.ToDouble(txtMaxVal.Text);
                        if (cmbDept.Text == "Financing")
                        {
                            ser.Holding = Convert.ToDouble(txtHolding.Text);
                            ser.DaifPenalty = Convert.ToDouble(txtDaif.Text);
                            ser.ClosedAccountPenalty = Convert.ToDouble(txtClosed.Text);
                            ser.AgentCommission = Convert.ToDouble(txtCom.Text);
                            ser.RestructureFee = Convert.ToDouble(txtResFee.Text);
                            ser.RestructureInterest = Convert.ToDouble(txtResInt.Text);
                            ser.AdjustmentFee = Convert.ToDouble(txtAdjust.Text);
                            ser.LatePaymentPenalty = 0;
                        }
                        else
                        {
                            ser.Holding = 0;
                            ser.DaifPenalty = 0;
                            ser.ClosedAccountPenalty = 0;
                            ser.AgentCommission = 0;
                            ser.RestructureFee = 0;
                            ser.RestructureInterest = 0;
                            ser.AdjustmentFee = 0;
                            ser.LatePaymentPenalty = Convert.ToDouble(txtLtPen.Text);
                        }

                        AuditTrail at = new AuditTrail { EmployeeID = UserID, DateAndTime = DateTime.Now, Action = "Updated Service " + txtName.Text };
                        ctx.AuditTrails.Add(at);
                        ctx.SaveChanges();
                        MessageBox.Show("Service has been successfully updated", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                        this.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                System.Windows.MessageBox.Show("Runtime Error: " + ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
                return;
            }
        }
        private void btnSave_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                if (lblFName.Content == "?" || lblLName.Content == "?" || lblMI.Content == "?" || lblEmail.Content == "?" || lblSuffix.Content == "?" || lblEmail.Content == "?"
                    || String.IsNullOrWhiteSpace(txtFName.Text) || String.IsNullOrWhiteSpace(txtLName.Text) || String.IsNullOrWhiteSpace(cmbPosition.Text) || String.IsNullOrWhiteSpace(cmbDept.Text))
                {
                    System.Windows.MessageBox.Show("Please input correct format and/or fill all required fields", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                    return;
                }

                if (status == "Add")
                {

                    DialogResult dr = System.Windows.Forms.MessageBox.Show("Are you sure you want to add this record?", "Question", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
                    if (dr == System.Windows.Forms.DialogResult.No)
                    {
                        return;
                    }

                    if (txtFName.Text == "" || txtLName.Text == "" || cmbPosition.Text == "" || cmbDept.Text == "")
                    {
                        System.Windows.MessageBox.Show("Please complete the required information", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
                        return;
                    }

                    if (!Regex.IsMatch(txtFName.Text, @"^[a-zA-Z]+$"))
                    { 
                        
                    }
                    

                    using (var ctx = new finalContext())
                    {
                        int pID = ctx.Positions.Where(x=>x.PositionName==cmbPosition.Text).First().PositionID;
                        Employee emp = new Employee { FirstName = txtFName.Text, LastName = txtLName.Text, PositionID = pID, Suffix = txtSuffix.Text, MI = txtMI.Text, Active = true, Department = cmbDept.Text, Email = txtEmail.Text, Photo = ConvertImageToByteArray(selectedFileName) };

                        var query = from con in ctx.TempAdresses
                                    select con;
                        foreach (var item in query)
                        {
                            EmployeeAddress add = new EmployeeAddress { Street = item.Street, City = item.City, Province = item.Province };
                            ctx.EmployeeAddresses.Add(add);
                        }

                        var query1 = from con in ctx.TempContacts
                                     select con;
                        foreach (var item in query1)
                        {
                            EmployeeContact con = new EmployeeContact { Contact = item.Contact };
                            ctx.EmployeeContacts.Add(con);
                        }
                        AuditTrail at = new AuditTrail { EmployeeID = UserID, DateAndTime = DateTime.Now, Action = "Added new Employee " + txtFName.Text + " " + txtMI.Text + " " + txtLName.Text + " " + txtSuffix.Text };
                        ctx.AuditTrails.Add(at);

                        ctx.Employees.Add(emp);
                        ctx.SaveChanges();
                        System.Windows.MessageBox.Show("Employee has been successfully added", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                        this.Close();


                    }
                }
                else
                {

                    DialogResult dr = System.Windows.Forms.MessageBox.Show("Are you sure you want to update this record?", "Question", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
                    if (dr == System.Windows.Forms.DialogResult.No)
                    {
                        return;
                    }
                    using (var ctx = new finalContext())
                    {
                        int pID = ctx.Positions.Where(x => x.PositionName == cmbPosition.Text).First().PositionID;
                        var emp = ctx.Employees.Find(uId);
                        emp.FirstName = txtFName.Text;
                        emp.LastName = txtLName.Text;
                        emp.PositionID = pID;
                        emp.Suffix = txtSuffix.Text;
                        emp.MI = txtMI.Text;
                        emp.Department = cmbDept.Text;
                        emp.Email = txtEmail.Text;
                        if (isChanged == true)
                        {
                            emp.Photo = ConvertImageToByteArray(selectedFileName);
                        }
                        AuditTrail at = new AuditTrail { EmployeeID = UserID, DateAndTime = DateTime.Now, Action = "Updated Employee " + txtFName.Text + " " + txtMI.Text + " " + txtLName.Text + " " + txtSuffix.Text };
                        ctx.AuditTrails.Add(at);
                        ctx.SaveChanges();
                        System.Windows.MessageBox.Show("Employee has been successfully updated", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                        this.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                System.Windows.MessageBox.Show("Runtime Error: " + ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
                return;
            }
        }
        private void btnRelease_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                if (status == "Releasing" || status == "Renewal")
                {
                    double max = 0;
                    double min = 0;
                    using (var ctx = new finalContext())
                    {
                        var lon = ctx.Loans.Find(lId);
                        var ser = ctx.Services.Find(lon.ServiceID);
                        max = ser.MaxTerm;
                        min = ser.MinTerm;


                        if (Convert.ToDouble(txtTerm.Text) > max || Convert.ToDouble(txtTerm.Text) < min)
                        {
                            System.Windows.MessageBox.Show("Term must not be greater than the maximum term(" + ser.MaxTerm + " mo.) OR less than the minimum term(" + ser.MinTerm + " mo.)", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
                            return;
                        }

                        max = ser.MaxValue;
                        min = ser.MinValue;

                        if (Convert.ToDouble(txtAmt.Text) > max || Convert.ToDouble(txtAmt.Text) < min)
                        {
                            System.Windows.MessageBox.Show("Principal amount must not be greater than the maximum loanable amount OR less than the minimum loanable amount", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
                            return;
                        }
                    }

                    if (Convert.ToDouble(txtAmt.Text) > Convert.ToDouble(lblPrincipal.Content) && status=="Releasing")
                    {
                        MessageBox.Show("Principal amount must not be greater than the maximum loanable amount", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
                        return;
                    }

                    foreach (var i in textarray)
                    {
                        if (i.Text.Length != 6)
                        {
                            System.Windows.MessageBox.Show("Please input all cheque numbers", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                            return;
                        }
                        bool err;
                        int res;
                        String str = i.Text;
                        err = int.TryParse(str, out res);
                        if (err == false)
                        {
                            System.Windows.MessageBox.Show("Please input the correct format for cheque numbers(Strictly numbers only.)", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                            return;
                        }
                    }

                    for (int x = 0; x < textarray.Length; x++)
                    {
                        for (int y = x + 1; y < textarray.Length; y++)
                        {
                            if (textarray[x].Text == textarray[y].Text)
                            {
                                System.Windows.MessageBox.Show("No duplications of cheque numbers", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                                return;
                            }
                        }
                    }

                    MessageBoxResult mr = MessageBox.Show("Are you sure you want to process this transaction?", "Question", MessageBoxButton.YesNo, MessageBoxImage.Question);
                    if (mr == MessageBoxResult.Yes)
                    {
                        if (status == "Renewal")
                        {
                            using (var ctx = new finalContext())
                            {
                                var bk = ctx.Banks.Where(x => x.BankName == cmbBank.Text).First();
                                int bId = bk.BankID;
                                var rn = ctx.LoanRenewals.Where(x => x.newLoanID == lId).First();
                                var lon = ctx.Loans.Find(rn.LoanID);
                                lon.Status = "Paid";
                                int tlID = lon.LoanID;
                                var pys = from p in ctx.FPaymentInfo
                                          where p.LoanID == rn.LoanID
                                          select p;
                                foreach (var itm in pys)
                                {
                                    itm.PaymentStatus = "Cleared";
                                }
                                lon = ctx.Loans.Find(lId);
                                lon.Status = "Released";
                                var cn = ctx.Services.Find(lon.ServiceID);
                                double co = cn.AgentCommission / 100;
                                double cm = Convert.ToDouble(txtAmt.Text) * co;
                                ReleasedLoan rl = new ReleasedLoan { LoanID=lId, AgentsCommission = cm, DateReleased = DateTime.Now.Date, MonthlyPayment = Convert.ToDouble(lblMonthly.Content), NetProceed = Convert.ToDouble(lblProceed.Content), Principal = Convert.ToDouble(txtAmt.Text), TotalLoan = Convert.ToDouble(lblInt.Content) };
                                var lo = from l in ctx.GenSOA
                                         select l;
                                int y = 0;
                                ctx.ReleasedLoans.Add(rl);
                                ctx.SaveChanges();
                                var inf = from i in ctx.CollateralLoanInfoes
                                          where i.LoanID == tlID
                                          select i;

                                foreach (var itm in inf)
                                {
                                    CollateralLoanInfo ci = new CollateralLoanInfo { CollateralInformationID = itm.CollateralInformationID, LoanID = lId, Value = itm.Value };
                                    ctx.CollateralLoanInfoes.Add(ci);
                                }

                                foreach (var item in lo)
                                {
                                    FPaymentInfo fp = new FPaymentInfo { PaymentNumber = item.PaymentNumber, Amount = Convert.ToDouble(item.Amount), ChequeInfo = textarray[y].Text, LoanID = lId, ChequeDueDate = item.PaymentDate, PaymentDate = item.PaymentDate, PaymentStatus = "Pending", RemainingBalance = Convert.ToDouble(item.RemainingBalance) };
                                    ctx.FPaymentInfo.Add(fp);
                                    y++;
                                }
                                AuditTrail at = new AuditTrail { EmployeeID = UserID, DateAndTime = DateTime.Now, Action = "Released loan renewal (" + lon.Service.Name + ") for client " + lon.Client.FirstName + " " + lon.Client.MiddleName + " " + lon.Client.LastName + " " + lon.Client.Suffix };
                                ctx.AuditTrails.Add(at);
                                ctx.SaveChanges();
                                printSOA();
                                MessageBox.Show("Transaction has been successfully processed", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                                this.Close();
                            }
                        }
                        else
                        {
                            using (var ctx = new finalContext())
                            {
                                var bk = ctx.Banks.Where(x => x.BankName == cmbBank.Text).First();
                                int bId = bk.BankID;
                                var lon = ctx.Loans.Find(lId);
                                lon.Status = "Released";
                                lon.BankID = bId;
                                lon.Mode = cmbMode.Text;
                                lon.Term = Convert.ToInt32(txtTerm.Text);
                                var cn = ctx.Services.Find(lon.ServiceID);
                                double co = cn.AgentCommission / 100;
                                double cm = Convert.ToDouble(txtAmt.Text) * co;
                                //MessageBox.Show(cm.ToString());
                                ReleasedLoan rl = new ReleasedLoan { AgentsCommission = cm, DateReleased = DateTime.Today.Date, LoanID = lId, MonthlyPayment = Convert.ToDouble(lblMonthly.Content), NetProceed = Convert.ToDouble(lblProceed.Content), Principal = Convert.ToDouble(txtAmt.Text), TotalLoan = Convert.ToDouble(lblInt.Content) };
                                lon.ReleasedLoan = rl;
                                var lo = from l in ctx.GenSOA
                                         select l;
                                int y = 0;
                                foreach (var item in lo)
                                {
                                    FPaymentInfo fp = new FPaymentInfo { PaymentNumber = item.PaymentNumber, Amount = Convert.ToDouble(item.Amount), ChequeInfo = textarray[y].Text, LoanID = lId, ChequeDueDate = item.PaymentDate, PaymentDate = item.PaymentDate, PaymentStatus = "Pending", RemainingBalance = Convert.ToDouble(item.RemainingBalance) };
                                    ctx.FPaymentInfo.Add(fp);
                                    y++;
                                }
                                AuditTrail at = new AuditTrail { EmployeeID = UserID, DateAndTime = DateTime.Now, Action = "Released loan (" + lon.Service.Name + ") for client " + lon.Client.FirstName + " " + lon.Client.MiddleName + " " + lon.Client.LastName + " " + lon.Client.Suffix };
                                ctx.AuditTrails.Add(at);
                                ctx.SaveChanges();
                                printSOA();
                                MessageBox.Show("Transaction has been successfully processed", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                                this.Close();
                            }
                        }
                    }
                }
                else if (status == "UReleasing")
                {
                    int myCtr = 0;
                    using (var ctx = new finalContext())
                    {
                        var lons = from lo in ctx.FPaymentInfo
                                   where lo.LoanID == lId
                                   select lo;
                        foreach (var item in lons)
                        {
                            item.ChequeInfo = textarray[myCtr].Text;
                            myCtr++;
                        }
                        var lon = ctx.Loans.Find(lId);
                        var bk = ctx.Banks.Where(x => x.BankName == cmbBank.Text).First();
                        int bId = bk.BankID;
                        lon.BankID = bId;

                        AuditTrail at = new AuditTrail { EmployeeID = UserID, DateAndTime = DateTime.Now, Action = "Updated Released loan (" + lon.Service.Name + ") for client " + lon.Client.FirstName + " " + lon.Client.MiddleName + " " + lon.Client.LastName + " " + lon.Client.Suffix };
                        ctx.AuditTrails.Add(at);
                        ctx.SaveChanges();
                        MessageBox.Show("Transaction has been successfully updated", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                        this.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                System.Windows.MessageBox.Show("Incorrect Format on some Fields / Incomplete Input(s)", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
                return;
            }
        }
        private void btnView_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                if (status == "Application")
                {
                    wpfLoanApplication frm = new wpfLoanApplication();
                    frm.status = "Edit";
                    int id = Convert.ToInt32(getRow(dgLoan, 0));
                    frm.lId = id;
                    frm.UserID = UserID;
                    frm.btnContinue.Content = "Update";
                    frm.ShowDialog();
                }
                else if (status == "Renewal Application")
                {
                    wpfLoanApplication frm = new wpfLoanApplication();
                    frm.status = "Renewal";
                    int id = Convert.ToInt32(getRow(dgLoan, 0));
                    using (var ctx = new finalContext())
                    {
                        var ctr = ctx.LoanRenewals.Where(x => x.LoanID == id && (x.Status == "Pending" || x.Status == "Approved")).Count();
                        if (ctr > 0)
                        {
                            System.Windows.MessageBox.Show("There is an existing application for this loan","Error", MessageBoxButton.OK, MessageBoxImage.Error);
                            return;
                        }
                    }
                    frm.lId = id;
                    frm.UserID = UserID;
                    frm.btnContinue.Content = "Renew";
                    frm.ShowDialog();
                }
                else if (status == "Confirmation")
                {
                    using (var ctx = new finalContext())
                    {
                        int lID = Convert.ToInt32(getRow(dgLoan, 0));
                        var clt = ctx.TemporaryLoanApplications.Where(x => x.TemporaryLoanApplicationID == lID).First();
                        int cid = clt.ClientID;
                        var ctr = ctx.Clients.Where(x => x.ClientID == cid).Count();
                        if (ctr == 0)
                        {
                            MessageBox.Show("Client doesn't exists");
                            return;
                        }

                        if (clt.Client.isConfirmed == false)
                        {
                            System.Windows.MessageBox.Show("Client is not yet confirmed. Please confirm the client first", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
                            return;
                        }

                        var ictr = ctx.Loans.Where(x => x.ClientID == cid && x.Status == "Released").Count();
                        if (ictr > 0)
                        {
                            ictr = ctx.Loans.Where(x => x.ClientID == cid && x.Status == "Released" && x.Service.Department != iDept).Count();
                            if (ictr > 0)
                            {
                                System.Windows.MessageBox.Show("Client has an existing loan on other department", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
                                return;
                            }
                            else
                            {
                                ictr = ctx.Loans.Where(x => x.ClientID == cid && x.Status == "Released" && x.Service.Type == "Non Collateral" && x.Service.Department == iDept).Count();
                                if (ictr > 0)
                                {
                                    System.Windows.MessageBox.Show("Client has an existing Non-Collateral loan. The client can only apply for collateral loan.", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                                }
                            }
                        }

                        ictr = ctx.Loans.Where(x => x.ClientID == cid && (x.Status == "Applied" || x.Status == "Approved")).Count();
                        if (ictr > 0)
                        {
                            System.Windows.MessageBox.Show("Client cannot have multiple applications at the same time", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
                            return;
                        }

                        ictr = ctx.Loans.Where(x => x.ClientID == cid && x.Status == "Under Collection").Count();
                        if (ictr > 0)
                        {
                            System.Windows.MessageBox.Show("Client cannot have another application while having a loan under collection", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
                            return;
                        }
                        wpfLoanApplication frm = new wpfLoanApplication();
                        frm.cId = Convert.ToInt32(clt.ClientID);
                        frm.status = "Confirmation";
                        frm.btnContinue.Content = "Confirm";
                        frm.iDept = iDept;
                        frm.UserID = UserID;
                        frm.lId = lID;
                        this.Close();
                        frm.ShowDialog();
                        this.Close();
                    }
                }
                else if (status == "UReleasing")
                {
                    wpfReleasedLoanInfo frm = new wpfReleasedLoanInfo();
                    int num = Convert.ToInt32(getRow(dgLoan, 0));
                    frm.lId = num;
                    frm.UserID = UserID;
                    frm.status = status;
                    frm.iDept = iDept;
                    frm.ShowDialog();
                }
                else if (status == "Full")
                {
                    wpfNewCheque frm = new wpfNewCheque();
                    int num = Convert.ToInt32(getRow(dgLoan, 0));
                    frm.lID = num;
                    frm.UserID = UserID;
                    frm.status = status;
                    frm.ShowDialog();
                }
                else if (status == "Holding")
                {
                    wpfReleasedLoanInfo frm = new wpfReleasedLoanInfo();
                    int num = Convert.ToInt32(getRow(dgLoan, 0));
                    frm.lId = num;
                    frm.UserID = UserID;
                    frm.status = status;
                    frm.ShowDialog();
                }
                else if (status == "Voiding")
                {
                    if (iDept == "Financing")
                    {
                        using (var ctx = new finalContext())
                        {
                            int n = Convert.ToInt32(getRow(dgLoan, 0));
                            int num = ctx.FPaymentInfo.Where(x => x.LoanID == n && x.PaymentStatus == "Deposited").Count();
                            if (num > 0)
                            {
                                MessageBox.Show("Unable to void payment due to deposited cheque", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                                return;
                            }
                            num = ctx.FPaymentInfo.Where(x => x.LoanID == n && x.PaymentStatus == "Cleared").Count();
                            if (num > 0)
                            {
                                var fp1 = from f in ctx.FPaymentInfo
                                          where f.LoanID == n && f.PaymentStatus == "Cleared"
                                          select f;
                                int x = 0;
                                FPaymentInfo fp = null;
                                foreach (var item in fp1)
                                {
                                    if (x == num - 1)
                                    {
                                        fp = item;
                                    }
                                    x++;
                                }
                                //var fp = ctx.FPaymentInfo.Where(x => x.LoanID == n && x.PaymentStatus == "Cleared").Last();
                                MessageBox.Show("The last payment has the following info: \n No :" + fp.PaymentNumber + " \n ChequeNumber: " + fp.ChequeInfo + " \n Amount: " + fp.Amount.ToString("N2") + "","Information",MessageBoxButton.OK, MessageBoxImage.Information);
                                MessageBoxResult mr = MessageBox.Show("Are you sure you want to process this transaction?", "Question", MessageBoxButton.YesNo, MessageBoxImage.Question);
                                if (mr == MessageBoxResult.Yes)
                                {
                                    wpfPassword frm = new wpfPassword();
                                    frm.status = "void";
                                    frm.ID = UserID;
                                    frm.ShowDialog();

                                    if (cont == false)
                                    {
                                        MessageBox.Show("Please re-enter you password", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                                        return;
                                    }   
                                    fp.PaymentStatus = "Deposited";
                                    ClearedCheque cc = ctx.ClearedCheques.Find(fp.ClearCheque.FPaymentInfoID);
                                    ctx.ClearedCheques.Remove(cc);
                                    AuditTrail at = new AuditTrail { EmployeeID = UserID, DateAndTime = DateTime.Now, Action = "Voided Payment for Cheque " + fp.ChequeInfo };
                                    ctx.AuditTrails.Add(at);
                                    ctx.SaveChanges();
                                    MessageBox.Show("Transaction has been successfully processed", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                                }
                            }
                            else
                            {
                                MessageBox.Show("No payments to void", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                            }
                        }
                    }
                    else
                    {
                        using (var ctx = new finalContext())
                        {
                            int n = Convert.ToInt32(getRow(dgLoan, 0));
                            int num = ctx.MPaymentInfoes.Where(x => x.LoanID == n && x.PaymentStatus == "Paid").Count();
                            if (num > 0)
                            {
                                var fp1 = from f in ctx.MPaymentInfoes
                                          where f.LoanID == n && f.PaymentStatus == "Paid"
                                          select f;
                                int x = 0;
                                MPaymentInfo fp = null;
                                foreach (var item in fp1)
                                {
                                    if (x == num - 1)
                                    {
                                        fp = item;
                                    }
                                    x++;
                                }
                                //var fp = ctx.FPaymentInfo.Where(x => x.LoanID == n && x.PaymentStatus == "Cleared").Last();
                                var mp = ctx.MPaymentInfoes.Where(m => m.LoanID == n && m.PaymentNumber == fp.PaymentNumber).First();
                                MessageBox.Show("The last payment has the following info: \n No :" + mp.PaymentNumber + "\n Total Payment: " + mp.TotalPayment.ToString("N2") + "");
                                MessageBoxResult mr = MessageBox.Show("Are you sure you want to process this transaction?", "Question", MessageBoxButton.YesNo, MessageBoxImage.Question);
                                if (mr == MessageBoxResult.Yes)
                                {
                                    wpfPassword frm = new wpfPassword();
                                    frm.status = "void";
                                    frm.ID = UserID;
                                    frm.ShowDialog();

                                    if (cont == false)
                                    {
                                        MessageBox.Show("Please re-enter you password", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                                        return;
                                    }

                                    try
                                    {
                                        var m2 = ctx.MPaymentInfoes.Where(m => m.LoanID == n && m.PaymentStatus == "Pending").First();
                                        ctx.MPaymentInfoes.Remove(m2);
                                    }
                                    catch (Exception)
                                    { 
                                        
                                    }
                                    mp.PaymentStatus = "Pending";
                                    mp.TotalPayment=0;
                                    mp.PaymentDate = null;

                                    x=1;
                                    var mps = from m in ctx.MPaymentInfoes
                                              where m.PaymentNumber == mp.PaymentNumber
                                              select m;

                                    foreach (var itm in mps)
                                    { 
                                        if(x!=1)
                                            ctx.MPaymentInfoes.Remove(itm);
                                        x++;
                                    }
                                    var lon = ctx.Loans.Find(n);
                                    if (lon.Status == "Paid")
                                    {
                                        lon.Status = "Released";
                                        var pl = ctx.PaidLoans.Find(n);
                                        ctx.PaidLoans.Remove(pl);
                                    }
                                    
                                    AuditTrail at = new AuditTrail { EmployeeID = UserID, DateAndTime = DateTime.Now, Action = "Voided Payment for Payment Number " + fp.PaymentNumber + " for Loan " + n };
                                    ctx.AuditTrails.Add(at);
                                    ctx.SaveChanges();
                                    MessageBox.Show("Transaction has been successfully processed", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                                }
                            }
                            else
                            {
                                MessageBox.Show("No payments to void", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                            }
                        }
                    }
                }
                else if (status == "Voiding2")
                {
                    using (var ctx = new finalContext())
                    {
                        int n = Convert.ToInt32(getRow(dgLoan, 0));
                        int num = ctx.CollectionInfoes.Where(x => x.LoanID == n).Count();
                        if (num > 0)
                        {
                            var fp1 = from f in ctx.CollectionInfoes
                                      where f.LoanID == n
                                      select f;
                            int x = 0;
                            CollectionInfo fp = null;
                            foreach (var item in fp1)
                            {
                                if (x == num - 1)
                                {
                                    fp = item;
                                }
                                x++;
                            }
                            MessageBox.Show("The last payment has the following info: \n Total Collection :" + fp.TotalCollection + "\n Collection Date: " + fp.DateCollected.ToString().Split(' ')[0] + "");
                            MessageBoxResult mr = MessageBox.Show("Are you sure you want to process this transaction?", "Question", MessageBoxButton.YesNo, MessageBoxImage.Question);
                            if (mr == MessageBoxResult.Yes)
                            {

                                var r = ctx.PassedToCollectors.Find(fp.LoanID);
                                r.RemainingBalance = r.RemainingBalance + fp.TotalCollection;
                                ctx.CollectionInfoes.Remove(fp);
                                AuditTrail at = new AuditTrail { EmployeeID = UserID, DateAndTime = DateTime.Now, Action = "Voided Collection for Loan " + n };
                                ctx.AuditTrails.Add(at);
                                ctx.SaveChanges();
                                MessageBox.Show("Transaction has been successfully processed", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                            }
                        }
                        else
                        {
                            MessageBox.Show("No payments to void", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                        }
                    }
                }
                else if (status == "VoidClosed")
                {
                    int n = Convert.ToInt32(getRow(dgLoan, 0));
                    MessageBoxResult mr = MessageBox.Show("Are you sure you want to process this transaction?", "Question", MessageBoxButton.YesNo);
                    if (mr == MessageBoxResult.Yes)
                    {

                        using (var ctx = new finalContext())
                        {
                            var lon = ctx.Loans.Find(n);
                            lon.Status = "Released";
                            var fp = ctx.ClosedAccounts.Where(x => x.LoanID == n && x.isPaid == false).First();
                            ctx.ClosedAccounts.Remove(fp);
                            var fp2 = from f in ctx.FPaymentInfo
                                      where f.LoanID == n && f.PaymentStatus != "Cleared"
                                      select f;
                            int myC = 0;
                            foreach (var item in fp2)
                            {
                                if (myC == 0)
                                {
                                    item.PaymentStatus = "Deposited";
                                }
                                else
                                {
                                    item.PaymentStatus = "Pending";
                                }
                                myC++;
                            }
                            AuditTrail at = new AuditTrail { EmployeeID = UserID, DateAndTime = DateTime.Now, Action = "Voided Closed Account for Loan " + lon.LoanID };
                            ctx.AuditTrails.Add(at);
                            ctx.SaveChanges();
                            MessageBox.Show("Transaction has been successfully processed", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                            return;
                        }
                    }
                }
                else if (status == "Pass")
                {
                    int n = Convert.ToInt32(getRow(dgLoan, 0));
                    wpfPassToCollectors frm = new wpfPassToCollectors();
                    frm.n = n;
                    frm.UserID = UserID;
                    this.Close();
                    frm.ShowDialog();
                }
                else if (status == "Renewal")
                {
                    int n = Convert.ToInt32(getRow(dgLoan, 0));
                    wpfRenewClosed frm = new wpfRenewClosed();
                    frm.UserID = UserID;
                    frm.lId = n;
                    this.Close();
                    frm.ShowDialog();
                }
                else if (status == "Adjustment" || status == "Restructure" || status == "View")
                {
                    if (status == "View")
                    {
                        using (var ctx = new finalContext())
                        {
                            int n = Convert.ToInt32(getRow(dgLoan, 0));
                            var lon = ctx.Loans.Find(n);
                            if (lon.Status == "Applied" || lon.Status == "Declined" || lon.Status == "Approved")
                            {
                                wpfAppliedLoanInfo frm = new wpfAppliedLoanInfo();
                                frm.lId = n;
                                frm.status = "View";
                                frm.UserID = UserID;
                                frm.Height = 605.5;
                                frm.ShowDialog();
                            }
                            else
                            {
                                wpfReleasedLoanInfo frm = new wpfReleasedLoanInfo();
                                frm.lId = n;
                                frm.status = "View";
                                frm.Height = 605.5;
                                frm.UserID = UserID;
                                frm.ShowDialog();
                            }
                        }
                    }
                    else
                    {
                        int n = Convert.ToInt32(getRow(dgLoan, 0));
                        wpfReleasedLoanInfo frm = new wpfReleasedLoanInfo();
                        frm.lId = n;
                        frm.UserID = UserID;
                        frm.status = status;
                        if (status == "View")
                        {
                            frm.Height = 605.5;
                        }
                        this.Close();
                        frm.ShowDialog();
                    }
                }
                else if (status == "Aging")
                {
                    wpfLoanInfo frm = new wpfLoanInfo();
                    int num = Convert.ToInt32(getRow(dgLoan, 0));
                    frm.lId = num;
                    frm.name = getRow(dgLoan, 4);
                    frm.UserID = UserID;
                    frm.ShowDialog();
                }
                else
                {
                    wpfAppliedLoanInfo frm = new wpfAppliedLoanInfo();
                    int num = Convert.ToInt32(getRow(dgLoan, 0));
                    frm.UserID = UserID;
                    frm.lId = num;
                    frm.status = status;
                    if (status == "Renewal Releasing")
                        frm.status = "Renewal";
                    frm.ShowDialog();
                }
            }
            catch (Exception ex)
            {
                //System.Windows.MessageBox.Show("Runtime Error: " + ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
                return;
            }
            
        }
        private void btnSave_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                checkError();

                /*if (String.IsNullOrWhiteSpace(txtFName.Text) || String.IsNullOrWhiteSpace(txtLName.Text) || String.IsNullOrWhiteSpace(cmbSex.Text) || String.IsNullOrWhiteSpace(cmbStatus.Text))
                {
                    System.Windows.MessageBox.Show("Please input correct format and/or fill all required fields");
                    return;
                }*/
                if(error == true)
                {
                    System.Windows.MessageBox.Show("Please input correct format and/or fill all required fields", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                    return;
                }

                if (status == "Add")
                {
                    DialogResult dr = System.Windows.Forms.MessageBox.Show("Are you sure you want to add this record?", "Question", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
                    if (dr == System.Windows.Forms.DialogResult.No)
                    {
                        return;
                    }

                    using (var ctx = new finalContext())
                    {
                        var ads = ctx.TempHomeAddresses.Count();
                        var con = ctx.TempClientContacts.Count();

                        if (ads == 0 || con == 0)
                        {
                            System.Windows.MessageBox.Show("Please input at least one address and one contact number", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                            return;
                        }
                    }

                    try
                    {
                        string age = "0";
                        int years = DateTime.Now.Year - dtBDay.SelectedDate.Value.Year;
                        if (dtBDay.SelectedDate.Value.AddYears(years) > DateTime.Now) ;
                        years--;

                        age = years.ToString();
                        int iAge = Convert.ToInt32(age);
                        if (iAge < 18 || iAge > 65)
                        {
                            System.Windows.MessageBox.Show("Client's age must be between 18 and 65");
                            return;
                        }
                    }
                    catch
                    { 
                    
                    }

                    using (var ctx = new finalContext())
                    {
                        var num = ctx.Clients.Where(x => x.FirstName == txtFName.Text && x.LastName == txtLName.Text && x.MiddleName == txtMName.Text && x.Birthday == dtBDay.SelectedDate).Count();
                        if (num > 0)
                        {
                            System.Windows.MessageBox.Show("Client already exists", "Information", MessageBoxButton.OK, MessageBoxImage.Exclamation);
                            return;
                        }
                        var result = "";
                        //if (txtEmail_Copy.Text != "")
                        //{
                            var c = ctx.Clients.Where(x => x.Email == txtEmail_Copy.Text).Count();
                            if (c > 0 && txtEmail_Copy.Text!="")
                            {
                                System.Windows.MessageBox.Show("Email has been already used", "Information", MessageBoxButton.OK, MessageBoxImage.Exclamation);
                                return;
                            }
                            
                            do
                            {
                                var chars = "ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";
                                var random = new Random();
                                result = new string(
                                    Enumerable.Repeat(chars, 15)
                                              .Select(s => s[random.Next(s.Length)])
                                              .ToArray());
                                c = ctx.Clients.Where(x => x.TrackingNumber == result).Count();
                            } while (c > 0);
                        if (txtEmail_Copy.Text != "")
                        {
                            try
                            {
                                string email = txtEmail_Copy.Text;
                                string link = "http://*****:*****@gmail.com"); //See the note afterwards...
                                msg.Body = message;
                                SmtpClient smtp = new SmtpClient("smtp.gmail.com");
                                smtp.EnableSsl = true;
                                smtp.Port = 587;
                                smtp.DeliveryMethod = SmtpDeliveryMethod.Network;
                                smtp.Credentials = new NetworkCredential("*****@*****.**", "312231212131");
                                smtp.Send(msg);
                            }
                            catch (Exception)
                            {

                            }
                        }

                        Client clt = new Client { Birthday = Convert.ToDateTime(dtBDay.SelectedDate).Date, Active = true, MiddleName = txtMName.Text, LastName = txtLName.Text, FirstName = txtFName.Text, Email = txtEmail_Copy.Text, Sex = cmbSex.Text, SSS = txtSSS.Text, Suffix = txtSuffix.Text, TIN = txtTIN.Text, Status = cmbStatus.Text, Photo = ConvertImageToByteArray(selectedFileName), isConfirmed=true, isRegistered=false, TrackingNumber = result };

                        var ads = from ad in ctx.TempHomeAddresses
                                  select ad;
                        foreach (var item in ads)
                        {
                            HomeAddress add = new HomeAddress { AddressNumber = item.AddressNumber, City = item.City, LengthOfStay = item.LengthOfStay, MonthlyFee = item.MonthlyFee, OwnershipType = item.OwnershipType, Province = item.Province, Street = item.Street };
                            ctx.HomeAddresses.Add(add);
                        }

                        var cts = from ct in ctx.TempClientContacts
                                  select ct;
                        foreach (var item in cts)
                        {
                            ClientContact con = new ClientContact { Contact = item.Contact, ContactNumber = item.ContactNumber, Primary = item.Primary };
                            ctx.ClientContacts.Add(con);
                        }

                        var dps = from dp in ctx.TempDependents
                                  select dp;
                        foreach (var item in dps)
                        {
                            Dependent dep = new Dependent { Birthday = item.Birthday, DependentNumber = item.DependentNumber, FirstName = item.FirstName, LastName = item.LastName, MiddleName = item.LastName, School = item.School, Suffix = item.Suffix };
                            ctx.Dependents.Add(dep);
                        }
                        var wks = from wk in ctx.TempWorks
                                  select wk;
                        foreach (var item in wks)
                        {
                            Work wrk = new Work { BusinessName = item.BusinessName, BusinessNumber = item.BusinessNumber, City = item.City, DTI = item.DTI, Employment = item.Employment, LengthOfStay = item.LengthOfStay, MonthlyIncome = item.MonthlyIncome, PLNumber = item.PLNumber, Position = item.Position, Province = item.Province, status = item.status, Street = item.Street, WorkNumber = item.WorkNumber };
                            ctx.Works.Add(wrk);
                        }
                        var rfs = from rf in ctx.TempReferences
                                  select rf;
                        foreach (var item in rfs)
                        {
                            Reference rfr = new Reference { City = item.City, Contact = item.Contact, FirstName = item.FirstName, LastName = item.LastName, MiddleName = item.MiddleName, Province = item.Province, ReferenceNumber = item.ReferenceNumber, Street = item.Street, Suffix = item.Suffix };
                            ctx.References.Add(rfr);
                        }

                        if (cmbStatus.Text == "Married")
                        {
                            //Spouse sps = new Spouse { Birthday = Convert.ToDateTime(dtSBday.SelectedDate).Date, BusinessName = txtSWName.Text, BusinessNumber = txtSBsNumber.Text, City = txtSCity.Text, DTI = txtSDTI.Text, Employment = cmbSEmployment.Text, FirstName = txtSFName.Text, LastName = txtSLName.Text, LengthOfStay = txtSLength.Text, MiddleName = txtSMName.Text, MonthlyIncome = Convert.ToDouble(txtSIncome.Text), PLNumber = txtSPLNumber.Text, Position = txtSPosition.Text, Province = txtSProvince.Text, status = cmbSStatus.Text, Street = txtSStreet.Text, Suffix = txtSuffix.Text };
                            Spouse sps = new Spouse { Birthday = Convert.ToDateTime(dtSBday.SelectedDate).Date, FirstName = txtSFName.Text, LastName = txtSLName.Text, MiddleName = txtSMName.Text, Suffix = txtSuffix.Text };
                            ctx.Spouses.Add(sps);
                        }

                        AuditTrail at = new AuditTrail { EmployeeID = UserID, DateAndTime = DateTime.Now, Action = "Added new Client " + txtFName.Text + " " + txtMName.Text + " " + txtLName.Text + " " + txtSuffix.Text };
                        ctx.AuditTrails.Add(at);

                        ctx.Clients.Add(clt);
                        ctx.SaveChanges();
                        System.Windows.MessageBox.Show("Client successfuly added", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                        this.Close();
                    }
                }
                else
                {
                    DialogResult dr = System.Windows.Forms.MessageBox.Show("Are you sure you want to update this record?", "Question", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
                    if (dr == System.Windows.Forms.DialogResult.No)
                    {
                        return;
                    }

                    using (var ctx = new finalContext())
                    {
                        var ads = ctx.HomeAddresses.Where(x=> x.ClientID == cId).Count();
                        var con = ctx.ClientContacts.Where(x => x.ClientID == cId).Count();

                        if (ads == 0 || con == 0)
                        {
                            System.Windows.MessageBox.Show("Please input at least one address and one contact number", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                            return;
                        }
                    }

                    using (var ctx = new finalContext())
                    {
                        
                        var clt = ctx.Clients.Find(cId);
                        if (txtEmail_Copy.Text != clt.Email && clt.isRegistered == false)
                        {
                            var c = ctx.Clients.Where(x => x.Email == txtEmail_Copy.Text).Count();
                            if (c > 0)
                            {
                                System.Windows.MessageBox.Show("Email has been already used", "Information", MessageBoxButton.OK, MessageBoxImage.Exclamation);
                                return;
                            }
                            else
                            {
                                try
                                {
                                    string email = txtEmail_Copy.Text;
                                    string link = "http://*****:*****@gmail.com"); //See the note afterwards...
                                    msg.Body = message;
                                    SmtpClient smtp = new SmtpClient("smtp.gmail.com");
                                    smtp.EnableSsl = true;
                                    smtp.Port = 587;
                                    smtp.DeliveryMethod = SmtpDeliveryMethod.Network;
                                    smtp.Credentials = new NetworkCredential("*****@*****.**", "312231212131");
                                    smtp.Send(msg);
                                }
                                catch (Exception)
                                { 
                                
                                }
                            }
                        }
                        clt.Birthday = Convert.ToDateTime(dtBDay.SelectedDate).Date;
                        clt.MiddleName = txtMName.Text;
                        clt.LastName = txtLName.Text;
                        clt.FirstName = txtFName.Text;
                        clt.Email = txtEmail_Copy.Text;
                        clt.Sex = cmbSex.Text;
                        clt.SSS = txtSSS.Text;
                        clt.Suffix = txtSuffix.Text;
                        clt.TIN = txtTIN.Text;
                        clt.Status = cmbStatus.Text;
                        if (cmbStatus.Text == "Married")
                        {
                            var sps = ctx.Spouses.Where(x => x.ClientID == clt.ClientID).First();
                            sps.Birthday = Convert.ToDateTime(dtSBday.SelectedDate).Date;
                            sps.FirstName = txtSFName.Text;
                            sps.LastName = txtSLName.Text;
                            sps.MiddleName = txtSMName.Text;
                            sps.Suffix = txtSuffix.Text;
                        }
                        if (isChanged == true)
                        {
                            clt.Photo = ConvertImageToByteArray(selectedFileName);
                        }

                        if (status2 == "Confirmation")
                        {
                            clt.isConfirmed = true;
                            AuditTrail at1 = new AuditTrail { EmployeeID = UserID, DateAndTime = DateTime.Now, Action = "Confirmed Client " + txtFName.Text + " " + txtMName.Text + " " + txtLName.Text + " " + txtSuffix.Text };
                            ctx.AuditTrails.Add(at1);
                            ctx.SaveChanges();
                            System.Windows.MessageBox.Show("Client has been successfully confirmed", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                            this.Close();
                            return;
                        }
                        AuditTrail at = new AuditTrail { EmployeeID = UserID, DateAndTime = DateTime.Now, Action = "Updated Client " + txtFName.Text + " " + txtMName.Text + " " + txtLName.Text + " " + txtSuffix.Text };
                        ctx.AuditTrails.Add(at);
                        ctx.SaveChanges();
                        System.Windows.MessageBox.Show("Client has been successfully updated", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                        this.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                System.Windows.MessageBox.Show("Runtime Error: " + ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
                return;
            }
        }
        private void Button_Click_4(object sender, RoutedEventArgs e)
        {
            try
            {
            MessageBoxResult mr = MessageBox.Show("Are you sure you want to clear the selected cheque(s)?","Question",MessageBoxButton.YesNo);
            if (mr == MessageBoxResult.Yes)
            {
                using (var ctx = new finalContext())
                {
                    var chq = from ch in ctx.TempClearings
                              select ch;
                    foreach (var item in chq)
                    {
                        var ch = ctx.FPaymentInfo.Find(item.FPaymentInfoID);
                        ch.PaymentStatus = "Cleared";
                        var ctr = ctx.FPaymentInfo.Where(x=> x.LoanID==item.FPaymentInfo.LoanID && x.PaymentStatus=="Due/Pending").Count();
                        if (ctr > 0)
                        {
                            var ch2 = ctx.FPaymentInfo.Where(x => x.LoanID == item.FPaymentInfo.LoanID && x.PaymentStatus == "Due/Pending").First();
                            ch2.PaymentStatus = "Due";
                        }
                        ClearedCheque cc = new ClearedCheque { FPaymentInfoID = item.FPaymentInfoID, DateCleared = DateTime.Today.Date };
                        ctx.ClearedCheques.Add(cc);
                    }
                    AuditTrail at = new AuditTrail { EmployeeID = UserID, DateAndTime = DateTime.Now, Action = "Cleared Cheques" };
                    ctx.AuditTrails.Add(at);

                    ctx.SaveChanges();
                    MessageBox.Show("Cheque(s) has/have been successfully cleared", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                    this.Close();
                }
            }
            }
            catch (Exception ex)
            {
                System.Windows.MessageBox.Show("Runtime Error: " + ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
                return;
            }
        }
        private void btnSave_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                if (lblFName.Content == "?" || lblLName.Content == "?" || lblMI.Content == "?" || lblEmail.Content == "?" || lblSuffix.Content == "?"
                    || String.IsNullOrWhiteSpace(txtFName.Text) || String.IsNullOrWhiteSpace(txtLName.Text))
                {
                    System.Windows.MessageBox.Show("Please input correct format and/or fill all required fields");
                    return;
                }

                if (status == "Add")
                {

                    DialogResult dr = System.Windows.Forms.MessageBox.Show("Are you sure you want to add this record?", "Question", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
                    if (dr == System.Windows.Forms.DialogResult.No)
                    {
                        return;
                    }

                    if (txtFName.Text == "" || txtLName.Text == "")
                    {
                        System.Windows.MessageBox.Show("Please complete the required information", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
                        return;
                    }

                    if (!Regex.IsMatch(txtFName.Text, @"^[a-zA-Z]+$"))
                    {

                    }


                    using (var ctx = new finalContext())
                    {
                        Agent agt = new Agent { FirstName = txtFName.Text, LastName = txtLName.Text,  Suffix = txtSuffix.Text, MI = txtMI.Text, Active = true,  Email = txtEmail.Text, Photo = ConvertImageToByteArray(selectedFileName) };

                        var query = from con in ctx.TempAgentAddresses
                                    select con;
                        foreach (var item in query)
                        {
                            AgentAddress add = new AgentAddress { AddressNumber=item.AddressNumber, Street = item.Street, City = item.City, Province = item.Province };
                            ctx.AgentAddresses.Add(add);
                        }

                        var query1 = from con in ctx.TempAgentContact
                                     select con;
                        foreach (var item in query1)
                        {
                            AgentContact con = new AgentContact { CNumber=item.CNumber, Contact = item.Contact };
                            ctx.AgentContacts.Add(con);
                        }
                        AuditTrail at = new AuditTrail { EmployeeID = UserID, DateAndTime = DateTime.Now, Action = "Added new Agent " + agt.FirstName + " " + agt.MI + " " + agt.LastName + " " + agt.Suffix};
                        ctx.AuditTrails.Add(at);
                        ctx.Agents.Add(agt);
                        ctx.SaveChanges();
                        System.Windows.MessageBox.Show("Added New Agent");
                        this.Close();


                    }
                }
                else
                {
                    
                    DialogResult dr = System.Windows.Forms.MessageBox.Show("Are you sure you want to update this record?", "Question", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
                    if (dr == System.Windows.Forms.DialogResult.No)
                    {
                        return;
                    }
                    using (var ctx = new finalContext())
                    {
                        var agt = ctx.Agents.Find(aId);
                        agt.FirstName = txtFName.Text;
                        agt.LastName = txtLName.Text;
                        agt.Suffix = txtSuffix.Text;
                        agt.MI = txtMI.Text;
                        agt.Email = txtEmail.Text;
                        if (isChanged == true)
                        {
                            agt.Photo = ConvertImageToByteArray(selectedFileName);
                        }
                        AuditTrail at = new AuditTrail { EmployeeID = UserID, DateAndTime = DateTime.Now, Action = "Updated Agent " + agt.FirstName + " " + agt.MI + " " + agt.LastName + " " + agt.Suffix };
                        ctx.AuditTrails.Add(at);
                        ctx.SaveChanges();
                        System.Windows.MessageBox.Show("Agent Updated", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                        this.Close();
                    }
                     
                }
            }
            catch (Exception ex)
            {
                System.Windows.MessageBox.Show("Runtime Error: " + ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
                return;
            }
        }
        private void btnPass_Click(object sender, RoutedEventArgs e)
        {
            if (cID == 0)
            {
                MessageBox.Show("Please assign a collector for this loan", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
                return;
            }

            MessageBoxResult mr = MessageBox.Show("Are you sure you want to process this transaction? This cannot be undone", "Question", MessageBoxButton.YesNo, MessageBoxImage.Question);
            if (mr == MessageBoxResult.Yes)
            {

                using (var ctx = new finalContext())
                {
                    var lon = ctx.Loans.Find(n);
                    lon.Status = "Under Collection";//active
                    int num = ctx.ClosedAccounts.Where(x => x.LoanID == n).Count();

                    var rmn = from rm in ctx.FPaymentInfo
                              where rm.LoanID == n && rm.PaymentStatus == "Cleared"
                              select rm;
                    double r = 0;
                    foreach (var item in rmn)
                    {
                        r = r + item.Amount;
                    }
                    double remain = lon.ReleasedLoan.TotalLoan - r;

                    PassedToCollector pc = new PassedToCollector { LoanID = n, DatePassed = DateTime.Today.Date, RemainingBalance = remain, TotalPassedBalance = remain, TotalPaidBeforePassing = r };
                    ctx.PassedToCollectors.Add(pc);
                    lon.CollectortID = cID;
                    AuditTrail at = new AuditTrail { EmployeeID = UserID, DateAndTime = DateTime.Now, Action = "Voided Closed Account for Loan " + lon.LoanID };
                    ctx.AuditTrails.Add(at);
                    ctx.SaveChanges();
                    MessageBox.Show("Transaction has been successfully processed", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                    return;
                }
            }
        }
        private void btnSave_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                if (txtPassword.Password != txtConfirm.Password)
                {
                    MessageBox.Show("Password doesn't match", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                    txtPassword.Password = "";
                    txtConfirm.Password = "";
                    return;
                }
                if (status != "view")
                {
                    using (var ctx = new finalContext())
                    {
                        var u = ctx.Users.Where(x => x.Username == txtUserName.Text).Count();
                        if (u > 0)
                        {
                            MessageBox.Show("Username already exists", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                            txtUserName.Text = "";
                            return;
                        }

                        User usr = new User { EmployeeID = eId, Username = txtUserName.Text, Password = txtPassword.Password };
                        Scope sc = new Scope { EmployeeID = eId };
                        ctx.Users.Add(usr);
                        ctx.Scopes.Add(sc);

                        AuditTrail at = new AuditTrail { EmployeeID = UserID, DateAndTime = DateTime.Now, Action = "Added new User Account " + txtUserName.Text };
                        ctx.AuditTrails.Add(at);

                        ctx.SaveChanges();
                        MessageBox.Show("User has been successfully added", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                        this.Close();
                    }
                }
                else
                {
                    using (var ctx = new finalContext())
                    {
                        var u = ctx.Users.Find(eId);
                        u.Password = txtPassword.Password;
                        AuditTrail at = new AuditTrail { EmployeeID = UserID, DateAndTime = DateTime.Now, Action = "Updated User Account " + txtUserName.Text };
                        ctx.AuditTrails.Add(at);
                        ctx.SaveChanges();
                        MessageBox.Show("User has been successfully updated", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                        this.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                System.Windows.MessageBox.Show("Runtime Error: " + ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
                return;
            }
        }
        private void btnRenew_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                foreach (var i in textarray)
                {
                    if (i.Text.Length != 6)
                    {
                        System.Windows.MessageBox.Show("Please input all cheque numbers", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                        return;
                    }
                    bool err;
                    int res;
                    String str = i.Text;
                    err = int.TryParse(str, out res);
                    if (err == false)
                    {
                        System.Windows.MessageBox.Show("Please input the correct format for cheque numbers(Strictly numbers only.)", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                        return;
                    }
                }

                for (int x = 0; x < textarray.Length; x++)
                {
                    for (int y = x + 1; y < textarray.Length; y++)
                    {
                        if (textarray[x].Text == textarray[y].Text)
                        {
                            System.Windows.MessageBox.Show("No duplications of cheque numbers", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                            return;
                        }
                    }
                }



                MessageBoxResult mr = MessageBox.Show("Are you sure you want to process this transaction?", "Question", MessageBoxButton.YesNo);
                if (mr == MessageBoxResult.Yes)
                {
                    wpfCheckout frm = new wpfCheckout();
                    frm.status = "RenewClosed";
                    frm.lId = lId;
                    frm.ShowDialog();

                    if (cont == false)
                    {
                        System.Windows.MessageBox.Show("Please pay the Closed Account Penalty first", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
                        return;
                    }

                    using (var ctx = new finalContext())
                    {
                        var bk = ctx.Banks.Where(x => x.BankName == cmbBank.Text).First();
                        int bId = bk.BankID;
                        var lon = ctx.Loans.Find(lId);
                        lon.Status = "Released";//active
                        lon.BankID = bId;
                        var ch = from c in ctx.FPaymentInfo
                                 where c.LoanID == lId && c.PaymentStatus == "Void"
                                 select c;
                        int Interval = 0;
                        DateInterval dInt = new DateInterval();
                        if (lon.Mode == "Monthly")
                        {
                            Interval = 1;
                            dInt = DateInterval.Month;
                        }
                        else if (lon.Mode == "Semi-Monthly")
                        {
                            Interval = 15;
                            dInt = DateInterval.Day;
                        }
                        else if (lon.Mode == "Weekly")
                        {
                            Interval = 7;
                            dInt = DateInterval.Day;
                        }
                        else if (lon.Mode == "Daily")
                        {
                            Interval = 1;
                            dInt = DateInterval.Day;
                        }
                        /*else if (cmbMode.Text == "One-Time Payment")
                        {
                            NetProceed = NetProceed - ((Convert.ToDouble(txtAmt.Text) * TotalInt));
                            lblProceed.Content = NetProceed.ToString("N2");
                            lblInt.Content = txtAmt.Text;
                            Remaining = Convert.ToDouble(lblPrincipal.Content);
                            Payment = Remaining;
                            Interval = Convert.ToInt32(txtTerm.Text);
                            dInt = DateInterval.Month;
                            lbl4.Content = "Total Payment";
                        }*/
                        DateTime dt = dtPcker.SelectedDate.Value.Date;
                        int num = 0;
                        foreach (var item in ch)
                        {
                            item.ChequeInfo = textarray[num].Text;
                            item.ChequeDueDate = dt;
                            item.PaymentDate = dt;
                            item.PaymentStatus = "Pending";
                            dt = DateAndTime.DateAdd(dInt, Interval, dt);
                            num++;
                        }

                        var cc = ctx.ClosedAccounts.Where(x => x.LoanID == lId && x.isPaid == false).First();
                        cc.isPaid = true;

                        AuditTrail at = new AuditTrail { EmployeeID = UserID, DateAndTime = DateTime.Now, Action = "Processed renewal for Closed Account Loan " + lon.LoanID };
                        ctx.AuditTrails.Add(at);

                        ctx.SaveChanges();
                        MessageBox.Show("Transaction has been successfully processed", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                        this.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                System.Windows.MessageBox.Show("Runtime Error: " + ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
                return;
            }
        }
        private void btnApprove_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                double max  = 0;
                double min = 0;
                using (var ctx = new finalContext())
                { 
                    var lon = ctx.Loans.Find(lId);
                    var ser = ctx.Services.Find(lon.ServiceID);
                    max = ser.MaxValue;
                    min = ser.MinValue;
                    double val = Convert.ToDouble(txtAmt.Text);
                    if (status == "Renewal")
                    {
                        if (val < lon.ReleasedLoan.TotalLoan)
                        {
                            System.Windows.MessageBox.Show("Amount must be greater than or equal the Total Loan Amount of Previous loan(" + lon.ReleasedLoan.TotalLoan.ToString("N2") + ")", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                            return;
                        }
                    }
                }
                if (Convert.ToDouble(txtAmt.Text) > max || Convert.ToDouble(txtAmt.Text) < min)
                {
                    System.Windows.MessageBox.Show("Principal amount must not be greater than the maximum loanable amount OR less than the minimum loanable amount", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
                    return;
                }
                string qst = "Are you sure you want to process this transaction?";

                using (var ctx = new finalContext())
                {
                    var c1 = ctx.RequirementChecklists.Where(x => x.LoanID == lId).Count();
                    var lon = ctx.Loans.Find(lId);
                    var c2 = ctx.Requirements.Where(x => x.ServiceID == lon.ServiceID).Count();
                    if (c1 != c2)
                        qst = "Are you sure you want to process this loan even with incomplete requirements?";
                }


                System.Windows.MessageBoxResult mr = System.Windows.MessageBox.Show(qst, "Question", MessageBoxButton.YesNo, MessageBoxImage.Question);
                if (mr == System.Windows.MessageBoxResult.Yes)
                {
                    using (var ctx = new finalContext())
                    {
                        var lon = ctx.Loans.Find(lId);
                        if (status == "Approval" || status == "Renewal Approval")
                        {

                            lon.Status = "Approved";
                            if (status == "Renewal Approval")
                            {
                                lon.Status = "Approved for Renewal";
                                var rn = ctx.LoanRenewals.Where(x => x.newLoanID == lId).First();
                                rn.Status = "Approved";
                            }
                            ApprovedLoan al = new ApprovedLoan { AmountApproved = Convert.ToDouble(txtAmt.Text), DateApproved = DateTime.Today.Date, ReleaseDate = dtDate.SelectedDate.Value.Date };
                            lon.ApprovedLoan = al;
                            AuditTrail at = new AuditTrail { EmployeeID = UserID, DateAndTime = DateTime.Now, Action = "Processed approval for loan (" + lon.Service.Name + ") for client " + lon.Client.FirstName + " " + lon.Client.MiddleName + " " + lon.Client.LastName + " " + lon.Client.Suffix };
                            ctx.AuditTrails.Add(at);
                            ctx.SaveChanges();
                            System.Windows.MessageBox.Show("Loan has been successfully approved", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                        }
                        else
                        {
                            lon.ApprovedLoan.AmountApproved = Convert.ToDouble(txtAmt.Text);
                            lon.ApprovedLoan.DateApproved = DateTime.Today.Date;
                            lon.ApprovedLoan.ReleaseDate = dtDate.SelectedDate.Value.Date;
                            AuditTrail at = new AuditTrail { EmployeeID = UserID, DateAndTime = DateTime.Now, Action = "Updated approval for loan (" + lon.Service.Name + ") for client " + lon.Client.FirstName + " " + lon.Client.MiddleName + " " + lon.Client.LastName + " " + lon.Client.Suffix };
                            ctx.AuditTrails.Add(at);
                            ctx.SaveChanges();
                            System.Windows.MessageBox.Show("Loan Updated");
                        }
                        try
                        {
                            mr = System.Windows.MessageBox.Show("Do you want to send a message to the client regarding this loan approval?", "Question", MessageBoxButton.YesNo, MessageBoxImage.Question);
                            if (mr == System.Windows.MessageBoxResult.Yes)
                            {
                                var cont = ctx.ClientContacts.Where(x => x.ClientID == lon.ClientID && x.Primary == true).First();

                                string con = cont.Contact;
                                MailMessage msg = new MailMessage();
                                msg.To.Add(con + "@m2m.ph");
                                msg.From = new MailAddress("*****@*****.**");
                                msg.Body = "We are glad to inform you that your loan application(" + lon.Service.Name + ") has been approved with the following details: \n\n Maximum Loanable Amount : " + txtAmt.Text + " \n Release Date : " + dtDate.SelectedDate.Value.Date.Date.Date.ToString() + "";
                                SmtpClient smtp = new SmtpClient("smtp.gmail.com");
                                smtp.EnableSsl = true;
                                smtp.Port = 587;
                                smtp.DeliveryMethod = SmtpDeliveryMethod.Network;
                                smtp.Credentials = new NetworkCredential("*****@*****.**", "312231212131");
                                smtp.Send(msg);
                                System.Windows.MessageBox.Show("Message successfuly sent", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                            }
                        }
                        catch (Exception ex)
                        {
                            System.Windows.MessageBox.Show(ex.Message.ToString());
                        }
                        this.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                System.Windows.MessageBox.Show("Incorrect Format on some Fields / Incomplete Input(s)", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
                return;
            }
        }
        private void btnRelease_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                if (status == "Releasing")
                {
                    double max = 0;
                    double min = 0;
                    using (var ctx = new finalContext())
                    {
                        var lon = ctx.Loans.Find(lId);
                        var ser = ctx.Services.Find(lon.ServiceID);
                        max = ser.MaxTerm;
                        min = ser.MinTerm;


                        if (Convert.ToDouble(txtTerm.Text) > max || Convert.ToDouble(txtTerm.Text) < min)
                        {
                            System.Windows.MessageBox.Show("Term must not be greater than the maximum term(" + ser.MaxTerm + " mo.) OR less than the minimum term(" + ser.MinTerm + " mo.)", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
                            return;
                        }

                        max = ser.MaxValue;
                        min = ser.MinValue;

                        if (Convert.ToDouble(txtAmt.Text) > max || Convert.ToDouble(txtAmt.Text) < min)
                        {
                            System.Windows.MessageBox.Show("Principal amount must not be greater than the maximum loanable amount OR less than the minimum loanable amount", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
                            return;
                        }
                    }

                    if (Convert.ToDouble(txtAmt.Text) > Convert.ToDouble(lblPrincipal.Content))
                    {
                        MessageBox.Show("Principal amount must not be greater than the maximum loanable amount", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
                        return;
                    }

                    if(ciId == 0)
                    {
                        MessageBox.Show("Please assign a collector for this loan", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
                        return;
                    }

                    MessageBoxResult mr = MessageBox.Show("Are you sure you want to process this transaction?", "Question", MessageBoxButton.YesNo, MessageBoxImage.Question);
                    if (mr == MessageBoxResult.Yes)
                    {
                        using (var ctx = new finalContext())
                        {
                            int bId = 0;
                            var lon = ctx.Loans.Find(lId);
                            lon.Status = "Released";
                            lon.Mode = cmbMode.Text;
                            lon.Term = Convert.ToInt32(txtTerm.Text);
                            lon.CollectortID = ciId;
                            var cn = ctx.Services.Find(lon.ServiceID);
                            double co = cn.AgentCommission / 100;
                            double cm = Convert.ToDouble(txtAmt.Text) * co;
                            //MessageBox.Show(cm.ToString());
                            ReleasedLoan rl = new ReleasedLoan { AgentsCommission = cm, DateReleased = DateTime.Today.Date, LoanID = lId, MonthlyPayment = Convert.ToDouble(lblMonthly.Content), NetProceed = Convert.ToDouble(lblProceed.Content), Principal = Convert.ToDouble(txtAmt.Text), TotalLoan = Convert.ToDouble(lblInt.Content) };
                            lon.ReleasedLoan = rl;
                            var lo = ctx.GenSOA.Where(x => x.PaymentNumber == 1).First();
                            int y = 0;
                            MPaymentInfo py = new MPaymentInfo { Amount = Convert.ToDouble(lo.Amount), BalanceInterest = 0, DueDate = lo.PaymentDate, LoanID = lId, PaymentNumber = 1, PaymentStatus = "Pending", PreviousBalance = 0, RemainingLoanBalance = Convert.ToDouble(lo.RemainingBalance), TotalAmount = Convert.ToDouble(lo.Amount), TotalBalance = 0 };
                            ctx.MPaymentInfoes.Add(py);
                            AuditTrail at = new AuditTrail { EmployeeID = UserID, DateAndTime = DateTime.Now, Action = "Released loan (" + lon.Service.Name + ") for client " + lon.Client.FirstName + " " + lon.Client.MiddleName + " " + lon.Client.LastName + " " + lon.Client.Suffix };
                            ctx.AuditTrails.Add(at);
                            ctx.SaveChanges();
                            printSOA();
                            MessageBox.Show("Transaction has been successfully processed", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                            this.Close();
                        }
                    }
                }
                else if (status == "UReleasing")
                {
                    
                    using (var ctx = new finalContext())
                    {
                        var lon = ctx.Loans.Find(lId);
                        lon.CollectortID = ciId;

                        AuditTrail at = new AuditTrail { EmployeeID = UserID, DateAndTime = DateTime.Now, Action = "Updated Released loan (" + lon.Service.Name + ") for client " + lon.Client.FirstName + " " + lon.Client.MiddleName + " " + lon.Client.LastName + " " + lon.Client.Suffix };
                        ctx.AuditTrails.Add(at);
                        ctx.SaveChanges();
                        MessageBox.Show("Transaction has been successfully updated", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                        this.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                System.Windows.MessageBox.Show("Incorrect Format on some Fields / Incomplete Input(s)", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
                return;
            }
        }
        private void btnLogIn_Click(object sender, RoutedEventArgs e)
        {
            //MessageBox.Show("Hi");
            //pr1.IsActive = true;
            try
            {

                if (txtUsername.Text == "")
                {
                    //MessageBox.Show("Please enter your username", "Error",MessageBoxButton.OK,MessageBoxImage.Error);
                    FocusManager.SetFocusedElement(this, txtUsername);
                    return;
                }

                if (txtPassword.Password == "")
                {
                    //MessageBox.Show("Please enter your password", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
                    FocusManager.SetFocusedElement(this, txtPassword);
                    return;
                }



                using (var ctx = new finalContext())
                {
                    var count = ctx.Users.Where(x => x.Username == txtUsername.Text && x.Password == txtPassword.Password).Count();
                    if (count > 0)
                    {
                        var em = ctx.Users.Where(x => x.Username == txtUsername.Text && x.Password == txtPassword.Password).First();
                        System.Windows.MessageBox.Show("Login Successful", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
                        wpfMain wnd = new wpfMain();

                        wnd.UserID = em.EmployeeID;

                        if (em.Employee.Department == "Financing")
                        {
                            //wnd.tbFinancing.IsEnabled = true;
                            wnd.grdM1.IsEnabled = true;
                            //wnd.tbMicro.IsEnabled = !true;
                            wnd.grdLo1.IsEnabled = !true;
                        }
                        else if (em.Employee.Department == "Micro Business")
                        {
                            //wnd.tbFinancing.IsEnabled = !true;
                            wnd.grdM1.IsEnabled = !true;
                            //wnd.tbMicro.IsEnabled = true;
                            wnd.grdLo1.IsEnabled = true;
                        }
                        else
                        {
                            //wnd.tbFinancing.IsEnabled = true;
                            wnd.grdM1.IsEnabled = true;
                            //wnd.tbMicro.IsEnabled = true;
                            wnd.grdLo1.IsEnabled = true;
                        }


                        var sc = ctx.PositionScopes.Find(em.Employee.PositionID);
                        wnd.btnMClients.IsEnabled = sc.MClient;
                        wnd.btnMAgents.IsEnabled = sc.MAgent;
                        wnd.btnMServ.IsEnabled = sc.MService;
                        wnd.btnMBank.IsEnabled = sc.MBank;
                        wnd.btnMEmployee.IsEnabled = sc.MEmployee;
                        wnd.btnMPosition.IsEnabled = sc.MPosition;
                        wnd.btnMHoliday.IsEnabled = sc.MHoliday;
                        wnd.btnMClients_Confirmation.IsEnabled = sc.MRegistration;

                        wnd.btnFApplication.IsEnabled = sc.TApplication;
                        wnd.btnMLoanApplication.IsEnabled = sc.TApplication;
                        wnd.btnFApproval.IsEnabled = sc.TApproval;
                        wnd.btnMApproval.IsEnabled = sc.TApproval;
                        wnd.btnFReleasing.IsEnabled = sc.TReleasing;
                        wnd.btnMReleasing.IsEnabled = sc.TReleasing;
                        wnd.btnFPayments.IsEnabled = sc.TPayments;
                        wnd.btnMPayments.IsEnabled = sc.TPayments;
                        wnd.btnFConfirmApplication.IsEnabled = sc.TOnlineConfirmation;
                        wnd.btnMConfirmApplication.IsEnabled = sc.TOnlineConfirmation;
                        wnd.btnFManage.IsEnabled = sc.TManageClosed ;
                        wnd.btnFRestructure.IsEnabled = sc.TResturcture;
                        wnd.btnFAdjustment.IsEnabled = sc.TPaymentAdjustment;
                        wnd.btnFRenewal.IsEnabled = sc.TRenewal;
                        wnd.tbCollectors.IsEnabled = sc.TCollection;

                        

                        wnd.grdArchive.IsEnabled=sc.UArchive;
                        wnd.btnUBackUp.IsEnabled=sc.UBackUp;
                        wnd.btnUUser.IsEnabled = sc.UUserAccounts;
                        wnd.grdStatistic.IsEnabled = sc.UStatistics;
                        wnd.tbRep.IsEnabled = sc.UReports;
                        wnd.tbOnline.IsEnabled = sc.UOnlineSettings;

                        var st = ctx.State.Find(1);
                        st.iState = 0;

                        AuditTrail at = new AuditTrail { EmployeeID = em.EmployeeID, DateAndTime = DateTime.Now, Action = "Accessed the system" };
                        ctx.AuditTrails.Add(at);

                        ctx.SaveChanges();
                        
                        this.Close();
                        wnd.Show();
                        
                    }
                    else
                    {
                        System.Windows.MessageBox.Show("Username/Password is incorrect", "Error");
                        txtPassword.Password = "";
                        txtUsername.Text = "";
                        var st = ctx.State.Find(1);
                        st.iState = st.iState + 1;
                        ctx.SaveChanges();
                        checkState();
                        pr1.IsActive = !true;
                    }
                }
            }
            catch (Exception ex)
            {
                //System.Windows.MessageBox.Show("Runtime Error: " + ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
                //return;
            }
        }