<?php

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;  
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Font;

class wagesAction extends action
{
	public $logic = null;
    function __construct(){
        parent::__construct();
		$this->mod = new wagesMod();
		$this->logic = new logicMod();
    }

    function __destruct(){
    }

	/**
	 * Method 列表数据
	 */
    function list(){
		$where = '';
		if($_POST['filters']){
			$groups = json_decode(htmlspecialchars_decode($_POST['filters']),true);
			$where = $this->groups2sql($groups);
		}
		$data = $this->mod->list($where, $_POST['page'], $_POST['rows'], $_POST['sidx'], $_POST['sord']);
		echo json_encode($data, JSON_UNESCAPED_UNICODE);
	}
	/**
	 * Method 通过关键字取一行数据
	 */
	function getRowByKey($key){
		$row = $this->mod->getRowByKey('*',$key);
		if($row){
			$this->exitJson(0,$row);
		}else{
			$this->exitJson(2000);
		}
	}
	/**
	 * Method 唯一值检测
	 */
	function onlyCheck(){
		$result = false;
		$w_id = (empty($_REQUEST['w_id'])) ? 0 : $_REQUEST['w_id'];
		$w_staff_text = $_REQUEST['w_staff_text'];
		if($w_staff_text!=''){
			$where = sprintf("w_id<>'%s' AND w_staff_text='%s'",$w_id,$w_staff_text);
			if($this->mod->getOne('1',$where)){
				$result = true;
			}
		}
		echo ($result) ? 'false' : 'true'; //存在不通过，不存在通过
	}
	/**
	 * Method 添加操作
	 */
	function add(){
		$input = file_get_contents('php://input');
		$params = json_decode($input,true);
		$key = $this->mod->e['key'];
		$post = $this->mod->checkValueForm($params,[$key]);//添加时,主键可以为空
		if($post['error']){
			$this->exitJson(2001,$post['error']);
		}
		$data = $post['data'];
		$this->mod->Begin();
		$lastID = $this->mod->addRow($data);
		$dbOk = $this->mod->End();
		if($dbOk && $lastID>0){
			$this->exitJson(0,['key'=>$lastID]);
		}else{
			$this->exitJson(2000);
		}
	}
	/**
	 * Method 修改操作
	 */
	function edit(){
		$input = file_get_contents('php://input');
		$params = json_decode($input,true);
		$post = $this->mod->checkValueForm($params);
		if($post['error']){
			$this->exitJson(2001,$post['error']);
		}
		$data = $post['data'];
		$key = $data[$this->mod->e['key']];


		$q = new sqler($this->logic->db);
		$row = $q->table('wages')->where('w_id', $data['w_id'])->Row();
		$row = array_merge($row, $data);
		$w_yfgz = 0;
		$w_money = 0;
		foreach ($row as $k => $v) {
			if (strpos($k, '_j_') !== false) {
				if ($v == '') {
					$v = 0;
				}
				$w_yfgz += $v * 1;
				$w_money += $v * 1;
			}
			if (strpos($k, '_k_') !== false) {
				if ($v == '') {
					$v = 0;
				}
				$w_yfgz -= $v * 1;
				$w_money -= $v * 1;
			}
			if (strpos($k, '_d_') !== false) {
				if ($v == '') {
					$v = 0;
				}
				$w_money -= $v * 1;
			}
		}
		$row['w_yfgz'] = $w_yfgz;
		$row['w_money'] = $w_money;
		$q->set($row);
		$dbOk = $this->logic->db->update($q);
		if($dbOk >= 0){
			$this->exitJson(0,['key'=>$key]);
		}else{
			$this->exitJson(2000);
		}
	}
	
	/**
	 * Method 删除操作
	 */
	function delete(){
		$input = file_get_contents('php://input');
		$params = json_decode($input,true);
		if(empty($params['cycle']) || !is_numeric($params['cycle'])){
			$this->exitJson(2001);
		}
		$this->mod->Begin();
		$this->mod->deleteRows("w_cycle=".$params['cycle']);
		$dbOk = $this->mod->End();
		if($dbOk){
			$this->exitJson(0);
		}else{
			$this->exitJson(2000);
		}
	}
	/**
	 * Method 更新自动列操作
	 */
	function update()
	{
		$input = file_get_contents('php://input');
		$params = json_decode($input, true);
		if (empty($params['cycle']) || !is_numeric($params['cycle'])) {
			$this->exitJson(2001);
		}
		$dbOk = true;
		$cycle = $params['cycle'];
		$yearMonth = substr($cycle, 0, 4) . '-' . substr($cycle, 4);
		$rows = (new sqler($this->logic->db))->table('wages')->where('w_cycle', $cycle)->all();
		$q = (new sqler($this->logic->db))->table('money_crm.yja_money_tcbill')
		->join('money_crm.yj_user', ['u_id' => '@ymt_u_id']);
		foreach($rows as $row){
			$w_j_ywtc = $q->clearWhere()->where('ymt_month', $yearMonth)
				->and('ymt_status', 3)->and('u_name', $row['w_staff_text'])->sum('ymt_money');
			$w_yfgz = $w_j_ywtc;
			$w_money = $w_j_ywtc;
			foreach($row as $k=>$v){
				if($k== 'w_j_ywtc'){
					continue; //原来的提成
				}
				if (strpos($k, '_j_') !== false) {
					if ($v == ''){
						$v = 0;
					}
					$w_yfgz += $v * 1;
					$w_money += $v * 1;
				}
				if (strpos($k, '_k_') !== false) {
					if ($v == ''){
						$v = 0;
					}
					$w_yfgz -= $v * 1;
					$w_money -= $v * 1;
				}
				if (strpos($k, '_d_') !== false) {
					if ($v == ''){
						$v = 0;
					}
					$w_money -= $v * 1;
				}
			}
			$this->logic->db->update((new sqler())->table('wages')
			->set('w_j_ywtc', $w_j_ywtc)
			->set('w_yfgz', $w_yfgz)
			->set('w_money', $w_money)
			->where('w_id', $row['w_id']));
		}		
		if ($dbOk) {
			$this->exitJson(0);
		} else {
			$this->exitJson(2000);
		}
	}
	/**
	 * 统计工资表 显示
	 */
	function report($cycle){
		if(empty($cycle))
			$this->exitJson(2001);
		$cycle = $cycle * 1 - 1;
		$data = $this->mod->report($cycle);
		$result = [
			'page'=> 1,
			'total'=> 1,
			'records' => 2,
			"userdata"=> [],
			'rows' => $data['data']
		];
		echo json_encode($result, JSON_UNESCAPED_UNICODE);
	}
	
	/**
	 * 统计工资表 完整数据
	 */
	function reportData($cycle){
		if(empty($cycle))
			$this->exitJson(2001);
		$cycle = $cycle * 1 - 1;
		$data = $this->mod->report($cycle);
		$this->exitJson(0,$data);
	}

	/**
	 * 导入工资表
	 */
	function importWages()
	{
		$object = new objectMod();
		$input = file_get_contents('php://input');
		$params = json_decode($input, true);
		$onamekeys = [];
		$cycle = '';

		$header = [
			"工资月份"=>"w_cycle",
			"发放日期" => "w_date", 
			"部门" => "w_depa_text",
			"姓名" => "w_staff_text", 
			"基本工资" => "w_j_jbgz",
			"津贴" => "w_j_jt",
			"岗位工资" => "w_j_gwgz",
			"技能工资" => "w_j_jngz", 
			"业务提成" => "w_j_ywtc",
			"绩效奖" => "w_j_jxj", 
			"全勤奖" => "w_j_jqj",
			"加班工资"=> "w_j_jbf",
			"加班餐费" => "w_j_jbcf",
			"其它补助" => "w_j_qtbc",
			"事/病假" => "w_k_sbj",
			"迟到扣" => "w_k_kkhk",
			"其他扣款" => "w_k_qtkk",
			"应发工資" => "w_yfgz", 
			"代扣社保" => "w_d_dksb",
			"代扣公积金" => "w_d_dkgjj", 
			"代扣个税" => "w_d_dkgs",
			"实发工資" => "w_money", 
			"转出行" => "w_zch_text",
			"保险公司部分" => "w_n_cbx",
			"公积金公司部分" => "w_n_cgjj",
			"五险一金发放行" => "w_zch51_text",
			"备注" => "w_remark",
		];
		$data = [];
		foreach ($params as $i => $row) {
			foreach($row as $k=>$v){
                if(isset($header[$k])){
				    $data[$i][$header[$k]] = $v;
                }
			}
		}
		$has_ywtc = false; //是否有业务提成列
		foreach ($data as $i => $row) {
			$line = $i + 1;
			$w_yfgz = 0; //应发工资
			$w_money = 0; //实发工资
			if(!isset($row['w_cycle']) || $row['w_cycle']==''){
				$row['w_cycle'] = date('Ym', strtotime(str_replace('.', '-', $row['w_date'])));
			}
			foreach ($row as $k => $v) {
				$v = trim($v);
				if ($k == 'w_date') {
					$v = date('Y-m-d', strtotime(str_replace('.', '-', $v)));
					if ($v <= '1970-01-01') {
						$this->exitJson(2001, '第 ' . $line . ' 行，不是一个正确的日期格式');
					}
				}else if ($k == 'w_cycle') {
					preg_match('/(\d+)年(\d+)月/', $v, $m);
					if (count($m) != 3 || !is_numeric($m[1]) || !is_numeric($m[2])) {
						$this->exitJson(2001, '第 ' . $line . ' 行，工资月份格式不正确, 格式例: 2020年5月');
					}
					if ($m[2] . '' < '10') $m[2] = '0' . $m[2];
					$v = $m[1] . $m[2];
					if (strlen($v) != 6) {
						$this->exitJson(2001, '第 ' . $line . ' 行，工资月份格式不正确, 格式例: 2020年5月');
					}
					if ($cycle == '') {
						$cycle = $v;
					}
					if ($cycle != $v) {
						$this->exitJson(2001, '第 ' . $line . ' 行，出现了新的工资月份，一次仅支持导入一个月份的数据');
					}
				} else if ($k == 'w_date') {
					$v = date('Y-m-d', strtotime(str_replace('.', '-', $v)));
					if ($v <= '1970-01-01') {
						$this->exitJson(2001, '第 ' . $line . ' 行，不是一个正确的日期格式');
					}
				} else if ($k == 'w_depa_text') {
					if (isset($onamekeys[$v])) {
						$o_id = $onamekeys[$v];
					} else {
						$o_id = $object->getOidByName(1, $v);
						if (empty($o_id)) {
							$this->exitJson(2001, '第 ' . $line . ' 行，部门 ' . $v . '，没有在部门表中找到');
						}
						$onamekeys[$v] = $o_id;
					}
					$row['w_depa'] = $o_id;
				} else if ($k == 'w_staff_text') {
					$o_id = $object->getOidByName(2, $v);
					if (empty($o_id)) {
						$this->exitJson(2001, '第 ' . $line . ' 行，职员 ' . $v . '，没有在职员表中找到');
					}
					$row['w_staff'] = $o_id;
				} else if ($k == 'w_zch_text') {
					$o_id = 0;
					if ($v != '') {
						if (isset($onamekeys[$v])) {
							$o_id = $onamekeys[$v];
						} else {
							$orow = $object->getRowByName(200, $v);
							if (empty($orow)) {
								$this->exitJson(2001, '第 ' . $line . ' 行，转出行 ' . $v . '，没有在科目表中找到');
							}
							$o_id = $orow['o_id'];
							$onamekeys[$v] = $o_id;
						}
					}
					$row['w_zch'] = $o_id;
				} else if ($k == 'w_zch51_text') {
					$o_id = 0;
					if ($v != '') {
						if (isset($onamekeys[$v])) {
							$o_id = $onamekeys[$v];
						} else {
							$orow = $object->getRowByName(200, $v);
							if (empty($orow)) {
								$this->exitJson(2001, '第 ' . $line . ' 行，五险一金发放行 ' . $v . '，没有在科目表中找到');
							}
							$o_id = $orow['o_id'];
							$onamekeys[$v] = $o_id;
						}
					}
					$row['w_zch51'] = $o_id;
				} else if ($k == 'w_staff_text' && $v == '') {
					$this->exitJson(2001, '第 ' . $line . ' 行，没有录入职员');
				} else if ($k == 'w_j_ywtc') {
					$has_ywtc = true;
				}

				if (strpos($k, '_j_') !== false) {
					if ($v == '')
						$v = 0;
					else if (!is_numeric($v)) {
						$this->exitJson(2001, '第 ' . $line . ' 行，第 ' . $j . '列 ' . $v . '，不是一个数字');
					}
					$w_yfgz += $v * 1;
					$w_money += $v * 1;
					
				}
				if (strpos($k, '_k_') !== false) {
					if ($v == '')
						$v = 0;
					else if (!is_numeric($v)) {
						$this->exitJson(2001, '第 ' . $line . ' 行，第 ' . $j . '列 ' . $v . '，不是一个数字');
					}
					$w_yfgz -= $v * 1;
					$w_money -= $v * 1;
				}
				if (strpos($k, '_d_') !== false) {
					if ($v == '')
						$v = 0;
					else if (!is_numeric($v)) {
						$this->exitJson(2001, '第 ' . $line . ' 行，第 ' . $j . '列 ' . $v . '，不是一个数字');
					}
					$w_money -= $v * 1;
				}
				if (strpos($k, '_n_') !== false) {
					if ($v == '')
						$v = 0;
					else if (!is_numeric($v)) {
						$this->exitJson(2001, '第 ' . $line . ' 行，第 ' . $j . '列 ' . $v . '，不是一个数字');
					}
				}
				$row[$k] = $v;
			}
			/*
			if ($w_yfgz . '' != $row['w_yfgz'] . '') {
				$this->exitJson(2001, $row['w_staff_text'] . ' 应发工资不正确,应发：' . $w_yfgz . '，提交:' . $row['w_yfgz']);
			}
			if ($w_money . '' != $row['w_money'] . '') {
				$this->exitJson(2001, $row['w_staff_text'] . ' 实发工资不正确,实发：' . $w_money . '，提交:' . $row['w_money']);
			}
			*/
			$row['w_yfgz'] = $w_yfgz;
			$row['w_money'] = $w_money;
			$data[$i] = $row;
		}
		$q = (new sqler($this->logic->db))->table('money_crm.yja_money_tcbill')
			->join('money_crm.yj_user',['u_id'=> '@ymt_u_id']);
		$yearMonth = substr($cycle,0,4).'-'. substr($cycle, 4);
		foreach ($data as $i => $row) {
			//w_j_ywtc 业务提成 如果没有传 业务提成 列
			if (!$has_ywtc) {
				$row['w_j_ywtc'] = $q->clearWhere()->where('ymt_month', $yearMonth)
						 ->and('ymt_status', 3)->and('u_name', $row['w_staff_text'])->sum('ymt_money');				
				$row['w_yfgz'] += $row['w_j_ywtc'];
				$row['w_money'] += $row['w_j_ywtc'];
			}
			$data[$i] = $row;
		}
		/*
		$report = new reportMod();
		if($cycle <= $report->getMaxCycle()){
			$this->exitJson(2001,$cycle.' 已结算，导入失败');
		}*/
		$this->mod->Begin();
		$this->mod->import($cycle, $data);
		$isOk = $this->mod->End();
		if ($isOk)
		$this->exitJson(0);
		else
		$this->exitJson(2000);
	}

	/**
	 * 导入工资条
	 */
	function exportBar()
	{
		ini_set('memory_limit', '128M');
		$params = $_POST;
		if (empty($params['cycle']) || !is_numeric($params['cycle'])) {
			$this->exitJson(2001);
		}
		$cycle = $params['cycle'];
		$q = (new sqler($this->logic->db))->table('wages')->where('w_cycle', $cycle);
		$rows = $q->all();
		$data = [];
		foreach($rows as $row){
			$data[] = [
				$row['w_staff_text'],
				$row['w_j_jbgz'],
				$row['w_j_gwgz'],
				$row['w_j_jngz'],
				$row['w_j_jbf'],
				$row['w_j_ywtc'],
				$row['w_j_jxj'],
				$row['w_j_jqj'],
				$row['w_j_jbcf'],
				$row['w_k_sbj'],
				$row['w_k_qtkk'],
				$row['w_k_chdk'],
				$row['w_yfgz'],
				$row['w_d_dksb'],
				$row['w_d_dkgjj'],
				$row['w_d_dkgs'],
				$row['w_money'],
				$row['w_remark'],
			];
		}
		if(empty($data)){
			dd('未找到数据');
		}
		$templateFile = \mvc::$cfg['SITE_PATH_TPL'] . 'wages/barTemplate.xlsx';
		$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');
		$spreadsheet = $reader->load($templateFile);
		$sheet = $spreadsheet->getActiveSheet();

		// 假设前三行有数据且需要复制格式  
		$startLine = 1; // 起始行  
		$numLinesToCopy = 3; // 要复制的行数  

		/*
		// 复制列宽  
		for ($col = 1; $col <= $sheet->getHighestColumn(); $col++) {
			$column = $sheet->getColumnIterator($col)->current()->getColumnIndex();
			$columnWidth = $sheet->getColumnDimension($column)->getWidth();
			for ($i = $nextRow; $i <= $sheet->getHighestRow(); $i += $numRowsToCopy) {
				$sheet->getColumnDimension($column)->setWidth($columnWidth);
			}
		}
		*/

		$copys = [];
		// 复制行高  
		for ($line = $startLine; $line < $startLine + $numLinesToCopy; $line++) {
			$copys[$line]['height'] = $sheet->getRowDimension($line)->getRowHeight();
		}

		// 复制单元格样式（以字体为例）  
		for ($line = $startLine; $line < $startLine + $numLinesToCopy; $line++) {
			$cellIterator = $sheet->getRowIterator($line)->current()->getCellIterator();
			$cellIterator->setIterateOnlyExistingCells(false); // 包括空单元格  
			$i=1;
			foreach ($cellIterator as $cell) {
				$font = $cell->getStyle()->getFont();
				$newFont = new Font();
				$newFont->setName($font->getName());
				$newFont->setSize($font->getSize());
				$newFont->setBold($font->getBold());
				$newFont->setItalic($font->getItalic());
				$newFont->setUnderline($font->getUnderline());
				$newFont->setColor(new Color($font->getColor()->getRGB()));
				$copys[$line][$i]['font'] = $newFont;
				$i++;
			}
			
		}

		// 获取第一行的最高列号  
		$highestColumn = $sheet->getHighestColumn();
		$highestColumnIndex = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn);

		// 创建一个数组来存储第一行的内容  
		$firstRowArray = [];
		$endCol = PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex(count($data[0]));
		$endCol .= count($data) * 3;
		$range = 'A1:' . $endCol;
		$styleArray = [
			'font' => [
				'size' => 10,
			],
			'borders' => [
				'allBorders' => [
					'borderStyle' => Border::BORDER_DASHED,
				],
			],
			'alignment' => [
				'wrapText' => true,
			],
		];
		$sheet->getStyle($range)->applyFromArray($styleArray);

		// 遍历第一行的每个单元格  
		for ($col = 1; $col <= $highestColumnIndex; $col++) {
			$cell = $sheet->getCellByColumnAndRow($col, 1);
			$firstRowArray[] = $cell->getValue();
		}  		
		$line = 2;
		foreach ($data as $i=> $rowData) {
			$column = 1;
			foreach ($firstRowArray as $headerName) {
				$sheet->setCellValueByColumnAndRow($column, $line-1, $headerName);
				$column++;
			}
			$column = 1;
			foreach ($rowData as $cellData) {
				$sheet->setCellValueByColumnAndRow($column, $line, $cellData);
				//$columnLetter = PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($column);
				//$sheet->getCell($columnLetter . $line)->getStyle()->setFont($copys[2][$column]['font']);
				$column++;
			}
			
			$sheet->getRowDimension($line + 2)->setRowHeight($copys[1]['height']);
			$sheet->getRowDimension($line + 3)->setRowHeight($copys[2]['height']);
			$sheet->getRowDimension($line + 4)->setRowHeight($copys[3]['height']);  
			
			$line = $line + 3;
		}

		// 保存Excel文件  
		$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');

		// 设置 header 信息，告诉浏览器这是一个文件下载  
		header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
		header('Content-Disposition: attachment;filename="'. $cycle.'工资条.xlsx"');
		header('Cache-Control: max-age=0');
		//$writer->save('e:/aaa'.time().'.xlsx'); // 指定保存路径和文件名
		$writer->save('php://output');  

		$dbOk = true;
		if ($dbOk) {
			$this->exitJson(0);
		} else {
			$this->exitJson(2000);
		}
	}


	/**
	 * 工资发放列表
	 */
	function listGrant()
	{
		$where = '';
		if ($_POST['filters']) {
			$groups = json_decode(htmlspecialchars_decode($_POST['filters']), true);
			$where = $this->groups2sql($groups);
		}
		$where .= ' AND vim_type="工资"';
		$data = (new voucherMod())->listImport($where, $_POST['page'], $_POST['rows'], $_POST['sidx'], $_POST['sord']);
		echo json_encode($data, JSON_UNESCAPED_UNICODE);
	}
	/**
	 * 工资发放 删除操作
	 */
	function deleteGrant() 
	{
		$input = file_get_contents('php://input');
		$params = json_decode($input, true);
		if (empty($params['cycle']) || !is_numeric($params['cycle'])) {
			$this->exitJson(2001);
		}
		$dbOk = (new voucherMod())->deleteImport('工资',$params['cycle']);
		if ($dbOk) {
			$this->exitJson(0);
		} else {
			$this->exitJson(2000);
		}
	}

	/**
	 * 导入工资发放
	 */
	function importGrant()
	{
		$input = file_get_contents('php://input');
		$params = json_decode($input, true);

		usort($params,function($a,$b){
			return strcmp($a['date'] . $a['staff'], $b['date'] . $b['staff']);
		});
		
		$groupKey = 1;
		$groupArr = [];
		foreach ($params as $i => $v) {
			$unique = md5($v['date']. $v['staff']);
			if(!isset($groupArr[$unique])){
				$groupArr[$unique] = true;
				$groupKey += 1 ;//$groupKey ?  incString($groupKey) : 'A';
			}

			$item = [
				'group' => $groupKey,
				'type' => '工资',
				'j_text' => $v['j_text'],
				'd_text'=>$v['d_text'],
				'money' => $v['money'],
				'depa' => $v['depa'],
				'staff' => $v['staff'],
				'client' => '',
				'summary'=> $v['summary'],
				'date' => $v['date'],
			];
			$data[$i] = $item;
		}
		$res = (new voucherMod())->import($data);
		if ($res===true)
			$this->exitJson(0);
		else
			$this->exitJson(2001, $res);
	}

	/**生成凭证-工资发放 */
	function createVoucherFromImportGrant()
	{
		$input = file_get_contents('php://input');
		$params = json_decode($input, true);
		parse_str($params['send'], $_POST);  
		$where = '';
		if ($_POST['filters']) {
			$groups = json_decode(htmlspecialchars_decode($_POST['filters']), true);
			$where = $this->groups2sql($groups);
		}
		$where .= ' AND vim_type="工资"';
		$res = (new voucherMod())->createVoucherFromImport($where, 0, 1500);
		if ($res === true)
			$this->exitJson(0);
		else
			$this->exitJson(2001, $res);
	}
}