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