/**
* 対象売上リスト取得します。
* @param string $billing_segment_id 請求先セグメント
* @param string $sales_genka_type 原価種別
* @param string $media_company_id メディア企業ID
* @param string $target_month 月度
*/
public function &Result_AdminReportSalesSalesSalesReport_getSales($like = null)
{
$db =& $this->_core->getDB();
// 条件を作成
$where = " s.status = ? ";
$statusEnum = new SalesGenkaReportStatusEnum();
$param = array($statusEnum->getKey("確定"));
// 請求先セグメントID
if (isset($like["billing_segment_id"]) == true && -1 < intval($like["billing_segment_id"])) {
$billingSegmentId = $like['billing_segment_id'];
$where .= " and b.billing_segment_id=? ";
$param[] = $billingSegmentId;
}
// 案件ID
if (empty($like['anken_id']) == false) {
$ankenId = $like['anken_id'];
$where .= " and s.anken_id=? ";
$param[] = $ankenId;
}
// クライアントID
if (empty($like['client_id']) == false) {
$clientId = $like['client_id'];
$where .= " and s.client_id=? ";
$param[] = $clientId;
}
// 担当者名称
if (empty($like['tanto_name']) == false) {
$name = mb_strtolower($like['tanto_name']);
$where .= " and LOWER(s.tanto_consulting_name) like ? ";
$param[] = "%{$name}%";
}
// 月度
if (empty($like['str_target_month']) == false) {
$where .= ' and (s.target_month=?) ';
$param[] = $like['str_target_month'];
}
// 入金種別
if (0 < count($like['payment_type'])) {
$type = $like['payment_type'];
$where .= ' and (d.payment_type_id in (' . implode(",", $type) . ')) ';
}
// 売上原価種別
if (empty($like['sales_genka_type_id']) == false) {
$salesGenkaTypeId = $like['sales_genka_type_id'];
$where .= " and d.sales_genka_type_id=? ";
$param[] = $salesGenkaTypeId;
}
// SQL
$sql = <<<SQL
select
s.client_id,
s.client_name,
s.anken_id,
s.anken_name,
s.billing_company_id,
s.target_month,
s.tanto_consulting_name,
s.status,
d.sales_genka_type_id,
d.title,
d.sales_price,
d.sales_count,
d.total_sales,
d.payment_scheduled_date,
d.payment_type_id,
d.memo
from ald_anken_sales as s
inner join ald_anken_sales_detail as d
on s.anken_sales_id=d.anken_sales_id
inner join ald_billing_company b
on s.billing_company_id = b.billing_company_id
where
{$where}
order by s.anken_id, d.anken_sales_detail_id
SQL;
return $db->query($sql, $param);
}
