PHP sql::fetchOne方法代码示例

 public function setRecord($data)
 {
     $data[blocktype] = $this->blocktype;
     $operation[$data[operation_id]] = array('date' => $data[action_date], 'comment_id' => sqltable_model::getCommentId($data[comment]));
     // если в поле для нового ввели номер сопроводиловки уже существующий в журнале
     // то будем править его
     $sql = "SELECT * FROM {$this->maintable} WHERE lanch_id='{$data[lanch_id]}'";
     $res = sql::fetchOne($sql);
     if (empty($res)) {
         // гадство! тут нужен уникальный, а без коментариев будет получаться один
         sql::insert('coments', array(array("comment" => multibyte::Json_encode($operation))));
         $data[coment_id] = sql::lastId();
     } else {
         $coment = multibyte::Json_decode(sqltable_model::getComment($res[coment_id]));
         $coment[$data[operation_id]] = $operation[$data[operation_id]];
         // заменить старый по ключу
         sql::insertUpdate('coments', array(array("id" => $res[coment_id], "comment" => multibyte::Json_encode($coment))));
         $data[edit] = $res[id];
         // если был такой его и правим
         $data[coment_id] = $res[coment_id];
     }
     $data[lastoperation] = $data[operation_id];
     parent::setRecord($data);
     return true;
 }

 /**
  * Получет две даты и выдает данные за период
  */
 public function getRangePeriod($sdate, $edate)
 {
     $ret = array();
     //console::getInstance()->out("$sdate nnn $edate");
     $sdate = date("Y-m-d", mktime(0, 0, 0, substr($sdate, 3, 2), substr($sdate, 0, 2), substr($sdate, 6, 4)));
     //$dyear."-".$dmonth."-".$dday;
     $edate = date("Y-m-d", mktime(0, 0, 0, substr($edate, 3, 2), substr($edate, 0, 2), substr($edate, 6, 4)));
     //$dyear."-".$dmonth."-".$dday;
     // выбор всех непустых названий
     $sql = "SELECT *,sk_{$this->sklad}_spr.id FROM {$this->db}sk_{$this->sklad}_spr\n\t\t\t\tJOIN {$this->db}sk_{$this->sklad}_ost ON sk_{$this->sklad}_ost.spr_id=sk_{$this->sklad}_spr.id\n\t\t\t\tWHERE nazv<>''\n\t\t\t\tORDER BY nazv";
     $res = sql::fetchAll($sql);
     foreach ($res as $rs) {
         // Запрос тот же что и для прихода, но берем последний и смотрим только последний и его цену
         $sql = "SELECT price as newprice FROM ({$this->db}sk_{$this->sklad}_dvizh)\n\t\t\t\t\tJOIN {$this->db}sk_{$this->sklad}_spr ON (sk_{$this->sklad}_spr.id=sk_{$this->sklad}_dvizh.spr_id)\n\t\t\t\t\tWHERE ddate >= '{$sdate}'\n\t\t\t\t\t\t\tAND ddate <= '{$edate}'\n\t\t\t\t\t\t\tAND sk_{$this->sklad}_spr.id='{$rs[id]}'\n\t\t\t\t\t\t\tAND type='1'\n\t\t\t\t\t\t\tAND numd<>'9999'\n\t\t\t\t\tORDER BY ddate DESC LIMIT 1";
         //echo $sql;
         $res1 = sql::fetchOne($sql);
         $newprice = $res1["newprice"];
         // почти такой же запрос, толь до первой даты даст старую цену
         $sql = "SELECT price as oldprice FROM ({$this->db}sk_{$this->sklad}_dvizh)\n\t\t\t\t\tJOIN {$this->db}sk_{$this->sklad}_spr ON (sk_{$this->sklad}_spr.id=sk_{$this->sklad}_dvizh.spr_id)\n\t\t\t\t\tWHERE ddate < '{$sdate}'\n\t\t\t\t\t\t\tAND sk_{$this->sklad}_spr.id='{$rs[id]}'\n\t\t\t\t\t\t\tAND type='1'\n\t\t\t\t\t\t\tAND numd<>'9999'\n\t\t\t\t\tORDER BY ddate DESC LIMIT 1";
         //echo $sql;
         $res1 = sql::fetchOne($sql);
         $oldprice = $res1["oldprice"];
         // если цены отичаются добавим в вывод
         if ($newprice != $oldprice && $oldprice != 0 && $newprice != 0) {
             // проверка на ноль, чтобы не делить на него при вычислении соотношения
             $col[nazv] = $rs[nazv];
             $col[newprice] = sprintf("%10.2f", $newprice);
             $col[oldprice] = sprintf("%10.2f", $oldprice);
             $col[ratio] = sprintf("%+10.2f", ($newprice - $oldprice) * 100 / $oldprice);
             $ret[] = $col;
         }
     }
     return $ret;
 }

 public function getNeedArc()
 {
     return false;
     //чтото  глючит или ктото. архивнули движения
     $sql = "SELECT YEAR(NOW())>(YEAR(sk_{$this->sklad}_dvizh_arc.ddate)+1) AS need\n                FROM {$this->db}sk_{$this->sklad}_dvizh_arc\n                ORDER BY ddate DESC LIMIT 1";
     $rs = sql::fetchOne($sql);
     return $rs[need];
 }

 public function getRecord($edit)
 {
     extract($edit);
     foreach ($id as $key => $value) {
         $sql = "SELECT *,sk_{$this->sklad}_spr.id\n                FROM {$this->db}sk_{$this->sklad}_dvizh\n                JOIN {$this->db}sk_{$this->sklad}_spr\n                ON sk_{$this->sklad}_dvizh.spr_id=sk_{$this->sklad}_spr.id\n                WHERE ddate='{$ddate}' AND {$this->db}sk_{$this->sklad}_spr.id='{$value}'";
         $ret[] = sql::fetchOne($sql);
     }
     return $ret;
 }

 public function getRecord($edit)
 {
     if (empty($edit)) {
         $rec[customers] = $this->getCustomers();
         return $rec;
     }
     $sql = "SELECT * FROM orders WHERE id='{$edit}'";
     $rec = sql::fetchOne($sql);
     $rec[files] = $this->getFilesForId('orders', $edit);
     return $rec;
 }

 public function getRights($userid)
 {
     $sql = "SELECT * FROM rtypes";
     $res = sql::fetchAll($sql);
     $sql = "SELECT * FROM rrtypes";
     $res1 = sql::fetchAll($sql);
     $out = array();
     foreach ($res as $rs) {
         $rec[type] = $rs[type];
         $rec[name] = "r|{$rs["id"]}";
         foreach ($res1 as $rs1) {
             $sql = "SELECT * FROM rights WHERE type_id='{$rs["id"]}' AND u_id='{$userid}' AND rtype_id='{$rs1["id"]}'";
             $rs2 = sql::fetchOne($sql);
             $value[$rs1["id"]] = $rs2["right"] == 1 ? 1 : 0;
             $values[$rs1["id"]] = $rs1["rtype"];
         }
         $rec[value] = $value;
         $rec[values] = $values;
         $out[types][] = $rec;
     }
     return $out;
 }

 public function getRangePeriod($sdate, $edate)
 {
     $ret = array();
     console::getInstance()->out("{$sdate} nnn {$edate}");
     $sdate = date("Y-m-d", mktime(0, 0, 0, substr($sdate, 3, 2), substr($sdate, 0, 2), substr($sdate, 6, 4)));
     //$dyear."-".$dmonth."-".$dday;
     $edate = date("Y-m-d", mktime(0, 0, 0, substr($edate, 3, 2), substr($edate, 0, 2), substr($edate, 6, 4)));
     //$dyear."-".$dmonth."-".$dday;
     $sql = "SELECT *,sk_{$this->sklad}_spr.id FROM {$this->db}sk_{$this->sklad}_spr\n\t\t\t\tJOIN {$this->db}sk_{$this->sklad}_ost ON sk_{$this->sklad}_ost.spr_id=sk_{$this->sklad}_spr.id\n\t\t\t\tWHERE nazv<>''\n\t\t\t\tORDER BY nazv";
     $res = sql::fetchAll($sql);
     foreach ($res as $rs) {
         $prih = 0;
         $rash = 0;
         $sql = "SELECT SUM(quant) as prihod FROM ({$this->db}sk_{$this->sklad}_dvizh)\n\t\t\t\t\tJOIN {$this->db}sk_{$this->sklad}_spr ON (sk_{$this->sklad}_spr.id=sk_{$this->sklad}_dvizh.spr_id)\n\t\t\t\t\tWHERE ddate >= '{$sdate}'\n\t\t\t\t\t\t\tAND ddate <= '{$edate}'\n\t\t\t\t\t\t\tAND sk_{$this->sklad}_spr.id='{$rs[id]}'\n\t\t\t\t\t\t\tAND type='1'\n\t\t\t\t\t\t\tAND numd<>'9999'\n\t\t\t\t\tGROUP BY sk_{$this->sklad}_spr.id";
         //echo $sql;
         $res1 = sql::fetchAll($sql);
         foreach ($res1 as $rs1) {
             $prih += $rs1["prihod"];
         }
         $sql = "SELECT SUM(quant) as prihod FROM ({$this->db}sk_{$this->sklad}_dvizh_arc)\n\t\t\t\t\tJOIN {$this->db}sk_{$this->sklad}_spr ON (sk_{$this->sklad}_spr.id=sk_{$this->sklad}_dvizh_arc.spr_id)\n\t\t\t\t\tWHERE ddate >= '{$sdate}'\n\t\t\t\t\t\t\tAND ddate <= '{$edate}'\n\t\t\t\t\t\t\tAND sk_{$this->sklad}_spr.id='{$rs[id]}'\n\t\t\t\t\t\t\tAND type='1'\n\t\t\t\t\t\t\tAND numd<>'9999'\n\t\t\t\t\t\t\tGROUP BY sk_{$this->sklad}_spr.id";
         $res1 = sql::fetchAll($sql);
         foreach ($res1 as $rs1) {
             $prih += $rs1["prihod"];
         }
         $sql = "SELECT SUM(quant) as prihod FROM ({$this->db}sk_{$this->sklad}_dvizh)\n\t\t\t\t\tJOIN {$this->db}sk_{$this->sklad}_spr ON (sk_{$this->sklad}_spr.id=sk_{$this->sklad}_dvizh.spr_id)\n\t\t\t\t\tWHERE ddate >= '{$sdate}'\n\t\t\t\t\t\t\tAND ddate <= '{$edate}'\n\t\t\t\t\t\t\tAND sk_{$this->sklad}_spr.id='{$rs[id]}'\n\t\t\t\t\t\t\tAND type='0'\n\t\t\t\t\t\t\tAND numd<>'9999'\n\t\t\t\t\tGROUP BY sk_{$this->sklad}_spr.id";
         $rs1 = sql::fetchOne($sql);
         if (!empty($rs1)) {
             $rash += $rs1["prihod"];
         }
         $sql = "SELECT SUM(quant) as prihod FROM ({$this->db}sk_{$this->sklad}_dvizh_arc)\n\t\t\t\t\tJOIN {$this->db}sk_{$this->sklad}_spr ON (sk_{$this->sklad}_spr.id=sk_{$this->sklad}_dvizh_arc.spr_id)\n\t\t\t\t\tWHERE ddate >= '{$sdate}'\n\t\t\t\t\t\t\tAND ddate <= '{$edate}'\n\t\t\t\t\t\t\tAND sk_{$this->sklad}_spr.id='{$rs[id]}'\n\t\t\t\t\t\t\tAND type='0'\n\t\t\t\t\t\t\tAND numd<>'9999'\n\t\t\t\t\tGROUP BY sk_{$this->sklad}_spr.id";
         $res1 = sql::fetchAll($sql);
         foreach ($res1 as $rs1) {
             $rash += $rs1["prihod"];
         }
         if ($prih != 0 || $rash != 0 || $rs["ost"] != 0) {
             $cols[nazv] = $rs[nazv];
             $cols[prihod] = sprintf("%10.2f", $prih);
             $cols[rashod] = sprintf("%10.2f", $rash);
             $cols[ost] = sprintf("%10.2f", $rs["ost"]);
             $cols[edizm] = $rs[edizm];
             $ret[] = $cols;
         }
     }
     return $ret;
 }

 public function getPath($id)
 {
     $sql = "SELECT customer,blockname\r\n            FROM lanch JOIN (blocks,customers)\r\n                            ON (blocks.id=lanch.block_id\r\n                                    AND customers.id=blocks.customer_id )\r\n                WHERE lanch.id='{$id}'";
     $rs = sql::fetchOne($sql);
     return "z:\\Заказчики\\{$rs['customer']}\\{$rs['blockname']}";
 }

 /**
  * Возвращает идентификатор коментария по тексту коментария
  * @param string $comment
  * @return int
  */
 public static function getCommentId($comment)
 {
     $sql = "SELECT * FROM coments WHERE comment='{$comment}'";
     $rs = sql::fetchOne($sql);
     if (empty($rs)) {
         $sql = "INSERT INTO coments (comment) VALUES ('{$comment}')";
         sql::query($sql);
         return sql::lastId();
     } else {
         return $rs[id];
     }
 }

 /**
  * Сохраняет или обновляет данные о расчете цены в таблице orderformoney
  * @param array $rec
  * @return var
  */
 public function moneyfororder($rec)
 {
     $rec = multibyte::cp1251_to_utf8($rec);
     extract($rec);
     $rec[hash] = hash('md5', $customer . $order . $board . $mater . $trud);
     $sql = "SELECT * FROM moneyfororder WHERE hash='{$rec[hash]}'";
     $rs = sql::fetchOne($sql);
     if (!empty($rs)) {
         $rec[id] = $rs[id];
     }
     //echo $sql;
     sql::insertUpdate("moneyfororder", array($rec));
     return true;
 }

 public function getRecord($id)
 {
     $sql = "SELECT * FROM todo WHERE id='{$id}'";
     $rec = sql::fetchOne($sql);
     return $rec;
 }

 public function getDataForCalc($id)
 {
     $rec = array();
     $sql = "SELECT *,CONCAT (number,' от ',DATE_FORMAT(orderdate,'%d.%m.%Y')) as letter, " . "boards.sizex/100.0 as psizex, " . "boards.sizey/100.0 as psizey, " . "blocks.sizex/100.0 as bsizex, " . "blocks.sizey/100.0 as bsizey, " . "immer*auarea as gold, " . "CONCAT(numlam,' ',lsizex,'x',lsizey) as lamel, " . "blocks.comment_id as bcid " . "FROM posintz JOIN (tz,orders,customers,blocks,blockpos,boards) " . "ON (posintz.tz_id=tz.id " . "AND tz.order_id=orders.id " . "AND posintz.block_id=blocks.id " . "AND orders.customer_id=customers.id " . "AND blockpos.block_id=blocks.id " . "AND blockpos.board_id=boards.id )  " . "WHERE posintz.id='{$id}'";
     $rec = sql::fetchOne($sql);
     $params = json_decode(multibyte::Unescape(sqltable_model::getComment($rec["bcid"])), true);
     //получим текщий комент из блока
     $rec[eltest] = $params[eltest];
     $rec[etpib] = $params[etpib];
     $rec[etpoints] = $params[etpoints];
     $rec[etcompl] = $params[etcompl];
     $rec[thickness] = (double) $rec[thickness];
     $rec[type] = $rec[layers] > 2 ? 'mpp' : 'dpp';
     $rec[template] = "r{$rec[type]}.xls";
     preg_match('/(?P<nummask>[+0-9]*)(?P<mask>.*)/i', $rec[mask], $matches);
     $rec[mask] = $matches[mask];
     $rec[nummask] = empty($matches[nummask]) ? 2 : $matches[nummask];
     $rec[frez_factor] = $rec[frez_factor] > 0 ? $rec[frez_factor] : 1.0;
     $orderstring = fileserver::removeOSsimbols($rec[letter] . " tz{$rec[tz_id]} posintz{$id}");
     $rec[filename] = "t:\\\\Расчет стоимости плат\\\\{$rec[customer]}\\\\{$rec[blockname]}\\\\{$orderstring}.xls";
     return $rec;
 }

 public function getUserById($id)
 {
     $sql = "SELECT * FROM users WHERE id='{$id}'";
     $user = sql::fetchOne($sql);
     return $user;
 }

require $_SERVER["DOCUMENT_ROOT"] . "/lib/engine.php";
authorize();
// вызов авторизации
$processing_type = basename(__FILE__, ".php");
ob_start();
if (isset($edit)) {
    if (!empty($id)) {
        $sql = "SELECT * FROM customers WHERE id='{$id}'";
        $rs = sql::fetchOne($sql);
        $_SESSION[customer_id] = $rs[id];
        $_SESSION[customer] = $rs[customer];
        echo "ok<script>selectmenu('orders','');</script>";
        exit;
    } else {
        $sql = "SELECT * FROM customers WHERE id='{$edit}'";
        $cust = sql::fetchOne($sql);
        $form = new Edit($processing_type);
        $form->init();
        $form->addFields(array(array("type" => CMSFORM_TYPE_TEXT, "name" => "customer", "label" => "Краткое название (имя каталога):", "value" => $cust["customer"]), array("type" => CMSFORM_TYPE_TEXT, "name" => "fullname", "label" => "Полное название (для теззаданий):", "value" => $cust["fullname"], "options" => array("html" => "size=60")), array("type" => CMSFORM_TYPE_TEXT, "name" => "kdir", "label" => "Каталог на диске К (для сверловок):", "value" => $cust["kdir"])));
        $form->show();
    }
} elseif (isset($delete)) {
    // удаление
    $sql = "DELETE FROM customers WHERE id='{$delete}'";
    sql::query($sql);
    sql::error(true);
    // удаление связей
    // удалить и платы заказчика
    $sql = "SELECT * FROM plates WHERE customer_id='{$delete}'";
    $res = sql::fetchAll($sql);
    foreach ($res as $rs) {

 public function getRecord($id)
 {
     $sql = "SELECT *, customers.id AS cusid, boards.id AS board_id\n                FROM zadel\n                JOIN (customers,boards)\n                ON (zadel.board_id=boards.id AND boards.customer_id=customers.id)\n                WHERE zadel.id='{$id}'";
     $rec = sql::fetchOne($sql);
     return $rec;
 }