mes-sdai-server-api/application/api/controller/OvertimeVerification.php

<?php

namespace app\api\controller;

use app\common\controller\Api;
use \think\Request;
use \think\Db;
/**
 * 计件员工加班费核查接口
 */
class OvertimeVerification extends Api
{
    protected $noNeedLogin = ['*'];
    protected $noNeedRight = ['*'];

    /**
     * 首页
     *
     */
    public function index()
    {
        $this->success('请求成功');
    }

    /**
     * 计件员工加班费核查侧边栏
     * @ApiMethod (GET)
     */
    public function getTab()
    {
        //get请求
        if(!$this->request->isGet()){
            $this->error('请求方式错误');
        }
        $res=db('绩效工资汇总')
            ->field('DATE_FORMAT(`sczl_rq`, "%Y%m") as sys_ny,bh')
            ->group('DATE_FORMAT(`sczl_rq`, "%Y%m"),bh')
            ->whereTime('sczl_rq', '>=', strtotime('-14 months'))
            ->order('sczl_rq desc')
            ->select();
        foreach($res as $v){
            $arr[$v['sys_ny'].'（'][]=$v['bh'];
        }
        $rs = db('人事_基本资料')->column('员工编号,所在部门');
        foreach($arr as $k=>$v){
            foreach($v as $value){
                $data[$k.count($v).'人）'][rtrim($rs[$value])][]=rtrim($rs[$value]);
            }
            $j=0;
            foreach($data[$k.count($v).'人）'] as $keys=>$values){
                $data[$k.count($v).'人）']['bm'][$j]=$keys;
                $data[$k.count($v).'人）'][$keys]=count($values);
                $j++;
            }
            usort($data[$k.count($v).'人）']['bm'], function($a, $b) {
                $order = array(
                    '胶印车间',
                    '凹印车间',
                    '丝印车间',
                    '模切车间',
                    '检验车间',
                    '精品试验车间',
                    '品保部',
                    '人力资源部',
                    '生产部',
                    '营销部'
                );
                $a_index = array_search($a, $order);
                $b_index = array_search($b, $order);
                return $a_index - $b_index;
            });
            foreach($data[$k.count($v).'人）']['bm'] as &$va){
                $va=$va.'（'.$data[$k.count($v).'人）'][$va].'人）';
            }
            $data[$k.count($v).'人）']=$data[$k.count($v).'人）']['bm'];
        }
        $i=0;
        foreach($data as $k=>$v){
            $datas[$i]['label']=$k;
            $gdbh=substr($k,0,strpos($k,'（'));
            foreach($v as $key=>$value){
                $datas[$i]['children'][$key]['label']=$value;
                $datas[$i]['children'][$key]['gdbh']=$gdbh;
            }
            $i++;
        }
        $this->success('成功',$datas);
    }
    

    /**
     * 计件员工加班费核查上侧列表
     * @ApiMethod (GET)
     * @param string $date 年月
     * @param string $department 部门
     */
    public function getList()
    {
        //get请求
        if(!$this->request->isGet()){
            $this->error('请求方式错误');
        }
        $req = $this->request->param();
        $res=db('绩效工资汇总')->alias('j')
            ->join('人事_基本资料 r','r.员工编号=j.bh')
            ->group('j.bh')
            ->where('j.sys_ny',$req['date'])
            ->where(['r.所在部门'=>['like',$req['department'].'%']])
            ->column('CONCAT(j.bh, "-", DATE_FORMAT(j.sczl_rq,"%Y.%m")) AS rq_bh,j.bh,rtrim(j.xm) as 员工姓名,r.工资表类别,DATE_FORMAT(r.聘用日期,"%Y.%m.%d") as 聘用日期,j.法定天数,
            sum(j.达标定额) as 当月定额,(sum(j.班组车头产量*j.工序难度系数)+sum(j.班组换算产量)) as 月度合计产量,sum(j.个人计件工资) as 个人计件工资,
            sum(j.个人加班工资) as 个人加班工资,sum(j.异常停机工时) as 月计时时数,LEFT(j.sczl_rq, 7) as sczl_rq');

        $year=substr($req['date'],0,4);
        $month=substr($req['date'],-2);
        $start_time = $year . '-' . $month . '-01 00:00:00';
        $end_time = date('Y-m-t', strtotime("$year-$month-01")) . ' 23:59:59';
        $arr = [];
        $columns = ['wgjs_bh1', 'wgjs_bh2', 'wgjs_bh3', 'wgjs_bh4', 'wgjs_bh5', 'wgjs_bh6'];
        for ($i = 1; $i <= 6; $i++) {
            $column = $columns[$i-1];
            $r = db('db_wgjs')
                ->field("DATE_FORMAT(wgjs_rq,'%Y.%m') as wgjs_rq, $column as wgjs_bh, wgjs_js$i as wgjs_js, wgjs_冲定额$i as wgjs_冲定额")
                ->join('人事_基本资料', "人事_基本资料.员工编号=db_wgjs.$column")
                ->where(['wgjs_rq' => ['between', "$start_time,$end_time"], '人事_基本资料.所在部门' => ['like', $req['department'] . '%']])
                ->select();

            $arr = array_merge($arr, $r);
        }

        $list = [];
        foreach($arr as $v){
            if(isset($list[$v['wgjs_bh'].'-'.$v['wgjs_rq']])){
                if($v['wgjs_冲定额']=='是'){
                    $list[$v['wgjs_bh'].'-'.$v['wgjs_rq']]['月计时时数']+=$v['wgjs_js'];
                }
            }else{
                $list[$v['wgjs_bh'].'-'.$v['wgjs_rq']]['bh']=$v['wgjs_bh'];
                $list[$v['wgjs_bh'].'-'.$v['wgjs_rq']]['sczl_rq']=$v['wgjs_rq'];
                if($v['wgjs_冲定额']=='是'){
                    $list[$v['wgjs_bh'].'-'.$v['wgjs_rq']]['月计时时数']=$v['wgjs_js'];
                }else{
                    $list[$v['wgjs_bh'].'-'.$v['wgjs_rq']]['月计时时数']=0;
                }
            }
        };

        //获取每个员工计时补差天数
        $arr1 = db('绩效工资汇总')->alias('j')
            ->field('j.bh,j.sczl_rq')
            ->join('人事_基本资料 r','r.员工编号=j.bh')
            ->group('j.bh,j.sczl_rq')
            ->where('j.sys_ny',$req['date'])
            ->where('j.达标定额','<>',0)
            ->where(['r.所在部门'=>['like',$req['department'].'%']])
            ->buildSql();
        $arr1 = db()->table($arr1 . ' t')
            ->group('t.bh, LEFT(t.sczl_rq, 7)')
            ->column('t.bh,count(LEFT(t.sczl_rq, 7)) as num');

        //查询每条记录
        $arr2 = db('绩效工资汇总')->alias('j')
            ->field('j.bh, DATE_FORMAT(j.sczl_rq,"%Y.%m") as sczl_rq,
            sum(j.达标定额) as 达标定额, j.Rate, j.千件工价')
            ->join('人事_基本资料 r','r.员工编号=j.bh')
            ->where('j.sys_ny',$req['date'])
            ->where('j.达标定额','<>',0)
            ->where(['r.所在部门'=>['like',$req['department'].'%']])
            ->group('j.bh,j.sczl_rq,j.sczl_gdbh')
            ->select();
        //计算每条记录的计时补差
        $brr = [];
        foreach($arr2 as $v){
            $yjsss1 = array_key_exists($v['bh'].'-'.$v['sczl_rq'],$list) ? $list[$v['bh'].'-'.$v['sczl_rq']]['月计时时数'] : 0;
            $yjsss2 = array_key_exists($v['bh'].'-'.$v['sczl_rq'],$res) ? $res[$v['bh'].'-'.$v['sczl_rq']]['月计时时数'] : 0;
            $yjsss = $yjsss1 + $yjsss2;
            $a = $yjsss/$arr1[$v['bh']]/11*0.6*$v['达标定额']*$v['Rate']*0.5/1000*$v['千件工价'];
                
            if(array_key_exists($v['bh'].'-'.$v['sczl_rq'],$brr)){
                $brr[$v['bh'].'-'.$v['sczl_rq']] += $a;
            }else{
                $brr[$v['bh'].'-'.$v['sczl_rq']] = $a;
            }
        }

        foreach($res as $k=>&$v){
            if(array_key_exists($k,$list)){
                $v['月计时时数'] += $list[$k]['月计时时数'];
            }
            $v['工资表类别'] = trim($v['工资表类别']);
            $v['法定天数'] = round($v['法定天数']);
            $v['当月定额'] = round($v['当月定额']);
            $v['月度合计产量'] = round($v['月度合计产量']);
            $v['月计时时数'] = floatval(number_format($v['月计时时数'],2));
            $v['计时补差'] = floatval(number_format($brr[$k],2));
            unset($v['rq_bh']);
        }
        $res = array_values($res);
        $this->success('成功',$res);
    }
    /**
     * 计件员工加班费核查下侧列表
     * @ApiMethod (GET)
     * @param string $date 年月
     * @param string $code 员工编号
     */
    public function getAllList()
    {
        //get请求
        if(!$this->request->isGet()){
            $this->error('请求方式错误');
        }
        $req = $this->request->param();
        $res=db('绩效工资汇总')
            ->field('LEFT(sczl_rq, 10) as sczl_rq,sczl_gdbh,rtrim(g.成品名称) as Gd_cpmc,sczl_yjno,sczl_gxh,sczl_type,
            sczl_jtbh,sum(班组车头产量) as 班组车头产量,工序难度系数 as 产量系数,(sum(班组车头产量*工序难度系数)) as 计件产量,
            sum(班组换算产量) as 补产产量,(sum(班组车头产量*工序难度系数)+sum(班组换算产量)) as 核算产量,日定额,sum(达标定额)
            as 达标定额,千件工价,补产标准,sum(个人计件工资) as 个人计件工资,sum(个人加班工资) as 个人加班工资,Rate as 个人比例,
            sum(装版工时) as 装版工时,sum(保养工时) as 保养工时,sum(打样工时) as 打样工时,sum(计时补差额工资) as 计时补差')
            ->join('工单_基本资料 g','g.Gd_gdbh=绩效工资汇总.sczl_gdbh AND g.行号=1')
            ->group('LEFT(sczl_rq, 10),sczl_gdbh')
            ->where(['sys_ny'=>$req['date'],'bh'=>$req['code']])
            ->select();
        if(!$res){
            $this->error('失败');
        }
        foreach($res as &$v){
            $v['sczl_rq']=str_replace('-','.',$v['sczl_rq']);
            if($v['sczl_yjno']>9){
                $sczl_yjno=$v['sczl_yjno'];
            }else{
                $sczl_yjno='0'.$v['sczl_yjno'];
            }
            unset($v['sczl_yjno']);
            if($v['sczl_gxh']>9){
                $sczl_gxh=$v['sczl_gxh'];
            }else{
                $sczl_gxh='0'.$v['sczl_gxh'];
            }
            unset($v['sczl_gxh']);
            $v['sczl_type']=$sczl_yjno.'-'.$sczl_gxh.'-->'.$v['sczl_type'];
        }
        $this->success('成功',$res);
    }
     /**
     * 计件员工加班费核查详情
     * @ApiMethod (GET)
     * @param string $date 日期
     * @param string $code 员工编号
     */
    public function getDetail()
    {
        //get请求
        if(!$this->request->isGet()){
            $this->error('请求方式错误');
        }
        $req = $this->request->param();
        $start_time=$req['date'].' 00:00:00';
        $end_time=$req['date'].' 23:59:59';
        $res=db('绩效工资汇总')
           ->field('LEFT(sczl_rq, 10) as sczl_rq,sczl_gdbh,rtrim(g.Gd_cpmc) as Gd_cpmc,sczl_yjno,sczl_gxh,sczl_type,
            sczl_jtbh,sum(班组车头产量) as 班组车头产量,工序难度系数 as 产量系数,(sum(班组车头产量*工序难度系数)) as 计件产量,
            sum(班组换算产量) as 补产产量,(sum(班组车头产量*工序难度系数)+sum(班组换算产量)) as 核算产量,日定额,sum(达标定额)
            as 达标定额,千件工价,补产标准,sum(个人计件工资) as 个人计件工资,sum(个人加班工资) as 个人加班工资,Rate as 个人比例,
            sum(装版工时) as 装版工时,sum(保养工时) as 保养工时,sum(打样工时) as 打样工时,sum(计时补差额工资) as 计时补差')
            ->join('工单_基本资料 g','g.Gd_gdbh=绩效工资汇总.sczl_gdbh','LEFT')
            ->group('LEFT(sczl_rq, 10),sczl_gdbh')
            ->where(['sczl_rq'=>['between',"$start_time,$end_time"],'bh'=>$req['code']])
            ->select();
        if(!$res){
            $this->error('失败');
        }
        foreach($res as &$v){
            $v['sczl_rq']=str_replace('-','.',$v['sczl_rq']);
            if($v['sczl_yjno']>9){
                $sczl_yjno=$v['sczl_yjno'];
            }else{
                $sczl_yjno='0'.$v['sczl_yjno'];
            }
            unset($v['sczl_yjno']);
            if($v['sczl_gxh']>9){
                $sczl_gxh=$v['sczl_gxh'];
            }else{
                $sczl_gxh='0'.$v['sczl_gxh'];
            }
            unset($v['sczl_gxh']);
            $v['sczl_type']=$sczl_yjno.'-'.$sczl_gxh.'-->'.$v['sczl_type'];
        }
        $this->success('成功',$res);
    }
    /**
     * 定位
     * @ApiMethod GET
    */
    public function search(){
      
        //get请求
        if(!$this->request->isGet()){
            $this->error('请求方式错误');
        }
        $req = $this->request->param();
        $yg = db()->table('人事_基本资料')->where('员工姓名',$req['search'])->value('员工编号');
        // $yg = db()->table('人事_基本资料')->where('员工姓名','like','%'.$req['search'].'%')->column('员工编号');

        if($yg){
            $req['search']=$yg;
        }
        $res=db('绩效工资汇总')->alias('j')
            ->join('人事_基本资料 r','r.员工编号=j.bh')
            ->group('j.bh')
            ->where('j.sys_ny',$req['date'])
            ->where('j.bh',$req['search'])
            // ->where(['r.所在部门'=>['like',$req['department'].'%']])
            ->column('CONCAT(j.bh, "-", DATE_FORMAT(sczl_rq,"%Y.%m")) AS rq_bh,j.bh,rtrim(j.xm) as 员工姓名,r.工资表类别,DATE_FORMAT(r.聘用日期,"%Y.%m.%d") as 聘用日期,j.法定天数,
            sum(j.达标定额) as 当月定额,(sum(j.班组车头产量*j.工序难度系数)+sum(j.班组换算产量)) as 月度合计产量,sum(j.个人计件工资) as 个人计件工资,
            sum(j.个人加班工资) as 个人加班工资,sum(j.异常停机工时) as 月计时时数,LEFT(j.sczl_rq, 7) as sczl_rq');

        $year=substr($req['date'],0,4);
        $month=substr($req['date'],-2);
        $start_time = $year . '-' . $month . '-01 00:00:00';
        $end_time = date('Y-m-t', strtotime("$year-$month-01")) . ' 23:59:59';
        $arr = [];
        $columns = ['wgjs_bh1', 'wgjs_bh2', 'wgjs_bh3', 'wgjs_bh4', 'wgjs_bh5', 'wgjs_bh6'];
        for ($i = 1; $i <= 6; $i++) {
            $column = $columns[$i-1];
            $r = db('db_wgjs')
                ->field("DATE_FORMAT(wgjs_rq,'%Y.%m') as wgjs_rq, $column as wgjs_bh, wgjs_js$i as wgjs_js, wgjs_冲定额$i as wgjs_冲定额")
                ->join('人事_基本资料', "人事_基本资料.员工编号=db_wgjs.$column")
                ->where($column,$req['search'])
                ->where(['wgjs_rq' => ['between', "$start_time,$end_time"]])
                ->select();

            $arr = array_merge($arr, $r);
        }

        $list = [];
        foreach($arr as $v){
            if(isset($list[$v['wgjs_bh'].'-'.$v['wgjs_rq']])){
                if($v['wgjs_冲定额']=='是'){
                    $list[$v['wgjs_bh'].'-'.$v['wgjs_rq']]['月计时时数']+=$v['wgjs_js'];
                }
            }else{
                $list[$v['wgjs_bh'].'-'.$v['wgjs_rq']]['bh']=$v['wgjs_bh'];
                $list[$v['wgjs_bh'].'-'.$v['wgjs_rq']]['sczl_rq']=$v['wgjs_rq'];
                if($v['wgjs_冲定额']=='是'){
                    $list[$v['wgjs_bh'].'-'.$v['wgjs_rq']]['月计时时数']=$v['wgjs_js'];
                }else{
                    $list[$v['wgjs_bh'].'-'.$v['wgjs_rq']]['月计时时数']=0;
                }
            }
        };

        //获取每个员工计时补差天数
        $arr1 = db('绩效工资汇总')->alias('j')
            ->field('j.bh,j.sczl_rq')
            ->join('人事_基本资料 r','r.员工编号=j.bh')
            ->group('j.bh,j.sczl_rq')
            ->where('j.sys_ny',$req['date'])
            ->where('j.bh',$req['search'])
            ->where('j.达标定额','<>',0)
            // ->where(['r.所在部门'=>['like',$req['department'].'%']])
            ->buildSql();
        $arr1 = db()->table($arr1 . ' t')
            ->group('t.bh, LEFT(t.sczl_rq, 7)')
            ->column('t.bh,count(LEFT(t.sczl_rq, 7)) as num');

        //查询每条记录
        $arr2 = db('绩效工资汇总')->alias('j')
            ->field('j.bh, DATE_FORMAT(j.sczl_rq,"%Y.%m") as sczl_rq,
            sum(j.达标定额) as 达标定额, j.Rate, j.千件工价')
            ->join('人事_基本资料 r','r.员工编号=j.bh')
            ->where('j.sys_ny',$req['date'])
            ->where('j.达标定额','<>',0)
            ->where('j.bh',$req['search'])
            // ->where(['r.所在部门'=>['like',$req['department'].'%']])
            ->group('j.bh,j.sczl_rq,j.sczl_gdbh')
            ->select();
        //计算每条记录的计时补差
        $brr = [];
        foreach($arr2 as $v){
            $yjsss1 = array_key_exists($v['bh'].'-'.$v['sczl_rq'],$list) ? $list[$v['bh'].'-'.$v['sczl_rq']]['月计时时数'] : 0;
            $yjsss2 = array_key_exists($v['bh'].'-'.$v['sczl_rq'],$res) ? $res[$v['bh'].'-'.$v['sczl_rq']]['月计时时数'] : 0;
            $yjsss = $yjsss1 + $yjsss2;
            $a = $yjsss/$arr1[$v['bh']]/11*0.6*$v['达标定额']*$v['Rate']*0.5/1000*$v['千件工价'];
                
            if(array_key_exists($v['bh'].'-'.$v['sczl_rq'],$brr)){
                $brr[$v['bh'].'-'.$v['sczl_rq']] += $a;
            }else{
                $brr[$v['bh'].'-'.$v['sczl_rq']] = $a;
            }
        }

        foreach($res as $k=>&$v){
            if(array_key_exists($k,$list)){
                $v['月计时时数'] += $list[$k]['月计时时数'];
            }
            $v['工资表类别'] = trim($v['工资表类别']);
            $v['法定天数'] = round($v['法定天数']);
            $v['当月定额'] = round($v['当月定额']);
            $v['月度合计产量'] = round($v['月度合计产量']);
            $v['月计时时数'] = floatval(number_format($v['月计时时数'],2));
            $v['计时补差'] = floatval(number_format($brr[$k],2));
            unset($v['rq_bh']);
        }
        $res = array_values($res);
        $this->success('成功',$res);
        $arr = [];
        $columns = ['wgjs_bh1', 'wgjs_bh2', 'wgjs_bh3', 'wgjs_bh4', 'wgjs_bh5', 'wgjs_bh6'];
        $field = 'wgjs_js';
        for ($i = 1; $i <= 6; $i++) {
            $column = $columns[$i-1];
            $r = db('db_wgjs')
                ->field("DATE_FORMAT(wgjs_rq,'%Y.%m') as wgjs_rq, $column as wgjs_bh, ${field}${i} as wgjs_js")
                ->join('人事_基本资料', "人事_基本资料.员工编号=db_wgjs.$column")
                ->where(['wgjs_rq' => ['between', "$start_time,$end_time"]])
                ->select();

            $arr = array_merge($arr, $r);
        }

        $list = [];
        foreach($arr as $v){
            if(isset($list[$v['wgjs_bh'].'-'.$v['wgjs_rq']])){
                $list[$v['wgjs_bh'].'-'.$v['wgjs_rq']]['月计时时数']+=$v['wgjs_js'];
            }else{
                $list[$v['wgjs_bh'].'-'.$v['wgjs_rq']]['bh']=$v['wgjs_bh'];
                $list[$v['wgjs_bh'].'-'.$v['wgjs_rq']]['sczl_rq']=$v['wgjs_rq'];
                $list[$v['wgjs_bh'].'-'.$v['wgjs_rq']]['月计时时数']=$v['wgjs_js'];
            }
        };
        
        foreach($res as $k=>&$v){
            if(array_key_exists($k,$list)){
                $v['月计时时数'] += $list[$k]['月计时时数'];
            }
            $v['工资表类别'] = trim($v['工资表类别']);
            $v['法定天数'] = round($v['法定天数']);
            $v['当月定额'] = round($v['当月定额']);
            $v['月度合计产量'] = round($v['月度合计产量']);
            $v['月计时时数'] = floatval(number_format($v['月计时时数'],2));
            $v['计时补差'] = floatval(bcmul($v['月计时时数'], 9.5, 2));
            unset($v['rq_bh']);
        }
        $res = array_values($res);
        $this->success('成功',$res);
    }
}