Solved

What does this mean?

Posted on 2016-10-06
6
34 Views
Last Modified: 2016-10-06
I've got a SELECT statement configured in PHP, the last part of which looks like this:

 and cg.id in (select chargegroupid from txn where accountid = $statement[AccountID]) "
            . (
                $filterDate ?
                    "
                    and cg.id not in (
                        select chargegroupid from txn where accountid = $statement[AccountID] group by chargegroupid having max(posted) > dbo.ufn_lastpaperstatementbefore($statement[AccountID], cast('2050-01-01' as date))
                    )"
                    :
                    ""
            ) . "
            group by t1.accountid, t1.chargegroupid, t1.encountercode "
            . (
                is_int($ageOutZeroAfter)?
                    " having datediff(d, max(t1.created), getDate()) <= $ageOutZeroAfter or sum(amount) <> 0 "
                    :
                    ""
            ) . "
order by dos";

Open in new window


When you print that portion of the sql out in MSSQL Studio, it looks like this:

cg.id in (select chargegroupid from txn where accountid = 12159877) group by t1.accountid, t1.chargegroupid, t1.encountercode having datediff(d, max(t1.created), getDate()) <= 45 or sum(amount) <> 0 order by dos

I didn't want to thicken the plot unnecessarily, but for the sake of ensuring you've got a complete picture of what's going on, here's the whole function:

protected static function getOldTransactions(
        $statement,
        $showPhysician = true,
        $filterDate = true,
        $ageOutZeroAfter = false
    ){
        /**
        Get the Old Transactions
         **/
        //krumo('oldtxn');
        $sql = "
            select
                t1.accountid,
                t1.chargegroupid,
                t1.encountercode,
                /* Max automatically disregards nulls */" . (
                    $showPhysician ?
                    "
                    coalesce(max(physician), '')
                    "
                    :
                    ("'" .
                    str_replace("'", "''", $statement["PracticeName"])
                    . "'")
                ) . " as provider,
                sum(case when t1.type = 'c' then amount else 0 end) as total_charges,
                sum(case when t1.type = 'a' then amount else 0 end) as total_adjustments,
                sum(case when t1.type = 'p' then amount else 0 end) as total_payments,
                sum(amount) as left_to_pay,
                /* Charge description */
                /* blank for now */
                '' as description,
                 cast(min(case when t1.type = 'c' then dos else null end) as date) as dos

            from txn t1
                left join chargegroup cg on t1.chargegroupid = cg.id
                /* Charge description join */
                left join (
                    select
                        max(case when (txn.type = 'C') then amount else null end ) as max_charge_amount,
                        accountid,
                        chargegroupid
                    from txn
                    group by accountid, chargegroupid
                ) t2 on t2.accountid = t1.accountid and t2.chargegroupid = t1.chargegroupid
                where t1.accountid = $statement[AccountID]
                and reversedref is null
                and (cg.status1 not in('rt','hd','rp','fc') or cg.status1 is null)
                and cg.id in (select chargegroupid from txn where accountid = $statement[AccountID]) "
            . (
                $filterDate ?
                    "
                    and cg.id not in (
                        select chargegroupid from txn where accountid = $statement[AccountID] group by chargegroupid having max(posted) > dbo.ufn_lastpaperstatementbefore($statement[AccountID], cast('2050-01-01' as date))
                    )"
                    :
                    ""
            ) . "
            group by t1.accountid, t1.chargegroupid, t1.encountercode "
            . (
                is_int($ageOutZeroAfter)?
                    " having datediff(d, max(t1.created), getDate()) <= $ageOutZeroAfter or sum(amount) <> 0 "
                    :
                    ""
            ) . "

        order by dos";
		echo $sql;
        return StatementImage::runQuery($sql);
    }

}

Open in new window


so $filterDate is true and  $ageOutZeroAfter = false

I'm not certain if the "?" represents the equivalent to an IF clause or a CASE dynamic. But what would the purpose be if the properties are fixed?

Bottom line: I don't understand the purpose of the "?" and the overall flow.

Thoughts?
0
Comment
Question by:brucegust
  • 3
  • 2
6 Comments
 
LVL 42

Assisted Solution

by:zephyr_hex
zephyr_hex earned 250 total points
ID: 41832088
oh lord, what horrible coding style!  no wonder it's cryptic.  That is a PHP condition wrapped in the middle of a string concatenation.

To pull out the condition:

$filterDate ? "then return this string" : ""

Open in new window


which reads as:  If $filterDate then return t his string otherwise return an empty string.

$filterDate ?  "and cg.id not in (select ...blah blah blah" : ""

Open in new window

0
 
LVL 51

Accepted Solution

by:
Julian Hansen earned 250 total points
ID: 41832119
The ? : is called a ternary operator - it is a shorthand if statement

if ($expr) {
  $x = 1;
}
else {
  $x = 2;
}

Open in new window

In a ternary expression looks like this
$x = $expr ? 1 : 2;

Open in new window

They are useful sometimes but if used incorrectly can make your code a nightmare.

I would never use it like it has been done in the query - the potential for things to go wrong is immense and it makes for spaghetti code

Preference for strings is always HEREDOC
$id = $authenticated ? 'admin' : 'public';
$query = <<< QUERY
    SELECT * FROM {$table} WHERE type='{$type}'
QUERY;

Open in new window

Any variables that require complex expressions are set up front and then used in the HEREDOC.
0
 

Author Comment

by:brucegust
ID: 41832205
Thank you!

So, basically, since $filterDate is always true, I can expect...

   and cg.id not in (
                        select chargegroupid from txn where accountid = $statement[AccountID] group by chargegroupid having max(posted) > dbo.ufn_lastpaperstatementbefore($statement[AccountID], cast('2050-01-01' as date))
                    )

Open in new window


...to always run. I'll never have an empty string.

Got it!

And Julian, I went out and looked up "ternary." I see how it's an appropriate label in that you're looking at "three" elements as part of building the IF dynamic. Makes sense, although I can see how there's a lot of room for error.

I was able to go back into the code and see how every time this method is called, the $ageOutZeroAfter value is passed into the method as the number 45, which I'm assuming overrides the property as it's defined by default as "false."

Correct?

Thank you, gentlemen!
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 42

Expert Comment

by:zephyr_hex
ID: 41832312
Yes, if $ageOutZeroAfter is passed in, it will override the default 'false'.

That being said, it's against generally accepted good coding practices to allow a variable to change data types.  In this case, $ageOutZeroAfter could be both a boolean and a number.  PHP is not a strongly typed language, so it allows variables to change data types.  But just because it allows it doesn't mean it should be done.  It's a potential source for bugs.  So it should be avoided.  Booleans variables should only be assigned true / false.  Numeric variables should only be assigned a number.
0
 

Author Comment

by:brucegust
ID: 41832399
It's funny that you say that because this is the way the method is called earlier in the page:

$number_of_days_to_keep_zero_balance_encounters_on_the_statement = 45;
$st['oldtxns'] = StatementImage::getOldTransactions(
      $st,
      $st['showphysician'] == '1',
      $st['StatementRollUp'] == '0',
      $number_of_days_to_keep_zero_balance_encounters_on_the_statement
);

Here's the way the method appears on the page:

    protected static function getOldTransactions(
        $statement,
        $showPhysician = true,
        $filterDate = true,
        $ageOutZeroAfter = false
    ){

So, if I understand you correctly and I'm reading this right, $filterDate is being set by $st['StatementRollUp'] which is 0. That's going to resonate as "false-y," will it not? That means that part of the method will never run, thus making a large part of this SELECT irrelevant.

There you go...

Thanks!

BTW: I would love y'all to take a look at my code samples and give me some input: https://www.experts-exchange.com/questions/28974267/My-Code-Samples-What-Do-You-Think.html
0
 
LVL 42

Expert Comment

by:zephyr_hex
ID: 41832433
Yes, the string '0' will be interpreted as false.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article discusses four methods for overlaying images in a container on a web page
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now