device.go

package repository

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

	"github.com/uniplaces/carbon"
)

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

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

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

	var response models.DeviceSum

	putSQL := `select ifnull(sum(pd.current_month), 0), ifnull(sum(convert(ifnull(pd.device_money, 0) / 1000, decimal(11,3))), 0)
from device_put_detail pd
left join device_put as p on p.id = pd.device_put_id
where pd.delflag = 0 and p.province != '' and DATE_FORMAT(pd.date_of_collection, ?) = ?`

	if deviceID != -1 {
		putSQL += fmt.Sprintf(` and p.device_id = %v`, deviceID)
	}

	err := db.QueryRow(putSQL, formatSQL, t).Scan(&response.ExposureCount, &response.CollectionMoney)
	if err != nil {
		return nil, err
	}

	return response, nil
}

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

	// 计算时间 及格式化的模板
	var formatSQL, formatTime string
	var sub int

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

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

	var endT *carbon.Carbon

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

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

	// 构造list 数据
	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)
		}

		analysisRepository, err := deviceCollectionAnalysisRepository(db, str, formatSQL, deviceID)
		if err != nil {
			return nil, err
		}

		var c1 models.CollectionAnalysis
		var c2 models.CollectionAnalysis

		c1.Year = str
		c2.Year = str

		c1.Type = "收款"
		c2.Type = "曝光"

		c1.Value = fmt.Sprintf("%v", analysisRepository.Amount)
		c2.Value = fmt.Sprintf("%v", analysisRepository.Count)

		response = append(response, c1, c2)
	}

	return response, nil
}

func deviceCollectionAnalysisRepository(db *sql.DB, t, formatSQL string, deviceID int) (*models.DeviceAnalysis, error) {

	var r models.DeviceAnalysis

	putSQL := `select ifnull(sum(d.current_month), 0), ifnull(sum(convert(ifnull(d.device_money, 0) / 1000, decimal(11,3))), 0) 
from device_put_detail as d
left join device_put as p on p.id = d.device_put_id
where d.delflag = 0 and DATE_FORMAT(d.date_of_collection, ?) = ?`

	if deviceID != -1 {
		putSQL += fmt.Sprintf(` and p.device_id = %v`, deviceID)
	}

	err := db.QueryRow(putSQL, formatSQL, t).Scan(&r.Count, &r.Amount)

	return &r, err
}

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

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

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

	putSQL := `select p.province, sum(pd.current_month), sum(convert(ifnull(pd.device_money, 0) / 1000, decimal(11,3)))
from device_put as p
left join device_put_detail as pd on pd.device_put_id = p.id
where province != '' and p.delflag = 0 and pd.delflag = 0
and  DATE_FORMAT(pd.date_of_collection, ?) = ?
`

	if deviceID != -1 {
		putSQL += fmt.Sprintf(` and p.device_id = %v`, deviceID)
	}

	putSQL += ` GROUP BY province_id  ORDER BY p.province_id asc`

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

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

	var sumMoney float64

	for rows.Next() {

		var t models.CollectionDistribution
		var money float64
		err = rows.Scan(&t.Item, &t.Count, &money)
		if err != nil {
			return nil, err
		}
		sumMoney += money
		t.Amount = fmt.Sprintf("%v", money)
		response = append(response, &t)
	}

	for k, v := range response {
		money, _ := strconv.ParseFloat(v.Amount, 10)
		response[k].Royalty = fmt.Sprintf("%.2f", (money/sumMoney)*100)
	}

	return response, nil
}

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

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

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

	putSQL := `select p._name, sum(convert(ifnull(pd.device_money, 0) / 1000, decimal(11,3))) as money
from device_put as p
left join device_put_detail as pd on pd.device_put_id = p.id
where province != '' and p.delflag = 0 and pd.delflag = 0
and  DATE_FORMAT(pd.date_of_collection, ?) = ?`

	if deviceID != -1 {
		putSQL += fmt.Sprintf(` and p.device_id = %v`, deviceID)
	}

	putSQL += ` GROUP BY _name  ORDER BY money desc`

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

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

	for rows.Next() {

		var t models.HospitalRank
		err = rows.Scan(&t.Name, &t.CollectionMoney)
		if err != nil {
			return nil, err
		}
		response = append(response, &t)
	}

	return response, nil
}

func DeviceHospitalRankDetail(db *sql.DB, t, hospitalName string, deviceID int) (interface{}, error) {

	// 计算时间 及格式化的模板
	var formatSQL, formatTime string
	var sub int

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

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

	var endT *carbon.Carbon

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

	putSQL := `select id from device_put where _name = ? `

	if deviceID != -1 {
		putSQL += fmt.Sprintf(` and device_id = %v`, deviceID)
	}

	// 查询医院的所有投放单
	rows, err := db.Query(putSQL, hospitalName)
	if err != nil {
		return nil, err
	}

	ids := make([]string, 0)

	for rows.Next() {
		var id int64
		err = rows.Scan(&id)
		if err != nil {
			return nil, err
		}
		ids = append(ids, fmt.Sprintf("%v", id))
	}

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

	querySQL := fmt.Sprintf(`select ifnull(sum(current_month), 0), ifnull(sum(convert(ifnull(device_money, 0) / 1000, decimal(11,3))), 0) from device_put_detail
where delflag = 0 and device_put_id in (%s) and DATE_FORMAT(date_of_collection, ?) = ?`, strings.Join(ids, ","))

	// 构造list 数据
	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)
		}

		var temp models.HospitalRankDetail

		if len(ids) > 0 {
			err = db.QueryRow(querySQL, formatSQL, str).Scan(&temp.Value, &temp.Type)
			switch {
			case err == sql.ErrNoRows:
				err = nil
			case err != nil:
				return nil, err
			}
		}

		temp.Year = str
		response = append(response, temp)
	}

	return response, nil
}