crawler/model/movieStats.go

package model

import (
	"crawler/config"
	"crawler/service"
	"net/url"
	"regexp"
	"strconv"
)

type MovieStatsModel struct {
	SearchBoxOfficeParams
	SearchBoxOfficeList
	BoxOfficeInfo
	MovieStatsTable
}

// 박스오피스 검색 변수
type SearchBoxOfficeParams struct {
	ServiceKey string `form:"serviceKey" url:"serviceKey" binding:"required"`
	NumOfRows  int    `form:"numOfRows" url:"numOfRows,omitempty"`
	PageNo     int    `form:"pageNo" url:"pageNo,omitempty"`
}

// 박스오피스 응답 변수
type SearchBoxOfficeList struct {
	Response struct {
		Header struct {
			ResultCode string `json:"resultCode"`
			ResultMsg  string `json:"resultMsg"`
		} `json:"header"`
		Body struct {
			Items struct {
				Item []BoxOfficeInfo `json:"item"`
			} `json:"items"`
			NumOfRows  string `json:"numOfRows"`
			PageNo     string `json:"pageNo"`
			TotalCount string `json:"totalCount"`
		} `json:"body"`
	} `json:"response"`
}

type BoxOfficeInfo struct {
	Title               string `json:"title"`
	AlternativeTitle    string `json:"alternativeTitle"`
	Creator             string `json:"creator"`
	RegDate             string `json:"regDate"`
	CollectionDb        string `json:"collectionDb"`
	SubjectCategory     string `json:"subjectCategory"`
	SubjectKeyword      string `json:"subjectKeyword"`
	Extent              string `json:"extent"`
	Description         string `json:"description"`
	SpatialCoverage     string `json:"spatialCoverage"`
	Temporal            string `json:"temporal"`
	Person              string `json:"person"`
	Language            string `json:"language"`
	SourceTitle         string `json:"sourceTitle"`
	ReferenceIdentifier string `json:"referenceIdentifier"`
	Rights              string `json:"rights"`
	CopyrightOthers     string `json:"copyrightOthers"`
	Url                 string `json:"url"`
	Contributor         string `json:"contributor"`
}

type MovieStatsTable struct {
	StatsID   int
	MovieCd   *string
	MovieNm   *string
	ShowDt    *int
	SaleAcc   int
	AudiAcc   int
	ScrnCnt   int
	ShowCnt   int
	RegDate   *string
	CreatedAt string
}

func (this *MovieStatsModel) Insert(list []BoxOfficeInfo) error {
	if len(list) <= 0 {
		return nil
	}

	var (
		db    = service.DB_MOVIEW
		conn  = db.SQLDB
		query = `
			INSERT INTO tb_movie_stats (
				movie_cd, movie_nm, show_dt, sale_acc, audi_acc, scrn_cnt, show_cnt, reg_date, updated_at, created_at
			)
			VALUES 
		`
		vals = []interface{}{}
		dup  string
	)

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

		data := this.DataFilter(row)

		vals = append(vals,
			data.MovieCd, data.MovieNm, data.ShowDt, data.SaleAcc, data.AudiAcc, data.ScrnCnt, data.ShowCnt, data.RegDate)

		dup += `movie_cd = VALUES(movie_cd), movie_nm = VALUES(movie_nm), show_dt = VALUES(show_dt), 
				sale_acc = VALUES(sale_acc), audi_acc = VALUES(audi_acc), scrn_cnt = VALUES(scrn_cnt),  
				show_cnt = VALUES(show_cnt), reg_date = VALUES(reg_date), updated_at = NOW(),`
	}

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

	query += ` ON DUPLICATE KEY UPDATE ` + dup

	_, _ = conn.Exec("LOCK TABLE tb_movie_stats READ;")

	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
	}

	_, _ = conn.Exec("UNLOCK TABLES;")

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

func (this *MovieStatsModel) IsExists(movieCd string) bool {
	var (
		db     = service.DB_MOVIEW
		conn   = db.SQLDB
		query  = "SELECT IF(COUNT(*) <= 0, 0, 1) AS `exists` FROM tb_movie_stats WHERE movie_cd = ?;"
		exists = false
	)

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

	db.SetGeneralLog(config.GL_ACTION_SELECT, query, "select exists movie stats")
	return exists
}

func (this *MovieStatsModel) Update(list []BoxOfficeInfo) error {
	if len(list) <= 0 {
		return nil
	}

	var (
		db    = service.DB_MOVIEW
		conn  = db.SQLDB
		query = `
			UPDATE tb_movie_stats SET 
				movie_cd = ?, movie_nm = ?, show_dt = ?, sale_acc = ?, audi_acc = ?, 
				scrn_cnt = ?, show_cnt = ?, reg_date = ?, updated_at = NOW()
			WHERE movie_cd = ?;
		`
	)

	for _, row := range list {
		data := this.DataFilter(row)
		_, err := conn.Exec(query,
			data.MovieCd, data.MovieNm, data.ShowDt, data.SaleAcc, data.AudiAcc, data.ScrnCnt, data.ShowCnt, data.RegDate, data.MovieCd)
		if err != nil {
			db.SetErrorLog(err, query)
			return err
		}
	}

	db.SetGeneralLog(config.GL_ACTION_MODIFY, query, "update movie stats")
	return nil
}

func (this *MovieStatsModel) DataFilter(row BoxOfficeInfo) MovieStatsTable {
	var result MovieStatsTable

	query, err := url.ParseQuery(row.Url)
	if err != nil {
		return result
	}

	var (
		movieCd = query.Get("dtCd")
		showDt  = query.Get("showdt")
	)

	result.MovieCd = &movieCd

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

	if showDt != "" {
		s, _ := strconv.Atoi(query.Get("showdt"))
		result.ShowDt = &s
	} else {
		result.ShowDt = nil
	}

	// 매출액, 관객수, 스크린수, 상영횟수
	re := regexp.MustCompile("[0-9]+")
	stats := re.FindAllString(row.Description, -1)

	if stats[0] != "" {
		s, _ := strconv.Atoi(stats[0])
		result.SaleAcc = s
	}
	if stats[1] != "" {
		s, _ := strconv.Atoi(stats[1])
		result.AudiAcc = s
	}
	if stats[2] != "" {
		s, _ := strconv.Atoi(stats[2])
		result.ScrnCnt = s
	}
	if stats[3] != "" {
		s, _ := strconv.Atoi(stats[3])
		result.ShowCnt = s
	}

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

	return result
}