public ActionResult Index()
{
//test comment
var getcustomerlist = db.Customers.ToList();
getcustomerlist.Insert(0, new Customer {
name = "All", id = 0
});
SelectList list_customer = new SelectList(getcustomerlist, "id", "name");
ViewBag.CustomerListName = list_customer;
var getsegmentlist = db.Segments.ToList();
getsegmentlist.Insert(0, new Segment {
name = "All", id = 0
});
SelectList list_segment = new SelectList(getsegmentlist, "id", "name");
ViewBag.SegmentListName = list_segment;
var getyearlist = db.Years.ToList();
SelectList list_year = new SelectList(getyearlist, "id", "name");
ViewBag.YearListName = list_year;
List <FixedTEViewModel> FixedTElist = new List <FixedTEViewModel>();
var fixedTEs = db.FixedTEs
.OrderBy(m => m.segment_id)
.GroupBy(m => new { m.year, m.Segment, m.Customer }) //m.FixedTE_details.FixedTE_types,
.Select(g => new {
segment_id = g.Key.Segment.id,
segment_name = g.Key.Segment.name,
customer_id = g.Key.Customer.id,
customer_name = g.Key.Customer.name,
year = g.Key.year,
value = g.Sum(x => x.value),
on_invoice = g.Sum(x => x.FixedTE_details.FixedTE_types.id == 1 ? x.value : 0),
off_invoice = g.Sum(x => x.FixedTE_details.FixedTE_types.id == 2 ? x.value : 0)
});
foreach (var item in fixedTEs)
{
if (item.year == DateTime.Now.Year)
{
FixedTEViewModel FixedTEvm = new FixedTEViewModel();
FixedTEvm.customer_id = item.customer_id;
FixedTEvm.customer_name = item.customer_name;
FixedTEvm.segment_id = item.segment_id;
FixedTEvm.segment_name = item.segment_name;
FixedTEvm.year = item.year;
FixedTEvm.On_invoice = item.on_invoice;
FixedTEvm.Off_invoice = item.off_invoice;
FixedTElist.Add(FixedTEvm);
}
}
return(View(FixedTElist));
}
public ActionResult Index(FixedTE FTE, FormCollection form)
{
var getcustomerlist = db.Customers.ToList();
getcustomerlist.Insert(0, new Customer {
name = "All", id = 0
});
SelectList list_customer = new SelectList(getcustomerlist, "id", "name");
ViewBag.CustomerListName = list_customer;
var getsegmentlist = db.Segments.ToList();
getsegmentlist.Insert(0, new Segment {
name = "All", id = 0
});
SelectList list_segment = new SelectList(getsegmentlist, "id", "name");
ViewBag.SegmentListName = list_segment;
var getyearlist = db.Years.ToList();
SelectList list_year = new SelectList(getyearlist, "id", "name");
ViewBag.YearListName = list_year;
//var fixedTEs = db.FixedTEs.Include(f => f.Customer).Include(f => f.Segment).Include(f => f.FixedTE_details);
//var fixedTEs = db.FixedTEs.Include(f => f.Customer).Include(f => f.FixedTE_details);
//if (form["CustomerList"] != null && form["SegmentList"] != null)
//if (form["CustomerList"] != null)
//{
// int intDDL_Customer = Convert.ToInt32(form["CustomerList"].ToString());
//int intDDL_Segment = Convert.ToInt32(form["SegmentList"].ToString());
//fixedTEs = db.FixedTEs.Include(f => f.Customer).Include(f => f.Segment).Include(f => f.FixedTE_details).Where(f => f.customer_id == intDDL_Customer).Where(f => f.segment_id == intDDL_Segment);
// fixedTEs = db.FixedTEs.Include(f => f.Customer).Include(f => f.FixedTE_details).Where(f => f.customer_id == intDDL_Customer);
//}
//return View(fixedTEs.ToList());
List <FixedTEViewModel> FixedTElist = new List <FixedTEViewModel>();
int intDDL_Customer = Convert.ToInt32(form["CustomerList"].ToString());
int intDDL_Segment = Convert.ToInt32(form["SegmentList"].ToString());
int intDDL_Year = Convert.ToInt32(form["YearList"].ToString());
intDDL_Year = getyearlist[intDDL_Year - 1].name;
var fixedTEs = db.FixedTEs
.OrderBy(m => m.segment_id)
.GroupBy(m => new { m.year, m.Segment, m.Customer }) //m.FixedTE_details.FixedTE_types,
.Select(g => new {
segment_id = g.Key.Segment.id,
segment_name = g.Key.Segment.name,
customer_id = g.Key.Customer.id,
customer_name = g.Key.Customer.name,
year = g.Key.year,
value = g.Sum(x => x.value),
on_invoice = g.Sum(x => x.FixedTE_details.FixedTE_types.id == 1 ? x.value : 0),
off_invoice = g.Sum(x => x.FixedTE_details.FixedTE_types.id == 2 ? x.value : 0)
});
// take a searched fixedTEs values from db
if (intDDL_Customer == 0 && intDDL_Segment == 0)
{
foreach (var item in fixedTEs)
{
if (item.year == intDDL_Year)
{
FixedTEViewModel FixedTEvm = new FixedTEViewModel();
FixedTEvm.customer_id = item.customer_id;
FixedTEvm.customer_name = item.customer_name;
FixedTEvm.segment_id = item.segment_id;
FixedTEvm.segment_name = item.segment_name;
FixedTEvm.year = item.year;
FixedTEvm.On_invoice = item.on_invoice;
FixedTEvm.Off_invoice = item.off_invoice;
FixedTElist.Add(FixedTEvm);
}
}
return(View(FixedTElist));
}
else if (intDDL_Customer == 0)
{
foreach (var item in fixedTEs)
{
if (item.segment_id == intDDL_Segment && item.year == intDDL_Year)
{
FixedTEViewModel FixedTEvm = new FixedTEViewModel();
FixedTEvm.customer_id = item.customer_id;
FixedTEvm.customer_name = item.customer_name;
FixedTEvm.segment_id = item.segment_id;
FixedTEvm.segment_name = item.segment_name;
FixedTEvm.year = item.year;
FixedTEvm.On_invoice = item.on_invoice;
FixedTEvm.Off_invoice = item.off_invoice;
FixedTElist.Add(FixedTEvm);
}
}
}
else if (intDDL_Segment == 0)
{
foreach (var item in fixedTEs)
{
if (item.customer_id == intDDL_Customer && item.year == intDDL_Year)
{
FixedTEViewModel FixedTEvm = new FixedTEViewModel();
FixedTEvm.customer_id = item.customer_id;
FixedTEvm.customer_name = item.customer_name;
FixedTEvm.segment_id = item.segment_id;
FixedTEvm.segment_name = item.segment_name;
FixedTEvm.year = item.year;
FixedTEvm.On_invoice = item.on_invoice;
FixedTEvm.Off_invoice = item.off_invoice;
FixedTElist.Add(FixedTEvm);
}
}
}
else
{
foreach (var item in fixedTEs)
{
if (item.customer_id == intDDL_Customer && item.segment_id == intDDL_Segment && item.year == intDDL_Year)
{
FixedTEViewModel FixedTEvm = new FixedTEViewModel();
FixedTEvm.customer_id = item.customer_id;
FixedTEvm.customer_name = item.customer_name;
FixedTEvm.segment_id = item.segment_id;
FixedTEvm.segment_name = item.segment_name;
FixedTEvm.year = item.year;
FixedTEvm.On_invoice = item.on_invoice;
FixedTEvm.Off_invoice = item.off_invoice;
FixedTElist.Add(FixedTEvm);
}
}
}
return(View(FixedTElist));
}
