###
'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
'Credit card void',
'Echeck void',
'Void invoices',#people are overusing this when credits are more appropriate
+ 'Backdate credit',
);
no warnings 'uninitialized';
# 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
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',
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) . "
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
"(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) . "
# 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
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;
}
} 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}) };
}
}
'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 )
'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 )
: ''
),
} 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
+ )
";
}
'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 ) {
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'
+ )
";
}
$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);
},
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 => {},
<INPUT TYPE="hidden" NAME="crednum" VALUE="">
<INPUT TYPE="hidden" NAME="custnum" VALUE="<% $custnum |h %>">
<INPUT TYPE="hidden" NAME="paybatch" VALUE="">
-<INPUT TYPE="hidden" NAME="_date" VALUE="<% $_date %>">
<INPUT TYPE="hidden" NAME="credited" VALUE="">
<% ntable("#cccccc", 2) %>
- <TR>
- <TD ALIGN="right"><% mt('Date') |h %></TD>
- <TD BGCOLOR="#ffffff"><% time2str($date_format, $_date) %></TD>
- </TR>
+% my %date_args = (
+% 'name' => '_date',
+% 'label' => emt('Date'),
+% 'value' => $_date,
+% 'format' => $date_format. ' %r',
+% );
+% if ( $FS::CurrentUser::CurrentUser->access_right('Backdate credit') ) {
+
+ <& /elements/tr-input-date-field.html, \%date_args &>
+
+% } else {
+
+ <& /elements/tr-fixed-date.html, \%date_args &>
+
+% }
<TR>
<TD ALIGN="right"><% mt('Amount') |h %></TD>
<% mt('Payment') |h %>
<% ntable("#cccccc", 2) %>
+% my %date_args = (
+% 'name' => '_date',
+% 'label' => emt('Date'),
+% 'value' => $_date,
+% 'format' => $date_format. ' %r',
+% 'colspan' => 2,
+% );
% if ( $FS::CurrentUser::CurrentUser->access_right('Backdate payment') ) {
-<TR>
- <TD ALIGN="right"><% mt('Date') |h %></TD>
- <TD COLSPAN=2>
- <INPUT TYPE="text" NAME="_date" ID="_date_text" VALUE="<% time2str($date_format.' %r',$_date) %>">
- <IMG SRC="../images/calendar.png" ID="_date_button" STYLE="cursor: pointer" TITLE="<% mt('Select date') |h %>">
- </TD>
-</TR>
-<SCRIPT TYPE="text/javascript">
- Calendar.setup({
- inputField: "_date_text",
- ifFormat: "<% $date_format %>",
- button: "_date_button",
- align: "BR"
- });
-</SCRIPT>
-% }
-% else {
-<TR>
- <TD ALIGN="right"><% mt('Date') |h %></TD>
- <TD COLSPAN=2>
- <% time2str($date_format.' %r',$_date) %>
- </TD>
-</TR>
+ <& /elements/tr-input-date-field.html, \%date_args &>
+
+% } else {
+
+ <& /elements/tr-fixed-date.html, \%date_args &>
+
% }
<TR>
<TD ALIGN="right"><% mt('Amount') |h %></TD>
- <TD BGCOLOR="#ffffff" ALIGN="right"><% $money_char %></TD>
- <TD><INPUT TYPE="text" NAME="paid" ID="paid" VALUE="<% $paid %>" SIZE=8 MAXLENGTH=9> <% mt('by') |h %> <B><% mt(FS::payby->payname($payby)) |h %></B></TD>
+ <TD BGCOLOR="#ffffff"><% $money_char |h %><INPUT TYPE="text" NAME="paid" ID="paid" VALUE="<% $paid %>" SIZE=8 MAXLENGTH=9> <% mt('by') |h %> <B><% mt(FS::payby->payname($payby)) |h %></B></TD>
</TR>
% if ( $conf->exists('part_pkg-term_discounts') ) {
}
$cgi->param('reasonnum', $reasonnum) unless $error;
+my $_date;
+if ( $FS::CurrentUser::CurrentUser->access_right('Backdate credit') ) {
+ $_date = parse_datetime($cgi->param('_date'));
+}
+else {
+ $_date = time;
+}
+
+my @fields = grep { $_ ne '_date' } fields('cust_credit');
+
unless ($error) {
my $new = new FS::cust_credit ( {
- map {
- $_, scalar($cgi->param($_));
- } fields('cust_credit')
+ _date => $_date,
+ map { $_ => scalar($cgi->param($_)) } @fields
} );
$error = $new->insert;
}
#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' ]
<% include('tr-fixed.html', %opt ) %>
<%init>
-my %opt = @_;
+my %opt;
+if ( ref($_[0]) ) {
+ my $hashref = shift;
+ %opt = %$hashref;
+} else {
+ %opt = @_;
+}
my $value = $opt{'curr_value'} || $opt{'value'};
my $conf = new FS::Conf;
-my $date_format = $conf->config('date_format') || '%m/%d/%Y';
+my $date_format = $opt{'format'} || $conf->config('date_format') || '%m/%d/%Y';
$opt{'formatted_value'} = time2str($date_format, $value);
<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',
projects / freeside.git / commitdiff