Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- <?php
- /**
- * Created by PhpStorm.
- * User: 5 звезд
- * Date: 12.12.2018
- * Time: 13:18
- */
- namespace Sers;
- use PDO;
- use Exception;
- require_once 'PHPExcel.php';
- class Sers
- {
- public $dbs;
- /****************PDO соединение с базой mySQL********************************/
- function __construct($user, $pass, $host, $db){
- $opt = [
- PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
- PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_BOTH,
- PDO::ATTR_EMULATE_PREPARES => false,
- ];
- $charset = 'utf8';
- $dsn = "mysql:host=$host;dbname=$db;charset=$charset";
- try {
- $this->dbs = new PDO($dsn, $user, $pass, $opt);
- } catch (Exception $e) {
- $GLOBALS['error']="Связь не установлена";
- }
- }
- /********************Получить все категории********************************/
- public function get_typ($id=false, $data=false){
- if (empty($id)) {
- if (!empty($data)){
- $data=$this->replace_attr($data);
- $r = $this->dbs->prepare('SELECT * FROM typs as c, tovar as t, prixod as p WHERE c.id=t.id_typ AND t.id=p.id_t AND p.data=:d GROUP BY c.nazv ORDER BY c.nazv DESC;');
- $r->execute([':d'=>$data]);
- }else {
- $r = $this->dbs->prepare('SELECT * FROM typs ORDER BY nazv DESC;');
- $r->execute();
- }
- $i = 0;
- foreach ($r as $res) {
- $result[$i] = ['id' => $res[0], 'nazv' => $res[1]];
- $i++;
- }
- return $result;
- }else{
- $id=$this->replace_attr($id);
- $r=$this->dbs->prepare('SELECT * FROM typs WHERE id=:i;');
- $r->execute([':i'=>$id]);
- foreach ($r as $res){
- $result=['id'=>$res['id'], 'nazv'=>$res['nazv']];
- }
- return $result;
- }
- }
- /*********************Поиск товара**************************************/
- public function get_tov_search($search){
- $search=$this->replace_attr_search($search);
- #print $search."<hr>";
- $r=$this->dbs->prepare('SELECT * FROM tovar AS t, kol AS k, typs AS tp WHERE t.id_kol=k.id and t.id_typ=tp.id and t.visibl<>0 and (naz LIKE :search1 OR art LIKE :search2 OR color LIKE :search3 OR siz LIKE :search4) ORDER BY t.naz ASC;');
- $r->execute([':search1'=>$search, ':search2'=>$search, ':search3'=>$search, ':search4'=>$search]);
- #$r->debugDumpParams();
- $i=0;
- foreach ($r as $res){
- $result[$i] = [
- 'id'=>$res[0],
- 'id_typ'=>$res['id_typ'],
- 'id_kol'=>$res['id_kol'],
- 'art'=>$res['art'],
- 'naz'=>$res['naz'],
- 'mat'=>$res['mat'],
- 'forma'=>$res['forma'],
- 'color'=>$res['color'],
- 'aer'=>$res['aer'],
- 'diver'=>$res['diver'],
- 'izl'=>$res['izl'],
- 'siz'=>$res['siz'],
- 'stil'=>$res['stil'],
- 'meh'=>$res['meh'],
- 'mont'=>$res['mont'],
- 'tpod'=>$res['tpod'],
- 'upr'=>$res['upr'],
- 'naznach'=>$res['naznach'],
- 'kompl'=>$res['kompl'],
- 'garant'=>$res['garant'],
- 'foto'=>$res['foto'],
- 'foto1'=>$res['foto1'],
- 'opis'=>$res['opis'],
- 'cena'=>$res['cena'],
- 'visibl'=>$res['visibl']
- ];
- $i++;
- }
- return $result;
- }
- /******************Вывод всего товара test************************************/
- public function get_all_tovtest($art=false, $visibl){
- if (empty($art)) {
- $visibl = $this->replace_attr($visibl);
- if ($visibl == 3) {
- $r = $this->dbs->prepare('SELECT * FROM tovar_test as t, typs as c, kol as k WHERE t.id_typ=c.id AND t.id_kol=k.id ORDER BY t.art ASC;');
- $r->execute();
- } else {
- $r = $this->dbs->prepare('SELECT * FROM tovar_test as t, typs as c, kol as k WHERE t.id_typ=c.id AND t.id_kol=k.id AND t.visibl=:v ORDER BY t.art ASC;');
- $r->execute([':v' => $visibl]);
- }
- }else{
- $art=$this->replace_attr($art);
- $visibl = $this->replace_attr($visibl);
- $r = $this->dbs->prepare('SELECT * FROM tovar_test as t, typs as c, kol as k WHERE t.id_typ=c.id AND t.id_kol=k.id AND t.visibl=:v AND t.art=:a ORDER BY t.art ASC;');
- $r->execute([':v' => $visibl, ':a'=>$art]);
- }
- if ($r->rowCount()!=0){
- $i=0;
- foreach ($r as $res){
- $result[$i]=[
- 'id'=>$res[0],
- 'id_typ'=>$res['id_typ'],
- 'id_kol'=>$res['id_kol'],
- 'art'=>$res['art'],
- 'naz_t'=>$res['naz'],
- 'naz_k'=>$res['nazvs'],
- 'naz_c'=>$res['nazv'],
- 'mat'=>$res['mat'],
- 'forma'=>$res['forma'],
- 'color'=>$res['color'],
- 'aer'=>$res['aer'],
- 'diver'=>$res['diver'],
- 'izl'=>$res['izl'],
- 'siz'=>$res['siz'],
- 'stil'=>$res['stil'],
- 'meh'=>$res['meh'],
- 'mont'=>$res['mont'],
- 'tpod'=>$res['tpod'],
- 'upr'=>$res['upr'],
- 'naznach'=>$res['naznach'],
- 'kompl'=>$res['kompl'],
- 'garant'=>$res['garant'],
- 'foto'=>$res['foto'],
- 'foto1'=>$res['foto1'],
- 'opis'=>$res['opis'],
- 'cena'=>$res['cena'],
- 'visibl'=>$res['visibl']
- ];
- $i++;
- }
- return $result;
- }else $GLOBALS['error']="Список товара пуст";
- }
- /******Вывод всего товара для нового прайса в соответствии с требованиями*************************/
- public function get_price_tov($id_kol, $id_typ, $nazv, $data=false){
- $id_kol=$this->replace_attr($id_kol);
- $id_typ=$this->replace_attr($id_typ);
- $nazv=$this->replace_attr($nazv);
- if (!empty($data)){
- $mas=[
- '3'=>'art',
- 'mat'=>'Материал: ',
- 'forma'=>'Форма: ',
- 'color'=>'Цвет: ',
- 'aer'=>'Аэратор: ',
- 'diver'=>'Дивертор: ',
- 'izl'=>'Излив: ',
- 'siz'=>'Размер: ',
- 'stil'=>'Стилистика: ',
- 'meh'=>'Механизм: ',
- 'mont'=>'Монтаж: ',
- 'tpod'=>'Тип подводки: ',
- 'upr'=>'Управление: ',
- 'naznach'=>'Назначение: ',
- 'kompl'=>'Комплектация: ',
- 'garant'=>'Гарантия: ',
- 'foto'=>'foto',
- 'foto1'=>'foto1',
- 'opis'=>'Описание: ',
- '23'=>'cena',
- '31'=>'cena_china',
- '30'=>'kolvo',
- '29'=>'data'
- ];
- }else
- $mas=[
- 'art'=>'art',
- 'mat'=>'Материал: ',
- 'forma'=>'Форма: ',
- 'color'=>'Цвет: ',
- 'aer'=>'Аэратор: ',
- 'diver'=>'Дивертор: ',
- 'izl'=>'Излив: ',
- 'siz'=>'Размер: ',
- 'stil'=>'Стилистика: ',
- 'meh'=>'Механизм: ',
- 'mont'=>'Монтаж: ',
- 'tpod'=>'Тип подводки: ',
- 'upr'=>'Управление: ',
- 'naznach'=>'Назначение: ',
- 'kompl'=>'Комплектация: ',
- 'garant'=>'Гарантия: ',
- 'foto'=>'foto',
- 'foto1'=>'foto1',
- 'opis'=>'Описание: ',
- 'cena'=>'cena',
- ];
- if (!empty($data)){
- $r = $this->dbs->prepare('SELECT * FROM tovar as t, prixod as p WHERE t.id_kol=:id_kol and t.id_typ=:id_typ and t.naz=:nazv and t.id=p.id_t and p.data=:d ORDER BY t.art;');
- $r->execute([':id_kol' => $id_kol, ':id_typ' => $id_typ, ':nazv' => $nazv, ':d'=>$data]);
- }else {
- $r = $this->dbs->prepare('SELECT * FROM tovar WHERE id_kol=:id_kol and id_typ=:id_typ and naz=:nazv ORDER BY art;');
- $r->execute([':id_kol' => $id_kol, ':id_typ' => $id_typ, ':nazv' => $nazv]);
- }
- if ($r->rowCount()!=0){
- $i=0;
- foreach ($r as $res) {
- foreach ($res as $key => $value) {
- if (!empty($value)) {
- if (isset($mas[$key])) {
- $result[$i][$mas[$key]] =$value;
- }
- }
- }
- $i++;
- }
- return $result;
- }
- }
- /************Вывод всего товара с коллекцей и категорией по приходу*******************************/
- public function in_tov($data){
- $data=$this->replace_attr($data);
- $r=$this->dbs->prepare('SELECT * FROM tovar as t, typs as c, kol as k, prixod as p WHERE t.id_typ=c.id AND t.id_kol=k.id AND t.id=p.id_t AND p.data=:d ORDER BY t.art ASC;');
- $r->execute([':d'=>$data]);
- if ($r->rowCount()!=0){
- $i=0;
- foreach ($r as $res){
- $result[$i]=[
- 'id'=>$res[0],
- 'id_typ'=>$res['id_typ'],
- 'id_kol'=>$res['id_kol'],
- 'art'=>$res[3],
- 'naz_t'=>$res['naz'],
- 'naz_k'=>$res['nazvs'],
- 'naz_c'=>$res['nazv'],
- 'mat'=>$res['mat'],
- 'forma'=>$res['forma'],
- 'color'=>$res['color'],
- 'aer'=>$res['aer'],
- 'diver'=>$res['diver'],
- 'izl'=>$res['izl'],
- 'siz'=>$res['siz'],
- 'stil'=>$res['stil'],
- 'meh'=>$res['meh'],
- 'mont'=>$res['mont'],
- 'tpod'=>$res['tpod'],
- 'upr'=>$res['upr'],
- 'naznach'=>$res['naznach'],
- 'kompl'=>$res['kompl'],
- 'garant'=>$res['garant'],
- 'foto'=>$res['foto'],
- 'foto1'=>$res['foto1'],
- 'opis'=>$res['opis'],
- 'cena'=>$res[23],
- 'cena_china'=>$res[35],
- 'kolvo'=>$res[34],
- 'data'=>$res[33],
- 'visibl'=>$res['visibl']
- ];
- }
- return $result;
- }
- }
- /******************Вывод всего товара с коллекцей и категорией************************************/
- public function get_all_tov($art=false, $visibl){
- if (empty($art)) {
- $visibl = $this->replace_attr($visibl);
- if ($visibl == 3) {
- $r = $this->dbs->prepare('SELECT * FROM tovar as t, typs as c, kol as k WHERE t.id_typ=c.id AND t.id_kol=k.id ORDER BY t.art ASC;');
- $r->execute();
- } else {
- $r = $this->dbs->prepare('SELECT * FROM tovar as t, typs as c, kol as k WHERE t.id_typ=c.id AND t.id_kol=k.id AND t.visibl=:v ORDER BY t.art ASC;');
- $r->execute([':v' => $visibl]);
- }
- }else{
- $art=$this->replace_attr($art);
- $visibl = $this->replace_attr($visibl);
- if ($visibl==3){
- $r = $this->dbs->prepare('SELECT * FROM tovar as t, typs as c, kol as k WHERE t.id_typ=c.id AND t.id_kol=k.id AND t.art=:a ORDER BY t.art ASC;');
- $r->execute([':a'=>$art]);
- }else {
- $r = $this->dbs->prepare('SELECT * FROM tovar as t, typs as c, kol as k WHERE t.id_typ=c.id AND t.id_kol=k.id AND t.visibl=:v AND t.art=:a ORDER BY t.art ASC;');
- $r->execute([':v' => $visibl, ':a' => $art]);
- }
- }
- if ($r->rowCount()!=0){
- $i=0;
- foreach ($r as $res){
- $result[$i]=[
- 'id'=>$res[0],
- 'id_typ'=>$res['id_typ'],
- 'id_kol'=>$res['id_kol'],
- 'art'=>$res['art'],
- 'naz_t'=>$res['naz'],
- 'naz_k'=>$res['nazvs'],
- 'naz_c'=>$res['nazv'],
- 'mat'=>$res['mat'],
- 'forma'=>$res['forma'],
- 'color'=>$res['color'],
- 'aer'=>$res['aer'],
- 'diver'=>$res['diver'],
- 'izl'=>$res['izl'],
- 'siz'=>$res['siz'],
- 'stil'=>$res['stil'],
- 'meh'=>$res['meh'],
- 'mont'=>$res['mont'],
- 'tpod'=>$res['tpod'],
- 'upr'=>$res['upr'],
- 'naznach'=>$res['naznach'],
- 'kompl'=>$res['kompl'],
- 'garant'=>$res['garant'],
- 'foto'=>$res['foto'],
- 'foto1'=>$res['foto1'],
- 'opis'=>$res['opis'],
- 'cena'=>$res['cena'],
- 'visibl'=>$res['visibl']
- ];
- $i++;
- }
- return $result;
- }else $GLOBALS['error']="Список товара пуст";
- }
- /*********************Вывод товара соответствующей коллекции**************************************/
- public function get_tov_kol($id_kol, $not_search, $order){
- if ($order=='')$order='t.naz ASC';
- if ($order=='rand')$order='RAND() LIMIT 1';
- $id_kol=$this->replace_attr($id_kol);
- if ($not_search!='')$not_search=$this->replace_attr_search($not_search);else $not_search=$this->replace_attr($not_search);
- #print "{ ".$id_kol." - ".$not_search." - ".$order." }";
- $r=$this->dbs->prepare('SELECT * FROM tovar AS t, kol AS k, typs AS tp WHERE t.id_kol=k.id and t.id_typ=tp.id and t.visibl<>0 and t.id_kol=:kol and t.foto NOT LIKE :search ORDER BY '.$order.';');
- $r->execute([':kol'=>$id_kol, ':search'=>$not_search]);
- $i=0;
- foreach ($r as $res){
- $result[$i] = [
- 'id'=>$res[0],
- 'id_typ'=>$res['id_typ'],
- 'id_kol'=>$res['id_kol'],
- 'art'=>$res['art'],
- 'naz'=>$res['naz'],
- 'mat'=>$res['mat'],
- 'forma'=>$res['forma'],
- 'color'=>$res['color'],
- 'aer'=>$res['aer'],
- 'diver'=>$res['diver'],
- 'izl'=>$res['izl'],
- 'siz'=>$res['siz'],
- 'stil'=>$res['stil'],
- 'meh'=>$res['meh'],
- 'mont'=>$res['mont'],
- 'tpod'=>$res['tpod'],
- 'upr'=>$res['upr'],
- 'naznach'=>$res['naznach'],
- 'kompl'=>$res['kompl'],
- 'garant'=>$res['garant'],
- 'foto'=>$res['foto'],
- 'foto1'=>$res['foto1'],
- 'opis'=>$res['opis'],
- 'cena'=>$res['cena'],
- 'visibl'=>$res['visibl']
- ];
- $i++;
- }
- return $result;
- }
- /*************************Вывод товара соответствующий ID*****************************************/
- public function get_tov($id){
- $id=$this->replace_attr($id);
- $r=$this->dbs->prepare('SELECT * FROM tovar AS t, kol AS k, typs AS tp WHERE t.id_kol=k.id and t.id_typ=tp.id and t.visibl<>0 and t.id=:id LIMIT 1;');
- $r->execute([':id'=>$id]);
- //$r->debugDumpParams();
- foreach ($r as $res){
- $result[0] = [
- 'id'=>$res[0],
- 'id_typ'=>$res['id_typ'],
- 'id_kol'=>$res['id_kol'],
- 'art'=>$res['art'],
- 'naz'=>$res['naz'],
- 'mat'=>$res['mat'],
- 'forma'=>$res['forma'],
- 'color'=>$res['color'],
- 'aer'=>$res['aer'],
- 'diver'=>$res['diver'],
- 'izl'=>$res['izl'],
- 'siz'=>$res['siz'],
- 'stil'=>$res['stil'],
- 'meh'=>$res['meh'],
- 'mont'=>$res['mont'],
- 'tpod'=>$res['tpod'],
- 'upr'=>$res['upr'],
- 'naznach'=>$res['naznach'],
- 'kompl'=>$res['kompl'],
- 'garant'=>$res['garant'],
- 'foto'=>$res['foto'],
- 'foto1'=>$res['foto1'],
- 'opis'=>$res['opis'],
- 'cena'=>$res['cena'],
- 'visibl'=>$res['visibl'],
- 'nazv'=>$res['nazv'],
- 'nazvs'=>$res['nazvs']
- ];
- }
- return $result;
- }
- /*********************Склонение имен**************************************/
- public function num_decline( $number, $titles, $param2 = '', $param3 = '' ){
- if( is_string($titles) )
- $titles = preg_split('~,\s*~', $titles );
- if( count($titles) < 3 )
- $titles = [ func_get_arg(1), func_get_arg(2), func_get_arg(3) ];
- $cases = [2, 0, 1, 1, 1, 2];
- $intnum = abs( intval( strip_tags( $number ) ) );
- return $number .' '. $titles[ ($intnum % 100 > 4 && $intnum % 100 < 20) ? 2 : $cases[min($intnum % 10, 5)] ];
- }
- /*********************Вывод товара соответствующей категории**************************************/
- public function get_tov_typ($id_typ, $not_search, $order){
- if ($order=='')$order='t.naz ASC';
- if ($order=='rand')$order='RAND() LIMIT 1';
- $id_typ=$this->replace_attr($id_typ);
- if ($not_search!='')$not_search=$this->replace_attr_search($not_search);else $not_search=$this->replace_attr($not_search);
- //print "{-".$id_typ."-".$not_search."-}";
- $r=$this->dbs->prepare('SELECT * FROM tovar AS t, kol AS k, typs AS tp WHERE t.id_kol=k.id and t.id_typ=tp.id and t.visibl<>0 and t.id_typ=:typ and foto NOT LIKE :search ORDER BY '.$order.';');
- //print 'SELECT * FROM tovar AS t, kol AS k, typs AS tp WHERE t.id_kol=k.id and t.id_typ=tp.id and t.id_typ=:typ and foto NOT LIKE :search ORDER BY '.$order.';';
- $r->execute([':typ'=>$id_typ, ':search'=>$not_search]);
- //$r->debugDumpParams();
- $i=0;
- foreach ($r as $res){
- $result[$i] = [
- 'id'=>$res[0],
- 'id_typ'=>$res['id_typ'],
- 'id_kol'=>$res['id_kol'],
- 'art'=>$res['art'],
- 'naz'=>$res['naz'],
- 'mat'=>$res['mat'],
- 'forma'=>$res['forma'],
- 'color'=>$res['color'],
- 'aer'=>$res['aer'],
- 'diver'=>$res['diver'],
- 'izl'=>$res['izl'],
- 'siz'=>$res['siz'],
- 'stil'=>$res['stil'],
- 'meh'=>$res['meh'],
- 'mont'=>$res['mont'],
- 'tpod'=>$res['tpod'],
- 'upr'=>$res['upr'],
- 'naznach'=>$res['naznach'],
- 'kompl'=>$res['kompl'],
- 'garant'=>$res['garant'],
- 'foto'=>$res['foto'],
- 'foto1'=>$res['foto1'],
- 'opis'=>$res['opis'],
- 'cena'=>$res['cena'],
- 'visibl'=>$res['visibl'],
- 'nazv'=>$res['nazv'],
- 'nazvs'=>$res['nazvs']
- ];
- $i++;
- }
- return $result;
- }
- /***************************В корзину****************************************/
- public function bay($id, $user){
- $r=$this->dbs->prepare('INSERT INTO recycler(id_kl, id_tov, data) VALUES (:users, :id, now());');
- $id=$this->replace_attr($id);
- $user=$this->replace_attr($user);
- if ($r->execute([':users'=>$user, ':id'=>$id])){
- $GLOBALS['success']="Товар успешно добавлен в корзину";
- }else $GLOBALS['error']="Произошла ошибка при добавлении товара в корзину";
- }
- /*********************В корзину в один клик**********************************/
- public function bay_one($id){
- $r=$this->dbs->prepare('INSERT INTO one_click(id_tov, zakaz) VALUES(:id, :zakaz);');
- $id=$this->replace_attr($id);
- $zakaz=strtoupper(substr($_COOKIE['unique'],1,15));
- if (trim($zakaz)!='') {
- if ($r->execute([':id' => $id, ':zakaz' => $zakaz])) {
- $GLOBALS['success']="Товар успешно добавлен в корзину";
- } else $GLOBALS['error']="Произошла ошибка при добавлении товара в корзину";
- }
- }
- /********************Получить все коллекции********************************/
- public function get_kol($id=false){
- if (empty($id)) {
- $r = $this->dbs->prepare('SELECT * FROM kol ORDER BY nazvs ASC;');
- $r->execute();
- $i = 0;
- foreach ($r as $res) {
- $result[$i] = ['id' => $res['id'], 'nazv' => $res['nazvs']];
- $i++;
- }
- return $result;
- }else{
- $id=$this->replace_attr($id);
- $r=$this->dbs->prepare('SELECT * FROM kol WHERE id=:i;');
- $r->execute([':i'=>$id]);
- foreach ($r as $res){
- $result=['id'=>$res['id'], 'nazv'=>$res['nazvs']];
- }
- return $result;
- }
- }
- /*********************Получить список заказов ONE**********************************/
- public function get_zone($id=false){
- if (!empty($id)){
- }else{
- $r = $this->dbs->prepare('SELECT fio, tel, mail, adres, zakaz, status FROM one_click WHERE zakaz<>"" GROUP BY fio, tel, mail, adres, zakaz, status;');
- $r->execute();
- $i=0;
- foreach ($r as $res){
- $result[$i]=['fio'=>$res['fio'], 'tel'=>$res['tel'], 'mail'=>$res['mail'], 'adres'=>$res['adres'], 'zakaz'=>$res['zakaz'], 'status'=>$res['status']];
- $i++;
- }
- return $result;
- }
- }
- /***********************Получить список заказов************************************/
- public function get_zak($id=false){
- if (!empty($id)) {
- $id = $this->replace_attr($id);
- $r = $this->dbs->prepare('SELECT zakaz, status FROM recycler WHERE status>0 and id_kl=:i GROUP BY zakaz, status;');
- $r->execute([':i' => $id]);
- $i = 0;
- foreach ($r as $res) {
- $result[$i] = ['zakaz' => $res['zakaz'], 'status' => $res['status']];
- $i++;
- }
- return $result;
- }else{
- $r = $this->dbs->prepare('SELECT r.zakaz, r.status, u.id, u.login FROM recycler as r, users as u WHERE r.id_kl=u.id GROUP BY zakaz, status;');
- $r->execute();
- $i = 0;
- foreach ($r as $res){
- $result[$i] = ['zakaz' => $res['zakaz'], 'status' => $res['status'], 'id_kl'=>$res['id'], 'login'=>$res['login']];
- $i++;
- }
- return $result;
- }
- }
- /*********************Получить содержимое корзины**********************************/
- public function get_korz($user, $status, $zak=false)
- {
- $user = $this->replace_attr($user);
- $status = $this->replace_attr($status);
- if (empty($zak)) {
- $r = $this->dbs->prepare('select t.art, t.naz, count(r.id_tov) as kol, (count(r.id_tov)*t.cena) as suma, t.cena, r.status, r.id_kl from tovar as t, recycler as r where r.id_tov=t.id and r.id_kl=:u and r.status=:s GROUP BY t.art;');
- $r->execute([':u' => $user, ':s' => $status]);
- $i = 0;
- } else {
- $zak = $this->replace_attr($zak);
- $r = $this->dbs->prepare('select t.art, t.naz, count(r.id_tov) as kol, (count(r.id_tov)*t.cena) as suma, t.cena, r.status, r.id_kl from tovar as t, recycler as r where r.id_tov=t.id and r.id_kl=:users and r.status=:status and r.zakaz=:zakaz GROUP BY t.art;');
- $r->execute([':users' => $user, ':status' => $status, ':zakaz' => $zak]);
- }
- $i = 0;
- foreach ($r as $res) {
- $result[$i] = ['art' => $res['art'], 'naz' => $res['naz'], 'kol' => $res['kol'], 'suma' => $res['suma'], 'cena' => $res['cena'], 'status' => $res['status'], 'id_kl' => $res['id_kl']];
- $i++;
- }
- return $result;
- }
- /**************************Удаление заказа*****************************************/
- public function del_korz($user=false, $status=false, $zakaz=false){
- if (!empty($zakaz)){
- $zakaz=$this->replace_attr($zakaz);
- $r = $this->dbs->prepare('DELETE FROM recycler WHERE zakaz=:z;');
- if ($r->execute([':z'=>$zakaz])){$GLOBALS['success']="Заказ успешно удален!";}else $GLOBALS['error']="Произошла ошибка при удалении заказа";
- }else{
- $user=$this->replace_attr($user);
- $status=$this->replace_attr($status);
- $r = $this->dbs->prepare('DELETE FROM recycler WHERE id_kl=:u and status=:s;');
- if ($r->execute([':s'=>$status, ':u'=>$user])){$GLOBALS['success']="Заказ успешно удален!";}else $GLOBALS['error']="Произошла ошибка при удалении заказа";
- }
- }
- /*************************Удаление товара******************************************/
- public function del_tovtest($id){
- $id=$this->replace_attr($id);
- $r=$this->dbs->prepare('DELETE FROM tovar_test WHERE id=:i;');
- if ($r->execute([':i'=>$id])){$GLOBALS['success']="Товар успешно удален";}else $GLOBALS['error']="Произошла ошибка при удалении товара";
- }
- /*************************Удаление товара******************************************/
- public function del_tov($id){
- $id=$this->replace_attr($id);
- $r=$this->dbs->prepare('DELETE FROM tovar WHERE id=:i;');
- if ($r->execute([':i'=>$id])){$GLOBALS['success']="Товар успешно удален";}else $GLOBALS['error']="Произошла ошибка при удалении товара";
- }
- /************************Добавление товара*****************************************/
- public function add_tov($mas){
- $str='INSERT INTO tovar SET ';
- $mas=array_diff($mas, array('', NULL, false));
- $r=$this->dbs->prepare('SELECT * FROM tovar WHERE art=:a LIMIT 1;');
- $mas['art']=$this->replace_attr($mas['art']);
- $r->execute([':a'=>$mas['art']]);
- if ($r->rowCount()==0) {
- foreach ($mas as $key => $value) {
- if (($key=='id_typ') || ($key=='id_kol')){
- if ($value[0]=='{') {
- $value = explode("{", $value);
- $value = explode("}", $value[1]);
- $value = $value[0];
- }
- }
- $value = $this->replace_attr($value);
- $str .= $key . "='" . $value . "', ";
- }
- $str .= ";";
- $str = str_replace(", ;", ";", $str);
- $r=$this->dbs->prepare($str);
- if ($r->execute()){$GLOBALS['success']="Товар успешно добавлен";}else $GLOBALS['error']="Произошла ошибка при добавлении товара";
- }else $GLOBALS['error']="Товар с данным артикулом уже существует";
- }
- /******************Редактирование товара по прайсу test*********************************/
- public function edit_price_test($mas){
- $str='UPDATE tovar_test SET ';
- $mas = array_diff($mas, array('', NULL, false));
- foreach ($mas as $key => $value){
- $value=$this->replace_attr($value);
- if ($key!='id') {
- $str .= $key . "='" . $value . "', ";
- }
- if ($key=='id') $id=$value;
- }
- $str.="WHERE id='".$id."'";
- $str=str_replace(", WHERE", " WHERE", $str);
- $r=$this->dbs->prepare($str);
- if ($r->execute()){$GLOBALS['success']="Данные о товаре успешно изменены";}else $GLOBALS['error']="Произошла ошибка при редактировании товара";
- }
- /******************Редактирование товара по прайсу*********************************/
- public function edit_price_tov($mas){
- $str='UPDATE tovar SET ';
- $mas = array_diff($mas, array('', NULL, false));
- foreach ($mas as $key => $value){
- $value=$this->replace_attr($value);
- if ($key!='id') {
- $str .= $key . "='" . $value . "', ";
- }
- if ($key=='id') $id=$value;
- }
- $str.="WHERE id='".$id."'";
- $str=str_replace(", WHERE", " WHERE", $str);
- $r=$this->dbs->prepare($str);
- if ($r->execute()){$GLOBALS['success']="Данные о товаре успешно изменены";}else $GLOBALS['error']="Произошла ошибка при редактировании товара";
- }
- /************************Удаление заказа One***************************************/
- public function delete_one($zakaz){
- $zakaz=$this->replace_attr($zakaz);
- $r=$this->dbs->prepare('delete from one_click where zakaz=:zakaz;');
- if ($r->execute([':zakaz'=>$zakaz])){$GLOBALS['success']='Заказ успешно удален';}else $GLOBALS['error']='Произошла ошибка при удалении заказа';
- }
- /*************************Оформление заказа****************************************/
- public function update_korz($id=false, $status, $zakaz=false, $f=false, $november=false){
- if (!empty($id)) {
- $id = $this->replace_attr($id);
- $status = $this->replace_attr($status);
- $zakaz = strtoupper(substr($_COOKIE['unique'], 1, 15));
- $r = $this->dbs->prepare('UPDATE recycler SET status=:s, zakaz=:z WHERE status=:st and id_kl=:i;');
- if ($r->execute([':s' => ($status + 1), ':z' => $zakaz, ':st' => $status, ':i' => $id])) {
- $GLOBALS['success']="Заказ успешно оформлен";
- } else $GLOBALS['error']="Произошла ошибка при оформлении заказа";
- }else{
- $status = $this->replace_attr($status);
- $zakaz = $this->replace_attr($zakaz);
- $r = $this->dbs->prepare('UPDATE recycler SET status=:s WHERE zakaz=:z;');
- if ($f=='0') {
- if ($status=='0'){
- if ($r->execute([':s' => $status, ':z' => $zakaz])) {
- $GLOBALS['success']="Заказ перенесен в корзину пользователя для редактирования";
- } else $GLOBALS['error']="Произошла ошибка при отмене заказа";
- }else {
- if ($r->execute([':s' => $status, ':z' => $zakaz])) {
- $GLOBALS['success']="Заказ успешно отправлен";
- } else $GLOBALS['error']="Произошла ошибка при отправке заказа";
- }
- }else{
- if ($r->execute([':s' => $status, ':z' => $zakaz])) {
- $GLOBALS['success']="Получение заказа подтверждено";
- } else $GLOBALS['error']="Произошла ошибка при подтверждении получения заказа";
- }
- }
- }
- /********************Изменение статуса заказа One**********************************/
- public function update_korz_one($zakaz, $status, $f){
- $zakaz=$this->replace_attr($zakaz);
- $status=$this->replace_attr($status);
- $r=$this->dbs->prepare('UPDATE one_click SET status=:s WHERE zakaz=:z;');
- if ($f==0){
- if ($r->execute([':s'=>$status, ':z'=>$zakaz])){
- $GLOBALS['success']="Заказ успешно отправлен";
- }else $GLOBALS['error']="Произошла ошибка при отправке заказа";
- }else{
- if ($r->execute([':s' => $status, ':z' => $zakaz])) {
- $GLOBALS['success']="Получение заказа подтверждено";
- } else $GLOBALS['error']="Произошла ошибка при подтверждении получения заказа";
- }
- }
- /***********************Оформление заказа One**************************************/
- public function update_one($zakaz, $status, $respon, $fio=false, $tel=false, $mail=false, $adres=false){
- $recaptcha=$respon['g-recaptcha-response'];
- if(!empty($recaptcha)) {
- $google_url = "https://www.google.com/recaptcha/api/siteverify";
- $secret = '6Ld9134UAAAAAKDtW4t5WswxaNo6nRsT6UopGPSM';
- $ip = $_SERVER['REMOTE_ADDR'];
- $url = $google_url . "?secret=" . $secret . "&response=" . $recaptcha . "&remoteip=" . $ip;
- $res = $this->getCurlData($url);
- $res = json_decode($res, true);
- //reCaptcha введена
- if ($res['success']) {
- if ($status != '-1') {
- $status = $this->replace_attr($status);
- $zakaz = $this->replace_attr($zakaz);
- $r = $this->dbs->prepare('UPDATE one_click SET status=:status WHERE zakaz=:zakaz;');
- if ($r->execute([':status' => $status, ':zakaz' => $zakaz])) {
- $GLOBALS['success']='Статус заказа успешно изменен';
- echo "<script language=\"JavaScript\">setTimeout(function(){document.location.href = 'http://xzels.adiante.su';}, 3 * 1000);</script>";
- } else $GLOBALS['error']='Произошла ошибка при изменении статуса заказа';
- } else {
- $zakaz = $this->replace_attr($zakaz);
- $fio = $this->replace_attr($fio);
- $tel = $this->replace_attr($tel);
- $mail = $this->replace_attr($mail);
- $adres = $this->replace_attr($adres);
- $r = $this->dbs->prepare('UPDATE one_click SET fio=:fio, tel=:tel, mail=:mail, adres=:adres, status=1 WHERE zakaz=:zakaz;');
- if ($r->execute([':fio' => $fio, ':tel' => $tel, ':mail' => $mail, ':adres' => $adres, ':zakaz' => $zakaz])) {
- $GLOBALS['success']='Заказ успешно оформлен';
- echo "<script language=\"JavaScript\">setTimeout(function(){document.location.href = 'http://xzels.adiante.su';}, 3 * 1000);</script>";
- } else $GLOBALS['error']='Произошла ошибка при оформлении заказа';
- }
- }else $GLOBALS['error']='Пожалуйста повторите ввод reCAPTCHA';
- }else $GLOBALS['error']='Пожалуйста повторите ввод reCAPTCHA, она пустая';
- }
- /*******************Получить содержимое корзины One********************************/
- public function get_korz_one($stat, $zakaz=false){
- $stat=$this->replace_attr($stat);
- if (empty($zakaz)){
- $zakaz=strtoupper(substr($_COOKIE['unique'],1,15));
- }else $zakaz=$this->replace_attr($zakaz);
- $r=$this->dbs->prepare('select t.art, t.naz, count(o.id_tov) as kol, (count(o.id_tov)*t.cena) as suma, t.cena, o.status, o.fio, o.tel, o.mail, o.adres from tovar as t, one_click as o where o.id_tov=t.id and o.zakaz=:zakaz and o.status=:status GROUP BY t.art;');
- $r->execute([':zakaz'=>$zakaz, ':status'=>$stat]);
- #$r->debugDumpParams();
- $i=0;
- foreach ($r as $res){
- $result[$i]=['art'=>$res['art'], 'naz'=>$res['naz'], 'kol'=>$res['kol'], 'suma'=>$res['suma'], 'cena'=>$res['cena'], 'fio'=>$res['fio'], 'tel'=>$res['tel'], 'status'=>$res['status'], 'mail'=>$res['mail'], 'adres'=>$res['adres']];
- $i++;
- }
- return $result;
- }
- /*********************Изменить данные пользователя********************************/
- public function update_user($id, $status=false, $pas=false, $nam=false, $tel=false, $mail=false, $adres=false, $org=false){
- if (empty($status)){
- $id=$this->replace_attr($id);
- $nam=$this->replace_attr($nam);
- $tel=$this->replace_attr($tel);
- $mail=$this->replace_attr($mail);
- $adres=$this->replace_attr($adres);
- $org=$this->replace_attr($org);
- if ($pas!=''){
- $pas=$this->replace_attr_md5($pas);
- $r=$this->dbs->prepare('UPDATE users set nam=:n, tel=:t, mail=:m, adres=:a, org=:o, pasw=:p WHERE id=:i');
- if ($r->execute([':n'=>$nam, ':t'=>$tel, ':m'=>$mail, ':a'=>$adres, ':o'=>$org, ':p'=>$pas, ':i'=>$id])){$GLOBALS['success']="Данные успешно обновлены";}else $GLOBALS['error']="Произошла ошибка обновления онформации о пользователе";
- }else{
- $r=$this->dbs->prepare('UPDATE users set nam=:n, tel=:t, mail=:m, adres=:a, org=:o WHERE id=:i');
- if ($r->execute([':n'=>$nam, ':t'=>$tel, ':m'=>$mail, ':a'=>$adres, ':o'=>$org, ':i'=>$id])){$GLOBALS['success']="Данные успешно обновлены";}else $GLOBALS['error']="Произошла ошибка обновления онформации о пользователе";
- }
- }else{
- $id=$this->replace_attr($id);
- $status=$this->replace_attr($status);
- $r=$this->dbs->prepare('UPDATE users SET status=:s WHERE id=:i;');
- if ($r->execute([':s'=>$status, ':i'=>$id])){$GLOBALS['success']="Статус пользователя успешно изменен";}else $GLOBALS['error']="Произошла ошибка при изменении статуса пользователя";
- }
- }
- /***********************Изменить категорию товара*********************************/
- public function edit_typ($id, $nazv){
- if ($id[0]=='{') {
- $id = explode("{", $id);
- $id = explode("}", $id[1]);
- $id=$id[0];
- }
- $id=$this->replace_attr($id);
- $nazv=$this->replace_attr($nazv);
- $r=$this->dbs->prepare('UPDATE typs SET nazv=:n WHERE id=:i;');
- if ($r->execute([':n'=>$nazv, ':i'=>$id])){$GLOBALS['success']="Название категории успешно изменено";}else $GLOBALS['error']="Произошла ошибка при изменении названия категории";
- }
- /************************Удалить категорию товара*********************************/
- public function del_typ($id){
- if ($id[0]=='{') {
- $id = explode("{", $id);
- $id = explode("}", $id[1]);
- $id=$id[0];
- }
- $id=$this->replace_attr($id);
- $r=$this->dbs->prepare('DELETE FROM typs WHERE id=:i;');
- if ($r->execute([':i'=>$id])){$GLOBALS['success']="Категория успешно удалена";}else $GLOBALS['error']="Произошла ошибка при удалении категории";
- }
- /***********************Добавить категорию товара*********************************/
- public function insert_typ($nazv){
- $nazv=$this->replace_attr($nazv);
- $r=$this->dbs->prepare('INSERT INTO typs(nazv) values (:n);');
- if ($r->execute([':n'=>$nazv])){$GLOBALS['success']="Категория товара успешно добавлена";}else $GLOBALS['error']="Произошла ошибка при добавлении категории товара";
- }
- /***********************Изменить коллекцию товара*********************************/
- public function edit_kol($id, $nazv){
- if ($id[0]=='{') {
- $id = explode("{", $id);
- $id = explode("}", $id[1]);
- $id=$id[0];
- }
- $id=$this->replace_attr($id);
- $nazv=$this->replace_attr($nazv);
- $r=$this->dbs->prepare('UPDATE kol SET nazvs=:n WHERE id=:i;');
- if ($r->execute([':n'=>$nazv, ':i'=>$id])){$GLOBALS['success']="Название коллекции успешно изменено";}else $GLOBALS['error']="Произошла ошибка при изменении названия коллекции";
- }
- /************************Удалить коллекцию товара*********************************/
- public function del_kol($id){
- if ($id[0]=='{') {
- $id = explode("{", $id);
- $id = explode("}", $id[1]);
- $id=$id[0];
- }
- $id=$this->replace_attr($id);
- $r=$this->dbs->prepare('DELETE FROM kol WHERE id=:i;');
- if ($r->execute([':i'=>$id])){$GLOBALS['success']="Коллекция успешно удалена";}else $GLOBALS['error']="Произошла ошибка при удалении коллекции";
- }
- /***********************Добавить коллекцию товара*********************************/
- public function insert_kol($nazv){
- $nazv=$this->replace_attr($nazv);
- $r=$this->dbs->prepare('INSERT INTO kol(nazvs) values (:n);');
- if ($r->execute([':n'=>$nazv])){$GLOBALS['success']="Коллекция товара успешно добавлена";}else $GLOBALS['error']="Произошла ошибка при добавлении коллекции товара";
- }
- /***************************Добавить отзывов**************************************/
- public function insert_otz($user, $otziv){
- $user=$this->replace_attr($user);
- $otziv=$this->replace_attr($otziv);
- $r=$this->dbs->prepare('INSERT INTO otzivs(from_n, otziv, dat) VALUES(:u, :o, now());');
- if ($r->execute([':u'=>$user, ':o'=>$otziv])){$GLOBALS['success']="Отзыв успешно добавлен";}else $GLOBALS['error']="Произошла ошибка при добавлении отзыва";
- }
- /************************Получить список отзывов**********************************/
- public function get_otz($id=false){
- if (empty($id)){
- $r=$this->dbs->prepare('SELECT * FROM otzivs ORDER BY id DESC;');
- $r->execute();
- if (($r->rowCount())!=0){
- $i=0;
- foreach ($r as $res){
- $result[$i]=['id'=>$res['id'], 'user'=>$res['from_n'], 'otziv'=>$res['otziv'], 'dat'=>$res['dat']];
- $i++;
- }
- return $result;
- }
- }
- }
- /****************************Удалить отзыв****************************************/
- public function delete_otz($id){
- $id=$this->replace_attr($id);
- $r=$this->dbs->prepare('DELETE FROM otzivs WHERE id=:i;');
- if ($r->execute([':i'=>$id])){$GLOBALS['success']="Отзыв успешно удален";}else $GLOBALS['error']="Произошла ошибка при удалении отзыва";
- }
- /*********************Получить данные пользователя********************************/
- public function get_user($id=false, $log=false){
- if (empty($log)) {
- if (!empty($id)) {
- if ($id[0] == '{') {
- $id = explode("{", $id);
- $id = explode("}", $id[1]);
- $id = $id[0];
- }
- $id = $this->replace_attr($id);
- $r = $this->dbs->prepare('SELECT * FROM users WHERE id=:id;');
- $r->execute(['id' => $id]);
- } else {
- $r = $this->dbs->prepare('SELECT * FROM users;');
- $r->execute();
- }
- $i = 0;
- foreach ($r as $res) {
- $result[$i] = ['id' => $res['id'], 'login' => $res['login'], 'nam' => $res['nam'], 'tel' => $res['tel'], 'adres' => $res['adres'], 'mail' => $res['mail'], 'status' => $res['status'], 'org' => $res['org']];
- $i++;
- }
- return $result;
- }else {
- $log = $this->replace_attr($log);
- $r=$this->dbs->prepare('SELECT * FROM users WHERE login=:l');
- $r->execute([':l'=>$log]);
- if ($r->rowCount()!=0){
- foreach ($r as $res){
- $result['mail']=$res['mail'];
- $result['md']=$res['pasw'];
- $result['id']=$res['id'];
- }
- return $result;
- }else $GLOBALS['error']="Пользователь с таким логином не существует";
- }
- }
- /********************Получить рандомно 7 категорий********************************/
- public function get_typ_rand(){
- $r=$this->dbs->prepare('select * from typs ORDER BY RAND() LIMIT 7;');
- $r->execute();
- $i=0;
- foreach ($r as $res){
- $result[$i]=['id'=>$res['id'], 'nazv'=>$res['nazv']];
- $i++;
- }
- return $result;
- }
- /************************Восстановление пароля*************************************/
- public function remember($id, $md, $pas){
- $id=$this->replace_attr($id);
- $md=$this->replace_attr($md);
- $r=$this->dbs->prepare('SELECT * FROM users WHERE id=:i AND pasw=:p;');
- $r->execute([':i'=>$id, ':p'=>$md]);
- if ($r->rowCount()!=0){
- $pas=$this->replace_attr_md5($pas);
- $r=$this->dbs->prepare('UPDATE users SET pasw=:pw WHERE id=:i;');
- if ($r->execute([':pw'=>$pas, ':i'=>$id])){$GLOBALS['success']="Пароль успешно изменен";}else $GLOBALS['error']="Произошла ошибка при изменении пароля";
- }
- }
- /********************Получить рандомно 11 коллекций********************************/
- public function get_kol_rand(){
- $r=$this->dbs->prepare('select * from kol ORDER BY RAND() LIMIT 11;');
- $r->execute();
- $i=0;
- foreach ($r as $res){
- $result[$i]=['id'=>$res['id'], 'nazvs'=>$res['nazvs']];
- $i++;
- }
- return $result;
- }
- /****************************Авторизация******************************************/
- public function autorization($login, $password){
- $r=$this->dbs->prepare('select * from users where login=:login and pasw=:password');
- $login = $this->replace_attr($login);
- $password = $this->replace_attr_md5($password);
- if ($r->execute([':login'=>$login, 'password'=>$password])){
- foreach ($r as $res){
- $_SESSION['id'] = $res['id'];
- $_SESSION['login'] = $res['login'];
- $_SESSION['name'] = $res['nam'];
- $_SESSION['tel'] = $res['tel'];
- $_SESSION['status'] = $res['status'];
- $_SESSION['adres'] = $res['adres'];
- $_SESSION['mail'] = $res['mail'];
- $_SESSION['org'] = $res['org'];
- }
- $GLOBALS['success']="Добро пожаловать ".ucwords($_SESSION['login']);
- }else $GLOBALS['error']="Связка логин/пароль не найдена в базе";
- }
- /***************************Выход************************************************************/
- public function close_sessions(){
- session_destroy();
- echo "<script language='javascript'>document.location.replace(\"http://xzels.adiante.su\");</script>";
- }
- /***********************Удаление новости*****************************************************/
- public function del_news($id){
- if ($id[0] == '{') {
- $id = explode("{", $id);
- $id = explode("}", $id[1]);
- $id = $id[0];
- }
- $id=$this->replace_attr($id);
- $r=$this->dbs->prepare('DELETE FROM news WHERE id=:i;');
- if ($r->execute([':i'=>$id])){$GLOBALS['success']="Новость успешно удалена";}else $GLOBALS['error']="Произошла ошибка при удалении новости";
- }
- /********************Редактирование новости**************************************************/
- public function edit_news($id, $mas){
- if ($id[0] == '{') {
- $id = explode("{", $id);
- $id = explode("}", $id[1]);
- $id = $id[0];
- }
- $id=$this->replace_attr($id);
- $str='UPDATE news SET ';
- foreach ($mas as $key => $value){
- $value=$this->replace_attr($value);
- if ($key!='id') {
- $str .= $key . "='" . $value . "', ";
- }
- if ($key=='id') $id=$value;
- }
- $str.='data=now() ';
- $str.="WHERE id='".$id."'";
- $str=str_replace(", WHERE", " WHERE", $str);
- $r=$this->dbs->prepare($str);
- if ($r->execute()){$GLOBALS['success']="Новость успешно изменена";}else $GLOBALS['error']="Произошла ошибка при изменении новости";
- }
- /******************Получение списка новостей*************************************************/
- public function get_news($id=false){
- if (empty($id)){
- $r=$this->dbs->prepare('SELECT * FROM news ORDER BY id DESC;');
- $r->execute();
- }else{
- if ($id[0] == '{') {
- $id = explode("{", $id);
- $id = explode("}", $id[1]);
- $id = $id[0];
- }
- $id=$this->replace_attr($id);
- $r=$this->dbs->prepare('SELECT * FROM news WHERE id=:i;');
- $r->execute([':i'=>$id]);
- }
- if ($r->rowCount()!=0) {
- $i=0;
- foreach ($r as $res) {
- /* $result[$key] = $value;*/
- $result[$i]=[
- 'id'=>$res['id'],
- 'foto'=>$res['foto'],
- 'new'=>$res['new'],
- 'data'=>$res['data'],
- 'new_n'=>$res['new_n'],
- 'user'=>$res['user']
- ];
- $i++;
- }
- return $result;
- }else $GLOBALS['error']="Нет новостей";
- }
- /*********************Добавление новости*****************************************************/
- public function add_news($mas){
- $str='INSERT INTO news SET ';
- $mas=array_diff($mas, array('', NULL, false));
- foreach ($mas as $key => $value) {
- $value = $this->replace_attr($value);
- $str .= $key . "='" . $value . "', ";
- }
- $str .= "data=now();";
- $str = str_replace(", ;", ";", $str);
- $r=$this->dbs->prepare($str);
- if ($r->execute()){$GLOBALS['success']="Новость успешно добавлена";}else $GLOBALS['error']="Произошла ошибка при добавлении новости";
- }
- /*********************Преобразование URL*****************************************************/
- public function getCurlData($url)
- {
- $curl = curl_init();
- curl_setopt($curl, CURLOPT_URL, $url);
- curl_setopt($curl, CURLOPT_RETURNTRANSFER, 1);
- curl_setopt($curl, CURLOPT_TIMEOUT, 10);
- curl_setopt($curl, CURLOPT_USERAGENT, "Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US; rv:1.9.2.16) Gecko/20110319 Firefox/3.6.16");
- $curlData = curl_exec($curl);
- curl_close($curl);
- return $curlData;
- }
- /****************Преобразование полей для SQL********************************/
- public function replace_attr($atr){
- $result = substr($this->dbs->quote(trim(strtolower($atr))), 1, strlen($this->dbs->quote(trim(strtolower($atr)))) - 2);
- return $result;
- }
- /****************Преобразование полей %LIKE% для SQL********************************/
- public function replace_attr_search($atr){
- $result = substr($this->dbs->quote("%".trim(strtolower($atr))."%"), 1, strlen($this->dbs->quote("%".trim(strtolower($atr))."%")) - 2);
- return $result;
- }
- /****************Преобразование полей md5 для SQL********************************/
- public function replace_attr_md5($atr){
- $result = substr($this->dbs->quote(md5(trim(strtolower($atr)))), 1, strlen($this->dbs->quote(md5(trim(strtolower($atr))))) - 2);
- return $result;
- }
- /**********************Получение скидок******************************************/
- public function gets_sales($user=false){
- if (empty($user)){
- $r=$this->dbs->prepare('SELECT * FROM sales;');
- $r->execute();
- }else{
- if ($user[0]=='{'){
- $user = explode("{", $user);
- $user = explode("}", $user[1]);
- $user = $user[0];
- }
- $user=$this->replace_attr($user);
- $r=$this->dbs->prepare('SELECT * FROM sales WHERE id_cl=:i');
- $r->execute([':i'=>$user]);
- }
- $i=0;
- if ($r->rowCount()!=0) {
- foreach ($r as $res) {
- $result[$i] = [
- 'id' => $res['id'],
- 'id_cl' => $res['id_cl'],
- 'sale' => $res['sale']
- ];
- $i++;
- }
- return $result;
- }else{
- $result[0]['sale']='0';
- $result[0]['user']='no';
- return $result;
- }
- }
- /*********************Назначение скидок*****************************************/
- public function add_sales($use, $sale){
- if ($use[0]=='{') {
- $use = explode("{", $use);
- $use = explode("}", $use[1]);
- $use = $use[0];
- }
- $use=$this->replace_attr($use);
- $sale=$this->replace_attr($sale);
- $r=$this->gets_sales($use);
- if (isset($r[0]['user'])){
- $r=$this->dbs->prepare('INSERT INTO sales(id_cl, sale) VALUES (:u, :s)');
- if ($r->execute([':u'=>$use, ':s'=>$sale])){$GLOBALS['success']="Скидка успешно назначена";}else $GLOBALS['error']="Произошла ошибка при назначении скидки";
- }else{
- $r=$this->dbs->prepare('UPDATE sales SET sale=:s WHERE id_cl=:u;');
- if ($r->execute([':s'=>$sale,':u'=>$use])){$GLOBALS['success']="Скидка успешно назначена";}else $GLOBALS['error']="Произошла ошибка при назначении скидки";
- }
- }
- /*****************Удаление данных пользователя***********************************/
- public function del_user($id){
- $id=$this->replace_attr($id);
- $r=$this->dbs->prepare('DELETE FROM users WHERE id=:i');
- if ($r->execute([':i'=>$id])){$GLOBALS['success']="Пользователь успешно удален";}else $GLOBALS['error']="Произошла ошибка при удалении пользователя";
- }
- /**************Редактирование данных пользователя********************************/
- public function edit_user($mas){
- $str='UPDATE users SET ';
- $mas = array_diff($mas, array('', NULL, false));
- foreach ($mas as $key => $value){
- if ($key!='id') {
- if ($key!='pasw') {
- $value= $this->replace_attr($value);
- }else{
- $value = $this->replace_attr_md5($value);
- }
- $str .= $key . "='" . $value . "', ";
- }else $id=$this->replace_attr($value);
- }
- $str.="WHERE id='".$id."'";
- $str=str_replace(", WHERE", " WHERE", $str);
- $r=$this->dbs->prepare($str);
- if ($r->execute()){$GLOBALS['success']="Данные пользователя успешно изменены";}else $GLOBALS['error']="Произошла ошибка при изменении данных пользователя";
- }
- /*****************Удаление товара из заказа*******************************************/
- public function del_china($id){
- $id=$this->replace_attr($id);
- $r=$this->dbs->prepare('DELETE FROM zakaz WHERE id=:i;');
- if ($r->execute([':i'=>$id])){$GLOBALS['success']='Позиция заказа успешно удалена';}else $GLOBALS['error']='Произошла ошибка удаления позиции заказа';
- }
- /****************Для совершения заказа товара*****************************************/
- public function ins_zak($mas){
- $str="INSERT INTO zakaz SET ";
- if (isset ($mas['foto'])){
- $str.="art = '".$this->replace_attr($mas['foto'])."', ";
- $r=$this->dbs->prepare('SELECT foto FROM tovar WHERE art=:a LIMIT 1;');
- $r->execute([':a'=>$mas['foto']]);
- foreach ($r as $res){
- $str.="foto = '".$res['foto']."', ";
- }
- }
- if ($key='foto1'){
- $r=$this->dbs->prepare('SELECT foto FROM tovar WHERE art=:a LIMIT 1;');
- $mas['foto1']=$this->replace_attr($mas['foto1']);
- $r->execute([':a'=>$mas['foto1']]);
- foreach ($r as $res){
- $str.="foto1 = '".$res['foto']."', ";
- }
- }
- if ($key='foto2'){
- $r=$this->dbs->prepare('SELECT foto FROM tovar WHERE art=:a LIMIT 1;');
- $mas['foto2']=$this->replace_attr($mas['foto2']);
- $r->execute([':a'=>$mas['foto2']]);
- foreach ($r as $res){
- $str.="foto2 = '".$res['foto']."', ";
- }
- }
- if ($key='foto3'){
- $r=$this->dbs->prepare('SELECT foto FROM tovar WHERE art=:a LIMIT 1;');
- $mas['foto3']=$this->replace_attr($mas['foto3']);
- $r->execute([':a'=>$mas['foto3']]);
- foreach ($r as $res){
- $str.="foto3 = '".$res['foto']."', ";
- }
- }
- if ($key='foto4'){
- $r=$this->dbs->prepare('SELECT foto FROM tovar WHERE art=:a LIMIT 1;');
- $mas['foto4']=$this->replace_attr($mas['foto4']);
- $r->execute([':a'=>$mas['foto4']]);
- foreach ($r as $res){
- $str.="foto4 = '".$res['foto']."', ";
- }
- if (isset($mas['kolvo'])){
- $str.="kolvo = '".$this->replace_attr($mas['kolvo'])."', ";
- }
- if (isset($mas['suma'])){
- $str.="suma = '".$this->replace_attr($mas['suma'])."', ";
- }
- }
- $str.="z";
- $str=str_replace(', z', ';', $str);
- $r=$this->dbs->prepare($str);
- #print $str;
- if ($r->execute()){$GLOBALS['success']='Товар в заказ добавлен';}else $GLOBALS['error']='Произошла ошибка при добавлении товара в заказ';
- }
- /*****************Для получения списка заказа*****************************************/
- public function get_zakaz($lim=false){
- $this->dbs->setAttribute( PDO::ATTR_CASE, PDO::CASE_NATURAL );
- if (!empty($lim)){
- $r=$this->dbs->prepare('SELECT * FROM zakaz ORDER BY art DESC;');
- }else $r=$this->dbs->prepare('SELECT * FROM zakaz ORDER BY id DESC LIMIT 10;');
- $r->execute();
- if ($r->rowCount()!=0){
- $i=0;
- foreach ($r as $res) {
- foreach ($res as $key => $value){
- $result[$i][$key] =$value;
- }
- $i++;
- }
- return $result;
- }
- }
- /************Для прайса получение коллекций и категорий**************************/
- public function get_typ_kol($id, $data=false){
- $id=$this->replace_attr($id);
- if (!empty($data)) {
- $data = $this->replace_attr($data);
- $r=$this->dbs->prepare('select k.nazvs, k.id as id_kol, t.id_typ as id_typ, t.naz, count(t.naz) as schet from tovar as t, kol as k, prixod as p where p.id_t=t.id AND p.data=:d AND t.id_typ=:id and t.id_kol=k.id group by t.naz, k.nazvs ORDER by k.nazvs ASC;');
- $r->execute([':d'=>$data, ':id'=>$id]);
- }else{
- $r=$this->dbs->prepare('select k.nazvs, k.id as id_kol, t.id_typ as id_typ, t.naz, count(t.naz) as schet from tovar as t, kol as k where t.id_typ=:id and t.id_kol=k.id group by t.naz, k.nazvs ORDER by k.nazvs ASC;');
- $r->execute([':id'=>$id]);
- }
- if ($r->rowCount()!=0){
- $i=0;
- foreach ($r as $res){
- $result[$i]=[
- 'nazv_kol'=>$res['nazvs'],
- 'id_kol'=>$res['id_kol'],
- 'id_typ'=>$res['id_typ'],
- 'nazv_tov'=>$res['naz'],
- 'schet'=>$res['schet']
- ];
- $i++;
- }
- return $result;
- }
- }
- /************************Регистрация*********************************************/
- public function insert_user($respon){
- try {
- $recaptcha=$respon['g-recaptcha-response'];
- if(!empty($recaptcha)) {
- $google_url = "https://www.google.com/recaptcha/api/siteverify";
- $secret = '6Ld9134UAAAAAKDtW4t5WswxaNo6nRsT6UopGPSM';
- $ip = $_SERVER['REMOTE_ADDR'];
- $url = $google_url . "?secret=" . $secret . "&response=" . $recaptcha . "&remoteip=" . $ip;
- $res = $this->getCurlData($url);
- $res = json_decode($res, true);
- //reCaptcha введена
- if ($res['success']) {
- $r['log'] = $this->replace_attr($respon['log']);
- $r['pas'] = $this->replace_attr_md5($respon['pas']);
- $r['nam'] = $this->replace_attr($respon['nam']);
- $r['tel'] = $this->replace_attr($respon['tel']);
- $r['mail'] = $this->replace_attr($respon['mail']);
- $r['org'] = $this->replace_attr($respon['org']);
- $r['status'] = 0;
- $r['adres'] = $this->replace_attr($respon['adres']);
- $q = $this->dbs->prepare("select * from users where login=:login;");
- $q->execute([':login' => $r['log']]);
- if ($q->rowCount() != 0) {
- $GLOBALS['error']="Пользователь с таким логином уже существует";
- } else {
- #print "insert into users(login, pasw, nam, tel, adres, mail, status, org) values(" . $r['log'] . ", " . $r['pas'] . ", " . $r['nam'] . ", " . $r['tel'] . ", " . $r['adres'] . ", " . $r['mail'] . ", " . $r['status'] . ", " . $r['org'] . ");";
- $s = $this->dbs->prepare("insert into users(login, pasw, nam, tel, adres, mail, status, org) values(:login, :pasw, :nam, :tel, :adres, :mail, :status, :org);");
- if ($s->execute([':login' => $r['log'], ':pasw' => $r['pas'], ':nam' => $r['nam'], ':tel' => $r['tel'], ':adres' => $r['adres'], ':mail' => $r['mail'], ':status' => $r['status'], ':org' => $r['org']])) {
- $GLOBALS['success']="Пользователь успешно зарегистрирован";
- } else{$GLOBALS['error']="Произошла ошибка при регистрации пользователя";}
- }
- }else{$GLOBALS['error']="Пожалуйста повторите ввод reCAPTCHA";}
- }else{$GLOBALS['error']="Пожалуйста повторите ввод reCAPTCHA";}
- } catch (Exception $e) {
- print_r($e);
- }
- }
- /**********************Печать заказа на Китай******************************/
- public function xls_china(){
- $china = new \PHPEXcel();
- $china->setActiveSheetIndex(0);
- $active_sheet = $china->getActiveSheet();
- $active_sheet->getPageSetup()->setOrientation(\PHPExcel_Worksheet_PageSetup::ORIENTATION_PORTRAIT);
- $china->getActiveSheet()->getPageSetup()->setFitToPage(true);
- $china->getActiveSheet()->getPageSetup()->setFitToWidth(1);
- $china->getActiveSheet()->getPageSetup()->setFitToHeight(0);
- $active_sheet->getPageSetup()->SetPaperSize(\PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
- $active_sheet->setTitle("Заказ");
- $active_sheet->getPageMargins()->setTop(1);
- $active_sheet->getPageMargins()->setRight(0.75);
- $active_sheet->getPageMargins()->setLeft(0.75);
- $active_sheet->getPageMargins()->setBottom(1);
- $active_sheet->getHeaderFooter()->setOddHeader("&C".AG_NAME.": заказ");
- $active_sheet->getHeaderFooter()->setOddFooter('&L&B'.$active_sheet->getTitle().'&RСтраница &P из &N');
- $china->getDefaultStyle()->getFont()->setName('Arial');
- $china->getDefaultStyle()->getFont()->setSize(8);
- $active_sheet->getColumnDimension('A')->setWidth(33);
- $active_sheet->getColumnDimension('B')->setWidth(20);
- $active_sheet->getColumnDimension('C')->setWidth(33);
- $active_sheet->getColumnDimension('D')->setWidth(33);
- $active_sheet->getColumnDimension('E')->setWidth(33);
- $active_sheet->getColumnDimension('F')->setWidth(33);
- $active_sheet->getColumnDimension('G')->setWidth(20);
- $active_sheet->getColumnDimension('H')->setWidth(20);
- $active_sheet->setCellValue('H1','"'.AG_NAME.'"');
- $active_sheet->setCellValue('H2','Т. '.AG_PHONE1.', '.AG_PHONE2);
- $active_sheet->setCellValue('H3',AG_MAIL_SERVER);
- $china->getActiveSheet()->getCell('H3')->getHyperlink()->setUrl('mailto:'.AG_MAIL_SERVER);
- $active_sheet->setCellValue('H4',AG_MAIL_ADMIN);
- $china->getActiveSheet()->getCell('H4')->getHyperlink()->setUrl('mailto:'.AG_MAIL_ADMIN);
- $active_sheet->setCellValue('H5',AG_SERVER);
- $china->getActiveSheet()->getCell('H5')->getHyperlink()->setUrl('http://'.AG_SERVER);
- $active_sheet->getRowDimension('1')->setRowHeight(20);
- $active_sheet->getRowDimension('2')->setRowHeight(20);
- $active_sheet->getRowDimension('3')->setRowHeight(20);
- $active_sheet->getRowDimension('4')->setRowHeight(20);
- $active_sheet->getRowDimension('5')->setRowHeight(20);
- $active_sheet->mergeCells('A1:B5');
- $imagePath = $_SERVER["DOCUMENT_ROOT"]."/img/img_xls.png";
- if (file_exists($imagePath)) {
- $logo1 = new \PHPExcel_Worksheet_Drawing();
- $logo1->setPath($imagePath);
- $logo1->setCoordinates("A1");
- $logo1->setOffsetX(15);
- $logo1->setOffsetY(15);
- $logo1->setWorksheet($active_sheet);
- }
- $active_sheet->mergeCells('D1:D5');
- $active_sheet->setCellValue('D1','Заказ');
- $active_sheet->mergeCells('F1:F5');
- $imagePath = $_SERVER["DOCUMENT_ROOT"]."/img/gerb.png";
- if (file_exists($imagePath)) {
- $logo1 = new \PHPExcel_Worksheet_Drawing();
- $logo1->setPath($imagePath);
- $logo1->setWidthAndHeight(230,230);
- $logo1->setResizeProportional(true);
- $logo1->setCoordinates("F1");
- $logo1->setOffsetX(-25);
- $logo1->setOffsetY(0);
- $logo1->setWorksheet($active_sheet);
- }
- $active_sheet->mergeCells('A6:H6');
- $active_sheet->setCellValue('A6','Португальский производитель сантехники');
- $active_sheet->mergeCells('A7:G7');
- $active_sheet->setCellValue('A7','Дата создания заказа');
- $date = date('d-m-Y');
- $active_sheet->setCellValue('H7',$date);
- $active_sheet->getStyle('H7')
- ->getNumberFormat()->
- setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX14);
- $active_sheet->setCellValue('A9','Изображение');
- $active_sheet->setCellValue('B9','Артикул');
- $active_sheet->setCellValue('C9','Фото1');
- $active_sheet->setCellValue('D9','Фото2');
- $active_sheet->setCellValue('E9','Фото3');
- $active_sheet->setCellValue('F9','Фото4');
- $active_sheet->setCellValue('G9','Количество');
- $active_sheet->setCellValue('H9','Сумма');
- /***************************Стили**************************************************/
- $style_wrap = [
- 'borders'=>[
- 'outline' =>[
- 'style'=>\PHPExcel_Style_Border::BORDER_THICK
- ],
- 'allborders'=>[
- 'style'=>\PHPExcel_Style_Border::BORDER_THIN,
- 'color' =>[
- 'rgb'=>'000000'
- ]
- ]
- ]
- ];
- $style_header =[
- 'font'=>[
- 'bold' => true,
- 'name' => 'Times New Roman',
- 'size' => 16,
- 'color' =>[
- 'rgb' => 'FFFFFF'
- ]
- ],
- 'alignment' =>[
- 'horizontal' => \PHPExcel_STYLE_ALIGNMENT::HORIZONTAL_LEFT,
- 'vertical' => \PHPExcel_STYLE_ALIGNMENT::VERTICAL_CENTER,
- ],
- 'fill' =>[
- 'type' => \PHPExcel_STYLE_FILL::FILL_SOLID,
- 'color'=>[
- 'rgb' => '000000'
- ]
- ]
- ];
- $style_right =[
- 'font'=>[
- 'bold' => true,
- 'name' => 'Times New Roman',
- 'size' => 8
- ],
- 'alignment' =>[
- 'horizontal' => \PHPExcel_STYLE_ALIGNMENT::HORIZONTAL_RIGHT,
- 'vertical' => \PHPExcel_STYLE_ALIGNMENT::VERTICAL_CENTER,
- ]
- ];
- $style_slogan =[
- 'font'=>[
- 'bold' => true,
- 'italic' => true,
- 'name' => 'Times New Roman',
- 'size' => 16,
- 'color'=>[
- 'rgb' => '111111'
- ]
- ],
- 'alignment' =>[
- 'horizontal' => \PHPExcel_STYLE_ALIGNMENT::HORIZONTAL_CENTER,
- 'vertical' => \PHPExcel_STYLE_ALIGNMENT::VERTICAL_CENTER,
- ],
- 'fill' =>[
- 'type' => \PHPExcel_STYLE_FILL::FILL_SOLID,
- 'color'=>[
- 'rgb' => 'fdfef1'
- ]
- ],
- 'borders' =>[
- 'bottom' =>[
- 'style'=>\PHPExcel_Style_Border::BORDER_THICK
- ]
- ]
- ];
- $style_tdate =[
- 'alignment' =>[
- 'horizontal' => \PHPExcel_STYLE_ALIGNMENT::HORIZONTAL_RIGHT,
- ],
- 'fill' =>[
- 'type' => \PHPExcel_STYLE_FILL::FILL_SOLID,
- 'color'=>[
- 'rgb' => 'fdfef1'
- ]
- ],
- 'borders' =>[
- 'right' =>[
- 'style'=>\PHPExcel_Style_Border::BORDER_NONE
- ]
- ]
- ];
- $style_date =[
- 'fill' =>[
- 'type' => \PHPExcel_STYLE_FILL::FILL_SOLID,
- 'color'=>[
- 'rgb' => 'fdfef1'
- ]
- ],
- 'borders' =>[
- 'left' =>[
- 'style'=>\PHPExcel_Style_Border::BORDER_NONE
- ]
- ],
- ];
- $style_hprice =[
- 'alignment' =>[
- 'horizontal' => \PHPExcel_STYLE_ALIGNMENT::HORIZONTAL_CENTER,
- ],
- 'fill' =>[
- 'type' => \PHPExcel_STYLE_FILL::FILL_SOLID,
- 'color'=>[
- 'rgb' => 'a50505'
- ]
- ],
- 'font'=>[
- 'bold' => true,
- 'italic' => true,
- 'name' => 'Times New Roman',
- 'size' => 12,
- 'color'=>[
- 'rgb' => 'FFFFFF'
- ]
- ],
- ];
- $style_typ = [
- 'alignment' =>[
- 'horizontal' => \PHPExcel_STYLE_ALIGNMENT::HORIZONTAL_CENTER,
- 'vertical' => \PHPExcel_STYLE_ALIGNMENT::VERTICAL_CENTER
- ],
- 'fill' =>[
- 'type' => \PHPExcel_STYLE_FILL::FILL_SOLID,
- 'color'=>[
- 'rgb' => 'a50505'
- ]
- ],
- 'font'=>[
- 'bold' => true,
- 'italic' => true,
- 'name' => 'Times New Roman',
- 'size' => 12,
- 'color'=>[
- 'rgb' => 'FFFFFF'
- ]
- ],
- ];
- $style_naz = [
- 'alignment' =>[
- 'horizontal' => \PHPExcel_STYLE_ALIGNMENT::HORIZONTAL_CENTER,
- 'vertical' => \PHPExcel_STYLE_ALIGNMENT::VERTICAL_CENTER
- ],
- 'fill' =>[
- 'type' => \PHPExcel_STYLE_FILL::FILL_SOLID,
- 'color'=>[
- 'rgb' => '085906'
- ]
- ],
- 'font'=>[
- 'bold' => true,
- 'italic' => true,
- 'name' => 'Times New Roman',
- 'size' => 12,
- 'color'=>[
- 'rgb' => 'FFFFFF'
- ]
- ],
- ];
- $active_sheet->getStyle('A6:H6')->applyFromArray($style_slogan);
- $active_sheet->getStyle('A7:G7')->applyFromArray($style_tdate);
- $active_sheet->getStyle('E7')->applyFromArray($style_date);
- $active_sheet->getStyle('A9:H9')->applyFromArray($style_hprice);
- /******************************************************************/
- $active_sheet->mergeCells('A8'.':E8');
- #$active_sheet->setCellValue('A8',ucwords($res['nazv']));
- $active_sheet->getStyle('A8'.':H8')->applyFromArray($style_typ);
- $china->getActiveSheet()->freezePane('A10');
- /******************************************************************/
- $china->getActiveSheet()->freezePane('A10');
- $row_start = 10;
- $i = 0;
- $schet=0;
- $r=$this->get_zakaz('1');
- foreach ($r as $res){
- $row_next = $row_start + $i;
- $active_sheet->getRowDimension($row_next)->setRowHeight(150);
- if ($res['foto'] != '') {
- $imagePath = $_SERVER["DOCUMENT_ROOT"] . "/" . $res['foto'];
- }else{
- $imagePath = $_SERVER["DOCUMENT_ROOT"] . "/images/no-image.png";
- }
- if (file_exists($imagePath)) {
- $logo = new \PHPExcel_Worksheet_Drawing();
- $logo->setPath($imagePath);
- $logo->setWidthAndHeight(190, 190);
- $logo->setResizeProportional(true);
- $logo->setCoordinates("A" . $row_next);
- $logo->setOffsetX(3);
- $logo->setOffsetY(3);
- $logo->setWorksheet($active_sheet);
- }
- if (($res['foto1'] != 'images/no-image.png') && ($res['foto1'] != '')) {
- $imagePath = $_SERVER["DOCUMENT_ROOT"] . "/" . $res['foto1'];
- if (file_exists($imagePath)) {
- $logo = new \PHPExcel_Worksheet_Drawing();
- $logo->setPath($imagePath);
- $logo->setWidthAndHeight(190, 190);
- $logo->setResizeProportional(true);
- $logo->setCoordinates("C" . $row_next);
- $logo->setOffsetX(3);
- $logo->setOffsetY(3);
- $logo->setWorksheet($active_sheet);
- }
- }
- if (($res['foto2'] != 'images/no-image.png') && ($res['foto2'] != '')) {
- $imagePath = $_SERVER["DOCUMENT_ROOT"] . "/" . $res['foto2'];
- if (file_exists($imagePath)) {
- $logo = new \PHPExcel_Worksheet_Drawing();
- $logo->setPath($imagePath);
- $logo->setWidthAndHeight(190, 190);
- $logo->setResizeProportional(true);
- $logo->setCoordinates("D" . $row_next);
- $logo->setOffsetX(3);
- $logo->setOffsetY(3);
- $logo->setWorksheet($active_sheet);
- }
- }
- if (($res['foto3'] != 'images/no-image.png') && ($res['foto3'] != '')) {
- $imagePath = $_SERVER["DOCUMENT_ROOT"] . "/" . $res['foto3'];
- if (file_exists($imagePath)) {
- $logo = new \PHPExcel_Worksheet_Drawing();
- $logo->setPath($imagePath);
- $logo->setWidthAndHeight(190, 190);
- $logo->setResizeProportional(true);
- $logo->setCoordinates("E" . $row_next);
- $logo->setOffsetX(3);
- $logo->setOffsetY(3);
- $logo->setWorksheet($active_sheet);
- }
- }
- if (($res['foto4'] != 'images/no-image.png') && ($res['foto4'] != '')) {
- $imagePath = $_SERVER["DOCUMENT_ROOT"] . "/" . $res['foto4'];
- if (file_exists($imagePath)) {
- $logo = new \PHPExcel_Worksheet_Drawing();
- $logo->setPath($imagePath);
- $logo->setWidthAndHeight(190, 190);
- $logo->setResizeProportional(true);
- $logo->setCoordinates("F" . $row_next);
- $logo->setOffsetX(3);
- $logo->setOffsetY(3);
- $logo->setWorksheet($active_sheet);
- }
- }
- $active_sheet->setCellValue('B'.$row_next,strtoupper($res['art']));
- $active_sheet->setCellValue('G'.$row_next,strtoupper($res['kolvo']));
- $active_sheet->setCellValue('H'.$row_next,strtoupper($res['suma']));
- $i++;
- }
- $active_sheet->getStyle('A1:H'.($i+10))->applyFromArray($style_wrap);
- $active_sheet->getStyle('A1:H'.($i+10))->getAlignment()->setWrapText(true);
- $active_sheet->getStyle('A1:H'.($i+10))->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
- $active_sheet->getStyle('H'.($row_start).':H'.($i+9))->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE);
- $active_sheet->getStyle('E'.($row_start+1).':H'.($i+9))->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
- $active_sheet->getStyle('E'.($row_start+1).':H'.($i+9))->getAlignment()->setVertical(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
- $active_sheet->getStyle('B'.($row_start+1).':B'.($i+9))->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
- $active_sheet->getStyle('B'.($row_start+1).':B'.($i+9))->getAlignment()->setVertical(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
- $active_sheet->getStyle('A1:H5')->applyFromArray($style_header);
- $active_sheet->getStyle('E1:H5')->applyFromArray($style_right);
- $active_sheet->mergeCells('A'.($i+10).':H'.($i+10));
- $active_sheet->getStyle('A'.($i+10).':H'.($i+10))->applyFromArray($style_hprice);
- $string="Итого: ".$this->num_decline(($i), "наименование, наименования, наименований");
- $active_sheet->setCellValue('A'.($i+10),$string);
- header("Content-Type:application/vnd.ms-excel");
- header("Content-Disposition:attachment;filename='" . AG_NAME . "-" . date('d-m-Y') . ".xls'");
- $objWriter1 = \PHPExcel_IOFactory::createWriter($china, 'Excel5');
- $objWriter1->save('php://output');
- }
- /************************Выбор дат поставок*******************************/
- public function get_date(){
- $r=$this->dbs->prepare('SELECT data FROM prixod GROUP BY data ORDER BY data DESC;');
- $r->execute();
- if ($r->rowCount()!=''){
- return $r;
- }else $GLOBALS['error']='Поставки отсутствуют';
- }
- /***********************Прайс лист поставок*******************************/
- /***********************Тестовый прайс лист*******************************/
- public function push_price($params=false, $type, $data=false){
- $objPHPExcel = new \PHPEXcel();
- $objPHPExcel->setActiveSheetIndex(0);
- $active_sheet = $objPHPExcel->getActiveSheet();
- /***************************Стили**************************************************/
- $style_wrap = [
- 'borders'=>[
- 'outline' =>[
- 'style'=>\PHPExcel_Style_Border::BORDER_THICK
- ],
- 'allborders'=>[
- 'style'=>\PHPExcel_Style_Border::BORDER_THIN,
- 'color' =>[
- 'rgb'=>'000000'
- ]
- ]
- ]
- ];
- $style_header =[
- 'font'=>[
- 'bold' => true,
- 'name' => 'Times New Roman',
- 'size' => 16,
- 'color' =>[
- 'rgb' => 'FFFFFF'
- ]
- ],
- 'alignment' =>[
- 'horizontal' => \PHPExcel_STYLE_ALIGNMENT::HORIZONTAL_LEFT,
- 'vertical' => \PHPExcel_STYLE_ALIGNMENT::VERTICAL_CENTER,
- ],
- 'fill' =>[
- 'type' => \PHPExcel_STYLE_FILL::FILL_SOLID,
- 'color'=>[
- 'rgb' => '000000'
- ]
- ]
- ];
- $style_right =[
- 'font'=>[
- 'bold' => true,
- 'name' => 'Times New Roman',
- 'size' => 8
- ],
- 'alignment' =>[
- 'horizontal' => \PHPExcel_STYLE_ALIGNMENT::HORIZONTAL_RIGHT,
- 'vertical' => \PHPExcel_STYLE_ALIGNMENT::VERTICAL_CENTER,
- ]
- ];
- $style_slogan =[
- 'font'=>[
- 'bold' => true,
- 'italic' => true,
- 'name' => 'Times New Roman',
- 'size' => 16,
- 'color'=>[
- 'rgb' => '111111'
- ]
- ],
- 'alignment' =>[
- 'horizontal' => \PHPExcel_STYLE_ALIGNMENT::HORIZONTAL_CENTER,
- 'vertical' => \PHPExcel_STYLE_ALIGNMENT::VERTICAL_CENTER,
- ],
- 'fill' =>[
- 'type' => \PHPExcel_STYLE_FILL::FILL_SOLID,
- 'color'=>[
- 'rgb' => 'fdfef1'
- ]
- ],
- 'borders' =>[
- 'bottom' =>[
- 'style'=>\PHPExcel_Style_Border::BORDER_THICK
- ]
- ]
- ];
- $style_tdate =[
- 'alignment' =>[
- 'horizontal' => \PHPExcel_STYLE_ALIGNMENT::HORIZONTAL_RIGHT,
- ],
- 'fill' =>[
- 'type' => \PHPExcel_STYLE_FILL::FILL_SOLID,
- 'color'=>[
- 'rgb' => 'fdfef1'
- ]
- ],
- 'borders' =>[
- 'right' =>[
- 'style'=>\PHPExcel_Style_Border::BORDER_NONE
- ]
- ]
- ];
- $style_date =[
- 'fill' =>[
- 'type' => \PHPExcel_STYLE_FILL::FILL_SOLID,
- 'color'=>[
- 'rgb' => 'fdfef1'
- ]
- ],
- 'borders' =>[
- 'left' =>[
- 'style'=>\PHPExcel_Style_Border::BORDER_NONE
- ]
- ],
- ];
- $style_hprice =[
- 'alignment' =>[
- 'horizontal' => \PHPExcel_STYLE_ALIGNMENT::HORIZONTAL_CENTER,
- ],
- 'fill' =>[
- 'type' => \PHPExcel_STYLE_FILL::FILL_SOLID,
- 'color'=>[
- 'rgb' => 'a50505'
- ]
- ],
- 'font'=>[
- 'bold' => true,
- 'italic' => true,
- 'name' => 'Times New Roman',
- 'size' => 12,
- 'color'=>[
- 'rgb' => 'FFFFFF'
- ]
- ],
- ];
- $style_typ = [
- 'alignment' =>[
- 'horizontal' => \PHPExcel_STYLE_ALIGNMENT::HORIZONTAL_CENTER,
- 'vertical' => \PHPExcel_STYLE_ALIGNMENT::VERTICAL_CENTER
- ],
- 'fill' =>[
- 'type' => \PHPExcel_STYLE_FILL::FILL_SOLID,
- 'color'=>[
- 'rgb' => 'a50505'
- ]
- ],
- 'font'=>[
- 'bold' => true,
- 'italic' => true,
- 'name' => 'Times New Roman',
- 'size' => 12,
- 'color'=>[
- 'rgb' => 'FFFFFF'
- ]
- ],
- ];
- $style_naz = [
- 'alignment' =>[
- 'horizontal' => \PHPExcel_STYLE_ALIGNMENT::HORIZONTAL_CENTER,
- 'vertical' => \PHPExcel_STYLE_ALIGNMENT::VERTICAL_CENTER
- ],
- 'fill' =>[
- 'type' => \PHPExcel_STYLE_FILL::FILL_SOLID,
- 'color'=>[
- 'rgb' => '085906'
- ]
- ],
- 'font'=>[
- 'bold' => true,
- 'italic' => true,
- 'name' => 'Times New Roman',
- 'size' => 12,
- 'color'=>[
- 'rgb' => 'FFFFFF'
- ]
- ],
- ];
- /*********************Получаем категории*******************************************/
- if (!empty($data)) {
- $r = $this->get_typ(false, $data);
- }else $r = $this->get_typ();
- $is=0;
- foreach ($r as $res){
- if ($is!=0){
- /*if (!empty($data)){
- $q=$this->get_typ_kol($res['id'], $data);
- if (count($q)!=0){
- $objPHPExcel->createSheet();
- $objPHPExcel->setActiveSheetIndex($is);
- $active_sheet = $objPHPExcel->getActiveSheet();
- }else $is--;*/
- #}else{
- $objPHPExcel->createSheet();
- $objPHPExcel->setActiveSheetIndex($is);
- $active_sheet = $objPHPExcel->getActiveSheet();
- #}
- }
- /*if (!empty($data)){
- $q=$this->get_typ_kol($res['id'], $data);
- if (count($q)!=0){
- $objPHPExcel->removeSheetByIndex($is);
- }
- }*/
- $active_sheet->getPageSetup()->setOrientation(\PHPExcel_Worksheet_PageSetup::ORIENTATION_PORTRAIT);
- $objPHPExcel->getActiveSheet()->getPageSetup()->setFitToPage(true);
- $objPHPExcel->getActiveSheet()->getPageSetup()->setFitToWidth(1);
- $objPHPExcel->getActiveSheet()->getPageSetup()->setFitToHeight(0);
- $active_sheet->getPageSetup()->SetPaperSize(\PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
- /***********************Поля документа*********************************************/
- $active_sheet->getPageMargins()->setTop(1);
- $active_sheet->getPageMargins()->setRight(0.75);
- $active_sheet->getPageMargins()->setLeft(0.75);
- $active_sheet->getPageMargins()->setBottom(1);
- $is++;
- $str=mb_substr(trim($res['nazv']), 0, 31);
- $active_sheet->setTitle($str);
- $active_sheet->getHeaderFooter()->setOddHeader("&C".AG_NAME.": прайс лист");
- $active_sheet->getHeaderFooter()->setOddFooter('&L&B'.$active_sheet->getTitle().'&RСтраница &P из &N');
- $objPHPExcel->getDefaultStyle()->getFont()->setName('Arial');
- $objPHPExcel->getDefaultStyle()->getFont()->setSize(8);
- $active_sheet->getColumnDimension('A')->setWidth(35);
- $active_sheet->getColumnDimension('B')->setWidth(25);
- $active_sheet->getColumnDimension('C')->setWidth(40);
- $active_sheet->getColumnDimension('D')->setWidth(35);
- $active_sheet->getColumnDimension('E')->setWidth(35);
- if (!empty($data)){
- $active_sheet->getColumnDimension('F')->setWidth(20);
- $active_sheet->getColumnDimension('G')->setWidth(18);
- $active_sheet->getColumnDimension('H')->setWidth(18);
- }
- $active_sheet->setCellValue('E1','"'.AG_NAME.'"');
- $active_sheet->setCellValue('E2','Т. '.AG_PHONE1.', '.AG_PHONE2);
- $active_sheet->setCellValue('E3',AG_MAIL_SERVER);
- $objPHPExcel->getActiveSheet()->getCell('E3')->getHyperlink()->setUrl('mailto:'.AG_MAIL_SERVER);
- $active_sheet->setCellValue('E4',AG_MAIL_ADMIN);
- $objPHPExcel->getActiveSheet()->getCell('E4')->getHyperlink()->setUrl('mailto:'.AG_MAIL_ADMIN);
- $active_sheet->setCellValue('E5',AG_SERVER);
- $objPHPExcel->getActiveSheet()->getCell('E5')->getHyperlink()->setUrl('http://'.AG_SERVER);
- $active_sheet->getRowDimension('1')->setRowHeight(20);
- $active_sheet->getRowDimension('2')->setRowHeight(20);
- $active_sheet->getRowDimension('3')->setRowHeight(20);
- $active_sheet->getRowDimension('4')->setRowHeight(20);
- $active_sheet->getRowDimension('5')->setRowHeight(20);
- $active_sheet->mergeCells('A1:B5');
- $imagePath = $_SERVER["DOCUMENT_ROOT"]."/img/img_xls.png";
- if (file_exists($imagePath)) {
- $logo = new \PHPExcel_Worksheet_Drawing();
- $logo->setPath($imagePath);
- $logo->setCoordinates("A1");
- $logo->setOffsetX(15);
- $logo->setOffsetY(15);
- $logo->setWorksheet($active_sheet);
- }
- $active_sheet->mergeCells('C1:C5');
- $active_sheet->setCellValue('C1','Прайс-лист');
- $active_sheet->mergeCells('D1:D5');
- $imagePath = $_SERVER["DOCUMENT_ROOT"]."/img/gerb.png";
- if (file_exists($imagePath)) {
- $logo = new \PHPExcel_Worksheet_Drawing();
- $logo->setPath($imagePath);
- $logo->setWidthAndHeight(230,230);
- $logo->setResizeProportional(true);
- $logo->setCoordinates("D1");
- $logo->setOffsetX(-25);
- $logo->setOffsetY(0);
- $logo->setWorksheet($active_sheet);
- }
- if (!empty($data)){
- $active_sheet->mergeCells('A6:H6');
- }else
- $active_sheet->mergeCells('A6:E6');
- $active_sheet->setCellValue('A6','Португальский производитель сантехники');
- $active_sheet->mergeCells('A7:D7');
- $active_sheet->setCellValue('A7','Дата создания прайслиста');
- $date = date('d-m-Y');
- $active_sheet->setCellValue('E7',$date);
- $active_sheet->getStyle('E7')
- ->getNumberFormat()->
- setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX14);
- $active_sheet->setCellValue('A9','Изображение');
- $active_sheet->setCellValue('B9','Артикул');
- $active_sheet->setCellValue('C9','Описание');
- $active_sheet->setCellValue('D9','Схема');
- $active_sheet->setCellValue('E9','Цена, EUR('.$params.')');
- if (!empty($data)) {
- $active_sheet->setCellValue('F9', 'Цена Китая');
- $active_sheet->setCellValue('G9', 'Количество');
- $active_sheet->setCellValue('H9', 'Дата');
- }
- /*************************Применяем стили**************************/
- if (!empty($data)){
- $active_sheet->getStyle('A6:H6')->applyFromArray($style_slogan);
- }else
- $active_sheet->getStyle('A6:E6')->applyFromArray($style_slogan);
- $active_sheet->getStyle('A7:D7')->applyFromArray($style_tdate);
- $active_sheet->getStyle('E7')->applyFromArray($style_date);
- if (!empty($data)){
- $active_sheet->getStyle('A9:H9')->applyFromArray($style_hprice);
- }else
- $active_sheet->getStyle('A9:E9')->applyFromArray($style_hprice);
- /******************************************************************/
- if (!empty($data)){
- $active_sheet->mergeCells('A8'.':H8');
- }else
- $active_sheet->mergeCells('A8'.':E8');
- $active_sheet->setCellValue('A8',ucwords($res['nazv']));
- if (!empty($data)){
- $active_sheet->getStyle('A8'.':H8')->applyFromArray($style_typ);
- }else
- $active_sheet->getStyle('A8'.':E8')->applyFromArray($style_typ);
- $objPHPExcel->getActiveSheet()->freezePane('A10');
- /*****************************Получаем заголовки****************************************/
- $row_start = 10;
- $i = 0;
- $schet=0;
- if (!empty($data)){
- $q=$this->get_typ_kol($res['id'], $data);
- }else
- $q=$this->get_typ_kol($res['id']);
- if (count($q)!=0) {
- foreach ($q as $qes) {
- if ($qes['schet'] != 0) {
- $row_next = $row_start + $i;
- if (!empty($data)) {
- $active_sheet->mergeCells('A' . $row_next . ':H' . $row_next);
- } else
- $active_sheet->mergeCells('A' . $row_next . ':E' . $row_next);
- $active_sheet->getRowDimension($row_next)->setRowHeight(30);
- if ($qes['nazv_kol'] == 'Без коллекции')
- $active_sheet->setCellValue('A' . $row_next, ucwords($qes['nazv_tov']));
- else
- $active_sheet->setCellValue('A' . $row_next, ucwords($qes['nazv_kol']) . " " . ucwords($qes['nazv_tov']));
- if (!empty($data)) {
- $active_sheet->getStyle('A' . $row_next . ':H' . $row_next)->applyFromArray($style_naz);
- } else
- $active_sheet->getStyle('A' . $row_next . ':E' . $row_next)->applyFromArray($style_naz);
- $i++;
- /************************Вывод товара**************************************/
- if (!empty($data)) {
- $d = $this->get_price_tov($qes['id_kol'], $qes['id_typ'], $qes['nazv_tov'], $data);
- } else
- $d = $this->get_price_tov($qes['id_kol'], $qes['id_typ'], $qes['nazv_tov']);
- foreach ($d as $des) {
- $opisanie = '';
- $row_next = $row_start + $i;
- $active_sheet->getRowDimension($row_next)->setRowHeight(150);
- if ($type == 0) {
- if ($des['foto'] != 'images/no-image.png') {
- $imagePath = $_SERVER["DOCUMENT_ROOT"] . "/" . $des['foto'];
- if (file_exists($imagePath)) {
- $logo = new \PHPExcel_Worksheet_Drawing();
- $logo->setPath($imagePath);
- $logo->setWidthAndHeight(190, 190);
- $logo->setResizeProportional(true);
- $logo->setCoordinates("A" . $row_next);
- $logo->setOffsetX(3);
- $logo->setOffsetY(3);
- $logo->setWorksheet($active_sheet);
- }
- }
- }
- /*******************************Описание************************************/
- foreach ($des as $key => $value) {
- if ($key != 'art' && $key != 'foto' && $key != 'foto1' && $key != 'cena') {
- $opisanie .= ucwords($key) . ucwords($value) . "\n";
- }
- }
- /*******************************Описание, артикул и цена************************************/
- $active_sheet->setCellValue('C' . $row_next, $opisanie);
- $active_sheet->setCellValue('B' . $row_next, strtoupper($des['art']));
- $active_sheet->setCellValue('E' . $row_next, $des['cena']);
- if (!empty($data)) {
- $active_sheet->setCellValue('F' . $row_next, $des['cena_china']);
- $active_sheet->setCellValue('G' . $row_next, $des['kolvo']);
- $active_sheet->setCellValue('H' . $row_next, $des['data']);
- }
- /***************************************************************************/
- if ($type == 0) {
- if ($des['foto1'] != 'images/no-image.png') {
- $imagePath = $_SERVER["DOCUMENT_ROOT"] . "/" . $des['foto1'];
- if (file_exists($imagePath)) {
- $logo = new \PHPExcel_Worksheet_Drawing();
- $logo->setPath($imagePath);
- $logo->setWidthAndHeight(190, 190);
- $logo->setResizeProportional(true);
- $logo->setCoordinates("D" . $row_next);
- $logo->setOffsetX(3);
- $logo->setOffsetY(3);
- $logo->setWorksheet($active_sheet);
- }
- }
- }
- $i++;
- }
- # Не забыть сделать $i++;
- /**************************************************************************/
- }
- $schet += $qes['schet'];
- }
- if (!empty($data)) {
- $active_sheet->getStyle('A1:H' . ($i + 10))->applyFromArray($style_wrap);
- $active_sheet->getStyle('A1:H' . ($i + 10))->getAlignment()->setWrapText(true);
- $active_sheet->getStyle('A1:H' . ($i + 10))->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
- $active_sheet->getStyle('E'.($row_start+1).':F'.($i+9))->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE);
- $active_sheet->getStyle('E'.($row_start+1).':H'.($i+9))->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
- $active_sheet->getStyle('E'.($row_start+1).':H'.($i+9))->getAlignment()->setVertical(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
- } else{
- $active_sheet->getStyle('A1:E' . ($i + 10))->applyFromArray($style_wrap);
- $active_sheet->getStyle('A1:E' . ($i + 10))->getAlignment()->setWrapText(true);
- $active_sheet->getStyle('A1:E' . ($i + 10))->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
- $active_sheet->getStyle('E'.($row_start+1).':E'.($i+9))->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE);
- $active_sheet->getStyle('E'.($row_start+1).':E'.($i+9))->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
- $active_sheet->getStyle('E'.($row_start+1).':E'.($i+9))->getAlignment()->setVertical(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
- }
- $active_sheet->getStyle('B'.($row_start+1).':B'.($i+9))->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
- $active_sheet->getStyle('B'.($row_start+1).':B'.($i+9))->getAlignment()->setVertical(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
- if (!empty($data)){
- $active_sheet->getStyle('A1:H5')->applyFromArray($style_header);
- }else
- $active_sheet->getStyle('A1:E5')->applyFromArray($style_header);
- $active_sheet->getStyle('E1:E5')->applyFromArray($style_right);
- $string="Итого: ".$this->num_decline(($schet), "товар, товара, товаров");
- $active_sheet->setCellValue('A'.($i+10),$string);
- if (!empty($data)){
- $active_sheet->mergeCells('A'.($i+10).':H'.($i+10));
- $active_sheet->getStyle('A'.($i+10).':H'.($i+10))->applyFromArray($style_hprice);
- }else {
- $active_sheet->mergeCells('A' . ($i + 10) . ':E' . ($i + 10));
- $active_sheet->getStyle('A' . ($i + 10) . ':E' . ($i + 10))->applyFromArray($style_hprice);
- }
- }
- /***************************************************************************************/
- /*if ($is==1) {
- if (!empty($data)) {
- $q = $this->get_typ_kol($res['id'], $data);
- if (count($q) != 0) {
- $objPHPExcel->removeSheetByIndex($is-1);
- $is=0;
- }
- }
- }*/
- }
- $objPHPExcel->setActiveSheetIndex(0);
- if ($type==0) {
- header("Content-Type:application/vnd.ms-excel");
- header("Content-Disposition:attachment;filename='" . AG_NAME . "-" . date('d-m-Y') . ".xls'");
- $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
- $objWriter->save('php://output');
- }else {
- $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
- $objWriter->save(AG_NAME . '-' . date('d-m-Y') . '.xls');
- $GLOBALS['send_price_name']=AG_NAME . '-' . date('d-m-Y') . '.xls';
- }
- #exit();
- }
- /*************************************************************************/
- }
- #file_put_contents('filename.json', $str."\r\n", FILE_APPEND);
- //file_put_contents('filename.json', json_encode($p), JSON_UNESCAPED_UNICODE);
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement