public static void GetUserInputChanges(DataGridView dataGridView, ref Metadata metadata, FileEntryAttribute fileEntry)
{
int columnIndex = DataGridViewHandler.GetColumnIndexUserInput(dataGridView, fileEntry);
if (columnIndex == -1)
{
return; //Column has not yet become aggregated or has already been removed
}
if (!DataGridViewHandler.IsColumnPopulated(dataGridView, columnIndex))
{
return;
}
metadata.PersonalRegionList.Clear();
for (int rowIndex = 0; rowIndex < DataGridViewHandler.GetRowCount(dataGridView); rowIndex++)
{
SwitchStates switchStates = DataGridViewHandler.GetCellStatusSwichStatus(dataGridView, columnIndex, rowIndex);
if (switchStates == SwitchStates.On)
{
RegionStructure regionStructure = DataGridViewHandler.GetCellRegionStructure(dataGridView, columnIndex, rowIndex);
if (regionStructure != null)
{
metadata.PersonalRegionListAddIfNotAreaAndNameExists(regionStructure);
}
}
}
}
private void cloneToolStripMenuItem_Click(object sender, EventArgs e)
{
if (locked || !Loaded)
{
return;
}
if (Read == null)
{
return;
}
if (RegionDataGrid.CurrentCell == null)
{
return;
}
try
{
int index = RegionDataGrid.CurrentCell.RowIndex;
RegionStructure selected = Read.regions[index].clone();
Read.regions.Add(selected);
RegionDataGrid.RowCount = Read.regions.Count;
RegionDataGrid.Refresh();
RegionDataGrid.ClearSelection();//If you want
int nRowIndex = RegionDataGrid.Rows.Count - 1;
int nColumnIndex = 0;
RegionDataGrid.Rows[nRowIndex].Selected = true;
RegionDataGrid.Rows[nRowIndex].Cells[nColumnIndex].Selected = true;
}
catch { }
}
/// <summary>
/// Copy region from inside a image and return the thumbnail bitmap
/// </summary>
/// <param name="image">The image to copy region area from</param>
/// <param name="region">Size of the recion to copy</param>
/// <returns>Thumbnail bitma create from the region given</returns>
public static Bitmap CopyRegionFromImage(Image image, RegionStructure region)
{
Bitmap regionThumbnail;
Rectangle rectangleInImage = region.GetImageRegionPixelRectangle(image.Size);
Size thumbnailSize = GetSizedImageBounds(rectangleInImage.Size, FaceThumbnailSize, false);
regionThumbnail = new Bitmap(thumbnailSize.Width, thumbnailSize.Height);
using (Graphics grD = Graphics.FromImage(regionThumbnail))
{
grD.DrawImage(image, new Rectangle(1, 1, regionThumbnail.Width - 3, regionThumbnail.Height - 3), rectangleInImage, GraphicsUnit.Pixel);
grD.DrawRectangle(new Pen(Color.Black), 0, 0, regionThumbnail.Width - 1, regionThumbnail.Height - 1);
}
return(regionThumbnail);
}
private static int AddRowRegion(DataGridView dataGridView, MetadataBrokerType metadataBrokerType, Metadata metadata, int columnIndex, DataGridViewGenericRow dataGridViewGenericRow, RegionStructure regionStructureToAdd, DataGridViewGenericCellStatus dataGridViewGenericCellStatusDefaults)
{
#region Find Row to Edit or Where to add
bool rowFound = false;
int lastHeaderRowFound = -1;
bool rowBlankFound = false;
int firstBlankFound = -1;
int rowIndexRowFound = -1;
int startSearchRow = 0;
for (int rowIndex = startSearchRow; rowIndex < DataGridViewHandler.GetRowCountWithoutEditRow(dataGridView); rowIndex++)
{
DataGridViewGenericRow dataGridViewGenericRowCheck = DataGridViewHandler.GetRowDataGridViewGenericRow(dataGridView, rowIndex);
if (!dataGridViewGenericRowCheck.IsHeader &&
!dataGridViewGenericRow.IsHeader && //Is row
dataGridViewGenericRowCheck.HeaderName == dataGridViewGenericRow.HeaderName &&
dataGridViewGenericRowCheck.RowName == dataGridViewGenericRow.RowName)
{
//Check if region match
RegionStructure regionStructureInCell = DataGridViewHandler.GetCellValue(dataGridView, columnIndex, rowIndex) as RegionStructure;
if (regionStructureToAdd != null && regionStructureInCell != null)
{
if (metadata != null && metadata.MediaHeight != null && metadata.MediaWidth != null)
{
Size imageSize = new Size((int)metadata.MediaWidth, (int)metadata.MediaHeight);
Rectangle mediaRectangleToAdd = regionStructureToAdd.GetImageRegionPixelRectangle(imageSize);
Rectangle mediaRectangleCell = regionStructureInCell.GetImageRegionPixelRectangle(imageSize);
if (RegionStructure.RectangleEqual(mediaRectangleToAdd, mediaRectangleCell))
{
rowFound = true;
rowIndexRowFound = rowIndex;
break; // return rowIndex;
}
}
}
if (regionStructureInCell == null)
{
rowBlankFound = true;
if (firstBlankFound == -1)
{
firstBlankFound = rowIndex;
}
}
}
#region Sorting
if (dataGridViewGenericRow.IsHeader && //A normal row is add (not header)
//dataGridViewGenericRowCheck.IsHeader && //If header, then check if same header name
dataGridViewGenericRow.HeaderName.CompareTo(dataGridViewGenericRowCheck.HeaderName) >= 0)
{
lastHeaderRowFound = rowIndex; //Remember head row found
}
//Add sorted
if (!dataGridViewGenericRow.IsHeader && //A normal row is add (not header)
dataGridViewGenericRowCheck.IsHeader && //If header, then check if same header name
dataGridViewGenericRowCheck.HeaderName == dataGridViewGenericRow.HeaderName)
{
lastHeaderRowFound = rowIndex; //Remember head row found
}
if (!dataGridViewGenericRow.IsHeader && //A normal row is add (not header)
!dataGridViewGenericRowCheck.IsHeader && //If header, then check if same header name
dataGridViewGenericRowCheck.HeaderName == dataGridViewGenericRow.HeaderName &&
dataGridViewGenericRow.RowName.CompareTo(dataGridViewGenericRowCheck.RowName) >= 0)
{
lastHeaderRowFound = rowIndex; //If lower or eaual, remeber last
}
#endregion
}
#endregion
#region Update row or Add
int rowIndexUsed;
if (rowFound) //Found row and cell with correct region
{
rowIndexUsed = rowIndexRowFound;
RegionStructure regionStructureInCell = DataGridViewHandler.GetCellRegionStructure(dataGridView, columnIndex, rowIndexUsed);
if (regionStructureInCell == null || regionStructureInCell?.Thumbnail == null || (metadata.Broker == MetadataBrokerType.ExifTool && regionStructureToAdd.Thumbnail != null))
{
DataGridViewHandler.SetCellValue(dataGridView, columnIndex, rowIndexUsed, regionStructureToAdd, false); //Prioritize ExifTool
}
}
else if (rowBlankFound) //Found row and but no cell with correct region
{
rowIndexUsed = firstBlankFound;
RegionStructure regionStructureInCell = DataGridViewHandler.GetCellRegionStructure(dataGridView, columnIndex, rowIndexUsed);
if (regionStructureInCell == null || regionStructureInCell?.Thumbnail == null || (metadata.Broker == MetadataBrokerType.ExifTool && regionStructureToAdd.Thumbnail != null))
{
DataGridViewHandler.SetCellValue(dataGridView, columnIndex, rowIndexUsed, regionStructureToAdd, false); //Prioritize ExifTool
}
}
else //No postion found, add on sorted location
{
//lastHeaderRowFound
rowIndexUsed = DataGridViewHandler.AddRow(dataGridView, columnIndex, dataGridViewGenericRow,
DataGridViewHandler.GetFavoriteList(dataGridView), regionStructureToAdd, dataGridViewGenericCellStatusDefaults, lastHeaderRowFound, true, true, true);
}
#endregion
SetCellDefault(dataGridView, metadataBrokerType, columnIndex, rowIndexUsed); //No DirtyFlagSet
DataGridViewHandler.SetCellRowHeight(dataGridView, rowIndexUsed, DataGridViewHandler.GetCellRowHeight(dataGridView));
#region Delete from suggestion
DataGridViewHandler.DeleteRow(dataGridView, headerPeopleSuggestion, regionStructureToAdd.Name);
DataGridViewHandler.DeleteRow(dataGridView, headerPeopleMostUsed, regionStructureToAdd.Name);
#endregion
return(rowIndexUsed);
}
public bool VerifyMetadata(Metadata metadata)
{
if (metadata == null)
{
return(true);
}
int indexAlbum = IndexOfFilter(Albums);
bool foundAlbum;
bool hasValueAlbum = (indexAlbum == -1 ? false : filters[indexAlbum].FilterValues.Count > 0);
if (indexAlbum == -1 || filters[indexAlbum].FilterValues.Count == 0)
{
foundAlbum = IsAndBewteenFieldTags;
}
else
{
foundAlbum = filters[indexAlbum].FilterValues.Contains(metadata.PersonalAlbum);
}
int indexTitle = IndexOfFilter(Titles);
bool foundTitle;
bool hasValueTitle = (indexTitle == -1 ? false : filters[indexTitle].FilterValues.Count > 0);
if (indexTitle == -1 || filters[indexTitle].FilterValues.Count == 0)
{
foundTitle = IsAndBewteenFieldTags;
}
else
{
foundTitle = filters[indexTitle].FilterValues.Contains(metadata.PersonalTitle);
}
int indexComment = IndexOfFilter(Comments);
bool foundComment;
bool hasValueComment = (indexComment == -1 ? false : filters[indexComment].FilterValues.Count > 0);
if (indexComment == -1 || filters[indexComment].FilterValues.Count == 0)
{
foundComment = IsAndBewteenFieldTags;
}
else
{
foundComment = filters[indexComment].FilterValues.Contains(metadata.PersonalComments);
}
int indexDescription = IndexOfFilter(Descriptions);
bool foundDescriptions;
bool hasValueDescriptions = (indexDescription == -1 ? false : filters[indexDescription].FilterValues.Count > 0);
if (indexDescription == -1 || filters[indexDescription].FilterValues.Count == 0)
{
foundDescriptions = IsAndBewteenFieldTags;
}
else
{
foundDescriptions = filters[indexDescription].FilterValues.Contains(metadata.PersonalDescription);
}
int indexAuthor = IndexOfFilter(Authors);
bool foundAuthor;
bool hasValueAuthor = (indexAuthor == -1 ? false : filters[indexAuthor].FilterValues.Count > 0);
if (indexAuthor == -1 || filters[indexAuthor].FilterValues.Count == 0)
{
foundAuthor = IsAndBewteenFieldTags;
}
else
{
foundAuthor = filters[indexAuthor].FilterValues.Contains(metadata.PersonalAuthor);
}
int indexRating = IndexOfFilter(Ratings);
bool foundRating;
bool hasValueRating = (indexRating == -1 ? false : filters[indexRating].FilterValues.Count > 0);
if (indexRating == -1 || filters[indexRating].FilterValues.Count == 0)
{
foundRating = IsAndBewteenFieldTags;
}
else
{
foundRating = filters[indexRating].FilterValues.Contains(metadata.PersonalRating == null ? null : metadata.PersonalRating.ToString());
}
int indexDate = IndexOfFilter(Dates);
bool foundDate;
bool hasValueDate = (indexDate == -1 ? false : filters[indexDate].FilterValues.Count > 0);
if (indexDate == -1 || filters[indexDate].FilterValues.Count == 0)
{
foundDate = IsAndBewteenFieldTags;
}
else
{
foundDate = filters[indexDate].FilterValues.Contains(metadata.MediaDateTaken == null ? null : ((DateTime)metadata.MediaDateTaken).ToString("yyyy-MM"));
}
int indexLocation = IndexOfFilter(Locations);
bool foundLocation;
bool hasValueLocation = (indexLocation == -1 ? false : filters[indexLocation].FilterValues.Count > 0);
if (indexLocation == -1 || filters[indexLocation].FilterValues.Count == 0)
{
foundLocation = IsAndBewteenFieldTags;
}
else
{
foundLocation = filters[indexLocation].FilterValues.Contains(metadata.LocationName);
}
int indexCity = IndexOfFilter(Cities);
bool foundCity;
bool hasValueCity = (indexCity == -1 ? false : filters[indexCity].FilterValues.Count > 0);
if (indexCity == -1 || filters[indexCity].FilterValues.Count == 0)
{
foundCity = IsAndBewteenFieldTags;
}
else
{
foundCity = filters[indexCity].FilterValues.Contains(metadata.LocationCity);
}
int indexState = IndexOfFilter(States);
bool foundState;
bool hasValueState = (indexState == -1 ? false : filters[indexState].FilterValues.Count > 0);
if (indexState == -1 || filters[indexState].FilterValues.Count == 0)
{
foundState = IsAndBewteenFieldTags;
}
else
{
foundState = filters[indexState].FilterValues.Contains(metadata.LocationState);
}
int indexCountry = IndexOfFilter(Countries);
bool foundCountry;
bool hasValueCountry = (indexCountry == -1 ? false : filters[indexCountry].FilterValues.Count > 0);
if (indexCountry == -1 || filters[indexCountry].FilterValues.Count == 0)
{
foundCountry = IsAndBewteenFieldTags;
}
else
{
foundCountry = filters[indexCountry].FilterValues.Contains(metadata.LocationCountry);
}
//
int indexPeople = IndexOfFilter(Peoples);
bool foundPeople;
bool hasValuePeople = (indexPeople == -1 ? false : filters[indexPeople].FilterValues.Count > 0);
if (indexPeople > -1 && filters[indexPeople].FilterValues.Count > 0)
{
bool foundPeopleSome = false;
bool foundPeopleAll = true;
foreach (string name in filters[indexPeople].FilterValues)
{
if (RegionStructure.DoesThisNameExistInList(metadata.PersonalRegionList, name))
{
foundPeopleSome = true;
}
else
{
foundPeopleAll = false;
}
}
if (filters[indexPeople].IsAndBetweenValues)
{
foundPeople = foundPeopleAll; //And / Intersection
}
else
{
foundPeople = foundPeopleSome; //Or / Union
}
}
else
{
foundPeople = IsAndBewteenFieldTags;
}
int indexKeyword = IndexOfFilter(Keywords);
bool foundKeyword;
bool hasValueKeyword = (indexKeyword == -1 ? false : filters[indexKeyword].FilterValues.Count > 0);
if (indexKeyword > -1 && filters[indexKeyword].FilterValues.Count > 0)
{
bool foundKeywordSome = false;
bool foundKeywordAll = true;
foreach (string name in filters[indexKeyword].FilterValues)
{
if (metadata.PersonalKeywordTags.Contains(new KeywordTag(name)))
{
foundKeywordSome = true;
}
else
{
foundKeywordAll = false;
}
}
if (filters[indexKeyword].IsAndBetweenValues)
{
foundKeyword = foundKeywordAll; //And / Intersection
}
else
{
foundKeyword = foundKeywordSome; //Or / Union
}
}
else
{
foundKeyword = IsAndBewteenFieldTags;
}
if (!(hasValueAlbum | hasValueComment | hasValueKeyword | hasValuePeople | hasValueTitle | hasValueDescriptions | hasValueAuthor | hasValueRating | hasValueDate |
hasValueLocation | hasValueCity | hasValueState | hasValueCountry))
{
return(true);
}
if (IsAndBewteenFieldTags)
{
return
(foundAlbum & foundTitle & foundComment & foundPeople & foundKeyword & foundDescriptions & foundAuthor & foundRating & foundDate & foundLocation & foundCity & foundState & foundCountry);
}
else
{
return
(foundAlbum | foundTitle | foundComment | foundPeople | foundKeyword | foundDescriptions | foundAuthor | foundRating | foundDate | foundLocation | foundCity | foundState | foundCountry);
}
}
private Metadata ReadMetadata(MetadataBrokerType broker, string filename, string windowsLivePhotoGalleryDirectory, string directory, int serialNumber)
{
Metadata metadata = null;
var errors = new StringBuilder();
var messages = new StringBuilder();
string sql = "SELECT tblVolume.Label, tblPath.Path, tblObject.Filename, tblObject.FileSize, " +
"tblLocation.LocationName, tblLocation.LocationLat, tblLocation.LocationLong, tblObject.Latitude, tblObject.Longitude, " +
"tblObject.DateModified, tblObject.Title, tblObject.\"Desc\" as Desciption, tblObject.DateTaken, tblObject.Rating, tblObject.Author, tblObject.LabelCount, " +
"tblObject.ResolutionX, tblObject.ResolutionY, tblObject.CameraMake, tblObject.CameraModel, " +
"tblObject.CameraShutterSpeed, tblObject.CameraFocalLength, tblObject.CameraAperture, tblObject.CameraISO, " +
"tblObject.VideoBitrate, tblObject.VideoFramerate, tblObject.MediaDuration " +
"FROM tblObject " +
"INNER JOIN tblPath ON tblObject.FilePathId = tblPath.PathId " +
"INNER JOIN tblVolume ON tblPath.VolumeId = tblVolume.VolumeId " +
"LEFT OUTER JOIN tblocationUsage ON tblocationUsage.ObjectID = tblObject.ObjectID " +
"LEFT OUTER JOIN tblLocation ON tblocationUsage.LocationId = tblLocation.LocationID " +
"WHERE " +
"tblObject.Filename = '" + filename + "' " +
"AND tblPath.Path = '" + windowsLivePhotoGalleryDirectory + "' " +
"AND tblVolume.SerialNo = " + serialNumber.ToString(CultureInfo.InvariantCulture);
int resultCount;
var result = database.ExecuteQuery(sql, errors, messages, out resultCount) as DataSet;
if (result == null)
{
return(metadata); //No point to contine, when header of information missing.
}
foreach (DataTable table in result.Tables)
{
if (table.Rows.Count > 0)
{
metadata = new Metadata(broker);
//File
metadata.FileName = table.Rows[0].Field <String>("Filename");
//metadata.FileDirectory = table.Rows[0].Field<String>("Path");
metadata.FileDirectory = directory; //Override path from database, it's not a complete folder path, missing root path
metadata.FileSize = table.Rows[0].Field <Int64>("FileSize");
DateTime newDateTime = new DateTime(new DateTime(1600, 12, 31, 23, 59, 59, 0, DateTimeKind.Utc).AddSeconds(1).Ticks + table.Rows[0].Field <long>("DateModified"));
metadata.FileDateModified = newDateTime.ToLocalTime();
string fullFilePath = Path.Combine(metadata.FileDirectory, metadata.FileName);
if (File.Exists(Path.Combine(metadata.FileDirectory, metadata.FileName)))
{
metadata.FileDateModified = File.GetLastWriteTime(fullFilePath);
metadata.FileDateCreated = File.GetCreationTime(fullFilePath);
metadata.FileDateAccessed = File.GetLastAccessTime(fullFilePath);
}
//Personal
metadata.PersonalTitle = table.Rows[0].Field <String>("Title");
metadata.PersonalDescription = table.Rows[0].Field <String>("Desciption");
metadata.PersonalRating = table.Rows[0].Field <Byte?>("Rating");
metadata.PersonalAuthor = table.Rows[0].Field <String>("Author");
//Media
metadata.MediaWidth = table.Rows[0].Field <Int32?>("ResolutionX");
metadata.MediaHeight = table.Rows[0].Field <Int32?>("ResolutionY");
metadata.MediaDateTaken = table.Rows[0].Field <DateTime?>("DateTaken");
//Camera
metadata.CameraMake = table.Rows[0].Field <String>("CameraMake");
metadata.CameraModel = table.Rows[0].Field <String>("CameraModel");
//Location
metadata.LocationName = table.Rows[0].Field <String>("LocationName");
metadata.LocationLatitude = (float?)table.Rows[0].Field <double?>("LocationLat"); //Fixed address location, tagged location in map
metadata.LocationLongitude = (float?)table.Rows[0].Field <double?>("LocationLong"); //Fixed address location, tagged location in map
//If -999, GPS location missiong, use tagged location
if (table.Rows[0].Field <double?>("Latitude") != -999)
{
metadata.LocationLatitude = (float?)table.Rows[0].Field <double?>("Latitude"); //Real GPS location
}
if (table.Rows[0].Field <double?>("Longitude") != -999)
{
metadata.LocationLongitude = (float?)table.Rows[0].Field <double?>("Longitude"); //Real GPS location
}
}
}
if (metadata == null)
{
return(metadata);
}
#region Region Name and Size
sql = "SELECT tblVolume.Label, tblPath.Path, tblObject.Filename, tblPerson.Name, tblRegion.\"Top\", tblRegion.\"Left\"," +
"tblRegion.Width, tblRegion.Height " +
"FROM tblObject " +
"INNER JOIN tblPath ON tblObject.FilePathId = tblPath.PathId " +
"INNER JOIN tblVolume ON tblPath.VolumeId = tblVolume.VolumeId " +
"INNER JOIN tblRegion ON tblRegion.ObjectID = tblObject.ObjectId " +
"INNER JOIN tblPerson ON tblRegion.PersonID = tblPerson.PersonId " +
"WHERE " +
"tblObject.Filename = '" + filename + "' " +
"AND tblPath.Path = '" + windowsLivePhotoGalleryDirectory + "' " +
"AND tblVolume.SerialNo = " + serialNumber.ToString();
result = database.ExecuteQuery(sql, errors, messages, out resultCount) as DataSet;
if (result != null)
{
foreach (DataTable table in result.Tables)
{
if (table.Rows.Count > 0)
{
foreach (DataRow row in table.Rows)
{
RegionStructure region = new RegionStructure();
region.Name = (String)row["Name"];
region.AreaX = (float)Math.Round((double)row["Left"], 5);
region.AreaY = (float)Math.Round((double)row["Top"], 5);
region.AreaWidth = (float)Math.Round((double)row["Width"], 5);
region.AreaHeight = (float)Math.Round((double)row["Height"], 5);
region.Type = "Face";
region.RegionStructureType = RegionStructureTypes.WindowsLivePhotoGalleryDatabase;
metadata.PersonalRegionListAddIfNotExists(region);
}
}
}
}
#endregion
#region Keywrods tags
sql = "SELECT tblVolume.Label, tblPath.Path, tblObject.Filename, LabelName " +
"FROM tblObject " +
"INNER JOIN tblPath ON tblObject.FilePathId = tblPath.PathId " +
"INNER JOIN tblVolume ON tblPath.VolumeId = tblVolume.VolumeId " +
"INNER JOIN tblLabelUsage ON tblLabelUsage.ObjectID = tblObject.ObjectId " +
"INNER JOIN tblLabel ON tblLabel.LabelID = tblLabelUsage.LabelId " +
"WHERE " +
"tblObject.Filename = '" + filename + "' " +
"AND tblPath.Path = '" + windowsLivePhotoGalleryDirectory + "' " +
"AND tblVolume.SerialNo = " + serialNumber.ToString();
result = database.ExecuteQuery(sql, errors, messages, out resultCount) as DataSet;
if (result != null)
{
foreach (DataTable table in result.Tables)
{
if (table.Rows.Count > 0)
{
foreach (DataRow row in table.Rows)
{
metadata.PersonalKeywordTagsAddIfNotExists(new KeywordTag((String)row["LabelName"]));
}
}
}
}
#endregion
return(metadata);
}
public Metadata Read(MetadataBrokerType broker, string fullFilePath)
{
Metadata metadata = null;
string fileDirectory = Path.GetDirectoryName(fullFilePath);
string fileName = Path.GetFileName(fullFilePath);
#region SELECT
String query = "SELECT(WITH RECURSIVE " +
"under_alice(folderid, folderlevel, foldername) AS( " +
"VALUES(Item_ParentFolderId, 0, NULL) " +
"UNION ALL " +
"SELECT Folder_ParentFolderId, under_alice.folderlevel + 1 AS folderlevel, Folder_DisplayName " +
"FROM Folder JOIN under_alice ON Folder.Folder_Id = under_alice.folderid " +
"), path_from_root AS( " +
"SELECT folderlevel, foldername, folderid " +
"FROM under_alice " +
"ORDER BY folderlevel DESC " +
") " +
"SELECT group_concat(foldername, '/') " +
"FROM path_from_root " +
"ORDER BY folderlevel DESC) " +
"AS ItemPath, " +
"Item_Filename, Item_ParentFolderId, " +
"Item_Filename, Item_FileSize, " +
"Location.Location_Name, LocationCountry.LocationCountry_Name, " +
"LocationDistrict.LocationDistrict_Name, LocationRegion.LocationRegion_Name, " +
"Item_DateTaken, Item_DateCreated, Item_DateModified, Item_Caption, Album.Album_Name, Item_SimpleRating, " +
"Item_Width, Item_Height, " +
"CameraManufacturer.CameraManufacturer_Text, CameraModel.CameraModel_Text, " +
"Item_Latitude, Item_Longitude FROM Item " +
"INNER JOIN Folder ON Folder.Folder_Id = Item.Item_ParentFolderId " +
"LEFT OUTER JOIN Location ON Item.Item_LocationId = Location.Location_Id " +
"LEFT OUTER JOIN LocationCountry ON Location.Location_LocationCountryId = LocationCountry.LocationCountry_Id " +
"LEFT OUTER JOIN LocationDistrict ON Location.Location_LocationDistrictId = LocationDistrict.LocationDistrict_Id " +
"LEFT OUTER JOIN LocationRegion ON Location.Location_LocationRegionId = LocationRegion.LocationRegion_Id " +
"LEFT OUTER JOIN CameraManufacturer ON Item.Item_CameraManufacturerId = CameraManufacturer.CameraManufacturer_Id " +
"LEFT OUTER JOIN CameraModel ON Item.Item_CameraModelId = CameraModel.CameraModel_Id " +
"LEFT OUTER JOIN AlbumItemLink ON AlbumItemLink.AlbumItemLink_ItemId = Item.Item_Id " +
"LEFT OUTER JOIN Album ON Album.Album_Id = AlbumItemLink.AlbumItemLink_AlbumId " +
"WHERE Item_Filename LIKE @FileName";
//"WHERE Item_Filename = '" + fullFilePath + "'";
//"AND ItemPath = '" + path + "%'";
using (CommonSqliteCommand commandDatabase = new CommonSqliteCommand(query, dbToolsMicrosoftReader.ConnectionDatabase))
{
commandDatabase.Parameters.AddWithValue("@FileName", fileName);
using (CommonSqliteDataReader reader = commandDatabase.ExecuteReader())
{
while (reader.Read())
{
String itemPath = dbToolsMicrosoftReader.ConvertFromDBValString(reader["ItemPath"]);
itemPath = itemPath.Replace("/", "\\");
if (fileDirectory.EndsWith(itemPath, StringComparison.InvariantCulture) == true)
{
//File
metadata = new Metadata(broker);
metadata.FileName = dbToolsMicrosoftReader.ConvertFromDBValString(reader["Item_Filename"]);
//metadata.FileDirectory = dbTools.ConvertFromDBValString(reader["ItemPath"]);
metadata.FileDirectory = fileDirectory; //Override path from database, it's not a complete folder path, missing root path
metadata.FileSize = dbToolsMicrosoftReader.ConvertFromDBValLong(reader["Item_FileSize"]);
metadata.FileDateCreated = dbToolsMicrosoftReader.ConvertSecoundsSince1600ToDateTime(reader["Item_DateCreated"], DateTimeKind.Utc);
metadata.FileDateModified = dbToolsMicrosoftReader.ConvertSecoundsSince1600ToDateTime(reader["Item_DateModified"], DateTimeKind.Utc);
if (metadata.FileDateCreated == null ||
metadata.FileDateModified == null ||
metadata.FileDateAccessed == null ||
File.Exists(fullFilePath))
{
try
{
//Due to sometimes NULL in Microsoft Database, I always use current file attributes.
FileInfo fileInfo = new FileInfo(fullFilePath);
metadata.FileDateCreated = fileInfo.CreationTime; //File.GetCreationTime(fullFilePath);
metadata.FileDateModified = fileInfo.LastWriteTime; //File.GetLastWriteTime(fullFilePath);
metadata.FileDateAccessed = fileInfo.LastAccessTime; //File.GetLastAccessTime(fullFilePath);
} catch (Exception ex)
{
Logger.Error(ex);
metadata.FileDateCreated = DateTime.Now;
metadata.FileDateModified = metadata.FileDateCreated; //File.GetLastWriteTime(fullFilePath);
metadata.FileDateAccessed = metadata.FileDateCreated; //File.GetLastAccessTime(fullFilePath);
}
}
//Personal
metadata.PersonalTitle = dbToolsMicrosoftReader.ConvertFromDBValString(reader["Item_Caption"]);
//metaData.PersonalDescription = dbTools.ConvertFromDBValString(reader["Item_Caption"]);
metadata.PersonalRating = dbToolsMicrosoftReader.ConvertFromDBValByte(reader["Item_SimpleRating"]);
//metaData.PersonalAuthor = dbTools.ConvertFromDBValString(reader["Unknown"]);
metadata.PersonalAlbum = dbToolsMicrosoftReader.ConvertFromDBValString(reader["Album_Name"]);
//Media
metadata.MediaWidth = dbToolsMicrosoftReader.ConvertFromDBValInt(reader["Item_Width"]);
metadata.MediaHeight = dbToolsMicrosoftReader.ConvertFromDBValInt(reader["Item_Height"]);
metadata.MediaDateTaken = dbToolsMicrosoftReader.ConvertSecoundsSince1600ToDateTime(reader["Item_DateTaken"], DateTimeKind.Local);
//Camera
metadata.CameraMake = dbToolsMicrosoftReader.ConvertFromDBValString(reader["CameraManufacturer_Text"]);
metadata.CameraModel = dbToolsMicrosoftReader.ConvertFromDBValString(reader["CameraModel_Text"]);
//Location
metadata.LocationName = dbToolsMicrosoftReader.ConvertFromDBValString(reader["Location_Name"]);
metadata.LocationCountry = dbToolsMicrosoftReader.ConvertFromDBValString(reader["LocationCountry_Name"]);
metadata.LocationCity = dbToolsMicrosoftReader.ConvertFromDBValString(reader["LocationDistrict_Name"]);
metadata.LocationState = dbToolsMicrosoftReader.ConvertFromDBValString(reader["LocationRegion_Name"]);
metadata.LocationLatitude = dbToolsMicrosoftReader.ConvertFromDBValFloat(reader["Item_Latitude"]);
metadata.LocationLongitude = dbToolsMicrosoftReader.ConvertFromDBValFloat(reader["Item_Longitude"]);
if (metadata.LocationLatitude == 0 && metadata.LocationLongitude == 0) //Due to bug in Microsoft Photos Gallery
{
metadata.LocationLatitude = null;
metadata.LocationLongitude = null;
}
break;
}
}
}
}
#endregion
if (metadata == null)
{
return(null);
}
#region SELECT
query = "SELECT(WITH RECURSIVE " +
"under_alice(folderid, folderlevel, foldername) AS( " +
"VALUES(Item_ParentFolderId, 0, NULL) " +
"UNION ALL " +
"SELECT Folder_ParentFolderId, under_alice.folderlevel + 1 AS folderlevel, Folder_DisplayName " +
"FROM Folder JOIN under_alice ON Folder.Folder_Id = under_alice.folderid " +
"), path_from_root AS( " +
"SELECT folderlevel, foldername, folderid " +
"FROM under_alice " +
"ORDER BY folderlevel DESC " +
") " +
"SELECT group_concat(foldername, '/') " +
"FROM path_from_root " +
"ORDER BY folderlevel DESC) " +
"AS ItemPath, Item_Filename, Item_FileSize, " +
"Person_Name, Face_Rect_Left, Face_Rect_Top, Face_Rect_Width, Face_Rect_Height " +
"FROM Item " +
"INNER JOIN Folder ON Folder.Folder_Id = Item.Item_ParentFolderId " +
"INNER JOIN Face ON Face.Face_ItemId = Item.Item_Id " +
"INNER JOIN Person ON Person.Person_Id = Face_PersonId " +
"WHERE " +
"Item.Item_Filename LIKE @FileName";
using (CommonSqliteCommand commandDatabase = new CommonSqliteCommand(query, dbToolsMicrosoftReader.ConnectionDatabase))
{
commandDatabase.Parameters.AddWithValue("@FileName", fileName);
using (CommonSqliteDataReader reader = commandDatabase.ExecuteReader())
{
while (reader.Read())
{
String itemPath = dbToolsMicrosoftReader.ConvertFromDBValString(reader["ItemPath"]);
itemPath = itemPath.Replace("/", "\\");
if (fileDirectory.EndsWith(itemPath, StringComparison.InvariantCulture) == true)
{
RegionStructure region = new RegionStructure();
region.Name = dbToolsMicrosoftReader.ConvertFromDBValString(reader["Person_Name"]);
region.AreaX = (float)dbToolsMicrosoftReader.ConvertFromDBValFloat(reader["Face_Rect_Left"]);
region.AreaY = (float)dbToolsMicrosoftReader.ConvertFromDBValFloat(reader["Face_Rect_Top"]);
region.AreaWidth = (float)dbToolsMicrosoftReader.ConvertFromDBValFloat(reader["Face_Rect_Width"]);
region.AreaHeight = (float)dbToolsMicrosoftReader.ConvertFromDBValFloat(reader["Face_Rect_Height"]);
region.Type = "Face";
region.RegionStructureType = RegionStructureTypes.MicrosoftPhotosDatabase;
metadata.PersonalRegionListAddIfNotExists(region);
}
}
}
}
#endregion
#region SELECT
query = "SELECT (WITH RECURSIVE under_alice(folderid, folderlevel, foldername) AS(VALUES(Item_ParentFolderId, 0, NULL) " +
"UNION ALL SELECT Folder_ParentFolderId, under_alice.folderlevel + 1 AS folderlevel, Folder_DisplayName " +
"FROM Folder JOIN under_alice " +
"ON Folder.Folder_Id = under_alice.folderid), path_from_root AS " +
"(SELECT folderlevel, foldername, folderid FROM under_alice ORDER BY folderlevel DESC) " +
"SELECT group_concat(foldername, '/') FROM path_from_root ORDER BY folderlevel DESC) AS ItemPath, " +
"Item_Filename, Item_FileSize, Item.Item_DateModified, Item.Item_DateCreated " +
", Item.Item_Id " +
", ItemTags1.ItemTags_TagId " +
//Workaround, I have now clue why, when ItemTags_Confidence not selected this way, then indexes wasn't used
", (SELECT ItemTags_Confidence FROM ItemTags AS ItemTags2 Where ItemTags2.ItemTags_TagId = ItemTags1.ItemTags_TagId ) AS ItemTags_Confidence " +
", (SELECT TagVariant_Text FROM TagVariant Where TagVariant.TagVariant_TagResourceId = Tag.Tag_ResourceId ) AS TagVariant_Text " +
"FROM Item " +
"INNER JOIN Folder ON Folder.Folder_Id = Item.Item_ParentFolderId " +
"INNER JOIN ItemTags AS ItemTags1 ON ItemTags1.ItemTags_ItemId = Item.Item_Id " +
"INNER JOIN Tag ON ItemTags1.ItemTags_TagId = Tag.Tag_Id " +
"WHERE Item.Item_Filename LIKE @FileName";
using (CommonSqliteCommand commandDatabase = new CommonSqliteCommand(query, dbToolsMicrosoftReader.ConnectionDatabase))
{
commandDatabase.Parameters.AddWithValue("@FileName", fileName);
using (CommonSqliteDataReader reader = commandDatabase.ExecuteReader())
{
while (reader.Read())
{
String itemPath = dbToolsMicrosoftReader.ConvertFromDBValString(reader["ItemPath"]);
itemPath = itemPath.Replace("/", "\\");
if (fileDirectory.EndsWith(itemPath, StringComparison.InvariantCulture) == true)
{
KeywordTag keywordTag = new KeywordTag(
dbToolsMicrosoftReader.ConvertFromDBValString(reader["TagVariant_Text"]),
(float)dbToolsMicrosoftReader.ConvertFromDBValFloat(reader["ItemTags_Confidence"])
);
metadata.PersonalKeywordTagsAddIfNotExists(keywordTag);
}
}
}
}
#endregion
return(metadata);
}
