public int AddUnitType(UnitType unitType)
{
try
{
using (SqlConnection conn = new SqlConnection(connString))
{
// Query the database for any existing unit type with the same block id and unit type name
String query = "SELECT * FROM UnitTypes WHERE BlockId=@BlockId AND UnitTypeName=@UnitTypeName";
using (BlockController bc = new BlockController())
using (SqlCommand cmd = new SqlCommand(query, conn))
{
cmd.Parameters.AddWithValue("@BlockId", bc.AddBlock(unitType.block));
cmd.Parameters.AddWithValue("@UnitTypeName", unitType.unitTypeName);
conn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
if (dr.Read())
{
// If a row is returned, return the retrieved unit type id
return(Convert.ToInt32(dr["UnitTypeId"]));
}
}
}
}
using (SqlConnection conn = new SqlConnection(connString))
{
// Query to insert unit type into database
String query = "INSERT INTO UnitTypes(BlockId,UnitTypeName,QuotaMalay,QuotaChinese,QuotaOthers) VALUES(@BlockId,@UnitTypeName,@QuotaMalay,@QuotaChinese,@QuotaOthers);";
query += "SELECT CAST(scope_identity() AS int)";
using (BlockController bc = new BlockController())
using (SqlCommand cmd = new SqlCommand(query, conn))
{
cmd.Parameters.AddWithValue("@BlockId", bc.AddBlock(unitType.block));
cmd.Parameters.AddWithValue("@UnitTypeName", unitType.unitTypeName);
cmd.Parameters.AddWithValue("@QuotaMalay", unitType.quotaMalay);
cmd.Parameters.AddWithValue("@QuotaChinese", unitType.quotaChinese);
cmd.Parameters.AddWithValue("@QuotaOthers", unitType.quotaOthers);
conn.Open();
Notify(unitType);
// Return the unit type id
return((Int32)cmd.ExecuteScalar());
}
}
}
catch (Exception ex)
{
// If an error occurred, return -1
return(-1);
}
}
public UnitType GetUnitType(int unitTypeId)
{
// Initialize a UnitType object
UnitType unitType = new UnitType();
if (unitTypeId == 0)
{
// Return empty unit type if id is 0
return(unitType);
}
try
{
using (SqlConnection conn = new SqlConnection(connString))
{
// Query the database for unit type with the specific unit type id
String query = "SELECT * FROM UnitTypes WHERE UnitTypeId = @UnitTypeId";
using (SqlCommand cmd = new SqlCommand(query, conn))
{
cmd.Parameters.AddWithValue("@UnitTypeId", unitTypeId);
conn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
if (dr.Read())
{
// If a row is returned, set all unit type information respectively
unitType.unitTypeName = dr["UnitTypeName"].ToString();
unitType.unitTypeId = Convert.ToInt32(dr["UnitTypeId"]);
unitType.quotaChinese = Convert.ToInt32(dr["QuotaChinese"]);
unitType.quotaMalay = Convert.ToInt32(dr["QuotaMalay"]);
unitType.quotaOthers = Convert.ToInt32(dr["QuotaOthers"]);
unitType.block = new Block();
unitType.block.blockId = Convert.ToInt32(dr["BlockId"]);
}
}
}
}
using (BlockController bc = new BlockController())
{
unitType.block = bc.GetBlock(unitType.block.blockId);
}
}
catch (Exception ex)
{
// If an error occurred, write error message on console
Console.WriteLine(ex.Message);
}
// Return the UnitType object
return(unitType);
}
public bool Subscribe(string unitTypeName, string deviceId)
{
try
{
using (SqlConnection conn = new SqlConnection(connString))
{
// Query the database for unit type subscriptions with specific unit type name and device id
String query = "SELECT * FROM UnitTypeSubscriptions WHERE UnitTypeName=@UnitTypeName AND DeviceId=@DeviceId";
using (BlockController bc = new BlockController())
using (SqlCommand cmd = new SqlCommand(query, conn))
{
cmd.Parameters.AddWithValue("@UnitTypeName", unitTypeName);
cmd.Parameters.AddWithValue("@DeviceId", deviceId);
conn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
if (dr.Read())
{
// If a row is returned, return true if there is existing subscription
return(true);
}
}
}
}
using (SqlConnection conn = new SqlConnection(connString))
{
// Query to insert unit type subscription into database
String query = "INSERT INTO UnitTypeSubscriptions(UnitTypeName,DeviceId) VALUES(@UnitTypeName,@DeviceId)";
using (SqlCommand cmd = new SqlCommand(query, conn))
{
cmd.Parameters.AddWithValue("@UnitTypeName", unitTypeName);
cmd.Parameters.AddWithValue("@DeviceId", deviceId);
conn.Open();
cmd.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
// If an error occurred, return false
return(false);
}
// If unit type subscription is inserted successfully, return true
return(true);
}
public List <string> GetSubscriptions(string deviceId)
{
// Initialize a list of string
List <string> unitTypeNames = new List <string>();
try
{
using (SqlConnection conn = new SqlConnection(connString))
{
// Query the database for all subscriptions for the particular device id
String query = "SELECT * FROM UnitTypeSubscriptions WHERE DeviceId=@DeviceId";
using (BlockController bc = new BlockController())
using (SqlCommand cmd = new SqlCommand(query, conn))
{
cmd.Parameters.AddWithValue("@DeviceId", deviceId);
conn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
// If a row is returned, add unit type name into the list of unit type names
unitTypeNames.Add(dr["UnitTypeName"].ToString());
}
}
}
}
}
catch (Exception ex)
{
}
// Return the list of unit type names
return(unitTypeNames);
}
/// <summary>
/// Method to inform user of their subscriptions using Firebase Cloud Messaging (FCM)
/// </summary>
/// <param name="unitType">A UnitType object of a unit that was newly added</param>
/// <returns>A bool true or false to indicate status of notification</returns>
public bool Notify(UnitType unitType)
{
// Initialize a list of string
List <string> deviceIds = new List <string>();
try
{
using (SqlConnection conn = new SqlConnection(connString))
{
// Query the database for subscriptions for the particular unit type
String query = "SELECT * FROM UnitTypeSubscriptions WHERE UnitTypeName=@UnitTypeName";
using (BlockController bc = new BlockController())
using (SqlCommand cmd = new SqlCommand(query, conn))
{
cmd.Parameters.AddWithValue("@UnitTypeName", unitType.unitTypeName);
conn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
// If a row is returned, add the device id into the list of string
deviceIds.Add(dr["DeviceId"].ToString());
}
}
}
}
}
catch (Exception ex)
{
// If an error occurred, return false
return(false);
}
// Form the notification content
string content = "New " + unitType.unitTypeName + " units available at " + unitType.block.blockNo + " " + unitType.block.project.projectName + "!";
foreach (string deviceId in deviceIds)
{
var message = JsonConvert.SerializeObject(new
{
to = deviceId,
notification = new
{
body = content,
title = ""
}
});
using (var client = new WebClient {
UseDefaultCredentials = true
})
{
// Send message to user's device using FCM
client.Headers.Add(HttpRequestHeader.ContentType, "application/json");
client.Headers.Add(HttpRequestHeader.Authorization, "key=AIzaSyB2t8hQOj1o6zPK6-TBdk3XkpnKwMXnN8Y");
byte[] response = client.UploadData("https://fcm.googleapis.com/fcm/send", "POST", Encoding.UTF8.GetBytes(message));
}
}
// If notification is successfully sent, return true
return(true);
}
