employee.go

package repository

import (
	"database/sql"
	"errors"
	"fmt"
	"gin-vue-admin/models"
	"strconv"
	"strings"
	"time"

	"github.com/uniplaces/carbon"
)

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

	var format string

	if len(t) == 10 {
		format = "%Y-%m-%d"
	} else if len(t) == 7 {
		format = "%Y-%m"
	} else if len(t) == 4 {
		format = "%Y"
	} else {
		return nil, errors.New("time error")
	}

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

	rows, err := db.Query(`select e.id, e.image_uri, e._name, e.con_role_id, 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.con_role_id != 0 and e.job_state = 0 and e.sys_hospital_id = ? 
	ORDER BY job_state DESC`, HospitalLocalId)

	if err != nil {
		return nil, err
	}

	var r models.EmployeeManage

	for rows.Next() {
		var t models.Employee
		var roleID int
		// 获取员工的相关信息
		err = rows.Scan(&t.ID, &t.ImageURL, &t.EmployeeName, &roleID, &t.RoleName, &t.JobState)
		if err != nil {
			return nil, err
		}
		t.Royalty = 0
		//t.BillNumber = 0
		response = append(response, t)
	}

	sqlStr := `select c.sale_type,
ifnull(convert(c.sale_price / 1000, decimal(11,3)), 0),
ifnull(convert(c.sale_percent / 1000, decimal(11,3)), 0), 
c.service_type,
ifnull(convert(c.service_price / 1000, decimal(11,3)), 0),
ifnull(convert(c.service_percent / 1000, decimal(11,3)), 0), 
ifnull(convert(c.quantity*c.payprice / 1000000, decimal(11,3)), 0),
ifnull(convert(c.quantity / 1000, decimal(11,3)), 0),
c.sale_employee_id, 
c.service_employee_id
from his_consumption as c
left join his_bill as b on b.id = c.his_bill_id
where c.delflag = 0 and c.his_retreatbill_id = 0 
and c.commodity_ismeal = 0
and c.meter_num = 0
and b.billtype in (1,4,6,7)
and DATE_FORMAT(b.paytime, ?) = ?
and c.sale_employee_id = ? 
and c.sys_hospital_id = ? `

	// 计算营业额 和 提成
	for k, v := range response {

		// 计算提成

		rows, err = db.Query(sqlStr, format, t, v.ID, HospitalLocalId)
		if err != nil {
			return nil, err
		}

		for rows.Next() {

			var saleType, serviceType int
			var saleEmployeeID, serviceEmployeeID int64
			var salePrice, salePercent, servicePrice, servicePercent float64
			var consumptionPrice, quantity float64

			err = rows.Scan(&saleType, &salePrice, &salePercent, &serviceType, &servicePrice, &servicePercent,
				&consumptionPrice, &quantity, &saleEmployeeID, &serviceEmployeeID)
			if err != nil {
				return nil, err
			}

			if saleEmployeeID == v.ID {
				r.SumMoney += consumptionPrice
				if saleType == 1 { // 按金额
					response[k].Royalty += quantity * salePrice
				} else if saleType == 2 { // 按百分比
					if salePercent != 0 {
						response[k].Royalty += consumptionPrice * (salePercent / 100)
					}
				}
			}

			if serviceEmployeeID == v.ID {
				if serviceType == 1 { // 按金额
					response[k].Royalty += quantity * servicePrice
				} else if serviceType == 2 { // 按百分比
					if servicePercent != 0 {
						response[k].Royalty += consumptionPrice * (servicePercent / 100)
					}
				}
			}
		}

		r.SumRoyalty += response[k].Royalty

		meterCardRecharge, err := MeterCardRecharge(db, t, format, int(v.ID), HospitalLocalId)
		if err != nil {
			return nil, err
		}
		r.SumMoney += meterCardRecharge
	}

	r.Employees = response

	return r, nil
}

func QueryEmployeeDetailList(db *sql.DB, HospitalLocalId int) (interface{}, error) {

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

	rows, err := db.Query(`select e.id, e.image_uri, e._name, e.con_role_id, 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.con_role_id != 0 and e.job_state = 0 and e.sys_hospital_id = ? 
	ORDER BY job_state DESC`, HospitalLocalId)

	if err != nil {
		return nil, err
	}

	for rows.Next() {
		var t models.Employee
		var roleID int
		// 获取员工的相关信息
		err = rows.Scan(&t.ID, &t.ImageURL, &t.EmployeeName, &roleID, &t.RoleName, &t.JobState)
		if err != nil {
			return nil, err
		}
		t.Royalty = 0
		//t.BillNumber = 0
		response = append(response, t)
	}

	return response, nil
}

// 查询员工 工作记录信息
func QueryEmployeeByID(db *sql.DB, t string, employeeID int) (interface{}, error) {

	hospitalLocalId, err := QueryHospitalLocalIDByEmployeeLocalID(db, employeeID)
	if err != nil {
		return nil, err
	}

	var formatSQL, formatTime string
	var sub int

	if len(t) == 10 {
		formatSQL = "%Y-%m-%d"
		formatTime = "2006-01-02"
		sub = 6
	} else if len(t) == 7 {
		formatSQL = "%Y-%m"
		formatTime = "2006-01"
		sub = 6
	} else if len(t) == 4 {
		formatSQL = "%Y"
		formatTime = "2006"
		sub = 3
	} else {
		return nil, errors.New("time error")
	}

	var anEmployee models.AnEmployee

	// 工作趋势

	c, err := carbon.Parse(formatTime, t, "UTC")
	if err != nil {
		return nil, err
	}

	var endT *carbon.Carbon
	var endTime string

	if len(t) == 10 {
		endT = c.SubDays(sub)
		endTime = c.SubDays(sub).Format(formatTime)
	} else if len(t) == 7 {
		endT = c.SubMonths(sub)
		endTime = c.SubMonths(sub).Format(formatTime)
	} else if len(t) == 4 {
		endT = c.SubYears(sub)
		endTime = c.SubYears(sub).Format(formatTime)
	} else {
		return nil, errors.New("time error")
	}

	res := make([]*models.WorkTrendData, 0)

	// 把每个时间都抽出来
	temp := make(map[string]*models.WorkTrend, 0)

	// 业绩额 - 这个人卖出去的商品的总额
	// 提成 -  这个人卖出去的商品的总额 的提成

	sqlStr := `select c.sale_type,
ifnull(convert(c.sale_price / 1000, decimal(11,3)), 0),
ifnull(convert(c.sale_percent / 1000, decimal(11,3)), 0), 
c.service_type,
ifnull(convert(c.service_price / 1000, decimal(11,3)), 0),
ifnull(convert(c.service_percent / 1000, decimal(11,3)), 0), 
ifnull(convert(c.quantity*c.payprice / 1000000, decimal(11,3)), 0),
ifnull(convert(c.quantity / 1000, decimal(11,3)), 0),
sale_employee_id, 
service_employee_id, 
DATE_FORMAT(b.paytime, ?)
from his_consumption as c
left join his_bill as b on b.id = c.his_bill_id
where c.delflag = 0 and c.his_retreatbill_id = 0 
and c.commodity_ismeal = 0
and c.meter_num = 0
and b.billtype in (1,4,6,7)
and DATE_FORMAT(b.paytime, ?) >= ? 
and DATE_FORMAT(b.paytime, ?) <= ? 
and c.sale_employee_id = ? 
and c.sys_hospital_id in (0,?) 
`

	rows, err := db.Query(sqlStr, formatSQL, formatSQL, endTime, formatSQL, t, employeeID, hospitalLocalId)
	if err != nil {
		return nil, err
	}

	for rows.Next() {

		var saleType, serviceType, saleEmployeeID, serviceEmployeeID int
		var salePrice, salePercent, servicePrice, servicePercent float64
		var consumptionPrice, quantity float64
		var billTime string

		err = rows.Scan(&saleType, &salePrice, &salePercent, &serviceType, &servicePrice, &servicePercent,
			&consumptionPrice, &quantity, &saleEmployeeID, &serviceEmployeeID, &billTime)
		if err != nil {
			return nil, err
		}

		// 如果没有、则加入一个
		if _, ok := temp[billTime]; !ok {
			r := new(models.WorkTrend)
			temp[billTime] = r
		}

		if _, ok := temp[billTime]; ok {

			// 计算出当天的、某一条商品的销售提成 和 服务提成
			if saleEmployeeID == employeeID {

				temp[billTime].Money += consumptionPrice

				if t == billTime {
					anEmployee.SumMoney += consumptionPrice
				}

				if saleType == 1 { // 按金额
					temp[billTime].Royalty += quantity * salePrice
				} else if saleType == 2 { // 按百分比
					if salePercent != 0 {
						temp[billTime].Royalty += consumptionPrice * (salePercent / 100)
					}
				}
			}
			if serviceEmployeeID == employeeID {
				if serviceType == 1 { // 按金额
					temp[billTime].Royalty += quantity * servicePrice
				} else if serviceType == 2 { // 按百分比
					if servicePercent != 0 {
						temp[billTime].Royalty += consumptionPrice * (servicePercent / 100)
					}
				}
			}
			if t == billTime {
				anEmployee.SumRoyalty = temp[billTime].Royalty
			}
		}
	}

	// 工作趋势

	for i := 0; i <= sub; i++ {
		var str string
		if len(t) == 10 {
			str = endT.AddDays(i).Format(formatTime)
		} else if len(t) == 7 {
			str = endT.AddMonths(i).Format(formatTime)
		} else {
			str = endT.AddYears(i).Format(formatTime)
		}

		meterCardRecharge, err := MeterCardRecharge(db, t, formatSQL, employeeID, hospitalLocalId)
		if err != nil {
			return nil, err
		}

		if _, ok := temp[str]; ok {
			y1 := new(models.WorkTrendData)
			y1.Year = str
			y1.Type = "业绩额"
			y1.Value = temp[str].Money + meterCardRecharge
			y2 := new(models.WorkTrendData)
			y2.Year = str
			y2.Type = "提成"
			y2.Value = temp[str].Royalty
			res = append(res, y1, y2)
		} else {
			y1 := new(models.WorkTrendData)
			y1.Year = str
			y1.Type = "业绩额"
			y1.Value = meterCardRecharge
			y2 := new(models.WorkTrendData)
			y2.Year = str
			y2.Type = "提成"
			res = append(res, y1, y2)
		}
	}

	meterCardRecharge, err := MeterCardRecharge(db, t, formatSQL, employeeID, hospitalLocalId)
	if err != nil {
		return nil, err
	}

	anEmployee.SumMoney += meterCardRecharge

	anEmployee.WorkTrends = res

	return anEmployee, nil
}

// 查询员工销售列表
func QueryEmployeeSaleRoyalty(db *sql.DB, t string, employeeID int) (interface{}, error) {

	hospitalLocalId, err := QueryHospitalLocalIDByEmployeeLocalID(db, employeeID)
	if err != nil {
		return nil, err
	}

	var formatSQL string

	if len(t) == 10 {
		formatSQL = "%Y-%m-%d"
	} else if len(t) == 7 {
		formatSQL = "%Y-%m"
	} else if len(t) == 4 {
		formatSQL = "%Y"
	} else {
		return nil, errors.New("time error")
	}

	rows, err := db.Query(`select c.id, ifnull(c.commodity_name, ''), ifnull(category._name, ''),  
convert(c.quantity / 1000, decimal(11,3)), 
convert(c.payprice / 1000, decimal(11,3)), 
convert(c.sale_price / 1000, decimal(11,3)),
convert(c.sale_percent / 1000, decimal(11,3)),
c.sale_type
from his_consumption as c
left join his_bill as b on b.id = c.his_bill_id
left join con_category as category on category.id = c.con_category_id
where c.delflag = 0 and c.his_retreatbill_id = 0 
and c.commodity_ismeal = 0 
and c.meter_num = 0
and b.billtype in (1,4,6,7)
and DATE_FORMAT(b.paytime, ?) = ? 
and sale_employee_id = ?
and c.sys_hospital_id in (0,?)  `, formatSQL, t, employeeID, hospitalLocalId)
	if err != nil {
		return nil, err
	}

	res := make([]*models.Sale, 0)
	resTemp := make(map[string]*models.Sale, 0)

	sale := new(models.Sale)
	sale.Date = t
	sale.Seledetail = make([]models.Seledetail, 0)
	res = append(res, sale)
	resTemp[t] = sale

	for rows.Next() {
		var r models.Seledetail
		var saleType int
		var salePrice, salePercent float64
		err = rows.Scan(&r.ID, &r.Project, &r.Projecttype, &r.Num, &r.Amount, &salePrice, &salePercent, &saleType)
		if err != nil {
			return nil, err
		}

		sumPrice := r.Num * r.Amount
		r.Amount = sumPrice

		if saleType == 1 { // 按金额
			r.Royalty = r.Num * salePrice
		} else if saleType == 2 { // 按百分比
			if salePercent != 0 {
				r.Royalty = sumPrice * (salePercent / 100)
			}
		}

		resTemp[t].Sale++
		resTemp[t].Amounts += r.Royalty
		resTemp[t].Seledetail = append(resTemp[t].Seledetail, r)
	}

	return res, nil
}

// 查询员工服务列表
func QueryEmployeeServiceRoyalty(db *sql.DB, t string, employeeID int) (interface{}, error) {

	hospitalLocalId, err := QueryHospitalLocalIDByEmployeeLocalID(db, employeeID)
	if err != nil {
		return nil, err
	}

	var formatSQL string

	if len(t) == 10 {
		formatSQL = "%Y-%m-%d"
	} else if len(t) == 7 {
		formatSQL = "%Y-%m"
	} else if len(t) == 4 {
		formatSQL = "%Y"
	} else {
		return nil, errors.New("time error")
	}

	rows, err := db.Query(`select c.id, ifnull(c.commodity_name, ''), ifnull(category._name, ''), 
convert(c.quantity / 1000, decimal(11,3)), 
convert(c.payprice / 1000, decimal(11,3)), 
convert(c.service_price / 1000, decimal(11,3)),
convert(c.service_percent / 1000, decimal(11,3)),
c.service_type
from his_consumption as c
left join his_bill as b on b.id = c.his_bill_id
left join con_category as category on category.id = c.con_category_id
where c.delflag = 0 and c.his_retreatbill_id = 0 
and c.commodity_ismeal = 0 
and c.meter_num = 0
and b.billtype in (1,4,6,7)
and DATE_FORMAT(b.paytime, ?) = ?
and c.service_employee_id = ?
and c.sys_hospital_id in (0,?)  `, formatSQL, t, employeeID, hospitalLocalId)
	if err != nil {
		return nil, err
	}

	res := make([]*models.Sale, 0)
	resTemp := make(map[string]*models.Sale, 0)

	sale := new(models.Sale)
	sale.Date = t
	sale.Seledetail = make([]models.Seledetail, 0)
	res = append(res, sale)
	resTemp[t] = sale

	for rows.Next() {
		var r models.Seledetail
		var serviceType int
		var servicePrice, servicePercent float64

		err = rows.Scan(&r.ID, &r.Project, &r.Projecttype, &r.Num, &r.Amount, &servicePrice, &servicePercent, &serviceType)
		if err != nil {
			return nil, err
		}

		sumPrice := r.Num * r.Amount
		r.Amount = sumPrice

		if serviceType == 1 { // 按金额
			r.Royalty = r.Num * servicePrice
		} else if serviceType == 2 { // 按百分比
			if servicePercent != 0 {
				r.Royalty = sumPrice * (servicePercent / 100)
			}
		}
		resTemp[t].Sale++
		resTemp[t].Amounts += r.Royalty
		resTemp[t].Seledetail = append(resTemp[t].Seledetail, r)
	}

	return res, nil
}

// 查询员工业绩列表
func QueryEmployeeAchievement(db *sql.DB, t string, employeeID int) (interface{}, error) {

	hospitalLocalId, err := QueryHospitalLocalIDByEmployeeLocalID(db, employeeID)
	if err != nil {
		return nil, err
	}

	// 查出数据

	// 计算时间 及格式化的模板
	var formatSQL string

	if len(t) == 10 {
		formatSQL = "%Y-%m-%d"
	} else if len(t) == 7 {
		formatSQL = "%Y-%m"
	} else if len(t) == 4 {
		formatSQL = "%Y"
	} else {
		return nil, errors.New("time error")
	}

	// 查这个员工所有的 已结订单
	rows, err := db.Query(`select id from his_bill
where delflag = 0 
and billtype in (1,4,6,7)
and DATE_FORMAT(billtime, ?) = ?
and sys_hospital_id in (0,?)  `, formatSQL, t, hospitalLocalId)
	if err != nil {
		return nil, err
	}

	bills := make([]string, 0)
	for rows.Next() {

		var billID int
		err = rows.Scan(&billID)
		if err != nil {
			return nil, err
		}

		bills = append(bills, fmt.Sprintf("%v", billID))
	}

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

	if len(bills) <= 0 {
		return response, nil
	}

	billIDs := strings.Join(bills, ",")

	rows, err = db.Query(fmt.Sprintf(`select 
ifnull(convert(sum(quantity*payprice) / 1000000, decimal(11,3)), 0) as money,
bussinesstype
from his_consumption
where delflag = 0 
and his_retreatbill_id = 0 
and commodity_ismeal = 0
and meter_num = 0
and his_bill_id in (%s)
and sale_employee_id = ?
GROUP BY bussinesstype`, billIDs), employeeID)
	if err != nil {
		return nil, err
	}

	// 构建结构

	// DB:
	// 1：商品销售 2：挂号 3：住院每日消费 4.住院门诊 41：住院门诊化验  42：住院门诊影像 43：住院门诊处方
	// 44：住院门诊手术 45：住院门诊处置  5：住院商品销售 6：普通门诊 61：普通门诊化验  62：普通门诊影像
	// 63：普通门诊处方 64：普通门诊手术 65：普通门诊处置 7：美容 8：寄养每日消费 9：寄养商品销售 10：疫苗 11:驱虫

	//correspondingType := map[int]string{
	//	1:  "1",
	//	2:  "2",
	//	3:  "3,4,5,6",
	//	4:  "41,61",
	//	5:  "42,62",
	//	6:  "43,63",
	//	7:  "44,64",
	//	8:  "45,65",
	//	9:  "7",
	//	10: "8,9",
	//	11: "10",
	//	12: "11",
	//}

	// Map
	// 1 商品销售
	// 2 挂号
	// 3 住院每日消费
	// 4 化验
	// 5 影像
	// 6 处方
	// 7 手术
	// 8 处置
	// 9 美容
	// 10 寄养
	// 11 疫苗
	// 12 驱虫

	tempRes := map[int]*models.EmployeeAchievement{
		1:  {},
		2:  {},
		3:  {},
		4:  {},
		5:  {},
		6:  {},
		7:  {},
		8:  {},
		9:  {},
		10: {},
		11: {},
		12: {},
	}
	for businessType := range tempRes {
		temp := new(models.EmployeeAchievement)
		switch businessType {
		case 1:
			temp.Item = "商品销售"
			tempRes[1] = temp
		case 2:
			temp.Item = "挂号"
			tempRes[2] = temp
		case 3:
			temp.Item = "住院"
			tempRes[3] = temp
		case 4:
			temp.Item = "化验"
			tempRes[4] = temp
		case 5:
			temp.Item = "影像"
			tempRes[5] = temp
		case 6:
			temp.Item = "处方"
			tempRes[6] = temp
		case 7:
			temp.Item = "手术"
			tempRes[7] = temp
		case 8:
			temp.Item = "处置"
			tempRes[8] = temp
		case 9:
			temp.Item = "美容"
			tempRes[9] = temp
		case 10:
			temp.Item = "寄养"
			tempRes[10] = temp
		case 11:
			temp.Item = "疫苗"
			tempRes[11] = temp
		case 12:
			temp.Item = "驱虫"
			tempRes[12] = temp
		}
	}

	for rows.Next() {

		temp := new(models.EmployeeAchievement)
		var businessType int
		err = rows.Scan(&temp.Amount, &businessType)
		if err != nil {
			return nil, err
		}
		switch businessType {
		case 1:
			tempRes[1].Amount += temp.Amount
		case 2:
			tempRes[2].Amount += temp.Amount
		case 3:
			tempRes[3].Amount += temp.Amount
		case 4:
			tempRes[3].Amount += temp.Amount
		case 41:
			tempRes[4].Amount += temp.Amount
		case 42:
			tempRes[5].Amount += temp.Amount
		case 43:
			tempRes[6].Amount += temp.Amount
		case 44:
			tempRes[7].Amount += temp.Amount
		case 45:
			tempRes[8].Amount += temp.Amount
		case 5:
			tempRes[3].Amount += temp.Amount
		case 6:
			tempRes[3].Amount += temp.Amount
		case 61:
			tempRes[4].Amount += temp.Amount
		case 62:
			tempRes[5].Amount += temp.Amount
		case 63:
			tempRes[6].Amount += temp.Amount
		case 64:
			tempRes[7].Amount += temp.Amount
		case 65:
			tempRes[8].Amount += temp.Amount
		case 7:
			tempRes[9].Amount += temp.Amount
		case 8:
			tempRes[10].Amount += temp.Amount
		case 9:
			tempRes[10].Amount += temp.Amount
		case 10:
			tempRes[11].Amount += temp.Amount
		case 11:
			tempRes[12].Amount += temp.Amount
		}
	}

	// 组合数据
	business := []int{1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12}

	employeeMoney, err := queryEmployeeMoney(db, billIDs, employeeID)
	if err != nil {
		return nil, err
	}

	// 次卡充值费用
	meterCardRecharge, err := MeterCardRecharge(db, t, formatSQL, employeeID, hospitalLocalId)
	if err != nil {
		return nil, err
	}

	// 总额
	employeeMoney += meterCardRecharge

	// 计算百分比
	if employeeMoney > 0 {

		for _, v := range business {
			s := fmt.Sprintf("%.3f", (tempRes[v].Amount/employeeMoney)*100)
			f, _ := strconv.ParseFloat(s, 64)
			tempRes[v].Per = f
			response = append(response, tempRes[v])
		}

		if meterCardRecharge > 0 {
			//meterCardRechargeSum, err := MeterCardRecharge(db, t, formatSQL, 0)
			//if err != nil {
			//	return nil, err
			//}

			meterCardByEmployee := new(models.EmployeeAchievement)
			meterCardByEmployee.Amount = meterCardRecharge
			meterCardByEmployee.Item = "次卡"
			if employeeMoney > 0 {
				s := fmt.Sprintf("%.3f", (meterCardByEmployee.Amount/employeeMoney)*100)
				f, _ := strconv.ParseFloat(s, 64)
				meterCardByEmployee.Per = f
			}

			response = append(response, meterCardByEmployee)
		}
	}

	return response, nil
}

// 查询某个员工某个时间段 所有的营业额
func queryEmployeeMoney(db *sql.DB, billIDs string, employeeID int) (float64, error) {

	var c float64
	err := db.QueryRow(fmt.Sprintf(`select 
convert(ifnull(sum(quantity*payprice), 0) / 1000000, decimal(11,3))
from his_consumption
where delflag = 0 
and his_retreatbill_id = 0 
and commodity_ismeal = 0
and meter_num = 0
and his_bill_id in (%s) 
and sale_employee_id = ?`, billIDs), employeeID).Scan(&c)

	return c, err
}

func EmployeeBusinessBillRes(db *sql.DB, t, formatSQL, timeFormat string, employeeID int, loginID string) (interface{}, error) {

	loginIDInt, _ := strconv.Atoi(loginID)

	hospitalLocalId, err := QueryHospitalLocalIDByEmployeeLocalID(db, loginIDInt)
	if err != nil {
		return 0, err
	}

	//已接诊、疫苗驱虫、美容洗澡、住院寄养
	response := new(models.EmployeeBusinessBill)

	appointmentSQL := fmt.Sprintf(`select count(*) as c from his_appointment 
where delflag = 0 
and DATE_FORMAT(appointment_time,'%v') = '%v'   and  sys_hospital_id in (0,%v)  `, formatSQL, t, hospitalLocalId)

	// 接诊
	clinicSQL := fmt.Sprintf(`select count(*) as c from his_clinic 
where delflag = 0 
and DATE_FORMAT(eventtime,'%v') = '%v'   and  sys_hospital_id in (0,%v)  `, formatSQL, t, hospitalLocalId)

	// 疫苗
	protectionSQL := fmt.Sprintf(`select count(*) as c from his_protection_detail 
where delflag = 0 
and DATE_FORMAT(eventtime,'%v') = '%v'     and  sys_hospital_id in (0,%v)  `, formatSQL, t, hospitalLocalId)

	// insect
	insectSQL := fmt.Sprintf(`select count(*) as c from his_insect_detail 
where delflag = 0 
and DATE_FORMAT(eventtime, '%v') = '%v'   and  sys_hospital_id in (0,%v)   `, formatSQL, t, hospitalLocalId)

	// 美容
	beautySQL := fmt.Sprintf(`select count(*) from his_beauty
where delflag = 0 
and DATE_FORMAT(eventtime,'%v') = '%v'    and  sys_hospital_id in (0,%v)  `, formatSQL, t, hospitalLocalId)

	if employeeID != 0 {

		appointmentSQL += fmt.Sprintf(` and con_employee_id = %v `, employeeID)
		clinicSQL += fmt.Sprintf(` and cure_employee_id = %v `, employeeID)
		protectionSQL += fmt.Sprintf(` and cure_employee_id = %v `, employeeID)
		insectSQL += fmt.Sprintf(` and cure_employee_id = %v `, employeeID)
		beautySQL += fmt.Sprintf(` and sale_employee_id = %v  `, employeeID)
	}
	var appointmentNumber, clinicNumber, protectionNumber, insectNumber, beautyNumber int

	//global.GVA_LOG.Info(fmt.Sprintf("appointmentSQL :%v  ", appointmentSQL))
	//global.GVA_LOG.Info(fmt.Sprintf("clinicSQL :%v  ", clinicSQL))
	//global.GVA_LOG.Info(fmt.Sprintf("protectionSQL :%v  ", protectionSQL))
	//global.GVA_LOG.Info(fmt.Sprintf("insectSQL :%v  ", insectSQL))
	//global.GVA_LOG.Info(fmt.Sprintf("beautySQL :%v  ", beautySQL))

	err = db.QueryRow(appointmentSQL).Scan(&appointmentNumber)
	if err != nil {
		return nil, err
	}
	err = db.QueryRow(clinicSQL).Scan(&clinicNumber)
	if err != nil {
		return nil, err
	}
	err = db.QueryRow(protectionSQL).Scan(&protectionNumber)
	if err != nil {
		return nil, err
	}
	err = db.QueryRow(insectSQL).Scan(&insectNumber)
	if err != nil {
		return nil, err
	}
	err = db.QueryRow(beautySQL).Scan(&beautyNumber)
	if err != nil {
		return nil, err
	}

	response.Appointment = appointmentNumber
	response.AlreadyAccepted = clinicNumber
	response.VaccineInsect = protectionNumber + insectNumber
	response.Beauty = beautyNumber

	hospitalFoster, err := QueryHospitalAndFosterBill(db, formatSQL, t, timeFormat, employeeID, hospitalLocalId)
	if err != nil {
		return nil, err
	}
	response.HospitalFoster = hospitalFoster

	return response, nil
}

// 首页 今日营业额、今日毛利、今日支出
func TodayDataRes(db *sql.DB, employeeID int, chainCode string) (interface{}, error) {

	hospitalLocalId, err := QueryHospitalLocalIDByEmployeeLocalID(db, employeeID)
	if err != nil {
		return nil, err
	}

	timeNow := time.Now().Format(`2006-01-02`)
	formatSQL := "%Y-%m-%d"

	response := new(models.TodayData)

	//1 - 首页营业额:
	//按已结账单 + 次卡充值+赠送金额结算 （不计算次卡消费）

	billOfSettlement, err := BillOfSettlement(db, timeNow, formatSQL, hospitalLocalId)
	if err != nil {
		return nil, err
	}

	meterCardRecharge, err := MeterCardRecharge(db, timeNow, formatSQL, 0, hospitalLocalId)
	if err != nil {
		return nil, err
	}

	response.Turnover = billOfSettlement + meterCardRecharge

	//3、毛利
	//毛利=营业额-成本
	billByCost, err := BillByCost(db, timeNow, formatSQL, hospitalLocalId, chainCode)
	if err != nil {
		return nil, err
	}

	response.GrossProfit = response.Turnover - billByCost

	//2 - 首页支出:
	//1. 退押金
	retreatDepositMoney, err := RetreatDepositMoney(db, timeNow, formatSQL, hospitalLocalId)
	if err != nil {
		return nil, err
	}

	//2. 退会员卡
	retreatConsumerCard, err := RetreatConsumerCard(db, timeNow, formatSQL, hospitalLocalId)
	if err != nil {
		return nil, err
	}

	//3. 退次卡
	retreatMeterCard, err := RetreatMeterCard(db, timeNow, formatSQL, hospitalLocalId)
	if err != nil {
		return nil, err
	}

	//4. 支出管理中的
	outChargeBill, err := OutChargeBill(db, timeNow, formatSQL, hospitalLocalId)
	if err != nil {
		return nil, err
	}

	//5. 预付款
	preOutFundBill, err := PreOutFundBill(db, timeNow, formatSQL, hospitalLocalId)
	if err != nil {
		return nil, err
	}

	//6. 入库结算
	stockInBill, err := StockInBill(db, timeNow, formatSQL, hospitalLocalId)
	if err != nil {
		return nil, err
	}

	response.Expenditure = retreatDepositMoney + retreatConsumerCard + retreatMeterCard + outChargeBill + preOutFundBill + stockInBill

	// 支出减掉 退预付款的金额
	//RetreatPreOutFundBill
	retreatPreOutFundBill, err := RetreatPreOutFundBill(db, timeNow, formatSQL, hospitalLocalId)
	if err != nil {
		return nil, err
	}

	response.Expenditure -= retreatPreOutFundBill

	return response, nil
}

func Appointment(db *sql.DB, formatSQL, t string, employeeID, hospitalLocalId int) (interface{}, error) {

	sqlStr := fmt.Sprintf(` select a.appointment_time, a.appointment_name, 
a.con_employee_name, a.his_pet_name, species, kind, 
his_consumer_name, c.telephone
from his_appointment as a
left join his_pet as p on a.his_pet_id = p.id
left join his_consumer as c on a.his_consumer_id = c.id
where a.delflag = 0 and DATE_FORMAT(a.appointment_time,?) = ?
and  a.sys_hospital_id in (0,%v)   and a.first_vaccine = 0
`, hospitalLocalId)
	if employeeID != 0 {
		sqlStr += fmt.Sprintf(` and a.con_employee_id = %v`, employeeID)
	}

	sqlStr += ` order by a.appointment_time asc `

	rows, err := db.Query(sqlStr, formatSQL, t)
	if err != nil {
		return nil, err
	}

	response := make([]*models.Appointment, 0)
	for rows.Next() {
		var temp models.Appointment
		err = rows.Scan(&temp.AppointmentTime, &temp.AppointmentName, &temp.ConEmployeeName,
			&temp.HisPetName, &temp.Species, &temp.Kind, &temp.HisConsumerName, &temp.Telephone)
		if err != nil {
			return nil, err
		}
		response = append(response, &temp)
	}

	return response, nil
}

func Clinic(db *sql.DB, formatSQL, t string, employeeID, hospitalLocalId int) (interface{}, error) {

	sqlStr := fmt.Sprintf(`select c.eventtime, c.cure_employee_name, p._name, p.kind, c.abstract
from his_clinic as c
left join his_pet as p on p.id = c.his_pet_id
where c.delflag = 0 and DATE_FORMAT(c.eventtime,?) = ?   and  c.sys_hospital_id in (0,%v) `, hospitalLocalId)

	if employeeID > 0 {
		var w string
		w = fmt.Sprintf(" and c.cure_employee_id = %v", employeeID)
		sqlStr += w
	}

	rows, err := db.Query(sqlStr, formatSQL, t)
	if err != nil {
		return nil, err
	}

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

	for rows.Next() {
		var temp models.Clinic
		var kind string
		err = rows.Scan(&temp.Time, &temp.EmployeeName, &temp.PetName, &kind, &temp.Diagnosis)
		if err != nil {
			return nil, err
		}
		temp.PetName = fmt.Sprintf("%s(%s)", temp.PetName, kind)
		response = append(response, &temp)
	}

	return response, nil
}

func ProtectionAndInsect(db *sql.DB, formatSQL, t string, employeeID, hospitalLocalId int) (interface{}, error) {

	sqlStr1 := fmt.Sprintf(`select d.eventtime, d.cure_employee_name, 
pet._name, pet.kind, d._name 
from his_protection_detail as d
left join his_protection as p on d.his_protection_id = p.id
left join his_pet as pet on pet.id = p.his_pet_id
where d.delflag = 0 and DATE_FORMAT(d.eventtime,?) = ?    and  d.sys_hospital_id in (0,%v)  `, hospitalLocalId)

	if employeeID > 0 {
		var w string
		w = fmt.Sprintf(" and d.cure_employee_id = %v", employeeID)
		sqlStr1 += w
	}

	sqlStr2 := fmt.Sprintf(`
UNION ALL
select d.eventtime, d.cure_employee_name, 
pet._name, pet.kind, d._name 
from his_insect_detail as d
left join his_insect as p on d.his_insect_id = p.id
left join his_pet as pet on pet.id = p.his_pet_id
where d.delflag = 0 and DATE_FORMAT(d.eventtime,?) = ? and  d.sys_hospital_id in (0,%v)  `, hospitalLocalId)

	if employeeID > 0 {
		var w string
		w = fmt.Sprintf(" and d.cure_employee_id = %v", employeeID)
		sqlStr2 += w
	}

	rows, err := db.Query(sqlStr1+sqlStr2, formatSQL, t, formatSQL, t)
	if err != nil {
		return nil, err
	}

	response := make([]*models.ProtectionInsect, 0)
	for rows.Next() {
		var temp models.ProtectionInsect
		var kind string
		err = rows.Scan(&temp.Time, &temp.EmployeeName, &temp.PetName, &kind, &temp.ProtectionOrInsect)
		if err != nil {
			return nil, err
		}
		temp.PetName = fmt.Sprintf("%s(%s)", temp.PetName, kind)
		response = append(response, &temp)
	}

	return response, nil
}

func Beauty(db *sql.DB, formatSQL, t string, employeeID, hospitalLocalId int) (interface{}, error) {

	sqlStr := fmt.Sprintf(`select b.eventtime, b.service_employee_name, p._name, p.kind
from his_beauty as b
left join his_pet as p on p.id = b.his_pet_id
where b.delflag = 0 and DATE_FORMAT(b.eventtime,?) = ?  and   and  c.sys_hospital_id in (0,%v)  `, hospitalLocalId)

	if employeeID > 0 {
		var w string
		w = fmt.Sprintf(" and b.sale_employee_id = %v", employeeID)
		sqlStr += w
	}

	rows, err := db.Query(sqlStr, formatSQL, t)
	if err != nil {
		return nil, err
	}

	response := make([]models.Beauty, 0)
	for rows.Next() {
		var temp models.Beauty
		var kind string
		err = rows.Scan(&temp.Time, &temp.EmployeeName, &temp.PetName, &kind)
		if err != nil {
			return nil, err
		}
		temp.PetName = fmt.Sprintf("%s(%s)", temp.PetName, kind)
		response = append(response, temp)
	}

	return response, nil
}

func HospitalAndFoster(db *sql.DB, formatSQL, t, timeFormat string, employeeID, hospitalLocalId int) (interface{}, error) {

	// 当天日期
	timeParam, err := time.Parse(timeFormat, t)
	if err != nil {
		return nil, err
	}

	sqlStr1 := fmt.Sprintf(`select DATE_FORMAT(h.intime,?), DATE_FORMAT(h.outtime,?), h.medical_employee_name, 
p._name, p.kind, convert(ifnull(c.deposit_money, 0) / 1000, decimal(11,2)), h.state, h.autoout, "住院"
from his_hospital as h
left join his_pet as p on p.id = h.his_pet_id
left join his_consumer as c on c.id = h.his_consumer_id
where h.delflag = 0 and  h.sys_hospital_id in (0,%v)  `, hospitalLocalId)

	if employeeID > 0 {
		var w string
		w = fmt.Sprintf(" and h.medical_employee_id = %v ", employeeID)
		sqlStr1 += w
	}

	sqlStr2 := fmt.Sprintf(` 
UNION ALL
select DATE_FORMAT(h.intime,?), DATE_FORMAT(h.outtime,?), h.main_employee_name, 
p._name, p.kind, convert(ifnull(c.deposit_money, 0) / 1000, decimal(11,2)), h.state, h.autoout, "寄养"
from his_foster as h
left join his_pet as p on p.id = h.his_pet_id
left join his_consumer as c on c.id = h.his_consumer_id
where h.delflag = 0  and  h.sys_hospital_id in (0,%v)   `, hospitalLocalId)

	if employeeID > 0 {
		var w string
		w = fmt.Sprintf(" and h.main_employee_id = %v ", employeeID)
		sqlStr2 += w
	}

	rows, err := db.Query(sqlStr1+sqlStr2, formatSQL, formatSQL, formatSQL, formatSQL)
	if err != nil {
		return nil, err
	}

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

	for rows.Next() {
		temp := new(models.HospitalFoster)
		var inTime, outTime, kind string
		var state, autoout int

		err = rows.Scan(&inTime, &outTime, &temp.EmployeeName, &temp.PetName, &kind, &temp.DepositMoney, &state, &autoout, &temp.HospitalOrFoster)
		if err != nil {
			return nil, err
		}

		if temp.HospitalOrFoster == "住院" {

			if state == 0 { // 住院中

				// 如果是手动出院的话 截止时间就是今天
				// 如果是自动出院、截止日期就是 outTime
				if autoout == 0 {
					outTime = t
				}

				// 住院截止日期
				endTime, err := time.Parse(timeFormat, outTime)
				if err != nil {
					return nil, err
				}

				// 住院开始日期
				startTime, err := time.Parse(timeFormat, inTime)
				if err != nil {
					return nil, err
				}

				// 筛选日期、必须要大于等于 住院开始时间
				// 筛选日期、必须要小于等于 住院结束时间
				if timeParam.Sub(startTime).Hours() < 0 || timeParam.Sub(endTime).Hours() > 0 {
					continue
				}

				checkInDay := int(timeParam.Sub(startTime).Hours() / 24)

				temp.HospitalDay = fmt.Sprintf("%v", checkInDay+1)

				temp.PetName = fmt.Sprintf("%s(%s)", temp.PetName, kind)
				response = append(response, temp)

			} else { // 已出院

				// 住院截止日期
				endTime, err := time.Parse(timeFormat, outTime)
				if err != nil {
					return nil, err
				}

				// 住院开始日期
				startTime, err := time.Parse(timeFormat, inTime)
				if err != nil {
					return nil, err
				}

				// 筛选日期、必须要大于等于 住院开始时间
				// 筛选日期、必须要小于等于 住院结束时间
				if timeParam.Sub(startTime).Hours() < 0 || timeParam.Sub(endTime).Hours() > 0 {
					continue
				}

				checkInDay := int(endTime.Sub(startTime).Hours() / 24)

				temp.HospitalDay = fmt.Sprintf("%v", checkInDay+1)

				temp.PetName = fmt.Sprintf("%s(%s)", temp.PetName, kind)
				response = append(response, temp)
			}
		} else {
			// 寄养截止日期
			endTime, err := time.Parse(timeFormat, outTime)
			if err != nil {
				return nil, err
			}

			// 住院开始日期
			startTime, err := time.Parse(timeFormat, inTime)
			if err != nil {
				return nil, err
			}

			// 筛选日期、必须要大于等于 住院开始时间
			// 筛选日期、必须要小于等于 住院结束时间
			if timeParam.Sub(startTime).Hours() < 0 || timeParam.Sub(endTime).Hours() > 0 {
				continue
			}

			if timeParam.Sub(endTime).Hours() <= 0 {

				checkInDay := int(timeParam.Sub(startTime).Hours() / 24)
				temp.HospitalDay = fmt.Sprintf("%v", checkInDay+1)
			} else {
				checkInDay := int(endTime.Sub(startTime).Hours() / 24)
				temp.HospitalDay = fmt.Sprintf("%v", checkInDay+1)
			}

			temp.PetName = fmt.Sprintf("%s(%s)", temp.PetName, kind)
			response = append(response, temp)
		}

	}

	return response, nil
}

func QueryHospitalAndFosterBill(db *sql.DB, formatSQL, t, timeFormat string, employeeID, hospitalLocalId int) (int, error) {

	var bill int

	// 当天日期
	timeParam, err := time.Parse(timeFormat, t)
	if err != nil {
		return 0, err
	}

	sqlStr1 := fmt.Sprintf(`select DATE_FORMAT(h.intime,?), DATE_FORMAT(h.outtime,?), h.medical_employee_name, 
ifnull(p._name, ''), ifnull(p.kind, ''), convert(ifnull(c.deposit_money, 0) / 1000, decimal(11,2)), h.state, h.autoout, "住院"
from his_hospital as h
left join his_pet as p on p.id = h.his_pet_id
left join his_consumer as c on c.id = h.his_consumer_id
where h.delflag = 0  and  h.sys_hospital_id in (0,%v)  `, hospitalLocalId)

	if employeeID > 0 {
		var w string
		w = fmt.Sprintf(" and h.medical_employee_id = %v   ", employeeID)
		sqlStr1 += w
	}

	sqlStr2 := fmt.Sprintf(` 
UNION ALL
select DATE_FORMAT(h.intime,?), DATE_FORMAT(h.outtime,?), h.main_employee_name, 
ifnull(p._name, ''), ifnull(p.kind, ''), convert(ifnull(c.deposit_money, 0) / 1000, decimal(11,2)), h.state, h.autoout, "寄养"
from his_foster as h
left join his_pet as p on p.id = h.his_pet_id
left join his_consumer as c on c.id = h.his_consumer_id
where h.delflag = 0    and  h.sys_hospital_id in (0,%v)   `, hospitalLocalId)

	if employeeID > 0 {
		var w string
		w = fmt.Sprintf(" and h.main_employee_id = %v ", employeeID)
		sqlStr2 += w
	}

	rows, err := db.Query(sqlStr1+sqlStr2, formatSQL, formatSQL, formatSQL, formatSQL)
	if err != nil {
		return 0, err
	}

	for rows.Next() {
		temp := new(models.HospitalFoster)
		var inTime, outTime, kind string
		var state, autoout int

		err = rows.Scan(&inTime, &outTime, &temp.EmployeeName, &temp.PetName, &kind, &temp.DepositMoney, &state, &autoout, &temp.HospitalOrFoster)
		if err != nil {
			return 0, err
		}

		if temp.HospitalOrFoster == "住院" {

			if state == 0 { // 住院中

				// 如果是手动出院的话 截止时间就是今天
				// 如果是自动出院、截止日期就是 outTime
				if autoout == 0 {
					outTime = t
				}

				// 住院截止日期
				endTime, err := time.Parse(timeFormat, outTime)
				if err != nil {
					return 0, err
				}

				// 住院开始日期
				startTime, err := time.Parse(timeFormat, inTime)
				if err != nil {
					return 0, err
				}

				// 筛选日期、必须要大于等于 住院开始时间
				// 筛选日期、必须要小于等于 住院结束时间
				if timeParam.Sub(startTime).Hours() < 0 || timeParam.Sub(endTime).Hours() > 0 {
					continue
				}

				bill++
			} else { // 已出院

				// 住院截止日期
				endTime, err := time.Parse(timeFormat, outTime)
				if err != nil {
					return 0, err
				}

				// 住院开始日期
				startTime, err := time.Parse(timeFormat, inTime)
				if err != nil {
					return 0, err
				}

				// 筛选日期、必须要大于等于 住院开始时间
				// 筛选日期、必须要小于等于 住院结束时间
				if timeParam.Sub(startTime).Hours() < 0 || timeParam.Sub(endTime).Hours() > 0 {
					continue
				}

				bill++
			}
		} else {
			// 寄养截止日期
			endTime, err := time.Parse(timeFormat, outTime)
			if err != nil {
				return 0, err
			}

			// 住院开始日期
			startTime, err := time.Parse(timeFormat, inTime)
			if err != nil {
				return 0, err
			}

			// 筛选日期、必须要大于等于 住院开始时间
			// 筛选日期、必须要小于等于 住院结束时间
			if timeParam.Sub(startTime).Hours() < 0 || timeParam.Sub(endTime).Hours() > 0 {
				continue
			}

			bill++
		}

	}

	return bill, nil
}