G6JiGy83

1CREATE TABLE IF NOT EXISTS `functie` (
2  `id` varchar(23) COLLATE latin1_general_ci NOT NULL,
3  `functionname` varchar(100) COLLATE latin1_general_ci NOT NULL,
4  `desc` varchar(2000) COLLATE latin1_general_ci NOT NULL,
5  `customercode` varchar(100) COLLATE latin1_general_ci DEFAULT NULL,
6  PRIMARY KEY (`id`),
7  UNIQUE KEY `functionname` (`functionname`)
8) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
9
10
11SELECT concat(u.voornaam,' ',u.famnaam) AS friendlyusername,
12  u.userpicture                         AS picture,
13  func.functionname,
14  COALESCE(f.doccount,0) AS doccount
15FROM `user` u
16LEFT JOIN
17  (SELECT COUNT(id) AS doccount,d.usercreated,d.id as documentid,d.type,d.datecreated
18  FROM document d
19  WHERE customercode='test'
20  AND d.removable  !='1'
21  GROUP BY d.usercreated, documentid, d.type, d.datecreated
22  )AS f
23ON f.usercreated = u.id
24LEFT JOIN documenttype dt
25ON f.type          = dt.id
26AND dt.customercode='test'
27LEFT JOIN userfuncties uf
28ON f.usercreated   = uf.userid
29AND uf.customercode='test'
30LEFT JOIN functie func
31ON uf.functieid   = uf.id
32AND func.customercode='test'
33WHERE EXISTS
34  (SELECT *
35  FROM permission p
36  WHERE p.objid     = f.documentid
37  AND p.userid      = '4e9a7914e85c85.75746751'
38  AND p.customercode='test'
39  )
40GROUP BY f.documentid
41ORDER BY f.datecreated DESC LIMIT 0,20