#=====================================================================
# LX-Office ERP
# Copyright (C) 2008
# Based on SQL-Ledger Version 2.1.9
# Web http://www.lx-office.org
#
#=====================================================================
#
# Letter module
#
#=====================================================================

package SL::Letter;

use strict;
use List::Util qw(max);

use SL::Common;
use SL::CT;
use SL::DBUtils;
use SL::MoreCommon;
use SL::TransNumber;
use SL::DB::Manager::Customer;

my $DEFINITION = <<SQL;
                                      Table "public.letter"
        Column       |            Type             |                Modifiers
  -------------------+-----------------------------+------------------------------------------
   id                | integer                     | not null default nextval('id'::regclass)
   vc_id             | integer                     | not null
   letternumber      | text                        |
   jobnumber         | text                        |
   text_created_for  | text                        |
   date              | date                        |
   subject           | text                        |
   greeting          | text                        |
   body              | text                        |
   close             | text                        |
   company_name      | text                        |
   employee_id       | integer                     |
   employee_position | text                        |
   salesman_id       | integer                     |
   salesman_position | text                        |
   itime             | timestamp without time zone | default now()
   mtime             | timestamp without time zone |
   page_created_for  | text                        |
   intnotes          | text                        |
   cp_id             | integer                     |
   reference         | text                        |
  Indexes:
      "letter_pkey" PRIMARY KEY, btree (id)
  Foreign-key constraints:
      "letter_cp_id_fkey" FOREIGN KEY (cp_id) REFERENCES contacts(cp_id)
      "letter_employee_id_fkey" FOREIGN KEY (employee_id) REFERENCES employee(id)
      "letter_salesman_id_fkey" FOREIGN KEY (salesman_id) REFERENCES employee(id)
SQL

# XXX not working yet
#sub customer {
#  my $self = shift;
#
#  die 'not a setter' if @_;
#
#  return unless $self->{customer_id};
#
#  # resolve customer_obj
#}

sub new {
  my $class  = ref $_[0] || $_[0]; shift;
  my %params = @_;
  my $ref    = $_[0];

  $ref = ref $_[0] eq 'HASH' ? $ref : \%params; # don't like it either...

  my $self = bless $ref, $class;

  $self->_lastname_used;
  $self->_resolve_customer;
  $self->set_greetings;

  return $self;
}

sub _create {
  my $self = shift;
  my $dbh  = $::form->get_standard_dbh;
  ($self->{id}) = selectfirst_array_query($::form, $dbh, "select nextval('id')");

  do_query($::form, $dbh, <<SQL, $self->{id}, $self->{customer_id});
    INSERT INTO letter (id, vc_id) VALUES (?, ?);
SQL
}

sub _create_draft {
  my $self = shift;
  my $dbh  = $::form->get_standard_dbh;
  ($self->{draft_id}) = selectfirst_array_query($::form, $dbh, "select nextval('id')");

  do_query($::form, $dbh, <<SQL, $self->{draft_id}, $self->{customer_id});
    INSERT INTO letter_draft (id, vc_id) VALUES (?, ?);
SQL
}


sub save {
  $::lxdebug->enter_sub;

  my $self     = shift;
  my %params   = @_;
  my $dbh      = $::form->get_standard_dbh;
  my ($table, $update_value);

  if ($params{draft}) {
    $self->_create_draft unless $self->{draft_id};
    $table = 'letter_draft';
    $update_value = 'draft_id';
  } else {
    $self->_create unless $self->{id};
    $table = 'letter';
    $update_value = 'id';
  }

  my %fields         = __PACKAGE__->_get_fields;
  my %field_mappings = __PACKAGE__->_get_field_mappings;

  delete $fields{id};

  my @update_fields = keys %fields;
  my $set_clause    = join ', ', map { "$_ = ?" } @update_fields;
  my @values        = map { _escaper($_)->( $self->{ $field_mappings{$_} || $_ } ) } @update_fields, $update_value;

  my $query = "UPDATE $table SET $set_clause WHERE id = ?";

  do_query($::form, $dbh, $query, @values);

  $dbh->commit;

  $::lxdebug->leave_sub;
}

sub find {
  $::lxdebug->enter_sub;

  my $class    = ref $_[0] || $_[0]; shift;
  my $myconfig = \%main::myconfig;
  my $form     = $main::form;
  my $dbh      = $form->get_standard_dbh($myconfig);
  my %params   = @_;
  my $letter_table = 'letter';

  $letter_table = 'letter_draft' if $params{draft};
  %params = %$form if  !scalar keys %params;

  my (@wheres, @values);
  my $add_token = sub { add_token(\@wheres, \@values, @_) };

  $add_token->(col => 'letter.id',           val => $params{id},           esc => 'id'    ) if $params{id};
  $add_token->(col => 'letter.letternumber', val => $params{letternumber}, esc => 'substr') if $params{letternumber};
  $add_token->(col => 'vc.name',             val => $params{customer},     esc => 'substr') if $params{customer};
  $add_token->(col => 'vc.id',               val => $params{customer_id},  esc => 'id'    ) if $params{customer_id};
  $add_token->(col => 'letter.cp_id',        val => $params{cp_id},        esc => 'id'    ) if $params{cp_id};
  $add_token->(col => 'ct.cp_name',          val => $params{contact},      esc => 'substr') if $params{contact};
  $add_token->(col => 'letter.subject',      val => $params{subject},      esc => 'substr') if $params{subject};
  $add_token->(col => 'letter.body',         val => $params{body},         esc => 'substr') if $params{body};
  $add_token->(col => 'letter.date',         val => $params{date_from}, method => '>='    ) if $params{date_from};
  $add_token->(col => 'letter.date',         val => $params{date_to},   method => '<='    ) if $params{date_to};

  my $query = qq|
    SELECT $letter_table.*, vc.name AS customer, vc.id AS customer_id, ct.cp_name AS contact FROM $letter_table
      LEFT JOIN customer vc ON vc.id = $letter_table.vc_id
      LEFT JOIN contacts ct ON $letter_table.cp_id = ct.cp_id
  |;

  if (@wheres) {
    $query .= ' WHERE ' . join ' AND ', @wheres;
  }

  my @results = selectall_hashref_query($form, $dbh, $query, @values);
  my @objects = map { $class->new($_) } @results;

  $::lxdebug->leave_sub;

  return @objects;
}

sub delete {
  $::lxdebug->enter_sub;

  my $self     = shift;

  do_query($::form, $::form->get_standard_dbh, <<SQL, $self->{id});
    DELETE FROM letter WHERE id = ?
SQL

  $::form->get_standard_dbh->commit;

  $::lxdebug->leave_sub;
}

sub delete_drafts {
  $::lxdebug->enter_sub;

  my $self        = shift;
  my @draft_ids   = @_;

  my $form        = $main::form;
  my $myconfig = \%main::myconfig;
  my $dbh         = $form->get_standard_dbh($myconfig);


  return $main::lxdebug->leave_sub() unless (@draft_ids);

  my  $query = qq|DELETE FROM letter_draft WHERE id IN (| . join(", ", map { "?" } @draft_ids) . qq|)|;
  do_query($form, $dbh, $query, @draft_ids);

  $dbh->commit;

  $::lxdebug->leave_sub;
}


sub check_number {
  my $self = shift;

  return if $self->{letternumber}
         && $self->{id}
         && 1 == scalar __PACKAGE__->find(letternumber => $self->{letternumber});

  $self->{letternumber} = SL::TransNumber->new(type => 'letter', id => $self->{id}, number => $self->{letternumber})->create_unique;
}

sub check_name {
  my $self   = shift;
  my %params = @_;

  unless ($params{_name_selected}) {
    $::form->{$_} = $self->{$_} for qw(oldcustomer customer selectcustomer customer_id);

    if (::check_name('customer')) {
      $self->_set_customer_from($::form);
    }
  } else {
    $self->_set_customer_from($::form);
  }
}

sub _set_customer_from {
  my $self = shift;
  my $from = shift;

  $self->{$_} = $from->{$_} for qw(oldcustomer customer_id customer selectcustomer);

  $self;
}

sub check_date {
  my $self = shift;
  $self->{date} ||= $::form->current_date(\%::myconfig);
}

sub load {
  my $self   = shift;
  my $table  = 'letter';
  my $draft = $self->{draft};
  $table     = 'letter_draft' if $draft;


  return $self unless $self && $self->{id}; # no id? dont load.

  my %mappings      = _get_field_mappings();
  my $mapped_select = join ', ', '*', map { "$_ AS $mappings{$_}" } keys %mappings;

  my ($db_letter) = selectfirst_hashref_query($::form, $::form->get_standard_dbh, <<SQL, $self->{id});
    SELECT $mapped_select FROM $table WHERE id = ?
SQL

  $self->update_from($db_letter);
  $self->_resolve_customer;
  $self->set_greetings;
  $self->{draft_id} = delete $self->{id} if $draft;  # set draft if we have one

  return $self;
}

sub update_from {
  my $self   = shift;
  my $src    = shift;
  my %fields = $self->_get_fields;

  $fields{$_} = $src->{$_} for qw{customer_id customer selectcustomer oldcustomer}; # customer stuff

  $self->{$_} = $src->{$_} for keys %fields;

  return $self;
}

sub export_to {
  my $self = shift;
  my $form = shift;

  my %fields         = $self->_get_fields;
  my %field_mappings = $self->_get_field_mappings;

  for (keys %fields) {
    $form->{$_} =  _escaper($_)->( $self->{ $field_mappings{$_} || $_ } );
  }
}

sub language {
  my $self = shift;
  die 'not a setter' if @_;

  return unless $self->{cp_id};

  # umetec/cetaq only!
  # contacts have a custom variable called "mailing"
  # it contains either a language code or the string "No"

  my $custom_variables = CVar->get_custom_variables(
    module      => 'Contacts',
    name_prefix => 'cp',
    trans_id    => $self->{cp_id},
  );

  my ($mailing) = grep { $_->{name} eq 'Mailing' } @$custom_variables;

  return $mailing->{value} eq 'No' ? undef : $mailing->{value};
}

sub set_greetings {
  $::lxdebug->enter_sub;

  my $self = shift;
  return $::lxdebug->leave_sub if $self->{greeting};

  # automatically set greetings
  # greetings depend mainly on contact person
#   my $contact = $self->_get_contact;

  $self->{greeting} = $::locale->text('Dear Sir or Madam,');

  $::lxdebug->leave_sub;
}

sub _lastname_used {
  # wrapper for form lastname_used
  # sets customer to last used customer,
  # also used to initalize customer for new objects
  my $self = shift;

  return if $self->{customer_id};

  my $saved_form = save_form($::form);

  $::form->lastname_used($::form->get_standard_dbh, \%::myconfig, 'customer');

  $self->{customer_id} = $::form->{customer_id};
  $self->{customer}    = $::form->{customer};

  restore_form($saved_form);

  return $self;
}

sub _resolve_customer {
  # used if an object is created with only id.
  my $self = shift;

  return unless $self->{customer_id} && !$self->{customer};

#  my ($customer) = CT->find_by_id(cv => 'customer', id => $self->{customer_id});
#  my ($customer) = CT->find_by_id(cv => 'customer', id => $self->{customer_id});
  # SL/CVar.pm:        : $cfg->{type} eq 'customer'  ? (SL::DB::Manager::Customer->find_by(id => 1*$ref->{number_value}) || SL::DB::Customer->new)->name
  $self->{customer} = SL::DB::Manager::Customer->find_by(id => $self->{customer_id})->name; # || SL::DB::Customer->new)->name


}

sub _get_definition {
  $DEFINITION;
}

sub _get_field_mappings {
  return (
    vc_id => 'customer_id',
  );
}

sub _get_fields {
  my %fields = _get_definition() =~ /(\w+) \s+ \| \s+ (integer|text|timestamp|numeric|date)/xg;
}

sub _escaper {
  my $field_name = shift;
  my %fields     = __PACKAGE__->_get_fields;

  for ($fields{$field_name}) {
    return sub { conv_i(shift) } if /integer/;
    return sub { shift };
  }
}

1;