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