public ActionResult MemberReport() { //var identity = (System.Web.HttpContext.Current.User as RegisterLions.MyPrincipal).Identity as RegisterLions.MyIdentity; ViewBag.fiscal_year = "ปีบริหาร " + ProjLib.displayFiscalYear(DateTime.Now.Year, DateTime.Now.Month); DateTime startDate = DateTime.ParseExact(ProjLib.getBegFiscalYear(DateTime.Now.Year, DateTime.Now.Month), "yyyy/MM/dd", CultureInfo.InvariantCulture); DateTime endDate = DateTime.ParseExact(ProjLib.getEndFiscalYear(DateTime.Now.Year, DateTime.Now.Month), "yyyy/MM/dd", CultureInfo.InvariantCulture); var totMemberList = (from m in db.Members where (m.member_sts == 1) join c in db.Clubs on m.club_id equals c.club_id // where c.district_id == identity.User.district_id select m) .GroupBy(t => t.club_id).Select(grp => new { club_id = grp.Key, totMember = grp.Count() } ).OrderBy(x => x.club_id); var newMemberList = (from mm in db.MemberMovements where (mm.hist_date >= startDate && mm.hist_date <= endDate && mm.move_sts == 1) join c in db.Clubs on mm.club_id equals c.club_id // where c.district_id == identity.User.district_id select mm) .GroupBy(t => t.club_id).Select(grp => new { club_id = grp.Key, newMember = grp.Count() }) .OrderBy(x => x.club_id).ToList(); var transferMemberList = (from mm in db.MemberMovements where (mm.hist_date >= startDate && mm.hist_date <= endDate && mm.move_sts == 2) join c in db.Clubs on mm.club_id equals c.club_id // where c.district_id == identity.User.district_id select mm) .GroupBy(t => t.club_id).Select(grp => new { club_id = grp.Key, transferMember = grp.Count() }) .OrderBy(x => x.club_id).ToList(); var reinstallMemberList = (from mm in db.MemberMovements where (mm.hist_date >= startDate && mm.hist_date <= endDate && mm.move_sts == 3) join c in db.Clubs on mm.club_id equals c.club_id // where c.district_id == identity.User.district_id select mm) .GroupBy(t => t.club_id).Select(grp => new { club_id = grp.Key, reinstallMember = grp.Count() }) .OrderBy(x => x.club_id).ToList(); var dropMemberList = (from mm in db.MemberMovements where (mm.hist_date >= startDate && mm.hist_date <= endDate && mm.move_sts == 4) join c in db.Clubs on mm.club_id equals c.club_id //where c.district_id == identity.User.district_id select mm) .GroupBy(t => t.club_id).Select(grp => new { club_id = grp.Key, dropMember = grp.Count() }) .OrderBy(x => x.club_id).ToList(); List <MemberReport> memberReport = new List <MemberReport>(); // memberReport.Add(new MemberReport(2559, 1, 1, 1, 1, 1, 1)); //MemberReport memberReport = new MemberReport(); foreach (var t in totMemberList) { var newMemer = 0; if (newMemberList.Find(x => x.club_id == t.club_id) != null) { newMemer = newMemberList.Find(x => x.club_id == t.club_id).newMember; } var transferMemer = 0; if (transferMemberList.Find(x => x.club_id == t.club_id) != null) { transferMemer = transferMemberList.Find(x => x.club_id == t.club_id).transferMember; } var reinstallMember = 0; if (reinstallMemberList.Find(x => x.club_id == t.club_id) != null) { reinstallMember = reinstallMemberList.Find(x => x.club_id == t.club_id).reinstallMember; } var dropMember = 0; if (dropMemberList.Find(x => x.club_id == t.club_id) != null) { dropMember = dropMemberList.Find(x => x.club_id == t.club_id).dropMember; } Club club = db.Clubs.Find(t.club_id); var tClubName = ""; if (club != null) { tClubName = club.club_name_thai; if (club.club_sts == 2) { tClubName = tClubName + "(" + club.ClubStatus.club_status_desc + ")"; } } memberReport.Add(new MemberReport(t.club_id, tClubName, newMemer, transferMemer, reinstallMember, dropMember, t.totMember, 0, 0)); } ViewBag.MemberReport = memberReport.OrderBy(x => x.club_name); var zoneClub = (from c1 in db.ZoneClubs join c2 in db.ZoneOfficers on c1.zone_officer_id equals c2.zone_officer_id select c1 ); var searchFisicalYear = ProjLib.getFiscalYear(DateTime.Now.Year, DateTime.Now.Month); zoneClub = zoneClub.Where(x => x.fiscal_year == searchFisicalYear); zoneClub = zoneClub.OrderBy(x => x.ZoneOfficer.zone_no).ThenBy(x => x.club_seq); ViewBag.zoneClub = zoneClub.ToList(); // Write log to table TransactionLog //ProjLib projlib = new ProjLib(); //ProjLib.writeTransactionLog(identity.User.member_seq, "MemberReport", identity.User.club_id); ProjLib.writeTransactionLog(0, "MemberReport", 0); var member = (from m in db.Members where m.member_sts == 1 select m); ViewBag.MemberCount = member.Count(); return(View()); }
public ActionResult Index() { int fiscal_year = ProjLib.getFiscalYear(DateTime.Now.Year, DateTime.Now.Month); var clubofficer = (from co in db.ClubOfficers where co.fiscal_year == fiscal_year && (co.officer_id >= 13 && co.officer_id <= 18) join o in db.Officers on co.officer_id equals o.officer_id select co ).OrderBy(x => x.Officer.officer_grp).ThenBy(x => x.seq_no); List <Officer2Col> officer2col = new List <Officer2Col>(); int i = 0; string imgsrc = "", title = "", name = ""; if (clubofficer.ToList().Count() != 0) { foreach (var c1 in clubofficer) { i++; string imgsrctmp = ""; if (c1.Member.image != null) { var base64 = Convert.ToBase64String(c1.Member.image); imgsrctmp = String.Format("data:image/gif;base64,{0}", base64); } else { imgsrctmp = "~/Pictures/login.jpg"; } if (i % 2 == 1) { imgsrc = imgsrctmp; title = c1.Officer.title; name = c1.Member.full_name; } else { officer2col.Add(new Officer2Col(imgsrc, title, name, imgsrctmp, c1.Officer.title, c1.Member.full_name)); imgsrc = ""; title = ""; name = ""; } } } // Write log to table TransactionLog //ProjLib projlib = new ProjLib(); ProjLib.writeTransactionLog(0, "Index", 0); ViewBag.officer = officer2col; // Statistic for Member DateTime startDate = DateTime.ParseExact(ProjLib.getBegFiscalYear(DateTime.Now.Year, DateTime.Now.Month), "yyyy/MM/dd", CultureInfo.InvariantCulture); DateTime endDate = DateTime.ParseExact(ProjLib.getEndFiscalYear(DateTime.Now.Year, DateTime.Now.Month), "yyyy/MM/dd", CultureInfo.InvariantCulture); var newMember = (from t in db.MemberMovements where (t.move_sts != 4) && (t.hist_date >= startDate && t.hist_date <= endDate) select t ); var dropMember = (from t in db.MemberMovements where t.move_sts == 4 && (t.hist_date >= startDate && t.hist_date <= endDate) select t ); var totMember = (from m in db.Members where m.member_sts == 1 select m); ViewBag.newMember = newMember.ToList().Count(); ViewBag.dropMember = dropMember.ToList().Count(); ViewBag.netMember = newMember.ToList().Count() - dropMember.ToList().Count(); ViewBag.totMember = totMember.ToList().Count(); ViewBag.fiscal_year = "ปีบริหาร " + ProjLib.displayFiscalYear(DateTime.Now.Year, DateTime.Now.Month); //club status return(View()); }