+ my ($object, $cfid, $cf, $column);
+ ($object, $field, $cf, $column) = $self->_CustomFieldDecipher( $field, $type );
+ $cfid = $cf ? $cf->id : 0 ;
+
+# If we're trying to find custom fields that don't match something, we
+# want tickets where the custom field has no value at all. Note that
+# we explicitly don't include the "IS NULL" case, since we would
+# otherwise end up with a redundant clause.
+
+ my ($negative_op, $null_op, $inv_op, $range_op)
+ = $self->ClassifySQLOperation( $op );
+
+ my $fix_op = sub {
+ return @_ unless RT->Config->Get('DatabaseType') eq 'Oracle';
+
+ my %args = @_;
+ return %args unless $args{'FIELD'} eq 'LargeContent';
+
+ my $op = $args{'OPERATOR'};
+ if ( $op eq '=' ) {
+ $args{'OPERATOR'} = 'MATCHES';
+ }
+ elsif ( $op eq '!=' ) {
+ $args{'OPERATOR'} = 'NOT MATCHES';
+ }
+ elsif ( $op =~ /^[<>]=?$/ ) {
+ $args{'FUNCTION'} = "TO_CHAR( $args{'ALIAS'}.LargeContent )";
+ }
+ return %args;
+ };
+
+ if ( $cf && $cf->Type eq 'IPAddress' ) {
+ my $parsed = RT::ObjectCustomFieldValue->ParseIP($value);
+ if ($parsed) {
+ $value = $parsed;
+ }
+ else {
+ $RT::Logger->warn("$value is not a valid IPAddress");
+ }
+ }
+
+ if ( $cf && $cf->Type eq 'IPAddressRange' ) {
+ my ( $start_ip, $end_ip ) =
+ RT::ObjectCustomFieldValue->ParseIPRange($value);
+ if ( $start_ip && $end_ip ) {
+ if ( $op =~ /^([<>])=?$/ ) {
+ my $is_less = $1 eq '<' ? 1 : 0;
+ if ( $is_less ) {
+ $value = $start_ip;
+ }
+ else {
+ $value = $end_ip;
+ }
+ }
+ else {
+ $value = join '-', $start_ip, $end_ip;
+ }
+ }
+ else {
+ $RT::Logger->warn("$value is not a valid IPAddressRange");
+ }
+ }
+
+ if ( $cf && $cf->Type =~ /^Date(?:Time)?$/ ) {
+ my $date = RT::Date->new( $self->CurrentUser );
+ $date->Set( Format => 'unknown', Value => $value );
+ if ( $date->Unix ) {
+
+ if (
+ $cf->Type eq 'Date'
+ || $value =~ /^\s*(?:today|tomorrow|yesterday)\s*$/i
+ || ( $value !~ /midnight|\d+:\d+:\d+/i
+ && $date->Time( Timezone => 'user' ) eq '00:00:00' )
+ )
+ {
+ $value = $date->Date( Timezone => 'user' );
+ }
+ else {
+ $value = $date->DateTime;
+ }
+ }
+ else {
+ $RT::Logger->warn("$value is not a valid date string");
+ }
+ }
+
+ my $single_value = !$cf || !$cfid || $cf->SingleValue;
+
+ my $cfkey = $cfid ? $cfid : "$type-$object.$field";
+
+ if ( $null_op && !$column ) {
+ # IS[ NOT] NULL without column is the same as has[ no] any CF value,
+ # we can reuse our default joins for this operation
+ # with column specified we have different situation
+ my ($ObjectCFs, $CFs) = $self->_CustomFieldJoin( $cfkey, $cfid, $field, $type );
+ $self->_OpenParen;
+ $self->_SQLLimit(
+ ALIAS => $ObjectCFs,
+ FIELD => 'id',
+ OPERATOR => $op,
+ VALUE => $value,
+ %rest
+ );
+ $self->_SQLLimit(
+ ALIAS => $CFs,
+ FIELD => 'Name',
+ OPERATOR => 'IS NOT',
+ VALUE => 'NULL',
+ QUOTEVALUE => 0,
+ ENTRYAGGREGATOR => 'AND',
+ ) if $CFs;
+ $self->_CloseParen;
+ }
+ elsif ( $op !~ /^[<>]=?$/ && ( $cf && $cf->Type eq 'IPAddressRange')) {
+
+ my ($start_ip, $end_ip) = split /-/, $value;
+
+ $self->_OpenParen;
+ if ( $op !~ /NOT|!=|<>/i ) { # positive equation
+ $self->_CustomFieldLimit(
+ $_field, '<=', $end_ip, %rest,
+ SUBKEY => $rest{'SUBKEY'}. '.Content',
+ );
+ $self->_CustomFieldLimit(
+ $_field, '>=', $start_ip, %rest,
+ SUBKEY => $rest{'SUBKEY'}. '.LargeContent',
+ ENTRYAGGREGATOR => 'AND',
+ );
+ # as well limit borders so DB optimizers can use better
+ # estimations and scan less rows
+# have to disable this tweak because of ipv6
+# $self->_CustomFieldLimit(
+# $_field, '>=', '000.000.000.000', %rest,
+# SUBKEY => $rest{'SUBKEY'}. '.Content',
+# ENTRYAGGREGATOR => 'AND',
+# );
+# $self->_CustomFieldLimit(
+# $_field, '<=', '255.255.255.255', %rest,
+# SUBKEY => $rest{'SUBKEY'}. '.LargeContent',
+# ENTRYAGGREGATOR => 'AND',
+# );
+ }
+ else { # negative equation
+ $self->_CustomFieldLimit($_field, '>', $end_ip, %rest);
+ $self->_CustomFieldLimit(
+ $_field, '<', $start_ip, %rest,
+ SUBKEY => $rest{'SUBKEY'}. '.LargeContent',
+ ENTRYAGGREGATOR => 'OR',
+ );
+ # TODO: as well limit borders so DB optimizers can use better
+ # estimations and scan less rows, but it's harder to do
+ # as we have OR aggregator
+ }
+ $self->_CloseParen;
+ }
+ elsif ( !$negative_op || $single_value ) {
+ $cfkey .= '.'. $self->{'_sql_multiple_cfs_index'}++ if !$single_value && !$range_op;
+ my ($ObjectCFs, $CFs) = $self->_CustomFieldJoin( $cfkey, $cfid, $field, $type );
+
+ $self->_OpenParen;
+
+ $self->_OpenParen;
+
+ $self->_OpenParen;
+ # if column is defined then deal only with it
+ # otherwise search in Content and in LargeContent
+ if ( $column ) {
+ $self->_SQLLimit( $fix_op->(
+ ALIAS => $ObjectCFs,
+ FIELD => $column,
+ OPERATOR => $op,
+ VALUE => $value,
+ CASESENSITIVE => 0,
+ %rest
+ ) );
+ $self->_CloseParen;
+ $self->_CloseParen;
+ $self->_CloseParen;
+ }
+ else {
+ # need special treatment for Date
+ if ( $cf and $cf->Type eq 'DateTime' and $op eq '=' && $value !~ /:/ ) {
+ # no time specified, that means we want everything on a
+ # particular day. in the database, we need to check for >
+ # and < the edges of that day.
+ my $date = RT::Date->new( $self->CurrentUser );
+ $date->Set( Format => 'unknown', Value => $value );
+ my $daystart = $date->ISO;
+ $date->AddDay;
+ my $dayend = $date->ISO;
+
+ $self->_OpenParen;
+
+ $self->_SQLLimit(
+ ALIAS => $ObjectCFs,
+ FIELD => 'Content',
+ OPERATOR => ">=",
+ VALUE => $daystart,
+ %rest,
+ );
+
+ $self->_SQLLimit(
+ ALIAS => $ObjectCFs,
+ FIELD => 'Content',
+ OPERATOR => "<",
+ VALUE => $dayend,
+ %rest,
+ ENTRYAGGREGATOR => 'AND',
+ );
+
+ $self->_CloseParen;
+ }
+ elsif ( $op eq '=' || $op eq '!=' || $op eq '<>' ) {
+ if ( length( Encode::encode( "UTF-8", $value) ) < 256 ) {
+ $self->_SQLLimit(
+ ALIAS => $ObjectCFs,
+ FIELD => 'Content',
+ OPERATOR => $op,
+ VALUE => $value,
+ CASESENSITIVE => 0,
+ %rest
+ );
+ }
+ else {
+ $self->_OpenParen;
+ $self->_SQLLimit(
+ ALIAS => $ObjectCFs,
+ FIELD => 'Content',
+ OPERATOR => '=',
+ VALUE => '',
+ ENTRYAGGREGATOR => 'OR'
+ );
+ $self->_SQLLimit(
+ ALIAS => $ObjectCFs,
+ FIELD => 'Content',
+ OPERATOR => 'IS',
+ VALUE => 'NULL',
+ ENTRYAGGREGATOR => 'OR'
+ );
+ $self->_CloseParen;
+ $self->_SQLLimit( $fix_op->(
+ ALIAS => $ObjectCFs,
+ FIELD => 'LargeContent',
+ OPERATOR => $op,
+ VALUE => $value,
+ ENTRYAGGREGATOR => 'AND',
+ CASESENSITIVE => 0,
+ ) );
+ }
+ }
+ else {
+ $self->_SQLLimit(
+ ALIAS => $ObjectCFs,
+ FIELD => 'Content',
+ OPERATOR => $op,
+ VALUE => $value,
+ CASESENSITIVE => 0,
+ %rest
+ );
+
+ $self->_OpenParen;
+ $self->_OpenParen;
+ $self->_SQLLimit(
+ ALIAS => $ObjectCFs,
+ FIELD => 'Content',
+ OPERATOR => '=',
+ VALUE => '',
+ ENTRYAGGREGATOR => 'OR'
+ );
+ $self->_SQLLimit(
+ ALIAS => $ObjectCFs,
+ FIELD => 'Content',
+ OPERATOR => 'IS',
+ VALUE => 'NULL',
+ ENTRYAGGREGATOR => 'OR'
+ );
+ $self->_CloseParen;
+ $self->_SQLLimit( $fix_op->(
+ ALIAS => $ObjectCFs,
+ FIELD => 'LargeContent',
+ OPERATOR => $op,
+ VALUE => $value,
+ ENTRYAGGREGATOR => 'AND',
+ CASESENSITIVE => 0,
+ ) );
+ $self->_CloseParen;
+ }
+ $self->_CloseParen;
+
+ # XXX: if we join via CustomFields table then
+ # because of order of left joins we get NULLs in
+ # CF table and then get nulls for those records
+ # in OCFVs table what result in wrong results
+ # as decifer method now tries to load a CF then
+ # we fall into this situation only when there
+ # are more than one CF with the name in the DB.
+ # the same thing applies to order by call.
+ # TODO: reorder joins T <- OCFVs <- CFs <- OCFs if
+ # we want treat IS NULL as (not applies or has
+ # no value)
+ $self->_SQLLimit(
+ ALIAS => $CFs,
+ FIELD => 'Name',
+ OPERATOR => 'IS NOT',
+ VALUE => 'NULL',
+ QUOTEVALUE => 0,
+ ENTRYAGGREGATOR => 'AND',
+ ) if $CFs;
+ $self->_CloseParen;
+
+ if ($negative_op) {
+ $self->_SQLLimit(
+ ALIAS => $ObjectCFs,
+ FIELD => $column || 'Content',
+ OPERATOR => 'IS',
+ VALUE => 'NULL',
+ QUOTEVALUE => 0,
+ ENTRYAGGREGATOR => 'OR',
+ );
+ }
+
+ $self->_CloseParen;
+ }
+ }
+ else {
+ $cfkey .= '.'. $self->{'_sql_multiple_cfs_index'}++;
+ my ($ObjectCFs, $CFs) = $self->_CustomFieldJoin( $cfkey, $cfid, $field, $type );
+
+ # reverse operation
+ $op =~ s/!|NOT\s+//i;
+
+ # if column is defined then deal only with it
+ # otherwise search in Content and in LargeContent
+ if ( $column ) {
+ $self->SUPER::Limit( $fix_op->(
+ LEFTJOIN => $ObjectCFs,
+ ALIAS => $ObjectCFs,
+ FIELD => $column,
+ OPERATOR => $op,
+ VALUE => $value,
+ CASESENSITIVE => 0,
+ ) );
+ }
+ else {
+ $self->SUPER::Limit(
+ LEFTJOIN => $ObjectCFs,
+ ALIAS => $ObjectCFs,
+ FIELD => 'Content',
+ OPERATOR => $op,
+ VALUE => $value,
+ CASESENSITIVE => 0,
+ );
+ }
+ $self->_SQLLimit(
+ %rest,
+ ALIAS => $ObjectCFs,
+ FIELD => 'id',
+ OPERATOR => 'IS',
+ VALUE => 'NULL',
+ QUOTEVALUE => 0,
+ );
+ }
+}
+
+sub _HasAttributeLimit {
+ my ( $self, $field, $op, $value, %rest ) = @_;
+
+ my $alias = $self->Join(
+ TYPE => 'LEFT',
+ ALIAS1 => 'main',
+ FIELD1 => 'id',
+ TABLE2 => 'Attributes',
+ FIELD2 => 'ObjectId',
+ );
+ $self->SUPER::Limit(
+ LEFTJOIN => $alias,
+ FIELD => 'ObjectType',
+ VALUE => 'RT::Ticket',
+ ENTRYAGGREGATOR => 'AND'
+ );
+ $self->SUPER::Limit(
+ LEFTJOIN => $alias,
+ FIELD => 'Name',
+ OPERATOR => $op,
+ VALUE => $value,
+ ENTRYAGGREGATOR => 'AND'
+ );
+ $self->_SQLLimit(
+ %rest,
+ ALIAS => $alias,
+ FIELD => 'id',
+ OPERATOR => $FIELD_METADATA{$field}->[1]? 'IS NOT': 'IS',
+ VALUE => 'NULL',
+ QUOTEVALUE => 0,
+ );
+}
+
+# End Helper Functions
+
+# End of SQL Stuff -------------------------------------------------
+
+
+=head2 OrderByCols ARRAY
+
+A modified version of the OrderBy method which automatically joins where
+C<ALIAS> is set to the name of a watcher type.
+
+=cut
+
+sub OrderByCols {
+ my $self = shift;
+ my @args = @_;
+ my $clause;
+ my @res = ();
+ my $order = 0;
+
+ foreach my $row (@args) {
+ if ( $row->{ALIAS} ) {
+ push @res, $row;
+ next;
+ }
+ if ( $row->{FIELD} !~ /\./ ) {
+ my $meta = $self->FIELDS->{ $row->{FIELD} };
+ unless ( $meta ) {
+ push @res, $row;
+ next;
+ }
+
+ if ( $meta->[0] eq 'ENUM' && ($meta->[1]||'') eq 'Queue' ) {
+ my $alias = $self->Join(
+ TYPE => 'LEFT',
+ ALIAS1 => 'main',
+ FIELD1 => $row->{'FIELD'},
+ TABLE2 => 'Queues',
+ FIELD2 => 'id',
+ );
+ push @res, { %$row, ALIAS => $alias, FIELD => "Name" };
+ } elsif ( ( $meta->[0] eq 'ENUM' && ($meta->[1]||'') eq 'User' )
+ || ( $meta->[0] eq 'WATCHERFIELD' && ($meta->[1]||'') eq 'Owner' )
+ ) {
+ my $alias = $self->Join(
+ TYPE => 'LEFT',
+ ALIAS1 => 'main',
+ FIELD1 => $row->{'FIELD'},
+ TABLE2 => 'Users',
+ FIELD2 => 'id',
+ );
+ push @res, { %$row, ALIAS => $alias, FIELD => "Name" };
+ } else {
+ push @res, $row;
+ }
+ next;
+ }
+
+ my ( $field, $subkey ) = split /\./, $row->{FIELD}, 2;
+ my $meta = $self->FIELDS->{$field};
+ if ( defined $meta->[0] && $meta->[0] eq 'WATCHERFIELD' ) {
+ # cache alias as we want to use one alias per watcher type for sorting
+ my $users = $self->{_sql_u_watchers_alias_for_sort}{ $meta->[1] };
+ unless ( $users ) {
+ $self->{_sql_u_watchers_alias_for_sort}{ $meta->[1] }
+ = $users = ( $self->_WatcherJoin( $meta->[1] ) )[2];
+ }
+ push @res, { %$row, ALIAS => $users, FIELD => $subkey };
+ } elsif ( defined $meta->[0] && $meta->[0] eq 'CUSTOMFIELD' ) {
+ my ($object, $field, $cf_obj, $column) = $self->_CustomFieldDecipher( $subkey );
+ my $cfkey = $cf_obj ? $cf_obj->id : "$object.$field";
+ $cfkey .= ".ordering" if !$cf_obj || ($cf_obj->MaxValues||0) != 1;
+ my ($ObjectCFs, $CFs) = $self->_CustomFieldJoin( $cfkey, ($cf_obj ?$cf_obj->id :0) , $field );
+ # this is described in _CustomFieldLimit
+ $self->_SQLLimit(
+ ALIAS => $CFs,
+ FIELD => 'Name',
+ OPERATOR => 'IS NOT',
+ VALUE => 'NULL',
+ QUOTEVALUE => 1,
+ ENTRYAGGREGATOR => 'AND',
+ ) if $CFs;
+ my $CFvs = $self->Join(
+ TYPE => 'LEFT',
+ ALIAS1 => $ObjectCFs,
+ FIELD1 => 'CustomField',
+ TABLE2 => 'CustomFieldValues',
+ FIELD2 => 'CustomField',
+ );
+ $self->SUPER::Limit(
+ LEFTJOIN => $CFvs,
+ FIELD => 'Name',
+ QUOTEVALUE => 0,
+ VALUE => $ObjectCFs . ".Content",
+ ENTRYAGGREGATOR => 'AND'
+ );
+
+ push @res, { %$row, ALIAS => $CFvs, FIELD => 'SortOrder' };
+ push @res, { %$row, ALIAS => $ObjectCFs, FIELD => 'Content' };
+ } elsif ( $field eq "Custom" && $subkey eq "Ownership") {
+ # PAW logic is "reversed"
+ my $order = "ASC";
+ if (exists $row->{ORDER} ) {
+ my $o = $row->{ORDER};
+ delete $row->{ORDER};
+ $order = "DESC" if $o =~ /asc/i;
+ }
+
+ # Ticket.Owner 1 0 X
+ # Unowned Tickets 0 1 X
+ # Else 0 0 X
+
+ foreach my $uid ( $self->CurrentUser->Id, RT->Nobody->Id ) {
+ if ( RT->Config->Get('DatabaseType') eq 'Oracle' ) {
+ my $f = ($row->{'ALIAS'} || 'main') .'.Owner';
+ push @res, {
+ %$row,
+ FIELD => undef,
+ ALIAS => '',
+ FUNCTION => "CASE WHEN $f=$uid THEN 1 ELSE 0 END",
+ ORDER => $order
+ };
+ } else {
+ push @res, {
+ %$row,
+ FIELD => undef,
+ FUNCTION => "Owner=$uid",
+ ORDER => $order
+ };
+ }
+ }
+
+ push @res, { %$row, FIELD => "Priority", ORDER => $order } ;
+
+ } elsif ( $field eq 'Customer' ) { #Freeside
+ # OrderBy(FIELD => expression) doesn't work, it has to be
+ # an actual field, so we have to do the join even if sorting
+ # by custnum
+ my $custalias = $self->JoinToCustomer;
+ my $cust_field = lc($subkey);
+ if ( !$cust_field or $cust_field eq 'number' ) {
+ $cust_field = 'custnum';
+ }
+ elsif ( $cust_field eq 'name' ) {
+ $cust_field = "COALESCE( $custalias.company,
+ $custalias.last || ', ' || $custalias.first
+ )";
+ }
+ else { # order by cust_main fields directly: 'Customer.agentnum'
+ $cust_field = $subkey;
+ }
+ push @res, { %$row, ALIAS => $custalias, FIELD => $cust_field };
+
+ } elsif ( $field eq 'Service' ) {
+
+ my $svcalias = $self->JoinToService;
+ my $svc_field = lc($subkey);
+ if ( !$svc_field or $svc_field eq 'number' ) {
+ $svc_field = 'svcnum';
+ }
+ push @res, { %$row, ALIAS => $svcalias, FIELD => $svc_field };
+
+ } #Freeside
+
+ else {
+ push @res, $row;
+ }
+ }
+ return $self->SUPER::OrderByCols(@res);
+}
+
+#Freeside
+
+sub JoinToCustLinks {
+ # Set up join to links (id = localbase),
+ # limit link type to 'MemberOf',
+ # and target value to any Freeside custnum URI.
+ # Return the linkalias for further join/limit action,
+ # and an sql expression to retrieve the custnum.
+ my $self = shift;
+ # only join once for each RT::Tickets object
+ my $linkalias = $self->{cust_main_linkalias};
+ if (!$linkalias) {
+ $linkalias = $self->Join(
+ TYPE => 'LEFT',
+ ALIAS1 => 'main',
+ FIELD1 => 'id',
+ TABLE2 => 'Links',
+ FIELD2 => 'LocalBase',
+ );
+ $self->SUPER::Limit(
+ LEFTJOIN => $linkalias,
+ FIELD => 'Base',
+ OPERATOR => 'LIKE',
+ VALUE => 'fsck.com-rt://%/ticket/%',
+ );
+ $self->SUPER::Limit(
+ LEFTJOIN => $linkalias,
+ FIELD => 'Type',
+ OPERATOR => '=',
+ VALUE => 'MemberOf',
+ );
+ $self->SUPER::Limit(
+ LEFTJOIN => $linkalias,
+ FIELD => 'Target',
+ OPERATOR => 'STARTSWITH',
+ VALUE => 'freeside://freeside/cust_main/',
+ );
+ $self->{cust_main_linkalias} = $linkalias;
+ }
+ my $custnum_sql = "CAST(SUBSTR($linkalias.Target,31) AS ";
+ if ( RT->Config->Get('DatabaseType') eq 'mysql' ) {
+ $custnum_sql .= 'SIGNED INTEGER)';
+ }
+ else {
+ $custnum_sql .= 'INTEGER)';
+ }
+ return ($linkalias, $custnum_sql);
+}
+
+sub JoinToCustomer {
+ my $self = shift;
+ my ($linkalias, $custnum_sql) = $self->JoinToCustLinks;
+ # don't reuse this join, though--negative queries need
+ # independent joins
+ my $custalias = $self->Join(
+ TYPE => 'LEFT',
+ EXPRESSION => $custnum_sql,
+ TABLE2 => 'cust_main',
+ FIELD2 => 'custnum',
+ );
+ return $custalias;
+}
+
+sub JoinToSvcLinks {
+ my $self = shift;
+ my $linkalias = $self->{cust_svc_linkalias};
+ if (!$linkalias) {
+ $linkalias = $self->Join(
+ TYPE => 'LEFT',
+ ALIAS1 => 'main',
+ FIELD1 => 'id',
+ TABLE2 => 'Links',
+ FIELD2 => 'LocalBase',
+ );
+ $self->SUPER::Limit(
+ LEFTJOIN => $linkalias,
+ FIELD => 'Base',
+ OPERATOR => 'LIKE',
+ VALUE => 'fsck.com-rt://%/ticket/%',
+ );
+
+ $self->SUPER::Limit(
+ LEFTJOIN => $linkalias,
+ FIELD => 'Type',
+ OPERATOR => '=',
+ VALUE => 'MemberOf',
+ );
+ $self->SUPER::Limit(
+ LEFTJOIN => $linkalias,
+ FIELD => 'Target',
+ OPERATOR => 'STARTSWITH',
+ VALUE => 'freeside://freeside/cust_svc/',
+ );
+ $self->{cust_svc_linkalias} = $linkalias;
+ }
+ my $svcnum_sql = "CAST(SUBSTR($linkalias.Target,30) AS ";
+ if ( RT->Config->Get('DatabaseType') eq 'mysql' ) {
+ $svcnum_sql .= 'SIGNED INTEGER)';
+ }
+ else {
+ $svcnum_sql .= 'INTEGER)';
+ }
+ return ($linkalias, $svcnum_sql);
+}
+
+sub JoinToService {
+ my $self = shift;
+ my ($linkalias, $svcnum_sql) = $self->JoinToSvcLinks;
+ $self->Join(
+ TYPE => 'LEFT',
+ EXPRESSION => $svcnum_sql,
+ TABLE2 => 'cust_svc',
+ FIELD2 => 'svcnum',
+ );
+}
+
+# This creates an alternate left join path to cust_main via cust_svc.
+# _FreesideFieldLimit needs to add this as a separate, independent join
+# and include all tickets that have a matching cust_main record via
+# either path.
+sub JoinToCustomerViaService {
+ my $self = shift;
+ my $svcalias = $self->JoinToService;
+ my $cust_pkg = $self->Join(
+ TYPE => 'LEFT',
+ ALIAS1 => $svcalias,
+ FIELD1 => 'pkgnum',
+ TABLE2 => 'cust_pkg',
+ FIELD2 => 'pkgnum',
+ );
+ my $cust_main = $self->Join(
+ TYPE => 'LEFT',
+ ALIAS1 => $cust_pkg,
+ FIELD1 => 'custnum',
+ TABLE2 => 'cust_main',
+ FIELD2 => 'custnum',
+ );
+ $cust_main;
+}
+
+sub _FreesideFieldLimit {
+ my ( $self, $field, $op, $value, %rest ) = @_;
+ my $is_negative = 0;
+ if ( $op eq '!=' || $op =~ /\bNOT\b/i ) {
+ # if the op is negative, do the join as though
+ # the op were positive, then accept only records
+ # where the right-side join key is null.
+ $is_negative = 1;
+ $op = '=' if $op eq '!=';
+ $op =~ s/\bNOT\b//;
+ }
+
+ my (@alias, $table2, $subfield, $pkey);
+ if ( $field eq 'Customer' ) {
+ push @alias, $self->JoinToCustomer;
+ push @alias, $self->JoinToCustomerViaService;
+ $pkey = 'custnum';
+ }
+ elsif ( $field eq 'Service' ) {
+ push @alias, $self->JoinToService;
+ $pkey = 'svcnum';
+ }
+ else {
+ die "malformed Freeside query: $field";
+ }
+
+ $subfield = $rest{SUBKEY} || $pkey;
+ # compound subkey: separate into table name and field in that table
+ # (must be linked by custnum)
+ $subfield = lc($subfield);
+ ($table2, $subfield) = ($1, $2) if $subfield =~ /^(\w+)?\.(\w+)$/;
+ $subfield = $pkey if $subfield eq 'number';
+
+ # if it's compound, create a join from cust_main or cust_svc to that
+ # table, using custnum or svcnum, and Limit on that table instead.
+ my @_SQLLimit = ();
+ foreach my $a (@alias) {
+ if ( $table2 ) {
+ $a = $self->Join(
+ TYPE => 'LEFT',
+ ALIAS1 => $a,
+ FIELD1 => $pkey,
+ TABLE2 => $table2,
+ FIELD2 => $pkey,
+ );
+ }
+
+ # do the actual Limit
+ $self->SUPER::Limit(
+ LEFTJOIN => $a,
+ FIELD => $subfield,
+ OPERATOR => $op,
+ VALUE => $value,
+ ENTRYAGGREGATOR => 'AND',
+ # no SUBCLAUSE needed, limits on different aliases across left joins
+ # are inherently independent
+ );
+
+ # then, since it's a left join, exclude tickets for which there is now
+ # no matching record in the table we just limited on. (Or where there
+ # is a matching record, if $is_negative.)
+ # For a cust_main query (where there are two different aliases), this
+ # will produce a subclause: "cust_main_1.custnum IS NOT NULL OR
+ # cust_main_2.custnum IS NOT NULL" (or "IS NULL AND..." for a negative
+ # query).
+ #$self->_SQLLimit(
+ push @_SQLLimit, {
+ %rest,
+ ALIAS => $a,
+ FIELD => $pkey,
+ OPERATOR => $is_negative ? 'IS' : 'IS NOT',
+ VALUE => 'NULL',
+ QUOTEVALUE => 0,
+ ENTRYAGGREGATOR => $is_negative ? 'AND' : 'OR',
+ SUBCLAUSE => 'fs_limit',
+ };
+ }
+
+ $self->_OpenParen;
+ foreach my $_SQLLimit (@_SQLLimit) {
+ $self->_SQLLimit( %$_SQLLimit);
+ }
+ $self->_CloseParen;
+
+}
+
+#Freeside
+
+=head2 Limit
+
+Takes a paramhash with the fields FIELD, OPERATOR, VALUE and DESCRIPTION
+Generally best called from LimitFoo methods
+
+=cut
+
+sub Limit {
+ my $self = shift;
+ my %args = (
+ FIELD => undef,
+ OPERATOR => '=',
+ VALUE => undef,
+ DESCRIPTION => undef,
+ @_
+ );
+ $args{'DESCRIPTION'} = $self->loc(
+ "[_1] [_2] [_3]", $args{'FIELD'},
+ $args{'OPERATOR'}, $args{'VALUE'}
+ )
+ if ( !defined $args{'DESCRIPTION'} );
+
+ my $index = $self->_NextIndex;
+
+# make the TicketRestrictions hash the equivalent of whatever we just passed in;
+
+ %{ $self->{'TicketRestrictions'}{$index} } = %args;
+
+ $self->{'RecalcTicketLimits'} = 1;
+
+# If we're looking at the effective id, we don't want to append the other clause
+# which limits us to tickets where id = effective id
+ if ( $args{'FIELD'} eq 'EffectiveId'
+ && ( !$args{'ALIAS'} || $args{'ALIAS'} eq 'main' ) )
+ {
+ $self->{'looking_at_effective_id'} = 1;
+ }
+
+ if ( $args{'FIELD'} eq 'Type'
+ && ( !$args{'ALIAS'} || $args{'ALIAS'} eq 'main' ) )
+ {
+ $self->{'looking_at_type'} = 1;
+ }
+
+ return ($index);
projects / freeside.git / blobdiff