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); }