#!/usr/bin/perl -I../lib -w


#-----------------------------------------------------------------------------
# SHOP.PL
#-----------------------------------------------------------------------------
# e-commerce basic engine
#-----------------------------------------------------------------------------
# author : stephane@defreyne.com
#-----------------------------------------------------------------------------
use CGI::Carp 'fatalsToBrowser';
use DBI;      # standard package for Database access
use CGI;      # standard package for easy CGI scripting
use define;   # home-made package for defines
use tools;    # home-made package for tools
use spec;     # home-made package for problem-specific solutions
use txtimg;     # home-made package for texts and pics contents

# make globals

# this script's name
$self = "shop2.pl";

# build the CGI object, and so on...
$cgi = new CGI;
$_ = $cgi->param('sw');

 # connect to DB
 $dbh = DBI->connect($config{db_name},$config{db_user},$config{db_passwd}) or suicide("cannot connect to $config{db_name}");

 $lang = get_quoted('lang') || "fr";

my %txt = %{get_translations($lang)};

my $currency = $cgi->param('currency');
if ($currency eq "") {$currency = 'euro';}
my $reseller = $cgi->param('reseller');
if ($reseller eq "") {$reseller = 2;}

# define for global urls
$view_shop_url = "$self?sw=view_shop&currency=$currency&reseller=$reseller&lang=$lang";
$view_detail_url = "$self?sw=view_detail&currency=$currency&reseller=$reseller&lang=$lang&id=";
$add_caddie_url = "$self?sw=add_caddie&qty=1&currency=$currency&reseller=$reseller&lang=$lang&id=";
$del_caddie_url = "$self?sw=del_caddie&currency=$currency&reseller=$reseller&lang=$lang&id=";
$update_caddie_url_sc = $self;
$update_caddie_url_sw = "update_caddie&currency=$currency&reseller=$reseller&lang=$lang";
$make_order_url = "$self?sw=make_order&currency=$currency&reseller=$reseller&lang=$lang";


$charset = $lgs{$lang}{charset};
$config{current_charset} = $lgs{$lang}{charset};


# go to the chosen function...
SWITCH:
 {
  if (/^view_shop/)        { view_shop(); last SWITCH;}
  if (/^view_detail/)      {  print $cgi->header(-expires=>'-1d',-charset=>$config{current_charset}); view_detail(); last SWITCH;}
  if (/^view_caddie/)      { print $cgi->header(-expires=>'-1d',-charset=>$config{current_charset}); view_caddie(); last SWITCH;}
  if (/^add_caddie/)       { add_caddie(); last SWITCH;}
  if (/^update_caddie/)    { update_caddie(); last SWITCH;}
  if (/^del_caddie/)       { del_caddie(); last SWITCH;}
  if (/^make_order/)       { print $cgi->header(-expires=>'-1d',-charset=>$config{current_charset}); add_identity_form(); last SWITCH;}
  if (/^login_db/)         { login_db(); last SWITCH;}
  if (/^add_cust_db/)      { add_cust_db();last SWITCH;}
  if (/^add_identity_form/){ print $cgi->header(-expires=>'-1d',-charset=>$config{current_charset}); add_identity_form(); last SWITCH;}
  if (/^add_identity_db/)  { add_identity_db();last SWITCH;}
  if (/^secure_payment/)   { print $cgi->header(-expires=>'-1d',-charset=>$config{current_charset}); secure_payment();last SWITCH;}
  if (/^compile_results/)  { compile_results(); last SWITCH;}
  if (/^finish/)           { print $cgi->header(-expires=>'-1d',-charset=>$config{current_charset}); finish(); last SWITCH;}

  # default function...
  print view_shop();
 }


################################################################################
# VIEW_SHOP
# -----------------------------------------------------------------------------
# display the products list
################################################################################

sub view_shop
{
 # get interface head & foot
 my $header = get_header();
 my $footer = get_footer();

 # get the products list
 my $products = get_products_page($add_caddie_url,$view_detail_url);

 my $currency_choice = get_currency_list($currency,"$self?sw=view_shop");

 # throw all of this to STDOUT...
 
  my $cookie = $cgi->cookie(-name=>$config{cookie_name},-value=>"",-path=>'/');
 # throw the cookie to STDOUT with the HTTP header...
 print $cgi->header(-expires=>'-1d',-charset=>$config{current_charset},-cookie=>$cookie);

# use Data::Dumper;
# $txt_h = Dumper(%txt);

 print <<"EOH";
$header
<!----------------------------------------------------------------------------->
$currency_choice
$products
$txt_h
<!----------------------------------------------------------------------------->
$footer
EOH

}

################################################################################
# VIEW_DETAIL
# -----------------------------------------------------------------------------
# display a product detail
################################################################################

sub view_detail
{
 # get interface head & foot
 my $header = get_header();
 my $footer = get_footer();

 # get the product's id
 my $prod = get_quoted('id');

 # get the detail of the product
 my $detail = get_product_detail ($prod,$add_caddie_url);

 my $currency_choice = get_currency_list($currency,"$self?sw=view_detail&id=$prod");

 # throw all of this to STDOUT...
 print <<"EOH";
$header
<!----------------------------------------------------------------------------->
$currency_choice
$detail
<!----------------------------------------------------------------------------->
$footer
EOH
}

################################################################################
# VIEW_CADDIE
# -----------------------------------------------------------------------------
# display the content of the shopping cart
################################################################################

sub view_caddie
{
 # get interface head & foot
 my $header = get_header();
 my $footer = get_footer();

 # information in the cookie is stored in the following form :
 #
 # %myCookie{list} = "1,2,3,4"; (list of the products)
 # %myCookie{1} = 1; ...  and then each quantity is stored
 # %myCookie{2} = 5; ...  using product's id as a key of the hashtable
 # %myCookie{3} = 2; ...
 # ...

 # get the cookie as a hashtable
 my %myCookie = $cgi->cookie($config{cookie_name});

 # get the list
 my $list = $myCookie{'list'};
 # set it into an array
 my @list_id = split(/,/,$list);
 # store how many items there is
 my $nb_prod = ($#list_id) + 1;


 my $list = "";
 for ($i = 0; $i<$nb_prod; $i++) # for each product
  {
   # get the product's id
   my $curr_id = $list_id[$i];
   # get the product's quantity
   my $qty = $myCookie{$curr_id};

   # get the line to display, and the price of the product
   ($price,$line) = get_line_prod($curr_id,$qty,$i,$del_caddie_url);

   # multiply the price by the quantity
   $price*=$qty;
   # increase the total amount
   $total_price += $price;

   # add the line to the list
   $list.=$line;
  }

 # add the line to display the total
 $list.= get_line_total($total_price);

 # give all these vars to a function that will make a nice layout...
 $caddie = get_caddie_page($list,$nb_prod,$update_caddie_url_sw,$update_caddie_url_sc,$make_order_url,$view_shop_url);

 my $currency_choice = get_currency_list($currency,"$self?sw=view_caddie");
 # throw all of this to STDOUT...
 print <<"EOH";
$header
<!----------------------------------------------------------------------------->
$currency_choice
$caddie
<!----------------------------------------------------------------------------->
$footer
EOH

}

################################################################################
# ADD_CADDIE
# -----------------------------------------------------------------------------
# add a product to the shopping cart
################################################################################

sub add_caddie
{
 # get the cookie as a hashtable
 my %myCookie = $cgi->cookie($config{cookie_name});

 # get the current list
 my $curr_list = $myCookie{'list'};

 # get the vars to add...
 my $id = $cgi->param('id');
 my $qty = $cgi->param('qty');

 # initialize vars
 my $new_list = "";
 my $curr_id = 0;

 # if this products doesn't exist
 if (!defined $myCookie{$id})
     {
      $new_list.="$id"; # just add it to the list
     }

 # add the quantity for this product
 $myCookie{$id} += $qty;

 # if the new list is not empty
 if ($new_list ne "")
     {
      if ($curr_list ne "") # nor the current list
          {
           $myCookie{list}.= ",$new_list"; # then add the old to the new
          }
      else
          {
           $myCookie{list}= $new_list; # else just put the new one :)
          }
    }

 # build a cookie from the hashtable, and set the options...
 my $cookie = $cgi->cookie(-name=>$config{cookie_name},-value=>\%myCookie,-path=>'/');
 # throw the cookie to STDOUT with the HTTP header...
 print $cgi->header(-expires=>'-1d',-charset=>$config{current_charset},-cookie=>$cookie);

 # go back to caddie content
 http_redirect("$self?sw=view_caddie&currency=$currency&reseller=$reseller&lang=$lang");
}

################################################################################
# UPDATE_CADDIE
# -----------------------------------------------------------------------------
# update the quantity stored for each product in the shopping cart
################################################################################

sub update_caddie
{
 # get the cookie as a hashtable
 my %myCookie = $cgi->cookie($config{cookie_name});

 # how many items are there in the form ?
 my $cpt = $cgi->param("nb");

 # for each one...
 for ($i = 0; $i<$cpt; $i++)
 {
  # get the id
  my $id = $cgi->param($i);
  # get the new quantity
  my $qty = $cgi->param("qty_$id");

  # store it
  $myCookie{$id} = $qty;
 }

 # build a cookie from the hashtable, and set the options...
 my $cookie = $cgi->cookie(-name=>$config{cookie_name},-value=>\%myCookie,-path=>'/');
 # throw the cookie to STDOUT with the HTTP header...
 print $cgi->header(-expires=>'-1d',-charset=>$config{current_charset},-cookie=>$cookie);
 # go back to caddie content
 http_redirect("$self?sw=view_caddie&currency=$currency&reseller=$reseller&lang=$lang");
}

################################################################################
# DEL_CADDIE
# -----------------------------------------------------------------------------
# delete an item from the caddie
################################################################################

sub del_caddie
{
 # get the cookie as a hashtable
 my %myCookie = $cgi->cookie($config{cookie_name});


 my %hash_id;

 my $list = $myCookie{'list'};
 my @list_id = split(/,/,$list);

 for (@list_id)
    {
     $hash_id{$_} = $_;
    }


 $curr_id = $cgi->param('id');

 if (defined $curr_id)
     {
      delete $myCookie{$curr_id};
      delete $hash_id{$curr_id};
     }

 @list_id = keys %hash_id;

 $list = "";
 for (@list_id)
    {
     $list.= "$_,";
    }
 chop $list;

 $myCookie{'list'} = $list;

 # build a cookie from the hashtable, and set the options...
 my $cookie = $cgi->cookie(-name=>$config{cookie_name},-value=>\%myCookie,-path=>'/');
 # throw the cookie to STDOUT with the HTTP header...
 print $cgi->header(-expires=>'-1d',-charset=>$config{current_charset},-cookie=>$cookie);

 # go back to caddie content
 http_redirect("$self?sw=view_caddie&currency=$currency&reseller=$reseller&lang=$lang");
}

################################################################################
# MAKE_ORDER
# -----------------------------------------------------------------------------
# let the user choose if he is already known by the system or not
################################################################################

sub make_order
{
 # get interface head & foot
 my $header = get_header();
 my $footer = get_footer();

 my $form_login = get_login_form();
 my $form_subscribe = get_subscribe_form();
 
 my $make_order_page = get_make_order_page($form_login,$form_subscribe);



 print <<"EOH";
$header
<!----------------------------------------------------------------------------->
$make_order_page
<!----------------------------------------------------------------------------->
$footer
EOH


} 

################################################################################
# LOGIN_DB
################################################################################

sub login_db
{

 my $email = get_quoted('email');
 my $passwd = get_quoted('passwd');
 

 insert_new_order($dbh,$id_customer,$email);    

 http_redirect("$self?sw=add_identity_form&reseller=$reseller&lang=$lang");
}


################################################################################
# ADD_CUST_DB
################################################################################

sub add_cust_db
{
 # get data for new customer from form
 my ($stmt,$email) = get_newcust_stmt(); 
 execstmt($dbh,$stmt);

 my $id_customer = $dbh->{'mysql_insertid'};
 
 insert_new_order($dbh,$id_customer,$email);    

 
 http_redirect("$self?sw=add_identity_form&reseller=$reseller&lang=$lang");
}

################################################################################
# ADD_IDENTITY_FORM
################################################################################

sub add_identity_form
{
 # get interface head & foot
 my $header = get_header();
 my $footer = get_footer();

# my %myCookie = $cgi->cookie($config{cookie_name});
# my $id_customer = $myCookie{id_customer};
  

# my %prev_identity = %{find_prev_identity($dbh,$id_customer)};
my %prev_identity;

 my $identity_form = get_identity_form(\%prev_identity,$dbh);
 
 print <<"EOH";
$header
<!----------------------------------------------------------------------------->
$identity_form
<!----------------------------------------------------------------------------->
$footer

EOH
}

################################################################################
# ADD_IDENTITY_DB
################################################################################

sub add_identity_db
{
 my ($stmt,$email) = get_newcust_stmt(); 
 execstmt($dbh,$stmt);

 my $id_customer = $dbh->{'mysql_insertid'};
 
 $id_order = insert_new_order($dbh,$id_customer,$email);    
	
 my $company = get_quoted('company');
 my $street = get_quoted('street');
 my $number = get_quoted('number');
 my $box = get_quoted('box');
 my $city = get_quoted('city');
 my $country = get_quoted('country');
 my $zip = get_quoted('zip');
 my $vat = get_quoted('vat');
 my $vat_app = get_quoted('vat_app');
 if ($vat_app ne "y") {$vat_app = 'n';}
 my $title = get_quoted('title');
 my $lg = get_quoted('lg');
 
 $stmt = "INSERT INTO identities (id_customer,company,street,number,zip,box,city,country,vat,vat_app,title,lg) VALUES ($id_customer,'$company','$street','$number','$zip','$box','$city','$country','$vat','$vat_app','$title','$lg')";    
 execstmt($dbh,$stmt);

 my $id_identity = $dbh->{'mysql_insertid'};

 $stmt = "UPDATE orders SET id_idbilling = $id_identity WHERE id = $id_order";
 execstmt($dbh,$stmt);

# make the invoice

 my $invoice = make_invoice ($dbh,$id_order);

 $invoice =~ s/\'/\\\'/g;
 
 $stmt = "UPDATE orders SET txt_fact = '$invoice' WHERE id = $id_order";
 execstmt ($dbh,$stmt);
  
 http_redirect("$self?sw=secure_payment&reseller=$reseller&lang=$lang");
}

################################################################################
# SECURE_PAYMENT
################################################################################

sub secure_payment
{
 my %myCookie = $cgi->cookie($config{cookie_name});
 
 my $id_order = $myCookie{id_order};
 my $order = get_invoice($dbh,$id_order);

 $stmt = "SELECT total_euro_tvac FROM orders WHERE id = $id_order";
 $cursor = $dbh->prepare($stmt);
 $rc = $cursor->execute;
 if (!defined $rc) {suicide($stmt);}
 $amo = $cursor->fetchrow_array;
 $cursor->finish;
 
 $amo*=100;

 # get interface head & foot
 my $header = get_header();
 my $footer = get_footer();

 print <<"EOH";
$header
<!----------------------------------------------------------------------------->
<H1 CLASS=ARKTITLE>$txt{title_check_invoice}</H1>
$order

<FORM METHOD="post" ACTION="https://secure.ogone.com/ncol/prod/orderstandard.asp" id=form1 name=form1>
<INPUT type="hidden" NAME="accepturl" value="https://secure.lifebadge.org/shop/cgi-bin/shop2.pl?lg=$lg&sw=compile_results&orderID=$id_order&lang=$lang">
<INPUT type="hidden" NAME="PSPID" value="life_badge">
<INPUT type="hidden" NAME="orderID" VALUE="$id_order">
<INPUT type="hidden" NAME="amount" VALUE="$amo">
<INPUT type="hidden" NAME="currency" VALUE="EUR">
<INPUT type="hidden" NAME="language" VALUE="en_us">
<INPUT type="submit"  VALUE="$txt{button_payment}" CLASS=ARKBUTTON>
</FORM>

<!----------------------------------------------------------------------------->
$footer
EOH
 
}

################################################################################
# COMPILE_RESULTS
################################################################################

sub compile_results
{
 my %myCookie = $cgi->cookie($config{cookie_name});
 
 #my $id_order = $myCookie{id_order};
 my $id_order = get_quoted('orderID');
 
 my $email = get_email_from_order($dbh,$id_order);
 my $order = get_invoice($dbh,$id_order);

 send_mail($config{from_email},$email,$config{invoice_mail_title},$order,'html');
 send_mail($email,$config{from_email},$config{invoice_mail_title},$order,'html');

 $stmt = "UPDATE orders SET status='paid', datetime_order = NOW() WHERE id = $id_order";
 execstmt($dbh,$stmt);

 my $cookie = $cgi->cookie(-name=>$config{cookie_name},-value=>"",-path=>'/',-expires=>'-1d');
 print $cgi->header(-expires=>'-1d',-charset=>$config{current_charset},-cookie=>$cookie);
 
 http_redirect("$self?sw=finish&reseller=$reseller&lang=$lang");
}

################################################################################
# FINISH
################################################################################

sub finish
{
 # get interface head & foot
 my $header = get_header();
 my $footer = get_footer();

 print <<"EOH";
$header
<!----------------------------------------------------------------------------->
<H1 CLASS=ARKTITLE>$txt{title_order_complete} </H1>
$txt{end_msg}
<BR>
<TABLE><tr>
 <TD CLASS=ARKBUTTON><A HREF="$view_shop_url" class=arkurl>$txt{button_back_cat}</A></TD>
</TR></TABLE>

<!----------------------------------------------------------------------------->
$footer
EOH

}

# SPECIFIC METHODS /////////////////////////////////////////////////////////////////////////

################################################################################
# GET_PRODUCTS_PAGE
################################################################################
sub get_products_page
{
 my $ac_url = $_[0];
 my $vd_url = $_[1];

 my $stmt = "SELECT id,name,price_".$currency."_tvac,descr
                    FROM products WHERE visible = 'y' order by ordby";

 my $cursor = $dbh->prepare($stmt);
 my $rc = $cursor->execute;
 if (!defined $rc) {suicide($stmt);}

 my ($id,$name,$price,$descr);
 

 %year_sing_trad = (
 "fr"=>"an",
 "en"=>"year",
 "nl"=>"jaar",
 "de"=>"Jahr",
 "it"=>"anno",
 "es"=>"aņo",
 "br"=>"ano",
 "pt"=>"ano",
);

 %year_plur_trad = (
 "fr"=>"ans",
 "en"=>"years",
 "nl"=>"jaar",
 "de"=>"Jahre",
 "it"=>"anni",
 "es"=>"aņos",
 "br"=>"anos",
 "pt"=>"anos",
);


 my $list = "";
 while (($id,$name,$price,$descr) = $cursor->fetchrow_array)
  {
   $price = format_currency($price);
   $name = $txt{name_prod};
   $descr = $txt{descr_prod};

   $name.=" ".get_prod_duration($id,$lang);

   $list.=<<"EOH";
<tr>
 <TD class=arkproductname width=240>$name</TD>
 <TD class=arkprice align=right>
 <TABLE border=0 cellpadding=0 cellspacing=0 width=240>
  <TR><TD colspan=3>$gfx{pricebox_up}</TD></TR>
  <TR><TD>$gfx{pricebox_le}</TD><TD CLASS=arkprice align=center width=238> $price</TD><TD align=right>$gfx{pricebox_ri}</TD></tr>
  <TR><TD colspan=3>$gfx{pricebox_do}</TD></TR>
 </TABLE> 
 </TD>
</TR>
<TR>
 <TD colspan=2 class=arkdescr>$descr</TD>
</TR>
<tr>
<TD colspan=2 align=center>
 <TABLE><tr>
  <TD class=arkbutton><A HREF=\"$ac_url$id\" class=arkurl>$txt{button_addtocart}</A></TD>
  <TD>&nbsp;&nbsp;</TD>
  <TD class=arkbutton><A HREF=\"$vd_url$id\" class=arkurl>$txt{button_viewdetail}</A></TD>
 </TR></TABLE>
 </TD>
</TR>
<tr><TD colspan=2><BR>$gfx{spacer}
<BR><BR></TD></tr>
EOH

  	
  }
 $cursor->finish;
 $dbh->disconnect;

 my $content ="";

 $content.=<<"EOH";
 <H1 CLASS=arktitle>$txt{title_product_catalogue} </H1>
<BR><BR> <BR><BR> 
 <TABLE WIDTH=560 class=arkcontent>
 $list
 </TABLE>
EOH

return $content;
}

################################################################################
# GET_PRODUCT_DETAIL
################################################################################
sub get_product_detail
{
 my $prod = $_[0];
 my $ac_url = $_[1];

 my $stmt = "SELECT name,price_".$currency."_tvac,descr,descr2,picture FROM products WHERE id = $prod";

 my $cursor = $dbh->prepare($stmt);
 my $rc = $cursor->execute;
 if (!defined $rc) {suicide($stmt);}

 my($name,$price,$descr,$descr2,$pic) = $cursor->fetchrow_array;
 $price = format_currency($price);


$name = $txt{prod_card}." ".$txt{prod_1year};
$descr = $txt{descr_prod};
$descr2 = $txt{longdescr_prod};

$name.=" ".get_prod_duration($prod,$lang);


 if ($pic ne "") {$pic = "<IMG SRC=\"$config{pic_dir}/$pic\">";}
 
 $cursor->finish;
 $dbh->disconnect;

 my $content ="";

 $content.=<<"EOH";
 <TABLE WIDTH=560>
<tr>
 <TD class=arkproductname width=240>$name</TD>
 <TD class=arkprice align=right>
 <TABLE border=0 cellpadding=0 cellspacing=0 width=240>
  <TR><TD colspan=3>$gfx{pricebox_up}</TD></TR>
  <TR><TD>$gfx{pricebox_le}</TD><TD CLASS=arkprice align=center width=238> $price</TD><TD align=right>$gfx{pricebox_ri}</TD></tr>
  <TR><TD colspan=3>$gfx{pricebox_do}</TD></TR>
 </TABLE> 
 </TD>
</TR>
<TR>
 <TD colspan=2>
 
 <TABLE><TR><TD width=40%>$pic</TD><TD class=arkdescr>$descr</TD></TR><TR><TD COLSPAN=2 class=arkdescr>$descr2</TD></TR></TABLE></TD>
</TR>
<tr>
<TD colspan=2 align=center>
 <TABLE><tr>
  <TD class=arkbutton><A HREF="$ac_url$prod" class=arkurl>$txt{button_addtocart}</A></TD>
 <TD>&nbsp;&nbsp;&nbsp;</TD>
 <TD CLASS=ARKBUTTON><A HREF="$view_shop_url" class=arkurl>$txt{button_back_cat}</A></TD>
 </TR></TABLE>
 </TD>
</TR>
<tr><TD><BR><BR><BR></TD></tr>
</TABLE>
EOH

return $content;
}

################################################################################
# GET_LINE_PROD
################################################################################

sub get_line_prod
{
 my $prod = $_[0];
 my $qty = $_[1];
 my $cpt = $_[2];
 my $dl_url = $_[3];

 my $stmt = "SELECT name,price_".$currency."_tvac FROM products WHERE id = $prod";

 my $cursor = $dbh->prepare($stmt);
 my $rc = $cursor->execute;
 if (!defined $rc) {suicide($stmt);}

 my($name,$price) = $cursor->fetchrow_array;

$name = "[".$txt{prod_card}."] ".$txt{prod_1year};


 $cursor->finish;
 $dbh->disconnect;

 my $subtotal = $qty * $price;

 $price2 = format_currency($price);
 $subtotal = format_currency($subtotal);

 my $content = <<"EOH";

 <TR><TD><TABLE><TR><TD CLASS=ARKBUTTON><A HREF="$del_caddie_url$prod" CLASS=ARKURL>$txt{button_deletefromcart}</A></TD></TR></TABLE></TD>
     <TD class=arkcontent>$name</TD>
     <TD><INPUT TYPE=HIDDEN NAME="$cpt" VALUE="$prod">
         <INPUT TYPE=TEXT NAME="qty_$prod" VALUE="$qty" SIZE=2 class=arkinput></TD>
     <TD class=arkprice align=right>$price2</TD>
     <TD class=arkprice align=right>$subtotal</TD>
 </TR>
EOH

 return ($price,$content);
}

################################################################################
# GET_LINE_TOTAL
################################################################################

sub get_line_total
{
 my $total = $_[0];

$total = format_currency($total);

 my $content = <<"EOH";


 <TR><TD>&nbsp;</TD><TD COLSPAN=4 class=arkinput>
 <TABLE WIDTH=100% BORDER=0 CELLPADDING=0 CELLSPACING=0><TR><TD CLASS=ARKHEADER>$txt{txt_total}</TD>
 <TD ALIGN=RIGHT class=arkprice>$total
     </TD></TR></TABLE>
     </TD>
 </TR>
EOH

 return $content;
}

################################################################################
# GET_CADDIE_PAGE
################################################################################

sub get_caddie_page
{
 my $list = $_[0];
 my $nb = $_[1];
 my $upd_sw = $_[2];
 my $upd_sc = $_[3];
 my $mk_url = $_[4];
 my $vw_url = $_[5];

 my $content = <<"EOH";
 <H1 CLASS=arktitle>$txt{title_checkout} </H1>
 <FORM ACTION="$upd_sc" METHOD=POST>
 <INPUT TYPE=HIDDEN NAME="sw" VALUE="$upd_sw">
 <INPUT TYPE=HIDDEN NAME="lang" VALUE="$lang">
 <TABLE WIDTH=560 border=0>

  <TR><TD class=arkheader>&nbsp;</TD>
     <TD class=arkheader WIDTH=240>$txt{txt_product}</TD>
     <TD class=arkheader>$txt{txt_qty}</TD>
     <TD class=arkheader align=right>$txt{txt_price}</TD>
     <TD class=arkheader align=right>$txt{txt_subtotal}</TD>
 </TR>

 $list
 <TR><TD COLSPAN=5 align=center><BR><BR>
 <TABLE BORDER=0 CELLPADDING=0 CELLSPACING=0><TR>
 <TD>
 <INPUT TYPE=SUBMIT VALUE="$txt{button_updatecart}" class=arkbutton>
 </TD>
 <TD>&nbsp;&nbsp;</TD>
 <TD CLASS=ARKBUTTON>
 <A HREF="$mk_url" class=arkurl>$txt{button_makeorder}</A>
 </TD>
 </tr></TABLE>
 </TD>
 
 </TABLE>
 <INPUT TYPE=HIDDEN NAME="nb" VALUE="$nb">
 </FORM>

<TABLE><TR></TR></TABLE>
EOH

 return $content;
}



################################################################################
# GET_NEWCUST_STMT
################################################################################
sub get_newcust_stmt
{
 my $stmt;
 
 my $firstname = get_quoted('firstname');
 my $lastname = get_quoted('lastname');
 my $email = get_quoted('email');
 my $passwd = get_quoted('passwd');
 my $passwd2 = get_quoted('passwd2');

 if ($passwd ne $passwd2)
     {
      make_error($errmsg{pass_mismatch});	
     }
 
 if (!($email =~  /^[^@]+@([-\w]+\.)+[A-Za-z]{2,4}$/)) {make_error($errmsg{miss_field}." [email]");}
 if ($firstname eq "" || $lastname eq "") {make_error($errmsg{miss_field});}


 $stmt = "INSERT INTO customers (firstname,lastname,email,password) 
                         values ('$firstname','$lastname','$email','$passwd')";
 return ($stmt,$email);	
}

################################################################################
# GET_NEWORDER_STMT
################################################################################
sub get_neworder_stmt
{
 my $id_customer = $_[0];
 my $stmt;
 
 $stmt = "INSERT INTO orders (id_customer,status,paid) VALUES ($id_customer,'current','n')";
 
 return $stmt;	
}

################################################################################
# INSERT_NEW_ORDER
################################################################################
sub insert_new_order
{
 my $dbh = $_[0];
 my $id_customer = $_[1];
 my $email = $_[2];
 
 # get data for new order from form & cookie
 my ($stmt) = get_neworder_stmt($id_customer);
 execstmt($dbh,$stmt);

 my $id_order = $dbh->{'mysql_insertid'};

 my %myCookie = $cgi->cookie($config{cookie_name});
 
 $myCookie{id_customer} = $id_customer;
 $myCookie{id_order} = $id_order;
 $myCookie{currency} = $currency;
 $myCookie{email} = $email;
 
 
 # get the list
 my $list = $myCookie{'list'};
 # set it into an array
 my @list_id = split(/,/,$list);
 # store how many items there is
 my $nb_prod = ($#list_id) + 1;


 # make the product list
 my $total_euro = 0;
 my $total_usd = 0;
 
 my $list = "";
 for ($i = 0; $i<$nb_prod; $i++) # for each product
  {
   # get the product's id
   my $curr_id = $list_id[$i];
   # get the product's quantity
   my $qty = $myCookie{$curr_id};
   
   ($stmt,$euro,$usd) = get_prod_order_insert_stmt ($dbh,$curr_id,$qty,$id_order);
   execstmt ($dbh,$stmt);
   $total_euro += $euro;   
   $total_usd += $usd;
  }

 $total_euro_ttc = $total_euro;
 $total_usd_ttc = $total_usd;

 # make the invoice

# my $invoice = make_invoice ($dbh,$id_order);

 $stmt = "UPDATE orders 
                    SET total_euro_tvac = $total_euro_ttc,
                        total_usd_tvac = $total_usd_ttc,
                        currency = '$currency',
                        id_reseller = '$reseller'                       
                  WHERE id = $id_order";

 execstmt ($dbh,$stmt);
                  
 my $cookie = $cgi->cookie(-name=>$config{cookie_name},-value=>\%myCookie,-path=>'/');
 # throw the cookie to STDOUT with the HTTP header...
 print $cgi->header(-expires=>'-1d',-charset=>$config{current_charset},-cookie=>$cookie);
 
 return $id_order;
}



################################################################################
# GET_PROD_ORDER_INSERT_STMT
################################################################################
sub get_prod_order_insert_stmt
{
 my $dbh = $_[0];
 my $id_product = $_[1];
 my $qty = $_[2];
 my $id_order = $_[3];
 my $retstmt = ""; 
 
 my$stmt = "SELECT name ,price_euro_tvac,price_usd_tvac,price_".$currency."_tvac FROM products WHERE id = $id_product";
 my $cursor = $dbh->prepare($stmt);
 my $rc = $cursor->execute;
 if (!defined $rc) {suicide($stmt);}

 my($name,$price_euro,$price_usd,$price) = $cursor->fetchrow_array;

 my $subtotal_euro = $qty * $price_euro;
 my $subtotal_usd = $qty * $price_usd;
 my $subtotal = $qty * $price;
 
 $price = format_currency($price);
 $subtotal = format_currency($subtotal);
 
 my $txt_line = "<TR><TD>$name</TD><TD>$qty</TD><TD>$price</TD><TD>$subtotal</TD></TR>\n";
 $retstmt = "INSERT INTO order_details (id_product,id_order,qty,subtotal_euro_tvac,subtotal_usd_tvac,txt_line)
                               values($id_product,$id_order,$qty,$subtotal_euro,$subtotal_usd,'$txt_line')"; 
                               
 return ($retstmt,$subtotal_euro,$subtotal_usd);
}

################################################################################
# GET_MAKE_ORDER_PAGE
################################################################################

sub get_make_order_page
{
 my $login_form = $_[0];
 my $subscribe_form = $_[1];	

 my $display = <<"EOH";
<H1 CLASS=arktitle>$txt{title_confirm_order} </H1>
<TABLE WIDTH=560>
 <TR>
  <TD WIDTH=50%>
  <H2 class=arkheader>$txt{txt_retcustomer}</H2>

  $login_form
  
  </TD>
  <TD>
  <H2 class=arkheader>$txt{txt_newcustomer}</H2>

  $subscribe_form
  
  </TD>
 </tr>
</TABLE>
EOH

return $display;
}


################################################################################
# GET_LOGIN_FORM
################################################################################
sub get_login_form
{
	
 my $display = <<"EOH";
<FORM ACTION="$self" METHOD=POST>
<INPUT TYPE=HIDDEN NAME="sw" VALUE="login_db">
<INPUT TYPE=HIDDEN NAME="currency" VALUE="$currency">
   <TABLE>
    <TR>
     <TD class=arkcontent>$txt{field_email}</TD>
     <TD><INPUT TYPE=TEXT NAME="email" SIZE=15 class=arkinput></TD>
     </TR><tr>
     <TD class=arkcontent>$txt{field_password}</TD>
     <TD><INPUT TYPE=PASSWORD NAME="passwd" SIZE=15 class=arkinput></TD>
     </TR><tr>
     <TD COLSPAN=2 align=center><INPUT TYPE=SUBMIT VALUE="$txt{button_login}" class=arkbutton></TD>
    </TR>
   </TABLE>
</FORM>
EOH
 return $display
}

################################################################################
# GET_SUBSCRIBE_FORM
################################################################################

sub get_subscribe_form
{

 my $display = <<"EOH";
<FORM ACTION="$self" METHOD=POST>
<INPUT TYPE=HIDDEN NAME="sw" VALUE="add_cust_db">
<INPUT TYPE=HIDDEN NAME="currency" VALUE="$currency">
<INPUT TYPE=HIDDEN NAME="reseller" VALUE="$reseller">
<INPUT TYPE=HIDDEN NAME="lang" VALUE="$lang">

   <TABLE>
    <TR>
     <TD class=arkcontent>$txt{field_firstname}</TD>
     <TD><INPUT TYPE=TEXT NAME="firstname" SIZE=15 class=arkinput></TD>
     </TR><tr>
     <TD class=arkcontent>$txt{field_lastname}</TD>
     <TD><INPUT TYPE=TEXT NAME="lastname" SIZE=15 class=arkinput></TD>
     </TR><tr>
     <TD class=arkcontent>$txt{field_email}</TD>
     <TD><INPUT TYPE=TEXT NAME="email" SIZE=15 class=arkinput></TD>
     </TR><tr>
     <TD class=arkcontent>$txt{field_password}</TD>
     <TD><INPUT TYPE=PASSWORD NAME="passwd" SIZE=15 class=arkinput></TD>
     </TR><tr>
     <TD class=arkcontent>$txt{field_password2}</TD>
     <TD><INPUT TYPE=PASSWORD NAME="passwd2" SIZE=15 class=arkinput></TD>
     </TR><tr>
     <TD COLSPAN=2 align=center><INPUT TYPE=SUBMIT VALUE="$txt{button_newuser}" class=arkbutton></TD>
    </TR>
   </TABLE>
</FORM>
EOH

return $display;
}

################################################################################
# GET_IDENTITY_FORM
################################################################################
sub get_identity_form
{
 my %item = %{$_[0]};
 my $dbh = $_[1];
 my $title_list = makeselecth(\%t_title);
 my $lg_list = makeselecth(\%t_lg);
 
 my $content = <<"EOH";
 
 <SCRIPT LANGUAGE=JAVASCRIPT>
 var iform = 0;
 
 function checkForm()
 {
  if (document.forms[iform].firstname.value == '' ||
      document.forms[iform].lastname.value == '' ||
      document.forms[iform].email.value == '' ||
      document.forms[iform].street.value == '' ||
      document.forms[iform].number.value == '' ||
      document.forms[iform].zip.value == '' ||
      document.forms[iform].city.value == '' ||
      document.forms[iform].country.value == ''
      ) {alert('$txt{field_error}'); return false;}	
  return true;
 } 
 </SCRIPT>
 <H1 CLASS=ARKTITLE>$txt{title_billing_info}</H1>
<FORM ACTION="$self" METHOD=POST onSubmit="return checkForm();">
<INPUT TYPE=HIDDEN NAME="sw" VALUE="add_identity_db">
<INPUT TYPE=HIDDEN NAME="reseller" VALUE="$reseller">
<INPUT TYPE=HIDDEN NAME="lang" VALUE="$lang">

 <TABLE>
     <tr>
     <TD class=arkcontent align=right>$txt{field_title}(*) :</TD>
     <TD><select NAME="title" CLASS=admf>$title_list</select></TD>
     </TR>  
    <TR>
     <TD class=arkcontent align=right>$txt{field_firstname}(*) :</TD>
     <TD><INPUT TYPE=TEXT NAME="firstname" SIZE=15 CLASS=admf></TD>
     </TR><tr>
     <TD class=arkcontent align=right>$txt{field_lastname}(*) :</TD>
     <TD><INPUT TYPE=TEXT NAME="lastname" SIZE=15 CLASS=admf></TD>
     </TR>
     <tr>
     <TD class=arkcontent align=right>$txt{field_email}(*) :</TD>
     <TD><INPUT TYPE=TEXT NAME="email" SIZE=15 CLASS=admf></TD>
     </TR>  
     <TR>
      <TD class=arkcontent align=right>$txt{field_company} :</TD>
      <TD><INPUT CLASS=admf  TYPE="TEXT" NAME="company" VALUE="$item{company}"></TD>
  </TR>
  <TR>
      <TD class=arkcontent align=right>$txt{field_street}(*) :</TD>
      <TD><INPUT CLASS=admf  TYPE="TEXT" NAME="street" VALUE="$item{street}"></TD>
  </TR>
  <TR>
      <TD class=arkcontent align=right>$txt{field_number}(*) :</TD>
      <TD><INPUT CLASS=admf  TYPE="TEXT" NAME="number" VALUE="$item{number}" SIZE=4></TD>
  </TR>
  <TR>
      <TD class=arkcontent align=right>$txt{field_box}:</TD>
      <TD><INPUT CLASS=admf  TYPE="TEXT" NAME="box" VALUE="$item{box}" SIZE=4></TD>
  </TR>
  <TR>
      <TD class=arkcontent align=right>$txt{field_zip_code}(*) :</TD>
      <TD><INPUT CLASS=admf  TYPE="TEXT" NAME="zip" VALUE="$item{zip}" SIZE=6></TD>
  </TR>
  <TR>
      <TD class=arkcontent align=right>$txt{field_city}(*) :</TD>
      <TD><INPUT CLASS=admf  TYPE="TEXT" NAME="city" VALUE="$item{city}"></TD>
  </TR>
  <TR>
      <TD class=arkcontent align=right>$txt{field_country}(*) :</TD>
      <TD><INPUT CLASS=admf  TYPE="TEXT" NAME="country" VALUE="$item{country}"></TD>
  </TR>
     <tr>
     <TD class=arkcontent align=right>$txt{field_language}(*) :</TD>
     <TD><select NAME="lg" CLASS=admf>$lg_list</select></TD>
     </TR>  
  <TR>
      <TD COLSPAN=2 CLASS=admd ALIGN=CENTER><BR><INPUT TYPE=SUBMIT CLASS=ARKBUTTON VALUE="$txt{button_saveprofile}"></TD>
  </TR>

   </TABLE>
</FORM>
EOH

return $content;
}

################################################################################
# FIND_PREV_IDENTITY
################################################################################

sub find_prev_identity
{
 my $dbh = $_[0];
 my $id_customer = $_[1];
 
 my $fields = join(",",@identities_fl);
 
 my $stmt = "SELECT $fields FROM identities WHERE id_customer=$id_customer ORDER BY id DESC";

 my $cursor = $dbh->prepare($stmt);
 my $rc = $cursor->execute;
 if (!defined $rc) {suicide($stmt);}

 my %item = %{$cursor->fetchrow_hashref};
 $cursor->finish;
 
 return (\%item);
}

################################################################################
# GET_CURRENCY_LIST
################################################################################

sub get_currency_list
{
 my $curr_list = "";
 
 my $curr_curr = $_[0];
 my $curr_url = $_[1];
 
 if ($curr_curr eq "usd") 
     {
      $str =<<"EOH";
<A HREF="$curr_url&currency=euro" class=arkurl>EURO &euro;</A> | <A HREF="$curr_url&currency=usd" class=arkurl><B>US \$</B></A>
EOH
     }
 else
     {
      $str =<<"EOH";
<A HREF="$curr_url&currency=euro" class=arkurl><B>EURO &euro;</B></A> | <A HREF="$curr_url&currency=usd" class=arkurl>US \$</A>
EOH

     }
 
 $curr_list = <<"EOH";
 <TABLE WIDTH=100%>
  <TR>
   <TD CLASS=arkcontent>&nbsp;</TD>
   <TD CLASS=ARKBUTTON width=50><A HREF="$self?sw=view_caddie&currency=$curr_curr" class=arkurl>$txt{button_checkout}</A></tr></TABLE>
EOH

 return ($curr_list);
}

################################################################################
# FORMAT_CURRENCY
################################################################################

sub format_currency
{
 my $price = $_[0];
 
 if ($currency eq "euro")
     {
      $price.=" EURO";
     }
 else
     {
      $price.=" USD";
     }
     
 return ($price);
}

################################################################################
# CHECK_USER
################################################################################

sub check_user
{
 my $dbh = $_[0];
 my $email = $_[1];
 my $passwd = $_[2];
 
 
 my $stmt = "SELECT id FROM customers WHERE email = '$email' AND password = '$passwd'";

 my $cursor = $dbh->prepare($stmt);
 my $rc = $cursor->execute;
 if (!defined $rc) {suicide($stmt);}

 my $id = $cursor->fetchrow_array;

 $cursor->finish;

 if ($id eq "") {$id = 0;} 
 return $id;	
}


################################################################################
# GET_INVOICE
################################################################################
sub get_invoice
{
 my $dbh = $_[0];
 my $id = $_[1];

  # get identity
  $stmt ="SELECT txt_fact
            FROM orders
           WHERE id = $id";
 
 $cursor = $dbh->prepare($stmt);
 $rc = $cursor->execute;
 if (!defined $rc) {suicide($stmt);}
 my $content = $cursor->fetchrow_array; 

 $cursor->finish;
 
 return $content;	
}

################################################################################
# MAKE_INVOICE
################################################################################

sub make_invoice
{
 my $dbh = $_[0];
 my $order = $_[1];
 my $content = "";
 
 my $txt_line = "<TD>$name</TD><TD>$qty</TD><TD>$price</TD><TD>$subtotal</TD>\n";
 
 # get products
 my $stmt ="SELECT txt_line FROM order_details WHERE id_order = $order ORDER BY id";
 
 my $cursor = $dbh->prepare($stmt);
 my $rc = $cursor->execute;
 if (!defined $rc) {suicide($stmt);}
 
 my $line = "";
 my $prods = "<TABLE><TR><TD>$txt{txt_product}</TD><TD>$txt{txt_qty} </TD><TD>$txt{txt_price} </TD><TD>$txt{txt_subtotal} </TD></TR>";
 while (($line) = $cursor->fetchrow_array)
  {
   $prods.="\n$line";
  }
 $prods.="</TABLE>";
 
 $cursor->finish;


  # get identity
  $stmt ="SELECT customers.lastname,
                 customers.firstname, 
                 customers.email,
                 identities.company,
                 identities.street,
                 identities.number,
                 identities.box,
                 identities.city,
                 identities.zip,
                 identities.country,
                 identities.vat,
                 identities.vat_app,
                 identities.lg,
                 identities.title,
                 orders.currency
            FROM orders,customers,identities 
           WHERE orders.id = $order 
             and orders.id_customer = customers.id
             and orders.id_idbilling = identities.id";
 
 $cursor = $dbh->prepare($stmt);
 $rc = $cursor->execute;
 if (!defined $rc) {suicide($stmt);}
 my ($lastname,$firstname,$email,$company,$street,$number,$box,$city,$zip,$country,$vat,$vat_app,$lg,$title);
 
 ($lastname,$firstname,$email,$company,$street,$number,$box,$city,$zip,$country,$vat,$vat_app,$lg,$title,$currency) = $cursor->fetchrow_array; 
 $cursor->finish;
 
 my $coords = <<"EOH";
 <TABLE><TR><TD>
 $title $lastname $firstname ($email)<BR>
 $company<BR>
 $street $number $box<BR>
 $zip $city<BR>
 $country ($lg)<BR>
 </TD></TR></TABLE>
 
EOH

 $stmt = "SELECT total_".$currency."_tva, total_".$currency."_tvac, total_".$currency."_htva FROM orders WHERE id = $order";
 $cursor = $dbh->prepare($stmt);
 $rc = $cursor->execute;
 if (!defined $rc) {suicide($stmt);}
 my ($tva,$tvac,$htva) = $cursor->fetchrow_array; 
 $cursor->finish;

 
$totline = "TOTAL VAT incl. : $tvac";	

 my $content = "<H3>ORDER LB-000$order</H3><TABLE><TR><TD><HR>BILLING INFORMATION<HR></TD></TR><TR><TD>$coords</TD></TR><TR><TD><HR>ORDER<HR></TD></TR><TR><TD>$prods<HR>$totline</TD></TR></TABLE>";
 
 return $content; 	
}

################################################################################
# GET_EMAIL_FROM_ORDER
################################################################################
sub get_email_from_order
{
 my $dbh = $_[0];
 my $id = $_[1];

  # get identity
  $stmt ="SELECT customers.email
            FROM orders,customers
           WHERE orders.id = $id and orders.id_customer = customers.id";
 
 $cursor = $dbh->prepare($stmt);
 $rc = $cursor->execute;
 if (!defined $rc) {suicide($stmt);}
 my $content = $cursor->fetchrow_array; 

 $cursor->finish;
 
 return $content;	
}

sub get_prod_duration
{
 my $id = $_[0];
 my $lang = $_[1];

 my $name = "";

 if ($id == 1) {
     %year_sing_trad = (
         "fr"=>"an",
         "en"=>"year",
         "nl"=>"jaar",
         "de"=>"Jahr",
         "it"=>"anno",
         "es"=>"aņo",
         "br"=>"ano",
         "pt"=>"ano",
     );

     $name = $year_sing_trad{$lang};
     if ($name eq "") {$year_sing_trad{en};}
     $name = "1 ".$name;
 }
 elsif ($id == 4) {
     %year_plur_trad = (
         "fr"=>"ans",
         "en"=>"years",
         "nl"=>"jaar",
         "de"=>"Jahre",
         "it"=>"anni",
         "es"=>"aņos",
         "br"=>"anos",
         "pt"=>"anos",
 );

     $name = $year_plur_trad{$lang};
     if ($name eq "") {$year_plur_trad{en};}
     $name = "5 ".$name;
 }

 return $name;
}