Excel.cs

//MISCELLENIOUS METHODS

       
        public static void AddBatchItems(string batchno, decimal principalAmount, decimal interestAmount, Guid customeraccountid, Guid loanproductid)
        {
            using (var db = new ApplicationDbContext())
            {
                var TheBatch = db.Batch.Where(s => s.BatchNo == batchno).FirstOrDefault();
                var transaction = new BatchItems
                {
                    Id = Guid.NewGuid(),
                    Batchno=batchno,
                    AccountNo = customeraccountid.ToString(),
                    Narration = TheBatch.Narration,
                    Debit = principalAmount,
                    IntDebit=interestAmount,
                    Ttype=loanproductid.ToString(),
                    GlaccCr=TheBatch.Account.ToString(),
                    GlaccDR= TheBatch.Account.ToString()
                };
                TheBatch.Variance += (transaction.Debit + transaction.IntDebit);
                db.BatchItems.Add(transaction);
                db.SaveChanges();
            }
        }
    
    
    ////It loops through each item of a  row
    
       public ActionResult Upload(HttpPostedFileBase upload, string Batchno)
        {

            if (upload != null && upload.ContentLength > 0)
            {
                Stream stream = upload.InputStream;
                IExcelDataReader reader = null;
                if (upload.FileName.EndsWith(".xls"))
                {
                    reader = ExcelReaderFactory.CreateBinaryReader(stream);
                }
                else if (upload.FileName.EndsWith(".xlsx"))
                {
                    reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
                }
                else
                {
                    ModelState.AddModelError("File", "This file format is not supported");
                    ViewBag.Message = "This file format is not supported";
                    return View();
                }
            
                DataSet result = reader.AsDataSet();
                DataTable t = new DataTable();
                DataTable table = result.Tables[0];
                DataRow rows = table.Rows[0];
                table.Rows.Remove(rows);
               
                    using (var db = new ApplicationDbContext())
                    {
                        var shares = db.ShareTypes.ToList();
                        var loans = db.LoanProducts.ToList();

                        foreach (DataRow row in table.Rows)
                        {
                            if (!string.IsNullOrEmpty(row[0].ToString()))
                            {
                                string accno = row[0].ToString().Trim().Trim();
                                accno = accno.Length == 4 ? $"0{accno}" : accno;
                                accno = accno.Length == 3 ? $"00{accno}" : accno;
                                accno = accno.Length == 2 ? $"000{accno}" : accno;
                                accno = accno.Length == 1 ? $"0000{accno}" : accno;
                                var customeraccounts = db.CustomerAccounts.FirstOrDefault(i => i.AccountNumber.Equals(accno, StringComparison.InvariantCultureIgnoreCase));
                           
                                if (customeraccounts != null && !string.IsNullOrEmpty(Batchno))
                                {
                                   
                                    if (!string.IsNullOrEmpty(row[4].ToString().Trim().Trim()))
                                    {
                                        var share = shares.FirstOrDefault(i => i.Description.Equals("SHARE DEPOSITS", StringComparison.InvariantCultureIgnoreCase));
                                        Miscellaneous.AddBatchItems(Batchno, row[4].ToString().Trim().Trim().ToDecimal(), 0, customeraccounts.Id, share.Id);
                                    }
                                    if (!string.IsNullOrEmpty(row[7].ToString().Trim().Trim()) || !string.IsNullOrEmpty(row[10].ToString().Trim().Trim().Trim('-')))
                                    {
                                        var loan = loans.FirstOrDefault(i => i.Description.Equals("NORMAL LOAN", StringComparison.InvariantCultureIgnoreCase));
                                        Miscellaneous.AddBatchItems(Batchno, row[7].ToString().Trim().Trim().ToDecimal(), row[10].ToString().Trim().Trim().ToDecimal(), customeraccounts.Id, loan.Id);
                                    }
                                    if (!string.IsNullOrEmpty(row[11].ToString().Trim().Trim())|| !string.IsNullOrEmpty(row[14].ToString().Trim().Trim('-')))
                                    {
                                        var loan = loans.FirstOrDefault(i => i.Description.Equals("SCHOOL FEES LOAN", StringComparison.InvariantCultureIgnoreCase));
                                        Miscellaneous.AddBatchItems(Batchno, row[11].ToString().Trim().Trim().ToDecimal(), row[14].ToString().Trim().ToDecimal(), customeraccounts.Id, loan.Id);
                                    }
                                    if (!string.IsNullOrEmpty(row[15].ToString().Trim().Trim())|| !string.IsNullOrEmpty(row[18].ToString().Trim().Trim('-')))
                                    {
                                        var loan = loans.FirstOrDefault(i => i.Description.Equals("EMERGENCY LOAN", StringComparison.InvariantCultureIgnoreCase));
                                        Miscellaneous.AddBatchItems(Batchno, row[15].ToString().Trim().Trim().ToDecimal(), row[18].ToString().Trim().ToDecimal(), customeraccounts.Id, loan.Id);

                                    }
                                    if (!string.IsNullOrEmpty(row[20].ToString().Trim().Trim().TrimStart().TrimEnd())|| !string.IsNullOrEmpty(row[21].ToString().Trim().Trim('-')))
                                    {
                                        var loan = loans.FirstOrDefault(i => i.Description.Equals("INSTANT LOAN", StringComparison.InvariantCultureIgnoreCase));
                                        Miscellaneous.AddBatchItems(Batchno, row[20].ToString().Trim().Trim().ToDecimal(), row[21].ToString().Trim().ToDecimal(), customeraccounts.Id, loan.Id);

                                    }
                                    if (!string.IsNullOrEmpty(row[22].ToString().Trim().Trim().TrimStart().TrimEnd())||!string.IsNullOrEmpty(row[23].ToString().Trim().Trim('-')))
                                    {
                                        var loan = loans.FirstOrDefault(i => i.Description.Equals("ANGAZA LOAN", StringComparison.InvariantCultureIgnoreCase));
                                        Miscellaneous.AddBatchItems(Batchno, row[22].ToString().Trim().Trim().ToDecimal(), row[23].ToString().Trim().ToDecimal(), customeraccounts.Id, loan.Id);

                                    }
                                    if (!string.IsNullOrEmpty(row[24].ToString().Trim().Trim().TrimStart().TrimEnd()))
                                    {
                                        var share = shares.FirstOrDefault(i => i.Description.Equals("SABEFU", StringComparison.InvariantCultureIgnoreCase));
                                        Miscellaneous.AddBatchItems(Batchno, row[24].ToString().Trim().Trim().ToDecimal(), 0, customeraccounts.Id, share.Id);

                                    }
                                    if (!string.IsNullOrEmpty(row[25].ToString().Trim().Trim().TrimStart().TrimEnd()))
                                    {
                                        var share = shares.FirstOrDefault(i => i.Description.Equals("EDUCATION SCHEME", StringComparison.InvariantCultureIgnoreCase));
                                        Miscellaneous.AddBatchItems(Batchno, row[25].ToString().Trim().Trim().ToDecimal(), 0, customeraccounts.Id, share.Id);
                                    }
                                    if (!string.IsNullOrEmpty(row[26].ToString().Trim().Trim().TrimStart().TrimEnd()))
                                    {
                                        var share = shares.FirstOrDefault(i => i.Description.Equals("MEDICARE", StringComparison.InvariantCultureIgnoreCase));
                                        Miscellaneous.AddBatchItems(Batchno, row[26].ToString().Trim().Trim().ToDecimal(), 0, customeraccounts.Id, share.Id);
                                    }


                                }
                          
                          
                            }
                        }
                       db.Database.ExecuteSqlCommand("delete from BatchItems where Debit=0 and Credit=0 and IntDebit=0 and IntCredit=0");
                    }

               
                this.AddToastMessage("Success", "Uploade Suceessfully");

                return RedirectToAction("batchitems", new { id = Batchno });
            }
            else
            {
                this.AddToastMessage("File", "Please Upload Your File", ToastType.Success);

                return RedirectToAction("upload", new { Batchno });
            }

        }