<?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;
	}

	$test[] = '1.0.1';
	function workflow_upgrade1_0_1()
	{
		# add an instance_supplements table
		$GLOBALS['phpgw_setup']->oProc->createTable('egw_wf_instance_supplements',
			array(
				'fd' => array(
					'wf_supplement_id' 	=> array('type' => 'auto', 'precision' => '4', 'nullable' => False),
					'wf_supplement_type'	=> array('type' => 'varchar', 'precision' => '50', 'nullable' => True),
					'wf_supplement_name'	=> array('type' => 'varchar', 'precision' => '100', 'nullable' => True),
					'wf_supplement_value'	=> array('type' => 'text', 'nullable' => True),
					'wf_workitem_id'	=> array('type' => 'int', 'precision' => '4', 'nullable' => True),
					'wf_supplement_blob'	=> array('type' => 'blob', 'nullable' => True)
				),
				'pk' => array('wf_supplement_id'),
				'fk' => array(),
				'ix' => array(),
				'uc' => array()
			)
		);

		#Add in activities table is_reassign_box, is_report, default_user and default group
		$GLOBALS['phpgw_setup']->oProc->AddColumn('egw_wf_activities' ,'wf_is_reassign_box',array('type' => 'char', 'precision' => 1, 'nullable' => True));
		$GLOBALS['phpgw_setup']->oProc->AddColumn('egw_wf_activities' ,'wf_is_report',array('type' => 'char', 'precision' => 1, 'nullable' => True));
		$GLOBALS['phpgw_setup']->oProc->AddColumn('egw_wf_activities' ,'wf_default_user', array('type' => 'varchar', 'precision' => '200', 'nullable' => True, 'default' => '*'));
		$GLOBALS['phpgw_setup']->oProc->AddColumn('egw_wf_activities' ,'wf_default_group', array('type' => 'varchar', 'precision' => '200', 'nullable' => True, 'default' => '*'));

		#Add in instance_activities table the group field
		$GLOBALS['phpgw_setup']->oProc->AddColumn('egw_wf_instance_activities' ,'wf_group',array('type' => 'varchar', 'precision' => 200, 'nullable' => True, 'default' => '*'));

		#Add in instance table the name, and the priority, we keep the properties for the moment
		$GLOBALS['phpgw_setup']->oProc->AddColumn('egw_wf_instances' ,'wf_priority',array('type' => 'int', 'precision' => 4, 'nullable' => True));
		$GLOBALS['phpgw_setup']->oProc->AddColumn('egw_wf_instances' ,'wf_name',array('type' => 'varchar', 'precision' => 120, 'nullable' => True));

		#Add in workitems table note and action
		$GLOBALS['phpgw_setup']->oProc->AddColumn('egw_wf_workitems' ,'wf_note',array('type' => 'text', 'precision' => 50, 'nullable' => True));
		$GLOBALS['phpgw_setup']->oProc->AddColumn('egw_wf_workitems' ,'wf_action',array('type' => 'text', 'precision' => 50, 'nullable' => True));

		#Add in user_roles table the account type
		$GLOBALS['phpgw_setup']->oProc->AddColumn('egw_wf_user_roles' ,'wf_account_type',array('type' => 'char', 'precision' => 1, 'nullable' => True, 'default' => 'u'));
			#modifying the sequence as well
			#we need a RefreshTable
		$GLOBALS['phpgw_setup']->oProc->RefreshTable('egw_wf_user_roles' ,array(
		 	'fd' => array(
				'wf_role_id'            => array('type' => 'auto', 'precision' => '4', 'nullable' => False),
				'wf_p_id'               => array('type' => 'int', 'precision' => '4', 'nullable' => False),
				'wf_user'               => array('type' => 'varchar', 'precision' => '200', 'nullable' => False),
				'wf_account_type'       => array('type' => 'char', 'precision' => '1', 'nullable' => True, 'default' => 'u'),
			 ),
			 'pk' => array('wf_role_id', 'wf_user', 'wf_account_type'),
			 'fk' => array(),
			 'ix' => array(),
			 'uc' => array()
		));

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

	$test[] = '1.1.00.000';
	function workflow_upgrade1_1_00_000()
	{
		# add a process_config table
		$GLOBALS['phpgw_setup']->oProc->createTable('egw_wf_process_config',
			array(
				'fd' => array(
					'wf_p_id'               => array('type' => 'int', 'precision' => '4', 'nullable' => False),
					'wf_config_name' 	=> array('type' => 'varchar', 'precision' => '255', 'nullable' => False),
					'wf_config_value'	=> array('type' => 'text', 'nullable' => True),
					'wf_config_value_int'	=> array('type' => 'int', 'precision' => '4', 'nullable' => True),
				),
				'pk' => array('wf_p_id','wf_config_name'),
				'fk' => array(),
				'ix' => array(),
				'uc' => array()
			)
		);

		//change de default value for priority


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

	$test[] = '1.1.01.000';
	function workflow_upgrade1_1_01_000()
	{
		#remove unused 'new' fields in activity and add a agent key
 		$GLOBALS['phpgw_setup']->oProc->DropColumn('egw_wf_activities', '', 'wf_is_reassign_box');
 		$GLOBALS['phpgw_setup']->oProc->DropColumn('egw_wf_activities', '', 'wf_is_report');
 		$GLOBALS['phpgw_setup']->oProc->DropColumn('egw_wf_activities', '', 'wf_default_group');
 		$GLOBALS['phpgw_setup']->oProc->AddColumn('egw_wf_activities' ,'wf_agent', array('type' => 'int', 'precision' => '4', 'nullable' => True));

		#add a readonly attribute to role/activty mapping
		$GLOBALS['phpgw_setup']->oProc->AddColumn('egw_wf_activity_roles' ,'wf_readonly', array('type' => 'int', 'precision' => '1', 'nullable' => False, 'default'=> 0));

		#add a instance category attribute
		$GLOBALS['phpgw_setup']->oProc->AddColumn('egw_wf_instances' ,'wf_category', array('type' => 'int', 'precision'=>'4', 'nullable' => True));

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


	$test[] = '1.1.02.000';
	function workflow_upgrade1_1_02_000()
	{
 		//drop the agent key in activity, we need something more complex in fact
 		$GLOBALS['phpgw_setup']->oProc->DropColumn('egw_wf_activities','','wf_agent');

		//add the agent table, link between activities and agents
		$GLOBALS['phpgw_setup']->oProc->createTable('egw_wf_activity_agents',
			array(
				'fd' => array(
					'wf_activity_id'	=> array('type' => 'int', 'precision' => '4', 'nullable' => False),
					'wf_agent_id' 		=> array('type' => 'int', 'precision' => '4', 'nullable' => False),
					'wf_agent_type'		=> array('type' => 'varchar', 'precision' => '15', 'nullable' => False),
				),
				'pk' => array('wf_activity_id', 'wf_agent_id'),
				'fk' => array(),
				'ix' => array(),
				'uc' => array()
			)
		);

		// add the mail_smtp agent table
		$GLOBALS['phpgw_setup']->oProc->createTable('egw_wf_agent_mail_smtp',
			array(
				'fd' => array(
					'wf_agent_id'		=> array('type' => 'auto', 'precision' => '4', 'nullable' => False),
					'wf_to' 		=> array('type' => 'varchar', 'precision' => '255', 'nullable' => False, 'default' => '%roles%'),
					'wf_cc'			=> array('type' => 'varchar', 'precision' => '255', 'nullable' => True),
					'wf_bcc'		=> array('type' => 'varchar', 'precision' => '255', 'nullable' => True),
					'wf_from'		=> array('type' => 'varchar', 'precision' => '255', 'nullable' => True, 'default' => '%user%'),
					'wf_replyTo'		=> array('type' => 'varchar', 'precision' => '255', 'nullable' => True, 'default' => '%user%'),
					'wf_subject'		=> array('type' => 'varchar', 'precision' => '255', 'nullable' => True),
					'wf_message'		=> array('type' => 'text', 'nullable' => True),
					'wf_send_mode'		=> array('type' => 'int', 'precision' => '4', 'nullable' => True, 'default' => 0),
				),
				'pk' => array('wf_agent_id'),
				'fk' => array(),
				'ix' => array(),
				'uc' => array()
			)
		);

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

	$test[] = '1.1.03.000';
	function workflow_upgrade1_1_03_000()
	{
		//change type of wf_next_user to handle serialization -> multiple states if instance has multiple activities running
		//we will loose current wf_next_activity but the update should'nt be made when instances are running and this is
		//a field needed only at runtime, normally.
		$GLOBALS['phpgw_setup']->oProc->DropColumn('egw_wf_instances','','wf_next_activity');
		$GLOBALS['phpgw_setup']->oProc->AddColumn('egw_wf_instances' ,'wf_next_activity', array('type' => 'blob', 'nullable' => True));

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

	$test[] = '1.1.04.000';
	function workflow_upgrade1_1_04_000()
	{
		//unused column. Notice I had to do this manually with MAIN versions of phpgwapi/class.schema_proc.inc.php
		$GLOBALS['phpgw_setup']->oProc->DropColumn('egw_wf_instance_activities','','wf_group');

		//Adding some indexes on some tables:

		//we need a RefreshTable for that
		$GLOBALS['phpgw_setup']->oProc->RefreshTable('egw_wf_instance_activities' ,array(
			'fd' => array(
				'wf_instance_id'	=> array('type' => 'int', 'precision' => '4', 'nullable' => False),
				'wf_activity_id'	=> array('type' => 'int', 'precision' => '4', 'nullable' => False),
				'wf_started'		=> array('type' => 'int', 'precision' => '4', 'nullable' => False),
				'wf_ended'		=> array('type' => 'int', 'precision' => '4', 'nullable' => True),
				'wf_user'		=> array('type' => 'varchar', 'precision' => '200', 'nullable' => True),
				'wf_status'		=> array('type' => 'varchar', 'precision' => '25', 'nullable' => True),
			),
			'pk' => array('wf_instance_id', 'wf_activity_id'),
			'fk' => array(),
			'ix' => array(array('wf_activity_id'),array('wf_instance_id'), array('wf_user')),
			'uc' => array()
		));
		// we change some indexes
		// we need a RefreshTable
		$GLOBALS['phpgw_setup']->oProc->RefreshTable('egw_wf_instances' ,array(
			'fd' => array(
				'wf_instance_id'	=> array('type' => 'auto', 'precision' => '4', 'nullable' => False),
				'wf_p_id'		=> array('type' => 'int', 'precision' => '4', 'nullable' => False),
				'wf_started'		=> array('type' => 'int', 'precision' => '4', 'nullable' => True),
				'wf_owner'		=> array('type' => 'varchar', 'precision' => '200', 'nullable' => True),
				'wf_next_activity'	=> array('type' => 'blob', 'nullable' => True),
				'wf_next_user'		=> array('type' => 'varchar', 'precision' => '200', 'nullable' => True),
				'wf_ended'		=> array('type' => 'int', 'precision' => '4', 'nullable' => True),
				'wf_status'		=> array('type' => 'varchar', 'precision' => '25', 'nullable' => True),
				'wf_priority'		=> array('type' => 'int', 'precision' => '4', 'nullable' => True, 'default'=> 0),
				'wf_properties'		=> array('type' => 'blob', 'nullable' => True),
				'wf_name'		=> array('type' => 'varchar', 'precision'=>'120', 'nullable' => True),
				'wf_category'		=> array('type' => 'int', 'precision'=>'4', 'nullable' => True),
			),
			'pk' => array('wf_instance_id'),
			'fk' => array(),
			'ix' => array(array('wf_owner'), array('wf_status')),
			'uc' => array()
		));
		// we change some indexes
		// we need a RefreshTable
		$GLOBALS['phpgw_setup']->oProc->RefreshTable('egw_wf_processes' ,array(
			'fd' => array(
				'wf_p_id'		=> array('type' => 'auto', 'precision' => '4', 'nullable' => False),
				'wf_name'		=> array('type' => 'varchar', 'precision' => '80', 'nullable' => True),
				'wf_is_valid'		=> array('type' => 'char', 'precision' => '1', 'nullable' => True),
				'wf_is_active'		=> array('type' => 'char', 'precision' => '1', 'nullable' => True),
				'wf_version'		=> array('type' => 'varchar', 'precision' => '12', 'nullable' => True),
				'wf_description'	=> array('type' => 'text', 'nullable' => True),
				'wf_last_modif'		=> array('type' => 'int', 'precision' => '4', 'nullable' => True),
				'wf_normalized_name'	=> array('type' => 'varchar', 'precision' => '80', 'nullable' => True),
			),
			'pk' => array('wf_p_id'),
			'fk' => array(),
			'ix' => array(array('wf_p_id','wf_is_active')),
			'uc' => array()
		));

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

	$test[] = '1.1.05.000';
	function workflow_upgrade1_1_05_000()
	{
		#serialized data is now stored with a Base64 encoding to ensure it work in all case (even with \' for example)
		//We gonna make our updates manually here:
		//they were an error (quotes) in the first version of this upgrade, if your database is MySQL you should
		//set the version to 1.1.05.000 in egw_applications and rerun this upgrade for old properties
		if (!$GLOBALS['phpgw_setup']->oProc->m_bDeltaOnly)
		{
			$GLOBALS['phpgw']->ADOdb->SetFetchMode(ADODB_FETCH_ASSOC);
			$result = $GLOBALS['phpgw']->ADOdb->query('select * from egw_wf_instances');
			if (!(empty($result)))
			{
				while ($res = $result->fetchRow())
				{
					$new_props = base64_encode($res['wf_properties']);
					$new_next = base64_encode($res['wf_next_activity']);
					$ok =  $GLOBALS['phpgw']->ADOdb->query(
						'update egw_wf_instances set wf_properties = ?, wf_next_activity=? where wf_instance_id = ?',
						array($new_props,$new_next, (int)$res['wf_instance_id'])
					);
				}
			}
			$result = $GLOBALS['phpgw']->ADOdb->query('select * from egw_wf_workitems');
			if (!(empty($result)))
			{
				while ($res = $result->fetchRow())
				{
					$new_props = base64_encode($res['wf_properties']);
					$ok =  $GLOBALS['phpgw']->ADOdb->query(
						'update egw_wf_workitems set wf_properties = ?  where wf_item_id = ?',
						array($new_props, (int)$res['wf_item_id'])
					);
				}
			}
		}

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

	$test[] = '1.2.00.000';
	function workflow_upgrade1_2_00_000()
	{
		#updating the current version
		$GLOBALS['setup_info']['workflow']['currentver'] = '1.2.00.001';
		return $GLOBALS['setup_info']['workflow']['currentver'];
	}
	$test[] = '1.2.00.001';
	function workflow_upgrade1_2_00_001()
	{
		#updating the current version
		$GLOBALS['setup_info']['workflow']['currentver'] = '1.2.00.002';
		return $GLOBALS['setup_info']['workflow']['currentver'];
	}
	$test[] = '1.2.00.002';
	function workflow_upgrade1_2_00_002()
	{
		#groups Ids are now negative in egroupware, we need to negative all user id which is positive and of type 'g'
		#code inspired by phpgwapi/setup/table_update.inc.php
		// convert all positive group id's to negative ones
		// this allows duplicate id for users and groups in ldap

		/* Celepar: não realizar esta atualização
		$where = false;
		list($table,$col,$where) = $data;
		$table = 'egw_wf_user_roles';
		$col = 'wf_user';
		$where = "wf_account_type='g' and (SUBSTR($col,1,1) <> '-')";
		$set = $col.'='.$GLOBALS['egw_setup']->db->concat("'-'",$col);
		$query = "UPDATE $table SET $set WHERE $where";
		//echo "<p>debug query: $query</p>\n";
		$GLOBALS['egw_setup']->db->query($query,__LINE__,__FILE__);

		*/

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

	$test[] = '1.2.01.000';
        function workflow_upgrade1_2_01_000()
        {
                #updating the current version
                $GLOBALS['setup_info']['workflow']['currentver'] = '1.2.01.001';
                return $GLOBALS['setup_info']['workflow']['currentver'];
        }

        $test[] = '1.2.01.001';
        function workflow_upgrade1_2_01_001()
        {
                #updating the current version
                $GLOBALS['setup_info']['workflow']['currentver'] = '1.2.01.002';
                return $GLOBALS['setup_info']['workflow']['currentver'];
        }

        $test[] = '1.2.01.002';
        function workflow_upgrade1_2_01_002()
        {
                #updating the current version
                $GLOBALS['setup_info']['workflow']['currentver'] = '1.2.01.003';
                return $GLOBALS['setup_info']['workflow']['currentver'];
        }

	$test[] = '1.2.01.003';
	function workflow_upgrade1_2_01_003()
	{
		#updating the current version
		$GLOBALS['setup_info']['workflow']['currentver'] = '1.2.01.004';
		return $GLOBALS['setup_info']['workflow']['currentver'];
	}

	$test[] = '1.2.01.004';
	function workflow_upgrade1_2_01_004()
	{
		#updating the current version
		$GLOBALS['setup_info']['workflow']['currentver'] = '1.2.01.005';
		return $GLOBALS['setup_info']['workflow']['currentver'];
	}

	$test[] = '1.2.01.005';
	function workflow_upgrade1_2_01_005()
	{
		if (!$GLOBALS['phpgw_setup']->oProc->m_bDeltaOnly)
		{
			$workflowHostInfo = extractDatabaseParameters();

			/* create the workflow database */
			$GLOBALS['phpgw']->ADOdb->query("CREATE DATABASE workflow WITH OWNER = postgres TEMPLATE = template0 ENCODING = 'LATIN1'");
			$GLOBALS['phpgw']->ADOdb->query("CREATE USER admin_workflow WITH PASSWORD 'admin_workflow' NOCREATEDB NOCREATEUSER VALID UNTIL 'infinity'");
			$GLOBALS['phpgw']->ADOdb->query("CREATE GROUP workflow");
			$GLOBALS['phpgw']->ADOdb->query("ALTER GROUP workflow ADD USER admin_workflow");
			$GLOBALS['phpgw']->ADOdb->query("GRANT workflow TO admin_workflow");

			/* connect to the new database */
			$workflowDB = $GLOBALS['phpgw']->ADOdb;
			if ($workflowDB->connect($workflowHostInfo['host'].":".$workflowHostInfo['port'], $workflowHostInfo['user'], $workflowHostInfo['password'], 'workflow'))
			{
				/* create a sample application (Music CD Loan) */
				$workflowDB->query("CREATE USER admin_cds WITH PASSWORD 'admin_cds' NOCREATEDB NOCREATEUSER VALID UNTIL 'infinity'");
				$workflowDB->query("ALTER GROUP workflow ADD USER admin_cds");
				$workflowDB->query("CREATE SCHEMA cds AUTHORIZATION admin_cds");
				$workflowDB->query("GRANT ALL ON SCHEMA cds TO admin_cds");
				$workflowDB->query("GRANT ALL ON SCHEMA cds TO postgres");
				$workflowDB->query("CREATE TABLE cds.cdcollection(cdid int4 NOT NULL, title varchar(200), status varchar(40), usuario varchar(200), CONSTRAINT cdcollection_pkey PRIMARY KEY (cdid))");
				$workflowDB->query("ALTER TABLE cds.cdcollection OWNER TO admin_cds");
				$workflowDB->query("GRANT ALL ON TABLE cds.cdcollection TO postgres WITH GRANT OPTION");
				$workflowDB->query("GRANT ALL ON TABLE cds.cdcollection TO admin_cds");
				$workflowDB->query("COMMENT ON TABLE cds.cdcollection IS 'Exemplo de Empréstimo de CDs'");
				$workflowDB->query("INSERT INTO cdcollection VALUES (1, 'Iron Maiden - Powerslave', 'disponivel', '')");
				$workflowDB->query("INSERT INTO cdcollection VALUES (2, 'Pink Floyd - The Wall', 'disponivel', '')");
				$workflowDB->query("INSERT INTO cdcollection VALUES (3, 'Deep Purple - Fireball', 'disponivel', '')");

				/* create the new tables */
				$workflowDB->query('CREATE TABLE organizacao (organizacao_id serial NOT NULL, nome character varying(20) NOT NULL, descricao character varying(100) NOT NULL)');
				$workflowDB->query('CREATE TABLE area_status (area_status_id serial NOT NULL, organizacao_id integer NOT NULL, descricao character varying(50) NOT NULL, nivel integer NOT NULL)');
				$workflowDB->query('CREATE TABLE centro_custo (organizacao_id integer NOT NULL, centro_custo_id serial NOT NULL, nm_centro_custo integer NOT NULL, grupo character varying(30), descricao character varying(100) NOT NULL)');
				$workflowDB->query('CREATE TABLE localidade (organizacao_id integer NOT NULL, localidade_id serial NOT NULL, centro_custo_id integer, descricao character varying(50) NOT NULL)');
				$workflowDB->query('CREATE TABLE funcionario (funcionario_id character varying NOT NULL, area_id integer NOT NULL, localidade_id integer NOT NULL, centro_custo_id integer NOT NULL, organizacao_id integer NOT NULL, funcionario_status_id integer NOT NULL)');
				$workflowDB->query('CREATE TABLE area (organizacao_id integer NOT NULL, area_id serial NOT NULL, area_status_id integer NOT NULL, areasup_organizacao_id integer, superior_area_id integer, centro_custo_id integer, titular_funcionario_id character varying, substituto_funcionario_id character varying, sigla character varying(20) NOT NULL, descricao character varying(100) NOT NULL, ativa character varying(1) NOT NULL)');
				$workflowDB->query('CREATE TABLE funcionario_status (funcionario_status_id serial NOT NULL, descricao character varying(50) NOT NULL)');
				$workflowDB->query('CREATE TABLE admin_access (admin_access_id integer NOT NULL, tipo character(3) NOT NULL, numvalue smallint NOT NULL, nivel integer DEFAULT 0)');
				$workflowDB->query('CREATE TABLE aplicacao_externa (apl_in_id serial NOT NULL, apl_st_nome character varying(50) NOT NULL, apl_st_descricao text, apl_st_imagem character varying(100), apl_st_endereco character varying(120) NOT NULL, apl_bt_autentica bit(1), apl_st_post text, apl_in_responsavel integer NOT NULL, apl_in_area integer NOT NULL)');
				$workflowDB->query('CREATE TABLE aplicacao_externa_acl (apl_in_id integer NOT NULL, apl_in_uid integer NOT NULL)');

				/* add the constraints */
				$workflowDB->query('ALTER TABLE ONLY organizacao ADD CONSTRAINT organizacao_pkey PRIMARY KEY (organizacao_id)');
				$workflowDB->query('ALTER TABLE ONLY area_status ADD CONSTRAINT areastatus_pkey PRIMARY KEY (area_status_id, organizacao_id)');
				$workflowDB->query('ALTER TABLE ONLY centro_custo ADD CONSTRAINT centrocusto_pkey PRIMARY KEY (organizacao_id, centro_custo_id)');
				$workflowDB->query('ALTER TABLE ONLY localidade ADD CONSTRAINT localidade_pkey PRIMARY KEY (organizacao_id, localidade_id)');
				$workflowDB->query('ALTER TABLE ONLY funcionario ADD CONSTRAINT funcionario_pkey PRIMARY KEY (funcionario_id)');
				$workflowDB->query('ALTER TABLE ONLY area ADD CONSTRAINT area_pkey PRIMARY KEY (area_id)');
				$workflowDB->query('ALTER TABLE ONLY funcionario_status ADD CONSTRAINT funcionario_status_pkey PRIMARY KEY (funcionario_status_id)');
				$workflowDB->query('ALTER TABLE ONLY area_status ADD CONSTRAINT "$1" FOREIGN KEY (organizacao_id) REFERENCES organizacao(organizacao_id)');
				$workflowDB->query('ALTER TABLE ONLY centro_custo ADD CONSTRAINT "$1" FOREIGN KEY (organizacao_id) REFERENCES organizacao(organizacao_id)');
				$workflowDB->query('ALTER TABLE ONLY localidade ADD CONSTRAINT "$1" FOREIGN KEY (centro_custo_id, organizacao_id) REFERENCES centro_custo(centro_custo_id, organizacao_id)');
				$workflowDB->query('ALTER TABLE ONLY localidade ADD CONSTRAINT "$2" FOREIGN KEY (organizacao_id) REFERENCES organizacao(organizacao_id)');
				$workflowDB->query('ALTER TABLE ONLY funcionario ADD CONSTRAINT "$1" FOREIGN KEY (organizacao_id, centro_custo_id) REFERENCES centro_custo(organizacao_id, centro_custo_id)');
				$workflowDB->query('ALTER TABLE ONLY funcionario ADD CONSTRAINT "$2" FOREIGN KEY (organizacao_id, localidade_id) REFERENCES localidade(organizacao_id, localidade_id)');
				$workflowDB->query('ALTER TABLE ONLY area ADD CONSTRAINT "$1" FOREIGN KEY (superior_area_id, areasup_organizacao_id) REFERENCES area(area_id, organizacao_id)');
				$workflowDB->query('ALTER TABLE ONLY area ADD CONSTRAINT "$2" FOREIGN KEY (centro_custo_id, organizacao_id) REFERENCES centro_custo(centro_custo_id, organizacao_id)');
				$workflowDB->query('ALTER TABLE ONLY area ADD CONSTRAINT "$5" FOREIGN KEY (organizacao_id) REFERENCES organizacao(organizacao_id)');
				$workflowDB->query('ALTER TABLE ONLY area ADD CONSTRAINT "$6" FOREIGN KEY (area_status_id, organizacao_id) REFERENCES area_status(area_status_id, organizacao_id)');
				$workflowDB->query('ALTER TABLE ONLY funcionario ADD CONSTRAINT "$3" FOREIGN KEY (organizacao_id, area_id) REFERENCES area(organizacao_id, area_id)');
				$workflowDB->query('ALTER TABLE ONLY funcionario ADD CONSTRAINT "$4" FOREIGN KEY (funcionario_status_id) REFERENCES funcionario_status(funcionario_status_id)');
				$workflowDB->query('ALTER TABLE ONLY area ADD CONSTRAINT "$3" FOREIGN KEY (titular_funcionario_id) REFERENCES funcionario(funcionario_id)');
				$workflowDB->query('ALTER TABLE ONLY area ADD CONSTRAINT "$4" FOREIGN KEY (substituto_funcionario_id) REFERENCES funcionario(funcionario_id)');
				$workflowDB->query('ALTER TABLE ONLY admin_access ADD CONSTRAINT admin_access_pkey PRIMARY KEY (admin_access_id, tipo, numvalue)');
				$workflowDB->query('ALTER TABLE ONLY aplicacao_externa ADD CONSTRAINT seq_aplicacao_externa PRIMARY KEY (apl_in_id)');
				$workflowDB->query('ALTER TABLE ONLY aplicacao_externa_acl ADD CONSTRAINT pk_aplicacao_externa_acl PRIMARY KEY (apl_in_id, apl_in_uid)');
				$workflowDB->query('ALTER TABLE ONLY aplicacao_externa_acl ADD CONSTRAINT fk01_aplicacao_externa_acl FOREIGN KEY (apl_in_id) REFERENCES aplicacao_externa(apl_in_id) ON UPDATE CASCADE ON DELETE CASCADE');

				/* set the permissions to the database objects */
				$dbObjects = array('organizacao', 'area_status', 'centro_custo', 'localidade', 'funcionario', 'area', 'funcionario_status', 'organizacao_organizacao_id_seq', 'area_status_area_status_id_seq', 'centro_custo_centro_custo_id_seq', 'localidade_localidade_id_seq', 'area_area_id_seq', 'funcionario_status_funcionario_status_id_seq', 'admin_access', 'aplicacao_externa', 'aplicacao_externa_apl_in_id_seq', 'aplicacao_externa_acl');
				foreach ($dbObjects as $dbObject)
				{
					$workflowDB->query("GRANT ALL ON TABLE $dbObject TO admin_workflow");
					$workflowDB->query("GRANT ALL ON TABLE $dbObject TO postgres");
					$workflowDB->query("GRANT SELECT ON TABLE $dbObject 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.3.00.000';
		return $GLOBALS['setup_info']['workflow']['currentver'];
	}

	$test[] = '1.3.00.000';
	function workflow_upgrade1_3_00_000()
	{
		$GLOBALS['phpgw_setup']->oProc->createTable('egw_wf_interinstance_relations',
			array(
				'fd' => array(
					'wf_parent_instance_id'	=> array('type' => 'int', 'precision' => '4', 'nullable' => False),
					'wf_parent_activity_id'	=> array('type' => 'int', 'precision' => '4', 'nullable' => False),
					'wf_child_instance_id' 	=> array('type' => 'int', 'precision' => '4', 'nullable' => False),
					'wf_parent_lock' 		=> array('type' => 'int', 'precision' => '2', 'nullable' => True)
				),
				'pk' => array('wf_parent_instance_id', 'wf_parent_activity_id', 'wf_child_instance_id'),
				'fk' => array(),
				'ix' => array(),
				'uc' => array()
			)
		);

		if (!$GLOBALS['phpgw_setup']->oProc->m_bDeltaOnly)
		{
			$workflowHostInfo = extractDatabaseParameters();

			/* connect to the workflow database */
			$workflowDB = $GLOBALS['phpgw']->ADOdb;
			if ($workflowDB->connect($workflowHostInfo['host'].":".$workflowHostInfo['port'], $workflowHostInfo['user'], $workflowHostInfo['password'], 'workflow'))
			{
				/* change the type of the employee ID (funcionario_id field) */
				$workflowDB->query('ALTER TABLE funcionario ALTER COLUMN centro_custo_id DROP NOT NULL');
				$workflowDB->query('ALTER TABLE area DROP CONSTRAINT "$3"');
				$workflowDB->query('ALTER TABLE area DROP CONSTRAINT "$4"');
				$workflowDB->query('ALTER TABLE funcionario ADD COLUMN funcionario_id2 int4');
				$workflowDB->query('UPDATE funcionario SET funcionario_id2 = int4(funcionario_id)');
				$workflowDB->query('ALTER TABLE funcionario DROP COLUMN funcionario_id');
				$workflowDB->query('ALTER TABLE funcionario RENAME COLUMN funcionario_id2 TO funcionario_id');
				$workflowDB->query('ALTER TABLE funcionario ALTER funcionario_id SET NOT NULL');
				$workflowDB->query('ALTER TABLE funcionario ADD CONSTRAINT funcionario_pkey PRIMARY KEY (funcionario_id)');
				$workflowDB->query('ALTER TABLE area ADD titular_funcionario_id2 int4');
				$workflowDB->query('ALTER TABLE area ADD substituto_funcionario_id2 int4');
				$workflowDB->query('UPDATE area SET titular_funcionario_id2 = int4(titular_funcionario_id), substituto_funcionario_id2 = int4(substituto_funcionario_id)');
				$workflowDB->query('ALTER TABLE area DROP COLUMN titular_funcionario_id');
				$workflowDB->query('ALTER TABLE area DROP COLUMN substituto_funcionario_id');
				$workflowDB->query('ALTER TABLE area RENAME COLUMN titular_funcionario_id2 TO titular_funcionario_id');
				$workflowDB->query('ALTER TABLE area RENAME COLUMN substituto_funcionario_id2 TO substituto_funcionario_id');
				$workflowDB->query('ALTER TABLE area ADD CONSTRAINT "$3" FOREIGN KEY (titular_funcionario_id) REFERENCES funcionario (funcionario_id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION');
				$workflowDB->query('ALTER TABLE AREA ADD CONSTRAINT "$4" FOREIGN KEY (substituto_funcionario_id) REFERENCES funcionario (funcionario_id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION');
			}

			/* 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.4.00.000';
		return $GLOBALS['setup_info']['workflow']['currentver'];
	}

	$test[] = '1.4.00.000';
	function workflow_upgrade1_4_00_000()
	{
		#updating the current version
		$GLOBALS['setup_info']['workflow']['currentver'] = '1.4.00.001';
		return $GLOBALS['setup_info']['workflow']['currentver'];
	}

	$test[] = '1.4.00.001';
	function workflow_upgrade1_4_00_001()
	{
		$GLOBALS['phpgw_setup']->oProc->AddColumn('egw_wf_activities' ,'wf_menu_path', array('type' => 'varchar', 'precision' => 200, 'nullable' => True));

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

	$test[] = '1.4.01.000';
	function workflow_upgrade1_4_01_000()
	{
		if (!$GLOBALS['phpgw_setup']->oProc->m_bDeltaOnly)
		{
			$workflowHostInfo = extractDatabaseParameters();

			/* connect to the new database */
			$workflowDB = $GLOBALS['phpgw']->ADOdb;
			if ($workflowDB->connect($workflowHostInfo['host'].":".$workflowHostInfo['port'], $workflowHostInfo['user'], $workflowHostInfo['password'], 'workflow'))
			{
				/* add a new column to the area table (orgchart) */
				$workflowDB->query('ALTER TABLE area ADD COLUMN auxiliar_funcionario_id int4');
				$workflowDB->query('ALTER TABLE ONLY area ADD CONSTRAINT "$7" FOREIGN KEY (auxiliar_funcionario_id) REFERENCES funcionario(funcionario_id)');
			}

			/* 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.4.02.000';
		return $GLOBALS['setup_info']['workflow']['currentver'];
	}

	$test[] = '1.4.02.000';
	function workflow_upgrade1_4_02_000()
	{
		$GLOBALS['phpgw_setup']->oProc->createTable('egw_wf_admin_access',
			array(
				'fd' => array(
					'admin_access_id'		=> array('type' => 'int', 'precision' => '4', 'nullable' => False),
					'tipo'	=> array('type' => 'char', 'precision' => '3', 'nullable' => False),
					'numvalue'	=> array('type' => 'int', 'precision' => '2', 'nullable' => False),
					'nivel'	=> array('type' => 'int', 'precision' => '4', 'nullable' => True, 'default' => 0)
				),
				'pk' => array('admin_access_id', 'tipo', 'numvalue'),
				'fk' => array(),
				'ix' => array(),
				'uc' => array()
			)
		);

		$GLOBALS['phpgw_setup']->oProc->createTable('egw_wf_external_application',
			array(
				'fd' => array(
					'external_application_id' => array('type' => 'auto', 'precision' => '4', 'nullable' => False),
					'name' => array('type' => 'varchar', 'precision' => '50', 'nullable' => False),
					'description' => array('type' => 'text', 'nullable' => True),
					'image' => array('type' => 'varchar', 'precision' => '100', 'nullable' => True),
					'address' => array('type' => 'varchar', 'precision' => '120', 'nullable' => False),
					'authentication' => array('type' => 'bit', 'precision' => '1', 'nullable' => True),
					'post' => array('type' => 'text', 'nullable' => True)
				),
				'pk' => array('external_application_id'),
				'fk' => array(),
				'ix' => array(),
				'uc' => array()
			)
		);

		if (!$GLOBALS['phpgw_setup']->oProc->m_bDeltaOnly)
		{
			$adminAccessData = array();
			$externalApplicationData = array();
			$externalApplicationSequence = 1;
			$externalApplicationACLData = array();
			$workflowHostInfo = extractDatabaseParameters();
			$GLOBALS['phpgw']->ADOdb->SetFetchMode(ADODB_FETCH_ASSOC);

			/* connect to the Workflow database */
			$workflowDB = $GLOBALS['phpgw']->ADOdb;
			if ($workflowDB->connect($workflowHostInfo['host'].":".$workflowHostInfo['port'], $workflowHostInfo['user'], $workflowHostInfo['password'], 'workflow'))
			{
				$result = $GLOBALS['phpgw']->ADOdb->query('SELECT admin_access_id, tipo, numvalue, nivel FROM admin_access');
				if ($result)
					while ($row = $result->fetchRow())
						$adminAccessData[] = array($row['admin_access_id'], $row['tipo'], $row['numvalue'], $row['nivel']);

				$result = $GLOBALS['phpgw']->ADOdb->query('SELECT apl_in_id, apl_st_nome, apl_st_descricao, apl_st_imagem, apl_st_endereco, apl_bt_autentica, apl_st_post FROM aplicacao_externa');
				if ($result)
					while ($row = $result->fetchRow())
						$externalApplicationData[] = array($row['apl_in_id'], $row['apl_st_nome'], $row['apl_st_descricao'], $row['apl_st_imagem'], $row['apl_st_endereco'], $row['apl_bt_autentica'], $row['apl_st_post']);
				$result = $GLOBALS['phpgw']->ADOdb->query('SELECT last_value FROM aplicacao_externa_apl_in_id_seq');
				if ($result)
					if ($row = $result->fetchRow())
						$externalApplicationSequence = $row['last_value'];

				$result = $GLOBALS['phpgw']->ADOdb->query('SELECT apl_in_uid, apl_in_id FROM aplicacao_externa_acl');
				if ($result)
					while ($row = $result->fetchRow())
						$externalApplicationACLData[] = array($row['apl_in_uid'], $row['apl_in_id']);

				/* remove the unused tables */
				$GLOBALS['phpgw']->ADOdb->query('DROP TABLE admin_access');
				$GLOBALS['phpgw']->ADOdb->query('DROP SEQUENCE aplicacao_externa_apl_in_id_seq');
				$GLOBALS['phpgw']->ADOdb->query('DROP CONSTRAINT fk01_aplicacao_externa_acl');
				$GLOBALS['phpgw']->ADOdb->query('DROP TABLE aplicacao_externa_acl');
				$GLOBALS['phpgw']->ADOdb->query('DROP TABLE aplicacao_externa');
			}

			/* reconnect to the previous database */
			$GLOBALS['phpgw']->ADOdb->connect($workflowHostInfo['host'].":".$workflowHostInfo['port'], $workflowHostInfo['user'], $workflowHostInfo['password'], $workflowHostInfo['dbname']);
			foreach ($adminAccessData as $newRow)
				$result = $GLOBALS['phpgw']->ADOdb->query("INSERT INTO egw_wf_admin_access(admin_access_id, tipo, numvalue, nivel) VALUES (?, ?, ?, ?)", $newRow);

			foreach ($externalApplicationData as $newRow)
				$result = $GLOBALS['phpgw']->ADOdb->query("INSERT INTO egw_wf_external_application(external_application_id, name, description, image, address, authentication, post) VALUES(?, ?, ?, ?, ?, ?, ?)", $newRow);
			$GLOBALS['phpgw']->ADOdb->query("SELECT pg_catalog.setval('seq_egw_wf_external_application', ?, true)", array($externalApplicationSequence));

			foreach ($externalApplicationACLData as $newRow)
				$result = $GLOBALS['phpgw']->ADOdb->query("INSERT INTO egw_wf_admin_access(admin_access_id, tipo, numvalue, nivel) VALUES (?, 'APX', ?, 0)", $newRow);
		}

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

	$test[] = '1.4.03.000';
	function workflow_upgrade1_4_03_000()
	{
		if (!$GLOBALS['phpgw_setup']->oProc->m_bDeltaOnly)
		{
			$workflowHostInfo = extractDatabaseParameters();

			/* connect to the new database */
			$workflowDB = $GLOBALS['phpgw']->ADOdb;
			if ($workflowDB->connect($workflowHostInfo['host'].":".$workflowHostInfo['port'], $workflowHostInfo['user'], $workflowHostInfo['password'], 'workflow'))
			{
				/* add a new column to the funcionario_status table (orgchart) */
				$workflowDB->query('ALTER TABLE funcionario_status ADD COLUMN exibir character varying(1)');
				$workflowDB->query('UPDATE funcionario_status SET exibir = ?', array('S'));
				$workflowDB->query('ALTER TABLE funcionario_status ALTER COLUMN exibir SET NOT NULL');
			}

			/* 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.4.04.000';
		return $GLOBALS['setup_info']['workflow']['currentver'];
	}

	$test[] = '1.4.04.000';
	function workflow_upgrade1_4_04_000()
	{
		/* remove unused table */
		$GLOBALS['phpgw_setup']->oProc->DropTable('egw_wf_instance_supplements');

		if (!$GLOBALS['phpgw_setup']->oProc->m_bDeltaOnly)
		{
			$GLOBALS['phpgw']->ADOdb->query('ALTER TABLE egw_wf_instances ALTER COLUMN wf_next_user TYPE text');
			$GLOBALS['phpgw']->ADOdb->SetFetchMode(ADODB_FETCH_ASSOC);
			$result = $GLOBALS['phpgw']->ADOdb->query('SELECT i.wf_instance_id, ia.wf_activity_id, i.wf_next_user FROM egw_wf_instances i, egw_wf_instance_activities ia WHERE (i.wf_instance_id = ia.wf_instance_id) AND (i.wf_next_user IS NOT NULL)');
			if (!empty($result))
			{
				while ($res = $result->fetchRow())
				{
					$newNextUser = base64_encode(serialize(array('*' . $res['wf_activity_id'] => $res['wf_next_user'])));
					$ok = $GLOBALS['phpgw']->ADOdb->query(
						'UPDATE egw_wf_instances SET wf_next_user = ? WHERE (wf_instance_id = ?)',
						array($newNextUser, (int)$res['wf_instance_id'])
					);
				}
			}

			$workflowHostInfo = extractDatabaseParameters();

			/* connect to the new database */
			$workflowDB = $GLOBALS['phpgw']->ADOdb;
			if ($workflowDB->connect($workflowHostInfo['host'].":".$workflowHostInfo['port'], $workflowHostInfo['user'], $workflowHostInfo['password'], 'workflow'))
			{
				/* add a new column to the organizacao table (orgchart) */
				$workflowDB->query('ALTER TABLE organizacao ADD COLUMN url_imagem character varying(200)');
			}

			/* 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.4.05.000';
		return $GLOBALS['setup_info']['workflow']['currentver'];
	}

	$test[] = '1.4.05.000';
	function workflow_upgrade1_4_05_000()
	{
		$GLOBALS['phpgw_setup']->oProc->AddColumn('egw_wf_external_application' ,'intranet_only', array('type' => 'bit', 'precision' => '1', 'nullable' => True));

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

	$test[] = '1.4.06.000';
	function workflow_upgrade1_4_06_000()
	{
		#updating the current version
		$GLOBALS['setup_info']['workflow']['currentver'] = '1.6.00.000';
		return $GLOBALS['setup_info']['workflow']['currentver'];
	}

	$test[] = '1.6.00.000';
	function workflow_upgrade1_6_00_000()
	{
		if (!$GLOBALS['phpgw_setup']->oProc->m_bDeltaOnly)
		{
			$workflowHostInfo = extractDatabaseParameters();

			/* connect to the new database */
			$workflowDB = $GLOBALS['phpgw']->ADOdb;
			if ($workflowDB->connect($workflowHostInfo['host'].":".$workflowHostInfo['port'], $workflowHostInfo['user'], $workflowHostInfo['password'], 'workflow'))
			{
				/* create the new tables and add the referencial constraints */
				$workflowDB->query('CREATE TABLE cargo (cargo_id serial NOT NULL, descricao character varying(150), organizacao_id int4)');
				$workflowDB->query('ALTER TABLE ONLY cargo ADD CONSTRAINT cargo_pkey PRIMARY KEY (cargo_id)');
				$workflowDB->query('ALTER TABLE ONLY cargo ADD CONSTRAINT "$1" FOREIGN KEY (organizacao_id) REFERENCES organizacao (organizacao_id)');
				$workflowDB->query('CREATE TABLE funcionario_categoria (funcionario_categoria_id serial NOT NULL, descricao character varying(150), organizacao_id int4)');
				$workflowDB->query('ALTER TABLE ONLY funcionario_categoria ADD CONSTRAINT funcionario_categoria_pkey PRIMARY KEY (funcionario_categoria_id)');
				$workflowDB->query('ALTER TABLE ONLY funcionario_categoria ADD CONSTRAINT "$1" FOREIGN KEY (organizacao_id) REFERENCES organizacao (organizacao_id)');

				/* add a new column to the organizacao table (orgchart) */
				$workflowDB->query('ALTER TABLE funcionario ADD COLUMN cargo_id int4');
				$workflowDB->query('ALTER TABLE funcionario ADD COLUMN nivel int2');
				$workflowDB->query('ALTER TABLE funcionario ADD COLUMN funcionario_categoria_id int4');
				$workflowDB->query('ALTER TABLE ONLY funcionario ADD CONSTRAINT "$5" FOREIGN KEY (cargo_id) REFERENCES cargo (cargo_id)');
				$workflowDB->query('ALTER TABLE ONLY funcionario ADD CONSTRAINT "$6" FOREIGN KEY (funcionario_categoria_id) REFERENCES funcionario_categoria (funcionario_categoria_id)');

				/* set the permissions to the database objects */
				$dbObjects = array('cargo', 'cargo_cargo_id_seq', 'funcionario_categoria', 'funcionario_categoria_funcionario_categoria_id_seq');
				foreach ($dbObjects as $dbObject)
				{
					$workflowDB->query("GRANT ALL ON TABLE $dbObject TO admin_workflow");
					$workflowDB->query("GRANT ALL ON TABLE $dbObject TO postgres");
					$workflowDB->query("GRANT SELECT ON TABLE $dbObject 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.6.01.000';
		return $GLOBALS['setup_info']['workflow']['currentver'];
	}

	$test[] = '1.6.01.000';
	function workflow_upgrade1_6_01_000()
	{
		if (!$GLOBALS['phpgw_setup']->oProc->m_bDeltaOnly)
		{
			/* tries to load the 'files_dir' value (usually '/home/expressolivre') */
			$GLOBALS['phpgw']->ADOdb->SetFetchMode(ADODB_FETCH_ASSOC);
			$result = $GLOBALS['phpgw']->ADOdb->query('SELECT config_value FROM phpgw_config WHERE config_app = ? AND config_name = ?', array('phpgwapi', 'files_dir'));
			if (!(empty($result)))
			{
				$res = $result->fetchRow();

				/* initialize some variables */
				$resourcesBase = $res['config_value'];
				$oldResourcesDir = PHPGW_SERVER_ROOT . '/workflow/resources';
				$success = true;

				/* if the resources dir exists, move it's subdirectories */
				if (is_dir($oldResourcesDir))
				{
					$success = false;
					$handle = opendir($oldResourcesDir);
					if ($handle)
					{
						$success = true;
						$OKToDeleteDir = true;

						/* move all subdirectories (except the '.' and '..' for oubvious reasons) to their new place */
						while (false !== ($processDir = readdir($handle)))
						{
							if (($processDir == '.') || ($processDir == '..'))
								continue;

							/* check if we are dealing with a directory */
							if (!is_dir($oldResourcesDir . '/' . $processDir))
							{
								$OKToDeleteDir = false;
								continue;
							}

							/* special treatment for the workflow directory */
							if ($processDir == 'workflow')
							{
								$success = $success && @rename($oldResourcesDir . '/' . $processDir, $resourcesBase . '/workflow/workflow');
								continue;
							}

							/* if the destination directory exists, move it */
							$newProcessDir = $resourcesBase . '/workflow/' . $processDir;
							if (is_dir($newProcessDir))
								$success = $success && @rename($oldResourcesDir . '/' . $processDir, $newProcessDir . '/resources');
							else
								$OKToDeleteDir = false;
						}

						/* close the handler */
						closedir($handle);

						/* if the directory is empty, remove it */
						if ($OKToDeleteDir && $success)
							if (!@rmdir($oldResourcesDir))
								echo '<font color="red">Não foi possível remover o diretório: ' . $oldResourcesDir . '<br/> Tente removê-lo manualmente.</font>';
					}
				}

				/* in case of an unsuccessful attemp to move a directory, warn the system admin */
				if (!$success)
					echo '<font color="red">Pelo menos um diretório não pode ser movido. Você precisa mover, manualmente, os diretórios de resources dos processos. Exemplo: <br/><strong>' . $oldResourcesDir . '/PROCESSO_XX</strong> para <strong>' . $resourcesBase . '/workflow/PROCESSO_XX/resources</strong></font>';
			}

		}

		/* updates the current version */
		$GLOBALS['setup_info']['workflow']['currentver'] = '1.6.02.000';
		return $GLOBALS['setup_info']['workflow']['currentver'];
	}

	$test[] = '1.6.02.000';
	function workflow_upgrade1_6_02_000()
	{
		$GLOBALS['phpgw_setup']->oProc->createTable('egw_wf_user_cache',
			array(
				'fd' => array(
					'uidnumber'			=> array('type' => 'int', 'precision' => '4', 'nullable' => False),
					'uid'				=> array('type' => 'varchar', 'precision' => '30', 'nullable' => True),
					'cn'				=> array('type' => 'varchar', 'precision' => '100', 'nullable' => True),
					'givenname'			=> array('type' => 'varchar', 'precision' => '30', 'nullable' => True),
					'sn'				=> array('type' => 'varchar', 'precision' => '70', 'nullable' => True),
					'mail'				=> array('type' => 'varchar', 'precision' => '80', 'nullable' => True),
					'accountstatus'		=> array('type' => 'varchar', 'precision' => '80', 'nullable' => True),
					'dn'				=> array('type' => 'varchar', 'precision' => '150', 'nullable' => True),
					'employeenumber'	=> array('type' => 'int', 'precision' => '4', 'nullable' => True),
					'cpf'				=> array('type' => 'varchar', 'precision' => '15', 'nullable' => True),
					'telephonenumber'	=> array('type' => 'varchar', 'precision' => '50', 'nullable' => True),
					'last_update'		=> array('type' => 'timestamp without time zone default now()', 'nullable' => True)
				),
				'pk' => array('uidnumber'),
				'fk' => array(),
				'ix' => array('mail', 'employeenumber', 'cpf'),
				'uc' => array()
			)
		);

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

	$test[] = '1.6.03.000';
	function workflow_upgrade1_6_03_000()
	{
		if (!$GLOBALS['phpgw_setup']->oProc->m_bDeltaOnly)
		{
			$workflowHostInfo = extractDatabaseParameters();

			/* connect to the new database */
			$workflowDB = $GLOBALS['phpgw']->ADOdb;
			if ($workflowDB->connect($workflowHostInfo['host'].":".$workflowHostInfo['port'], $workflowHostInfo['user'], $workflowHostInfo['password'], 'workflow'))
			{
				$result = $workflowDB->query("SELECT 1 FROM pg_tables WHERE (schemaname = 'public') AND (tablename = 'funcionario_status')");
				$row = array();
				if (!empty($result))
					$row = $result->fetchRow();

				/* only some instalations are missing this table */
				if ($row === false)
				{
					$workflowDB->query('CREATE TABLE funcionario_status (funcionario_status_id serial NOT NULL, descricao character varying(50) NOT NULL, exibir character varying(1) NOT NULL)');
					$workflowDB->query('ALTER TABLE ONLY funcionario_status ADD CONSTRAINT funcionario_status_pkey PRIMARY KEY (funcionario_status_id)');
					$workflowDB->query('ALTER TABLE ONLY funcionario ADD CONSTRAINT "$4" FOREIGN KEY (funcionario_status_id) REFERENCES funcionario_status(funcionario_status_id)');

					$dbObjects = array('funcionario_status', 'funcionario_status_funcionario_status_id_seq');
					foreach ($dbObjects as $dbObject)
					{
						$workflowDB->query("GRANT ALL ON TABLE $dbObject TO admin_workflow");
						$workflowDB->query("GRANT ALL ON TABLE $dbObject TO postgres");
						$workflowDB->query("GRANT SELECT ON TABLE $dbObject 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.6.03.001';
		return $GLOBALS['setup_info']['workflow']['currentver'];
	}

	$test[] = '1.6.03.001';
	function workflow_upgrade1_6_03_001()
	{
		$GLOBALS['phpgw_setup']->oProc->createTable('egw_wf_jobs',
			array(
				'fd' => array(
					'job_id'			=> array('type' => 'auto', 'precision' => '4', 'nullable' => False),
					'wf_process_id'		=> array('type' => 'int', 'precision' => '4', 'nullable' => False),
					'name'				=> array('type' => 'varchar', 'precision' => '100', 'nullable' => False),
					'description'		=> array('type' => 'text', 'nullable' => True),
					'time_start'		=> array('type' => 'timestamp without time zone', 'nullable' => False),
					'interval_value'	=> array('type' => 'int', 'precision' => '4', 'nullable' => True),
					'interval_unity'	=> array('type' => 'int', 'precision' => '4', 'nullable' => True),
					'date_type'			=> array('type' => 'int', 'precision' => '4', 'nullable' => True),
					'week_days'			=> array('type' => 'int', 'precision' => '4', 'nullable' => True, 'default' => 0),
					'month_offset'		=> array('type' => 'int', 'precision' => '4', 'nullable' => True, 'default' => 0),
					'active'			=> array('type' => 'bool', 'nullable' => True)
				),
				'pk' => array('job_id'),
				'fk' => array(),
				'ix' => array(),
				'uc' => array()
			)
		);

		$GLOBALS['phpgw_setup']->oProc->createTable('egw_wf_job_logs',
			array(
				'fd' => array(
					'job_id'		=> array('type' => 'int', 'precision' => '4', 'nullable' => False),
					'date_time'		=> array('type' => 'timestamp without time zone', 'nullable' => False),
					'result'		=> array('type' => 'blob', 'nullable' => True),
					'status'		=> array('type' => 'int', 'precision' => '4', 'nullable' => True)
				),
				'pk' => array('job_id', 'date_time'),
				'fk' => array(),
				'ix' => array(),
				'uc' => array()
			)
		);

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

	$test[] = '1.7.00.000';
	function workflow_upgrade1_7_00_000()
	{
		#updating the current version
		$GLOBALS['setup_info']['workflow']['currentver'] = '1.8.00.000';
		return $GLOBALS['setup_info']['workflow']['currentver'];
	}

	$test[] = '1.8.00.000';
	function workflow_upgrade1_8_00_000()
	{
		#updating the current version
		$GLOBALS['setup_info']['workflow']['currentver'] = '1.8.01.000';
		return $GLOBALS['setup_info']['workflow']['currentver'];
	}

	$test[] = '1.8.01.000';
	function workflow_upgrade1_8_01_000()
	{
		if (!$GLOBALS['phpgw_setup']->oProc->m_bDeltaOnly)
		{
			$GLOBALS['phpgw']->ADOdb->query('CREATE INDEX egw_wf_instances_wf_name_idx ON egw_wf_instances(UPPER(wf_name))');
			$GLOBALS['phpgw']->ADOdb->query('CREATE INDEX egw_wf_instances_wf_p_id_idx ON egw_wf_instances(wf_p_id)');
			$GLOBALS['phpgw']->ADOdb->query('DELETE FROM phpgw_config where config_app = \'workflow\' and config_name in (\'database_user\', \'database_password\')');

			$workflowHostInfo = extractDatabaseParameters();

			/* connect to the new database */
			$workflowDB = $GLOBALS['phpgw']->ADOdb;
			if ($workflowDB->connect($workflowHostInfo['host'].":".$workflowHostInfo['port'], $workflowHostInfo['user'], $workflowHostInfo['password'], 'workflow'))
			{
				/* add a new column to the area organization (orgchart) */
				$workflowDB->query('ALTER TABLE organizacao ADD COLUMN ativa character varying(1)');
				$workflowDB->query('UPDATE organizacao SET ativa = \'S\'');
				$workflowDB->query('ALTER TABLE organizacao ALTER ativa SET NOT NULL');
			}

			/* 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.02.000';
		return $GLOBALS['setup_info']['workflow']['currentver'];
	}

	$test[] = '1.8.02.000';
	function workflow_upgrade1_8_02_000()
	{
		if (!$GLOBALS['phpgw_setup']->oProc->m_bDeltaOnly)
		{
			$workflowHostInfo = extractDatabaseParameters();

			/* connect to the new database */
			$workflowDB = $GLOBALS['phpgw']->ADOdb;
			if ($workflowDB->connect($workflowHostInfo['host'].":".$workflowHostInfo['port'], $workflowHostInfo['user'], $workflowHostInfo['password'], 'workflow'))
			{
				/* gather some information */
				$workflowDB->setFetchMode(ADODB_FETCH_ASSOC);
				$organizations = $workflowDB->GetAll('SELECT organizacao_id FROM organizacao');
				$employeeStatus = $workflowDB->GetAll('SELECT funcionario_status_id, descricao, exibir FROM funcionario_status');

				/* add the new column */
				$workflowDB->query('ALTER TABLE funcionario_status ADD COLUMN organizacao_id INTEGER');
				if (count($organizations) > 0)
				{
					/* run update for the first organization */
					$workflowDB->query('UPDATE funcionario_status SET organizacao_id = ?', array($organizations[0]['organizacao_id']));

					/* replicate the 'funcionario_status' table information for other organizations */
					for ($i = 1; $i < count($organizations); $i++)
					{
						$organizationID = $organizations[$i]['organizacao_id'];
						foreach ($employeeStatus as $es)
						{
							$values = array(
								$es['descricao'],
								$es['exibir'],
								$organizationID
							);

							/* insert the new status */
							$workflowDB->query('INSERT INTO funcionario_status (descricao, exibir, organizacao_id) VALUES (?, ?, ?)', $values);

							$values = array(
								$organizationID,
								$es['funcionario_status_id']
							);

							/* update the existing employees */
							$workflowDB->query('UPDATE funcionario SET funcionario_status_id = (SELECT MAX(funcionario_status_id) FROM funcionario_status) WHERE (organizacao_id = ?) AND (funcionario_status_id = ?)', $values);
						}
					}
				}
				/* add the new constraint */
				$workflowDB->query('ALTER TABLE ONLY funcionario_status ADD CONSTRAINT "$1" FOREIGN KEY (organizacao_id) REFERENCES organizacao(organizacao_id)');
			}

			/* 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.03.000';
		return $GLOBALS['setup_info']['workflow']['currentver'];
	}

	$test[] = '1.8.03.000';
	function workflow_upgrade1_8_03_000()
	{
		/* setting new default attributes for workflow Logger  */
		if (!$GLOBALS['phpgw_setup']->oProc->m_bDeltaOnly)
		{
			$GLOBALS['phpgw']->ADOdb->query('INSERT INTO phpgw_config (config_app, config_name, config_value) VALUES (\'workflow\', \'log_type_file\', \'True\')');
			$GLOBALS['phpgw']->ADOdb->query('INSERT INTO phpgw_config (config_app, config_name, config_value) VALUES (\'workflow\', \'log_type_firebug\', \'True\')');
			$GLOBALS['phpgw']->ADOdb->query('INSERT INTO phpgw_config (config_app, config_name, config_value) VALUES (\'workflow\', \'log_level\', \'7\')');
		}

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

	$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.1.000';
		return $GLOBALS['setup_info']['workflow']['currentver'];
	}
	$test[] = '2.1.000';
	function workflow_upgrade2_1_000()
	{
		if (!$GLOBALS['phpgw_setup']->oProc->m_bDeltaOnly)
		{
			$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']);
		}

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