Logic Hooks - Help Please

[drk2009]

I am new on programing and I just know basics things:
I have created a custom modules to keep track of statistical values and I need a logic hook to add the values of field based on a particular date and code:

Table (1) (Statistcs) contain the following fields:
Stat code: code for the statistic
Statistic name: name of the statistic
WeekEnding date: this represents the end day of the working week, 5 days.
Quotas: what is the value that should be achieved for the week.
Values: This is the calculating filed.

Table (2) (Daily Values) is where the daily values get entered, it contain the following fields
This is link to Table (statistic)
Stat Code:
Date: daily
Values: daily value.

I need to create a logic hook that will calculate the total for the week for a particular stats code:
and save the data calculated on the table (Statistics) under field (value) for that statcode and week ending date when I click on the save button.
if the week ends on a thursday (29/04/2010)
I need the logic to start calculting the total from 23/04/2010 to 29/04/2010 (5 working days)for all the values entered for a particula stat code.

I have never done anything like this before and I don't know where to start, I am very new to this, I can create modules and basic field with the module builder. Any help with a code to generate what I want will be very much appreciated or please guide to a location when I can learn how to do it.

I am using Sugar 5.5
running on Windows 7(but I want to build it so it works in linux and windows.
I am not sure what PHP, MYSQL or Apache versions I am running.

[datasponge]

Hi drk2009,

I'll sketch out a possible design. See the Developer's Guide and the Wiki for details of how to write logic hooks.

Say your module is named MYMODULE. You can use a before_save logic hook or after_save. This will be defined in two files:

1. <sugarroot>/custom/modules/MYMODULE/logic_hooks.php with content something like:

 <?php
if(!defined('sugarEntry') || !sugarEntry) die('Not A Valid Entry Point');
 
$hook_version = 1;
$hook_array = Array();
$hook_array['before_save'] = Array();
$hook_array['before_save'][] = Array(1, 'calc_stats','custom/modules/MYMODULE/calc_stats.php','calc_stats', 'calc_stats'); 
?>  

2. <sugarroot>custom/modules/MYMODULE/calc_stats.php (Note that this file is referenced in the last line above and must be the same path. It would have contents like:

 <?php
if(!defined('sugarEntry') || !sugarEntry) die('Not A Valid Entry Point');
 
class calc_stats
{
  function calc_stats(&$bean, $event, $arguments) 
  {
    // ADD YOUR CODE HERE.
  }
}
?>  

The names in the array match the class and function name. You will add code that takes the fields and calculates the stat.

I did not see how you are saving your products you want to count. Let's say your stats is dollars sold. You could enter to total dollars into your module, but then there is nothing to calculate. More likely you will enter each sale and there will be a dollar amount for that sale. Each would be in it's own record. Then you can use the logic hook to calculate the total using a query to get the records.

You can access the database from your logic hook. The $bean variable contains all the fields in this module and lets you get to related modules. There are lots of examples in the forums.

Hopefully that gets you started. Post any specific followup questions.

Phil

[drk2009]

Thank you Phil,

That is a start, I will stat working on it.

Answer to your question:

Each daily value will be manually enterd on the daily statistical module, this is money, quatiti etc. once this
data is being entered for a week I will go to the weekly module and entered the week ending date and click save at which point I want the logic hook to record the information in the weekly value field.

I am asking for some guidance on the MYSQL forums to find out how to write the querie. I have big ideas for the company that I am working for but not eough knowledge to create the code in the time frame that I want to finish this module.

I have looked into getting someone else to finish it for me but I want to learn a little bit and sometimes the principles or ideas that I have are not so easy to explain specially when english is my second lenguage.

Thank you again for your help

[datasponge]

That should be a simple query.

Given the way you describe it, I would probably run the logic hook after each daily stat save and calculate the weekly stat so far. Then after the last daily stat is saved the total will be correct.

Using this method, you don't even need to bother with a query since every daily save is added in right away.

The daily stat and weekly stat modules would have to be related. Then you could get the weekly stat related to this daily stat and save it as $weekly_bean. On the first day's save you would create the weekly stat record. $bean would then be the daily stat record and $weekly_bean would be the weekly stat record.

Use a before_save logic hook so you can see the value before the save and the value after the save using $bean['fetched_row']. When the fetched row is empty this is a new stat - just add it to the weekly stat. When it is not empty, this is a stat update/change, so you'll have to subtract the old stat value then add the new stat value. The code is then simple something like

 if (!empty($bean['fetched_row']->income)
    $weekly_bean->income -= $bean['fetched_row']->income;
$weekly_bean->income += $bean->income; 
  

The quality stat may be more complicated than adding, but the formula is the easy part.

The hardest part is following the relationship to get the related weekly bean. See this thread for code that can get the relationship. It also creates a new relationship, but you should be able to get the idea.

You could also not choose to relate the daily and weekly and use a query instead since the dates are well known. Your choice for which better suits your design.

Phil

[drk2009]

Hello to anyone reading this,
I have created a logic hook with some help from this forum but it is not working. I can save the data but nothings happens and if I change the code I get an error on my web browser.

I have 2 tables:
The first table name is statmain:
Fields: weekenddate= date
value = numerical value
statcode= code to diferenciate each item

The 2nd table is stats1
Fields: weekenddate = date
value = numerical value
startcode= code to diferenciate each item

I am trying to to add a series of values from stats1 where the weekenddate and the startscode from stats1
are the same as statmain field weekenddate and statscode.

When I run my query in phpmyadmin under sql I do get the right result but when I run the logic hook nothings happens.

Can someone please help me complete it
--File-- Logic_hooks.php
<?php
if(!defined('sugarEntry') || !sugarEntry) die('Not A Valid Entry Point');

$hook_version = 1;
$hook_array = Array();
$hook_array['before_save'] = Array();
$hook_array['before_save'][] = Array(1, 'calc_stats2','custom/modules/A2_StatMain/calc_stats2.php','calc_stats2', 'calc_stats2');
?>
-- File - calc_stats2
<?php
if(!defined('sugarEntry') || !sugarEntry) die('Not A Valid Entry Point');

class calc_stats2
{
function calc_stats2(&$bean, $event, $arguments)
{
if ($bean->value !="")
{
$query = "UPDATE a2_statmain Set value=(SELECT SUM(`a2_stats1`.`value`) AS `value`\n"
. "FROM `a2_stats1` WHERE (`a2_stats1`.`weekenddate`='2010/06/03' AND `a2_stats1`.`startcode`='SG1')GROUP BY `a2_stats1`.`weekenddate`)\n"
. "WHERE weekenddate='2010/06/03' AND statcode=''";
$result = $bean->db->query($sql,true);
$row = $db->fetchByAssoc($result);


}
}
}
?>

My second question is:
does anyone know how can I make the above query work with the data that I am entering into my detail edit in sugarcrm, as I have quite a number of statcodes with different weekenddates that I will have to calculate separate. If no one knows that is fine, I guess I will have to create a different logic hook for each one of my codes and weekenddate.

Thank you in advance for any assistance you can give me.
drk2009

[davidboris]

hello,

1. filename should be logic_hooks.php -> not -> Logic_hooks.php
2. die() your query & try to run that in phpmyadmin to see, whether it is good or not.
3. prepare an array -> statecode - weekend dates.

[kenshiro]

Hello,

You may want to take a look at the Logic Hook Programming Interface of dispage Enhanced Studio.

[drk2009]

Thank you for your reply,
I have made the followingchanges and nothing is happening, do I need to make arrays to make it work? or my code is worng?

<?php
if(!defined('sugarEntry') || !sugarEntry) die('Not A Valid Entry Point');

class calc_stats2
{
function calc_stats2(&$bean, $event, $arguments)
{
if ($bean->value !="")
{
$sql = "UPDATE a2_statmain\n"
. "Set Value=(\n"
. "SELECT\n"
. "SUM(`a2_stats1`.`value`) AS `value`\n"
. "FROM `a2_stats1` WHERE (`a2_stats1`.`weekenddate`=\'2010/06/03\' AND `a2_stats1`.`startcode`=\'SG1\')\n"
. "GROUP BY `a2_stats1`.`weekenddate`)\n"
. "WHERE weekenddate= \'2010/06/03\'AND statcode=\'^SG1^\'\n"
. "";
$result = mysql_query($sql) or die ("Error in query: $sql. ".mysql_error());
$result = $bean->db->query($sql,true);
$row = $db->fetchByAssoc($result);


}
}
}
?>

[datasponge]

if ($bean->value !="")

$bean is a complex array definiing all fields for that module. So dereferencing it as $bean->value won't produce anything and this check will likely always fail. Apparently it's passing the syntax check if your page isn't dying, but probably prevents your query from getting called.

What I do to figure out what the bean looks like and to figure out what field I need and how to reference it is dump the contents of the bean to a log file something like:

 $fh = fopen("/var/www/html/PAC.log","w");
fprint($fh,print_r($bean,true));
fclose($fh); 
  

Then after calling my logic hook, I look at the log file in whatever path I set and if the logic hook was actually called, I see the full contents of the bean in all its glory. The bean is actually an array of arrays with many values and you will see 5-20 or more screens worth of data and see the structure of each array at each level. Once I get it working, I comment out or delete all the log file statements. For some hooks you can dump directly to the screen, but this way it's less intrusive and works for all logic hooks.

Check my syntax, as that was off the top of my head, but you should get the idea.

Phil

[drk2009]

WOOOHOOOOOOOOOOOO,

I got it working,
Here is code of my logic hook to all those interested on doing something similar.

calc_stats2.php
<?php
if(!defined('sugarEntry') || !sugarEntry) die('Not A Valid Entry Point');

class calc_stats2
{
function calc_stats2(&$bean, $event, $arguments)

{

$query = "UPDATE a2_statmain\n"
. "Set Value=(\n"
. "SELECT\n"
. "SUM(`a2_stats1`.`value`) AS `value`\n"
. "FROM `a2_stats1` WHERE (`a2_stats1`.`weekenddate`='2010/06/03' AND `a2_stats1`.`startcode`='SG1')\n"
. "GROUP BY `a2_stats1`.`weekenddate`)\n"
. "WHERE weekenddate= '2010/06/03'AND statcode='^SG1^'\n"
. "";
$result = $bean->db->query($query,true);
$row = $bean->db->fetchByAssoc($result);
$value =$row['value'];
$bean->value=$value;

}
}
?>

Thank you for your help,
I also got some date from thefollowing link:
http://cheleguanaco.blogspot.com/200...k-example.html
This is not my blog.