Changeset 1460

Timestamp:

02/04/10 11:42:06 (5 weeks ago)

Author:

teow

Message:

<?php
class Log extends AppModel? {

var $name = 'Log';
var $validate = array(

'user_id' => array('numeric'),
'controller' => array('notempty'),
'action' => array('notempty'),
'url' => array('notempty')

);

//The Associations below have been created with all possible keys, those that are not needed can be removed

/*remove the user and log relation

var $belongsTo = array(

'User' => array('className' => 'User',

'foreignKey' => 'user_id',
'conditions' => ,
'fields' => ,
'order' =>

)

);

*/

function getReport($type) {

if ($type == 'USERS') {

//get user and login information
$sql3 = "select u.id, u.name, u.job_title, t.long_name

from titles as t, users as u
where u.deleted!='1' and t.id=u.title_id
group by u.id
order by u.name asc";

$m = $this->query($sql3);
$user_id=implode(',',Set::extract($m,'{n}.u.id'));

//get login info
$sql = "select l.user_id, count(l.id) as login from logs as l

where l.url='/' and l.user_id in (".$user_id.")
group by l.user_id";

$p = $this->query($sql);
$pval=set::extract($p,'{n}.l.user_id');

//get comment info
$sql1 = "select c.user_id,count(c.id) as comment from comments as c

where c.user_id in (".$user_id.") group by c.user_id";

$o = $this->query($sql1);
$oval=set::extract($o,'{n}.c.user_id');

//get status info
$sql2 = "select us.updater,count(us.id) as feedback from userstatuses as us

where us.updater in (".$user_id.") group by us.updater";

$n=$this->query($sql2);
$nval=set::extract($n,'{n}.us.updater');
$sql2a = "select gs.user_id,count(gs.id) as feedback from groupstatuses as gs

where gs.user_id in (".$user_id.") group by gs.user_id";

$na = $this->query($sql2a);
$naval=set::extract($na,'{n}.gs.user_id');

foreach ($m as $mkey=>$mval) {

$m[$mkey][0]feedback? =

$this->getInfo($mvalu?id?,$nval,$n,'feedback')+
$this->getInfo($mvalu?id?,$naval,$na,'feedback');

$m[$mkey]0?comment?=$this->getInfo($mvalu?id?,$oval,$o,'comment');
$m[$mkey]0?login?=$this->getInfo($mvalu?id?,$pval,$p,'login');

}
return $m;

} else if ($type == 'MEETINGS') {

// get num of meetings for each committee
$sql = "select c.name, m.meeting_title, count(m.id) as kuantiti

from committees as c
join meetings as m on (c.id=m.committee_id and m.deleted!='1')
where c.deleted!='1'
group by m.meeting_title
order by c.name asc, m.meeting_title asc";

$r = $this->query($sql);
$meeting_title='"'.implode('","',Set::extract($r,'{n}.m.meeting_title')).'"';

// get num of decisions for each meeting
$sql4 = "select m.meeting_title, count(d.id) as numofdec

from meetings as m, decisions as d
where m.deleted!='1' and d.deleted!='1' and d.meeting_id=m.id
and m.meeting_title in (".$meeting_title.")
group by m.meeting_title";

$q = $this->query($sql4);
$qval=set::extract($q,'{n}.m.meeting_title');

// get num of individual status for each meeting
$sql2 = "select m.meeting_title, count(us.id) as numofstat_usr

from meetings as m, decisions as d
left join userstatuses as us on (us.decision_id=d.id)
where d.meeting_id=m.id and m.deleted!='1' and d.deleted!='1'
and m.meeting_title in (".$meeting_title.")
group by m.meeting_title";

$s = $this->query($sql2);
$sval=set::extract($s,'{n}.m.meeting_title');

// get num of group status for each meeting
$sql3 = "select m.meeting_title, count(gs.id) as numofstat_grp

from meetings as m, decisions as d
left join groupstatuses as gs on (gs.decision_id=d.id)
where d.meeting_id=m.id and m.deleted!='1' and d.deleted!='1'
and m.meeting_title in (".$meeting_title.")
group by m.meeting_title";

$t = $this->query($sql3);
$tval=set::extract($t,'{n}.m.meeting_title');

// get num of individual implementor for each meeting
$sql5 = "select m.meeting_title, count(DISTINCT du.id) as numofdec_assg_usr

from meetings as m, decisions as d
left join decisions_users as du on (d.id=du.decision_id)
where m.id=d.meeting_id and m.deleted!='1' and d.deleted!='1'
and m.meeting_title in (".$meeting_title.")
group by m.meeting_title";

$u = $this->query($sql5);
$uval=set::extract($u,'{n}.m.meeting_title');

// get num of group implementor for each meeting
$sql6 = "select m.meeting_title, count(DISTINCT dg.id) as numofdec_assg_grp

from meetings as m, decisions as d
left join decisions_groups as dg on (d.id=dg.decision_id)
where m.id=d.meeting_id and m.deleted!='1' and d.deleted!='1'
and m.meeting_title in (".$meeting_title.")
group by m.meeting_title";

$v = $this->query($sql6);
$vval=set::extract($v,'{n}.m.meeting_title');

// combine all these into 1 nice array
foreach ($r as $rkey=>$rval) {

$r[$rkey]0?numofdec?=$this->getInfo($rvalm?meeting_title?,$qval,$q,'numofdec');
$r[$rkey]0?numofdec_assg_usr? = $this->getInfo($rvalm?meeting_title?,$uval,$u,'numofdec_assg_usr');
$r[$rkey]0?numofdec_assg_grp? = $this->getInfo($rvalm?meeting_title?,$vval,$v,'numofdec_assg_grp');
$r[$rkey]0?numofstat_usr? = $this->getInfo($rvalm?meeting_title?,$sval,$s,'numofstat_usr');
$r[$rkey]0?numofstat_grp? = $this->getInfo($rvalm?meeting_title?,$tval,$t,'numofstat_grp');

}
return $r;

}

}

/*describe getInfo

• Find the needed information
• @param $needle
• @param $haystack
• @param $array -the array of the info
• @param $info - variable name of the needed info
• @return the needed value

*/
function getInfo($needle,$haystack,$array,$info){

if(in_array($needle,$haystack)){

$key=array_search($needle,$haystack);
return $array[$key][0][$info];

}else{

return 0;

}

}

}
?>

Files:

• trunk/app/models/log.php (modified) (7 diffs)

trunk/app/models/log.php

@@ -31 +31 @@
                     order by u.name asc";
             $m = $this->query($sql3);
+            $user_id=implode(',',Set::extract($m,'{n}.u.id'));
             
             //get login info
             $sql = "select l.user_id, count(l.id) as login from logs as l 
-                    where l.url='/' group by l.user_id";
+                    where l.url='/' and l.user_id in (".$user_id.")
+                    group by l.user_id";
             $p = $this->query($sql);
+            $pval=set::extract($p,'{n}.l.user_id');
             
             //get comment info
             $sql1 = "select c.user_id,count(c.id) as comment from comments as c 
-                    where c.user_id!=0 group by c.user_id";
+                    where c.user_id in (".$user_id.") group by c.user_id";
             $o = $this->query($sql1);
+            $oval=set::extract($o,'{n}.c.user_id');
             
             //get status info
             $sql2 = "select us.updater,count(us.id) as feedback from userstatuses as us
-                    where us.updater!=0 group by us.updater";
+                    where us.updater in (".$user_id.") group by us.updater";
             $n=$this->query($sql2);
+            $nval=set::extract($n,'{n}.us.updater');
             $sql2a = "select gs.user_id,count(gs.id) as feedback from groupstatuses as gs 
-                    where gs.user_id!=0 group by gs.user_id";       
+                    where gs.user_id in (".$user_id.") group by gs.user_id";       
             $na = $this->query($sql2a);
+            $naval=set::extract($na,'{n}.gs.user_id');
             
             foreach ($m as $mkey=>$mval) {
-                $ncalc=0;
-                foreach ($n as $nval) {
-                    if($nval['us']['updater']==$mval['u']['id']){
-                        $ncalc=$nval[0]['feedback'];
-                    }
-                }
-                foreach ($na as $nval) {
-                    if($nval['gs']['user_id']==$mval['u']['id']){
-                        $ncalc+=$nval[0]['feedback'];
-                    }
-                }
-                $m[$mkey][0]['feedback'] = $ncalc; 
-                
-                foreach ($o as $oval) {
-                    if ($oval['c']['user_id']==$mval['u']['id']) 
-                        $m[$mkey]['0']['comment'] = $oval['0']['comment']; 
-                }
-                
-                foreach ($p as $pval) {
-                    if ($pval['l']['user_id']==$mval['u']['id']) 
-                        $m[$mkey]['0']['login'] = $pval['0']['login']; 
-                }
+                $m[$mkey][0]['feedback'] =
+                    $this->getInfo($mval['u']['id'],$nval,$n,'feedback')+
+                    $this->getInfo($mval['u']['id'],$naval,$na,'feedback');
+                $m[$mkey]['0']['comment']=$this->getInfo($mval['u']['id'],$oval,$o,'comment');
+                $m[$mkey]['0']['login']=$this->getInfo($mval['u']['id'],$pval,$p,'login');
             }
             return $m;
             
         } else if ($type == 'MEETINGS') {
-            // get num of decisions for each meeting
-            $sql4 = "select m.meeting_title, count(d.id) as numofdec
-                    from meetings as m, decisions as d
-                    where m.deleted!='1' and d.deleted!='1' and d.meeting_id=m.id
-                    group by m.meeting_title";
-            $q = $this->query($sql4);
-            
             // get num of meetings for each committee
             $sql = "select c.name, m.meeting_title, count(m.id) as kuantiti
@@ -92 +74 @@
                     order by c.name asc, m.meeting_title asc";
             $r = $this->query($sql);
+            $meeting_title='"'.implode('","',Set::extract($r,'{n}.m.meeting_title')).'"';
 
+            // get num of decisions for each meeting
+            $sql4 = "select m.meeting_title, count(d.id) as numofdec
+                    from meetings as m, decisions as d
+                    where m.deleted!='1' and d.deleted!='1' and d.meeting_id=m.id 
+                    and m.meeting_title in (".$meeting_title.") 
+                    group by m.meeting_title";
+            $q = $this->query($sql4);
+            $qval=set::extract($q,'{n}.m.meeting_title'); 
+            
             // get num of individual status for each meeting
             $sql2 = "select m.meeting_title, count(us.id) as numofstat_usr
@@ -98 +90 @@
                     left join userstatuses as us on (us.decision_id=d.id)
                     where d.meeting_id=m.id and m.deleted!='1' and d.deleted!='1'
+                    and m.meeting_title in (".$meeting_title.") 
                     group by m.meeting_title";
             $s = $this->query($sql2);
+            $sval=set::extract($s,'{n}.m.meeting_title'); 
                 
             // get num of group status for each meeting
@@ -106 +100 @@
                     left join groupstatuses as gs on (gs.decision_id=d.id)
                     where d.meeting_id=m.id and m.deleted!='1' and d.deleted!='1'
+                    and m.meeting_title in (".$meeting_title.") 
                     group by m.meeting_title";
             $t = $this->query($sql3);
+            $tval=set::extract($t,'{n}.m.meeting_title'); 
                 
             // get num of individual implementor for each meeting
@@ -114 +110 @@
                     left join decisions_users as du on (d.id=du.decision_id)
                     where m.id=d.meeting_id and m.deleted!='1' and d.deleted!='1' 
+                    and m.meeting_title in (".$meeting_title.") 
                     group by m.meeting_title";
             $u = $this->query($sql5);
+            $uval=set::extract($u,'{n}.m.meeting_title'); 
 
             // get num of group implementor for each meeting
@@ -122 +120 @@
                     left join decisions_groups as dg on (d.id=dg.decision_id)
                     where m.id=d.meeting_id and m.deleted!='1' and d.deleted!='1' 
+                    and m.meeting_title in (".$meeting_title.") 
                     group by m.meeting_title";
             $v = $this->query($sql6);
-                
+            $vval=set::extract($v,'{n}.m.meeting_title'); 
+               
             // combine all these into 1 nice array
             foreach ($r as $rkey=>$rval) {
-                // make sure all index exist
-                $r[$rkey]['0']['numofdec'] = '0';
-                $r[$rkey]['0']['numofdec_assg_usr'] = '0';
-                $r[$rkey]['0']['numofdec_assg_grp'] = '0';
-                $r[$rkey]['0']['numofstat_usr'] = '0';
-                $r[$rkey]['0']['numofstat_grp'] = '0';
-                    
-                // merge with the rest of sql
-                foreach ($q as $qval) 
-                    if ($qval['m']['meeting_title']==$rval['m']['meeting_title']) 
-                        $r[$rkey]['0']['numofdec'] = $qval['0']['numofdec']; 
-                foreach ($s as $sval) 
-                    if ($sval['m']['meeting_title']==$rval['m']['meeting_title']) 
-                        $r[$rkey]['0']['numofstat_usr'] = $sval['0']['numofstat_usr']; 
-                foreach ($t as $tval) 
-                    if ($tval['m']['meeting_title']==$rval['m']['meeting_title']) 
-                        $r[$rkey]['0']['numofstat_grp'] = $tval['0']['numofstat_grp']; 
-                foreach ($u as $uval) 
-                    if ($uval['m']['meeting_title']==$rval['m']['meeting_title']) 
-                        $r[$rkey]['0']['numofdec_assg_usr'] = $uval['0']['numofdec_assg_usr']; 
-                foreach ($v as $vval) 
-                    if ($vval['m']['meeting_title']==$rval['m']['meeting_title']) 
-                        $r[$rkey]['0']['numofdec_assg_grp'] = $vval['0']['numofdec_assg_grp']; 
+                $r[$rkey]['0']['numofdec']=$this->getInfo($rval['m']['meeting_title'],$qval,$q,'numofdec');
+                $r[$rkey]['0']['numofdec_assg_usr'] = $this->getInfo($rval['m']['meeting_title'],$uval,$u,'numofdec_assg_usr');
+                $r[$rkey]['0']['numofdec_assg_grp'] = $this->getInfo($rval['m']['meeting_title'],$vval,$v,'numofdec_assg_grp');
+                $r[$rkey]['0']['numofstat_usr'] = $this->getInfo($rval['m']['meeting_title'],$sval,$s,'numofstat_usr');
+                $r[$rkey]['0']['numofstat_grp'] = $this->getInfo($rval['m']['meeting_title'],$tval,$t,'numofstat_grp');
             }
             return $r;
@@ -156 +138 @@
        
     }
+    
+    /*describe getInfo
+     * Find the needed information
+     * @param $needle 
+     * @param $haystack 
+     * @param $array -the array of the info 
+     * @param $info - variable name of the needed info
+     * @return the needed value 
+    */
+    function getInfo($needle,$haystack,$array,$info){
+        if(in_array($needle,$haystack)){
+            $key=array_search($needle,$haystack);
+            return $array[$key][0][$info];
+        }else{
+            return 0;
+        }
+    }
 
 }