logic hook

[drk2009]

I am new to progaming but I am still going ahead with a project that

I want to get done.

I have created a set of modules in sugarcrm (PHP) programing and

I need to generate a logic hook that will do the following - before saving.

 

I have 2 tables. Table one contains Values: Numerical values, Date: daily dates and Code: this is the ID for the values being entered.

The second table contains a field for Values, Date: this is for a week to week basis, always a thursday date which is the end of the week for us and a field for Code, this is a code that will identify this values.

 

The code I need to generate has to do the following:

the field in date in table2 will contain a date: thursday date.

I need the code to be able to add all the values in field "value" from "table1" stating from the date stated in the "date" field in "table2" back for a week.

 

If "table1" has the following data stored in mysql:

date1: 27/05/2010 Value1:50 Code1: SA1

date1: 26/05/2010 Value1:20 Code1: SA1

date1: 25/05/2010 Value1:20 Code1: SA1

date1: 24/05/2010 Value1:20 Code1: SA1

date1: 21/05/2010 Value1:20 Code1: SA1

etc

etc

etc

--

date1: 27/05/2010 Value1:50 Code1: SA2

date1: 26/05/2010 Value1:20 Code1: SA2

date1: 25/05/2010 Value1:20 Code1: SA2

date1: 24/05/2010 Value1:20 Code1: SA2

date1: 21/05/2010 Value1:20 Code1: SA2

etc

etc

etc

--

date1: 27/05/2010 Value1:50 Code1: SA3

date1: 26/05/2010 Value1:20 Code1: SA3

date1: 25/05/2010 Value1:20 Code1: SA3

date1: 24/05/2010 Value1:20 Code1: SA3

date1: 21/05/2010 Value1:20 Code1: SA3

etc

etc

etc

--

In table2 I will have the following data:

Date2: 27/05/2010 Code2: SA1,SA2,SA3 Value2:???

 

The mysql code need to do:

Look at all the data stored in "Code2" in "Table2", search in

"table1" for all the "Code1" equal to "Code2" and add all the

values in "values1" starting from date2 dategoing back 5 working days,the logic hook needs to save this into "value2" before saving it.

 

 

[corbin]

I think you're looking for a JOIN and the SUM() function.  Also probably DATE_SUB.

[drk2009]

Hello Everyone, 

 

I have made some progress since my last post.

 

Can someone please give me a hand on getting this logic completed, Please don't give me a riddle. I am asking for help because I don't know how to do it and I have not being able to find any tutorials on this particular subject or there is no explanation of the actual code.

 

I have 2 tables that contain the same fields, the only difference is that table = statmain keep track of weekly values and the table =stats1 keep tracks of the daily values.

 

Field: statscode = (string)

 

        weekenddate = date (date)

 

        value = value (interger)

 

I am working on a logic that will look into the 'stats1' table and add all the values that are contain in table 'stats1' which have the same data entered into the 'weekenddate' and the same 'statcode' as in the table 'statmain'.

 

This logic is meant to calculate the total and enter it into the table 'statsmain' field 'value' before it saves.

 

The following code works fine for one particular date (weekenddate) and one particular code (Statcode) but I need to create a code that will work for any (weekenddate) and (statcode) being entered in the 'statmain' table otherwise I will have to create a new logic every time.

 

FILE: logic_hook.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_stats3.php','calc_stats2', 'calc_stats2');

?>

 

FILE: calc_stats3.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;

}

}

?>

 

Can anyone help, please?