/**
* Create yaml files from database data.
*
* Usage (from command line):
*
* php oil r dbfixt:generate [-n=5] [-o=/tmp] <table1> [<table2> [...]]
*
* @return string
*/
public static function generate()
{
$num = Cli::option('n') ? (int) Cli::option('n') : 5;
$dir = Cli::option('o');
if (is_null($dir)) {
$dir = APPPATH . 'tests/fixture';
if (!is_dir($dir)) {
mkdir($dir);
}
} else {
if (!is_dir($dir)) {
return Cli::color('No such directory: ' . $dir, 'red');
}
}
$args = func_get_args();
foreach ($args as $table) {
if (DBUtil::table_exists($table)) {
$result = DB::select('*')->from($table)->limit($num)->execute();
$data = $result->as_array();
static::setToYaml($dir, $data, $table);
} elseif (Mongo_Db::instance()->get_collection($table)) {
$mongo = Mongo_Db::instance();
$data = $mongo->limit($num)->get($table);
static::setToYaml($dir, $data, $table);
} else {
echo Cli::color('No such table: ' . $table, 'red') . PHP_EOL;
}
}
}
public static function breadcum_base_type($type)
{
//Name
$dbResult = DB::select('TypeName')->from(self::$TABLE)->where('ID', $type);
// Parent name
$dbResult = DB::select('TypeName')->from(self::$TABLE)->where('ParentID', $type);
}
public function connectDB()
{
$userData = null;
$query = DB::select()->from('users');
$userData = $query->execute();
return $userData;
}
private function _get_where($filters = array())
{
$query = \Fuel\Core\DB::select('emcall.*')->from('emcall');
if (isset($filters['person_id']) && $filters['person_id']) {
$query->where('person_id', '=', $filters['person_id']);
}
return $query;
}
public function getListBaseType($type)
{
try {
$dbResult = DB::select('*')->from('News')->where('Type', $type)->execute();
return $this->format($dbResult->as_array());
} catch (\Exception $e) {
ErrorMsg::_log($e);
return null;
}
}
/**
* @author dangbc <*****@*****.**>
* get data plan
*/
public function data_plan($current_date)
{
$data_plan['have_data'] = false;
$current_year = $current_date;
$data = \Fuel\Core\DB::select('*')->from('plan')->where('start_date', '=', $current_year)->execute()->as_array();
if (!empty($data)) {
$data_plan['have_data'] = true;
}
$data_plan['data_plan'] = $data;
return $data_plan;
}
/**
* @author Bui Dang <*****@*****.**>
* action check group name exits
*/
public static function check_name($group_id = null, $group_name = null)
{
$select_group = \Fuel\Core\DB::select('*')->from(self::$_table_name);
if (isset($group_id)) {
$select_group->where('m_group_id', '!=', $group_id);
}
if (isset($group_name)) {
$select_group->where('name', '=', $group_name);
}
return count($select_group->execute());
}
public static function getStaffBaseID($id)
{
try {
$dbObj = DB::select('*')->from("Employee")->where('ID', $id);
$dbResult = $dbObj->execute();
print_r($dbResult);
exit;
} catch (\Exception $e) {
print_r($e);
exit;
}
}
public static function buildListFaculty()
{
try {
/** @var Database_Query $dbObj */
$dbObj = DB::select('*')->from('Faculty');
$dbResult = $dbObj->execute();
return $dbResult->as_array();
} catch (\Exception $e) {
ErrorCode::dbLog($e);
return null;
}
}
public static function update_content($data)
{
$type = $data['type'];
$content = $data['content'];
$dbResult = DB::select('id')->from('recruitment')->where('type', $type)->execute();
$id = $dbResult->as_array();
if (empty($id)) {
DB::insert('recruitment')->set(array('name' => $type, 'content' => 'N' . 'init data ', 'type' => $type))->execute();
}
$query = "\n UPDATE recruitment\n SET\n content = N'{$content}'\n WHERE\n type = '{$type}'\n ";
$dbResult = DB::query($query)->execute();
return $dbResult;
}
public static function update_content($data)
{
$faculty = $data['faculty'];
$name = $data['type'];
$content = $data['content'];
$dbResult = DB::select('id')->from('admissions_faculty')->where('name', $name)->and_where('faculty_id', $faculty)->execute();
$id = $dbResult->as_array();
if (empty($id)) {
DB::insert('admissions_faculty')->set(array('name' => $name, 'faculty_id' => $faculty, 'content' => 'N' . 'init data '))->execute();
}
$query = "\n UPDATE admissions_faculty\n SET\n content = N'{$content}'\n WHERE\n faculty_id = '{$faculty}'\n and\n name = '{$name}'\n ";
$dbResult = DB::query($query)->execute();
return $dbResult;
}
public static function _get_where($filter = array(), $keyword = array())
{
$query = \Fuel\Core\DB::select('*')->from(self::$_table_name)->order_by('created_at', 'desc');
if (isset($filter['start_date']) and isset($filter['end_date'])) {
$query->where_open();
$query->where('created_at', '>=', $filter['start_date']);
$query->where('created_at', '<=', $filter['end_date']);
$query->where_close();
}
if (isset($filter['start_date']) and !isset($filter['end_date'])) {
$query->where('created_at', '>=', $filter['start_date']);
}
if (isset($filter['end_date']) and !isset($filter['start_date'])) {
$query->where('created_at', '<=', $filter['end_date']);
}
if (!empty($keyword)) {
$query->and_where_open();
$query->or_where_open();
foreach ($keyword as $word) {
$query->where('name', 'like', '%' . $word . '%');
}
$query->or_where_close();
$query->or_where_open();
foreach ($keyword as $word) {
$query->where('name_kana', 'like', '%' . $word . '%');
}
$query->or_where_close();
$query->or_where_open();
foreach ($keyword as $word) {
$query->where('mobile', 'like', '%' . $word . '%');
}
$query->or_where_close();
$query->or_where_open();
foreach ($keyword as $word) {
$query->where('mail', 'like', '%' . $word . '%');
}
$query->or_where_close();
$query->and_where_close();
}
if (isset($filter['offset'])) {
$query->offset($filter['offset']);
}
if (isset($filter['limit'])) {
$query->limit($filter['limit']);
}
return $query;
}
protected function getLetters()
{
$numSymbol = \Arr::get($this->config, 'numeric-symbol');
$lettersQuery = DB::select(array(\DB::expr('LOWER(SUBSTR(' . Model_Word::title_property() . ',1,1)) collate utf8_general_ci '), 'initial'))->from(Model_Word::table())->group_by('initial')->order_by('initial', 'asc')->execute();
$letters = array();
foreach ($lettersQuery as $let) {
$let['initial'] = iconv('UTF-8', 'ASCII//TRANSLIT', $let['initial']);
if (!preg_match('/[a-z]/i', $let['initial'])) {
$let['initial'] = $numSymbol;
}
if (in_array($let['initial'], $letters)) {
continue;
}
$letters[] = $let['initial'];
}
return $letters;
}
/**
* @author Thuanth6589 <*****@*****.**>
* @param array $filters
* @return $this
*/
private function _get_where($filters = array())
{
$query = \Fuel\Core\DB::select('m_user.*')->from('m_user');
if (isset($filters['department_id']) && $filters['department_id']) {
$query->where('m_user.department_id', '=', $filters['department_id']);
}
if (isset($filters['name']) && $filters['name']) {
$query->where('m_user.name', 'like', '%' . $filters['name'] . '%');
}
if (isset($filters['limit']) && $filters['limit']) {
$query->limit($filters['limit']);
}
if (isset($filters['offset']) && $filters['offset']) {
$query->offset($filters['offset']);
}
if (isset($filters['order_by_time'])) {
$query->order_by('m_user.created_at');
} else {
$query->order_by('m_user.user_id', 'desc');
}
return $query;
}
public function load_details($id)
{
try {
$dbObj = DB::select_array($this->fields)->from($this->TABLE)->where('ID', $id);
//$dbResult = $dbObj->compile();
$dbResult = $dbObj->execute();
if (count($dbResult) > 0) {
// Map object
$this->isValid = true;
$this->map($this, $dbResult->as_array()[0]);
} else {
$this->isValid = false;
}
$dbObj = DB::select('TypeName')->from('StoryType')->where('ID', $this->Type);
$dbResult = $dbObj->execute();
$this->TypeName = $dbResult->as_array()[0]['TypeName'];
} catch (\Exception $e) {
CError::dbLog($e);
$this->isValid = false;
return null;
}
}
public static function get($id)
{
$dbResult = DB::select('*')->from(self::$TABLE)->where('ID', $id)->execute();
$dbData = $dbResult->as_array();
return $dbData[0];
}
/**
* @author thuanth6589 <*****@*****.**>
* get list person for top(division = 2)
* @param array $array_user
* @return mixed
*/
public function get_person_division_2($array_user = array())
{
$array_user = empty($array_user) ? array(0) : $array_user;
$query = \Fuel\Core\DB::select('orders.interview_user_id', 'orders.agreement_user_id', 'orders.training_user_id', array('m_partner.user_id', 'partner_user_id'), 'employment.contact_result', 'employment.review_date', 'employment.review_result', 'employment.adoption_result', 'employment.contract_date', 'employment.contract_result', 'employment.hire_date', 'employment.employee_code', 'employment.work_confirmation')->from('person');
$query->join('employment', 'left')->on('employment.person_id', '=', 'person.person_id');
$query->join('orders', 'left')->on('person.order_id', '=', 'orders.order_id');
$query->join('sssale', 'left')->on('sssale.sssale_id', '=', 'person.sssale_id');
$query->join('m_ss', 'left')->on('sssale.ss_id', '=', 'm_ss.ss_id');
$query->join('m_partner', 'left')->on('m_ss.partner_code', '=', 'm_partner.partner_code');
$query->and_where_open();
$query->where('orders.interview_user_id', 'in', $array_user);
$query->or_where('orders.agreement_user_id', 'in', $array_user);
$query->or_where('orders.training_user_id', 'in', $array_user);
$query->or_where_open();
$query->where('m_partner.user_id', 'in', $array_user);
//$query->where('person.order_id', 'is', null);
$query->or_where_close();
$query->and_where_close();
$query->and_where_open();
$query->or_where('employment.contact_result', '=', null);
$query->or_where('employment.contact_result', '<', 2);
$query->and_where_close();
$query->and_where_open();
$query->or_where('employment.review_result', '=', null);
$query->or_where('employment.review_result', '<', 2);
$query->and_where_close();
$query->and_where_open();
$query->or_where('employment.adoption_result', '=', null);
$query->or_where('employment.adoption_result', '<', 2);
$query->and_where_close();
$query->and_where_open();
$query->or_where('employment.contract_result', '=', null);
$query->or_where('employment.contract_result', '<', 2);
$query->and_where_close();
$query->and_where_open();
$query->or_where('employment.work_confirmation', '=', null);
$query->or_where('employment.work_confirmation', '<', 2);
$query->and_where_close();
return $query->execute();
}
/**
* Get articles of a category with limit for Load-more (10 articles)
*
* @params int $cat_id Category - ID
* @params string $lang Lanuague of acticles
* @params boolean $limit Limit or Not
*
* @return array articles of category
*
* @version 1.0
* @since 1.0
* @access public
* @author Nguyen Van hiep
*/
public static function load_articles_limit($cat_id, $lang, $current_news_count, $limit = 1000)
{
$arts = \Fuel\Core\DB::select('a.id', 'a.title', 'a.thumb', 'a.slug', 'a.desc', 'a.content', 'a.lang', 'a.views', 'a.created_at', 'a.updated_at', array('ac.cat_id', 'cat_id'))->join(array('art_cat', 'ac'), 'LEFT')->on('ac.art_id', '=', 'a.id')->from(array('article', 'a'))->where('ac.cat_id', '=', $cat_id)->where('a.lang', '=', $lang)->order_by('a.updated_at', 'desc');
$arts->limit($limit);
$arts->offset($current_news_count);
return $arts->execute()->as_array();
}
/**
* @author Dangbc <*****@*****.**>
* @param $where
* @return object
*/
public function get_list_ss_addr($filter = array())
{
$query = \Fuel\Core\DB::select('ss_id')->from(self::$_table_name);
if ($filter['addr_1']) {
$query->where('addr1', '=', $filter['addr_1']);
}
if ($filter['addr_2']) {
$query->where('addr2', '=', $filter['addr_2']);
}
return $query->execute()->as_array();
}
/**
* find for pagination
*
* @param mixed $params array - like model find param
* Database_Query_Builder_Select instance - you can use raw DB model
* boolean $need_count
* @return array
* |-- list (array)
* `-- count (int)
*/
public function find($params, $need_count = true)
{
if ($params instanceof \Fuel\Core\Database_Query_Builder_Select) {
$query_cnt = clone $params;
$query_cnt->select_array(array(\Fuel\Core\DB::expr('COUNT(*) as cnt')), true);
$query_list = $params;
$query_list->limit($this->per_page)->offset($this->get_offset());
} else {
$default = array('select' => array('*'), 'where' => array(), 'limit' => $this->per_page, 'offset' => $this->get_offset(), 'order_by' => array(array('id', 'ASC')));
$params = array_merge($default, $params);
if (!isset($params['from'])) {
throw new \Fuel\Core\Database_Exception('Query "from" not found!');
}
$query_list = \Fuel\Core\DB::select_array($params['select'])->from($params['from'])->limit($params['limit'])->offset($params['offset']);
$query_cnt = \Fuel\Core\DB::select(\Fuel\Core\DB::expr('COUNT(*) as cnt'))->from($params['from']);
foreach ($params['where'] as $w) {
if (count($w) > 1) {
call_user_func_array(array($query_list, 'where'), $w);
call_user_func_array(array($query_cnt, 'where'), $w);
} else {
$query_list->where($w);
$query_cnt->where($w);
}
}
foreach ($params['order_by'] as $order) {
call_user_func_array(array($query_list, 'order_by'), $order);
}
if (isset($params['group_by'])) {
if (is_string($params['group_by'])) {
$params['group_by'] = array($params['group_by']);
}
call_user_func_array(array($query_list, 'group_by'), $params['group_by']);
call_user_func_array(array($query_cnt, 'group_by'), $params['group_by']);
}
}
$list = $query_list->execute()->as_array();
$cnt = 0;
if ($need_count) {
$cnt = $query_cnt->execute()->as_array();
if (count($cnt) > 1) {
$cnt = count($cnt);
} else {
$cnt = \Arr::get(\Arr::get($cnt, 0, array()), 'cnt', 0);
}
}
// set items automatically
$this->item_list = $list;
$this->total_items = $cnt;
return array('list' => $list, 'count' => $cnt);
}
public function getSectionInfoBaseID($sectionID)
{
try {
$dbObj = DB::select('TypeName', 'ParentID')->from($this->STORY_TYPE_TABLE)->where('ID', $sectionID);
$dbResult = $dbObj->execute();
$subSectionObj = $dbResult->as_array()[0];
return array('MainSection' => $this->listSection[$subSectionObj['ParentID']], 'SubSection' => $subSectionObj['TypeName']);
} catch (\Exception $e) {
}
}
public static function get_partnercode_department($department_id)
{
$select = \Fuel\Core\DB::select('partner_code')->from(self::$_table_name)->where('department_id', '=', $department_id);
return $select->execute()->as_array();
}
/**
* @author thuanth6589 <*****@*****.**>
* count job for top
* @param array $filters
* @return int
*/
public function count_job_department_id($filters = array())
{
$query = \Fuel\Core\DB::select('job.job_id')->from('job');
$query->join('sssale', 'left')->on('sssale.sssale_id', '=', 'job.sssale_id');
$query->join('m_ss', 'left')->on('m_ss.ss_id', '=', 'sssale.ss_id');
$query->join('m_partner', 'left')->on('m_ss.partner_code', '=', 'm_partner.partner_code');
if (isset($filters['department_id']) && $filters['department_id']) {
$query->where('m_partner.department_id', '=', $filters['department_id']);
}
if (isset($filters['status']) && $filters['status'] !== '') {
$query->where('job.status', '=', $filters['status']);
}
return count($query->execute());
}
/**
* @author thuanth6589 <*****@*****.**>
* count person for export result csv
* @param $filters
* @return int
*/
public function count_person_in_ss($filters)
{
$query = \Fuel\Core\DB::select('person.person_id')->from('orders');
$query->join('person', 'left')->on('orders.order_id', '=', 'person.order_id')->and_on('orders.post_id', '=', 'person.post_id');
if (isset($filters['ss_id'])) {
if (isset($filters['main']) && $filters['main']) {
$query->where('orders.ss_id', '=', $filters['ss_id']);
} else {
$query->where('orders.ss_list', 'like', '%,' . $filters['ss_id'] . ',%');
}
}
if (isset($filters['sssale_id']) && is_array($filters['sssale_id'])) {
$filters['sssale_id'] = empty($filters['sssale_id']) ? array(0) : $filters['sssale_id'];
$query->where('person.sssale_id', 'in', $filters['sssale_id']);
}
if (isset($filters['reprinted_via']) && $filters['reprinted_via']) {
$query->where('person.reprinted_via', '=', $filters['reprinted_via']);
} else {
$query->where('person.reprinted_via', 'is', null);
}
return $query->execute();
}
public static function execute_array()
{
return DB::select('person.person_id', 'person.order_id', 'person.interview_user_id', 'person.agreement_user_id', 'person.training_user_id', 'person.business_user_id', 'm_partner.user_id', 'm_ss.ss_name', 'person.name_kana')->from('person')->join('employment', 'LEFT')->on('person.person_id', '=', 'employment.person_id')->join('sssale')->on('person.sssale_id', '=', 'sssale.sssale_id')->join('m_ss')->on('sssale.ss_id', '=', 'm_ss.ss_id')->join('m_partner')->on('m_ss.partner_code', '=', 'm_partner.partner_code');
}
public function getIdBaseSubId($subID)
{
try {
$dbObj = DB::select('ID')->from($this->STORY_TYPE_TABLE)->where('SubID', $subID);
$dbResult = $dbObj->execute();
return $dbResult->as_array()[0]['ID'];
} catch (\Exception $e) {
CError::dbLog($e);
return null;
}
}
