public int AddUnit(Unit unit) { try { using (SqlConnection conn = new SqlConnection(connString)) { // Query the database for any existing unit with the same unit type id and unit number String query = "SELECT * FROM Units WHERE UnitTypeId=@UnitTypeId AND UnitNo=@UnitNo"; using (UnitTypeController utc = new UnitTypeController()) using (SqlCommand cmd = new SqlCommand(query, conn)) { cmd.Parameters.AddWithValue("@UnitTypeId", utc.AddUnitType(unit.unitType)); cmd.Parameters.AddWithValue("@UnitNo", unit.unitNo); conn.Open(); using (SqlDataReader dr = cmd.ExecuteReader()) { if (dr.Read()) { // If a row is returned, return the retrieved unit id return(Convert.ToInt32(dr["UnitId"])); } } } } using (SqlConnection conn = new SqlConnection(connString)) { // Query to insert unit into database String query = "INSERT INTO Units(UnitNo,Price,FloorArea,Avail,UnitTypeId) VALUES(@UnitNo,@Price,@FloorArea,@Avail,@UnitTypeId);"; query += "SELECT CAST(scope_identity() AS int)"; using (UnitTypeController utc = new UnitTypeController()) using (SqlCommand cmd = new SqlCommand(query, conn)) { cmd.Parameters.AddWithValue("@UnitNo", unit.unitNo); cmd.Parameters.AddWithValue("@Price", unit.price); cmd.Parameters.AddWithValue("@FloorArea", unit.floorArea); cmd.Parameters.AddWithValue("@Avail", unit.avail); cmd.Parameters.AddWithValue("@UnitTypeId", utc.AddUnitType(unit.unitType)); conn.Open(); // Return the unit id return((Int32)cmd.ExecuteScalar()); } } } catch (Exception ex) { // If an error occurred, return -1 return(-1); } }
public Unit GetUnit(int unitId) { // Initialize a Unit object Unit unit = new Unit(); if (unitId == 0) { // Return empty unit if id is 0 return(unit); } try { using (SqlConnection conn = new SqlConnection(connString)) { // Query the database for unit with the specific unit id String query = "SELECT * FROM Units WHERE UnitId = @UnitId"; using (SqlCommand cmd = new SqlCommand(query, conn)) { cmd.Parameters.AddWithValue("@UnitId", unitId); conn.Open(); using (SqlDataReader dr = cmd.ExecuteReader()) { if (dr.Read()) { // If a row is returned, set all unit information respectively unit.unitId = Convert.ToInt32(dr["UnitId"]); unit.unitNo = dr["UnitNo"].ToString(); unit.price = Convert.ToDecimal(dr["Price"]); unit.floorArea = Convert.ToInt32(dr["FloorArea"]); unit.avail = Convert.ToBoolean(dr["Avail"]); unit.faveCount = Convert.ToInt32(dr["FaveCount"]); unit.unitType = new UnitType(); unit.unitType.unitTypeId = Convert.ToInt32(dr["UnitTypeId"]); } } } } using (UnitTypeController utc = new UnitTypeController()) { unit.unitType = utc.GetUnitType(unit.unitType.unitTypeId); } } catch (Exception ex) { // If an error occurred, write error message on console Console.WriteLine(ex.Message); } // Return the Unit object return(unit); }
public Block GetBlockWithUnits(int blockId) { // Initialize a Block object Block block = new Block(); if (blockId == 0) { // Return empty block if id is 0 return(block); } try { // Get block information block = GetBlock(blockId); using (UnitTypeController utc = new UnitTypeController()) { // Get unit types in block block.unitTypes = utc.GetUnitTypesInBlock(block.blockId); } foreach (UnitType unitType in block.unitTypes) { using (UnitController uc = new UnitController()) { // Get all units in each unit types unitType.units = uc.GetUnitsInUnitType(unitType.unitTypeId); } } } catch (Exception ex) { // If an error occurred, write error message on console Console.WriteLine(ex.Message); } // Return the Block object return(block); }
public List <Block> SearchBlocks(SearchParameters searchParams) { // Initialize a list of Block object List <Block> blocks = new List <Block>(); // Query the database for blocks that fulfill the search paramters String query = "SELECT Blocks.BlockId, Blocks.BlockNo, Blocks.Street, Blocks.ProjectId, Blocks.DeliveryDate, Blocks.LocLat, Blocks.LocLong, Blocks.SitePlan, Blocks.TownMap, "; query += "Blocks.BlockPlan, Blocks.UnitDist, Blocks.FloorPlan, Blocks.LayoutIdeas, Blocks.Specs, MIN(Units.Price) AS MinPrice, MAX(Units.Price)AS MaxPrice "; query += "FROM Blocks INNER JOIN "; query += "Projects ON Blocks.ProjectId = Projects.ProjectId INNER JOIN "; query += "UnitTypes ON Blocks.BlockId = UnitTypes.BlockId INNER JOIN "; query += "Units ON UnitTypes.UnitTypeId = Units.UnitTypeId WHERE "; // If search paramter includes town names if (searchParams.townNames.Length > 0) { query += "( "; // Loop through the town names for (int i = 0; i < searchParams.townNames.Length; i++) { // Add town name into the query query += "(Projects.TownName = '" + searchParams.townNames[i] + "') "; if (i != searchParams.townNames.Length - 1) { // If not the last town name query += "OR "; } else { // If reached the last town name query += ") AND "; } } } // If search paramter includes ethnic group, add relevant ethnic group into the query // 'C' = Chinese, 'M' = Malay, 'O' = Indian/Others if (searchParams.ethnicGroup == 'C') { query += "(UnitTypes.QuotaChinese > 0) AND "; } else if (searchParams.ethnicGroup == 'M') { query += "(UnitTypes.QuotaMalay > 0) AND "; } else if (searchParams.ethnicGroup == 'O') { query += "(UnitTypes.QuotaOthers > 0) AND "; } // If search paramter includes unit types if (searchParams.unitTypes.Length > 0) { query += "( "; // Loop through the unit types for (int i = 0; i < searchParams.unitTypes.Length; i++) { // Add unit type into the query query += "(UnitTypes.UnitTypeName = '" + searchParams.unitTypes[i] + "') "; if (i != searchParams.unitTypes.Length - 1) { // If not the last unit type query += "OR "; } else { // If reached the last unit type query += ") AND "; } } } // Add search for price within search parameter price range into the query query += "(Units.Price >= " + searchParams.minPrice + ") AND (Units.Price <= " + searchParams.maxPrice + ") "; query += "GROUP BY Blocks.BlockId, Blocks.BlockNo, Blocks.Street, Blocks.ProjectId, Blocks.DeliveryDate, Blocks.LocLat, Blocks.LocLong, "; query += "Blocks.SitePlan, Blocks.TownMap, Blocks.BlockPlan, Blocks.UnitDist, Blocks.FloorPlan, Blocks.LayoutIdeas, Blocks.Specs "; try { using (SqlConnection conn = new SqlConnection(connString)) { using (SqlCommand cmd = new SqlCommand(query, conn)) { conn.Open(); using (SqlDataReader dr = cmd.ExecuteReader()) { while (dr.Read()) { // If a row is returned, initialize a Block object Block b = GetBlock(Convert.ToInt32(dr["BlockId"])); using (UnitTypeController utc = new UnitTypeController()) { b.unitTypes = utc.GetUnitTypesInBlock(b.blockId); CalculateTravel(b, searchParams.postalCode); } // Add Block object into the list of blocks blocks.Add(b); } } } } SortBlocks(searchParams.orderBy, blocks); } catch (Exception ex) { // If an error occurred, write error message on console Console.WriteLine(ex.Message); } // Return the list of Block objects return(blocks); }