-- Table listing department names and recipient email addresses
delete from db..deptemails;
drop table db..deptemails;
create table db..deptemails (dname varchar, demail varchar);

insert into db..deptemails values ('Testing', 'tim.haynes@openlinksw.co.uk');

insert into db..deptemails values ('Discussion/News administrator', 'discussion@openlinksw.com'); 
insert into db..deptemails values ('phpBB boards group', 'boards@openlinksw.com'); 
insert into db..deptemails values ('UDA Documentation Maintainer', 'uda.docs@openlinksw.com'); 
insert into db..deptemails values ('Virtuoso Documentation Maintainer', 'virtuoso.docs@openlinksw.com'); 
insert into db..deptemails values ('Virtuoso Open Source Maintainer', 'vos.admin@openlinksw.com'); 
insert into db..deptemails values ('iODBC Manager', 'iodbc@openlinksw.com'); 
insert into db..deptemails values ('JDBCBench Administrator', 'jdbc-bench@openlinksw.com'); 
insert into db..deptemails values ('OAT group', 'oat.admin@openlinksw.com'); 
insert into db..deptemails values ('ODBC Benchmark Maintainer', 'odbc-bench@openlinksw.com'); 
insert into db..deptemails values ('OpenLink Data Explorer', 'ode@openlinksw.com'); 
insert into db..deptemails values ('OpenLink Data-Space', 'ods@openlinksw.com'); 
insert into db..deptemails values ('Ruby on Rails ODBC Adapter', 'odbc-rails@openlinksw.com'); 
insert into db..deptemails values ('ATOM', 'atom@openlinksw.com'); 
insert into db..deptemails values ('FOAF', 'foaf@openlinksw.com'); 
insert into db..deptemails values ('SOAP', 'soap@openlinksw.com'); 
insert into db..deptemails values ('SparQL', 'sparql@openlinksw.com'); 
insert into db..deptemails values ('EMEA Customer Services Account', 'emea.customer.services@openlinksw.com'); 
insert into db..deptemails values ('General Information', 'general.information@openlinksw.com'); 
insert into db..deptemails values ('Human Resources', 'human.resources@openlinksw.com'); 
insert into db..deptemails values ('Product Availability', 'product.availability@openlinksw.com'); 
insert into db..deptemails values ('Download Wizard', 'downwiz@openlinksw.com'); 
insert into db..deptemails values ('Training', 'training@openlinksw.com'); 

alter table db..deptemails add displayorder integer;


-- Table logging who's contacted us and when
-- drop table db..deptcontactlog;
create table db..deptcontactlog(tstamp datetime not null primary key, 
    ipaddr varchar not null,
    name varchar not null, email varchar not null, 
    dept varchar not null, subject varchar not null, 
    content long varchar not null);

-- Procedure to effect the sending of standard-form mail
create procedure contact_sendmail(in mx any, 
       in sender any, in name any, in email any, 
       in dept any, in subject any, in content any) 
{
    for (select top 1 dname, demail from db..deptemails where dname=dept) do {
      contact_success(dname, demail);

      insert into db..deptcontactlog(tstamp, ipaddr, name, email, 
            dept, subject, content)
        values (now(), cast(http_client_ip(0) as varchar), name, email, 
            dept, subject, content);

      smtp_send(mx, sender, demail, concat(
        'Sender: "OpenLink Contact Bot" <contact@openlinksw.com>\r\n',
        'X-Sender: OpenLink Contact Bot <contact@openlinksw.com>\r\n',
        'X-Mailer: Virtuoso contact assistant\r\n',
        'X-Originating-IP: ', cast(http_client_ip(0) as varchar), '\r\n',
        'Date: ', soap_print_box (now (), '', 1), '\r\n', 
        'Message-ID: <', regexp_replace(cast(now() as varchar), '[- :.]', '', 1, null), '#contactbot@openlinksw.com>\r\n',
        'Subject: [oplcontact] ', trim(subject), '\r\n',
        'From: "',trim(name),'" <',trim(email),'>\n',
        'Reply-To: "',trim(name),'" <',trim(email),'>\n',
        'To: <', demail, '>\r\n',
        '\n',
        '[Message sent using the OpenLink Contact Bot, <http://www.openlinksw.com/contact/>.]\n\n',
        trim(content), '\n\n',
        '-- \n',
        'OpenLink Contact Bot\n',
        'http://www.openlinksw.com/contact/\n'));                          }
};

-- Function for checking whether submitted data is valid or not
create function contact_validate(in secret any, in turing any, 
       in name any, in email any, in subject any, in content any, in dept any) 
{
    if( '17'           =turing and 
        'Alohomora'    =secret and
        name           like '% %' and
        email          like '%@%.%' and 
        trim(subject)  is not null and
        length(name)   <128 and
        length(email)  <256 and
        length(subject)<256 and
        length(dept)   >2 and
        length(dept)   <256 and
        0              <length(trim(content))) {
    return 1;
  } else {
    return 0;
  }
};

create procedure contact_success(in dname varchar, in demail varchar)
{
  declare outp varchar;
  outp:=sprintf('<p style="min-height: 10em">Thank you for your comments, which have been relayed to the %s department. We will endeavour to be in touch with you shortly.</p>', dname);

  http(outp);
};