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){ //----------------------------------------------------- // ライトプラン //----------------------------------------------------- //----------------------------------------------------- // 条件 // 1.月の有効手数料が限度額の範囲以内 // 2.対応地区 // 3.掲載中 // 4.利用目的により //----------------------------------------------------- $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) { //----------------------------------------------------- // ライトプラン //----------------------------------------------------- //----------------------------------------------------- // 条件 // 1.月の有効手数料が限度額の範囲以内 // 2.対応地区 // 3.掲載中 // 4.利用目的により //----------------------------------------------------- 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; }