source data_lbf_referenceformacode
{
	# data source type. mandatory, no default value
	# known types are 'mysql', 'pgsql', 'xmlpipe', 'xmlpipe2'
	type                   = mysql

	#####################################################################
	## SQL settings (for 'mysql' and 'pgsql' types)
	#####################################################################

	# some straightforward parameters for SQL source types
	sql_host               = localhost
	sql_user               = root
	sql_pass               = 
	sql_db                 = labonneformation
	sql_port               = 3306  # optional, default is 3306
	mysql_connect_flags    = 32 # enable compression

	sql_query              = SELECT @newid:=@newid+1 AS id,REPLACE(FUNC_LOWERNOACC(r.label),'-',' ') AS label,r.extradata AS formacode,0 AS ad_id,r.id AS reference_id \
	                         FROM (SELECT @newid:=0) AS newid,reference r \
	                         WHERE r.type=5 AND r.status=1

	sql_attr_uint          = ad_id
	sql_attr_uint          = reference_id
}

index lbfreferenceformacode
{
	source                 = data_lbf_referenceformacode
	path                   = /home/sphinx/indexes/lbf/referenceformacode
	docinfo                = extern
	min_word_len           = 1
	min_prefix_len         = 1

	charset_table          = U+00AB->e,0..9, A..Z->a..z, _, a..z, \
	                         U+C0->a, U+C1->a, U+C2->a, U+C3->a, U+C4->a, U+C5->a, U+C6->a, \
	                         U+E0->a, U+E1->a, U+E2->a, U+E3->a, U+E4->a, U+E5->a, U+E6->a, U+C7->c,U+E7->c,\
	                         U+C8->e, U+C9->e, U+CA->e, U+CB->e, U+E8->e, U+E9->e, U+EA->e, U+EB->e, \
	                         U+CC->i, U+CD->i, U+CE->i, U+CF->i, U+EC->i, U+ED->i, U+EE->i, U+EF->i, \
	                         U+00C8->e, U+00C9->e, U+00CA->e, U+00CB->e, U+00E8->e, U+00E9->e, U+00EA->e, \
	                         U+00EB->e, U+0112->e, U+0113->e, U+0114->e, U+0115->e, U+0116->e, U+0117->e, \
	                         U+0118->e, U+0119->e, U+011A->e, U+011B->e, U+018E->e, U+0190->e, U+01DD->e, \
	                         U+0204->e, U+0205->e, U+0206->e, U+0207->e, U+0228->e, U+0229->e, U+0246->e, \
	                         U+0247->e, U+0258->e, U+025B->e, U+025C->e, U+025D->e, U+025E->e, U+029A->e, \
	                         U+1D07->e, U+1D08->e, U+1D31->e, U+1D32->e, U+1D49->e, U+1D4B->e, U+1D4C->e, \
	                         U+1D92->e, U+1D93->e, U+1D94->e, U+1D9F->e, U+1E14->e, U+1E15->e, U+1E16->e, \
	                         U+1E17->e, U+1E18->e, U+1E19->e, U+1E1A->e, U+1E1B->e, U+1E1C->e, U+1E1D->e, \
	                         U+1EB8->e, U+1EB9->e, U+1EBA->e, U+1EBB->e, U+1EBC->e, U+1EBD->e, U+1EBE->e, \
	                         U+1EBF->e, U+1EC0->e, U+1EC1->e, U+1EC2->e, U+1EC3->e, U+1EC4->e, U+1EC5->e, \
	                         U+1EC6->e, U+1EC7->e, U+2091->e,\
	                         U+00D2->o, U+00D3->o, U+00D4->o, U+00D5->o, U+00D6->o, U+00D8->o, U+00F2->o, U+00F3->o, \
	                         U+00F4->o, U+00F5->o, U+00F6->o, U+00F8->o, U+01030F->o, U+014C->o, U+014D->o, U+014E->o, \
	                         U+014F->o, U+0150->o, U+0151->o, U+0186->o, U+019F->o, U+01A0->o, U+01A1->o, U+01D1->o, \
	                         U+01D2->o, U+01EA->o, U+01EB->o, U+01EC->o, U+01ED->o, U+01FE->o, U+01FF->o, U+020C->o, \
	                         U+020D->o, U+020E->o, U+020F->o, U+022A->o, U+022B->o, U+022C->o, U+022D->o, U+022E->o, \
	                         U+022F->o, U+0230->o, U+0231->o, U+0254->o, U+0275->o, U+043E->o, U+04E6->o, U+04E7->o, \
	                         U+04E8->o, U+04E9->o, U+04EA->o, U+04EB->o, U+1D0F->o, U+1D10->o, U+1D11->o, U+1D12->o, \
	                         U+1D13->o, U+1D16->o, U+1D17->o, U+1D3C->o, U+1D52->o, U+1D53->o, U+1D54->o, U+1D55->o, \
	                         U+1D97->o, U+1DB1->o, U+1E4C->o, U+1E4D->o, U+1E4E->o, U+1E4F->o, U+1E50->o, U+1E51->o, \
	                         U+1E52->o, U+1E53->o, U+1ECC->o, U+1ECD->o, U+1ECE->o, U+1ECF->o, U+1ED0->o, U+1ED1->o, \
	                         U+1ED2->o, U+1ED3->o, U+1ED4->o, U+1ED5->o, U+1ED6->o, U+1ED7->o, U+1ED8->o, U+1ED9->o, \
	                         U+1EDA->o, U+1EDB->o, U+1EDC->o, U+1EDD->o, U+1EDE->o, U+1EDF->o, U+1EE0->o, U+1EE1->o, \
	                         U+1EE2->o, U+1EE3->o, U+2092->o, U+2C9E->o, U+2C9F->o
}

source data_lbf_reference
{
	# data source type. mandatory, no default value
	# known types are 'mysql', 'pgsql', 'xmlpipe', 'xmlpipe2'
	type                   = mysql

	#####################################################################
	## SQL settings (for 'mysql' and 'pgsql' types)
	#####################################################################

	# some straightforward parameters for SQL source types
	sql_host               = localhost
	sql_user               = root
	sql_pass               = 
	sql_db                 = labonneformation
	sql_port               = 3306  # optional, default is 3306
	mysql_connect_flags    = 32 # enable compression

	#sql_query              = SELECT r.id,r.type,FUNC_LOWERNOACC(r.label) AS label,rl.level,REPLACE(rl.path,'/','_') AS path,rl.id AS locationid \
	#                         FROM reference r \
	#                         INNER JOIN reference rl ON rl.status=1 AND rl.type=6 AND rl.path=r.path \
	#                         WHERE r.status=1 AND r.type IN (6) AND r.path LIKE '/1/1/%' AND r.path!='/1/1/6/1/' AND r.level>2
	sql_query              = SELECT r.id, \
	                                r.type, \
	                                CONCAT(FUNC_LOWERNOACC(IF(r.labelsearch IS NOT NULL,r.labelsearch,r.label)),' ',FUNC_EXTRADATAALL('zc',r.extradata,'',null)) AS label, \
	                                r.level, \
	                                REPLACE(r.path,'/','_') AS path, \
	                                r.id AS locationid, \
	                         FUNC_EXTRADATA('in',r.extradata,'') AS codeinsee, \
	                         FUNC_EXTRADATA('rm',r.extradata,'') AS rome, \
	                         FUNC_EXTRADATA('ba',r.extradata,'') AS bassininsee, \
	                         FUNC_EXTRADATA('fm',r.extradata,'') AS formacode, \
	                         FUNC_EXTRADATA('dn',r.extradata,'') AS departementinsee, \
	                         TRIM(SUBSTRING(FUNC_EXTRADATA('fm',r.extradata,''),1,3)) AS racineformacode \
	                         FROM reference r \
	                         WHERE r.status=1

	sql_attr_uint          = type
	sql_attr_uint          = level
	sql_attr_uint          = locationid
	#sql_attr_string        = codeinsee
}

index lbfreference
{
	source                 = data_lbf_reference
	path                   = /home/sphinx/indexes/lbf/reference
	docinfo                = extern
	#    charset_type           = sbcs
	#    enable_star            = 1
	min_word_len           = 1
	min_prefix_len         = 1
}

source data_lbf_ad
{
	# data source type. mandatory, no default value
	# known types are 'mysql', 'pgsql', 'xmlpipe', 'xmlpipe2'
	type                   = mysql

	#####################################################################
	## SQL settings (for 'mysql' and 'pgsql' types)
	#####################################################################

	#alter table sphad connection='sphinx://localhost:3312/lbfad';

	# some straightforward parameters for SQL source types
	sql_host               = localhost
	sql_user               = root
	sql_pass               = 
	sql_db                 = labonneformation
	sql_port               = 3306  # optional, default is 3306
	mysql_connect_flags    = 32 # enable compression

	sql_query_pre          = SET CHARACTER_SET_RESULTS=utf8
	sql_query_pre          = SET NAMES utf8

	sql_query              = SELECT s.id,a.id AS ad_id,a.catalogue_id,a.orga_id AS orgaid,s.id AS session_id,arb.id AS adrankid,arr.id AS adrankregionid,a.idorgaintercarif, \
	                                IF(s.beganat IS NULL,a.beganat,s.beganat) AS beganat,IF(s.endedat IS NULL,a.endedat,s.endedat) AS endedat, \
	                                REPLACE(REPLACE(a.codefinanceur,'[','_'),']','') AS codefinanceur, \
	                                a.idformintercarif,CONCAT(a.title,' ',o.name) AS title,IF(ar.followed>20,ar.cdi/IF(ar.followed>0,ar.followed,1),0) AS ratio, \
	                                IF(arb.bassinrate IS NULL,IF(arb.departementrate IS NULL,IF(arb.regionalrate IS NULL,arb.nationalrate,arb.regionalrate),arb.departementrate),arb.bassinrate) AS rate, \
	                                REPLACE(r.path,'/','_') AS locationpath, \
	                                REPLACE(r.path,'/','_') AS location, \
	                                REPLACE(r.path,'/','_') AS locationsearch, \
	                                a.romecode, \
	                                a.flags, \
	                                IF(a.flags&1,1,0) AS convention, \
	                                IF(a.flags&2,1,0) AS entreessortiespermanentes, \
	                                IF(a.flags&4,1,0) AS adistance, \
	                                IF(a.flags&8,1,0) AS diplomante, \
	                                IF(a.flags&16,1,0) AS certifiante, \
	                                IF(a.flags&32,1,0) AS contratapprentissage, \
	                                IF(a.flags&64,1,0) AS contratprofessionalisation, \
	                                IF(a.flags&128,1,0) AS rncp, \
	                                IF(a.flags&256,1,0) AS financementde, \
	                                IF((a.flags&256=0 OR a.flags&512) AND a.flags&32=0 AND a.flags&64=0,1,0) AS financementsalarie, \
	                                IF(a.flags&1024,1,0) AS financementpic, \
	                                a.formacode, \
	                                FUNC_EXTRADATA('in',r.extradata,'') AS codeinsee, \
	                                TRIM(SUBSTRING_INDEX(a.formacode,' ',1)) AS formacodeprincipal, \
	                                TRIM(SUBSTRING(SUBSTRING_INDEX(a.formacode,' ',1),1,3)) AS racineformacodeprincipal, \
	                                RADIANS(FUNC_EXTRADATA('lt',r.extradata,'')) AS lat,RADIANS(FUNC_EXTRADATA('lg',r.extradata,'')) AS lng, \
	                                IF(s.beganat IS NOT NULL,IF(IF(month(s.beganat)=0,CONCAT(DATE_FORMAT(s.beganat, '%Y'), '-01-01'),s.beganat)>NOW() OR a.flags&2,10000000,DATEDIFF(IF(month(s.beganat)=0,CONCAT(DATE_FORMAT(s.beganat, '%Y'), '-01-01'),s.beganat),NOW())),-9999999) AS proximitydate, \
	                                UNIX_TIMESTAMP(IF(month(s.beganat)=0,CONCAT(DATE_FORMAT(s.beganat, '%Y'), '-01-01'),s.beganat)) AS beganattimestamp, \
	                                IF(a.duration>0,CEIL(a.duration/7/21),IF(s.beganat IS NOT NULL AND s.endedat IS NOT NULL,TIMESTAMPDIFF(MONTH,IF(month(s.beganat)=0,CONCAT(DATE_FORMAT(s.beganat, '%Y'), '-01-01'),s.beganat),IF(month(s.endedat)=0,CONCAT(DATE_FORMAT(s.endedat, '%Y'), '-12-31'),s.endedat)),0)) AS dureeenmois, \
	                                IF(s.beganat IS NULL OR (a.flags&2)=1,0,IF(TIMESTAMPDIFF(MONTH,NOW(),IF(month(s.beganat)=0,CONCAT(DATE_FORMAT(s.beganat, '%Y'), '-01-01'),s.beganat))<0,0,TIMESTAMPDIFF(MONTH,NOW(),IF(month(s.beganat)=0,CONCAT(DATE_FORMAT(s.beganat, '%Y'), '-01-01'),s.beganat)))) AS debutenmois, \
	                                a.niveausortie, \
	                                CONCAT(a.id,r.path) AS groupby \
	                         FROM ad a \
	                         INNER JOIN `session` s ON s.ad_id=a.id AND s.status='ACTIVE' AND (s.endedat IS NULL OR s.endedat>NOW()) \
	                         INNER JOIN orga o ON o.id=a.orga_id AND o.status='ACTIVE' \
	                         INNER JOIN reference r ON r.path=s.locationpath AND r.status=1 AND r.type=6 \
	                         LEFT OUTER JOIN adrankbassin arb ON arb.status='ACTIVE' AND arb.codeinsee=FUNC_EXTRADATA('ba',r.extradata,'') AND arb.formacode=TRIM(SUBSTRING_INDEX(a.formacode,' ',1)) \
	                         LEFT OUTER JOIN adrank ar ON ar.formacode=TRIM(SUBSTRING_INDEX(a.formacode,' ',1)) \
	                         LEFT OUTER JOIN adrankdepartement ard ON ard.formacode=TRIM(SUBSTRING_INDEX(a.formacode,' ',1)) AND ard.departementpath=FUNC_SUBPATH(a.locationpath,4) AND ard.status='ACTIVE' \
	                         LEFT OUTER JOIN adrankregion arr ON arr.formacode=TRIM(SUBSTRING_INDEX(a.formacode,' ',1)) AND arr.regionpath=FUNC_SUBPATH(a.locationpath,3) AND arr.status='ACTIVE' \
	                         WHERE a.status='ACTIVE'


	sql_attr_uint          = ad_id
	sql_attr_uint          = orgaid
	sql_attr_uint          = adrankid
	sql_attr_uint          = adrankregionid
	#sql_attr_uint          = locationid
	#sql_attr_uint          = locationparentid
	sql_attr_string        = location
	sql_attr_string        = formacodeprincipal
	sql_attr_float         = lat
	sql_attr_float         = lng
	sql_attr_float         = ratio
	sql_attr_float         = rate
	sql_attr_uint          = flags
	sql_attr_uint          = convention
	sql_attr_uint          = entreessortiespermanentes
	sql_attr_uint          = adistance
	sql_attr_uint          = certifiante
	sql_attr_uint          = diplomante
	sql_attr_uint          = contratapprentissage
	sql_attr_uint          = contratprofessionalisation
	sql_attr_uint          = rncp
	sql_attr_uint          = financementde
	sql_attr_uint          = financementsalarie
	sql_attr_uint          = financementpic
	sql_attr_uint          = racineformacodeprincipal
	sql_attr_float         = proximitydate
	sql_attr_timestamp     = beganattimestamp
	sql_attr_uint          = dureeenmois
	sql_attr_uint          = debutenmois
	sql_attr_uint          = niveausortie
	sql_attr_string        = groupby
}


index lbfad
{
	source                 = data_lbf_ad
	path                   = /home/sphinx/indexes/lbf/ad
	docinfo                = extern
	min_word_len           = 1
	min_prefix_len         = 1
	#enable_star           = true
	#morphology             = libstemmer_fr
	#expand_keywords        = 1
	min_stemming_len       = 3
	index_exact_words      = 1
	#wordforms              = /etc/sphinxsearch/wordforms.txt

	charset_table          = U+00AB->e,0..9, A..Z->a..z, _, a..z, \
	                         U+C0->a, U+C1->a, U+C2->a, U+C3->a, U+C4->a, U+C5->a, U+C6->a, \
	                         U+E0->a, U+E1->a, U+E2->a, U+E3->a, U+E4->a, U+E5->a, U+E6->a, U+C7->c,U+E7->c,\
	                         U+C8->e, U+C9->e, U+CA->e, U+CB->e, U+E8->e, U+E9->e, U+EA->e, U+EB->e, \
	                         U+CC->i, U+CD->i, U+CE->i, U+CF->i, U+EC->i, U+ED->i, U+EE->i, U+EF->i, \
	                         U+00C8->e, U+00C9->e, U+00CA->e, U+00CB->e, U+00E8->e, U+00E9->e, U+00EA->e, \
	                         U+00EB->e, U+0112->e, U+0113->e, U+0114->e, U+0115->e, U+0116->e, U+0117->e, \
	                         U+0118->e, U+0119->e, U+011A->e, U+011B->e, U+018E->e, U+0190->e, U+01DD->e, \
	                         U+0204->e, U+0205->e, U+0206->e, U+0207->e, U+0228->e, U+0229->e, U+0246->e, \
	                         U+0247->e, U+0258->e, U+025B->e, U+025C->e, U+025D->e, U+025E->e, U+029A->e, \
	                         U+1D07->e, U+1D08->e, U+1D31->e, U+1D32->e, U+1D49->e, U+1D4B->e, U+1D4C->e, \
	                         U+1D92->e, U+1D93->e, U+1D94->e, U+1D9F->e, U+1E14->e, U+1E15->e, U+1E16->e, \
	                         U+1E17->e, U+1E18->e, U+1E19->e, U+1E1A->e, U+1E1B->e, U+1E1C->e, U+1E1D->e, \
	                         U+1EB8->e, U+1EB9->e, U+1EBA->e, U+1EBB->e, U+1EBC->e, U+1EBD->e, U+1EBE->e, \
	                         U+1EBF->e, U+1EC0->e, U+1EC1->e, U+1EC2->e, U+1EC3->e, U+1EC4->e, U+1EC5->e, \
	                         U+1EC6->e, U+1EC7->e, U+2091->e,\
	                         U+00D2->o, U+00D3->o, U+00D4->o, U+00D5->o, U+00D6->o, U+00D8->o, U+00F2->o, U+00F3->o, \
	                         U+00F4->o, U+00F5->o, U+00F6->o, U+00F8->o, U+01030F->o, U+014C->o, U+014D->o, U+014E->o, \
	                         U+014F->o, U+0150->o, U+0151->o, U+0186->o, U+019F->o, U+01A0->o, U+01A1->o, U+01D1->o, \
	                         U+01D2->o, U+01EA->o, U+01EB->o, U+01EC->o, U+01ED->o, U+01FE->o, U+01FF->o, U+020C->o, \
	                         U+020D->o, U+020E->o, U+020F->o, U+022A->o, U+022B->o, U+022C->o, U+022D->o, U+022E->o, \
	                         U+022F->o, U+0230->o, U+0231->o, U+0254->o, U+0275->o, U+043E->o, U+04E6->o, U+04E7->o, \
	                         U+04E8->o, U+04E9->o, U+04EA->o, U+04EB->o, U+1D0F->o, U+1D10->o, U+1D11->o, U+1D12->o, \
	                         U+1D13->o, U+1D16->o, U+1D17->o, U+1D3C->o, U+1D52->o, U+1D53->o, U+1D54->o, U+1D55->o, \
	                         U+1D97->o, U+1DB1->o, U+1E4C->o, U+1E4D->o, U+1E4E->o, U+1E4F->o, U+1E50->o, U+1E51->o, \
	                         U+1E52->o, U+1E53->o, U+1ECC->o, U+1ECD->o, U+1ECE->o, U+1ECF->o, U+1ED0->o, U+1ED1->o, \
	                         U+1ED2->o, U+1ED3->o, U+1ED4->o, U+1ED5->o, U+1ED6->o, U+1ED7->o, U+1ED8->o, U+1ED9->o, \
	                         U+1EDA->o, U+1EDB->o, U+1EDC->o, U+1EDD->o, U+1EDE->o, U+1EDF->o, U+1EE0->o, U+1EE1->o, \
	                         U+1EE2->o, U+1EE3->o, U+2092->o, U+2C9E->o, U+2C9F->o
}

#############################################################################
## indexer settings
#############################################################################

indexer
{
	# memory limit, in bytes, kiloytes (16384K) or megabytes (256M)
	# optional, default is 32M, max is 2047M, recommended is 256M to 1024M
	mem_limit              = 256M

	# maximum IO calls per second (for I/O throttling)
	# optional, default is 0 (unlimited)
	#
	# max_iops             = 40

	# maximum IO call size, bytes (for I/O throttling)
	# optional, default is 0 (unlimited)
	#
	# max_iosize           = 1048576
}

#############################################################################
## searchd settings
#############################################################################

searchd
{
	# IP address to bind on
	# optional, default is 0.0.0.0 (ie. listen on all interfaces)
	#
	listen                 = 127.0.0.1:3312

	# log file, searchd run info is logged here
	# optional, default is 'searchd.log'
	log                    = /home/sphinx/log/searchd.log

	# query log file, all search queries are logged here
	# optional, default is empty (do not log queries)
	query_log              = /home/sphinx/log/query.log

	# client read timeout, seconds
	# optional, default is 5
	read_timeout           = 5

	# maximum amount of children to fork (concurrent searches to run)
	# optional, default is 0 (unlimited)
	max_children           = 30

	# PID file, searchd process ID file name
	# mandatory
	pid_file               = /var/run/sphinxsearch/searchd.pid

	# max amount of matches the daemon ever keeps in RAM, per-index
	# WARNING, THERE'S ALSO PER-QUERY LIMIT, SEE SetLimits() API CALL
	# default is 1000 (just like Google)
	#    max_matches            = 200000

	# seamless rotate, prevents rotate stalls if precaching huge datasets
	# optional, default is 1
	seamless_rotate        = 1

	# whether to forcibly preopen all indexes on startup
	# optional, default is 0 (do not preopen)
	# preopen_indexes        = 0

	# whether to unlink .old index copies on succesful rotation.
	# optional, default is 1 (do unlink)
	unlink_old             = 1
}