`
/**
*
* @param $file
* @return array
* @throws InfoException
* @throws \Es3\Exception\WaringException
* @throws \Es3\Exception\ErrorException
*/
public function invitationByFile(UploadFile $file): array
{
if ('xlsx' != strtolower(File::extension($file->getClientFilename()))) {
throw new InfoException(1000, '仅支持xlsx格式的表格,如果是其它格式的文件,请通过Excel另存为xlsx格式.');
}
//empty check
$mappings = ['A' => '姓名', 'B' => '身份证号', 'C' => '手机号', 'D' => '公司', 'E' => '部门', 'F' => '岗位', 'G' => '岗位类型', 'H' => '上级领导', 'I' => '职级', 'J' => '工时制度', 'K' => '工作地点', 'L' => '入职日期', 'M' => '试用期', 'N' => '转正日期', 'O' => '劳动合同开始日期', 'P' => '劳动合同结束日期', 'Q' => '员工发展类型', 'R' => '状态'];
$employeeList = Excel::xlswriterMapping($file->getTempName(), $mappings, 1);
if (superEmpty($employeeList)) {
throw new InfoException(1036, '尚未从表格中获取到数据,请重新填写后上传!');
}
$requestId = traceCode();
$employeeInvDao = new EmployeeInvDao();
$opTableName = "hr_employee";
//截断表
if (!isProduction()) {
$employeeInvDao->truncate();
}
$rowNumber = 1;
foreach ($employeeList as $key => $row) {
$employeeList[$key]['request_id'] = $requestId;
$employeeList[$key]['row_number'] = ++$rowNumber;
$employeeList[$key]['入职日期'] = $this->excelDateToYmd($row['入职日期']);
$employeeList[$key]['转正日期'] = $this->excelDateToYmd($row['转正日期']);
$employeeList[$key]['劳动合同开始日期'] = $this->excelDateToYmd($row['劳动合同开始日期']);
$employeeList[$key]['劳动合同结束日期'] = $this->excelDateToYmd($row['劳动合同结束日期']);
}
//所有数据都不能为空
// 入库
$employeeInvDao->insertAll($employeeList);
//所有数据都不能为空
$msgList = [];
//通过数据库对字段进行空校验 row_number 是个精髓 select
foreach ($mappings as $key => $mapping) {
// 验证身份证号码是否存在
$rowNumber = $employeeInvDao->verifyInvFieldEmpty($requestId, $mappings[$key])['row_number'] ?? null;
if (!superEmpty($rowNumber)) {
$msgList[] = "表格中第{$rowNumber}行的{$mappings[$key]}不能为空";
}
}
//校验格式 select
//身份证
$rowNumber = $employeeInvDao->verifyInvFieldFormat($requestId, "身份证号")['row_number'] ?? null;
if (!superEmpty($rowNumber)) {
$msgList[] = "表格中第{$rowNumber}行的身份证号格式不对";
}
//手机号
$rowNumber = $employeeInvDao->verifyInvFieldFormat($requestId, "手机号", "[1][123456789][0-9]{9}$")['row_number'] ?? null;
if (!superEmpty($rowNumber)) {
$msgList[] = "表格中第{$rowNumber}行的手机号格式不对";
}
// 数据匹配 update
// 公司
$employeeInvDao->matchInvFieldFromOtherTable($requestId, "hr_org_company", "name", "公司", ["id" => "company_id"]);
//部门
$employeeInvDao->matchInvFieldFromOtherTable($requestId, "hr_org_department", "name", "部门", ["id" => "depart_id"]);
//岗位类型
$employeeInvDao->matchInvFieldFromOtherTable($requestId, "hr_position", "name", "岗位类型", ["id" => "position_type_id"]);
//上级领导
$employeeInvDao->matchInvFieldFromOtherTable($requestId, "hr_employee", "name", "上级领导", ["id" => "superior_id"]);
//职级
$employeeInvDao->matchInvFieldFromOtherTable($requestId, "hr_basic_rank", "name", "职级", ["id" => "rank_id"]);
//员工发展类型
$employeeInvDao->matchInvFieldFromEnum($requestId, "hr_employee_inv", "id", "员工发展类型", "rank_type", [
"技术岗" => "P",
"管理岗" => "M",
"工人岗" => "W"
]);
//试用时间 0，1，2，3，4 5 =》0 1 3 6 12 7
$employeeInvDao->matchInvFieldFromEnum($requestId, "hr_employee_inv", "id", "试用期", "try_day", [
"入职后1个月" => "1",
"入职后3个月" => "2",
"入职后6个月" => "3",
"入职后12个月" => "4",
"无" => "0"
//"入职后7个月"=>"5"
]);
//工时制度
$employeeInvDao->matchInvFieldFromEnum($requestId, "hr_employee_inv", "id", "工时制度", "work_schedule", [
"不定时工作时间" => "UNTIME",
"标准工作时间" => "STANDARD",
"综合工作时间" => "COMPOSITE",
]);
//工时制度
$employeeInvDao->matchInvFieldFromEnum($requestId, "hr_employee_inv", "id", "工时制度", "work_schedule", [
"不定时工作时间" => "UNTIME",
"标准工作时间" => "STANDARD",
"综合工作时间" => "COMPOSITE",
]);
// 状态({"INTERN":"实习","PROBATION":"试用","NORMAL":"正式","REHIRING":"返聘","QUIT":"离职","REIN":"二次入职","WAIT":"待入职","CANCEL":"取消入职","REVIEWED":"待审核”,”CONFIRM”:”待确认”,”REJECT":"暂时驳回"}
//员工状态
$employeeInvDao->matchInvFieldFromEnum($requestId, "hr_employee_inv", "id", "状态", "status", [
"实习" => "INTERN",
"试用" => "PROBATION",
"正式" => "NORMAL",
"返聘" => "REHIRING",
"离职" => "QUIT",
"二次入职" => "REIN",
"待入职" => "WAIT",
"取消入职" => "CANCEL",
"待审核" => "REVIEWED",
"待确认" => "CONFIRM",
"暂时驳回" => "REJECT",
]);
//校验数据匹配是否成功
$matchFields = ["company_id" => "公司", "depart_id" => "部门", "position_type_id" => "岗位类型", "superior_id" => "上级领导", "rank_id" => "职级", "status" => "状态"];
foreach ($matchFields as $key => $matchField) {
$rowNumber = $employeeInvDao->verifyInvFieldEmpty($requestId, $key)['row_number'] ?? null;
if (!superEmpty($rowNumber)) {
if ($key == "depart_id") {
//先获取部门
$departList = (new EmployeeInvDao())->getDepartList($requestId, explode(",", $rowNumber));
$departListInsertAll = [];
foreach ($departList as $key => $val) {
$departListInsertAll [$val['部门']] = [
'name' => $val['部门'],
'parent_id' => 1,
"level" => 2,
"code" => "1"
];
}
//创建部门
$departmentDao = new DepartmentDao();
$departmentDao->insertAll($departListInsertAll);
}
$msgList[] = "表格中第{$rowNumber}行的{$matchField}没有匹配成功";
}
}
//更新 employee_inv 中的 employee_id 通过身份证 关联 hr_employee 取 id
$employeeInvDao->matchInvFieldFromOtherTable($requestId, $opTableName, "id_card", "身份证号", ["id" => "employee_id"]);
//查看 employee_id 是否都关联上
$rowNumber = $employeeInvDao->verifyInvFieldEmpty($requestId, 'employee_id')['row_number'] ?? null;
if (!superEmpty($rowNumber)) {
//创建员工
$employeeInvDao->creacteEmployeeFromInv($requestId, $opTableName);
}
$employeeInvDao->matchInvFieldFromOtherTable($requestId, $opTableName, "id_card", "身份证号", ["id" => "employee_id"]);
//更新一下 work_no
$employeeInvDao->matchInvFieldFromOtherTable($requestId, $opTableName, "id_card", "身份证号", ["work_no" => "work_no"]);
//更新 hr_employee 表中的数据
$employeeInvDao->updateEmployeeFromInv($requestId, $opTableName);
//更新员工关联企业表
//删除 inv 表中的 员工的部门
$employeeInvDao->delEmployeeDepartmentFromInv($requestId);
$employeeInvDao->insertEmployeeDepartmentFromInv($requestId);
return [$msgList, $employeeList];
}`
`
/**
* @param string $requestId
* @param $field
* @return array
*/
public function verifyInvFieldEmpty(string $requestId, $field): array
{
$sqlWhere = "";
if ($field=="employee_id") {
$sqlWhere = " OR `{$field}` < 1 ";
}
$sql = "SELECT
GROUP_CONCAT(row_number) row_number
FROM
hr_employee_inv
WHERE
request_id = ?
AND ( `{$field}` IS NULL OR LENGTH(trim( `{$field}`)) <1 {$sqlWhere} )
";
return $this->query($sql, [$requestId], true);
}
/**
* 获取所有部门
* @param string $requestId
* @param $field
* @return array
*/
public function getDepartList(string $requestId, $rowNumbers): array
{
$rowNumberstr =implode("','", $rowNumbers);
$sql = "select * from hr_employee_inv where request_id = ? and row_number in ('{$rowNumberstr}')";
return $this->query($sql, [$requestId]);
}
/**
* @param string $requestId
* @param $matchTable
* @param $onFromField
* @param $onInvField
* @param array $setFieldArr
* @throws \Es3\Exception\ErrorException
*/
public function matchInvFieldFromOtherTable(string $requestId, $matchTable, $onFromField, $onInvField, $setFieldArr = ["id"=>"employee_id"]): void
{
if ($setFieldArr) {
$setSql = "";
foreach ($setFieldArr as $key=>$set) {
$setSql.="SET inv.`{$set}` = {$matchTable}.`{$key}` ";
}
$sql = "UPDATE hr_employee_inv inv
LEFT JOIN {$matchTable} ON inv.`{$onInvField}` = {$matchTable}.`{$onFromField}`
{$setSql}
WHERE
{$matchTable}.`id` > 0
AND inv.`request_id` = ?
AND (({$matchTable}.`{$onFromField}` IS not NULL ) OR (LENGTH(trim( {$matchTable}.`{$onFromField}`)) > 0) )
";
$this->exec($sql, [$requestId]);
}
}
/**
* @param string $requestId
* @param $matchTable
* @param $onInvField
* @param $setFromField
* @param $setInvField
* @param array $matchArr
* @throws \Es3\Exception\ErrorException
*/
public function matchInvFieldFromEnum(string $requestId, $matchTable, $onInvField, $setFromField, $setInvField, $matchArr = []): void
{
if (!empty($matchArr)) {
$sqlWhen = "";
foreach ($matchArr as $key=>$match) {
$sqlWhen .=" WHEN '{$key}' THEN '{$match}' ";
}
$sql = "UPDATE hr_employee_inv
LEFT JOIN (
SELECT
id AS mid,
CASE
{$matchTable}.`{$setFromField}`
{$sqlWhen}
ELSE ''
END AS enumValue
FROM
{$matchTable}
) left_table ON hr_employee_inv.`{$onInvField}` = left_table.mid
SET hr_employee_inv.`{$setInvField}` = left_table.enumValue
where hr_employee_inv.`request_id` = ?
";
$this->exec($sql, [$requestId]);
}
}
/**
* @param string $requestId
* @param $matchField
* @param string $regexp
* @return array
*/
public function verifyInvFieldFormat(string $requestId, $matchField, $regexp="[0-9]{18}|[0-9]{17}X|[0-9]{15}"): array
{
$selfTable = "hr_employee_inv";
$sql = "SELECT GROUP_CONCAT(row_number) row_number
FROM {$selfTable}
WHERE {$selfTable}.`{$matchField}` NOT REGEXP '{$regexp}'
and {$selfTable}.`request_id` = ?";
return $this->query($sql, [$requestId], ture);
}
/**
* @param string $requestId
* @param $insertTable
* @throws \Es3\Exception\ErrorException
*/
public function creacteEmployeeFromInv(string $requestId, $insertTable): void
{
$sql = "INSERT INTO {$insertTable} (
`work_no`,
`name`,
`id_card`,
`mobile`,
`company_id`,
`job_name`,
`position_type_id`,
`superior_id`,
`rank_id`,
`work_schedule`,
`work_address`,
`entry_date`,
`try_day`,
`regular_date`,
`contract_start_date`,
`contract_end_date`,
`rank_type`,
`status`
) (
SELECT
( SELECT MAX( work_no ) FROM hr_employee )+(
@i := @i + 1
) i,
a.*
FROM
(
SELECT
he2.`姓名`,
he2.`身份证号`,
he2.`手机号`,
he2.`company_id`,
he2.`岗位`,
he2.`position_type_id`,
he2.`superior_id`,
he2.`rank_id`,
he2.`work_schedule`,
he2.`工作地点`,
he2.`入职日期`,
he2.`try_day`,
he2.`转正日期`,
he2.`劳动合同开始日期`,
he2.`劳动合同结束日期`,
he2.`rank_type`,
he2.`status`
FROM
hr_employee_inv AS he2
LEFT JOIN {$insertTable} AS he1 ON he1.id_card = he2.`身份证号`
WHERE
he1.id IS NULL
AND he2.request_id = ?
AND ( he2.`身份证号` IS not NULL OR LENGTH(trim( he2.`身份证号`)) > 1 )
) AS a,(
SELECT
@i := 0
) t2
);
";
$this->exec($sql, [$requestId]);
}
/**
* @param string $requestId
* @param $updateTable
* @throws \Es3\Exception\ErrorException
*/
public function updateEmployeeFromInv(string $requestId, $updateTable): void
{
$sql = "UPDATE {$updateTable} AS h1,
hr_employee_inv h2
SET h1.NAME = h2.`姓名`,
h1.id_card = h2.`身份证号`,
h1.mobile = h2.`手机号`,
h1.company_id = h2.`company_id`,
h1.job_name = h2.`岗位`,
h1.position_type_id = h2.`position_type_id`,
h1.superior_id = h2.`superior_id`,
h1.rank_id = h2.`rank_id`,
h1.work_schedule = h2.`work_schedule`,
h1.work_address = h2.`工作地点`,
h1.entry_date = h2.`入职日期`,
h1.try_day = h2.`try_day`, # 0，1，2，3，4 =》0 1 3 6 12
h1.regular_date = h2.`转正日期`,
h1.contract_start_date = h2.`劳动合同开始日期`,
h1.contract_end_date = h2.`劳动合同结束日期`,
h1.rank_type = h2.`rank_type`,
h1.status = h2.`status`
WHERE
h1.id_card = h2.`身份证号`
AND h2.request_id = ? AND ( h2.`身份证号` IS not NULL OR LENGTH(trim( h2.`身份证号`)) > 1 )";
$this->exec($sql, [$requestId]);
}
/**
* @param string $requestId
* @throws \Es3\Exception\ErrorException
*/
public function delEmployeeDepartmentFromInv(string $requestId): void
{
$sql = " delete from hr_employee_department where employee_id IN(select employee_id from hr_employee_inv where request_id = ? )";
$this->exec($sql, [$requestId]);
}
public function insertEmployeeDepartmentFromInv(string $requestId): void
{
$sql = "
INSERT INTO hr_employee_department ( department_id, employee_id )
SELECT
hi.depart_id,
hi.employee_id
FROM
hr_employee_inv hi
# LEFT JOIN hr_employee_department het ON het.employee_id = hi.employee_id
where hi.request_id = ?
";
$this->exec($sql, [$requestId]);
}`
