case_history_analysis.go

package repository

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

func QueryCaseHistoryAndMoney(db *sql.DB, t string) (interface{}, error) {

	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")
	}

	var res models.CaseHistoryAnalysis

	err := db.QueryRow(`select count(t.abstract) as counts, ifnull(sum(t.money), 0) from (
select cli.abstract as abstract, 
(select ifnull(convert(sum(c.quantity*c.payprice) / 1000000, decimal(11,3)), 0)
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 c.his_bill_id != 0 
and c.bussinesstype in (41,42,43,44,45,61,62,63,64,65)
and b.billtype in (1,4,6,7)
and c.parent_id = cli.id
) as money
from his_clinic as cli
where cli.delflag = 0 
and DATE_FORMAT(cli.eventtime, ?) = ? 
) as t`, formatSQL, t).Scan(&res.Count, &res.Money)

	if err != nil {
		return nil, err
	}

	return res, nil
}

func QuerySpeciesAndAgeByKindRepository(db *sql.DB, t string, _type int) (interface{}, error) {

	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 t.species, count(pet_id) from (
select p.species as species, c.his_pet_id as pet_id 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 p.con_kind_id = ?
GROUP BY c.his_pet_id
) as t
GROUP BY t.species
limit 6`, formatSQL, t, _type)
	if err != nil {
		return nil, err
	}

	var response models.Kind
	kindDetail := make([]models.KindDetail, 0)
	ageDetail := make([]models.KindDetail, 5)

	for rows.Next() {
		var temp models.KindDetail
		err = rows.Scan(&temp.Type, &temp.Cost)
		if err != nil {
			return nil, err
		}
		temp.A = "1"
		response.Count += temp.Cost
		kindDetail = append(kindDetail, temp)

	}

	// 计算病历中所有宠物的数量
	var sumPetNumber int
	err = db.QueryRow(`select count(t.counts) from (
select count(c.id) as counts from his_clinic as c
where c.delflag = 0 and DATE_FORMAT(c.eventtime, ?) = ? 
GROUP BY c.his_pet_id
) as t`, formatSQL, t).Scan(&sumPetNumber)
	if err != nil {
		return nil, err
	}

	// 计算当前品种 所占百分比
	if sumPetNumber != 0 {
		response.Proportion = fmt.Sprintf("%.2f", float64(response.Count)/float64(sumPetNumber)*100)
	} else {
		response.Proportion = "0%"
	}

	// 获取年龄分布
	rows, err = db.Query(`select TIMESTAMPDIFF(YEAR, p.birthday, CURDATE()) 
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 p.con_kind_id = ?
and p.birthday != '0000-00-00 00:00:00' 
GROUP BY c.his_pet_id`, formatSQL, t, _type)
	if err != nil {
		return nil, err
	}

	ages := make([]int, 5)
	for rows.Next() {
		var temp int
		err = rows.Scan(&temp)
		if err != nil {
			return nil, err
		}
		switch {
		case temp < 1:
			ages[0]++
		case temp >= 1 && temp < 3:
			ages[1]++
		case temp >= 3 && temp < 5:
			ages[2]++
		case temp >= 5 && temp < 8:
			ages[3]++
		case temp > 8:
			ages[4]++
		}
	}

	for k, v := range ages {
		switch k {
		case 0:
			ageDetail[k].Type = "1岁以下"
			ageDetail[k].Cost = v
			ageDetail[k].A = "1"
		case 1:
			ageDetail[k].Type = "1-3岁"
			ageDetail[k].Cost = v
			ageDetail[k].A = "1"
		case 2:
			ageDetail[k].Type = "3-5岁"
			ageDetail[k].Cost = v
			ageDetail[k].A = "1"
		case 3:
			ageDetail[k].Type = "5-8岁"
			ageDetail[k].Cost = v
			ageDetail[k].A = "1"
		case 4:
			ageDetail[k].Type = "8岁以上"
			ageDetail[k].Cost = v
			ageDetail[k].A = "1"
		}
	}

	resultAgeDetail := make([]models.KindDetail, 0)

	for _, v := range ageDetail {
		if v.Cost != 0 {
			resultAgeDetail = append(resultAgeDetail, v)
		}
	}

	response.KindDetails = kindDetail
	response.AgeDetails = resultAgeDetail

	return response, nil
}

func DiseaseAndOperationRankRepository(db *sql.DB, t string, _type int) (interface{}, error) {

	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")
	}

	sqlStr := ""
	if _type == 1 {
		sqlStr = `select t.abstract, count(t.abstract) as counts, ifnull(sum(t.money), 0) from (
select c.abstract as abstract, 
(select ifnull(convert(sum(co.quantity*co.payprice) / 1000000, decimal(11,3)), 0)
from his_consumption as co
left join his_bill as b on b.id = co.his_bill_id
where co.delflag = 0 
and co.his_retreatbill_id = 0 
and co.commodity_ismeal = 0
and co.meter_num = 0
and co.his_bill_id != 0 
and co.bussinesstype in (41,42,43,44,45,61,62,63,64,65)
and b.billtype in (1,4,6,7)
and co.parent_id = c.id) as money
from his_clinic as c
where c.delflag = 0 
and c.abstract != ''
and DATE_FORMAT(c.eventtime, ?) = ? 
) as t
GROUP BY t.abstract
ORDER BY counts desc
limit 8`
	} else {
		sqlStr = `select commodity_name, ifnull(convert(sum(quantity) / 1000, decimal(11,0)), 0) as counts, 
ifnull(convert(sum(quantity*payprice) / 1000000, decimal(11,3)), 0)
from his_consumption
where delflag = 0 
and his_retreatbill_id = 0 
and commodity_ismeal = 0
and meter_num = 0
and his_bill_id != 0  
and bussinesstype in (44, 64)
and DATE_FORMAT(eventtime, ?) = ?
GROUP BY commodity_name
ORDER BY counts desc
limit 8`
	}

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

	response := make([]models.ClinicTop, 0)
	for rows.Next() {
		var temp models.ClinicTop
		err = rows.Scan(&temp.Name, &temp.Num, &temp.Amount)
		if err != nil {
			return nil, err
		}
		response = append(response, temp)
	}

	return response, nil
}