/// <summary>
/// Gets the available options.
/// </summary>
/// <param name="derivId">The derivative id.</param>
/// <param name="plateId">The plate id.</param>
/// <returns>List of valuation options</returns>
public List <IValuationOption> GetAvailableOptions(int derivId, int plateId)
{
Sproc procedure = new Sproc("Used_Select_Options", DatabaseName);
procedure.Parameters.Add("@CARDerId", SqlDbType.Int).Value = derivId;
procedure.Parameters.Add("@YearPlateId", SqlDbType.Int).Value = plateId;
using (SqlDataReader reader = procedure.ExecuteReader())
{
if (reader != null && reader.HasRows)
{
List <IValuationOption> result = new List <IValuationOption>();
while (reader.Read())
{
ValuationOption opt = new ValuationOption();
opt.OptionCode = (int)reader["OptionCode"];
opt.Category = (string)reader["Category"];
opt.Item = ((string)reader["Item"]).Trim();
opt.Cost = (decimal)reader["Cost"];
opt.Value = (decimal)(double)reader["Value"];
result.Add(opt);
}
return(result);
}
}
return(null);
}
/// <summary>
/// Calls Der_Select_ById or Der_S_AllInModelById to create a derivative
/// </summary>
/// <param name="id">The derivative id</param>
/// <param name="hintModel">If true, precache all derivatives for this model if this derivative is not already cached</param>
/// <returns>A car derivative</returns>
private ICarDerivative GetDerivativeFromId(int id, bool hintModel)
{
if (id <= 0)
{
return(null);
}
CarDerivative result = null;
Sproc procedure = new Sproc("Der_Select_ById", DatabaseName);
procedure.Parameters.Add("@Id", SqlDbType.Int).Value = id;
using (SqlDataReader dataReader = procedure.ExecuteReader())
{
if (dataReader != null && dataReader.HasRows)
{
while (dataReader.Read())
{
CarDerivative derivative = this.GetDerivativeFromDataReader(dataReader);
if (derivative.Id == id)
{
result = derivative;
}
}
}
}
return(result);
}
/// <summary>
/// Gets the tech data derivatives from id.
/// </summary>
/// <param name="modelId">The model id.</param>
/// <returns>List of van derivatives</returns>
public List <IVanDerivative> GetTechDataDerivativesFromId(int modelId)
{
Sproc sp = new Sproc("Review_Select_DerivativesByModel", DatabaseName);
sp.Parameters.Add("@VANModId", SqlDbType.Int).Value = modelId;
using (SqlDataReader dr = sp.ExecuteReader())
{
if (dr != null && dr.HasRows)
{
List <IVanDerivative> result = new List <IVanDerivative>();
IVanProvider vanProvider = ObjectFactory.GetInstance <IVanProvider>();
while (dr.Read())
{
IVanDerivative d = vanProvider.GetDerivativeFromId((int)dr["VANDerId"]);
if (d.HasTechData)
{
result.Add(d);
}
}
return(result);
}
}
return(null);
}
/// <summary>
/// Gets the models by range and manufacturer identifier.
/// </summary>
/// <param name="rangeName">The range name.</param>
/// <param name="manufacturerId">The manufacturer identifier.</param>
/// <returns>the car models</returns>
public List <ICarModel> GetModelsByRangeNameAndManufacturer(string rangeName, int manufacturerId)
{
List <ICarModel> result = new List <ICarModel>();
Sproc procedure = new Sproc("ModelIds_Select_ByRangeAndManufacturer", DatabaseName);
procedure.Parameters.Add("@manufacturerId", SqlDbType.Int).Value = manufacturerId;
procedure.Parameters.Add("@range", SqlDbType.VarChar).Value = rangeName;
using (SqlDataReader reader = procedure.ExecuteReader())
{
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
int id;
if (Int32.TryParse(reader["CARModId"].ToString(), out id))
{
result.Add(this.GetModelFromId(id));
}
}
}
}
return(result);
}
/// <summary>
/// Gets the derivatives by model and plate id.
/// </summary>
/// <param name="modelId">The model id.</param>
/// <param name="plateId">The plate id.</param>
/// <param name="section">The section.</param>
/// <returns> List of van derivatives </returns>
public List <IVanDerivative> GetDerivativesByModelAndPlateId(int modelId, int plateId, VanValuationSection section)
{
if (plateId >= GetYearPlateFromForSection(section) && plateId <= GetYearPlateToForSection(section))
{
Sproc sp = new Sproc("Used_Select_DerivsByModelAndPlate", Database);
sp.Parameters.Add("@VANModId", SqlDbType.Int).Value = modelId;
sp.Parameters.Add("@YearPlateId", SqlDbType.Int).Value = plateId;
using (SqlDataReader dr = sp.ExecuteReader())
{
if (dr != null && dr.HasRows)
{
List <IVanDerivative> result = new List <IVanDerivative>();
IVanProvider vanProvider = ObjectFactory.GetInstance <IVanProvider>();
while (dr.Read())
{
result.Add(vanProvider.GetDerivativeFromId((int)dr["VANDerId"]));
}
return(result);
}
}
return(null);
}
else
{
return(null);
}
}
/// <summary>
/// Calls Man_Select_ById or Man_S_All to create a manufacturer
/// </summary>
/// <param name="id">The manufacturer id</param>
/// <param name="hintAll">Precache all manufacturers if the requested manufacturer is not already cached</param>
/// <returns>A car manufacturer</returns>
private ICarManufacturer GetManufacturerFromId(int id, bool hintAll)
{
if (id == 0)
{
return(null);
}
CarManufacturer result = null;
Sproc procedure = new Sproc("Man_S_All", DatabaseName);
if (hintAll == false)
{
procedure.Name = "Man_Select_ById";
procedure.Parameters.Add("@Id", SqlDbType.Int).Value = id;
}
using (SqlDataReader dataReader = procedure.ExecuteReader())
{
if (dataReader.HasRows)
{
while (dataReader.Read())
{
CarManufacturer manufacturer = this.GetManufacturerFromDataReader(dataReader);
if (manufacturer.Id == id)
{
result = manufacturer;
}
}
}
}
return(result);
}
/// <summary>
/// Populates the mileage adjusted valuation.
/// </summary>
/// <param name="derivId">The deriv identifier.</param>
/// <param name="plateId">The plate identifier.</param>
/// <param name="section">The section.</param>
/// <param name="valuation">The valuation.</param>
private static void PopulateVanValuation(int derivId, int plateId, VanValuationSection section, VanValuation valuation)
{
if (plateId >= GetYearPlateFromForSection(section) && plateId <= GetYearPlateToForSection(section))
{
valuation.Result = ValuationResultType.Error;
IVanProvider vanProvider = ObjectFactory.GetInstance <IVanProvider>();
IYearPlateProvider yearPlateProvider = ObjectFactory.GetInstance <IYearPlateProvider>();
valuation.Derivative = vanProvider.GetDerivativeFromId(derivId);
valuation.YearPlate = yearPlateProvider.FromId(plateId);
valuation.ValuationSegments = new Dictionary <int, IVanValuationSegment>();
valuation.OriginalPrice = GetOriginalPrice(derivId, plateId);
Sproc sp = new Sproc("Used_Select_Valuation", Database);
sp.Parameters.Add("@VANDerId", SqlDbType.Int).Value = derivId;
sp.Parameters.Add("@YearPlateId", SqlDbType.Int).Value = plateId;
using (SqlDataReader dataReader = sp.ExecuteReader())
{
if (dataReader != null)
{
while (dataReader.Read())
{
PopulateValuationSegmentFromDataReader(dataReader, valuation);
}
valuation.Result = ValuationResultType.VanEstimatedMileage;
}
}
}
}
/// <summary>
/// A list of all options and standard for the given derivative
/// </summary>
/// <param name="derivId">The derivative id</param>
/// <returns>The list of options</returns>
public List <IOption> GetOptionsByDerivativeId(int derivId)
{
Sproc procedure = new Sproc("DerOption_S", DatabaseName);
procedure.Parameters.Add("@CARDerId", SqlDbType.Int).Value = derivId;
return(procedure.ExecuteList <IOption>(this.GetOptionFromDataReader));
}
/// <summary>
/// Sets the data version poll.
/// </summary>
/// <param name="version">The version.</param>
/// <param name="server">The server.</param>
public void SetDataVersionPoll(int version, string server)
{
Sproc procedure = new Sproc("InsertDataVersionPoll", DatabaseName);
procedure.Parameters.Add("@version", SqlDbType.Int).Value = version;
procedure.Parameters.Add("@server", SqlDbType.Char).Value = server;
procedure.ExecuteNonQuery();
}
/// <summary>
/// Gets the list of manufacturer ids
/// </summary>
/// <returns>The list of manufacturer ids</returns>
private List <int> GetManufacturerIds()
{
List <int> result = new List <int>();
Sproc sp = new Sproc("Man_List_S", DatabaseName);
result = sp.ExecuteList <int>(dr => DataUtil.GetInt32(dr, "CARManId"));
return(result);
}
/// <summary>
/// Gets the server data versions.
/// </summary>
/// <returns>A collection of server data version info</returns>
public IEnumerable <KeyValuePair <string, int> > GetServerDataVersions()
{
IEnumerable <KeyValuePair <string, int> > result = null;
using (Sproc procedure = new Sproc("SelectServerDataVersion", DatabaseName))
{
result = procedure.ExecuteList(GetServerDataVersionsFromDataReader);
}
return(result);
}
public static Town FromGridRef(GridRef input)
{
if (input == null)
{
return(null);
}
Sproc sp = new Sproc("Geonames_S_FindName", _database);
sp.Parameters.Add("@Latitude", SqlDbType.Float).Value = input.Latitude;
sp.Parameters.Add("@Longitude", SqlDbType.Float).Value = input.Longitude;
return(sp.ExecuteObject <IPlace>(FromDataRecord) as Town);
}
/// <summary>
/// Calls Der_Select_ByCapCode to create a derivative
/// </summary>
/// <param name="capCode">The cap code</param>
/// <returns>A Car Derivative</returns>
public ICarDerivative GetDerivativeFromCapCode(string capCode)
{
Sproc sp = new Sproc("Der_Select_ByCapCode", DatabaseName);
sp.Parameters.Add("@CapCode", SqlDbType.VarChar).Value = capCode;
using (SqlDataReader dr = sp.ExecuteReader())
{
if (dr.HasRows && dr.Read())
{
return(this.GetDerivativeFromDataReader(dr));
}
}
return(null);
}
public IComingSoon GetComingSoonFromId(int id)
{
Sproc sp = new Sproc("ComingSoon_Select", _database);
sp.Parameters.Add("@PageId", SqlDbType.Int).Value = id;
using (SqlDataReader dr = sp.ExecuteReader())
{
if (dr != null && dr.Read())
{
return(GetComingSoonFromDataReader(dr));
}
}
return(null);
}
/// <summary>
/// Gets the model trim equipment table.
/// </summary>
/// <param name="modelId">The model id.</param>
/// <returns>A table with rows for equipment items and a tri state flag for trim level (none - no derivatives have the equipment, some or all)</returns>
public DataTable GetModelTrimEquipmentTable(int modelId)
{
Sproc procedure = new Sproc("TrimEquipmentGrid_S_ByModel", DatabaseName);
procedure.Parameters.Add("@CARModId", SqlDbType.Int).Value = modelId;
DataSet trimEquipmentDataSet = procedure.ExecuteDataSet();
if (trimEquipmentDataSet.Tables.Count > 0)
{
return(trimEquipmentDataSet.Tables[0]);
}
return(null);
}
/// <summary>
/// Gets the range from id.
/// </summary>
/// <param name="id">The range id.</param>
/// <returns>The Van range</returns>
public IVanRange GetRangeFromId(int id)
{
Sproc sp = new Sproc("Ran_Select_ById", DatabaseName);
sp.Parameters.Add("@Id", SqlDbType.Int).Value = id;
using (SqlDataReader dr = sp.ExecuteReader())
{
if (dr.HasRows && dr.Read())
{
return(this.GetRangeFromDataReader(dr));
}
}
return(null);
}
/// <summary>
/// Get the review for a given CARModId
/// </summary>
/// <param name="id">The CARModiI</param>
/// <returns>The review</returns>
public ICarReview GetReviewFromId(int id)
{
ICarReview result = null;
Sproc procedure = new Sproc("Mod_Select_ById", DatabaseName);
procedure.Parameters.Add("@Id", SqlDbType.Int).Value = id;
using (SqlDataReader dataReader = procedure.ExecuteReader())
{
if (dataReader.HasRows && dataReader.Read())
{
result = this.GetReviewFromDataReader(dataReader);
}
}
return(result);
}
public IVanReview GetReviewFromId(int id)
{
Sproc sp = new Sproc("Mod_Select_ById", _database);
sp.Parameters.Add("@Id", SqlDbType.Int).Value = id;
using (SqlDataReader dr = sp.ExecuteReader())
{
if (dr.HasRows && dr.Read())
{
if ((bool)dr["Review"])
{
return(GetReviewFromDataReader(dr));
}
}
}
return(null);
}
public static List <IPlace> GetPlacesFromString(string input)
{
List <IPlace> result = new List <IPlace>();
if (String.IsNullOrEmpty(input))
{
return(result);
}
Postcode pc = new Postcode(input);
if (pc.Outcode != null)
{
result.Add(pc);
return(result);
}
Sproc sp = new Sproc("Geonames_S_FindLocation", _database);
sp.Parameters.Add("@Search", SqlDbType.VarChar).Value = input.Replace(',', ' ');
using (SqlDataReader dr = sp.ExecuteReader())
{
bool hasWholeWordMatch = false;
bool hasSimpleNameMatch = false;
if (dr != null && dr.HasRows)
{
while (dr.Read())
{
hasWholeWordMatch = hasWholeWordMatch || DataUtil.GetBoolean(dr, "WholeWord");
hasSimpleNameMatch = hasSimpleNameMatch || DataUtil.GetBoolean(dr, "ExcludingCounty");
if (hasWholeWordMatch == false || DataUtil.GetBoolean(dr, "WholeWord"))
{
if (hasSimpleNameMatch == false || DataUtil.GetBoolean(dr, "ExcludingCounty"))
{
result.Add(FromDataRecord(dr));
}
}
}
}
}
return(result);
}
/// <summary>
/// Gets the original price.
/// </summary>
/// <param name="derivId">The deriv identifier.</param>
/// <param name="yearPlateId">The year plate identifier.</param>
/// <returns>
/// the original price for derivative
/// </returns>
private static int GetOriginalPrice(int derivId, int yearPlateId)
{
int result = 0;
Sproc sp = new Sproc("Used_Select_OriginalPrice", Database);
sp.Parameters.Add("@VANDerId", SqlDbType.Int).Value = derivId;
sp.Parameters.Add("@YearPlateId", SqlDbType.Int).Value = yearPlateId;
using (SqlDataReader dataReader = sp.ExecuteReader())
{
if (dataReader != null && dataReader.Read())
{
result = DataUtil.GetInt32(dataReader, "New");
}
}
return(result);
}
public List <IComingSoon> GetActiveComingSoons()
{
Sproc sp = new Sproc("ComingSoon_Select_Active", _database);
using (SqlDataReader dr = sp.ExecuteReader())
{
if (dr != null && dr.HasRows)
{
List <IComingSoon> results = new List <IComingSoon>();
while (dr.Read())
{
results.Add(GetComingSoonFromId((int)dr["PageId"]));
}
return(results);
}
}
return(null);
}
private void SaveChanges()
{
using (var db = new SqlConnection(ConnectionHelper.ConnectionString))
{
db.Open();
// not part of a single transaction. you can handle that on your own if important...
foreach (var changedContact in _vm.Contacts.Where(a => a.IsChanged))
{
var sp = new Sproc("Person.spUpdateExtensibleData", db);
sp.SetParam("@ContactId", changedContact.ContactId);
var fieldsInXml = CollectionHelper.GetExtensibleDataAsXml(changedContact);
sp.SetParam("@XmlFieldVals", fieldsInXml);
sp.ExecNonQuery();
}
}
}
/// <summary>
/// Returns a grid reference for the postcode, or null if none can be determined.
/// </summary>
/// <returns></returns>
public GridRef GetLocation()
{
if (_gridRef == null)
{
// If we have a complete postcode, get a location from Capscan
if (_isValid)
{
com.bauerhosting.postcodes.GridRef g = _service.GetLocation(_value, _siteName);
if (g != null)
{
_gridRef = new GridRef(g.Easting, g.Northing, g.Latitude, g.Longitude);
}
//_gridRef = CapScanUtil.GetLocation( _value );
}
// If this is a Belfast postcode and Capscan reports that it is east of Douglas, something
// has gone wrong. Throw the grid ref away and get one from the database.
if (_gridRef != null && _outcode != null &&
_outcode.StartsWith("BT") && _gridRef.Easting > 237800)
{
_gridRef = null;
}
// If we have an outcode but no grid ref, get a approximate location from the database
if (_gridRef == null && _outcode != null)
{
Sproc sp = new Sproc("PostcodeDistrict_Select", "ParkersMeta");
sp.Parameters.Add("@Postcode", SqlDbType.VarChar, 10).Value = _outcode;
using (SqlDataReader dr = sp.ExecuteReader())
{
if (dr.HasRows && dr.Read())
{
_gridRef = new GridRef((int)dr["Easting"], (int)dr["Northing"], (double)dr["Latitude"], (double)dr["Longitude"]);
}
else
{
_gridRef = null;
}
}
}
}
return(_gridRef);
}
/// <summary>
/// Populates a valuation object based on the mileage
/// </summary>
/// <param name="derivId">The derivative id</param>
/// <param name="plateId">The plate id</param>
/// <param name="mileage">The mileage</param>
/// <param name="section">The valuation section</param>
/// <param name="valuation">The valuation</param>
private static void PopulateMileageAdjustedValuation(int derivId, int plateId, int mileage, CarValuationSection section, Valuation valuation)
{
if (plateId >= GetFromYearPlateIdForSection(section) && plateId <= GetToYearPlateIdForSection(section))
{
Sproc procedure = new Sproc("Used_Select_Valuation", DatabaseName);
procedure.Parameters.Add("@CARDerId", SqlDbType.Int).Value = derivId;
procedure.Parameters.Add("@YearPlateId", SqlDbType.Int).Value = plateId;
if (mileage > 0)
{
procedure.Parameters.Add("@Mileage", SqlDbType.Int).Value = mileage;
}
else
{
procedure.Parameters.Add("@Mileage", SqlDbType.Int).Value = DBNull.Value;
}
valuation.Result = ValuationResultType.Error;
using (SqlDataReader reader = procedure.ExecuteReader())
{
if (reader != null && reader.Read())
{
PopulateValuationFromDataReader(reader, valuation);
if (mileage == 0)
{
valuation.Result = ValuationResultType.StandardMileage;
}
else if (mileage == valuation.Mileage)
{
valuation.Result = ValuationResultType.MileageAdjusted;
}
else if (mileage > valuation.Mileage)
{
valuation.Result = ValuationResultType.MaximumMileage;
}
else if (mileage < valuation.Mileage)
{
valuation.Result = ValuationResultType.MinimumMileage;
}
}
}
}
}
/// <summary>
/// The trims and equipment details for a model
/// </summary>
/// <param name="modelId">The model id</param>
/// <returns>The trim list</returns>
public TrimList GetTrimListFromModelId(int modelId)
{
TrimList result = new TrimList();
Sproc procedure = new Sproc("Trim_S_ByModel", DatabaseName);
procedure.Parameters.Add("@CARModId", SqlDbType.Int).Value = modelId;
using (SqlDataReader datareader = procedure.ExecuteReader())
{
if (datareader != null && datareader.HasRows)
{
this.ProcessTrims(result, datareader);
datareader.NextResult();
this.ProcessTrimEquipment(result, datareader);
}
}
return(result);
}
public List <IVanDerivative> GetDerivativesFromId(int modelId)
{
Sproc sp = new Sproc("Review_Select_DerivativesByModel", _database);
sp.Parameters.Add("@VanModId", SqlDbType.Int).Value = modelId;
using (SqlDataReader dr = sp.ExecuteReader())
{
if (dr != null && dr.HasRows)
{
List <IVanDerivative> result = new List <IVanDerivative>();
while (dr.Read())
{
result.Add(vanProvider.GetDerivativeFromId((int)dr["VanDerId"]));
}
return(result);
}
}
return(null);
}
/// <summary>
/// Gets the manufacturer ids with insurance infro
/// </summary>
/// <returns>List of manufacturer ids</returns>
public List <int> GetManufacturerIdsWithInsuranceInfo()
{
Sproc procedure = new Sproc("Man_Ins_S", DatabaseName);
List <int> result = new List <int>();
using (SqlDataReader dataReader = procedure.ExecuteReader())
{
if (dataReader != null && dataReader.HasRows)
{
while (dataReader.Read())
{
result.Add(dataReader.GetInt32(0));
}
}
}
return(result);
}
/// <summary>
/// A list of manufacturers which have at least one CarCheck
/// </summary>
/// <returns>The manufacturer ids with a checklist</returns>
public List <int> GetManufacturerIdsWithChecklist()
{
Sproc procedure = new Sproc("CarCheck_Select_Manufacturers", "ParkersTransactionNew");
List <int> result = new List <int>();
using (SqlDataReader dataReader = procedure.ExecuteReader())
{
if (dataReader != null && dataReader.HasRows)
{
while (dataReader.Read())
{
result.Add(dataReader.GetInt32(0));
}
}
}
return(result);
}
/// <summary>
/// Gets all derivatives.
/// </summary>
/// <returns>All the derivatives</returns>
public List <ICarDerivative> GetAllDerivatives()
{
List <ICarDerivative> result = new List <ICarDerivative>();
Sproc procedure = new Sproc("Der_Select", DatabaseName);
using (SqlDataReader reader = procedure.ExecuteReader())
{
if (reader.HasRows && reader.Read())
{
while (reader.Read())
{
result.Add(this.GetDerivativeFromDataReader(reader));
}
}
}
return(result);
}
/// <summary>
/// Calls Mod_Select_ById or Mod_S_AllInManById to create a model
/// </summary>
/// <param name="id">The model id</param>
/// <param name="hintManufacturer">If true, cache all models from this manufacturer is the requested range is not already cached</param>
/// <param name="hintReview">If true,cache the CarReview at the same time if the model is not already cached</param>
/// <param name="hintRange">If true, cache the CarRange object at the same time if the model is not already cached</param>
/// <returns>A car model</returns>
private ICarModel GetModelFromId(int id, bool hintManufacturer, bool hintReview, bool hintRange)
{
if (id == 0)
{
return(null);
}
CarModel result = null;
Sproc procedure = new Sproc("Mod_Select_ById", DatabaseName);
if (hintManufacturer)
{
procedure.Name = "Mod_S_AllInManById";
procedure.Parameters.Add("@CARModId", SqlDbType.Int).Value = id;
}
else
{
procedure.Parameters.Add("@Id", SqlDbType.Int).Value = id;
}
using (SqlDataReader dataReader = procedure.ExecuteReader())
{
if (dataReader != null && dataReader.HasRows)
{
while (dataReader.Read())
{
CarModel model = this.GetModelFromDataReader(dataReader);
if (hintRange && hintManufacturer)
{
GetRangeFromId(model.RangeId, hintManufacturer);
}
if (model.Id == id)
{
result = model;
}
}
}
}
return(result);
}