i cant help with the dashboard but here is one of my very detailed temporary tables I had no choice to create because of the logic needed, if creating one can help you. Its an example in php of multiple Joins and adding additional selects and Where statements depending on the logic. I was able to get around temp tables for much of my needs for koolreports but sometimes you have no choice. I had to also do it to insert multiple new rows based on other areas of my database so in a way I had to create a dozen temp tables all in one then send that temp table to koolreports. Then just drop it when done. Hope it helps you some. you are not going to be able to get around creating temp tables at some point depending on how much data and logic is involved. This example covers just about everything anyone could run into in creating a temp table with multi logic paths. creating a temp table in php is so easy and pulling from in with cool reports is so easy if you are hung up doing it in KR do it php. If you have questions about temp tables i could most likely answer if its straight PHP but thats an easier path than where you are going. Just trying to help if i can.
//create temp table to hold Loan_id, customer_id all active customers
@db_exec('DROP TABLE IF EXISTS ' . temp_table_name);
$sql = 'CREATE TABLE ' . temp_table_name . ' (
temp_id INT NOT NULL AUTO_INCREMENT,
loan_id INT NOT NULL DEFAULT 0,
contactlog_id INT NOT NULL DEFAULT 0,
promise_contactlog_id INT NOT NULL DEFAULT 0,
loan_account_number INT NOT NULL DEFAULT 0,
loan_number SMALLINT NOT NULL DEFAULT 0,
full_account_number VARCHAR(10) NOT NULL DEFAULT "",
customer_id INT NOT NULL DEFAULT 0,
customer_last_name VARCHAR(20) NOT NULL DEFAULT "",
customer_first_name VARCHAR(10) NOT NULL DEFAULT "",
loan_delinquent_status INT NOT NULL DEFAULT 0,
loan_date DATE NOT NULL,
loan_balance_amount DECIMAL(11,4) NOT NULL DEFAULT 0,
loan_past_due_amount DECIMAL(9,4) NOT NULL DEFAULT 0,
loan_credit_limit_available_amount DECIMAL(9,4) NOT NULL DEFAULT 0,
last_payment_date DATETIME NOT NULL,
last_promise_date DATETIME NOT NULL,
last_contact_date DATETIME NOT NULL,
last_contact_code VARCHAR(10) NOT NULL DEFAULT "",
last_contact_promise DATETIME NOT NULL,
previous_contact_date DATETIME NOT NULL,
previous_contact_code VARCHAR(10) NOT NULL DEFAULT "",
previous_contact_promise DATETIME NOT NULL,
full_phone VARCHAR(16) NOT NULL DEFAULT "",
renew_calc VARCHAR(20) NOT NULL DEFAULT 0,
PRIMARY KEY (temp_id),
INDEX (loan_id),
INDEX (customer_id),
INDEX (loan_delinquent_status),
INDEX (customer_last_name, customer_first_name),
INDEX (full_account_number)
) ENGINE=MyISAM DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci';
db_exec($sql);
$selects = array();
$selects[] = 't1.loan_id';
$sql = 'INSERT INTO ' .temp_table_name . '
(loan_id,
contactlog_id,
promise_contactlog_id,
loan_account_number,
loan_number,
customer_id,
customer_last_name,
customer_first_name,
loan_delinquent_status,
loan_balance_amount,
loan_date,
loan_past_due_amount,
loan_credit_limit_available_amount)
SELECT %s, -- PLACE HOLDER FOR ADDITIONAL SELECTS BELOW %S
t1.loan_account_number,
t1.loan_number,
t3.customer_id,
t3.customer_last_name,
t3.customer_first_name,
t1.loan_delinquent_status,
t1.loan_balance_amount,
t1.loan_date,
t1.loan_past_due_amount,
t1.loan_credit_limit_available_amount
FROM '.$db_loan_table_name_del_rpt.' t1
%s -- PLACE HOLDER FOR ADDITIONAL JOINS BELOW
WHERE t1.loan_file_type= '.LOAN_FILE_OPEN.'
AND t1.loan_account_number > 0
AND t2.loan2cust_primary > 0';
$joins = array();
$joins[] = 'LEFT JOIN loan2custs t2 ON t1.loan_id = t2.loan2cust_loan_id';
$joins[] = 'LEFT JOIN customers t3 ON t2.loan2cust_customer_id = t3.customer_id';
$selects[] = '0';
// get promise contact log id, may not always be the latest contact
$joins[] = 'LEFT JOIN contactlogs cp ON cp.contactlog_loan_id=t1.loan_id AND
cp.contactlog_promise_date > "1"';
$selects[] = 'MAX(cp.contactlog_id) promise_contactlog_id';
if (count($delinquency_files)) { // restrict to certain customer files?
// this method does not rely on loan file assignment
$sql .= ' AND SUBSTR(t3.customer_last_name,1,1) IN ('.$alpha_files_csv.')';
}
if (count($loan_type_files)) { // restrict to certain loan types
$sql .= ' AND t1.loan_class IN ("'.implode('","', $loan_type_files).'")';
}
if ($search_age == LOAN_AGE_PAST_DUE) {
$sql .= ' AND t1.loan_delinquent_status > ' . LOAN_AGE_CURRENT;
} elseif (($search_age == LOAN_AGE_5 && $aging_method == LOAN_AGING_BEST_RECENCY) ||
($search_age == LOAN_AGE_4 && $aging_method != LOAN_AGING_BEST_RECENCY)) {
// include 4+ monthers (5+)
$sql .= ' AND t1.loan_delinquent_status >= '. $search_age;
} elseif ($search_age != LOAN_AGE_ALL) {
$sql .= ' AND t1.loan_delinquent_status IN ('.$search_age.')';
if ($days_pastdue_min > -1) {
$sql .= ' AND t1.loan_days_past_due >= '.$days_pastdue_min;
}
if ($days_pastdue_max > -1) {
$sql .= ' AND t1.loan_days_past_due <= '.$days_pastdue_max;
}
}
//jsonLog($loan_amount_max);
if (intval($loan_amount_min * 100) > 0) {
$sql .= ' AND t1.loan_amount >= '. (intval($loan_amount_min * 100)/100.00);
}
if (intval($loan_amount_max * 100) > 0) {
$sql .= ' AND t1.loan_amount <= '. (intval($loan_amount_max * 100)/100.00);
}
if (intval($loan_number_min) > 0) {
$sql .= ' AND t1.loan_number >= '. intval($loan_number_min);
}
if (intval($loan_number_max) > 0) {
$sql .= ' AND t1.loan_number <= '. intval($loan_number_max);
}
//$sql .= ' AND t1.loan_account_number=7554'; // debug
$sql .= ' GROUP BY t1.loan_id ORDER BY t3.customer_last_name, t3.customer_first_name';
$sql = sprintf($sql, implode($selects, ','), implode($joins, ' '));
db_exec($sql);
$sql = 'SELECT t.* FROM ' . temp_table_name . ' t';
$results = db_exec($sql);
// all you other setup code
}
// drop the table after protected function setup has completed function
protected function OnRunEnd() {
@db_exec('DROP TABLE ' . temp_table_name);
}