'========================================================================================
'Select modified files script
'by John Beardsworth 22/12/05
'version 1.1
'Loops through media items on screen and writes data to a file
'I use this to generate SQL to update web site
'========================================================================================

Const kMsgBoxTitle = "Output web script"
Const kFilename = "D:\iViewSQL.sql"

    Set ivApp = CreateObject("iView.Application")
   
    If (ivApp.Catalogs.Count = 0) Then
        MsgBox "Please launch Iview MediaPro.", vbCritical, kMsgBoxTitle
    End If

    Const ForReading = 1, ForWriting = 2, ForAppending = 3
    Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
  
    Set fs2 = CreateObject("Scripting.FileSystemObject")
    Set f2 = fs2.createtextFile(kFilename )
            
flush = Msgbox ("Do you want to flush?", vbYesNo	, kMsgBoxTitle )

if flush = vbYes then
 
    sqlText = "DELETE from photos;" & vbCrLf _
        & "DELETE from photos_galleries;" & vbCrLf _
        & "DELETE from photos_keywords;" & vbCrLf
    f2.writeline sqlText
end if
    
    '
    DeleteItems = True
     
    SQLstring = RefreshSQL()
  
    f2.writeline SQLstring
    f2.Close

    Set ivCat = ivApp.ActiveCatalog
    msg = kFilename & " created with " & ivCat.Selection.Count & " record(s)" 
    inputbox msg, kMsgBoxTitle ,    SQLstring






Function RefreshSQL()
    Set ivApp = CreateObject("iView.Application")
    Set ivCat = ivApp.ActiveCatalog
    
    Set portRecords = ivCat.Selection
    portRecordCount = portRecords.Count
    
    i = 1
    For Each ivItem In portRecords
        portFilename = Replace(ivItem.Name, ".jpg", "")
        portFilePath = ivItem.Path
        portDescription = ivItem.Annotations.Caption
        portDocumentTitle = ivItem.Annotations.Product
        portForSale = ivItem.CustomFields("For sale").Value
        portSequence = ivItem.CustomFields("Sequence").Value
        portType = ivItem.CustomFields("Type").Value
        portOriginal = ivItem.CustomFields("Original format").Value
        portRoot = 1
        ivKeywords = ivItem.Annotations.Keywords
        gGallery = trim(ivItem.CustomFields("Gallery").Value)
        gNew = trim(ivItem.CustomFields("Gallery - New").Value)
        gShowcase = trim(ivItem.CustomFields("Gallery - Showcase").Value)

        If DeleteItems = True Then
        RefreshSQL = RefreshSQL & vbCrLf & DeleteRecord(portFilename)
        End If
        RefreshSQL = RefreshSQL & vbCrLf & InsertRecord(portFilename, portDocumentTitle, portDescription, portForSale, portSequence, portType, portOriginal, portRoot, ivKeywords, gGallery, gNew, gShowcase)
        
    Next

    
End Function




Function InsertRecord(webFile, webTitle, webNarrative, webSale, webSeq, webType, webOriginal, webRoot, webKeywords, gGallery, gNew, gShowcase)
    
    InsertPhoto = "INSERT INTO photos VALUES ('" & _
        webFile & "' , '" & _
        Replace(webTitle, "'", "\'") & "' , '" & _
        webSeq & "' , '" & _
        Replace(webNarrative, "'", "\'") & "' , '" & _
        webType & "' , '" & _
        webRoot & "' , '" & _
        webSale & "' , '" & _
        webOriginal & "');" & vbCrLf
        

        insGallery = "INSERT INTO photos_galleries VALUES ('" & webFile & "' , '" & gGallery & "');" & vbCrLf 
	if gNew <> "" then insNew = "INSERT INTO photos_galleries VALUES ('" & webFile & "' , '" & gNew & "');" & vbCrLf 
	if gShowcase <> "" then insShowcase = "INSERT INTO photos_galleries VALUES ('" & webFile & "' , '" & gShowcase & "');" & vbCrLf 

        'insKeywords = "INSERT INTO photos_keywords VALUES ('" & webFile & "' , '" & webKeywords & "');" & vbCrLf
	
	insKeywords =""
	kWords = Split(webKeywords, ";")
	For i = 0 To UBound(kWords)
        insKeywords = insKeywords & "INSERT INTO photos_keywords VALUES ('" & webFile & "' , '" & kWords(i) & "');" & vbCrLf
	Next
              
	InsertRecord = InsertPhoto & insGallery & insNew & insShowcase & insKeywords
        
End Function



Function DeleteRecord(webFile)
    DeleteRecord = "DELETE FROM photos WHERE pho_file = '" & webFile & "'; " & vbCrLf & _
         "DELETE FROM photos_keywords WHERE phk_file = '" & webFile & "'; " & vbCrLf & _
         "DELETE FROM photos_galleries WHERE phg_file = '" & webFile & "'; "
End Function