/**
* @return array
*/
public static function getMappingSpe()
{
$db = MMDB::instance();
$cluster = ClusterTool::clusterIdentifier();
// ct->label
$sql = "SELECT t.code, l.label
FROM mm_taxonomy AS t
LEFT JOIN mm_taxonomy_translation AS l ON t.id = l.taxonomy_id AND l.cluster_identifier = '$cluster'
INNER JOIN mm_cluster_has_taxonomy AS cht ON cht.taxonomy_id = t.id AND cht.is_mappable = 1 AND cht.cluster_identifier = '$cluster'
WHERE t.taxonomy_category_id = 1
ORDER BY cht.placement, l.label ASC";
if( SolrSafeOperatorHelper::featureIsActive( 'HiddenCustomerTypes' ) ){
$customerTypeFilter = '';
$hiddenCustomerTypes = SolrSafeOperatorHelper::feature( 'HiddenCustomerTypes', 'customerTypes' );
if(count($hiddenCustomerTypes) > 0){
for ($i = 0; $i < count($hiddenCustomerTypes); $i++){
$customerTypeFilter .= ($i+1 == count($hiddenCustomerTypes)) ? "'".$hiddenCustomerTypes[$i]."'" : "'".$hiddenCustomerTypes[$i]."'," ;
}
$sql = "SELECT t.code, l.label
FROM mm_taxonomy AS t
LEFT JOIN mm_taxonomy_translation AS l ON t.id = l.taxonomy_id AND l.cluster_identifier = '$cluster'
INNER JOIN mm_cluster_has_taxonomy AS cht ON cht.taxonomy_id = t.id AND cht.is_mappable = 1 AND cht.cluster_identifier = '$cluster'
WHERE t.taxonomy_category_id = 1 AND t.code NOT IN (".$customerTypeFilter.")
ORDER BY cht.placement, l.label ASC";
}
}
$results = $db->arrayQuery( $sql );
$tabCT = array();
StringTool::sortAlphaValues($results, "label");
foreach($results as $result){
$tabCT[$result["code"]] = $result["label"];
}
// mapping spe->ct
$sql = "SELECT group_concat(customer_type_code separator ',') AS ct_codes, user_speciality_code AS uspe_code, tlspe.label AS uspe_label
FROM mm_customer_type_specialities AS cts
LEFT JOIN mm_taxonomy AS tspe ON tspe.code = cts.user_speciality_code
LEFT JOIN mm_taxonomy_translation AS tlspe ON tlspe.taxonomy_id = tspe.id
INNER JOIN mm_cluster_has_taxonomy AS cht ON cht.taxonomy_id = tspe.id AND cht.is_mappable = 1 AND cht.cluster_identifier = '$cluster'
WHERE cts.cluster_identifier = '$cluster' AND tlspe.cluster_identifier = '$cluster'
GROUP BY user_speciality_code
ORDER BY uspe_label ASC";
$tabSpe = $db->arrayQuery( $sql );
StringTool::sortAlphaValues($tabSpe, "uspe_label");
if(!count($tabSpe))
{
$sql = "SELECT group_concat(customer_type_code separator ',') AS ct_codes, user_speciality_code AS uspe_code, tlspe.label AS uspe_label
FROM mm_customer_type_specialities AS cts
LEFT JOIN mm_taxonomy AS tspe ON tspe.code = cts.user_speciality_code
LEFT JOIN mm_taxonomy_translation AS tlspe ON tlspe.taxonomy_id = tspe.id
INNER JOIN mm_cluster_has_taxonomy AS cht ON cht.taxonomy_id = tspe.id AND cht.is_mappable = 1 AND cht.cluster_identifier = '$cluster'
WHERE cts.cluster_identifier IS NULL AND tlspe.cluster_identifier = '$cluster'
GROUP BY user_speciality_code
ORDER BY uspe_label ASC";
$tabSpe = $db->arrayQuery( $sql );
}
if( !SolrSafeOperatorHelper::featureIsActive( 'SpecialtySequence' ) )
{
$mapping = array();
foreach($tabSpe as $spe)
{
$mapping[$spe["uspe_code"]] = array( "name" => $spe["uspe_label"],
"ct" => explode(',', $spe["ct_codes"]));
}
return array("mapping" => $mapping, "ct" => $tabCT);
}
$mappingArranged = array();
$mappingOthers = array();
// get specialty sequence from feature
$specialtySequence = SolrSafeOperatorHelper::feature( 'SpecialtySequence', 'Sequence' );
// iterate through sequence
foreach($specialtySequence as $position => $specialty){
foreach($tabSpe as $key => $spe)
{
// put user specialty at desired position
if($spe["uspe_code"] === $specialty){
$mappingArranged[$spe["uspe_code"]] = array( "name" => $spe["uspe_label"],
"ct" => explode(',', $spe["ct_codes"]));
// remove proccessed specialty from base table
unset($tabSpe[$key]);
}
// get others specialties that are not listed in feature
if(!in_array($spe["uspe_code"], $specialtySequence)){
$mappingOthers[$spe["uspe_code"]] = array( "name" => $spe["uspe_label"],
"ct" => explode(',', $spe["ct_codes"]));
}
}
}
return array("mapping" => array_merge($mappingArranged, $mappingOthers), "ct" => $tabCT);
}
/**
* @param string $city
* @return string[][]
*/
static public function fetchDistrictsAndHospitalsByCity($city)
{
$db = MMDB::instance();
$table = self::TABLE_NAME_DEFINITION;
$query = "SELECT DISTINCT district, hospital, adress from $table WHERE city='$city' order by district asc, hospital asc, adress asc";
$results = $db->arrayQuery($query);
$tab = self::arrayfy($results, 'district', 'hospital', 'adress');
foreach($tab as $key=>$value)
{
StringTool::sortAlphaValues($tab[$key]);
}
return $tab;
}
