public function postOrganizationExcel(Request $request)
{
$ids = [1, 27];
$organizations = \App\Models\Organization::where('type', 'client')->whereNotIn('id', $ids)->get();
$year = $request->input('year');
$quarters = $request->input('quarters');
if (isset($quarters[1])) {
$first = ' 1,';
} else {
$first = '';
}
if (isset($quarters[2])) {
$second = ' 2,';
} else {
$second = '';
}
if (isset($quarters[3])) {
$third = ' 3,';
} else {
$third = '';
}
if (isset($quarters[4])) {
$fourth = ' 4';
} else {
$fourth = '';
}
$filename = 'Сводный отчет за ' . $year . ' год' . $first . $second . $third . $fourth . ' квартал';
$file = Excel::create($filename, function ($excel) use($organizations, $year, $quarters) {
$excel->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$excel->sheet('Сводный отчет', function ($sheet) use($organizations, $year, $quarters) {
$min = min($quarters);
$iterations = count($quarters);
$sheet->setMergeColumn(array('columns' => array('A', 'B', 'C', 'D'), 'rows' => array(array(1, 2))));
$sheet->row(1, ["№", "Организация", "ИНН", "Балансовая стоимость"]);
$sheet->setWidth('B', 45);
$sheet->setWidth('C', 13);
$sheet->getStyle('A')->getAlignment()->applyFromArray(array('horizontal' => 'center'));
$sheet->setWidth('D', 22);
if ($iterations == 1) {
$number = 1;
$row = 3;
$sheet->getColumnDimension('E')->setAutoSize(true);
$sheet->mergeCells('E1:H1');
$sheet->setCellValue('E1', $min . ' квартал');
$sheet->mergeCells('I1:I2');
$sheet->setWidth('G', 23);
$sheet->setWidth('H', 23);
$sheet->setWidth('I', 23);
$sheet->fromArray(array(array('Статус', 'Износ', 'Приобретение', 'Списание')), null, 'E2', false, false);
$sheet->setCellValue('I1', 'Остаточная стоимость');
$sheet->cells('A1:I2', function ($cells) {
$cells->setAlignment('center');
$cells->setValignment('middle');
});
foreach ($organizations as $organization) {
$report = DB::table('reports')->whereOrganization_idAndYearAndQuarter($organization->id, $year, $min)->first();
$start = [$number, $organization->short_name, $organization->inn, isset($report->report_total_carrying_amount) ? $report->report_total_carrying_amount : 0];
$middle = $this->middle($report);
$end = [isset($report->report_wearout_residual_value) ? $report->report_wearout_residual_value : 0];
$sheet->row($row, array_merge($start, $middle, $end));
$row++;
$sheet->row($row, array('', 'Особоценное движимое имущество', ''));
$row++;
$sheet->row($row, array('', 'Автомобили', ''));
$row++;
$sheet->row($row, array('', 'Движимое имущество', ''));
$row++;
$sheet->row($row, array('', 'Здания и сооружения', ''));
$row++;
$sheet->row($row, array('', 'Земельные участки', ''));
$row++;
$number++;
}
}
if ($iterations == 2) {
$number = 1;
$row = 3;
$sheet->getColumnDimension('E')->setAutoSize(true);
$sheet->getColumnDimension('I')->setAutoSize(true);
$sheet->mergeCells('E1:H1');
$sheet->mergeCells('I1:L1');
$sheet->setCellValue('E1', $min . ' квартал');
$sheet->setCellValue('I1', $min + 1 . ' квартал');
$sheet->setWidth('G', 15);
$sheet->setWidth('H', 15);
$sheet->setWidth('I', 15);
$sheet->setWidth('J', 15);
$sheet->setWidth('K', 15);
$sheet->setWidth('L', 15);
$sheet->fromArray(array(array('Статус', 'Износ', 'Приобретение', 'Списание')), null, 'E2', false, false);
$sheet->fromArray(array(array('Статус', 'Износ', 'Приобретение', 'Списание')), null, 'I2', false, false);
$sheet->mergeCells('M1:M2');
$sheet->setWidth('M', 23);
$sheet->setCellValue('M1', 'Остаточная стоимость');
$sheet->cells('A1:M2', function ($cells) {
$cells->setAlignment('center');
$cells->setValignment('middle');
});
foreach ($organizations as $organization) {
$report = DB::table('reports')->whereOrganization_idAndYearAndQuarter($organization->id, $year, $min)->first();
$report1 = DB::table('reports')->whereOrganization_idAndYearAndQuarter($organization->id, $year, $min + 1)->first();
$start = [$number, $organization->short_name, $organization->inn, isset($report->report_total_carrying_amount) ? $report->report_total_carrying_amount : 0];
$middle = $this->middle($report);
$middle1 = $this->middle($report1);
$end = [isset($report1->report_wearout_residual_value) ? $report1->report_wearout_residual_value : 0];
$sheet->row($row, array_merge($start, $middle, $middle1, $end));
$row++;
$sheet->row($row, array('', 'Особоценное движимое имущество', ''));
$row++;
$sheet->row($row, array('', 'Автомобили', ''));
$row++;
$sheet->row($row, array('', 'Движимое имущество', ''));
$row++;
$sheet->row($row, array('', 'Здания и сооружения', ''));
$row++;
$sheet->row($row, array('', 'Земельные участки', ''));
$row++;
$number++;
}
}
if ($iterations == 3) {
$number = 1;
$row = 3;
$sheet->getColumnDimension('E')->setAutoSize(true);
$sheet->getColumnDimension('I')->setAutoSize(true);
$sheet->getColumnDimension('M')->setAutoSize(true);
$sheet->mergeCells('E1:H1');
$sheet->mergeCells('I1:L1');
$sheet->mergeCells('M1:P1');
$sheet->setCellValue('E1', $min . ' квартал');
$sheet->setCellValue('I1', $min + 1 . ' квартал');
$sheet->setCellValue('M1', $min + 2 . ' квартал');
$sheet->setWidth('G', 15);
$sheet->setWidth('H', 15);
$sheet->setWidth('I', 15);
$sheet->setWidth('J', 15);
$sheet->setWidth('K', 15);
$sheet->setWidth('L', 15);
$sheet->setWidth('N', 15);
$sheet->setWidth('O', 15);
$sheet->setWidth('P', 15);
$sheet->fromArray(array(array('Статус', 'Износ', 'Приобретение', 'Списание')), null, 'E2', false, false);
$sheet->fromArray(array(array('Статус', 'Износ', 'Приобретение', 'Списание')), null, 'I2', false, false);
$sheet->setCellValue('M2', 'Статус');
$sheet->setCellValue('N2', 'Износ');
$sheet->setCellValue('O2', 'Приобретение');
$sheet->setCellValue('P2', 'Списание');
$sheet->mergeCells('Q1:Q2');
$sheet->setWidth('Q', 23);
$sheet->setCellValue('Q1', 'Остаточная стоимость');
$sheet->cells('A1:Q2', function ($cells) {
$cells->setAlignment('center');
$cells->setValignment('middle');
});
foreach ($organizations as $organization) {
$report = DB::table('reports')->whereOrganization_idAndYearAndQuarter($organization->id, $year, $min)->first();
$report1 = DB::table('reports')->whereOrganization_idAndYearAndQuarter($organization->id, $year, $min + 1)->first();
$report2 = DB::table('reports')->whereOrganization_idAndYearAndQuarter($organization->id, $year, $min + 2)->first();
$start = [$number, $organization->short_name, $organization->inn, isset($report->report_total_carrying_amount) ? $report->report_total_carrying_amount : 0];
$middle = $this->middle($report);
$middle1 = $this->middle($report1);
$middle2 = $this->middle($report2);
$end = [isset($report2->report_wearout_residual_value) ? $report2->report_wearout_residual_value : 0];
$sheet->row($row, array_merge($start, $middle, $middle1, $middle2, $end));
$row++;
$sheet->row($row, array('', 'Особоценное движимое имущество', ''));
$row++;
$sheet->row($row, array('', 'Автомобили', ''));
$row++;
$sheet->row($row, array('', 'Движимое имущество', ''));
$row++;
$sheet->row($row, array('', 'Здания и сооружения', ''));
$row++;
$sheet->row($row, array('', 'Земельные участки', ''));
$row++;
$number++;
}
}
if ($iterations == 4) {
$number = 1;
$row = 3;
$sheet->getColumnDimension('E')->setAutoSize(true);
$sheet->getColumnDimension('I')->setAutoSize(true);
$sheet->getColumnDimension('M')->setAutoSize(true);
$sheet->getColumnDimension('Q')->setAutoSize(true);
$sheet->mergeCells('E1:H1');
$sheet->mergeCells('I1:L1');
$sheet->mergeCells('M1:P1');
$sheet->mergeCells('Q1:T1');
$sheet->setCellValue('E1', $min . ' квартал');
$sheet->setCellValue('I1', $min + 1 . ' квартал');
$sheet->setCellValue('M1', $min + 2 . ' квартал');
$sheet->setCellValue('Q1', $min + 3 . ' квартал');
$sheet->setWidth('G', 15);
$sheet->setWidth('H', 15);
$sheet->setWidth('I', 15);
$sheet->setWidth('J', 15);
$sheet->setWidth('K', 15);
$sheet->setWidth('L', 15);
$sheet->setWidth('N', 15);
$sheet->setWidth('O', 15);
$sheet->setWidth('P', 15);
$sheet->setWidth('R', 15);
$sheet->setWidth('S', 15);
$sheet->setWidth('T', 15);
$sheet->fromArray(array(array('Статус', 'Износ', 'Приобретение', 'Списание')), null, 'E2', false, false);
$sheet->fromArray(array(array('Статус', 'Износ', 'Приобретение', 'Списание')), null, 'I2', false, false);
$sheet->setCellValue('M2', 'Статус');
$sheet->setCellValue('N2', 'Износ');
$sheet->setCellValue('O2', 'Приобретение');
$sheet->setCellValue('P2', 'Списание');
$sheet->setCellValue('Q2', 'Статус');
$sheet->setCellValue('R2', 'Износ');
$sheet->setCellValue('S2', 'Приобретение');
$sheet->setCellValue('T2', 'Списание');
$sheet->mergeCells('U1:U2');
$sheet->setWidth('U', 23);
$sheet->setCellValue('U1', 'Остаточная стоимость');
$sheet->cells('A1:U2', function ($cells) {
$cells->setAlignment('center');
$cells->setValignment('middle');
});
foreach ($organizations as $organization) {
$report = DB::table('reports')->whereOrganization_idAndYearAndQuarter($organization->id, $year, $min)->first();
$report1 = DB::table('reports')->whereOrganization_idAndYearAndQuarter($organization->id, $year, $min + 1)->first();
$report2 = DB::table('reports')->whereOrganization_idAndYearAndQuarter($organization->id, $year, $min + 2)->first();
$report3 = DB::table('reports')->whereOrganization_idAndYearAndQuarter($organization->id, $year, $min + 3)->first();
$start = [$number, $organization->short_name, $organization->inn, isset($report->report_total_carrying_amount) ? $report->report_total_carrying_amount : 0];
$middle = $this->middle($report);
$middle1 = $this->middle($report1);
$middle2 = $this->middle($report2);
$middle3 = $this->middle($report3);
$end = [isset($report3->report_wearout_residual_value) ? $report3->report_wearout_residual_value : 0];
$sheet->row($row, array_merge($start, $middle, $middle1, $middle2, $middle3, $end));
$row++;
$sheet->row($row, array('', 'Особоценное движимое имущество', ''));
$row++;
$sheet->row($row, array('', 'Автомобили', ''));
$row++;
$sheet->row($row, array('', 'Движимое имущество', ''));
$row++;
$sheet->row($row, array('', 'Здания и сооружения', ''));
$row++;
$sheet->row($row, array('', 'Земельные участки', ''));
$row++;
$number++;
}
}
});
})->store('xlsx', storage_path('excel/exports'), true);
return Response::download($file['full']);
}
/**
* @param $verification_code
* @return Organization
*/
public static function findByVerificationCodeOrFail($verification_code)
{
return Organization::where('verification_code', $verification_code)->firstOrFail();
}
protected function parseTenders(Client $client, Crawler $crawler)
{
$repeatSensor = 0;
$contractsNum = 0;
$crawler->filter('div.registerBox')->each(function (Crawler $node, $i) use($client, &$repeatSensor, &$contractsNum) {
$systemId = str_replace('№ ', '', trim($node->filter('td.descriptTenderTd > dl > dt > a')->text()));
$organizationNode = $node->filter('dd.nameOrganization > a');
$organizationName = trim($organizationNode->text());
$organizationUrl = trim($organizationNode->attr('href'));
//Log::info('Node data', [$node->html()]);
$contractName = trim($node->filter('td.descriptTenderTd > dl > dd')->eq(1)->text());
$contractUrl = $node->filter('td.descriptTenderTd > dl > dt > a')->attr('href');
$contractType = trim($node->filter('td.tenderTd > dl > dt')->eq(0)->text());
$contractStatus = '';
/*Log::info('Обработка нового контракта', [
'org_name' => $organizationName,
'org_url' => $organizationUrl,
'name' => $contractName,
'url' => $contractUrl
]);*/
$contract = Contract::where('system_id', $systemId)->first();
if (!$contract) {
$repeatSensor = 0;
// Search organization in database
$organization = Organization::where('url', $organizationUrl)->first();
if (!$organization) {
//Log::info('Организация не найдена, добавляем в базу.');
$organization = new Organization();
$organization->name = $organizationName;
$organization->url = $organizationUrl;
$this->info($organizationUrl);
$organizationResponse = $client->get($organizationUrl);
$organizationCrawler = new Crawler((string) $organizationResponse->getBody());
//Log::info('Информация по организации загружена.');
// Federal Law 223
if (preg_match("/223\\/ppa/", $organizationUrl)) {
$organizationCrawler->filter('div.noticeTabBoxWrapper > table tr')->each(function (Crawler $row, $j) use(&$organization) {
if ($row->children('td')->count() > 1) {
$nameColumn = trim($row->children('td')->eq(0)->text());
$valueColumn = trim($row->children('td')->eq(1)->text());
if ($valueColumn) {
switch ($nameColumn) {
case 'Уровень организации':
$organization->level = $valueColumn;
break;
case 'ИНН':
$organization->inn = $valueColumn;
break;
case 'КПП':
$organization->kpp = $valueColumn;
break;
case 'ОГРН':
$organization->ogrn = $valueColumn;
break;
case 'ОКАТО':
$organization->okato = $valueColumn;
break;
case 'Адрес (место нахождения)':
$addresses = array_map(function ($value) {
return trim($value);
}, explode(',', $valueColumn));
$address = collect($addresses);
$address->forget('Российская Федерация');
$organization->postal_code = $address[0];
$organization->country_id = 1;
$country = Country::find(1);
if (isset($address[3])) {
$region = Region::where('name', $address[2])->where('country_id', $country->id)->first();
if (!$region) {
$region = Region::create(['country_id' => $country->id, 'name' => $address[2]]);
}
$town = Town::where('name', $address[3])->where('region_id', $region->id)->first();
if (!$town) {
$town = Town::create(['region_id' => $region->id, 'name' => $address[3]]);
}
$organization->region_id = $region->id;
$organization->town_id = $town->id;
}
$organization->address = $valueColumn;
break;
case 'Телефон':
$organization->contact_phone = $valueColumn;
break;
case 'Факс':
$organization->contact_fax = $valueColumn;
break;
case 'Почтовый адрес':
$organization->contact_address = $valueColumn;
break;
case 'Контактное лицо':
$organization->contact_name = $valueColumn;
break;
case 'Адрес электронной почты для системных уведомлений':
$organization->contact_email = $valueColumn;
break;
}
}
}
});
} else {
$organizationCrawler->filter('td.icePnlTbSetCnt table tr')->each(function (Crawler $row, $j) use($organization) {
if ($row->children('td')->count() > 1) {
$nameColumn = trim($row->children('td')->eq(0)->text());
$valueColumn = trim($row->children('td')->eq(1)->text());
if ($valueColumn) {
switch ($nameColumn) {
case 'Уровень организации':
$organization->level = $valueColumn;
break;
case 'ИНН':
$organization->inn = $valueColumn;
break;
case 'КПП':
$organization->kpp = $valueColumn;
break;
case 'ОГРН':
$organization->ogrn = $valueColumn;
break;
case 'ОКАТО':
$organization->okato = $valueColumn;
break;
case 'Место нахождения':
$address = array_map(function ($value) {
return trim($value);
}, explode(',', $valueColumn));
$this->info($valueColumn);
$organization->postal_code = $address[1];
$country = Country::where('name', 'Российская Федерация')->first();
$region = Region::where('name', $address[2])->where('country_id', $country->id)->first();
if (!$region) {
$region = Region::create(['country_id' => $country->id, 'name' => $address[2]]);
}
if (isset($address[3])) {
$town = Town::where('name', $address[3])->where('region_id', $region->id)->first();
if (!$town) {
$town = Town::create(['region_id' => $region->id, 'name' => $address[3]]);
}
}
$organization->country_id = $country->id;
$organization->region_id = $region->id;
$organization->town_id = isset($town) ? $town->id : null;
$organization->address = $valueColumn;
break;
case 'Телефон':
$organization->contact_phone = $valueColumn;
break;
case 'Факс':
$organization->contact_fax = $valueColumn;
break;
case 'Почтовый адрес':
$organization->contact_address = $valueColumn;
break;
case 'Контактное лицо':
$organization->contact_name = $valueColumn;
break;
case 'Контактный адрес электронной почты':
$organization->contact_email = $valueColumn;
break;
}
}
}
});
}
//dd($organization);
$organization->save();
//Log::info('Организация добавлена в базу.');
$this->info('Organization ' . $organizationName);
} else {
//Log::info('Организация найдена в базе.');
}
//Log::info('Переходим на страницу контракта.');
$contractResponse = $client->get($contractUrl);
$contractCrawler = new Crawler((string) $contractResponse->getBody());
//Log::info('Страница контракта загружена.');
$contract = new Contract();
$contract->organization_id = $organization->id;
$contract->system_id = $systemId;
$contract->name = $contractName;
$contract->link = $contractUrl;
$contract->status = $contractStatus;
$contract->type = $contractType;
$price = str_replace(',', '.', preg_replace("/([^0-9\\.\\,]*)/", '', trim($node->filter('td.tenderTd > dl > dd')->eq(1)->text())));
$contract->price = $price;
// Federal Law 223
$contractCrawler->filter('div.noticeTabBoxWrapper > table tr')->each(function (Crawler $row, $j) use($contract, $contractUrl) {
if ($row->filter('td')->count() > 1) {
$nameColumn = trim($row->filter('td')->eq(0)->text());
$valueColumn = trim($row->filter('td')->eq(1)->text());
if (!$valueColumn) {
return;
}
if (preg_match("/223\\/purchase/", $contractUrl)) {
if (preg_match('/подачи заявок/i', $nameColumn)) {
preg_match("/(\\d{2}\\.\\d{2}\\.\\d{4}\\sв\\s\\d{2}:\\d{2})/ui", $valueColumn, $date);
if (!isset($date[1])) {
return;
}
$valueColumn = str_replace('в', '', $date[1]);
$finishDate = new Carbon($valueColumn);
$contract->finished_at = $finishDate;
} elseif (preg_match('/подведения итогов/i', $nameColumn)) {
preg_match("/(\\d{2}[\\.]{1}\\d{2}[\\.]{1}\\d{4}[ ]{1}[в]{1}[ ]{1}\\d{2}:\\d{2})/ui", $valueColumn, $date);
if (!isset($date[1])) {
return;
}
$valueColumn = str_replace('в', '', $date[1]);
$resultDate = new Carbon($valueColumn);
$contract->results_at = $resultDate;
}
} else {
if (preg_match('/Дата и время окончания подачи заявок/i', $nameColumn) || preg_match('/Дата и время окончания подачи котировочных заявок/i', $nameColumn)) {
$valueColumn = str_replace('в', '', $valueColumn);
$finishDate = new Carbon($valueColumn);
$contract->finished_at = $finishDate;
} elseif (preg_match('/Дата проведения аукциона в электронной форме/i', $nameColumn) || preg_match('/Дата и время вскрытия конвертов с заявками/i', $nameColumn)) {
// Не ставим точную дату, так как скрипт проверять будет на следующий день
$valueColumn = str_replace('в', '', $valueColumn);
$resultDate = new Carbon($valueColumn);
$contract->results_at = $resultDate;
}
}
}
});
$contract->save();
$contractsNum++;
$this->info('Контракт ' . $systemId . ' ' . $contractName);
Log::info('Контракт сохранен в базу.');
} else {
$repeatSensor++;
Log::info('Контракт найден в базе.');
}
usleep(rand(200, 2000) * 1000);
// sleep for random time
});
return $contractsNum;
}
