public abstract Task<bool> UpdatePOI(POI poi);
private bool updateSecondaryCategoryTable(POI poi)
{
const string insertInSecondaryCategoryStatement = "INSERT INTO \"SECONDARYCATEGORY\" VALUES('";
const string deleteInSecondaryCategoryStatement = "DELETE FROM \"SECONDARYCATEGORY\" WHERE POI_ID='";
const string selectSecondaryCategoryEntriesForPoiId = "SELECT * FROM SECONDARYCATEGORY WHERE POI_ID='";
string poiId = poi.ID;
LocationCategory[] categories = poi.Description.Categories;
string fullSelectSecondaryCategoryEntriesForPoid = selectSecondaryCategoryEntriesForPoiId + poiId + "'";
string fullDeleteSecondaryCategoryEntriesForPoid = deleteInSecondaryCategoryStatement + poiId + "'";
#if DEBUG
SystemLogger.Log (SystemLogger.Module.PLATFORM, "updateSecondaryCategoryTable: SELECT statement: " + fullSelectSecondaryCategoryEntriesForPoid);
SystemLogger.Log (SystemLogger.Module.PLATFORM, "updateSecondaryCategoryTable: DELETE statement: " + fullDeleteSecondaryCategoryEntriesForPoid);
#endif
IResultSet resultSetSelect = mapDDBB.ExecuteSQLQuery (mapDDBB.GetDatabase (mapDDBBName), fullSelectSecondaryCategoryEntriesForPoid);
int countPoi = resultSetSelect.GetRowCount ();
if (countPoi > 0) {
mapDDBB.ExecuteSQLQuery (mapDDBB.GetDatabase (mapDDBBName), fullDeleteSecondaryCategoryEntriesForPoid);
}
int numElements = categories.Length;
bool[] results = new bool[numElements];
int j = 0;
foreach (LocationCategory item in categories) {
string category = item.Name;
string fullInsertSecondCategoryStatement = insertInSecondaryCategoryStatement + category + "'," + "'" + poiId + "')";
#if DEBUG
SystemLogger.Log (SystemLogger.Module.PLATFORM, "updateSecondaryCategoryTable: INSERT statement: " + fullInsertSecondCategoryStatement);
#endif
bool isSqlStatementOk = mapDDBB.ExecuteSQLStatement (mapDDBB.GetDatabase (mapDDBBName), fullInsertSecondCategoryStatement);
results[j] = isSqlStatementOk;
#if DEBUG
SystemLogger.Log (SystemLogger.Module.PLATFORM, "updateSecondaryCategoryTable: Is insert into SECONDARYCATEGORY OK:" + isSqlStatementOk);
SystemLogger.Log (SystemLogger.Module.PLATFORM, "updateSecondaryCategoryTable: results[" + j + "]:" + results[j]);
#endif
++j;
}
#if DEBUG
const string selectSecondaryCategory = "SELECT * FROM \"SECONDARYCATEGORY\"";
IResultSet resultSet = mapDDBB.ExecuteSQLQuery (mapDDBB.GetDatabase (mapDDBBName), selectSecondaryCategory);
int numRows = resultSet.GetRowCount ();
for (int i = 0; i < numRows; i++) {
string resultId = resultSet.GetString ("POI_ID");
string resultSecCategName = resultSet.GetString ("SECONDCATEGORYNAME");
resultSet.MoveToNext ();
SystemLogger.Log (SystemLogger.Module.PLATFORM, "POI_ID in SECONDARYCATEGORY table:" + resultId);
SystemLogger.Log (SystemLogger.Module.PLATFORM, "SECONDCATEGORYNAME in SECONDARYCATEGORY table:" + resultSecCategName);
}
#endif
bool areAllInsertOK = true;
for (int i = 0; i < numElements; i++)
{
areAllInsertOK = areAllInsertOK && results[i];
}
return areAllInsertOK;
}
public abstract bool UpdatePOI(POI poi);
private bool updateLocationDescriptionTable(POI poi)
{
const string insertInLocationDescriptionStatement = "INSERT INTO \"LOCATIONDESCRIPTION\" VALUES('";
const string updateInLocationDescriptionStatement = "UPDATE \"LOCATIONDESCRIPTION\" SET DESCRIPTION='";
const string selectLocationDescriptionForPoi = "SELECT * FROM \"LOCATIONDESCRIPTION\" WHERE POI_ID='";
string poiId = poi.ID;
string description = poi.Description.Description;
string name = poi.Description.Name;
string categoryMainName = poi.Description.CategoryMain.Name;
string fullInsertLocationDescriptionStatement = insertInLocationDescriptionStatement + description + "'," + "'" + name + "'," + "'" + categoryMainName.ToString () + "','" + poiId + "')";
string fullUpdateInLocationDescriptionStatement = updateInLocationDescriptionStatement + description + "', NAME='" + name + "', CATEGORYMAINNAME='" + categoryMainName + "' WHERE POI_ID='" + poiId + "'";
string fullSelectLocationDescriptionForPoi = selectLocationDescriptionForPoi + poiId + "'";
#if DEBUG
SystemLogger.Log (SystemLogger.Module.PLATFORM, "updateLocationDescriptionTable INSERT statement: " + fullInsertLocationDescriptionStatement);
SystemLogger.Log (SystemLogger.Module.PLATFORM, "updateLocationDescriptionTable UPDATE statement: " + fullUpdateInLocationDescriptionStatement);
SystemLogger.Log (SystemLogger.Module.PLATFORM, "updateLocationDescriptionTable SELECT statement: " + fullSelectLocationDescriptionForPoi);
#endif
IResultSet resultSetSelect = mapDDBB.ExecuteSQLQuery (mapDDBB.GetDatabase (mapDDBBName), fullSelectLocationDescriptionForPoi);
int selectCount = resultSetSelect.GetRowCount ();
bool isSqlStatementOk;
if (selectCount > 0) {
isSqlStatementOk = mapDDBB.ExecuteSQLStatement (mapDDBB.GetDatabase (mapDDBBName), fullUpdateInLocationDescriptionStatement);
#if DEBUG
SystemLogger.Log (SystemLogger.Module.PLATFORM, "updateLocationDescriptionTable: UPDATING:" + isSqlStatementOk);
#endif
} else {
isSqlStatementOk = mapDDBB.ExecuteSQLStatement (mapDDBB.GetDatabase (mapDDBBName), fullInsertLocationDescriptionStatement);
#if DEBUG
SystemLogger.Log (SystemLogger.Module.PLATFORM, "updateLocationDescriptionTable: INSERTING:" + isSqlStatementOk);
#endif
}
#if DEBUG
SystemLogger.Log (SystemLogger.Module.PLATFORM, "Is INSERT/UPDATE into LOCATIONDESCRIPTION OK:" + isSqlStatementOk);
const string selectFromLocationDescription = "SELECT * FROM \"LOCATIONDESCRIPTION\"";
IResultSet resultSet = mapDDBB.ExecuteSQLQuery (mapDDBB.GetDatabase (mapDDBBName), selectFromLocationDescription);
int numRows = resultSet.GetRowCount ();
for (int i = 0; i < numRows; i++) {
string resultId = resultSet.GetString ("POI_ID");
string resultDescription = resultSet.GetString ("DESCRIPTION");
string resultName = resultSet.GetString ("NAME");
string resultCatMainName = resultSet.GetString ("CATEGORYMAINNAME");
resultSet.MoveToNext ();
SystemLogger.Log (SystemLogger.Module.PLATFORM, "POI_ID in LOCATIONDESCRIPTION table:" + resultId);
SystemLogger.Log (SystemLogger.Module.PLATFORM, "DESCRIPTION in LOCATIONDESCRIPTION table:" + resultDescription);
SystemLogger.Log (SystemLogger.Module.PLATFORM, "NAME in LOCATIONDESCRIPTION table:" + resultName);
SystemLogger.Log (SystemLogger.Module.PLATFORM, "CATEGORYMAINNAME in LOCATIONDESCRIPTION table:" + resultCatMainName);
}
#endif
return isSqlStatementOk;
}
private bool updatePoiTable(POI poi)
{
const string insertInPoiStatement = "INSERT INTO \"POI\" VALUES('";
const string updateInPoiStatement = "UPDATE \"POI\" SET CATEGORYMAIN='";
const string selectPoiFromPoiId = "SELECT * FROM \"POI\" WHERE ID='";
string poiId = poi.ID;
string category = poi.Description.Name;
string fullInsertPoiStatement = insertInPoiStatement + poiId + "'," + "'" + category + "')";
string fullUpdatePoiStatement = updateInPoiStatement + category + "' WHERE ID='" + poiId + "'";
string fullSelectPoiFromPoiId = selectPoiFromPoiId + poiId + "'";
#if DEBUG
SystemLogger.Log (SystemLogger.Module.PLATFORM, "updatePoiTable INSERT statement: " + fullInsertPoiStatement);
SystemLogger.Log (SystemLogger.Module.PLATFORM, "updatePoiTable UPDATE statement: " + fullUpdatePoiStatement);
SystemLogger.Log (SystemLogger.Module.PLATFORM, "updatePoiTable SELECT statement: " + fullSelectPoiFromPoiId);
#endif
bool isSqlStatementOk;
IResultSet resultSetSelect = mapDDBB.ExecuteSQLQuery (mapDDBB.GetDatabase (mapDDBBName), fullSelectPoiFromPoiId);
int selectCount = resultSetSelect.GetRowCount ();
if (selectCount > 0) {
isSqlStatementOk = mapDDBB.ExecuteSQLStatement (mapDDBB.GetDatabase (mapDDBBName), fullUpdatePoiStatement);
#if DEBUG
SystemLogger.Log (SystemLogger.Module.PLATFORM, "updatePoiTable: Result of UPDATE POI table:" + isSqlStatementOk);
#endif
} else {
isSqlStatementOk = mapDDBB.ExecuteSQLStatement (mapDDBB.GetDatabase (mapDDBBName), fullInsertPoiStatement);
#if DEBUG
SystemLogger.Log (SystemLogger.Module.PLATFORM, "updatePoiTable: Result of INSERT POI table:" + isSqlStatementOk);
#endif
}
#if DEBUG
SystemLogger.Log (SystemLogger.Module.PLATFORM, "updatePoiTable: Is INSERT/UPDATE into POI OK:" + isSqlStatementOk);
IResultSet resultSet = mapDDBB.ExecuteSQLQuery (mapDDBB.GetDatabase (mapDDBBName), "SELECT * FROM \"POI\"");
int numRows = resultSet.GetRowCount ();
for (int i = 0; i < numRows; i++) {
string resultId = resultSet.GetString ("ID");
string resultText = resultSet.GetString ("CATEGORYMAIN");
resultSet.MoveToNext ();
SystemLogger.Log (SystemLogger.Module.PLATFORM, "updatePoiTable: ID in POI table:" + resultId);
SystemLogger.Log (SystemLogger.Module.PLATFORM, "updatePoiTable: CATEGORYMAIN in POI table:" + resultText);
}
#endif
return isSqlStatementOk;
}
private POI getPoiFromId(string id)
{
POI poi = new POI ();
LocationCoordinate locCoord = new LocationCoordinate ();
LocationDescription locDesc = new LocationDescription ();
LocationCategory locCat = new LocationCategory ();
List<LocationCategory> listCategories = new List<LocationCategory> ();
string selectFromPOI = "SELECT * FROM \"POI\" WHERE ID='" + id + "'";
IResultSet resultSelectPoi = mapDDBB.ExecuteSQLQuery (mapDDBB.GetDatabase (mapDDBBName), selectFromPOI);
if (resultSelectPoi.GetRowCount () > 0) {
string selectLocationCoordinateForPoId = "SELECT * FROM \"LOCATIONCOORDINATE\" WHERE POI_ID='" + id + "'";
string selectLocationDescriptionForPoiId = "SELECT * FROM \"LOCATIONDESCRIPTION\" WHERE POI_ID='" + id + "'";
string selectSecondaryCategoryForPoiId = "SELECT * FROM \"SECONDARYCATEGORY\" WHERE POI_ID='" + id + "'";
IResultSet resultSetSelectLocCoord = mapDDBB.ExecuteSQLQuery (mapDDBB.GetDatabase (mapDDBBName), selectLocationCoordinateForPoId);
IResultSet resultSetSelectLocDescr = mapDDBB.ExecuteSQLQuery (mapDDBB.GetDatabase (mapDDBBName), selectLocationDescriptionForPoiId);
IResultSet resultSetSelectSecondCat = mapDDBB.ExecuteSQLQuery (mapDDBB.GetDatabase (mapDDBBName), selectSecondaryCategoryForPoiId);
double latitude = resultSetSelectLocCoord.GetDouble ("LATITUDE");
double longitude = resultSetSelectLocCoord.GetDouble ("LONGITUDE");
double altitude = resultSetSelectLocCoord.GetDouble ("ALTITUDE");
float horAccur = resultSetSelectLocCoord.GetFloat ("HORIZONTALACCURACY");
float verAccur = resultSetSelectLocCoord.GetFloat ("VERTICALACCURACY");
string description = resultSetSelectLocDescr.GetString ("DESCRIPTION");
string name = resultSetSelectLocDescr.GetString ("NAME");
string catMainName = resultSetSelectLocDescr.GetString ("CATEGORYMAINNAME");
poi.ID = id;
locCoord.XCoordinate = latitude;
locCoord.YCoordinate = longitude;
locCoord.ZCoordinate = altitude;
locCoord.XDoP = horAccur;
locCoord.YDoP = verAccur;
poi.Location = locCoord;
locDesc.Description = description;
locDesc.Name = name;
locCat.Name = catMainName;
locDesc.CategoryMain = locCat;
int numRowsSecCat = resultSetSelectSecondCat.GetRowCount ();
for (int i = 0; i < numRowsSecCat; i++) {
string secondCatName = resultSetSelectSecondCat.GetString ("SECONDCATEGORYNAME");
LocationCategory locCategory = new LocationCategory ();
locCategory.Name = secondCatName;
listCategories.Add (locCategory);
resultSetSelectSecondCat.MoveToNext ();
}
locDesc.Categories = listCategories.ToArray ();
poi.Description = locDesc;
poi.Category = locCat;
} else {
poi = null;
}
return poi;
}
private bool updateLocationCoordinateTable(POI poi)
{
const string insertInLocationCoordinateStatement = "INSERT INTO \"LOCATIONCOORDINATE\" VALUES(";
const string updateInLocationCoordinateStatement = "UPDATE \"LOCATIONCOORDINATE\" SET LATITUDE=";
const string selectFromLocationCoordinateForPoi = "SELECT * FROM \"LOCATIONCOORDINATE\" WHERE POI_ID='";
string poiId = poi.ID;
string latitude = poi.Location.XCoordinateString();
string longitude = poi.Location.YCoordinateString();
string altitude = poi.Location.ZCoordinateString();
string horAccur = poi.Location.XDoPString();
string verAccur = poi.Location.YDoPString();
string fullInsertLocationCoordinateStatement = insertInLocationCoordinateStatement + latitude + "," + longitude + "," + altitude + "," + horAccur + "," + verAccur + ",'" + poiId + "')";
string fullUpdateLocationCoordinateStatement = updateInLocationCoordinateStatement + latitude + ", LONGITUDE=" + longitude + ", ALTITUDE=" + altitude + ", HORIZONTALACCURACY=" + horAccur + ", VERTICALACCURACY=" + verAccur + " WHERE POI_ID='" + poiId + "'";
string fullSelectLocationCoordinateForPoi = selectFromLocationCoordinateForPoi + poiId + "'";
#if DEBUG
SystemLogger.Log (SystemLogger.Module.PLATFORM, "updateLocationCoordinateTable INSERT statement: " + fullInsertLocationCoordinateStatement);
SystemLogger.Log (SystemLogger.Module.PLATFORM, "updateLocationCoordinateTable UPDATE statement: " + fullUpdateLocationCoordinateStatement);
SystemLogger.Log (SystemLogger.Module.PLATFORM, "updateLocationCoordinateTable SELECT statement: " + fullSelectLocationCoordinateForPoi);
#endif
IResultSet resultSetSelect = mapDDBB.ExecuteSQLQuery (mapDDBB.GetDatabase (mapDDBBName), fullSelectLocationCoordinateForPoi);
int selectCount = resultSetSelect.GetRowCount ();
bool isSqlStatementOk;
if (selectCount > 0) {
isSqlStatementOk = mapDDBB.ExecuteSQLStatement (mapDDBB.GetDatabase (mapDDBBName), fullUpdateLocationCoordinateStatement);
#if DEBUG
SystemLogger.Log (SystemLogger.Module.PLATFORM, "updateLocationCoordinateTable: Result of UPDATE LOCATIONCOORDINATE table:" + isSqlStatementOk);
#endif
} else {
isSqlStatementOk = mapDDBB.ExecuteSQLStatement (mapDDBB.GetDatabase (mapDDBBName), fullInsertLocationCoordinateStatement);
#if DEBUG
SystemLogger.Log (SystemLogger.Module.PLATFORM, "updateLocationCoordinateTable: Result of UPDATE LOCATIONCOORDINATE table:" + isSqlStatementOk);
#endif
}
#if DEBUG
SystemLogger.Log (SystemLogger.Module.PLATFORM, "Is INSERT/UPDATE into LOCATIONCOORDINATE OK:" + isSqlStatementOk);
const string selectFromLocationCoordinate = "SELECT * FROM \"LOCATIONCOORDINATE\"";
IResultSet resultSet = mapDDBB.ExecuteSQLQuery (mapDDBB.GetDatabase (mapDDBBName), selectFromLocationCoordinate);
int numRows = resultSet.GetRowCount ();
for (int i = 0; i < numRows; i++) {
string resultId = resultSet.GetString ("POI_ID");
double resultLatitude = resultSet.GetDouble ("LATITUDE");
double resultLongitude = resultSet.GetDouble ("LONGITUDE");
double resultAltitude = resultSet.GetDouble ("ALTITUDE");
float resultHorAccur = resultSet.GetFloat ("HORIZONTALACCURACY");
float resultVerAccur = resultSet.GetFloat ("VERTICALACCURACY");
resultSet.MoveToNext ();
SystemLogger.Log (SystemLogger.Module.PLATFORM, "POI_ID in POI table:" + resultId);
SystemLogger.Log (SystemLogger.Module.PLATFORM, "LATITUDE in POI table:" + resultLatitude);
SystemLogger.Log (SystemLogger.Module.PLATFORM, "LONGITUDE in POI table:" + resultLongitude);
SystemLogger.Log (SystemLogger.Module.PLATFORM, "ALTITUDE in POI table:" + resultAltitude);
SystemLogger.Log (SystemLogger.Module.PLATFORM, "HORIZONTALACCURACY in POI table:" + resultHorAccur);
SystemLogger.Log (SystemLogger.Module.PLATFORM, "VERTICALACCURACY in POI table:" + resultVerAccur);
}
#endif
return isSqlStatementOk;
}
public override bool UpdatePOI(POI poi)
{
#if DEBUG
SystemLogger.Log (SystemLogger.Module.PLATFORM, "Before update POI table");
#endif
bool isPoiOk = updatePoiTable (poi);
bool isLocCoordOk = updateLocationCoordinateTable (poi);
bool isLocDescrOK = updateLocationDescriptionTable (poi);
bool isSecondCatOK = updateSecondaryCategoryTable (poi);
return (isPoiOk && isLocCoordOk && isLocDescrOK && isSecondCatOK);
}
public abstract bool UpdatePOI(POI poi);
public abstract Task <bool> UpdatePOI(POI poi);
public override bool UpdatePOI(POI poi)
{
throw new NotImplementedException();
}
public override async Task<bool> UpdatePOI(POI poi)
{
throw new NotImplementedException();
}
