Vyššie na [Platon] / web-apps / call-centre-system / modules

CALL CENTRE SYSTEM added into CVS.

#
# modules/Customer.pm
#
# Developed by Ondrej Jombik <nepto@platon.sk>
# Copyright (c) 2004 Platon SDG, http://platon.sk/
# Licensed under terms of GNU General Public License.
# All rights reserved.
#
# Changelog:
# 17/01/2004 - created
#

# $Platon$

package Customer;

use strict;
use Carp;
use vars qw($VERSION @ISA @EXPORT $AUTOLOAD);

@ISA     = qw(Exporter);
@EXPORT  = qw( );
$VERSION = '0.1';

use dbh_query;
use User;

sub new    # {{{
{
    my $this  = shift;
    my $class = ref($this) || $this;
    my $self  = {};
    bless $self, $class;

    return $self;
}          # }}}

sub exist  # {{{
{
    my $cid   = shift;
    my $query = 'SELECT COUNT(*) FROM customers WHERE cid = ' . dbh_quote($cid);
    return dbh_query( $query, 'SCALAR' ) == 1;
}          # }}}

sub get    # {{{
{
    my $cid   = shift;
    my $query = 'SELECT c.*,'
      . ' u1.login AS created_by_login, u2.login AS changed_by_login FROM customers c'
      . ' LEFT JOIN users u1 ON c.created_by = u1.uid'
      . ' LEFT JOIN users u2 ON c.changed_by = u2.uid'
      . ' WHERE c.cid = ' . dbh_quote($cid);
    my $customer = ( dbh_query( $query, 'HASHREF' ) )[0];
    $customer->{'created_by_login'} ||= User::getAnnonymousLogin();
    $customer->{'changed_by_login'} ||= User::getAnnonymousLogin();
    return $customer;
}          # }}}

sub set    # {{{
{
    my $cid       = shift;
    my $data      = shift;
    my $cur_login = shift;
    my %real_data = ();
    my $key;
    my $val;
    foreach $key ( keys %$data ) {
        $val = $data->{$key};
        next if ( $key !~ /^(ico|name|website|phone|person|notes)$/ );
        $real_data{$key} = dbh_quote($val);
    }
    if ( scalar keys %real_data ) {
        $real_data{'changed_by'}   = dbh_quote($cur_login);
        $real_data{'changed_date'} = 'SYSDATE()';
        my $query;
        if ( Customer::exist($cid) ) {
            my $delim = ' ';
            $query = 'UPDATE customers SET';
            foreach $key ( keys %real_data ) {
                $val = $real_data{$key};
                $query .= $delim;
                $query .= $key;
                $query .= '=';
                $query .= $val;
                $delim = ',' if $delim ne ',';
            }
            $query .= ' WHERE cid = ' . dbh_quote($cid)
        } else {
            $query = sprintf( 'INSERT INTO customers'
                  . '(ico, name, website, phone, person, notes,'
                  . ' created_by, created_date, changed_by, changed_date)'
                  . ' VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)',
                $real_data{'ico'}, $real_data{'name'}, $real_data{'website'},
                $real_data{'phone'}, $real_data{'person'}, $real_data{'notes'},
                $real_data{'changed_by'}, $real_data{'changed_date'},
                $real_data{'changed_by'}, $real_data{'changed_date'} );
        }
        dbh_query( $query, 'UNDEF' );
    }
}    # }}}

sub delete    # {{{
{
    my $cid   = shift;
    my $query = 'DELETE FROM customers WHERE cid = ' . dbh_quote($cid);
    dbh_query( $query, 'UNDEF' );
}             # }}}

sub getCount  # {{{
{
    my $query = 'SELECT COUNT(*) FROM customers';
    return dbh_query( $query, 'SCALAR' );
}             # }}}

sub getAll    # {{{
{
    return Customer::getFilteredList( undef, undef );
}             # }}}

sub getFilteredList    # {{{
{
    my $where = Customer::_createWhere(shift);
    my $order = Customer::_createOrder(shift);
    my $query = 'SELECT * FROM customers';
    $query .= " WHERE $where"    if ( length $where );
    $query .= " ORDER BY $order" if ( length $order );
    my $ret = dbh_query( $query, 'HASHREF' );
    my @ids = ();
    foreach (@$ret) {
        push @ids, $_->{'cid'};
    }
    if ( scalar(@ids) ) {
        $query = 'SELECT c.cid, count(*) AS cnt,'
          . ' SUBSTRING(MAX(a.call_date),1,10) AS call_date'
          . ' FROM customers c LEFT JOIN calls a ON c.cid = a.cid'
          . ' WHERE c.cid IN (' . join ( ',', @ids ) . ')'
          . ' GROUP BY c.cid';
        my $tmp1 = dbh_query( $query, 'HASHARRAYREF' );
        $query = 'SELECT c.cid, count(*) AS cnt,'
          . ' SUBSTRING(MAX(o.changed_by),1,10) AS changed_by'
          . ' FROM customers c LEFT JOIN orders o ON c.cid = o.cid'
          . ' WHERE c.cid IN (' . join ( ',', @ids ) . ')'
          . ' GROUP BY c.cid';
        my $tmp2 = dbh_query( $query, 'HASHARRAYREF' );

        for ( $_ = 0 ; $_ < scalar(@$ret) ; $_++ ) {
            my $cid        = int $ret->[$_]{'cid'};
            my $calls      = $tmp1->{$cid}[0];
            my $orders     = $tmp2->{$cid}[0];
            my $last_call  = $tmp1->{$cid}[1];
            my $last_order = $tmp2->{$cid}[1];
            if ( !defined $last_call ) {
                $calls     = 0;
                $last_call = '';
            }
            if ( !defined $last_order ) {
                $orders     = 0;
                $last_order = '';
            }

            #print STDERR "XXX: $calls, $last_call, $orders, $last_order\n";
            #print STDERR "CID: $cid\n";
            #print STDERR "XXX: " . $tmp1->{$cid}[2] . "|" . $calls . "\n";
            $ret->[$_]{'calls'}      = $calls;
            $ret->[$_]{'last_call'}  = $last_call;
            $ret->[$_]{'orders'}     = $orders;
            $ret->[$_]{'last_order'} = $last_order;
        }
    }

    return $ret;
}    # }}}

sub getNameList    # {{{
{
    my $query = 'SELECT cid, name FROM customers';
    return dbh_query( $query, 'HASH' );
}                  # }}}

sub _createWhere   # {{{
{
    my $filter = shift;
    my @ret_ar = ();
    my $key;
    my $val;
    foreach $key ( keys %$filter ) {
        $val = $filter->{$key};
        next if ( $key !~ /^(cid|ico|name|website|phone)$/ );
        next if ( !length $val );
        if ( $key eq 'cid' ) {
            push @ret_ar, sprintf( '%s = %d', $key, $val ) if ( $val =~ /\d+/ );
            next;
        }
        push @ret_ar, sprintf( 'LOWER(%s) LIKE LOWER(%s)',
            $key, dbh_quote( '%' . $val . '%' ) );
    }
    return join ( ' AND ', @ret_ar );
}    # }}}

sub _createOrder    # {{{
{
    my $key = shift;
    return '' if ( not defined $key );
    return '' if ( $key !~ /^[-]?(cid|ico|name|website|phone)$/ );
    return substr( $key, 1 ) . ' DESC' if ( substr( $key, 0, 1 ) eq '-' );
    return $key;
}                   # }}}

1;

__END__

=head1 NAME

Customer - <<<description of module>>>

=head1 SYNOPSIS

use Customer;

my $xxx = new Customer;

=head1 DESCRIPTION

The Customer module allows you ...
<<<your description here>>>

=head2 EXPORT

<<here describe exported methods>>>

=head1 SEE ALSO

=head1 AUTHORS

Ondrej Jombik, <nepto@platon.sk>

=cut




# vim: ts=4
# vim600: fdm=marker fdl=0 fdc=3