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