Query provides a set of methods to facilitate the specification of "find" command.
These methods can be chained together.
For example,
~~~
$query = new Query;
compose the query
$query->select(['name', 'status'])
->from('customer')
->limit(10);
execute the query
$rows = $query->all();
~~~
public function perform()
{
$args = $this->args;
if (empty($args['key']) || empty($args['header']) || empty($args['accountId'])) {
ResqueUtil::log(['status' => 'fail to export registration record', 'message' => 'missing params', 'args' => $args]);
return false;
}
$keyword = $args['keyword'];
$header = $args['header'];
$fileName = $args['key'];
$accountId = unserialize($args['accountId']);
$filePath = ExcelUtil::getFile($fileName, 'csv');
$query = new Query();
if ($keyword == '') {
$records = $query->from('uhkklpRegistration')->select(['name', 'mobile', 'restaurantName', 'zipCode', 'city', 'address'])->where(['accountId' => $accountId])->all();
} else {
$records = $query->from('uhkklpRegistration')->select(['name', 'mobile', 'restaurantName', 'zipCode', 'city', 'address'])->where(['accountId' => $accountId])->andWhere(['like', 'mobile', $keyword])->all();
}
$rows = $records;
ExcelUtil::exportCsv($header, $rows, $filePath, 1);
$hashKey = ExcelUtil::setQiniuKey($filePath, $fileName);
if ($hashKey) {
//notice frontend the job is finished
Yii::$app->tuisongbao->triggerEvent(Message::EVENT_EXPORT_FINISH, ['key' => $fileName], [Message::CHANNEL_GLOBAL]);
return true;
} else {
ResqueUtil::log(['status' => 'fail to export registration', 'message' => 'fail to setQiniuKey', 'filePath' => $filePath]);
return false;
}
}
public function actionRegister()
{
//get request
$postData = file_get_contents('php://input', true);
$data = json_decode($postData, true);
$mobile = $data['data']['phone'];
Yii::getLogger()->log($data['data'], 2, "webhook");
//send sms
if ($mobile != null && $mobile != '') {
$reminder = new Reminder();
$createDate = time();
$reminder->mobile = $mobile;
$reminder->createDate = $createDate;
$reminder->updated = false;
$reminder->origin = 'webhook';
$reminder->save();
$rid = $reminder->_id . '';
$reminder->url = $this->replaceHttp(Yii::$app->request->hostInfo) . "/user/reset_password/" . $rid;
Yii::getLogger()->log($reminder->url, 2, "webhookUrl");
$reminder->save();
$name = null;
$qry = new Query();
$qry->from('reward');
$list = $qry->all();
if ($list != null && count($list) > 0) {
$name = $list[0]['name'];
}
if ($name == null) {
$name = "\$25聖安娜蛋糕禮券";
}
$content = "恭喜您參加家樂牌「儲分有賞」活動。您的帳戶已建立，立即按連結重設密碼並登入，即可查詢分數並有機會獲得" . $name . "!\n" . $reminder->url;
Yii::getLogger()->log($content, 2, "content");
Yii::$app->sms->sendSms($reminder->mobile, $content);
}
}
public function getList($condition = [])
{
$sort = ['begin' => SORT_DESC];
$query = new Query();
$data = $query->from(self::collectionName())->select($this->attributes())->where($condition)->orderBy($sort)->all();
return $data;
}
public function actionGetList()
{
$currentPage = Yii::$app->request->get("currentPage", 1);
$pageSize = Yii::$app->request->get("pageSize", 10);
$offset = ($currentPage - 1) * $pageSize;
$sortName = "_id";
$sortDesc = Yii::$app->request->get('sortDesc', 'ASC');
$sort = $sortName . ' ' . $sortDesc;
$keyword = Yii::$app->request->get("keyword", '');
$query = new Query();
$accountId = $this->getAccountId();
if ($keyword == '') {
$records = $query->from('uhkklpOrder')->select(['_id', 'createdAt', 'name', 'mobile', 'restaurantName', 'address', 'businessForm', 'product'])->where(['accountId' => $accountId])->orderBy($sort)->offset($offset)->limit($pageSize)->all();
} else {
$records = $query->from('uhkklpOrder')->select(['_id', 'createdAt', 'name', 'mobile', 'restaurantName', 'address', 'businessForm', 'product'])->where(['accountId' => $accountId])->andWhere(['like', 'mobile', $keyword])->orderBy($sort)->offset($offset)->limit($pageSize)->all();
}
for ($i = 0; $i < count($records); $i++) {
$records[$i]['createdAt'] = MongodbUtil::MongoDate2String($records[$i]['createdAt'], 'Y-m-d H:i:s', null);
$records[$i]['_id'] = (string) $records[$i]['_id'];
}
$query = new Query();
if ($keyword == '') {
$totalPageCount = $query->from('uhkklpOrder')->where(['accountId' => $accountId])->count();
} else {
$totalPageCount = $query->from('uhkklpOrder')->where(['accountId' => $accountId])->andWhere(['like', 'name', $keyword])->count();
}
// LogUtil::error(date('Y-m-d h:i:s') . ' $totalPageCount: ' . $totalPageCount);
Yii::$app->response->format = \yii\web\Response::FORMAT_JSON;
return ['code' => 200, 'list' => $records, 'totalPageCount' => $totalPageCount];
}
private function _savePushMessages($messageId, $phoneNums)
{
$pushUsers = [];
if (empty($phoneNums)) {
$query = new Query();
$result = $query->from(PushUser::collectionName())->select(['token', 'deviceType', 'mobile'])->where(['accountId' => $this->getAccountId()])->all();
$pushUsers = array_merge($pushUsers, $result);
} else {
foreach ($phoneNums as $phone) {
$query = new Query();
$result = $query->from(PushUser::collectionName())->select(['token', 'deviceType', 'mobile'])->where(['mobile' => $phone, 'accountId' => $this->getAccountId()])->all();
$pushUsers = array_merge($pushUsers, $result);
}
}
$pushUsers = $this->_uniquePushUsers($pushUsers);
foreach ($pushUsers as $pushUser) {
$pushMessage = new PushMessage();
if (!empty($pushUser['mobile'])) {
$pushMessage->mobile = $pushUser['mobile'];
}
$pushMessage->token = $pushUser['token'];
$pushMessage->deviceType = $pushUser['deviceType'];
$pushMessage->messageId = $messageId;
if (!empty($this->getAccountId())) {
$pushMessage->accountId = $this->getAccountId();
}
$pushMessage->insert();
}
}
public function testLimitOffset()
{
$query = new Query();
$query->limit(10)->offset(5);
$this->assertEquals(10, $query->limit);
$this->assertEquals(5, $query->offset);
}
public function actionIndex()
{
$query = new Query();
$query->select(["username", "password"])->from("admin_user");
$re = $query->all();
//print_r($re);die;
return $this->renderPartial('index', ["data" => $re]);
}
/**
* @inheritdoc
*/
protected function findTemplate($name)
{
$query = new Query();
$template = $query->select($this->templateDataFields)->from($this->templateCollection)->where([$this->templateNameField => $name])->one();
if ($template === false) {
return null;
}
return $template;
}
public function actionCheck_login()
{
$username = $_POST['username'];
$pwd = $_POST['pwd'];
$query = new Query();
$query->select(['name', 'status', 'password'])->from('admin')->where(['name' => $username]);
$arr = $query->one();
if ($arr) {
if ($arr['password'] == md5($pwd)) {
$_SESSION['user_id'] = $arr['_id'];
$_SESSION['user_name'] = $arr['name'];
if ($arr['name'] != "admin") {
$userid = $arr['_id'];
//根据用户id查找用户的角色ID
$query->select(['role_id'])->from('user_role')->where(['user_id' => "{$userid}"]);
$res = $query->one();
$role_id = $res['role_id'];
//根据角色ID查询所有权限ID,将其ID存入字符串中
$q = new Query();
$q->select(['node_id'])->from('role_node')->where(['role_id' => $role_id]);
$ar = $q->all();
$que = new Query();
foreach ($ar as $key => $v) {
// $node[$key] = $v['node_id'];
$idd = $v['node_id'];
$que->select(['n_name', 'nickname', 'n_level', 'p_id'])->from('node')->where(['_id' => "{$idd}"])->andwhere(['n_level' => "1"]);
$quan[$key] = $que->all();
}
$yi_quan = array_filter($quan);
$_SESSION['yi_quan'] = $yi_quan;
$quee = new Query();
foreach ($ar as $kk => $vv) {
// $node[$key] = $v['node_id'];
$iddd = $vv['node_id'];
$quee->select(['n_name', 'nickname', 'n_level', 'p_id'])->from('node')->where(['_id' => "{$iddd}"])->andwhere(['n_level' => "2"]);
$quanx[$kk] = $quee->all();
}
$er_quan = array_filter($quanx);
$_SESSION['er_quan'] = $er_quan;
} else {
$que = new Query();
$que->select(['n_name', 'nickname', 'n_level', 'p_id'])->from('node')->where(['n_level' => "1"]);
$yi_quan = $que->all();
$_SESSION['yi_q'] = $yi_quan;
$quee = new Query();
$quee->select(['n_name', 'nickname', 'n_level', 'p_id'])->from('node')->where(['n_level' => "2"]);
$er_quan = $quee->all();
$_SESSION['er_q'] = $er_quan;
}
echo "<script>location.href='index.php?r=site/index'</script>";
} else {
echo "<script>alert('密码错误');location.href='index.php?r=public/login'</script>";
}
} else {
echo "<script>alert('用户名或密码错误');location.href='index.php?r=public/login'</script>";
}
}
static function GetForms()
{
$query = new Query();
// compose the query
$query->select([])->from('forms');
// execute the query
$rows = $query->all();
return $rows;
}
public function actionEdit()
{
$id = $_GET['id'];
$query = new Query();
$query->select(['type_name', 'level', 'p_id'])->from('type')->where(['_id' => $id]);
$data['rows'] = $query->one();
$query->select(['type_name', 'level', 'p_id'])->from('type')->where(['p_id' => '0']);
$data['arr'] = $query->all();
return $this->renderPartial("edit", $data);
}
/**
* @inheritdoc
*/
public function getHistory($chatId, $limit = 10)
{
$query = new Query();
$query->select(['user_id', 'username', 'message', 'timestamp', 'avatar_16', 'avatar_32'])->from(self::collectionName())->where(['chat_id' => $chatId]);
$query->orderBy(['timestamp' => SORT_DESC]);
if ($limit) {
$query->limit($limit);
}
return $query->all();
}
/**
* @inheritdoc
*/
public function get()
{
$query = new Query();
$rows = $query->from($this->collection)->all();
$values = [];
foreach ($rows as $row) {
$values[$row['id']] = $row['value'];
}
return $values;
}
/**
* Retrieves a value from cache with a specified key.
* This method should be implemented by child classes to retrieve the data
* from specific cache storage.
* @param string $key a unique key identifying the cached value
* @return string|boolean the value stored in cache, false if the value is not in the cache or expired.
*/
protected function getValue($key)
{
$query = new Query();
$row = $query->select(['data'])->from($this->cacheCollection)->where(['id' => $key, '$or' => [['expire' => 0], ['expire' => ['$gt' => time()]]]])->one($this->db);
if (empty($row)) {
return false;
} else {
return $row['data'];
}
}
public static function findList($currentPage = 1, $pageSize = 10, $condition = [])
{
$offset = ($currentPage - 1) * $pageSize;
$query = new Query();
$users = $query->from('uhkklpActivityUser')->select(['_id', 'deviceId', 'prizeContent', 'mobile', 'createdAt'])->where($condition)->orderBy(['createdAt' => SORT_DESC])->offset($offset)->limit($pageSize)->all();
for ($i = 0; $i < count($users); $i++) {
$users[$i]['createdAt'] = MongodbUtil::MongoDate2String($users[$i]['createdAt'], 'Y-m-d H:i:s', null);
}
return $users;
}
public function testQuery()
{
$query = new Query();
$query->from('customer');
$provider = new ActiveDataProvider(['query' => $query, 'db' => $this->getConnection()]);
$models = $provider->getModels();
$this->assertEquals(10, count($models));
$provider = new ActiveDataProvider(['query' => $query, 'db' => $this->getConnection(), 'pagination' => ['pageSize' => 5]]);
$models = $provider->getModels();
$this->assertEquals(5, count($models));
}
public static function findList($currentPage = 1, $pageSize = 10, $condition = [])
{
$offset = ($currentPage - 1) * $pageSize;
$query = new Query();
$records = $query->from('uhkklpLuckyDrawRecord')->select(['_id', 'createdAt', 'remark'])->where($condition)->orderBy(['createdAt' => SORT_DESC])->offset($offset)->limit($pageSize)->all();
for ($i = 0; $i < count($records); $i++) {
$records[$i]['createdAt'] = MongodbUtil::MongoDate2String($records[$i]['createdAt'], 'Y-m-d H:i:s', null);
$records[$i]['_id'] = (string) $records[$i]['_id'];
}
return $records;
}
public static function getSampleRecordExcelDate($cookbookId, $accountId)
{
$query = new Query();
$query->from('uhkklpSamplerecord')->select(['mobile', 'cookbookTitle', 'sampleId', 'sampleName', 'username', 'city', 'address', 'createdDate', 'quantity', 'sent'])->where(['cookbookId' => $cookbookId])->andWhere(['accountId' => $accountId]);
$list = $query->all();
$rows = [];
for ($i = 0; $i < count($list); $i++) {
$row = ['mobile' => $list[$i]['mobile'], 'sampleName' => $list[$i]['sampleName'], 'username' => $list[$i]['username'], 'city' => $list[$i]['city'], 'address' => $list[$i]['address'], 'quantity' => $list[$i]['quantity']];
$rows[] = $row;
unset($row);
}
return $rows;
}
public function actionInit()
{
$blackList = ['0978085158', '0927965300', '0919068348', '0917256888', '0935293966', '0926231395', '0922376121', '0918762006', '0920220502', '0937041116', '0976556382', '0926163196', '0989563597', '0933170602', '0982100057', '0926988398', '0986265939', '0910254314', '0937189314', '0972113501', '0912210412', '0987906236', '0937005340', '0916206650', '0918789015', '0985979734', '0910619168', '0912866012', '0930002577', '0937006276', '0982066318', '0909111302', '0911031494', '0912600810', '0981365018', '0919096112', '0911091053', '0923319780', '0989173848', '0952655000', '0979767968', '0905630508', '0963260389', '0958917419', '0929190959', '0939068141', '0925338020', '0912764270', '0913177063', '0921530245', '0911078461', '0982919062', '0960000277'];
$query = new Query();
$query->from('voucher_black');
$count = $query->count();
if ($count == 0) {
foreach ($blackList as $mobile) {
$item = new VoucherBlack();
$item->mobile = $mobile;
$item->enable = true;
$item->save();
}
}
}
public static function getResults($messageId)
{
$query1 = new Query();
$data = $query1->from(self::collectionName())->select(['messageId', 'results'])->where(['messageId' => $messageId])->all();
if (empty($data)) {
return [];
}
$data = $data[0];
$query2 = new Query();
$messageContent = $query2->from(Message::collectionName())->select(['content'])->where(['_id' => new \MongoId($messageId)])->all()[0]['content'];
$results = [];
foreach ($data['results'] as $key => $value) {
if (empty($value['mobile'])) {
$value['mobile'] = '';
}
if ($value['type'] == 'iOS') {
if (empty($value['res']) || $value['res'] != 200) {
$value['res'] = '失敗';
} else {
$value['res'] = '成功';
}
} else {
if (!empty($value['res'])) {
$jsonObj = json_decode($value['res']);
if ($jsonObj->success != null && $jsonObj->success > 0) {
$value['res'] = '成功';
} else {
$value['res'] = '失敗';
}
}
}
// $query3 = new Query();
// $mobile = $query3->from(PushUser::collectionName())
// ->select(['mobile'])
// ->where(['token' => $value['token']])
// ->all();
// if (empty($mobile)) {
// $mobile = '';
// } else {
// $mobile = $mobile[0]['mobile'];
// }
$results[] = ['messageId' => $messageId, 'messageContent' => $messageContent, 'mobile' => $value['mobile'] . ' ', 'deviceType' => $value['type'], 'deviceId' => $value['token'], 'result' => $value['res']];
}
return $results;
}
public static function getList($currentPage = 1, $pageSize = 10, $sort = [], $condition = [])
{
if (empty($sort)) {
$sort = ['_id' => SORT_DESC];
} else {
foreach ($sort as $key => $value) {
if ($value) {
$sort = [$key => SORT_DESC];
} else {
$sort = [$key => SORT_ASC];
}
}
}
$offset = ($currentPage - 1) * $pageSize;
$query = new Query();
$datas = $query->from(self::collectionName())->select(self::attributes())->where(['accountId' => Token::getAccountId()])->andWhere($condition)->orderBy($sort)->offset($offset)->limit($pageSize)->all();
return $datas;
}
public function getList($currentPage = 1, $pageSize = 10, $sort, $condition = [])
{
if (empty($sort)) {
$sort = ['_id' => SORT_DESC];
} else {
foreach ($sort as $key => $value) {
if ($value) {
$sort = [$key => SORT_DESC];
} else {
$sort = [$key => SORT_ASC];
}
}
}
$offset = ($currentPage - 1) * $pageSize;
$condition['isDeleted'] = false;
$query = new Query();
$datas = $query->from(self::collectionName())->select($this->attributes())->where($condition)->orderBy($sort)->offset($offset)->limit($pageSize)->all();
return $datas;
}
/**
* Converts the raw query results into the format as specified by this query.
* This method is internally used to convert the data fetched from database
* into the format as required by this query.
* @param array $rows the raw query result from database
* @return array the converted query result
*/
public function populate($rows)
{
$result = [];
foreach ($rows as $file) {
$row = $file->file;
$row['file'] = $file;
$result[] = $row;
}
return parent::populate($result);
}
public function actionCheck_login()
{
//print_r($_POST);
$username = $_POST['username'];
$arr = array('username' => $username);
$query = new Query();
$query->select(['username', 'password'])->from('admin_user')->where($arr);
$rows = $query->all();
//echo $rows[0]['password'];die;
if ($rows) {
if ($rows[0]['password'] == md5($_POST['password'])) {
$_SESSION['user_info'] = $username;
echo $this->success("登陆成功", "index.php?r=index/index", '1');
} else {
echo $this->error("密码错误，登陆失败", "index.php?r=public/login", '1');
}
} else {
echo $this->error("账号错误，登陆失败", "index.php?r=public/login", '1');
}
}
/**
* Displays homepage.
*
* @return mixed
*/
public function actionIndex()
{
$dsn = "mongodb://192.168.1.164";
$connection = new \yii\mongodb\Connection(['dsn' => $dsn]);
$connection->open();
//var_dump($connection);die;
$database = $connection->getDatabase('niuke');
/*
$collection = $database->getCollection('user');
//var_dump($collection);die;
*/
$query = new Query();
// compose the query
$query->select(['niukename', 'password'])->from('user')->limit(10);
// execute the query
$rows = $query->all();
var_dump($rows);
/*
//return $this->render('index');*/
}
public function perform()
{
$args = $this->args;
if (empty($args['key']) || empty($args['header']) || empty($args['accountId'])) {
ResqueUtil::log(['status' => 'fail to export goods record', 'message' => 'missing params', 'args' => $args]);
return false;
}
$keyword = $args['keyword'];
$header = $args['header'];
$fileName = $args['key'];
$accountId = unserialize($args['accountId']);
$filePath = ExcelUtil::getFile($fileName, 'csv');
$query = new Query();
if ($keyword == '') {
$records = $query->from('uhkklpOrder')->select(['_id', 'createdAt', 'name', 'mobile', 'restaurantName', 'address', 'businessForm', 'product'])->where(['accountId' => $accountId])->all();
} else {
$records = $query->from('uhkklpOrder')->select(['_id', 'createdAt', 'name', 'mobile', 'restaurantName', 'address', 'businessForm', 'product'])->where(['accountId' => $accountId])->andWhere(['like', 'mobile', $keyword])->all();
}
for ($i = 0; $i < count($records); $i++) {
$records[$i]['createdAt'] = MongodbUtil::MongoDate2String($records[$i]['createdAt'], 'Y-m-d H:i:s', null);
$records[$i]['_id'] = (string) $records[$i]['_id'];
$pro = "";
for ($j = 0; $j < count($records[$i]['product']); $j++) {
$pro .= " " . $records[$i]['product'][$j];
}
$records[$i]['product'] = $pro;
}
$rows = $records;
ExcelUtil::exportCsv($header, $rows, $filePath, 1);
$hashKey = ExcelUtil::setQiniuKey($filePath, $fileName);
if ($hashKey) {
//notice frontend the job is finished
Yii::$app->tuisongbao->triggerEvent(Message::EVENT_EXPORT_FINISH, ['key' => $fileName], [Message::CHANNEL_GLOBAL]);
return true;
} else {
ResqueUtil::log(['status' => 'fail to export order', 'message' => 'fail to setQiniuKey', 'filePath' => $filePath]);
return false;
}
}
public function testLike()
{
$connection = $this->getConnection();
$query = new Query();
$rows = $query->from('customer')->where(['LIKE', 'name', '/me1/'])->all($connection);
$this->assertEquals(2, count($rows));
$this->assertEquals('name1', $rows[0]['name']);
$this->assertEquals('name10', $rows[1]['name']);
}
/**
* Ensures migration history contains at least base migration entry.
*/
protected function ensureBaseMigrationHistory()
{
if (!$this->baseMigrationEnsured) {
$query = new Query();
$row = $query->select(['version'])->from($this->migrationCollection)->andWhere(['version' => self::BASE_MIGRATION])->limit(1)->one($this->db);
if (empty($row)) {
$this->addMigrationHistory(self::BASE_MIGRATION);
}
$this->baseMigrationEnsured = true;
}
}
private function formatCookbookForAPI($cookbook)
{
//averageScore
if (!isset($cookbook['averageScore'])) {
$cookbook['averageScore'] = 0.0;
}
//isNewest
for ($i = 0; $i < count($cookbook['type']); $i++) {
if ($cookbook['type'][$i] == '最新食譜') {
$cookbook['isNewest'] = 'Y';
} else {
$cookbook['isNewest'] = 'N';
}
}
//collection number
$cookbookId = $cookbook['cookbookId'];
$query = new Query();
$query->from('uhkklpUserCookbook')->where(['cookbookId' => $cookbookId])->andWhere(['collection' => 'Y']);
$collectionNumber = $query->count();
$cookbook['collectionNumber'] = $collectionNumber;
//score number
$cookbookId = $cookbook['cookbookId'];
$query = new Query();
$query->from('uhkklpUserCookbook')->where(['cookbookId' => $cookbookId]);
$scorerNumber = $query->count();
$cookbook['scorerNumber'] = $scorerNumber;
//sample
$sample = $cookbook['sample'];
for ($j = 0; $j < count($sample); $j++) {
$querySample = new Query();
$querySample->from('uhkklpSample')->where(['_id' => $sample[$j]['id']]);
$sampleDate = $querySample->one();
$cookbook['sample'][$j]['imgUrl'] = $sampleDate['imgUrl'];
$cookbook['sample'][$j]['explain'] = $sampleDate['quantity'];
unset($cookbook['sample'][$j]['quantity']);
}
return $cookbook;
}
/**
* Rewrite init function and add isDelete condition
*/
public function init()
{
parent::init();
$this->andWhere(['isDeleted' => self::NOT_DELETED]);
}