/// <summary>
/// Will attempt to insert customers data into SQL.
/// </summary>
/// <param name="pCustomer"></param>
/// <returns></returns>
public static int CreateCustomerRecord(Customer pCustomer)
{
List<Tuple<Type, string>> CustomersAttributes = new List<Tuple<Type, string>>()
{
new Tuple<Type, string>( typeof(string), pCustomer.Name ),
new Tuple<Type, string>( typeof(int), pCustomer.HouseNumber.ToSafeString() ),
new Tuple<Type, string>( typeof(string), pCustomer.HouseName ),
new Tuple<Type, string>( typeof(string), pCustomer.Street ),
new Tuple<Type, string>( typeof(string), pCustomer.Postcode )
};
List<PropertyInfo> CustomerColumnsToBeAddedTo = typeof(Customer).GetProperties().ToList();
CustomerColumnsToBeAddedTo.RemoveAt(0); //RemoveID as it will be automatically assigned by SQL.
return DataAccess.ExecuteInsertQuery("ODS", Global.DBConfig["CustomerTN"], CustomerColumnsToBeAddedTo, CustomersAttributes);
}
public static bool CreateBooking(string pName, int pHouseNumber, string pHouseName, string pStreet, string pPostcode, DateTime pDateFrom, int pDaysDuration, double pPaymentAmount, Room pMyRoom, string pNotes)
{
int CustomerID = 0;
Customer varCustomer = new Customer(pName, pHouseNumber, pHouseName, pStreet, pPostcode);
if (!Customer.CheckCustomerExists(varCustomer))
{//No customer exists, create one and update database.
int CustomerRowsAffected = Customer.CreateCustomerRecord(varCustomer);
if (CustomerRowsAffected == 1) { Logger.WriteLine("DEBUG", "Customer created and added to database successfully."); }
else if (CustomerRowsAffected == 0) { Logger.WriteLine("ERROR", "Failed to update the database with new customer."); return false; }
CustomerID = Customer.SearchForCustomerID(varCustomer);
}
else
{//Customer already exists, get data from database.
CustomerID = Customer.SearchForCustomerID(varCustomer);
}
if(CustomerID != 0)
{//Customer ID exists
List<Tuple<Type, string>> BookingAttributes = new List<Tuple<Type, string>>()
{//Create list of values to be inserted.
new Tuple<Type, string>(typeof(int), pDaysDuration.ToSafeString() ),
new Tuple<Type, string>(typeof(DateTime), HelperMethods.FormatDateForSQL(pDateFrom.ToShortDateString()) ),
new Tuple<Type, string>(typeof(double), pPaymentAmount.ToSafeString() ),
new Tuple<Type, string>(typeof(int), pMyRoom.RoomID.ToSafeString() ),
new Tuple<Type, string>(typeof(int), CustomerID.ToSafeString() ),
new Tuple<Type, string>(typeof(string), pNotes )
};
List<PropertyInfo> ColumnsToBeAddedTo = new List<PropertyInfo>()
{//Get BookingDetail object propertys
typeof(BookingDetail).GetProperty("Days"),
typeof(BookingDetail).GetProperty("DateStart"),
typeof(BookingDetail).GetProperty("PaymentAmount"),
typeof(BookingDetail).GetProperty("myRoom"),
typeof(BookingDetail).GetProperty("myCustomer"),
typeof(BookingDetail).GetProperty("Notes")
};
int BookingRowsAffected = DataAccess.ExecuteInsertQuery("ODS", Global.DBConfig["BookingTN"], ColumnsToBeAddedTo, BookingAttributes);
if (BookingRowsAffected == 1) { Logger.WriteLine("DEBUG", "Booking created and added to database successfully."); }
else if (BookingRowsAffected == 0) { Logger.WriteLine("ERROR", "Failed to update the database with new booking."); return false; }
if(!SyncRoomRecord(pDaysDuration, pDateFrom, pPaymentAmount, pMyRoom.RoomID, CustomerID, pNotes)) { return false; }
}
else { return false; }
return true;
}
public static bool CheckCustomerExists(Customer pCustomer)
{
string sqlCmdConditions = HelperMethods.BuildSQLConditionsWithParams
(new SortedList<string, string>()
{
{ "Name", pCustomer.Name },
{ "HouseNumber", Convert.ToString(pCustomer.HouseNumber) },
{ "HouseName", pCustomer.HouseName },
{ "Street", pCustomer.Street },
{ "Postcode", pCustomer.Postcode }
});
SqlCommand SqlCmd0 = new SqlCommand("SELECT CASE WHEN EXISTS((SELECT TOP 1 * FROM " +
Global.DBConfig["CustomerTN"] +
sqlCmdConditions +
")) THEN 1 ELSE 0 END;");
SqlCmd0.Parameters.Add(new SqlParameter("@pName", pCustomer.Name));
SqlCmd0.Parameters.Add(new SqlParameter("@pHouseNumber", pCustomer.HouseNumber));
SqlCmd0.Parameters.Add(new SqlParameter("@pHouseName", pCustomer.HouseName));
SqlCmd0.Parameters.Add(new SqlParameter("@pStreet", pCustomer.Street));
SqlCmd0.Parameters.Add(new SqlParameter("@pPostcode", pCustomer.Postcode));
return DataAccess.ExecuteBoolReturnQuery("ODS", SqlCmd0);
}
/// <summary>
/// Will attempt to retrive customers ID from all fields in customer.
/// </summary>
/// <param name="pCustomer"></param>
/// <returns></returns>
public static int SearchForCustomerID(Customer pCustomer)
{
SqlCommand SqlCmd2 = new SqlCommand("SELECT TOP 1 [CustomerID] FROM " +
Global.DBConfig["CustomerTN"] +
HelperMethods.BuildSQLConditionsWithParams(new SortedList<string, string>()
{
{ "Name", pCustomer.Name },
{ "HouseNumber", Convert.ToString(pCustomer.HouseNumber) },
{ "HouseName", pCustomer.HouseName },
{ "Street", pCustomer.Street },
{ "Postcode", pCustomer.Postcode }
}));
SqlCmd2.Parameters.Add(new SqlParameter("@pName", pCustomer.Name));
SqlCmd2.Parameters.Add(new SqlParameter("@pHouseNumber", pCustomer.HouseNumber));
SqlCmd2.Parameters.Add(new SqlParameter("@pHouseName", pCustomer.HouseName));
SqlCmd2.Parameters.Add(new SqlParameter("@pStreet", pCustomer.Street));
SqlCmd2.Parameters.Add(new SqlParameter("@pPostcode", pCustomer.Postcode));
return DataAccess.ExecuteIntegerReturnQuery("ODS", SqlCmd2);
}
