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
  30 use constant {
  31     INSERT_NEW_AD                             =>
  32         qq{INSERT INTO ads(id_act, id_user, id_state, ad_text) VALUES (?,?,?,?)},
  33
  34         INSERT_NEW_AD_LOCALITY                =>
  35         qq{INSERT INTO ads_user_localities(id_ad, id_locus, p_min, p_max, t_e, t_c) }.
  36         qq{VALUES( ? , ?, ?, ?, ?, ?)},
  37
  38         GET_AD                                =>
  39         qq{SELECT id_act, id_user, id_state, created_on, last_price_calculated_on, ad_text }.
  40         qq{ FROM ads WHERE id = ?},
  41
  42         GET_ADS_USER_LOCALITIES               =>
  43         qq{SELECT id_locus, p_min, p_max, t_e, t_c FROM ads_user_localities WHERE }.
  44         qq{id_ad = ? },
  45
  46         GET_USER_ADS                          =>
  47         qq{SELECT a.id, a.id_act, act.description, a.created_on, a.ad_text }.
  48         qq{ FROM ads as a, activities as act WHERE a.id_user = ? AND a.id_state = }.
  49         Bcd::Constants::AdsConstants::ACTIVE .
  50         qq{ AND a.id_act = act.id},
  51
  52         GET_ADS_FOR_THIS_ACTIVITY             =>
  53         qq{SELECT a.id, a.id_user, a.created_on, a.ad_text FROM ads as a, users as u }.
  54         qq{ WHERE a.id_act = ? AND a.id_user = u.id AND u.id_ant_nest = ? AND a.id_state = }.
  55         Bcd::Constants::AdsConstants::ACTIVE,
  56
  57         GET_COUNT_ADS_FOR_ACTIVITY            =>
  58         qq{SELECT COUNT(*) 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 };
  63
  64 sub get_activity_post_code_ads_arr{
  65     my ($class, $stash, $id_act, $post_code) = @_;
  66     return $stash->select_all_arr(GET_ADS_FOR_THIS_ACTIVITY, $id_act, $post_code);
  67 }
  68
  69 sub get_count_activity_post_code_ads{
  70     my ($class, $stash, $id_act, $post_code) = @_;
  71     my $row = $stash->select_one_row_arr(GET_COUNT_ADS_FOR_ACTIVITY, $id_act, $post_code);
  72     return (!defined($row)) ? 0 : $row->[0];
  73 }
  74
  75 sub get_user_ads_ds{
  76     my ($class, $stash, $user_id) = @_;
  77     return $stash->select_all_ds(GET_USER_ADS, $user_id);
  78 }
  79
  80
  81 sub get_all_localities_for_ad_arr{
  82     my ($class, $stash, $id) = @_;
  83     return $stash->select_all_arr(GET_ADS_USER_LOCALITIES, $id);
  84 }
  85
  86 sub get_ad_hash{
  87     my ($class, $stash, $id) = @_;
  88     return $stash->select_one_row_hash
  89         (GET_AD, $id);
  90 }
  91
  92 sub create_new_ad{
  93     my ($class, $stash, $id_act, $id_user,
  94         $text, $id_presence, $p_min, $p_max, $t_e, $t_c) = @_;
  95
  96     #ok, I should simply insert two rows in the db...
  97     #one row is the ad
  98     $stash->prep_exec
  99         (INSERT_NEW_AD, $id_act, $id_user,
 100          Bcd::Constants::AdsConstants::ACTIVE, $text);
 101
 102     #then I get the id of this ad...
 103     my $ad_id = $stash->get_last_ad_id();
 104
 105     #ok, Now I add the ads_localities row
 106     $stash->prep_exec
 107         (INSERT_NEW_AD_LOCALITY, $ad_id, $id_presence, $p_min, $p_max, $t_e, $t_c);
 108
 109     return $ad_id;
 110 }
 111
 112
 113 sub init_db {
 114     my ($class, $stash) = @_;
 115
 116     my $conn = $stash->get_connection();
 117
 118     #ok, now I should insert the values in the table...
 119     my $sth = $conn->prepare(qq{insert into ads_states values(?,?)});
 120
 121     foreach (Bcd::Constants::AdsConstants::LIST_ADS_STATES){
 122         $sth->bind_param(1, $_->[0]);
 123         $sth->bind_param(2, $_->[1]);
 124         $sth->execute();
 125     }
 126 }
 127
 128
 129 1;