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