/**
* This uses a native query because you can't use the DATE() function in MySQL in a JPA query
*
* @param start startDate
* @param end endDate end date ( uses the whole day via DateUtil.getNextDay() )
* @param c Company
* @param campaign Campaign (optional)
* @param device Device (optional)
* @return
*/
@Override
public List<BluetoothFileSendSummary> getBluetoothFileSendSummaryFromBluetoothSend(
Date start, Date end, Company company, Campaign campaign, Device device) {
int count = 4;
HashMap<Integer, Object> parameters = new HashMap<Integer, Object>();
String query =
"SELECT DATE(event_date), file, count(*) "
+ "FROM bluetooth_send "
+ "WHERE send_status = 1 "
+ "AND company_id = ?1 "
+ "AND event_date BETWEEN ?2 AND ?3 ";
if (campaign != null) {
query += "AND campaign_id = ?" + count + " ";
parameters.put(count, campaign.getId());
count++;
}
if (device != null) {
query += "AND device_id = ?" + count + " ";
parameters.put(count, device.getId());
count++;
}
query += "GROUP BY file, DATE(event_date) ORDER BY DATE(event_date)";
Query q = em.createNativeQuery(query);
q.setParameter(1, company.getId());
q.setParameter(2, start);
q.setParameter(3, DateUtil.getNextDay(end));
for (Map.Entry<Integer, Object> entry : parameters.entrySet()) {
Integer position = entry.getKey();
Object param = entry.getValue();
q.setParameter(position, param);
}
List<BluetoothFileSendSummary> results = new ArrayList<BluetoothFileSendSummary>();
List<Object[]> result = q.getResultList();
for (Object[] object : result) {
Date eventDate = (Date) object[0];
String file = (String) object[1];
Long sendCount = (Long) object[2];
BluetoothFileSendSummary item = new BluetoothFileSendSummary();
item.setEventDate(eventDate);
item.setFile(file);
item.setSendCount(sendCount);
item.setCompany(company);
item.setCampaign(campaign);
item.setDevice(device);
results.add(item);
}
return results;
}
@Override
public List<BluetoothSend> getBluetoothSendInRangeByCampaignAndDevice(
Date start, Date end, Company company, Campaign campaign, Device device) {
String query = "SELECT s FROM BluetoothSend s WHERE s.company = ?1 ";
int paramCount = 1;
HashMap<Integer, Object> params = new HashMap<Integer, Object>();
if (campaign != null && campaign.getName() != null && !campaign.getName().isEmpty()) {
paramCount++;
query += " AND s.campaign = ?" + paramCount + " ";
params.put(paramCount, campaign);
}
if (device != null) {
paramCount++;
query += " AND s.device = ?" + paramCount + " ";
params.put(paramCount, device);
}
if (start != null) {
paramCount++;
query += " AND s.eventDate >= ?" + paramCount + " ";
params.put(paramCount, start);
}
if (end != null) {
paramCount++;
query += " AND s.eventDate <= ?" + paramCount + " ";
params.put(paramCount, DateUtil.getEndOfDay(end));
}
Query q = em.createQuery(query);
q.setParameter(1, company);
for (Map.Entry<Integer, Object> entry : params.entrySet()) {
Integer paramId = entry.getKey();
Object object = entry.getValue();
q.setParameter(paramId, object);
}
List<BluetoothSend> results = (List<BluetoothSend>) q.getResultList();
if (results.size() > 0) {
return results;
}
return null;
}