Problem to write 2 email plugin when recipient1 is YES/NO on holiday

B

blue-mouse

Member
Hi,
i need to modify my simple email plugin that was sended ,earlier, email to recipient1.

Now i would like to write 2 email plugin:
email(1) is send email to recipient1 when he is NOT on holiday
email(2) should send email to other person (i write sql SELECT for this)= recipient_substituted1_person that has email recipient_substituted1_email in case when recipient1 is on holiday

we have knowledge if recipient1 is on holiday because we have:
Status if holiday are active: YES/NO = TAK/NIE --- `ccjom_cc_lista_zastepstw`.`status_zastepstwa`
START_DATA of holiday --- `ccjom_cc_lista_zastepstw`.`data_zast_od`
END_DATA of holiday --- `ccjom_cc_lista_zastepstw`.`data_zast_do`


Please help with this php scripting and mysql query,

======================================================================================
Fabrik:edit form--Plugins -- email(1)


Email to (eval) ----- (This works)

$db =& JFactory::getDBO();
$recipient1 = '{ccjom_cc_zapotrzebowanie___recipient1_raw}';
var_dump($recipient1);
$query = "SELECT `email` FROM `ccjom_users` WHERE `id` = " . $db->Quote($recipient1);
$db->setQuery($query);
$result = $db->loadResult();
return $result;
----------------------------------------------------------------

User tools:
Condition: ---- email should be send when SELECT return no row/record ----
----- and i have problem with this: `data_zast_od`===START_DATA=<today>=END_DATA===`data_zast_do`
I have a problem to write right condition



SELECT `email_zastep` FROM `ccjom_cc_lista_zastepstw` WHERE `ccjom_cc_lista_zastepstw`.`status_zastepstwa`='TAK' AND `ccjom_cc_lista_zastepstw`.`osoba_zastepowana`=`ccjom_cc_zapotrzebowanie`.`recipient1` AND `ccjom_cc_lista_zastepstw`.`form_id`='20' AND `ccjom_cc_lista_zastepstw`.`data_zast_od`<=today AND `ccjom_cc_lista_zastepstw`.`data_zast_do`>=today;

====================================================================================================

Fabrik:edit form--Plugins -- email(2)


Email to (eval) -----
Email should be send to the email address
$db =& JFactory::getDBO();
$recipient_substituted1_email = '{ccjom_cc_lista_zastepstw___email_zastep}'
var_dump($recipient_substituted1_email);

$query="SELECT `email_zastep` FROM `ccjom_cc_lista_zastepstw` WHERE `ccjom_cc_lista_zastepstw`.`status_zastepstwa`='TAK' AND `ccjom_cc_lista_zastepstw`.`osoba_zastepowana`=`ccjom_cc_zapotrzebowanie`.`recipient1` AND `ccjom_cc_lista_zastepstw`.`form_id`='20' AND `ccjom_cc_lista_zastepstw`.`data_zast_od`<=today AND `ccjom_cc_lista_zastepstw`.`data_zast_do`>=today;
????
$db->setQuery($query);
$result = $db->loadResult();
return $result;
---------------------------------------------------------------------------------------

User tools:
Condition: ---- email should be send when SELECT return any row/record - 1 or more records ----
----- and i have problem with this: `data_zast_od`===START_DATA=<today>=END_DATA===`data_zast_do`
I have a problem to write right condition


SELECT `email_zastep` FROM `ccjom_cc_lista_zastepstw` WHERE `ccjom_cc_lista_zastepstw`.`status_zastepstwa`='TAK' AND `ccjom_cc_lista_zastepstw`.`osoba_zastepowana`=`ccjom_cc_zapotrzebowanie`.`recipient1` AND `ccjom_cc_lista_zastepstw`.`form_id`='20' AND `ccjom_cc_lista_zastepstw`.`data_zast_od`<=today AND `ccjom_cc_lista_zastepstw`.`data_zast_do`>=today;

=============================================================================================================
 
I have a problem to write right: condition for email(1) and (Email to eval + condition) for email(2)

Condition for both check if exist right record (1 or more) in table `ccjom_cc_lista_zastepstw` (list of holidays for workers).
This table is on screen shot

Can i get even small tips how to write this?
 

Attachments

  • Zaznaczenie_216a.png
    Zaznaczenie_216a.png
    112.2 KB · Views: 351
  • Zaznaczenie_218a.png
    Zaznaczenie_218a.png
    104.4 KB · Views: 334
Well, "today" isn't a valid MySQL command. Try using NOW() ...

Code: 
AND `ccjom_cc_lista_zastepstw`.`data_zast_od` <= NOW() AND `ccjom_cc_lista_zastepstw`.`data_zast_do` >= NOW();

Or you could use one of the many other MySQL date/time functions:

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

And maybe use MySQL's "between" function. Like "NOW() BETWEEN start_date AND end_date"

You may need to be careful about time zones, though. NOW() will return the date in whatever your MySQL's TZ is set to, which may or may not be what your J! is set to. So you may need to CONVERT_TZ() on it.

-- hugh
 
thx, but i also have problem to write right conditions:
for email(1)

condition(1): when SELECT (...) no return records
---------------------------
for email(2)

condition(2): when SELECT (...) returns 1 or more records
=======================
how to write conditions?

I need to use 2 x email plugin [ email(1) and email(2) ]
for each email plugin i need to write code into 2 fields:
- email to (eval)
- condition
 
Hmmm,
I try this email(1) ---for case when recipient1 is NOT on holiday

$db =& JFactory::getDBO();
$recipient1 = '{ccjom_cc_zapotrzebowanie___recipient1_raw}';
var_dump($recipient1);
$query1 = "SELECT `email` FROM `ccjom_users` WHERE `id` = " . $db->Quote($recipient1);
$db->setQuery($query1);
$result1 = $db->loadResult();

$query2 = "SELECT `email_zastep` FROM `ccjom_cc_lista_zastepstw` WHERE `ccjom_cc_lista_zastepstw`.`status_zastepstwa`='TAK' AND `ccjom_cc_lista_zastepstw`.`osoba_zastepowana`=`ccjom_cc_zapotrzebowanie`.`recipient1` AND `ccjom_cc_lista_zastepstw`.`form_id`='20' AND `ccjom_cc_lista_zastepstw`.`data_zast_od`<= NOW() AND `ccjom_cc_lista_zastepstw`.`data_zast_do`>=NOW()";
$db->setQuery($query2);
$result2 = $db->loadResult();

if(mysql_num_row($result2) == 0) {
return $result1;}

--------------------------------------------
I try this code for 2 case:
-- recipient1 was NOT on holiday - mail should be sended --- i get an error String(2) "45"
-- recipient1 was (YES) on holiday - mail shouldn't be sendet --- i get an error String(2) "47"
emails were not sended for both cases
----------------------------------------------
Can i get a tip were is an error in my code?
 
"45" it is "id" identifier in table ccjom_users for user: CC Pracownik testowy
"47" it is "id" identifier in table ccjom_users for user: CC Ewa Malinowska
something wrong with: `ccjom_cc_lista_zastepstw`.`osoba_zastepowana`=`ccjom_cc_zapotrzebowanie`.`recipient1` where `osoba_zastepowana` and `recipient1` are "name" not "id" for users from Joomla table with users: ccjom_users
but i am having problem how to change this?
 
Can someone have a look at my script in my post #5 - i am not sure if i have made any error or is something wrong with fabrik?
I very need this option,
 
:( Hi,

Code: 
$db =& JFactory::getDBO();
$recipient1 = '{ccjom_cc_zapotrzebowanie___recipient1_raw}';
var_dump($recipient1);
$query1 = "SELECT `email` FROM `ccjom_users` WHERE `id` = " . $db->Quote($recipient1);
$db->setQuery($query1);
$result1 = $db->loadResult();
 
$query2 = "SELECT `email_zastep` FROM
`ccjom_cc_lista_zastepstw` WHERE
`ccjom_cc_lista_zastepstw`.`status_zastepstwa`='TAK' AND  `ccjom_cc_lista_zastepstw`.`form_id`='20'";
$db2->setQuery($query2);
$result2 = $db2->loadResult();
 
if(mysql_num_row($result2) == 0) {
return $result1;}

I get an error
Code: 
string(2) "45"

Can someone check this simple code (for advanced users in php) - and write what is wrong with this.

I try alone to write what i need but have problem, how to write in php when having $query1 and $query2 --- is OK with $db1 and $db2? and loadResult not loadResult1 and loadResult2 ?

This code is simplified "step1" - please help with this...
----------------------------
Idea:
when query2 returns any result (any email) then script should return nothing
when query2 returns no records (no email) (list of records is empty) then script should return $result1
 
Hi,

I try in this way also:
Email to (eval)
Code: 
$db =& JFactory::getDBO();
$recipient1 = '{ccjom_cc_zapotrzebowanie___recipient1_raw}';
var_dump($recipient1);
$query1 = "SELECT `email` FROM `ccjom_users` WHERE `id` = " . $db->Quote($recipient1);
$db->setQuery($query1);
$result1 = $db->loadResult();
return $result1;



Condition:
Code: 
$query2 = "SELECT `email_zastep` FROM
`ccjom_cc_lista_zastepstw` WHERE
`ccjom_cc_lista_zastepstw`.`status_zastepstwa`='TAK' AND  `ccjom_cc_lista_zastepstw`.`form_id`='23'";
$db2->setQuery($query2);
$result2 = $db2->loadResult();
 
if(mysql_num_rows($result2) == 0) {
return true;}

there is no error message - but is "white screen" and email is not send.

Can i get a tip how to write right condition- so i do this first time in fabrik?

-------------------------------------
when no condition code works, what is wrong with my condition :( ?
 
loadResult will load the first column from the first record that your query produces.
If you want to do a COUNT of then number of records then this would be better:

Code: 
$query2 = "SELECT COUNT(*) FROM
`ccjom_cc_lista_zastepstw` WHERE
`ccjom_cc_lista_zastepstw`.`status_zastepstwa`='TAK' AND  `ccjom_cc_lista_zastepstw`.`form_id`='23'";
$db2->setQuery($query2);
$result2 = $db2->loadResult();
 
if ($result2 == 0) {
return true;
}
 
But this condition doesn't work also..............
i try 2 way:
1. Code only in: .....email to (eval)
PHP: 
$db =& JFactory::getDBO();
$recipient1 = '{ccjom_cc_zapotrzebowanie___recipient1_raw}';
var_dump($recipient1);
$query1 = "SELECT `email` FROM `ccjom_users` WHERE `id` = " . $db->Quote($recipient1);
$db->setQuery($query1);
$result1 = $db->loadResult();
$query2 = "SELECT COUNT(*) FROM
`ccjom_cc_lista_zastepstw` WHERE
`form_id`='23'";
$db2->setQuery($query2);
$result2 = $db2->loadResult();
 
if ($result2 == 0) {
return $result1;
}
i get an error:
string(2) "48"
no email is sended

2. code in: .... email to (eval)
PHP: 
$db =& JFactory::getDBO();
$recipient1 = '{ccjom_cc_zapotrzebowanie___recipient1_raw}';
var_dump($recipient1);
$query1 = "SELECT `email` FROM `ccjom_users` WHERE `id` = " . $db->Quote($recipient1);
$db->setQuery($query1);
$result1 = $db->loadResult();
return $result1;

and in "condition" field
PHP: 
$query2 = "SELECT COUNT(*) FROM
 
`ccjom_cc_lista_zastepstw` WHERE
 
`form_id`='23'";
 
$db2->setQuery($query2);
 
$result2 = $db2->loadResult();
 
 
 
if ($result2 == 0) {
 
return true;
 
}
I get an error: blank-white-page in my browser
email is not sended

Nothing works.....

Code for SELECT ($query2) is most simplified as possible - i need to get working "condition", :(
 

Attachments

  • Zaznaczenie_241a.png
    Zaznaczenie_241a.png
    39.4 KB · Views: 289
I am not advanced in fabrik and need : sending email from form that depends on records in other table.

I read in wiki-fabrik that condition should be:
without" if", like:
PHP: 
$result2==0

but i don't know what really can i write in "condition" area in plugin "email" for fabrik form?

I even don't know if plugin "email" for fabrik form is the good solution for my issue:
" sending email from form that depends on records in other table",

Need any advice and tips in this,
 
friendly bump,

I need to find a way:
to "send email from form when SELECT query to other table meet some conditions" .

It is very important issue for my aplication in Fabrik.

Can someone tell me if this is possible in fabrik or if this will be possible in fabrik?
 
Where is $db2 coming from in your code in your first example? You get $db from JFactory::getDBO(), but then you try and use a $db2 ...?

The code looks OK otherwise, but in each eval'ed fragment, you need to do the ...

PHP: 
$db = JFactory::getDBO();

... and then use $db, not $db2, for things ike $db->setQuery(), etc.

I suspect the blank screen is because PHp is erroring out, because you are trying to use a database object,, $db2, which doesn't exist because you haven't created it.

-- hugh
 
Thx cheesegrits, but i can't write condition for email (eval), condition does't work for me:

I put in Email to (eval) code (this is OK):
PHP: 
$db =& JFactory::getDBO();
$recipient1 = '{ccjom_cc_zapotrzebowanie___recipient1_raw}';
var_dump($recipient1);
$query1 = "SELECT `email` FROM `ccjom_users` WHERE `id` = " . $db->Quote($recipient1);
$db->setQuery($query1);
$result1 = $db->loadResult();
return $result1;

But i don't know what to write in field "Condition", i try:
PHP: 
$db =& JFactory::getDBO();
 
$query2 = "SELECT COUNT(*) FROM
`ccjom_cc_lista_zastepstw` WHERE
`ccjom_cc_lista_zastepstw`.`status_zastepstwa`='TAK' AND  `ccjom_cc_lista_zastepstw`.`form_id`='23'";
$db->setQuery($query2);
$result2 = $db->loadResult();
return $result2;

I try to write condition:
WHEN no records in `ccjom_cc_lista_zastepstw` with `status_zastepstwa`='TAK and `form_id`='23 THEN send email to recipient1 otherwise not send email.

Can you help me to write right condition?

So, this doesn,t send any email to recipient1 - and should send :(
=====================================================================================
i try also in field "Condition" code:
PHP: 
$db =& JFactory::getDBO();
$query2 = "SELECT COUNT(*) FROM
`ccjom_cc_lista_zastepstw` WHERE
`ccjom_cc_lista_zastepstw`.`status_zastepstwa`='TAK' AND  `ccjom_cc_lista_zastepstw`.`form_id`='20'";
$db->setQuery($query2);
$result2 = $db->loadResult();
if ($result2 > 0){
return false;}
this is also wrong, becouse it send email always.
I try to write condition:
WHEN are records in table `ccjom_cc_lista_zastepstw` with with `status_zastepstwa`='TAK and `form_id`='20' THEN email to recipient1 shouldn't be send.
(WHEN no records.... email should be send)
 

Attachments

  • Zaznaczenie_246a.png
    Zaznaczenie_246a.png
    49.2 KB · Views: 308
Hi, there is something wrong with my condition,
when have:
PHP: 
$db =& JFactory::getDBO();
$query2 = "SELECT COUNT(*) FROM
`ccjom_cc_lista_zastepstw` WHERE
`status_zastepstwa`='TAK' AND  `form_id`='20'";
$db->setQuery($query2);
$result2 = $db->loadResult();
return $result2 == 0;
always send email - not important on records in table `ccjom_cc_lista_zastepstw`

when have:

PHP: 
$db =& JFactory::getDBO();
$query2 = "SELECT COUNT(*) FROM
`ccjom_cc_lista_zastepstw` WHERE
`status_zastepstwa`='TAK' AND  `form_id`='20'";
$db->setQuery($query2);
$result2 = $db->loadResult();
return $result2 > 0;

newer send email - not important what is in `ccjom_cc_lista_zastepstw`

??

This means condition "SELECT COUNT...." doesn't work at all, but why?

There is a tip to "Condition":
Optional PHP code. If code returns false, emails will not be sent. The form data is contained within the variable $data.
The form's original data is available as an array called $origData.

There is no manual at WIKI how to write "Condition" when need use SELECT query,
 
Can you try giving the count a field name?

PHP: 
$db = JFactory::getDBO();
$query2 = "SELECT COUNT(*) AS tot FROM
`ccjom_cc_lista_zastepstw` WHERE
`status_zastepstwa`='TAK' AND  `form_id`='20'";
$db->setQuery($query2);
$result2 = (int) $db->loadResult();
return $result2 == 0;

I seem to recall running in to this issue before, where if you don't give the COUNT(*) a field name, loadResult() doesn't correctly return the result.

-- hugh
 
Thx, small step to solution

When try the same (condition) for:
`form_id1` -----> {ccjom_cc_lista_zastepstw___form_id1} ----> that is type "field" ---- it works
but i need the same for:
`form_id` -----> {ccjom_cc_lista_zastepstw___form_id} ----> that is type "cascadingdropdown" ---- this doesn't work - this always send email
--------------------------------------
In other way, this code works: (condition)
PHP: 
$db =& JFactory::getDBO();
$query2 = "SELECT COUNT(`email_zastep`) AS tot FROM
`ccjom_cc_lista_zastepstw` WHERE `form_id1`='23'";
$db->setQuery($query2);
$result2 = $db->loadResult();
return $result2 == 0;
I have no records in table `ccjom_cc_lista_zastepstw` with `form_id1`='23' ---- so it send email

when testing this code for `form_id1`='20'
PHP: 
$db =& JFactory::getDBO();
$query2 = "SELECT COUNT(`email_zastep`) AS tot FROM
`ccjom_cc_lista_zastepstw` WHERE `form_id1`='20'";
$db->setQuery($query2);
$result2 = $db->loadResult();
return $result2 == 0;
I have records in table `ccjom_cc_lista_zastepstw` with `form_id1`='20' ---- so it doesn't send email
-------------------------------------------------------------------------------------

but i need use `form_id` that is type "cascadingdropdown" - how to modify SELECT ?

and include in my condition SELECT more fields
 
i have:
form_id ------------------- cascadingdropdown
status_zastepstwa1 ----- dropdown
data_zast_od1 ------------ date
data_zast_do1 ------------ date
{ccjom_cc_lista_zastepstw___osoba_zastepowana} ------- databasejoin to table ccjom_users
{ccjom_cc_zapotrzebowanie___recipient1} ---------- ------- databasejoin to table ccjom_users

How to write right condition when fields have those type?

I try also:
AND `data_zast_od1`<= CURDATE() AND `data_zast_do1`>=CURDATE() - this also doesn't work - always send email (also then when is record in table `ccjom_cc_lista_zastepstw`)

I try also simplificied condition:
PHP: 
$db =& JFactory::getDBO();
$recipient1 = '{ccjom_cc_zapotrzebowanie___recipient1_raw}';
var_dump($recipient1);
$query2 = "SELECT COUNT(`email_zastep`) AS tot FROM
`ccjom_cc_lista_zastepstw` WHERE `form_id1`='20' AND `status_zastepstwa1`='TAK' AND  `osoba_zastepowana` = " . $db->Quote($recipient1);
$db->setQuery($query2);
$result2 = $db->loadResult();
return $result2 == 0;

and this works,
=======================================================================================
So, need to modify this:
PHP: 
$db =& JFactory::getDBO();
$recipient1 = '{ccjom_cc_zapotrzebowanie___recipient1_raw}';
var_dump($recipient1);
 
$query2 = "SELECT COUNT(`email_zastep`) AS tot FROM
`ccjom_cc_lista_zastepstw` WHERE `form_id`='20' AND `status_zastepstwa1`='TAK'
AND  `data_zast_od1`<= NOW() AND `data_zast_do1`>=NOW() AND  `osoba_zastepowana` = " . $db->Quote($recipient1);
$db->setQuery($query2);
$result2 = $db->loadResult();
return $result2 == 0;
so, in this moment:
form_id ------------------- cascadingdropdown --- works WRONG
data_zast_od1 ------------ date --------------------- works WRONG
data_zast_do1 ------------ date --------------------- works WRONG,

any tip, very please...
 

Attachments

  • Zaznaczenie_248.png
    Zaznaczenie_248.png
    30.4 KB · Views: 296
  • Zaznaczenie_249.png
    Zaznaczenie_249.png
    30.9 KB · Views: 307
  • Zaznaczenie_250b.png
    Zaznaczenie_250b.png
    93.9 KB · Views: 310
It is something wrong with my code or it is an error in fabrik?

When use code with form_id (cascadingdropdown) email is newer send.
When use code with data_zast_od1 and data_zast_do1 (date) then email is send always.
 
You must log in or register to reply here.

Useful links

  • Create a Fabrik Account (Downloads)
  • Create a Forum Account
  • Fabrik Wiki
  • Fabrik 4 Changelog /Install /Upgrade
    • We are in need of some funding.
    More details.

    Thank you.

    Members online

    No members online now.
    Total: 114 (members: 1, guests: 113)
    Back
    Top