bin/lib/c_customer.pm

package c_customer;

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

# Methods:
#
#   new
#   select
#   insert
#   delete
#   update
#
# Syntax:
#
#   my $c_customer = new c_customer;
#
#   $c_customer->select(param1 => value1, param2, value2, ... );
#   $c_customer->insert(param1 => value1, param2, value2, ... );
#   $c_customer->delete(param1 => value1, param2, value2, ... );
#   $c_customer->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_customer_id,
                        c_customer_name,
                        c_customer_phone,
                        c_customer_email,
                        c_customer_notes
                FROM c_customer\n";

    # restrict results
    my @where = ();

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

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

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

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

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

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

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

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


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

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

    return @result;
}

###

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

    my $query = "SELECT
                        c_customer_id,
                        c_customer_name,
                        c_customer_phone,
                        c_customer_email,
                        c_customer_notes,
                        c_customer_date_created,
                        c_customer_date_updated,
                        c_customer_date_deleted
                FROM view_c_customer\n";

    # restrict results
    my @where = ();

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

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

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

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

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

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

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

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


    $query .= "WHERE " . join ("\nAND ", @where) . "\n"
           if ($#where > -1);
    
    $attr->{"order_by"} ||= "c_customer_name";
    $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_customer_id,$c_customer_name,$c_customer_phone,$c_customer_email,$c_customer_notes,$c_customer_date_created,$c_customer_date_updated,$c_customer_date_deleted) = $result->fetchrow) {
      push @result, {
        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_customer_date_created => $c_customer_date_created,
        c_customer_date_updated => $c_customer_date_updated,
        c_customer_date_deleted => $c_customer_date_deleted
      };
    }

    return @result;
}

###

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

    my $query = "SELECT sp_ins_c_customer (" .
                        quote($attr->{'c_customer_name'}) . ',' .
                        quote($attr->{'c_customer_phone'}) . ',' .
                        quote($attr->{'c_customer_email'}) . ',' .
                        quote($attr->{'c_customer_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_customer_id = $attr->{c_customer_id};
    my @c_customer_id = split (/\0/,$c_customer_id);

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