<?require($_SERVER["DOCUMENT_ROOT"]."/bitrix/modules/main/include/prolog_before.php");
require_once('PHPExcel.php');
require_once('PHPExcel/Writer/Excel2007.php');
$xls = new PHPExcel();
$locale = 'ru';
$validLocale = PHPExcel_Settings::setLocale($locale);
$objPHPexcel = PHPExcel_IOFactory::load('last.xlsm');
$objWorksheet = $objPHPexcel->getActiveSheet();
$nowParent = '';
$exporImg = $_POST['img'];
// $exporImg = "true";
$variable = $_POST['id'];
// $variable = Array(662);
$recDir = Array();
$num = 0;
$i = 6;
CModule::IncludeModule("iblock");
CModule::IncludeModule("catalog");
function getNDSbyId($ID){
$ar_res = CCatalogProduct::GetByID($ID);
$ar_rs = CCatalogVat::GetByID($ar_res['VAT_ID']);
if($a = $ar_rs->GetNext()) return $a["RATE"];
}
/*function getPraysSales(){
$arSelect = Array("ID", "IBLOCK_ID", "NAME", "PROPERTY_*.VALUE");//IBLOCK_ID и ID обязательно должны быть указаны, см. описание arSelectFields выше
$arFilter = Array("IBLOCK_ID"=>9, "ACTIVE_DATE"=>"Y", "ACTIVE"=>"Y");
$res = CIBlockElement::GetList(Array(), $arFilter, false, Array(), $arSelect);
while($ob = $res->GetNextElement()){
$arProps[] = $ob->GetProperties();
}
$i = 1;
$arrSize = count($arProps[0]);
echo $arrSize;
while($arrSize <= $i){
echo 'fg';
$v = $arProps[0]['SKIDKA_VAL_'.$i]['VALUE'];
$s = $arProps[0]['SKIDKA_SUM_'.$i]['VALUE'];
$res[$v] = $s;
$i++;
}
echo '<pre>'; print_r($res); echo '</pre>';
}*/
//getPraysSales();
foreach ($variable as $value){
$rsParentSection = CIBlockSection::GetByID($value);
$arParentSection = $rsParentSection->GetNext();
if ($arParentSection['DEPTH_LEVEL'] == 1){
$nowParent = $arParentSection['ID'];
// echo "1";
$objWorksheet->setCellValue('A'.$i, iconv('windows-1251', 'UTF-8', $arParentSection['NAME']));
$objPHPexcel->getActiveSheet()->getStyle('A'.$i.':L'.$i)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('b69e83');
$i++;
$arFilter = array(
'IBLOCK_ID' => $arParentSection['IBLOCK_ID'],
'>LEFT_MARGIN' => $arParentSection['LEFT_MARGIN'],
'<RIGHT_MARGIN' => $arParentSection['RIGHT_MARGIN'],
'>DEPTH_LEVEL' => $arParentSection['DEPTH_LEVEL'],
"ACTIVE" => "Y"
);
$rsSect = CIBlockSection::GetList(array('left_margin' => 'asc'),$arFilter);
while ($arSect = $rsSect->GetNext())
{
if ($arSect['DEPTH_LEVEL'] == 2){
$objPHPexcel->getActiveSheet()->getStyle('A'.$i.':L'.$i)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('b2ffa0');
}else{
$objPHPexcel->getActiveSheet()->getStyle('A'.$i.':L'.$i)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('f6d6c0');
}
$objWorksheet->setCellValue('A'.$i, iconv('windows-1251', 'UTF-8', $arSect['NAME']));
$objPHPexcel->getActiveSheet()->getRowDimension($i)->setOutlineLevel($arSect['DEPTH_LEVEL']-1);
$objPHPexcel->getActiveSheet()->getRowDimension($i)->setCollapsed(true);
$objPHPexcel->getActiveSheet()->getRowDimension($i)->setVisible(false);
$i++;
$arSelect = Array(
"ID",
"IBLOCK_ID",
"NAME", //Наименование
"PREVIEW_TEXT", //описание
"PREVIEW_PICTURE", //изображение
"PROPERTY_CML2_BAR_CODE", //штрихкод
"PROPERTY_CML2_ARTICLE", //артикул
"PROPERTY_MATERIAL", //материал
"PROPERTY_HOLIDAY", //праздники
"PROPERTY_NEWPRODUCT", // новинка
);
$arFilter = Array(
"IBLOCK_ID"=>2,
"ACTIVE_DATE"=>"Y",
"ACTIVE"=>"Y",
"INCLUDE_SUBSECTIONS" => "N",
"SECTION_ID" => $arSect['ID']
);
$res = CIBlockElement::GetList(Array(), $arFilter, false, false, $arSelect);
while ($arr = $res->GetNext()){
// echo '<pre>'; print_r($arr); echo '</pre>';
$arPrice = CPrice::GetBasePrice($arr['ID']);
$objWorksheet->setCellValue('B'.$i, iconv('windows-1251', 'UTF-8', $arr['PROPERTY_CML2_ARTICLE_VALUE']));
$objWorksheet->setCellValue('C'.$i, iconv('windows-1251', 'UTF-8', $arr['NAME']));
$objWorksheet->setCellValue('D'.$i, iconv('windows-1251', 'UTF-8', $arPrice['PRICE']));
$objWorksheet->setCellValue('H'.$i, iconv('windows-1251', 'UTF-8', $arr['~PREVIEW_TEXT']));
$objWorksheet->setCellValue('I'.$i, iconv('windows-1251', 'UTF-8', $arr['PROPERTY_NEWPRODUCT_VALUE']));
$objWorksheet->setCellValue('J'.$i, iconv('windows-1251', 'UTF-8', $arr['PROPERTY_HOLIDAY_VALUE']));
$objWorksheet->setCellValue('K'.$i, iconv('windows-1251', 'UTF-8', $arr['PROPERTY_CML2_BAR_CODE_VALUE']));
$objWorksheet->setCellValue('L'.$i, iconv('windows-1251', 'UTF-8', $arr['PROPERTY_MATERIAL_VALUE']));
$objWorksheet->setCellValue('M'.$i, iconv('windows-1251', 'UTF-8', getNDSbyId($arr['ID'])));
if (($exporImg == "true") && ($arr['PREVIEW_PICTURE'] != NULL)){
$objPHPexcel->getActiveSheet()->getRowDimension($i)->setRowHeight(79);
$path = CFile::GetPath($arr['PREVIEW_PICTURE']);
$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName('Logo');
$objDrawing->setDescription('Logo');
$objDrawing->setPath('../'.$path);
$objDrawing->setHeight(75);
$objDrawing->setWidth(75);
$objDrawing->setOffsetX(4);
$objDrawing->setOffsetY(4);
$objDrawing->setCoordinates('G'.$i);
$objDrawing->setWorksheet($objPHPexcel->getActiveSheet());
}
$objPHPexcel->getActiveSheet()->getRowDimension($i)->setOutlineLevel($arSect['DEPTH_LEVEL']);
$objPHPexcel->getActiveSheet()->getRowDimension($i)->setCollapsed(true);
$objPHPexcel->getActiveSheet()->getRowDimension($i)->setVisible(false);
//считаем сумму для каждого элемента
$objPHPexcel->getActiveSheet()->setCellValue('F'.$i,'=D'.$i.'*E'.$i);
$objPHPexcel->getActiveSheet()->getStyle('D'.$i)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);
$objPHPexcel->getActiveSheet()->getStyle('E'.$i)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER);
$objPHPexcel->getActiveSheet()->getStyle('F'.$i)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);
$i++;
$num++;
}
}
}else{
$rsParentSection2 = CIBlockSection::GetByID($arParentSection['IBLOCK_SECTION_ID']);
$arParentSection = $rsParentSection2->GetNext();
if ($nowParent != $arParentSection['ID']){
$nowParent = $arParentSection['ID'];
$objWorksheet->setCellValue('A'.$i, iconv('windows-1251', 'UTF-8', $arParentSection['NAME']));
$i++;
}
$rsParentSection3 = CIBlockSection::GetByID($value);
$arParentSection3 = $rsParentSection3->GetNext();
$objWorksheet->setCellValue('A'.$i, iconv('windows-1251', 'UTF-8', $arParentSection3['NAME']));
$objPHPexcel->getActiveSheet()->getRowDimension($i)->setOutlineLevel($arParentSection3['DEPTH_LEVEL']-1);
$objPHPexcel->getActiveSheet()->getRowDimension($i)->setCollapsed(true);
$objPHPexcel->getActiveSheet()->getRowDimension($i)->setVisible(false);
$objPHPexcel->getActiveSheet()->getStyle('A'.$i.':L'.$i)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('f6d6c0');
$i++;
$arFilter = array(
'IBLOCK_ID' => $arParentSection['IBLOCK_ID'],
"ACTIVE" => "Y",
'>LEFT_MARGIN' => $arParentSection3['LEFT_MARGIN'],
'<RIGHT_MARGIN' => $arParentSection3['RIGHT_MARGIN'],
'>=DEPTH_LEVEL' => $arParentSection3['DEPTH_LEVEL']
);
$rsSect = CIBlockSection::GetList(array('left_margin' => 'asc'),$arFilter);
while ($arSect = $rsSect->GetNext())
{
$recDir[] = $arSect['ID'];
$objWorksheet->setCellValue('A'.$i, iconv('windows-1251', 'UTF-8', $arSect['NAME']));
$objPHPexcel->getActiveSheet()->getRowDimension($i)->setOutlineLevel($arSect['DEPTH_LEVEL']-1);
$objPHPexcel->getActiveSheet()->getRowDimension($i)->setCollapsed(true);
$objPHPexcel->getActiveSheet()->getRowDimension($i)->setVisible(false);
$objPHPexcel->getActiveSheet()->getStyle('A'.$i.':L'.$i)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('c0e1f6');
$i++;
$arSelect = Array(
"ID",
"IBLOCK_ID",
"NAME", //Наименование
"PREVIEW_TEXT", //описание
"PREVIEW_PICTURE", //изображение
"PROPERTY_CML2_BAR_CODE", //штрихкод
"PROPERTY_CML2_ARTICLE", //артикул
"PROPERTY_MATERIAL", //материал
"PROPERTY_HOLIDAY", //праздники
"PROPERTY_NEWPRODUCT", // новинка
);
$arFilter = Array(
"IBLOCK_ID"=>2,
"ACTIVE_DATE"=>"Y",
"ACTIVE"=>"Y",
"INCLUDE_SUBSECTIONS" => "N",
"SECTION_ID" => $arSect['ID']
);
$res = CIBlockElement::GetList(Array(), $arFilter, false, false, $arSelect);
while ($arr = $res->GetNext())
{
// echo '<pre>'; print_r($arr); echo '</pre>';
$arPrice = CPrice::GetBasePrice($arr['ID']);
$objWorksheet->setCellValue('B'.$i, iconv('windows-1251', 'UTF-8', $arr['PROPERTY_CML2_ARTICLE_VALUE']));
$objWorksheet->setCellValue('C'.$i, iconv('windows-1251', 'UTF-8', $arr['NAME']));
$objWorksheet->setCellValue('D'.$i, iconv('windows-1251', 'UTF-8', $arPrice['PRICE']));
$objWorksheet->setCellValue('H'.$i, iconv('windows-1251', 'UTF-8', $arr['~PREVIEW_TEXT']));
$objWorksheet->setCellValue('I'.$i, iconv('windows-1251', 'UTF-8', $arr['PROPERTY_NEWPRODUCT_VALUE']));
$objWorksheet->setCellValue('J'.$i, iconv('windows-1251', 'UTF-8', $arr['PROPERTY_HOLIDAY_VALUE']));
$objWorksheet->setCellValue('K'.$i, iconv('windows-1251', 'UTF-8', $arr['PROPERTY_CML2_BAR_CODE_VALUE']));
$objWorksheet->setCellValue('L'.$i, iconv('windows-1251', 'UTF-8', $arr['PROPERTY_MATERIAL_VALUE']));
$objWorksheet->setCellValue('M'.$i, iconv('windows-1251', 'UTF-8', getNDSbyId($arr['ID'])));
if (($exporImg == "true") && ($arr['PREVIEW_PICTURE'] != NULL)){
$objPHPexcel->getActiveSheet()->getRowDimension($i)->setRowHeight(79);
$path = CFile::GetPath($arr['PREVIEW_PICTURE']);
$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName('Logo');
$objDrawing->setDescription('Logo');
$objDrawing->setPath('../'.$path);
$objDrawing->setHeight(75);
$objDrawing->setWidth(75);
$objDrawing->setOffsetX(4);
$objDrawing->setOffsetY(4);
$objDrawing->setCoordinates('G'.$i);
$objDrawing->setWorksheet($objPHPexcel->getActiveSheet());
}
$objPHPexcel->getActiveSheet()->getRowDimension($i)->setOutlineLevel($arSect['DEPTH_LEVEL']);
$objPHPexcel->getActiveSheet()->getRowDimension($i)->setCollapsed(true);
$objPHPexcel->getActiveSheet()->getRowDimension($i)->setVisible(false);
//считаем сумму для каждого элемента
$objPHPexcel->getActiveSheet()->setCellValue('F'.$i,'=D'.$i.'*E'.$i);
$objPHPexcel->getActiveSheet()->getStyle('D'.$i)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);
$objPHPexcel->getActiveSheet()->getStyle('E'.$i)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER);
$objPHPexcel->getActiveSheet()->getStyle('F'.$i)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);
$i++;
$num++;
}
}
}
}
$objWorksheet->setCellValue("F3", "=IF(F2<=9999,0,IF(F2<=14999,5,IF(F2<=24999,10,IF(F2<=49999,15,IF(F2<=99999,20,IF(F2<=149999,25,25))))))");
$objWorksheet->setCellValue('A'.$i, "Раскройте «+» слева");
$fileLink = 'download/polnotsvet-price_'.substr(md5(microtime() . rand(0, 9999)), 0, 5).'.xlsm';
$objPHPexcel->getActiveSheet()->setShowSummaryBelow(false);
$objPHPexcel->getActiveSheet()->setCellValue('F2','=SUM(F6:F'.$i.')');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPexcel, 'Excel2007');
$objWriter->save($fileLink);
$fileSize = 'Размер файла: '.round((filesize($fileLink) / 1048576), 3).' МБ';
header('Content-type: application/json');
print json_encode($result = array(
'success' => true,
'linkFile' => $fileLink,
'fileSize' => $fileSize
));
require($_SERVER["DOCUMENT_ROOT"]."/bitrix/modules/main/include/epilog_after.php");
?> |