public void SetModelYears(ModelTypeObj obj)
{
try
{
string query = "CREATE_MODEL_YEARS_BY_MODEL_ID";
//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.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new MySqlParameter("m_id", obj.ModelTypeID));
//Execute command
cmd.ExecuteNonQuery();
//close connection
_mySqlExporter.CloseConnection();
}
}
catch (Exception ex)
{
if (Core != null && Core.Log != null)
{
Core.Log.Error(string.Format("SQLCarModelType::SetModelYears : {0}", ex.Message));
}
else
{
throw new Exception("SQLCarModelType::SetModelYears", ex);
}
}
}
public void SelectModelTypesFromDB()
{
string query = string.Format("SELECT * FROM {0}", MySQLExporter.MODELTYPE_TABLE);
/*
* //Create a list to store the result
* List<string>[] list = new List<string>[3];
* list[0] = new List<string>();
* list[1] = new List<string>();
* list[2] = new List<string>();
*/
modelTypesList.Clear();
modelTypesList.Capacity = 1;
//Open connection
if (this.OpenConnection() == true)
{
//Create Command
MySqlCommand cmd = new MySqlCommand(query, connection);
//Create a data reader and Execute the command
MySqlDataReader dataReader = cmd.ExecuteReader();
//Read the data and store them in the list
while (dataReader.Read())
{
ModelTypeObj obj = new ModelTypeObj();
obj.MakerID = Convert.ToInt32(dataReader["maker_id"] + "");
obj.ModelID = Convert.ToInt32(dataReader["model_id"] + "");
obj.ModelTypeID = Convert.ToInt32(dataReader["modeltype_id"] + "");
obj.ModelTypeName = dataReader["name"] + "";
obj.ModelTypeDetailsUrl = dataReader["type_url"] + "";
modelTypesList.Capacity++;
modelTypesList.Add(obj);
/*
* list[0].Add(dataReader["id"] + "");
* list[1].Add(dataReader["name"] + "");
* list[2].Add(dataReader["age"] + "");
*/
}
//close Data Reader
dataReader.Close();
//close Connection
this.CloseConnection();
}
}
public void Update_CarModelType(ModelTypeObj obj, long makerId, long modelId, long id)
{
try
{
string query = string.Format("UPDATE {0} SET " +
"maker_id=@maker_id, " +
"model_id=@model_id, " +
"modeltype_id=@modeltype_id," +
"name=@name," +
"cubic=@cubic," +
"fuel=@fuel," +
"power=@power," +
"tank=@tank," +
"from_year=@from_year," +
"to_year=@to_year," +
"chassis=@chassis," +
"doors=@doors," +
"type_url=@type_url" +
" WHERE id={1}", MySQLExporter.MODELTYPE_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);
//Execute query
cmd.ExecuteNonQuery();
//close connection
_mySqlExporter.CloseConnection();
}
}
catch (Exception ex)
{
if (Core != null && Core.Log != null)
{
Core.Log.Error(string.Format("SQLCarModelType::Update_CarModel : {0}", ex.Message));
}
else
{
throw new Exception("SQLCarModelType::Update_CarModel", ex);
}
}
}
private static void SetSQLParameters(ModelTypeObj obj, MySqlCommand cmd, long makerId, long modelId)
{
cmd.Parameters.AddWithValue("@maker_id", makerId);
cmd.Parameters.AddWithValue("@model_id", modelId);
cmd.Parameters.AddWithValue("@modeltype_id", obj.ModelTypeID);
cmd.Parameters.AddWithValue("@name", obj.ModelTypeName);
cmd.Parameters.AddWithValue("@cubic", obj.ModelTypeCubic);
cmd.Parameters.AddWithValue("@fuel", obj.ModelTypeFuel);
cmd.Parameters.AddWithValue("@power", obj.ModelTypePower);
cmd.Parameters.AddWithValue("@tank", obj.ModelTypeTank);
cmd.Parameters.AddWithValue("@from_year", obj.ModelTypeFromYear);
cmd.Parameters.AddWithValue("@to_year", obj.ModelTypeToYear);
cmd.Parameters.AddWithValue("@chassis", obj.ModelTypeChassis);
cmd.Parameters.AddWithValue("@doors", obj.ModelTypeDoors);
cmd.Parameters.AddWithValue("@type_url", obj.ModelTypeDetailsUrl);
}
private void GetModelTypes(ModelObj modelObj)
{
string modelTypesUrl = string.Format("{0}{1}", this.baseUrl, modelObj.ModelUrlPath);
string modelsContent = GetContent(modelTypesUrl);
var htmlDoc = new HtmlDocument();
htmlDoc.LoadHtml(modelsContent);
HtmlNodeCollection types_div = null;
if (htmlDoc.DocumentNode != null && htmlDoc.ParseErrors != null && !htmlDoc.ParseErrors.Any())
{
types_div = htmlDoc.DocumentNode.SelectNodes("//*[@id=\"modelis\"]/table");
}
if (types_div != null)
{
foreach (var node in types_div.First().ChildNodes)
{
ModelTypeObj typeObj = new ModelTypeObj();
typeObj.ModelTypeID = modelTypesList.Count + 1;
typeObj.ModelID = modelObj.ModelID;
typeObj.ModelTypeName = node.ChildNodes[0].InnerText;
typeObj.ModelTypeCubic = node.ChildNodes[1].InnerText
.Substring(0, node.ChildNodes[1].InnerText.IndexOf("cm3"));
typeObj.ModelTypeFuel = node.ChildNodes[2].InnerText;
typeObj.ModelTypePower = node.ChildNodes[3].InnerText;
typeObj.ModelTypeTank = node.ChildNodes[4].InnerText;
typeObj.ModelTypeFromYear = node.ChildNodes[5].InnerText;
typeObj.ModelTypeToYear = node.ChildNodes[6].InnerText;
string value = node.ChildNodes[0].Attributes.AttributesWithName("onclick").First().Value;
value = value.Substring(value.IndexOf("'") + 1, value.LastIndexOf("'") - value.IndexOf("'") - 1);
typeObj.ModelTypeDetailsUrl = value;
modelTypesList.Add(typeObj);
}
}
}
public void Insert_CarModelType(ModelTypeObj obj, long makerId, long modelId)
{
try
{
string query = string.Format("INSERT INTO {0} " +
"(maker_id, model_id, modeltype_id, name, cubic, fuel, power, tank, from_year, to_year, chassis, doors, type_url) " +
"VALUES" +
"(@maker_id, @model_id, @modeltype_id, @name, @cubic, @fuel, @power, @tank, @from_year, @to_year, @chassis, @doors, @type_url)",
MySQLExporter.MODELTYPE_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);
//Execute command
cmd.ExecuteNonQuery();
//close connection
_mySqlExporter.CloseConnection();
}
}
catch (Exception ex)
{
if (Core != null && Core.Log != null)
{
Core.Log.Error(string.Format("SQLCarModelType::Insert_CarModel : {0}", ex.Message));
}
else
{
throw new Exception("SQLCarModelType::Insert_CarModel", ex);
}
}
}
private void GetModelTypes(ModelObj modelObj)
{
string modelTypesUrl = string.Format("{0}{1}", baseUrl, modelObj.ModelUrlPath);
string modelsContent = GetContent(modelTypesUrl);
var htmlDoc = new HtmlDocument();
htmlDoc.LoadHtml(modelsContent);
HtmlNodeCollection types_div = null;
if (htmlDoc.DocumentNode != null)
{
types_div = htmlDoc.DocumentNode.SelectNodes("//*[@id=\"car_db_select_hits\"]/tbody");
}
if (types_div != null)
{
int limit_cnt = 0;
foreach (var node in types_div.First().ChildNodes)
{
try
{
if (node.Name.ToLower().Equals("tr"))
{
limit_cnt++;
ModelTypeObj typeObj = new ModelTypeObj();
typeObj.ModelTypeID = modelTypesList.Count + 1;
typeObj.ModelID = modelObj.ModelID;
typeObj.MakerName = modelObj.MakerName;
typeObj.ModelName = modelObj.ModelName;
typeObj.ModelTypeDetailsUrl = node.ChildNodes[3].ChildNodes[1].Attributes
.AttributesWithName("href")
.First().Value;
typeObj.ModelTypeName = node.ChildNodes[5].InnerText.Trim();
typeObj.ModelTypeChassis = node.ChildNodes[7].InnerText.Trim();
typeObj.ModelTypeDoors = node.ChildNodes[9].InnerText.Trim().ToInt32OrDefault(0);
typeObj.ModelTypeFuel = node.ChildNodes[11].InnerText.Trim();
typeObj.ModelTypePower = node.ChildNodes[13].InnerText.Trim();
typeObj.ModelTypeCubic = node.ChildNodes[17].InnerText.Trim();
//link to details page
//Type Name
//Chassis
//Doors
//Fuel
//KW
modelTypesList.Add(typeObj);
//DEBUG: Break after x number of model types
if (IsLimited(limit_cnt))
{
break;
}
}
}
catch (Exception ex)
{
if (Core != null && Core.Log != null)
{
Core.Log.Error(string.Format("ADACImporter::GetModelTypes : {0}", ex.Message));
}
else
{
throw new Exception("ADACImporter::GetModelTypes", ex);
}
}
}
}
}
