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

<?php

namespace app\api\controller;

use app\common\controller\Api;
use \think\Request;
use \think\Db;
/**
 * 员工日工资查询接口
 */
class EmployeeDailySalary extends Api
{
    protected $noNeedLogin = ['*'];
    protected $noNeedRight = ['*'];

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

    /**
     * 员工日工资查询侧边栏
     * @ApiMethod (GET)
     */
    public function getTab()
    {
        //get请求
        if(!$this->request->isGet()){
            $this->error('请求方式错误');
        }
        // $rows = db()->table('绩效工资汇总')
        // ->whereTime('sczl_rq', '>=', strtotime('-15 months'))
        // ->group('sys_ny')
        // ->order('sys_ny desc')
        // ->limit(15)
        // ->column('sys_ny');
        $rows = db()->table('db_大废品')
        ->whereTime('sys_rq', '>=', strtotime('-15 months'))
        ->group("DATE_FORMAT(`sys_rq`, '%Y%m')")
        ->order('sys_rq desc')
        ->limit(15)
        ->column("DATE_FORMAT(`sys_rq`, '%Y%m')");
        // $row = db()->table('绩效工资汇总')
        // ->field('sys_ny,人事_基本资料.所在部门')
        // ->join('人事_基本资料','人事_基本资料.员工编号=绩效工资汇总.bh','LEFT')
        // ->group('sys_ny,人事_基本资料.所在部门')
        // ->where(['sys_ny'=>['between',"$rows[14],$rows[0]"]])
        // ->order('sys_ny desc')
        // ->fetchSql(true)
        // ->select();
        foreach($rows as $k=>$v){
            $res[$k]['ny']=$v;
            $res[$k]['bh']=db()->table('绩效工资汇总')
            ->group('bh')
            ->where('sys_ny',$v)
            ->column('bh');
        }
        $rs = db()->table('人事_基本资料')->column('员工编号,所在部门');
        foreach($res as $k=>$v){
            foreach($v['bh'] as $value){
                $res[$k]['bm'][]=rtrim($rs[$value]);
            }
        }
        foreach($res as &$v){
            unset($v['bh']);
            $v['bm']=array_unique($v['bm']);
            usort($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($res as $k=>$v){
            $data[$k]['lable']=$v['ny'];
            foreach($v['bm'] as $value){
                $data[$k]['children'][]['lable']=$value;
            }
        }
        $this->success('成功',$data);
    }
    

    /**
     * 员工日工资查询列表
     * @ApiMethod (GET)
     * @param string $date 年月
     * @param string $department 部门
     */
    public function getList()
    {
        //get请求
        if(!$this->request->isGet()){
            $this->error('请求方式错误');
        }
        $req = $this->request->param();
        $res=db()->table('绩效工资汇总')
            ->join('人事_基本资料','人事_基本资料.员工编号=绩效工资汇总.bh','LEFT')
            ->field('bh,xm as 员工姓名,LEFT(sczl_rq, 10) as sczl_rq,sum(个人计件工资) as 计件工资,
            sum(个人加班工资) as 加班工资,(sum(个人计件工资)+sum(个人加班工资)) as 日工资合计')
            ->group('绩效工资汇总.bh,LEFT(sczl_rq, 10)')
            ->where('sys_ny',$req['date'])
            ->where('人事_基本资料.所在部门',$req['department'])
            ->select();
        if(!$res){
            $this->error('失败');
        }
        $year=substr($req['date'],0,4);
        $month=substr($req['date'],-2);
        if($month==12){
            $start_time=$year.'-12-01 00:00:00';
            $end_time=($year+1).'-01-01 00:00:00';
        }else{
            $start_time=$year.'-'.$month.'-01 00:00:00';
            $end_time=$year.'-'.($month+1).'-01 00:00:00';
        }
        $res1=db()->table('db_wgjs1')
        ->field('LEFT(wgjs_rq, 10) as wgjs_rq,wgjs_bh1 as wgjs_bh,wgjs_js1 as wgjs_js')
        ->join('人事_基本资料','人事_基本资料.员工编号=db_wgjs1.wgjs_bh1','LEFT')
        ->where(['wgjs_rq'=>['between',"$start_time,$end_time"],'人事_基本资料.所在部门'=>$req['department']])
        ->select();
        $res2=db()->table('db_wgjs1')
        ->field('LEFT(wgjs_rq, 10) as wgjs_rq,wgjs_bh2 as wgjs_bh,wgjs_js2 as wgjs_js')
        ->join('人事_基本资料','人事_基本资料.员工编号=db_wgjs1.wgjs_bh2','LEFT')
        ->where(['wgjs_rq'=>['between',"$start_time,$end_time"],'人事_基本资料.所在部门'=>$req['department']])
        ->select();
        $res3=db()->table('db_wgjs1')
        ->field('LEFT(wgjs_rq, 10) as wgjs_rq,wgjs_bh3 as wgjs_bh,wgjs_js3 as wgjs_js')
        ->join('人事_基本资料','人事_基本资料.员工编号=db_wgjs1.wgjs_bh3','LEFT')
        ->where(['wgjs_rq'=>['between',"$start_time,$end_time"],'人事_基本资料.所在部门'=>$req['department']])
        ->select();
        $res4=db()->table('db_wgjs1')
        ->field('LEFT(wgjs_rq, 10) as wgjs_rq,wgjs_bh4 as wgjs_bh,wgjs_js4 as wgjs_js')
        ->join('人事_基本资料','人事_基本资料.员工编号=db_wgjs1.wgjs_bh4','LEFT')
        ->where(['wgjs_rq'=>['between',"$start_time,$end_time"],'人事_基本资料.所在部门'=>$req['department']])
        ->select();
        $res5=db()->table('db_wgjs1')
        ->field('LEFT(wgjs_rq, 10) as wgjs_rq,wgjs_bh5 as wgjs_bh,wgjs_js5  as wgjs_js')
        ->join('人事_基本资料','人事_基本资料.员工编号=db_wgjs1.wgjs_bh5','LEFT')
        ->where(['wgjs_rq'=>['between',"$start_time,$end_time"],'人事_基本资料.所在部门'=>$req['department']])
        ->select();
        $res6=db()->table('db_wgjs1')
        ->field('LEFT(wgjs_rq, 10) as wgjs_rq,wgjs_bh6 as wgjs_bh,wgjs_js6 as wgjs_js')
        ->join('人事_基本资料','人事_基本资料.员工编号=db_wgjs1.wgjs_bh6','LEFT')
        ->where(['wgjs_rq'=>['between',"$start_time,$end_time"],'人事_基本资料.所在部门'=>$req['department']])
        ->select();
        $arr=array_merge($res1,$res2,$res3,$res4,$res5,$res6);
        foreach($arr as $v){
            if(isset($list[$v['wgjs_rq']][$v['wgjs_bh']])){
                $list[$v['wgjs_rq']][$v['wgjs_bh']]+=$v['wgjs_js'];
            }else{
                $list[$v['wgjs_rq']][$v['wgjs_bh']]=$v['wgjs_js'];
            }
        }
        foreach($res as &$v){
            $v['sczl_rq']=str_replace('-','.',$v['sczl_rq']);
            if(isset($list[$v['sczl_rq']][$v['bh']])){
                $v['计时']=$list[$v['sczl_rq']][$v['bh']];
            }else{
                $v['计时']='';
            }  
        }
        $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()->table('绩效工资汇总')
            ->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 班组车头产量,sum(班组换算产量) as 班组换算产量,工序难度系数,sum(个人计件工资)
            as 个人计件工资,sum(个人加班工资) as 个人加班工资,sum(装版工时) as 装版工时,sum(保养工时) as 保养工时,
            sum(打样工时) as 打样工时,sum(异常停机工时) as 异常停机工时,sum(车头产量占用机时) as 车头产量占用机时,日定额,
            sum(达标定额) as 达标定额,千件工价,补产标准,bh,rtrim(xm) as xm,Rate')
            ->join('工单_基本资料 g','g.Gd_gdbh=绩效工资汇总.sczl_gdbh','LEFT')
            ->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()->table('绩效工资汇总')
            ->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 班组车头产量,sum(班组换算产量) as 班组换算产量,工序难度系数,sum(个人计件工资)
            as 个人计件工资,sum(个人加班工资) as 个人加班工资,sum(装版工时) as 装版工时,sum(保养工时) as 保养工时,
            sum(打样工时) as 打样工时,sum(异常停机工时) as 异常停机工时,sum(车头产量占用机时) as 车头产量占用机时,日定额,
            sum(达标定额) as 达标定额,千件工价,补产标准,bh,rtrim(xm) as xm,Rate')
            ->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)
     * @param string $UniqId UniqId
     */
    public function getInfo()
    {
        //get请求
        if(!$this->request->isGet()){
            $this->error('请求方式错误');
        }
        $req = $this->request->param();
        if (isset($req['UniqId']) && !empty($req['UniqId'])){
            $UniqId = $req['UniqId'];
        }else{
            $this->error('参数错误');
        }

        $rows = db()->table('db_wgjs')->alias('d')
            ->field('d.*, ')
            ->join('工单_基本资料 g', 'd.')
            ->where('d.UniqId',$UniqId)
            ->select();


        $this->success('成功',$rows);
    }
    /**
     * 定位
     * @ApiMethod GET
    */
    public function search(){
        
        //get请求
        if(!$this->request->isGet()){
            $this->error('请求方式错误');
        }
        $req = $this->request->param();
        $yg = db()->table('人事_基本资料')->where('员工姓名',$req['search'])->value('员工编号');
        if($yg){
            $req['search']=$yg;
        }
        $res=db()->table('绩效工资汇总')
            // ->join('人事_基本资料','人事_基本资料.员工编号=绩效工资汇总.bh','LEFT')
            ->field('bh,xm as 员工姓名,LEFT(sczl_rq, 10) as sczl_rq,sum(个人计件工资) as 计件工资,
            sum(个人加班工资) as 加班工资,(sum(个人计件工资)+sum(个人加班工资)) as 日工资合计')
            ->group('绩效工资汇总.bh,LEFT(sczl_rq, 10)')
            ->where('sys_ny',$req['date'])
            ->where('bh',$req['search'])
            // ->where('人事_基本资料.所在部门',$req['department'])
            ->select();
        if(!$res){
            $this->error('失败');
        }
        $year=substr($req['date'],0,4);
        $month=substr($req['date'],-2);
        if($month==12){
            $start_time=$year.'-12-01 00:00:00';
            $end_time=($year+1).'-01-01 00:00:00';
        }else{
            $start_time=$year.'-'.$month.'-01 00:00:00';
            $end_time=$year.'-'.($month+1).'-01 00:00:00';
        }
        $res1=db()->table('db_wgjs1')
        ->field('LEFT(wgjs_rq, 10) as wgjs_rq,wgjs_bh1 as wgjs_bh,wgjs_js1 as wgjs_js')
        // ->join('人事_基本资料','人事_基本资料.员工编号=db_wgjs1.wgjs_bh1','LEFT')
        ->where(['wgjs_rq'=>['between',"$start_time,$end_time"],'wgjs_bh1'=>$req['search']])
        ->select();
        $res2=db()->table('db_wgjs1')
        ->field('LEFT(wgjs_rq, 10) as wgjs_rq,wgjs_bh2 as wgjs_bh,wgjs_js2 as wgjs_js')
        // ->join('人事_基本资料','人事_基本资料.员工编号=db_wgjs1.wgjs_bh2','LEFT')
        ->where(['wgjs_rq'=>['between',"$start_time,$end_time"],'wgjs_bh2'=>$req['search']])
        ->select();
        $res3=db()->table('db_wgjs1')
        ->field('LEFT(wgjs_rq, 10) as wgjs_rq,wgjs_bh3 as wgjs_bh,wgjs_js3 as wgjs_js')
        // ->join('人事_基本资料','人事_基本资料.员工编号=db_wgjs1.wgjs_bh3','LEFT')
        ->where(['wgjs_rq'=>['between',"$start_time,$end_time"],'wgjs_bh3'=>$req['search']])
        ->select();
        $res4=db()->table('db_wgjs1')
        ->field('LEFT(wgjs_rq, 10) as wgjs_rq,wgjs_bh4 as wgjs_bh,wgjs_js4 as wgjs_js')
        // ->join('人事_基本资料','人事_基本资料.员工编号=db_wgjs1.wgjs_bh4','LEFT')
        ->where(['wgjs_rq'=>['between',"$start_time,$end_time"],'wgjs_bh4'=>$req['search']])
        ->select();
        $res5=db()->table('db_wgjs1')
        ->field('LEFT(wgjs_rq, 10) as wgjs_rq,wgjs_bh5 as wgjs_bh,wgjs_js5  as wgjs_js')
        // ->join('人事_基本资料','人事_基本资料.员工编号=db_wgjs1.wgjs_bh5','LEFT')
        ->where(['wgjs_rq'=>['between',"$start_time,$end_time"],'wgjs_bh5'=>$req['search']])
        ->select();
        $res6=db()->table('db_wgjs1')
        ->field('LEFT(wgjs_rq, 10) as wgjs_rq,wgjs_bh6 as wgjs_bh,wgjs_js6 as wgjs_js')
        // ->join('人事_基本资料','人事_基本资料.员工编号=db_wgjs1.wgjs_bh6','LEFT')
        ->where(['wgjs_rq'=>['between',"$start_time,$end_time"],'wgjs_bh6'=>$req['search']])
        ->select();
        $arr=array_merge($res1,$res2,$res3,$res4,$res5,$res6);
        foreach($arr as $v){
            if(isset($list[$v['wgjs_rq']][$v['wgjs_bh']])){
                $list[$v['wgjs_rq']][$v['wgjs_bh']]+=$v['wgjs_js'];
            }else{
                $list[$v['wgjs_rq']][$v['wgjs_bh']]=$v['wgjs_js'];
            }
        }
        foreach($res as &$v){
            $v['sczl_rq']=str_replace('-','.',$v['sczl_rq']);
            if(isset($list[$v['sczl_rq']][$v['bh']])){
                $v['计时']=$list[$v['sczl_rq']][$v['bh']];
            }else{
                $v['计时']='';
            }  
        }
        $this->success('成功',$res);
    }
    /**
     * 详情
     * @ApiMethod (GET)
     * @param string $wgjs_rq 日期
     * @param string $wgjs_bh1 员工编号
    */
    public function detail(){
        //get请求
        if(!$this->request->isGet()){
            $this->error('请求方式错误');
        }
        $req = $this->request->param();

        if (!isset($req['wgjs_rq']) || !isset($req['wgjs_bh1']) ){
            $this->error('参数错误');
        }else{
        }
        if (empty($req['wgjs_rq']) || empty($req['wgjs_bh1'])){
            $this->error('参数不能为空');
        }
        $rows = db()->table('db_wgjs1')
        ->field('LEFT(wgjs_rq, 10) as wgjs_rq, 
        wgjs_bh1, CAST(wgjs_js1 AS SIGNED) as wgjs_js1, rtrim(wgjs_yy1) as wgjs_yy1, 
        wgjs_bh2, CAST(wgjs_js2 AS SIGNED) as wgjs_js2, rtrim(wgjs_yy2) as wgjs_yy2, 
        wgjs_bh3, CAST(wgjs_js3 AS SIGNED) as wgjs_js3, rtrim(wgjs_yy3) as wgjs_yy3, 
        wgjs_bh4, CAST(wgjs_js4 AS SIGNED) as wgjs_js4, rtrim(wgjs_yy4) as wgjs_yy4, 
        wgjs_bh5, CAST(wgjs_js5 AS SIGNED) as wgjs_js5, rtrim(wgjs_yy5) as wgjs_yy5, 
        wgjs_bh6, CAST(wgjs_js6 AS SIGNED) as wgjs_js6, rtrim(wgjs_yy6) as wgjs_yy6,
        rtrim(rs1.员工姓名) as name1,rtrim(rs2.员工姓名) as name2,rtrim(rs3.员工姓名)
        as name3,rtrim(rs4.员工姓名) as name4,rtrim(rs5.员工姓名) as name5,rtrim(rs6.员工姓名)
        as name6,wgjs_冲定额1,wgjs_冲定额2,wgjs_冲定额3,wgjs_冲定额4,wgjs_冲定额5,wgjs_冲定额6')
        ->join('人事_基本资料 rs1','rs1.员工编号=db_wgjs1.wgjs_bh1','LEFT')
        ->join('人事_基本资料 rs2','rs2.员工编号=db_wgjs1.wgjs_bh2','LEFT')
        ->join('人事_基本资料 rs3','rs3.员工编号=db_wgjs1.wgjs_bh3','LEFT')
        ->join('人事_基本资料 rs4','rs4.员工编号=db_wgjs1.wgjs_bh4','LEFT')
        ->join('人事_基本资料 rs5','rs5.员工编号=db_wgjs1.wgjs_bh5','LEFT')
        ->join('人事_基本资料 rs6','rs6.员工编号=db_wgjs1.wgjs_bh6','LEFT')
        ->where(['wgjs_rq'=>$req['wgjs_rq'].' 00:00:00','wgjs_bh1'=>$req['wgjs_bh1']])
        ->find();
        if($rows){
        $this->success('成功',$rows);
        }else{
            $this->error('失败');
        }   
    }
     /**
     * 修改
     * @ApiMethod POST
    */
    public function edit()
    {
        if(!$this->request->isPost()){
            $this->error('请求方式错误');
        }
        $req = $this->request->param();
        // $req = ['wgjs_js1'=>12,'wgjs_rq'=>'2023-10-12','wgjs_bh1'=>'ZM01269'];

        if (!isset($req['wgjs_rq']) || !isset($req['wgjs_bh1']) ){
            $this->error('参数错误');
        }
        if (empty($req['wgjs_rq']) || empty($req['wgjs_bh1'])){
            $this->error('参数不能为空');
        }

        $req['wgjs_rq'] = $req['wgjs_rq'].' 00:00:00';
        $req['mod_rq'] = date('Y-m-d H:i:s');

        //开启事务
        db()->startTrans();
        try{
            $sql = db()->table('db_wgjs1')->where(['wgjs_rq'=>$req['wgjs_rq'],'wgjs_bh1'=>$req['wgjs_bh1']])
            ->fetchSql(true)->update($req);
            $res= db()->query($sql);
            // 提交事务
            db()->commit();
        } catch (\Exception $e) {
            // 回滚事务
            db()->rollback();
            $this->error($e->getMessage());
        }

        if($res===false) $this->error('失败');

        $this->success('成功');
    }
      /**
     * 新增
     * @ApiMethod POST
    */
    public function add()
    {
        if(!$this->request->isPost()){
            $this->error('请求方式错误');
        }
        $req = $this->request->param();

        if (!isset($req['wgjs_rq']) || !isset($req['wgjs_bh1']) ){
            $this->error('参数错误');
        }
        if (empty($req['wgjs_rq']) || empty($req['wgjs_bh1'])){
            $this->error('参数不能为空');
        }

        $req['wgjs_rq'] = $req['wgjs_rq'].' 00:00:00';
        $req['sys_rq'] = date('Y-m-d H:i:s');

        //开启事务
        db()->startTrans();
        try{
            $sql = db()->table('db_wgjs1')->fetchSql(true)->insert($req);
            $res= db()->query($sql);
            // 提交事务
            db()->commit();
        } catch (\Exception $e) {
            // 回滚事务
            db()->rollback();
            $this->error($e->getMessage());
        }

        if($res===false) $this->error('失败');

        $this->success('成功');
    }
    /**
     * 删除
     * @ApiMethod (GET)
     * @param string $wgjs_rq 日期
     * @param string $wgjs_bh1 员工编号
    */
    public function del(){
        //get请求
        if(!$this->request->isGet()){
            $this->error('请求方式错误');
        }
        $req = $this->request->param();

        if (!isset($req['wgjs_rq']) || !isset($req['wgjs_bh1']) ){
            $this->error('参数错误');
        }else{
        }
        if (empty($req['wgjs_rq']) || empty($req['wgjs_bh1'])){
            $this->error('参数不能为空');
        }
        $rows = db()->table('db_wgjs1')->where(['wgjs_rq'=>$req['wgjs_rq'].' 00:00:00','wgjs_bh1'=>$req['wgjs_bh1']])
        ->delete();
        if($rows){
        $this->success('成功');
        }else{
            $this->error('失败');
        }   
    }
}