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());
}