[SOLVED] Logic Hook working fine in localhost not in cpanel live

šŸ‘Øā€šŸ’» Development Help
1

Hello all
I have written logic hook for listing of accounts and it is written in SQL Queries.
Problem is code is working fine in localhost but not for Cpanel live instance.
Here is the code

<?php

    if (!defined('sugarEntry') || !sugarEntry) 
    	die('Not A Valid Entry Point');
    class ListData
    {
    	function listing($bean , $event, $arguments)
    	{
            $db = DBManagerFactory::getInstance();  
            $query ="SELECT * FROM ACCOUNTS";
            $result = $db->query($query, true,"Error reading number of accounts: "); 
            while( $row = $db->fetchByAssoc($result))  
            {  
              $parent_id = $row['id'];
              $query1 = "SELECT t.parent_id,COUNT(t.id) as countR,tc.* FROM `TASKS` t JOIN TASKS_CSTM tc ON t.id=tc.id_c WHERE t.parent_id = '$parent_id'";
             
              $result1 = $db->query($query1, true,"Error reading number of accounts: "); 
              while( $row1 = $db->fetchByAssoc($result1))
              {  
                  $countR = $row1['countR'];
                  if($countR == 2)
                   {
                     $query2 = "UPDATE ACCOUNTS_CSTM SET order_type_c = 'Both' WHERE id_c = '$parent_id'";
                    $result2 = $db->query($query2, true,"Error reading number of accounts: "); 
                   }
                   else if($countR == 1)
                   {
                      $query3 = "SELECT tc.select_order_type_c FROM `TASKS` t JOIN TASKS_CSTM tc ON t.id=tc.id_c WHERE t.parent_id = '$parent_id'";
                      $result3 = $db->query($query3, true,"Error reading number of accounts: ");
                      $row3 = $db->fetchByAssoc($result3);
                      $var = $row3['select_order_type_c'];
                      $query4 = "UPDATE ACCOUNTS_CSTM SET order_type_c = '$var' WHERE id_c = '$parent_id'";
                      $result4 = $db->query($query4, true,"Error reading number of accounts: ");   
                    }
                     else{
                    //  echo "No";    
                     }
                     
              }
             
           }


    	}
    }
2

Please provide further details about this issue. For example;
What errors if any do you receive?
What is your logic hook intend to do?
What is it not doing? How is it ā€œnot workingā€?

3

Sir problem is not in code. code is working fine issue is if add my logic hook which is basically for account listing which is not showing simply loading is showing. But if i remove this hook its working fine.

4

Can you from Cpanel check the permissions that the files has? and ensure it falls in line with the others. Donā€™t think this is the issue but always a possibility.

5

Sir permission are okay because i had already created some hooks for other module and they are working fine. I think may be i am doing some mistake in writing SQL queries Here. Sir Please check code my code once.

6

This the error i am getting
Tue Dec 31 11:27:40 2019 [1010215][-none-][FATAL] Mysqli_query failed.
Tue Dec 31 11:27:40 2019 [1010215][-none-][FATAL] Retrieving record by id users:1 found Query Failed: SELECT users.,users_cstm. FROM users LEFT JOIN users_cstm ON users.id = users_cstm.id_c WHERE users.id = ā€˜1ā€™ AND users.deleted=0 LIMIT 0,1: MySQL error 2006: MySQL server has gone away

7

You just confined that this works locally? Therefore low chance there a low chance something wrong with the queries unless youā€™re developing on a different database structure?

8

Yes definitely its working fine in localhost.

9

Altho that error says otherwiseā€¦

Copy that query and try to run it in phpmyadmin.

SELECT users.,users_cstm. FROM users LEFT JOIN users_cstm ON users.id = users_cstm.id_c WHERE users.id = ā€˜1ā€™ AND users.deleted=0

Thinking it will fail here users.,users_cstm. as that syntax doesnā€™t appear to be correct at all. Let me know the error you get back.

10

This is the error

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'users_cstm. FROM users LEFT JOIN users_cstm ON users.id = users_cstm.id_c WHERE ā€™ at line 1

11

same error is also in localhost but hook is working fine.

12

IGNORE THIS PART OF THE POST

OK sorting some of the formatting of the query it appears to run however I donā€™t understand what you attempting to do here?

SELECT
  users.users_cstm

Select from what i can assume is a custom field created within users table?

FROM
  users
  LEFT JOIN users_cstm ON users.id = users_cstm.id_c

Then left join a table named users_cstm which makes way more sense to me where both ids are the same

WHERE
  users.id = 1
  AND users.deleted = 0

and where user id = 1 and user is not deleted (Is this just not the default admin user?)

ā€œCorrectā€ format;

SELECT users.users_cstm FROM users LEFT JOIN users_cstm ON users.id = users_cstm.id_c WHERE users.id = 1 AND users.deleted = 0

Could you give me an overview of what your attempting to do?

Taking a look at the logic hook, you have me even more confused. Why are you selecting all accounts in the first query? Seems extremely inefficient, Iā€™m thinking the error;

Might be caused by time out or something similar. Could you let me know a complete overview of what youā€™re attempting to do with this logic hook?

Iā€™ll add again that although we always encourage people to learn for themselves, if you donā€™t have the time or the understanding, then I encourage you to find hired help that can assist or provide 1-1 training.

13

Yes So scenario is basically i have to list customer on the basis of order types.
Step 1 : Let say i create a customer and initially no order placed so no customer type .
Step 2: order placed for ā€œLSā€ so update customer type to LS .Again order placed for ā€œLRā€ update customer type to ā€œBothā€ .thatā€™s why i am getting all accounts and fire hook for listing their customer types.
And finally is count == 2 thatā€™s he/she has both service then no Updation will take place .

I have to update all the accounts customer types thatā€™s why i am fetching all the accounts.

Please watch this video for more information.

15

I donā€™t think you are giving us enough information. Donā€™t know if this is on purpose or perhaps you donā€™t have enough experience extending an application. Can I ask what your role/experience is so we can adjust our answers to assist?

Letā€™s start with the differences between your localhost & Cpanel.

  1. The problem is that the ListView page on your Cpanel (lets call this Live) is not loading and the error message is that MySQL has gone away? Yes, No?
  2. Your video shows that you have very little number of accounts (this is on your localhost). How many accounts do you have on the Live instance?
  3. Why do you need to update the Account on the process_record logic hook? That is alot of processing - this logic hook gets fired extremely frequently. I would recommend you use a after_save on the Task instead.
  4. Your code could be syntactically correct but the logic and flow isnā€™t. There is too much processing required for an activity that is infrequently triggered (this is why Iā€™m suggesting use a different logic_hook). You are calling ALL accounts when you only save 1 accountā€¦ that poor database is having to pull in all the accounts in order to update 1 record. It would be more sound to only get the account that actually has been recently modified which would one that had a newly created Task related to it.
16

1: Yes
2: for now 300 accounts.
3: OK
Ok sir i will check throughly.

17

Sir i try to do it using after_save but after_save is not working .
please elaborate how after save works.
when i create tasks then ?

18

Answer this question first :slight_smile:

I could give you a ā€œheres where to lookā€ or I could give you more detail. Depending on the above answer I donā€™t know which one. It would be good to get an understanding of your PHP experience and coding knowledge.

19

I am beginner PHP developer. Can you please help now ?

20

Hey @namitkakusuitecrm

That is good to know, then I feel that I can explain more aspects when providing code that you can utilise for later development. Thank you for sharing that info.

So what Iā€™ve done is whipped a very simple after_save logic based on the same fields that you have described in Accounts & Tasks.

I took your code and alter a few aspects. This should run on Tasks now rather than on accounts and I had to adjust some of the SQL queries due to my installation. Basically what Iā€™m try to state is that this is not an otb solution but this will get you pretty close to what you are aimming to achieve (please test and adjust on your localhost first)

Create a file in custom/Extension/Tasks/Ext/LogicHooks/Update_Accounts_Order_Type.php

<?php

if (!isset($hook_array) || !is_array($hook_array)) {
$hook_array = array();
}
if (!isset($hook_array['after_save']) || !is_array($hook_array['after_save'])) {
$hook_array['after_save'] = array();
}
$hook_array['after_save'][] = array(99, 'Update the related Accounts when Task is saved', 'custom/modules/Tasks/Update_Accounts_Order_Type_Class.php','CustomTaskLogicHooks', 'UpdateRelatedAccount');

The above is a reference to the logic hook. The actual logic hook function is within
custom/modules/Tasks/Update_Accounts_Order_Type_Class.php

<?php

if (!defined('sugarEntry') || !sugarEntry)
    die('Not A Valid Entry Point');
class CustomTaskLogicHooks
{
    function UpdateRelatedAccount($bean, $event, $arguments)
    {

        /**
         * Ok so this will run on after_save
         *
         * Let's check if there is a related account first
         */

        if ($bean->parent_type == "Accounts" && $bean->parent_id != "") {
            // There is.. So now lets see if the select_order_type_c is not empty

            if ($bean->select_order_type_c != "") {
                $db = DBManagerFactory::getInstance();
                $account_id = $bean->parent_id;
                // Great! Now we can use your query to update the account

                // Looping through all the tasks of the parent account
                $query1 = "SELECT t.parent_id,COUNT(t.id) as countR,tc.* FROM tasks t JOIN tasks_cstm tc ON t.id=tc.id_c WHERE t.parent_id = '$account_id' AND t.deleted = 0";
                $result1 = $db->query($query1, true, "Error reading number of accounts: ");
                while ($row1 = $db->fetchByAssoc($result1)) {
                    $countR = $row1['countR'];
                    // Now You could do it this way... BUT what happens if the Account record doesn't have a row in this table yet? Perhaps it's brand new?
                    // It is sometimes better to use the bean and save rather than insert new rows when you don't know if it isn't there...
                    $quick_check = "SELECT id_c FROM accounts_cstm WHERE id_c = '$account_id'";
                    $result_qc = $db->query($quick_check, true, "No customised account: ");
                    $db_way = $db->fetchByAssoc($result_qc);

                    if ($countR == 2) {

                        if($db_way === false){
                            // Then there is no row so lets use the bean instead (which handles this)
                            $account = BeanFactory::getBean("Accounts", $account_id);
                            $account->order_type_c = 'Both';
                            $account->save();
                        }
                        else{
                            $query2 = "UPDATE accounts_cstm SET order_type_c = 'Both' WHERE id_c = '$account_id'";
                            $result2 = $db->query($query2, true, "Error updating related accounts: ");
                        }
                    } else if ($countR == 1) {
                        if($db_way === false){
                            // Then there is no row so lets use the bean instead (which handles this)
                            $account = BeanFactory::getBean("Accounts", $account_id);
                            $account->order_type_c = $bean->select_order_type_c;
                            $account->save();
                        }
                        else{
                            $task_order_type = $bean->select_order_type_c;
                            $query4 = "UPDATE accounts_cstm SET order_type_c = '$task_order_type' WHERE id_c = '$account_id'";
                            $result4 = $db->query($query4, true, "Error updating related accounts: ");
                        }

                    } else {
                        //  echo "No";
                    }

                }


            }

        }
    }
}

I hope Iā€™ve explained alot trhough the code, and as a PHP developer you should have a good grip of the logic.

When you have those two files in place (and removing your previous not working logic_hook) you will need to Repair & Rebuild.

After this, Repair & Rebuild go to custom/modules/Tasks/Ext/ and you should then see a new folder called LogicHooks/logichooks.ext.php

That at least let you know that the application had picked up that you have added a new logic hook.

A point to add, this logic isnā€™t 100% perfect so have a think on how to improve it. Also you may need to have a after_delete logic hook because when you delete a Task then this hook will not fire off.

21

Ok thanks actually that part has been done sir . Thank you but ya one thing is also important that after delete part i will work on that.