Библиотека кода – Page 5 – Заметки веб-разработчика

Yii2 прелоудер/preloader для формы

$('#auth-form').on('ajaxBeforeSend', function (event, jqXHR, settings) {
    $('section.authForm .loader').show();
}).on('ajaxComplete', function (event, jqXHR, textStatus) {
    $('section.authForm .loader').hide();
});

$('#auth-form').on('ajaxBeforeSend', function (event, jqXHR, settings) {

$('section.authForm .loader').show();

}).on('ajaxComplete', function (event, jqXHR, textStatus) {

$('section.authForm .loader').hide();

});

Yii2 валидация для ЧПУ (slug)

['slug', 'match', 'pattern' => '/^[\w-]+$/',
 'message' => 'Поле может содержать только латинские буквы, цифры, и знаки "_", "-"'
],

['slug', 'match', 'pattern' => '/^[\w-]+$/',

'message' => 'Поле может содержать только латинские буквы, цифры, и знаки "_", "-"'

/**
 * {@inheritdoc}
 */
public function rules()
{
    return [
        [['image'], 'required'],
        [['active', 'sort'], 'integer'],
        [['date_start', 'date_end'], 'safe'],
        [['title', 'image'], 'string', 'max' => 64],
        [['slug', 'link'], 'string', 'max' => 255],
        [['slug'], 'unique'],
        [['title', 'link'], 'trim'],
        ['slug', 'match', 'pattern' => '/^[\w-]+$/',
         'message' => 'Поле может содержать только латинские буквы, цифры, и знаки "_", "-"'
        ],
    ];
}

/**

* {@inheritdoc}

public function rules()

{

return [

[['image'], 'required'],

[['active', 'sort'], 'integer'],

[['date_start', 'date_end'], 'safe'],

[['title', 'image'], 'string', 'max' => 64],

[['slug', 'link'], 'string', 'max' => 255],

[['slug'], 'unique'],

[['title', 'link'], 'trim'],

['slug', 'match', 'pattern' => '/^[\w-]+$/',

'message' => 'Поле может содержать только латинские буквы, цифры, и знаки "_", "-"'

];

}

yii2 slug

Удалить определенные директории linux bash

find /path/to \( -name "preview" -o -name "slider" -o -name "popup" -o -name "detail-view" -o -name "cart" \) | xargs rm -rf

1	find /path/to $ -name "preview" -o -name "slider" -o -name "popup" -o -name "detail-view" -o -name "cart" $ \| xargs rm -rf

Yii2 получить класс/имя модели

Способ 1

$model = new Test();
$modelName = \yii\helpers\StringHelper::basename(get_class($model));

var_dump($modelName); // Test

$model = new Test();

$modelName = \yii\helpers\StringHelper::basename(get_class($model));

var_dump($modelName); // Test

Способ 2

$model = new Test;
$modelName = (new \ReflectionClass($model))->getShortName();

var_dump($modelName); // Test

$model = new Test;

$modelName = (new \ReflectionClass($model))->getShortName();

var_dump($modelName); // Test

Для форм можно получить таким образом

Html::getInputName($searchModel, 'popular'); // ShopBrand[popular]
Html::getInputId($searchModel, 'popular');   // shopbrand-popular

1 2	Html::getInputName($searchModel, 'popular'); // ShopBrand[popular] Html::getInputId($searchModel, 'popular'); // shopbrand-popular

Yii2 GridView адаптив

Для адаптивных колонок в yii2 GridView нужно добавить следующий код в виджет

'tableOptions' => [
    'class' => 'table table-striped',
],
'options' => [
    'class' => 'table-responsive',
],

'tableOptions' => [

'class' => 'table table-striped',

'options' => [

'class' => 'table-responsive',

Полный пример

GridView::widget([
    'dataProvider' => $dataProvider,
    'filterModel'  => $searchModel,
    'tableOptions' => [
        'class' => 'table table-striped',
    ],
    'options' => [
        'class' => 'table-responsive',
    ],
    'columns'      => [
        ['class' => 'yii\grid\SerialColumn'],

        'id',
        'name',
        'path',
        [
            'attribute'     => 'popular',
            'filter'        => Select2::widget([
                'name'       => 'ShopBrandSearch[popular]',
                'value'      => $searchModel->popular,
                'hideSearch' => true,
                'theme'      => Select2::THEME_DEFAULT,
                'data'       => [
                    null => 'Все',
                    '1'  => 'Да',
                    '0'  => 'Нет',
                ],
            ]),
            'value'         => function ($model) {
                return $model->popular == true ? 'Да' : 'Нет';
            },
            'headerOptions' => [
                'width' => 130,
            ],
        ],

        [
            'class' => 'yii\grid\ActionColumn',
            'template' => '{view} {update}'
        ],
    ],
]);

GridView::widget([

'dataProvider' => $dataProvider,

'filterModel' => $searchModel,

'tableOptions' => [

'class' => 'table table-striped',

'options' => [

'class' => 'table-responsive',

'columns' => [

['class' => 'yii\grid\SerialColumn'],

'id',

'name',

'path',

[

'attribute' => 'popular',

'filter' => Select2::widget([

'name' => 'ShopBrandSearch[popular]',

'value' => $searchModel->popular,

'hideSearch' => true,

'theme' => Select2::THEME_DEFAULT,

'data' => [

null => 'Все',

'1' => 'Да',

'0' => 'Нет',

]),

'value' => function ($model) {

return $model->popular == true ? 'Да' : 'Нет';

'headerOptions' => [

'width' => 130,

[

'class' => 'yii\grid\ActionColumn',

'template' => '{view} {update}'

]);

Большой страшный sql запрос Интернет магазина для Яндекс маркета

SELECT 
    product.goods_code as 'id',
    'В наличии' as 'Статус товара',
    'Есть' as 'Доставка',
    ' ' as 'Стоимость доставки',
    ' ' as 'Срок доставки',
    'Есть' as 'Самовывоз',
    'Можно' as 'Купить в магазине без заказа',
    
    CASE 
        WHEN price.price = (
            select price 
            FROM shop_goods_base 
            where goods_id = product.id 
            AND base_id NOT IN (select id from `base` where hide_contacts = 1)
            ORDER BY price 
            DESC LIMIT 1
        ) 
            THEN CONCAT('https://site.ru/', category.path,'/', product.path) 
        ELSE 
            CONCAT('https://', (
                select domain from base where id =          
                    (
                        select base_id 
                        FROM shop_goods_base 
                        where goods_id = product.id 
                        AND base_id NOT IN (select id from `base` where hide_contacts = 1)
                        ORDER BY price 
                        DESC LIMIT 1
                    )
                ),
                '.site.ru/', 
                category.path, '/', product.path
            ) 
    END AS 'Ссылка на товар на сайте магазина',
    
    brand.name as 'Производитель',
    product.name as 'Название',  
    category.name as 'Категория',
    
    (
        select price 
        FROM shop_goods_base 
        where goods_id = product.id 
        AND base_id NOT IN (select id from `base` where hide_contacts = 1)
        ORDER BY price 
        DESC LIMIT 1
    ) as 'Цена',
    
    '' as 'Цена без скидки',
    'RUR' as 'Валюта',
    
    CONCAT('https://site.ru/u/shop/', SUBSTRING(product.goods_code, 1, 3), '/orig/', image.file) as 'Ссылка на картинку',
    product.description as 'Описание',
    
        CONCAT_WS(
        "",
        (
            select  GROUP_CONCAT('Цвет|', sc.header, ';')
            from shop_filter_value as sfv
            LEFT JOIN shop_color as sc
            ON sfv.int_value = sc.id
            where sfv.filter_set_id = 7 and sfv.goods_id = product.id
            limit 1
        ),
        (
            select  GROUP_CONCAT('Длина|', sfv.float_value, ' ', shc.units, ';')
            from shop_filter_value as sfv
            LEFT JOIN shop_filter_set as shc
            ON sfv.filter_set_id = shc.id
            where sfv.filter_set_id = 9 and sfv.goods_id = product.id
            limit 1
        ),
        (
            select  GROUP_CONCAT('Ширина|', sfv.float_value, ' ', shc.units, ';')
            from shop_filter_value as sfv
            LEFT JOIN shop_filter_set as shc
            ON sfv.filter_set_id = shc.id
            where sfv.filter_set_id = 35 and sfv.goods_id = product.id
            limit 1
        ),
        (
            select  GROUP_CONCAT('Высота|', sfv.float_value, ' ', shc.units, ';')
            from shop_filter_value as sfv
            LEFT JOIN shop_filter_set as shc
            ON sfv.filter_set_id = shc.id
            where sfv.filter_set_id = 32 and sfv.goods_id = product.id
            limit 1
        ),
        (
            select  GROUP_CONCAT('Пустотность|', sfs.value, ';')
            from shop_filter_value as sfv
            LEFT JOIN shop_filter_select as sfs
            ON sfv.int_value = sfs.id
            where sfv.filter_set_id = 196 and sfv.goods_id = product.id
            limit 1
        ),
        (
            select  GROUP_CONCAT('Тип|', sfs.value, ';')
            from shop_filter_value as sfv
            LEFT JOIN shop_filter_select as sfs
            ON sfv.int_value = sfs.id
            where sfv.filter_set_id = 43 and sfv.goods_id = product.id
            limit 1
        ),
        
        (
            select  GROUP_CONCAT('Материал|', sfs.value, ';')
            from shop_filter_value as sfv
            LEFT JOIN shop_filter_select as sfs
            ON sfv.int_value = sfs.id
            where sfv.filter_set_id = 18 and sfv.goods_id = product.id
            limit 1
        ),
        (
            select  GROUP_CONCAT('Диаметр|', sfv.float_value, ' ', shc.units, ';')
            from shop_filter_value as sfv
            LEFT JOIN shop_filter_set as shc
            ON sfv.filter_set_id = shc.id
            where sfv.filter_set_id = 8 and sfv.goods_id = product.id
            limit 1
        )
        
    ) as 'Характеристики товара',
    
    'Необходима предоплата.' as 'Условия продажи',
    'Есть' as 'Гарантия производителя',
    country.xart_header as 'Страна происхождения',
    product.goods_code as 'Штрихкод',
    '80' as 'bid'

FROM shop_goods as product

LEFT JOIN shop_goods_base as price
ON product.id = price.goods_id

LEFT JOIN shop_catalog_goods as cat_table
ON product.id = cat_table.goods_id

LEFT JOIN shop_catalog as category
ON cat_table.catalog_id = category.id

LEFT JOIN shop_brand as brand
ON product.brand_id = brand.id

LEFT JOIN shop_country as country
ON product.country_id = country.id

LEFT JOIN shop_image as image
ON product.id = image.goods_id

WHERE product.goods_code IN (1,2,3,4,5) 
GROUP BY product.goods_code

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

SELECT

product.goods_code as 'id',

'В наличии' as 'Статус товара',

'Есть' as 'Доставка',

' ' as 'Стоимость доставки',

' ' as 'Срок доставки',

'Есть' as 'Самовывоз',

'Можно' as 'Купить в магазине без заказа',

CASE

WHEN price.price = (

select price

FROM shop_goods_base

where goods_id = product.id

AND base_id NOT IN (select id from `base` where hide_contacts = 1)

ORDER BY price

DESC LIMIT 1

)

THEN CONCAT('https://site.ru/', category.path,'/', product.path)

ELSE

CONCAT('https://', (

select domain from base where id =

(

select base_id

FROM shop_goods_base

where goods_id = product.id

AND base_id NOT IN (select id from `base` where hide_contacts = 1)

ORDER BY price

DESC LIMIT 1

)

'.site.ru/',

category.path, '/', product.path

)

END AS 'Ссылка на товар на сайте магазина',

brand.name as 'Производитель',

product.name as 'Название',

category.name as 'Категория',

(

select price

FROM shop_goods_base

where goods_id = product.id

AND base_id NOT IN (select id from `base` where hide_contacts = 1)

ORDER BY price

DESC LIMIT 1

) as 'Цена',

'' as 'Цена без скидки',

'RUR' as 'Валюта',

CONCAT('https://site.ru/u/shop/', SUBSTRING(product.goods_code, 1, 3), '/orig/', image.file) as 'Ссылка на картинку',

product.description as 'Описание',

CONCAT_WS(

"",

(

select GROUP_CONCAT('Цвет|', sc.header, ';')

from shop_filter_value as sfv

LEFT JOIN shop_color as sc

ON sfv.int_value = sc.id

where sfv.filter_set_id = 7 and sfv.goods_id = product.id

limit 1

(

select GROUP_CONCAT('Длина|', sfv.float_value, ' ', shc.units, ';')

from shop_filter_value as sfv

LEFT JOIN shop_filter_set as shc

ON sfv.filter_set_id = shc.id

where sfv.filter_set_id = 9 and sfv.goods_id = product.id

limit 1

(

select GROUP_CONCAT('Ширина|', sfv.float_value, ' ', shc.units, ';')

from shop_filter_value as sfv

LEFT JOIN shop_filter_set as shc

ON sfv.filter_set_id = shc.id

where sfv.filter_set_id = 35 and sfv.goods_id = product.id

limit 1

(

select GROUP_CONCAT('Высота|', sfv.float_value, ' ', shc.units, ';')

from shop_filter_value as sfv

LEFT JOIN shop_filter_set as shc

ON sfv.filter_set_id = shc.id

where sfv.filter_set_id = 32 and sfv.goods_id = product.id

limit 1

(

select GROUP_CONCAT('Пустотность|', sfs.value, ';')

from shop_filter_value as sfv

LEFT JOIN shop_filter_select as sfs

ON sfv.int_value = sfs.id

where sfv.filter_set_id = 196 and sfv.goods_id = product.id

limit 1

(

select GROUP_CONCAT('Тип|', sfs.value, ';')

from shop_filter_value as sfv

LEFT JOIN shop_filter_select as sfs

ON sfv.int_value = sfs.id

where sfv.filter_set_id = 43 and sfv.goods_id = product.id

limit 1

(

select GROUP_CONCAT('Материал|', sfs.value, ';')

from shop_filter_value as sfv

LEFT JOIN shop_filter_select as sfs

ON sfv.int_value = sfs.id

where sfv.filter_set_id = 18 and sfv.goods_id = product.id

limit 1

(

select GROUP_CONCAT('Диаметр|', sfv.float_value, ' ', shc.units, ';')

from shop_filter_value as sfv

LEFT JOIN shop_filter_set as shc

ON sfv.filter_set_id = shc.id

where sfv.filter_set_id = 8 and sfv.goods_id = product.id

limit 1

)

) as 'Характеристики товара',

'Необходима предоплата.' as 'Условия продажи',

'Есть' as 'Гарантия производителя',

country.xart_header as 'Страна происхождения',

product.goods_code as 'Штрихкод',

'80' as 'bid'

FROM shop_goods as product

LEFT JOIN shop_goods_base as price

ON product.id = price.goods_id

LEFT JOIN shop_catalog_goods as cat_table

ON product.id = cat_table.goods_id

LEFT JOIN shop_catalog as category

ON cat_table.catalog_id = category.id

LEFT JOIN shop_brand as brand

ON product.brand_id = brand.id

LEFT JOIN shop_country as country

ON product.country_id = country.id

LEFT JOIN shop_image as image

ON product.id = image.goods_id

WHERE product.goods_code IN (1,2,3,4,5)

GROUP BY product.goods_code

Вывести одной строкой несколько значений из таблицы

Есть запрос

select * from shop_product_props
where prop_id in (559, 799)

1 2	select * from shop_product_props where prop_id in (559, 799)

А нужно сгруппировать результат по товару, и получить его значения Запрос должен быть следующим (Запрос занял 0.0118 сек.)

select product_id, 
MAX(if(prop_id=559,value,NULL)) as brand, 
MAX(if(prop_id=799,value,NULL)) as mark 
from shop_product_props
where prop_id in (559, 799) GROUP BY 1

select product_id,

MAX(if(prop_id=559,value,NULL)) as brand,

MAX(if(prop_id=799,value,NULL)) as mark

from shop_product_props

where prop_id in (559, 799) GROUP BY 1

Еще вариант (Запрос занял 0.2479 сек.)

select
    spp.product_id,
    spp1.value as brand,
    spp2.value as mark
from (
    select distinct product_id
    from shop_product_props
    where prop_id in (559, 799)
) as spp
    left join shop_product_props as spp1 on spp.product_id = spp1.product_id and spp1.prop_id = 559
    left join shop_product_props as spp2 on spp.product_id = spp2.product_id and spp2.prop_id = 799

select

spp.product_id,

spp1.value as brand,

spp2.value as mark

from (

select distinct product_id

from shop_product_props

where prop_id in (559, 799)

) as spp

left join shop_product_props as spp1 on spp.product_id = spp1.product_id and spp1.prop_id = 559

left join shop_product_props as spp2 on spp.product_id = spp2.product_id and spp2.prop_id = 799

Datetime to Date and interval

Преобразовать дату с временем (datetime) в дату

date(created_at)

1	date(created_at)

Модифицировать текущую дату на ± дней

date(DATE_ADD(NOW(), INTERVAL -5 DAY)) -- Минус 5 дней
date(DATE_ADD(NOW(), INTERVAL 5 DAY))  -- Плюс 5 дней

1 2	date(DATE_ADD(NOW(), INTERVAL -5 DAY)) -- Минус 5 дней date(DATE_ADD(NOW(), INTERVAL 5 DAY)) -- Плюс 5 дней

Пример #1

SELECT * FROM `shop_cart` 
where ordered = 0 
AND modify_date < date(DATE_ADD(NOW(), INTERVAL -14 DAY)) 
ORDER BY `shop_cart`.`modify_date` DESC

SELECT * FROM `shop_cart`

where ordered = 0

AND modify_date < date(DATE_ADD(NOW(), INTERVAL -14 DAY))

ORDER BY `shop_cart`.`modify_date` DESC

Выбрать заказы за сегодня

SELECT count(*) as 'кол-во заказов', SUM(order_total) as 'Общая сумма' FROM shop_order where date(reg_date) = date(NOW())

1	SELECT count(*) as 'кол-во заказов', SUM(order_total) as 'Общая сумма' FROM shop_order where date(reg_date) = date(NOW())

Выбрать заказы за вчера

SELECT * FROM shop_order where date(reg_date) = date(DATE_ADD(NOW(), INTERVAL -1 DAY))

1	SELECT * FROM shop_order where date(reg_date) = date(DATE_ADD(NOW(), INTERVAL -1 DAY))

Отобразить последние измененные файлы

find . -type f -printf '%TY-%Tm-%Td %TT %p\n' | sort -r | less

1	find . -type f -printf '%TY-%Tm-%Td %TT %p\n' \| sort -r \| less

last modify последние изменения

CentOs alias

Для создания alias глобально в CentOs нужно перейти в директорию

cd /etc/profile.d/

1	cd /etc/profile.d/

Создать файл

00-aliases.sh

1	00-aliases.sh

Полная команда:

vim /etc/profile.d/00-aliases.sh

1	vim /etc/profile.d/00-aliases.sh

и написать alias’ы

alias php71='/opt/php71/bin/php'

1	alias php71='/opt/php71/bin/php'

Будет работать глобально