· 9 years ago · Oct 14, 2016, 09:06 AM
1<?php
2require_once('customer/AWO/controller/OdbcConnector.php');
3
4class RemoteDataProvider
5{
6 protected $db;
7 private static $instance;
8
9 protected function __construct()
10 {
11 $config = parse_ini_file('customer/AWO/conf/custom.ini', true);
12 $odbcSettings = $config['DataSource'];
13 $this->db = new OdbcConnector(
14 $odbcSettings['DsnName'],
15 $odbcSettings['DsnUser'],
16 $odbcSettings['DsnPassword']
17 );
18
19 $this->createLogsTable();
20 }
21
22 public static function instance()
23 {
24 if (!isset(self::$instance))
25 self::$instance = new self();
26 return self::$instance;
27 }
28
29 public function getCourseData()
30 {
31 return $this->assembleCourseData();
32 }
33
34 private function getCourseDbRows()
35 {
36 $sql = "
37 SELECT
38 s.S_ID AS SeminarID,
39 s.S_NrTXT AS SeminarNummer,
40 s.Kurzbezeichnung AS SeminarKurzbezeichnung,
41 s.BeginnDat + CAST(s.BeginnZeit AS Time) AS SeminarBeginnDatum,
42 s.EndeDat + CAST(s.EndeZeit AS Time) AS SeminarEndeDatum,
43 sk.bezeichnung AS SeminarKategorie,
44 s.Veranstaltungsort AS SeminarOrt,
45 k.Name2 AS VeranstalterName2,
46 s.Gebuehr1 AS SeminarGebuehr1,
47 s.Gebuehr2 AS SeminarGebuehr2,
48 s.Status AS SeminarStatus,
49 k1.Bezeichnung AS SeminarInterneKategorie1,
50 k2.Bezeichnung AS SeminarInterneKategorie2,
51 ISNULL(smtn.TNAngemeldet, 0) AS SeminarAnzahlTNAngemeldet
52 FROM ((((
53 Seminar AS s
54 INNER JOIN Seminar_Kategorien AS sk ON s.Kategorie = sk.zaehler)
55 INNER JOIN Kunden AS k ON s.VANr = k.Kdnr)
56 LEFT JOIN (
57 SELECT
58 tn.s_ID,
59 COUNT(*) AS TNAngemeldet
60 FROM N1_AD_Seminar AS tn
61 WHERE tn.Status = 2
62 AND tn.Dozent = 0
63 GROUP BY tn.s_ID
64 ) AS smtn ON smtn.S_ID = s.s_ID)
65 LEFT JOIN Kategorien AS k1 ON s.Kategorie1 = k1.Kategorienr)
66 LEFT JOIN Kategorien AS k2 ON s.Kategorie2 = k2.Kategorienr
67 WHERE s.Status = 1
68 AND (k1.Kategoriegruppe = 1 OR k1.Kategoriegruppe IS NULL)
69 AND (k2.Kategoriegruppe = 2 OR k2.Kategoriegruppe IS NULL)
70 AND (
71 s.BeginnDat
72 BETWEEN CONVERT(date, GETDATE())
73 AND CONVERT(date, DATEADD(DD, 8*7, GETDATE()))
74 OR s.EndeDat
75 BETWEEN CONVERT(date, GETDATE())
76 AND CONVERT(date, DATEADD(DD, 8*7, GETDATE()))
77 )
78 ";
79
80 $resource = $this->db->query($sql);
81 return $this->db->fetchAll($resource);
82 }
83
84 private function getInstructorDbRows()
85 {
86 $sql = "
87 SELECT DISTINCT
88 a.AD_Nr AS AdressID,
89 d.d_nummer AS DozentID,
90 s.S_ID AS SeminarID,
91 a.Vorname AS DozentVorname,
92 a.Name AS DozentNachname,
93 a.E_Mail AS DozentEMailDienstlich,
94 (CASE WHEN a.geschlecht = 1 THEN 'w' WHEN a.geschlecht = 2 THEN 'm' ELSE '-' END) AS Geschlecht
95 FROM ((N1_SM_Dozenten AS smdoz
96 INNER JOIN Seminar AS s
97 ON s.S_ID = smdoz.s_id)
98 INNER JOIN Dozenten AS d
99 ON d.d_nummer = smdoz.d_nummer)
100 INNER JOIN Adressen AS a
101 ON d.ad_nr = a.AD_Nr
102 WHERE s.Status = 1
103 AND (
104 s.BeginnDat
105 BETWEEN CONVERT(date, GETDATE())
106 AND CONVERT(date, DATEADD(DD, 8*7, GETDATE()))
107 OR s.EndeDat
108 BETWEEN CONVERT(date, GETDATE())
109 AND CONVERT(date, DATEADD(DD, 8*7, GETDATE()))
110 )
111 ";
112
113 $resource = $this->db->query($sql);
114 return $this->db->fetchAll($resource);
115 }
116
117 protected function assembleCourseData($courseRows = NULL, $instructorRows = NULL)
118 {
119 if (!$courseRows) {
120 $courseRows = $this->getCourseDbRows();
121 }
122 if (!$instructorRows) {
123 $instructorRows = $this->getInstructorDbRows();
124 }
125
126 $instructors = array();
127 foreach ($instructorRows as $row)
128 {
129 $seminarId = $row['SeminarID'];
130 $instructor = array(
131 'INSTRUCTOR_UID' => $row['DozentID'],
132 'INSTRUCTOR_FIRSTNAME' => $row['DozentVorname'],
133 'INSTRUCTOR_LASTNAME' => $row['DozentNachname'],
134 'INSTRUCTOR_GENDER' => $row['Geschlecht'],
135 'INSTRUCTOR_EMAIL' => $row['DozentEMailDienstlich'],
136 'INSTRUCTOR_PHONE' => '',
137 );
138
139 $instructors[$seminarId][] = $instructor;
140 }
141
142 $courses = array();
143 foreach($courseRows as $row)
144 {
145 $seminarId = $row['SeminarID'];
146 $moduleInstructors = $instructors[$seminarId];
147 $mainInstructorName = null;
148 $mainInstructor = null;
149 $tmpSecInstr = null;
150
151 if($moduleInstructors)
152 {
153 $mainInstructor = reset($moduleInstructors);
154 if($mainInstructor)
155 {
156 $mainInstructorName = $mainInstructor['INSTRUCTOR_LASTNAME']
157 . ', ' . $mainInstructor['INSTRUCTOR_FIRSTNAME'];
158 }
159
160 if(sizeof($moduleInstructors) > 1)
161 {
162 $secondaryInstructors = array();
163 $instructor = next($moduleInstructors);
164 while ($instructor) {
165 $secondaryInstructors[] = array(
166 'FIRSTNAME' => $instructor['INSTRUCTOR_FIRSTNAME'],
167 'LASTNAME' => $instructor['INSTRUCTOR_LASTNAME']
168 );
169 $instructor = next($moduleInstructors);
170 }
171 /*
172 $secondaryInstructor = next($moduleInstructors);
173 $tmpSecInstr = array(
174 'INSTRUCTOR2_FIRSTNAME' => $secondaryInstructor['INSTRUCTOR_FIRSTNAME'],
175 'INSTRUCTOR2_LASTNAME' => $secondaryInstructor['INSTRUCTOR_LASTNAME'],
176 );
177 *
178 */
179 }
180 }
181
182 $startDate = '';
183 if($row['SeminarBeginnDatum'])
184 {
185 $startDate = date('d.m.Y, H:i', strtotime($row['SeminarBeginnDatum']));
186 }
187
188 $endDate = '';
189 if($row['SeminarBeginnDatum'])
190 {
191 $endDate = date('d.m.Y, H:i', strtotime($row['SeminarEndeDatum']));
192 }
193
194 $course = array(
195 'SUBUNIT_NAME' =>$row['VeranstalterName2'],
196 'MODULE_ID' => $seminarId,
197 'MODULE_NAME' => $row['SeminarKurzbezeichnung'],
198 'MODULE_CODE' => $row['SeminarNummer'],
199 'MODULE_SESSION_TYPE' => $row['SeminarKategorie'],
200 'MODULE_POS' => $row['SeminarInterneKategorie1']
201 . $row['SeminarInterneKategorie2'],
202 'MODULE_ROOMNAME' => $row['SeminarOrt'],
203 'MODULE_ENROLLMENT' => $row['SeminarAnzahlTNAngemeldet'],
204 'MODULE_PERIOD_ID' => $row['SeminarBeginnDatum'],
205 'START_DATE' => $startDate,
206 'END_DATE' => $endDate,
207 'INSTRUCTOR_NAME' =>$mainInstructorName ? $mainInstructorName : '',
208 'InstructorList' => $moduleInstructors ? $moduleInstructors : array(),
209 );
210
211 if($mainInstructor)
212 {
213 $course = array_merge($course, $mainInstructor);
214 }
215 /*
216 if($tmpSecInstr)
217 {
218 $course = array_merge($course, $tmpSecInstr);
219 }
220 *
221 */
222 if ($secondaryInstructors) {
223 //For some reason arrays cannot be passed, don't want to find out why
224 $course['MODULE_PARTS'] = implode(
225 '|',
226 array_map(function ($part) {
227 return $part['LASTNAME'] . ', ' . $part['FIRSTNAME'];
228 }, $secondaryInstructors)
229 );
230 }
231
232 foreach($course as &$field)
233 {
234 $field = EP_Util_String::utf8EncodeExtended($field);
235 }
236 $courses[] = $course;
237 }
238
239 return $courses;
240 }
241
242 //This class is not the best place for this, but don't want to refactore major parts of the project
243 private function createLogsTable() {
244 if (DB_TYPE == 'mysql') {
245 $sql = "
246 CREATE TABLE IF NOT EXISTS
247 `orbis_log` (
248 `id` INT(11) NOT NULL AUTO_INCREMENT,
249 `is_error` INT(11) NULL DEFAULT NULL,
250 `date` VARCHAR(255) NULL DEFAULT NULL,
251 `module_id` VARCHAR(255) NULL DEFAULT NULL,
252 `module_name` VARCHAR(255) NULL DEFAULT NULL,
253 `msg` VARCHAR(255) NULL DEFAULT NULL,
254 `survey_id` VARCHAR(255) NULL DEFAULT NULL,
255 `course_type` VARCHAR(255) NULL DEFAULT NULL,
256 PRIMARY KEY (`id`)
257 )
258 ENGINE = MYISAM
259 ";
260 } else {
261 $sql = "
262 IF OBJECT_ID(\'orbis_log\')
263 IS NULL
264 CREATE TABLE dbo.orbis_log (
265 id INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
266 is_error INT NULL DEFAULT NULL,
267 date VARCHAR(255) NULL DEFAULT NULL,
268 module_id VARCHAR(255) NULL DEFAULT NULL,
269 module_name VARCHAR(255) NULL DEFAULT NULL,
270 msg VARCHAR(255) NULL DEFAULT NULL,
271 survey_id VARCHAR(255) NULL DEFAULT NULL,
272 course_type VARCHAR(255) NULL DEFAULT NULL,
273 )
274 ";
275 }
276
277 $db = Zend_Registry::get('oDb');
278 $db->query($sql);
279 }
280}