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.title AS title, l.salutation AS salutation,
 172                                 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,
 173                                 l.account_name AS account_name,
 174                                 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,
 175                                 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
 176                                 FROM prospect_lists_prospects plp
 177                                 INNER JOIN leads l ON plp.related_id=l.id
 178                                 LEFT JOIN email_addr_bean_rel ear ON  ear.bean_id=l.id
 179                                 LEFT JOIN email_addresses ea ON ear.email_address_id=ea.id
 180                                 WHERE plp.prospect_list_id = $record_id AND plp.deleted=0
 181                                 AND l.deleted=0
 182                                 AND ear.deleted=0";
 183                 $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,
 184                 u.title AS title, '' AS salutation,
 185                                 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,
 186                                 '' AS account_name,
 187                                 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,
 188                                 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
 189                                 FROM prospect_lists_prospects plp
 190                                 INNER JOIN users u ON plp.related_id=u.id
 191                                 LEFT JOIN email_addr_bean_rel ear ON  ear.bean_id=u.id
 192                                 LEFT JOIN email_addresses ea ON ear.email_address_id=ea.id
 193                                 WHERE plp.prospect_list_id = $record_id AND plp.deleted=0
 194                                 AND u.deleted=0
 195                                 AND ear.deleted=0";
 196                 $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,
 197                 c.title AS title, c.salutation AS salutation,
 198                                 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,
 199                                 a.name AS account_name,
 200                                 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,
 201                                 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
 202                                 FROM  accounts a,accounts_contacts ac, prospect_lists_prospects plp
 203                                 INNER JOIN contacts c ON plp.related_id=c.id
 204                                 LEFT JOIN email_addr_bean_rel ear ON  ear.bean_id=c.id
 205                                 LEFT JOIN email_addresses ea ON ear.email_address_id=ea.id
 206                                 WHERE plp.prospect_list_id = $record_id  AND plp.deleted=0
 207                                 AND c.deleted=0
 208                                 AND ac.contact_id=c.id
 209                                 AND ac.account_id=a.id
 210                                 AND ear.deleted=0";
 211                 $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,
 212                 p.title AS title, p.salutation AS salutation,
 213                                 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,
 214                                 p.account_name AS account_name,
 215                                 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,
 216                                 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
 217                                 FROM prospect_lists_prospects plp
 218                                 INNER JOIN prospects p ON plp.related_id=p.id
 219                                 LEFT JOIN email_addr_bean_rel ear ON  ear.bean_id=p.id
 220                                 LEFT JOIN email_addresses ea ON ear.email_address_id=ea.id
 221                                 WHERE plp.prospect_list_id = $record_id  AND plp.deleted=0
 222                                 AND p.deleted=0
 223                                 AND ear.deleted=0";
 224                 $accounts_query = "SELECT a.id AS id, 'Accounts' AS related_type, a.name AS \"name\", '' AS first_name, '' AS last_name,
 225                 '' AS title, '' AS salutation,
 226                                 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,
 227                                 '' AS account_name,
 228                                 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,
 229                                 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
 230                                 FROM prospect_lists_prospects plp
 231                                 INNER JOIN accounts a ON plp.related_id=a.id
 232                                 LEFT JOIN email_addr_bean_rel ear ON  ear.bean_id=a.id
 233                                 LEFT JOIN email_addresses ea ON ear.email_address_id=ea.id
 234                                 WHERE plp.prospect_list_id = $record_id  AND plp.deleted=0
 235                                 AND a.deleted=0
 236                                 AND ear.deleted=0";
 237                 $order_by = "ORDER BY related_type, id, primary_address DESC";
 238                 $query = "$leads_query UNION ALL $users_query UNION ALL $contacts_query UNION ALL $prospects_query UNION ALL $accounts_query $order_by";
 239                 return $query;
 240         }
 241
 242         function save_relationship_changes($is_update)
 243     {
 244         parent::save_relationship_changes($is_update);
 245                 if($this->lead_id != "")
 246                         $this->set_prospect_relationship($this->id, $this->lead_id, "lead");
 247         if($this->contact_id != "")
 248                 $this->set_prospect_relationship($this->id, $this->contact_id, "contact");
 249         if($this->prospect_id != "")
 250                 $this->set_prospect_relationship($this->id, $this->contact_id, "prospect");
 251     }
 252
 253         function set_prospect_relationship($prospect_list_id, &$link_ids, $link_name)
 254         {
 255                 $link_field = sprintf("%s_id", $link_name);
 256
 257                 foreach($link_ids as $link_id)
 258                 {
 259                         $this->set_relationship('prospect_lists_prospects', array( $link_field=>$link_id, 'prospect_list_id'=>$prospect_list_id ));
 260                 }
 261         }
 262
 263         function set_prospect_relationship_single($prospect_list_id, $link_id, $link_name)
 264         {
 265                 $link_field = sprintf("%s_id", $link_name);
 266
 267                 $this->set_relationship('prospect_lists_prospects', array( $link_field=>$link_id, 'prospect_list_id'=>$prospect_list_id ));
 268         }
 269
 270
 271         function clear_prospect_relationship($prospect_list_id, $link_id, $link_name)
 272         {
 273                 $link_field = sprintf("%s_id", $link_name);
 274                 $where_clause = " AND $link_field = '$link_id' ";
 275
 276                 $query = sprintf("DELETE FROM prospect_lists_prospects WHERE prospect_list_id='%s' AND deleted = '0' %s", $prospect_list_id, $where_clause);
 277
 278                 $this->db->query($query, true, "Error clearing prospect/prospect_list relationship: ");
 279         }
 280
 281
 282         function mark_relationships_deleted($id)
 283         {
 284         }
 285
 286         function fill_in_additional_list_fields()
 287         {
 288         }
 289
 290         function fill_in_additional_detail_fields()
 291         {
 292                 parent::fill_in_additional_detail_fields();
 293         $this->entry_count = $this->get_entry_count();
 294         }
 295
 296
 297         function update_currency_id($fromid, $toid){
 298         }
 299
 300
 301         function get_entry_count()
 302         {
 303                 $query = "SELECT count(*) AS num FROM prospect_lists_prospects WHERE prospect_list_id='$this->id' AND deleted = '0'";
 304                 $result = $this->db->query($query, true, "Grabbing prospect_list entry count");
 305
 306                 $row = $this->db->fetchByAssoc($result);
 307
 308                 if($row)
 309                         return $row['num'];
 310                 else
 311                         return 0;
 312         }
 313
 314
 315         function get_list_view_data(){
 316                 $temp_array = $this->get_list_view_array();
 317                 $temp_array["ENTRY_COUNT"] = $this->get_entry_count();
 318                 return $temp_array;
 319         }
 320         /**
 321                 builds a generic search based on the query string using or
 322                 do not include any $this-> because this is called on without having the class instantiated
 323         */
 324         function build_generic_where_clause ($the_query_string)
 325         {
 326                 $where_clauses = Array();
 327                 $the_query_string = $GLOBALS['db']->quote($the_query_string);
 328                 array_push($where_clauses, "prospect_lists.name like '$the_query_string%'");
 329
 330                 $the_where = "";
 331                 foreach($where_clauses as $clause)
 332                 {
 333                         if($the_where != "") $the_where .= " or ";
 334                         $the_where .= $clause;
 335                 }
 336
 337
 338                 return $the_where;
 339         }
 340
 341         function save($check_notify = FALSE) {
 342
 343                 return parent::save($check_notify);
 344
 345         }
 346
 347          function bean_implements($interface){
 348                 switch($interface){
 349                         case 'ACL':return true;
 350                 }
 351                 return false;
 352         }
 353
 354 }
 355
 356
 357
 358
 359
 360 ?>