#NoTrayIcon

;===============================================================================
;
; Description: Downloads a local copy of video messages sent through Skype. Videos are saved as "<video_sender>-<timestamp>.mp4".
; Tested on AutoIt version 3.3.12.0; Skype for Windows v.7.0 & v.7.2 (on a couple Windows 7 installations in different languages)
; Requirements: Skype for Desktop installed; Local SQLite3 dll or network access
; Author: VinCor
; Credits :
;     - "Skype Community" for how to download those videos (http://community.skype.com/t5/Windows-desktop-client/HOW-TO-SAVE-VIDEO-MESSAGE/td-p/1763009)
;     - Help files for "inspiration" of most of the code (SQLite, progress bar, InetGetInfo, etc);
;     - trancexx for the original _EPOCH_decrypt function (http://www.autoitscript.com/forum/topic/83667-epoch-time/)
;
; ATTENTION.1: The URLs found in the database are dynamic. You have to open Skype and play the video message to get a new (and valid) URL before running this script.
; ATTENTION.2: This works on Skype "for Desktop" on windows 7. For "Modern" Skype on Windows 8.x, the location of the main.db is different. Change the script accordingly to make it work for you.
;
;===============================================================================
; *** Functionality in pseudo code ***
;
; Find Skype SQLite DBs/users
; For each DB/user
;   Open SQLite DB
;   Find Videos Table
;   For each video entry
;     Check if already downloaded
;     If not, download it
;     Keep only successfully downloaded videos
; Exit
;
;===============================================================================


#include <File.au3>
#include <SQLite.au3>
#include <SQLite.dll.au3>
#include <Date.au3>

_SQLite_Startup()
If @error Then
	MsgBox($MB_SYSTEMMODAL, "SQLite Error", "SQLite3.dll Can't be Loaded!" & @CRLF & @CRLF & _
			"Not FOUND in @SystemDir, @WindowsDir, @ScriptDir, @WorkingDir or from www.autoitscript.com")
	Exit -1
EndIf

If Not FileExists(@ScriptDir & "\videos") Then DirCreate(@ScriptDir & "\videos")

; Find SQLite DBs
$aSkypeUserDBs = _FileListToArrayRec(@UserProfileDir & "\AppData\Roaming\Skype", "main.db||shared*", $FLTAR_FILES, $FLTAR_RECUR, $FLTAR_NOSORT, $FLTAR_FULLPATH)
If Not IsArray($aSkypeUserDBs) Then
	MsgBox(0, "No Skype users found!", "Cannot find Skype data under user profile folder... Bye!")
	Exit
EndIf

; For each skype user found
For $db = 1 To $aSkypeUserDBs[0]
	ProgressOn("Download Skype video messages", "Skype user: " & StringRegExpReplace($aSkypeUserDBs[$db], ".*\\(\w+)\\main.db", "\1"), "Checking video URLs", -1, -1, BitOR($DLG_NOTONTOP, $DLG_MOVEABLE))
	Local $hQuery, $aRow

	; Open SQLite DB
	_SQLite_Open($aSkypeUserDBs[$db])

	; Find VideoTable:VOD_path,author,timestamp column
	_SQLite_Query(-1, "SELECT VOD_path,author,creation_timestamp FROM VideoMessages;", $hQuery)

	While _SQLite_FetchData($hQuery, $aRow, False, False) = $SQLITE_OK ; For each row in the DB
		If StringLen($aRow[0]) > 0 Then ; not a null URL entry
			$filename = $aRow[1] & "-" & _EPOCH_decrypt($aRow[2]) ; base filename
			If FileExists(@ScriptDir & "\videos\" & $filename & ".mp4") Then ContinueLoop ; to avoid downloading a previously downloaded video
			$hVideoMessage = InetGet($aRow[0], @ScriptDir & "\videos\" & $filename & ".tmp", $INET_FORCERELOAD, $INET_DOWNLOADBACKGROUND) ; start download in the background
			ProgressSet(100 * InetGetInfo($hVideoMessage, $INET_DOWNLOADREAD) / InetGetInfo($hVideoMessage, $INET_DOWNLOADSIZE), "Video message: " & $filename)
			Do ; update progress bar while downloading video...
				Sleep(250)
				ProgressSet(100 * InetGetInfo($hVideoMessage, $INET_DOWNLOADREAD) / InetGetInfo($hVideoMessage, $INET_DOWNLOADSIZE))
			Until InetGetInfo($hVideoMessage, $INET_DOWNLOADCOMPLETE) ; until it is done downloading

			; Rename file in case of successful download
			If InetGetInfo($hVideoMessage, $INET_DOWNLOADERROR) = 0 And InetGetInfo($hVideoMessage, $INET_DOWNLOADREAD) = InetGetInfo($hVideoMessage, $INET_DOWNLOADSIZE) Then
				FileMove(@ScriptDir & "\videos\" & $filename & ".tmp", @ScriptDir & "\videos\" & $filename & ".mp4")
			Else ; or delete it in case of failure
				FileDelete(@ScriptDir & "\videos\" & $filename & ".tmp")
			EndIf
		EndIf
	WEnd

	_SQLite_QueryFinalize($hQuery)
	_SQLite_Close()
Next
_SQLite_Shutdown()
Exit

Func _EPOCH_decrypt($iEpochTime)
	Local $iDayToAdd = Int($iEpochTime / 86400)
	Local $iTimeVal = Mod($iEpochTime, 86400)
	If $iTimeVal < 0 Then
		$iDayToAdd -= 1
		$iTimeVal += 86400
	EndIf
	Local $i_wFactor = Int((573371.75 + $iDayToAdd) / 36524.25)
	Local $i_xFactor = Int($i_wFactor / 4)
	Local $i_bFactor = 2442113 + $iDayToAdd + $i_wFactor - $i_xFactor
	Local $i_cFactor = Int(($i_bFactor - 122.1) / 365.25)
	Local $i_dFactor = Int(365.25 * $i_cFactor)
	Local $i_eFactor = Int(($i_bFactor - $i_dFactor) / 30.6001)
	Local $aDatePart[3]
	$aDatePart[2] = $i_bFactor - $i_dFactor - Int(30.6001 * $i_eFactor)
	$aDatePart[1] = $i_eFactor - 1 - 12 * ($i_eFactor - 2 > 11)
	$aDatePart[0] = $i_cFactor - 4716 + ($aDatePart[1] < 3)
	Local $aTimePart[3]
	$aTimePart[0] = Int($iTimeVal / 3600)
	$iTimeVal = Mod($iTimeVal, 3600)
	$aTimePart[1] = Int($iTimeVal / 60)
	$aTimePart[2] = Mod($iTimeVal, 60)
	Return StringFormat("%.2d%.2d%.2d_%.2d%.2d%.2d", $aDatePart[0], $aDatePart[1], $aDatePart[2], $aTimePart[0], $aTimePart[1], $aTimePart[2])
EndFunc   ;==>_EPOCH_decrypt