/[clipping]/bin/lib/c_news.pm
This is repository of my old source code which isn't updated any more. Go to git.rot13.org for current projects!
ViewVC logotype

Contents of /bin/lib/c_news.pm

Parent Directory Parent Directory | Revision Log Revision Log


Revision 1.1.1.1 - (show annotations) (vendor branch)
Fri Jan 16 22:46:52 2004 UTC (20 years, 2 months ago) by dpavlin
Branch: MAIN, phperl
CVS Tags: r20040116, HEAD
Changes since 1.1: +0 -0 lines
initial import of downloaded version

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

  ViewVC Help
Powered by ViewVC 1.1.26