//end if else mysql or oracle } $query_object1->save(); $query_object2 = new CustomQuery(); $query_object2->name = $mod_strings['LBL_Q'] . "2"; $query_object2->description = $mod_strings['LBL_Q2_DESC']; $query_object2->query_locked = "off"; $query_object2->team_id = 1; if ($query_object2->db->dbType == 'oci8') { } elseif ($query_object1->db->dbType == 'mssql') { $query_object2->custom_query = "SELECT accounts.name AS 'Account Name',\n\tcase MONTH(opportunities.date_closed) when MONTH(GETDATE()) then SUM(opportunities.amount_usdollar) else SUM(0) end '{sc}0{sc}0',\n\tcase MONTH(opportunities.date_closed) when DATEADD(mm,1,GETDATE()) then SUM(opportunities.amount_usdollar) else SUM(0) end '{sc}0{sc}1',\n\tcase MONTH(opportunities.date_closed) when DATEADD(mm,2,GETDATE()) then SUM(opportunities.amount_usdollar) else SUM(0) end '{sc}0{sc}2',\n\tcase MONTH(opportunities.date_closed) when DATEADD(mm,3,GETDATE()) then SUM(opportunities.amount_usdollar) else SUM(0) end '{sc}0{sc}3',\n\tcase MONTH(opportunities.date_closed) when DATEADD(mm,4,GETDATE()) then SUM(opportunities.amount_usdollar) else SUM(0) end '{sc}0{sc}4',\n\tcase MONTH(opportunities.date_closed) when DATEADD(mm,5,GETDATE()) then SUM(opportunities.amount_usdollar) else SUM(0) end '{sc}0{sc}5',\n\tSUM(opportunities.amount_usdollar) AS 'Total Revenue'\n \n\tFROM opportunities\n\tLEFT JOIN accounts_opportunities ON opportunities.id=accounts_opportunities.opportunity_id \n\tLEFT JOIN accounts ON accounts_opportunities.account_id=accounts.id\n\tWHERE opportunities.date_closed <= DATEADD(mm,5,GETDATE()) AND opportunities.date_closed >= GETDATE()\n\tGROUP BY opportunities.date_closed, accounts.id, accounts.name order by accounts.name\n\t"; } elseif ($query_object1->db->dbType == 'mysql') { $query_object2->custom_query = "SELECT accounts.name AS 'Account Name'\n\t,SUM(IF(MONTH(opportunities.date_closed) = MONTH(CURDATE()), opportunities.amount_usdollar,0)) as '{sc}0{sc}0'\n\t,SUM(IF(MONTH(opportunities.date_closed) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH)), opportunities.amount_usdollar,0)) as '{sc}0{sc}1'\n\t,SUM(IF(MONTH(opportunities.date_closed) = MONTH(DATE_ADD(CURDATE(),INTERVAL 2 MONTH)), opportunities.amount_usdollar,0)) as '{sc}0{sc}2'\n\t,SUM(IF(MONTH(opportunities.date_closed) = MONTH(DATE_ADD(CURDATE(),INTERVAL 3 MONTH)), opportunities.amount_usdollar,0)) as '{sc}0{sc}3'\n\t,SUM(IF(MONTH(opportunities.date_closed) = MONTH(DATE_ADD(CURDATE(),INTERVAL 4 MONTH)), opportunities.amount_usdollar,0)) as '{sc}0{sc}4'\n\t,SUM(IF(MONTH(opportunities.date_closed) = MONTH(DATE_ADD(CURDATE(),INTERVAL 5 MONTH)), opportunities.amount_usdollar,0)) as '{sc}0{sc}5'\n\t,SUM(opportunities.amount_usdollar) AS 'Total Revenue'\n\t \n\tFROM opportunities\n\t LEFT JOIN accounts_opportunities \n\tON opportunities.id=accounts_opportunities.opportunity_id \n\tLEFT JOIN accounts \n\tON accounts_opportunities.account_id=accounts.id\n\tWHERE opportunities.date_closed <= DATE_ADD(CURDATE(),INTERVAL 5 MONTH)\n\tAND opportunities.date_closed >= CURDATE()\n\tGROUP BY accounts.id ORDER BY accounts.name"; //end if else mysql or oracle } $query_object2->save(); $query_id1 = $query_object1->id; $query_id2 = $query_object2->id; //Create new Report $report_object = new ReportMaker(); $report_object->name = $mod_strings['LBL_R1']; $report_object->title = $mod_strings['LBL_R1']; $report_object->description = $mod_strings['LBL_R1_DESC']; $report_object->report_align = "center"; $report_object->team_id = 1; $report_object->save(); $report_id = $report_object->id; //Create the data sets for the two custom queries $format_object = new DataSet(); $format_object->name = $mod_strings['LBL_OPP'] . "1"; $format_object->description = $mod_strings['LBL_OPP1_DESC'];