hongze_api/models/bill.go

package models

import (
	"fmt"
	"github.com/beego/beego/v2/client/orm"
	"hongze/hongze_api/utils"
	"time"
)

type BillDetailResp struct {
	RealName                     string  `description:"用户实际名称"`
	TogetherDay                  string  `description:"相伴天数"`
	CreateDate                   string  `description:"创建时间"`
	FirstReadReportType          string  `description:"首次阅读报告类型"`
	FirstReadReportTitle         string  `description:"首次阅读报告标题"`
	ListenReportCount            int     `description:"收听报告次数"`
	ListenReportDuration         float64 `description:"收听报告时长"`
	MaxReadReportDate            string  `description:"阅读报告最多的一天"`
	MaxReadReportCount           int     `description:"阅读报告最多的一天,报告数量"`
	LatestReadReportDate         string  `description:"阅读报告时间最晚的一天"`
	LatestReadReportTime         string  `description:"阅读报告时间最晚的一天,最晚的时间"`
	LatestReadReportDateDuration float64 `description:"阅读报告时间最晚的一天,总共阅读报告的时长"`
	MaxOpenReportClassify        string  `description:"打开次数最多报告的栏目"`
	MaxOpenReportCount           int     `description:"打开次数最多报告的栏目下,用户阅读的报告数"`
	TotalReadDuration            float64 `description:"总阅读时长"`
	TotalReportDayCount          int     `description:"总阅读晨报数"`
	TotalReportWeekCount         int     `description:"总阅读周报数"`
	TotalReportMonthCount        int     `description:"总阅读月报数"`
	TotalReportTwoWeekCount      int     `description:"总阅读双周报数"`
	TotalReportRddpCount         int     `description:"总阅读点评数"`
	TotalReport                  int     `description:"总阅读报告数"`
	LearnDay                     int     `description:"连续学习天数"`
}

type ReadReportType struct {
	ReportType string
	CreateTime time.Time
	Title      string
	Stage      int
}

func GetRddpMinReportType(uid int, startDate string) (item *ReadReportType, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `SELECT 'rddp' AS report_type,MIN(a.create_time) AS create_time,b.title,b.stage FROM report_view_record AS a
			INNER JOIN  report AS b ON a.report_id=b.id
			WHERE a.user_id=? AND a.create_time>=? `
	err = o.Raw(sql, uid, startDate).QueryRow(&item)
	return
}

func GetWeekMinReportType(uid int, startDate string) (item *ReadReportType, err error) {
	o := orm.NewOrm()
	sql := `SELECT b.type AS report_type,MIN(a.created_time) AS create_time,b.research_report_name AS title,b.periods AS stage FROM user_view_history AS a
			INNER JOIN  research_report AS b ON a.research_report_id=b.research_report_id
			WHERE a.user_id=? AND a.created_time>=? `
	err = o.Raw(sql, uid, startDate).QueryRow(&item)
	return
}

func GetFirstReportInfo(uid int, startDate string) (firstReadReportType, firstReadReportTitle string, err error) {
	rddpFlag := true
	weekFlag := true
	rddpMinReportItem, err := GetRddpMinReportType(uid, startDate)
	if err != nil {
		if err.Error() == utils.ErrNoRow() {
			rddpFlag = false
		} else {
			return
		}
	}
	if rddpMinReportItem == nil {
		rddpFlag = false
	}
	weekMinReportItem, err := GetWeekMinReportType(uid, startDate)
	if err != nil {
		if err.Error() == utils.ErrNoRow() {
			weekFlag = false
		} else {
			return
		}
	}
	if weekMinReportItem == nil {
		weekFlag = false
	}

	if rddpFlag && weekFlag {
		if rddpMinReportItem.CreateTime.After(weekMinReportItem.CreateTime) {
			firstReadReportType = rddpMinReportItem.ReportType
			firstReadReportTitle = rddpMinReportItem.Title
		} else {
			firstReadReportType = weekMinReportItem.ReportType
			firstReadReportTitle = weekMinReportItem.Title
		}
	} else {
		if rddpFlag == false && weekFlag {
			firstReadReportType = weekMinReportItem.ReportType
			firstReadReportTitle = weekMinReportItem.Title
		}
		if rddpFlag && weekFlag == false {
			firstReadReportType = rddpMinReportItem.ReportType
			firstReadReportTitle = rddpMinReportItem.Title
		}
	}
	return
}

type Listen struct {
	Count            int
	VideoPlaySeconds float64
}

func GetRddpListen(uid int, startDate string) (item *Listen, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := ` SELECT COUNT(1) AS count,SUM(b.video_play_seconds) AS video_play_seconds FROM  report_audio_record AS a
			 INNER JOIN report AS b ON a.report_id=b.id
			 WHERE user_id=? AND a.create_time>=? LIMIT 1`
	err = o.Raw(sql, uid, startDate).QueryRow(&item)
	return
}

func GetWeekListen(uid int, startDate string) (item *Listen, err error) {
	o := orm.NewOrm()
	sql := `SELECT COUNT(1) AS count,SUM(b.video_play_seconds) AS video_play_seconds FROM voice_record AS a
			INNER JOIN teleconference AS b ON a.teleconference_id=b.teleconference_id
			WHERE a.uid=? AND a.create_time>=? LIMIT 1`
	err = o.Raw(sql, uid, startDate).QueryRow(&item)
	return
}

func GetListenInfo(uid int, startDate string) (count int, videoPlaySeconds float64, err error) {
	rddpFlag := true
	weekFlag := true
	rddpListenItem, err := GetRddpListen(uid, startDate)
	if err != nil {
		if err.Error() == utils.ErrNoRow() {
			rddpFlag = false
		} else {
			return
		}
	}
	if rddpListenItem == nil {
		rddpFlag = false
	}
	weekListenItem, err := GetWeekListen(uid, startDate)
	if err != nil {
		if err.Error() == utils.ErrNoRow() {
			weekFlag = false
		} else {
			return
		}
	}
	if weekListenItem == nil {
		weekFlag = false
	}

	if rddpFlag && weekFlag {
		count = rddpListenItem.Count + weekListenItem.Count
		videoPlaySeconds = rddpListenItem.VideoPlaySeconds + weekListenItem.VideoPlaySeconds
	} else {
		if rddpFlag == false && weekFlag {
			count = weekListenItem.Count
			videoPlaySeconds = weekListenItem.VideoPlaySeconds
		}
		if rddpFlag && weekFlag == false {
			count = rddpListenItem.Count
			videoPlaySeconds = rddpListenItem.VideoPlaySeconds
		}
	}
	return
}

type LatestReadReport struct {
	LatestTime string
	CreateTime time.Time
}

func GetRddpLatestReadReport(uid int, startDate string) (item *LatestReadReport, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := ` SELECT DATE_FORMAT(create_time,'%H:%i:%s') AS latest_time,create_time FROM report_view_record
			WHERE user_id=? AND create_time>=? AND (DATE_FORMAT(create_time,'%H:%i:%s')<='06:00:00' OR DATE_FORMAT(create_time,'%H:%i:%s')>'06:00:00')
			ORDER BY DATE_FORMAT(create_time,'%H:%i:%s')  DESC
			LIMIT 1 `
	err = o.Raw(sql, uid, startDate).QueryRow(&item)
	return
}

func GetWeekLatestReadReportCount(uid int, startDate string) (item *LatestReadReport, err error) {
	o := orm.NewOrm()
	sql := ` SELECT DATE_FORMAT(created_time,'%H:%i:%s') AS latest_time,created_time FROM user_view_history 
			WHERE user_id=? AND created_time>=? AND (DATE_FORMAT(created_time,'%H:%i:%s')<='06:00:00' OR DATE_FORMAT(created_time,'%H:%i:%s')>'06:00:00')
			ORDER BY DATE_FORMAT(created_time,'%H:%i:%s')  DESC
			LIMIT 1 `
	err = o.Raw(sql, uid, startDate).QueryRow(&item)
	return
}

func GetLatestReadReportInfo(uid int, startDate string) (latestTime string, latestCreateTime time.Time, err error) {
	rddpFlag := true
	weekFlag := true
	rddpReadReportItem, err := GetRddpLatestReadReport(uid, startDate)
	if err != nil {
		if err.Error() == utils.ErrNoRow() {
			rddpFlag = false
		} else {
			return
		}
	}
	if rddpReadReportItem == nil {
		rddpFlag = false
	}
	weekReadReportItem, err := GetWeekLatestReadReportCount(uid, startDate)
	if err != nil {
		if err.Error() == utils.ErrNoRow() {
			weekFlag = false
		} else {
			return
		}
	}
	if weekReadReportItem == nil {
		weekFlag = false
	}

	if rddpFlag && weekFlag {
		if rddpReadReportItem.CreateTime.After(weekReadReportItem.CreateTime) {
			latestTime = rddpReadReportItem.LatestTime
			latestCreateTime = rddpReadReportItem.CreateTime
		} else {
			latestTime = weekReadReportItem.LatestTime
			latestCreateTime = weekReadReportItem.CreateTime
		}
	} else {
		if rddpFlag == false && weekFlag {
			latestTime = weekReadReportItem.LatestTime
			latestCreateTime = weekReadReportItem.CreateTime
		}
		if rddpFlag && weekFlag == false {
			latestTime = rddpReadReportItem.LatestTime
			latestCreateTime = rddpReadReportItem.CreateTime
		}
	}
	fmt.Println("line 244")
	fmt.Println(latestTime, latestCreateTime)
	return
}

type MaxReadReportCount struct {
	Count      int
	CreateDate time.Time
}

func GetRddpMaxReadReportCount(uid int, startDate string) (item *MaxReadReportCount, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := ` SELECT DATE(create_time)AS create_date,COUNT(1) AS count FROM report_view_record AS a
				WHERE a.user_id=?
                AND create_time>=?
				GROUP BY DATE(create_time)
				ORDER BY count DESC
				LIMIT 1 `
	err = o.Raw(sql, uid, startDate).QueryRow(&item)
	return
}

func GetWeekMaxReadReportCount(uid int, startDate string) (item *MaxReadReportCount, err error) {
	o := orm.NewOrm()
	sql := ` SELECT DATE(a.created_time)AS create_date,COUNT(1) AS count  FROM user_view_history AS a
			WHERE a.user_id=? AND created_time>=?
			GROUP BY DATE(created_time)
			ORDER BY count DESC
			LIMIT 1 `
	err = o.Raw(sql, uid, startDate).QueryRow(&item)
	return
}

func GetMaxReadReportInfo(uid int, startDate string) (count int, createDate time.Time, err error) {
	rddpFlag := true
	weekFlag := true
	rddpReadReportItem, err := GetRddpMaxReadReportCount(uid, startDate)
	if err != nil {
		if err.Error() == utils.ErrNoRow() {
			rddpFlag = false
		} else {
			return
		}
	}
	if rddpReadReportItem == nil {
		rddpFlag = false
	}
	weekReadReportItem, err := GetWeekMaxReadReportCount(uid, startDate)
	if err != nil {
		if err.Error() == utils.ErrNoRow() {
			weekFlag = false
		} else {
			return
		}
	}
	if weekReadReportItem == nil {
		weekFlag = false
	}

	if rddpFlag && weekFlag {
		if rddpReadReportItem.Count > weekReadReportItem.Count {
			count = rddpReadReportItem.Count
			createDate = rddpReadReportItem.CreateDate
		} else {
			count = weekReadReportItem.Count
			createDate = weekReadReportItem.CreateDate
		}
	} else {
		if rddpFlag == false && weekFlag {
			count = weekReadReportItem.Count
			createDate = weekReadReportItem.CreateDate
		}
		if rddpFlag && weekFlag == false {
			count = rddpReadReportItem.Count
			createDate = rddpReadReportItem.CreateDate
		}
	}
	return
}

func GetRddpReadReportCountByDate(uid int, readDate string) (count int, video_play_seconds float64, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `  SELECT COUNT(1) AS count,SUM(b.video_play_seconds) AS video_play_seconds FROM  report_view_record AS a
			LEFT JOIN report AS b ON a.report_id=b.id
			 WHERE a.user_id=? AND DATE(a.create_time)=? LIMIT 1`
	err = o.Raw(sql, uid, readDate).QueryRow(&count, &video_play_seconds)
	return
}

func GetWeekReadReportCountByDate(uid int, readDate string) (count int, err error) {
	o := orm.NewOrm()
	sql := ` SELECT COUNT(1) AS count FROM user_view_history  AS a
			WHERE a.user_id=? AND DATE(a.created_time)=?
			LIMIT 1 `
	err = o.Raw(sql, uid, readDate).QueryRow(&count)
	return
}

type MaxOpenReport struct {
	OpenReportCount int
	ClassifyName    string
}

func GetRddpOpenReadReport(uid int, startDate string) (item *MaxOpenReport, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := ` SELECT COUNT(b.classify_id_second) AS open_report_count,b.classify_name_second AS classify_name 
            FROM report_view_record AS a
			INNER JOIN report AS b ON a.report_id=b.id
			WHERE a.user_id=? AND a.create_time>=?
			GROUP BY b.classify_id_second
			ORDER BY COUNT(b.classify_id_second) DESC
			LIMIT 1 `
	err = o.Raw(sql, uid, startDate).QueryRow(&item)
	return
}

func GetWeekOpenReadReportCount(uid int, startDate string) (item *MaxOpenReport, err error) {
	o := orm.NewOrm()
	sql := ` SELECT COUNT(b.research_report_name) AS open_report_count,b.research_report_name AS classify_name 
            FROM user_view_history AS a 
			LEFT JOIN research_report AS b ON a.research_report_id=b.research_report_id
			WHERE user_id=? AND a.created_time=?
			GROUP BY b.research_report_name
			ORDER BY COUNT(b.research_report_name) DESC
			LIMIT 1  `
	err = o.Raw(sql, uid, startDate).QueryRow(&item)
	return
}

func GetOpenReadReportInfo(uid int, startDate string) (maxOpenReportClassify string, maxOpenReportCount int, err error) {
	rddpFlag := true
	weekFlag := true
	rddpReadReportItem, err := GetRddpOpenReadReport(uid, startDate)
	if err != nil {
		if err.Error() == utils.ErrNoRow() {
			rddpFlag = false
		} else {
			return
		}
	}
	if rddpReadReportItem == nil {
		rddpFlag = false
	}
	weekReadReportItem, err := GetWeekOpenReadReportCount(uid, startDate)
	if err != nil {
		if err.Error() == utils.ErrNoRow() {
			weekFlag = false
		} else {
			return
		}
	}
	if weekReadReportItem == nil {
		weekFlag = false
	}

	if rddpFlag && weekFlag {
		if rddpReadReportItem.OpenReportCount > weekReadReportItem.OpenReportCount {
			maxOpenReportCount = rddpReadReportItem.OpenReportCount
			maxOpenReportClassify = rddpReadReportItem.ClassifyName
		} else {
			maxOpenReportCount = weekReadReportItem.OpenReportCount
			maxOpenReportClassify = weekReadReportItem.ClassifyName
		}
	} else {
		if rddpFlag == false && weekFlag {
			maxOpenReportCount = weekReadReportItem.OpenReportCount
			maxOpenReportClassify = weekReadReportItem.ClassifyName
		}
		if rddpFlag && weekFlag == false {
			maxOpenReportCount = rddpReadReportItem.OpenReportCount
			maxOpenReportClassify = rddpReadReportItem.ClassifyName
		}
	}
	return
}

func GetRddpTotalReadDuration(uid int, startDate string) (video_play_seconds float64, total int, err error) {
	sql := `SELECT SUM(b.video_play_seconds) AS video_play_seconds ,COUNT(1) AS total FROM  report_view_record AS a
          INNER JOIN report AS b ON a.report_id=b.id
          WHERE a.user_id=? AND  a.create_time>=? `
	o := orm.NewOrmUsingDB("rddp")
	err = o.Raw(sql, uid, startDate).QueryRow(&video_play_seconds, &total)
	return
}

func GetWeekTotalRead(uid int, startDate string) (report_count int, err error) {
	o := orm.NewOrm()
	sql := ` SELECT SUM(1) AS report_count
			FROM user_view_history AS a 
			INNER JOIN research_report AS b ON a.research_report_id=b.research_report_id
			WHERE a.user_id=? AND a.created_time>=? `
	err = o.Raw(sql, uid, startDate).QueryRow(&report_count)
	return
}

func GetWeekTotalReadByType(uid int, reportType string, startDate string) (report_count int, err error) {
	o := orm.NewOrm()
	sql := `SELECT count(DISTINCT a.research_report_id) AS report_count
            FROM user_view_history AS a 
			INNER JOIN research_report AS b ON a.research_report_id=b.research_report_id
			WHERE a.user_id=? AND a.created_time>=?  AND b.type=?`
	err = o.Raw(sql, uid, startDate, reportType).QueryRow(&report_count)
	return
}

func GetRddpLearnDay(uid int, startDate, endDate string) (learn_day int, err error) {
	//, MIN(login_day) start_date,MAX(login_day) end_date
	o := orm.NewOrmUsingDB("rddp")
	sql := ` SELECT MAX(days) AS learn_day
  FROM (SELECT user_id,
               @cont_day :=
               (CASE
                 WHEN (@last_uid = user_id AND DATEDIFF(login_dt, @last_dt)=1) THEN
                  (@cont_day + 1)
                 WHEN (@last_uid = user_id AND DATEDIFF(login_dt, @last_dt)<1) THEN
                  (@cont_day + 0)
                 ELSE
                  1
               END) AS days,
               (@cont_ix := (@cont_ix + IF(@cont_day = 1, 1, 0))) AS cont_ix,
               @last_uid := user_id,
               @last_dt := login_dt login_day
          FROM (SELECT user_id, DATE(create_time) AS login_dt
                  FROM  report_view_record
                  WHERE 
                  user_id=?
                  AND create_time>=?
                 ORDER BY user_id, create_time) AS t,
               (SELECT @last_uid := '',
                       @last_dt  := '',
                       @cont_ix  := 0,
                       @cont_day := 0) AS t1) AS t2
 GROUP BY user_id, cont_ix
 ORDER BY MAX(days) DESC
 LIMIT 1 `
	err = o.Raw(sql, uid, startDate).QueryRow(&learn_day)
	return
}

func GetWeekpLearnDay(uid int, startDate string) (learn_day int, err error) {
	//MIN(login_day) start_date,MAX(login_day) end_date
	o := orm.NewOrm()
	sql := ` SELECT MAX(days) lianxu_days
			  FROM (SELECT user_id,
						   @cont_day :=
						   (CASE
							 WHEN (@last_uid = user_id AND DATEDIFF(login_dt, @last_dt)=1) THEN
							  (@cont_day + 1)
							 WHEN (@last_uid = user_id AND DATEDIFF(login_dt, @last_dt)<1) THEN
							  (@cont_day + 0)
							 ELSE
							  1
						   END) AS days,
						   (@cont_ix := (@cont_ix + IF(@cont_day = 1, 1, 0))) AS cont_ix,
						   @last_uid := user_id,
						   @last_dt := login_dt login_day
					  FROM (SELECT user_id, DATE(created_time) AS login_dt
							  FROM user_view_history
							  WHERE 
							  user_id=?
							  AND created_time>=?
							 ORDER BY user_id, created_time) AS t,
						   (SELECT @last_uid := '',
								   @last_dt  := '',
								   @cont_ix  := 0,
								   @cont_day := 0) AS t1) AS t2
			 GROUP BY user_id, cont_ix
			 ORDER BY MAX(days) DESC
			 LIMIT 1 `
	err = o.Raw(sql, uid, startDate).QueryRow(&learn_day)
	return
}

func GetRddpTotalReadByType(uid int, startDate string) (report_count int, err error) {
	o := orm.NewOrmUsingDB("rddp")
	sql := `SELECT count(DISTINCT a.report_id) AS report_count
            FROM report_view_record AS a 
			INNER JOIN report AS b ON a.report_id=b.id
			WHERE a.user_id=? AND a.create_time>=? `
	err = o.Raw(sql, uid, startDate).QueryRow(&report_count)
	return
}

func GetAnnualReportCount(userId int) (count int, err error) {
	o := orm.NewOrm()
	sql := `SELECT COUNT(1) AS count FROM annual_report WHERE user_id=? `
	err = o.Raw(sql, userId).QueryRow(&count)
	return
}