ALTER TABLE ad_info add column judged int(1) default 0;
ALTER TABLE ad_info add column reason text;

update ad_info set judged = 1 where active = 1;

UPDATE blocks set block = CONCAT(block, ',\nredirect') WHERE bid='opcodes';
UPDATE blocks set block = CONCAT(block, ',\nredirect=/ad_id/,') WHERE bid='op_templates';

UPDATE blocks set block = '<table bgcolor=\"#ffffff\" fgcolor=\"#000000\" border=1 cellpadding=1 cellspacing=0 width=\"100%\">\r\n<tr><td>\r\n<table bgcolor=\"#000000\" fgcolor=\"#000000\" border=0 cellpadding=1 cellspacing=0 width=\"100%\">\r\n<tr><td>\r\n<TABLE bgcolor=\"FFFFFF\" fgcolor=\"000000\" border=\"0\" cellpadding=\"2\" cellspacing=\"0\" width=\"100%\">\r\n<TR><TD><a href=\"%%LINK_URL%%\">%%norm_font%%%%TITLE%%%%norm_font_end%%</a></TD></TR>\r\n<TR><TD>%%norm_font%%%%TEXT1%%%%norm_font_end%%</TD></TR>\r\n</TABLE>\r\n</td></tr>\r\n</table></td></tr>\r\n</table>\r\n' where bid ='text_ad_template';
INSERT INTO blocks VALUES ('preview_text_ad_template','<table bgcolor=\"#ffffff\" fgcolor=\"#000000\" border=1 cellpadding=1 cellspacing=0 width=\"200\">\r\n<tr><td>\r\n<table bgcolor=\"#000000\" fgcolor=\"#000000\" border=0 cellpadding=1 cellspacing=0 width=\"100%\">\r\n<tr><td>\r\n<TABLE bgcolor=\"FFFFFF\" fgcolor=\"000000\" border=\"0\" cellpadding=\"2\" cellspacing=\"0\" width=\"100%\">\r\n<TR><TD><a href=\"%%LINK_URL%%\">%%norm_font%%%%TITLE%%%%norm_font_end%%</a></TD></TR>\r\n<TR><TD>%%norm_font%%%%TEXT1%%%%norm_font_end%%</TD></TR>\r\n</TABLE>\r\n</td></tr>\r\n</table></td></tr>\r\n</table>',NULL,NULL);
INSERT INTO blocks VALUES ('ad_disapproval_mail','The ad that you submitted to %%sitename%% was disapproved today\r\nfor the following reason:\r\n\r\n%%REASON%%\r\n\r\nAdvertisement details:\r\nTitle: %%TITLE%%\r\nText1: %%TEXT1%%\r\nURL: %%URL%%\r\n\r\nIf you have received this message in error, please reply and let us know.\r\n\r\n-%%sitename%%\r\n%%local_email%%\r\n',NULL,NULL);
INSERT INTO blocks VALUES ('ad_approval_mail','The ad that you submitted to %%sitename%% was accepted for display on our site.\r\n\r\nAdvertisement details:\r\nTitle: %%TITLE%%\r\nText1: %%TEXT1%%\r\nURL: %%URL%%\r\n\r\nIf you have received this message in error, please reply and let us know.\r\n\r\n-%%sitename%%\r\n%%local_email%%\r\n',NULL,NULL);

UPDATE box set content = 'my $arg0 = $ARGS[0];\r\nmy $ad_id;\r\nmy $adhash = {};\r\n\r\n# if the ad_id is in the url, use the url\r\nif( $arg0 eq \'fromurl\' || $arg0 eq \'allcgi\' ) {\r\n $ad_id = $S->{CGI}->param(\'ad_id\');\r\n\r\n# else if ad_id is specified in template, use that\r\n} elsif( $arg0 =~ /\\d/ ) {\r\n $ad_id = $arg0;\r\n}\r\n\r\n\r\nunless( defined( $ad_id ) || $arg0 eq \'allcgi\' ) {\r\n  warn \"returning due to no ad_id in show_ad box\";\r\n  return \'\';\r\n}\r\n\r\n# if we need everything from cgi, let get_ad_hash know\r\nmy $source = \'db\';\r\n$source = \'cgi\' if( $arg0 eq \'allcgi\' );\r\n\r\n$adhash = $S->get_ad_hash($ad_id, $source);\r\n\r\nmy $image = $adhash->{ad_file};\r\nmy $subdir = $adhash->{sponser};\r\n$subdir = \'example\' if( $adhash->{example} == 1 );\r\n\r\nmy $image_path = $subdir . \'/\' . $image;\r\n\r\nmy $content = \'\';\r\nif( exists $S->{UI}->{BLOCKS}->{ \"preview_\".$adhash->{ad_tmpl} } ) {\r\n   $content = $S->{UI}->{BLOCKS}->{ \"preview_\" . $adhash->{ad_tmpl} };\r\n} else {\r\n        $content = $S->{UI}->{BLOCKS}->{ $adhash->{ad_tmpl} };\r\n}\r\n\r\n$content =~ s/%%FILE_PATH%%/$image_path/g;\r\n$content =~ s/%%TEXT1%%/$adhash->{ad_text1}/g;\r\n$content =~ s/%%TEXT2%%/$adhash->{ad_text2}/g;\r\n$content =~ s/%%TITLE%%/$adhash->{ad_title}/g;\r\n$content =~ s/%%LINK_URL%%/%%rootdir%%\\/redirect\\/$adhash->{ad_id}/g;\r\n\r\nreturn { content => $content };\r\n' where boxid = 'show_ad';

UPDATE box set content = 'return \'\' unless( $S->{UI}->{VARS}->{use_ads} );\r\n\r\nmy $adhash = $S->get_next_ad();\r\nreturn \'\' unless( defined $adhash );\r\n\r\nmy $content = $S->{UI}->{BLOCKS}->{$adhash->{ad_tmpl}};\r\n\r\n$content =~ s/%%LINK_URL%%/%%rootdir%%\\/redirect\\/$adhash->{ad_id}/g;\r\n$content =~ s/%%TITLE%%/$adhash->{ad_title}/g;\r\n$content =~ s/%%TEXT1%%/$adhash->{ad_text1}/g;\r\n$content =~ s/%%TEXT2%%/$adhash->{ad_text2}/g;\r\n$content =~ s/%%FILE_PATH%%/$adhash->{ad_file}/g;\r\n\r\nreturn { content => $content };\r\n' where boxid = 'ad_box';