Здраствуйте. Помогите решить следующий вопрос. Есть следующий код | Код |
function detalizeForDays()
{
global $db,$core;
$sql = "SELECT
SUM(o.amount) AS `total_amount`,
SUM(oa1.amount) AS `cash`,
SUM(oa2.amount) AS `bonus_on`,
SUM(oa4.amount) AS `bonus_off`,
SUM(oa3.amount) AS `discount`,
oa5.amount as `percent`,
COUNT(DISTINCT o.id) AS `operation_count`,
DATE_FORMAT(o.date,'%d.%m.%Y') AS `dat`,
DATE_FORMAT(o.date, '%y.%m.%d') AS `date_format`,
DATE_FORMAT(o.date, '%d.%m.%Y') AS `date_format_rev`
FROM `operation` AS `o`
LEFT JOIN `operation_action` AS `oa1` ON oa1.operation_id=o.id AND oa1.type=4
LEFT JOIN `operation_action` AS `oa2` ON oa2.operation_id=o.id AND oa2.type=1
LEFT JOIN `operation_action` AS `oa4` ON oa4.operation_id=o.id AND oa4.type=2
LEFT JOIN `operation_action` AS `oa3` ON oa3.operation_id=o.id AND oa3.type=3
LEFT JOIN `operation_action` AS `oa5` ON oa5.operation_id=o.id AND oa5.type=8
WHERE (o.brand_id=:brand_id) AND (DATE(o.date) >= :date_begin AND DATE(o.date) <= :date_end)
GROUP BY `date_format`
ORDER BY `date_format` ASC;";
$sql = $db->prepare($sql);
$begin_date = $_POST['date_begin'];
$end_date = $_POST['date_end'];
$sql->bindParam(':date_begin',encodeDate($begin_date),PDO::PARAM_STR);
$sql->bindParam(':date_end',encodeDate($end_date),PDO::PARAM_STR);
$sql->bindParam(':brand_id',$core->user['id'],PDO::PARAM_INT);
$sql->execute();
//print_r($sql->errorInfo());
$data = $sql->fetchAll(PDO::FETCH_ASSOC);
$data = date_array_mergre(array($data,getNewCardsArrayDays(array($begin_date,$end_date))));
$res = "<table>
<tr style='background-color: rgb(000, 250, 000)';>
<th>Дата</th>
<th>Количество операций</th>
<th>Общая сумма операций</th>
<th>Оплата наличными</th>
<th>Скидка</th>
<th>Бонусов начисленно</th>
<th>Бонусов списано</th>
<th>Выдано карт</th>
<th> </th>
</tr>";
foreach ($data as $day)
{
$bonus_on=$day['cash']*$day['percent']/100 + $day['bonus_on'];
$res .='<tr style="background-color: rgb(000, 250, 000);">
<td>'.$day['dat'].'</td>
<td>'.$day['operation_count'].'</td>
<td>'.$day['total_amount'].'</td>
<td>'.$day['cash'].'</td>
<td>'.$day['discount'].'</td>
<td>'.$bonus_on.'</td>
<td>'.$day['bonus_off'].'</td>
<td>'.$day['cards_count'].'</td>
<td class="onDayUpTd up" onclick="return detalizeOnDay(\''.$day['dat'].'\',this);"></td>
</tr>';
}
$res .= "</table>";
print $res;
}
function getNewCardsArrayDays($time)
{
global $db,$core;
$sql = "SELECT
COUNT(c.id) AS `cards_count`,
DATE_FORMAT(c.date, '%y.%m.%d') AS `date_format`
FROM
`card` AS `c`
WHERE
c.group_id=:brand_id
AND
DATE(c.date) >=:date_begin
AND
DATE(c.date) <=:date_end
GROUP BY `date_format`
ORDER BY `date_format` ASC;
";
$sql = $db->prepare($sql);
$sql->bindParam(':date_begin',encodeDate($time[0]),PDO::PARAM_STR);
$sql->bindParam(':date_end',encodeDate($time[1]),PDO::PARAM_STR);
$sql->bindParam(':brand_id',$core->user['id'],PDO::PARAM_INT);
$sql->execute();
//print_r($sql->errorInfo());
return $sql->fetchAll(PDO::FETCH_ASSOC);
}
// merge array of date
function date_array_mergre($data)
{
$cnt = 0;
foreach ($data[0] as $k => $v)
{
if ($data[0][$k]['date_format'] == $data[1][$cnt]['date_format'])
{
$data[0][$k] = array_merge($data[0][$k],$data[1][$cnt]);
$cnt++;
}
}
return $data[0];
}
function encodeDate($s) {
return preg_replace("/([0-9]{2})\.([0-9]{2})\.([0-9]{4})/", "$3-$2-$1", $s);
}
|
Проблема в том что выводятся данные по картам не для всех brand_id. Ломаю голову над этим уже целый день. Если оба sql запроса я выполняю в одной функции. То все норм. Но как обьeдинить выборки sql запросов в одном массиве? Это сообщение отредактировал(а) MrDmitry - 28.7.2011, 22:13 |
Обьединение двух запросов 
Загрузка ...
