bin/lib/c_type.pm

package c_type;

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

# Methods:
#
#   new
#   select
#   insert
#   delete
#   update
#
# Syntax:
#
#   my $c_type = new c_type;
#
#   $c_type->select(param1 => value1, param2, value2, ... );
#   $c_type->insert(param1 => value1, param2, value2, ... );
#   $c_type->delete(param1 => value1, param2, value2, ... );
#   $c_type->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_type_id,
                        c_type_description,
                        c_type_notes
                FROM c_type\n";

    # restrict results
    my @where = ();

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

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

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

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

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

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


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

    $query .= "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_id,$c_type_description,$c_type_notes) = $result->fetchrow) {
      # FIXME: fetch using a hash?
      push @result, {
        c_type_id => $c_type_id,
        c_type_description => $c_type_description,
        c_type_notes => $c_type_notes
      };
    }

    return @result;
}

###

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

    my $query = "SELECT
                        c_type_id,
                        c_type_description,
                        c_type_notes,
                        c_type_date_created,
                        c_type_date_updated,
                        c_type_date_deleted
                FROM view_c_type\n";

    # restrict results
    my @where = ();

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

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

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

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

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

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


    $query .= "WHERE " . join ("\nAND ", @where) . "\n"
           if ($#where > -1);
    
    $attr->{"order_by"} ||= "c_type_description";
    $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_type_id,$c_type_description,$c_type_notes,$c_type_date_created,$c_type_date_updated,$c_type_date_deleted) = $result->fetchrow) {
      push @result, {
        c_type_id => $c_type_id,
        c_type_description => $c_type_description,
        c_type_notes => $c_type_notes,
        c_type_date_created => $c_type_date_created,
        c_type_date_updated => $c_type_date_updated,
        c_type_date_deleted => $c_type_date_deleted
      };
    }

    return @result;
}

###

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

    my $query = "SELECT sp_ins_c_type (" .
                        quote($attr->{'c_type_description'}) . ',' .
                        quote($attr->{'c_type_notes'}) . 
                ")";

    # 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_type_id = $attr->{c_type_id};
    my @c_type_id = split (/\0/,$c_type_id);

    foreach my $id (@c_type_id) {
      my $q_id = quote ($id);
      my $query = "SELECT sp_del_c_type ($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_type (" .
                         quote($attr->{'c_type_id'}) . ',' .
                         quote($attr->{'c_type_description'}) . ',' .
                         quote($attr->{'c_type_notes'}) . 
                ")";

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

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

###


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_type;
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_type = new c_type;
18			#
19			# $c_type->select(param1 => value1, param2, value2, ... );
20			# $c_type->insert(param1 => value1, param2, value2, ... );
21			# $c_type->delete(param1 => value1, param2, value2, ... );
22			# $c_type->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_type_id,
51			c_type_description,
52			c_type_notes
53			FROM c_type\n";
54
55			# restrict results
56			my @where = ();
57
58			push @where, "c_type_id = " . quote($attr->{c_type_id})
59			if $attr->{"c_type_id"};
60
61			push @where, "c_type_description LIKE " . quote ("\%$attr->{c_type_description}\%")
62			if $attr->{"c_type_description"};
63
64			push @where, "c_type_notes LIKE " . quote ("\%$attr->{c_type_notes}\%")
65			if $attr->{"c_type_notes"};
66
67			push @where, "c_type_date_created = " . quote($attr->{c_type_date_created})
68			if $attr->{"c_type_date_created"};
69
70			push @where, "c_type_date_updated = " . quote($attr->{c_type_date_updated})
71			if $attr->{"c_type_date_updated"};
72
73			push @where, "c_type_date_deleted = " . quote($attr->{c_type_date_deleted})
74			if $attr->{"c_type_date_deleted"};
75
76
77			$query .= "WHERE " . join ("\nAND ", @where) . "\n" if ($#where > -1);
78
79			$query .= "ORDER BY c_type_description";
80
81			# Execute query
82			my $result = $conn->exec($query);
83
84			# Error checking
85			if ($conn->errorMessage) {
86			my $errorMessage = $conn->errorMessage;
87			die "Error executing query '$query':\n$errorMessage";
88			}
89
90			my @result = ();
91			while (my ($c_type_id,$c_type_description,$c_type_notes) = $result->fetchrow) {
92			# FIXME: fetch using a hash?
93			push @result, {
94			c_type_id => $c_type_id,
95			c_type_description => $c_type_description,
96			c_type_notes => $c_type_notes
97			};
98			}
99
100			return @result;
101			}
102
103			###
104
105			sub view {
106			my($self, $attr) = @_;
107
108			my $query = "SELECT
109			c_type_id,
110			c_type_description,
111			c_type_notes,
112			c_type_date_created,
113			c_type_date_updated,
114			c_type_date_deleted
115			FROM view_c_type\n";
116
117			# restrict results
118			my @where = ();
119
120			push @where, "c_type_id = " . quote($attr->{c_type_id})
121			if $attr->{"c_type_id"};
122
123			push @where, "c_type_description LIKE " . quote ("\%$attr->{c_type_description}\%")
124			if $attr->{"c_type_description"};
125
126			push @where, "c_type_notes LIKE " . quote ("\%$attr->{c_type_notes}\%")
127			if $attr->{"c_type_notes"};
128
129			push @where, "c_type_date_created = " . quote($attr->{c_type_date_created})
130			if $attr->{"c_type_date_created"};
131
132			push @where, "c_type_date_updated = " . quote($attr->{c_type_date_updated})
133			if $attr->{"c_type_date_updated"};
134
135			push @where, "c_type_date_deleted = " . quote($attr->{c_type_date_deleted})
136			if $attr->{"c_type_date_deleted"};
137
138
139
140			$query .= "WHERE " . join ("\nAND ", @where) . "\n"
141			if ($#where > -1);
142
143			$attr->{"order_by"} \|\|= "c_type_description";
144			$query .= "ORDER BY " . $attr->{"order_by"} . "\n"
145			if defined $attr->{"order_by"};
146
147			$query .= "GROUP BY " . $attr->{"group_by"} . "\n"
148			if defined $attr->{"group_by"};
149
150			$attr->{"limit"} \|\|= 20;
151			$attr->{"offset"} \|\|= '0';
152
153			$query .= "LIMIT $attr->{'limit'} OFFSET $attr->{'offset'}\n";
154
155			# Execute query
156			my $result = $conn->exec($query);
157
158			# Error checking
159			if ($conn->errorMessage) {
160			my $errorMessage = $conn->errorMessage;
161			die "Error executing query '$query':\n$errorMessage";
162			}
163
164			my @result = ();
165			while (my ($c_type_id,$c_type_description,$c_type_notes,$c_type_date_created,$c_type_date_updated,$c_type_date_deleted) = $result->fetchrow) {
166			push @result, {
167			c_type_id => $c_type_id,
168			c_type_description => $c_type_description,
169			c_type_notes => $c_type_notes,
170			c_type_date_created => $c_type_date_created,
171			c_type_date_updated => $c_type_date_updated,
172			c_type_date_deleted => $c_type_date_deleted
173			};
174			}
175
176			return @result;
177			}
178
179			###
180
181			sub insert {
182			my($self, $attr) = @_;
183
184			my $query = "SELECT sp_ins_c_type (" .
185			quote($attr->{'c_type_description'}) . ',' .
186			quote($attr->{'c_type_notes'}) .
187			")";
188
189			# Execute query
190			my $result = $conn->exec($query);
191
192			# Error checking
193			if ($conn->errorMessage) {
194			my $errorMessage = $conn->errorMessage;
195			die "Error executing query '$query':\n$errorMessage";
196			}
197			}
198
199			###
200
201			sub delete {
202			my($self, $attr) = @_;
203
204			my $c_type_id = $attr->{c_type_id};
205			my @c_type_id = split (/\0/,$c_type_id);
206
207			foreach my $id (@c_type_id) {
208			my $q_id = quote ($id);
209			my $query = "SELECT sp_del_c_type ($q_id)";
210
211			# Execute query
212			my $result = $conn->exec($query);
213
214			# Error checking
215			if ($conn->errorMessage) {
216			my $errorMessage = $conn->errorMessage;
217			die "Error executing query '$query':\n$errorMessage";
218			}
219			}
220			}
221
222			###
223
224			sub update {
225			my($self, $attr) = @_;
226
227			my $query = "SELECT sp_upd_c_type (" .
228			quote($attr->{'c_type_id'}) . ',' .
229			quote($attr->{'c_type_description'}) . ',' .
230			quote($attr->{'c_type_notes'}) .
231			")";
232
233			# Execute query
234			my $result = $conn->exec($query);
235
236			# Error checking
237			if ($conn->errorMessage) {
238			my $errorMessage = $conn->errorMessage;
239			die "Error executing query '$query':\n$errorMessage";
240			}
241			}
242
243			###
244
245
246
247			sub quote {
248			my $str = shift;
249			if ($str eq '') {
250			return "NULL";
251			} else {
252			$str =~ s/'/''/g; # ISO SQL2
253			return "'$str'";
254			}
255			}
256
257			1;
258
259			__END__
260
261			Clipping - Copyright 2003 PhPerl.com
262
263			This program is free software; you can redistribute it and/or
264			modify it under the terms of the GNU General Public License
265			as published by the Free Software Foundation; either version 2
266			of the License, or (at your option) any later version.
267
268			This program is distributed in the hope that it will be useful,
269			but WITHOUT ANY WARRANTY; without even the implied warranty of
270			MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
271
272			See the GNU General Public License for more details:
273
274			http://www.gnu.org/copyleft/gpl.html