function get_standard_company($gc)
{
//会社取得数
$cnt = 3;
$gc->arr_company = null;
$gc->arr_company_kbn = null;
$it = 0;
if ($_POST["relocation_flg"] == 1) {
$it++;
}
if ($_POST["chintai_flg"] == 1) {
$it++;
}
if ($_POST["sublease_flg"] == 1) {
$it++;
}
if ($it != 3) {
//リロケーション
if ($_POST["relocation_flg"] == 1) {
$sql = "SELECT";
$sql .= " company_no,";
$sql .= " company_nm,";
$sql .= " company_kbn,";
$sql .= " kakuritsu,";
$sql .= " chintai_flg,";
$sql .= " chintai_ritu,";
$sql .= " relocation_flg,";
$sql .= " relocation_ritu,";
$sql .= " sublease_flg,";
$sql .= " sublease_ritu,";
$sql .= " comment ";
$sql .= "FROM";
$sql .= " m_company c ";
$sql .= "WHERE";
$sql .= " c.keisai_flg = 1 AND";
$sql .= " c.plan_flg != 1 AND ";
if ($gc->buken_sbt != 2) {
// 一棟アパート・マンション選択されない場合、表示しない
$sql .= " c.display_flg != 2 AND";
if ($gc->input_val < 40) {
//一棟アパート・マンションは除外、さらに延床面積が40平米以上のみ表示
$sql .= " c.display_flg != 5 AND";
}
} else {
$sql .= " c.display_flg != 4 AND";
$sql .= " c.display_flg != 5 AND";
}
// ご希望サービス賃貸管理、表示しない
$sql .= " c.display_flg != 1 AND";
if ($gc->chikunensu > 15 || $gc->input_val < 35) {
//築年数が15年以下で延床面積が35㎡以上の場合のみ表示
$sql .= " c.display_flg != 3 AND";
}
if ($gc->buken_sbt != 1 || $gc->chikunensu > 15 || $gc->input_val < 35) {
//築年数が15年以下で延床面積が35㎡以上の分譲マンションの場合のみ表示
$sql .= " c.display_flg != 6 AND";
}
$sql .= " c.relocation_flg = 1 AND ";
$sql .= " (";
$sql .= " (";
$sql .= " (c.jyougen - " . $gc->tesuryou . ") >= IFNULL((";
$sql .= " SELECT";
$sql .= " SUM(tesuryou) AS sum_tesuryou ";
$sql .= " FROM";
$sql .= " t_item ti ";
$sql .= " INNER JOIN";
$sql .= " t_item_company tc ";
$sql .= " ON";
$sql .= " ti.item_no = tc.item_no ";
$sql .= " WHERE";
$sql .= " ti.mukou_flg = 0 AND";
$sql .= " DATE_FORMAT(ti.moushikomibi,'%Y%m%d') BETWEEN '" . date("Ym01", time()) . "' AND '" . date("Ymt", time()) . "' AND";
$sql .= " tc.company_no = c.company_no";
$sql .= " ), 0)";
$sql .= " ) OR ";
$sql .= " c.jyougen = 0";
$sql .= " ) AND";
$sql .= " EXISTS (";
$sql .= " SELECT * FROM";
$sql .= " m_company_area ca";
$sql .= " WHERE";
$sql .= " ca.company_no = c.company_no AND";
$sql .= " ca.shikucyouson_no = " . $gc->shikucyouson_no . ")";
$rs = $gc->db->query($sql);
while ($rs->eof == false) {
//大手＝1 地場＝2
$company_kbn = $rs->fields["company_kbn"];
$wk_r["company_no"] = $rs->fields["company_no"];
$wk_r["kakuritsu"] = $rs->fields["relocation_ritu"];
$company_r[] = $wk_r;
$rs->move_next();
}
$rs->free_result();
//確率で会社取得
arr_company($gc, $company_r, 3, "relocation");
//賃貸件数
$gc->r_cnt = count($gc->arr_company);
}
//賃貸
if ($_POST["chintai_flg"] == 1) {
$sql = "SELECT";
$sql .= " company_no,";
$sql .= " company_nm,";
$sql .= " company_kbn,";
$sql .= " kakuritsu,";
$sql .= " chintai_flg,";
$sql .= " chintai_ritu,";
$sql .= " relocation_flg,";
$sql .= " relocation_ritu,";
$sql .= " sublease_flg,";
$sql .= " sublease_ritu,";
$sql .= " comment ";
$sql .= "FROM";
$sql .= " m_company c ";
$sql .= "WHERE";
$sql .= " c.keisai_flg = 1 AND";
$sql .= " c.plan_flg != 1 AND ";
if ($gc->buken_sbt != 2) {
// 一棟アパート・マンション選択されない場合、表示しない
$sql .= " c.display_flg != 2 AND";
if ($gc->input_val < 40) {
//一棟アパート・マンションは除外、さらに延床面積が40平米以上のみ表示
$sql .= " c.display_flg != 5 AND";
}
} else {
$sql .= " c.display_flg != 4 AND";
$sql .= " c.display_flg != 5 AND";
}
if ($_POST["relocation_flg"] == 1 || $_POST["sublease_flg"] == 1) {
// ご希望サービス賃貸管理のみ、表示しない
$sql .= " c.display_flg != 1 AND";
}
if ($gc->chikunensu > 15 || $gc->input_val < 35) {
//築年数が15年以下で延床面積が35㎡以上の場合のみ表示
$sql .= " c.display_flg != 3 AND";
}
if ($gc->buken_sbt != 1 || $gc->chikunensu > 15 || $gc->input_val < 35) {
//築年数が15年以下で延床面積が35㎡以上の分譲マンションの場合のみ表示
$sql .= " c.display_flg != 6 AND";
}
$sql .= " c.chintai_flg = 1 AND ";
if (count($gc->arr_company) > 0) {
for ($i = 0; $i < count($gc->arr_company); $i++) {
$sql .= " c.company_no != " . $gc->arr_company[$i] . " AND ";
}
}
$sql .= " (";
$sql .= " (";
$sql .= " (c.jyougen - " . $gc->tesuryou . ") >= IFNULL((";
$sql .= " SELECT";
$sql .= " SUM(tesuryou) AS sum_tesuryou ";
$sql .= " FROM";
$sql .= " t_item ti ";
$sql .= " INNER JOIN";
$sql .= " t_item_company tc ";
$sql .= " ON";
$sql .= " ti.item_no = tc.item_no ";
$sql .= " WHERE";
$sql .= " ti.mukou_flg = 0 AND";
$sql .= " DATE_FORMAT(ti.moushikomibi,'%Y%m%d') BETWEEN '" . date("Ym01", time()) . "' AND '" . date("Ymt", time()) . "' AND";
$sql .= " tc.company_no = c.company_no";
$sql .= " ), 0)";
$sql .= " ) OR ";
$sql .= " c.jyougen = 0";
$sql .= " ) AND";
$sql .= " EXISTS (";
$sql .= " SELECT * FROM";
$sql .= " m_company_area ca";
$sql .= " WHERE";
$sql .= " ca.company_no = c.company_no AND";
$sql .= " ca.shikucyouson_no = " . $gc->shikucyouson_no . ")";
$rs = $gc->db->query($sql);
while ($rs->eof == false) {
//大手＝1 地場＝2
$company_kbn = $rs->fields["company_kbn"];
$wk_c["company_no"] = $rs->fields["company_no"];
$wk_c["kakuritsu"] = $rs->fields["chintai_ritu"];
$company_c[] = $wk_c;
$rs->move_next();
}
$rs->free_result();
//確率で会社取得
arr_company($gc, $company_c, 3, "chintai");
//賃貸件数
$gc->c_cnt = count($gc->arr_company) - $gc->r_cnt;
}
//サブリース
if ($_POST["sublease_flg"] == 1) {
$sql = "SELECT";
$sql .= " company_no,";
$sql .= " company_nm,";
$sql .= " company_kbn,";
$sql .= " kakuritsu,";
$sql .= " chintai_flg,";
$sql .= " chintai_ritu,";
$sql .= " relocation_flg,";
$sql .= " relocation_ritu,";
$sql .= " sublease_flg,";
$sql .= " sublease_ritu,";
$sql .= " comment ";
$sql .= "FROM";
$sql .= " m_company c ";
$sql .= "WHERE";
$sql .= " c.keisai_flg = 1 AND";
$sql .= " c.plan_flg != 1 AND ";
if ($gc->buken_sbt != 2) {
// 一棟アパート・マンション選択されない場合、表示しない
$sql .= " c.display_flg != 2 AND";
if ($gc->input_val < 40) {
//一棟アパート・マンションは除外、さらに延床面積が40平米以上のみ表示
$sql .= " c.display_flg != 5 AND";
}
} else {
$sql .= " c.display_flg != 4 AND";
$sql .= " c.display_flg != 5 AND";
}
// ご希望サービス賃貸管理、表示しない
$sql .= " c.display_flg != 1 AND";
if ($gc->chikunensu > 15 || $gc->input_val < 35) {
//築年数が15年以下で延床面積が35㎡以上の場合のみ表示
$sql .= " c.display_flg != 3 AND";
}
if ($gc->buken_sbt != 1 || $gc->chikunensu > 15 || $gc->input_val < 35) {
//築年数が15年以下で延床面積が35㎡以上の分譲マンションの場合のみ表示
$sql .= " c.display_flg != 6 AND";
}
$sql .= " c.sublease_flg = 1 AND ";
if (count($gc->arr_company) > 0) {
for ($i = 0; $i < count($gc->arr_company); $i++) {
$sql .= " c.company_no != " . $gc->arr_company[$i] . " AND ";
}
}
$sql .= " (";
$sql .= " (";
$sql .= " (c.jyougen - " . $gc->tesuryou . ") >= IFNULL((";
$sql .= " SELECT";
$sql .= " SUM(tesuryou) AS sum_tesuryou ";
$sql .= " FROM";
$sql .= " t_item ti ";
$sql .= " INNER JOIN";
$sql .= " t_item_company tc ";
$sql .= " ON";
$sql .= " ti.item_no = tc.item_no ";
$sql .= " WHERE";
$sql .= " ti.mukou_flg = 0 AND";
$sql .= " DATE_FORMAT(ti.moushikomibi,'%Y%m%d') BETWEEN '" . date("Ym01", time()) . "' AND '" . date("Ymt", time()) . "' AND";
$sql .= " tc.company_no = c.company_no";
$sql .= " ), 0)";
$sql .= " ) OR ";
$sql .= " c.jyougen = 0";
$sql .= " ) AND";
$sql .= " EXISTS (";
$sql .= " SELECT * FROM";
$sql .= " m_company_area ca";
$sql .= " WHERE";
$sql .= " ca.company_no = c.company_no AND";
$sql .= " ca.shikucyouson_no = " . $gc->shikucyouson_no . ")";
$rs = $gc->db->query($sql);
while ($rs->eof == false) {
//大手＝1 地場＝2
$company_kbn = $rs->fields["company_kbn"];
$wk_s["company_no"] = $rs->fields["company_no"];
$wk_s["kakuritsu"] = $rs->fields["sublease_ritu"];
$company_s[] = $wk_s;
$rs->move_next();
}
$rs->free_result();
//確率で会社取得
arr_company($gc, $company_s, 3, "sublease");
//サブリース件数
$gc->s_cnt = count($gc->arr_company) - $gc->r_cnt - $gc->c_cnt;
}
} else {
//リロケーション
if ($_POST["relocation_flg"] == 1) {
$sql = "SELECT";
$sql .= " company_no,";
$sql .= " company_nm,";
$sql .= " company_kbn,";
$sql .= " kakuritsu,";
$sql .= " chintai_flg,";
$sql .= " chintai_ritu,";
$sql .= " relocation_flg,";
$sql .= " relocation_ritu,";
$sql .= " sublease_flg,";
$sql .= " sublease_ritu,";
$sql .= " comment ";
$sql .= "FROM";
$sql .= " m_company c ";
$sql .= "WHERE";
$sql .= " c.keisai_flg = 1 AND";
$sql .= " c.plan_flg != 1 AND ";
if ($gc->buken_sbt != 2) {
// 一棟アパート・マンション選択されない場合、表示しない
$sql .= " c.display_flg != 2 AND";
if ($gc->input_val < 40) {
//一棟アパート・マンションは除外、さらに延床面積が40平米以上のみ表示
$sql .= " c.display_flg != 5 AND";
}
} else {
$sql .= " c.display_flg != 4 AND";
$sql .= " c.display_flg != 5 AND";
}
// ご希望サービス賃貸管理、表示しない
$sql .= " c.display_flg != 1 AND";
if ($gc->chikunensu > 15 || $gc->input_val < 35) {
//築年数が15年以下で延床面積が35㎡以上の場合のみ表示
$sql .= " c.display_flg != 3 AND";
}
if ($gc->buken_sbt != 1 || $gc->chikunensu > 15 || $gc->input_val < 35) {
//築年数が15年以下で延床面積が35㎡以上の分譲マンションの場合のみ表示
$sql .= " c.display_flg != 6 AND";
}
$sql .= " c.relocation_flg = 1 AND ";
$sql .= " (";
$sql .= " (";
$sql .= " (c.jyougen - " . $gc->tesuryou . ") >= IFNULL((";
$sql .= " SELECT";
$sql .= " SUM(tesuryou) AS sum_tesuryou ";
$sql .= " FROM";
$sql .= " t_item ti ";
$sql .= " INNER JOIN";
$sql .= " t_item_company tc ";
$sql .= " ON";
$sql .= " ti.item_no = tc.item_no ";
$sql .= " WHERE";
$sql .= " ti.mukou_flg = 0 AND";
$sql .= " DATE_FORMAT(ti.moushikomibi,'%Y%m%d') BETWEEN '" . date("Ym01", time()) . "' AND '" . date("Ymt", time()) . "' AND";
$sql .= " tc.company_no = c.company_no";
$sql .= " ), 0)";
$sql .= " ) OR ";
$sql .= " c.jyougen = 0";
$sql .= " ) AND";
$sql .= " EXISTS (";
$sql .= " SELECT * FROM";
$sql .= " m_company_area ca";
$sql .= " WHERE";
$sql .= " ca.company_no = c.company_no AND";
$sql .= " ca.shikucyouson_no = " . $gc->shikucyouson_no . ")";
$rs = $gc->db->query($sql);
while ($rs->eof == false) {
//大手＝1 地場＝2
$company_kbn = $rs->fields["company_kbn"];
$wk_r["company_no"] = $rs->fields["company_no"];
$wk_r["kakuritsu"] = $rs->fields["relocation_ritu"];
$company_r[] = $wk_r;
$rs->move_next();
}
$rs->free_result();
//確率で会社取得
arr_company($gc, $company_r, $cnt, "relocation");
if (count($gc->arr_company) == 3) {
$cnt--;
}
//賃貸件数
$gc->r_cnt = count($gc->arr_company);
}
//賃貸
if ($_POST["chintai_flg"] == 1) {
$sql = "SELECT";
$sql .= " company_no,";
$sql .= " company_nm,";
$sql .= " company_kbn,";
$sql .= " kakuritsu,";
$sql .= " chintai_flg,";
$sql .= " chintai_ritu,";
$sql .= " relocation_flg,";
$sql .= " relocation_ritu,";
$sql .= " sublease_flg,";
$sql .= " sublease_ritu,";
$sql .= " comment ";
$sql .= "FROM";
$sql .= " m_company c ";
$sql .= "WHERE";
$sql .= " c.keisai_flg = 1 AND";
$sql .= " c.plan_flg != 1 AND ";
if ($gc->buken_sbt != 2) {
// 一棟アパート・マンション選択されない場合、表示しない
$sql .= " c.display_flg != 2 AND";
if ($gc->input_val < 40) {
//一棟アパート・マンションは除外、さらに延床面積が40平米以上のみ表示
$sql .= " c.display_flg != 5 AND";
}
} else {
$sql .= " c.display_flg != 4 AND";
$sql .= " c.display_flg != 5 AND";
}
if ($_POST["relocation_flg"] == 1 || $_POST["sublease_flg"] == 1) {
// ご希望サービス賃貸管理のみ、表示しない
$sql .= " c.display_flg != 1 AND";
}
if ($gc->chikunensu > 15 || $gc->input_val < 35) {
//築年数が15年以下で延床面積が35㎡以上の場合のみ表示
$sql .= " c.display_flg != 3 AND";
}
if ($gc->buken_sbt != 1 || $gc->chikunensu > 15 || $gc->input_val < 35) {
//築年数が15年以下で延床面積が35㎡以上の分譲マンションの場合のみ表示
$sql .= " c.display_flg != 6 AND";
}
$sql .= " c.chintai_flg = 1 AND ";
if (count($gc->arr_company) > 0) {
for ($i = 0; $i < count($gc->arr_company); $i++) {
$sql .= " c.company_no != " . $gc->arr_company[$i] . " AND ";
}
}
$sql .= " (";
$sql .= " (";
$sql .= " (c.jyougen - " . $gc->tesuryou . ") >= IFNULL((";
$sql .= " SELECT";
$sql .= " SUM(tesuryou) AS sum_tesuryou ";
$sql .= " FROM";
$sql .= " t_item ti ";
$sql .= " INNER JOIN";
$sql .= " t_item_company tc ";
$sql .= " ON";
$sql .= " ti.item_no = tc.item_no ";
$sql .= " WHERE";
$sql .= " ti.mukou_flg = 0 AND";
$sql .= " DATE_FORMAT(ti.moushikomibi,'%Y%m%d') BETWEEN '" . date("Ym01", time()) . "' AND '" . date("Ymt", time()) . "' AND";
$sql .= " tc.company_no = c.company_no";
$sql .= " ), 0)";
$sql .= " ) OR ";
$sql .= " c.jyougen = 0";
$sql .= " ) AND";
$sql .= " EXISTS (";
$sql .= " SELECT * FROM";
$sql .= " m_company_area ca";
$sql .= " WHERE";
$sql .= " ca.company_no = c.company_no AND";
$sql .= " ca.shikucyouson_no = " . $gc->shikucyouson_no . ")";
$rs = $gc->db->query($sql);
while ($rs->eof == false) {
//大手＝1 地場＝2
$company_kbn = $rs->fields["company_kbn"];
$wk_c["company_no"] = $rs->fields["company_no"];
$wk_c["kakuritsu"] = $rs->fields["chintai_ritu"];
$company_c[] = $wk_c;
$rs->move_next();
}
$rs->free_result();
//確率で会社取得
arr_company($gc, $company_c, $cnt, "chintai");
if (count($gc->arr_company) > 3) {
$cnt--;
}
//賃貸件数
$gc->c_cnt = count($gc->arr_company) - $gc->r_cnt;
}
//サブリース
if ($_POST["sublease_flg"] == 1) {
$sql = "SELECT";
$sql .= " company_no,";
$sql .= " company_nm,";
$sql .= " company_kbn,";
$sql .= " kakuritsu,";
$sql .= " chintai_flg,";
$sql .= " chintai_ritu,";
$sql .= " relocation_flg,";
$sql .= " relocation_ritu,";
$sql .= " sublease_flg,";
$sql .= " sublease_ritu,";
$sql .= " comment ";
$sql .= "FROM";
$sql .= " m_company c ";
$sql .= "WHERE";
$sql .= " c.keisai_flg = 1 AND";
$sql .= " c.plan_flg != 1 AND ";
if ($gc->buken_sbt != 2) {
// 一棟アパート・マンション選択されない場合、表示しない
$sql .= " c.display_flg != 2 AND";
if ($gc->input_val < 40) {
//一棟アパート・マンションは除外、さらに延床面積が40平米以上のみ表示
$sql .= " c.display_flg != 5 AND";
}
} else {
$sql .= " c.display_flg != 4 AND";
$sql .= " c.display_flg != 5 AND";
}
// ご希望サービス賃貸管理、表示しない
$sql .= " c.display_flg != 1 AND";
if ($gc->chikunensu > 15 || $gc->input_val < 35) {
//築年数が15年以下で延床面積が35㎡以上の場合のみ表示
$sql .= " c.display_flg != 3 AND";
}
if ($gc->buken_sbt != 1 || $gc->chikunensu > 15 || $gc->input_val < 35) {
//築年数が15年以下で延床面積が35㎡以上の分譲マンションの場合のみ表示
$sql .= " c.display_flg != 6 AND";
}
$sql .= " c.sublease_flg = 1 AND ";
if (count($gc->arr_company) > 0) {
for ($i = 0; $i < count($gc->arr_company); $i++) {
$sql .= " c.company_no != " . $gc->arr_company[$i] . " AND ";
}
}
$sql .= " (";
$sql .= " (";
$sql .= " (c.jyougen - " . $gc->tesuryou . ") >= IFNULL((";
$sql .= " SELECT";
$sql .= " SUM(tesuryou) AS sum_tesuryou ";
$sql .= " FROM";
$sql .= " t_item ti ";
$sql .= " INNER JOIN";
$sql .= " t_item_company tc ";
$sql .= " ON";
$sql .= " ti.item_no = tc.item_no ";
$sql .= " WHERE";
$sql .= " ti.mukou_flg = 0 AND";
$sql .= " DATE_FORMAT(ti.moushikomibi,'%Y%m%d') BETWEEN '" . date("Ym01", time()) . "' AND '" . date("Ymt", time()) . "' AND";
$sql .= " tc.company_no = c.company_no";
$sql .= " ), 0)";
$sql .= " ) OR ";
$sql .= " c.jyougen = 0";
$sql .= " ) AND";
$sql .= " EXISTS (";
$sql .= " SELECT * FROM";
$sql .= " m_company_area ca";
$sql .= " WHERE";
$sql .= " ca.company_no = c.company_no AND";
$sql .= " ca.shikucyouson_no = " . $gc->shikucyouson_no . ")";
$rs = $gc->db->query($sql);
while ($rs->eof == false) {
//大手＝1 地場＝2
$company_kbn = $rs->fields["company_kbn"];
$wk_s["company_no"] = $rs->fields["company_no"];
$wk_s["kakuritsu"] = $rs->fields["sublease_ritu"];
$company_s[] = $wk_s;
$rs->move_next();
}
$rs->free_result();
//確率で会社取得
arr_company($gc, $company_s, $cnt, "sublease");
//サブリース件数
$gc->s_cnt = count($gc->arr_company) - $gc->r_cnt - $gc->c_cnt;
}
}
//print_r($gc->arr_company);
}
function get_light_company($gc){
//-----------------------------------------------------
// ライトプラン
//-----------------------------------------------------
//-----------------------------------------------------
// 条件
// １．月の有効手数料が限度額の範囲以内
// ２．対応地区
// ３．掲載中
// ４．利用目的により
//-----------------------------------------------------
$sql = "SELECT";
$sql.= " c.company_no,";
$sql.= " company_nm,";
$sql.= " company_kbn,";
$sql.= " url,";
$sql.= " kakuritsu,";
$sql.= " chintai_flg,";
$sql.= " chintai_ritu,";
$sql.= " relocation_flg,";
$sql.= " relocation_ritu,";
$sql.= " sublease_flg,";
$sql.= " sublease_ritu,";
$sql.= " light_ritu,";
$sql.= " comment ";
$sql.= "FROM";
$sql.= " m_company c ";
$sql.= "INNER JOIN ";
$sql.= "(";
$sql.= "SELECT * FROM ";
$sql.= " m_company_area mc ";
$sql.= "WHERE ";
$sql.= " mc.shikucyouson_no = ".$gc->shikucyouson_no.") mca ";
$sql.= "ON c.company_no = mca.company_no ";
$sql.= "WHERE";
$sql.= " c.keisai_flg = 1 AND";
$sql.= " c.plan_flg != 2 AND";
//物件種別により抽出する
if($gc->buken_sbt == 1){
$sql.= " c.buken_mansion_flg = 1 AND";
}
if($gc->buken_sbt == 2){
$sql.= " c.buken_ittou_flg = 1 AND";
}
if($gc->buken_sbt == 3){
$sql.= " c.buken_kodate_flg = 1 AND";
}
if(($gc->chintai_flg != 1) || ($gc->sublease_flg == 1) || ($gc->relocation_flg == 1)){
//ご希望サービスの賃貸募集・賃貸管理を選択した場合のみ表示
$sql.= " c.display_flg != 1 AND";
}
if($gc->chikunensu > 15 || $gc->input_val < 35){
//築年数が15年以下で延床面積が35㎡以上の場合のみ表示
$sql.= " c.display_flg != 2 AND";
}
if($gc->input_val < 40){
//延床面積が40平米以上のみ表示
$sql.= " c.display_flg != 3 AND";
}
if(($gc->input_val < 10) || ($gc->input_val > 25)){
//延床面積10平米以上25平米以下の物件の場合のみ表示
$sql.= " c.display_flg != 5 AND";
}
if($gc->purpose_flg != 4){
//賃貸管理会社を探しているにチェックが入った場合のみ表示
$sql.= " c.display_flg != 6 AND";
}
$sql.= " (c.chintai_flg = 1 OR c.relocation_flg = 1 OR c.sublease_flg = 1) AND";
$sql.= " (";
$sql.= " (";
$sql.= " (c.jyougen - (case when c.kingaku_light>'0' then c.kingaku_light else mca.shikucyouson_kingaku end)) >= IFNULL((";
$sql.= " SELECT";
$sql.= " SUM(tesuryou) AS sum_tesuryou";
$sql.= " FROM";
$sql.= " t_tesuryou ti";
$sql.= " INNER JOIN";
$sql.= " t_item_company tc";
$sql.= " ON";
$sql.= " ti.item_no = tc.item_no AND tc.company_no = ti.company_no ";
$sql.= " INNER JOIN ( ";
$sql.= " SELECT * FROM";
$sql.= " m_company_area mc ";
$sql.= " WHERE";
$sql.= " mc.shikucyouson_no = ".$gc->shikucyouson_no.") mca ";
$sql.= " ON tc.company_no = mca.company_no ";
$sql.= " WHERE";
$sql.= " tc.c_mukou_flg = 0 AND";
$sql.= " DATE_FORMAT(ti.moushikomibi,'%Y%m%d') BETWEEN '".date("Ym01", time())."' AND '".date("Ymt", time())."' AND";
$sql.= " tc.company_no = c.company_no";
$sql.= " ), 0)";
$sql.= " ) OR";
$sql.= " c.jyougen = 0 ";
$sql.= " OR ";
$sql.= " c.kingaku_kotei > 0";
$sql.= " ) AND";
$sql.= " EXISTS (";
$sql.= " SELECT * FROM";
$sql.= " m_company_area ca";
$sql.= " WHERE";
$sql.= " ca.company_no = c.company_no AND";
$sql.= " ca.shikucyouson_no = ".$gc->shikucyouson_no.") ";
$sql.= "ORDER BY light_ritu DESC";
$rs = $gc->db->query($sql);
while ($rs->eof==false) {
$wk_r["company_no"] = $rs->fields["company_no"];
$wk_r["kakuritsu"] = $rs->fields["light_ritu"];
if($wk_r["kakuritsu"] < 1){
$wk_r["kakuritsu"] = 1;
}
$company_r[] = $wk_r;
$rs->move_next();
}
$rs->free_result();
$gc->arr_company = null;
//確率で会社取得
arr_company($gc, $company_r, 6, "light");
$sql = "SELECT SQL_CALC_FOUND_ROWS";
$sql.= " company_no ";
$sql.= "FROM";
$sql.= " m_company ";
$sql.= "WHERE";
if(count($gc->arr_company) > 0){
$sql.= " company_no IN (";
for ($i = 0; $i < count($gc->arr_company); $i++) {
if($i > 0){
$sql.= " ,";
}
$sql.= $gc->arr_company[$i];
}
$sql.= " ) ";
}else{
$sql.= " 0 = 1 ";
}
$rs = $gc->db->query($sql);
$gc->row_count = $gc->db->get_row_count();
}
function get_lightplan($posted) {
//-----------------------------------------------------
// ライトプラン
//-----------------------------------------------------
//-----------------------------------------------------
// 条件
// １．月の有効手数料が限度額の範囲以内
// ２．対応地区
// ３．掲載中
// ４．利用目的により
//-----------------------------------------------------
require_once(dirname(__FILE__)."/../../../init.inc.php");
require_once(C_INC_DIR_SSL."db_utils.php");
$db = connect_db();
$sql = "SELECT";
$sql.= " c.company_no,";
$sql.= " company_nm,";
$sql.= " company_kbn,";
$sql.= " url,";
$sql.= " kakuritsu,";
$sql.= " chintai_flg,";
$sql.= " chintai_ritu,";
$sql.= " relocation_flg,";
$sql.= " relocation_ritu,";
$sql.= " sublease_flg,";
$sql.= " sublease_ritu,";
$sql.= " light_ritu,";
$sql.= " comment ";
$sql.= "FROM";
$sql.= " m_company c ";
$sql.= "INNER JOIN ";
$sql.= "(";
$sql.= "SELECT * FROM ";
$sql.= " m_company_area mc ";
$sql.= "WHERE ";
$sql.= " mc.shikucyouson_no = :shikucyouson_no) mca ";
$sql.= "ON c.company_no = mca.company_no ";
$sql.= "WHERE";
$sql.= " c.keisai_flg = 1 AND";
$sql.= " c.plan_flg != 2 AND";
//物件種別により抽出する
if($posted["buken_sbt"] == 1){
$sql.= " c.buken_mansion_flg = 1 AND";
}
if($posted["buken_sbt"] == 2){
$sql.= " c.buken_ittou_flg = 1 AND";
}
if($posted["buken_sbt"] == 3){
$sql.= " c.buken_kodate_flg = 1 AND";
}
if(($posted["chintai_flg"] != 1) || ($posted["sublease_flg"] == 1) || ($posted["relocation_flg"] == 1)){
//ご希望サービスの賃貸募集・賃貸管理を選択した場合のみ表示
$sql.= " c.display_flg != 1 AND";
}
if($posted["chikunensu"] <= 15 && $posted["input_val"] >= 35){
}else{
//築年数が15年以下で延床面積が35㎡以上の場合のみ表示
$sql.= " c.display_flg != 2 AND";
}
if($posted["input_val"] < 40){
//延床面積が40平米以上のみ表示
$sql.= " c.display_flg != 3 AND";
}
if((($posted["chintai_flg"] == 1) || ($posted["sublease_flg"] != 1) || ($posted["relocation_flg"] == 1)) || (($posted["purpose_flg"] == 1) || ($posted["purpose_flg"] == 8))){
//ご希望のサービスのサブリースを選択した場合のみ表示
$sql.= " c.display_flg != 4 AND";
}
if(($posted["input_val"] >= 10) && ($posted["input_val"] <= 25)){
}else{
//延床面積10平米以上25平米以下の物件の場合のみ表示
$sql.= " c.display_flg != 5 AND";
}
if($posted["purpose_flg"] != 4){
//賃貸管理会社を探しているにチェックが入った場合のみ表示
$sql.= " c.display_flg != 6 AND";
}
if($posted["chikunensu"] > 20){
//築年数が20年以下の場合のみ表示
$sql.= " c.display_flg != 7 AND";
}
$sql.= " (c.chintai_flg = 1 OR c.relocation_flg = 1 OR c.sublease_flg = 1) AND";
$sql.= " (";
$sql.= " (";
$sql.= " (c.jyougen - (case when c.kingaku_light>'0' then c.kingaku_light else mca.shikucyouson_kingaku end)) >= IFNULL((";
$sql.= " SELECT";
$sql.= " SUM(tesuryou) AS sum_tesuryou";
$sql.= " FROM";
$sql.= " t_tesuryou ti";
$sql.= " INNER JOIN";
$sql.= " t_item_company tc";
$sql.= " ON";
$sql.= " ti.item_no = tc.item_no AND tc.company_no = ti.company_no ";
$sql.= " INNER JOIN ( ";
$sql.= " SELECT * FROM";
$sql.= " m_company_area mc ";
$sql.= " WHERE";
$sql.= " mc.shikucyouson_no = :shikucyouson_no) mca ";
$sql.= " ON tc.company_no = mca.company_no ";
$sql.= " WHERE";
$sql.= " tc.c_mukou_flg = 0 AND";
$sql.= " DATE_FORMAT(ti.moushikomibi,'%Y%m%d') BETWEEN '".date("Ym01", time())."' AND '".date("Ymt", time())."' AND";
$sql.= " tc.company_no = c.company_no";
$sql.= " ), 0)";
$sql.= " ) OR";
$sql.= " c.jyougen = 0 ";
$sql.= " OR ";
$sql.= " c.kingaku_kotei > 0";
$sql.= " ) AND";
$sql.= " EXISTS (";
$sql.= " SELECT * FROM";
$sql.= " m_company_area ca";
$sql.= " WHERE";
$sql.= " ca.company_no = c.company_no AND";
$sql.= " ca.shikucyouson_no = :shikucyouson_no) ";
$sql.= "ORDER BY light_ritu DESC";
$stmt = $db->prepare($sql);
$stmt->bindParam(":shikucyouson_no", $posted["shikucyouson_no"], PDO::PARAM_STR);
$stmt->execute();
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$wk_r["company_no"] = $row["company_no"];
$wk_r["kakuritsu"] = $row["light_ritu"];
if($wk_r["kakuritsu"] < 1){
$wk_r["kakuritsu"] = 1;
}
$company_r[] = $wk_r;
}
$stmt->closeCursor();
//確率で会社取得
$arr_company = arr_company($company_r, 6, "light"); // ???
$sql = "SELECT";
$sql.= " company_no,";
$sql.= " company_nm,";
$sql.= " company_kbn,";
$sql.= " e_mail,";
$sql.= " e_mail_2,";
$sql.= " e_mail_3,";
$sql.= " e_mail_4,";
$sql.= " e_mail_5,";
$sql.= " e_mail_6,";
$sql.= " e_mail_7,";
$sql.= " e_mail_8,";
$sql.= " e_mail_9,";
$sql.= " e_mail_10,";
$sql.= " url, ";
$sql.= " kakuritsu,";
$sql.= " chintai_flg,";
$sql.= " chintai_ritu,";
$sql.= " relocation_flg,";
$sql.= " relocation_ritu,";
$sql.= " sublease_flg,";
$sql.= " sublease_ritu,";
$sql.= " light_ritu,";
$sql.= " comment ";
$sql.= "FROM";
$sql.= " m_company ";
$sql.= "WHERE";
if(count($arr_company) > 0){
$sql.= " company_no IN (";
for ($i = 0; $i < count($arr_company); $i++) {
if($i > 0){
$sql.= " ,";
}
$sql.= array_keys($arr_company)[$i];
}
$sql.= " ) ";
}else{
$sql.= " 0 = 1 ";
}
$stmt = $db->query($sql);
$arr_companies_filtered = [];
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
unset($company);
$company = $row;
$company["sub_mails"] = [$row["e_mail_2"],
$row["e_mail_3"],
$row["e_mail_4"],
$row["e_mail_5"],
$row["e_mail_6"],
$row["e_mail_7"],
$row["e_mail_8"],
$row["e_mail_9"],
$row["e_mail_10"]];
$arr_companies_filtered[] = $company;
}
$stmt->closeCursor();
return $arr_companies_filtered;
}
