espoCRM V6 SQL Sample

[item]

Hello,

who can make this sql to v6 espocrm :

As you see.. some data is Hard coded .. .other come from search front-end

$sql = "SELECT c.national_number as cNationalNumber, s.type as sType, m.name as mName, m.date_start as mDateStart,
MOD(TIME_FORMAT(SEC_TO_TIME(s.seconds), '%H'),24) as hours,
TIME_FORMAT(SEC_TO_TIME(s.seconds), '%i') as minutes,
a.name as aName, a.billing_address_postal_code as aPostalCode, a.billing_address_city as aCity, cm.status as cmAccept, c.first_name as cFirstName, c.last_name as cLastName
FROM meeting m
LEFT JOIN contact_meeting cm ON cm.meeting_id = m.id
LEFT JOIN contact c on c.id = cm.contact_id
LEFT JOIN specification s ON m.id = s.entity_id
LEFT JOIN account a on a.id = m.account_id
WHERE m.deleted='0' AND cm.deleted='0' AND c.deleted='0' AND s.deleted='0'";
// isNotLinked, currentMonth, ...

foreach($data->where as $key){

if ($key->type == 'in')
{
$sql .= " AND ( m." .Util::toUnderScore($key->attribute) ." IN ( '" .implode("', '", $key->value) ."' ) )";
}

if ($key->type == 'between'){

$sql .= " AND ( m." .Util::toUnderScore($key->attribute) ." BETWEEN '" .$key->value[0] ."' AND '" .$key->value[1] ."' )";
}
}
$sql .= " GROUP BY c.national_number, s.type, m.name,m.type, s.seconds, m.date_start, hours, minutes, a.name, a.billing_address_postal_code , a.billing_address_city, cm.status, c.first_name, c.last_name ";
$sql .= "ORDER BY m.date_start ASC;"; 

Thanks

@Yuri ?

[item]

Or this :

$sql = "SELECT c.practrice, c.name, c.national_number as nationalNumber, ROUND(SUM(c.amount_total),2) as amount, i.local_number as localNumber, i.name as nameInsurance, YEAR(date_time) as year, month(date_time) as month,
IF (local_number='0', p.address_street , i.billing_address_street ) as addressStreet,
IF (local_number='0', p.address_postal_code , i.billing_address_postal_code) as postalCode,
IF (local_number='0', p.address_city , i.billing_address_city) as city
FROM care as c
JOIN insurance as i ON i.local_number = c.insurance_local_number
JOIN patient as p on p.national_number = c.national_number
WHERE c.on_error='0' AND c.deleted='0' AND p.deleted='0' AND i.deleted='0' AND c.insurance_number < '100'
group by c.practrice, c.name, c.national_number, i.local_number, i.name, YEAR(date_time), month(date_time), addressStreet, postalCode, city
order by YEAR(date_time) ASC, MONTH(date_time) ASC"; 

[yuri]

$query = $entityManager
->getQueryBuilder()
->select() // SELECT query builder
->from('Care') // from entity type
->select( // what to select
    [
        'practrice',
        'name',
        'nationalNumber',
        [
            'IF:(EQUAL:(localNumber, 0), addressStreet, billingAddressStreet)', // complex expression (see documentation)
            'addressStreet' // alias
        ],
    ]
)
->join(
    'Insurance', // entity type
    'i', // alias
    [
        // add ON conditions here
    ],
)
->where([
   // add where conditions here
])
->groupBy('practrice')
->groupBy('name')
->order('YEAR:(dateTime)', 'ASC')
->order('MONTH:(dateTime)', 'ASC')
->build();

$sth = $entityManager
    ->getQueryExecutor()
    ->execute($query);

while ($row = $sth->fetch()) {
    //
} 

Complex expressions: https://docs.espocrm.com/user-guide/...x-expressions/