AnsweredAssumed Answered

SQL Query - bean making weird date comparisons & not finishing

Question asked by Giovanni Marazzi on Jan 26, 2018
Latest reply on Jan 30, 2018 by Giovanni Marazzi

My recently developed scheduler is not only not finishing but when i try comparing bean dates it retrieves weird/wrong results.

Here is the code on GitHub (If you feel like making a pull request) GitHub - Telres/SugarSchedulerActivation: Sugar PHP to run on cron .

Since the code will change ill post it here for making it easy to see.

$sql = "SELECT T3.id, T0.id_c FROM ger01_gerenciamentocliente_cstm T0 INNER JOIN ";
     $sql .= "ger01_gerenciamentocliente T1 ON T0.id_c = T1.id AND T1.deleted = 0 INNER JOIN ";
     $sql .= "ger01_gerenciamentocliente_contacts_c T2 ON T1.id = T2.ger01_gere60bccliente_idb AND T2.deleted = 0 INNER JOIN ";
     $sql .= "contacts T3 ON T3.id = T2.ger01_gerenciamentocliente_contactscontacts_ida AND T3.deleted = 0 INNER JOIN ";
     $sql .= "contacts_cstm T4 ON T4.id_c = T3.id ";
     $sql .= "WHERE T0.lftm_mes_referencia_c IS NOT NULL ORDER BY T3.first_name;";

     $cnt = 0;
     $conn = $GLOBALS['db']->getConnection();
    
     $GLOBALS['log']->fatal('Got Connection');
    
     $stmt = $conn->executeQuery($sql);
    
     $GLOBALS['log']->fatal('Query executed');
    
     while($row = $stmt->fetch()) {    
    
          $gCliente_bean = BeanFactory::retrieveBean('Ger01_GerenciamentoCliente', $row['id_c'], array('disable_row_level_security' => true));
          $contact_bean = BeanFactory::retrieveBean('Contacts', $row['id'], array('disable_row_level_security' => true));

          if(!empty($contact_bean->lftm_data_ativacao_c)) {
               if($gCliente_bean->lftm_mes_referencia_c < $contact_bean->lftm_data_ativacao_c) {
                    $contact_bean->lftm_data_ativacao_c = $gCliente_bean->lftm_mes_referencia_c;
                    $contact_bean->save();
               }
          } else {
               $contact_bean->lftm_data_ativacao_c = $gCliente_bean->lftm_mes_referencia_c;
               $contact_bean->save();
          }
          $GLOBALS['log']->fatal('Processing Client: '. $contact_bean->first_name .' '. $contact_bean->last_name .'| Activation Date: ' . $contact_bean->lftm_data_ativacao_c . '| Reference Month: ' . $gCliente_bean->lftm_mes_referencia_c);
          $cnt++;
     }
    
     $GLOBALS['log']->fatal(''. $cnt .' Registers Saved');

On Attached files there is my SQL tables (if i made any mistake in that) and the Log produced. Why does it take so long to process each Contact?

Outcomes