Revision 1e251313
Von Moritz Bunkus vor etwa 17 Jahren hinzugefügt
| SL/IC.pm | ||
|---|---|---|
| 422 | 422 |
ean = ?, |
| 423 | 423 |
not_discountable = ?, |
| 424 | 424 |
microfiche = ?, |
| 425 |
partsgroup_id = ? |
|
| 425 |
partsgroup_id = ?, |
|
| 426 |
price_factor_id = ? |
|
| 426 | 427 |
WHERE id = ?|; |
| 427 | 428 |
@values = ($form->{partnumber},
|
| 428 | 429 |
$form->{description},
|
| ... | ... | |
| 451 | 452 |
$form->{not_discountable} ? 't' : 'f',
|
| 452 | 453 |
$form->{microfiche},
|
| 453 | 454 |
conv_i($partsgroup_id), |
| 455 |
conv_i($form->{price_factor_id}),
|
|
| 454 | 456 |
conv_i($form->{id})
|
| 455 | 457 |
); |
| 456 | 458 |
do_query($form, $dbh, $query, @values); |
| ... | ... | |
| 850 | 852 |
# my @inactive_flags = qw(l_subtotal short l_linetotal); |
| 851 | 853 |
|
| 852 | 854 |
my %joins = ( |
| 853 |
partsgroup => 'LEFT JOIN partsgroup pg ON p.partsgroup_id = pg.id', |
|
| 854 |
makemodel => 'LEFT JOIN makemodel mm ON mm.parts_id = p.id', |
|
| 855 |
partsgroup => 'LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)', |
|
| 856 |
makemodel => 'LEFT JOIN makemodel mm ON (mm.parts_id = p.id)', |
|
| 857 |
pfac => 'LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id)', |
|
| 855 | 858 |
invoice_oi => |
| 856 | 859 |
q|LEFT JOIN ( |
| 857 | 860 |
SELECT parts_id, description, serialnumber, trans_id, unit, sellprice, qty, assemblyitem, 'invoice' AS ioi FROM invoice UNION |
| ... | ... | |
| 869 | 872 |
SELECT id, name, 'vendor' AS cv FROM vendor |
| 870 | 873 |
) AS cv ON cv.id = apoe.customer_id OR cv.id = apoe.vendor_id|, |
| 871 | 874 |
); |
| 872 |
my @join_order = qw(partsgroup makemodel invoice_oi apoe cv); |
|
| 873 |
my %joins_needed = (0) x scalar keys %joins;
|
|
| 875 |
my @join_order = qw(partsgroup makemodel invoice_oi apoe cv pfac);
|
|
| 876 |
my %joins_needed; |
|
| 874 | 877 |
|
| 875 | 878 |
#===== switches and simple filters ========# |
| 876 | 879 |
|
| 877 |
my @select_tokens = qw(id); |
|
| 880 |
my @select_tokens = qw(id factor);
|
|
| 878 | 881 |
my @where_tokens = qw(1=1); |
| 879 | 882 |
my @group_tokens = (); |
| 880 | 883 |
|
| ... | ... | |
| 945 | 948 |
push @where_tokens, join ' OR ', map { "($_)" } @bsooqr_tokens if $bsooqr;
|
| 946 | 949 |
|
| 947 | 950 |
$joins_needed{partsgroup} = 1;
|
| 951 |
$joins_needed{pfac} = 1;
|
|
| 948 | 952 |
$joins_needed{makemodel} = 1 if grep { $form->{$_} || $form->{"l_$_"} } @makemodel_filters;
|
| 949 | 953 |
$joins_needed{cv} = 1 if $bsooqr;
|
| 950 | 954 |
$joins_needed{apoe} = 1 if $joins_needed{cv} || grep { $form->{$_} || $form->{"l_$_"} } @apoe_filters;
|
| ... | ... | |
| 967 | 971 |
if ($form->{l_soldtotal}) {
|
| 968 | 972 |
push @where_tokens, 'ioi.qty >= 0'; |
| 969 | 973 |
push @group_tokens, @select_tokens; |
| 970 |
push @select_tokens, 'SUM(ioi.qty) AS soldtotal';
|
|
| 974 |
push @select_tokens, 'SUM(ioi.qty)'; |
|
| 971 | 975 |
} |
| 972 | 976 |
|
| 973 | 977 |
#============= build query ================# |
| ... | ... | |
| 979 | 983 |
ordnumber => 'apoe.', make => 'mm.', |
| 980 | 984 |
quonumber => 'apoe.', model => 'mm.', |
| 981 | 985 |
invnumber => 'apoe.', partsgroup => 'pg.', |
| 982 |
'SUM(ioi.qty) AS soldtotal' => ' ', |
|
| 986 |
factor => 'pfac.', |
|
| 987 |
'SUM(ioi.qty)' => ' ', |
|
| 988 |
); |
|
| 989 |
|
|
| 990 |
my %renamed_columns = ( |
|
| 991 |
'factor' => 'price_factor', |
|
| 992 |
'SUM(ioi.qty)' => 'soldtotal', |
|
| 983 | 993 |
); |
| 984 | 994 |
|
| 985 | 995 |
map { $table_prefix{$_} = 'ioi.' } qw(description serialnumber qty unit) if $joins_needed{invoice_oi};
|
| 996 |
map { $renamed_columns{$_} = ' AS ' . $renamed_columns{$_} } keys %renamed_columns;
|
|
| 986 | 997 |
|
| 987 |
my $select_clause = join ', ', map { ($table_prefix{$_} || "p.") . $_ } @select_tokens;
|
|
| 998 |
my $select_clause = join ', ', map { ($table_prefix{$_} || "p.") . $_ . $renamed_columns{$_} } @select_tokens;
|
|
| 988 | 999 |
my $join_clause = join ' ', @joins{ grep $joins_needed{$_}, @join_order };
|
| 989 | 1000 |
my $where_clause = join ' AND ', map { "($_)" } @where_tokens;
|
| 990 | 1001 |
my $group_clause = ' GROUP BY ' . join ', ', map { ($table_prefix{$_} || "p.") . $_ } @group_tokens if scalar @group_tokens;
|
| 991 | 1002 |
|
| 992 | 1003 |
my $query = qq|SELECT DISTINCT $select_clause FROM parts p $join_clause WHERE $where_clause $group_clause $order_clause $limit_clause|; |
| 1004 |
|
|
| 993 | 1005 |
$form->{parts} = selectall_hashref_query($form, $dbh, $query, @bind_vars);
|
| 994 | 1006 |
|
| 995 | 1007 |
## my $where = qq|1 = 1|; |
Auch abrufbar als: Unified diff
Preisfatkoren implementiert.