Subversion-Projekte lars-tiefland.ci

Revision

Revision 2364 | Zur aktuellen Revision | Details | Vergleich mit vorheriger | Letzte Änderung | Log anzeigen | RSS feed

Revision Autor Zeilennr. Zeile
2243 lars 1 
<?php
2 
 
3 
/**
4 
 *
5 
 * @package WebanOS CI
6 
 * @author Lars Tiefland <ltiefland@gmail.com>
7 
 * @copyright 2016
8 
 * @version $Rev: 2365 $
9 
 */
10 
 
11 
class Logistiker_model extends CI_Model
12 
{
13 
	public function __construct()
14 
	{
15 
		parent::__construct();
16 
	}
17 
 
18 
	public function get_list($start = 0)
19 
	{
2244 lars 20 
		$sql = "SELECT
21 
				*
22 
			FROM
23 
				logistiker
24 
		";
2247 lars 25 
		$res = $this->db->query($sql);
2244 lars 26 
		while ($row = $res->unbuffered_row('array')) {
27 
			$logistiker[] = $row;
28 
		}
29 
		return $logistiker;
2243 lars 30 
	}
31 
 
32 
	public function get($id)
33 
	{
2244 lars 34 
		$sql = "SELECT
35 
				*
36 
			FROM
37 
				logistiker
38 
			WHERE
39 
				id=" . $id . "
40 
		";
2247 lars 41 
		$res = $this->db->query($sql);
2244 lars 42 
		$logistiker = $res->unbuffered_row('array');
43 
		return $logistiker;
2243 lars 44 
	}
45 
 
2327 lars 46 
	public function save($daten)
2243 lars 47 
	{
2364 lars 48 
		if (!$this->input->post('id')) {
2321 lars 49 
			$sql = "
50 
				INSERT INTO
51 
					logistiker
52 
				SET
53 
			";
2364 lars 54 
			foreach ($daten as $key => $value) {
55 
				$felder[] = $key . "=" . $GLOBALS["order_db"]->escape($value);
2321 lars 56 
			}
57 
			$sql .= implode(",", $felder);
58 
			$res = $this->db->query($sql);
59 
			$id = $this->db->insert_id();
2364 lars 60 
		} else {
2321 lars 61 
			$sql = "UPDATE
62 
					logistiker
63 
				SET
64 
			";
2364 lars 65 
			foreach ($daten as $key => $value) {
66 
				$felder[] = $key . "=" . $GLOBALS["order_db"]->escape($value);
2321 lars 67 
			}
68 
			$sql .= implode(",", $felder);
69 
			$sql .= "
70 
				WHERE
2364 lars 71 
					id=" . $this->input->post('id') . "
2321 lars 72 
			";
73 
			$res = $this->db->query($sql);
74 
		}
2364 lars 75 
		if ($res) {
2333 lars 76 
			echo "Daten wurden erfolgreich geändert!";
2364 lars 77 
		} else {
2333 lars 78 
			echo "Leider ist ein Fehler aufgetreten. Bitte versuchen Sie es später noch einmal!";
2321 lars 79 
		}
2243 lars 80 
	}
2364 lars 81 
 
2348 lars 82 
	public function del($id)
83 
	{
84 
		$sql = "DELETE FROM
85 
				logistiker
86 
			WHERE
2364 lars 87 
				id=" . $id . "
2348 lars 88 
		";
89 
		$res = $this->db->query($sql);
2364 lars 90 
		if ($res) {
2349 lars 91 
			echo "Logistiker wurde erfolgreich gelöscht!";
2364 lars 92 
		} else {
2348 lars 93 
			echo "Leider ist ein Fehler aufgetreten. Bitte versuchen Sie es später noch einmal!";
94 
		}
95 
	}
2283 lars 96 
 
2280 lars 97 
	public function export($id)
98 
	{
2309 lars 99 
		$aColumns = array("exported");
2314 lars 100 
		$sIndexColumn = "exported";
2313 lars 101 
		$sLimit = $this->input->get('iDisplayStart') . "," . $this->input->get('iDisplayLength');
2317 lars 102 
		if (isset($_GET['iSortCol_0'])) {
103 
			$sOrder = "ORDER BY  ";
104 
			for ($i = 0; $i < intval($this->input->get('iSortingCols')); $i++) {
105 
				if ($this->input->get('bSortable_' . intval($this->input->get('iSortCol_' . $i))) ==
106 
					"true") {
107 
					$sOrder .= $aColumns[intval($this->input->get('iSortCol_' . $i))] . "
2364 lars 108 
									" . $this->input->get('sSortDir_' . $i) . ", ";
2317 lars 109 
				}
110 
			}
111 
 
112 
			$sOrder = substr_replace($sOrder, "", -2);
113 
			if ($sOrder == "ORDER BY") {
114 
				$sOrder = "";
115 
			}
116 
		}
2365 lars 117 
		$sQuery = "
118 
			SELECT DISTINCT
119 
				exported
120 
			FROM
121 
				artikel_to_Bestellung ab
122 
			JOIN
123 
				bestellung_logistiker bl
124 
			ON
125 
				bl.logistiker=$id
126 
			AND
127 
				bl.bestellung = ab.Bestellung
128 
			WHERE
129 
				exported
2283 lars 130 
 
2365 lars 131 
			" . $sOrder . "
132 
			LIMIT " . $sLimit . "
133 
		";
2310 lars 134 
		trigger_error($sQuery);
2288 lars 135 
		$rResult = $GLOBALS['order_db']->query($sQuery);
2286 lars 136 
 
137 
		/* Data set length after filtering */
138 
		$sQuery = "
2364 lars 139 
						SELECT FOUND_ROWS() AS total
140 
				";
2288 lars 141 
		$rResultFilterTotal = $GLOBALS['order_db']->query($sQuery);
2312 lars 142 
		$aResultFilterTotal = $rResultFilterTotal->unbuffered_row('array');
2286 lars 143 
		$iFilteredTotal = $aResultFilterTotal["total"];
144 
 
145 
		/* Total data set length */
146 
		$sQuery = "
2364 lars 147 
			SELECT
148 
				COUNT(" . $sIndexColumn . ") AS total
149 
			FROM
150 
				artikel_to_Bestellung ab
151 
			JOIN
152 
				bestellung_logistiker bl
153 
			ON
154 
				bl.logistiker=$id
155 
			AND
156 
				bl.bestellung = ab.Bestellung
157 
			WHERE
158 
				exported
159 
		";
2288 lars 160 
		$rResultTotal = $GLOBALS['order_db']->query($sQuery);
2315 lars 161 
		$aResultTotal = $rResultTotal->unbuffered_row('array');
2286 lars 162 
		$iTotal = $aResultTotal["total"];
163 
 
164 
 
165 
		/*
166 
		* Output
167 
		*/
168 
		$output = array(
2316 lars 169 
			"sEcho" => intval($this->input->get('sEcho')),
2286 lars 170 
			"iTotalRecords" => $iTotal,
171 
			"iTotalDisplayRecords" => $iTotal,
172 
			"aaData" => array());
173 
 
174 
		$sql = "SELECT
2364 lars 175 
				export_script
176 
			FROM
177 
				logistiker
178 
			WHERE
179 
				id = " . $id . "
180 
		";
2288 lars 181 
		$res = $GLOBALS['order_db']->query($sql);
2286 lars 182 
		$nrow = $res->unbuffered_row('array');
183 
		if (strstr($nrow["export_script"], "=")) {
184 
			$qs = explode(";", $nrow["export_script"]);
185 
			foreach ($qs as $val) {
186 
				list($key, $wert) = explode("=", $val);
187 
				if ($key == "mode") {
188 
					$qsArr[] = "mode=" . $wert;
189 
				} elseif (is_null($wert)) {
190 
					$script = $key;
191 
				} elseif (!stristr($key, "status") && $wert) {
192 
					$qsArr[] = "$key=" . $wert;
193 
				}
194 
			}
195 
			$qs = implode("&", $qsArr);
196 
		} else {
197 
			list($script, $mode) = explode(";", $nrow["export_script"]);
198 
			$qs = "mode=" . $mode;
199 
		}
200 
		while ($aRow = $rResult->unbuffered_row('array')) {
201 
			$aRow['exported'] = '<a href="/Warenwirtschaft/' . $script . '?time=' . $aRow['exported'] .
202 
				'&id=' . $id . '&' . $qs . '" target="_blank">' . date('d.m.Y H:i:s', strtotime
203 
				($aRow['exported'])) . '</a>';
204 
			$row = array();
205 
			for ($i = 0; $i < count($aColumns); $i++) {
206 
				if ($aColumns[$i] == "version") {
207 
					/* Special output formatting for 'version' column */
208 
					$row[] = ($aRow[$aColumns[$i]] == "0") ? '-' : $aRow[$aColumns[$i]];
209 
				} else
210 
					if ($aColumns[$i] != ' ') {
211 
						/* General output */
212 
						$row[] = $aRow[$aColumns[$i]];
213 
					}
214 
			}
215 
			$output['aaData'][] = $row;
216 
		}
217 
 
218 
		return json_encode($output);
2280 lars 219 
	}
2283 lars 220 
 
2280 lars 221 
	public function tracking($id)
222 
	{
2303 lars 223 
		$aColumns = array(
224 
			'paketnummer',
225 
			'datum',
226 
			'bestellung',
227 
			);
2305 lars 228 
		if (isset($_GET['iSortCol_0'])) {
229 
			$sOrder = "ORDER BY  ";
230 
			for ($i = 0; $i < intval($this->input->get('iSortingCols')); $i++) {
231 
				if ($this->input->get('bSortable_' . intval($this->input->get('iSortCol_' . $i))) ==
232 
					"true") {
233 
					$sOrder .= $aColumns[intval($this->input->get('iSortCol_' . $i))] . "
2364 lars 234 
									" . $this->input->get('sSortDir_' . $i) . ", ";
2305 lars 235 
				}
236 
			}
237 
 
238 
			$sOrder = substr_replace($sOrder, "", -2);
239 
			if ($sOrder == "ORDER BY") {
240 
				$sOrder = "";
241 
			}
242 
		}
2285 lars 243 
		$sWhere = "";
2287 lars 244 
		if ($this->input->get('sSearch') != "") {
2285 lars 245 
			$sWhere = "WHERE (";
246 
			for ($i = 0; $i < count($aColumns); $i++) {
247 
				if ($aColumns[$i] == "datum") {
248 
					$a = " from_unixtime(datum) ";
2288 lars 249 
					$sWhere .= $a . " LIKE '%" . $GLOBALS['order_db']->escape($this->input->get('sSearch')) .
2285 lars 250 
						"%' OR ";
251 
				} else {
2295 lars 252 
					$sWhere .= $aColumns[$i] . " LIKE '%" . $GLOBALS['order_db']->escape($this->
253 
						input->get('sSearch')) . "%' OR ";
2285 lars 254 
				}
255 
 
256 
			}
257 
			$sWhere = substr_replace($sWhere, "", -3);
258 
			$sWhere .= ')';
2280 lars 259 
		}
2285 lars 260 
 
261 
		/* Individual column filtering */
262 
		for ($i = 0; $i < count($aColumns); $i++) {
2287 lars 263 
			if ($this->input->get('bSearchable_' . $i) == "true" && $this->input->get('sSearch_' .
264 
				$i) != '') {
2285 lars 265 
				if ($sWhere == "") {
266 
					$sWhere = "WHERE ";
267 
				} else {
268 
					$sWhere .= " AND ";
269 
				}
270 
				if ($aColumns[$i] == "datum") {
271 
					$r = " from_unixtime(datum) ";
2288 lars 272 
					$sWhere .= $r . " LIKE '%" . $GLOBALS['order_db']->escape($this->input->get('sSearch_' .
2287 lars 273 
						$i)) . "%' ";
2285 lars 274 
				} else {
2288 lars 275 
					$sWhere .= $r . " LIKE '%" . $GLOBALS['order_db']->escape($this->input->get('sSearch_' .
2287 lars 276 
						$i)) . "%' ";
2285 lars 277 
				}
278 
 
279 
			}
280 
		}
281 
 
282 
		if (empty($sWhere)) {
283 
			$sWhere = " where bp.logistiker=l.id AND l.id = $id ";
284 
		} else {
285 
			$sWhere = " $sWhere and bp.logistiker=l.id AND l.id = $id ";
286 
		}
2297 lars 287 
		$sLimit = $this->input->get('iDisplayStart') . "," . $this->input->get('iDisplayLength');
2364 lars 288 
		$sQuery = "
289 
			SELECT
290 
				SQL_CALC_FOUND_ROWS
291 
				bp.*,
292 
				l.kname,
293 
				l.trackinglink
294 
			FROM
295 
				logistiker l,
296 
				bestellung_paketnummern bp
2365 lars 297 
			" . $sWhere . "
298 
			" . $sOrder . "
299 
			LIMIT " . $sLimit . "
2364 lars 300 
		";
2285 lars 301 
		//echo $sQuery;
2288 lars 302 
		$rResult = $GLOBALS['order_db']->query($sQuery);
2285 lars 303 
 
304 
		/* Data set length after filtering */
305 
		$sQuery = "
2364 lars 306 
			SELECT FOUND_ROWS() AS total
307 
		";
2288 lars 308 
		$rResultFilterTotal = $GLOBALS['order_db']->query($sQuery);
2289 lars 309 
		$aResultFilterTotal = $rResultFilterTotal->unbuffered_row('array');
2285 lars 310 
		$iFilteredTotal = $aResultFilterTotal["total"];
311 
 
312 
		/* Total data set length */
313 
		$sQuery = "
2364 lars 314 
			SELECT
315 
				COUNT(bp.id) AS total
316 
			FROM
317 
				logistiker l,
318 
				bestellung_paketnummern bp
319 
			WHERE
320 
				bp.logistiker=l.id
321 
			AND
322 
				l.id = $id
323 
		";
2285 lars 324 
 
325 
 
2288 lars 326 
		$rResultTotal = $GLOBALS['order_db']->query($sQuery);
2294 lars 327 
		$aResultTotal = $rResultTotal->unbuffered_row('array');
2285 lars 328 
		$iTotal = $aResultTotal["total"];
329 
 
330 
 
331 
		/*
332 
		* Output
333 
		*/
334 
		$output = array(
2286 lars 335 
			"sEcho" => intval($this->input->get('sEcho')),
2285 lars 336 
			"iTotalRecords" => $iTotal,
337 
			"iTotalDisplayRecords" => $iFilteredTotal,
338 
			"aaData" => array());
339 
 
340 
		while ($aRow = $rResult->unbuffered_row('array')) {
341 
			//$aRow['datum'] = strtotime($aRow['datum']);
342 
			$aRow['datum'] = date('d.m.Y', $aRow['datum']);
2308 lars 343 
			$aRow['bestellung'] = ' <a target="_blank" href="/backend/order/edit/' . $aRow['bestellung'] .
2307 lars 344 
				'">zum Vorgang</a>';
2285 lars 345 
 
346 
			$url = $aRow["trackinglink"];
347 
			if (stristr($url, "%")) {
348 
				$tag = date("d", $row_p["datum"]);
349 
				$monat = date("m", $row_p["datum"]);
350 
				$jahr = date("Y", $row_p["datum"]);
351 
				$url = str_replace("%tag%", $tag, $url);
352 
				$url = str_replace("%monat%", $monat, $url);
353 
				$url = str_replace("%jahr%", $jahr, $url);
354 
				$url = str_replace("%trackingnr%", $aRow["paketnummer"], $url);
355 
			} else {
356 
				$url .= $aRow["paketnummer"];
357 
			}
358 
 
359 
 
360 
			$aRow["paketnummer"] = $aRow["kname"] . ': <a href=' . $url . ' target=_blank>' .
361 
				$aRow["paketnummer"] . '</a>';
362 
			$row = array();
363 
			for ($i = 0; $i < count($aColumns); $i++)
364 
				if ($aColumns[$i] == "version") {
365 
					/* Special output formatting for 'version' column */
366 
					$row[] = ($aRow[$aColumns[$i]] == "0") ? '-' : $aRow[$aColumns[$i]];
367 
				} else
368 
					if ($aColumns[$i] != ' ') {
369 
						/* General output */
370 
						$row[] = $aRow[$aColumns[$i]];
371 
					}
2304 lars 372 
			$output['aaData'][] = $row;
2285 lars 373 
		}
374 
 
375 
		return json_encode($output);
2280 lars 376 
	}
2285 lars 377 
 
2243 lars 378 
}
379 
 
380 
?>