PHP скрипт сжирает всю память, помогите оптимизировать

1101

Dram

9 октября 2021, 07:14

254

Я написал просто адский говнокод, прошу помочь его оптимизировать. Суть скрипта в следующем - есть гугл таблица, он берет из определённого диапазона комментарии к ячейкам и далее в цикле он перемножает эти данные с данными из базы и формирует массив, который я пишу в другой диапазон этой же таблицы.

На ВПС 4 гига памяти, скрипту я выделили 3.

9 из 10 раз он вываливается по

PHP Fatal error:  Allowed memory size of 3145728000

но иногда все же получается отработать как нужно. Прошу помочь его оптимизировать. (достаньте капли для глаз, далее много говнокода!)

<?php
$day_array = ['2021-10-01'];

$letter = 'I';
$range0 = 'Holdem!C4:C23';
$response0 = $service->spreadsheets_values->get($spreadsheetId, $range0);
foreach ($day_array as $date) {
    var_dump($date);
    // Удаление содержимого ячеек рабочей области (она скрыта)
    $response = $service->spreadsheets_values->clear($spreadsheetId, 'Holdem!I4:M23', new Google_Service_Sheets_ClearValuesRequest([]));
    echo 'Удаление содержимого ячеек рабочей области (она скрыта)'.PHP_EOL;
    ////////////////////////// I4 ///////////////////////////
    $rangeC4 = 'Holdem!C4';
    $service = new Google_Service_Sheets($client);
    $spreadsheet = $service->spreadsheets->get($spreadsheetId, ['includeGridData' => true]);
    $sheet = $spreadsheet->getSheets();
    $bbC4 = (float)str_replace(',', '.', $sheet[0]['data'][0]['rowData'][3]['values'][3]['note']);
    $searchC4 = $conn->query("SELECT COUNT(*) as countC4 FROM `hands` WHERE `bb` = '{$bbC4}' AND `prize` = '{$response0->values[0][0]}' and `poker_type` = 'holdem' and win_date like '{$date}%'");
    $result_searchC4 = $searchC4->fetchAll(PDO::FETCH_ASSOC);
    $options = ['valueInputOption' => 'USER_ENTERED'];
////////////////////////// I5 ///////////////////////////
    $rangeC5 = 'Holdem!C5';
    $bbC5 = (float)str_replace(',', '.', $sheet[0]['data'][0]['rowData'][4]['values'][3]['note']);
    $searchC5 = $conn->query("SELECT COUNT(*) as countC5 FROM `hands` WHERE `bb` = '{$bbC5}' AND `prize` = '{$response0->values[1][0]}' and `poker_type` = 'holdem' and win_date like '{$date}%'");
    $result_searchC5 = $searchC5->fetchAll(PDO::FETCH_ASSOC);
////////////////////////// K5 ///////////////////////////
    $rangeK5 = 'Holdem!C5';
    $bbK5 = (float)str_replace(',', '.', $sheet[0]['data'][0]['rowData'][4]['values'][5]['note']);
    $searchK5 = $conn->query("SELECT COUNT(*) as countC5 FROM `hands` WHERE `bb` = '{$bbK5}' AND `prize` = '{$response0->values[1][0]}' and `poker_type` = 'holdem' and win_date like '{$date}%'");
    $result_searchK5 = $searchK5->fetchAll(PDO::FETCH_ASSOC);
////////////////////////// I_J6 ///////////////////////////
    $rangeI_J6 = 'Holdem!C6';
    $bbI_J6 = (float)str_replace(',', '.', $sheet[0]['data'][0]['rowData'][5]['values'][3]['note']);
    $searchI_J6 = $conn->query("SELECT COUNT(*) as countI_J6 FROM `hands` WHERE `bb` = '{$bbI_J6}' AND `prize` = '{$response0->values[2][0]}' and `poker_type` = 'holdem' and win_date like '{$date}%'");
    $result_searchI_J6 = $searchI_J6->fetchAll(PDO::FETCH_ASSOC);
////////////////////////// K_M6 ///////////////////////////
    $rangeK_M6 = 'Holdem!C6';
    $bbK_M6 = (float)str_replace(',', '.', $sheet[0]['data'][0]['rowData'][5]['values'][5]['note']);
    $searchK_M6 = $conn->query("SELECT COUNT(*) as countK_M6 FROM `hands` WHERE `bb` = '{$bbK_M6}' AND `prize` = '{$response0->values[2][0]}' and `poker_type` = 'holdem' and win_date like '{$date}%'");
    $result_searchK_M6 = $searchK_M6->fetchAll(PDO::FETCH_ASSOC);
////////////////////////// I_M7 ///////////////////////////
    $rangeI_M7 = 'Holdem!C7';
    $bbI_M7 = (float)str_replace(',', '.', $sheet[0]['data'][0]['rowData'][6]['values'][3]['note']);
    $searchI_M7 = $conn->query("SELECT COUNT(*) as countI_M7 FROM `hands` WHERE `bb` = '{$bbI_M7}' AND `prize` = '{$response0->values[3][0]}' and `poker_type` = 'holdem' and win_date like '{$date}%'");
    $result_searchI_M7 = $searchI_M7->fetchAll(PDO::FETCH_ASSOC);
////////////////////////// I_K8 ///////////////////////////
    $rangeI_K8 = 'Holdem!C8';
    $bbI_K8 = (float)str_replace(',', '.', $sheet[0]['data'][0]['rowData'][7]['values'][3]['note']);
    $searchI_K8 = $conn->query("SELECT COUNT(*) as countI_K8 FROM `hands` WHERE `bb` = '{$bbI_K8}' AND `prize` = '{$response0->values[4][0]}' and `poker_type` = 'holdem' and win_date like '{$date}%'");
    $result_searchI_K8 = $searchI_K8->fetchAll(PDO::FETCH_ASSOC);
////////////////////////// K_M8 ///////////////////////////
    $rangeK_M8 = 'Holdem!C8';
    $bbK_M8 = (float)str_replace(',', '.', $sheet[0]['data'][0]['rowData'][7]['values'][5]['note']);
    $searchK_M8 = $conn->query("SELECT COUNT(*) as countK_M8 FROM `hands` WHERE `bb` = '{$bbK_M8}' AND `prize` = '{$response0->values[4][0]}' and `poker_type` = 'holdem' and win_date like '{$date}%'");
    $result_searchK_M8 = $searchK_M8->fetchAll(PDO::FETCH_ASSOC);
////////////////////////// I_J9 ///////////////////////////
    $rangeI_J9 = 'Holdem!C9';
    $bbI_J9 = (float)str_replace(',', '.', $sheet[0]['data'][0]['rowData'][8]['values'][3]['note']);
    $searchI_J9 = $conn->query("SELECT COUNT(*) as countI_J9 FROM `hands` WHERE `bb` = '{$bbI_J9}' AND `prize` = '{$response0->values[5][0]}' and `poker_type` = 'holdem' and win_date like '{$date}%'");
    $result_searchI_J9 = $searchI_J9->fetchAll(PDO::FETCH_ASSOC);
////////////////////////// K_M9 ///////////////////////////
    $rangeK_M9 = 'Holdem!C9';
    $bbK_M9 = (float)str_replace(',', '.', $sheet[0]['data'][0]['rowData'][8]['values'][5]['note']);
    $searchK_M9 = $conn->query("SELECT COUNT(*) as countK_M9 FROM `hands` WHERE `bb` = '{$bbK_M9}' AND `prize` = '{$response0->values[5][0]}' and `poker_type` = 'holdem' and win_date like '{$date}%'");
    $result_searchK_M9 = $searchK_M9->fetchAll(PDO::FETCH_ASSOC);
////////////////////////// I10 ///////////////////////////
    $rangeI10 = 'Holdem!C10';
    $bbI10 = (float)str_replace(',', '.', $sheet[0]['data'][0]['rowData'][9]['values'][3]['note']);
    $searchI10 = $conn->query("SELECT COUNT(*) as countI10 FROM `hands` WHERE `bb` = '{$bbI10}' AND `prize` = '{$response0->values[6][0]}' and `poker_type` = 'holdem' and win_date like '{$date}%'");
    $result_searchI10 = $searchI10->fetchAll(PDO::FETCH_ASSOC);
////////////////////////// I11 ///////////////////////////
    $rangeI11 = 'Holdem!C11';
    $bbI11 = (float)str_replace(',', '.', $sheet[0]['data'][0]['rowData'][10]['values'][3]['note']);
    $searchI11 = $conn->query("SELECT COUNT(*) as countI11 FROM `hands` WHERE `bb` = '{$bbI11}' AND `prize` = '{$response0->values[7][0]}' and `poker_type` = 'holdem' and win_date like '{$date}%'");
    $result_searchI11 = $searchI11->fetchAll(PDO::FETCH_ASSOC);
////////////////////////// J_K11 ///////////////////////////
    echo 'Собрали половину массива'.PHP_EOL;
    $rangeJ_K11 = 'Holdem!C11';
    $bbJ_K11 = (float)str_replace(',', '.', $sheet[0]['data'][0]['rowData'][10]['values'][4]['note']);
    $searchJ_K11 = $conn->query("SELECT COUNT(*) as countJ_K11 FROM `hands` WHERE `bb` = '{$bbJ_K11}' AND `prize` = '{$response0->values[7][0]}' and `poker_type` = 'holdem' and win_date like '{$date}%'");
    $result_searchJ_K11 = $searchJ_K11->fetchAll(PDO::FETCH_ASSOC);
////////////////////////// L_M11 ///////////////////////////
    $rangeL_M11 = 'Holdem!C11';
    $bbL_M11 = (float)str_replace(',', '.', $sheet[0]['data'][0]['rowData'][10]['values'][5]['note']);
    $searchL_M11 = $conn->query("SELECT COUNT(*) as countL_M11 FROM `hands` WHERE `bb` = '{$bbL_M11}' AND `prize` = '{$response0->values[7][0]}' and `poker_type` = 'holdem' and win_date like '{$date}%'");
    $result_searchL_M11 = $searchL_M11->fetchAll(PDO::FETCH_ASSOC);
////////////////////////// I12 ///////////////////////////
    $rangeI12 = 'Holdem!C12';
    $bbI12 = (float)str_replace(',', '.', $sheet[0]['data'][0]['rowData'][11]['values'][3]['note']);
    $searchI12 = $conn->query("SELECT COUNT(*) as countI12 FROM `hands` WHERE `bb` = '{$bbI12}' AND `prize` = '{$response0->values[8][0]}' and `poker_type` = 'holdem' and win_date like '{$date}%'");
    $result_searchI12 = $searchI12->fetchAll(PDO::FETCH_ASSOC);
////////////////////////// I13 ///////////////////////////
    $rangeI13 = 'Holdem!C13';
    $bbI13 = (float)str_replace(',', '.', $sheet[0]['data'][0]['rowData'][12]['values'][3]['note']);
    $searchI13 = $conn->query("SELECT COUNT(*) as countI13 FROM `hands` WHERE `bb` = '{$bbI13}' AND `prize` = '{$response0->values[9][0]}' and `poker_type` = 'holdem' and win_date like '{$date}%'");
    $result_searchI13 = $searchI13->fetchAll(PDO::FETCH_ASSOC);
////////////////////////// J13 ///////////////////////////
    $rangeJ13 = 'Holdem!C13';
    $bbJ13 = (float)str_replace(',', '.', $sheet[0]['data'][0]['rowData'][12]['values'][4]['note']);
    $searchJ13 = $conn->query("SELECT COUNT(*) as countJ13 FROM `hands` WHERE `bb` = '{$bbJ13}' AND `prize` = '{$response0->values[9][0]}' and `poker_type` = 'holdem' and win_date like '{$date}%'");
    $result_searchJ13 = $searchJ13->fetchAll(PDO::FETCH_ASSOC);
////////////////////////// K_M13 ///////////////////////////
    $rangeK_M13 = 'Holdem!C13';
    $bbK_M13 = (float)str_replace(',', '.', $sheet[0]['data'][0]['rowData'][12]['values'][5]['note']);
    $searchK_M13 = $conn->query("SELECT COUNT(*) as countK_M13 FROM `hands` WHERE `bb` = '{$bbK_M13}' AND `prize` = '{$response0->values[9][0]}' and `poker_type` = 'holdem' and win_date like '{$date}%'");
    $result_searchK_M13 = $searchK_M13->fetchAll(PDO::FETCH_ASSOC);
////////////////////////// I14 ///////////////////////////
    $rangeI14 = 'Holdem!C14';
    $bbI14 = (float)str_replace(',', '.', $sheet[0]['data'][0]['rowData'][13]['values'][3]['note']);
    $searchI14 = $conn->query("SELECT COUNT(*) as countI14 FROM `hands` WHERE `bb` = '{$bbI14}' AND `prize` = '{$response0->values[10][0]}' and `poker_type` = 'holdem' and win_date like '{$date}%'");
    $result_searchI14 = $searchI14->fetchAll(PDO::FETCH_ASSOC);
////////////////////////// J14 ///////////////////////////
    $rangeJ14 = 'Holdem!C14';
    $bbJ14 = (float)str_replace(',', '.', $sheet[0]['data'][0]['rowData'][13]['values'][4]['note']);
    $searchJ14 = $conn->query("SELECT COUNT(*) as countJ14 FROM `hands` WHERE `bb` = '{$bbJ14}' AND `prize` = '{$response0->values[10][0]}' and `poker_type` = 'holdem' and win_date like '{$date}%'");
    $result_searchJ14 = $searchJ14->fetchAll(PDO::FETCH_ASSOC);
////////////////////////// K14 ///////////////////////////
    $rangeK14 = 'Holdem!C14';
    $bbK14 = (float)str_replace(',', '.', $sheet[0]['data'][0]['rowData'][13]['values'][5]['note']);
    $searchK14 = $conn->query("SELECT COUNT(*) as countK14 FROM `hands` WHERE `bb` = '{$bbK14}' AND `prize` = '{$response0->values[10][0]}' and `poker_type` = 'holdem' and win_date like '{$date}%'");
    $result_searchK14 = $searchK14->fetchAll(PDO::FETCH_ASSOC);
////////////////////////// L14 ///////////////////////////
    $rangeL14 = 'Holdem!C14';
    $bbL14 = (float)str_replace(',', '.', $sheet[0]['data'][0]['rowData'][13]['values'][6]['note']);
    $searchL14 = $conn->query("SELECT COUNT(*) as countL14 FROM `hands` WHERE `bb` = '{$bbL14}' AND `prize` = '{$response0->values[10][0]}' and `poker_type` = 'holdem' and win_date like '{$date}%'");
    $result_searchL14 = $searchL14->fetchAll(PDO::FETCH_ASSOC);
////////////////////////// M14 ///////////////////////////
    $rangeM14 = 'Holdem!C14';
    $bbM14 = (float)str_replace(',', '.', $sheet[0]['data'][0]['rowData'][13]['values'][7]['note']);
    $searchM14 = $conn->query("SELECT COUNT(*) as countM14 FROM `hands` WHERE `bb` = '{$bbM14}' AND `prize` = '{$response0->values[10][0]}' and `poker_type` = 'holdem' and win_date like '{$date}%'");
    $result_searchM14 = $searchM14->fetchAll(PDO::FETCH_ASSOC);
////////////////////////// I15 ///////////////////////////
    $rangeI15 = 'Holdem!C15';
    $bbI15 = (float)str_replace(',', '.', $sheet[0]['data'][0]['rowData'][14]['values'][3]['note']);
    $searchI15 = $conn->query("SELECT COUNT(*) as countI15 FROM `hands` WHERE `bb` = '{$bbI15}' AND `prize` = '{$response0->values[11][0]}' and `poker_type` = 'holdem' and win_date like '{$date}%'");
    $result_searchI15 = $searchI15->fetchAll(PDO::FETCH_ASSOC);
////////////////////////// J15 ///////////////////////////
    $rangeJ15 = 'Holdem!C15';
    $bbJ15 = (float)str_replace(',', '.', $sheet[0]['data'][0]['rowData'][14]['values'][4]['note']);
    $searchJ15 = $conn->query("SELECT COUNT(*) as countJ15 FROM `hands` WHERE `bb` = '{$bbJ15}' AND `prize` = '{$response0->values[11][0]}' and `poker_type` = 'holdem' and win_date like '{$date}%'");
    $result_searchJ15 = $searchJ15->fetchAll(PDO::FETCH_ASSOC);
////////////////////////// K_M15 ///////////////////////////
    $rangeK_M15 = 'Holdem!C15';
    $bbK_M15 = (float)str_replace(',', '.', $sheet[0]['data'][0]['rowData'][14]['values'][5]['note']);
    $searchK_M15 = $conn->query("SELECT COUNT(*) as countK_M15 FROM `hands` WHERE `bb` = '{$bbK_M15}' AND `prize` = '{$response0->values[11][0]}' and `poker_type` = 'holdem' and win_date like '{$date}%'");
    $result_searchK_M15 = $searchK_M15->fetchAll(PDO::FETCH_ASSOC);
////////////////////////// I_J16 ///////////////////////////
    $rangeI_J16 = 'Holdem!C16';
    $bbI_J16 = (float)str_replace(',', '.', $sheet[0]['data'][0]['rowData'][15]['values'][3]['note']);
    $searchI_J16 = $conn->query("SELECT COUNT(*) as countI_J16 FROM `hands` WHERE `bb` = '{$bbI_J16}' AND `prize` = '{$response0->values[12][0]}' and `poker_type` = 'holdem' and win_date like '{$date}%'");
    $result_searchI_J16 = $searchI_J16->fetchAll(PDO::FETCH_ASSOC);
////////////////////////// K_M16 ///////////////////////////
    $rangeK_M16 = 'Holdem!C16';
    $bbK_M16 = (float)str_replace(',', '.', $sheet[0]['data'][0]['rowData'][15]['values'][5]['note']);
    $searchK_M16 = $conn->query("SELECT COUNT(*) as countK_M16 FROM `hands` WHERE `bb` = '{$bbK_M16}' AND `prize` = '{$response0->values[12][0]}' and `poker_type` = 'holdem' and win_date like '{$date}%'");
    $result_searchK_M16 = $searchK_M16->fetchAll(PDO::FETCH_ASSOC);
////////////////////////// I17 ///////////////////////////
    $rangeI17 = 'Holdem!C17';
    $bbI17 = (float)str_replace(',', '.', $sheet[0]['data'][0]['rowData'][16]['values'][3]['note']);
    $searchI17 = $conn->query("SELECT COUNT(*) as countI17 FROM `hands` WHERE `bb` = '{$bbI17}' AND `prize` = '{$response0->values[13][0]}' and `poker_type` = 'holdem' and win_date like '{$date}%'");
    $result_searchI17 = $searchI17->fetchAll(PDO::FETCH_ASSOC);
////////////////////////// K_M17 ///////////////////////////
    $rangeK_M17 = 'Holdem!C17';
    $bbK_M17 = (float)str_replace(',', '.', $sheet[0]['data'][0]['rowData'][16]['values'][5]['note']);
    $searchK_M17 = $conn->query("SELECT COUNT(*) as countK_M17 FROM `hands` WHERE `bb` = '{$bbK_M17}' AND `prize` = '{$response0->values[13][0]}' and `poker_type` = 'holdem' and win_date like '{$date}%'");
    $result_searchK_M17 = $searchK_M17->fetchAll(PDO::FETCH_ASSOC);
////////////////////////// I_J18 ///////////////////////////
    $rangeI18 = 'Holdem!C18';
    $bbI18 = (float)str_replace(',', '.', $sheet[0]['data'][0]['rowData'][17]['values'][3]['note']);
    $searchI18 = $conn->query("SELECT COUNT(*) as countI18 FROM `hands` WHERE `bb` = '{$bbI18}' AND `prize` = '{$response0->values[14][0]}' and `poker_type` = 'holdem' and win_date like '{$date}%'");
    $result_searchI18 = $searchI18->fetchAll(PDO::FETCH_ASSOC);
////////////////////////// K_L18 ///////////////////////////
    $rangeK_L18 = 'Holdem!C18';
    $bbK_L18 = (float)str_replace(',', '.', $sheet[0]['data'][0]['rowData'][17]['values'][5]['note']);
    $searchK_L18 = $conn->query("SELECT COUNT(*) as countK_L18 FROM `hands` WHERE `bb` = '{$bbK_L18}' AND `prize` = '{$response0->values[14][0]}' and `poker_type` = 'holdem' and win_date like '{$date}%'");
    $result_searchK_L18 = $searchK_L18->fetchAll(PDO::FETCH_ASSOC);
////////////////////////// M18 ///////////////////////////
    $rangeM18 = 'Holdem!C18';
    $bbM18 = (float)str_replace(',', '.', $sheet[0]['data'][0]['rowData'][17]['values'][7]['note']);
    $searchM18 = $conn->query("SELECT COUNT(*) as countM18 FROM `hands` WHERE `bb` = '{$bbM18}' AND `prize` = '{$response0->values[14][0]}' and `poker_type` = 'holdem' and win_date like '{$date}%'");
    $result_searchM18 = $searchM18->fetchAll(PDO::FETCH_ASSOC);
////////////////////////// I_J19 ///////////////////////////
    $rangeI19 = 'Holdem!C19';
    $bbI19 = (float)str_replace(',', '.', $sheet[0]['data'][0]['rowData'][18]['values'][3]['note']);
    $searchI19 = $conn->query("SELECT COUNT(*) as countI19 FROM `hands` WHERE `bb` = '{$bbI19}' AND `prize` = '{$response0->values[15][0]}' and `poker_type` = 'holdem' and win_date like '{$date}%'");
    $result_searchI19 = $searchI19->fetchAll(PDO::FETCH_ASSOC);
////////////////////////// K19 ///////////////////////////
    $rangeK19 = 'Holdem!C19';
    $bbK19 = (float)str_replace(',', '.', $sheet[0]['data'][0]['rowData'][18]['values'][4]['note']);
    $searchK19 = $conn->query("SELECT COUNT(*) as countK19 FROM `hands` WHERE `bb` = '{$bbK19}' AND `prize` = '{$response0->values[15][0]}' and `poker_type` = 'holdem' and win_date like '{$date}%'");
    $result_searchK19 = $searchK19->fetchAll(PDO::FETCH_ASSOC);
////////////////////////// L19 ///////////////////////////
    $rangeL19 = 'Holdem!C19';
    $bbL19 = (float)str_replace(',', '.', $sheet[0]['data'][0]['rowData'][18]['values'][5]['note']);
    $searchL19 = $conn->query("SELECT COUNT(*) as countL19 FROM `hands` WHERE `bb` = '{$bbL19}' AND `prize` = '{$response0->values[15][0]}' and `poker_type` = 'holdem' and win_date like '{$date}%'");
    $result_searchL19 = $searchL19->fetchAll(PDO::FETCH_ASSOC);
////////////////////////// M19 ///////////////////////////
    $rangeM19 = 'Holdem!C19';
    $bbM19 = (float)str_replace(',', '.', $sheet[0]['data'][0]['rowData'][18]['values'][7]['note']);
    $searchM19 = $conn->query("SELECT COUNT(*) as countM19 FROM `hands` WHERE `bb` = '{$bbM19}' AND `prize` = '{$response0->values[15][0]}' and `poker_type` = 'holdem' and win_date like '{$date}%'");
    $result_searchM19 = $searchM19->fetchAll(PDO::FETCH_ASSOC);
////////////////////////// I_J20 ///////////////////////////
    $rangeI_J20 = 'Holdem!C20';
    $bbI_J20 = (float)str_replace(',', '.', $sheet[0]['data'][0]['rowData'][19]['values'][3]['note']);
    $searchI_J20 = $conn->query("SELECT COUNT(*) as countI_J20 FROM `hands` WHERE `bb` = '{$bbI_J20}' AND `prize` = '{$response0->values[16][0]}' and `poker_type` = 'holdem' and win_date like '{$date}%'");
    $result_searchI_J20 = $searchI_J20->fetchAll(PDO::FETCH_ASSOC);
////////////////////////// K_M20 ///////////////////////////
    $rangeK_M20 = 'Holdem!C20';
    $bbK_M20 = (float)str_replace(',', '.', $sheet[0]['data'][0]['rowData'][19]['values'][5]['note']);
    $searchK_M20 = $conn->query("SELECT COUNT(*) as countK_M20 FROM `hands` WHERE `bb` = '{$bbK_M20}' AND `prize` = '{$response0->values[16][0]}' and `poker_type` = 'holdem' and win_date like '{$date}%'");
    $result_searchK_M20 = $searchK_M20->fetchAll(PDO::FETCH_ASSOC);
////////////////////////// I21 ///////////////////////////
    $rangeI21 = 'Holdem!C21';
    $bbI21 = (float)str_replace(',', '.', $sheet[0]['data'][0]['rowData'][20]['values'][3]['note']);
    $searchI21 = $conn->query("SELECT COUNT(*) as countI21 FROM `hands` WHERE `bb` = '{$bbI21}' AND `prize` = '{$response0->values[17][0]}' and `poker_type` = 'holdem' and win_date like '{$date}%'");
    $result_searchI21 = $searchI21->fetchAll(PDO::FETCH_ASSOC);
////////////////////////// I22 ///////////////////////////
    $rangeI22 = 'Holdem!C22';
    $bbI22 = (float)str_replace(',', '.', $sheet[0]['data'][0]['rowData'][21]['values'][3]['note']);
    $searchI22 = $conn->query("SELECT COUNT(*) as countI22 FROM `hands` WHERE `bb` = '{$bbI22}' AND `prize` = '{$response0->values[18][0]}' and `poker_type` = 'holdem' and win_date like '{$date}%'");
    $result_searchI22 = $searchI22->fetchAll(PDO::FETCH_ASSOC);
////////////////////////// I23 ///////////////////////////
    $rangeI23 = 'Holdem!C23';
    $bbI23 = (float)str_replace(',', '.', $sheet[0]['data'][0]['rowData'][22]['values'][3]['note']);
    $searchI23 = $conn->query("SELECT COUNT(*) as countI23 FROM `hands` WHERE `bb` = '{$bbI23}' AND `prize` = '{$response0->values[19][0]}' and `poker_type` = 'holdem' and win_date like '{$date}%'");
    $result_searchI23 = $searchI23->fetchAll(PDO::FETCH_ASSOC);
    echo 'собираем массив из базы'.PHP_EOL;
    $values0 = [
        [],
        [$result_searchC4[0]['countC4']],
        [$result_searchC5[0]['countC5'], '', $result_searchK5[0]['countC5']],
        [$result_searchI_J6[0]['countI_J6'], '', $result_searchK_M6[0]['countK_M6']],
        [$result_searchI_M7[0]['countI_M7']],
        [$result_searchI_K8[0]['countI_K8'], '', $result_searchK_M8[0]['countK_M8']],
        [$result_searchI_J9[0]['countI_J9'], '', $result_searchK_M9[0]['countK_M9']],
        [$result_searchI10[0]['countI10']],
        [$result_searchI11[0]['countI11'], $result_searchJ_K11[0]['countJ_K11'], '', $result_searchL_M11[0]['countL_M11']],
        [$result_searchI12[0]['countI12']],
        [$result_searchI13[0]['countI13'], $result_searchJ13[0]['countJ13'], $result_searchK_M13[0]['countK_M13']],
        [$result_searchI14[0]['countI14'], $result_searchJ14[0]['countJ14'], $result_searchK14[0]['countK14'], $result_searchL14[0]['countL14'], $result_searchM14[0]['countM14']],
        [$result_searchI15[0]['countI15'], $result_searchJ15[0]['countJ15'], $result_searchK_M15[0]['countK_M15']],
        [$result_searchI_J16[0]['countI_J16'], '', $result_searchK_M16[0]['countK_M16']],
        [$result_searchI17[0]['countI17'], '', $result_searchK_M17[0]['countK_M17']],
        [$result_searchI18[0]['countI18'], '', $result_searchK_L18[0]['countK_L18'], '', $result_searchM18[0]['countM18']],
        [$result_searchI19[0]['countI19'], '', $result_searchK19[0]['countK19'], $result_searchL19[0]['countL19'], $result_searchM19[0]['countM19']],
        [$result_searchI_J20[0]['countI_J20'], '', $result_searchK_M20[0]['countK_M20']],
        [$result_searchI21[0]['countI21']],
        [$result_searchI22[0]['countI22']],
        [$result_searchI23[0]['countI23']],
    ];
// Объект - диапазон значений
    $ValueRange = new Google_Service_Sheets_ValueRange();
// Устанавливаем наши данные
    $ValueRange->setValues($values0);
    $service->spreadsheets_values->update($spreadsheetId, "Holdem!I3", $ValueRange, $options);


////////////////////////// Запись в ячейку следующиего диапазона для следующего дня ///////////////////////////
    $rangeI24 = 'Holdem!I24';
    $prizeI24 = $service->spreadsheets_values->get($spreadsheetId, $rangeI24);
    if (empty($prizeI24['values'][0][0])) {
        $cellRange = getLetter("I") . '2';
    } else {
        preg_match('~\D+~m', $prizeI24['values'][0][0], $match2);
        $cellRange = getLetter($match2[0]) . '2';
    }
    $valuesI24 = [[$cellRange],];
    $ValueRangeI24 = new Google_Service_Sheets_ValueRange();
    $ValueRangeI24->setValues($valuesI24);
    //сам запрос записи ниже строка 574

    //запись даты + диапазон
    $valuesJ24 = [[$date . '~' . $cellRange],];
    $ValueRangeJ24 = new Google_Service_Sheets_ValueRange();
    $ValueRangeJ24->setValues($valuesJ24);
    //сам запрос записи ниже строка 575
    echo 'Получение содержимого диапазона ячеек текущего дня'.PHP_EOL;
// Получение содержимого диапазона ячеек текущего дня
    $range = 'Holdem!I4:M23';
    $response = $service->spreadsheets_values->get($spreadsheetId, $range);
// Копируем данные рабочей области чтобы вставить в текущий день
    $newDate = date('d-m-Y', strtotime($date));
    $values = [
        [$newDate],
        [],
        [$response['values'][0][0]],
        [$response['values'][1][0], '', $response['values'][1][2]],
        [$response['values'][2][0], '', $response['values'][2][2]],
        [$response['values'][3][0]],
        [$response['values'][4][0], '', $response['values'][4][2]],
        [$response['values'][5][0], '', $response['values'][5][2]],
        [$response['values'][6][0]],
        [$response['values'][7][0], $response['values'][7][1], '', $response['values'][7][3]],
        [$response['values'][8][0]],
        [$response['values'][9][0], $response['values'][9][1], $response['values'][9][2]],
        [$response['values'][10][0], $response['values'][10][1], $response['values'][10][2], $response['values'][10][3], $response['values'][10][4]],
        [$response['values'][11][0], $response['values'][11][1], $response['values'][11][2]],
        [$response['values'][12][0], '', $response['values'][12][2]],
        [$response['values'][13][0], '', $response['values'][13][2]],
        [$response['values'][14][0], '', $response['values'][14][2], '', $response['values'][14][4]],
        [$response['values'][15][0], '', $response['values'][15][2], $response['values'][15][3], $response['values'][15][4]],
        [$response['values'][16][0], '', $response['values'][16][2]],
        [$response['values'][17][0]],
        [$response['values'][18][0]],
        [$response['values'][19][0]],
    ];

    //проверяем если есть уже такой день, то определяем его диапазон и перезаписываем данные в него
    $range0 = 'Holdem!J24:M24';
    $response0 = $service->spreadsheets_values->get($spreadsheetId, $range0);
    $chekCellRange = false;
    foreach ($response0->values[0] as $value) {
        $item = explode("~", $value);
        if ($item[0] == $date) {
            $cellRange = $item[1];
            $chekCellRange = true;
            break;
        }
    }
    var_dump($cellRange);
    if (!$chekCellRange) {
        //если данных за такой день еще не было - обновляем ячейку с диапазоном
        $service->spreadsheets_values->update($spreadsheetId, 'Holdem!I24', $ValueRangeI24, $options);
        $service->spreadsheets_values->update($spreadsheetId, 'Holdem!J24', $ValueRangeJ24, $options);
    }


// Объект - диапазон значений
    $ValueRange = new Google_Service_Sheets_ValueRange();
    $ValueRange->setValues($values);
    $service->spreadsheets_values->update($spreadsheetId, "Holdem!$cellRange", $ValueRange, $options);
    //ставим дату
    $valuesDate = [[$date],];
    $ValueRangeDate = new Google_Service_Sheets_ValueRange();
    $ValueRangeDate->setValues($valuesDate);
    $service->spreadsheets_values->update($spreadsheetId, "Holdem!$cellRange", $ValueRangeDate, $options);
}

1101

Dram

9 октября 2021, 07:21

Расставил echo чтобы понять до чего хоть скрипт успевает дойти

Оказывается почти сразу спотыкается. Что его там может так напрягать?

1101

Dram

9 октября 2021, 07:49

Вынес этот кусок из цикла, что собственно правильно, об него и спотыкается

$service = new Google_Service_Sheets($client);
$spreadsheet = $service->spreadsheets->get($spreadsheetId, ['includeGridData' => true]);
$sheet = $spreadsheet->getSheets();

Это получение данных из таблицы (всей таблицы). Полез в нее смотреть, а там сегодня ночь заказчик добавил кучу формул (прям на сотни ячеек) и скрыл этот диапазон ну и мне не сказал. Вот скрипт и пытался вобрать все в память.

Можно сказать опять все визуализировал и сам решил ))) Сори )))

284

webinfo

9 октября 2021, 07:57

Dram :
просто адский говнокод

Ну если это полный скрипт, то так и есть. Адский.

$response0 = $service->spreadsheets_values->get($spreadsheetId, $range0);

А где определена переменная $service? Дальше в цикле:

$service = new Google_Service_Sheets($client);

А что такое $client? Вообще ХЗ. С таким подходом в любом месте можно споткнуться.

Мой форум - https://webinfo.guru –Там я всегда на связи

В 2023 году 36,9% всех DDoS-атак пришлось на сферу финансов

Маркетинг для шоколадной фабрики. На 34% выше средний чек

PHP скрипт сжирает всю память, помогите оптимизировать