use SL::CVar;
use SL::DBUtils;
+use SL::TransNumber;
use strict;
#correct rows
$form->{price_rows} = $i - 1;
- unless ($form->{item} eq 'service') {
+ # get makes
+ if ($form->{makemodel}) {
+ #hli
+ $query = qq|SELECT m.make, m.model,m.lastcost,m.lastcost,m.lastupdate,m.sortorder FROM makemodel m | .
+ qq|WHERE m.parts_id = ? order by m.sortorder asc|;
+ my @values = ($form->{id});
+ $sth = $dbh->prepare($query);
+ $sth->execute(@values) || $form->dberror("$query (" . join(', ', @values) . ")");
- # get makes
- if ($form->{makemodel}) {
- $query = qq|SELECT m.make, m.model FROM makemodel m | .
- qq|WHERE m.parts_id = ?|;
- my @values = ($form->{id});
- $sth = $dbh->prepare($query);
- $sth->execute(@values) || $form->dberror("$query (" . join(', ', @values) . ")");
-
- my $i = 1;
- while (($form->{"make_$i"}, $form->{"model_$i"}) = $sth->fetchrow_array)
- {
- $i++;
- }
- $sth->finish;
- $form->{makemodel_rows} = $i - 1;
+ my $i = 1;
+ while (($form->{"make_$i"}, $form->{"model_$i"}, $form->{"old_lastcost_$i"},
+ $form->{"lastcost_$i"}, $form->{"lastupdate_$i"}, $form->{"sortorder_$i"}) = $sth->fetchrow_array)
+ {
+ $i++;
}
+ $sth->finish;
+ $form->{makemodel_rows} = $i - 1;
+
}
# get translations
my ($self, $myconfig, $form) = @_;
my @values;
# connect to database, turn off AutoCommit
- my $dbh = $form->dbconnect_noauto($myconfig);
+ my $dbh = $form->get_standard_dbh;
# save the part
# make up a unique handle and store in partnumber field
my $priceupdate = ', priceupdate = current_date';
if ($form->{id}) {
+ my $trans_number = SL::TransNumber->new(type => $form->{item}, dbh => $dbh, number => $form->{partnumber}, id => $form->{id});
+ if (!$trans_number->is_unique) {
+ $::lxdebug->leave_sub;
+ return 3;
+ }
# get old price
$query = qq|SELECT sellprice, weight FROM parts WHERE id = ?|;
}
$sth->finish;
- if ($form->{item} ne 'service') {
- # delete makemodel records
- do_query($form, $dbh, qq|DELETE FROM makemodel WHERE parts_id = ?|, conv_i($form->{id}));
- }
+ # delete makemodel records
+ do_query($form, $dbh, qq|DELETE FROM makemodel WHERE parts_id = ?|, conv_i($form->{id}));
if ($form->{item} eq 'assembly') {
# delete assembly records
$priceupdate = '' if (all { $previous_values->{$_} == $form->{$_} } qw(sellprice lastcost listprice));
} else {
- my ($count) = selectrow_query($form, $dbh, qq|SELECT COUNT(*) FROM parts WHERE partnumber = ?|, $form->{partnumber});
- if ($count) {
- $main::lxdebug->leave_sub();
+ my $trans_number = SL::TransNumber->new(type => $form->{item}, dbh => $dbh, number => $form->{partnumber}, save => 1);
+
+ if ($form->{partnumber} && !$trans_number->is_unique) {
+ $::lxdebug->leave_sub;
return 3;
}
+ $form->{partnumber} ||= $trans_number->create_unique;
+
($form->{id}) = selectrow_query($form, $dbh, qq|SELECT nextval('id')|);
- do_query($form, $dbh, qq|INSERT INTO parts (id, partnumber) VALUES (?, '')|, $form->{id});
+ do_query($form, $dbh, qq|INSERT INTO parts (id, partnumber, unit) VALUES (?, ?, ?)|, $form->{id}, $form->{partnumber}, $form->{unit});
$form->{orphaned} = 1;
- if ($form->{partnumber} eq "" && $form->{"item"} eq "service") {
- $form->{partnumber} = $form->update_defaults($myconfig, "servicenumber");
- }
- if ($form->{partnumber} eq "" && $form->{"item"} ne "service") {
- $form->{partnumber} = $form->update_defaults($myconfig, "articlenumber");
- }
-
}
my $partsgroup_id = 0;
}
# insert makemodel records
- unless ($form->{item} eq 'service') {
+ my $lastupdate = '';
+ my $value = 0;
for my $i (1 .. $form->{makemodel_rows}) {
if (($form->{"make_$i"}) || ($form->{"model_$i"})) {
-
- $query = qq|INSERT INTO makemodel (parts_id, make, model) | .
- qq|VALUES (?, ?, ?)|;
- @values = (conv_i($form->{id}), conv_i($form->{"make_$i"}), $form->{"model_$i"});
+ #hli
+ $value = $form->parse_amount($myconfig, $form->{"lastcost_$i"});
+ if ($value == $form->parse_amount($myconfig, $form->{"old_lastcost_$i"}))
+ {
+ if ($form->{"lastupdate_$i"} eq "") {
+ $lastupdate = 'now()';
+ } else {
+ $lastupdate = $dbh->quote($form->{"lastupdate_$i"});
+ }
+ } else {
+ $lastupdate = 'now()';
+ }
+ $query = qq|INSERT INTO makemodel (parts_id, make, model, lastcost, lastupdate, sortorder) | .
+ qq|VALUES (?, ?, ?, ?, ?, ?)|;
+ @values = (conv_i($form->{id}), conv_i($form->{"make_$i"}), $form->{"model_$i"}, $value, $lastupdate, conv_i($form->{"sortorder_$i"}) );
do_query($form, $dbh, $query, @values);
}
}
- }
# insert taxes
foreach my $item (split(/ /, $form->{taxaccounts})) {
}
}
- CVar->save_custom_variables('dbh' => $dbh,
- 'module' => 'IC',
- 'trans_id' => $form->{id},
- 'variables' => $form);
+ CVar->save_custom_variables(dbh => $dbh,
+ module => 'IC',
+ trans_id => $form->{id},
+ variables => $form,
+ save_validity => 1);
# commit
my $rc = $dbh->commit;
- $dbh->disconnect;
$main::lxdebug->leave_sub();
my %columns = ( "assembly" => "id", "parts" => "id" );
- for my $table (qw(prices partstax makemodel inventory assembly license translation parts)) {
+ for my $table (qw(prices partstax makemodel inventory assembly translation parts)) {
my $column = defined($columns{$table}) ? $columns{$table} : "parts_id";
do_query($form, $dbh, qq|DELETE FROM $table WHERE $column = ?|, @values);
}
push(@values, conv_i($form->{id}));
}
+ # Search for part ID overrides all other criteria.
+ if ($form->{"id_${i}"}) {
+ $where = qq|p.id = ?|;
+ @values = ($form->{"id_${i}"});
+ }
+
if ($form->{partnumber}) {
$where .= qq| ORDER BY p.partnumber|;
} else {
$form->{soldtotal} = undef if $form->{l_soldtotal}; # security fix. top100 insists on putting strings in there...
my @simple_filters = qw(partnumber ean description partsgroup microfiche drawing onhand);
+ my @project_filters = qw(projectnumber projectdescription);
my @makemodel_filters = qw(make model);
my @invoice_oi_filters = qw(serialnumber soldtotal);
my @apoe_filters = qw(transdate);
- my @like_filters = (@simple_filters, @makemodel_filters, @invoice_oi_filters);
- my @all_columns = (@simple_filters, @makemodel_filters, @apoe_filters, qw(serialnumber));
- my @simple_l_switches = (@all_columns, qw(listprice sellprice lastcost priceupdate weight unit bin rop image));
+ my @like_filters = (@simple_filters, @invoice_oi_filters);
+ my @all_columns = (@simple_filters, @makemodel_filters, @apoe_filters, @project_filters, qw(serialnumber));
+ my @simple_l_switches = (@all_columns, qw(notes listprice sellprice lastcost priceupdate weight unit bin rop image));
my @oe_flags = qw(bought sold onorder ordered rfq quoted);
my @qsooqr_flags = qw(invnumber ordnumber quonumber trans_id name module qty);
my @deliverydate_flags = qw(deliverydate);
pfac => 'LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id)',
invoice_oi =>
q|LEFT JOIN (
- SELECT parts_id, description, serialnumber, trans_id, unit, sellprice, qty, assemblyitem, deliverydate, 'invoice' AS ioi, id FROM invoice UNION
- SELECT parts_id, description, serialnumber, trans_id, unit, sellprice, qty, FALSE AS assemblyitem, NULL AS deliverydate, 'orderitems' AS ioi, id FROM orderitems
+ SELECT parts_id, description, serialnumber, trans_id, unit, sellprice, qty, assemblyitem, deliverydate, 'invoice' AS ioi, project_id, id FROM invoice UNION
+ SELECT parts_id, description, serialnumber, trans_id, unit, sellprice, qty, FALSE AS assemblyitem, NULL AS deliverydate, 'orderitems' AS ioi, project_id, id FROM orderitems
) AS ioi ON ioi.parts_id = p.id|,
apoe =>
q|LEFT JOIN (
- SELECT id, transdate, 'ir' AS module, ordnumber, quonumber, invnumber, FALSE AS quotation, NULL AS customer_id, vendor_id, NULL AS deliverydate, 'invoice' AS ioi FROM ap UNION
- SELECT id, transdate, 'is' AS module, ordnumber, quonumber, invnumber, FALSE AS quotation, customer_id, NULL AS vendor_id, deliverydate, 'invoice' AS ioi FROM ar UNION
- SELECT id, transdate, 'oe' AS module, ordnumber, quonumber, NULL AS invnumber, quotation, customer_id, vendor_id, reqdate AS deliverydate, 'orderitems' AS ioi FROM oe
+ SELECT id, transdate, 'ir' AS module, ordnumber, quonumber, invnumber, FALSE AS quotation, NULL AS customer_id, vendor_id, NULL AS deliverydate, globalproject_id, 'invoice' AS ioi FROM ap UNION
+ SELECT id, transdate, 'is' AS module, ordnumber, quonumber, invnumber, FALSE AS quotation, customer_id, NULL AS vendor_id, deliverydate, globalproject_id, 'invoice' AS ioi FROM ar UNION
+ SELECT id, transdate, 'oe' AS module, ordnumber, quonumber, NULL AS invnumber, quotation, customer_id, vendor_id, reqdate AS deliverydate, globalproject_id, 'orderitems' AS ioi FROM oe
) AS apoe ON ((ioi.trans_id = apoe.id) AND (ioi.ioi = apoe.ioi))|,
cv =>
q|LEFT JOIN (
SELECT id, name, 'customer' AS cv FROM customer UNION
SELECT id, name, 'vendor' AS cv FROM vendor
) AS cv ON cv.id = apoe.customer_id OR cv.id = apoe.vendor_id|,
+ mv => 'LEFT JOIN vendor AS mv ON mv.id = mm.make',
+ project => 'LEFT JOIN project AS pj ON pj.id = COALESCE(ioi.project_id, apoe.globalproject_id)',
);
- my @join_order = qw(partsgroup makemodel invoice_oi apoe cv pfac);
+ my @join_order = qw(partsgroup makemodel mv invoice_oi apoe cv pfac project);
my %table_prefix = (
deliverydate => 'apoe.', serialnumber => 'ioi.',
ordnumber => 'apoe.', make => 'mm.',
quonumber => 'apoe.', model => 'mm.',
invnumber => 'apoe.', partsgroup => 'pg.',
- lastcost => ' ', , soldtotal => ' ',
- factor => 'pfac.',
- 'SUM(ioi.qty)' => ' ',
+ lastcost => 'p.', , soldtotal => ' ',
+ factor => 'pfac.', projectnumber => 'pj.',
+ 'SUM(ioi.qty)' => ' ', projectdescription => 'pj.',
description => 'p.',
qty => 'ioi.',
serialnumber => 'ioi.',
# of the scecified table will gently override (coalesce actually) the original value
# use it to conditionally coalesce values from subtables
my @column_override = (
- # column name, prefix, joins_needed
+ # column name, prefix, joins_needed, nick name (in case column is named like another)
[ 'description', 'ioi.', 'invoice_oi' ],
[ 'deliverydate', 'ioi.', 'invoice_oi' ],
[ 'transdate', 'apoe.', 'apoe' ],
'SUM(ioi.qty)' => 'soldtotal',
'ioi.id' => 'ioi_id',
'ioi.ioi' => 'ioi',
+ 'projectdescription' => 'projectdescription',
+ );
+
+ my %real_column = (
+ projectdescription => 'description',
);
if (($form->{searchitems} eq 'assembly') && $form->{l_lastcost}) {
my $make_token_builder = sub {
my $joins_needed = shift;
sub {
- my ($col, $alias) = @_;
+ my ($nick, $alias) = @_;
+ my ($col) = $real_column{$nick} || $nick;
my @coalesce_tokens =
map { ($_->[1] || 'p.') . $_->[0] }
grep { !$_->[2] || $joins_needed->{$_->[2]} }
- grep { $_->[0] eq $col }
- @column_override, [ $col, $table_prefix{$col} ];
+ grep { ($_->[3] || $_->[0]) eq $nick }
+ @column_override, [ $col, $table_prefix{$nick}, undef , $nick ];
my $coalesce = scalar @coalesce_tokens > 1;
return ($coalesce
? sprintf 'COALESCE(%s)', join ', ', @coalesce_tokens
: shift @coalesce_tokens)
- . ($alias && ($coalesce || $renamed_columns{$col})
- ? " AS " . ($renamed_columns{$col} || $col)
+ . ($alias && ($coalesce || $renamed_columns{$nick})
+ ? " AS " . ($renamed_columns{$nick} || $nick)
: '');
}
};
}
}
+ if ($form->{"partsgroup_id"}) {
+ $form->{"l_partsgroup"} = '1'; # show the column
+ push @where_tokens, "pg.id = ?";
+ push @bind_vars, $form->{"partsgroup_id"};
+ }
+
foreach (@like_filters) {
next unless $form->{$_};
$form->{"l_$_"} = '1'; # show the column
WHERE (a_lc.id = p.id)) AS lastcost|;
$table_prefix{$q_assembly_lastcost} = ' ';
+ # special case makemodel search
+ # all_parts is based upon the assumption that every parameter is named like the column it represents
+ # unfortunately make would have to match vendor.name which is already taken for vendor.name in bsooqr mode.
+ # fortunately makemodel doesn't need to be displayed later, so adding a special clause to where_token is sufficient.
+ if ($form->{make}) {
+ push @where_tokens, 'mv.name ILIKE ?';
+ push @bind_vars, "%$form->{make}%";
+ }
+ if ($form->{model}) {
+ push @where_tokens, 'mm.model ILIKE ?';
+ push @bind_vars, "%$form->{model}%";
+ }
+
# special case: sorting by partnumber
# since partnumbers are expected to be prefixed integers, a special sorting is implemented sorting first lexically by prefix and then by suffix.
# and yes, that expression is designed to hold that array of regexes only once, so the map is kinda messy, sorry about that.
$joins_needed{partsgroup} = 1;
$joins_needed{pfac} = 1;
+ $joins_needed{project} = 1 if grep { $form->{$_} || $form->{"l_$_"} } @project_filters;
$joins_needed{makemodel} = 1 if grep { $form->{$_} || $form->{"l_$_"} } @makemodel_filters;
+ $joins_needed{mv} = 1 if $joins_needed{makemodel};
$joins_needed{cv} = 1 if $bsooqr;
$joins_needed{apoe} = 1 if $joins_needed{cv} || grep { $form->{$_} || $form->{"l_$_"} } @apoe_filters;
- $joins_needed{invoice_oi} = 1 if $joins_needed{apoe} || grep { $form->{$_} || $form->{"l_$_"} } @invoice_oi_filters;
-
- # in bsoorq, use qtys instead of onhand
- if ($joins_needed{invoice_oi}) {
- $renamed_columns{onhand} = 'onhand_before_bsooqr';
- $renamed_columns{qty} = 'onhand';
- }
+ $joins_needed{invoice_oi} = 1 if $joins_needed{project} || $joins_needed{apoe} || grep { $form->{$_} || $form->{"l_$_"} } @invoice_oi_filters;
# special case for description search.
# up in the simple filter section the description filter got interpreted as something like: WHERE description ILIKE '%$form->{description}%'
my $select_clause = join ', ', map { $token_builder->($_, 1) } @select_tokens;
my $join_clause = join ' ', @joins{ grep $joins_needed{$_}, @join_order };
my $where_clause = join ' AND ', map { "($_)" } @where_tokens;
- my $group_clause = ' GROUP BY ' . join ', ', map { $token_builder->($_) } @group_tokens if scalar @group_tokens;
+ my $group_clause = @group_tokens ? ' GROUP BY ' . join ', ', map { $token_builder->($_) } @group_tokens : '';
my %oe_flag_to_cvar = (
bought => 'invoice',
map { $_->{onhand} *= 1 } @{ $form->{parts} };
+ # fix qty sign in ap. those are saved negative
+ if ($bsooqr && $form->{bought}) {
+ for my $row (@{ $form->{parts} }) {
+ $row->{qty} *= -1 if $row->{module} eq 'ir';
+ }
+ }
+
# post processing for assembly parts lists (bom)
# for each part get the assembly parts and add them into the partlist.
my @assemblies;
if ($form->{searchitems} eq 'assembly' && $form->{bom}) {
$query =
qq|SELECT p.id, p.partnumber, p.description, a.qty AS onhand,
- p.unit, p.bin,
+ p.unit, p.bin, p.notes,
p.sellprice, p.listprice, p.lastcost,
p.rop, p.weight, p.priceupdate,
p.image, p.drawing, p.microfiche,
$form->{parts} = \@assemblies;
}
+ if ($form->{l_pricegroups} ) {
+ my $query = <<SQL;
+ SELECT parts_id, price, pricegroup_id
+ FROM prices
+ WHERE parts_id = ?
+SQL
+
+ my $sth = prepare_query($form, $dbh, $query);
+
+ foreach my $part (@{ $form->{parts} }) {
+ do_statement($form, $sth, $query, conv_i($part->{id}));
+
+ while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
+ $part->{"pricegroup_$ref->{pricegroup_id}"} = $ref->{price};
+ }
+ $sth->finish;
+ }
+ };
+
+
$main::lxdebug->leave_sub();
return wantarray ? @{ $form->{parts} } : $form->{parts};
my ($query, $sth);
- $query =
- qq|SELECT c.new_chart_id, date($transdate) >= c.valid_from AS is_valid, | .
- qq| cnew.accno | .
- qq|FROM chart c | .
- qq|LEFT JOIN chart cnew ON c.new_chart_id = cnew.id | .
- qq|WHERE (c.id = ?) AND NOT c.new_chart_id IS NULL AND (c.new_chart_id > 0)|;
- $sth = prepare_query($form, $dbh, $query);
+ $form->{ACCOUNT_CHAIN_BY_ID} ||= {
+ map { $_->{id} => $_ }
+ selectall_hashref_query($form, $dbh, <<SQL, $transdate) };
+ SELECT c.id, c.new_chart_id, date(?) >= c.valid_from AS is_valid, cnew.accno
+ FROM chart c
+ LEFT JOIN chart cnew ON c.new_chart_id = cnew.id
+ WHERE NOT c.new_chart_id IS NULL AND (c.new_chart_id > 0)
+SQL
while (1) {
- do_statement($form, $sth, $query, $accno_id);
- my $ref = $sth->fetchrow_hashref();
+ my $ref = $form->{ACCOUNT_CHAIN_BY_ID}->{$accno_id};
last unless ($ref && $ref->{"is_valid"} &&
!grep({ $_ == $ref->{"new_chart_id"} } @visited_accno_ids));
$accno_id = $ref->{"new_chart_id"};
}
sub retrieve_accounts {
- $main::lxdebug->enter_sub(2);
+ $main::lxdebug->enter_sub;
- my ($self, $myconfig, $form, $parts_id, $index) = @_;
-
- my ($query, $sth, $dbh);
+ my $self = shift;
+ my $myconfig = shift;
+ my $form = shift;
+ my $dbh = $form->get_standard_dbh;
+ my %args = @_; # index => part_id
- $form->{"taxzone_id"} *= 1;
+ $form->{taxzone_id} *= 1;
- $dbh = $form->get_standard_dbh($myconfig);
+ return unless grep $_, values %args; # shortfuse if no part_id supplied
+ # transdate madness.
my $transdate = "";
if ($form->{type} eq "invoice") {
if (($form->{vc} eq "vendor") || !$form->{deliverydate}) {
}
if ($transdate eq "") {
- $transdate = "current_date";
+ $transdate = DateTime->today_local->to_lxoffice;
} else {
$transdate = $dbh->quote($transdate);
}
+ #/transdate
+ my $inc_exp = $form->{"vc"} eq "customer" ? "income_accno_id" : "expense_accno_id";
+
+ my @part_ids = grep { $_ } values %args;
+ my $in = join ',', ('?') x @part_ids;
+
+ my %accno_by_part = map { $_->{id} => $_ }
+ selectall_hashref_query($form, $dbh, <<SQL, @part_ids);
+ SELECT
+ p.id, p.inventory_accno_id AS is_part,
+ bg.inventory_accno_id,
+ bg.income_accno_id_$form->{taxzone_id} AS income_accno_id,
+ bg.expense_accno_id_$form->{taxzone_id} AS expense_accno_id,
+ c1.accno AS inventory_accno,
+ c2.accno AS income_accno,
+ c3.accno AS expense_accno
+ FROM parts p
+ LEFT JOIN buchungsgruppen bg ON p.buchungsgruppen_id = bg.id
+ LEFT JOIN chart c1 ON bg.inventory_accno_id = c1.id
+ LEFT JOIN chart c2 ON bg.income_accno_id_$form->{taxzone_id} = c2.id
+ LEFT JOIN chart c3 ON bg.expense_accno_id_$form->{taxzone_id} = c3.id
+ WHERE p.id IN ($in)
+SQL
+
+ my $sth_tax = prepare_query($::form, $dbh, <<SQL);
+ SELECT c.accno, t.taxdescription AS description, t.rate, t.taxnumber
+ FROM tax t
+ LEFT JOIN chart c ON c.id = t.chart_id
+ WHERE t.id IN
+ (SELECT tk.tax_id
+ FROM taxkeys tk
+ WHERE tk.chart_id = ? AND startdate <= ?
+ ORDER BY startdate DESC LIMIT 1)
+SQL
+
+ while (my ($index => $part_id) = each %args) {
+ my $ref = $accno_by_part{$part_id} or next;
+
+ $ref->{"inventory_accno_id"} = undef unless $ref->{"is_part"};
+
+ my %accounts;
+ for my $type (qw(inventory income expense)) {
+ next unless $ref->{"${type}_accno_id"};
+ ($accounts{"${type}_accno_id"}, $accounts{"${type}_accno"}) =
+ $self->follow_account_chain($form, $dbh, $transdate, $ref->{"${type}_accno_id"}, $ref->{"${type}_accno"});
+ }
- $query =
- qq|SELECT | .
- qq| p.inventory_accno_id AS is_part, | .
- qq| bg.inventory_accno_id, | .
- qq| bg.income_accno_id_$form->{taxzone_id} AS income_accno_id, | .
- qq| bg.expense_accno_id_$form->{taxzone_id} AS expense_accno_id, | .
- qq| c1.accno AS inventory_accno, | .
- qq| c2.accno AS income_accno, | .
- qq| c3.accno AS expense_accno | .
- qq|FROM parts p | .
- qq|LEFT JOIN buchungsgruppen bg ON p.buchungsgruppen_id = bg.id | .
- qq|LEFT JOIN chart c1 ON bg.inventory_accno_id = c1.id | .
- qq|LEFT JOIN chart c2 ON bg.income_accno_id_$form->{taxzone_id} = c2.id | .
- qq|LEFT JOIN chart c3 ON bg.expense_accno_id_$form->{taxzone_id} = c3.id | .
- qq|WHERE p.id = ?|;
- my $ref = selectfirst_hashref_query($form, $dbh, $query, $parts_id);
-
- return $main::lxdebug->leave_sub(2) if (!$ref);
-
- $ref->{"inventory_accno_id"} = undef unless ($ref->{"is_part"});
-
- my %accounts;
- foreach my $type (qw(inventory income expense)) {
- next unless ($ref->{"${type}_accno_id"});
- ($accounts{"${type}_accno_id"}, $accounts{"${type}_accno"}) =
- $self->follow_account_chain($form, $dbh, $transdate,
- $ref->{"${type}_accno_id"},
- $ref->{"${type}_accno"});
- }
-
- map({ $form->{"${_}_accno_$index"} = $accounts{"${_}_accno"} }
- qw(inventory income expense));
+ $form->{"${_}_accno_$index"} = $accounts{"${_}_accno"} for qw(inventory income expense);
- my $inc_exp = $form->{"vc"} eq "customer" ? "income" : "expense";
- my $accno_id = $accounts{"${inc_exp}_accno_id"};
+ $sth_tax->execute($accounts{$inc_exp}, quote_db_date($transdate));
+ $ref = $sth_tax->fetchrow_hashref or next;
- $query =
- qq|SELECT c.accno, t.taxdescription AS description, t.rate, t.taxnumber | .
- qq|FROM tax t | .
- qq|LEFT JOIN chart c ON c.id = t.chart_id | .
- qq|WHERE t.id IN | .
- qq| (SELECT tk.tax_id | .
- qq| FROM taxkeys tk | .
- qq| WHERE tk.chart_id = ? AND startdate <= | . quote_db_date($transdate) .
- qq| ORDER BY startdate DESC LIMIT 1) |;
- $ref = selectfirst_hashref_query($form, $dbh, $query, $accno_id);
-
- unless ($ref) {
- $main::lxdebug->leave_sub(2);
- return;
- }
+ $form->{"taxaccounts_$index"} = $ref->{"accno"};
+ $form->{"taxaccounts"} .= "$ref->{accno} "if $form->{"taxaccounts"} !~ /$ref->{accno}/;
- $form->{"taxaccounts_$index"} = $ref->{"accno"};
- if ($form->{"taxaccounts"} !~ /$ref->{accno}/) {
- $form->{"taxaccounts"} .= "$ref->{accno} ";
+ $form->{"$ref->{accno}_${_}"} = $ref->{$_} for qw(rate description taxnumber);
}
- map({ $form->{"$ref->{accno}_${_}"} = $ref->{$_}; }
- qw(rate description taxnumber));
-# $main::lxdebug->message(0, "formvars: rate " . $form->{"$ref->{accno}_rate"} .
-# " description " . $form->{"$ref->{accno}_description"} .
-# " taxnumber " . $form->{"$ref->{accno}_taxnumber"} .
-# " || taxaccounts_$index " . $form->{"taxaccounts_$index"} .
-# " || taxaccounts " . $form->{"taxaccounts"});
+ $sth_tax->finish;
- $main::lxdebug->leave_sub(2);
+ $::lxdebug->leave_sub;
}
sub get_basic_part_info {
}
my $placeholders = join ', ', ('?') x scalar(@part_ids);
- my $query = qq|SELECT mm.parts_id, mm.model, v.name AS make
+ my $query = qq|SELECT mm.parts_id, mm.model, mm.lastcost, v.name AS make
FROM makemodel mm
- LEFT JOIN vendor v ON (mm.make = cast (v.id as text))
+ LEFT JOIN vendor v ON (mm.make = v.id)
WHERE mm.parts_id IN ($placeholders)|;
my %makemodel = ();