<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
</head>
<body text="#000000" bgcolor="#FFFFFF">
<div class="moz-cite-prefix">10 minutes and counting so far :(<br>
<br>
<br>
El 25/01/2018 a las 16:45, Rene Krenn escribió:<br>
</div>
<blockquote type="cite"
cite="mid:020e01d395f3$852ce5a0$8f86b0e0$@sipwise.com">
<pre wrap="">Hi,
thanks for pointing.
Can you try running this modified query below on your test database a few times,
and report back how long it took (longer or less 2 minutes)?
SELECT bpinfo.id, bpinfo.reseller_id, bpinfo.interval, bpinfo.type,
IF (bpinfo.fraud_use_reseller_rates > 0,
SUM(cdr.source_reseller_cost),
SUM(cdr.source_customer_cost)) as interval_cost,
bpinfo.interval_limit,
bpinfo.interval_lock,
bpinfo.interval_notify,
bpinfo.fraud_use_reseller_rates as use_reseller_rates
FROM (
SELECT c.id, n.reseller_id, bp.fraud_use_reseller_rates, i.interval,
IF (i.interval = 'month',
IF (cfp.fraud_interval_limit > 0,
'account_limit', 'profile_limit'),
IF (cfp.fraud_daily_limit > 0,
'account_limit', 'profile_limit')
) AS type,
IF (i.interval = 'month',
IF (cfp.fraud_interval_limit > 0,
cfp.fraud_interval_limit, bp.fraud_interval_limit),
IF (cfp.fraud_daily_limit > 0,
cfp.fraud_daily_limit, bp.fraud_daily_limit)
) AS interval_limit,
IF (i.interval = 'month',
IF (cfp.fraud_interval_limit > 0,
cfp.fraud_interval_lock, bp.fraud_interval_lock),
IF (cfp.fraud_daily_limit > 0,
cfp.fraud_daily_lock, bp.fraud_daily_lock)
) AS interval_lock,
IF (i.interval = 'month',
IF (cfp.fraud_interval_limit > 0,
cfp.fraud_interval_notify, bp.fraud_interval_notify),
IF (cfp.fraud_daily_limit > 0,
cfp.fraud_daily_notify, bp.fraud_daily_notify)
) AS interval_notify
FROM (SELECT IF("day" = 'month','month','day') AS 'interval') i,
billing.contracts c
INNER JOIN (
select ca.id as contract_id,
(select bma.id from billing.billing_mappings bma where
bma.contract_id=ca.id and
(bma.end_date >= NOW() OR bma.end_date IS NULL)
AND (bma.start_date <= NOW() OR bma.start_date IS NULL) order
by bma.start_date desc, bma.id desc limit 1) as id from
billing.contracts ca
) AS bm_actual ON bm_actual.contract_id = c.id
JOIN billing.billing_mappings bm ON bm.id = bm_actual.id
JOIN billing.billing_profiles bp ON bp.id = bm.billing_profile_id
JOIN billing.contacts n ON n.id = c.contact_id
JOIN billing.resellers r ON r.id = n.reseller_id
LEFT JOIN billing.contract_fraud_preferences cfp
ON cfp.contract_id = c.id
WHERE c.status = 'active'
HAVING interval_limit > 0
) AS bpinfo
JOIN accounting.cdr ON cdr.source_account_id = bpinfo.id
WHERE CASE WHEN bpinfo.interval = 'month'
THEN cdr.start_time
BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(NOW(), '%Y-%m-01 00:00:00'))
AND UNIX_TIMESTAMP(DATE_FORMAT(NOW() + INTERVAL 1 MONTH,
'%Y-%m-01 00:00:00'))-1
ELSE cdr.start_time
BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00'))
AND UNIX_TIMESTAMP(DATE_FORMAT(NOW() + INTERVAL 1 DAY,
'%Y-%m-%d 00:00:00'))-1
END
GROUP BY bpinfo.id
HAVING interval_cost >= interval_limit;
thanks®ards,
rene
</pre>
</blockquote>
<br>
<br>
<div class="moz-signature">-- <br>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Documento sin título</title>
<style>
body{
font-family: Helvetica, "Trebuchet MS", Arial, sans-serif;
font-size: 14px;
line-height: 14pt;
}
a {
color: #ff2600;
/*text-decoration: none;*/
}
p-viejo{
color:#b1b2b5;
}
.firma{
color:#618094;
}
.nombre{
color: #000000;
font-size: 18px;
font-weight: normal;
}
.letra{
color:#ff2600;
/*font-weight:bold;*/
}
.direccion{
color:#57585a;
font-size:14
px;
}
.aviso{
border-top:1px #48bbf2 solid;
color:#6f7072;
font-size:10px;
margin-top:20px;
padding-top:10px;
}
</style>
<p class="firma"> <span class="nombre">Alejandro Grijalba
Martínez</span><br>
<span style="color: #666666">Departamento de Sistemas</span><br>
</p>
<p><img src="cid:part1.B663A4A5.3A1E4332@innovasur.es" width="188"></p>
<span class="letra">E</span> <span style=""><a class="moz-txt-link-abbreviated" href="mailto:agrijalba@innovasur.com">agrijalba@innovasur.com</a></span><br>
<span class="letra">D</span> <span style="">
Calle Sierra Morena 12 A Edif.CTSAII. Pta. 2º, Oficina 9. 23620
Mengíbar (Jaén) / España.
<br>
<span class="letra">T</span> <a href="tel:+34953267919">+34 953
267 919</a><br>
<br>
<span class="letra"><a href="http://www.innovasur.com">www.innovasur.com</a></span>
</span></div>
</body>
</html>