gistfile1.txt

703:         $query = "SELECT b.id FROM ".$bean->getTableName()." b LEFT JOIN aod_indexevent ie ON (ie.record_id = b.id AND ie.record_module = '".$beanModule."') WHERE b.deleted = 0 AND (ie.id IS NULL OR ie.date_modified < b.date_modified) ORDER BY b.date_modified ASC";



148:             $query = "SELECT filename name FROM document_revisions INNER JOIN documents ON documents.id = document_revisions.document_id ";




210:         $q2="select *  from email_addr_bean_rel eabr WHERE eabr.bean_id = '".$this->db->quote($id)."' AND eabr.bean_module = '".$this->db->quote($module)."' and eabr.deleted=0";


343:         $q = "SELECT bean_id FROM email_addr_bean_rel eabr
344                  JOIN email_addresses ea ON (eabr.email_address_id = ea.id)
345                  WHERE bean_module = '$module' AND ea.email_address_caps = '$email' AND eabr.deleted=0";


332: 		$q = "SELECT emails.id , emails.name, emails.date_sent, emails.status, emails.type, emails.flagged, emails.reply_to_status, emails_text.from_addr, emails_text.to_addrs, 'Emails' polymorphic_module FROM emails" .
333  								   " JOIN emails_text on emails.id = emails_text.email_id
334                                     WHERE (type = '{$type}' OR status = '{$status}') AND assigned_user_id = '{$current_user->id}' AND emails.deleted=0";




843: 		$checkEmailQuery = "SELECT count(*) c FROM folders_rel where polymorphic_module = 'Emails' and folder_id = '{$id}' and deleted = 0";


59:             $sql = "SELECT pg.id, pg.group_id FROM aos_products_quotes pg LEFT JOIN aos_line_item_groups lig ON pg.group_id = lig.id WHERE pg.parent_type = '" . $focus->object_name . "' AND pg.parent_id = '" . $focus->id . "' AND pg.deleted = 0 ORDER BY lig.number ASC, pg.number ASC";



56:                 $this->number = $this->db->getOne("SELECT MAX(CAST(number as INT))+1 FROM aos_quotes");



$opp_query1  = "select camp.name, count(*) opp_count,SUM(opp.amount) as Revenue, SUM(camp.actual_cost) as Investment, 
ROUND((SUM(opp.amount) - SUM(camp.actual_cost))/(SUM(camp.actual_cost)), 2)*100 as ROI";	           
$opp_query1 .= " from opportunities opp";
$opp_query1 .= " right join campaigns camp on camp.id = opp.campaign_id";
$opp_query1 .= " where opp.sales_stage = 'Closed Won' and camp.id='$campaign_id'";
$opp_query1 .= " and opp.deleted=0";                                  
$opp_query1 .= " group by camp.name";


$query = "SELECT lead_source,sum(amount_usdollar/1000) as total,count(*) as opp_count FROM opportunities ";
$query .= "WHERE ".$where." AND opportunities.deleted=0 ";
$query .= "GROUP BY lead_source ORDER BY total DESC";



4851: 	    $query = "SELECT count(*) as c FROM inbound_email WHERE deleted=0 AND is_personal='1' AND group_id='{$user->id}' AND status='Active'";




$query = "SELECT meetings.id, meetings_cstm.id_c FROM meetings " .
" LEFT JOIN meetings_cstm ON meetings.id = meetings_cstm.id_c " .
" WHERE meetings.deleted = 0 AND " .
" meetings.parent_type = '" . $this->db->quote($bean->module_name) . "' AND " .
" meetings.parent_id = '" . $this->db->quote($bean->id) . "'";






$contacts_query = "SELECT c.id AS id, 'Contacts' AS related_type, '' AS \"name\", c.first_name AS first_name, c.last_name AS last_name,c.title AS title, c.salutation AS salutation,
c.primary_address_street AS primary_address_street,c.primary_address_city AS primary_address_city, c.primary_address_state AS primary_address_state,  c.primary_address_postalcode AS primary_address_postalcode, c.primary_address_country AS primary_address_country,
a.name AS account_name,
ea.email_address AS email_address, ea.invalid_email AS invalid_email, ea.opt_out AS opt_out, ea.deleted AS ea_deleted, ear.deleted AS ear_deleted, ear.primary_address AS primary_address,
c.do_not_call AS do_not_call, c.phone_fax AS phone_fax, c.phone_other AS phone_other, c.phone_home AS phone_home, c.phone_mobile AS phone_mobile, c.phone_work AS phone_work
".(count($members['Contacts']['fields']) ? ', ' : '') . implode(', ', $members['Contacts']['fields'])."
FROM prospect_lists_prospects plp
INNER JOIN contacts c ON plp.related_id=c.id LEFT JOIN accounts_contacts ac ON ac.contact_id=c.id
LEFT JOIN accounts a ON ac.account_id=a.id AND ac.deleted=0
".($members['Contacts']['has_custom_fields'] ? 'LEFT join contacts_cstm ON c.id = contacts_cstm.id_c' : '')."
LEFT JOIN email_addr_bean_rel ear ON ear.bean_id=c.id AND ear.deleted=0
LEFT JOIN email_addresses ea ON ear.email_address_id=ea.id
WHERE plp.prospect_list_id = $record_id AND plp.deleted=0
AND c.deleted=0
AND (ear.deleted=0 OR ear.deleted IS NULL)";