Subversion-Projekte lars-tiefland.ci

Revision

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