protected function actionAjax1()
{
$request = $this->getRequest();
$container = $this->getContainer();
/** @var \Zero\Module\Metronic\AdminModule\Database\MySQL\TestDatabase $db */
$db = $container->tag('metronic_test_db');
// 查询 information_schema的schemata表
$table_name = $db->getTableName('schemata');
$schema_sql = "show columns from {$table_name}";
$query = $db->getSelectQuery($schema_sql);
$schema_data = $query->getResult();
$data_table = array('head' => array(), 'body' => array());
$fields = array();
foreach ($schema_data as $i => $row) {
if ($i < 5) {
$data_table['head'][] = $row['Field'];
$fields[] = $row['Field'];
}
}
$posts = $request->request;
$page_offset = $posts->get('start');
$page_offset = intval($page_offset);
$page_size = $posts->get('length');
$page_size = intval($page_size);
$is_all = false;
if ($page_size < 0) {
$is_all = true;
}
$s_echo = $posts->get('draw');
$s_echo = intval($s_echo);
$search = $posts->get('search');
$search_value = $search['value'];
$records = array();
$records['data'] = array();
$records['draw'] = $s_echo;
$records['recordsTotal'] = 0;
$records['recordsFiltered'] = 0;
$columns = $fields;
$orders = $posts->get('order');
$sorts = array();
foreach ($orders as $order) {
$col = $order['column'];
$dir = $order['dir'];
$sorts[] = array('field' => $columns[$col], 'direction' => $dir);
}
$pager = null;
// 直接搜索所有
$sql = "select count(*) as `num` from {$table_name}";
$query = $db->getSelectQuery($sql);
$row = $query->getRow();
$total = $row['num'];
if ($is_all) {
$page_size = $total;
}
$page = $page_offset / $page_size + 1;
$qb = $db->createQueryBuilder();
$qb->select($fields)->from($table_name)->setFirstResult(max(0, ($page - 1) * $page_size))->setMaxResults($page_size);
if (!empty($sorts)) {
foreach ($sorts as $sort) {
$field = "`{$sort['field']}`";
$direction = $sort['direction'];
$qb->addOrderBy($field, $direction);
}
}
if ($search_value) {
foreach ($fields as $field) {
$qb->orWhere($qb->expr()->eq("`{$field}`", ":{$field}"))->setParameter(":{$field}", $search_value);
}
}
$sql = $qb->getSQL();
$query = $db->getSelectQuery($sql, $qb->getParameters());
$pager = new Pager($query->getResult(), $total, $page, $page_size);
$total = $pager->getCount();
$records['recordsTotal'] = $total;
$records['recordsFiltered'] = $total;
$data = $pager->getData();
foreach ($data as $k => $v) {
$line = array();
foreach ($v as $kk => $vv) {
if ($vv) {
$line[] = $vv;
} else {
$line[] = ' ';
}
}
$records['data'][] = $line;
}
return new JsonResponse($records);
}
