/**
* 这里要兼容两种数据保存方式：多维数组和键名是路径的一维数组
* 如array('a/b'=>1)或array('a'=>array('b'=>1))
* @param type $item
* @return boolean
*/
function user_item($item, $method = NULL, $controller = NULL)
{
is_null($controller) && ($controller = CONTROLLER);
is_null($method) && ($method = METHOD);
$plain_config = array_merge($this->company, $this->user, $this->session);
$method = array_prefix($plain_config, $controller . '/' . $method . '/' . $item);
$controller = array_prefix($plain_config, $controller . '/' . $item);
if ($method !== array()) {
return $method;
}
if ($controller !== array()) {
return $controller;
}
$global = array_prefix($plain_config, $item);
if ($global !== array()) {
return $global;
}
return false;
}
function all_userdata($prefix = '')
{
$user_data = parent::all_userdata();
return array_prefix($user_data, $prefix);
}
/**
*
* @param array $args
* labels array
* without_labels array
* name
* company
* display
* type
* type_is_not
* id_in
* orderby string or array
* limit string, array or 'pagination'
* with_profiles bool or array()
* @todo team array or int
* profiles array()
* @return array
*/
function getList($args = array())
{
/**
* 这是一个model方法，它具有配置独立性，即所有条件接口均通过参数$args来传递，不接受其他系统变量
*/
if (!$this->db->ar_select) {
$this->db->select($this->table . '.*');
}
$this->db->from($this->table);
//验证读权限
if ($this->mod && !$this->user->isLogged($this->table . 'admin')) {
$this->db->where("document.id IN (\n\t\t\t\tSELECT document FROM document_mod\n\t\t\t\tWHERE (document_mod.people IS NULL OR document_mod.people{$this->db->escape_int_array(array_merge(array_keys($this->user->teams), array($this->user->id)))})\n\t\t\t\t\tAND ((document_mod.mod & 1) = 1)\n\t\t\t\t)\n\t\t\t");
}
//使用INNER JOIN的方式来筛选标签，聪明又机灵。
if (isset($args['labels']) && is_array($args['labels'])) {
foreach ($args['labels'] as $id => $label_name) {
//每次连接people_label表需要定一个唯一的名字
$this->db->join("{$this->table}_label t_{$id}", "{$this->table}.id = t_{$id}.{$this->table} AND t_{$id}.label_name = '{$label_name}'", 'INNER');
}
}
if (isset($args['without_labels'])) {
foreach ($args['without_labels'] as $id => $label_name) {
$this->db->where("{$this->table}.id NOT IN (SELECT {$this->table} FROM {$this->table}_label WHERE label_name = '{$label_name}')");
}
}
if (!isset($args['company']) || $args['company'] === true) {
$this->db->where($this->table . '.company', $this->company->id);
}
if (!isset($args['display']) || $args['display'] === true) {
$this->db->where($this->table . '.display', true);
}
if (isset($args['name'])) {
$this->db->like($this->table . '.name', $args['name']);
}
if (isset($args['type']) && $args['type']) {
$this->db->where($this->table . '.type', $args['type']);
}
if (isset($args['type_is_not']) && $args['type_is_not']) {
$this->db->where($this->table . '.type !=', $args['type_is_not']);
}
if (isset($args['uid']) && $args['uid']) {
$this->db->where($this->table . '.uid', $args['uid']);
}
foreach (array('time', 'time_insert') as $date_args) {
if (!isset($args[$date_args])) {
$args[$date_args] = array_prefix($args, $date_args);
}
}
foreach (array('time', 'time_insert') as $time_field) {
if (isset($args[$time_field]['from']) && $args[$time_field]['from']) {
//默认以日期处理输入
if (strtotime($args[$time_field]['from'])) {
$args[$time_field]['from'] = strtotime($args[$time_field]['from']);
}
$this->db->where($this->table . '.' . $time_field . ' >= ', $args[$time_field]['from']);
}
if (isset($args[$time_field]['to']) && $args[$time_field]['to']) {
if (strtotime($args[$time_field]['to'])) {
$args[$time_field]['to'] = strtotime($args[$time_field]['to']);
}
//默认以纯日期处理输入
if (strtotime($args[$time_field]['to']) && (empty($args[$time_field]['input_form']) || $args[$time_field]['input_form'] !== 'datetime')) {
$args[$time_field]['to'] += 86400;
}
$this->db->where($this->table . '.' . $time_field . ' < ', $args[$time_field]['to']);
}
}
if (array_key_exists('id_in', $args)) {
if (!$args['id_in']) {
$this->db->where('FALSE', NULL, false);
} else {
$this->db->where_in($this->table . '.id', $args['id_in']);
}
}
if (isset($args['has_profiles']) && is_array($args['has_profiles'])) {
foreach ($args['has_profiles'] as $name => $content) {
if (is_integer($name)) {
$on = "{$this->table}.id = `{$name}`.{$this->table} AND `{$name}`.name{$this->db->escape_array($content)}";
} else {
$on = "{$this->table}.id = `{$name}`.{$this->table} AND `{$name}`.name = {$this->db->escape($name)} AND `{$name}`.content{$this->db->escape_array($content)}";
}
$this->db->join("{$this->table}_profile `" . $name . '`', $on, 'inner', false);
}
}
if (isset($args['where']) && $args['where']) {
$this->db->where($args['where'], NULL, FALSE);
}
//复制一个DB对象用来计算行数，因为计算行数需要运行sql，将清空DB对象中属性
$db_num_rows = clone $this->db;
if (isset($args['order_by']) && $args['order_by']) {
if (is_array($args['order_by'])) {
foreach ($args['order_by'] as $orderby) {
$this->db->order_by($orderby[0], $orderby[1]);
}
} else {
$this->db->order_by($args['order_by']);
}
}
if (isset($args['limit']) && $args['limit']) {
if ($args['limit'] === 'pagination') {
if (array_key_exists('group_by', $args)) {
$args['limit'] = $this->pagination($db_num_rows, true, $this->table . '.id');
} else {
$args['limit'] = $this->pagination($db_num_rows);
}
call_user_func_array(array($this->db, 'limit'), $args['limit']);
} elseif (is_array($args['limit'])) {
call_user_func_array(array($this->db, 'limit'), $args['limit']);
} else {
call_user_func(array($this->db, 'limit'), $args['limit']);
}
}
$result_array = $this->db->get()->result_array();
if (isset($args['get_profiles']) && $args['get_profiles']) {
foreach ($result_array as &$row) {
$profiles = array_sub($this->getProfiles($row['id']), 'content', 'name');
if ($args['get_profiles'] === true) {
$row['profiles'] = $profiles;
} elseif (is_array($args['get_profiles'])) {
foreach ($args['get_profiles'] as $key => $value) {
$profile_content = array_key_exists($value, $profiles) ? $profiles[$value] : NULL;
if (is_integer($key)) {
$row[$value] = $profile_content;
} else {
$row[$key] = $profile_content;
}
}
}
}
}
if (isset($args['get_labels']) && $args['get_labels']) {
foreach ($result_array as &$row) {
$labels = $this->getlabels($row['id']);
if ($args['get_labels'] === true) {
$row['labels'] = $labels;
} elseif (is_array($args['get_labels'])) {
foreach ($args['get_labels'] as $key => $value) {
$label_content = array_key_exists($value, $labels) ? $labels[$value] : NULL;
if (is_integer($key)) {
$row[$value] = $label_content;
} else {
$row[$key] = $label_content;
}
}
}
}
}
return $result_array;
}
/**
* @param array $args
* name: 帐目摘要
* received: 是否到帐
* reviewed: 已审核
* count: 计入创收
* project: 指定项目id
* project_labels
* project_without_labels
* project_is_active
* show_payer: 获得付款人信息payer, payer_name
* date: (预估)到账日期
* array(
* from=>日期字符串
* to=>日期字符串
* )
* contract_date: 签约日期
* array(
* from=>日期字符串
* to=>日期字符串
* )
* group_by:
* account: 根据帐目编号分组并获得帐目中创收金额received, 总额total, receive_date, receivable_date, comment
* team: 根据团组分组并获得team_name, team
* people: 根据项目人员分组并获得people_name, people,role,type
* month: 根据收款月份分组并获得month
* month_contract: 根据签约月份分组并获得month
* team: 团队
* people: 指定项目人员
* role: 指定项目人员角色(需配合group:people或people)
* account: 指定帐目编号
* sum: 获得amount求和，此操作将使除group获得的其他值失去意义
* @return array
*/
function getList(array $args = array())
{
$this->db->select('account.*')->join('project', 'project.id = account.project', 'LEFT');
if (isset($args['received'])) {
$this->db->where('account.received', (bool) intval($args['received']));
}
if (isset($args['project'])) {
$this->db->where('project', $args['project']);
}
if (isset($args['project_labels'])) {
foreach ($args['project_labels'] as $id => $label_name) {
$this->db->join("project_label t_{$id}", "account.project = t_{$id}.project AND t_{$id}.label_name = '{$label_name}'", 'inner');
}
}
if (isset($args['project_without_labels'])) {
foreach ($args['project_without_labels'] as $id => $label_name) {
$this->db->where("account.project NOT IN (SELECT project FROM project_label WHERE label_name = '{$label_name}')");
}
}
if (isset($args['project_is_active'])) {
$this->db->where('project.active', (bool) $args['project_is_active']);
}
if (isset($args['show_project'])) {
$this->db->select('project.id project, project.type project_type, project.name project_name');
if (isset($args['project_name'])) {
$this->db->like('project.name', $args['project_name']);
}
}
if (isset($args['show_payer'])) {
$this->db->join('people payer', "payer.id = account.people", 'left')->select('IF(payer.abbreviation IS NULL, payer.name, payer.abbreviation) AS payer_name,payer.id AS payer', false);
if (isset($args['payer_name'])) {
$this->db->like('payer.name', $args['payer_name']);
}
}
if (isset($args['show_account'])) {
$this->db->join('account a', 'a.id = account.account', 'inner')->select('a.name AS name, a.type AS type');
}
foreach (array('date', 'contract_date') as $date_args) {
if (!isset($args[$date_args])) {
$args[$date_args] = array_prefix($args, $date_args);
}
}
if (isset($args['date']['from']) && $args['date']['from']) {
$this->db->where("TO_DAYS(account.date) >= TO_DAYS('{$args['date']['from']}')", NULL, FALSE);
}
if (isset($args['date']['to']) && $args['date']['to']) {
$this->db->where("TO_DAYS(account.date) <= TO_DAYS('{$args['date']['to']}')", NULL, FALSE);
}
if (isset($args['contract_date']['from']) && $args['contract_date']['from']) {
$this->db->where("TO_DAYS(project.time_contract) >= TO_DAYS('{$args['contract_date']['from']}')", NULL, FALSE);
}
if (isset($args['contract_date']['to']) && $args['contract_date']['to']) {
$this->db->where("TO_DAYS(project.time_contract) <= TO_DAYS('{$args['contract_date']['to']}')", NULL, FALSE);
}
if (isset($args['team'])) {
$team = intval($args['team']);
$this->db->where('account.team', $team);
}
if (isset($args['people'])) {
$people = intval($args['people']);
$this->db->join('project_people', "project_people.project = project.id AND project_people.people = {$people}", 'inner');
if (isset($args['role'])) {
$this->db->select('weight', false);
if (is_array($args['role'])) {
$this->db->where_in('project_people.role', $args['role']);
} else {
$this->db->where('project_people.role', $args['role']);
}
}
}
if (isset($args['account'])) {
$account = intval($args['account']);
$this->db->where('account.account', $account);
}
if (isset($args['amount'])) {
$this->db->where('account.amount', $args['amount']);
}
if (isset($args['reviewed'])) {
$this->db->where('account.received', (bool) intval($args['reviewed']));
}
if (isset($args['count'])) {
$this->db->where('account.count', (bool) intval($args['count']));
unset($args['count']);
}
if (isset($args['group_by'])) {
if ($args['group_by'] === 'account') {
$this->db->group_by('account.account')->select('
MAX(account.type) AS type,
SUM(IF(account.received,account.amount,0)) AS received_amount,
SUM(IF(account.received,0,account.amount)) AS total_amount,
SUM(IF(account.received,0,account.amount)) - SUM(IF(account.received,account.amount,0)) AS receivable_amount,
MAX(IF(account.received,account.date,NULL)) AS received_date,
MAX(IF(account.received,NULL,account.date)) AS receivable_date,
GROUP_CONCAT(account.comment) AS comment
', false);
} elseif ($args['group_by'] === 'team') {
$this->db->group_by('account.team')->join('team', 'team.id = account.team', 'inner')->select('team.name AS team_name, team.id AS team');
} elseif ($args['group_by'] === 'people') {
if ($args['role']) {
$this->db->join('project_people', 'project_people.project = account.project', 'inner')->select('project_people.role, project_people.weight, account.amount * project_people.weight `amount`', false);
if (is_array($args['role'])) {
$this->db->where_in('project_people.role', $args['role']);
} else {
$this->db->where('project_people.role', $args['role']);
}
} else {
$this->db->join('project_people', 'project_people.project = account.project', 'inner');
}
$this->db->join('people', 'people.id = project_people.people', 'inner')->group_by('project_people.people')->select('people.name AS people_name, people.id AS people');
} elseif ($args['group_by'] === 'month') {
$this->db->group_by('LEFT(account.date,7)', false)->order_by('month')->select('LEFT(account.date,7) AS month', false);
} elseif ($args['group_by'] === 'month_contract') {
$this->db->group_by('LEFT(project.time_contract,7)', false)->order_by('month')->select('LEFT(project.time_contract,7) AS month', false);
} elseif ($args['group_by'] === 'project') {
$this->db->group_by('account.project')->select('account.project');
}
if (isset($args['having'])) {
$this->db->having($args['having']);
}
}
if (isset($args['sum']) && $args['sum'] === true) {
array_remove_value($this->db->ar_select, 'account.*');
array_remove_value($this->db->ar_select, '`amount`', true);
if (isset($args['role'])) {
if (isset($args['ten_thousand_unit']) && $args['ten_thousand_unit']) {
$this->db->select('ROUND(SUM(account.amount * weight)/1E4,1) `sum`', false);
} else {
$this->db->select('ROUND(SUM(account.amount * weight)) `sum`', false);
}
} else {
if (isset($args['ten_thousand_unit']) && $args['ten_thousand_unit']) {
$this->db->select('ROUND(SUM(account.amount)/1E4,1) `sum`', false);
} else {
$this->db->select('ROUND(SUM(account.amount)) `sum`', false);
}
}
//$this->db->having('sum >',0);
} else {
if (isset($args['ten_thousand_unit']) && $args['ten_thousand_unit']) {
$this->db->select('ROUND(account.amount/1E4,1) `amount`', false);
}
}
return parent::getList($args);
}
/**
*
* @param array $args
* project: get schedule only under this project
* project_type
* project_labels: 仅获取带有给定标签的事务的日程
* people:
* people_type:
* people_labels: 仅获取带有给定标签的人员的相关日程
* group_by
* people
* in_todo_list 仅在指定people或group_by people的时候有效
* enrolled 仅在指定people或group_by people的时候有效
* completed 仅在指定people或group_by people的时候有效
* time array or boolean 时间段的范围
* false
* array(
* from=>timestamp/date string/datetime string
* to=>timestamp/date string/datetime string
* input_format=>timestamp, date(default)
* date_form=>mysql date form string, or false (default: '%Y-%m-%d')
* )
* in_project_of_people bool
* show_creater
* show_project
* id_in_set
* @return array
*/
function getList(array $args = array())
{
$this->db->select('schedule.*');
if (isset($args['project']) && $args['project']) {
$this->db->where('schedule.project', $args['project']);
}
if (isset($args['project_type']) && $args['project_type']) {
$this->db->where("schedule.project IN (SELECT id FROM project WHERE type ={$this->db->escape($args['project_type'])} AND company = {$this->company->id} )");
}
if (isset($args['project_labels']) && $args['project_labels']) {
foreach ($args['project_labels'] as $id => $label_name) {
$this->db->join("project_label t_{$id}", "schedule.project = t_{$id}.project AND t_{$id}.label_name = {$this->db->escape($label_name)}", 'inner');
}
}
//判断需要内联schedule_people表的参数
if (isset($args['people']) || isset($args['people_labels']) || isset($args['group_by']) && $args['group_by'] === 'people') {
$this->db->join('schedule_people', 'schedule_people.schedule = schedule.id', 'inner');
}
//依赖schedule_people表
//TODO 判断多个人同时属于一个日程，并区分对待人员状态（如统计一个律师对一个客户的时间）
if (isset($args['people']) && $args['people']) {
$this->db->where('schedule_people.people' . $this->db->escape_int_array($args['people']), NULL, FALSE);
}
//依赖schedule_people表
if (isset($args['people_type']) && $args['people_type']) {
$this->db->where("schedule.id IN (\n\t\t\t\tSELECT schedule FROM schedule_people WHERE people IN (\n\t\t\t\t\tSELECT id FROM people WHERE type = {$this->db->escape($args['people_type'])}\n\t\t\t\t)\n\t\t\t)");
}
//依赖schedule_people表
if (isset($args['people_labels']) && $args['people_labels']) {
foreach ($args['people_labels'] as $id => $label_name) {
$this->db->join("people_label t_{$id}", "schedule_people.people = t_{$id}.people AND t_{$id}.label_name = {$label_name}", 'inner');
}
}
if (isset($args['group_by'])) {
//依赖schedule_people表
//TODO 判断多个人同时属于一个日程，并区分对待人员状态（如统计一个律师对一个客户的时间）
if ($args['group_by'] === 'people') {
if (isset($args['people_is_staff']) && $args['people_is_staff']) {
$this->db->where('schedule_people.people IN (SELECT id FROM staff)', NULL, false);
}
$this->db->group_by('schedule_people.people')->join('people', 'people.id = schedule_people.people', 'inner')->select('people.id people, people.name people_name');
}
}
if ((isset($args['people']) || isset($args['group_by']) && $args['group_by'] === 'people') && isset($args['in_todo_list'])) {
//依赖人员参数
$this->db->where('schedule_people.in_todo_list', $args['in_todo_list']);
}
if ((isset($args['people']) || isset($args['group_by']) && $args['group_by'] === 'people') && isset($args['enrolled'])) {
//依赖人员参数
$this->db->where('schedule_people.enrolled', $args['enrolled']);
}
if (isset($args['people']) || isset($args['group_by']) && $args['group_by'] === 'people') {
//依赖人员参数
!isset($args['deleted']) && ($args['deleted'] = false);
$this->db->where('schedule_people.deleted', $args['deleted']);
}
if (isset($args['completed'])) {
$this->db->where('schedule.completed', $args['completed']);
}
if (!isset($args['time'])) {
$args['time'] = array_prefix($args, 'time');
}
if ($args['time']) {
if ($args['time'] === false) {
$this->db->where(array('schedule.start' => NULL, 'schedule.end' => NULL));
}
if (isset($args['time']['from']) && $args['time']['from']) {
if (isset($args['time']['input_format']) && $args['time']['input_format'] !== 'timestamp') {
$args['time']['from'] = strtotime($args['time']['from']);
}
$this->db->where('schedule.start >=', $args['time']['from']);
}
if (isset($args['time']['to']) && $args['time']['to']) {
if (isset($args['time']['input_format']) && $args['time']['input_format'] !== 'timestamp') {
$args['time']['to'] = strtotime($args['time']['to']);
}
if (isset($args['time']['input_format']) && $args['time']['input_format'] === 'date') {
$args['time']['to'] += 86400;
}
$this->db->where('schedule.end <', $args['time']['to']);
}
if (!isset($args['date_form'])) {
$args['date_form'] = '%Y-%m-%d';
}
if ($args['date_form'] !== false) {
$this->db->select(array("FROM_UNIXTIME(schedule.start, '{$args['date_form']}') `start`", "FROM_UNIXTIME(schedule.end, '{$args['date_form']}') `end`", "FROM_UNIXTIME(schedule.deadline, '{$args['date_form']}') `deadline`"), false);
}
unset($args['time']);
}
if (isset($args['in_project_of_people']) && $args['in_project_of_people']) {
$this->db->where("\n\t\t\t\tschedule.project IN (\n\t\t\t\t\tSELECT project FROM project_people WHERE people{$this->db->escape_int_array($args['in_project_of_people'])}\n\t\t\t\t)", NULL, FALSE);
}
if (isset($args['show_project']) && $args['show_project']) {
$this->db->join('project', 'project.id = schedule.project', 'left')->select('project.name project_name');
}
if (isset($args['show_creater']) && $args['show_creater']) {
$this->db->join('people creater', 'creater.id = schedule.uid', 'inner')->select('creater.id creater, creater.name creater_name');
}
if (isset($args['id_in_set'])) {
$args['id_in'] = $args['id_in_set'];
if ($args['id_in_set']) {
$this->db->order_by("FIELD(schedule.id, " . implode(', ', $args['id_in_set']) . ")", '', false);
$args['order_by'] = false;
}
}
if (isset($args['sum']) && $args['sum']) {
array_remove_value($this->db->ar_select, 'schedule.*');
array_remove_value($this->db->ar_select, '`deadline`', true);
array_remove_value($this->db->ar_select, '`start`', true);
array_remove_value($this->db->ar_select, '`end`', true);
$this->db->select('SUM(IF(hours_checked IS NULL, hours_own, hours_checked)) sum', false);
}
$schedules = parent::getList($args);
!isset($args['sum']) && array_walk($schedules, function (&$schedule, $index) {
if ($schedule['completed']) {
$schedule['color'] = '#36C';
} else {
if ($schedule['start'] < $this->date->now) {
$schedule['color'] = '#555';
} else {
$schedule['color'] = '#E35B00';
}
}
$schedule['all_day'] = (bool) $schedule['all_day'];
$schedule['completed'] = (bool) $schedule['completed'];
}, $this);
return $schedules;
}
