Hi @FozzyGo4

That's not correct. You will get type = 3 in cdrs table when any provider has tried to make calls or send you calls.

Basically whoever making calls (Leg A), we capture that account type and insert into cdrs table as we consider they are the originator of the

If you will put type != 3 then it will start skipping of your interconnect provider cdrs as well DID cdrs, and that will make report inappropriate.

We are both half right. In the CDR table there are double entries for an inbound call. The same call with the same date time stamp has two records of type = 3. One is a DID and the other is a Local with disposition of STANDARD.

The dashboard currently counts 2 count(uniqueid) as sum calls but it is actually only 1 call. The query needs to only count the DID record I feel but correct me please if I'm wrong.

For DID calls, yes we are doing two entries of CDRs.
1st CDR with Provider ID (STANDARD)
2nd CDR with Customer ID (DID)

The reason for not merging both CDRs in dashboard is, both call records are associated with a different account and both records have billable seconds (However for providers you have the option to make chargeable to make it free using INB_FREE) and their charges.

If we will apply unique constraint while showing in dashboard, then it will start showing incorrect counts as it may possible someone has charged provider based on allocated tariff plan and it will be skipped.

Im not saying "do not capture two records". Im saying the call count is duplicated and is incorrect. Let me try and explain.
Caller calls via provider and this call is routed to ASTPP. ASTPP received the call and if there is a DID for this call, should route to the customer / reseller etc. Im sure you will agree that this is 1 call for x amount of seconds. Lets say it is for 60 seconds (1 minute).

Regardless of how many records are in the CDR and if the call is free or charged for, it is still 1 call for 60 seconds.

The dashboard graph and pie graph is currently counting records so it will see 1st CDR with Provider ID (STANDARD) and 2nd CDR with Customer ID (DID) as 2 calls and the minute count is now 120 seconds (2 minutes).

This means all inbound calls are counted twice and the dashboard is not accurate.

It should only count 1 of these CDR records on the dashboard.

I agree with your point. Dashboard logic requires a change to display 1
record for such cases.

We will work out on that.

[image: Mailtrack]
<https://mailtrack.io?utm_source=gmail&utm_medium=signature&utm_campaign=signaturevirality6&>
Sender
notified by
Mailtrack
<https://mailtrack.io?utm_source=gmail&utm_medium=signature&utm_campaign=signaturevirality6&>
07/19/18,
1:07:07 PM

Best Regards

Samir Doshi
iNextrix Technologies Pvt. Ltd.
http://www.inextrix.com

Disclaimer:
The information contained in this communication is confidential and may be
legally privileged. It is intended solely for the use of the individual or
entity to whom it is addressed and others authorized to receive it. If you
are not the intended recipient you are hereby notified that any disclosure,
copying, distribution or taking action in reliance on the contents of this
information is strictly prohibited and may be unlawful. Please notify the
sender immediately and destroy all copies of this message and any
attachments contained in it.

On Tue, Jul 17, 2018 at 12:46 AM, Stephen Forster <notifications@github.com>
wrote:

> Im not saying "do not capture two records". Im saying the call count is
> duplicated and is incorrect. Let me try and explain.
> Caller calls via provider and this call is routed to ASTPP. ASTPP received
> the call and if there is a DID for this call, should route to the customer
> / reseller etc. Im sure you will agree that this is 1 call for x amount of
> seconds. Lets say it is for 60 seconds (1 minute).
>
> Regardless of how many records are in the CDR and if the call is free or
> charged for, it is still 1 call for 60 seconds.
>
> The dashboard graph and pie graph is currently counting records so it will
> see 1st CDR with Provider ID (STANDARD) and 2nd CDR with Customer ID (DID)
> as 2 calls and the minute count is now 120 seconds (2 minutes).
>
> This means all inbound calls are counted twice and the dashboard is not
> accurate.
>
> It should only count 1 of these CDR records on the dashboard.
>
> —
> You are receiving this because you were assigned.
> Reply to this email directly, view it on GitHub
> <https://github.com/iNextrix/ASTPP/issues/455#issuecomment-405351051>, or mute
> the thread
> <https://github.com/notifications/unsubscribe-auth/AA6gcefT4x3vNJoARpfKNEqmvUPaQibmks5uHOZ1gaJpZM4VKz0O>
> .
>

Hi @FozzyGo4

We changed code to make dashboard graph working. Here is the code,

function get_call_statistics($table, $parent_id, $start_date = '', $end_date = '', $group_flag = true) {
		$this->db->select ( "count(DISTINCT substr(uniqueid,1,36)) as sum,
                           count(CASE WHEN (billseconds > 0 and substr(uniqueid,37,3)!='DID') THEN 1 END) as answered,
                           MAX(billseconds) AS mcd,
                           SUM(CASE WHEN (billseconds > 0 and substr(uniqueid,37,3)!='DID') THEN 1 END) AS duration,
                           count(CASE WHEN (disposition NOT IN ('NORMAL_CLEARING','SUCCESS','NORMAL_CLEARING [16]') and substr(uniqueid,37,3)!='DID') THEN 1 END) as failed,
                           SUM(CASE WHEN (calltype !='free' and substr(uniqueid,37,3)!='DID') THEN billseconds ELSE 0 END) as billable,
                           sum(debit-cost) as profit,
                           sum(debit) as debit,
                           sum(cost) as cost,
                           SUM(CASE WHEN (billseconds > 0 and substr(uniqueid,37,3)!='DID') THEN 1 ELSE 0 END) as completed,
                           DAY(callstart) as day", false );
		$this->db->where ( 'callstart >=', $start_date . " 00:00:00" );
		$this->db->where ( 'callstart <=', $end_date . " 23:59:59" );
		$this->db->where ( 'reseller_id', $parent_id );
		if ($group_flag)
			$this->db->group_by ( "DAY(callstart)" );
		$result = $this->db->get ( $table );
		return $result;
	}

Please replace this code in dashboard_model.php and check. If this works well then we will workout on other related reports.

I see, so you are excluding all calls with disposition of "DID" so the query is counting the actual calls. Good idea. I will make the changes and check from my side and give feedback.

Alright. Waiting for your feedback

[image: Mailtrack]
<https://mailtrack.io?utm_source=gmail&utm_medium=signature&utm_campaign=signaturevirality6&>
Sender
notified by
Mailtrack
<https://mailtrack.io?utm_source=gmail&utm_medium=signature&utm_campaign=signaturevirality6&>
07/25/18,
10:55:26 AM

Best Regards

Samir Doshi
iNextrix Technologies Pvt. Ltd.
http://www.inextrix.com

Disclaimer:
The information contained in this communication is confidential and may be
legally privileged. It is intended solely for the use of the individual or
entity to whom it is addressed and others authorized to receive it. If you
are not the intended recipient you are hereby notified that any disclosure,
copying, distribution or taking action in reliance on the contents of this
information is strictly prohibited and may be unlawful. Please notify the
sender immediately and destroy all copies of this message and any
attachments contained in it.

On Tue, Jul 24, 2018 at 11:49 AM, Stephen Forster <notifications@github.com>
wrote:

> I see, so you are excluding all calls with disposition of "DID" so the
> query is counting the actual calls. Good idea. I will make the changes and
> check from my side and give feedback.
>
> —
> You are receiving this because you were assigned.
> Reply to this email directly, view it on GitHub
> <https://github.com/iNextrix/ASTPP/issues/455#issuecomment-407294180>, or mute
> the thread
> <https://github.com/notifications/unsubscribe-auth/AA6gcTiiX5tWxb1W1_rz_6y-9t3Rct7Bks5uJryKgaJpZM4VKz0O>
> .
>

Hi @FozzyGo4

Did you check?

Hi, yes sorry I did check and it works as expected thank you.

Sent from my iPhone

On 08 Sep 2018, at 9:31 AM, Samir Doshi <notifications@github.com<notifications@github.com>> wrote:

Hi @FozzyGo4<https://github.com/FozzyGo4>

Did you check?

-
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub<https://github.com/iNextrix/ASTPP/issues/455#issuecomment-419620293>, or mute the thread<https://github.com/notifications/unsubscribe-auth/AT8i_6kxeH0rjivcV2iW_v5ITbMCa2irks5uY3JSgaJpZM4VKz0O>.

Alright. Thanks for the update.

On Sat, Sep 8, 2018, 1:52 PM Stephen Forster <notifications@github.com>
wrote:

> Hi, yes sorry I did check and it works as expected thank you.
>
> Sent from my iPhone
>
> On 08 Sep 2018, at 9:31 AM, Samir Doshi <notifications@github.com<mailto:
> notifications@github.com>> wrote:
>
>
> Hi @FozzyGo4<https://github.com/FozzyGo4>
>
> Did you check?
>
> -
> You are receiving this because you were mentioned.
> Reply to this email directly, view it on GitHub<
> https://github.com/iNextrix/ASTPP/issues/455#issuecomment-419620293>, or
> mute the thread<
> https://github.com/notifications/unsubscribe-auth/AT8i_6kxeH0rjivcV2iW_v5ITbMCa2irks5uY3JSgaJpZM4VKz0O
> >.
>
> —
> You are receiving this because you were assigned.
> Reply to this email directly, view it on GitHub
> <https://github.com/iNextrix/ASTPP/issues/455#issuecomment-419623121>, or mute
> the thread
> <https://github.com/notifications/unsubscribe-auth/AA6gcVuUIGkKIZD4HBrmpHE9QbSd1l01ks5uY35jgaJpZM4VKz0O>
> .
>

Hi Samir,

Further to the fixes with the pie charts, I have optimised the speed of the query to MySQL by using temporary tables in the PHP code. I noticed that the query was putting a lot of stress on the MySQL DB. This is mostly because of MySQL and "order by descending" having to work with millions of records. By using a query to get only the records required into a temporary table and then running the charts query on the temp data, the query is much faster and less stress on the recourses of the server.

Shall I contribute the code here?

Yes please.

On Sun, Oct 14, 2018, 12:45 AM Stephen Forster <notifications@github.com>
wrote:

> Hi Samir,
>
> Further to the fixes with the pie charts, I have optimised the speed of
> the query to MySQL by using temporary tables in the PHP code. I noticed
> that the query was putting a lot of stress on the MySQL DB. This is mostly
> because of MySQL and "order by descending" having to work with millions of
> records. By using a query to get only the records required into a temporary
> table and then running the charts query on the temp data, the query is much
> faster and less stress on the recourses of the server.
>
> Shall I contribute the code here?
>
> —
> You are receiving this because you were assigned.
> Reply to this email directly, view it on GitHub
> <https://github.com/iNextrix/ASTPP/issues/455#issuecomment-429567963>, or mute
> the thread
> <https://github.com/notifications/unsubscribe-auth/AA6gcdWV7gKsNK1oDM_5spPsLzb-M7w7ks5ukjvvgaJpZM4VKz0O>
> .
>

Herewith the modified code.

Top 10 customer minutes function

function get_customer_maximum_callminutes($start_date, $end_date) {
                $start_date = $start_date . " 00:00:00";
                $end_date = $end_date . " 23:59:59";
                $accountinfo = $this->session->userdata ( 'accountinfo' );
                $parent_id = ($accountinfo ['type'] == 1) ? $accountinfo ['id'] : 0;
                if ($this->session->userdata ( 'userlevel_logintype' ) != 0 && $this->session->userdata ( 'userlevel_logintype' ) != 3) {
                        $where = "reseller_id ='$parent_id'";
                } else {
                        $where = "accountid ='$parent_id'";
                }
                $where = $where . " AND callstart >= '" . $start_date . "' AND  callstart <= '" . $end_date . "'";

                $billingsectemp_query = "CREATE TEMPORARY TABLE IF NOT EXISTS totalbillingsec AS (SELECT billseconds,accountid FROM (cdrs) WHERE $where and type != '3')
                UNION
                (SELECT billseconds,accountid FROM (cdrs) WHERE $where and type = '3' AND disposition = 'DID')
                UNION
                (SELECT billseconds,accountid FROM (reseller_cdrs) WHERE $where ) ";
                $select_query = "SELECT sum( billseconds ) as billseconds, accountid from totalbillingsec group by accountid order by billseconds DESC LIMIT 10";
                $this->db->query ( $billingsectemp_query );
                return $this->db->query ( $select_query );
        }

Top 10 customer call count function

function get_customer_maximum_callcount($start_date, $end_date) {
                $start_date = $start_date . " 00:00:00";
                $end_date = $end_date . " 23:59:59";
                $accountinfo = $this->session->userdata ( 'accountinfo' );
                $parent_id = ($accountinfo ['type'] == 1) ? $accountinfo ['id'] : 0;
                if ($this->session->userdata ( 'userlevel_logintype' ) != 0 && $this->session->userdata ( 'userlevel_logintype' ) != 3) {
                        $where = "reseller_id ='$parent_id'";
                } else {
                        $where = "accountid ='$parent_id'";
                }
                $where = $where . " AND callstart >= '" . $start_date . "' AND  callstart <= '" . $end_date . "'";
                $callcounttemp_query = "CREATE TEMPORARY TABLE IF NOT EXISTS totalcalls AS (SELECT uniqueid,accountid FROM cdrs WHERE $where)
                UNION
                (SELECT uniqueid, accountid FROM reseller_cdrs WHERE $where) ";
                $select_query = "SELECT count(DISTINCT substr(uniqueid,1,36)) as call_count, accountid FROM totalcalls GROUP BY accountid ORDER BY call_count desc LIMIT 10";
                $this->db->query ( $callcounttemp_query );
                return $this->db->query ( $select_query );
        }

The changes are to first get the data based on where clause into a temporary table and then run the order by, group by and and calculations on the temporary data. Shaves of many seconds and resources on my system.

Im sure you could probably optimise this even further and perhaps use the same principle on other modules like reports.

Regards
Stephen