public ActionResult gridAddOrUpdate(setfee setfee)
{
if (setFeeExists(setfee))
{
return(Json(new { success = false, message = "Record Already Exists" }, JsonRequestBehavior.AllowGet));
}
if (setfee.id.CompareTo(0) > 0)
{
feeEntities db1 = new feeEntities();
if (setfee.amount < 0)
{
setfee setfee1 = db.setfees.Find(setfee.id);
db.setfees.Remove(setfee1);
}
else
{
long previousAmount = db1.setfees.Find(setfee.id).amount;
db1.Dispose();
//long previousAmount = setfees.AsQueryable().FirstOrDefault().amount;
if (setfee.amount < previousAmount)
{
return(Json(new { success = false, message = "You cannot decrease amount, try adding concession instead" }, JsonRequestBehavior.AllowGet));
}
db.Entry(setfee).State = EntityState.Modified;
//return Json(new { success = true, message = setfee.id.CompareTo(0)>0 }, JsonRequestBehavior.AllowGet);
}
}
else
{
db.setfees.Add(setfee);
//return Json(new { success = true, message = setfee.id.CompareTo(0) > 0 }, JsonRequestBehavior.AllowGet);
}
db.SaveChanges();
return(Json(new { success = true, message = "Record saved successfully" }, JsonRequestBehavior.AllowGet));
}
protected override void Dispose(bool disposing)
{
db.Dispose();
base.Dispose(disposing);
}
public ActionResult exportToExcelSemWise(
string Quota,
string Caste,
int semid,
int acayearid,
bool?feeExemption)
{
var setfees = db.setfees.Where(s => s.acaYearid.Equals(acayearid) && s.student.semid.Equals(semid));
if (feeExemption.HasValue)
{
setfees = setfees.Where(s => s.student.feeExemption.Equals((bool)feeExemption));
}
setfees = setfees.Where(s =>
s.student.quota.name.Contains(Quota) &&
s.student.caste.name.Contains(Caste));
var setfees1 = setfees.Select(s => new
{
studentid = s.student.id,
acayearid = s.acaYearid,
HTNo = s.student.htno,
Name = s.student.name,
Semester = s.student.sem.name,
Batch = s.student.batch.name,
Quota = s.student.quota.name,
Caste = s.student.caste.name,
}).Distinct().OrderBy(s => s.HTNo);
List <feetype> feeTypesList = db.feetypes.ToList();
List <long> totals = new List <long>();
foreach (feetype ft in feeTypesList)
{
totals.Add((long)0);
totals.Add((long)0);
}
List <Dictionary <string, object> > dues1 = new List <Dictionary <string, object> >();
feeEntities db1 = new feeEntities();
long GrandTotalAmount = 0;
long GrandTotalDue = 0;
foreach (var item in setfees1)
{
Dictionary <string, object> dues = new Dictionary <string, object>();
dues.Add("HTNo", item.HTNo);
dues.Add("Name", item.Name);
dues.Add("Batch", item.Batch);
dues.Add("Quota", item.Quota);
dues.Add("Caste", item.Caste);
long totalAmount = 0;
long totalDue = 0;
int i = 0;
foreach (feetype ft in feeTypesList)
{
long Amount = 0;
long concession = 0;
long payments = 0;
try
{
Amount = db1.setfees.Where(s => s.studentid.Equals(item.studentid) && s.acaYearid.Equals(item.acayearid) && s.feeTypeid.Equals(ft.id)).FirstOrDefault().amount;
concession = (from p in db1.concessions
where p.studentid == item.studentid
where p.acaYearid == item.acayearid
where p.feeTypeid == ft.id
select(long?) p.amount).Sum() ?? 0;
Amount = Amount - concession;
}
catch (Exception e)
{
}
try
{
payments = (from p in db1.payfees
where p.studentid == item.studentid
where p.acaYearid == item.acayearid
where p.feeTypeid == ft.id
select(long?) p.amount).Sum() ?? 0;
}
catch (Exception e)
{
}
long thisDue = Amount - payments;
Amount = Amount >= 0 ? Amount : 0;
thisDue = thisDue >= 0 ? thisDue : 0;
totals[i++] += Amount;
totals[i++] += thisDue;
dues.Add(ft.type, Amount);
dues.Add(ft.type + " Due", thisDue);
totalAmount += Amount;
totalDue += thisDue;
}
dues.Add("Total", totalAmount);
dues.Add("Total Due", totalDue);
if (totalDue > 0)
{
GrandTotalAmount += totalAmount;
GrandTotalDue += totalDue;
dues1.Add(dues);
}
}
totals.Add(GrandTotalAmount);
totals.Add(GrandTotalDue);
MemoryStream memStream = new MemoryStream();
TextWriter writer = new StreamWriter(memStream);
var semester = db.sems.Find(semid).name;
var acayear = db.acayears.Find(acayearid).year;
if (dues1.Count() <= 0)
{
try
{
writer.Write("No dues exist with the provided parameters");
}
catch (Exception e)
{
}
finally
{
writer.Close();
}
return(File(memStream.ToArray(), "application/ms-excel", "DuesList_" + semester + "_" + acayear + ".xls"));
}
int numberOfFields = dues1[0].Count();
db1.Dispose();
StringBuilder html = new StringBuilder();
html.Append("<table border=\"1\"><tr><td colspan=" + numberOfFields + ">");
html.Append("Dues list of ");
html.Append("<b>-" + semester + "-</b>");
html.Append(" for the Academic Year ");
html.Append("<b>-" + acayear + "-</b>");
html.Append("</td></tr></table>");
html.Append("<table border=\"1\"><tr>");
foreach (KeyValuePair <string, object> kv in dues1.First())
{
html.Append("<th>");
html.Append(kv.Key);
html.Append("</th>");
}
html.Append("</tr>");
foreach (Dictionary <string, object> dic in dues1)
{
html.Append("<tr>");
foreach (KeyValuePair <string, object> kv in dic)
{
html.Append("<td>").Append(kv.Value).Append("</td>");
}
html.Append("</tr>");
}
html.Append("<tr>");
html.Append("<td colspan=\"5\" >Total</td>");
foreach (long total in totals)
{
html.Append("<td>" + total + "</td>");
}
html.Append("</tr>");
html.Append("</table>");
try
{
writer.Write(html);
}
catch (Exception e)
{
}
finally
{
writer.Close();
}
return(File(memStream.ToArray(), "application/ms-excel", "DuesList_" + semester + "_" + acayear + ".xls"));
}