public function actionLpodr()
{
$entity = new LetualProduct();
$offset = 0;
do {
$products = $entity->getEntity($offset, 20);
if (!empty($products)) {
foreach ($products as $product) {
if (empty($product->letu_id)) {
$query = new Query();
$str = '';
$rows = $query->from('pproduct')->match(new Expression(':match', ['match' => '@(description) ' . \Yii::$app->sphinx->escapeMatchValue($product->title)]))->all();
foreach ($rows as $row) {
if (!empty($row['id'])) {
// проставляем идентификатор
$str = $row['id'];
break;
}
}
// обновляем таблицу podruzka_product
if (!empty($str)) {
$productEntity = PodruzkaProduct::findOne(['id' => $str]);
if ($productEntity instanceof PodruzkaProduct) {
if (empty($productEntity->letu_id)) {
$productEntity->letu_id = (string) $product->id;
$productEntity->save();
}
}
}
}
}
$z = 1;
$offset += 20;
unset($productEntity);
unset($query);
} else {
$z = 0;
}
} while ($z > 0);
return 0;
}
public function uploadInformProduct()
{
// проверяем существует есть ли файл
$filename = \Yii::$app->basePath . '/web/files/upload.xlsx';
$chunkSize = 500;
//размер считываемых строк за раз
//начинаем читать со строки 2, в PHPExcel первая строка имеет индекс 1, и как правило это строка заголовков
$startRow = 2;
$exit = false;
//флаг выхода
$empty_value = 0;
//счетчик пустых знаений
$objReader = \PHPExcel_IOFactory::createReaderForFile($filename);
$objReader->setReadDataOnly(true);
$chunkFilter = new chunkReadFilter();
$objReader->setReadFilter($chunkFilter);
/** Tell the Reader that we want to use the Read Filter that we've Instantiated **/
$objReader->setReadFilter($chunkFilter);
//внешний цикл, пока файл не кончится
while (!$exit) {
$chunkFilter->setRows($startRow, $chunkSize);
//устанавливаем знаечние фильтра
$objPHPExcel = $objReader->load($filename);
//открываем файл
$objPHPExcel->setActiveSheetIndex(0);
//устанавливаем индекс активной страницы
$objWorksheet = $objPHPExcel->getActiveSheet();
//делаем активной нужную страницу
//внутренний цикл по строкам
for ($i = $startRow; $i < $startRow + $chunkSize; $i++) {
//получаем первое знаение в строке
$value = $objWorksheet->getCellByColumnAndRow(0, $i)->getValue();
if (!empty($value)) {
if (!PodruzkaProduct::findOne(['article' => $value])) {
$product = new PodruzkaProduct();
} else {
$product = PodruzkaProduct::findOne(['article' => $value]);
}
$product->article = trim(htmlspecialchars($objWorksheet->getCellByColumnAndRow(0, $i)->getValue()));
$product->title = (string) $objWorksheet->getCellByColumnAndRow(1, $i)->getValue();
$product->arrival = (string) $objWorksheet->getCellByColumnAndRow(2, $i)->getValue();
$product->group = (string) $objWorksheet->getCellByColumnAndRow(3, $i)->getValue();
$product->category = (string) $objWorksheet->getCellByColumnAndRow(4, $i)->getValue();
$product->sub_category = (string) $objWorksheet->getCellByColumnAndRow(5, $i)->getValue();
$product->detail = (string) $objWorksheet->getCellByColumnAndRow(6, $i)->getValue();
$product->brand = (string) $objWorksheet->getCellByColumnAndRow(7, $i)->getValue();
$product->sub_brand = (string) $objWorksheet->getCellByColumnAndRow(8, $i)->getValue();
$product->line = (string) $objWorksheet->getCellByColumnAndRow(9, $i)->getValue();
$product->price = (string) $objWorksheet->getCellByColumnAndRow(10, $i)->getValue();
$product->ma_price = (string) $objWorksheet->getCellByColumnAndRow(11, $i)->getValue();
$product->save();
}
$productPrice = new PodruzkaPrice();
$productPrice->article = $objWorksheet->getCellByColumnAndRow(0, $i)->getValue();
$productPrice->price = (string) $objWorksheet->getCellByColumnAndRow(10, $i)->getValue();
$productPrice->ma_price = (string) $objWorksheet->getCellByColumnAndRow(11, $i)->getValue();
if (!$productPrice->save()) {
//TODO ?
}
//проверяем значение на пустоту
if (empty($value)) {
$empty_value++;
}
//после 1 пустого значения, завершаем обработку файла, думая, что это конец
if ($empty_value == 1) {
$exit = true;
continue;
}
}
$objPHPExcel->disconnectWorksheets();
//чистим
unset($objPHPExcel);
//память
$startRow += $chunkSize;
//переходим на следующий шаг цикла, увеличивая строку, с которой будем читать файл
}
unlink($filename);
}
/**
* @return string
*/
public function actionArticleUpdate()
{
if ($pArticle = reset($_POST['PodruzkaProduct'])['article']) {
if ($pp = PodruzkaProduct::find()->where(['article' => $pArticle])->one()) {
if (!empty($_POST['l_id'])) {
if ($lp = LetualProduct::find()->where(['article' => $_POST['l_id']])->one()) {
$pp->l_id = $lp->id;
}
} else {
$pp->l_id = null;
}
if (!empty($_POST['r_id'])) {
if ($rp = RivegaucheProduct::find()->where(['article' => $_POST['r_id']])->one()) {
$pp->r_id = $rp->id;
}
} else {
$pp->r_id = null;
}
if (!empty($_POST['i_id'])) {
if ($ip = IledebeauteProduct::find()->where(['article' => $_POST['i_id']])->one()) {
$pp->i_id = $ip->id;
}
} else {
$pp->i_id = null;
}
if (!empty($_POST['e_id'])) {
if ($ep = ElizeProduct::find()->where(['article' => $_POST['e_id']])->one()) {
$pp->e_id = $ep->id;
}
} else {
$pp->e_id = null;
}
$pp->save();
return json_encode(['result' => true]);
}
}
return json_encode(['result' => false]);
}
/**
* @return \yii\db\ActiveQuery
*/
public function getArticle0()
{
return $this->hasOne(PodruzkaProduct::className(), ['article' => 'article']);
}
/**
* Выгрузка полной таблицы информ продукта
*
* @TODO переделать порционно лимит по памяти (
*/
public function actionInformProduct()
{
$downloadAttr = [];
$command = Yii::$app->getDb()->createCommand('
SELECT
`id`,
`article`,
`title`,
`group`,
`category`,
`sub_category`,
`detail`,
`brand`,
`sub_brand`,
`line`,
`price`,
`ma_price`,
`arrival`
FROM podruzka_product
');
$attr = new PodruzkaProduct();
foreach ($attr->attributes() as $att) {
if ($att != 'l_id' && $att != 'r_id' && $att != 'i_id' && $att != 'ile_id' && $att != 'rive_id' && $att != 'letu_id' && $att != 'updated_at' && $att != 'created_at' && $att != 'deleted_at') {
$downloadAttr[] = $att;
}
}
$reader = $command->query();
if (!empty($reader) && !empty($attr)) {
$filename = sprintf('%s.csv', date_format(new \DateTime(), 'Y-m-d'));
$now = gmdate("D, d M Y H:i:s");
header("Expires: Tue, 03 Jul 2001 06:00:00 GMT");
header("Cache-Control: max-age=0, no-cache, must-revalidate, proxy-revalidate");
header("Last-Modified: {$now} GMT");
// force download
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");
header('Content-Type: text/csv; charset=windows-1251');
// disposition / encoding on response body
header("Content-Disposition: attachment;filename={$filename}");
header("Content-Transfer-Encoding: binary");
ob_start();
$df = fopen("php://output", 'w');
fputcsv($df, $downloadAttr, ';');
while ($row = $reader->read()) {
foreach ($row as $r) {
$data[] = iconv('utf-8', 'windows-1251', $r);
}
fputcsv($df, $data, ';');
unset($row);
unset($data);
}
fclose($df);
echo ob_get_clean();
}
}
/**
* Creates data provider instance with search query applied
*
* @param array $params
*
* @return ActiveDataProvider
*/
public function searchPriceMatching($params)
{
$query = PodruzkaProduct::find();
$query->joinWith('l', true, 'LEFT JOIN');
$query->joinWith('r', true, 'LEFT JOIN');
$query->joinWith('i', true, 'LEFT JOIN');
$query->joinWith('e', true, 'LEFT JOIN');
$dataProvider = new ActiveDataProvider(['query' => $query, 'pagination' => ['pageSize' => 50]]);
$this->load($params);
if (!$this->validate()) {
// uncomment the following line if you do not want to return any records when validation fails
// $query->where('0=1');
return $dataProvider;
}
$query->andFilterWhere(['id' => $this->id, 'created_at' => $this->created_at, 'updated_at' => $this->updated_at, 'deleted_at' => $this->deleted_at]);
$dataProvider->sort->attributes['l_article'] = ['asc' => ['letual_product.article' => SORT_ASC], 'desc' => ['letual_product.article' => SORT_DESC]];
$dataProvider->sort->attributes['l_title'] = ['asc' => ['letual_product.title' => SORT_ASC], 'desc' => ['letual_product.title' => SORT_DESC]];
$dataProvider->sort->attributes['l_old_price'] = ['asc' => ['letual_product.old_price' => SORT_ASC], 'desc' => ['letual_product.old_price' => SORT_DESC]];
$dataProvider->sort->attributes['l_new_price'] = ['asc' => ['letual_product.new_price' => SORT_ASC], 'desc' => ['letual_product.new_price' => SORT_DESC]];
$dataProvider->sort->attributes['l_desc'] = ['asc' => ['letual_product.description' => SORT_ASC], 'desc' => ['letual_product.description' => SORT_DESC]];
$dataProvider->sort->attributes['r_article'] = ['asc' => ['rivegauche_product.article' => SORT_ASC], 'desc' => ['rivegauche_product.article' => SORT_DESC]];
$dataProvider->sort->attributes['r_title'] = ['asc' => ['rivegauche_product.title' => SORT_ASC], 'desc' => ['rivegauche_product.title' => SORT_DESC]];
$dataProvider->sort->attributes['r_price'] = ['asc' => ['rivegauche_product.price' => SORT_ASC], 'desc' => ['rivegauche_product.price' => SORT_DESC]];
$dataProvider->sort->attributes['r_blue_price'] = ['asc' => ['rivegauche_product.blue_price' => SORT_ASC], 'desc' => ['rivegauche_product.blue_price' => SORT_DESC]];
$dataProvider->sort->attributes['r_gold_price'] = ['asc' => ['rivegauche_product.gold_price' => SORT_ASC], 'desc' => ['rivegauche_product.gold_price' => SORT_DESC]];
$dataProvider->sort->attributes['r_desc'] = ['asc' => ['rivegauche_product.description' => SORT_ASC], 'desc' => ['rivegauche_product.description' => SORT_DESC]];
$dataProvider->sort->attributes['i_article'] = ['asc' => ['iledebeaute_product.article' => SORT_ASC], 'desc' => ['iledebeaute_product.article' => SORT_DESC]];
$dataProvider->sort->attributes['i_title'] = ['asc' => ['iledebeaute_product.title' => SORT_ASC], 'desc' => ['iledebeaute_product.title' => SORT_DESC]];
$dataProvider->sort->attributes['i_old_price'] = ['asc' => ['iledebeaute_product.old_price' => SORT_ASC], 'desc' => ['iledebeaute_product.old_price' => SORT_DESC]];
$dataProvider->sort->attributes['i_new_price'] = ['asc' => ['iledebeaute_product.new_price' => SORT_ASC], 'desc' => ['iledebeaute_product.new_price' => SORT_DESC]];
$dataProvider->sort->attributes['i_desc'] = ['asc' => ['iledebeaute_product.description' => SORT_ASC], 'desc' => ['iledebeaute_product.description' => SORT_DESC]];
$dataProvider->sort->attributes['e_old_price'] = ['asc' => ['elize_product.old_price' => SORT_ASC], 'desc' => ['elize_product.old_price' => SORT_DESC]];
$dataProvider->sort->attributes['e_new_price'] = ['asc' => ['elize_product.new_price' => SORT_ASC], 'desc' => ['elize_product.new_price' => SORT_DESC]];
$query->andFilterWhere(['like', 'podruzka_product.article', $this->article])->andFilterWhere(['like', 'podruzka_product.title', $this->title])->andFilterWhere(['like', 'podruzka_product.arrival', $this->arrival])->andFilterWhere(['like', 'podruzka_product.group', $this->group])->andFilterWhere(['like', 'podruzka_product.category', $this->category])->andFilterWhere(['like', 'podruzka_product.sub_category', $this->sub_category])->andFilterWhere(['like', 'podruzka_product.detail', $this->detail])->andFilterWhere(['like', 'podruzka_product.brand', $this->brand])->andFilterWhere(['like', 'podruzka_product.sub_brand', $this->sub_brand])->andFilterWhere(['like', 'podruzka_product.line', $this->line])->andFilterWhere(['like', 'podruzka_product.price', $this->price])->andFilterWhere(['like', 'podruzka_product.ma_price', $this->ma_price])->andFilterWhere(['like', 'letual_product.title', $this->l_title])->andFilterWhere(['like', 'letual_product.article', $this->l_article])->andFilterWhere(['like', 'letual_product.description', $this->l_desc])->andFilterWhere(['like', 'letual_product.old_price', $this->l_old_price])->andFilterWhere(['like', 'letual_product.new_price', $this->l_new_price])->andFilterWhere(['like', 'rivegauche_product.title', $this->r_title])->andFilterWhere(['like', 'rivegauche_product.article', $this->r_article])->andFilterWhere(['like', 'rivegauche_product.description', $this->r_desc])->andFilterWhere(['like', 'rivegauche_product.price', $this->r_price])->andFilterWhere(['like', 'rivegauche_product.new_price', $this->r_blue_price])->andFilterWhere(['like', 'rivegauche_product.old_price', $this->r_gold_price])->andFilterWhere(['like', 'iledebeaute_product.title', $this->i_title])->andFilterWhere(['like', 'iledebeaute_product.article', $this->i_article])->andFilterWhere(['like', 'iledebeaute_product.description', $this->i_desc])->andFilterWhere(['like', 'iledebeaute_product.old_price', $this->i_old_price])->andFilterWhere(['like', 'iledebeaute_product.new_price', $this->i_new_price])->andFilterWhere(['like', 'elize_product.old_price', $this->e_old_price])->andFilterWhere(['like', 'elize_product.new_price', $this->e_new_price])->andWhere('l_id is not null or r_id is not null or i_id is not null or e_id is not null');
return $dataProvider;
}
