Exemplo n.º 1
0
 public function getData($all = false, $order = '', $find = '', $idstr = '')
 {
     $ret = array();
     $sql = "SELECT *,zadel.id AS zid,zadel.id, board_name AS plate\n                FROM zadel\n                JOIN (boards,customers)\n                ON (zadel.board_id=boards.id\n                   AND boards.customer_id=customers.id) " . (!empty($find) ? "WHERE (board_name LIKE '%{$find}%'\n                                OR customers.customer LIKE '%{$find}%')" : "") . (!empty($order) ? " ORDER BY {$order} " : " ORDER BY zadel.id DESC ") . ($all ? "" : "LIMIT 20");
     $ret = sql::fetchAll($sql);
     return $ret;
 }
Exemplo n.º 2
0
 public function delete($delete)
 {
     $affected = 0;
     $sql = "DELETE FROM orders WHERE id='{$delete}'";
     sql::query($sql);
     $affected += sql::affected();
     // удаление связей
     $sql = "DELETE FROM files WHERE `table`='orders' AND rec_id='{$delete}'";
     sql::query($sql);
     $affected += sql::affected();
     $sql = "SELECT * FROM tz WHERE order_id='{$delete}'";
     $res = sql::fetchAll($sql);
     foreach ($res as $rs) {
         // удаление
         $delete = $rs["id"];
         $sql = "DELETE FROM tz WHERE id='{$delete}'";
         sql::query($sql);
         $affected += sql::affected();
         // удаление связей
         $sql = "SELECT * FROM posintz WHERE tz_id='{$delete}'";
         $res1 = sql::fetchAll($sql);
         foreach ($res1 as $rs1) {
             $delete = $rs1["id"];
             $sql = "DELETE FROM posintz WHERE id='{$delete}'";
             sql::query($sql);
             $affected += sql::affected();
         }
     }
     return $affected;
 }
Exemplo n.º 3
0
 /**
  * Воззвращает массив данных из базы
  * @param boolean $all Покказывать все
  * @param string $order Наззвание столбца по которому сортировать
  * @param string $find Подстрока поиска
  * @param string $idstr строка  идентификаторов, специальное использование, очень специальное
  * @return array 
  */
 public function getData($all = false, $order = '', $find = '', $idstr = '')
 {
     $ret = array();
     $sql = "SELECT * " . "FROM users__settings_types " . (!empty($find) ? "WHERE description LIKE '%{$find}%'\n                            OR key LIKE '%{$find}%'" : "") . (!empty($order) ? "ORDER BY {$order} " : "ORDER BY users__settings_types.id DESC ") . ($all ? "" : "LIMIT 20");
     $ret = sql::fetchAll($sql);
     return $ret;
 }
Exemplo n.º 4
0
 public function getData($all = false, $order = '', $find = '', $idstr = '')
 {
     $ret = array();
     $sql = "SELECT *\n                FROM workers " . (!empty($find) ? "WHERE (fio LIKE '%{$find}%' OR DATE_FORMAT(dr,'%Y-%m-%d') LIKE '%{$find}%') " : "") . (!empty($order) ? "ORDER BY " . $order . " " : "ORDER BY fio ") . ($all ? "" : "LIMIT 20");
     $ret = sql::fetchAll($sql);
     return $ret;
 }
Exemplo n.º 5
0
 public function getData($all = false, $order = '', $find = '', $idstr = '')
 {
     $ret = parent::getData($all, $order, $find, $idstr);
     $sql = "SELECT * \n                    FROM moneyfororder " . (!empty($find) ? "WHERE (`customer` LIKE '%{$find}%' OR `order` LIKE '%{$find}%' ) " : "") . "GROUP BY `customer`, `order`,`mater`,`trud` " . (!empty($order) ? "ORDER BY {$order} " : "ORDER BY customer DESC ") . ($all ? "LIMIT 500" : "LIMIT 20");
     $ret = sql::fetchAll($sql);
     return $ret;
 }
Exemplo n.º 6
0
 public function getData($all = false, $order = '', $find = '', $idstr = '')
 {
     $ret = array();
     $sql = "SELECT *,conductors.id AS condid,boards.id AS plid,conductors.id\n                FROM conductors\n                JOIN (boards,customers)\n                ON (conductors.board_id=boards.id\n                    AND boards.customer_id=customers.id )\n                WHERE ready='0' " . (!empty($find) ? " WHERE (board_name LIKE '%{$find}%')" : "") . (!empty($order) ? " ORDER BY {$order} " : " ORDER BY conductors.id DESC ") . ($all ? "" : "LIMIT 20");
     $ret = sql::fetchAll($sql);
     return $ret;
 }
Exemplo n.º 7
0
 public function getData($all = false, $order = '', $find = '', $idstr = '')
 {
     $ret = array();
     $sql = "SELECT *,unix_timestamp(ts) AS uts, phototemplates.id AS ptid,phototemplates.id \n              FROM phototemplates\n              JOIN users\n              ON phototemplates.user_id=users.id " . (!empty($find) ? "WHERE filenames LIKE '%{$find}%' OR DATE_FORMAT(ts,'%Y-%m-%d') LIKE '%{$find}%' " : "") . (!empty($order) ? "ORDER BY {$order} " : "ORDER BY ts DESC ") . ($all ? "LIMIT 150" : "LIMIT 20");
     $ret = sql::fetchAll($sql);
     return $ret;
 }
Exemplo n.º 8
0
 public function getRecord($edit)
 {
     $rec = parent::getRecord($edit);
     $rec[customer] = $this->getCustomer($rec[customer_id]);
     $rec[customer] = $rec[customer][customer];
     $sql = "SELECT * \n                FROM blockpos \n                JOIN boards ON boards.id=blockpos.board_id \n                WHERE blockpos.block_id='{$edit}'";
     $rec[blockpos] = sql::fetchAll($sql);
     $param = multibyte::Json_decode(multibyte::Unescape($this->getComment($rec[comment_id])));
     if (empty($param["class"])) {
         $param["class"] = $rec[blockpos][0]["class"];
     }
     if (empty($param[basemat])) {
         $basemat = explode("-", $rec[blockpos][0][textolite]);
         $param[basemat] = $basemat[0];
     }
     $rec["comment"] = $param["coment"];
     $wideandgaps = $param["wideandgaps"];
     // если слои еще не заполнены заполним из wideandgaps
     for ($i = 1; $i < 11; $i++) {
         $sl1 = $wideandgaps[2 * $i - 2][0];
         $sl2 = $wideandgaps[2 * $i - 1][0];
         $pr1 = $wideandgaps[2 * $i - 2][1];
         $pr2 = $wideandgaps[2 * $i - 1][1];
         if (!isset($param["sl{$i}"]) || empty($param["sl{$i}"])) {
             if (!empty($sl1)) {
                 $param["sl{$i}"] = $sl1 . "-" . $sl2;
                 $param["pr{$i}"] = sprintf("%5.3f/%5.3f", $pr1, $pr2);
             }
         }
     }
     $rec["param"] = $param;
     $rec[files] = $this->getFilesForId('blocks', $edit);
     return $rec;
 }
 /**
  * Получет две даты и выдает данные за период
  */
 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;
 }
Exemplo n.º 10
0
 public function getData($all = false, $order = '', $find = '', $idstr = '')
 {
     $ret = array();
     $sql = "SELECT *,if((krost>ost),'<span style=\"color:red\"><b>мало</b></span>','') as malo,sk_arc_{$this->sklad}_spr.id\n                FROM {$this->db}`sk_arc_{$this->sklad}_spr`\n                JOIN {$this->db}sk_arc_{$this->sklad}_ost\n                ON sk_arc_{$this->sklad}_ost.spr_id=sk_arc_{$this->sklad}_spr.id\n                WHERE nazv!='' " . (!empty($find) ? " AND nazv LIKE '%{$find}%' " : "") . (!empty($order) ? "ORDER BY {$order} " : "ORDER BY nazv ") . ($all ? "" : "LIMIT 20");
     $ret = sql::fetchAll($sql);
     return $ret;
 }
Exemplo n.º 11
0
 public function getData($all = false, $order = '', $find = '', $idstr = '')
 {
     $ret = array();
     $spr_id = $_SESSION[Auth::$lss][arctovarid];
     $sql = "SELECT *,sk_arc_{$this->sklad}_dvizh.id\n                FROM {$this->db}sk_arc_{$this->sklad}_dvizh\n                JOIN ({$this->db}sk_{$this->sklad}_postav,{$this->db}coments)\n                ON (sk_{$this->sklad}_postav.id=sk_arc_{$this->sklad}_dvizh.post_id\n                    AND coments.id=sk_arc_{$this->sklad}_dvizh.comment_id)\n                WHERE spr_id='{$spr_id}'" . (!empty($find) ? " AND comment LIKE '%{$find}%' OR supply LIKE '%{$find}%'\n                    OR numd LIKE '%{$find}%'" : "") . (!empty($order) ? "ORDER BY {$order} " : "ORDER BY ddate DESC ") . ($all ? "" : "LIMIT 20");
     $ret = sql::fetchAll($sql);
     return $ret;
 }
Exemplo n.º 12
0
 public function getData($all = false, $order = '', $find = '', $idstr = '')
 {
     $ret = array();
     if (!empty($find)) {
     }
     $sql = "SELECT *, masterplate.id AS mpid,masterplate.id " . "FROM masterplate " . "JOIN (users,blocks,customers) " . "ON ( " . "masterplate.user_id=users.id " . "AND blocks.customer_id=customers.id " . "AND masterplate.block_id=blocks.id " . ") " . (!empty($find) ? "WHERE blockname LIKE '%{$find}%'\n                            OR customer LIKE '%{$find}%'" : "") . (!empty($order) ? "ORDER BY {$order} " : "ORDER BY masterplate.id DESC ") . ($all ? "LIMIT 50" : "LIMIT 20");
     $ret = sql::fetchAll($sql);
     return $ret;
 }
Exemplo n.º 13
0
 /**
  * Текстолит возвращает для ТЗ
  * @param array $rec Массив $REQUEST из контроллера передается
  * @return json
  */
 public function textolite($rec)
 {
     $rec = multibyte::cp1251_to_utf8($rec);
     extract($rec);
     $out = '';
     $sql = "SELECT * FROM `zaomppsklads`.`sk_mat__spr` ORDER BY nazv";
     $res[textolite] = sql::fetchAll($sql);
     $out .= json_encode($res, JSON_HEX_TAG | JSON_HEX_APOS | JSON_HEX_QUOT | JSON_HEX_AMP);
     return $out;
 }
Exemplo n.º 14
0
 public function getRecord($edit)
 {
     $rec = parent::getRecord($edit);
     $sql = "SELECT * FROM blockpos JOIN boards ON boards.id=blockpos.board_id WHERE block_id={$rec[block_id]}";
     $rec[boards] = sql::fetchAll($sql);
     foreach ($rec[boards] as &$value) {
         $value[filelinks] = $this->getFilesForId('boards', $value[board_id]);
     }
     return $rec;
 }
Exemplo n.º 15
0
 public function getData($all = false, $order = '', $find = '', $idstr = '')
 {
     $ret = array();
     $sql = "SELECT *\n                FROM rtypes\n                " . (!empty($find) ? "WHERE (type LIKE '%{$find}%' ) " : "") . (!empty($order) ? "ORDER BY " . $order . " " : "ORDER BY type ") . (isset($all) ? "" : "LIMIT 20");
     $ret = sql::fetchAll($sql);
     foreach ($ret as &$value) {
         $value[what] = html_entity_decode($value[what]);
     }
     return $ret;
 }
Exemplo n.º 16
0
 public function getData($all = false, $order = '', $find = '', $idstr = '')
 {
     $ret = array();
     $sql = "SELECT *, todo.id\n                FROM todo\n                JOIN users\n                ON users.id=u_id " . (!empty($find) ? "WHERE (what LIKE '%{$find}%' ) " : "") . ($all ? "" : (isset($find) ? " AND rt='0000-00-00 00:00:00' " : " WHERE rts='0000-00-00 00:00:00' ")) . (!empty($order) ? "ORDER BY " . $order . " " : "ORDER BY cts ") . (isset($all) ? "" : "LIMIT 20");
     $ret = sql::fetchAll($sql);
     foreach ($ret as &$value) {
         $value[what] = html_entity_decode($value[what]);
     }
     return $ret;
 }
Exemplo n.º 17
0
 public function getRecord($edit)
 {
     // поставщики список для выбора
     $supply["0"] = "Новый";
     $sql = "SELECT * FROM {$this->db}sk_{$this->sklad}_postav";
     $res = sql::fetchAll($sql);
     foreach ($res as $rs) {
         $supply[$rs["id"]] = $rs["supply"];
     }
     $sql = "SELECT *,sk_{$this->sklad}_dvizh.id,sk_{$this->sklad}_postav.id AS supply_id\n             FROM {$this->db}sk_{$this->sklad}_dvizh\n             JOIN ({$this->db}sk_{$this->sklad}_postav,{$this->db}coments,{$this->db}sk_{$this->sklad}_spr)\n             ON (sk_{$this->sklad}_postav.id=sk_{$this->sklad}_dvizh.post_id\n                AND coments.id=sk_{$this->sklad}_dvizh.comment_id\n                AND {$this->db}sk_{$this->sklad}_spr.id={$this->db}sk_{$this->sklad}_dvizh.spr_id)\n             WHERE sk_{$this->sklad}_dvizh.id='{$edit}'";
     $rec = sqltable_model::getRecord($sql);
     $rec[supply] = $supply;
     return $rec;
 }
Exemplo n.º 18
0
 public function getRecord($edit)
 {
     $rec = parent::getRecord($edit);
     $sql = "SELECT id,operation FROM operations WHERE block_type='{$this->blocktype}' OR block_type='both' ORDER BY priority,id";
     $res = sql::fetchAll($sql);
     foreach ($res as $value) {
         $rec[operations][$value[id]] = $value[operation];
     }
     $operation = array_shift($res);
     $operations = multibyte::Json_decode(sqltable_model::getComment($rec[coment_id]));
     $coment_id = $operations[$operation[id]][comment_id];
     $rec[comment] = sqltable_model::getComment($coment_id);
     $rec[action_date] = $operations[$operation[id]][date];
     return $rec;
 }
Exemplo n.º 19
0
 public function delete($delete)
 {
     $affected = 0;
     $sql = "DELETE FROM tz WHERE id='{$delete}'";
     sql::query($sql);
     $affected += sql::affected();
     // удаление связей
     $sql = "SELECT * FROM posintz WHERE tz_id='{$delete}'";
     $res = sql::fetchAll($sql);
     foreach ($res as $rs) {
         $delete = $rs["id"];
         $sql = "DELETE FROM posintz WHERE id='{$delete}'";
         sql::query($sql);
         $affected += sql::affected();
     }
     return $affected;
 }
Exemplo n.º 20
0
 public function getData($all = false, $order = '', $find = '', $idstr = '')
 {
     $ret = array();
     $order = strstr($order, 'files') ? '' : $order;
     // не удается отсортировать по файлам
     if (empty($_SESSION[customer_id])) {
         $customer = "Выберите заказчика!!!";
         $sql = "SELECT *, CONCAT(boards.sizex,'x',boards.sizey) AS size, \n                    boards.id AS boardid,boards.id\n                    FROM boards\n                    JOIN (customers)\n                    ON (customers.id=boards.customer_id ) " . (!empty($find) ? "WHERE board_name LIKE '%{$find}%' " : "") . (!empty($order) ? "ORDER BY {$order} " : "ORDER BY board_name  DESC ") . ($all ? "LIMIT 50" : "LIMIT 20");
     } else {
         $cusid = $_SESSION[customer_id];
         $customer = $_SESSION[customer];
         // sql
         $sql = "SELECT *, CONCAT(boards.sizex,'x',boards.sizey) AS size, \n                    boards.id AS boardid,boards.id\n                    FROM boards\n                    JOIN (customers)\n                    ON (customers.id=boards.customer_id ) " . (!empty($find) ? "WHERE board_name LIKE '%{$find}%' AND customers.id='{$_SESSION[customer_id]}' " : " WHERE customers.id='{$_SESSION[customer_id]}' ") . (!empty($order) ? "ORDER BY {$order} " : "ORDER BY board_name DESC ") . ($all ? "LIMIT 50" : "LIMIT 20");
     }
     $ret = sql::fetchAll($sql);
     foreach ($ret as &$value) {
         $files = $this->getFilesForId('boards', $value[boardid]);
         $value[files] = $files[link];
     }
     return $ret;
 }
Exemplo n.º 21
0
 public function delete($delete)
 {
     $affected = 0;
     $sql = "DELETE FROM customers WHERE id='{$delete}'";
     sql::query($sql);
     $affected += sql::affected();
     // удаление связей
     // TODO: Удаллить блоки и позиции в блоках, а также платы
     // удалить вязанные заказы и тз
     $sql = "SELECT * FROM orders WHERE customer_id='{$delete}'";
     $res = sql::fetchAll($sql);
     foreach ($res as $rs) {
         // удаление
         $delete = $rs["id"];
         $sql = "DELETE FROM orders WHERE id='{$delete}'";
         sql::query($sql);
         $affected += sql::affected();
         // удаление связей
         $sql = "SELECT * FROM tz WHERE order_id='{$delete}'";
         $res1 = sql::fetchAll($sql);
         foreach ($res1 as $rs1) {
             // удаление
             $delete = $rs1["id"];
             $sql = "DELETE FROM tz WHERE id='{$delete}'";
             sql::query($sql);
             $affected += sql::affected();
             // удаление связей
             $sql = "SELECT * FROM posintz WHERE tz_id='{$delete}'";
             $res2 = sql::fetchAll($sql);
             foreach ($res2 as $rs2) {
                 $delete = $rs2["id"];
             }
             $sql = "DELETE FROM posintz WHERE id='{$delete}'";
             sql::query($sql);
             $affected += sql::affected();
         }
     }
     return $affected;
 }
Exemplo n.º 22
0
 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;
 }
Exemplo n.º 23
0
    public function getToday() {
        $filename = $_SERVER[CACHE] . '/birhdays.html';
        if(file_exists($filename) && (date('d',@filemtime($filename))==date('d'))) {
            return file_get_contents($filename);
        }
        $dr = '';
        $sql = "SELECT *, (YEAR(NOW())-YEAR(dr)) as let FROM workers WHERE DAYOFYEAR(dr)>= DAYOFYEAR(CURRENT_DATE()) AND DAYOFYEAR(dr)<= (DAYOFYEAR(CURRENT_DATE())+4) ORDER BY DAYOFYEAR(dr)";
        $rs = sql::fetchAll($sql);
        if (empty($rs)) {
            //$calend = file_get_contents('http://www.calend.ru/img/export/calend.rss');
            //$calend = file_get_contents(__DIR__ . '/sample.xml');
            //return $this->xml2html($calend, __DIR_ . "/calend.xsl");
            $filenamerss = $_SERVER[CACHE] . '/calend.xml';
            $filetime = @filemtime($filenamerss);
            if (!$filetime || (date('d',$filetime)!=date('d'))) {
                $data = preg_replace("/\x01/","",file_get_contents('http://www.calend.ru/img/export/calend.rss'));
                @file_put_contents ($filenamerss, $data);
            }
            $rss = simplexml_load_file($filenamerss);
            $count = 0;
            foreach ($rss->channel->item as $item) {
                if ($count++ >= 3) break;
                $text = explode(' ', $item->title.' - '.$item->description, 10);
                array_pop($text);
                $text = implode(' ', $text);
                $dr .= "<a target=blank href={$item->link}><div>{$text}...</div></a>";
            }
        } else {

            foreach ($rs as $res) {
                $dr .= "<div>День рождения - {$res["fio"]} - {$res["dr"]} - {$res["let"]} лет</div>";
            }
        }
        @file_put_contents ($filename, $dr);
        return $dr;
    }
Exemplo n.º 24
0
 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;
 }
Exemplo n.º 25
0
 /**
  * Возвращает по номеру позиции в ТЗ количество использованого задела
  * @param int $id
  * @return int
  */
 public function getZadelByPosintzId($id)
 {
     $rec = array();
     $sql = "SELECT\r\n                    zadel.number AS zadel,\r\n                    posintz.numbers AS zakaz,\r\n                    zadel.id AS zadelid\r\n                FROM posintz\r\n                LEFT JOIN (zadel,blocks,blockpos,boards)\r\n                ON\r\n                    posintz.block_id=blocks.id\r\n                    AND blockpos.block_id=blocks.id\r\n                    AND boards.id=blockpos.board_id\r\n                    AND zadel.board_id=boards.id\r\n                WHERE posintz.id='{$id}'\r\n                ORDER BY zadel ASC";
     // сорртирован по количеству чтоб удалять сначала те что меньше
     $res = sql::fetchAll($sql);
     foreach ($res as $zd) {
         $rec[zadel] += $zd["zadel"];
         $rec[zds] .= "{$zd[zadelid]}-{$zd["zadel"]},";
         $rec[zakaz] = $zd[zakaz];
     }
     $rec["use"] = min($rec[zadel], $rec[zakaz]);
     return $rec;
 }
Exemplo n.º 26
0
 /**
  * 	Функция обновляет с вставкой таблицу, можно использовать для 
  * массового update, возвращает affected rows
  * 	@param	string	$table		Таблица для обновления
  * 	@param	array		$data			Массив из массивов 
  * с данными, важно заметить, что имена полей для запроса берутся из 
  * первого подмассива
  * 	@param	array		$exclude	Список полей, не участвующих 
  * в обновлении (обычно ID — те поля, по которым будет контролироваться 
  * уникальность записей, как-то так)
  * 	@param	bool		$log	Тип лога, см. функцию query()
  * 	@return	int
  */
 function insertUpdate($table, $data, $exclude = array("id"), $log = SQL_LOG)
 {
     if (!is_array($data) || count($data) < 1) {
         return false;
     }
     $fieldsintable = sql::fetchAll("SHOW COLUMNS FROM `{$table}`");
     foreach ($fieldsintable as $value) {
         $fields[] = "`{$value["Field"]}`";
     }
     $fieldsintable = $fields;
     $fields = $this->insert_getFields(reset($data), true);
     sort($fields);
     sort($fieldsintable);
     $fields = array_intersect($fields, $fieldsintable);
     $values = array();
     foreach ($data as $dataRow) {
         $values[] = $this->insert_prepare($dataRow, $fields);
     }
     $upd = array();
     $sql = "INSERT INTO `{$table}` (" . implode(", ", $fields) . ") VALUES ";
     foreach ($fields as $field) {
         if (@(!in_array(substr($field, 1, -1), $exclude))) {
             $upd[] = "{$field} = VALUES({$field})";
         }
     }
     $upd = " ON DUPLICATE KEY UPDATE " . implode(", ", $upd);
     $SQLs = array();
     $n = 0;
     foreach ($values as $value) {
         if (mb_strlen($sql) + mb_strlen($SQLs[$n] . $value . ", ") + mb_strlen($upd) > $this->maxPacket()) {
             $n++;
             $this->log("insertUpdate(): Новый подзапрос №{$n}", SQL_TYPE_WARNING);
         }
         $SQLs[$n] .= $value . ", ";
     }
     foreach ($SQLs as $SQLn) {
         $this->query($sql . substr($SQLn, 0, -2) . $upd, array(), $log);
     }
     // Оптимизируем, если нет ошибок
     //if (!$this->error())
     //$this->query("OPTIMIZE TABLE {$table}", array(), SQL_LOG_BLOCK);
     // Cleanup
     unset($data);
     unset($SQLs);
     unset($upd);
     return $this->affected();
 }
Exemplo n.º 27
0
 public function getCommentsForId($id, $table = '')
 {
     if (empty($table)) {
         $table = $this->maintable;
     }
     $sql = "SELECT `time`, `nik` AS `author`, `comment`, comments.id as id FROM comments JOIN (users,coments) ON (comments.author_id = users.id AND comments.coment_id = coments.id ) WHERE forobject='{$table}' AND record_id='{$id}'";
     $rec["comments"] = sql::fetchAll($sql);
     $rec["table"] = $table;
     $rec["id"] = $id;
     return $rec;
 }
Exemplo n.º 28
0
 public function arc()
 {
     // годовая архивация
     // перенести движения
     $sql = "INSERT INTO {$this->db}sk_{$this->sklad}_dvizh_arc\n                (type,numd,numdf,docyr,spr_id,quant,ddate,post_id,comment_id,price)\n                SELECT sk_{$this->sklad}_dvizh.type,sk_{$this->sklad}_dvizh.numd,sk_{$this->sklad}_dvizh.numdf,sk_{$this->sklad}_dvizh.docyr,sk_{$this->sklad}_dvizh.spr_id,sk_{$this->sklad}_dvizh.quant,sk_{$this->sklad}_dvizh.ddate,sk_{$this->sklad}_dvizh.post_id,sk_{$this->sklad}_dvizh.comment_id,sk_{$this->sklad}_dvizh.price\n                FROM {$this->db}sk_{$this->sklad}_dvizh";
     if (!sql::query($sql)) {
         return false;
     }
     // очистить движения
     $sql = "TRUNCATE TABLE {$this->db}sk_{$this->sklad}_dvizh";
     if (!sql::query($sql)) {
         return false;
     }
     $sql = "SELECT * FROM {$this->db}sk_{$this->sklad}_spr";
     $res = sql::fetchAll($sql);
     foreach ($res as $rs) {
         $id = $rs["id"];
         // получить остатки
         $sql = "SELECT * FROM {$this->db}sk_{$this->sklad}_ost WHERE sk_{$this->sklad}_ost.spr_id='{$id}'";
         $ost = mysql_fetch_array(mysql_query($sql));
         $ost = $ost["ost"];
         // создать архивное движение
         // поставщик
         $sql = "SELECT id FROM {$this->db}sk_{$this->sklad}_postav WHERE supply=''";
         $rs1 = sql::fetchOne($sql);
         if (!empty($rs1)) {
             $post_id = $rs1["id"];
         }
         // коментарий
         $sql = "SELECT id FROM {$this->db}coments WHERE comment='Передача остатка'";
         $rs1 = sql::fetchOne($sql);
         if (!empty($rs1)) {
             $comment_id = $rs1["id"];
         } else {
             $sql = "INSERT INTO {$this->db}coments (comment) VALUES ('Передача остатка')";
             sql::query($sql) or die(sql::error(true));
             $comment_id = sql::lastId();
         }
         $numd = "9999";
         $numdf = "9999";
         $docyr = date("Y") - 1;
         $ddate = date("Y-m-d", mktime(0, 0, 0, 12, 31, $docyr));
         $sql = "INSERT INTO {$this->db}sk_{$this->sklad}_dvizh_arc (type,numd,numdf,docyr,spr_id,quant,ddate,post_id,comment_id,price)\n                    VALUES ('0','{$numd}','{$numdf}','{$docyr}','{$id}','{$ost}','{$ddate}','{$post_id}','{$comment_id}','0')";
         echo $sql . "<br>";
         if (!sql::query($sql)) {
             return false;
         }
         // создадим первое движение года
         // коментарий
         $sql = "SELECT id FROM {$this->db}coments WHERE comment='Остаток на 31.12.{$docyr}'";
         $rs1 = sql::fetchOne($sql);
         if (!empty($rs1)) {
             $comment_id = $rs1["id"];
         } else {
             $sql = "INSERT INTO {$this->db}coments (comment) VALUES ('Остаток на 31.12.{$docyr}')";
             sql::query($sql) or die(sql::error(true));
             $comment_id = sql::lastId();
         }
         $docyr = date("Y");
         $ddate = date("Y-m-d", mktime(0, 0, 0, 1, 1, $docyr));
         $sql = "INSERT INTO {$this->db}sk_{$this->sklad}_dvizh (type,numd,numdf,docyr,spr_id,quant,ddate,post_id,comment_id,price)\n                    VALUES ('1','{$numd}','{$numdf}','{$docyr}','{$id}','{$ost}','{$ddate}','{$post_id}','{$comment_id}','0')";
         if (!sql::query($sql)) {
             return false;
         }
     }
     return true;
 }
Exemplo n.º 29
0
 public function update_lanched()
 {
     $out = '';
     $sql = "TRUNCATE TABLE `lanched`";
     sql::query($sql);
     $out .= sql::error() . "<br>";
     $sql = "SELECT block_id, MAX( ldate ) AS md\n                FROM lanch\n                WHERE ldate >= DATE_SUB(NOW(),INTERVAL 1 MONTH)\n                GROUP BY block_id\n                ";
     $rs = sql::fetchAll($sql);
     foreach ($rs as $res) {
         $sql = "INSERT INTO lanched SET block_id='{$res[block_id]}', lastdate='{$res[md]}'";
         sql::query($sql);
         $out .= sql::error() . "<br>";
     }
     return $out;
 }
Exemplo n.º 30
0
    }
} elseif (isset($delete)) {
    // удаление
    $sql = "DELETE FROM orders WHERE id='{$delete}'";
    sql::query($sql);
    // удаление связей
    $sql = "SELECT * FROM tz WHERE order_id='{$delete}'";
    $res = sql::fetchAll($sql);
    foreach ($res as $rs) {
        // удаление
        $delete = $rs["id"];
        $sql = "DELETE FROM tz WHERE id='{$delete}'";
        sql::query($sql);
        // удаление связей
        $sql = "SELECT * FROM posintz WHERE tz_id='{$delete}'";
        $res1 = sql::fetchAll($sql);
        foreach ($res1 as $rs1) {
            $delete = $rs1["id"];
            $sql = "DELETE FROM posintz WHERE id='{$delete}'";
            sql::query($sql);
        }
    }
    echo "ok";
} else {
    // вывести таблицу
    if (isset($_GET["all"])) {
        $_SESSION[order_id] = '';
    }
    if (empty($_SESSION[customer_id])) {
        $customer = "Выберите заказчика!!!";
        $sql = "SELECT *,CONCAT(\"<a target=_blank href='http://" . $_SERVER["HTTP_HOST"] . UPLOAD_FILES_DIR . "/customers/\",SUBSTRING_INDEX(filelinks.file_link,'/',-1),\"'>\",SUBSTRING_INDEX(filelinks.file_link,'/',-1),\"</a>\") AS filename,orders.id FROM orders JOIN customers ON customers.id=customer_id LEFT JOIN filelinks ON orders.filelink=filelinks.id " . (isset($_GET["find"]) ? "WHERE (number LIKE '%{$_GET["find"]}%' OR orderdate LIKE '%{$_GET["find"]}%' ) " : "") . (isset($_GET["order"]) ? "ORDER BY {$_GET["order"]} " : "ORDER BY orders.orderdate DESC ") . (isset($_GET["all"]) ? "LIMIT 50" : "LIMIT 20");