function changeProductStatus(LC_Page $objPage) { $objQuery = SC_Query_Ex::getSingletonInstance(); $objQuery->begin(); $n = array(); $n2 = array("auto_display_status" => 0); $objSql = new SC_SelectSql_Ex(); $objSql->setWhere("auto_display_status = 1"); $objSql->setWhere("del_flg = 0"); $s = "auto_display_end_date"; $e = "auto_display_start_date"; // 公開日による状態変更 // {$s} > NOW() 公開開始前 // {$e} < NOW() 公開終了後 // その他 公開中 $objQuery->update("dtb_products", $n, $objSql->getWhere(), $n, array("status" => "CASE WHEN {$s} > NOW() THEN 2 WHEN {$e} < NOW() THEN 2 ELSE 1 END", "auto_display_status" => "CASE WHEN {$e} < NOW() THEN 0 ELSE 1 END")); $objSql->setWhere("{$e} < NOW() "); $objQuery->update("dtb_products", $n, $objSql->getWhere(), $n2); $objQuery->commit(); if (GC_Utils_Ex::isFrontFunction()) { // 商品数量を再計算 $objDb = new SC_Helper_DB_Ex(); $objDb->sfCountCategory($objQuery); $objDb->sfCountMaker($objQuery); } }
/** * 検索条件のwhere文とかを取得 * * @return array */ public function lfGetSearchCondition(&$arrSearchData) { $searchCondition = parent::lfGetSearchCondition($arrSearchData); GC_Utils_Ex::gfPrintLog(print_r($this->arrForm, true), DEBUG_LOG_REALFILE); $objSql = new SC_SelectSql_Ex(); $objSql->setWhere($searchCondition['where']); $character_id = $this->arrForm['character_id']; if (SC_Utils_Ex::sfIsInt($character_id)) { $this->arrSearchData["character_id"] = $character_id; $objSql->setWhere("product_id IN (\n SELECT product_id\n FROM cp_dtb_product_characters\n INNER JOIN cp_dtb_character USING(character_id)\n WHERE character_id = ?\n AND cp_dtb_character.status = 1\n AND cp_dtb_character.del_flg = 0\n )"); $searchCondition['arrval'][] = intval($character_id); } $searchCondition['where'] = $objSql->getWhere(false); $searchCondition['where_for_count'] = $objSql->getWhere(false); return $searchCondition; }
/** * 開始行番号, 行数を指定して管理者データを取得する. * * @access private * @param integer $startno 開始行番号 * @return array 管理者データの連想配列 */ function getMemberData($startno) { $objSql = new SC_SelectSql_Ex(); $objSql->setSelect("SELECT member_id,name,department,login_id,authority,rank,work FROM dtb_member"); $objSql->setOrder("rank DESC"); $objSql->setWhere("del_flg <> 1 AND member_id <> " . ADMIN_ID); $objSql->setLimitOffset(MEMBER_PMAX, $startno); $objQuery =& SC_Query_Ex::getSingletonInstance(); $arrMemberData = $objQuery->getAll($objSql->getSql()); return $arrMemberData; }
/** * クエリを構築する. * * 検索条件のキーに応じた WHERE 句と, クエリパラメーターを構築する. * クエリパラメーターは, SC_FormParam の入力値から取得する. * * 構築内容は, 引数の $where 及び $arrValues にそれぞれ追加される. * * @param string $key 検索条件のキー * @param string $where 構築する WHERE 句 * @param array $arrValues 構築するクエリパラメーター * @param SC_FormParam $objFormParam SC_FormParam インスタンス * @param SC_FormParam $objDb SC_Helper_DB_Ex インスタンス * @return void */ public function buildQuery($key, &$where, &$arrValues, SC_FormParam &$objFormParam, SC_Helper_DB &$objDb) { $objSql = new SC_SelectSql_Ex(); $objSql->setWhere($where); $objQuery = new SC_Query_Ex("", false, true); $data = array(); $id = "product_id"; $extends = true; $value = $objFormParam->getValue($key); switch ($key) { case "search_orderby": $column = "update_date"; switch ($value) { default: case "date": $column = "auto_display_start_date"; } if ($objDb->sfColumnExists("dtb_products", $column)) { $this->order = $column; } break; case "search_character_id": $sub_query = $objQuery->getSql($id, "cp_dtb_product_characters", "character_id=?"); $objSql->setWhere("{$id} IN ({$sub_query})"); $arrValues[] = $value; break; case "search_auto_display_status": if (is_array($value)) { $data = $objSql->setItemTerm($value, "auto_display_status"); } elseif (is_string($value)) { $data = $objSql->setItemTerm((array) $value, "auto_display_status"); } break; case "search_auto_display_start_date_start_year": case "search_auto_display_start_date_end_year": case "search_auto_display_end_date_start_year": case "search_auto_display_end_date_end_year": case "search_spass_provide_start_date_start_year": case "search_spass_provide_start_date_end_year": case "search_spass_provide_end_date_start_year": case "search_spass_provide_end_date_end_year": $y = $objFormParam->getValue($key); $m = $objFormParam->getValue(str_replace("_year", "_month", $key)); $d = $objFormParam->getValue(str_replace("_year", "_day", $key)); switch ($key) { case "search_auto_display_start_date_start_year": $data = $objSql->selectTermRange($y, $m, $d, "", "", "", "auto_display_start_date"); break; case "search_auto_display_start_date_end_year": $data = $objSql->selectTermRange("", "", "", $y, $m, $d, "auto_display_start_date"); break; case "search_auto_display_end_date_start_year": $data = $objSql->selectTermRange($y, $m, $d, "", "", "", "auto_display_end_date"); break; case "search_auto_display_end_date_end_year": $data = $objSql->selectTermRange("", "", "", $y, $m, $d, "auto_display_end_date"); break; case "search_spass_provide_start_date_start_year": $data = $objSql->selectTermRange($y, $m, $d, "", "", "", "spass_provide_start_date"); break; case "search_search_spass_provide_start_date_end_year": $data = $objSql->selectTermRange("", "", "", $y, $m, $d, "spass_provide_start_date"); break; case "search_search_spass_provide_end_date_start_year": $data = $objSql->selectTermRange($y, $m, $d, "", "", "", "spass_provide_end_date"); break; case "search_search_spass_provide_end_date_end_year": $data = $objSql->selectTermRange("", "", "", $y, $m, $d, "spass_provide_end_date"); break; } break; default: $extends = false; parent::buildQuery($key, $where, $arrValues, $objFormParam, $objDb); } if ($extends) { foreach ($data as $d) { $arrValues[] = $d; } $where = $objSql->getWhere(); } }
public function setFromByIn($column, $append = true, $and = false, $case = "=") { $count = count($column); if ($append) { $objSql = new SC_SelectSql_Ex(); for ($i = 0; $i < $count; $i++) { $column = $column[$i]['column']; $value = $column[$i]['value']; if (strlen($column[$i]['value'])) { $objSql->setWhere("{$column} {$case} ?"); $this->arrWhereVal[] = $value; } $statement .= $column[$i]['column'] . ' >= ?'; $this->arrWhereVal[] = $column[$i]['value']; } $statement = $objSql->getWhere(false); if (strlen($statement) > 0) { if ($and) { $statement = str_replace(" AND ", " AND ", $statement); } else { $statement = str_replace(" AND ", " OR ", $statement); } $this->setWhere('(' . $statement . ')'); } } }