src/lib/Bcd/Data/Ads.pm

   1 package Bcd::Data::Ads;
   2
   3 # This file is part of the breadcrumbs daemon (bcd).
   4 # Copyright (C) 2007 Pasqualino Ferrentino
   5
   6 # This program is free software; you can redistribute it and/or modify
   7 # it under the terms of the GNU General Public License as published by
   8 # the Free Software Foundation; either version 2 of the License, or
   9 # (at your option) any later version.
  10
  11 # This program is distributed in the hope that it will be useful, but
  12 # WITHOUT ANY WARRANTY; without even the implied warranty of
  13 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
  14 # General Public License for more details.
  15
  16 # You should have received a copy of the GNU General Public License
  17 # along with this program; if not, write to the Free Software
  18 # Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
  19 # 02110-1301, USA.
  20
  21 # Contact: lino.ferrentino@yahoo.it (in Italian, English or German).
  22
  23 use strict;
  24 use warnings;
  25
  26 use Data::Dumper;
  27
  28 use Bcd::Constants::AdsConstants;
  29 use Bcd::Constants::ActivitiesConstants;
  30
  31 use constant {
  32     INSERT_NEW_AD                             =>
  33         qq{INSERT INTO ads(id_act, id_user, id_state, ad_text) VALUES (?,?,?,?)},
  34
  35         INSERT_NEW_AD_LOCALITY                =>
  36         qq{INSERT INTO ads_user_localities(id_ad, id_locus, p_min, p_max, t_e, t_c) }.
  37         qq{VALUES( ? , ?, ?, ?, ?, ?)},
  38
  39         GET_AD                                =>
  40         qq{SELECT id_act, id_user, id_state, created_on, last_price_calculated_on, ad_text }.
  41         qq{ FROM ads WHERE id = ?},
  42
  43         GET_ADS_USER_LOCALITIES               =>
  44         qq{SELECT id_locus, p_min, p_max, t_e, t_c FROM ads_user_localities WHERE }.
  45         qq{id_ad = ? },
  46
  47         GET_AD_USER_LOCALITY                  =>
  48         qq{SELECT p_min, p_max, t_e, t_c FROM ads_user_localities WHERE }.
  49         qq{id_locus = ? AND id_ad = ?},
  50
  51         GET_USER_ADS                          =>
  52         qq{SELECT a.id, a.id_act, act.description, a.created_on, a.ad_text }.
  53         qq{ FROM ads as a, activities as act WHERE a.id_user = ? AND a.id_state = }.
  54         Bcd::Constants::AdsConstants::ACTIVE .
  55         qq{ AND a.id_act = act.id},
  56
  57         GET_ADS_FOR_THIS_ACTIVITY             =>
  58         qq{SELECT a.id, a.id_user, a.created_on, a.ad_text FROM ads as a, users as u }.
  59         qq{ WHERE a.id_act = ? AND a.id_user = u.id AND u.id_ant_nest = ? AND a.id_state = }.
  60         Bcd::Constants::AdsConstants::ACTIVE,
  61
  62         GET_COUNT_ADS_FOR_ACTIVITY            =>
  63         qq{SELECT COUNT(*) FROM ads as a, users as u }.
  64         qq{ WHERE a.id_act = ? AND a.id_user = u.id AND u.id_ant_nest = ? AND a.id_state = }.
  65         Bcd::Constants::AdsConstants::ACTIVE,
  66
  67         UPDATE_AD_STATE                       =>
  68         qq{UPDATE ads SET id_state = ? WHERE id = ? },
  69
  70 };
  71
  72 sub this_ad_is_inactive_now{
  73     my ($class, $stash, $id_ad) = @_;
  74     $stash->prep_exec
  75         (UPDATE_AD_STATE, Bcd::Constants::AdsConstants::INACTIVE, $id_ad);
  76 }
  77
  78 sub get_ad_locality_hash{
  79     my ($class, $stash, $id_ad, $id_locus) = @_;
  80     return $stash->select_one_row_hash(GET_AD_USER_LOCALITY, $id_locus, $id_ad);
  81 }
  82
  83 sub get_activity_post_code_ads_arr{
  84     my ($class, $stash, $id_act, $post_code) = @_;
  85     return $stash->select_all_arr(GET_ADS_FOR_THIS_ACTIVITY, $id_act, $post_code);
  86 }
  87
  88 sub get_count_activity_post_code_ads{
  89     my ($class, $stash, $id_act, $post_code) = @_;
  90     my $row = $stash->select_one_row_arr(GET_COUNT_ADS_FOR_ACTIVITY, $id_act, $post_code);
  91     return (!defined($row)) ? 0 : $row->[0];
  92 }
  93
  94 sub get_user_ads_ds{
  95     my ($class, $stash, $user_id) = @_;
  96     return $stash->select_all_ds(GET_USER_ADS, $user_id);
  97 }
  98
  99
 100 sub get_all_localities_for_ad_arr{
 101     my ($class, $stash, $id) = @_;
 102     return $stash->select_all_arr(GET_ADS_USER_LOCALITIES, $id);
 103 }
 104
 105 sub get_all_localities_for_ad_ds{
 106     my ($class, $stash, $id) = @_;
 107     return $stash->select_all_ds(GET_ADS_USER_LOCALITIES, $id);
 108 }
 109
 110 sub get_ad_hash{
 111     my ($class, $stash, $id) = @_;
 112     return $stash->select_one_row_hash
 113         (GET_AD, $id);
 114 }
 115
 116 sub create_new_ad{
 117     my ($class, $stash, $id_act, $id_user,
 118         $text, $id_presence, $p_min, $p_max, $t_e, $t_c) = @_;
 119
 120     #ok, I should simply insert two rows in the db...
 121     #one row is the ad
 122     $stash->prep_exec
 123         (INSERT_NEW_AD, $id_act, $id_user,
 124          Bcd::Constants::AdsConstants::ACTIVE, $text);
 125
 126     #then I get the id of this ad...
 127     my $ad_id = $stash->get_last_ad_id();
 128
 129     #ok, Now I add the ads_localities row
 130     $class->add_locus_to_ad($stash, $ad_id, $id_presence, $p_min, $p_max, $t_e, $t_c);
 131
 132     return $ad_id;
 133 }
 134
 135 sub decode_ad_type_from_activity{
 136     my ($class, $id_act) = @_;
 137
 138     my $ad_type;
 139
 140     if ($id_act <= Bcd::Constants::ActivitiesConstants::MAX_SERVICE_ID){
 141         $ad_type = 'service';
 142     } elsif ($id_act < Bcd::Constants::ActivitiesConstants::MAX_HOMEMADE_ID){
 143         $ad_type = 'homemade';
 144     } else {
 145         $ad_type = 'object';
 146     }
 147
 148     return $ad_type;
 149 }
 150
 151 sub add_locus_to_ad{
 152     my ($class, $stash, $ad_id, $id_presence, $p_min, $p_max, $t_e, $t_c) = @_;
 153
 154     $stash->prep_exec
 155         (INSERT_NEW_AD_LOCALITY, $ad_id, $id_presence, $p_min, $p_max, $t_e, $t_c);
 156 }
 157
 158
 159 sub init_db {
 160     my ($class, $stash) = @_;
 161
 162     my $conn = $stash->get_connection();
 163
 164     #ok, now I should insert the values in the table...
 165     my $sth = $conn->prepare(qq{insert into ads_states values(?,?)});
 166
 167     foreach (Bcd::Constants::AdsConstants::LIST_ADS_STATES){
 168         $sth->bind_param(1, $_->[0]);
 169         $sth->bind_param(2, $_->[1]);
 170         $sth->execute();
 171     }
 172 }
 173
 174
 175 1;