projekt kivitendo

  my $query = qq{

    SELECT c.accno, c.description, c.charttype, c.category,

      c.link, c.pos_bilanz, c.pos_eur, c.new_chart_id, c.valid_from, 

      c.pos_bwa, datevautomatik,

      tk.taxkey_id, tk.pos_ustva, tk.tax_id,

      tk.tax_id || '--' || tk.taxkey_id AS tax, tk.startdate

    FROM chart c 

    LEFT JOIN taxkeys tk 

    ON (c.id=tk.chart_id AND tk.id = 

      (SELECT id FROM taxkeys 

       WHERE taxkeys.chart_id = c.id AND startdate <= current_date 

       ORDER BY startdate DESC LIMIT 1)) 

    WHERE c.id = ?

    };

  $main::lxdebug->message(LXDebug::QUERY, "\$query=\n $query");

  $main::lxdebug->message(LXDebug::QUERY, "\$query=\n $query");

  $query = qq{

    SELECT 

      id, 

      (SELECT accno FROM chart WHERE id=tax.chart_id) AS chart_accno,

      taxkey,

      id||'--'||taxkey AS tax, 

      taxdescription, 

      rate

    FROM tax ORDER BY taxkey

  };

  $main::lxdebug->message(LXDebug::QUERY, "\$query=\n $query");

                FROM chart 

                WHERE link = ? 

                ORDER BY accno|;

    $main::lxdebug->message(LXDebug::QUERY, "\$query=\n $query");

    # get the taxkeys of account

    $query = qq{

      SELECT

        tk.id,

        tk.chart_id,

        c.accno,

        tk.tax_id,

        t.taxdescription,

        t.rate,

        tk.taxkey_id, 

        tk.pos_ustva, 

        tk.startdate

      FROM taxkeys tk

      LEFT JOIN   tax t ON (t.id = tk.tax_id)

      LEFT JOIN chart c ON (c.id = t.chart_id)

      WHERE tk.chart_id = ?

      ORDER BY startdate DESC 

    };

    $main::lxdebug->message(LXDebug::QUERY, "\$query=\n $query");

    $sth = $dbh->prepare($query);

    $sth->execute($form->{id}) || $form->dberror($query . " ($form->{id})");

    $form->{ACCOUNT_TAXKEYS} = [];

    while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {

      push @{ $form->{ACCOUNT_TAXKEYS} }, $ref;

    }

    $sth->finish;    

  $main::lxdebug->message(LXDebug::QUERY, "\$query=\n $query");

    $main::lxdebug->message(LXDebug::QUERY, "\$query=\n $query");

                  accno = ?, 

                  description = ?, 

                  charttype = ?,

                  category = ?, 

                  link = ?,

                  pos_bwa   = ?, 

                  pos_bilanz = ?,

                  pos_eur = ?, 

                  new_chart_id = ?, 

                  valid_from = ?,

                  datevautomatik = ?

    @values = (   

                  $form->{accno}, 

                  $form->{description}, 

                  $form->{charttype},

                  $form->{category}, 

                  $form->{link},

                  conv_i($form->{pos_bwa}),

                  conv_i($form->{pos_bilanz}), 

                  conv_i($form->{pos_eur}),

                  conv_i($form->{new_chart_id}),

                  conv_date($form->{valid_from}),

                  ($form->{datevautomatik} eq 'T') ? 'true':'false',

                $form->{id},

    );

  } 

  elsif ($form->{id} && !$form->{new_chart_valid}) {

    $query = qq|

                  UPDATE chart 

                  SET new_chart_id = ?, 

                  valid_from = ?

                  WHERE id = ?

             |;

    @values = (   

                  conv_i($form->{new_chart_id}), 

                  conv_date($form->{valid_from}),

                  $form->{id}

              );

  } 

  else {

    $query = qq|

                  INSERT INTO chart (

                      accno, 

                      description, 

                      charttype,

                      category, 

                      link,

                      pos_bwa, 

                      pos_bilanz, 

                      pos_eur,

                      new_chart_id, 

                      valid_from,

                      datevautomatik )

                  VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

             |;

    @values = (

                      $form->{accno}, 

                      $form->{description}, 

                      $form->{charttype},

                      $form->{category}, $form->{link},

                      conv_i($form->{pos_bwa}),

                      conv_i($form->{pos_bilanz}), conv_i($form->{pos_eur}),

                      conv_i($form->{new_chart_id}),

                      conv_date($form->{valid_from}),

                      ($form->{datevautomatik} eq 'T') ? 'true':'false',

              );

  #Save Taxkeys

  my @taxkeys = ();

  my $MAX_TRIES = 10; # Maximum count of taxkeys in form

  my $tk_count;

  READTAXKEYS:

  for $tk_count (0 .. $MAX_TRIES) {

    # Loop control

    # Check if the account already exists, else cancel

    last READTAXKEYS if ( $form->{'id'} == 0);

    # check if there is a startdate

    if ( $form->{"taxkey_startdate_$tk_count"} eq '' ) {

      $tk_count++;

      next READTAXKEYS;

    }

    # check if there is at least one relation to pos_ustva or tax_id

    if ( $form->{"taxkey_pos_ustva_$tk_count"} eq '' && $form->{"taxkey_tax_$tk_count"} == 0 ) {

      $tk_count++;

      next READTAXKEYS;

    }

    # Add valid taxkeys into the array

    push @taxkeys , 

      {

        id        => ($form->{"taxkey_id_$tk_count"} eq 'NEW') ? conv_i('') : conv_i($form->{"taxkey_id_$tk_count"}),

        tax_id    => conv_i($form->{"taxkey_tax_$tk_count"}),

        startdate => conv_date($form->{"taxkey_startdate_$tk_count"}),

        chart_id  => conv_i($form->{"id"}),

        pos_ustva => conv_i($form->{"taxkey_pos_ustva_$tk_count"}),

        delete    => ( $form->{"taxkey_del_$tk_count"} eq 'delete' ) ? '1' : '',

      };

    $tk_count++;

  }

  TAXKEY:

  for my $j (0 .. $#taxkeys){

    if ( defined $taxkeys[$j]{'id'} ){

      # delete Taxkey?

      if ($taxkeys[$j]{'delete'}){

        $query = qq{

          DELETE FROM taxkeys WHERE id = ?

        };

        @values = ($taxkeys[$j]{'id'});

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

        next TAXKEY;

      }

      # UPDATE Taxkey

      $query = qq{

        UPDATE taxkeys

        SET taxkey_id = (SELECT taxkey FROM tax WHERE tax.id = ?),

            chart_id  = ?,

            tax_id    = ?,

            pos_ustva = ?,

            startdate = ?

        WHERE id = ?

      };    

      @values = (

        $taxkeys[$j]{'tax_id'},

        $taxkeys[$j]{'chart_id'},

        $taxkeys[$j]{'tax_id'}, 

        $taxkeys[$j]{'pos_ustva'},

        $taxkeys[$j]{'startdate'}, 

        $taxkeys[$j]{'id'},  

      );

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

    }

    else {

      # INSERT Taxkey

      $query = qq{

        INSERT INTO taxkeys (

          taxkey_id,

          chart_id,

          tax_id,

          pos_ustva,

          startdate

        )

        VALUES ((SELECT taxkey FROM tax WHERE tax.id = ?), ?, ?, ?, ?)

      };    

      @values = (

        $taxkeys[$j]{'tax_id'}, 

        $taxkeys[$j]{'chart_id'},  

        $taxkeys[$j]{'tax_id'}, 

        $taxkeys[$j]{'pos_ustva'},

        $taxkeys[$j]{'startdate'}, 

      );

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

    }

  # delete account taxkeys

  $query = qq|DELETE FROM taxkeys

              WHERE chart_id = ?|;

  do_query($form, $dbh, $query, $form->{id});

  my ($self, $myconfig, $form, $chart_id) = @_;

  my $where = "AND c.id = $chart_id" if ($chart_id ne '');

  $query = qq{

    SELECT 

      c.accno,

      c.id,

      c.description,

      c.charttype,

      c.category,

      c.link,

      c.pos_bwa,

      c.pos_bilanz,

      c.pos_eur,

      c.valid_from,

      c.datevautomatik,

      comma(tk.startdate) AS startdate,

      comma(tk.taxkey_id) AS taxkey,

      comma(tx.taxdescription || to_char (tx.rate, '99V99' ) || '%') AS taxdescription,

      comma(tx.taxnumber) AS taxaccount,

      comma(tk.pos_ustva) AS tk_ustva,

      ( SELECT accno

      FROM chart c2

      WHERE c2.id = c.id

      ) AS new_account

    FROM chart c

    LEFT JOIN taxkeys tk ON (c.id = tk.chart_id)

    LEFT JOIN tax tx ON (tk.tax_id = tx.id)

    WHERE 1=1

    $where

    GROUP BY c.accno, c.id, c.description, c.charttype, c.gifi_accno,

      c.category, c.link, c.pos_bwa, c.pos_bilanz, c.pos_eur, c.valid_from,      

      c.datevautomatik

    ORDER BY c.accno

  };

use SL::USTVA;

use CGI::Ajax;

use CGI;

  if ( $form->{action} eq 'edit_account') {

    $form->{account_exists} = '1';

  } 

  $form->{"$form->{charttype}_checked"} = "checked";

  $form->{"$form->{category}_checked"}  = "checked";

  $form->{select_tax} = "";

  my @tax_report_pos = USTVA->report_variables({

      myconfig   => \%myconfig, 

      form       => $form, 

      type       => '', 

      attribute  => 'position',

      calc       => '',

  });

    }

    # Fill in empty row for new Taxkey

    $newtaxkey_ref = {

      id             => '',

      chart_id       => '',

      accno          => '',

      tax_id         => '',

      taxdescription => '',

      rate           => '',

      taxkey_id      => '',

      pos_ustva      => '',

      startdate      => '',

    };

    push @{ $form->{ACCOUNT_TAXKEYS} }, $newtaxkey_ref;

    my $i = 0;

    foreach my $taxkey_used (@{ $form->{ACCOUNT_TAXKEYS} } ) {

      # Fill in a runningnumber

      $form->{ACCOUNT_TAXKEYS}[$i]{runningnumber} = $i;

      # Fill in the Taxkeys as select options

      foreach my $item (@{ $form->{TAXKEY} }) {

        if ($item->{id} == $taxkey_used->{tax_id}) {

          $form->{ACCOUNT_TAXKEYS}[$i]{selecttaxkey} .=

            qq|<option value="$item->{id}" selected="selected">|

            . sprintf("%.2d", $item->{taxkey}) 

            . qq|. $item->{taxdescription} ($item->{rate}) |

            . $locale->text('Tax-o-matic Account: ') 

            . qq|$item->{chart_accno}\n|;

        } 

        else {

          $form->{ACCOUNT_TAXKEYS}[$i]{selecttaxkey} .=

            qq|<option value="$item->{id}">|

            . sprintf("%.2d", $item->{taxkey}) 

            . qq|. $item->{taxdescription} ($item->{rate}) |

            . $locale->text('Tax-o-matic Account: ')

            . qq|$item->{chart_accno}\n|;

        }

      }

      # Fill in the USTVA Numbers as select options

      foreach my $item ( '', sort({ $a cmp $b } @tax_report_pos) ) {

        if ($item eq ''){

          $form->{ACCOUNT_TAXKEYS}[$i]{select_tax} .= qq|<option value="" selected="selected">-\n|;

        } 

        elsif ( $item == $taxkey_used->{pos_ustva} ) {

          $form->{ACCOUNT_TAXKEYS}[$i]{select_tax} .= qq|<option value="$item" selected="selected">$item\n|;

        }

        else {

          $form->{ACCOUNT_TAXKEYS}[$i]{select_tax} .= qq|<option value="$item">$item\n|;

        }

      }      

      $i++;

  # Newaccount Folgekonto 

      $form->{selectnewaccount} = qq|<option value=""> |. $locale->text('None') .q|</option>|;

          qq|<option value="$item->{id}" selected>$item->{accno}--$item->{description}</option>|;

          qq|<option value="$item->{id}">$item->{accno}--$item->{description}</option>|;

  $select_eur = q|<option value=""> |. $locale->text('None') .q|</option>\n|;

      $select_eur .= qq|<option value=$item selected>|. sprintf("%.2d", $item) .qq|. $eur{$item}</option>\n|;

      $select_eur .= qq|<option value=$item>|. sprintf("%.2d", $item) .qq|. $eur{$item}</option>\n|;

  $select_bwa = q|<option value=""> |. $locale->text('None') .q|</option>\n|;

      $select_bwa .= qq|<option value="$item" selected>|. sprintf("%.2d", $item) .qq|. $bwapos{$item}\n|;

      $select_bwa .= qq|<option value="$item">|. sprintf("%.2d", $item) .qq|. $bwapos{$item}\n|;

# Wieder hinzugef?gt zu evaluationszwecken (us) 09.03.2007

  $select_bilanz = q|<option value=""> |. $locale->text('None') .q|</option>\n|;

  foreach $item ((1, 2, 3, 4)) {

    if ($item == $form->{pos_bilanz}) {

      $select_bilanz .= qq|<option value=$item selected>|. sprintf("%.2d", $item) .qq|.\n|;

    } else {

      $select_bilanz .= qq|<option value=$item>|. sprintf("%.2d", $item) .qq|.\n|;

    }

  }

  # this is for our parser only! Do not remove.

  # preselections category

  $select_category = q|<option value=""> |. $locale->text('None') .q|</option>\n|;

  %category = (

      'A'  => $locale->text('Asset'),

      'L'  => $locale->text('Liability'),

      'Q'  => $locale->text('Equity'),

      'I'  => $locale->text('Revenue'),      

      'E'  => $locale->text('Expense'),

      'C'  => $locale->text('Costs'),

  );

  foreach $item ( sort({ $a <=> $b } keys %category) ) {

    if ($item eq $form->{category}) {

      $select_category .= qq|<option value="$item" selected="selected">$category{$item} (|. sprintf("%s", $item) .qq|)\n|;

    } else {

      $select_category .= qq|<option value="$item">$category{$item} (|. sprintf("%s", $item) .qq|)\n|;

    }

  }

  # preselection chart type

  my $select_charttype = q{};

  my %charttype = (

      'A'  => $locale->text('Account'),

      'H'  => $locale->text('Header'),

  );

  foreach $item ( sort({ $a <=> $b } keys %charttype) ) {

    if ($item eq $form->{charttype}) {

      $select_charttype .= qq|<option value="$item" selected="selected">$charttype{$item}\n|;

    } else {

      $select_charttype .= qq|<option value="$item">$charttype{$item}\n|;

    }

  my $ChartTypeIsAccount = ($form->{charttype} eq "A") ? "1":"";

  $form->header();

  my $parameters_ref = {

    ChartTypeIsAccount         => $ChartTypeIsAccount,

    select_category            => $select_category,

    select_charttype           => $select_charttype,

    newaccount                 => $newaccount,

    checked                    => $checked,

    select_bwa                 => $select_bwa,

    select_bilanz              => $select_bilanz,

    select_eur                 => $select_eur,

  };

  # Ausgabe des Templates

  print($form->parse_html_template('am/edit_accounts', $parameters_ref));

  $form->isblank("accno",       $locale->text('Account Number missing!'));

  $form->isblank("description", $locale->text('Account Description missing!'));

  if ($form->{charttype} eq 'A'){

    $form->isblank("category",  $locale->text('Account Type missing!'));

  }

  # escape callback

  $callback = $form->escape($callback);

  foreach $ca (@{ $form->{CA} }) {

    $ca->{debit}  = " ";

    $ca->{credit} = " ";

    if ($ca->{amount} > 0) {

      $ca->{credit} =

        $form->format_amount(\%myconfig, $ca->{amount}, 2, " ");

    }

    if ($ca->{amount} < 0) {

      $ca->{debit} =

        $form->format_amount(\%myconfig, -1 * $ca->{amount}, 2, " ");

    }

    $ca->{heading}   = ( $ca->{charttype} eq 'H' ) ? 1:''; 

    $ca->{link_edit_account} = 

        qq|$form->{script}?action=edit_account&id=$ca->{id}|

       .qq|&path=$form->{path}&login=$form->{login}|

       .qq|&password=$form->{password}&callback=$callback|;

  }

  # Ajax 

  my $list_account_details_url = 

              "$form->{script}?login=$form->{login}&path=$form->{path}"

             ."&password=$form->{password}&action=list_account_details&";

  my $pjx = new CGI::Ajax( 

             'list_account_details' => $list_account_details_url 

  );

  # Eneable AJAX debuging

  #$pjx->DEBUG(1);

  #$pjx->JSDEBUG(1);

  push(@ { $form->{AJAX} }, $pjx);

  $form->header;

  my $parameters_ref = {

  #   hidden_variables                => $_hidden_variables_ref,

  };

  # Ausgabe des Templates

  print($form->parse_html_template('am/list_accounts', $parameters_ref));

  $lxdebug->leave_sub();

}

sub list_account_details {

# Ajax Funktion aus list_account_details  

  $lxdebug->enter_sub();

  my $chart_id = $form->{args};

  CA->all_accounts(\%myconfig, \%$form, $chart_id);

  $form->{title} = $locale->text('Chart of Accounts');

  # construct callback

  $callback =

    "$form->{script}?action=list_account&path=$form->{path}&login=$form->{login}&password=$form->{password}";

        $form->format_amount(\%myconfig, -1 * $ca->{amount}, 2, " ");

#               : ( $link eq 'CT_tax' )        ? $locale->text('Account Link CT_tax')

      $ca->{link} .= ($link ne '') ?  "[$link] ":'';

    $ca->{datevautomatik} = ($ca->{datevautomatik}) ? $locale->text('On'):$locale->text('Off');

  #my $q = CGI->new();

  my $result = $form->parse_html_template('am/list_account_details', $parameters_ref);

  print $result;

#  print "chart_id:$chart_id, form->chartid:$form->{chart_id}, rest=$rest";

.accountlistheading { 

	font-size: 10pt; 

	padding:3px;

	color: white; 

	font-weight: bold;

	text-align:left;

	background-color:rgb(133,132,129);

}

fieldset {

  margin-top:15px;

  color: black;

  font-weight: bolder;

}

/*

	Form Manager: A simple method of constructing complex dynamic forms.

	Written by Twey, http://www.twey.co.uk/.

	Use, copying, and modification allowed, so long as credit

	remains intact, under the terms of the GNU General Public License,

	version 2 or later. See http://www.gnu.org/copyleft/gpl.html for details.

*/

var FORM_MANAGER_CONDITION_SEPARATOR = " AND ";

var FORM_MANAGER_POSSIBILITY_SEPARATOR = " OR ";

var FORM_MANAGER_NAME_VALUE_SEPARATOR = " BEING ";

var FORM_MANAGER_DEPENDS = "DEPENDS ON ";

var FORM_MANAGER_CONFLICTS = "CONFLICTS WITH ";

var FORM_MANAGER_EMPTY = "EMPTY";

function addEvent(el, ev, f) {

  if(el.addEventListener)

    el.addEventListener(ev, f, false);

  else if(el.attachEvent) {

    var t = function() {

      f.apply(el);

    };

    addEvent.events.push({'element': el, 'event': ev, 'handler': f});

    el.attachEvent("on" + ev, t);

  } else

    el['on' + ev] = f;

}

function addEvents(els, evs, f) {

  for(var i = 0; i < els.length; ++i)

    for(var j = 0; j < evs.length; ++j)

      addEvent(els[i], evs[j], f);

}

addEvent.events = [];

if(typeof window.event !== "undefined")

  addEvent(window, "unload", function() {

      for(var i = 0, e = addEvent.events; i < e.length; ++i)

        e[i].element.detachEvent("on" + e[i].event, e[i].handler);

    }

  );

function getRadioValue(el) {

  if(!el.length) return null;

  for(var i = 0; i < el.length; ++i)

    if(el[i].checked) return el[i].value;

  return null;

}

function getSelectValue(el) {

  if(!el.tagName  || el.tagName.toLowerCase() !== "select")

    return null;

  return el.options[el.selectedIndex].value;

}

function isElementValue(el, v) {

  if(v === FORM_MANAGER_EMPTY) v = '';

  return (

    getRadioValue(el) == v ||

    getSelectValue(el) == v ||

    (

      el.tagName &&

      el.tagName.toLowerCase() !== "select" &&

      el.value == v

    )

  );

}

function setupDependencies() {

  var showEl = function() {

    this.style.display = "";

    if(this.parentNode.tagName.toLowerCase() == "label")

      this.parentNode.style.display = "";

  };

  var hideEl = function() {

    this.style.display = "none";

    if(typeof this.checked !== "undefined") this.checked = false;

    else this.value = "";

    if(this.parentNode.tagName.toLowerCase() == "label")

      this.parentNode.style.display = "none";

    this.hidden = true;

  };

  var calcDeps = function() {

    for(var i = 0, e = this.elements; i < e.length; ++i) {

      e[i].hidden = false;

      for(var j = 0, f = e[i].className.split(FORM_MANAGER_CONDITION_SEPARATOR); j < f.length; ++j)

        if(f[j].indexOf(FORM_MANAGER_DEPENDS) === 0) {

          for(var k = 0, g = f[j].substr(FORM_MANAGER_DEPENDS.length).split(FORM_MANAGER_POSSIBILITY_SEPARATOR); k < g.length; ++k)

            if(g[k].indexOf(FORM_MANAGER_NAME_VALUE_SEPARATOR) === -1) {

	      if(e[g[k]] && e[g[k]].checked) break;

            else if(k + 1 == g.length)

                e[i].hide();

            } else {

              var n = g[k].split(FORM_MANAGER_NAME_VALUE_SEPARATOR),

                v = n[1];

              n = n[0];

	      if(e[n])

	        if(isElementValue(e[n], v)) break;

	        else if(k + 1 == g.length) e[i].hide();

	    }

        } else if(f[j].indexOf(FORM_MANAGER_CONFLICTS) === 0) {

          if(f[j].indexOf(FORM_MANAGER_NAME_VALUE_SEPARATOR) === -1) {

	    if(e[f[j].substr(FORM_MANAGER_CONFLICTS.length)] && e[f[j].substr(FORM_MANAGER_CONFLICTS.length)].checked) {

              e[i].hide();

              break;

            }

          } else {

            var n = f[j].substr(FORM_MANAGER_CONFLICTS.length).split(FORM_MANAGER_NAME_VALUE_SEPARATOR),

              v = n[1];

            n = n[0];

            if(e[n]) {

              if(isElementValue(e[n], v)) {

                e[i].hide();

                break;

              }

            }

          }

        }

      if(!e[i].hidden) e[i].show();

    }

  };

  var changeHandler = function() {

    this.form.calculateDependencies();

    return true;

  };

  for(var i = 0; i < arguments.length; ++i) {

    for(var j = 0, e = window.document.forms[arguments[i]].elements; j < e.length; ++j) {

      addEvents([e[j]], ["change", "keyup", "focus", "click", "keydown"], changeHandler);

      e[j].hide = hideEl;

      e[j].show = showEl;

    }

    (e = window.document.forms[arguments[i]]).calculateDependencies = calcDeps;

    e.calculateDependencies();

  }

}

  'Account Description missing!' => 'Beschreibung fehlt!',

  'Chart Type'                  => 'Kontentyp',

  'Datevautomatik'              => 'Datevexport',

  'Description (Click on Description for details)' => 'Beschreibung (Klick

  ?ffnet einzelne Kontendetails)',

  'Header'                      => '?berschrift',

  'Main Preferences'            => 'Grundeinstellungen',

  'None'                        => 'Kein',

  'Off'                         => 'Aus',

  'Posustva_coa'                => 'USTVA Kennz.',

  'Report Positions'            => 'Berichte',

  'Report and misc. Preferences' => 'Sonstige Einstellungen',

  'Save account first to insert taxkeys' => 'Einstellungen sind nach

  dem Speichern des Kontos verf?gbar...',

  'Startdate_coa'               => 'G?ltig ab',

  'Tax-o-matic Account: '       => '',

  'Taxaccount_coa'              => 'Automatikkonto',

  'Taxdescription_coa'          => 'Steuer',

  'Taxkey_coa'                  => 'Steuerschl?ssel',

  'Taxkeys and Taxreport Preferences' => 'Steuerautomatik und UStVA',

  'Taxlink_coa'                 => 'Steuerautomatik',