dofile('./interface/include.lua');
-- parametre identification base de données
identifiantDB = 'root';
motdepasseDB = '';

NomBase = 'gecad_tv'; -- Eviter de modifier ce paramètre 


-- Information : Numéro de Version, Nom, Interface
function device.GetInformation()
	return { version = 1.2, name = 'Gecad TV Chrono', class = 'display', 
				interface = { 
					{ type='tcp', port = 80, hostname = 'localhost' } 
				} 
			};
end	

function connection(params)

end

-- Ouvertures
function device.OnInit(params)
	-- Connexion Base MySQL 	
device.dbTV = sqlBase.ConnectMySQL(params.hostname, NomBase, identifiantDB, motdepasseDB, 3306);
	
	if device.dbTV == nil then
		app.GetAuiMessage():AddLine("Installation de Gecad pour corriger cette erreur");
  	    device.dbTV = sqlBase.ConnectMySQL(params.hostname, '',identifiantDB, motdepasseDB, 3306);
		if device.dbTV == nil  then
			adv.Error("Erreur d'accès à la base de données, erreur d'Installation de Gecad TV");
			else
-- Creation de la base
			device.dbTV:Query("CREATE DATABASE IF NOT EXISTS "..NomBase);
			device.dbTV:Delete();
			device.dbTV = nil;
			device.dbTV = sqlBase.ConnectMySQL(params.hostname, NomBase, identifiantDB, motdepasseDB, 3306);
-- Creation de tables
			device.dbTV:Query("CREATE TABLE IF NOT EXISTS `Context` ("..
			 "`ID` int(11) NOT NULL AUTO_INCREMENT,"..
			 "`Mode` char(20) DEFAULT NULL,"..
			 "`Title` char(150) DEFAULT NULL,"..
			 "`Best_identity` char(150) DEFAULT NULL,"..
			 "`Best_bib` int(11) DEFAULT NULL,"..
			 "`Best_categ` char(12) DEFAULT NULL,"..
			 "`Best_time` char(12) DEFAULT NULL,"..
			 "`Manche` int(8) DEFAULT NULL,"..
			 "`CodeCourse` int(11) NOT NULL,"..
			 "PRIMARY KEY (`ID`)"..
			 ") ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;");
			device.dbTV:Query("CREATE TABLE IF NOT EXISTS `Ranking` ("..
			  "`ID` int(11) NOT NULL,"..
			  "`Tick` char(12) DEFAULT NULL,"..
			  "`Dossard` int(11) DEFAULT NULL,"..
			  "`Bateau` char(180) DEFAULT NULL,"..
			  "`Club` char(30) DEFAULT NULL,"..
			  "`Rang1` char(4) DEFAULT NULL,"..
			  "`Temps1` char(12) DEFAULT NULL,"..
			  "`Temps` char(12) DEFAULT NULL,"..
			  "`Ordre_arrivee` int(10) DEFAULT NULL,"..
			  "`Heure_depart` char(12) DEFAULT NULL,"..
			  "`Rang2` char(4) DEFAULT NULL,"..
			  "`Temps2` char(12) DEFAULT NULL,"..
			  "`Rang` char(4) DEFAULT NULL,"..
			  "`Heure` char(12) DEFAULT NULL,"..
			  "`Categ` char(12) DEFAULT NULL,"..
			  "`Sex` char(1) DEFAULT NULL,"..
			  "`Distance` char(12) DEFAULT NULL,"..
			  "`Epreuve` int(11) DEFAULT NULL,"..
			  "`Finish` int(11) DEFAULT NULL,"..
			  "`Nation` char(6) DEFAULT NULL,"..
			  "`Ordre` int(11) DEFAULT NULL,"..
			  "`Clt` int(11) DEFAULT NULL,"..
			  "`Clt_epreuve` int(11) DEFAULT NULL"..
			") ENGINE=MyISAM DEFAULT CHARSET=latin1;");

			device.dbTV:Query("CREATE TABLE IF NOT EXISTS `Startlist` ("..
			  "`ID` int(11) NOT NULL,"..
			  "`Tick` char(12) DEFAULT NULL,"..
			  "`Dossard` int(11) DEFAULT NULL,"..
			  "`Club` char(30) DEFAULT NULL,"..
			  "`Clt` int(11) DEFAULT NULL,"..
			  "`Heure` char(12) DEFAULT NULL,"..
			  "`Clt_epreuve` int(11) DEFAULT NULL,"..
			  "`Rang` char(4) DEFAULT NULL,"..
			  "`Heure_depart` char(12) DEFAULT NULL,"..
			  "`Epreuve` int(11) DEFAULT NULL,"..
			  "`Nation` char(6) DEFAULT NULL,"..
			  "`Bateau` char(180) DEFAULT NULL,"..
			  "`Rank1` char(4) DEFAULT NULL,"..
			  "`Temps` char(12) DEFAULT NULL,"..
			  "`Rank2` char(4) DEFAULT NULL,"..
			  "`Categ` char(12) DEFAULT NULL,"..
			  "`Sex` char(1) DEFAULT NULL,"..
			  "`Distance` char(12) DEFAULT NULL,"..
			  "PRIMARY KEY (`ID`)"..
			") ENGINE=MyISAM DEFAULT CHARSET=latin1;");			
		end
	end

	device.dbTV:Load();
	
	-- Initialisation table Context
	local tvContext = device.dbTV:GetTable('Context'); 
	if tvContext == nil then adv.Error('Erreur table Context');
	else 
		device.dbTV:Query("Delete From Context");
		device.dbTV:Query("INSERT INTO `Context` (`ID`) VALUES ('1')");
	end

	-- Initialisation des tables
	device.dbTV:Query("Delete From Ranking");
	device.dbTV:Query("Delete From StartList");


	-- Tableau de Bord
	tbNavigation = wnd.CreateAuiToolBar({ style = auiToolBarStyle.HORIZONTAL });

	local btn_startlist = tbNavigation:AddTool("Exportation liste de Départ pour tablette", "./res/32x32_order.png");
	local btn_clear = tbNavigation:AddTool("Ecran vide", "./res/32x32_clear.png");
	local btn_ranking = tbNavigation:AddTool("Affichage liste départ ou classement", "./res/32x32_ranking.png");

	tbNavigation:AddSeparator();
	modeLabel = wnd.CreateStaticText({parent = tbNavigation, label = "Mode StartList" });
	tbNavigation:AddControl(modeLabel);
	tbNavigation:Realize();

	local mgr = app.GetAuiManager();
	mgr:AddPane(tbNavigation, { toolbarpane=true, direction='top', caption = 'Gecad TV', gripper=true });
	mgr:Update();

	-- Prise des Evenements (Bind)
	tbNavigation:Bind(eventType.MENU, OnModeClear, btn_clear);
	tbNavigation:Bind(eventType.MENU, OnModeStartlist, btn_startlist);
	tbNavigation:Bind(eventType.MENU, OnModeRanking, btn_ranking);

	-- Prise valeur offset Horloge PC - Horloge Chrono Officielle
	local rc, offsetInfo = app.SendNotify('<offset_time_load>');
	assert(rc);
	device.offset = tonumber(offsetInfo.offset);

	-- Récupération des infos de la course
	local rc, raceInfo = app.SendNotify('<race_load>');
	assert(rc);
	device.raceInfo = raceInfo;
	app.GetAuiMessage():AddLine('Gecad TV lancé, Course '..GetTitle()..' - Manche '..device.raceInfo.Code_phase);

	-- Récupération Ranking
	local rc, data = app.SendNotify('<ranking_load>');
	assert(rc and data.ranking ~= nil and app.GetNameSpace(data.ranking) == 'sqlTableGC');
	device.ranking = data.ranking;

	app.BindNotify("<bib_time>", device.OnNotifyBibTime); -- Quand il y a un evenement temps sur un dossard

	-- Mode Ranking par défaut ...
	OnModeRanking();
end


function device.OnNotifyBibTime(key, params)
--	 for k,v in pairs(params) do
--		 adv.Alert('Key '..k..'='..tostring(v));
--	 end
	local idPassage = tonumber(params.passage) or -2;  -- -1 si arrivée et >0 si temps inter
	
	if device.mode ~= 'startlist' and idPassage == -1 then
		-- Prise des coureurs arrivés ou Abd ou Dsq
		local filter = 'if @TPS ~= nil and (@TPS > 0 or @TPS == -500 or @TPS == -800) then return true else return false end';
		local TpsCoursePhase = 'Tps_chrono'..tostring(device.raceInfo.Code_course)..'_'..tostring(device.raceInfo.Code_phase);
		filter = string.gsub(filter, '@TPS', TpsCoursePhase);

		local rc, data = app.SendNotify('<ranking_load>', { filter = filter } );
		assert(rc and data.ranking ~= nil and app.GetNameSpace(data.ranking) == 'sqlTableGC');
		device.ranking = data.ranking;	
		RefreshMode();
	end
end

-- Fermeture
function device.OnClose()

	-- Fermeture Base
	if device.dbTV ~= nil then
		device.dbTV:Delete();
		device.dbTV = nil;
	end
	
	-- Fermeture Toolbar 
	if tbNavigation ~= nil then
		local mgr = app.GetAuiManager();
		mgr:DeletePane(tbNavigation);
	end
end

function GetTitle()
	if device.raceInfo.tables ~= nil then
		local tCompetition = device.raceInfo.tables.Competition;
		if tCompetition ~= nil then
			local title = tCompetition:GetCell('Nom', 0);
			return string.gsub(title,"'", "''");
		end
	end
	return '';
end

function GetCodeCourse()
	if device.raceInfo.tables ~= nil then
		local tCompetition = device.raceInfo.tables.Competition;
		if tCompetition ~= nil then
			local Code = tCompetition:GetCell('Code', 0);
			return string.gsub(Code,"'", "''");
		end
	end
	return '';
end

function OnModeClear()
	device.mode = 'clear';
	modeLabel:SetLabel('clear');

	device.dbTV:Query("Update Context Set Mode = 'clear' Where ID = 1");
	device.dbTV:Query("Delete From Ranking");
	device.dbTV:Query("Delete From StartList");
	RefreshMode();
end

function OnModeStartlist()
	-- Récupération des infos de la course
	local rc, raceInfo = app.SendNotify('<race_load>');
	assert(rc);
	device.raceInfo = raceInfo;
	local txtCoursePhase = tostring(device.raceInfo.Code_course)..'_'..tostring(device.raceInfo.Code_phase);
	
	-- Récupération Ranking
	local rc, data = app.SendNotify('<ranking_load>');
	assert(rc and data.ranking ~= nil and app.GetNameSpace(data.ranking) == 'sqlTableGC');
	device.ranking = data.ranking;
	device.ranking:OrderBy('Heure_depart'..txtCoursePhase..' Asc');
	local tRanking = device.ranking;

	-- Recupération du chemin du dossier du bureau
	local os = os
	paths = os.getenv('USERPROFILE');
	filetxt = paths.."\\desktop\\listedepart.txt";		-- Nom du Fichier de sortie

	-- Ecriture du fichier	
	f = assert(io.open(filetxt, "w+"));
	for i=0,tRanking:GetNbRows()-1 do
		f:write(tRanking:GetCell('Dossard', i));
		f:write(" "..tRanking:GetCell('Heure_depart'..txtCoursePhase, i, '[P1000]%2hh%2m:%2s').."\n");
	end
	f:close();

	app.GetAuiMessage():AddLine("Pour importer la liste départ sur la tablette,");
	app.GetAuiMessage():AddLine("retrouver le fichier listedepart.tx sur le bureau ou dans le dossier :");
	app.GetAuiMessage():AddLine(paths.."\\desktop");
end

function OnModeRanking()
    -- initialisation de la liste départ
	device.mode = 'startlist';
	modeLabel:SetLabel('startlist');
	device.dbTV:Query("Update Context Set Mode = 'startlist', Title = '"..GetTitle().."', CodeCourse = '"..GetCodeCourse().."', Manche = '"..device.raceInfo.Code_phase.."' Where ID = 1");

	local rc, data = app.SendNotify('<ranking_load>');
	assert(rc and data.ranking ~= nil and app.GetNameSpace(data.ranking) == 'sqlTableGC');
	device.ranking = data.ranking;
	RefreshMode();
	
	device.mode = 'ranking';
	modeLabel:SetLabel('en course');
	device.dbTV:Query("Update Context Set Mode = 'ranking', Title = '"..GetTitle().."', CodeCourse = '"..GetCodeCourse().."', Manche = '"..device.raceInfo.Code_phase.."' Where ID = 1");

	local TpsCoursePhase = 'Tps_chrono'..tostring(device.raceInfo.Code_course)..'_'..tostring(device.raceInfo.Code_phase);

	local filter = 'if @TPS ~= nil and (@TPS > 0 or @TPS == -500 or @TPS == -800) then return true else return false end';
	filter = string.gsub(filter, '@TPS', TpsCoursePhase);
	
	local rc, data = app.SendNotify('<ranking_load>', { filter = filter } );
	assert(rc and data.ranking ~= nil and app.GetNameSpace(data.ranking) == 'sqlTableGC');

	device.ranking = data.ranking;
	RefreshMode();
end

function RefreshMode()
	-- Tri ...
	local txtCoursePhase = tostring(device.raceInfo.Code_course)..'_'..tostring(device.raceInfo.Code_phase);

	if device.mode == 'startlist' then
		-- Liste de Départ 
		device.ranking:OrderBy('Heure_depart'..txtCoursePhase..', Dossard');
		TvSynchroStartlist();
	elseif device.mode == 'ranking' then
		-- Liste de Résultat
		device.ranking:OrderBy('Clt'..txtCoursePhase..', Dossard');
		TvSynchroRanking();
	end
end

function TvSynchroStartlist()
	if device.ranking == nil or type(device.ranking) ~= 'userdata' then return end
	if device.dbTV == nil or type(device.dbTV) ~= 'userdata' then return end

	local tvStartlist = device.dbTV:GetTable('Startlist');
	if tvStartlist == nil then return end

	local tRanking = device.ranking;
	local txtCoursePhase = tostring(device.raceInfo.Code_course)..'_'..tostring(device.raceInfo.Code_phase);

	tvStartlist:RemoveAllRows();
	for i=0,tRanking:GetNbRows()-1 do
		tvStartlist:GetRecord():Set('ID', i+1);
		tvStartlist:GetRecord():Set('Dossard', tRanking:GetCell('Dossard', i));
		tvStartlist:GetRecord():Set('Bateau', string.gsub(tRanking:GetCell('Bateau', i), "'", "''"));
		tvStartlist:GetRecord():Set('Club', string.gsub(tRanking:GetCell('Club', i), "'", "''"));
		tvStartlist:GetRecord():Set('Nation', tRanking:GetCell('Code_pays', i));
		tvStartlist:GetRecord():Set('Epreuve', tRanking:GetCell('Code_categorie', i));
		tvStartlist:GetRecord():Set('Rang', tRanking:GetCell('Rang'..txtCoursePhase, i));
		tvStartlist:GetRecord():Set('Heure_depart', tRanking:GetCell('Heure_depart'..txtCoursePhase, i, '[P1000]%2hh%2m:%2s'));
		tvStartlist:GetRecord():Set('Temps', tRanking:GetCell('Tps_chrono'..txtCoursePhase, i));
		tvStartlist:GetRecord():Set('Clt', tRanking:GetCell('Clt'..txtCoursePhase, i));
		tvStartlist:GetRecord():Set('Clt_epreuve', tRanking:GetCell('Cltc'..txtCoursePhase, i));
		tvStartlist:GetRecord():Set('Categ', tRanking:GetCell('Code_categorie', i));
		tvStartlist:AddRow();
	end

	device.dbTV:Query('Delete From Startlist');
	device.dbTV:TableBulkInsert(tvStartlist);
	device.dbTV:Query("Update Startlist Set Tick = '"..app.GetTickCount().."' ");
end

function TvSynchroRanking()
	if device.ranking == nil or type(device.ranking) ~= 'userdata' then return end
	if device.dbTV == nil or type(device.dbTV) ~= 'userdata' then return end

	local tvRanking = device.dbTV:GetTable('Ranking');
	if tvRanking == nil then return end

	local tRanking = device.ranking;
	local txtCoursePhase = tostring(device.raceInfo.Code_course)..'_'..tostring(device.raceInfo.Code_phase);

	tvRanking:RemoveAllRows();
	for i=0,tRanking:GetNbRows()-1 do
		tvRanking:GetRecord():Set('ID', i+1);
		tvRanking:GetRecord():Set('Dossard', tRanking:GetCell('Dossard', i));
		tvRanking:GetRecord():Set('Bateau', string.gsub(tRanking:GetCell('Bateau', i), "'", "''"));
		tvRanking:GetRecord():Set('Club', string.gsub(tRanking:GetCell('Club', i), "'", "''"));
		tvRanking:GetRecord():Set('Nation', tRanking:GetCell('Code_pays', i));
		tvRanking:GetRecord():Set('Epreuve', tRanking:GetCell('Code_categorie', i));
		tvRanking:GetRecord():Set('Rang', tRanking:GetCell('Rang'..txtCoursePhase, i));
		tvRanking:GetRecord():Set('Heure_depart', tRanking:GetCell('Heure_depart'..txtCoursePhase, i));
		tvRanking:GetRecord():Set('Heure_depart', tRanking:GetCell('Heure_depart'..txtCoursePhase, i, '[P1000]%2hh%2m:%2s'));
		tvRanking:GetRecord():Set('Temps', tRanking:GetCell('Tps_chrono'..txtCoursePhase, i));
		tvRanking:GetRecord():Set('Clt', tRanking:GetCell('Clt'..txtCoursePhase, i));
		tvRanking:GetRecord():Set('Clt_epreuve', tRanking:GetCell('Cltc'..txtCoursePhase, i));
		tvRanking:GetRecord():Set('Ordre_arrivee', tRanking:GetCellInt('Heure_arrivee_reelle', i));
		tvRanking:GetRecord():Set('Categ', tRanking:GetCell('Code_categorie', i));		
		tvRanking:AddRow();		
	end

	device.dbTV:Query('Delete From Ranking');
	device.dbTV:TableBulkInsert(tvRanking);
	device.dbTV:Query("Update Ranking Set Tick = '"..app.GetTickCount().."' ");
end