ZZCS/coding/application/financeManager/groupFinance.class.php

<?php

/**
 * User: yimazsq
 * Date: 2016/10/28
 * Time: 11:17
 * 组合线路票种相关
 */
class groupFinance extends base
{
    /**
     * 获取出团报账列表
     * @param $param
     */
    public function getFinanceList($param)
    {
        #region 获取渠道权限
        $user_id = $this->user_id;
        $opera_org_id_sql = "select opera_org_id from base_user where id = " . $user_id . " and cancel_flag = 0";
        $opera_org_id_result = $this->query($opera_org_id_sql);
        $opera_org_id = $opera_org_id_result[0]['opera_org_id'];
        if ($opera_org_id == '') {
            $and_sql = '';
        } else {
            $and_sql = " and l.org_id in (" . $opera_org_id . ") ";
        }
        #endregion

        $page_size = isset($param['page_size']) ? trim($param['page_size']) : false;//每页展示条数
        $current_page = isset($param['current_page']) ? trim($param['current_page']) : false;//当前页
        $line_code = isset($param['line_code']) ? (empty($param['line_code']) ? "''" : "'" . trim($param['line_code']) . "'") : false;//线路编码或名称
        $start_date = isset($param['start_date']) ? (empty($param['start_date']) ? date('Y-m-d', time()) : trim($param['start_date'])) : false;//出发日期
        $end_date = isset($param['end_date']) ? (empty($param['end_date']) ? date('Y-m-d', time()) : trim($param['end_date'])) : false;//截止日期
        $bill_type = isset($param['bill_type']) ? trim($param['bill_type']) : false;//账单类型
        if (!$start_date || !$end_date || false === $bill_type) {
            $json['code'] = '2';
            $json['info'] = '缺少必要参数';
            return $json;
        }
        if (date('Y-m-d', strtotime($start_date)) > date('Y-m-d', time()) || date('Y-m-d', strtotime($end_date)) > date('Y-m-d', time())) {
            $end_date = date('Y-m-d', time());
        }
        $start_row = ($current_page - 1) * $page_size;

        $sql = "SELECT temp.code_id,temp.run_id,temp.run_bus_order_id as bus_order_id,temp.run_date,temp.run_time,group_concat(temp.line_code_name) AS line_code_name,temp.send_bus_no as bus_no,temp.send_tour_guide_name as guide_name,temp.send_driver_name as driver_name,concat(temp.seat_count,'座') as seat_count,temp.saled_count,temp.finance_type,temp.finance_status
				FROM(SELECT m.run_id,m.run_bus_order_id,m.run_date,m.run_time,
				            m.parent_prod_id,
							(SELECT concat(line_code, ' ', line_name) FROM opera_line WHERE line_id in (select parent_prod_id from order_main where order_id = m.parent_order_id)) AS line_code_name,
							b.send_bus_no,b.send_tour_guide_name,b.seat_count,b.saled_count,b.id AS code_id,b.finance_status,b.send_driver_name,
							(SELECT type_name FROM dict_type WHERE id = b.finance_status) AS finance_type
						FROM order_main AS m,run_bus AS b
						WHERE m.cancel_flag = 0 AND b.cancel_flag = 0 AND m.run_date between '{$start_date}' and '{$end_date}' AND	m.run_id = b.run_id AND m.run_bus_order_id = b.bus_order_id AND m.run_id != 0 AND m.cancel_flag = 0 AND b.cancel_flag = 0
						      AND m.run_id not in (SELECT main.run_id from run_main main,run_bus bus where main.run_id=bus.run_id and main.PROD_ID>0 AND bus.group_line_flag=1)
						GROUP BY m.run_id,m.run_bus_order_id,m.parent_prod_id,run_date,run_time
					) AS temp,
					opera_line as l
                WHERE temp.parent_prod_id = l.line_id 
                      " . $and_sql ."
                      AND temp.run_date between '{$start_date}' and '{$end_date}'
                      AND IF(TRIM({$line_code})='',0=0,(temp.line_code_name LIKE CONCAT('%',{$line_code},'%')  OR code_id LIKE CONCAT('%',{$line_code},'%'))) 
                      AND IF({$bill_type}=-1,0=0,temp.finance_status ={$bill_type}) 
				GROUP BY temp.run_id,temp.run_bus_order_id,temp.run_date,temp.run_time
				order by run_date desc";

        $sql_query = $sql . " limit " . $start_row . "," . $page_size;       //查询数据列表
        $sql_count = "SELECT COUNT(*) as count from(" . $sql . ") as a";          //查询总条数
        $finance_list = $this->query($sql_query);
        if (false === $finance_list) {
            $json['code'] = '0';
            $json['info'] = '没有相关数据信息';
            $json['finance_list'] = array();
            $json['page']['page_size'] = $page_size;
            $json['page']['current_page'] = $current_page;
            $json['page']['total_count'] = '0';
            $json['page']['total_page'] = '0';
            return $json;
        }
        $total_count = $this->query($sql_count);
        $total_count = $total_count[0]['count'];
        $total_page = ceil($total_count / $page_size);
        $user_id = $this->user_id;
        if ($user_id == 143 || $user_id == 144) {
            $finance_status = 'check';
        } else {
            $finance_status = 'update';
        }
        $json['code'] = '0';
        $json['info'] = '返回列表信息成功';
        $json['finance_list'] = $finance_list;
        $json['finance_status'] = $finance_status;
        $json['page']['page_size'] = $page_size;
        $json['page']['current_page'] = $current_page;
        $json['page']['total_count'] = $total_count;
        $json['page']['total_page'] = $total_page;
        return $json;

    }

    /**
     * 报账配置数据
     * @return mixed
     */
    public function getAddFinanceBase($param)
    {
        $run_id = isset($param['run_id']) ? trim($param['run_id']) : false;//班次id
        $bus_order_id = isset($param['bus_order_id']) ? trim($param['bus_order_id']) : false;//车号
        if (!$run_id || !$bus_order_id) {
            $json['code'] = '2';
            $json['info'] = '缺少必要参数';
            return $json;
        }
        $run_bus_info = $this->getFinanceByRun($run_id, $bus_order_id);

        //1.收支类型
        $payment_type = Dictionary::payment_type();
        //2.费用科目
        $fee_type_sql = 'SELECT
                                id,
                                type_name
                            FROM
                                dict_type
                            WHERE
                                parent_id = 356';
        $fee_type = $this->query($fee_type_sql);
        //3.结算单位
        $supply_list_sql = 'SELECT
                                id,
                                supplier_name
                            FROM
                                base_supplier
                            WHERE
                                supplier_type = 187
                            AND cancel_flag = 0';
        $supply_list = $this->query($supply_list_sql);
        //4.结算方式
        $settle_type = Dictionary::settle_type();
        $json['code'] = '0';
        $json['info'] = '返回配置数据成功';
        $json['payment_type'] = $payment_type;
        $json['fee_type'] = $fee_type;
        $json['supply_list'] = $supply_list;
        $json['settle_type'] = $settle_type;
        $json['run_bus_info'] = $run_bus_info;
        return $json;
    }

    /**
     * 提交报账
     * @param $param
     * @return mixed
     */
    public function addFinance($param)
    {
        $run_id = isset($param['run_id']) ? $param['run_id'] : false;//班次id
        $bus_order_id = isset($param['bus_order_id']) ? $param['bus_order_id'] : false;//车次id
        $finance_str = isset($param['finance_str']) ? $param['finance_str'] : false;//车次id
        $user_id = $this->user_id;
        $create_time = date('Y-m-d', time());
        if (!$run_id || !$bus_order_id || !$finance_str) {
            $json['code'] = '2';
            $json['info'] = "缺少必要参数";
            return $json;
        }
        $finance_list = substr($finance_str, 1, -1);
        $finance_list = explode('}{', $finance_list);
        $status = 0;
        foreach ($finance_list as $key => $value) {
            $value = substr($value, 0, -1);
            $finance_one = explode(',', $value);
            foreach ($finance_one as $k => $v) {
                if ($v == '') {
                    $status = 1;
                    break;
                }
            }
            $finance_list[$key] = $finance_one;
        }
        //有必填项没填
        if ($status == 1) {
            $json['code'] = '1';
            $json['info'] = '有必填项为空';
            return $json;
        }
        $insert_sql = 'INSERT INTO run_finance (create_user_id,create_time,update_user_id,update_time,cancel_flag,run_id,bus_order_id,payment_type,fee_type,supplier_id,unit_price,num,total_price,
                        finance_date,finance_man,settle_type,sign_order_id,remark) VALUES';
        foreach ($finance_list as $key => $v) {
            $value1[] = '(' . $user_id . ',\'' . $create_time . '\',' . $user_id . ',\'' . $create_time . '\',0,' . $run_id . ',' . $bus_order_id . ',' . $v[0] . ',' . $v[1] . ',' . $v[2] . ',\'' . $v[3] . '\',' . $v[4] . ',\'' . $v[5] . '\',\'' . $v[6] . '\',\'' . $v[7] . '\',' . $v[8] . ',\'' . $v[9] . '\',\'' . $v[10] . '\')';
        }
        $insert_sql .= implode(',', $value1);
        $update_sql = "UPDATE run_bus
                        SET finance_status = 137,
                         update_user_id = " . $user_id . ",
                         update_time = '" . $create_time . "'
                        WHERE
                            run_id = " . $run_id . "
                        AND bus_order_id = " . $bus_order_id . "
                        AND cancel_flag = 0";
        $insert_sql .= ';' . $update_sql;
        $result = $this->exec($insert_sql);
        if ($result) {
            $json['code'] = '0';
            $json['info'] = '提交报账成功';
        } else {
            $json['code'] = '1';
            $json['info'] = '数据库原因，提交报账失败';
        }
        return $json;
    }


    /**
     * 通过run_id和bus_order_id获取详情
     * @param $run_id
     * @param $bus_order_id
     * @return array
     */
    private function getFinanceByRun($run_id, $bus_order_id)
    {
        $sql = "SELECT
                    run_id,run_bus_order_id as bus_order_id,run_date,run_time,
                    group_concat(line_code_name) AS line_code_name,
                    saled_count
                FROM
                    (
                        SELECT
                            m.run_id,m.run_bus_order_id,m.run_date,m.run_time,
                            (SELECT concat(line_code, ' ', line_name) FROM opera_line WHERE line_id = m.parent_prod_id) AS line_code_name,
                            b.saled_count
                        FROM
                            order_main AS m,
                            run_bus AS b
                        WHERE
                            m.run_id = b.run_id
                        AND m.run_bus_order_id = b.bus_order_id
                        AND m.run_id != 0
                        AND m.cancel_flag = 0
                        AND b.cancel_flag = 0
                        AND m.run_id = " . $run_id . "
                        AND b.bus_order_id = " . $bus_order_id . "
                        GROUP BY
                            m.run_id,m.run_bus_order_id,m.parent_prod_id,run_date,run_time
                    ) AS temp 
                GROUP BY
                    run_id,run_bus_order_id,run_date,run_time
                LIMIT  1";
        $run_bus_info = $this->query($sql);
        $run_bus_info = $run_bus_info[0];
        return $run_bus_info;
    }

    /**
     * 获取报账详情
     * @param $param
     * @return mixed
     */
    public function getFinanceInfo($param)
    {
        $run_id = isset($param['run_id']) ? trim($param['run_id']) : false;//班次id
        $bus_order_id = isset($param['bus_order_id']) ? trim($param['bus_order_id']) : false;//车号
        if (!$run_id || !$bus_order_id) {
            $json['code'] = '2';
            $json['info'] = '缺少必要参数';
            return $json;
        }
        $run_bus_array = array('run_id' => $run_id, 'bus_order_id' => $bus_order_id);
        $data_info = $this->getAddFinanceBase($run_bus_array);
        unset($data_info['code']);
        unset($data_info['info']);
        $sql = "SELECT
                    id,run_id,bus_order_id,payment_type,fee_type,supplier_id,unit_price,num,
                    total_price,finance_date,finance_man,settle_type,sign_order_id,remark
                FROM
                    run_finance
                WHERE
                    cancel_flag = 0
                AND run_id = " . $run_id . "
                AND bus_order_id = " . $bus_order_id;
        $finance_list = $this->query($sql);
        $json['code'] = '0';
        $json['info'] = '获取报账信息成功';
        $json['finance_list'] = $finance_list;
        $json['data_info'] = $data_info;
        return $json;
    }

    /**
     * 修改或审核报账信息
     * @param $param
     */
    public function updateFinanceInfo($param)
    {
        $operate = isset($param['operate']) ? $param['operate'] : false;//修改update,审核check
        $run_id = isset($param['run_id']) ? $param['run_id'] : false;//班次id
        $bus_order_id = isset($param['bus_order_id']) ? $param['bus_order_id'] : false;//车次id
        $finance_str = isset($param['finance_str']) ? $param['finance_str'] : false;//报账的字符串
        $user_id = $this->user_id;
        $update_time = date('Y-m-d H:i:s', time());
        if (!$run_id || !$bus_order_id || !$finance_str || !$operate) {
            $json['code'] = '2';
            $json['info'] = "缺少必要参数";
            return $json;
        }
        $data_array = substr($finance_str, 1, -1);
        $data_array = explode('}{', $data_array);
        $i = -1;
        foreach ($data_array as $k => $v) {
            $v = explode(',', $v);
            if ($v[0] == 0) {
                $data_finance_array[$i]['id'] = $v[0];
                $data_finance_array[$i]['run_id'] = $run_id;
                $data_finance_array[$i]['bus_order_id'] = $bus_order_id;
                $data_finance_array[$i]['payment_type'] = $v[1];
                $data_finance_array[$i]['fee_type'] = $v[2];
                $data_finance_array[$i]['supplier_id'] = $v[3];
                $data_finance_array[$i]['unit_price'] = $v[4];
                $data_finance_array[$i]['num'] = $v[5];
                $data_finance_array[$i]['total_price'] = $v[6];
                $data_finance_array[$i]['finance_date'] = $v[7];
                $data_finance_array[$i]['finance_man'] = $v[8];
                $data_finance_array[$i]['settle_type'] = $v[9];
                $data_finance_array[$i]['sign_order_id'] = $v[10];
                $data_finance_array[$i]['remark'] = $v[11];
            } else {
                $data_finance_array[$v[0]]['id'] = $v[0];
                $data_finance_array[$v[0]]['run_id'] = $run_id;
                $data_finance_array[$v[0]]['bus_order_id'] = $bus_order_id;
                $data_finance_array[$v[0]]['payment_type'] = $v[1];
                $data_finance_array[$v[0]]['fee_type'] = $v[2];
                $data_finance_array[$v[0]]['supplier_id'] = $v[3];
                $data_finance_array[$v[0]]['unit_price'] = $v[4];
                $data_finance_array[$v[0]]['num'] = $v[5];
                $data_finance_array[$v[0]]['total_price'] = $v[6];
                $data_finance_array[$v[0]]['finance_date'] = $v[7];
                $data_finance_array[$v[0]]['finance_man'] = $v[8];
                $data_finance_array[$v[0]]['settle_type'] = $v[9];
                $data_finance_array[$v[0]]['sign_order_id'] = $v[10];
                $data_finance_array[$v[0]]['remark'] = $v[11];
            }
        }
        $run_bus = array('run_id' => $run_id, 'bus_order_id' => $bus_order_id);
        $db_array = $this->getFinanceInfo($run_bus);
        $db_array = $db_array['finance_list'];
        foreach ($db_array as $k => $v) {
            $db_finance_array[$v['id']] = $v;
        }
        $update_array = $this->compare($data_finance_array, $db_finance_array);

        //操作数据库
        if (isset($update_array['update']) && count($update_array['update']) > 0)//修改
        {
            $update_finance_array = array();
            foreach ($update_array['update'] as $key => $value) {
                unset($value['id']);
                $set_str = $this->getUpdateSet($value);
                $update_finance_array[] = "UPDATE run_finance
                                           SET update_user_id = " . $user_id . ",update_time = '" . $update_time . "'," . $set_str . "
                                           WHERE id = " . $key;
            }
            $update_finance_sql = implode(";", $update_finance_array);
            $update_sql[] = $update_finance_sql;
        }

        if (isset($update_array['insert']) && count($update_array['insert']) > 0)//增加
        {
            $insert_finance_sql = 'INSERT INTO run_finance (create_user_id,create_time,update_user_id,update_time,cancel_flag,run_id,bus_order_id,payment_type,fee_type,supplier_id,unit_price,num,total_price,finance_date,finance_man,
                                        settle_type,sign_order_id,remark) VALUES';
            foreach ($update_array['insert'] as $key => $v) {
                $finance_sql_array[] = "(" . $user_id . ",'" . $update_time . "'," . $user_id . ",'" . $update_time . "',0," . $v['run_id'] . "," . $v['bus_order_id'] . "," . $v['payment_type'] . "," . $v['fee_type'] . "," . $v['supplier_id'] . ",'" . $v['unit_price'] . "'," . $v['num'] . ",'" . $v['total_price'] . "','" . $v['finance_date'] . "','" . $v['finance_man'] . "'," . $v['settle_type'] . ",'" . $v['sign_order_id'] . "','" . $v['remark'] . "')";
            }
            $insert_finance_sql .= implode(",", $finance_sql_array);
            $update_sql[] = $insert_finance_sql;
        }

        if (isset($update_array['delete']) && count($update_array['delete']) > 0)//删除
        {
            $delete_finance_array = array();
            foreach ($update_array['delete'] as $key => $value) {
                $delete_finance_array[] = "UPDATE run_finance
                                                SET update_user_id = " . $user_id . ",update_time = '" . $update_time . "',cancel_flag = 1
                                                WHERE
                                                    id = " . $key;
            }
            $delete_finance_sql = implode(";", $delete_finance_array);
            $update_sql[] = $delete_finance_sql;
        }

        if ($operate == 'check') {  //管理员审核（包括修改）
            $update_run_bus_sql = "UPDATE run_bus
                                    SET finance_status = 341,update_user_id = " . $user_id . ",update_time = '" . $update_time . "'
                                    WHERE
                                        run_id = " . $run_id . " AND bus_order_id = " . $bus_order_id . " AND cancel_flag = 0";
            $update_sql[] = $update_run_bus_sql;
            $operate = '审核';
        } else {
            $operate = '修改';
        }

        $update_sql = implode(';', $update_sql);
        zzcsUtils::writeLog($update_sql);
        $result = $this->exec($update_sql);
        if ($result) {
            $json['code'] = '0';
            $json['info'] = $operate . '报账成功';
        } else {
            $json['code'] = '1';
            $json['info'] = '数据库异常，' . $operate . '报账失败';
        }
        return $json;
    }

    //比较数组(前台数组，数据库数组)
    private function compare($arr, $db_arr)
    {
        $result = array();
        if (count($db_arr) == 0 && count($arr) == 0) {
            return $result;
        } elseif (count($db_arr) == 0) {
            $result['insert'] = $arr;
            return $result;
        } elseif (count($arr) == 0) {
            $result['delete'] = $db_arr;
            return $result;
        } else {
            foreach ($arr as $key => $value) {
                if (isset($db_arr[$key])) {
                    if ($arr != $db_arr) {
                        $result['update'][$key] = $value;
                    }
                } else {
                    $result['insert'][$key] = $value;
                }
            }
            foreach ($db_arr as $key => $value) {
                if (!isset($arr[$key])) {
                    $result['delete'][$key] = $value;
                }
            }
            return $result;
        }
    }

    //获取update操作中的set数据
    private function getUpdateSet($arr)
    {
        $set_array = array();
        foreach ($arr as $k => $v) {
            $set_array[] = $k . " = '" . $v . "'";
        }
        $set_str = implode(",", $set_array);
        return $set_str;
    }
}