public void UpdateMeetingAttendees(VillageAttendee attendee) { _loanManager.UpdateMeetingAttendees(attendee); }
public List<VillageAttendee> SelectMeetingAttendees(int villageId, DateTime date) { List<VillageAttendee> attendees = new List<VillageAttendee>(); string q = @"SELECT VillagesAttendance.id AS attendee_id, Persons.id AS person_id, Persons.first_name + SPACE(1) + Persons.last_name AS person_name, [date], attended, comment, loan_id FROM dbo.VillagesAttendance INNER JOIN dbo.Persons ON dbo.VillagesAttendance.person_id = dbo.Persons.id WHERE village_id = @village_id AND [date] = @attended_date"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) { c.AddParam("@village_id", villageId); c.AddParam("@attended_date", date); using (OpenCbsReader r = c.ExecuteReader()) { while (r.Read()) { VillageAttendee attendee = new VillageAttendee(); attendee.Id = r.GetInt("attendee_id"); attendee.VillageId = villageId; attendee.TiersId = r.GetInt("person_id"); attendee.PersonName = r.GetString("person_name"); attendee.AttendedDate = r.GetDateTime("date"); attendee.Attended = r.GetBool("attended"); attendee.Comment = r.GetString("comment"); attendee.LoanId = r.GetInt("loan_id"); attendees.Add(attendee); } } } if (attendees.Count == 0) { q = @"SELECT CAST(0 AS int) AS attendee_id, Persons.id AS person_id, Persons.first_name + SPACE(1) + Persons.last_name AS person_name, Installments.expected_date AS [date], CASE WHEN (SELECT COUNT(*) FROM dbo.ContractEvents INNER JOIN dbo.RepaymentEvents ON dbo.ContractEvents.id = dbo.RepaymentEvents.id WHERE ContractEvents.contract_id = c.id AND event_date = @attended_date AND RepaymentEvents.installment_number = Installments.number) > 0 THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END AS attended, '' AS comment, c.id AS loan_id FROM dbo.VillagesPersons vp INNER JOIN dbo.Persons ON vp.person_id = dbo.Persons.id INNER JOIN dbo.Projects pr ON pr.tiers_id = vp.person_id INNER JOIN dbo.Contracts c ON c.project_id = pr.id INNER JOIN dbo.Installments ON Installments.contract_id = c.id WHERE village_id = @village_id AND c.nsg_id IS NOT NULL AND Installments.expected_date = @attended_date AND c.[status] = 5"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) { c.AddParam("@village_id", villageId); c.AddParam("@attended_date", date); using (OpenCbsReader r = c.ExecuteReader()) { while (r.Read()) { VillageAttendee attendee = new VillageAttendee(); attendee.Id = r.GetInt("attendee_id"); attendee.VillageId = villageId; attendee.TiersId = r.GetInt("person_id"); attendee.PersonName = r.GetString("person_name"); attendee.AttendedDate = r.GetDateTime("date"); attendee.Attended = r.GetBool("attended"); attendee.Comment = r.GetString("comment"); attendee.LoanId = r.GetInt("loan_id"); attendees.Add(attendee); } } } } return attendees; }
public void UpdateMeetingAttendees(VillageAttendee attendee) { if (attendee.Id == 0) { const string q = @"INSERT INTO dbo.VillagesAttendance (village_id, person_id, [date], attended, comment, loan_id) VALUES (@village_id, @person_id, @attended_date, @attended, @comment, @loan_id) SELECT SCOPE_IDENTITY()"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) { c.AddParam("@attended", attendee.Attended); c.AddParam("@comment", attendee.Comment); c.AddParam("@village_id", attendee.VillageId); c.AddParam("@person_id", attendee.TiersId); c.AddParam("@attended_date", attendee.AttendedDate); c.AddParam("@loan_id", attendee.LoanId); attendee.Id = Convert.ToInt32(c.ExecuteScalar()); } } else { const string q = @"UPDATE dbo.VillagesAttendance SET attended = @attended, comment = @comment WHERE village_id = @village_id AND person_id = @person_id AND [date] = @attended_date"; using (SqlConnection conn = GetConnection()) using (OpenCbsCommand c = new OpenCbsCommand(q, conn)) { c.AddParam("@attended", attendee.Attended); c.AddParam("@comment", attendee.Comment); c.AddParam("@village_id", attendee.VillageId); c.AddParam("@person_id", attendee.TiersId); c.AddParam("@attended_date", attendee.AttendedDate); c.ExecuteNonQuery(); } } }
private void buttonUpdateAttendence_Click(object sender, EventArgs e) { foreach (OLVListItem item in olvAttendees.Items) { VillageAttendee attendee = new VillageAttendee(); attendee.Id = Convert.ToInt32(item.SubItems[0].Text); attendee.VillageId = _village.Id; attendee.TiersId = Convert.ToInt32(item.SubItems[1].Text); attendee.AttendedDate = ((DateTime)this.comboBoxMeetingDates.SelectedValue).Date; attendee.Attended = Convert.ToBoolean(item.SubItems[3].Text); attendee.Comment = item.SubItems[4].Text; attendee.LoanId = Convert.ToInt32(item.SubItems[5].Text); ServicesProvider.GetInstance().GetContractServices().UpdateMeetingAttendees(attendee); item.SubItems[0].Text = attendee.Id.ToString(); } }