crawler/model/movieWeekly.go

package model

import (
	"crawler/config"
	"crawler/service"
	"database/sql"
	"encoding/json"
	"errors"
	"fmt"
	"strconv"
	"strings"
	"time"
)

type MovieWeeklyModel struct {
	SearchWeeklyBoxOfficeParams
	SearchWeeklyBoxOfficeList
	WeeklyBoxOfficeInfo
}

// 일별 박스오피스 검색 변수
type SearchWeeklyBoxOfficeParams struct {
	Key          string `form:"key" url:"key" binding:"required"`
	TargetDt     string `form:"targetDt" url:"targetDt" binding:"required"`
	WeekGb       string `form:"weekGb" url:"weekGb,omitempty"`
	ItemPerPage  string `form:"itemPerPage" url:"itemPerPage,omitempty"`
	MultiMovieYn string `form:"multiMovieYn" url:"multiMovieYn,omitempty"`
	RepNationCd  string `form:"repNationCd" url:"repNationCd,omitempty"`
	WideAreaCd   string `form:"wideAreaCd" url:"wideAreaCd,omitempty"`
}

// 일별 박스오피스 응답 변수
type SearchWeeklyBoxOfficeList struct {
	BoxOfficeResult struct {
		BoxofficeType       string                `json:"boxofficeType"`
		ShowRange           string                `json:"showRange"`
		YearWeekTime        string                `json:"yearWeekTime"`
		WeeklyBoxOfficeList []WeeklyBoxOfficeInfo `json:"weeklyBoxOfficeList"`
	} `json:"boxOfficeResult"`
}

type WeeklyBoxOfficeInfo struct {
	Rnum          string `json:"rnum"`
	Rank          string `json:"rank"`
	RankInten     string `json:"rankInten"`
	RankOldAndNew string `json:"rankOldAndNew"`
	MovieCd       string `json:"movieCd"`
	MovieNm       string `json:"movieNm"`
	OpenDt        string `json:"openDt"`
	SalesAmt      string `json:"salesAmt"`
	SalesShare    string `json:"salesShare"`
	SalesInten    string `json:"salesInten"`
	SalesChange   string `json:"salesChange"`
	SalesAcc      string `json:"salesAcc"`
	AudiCnt       string `json:"audiCnt"`
	AudiInten     string `json:"audiInten"`
	AudiChange    string `json:"audiChange"`
	AudiAcc       string `json:"audiAcc"`
	ScrnCnt       string `json:"scrnCnt"`
	ShowCnt       string `json:"showCnt"`
}

type MovieWeeklyTable struct {
	WeeklyID      int
	BoxofficeType *string
	ShowRange     *string
	YearWeekTime  *string
	Rnum          *int
	Rank          *int
	RankInten     *int
	RankOldAndNew *string
	MovieCd       string
	MovieNm       *string
	OpenDt        *string
	SalesAmt      *int
	SalesShare    *float64
	SalesInten    *int
	SalesChange   *float64
	SalesAcc      *int
	AudiCnt       *int
	AudiInten     *int
	AudiChange    *float64
	AudiAcc       *int
	ScrnCnt       *int
	ShowCnt       *int
	UpdatedAt     *string
	CreatedAt     string

	Detail *MovieDetailTable
}

func (this *MovieWeeklyModel) Total() int {
	var (
		db    = service.DB_MOVIEW
		conn  = db.SQLDB
		query = "SELECT COUNT(*) FROM tb_movie_weekly;"
		total = 0
	)

	err := conn.QueryRow(query).Scan(&total)
	if err != nil {
		db.SetErrorLog(err, query)
		return total
	}

	db.SetGeneralLog(config.GL_ACTION_SELECT, query, "select total movie weekly")
	return total
}

func (this *MovieWeeklyModel) List(ids string) ([]MovieWeeklyTable, error) {
	var (
		db    = service.DB_MOVIEW
		conn  = db.SQLDB
		query = fmt.Sprintf("SELECT * FROM tb_movie_weekly WHERE id IN (%s);", ids)
		list  = make([]MovieWeeklyTable, 0)
	)

	rows, err := conn.Query(query)
	if err != nil && err != sql.ErrNoRows {
		db.SetErrorLog(err, query)
		return list, err
	}
	defer rows.Close()

	for rows.Next() {
		var row MovieWeeklyTable
		if err = rows.Scan(
			&row.WeeklyID, &row.BoxofficeType, &row.ShowRange, &row.YearWeekTime, &row.Rnum, &row.Rank, &row.RankInten,
			&row.RankOldAndNew, &row.MovieCd, &row.MovieNm, &row.OpenDt, &row.SalesAmt,
			&row.SalesShare, &row.SalesInten, &row.SalesChange, &row.SalesAcc, &row.AudiCnt,
			&row.AudiInten, &row.AudiChange, &row.AudiAcc, &row.ScrnCnt, &row.ShowCnt,
			&row.UpdatedAt, &row.CreatedAt); err != nil {
			return list, err
		}
		list = append(list, row)
	}

	if err = rows.Err(); err != nil {
		return list, err
	}

	db.SetGeneralLog(config.GL_ACTION_SELECT, query, "select movie week")
	return list, nil
}

func (this *MovieWeeklyModel) Insert(list SearchWeeklyBoxOfficeList) error {
	if len(list.BoxOfficeResult.WeeklyBoxOfficeList) <= 0 {
		return nil
	}

	var (
		db    = service.DB_MOVIEW
		conn  = db.SQLDB
		query = `
			INSERT INTO tb_movie_weekly (
				box_office_type, show_range, year_week_time, rnum, ` + "`rank`" + `, rank_Inten, 
			    rank_old_and_new, movie_cd, movie_nm, open_dt, sales_amt, 
			    sales_share, sales_Inten, sales_change, sales_acc,
			    audi_cnt, audi_Inten, audi_change, audi_acc,
			    scrn_cnt, show_cnt, updated_at, created_at
			)
			VALUES 
		`
		vals = []interface{}{}
		dup  string
	)

	for _, row := range list.BoxOfficeResult.WeeklyBoxOfficeList {
		query += `(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NULL, NOW()),`

		data := this.DataFilter(list.BoxOfficeResult.BoxofficeType, list.BoxOfficeResult.ShowRange, list.BoxOfficeResult.YearWeekTime, row)

		vals = append(vals,
			data.BoxofficeType, data.ShowRange, data.YearWeekTime, data.Rnum, data.Rank, data.RankInten,
			data.RankOldAndNew, data.MovieCd, data.MovieNm, data.OpenDt, data.SalesAmt,
			data.SalesShare, data.SalesInten, data.SalesChange, data.SalesAcc,
			data.AudiCnt, data.AudiInten, data.AudiChange, data.AudiAcc,
			data.ScrnCnt, data.ShowCnt)

		dup += `box_office_type = VALUES(box_office_type), show_range = VALUES(show_range), year_week_time = VALUES(year_week_time), 
				rnum = VALUES(rnum), ` + "`rank`" + ` = VALUES(` + "`rank`" + `), rank_Inten = VALUES(rank_Inten), rank_old_and_new = VALUES(rank_old_and_new), 
				movie_cd = VALUES(movie_cd), movie_nm = VALUES(movie_nm), open_dt = VALUES(open_dt),
				sales_amt = VALUES(sales_amt), sales_share = VALUES(sales_share), sales_Inten = VALUES(sales_Inten),
				sales_change = VALUES(sales_change), sales_acc = VALUES(sales_acc), audi_cnt = VALUES(audi_cnt),
				audi_Inten = VALUES(audi_Inten), audi_change = VALUES(audi_change), audi_acc = VALUES(audi_acc),
				scrn_cnt = VALUES(scrn_cnt), show_cnt = VALUES(show_cnt), updated_at = NOW(),`
	}

	query = query[0 : len(query)-1]
	dup = dup[0 : len(dup)-1]

	query += `ON DUPLICATE KEY UPDATE ` + dup

	stmt, err := conn.Prepare(query)
	if err != nil {
		db.SetErrorLog(err, query)
		return err
	}
	defer stmt.Close()

	_, err = stmt.Exec(vals...)
	if err != nil {
		db.SetErrorLog(err, query)
		return err
	}

	db.SetGeneralLog(config.GL_ACTION_WRITE, query, "insert movie week")
	return nil
}

func (this *MovieWeeklyModel) LastInsertIDs(movieCd []string, targetDt string) (string, error) {
	if movieCd == nil || targetDt == "" {
		return "", errors.New("날짜를 지정해주세요.")
	}

	var (
		db         = service.DB_MOVIEW
		conn       = db.SQLDB
		query      = fmt.Sprintf("SELECT id FROM tb_movie_weekly WHERE year_week_time = CONCAT(?, ?) AND movie_cd IN (%s);", strings.Join(movieCd, ", "))
		ids        = make([]string, 0)
		t, _       = time.Parse("20060102", targetDt)
		year, week = t.ISOWeek()
	)

	rows, err := conn.Query(query, year, fmt.Sprintf("%02d", week))
	if err != nil && err != sql.ErrNoRows {
		db.SetErrorLog(err, query)
		return "", err
	}

	for rows.Next() {
		var id string
		if err = rows.Scan(&id); err != nil {
			return "", err
		}
		ids = append(ids, id)
	}

	if err = rows.Err(); err != nil {
		return "", err
	}

	db.SetGeneralLog(config.GL_ACTION_SELECT, query, "select ids movie week")

	return strings.Join(ids, ","), nil
}

func (this *MovieWeeklyModel) DataFilter(boxofficeType, showRange, yearWeekTime string, row WeeklyBoxOfficeInfo) MovieWeeklyTable {
	var result MovieWeeklyTable
	result.MovieCd = row.MovieCd
	result.BoxofficeType = &boxofficeType
	result.ShowRange = &showRange
	result.YearWeekTime = &yearWeekTime

	if row.Rnum != "" {
		rnum, _ := strconv.Atoi(row.Rnum)
		result.Rnum = &rnum
	} else {
		result.Rnum = nil
	}

	if row.Rank != "" {
		rank, _ := strconv.Atoi(row.Rank)
		result.Rank = &rank
	} else {
		result.Rank = nil
	}

	if row.RankInten != "" {
		rankInten, _ := strconv.Atoi(row.RankInten)
		result.RankInten = &rankInten
	} else {
		result.RankInten = nil
	}

	if row.RankOldAndNew != "" {
		result.RankOldAndNew = &row.RankOldAndNew
	} else {
		result.RankOldAndNew = nil
	}

	if row.MovieNm != "" {
		result.MovieNm = &row.MovieNm
	} else {
		result.MovieNm = nil
	}

	if row.OpenDt != "" {
		result.OpenDt = &row.OpenDt
	} else {
		result.OpenDt = nil
	}

	if row.SalesAmt != "" {
		salesAmt, _ := strconv.Atoi(row.SalesAmt)
		result.SalesAmt = &salesAmt
	} else {
		result.SalesAmt = nil
	}

	if row.SalesShare != "" {
		salesShare, _ := strconv.ParseFloat(row.SalesShare, 8)
		result.SalesShare = &salesShare
	} else {
		result.SalesShare = nil
	}

	if row.SalesInten != "" {
		salesInten, _ := strconv.Atoi(row.SalesInten)
		result.SalesInten = &salesInten
	} else {
		result.SalesInten = nil
	}

	if row.SalesChange != "" {
		salesChange, _ := strconv.ParseFloat(row.SalesChange, 8)
		result.SalesChange = &salesChange
	} else {
		result.SalesChange = nil
	}

	if row.SalesAcc != "" {
		salesAcc, _ := strconv.Atoi(row.SalesAcc)
		result.SalesAcc = &salesAcc
	} else {
		result.SalesAcc = nil
	}

	if row.AudiCnt != "" {
		audiCnt, _ := strconv.Atoi(row.AudiCnt)
		result.AudiCnt = &audiCnt
	} else {
		result.AudiCnt = nil
	}

	if row.AudiInten != "" {
		audiInten, _ := strconv.Atoi(row.AudiInten)
		result.AudiInten = &audiInten
	} else {
		result.AudiInten = nil
	}

	if row.AudiChange != "" {
		audiChange, _ := strconv.ParseFloat(row.AudiChange, 8)
		result.AudiChange = &audiChange
	} else {
		result.AudiChange = nil
	}

	if row.AudiAcc != "" {
		audiAcc, _ := strconv.Atoi(row.AudiAcc)
		result.AudiAcc = &audiAcc
	} else {
		result.AudiAcc = nil
	}

	if row.ScrnCnt != "" {
		scrnCnt, _ := strconv.Atoi(row.ScrnCnt)
		result.ScrnCnt = &scrnCnt
	} else {
		result.ScrnCnt = nil
	}

	if row.ShowCnt != "" {
		showCnt, _ := strconv.Atoi(row.ShowCnt)
		result.ShowCnt = &showCnt
	} else {
		result.ShowCnt = nil
	}

	return result
}

func (this *MovieWeeklyModel) MakeParams(req SearchWeeklyBoxOfficeParams) (string, error) {
	params, err := json.Marshal(WeeklyBoxOfficeListParams{
		TargetDt:     req.TargetDt,
		WeekGb:       req.WeekGb,
		ItemPerPage:  req.ItemPerPage,
		MultiMovieYn: req.MultiMovieYn,
		RepNationCd:  req.RepNationCd,
		WideAreaCd:   req.WideAreaCd,
	})
	return string(params), err
}

func (this *MovieWeeklyModel) Info(dailyID int) (MovieWeeklyTable, error) {
	var (
		db    = service.DB_MOVIEW
		conn  = db.SQLDB
		query = "SELECT * FROM tb_movie_weekly WHERE id = ?;"
		info  MovieWeeklyTable
	)

	err := conn.QueryRow(query, dailyID).Scan(
		&info.WeeklyID, &info.BoxofficeType, &info.ShowRange, &info.YearWeekTime, &info.Rnum,
		&info.Rank, &info.RankInten, &info.RankOldAndNew,
		&info.MovieCd, &info.MovieNm, &info.OpenDt,
		&info.SalesAmt, &info.SalesShare, &info.SalesInten, &info.SalesChange, &info.SalesAcc,
		&info.AudiCnt, &info.AudiInten, &info.AudiChange, &info.AudiAcc,
		&info.ScrnCnt, &info.ShowCnt, &info.UpdatedAt, &info.CreatedAt,
	)

	if err != nil && err != sql.ErrNoRows {
		db.SetErrorLog(err, query)
		return info, err
	}

	db.SetGeneralLog(config.GL_ACTION_SELECT, query, "select movie weekly")
	return info, nil
}