#!/usr/bin/perl use strict; push(@INC,'/home/oc/cgi-bin/'); use lib '/home/oc/cgi-bin/'; use OC; use CGI qw/:standard/; use CGI::Carp qw(fatalsToBrowser); use HTML::Template; #use Tie::IxHash; #use Digest::MD5 qw(md5_hex); #use Mail::Sender; use vars qw/ %CONFIG @NOTES %form %cookie /; sub ConnectToDatabase { if (!defined $::db) { $::db = DBI->connect("DBI:mysql:$CONFIG{db_name}:$CONFIG{db_host}: $CONFIG{db_port}",$CONFIG{db_username},$CONFIG{db_password}) || die "Can't connect to database server: $CONFIG{db_name} $CONFIG{db_host} $CONFIG{db_port}"; } return $::db; } sub PrepareSQL { my ($str) = (@_); $::sth_wishlist=$::db->prepare($str); } sub ExecuteSQL { $::sth_wishlist->execute(@_) || die "Could not execute SQL statement"; } sub SendSQL { PrepareSQL(@_); ExecuteSQL(); } sub FetchSQLData { return $::sth_wishlist->fetchrow_array(); } sub Note { my %note=(); $note{note}=$_[0]; push(@NOTES,\%note); } sub Result { my ($msg,$params)=@_; my %param=(); if (defined $params) { %param=%{$params}; } print header(-type => 'text/html',-charset=>'') if ($param{no_header} eq ''); my $template=HTML::Template->new( filename=>$CONFIG{tmpl_wishlist_result_file}, die_on_bad_params=>0, loop_context_vars=>1, global_vars=>0, shared_cache=>0, ); $msg.="
Back to wishlist"; $template->param(result=>$msg); print $template->output; exit; } sub Create_Page { my ($project)=@_; my @list=(); my %fullname=(); SendSQL("select username, fullname from account"); while (my @row=FetchSQLData()) { $row[1]=$row[0] if ($row[1] eq ''); $fullname{$row[0]}=$row[1]; } my %display_name=(); SendSQL("select name,display_name from project"); while(my @result=FetchSQLData()) { $display_name{$result[0]}=$result[1]; } SendSQL("select id,author,email,type,status,title,body,links,date,project, assigned_to,rated1,rated2,rated3,rated4,rated5,comment from wishlist order by project desc"); while (my @result=FetchSQLData()) { my %entry=(); ($entry{id},$entry{author},$entry{email},$entry{type},$entry{status},$entry{title},$entry{body}, $entry{links},$entry{date},$entry{project},$entry{assigned_to},$entry{rated1},$entry{rated2}, $entry{rated3},$entry{rated4},$entry{rated5},$entry{comment})=@result; $entry{date}=OC::Time_To_Str($entry{date},'nice'); my $link=OC::Store_Email($entry{email}); $link=$CONFIG{post_url}."?to=$link"; $entry{email}=$link; foreach (1..5) { $entry{"rated$_"}=0 unless (defined $entry{"rated$_"}); } $entry{rating}=OC::Average($entry{rated1},$entry{rated2},$entry{rated3},$entry{rated4},$entry{rated5}); my @tmpl_links=(); my @links=split(/\n/,$entry{links}); foreach (@links) { my %link=(); ($link{url},$link{desc})=split(/ /,$_,2); unless ($link{url}=~m!^http://!) { $link{url}="http://$link{url}"; } $link{desc}=$link{url} if ($link{desc} eq ''); push(@tmpl_links,\%link); } my %assigned_to=(); foreach (split(/ +/,$entry{assigned_to})) { $assigned_to{$_}=$fullname{$_}; } my @assigned_to=(); foreach (keys %assigned_to) { my %item=(); $item{link}="$CONFIG{account_url}/$_"; $item{fullname}="$assigned_to{$_}"; push(@assigned_to,\%item); } if ($entry{project} ne '') { $entry{project_link}="$CONFIG{project_url}/$entry{project}"; $entry{project_display_name}=$display_name{$entry{project}}; } $entry{assigned_to}=\@assigned_to; $entry{links}=\@tmpl_links; $entry{add_link}="$CONFIG{wishlist_url}?cmd=work&subcmd=add&id=$entry{id}"; $entry{remove_link}="$CONFIG{wishlist_url}?cmd=work&subcmd=remove&id=$entry{id}"; $entry{edit_link}="$CONFIG{wishlist_url}?cmd=edit&id=$entry{id}"; $entry{self_url}=$CONFIG{wishlist_url}; $entry{vote_count}=$entry{rated1}+$entry{rated2}+$entry{rated3}+$entry{rated4}+$entry{rated5}; $entry{color1}=$OC::CONFIG{color}{bg0}; $entry{color2}=$OC::CONFIG{color}{bg2}; push(@list,\%entry); } my $add_link="$CONFIG{wishlist_url}?cmd=add"; my $template=HTML::Template->new( filename=>$CONFIG{tmpl_wishlist_list_file}, die_on_bad_params=>0, loop_context_vars=>1, global_vars=>0, shared_cache=>0, ); $template->param( project=>$project, add_link=>$add_link, list=>\@list, self_url=>$CONFIG{wishlist_url}, ); my $file="$CONFIG{wishlist_folder}/index.shtml"; if ($project ne '') { $file="$CONFIG{wishlist_folder}/$project.shtml"; } if (open(FILE,">$file")) { print FILE $template->output; close(FILE); } else { die "cannot open file: $file"; } } sub Add { my $project=$form{project}; my ($display_name,$name); if ($project ne '') { SendSQL("select display_name,name from project where name='$project'"); ($display_name,$name)=FetchSQLData(); $display_name=$name if ($display_name eq ''); if ($name) { Note("Project doesn't exist: $project"); } } my $template=HTML::Template->new( filename=>$CONFIG{tmpl_wishlist_add_file}, die_on_bad_params=>0, loop_context_vars=>1, global_vars=>0, shared_cache=>0, ); if ($form{save} ne '') { my $error=0; foreach ('author','email','type','title','body') { if ($form{$_} eq '') { $error=1; $template->param("error_$_"=>1); } } unless ($form{email}=~m/^.+\@.+\..+/) { $error=1; $template->param(error_email=>1); } my $assigned_to=""; if ($form{type} eq 'proposal' || $form{type} eq 'reminder') { if ($cookie{username} ne '') { $assigned_to=$cookie{username}; } else { $error=1; $template->param("error_type_no_username"=>1); } } unless ($error) { PrepareSQL("insert into wishlist (author,email,type,title,body,links,date,project,assigned_to) values (?,?,?,?,?,?,?,?,?)"); ExecuteSQL($form{author},$form{email},$form{type},$form{title},$form{body}, $form{links},time,$form{project},$assigned_to); Note("Entry successfuly added!"); } } foreach (keys %form) { $template->param($_=>$form{$_}); } $template->param( notes=>\@NOTES, project=>$project, project_display_name=>$display_name, list_all_link=>$CONFIG{wishlist_list_url}, self_url=>$CONFIG{wishlist_url}, ); print header(-type => 'text/html',-charset=>''); print $template->output; } sub Edit { @NOTES=(); my $id=$form{id}; unless (OC::Login($cookie{username},$cookie{password})) { Result("You must log in first!"); } unless ($form{id}=~m!^\d+$!) { Result("Invalid id: $form{id}"); } my %display_name=(); SendSQL("select project.name,project.display_name from project,project_maintainers where project.name=project_maintainers.project and project_maintainers.username='$cookie{username}'"); while (my @result=FetchSQLData()) { $display_name{$result[0]}=$result[1]; } SendSQL("select id,author,email,type,title,body,links,date,project,assigned_to,comment from wishlist where id=$id"); ($id, my($author,$email,$type,$title,$body,$links,$date,$project,$assigned_to,$comment))=FetchSQLData; my @assigned_to=split(/ +/,$assigned_to); my $found=0; foreach (@assigned_to) { if ($_ eq $cookie{username}) { $found++; } } unless ($found) { Result("Only developers working on this $type can edit it."); } unless ($id=~m!^\d+$!) { Result("Invalid id: $form{id}"); } if ($form{subcmd} eq 'comment') { PrepareSQL("update wishlist set comment=? where id=?"); ExecuteSQL($form{comment},$form{id}); Note("Comment updated successfully."); } elsif ($form{subcmd} eq 'unlink') { SendSQL("update wishlist set project='' where id=$id"); Note("Entry unlinked successfully."); } elsif ($form{subcmd} eq 'link') { if (exists $display_name{$form{project}}) { SendSQL("update wishlist set project='$form{project}' where id=$id"); Note("Entry linked successfully."); } else { Note("Error: you are not maintainer of project: $form{project}"); } } SendSQL("select id,author,email,type,title,body,links,date,project,assigned_to,comment from wishlist where id=$id"); ($id,$author,$email,$type,$title,$body,$links,$date,$project,$assigned_to,$comment)=FetchSQLData; my $template=HTML::Template->new( filename=>$CONFIG{tmpl_wishlist_edit_file}, die_on_bad_params=>0, loop_context_vars=>1, global_vars=>0, shared_cache=>0, ); my @projects=(); foreach my $key (keys %display_name) { my %row=(); $row{project}=$key; $row{project_display_name}=$display_name{$key}; push(@projects,\%row); } my $project_link="$CONFIG{project_url}/$project"; my $unlink_link="$CONFIG{wishlist_url}?cmd=edit&subcmd=unlink&id=$id"; $comment=~s!param( projects=>\@projects, notes=>\@NOTES, page_title=>1, title=>$title, body=>$body, type=>$type, project=>$project, project_link=>$project_link, unlink_link=>$unlink_link, project_display_name=>$display_name{$project}, comment=>$comment, id=>$id, list_all_link=>$CONFIG{wishlist_list_url}, self_url=>$CONFIG{wishlist_url}, ); print header(-type => 'text/html',-charset=>''); print $template->output; } sub Assign { unless (OC::Login($cookie{username},$cookie{password})) { Result("You must log in first!"); } SendSQL("select project,assigned_to,type from wishlist where id=$form{id}"); my ($project,$assigned_to,$type)=(undef,undef,undef); unless (($project,$assigned_to,$type)=FetchSQLData()) { Result("Invalid $type id: $form{id}"); } if ($project ne '') { SendSQL("select count(username) from project_maintainers where project='$project' and username='$cookie{username}'"); unless (FetchSQLData>0) { Result("Only project's maintainers can work on this type!"); } } my %assigned_to=(); foreach (split(/ +/,$assigned_to)) { $assigned_to{$_}=1; if ($_ eq $cookie{username} && $form{subcmd} eq 'add') { Result("You are already assigned to work on this $type."); } } my @assigned_to=(); foreach (keys %assigned_to) { unless ($form{subcmd} eq 'remove' && $_ eq $cookie{username}) { push(@assigned_to,$_); } } if ($form{subcmd} eq 'add') { push(@assigned_to,$cookie{username}); } $assigned_to=join(' ',@assigned_to); SendSQL("update wishlist set assigned_to='$assigned_to' where id=$form{id}"); Create_Page($form{project}); if ($form{subcmd} eq 'add') { Result("You have been successfully added to the list."); } elsif ($form{subcmd} eq 'remove') { Result("You have been successfully removed from the list."); } } sub Rate { unless ($form{value}=~m/^[12345]{1,1}$/) { Result("Invalid rate value: $form{value}"); } my ($result,$type); SendSQL("select id,type from wishlist where id=$form{id}"); unless (($result,$type)=FetchSQLData) { Result("Invalid $type id: $form{id}"); } SendSQL("select rated$form{value} from wishlist where id=$form{id}"); my $result; if ($cookie{"wishlist_$form{id}"} ne '') { Result("You already reted this $type."); } $result++; SendSQL("update wishlist set rated$form{value}=$result where id=$form{id}"); my $cookie=cookie( -name=>"wishlist_$form{id}", -value=>"yes", ); print header( -type => 'text/html', -charset=>'', -cookie=>[$cookie], ); my %param=(); $param{no_header}=1; Create_Page($form{project}); Result("Thanks for rating this $type.",\%param); } sub Main { ConnectToDatabase; my $query=new CGI; foreach ($query->param) { $form{$_}=$query->param($_); } foreach ($query->cookie) { $cookie{$_}=$query->cookie($_); } %CONFIG=%OC::CONFIG; if ($form{cmd} eq 'add') { Add; } elsif ($form{cmd} eq 'work') { Assign; } elsif ($form{cmd} eq 'edit') { Edit; } elsif ($form{cmd} eq 'rate') { Rate; } else { print header; print "Invalid command!"; } Create_Page(); } Main;