WebSVN – lars-tiefland.ci – /backend/models/Logistiker_model.php

<?php

/**
 *
 * @package WebanOS CI
 * @author Lars Tiefland <ltiefland@gmail.com> 
 * @copyright 2016
 * @version $Rev: 2363 $
 */

class Logistiker_model extends CI_Model
{
        public function __construct()
        {
                parent::__construct();
        }

        public function get_list($start = 0)
        {
                $sql = "SELECT
                                *
                        FROM
                                logistiker
                ";
                $res = $this->db->query($sql);
                while ($row = $res->unbuffered_row('array')) {
                        $logistiker[] = $row;
                }
                return $logistiker;
        }

        public function get($id)
        {
                $sql = "SELECT
                                *
                        FROM
                                logistiker
                        WHERE
                                id=" . $id . "
                ";
                $res = $this->db->query($sql);
                $logistiker = $res->unbuffered_row('array');
                return $logistiker;
        }

        public function save($daten)
        {
                if (!$this->input->post('id'))
                {
                        $sql = "
                                INSERT INTO
                                        logistiker
                                SET
                        ";
                        foreach ($daten as $key => $value)
                        {
                                $felder[] = $key."=".$GLOBALS["order_db"]->escape($value);
                        }
                        $sql .= implode(",", $felder);
                        $res = $this->db->query($sql);
                        $id = $this->db->insert_id();
                }
                else
                {
                        $sql = "UPDATE
                                        logistiker
                                SET
                        ";
                        foreach ($daten as $key => $value)
                        {
                                $felder[] = $key."=".$GLOBALS["order_db"]->escape($value);
                        }
                        $sql .= implode(",", $felder);
                        $sql .= "
                                WHERE
                                        id=".$this->input->post('id')."
                        ";
                        $res = $this->db->query($sql);
                }
                if ($res)
                {
                        echo "Daten wurden erfolgreich geändert!";
                }
                else
                {
                        echo "Leider ist ein Fehler aufgetreten. Bitte versuchen Sie es später noch einmal!";
                }
        }
        
        public function del($id)
        {
                $sql = "DELETE FROM
                                logistiker
                        WHERE
                                id=".$id."
                ";
                $res = $this->db->query($sql);
                if ($res)
                {
                        echo "Logistiker wurde erfolgreich gelöscht!";
                }
                else
                {
                        echo "Leider ist ein Fehler aufgetreten. Bitte versuchen Sie es später noch einmal!";
                }
        }

        public function export($id)
        {
                $aColumns = array("exported");
                $sIndexColumn = "exported";
                $sLimit = $this->input->get('iDisplayStart') . "," . $this->input->get('iDisplayLength');
                if (isset($_GET['iSortCol_0'])) {
                        $sOrder = "ORDER BY  ";
                        for ($i = 0; $i < intval($this->input->get('iSortingCols')); $i++) {
                                if ($this->input->get('bSortable_' . intval($this->input->get('iSortCol_' . $i))) ==
                                        "true") {
                                        $sOrder .= $aColumns[intval($this->input->get('iSortCol_' . $i))] . "
                                    " . $this->input->get('sSortDir_' . $i) .
                                                ", ";
                                }
                        }

                        $sOrder = substr_replace($sOrder, "", -2);
                        if ($sOrder == "ORDER BY") {
                                $sOrder = "";
                        }
                }
                $sQuery = "SELECT DISTINCT
                    exported
                FROM
                    artikel_to_Bestellung ab
                JOIN
                    bestellung_logistiker bl
                ON
                    bl.logistiker=$id
                AND
                    bl.bestellung = ab.Bestellung
                WHERE
                    exported

                " . $sOrder . "
                LIMIT " . $sLimit . "
            ";
                trigger_error($sQuery);
                $rResult = $GLOBALS['order_db']->query($sQuery);

                /* Data set length after filtering */
                $sQuery = "
                        SELECT FOUND_ROWS() AS total
                ";
                $rResultFilterTotal = $GLOBALS['order_db']->query($sQuery);
                $aResultFilterTotal = $rResultFilterTotal->unbuffered_row('array');
                $iFilteredTotal = $aResultFilterTotal["total"];

                /* Total data set length */
                $sQuery = "
                        SELECT
                    COUNT(" . $sIndexColumn . ") AS total
                FROM
                    artikel_to_Bestellung ab
                JOIN
                    bestellung_logistiker bl
                ON
                    bl.logistiker=$id
                AND
                    bl.bestellung = ab.Bestellung
                WHERE
                    exported
                ";
                $rResultTotal = $GLOBALS['order_db']->query($sQuery);
                $aResultTotal = $rResultTotal->unbuffered_row('array');
                $iTotal = $aResultTotal["total"];


                /*
                * Output
                */
                $output = array(
                        "sEcho" => intval($this->input->get('sEcho')),
                        "iTotalRecords" => $iTotal,
                        "iTotalDisplayRecords" => $iTotal,
                        "aaData" => array());

                $sql = "SELECT
                    export_script
                FROM
                    logistiker
                WHERE
                    id = " . $id . "
            ";
                $res = $GLOBALS['order_db']->query($sql);
                $nrow = $res->unbuffered_row('array');
                if (strstr($nrow["export_script"], "=")) {
                        $qs = explode(";", $nrow["export_script"]);
                        foreach ($qs as $val) {
                                list($key, $wert) = explode("=", $val);
                                if ($key == "mode") {
                                        $qsArr[] = "mode=" . $wert;
                                } elseif (is_null($wert)) {
                                        $script = $key;
                                } elseif (!stristr($key, "status") && $wert) {
                                        $qsArr[] = "$key=" . $wert;
                                }
                        }
                        $qs = implode("&", $qsArr);
                } else {
                        list($script, $mode) = explode(";", $nrow["export_script"]);
                        $qs = "mode=" . $mode;
                }
                while ($aRow = $rResult->unbuffered_row('array')) {
                        $aRow['exported'] = '<a href="/Warenwirtschaft/' . $script . '?time=' . $aRow['exported'] .
                                '&id=' . $id . '&' . $qs . '" target="_blank">' . date('d.m.Y H:i:s', strtotime
                                ($aRow['exported'])) . '</a>';
                        $row = array();
                        for ($i = 0; $i < count($aColumns); $i++) {
                                if ($aColumns[$i] == "version") {
                                        /* Special output formatting for 'version' column */
                                        $row[] = ($aRow[$aColumns[$i]] == "0") ? '-' : $aRow[$aColumns[$i]];
                                } else
                                        if ($aColumns[$i] != ' ') {
                                                /* General output */
                                                $row[] = $aRow[$aColumns[$i]];
                                        }
                        }
                        $output['aaData'][] = $row;
                }

                return json_encode($output);
        }

        public function tracking($id)
        {
                $aColumns = array(
                        'paketnummer',
                        'datum',
                        'bestellung',
                        );
                if (isset($_GET['iSortCol_0'])) {
                        $sOrder = "ORDER BY  ";
                        for ($i = 0; $i < intval($this->input->get('iSortingCols')); $i++) {
                                if ($this->input->get('bSortable_' . intval($this->input->get('iSortCol_' . $i))) ==
                                        "true") {
                                        $sOrder .= $aColumns[intval($this->input->get('iSortCol_' . $i))] . "
                                    " . $this->input->get('sSortDir_' . $i) .
                                                ", ";
                                }
                        }

                        $sOrder = substr_replace($sOrder, "", -2);
                        if ($sOrder == "ORDER BY") {
                                $sOrder = "";
                        }
                }
                $sWhere = "";
                if ($this->input->get('sSearch') != "") {
                        $sWhere = "WHERE (";
                        for ($i = 0; $i < count($aColumns); $i++) {
                                if ($aColumns[$i] == "datum") {
                                        $a = " from_unixtime(datum) ";
                                        $sWhere .= $a . " LIKE '%" . $GLOBALS['order_db']->escape($this->input->get('sSearch')) .
                                                "%' OR ";
                                } else {
                                        $sWhere .= $aColumns[$i] . " LIKE '%" . $GLOBALS['order_db']->escape($this->
                                                input->get('sSearch')) . "%' OR ";
                                }

                        }
                        $sWhere = substr_replace($sWhere, "", -3);
                        $sWhere .= ')';
                }

                /* Individual column filtering */
                for ($i = 0; $i < count($aColumns); $i++) {
                        if ($this->input->get('bSearchable_' . $i) == "true" && $this->input->get('sSearch_' .
                                $i) != '') {
                                if ($sWhere == "") {
                                        $sWhere = "WHERE ";
                                } else {
                                        $sWhere .= " AND ";
                                }
                                if ($aColumns[$i] == "datum") {
                                        $r = " from_unixtime(datum) ";
                                        $sWhere .= $r . " LIKE '%" . $GLOBALS['order_db']->escape($this->input->get('sSearch_' .
                                                $i)) . "%' ";
                                } else {
                                        $sWhere .= $r . " LIKE '%" . $GLOBALS['order_db']->escape($this->input->get('sSearch_' .
                                                $i)) . "%' ";
                                }

                        }
                }

                if (empty($sWhere)) {
                        $sWhere = " where bp.logistiker=l.id AND l.id = $id ";
                } else {
                        $sWhere = " $sWhere and bp.logistiker=l.id AND l.id = $id ";
                }
                $sLimit = $this->input->get('iDisplayStart') . "," . $this->input->get('iDisplayLength');
                $sQuery = "SELECT
                    SQL_CALC_FOUND_ROWS
                    bp.*,
                    l.kname,
                    l.trackinglink
                FROM
                    logistiker l,
                    bestellung_paketnummern bp
                    " . $sWhere . "
                    " . $sOrder . "
                                        LIMIT " . $sLimit . "
                ";
                //echo $sQuery;
                $rResult = $GLOBALS['order_db']->query($sQuery);

                /* Data set length after filtering */
                $sQuery = "
                        SELECT FOUND_ROWS() AS total
                ";
                $rResultFilterTotal = $GLOBALS['order_db']->query($sQuery);
                $aResultFilterTotal = $rResultFilterTotal->unbuffered_row('array');
                $iFilteredTotal = $aResultFilterTotal["total"];

                /* Total data set length */
                $sQuery = "
                        SELECT
                    COUNT(bp.id) AS total
                FROM
                    logistiker l,
                    bestellung_paketnummern bp
                WHERE
                    bp.logistiker=l.id
                AND
                    l.id = $id
                ";


                $rResultTotal = $GLOBALS['order_db']->query($sQuery);
                $aResultTotal = $rResultTotal->unbuffered_row('array');
                $iTotal = $aResultTotal["total"];


                /*
                * Output
                */
                $output = array(
                        "sEcho" => intval($this->input->get('sEcho')),
                        "iTotalRecords" => $iTotal,
                        "iTotalDisplayRecords" => $iFilteredTotal,
                        "aaData" => array());

                while ($aRow = $rResult->unbuffered_row('array')) {
                        //$aRow['datum'] = strtotime($aRow['datum']);
                        $aRow['datum'] = date('d.m.Y', $aRow['datum']);
                        $aRow['bestellung'] = ' <a target="_blank" href="/backend/order/edit/' . $aRow['bestellung'] .
                                '">zum Vorgang</a>';

                        $url = $aRow["trackinglink"];
                        if (stristr($url, "%")) {
                                $tag = date("d", $row_p["datum"]);
                                $monat = date("m", $row_p["datum"]);
                                $jahr = date("Y", $row_p["datum"]);
                                $url = str_replace("%tag%", $tag, $url);
                                $url = str_replace("%monat%", $monat, $url);
                                $url = str_replace("%jahr%", $jahr, $url);
                                $url = str_replace("%trackingnr%", $aRow["paketnummer"], $url);
                        } else {
                                $url .= $aRow["paketnummer"];
                        }


                        $aRow["paketnummer"] = $aRow["kname"] . ': <a href=' . $url . ' target=_blank>' .
                                $aRow["paketnummer"] . '</a>';
                        $row = array();
                        for ($i = 0; $i < count($aColumns); $i++)
                                if ($aColumns[$i] == "version") {
                                        /* Special output formatting for 'version' column */
                                        $row[] = ($aRow[$aColumns[$i]] == "0") ? '-' : $aRow[$aColumns[$i]];
                                } else
                                        if ($aColumns[$i] != ' ') {
                                                /* General output */
                                                $row[] = $aRow[$aColumns[$i]];
                                        }
                        $output['aaData'][] = $row;
                }

                return json_encode($output);
        }

}

?>
Subversion-Projekte lars-tiefland.ci

(root)/backend/models/Logistiker_model.php – Revision 2363
