# 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
}
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
} 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}) };
}
}
} 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
+ )
";
}
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);
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;
}
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
). ' ) ';
}
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'
+ )
";
}
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 => {},
#XXX Active tickets not assigned to a customer
-tie my %report_prospects, 'Tie::IxHash',
- 'List prospects' => [ $fsurl. 'search/prospect_main.html', '' ],
- 'Advanced prospect reports' => [ $fsurl. 'search/report_prospect_main.html', '' ],
-;
+tie my %report_prospects, 'Tie::IxHash';
+if ( $curuser->access_right('List prospects') ) {
+ $report_prospects{'List prospects'} = [ $fsurl. 'search/prospect_main.html', '' ];
+ $report_prospects{'Advanced prospect reports'} = [ $fsurl. 'search/report_prospect_main.html', '' ];
+}
+$report_prospects{'separator'} = ''
+ if $curuser->access_right('List prospects')
+ && $curuser->access_right('List contacts');
+$report_prospects{'Prospect contacts'} = [ $fsurl. 'search/report_contact.html?link=prospect_main', '' ]
+ if $curuser->access_right('List contacts');
tie my %report_quotations, 'Tie::IxHash',
'List quotations' => [ $fsurl. 'search/quotation.html', '' ],
$report_customers{'Signup date report'} = [ $fsurl. 'graph/report_signupdate.html', 'Signup date report (by date of signup)' ];
$report_customers{'Advanced customer reports'} = [ $fsurl. 'search/report_cust_main.html', 'by status, signup date, agent, etc.' ]
if $curuser->access_right('Advanced customer search');
+if ( $curuser->access_right('List contacts') ) {
+ $report_customers{'separator'} = '';
+ $report_customers{'Customer contacts'} = [ $fsurl. 'search/report_contact.html?link=cust_main' ];
+}
tie my %report_invoices_open, 'Tie::IxHash',
'All open invoices' => [ $fsurl.'search/cust_bill.html?OPEN_date', 'All invoices with an unpaid balance' ],
tie my %report_menu, 'Tie::IxHash';
$report_menu{'Prospects'} = [ \%report_prospects, 'Prospect reports' ]
- if $curuser->access_right('List prospects');
+ if $curuser->access_right('List prospects')
+ || $curuser->access_right('List contacts');
$report_menu{'Quotations'} = [ \%report_quotations, 'Quotation reports' ]
if $curuser->access_right('List quotations');
$report_menu{'Customers'} = [ \%report_customers, 'Customer reports' ]
- if $curuser->access_right('List customers');
+ if $curuser->access_right('List customers')
+ || $curuser->access_right('List contacts');
$report_menu{'Invoices'} = [ \%report_invoices, 'Invoice reports' ]
if $curuser->access_right('List invoices');
$report_menu{'Discounts'} = [ \%report_discounts, 'Discount reports' ]
<P ALIGN="center"><B><% mt('Permanently delete all services and cancel this customer?') |h %></B>
-<TABLE BORDER="0" CELLSPACING="2"
-STYLE="margin-left:auto; margin-right:auto">
+<TABLE BORDER="0" CELLSPACING="2" STYLE="margin-left:auto; margin-right:auto">
<TR>
<TD ALIGN="right">
<INPUT TYPE="radio" NAME="now_or_later" VALUE="0" onclick="toggle(false)" CHECKED />
<INPUT TYPE="checkbox" NAME="ban" ID="ban" VALUE="1"><% mt($ban) |h %>
% }
-<TABLE BGCOLOR="#cccccc", BORDER="0" CELLSPACING="2"
-STYLE="margin-left:auto; margin-right:auto">
+<TABLE BGCOLOR="#cccccc" BORDER="0" CELLSPACING="2" STYLE="margin-left:auto; margin-right:auto">
<& /elements/tr-select-reason.html,
'field' => 'reasonnum',
'reason_class' => 'C',
<P ALIGN="center"><B><% mt('Suspend this customer?') |h %></B>
-<TABLE BORDER="0" CELLSPACING="2"
-STYLE="margin-left:auto; margin-right:auto">
+<TABLE BORDER="0" CELLSPACING="2" STYLE="margin-left:auto; margin-right:auto">
<TR>
<TD ALIGN="right">
<INPUT TYPE="radio" NAME="now_or_later" VALUE="0" onclick="toggle(false)" CHECKED />
toggle(false);
</SCRIPT>
-<TABLE BGCOLOR="#cccccc", BORDER="0" CELLSPACING="2"
-STYLE="margin-left:auto; margin-right:auto">
+<TABLE BGCOLOR="#cccccc" BORDER="0" CELLSPACING="2" STYLE="margin-left:auto; margin-right:auto">
<& /elements/tr-select-reason.html,
'field' => 'reasonnum',
'reason_class' => 'S',