public static DataTable GetAll()
{
var connector = new Connector("SELECT ID, CongregationName FROM Congregation ORDER BY CongregationName");
var table = connector.ExecuteDataTable();
connector = null;
return table;
}
public static DataTable GetById(int congregationId)
{
var connector = new Connector("SELECT ID, CongregationName FROM Congregation WHERE ID = @CongregationId");
connector.AddParameter("@CongregationId", congregationId);
var table = connector.ExecuteDataTable();
connector = null;
return table;
}
public static DataTable GetDepartmentsTable()
{
var connector = new Connector("SELECT ID, Trade " +
"FROM Trades " +
"ORDER BY Trade");
DataTable table = connector.ExecuteDataTable();
connector = null;
return table;
}
public static void SaveEntry(string description)
{
var query = ("INSERT INTO ActivityLog (Description) " +
"VALUES (@Description)");
var conn = new Connector(query);
conn.AddParameter("@Description", description);
conn.ExecuteNonQuery();
conn = null;
}
public static DataTable GetAllVolunteers()
{
DataTable table = null;
var connector = new Connector(BasicQuery +
" WHERE Volunteers.Surname LIKE 'A%' " +
" ORDER BY Volunteers.Surname ");
table = connector.ExecuteDataTable();
connector = null;
return table;
}
public static DataTable GetByLastName(string lastName, GenderKind gender)
{
var connector = new Connector(BasicQuery +
" WHERE Volunteers.Surname = @Surname " +
// " AND Volunteers.Gender = @Gender " +
" ORDER BY Volunteers.Surname ");
connector.AddParameter("@Surname", lastName);
// connector.AddParameter("@Gender", gender.ToString());
var table = connector.ExecuteDataTable();
connector = null;
return table;
}
public static DataTable GetUnsynchronisedVolunteers()
{
var connector = new Connector(" SELECT " +
" Volunteers.ID, " +
" Volunteers.FirstName, " +
" Volunteers.MiddleName, " +
" Volunteers.Surname " +
" FROM Volunteers " +
" WHERE Volunteers.ID IN(" + DatabaseState.UnsyncIdList + ") " +
" ORDER BY Volunteers.Surname ");
var dataTable = connector.ExecuteDataTable();
return dataTable;
}
public static DataTable GetByDepartment(Department department)
{
var connector = new Connector(" SELECT " +
" Volunteers.ID, " +
" Volunteers.FirstName, " +
" Volunteers.MiddleName, " +
" Volunteers.Surname " +
" FROM Volunteers " +
" INNER JOIN Trades ON (Trades.ID = Volunteers.Trade) " +
" WHERE Trades.ID = @DepartmentId " +
" ORDER BY Volunteers.Surname ");
connector.AddParameter("@DepartmentID", department.ID);
var table = connector.ExecuteDataTable();
connector = null;
return table;
}
private static bool IsActivityLogSynchronised()
{
var connector = new Connector(" SELECT " +
" ActivityLog.ID, " +
" ActivityLog.LogDateTime, " +
" ActivityLog.Description " +
" FROM ActivityLog " +
" WHERE ActivityLog.ID = -1 " +
" ORDER BY ActivityLog.LogDateTime ");
var synced = connector.ExecuteDataTable().Rows.Count == 0;
connector = null;
return synced;
}
private static bool IsVolunteersSynchronised()
{
var connector = new Connector(" SELECT " +
" Volunteers.ID, " +
" Volunteers.FirstName, " +
" Volunteers.MiddleName, " +
" Volunteers.Surname " +
" FROM Volunteers " +
" WHERE Volunteers.ID = -1 " +
" ORDER BY Volunteers.Surname ");
var synced = connector.ExecuteDataTable().Rows.Count == 0;
connector = null;
return synced;
}
public static Department GetById(int id)
{
var connector = new Connector("SELECT ID, Trade " +
"FROM Trades " +
"WHERE Trades.ID = @DepartmentId");
connector.AddParameter("@DepartmentId", id);
DataTable table = connector.ExecuteDataTable();
Department department = null;
if(table.Rows.Count == 1)
{
department = new Department(table.Rows[0]);
}
connector = null;
table = null;
return department;
}
public void SaveToDatabase()
{
var query = "";
if(this.ID == 0)
{
// INSERT into database
query = ("INSERT INTO Volunteers " +
"(TypeOfApplication, KingdomAssemblyHallConstruction, DisasterRelief, " +
" Surname, FirstName, MiddleName, Gender, DateOfBirth, DateOfBaptism, " +
" Address, EMailAddress, HomePhoneNo, WorkPhoneNo, MobilePhoneNo, " +
" CongPrivilege, RegularPioneer, NameOfMate, " +
" Trade1, Experience1, Years1, " +
" Trade2, Experience2, Years2, " +
" Trade3, Experience3, Years3, " +
" Trade4, Experience4, Years4," +
" EmergencyContactName, EmergencyContactRelationship," +
" EmergencyContactPhoneNo, EmergencyContactAddress, " +
" ApplicationDate, ProcessedDate, " +
" CongregationName, " +
" Authenticated, " +
" Trade) " +
"VALUES (@ApplicationKind, @HallConstruction, @DisasterRelief, " +
" @Surname, @FirstName, @MiddleName, @Gender, @DateOfBirth, @DateOfBaptism, " +
" @Address, @EmailAddress, @PhoneNumberHome, @PhoneNumberWork, @PhoneNumberMobile, " +
" @CongPrivilege, @RegularPioneer, @NameOfMate, " +
" @Trade1, @Experience1, @Years1, " +
" @Trade2, @Experience2, @Years2, " +
" @Trade3, @Experience3, @Years3, " +
" @Trade4, @Experience4, @Years4," +
" @EmergencyContactName, @EmergencyContactRelationship, " +
" @EmergencyContactPhoneNumber, @EmergencyContactAddress, " +
" @ApplicationDate, @ProcessedDate, " +
" @CongregationID, " +
" True, " +
" 34) "); // USE 34 for Trade ID (Not Assigned) - INSERT ONLY
}
else
{
// UPDATE database
query = ("UPDATE Volunteers SET " +
" TypeOfApplication = @ApplicationKind, " +
" KingdomAssemblyHallConstruction = @HallConstruction," +
" DisasterRelief = @DisasterRelief," +
" Surname = @Surname, " +
" FirstName = @FirstName, " +
" MiddleName = @MiddleName, " +
" Gender = @Gender, " +
" DateOfBirth = @DateOfBirth, " +
" DateOfBaptism = @DateOfBaptism, " +
" Address = @Address," +
" EMailAddress = @EmailAddress, " +
" HomePhoneNo = @PhoneNumberHome, " +
" WorkPhoneNo = @PhoneNumberWork, " +
" MobilePhoneNo = @PhoneNumberMobile, " +
" CongPrivilege = @CongPrivilege, " +
" RegularPioneer = @RegularPioneer, " +
" NameOfMate = @NameOfMate, " +
" Trade1 = @Trade1, Experience1 = @Experience1, Years1 = @Years1, " +
" Trade2 = @Trade2, " +
" Experience2 = @Experience2, " +
" Years2 = @Years2, " +
" Trade3 = @Trade3, " +
" Experience3 = @Experience3, " +
" Years3 = @Years3, " +
" Trade4 = @Trade4, " +
" Experience4 = @Experience4, " +
" Years4 = @Years4, " +
" EmergencyContactName = @EmergencyContactName, " +
" EmergencyContactRelationship = @EmergencyContactRelationship, " +
" EmergencyContactPhoneNo = @EmergencyContactPhoneNumber, " +
" EmergencyContactAddress = @EmergencyContactAddress, " +
" ApplicationDate = @ApplicationDate, " +
" ProcessedDate = @ProcessedDate, " +
" CongregationName = @CongregationID, " +
" Authenticated = True " +
" WHERE ID = @ID");
}
var connector = new Connector(query);
connector.AddParameter("@ApplicationKind", this.ApplicationKind.GetName());
connector.AddParameter("@HallConstruction", this.FormsOfService.HasFlag(FormOfServiceKinds.HallConstruction));
connector.AddParameter("@DisasterRelief", this.FormsOfService.HasFlag(FormOfServiceKinds.DisasterRelief));
connector.AddParameter("@Surname", this.LastName);
connector.AddParameter("@FirstName", this.FirstName);
connector.AddParameter("@MiddleName", this.MiddleNames);
connector.AddParameter("@Gender", this.Gender.ToString());
connector.AddParameter("@DateOfBirth", this.DateOfBirth);
connector.AddParameter("@DateOfBaptism", this.DateOfBaptism);
connector.AddParameter("@Address", this.Address);
connector.AddParameter("@EmailAddress", this.EmailAddress);
connector.AddParameter("@PhoneNumberHome", this.PhoneNumberHome);
connector.AddParameter("@PhoneNumberWork", this.PhoneNumberWork);
connector.AddParameter("@PhoneNumberMobile", this.PhoneNumberMobile);
connector.AddParameter("@CongPrivilege", this.CongregationPrivileges.GetName());
connector.AddParameter("@RegularPioneer", this.RegularPioneer);
connector.AddParameter("@NameOfMate", this.NameOfMate);
connector.AddParameter("@Trade1", this.WorkBackgroundList[0].TradeOrProfession);
connector.AddParameter("@Experience1", this.WorkBackgroundList[0].TypeOfExprience);
connector.AddParameter("@Years1", this.WorkBackgroundList[0].Years);
connector.AddParameter("@Trade2", this.WorkBackgroundList[1].TradeOrProfession);
connector.AddParameter("@Experience2", this.WorkBackgroundList[1].TypeOfExprience);
connector.AddParameter("@Years2", this.WorkBackgroundList[1].Years);
connector.AddParameter("@Trade3", this.WorkBackgroundList[2].TradeOrProfession);
connector.AddParameter("@Experience3", this.WorkBackgroundList[2].TypeOfExprience);
connector.AddParameter("@Years3", this.WorkBackgroundList[2].Years);
connector.AddParameter("@Trade4", this.WorkBackgroundList[3].TradeOrProfession);
connector.AddParameter("@Experience4", this.WorkBackgroundList[3].TypeOfExprience);
connector.AddParameter("@Years4", this.WorkBackgroundList[3].Years);
if(string.IsNullOrEmpty(this.EmergencyContactName)) this.EmergencyContactName = " ";
if(string.IsNullOrEmpty(this.EmergencyContactRelationship)) this.EmergencyContactRelationship = " ";
if(string.IsNullOrEmpty(this.EmergencyContactPhoneNumber)) this.EmergencyContactPhoneNumber = " ";
if(string.IsNullOrEmpty(this.EmergencyContactAddress)) this.EmergencyContactAddress = " ";
connector.AddParameter("@EmergencyContactName", this.EmergencyContactName);
connector.AddParameter("@EmergencyContactRelationship", this.EmergencyContactRelationship);
connector.AddParameter("@EmergencyContactPhoneNumber", this.EmergencyContactPhoneNumber);
connector.AddParameter("@EmergencyContactAddress", this.EmergencyContactAddress);
connector.AddParameter("@ApplicationDate", this.ApplicationDate);
connector.AddParameter("@ProcessedDate", DateTime.Now.Date);
connector.AddParameter("@CongregationID", this.CongregationID);
connector.AddParameter("@ID", this.ID);
connector.ExecuteNonQuery();
connector = null;
}
public static List<Badge> GetBadgesByDepartment(Department department)
{
var connector = new Connector(" SELECT Volunteers.*, Trades.Trade AS TradeName, Congregation.CongregationName AS Congregation " +
" FROM (Volunteers INNER JOIN Trades ON (Trades.ID = Volunteers.Trade)) INNER JOIN Congregation ON (Congregation.ID = Volunteers.CongregationName) " +
" WHERE Trades.ID = @DepartmentId " +
" ORDER BY Volunteers.Surname");
connector.AddParameter("@DepartmentID", department.ID);
var table = connector.ExecuteDataTable();
var badges = new List<Badge>();
foreach(DataRow row in table.Rows)
{
badges.Add(new Badge(row));
}
connector = null;
return badges;
}
public static Volunteer GetByID(int volunteerId)
{
Volunteer volunteer = null;
var connector = new Connector(BasicQuery +
" WHERE Volunteers.ID = @VolunteerID " +
" ORDER BY Volunteers.Surname ");
connector.AddParameter("@VolunteerID", volunteerId);
var table = connector.ExecuteDataTable();
connector = null;
if(table.Rows.Count == 1)
{
volunteer = new Volunteer(table.Rows[0]);
}
return volunteer;
}
public static List<Badge> GetBadgesByVolunteerIdList(List<int> volunteerIds)
{
var volunteerIdString = string.Join(", ", volunteerIds);
var connector = new Connector(" SELECT Volunteers.*, Trades.Trade AS TradeName, Congregation.CongregationName AS Congregation " +
" FROM (Volunteers INNER JOIN Trades ON (Trades.ID = Volunteers.Trade)) INNER JOIN Congregation ON (Congregation.ID = Volunteers.CongregationName) " +
" WHERE Volunteers.ID IN(" + volunteerIdString + ") " +
" ORDER BY Volunteers.Surname");
var table = connector.ExecuteDataTable();
var badges = new List<Badge>();
foreach(DataRow row in table.Rows)
{
badges.Add(new Badge(row));
}
connector = null;
return badges;
}
public static DataTable GetUnsynchronisedActivity()
{
var connector = new Connector(" SELECT " +
" ActivityLog.Description " +
" FROM ActivityLog " +
" WHERE ActivityLog.ID IN(" + DatabaseState.UnsyncIdList + ") " +
" ORDER BY ActivityLog.ID ");
var dataTable = connector.ExecuteDataTable();
return dataTable;
}
