view release on metacpan or  search on metacpan

example/DB/Default.pm  view on Meta::CPAN

    my ($username, $password) = @_;
    
    my ($user_id) = $self->get_one_row(
            sql => "SELECT id FROM CMSUser WHERE username = ? AND password_md5 = ?",
            bind => [ $username, md5_hex($password) ],
            );
    
    return $user_id;
}

sub get_asset {
    my $self = shift;
    my %p = @_;
    
    my @bind;
    push @bind, $p{asset_id} if $p{asset_id};
    push @bind, $p{status} if $p{status};
    push @bind, $p{type} if $p{type};

    return $self->get_rows_hashref(
            sql => "SELECT WebItem.id,
                           ItemType.short_desc    AS item_type,
                           WebItem.item_type_id,
                           ItemStatus.description AS item_status,
                           WebItem.item_status_id,
                           ItemGroup.description  AS item_group,
                           WebItem.item_group_id,
                           to_char(WebItem.date_created, 'Month DD, YYYY') AS date_created,
                           to_char(WebItem.date_live, 'Month DD, YYYY') AS date_live,
                           to_char(WebItem.date_live, 'YYYY') AS live_year,
                           to_char(WebItem.date_live, 'MM') AS live_mon,
                           to_char(WebItem.date_live, 'DD') AS live_day,
                           to_char(WebItem.date_live, 'HH') as live_hour,
                           to_char(WebItem.date_expires, 'Month DD, YYYY') AS date_expires,
                           to_char(WebItem.date_expires, 'YYYY') AS expires_year,
                           to_char(WebItem.date_expires, 'MM') AS expires_mon,
                           to_char(WebItem.date_expires, 'DD') AS expires_day,
                           WebItem.title,
                           WebItem.link,
                           WebItem.subtitle,
                           WebItem.location,
                           WebItem.booth,
                           WebItem.body
                    FROM WebItem 
                    JOIN ItemType 
                      ON WebItem.item_type_id = ItemType.id
                    JOIN ItemStatus
                      ON WebItem.item_status_id = ItemStatus.id
                    JOIN ItemGroup
                      ON WebItem.item_group_id = ItemGroup.id
                    WHERE 1 = 1
                    " .
                  ($p{asset_id} ? " AND WebItem.id = ? " : "") .
                  ($p{status} ? " AND ItemStatus.description = ? " : "") .
                  ($p{type} ? " AND ItemType.short_desc = ? " : "") .
                  ($p{include_expired} ? "" : "AND WebItem.date_expires > now()"),
            (@bind ? (bind => \@bind) : ()),
            );
}

sub update_announce {
    my $self = shift;
    my %p = @_;
    
    $p{expires} = $self->sql_date_struct($p{expires});
    $p{live} = $self->sql_date_struct($p{live});
    
    $self->do_sql(
            sql => "UPDATE WebItem
                    SET title = ?,
                        link = ?,
                        date_expires = ?,
                        date_live = ?
                    WHERE id = ?",
            bind => [ @p{qw(title link expires live id)} ],
            );
}

sub update_news {
    my $self = shift;
    my %p = @_;
    
    $p{expires} = $self->sql_date_struct($p{expires});
    $p{live} = $self->sql_date_struct($p{live});
    
    $self->do_sql(
            sql => "UPDATE WebItem
                    SET title = ?,
                        link = ?,
                        date_expires = ?,
                        date_live = ?
                    WHERE id = ?",
            bind => [ @p{qw(title link expires live id)} ],
            );
}

sub update_event {
    my $self = shift;
    my %p = @_;
    
    $p{expires} = $self->sql_date_struct($p{expires});
    $p{live} = $self->sql_date_struct($p{live});
    
    $self->do_sql(
            sql => "UPDATE WebItem
                    SET title = ?,
                        link = ?,
                        date_expires = ?,
                        date_live = ?,
                        location = ?,
                        booth = ?
                    WHERE id = ?",
            bind => [ @p{qw(title link expires live location booth id)} ],
            );
}

sub update_pr {
    my $self = shift;
    my %p = @_;
    
    $p{expires} = $self->sql_date_struct($p{expires});
    $p{live} = $self->sql_date_struct($p{live});
    
    $self->do_sql(
            sql => "UPDATE WebItem
                    SET title = ?,
                        date_expires = ?,
                        date_live = ?,
                        subtitle = ?,
                        location = ?,
                        body = ?
                    WHERE id = ?",
            bind => [ @p{qw(title expires live subtitle location body id)} ],
            );
}

sub update_asset_column {
    my $self = shift;
    my ($id, $column, $value) = @_;
    
    $self->do_sql(
                sql => "UPDATE WebItem
                        SET $column = ?
                        WHERE id = ?",
                bind => [ $value, $id ],
                );
}

sub create_asset {
    my $self = shift;
    my %p = @_;
    
    $p{expires} = $self->sql_date_struct($p{expires} || {year => 2030, month => 1, day_of_month => 1 });
    $p{live} = $self->sql_date_struct($p{live} || { year => 1970, month => 1, day_of_month => 1 });
    
    # get defaults
    my ($item_group_id, $item_status_id, $item_type_id) = 
        $self->get_one_row(
            sql => "SELECT ItemGroup.id AS itemgroup_id, 
                            ItemStatus.id AS itemstatus_id,
                            ItemType.id AS itemtype_id
                    FROM ItemGroup, ItemStatus, ItemType
                    WHERE ItemStatus.description = 'Initial Edit'
                    AND   ItemGroup.description = 'None'
                    AND   ItemType.short_desc = ?",
            bind => [ $p{asset_type} ],
        );
    
    my $next_id = $self->get_next_pk(table => "WebItem");
    
    $self->do_sql(
            sql => "INSERT INTO WebItem (id, item_type_id,
                    item_status_id, item_group_id,
                    date_created, date_live, date_expires,
                    title, link, subtitle, location,
                    booth, body )
                    VALUES ( ?, ?,
                    ?, ?, 
                    now(), ?, ?,
                    ?, ?, ?, ?,
                    ?, ? )",
            bind => [ $next_id, $item_type_id, $item_status_id, $p{item_group_id} || $item_group_id,
                      @p{qw(live expires title link subtitle location booth body)} ],
            );
    
    return $next_id;
}

sub get_create_pages {
    my $self = shift;
    
    my @rows = $self->get_rows(
            sql => "SELECT short_desc, create_page FROM ItemType ORDER BY id"
            );
    
    my @results;
    

example/DB/Default.pm  view on Meta::CPAN

                sql => "SELECT ItemType.view_page
                        FROM ItemType
                        JOIN WebItem
                          ON WebItem.item_type_id = ItemType.id
                        WHERE WebItem.id = ?",
                bind => $p{asset_id},
                );
    }
    return $page;
}

sub list_users {
    my $self = shift;
    
    return $self->get_rows_hashref(
            sql => "SELECT * FROM CMSUser ORDER BY super_user, last_name, first_name",
            );
}

sub is_super_user {
    my $self = shift;
    my $user_id = shift;
    
    return $self->get_one_row(
            sql => "SELECT super_user FROM CMSUser WHERE id = ?",
            bind => $user_id,
            );
}

use Digest::MD5 qw(md5_hex);

sub add_user {
    my $self = shift;
    my %p = @_;
    
    $p{password_md5} = md5_hex($p{password});
    $p{super_user} = $p{super_user} ? 't' : 'f';
    
    $self->do_sql(
            sql => "INSERT INTO CMSUser 
                    (id, username, password_md5, 
                    first_name, last_name, email, super_user)
                    VALUES
                    (nextval('CMSUser_seq'), ?, ?, ?, ?, ?, ?)",
            bind => [ @p{qw(username password_md5 first_name last_name email super_user)} ],
            );
}

sub get_user {
    my $self = shift;
    my $id = shift;
    
    return {
        $self->get_one_row_hash(
                sql => "SELECT * FROM CMSUser WHERE id = ?",
                bind => $id,
                )
            };
}

sub update_user {
    my $self = shift;
    my %p = @_;
    
    if ($p{password}) {
        $p{password_md5} = md5_hex($p{password});
    }
    
    $p{super_user} = $p{super_user} ? 't' : 'f';
    
    $self->do_sql(
                sql => "UPDATE CMSUser
                        SET first_name = ?,
                            last_name = ?,
                            email = ?,
                            super_user = ?
                            " .
                        ($p{password_md5} ? ", password_md5 = ?" : "") .
                        " WHERE id = ?",
                bind => [ @p{qw(first_name last_name email super_user)}, ($p{password_md5} ? ($p{password_md5}) : ()), $p{id} ],
                );
}

sub get_user_id {
    my $self = shift;
    my $username = shift;
    
    return $self->get_one_row(
            sql => "SELECT id FROM CMSUser WHERE username = ?",
            bind => $username,
            );
}

sub log {
    my $self = shift;
    my $log_text = join('', @_);

    my $next_id = $self->get_next_pk(table => "CMSLog");
    
    my $user_id = $self->get_user_id(Example::User::get_user());
    
    $self->do_sql(
            sql => "INSERT INTO CMSLog (id, user_id, log_detail)
                    VALUES ( ?, ?, ? )",
            bind => [ $next_id, $user_id, $log_text ],
            );
}

sub set_status {
    my $self = shift;
    
    my ($status, $asset_id) = @_;
    
    if ($status =~ /\D/) {
        # status is a description
        $self->do_sql(
                sql => "UPDATE WebItem SET item_status_id = 
                        (SELECT ItemStatus.id FROM ItemStatus 
                         WHERE ItemStatus.description = ?)
                        WHERE WebItem.id = ?",
                bind => [ $status, $asset_id ],