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; }