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