summaryrefslogtreecommitdiff
path: root/FS
diff options
context:
space:
mode:
Diffstat (limited to 'FS')
-rw-r--r--FS/FS/AccessRight.pm2
-rw-r--r--FS/FS/Cron/notify.pm72
-rw-r--r--FS/FS/Misc/prune.pm16
-rw-r--r--FS/FS/Report/FCC_477.pm12
-rw-r--r--FS/FS/Template_Mixin.pm24
-rw-r--r--FS/FS/TicketSystem/RT_External.pm11
-rw-r--r--FS/FS/access_right.pm1
-rw-r--r--FS/FS/cust_main/Billing_Discount.pm10
-rw-r--r--FS/FS/cust_pkg/Search.pm16
-rw-r--r--FS/FS/part_event/Condition/once_percust.pm14
-rw-r--r--FS/FS/part_export/send_email.pm12
-rw-r--r--FS/FS/tax_rate.pm13
12 files changed, 105 insertions, 98 deletions
diff --git a/FS/FS/AccessRight.pm b/FS/FS/AccessRight.pm
index 92cede6a5..4a1f89aa0 100644
--- a/FS/FS/AccessRight.pm
+++ b/FS/FS/AccessRight.pm
@@ -217,6 +217,7 @@ tie my %rights, 'Tie::IxHash',
###
'Customer credit and refund rights' => [
'Post credit',
+ { rightname=>'Backdate credit', desc=>'Enable credits to be posted for days other than today.' },
'Credit line items', #NEWNEWNEW
'Apply credit', #NEWNEW
{ rightname=>'Unapply credit', desc=>'Enable "unapplication" of unclosed credits.' }, #aka unapplycredits
@@ -444,6 +445,7 @@ sub default_superuser_rights {
'Credit card void',
'Echeck void',
'Void invoices',#people are overusing this when credits are more appropriate
+ 'Backdate credit',
);
no warnings 'uninitialized';
diff --git a/FS/FS/Cron/notify.pm b/FS/FS/Cron/notify.pm
index 1859fcaf7..6d7065429 100644
--- a/FS/FS/Cron/notify.pm
+++ b/FS/FS/Cron/notify.pm
@@ -28,52 +28,52 @@ sub notify_flat_delay {
# select * from cust_pkg where
my $where_pkg = <<"END";
- where ( cancel is null or cancel = 0 )
- and ( bill > 0 )
- and
- 0 < ( select count(*) from part_pkg
- where cust_pkg.pkgpart = part_pkg.pkgpart
- and part_pkg.plan = 'flat_delayed'
- and 0 < ( select count(*) from part_pkg_option
- where part_pkg.pkgpart = part_pkg_option.pkgpart
- and part_pkg_option.optionname = 'recur_notify'
- and CAST( part_pkg_option.optionvalue AS $integer ) > 0
- and 0 <= ( $time
- + CAST( part_pkg_option.optionvalue AS $integer )
- * 86400
- - cust_pkg.bill
- )
- and ( cust_pkg.expire is null
- or cust_pkg.expire > ( $time
- + CAST( part_pkg_option.optionvalue AS $integer )
- * 86400
- )
+ WHERE ( cancel IS NULL OR cancel = 0 )
+ AND ( bill > 0 )
+ AND EXISTS (
+ SELECT 1 FROM part_pkg
+ WHERE cust_pkg.pkgpart = part_pkg.pkgpart
+ AND part_pkg.plan = 'flat_delayed'
+ AND EXISTS ( SELECT 1 from part_pkg_option
+ WHERE part_pkg.pkgpart = part_pkg_option.pkgpart
+ AND part_pkg_option.optionname = 'recur_notify'
+ AND CAST( part_pkg_option.optionvalue AS $integer ) > 0
+ AND 0 <= ( $time
+ + CAST( part_pkg_option.optionvalue AS $integer )
+ * 86400
+ - cust_pkg.bill
+ )
+ AND ( cust_pkg.expire is null
+ OR cust_pkg.expire > ( $time
+ + CAST( part_pkg_option.optionvalue AS $integer )
+ * 86400
+ )
END
-#/* and ( cust_pkg.adjourn is null
-# or cust_pkg.adjourn > $time
+#/* and ( cust_pkg.adjourn is null
+# or cust_pkg.adjourn > $time
#-- Should notify suspended ones + cast(part_pkg_option.optionvalue as $integer)
-# * 86400
+# * 86400
#*/
$where_pkg .= <<"END";
- )
- )
- )
- and
- 0 = ( select count(*) from cust_pkg_option
- where cust_pkg.pkgnum = cust_pkg_option.pkgnum
- and cust_pkg_option.optionname = 'impending_recur_notification_sent'
- and CAST( cust_pkg_option.optionvalue AS $integer ) = 1
- )
+ )
+ )
+ )
+ AND NOT EXISTS (
+ SELECT 1 from cust_pkg_option
+ WHERE cust_pkg.pkgnum = cust_pkg_option.pkgnum
+ AND cust_pkg_option.optionname = 'impending_recur_notification_sent'
+ AND CAST( cust_pkg_option.optionvalue AS $integer ) = 1
+ )
END
if ($opt{a}) {
$where_pkg .= <<END;
- and 0 < ( select count(*) from cust_main
- where cust_pkg.custnum = cust_main.custnum
- and cust_main.agentnum IN ( $opt{a} )
- )
+ AND EXISTS ( SELECT 1 from cust_main
+ WHERE cust_pkg.custnum = cust_main.custnum
+ AND cust_main.agentnum IN ( $opt{a} )
+ )
END
}
diff --git a/FS/FS/Misc/prune.pm b/FS/FS/Misc/prune.pm
index 3f0c79d00..ce7240578 100644
--- a/FS/FS/Misc/prune.pm
+++ b/FS/FS/Misc/prune.pm
@@ -44,34 +44,34 @@ sub prune_applications {
my $ccr = <<EOW;
WHERE
- 0 = (select count(*) from cust_credit
+ NOT EXISTS (select 1 from cust_credit
where cust_credit_refund.crednum = cust_credit.crednum)
or
- 0 = (select count(*) from cust_refund
+ NOT EXISTS (select 1 from cust_refund
where cust_credit_refund.refundnum = cust_refund.refundnum)
EOW
my $ccb = <<EOW;
WHERE
- 0 = (select count(*) from cust_credit
+ NOT EXISTS (select 1 from cust_credit
where cust_credit_bill.crednum = cust_credit.crednum)
or
- 0 = (select count(*) from cust_bill
+ NOT EXISTS (select 1 from cust_bill
where cust_credit_bill.invnum = cust_bill.invnum)
EOW
my $cbp = <<EOW;
WHERE
- 0 = (select count(*) from cust_bill
+ NOT EXISTS (select 1 from cust_bill
where cust_bill_pay.invnum = cust_bill.invnum)
or
- 0 = (select count(*) from cust_pay
+ NOT EXISTS (select 1 from cust_pay
where cust_bill_pay.paynum = cust_pay.paynum)
EOW
my $cpr = <<EOW;
WHERE
- 0 = (select count(*) from cust_pay
+ NOT EXISTS (select 1 from cust_pay
where cust_pay_refund.paynum = cust_pay.paynum)
or
- 0 = (select count(*) from cust_refund
+ NOT EXISTS (select 1 from cust_refund
where cust_pay_refund.refundnum = cust_refund.refundnum)
EOW
diff --git a/FS/FS/Report/FCC_477.pm b/FS/FS/Report/FCC_477.pm
index f5d6a06ec..20d402d7d 100644
--- a/FS/FS/Report/FCC_477.pm
+++ b/FS/FS/Report/FCC_477.pm
@@ -355,8 +355,10 @@ sub fbs_sql {
my $agentnum = $opt{agentnum};
my $q = $opt{ignore_quantity} ? '1' : 'COALESCE(cust_pkg.quantity, 1)';
+ my $censustract = "replace(cust_location.censustract, '.', '')";
+
my @select = (
- 'cust_location.censustract',
+ "$censustract AS censustract",
'technology',
'broadband_downstream',
'broadband_upstream',
@@ -381,8 +383,7 @@ sub fbs_sql {
is_fixed_broadband()
);
push @where, "cust_main.agentnum = $agentnum" if $agentnum;
- my $group_by = 'cust_location.censustract, technology, '.
- 'broadband_downstream, broadband_upstream ';
+ my $group_by = "$censustract, technology, broadband_downstream, broadband_upstream ";
my $order_by = $group_by;
"SELECT ".join(', ', @select) . "
@@ -400,9 +401,10 @@ sub fvs_sql {
my $date = $opt{date} || time;
my $agentnum = $opt{agentnum};
my $q = $opt{ignore_quantity} ? '1' : 'COALESCE(cust_pkg.quantity, 1)';
+ my $censustract = "replace(cust_location.censustract, '.', '')";
my @select = (
- 'cust_location.censustract',
+ "$censustract AS censustract",
# VoIP indicator (0 for non-VoIP, 1 for VoIP)
'COALESCE(is_voip, 0)',
# number of lines/subscriptions
@@ -426,7 +428,7 @@ sub fvs_sql {
"(is_voip = 1 OR is_phone = 1)",
);
push @where, "cust_main.agentnum = $agentnum" if $agentnum;
- my $group_by = 'cust_location.censustract, COALESCE(is_voip, 0)';
+ my $group_by = "$censustract, COALESCE(is_voip, 0)";
my $order_by = $group_by;
"SELECT ".join(', ', @select) . "
diff --git a/FS/FS/Template_Mixin.pm b/FS/FS/Template_Mixin.pm
index 2fae4643d..b153d3114 100644
--- a/FS/FS/Template_Mixin.pm
+++ b/FS/FS/Template_Mixin.pm
@@ -703,10 +703,10 @@ sub print_generic {
# cares about application dates. We want to know the sum of all
# _top-level transactions_ dated before the last invoice.
my @sql = (
- 'SELECT SUM(charged) FROM cust_bill WHERE _date <= ? AND custnum = ?',
- 'SELECT -1*SUM(amount) FROM cust_credit WHERE _date <= ? AND custnum = ?',
- 'SELECT -1*SUM(paid) FROM cust_pay WHERE _date <= ? AND custnum = ?',
- 'SELECT SUM(refund) FROM cust_refund WHERE _date <= ? AND custnum = ?',
+ "SELECT COALESCE( SUM(charged), 0 ) FROM cust_bill",
+ "SELECT -1 * COALESCE( SUM(amount), 0 ) FROM cust_credit",
+ "SELECT -1 * COALESCE( SUM(paid), 0 ) FROM cust_pay",
+ "SELECT COALESCE( SUM(refund), 0 ) FROM cust_refund",
);
# the customer's current balance immediately after generating the last
@@ -714,13 +714,11 @@ sub print_generic {
my $last_bill_balance = $last_bill->charged;
foreach (@sql) {
- #warn "$_\n";
my $delta = FS::Record->scalar_sql(
- $_,
+ "$_ WHERE _date <= ? AND custnum = ?",
$last_bill->_date - 1,
$self->custnum,
);
- #warn "$delta\n";
$last_bill_balance += $delta;
}
@@ -2153,9 +2151,9 @@ sub _items_sections {
} else {
$section->{'category'} = $sectionname;
$section->{'description'} = &{ $escape }($sectionname);
- if ( _pkg_category($_) ) {
- $section->{'sort_weight'} = _pkg_category($_)->weight;
- if ( _pkg_category($_)->condense ) {
+ if ( _pkg_category($sectionname) ) {
+ $section->{'sort_weight'} = _pkg_category($sectionname)->weight;
+ if ( _pkg_category($sectionname)->condense ) {
$section = { %$section, $self->_condense_section($opt{format}) };
}
}
@@ -2729,7 +2727,7 @@ sub _items_cust_bill_pkg {
'pkgnum' => $cust_bill_pkg->pkgpart, #so it displays in Ref
'description' => $description,
'amount' => sprintf("%.2f", $cust_bill_pkg->setup),
- 'unit_amount' => sprintf("%.2f", $cust_bill_pkg->unitsetup),
+ 'unit_amount' => sprintf("%.2f", $cust_bill_pkg->unitsetup),
'quantity' => $cust_bill_pkg->quantity,
'preref_html' => ( $opt{preref_callback}
? &{ $opt{preref_callback} }( $cust_bill_pkg )
@@ -2742,9 +2740,9 @@ sub _items_cust_bill_pkg {
'pkgnum' => $cust_bill_pkg->pkgpart, #so it displays in Ref
'description' => "$desc (". $cust_bill_pkg->part_pkg->freq_pretty.")",
'amount' => sprintf("%.2f", $cust_bill_pkg->recur),
- 'unit_amount' => sprintf("%.2f", $cust_bill_pkg->unitrecur),
+ 'unit_amount' => sprintf("%.2f", $cust_bill_pkg->unitrecur),
'quantity' => $cust_bill_pkg->quantity,
- 'preref_html' => ( $opt{preref_callback}
+ 'preref_html' => ( $opt{preref_callback}
? &{ $opt{preref_callback} }( $cust_bill_pkg )
: ''
),
diff --git a/FS/FS/TicketSystem/RT_External.pm b/FS/FS/TicketSystem/RT_External.pm
index c2aac2db7..9f07732c7 100644
--- a/FS/FS/TicketSystem/RT_External.pm
+++ b/FS/FS/TicketSystem/RT_External.pm
@@ -175,12 +175,11 @@ sub _from_customer {
} else {
- $where =
- "AND 0 = ( SELECT COUNT(*) FROM ObjectCustomFieldValues
- WHERE ObjectId = Tickets.id
- AND ObjectType = 'RT::Ticket'
- AND $customfield_sql
- )
+ $where = " AND NOT EXISTS ( SELECT 1 FROM ObjectCustomFieldValues
+ WHERE ObjectId = Tickets.id
+ AND ObjectType = 'RT::Ticket'
+ AND $customfield_sql
+ )
";
}
diff --git a/FS/FS/access_right.pm b/FS/FS/access_right.pm
index ee0c494ae..e5a5781a9 100644
--- a/FS/FS/access_right.pm
+++ b/FS/FS/access_right.pm
@@ -249,6 +249,7 @@ sub _upgrade_data { # class method
'Edit package definition costs' => 'View package definition costs',
'List prospects' => 'List contacts',
'List customers' => 'List contacts',
+ 'Backdate payment' => 'Backdate credit',
);
# foreach my $old_acl ( keys %onetime ) {
diff --git a/FS/FS/cust_main/Billing_Discount.pm b/FS/FS/cust_main/Billing_Discount.pm
index 9dda389f6..d437740e3 100644
--- a/FS/FS/cust_main/Billing_Discount.pm
+++ b/FS/FS/cust_main/Billing_Discount.pm
@@ -42,11 +42,13 @@ sub _discount_pkgs_and_bill {
push @where, "part_pkg.freq = '1'";
push @where, "(cust_pkg.cancel IS NULL OR cust_pkg.cancel = 0)";
push @where, "(cust_pkg.susp IS NULL OR cust_pkg.susp = 0)";
- push @where, "0<(SELECT count(*) FROM part_pkg_discount
- WHERE part_pkg.pkgpart = part_pkg_discount.pkgpart)";
+ push @where, "EXISTS( SELECT 1 FROM part_pkg_discount
+ WHERE part_pkg.pkgpart = part_pkg_discount.pkgpart )";
push @where,
- "0=(SELECT count(*) FROM cust_bill_pkg_discount
- WHERE cust_bill_pkg.billpkgnum = cust_bill_pkg_discount.billpkgnum)";
+ "NOT EXISTS (
+ SELECT 1 FROM cust_bill_pkg_discount
+ WHERE cust_bill_pkg.billpkgnum = cust_bill_pkg_discount.billpkgnu:
+ )";
my $extra_sql = 'WHERE '. join(' AND ', @where);
diff --git a/FS/FS/cust_pkg/Search.pm b/FS/FS/cust_pkg/Search.pm
index 9cd1ff063..aacd387a6 100644
--- a/FS/FS/cust_pkg/Search.pm
+++ b/FS/FS/cust_pkg/Search.pm
@@ -312,10 +312,10 @@ sub search {
if (@report_option) {
# this will result in the empty set for the dangling comma case as it should
push @where,
- map{ "0 < ( SELECT count(*) FROM part_pkg_option
- WHERE part_pkg_option.pkgpart = part_pkg.pkgpart
- AND optionname = 'report_option_$_'
- AND optionvalue = '1' )"
+ map{ "EXISTS ( SELECT 1 FROM part_pkg_option
+ WHERE part_pkg_option.pkgpart = part_pkg.pkgpart
+ AND optionname = 'report_option_$_'
+ AND optionvalue = '1' )"
} @report_option;
}
@@ -331,10 +331,10 @@ sub search {
if (@report_option_any) {
# this will result in the empty set for the dangling comma case as it should
push @where, ' ( '. join(' OR ',
- map{ "0 < ( SELECT count(*) FROM part_pkg_option
- WHERE part_pkg_option.pkgpart = part_pkg.pkgpart
- AND optionname = 'report_option_$_'
- AND optionvalue = '1' )"
+ map{ "EXISTS ( SELECT 1 FROM part_pkg_option
+ WHERE part_pkg_option.pkgpart = part_pkg.pkgpart
+ AND optionname = 'report_option_$_'
+ AND optionvalue = '1' )"
} @report_option_any
). ' ) ';
}
diff --git a/FS/FS/part_event/Condition/once_percust.pm b/FS/FS/part_event/Condition/once_percust.pm
index 67767f91b..2773ca020 100644
--- a/FS/FS/part_event/Condition/once_percust.pm
+++ b/FS/FS/part_event/Condition/once_percust.pm
@@ -52,13 +52,13 @@ sub condition_sql {
my $pkey = $pkey{$table};
- "0 = ( SELECT COUNT(*) FROM cust_event
- WHERE cust_event.eventpart = part_event.eventpart
- AND cust_event.tablenum IN (
- SELECT $pkey FROM $table AS once_percust
- WHERE once_percust.custnum = cust_main.custnum )
- AND status != 'failed'
- )
+ "NOT EXISTS ( SELECT 1 FROM cust_event
+ WHERE cust_event.eventpart = part_event.eventpart
+ AND cust_event.tablenum IN (
+ SELECT $pkey FROM $table AS once_percust
+ WHERE once_percust.custnum = cust_main.custnum )
+ AND status != 'failed'
+ )
";
}
diff --git a/FS/FS/part_export/send_email.pm b/FS/FS/part_export/send_email.pm
index 3e5142260..537a562bb 100644
--- a/FS/FS/part_export/send_email.pm
+++ b/FS/FS/part_export/send_email.pm
@@ -17,13 +17,13 @@ my %template_select = (
$templates{$_[0]};
},
option_values => sub {
- %templates = (0 => '',
+ %templates = (
+ 0 => '',
map { $_->msgnum, $_->msgname }
- qsearch({ table => 'msg_template',
- hashref => { disabled => { 'op' => '!=',
- 'value' => 1 }},
- order_by => 'ORDER BY msgnum ASC'
- })
+ qsearch({ table => 'msg_template',
+ hashref => { disabled => '', },
+ order_by => 'ORDER BY msgnum ASC'
+ })
);
sort keys (%templates);
},
diff --git a/FS/FS/tax_rate.pm b/FS/FS/tax_rate.pm
index a6da3d111..ec3bb12b6 100644
--- a/FS/FS/tax_rate.pm
+++ b/FS/FS/tax_rate.pm
@@ -1366,11 +1366,14 @@ sub _remember_tax_products {
my ( $imported, $last, $min_sec ) = _progressbar_foo();
- my $extra_sql = "WHERE taxproductnum IS NOT NULL OR ".
- "0 < ( SELECT count(*) from part_pkg_option WHERE ".
- " part_pkg_option.pkgpart = part_pkg.pkgpart AND ".
- " optionname LIKE 'usage_taxproductnum_%' AND ".
- " optionvalue != '' )";
+ my $extra_sql = "
+ WHERE taxproductnum IS NOT NULL
+ OR EXISTS ( SELECT 1 from part_pkg_option
+ WHERE part_pkg_option.pkgpart = part_pkg.pkgpart
+ AND optionname LIKE 'usage_taxproductnum_%'
+ AND optionvalue != ''
+ )
+ ";
my @items = qsearch( { table => 'part_pkg',
select => 'DISTINCT pkgpart,taxproductnum',
hashref => {},
generated by cgit v1.2.1 (git 2.18.0) at 2025-10-17 12:40:30 +0000