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