projekt kivitendo

sub adr {

  $main::lxdebug->enter_sub();

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

  # connect to database

  my $dbh = $form->dbconnect($myconfig);

  my $query = qq|SELECT id, adr_description, adr_code

                 FROM adr

		 ORDER BY adr_code|;

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

  $sth->execute || $form->dberror($query);

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

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

  }

  $sth->finish;

  $dbh->disconnect;

  $main::lxdebug->leave_sub();

}

sub get_adr {

  $main::lxdebug->enter_sub();

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

  # connect to database

  my $dbh = $form->dbconnect($myconfig);

  my $query =

    qq|SELECT a.adr_description, a.adr_code

                 FROM adr a

	         WHERE a.id = $form->{id}|;

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

  $sth->execute || $form->dberror($query);

  my $ref = $sth->fetchrow_hashref(NAME_lc);

  map { $form->{$_} = $ref->{$_} } keys %$ref;

  $sth->finish;

  $dbh->disconnect;

  $main::lxdebug->leave_sub();

}

sub save_adr {

  $main::lxdebug->enter_sub();

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

  # connect to database

  my $dbh = $form->dbconnect($myconfig);

  $form->{adr_description} =~ s/\'/\'\'/g;

  $form->{adr_code} =~ s/\'/\'\'/g;

  # id is the old record

  if ($form->{id}) {

    $query = qq|UPDATE adr SET

		adr_description = '$form->{adr_description}',

		adr_code = '$form->{adr_code}'

		WHERE id = $form->{id}|;

  } else {

    $query = qq|INSERT INTO adr

                (adr_description, adr_code)

                VALUES ('$form->{adr_description}', '$form->{adr_code}')|;

  }

  $dbh->do($query) || $form->dberror($query);

  $dbh->disconnect;

  $main::lxdebug->leave_sub();

}

sub delete_adr {

  $main::lxdebug->enter_sub();

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

  # connect to database

  my $dbh = $form->dbconnect($myconfig);

  $query = qq|DELETE FROM adr

	      WHERE id = $form->{id}|;

  $dbh->do($query) || $form->dberror($query);

  $dbh->disconnect;

  $main::lxdebug->leave_sub();

}

  my $query = qq|select shiptoname, adr_code, $tabelle.transdate, $tabelle.invnumber, $tabelle.ordnumber, invoice.description, qty, invoice.unit FROM $tabelle LEFT JOIN shipto ON |;

  $query .=qq|LEFT join invoice on ($tabelle.id=invoice.trans_id) LEFT join parts ON (parts.id=invoice.parts_id) LEFT join adr ON (parts.adr_id=adr.id) $where ORDER BY $tabelle.transdate DESC LIMIT 15|;

sub adr {

  $main::lxdebug->enter_sub();

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

  my $dbh   = $form->dbconnect($myconfig);

  $where = " WHERE 1=1 ";

  if ($form->{from}) {

    $where .= "AND ar.transdate >= '$form->{from}' ";

  }

  if ($form->{to}) {

    $where .= "AND ar.transdate <= '$form->{to}' ";

  }

  if ($form->{year}) {

    $where = " WHERE ar.transdate >= '$form->{year}-01-01' AND ar.transdate <= '$form->{year}-12-31' ";

  }

  my $query = qq|select adr_code, adr_description, sum(base_qty), parts.unit from ar LEFT join invoice on (ar.id=invoice.trans_id) LEFT join parts ON (invoice.parts_id=parts.id) LEFT join adr ON (adr.id=parts.adr_id) $where GROUP BY adr_code,adr_description,parts.unit|;

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

  $sth->execute || $form->dberror($query);

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

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

  }

  $sth->finish;

  $dbh->disconnect;

  $main::lxdebug->leave_sub();

}

  # get adr

  $query = qq|SELECT id, adr_description, adr_code

              FROM adr|;

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

  $sth->execute || $form->dberror($query);

  $self->{ADR} = [];

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

    push @{ $self->{ADR} }, $ref;

  }

  $sth->finish;

  $form->{adr_id}       *= 1;

	      adr_id = '$form->{adr_id}',

  # get adr

  $query = qq|SELECT id, adr_description, adr_code

              FROM adr|;

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

  $sth->execute || $form->dberror($query);

  $form->{ADR} = [];

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

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

  }

  $sth->finish;

		 pg.partsgroup, p.formel, p.alu, p.payment_id AS part_payment_id, adr.adr_description

                 LEFT JOIN adr adr ON (adr.id = p.adr_id)

sub add_adr {

  $lxdebug->enter_sub();

  $form->{title} = "Add";

  $form->{callback} =

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

    unless $form->{callback};

  &adr_header;

  &form_footer;

  $lxdebug->leave_sub();

}

sub edit_adr {

  $lxdebug->enter_sub();

  $form->{title} = "Edit";

  AM->get_adr(\%myconfig, \%$form);

  &adr_header;

  $form->{orphaned} = 1;

  &form_footer;

  $lxdebug->leave_sub();

}

sub list_adr {

  $lxdebug->enter_sub();

  AM->adr(\%myconfig, \%$form);

  $form->{callback} =

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

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

  $form->{title} = $locale->text('ADR');

  @column_index = qw(adr_code adr_description);

  $column_header{adr_description} =

      qq|<th class=listheading width=60%>|

    . $locale->text('ADR Description')

    . qq|</th>|;

  $column_header{adr_code} =

      qq|<th class=listheading width=10%>|

    . $locale->text('ADR Code')

    . qq|</th>|;

  $form->header;

  print qq|

<body>

<table width=100%>

  <tr>

    <th class=listtop>$form->{title}</th>

  </tr>

  <tr height="5"></tr>

  <tr>

    <td>

      <table width=100%>

        <tr class=listheading>

|;

  map { print "$column_header{$_}\n" } @column_index;

  print qq|

        </tr>

|;

  foreach $ref (@{ $form->{ALL} }) {

    $i++;

    $i %= 2;

    print qq|

        <tr valign=top class=listrow$i>

|;

    $column_data{adr_code} =

      qq|<td><a href=$form->{script}?action=edit_adr&id=$ref->{id}&path=$form->{path}&login=$form->{login}&password=$form->{password}&callback=$callback>$ref->{adr_code}</td>|;

    $column_data{adr_description}           = qq|<td align=left>$ref->{adr_description}</td>|;

    map { print "$column_data{$_}\n" } @column_index;

    print qq|

	</tr>

|;

  }

  print qq|

      </table>

    </td>

  </tr>

  <tr>

  <td><hr size=3 noshade></td>

  </tr>

</table>

<br>

<form method=post action=$form->{script}>

<input name=callback type=hidden value="$form->{callback}">

<input type=hidden name=type value=adr>

<input type=hidden name=path value=$form->{path}>

<input type=hidden name=login value=$form->{login}>

<input type=hidden name=password value=$form->{password}>

<input class=submit type=submit name=action value="|

    . $locale->text('Add') . qq|">|;

  if ($form->{menubar}) {

    require "$form->{path}/menu.pl";

    &menubar;

  }

  print qq|

  </form>

  </body>

  </html>

|;

  $lxdebug->leave_sub();

}

sub adr_header {

  $lxdebug->enter_sub();

  $form->{title}    = $locale->text("$form->{title} ADR");

  # $locale->text('Add ADR')

  # $locale->text('Edit ADR')

  $form->{adr_description} =~ s/\"/"/g;

  $form->{adr_code} =~ s/\"/"/g;

  $form->header;

  print qq|

<body>

<form method=post action=$form->{script}>

<input type=hidden name=id value=$form->{id}>

<input type=hidden name=type value=adr>

<table width=100%>

  <tr>

    <th class=listtop colspan=2>$form->{title}</th>

  </tr>

  <tr height="5"></tr>

  <tr>

    <th align=right>| . $locale->text('ADR Code') . qq|</th>

    <td><input name=adr_code size=30 value="$form->{adr_code}"></td>

  <tr>

  <tr>

    <th align=right>| . $locale->text('ADR Description') . qq|</th>

    <td><input name=adr_description size=60 value="$form->{adr_description}"></td>

  </tr>

  <td colspan=2><hr size=3 noshade></td>

  </tr>

</table>

|;

  $lxdebug->leave_sub();

}

sub save_adr {

  $lxdebug->enter_sub();

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

  $form->isblank("adr_code", $locale->text('ADR Code missing!'));

  AM->save_adr(\%myconfig, \%$form);

  $form->redirect($locale->text('ADR saved!'));

  $lxdebug->leave_sub();

}

sub delete_adr {

  $lxdebug->enter_sub();

  AM->delete_adr(\%myconfig, \%$form);

  $form->redirect($locale->text('ADR deleted!'));

  $lxdebug->leave_sub();

}

sub search_adr {

  $lxdebug->enter_sub();

  $form->{title} = $locale->text('ADR Report');

  # use JavaScript Calendar or not

  $form->{jsscript} = $jscalendar;

  $jsscript = "";

  if ($form->{jsscript}) {

    # with JavaScript Calendar

    $button1 = qq|

       <td><input name=from id=from size=11 title="$myconfig{dateformat}" value=$form->{from}></td>

       <td><input type=button name=from id="trigger1" value=|

      . $locale->text('button') . qq|></td>

       |;

    $button2 = qq|

       <td width="13"><input name=to id=to size=11 title="$myconfig{dateformat}" value=$form->{to}></td>

       <td width="4"><input type=button name=to id="trigger2" value=|

      . $locale->text('button') . qq|></td></td>

     |;

    #write Trigger

    $jsscript =

      Form->write_trigger(\%myconfig,     "2",

                          "from",      "BL",

                          "trigger1",     "to",

                          "BL",           "trigger2");

  } else {

    # without JavaScript Calendar

    $button1 =

      qq|<td><input name=from size=11 title="$myconfig{dateformat}" value=$form->{from}></td>|;

    $button2 =

      qq|<td width="13"><input name=to size=11 title="$myconfig{dateformat}" value=$form->{to}></td>|;

  }

  $form->header;

  print qq|

<body>

<form method=post action=$form->{script}>

<table width=100%>

  <tr>

    <th class=listtop>$form->{title}</th>

  </tr>

  <tr height="5"></tr>

  <tr valign=top>

    <td>

      <table>

        <tr>

          <th align=right nowrap>| . $locale->text('Year') . qq|</th>

          <td><input name=year type=input value=$form->{year}></td>

	<tr>

	  <th align=right nowrap>| . $locale->text('From') . qq|</th>

          $button1

	</tr>

	<tr>

	  <th align=right nowrap>| . $locale->text('Bis') . qq|</th>

	  $button2

	</tr>

	<tr>

	  <td></td>

	  <td><input name=format class=radio type=radio value=html checked>&nbsp;|

    . $locale->text('HTML') . qq|

	  <input name=format class=radio type=radio value=csv>&nbsp;|

    . $locale->text('CSV') . qq|</td>

	</tr>

      </table>

    </td>

  </tr>

  <tr>

    <td><hr size=3 noshade></td>

  </tr>

</table>

$jsscript

<input type=hidden name=nextsub value=adr_report>

<input type=hidden name=path value=$form->{path}>

<input type=hidden name=login value=$form->{login}>

<input type=hidden name=password value=$form->{password}>

<br>

<input type=submit class=submit name=action value="|

    . $locale->text('Continue') . qq|">

</form>

</body>

</html>

|;

  $lxdebug->leave_sub();

}

sub adr_report {

  $lxdebug->enter_sub();

  CT->adr(\%myconfig, \%$form);

  $form->{title} =  $locale->text('ADR Report');

  if ($form->{from} || $form->{to}) {

    $option = "F?r den Zeitraum $form->{from} bis $form->{to}";

  } elsif ($form->{year}) {

    $option = "F?r das Jahr $form->{year}";

  }

  if ($form->{format} eq "html") {

      $form->header();

      print qq|

<body>

<table width=100%>

  <tr>

    <th class=listtop>$form->{title}</th>

  </tr>

  <tr height="5"></tr>

  <tr>

    <td>$option</td>

  </tr>

</table>|;

  @column_index =

    $form->sort_columns(adr_code,

                        adr_description,

                        sum,

                        unit);

  $column_header{adr_code} =

    qq|<th class=listheading>| . $locale->text('ADR Code') . qq|</th>|;

  $column_header{adr_description} =

      qq|<th class=listheading>|. $locale->text('ADR Description'). qq|</th>|;

  $column_header{sum} =

      qq|<th class=listheading>|. $locale->text('Quantity'). qq|</th>|;

  $column_header{unit} =

    qq|<th class=listheading>| . $locale->text('Unit') . qq|</th>|;

  print qq|

<table width=100%>

  <tr>

    <td>

      <table width=100%>

	<tr class=listheading>

|;

  map { print "$column_header{$_}\n" } @column_index;

  print qq|

        </tr>

|;

  foreach $ref (@{ $form->{ADR} }) {

    map { $column_data{$_} = "<td>$ref->{$_}&nbsp;</td>" } @column_index;

    $i++;

    $i %= 2;

    print "

        <tr class=listrow$i>

";

    map { print "$column_data{$_}\n" } @column_index;

    print qq|

        </tr>

|;

  }

  print qq|

</table>

</body>

</html>

|;

  } else {

  my $filename = "adr-report-$form->{from}-$form->{to}.csv";

  if ($form->{year}) {

    $filename = "adr-report-$form->{year}.csv";

  }

  @column_index =

  $form->sort_columns(adr_code,

                      adr_description,

                      sum,

                      unit);

  $tmpfile = qq|ADR Code;ADR Beschreibung;Menge;Einheit\n|;

    foreach $ref (@{ $form->{ADR} }) {

      map { $column_data{$_} = qq|"$ref->{$_}"| } @column_index;

      $line = "";

      map { $line .= "$column_data{$_};" } @column_index;

      chomp($line);

      $tmpfile .= qq|$line\n|;;

    }

  my $size = length($tmpfile);

  # launch application

  print qq|Content-Type: application/csv

Content-Disposition: attachment; filename="$filename"

Content-Length: $size

$tmpfile

|;

  }

  $lxdebug->leave_sub();

}

                        adr_code,

  $column_header{adr_code} =

    qq|<th class=listheading>| . $locale->text('ADR') . qq|</th>|;

  $form->{selectadr} = "<option></option>";

  if (@{ $form->{ADR} }) {

    foreach $item (@{ $form->{ADR} }) {

      if ($item->{id} == $form->{adr_id}) {

        $form->{selectadr} .=

          "<option value=$item->{id} selected>$item->{adr_code}\n";

      } else {

        $form->{selectadr} .=

          "<option value=$item->{id}>$item->{adr_code}\n";

      }

    }

  }

  $adr = qq|

	      <tr>

		<th align=right>| . $locale->text('ADR') . qq|</th>

		<td><select name=adr_id>$form->{selectadr}</select></td>

	      </tr>|;

                  $adr

<input type=hidden name="adr_description_$i" value="$form->{"adr_description_$i"}">

<input name="new_adr_description_$i" type=hidden value="$ref->{adr_description}">

    qw(id partnumber description sellprice listprice inventory_accno income_accno expense_accno bin unit weight assembly taxaccounts partsgroup formel alu longdescription not_discountable adr_description);

    qw(id partnumber description qty ship sellprice unit discount inventory_accno income_accno expense_accno listprice taxaccounts bin assembly weight projectnumber project_id oldprojectnumber runningnumber serialnumber partsgroup payment_id not_discountable shop ve gv buchungsgruppen_id adr_id language_values)