;Excel to sql for vpgdataimport
;Jiong Ye
;Version: 0.1
;7-09-09

#include <GUIConstantsEx.au3>
#include <WindowsConstants.au3>
#include <EditConstants.au3>
#include <Excel.au3>
#include <Array.au3>
#Include <Date.au3>

;file picker
$excelFile = FileOpenDialog("Select an excel file",@DesktopDir,"Excel (*.xls)" ,1+2)

If Not @error Then
	;create main window
	$mainGui = GUICreate("Importing",350,150)
	GUISetState(@SW_SHOW)

	$status = GUICtrlCreateEdit("Begin importing",10,10,330,130,$ES_AUTOVSCROLL+$ES_AUTOHSCROLL+$ES_MULTILINE+$ES_READONLY)

	addStatus("File selected:"&$excelFile)

	doExport($excelFile)
Else
	MsgBox(0,"Error","Error opening file")
	Exit
EndIf

While 1
	$msg = GUIGetMsg()
    If $msg = $GUI_EVENT_CLOSE Then ExitLoop
WEnd
GUIDelete()

Func doExport($file)
	$fpExcel = _ExcelBookOpen($file,0,1)
	If @error = 1 Then
		addStatus("Unable to Create the Excel Object")
		Exit
	ElseIf @error = 2 Then
		addStatus("File does not exist - Shame on you!")
		Exit
	Else
		addStatus("File open successfully")
	EndIf

	$done = False
	$i = 2
	While Not $done
		;read a row from excel file
		$excelArray = _ExcelReadArray($fpExcel,$i,1,9,0,0)

		If $excelArray[0] <> "" Then
			addStatus("reading Row " & $i)
			;insert into sql file
			arrayToSql($excelArray)
		Else
			addStatus("Finsihed reading excel file")
			addStatus($i-2 & " rows converted into sql statement")
			$done = True
		EndIf
		$i += 1
	WEnd

	_ExcelBookClose($fpExcel)
EndFunc

Func arrayToSql($array)
	;save sql file to desktop
	$sqlFile = FileOpen(@DesktopDir&"/ExcelToSql-"&@MDAY&"-"&@MON&"-"&@YEAR&".sql",1+8)

	$sqlStatement = "INSERT INTO `_products` (`int_id`, `product_type`, `title`, `description`, `synopsis`, `publication_date`, `price`, `categories`, `list`, `type_id`, `processed`, `write_err`) VALUES ("
	$sqlStatement &= "'"& escapeString($array[0]) & "'," ;int_id
	$sqlStatement &= "'"& escapeString($array[1]) & "'," ;product_type
	$sqlStatement &= "'"& escapeString($array[2]) & "'," ;title
	$sqlStatement &= "'"& escapeString($array[3]) & "'," ;description
	$sqlStatement &= "'"& escapeString($array[4]) & "'," ;synopsis
	$sqlStatement &= "'"& toExcelDate($array[5]) & "',"  ;date
	$sqlStatement &= "'"& escapeString($array[6]) & "'," ;price
	$sqlStatement &= "'"& escapeString($array[7]) & "'," ;category
	$sqlStatement &= "'"& escapeString($array[8]) & "'," ;list
	$sqlStatement &= "'','N','N'"
	$sqlStatement &= ");"

	FileWriteLine($sqlFile,$sqlStatement)
	FileClose($sqlFile)
EndFunc

Func escapeString($string)
	;replace anything that is not a number, letter or common punctuation
	$string = StringRegExpReplace($string,"[^0-9a-zA-Z.,\\/\!@#$%\^\&\*\(\)\-_\+=\h<>]","")
	;escape single quote
	$string = StringRegExpReplace($string,"[']","\'")
	Return $string
EndFunc

Func toExcelDate($dateString)
	$yearString = StringMid($dateString,1,4)
	$monthString = StringMid($dateString,5,2)
	$dayString = StringMid($dateString,7,2)
	return $dayString&"-"&StringMid(_DateToMonth($monthString,1),1,3)&"-"&$yearString
EndFunc

Func addStatus($text)
	GUICtrlSetData($status,@CRLF&$text,1)
EndFunc