Revision 1400 | Revision 1405 | Zur aktuellen Revision | Blame | Vergleich mit vorheriger | Letzte Änderung | Log anzeigen | RSS feed
<?php/**** @package WebanOS CI* @author Lars Tiefland <ltiefland@gmail.com>* @copyright 2016* @version $Rev: 1402 $*/class Order_model extends CI_Model{public function __construct(){parent::__construct();$this->load->model('bestelladresse_model', 'bestelladresse');$this->load->model('artikel_to_bestellung_model', 'artikel');$this->load->model('dokumente_model', 'dokumente');}public function get_list($start = 0){$filter = $this->input->post('filter');if (isset($GLOBALS["web_rechte"]["Warenwirtschaft"]["bestellung"]["bestellStatus"])){switch ($filter["bStatus"]){case "of_fr_ve":$bStatus_filter = "b.status IN ('offen','freigegeben','versand')";break;case "offen":case "wartend":case "storniert":case "freigegeben":case "versand":$bStatus_filter = " b.status='".$filter["bStatus"]."'";break;case "alle":$bStatus_filter = "";break;}}if ($filter["laender"]){switch ($filter["laender"]){case 0: //alle$sql_filter[] = "";break;case 1:if (isset($GLOBALS["web_rechte"]["Warenwirtschaft"]["bestellung"]["adressbuch"])){$coFeld = "co.country";if (isset($GLOBALS["web_rechte"]["general"]["laenderzuordnung"]["global"])){if ($GLOBALS["web_rechte"]["online_shop"]["language"]["standardsprache"] == "UK"){$coFeld = "co.`name-en`";$coFeldSip = "coShip.`name-en`";$coFeldBill = "coBill.`name-en`";}else{$coFeld = "co.`name-ger`";$coFeldShip = "coShip.`name-ger`";$coFeldBill = "coBill.`name-ger`";}}$sql_filter[] = $coFeldShip." NOT LIKE '%Deutschland%'";$sql_filter[] = $coFeldShip." NOT LIKE '%Germany%'";$sql_filter[] = $coFeldBill." NOT LIKE '%Deutschland%'";$sql_filter[] = $coFeldBill." NOT LIKE '%Germany%'";}else{$sql_filter[] = " b.liefer_Land NOT LIKE '%Deutschland%'";$sql_filter[] = " l.Land NOT LIKE '%Deutschland%'";$sql_filter[] = " b.liefer_Land NOT LIKE '%Germany%'";$sql_filter[] = " l.Land NOT LIKE '%Germany%'";}break;default:if (isset($GLOBALS["web_rechte"]["Warenwirtschaft"]["bestellung"]["adressbuch"])){$coFeld = "co.country";if (isset($GLOBALS["web_rechte"]["general"]["laenderzuordnung"]["global"])){if ($GLOBALS["web_rechte"]["online_shop"]["language"]["standardsprache"] == "UK"){$coFeld = "co.`name-en`";$coFeldSip = "coShip.`name-en`";$coFeldBill = "coBill.`name-en`";}else{$coFeld = "co.`name-ger`";$coFeldShip = "coShip.`name-ger`";$coFeldBill = "coBill.`name-ger`";}}$sql_filter[] = $coFeldShip." LIKE '%".$laender_filter[$filter["laender"]]."%'";$sql_filter[] = $coFeldBill." LIKE '%".$laender_filter[$filter["laender"]]."%'";}else{//Diese Wenn das Recht nicht gesetzt ist, dann müssen die Länder wieder decodiert werden, damit das SQL keine falschen Daten liefertif (!isset($GLOBALS["web_rechte"]["general"]["zeichenkodierung"]["utf-8"])){$suchland = html_entity_decode($laender_filter[$filter["laender"]], ENT_COMPAT,"ISO-8859-15");}else{$suchland = $laender_filter[$filter["laender"]];}$sql_filter[] = " (b.liefer_Land LIKE '%".$suchland."%' OR l.Land LIKE '%".$suchland."%')";}break;}}if (isset($GLOBALS["web_rechte"]["Warenwirtschaft"]["logistiker"]["logistiker"])){switch ($filter["logistiker"]){case 0:$logistiker_filter = "";break;default:$logistiker_filter = "btl.logistiker = ".$filter["logistiker"];}}switch ($filter["zeit"]){case 0: // alle$time_filter = "";break;case 1: // heute$time_filter = " current_date()=date_format(b.erstellt_am,\"%Y-%m-%d\") ";break;case 2: // gestern$time_filter = " date_sub(current_date(), INTERVAL 1 DAY)=date_format(b.erstellt_am,\"%Y-%m-%d\") ";break;case 3: // vorgestern$time_filter = " date_sub(current_date(), INTERVAL 2 DAY)=date_format(b.erstellt_am,\"%Y-%m-%d\") ";break;case 4: // lfd. Woche$time_filter = " date_format(b.erstellt_am,\"%U%Y\") = date_format(NOW(),\"%U%Y\")";break;case 5: // letzte Woche$time_filter = " date_format(b.erstellt_am,\"%U%Y\") = date_format(date_sub(NOW(), INTERVAL 7 DAY),\"%U%Y\")";break;case 6: // lfd. Monat$time_filter = " date_format(b.erstellt_am,\"%m%Y\") = date_format(NOW(),\"%m%Y\")";break;case 7: // letzter Monat$time_filter = " date_format(b.erstellt_am,\"%m%Y\") = date_format(date_sub(NOW(), INTERVAL 1 MONTH),\"%m%Y\")";break;case 8: // letzter Monat$time_filter = " date_format(b.erstellt_am,\"%m%Y\") = date_format(date_sub(NOW(), INTERVAL 2 MONTH),\"%m%Y\")";break;case 9: // letzter Monat$time_filter = " date_format(b.erstellt_am,\"%m%Y\") = date_format(date_sub(NOW(), INTERVAL 3 MONTH),\"%m%Y\")";break;case 10: // letzter Monat$time_filter = " date_format(b.erstellt_am,\"%m%Y\") = date_format(date_sub(NOW(), INTERVAL 4 MONTH),\"%m%Y\")";break;case 11: // letzter Monat$time_filter = " date_format(b.erstellt_am,\"%m%Y\") = date_format(date_sub(NOW(), INTERVAL 5 MONTH),\"%m%Y\")";break;case 12: // letzter Monat$time_filter = " date_format(b.erstellt_am,\"%m%Y\") = date_format(date_sub(NOW(), INTERVAL 6 MONTH),\"%m%Y\")";break;case 13: // letzter Monat$time_filter = " date_format(b.erstellt_am,\"%m%Y\") = date_format(date_sub(NOW(), INTERVAL 7 MONTH),\"%m%Y\")";break;case 14: // Aktuelles Jahr$time_filter = " date_format(b.erstellt_am,\"%Y\") = date_format(NOW(),\"%Y\")";break;case 15: // Letztes Jahr$anfangletztesJahr = (date('Y') - 1)."-01-01 00:00:00";$endeletztesJahr = (date('Y') - 1)."-12-31 23:59:59";$time_filter = " b.erstellt_am BETWEEN '".$anfangletztesJahr."' AND '".$endeletztesJahr."'";break;default: // alle$time_filter = "";break;}if ($filter["dokument"] && isset($GLOBALS["user_rechte"]["Warenwirtschaft"]["bestellung"]["suche"])){$doc_join = "JOINdokumente dONd.data_foreign=b.idANDtable_foreign='Bestellung'";$sql_filter[] = "document LIKE '%".$filter["dokument"]."%'";}//print_r($filter);if (!empty($filter["versanddatum"])){$sql_filter[] = " bd.EarliestShipDate like '%".$filter["versanddatum"]."%' ";// echo $sql_filter;}if ($time_filter){$sql_filter[] = $time_filter;}if ($status_filter){$sql_filter[] = $status_filter;}if ($bezahlstatus_filter){$sql_filter[] = $bezahlstatus_filter;}if ($versandstatus_filter){$sql_filter[] = $versandstatus_filter;}if ($bStatus_filter){$sql_filter[] = $bStatus_filter;}if ($logistiker_filter){$sql_filter[] = $logistiker_filter;}if (isset($filter["bestellung_ID"]) && $filter["bestellung_ID"]){$sql_filter[] = " ab.Bestellung=".$filter["bestellung_ID"];}if (isset($filter["fremd_ID"]) && $filter["fremd_ID"]){$sql_filter[] = " b.fremd_id LIKE '%".$filter["fremd_ID"]."%'";}if (isset($filter["status1"]) && $filter["status1"]){$sql_filter[] = " status1 = '".$filter["status1"]."'";}if (isset($filter["shortline1"]) && $filter["shortline1"]){$sql_filter[] = " shortline1 LIKE '%".$filter["shortline1"]."%'";}if (isset($_GET["login_id"]) && $_GET["login_id"]){$sql_filter[] = "b.Login=".$_GET["login_id"];$limit = "LIMIT ".SmartyPaginate::getCurrentIndex($pagination_id).", ".SmartyPaginate::getLimit($pagination_id);}$this->smarty->assign("script_name", "/Warenwirtschaft/bestellungen_neu.php");// <!-- M.Niewerth 22.04.2004-->$is_string = (strlen($filter["kundensuche"]) > 1 && strlen($filter["kundensuche"]) <$max_string_lenght) ? true : false;$fields = array(0 => "Vorname","Name1","Name2","Strasse","PLZ","ORT","Telefon","Fax","E_Mail","ID",);if (column_exists("Login", "ebay_UserID", $webs["datenbank"], $remoteServer_dbh)){$fields[] = "ebay_UserID";}//Array welches die Felder in der Bestelladresse enthält$fields_neu = array(0 => "vorname","nachname","firma","strasse1","plz","ort","telefon","fax","email",);switch ($is_string){case false: // leere Zeichenketteunset($kundensuche_filter);unset($tabelle_login);break;case true:$kundensuche_filter = " ( ";foreach (array_keys($fields) as $elem){$kundensuche_filter .= " (l.".$fields[$elem]." like '%".$filter["kundensuche"]."%') ";//Wenn das Recht gesetzt ist, muss zusätzlich in der Bestelladresse gesucht werden weil Gastbestellungen die Logins_ID 1 haben und somit nicht gefunden werdenif (isset($GLOBALS["web_rechte"]["Warenwirtschaft"]["bestellung"]["adressbuch"]) ||column_exists("Bestellung", "ship_addr_id", $webs["datenbank"], $remoteServer_dbh)){//Da es weniger felder im neuem Array gibt, muss geprüft werden, ob das Feld überhaupt gesetzt ist.if (isset($fields_neu[$elem])){$kundensuche_filter .= " or (baShip.".$fields_neu[$elem]." like '%".$filter["kundensuche"]."%') ";$kundensuche_filter .= " or (baBill.".$fields_neu[$elem]." like '%".$filter["kundensuche"]."%') ";}}$kundensuche_filter .= ($elem + 1 == count($fields)) ? "" : " or ";}$kundensuche_filter .= "OR (b.erstellt_von LIKE '".$filter["kundensuche"]."')";if (isset($GLOBALS["web_rechte"]["Warenwirtschaft"]["bestellung"]["adressbuch"])){$coFeld = "co.country";if (isset($GLOBALS["web_rechte"]["general"]["laenderzuordnung"]["global"])){if ($GLOBALS["web_rechte"]["online_shop"]["language"]["standardsprache"] == "UK"){$coFeld = "co.`name-en`";$coFeldSip = "coShip.`name-en`";$coFeldBill = "coBill.`name-en`";}else{$coFeld = "co.`name-ger`";$coFeldShip = "coShip.`name-ger`";$coFeldBill = "coBill.`name-ger`";}}$kundensuche_filter .= " or ( ".$coFeldShip." LIKE '%".$filter["kundensuche"]."%' )";$kundensuche_filter .= " or ( ".$coFeldBill." LIKE '%".$filter["kundensuche"]."%' )";}else{//MK: Lieferland auch abfragen$kundensuche_filter .= " or ( b.liefer_Land like '%".$filter["kundensuche"]."%' )";}$kundensuche_filter .= " ) ";$sql_filter[] = $kundensuche_filter;unset($kundensuche_filter);break;default: // standardunset($kundensuche_filter);break;}switch ($filter["rechnungsstatus"]){case "S":$r_status_filter = " ab.rechnungstatus='schreiben'";break;case "G":$r_status_filter = " ab.rechnungstatus='geschrieben'";break;case "B":$r_status_filter = " ab.rechnungstatus='bezahlt'";break;case "O":$r_status_filter = " ab.rechnungstatus=''";$r_status_select = "SUM(IF(ab.rechnungstatus='',1,0)) AS sum_ohne,";if ($filter["status"] != ""){$r_status_group = " GROUP BY Bestellung HAVING sum_ohne >= 1";}else{$r_status_group = " GROUP BY Bestellung HAVING sum_ohne > 1";}break;case "":default:unset($r_status_filter);}if ($filter["shopID"] && !$_GET["login_id"]){$sql_filter[] = " b.shops_ID=".$filter["shopID"];}if ($filter["rechnungsstatus"]){$sql_filter[] = $r_status_filter;}if ($filter["bestellart_id"] != "" && !$_GET["login_id"]){$bestellart_id = $filter["bestellart_id"];$sql_filter[] = "bestellart_id=$bestellart_id";}// Anfang Artikelsuche 05.10.2004$is_numeric = is_numeric($filter["artikelsuche"]);$is_string = (strlen($filter["artikelsuche"]) > 1 && strlen($filter["artikelsuche"]) <$max_string_lenght) ? true : false;if ($is_numeric){$is_string = false;}$fields = array(0 => "Bezeichnung");switch ($is_string){case false: // leere Zeichenketteif ($is_numeric){$artikelsuche_filter = "ab.artikel=".$filter["artikelsuche"];$sql_filter[] = $artikelsuche_filter;}else{unset($artikelsuche_filter);}break;case true:$artikelsuche_filter = " ( ";foreach (array_keys($fields) as $elem){$artikelsuche_filter .= " (ab.".$fields[$elem]." like '%".$filter["artikelsuche"]."%') ";$artikelsuche_filter .= ($elem + 1 == count($fields)) ? "" : " or ";}$artikelsuche_filter .= " ) ";$sql_filter[] = $artikelsuche_filter;unset($artikelsuche_filter);break;default: // standardunset($artikelsuche_filter);break;}// Ende Artikelsucheif ($filter["paketnummer"] && $filter_pn){$sql_filter[] = "bp.paketnummer LIKE '%".$filter["paketnummer"]."%'";$bp_join = "JOIN bestellung_paketnummern bp ON bp.bestellung=b.id";}//$order = "Bestellung DESC";if ($filter["fremd_ID"] || $filter["bestellung_ID"]){$limit = "";}else{$limit = "LIMIT ".$start.", 100";}if (isset($GLOBALS["web_rechte"]["Warenwirtschaft"]["bestellung"]["adressbuch"]) ||column_exists("Bestellung", "ship_addr_id", $webs["datenbank"], $remoteServer_dbh)){$countryTable = "countries co";if (isset($GLOBALS["web_rechte"]["general"]["laenderzuordnung"]["global"])){$countryTable = "content_management.".$countryTable;}elseif (!isset($GLOBALS["web_rechte"]["logins"]['admin']['adressverwaltung']) &&column_exists("Bestellung", "ship_addr_id", $webs["datenbank"], $remoteServer_dbh)){$countryTable = "content_management.".$countryTable;$coFeld = "co.`name-ger`";}$adresse_join = "LEFT JOIN bestelladresse baShipON b.ship_addr_id = baShip.idLEFT JOIN bestelladresse baBillON b.bill_addr_id = baBill.idLEFT JOIN ".$countryTable."ShipON coShip.id = baShip.landLEFT JOIN ".$countryTable."BillON coBill.id = baBill.land";}if (isset($GLOBALS["web_rechte"]["Warenwirtschaft"]["logistiker"]["logistiker"]) &&$logistiker_filter != ""){$logistiker_join = "JOIN bestellung_logistiker btl ON btl.bestellung = b.ID";}if (!$sql_filter){$sql_filter[] = "1";}$ret = array("orders" => array(),"orderAnz" => 0,);$sql = "SELECT SQL_CALC_FOUND_ROWSb.idFROMbestellart ba,Logins AS l,shops AS s,artikel_to_Bestellung ab,Bestellung bWHERE".implode(" AND ", $sql_filter)."ANDl.ID = b.LoginANDs.ID = b.shops_IDANDab.bestellung=b.idANDba.id=b.bestellart_idORDER BYb.id DESCLIMIT100";$res = $GLOBALS['order_db']->query($sql);while ($order = $res->unbuffered_row('array')){$order = $this->get($order['id']);$ret["orders"][] = $order;}$sql = "SELECTFOUND_ROWS() AS total";$res = $GLOBALS["order_db"]->query($sql);$row = $res->row_array();$ret["orderAnz"] = $row["total"];return $ret;}public function get($id){$orders = array();$sql = "SELECTb.id,b.language,b.shops_ID,b.login,bill_addr_id,ship_addr_id,erstellt_am,erstellt_von,letzte_aenderung_am,letzte_aenderung_von,status,gesamtsumme_buffer,fremd_id,artikelstatus_buffer,rechnungsstatus_buffer,lieferscheinstatus_buffer,bezahlt,Bemerkung,Bemerkung_extern,bezahlt_am,s.name AS shop,ba.name AS bestellartFROMBestellung bJOINshops sONs.id=b.shops_idJOINbestellart baONba.id=b.bestellart_idWHEREb.id=".$id."";$res = $GLOBALS['order_db']->query($sql);$order = $res->unbuffered_row('array');$billAddr = $this->bestelladresse->get($order['bill_addr_id']);$shipAddr = $this->bestelladresse->get($order['ship_addr_id']);$artikel = $this->artikel->get($order['id']);$dokumente = $this->dokumente->get_list($order['id']);$artikelstatus_buffer = unserialize($order["artikelstatus_buffer"]);foreach ($artikelstatus_buffer as $key => $value){switch ($key){case "statusOffen":$info[$key]["class"] = "status_red";break;case "statusInBearbeitung":$info[$key]["class"] = "status_yellow";break;case "statusReserviert":$info[$key]["class"] = "status_blue";break;case "statusErledigt":$info[$key]["class"] = "status_green";break;case "statusAbgeschlossen":default:$info[$key]["class"] = "status_white";break;}$info[$key]["wert"] = $value;}$order["info"] = $info;$rechnungsstatus_buffer = unserialize($order["rechnungsstatus_buffer"]);foreach ($rechnungsstatus_buffer as $key => $value){switch ($key){case "rechnungSchreiben":$rstatus[$key]["class"] = "status_red";break;case "rechnungGeschrieben":$rstatus[$key]["class"] = "status_yellow";break;case "rechnungBezahlt":$rstatus[$key]["class"] = "status_green";break;}$rstatus[$key]["wert"] = $value;}$order["rechnungsstatus"] = $rstatus;$lieferscheinstatus_buffer = unserialize($order["lieferscheinstatus_buffer"]);foreach ($lieferscheinstatus_buffer as $key => $value){switch ($key){case "lieferscheinSchreiben":$lstatus[$key]["class"] = "status_yellow";break;case "lieferscheinGeschrieben":$lstatus[$key]["class"] = "status_green";break;case "lieferscheinOffen":$lstatus[$key]["class"] = "status_red";break;}$lstatus[$key]["wert"] = $value;}$order['lieferscheinstatus'] = $lstatus;$order['dokumente'] = $dokumente;$order['artikel'] = $artikel;$order['shipAddr'] = $shipAddr;$order['billAddr'] = $billAddr;return $order;}public function save(){$Bestellung = $this->input->post('Bestellung');if ((isset($_GET["update"]) && $_GET["update"]) || (isset($_POST["update"]) && $_POST["update"])){if ($_POST["update"]){$update = $_POST["update"];}else{$update = $_GET["update"];}if ($_POST["cmd"]){foreach (array_keys($_POST["cmd"]) as $table){foreach (array_keys($_POST["cmd"][$table]) as $primaryKeyValue){$changed_id = "artikel_".$primaryKeyValue."_changed";if ($_POST[$changed_id] == 1){foreach (array_keys($_POST["cmd"][$table][$primaryKeyValue]) as $tablefield){$query = "UPDATE".$table."SET".$tablefield." = '".$GLOBALS["order_db"]->escape(html_entity_decode($_POST["cmd"][$table][$primaryKeyValue][$tablefield],ENT_COMPAT, $_SESSION["charset"]))."'WHEREID = '".$primaryKeyValue."'";$result = $GLOBALS["order_db"]->query($query);}}}}}$upd_fields[] = "Bemerkung=".$GLOBALS["order_db"]->escape($_POST["Bemerkung"])."";if (isset($user_rechte["Warenwirtschaft"]["bestellung"]["shortline1"])){$upd_fields[] = "shortline1 = ".$GLOBALS["order_db"]->escape($_POST["shortline1"])."";}if (isset($user_rechte["Warenwirtschaft"]["bestellung"]["shortline2"])){$upd_fields[] = "shortline2 = ".$GLOBALS["order_db"]->escape($_POST["shortline2"])."'";}if (isset($user_rechte["Warenwirtschaft"]["bestellung"]["status1"])){$upd_fields[] = "status1 = '".$GLOBALS["order_db"]->escape($_POST["status1"])."";}if (isset($user_rechte["Warenwirtschaft"]["bestellung"]["Bemerkung_extern"])){$upd_fields[] = "Bemerkung_extern=".$GLOBALS["order_db"]->escape($_POST["Bemerkung_extern"])."";}if (isset($user_rechte["Warenwirtschaft"]["bezahlstatus"]["bezahlstatus"])){$upd_fields[] = "bezahlt=".$GLOBALS["order_db"]->escape($_POST["bezahlt"])."";$upd_fields[] = "bezahlt_bemerkung=".$GLOBALS["order_db"]->escape($_POST["bezahlt_bemerkung"])."";}if (isset($_POST['fremd_id'])){$upd_fields[] = "fremd_id=".$GLOBALS["order_db"]->escape($_POST["fremd_id"])."";}$sql = "SELECTerstellt_vonFROMBestellungWHEREID=".$Bestellung."";$result = $GLOBALS["order_db"]->query($sql);$zeile = $result->unbuffered_row('array');if ($zeile["erstellt_von"] == ''){$upd_fields[] = "erstellt_von='".$this->session->user_data['user']."'";}if ($_POST["s_changed"] == 1){$upd_fields[] = "shops_ID=".$_POST["s_id"];}$sql = "UPDATEBestellungSETletzte_Aenderung_von='".$this->session->user_data['user']."',".implode(",", $upd_fields)."WHEREID=".$Bestellung."";$GLOBALS["order_db"]->query($sql);if (isset($_POST["logistiker_id"])){if ($_POST["logistiker_id"]){$sql_bl = "REPLACE INTObestellung_logistikerSETbestellung=".$Bestellung.",logistiker=".$_POST["logistiker_id"]."";$GLOBALS["order_db"]->query($sql_bl);if (isset($web_rechte["admin"]["toolbox"]["logbuch"])){$GLOBALS["Log"]["best"]->log("Logistiker der Bestellung geändert", $Bestellung);}}else{$sql_bl = "DELETE FROMbestellung_logistikerWHEREbestellung=".$Bestellung."";$GLOBALS["order_db"]->query($sql_bl);if (isset($web_rechte["admin"]["toolbox"]["logbuch"])){$GLOBALS["Log"]["best"]->log("Logistiker der Bestellung geändert", $Bestellung);}}}if (isset($update["current"])){header("Location: /backend/order/edit/".$Bestellung);}elseif (isset($update["list"])){header("Location: /backend/order/index/".$Bestellung);}}}}?>