SELECT
ps_user_loc AS loc, COUNT(ps_id) AS ser, Count(Distinct ps_ur) AS patient
FROM patient_services GROUP BY loc
UNION ALL
SELECT
eng_user_loc AS loc, COUNT(eng_id) AS ser, Count(Distinct eng_ur) AS patient
FROM engagements WHERE LENGTH( eng_ur )>0 GROUP BY locSELECT
SUM( CASE WHEN TIMESTAMPDIFF(YEAR,patient_dob,CURDATE())<5 THEN 1 ELSE 0 END ) q,
SUM( CASE WHEN TIMESTAMPDIFF(YEAR,patient_dob,CURDATE()) BETWEEN 5 AND 10 THEN 1 ELSE 0 END ) w,
SUM( CASE WHEN TIMESTAMPDIFF(YEAR,patient_dob,CURDATE()) BETWEEN 10 AND 15 THEN 1 ELSE 0 END ) e,
SUM( CASE WHEN TIMESTAMPDIFF(YEAR,patient_dob,CURDATE()) BETWEEN 15 AND 20 THEN 1 ELSE 0 END ) r,
SUM( CASE WHEN TIMESTAMPDIFF(YEAR,patient_dob,CURDATE()) BETWEEN 20 AND 30 THEN 1 ELSE 0 END ) t,
SUM( CASE WHEN TIMESTAMPDIFF(YEAR,patient_dob,CURDATE()) BETWEEN 30 AND 40 THEN 1 ELSE 0 END ) y,
SUM( CASE WHEN TIMESTAMPDIFF(YEAR,patient_dob,CURDATE()) BETWEEN 40 AND 60 THEN 1 ELSE 0 END ) u,
SUM( CASE WHEN TIMESTAMPDIFF(YEAR,patient_dob,CURDATE()) BETWEEN 60 AND 80 THEN 1 ELSE 0 END ) i,
SUM( CASE WHEN TIMESTAMPDIFF(YEAR,patient_dob,CURDATE())>80 THEN 1 ELSE 0 END ) o
FROM patients WHERE patient_ur
IN( SELECT ps_ur FROM patient_services AS ur JOIN ( SELECT eng_ur FROM engagements WHERE eng_ur > 1 AND ( eng_datetime BETWEEN '2015-01-01' AND '2017-01-01' )) AS ur WHERE ( ps_datetime BETWEEN '2015-01-01' AND '2017-01-01' ) )SELECT ps_ur FROM patient_services AS ur JOIN ( SELECT eng_ur FROM engagements WHERE eng_ur > 1 AND ( eng_datetime BETWEEN '2015-01-01' AND '2017-01-01' )) AS ur WHERE ( ps_datetime BETWEEN '2015-01-01' AND '2017-01-01' )SELECT COUNT(ps_ur) FROM ( SELECT ps_ur FROM patient_services AS ur JOIN ( SELECT eng_ur FROM engagements WHERE eng_ur > 1 AND( eng_datetime BETWEEN '2015-01-01' AND '2017-01-01' ) ) AS ur WHERE ( ps_datetime BETWEEN '2015-01-01' AND '2017-01-01' ) ) AS ps_ur GROUP BY ps_ur$success=$mysqli->query($query)
or die ('cannot select services');
$myArray=array();
while($row = $success->fetch_assoc())
{
foreach ($row as $key => $value) {
$row[$key]= base64_encode($value);
}
array_push($myArray,$row);
}
printf(json_encode($myArray));(response)=> {
var myArr =JSON.parse(response);
for (var i = 0; i < myArr.length; i++){
var obj = myArr[i];
for (var key in obj){
obj[key]=window.atob(obj[key]);
}
}
}var array = [{"date":"2015 August","value":"40"},
{"date":"2016 August","value":"473"},
{"date":"2016 July","value":"106"},
{"date":"2016 June","value":"55"},
{"date":"2016 November","value":"3"},
{"date":"2016 October","value":"2"},
{"date":"2016 September","value":"287"},
{"date":"2016 August","value":"3"},
{"date":"2016 July","value":"4"},
{"date":"2016 September","value":"5"}];
var arr=[],
arrSort=[];
var searchArr = function(nameKey, prop, myArray){
for (var i=0; i < myArray.length; i++) {
if (myArray[i][prop] == nameKey) {
return myArray[i];
}
}
};
var searchI = function(nameKey, prop, myArray){
for (var i=0; i < myArray.length; i++) {
if (myArray[i][prop]== nameKey) {
return i;
}
}
};
array.map(function(result, item) {
var iArr = searchArr(result.date, 'date', arr);
if ( iArr != null ) {
let i = searchI(result.date, 'date', arr);
arr[ i ].value = parseInt( iArr.value ) + parseInt( arr[ i ].value ) ;
} else {
arr.push( { "date": result.date , "value": parseInt( result.value ) } );
}
});
arr.map(function(result, item) {
arrSort.push( { "date": new Date( result.date ) , "value": result.value } );
});
arrSort.sort(function(a,b){
return a.date - b.date;
});
console.log(arrSort);