function extract_data_level($cubename_p, $level_pivoting, $slice_p)
{
global $xmlfile;
$xml = simplexml_load_file($xmlfile);
list($dim1, $hier1, $lev1, $prop1) = explode(".", $level_pivoting);
//print "LP $dim1,$hier1,$lev1,$prop1<br>";
foreach ($xml->Cube as $cube) {
//print "OK<br>";
$cubename = (string) $cube['name'];
//print "CU $cubename<br>";
if ($cubename == $cubename_p) {
//print "CU FOUND $cubename_p<br>";
foreach ($cube->DimensionUsage as $dimension) {
$dimensionname = (string) $dimension['name'];
if ($dimensionname == $dim1) {
//print "D FOUND1 $dim1<br>";
foreach ($xml->Dimension as $dimensioncube) {
$dimensionname2 = (string) $dimensioncube['name'];
if ($dimensionname2 == $dim1) {
//print "D FOUND2 $dim1<br>";
foreach ($dimensioncube->Hierarchy as $hier) {
$hiername = $hier['name'];
$pk_hiertable = $hier['primaryKeyTable'];
if ($hiername == $hier1) {
//print "H FOUND $hier1<br>";
foreach ($hier->Level as $level) {
$levelname = $level['name'];
$level_col = $level['column'];
$level_table = $level['table'];
if ($level_table == "") {
$level_table = $pk_hiertable;
}
if ($levelname == $lev1) {
//print "L FOUND $lev1 COL $level_col TAB $level_table<br>";
///***************where
$n = strlen($slice_p);
$slice_p = substr($slice_p, 0, $n - 2);
$cond = explode("--", $slice_p);
$nc = count($cond);
$where = "";
for ($i = 0; $i < $nc; $i++) {
list($dim_c, $hier_c, $lev_c, $prop_c, $cond1) = explode(".", $cond[$i]);
if ($dim_c == $dim1 && $hier_c == $hier1 && $lev_c == $lev1) {
$cond1 = trasforma($cond1);
$where = "{$level_table}.{$level_col} {$cond1}";
}
}
//**********************
$dataset = read_data($level_table, $level_col, $where);
return $dataset;
}
}
}
}
}
}
}
}
}
}
}
function SQLgenerator2($cubename_sel, $levels_ser, $slice, $colonna, $ordinamento)
{
global $xmlfile;
$xml = simplexml_load_file($xmlfile);
$levels = explode("-", $levels_ser);
//print "XML $xmlfile, CUB $cubename_sel, LEV $levels_ser<br>";
//*******************************CUBE
$groupby = false;
foreach ($xml->Cube as $cube) {
$cubename = $cube['name'];
if ($cubename == $cubename_sel) {
$cubetable = $cube->Table;
$cubetablename = $cubetable['name'];
//print "CUB TABLE $cubetablename<br>";
$nl = count($levels);
$nj = 0;
for ($i = 0; $i < $nl; $i++) {
list($dim1, $hier1, $lev1, $prop1) = explode(".", $levels[$i]);
if ($dim1 == "cube" and $hier1 == "cube" and $lev1 != "aggregate") {
//**************misure del cubo
foreach ($cube->Measure as $measure) {
$measurename = $measure["name"];
$measurecol = $measure["column"];
if ($measurename == $prop1) {
$target_list .= "{$measurecol},";
}
}
//misure calcolate
foreach ($cube->CalculatedMember as $calc_measure) {
$calc_measurename = $calc_measure["name"];
if ($calc_measurename == $prop1) {
$formula = $calc_measure->Formula;
$expr = $formula["expression"];
$alias = $formula["alias"];
$target_list .= "{$expr} as {$alias},";
}
}
}
if ($dim1 == "cube" and $hier1 == "cube" and $lev1 == "aggregate") {
//$a=eregi("(.+)\(",$prop1,$regs);
$a = preg_match("/(.+)\\(/", $prop1, $regs);
$funz = $regs[1];
//$a=eregi("\((.+)\)",$prop1,$regs);
$a = preg_match("/\\((.+)\\)/", $prop1, $regs);
$measurename = $regs[1];
foreach ($cube->Measure as $measure) {
$measurename2 = $measure["name"];
$measurecol = $measure["column"];
if ($measurename2 == $measurename) {
$target_list .= "{$funz}({$measurecol}),";
}
$groupby = true;
}
//misure calcolate
foreach ($cube->CalculatedMember as $calc_measure) {
$calc_measurename = $calc_measure["name"];
if ($calc_measurename == $measurename) {
$formula = $calc_measure->Formula;
$expr = $formula["expression"];
$alias = $formula["alias"];
$target_list .= "{$funz}({$expr}) as {$alias},";
$groupby = true;
}
}
}
foreach ($cube->DimensionUsage as $dimension) {
$dimensionname = $dimension['name'];
//print "d $dimensionname<br>";
if ($dimensionname == $dim1) {
$fk_cube = $dimension['foreignKey'];
foreach ($xml->Dimension as $dimensioncube) {
$dimensionname2 = $dimensioncube['name'];
if ($dimensionname2 == $dim1) {
foreach ($dimensioncube->Hierarchy as $hier) {
$hiername = $hier['name'];
if ($hiername == $hier1) {
$pk_hier = $hier['primaryKey'];
$pk_hiertable = $hier['primaryKeyTable'];
if ($pk_hiertable != "") {
$join[$nj] = "right join {$pk_hiertable} on {$cubetablename}.{$fk_cube}={$pk_hiertable}.{$pk_hier} ";
$nj = $nj + 1;
}
//************************ Add join
$join[$nj] = buildJoin($hier);
$nj = $nj + 1;
//*********************************
foreach ($hier->Level as $level) {
$levelname = $level['name'];
if ($levelname == $lev1) {
$level_table = $level['table'];
if ($level_table == "") {
$level_table = $pk_hiertable;
}
$level_col = $level['column'];
$group[$i] = "{$level_table}.{$level_col}";
foreach ($level->Property as $prop) {
$propname = $prop['name'];
if ($propname == $prop1) {
$level_col = $prop['column'];
$target_list .= "{$level_table}.{$level_col},";
}
}
}
}
}
}
}
}
}
}
}
}
}
//************************************WHERE
$n = strlen($slice);
$slice = substr($slice, 0, $n - 2);
$cond = explode("--", $slice);
$nc = count($cond);
for ($i = 0; $i < $nc; $i++) {
list($dim_c, $hier_c, $lev_c, $prop_c, $cond1) = explode(".", $cond[$i]);
$cond1 = trasforma($cond1);
// Add fk_cube
foreach ($cube->DimensionUsage as $dimension_cube) {
$dimensionname_cube = $dimension_cube['name'];
if ($dimensionname_cube == $dim_c) {
$fk_cube = $dimension_cube['foreignKey'];
}
}
foreach ($xml->Dimension as $dimensioncube) {
$dimensionname = $dimensioncube['name'];
if ($dim_c == $dimensionname) {
foreach ($dimensioncube->Hierarchy as $hier) {
$hiername = $hier['name'];
if ($hiername == $hier_c) {
$pk_hier = $hier['primaryKey'];
$pk_hiertable = $hier['primaryKeyTable'];
//**********************************************
if ($pk_hiertable != "") {
$join[$nj] = "right join {$pk_hiertable} on {$cubetablename}.{$fk_cube}={$pk_hiertable}.{$pk_hier} ";
$nj = $nj + 1;
}
//************************ Add join
$join[$nj] = buildJoin($hier);
$nj = $nj + 1;
//*********************************************
foreach ($hier->Level as $level) {
$levelname = $level['name'];
if ($levelname == $lev_c) {
$level_table = $level['table'];
$level_col = $level['column'];
if ($level_table == "") {
$level_table = $pk_hiertable;
}
foreach ($level->Property as $prop) {
$propname = $prop['name'];
if ($propname == $prop_c) {
$level_col = $prop['column'];
$where[$i] = "{$level_table}.{$level_col} {$cond1}";
}
}
}
}
}
}
}
}
}
//metti in AND
$where_final = "";
for ($i = 0; $i < $nc; $i++) {
$where_final = $where_final . " {$where[$i]} AND ";
}
//***********************elimina join ridondanti
for ($i = 0; $i < $nj; $i++) {
for ($j = 0; $j < $nj; $j++) {
if ($i != $j && $join[$i] == $join[$j]) {
$join[$j] = "";
}
}
}
for ($i = 0; $i < $nj; $i++) {
$join_final .= " {$join[$i]}";
}
//***********************costruisci target list
$n = strlen($target_list);
$target_list = substr($target_list, 0, $n - 1);
//print $target_list;
//******************************costruisci group by
for ($i = 0; $i < $nl; $i++) {
for ($j = 0; $j < $nl; $j++) {
if ($i != $j && $group[$i] == $group[$j]) {
$group[$j] = "";
}
}
}
for ($i = 0; $i < $nl; $i++) {
if ($group[$i] != "") {
$group_final .= "{$group[$i]},";
}
}
$n = strlen($group_final);
$group_final = substr($group_final, 0, $n - 1);
//***********************costruisci query finale
$n = strlen($where_final);
$where_final = substr($where_final, 0, $n - 5);
$n = strlen($where_final);
if ($groupby == false or $group_final == "") {
$query = "select distinct {$target_list} {$mea} from {$cubetablename} {$join_final}";
if ($n > 1) {
$query = "select distinct {$target_list} {$mea} from {$cubetablename} {$join_final} where {$where_final}";
}
} else {
if ($n > 1) {
$query = "select distinct {$target_list} {$mea} from {$cubetablename} {$join_final} where {$where_final} group by {$group_final}";
} else {
$query = "select distinct {$target_list} {$mea} from {$cubetablename} {$join_final} group by {$group_final}";
}
}
//***************************ORDINAMENTO
list($tab1, $col1) = explode(".", $colonna);
$a = strrpos($join_final, $tab1);
if ($tab1 != "" && $a != false && $colonna != "" && $ordinamento != "") {
$query = "{$query} order by {$col1} {$ordinamento}";
}
if ($tab1 == "" && $colonna != "" && $ordinamento != "") {
$query = "{$query} order by {$col1} {$ordinamento}";
}
//print "$query<br>";
return $query;
}
