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	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