public ActionResult NewMember(int?club_id, int?fiscal_year) { //var identity = (System.Web.HttpContext.Current.User as RegisterLions.MyPrincipal).Identity as RegisterLions.MyIdentity; //var tDistrict_id = identity.User.district_id; var intYear = 0; var intMth = 0; if (fiscal_year == null) { intYear = DateTime.Now.Year; intMth = DateTime.Now.Month; fiscal_year = ProjLib.getFiscalYear(DateTime.Now.Year, DateTime.Now.Month); } else { intYear = (int)fiscal_year - 543; intMth = 7; } //ViewBag.fiscal_year = "ปีบริหาร " + displayFiscalYear(DateTime.Now.Year, DateTime.Now.Month); //ViewBag.fiscal_year = "ปีบริหาร " + displayFiscalYear(intYear, intMth); DateTime startDate = DateTime.ParseExact(ProjLib.getBegFiscalYear(intYear, intMth), "yyyy/MM/dd", CultureInfo.InvariantCulture); DateTime endDate = DateTime.ParseExact(ProjLib.getEndFiscalYear(intYear, intMth), "yyyy/MM/dd", CultureInfo.InvariantCulture); var memberMovement = (from t in db.MemberMovements where (t.move_sts != 4) && (t.hist_date >= startDate && t.hist_date <= endDate) join m in db.Members on t.member_seq equals m.member_seq join c in db.Clubs on t.club_id equals c.club_id //join d in db.Districts on c.district_id equals d.district_id //where d.district_id == tDistrict_id select t ); if (club_id != null) { memberMovement = memberMovement.Where(x => x.club_id == club_id); } var tClub = (from c in db.Clubs //where c.district_id== identity.User.district_id join t in db.MemberMovements on c.club_id equals t.club_id where (t.move_sts != 4) && (t.hist_date >= startDate && t.hist_date <= endDate) select new { club_id = c.club_id, club_name_thai = c.club_name_thai } ).Distinct(); SelectList listClub = new SelectList(tClub.OrderBy(x => x.club_name_thai), "club_id", "club_name_thai"); ViewBag.club_id = listClub; var c_fiscal_year = (from m1 in db.MemberMovements where m1.move_sts != 4 && (m1.hist_date.Value.Year >= 2016 && m1.hist_date.Value.Month >= 7) select new { fiscal_year = m1.hist_date.Value.Year + 543 }).Distinct().ToList(); var c_fiscal_year2 = from m in c_fiscal_year select new { m.fiscal_year, fiscal_year_disp = string.Format("ปีบริหาร {0}-{1}", m.fiscal_year, (m.fiscal_year) + 1) }; ViewBag.fiscal_year = new SelectList(c_fiscal_year2.OrderByDescending(x => x.fiscal_year), "fiscal_year", "fiscal_year_disp", fiscal_year); ViewBag.MemberCount = memberMovement.Count(); // Write log to table TransactionLog //ProjLib projlib = new ProjLib(); //ProjLib.writeTransactionLog(identity.User.member_seq, "NewMember", identity.User.club_id); ProjLib.writeTransactionLog(0, "NewMember", 0); return(View(memberMovement)); }
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()); }