my.go

package repository

import (
	"database/sql"
	"errors"
	"fmt"
	"gin-vue-admin/common"
	"gin-vue-admin/global"
	"gin-vue-admin/models"
	"strconv"
)

// 查询这个员工是否有默认医院
func QueryIsDefaultHospital(phone string) (interface{}, error) {

	db, err := common.NewAccountConn()
	if err != nil {
		return nil, err
	}

	var is int
	err = db.QueryRow(`select count(id) from employee_wx
where employee_phone = ?`, phone).Scan(&is)
	if err != nil {
		return nil, err
	}

	if is <= 0 {
		return 0, err
	}

	var defaultChainCode string
	err = db.QueryRow(`select default_chain_code from employee_wx
where employee_phone = ?`, phone).Scan(&defaultChainCode)
	if err != nil {
		return nil, err
	}

	if defaultChainCode == "" {
		return 0, err
	}

	return 1, nil
}

// QueryHospitalsByEmployeePhone 根据 员工手机号查询 员工所在的医院列表
func QueryHospitalsByEmployeePhone(phone, sourceType string) (interface{}, error) {

	db, err := common.NewAccountConn()
	if err != nil {
		return nil, err
	}

	rows, err := db.Query(`select h.id, h._name, e.employee_local_id
from employee_main as e
left join hospital_main as h on h.id = e.hospital_id
left join chain_main as c on c.id = h.chain_id
left join chain_config as cc on cc.chain_main_id = c.id
where e.delflag = 0
and h.delflag = 0
and h.id != 0
and cc.store_type = 0
and e.job_state = 0 
and e.phone = ?
and h.is_bk = ?
GROUP BY h.id, e.employee_local_id`, phone, sourceType)
	if err != nil {
		return nil, err
	}

	response := make([]models.MyHospitals, 0)

	for rows.Next() {
		var t models.MyHospitals
		err = rows.Scan(&t.ID, &t.Name, &t.LocalID)
		if err != nil {
			return nil, err
		}
		response = append(response, t)

	}

	// 如果医院列表 == 1、则直接设置为默认医s院
	// 如果医院列表 > 1、则需要让用户选择默认医院

	global.GVA_LOG.Info("response len" + strconv.Itoa(len(response)))
	global.GVA_LOG.Info("phone: " + phone)

	if len(response) == 1 {

		// 查询微信小程序用户 如果查不到 则创建
		var counts int
		err = db.QueryRow(`select count(id) from employee_wx
where employee_phone = ? and hospital_main_id = ? and employee_local_id = ?`, phone, response[0].ID, response[0].LocalID).Scan(&counts)
		if err != nil {
			return nil, err
		}

		if counts <= 0 {
			err = SetEmployeeDefaultHospital(phone, int(response[0].ID), int(response[0].LocalID))
			if err != nil {
				return nil, err
			}
		}

		var defaultChainCode string
		err = db.QueryRow(`select default_chain_code from employee_wx
where employee_phone = ?`, phone).Scan(&defaultChainCode)
		if err != nil {
			return nil, err
		}

		if defaultChainCode == "" {
			err = SetEmployeeDefaultHospital(phone, int(response[0].ID), int(response[0].LocalID))
			if err != nil {
				return nil, err
			}
		}

	}

	return response, nil
}

// SetEmployeeDefaultHospital 设置员工 默认使用哪个医院
func SetEmployeeDefaultHospital(phone string, hospitalID, employeeLocalID int) error {

	db, err := common.NewAccountConn()
	if err != nil {
		return err
	}

	// 查询医院是否存在
	var is int
	err = db.QueryRow(`select count(*) from employee_wx
	where employee_phone = ?`, phone).Scan(&is)
	if err != nil {
		return err
	}

	// 查询医院chain code
	var chainCode string
	err = db.QueryRow(`select c.chain_code from hospital_main as h
	join chain_main as c on c.id = h.chain_id
	where h.id = ?`, hospitalID).Scan(&chainCode)
	if err != nil {
		return err
	}

	if is > 0 {

		result, err := db.Exec(`update employee_wx set default_chain_code = ?, 
		hospital_main_id = ?, employee_local_id = ? where employee_phone = ?`, chainCode, hospitalID, employeeLocalID, phone)
		if err != nil {
			return err
		}
		_, err = result.RowsAffected()
		if err != nil {
			return err
		}

	} else {

		result, err := db.Exec(`insert employee_wx set employee_phone = ?, 
		default_chain_code = ?, employee_local_id = ?,
		hospital_main_id = ?`, phone, chainCode, employeeLocalID, hospitalID)
		if err != nil {
			return err
		}
		_, err = result.LastInsertId()
		if err != nil {
			return err
		}
	}

	return nil
}

func QueryUserIsBindingWx(phone string) (uint, error) {

	db, err := common.NewAccountConn()
	if err != nil {
		return 0, err
	}

	// 查询 用户是否绑定微信
	var openID string
	err = db.QueryRow(`select wx_open_id from employee_wx
	where employee_phone = ?`, phone).Scan(&openID)
	if err != nil {
		return 0, err
	}

	if openID == "" {
		return 0, nil
	}

	return 1, nil
}

// QueryUserInfoByID 根据顾客本地id 查询顾客信息
func QueryUserInfoByID(db *sql.DB, employeeLocalID int) (*models.UserInfo, error) {

	var u models.UserInfo
	var jobState int

	err := db.QueryRow(`select c.id, c.image_uri, s.hospital_code, s._name, c._name, c.sex, r._name, c.is_wx_admin, c.job_state  from con_employee as c
	join sys_hospital as s on s.id = sys_hospital_id
	join con_role as r on r.id = c.con_role_id
	where c.id = ?`, employeeLocalID).Scan(&u.ID, &u.ImageURL, &u.HospitalCode, &u.HospitalName, &u.EmployeeName, &u.EmployeeSex, &u.RoleName, &u.IsAdmin, &jobState)
	switch {
	case err == sql.ErrNoRows:
		return nil, errors.New("没有此用户")
	case err != nil:
		return nil, err
	}

	if jobState > 0 {
		return nil, errors.New("没有此用户")
	}

	// 帮助过的宠物
	err = db.QueryRow(`select count(*) from (
	select count(id) from his_consumption
	where delflag = 0 and sale_employee_id = ?
	GROUP BY his_pet_id
	) as t`, u.ID).Scan(&u.Help)
	if err != nil {
		return nil, err
	}

	// 营业额

	var hospitalHelp float64 // 医院帮助过的宠物
	err = db.QueryRow(`select count(*) from (
	select count(id) from his_consumption
	where delflag = 0
	GROUP BY his_pet_id
	) as t`).Scan(&hospitalHelp)
	if err != nil {
		return nil, err
	}

	u.Effect = fmt.Sprintf("%.2f", (u.Help/hospitalHelp)*100)

	return &u, err
}

// QueryRolePermissions 查询职位列表及列表的权限
func QueryRolePermissions(db *sql.DB, hospitalLocalId int) (interface{}, error) {

	response := make([]models.RolePermissionList, 0)

	rows, err := db.Query(`select id, _name, (select count(*) from wx_role_permission 
	where delflag = 0 and wx_doctor_module_id not in (2,3,4,7,10) and operate_type = 1 and con_role_id = r.id  and sys_hospital_id = ?) 
	from con_role r
	where r.delflag = 0 and r.sys_hospital_id = ?`, hospitalLocalId, hospitalLocalId)
	if err != nil {
		return nil, err
	}

	for rows.Next() {
		var t models.RolePermissionList
		rows.Scan(&t.RoleID, &t.RoleName, &t.PermissionCount)
		response = append(response, t)
	}

	// 把管理员添加进去
	var t models.RolePermissionList
	t.RoleID = 0
	t.RoleName = "管理员"
	t.PermissionCount = -1

	return response, nil
}

// QueryPermissionDetail 查询 职位权限详情
func QueryPermissionDetail(db *sql.DB, roleID int) ([]models.WxDoctorModule, error) {

	temp := make(map[int64]int, 0)

	rows, err := db.Query(`select wx_doctor_module_id, operate_type from wx_role_permission where delflag = 0 and con_role_id = ?`, roleID)
	if err != nil {
		return nil, err
	}

	for rows.Next() {
		var modultID int64
		var operateType int
		rows.Scan(&modultID, &operateType)
		temp[modultID] = operateType
	}

	response, err := QueryWxModuleInfo()
	if err != nil {
		return nil, err
	}

	for k, v := range response {

		if v, ok := temp[v.ID]; ok {
			response[k].OperateType = v
		}
	}

	return response, nil
}

// QueryPermissionDetail 查询 职位权限详情
func QueryPermissionDetailByEmployeeID(db *sql.DB, employeeID, hospitalID int) ([]models.WxDoctorModule, error) {

	var roleID, isAdmin int
	err := db.QueryRow(`select con_role_id, is_wx_admin from con_employee where sys_hospital_id = ? and id = ?`, hospitalID, employeeID).Scan(&roleID, &isAdmin)
	if err != nil {
		return nil, err
	}

	if isAdmin == 0 {

		temp := make(map[int64]int, 0)

		rows, err := db.Query(`select wx_doctor_module_id, operate_type from wx_role_permission where delflag = 0 and sys_hospital_id = ? and con_role_id = ?`, hospitalID, roleID)
		if err != nil {
			return nil, err
		}

		for rows.Next() {
			var modultID int64
			var operateType int
			err = rows.Scan(&modultID, &operateType)
			if err != nil {
				return nil, err
			}
			temp[modultID] = operateType
		}

		response := make([]models.WxDoctorModule, 0)
		result := make([]models.WxDoctorModule, 0)
		module, err := QueryWxModuleInfo()

		if err != nil {
			return nil, err
		}

		for k, v := range module {

			if v, ok := temp[v.ID]; ok {
				module[k].OperateType = v
				var tempModule models.WxDoctorModule
				tempModule.ID = module[k].ID
				tempModule.Name = module[k].Name
				tempModule.OperateType = module[k].OperateType
				tempModule.Icon = module[k].Icon
				response = append(response, tempModule)
			}
		}

		noAuthModel := models.WxDoctorModule{
			ID:          13,
			Name:        "远程判读",
			OperateType: 1,
			Icon:        "https://dbc-doctor-wx.oss-cn-beijing.aliyuncs.com/icon/34icon_pandu%403x.png",
		}
		response = append(response, noAuthModel)

		for _, v := range response {
			if v.OperateType != 0 {
				result = append(result, v)
			}
		}

		return result, nil
	} else {

		module, err := QueryWxModuleInfo()
		if err != nil {
			return nil, err
		}

		for k, _ := range module {
			module[k].OperateType = 1
		}

		noAuthModel := models.WxDoctorModule{
			ID:          13,
			Name:        "远程判读",
			OperateType: 1,
			Icon:        "https://dbc-doctor-wx.oss-cn-beijing.aliyuncs.com/icon/34icon_pandu%403x.png",
		}
		module = append(module, noAuthModel)

		return module, nil
	}

}

// QueryUserListByRoleID 根据职位查询对应的员工
func QueryUserListByRoleID(db *sql.DB, roleID int) ([]models.UserList, error) {

	response := make([]models.UserList, 0)

	rows, err := db.Query(`select id, image_uri, _name from con_employee
	 where delflag = 0 and job_state = 0 and con_role_id = ?`, roleID)
	if err != nil {
		return nil, err
	}

	for rows.Next() {
		var t models.UserList
		err = rows.Scan(&t.ID, &t.ImageURL, &t.EmployeeName)
		if err != nil {
			return nil, err
		}
		response = append(response, t)
	}

	return response, nil
}

// SetPermission 设置权限是否开启
func SetPermission(db *sql.DB, roleID, doctorModuleID, operateType, hospitalID int) error {

	var count int

	err := db.QueryRow(`select count(*) from wx_role_permission
	where con_role_id = ? and wx_doctor_module_id = ? and sys_hospital_id = ?`, roleID, doctorModuleID, hospitalID).Scan(&count)

	if err != nil {
		return err
	}

	if count <= 0 {

		// 插入一条
		result, err := db.Exec(`insert wx_role_permission set sys_hospital_id = ?, con_role_id = ?, wx_doctor_module_id = ?, operate_type = ?`,
			hospitalID, roleID, doctorModuleID, operateType)
		if err != nil {
			return err
		}
		_, err = result.LastInsertId()
		if err != nil {
			return err
		}

		return nil
	} else {
		// 更新一条
		result, err := db.Exec(`update wx_role_permission set operate_type = ? where con_role_id = ? and wx_doctor_module_id = ? and sys_hospital_id = ?`,
			operateType, roleID, doctorModuleID, hospitalID)
		if err != nil {
			return err
		}
		_, err = result.RowsAffected()
		if err != nil {
			return err
		}

		return nil
	}
}

// QueryUserList 查询用户列表
func QueryUserList(db *sql.DB, hospitalLocalId int) (interface{}, error) {

	response := make([]models.UserList, 0)

	rows, err := db.Query(`select e.id, e.image_uri, e._name, ifnull(r._name, ''), e.job_state from con_employee as e
	left join con_role as r on r.id = e.con_role_id
	where e.sys_hospital_id = ? and e.con_role_id != 0
	ORDER BY job_state ASC`, hospitalLocalId)
	if err != nil {
		return nil, err
	}
	for rows.Next() {
		var t models.UserList
		err = rows.Scan(&t.ID, &t.ImageURL, &t.EmployeeName, &t.RoleName, &t.JobState)
		if err != nil {
			return nil, err
		}
		response = append(response, t)
	}

	return response, nil
}

// SetJobState 修改在职状态
func SetJobState(db *sql.DB, employeeID, jobState int) error {

	result, err := db.Exec(`update con_employee set job_state = ? where id = ?`, jobState, employeeID)
	if err != nil {
		return err
	}

	_, err = result.RowsAffected()
	if err != nil {
		return err
	}

	var sysHospitalID int
	err = db.QueryRow(`select sys_hospital_id from con_employee where id = ?`, employeeID).Scan(&sysHospitalID)
	if err != nil {
		return err
	}

	var hospitalCode string
	err = db.QueryRow(`select ifnull(hospital_code, '') from sys_hospital where id = ?`, sysHospitalID).Scan(&hospitalCode)
	if err != nil {
		return err
	}

	if hospitalCode == "" {
		return errors.New("找不到医院")
	}

	return setJobStateByEmployeeMain(employeeID, jobState, hospitalCode)
}

// 设置employee_main 员工离职
func setJobStateByEmployeeMain(employeeID, jobState int, hospitalCode string) error {
	db, err := common.NewAccountConn()
	if err != nil {
		return err
	}

	var hospitalID int
	err = db.QueryRow(`select id from hospital_main where hospital_code = ?`, hospitalCode).Scan(&hospitalID)
	if err != nil {
		return err
	}

	result, err := db.Exec(`update employee_main set job_state = ? where hospital_id = ? and employee_local_id = ?`, jobState, hospitalID, employeeID)
	if err != nil {
		return err
	}
	_, err = result.RowsAffected()
	if err != nil {
		return err
	}

	return nil
}

// SetEmployeeImage
func SetEmployeeImage(db *sql.DB, employeeID int, imageURL string) error {

	result, err := db.Exec(`update con_employee set image_uri = ? where id = ?`, imageURL, employeeID)
	if err != nil {
		return err
	}

	_, err = result.RowsAffected()

	return err
}

func BindingWx(phone, openID string) error {

	db, err := common.NewAccountConn()
	if err != nil {
		return err
	}

	result, err := db.Exec(`update employee_wx set wx_open_id = ? where employee_phone = ?`, openID, phone)
	if err != nil {
		return err
	}
	_, err = result.RowsAffected()
	if err != nil {
		return err
	}

	return nil
}

func Untying(phone string) error {

	db, err := common.NewAccountConn()
	if err != nil {
		return err
	}

	result, err := db.Exec(`update employee_wx set wx_open_id = '' where employee_phone = ?`, phone)
	if err != nil {
		return err
	}
	_, err = result.RowsAffected()
	if err != nil {
		return err
	}

	return nil
}

func SaveLisOcrResult(p *models.LisExamine, chainCode, hospitalCode string, body string) error {
	db, err := common.NewAccountConn()
	if err != nil {
		return err
	}

	// 查询出 chain id  和 hospital id
	var chainId int
	var hospitalId int
	err = db.QueryRow(`select id from hospital_main
	where hospital_code = ? limit 1`, hospitalCode).Scan(&hospitalId)
	if err != nil {
		return err
	}

	err = db.QueryRow(`select id from chain_main
	where chain_code = ? limit 1`, chainCode).Scan(&chainId)
	if err != nil {
		return err
	}

	tx, err := db.Begin()
	if err != nil {
		return err
	}

	res, err := tx.Exec(`insert into lis_examine(chain_id,hospital_id,examine_id,examine_date,examine_pid,examine_pname,
		examine_ptype,device_factory,device_type,device_name,device_id,lot_number,serial,version) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)`,
		chainId, hospitalId, 0, p.Time, p.ExaminePid, p.ExaminePname, p.ExaminePtype,
		p.DeviceFactory, p.DeviceType, p.DeviceName, p.DeviceId, p.LotNumber, p.Serial, p.Version)
	if err != nil {
		tx.Rollback()
		return err
	}

	insertId, err := res.LastInsertId()
	if err != nil {
		tx.Rollback()
		return err
	}
	for _, v := range p.ExamineDetail {
		_, err = tx.Exec("insert into lis_examine_item(lis_examine_id,code,unit,result) values(?,?,?,?)",
			insertId, v.Name, v.Unit, v.Value)
		if err != nil {
			tx.Rollback()
			return err
		}
	}

	// 存储图片与结果
	saveRes, err := tx.Exec(`insert ocr_img_lis_examine set chain_id = ?, hospital_id = ?, image_url = ?, value = ?`,
		chainId, hospitalId, p.ImageUrl, body)
	if err != nil {
		tx.Rollback()
		return err
	}

	_, err = saveRes.LastInsertId()
	if err != nil {
		tx.Rollback()
		return err
	}

	tx.Commit()

	return nil
}