public void EditRows()
{
var gridModel = new LeasingDatabase.Models.Grid.OrdersJqGridModel();
var db = new AuleaseEntities();
var e = gridModel.OrdersGrid.GetEditData(); // Edit Row
if (gridModel.OrdersGrid.AjaxCallBackMode == AjaxCallBackMode.EditRow)
{
int Term = Convert.ToInt32(e.RowData["Term"]);
string Type = e.RowData["Type"].ToString();
string RateLevel = e.RowData["RateLevel"].ToString();
decimal Rate = Convert.ToDecimal(e.RowData["Rate"]);
Overhead overhead = new Overhead();
overhead.Term = Term;
overhead.Type = db.Types.Where(n => n.Name == Type).Single();
overhead.RateLevel = RateLevel;
overhead.Rate = Rate;
overhead.BeginDate = DateTime.Now;
db.Overheads.Add(overhead);
db.SaveChanges();
}
if (gridModel.OrdersGrid.AjaxCallBackMode == AjaxCallBackMode.AddRow)
{
// Not implemented
}
if (gridModel.OrdersGrid.AjaxCallBackMode == AjaxCallBackMode.DeleteRow)
{
// Not gonna allow deleting of rates
throw new NotImplementedException();
}
}
public ActionResult FOP(string Fund, string Org, string Program)
{
Session["Fund"] = Fund;
Session["Org"] = Org;
Session["Program"] = Program;
AuleaseEntities db = new AuleaseEntities();
int count = db.Departments.Where(n => n.Fund == Fund && n.Org == Org && n.Program == Program).Count();
if (count < 1)
{
ViewBag.error = "No Department Found";
return(RedirectToAction("Index"));
}
else if (count > 1)
{
ViewBag.error = "Duplicate Departments found...this is a serious issue that needs to be resolved right away";
return(RedirectToAction("Index"));
}
var gridModel = new LeasingDatabase.Models.Grid.BillingCoordinatorJqGridModel();
var ordersGrid = gridModel.OrdersGrid;
SetUpGrid(ordersGrid);
return(View(gridModel));
}
public JsonResult DataRequested()
{
var gridModel = new LeasingDatabase.Models.Grid.OverheadRateJqGridModel();
var db = new AuleaseEntities();
SetUpGrid(gridModel.OrdersGrid);
List <Overhead> overhead = new List <Overhead>();
var Overhead = db.Overheads.Select(n => new { n.TypeId, n.Term, n.RateLevel }).Distinct();
foreach (var over in Overhead)
{
overhead.Add(db.Overheads.Where(n => n.TypeId == over.TypeId && n.Term == over.Term && n.RateLevel == over.RateLevel).OrderByDescending(n => n.BeginDate).FirstOrDefault());
}
var model = overhead.Select(n => new OverheadModel
{
Id = n.Id,
Type = n.Type.Name,
Rate = n.Rate,
RateLevel = n.RateLevel,
Term = n.Term
}).AsQueryable();
return(gridModel.OrdersGrid.DataBind(model));
}
public void EditRows()
{
var gridModel = new LeasingDatabase.Models.Grid.DepartmentJqGridModel();
var db = new AuleaseEntities();
var e = gridModel.OrdersGrid.GetEditData(); // Edit Row
if (gridModel.OrdersGrid.AjaxCallBackMode == AjaxCallBackMode.EditRow)
{
int _Id = Convert.ToInt32(e.RowData["Id"]);
Department dept = db.Departments.Where(n => n.Id == _Id).Single();
string _Fund = e.RowData["Fund"].ToString();
string _Org = e.RowData["Org"].ToString();
string _Program = e.RowData["Program"].ToString();
string _Name = e.RowData["Name"].ToString();
dept.Name = _Name;
dept.Fund = _Fund;
dept.Org = _Org;
dept.Program = _Program;
db.SaveChanges();
}
if (gridModel.OrdersGrid.AjaxCallBackMode == AjaxCallBackMode.AddRow)
{
// Add
}
if (gridModel.OrdersGrid.AjaxCallBackMode == AjaxCallBackMode.DeleteRow)
{
// Not gonna allow deleting of rates
throw new NotImplementedException();
}
}
public ActionResult Edit(string id, string GID, string Amount, string Note, string Fund, string Org, string Program, string Date)
{
AuleaseEntities db = new AuleaseEntities();
int Id = Convert.ToInt32(id);
SingleCharge charge = db.SingleCharges.Where(n => n.Id == Id).Single();
charge.GID = GID;
charge.Price = Convert.ToDecimal(Amount);
charge.Note = Note;
charge.Date = Convert.ToDateTime(Date);
if (db.Departments.Any(n => n.Fund == Fund && n.Org == Org && n.Program == Program))
{
charge.Department = db.Departments.Where(n => n.Fund == Fund && n.Org == Org && n.Program == Program).Single();
}
else
{
Department dept = new Department();
dept.Fund = Fund;
dept.Org = Org;
dept.Program = Program;
dept.Name = "";
db.Departments.Add(dept);
db.SaveChanges();
charge.Department = dept;
}
db.SaveChanges();
return(View());
}
public JsonResult DataRequested()
{
string Fund = Session["Fund"].ToString();
string Org = Session["Org"].ToString();
string Program = Session["Program"].ToString();
var gridModel = new LeasingDatabase.Models.Grid.BillingCoordinatorJqGridModel();
var db = new AuleaseEntities();
SetUpGrid(gridModel.OrdersGrid);
var dept = db.Departments.Where(n => n.Fund == Fund && n.Org == Org && n.Program == Program).Single();
var users = dept.Coordinators;
var model = users.Select(n => new BillingCoordinatorModel
{
Id = n.Id.ToString(),
GID = n.GID,
Fund = Fund,
BillingAccess = n.BillingAccess,
Org = Org,
Program = Program
}).AsQueryable();
return(gridModel.OrdersGrid.DataBind(model));
}
public JsonResult DataRequested()
{
var gridModel = new LeasingDatabase.Models.Grid.BillingJqGridModel();
var db = new AuleaseEntities();
string SN = Session["SN"].ToString();
SetUpGrid(gridModel.OrdersGrid);
var leases = db.Leases.Where(n => n.Component.SerialNumber == SN);
var model = leases.Select(n => new BillingModel
{
Id = n.Id,
BeginDate = n.BeginDate,
EndDate = n.EndDate,
StatementName = n.StatementName,
Timestamp = n.Timestamp,
ContractNumber = n.ContractNumber,
Fund = n.Department.Fund,
Org = n.Department.Org,
Program = n.Department.Program,
RateLevel = n.Overhead.RateLevel,
MonthlyCharge = n.MonthlyCharge.HasValue ? n.MonthlyCharge.Value : 0M, //n.MonthlyCharge??0M,
Term = n.Overhead.Term,
PrimaryCharge = (n.Charges.Where(o => o.Type.Name == "CPU" || o.Type.Name == "Laptop" || o.Type.Name == "Server" || o.Type.Name == "Monitor").Count() > 0) ? n.Charges.Where(o => o.Type.Name == "CPU" || o.Type.Name == "Laptop" || o.Type.Name == "Server" || o.Type.Name == "Monitor").Where(p => p.Price != null).Sum(o => o.Price) : 0M,
SecondaryCharges = (n.Charges.Where(o => o.Type.Name != "CPU" && o.Type.Name != "Laptop" && o.Type.Name != "Server" && o.Type.Name != "Monitor").Count() > 0) ? n.Charges.Where(o => o.Type.Name != "CPU" && o.Type.Name != "Laptop" && o.Type.Name != "Server" && o.Type.Name != "Monitor").Where(p => p.Price != null).Sum(o => o.Price) : 0M
});
return(gridModel.OrdersGrid.DataBind(model));
}
public ActionResult Excel()
{
AuleaseEntities db = new AuleaseEntities();
List <SingleCharge> charges = db.SingleCharges.ToList();
var table = new DataTable("Misc Charges");
table.Columns.Add("Amount", typeof(decimal));
table.Columns.Add("Description", typeof(string));
table.Columns.Add("FOP", typeof(string));
table.Columns.Add("code", typeof(string));
table.Columns.Add("First Name", typeof(string));
table.Columns.Add("Last Name", typeof(string));
table.Columns.Add("Department", typeof(string));
table.Columns.Add("Date", typeof(string));
foreach (var charge in charges)
{
table.Rows.Add(charge.Price, charge.Note, charge.Department.Fund + "-" + charge.Department.Org + "-" + charge.Department.Program, "CSMISC", charge.FirstName, charge.LastName, charge.Department.Name, charge.Date.ToString("d"));
}
MemoryStream ms = new MemoryStream();
using (ExcelPackage pck = new ExcelPackage(ms))
{
ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Charges");
ws.Cells["A1"].LoadFromDataTable(table, true);
ws.Cells.AutoFitColumns();
pck.Save();
return(File(ms.ToArray(), "application/excel", "MiscCharges.xlsx"));
}
}
public static decimal GetIGFRate(int Id)
{
AuleaseEntities db = new AuleaseEntities();
var comp = db.Components.Where(n => n.Id == Id).Single();
return(comp.Leases.OrderByDescending(n => n.EndDate).First().Charges.Where(n => n.Type == comp.Type).Single().VendorRate.Rate);
}
//
// GET: /Test/
public ActionResult Index()
{
AuleaseEntities db = new AuleaseEntities();
var user = db.Users.Where(n => n.GID == "woffojl").FirstOrDefault();
var groups = db.SystemGroups.Where(n => n.User.GID == "woffojl");
foreach (var group in groups)
{
User oldUser = group.User;
if (oldUser != user)
{
group.User = user;
user.SystemGroups.Add(group);
oldUser.SystemGroups.Clear();
}
if (oldUser != user)
{
db.Users.Remove(oldUser);
}
}
db.SaveChanges();
var users = db.Users.GroupBy(n => n.GID).Where(n => n.Count() > 1).Select(n => n.Key).ToList();
ViewBag.Users = users;
return(View());
}
public static string CreateStringSummary(int SystemGroupId)
{
AuleaseEntities db = new AuleaseEntities();
SystemGroup systemGroup = db.SystemGroups.Where(n => n.Id == SystemGroupId).Single();
string summary = "";
List <Lease> leases = systemGroup.Leases.OrderBy(n => n.Component.TypeId).ToList();
string lineBreak = "\n";
int i = 1;
foreach (var lease in leases)
{
summary += "Component " + i.ToString() + ": " + lease.Component.Type.Name + lineBreak;
i++;
decimal TotalCost = lease.Charges.Where(n => n.TypeId == lease.Component.TypeId).Single().Price;
summary += "Total Cost: " + TotalCost.ToString() + lineBreak;
summary += "Leasing Rate: " + CalculateLeasingRate(lease.ComponentId).ToString() + lineBreak;
summary += "Insurance/Shipping/Warranty Rate: " + CalculateSecondaryCosts(lease.ComponentId).ToString("###.###") + lineBreak;
summary += "Overhead Rate: " + lease.Overhead.Rate + lineBreak;
summary += "Vendor Rate: " + lease.Charges.Where(n => n.TypeId == lease.Component.TypeId).Single().VendorRate.Rate + lineBreak;
summary += "Vendor Insurance Rate: " + (lease.Charges.Where(n => n.Type.Name == "Insurance").Count() > 0 ? lease.Charges.Where(n => n.Type.Name == "Insurance").FirstOrDefault().VendorRate.Rate.ToString("###.###") : "NaN") + lineBreak;
summary += "Tax: " + CalculateTax(lease.Component.Id).ToString("##.##") + lineBreak;
summary += "Monthly Charge: " + CalculateMonthlyCharge(lease.Id).ToString("###.###") + lineBreak;
summary += lineBreak;
}
return(summary);
}
// GET api/repairs
public IEnumerable <RepairsViewModel> Get()
{
AuleaseEntities db = new AuleaseEntities();
List <Component> RepairedComps = db.Components.Where(n => n.Repairs.Count > 0).ToList(); // Get Components that have been repaired
List <RepairsViewModel> model = RepairedComps.Select(n => new RepairsViewModel
{
id = n.Repairs.OrderByDescending(o => o.Timestamp).FirstOrDefault().Id,
Name = n.User.FirstName + " " + n.User.LastName,
SerialNumber = n.SerialNumber,
LeaseTag = n.LeaseTag,
GID = n.User.GID,
Type = n.Type.Name,
Make = n.Make.Name,
Model = n.Model.Name,
Completed = n.Repairs.OrderByDescending(o => o.Timestamp).FirstOrDefault().Status.Name == "Completed Repair" ? true: false,
Note = n.Repairs.OrderByDescending(o => o.Timestamp).FirstOrDefault().Note,
Assignee = n.Repairs.OrderByDescending(o => o.Timestamp).FirstOrDefault().Assignee,
DateCreated = n.Repairs.OrderByDescending(o => o.Timestamp).FirstOrDefault().Timestamp.ToString("d"),
PastRepairs = n.Repairs.OrderByDescending(o => o.Timestamp).Skip(1).Select(o => new RepairHistory
{
Note = o.Note,
Date = o.Timestamp.ToString("d"),
Assignee = o.Assignee
}).ToList()
}).ToList();
return(model);
}
public JsonResult DataRequested()
{
var gridModel = new LeasingDatabase.Models.Grid.VendorRateJqGridModel();
var db = new AuleaseEntities();
SetUpGrid(gridModel.OrdersGrid);
List <VendorRate> rates = new List <VendorRate>();
var Types = db.VendorRates.Select(n => new { n.TypeId, n.Term }).Distinct();
foreach (var type in Types)
{
rates.Add(db.VendorRates.Where(n => n.TypeId == type.TypeId && n.Term == type.Term).OrderByDescending(n => n.BeginDate).FirstOrDefault());
}
var model = rates.Select(n => new VendorRateModel
{
Id = n.Id,
Rate = n.Rate,
RateType = n.Type.Name,
Term = n.Term
}).AsQueryable();
return(gridModel.OrdersGrid.DataBind(model));
}
//
// GET: /Home/
public ActionResult Index()
{
AuleaseEntities db = new AuleaseEntities();
ViewData["NewOrders"] = db.SystemGroups.Where(n => n.PO == null).Select(n => n.Order).ToList();
ViewData["Repairs"] = db.Repairs.Where(n => n.Status.Id != Status.CompletedRepair).Count();
ViewData["Tasks"] = db.Tasks.Where(n => n.Status.Id != Status.CompletedTask).ToList();
//List<string[]> NewOrderList = new List<string[]>();
//List<SystemGroup> list = db.SystemGroups.Where(n => n.PO == null).OrderByDescending(n => n.Id).Take(10).OrderBy(n => n.Id).ToList();
//list.Reverse();
//foreach (var item in list)
//{
// string[] array = new string[6];
// array[0] = @Url.Action("Index", "Orders");
// array[1] = item.Order.User.GID + " has placed an order. And sometime later I will add more details about this specific order.";
// array[2] = item.Order.Date.ToString("MMM");
// array[3] = item.Order.Date.Day.ToString();
// array[4] = item.Order.User.GID;
// NewOrderList.Add(array);
//}
//ViewData["NewOrdersList"] = NewOrderList;
return(View(/*ViewData*/));
}
public ActionResult GenerateBilling(string Date)
{
DateTime statementDate = Convert.ToDateTime(Date);
statementDate = new DateTime(statementDate.Year, statementDate.Month, 15);
AuleaseEntities db = new AuleaseEntities();
IEnumerable <Lease> leases = db.Leases.Where(n => n.BeginDate <= statementDate && n.EndDate >= statementDate && n.MonthlyCharge != null);
IEnumerable <Department> tempdepts = leases.Select(n => n.Department).OrderBy(n => n.Fund).ThenBy(n => n.Org).ThenBy(n => n.Program);
IEnumerable <Department> depts = tempdepts.Distinct();
MemoryStream OutputStream = new MemoryStream();
TextWriter tw = new StreamWriter(OutputStream);
foreach (var dept in depts)
{
tw.WriteLine(dept.Fund + "-" + dept.Org + "-" + dept.Program + ", " + dept.Leases.Where(n => n.BeginDate <= statementDate && n.EndDate >= statementDate && n.MonthlyCharge != null).Sum(n => n.MonthlyCharge).ToString());
}
tw.Close();
Response.Clear();
Response.ContentType = "application/octate-stream";
Response.AddHeader("content-disposition", "attachment;filename=" + statementDate.ToString("MMMyy") + ".txt");
Response.AddHeader("Content-length", OutputStream.GetBuffer().Length.ToString());
Response.OutputStream.Write(OutputStream.GetBuffer(), 0, OutputStream.GetBuffer().Length);
Response.End();
return(RedirectToAction("GenerateBilling"));
}
public JsonResult DataRequested()
{
string DateString = Session["date"].ToString();
int Month = Convert.ToInt32(DateString.Substring(0, 2));
int Year = Convert.ToInt32(DateString.Substring(2, 4));
//string Parsed = DateString.Substring(0, 2) + "/" + DateString.Substring(2, 4);
//DateTime date = Convert.ToDateTime(DateString);
var gridModel = new LeasingDatabase.Models.Grid.EOLJqGridModel();
var db = new AuleaseEntities();
SetUpGrid(gridModel.OrdersGrid);
var EOL = db.Components.Where(n => n.ReturnDate.Value.Month == Month && n.ReturnDate.Value.Year == Year);
var model = EOL.Select(n => new EOLModel
{
Id = n.Id,
SN = n.SerialNumber.Substring(n.SerialNumber.Length - 7, n.SerialNumber.Length),
SerialNumber = n.SerialNumber,
ComponentType = n.Type.Name,
Make = n.Make.Name,
Model = n.Model.Name ?? null,
StatementName = n.Leases.OrderByDescending(o => o.Timestamp).FirstOrDefault().StatementName,
GID = n.Leases.OrderByDescending(o => o.Timestamp).FirstOrDefault().SystemGroup.User.GID,
EndBillingDate = n.Leases.OrderByDescending(o => o.EndDate).FirstOrDefault().EndDate,
ReturnDate = n.ReturnDate,
Damages = n.Damages
});
return(gridModel.OrdersGrid.DataBind(model));
}
public static decimal CalculateMonthlyCharge(int LeaseID)
{
AuleaseEntities db = new AuleaseEntities();
Lease lease = db.Leases.Where(n => n.Id == LeaseID).Single();
decimal MonthlyCharge = 0.00M;
List <Charge> charges = lease.Charges.ToList();
foreach (var charge in charges)
{
decimal term = lease.Overhead.Term;
if (ChargedComponent.Contains(charge.Type.Name.ToUpper()))
{
decimal OverheadRate = lease.Overhead.Rate;
decimal tax = charge.Tax.Price;
decimal VendorRate = charge.VendorRate.Rate;
MonthlyCharge = +MonthlyCharge + ((((charge.Price * VendorRate / 1000) * (1 + tax)) + (OverheadRate)) * ((term + 1) / term));
}
else
{
MonthlyCharge = +MonthlyCharge + ((charge.VendorRate.Rate * charge.Price / 1000) * ((term + 1) / term));
}
}
return(MonthlyCharge);
}
public void EditRows()
{
string Fund = Session["Fund"].ToString();
string Org = Session["Org"].ToString();
string Program = Session["Program"].ToString();
var gridModel = new LeasingDatabase.Models.Grid.BillingCoordinatorJqGridModel();
var db = new AuleaseEntities();
var e = gridModel.OrdersGrid.GetEditData(); // Edit Row
if (gridModel.OrdersGrid.AjaxCallBackMode == AjaxCallBackMode.EditRow)
{
// Not gonna allow editing, just adding and deleting
throw new NotImplementedException();
}
if (gridModel.OrdersGrid.AjaxCallBackMode == AjaxCallBackMode.AddRow)
{
string GID = e.RowData["GID"];
bool BillingAccess = Convert.ToBoolean(e.RowData["BillingAccess"]);
Coordinator user;
if (db.Coordinators.Any(n => n.GID == GID))
{
user = db.Coordinators.Where(n => n.GID == GID).Single();
}
else
{
user = new Coordinator();
user.BillingAccess = BillingAccess;
user.GID = GID;
}
Department dept;
if (!db.Departments.Any(n => n.Fund == Fund && n.Org == Org && n.Program == Program))
{
Department newDept = new Department();
newDept.Fund = Fund;
newDept.Org = Org;
newDept.Program = Program;
newDept.Name = "New Department";
db.Departments.Add(newDept);
db.SaveChanges();
}
dept = db.Departments.Where(n => n.Fund == Fund && n.Org == Org && n.Program == Program).Single();
dept.Coordinators.Add(user);
db.SaveChanges();
}
if (gridModel.OrdersGrid.AjaxCallBackMode == AjaxCallBackMode.DeleteRow)
{
int Id = Convert.ToInt32(e.RowData["Id"]);
var dept = db.Departments.Where(n => n.Fund == Fund && n.Org == Org && n.Program == Program).Single();
Coordinator user = db.Coordinators.Where(n => n.Id == Id).Single();
dept.Coordinators.Remove(user);
db.SaveChanges();
}
}
public ActionResult Editor()
{
AuleaseEntities db = new AuleaseEntities();
string form = db.FormTemplates.OrderByDescending(n => n.Id).First().HTML.Replace(System.Environment.NewLine, @"\r\n");
ViewBag.HTML = form;//HttpUtility.HtmlEncode(form);
return(View());
}
public ActionResult EditTask(int Id)
{
AuleaseEntities db = new AuleaseEntities();
Task task = db.Tasks.Where(n => n.Id == Id).Single();
ViewBag.Task = task;
return(View());
}
public ActionResult Vendor()
{
AuleaseEntities db = new AuleaseEntities();
List <VendorEmail> emails = db.VendorEmails.ToList();
ViewBag.Emails = emails;
return(View());
}
public ActionResult EditEmail(int Id)
{
AuleaseEntities db = new AuleaseEntities();
VendorEmail email = db.VendorEmails.Where(n => n.Id == Id).Single();
ViewBag.Email = email;
return(View());
}
//
// GET: /SingleCharge/
public ActionResult Index()
{
AuleaseEntities db = new AuleaseEntities();
List <SingleCharge> charges = db.SingleCharges.OrderByDescending(n => n.Date).ToList();
ViewBag.Charges = charges;
return(View());
}
//
// GET: /Email/
public ActionResult Index()
{
AuleaseEntities db = new AuleaseEntities();
DateTime StatementMonth = db.Statements.OrderByDescending(n => n.Date).First().Date;
ViewBag.StatementMonth = StatementMonth.AddMonths(1).ToString("MMMM");
return(View());
}
public ActionResult FormGenerate(string tag)
{
AuleaseEntities db = new AuleaseEntities();
@ViewBag.HTML = db.FormTemplates.OrderByDescending(n => n.Id).FirstOrDefault().HTML;
@ViewBag.Tag = tag;
return(View());
}
//
// GET: /Task/
public ActionResult Index()
{
AuleaseEntities db = new AuleaseEntities();
List <Task> Tasks = db.Tasks.Where(n => n.Status.Id != Status.CompletedTask).ToList();
ViewBag.Tasks = Tasks;
return(View());
}
public ActionResult ChangeEmail(int Id, string Email)
{
AuleaseEntities db = new AuleaseEntities();
VendorEmail email = db.VendorEmails.Where(n => n.Id == Id).Single();
email.EmailAddress = Email;
db.SaveChanges();
return(RedirectToAction("Vendor"));
}
public ActionResult Download(int Id)
{
AuleaseEntities db = new AuleaseEntities();
Component comp = db.Components.Where(n => n.Id == Id).Single();
byte[] fileBytes = GetBytes(comp.Signature.MIME);
return(File(fileBytes, "image/png", "sig.png"));
}
public ActionResult RemoveEmail(int Id)
{
AuleaseEntities db = new AuleaseEntities();
VendorEmail email = db.VendorEmails.Where(n => n.Id == Id).Single();
db.VendorEmails.Remove(email);
db.SaveChanges();
return(RedirectToAction("Vendor"));
}
public static decimal CalculateChargedRate(int Id)
{
AuleaseEntities db = new AuleaseEntities();
var comp = db.Components.Where(n => n.Id == Id).Single();
var overhead = comp.Leases.OrderByDescending(n => n.EndDate).First().Overhead.Rate;
return(overhead);
}