__Proposal_List.php__
<?php
use \koolreport\processes\Sort;
use \koolreport\processes\Group;
use \koolreport\processes\Limit;
use \koolreport\processes\ColumnMeta;
use \koolreport\processes\RemoveColumn;
use \koolreport\processes\ColumnRename;
use \koolreport\pivot\processes\Pivot;
class Proposals_List extends \koolreport\KoolReport
{
use \koolreport\codeigniter\Friendship;
use \koolreport\inputs\Bindable;
function settings()
{
return array(
"dataSources"=>array(
"groupBySelect"=>array(
"class"=>'\koolreport\datasources\ArrayDataSource',
"dataFormat"=>"associate",
"groupBySelect"=>array(
array("assigned","Staff"),
array("city","City"),
array("country","Country"),
array("currency","Currency"),
array("state","State"),
array("status","Status"),
array("zip","Zip"),
)
),
),
);
}
function setup()
{
$today = getCurrentDate();
$currentMonth = substr($today, 0, 8);
if(isset($this->params["dateRange_start"])){
$start = substr($this->params["dateRange_start"], 0, 10);
}else{
$start = $currentMonth."01";
$end = $today;
}
if(isset($this->params["dateRange_end"])){
$end = substr($this->params["dateRange_end"], 0, 10);
}else{
$start = $currentMonth."01";
$end = $today;
}
//setup Query to create the table based on the fields selected
if (isset($this->params["MultiSelectFields"])){
$fielsSelected = setQueryTableFields($this->params["MultiSelectFields"]);
$MultiSelectFields = $this->params["MultiSelectFields"];
}else{
$fielsSelected = "";
$MultiSelectFields = "";
}
if(isset($this->params["MultiSelectCustomFields"]) and !empty($this->params["MultiSelectCustomFields"])){
$fielsSelected .= "tblcustomfieldsvalues.value as ".verifyCustomFieldName($this->params["MultiSelectCustomFields"]).", ";
$MultiSelectCustomFields = $this->params["MultiSelectCustomFields"];
}else{
$MultiSelectCustomFields = "";
}
$expiredQuery = "";
if(isset($this->params["SelectExpired"])){
$expiredQuery = setQueryExpiredFields($this->params["SelectExpired"]);
}
$selected_rel_type = "";
if(isset($this->params["SelectRelType"])){
switch ($this->params["SelectRelType"]) {
case 'All':
$SelectRelType = null;
break;
case 'Client':
$SelectRelType = 'customer';
break;
case 'Lead':
$SelectRelType = 'lead';
break;
default:
$SelectRelType = null;
}
}else{
$SelectRelType = "";
}
$SelectLeadClient = array();
$leadClientID_selected = 0;
$leadClientType_selected = "";
if(isset($this->params["SelectLeadClient"]) and !empty($this->params["SelectLeadClient"])){
$SelectLeadClient = $this->params["SelectLeadClient"];
$leadClientID_selected = $SelectLeadClient[0];
$leadClientType_selectedn = $SelectLeadClient[1];
}
if(isset($this->params["MultiSelectStaff"])){
$MultiSelectStaff = $this->params["MultiSelectStaff"];
}else{
$MultiSelectStaff = "";
}
if(isset($this->params["MultiSelectStatus"])){
$MultiSelectStatus = $this->params["MultiSelectStatus"];
}else{
$MultiSelectStatus = "";
}
if(isset($this->params["SelectCurrency"])){
$SelectCurrency = $this->params["SelectCurrency"];
}else{
$SelectCurrency = "";
}
if(isset($this->params["SelectCity"])){
$SelectCity = $this->params["SelectCity"];
}else{
$SelectCity = "";
}
if(isset($this->params["SelectZip"])){
$SelectZip = $this->params["SelectZip"];
}else{
$SelectZip = "";
}
if(isset($this->params["SelectState"])){
$SelectState = $this->params["SelectState"];
}else{
$SelectState = "";
}
if(isset($this->params["SelectCountry"])){
$SelectCountry = $this->params["SelectCountry"];
}else{
$SelectCountry = "";
}
if(isset($this->params["SelectCustomFieldValue"])){
$SelectCustomFieldValue = $this->params["SelectCustomFieldValue"];
}else{
$SelectCustomFieldValue = "";
}
$start = '2015-01-01';
$end = '2019-01-01';
//$SelectCurrency[0] = 3;
//Table content
$this->src('default')
->query("
SELECT CONCAT(tbloptions.value, LPAD(tblproposals.id, (select value from tbloptions where name = 'number_padding_prefixes'), '0')) as idproposal,
tblproposals.subject,
CONCAT(tblcurrencies.symbol, tblproposals.total) as total,
".$fielsSelected."
(CASE tblproposals.status
WHEN 1 THEN 'Draft'
WHEN 2 THEN 'Sent'
WHEN 3 THEN 'Open'
WHEN 4 THEN 'Revised'
WHEN 5 THEN 'Declined'
WHEN 6 THEN 'Accepted'END) as status,
tblproposals.city as cityC,
tblcountries.short_name as countryC,
tblcurrencies.name as currencyC,
tblproposals.email as emailC,
tblproposals.phone as phoneC,
tblproposals.state as stateC,
tblstaff.firstname as staffC,
tblproposals.zip as zipC,
tblproposals.total as totalC,
tblproposals.subtotal as subtotalC,
tblproposals.total_tax as total_taxC,
tblproposals.adjustment as adjustmentC,
tblproposals.discount_total as discount_totalC,
tblproposals.rel_type as rel_typeC,
IF(rel_type='lead',
(select tblleads.name from tblleads where tblleads.id = tblproposals.rel_id),
IF(rel_type='customer',
(select tblclients.company from tblclients where tblclients.userid = tblproposals.rel_id),'')) as RelNameC,
tblproposals.status as statusId
FROM tblproposals
LEFT JOIN tblstaff ON tblstaff.staffid = tblproposals.assigned
LEFT JOIN tblcurrencies ON tblcurrencies.id = tblproposals.currency
LEFT JOIN tblcountries ON tblcountries.country_id = tblproposals.country
LEFT JOIN tblcustomfieldsvalues on tblcustomfieldsvalues.relid = tblproposals.id and fieldto = 'proposal'
".
((isset($this->params["MultiSelectCustomFields"]) and $this->params["MultiSelectCustomFields"]!="")?"AND tblcustomfieldsvalues.fieldid = :MultiSelectCustomFields":"")
."
JOIN tbloptions on tbloptions.name = 'proposal_number_prefix'
WHERE tblproposals.datecreated > :start
AND tblproposals.datecreated < :end
".
(($MultiSelectStatus !="")?"AND tblproposals.status IN :MultiSelectStatus":"")
."
".
(($MultiSelectStaff !="")?"AND tblproposals.assigned IN :MultiSelectStaff":"")
."
".
((isset($this->params["SelectExpired"]) and $this->params["SelectExpired"]!="")? $expiredQuery :"")
."
".
((isset($this->params["SelectRelType"]) and $this->params["SelectRelType"]!="" and $SelectRelType!="")? "AND tblproposals.rel_type = :SelectRelType" :"")
."
".
((isset($this->params["SelectLeadClient"]) and $this->params["SelectLeadClient"]!="")?"AND tblproposals.rel_type = :SelectRelType AND tblproposals.rel_id = :SelectLeadClient":"")
."
".
(($SelectCurrency !="")?"AND tblproposals.currency IN :SelectCurrency":"")
."
".
((isset($this->params["SelectCity"]) and $this->params["SelectCity"] !="")?"AND tblproposals.city IN :SelectCity":"")
."
".
((isset($this->params["SelectZip"]) and $this->params["SelectZip"]!="")?"AND tblproposals.zip IN :SelectZip":"")
."
".
((isset($this->params["SelectState"]) and $this->params["SelectState"] !="")?"AND tblproposals.state IN :SelectState":"")
."
".
((isset($this->params["SelectCountry"]) and $this->params["SelectCountry"] !="")?"AND tblcountries.short_name IN :SelectCountry":"")
."
".
((isset($this->params["SelectCustomFieldValue"]) and $this->params["SelectCustomFieldValue"]!="")?"AND tblcustomfieldsvalues.value = :SelectCustomFieldValue":"")
."
")
->params(array(
":start"=>$start,
":end"=>$end,
":MultiSelectStatus"=>$MultiSelectStatus,
":MultiSelectStaff"=>$MultiSelectStaff,
":MultiSelectCustomFields"=>$MultiSelectCustomFields,
":SelectRelType"=>$SelectRelType,
":SelectLeadClient"=>$SelectLeadClient,
":SelectLeadClientRelType"=>$leadClientType_selected,
":SelectLeadClientRelID"=>$leadClientID_selected,
":SelectCurrency"=>$SelectCurrency,
":SelectCity"=>$SelectCity,
":SelectZip"=>$SelectZip,
":SelectState"=>$SelectState,
":SelectCountry"=>$SelectCountry,
":SelectCustomFieldValue"=>$SelectCustomFieldValue,
))
->saveTo($source);
//Original select
$source->pipe($this->dataStore("result"));
//verify selected fields of Group By
if(isset($this->params["SelectFieldsGroupBy"]) and !empty($this->params["SelectFieldsGroupBy"])){
$groupByTotal = $this->params["SelectFieldsGroupBy"];
$groupByTotalName = ucwords(str_replace("C", "", str_replace('_', " ", $this->params["SelectFieldsGroupBy"])));
}else{
$groupByTotal = "status";
$groupByTotalName = "Status";
}
//verify GroupBySelection
$createPivotDimensions = createGroupBy($groupByTotal);
//verify selected fields of totals
if(isset($this->params["SelectFieldTotal"]) and !empty($this->params["SelectFieldTotal"])){
$pivotTotal = $this->params["SelectFieldTotal"];
$pivotTotalName = ucwords(str_replace("C", "", str_replace('_', " ", $this->params["SelectFieldTotal"])));
}else{
$pivotTotal = "totalC";
$pivotTotalName = "Total";
}
//Pivot Table Result
$source->pipe(new ColumnRename(array(
$pivotTotal=>$pivotTotalName,
"idproposal"=>"Proposals",
)))
->pipe(new ColumnMeta(array(
$pivotTotalName=>array(
'type' => 'number',
"prefix" => "$",
),
)))
->pipe(new ColumnMeta(array(
'Proposals'=>array(
'type' => 'number',
'decimals'=>2,
),
)))
->pipe(new Pivot(array(
'dimensions'=>array(
'row'=>$createPivotDimensions
),
'aggregates'=>array(
'count'=>'Proposals',
'sum'=>'Total',
'min'=>'Total',
'max'=>'Total',
'avg'=>'Total',
)
)))
->pipe($this->dataStore('result3'));
//charts Result
$source
->pipe(new Group(array(
"by"=>$groupByTotal,
"sum"=>"totalC",
"count"=>"Proposals",
)))
->pipe($this->dataStore('groupedProposalsStatus'));
//Table result
$source
//Remove Columns
->pipe(new RemoveColumn(array("cityC","countryC","emailC","phoneC","stateC","staffC","zipC","totalC","subtotalC","total_taxC","adjustmentC","discount_totalC","statusId","currencyC","RelNameC","rel_typeC")))
//Rename Columns
->pipe(new ColumnRename(array(
"total"=>"Total",
"subject"=>"Subject",
"idproposal"=>"Proposal #",
"status"=>"Status",
"staff"=>"Staff",
"datecreated"=>"Date Created",
"total"=>"Amount",
"total_tax"=>"Total tax",
"adjustment"=>"Adjustment",
"discount_total"=>"Discount",
"subtotal"=>"Subtotal",
"currency"=>"Currency",
"open_till"=>"Open Till",
"rel_type"=>"Rel Type",
"RelName"=>"Rel Name",
"state"=>"State",
"zip"=>"Zip",
"country"=>"Country",
"city"=>"City",
"email"=>"Email",
"phone"=>"Phone",
)))
->pipe($this->dataStore("result2"));
}
}
?>
