modules/ProspectLists/ProspectList.php

   1 <?php
   2 if(!defined('sugarEntry') || !sugarEntry) die('Not A Valid Entry Point');
   3 /*********************************************************************************
   4  * SugarCRM Community Edition is a customer relationship management program developed by
   5  * SugarCRM, Inc. Copyright (C) 2004-2011 SugarCRM Inc.
   6  *
   7  * This program is free software; you can redistribute it and/or modify it under
   8  * the terms of the GNU Affero General Public License version 3 as published by the
   9  * Free Software Foundation with the addition of the following permission added
  10  * to Section 15 as permitted in Section 7(a): FOR ANY PART OF THE COVERED WORK
  11  * IN WHICH THE COPYRIGHT IS OWNED BY SUGARCRM, SUGARCRM DISCLAIMS THE WARRANTY
  12  * OF NON INFRINGEMENT OF THIRD PARTY RIGHTS.
  13  *
  14  * This program is distributed in the hope that it will be useful, but WITHOUT
  15  * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
  16  * FOR A PARTICULAR PURPOSE.  See the GNU Affero General Public License for more
  17  * details.
  18  *
  19  * You should have received a copy of the GNU Affero General Public License along with
  20  * this program; if not, see http://www.gnu.org/licenses or write to the Free
  21  * Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
  22  * 02110-1301 USA.
  23  *
  24  * You can contact SugarCRM, Inc. headquarters at 10050 North Wolfe Road,
  25  * SW2-130, Cupertino, CA 95014, USA. or at email address contact@sugarcrm.com.
  26  *
  27  * The interactive user interfaces in modified source and object code versions
  28  * of this program must display Appropriate Legal Notices, as required under
  29  * Section 5 of the GNU Affero General Public License version 3.
  30  *
  31  * In accordance with Section 7(b) of the GNU Affero General Public License version 3,
  32  * these Appropriate Legal Notices must retain the display of the "Powered by
  33  * SugarCRM" logo. If the display of the logo is not reasonably feasible for
  34  * technical reasons, the Appropriate Legal Notices must display the words
  35  * "Powered by SugarCRM".
  36  ********************************************************************************/
  37
  38 /*********************************************************************************
  39
  40  * Description:
  41  ********************************************************************************/
  42
  43
  44
  45
  46
  47
  48
  49 class ProspectList extends SugarBean {
  50         var $field_name_map;
  51
  52         // Stored fields
  53         var $id;
  54         var $date_entered;
  55         var $date_modified;
  56         var $modified_user_id;
  57         var $assigned_user_id;
  58         var $created_by;
  59         var $created_by_name;
  60         var $modified_by_name;
  61         var $list_type;
  62         var $domain_name;
  63
  64         var $name;
  65         var $description;
  66
  67         // These are related
  68         var $assigned_user_name;
  69         var $prospect_id;
  70         var $contact_id;
  71         var $lead_id;
  72
  73         // module name definitions and table relations
  74         var $table_name = "prospect_lists";
  75         var $module_dir = 'ProspectLists';
  76         var $rel_prospects_table = "prospect_lists_prospects";
  77         var $object_name = "ProspectList";
  78
  79         // This is used to retrieve related fields from form posts.
  80         var $additional_column_fields = array(
  81                 'assigned_user_name', 'assigned_user_id', 'campaign_id',
  82         );
  83         var $relationship_fields = array(
  84                 'campaign_id'=>'campaigns',
  85                 'prospect_list_prospects' => 'prospects',
  86         );
  87
  88     var $entry_count;
  89
  90         function ProspectList() {
  91                 global $sugar_config;
  92                 parent::SugarBean();
  93
  94         }
  95
  96         var $new_schema = true;
  97
  98         function get_summary_text()
  99         {
 100                 return "$this->name";
 101         }
 102
 103         function create_list_query($order_by, $where, $show_deleted = 0)
 104         {
 105                 $custom_join = $this->custom_fields->getJOIN();
 106
 107                 $query = "SELECT ";
 108                 $query .= "users.user_name as assigned_user_name, ";
 109                 $query .= "prospect_lists.*";
 110
 111                 if($custom_join){
 112                         $query .= $custom_join['select'];
 113                 }
 114                 $query .= " FROM prospect_lists ";
 115
 116                 $query .= "LEFT JOIN users
 117                                         ON prospect_lists.assigned_user_id=users.id ";
 118
 119                 if($custom_join){
 120                         $query .= $custom_join['join'];
 121                 }
 122
 123                         $where_auto = '1=1';
 124                                 if($show_deleted == 0){
 125                         $where_auto = "$this->table_name.deleted=0";
 126                                 }else if($show_deleted == 1){
 127                         $where_auto = "$this->table_name.deleted=1";
 128                                 }
 129
 130                 if($where != "")
 131                         $query .= "where $where AND ".$where_auto;
 132                 else
 133                         $query .= "where ".$where_auto;
 134
 135                 if($order_by != "")
 136                         $query .= " ORDER BY $order_by";
 137                 else
 138                         $query .= " ORDER BY prospect_lists.name";
 139
 140                 return $query;
 141         }
 142
 143
 144         function create_export_query($order_by, $where)
 145         {
 146
 147                                 $query = "SELECT
 148                                 prospect_lists.*,
 149                                 users.user_name as assigned_user_name ";
 150                                     $query .= "FROM prospect_lists ";
 151                 $query .=                               "LEFT JOIN users
 152                                 ON prospect_lists.assigned_user_id=users.id ";
 153
 154                 $where_auto = " prospect_lists.deleted=0";
 155
 156         if($where != "")
 157                 $query .= " WHERE $where AND ".$where_auto;
 158         else
 159                 $query .= " WHERE ".$where_auto;
 160
 161         if($order_by != "")
 162                 $query .= " ORDER BY $order_by";
 163         else
 164                 $query .= " ORDER BY prospect_lists.name";
 165         return $query;
 166     }
 167
 168         function create_export_members_query($record_id)
 169         {
 170                 $leads_query = "SELECT l.id AS id, 'Leads' AS related_type, '' AS \"name\", l.first_name AS first_name, l.last_name AS last_name,
 171                                 l.primary_address_street AS primary_address_street,l.primary_address_city AS primary_address_city, l.primary_address_state AS primary_address_state, l.primary_address_postalcode AS primary_address_postalcode, l.primary_address_country AS primary_address_country,
 172                                 l.account_name AS account_name,
 173                                 ea.email_address AS primary_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,
 174                                 l.do_not_call AS do_not_call, l.phone_fax AS phone_fax, l.phone_other AS phone_other, l.phone_home AS phone_home, l.phone_mobile AS phone_mobile, l.phone_work AS phone_work
 175                                 FROM prospect_lists_prospects plp
 176                                 INNER JOIN leads l ON plp.related_id=l.id
 177                                 LEFT JOIN email_addr_bean_rel ear ON  ear.bean_id=l.id
 178                                 LEFT JOIN email_addresses ea ON ear.email_address_id=ea.id
 179                                 WHERE plp.prospect_list_id = $record_id AND plp.deleted=0
 180                                 AND l.deleted=0";
 181                 $users_query = "SELECT u.id AS id, 'Users' AS related_type, '' AS \"name\", u.first_name AS first_name, u.last_name AS last_name,
 182                                 u.address_street AS primary_address_street,u.address_city AS primary_address_city, u.address_state AS primary_address_state,  u.address_postalcode AS primary_address_postalcode, u.address_country AS primary_address_country,
 183                                 '' AS account_name,
 184                                 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,
 185                                 0 AS do_not_call, u.phone_fax AS phone_fax, u.phone_other AS phone_other, u.phone_home AS phone_home, u.phone_mobile AS phone_mobile, u.phone_work AS phone_work
 186                                 FROM prospect_lists_prospects plp
 187                                 INNER JOIN users u ON plp.related_id=u.id
 188                                 LEFT JOIN email_addr_bean_rel ear ON  ear.bean_id=u.id
 189                                 LEFT JOIN email_addresses ea ON ear.email_address_id=ea.id
 190                                 WHERE plp.prospect_list_id = $record_id AND plp.deleted=0
 191                                 AND u.deleted=0";
 192                 $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,
 193                                 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,
 194                                 a.name AS account_name,
 195                                 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,
 196                                 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
 197                                 FROM  accounts a,accounts_contacts ac, prospect_lists_prospects plp
 198                                 INNER JOIN contacts c ON plp.related_id=c.id
 199                                 LEFT JOIN email_addr_bean_rel ear ON  ear.bean_id=c.id
 200                                 LEFT JOIN email_addresses ea ON ear.email_address_id=ea.id
 201                                 WHERE plp.prospect_list_id = $record_id  AND plp.deleted=0
 202                                 AND c.deleted=0
 203                                 AND ac.contact_id=c.id
 204                                 AND ac.account_id=a.id";
 205                 $prospects_query = "SELECT p.id AS id, 'Prospects' AS related_type, '' AS \"name\", p.first_name AS first_name, p.last_name AS last_name,
 206                                 p.primary_address_street AS primary_address_street,p.primary_address_city AS primary_address_city, '' AS primary_address_state,  p.primary_address_postalcode AS primary_address_postalcode, p.primary_address_country AS primary_address_country,
 207                                 p.account_name AS account_name,
 208                                 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,
 209                                 p.do_not_call AS do_not_call, p.phone_fax AS phone_fax, p.phone_other AS phone_other, p.phone_home AS phone_home, p.phone_mobile AS phone_mobile, p.phone_work AS phone_work
 210                                 FROM prospect_lists_prospects plp
 211                                 INNER JOIN prospects p ON plp.related_id=p.id
 212                                 LEFT JOIN email_addr_bean_rel ear ON  ear.bean_id=p.id
 213                                 LEFT JOIN email_addresses ea ON ear.email_address_id=ea.id
 214                                 WHERE plp.prospect_list_id = $record_id  AND plp.deleted=0
 215                                 AND p.deleted=0";
 216                 $accounts_query = "SELECT a.id AS id, 'Accounts' AS related_type, a.name AS \"name\", '' AS first_name, '' AS last_name,
 217                                 a.billing_address_street AS primary_address_street,a.billing_address_city AS primary_address_city, a.billing_address_state AS primary_address_state, a.billing_address_postalcode AS primary_address_postalcode, a.billing_address_country AS primary_address_country,
 218                                 '' AS account_name,
 219                                 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,
 220                                 0 AS do_not_call, a.phone_fax as phone_fax, a.phone_alternate AS phone_other, '' AS phone_home, '' AS phone_mobile, a.phone_office AS phone_office
 221                                 FROM prospect_lists_prospects plp
 222                                 INNER JOIN accounts a ON plp.related_id=a.id
 223                                 LEFT JOIN email_addr_bean_rel ear ON  ear.bean_id=a.id
 224                                 LEFT JOIN email_addresses ea ON ear.email_address_id=ea.id
 225                                 WHERE plp.prospect_list_id = $record_id  AND plp.deleted=0
 226                                 AND a.deleted=0";
 227                 $order_by = "ORDER BY related_type, id, primary_address DESC";
 228                 $query = "$leads_query UNION ALL $users_query UNION ALL $contacts_query UNION ALL $prospects_query UNION ALL $accounts_query $order_by";
 229                 return $query;
 230         }
 231
 232         function save_relationship_changes($is_update)
 233     {
 234         parent::save_relationship_changes($is_update);
 235                 if($this->lead_id != "")
 236                         $this->set_prospect_relationship($this->id, $this->lead_id, "lead");
 237         if($this->contact_id != "")
 238                 $this->set_prospect_relationship($this->id, $this->contact_id, "contact");
 239         if($this->prospect_id != "")
 240                 $this->set_prospect_relationship($this->id, $this->contact_id, "prospect");
 241     }
 242
 243         function set_prospect_relationship($prospect_list_id, &$link_ids, $link_name)
 244         {
 245                 $link_field = sprintf("%s_id", $link_name);
 246
 247                 foreach($link_ids as $link_id)
 248                 {
 249                         $this->set_relationship('prospect_lists_prospects', array( $link_field=>$link_id, 'prospect_list_id'=>$prospect_list_id ));
 250                 }
 251         }
 252
 253         function set_prospect_relationship_single($prospect_list_id, $link_id, $link_name)
 254         {
 255                 $link_field = sprintf("%s_id", $link_name);
 256
 257                 $this->set_relationship('prospect_lists_prospects', array( $link_field=>$link_id, 'prospect_list_id'=>$prospect_list_id ));
 258         }
 259
 260
 261         function clear_prospect_relationship($prospect_list_id, $link_id, $link_name)
 262         {
 263                 $link_field = sprintf("%s_id", $link_name);
 264                 $where_clause = " AND $link_field = '$link_id' ";
 265
 266                 $query = sprintf("DELETE FROM prospect_lists_prospects WHERE prospect_list_id='%s' AND deleted = '0' %s", $prospect_list_id, $where_clause);
 267
 268                 $this->db->query($query, true, "Error clearing prospect/prospect_list relationship: ");
 269         }
 270
 271
 272         function mark_relationships_deleted($id)
 273         {
 274         }
 275
 276         function fill_in_additional_list_fields()
 277         {
 278         }
 279
 280         function fill_in_additional_detail_fields()
 281         {
 282                 parent::fill_in_additional_detail_fields();
 283         $this->entry_count = $this->get_entry_count();
 284         }
 285
 286
 287         function update_currency_id($fromid, $toid){
 288         }
 289
 290
 291         function get_entry_count()
 292         {
 293                 $query = "SELECT count(*) AS num FROM prospect_lists_prospects WHERE prospect_list_id='$this->id' AND deleted = '0'";
 294                 $result = $this->db->query($query, true, "Grabbing prospect_list entry count");
 295
 296                 $row = $this->db->fetchByAssoc($result);
 297
 298                 if($row)
 299                         return $row['num'];
 300                 else
 301                         return 0;
 302         }
 303
 304
 305         function get_list_view_data(){
 306                 $temp_array = $this->get_list_view_array();
 307                 $temp_array["ENTRY_COUNT"] = $this->get_entry_count();
 308                 return $temp_array;
 309         }
 310         /**
 311                 builds a generic search based on the query string using or
 312                 do not include any $this-> because this is called on without having the class instantiated
 313         */
 314         function build_generic_where_clause ($the_query_string)
 315         {
 316                 $where_clauses = Array();
 317                 $the_query_string = $GLOBALS['db']->quote($the_query_string);
 318                 array_push($where_clauses, "prospect_lists.name like '$the_query_string%'");
 319
 320                 $the_where = "";
 321                 foreach($where_clauses as $clause)
 322                 {
 323                         if($the_where != "") $the_where .= " or ";
 324                         $the_where .= $clause;
 325                 }
 326
 327
 328                 return $the_where;
 329         }
 330
 331         function save($check_notify = FALSE) {
 332
 333                 return parent::save($check_notify);
 334
 335         }
 336
 337          function bean_implements($interface){
 338                 switch($interface){
 339                         case 'ACL':return true;
 340                 }
 341                 return false;
 342         }
 343
 344 }
 345
 346
 347
 348
 349
 350 ?>