(* Script:  Find file pairs for files by exclusion string.
	Locate and display files which match other files *excluding* a specified string in the filename.
	i.e.   locate files matching xxxxx_hdr  where '_hdr' is exclued  (z1_hdr.jpg ->  z1.jpg, z1.rw2, z1.tif).  
	The string to exclude in the matching is provided by the user.
	NB: The extension is not included in the matching. 
	
	By: Will H,  4-jul-21   (some code copied based other scripts)  https://repository.idiimager.com
	
	Version 2.0
	
	Use: 
	
	1. Select thumnails to work with  (wouLD typically be the images which contain the string you want to exclude in the matching).
	2. You can select any number of images, and only those containing the exclusion string would be used for matching (e.g. you could select all images for a month or year, only those including the string would be used)
	3. Invoke script,
	4. provide the string for exclusion (eg. z1_hdr.jpg - provide '_hdr') in the dialogue box.
	5. Optional check boxes:  'Constrain Results to common extension': only results with matching extensions will be returned (i.e. z1_hdr.jpg -> z1.jpg), 'Ignore Case in exclsuion string': disregard the case of the exclusion string when matching.
	
	Results:
	1. Tab containing images which match each source image excluding the 'exclusion string' and with any extension
	2. Tab containing source images which have at least one matched files (without the exclusion string) in the results tab #1
	
	Other notes:
	1. Currently if there is an instance where two separate filenames with same name and the same exclusion string but different extension,
	a note will be displayed advising of possible duplicate return.  Duplicate results are notincluded in the output.  (eg. z1_hdr.png and z1_hdr.jpg could give dupicate results)
	2. The path names are not examined for the exclusion string
	3. Only those files in the same path will be matched.
	
	Possible uses:
	Search for duplicate photos z1(001).jpg,  z2(002).jpg etc.
	locate source files for processed files with tail (e.g.  z1_hdr.jpg)
	locate related files for source image (z1_hdr.jpg -> z1.jpg, z1.raw, z1.tif)  - perhaps when needing to update keywords, or portfolios.
*)

const
	cGUIDFileWOExclStr   = 'wphA5DDD2EA8E5A47E598F270326903C21A';  //data set of files *without* the exclusion string
	cGUIDFileInclExclStr = 'wph7440A20D2E26437A80091F8420FE3B38'; //data set of files with exclusion string which match against cGUIDFileWOExclStr
	cExclStr = '(001)';   // default exclusion string - change this to suit your practice
	rfReplaceAll = 1;  // constanbt for stringreplace function
	rfIgnoreCase = 2;  // costant for stringreplace function

var
	ATextBox: TvxTextBox;  // response from form with extension
	ACheckBoxExtension: TvxCheckBox; // yes to constrain matching on extension
	ACheckBoxIgnoreCase: TvxCheckBox; // Yes to ignore case
	AForm: TvxSceneForm;   // extension form
	ExclStr: WideString;    // Exclusion string
	bExtension: Boolean;    // If 'match extension' is true, only the which have the same extension as the search file are returned
	bIgnoreCase: Boolean;  // ignore case on the search
	rFlags: Byte //options for replace function

function WideQuotedStr(AStr: WideString): WideString;  //returns wide string with single quotes
begin
	result := UTF8Decode(QuotedStr(UTF8Encode(AStr)));
end;

function CreateForm: TvxSceneForm;
var
	ARoot: TvxVisualObject;
	ALabel: TvxLabel;

begin
	result := TvxSceneForm.Create(nil);
	result.Caption := UTF8Encode('Exclusion String For Search');
	result.Position := poScreenCenter;
	result.Height := 200 * result.DPIFactor;
	
	ARoot := result.Root;
	
	ALabel := TvxLabel.Create(ARoot);
	ALabel.Text := 'Enter Exclusion String: (default: '+cExclStr+')';
	ALabel.TextAlign := vgTextAlignNear;
	ALabel.Padding.Left := 10;
	ALabel.Padding.Top := 10;
	ALabel.Align := vaTop;
	ARoot.AddObject(ALabel);
	
	ATextBox := TvxTextBox.Create(ARoot);
	ATextBox.Padding.Left := 10;
	ATextBox.Padding.Right := 10;
	ATextBox.Padding.Top := 10;
	ATextBox.Align := vaTop;
	ARoot.AddObject(ATextBox);
	
	ACheckBoxExtension := TvxCheckBox.Create(ARoot);
    ACheckBoxExtension.Text:= 'Constrain Results to common extension';
	ACheckBoxExtension.IsChecked:= 0;
	ACheckBoxExtension.Padding.Left := 10;
	ACheckBoxExtension.Padding.Right := 10;
	ACheckBoxExtension.Padding.Top := 10;
	ACheckBoxExtension.Align := vaTop;
	ARoot.AddObject(ACheckBoxExtension);
	
	ACheckBoxIgnoreCase := TvxCheckBox.Create(ARoot);
	ACheckBoxIgnoreCase.Text := 'Ignore Case in exclsuion string';
	ACheckBoxIgnoreCase.IsChecked := 1;
	ACheckBoxIgnoreCase.Padding.Left := 10;
	ACheckBoxIgnoreCase.Padding.Right := 10;
	ACheckBoxIgnoreCase.Padding.Top := 10;
	ACheckBoxIgnoreCase.Align := vaTop;
	ARoot.AddObject(ACheckBoxIgnoreCase);	
	
end;

procedure CleanIDTemp(myGUID: String);  // remove entries from idTempList for provided GUIDs
Var
	ADs: TDBXOMClientDataSet;

Begin
	ADs := PublicCatalog.NewDataSet;
	try  // clean up templist - remove my results from the list
		ADs.CommandText := 'delete from idTempList where ClassGUID=''' + myGUID + '''';
		ADs.Run;
	finally
		PublicCatalog.FreeDataSet (ADs);
	end;
end;

procedure FindFileExcl(ExclStr: WideString); 
var
	i: Integer;
	ADs: TDBXOMClientDataSet;
	AItems: TCatalogItems;
	FileExcl: WideString;
	FileExclWildCard: WideString;
	FileExclPath: WideString;
	FileNameSel : WideString;
	FileExclCount: LongInt;
	FileSQL: WideString;
	bHasExclStr: Boolean;

begin
	// scrub idTemplList
	CleanIDTemp(cGUIDFileWOExclStr)
	CleanIDTemp(cGUIDFileInclExclStr)
	
	for i := 0 to Selected.Count - 1 do  // for each selected image
	begin
		if bIgnoreCase then  // check that exclusion string exists in selected image
			bHasExclStr:=WideTextPos(WideUpperCase(ExclStr), WideUpperCase(Selected.Items[i].FileName)) >0
		else
			bHasExclStr:=WidePos(ExclStr, Selected.Items[i].FileName)>0;	
		if bHasExclStr then
		begin
			FileExclPath := WideExtractFilePath(Selected.Items[i].FileName);   // path only
			FileNameSel := WideExtractFileName(Selected.Items[i].FileName);  // filename only
			FileExcl := StringReplace(WideChangeFileExt(FileNameSel,''), ExclStr,'',(rfReplaceAll or iif(bIgnoreCase,rfIgnoreCase,0)));  // filename withpout exclusion string
			if bExtension then //check whether to match against extension also
				FileExclWildCard := FileExcl + WideExtractFileExt(Selected.Items[i].FileName)
			else
				FileExclWildCard :=  WideChangeFileExt(FileExcl,'.%');  // wild card for SQL
			FileSQL := ' select distinct ''' + cGUIDFileWOExclStr + ''', idCatalogItem.GUID ' +
			'  from   idCatalogItem ' +
			'  inner  join idCache_FilePath on idCatalogItem.PathGUID = idCache_FilePath.FilePathGUID ' +
			'  where  (idCatalogItem.FileName Like ' + WideQuotedStr(FileExclWildCard) + ') ' +
			'  and    (idCache_FilePath.FilePath = ' + WideQuotedStr(FileExclPath) + ') ' +
			iif(IsSQLite, 'collate nocase', '') +
			'';
			// check to see if there at least one matching image
			try
				ADs := PublicCatalog.NewDataSet;
				ADs.CommandText := FileSQL;
				ADs.OpenSet;
				try
					FileExclCount := ADs.RecordsInSet;
				finally
					ADs.CloseSet;
				end;
			finally
				PublicCatalog.FreeDataSet(ADs);
			end;
			// if at least one matching image then process
			if FileExclCount then
			begin
				ADs := PublicCatalog.NewDataSet;
				// add matching images to idTempList
				try
					ADs.CommandText := 'insert into idTempList (ClassGUID, GUID) ' + FileSQL               
					try
						ADs.Run;
					except
						say ('Possible duplicate result for: ' + Selected.Items[i].FileName);
					end;
				finally
					PublicCatalog.FreeDataSet(ADs);
				end;
				
				// add source images which have at least one matching image into idTempList
				ADs := PublicCatalog.NewDataSet;
				ADs.CommandText := 'insert into idTempList (ClassGUID, GUID) ' +
				'  select distinct ''' + cGUIDFileInclExclStr + ''', idCatalogItem.GUID ' +
				'  from   idCatalogItem ' +
				'  inner  join idCache_FilePath on idCatalogItem.PathGUID = idCache_FilePath.FilePathGUID ' +
				'  where  (idCatalogItem.FileName = ' + WideQuotedStr(FileNameSel) +') ' +
				'  and    (idCache_FilePath.FilePath = ' + WideQuotedStr(FileExclPath) + ') ' +
				'';
				try
					ADs.Run;
				finally
					PublicCatalog.FreeDataSet(ADs);
				end;
			end;
		end;
	end;
	
	// Display matching images in new tab
	
	ADs := PublicCatalog.NewDataSet;  
	try
		ADs.CommandText := 'select ' + PublicCatalog.FullColumnList('v.') + ' ' +
		'from   v_CatalogItem v, idTempList t ' +
		'where  t.ClassGUID = ''' + cGUIDFileWOExclStr + ''' ' +
		'and    v.GUID = t.GUID ' +
		'union all ' +
		'select ' + PublicCatalog.FullColumnList('v.') + ' ' +
		'from   v_CatalogItemVersion v, idTempList t ' +
		'where  t.ClassGUID = ''' + cGUIDFileWOExclStr + ''' ' +
		'and    v.GUID = t.GUID ' +
		'order by FullFileName ' +
		'';
		//CopyTextToClipboard(ADs.CommandText);
		ADs.OpenSet;
		AItems := TCatalogItems.Create(TCatalogItem, '');  // results
		
		try  // display results in new tab
			AItems.Name := 'Matches excl. ' + WideQuotedStr(ExclStr);
			AItems.AddDataSet (ADs, True);
			PublicBroadCast(nil, 'OpenNewTab', nil);
			PublicBroadcastObject(nil, 'ShowImageResults', AItems, nil, nil);
		finally
			AItems.Free;
		end;
		ADs.CloseSet;
	finally
		PublicCatalog.FreeDataSet (ADs);
end;

CleanIDTemp(cGUIDFileWOExclStr);

// display source images with at least one matching image in a new tab

ADs := PublicCatalog.NewDataSet;  // create the resultant dataset for display
try
	ADs.CommandText := 'select ' + PublicCatalog.FullColumnList('v.') + ' ' +
	'from   v_CatalogItem v, idTempList t ' +
	'where  t.ClassGUID = ''' + cGUIDFileInclExclStr + ''' ' +
	'and    v.GUID = t.GUID ' +
	'union all ' +
	'select ' + PublicCatalog.FullColumnList('v.') + ' ' +
	'from   v_CatalogItemVersion v, idTempList t ' +
	'where  t.ClassGUID = ''' + cGUIDFileInclExclStr + ''' ' +
	'and    v.GUID = t.GUID ' +
	'order by FullFileName ' +
	'';
	ADs.OpenSet;
	AItems := TCatalogItems.Create(TCatalogItem, '');  // results
	try  // display results in new tab
		AItems.Name := 'Source incl. ' + WideQuotedStr(ExclStr);
		AItems.AddDataSet (ADs, True);
		PublicBroadCast(nil, 'OpenNewTab', nil);
		PublicBroadcastObject(nil, 'ShowImageResults', AItems, nil, nil);
	finally
		AItems.Free;
	end;
	ADs.CloseSet;
finally
	PublicCatalog.FreeDataSet (ADs);
end;
CleanIDTemp(cGUIDFileInclExclStr);
Say ('Done. Found ' + ExclStr + ' files are placed in a new Tab');
end;

// ----  main - get exclusion string for matching
begin
	if Selected.Count = 0 then
	begin
		Say('No image selected');
		exit;
	end;
	
	AForm := CreateForm; 
	try
		ATextBox.Text := cExclStr; // default exclusion string
		AForm.PrepareModal([mbOK, mbCancel]);
		AForm.ShowModal;
		if AForm.ModalResult = mrOk then
		begin
			if Length(ATextBox.Text) <> 0 then // double check for blank extension entered
			begin		
				bExtension := ACheckBoxExtension.IsChecked = 1;
				bIgnoreCase := ACheckBoxIgnoreCase.IsChecked = 1;
				ExclStr := ATextBox.Text;
				FindFileExcl(ExclStr); // go look
			end;
		end;
	finally
		AForm.Free;
	end;
end;