public ActionResult LoadBatch() { // add new user if (Session["UserName"] == null) { return(RedirectToAction("Index", "Account")); } ViewBag.ReportTitle = "Create new bank record"; LoadBatchVM loadBatch = new LoadBatchVM(); loadBatch.AllBankAccount = GetSelectListItems((short)Helpers.Helpers.ListType.bankaccount); loadBatch.AllAccountType = GetSelectListItems((short)Helpers.Helpers.ListType.allAccountType); return(View(loadBatch)); }
public ActionResult Upload(string FinancialBankAccountID, string FinancialAccountTypeID, HttpPostedFileBase upload) { if (ModelState.IsValid) { LoadBatchVM loadBatch = new LoadBatchVM(); if (upload != null && upload.ContentLength > 0) { if (upload.FileName.ToLower().EndsWith(".csv")) { Stream stream = upload.InputStream; DataTable csvTable = new DataTable(); using (CsvReader csvReader = new CsvReader(new StreamReader(stream), true)) { csvTable.Load(csvReader); } //load the data to unit operation SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["SunriseConnectionString"].ConnectionString); SqlCommand cmd = sqlConn.CreateCommand(); DataTable dtSearchResult = new DataTable(); SqlDataAdapter daSearchResult = new SqlDataAdapter(); List <OperationRecord> operations = new List <OperationRecord>(); try { sqlConn.Open(); //Get accountid by account number int accountid = Int32.Parse(FinancialBankAccountID); int accoutypeid = Int32.Parse(FinancialAccountTypeID); foreach (DataRow dr in csvTable.Rows) { OperationRecord model = new OperationRecord(); model.IsCredit = false; //load from chase credit card if (accoutypeid == 3) { model.DueDate = DateTime.Parse(dr[1].ToString()); model.CompleteDate = DateTime.Parse(dr[2].ToString()); model.Memo = dr[3].ToString().Replace("'", ""); model.Payment = double.Parse(dr[4].ToString()); if (model.Payment < 0) { model.DueAmount = -model.Payment; //set all expense as repair by default. model.CategoryID = 17; } else { model.IsCredit = true; model.DueAmount = model.Payment; //set all income as owner contribution model.CategoryID = 23; } model.BankTracking = "Chase credit card"; } //load data from quickbook else if (accoutypeid == 4) { model.UnitID = 18; model.ContractorID = 0; if (!string.IsNullOrEmpty(dr[6].ToString())) { model.IsCredit = true; model.Payment = double.Parse(dr[6].ToString()); model.DueAmount = model.Payment; model.CategoryID = 36; } else { model.Payment = -double.Parse(dr[7].ToString()); model.DueAmount = -model.Payment; model.CategoryID = 17; } if (dr[5] != DBNull.Value) { cmd.CommandText = "select CategoryID from cExpenseCategory where CategoryName like + '%" + (dr[5].ToString().Split(' '))[0] + "%'"; object result = cmd.ExecuteScalar(); if (result != null) { model.CategoryID = (short)result; } } if (dr[3] != null) { string[] names = dr[3].ToString().Split(' ').ToArray(); string firstname = names[0]; string lastname = ""; if (names.Length > 1) { lastname = names[1]; } cmd.CommandText = "select UserID from cUser where lastname like '%" + lastname + "%'"; object result = cmd.ExecuteScalar(); if (result != null) { model.ContractorID = ((Int32)cmd.ExecuteScalar()); cmd.CommandText = "select UnitID from tblTenant where UserID = " + model.ContractorID + " order by StartDate Desc"; result = cmd.ExecuteScalar(); if (result != null) { model.UnitID = ((Int32)cmd.ExecuteScalar()); } } else { //create user direct because this user does not exist in the system cmd.CommandText = "INSERT INTO cuser (username, firstname, lastname, password, statusid) values ('" + (firstname + lastname) + "', '" + firstname + "', '" + lastname + "','sunrise', 1); SELECT SCOPE_IDENTITY()"; model.ContractorID = int.Parse(cmd.ExecuteScalar().ToString()); cmd.CommandText = "insert into tblCompanyUser (CompanyID, StartDate, RoleID, UserID, note) values (1,'" + DateTime.Now + "',5," + model.ContractorID + ",'create from quickbook loading')"; cmd.ExecuteNonQuery(); //setup default unitid as ownerland realty model.UnitID = 18; } } model.FinancialBankAccountID = FinancialBankAccountID; model.DueDate = DateTime.Parse(dr[0].ToString()); model.CompleteDate = model.DueDate; model.Memo = (dr[2].ToString() + '-' + dr[3].ToString() + '-' + dr[4].ToString()).Replace("'", ""); model.BankTracking = FinancialBankAccountID; } else { model.IsCredit = (dr[6] != null && !String.IsNullOrEmpty(dr[6].ToString())); if (model.IsCredit) { model.Payment = double.Parse(dr[6].ToString()); model.DueAmount = model.Payment; //set all deposit as rent by default. model.CategoryID = 36; } else { model.Payment = -double.Parse(dr[5].ToString()); model.DueAmount = -model.Payment; //set all expense as repair by default. model.CategoryID = 17; } model.CompleteDate = DateTime.Parse(dr[2].ToString()); model.DueDate = model.CompleteDate; model.Memo = (dr[4].ToString() + "-" + dr[10].ToString()).Replace("'", ""); model.BankTracking = ("53 bank" + dr[3].ToString().Replace("'", "") + "-" + dr[8].ToString().Replace("'", "") + "-" + dr[9].ToString().Replace("'", "")).Replace("'", "");; } int i = (model.IsCredit == true) ? 1 : 0; model.StatusID = 1; model.UploadBy = ((int)Session["UserID"]); model.FinancialBankAccountID = accountid.ToString(); if (model.Memo.IndexOf("'") > 0) { model.Memo = model.Memo.Replace("'", ""); } cmd.CommandText = "insert into tblFinancialAccountOperation(UploadedBy, StatusID, CategoryID, IsCredit, DueDate, DueAmount, Description,FinancialAccountID, Amount, FinishDate, BankTracking, FoundInExpense, HasConsolidated, ContractorID, UnitID) values ("; cmd.CommandText += model.UploadBy + "," + model.StatusID + ", " + model.CategoryID + ", " + i + ",'" + model.DueDate + "', " + model.DueAmount + ", '" + model.Memo; cmd.CommandText += "'," + model.FinancialBankAccountID + ", " + model.Payment + ", '" + model.CompleteDate + "', '" + model.BankTracking + "', 0,0, " + model.ContractorID + "," + model.UnitID + ")"; try { cmd.ExecuteNonQuery(); operations.Add(model); } catch (Exception ex) { LogException(ex.Message + cmd.CommandText.Replace("'", "")); } } //loadBatch.operations = operations; } catch (Exception ex) { LogException(ex.Message); } finally { sqlConn.Close(); } ViewBag.TotalPayment = 0; ViewBag.TotalDeposit = 0; ViewBag.TotalBalace = 0; return(View(operations)); } else { ModelState.AddModelError("File", "This file format is not supported"); LogException("This file format is not supported"); return(View()); } } else { ModelState.AddModelError("File", "Please Upload Your file"); LogException("This file format is not supported"); } } return(View()); }