public void Insert_CarDetails(CarDetailsObj obj, long makerId, long modelId, long typeId)
{
try
{
string query = string.Format("INSERT INTO {0} " +
"(maker_id, model_id, modeltype_id, maker, model, type, series, modeltypename, internal_class_name, model_start, model_end, " +
"series_start, series_end, hsn, tsn, tsn2, car_tax, co2_class, base_price, engine_type, fuel, fuel2, " +
"emission_control, engine_design, cylinder, fuel_type, charge, valves, cubic, power_kw, power_ps, " +
"max_power, turning_moment, max_turning_moment, type_of_drive, gearing, gears, start_stop_automatic, " +
"emission_class, length, width, height, chassis, doors, car_class, seats, speed_up, max_speed, tank, tank2) " +
"VALUES" +
"(@maker_id, @model_id, @modeltype_id, @maker, @model, @type, @series, @modeltypename, @internal_class_name, STR_TO_DATE(@model_start, '%d/%m/%Y'), STR_TO_DATE(@model_end, '%d/%m/%Y'), " +
"STR_TO_DATE(@series_start, '%d/%m/%Y'), STR_TO_DATE(@series_end, '%d/%m/%Y'), @hsn, @tsn, @tsn2, @car_tax, @co2_class, @base_price, @engine_type, @fuel, @fuel2, " +
"@emission_control, @engine_design, @cylinder, @fuel_type, @charge, @valves, @cubic, @power_kw, @power_ps, " +
"@max_power, @turning_moment, @max_turning_moment, @type_of_drive, @gearing, @gears, @start_stop_automatic, " +
"@emission_class, @length, @width, @height, @chassis, @doors, @car_class, @seats, @speed_up, @max_speed, @tank, @tank2)",
MySQLExporter.DETAILS_TABLE);
//open connection
if (_mySqlExporter.connection.State == ConnectionState.Open || _mySqlExporter.OpenConnection() == true)
{
//create command and assign the query and connection from the constructor
MySqlCommand cmd = new MySqlCommand(query, _mySqlExporter.connection);
//cmd.CommandText = query;
SetSQLParameters(obj, cmd, makerId, modelId, typeId);
//Execute command
cmd.ExecuteNonQuery();
//close connection
_mySqlExporter.CloseConnection();
}
}
catch (Exception ex)
{
if (Core != null && Core.Log != null)
{
Core.Log.Error(string.Format("SQLCarDetails::Insert_CarDetails : {0}", ex.Message));
}
else
{
throw new Exception("SQLCarDetails::Insert_CarDetails", ex);
}
}
}
private void GetCarDetails()
{
foreach (ModelTypeObj type in modelTypesList)
{
string modelDetailsUrl = string.Format("{0}{1}", baseUrl, type.ModelTypeDetailsUrl);
try
{
string carContent = GetContent(modelDetailsUrl);
var htmlDoc = new HtmlDocument();
htmlDoc.LoadHtml(carContent);
HtmlNodeCollection cars_div = null;
if (htmlDoc.DocumentNode != null)
{
cars_div = htmlDoc.DocumentNode.SelectNodes(
"//*[@id=\"ctl00_ctl00_cphContentRow_cphContent_wucNFBAutokatalogDetail1_ctl01_updatePanelDetail\"]/div[2]/div[3]/div[1]/table/tbody");
}
if (cars_div != null)
{
CarDetailsObj carObj = new CarDetailsObj();
carObj.ModelTypeID = type.ModelTypeID;
carObj.ModelID = type.ModelID;
carObj.Maker = HttpUtility.HtmlDecode(cars_div.First().ChildNodes[1].ChildNodes[1].InnerText);
carObj.Model = HttpUtility.HtmlDecode(cars_div.First().ChildNodes[2].ChildNodes[1].InnerText);
carObj.Type = HttpUtility.HtmlDecode(cars_div.First().ChildNodes[3].ChildNodes[1].InnerText);
carObj.Series = HttpUtility.HtmlDecode(cars_div.First().ChildNodes[4].ChildNodes[1].InnerText);
carObj.ModelTypeName = type.ModelTypeName;
carObj.InternalClassName =
HttpUtility.HtmlDecode(cars_div.First().ChildNodes[5].ChildNodes[1].InnerText);
carObj.ModelStart =
HttpUtility.HtmlDecode(cars_div.First().ChildNodes[6].ChildNodes[1].InnerText);
carObj.ModelEnd =
HttpUtility.HtmlDecode(cars_div.First().ChildNodes[7].ChildNodes[1].InnerText);
carObj.SeriesStart =
HttpUtility.HtmlDecode(cars_div.First().ChildNodes[8].ChildNodes[1].InnerText);
carObj.SeriesEnd =
HttpUtility.HtmlDecode(cars_div.First().ChildNodes[9].ChildNodes[1].InnerText);
carObj.HSN = HttpUtility.HtmlDecode(cars_div.First().ChildNodes[10].ChildNodes[1].InnerText);
carObj.TSN = HttpUtility.HtmlDecode(cars_div.First().ChildNodes[11].ChildNodes[1].InnerText);
carObj.TSN2 = HttpUtility.HtmlDecode(cars_div.First().ChildNodes[12].ChildNodes[1].InnerText);
carObj.CarTax = HttpUtility.HtmlDecode(cars_div.First().ChildNodes[13].ChildNodes[1].InnerText);
carObj.CO2Class =
HttpUtility.HtmlDecode(cars_div.First().ChildNodes[14].ChildNodes[1].InnerText);
carObj.BasePrice =
HttpUtility.HtmlDecode(cars_div.First().ChildNodes[15].ChildNodes[1].InnerText);
// Motor & Antrieb
HtmlNodeCollection carEngine_div = null;
carEngine_div = htmlDoc.DocumentNode.SelectNodes(
"//*[@id=\"ctl00_ctl00_cphContentRow_cphContent_wucNFBAutokatalogDetail1_ctl01_updatePanelDetail\"]/div[2]/div[3]/div[2]/table/tbody");
if (carEngine_div != null)
{
carObj.EngineType =
HttpUtility.HtmlDecode(carEngine_div.First().ChildNodes[0].ChildNodes[1].InnerText);
carObj.Fuel =
HttpUtility.HtmlDecode(carEngine_div.First().ChildNodes[1].ChildNodes[1].InnerText);
carObj.Fuel2 =
HttpUtility.HtmlDecode(carEngine_div.First().ChildNodes[2].ChildNodes[1].InnerText);
carObj.EmissionControl =
HttpUtility.HtmlDecode(carEngine_div.First().ChildNodes[3].ChildNodes[1].InnerText);
carObj.EngineDesign =
HttpUtility.HtmlDecode(carEngine_div.First().ChildNodes[4].ChildNodes[1].InnerText);
carObj.Cylinder = carEngine_div.First().ChildNodes[5].ChildNodes[1].InnerText.ToInt32OrDefault(0);
//Convert.ToInt32();
carObj.FuelType =
HttpUtility.HtmlDecode(carEngine_div.First().ChildNodes[6].ChildNodes[1].InnerText);
carObj.Charge =
HttpUtility.HtmlDecode(carEngine_div.First().ChildNodes[7].ChildNodes[1].InnerText);
carObj.Valves = carEngine_div.First().ChildNodes[8].ChildNodes[1].InnerText.ToInt32OrDefault(0);
carObj.Cubic =
HttpUtility.HtmlDecode(carEngine_div.First().ChildNodes[9].ChildNodes[1].InnerText);
carObj.PowerKW = carEngine_div.First().ChildNodes[10].ChildNodes[1].InnerText.ToInt32OrDefault(0);
carObj.PowerPS = carEngine_div.First().ChildNodes[11].ChildNodes[1].InnerText
.ToInt32OrDefault(0);
carObj.MaxPower =
HttpUtility.HtmlDecode(carEngine_div.First().ChildNodes[12].ChildNodes[1].InnerText);
carObj.TurningMoment =
HttpUtility.HtmlDecode(carEngine_div.First().ChildNodes[13].ChildNodes[1].InnerText);
carObj.MaxTurningMoment =
HttpUtility.HtmlDecode(carEngine_div.First().ChildNodes[14].ChildNodes[1].InnerText);
carObj.TypeOfDrive =
HttpUtility.HtmlDecode(carEngine_div.First().ChildNodes[15].ChildNodes[1].InnerText);
carObj.Gearing =
HttpUtility.HtmlDecode(carEngine_div.First().ChildNodes[16].ChildNodes[1].InnerText);
carObj.Gears = carEngine_div.First().ChildNodes[17].ChildNodes[1].InnerText
.ToInt32OrDefault(0);
carObj.StartStopAutomatic =
HttpUtility.HtmlDecode(carEngine_div.First().ChildNodes[18].ChildNodes[1].InnerText);
carObj.EmissionClass =
HttpUtility.HtmlDecode(carEngine_div.First().ChildNodes[20].ChildNodes[1].InnerText);
}
// Maße & Gewicht
HtmlNodeCollection carDimensions_div = null;
carDimensions_div = htmlDoc.DocumentNode.SelectNodes(
"//*[@id=\"ctl00_ctl00_cphContentRow_cphContent_wucNFBAutokatalogDetail1_ctl01_updatePanelDetail\"]/div[2]/div[3]/div[3]/table/tbody");
if (carDimensions_div != null)
{
carObj.Length =
HttpUtility.HtmlDecode(carDimensions_div.First().ChildNodes[0].ChildNodes[1].InnerText);
carObj.Width =
HttpUtility.HtmlDecode(carDimensions_div.First().ChildNodes[1].ChildNodes[1].InnerText);
carObj.Height =
HttpUtility.HtmlDecode(carDimensions_div.First().ChildNodes[2].ChildNodes[1].InnerText);
}
// Karosserie & Fahrwerk
HtmlNodeCollection carChassis_div = null;
carChassis_div = htmlDoc.DocumentNode.SelectNodes(
"//*[@id=\"ctl00_ctl00_cphContentRow_cphContent_wucNFBAutokatalogDetail1_ctl01_updatePanelDetail\"]/div[2]/div[3]/div[4]/table/tbody");
if (carChassis_div != null)
{
carObj.Chassis =
HttpUtility.HtmlDecode(carChassis_div.First().ChildNodes[0].ChildNodes[1].InnerText);
carObj.Doors = carChassis_div.First().ChildNodes[1].ChildNodes[1].InnerText.ToInt32OrDefault(0);
carObj.CarClass =
HttpUtility.HtmlDecode(carChassis_div.First().ChildNodes[3].ChildNodes[1].InnerText);
carObj.Seats = carChassis_div.First().ChildNodes[4].ChildNodes[1].InnerText.ToInt32OrDefault(0);
}
//Messwerte Hersteller
HtmlNodeCollection carMeasured_div = null;
carMeasured_div = htmlDoc.DocumentNode.SelectNodes(
"//*[@id=\"ctl00_ctl00_cphContentRow_cphContent_wucNFBAutokatalogDetail1_ctl01_updatePanelDetail\"]/div[2]/div[3]/div[5]/table/tbody");
if (carMeasured_div != null)
{
carObj.SpeedUp =
HttpUtility.HtmlDecode(carMeasured_div.First().ChildNodes[0].ChildNodes[1].InnerText);
carObj.MaxSpeed =
HttpUtility.HtmlDecode(carMeasured_div.First().ChildNodes[1].ChildNodes[1].InnerText);
carObj.Tank =
HttpUtility.HtmlDecode(carMeasured_div.First().ChildNodes[26].ChildNodes[1].InnerText);
carObj.Tank2 =
HttpUtility.HtmlDecode(carMeasured_div.First().ChildNodes[27].ChildNodes[1].InnerText);
}
carDetailsList.Add(carObj);
}
}
catch (Exception ex)
{
if (Core != null && Core.Log != null)
{
Core.Log.Error(string.Format("ADACImporter::GetCarDetails : {0} ({1})", ex.Message, modelDetailsUrl));
}
else
{
throw new Exception("ADACImporter::GetCarDetails", ex);
}
}
}
if (Core != null && Core.Log != null)
{
Core.Log.Info(string.Format("{0} Car Detail Records imported.", carDetailsList.Count));
}
}
private static void SetSQLParameters(CarDetailsObj obj, MySqlCommand cmd, long makerId, long modelId,
long typeId)
{
cmd.Parameters.AddWithValue("@maker_id", makerId);
cmd.Parameters.AddWithValue("@model_id", modelId);
cmd.Parameters.AddWithValue("@modeltype_id", typeId);
cmd.Parameters.AddWithValue("@maker", obj.Maker);
cmd.Parameters.AddWithValue("@model", obj.Model);
cmd.Parameters.AddWithValue("@type", obj.Type);
cmd.Parameters.AddWithValue("@series", obj.Series);
cmd.Parameters.AddWithValue("@modeltypename", obj.ModelTypeName);
cmd.Parameters.AddWithValue("@internal_class_name", obj.InternalClassName);
//dateTime ?
cmd.Parameters.AddWithValue("@model_start", obj.ModelStart.Length <= 7 ? string.Format("01/{0}", obj.ModelStart) : obj.ModelStart);
if (string.IsNullOrWhiteSpace(obj.ModelEnd))
{
cmd.Parameters.AddWithValue("@model_end", obj.ModelEnd);
}
else
{
string[] parts = obj.ModelEnd.Split('/');
string lastday = DateTime.DaysInMonth(Convert.ToInt32(parts[1]), Convert.ToInt32(parts[0])).ToString();
cmd.Parameters.AddWithValue("@model_end",
obj.ModelEnd.Length <= 7 ? string.Format("{0}/{1}", lastday, obj.ModelEnd) : obj.ModelEnd);
}
cmd.Parameters.AddWithValue("@series_start", obj.SeriesStart.Length <= 7 ? string.Format("01/{0}", obj.SeriesStart) : obj.SeriesStart);
if (string.IsNullOrWhiteSpace(obj.SeriesEnd))
{
cmd.Parameters.AddWithValue("@series_end", obj.SeriesEnd);
}
else
{
string[] parts = obj.SeriesEnd.Split('/');
string lastday = DateTime.DaysInMonth(Convert.ToInt32(parts[1]), Convert.ToInt32(parts[0])).ToString();
cmd.Parameters.AddWithValue("@series_end",
obj.SeriesEnd.Length <= 7 ? string.Format("{0}/{1}", lastday, obj.SeriesEnd) : obj.SeriesEnd);
}
cmd.Parameters.AddWithValue("@hsn", obj.HSN);
cmd.Parameters.AddWithValue("@tsn", obj.TSN);
cmd.Parameters.AddWithValue("@tsn2", obj.TSN2);
cmd.Parameters.AddWithValue("@car_tax", obj.CarTax);
cmd.Parameters.AddWithValue("@co2_class", obj.CO2Class);
cmd.Parameters.AddWithValue("@base_price", obj.BasePrice);
cmd.Parameters.AddWithValue("@engine_type", obj.EngineType);
cmd.Parameters.AddWithValue("@fuel", obj.Fuel);
cmd.Parameters.AddWithValue("@fuel2", obj.Fuel2);
cmd.Parameters.AddWithValue("@emission_control", obj.EmissionControl);
cmd.Parameters.AddWithValue("@engine_design", obj.EngineDesign);
cmd.Parameters.AddWithValue("@cylinder", obj.Cylinder);
cmd.Parameters.AddWithValue("@fuel_type", obj.FuelType);
cmd.Parameters.AddWithValue("@charge", obj.Charge);
cmd.Parameters.AddWithValue("@valves", obj.Valves);
cmd.Parameters.AddWithValue("@cubic", obj.Cubic);
cmd.Parameters.AddWithValue("@power_kw", obj.PowerKW);
cmd.Parameters.AddWithValue("@power_ps", obj.PowerPS);
cmd.Parameters.AddWithValue("@max_power", obj.MaxPower);
cmd.Parameters.AddWithValue("@turning_moment", obj.TurningMoment);
cmd.Parameters.AddWithValue("@max_turning_moment", obj.MaxTurningMoment);
cmd.Parameters.AddWithValue("@type_of_drive", obj.TypeOfDrive);
cmd.Parameters.AddWithValue("@gearing", obj.Gearing);
cmd.Parameters.AddWithValue("@gears", obj.Gears);
cmd.Parameters.AddWithValue("@start_stop_automatic", obj.StartStopAutomatic);
cmd.Parameters.AddWithValue("@emission_class", obj.EmissionClass);
cmd.Parameters.AddWithValue("@length", obj.Length);
cmd.Parameters.AddWithValue("@width", obj.Width);
cmd.Parameters.AddWithValue("@height", obj.Height);
cmd.Parameters.AddWithValue("@chassis", obj.Chassis);
cmd.Parameters.AddWithValue("@doors", obj.Doors);
cmd.Parameters.AddWithValue("@car_class", obj.CarClass);
cmd.Parameters.AddWithValue("@seats", obj.Seats);
cmd.Parameters.AddWithValue("@speed_up", obj.SpeedUp);
cmd.Parameters.AddWithValue("@max_speed", obj.MaxSpeed);
cmd.Parameters.AddWithValue("@tank", obj.Tank);
cmd.Parameters.AddWithValue("@tank2", obj.Tank2);
}
public void Update_CarDetails(CarDetailsObj obj, long makerId, long modelId, long typeId, long id)
{
try
{
string query = string.Format("UPDATE {0} SET " +
"maker_id=@maker_id, " +
"model_id=@model_id, " +
"modeltype_id=@modeltype_id, " +
"maker=@maker, " +
"model=@model, " +
"type=@type, " +
"series=@series, " +
"modeltypename=@modeltypename, " +
"internal_class_name=@internal_class_name, " +
"model_start=STR_TO_DATE(@model_start, '%d/%m/%Y'), " +
"model_end=STR_TO_DATE( @model_end, '%d/%m/%Y'), " +
"series_start=STR_TO_DATE(@series_start, '%d/%m/%Y') , " +
"series_end=STR_TO_DATE(@series_end, '%d/%m/%Y') , " +
"hsn=@hsn, " +
"tsn=@tsn, " +
"tsn2=@tsn2, " +
"car_tax=@car_tax, " +
"co2_class=@co2_class, " +
"base_price=@base_price, " +
"engine_type=@engine_type, " +
"fuel=@fuel, " +
"fuel2=@fuel2, " +
"emission_control=@emission_control, " +
"engine_design=@engine_design, " +
"cylinder=@cylinder, " +
"fuel_type=@fuel_type, " +
"charge=@charge, " +
"valves=@valves, " +
"cubic=@cubic, " +
"power_kw=@power_kw, " +
"power_ps=@power_ps, " +
"max_power=@max_power, " +
"turning_moment=@turning_moment, " +
"max_turning_moment=@max_turning_moment, " +
"type_of_drive=@type_of_drive, " +
"gearing=@gearing, " +
"gears=@gears, " +
"start_stop_automatic=@start_stop_automatic, " +
"emission_class=@emission_class, " +
"length=@length, " +
"width=@width, " +
"height=@height, " +
"chassis=@chassis, " +
"doors=@doors, " +
"car_class=@car_class, " +
"seats=@seats, " +
"speed_up=@speed_up, " +
"max_speed=@max_speed, " +
"tank=@tank, " +
"tank2=@tank2" +
" WHERE id={1}", MySQLExporter.DETAILS_TABLE,
id);
//Open connection
if (_mySqlExporter.connection.State == ConnectionState.Open || _mySqlExporter.OpenConnection() == true)
{
//create mysql command
MySqlCommand cmd = new MySqlCommand
{
CommandText = query,
Connection = _mySqlExporter.connection
};
//Assign the query using CommandText
//Assign the connection using Connection
SetSQLParameters(obj, cmd, makerId, modelId, typeId);
//Execute query
cmd.ExecuteNonQuery();
//close connection
_mySqlExporter.CloseConnection();
}
}catch (Exception ex)
{
if (Core != null && Core.Log != null)
{
Core.Log.Error(string.Format("SQLCarDetails::Update_CarDetails : {0}", ex.Message));
}
else
{
throw new Exception("SQLCarDetails::Update_CarDetails", ex);
}
}
}
