public bool wasPresent(int memberId, DateTime date, Member.memberType type)
{
bool present = false;
foreach (List<string> data in database.retrieveData("select 1 from lds_frequency where member_id = @member_id and created_by = @date and type_id = @type ", memberId, common.convertDate(date, true), (int)type))
{
present = true;
}
return present;
}
public ActionResult SearchMembers(int typeId, string date)
{
if (!isAuthenticated())
return new HttpUnauthorizedResult();
else
{
Member member = new Member();
return Json(member.getMembers((Member.memberType)typeId, loggedUser.Unit, new Common().convertDate(date.Replace("/", "-"), true)), JsonRequestBehavior.AllowGet);
}
}
public ActionResult SearchMember(string keyword)
{
if (!isAuthenticated())
return new HttpUnauthorizedResult();
else
{
Member member = new Member();
return Json(member.getMembers(keyword, loggedUser.Unit), JsonRequestBehavior.AllowGet);
}
}
public ActionResult Index(MemberViewModel model, string gender, string unitMember, string priesthood)
{
if (!isAuthenticated() || !loggedUser.isAdmin)
return new HttpUnauthorizedResult();
else
{
if (ModelState.IsValid)
{
//new Common().convertDate(date.Replace("/", "-")
Result result = new Member().addMember(model.FirstName, model.LastName, model.BirthDate, gender, new Common().convertBool(unitMember), priesthood, loggedUser.Unit);
if (result.Success)
return RedirectToAction("Success");
else
ModelState.AddModelError("", "Ocorreu um erro ao tentar cadastrar novo membro");
}
return View(model);
}
}
public bool setPresent(int memberId, DateTime date, Member.memberType listType, bool present)
{
MySqlCommand cmd = null;
if (present)
{
cmd = new MySqlCommand("insert into lds_frequency (member_id, created_by, type_id) values (@id, @date, @type)");
cmd.Parameters.AddWithValue("id", memberId);
cmd.Parameters.AddWithValue("date", common.convertDate(date, true));
cmd.Parameters.AddWithValue("type", (int)listType);
}
else
{
cmd = new MySqlCommand("delete from lds_frequency where member_id = @id and created_by = @date and type_id = @type");
cmd.Parameters.AddWithValue("id", memberId);
cmd.Parameters.AddWithValue("date", common.convertDate(date, true));
cmd.Parameters.AddWithValue("type", (int)listType);
}
return database.executeQuery(cmd).Success;
}
public ActionResult Edit(int id)
{
if (!isAuthenticated() || !loggedUser.isAdmin)
return new HttpUnauthorizedResult();
else
{
Member member = new Member().getMember(id, loggedUser.Unit);
MemberViewModel model = new MemberViewModel();
model.Id = member.Id;
model.BirthDate = member.BirthDate;
model.FirstName = member.FirstName;
model.Gender = member.Gender;
model.IsUnitMember = (member.isUnitMember) ? "1" : "0";
model.LastName = member.LastName;
model.PriesthoodOption = member.priesthood.ToString();
return View(model);
}
}
/// <summary>
/// File format: first;last;1982-12-23;M
/// first;last;1989-07-12;F
/// </summary>
/// <param name="file"></param>
/// <param name="unitId"></param>
/// <returns></returns>
public Result execute(StreamReader file, int unitId)
{
Result result = new Result(true);
int errors = 0;
if (file != null)
{
string line;
// Read the file and display it line by line.
while ((line = file.ReadLine()) != null)
{
string[] data = line.Split(',');
if (data.Length >= 4)
{
string firstName = data[1].Trim();
string lastName = data[0].Trim();
DateTime birthDate = common.convertDate(data[2].Trim(), true);
string gender = data[3].Trim();
int priesthood = 0;
if (data.Length > 4)
priesthood = common.convertNumber(data[4].Trim());
Member member = new Member().findMember(lastName, firstName, birthDate, gender, unitId);
if (member == null || member.Id == 0)
{
MySqlCommand cmd = new MySqlCommand("insert into lds_member (first_name, last_name, birthdate, unit_id, created_by, active, restricted, gender, unit_member) values " +
"(@first_name, @last_name, @birthdate, @unit_id, now(), 1, 0, @gender, 1)");
cmd.Parameters.AddWithValue("first_name", firstName);
cmd.Parameters.AddWithValue("last_name", lastName);
cmd.Parameters.AddWithValue("birthdate", birthDate);
cmd.Parameters.AddWithValue("unit_id", unitId);
cmd.Parameters.AddWithValue("gender", gender);
result = database.executeQuery(cmd);
if (result.Success)
{
if (gender == "M" && priesthood != 0)
{
result = new Priesthood().addPriesthood(result.Id, priesthood);
if (!result.Success)
errors++;
}
}
else
errors++;
}
else
{
int priestH = new Priesthood().getMemberPriesthood(member.Id);
if (priestH != priesthood)
{
result = new Priesthood().deletePriesthood(member.Id);
if (result.Success)
{
result = new Priesthood().addPriesthood(member.Id, priesthood);
if (!result.Success)
errors++;
}
else
errors++;
}
}
}
}
file.Close();
}
if (errors > 0)
result.Success = false;
return result;
}
public List<Report> getReport(Member.memberType type, int year, int unitId)
{
string sql = "select " +
" m.last_name, " +
" m.first_name, " +
" DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(m.birthdate, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(m.birthdate, '00-%m-%d')) AS age, " +
" (select count(0) from lds_frequency where month(created_by) = 1 and year(created_by) = " + year + " and member_id = m.id and type_id = " + (int)type + ") jan, " +
" (select count(0) from lds_frequency where month(created_by) = 2 and year(created_by) = " + year + " and member_id = m.id and type_id = " + (int)type + ") feb, " +
" (select count(0) from lds_frequency where month(created_by) = 3 and year(created_by) = " + year + " and member_id = m.id and type_id = " + (int)type + ") mar, " +
" (select count(0) from lds_frequency where month(created_by) = 4 and year(created_by) = " + year + " and member_id = m.id and type_id = " + (int)type + ") apr, " +
" (select count(0) from lds_frequency where month(created_by) = 5 and year(created_by) = " + year + " and member_id = m.id and type_id = " + (int)type + ") may, " +
" (select count(0) from lds_frequency where month(created_by) = 6 and year(created_by) = " + year + " and member_id = m.id and type_id = " + (int)type + ") jun, " +
" (select count(0) from lds_frequency where month(created_by) = 7 and year(created_by) = " + year + " and member_id = m.id and type_id = " + (int)type + ") jul, " +
" (select count(0) from lds_frequency where month(created_by) = 8 and year(created_by) = " + year + " and member_id = m.id and type_id = " + (int)type + ") aug, " +
" (select count(0) from lds_frequency where month(created_by) = 9 and year(created_by) = " + year + " and member_id = m.id and type_id = " + (int)type + ") sep, " +
" (select count(0) from lds_frequency where month(created_by) = 10 and year(created_by) = " + year + " and member_id = m.id and type_id = " + (int)type + ") oct, " +
" (select count(0) from lds_frequency where month(created_by) = 11 and year(created_by) = " + year + " and member_id = m.id and type_id = " + (int)type + ") nov, " +
" (select count(0) from lds_frequency where month(created_by) = 12 and year(created_by) = " + year + " and member_id = m.id and type_id = " + (int)type + ") as \"dec\", " +
" m.unit_member " +
"from lds_member m ";
string sqlCont = " where ";
if (type == Member.memberType.sacramental)
sqlCont = "";
switch (type)
{
case Member.memberType.elder:
// male ages 18+ minus high priest or elder
sqlCont += " gender = 'M' and " +
"DATEDIFF(CURRENT_DATE, birthdate) >= (18 * 365.25) and id not in (select member_id from lds_priesthood where reference = 5) ";
break;
case Member.memberType.highPriest:
// male with high priest
sqlCont += " id in (select member_id from lds_priesthood where reference = 5) ";
break;
case Member.memberType.reliefSociety:
// female ages 18+
sqlCont += " gender = 'F' and " +
"DATEDIFF(CURRENT_DATE, birthdate) >= (18 * 365.25) ";
break;
case Member.memberType.sundaySchool:
// members 18+
sqlCont += " DATEDIFF(CURRENT_DATE, birthdate) >= (18 * 365.25) ";
break;
case Member.memberType.sundaySchoolYouth:
// members ages 12 to 17
sqlCont += " DATEDIFF(CURRENT_DATE, birthdate) >= (12 * 365.25) and " +
"DATEDIFF(CURRENT_DATE, birthdate) < (18 * 365.25) ";
break;
case Member.memberType.primary:
// members age 0 to 11
sqlCont += " DATEDIFF(CURRENT_DATE, birthdate) < (12 * 365.25) ";
break;
case Member.memberType.youngMen:
// men age 12 to 17
sqlCont += " gender = 'M' and " +
"DATEDIFF(CURRENT_DATE, birthdate) >= (12 * 365.25) and " +
"DATEDIFF(CURRENT_DATE, birthdate) < (18 * 365.25) ";
break;
case Member.memberType.youngWomen:
// women age 12 to 17
sqlCont += " gender = 'F' and " +
"DATEDIFF(CURRENT_DATE, birthdate) >= (12 * 365.25) and " +
"DATEDIFF(CURRENT_DATE, birthdate) < (18 * 365.25) ";
break;
}
if (sqlCont != "")
sqlCont += " and m.unit_id = @unit_id ";
else
sqlCont += " where m.unit_id = @unit_id ";
sql += sqlCont + " order by m.last_name, m.first_name";
List<Report> reportData = new List<Report>();
foreach (List<string> data in database.retrieveData(sql, unitId))
{
Report report = new Report();
report.lastName = data[0];
report.firstName = data[1];
report.age = common.convertNumber(data[2]);
report.jan = common.convertNumber(data[3]);
report.feb = common.convertNumber(data[4]);
report.mar = common.convertNumber(data[5]);
report.apr = common.convertNumber(data[6]);
report.may = common.convertNumber(data[7]);
report.jun = common.convertNumber(data[8]);
report.jul = common.convertNumber(data[9]);
report.aug = common.convertNumber(data[10]);
report.sep = common.convertNumber(data[11]);
report.oct = common.convertNumber(data[12]);
report.nov = common.convertNumber(data[13]);
report.dec = common.convertNumber(data[14]);
report.isUnitMember = common.convertBool(data[15]);
report.memberType = type;
reportData.Add(report);
}
return reportData;
}
public List<Member> getMembers(string keyword, int unitId)
{
keyword = "%" + keyword + "%";
List<Member> members = new List<Member>();
foreach (List<string> data in database.retrieveData("SELECT id, first_name, last_name, " + common.formatDate("birthdate") + ", unit_id, " + common.formatDate("created_by") + ", member_record, active, restricted, gender, unit_member " +
"FROM bakeappdb.lds_member " +
"where unit_id = @unit " +
" and (first_name like @first_name " +
" or last_name like @last_name " +
" or birthdate like @birthdate " +
" or member_record like @member_record) " +
"order by last_name, first_name", unitId, keyword, keyword, keyword, keyword))
{
Member member = new Member();
member.Id = common.convertNumber(data[0]);
member.FirstName = data[1];
member.LastName = data[2];
member.BirthDate = common.convertDate(data[3]);
member.Date = common.convertDate(data[5]);
member.MemberRecord = data[6];
member.Actived = common.convertBool(data[7]);
member.Restricted = common.convertBool(data[8]);
member.Gender = data[9];
member.isUnitMember = common.convertBool(data[10]);
members.Add(member);
}
return members;
}
public List<Member> getMembers(memberType type, int unitId, DateTime date = new DateTime())
{
List<Member> members = new List<Member>();
string sql = "SELECT id, first_name, last_name, " + common.formatDate("birthdate") +
", unit_id, " + common.formatDate("created_by") + ", member_record, (select count(0) from lds_frequency where created_by >= (curdate() - INTERVAL 3 MONTH) and member_id = m.id) presencas, restricted, gender, unit_member " +
"FROM bakeappdb.lds_member m where unit_id = @unit ";
string sqlCont = string.Empty;
switch (type)
{
case memberType.active:
// check last three months
sqlCont += " and id in (select member_id from lds_frequency where created_by >= (curdate() - INTERVAL 3 MONTH)) ";
break;
case memberType.absent:
// if absent last sunday
DateTime sunday = common.getLastSundayDate();
sqlCont += string.Format(" and id not in (select member_id from lds_frequency where created_by >= '{0}-{1}-{2}') ",
sunday.Year, sunday.Month.ToString().PadLeft(2, '0'), sunday.Day.ToString().PadLeft(2, '0'));
break;
case memberType.beehive:
// female ages 12 to 13
sqlCont += " and gender = 'F' and " +
"DATEDIFF(CURRENT_DATE, birthdate) >= (12 * 365.25) and " +
"DATEDIFF(CURRENT_DATE, birthdate) < (14 * 365.25) ";
break;
case memberType.miaMaid:
// female ages 14 to 15
sqlCont += " and gender = 'F' and " +
"DATEDIFF(CURRENT_DATE, birthdate) >= (14 * 365.25) and " +
"DATEDIFF(CURRENT_DATE, birthdate) < (16 * 365.25) ";
break;
case memberType.laurel:
// female ages 16 to 17
sqlCont += " and gender = 'F' and " +
"DATEDIFF(CURRENT_DATE, birthdate) >= (16 * 365.25) and " +
"DATEDIFF(CURRENT_DATE, birthdate) < (18 * 365.25) ";
break;
case memberType.deacon:
// male ages 12 to 13
sqlCont += " and id in (select member_id from lds_priesthood where reference = 1) ";
break;
case memberType.teacher:
// male ages 14 to 15
sqlCont += " and id in (select member_id from lds_priesthood where reference = 2) ";
break;
case memberType.priest:
// male ages 16 to 17
sqlCont += " and id in (select member_id from lds_priesthood where reference = 3) ";
break;
case memberType.elder:
// male ages 18+ minus high priest or elder
sqlCont += " and gender = 'M' and " +
"DATEDIFF(CURRENT_DATE, birthdate) >= (18 * 365.25) and id not in (select member_id from lds_priesthood where reference = 5) ";
break;
case memberType.highPriest:
// male with high priest
sqlCont += " and id in (select member_id from lds_priesthood where reference = 5) ";
break;
case memberType.reliefSociety:
// female ages 18+
sqlCont += " and gender = 'F' and " +
"DATEDIFF(CURRENT_DATE, birthdate) >= (18 * 365.25) ";
break;
case memberType.sundaySchool:
// members 18+
sqlCont += " and DATEDIFF(CURRENT_DATE, birthdate) >= (18 * 365.25) ";
break;
case memberType.sundaySchoolYouth:
// members ages 12 to 17
sqlCont += " and DATEDIFF(CURRENT_DATE, birthdate) >= (12 * 365.25) and " +
"DATEDIFF(CURRENT_DATE, birthdate) < (18 * 365.25) ";
break;
case memberType.primary:
// members age 0 to 11
sqlCont += " and DATEDIFF(CURRENT_DATE, birthdate) < (12 * 365.25) ";
break;
case memberType.youngMen:
// men age 12 to 17
sqlCont += " and gender = 'M' and " +
"DATEDIFF(CURRENT_DATE, birthdate) >= (12 * 365.25) and " +
"DATEDIFF(CURRENT_DATE, birthdate) < (18 * 365.25) ";
break;
case memberType.youngWomen:
// women age 12 to 17
sqlCont += " and gender = 'F' and " +
"DATEDIFF(CURRENT_DATE, birthdate) >= (12 * 365.25) and " +
"DATEDIFF(CURRENT_DATE, birthdate) < (18 * 365.25) ";
break;
case memberType.adultsMen:
// men age 18+
sqlCont += " and gender = 'M' and " +
"DATEDIFF(CURRENT_DATE, birthdate) >= (18 * 365.25) ";
break;
}
sql += sqlCont + " order by last_name, first_name";
foreach (List<string> data in database.retrieveData(sql, unitId))
{
Member member = new Member();
member.Id = common.convertNumber(data[0]);
member.FirstName = data[1];
member.LastName = data[2];
member.BirthDate = common.convertDate(data[3]);
member.Date = common.convertDate(data[5]);
member.MemberRecord = data[6];
member.Actived = (data[7] != "0");
member.Restricted = common.convertBool(data[8]);
member.Gender = data[9];
member.Present = new Frequency().wasPresent(member.Id, date, type);
member.isUnitMember = common.convertBool(data[10]);
members.Add(member);
}
members = members.OrderByDescending(m => m.Actived).ThenBy(m => m.LastName).ThenBy(m => m.FirstName).ToList();
return members;
}
public Member getMember(int id, int unitId)
{
Member member = new Member();
foreach (List<string> data in database.retrieveData("SELECT id, first_name, last_name, " + common.formatDate("birthdate") + ", unit_id, " + common.formatDate("created_by") + ", member_record, active, restricted, gender, unit_member " +
"FROM bakeappdb.lds_member " +
"where id = @id and unit_id = @unit_id " +
"order by last_name, first_name", id, unitId))
{
member.Id = common.convertNumber(data[0]);
member.FirstName = data[1];
member.LastName = data[2];
member.BirthDate = common.convertDate(data[3]);
member.Date = common.convertDate(data[5]);
member.MemberRecord = data[6];
member.Actived = common.convertBool(data[7]);
member.Restricted = common.convertBool(data[8]);
member.Gender = data[9];
member.isUnitMember = common.convertBool(data[10]);
member.priesthood = new Priesthood().getMemberPriesthood(member.Id);
}
return member;
}
public List<Member> parseFile(string fileContent)
{
List<Member> members = new List<Member>();
foreach (string line in fileContent.Replace("/r/n", "|").Split('|'))
{
string[] memberData = line.Split(',');
if (memberData.Length > 2)
{
Member member = new Member();
member.LastName = memberData[0].Split('-')[0];
member.FirstName = memberData[0].Split('-')[1];
member.Gender = memberData[1];
int day = Convert.ToInt32(memberData[2].Split('/')[0]);
int month = Convert.ToInt32(memberData[2].Split('/')[1]);
int year = Convert.ToInt32(memberData[2].Split('/')[2]);
member.BirthDate = new DateTime(year, month, day);
members.Add(member);
}
}
return members;
}
public Calling get(int id, int unitId)
{
Calling call = null;
foreach (List<string> data in database.retrieveData("select cm.id, cm.calling_id, cm.member_id, cm.other, " + common.formatDate("cm.calling_date") + ", cm.calling_flag " +
"from lds_calling_member cm, lds_member m where cm.member_id = m.id and cm.id = @id and m.unit_id = @unit_id", id, unitId))
{
call = new Calling();
call.Id = common.convertNumber(data[0]);
call.Name = getCallingName(common.convertNumber(data[1]));
call.CallingId = common.convertNumber(data[1]);
Member member = new Member().getMember(common.convertNumber(data[2]), unitId);
call.MemberId = common.convertNumber(data[2]);
call.Member = member.FirstName + " " + member.LastName;
call.Other = data[3];
call.Date = common.convertDate(data[4]);
call.CallingFlag = common.convertBool(data[5]);
}
return call;
}
public List<Calling> getNextNMonths(int nInterval, int unitId)
{
List<Calling> list = new List<Calling>();
foreach (List<string> data in database.retrieveData("select cm.id, cm.calling_id, cm.member_id, cm.other, " + common.formatDate("cm.calling_date") + ", cm.calling_flag " +
"from lds_calling_member cm, lds_member m where cm.member_id = m.id and cm.calling_date >= date_sub(curdate(), interval 1 day) and cm.calling_date < DATE_ADD(curdate(), INTERVAL " + nInterval + " MONTH) " +
" and m.unit_id = @unit_id order by cm.calling_date asc", unitId))
{
Calling call = new Calling();
call.Id = common.convertNumber(data[0]);
call.Name = getCallingName(common.convertNumber(data[1]));
Member member = new Member().getMember(common.convertNumber(data[2]), unitId);
call.Member = member.FirstName + " " + member.LastName;
call.Other = data[3];
call.Date = common.convertDate(data[4]);
call.CallingFlag = common.convertBool(data[5]);
list.Add(call);
}
return list;
}
public List<Calling> getAllCallings(int unit_id)
{
List<Calling> list = new List<Calling>();
foreach (List<string> data in database.retrieveData("select cm.id, cm.calling_id, cm.member_id, cm.other, " + common.formatDate("cm.calling_date") + ", cm.calling_flag " +
" from lds_calling_member cm, lds_member m " +
" where cm.member_id = m.id " +
" and m.unit_id = @unit_id " +
"order by cm.calling_date asc", unit_id))
{
Calling call = new Calling();
call.Id = common.convertNumber(data[0]);
call.Name = getCallingName(common.convertNumber(data[1]));
Member member = new Member().getMember(common.convertNumber(data[2]), unit_id);
call.Member = member.FirstName + " " + member.LastName;
call.Other = data[3];
call.Date = common.convertDate(data[4]);
call.CallingFlag = common.convertBool(data[5]);
list.Add(call);
}
return list;
}