{******************************************************************* This Script is designed to enable DJs with no scripting experience to query the database and output the results to text files and to give examples of the data held within the database and the format *** THIS SCRIPT SHOULD ONLY BE RUN WHEN SAM BROADCASTER IS IDLE *** *** THIS WILL BY DESIGN CAUSE SAM BROADCASTER TO STOP IF RUNNING *** *******************************************************************} PAL.LockExecution; // Speed up the Script processing // Declare Constants Const Option1 : string = 'Select 1 to Search by Artist'; Const Option2 : string = 'Select 2 to Search by Track'; Const Option3 : string = 'Select 3 to Search by Album'; Const Option4 : string = 'Select 4 to Search by a single Year'; Const Option5 : string = 'Select 5 to Search by Year Range'; Const Option6 : string = 'Select 6 to Search by Track Type'; // Declare Fixed Variables var Results : TDataSet; var TrackName : string; var TrkCount : integer = 1; var TrkTotal : integer = 0; var StartYear : integer; var EndYear : integer; var ReqYear : integer; var SearchArtist : string = ''; var SearchAlbum : string = ''; var SearchTrack : string = ''; var TempList : TStringList; var Option : integer = 0; var FilePath : string; var CatType : string = ''; // MAIN ------------------------------------------------------------------------------------------- TempList := TStringList.Create; FilePath := GetCurrentDir+'\Output\'; CreateDir(Filepath); WriteLn('Output Directory: '+FilePath); WriteLn(' '); Option := StrToInt(InputBox('Options',Option1+#13#10+Option2+#13#10+Option3+#13#10+Option4+#13#10+Option5+#13#10+Option6+#13#10,'')); // Query based on artist (Option 1) --------------------------------------------------------------- If Option = 1 then begin SearchArtist := InputBox('Artist Search','Enter the artist name to search for...',''); Filepath := Filepath+SearchArtist+'.txt'; WriteLn('Tracks for Artist: '+SearchArtist); WriteLn(' '); if SearchArtist <> '' then begin TempList.Add('Tracks for Artist: '+SearchArtist); TempList.Add(' '); Results := Query('SELECT * FROM songlist WHERE artist = :ArtistName',[SearchArtist],False); while not Results.EOF do begin writeln(FloatToSTr(TrkCount)+'. '+Results['title']); TrackName := Results['artist']+' - '+Results['title']; TrackName := FloatToStr(TrkCount)+'. '+TrackName; TempList.Add(TrackName); TrkCount := TrkCount + 1; TrkTotal := TrkTotal + 1; Results.Next; end; end; WriteLn(' '); WriteLn('Total Tracks found for album '+SearchArtist+': '+FloatToStr(TrkTotal)); WriteLn(' '); WriteLn('- - - - - End of Record - - - - -'); WriteLn(' '); TempList.Add(' '); TempList.Add('Total Number of Tracks Found: '+FloatToStr(TrkTotal)); TempList.Add(' '); TempList.Add('- - - - - End of Record - - - - -'); TempList.Add(' '); TempList.SaveToFile(FilePath); TempList.Clear; end; // Query based on track (Option 2) ---------------------------------------------------------------- If Option = 2 then begin SearchTrack := InputBox('Track Search','Enter the track name to search for...',''); Filepath := Filepath+SearchTrack+'.txt'; WriteLn('Track Record for: '+SearchTrack); WriteLn(' '); TempList.Add('Track Record for: '+SearchTrack); TempList.Add(' '); if SearchTrack <> '' then begin Results := Query('SELECT * FROM songlist WHERE title = :TrackName',[SearchTrack],False); while not Results.EOF do begin // Write Record to PAL IDE Output WriteLn('Track ID: '+FloatToStr(Results['ID'])); WriteLn('Filename: '+Results['filename']); WriteLn('Disk ID: '+FloatToStr(Results['DiskID'])); WriteLn('Flags: '+Results['flags']); WriteLn('Song Type: '+Results['songtype']); WriteLn('Status: '+FloatToStr(Results['status'])); WriteLn('Weight: '+FloatToStr(Results['weight'])); WriteLn('Balance: '+FloatToStr(Results['balance'])); WriteLn('Date Added: '+DateTimeToStr(Results['date_added'])); WriteLn('Date Played: '+DateTimeToStr(Results['date_played'])); WriteLn('Date Artist Played: '+DateTimeToStr(Results['date_artist_played'])); WriteLn('Date Album Played: '+DateTimeToStr(Results['date_album_played'])); WriteLn('Date Title Played: '+DateTimeToStr(Results['date_title_played'])); WriteLn('Duration: '+FloatToStr(Results['Duration'])); WriteLn('Artist: '+Results['artist']); WriteLn('Title: '+Results['title']); WriteLn('Album: '+Results['album']); WriteLn('Label: '+Results['label']); WriteLn('PLine: '+Results['pline']); WriteLn('Track No: '+FloatToStr(Results['trackno'])); WriteLn('Composer: '+Results['composer']); WriteLn('ISRC: '+Results['ISRC']); WriteLn('Catalog: '+Results['Catalog']); WriteLn('UPC: '+Results['UPC']); WriteLn('Fee Agency: '+Results['feeagency']); WriteLn('Album Year: '+Results['albumyear']); WriteLn('Genre: '+Results['genre']); WriteLn('Website: '+Results['website']); WriteLn('Buy CD: '+Results['buycd']); WriteLn('Info: '+Results['info']); WriteLn('Lyics: '+Results['lyrics']); WriteLn('Picture: '+Results['picture']); WriteLn('Count Played: '+FloatToStr(Results['count_played'])); WriteLn('Count Requested: '+FloatToStr(Results['count_requested'])); WriteLn('Last Requested: '+DateTimeToStr(Results['last_requested'])); WriteLn('Count Performances: '+FloatToStr(Results['count_performances'])); WriteLn('Crossfade: '+Results['xfade']); WriteLn('BPM: '+FloatToStr(Results['bpm'])); WriteLn('Mood: '+Results['mood']); WriteLn('Rating: '+FloatToStr(Results['rating'])); WriteLn('Overlay: '+Results['overlay']); WriteLn('Playlimit Count: '+FloatToStr(Results['playlimit_count'])); WriteLn('Playlimit Action: '+Results['playlimit_action']); WriteLn('Songrights: '+Results['songrights']); WriteLn(' '); WriteLn('Total Tracks found for album '+SearchTrack+': '+FloatToStr(TrkTotal)); WriteLn(' '); WriteLn('- - - - - End of Record - - - - -'); WriteLn(' '); // Write Record to Text File TempList.Add('Track ID: '+FloatToStr(Results['ID'])); TempList.Add('Filename: '+Results['filename']); TempList.Add('Disk ID: '+FloatToStr(Results['DiskID'])); TempList.Add('Flags: '+Results['flags']); TempList.Add('Song Type: '+Results['songtype']); TempList.Add('Status: '+FloatToStr(Results['status'])); TempList.Add('Weight: '+FloatToStr(Results['weight'])); TempList.Add('Balance: '+FloatToStr(Results['balance'])); TempList.Add('Date Added: '+DateTimeToStr(Results['date_added'])); TempList.Add('Date Played: '+DateTimeToStr(Results['date_played'])); TempList.Add('Date Artist Played: '+DateTimeToStr(Results['date_artist_played'])); TempList.Add('Date Album Played: '+DateTimeToStr(Results['date_album_played'])); TempList.Add('Date Title Played: '+DateTimeToStr(Results['date_title_played'])); TempList.Add('Duration: '+FloatToStr(Results['Duration'])); TempList.Add('Artist: '+Results['artist']); TempList.Add('Title: '+Results['title']); TempList.Add('Album: '+Results['album']); TempList.Add('Label: '+Results['label']); TempList.Add('PLine: '+Results['pline']); TempList.Add('Track No: '+FloatToStr(Results['trackno'])); TempList.Add('Composer: '+Results['composer']); TempList.Add('ISRC: '+Results['ISRC']); TempList.Add('Catalog: '+Results['Catalog']); TempList.Add('UPC: '+Results['UPC']); TempList.Add('Fee Agency: '+Results['feeagency']); TempList.Add('Album Year: '+Results['albumyear']); TempList.Add('Genre: '+Results['genre']); TempList.Add('Website: '+Results['website']); TempList.Add('Buy CD: '+Results['buycd']); TempList.Add('Info: '+Results['info']); TempList.Add('Lyics: '+Results['lyrics']); TempList.Add('Picture: '+Results['picture']); TempList.Add('Count Played: '+FloatToStr(Results['count_played'])); TempList.Add('Count Requested: '+FloatToStr(Results['count_requested'])); TempList.Add('Last Requested: '+DateTimeToStr(Results['last_requested'])); TempList.Add('Count Performances: '+FloatToStr(Results['count_performances'])); TempList.Add('Crossfade: '+Results['xfade']); TempList.Add('BPM: '+FloatToStr(Results['bpm'])); TempList.Add('Mood: '+Results['mood']); TempList.Add('Rating: '+FloatToStr(Results['rating'])); TempList.Add('Overlay: '+Results['overlay']); TempList.Add('Playlimit Count: '+FloatToStr(Results['playlimit_count'])); TempList.Add('Playlimit Action: '+Results['playlimit_action']); TempList.Add('Songrights: '+Results['songrights']); TempList.Add(' '); TempList.Add('- - - - - End of Record - - - - -'); TempList.SaveToFile(FilePath); TempList.Clear; Results.Next; end; end; end; // Query based on album (Option 3) ---------------------------------------------------------------- If Option = 3 then begin SearchAlbum := InputBox('Album Search','Enter the album name to search for...',''); Filepath := Filepath+SearchAlbum+'.txt'; WriteLn('Tracks for Album: '+SearchAlbum); WriteLn(' '); TempList.Add('Tracks for Album: '+SearchAlbum); TempList.Add(' '); if SearchAlbum <> '' then begin Results := Query('SELECT * FROM songlist WHERE album = :AlbumName',[SearchAlbum],False); while not Results.EOF do begin TrackName := Results['artist']+' - '+Results['title']; TrackName := FloatToStr(TrkCount)+'. '+TrackName; WriteLn(TrackName); TempList.Add(TrackName); TrkCount := TrkCount + 1; TrkTotal := TrkTotal + 1; Results.Next; end; end; WriteLn(' '); WriteLn('Total Tracks found for album '+SearchAlbum+': '+FloatToStr(TrkTotal)); WriteLn(' '); WriteLn('- - - - - End of Record - - - - -'); WriteLn(' '); TempList.Add(' '); TempList.Add('Total Tracks found for album '+SearchAlbum+': '+FloatToStr(TrkTotal)); TempList.Add(' '); TempList.Add('- - - - - End of Record - - - - -'); TempList.SaveToFile(FilePath); TempList.Clear; end; // Query based on single year (Option 4) ---------------------------------------------------------- If Option = 4 then begin StartYear := StrToInt(InputBox('Search Year','Please enter the year to search','')); ReqYear := StartYear; WriteLn('Tracks for Year: '+FloatToStr(ReqYear)); WriteLn(' '); TempList.Add('Tracks for Year: '+FloatToStr(ReqYear)); TempList.Add(' '); Filepath := Filepath+FloatToStr(ReqYear)+'.txt'; Results := Query('SELECT * FROM songlist WHERE albumyear = :Dated',[ReqYear],False); Results.First; while not Results.EOF do begin TrackName := Results['artist']+' - '+Results['title']; TrackName := FloatToStr(TrkCount)+'. '+TrackName; WriteLn(TrackName); TempList.Add(TrackName); TrkCount := TrkCount + 1; TrkTotal := TrkTotal + 1; Results.Next; end; WriteLn(' '); WriteLn('Total Number of Tracks Found for Year '+FloatToStr(ReqYear)+': '+FloatToStr(TrkTotal)); WriteLn(' '); WriteLn('- - - - - End of Record - - - - -'); WriteLn(' '); TempList.Add(' '); TempList.Add('Total Number of Tracks Found: '+FloatToStr(TrkTotal)); TempList.Add(' '); TempList.Add('- - - - - End of Record - - - - -'); TempList.SaveToFile(FilePath); TempList.Clear; end; // Query based on a range of years (Option 5) ----------------------------------------------------- If Option = 5 then begin StartYear := StrToInt(InputBox('Starting Year','Please enter the year to search from','')); EndYear := StrToInt(InputBox('End Year','Please enter the year to search up to','')); ReqYear := StartYear; While (EndYear > StartYear) AND (ReqYear <= EndYear) do begin Filepath := Filepath+intToStr(ReqYear)+'.txt'; WriteLn('Tracks for Year: '+FloatToStr(ReqYear)); WriteLn(' '); TempList.Add('Tracks for Year: '+FloatToStr(ReqYear)); TempList.Add(' '); Results := Query('SELECT * FROM songlist WHERE albumyear = :Dated',[ReqYear],False); Results.First; while not Results.EOF do begin TrackName := Results['artist']+' - '+Results['title']; TrackName := FloatToSTr(TrkCount)+'. '+TrackName; WriteLn(TrackName); TempList.Add(TrackName); TrkCount := TrkCount + 1; TrkTotal := TrkTotal + 1; Results.Next; end; WriteLn(' '); WriteLn('Total Number of Tracks Found for '+FloatToStr(ReqYear)+': '+FloatToStr(TrkCount - 1)); WriteLn(' '); WriteLn('- - - - - End of Record - - - - -'); WriteLn(' '); TempList.Add(' '); TempList.Add('Total Number of Tracks Found for '+FloatToStr(ReqYear)+': '+FloatToStr(TrkCount - 1)); TempList.Add(' '); TempList.Add('- - - - - End of Record - - - - -'); TempList.Add(' '); TempList.SaveToFile(FilePath); TempList.Clear; ReqYear := ReqYear + 1; TrkCount := 1; FilePath := GetCurrentDir+'\Output\'; end; end; // Query to List the tracks in the library based on category type (Option 6) ----------------------------------------------------- If Option = 6 then begin CatType := InputBox('Category Search','Enter the category type to search for...(S, I, P, J, N, V, X, C, ?)',''); Filepath := Filepath+CatType+'.txt'; If CatType='?' Then FilePath := GetCurrentDir+'\Output\QuestionMark'+'.txt'; WriteLn('Tracks for Category: '+CatType); WriteLn(' '); TempList.Add('Tracks for Category: '+CatType); TempList.Add(' '); if CatType <> '' then begin Results := Query('SELECT * FROM songlist WHERE songtype = :CatType',[CatType],False); while not Results.EOF do begin TrackName := FloatToStr(Results['ID'])+' - '+Results['artist']+' - '+Results['title']; TrackName := FloatToStr(TrkCount)+'. '+TrackName; WriteLn(TrackName); TempList.Add(TrackName); TrkCount := TrkCount + 1; TrkTotal := TrkTotal + 1; Results.Next; end; end; WriteLn(' '); WriteLn('Total Tracks found for Category '+CatType+': '+FloatToStr(TrkTotal)); WriteLn(' '); WriteLn('- - - - - End of Record - - - - -'); WriteLn(' '); TempList.Add(' '); TempList.Add('Total Tracks found for Category '+CatType+': '+FloatToStr(TrkTotal)); TempList.Add(' '); TempList.Add('- - - - - End of Record - - - - -'); TempList.SaveToFile(FilePath); TempList.Clear; end; // End of Main ------------------------------------------------------------------------------------ PAL.UnLockExecution; // Return PAL Script processing to normal speed // Housekeeping Results.Free;