i have list of sales of services specific time period (activationdate through enddate). need generate sell report grouping month-year (eg april 2012). each month i'd show how full month of use , how many days.
my class:
public class salemonth { public datetime monthyear { get; set; }//.tostring("y") public int fullmonth { get; set; } public int daysmonth { get; set; } public string totalmonths { { return string.format("{0:n2}", (((fullmonth * 30.5) + daysmonth) / 30.5)); } } } what have tried:
using (companycontext db = new companycontext()) { var salelist = db.mysales.tolist(); datetime = salelist.min(s => s.activationdate), = salelist.max(s => s.enddate); (datetime currdate = from.adddays(-from.day + 1) .addticks(-from.timeofday.ticks); currdate < to; currdate = currdate.addmonths(1)) { var sm = new salemonth { monthyear = currdate, fullmonth = 0, daysmonth = 0 }; var monthsell = salelist.where(p => p.activationdate < currdate.addmonths(1) || p.enddate > currdate); foreach (var sale in monthsell) { if (sale.activationdate.month == sale.enddate.month && sale.activationdate.year == sale.enddate.year) {//eg 4/6/13 - 17/6/13 sm.daysmonth += (sale.enddate.day - sale.activationdate.day + 1); } else { if (sale.activationdate.year == currdate.year && sale.activationdate.month == currdate.month) sm.daysmonth += (currdate.addmonths(1) - sale.activationdate).days; else if (sale.enddate.year == currdate.year && sale.enddate.month == currdate.month) sm.daysmonth += sale.enddate.day; else if(sale.activationdate.date <= currdate && sale.enddate > currdate.addmonths(1)) sm.fullmonth++; } } vm.salemonthlist.add(sm); } } i have feeling i'm missing here , there must more elegant way it.
here picture showing sells , report generated them.
linq contain way group data. start taking @ statement:
// group year-month var rows = s in salelist orderby s.monthyear group s new { year = s.monthyear.year, month = s.monthyear.month }; the above statement take data , group year-month create main key each year-month combination , create group of corresponding salemonth items group.
when grasp that, next step use groups calculate whatever want calculate within each group. so, if looking total fullmonths , daysmonths each year-month, this:
var rowstotals = s in salelist orderby s.monthyear group s new { year = s.monthyear.year, month = s.monthyear.month } grp select new { yearmonth = grp.key.year + " " + grp.key.month, fullmonthtotal = grp.sum (x => x.fullmonth), daysmonthtotal = grp.sum (x => x.daysmonth) }; edit:
after looking again @ you're doing, think more efficient this:
// populate our class time period interested in var startdate = salelist.min (x => x.activationdate); var enddate = salelist.max (x => x.enddate); list<salemonth> salesreport = new list<salemonth>(); for(var = new datetime(startdate.year, startdate.month, 1); <= new datetime(enddate.year, enddate.month, 1); = i.addmonths(1)) { salesreport.add(new salemonth { monthyear = }); } // loop through each month-year foreach(var sr in salesreport) { // sales happen in month var lastdaythismonth = sr.monthyear.addmonths(1).adddays(-1); var sales = s in salelist lastdaythismonth >= s.activationdate, sr.monthyear <= s.enddate select s; // calculate number of days sale spans month var nextmonth = sr.monthyear.addmonths(1); var firstofnextmonth = sr.monthyear.addmonths(1).adddays(-1).day; sr.daysmonth = sales.sum (x => (x.enddate < nextmonth ? x.enddate.day : firstofnextmonth) - (sr.monthyear > x.activationdate ? sr.monthyear.day : x.activationdate.day)); // how many sales occur on entire month sr.fullmonth = sales.where (x => x.activationdate <= sr.monthyear && nextmonth < x.enddate).count (); } 
Comments
Post a Comment