bin/lib/c_news.pm

package c_news;

# Project: Clipping
# Author:  Nelson Ferraz <nferraz@phperl.com>
# Date:    2003-08-14

# Methods:
#
#   new
#   select
#   insert
#   delete
#   update
#
# Syntax:
#
#   my $c_news = new c_news;
#
#   $c_news->select(param1 => value1, param2, value2, ... );
#   $c_news->insert(param1 => value1, param2, value2, ... );
#   $c_news->delete(param1 => value1, param2, value2, ... );
#   $c_news->update(param1 => value1, param2, value2, ... );

use strict;
use vars qw($conn); # Connection

###

sub new {
    my $proto    = shift;
    my $conn_ref = shift;

    die 'Missing database connection reference ($conn_ref)' if !ref $conn_ref;

    my $self  = {};
    bless($self, (ref($proto) || $proto));

    # Database connection
    $conn = ${$conn_ref};

    return $self;
}

###

sub select {
    my($self, $attr) = @_;

    my $query = "SELECT
                        c_news_id,
                        c_source_fk,
                        c_customer_fk,
                        c_type_fk,
                        c_news_section,
                        c_news_page,
                        c_news_title,
                        c_news_abstract,
                        c_news_full_text
                FROM c_news\n";

    # restrict results
    my @where = ();

    push @where, "c_source_id = " . quote($attr->{c_source_id})
         if $attr->{"c_source_id"};

    push @where, "c_source_description = " . quote($attr->{c_source_description})
         if $attr->{"c_source_description"};

    push @where, "c_source_notes = " . quote($attr->{c_source_notes})
         if $attr->{"c_source_notes"};

    push @where, "c_customer_id = " . quote($attr->{c_customer_id})
         if $attr->{"c_customer_id"};

    push @where, "c_customer_name = " . quote($attr->{c_customer_name})
         if $attr->{"c_customer_name"};

    push @where, "c_customer_phone = " . quote($attr->{c_customer_phone})
         if $attr->{"c_customer_phone"};

    push @where, "c_customer_email = " . quote($attr->{c_customer_email})
         if $attr->{"c_customer_email"};

    push @where, "c_customer_notes = " . quote($attr->{c_customer_notes})
         if $attr->{"c_customer_notes"};

    push @where, "c_type_id = " . quote($attr->{c_type_id})
         if $attr->{"c_type_id"};

    push @where, "c_type_description = " . quote($attr->{c_type_description})
         if $attr->{"c_type_description"};

    push @where, "c_type_notes = " . quote($attr->{c_type_notes})
         if $attr->{"c_type_notes"};

    push @where, "c_news_id = " . quote($attr->{c_news_id})
         if $attr->{"c_news_id"};

    push @where, "c_news_section LIKE " . quote ("\%$attr->{c_news_section}\%")
         if $attr->{"c_news_section"};

    push @where, "c_news_page LIKE " . quote ("\%$attr->{c_news_page}\%")
         if $attr->{"c_news_page"};

    push @where, "c_news_title LIKE " . quote ("\%$attr->{c_news_title}\%")
         if $attr->{"c_news_title"};

    push @where, "c_news_abstract LIKE " . quote ("\%$attr->{c_news_abstract}\%")
         if $attr->{"c_news_abstract"};

    push @where, "c_news_full_text LIKE " . quote ("\%$attr->{c_news_full_text}\%")
         if $attr->{"c_news_full_text"};

    push @where, "c_news_date_created = " . quote($attr->{c_news_date_created})
         if $attr->{"c_news_date_created"};

    push @where, "c_news_date_updated = " . quote($attr->{c_news_date_updated})
         if $attr->{"c_news_date_updated"};

    push @where, "c_news_date_deleted = " . quote($attr->{c_news_date_deleted})
         if $attr->{"c_news_date_deleted"};


    $query .= "WHERE " . join ("\nAND ", @where) . "\n" if ($#where > -1);

    $query .= "ORDER BY c_news_title";

    # Execute query
    my $result = $conn->exec($query);

    # Error checking
    if ($conn->errorMessage) {
      my $errorMessage = $conn->errorMessage;
      die "Error executing query '$query':\n$errorMessage";
    }

    my @result = ();
    while (my ($c_news_id,$c_source_fk,$c_customer_fk,$c_type_fk,$c_news_section,$c_news_page,$c_news_title,$c_news_abstract,$c_news_full_text) = $result->fetchrow) {
      # FIXME: fetch using a hash?
      push @result, {
        c_news_id => $c_news_id,
        c_source_fk => $c_source_fk,
        c_customer_fk => $c_customer_fk,
        c_type_fk => $c_type_fk,
        c_news_section => $c_news_section,
        c_news_page => $c_news_page,
        c_news_title => $c_news_title,
        c_news_abstract => $c_news_abstract,
        c_news_full_text => $c_news_full_text
      };
    }

    return @result;
}

###

sub view {
    my($self, $attr) = @_;

    my $query = "SELECT
                        c_source_id,
                        c_source_description,
                        c_source_notes,
                        c_customer_id,
                        c_customer_name,
                        c_customer_phone,
                        c_customer_email,
                        c_customer_notes,
                        c_type_id,
                        c_type_description,
                        c_type_notes,
                        c_news_id,
                        c_news_section,
                        c_news_page,
                        c_news_title,
                        c_news_abstract,
                        c_news_full_text,
                        c_news_date_created,
                        c_news_date_updated,
                        c_news_date_deleted
                FROM view_c_news\n";

    # restrict results
    my @where = ();

    push @where, "c_source_id = " . quote($attr->{c_source_id})
         if $attr->{"c_source_id"};

    push @where, "c_source_description = " . quote($attr->{c_source_description})
         if $attr->{"c_source_description"};

    push @where, "c_source_notes = " . quote($attr->{c_source_notes})
         if $attr->{"c_source_notes"};

    push @where, "c_customer_id = " . quote($attr->{c_customer_id})
         if $attr->{"c_customer_id"};

    push @where, "c_customer_name = " . quote($attr->{c_customer_name})
         if $attr->{"c_customer_name"};

    push @where, "c_customer_phone = " . quote($attr->{c_customer_phone})
         if $attr->{"c_customer_phone"};

    push @where, "c_customer_email = " . quote($attr->{c_customer_email})
         if $attr->{"c_customer_email"};

    push @where, "c_customer_notes = " . quote($attr->{c_customer_notes})
         if $attr->{"c_customer_notes"};

    push @where, "c_type_id = " . quote($attr->{c_type_id})
         if $attr->{"c_type_id"};

    push @where, "c_type_description = " . quote($attr->{c_type_description})
         if $attr->{"c_type_description"};

    push @where, "c_type_notes = " . quote($attr->{c_type_notes})
         if $attr->{"c_type_notes"};

    push @where, "c_news_id = " . quote($attr->{c_news_id})
         if $attr->{"c_news_id"};

    push @where, "c_news_section LIKE " . quote ("\%$attr->{c_news_section}\%")
         if $attr->{"c_news_section"};

    push @where, "c_news_page LIKE " . quote ("\%$attr->{c_news_page}\%")
         if $attr->{"c_news_page"};

    push @where, "c_news_title LIKE " . quote ("\%$attr->{c_news_title}\%")
         if $attr->{"c_news_title"};

    push @where, "c_news_abstract LIKE " . quote ("\%$attr->{c_news_abstract}\%")
         if $attr->{"c_news_abstract"};

    push @where, "c_news_full_text LIKE " . quote ("\%$attr->{c_news_full_text}\%")
         if $attr->{"c_news_full_text"};

    push @where, "c_news_date_created = " . quote($attr->{c_news_date_created})
         if $attr->{"c_news_date_created"};

    push @where, "c_news_date_updated = " . quote($attr->{c_news_date_updated})
         if $attr->{"c_news_date_updated"};

    push @where, "c_news_date_deleted = " . quote($attr->{c_news_date_deleted})
         if $attr->{"c_news_date_deleted"};


    $query .= "WHERE " . join ("\nAND ", @where) . "\n"
           if ($#where > -1);
    
    $attr->{"order_by"} ||= "c_news_title";
    $query .= "ORDER BY " . $attr->{"order_by"} . "\n"
           if defined $attr->{"order_by"};

    $query .= "GROUP BY " . $attr->{"group_by"} . "\n"
           if defined $attr->{"group_by"};

    $attr->{"limit"}  ||= 20;
    $attr->{"offset"} ||= '0';

    $query .= "LIMIT $attr->{'limit'} OFFSET $attr->{'offset'}\n";

    # Execute query
    my $result = $conn->exec($query);

    # Error checking
    if ($conn->errorMessage) {
      my $errorMessage = $conn->errorMessage;
      die "Error executing query '$query':\n$errorMessage";
    }

    my @result = ();
    while (my ($c_source_id,$c_source_description,$c_source_notes,$c_customer_id,$c_customer_name,$c_customer_phone,$c_customer_email,$c_customer_notes,$c_type_id,$c_type_description,$c_type_notes,$c_news_id,$c_news_section,$c_news_page,$c_news_title,$c_news_abstract,$c_news_full_text,$c_news_date_created,$c_news_date_updated,$c_news_date_deleted) = $result->fetchrow) {
      push @result, {
        c_source_id => $c_source_id,
        c_source_description => $c_source_description,
        c_source_notes => $c_source_notes,
        c_customer_id => $c_customer_id,
        c_customer_name => $c_customer_name,
        c_customer_phone => $c_customer_phone,
        c_customer_email => $c_customer_email,
        c_customer_notes => $c_customer_notes,
        c_type_id => $c_type_id,
        c_type_description => $c_type_description,
        c_type_notes => $c_type_notes,
        c_news_id => $c_news_id,
        c_news_section => $c_news_section,
        c_news_page => $c_news_page,
        c_news_title => $c_news_title,
        c_news_abstract => $c_news_abstract,
        c_news_full_text => $c_news_full_text,
        c_news_date_created => $c_news_date_created,
        c_news_date_updated => $c_news_date_updated,
        c_news_date_deleted => $c_news_date_deleted
      };
    }

    return @result;
}

###

sub insert {
    my($self, $attr) = @_;

    my $query = "SELECT sp_ins_c_news (" .
                        quote($attr->{'c_source_fk'}) . ',' .
                        quote($attr->{'c_customer_fk'}) . ',' .
                        quote($attr->{'c_type_fk'}) . ',' .
                        quote($attr->{'c_news_section'}) . ',' .
                        quote($attr->{'c_news_page'}) . ',' .
                        quote($attr->{'c_news_title'}) . ',' .
                        quote($attr->{'c_news_abstract'}) . ',' .
                        quote($attr->{'c_news_full_text'}) . 
                ")";

    # Execute query
    my $result = $conn->exec($query);

    # Error checking
    if ($conn->errorMessage) {
      my $errorMessage = $conn->errorMessage;
      die "Error executing query '$query':\n$errorMessage";
    }
}

###

sub delete {
    my($self, $attr) = @_;

    my $c_news_id = $attr->{c_news_id};
    my @c_news_id = split (/\0/,$c_news_id);

    foreach my $id (@c_news_id) {
      my $q_id = quote ($id);
      my $query = "SELECT sp_del_c_news ($q_id)";

      # Execute query
      my $result = $conn->exec($query);

      # Error checking
      if ($conn->errorMessage) {
        my $errorMessage = $conn->errorMessage;
        die "Error executing query '$query':\n$errorMessage";
      }
    }
}

###

sub update {
    my($self, $attr) = @_;

    my $query = "SELECT sp_upd_c_news (" .
                         quote($attr->{'c_news_id'}) . ',' .
                         quote($attr->{'c_source_fk'}) . ',' .
                         quote($attr->{'c_customer_fk'}) . ',' .
                         quote($attr->{'c_type_fk'}) . ',' .
                         quote($attr->{'c_news_section'}) . ',' .
                         quote($attr->{'c_news_page'}) . ',' .
                         quote($attr->{'c_news_title'}) . ',' .
                         quote($attr->{'c_news_abstract'}) . ',' .
                         quote($attr->{'c_news_full_text'}) . 
                ")";

    # Execute query
    my $result = $conn->exec($query);

    # Error checking
    if ($conn->errorMessage) {
      my $errorMessage = $conn->errorMessage;
      die "Error executing query '$query':\n$errorMessage";
    }
}

###


sub count_by_c_source {
    my($self, $attr) = @_;

    my $query = "SELECT
                c_source_description,
                count_c_news_by_c_source.c_source_id,
                count_c_news_by_c_source.count_c_news
        FROM
                count_c_news_by_c_source,
                c_source
        WHERE
                count_c_news_by_c_source.c_source_id = c_source.c_source_id
        ORDER BY
                c_source_description";

    # Execute query
    my $result = $conn->exec($query);

    # Error checking
    if ($conn->errorMessage) {
      my $errorMessage = $conn->errorMessage;
      die "Error executing query '$query':\n$errorMessage";
    }

    my @result = ();
    while (my ($c_source_description,
               $c_source_id,
               $count_c_news) = $result->fetchrow) {
      # FIXME: fetch using a hash?
      push @result, {
               c_source_description => $c_source_description,
               c_source_id => $c_source_id,
               count_c_news => $count_c_news
      };
    }

    return @result;


}

###


sub count_by_c_customer {
    my($self, $attr) = @_;

    my $query = "SELECT
                c_customer_name,
                count_c_news_by_c_customer.c_customer_id,
                count_c_news_by_c_customer.count_c_news
        FROM
                count_c_news_by_c_customer,
                c_customer
        WHERE
                count_c_news_by_c_customer.c_customer_id = c_customer.c_customer_id
        ORDER BY
                c_customer_name";

    # Execute query
    my $result = $conn->exec($query);

    # Error checking
    if ($conn->errorMessage) {
      my $errorMessage = $conn->errorMessage;
      die "Error executing query '$query':\n$errorMessage";
    }

    my @result = ();
    while (my ($c_customer_name,
               $c_customer_id,
               $count_c_news) = $result->fetchrow) {
      # FIXME: fetch using a hash?
      push @result, {
               c_customer_name => $c_customer_name,
               c_customer_id => $c_customer_id,
               count_c_news => $count_c_news
      };
    }

    return @result;


}

###


sub count_by_c_type {
    my($self, $attr) = @_;

    my $query = "SELECT
                c_type_description,
                count_c_news_by_c_type.c_type_id,
                count_c_news_by_c_type.count_c_news
        FROM
                count_c_news_by_c_type,
                c_type
        WHERE
                count_c_news_by_c_type.c_type_id = c_type.c_type_id
        ORDER BY
                c_type_description";

    # Execute query
    my $result = $conn->exec($query);

    # Error checking
    if ($conn->errorMessage) {
      my $errorMessage = $conn->errorMessage;
      die "Error executing query '$query':\n$errorMessage";
    }

    my @result = ();
    while (my ($c_type_description,
               $c_type_id,
               $count_c_news) = $result->fetchrow) {
      # FIXME: fetch using a hash?
      push @result, {
               c_type_description => $c_type_description,
               c_type_id => $c_type_id,
               count_c_news => $count_c_news
      };
    }

    return @result;


}

###


sub quote {
    my $str = shift;
    if ($str eq '') {
      return "NULL";
    } else {
      $str =~ s/'/''/g; # ISO SQL2
      return "'$str'";
    }
}

1;

__END__

Clipping - Copyright 2003 PhPerl.com

This program is free software; you can redistribute it and/or
modify it under the terms of the GNU General Public License
as published by the Free Software Foundation; either version 2
of the License, or (at your option) any later version.

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.

See the GNU General Public License for more details:

http://www.gnu.org/copyleft/gpl.html
1	dpavlin	1.1	package c_news;
2
3			# Project: Clipping
4			# Author: Nelson Ferraz <nferraz@phperl.com>
5			# Date: 2003-08-14
6
7			# Methods:
8			#
9			# new
10			# select
11			# insert
12			# delete
13			# update
14			#
15			# Syntax:
16			#
17			# my $c_news = new c_news;
18			#
19			# $c_news->select(param1 => value1, param2, value2, ... );
20			# $c_news->insert(param1 => value1, param2, value2, ... );
21			# $c_news->delete(param1 => value1, param2, value2, ... );
22			# $c_news->update(param1 => value1, param2, value2, ... );
23
24			use strict;
25			use vars qw($conn); # Connection
26
27			###
28
29			sub new {
30			my $proto = shift;
31			my $conn_ref = shift;
32
33			die 'Missing database connection reference ($conn_ref)' if !ref $conn_ref;
34
35			my $self = {};
36			bless($self, (ref($proto) \|\| $proto));
37
38			# Database connection
39			$conn = ${$conn_ref};
40
41			return $self;
42			}
43
44			###
45
46			sub select {
47			my($self, $attr) = @_;
48
49			my $query = "SELECT
50			c_news_id,
51			c_source_fk,
52			c_customer_fk,
53			c_type_fk,
54			c_news_section,
55			c_news_page,
56			c_news_title,
57			c_news_abstract,
58			c_news_full_text
59			FROM c_news\n";
60
61			# restrict results
62			my @where = ();
63
64			push @where, "c_source_id = " . quote($attr->{c_source_id})
65			if $attr->{"c_source_id"};
66
67			push @where, "c_source_description = " . quote($attr->{c_source_description})
68			if $attr->{"c_source_description"};
69
70			push @where, "c_source_notes = " . quote($attr->{c_source_notes})
71			if $attr->{"c_source_notes"};
72
73			push @where, "c_customer_id = " . quote($attr->{c_customer_id})
74			if $attr->{"c_customer_id"};
75
76			push @where, "c_customer_name = " . quote($attr->{c_customer_name})
77			if $attr->{"c_customer_name"};
78
79			push @where, "c_customer_phone = " . quote($attr->{c_customer_phone})
80			if $attr->{"c_customer_phone"};
81
82			push @where, "c_customer_email = " . quote($attr->{c_customer_email})
83			if $attr->{"c_customer_email"};
84
85			push @where, "c_customer_notes = " . quote($attr->{c_customer_notes})
86			if $attr->{"c_customer_notes"};
87
88			push @where, "c_type_id = " . quote($attr->{c_type_id})
89			if $attr->{"c_type_id"};
90
91			push @where, "c_type_description = " . quote($attr->{c_type_description})
92			if $attr->{"c_type_description"};
93
94			push @where, "c_type_notes = " . quote($attr->{c_type_notes})
95			if $attr->{"c_type_notes"};
96
97			push @where, "c_news_id = " . quote($attr->{c_news_id})
98			if $attr->{"c_news_id"};
99
100			push @where, "c_news_section LIKE " . quote ("\%$attr->{c_news_section}\%")
101			if $attr->{"c_news_section"};
102
103			push @where, "c_news_page LIKE " . quote ("\%$attr->{c_news_page}\%")
104			if $attr->{"c_news_page"};
105
106			push @where, "c_news_title LIKE " . quote ("\%$attr->{c_news_title}\%")
107			if $attr->{"c_news_title"};
108
109			push @where, "c_news_abstract LIKE " . quote ("\%$attr->{c_news_abstract}\%")
110			if $attr->{"c_news_abstract"};
111
112			push @where, "c_news_full_text LIKE " . quote ("\%$attr->{c_news_full_text}\%")
113			if $attr->{"c_news_full_text"};
114
115			push @where, "c_news_date_created = " . quote($attr->{c_news_date_created})
116			if $attr->{"c_news_date_created"};
117
118			push @where, "c_news_date_updated = " . quote($attr->{c_news_date_updated})
119			if $attr->{"c_news_date_updated"};
120
121			push @where, "c_news_date_deleted = " . quote($attr->{c_news_date_deleted})
122			if $attr->{"c_news_date_deleted"};
123
124
125			$query .= "WHERE " . join ("\nAND ", @where) . "\n" if ($#where > -1);
126
127			$query .= "ORDER BY c_news_title";
128
129			# Execute query
130			my $result = $conn->exec($query);
131
132			# Error checking
133			if ($conn->errorMessage) {
134			my $errorMessage = $conn->errorMessage;
135			die "Error executing query '$query':\n$errorMessage";
136			}
137
138			my @result = ();
139			while (my ($c_news_id,$c_source_fk,$c_customer_fk,$c_type_fk,$c_news_section,$c_news_page,$c_news_title,$c_news_abstract,$c_news_full_text) = $result->fetchrow) {
140			# FIXME: fetch using a hash?
141			push @result, {
142			c_news_id => $c_news_id,
143			c_source_fk => $c_source_fk,
144			c_customer_fk => $c_customer_fk,
145			c_type_fk => $c_type_fk,
146			c_news_section => $c_news_section,
147			c_news_page => $c_news_page,
148			c_news_title => $c_news_title,
149			c_news_abstract => $c_news_abstract,
150			c_news_full_text => $c_news_full_text
151			};
152			}
153
154			return @result;
155			}
156
157			###
158
159			sub view {
160			my($self, $attr) = @_;
161
162			my $query = "SELECT
163			c_source_id,
164			c_source_description,
165			c_source_notes,
166			c_customer_id,
167			c_customer_name,
168			c_customer_phone,
169			c_customer_email,
170			c_customer_notes,
171			c_type_id,
172			c_type_description,
173			c_type_notes,
174			c_news_id,
175			c_news_section,
176			c_news_page,
177			c_news_title,
178			c_news_abstract,
179			c_news_full_text,
180			c_news_date_created,
181			c_news_date_updated,
182			c_news_date_deleted
183			FROM view_c_news\n";
184
185			# restrict results
186			my @where = ();
187
188			push @where, "c_source_id = " . quote($attr->{c_source_id})
189			if $attr->{"c_source_id"};
190
191			push @where, "c_source_description = " . quote($attr->{c_source_description})
192			if $attr->{"c_source_description"};
193
194			push @where, "c_source_notes = " . quote($attr->{c_source_notes})
195			if $attr->{"c_source_notes"};
196
197			push @where, "c_customer_id = " . quote($attr->{c_customer_id})
198			if $attr->{"c_customer_id"};
199
200			push @where, "c_customer_name = " . quote($attr->{c_customer_name})
201			if $attr->{"c_customer_name"};
202
203			push @where, "c_customer_phone = " . quote($attr->{c_customer_phone})
204			if $attr->{"c_customer_phone"};
205
206			push @where, "c_customer_email = " . quote($attr->{c_customer_email})
207			if $attr->{"c_customer_email"};
208
209			push @where, "c_customer_notes = " . quote($attr->{c_customer_notes})
210			if $attr->{"c_customer_notes"};
211
212			push @where, "c_type_id = " . quote($attr->{c_type_id})
213			if $attr->{"c_type_id"};
214
215			push @where, "c_type_description = " . quote($attr->{c_type_description})
216			if $attr->{"c_type_description"};
217
218			push @where, "c_type_notes = " . quote($attr->{c_type_notes})
219			if $attr->{"c_type_notes"};
220
221			push @where, "c_news_id = " . quote($attr->{c_news_id})
222			if $attr->{"c_news_id"};
223
224			push @where, "c_news_section LIKE " . quote ("\%$attr->{c_news_section}\%")
225			if $attr->{"c_news_section"};
226
227			push @where, "c_news_page LIKE " . quote ("\%$attr->{c_news_page}\%")
228			if $attr->{"c_news_page"};
229
230			push @where, "c_news_title LIKE " . quote ("\%$attr->{c_news_title}\%")
231			if $attr->{"c_news_title"};
232
233			push @where, "c_news_abstract LIKE " . quote ("\%$attr->{c_news_abstract}\%")
234			if $attr->{"c_news_abstract"};
235
236			push @where, "c_news_full_text LIKE " . quote ("\%$attr->{c_news_full_text}\%")
237			if $attr->{"c_news_full_text"};
238
239			push @where, "c_news_date_created = " . quote($attr->{c_news_date_created})
240			if $attr->{"c_news_date_created"};
241
242			push @where, "c_news_date_updated = " . quote($attr->{c_news_date_updated})
243			if $attr->{"c_news_date_updated"};
244
245			push @where, "c_news_date_deleted = " . quote($attr->{c_news_date_deleted})
246			if $attr->{"c_news_date_deleted"};
247
248
249
250			$query .= "WHERE " . join ("\nAND ", @where) . "\n"
251			if ($#where > -1);
252
253			$attr->{"order_by"} \|\|= "c_news_title";
254			$query .= "ORDER BY " . $attr->{"order_by"} . "\n"
255			if defined $attr->{"order_by"};
256
257			$query .= "GROUP BY " . $attr->{"group_by"} . "\n"
258			if defined $attr->{"group_by"};
259
260			$attr->{"limit"} \|\|= 20;
261			$attr->{"offset"} \|\|= '0';
262
263			$query .= "LIMIT $attr->{'limit'} OFFSET $attr->{'offset'}\n";
264
265			# Execute query
266			my $result = $conn->exec($query);
267
268			# Error checking
269			if ($conn->errorMessage) {
270			my $errorMessage = $conn->errorMessage;
271			die "Error executing query '$query':\n$errorMessage";
272			}
273
274			my @result = ();
275			while (my ($c_source_id,$c_source_description,$c_source_notes,$c_customer_id,$c_customer_name,$c_customer_phone,$c_customer_email,$c_customer_notes,$c_type_id,$c_type_description,$c_type_notes,$c_news_id,$c_news_section,$c_news_page,$c_news_title,$c_news_abstract,$c_news_full_text,$c_news_date_created,$c_news_date_updated,$c_news_date_deleted) = $result->fetchrow) {
276			push @result, {
277			c_source_id => $c_source_id,
278			c_source_description => $c_source_description,
279			c_source_notes => $c_source_notes,
280			c_customer_id => $c_customer_id,
281			c_customer_name => $c_customer_name,
282			c_customer_phone => $c_customer_phone,
283			c_customer_email => $c_customer_email,
284			c_customer_notes => $c_customer_notes,
285			c_type_id => $c_type_id,
286			c_type_description => $c_type_description,
287			c_type_notes => $c_type_notes,
288			c_news_id => $c_news_id,
289			c_news_section => $c_news_section,
290			c_news_page => $c_news_page,
291			c_news_title => $c_news_title,
292			c_news_abstract => $c_news_abstract,
293			c_news_full_text => $c_news_full_text,
294			c_news_date_created => $c_news_date_created,
295			c_news_date_updated => $c_news_date_updated,
296			c_news_date_deleted => $c_news_date_deleted
297			};
298			}
299
300			return @result;
301			}
302
303			###
304
305			sub insert {
306			my($self, $attr) = @_;
307
308			my $query = "SELECT sp_ins_c_news (" .
309			quote($attr->{'c_source_fk'}) . ',' .
310			quote($attr->{'c_customer_fk'}) . ',' .
311			quote($attr->{'c_type_fk'}) . ',' .
312			quote($attr->{'c_news_section'}) . ',' .
313			quote($attr->{'c_news_page'}) . ',' .
314			quote($attr->{'c_news_title'}) . ',' .
315			quote($attr->{'c_news_abstract'}) . ',' .
316			quote($attr->{'c_news_full_text'}) .
317			")";
318
319			# Execute query
320			my $result = $conn->exec($query);
321
322			# Error checking
323			if ($conn->errorMessage) {
324			my $errorMessage = $conn->errorMessage;
325			die "Error executing query '$query':\n$errorMessage";
326			}
327			}
328
329			###
330
331			sub delete {
332			my($self, $attr) = @_;
333
334			my $c_news_id = $attr->{c_news_id};
335			my @c_news_id = split (/\0/,$c_news_id);
336
337			foreach my $id (@c_news_id) {
338			my $q_id = quote ($id);
339			my $query = "SELECT sp_del_c_news ($q_id)";
340
341			# Execute query
342			my $result = $conn->exec($query);
343
344			# Error checking
345			if ($conn->errorMessage) {
346			my $errorMessage = $conn->errorMessage;
347			die "Error executing query '$query':\n$errorMessage";
348			}
349			}
350			}
351
352			###
353
354			sub update {
355			my($self, $attr) = @_;
356
357			my $query = "SELECT sp_upd_c_news (" .
358			quote($attr->{'c_news_id'}) . ',' .
359			quote($attr->{'c_source_fk'}) . ',' .
360			quote($attr->{'c_customer_fk'}) . ',' .
361			quote($attr->{'c_type_fk'}) . ',' .
362			quote($attr->{'c_news_section'}) . ',' .
363			quote($attr->{'c_news_page'}) . ',' .
364			quote($attr->{'c_news_title'}) . ',' .
365			quote($attr->{'c_news_abstract'}) . ',' .
366			quote($attr->{'c_news_full_text'}) .
367			")";
368
369			# Execute query
370			my $result = $conn->exec($query);
371
372			# Error checking
373			if ($conn->errorMessage) {
374			my $errorMessage = $conn->errorMessage;
375			die "Error executing query '$query':\n$errorMessage";
376			}
377			}
378
379			###
380
381
382
383			sub count_by_c_source {
384			my($self, $attr) = @_;
385
386			my $query = "SELECT
387			c_source_description,
388			count_c_news_by_c_source.c_source_id,
389			count_c_news_by_c_source.count_c_news
390			FROM
391			count_c_news_by_c_source,
392			c_source
393			WHERE
394			count_c_news_by_c_source.c_source_id = c_source.c_source_id
395			ORDER BY
396			c_source_description";
397
398			# Execute query
399			my $result = $conn->exec($query);
400
401			# Error checking
402			if ($conn->errorMessage) {
403			my $errorMessage = $conn->errorMessage;
404			die "Error executing query '$query':\n$errorMessage";
405			}
406
407			my @result = ();
408			while (my ($c_source_description,
409			$c_source_id,
410			$count_c_news) = $result->fetchrow) {
411			# FIXME: fetch using a hash?
412			push @result, {
413			c_source_description => $c_source_description,
414			c_source_id => $c_source_id,
415			count_c_news => $count_c_news
416			};
417			}
418
419			return @result;
420
421
422			}
423
424			###
425
426
427			sub count_by_c_customer {
428			my($self, $attr) = @_;
429
430			my $query = "SELECT
431			c_customer_name,
432			count_c_news_by_c_customer.c_customer_id,
433			count_c_news_by_c_customer.count_c_news
434			FROM
435			count_c_news_by_c_customer,
436			c_customer
437			WHERE
438			count_c_news_by_c_customer.c_customer_id = c_customer.c_customer_id
439			ORDER BY
440			c_customer_name";
441
442			# Execute query
443			my $result = $conn->exec($query);
444
445			# Error checking
446			if ($conn->errorMessage) {
447			my $errorMessage = $conn->errorMessage;
448			die "Error executing query '$query':\n$errorMessage";
449			}
450
451			my @result = ();
452			while (my ($c_customer_name,
453			$c_customer_id,
454			$count_c_news) = $result->fetchrow) {
455			# FIXME: fetch using a hash?
456			push @result, {
457			c_customer_name => $c_customer_name,
458			c_customer_id => $c_customer_id,
459			count_c_news => $count_c_news
460			};
461			}
462
463			return @result;
464
465
466			}
467
468			###
469
470
471			sub count_by_c_type {
472			my($self, $attr) = @_;
473
474			my $query = "SELECT
475			c_type_description,
476			count_c_news_by_c_type.c_type_id,
477			count_c_news_by_c_type.count_c_news
478			FROM
479			count_c_news_by_c_type,
480			c_type
481			WHERE
482			count_c_news_by_c_type.c_type_id = c_type.c_type_id
483			ORDER BY
484			c_type_description";
485
486			# Execute query
487			my $result = $conn->exec($query);
488
489			# Error checking
490			if ($conn->errorMessage) {
491			my $errorMessage = $conn->errorMessage;
492			die "Error executing query '$query':\n$errorMessage";
493			}
494
495			my @result = ();
496			while (my ($c_type_description,
497			$c_type_id,
498			$count_c_news) = $result->fetchrow) {
499			# FIXME: fetch using a hash?
500			push @result, {
501			c_type_description => $c_type_description,
502			c_type_id => $c_type_id,
503			count_c_news => $count_c_news
504			};
505			}
506
507			return @result;
508
509
510			}
511
512			###
513
514
515			sub quote {
516			my $str = shift;
517			if ($str eq '') {
518			return "NULL";
519			} else {
520			$str =~ s/'/''/g; # ISO SQL2
521			return "'$str'";
522			}
523			}
524
525			1;
526
527			__END__
528
529			Clipping - Copyright 2003 PhPerl.com
530
531			This program is free software; you can redistribute it and/or
532			modify it under the terms of the GNU General Public License
533			as published by the Free Software Foundation; either version 2
534			of the License, or (at your option) any later version.
535
536			This program is distributed in the hope that it will be useful,
537			but WITHOUT ANY WARRANTY; without even the implied warranty of
538			MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
539
540			See the GNU General Public License for more details:
541
542			http://www.gnu.org/copyleft/gpl.html