crawler/model/movie.go

package model

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

type MovieListModel struct {
	SearchMovieListParams
	SearchMovieList
	MovieListInfo
	Director
	Company
	MovieListTable
}

// 영화 목록 검색 변수
type SearchMovieListParams struct {
	Key           string `form:"key" url:"key" binding:"required"`
	CurPage       int    `form:"curPage" url:"curPage"`
	ItemPerPage   int    `form:"itemPerPage" url:"itemPerPage,omitempty"`
	MovieNm       string `form:"movieNm" url:"movieNm,omitempty"`
	DirectorNm    string `form:"directorNm" url:"directorNm,omitempty"`
	OpenStartDt   int    `form:"openStartDt" url:"openStartDt,omitempty"`
	OpenEndDt     int    `form:"openEndDt" url:"openEndDt,omitempty"`
	PrdtStartYear int    `form:"prdtStartYear" url:"prdtStartYear,omitempty"`
	PrdtEndYear   int    `form:"prdtEndYear" url:"prdtEndYear,omitempty"`
	RepNationCd   string `form:"repNationCd" url:"repNationCd,omitempty"`
	MovieTypeCd   string `form:"movieTypeCd" url:"movieTypeCd,omitempty"`
}

type SearchMovieList struct {
	MovieListResult struct {
		TotCnt    int             `json:"totCnt"`
		Source    string          `json:"source"`
		MovieList []MovieListInfo `json:"movieList"`
	} `json:"movieListResult"`
}

type MovieListInfo struct {
	MovieCd     string     `json:"movieCd"`
	MovieNm     string     `json:"movieNm"`
	MovieNmEn   string     `json:"movieNmEn"`
	PrdtYear    string     `json:"prdtYear"`
	OpenDt      string     `json:"openDt"`
	TypeNm      string     `json:"typeNm"`
	PrdtStatNm  string     `json:"prdtStatNm"`
	NationAlt   string     `json:"nationAlt"`
	GenreAlt    string     `json:"genreAlt"`
	RepNationNm string     `json:"repNationNm"`
	RepGenreNm  string     `json:"repGenreNm"`
	Directors   []Director `json:"directors,omitempty"`
	Companys    []Company  `json:"companys,omitempty"`
}

type Director struct {
	PeopleNm string `json:"peopleNm,omitempty"`
}

type Company struct {
	CompanyCd string `json:"companyCd,omitempty"`
	CompanyNm string `json:"companyNm,omitempty"`
}

type MovieListTable struct {
	MovieID     int
	MovieCd     string
	MovieNm     *string
	MovieNmEn   *string
	PrdtYear    *int
	OpenDt      *int
	TypeNm      *string
	PrdtStatNm  *string
	NationAlt   *string
	GenreAlt    *string
	RepNationNm *string
	RepGenreNm  *string
	Directors   *string
	Companys    *string
	UpdatedAt   *string
	CreatedAt   string

	Detail *MovieDetailTable
}

func (this *MovieListModel) Total() int {
	var (
		db    = service.DB_MOVIEW
		conn  = db.SQLDB
		query = "SELECT COUNT(*) FROM tb_movie;"
		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")
	return total
}

func (this *MovieListModel) List(ids string) ([]MovieListTable, error) {
	var (
		db    = service.DB_MOVIEW
		conn  = db.SQLDB
		query = fmt.Sprintf("SELECT * FROM tb_movie WHERE id IN (%s) ORDER BY open_dt DESC;", ids)
		list  = make([]MovieListTable, 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 MovieListTable
		if err = rows.Scan(
			&row.MovieID, &row.MovieCd, &row.MovieNm, &row.MovieNmEn, &row.PrdtYear, &row.OpenDt, &row.TypeNm,
			&row.PrdtStatNm, &row.NationAlt, &row.GenreAlt, &row.RepNationNm, &row.RepGenreNm,
			&row.Directors, &row.Companys, &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 daily")
	return list, nil
}

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

	var (
		db    = service.DB_MOVIEW
		conn  = db.SQLDB
		query = `
			INSERT INTO tb_movie (
				movie_cd, movie_nm, movie_nm_en, prdt_year, open_dt, 
			    type_nm, prdt_stat_nm, nation_alt, genre_alt, rep_nation_nm, 
			    rep_genre_nm, directors, companys, 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.MovieNmEn, data.PrdtYear, data.OpenDt,
			data.TypeNm, data.PrdtStatNm, data.NationAlt, data.GenreAlt, data.RepNationNm,
			data.RepGenreNm, data.Directors, data.Companys)

		dup += `movie_cd = VALUES(movie_cd), movie_nm = VALUES(movie_nm), movie_nm_en = VALUES(movie_nm_en),
				prdt_year = VALUES(prdt_year), open_dt = VALUES(open_dt), type_nm = VALUES(type_nm), 
				prdt_stat_nm = VALUES(prdt_stat_nm), nation_alt = VALUES(nation_alt), genre_alt = VALUES(genre_alt),
				rep_nation_nm = VALUES(rep_nation_nm), rep_genre_nm = VALUES(rep_genre_nm), directors = VALUES(directors),
				companys = VALUES(companys), 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 list")
	return nil
}

func (this *MovieListModel) IsExists(movieCd string) bool {
	var (
		db     = service.DB_MOVIEW
		conn   = db.SQLDB
		query  = "SELECT IF(COUNT(*) <= 0, 0, 1) AS `exists` FROM tb_movie 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")
	return exists
}

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

	var (
		db    = service.DB_MOVIEW
		conn  = db.SQLDB
		query = `
			UPDATE tb_movie SET 
				movie_nm = ?, movie_nm_en = ?, prdt_year = ?, open_dt = ?, 
			    type_nm = ?, prdt_stat_nm = ?, nation_alt = ?, genre_alt = ?, 
			    rep_nation_nm = ?, rep_genre_nm = ?, directors = ?, companys = ?,
			    updated_at = NOW()
			WHERE movie_cd = ?
		`
	)

	for _, row := range list {
		data := this.DataFilter(row)
		_, err := conn.Exec(query,
			data.MovieNm, data.MovieNmEn, data.PrdtYear, data.OpenDt,
			data.TypeNm, data.PrdtStatNm, data.NationAlt, data.GenreAlt, data.RepNationNm,
			data.RepGenreNm, data.Directors, data.Companys, data.MovieCd)
		if err != nil {
			db.SetErrorLog(err, query)
			return err
		}
	}

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

func (this *MovieListModel) Replace(info MovieListInfo) error {
	if reflect.ValueOf(info).IsZero() {
		return nil
	}

	var (
		db    = service.DB_MOVIEW
		conn  = db.SQLDB
		query = `
			INSERT tb_movie SET 
				movie_cd = ?, movie_nm = ?, movie_nm_en = ?, prdt_year = ?, open_dt = ?, 
			    type_nm = ?, prdt_stat_nm = ?, nation_alt = ?, genre_alt = ?, 
			    rep_nation_nm = ?, rep_genre_nm = ?, directors = ?, companys = ?,
			    created_at = NOW()
			ON DUPLICATE KEY UPDATE 
			    movie_nm = VALUES(movie_nm), movie_nm_en = VALUES(movie_nm_en), prdt_year = VALUES(prdt_year), open_dt = VALUES(open_dt), 
			    type_nm = VALUES(type_nm), prdt_stat_nm = VALUES(prdt_stat_nm), nation_alt = VALUES(nation_alt), genre_alt = VALUES(genre_alt), 
			    rep_nation_nm = VALUES(rep_nation_nm), rep_genre_nm = VALUES(rep_genre_nm), directors = VALUES(directors), companys = VALUES(companys),
			    updated_at = NOW();
		`
	)

	data := this.DataFilter(info)
	_, err := conn.Exec(query,
		data.MovieCd, data.MovieNm, data.MovieNmEn, data.PrdtYear, data.OpenDt,
		data.TypeNm, data.PrdtStatNm, data.NationAlt, data.GenreAlt, data.RepNationNm,
		data.RepGenreNm, data.Directors, data.Companys)

	if err != nil {
		db.SetErrorLog(err, query)
		return err
	}

	db.SetGeneralLog(config.GL_ACTION_MODIFY, query, "replace movie list")
	return nil
}

// 영화 기본 정보에 없는 고유 코드 조회
func (this *MovieListModel) MovieInfoExcludeCodes() []string {
	var (
		db    = service.DB_MOVIEW
		conn  = db.SQLDB
		query = `SELECT movie_cd FROM tb_movie WHERE NOT EXISTS(SELECT movie_cd FROM tb_movie_info WHERE tb_movie.movie_cd = tb_movie_info.movie_cd);`
		code  []string
	)

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

	for rows.Next() {
		var cd string
		if err := rows.Scan(&cd); err != nil {
			log.Fatal(err)
		}
		code = append(code, cd)
	}

	db.SetGeneralLog(config.GL_ACTION_SELECT, query, "select movie_cd list")
	return code
}

// 영화 상세 정보에 없는 고유 코드 조회
func (this *MovieListModel) MovieDetailExcludeCodes() []string {
	var (
		db   = service.DB_MOVIEW
		conn = db.SQLDB
		//query = `SELECT movie_cd FROM tb_movie WHERE NOT EXISTS(SELECT movie_cd FROM tb_movie_detail WHERE tb_movie_detail.movie_cd = tb_movie.movie_cd);`
		//query = `SELECT movie_cd FROM tb_movie ORDER BY movie_id ASC`
		query = `SELECT m.movie_cd
				 FROM tb_movie AS m
				 JOIN tb_movie_detail AS d ON d.movie_cd = m.movie_cd 
				 WHERE (d.main_img IS NULL OR d.main_img = '') OR (d.thumb_img IS NULL OR d.thumb_img = '');
				`
		code []string
	)

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

	for rows.Next() {
		var cd string
		if err := rows.Scan(&cd); err != nil {
			log.Fatal(err)
		}
		code = append(code, cd)
	}

	db.SetGeneralLog(config.GL_ACTION_SELECT, query, "select movie_cd list")

	return code
}

func (this *MovieListModel) DataFilter(row MovieListInfo) MovieListTable {
	var result MovieListTable
	result.MovieCd = row.MovieCd

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

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

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

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

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

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

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

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

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

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

	if len(row.Directors) > 0 {
		directors, _ := json.Marshal(row.Directors)
		s := string(directors)
		result.Directors = &s
	} else {
		result.Directors = nil
	}

	if len(row.Companys) > 0 {
		companys, _ := json.Marshal(row.Companys)
		s := string(companys)
		result.Companys = &s
	} else {
		result.Companys = nil
	}

	return result
}

func (this *MovieListModel) MakeParams(req SearchMovieListParams) (string, error) {
	params, err := json.Marshal(MovieListParams{
		CurPage:       req.CurPage,
		ItemPerPage:   req.ItemPerPage,
		MovieNm:       req.MovieNm,
		DirectorNm:    req.DirectorNm,
		OpenStartDt:   req.OpenStartDt,
		OpenEndDt:     req.OpenEndDt,
		PrdtStartYear: req.PrdtStartYear,
		PrdtEndYear:   req.PrdtEndYear,
		RepNationCd:   req.RepNationCd,
		MovieTypeCd:   req.MovieTypeCd,
	})
	return string(params), err
}

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

	var (
		db    = service.DB_MOVIEW
		conn  = db.SQLDB
		query = fmt.Sprintf("SELECT id FROM tb_movie WHERE movie_cd IN(%s);",
			strings.Join(strings.Split(strings.Repeat("?", len(movieCDs)), ""), ", "))
		params = make([]interface{}, 0)
		ids    = make([]string, 0)
	)

	for _, movieCd := range movieCDs {
		params = append(params, movieCd)
	}

	rows, err := conn.Query(query, params...)

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

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