<?php
	/**************************************************************************\
	* eGroupWare - Setup                                                       *
	* http://www.egroupware.org                                                *
	* --------------------------------------------                             *
	*  This program is free software; you can redistribute it and/or modify it *
	*  under the terms of the GNU General Public License as published by the   *
	*  Free Software Foundation; either version 2 of the License, or (at your  *
	*  option) any later version.                                              *
	\**************************************************************************/

	function extractDatabaseParameters()
	{
		/* extract the database connection parameters */

		$workflowHostInfo = array();
		if (!empty($GLOBALS['phpgw']->ADOdb->database))
		{
			$workflowHostInfo = array(
				'dbname' => $GLOBALS['phpgw']->ADOdb->database,
				'host' => $GLOBALS['phpgw']->ADOdb->host,
				'port' => $GLOBALS['phpgw']->ADOdb->port,
				'user' => $GLOBALS['phpgw']->ADOdb->user,
				'password' => $GLOBALS['phpgw']->ADOdb->password);
		}
		else
		{
			$hostInfo = "dbname= host= password= port= user= " . $GLOBALS['phpgw']->ADOdb->host;
			$hostInfo = explode(' ', $hostInfo);
			foreach ($hostInfo as $parameter)
			{
				$currentParameter = explode('=', $parameter);
				$workflowHostInfo[$currentParameter[0]] = isset($currentParameter[1]) ? $currentParameter[1] : "";
			}
		}
		if (($workflowHostInfo['password']{0} == "'") && ($workflowHostInfo['password']{strlen($workflowHostInfo['password'])-1} == "'"))
			$workflowHostInfo['password'] = substr($workflowHostInfo['password'], 1, strlen($workflowHostInfo['password']) - 2);

		return $workflowHostInfo;
	}

	// Since Expresso 1.2 using Workflow 1.8.04.000
	$test[] = '1.8.04.000';
	function workflow_upgrade1_8_04_000()
	{
		/* Create index for table egw_wf_workitems  */
		if (!$GLOBALS['phpgw_setup']->oProc->m_bDeltaOnly)
		{
			$GLOBALS['phpgw']->ADOdb->query('CREATE INDEX egw_wf_workitems_wf_instance_id_idx ON egw_wf_workitems(wf_instance_id)');
		}

		#updating the current version
		$GLOBALS['setup_info']['workflow']['currentver'] = '1.8.05.000';
		return $GLOBALS['setup_info']['workflow']['currentver'];
	}

	$test[] = '1.8.05.000';
	function workflow_upgrade1_8_05_000()
	{
		/* Alter tables of orgchart */
		if (!$GLOBALS['phpgw_setup']->oProc->m_bDeltaOnly)
		{
			$workflowHostInfo = extractDatabaseParameters();

			/* connect to workflow database */
			$workflowDB = $GLOBALS['phpgw']->ADOdb;
			if ($workflowDB->connect($workflowHostInfo['host'].":".$workflowHostInfo['port'], $workflowHostInfo['user'], $workflowHostInfo['password'], 'workflow'))
			{
				/* add the new columns */
				$workflowDB->query('ALTER TABLE public.funcionario ADD COLUMN titulo CHARACTER VARYING(30)');
				$workflowDB->query('ALTER TABLE public.organizacao ADD COLUMN sitio CHARACTER VARYING(100)');
				$workflowDB->query('ALTER TABLE public.localidade ADD COLUMN empresa CHARACTER VARYING(100)');
				$workflowDB->query('ALTER TABLE public.localidade ADD COLUMN endereco CHARACTER VARYING(100)');
				$workflowDB->query('ALTER TABLE public.localidade ADD COLUMN complemento CHARACTER VARYING(50)');
				$workflowDB->query('ALTER TABLE public.localidade ADD COLUMN cep CHARACTER VARYING(9)');
				$workflowDB->query('ALTER TABLE public.localidade ADD COLUMN bairro CHARACTER VARYING(30)');
				$workflowDB->query('ALTER TABLE public.localidade ADD COLUMN cidade CHARACTER VARYING(50)');
				$workflowDB->query('ALTER TABLE public.localidade ADD COLUMN uf CHARACTER(2)');

				/* create table telefone */
				$workflowDB->query('CREATE TABLE public.telefone (telefone_id serial NOT NULL, descricao character varying(50) NOT NULL, numero character varying(50) NOT NULL, organizacao_id integer NOT NULL)');
				$workflowDB->query('ALTER TABLE ONLY public.telefone ADD CONSTRAINT telefone_pkey PRIMARY KEY (telefone_id)');
				$workflowDB->query('ALTER TABLE ONLY public.telefone ADD CONSTRAINT "$1" FOREIGN KEY (organizacao_id) REFERENCES organizacao(organizacao_id)');
				$workflowDB->query("GRANT ALL ON TABLE public.telefone TO admin_workflow");
				$workflowDB->query("GRANT ALL ON TABLE public.telefone TO postgres");
				$workflowDB->query("GRANT SELECT ON TABLE public.telefone TO public");
				$workflowDB->query("GRANT ALL ON TABLE public.telefone_telefone_id_seq TO admin_workflow");
				$workflowDB->query("GRANT ALL ON TABLE public.telefone_telefone_id_seq TO postgres");
				$workflowDB->query("GRANT SELECT ON TABLE public.telefone_telefone_id_seq TO public");
			}

			/* reconnect to the previous database */
			$GLOBALS['phpgw']->ADOdb->connect($workflowHostInfo['host'].":".$workflowHostInfo['port'], $workflowHostInfo['user'], $workflowHostInfo['password'], $workflowHostInfo['dbname']);
		}
		#updating the current version
		$GLOBALS['setup_info']['workflow']['currentver'] = '1.8.06.000';
		return $GLOBALS['setup_info']['workflow']['currentver'];
	}

	$test[] = '1.8.06.000';
	function workflow_upgrade1_8_06_000()
	{
		#updating the current version
		$GLOBALS['setup_info']['workflow']['currentver'] = '2.0.000';
		return $GLOBALS['setup_info']['workflow']['currentver'];
	}
	$test[] = '2.0.000';
	function workflow_upgrade2_0_000()
	{
		#updating the current version
		$GLOBALS['setup_info']['workflow']['currentver'] = '2.0.001';
		return $GLOBALS['setup_info']['workflow']['currentver'];
	}
	$test[] = '2.0.001';
	function workflow_upgrade2_0_001()
	{
		#updating the current version
		$GLOBALS['setup_info']['workflow']['currentver'] = '2.0.002';
		return $GLOBALS['setup_info']['workflow']['currentver'];
	}
	$test[] = '2.0.002';
	function workflow_upgrade2_0_002()
	{
		#updating the current version
		$GLOBALS['setup_info']['workflow']['currentver'] = '2.0.003';
		return $GLOBALS['setup_info']['workflow']['currentver'];
	}
	$test[] = '2.0.003';
	function workflow_upgrade2_0_003()
	{
		#updating the current version
		$GLOBALS['setup_info']['workflow']['currentver'] = '2.0.004';
		return $GLOBALS['setup_info']['workflow']['currentver'];
	}
	$test[] = '2.0.004';
	function workflow_upgrade2_0_004()
	{
		#updating the current version
		$GLOBALS['setup_info']['workflow']['currentver'] = '2.1.000';
		return $GLOBALS['setup_info']['workflow']['currentver'];
	}
	$test[] = '2.1.000';
	function workflow_upgrade2_1_000()
	{
		if (!$GLOBALS['phpgw_setup']->oProc->m_bDeltaOnly)
		{
			/* updating log level */
			$values = array('0', 'workflow', 'log_level');
			$GLOBALS['phpgw']->ADOdb->query('UPDATE phpgw_config SET config_value=? WHERE config_app=? AND config_name=?', $values);

			$workflowHostInfo = extractDatabaseParameters();

			/* connect to workflow database */
			$workflowDB = $GLOBALS['phpgw']->ADOdb;
			if ($workflowDB->connect($workflowHostInfo['host'], $workflowHostInfo['user'], $workflowHostInfo['password'], 'workflow'))
			{
				/* creating table substitution */
				$workflowDB->query('CREATE TABLE substituicao (substituicao_id serial NOT NULL, area_id integer NOT NULL, funcionario_id integer NOT NULL, data_inicio date NOT NULL, data_fim date NOT NULL)');

				$workflowDB->query('ALTER TABLE ONLY public.substituicao ADD CONSTRAINT substituicao_pkey PRIMARY KEY (substituicao_id)');
				$workflowDB->query('ALTER TABLE ONLY public.substituicao ADD CONSTRAINT "$1" FOREIGN KEY (area_id) REFERENCES area(area_id)');
				$workflowDB->query('ALTER TABLE ONLY public.substituicao ADD CONSTRAINT "$2" FOREIGN KEY (funcionario_id) REFERENCES funcionario(funcionario_id)');

				/* granting privilegies */
				$workflowDB->query("GRANT ALL ON TABLE public.substituicao TO admin_workflow");
				$workflowDB->query("GRANT ALL ON TABLE public.substituicao TO postgres");
				$workflowDB->query("GRANT SELECT ON TABLE public.substituicao TO public");
				$workflowDB->query("GRANT ALL ON TABLE public.substituicao_substituicao_id_seq TO admin_workflow");
				$workflowDB->query("GRANT ALL ON TABLE public.substituicao_substituicao_id_seq TO postgres");
				$workflowDB->query("GRANT SELECT ON TABLE public.substituicao_substituicao_id_seq TO public");

				/* migrating records */
				$result = $workflowDB->query('SELECT area_id, substituto_funcionario_id FROM area WHERE substituto_funcionario_id IS NOT NULL');
				if ($result)
					while ($row = $result->fetchRow()) {
						$values = array($row['area_id'], $row['substituto_funcionario_id']);
						$workflowDB->query('INSERT INTO substituicao (area_id, funcionario_id, data_inicio, data_fim) VALUES (?, ?, CURRENT_DATE, CURRENT_DATE+integer \'7\')', $values);
					}


				/* erasing old atributes */
				$workflowDB->query("ALTER TABLE area DROP COLUMN substituto_funcionario_id");
			}

			/* reconnect to the previous database */
			$GLOBALS['phpgw']->ADOdb->connect($workflowHostInfo['host'], $workflowHostInfo['user'], $workflowHostInfo['password'], $workflowHostInfo['dbname']);

			/* removing primary key of egw_wf_interinstance_relations */
			$GLOBALS['phpgw']->ADOdb->query('ALTER TABLE egw_wf_interinstance_relations DROP CONSTRAINT egw_wf_interinstance_relations_pkey');

			/* removing wf_parent_activity_id column from egw_wf_interinstance_relations table */
			$GLOBALS['phpgw']->ADOdb->query('ALTER TABLE egw_wf_interinstance_relations DROP COLUMN wf_parent_activity_id');

			/* adding primary key without the column removed */
			$GLOBALS['phpgw']->ADOdb->query('ALTER TABLE egw_wf_interinstance_relations ADD CONSTRAINT egw_wf_interinstance_relations_pkey PRIMARY KEY (wf_parent_instance_id, wf_child_instance_id)');
			
			/* Update the organogram level of administration off all users from 0 to 1. */
			$GLOBALS['phpgw']->ADOdb->query('UPDATE egw_wf_admin_access set nivel = 1 WHERE tipo = \'ORG\' and nivel = 0');
		}

		#updating the current version
		$GLOBALS['setup_info']['workflow']['currentver'] = '2.2.000';
		return $GLOBALS['setup_info']['workflow']['currentver'];
	}
?>