Revision 1416 | Revision 1431 | 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: 1417 $*/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->session->filter;switch ($filter['versandstatus']){case 1:$versandstatus_filter = " ab.status = 'offen' ";break;case 2:$versandstatus_filter = " ab.status = 'freigegeben' ";break;case 3:$versandstatus_filter = " ab.status = 'versand' ";break;case 4:$versandstatus_filter = " ab.status = '' ";break;case 5:$versandstatus_filter = " ab.status = '' or ab.status = 'offen' ";break;default:$versandstatus_filter = "";break;}switch ($filter["status"]){case "op": // offene Posten$status_filter = " ab.status IN ('offen')";break;case "ib": // in Bearbeitung$status_filter = " ab.status IN ('in Bearbeitung')";break;case "re": // reserviert$status_filter = " ab.status IN ('reserviert')";break;case "op_ib": // offene Posten & in Bearbeitung$status_filter = " ab.status IN ('offen','in Bearbeitung')";break;case "op_ib_re": // offene Posten & in Bearbeitung$status_filter = " ab.status IN ('offen', 'in Bearbeitung','reserviert')";break;case "er": // erledigt$status_filter = " ab.status IN ('erledigt')";break;case "ab": // erledigt$status_filter = " ab.status IN ('abgeschlossen')";break;default: // alle$status_filter = "";break;}if (isset($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;}}switch ($filter['bezahlstatus']){case 1:$bezahlstatus_filter = " b.bezahlt=0 ";break;case 2:$bezahlstatus_filter = " b.bezahlt=1 ";break;case 3:$bezahlstatus_filter = " b.bezahlt=2 ";break;case 4:$bezahlstatus_filter = " b.bezahlt IN (1,2) ";break;default:$bezahlstatus_filter = "";break;}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"]["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%'";break;default: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"]]."%'";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"]["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"]."%' )";$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";}$countryTable = "countries co";$countryTable = "content_management.".$countryTable;$coFeld = "co.`name-ger`";$adresse_join = "LEFT JOINbestelladresse baShipONb.ship_addr_id = baShip.idLEFT JOINbestelladresse baBillONb.bill_addr_id = baBill.idLEFT JOIN".$countryTable."ShipONcoShip.id = baShip.landLEFTJOIN ".$countryTable."BillONcoBill.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_ROWSDISTINCT".$r_status_select."b.idFROMartikel_to_Bestellung AS abJOINBestellung AS bONb.ID=ab.BestellungJOINLogins AS lONl.ID=b.Login".$doc_join."".$bp_join."JOINshops sONs.id=b.shops_idJOINbestellart baONba.id=b.bestellart_idWHERE".implode(" AND ", $sql_filter)."ORDER BYb.id DESCLIMIT100";$res = $GLOBALS['order_db']->query($sql);echo $sql;$sql_anz = "SELECTFOUND_ROWS() AS total";$res_anz = $GLOBALS["order_db"]->query($sql_anz);$row = $res_anz->row_array();while ($order = $res->unbuffered_row('array')){$order = $this->get($order['id']);$ret["orders"][] = $order;}$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);}}}}?>