CREATE OR REPLACE FUNCTION nico.create_xml_file(int4, int4, int4, text, text)
  RETURNS int4 AS
$BODY$
        use strict;
        ###############################
	#	CREATE_XML_FILE	#
	###############################
	my $OEID = 'OID';
	my $theClob;
	# open xml file result of export
        select FILE;

	open(FILE, '>>'.$_[4].'\\'.$_[3]);
	my @params=($_[0], $_[1], $_[2], 0);
	GET_XML_FRAG(\@params, \$theClob);
	WRITE_CLOB_IN_FILE(\$theClob);
	close FILE;

	
	###############################
	#        WRITE_CLOB_IN_FILE         #
	###############################
	sub WRITE_CLOB_IN_FILE {
                my $theClob_ref = shift;
		# print in file
		print $$theClob_ref;
		# empty the clob
		$$theClob_ref = '';
	}
        
	###############################
	#        GET_XML_FRAG         #
	###############################
	sub GET_XML_FRAG {
                my $params_ref = shift;
		my $theClob_ref = shift;

		if (($$params_ref[1]%100000)==0){
			# write clob in file
			WRITE_CLOB_IN_FILE($theClob_ref);
			# trace of time
			my $time_f = $$params_ref[1]. ' ---> '.localtime(time);
			elog NOTICE, $time_f;
		}

		my $attrQuery = 'select * from nico.XDB_ATTR a1
				where a1.doc_id = '.$$params_ref[0].'
				and a1.ele_id = '.$$params_ref[1].'
				and a1.isremoved = 0
				and a1.evolution = (select max(evolution) from nico.XDB_ATTR a2
							where a2.doc_id='.$$params_ref[0].'
							and a2.ele_id='.$$params_ref[1].'
							and a2.evolution<='.$$params_ref[2].'
							and a2.attr_id=a1.attr_id)';

		my $attrTab = spi_exec_query($attrQuery);

		my $childQuery = 'select * from nico.XDB_CHILD c1
				where c1.doc_id = '.$$params_ref[0].'
				and c1.ele_id = '.$$params_ref[1].'
				and c1.isremoved = 0
				and c1.evolution = (select max(evolution)
							from nico.XDB_CHILD c2
							where c2.doc_id='.$$params_ref[0].'
							and c2.ele_id='.$$params_ref[1].'
							and c2.evolution<='.$$params_ref[2].'
							and c2.child_id=c1.child_id
							and c2.child_class=c1.child_class) ORDER BY c1.evolution, c1.indx';
		my $childTab = spi_exec_query($childQuery);

		elog NOTICE, 'GET_XML_FRAG : -----> docId -> '. $$params_ref[0] .' | eleId -> '. $$params_ref[1].' | evo -> '.$$params_ref[2].' | indx -> '.$$params_ref[3].' | nbFils -> '.$childTab->{processed};
		
		# Tag
		my $tagQuery = 'select tag from nico.XDB_ELE
				where doc_id='.$$params_ref[0].'
				and ele_id='.$$params_ref[1].'
				and isremoved=0
				and evolution = (select MAX(evolution)
					from nico.XDB_ELE
					where doc_id='.$$params_ref[0].'
					and ele_id='.$$params_ref[1].'
					and evolution<='.$$params_ref[2].')';
		my $tagTab = spi_exec_query($tagQuery);
	
		# Formatting element
		$$theClob_ref .= '<'.$tagTab->{rows}[0]->{tag};
		
		#Attributes
		foreach my $rn (0 .. $attrTab->{processed} - 1) {
			$$theClob_ref .= ' '.$attrTab->{rows}[$rn]->{name}.'="'.$attrTab->{rows}[$rn]->{valu}.'"';
		}
		
		# Specific root element processing
		if ($$params_ref[1]==1){
			$$theClob_ref.= ' AID="'.$OEID.'"';
		}
		$$theClob_ref .= ' '.$OEID.'="'.$$params_ref[1].'">';
		
		#ordering children
                if ($childTab->{processed}>1){
		        SORT_CHILDREN($childTab);
                }

		# looking for children
		foreach my $i (0 .. $childTab->{processed} - 1) {
			# obtain child class
			my $childClass = $childTab->{rows}[$i]->{child_class};

			if ($childClass==0) {
				$$params_ref[1] = $childTab->{rows}[$i]->{child_id};
				$$params_ref[3] = $childTab->{rows}[$i]->{indx};
				GET_XML_FRAG($params_ref, $theClob_ref);
			}
			elsif ($childClass==2) {
				my $valueQuery = 'select value from nico.XDB_STR s1
					where s1.doc_id = '.$$params_ref[0].'
					and s1.cdata_id = '.$childTab->{rows}[$i]->{child_id}.'
					and s1.isremoved = 0
					and s1.evolution = (select MAX(evolution)
						from nico.XDB_STR s2
							where s2.doc_id='.$$params_ref[0].'
							and s2.cdata_id = '.$childTab->{rows}[$i]->{child_id}.'
							and s2.evolution<='.$$params_ref[2].')';
				my $valueTab = spi_exec_query($valueQuery);

				$$theClob_ref .= $valueTab->{rows}[0]->{value};
			}
			elsif ($childClass==3) {
				my $valueQuery = 'select value from nico.XDB_TEXT t1
					where t1.doc_id = '.$$params_ref[0].'
					and t1.cdata_id = '.$childTab->{rows}[$i]->{child_id}.'
					and t1.isremoved = 0
					and t1.evolution = (select MAX(evolution) 
						from nico.XDB_TEXT t2
							where t2.doc_id='.$$params_ref[0].'
							and t2.cdata_id = '.$childTab->{rows}[$i]->{child_id}.'
							and t2.evolution<='.$$params_ref[2].')';
				my $valueTab = spi_exec_query($valueQuery);

				$$theClob_ref .= $valueTab->{rows}[0]->{value};
			}
			elsif ($childClass==1) {
				my $valueQuery = 'select target, value from nico.XDB_PI p1
							where p1.doc_id = '.$$params_ref[0].'
							and p1.pi_id = '.$childTab->{rows}[$i]->{child_id}.'
							and p1.isremoved = 0
							and p1.evolution = (select MAX(evolution)
								from nico.XDB_PI p2
									where p2.doc_id='.$$params_ref[0].'
									and p2.pi_id = '.$childTab->{rows}[$i]->{child_id}.'
									and p2.evolution<='.$$params_ref[2].')';

				my $valueTab = spi_exec_query($valueQuery);
				$$theClob_ref .= '<?'.$valueTab->{rows}[0]->{target}.' '.$valueTab->{rows}[0]->{value}.'?>';
			}
		}

		$$theClob_ref .= '</'. $tagTab->{rows}[0]->{tag} .'>';
	}


	###############################
	#	SORT_CHILDREN	#
	###############################
	sub SORT_CHILDREN {
                my ($children_ref) = @_;
		my $nbChildren = $children_ref->{processed} - 1;
		
		foreach my $i (0 .. $nbChildren) {
			my $tmp_row = @{$children_ref->{rows}}[$i];
			my $indx = $tmp_row->{indx};
			if ($indx < $i) {
				# on déplace l'élément vers sa place par rapport à son index
				splice(@{$children_ref->{rows}}, $indx-1, 0, splice(@{$children_ref->{rows}}, $i, 1));
			}
		}
	}
$BODY$
  LANGUAGE 'plperl' VOLATILE;