JH: Junco Code - Archives.pm

code=yes

package Archives;

use strict;

my $pt_db_source = Config::get_value_for("database_host");
my $pt_db_catalog = Config::get_value_for("database_name");
my $pt_db_user_id = Config::get_value_for("database_username");
my $pt_db_password = Config::get_value_for("database_password");

my $dbtable_content = Config::get_value_for("dbtable_content");
my $dbtable_users = Config::get_value_for("dbtable_users");

sub show_archives {
my $tmp_hash = shift;

my $username = $tmp_hash->{one};

my $str = "";
my @loop_data = _get_archives($username);
Web::set_template_name("archives");
Web::set_template_loop_data("archives", \@loop_data);
if ( $username ) {
Web::set_template_variable("userblog", 1);
Web::set_template_variable("userarchives", $username);
$str = "for $username\'s workspace";
}
Web::display_page("Archives $str");
}

sub _get_archives {
my $username = shift;

my @months = qw(xxx January February March April May June July August September October November December);

my $offset = Utils::get_time_offset();

my @loop_data;

my $db = Db->new($pt_db_catalog, $pt_db_user_id, $pt_db_password);
Web::report_error("system", "Error connecting to database.", $db->errstr) if $db->err;

my $id=0;
my $sql;

$sql = "select distinct date_format(date_add(createddate, interval $offset hour), '%Y') as year, ";
$sql .= "date_format(date_add(createddate, interval $offset hour), '%m') as month ";

if ( $username ) {
$id = User::get_userid($username);
$sql .= "from $dbtable_content where authorid = $id and type in ('b') and status in ('o') order by year desc, month desc";
} else {
$sql .= "from $dbtable_content where type in ('b') and status in ('o') order by year desc, month desc";
}

$db->execute($sql);

Web::report_error("system", "(35) Error executing SQL", $db->errstr) if $db->err;

my $cgi_app = Config::get_value_for("cgi_app");

while ( $db->fetchrow ) {
my %hash;

$hash{year} = $db->getcol("year");
$hash{month} = $db->getcol("month");
$hash{monthyear} = "$months[$hash{month}] $hash{year}";
$hash{userarchives} = $username if ( $username );
$hash{cgi_app} = $cgi_app;
push(@loop_data, \%hash);
}
Web::report_error("system", "Error retrieving data from database.", $db->errstr) if $db->err;

$db->disconnect;
Web::report_error("system", "Error disconnecting from database.", $db->errstr) if $db->err;

return @loop_data;
}

1;

#juncocode

showintro=no

created Apr 08, 2013