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