dev-chiefworks
289 lines
12 KiB
PHP
289 lines
12 KiB
PHP
<?php
|
|
|
|
//echo "[".date("Y-m-d H:i:s")."] Daily Report job is starting.\n";
|
|
include '../backend.php';
|
|
|
|
$db_host = $savvyext->cfgReadChar('database.host');
|
|
$db_name = $savvyext->cfgReadChar('database.name');
|
|
$db_user = $savvyext->cfgReadChar('database.user');
|
|
$db_pass = $savvyext->cfgReadChar('database.pass');
|
|
$db_port = $savvyext->cfgReadLong('database.port');
|
|
$connstr = "host=${db_host} port=${db_port} dbname=${db_name} user=${db_user} password=${db_pass}";
|
|
$conn = pg_connect($connstr);
|
|
|
|
|
|
$member_id = 1;
|
|
$screen_name = "activity-time-traveled";
|
|
$screen_name = "activity-time-traveled";
|
|
|
|
$query = "SELECT 0 AS card_id, mn.msg, et.category AS action, et.icon ";
|
|
$query .= " FROM members_notification mn ";
|
|
$query .= " inner join email_trigger et on et.e_trigger = mn.trigger_key ";
|
|
$query .= " inner join screen_cards sc on sc.trigger_id = mn.trigger_key ";
|
|
$query .= " where sc.screen_name = '{$screen_name}' and mn.member_id = {$member_id} ";
|
|
$query .= " and sc.status = 1 ";
|
|
$query .= " and expire > now() ";
|
|
$query .= " order by mn.added desc LIMIT 5; ";
|
|
|
|
//echo $query;
|
|
|
|
|
|
echo "<table>";
|
|
|
|
|
|
define('SCREEN_ACTIVITIES_DETAILS', 1);
|
|
define('SCREEN_ACTIVITIES_CATEGORY', 2);
|
|
define('SCREEN_ACTIVITIES_TIME_TRAVEL', 3);
|
|
define('SCREEN_ACTIVITIES_EMISSION', 4);
|
|
define('SCREEN_ACTIVITIES_PERSONALTY', 5);
|
|
|
|
|
|
$re = pg_query($conn, $query);
|
|
if ($re && pg_num_rows($re)) {
|
|
$itm_e = 0;
|
|
while ($fe = pg_fetch_assoc($re)) {
|
|
$message = $fe["msg"];
|
|
$icon = $fe["icon"];
|
|
$action = $fe["action"];
|
|
$card_id = 0;
|
|
|
|
echo "<tr> <td>$message</td> <td>$icon</td> <td>$action</td> <td>$card_id</td> </tr>";
|
|
// $emiss_arr[$gas_date] = array("gas_date" => "$gas_date", "value" => $gas_emission_value, "gas" => $gas_amount);
|
|
}
|
|
}
|
|
|
|
|
|
echo "</table><hr>";
|
|
|
|
switch ($screen_name) {
|
|
case 'activity-emissions':
|
|
$activity_screen = 4;
|
|
break;
|
|
case 'activity-time-traveled':
|
|
$activity_screen = 3;
|
|
break;
|
|
case 'activity-monthly-spent':
|
|
$activity_screen = 2;
|
|
break;
|
|
case 'activity-personality':
|
|
$activity_screen = 5;
|
|
break;
|
|
default:
|
|
$activity_screen = 0;
|
|
break;
|
|
}
|
|
$query = "SELECT 0 AS card_id, mn.msg, et.category AS action, et.icon AS card_icon ";
|
|
$query .= " FROM members_notification mn ";
|
|
$query .= " inner join email_trigger et on et.e_trigger = mn.trigger_key ";
|
|
$query .= " inner join screen_cards sc on sc.trigger_id = mn.trigger_key ";
|
|
$query .= " where sc.screen_name = '{$screen_name}' and mn.member_id = {$member_id} ";
|
|
$query .= " and sc.status = 1 ";
|
|
$query .= " and expire > now() ";
|
|
$query .= " UNION ";
|
|
|
|
|
|
$query .= "SELECT ma.card_id,mc.description AS msg,mc.button1_action AS action,'' AS card_icon "
|
|
. "FROM members_card_assign ma "
|
|
. "LEFT JOIN main_cards mc ON mc.id=ma.card_id "
|
|
. "WHERE mc.activity_screen= {$activity_screen} "
|
|
. "AND ma.subscribe IS NULL "
|
|
. "AND ma.status = 1 AND mc.show_area = 2 "
|
|
. "AND ma.member_id= {$member_id} ";
|
|
|
|
|
|
|
|
|
|
/* card_id | description | button1_action | card_icon
|
|
---------+------------------------------------------------------------------------------------------------+----------------+-----------
|
|
349 | Save money by subscribing to Float's exclusive deals that are targeted to suit your lifestyle. | GOOFFERS |
|
|
(1 row)
|
|
*/
|
|
|
|
|
|
switch ($screen_name) {
|
|
case 'activity-emissions':
|
|
$activity_screen = 4;
|
|
break;
|
|
case 'activity-time-traveled':
|
|
$activity_screen = 3;
|
|
break;
|
|
case 'activity-monthly-spent':
|
|
$activity_screen = 2;
|
|
break;
|
|
case 'activity-personality':
|
|
$activity_screen = 5;
|
|
break;
|
|
default:
|
|
$activity_screen = 0;
|
|
break;
|
|
}
|
|
|
|
$cards = array();
|
|
|
|
//$screen_name = 'activity-emissions';
|
|
/*
|
|
$query = "SELECT mn.msg, mn.expire, mn.added, et.category, et.icon ";
|
|
$query .= " FROM members_notification mn ";
|
|
$query .= " inner join email_trigger et on et.e_trigger = mn.trigger_key ";
|
|
$query .= " inner join screen_cards sc on sc.trigger_id = mn.trigger_key ";
|
|
$query .= " where sc.screen_name = '{$screen_name}' and mn.member_id = {$member_id} ";
|
|
$query .= " and sc.status = 1 ";
|
|
$query .= " and expire > now() ";
|
|
$query .= " order by mn.added desc LIMIT 1; ";
|
|
|
|
|
|
$query = "SELECT 0 AS card_id, mn.msg, et.category AS action, et.icon AS card_icon ,0 AS blog_id, 0 AS assign_id";
|
|
$query .= " FROM members_notification mn ";
|
|
$query .= " inner join email_trigger et on et.e_trigger = mn.trigger_key ";
|
|
$query .= " inner join screen_cards sc on sc.trigger_id = mn.trigger_key ";
|
|
$query .= " where sc.screen_name = '{$screen_name}' and mn.member_id = {$member_id} ";
|
|
$query .= " and sc.status = 1 ";
|
|
$query .= " and expire > now() ";
|
|
$query .= " UNION ";
|
|
|
|
|
|
* TEST QUERY -------------------------------------------
|
|
|
|
$query .= "SELECT ma.card_id,mc.description AS msg,mc.button1_action AS action,'' AS card_icon,mc.blog_id, "
|
|
. " ma.id AS assign_id, mc.*, ca.type AS card_action_type, ca.data AS card_action_data, ma.subscribe, ma.message "
|
|
. " FROM members_card_assign ma "
|
|
. " LEFT JOIN main_cards mc ON mc.id=ma.card_id "
|
|
. " LEFT JOIN card_actions ca ON (ca.id=mc.card_action_id) "
|
|
. "WHERE mc.activity_screen= {$activity_screen} " // AND mc.button1_action == 'GOOFFERS'
|
|
. ""
|
|
. "AND ma.status = 1 AND mc.show_area = 2 "
|
|
. "AND mc.id=355;";
|
|
$query = "";
|
|
// END TEST QUERY ---------------------------------------------
|
|
|
|
|
|
*/
|
|
// do not need the union anymore
|
|
|
|
// Get user country
|
|
$member_country = '';
|
|
echo $mysq_member_country = "SELECT country FROM members WHERE id = {$member_id}";
|
|
$rec = pg_query($conn, $mysq_member_country);
|
|
if ($rec && pg_num_rows($rec)) {
|
|
$rowc = pg_fetch_assoc($rec);
|
|
print_r($rowc);
|
|
$member_country= $rowc["country"];
|
|
}
|
|
|
|
|
|
$query = "";
|
|
|
|
$country_query = " AND mc.card_country = '' ";
|
|
if ($member_country !=''){
|
|
$country_query = " AND mc.card_country IN ('', '$member_country')";
|
|
}
|
|
|
|
$query .= "SELECT ma.card_id,mc.description AS msg,mc.button1_action AS action,'' AS card_icon,mc.blog_id, "
|
|
. " ma.id AS assign_id, mc.*, ca.type AS card_action_type, ca.data AS card_action_data, ma.subscribe, ma.message "
|
|
. " FROM members_card_assign ma "
|
|
. " LEFT JOIN main_cards mc ON mc.id=ma.card_id "
|
|
. " LEFT JOIN card_actions ca ON (ca.id=mc.card_action_id) "
|
|
. " WHERE mc.activity_screen= {$activity_screen} " // AND mc.button1_action == 'GOOFFERS'
|
|
. " AND ma.subscribe IS NULL "
|
|
. " AND ma.status = 1 AND mc.show_area = 2 $country_query "
|
|
. " AND ma.member_id= {$member_id} ORDER BY random() LIMIT 1 ";
|
|
|
|
|
|
echo $query;
|
|
|
|
echo "<hr>[[[[<table>";
|
|
|
|
|
|
$re = pg_query($conn, $query);
|
|
if ($re && pg_num_rows($re)) {
|
|
$itm_e = 0;
|
|
while ($fe = pg_fetch_assoc($re)) {
|
|
$message = $fe["msg"];
|
|
$icon = $fe["card_icon"];
|
|
$action = $fe["action"];
|
|
$card_id = $fe["card_id"];
|
|
;
|
|
|
|
echo "<tr> <td>[$message</td> <td>$icon</td> <td>$action</td> <td>$card_id]</td> </tr>";
|
|
// $emiss_arr[$gas_date] = array("gas_date" => "$gas_date", "value" => $gas_emission_value, "gas" => $gas_amount);
|
|
}
|
|
}
|
|
|
|
|
|
echo "</table>]]]]]";
|
|
|
|
|
|
//SELECT ma.card_id,mc.description,mc.button1_action,'' AS card_icon FROM members_card_assign ma LEFT JOIN main_cards mc ON mc.id=ma.card_id WHERE mc.activity_screen=1 AND ma.subscribe IS NULL AND ma.status = 1 AND ma.member_id=1;
|
|
|
|
|
|
/*
|
|
|
|
SELECT short_title,description,button1_action FROM main_cards;
|
|
*
|
|
|
|
|
|
savvy=> \d main_cards;
|
|
Table "public.main_cards"
|
|
Column | Type | Collation | Nullable | Default
|
|
--------------------+-----------------------------+-----------+----------+----------------------------------------
|
|
id | integer | | not null | nextval('main_cards_id_seq'::regclass)
|
|
name | character varying(100) | | not null |
|
|
title | character varying(100) | | not null |
|
|
short_title | character varying(35) | | not null |
|
|
description | character varying(250) | | not null |
|
|
background_picture | character varying(150) | | not null |
|
|
button1 | character varying(35) | | not null |
|
|
button1_text | character varying(35) | | not null |
|
|
button1_action | character varying(15) | | not null |
|
|
status | integer | | | 1
|
|
added | timestamp without time zone | | | now()
|
|
can_save | integer | | | 0
|
|
template | integer | | | 0
|
|
card_canexpire | integer | | | 0
|
|
card_expiration | timestamp without time zone | | |
|
|
notify | integer | | | 0
|
|
card_country | character varying(2) | | |
|
|
card_action_id | integer | | |
|
|
titleshow | integer | | | 1
|
|
multiple_answer | integer | | | 0
|
|
use_short_title | integer | | | 1
|
|
deleted | timestamp without time zone | | |
|
|
list_order | integer | | | 0
|
|
target_key | character varying(10) | | |
|
|
target_text | character varying(250) | | |
|
|
long_description | text | | |
|
|
card_behavior | character varying(10) | | |
|
|
card_type | character varying(2) | | | ''::character varying
|
|
card_time | character varying(12) | | | ''::character varying
|
|
card_location | integer | | | 0
|
|
card_points | integer | | | 0
|
|
card_reciept | character varying(15) | | |
|
|
card_logic | character varying(15) | | | ''::character varying
|
|
card_order | integer | | | 100
|
|
background_color | character varying(8) | | |
|
|
dynamic_key | character varying(25) | | |
|
|
blog_id | integer | | |
|
|
show_area | integer | | | 0
|
|
activity_screen | integer | | | 0
|
|
Indexes:
|
|
"main_cards_dynamic_key_key" UNIQUE CONSTRAINT, btree (dynamic_key)
|
|
"main_cards_id_key" UNIQUE CONSTRAINT, btree (id)
|
|
Foreign-key constraints:
|
|
"main_cards_blog_id_fkey" FOREIGN KEY (blog_id) REFERENCES blog_app_articles(blog_id)
|
|
"main_cards_card_action_id_fkey" FOREIGN KEY (card_action_id) REFERENCES card_actions(id)
|
|
"main_cards_card_behavior_fkey" FOREIGN KEY (card_behavior) REFERENCES card_behavior(key)
|
|
"main_cards_card_country_fkey" FOREIGN KEY (card_country) REFERENCES country(code)
|
|
Referenced by:
|
|
TABLE "decision_cards" CONSTRAINT "decision_cards_card_id_fkey" FOREIGN KEY (card_id) REFERENCES main_cards(id)
|
|
TABLE "member_saved_cards" CONSTRAINT "member_saved_cards_card_id_fkey" FOREIGN KEY (card_id) REFERENCES main_cards(id)
|
|
TABLE "members_card_assign" CONSTRAINT "members_card_assign_card_id_fkey" FOREIGN KEY (card_id) REFERENCES main_cards(id)
|
|
TABLE "members_cardclicktrack" CONSTRAINT "members_cardclicktrack_card_id_fkey" FOREIGN KEY (card_id) REFERENCES main_cards(id)
|
|
TABLE "members_carpool" CONSTRAINT "members_carpool_card_id_fkey" FOREIGN KEY (card_id) REFERENCES main_cards(id)
|
|
TABLE "members_survey" CONSTRAINT "members_survey_card_id_fkey" FOREIGN KEY (card_id) REFERENCES main_cards(id)
|
|
TABLE "onboarding_survey_cards" CONSTRAINT "onboarding_survey_cards_card_id_fkey" FOREIGN KEY (card_id) REFERENCES main_cards(id)
|
|
TABLE "subscription_summary" CONSTRAINT "subscription_summary_card_id_fkey" FOREIGN KEY (card_id) REFERENCES main_cards(id)
|
|
TABLE "survey_card_country" CONSTRAINT "survey_card_country_card_id_fkey" FOREIGN KEY (card_id) REFERENCES main_cards(id)
|
|
|
|
savvy=>
|
|
|