Check for Possible Duplicate Media.fh_lua

--[[
@Title: Check for Possible Duplicate Media
@Author: Jane Taubman
@Version: 1.3
@LastUpdated: June 2013 
@Description: 
This plugin checks all the media records in your project and compares the files linked to them looking for files 
which are identical it will pick up both records which are linked to the same file and to those where the names 
are different, but the contents are the same. 
When complete it returns a result set containing the matching records.
By selecting each pair in turn, just drag over both cells, you can then select Edit>Merge Compare Records from 
the menu and merge the records together.
Once the process is complete, it is recommended to use the "Check for Unlinked Media" plugin to clean up any duplicate files.

1.1 Add trap for Missing Files and report them on the Result Set.
1.2 Add Caching of File details between runs
1.3 Correct Clear Records Call
 ]]
function mainfunction()
    if fhGetContextInfo('CI_APP_MODE') ~= 'Project Mode' then
        fhMessageBox('This Plugin Requires a Project','MB_OK','MB_ICONEXCLAMATION')
        return
    end
    -- Get Previous Run Data / Set up Database
    local dbenv,dbcon,bCreate = opendb(fhGetPluginDataFileName())
    iCount = countrecords(dbcon)
    if iCount and iCount > 0 then
       local ans =  fhMessageBox('There are '..iCount..' Cached Records..\nDo you wish to use them?','MB_YESNO','MB_ICONQUESTION')
       if ans == 'No' then clearlist(dbcon)	-- was clearrecords() in V1.2
       elseif ans ~= 'Yes' then return end        
    end
    -- Define Columns
    tblOutput,iC = createResultTable()
    tblOutput.record = {title='Record',type='item',width=140,align='align_left',content={}}
    tblOutput.duplicate = {title='Duplicate',type='item',width=140,align='align_left',content={}}
    tblOutput.message = {title='Message',type='Text',width=140,align='align_left',content={}}
    -- Build Duplicate List
    local rootfolder = fhGetContextInfo('CI_PROJECT_DATA_FOLDER')
    local medialist = {}
    local filelist = {}
    local mediaCount = 0
    local pm = fhNewItemPtr()
    -- Quick Count Records
    local icnt = 0
    for pi in records('OBJE') do
        icnt = icnt + 1
    end
    ProgressDisplay.Start('Searching All Media Please Wait',icnt)
    for pi in records('OBJE') do
        local recordid = fhGetRecordId(pi)
        ProgressDisplay.SetMessage(fhGetDisplayText(pi))
        ProgressDisplay.Step(1)
        if ProgressDisplay.Cancel() then
            break
        end
        pm:MoveTo(pi,'~._FILE')
        local mediaFile = fhGetValueAsText(pm)
        if mediaFile:starts('Media\\') then
            mediaFile = rootfolder..'\\'..mediaFile
        end
        local strlc = mediaFile:lower()
        if FileExists(mediaFile) then
            local smd5 = chkrecord(dbcon,recordid)
            if not(sdm5) then 
               smd5 = md5.sumhexa(LoadFromFile(mediaFile))
               updaterecord(dbcon,recordid,smd5)
            end
            local newmedia = {filename = fhGetValueAsText(pm), record = pi:Clone(), md5 = smd5}
            -- Check for Duplicate
            bDup = false
            for i = 1,mediaCount do
                if smd5 == medialist[i].md5 then
                    iC = iC + 1
                    tblOutput.record.content[iC] = medialist[i].record
                    tblOutput.duplicate.content[iC] = newmedia.record
                    bDup = true
                end
            end
            if not(bDup) then
                mediaCount = mediaCount + 1
                medialist[mediaCount] = newmedia
            end
        else
            -- File not found
            iC = iC + 1
            tblOutput.record.content[iC] = pi:Clone()
            tblOutput.message.content[iC] = 'File Missing:'..fhGetValueAsText(pm)
        end
    end
    ProgressDisplay.Reset()
    ProgressDisplay.Close()
    if iC > 0 then
        -- Offer to delete the files
        local strTitle = "Possible Duplicate Media Records"
        fhOutputResultSetTitles(strTitle,strTitle, "Date: %#x")
        for t in tblOutput() do
            fhOutputResultSetColumn(t.title, t.type, t.content, iC, t.width,t.align)
        end
    else
        fhMessageBox('No Duplicates Found','MB_OK','MB_ICONINFORMATION')
    end
end
-------------------------------------- Custom Functions
-------------------------------------- Standard Functions
--[[
@Title: Progress Display (drop in)
@Author: Jane Taubman / Mike Tate
@LastUpdated: May 2012
@Description: Allows easy adding of a Progress Bar to any long running Plugin
]]
local StrWhite = "255 255 255" 
ProgressDisplay = {   
    Start = function(strTitle,intMax) -- Create and start the Progress Display window controls
        if not dlgProgress then
            cancelflag = false
            local cancelbutton = iup.button{ title="Cancel", rastersize="200x30",
                action = function()
                    cancelflag = true -- Signal that Cancel button was pressed
                    return iup.CLOSE
                end
            }
            gaugeProgress = iup.progressbar{ rastersize="400x30", max=intMax } -- Set progress bar maximum range
            messageline = iup.label{ title=" ", expand="YES", alignment="ACENTER" }
            dlgProgress = iup.dialog{ title=strTitle, dialogframe="YES", background=StrWhite, -- Remove Windows minimize/maximize menu
                iup.vbox{ alignment="ACENTER", gap="10", margin="10x10",
                    messageline,
                    gaugeProgress,
                    cancelbutton
                }
            }
            dlgProgress.close_cb = cancelbutton.action -- Windows Close button acts as Cancel button
            dlgProgress:showxy(iup.CENTER, iup.CENTER) -- Show the Progress Display dialogue window
        end
    end,
    
    SetMessage = function(strMessage) -- Set the progress message
        if dlgProgress then messageline.title = strMessage end
    end,
    
    Step = function(iStep) -- Step the Progress Bar forward
        if dlgProgress then
            gaugeProgress.value = gaugeProgress.value + iStep
            local val = tonumber(gaugeProgress.value)
            local max = tonumber(gaugeProgress.max)
            if val > max then
                gaugeProgress.value = 0
            end
            iup.LoopStep()
        end
    end,
    
    Reset = function() -- Reset progress bar
        if dlgProgress then gaugeProgress.value = 0 end
    end,
    
    Cancel = function() -- Check if Cancel button pressed
        return cancelflag
    end,
    
    Close = function() -- Close the dialogue window
        cancelflag = false
        if dlgProgress then dlgProgress:destroy() dlgProgress = nil end
    end,
    
}

function records(type)
    local pi = fhNewItemPtr()
    local p2 = fhNewItemPtr()
    pi:MoveToFirstRecord(type)
    return function ()
        p2:MoveTo(pi)
        pi:MoveNext()
        if p2:IsNotNull() then return p2 end
    end
end

function createResultTable()
    -- create metatable
    local tblOutput_mt = {}
    tblOutput_mt.col = 0
    tblOutput_mt.seq = {}
    tblOutput_mt.__newindex = function (t,k,v)
        rawset(t,k,v) -- update original table
        local m = getmetatable(t)
        m.col = m.col + 1
        table.insert(m.seq,k)
    end
    tblOutput_mt.__call = function (t)
        local i = 0
        local m = getmetatable(t)
        local n = table.getn(m.seq)
        return function ()
            i = i + 1
            if i <= n then return t[m.seq[i]] end
        end
    end
    local tblOutput = {} -- Define Columns Table
    setmetatable(tblOutput, tblOutput_mt)
    local iC = 0 -- Define count of lines
    return tblOutput,iC
end

function loadrequire(module,extended)
    if not(extended) then extended = module end
    local function installmodule(module,filename)
        local bmodule = false
        if not(filename) then
            filename = module..'.mod'
            bmodule = true
        end
        local storein = fhGetContextInfo('CI_APP_DATA_FOLDER')..'\\Plugins\\'
        -- Check if subdirectory needed
        local path = string.match(filename, "(.-)[^/]-[^%.]+$")
        if path ~= "" then
            path = path:gsub('/','\\')
            -- Create sub-directory
            lfs.mkdir(storein..path)
        end
        -- Get file down and install it
        local http = luacom.CreateObject("winhttp.winhttprequest.5.1")
        local url = "http://www.family-historian.co.uk/lnk/getpluginmodule.php?file="..filename
        http:Open("GET",url,false)
        http:Send()
        http:WaitForResponse(30)
        local status = http.StatusText
        if status == 'OK' then
            length = http:GetResponseHeader('Content-Length')
            data = http.ResponseBody
            if bmodule then
                local modlist = loadstring(http.ResponseBody)
                for _,f in pairs(modlist()) do
                    if not(installmodule(module,f)) then
                        break
                    end
                end
            else
                local function OpenFile(strFileName,strMode)
                    local fileHandle, strError = io.open(strFileName,strMode)
                    if not fileHandle then
                        error("\n Unable to open file in \""..strMode.."\" mode. \n "..
                        strFileName.." \n "..tostring(strError).." \n")
                    end
                    return fileHandle
                end -- OpenFile
                local function SaveStringToFile(strString,strFileName)
                    local fileHandle = OpenFile(strFileName,"wb")
                    fileHandle:write(strString)
                    assert(fileHandle:close())
                end -- SaveStringToFile
                SaveStringToFile(data,storein..filename)
            end
            return true
        else
            fhMessageBox('An error occurred in Download please try later')
            return false
        end
    end
    local function requiref(module)
        require(module)
    end
    res = pcall(requiref,extended)
    if not(res) then
        local ans = fhMessageBox(
        'This plugin requires '..module..' support, please click OK to download and install the module',
        'MB_OKCANCEL','MB_ICONEXCLAMATION')
        if ans ~= 'OK' then
            return false
        end
        if installmodule(module) then
            package.loaded[extended] = nil -- Reset Failed Load
            require (extended)
        else
            return false
        end
    end
    return true
end

function OpenFile(strFileName,strMode)
    -- Open File and return Handle --
    local fileHandle, strError = io.open(strFileName,strMode)
    if not fileHandle then
        error("\n Unable to open file in \""..strMode.."\" mode. \n "..strFileName.." \n "..tostring(strError).." \n")
    end
    return fileHandle
end -- function OpenFile

function FileExists(strFileName)
    local fileHandle = io.open(strFileName,"r")
    if fileHandle ~= nil then
        io.close(fileHandle)
        return true
    else
        return false
    end
end

function LoadFromFile(strFileName)
    -- Load string from file --
    local fileHandle = OpenFile(strFileName,"rb")
    local strString = fileHandle:read("*all")
    assert(fileHandle:close())
    return strString
end -- function LoadFromFile

function string.starts(String,Start)
    return string.sub(String,1,string.len(Start))==Start
end
-------------------------------------- SQL Lite Functions
function opendb(dbname)
    -- Check for Settings Database and create if needed
    local db = fhGetPluginDataFileName()
    local dbenv = assert (luasql.sqlite3())
    -- connect to data source, if the file does not exist it will be created
    dbcon = assert (dbenv:connect(db))

    checkTable(dbcon,'medialist',
    [[CREATE TABLE medialist(recordid BIGINT, 
               md5 varchar(100), UNIQUE (recordid))
    ]])
    return dbenv,dbcon
end
function checkTable(dbcon,table,createString)
    local sql = string.format([[SELECT count(name) as count FROM sqlite_master WHERE type='table' AND name='%s']],table)
    local cur = assert(dbcon:execute(sql))
    local rowcount = cur:fetch (row, "a")
    cur:close()
    if tonumber(rowcount) == 0 then
        -- Table not found create it
        res,err = assert(dbcon:execute(createString))
    end
end
function closedb(dbenv,dbcon)
    dbcon:close()
    dbenv:close()
end
function countrecords(dbcon)
    local sql = [[SELECT count(recordid) as count FROM medialist]]
    local cur,err = assert(dbcon:execute(sql))
    local row = cur:fetch({},'a')
    cur:close()
    if row then
        return tonumber(row['count'])
    else
      return false
    end
end
function chkrecord(dbcon,recordid)
    local sql = string.format([[SELECT * FROM medialist where recordid=%s]],recordid)
    local cur,err = assert(dbcon:execute(sql))
    local row = cur:fetch({},'a')
    cur:close()
    if row then
        return row['md5']
    else
      return false
    end
end
function updaterecord(dbcon,recordid,md5)
        local sql = string.format("INSERT OR IGNORE INTO medialist (recordid,md5) VALUES (%s,'%s')",recordid,md5)
        local res = assert(dbcon:execute(sql))
end
function clearlist(dbcon)
        local sql = "DELETE FROM medialist"
        local res = assert(dbcon:execute(sql))
end

-------------------------------------- Call Main Function
require 'lfs'
require 'luacom'
if not(loadrequire('md5')) then return end
if not(loadrequire('luasql','luasql.sqlite3')) then return end
mainfunction()