public bool didMemberAttendMultiMeetings(List<Meeting> meetings, Member member)
{
foreach (Meeting meeting in meetings)
{
if (!didMemberAttendSingleMeeting(meeting, member))
{
return false;
}
}
return true;
}
public bool didMemberAttendSingleMeeting(Meeting meeting, Member member)
{
string query = "SELECT COUNT(*) FROM attendance WHERE member_id = " + member.Id + " AND meeting_id = " + meeting.Id + "";
int returnCount = 0;
using (SqlCeConnection con = this.connectionManager.getConnection())
{
con.Open();
using (SqlCeCommand command = new SqlCeCommand(query, con))
{
command.CommandType = CommandType.Text;
returnCount = (Int32)command.ExecuteScalar();
}
}
return returnCount > 0 ? true : false;
}
public double getMemberAttendanceRate(Member member)
{
MeetingImpl meetingImpl = new MeetingImpl();
int supposedMeetingsCount = 0;
int attendedMeetingsCount = 0;
foreach (Meeting meeting in meetingImpl.getAllMeetings())
{
if (DateTime.Compare(meeting.Date, member.EntryDate) >= 0)
{
supposedMeetingsCount++;
if (didMemberAttendSingleMeeting(meeting, member))
{
attendedMeetingsCount++;
}
}
}
return Math.Round(Convert.ToDouble(attendedMeetingsCount / supposedMeetingsCount * 100), 2, MidpointRounding.AwayFromZero);
}
public Member addMember(Member member)
{
string query = "INSERT INTO members (lastname, firstname, sex, city, email, phone, cardnum, entryDate)" +
"VALUES (@lastname, @firstname, @sex, @city, @email, @phone, @cardnum, @entryDate)";
return this.insertOrUpdate(query, member);
}
private Member insertOrUpdate(string query, Member member)
{
try
{
using (SqlCeConnection con = connectionManager.getConnection())
{
con.Open();
using (SqlCeCommand command = new SqlCeCommand(query, con))
{
command.Parameters.AddWithValue("@lastname", member.Lastname);
command.Parameters.AddWithValue("@firstname", member.Firstname);
command.Parameters.AddWithValue("@sex", this.converter.sexToBit(member.Sex));
command.Parameters.AddWithValue("@city", member.City.ToString());
command.Parameters.AddWithValue("@email", member.Email);
command.Parameters.AddWithValue("@phone", member.Phone);
command.Parameters.AddWithValue("@cardnum", member.Cardnum);
command.Parameters.AddWithValue("@entryDate", member.EntryDate);
int affectedRows = command.ExecuteNonQuery();
con.Close();
return affectedRows == 1 ? member : null;
}
}
}
catch (SqlCeException ex)
{
Console.WriteLine("Byapfuye " + ex.Message);
return null;
}
}
private List<Member> getMembers(string query)
{
List<Member> members = new List<Member>();
using (SqlCeConnection con = connectionManager.getConnection())
{
con.Open();
using (SqlCeCommand command = new SqlCeCommand(query, con))
{
SqlCeDataAdapter adapter = new SqlCeDataAdapter(command);
DataTable data = new DataTable();
adapter.Fill(data);
if (data.Rows.Count > 0)
{
foreach (DataRow row in data.Rows)
{
Member member = new Member();
member.Id = int.Parse(row.ItemArray[0].ToString());
member.Lastname = row.ItemArray[1].ToString();
member.Firstname = row.ItemArray[2].ToString();
member.Sex = this.converter.stringToGender(row.ItemArray[3].ToString());
member.City = this.converter.stringToCity(row.ItemArray[4].ToString());
member.Email = row.ItemArray[5].ToString();
member.Phone = row.ItemArray[6].ToString();
member.Cardnum = int.Parse(row.ItemArray[7].ToString());
member.EntryDate = Convert.ToDateTime(row.ItemArray[8].ToString());
members.Add(member);
}
}
}
con.Close();
}
return members;
}
public Member updateMember(Member member)
{
string query = "UPDATE members SET lastname = @lastname, firstname = @firstname, sex = @sex, " +
"city = @city, email = @email, phone = @phone, cardnum = @cardnum, entryDate = @entryDate " +
"WHERE (id = " + member.Id + ")";
return this.insertOrUpdate(query, member);
}
public Attendance(Member member, Meeting meeting)
{
this.Attendee = member;
this.AttendedMeeting = meeting;
}
private void loadDatabaseToolStripMenuItem_Click(object sender, EventArgs e)
{
this.openFileDialog.Title = "Select Excel File";
this.openFileDialog.Multiselect = false;
this.openFileDialog.Filter = "MS Office Excel File(*.xls, *xlsx)|*.xls; *xlsx";
Converter converter = new Converter();
if (this.openFileDialog.ShowDialog() == DialogResult.OK) {
string dirName = Path.GetFullPath(this.openFileDialog.FileName);
FileStream stream = File.Open(dirName, FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
//excelReader.IsFirstRowAsColumnNames = true;
//DataSet result = excelReader.AsDataSet();
if (excelReader.ResultsCount > 0)
{
List<Member> newMembers = new List<Member>();
while (excelReader.Read())
{
Member member = new Member();
member.IsValid = true;
member.Lastname = excelReader.GetString(0);
member.Firstname = excelReader.GetString(1);
if (member.Lastname.Length == 0 || member.Firstname.Length == 0) {
member.IsValid = false;
}
string thisMemberSex = excelReader.GetString(2).ToLower();
try
{
member.Sex = converter.stringToGender(thisMemberSex);
}
catch (Exception) {
member.Sex = Gender.Unspecified;
member.IsValid = false;
}
string thisMemberCity = excelReader.GetString(3).ToLower();
try
{
member.City = converter.stringToCity(thisMemberCity);
}
catch (Exception) {
member.City = Province.Unspecified;
member.IsValid = false;
}
member.Email = excelReader.GetString(4).ToLower();
if (!(member.Email.Contains("@") || member.Email.Contains(".") || member.Email.IndexOf("@") > 0 || member.Email.LastIndexOf(".") > member.Email.IndexOf("@")))
{
member.IsValid = false;
}
member.Phone = excelReader.GetString(5);
try
{
member.Cardnum = excelReader.GetInt16(6);
}
catch (FormatException) {
member.IsValid = false;
}
newMembers.Add(member);
}
this.uploadedMembers = newMembers;
//Create a file upload manager...
ExcelFileUploader fileUploader = new ExcelFileUploader(this);
fileUploader.ShowDialog();
}
excelReader.Close();
}
}
private void btnSave_Click(object sender, EventArgs e)
{
Member newMember;
Member member = new Member();
member.Lastname = this.txtLastname.Text;
member.Firstname = this.txtFirstname.Text;
try
{
member.Sex = this.converter.stringToGender(cbxSex.SelectedItem.ToString());
member.City = this.converter.stringToCity(cbxCity.SelectedItem.ToString());
}
catch (Exception)
{
lblErrorMessage.Text = "Sèxe incorrect...";
}
member.Email = this.txtEmail.Text;
member.Phone = this.txtPhone.Text;
member.Cardnum = int.Parse(this.txtCardnum.Text);
member.EntryDate = this.dtEntryDate.Value;
if (this.newRecord)
{
newMember = memberImpl.addMember(member);
}
else
{
member.Id = this.editMembers[this.activeMemberIndex].Id;
newMember = memberImpl.updateMember(member);
}
if (newMember != null)
{
//This disables selectionChanged event to be triggered
parentForm.TriggerSelectEvent = false;
parentForm.display(memberImpl.getAllMembers());
parentForm.TriggerSelectEvent = true;
this.Close();
}
}