<repo_name>latonaio/site-controller-data-update-to-mysql<file_sep>/README.md
# site-controller-data-update-to-mysql
site-controller-data-update-to-mysqlは、エッジ端末からWindows端末のディレクトリを監視し、そのディレクトリに新しいサイトコントローラー連携ファイル（CSV）が作成された場合に、その情報をkanbanに渡す、エッジ端末上で動くマイクロサービスです。

## サイトコントローラーとは
サイトコントローラーとは、主に宿泊業向けに、予約情報をまとめて管理運用するサービスです。   
サイトコントローラーは、サービスオーナー各社により提供されております。   

## 概要
site-controller-data-update-to-mysqlは、 一定時間おきにWindows端末ディレクトリを走査し、新しいファイルが作成されたら、以下の処理を行います。   
監視するディレクトリは`services.yml`の`volumeMountPathList`で、走査間隔は`POLLING_INTERVAL`で指定します。   
   
1. 監視しているディレクトリに新しく作成されたファイルを、`/var/lib/aion/defalt/Data/`配下にコピーする。   
2. MySQLの`csv_upload_transaction`テーブルに、タイムスタンプ、1.でコピーしたファイルパス、status="before"を挿入する。 監視しているディレクトリに新しく作成されたファイルを、`/var/lib/aion/defalt/Data/`配下にコピーする。   
3. kanbanに、`omotebako-sc`のconnectionKeyで、タイムスタンプと1.でコピーしたファイルパスを送る。 MySQLの`csv_upload_transaction`テーブルに、タイムスタンプ、1.でコピーしたファイルパス、status="before"を挿入する。


## 動作環境
site-controller-data-update-to-mysqlは、aion-coreのプラットフォーム上での動作を前提としています。    
使用する際は、事前に下記の通りAIONの動作環境を用意してください。

* OS: Linux OS   
  
* CPU: ARM/AMD/Intel   
  
* Kubernetes   
  
* AION のリソース   

## セットアップ
### 1. エッジ端末でWindowsサーバの共有フォルダをマウント

#### 1.1. エッジ端末でWindows共有フォルダをマウント
1.1.1 Windows側で共有フォルダを作成
- 任意のディレクトリに共有フォルダ用のフォルダを新規作成する
- 作成したフォルダを右クリック→「プロパティ」→「共有タブ」を開く
- 「共有」ボタンを押し、プルダウンから「Everyone」を選択し、追加
- 「共有」を押し、「終了」を押す
- コマンドプロンプトを開き、`ipconfig`と入力
- IPv4 アドレスからwindowsサーバのIPアドレスを確認する（後で使う）

※ 参考URL   

https://bsj-k.com/win10-common-file/   

1.2.2 エッジ端末でWindowsの共有フォルダをマウント   

smbnetfsを使用します。マウントする際、ルートユーザーでないと挙動がおかしくなるため、ルートユーザーでマウント実行することを推奨します。   

```
# install smbnetfs
sudo apt install smbnetfs

# make folder
mkdir ~/.smb
cd ~/.smb

# copy default config files
cp /etc/samba/smb.conf .
cp /etc/smbnetfs.conf .

# make config files
echo "host {windowsIPアドレス} WORKGROUP visible=true" > smbnetfs.host
echo "auth {windowsユーザー名} {windowsユーザーパスワード}" > smbnetfs.auth

# drop file permission
chmod 600 smbnetfs.host smbnetfs.auth

# mount
mkdir /mnt/windows
sudo -i
smbnetfs /mnt/windows
```
マウントした後、共有フォルダがあることを確認します。デスクトップに共有フォルダを置いた場合、`/{windowsIPアドレス}/Users/{ユーザー名}/Desktop`にあると思われます。

※ マウントを解除したい場合   
```
# unmount for root user
umount /mnt/windows

# unmount for non-root users
fusermount -u test
```

※ 参考URL   

https://ruco.la/memo/tag/fuse   

### 2. Dockerイメージの作成   
```
cd site-controller-data-update-to-mysql
make docker-build
```

## 起動方法   
### デプロイ on AION   
`services.yml`に設定を記載し、AionCore経由でコンテナを起動します。   

例）   
```
  site-controller-data-update-to-mysql:
    scale: 1
    startup: yes
    always: yes
    env:
      KANBAN_ADDR: aion-statuskanban:10000
      MYSQL_USER: MYSQL_USER_XXX
      MYSQL_HOST: mysql
      MYSQL_PASSWORD: <PASSWORD>
      POLLING_INTERVAL: 5
      SITE_CONTOROLLER_NAME: XXX
      MOUNT_PATH: /mnt/windows/{共有フォルダへのパス}
    nextService:
      sc_csv:
        - name: omotebako-sc
    volumeMountPathList:
      - /mnt/windows:/mnt/windows/{共有フォルダへのパス}
```
{共有フォルダへのパス}の例（デスクトップにある場合）：`{windowsIPアドレス}/Users/{ユーザー名}/Desktop/{フォルダ名}`


## I/O   
kanbanのメタデータから下記の情報を入出力します。   

### input   
kanbanのメタデータ   

### output   
* outputPath: `/var/lib/aion/defalt/Data/`配下にコピーしたcsvのファイルパス
* timestamp:

## エラー対処法   
1. ホストのディレクトリがマウントできないと言われた時
```
Error: failed to start container "site-controller-data-update-to-mysql-001": Error response from daemon: error while creating mount source path '/mnt/windows': mkdir /mnt/windows: file exists
```
→ mac側の共有フォルダから削除   
→ 再度共有フォルダに追加・linuxでマウント   

## ローカルからデバッグする場合   
MYSQLの情報をコマンドライン引数で渡すことによって、別のローカル端末からエッジ端末のデータベースにアクセスすることができます。   

<file_sep>/app/server/handlers/updateErrorStatus.go
package handlers

import (
	"fmt"
	"net/http"
	"site-controller-data-update-to-mysql/app/database"
	"site-controller-data-update-to-mysql/app/models"

	"github.com/gin-gonic/gin"
)

func UpdateErrorStatus(c *gin.Context, db *database.Database) {
	rows, err := models.CSVExecutionErrors(
		models.CSVExecutionErrorWhere.Status.EQ(0),
	).All(c, db.DB)
	if err != nil {
		logging.Error(fmt.Sprintf("failed to get csv_excution_error: %v", err), nil)
		c.JSON(http.StatusInternalServerError, gin.H{"timestamp": nil})
		return
	}
	logging.Debug(fmt.Sprintf("csv_excution_error record: %p", rows), nil)

	if len(rows) == 0 {
		logging.Info("no error csv", nil)
		c.JSON(http.StatusOK, gin.H{"timestamp": nil})
		return
	}

	_, err = rows.UpdateAll(c, db.DB, models.M{"status": 1})
	if err != nil {
		logging.Error(fmt.Sprintf("failed to get csv_excution_error: %v", err), nil)
		c.JSON(http.StatusInternalServerError, gin.H{"timestamp": nil})
		return
	}
	c.JSON(http.StatusOK, gin.H{"timestamp": nil})
}
<file_sep>/config/config.go
package config

import (
	"fmt"
	"os"
	"strconv"

	"golang.org/x/xerrors"
)

type Env struct {
	*MysqlEnv
	*WatchEnv
	Port string
}
type MysqlEnv struct {
	User     xxxx
	Host     xxxx
	Password xxxx
	Port     xxxx
}

type WatchEnv struct {
	PollingInterval xxxx
	MountPath       xxxx
}

// NewEnv 必ずEnv構造体は返る、POLLING_INTERVALに数字が入っていない場合にエラーが返る
func NewEnv() (*Env, error) {
	watchEnv, err := NewWatchEnv()
	return &Env{
		MysqlEnv: NewMysqlEnv(),
		WatchEnv: watchEnv,
		Port:     GetEnv("PORT", "8080"),
	}, err
}

func NewMysqlEnv() *MysqlEnv {
	user := GetEnv("MYSQL_USER", "xxxx")
	host := GetEnv("MYSQL_HOST", "xxxx")
	pass := GetEnv("MYSQL_PASSWORD", "<PASSWORD>")
	port := GetEnv("MYSQL_PORT", "xxxx")

	return &MysqlEnv{
		User:     user,
		Host:     host,
		Password: <PASSWORD>,
		Port:     port,
	}
}

func NewWatchEnv() (*WatchEnv, error) {
	pollingInterval, err := strconv.Atoi(GetEnv("POLLING_INTERVAL", "1"))
	if err != nil {
		pollingInterval = 1
		err = xerrors.Errorf("POLLING_INTERVAL should be int: %w", err)
	}
	return &WatchEnv{
		PollingInterval: pollingInterval,
		MountPath:       GetEnv("MOUNT_PATH", "/mnt/windows"),
	}, err
}

func (c *MysqlEnv) DSN() string {
	return fmt.Sprintf(`%v:%v@tcp(%v:%v)/%s?charset=utf8mb4&parseTime=True&loc=Local`, c.User, c.Password, c.Host, c.Port, "xxxxx")
}

func GetEnv(key, def string) string {
	value := os.Getenv(key)
	if value == "" {
		value = def
	}
	return value
}
<file_sep>/app/database/siteControl.go
package database

import (
	"context"
	"database/sql"
	"fmt"
	scCsv "site-controller-data-update-to-mysql/app/csv"
	"site-controller-data-update-to-mysql/app/file"
	"site-controller-data-update-to-mysql/app/helper"
	"site-controller-data-update-to-mysql/app/models"
	"strings"
	"time"

	"github.com/volatiletech/null/v8"
	"github.com/volatiletech/sqlboiler/v4/boil"
	"github.com/volatiletech/sqlboiler/v4/queries/qm"
	"golang.org/x/xerrors"

	"github.com/latonaio/golang-logging-library/logger"
)

type reservationGuest struct {
	ReservationID xxx
	GuestID       xxx
	*scCsv.ReservationData
}

type ErrorStruct struct {
	CustomerName        xxxx
	CustomerPhoneNumber xxxx
	ErrorMsg            xxxx
}

var logging = logger.NewLogger()

func (d *Database) TransactionReservationInfo(reservations []*scCsv.ReservationData, ctx context.Context) (map[int]ErrorStruct, error) {
	var reservationGuests []*reservationGuest
	errorMap := map[int]ErrorStruct{}

	for i, reservation := range reservations {
		tx, err := d.DB.BeginTx(ctx, nil)
		if err != nil {
			return nil, xerrors.Errorf("failed to begin transaction: %w", err)
		}
		switch reservation.Notice {
		case "予約":
			reservationGuest, err := d.addReservationInfoToDB(reservation, tx, ctx)
			if err != nil {
				errorMap[i] = ErrorStruct{
					CustomerName:        reservation.ReservationHolder,
					CustomerPhoneNumber: reservation.ReservationHolderPhoneNumber,
					ErrorMsg:            fmt.Sprint(err),
				}
				if err := tx.Rollback(); err != nil {
					return nil, xerrors.Errorf("Rolleback is uncompleted: %w", err)
				}
				continue
			}
			if err := tx.Commit(); err != nil {
				return nil, xerrors.Errorf("Database Commit is uncompleted: %w", err)
			}
			reservationGuests = append(reservationGuests, reservationGuest)
		case "取消":
			err := deleteReservationInfoFromDB(reservation, reservationGuests, tx, ctx)
			if err != nil {
				errorMap[i] = ErrorStruct{
					CustomerName:        reservation.ReservationHolder,
					CustomerPhoneNumber: reservation.ReservationHolderPhoneNumber,
					ErrorMsg:            fmt.Sprint(err),
				}
				if err := tx.Rollback(); err != nil {
					return nil, xerrors.Errorf("Rolleback is uncompleted: %w", err)
				}
				continue
			}
			if err := tx.Commit(); err != nil {
				return nil, xerrors.Errorf("Database Commit is uncompleted: %w", err)
			}
		default:
			errorMap[i] = ErrorStruct{
				CustomerName:        reservation.ReservationHolder,
				CustomerPhoneNumber: reservation.ReservationHolderPhoneNumber,
				ErrorMsg:            fmt.Sprintf("unknown reservation type :%v", reservation.Notice),
			}
		}
	}

	if len(errorMap) != 0 {
		return errorMap, nil
	}

	return nil, nil
}

func (d *Database) GetCsvExecutionErrorsWithCsvUploadTransactionByStatus(ctx context.Context, status int) (models.CSVExecutionErrorSlice, error) {
	rows, err := models.CSVExecutionErrors(
		qm.Select("*"),
		models.CSVExecutionErrorWhere.Status.EQ(status),
		qm.Load(models.CSVExecutionErrorRels.CSV),
	).All(ctx, d.DB)
	if err != nil {
		return nil, err
	}
	return rows, nil
}

func (d *Database) GetCsvUploadTransactionByTimeStamp(ctx context.Context, timestamp string) (models.CSVUploadTransactionSlice, error) {
	queries_csv_transaction := []qm.QueryMod{
		qm.Where(models.CSVUploadTransactionColumns.Timestamp+"=?", timestamp),
	}
	csvTransaction, err := models.CSVUploadTransactions(queries_csv_transaction...).All(ctx, d.DB)
	if err != nil {
		return nil, err
	}
	return csvTransaction, nil
}

func (d *Database) GetCsvExecutionErrorsByStatus(ctx context.Context, status int) (models.CSVExecutionErrorSlice, error) {
	rows, err := models.CSVExecutionErrors(
		models.CSVExecutionErrorWhere.Status.EQ(status),
	).All(ctx, d.DB)
	if err != nil {
		return nil, err
	}
	return rows, nil
}

func (d *Database) addReservationInfoToDB(reservation *scCsv.ReservationData, tx *sql.Tx, ctx context.Context) (*reservationGuest, error) {
	currentTime := time.Now()
	newReservationGuest := reservationGuest{
		ReservationData: reservation,
	}

	stayDateFrom, err := checkInTime(reservation)
	if err != nil {
		logging.Error(fmt.Sprintf("failed to parse reservationDate: %v\n", err), nil)
		return &newReservationGuest, fmt.Errorf("チェックイン日が不正か入力されていません。")
	}

	stayDateTo, err := time.Parse("20060102", reservation.StayDateTo)
	if err != nil {
		logging.Error(fmt.Sprintf("failed to parse stayDateTo: %v\n", err), nil)

		return &newReservationGuest, fmt.Errorf("チェックアウト日が不正か入力されていません。")
	}

	reservationDate, err := time.Parse("20060102", reservation.ReservatioinDate)
	if err != nil {
		logging.Error(fmt.Sprintf("failed to parse reservationDate: %v\n", err), nil)
		return &newReservationGuest, fmt.Errorf("予約受信日が不正か入力されていません。")
	}

	reservationMethodId, err := checkReservationMethod(reservation, ctx, tx)
	if err != nil {
		logging.Error(fmt.Sprintf("invalid reservation method: %v", err), nil)
		return &newReservationGuest, fmt.Errorf("予約経路が不正か入力されていません。")
	}

	paymentMethodId, err := checkPaymentMethod(reservation.PaymentMethodName, ctx, tx)
	if err != nil {
		logging.Error(fmt.Sprintf("failed to insert payment method error: %v", err), nil)
	}

	planId := checkProductMaster(reservation.ProductCode, reservation.ProductName, ctx, tx)

	if Results := validateReservationData(reservation); Results != nil {
		logging.Error(fmt.Sprintf("validation reservation data error: %v", Results), nil)

		ResultsStr := fmt.Sprint(strings.Join(Results, ", "))
		return &newReservationGuest, fmt.Errorf("必要な項目が入力されていません。: %v", ResultsStr)
	}

	guest := checkNewGuest(reservation, ctx, tx)

	// Insert reservationの準備
	newReservation := models.Reservation{
		ReservationHolder:     null.StringFrom(reservation.ReservationHolder),
		ReservationHolderKana: null.StringFrom(reservation.ReservationHolderKana),
		StayDateFrom:          null.TimeFrom(stayDateFrom),
		StayDateTo:            null.TimeFrom(stayDateTo),
		StayDays:              null.Int16From(reservation.StayDays),
		NumberOfRooms:         null.Int16From(reservation.NumberOfRooms),
		NumberOfGuests:        null.Int16From(reservation.NumberOfGuestsMale + reservation.NumberOfGuestsFemale),
		NumberOfGuestsMale:    null.Int16From(reservation.NumberOfGuestsMale),
		NumberOfGuestsFemale:  null.Int16From(reservation.NumberOfGuestsFemale),
		HasChild:              null.Int8From(checkChild(reservation)),
		ProductID:             null.StringFromPtr(planId),
		ReservationMethod:     null.IntFrom(reservationMethodId),
		PaymentMethod:         null.IntFrom(paymentMethodId),
		Coupon:                null.IntFrom(0), //【要検討】0:未, 1:有, 2:無
		// StatusCode:            null.Int8From(0),   // default:0が指定される
		Plan:       null.StringFrom(reservation.ProductName),
		UpdateDate: null.TimeFrom(currentTime),
		// IsCheckin:             null.Int8From(1),   // defaultで0:未が指定される
		ReservationDate: null.TimeFrom(reservationDate),
		PaymentStatus:   null.IntFrom(0), // defaultとして0:未を指定
		// NewGuestFlag:          null.Int8From(1),   // defaultで0が指定される
		// DeleteFlag:            null.IntFrom(1),   // defaultで0が指定される
	}

	if guest == nil {
		logging.Info("新規顧客", nil)

		// Insert guest
		newGuests := models.Guest{
			// GuestID:       int
			Name:     null.StringFrom(reservation.Name),
			NameKana: null.StringFrom(reservation.NameKana),
			Gender:   null.IntFrom(1), // defaultとして1:女性を指定
			// GenderByFace:  null.StringFrom(),
			// AgeByFace:     null.Float32From(),
			// BirthDate:     null.TimeFrom(),
			// Age:           null.IntFrom(30),   // defaultとして30を指定？
			GuestEmail:  null.StringFrom(reservation.Email),
			PhoneNumber: null.StringFrom(reservation.PhoneNumber),
			PostalCode:  null.StringFrom(helper.PostalCodeFormat(reservation.PostalCode)),
			HomeAddress: null.StringFrom(reservation.HomeAddress),
			CreateDate:  null.TimeFrom(currentTime),
			UpdateDate:  null.TimeFrom(currentTime),
			// FaceIDAzure:   null.StringFrom(),
			// FaceImagePath: null.StringFrom(),
			// DeleteFlag:    null.Int8From(0),  // default: 0
		}
		if err := newGuests.Insert(ctx, tx, boil.Infer()); err != nil {
			logging.Error(fmt.Sprintf("failed to insert Guset record: %v", err), nil)
			return &newReservationGuest, fmt.Errorf("顧客情報の登録に失敗しました。")
		}

		//
		newReservation.GuestID = null.IntFrom(newGuests.GuestID)
	} else {
		logging.Info(fmt.Sprintf("既存顧客 guest_id: %d", guest.GuestID), nil)
		guest.GuestEmail = null.StringFrom(reservation.Email)
		guest.PhoneNumber = null.StringFrom(reservation.PhoneNumber)
		guest.PostalCode = null.StringFrom(helper.PostalCodeFormat(reservation.PostalCode))
		guest.HomeAddress = null.StringFrom(reservation.HomeAddress)

		if _, err := guest.Update(ctx, tx, boil.Whitelist(
			models.GuestColumns.GuestEmail,
			models.GuestColumns.PhoneNumber,
			models.GuestColumns.PostalCode,
			models.GuestColumns.HomeAddress,
		)); err != nil {
			logging.Error(fmt.Sprintf("failed to update Guset record: %v", err), nil)
			return &newReservationGuest, fmt.Errorf("顧客情報の更新に失敗しました。")
		}

		newReservation.GuestID = null.IntFrom(guest.GuestID)
		newReservation.NewGuestFlag = null.Int8From(1)
	}

	if err := newReservation.Insert(ctx, tx, boil.Infer()); err != nil {
		logging.Error(fmt.Sprintf("failed to insert Reservation record: %v", err), nil)
		return &newReservationGuest, fmt.Errorf("予約情報の登録に失敗しました。")
	}
	logging.Info(fmt.Sprintf("add reservation ID: %v, Name: %v\n", newReservation.GuestID, newReservation.ReservationHolder), "csv")
	newReservationGuest = reservationGuest{
		ReservationID:   newReservation.ReservationID,
		ReservationData: reservation,
	}
	logging.Debug(fmt.Sprintf("reservation guest: %v", newReservationGuest), nil)
	return &newReservationGuest, nil
}

func deleteReservationInfoFromDB(reservation *scCsv.ReservationData, reservationGuests []*reservationGuest, tx *sql.Tx, ctx context.Context) error {
	updCols := map[string]interface{}{
		models.ReservationColumns.DeleteFlag: 1,
	}

	targetID, err := selectDeleteReservationID(reservation, reservationGuests, tx, ctx)
	if err != nil || targetID == 0 {
		return err
	}

	query := qm.Where(models.ReservationColumns.ReservationID+"=?", targetID)

	_, err = models.Reservations(query).UpdateAll(ctx, tx, updCols)
	if err != nil {
		logging.Error(fmt.Sprintf("failed to update reservation delete flag: %v", err), nil)

		return fmt.Errorf("予約のキャンセルに失敗しました。")
	}

	return nil
}

func selectDeleteReservationID(reservation *scCsv.ReservationData, reservationGuests []*reservationGuest, tx *sql.Tx, ctx context.Context) (int, error) {
	if Results := validateDeleteReservationData(reservation); Results != nil {
		logging.Error(fmt.Sprintf("validation delete reservation data error: %v", Results), nil)
		ResultsStr := fmt.Sprint(strings.Join(Results, ", "))
		return 0, fmt.Errorf("必要な項目が入力されていません。: %v", ResultsStr)
	}

	queries_guest := []qm.QueryMod{
		//qm.Select("guest_id"),
		qm.Where(models.GuestColumns.Name+"=?", reservation.Name),
		qm.And(models.GuestColumns.NameKana+"=?", reservation.NameKana),
		qm.And(models.GuestColumns.PhoneNumber+"=?", reservation.PhoneNumber),
		// qm.And(models.GuestColumns.HomeAddress+"=?", reservation.PostalCode + reservation.HomeAddress),
	}
	guests, err := models.Guests(queries_guest...).All(ctx, tx)
	if err != nil {
		logging.Error(fmt.Sprintf("failed to get guest records: %v", err), nil)

		return 0, fmt.Errorf("キャンセルする顧客の取得に失敗しました。")
	}

	// WhereIn method needs to pass a slice of interface{}
	var guestIDs []interface{}
	for _, v := range guests {
		logging.Debug(fmt.Sprintf("guestID: %d", v.GuestID), nil)
		guestIDs = append(guestIDs, v.GuestID)
	}

	// guest_id, stayDateFrom, stayDateTo　→　reservationIdの特定
	queries_reservation := []qm.QueryMod{
		qm.WhereIn(models.ReservationColumns.GuestID+" IN ?", guestIDs),
		qm.And(models.ReservationColumns.StayDateTo+"=?", reservation.StayDateTo),
		qm.And(models.ReservationColumns.StayDays+"=?", reservation.StayDays),
		qm.And(models.ReservationColumns.NumberOfRooms+"=?", reservation.NumberOfRooms),
		qm.And(models.ReservationColumns.NumberOfGuests+"=?", reservation.NumberOfGuests),
	}

	counts, err := models.Reservations(queries_reservation...).Count(ctx, tx)
	if counts == 0 {
		logging.Debug("No reservation", nil)
		for _, reservationGuest := range reservationGuests {
			logging.Debug(fmt.Sprintf("reservationGuest: %v, %v, %v", reservationGuest.Name, reservationGuest.NameKana, reservationGuest.PhoneNumber), nil)
			if reservation.Name == reservationGuest.Name && reservation.NameKana == reservationGuest.NameKana && reservation.PhoneNumber == reservationGuest.PhoneNumber {
				return reservationGuest.ReservationID, nil
			}
		}
		logging.Error(fmt.Sprintf("no reservation, name: %s, phone number: %s", reservation.Name, reservation.PhoneNumber), nil)

		return 0, fmt.Errorf("キャンセルする予約が登録されていません。")
	} else if counts > 1 {
		logging.Error(fmt.Sprintf("multiple reservations exist, name: %v, phone number: %v", reservation.Name, reservation.PhoneNumber), nil)

		return 0, fmt.Errorf("キャンセルする同一予約が複数登録されています。")
	} else if err != nil {
		logging.Error(fmt.Sprintf("cannot detect delete reservationID: %v\n", err), nil)
		return 0, fmt.Errorf("キャンセルする予約を特定できませんでした。")
	}

	reservationId, err := models.Reservations(queries_reservation...).All(ctx, tx)
	if err != nil {
		logging.Error(fmt.Sprintf("failed to get reservation ID: %v", err), nil)
		// エラーメッセージ：reservationのgetエラー
		return 0, fmt.Errorf("キャンセルする予約の取得に失敗しました。")
	}
	logging.Info(fmt.Sprintf("delete ReservationID: %v, Name: %v\n", reservationId[0].ReservationID, reservationId[0].ReservationHolder), nil)
	return reservationId[0].ReservationID, nil
}

// TODO: websocket実装時に必要かも
func (d *Database) SelectErrorCSVRowsWithIds(csvIds []int, ctx context.Context, tx *sql.Tx) (models.CSVExecutionErrorSlice, error) {
	// whereinに合わせて型を変換する
	var ids []interface{}
	for _, v := range csvIds {
		ids = append(ids, v)
	}
	// query := []qm.QueryMod{
	// 	qm.Select("*"),
	// 	qm.From("csv_execution_errors"),
	// 	qm.InnerJoin("csv_upload_transaction on csv_upload_transaction.id = csv_execution_errors.csv_id"),
	// 	   qm.WhereIn(models.CSVExecutionErrorColumns.ID+" IN ?", csvIds),
	// 	   qm.WhereIn(fmt.Sprintf("%s in ?", models.CSVExecutionErrorColumns.ID), ids...),
	// }
	rows, err := models.CSVExecutionErrors(qm.InnerJoin("csv_upload_transaction on csv_upload_transaction.id = csv_execution_errors.csv_id")).All(ctx, tx)
	if err != nil {
		return nil, err
	}
	fmt.Printf("%v", *rows[0])
	// rows, err := models.CSVExecutionErrors(query...).All(ctx, tx)
	// if err != nil {
	// 	return nil, err
	// }
	return rows, nil
}

func (d *Database) RegisterCSVDataToDB(ctx context.Context, file file.File, path string, id int, siteControllerName string) error {
	logging.Info(fmt.Sprintf("site ControllerName: %v ", siteControllerName), "controller_name")

	// トランザクション：insertReservation, insertGuest
	csvPath := fmt.Sprintf("%s/%s", path, file.Name)
	var reservations []*scCsv.ReservationData
	var err error
	switch siteControllerName {
	case "xxxx":
		reservations, err = scCsv.ImportFromLincoln(csvPath)
	case "xxxx":
		reservations, err = scCsv.ImportFromLincoln(csvPath)
	case "xxxx":
		reservations, err = scCsv.ImportFromLincoln(csvPath)
	default:
		return xerrors.Errorf("site controller name '%s' is not available", siteControllerName)
	}
	if err != nil {
		if err := d.updateCsvUploadTransactionStatusToError(id, ctx); err != nil {
			return xerrors.Errorf("failed to upload csv_upload_transaction status: %w", err)
		}
		return xerrors.Errorf("path: %s, failed to import csv: %w", csvPath, err)
	}

	errors, err := d.TransactionReservationInfo(reservations, ctx)

	// トランザクションOK...csvステータスをcompleteに変える
	if err == nil && errors == nil {
		if err := d.finishCsvUpload(id, ctx); err != nil {
			return fmt.Errorf("failed to upload csv_upload_transaction status: %v", err)
		} else {
			logging.Info("successful of csv uploading", nil)
		}
	} else {
		// トランザクションERROR...csvステータスをerrorに変える
		if err := d.updateCsvUploadTransactionStatusToError(id, ctx); err != nil {
			return fmt.Errorf("failed to upload csv_upload_transaction status: %v", err)
		} else {
			logging.Info("failed to upload csv", nil)

		}
		// csv_execution_errorにerror内容を入れる
		// TODO 戻り値のidsをチャネル使ってwebsocketに渡す
		ids := d.InsertCSVExecutionError(ctx, errors, id)
		logging.Debug(fmt.Sprintf("error ids: %v", ids), nil)
	}
	return nil
}

func (d *Database) CreateCsvUploadTransaction(ctx context.Context, fileName string, createdTime time.Time, timestamp string, path string) (*models.CSVUploadTransaction, error) {
	// mysqlにinsertするデータを作成
	newCSVUploadTransaction := models.CSVUploadTransaction{
		FileName:             null.StringFrom(fileName),
		Status:               null.StringFrom("before"),
		CreatedTimeInWindows: null.TimeFrom(createdTime),
		Timestamp:            null.StringFrom(timestamp),
		Path:                 null.StringFrom(path),
	}

	// mysqlにinsertする
	if err := newCSVUploadTransaction.Insert(ctx, d.DB, boil.Infer()); err != nil {
		return nil, err
	}

	return &newCSVUploadTransaction, nil
}

func (d *Database) GetCSVUpdateTransaction(ctx context.Context) (models.CSVUploadTransactionSlice, error) {
	q := models.CSVUploadTransactions(
		qm.Select("id", "file_name", "created_time_in_windows"),
		qm.OrderBy("created_time_in_windows DESC"),
	)

	rows, err := q.All(ctx, d.DB)
	if err != nil {
		return nil, fmt.Errorf("failed to get records of csv_upload_transaction records: %v", err)
	}

	return rows, nil
}

func (d *Database) updateCsvUploadTransactionStatusToError(id int, ctx context.Context) error {
	record := models.CSVUploadTransaction{
		ID:     id,
		Status: null.StringFrom("ERROR"),
	}

	err := record.Upsert(ctx, d.DB, boil.Whitelist(models.CSVUploadTransactionColumns.Status), boil.Infer())
	if err != nil {
		return err
	}

	return nil
}

func (d *Database) finishCsvUpload(id int, ctx context.Context) error {
	record, err := models.CSVUploadTransactions(
		qm.Where(models.CSVUploadTransactionColumns.ID+" = ?", id),
	).One(ctx, d.DB)
	if err != nil {
		return xerrors.Errorf("failed to get csv transaction data: %w", err)
	}
	if record.Status.String == "complete" {
		return xerrors.New("csv upload status is already complete")
	}

	record.Status = null.StringFrom("complete")
	_, err = record.Update(ctx, d.DB, boil.Infer())
	if err != nil {
		return xerrors.Errorf("failed to get csv transaction data: %w", err)
	}
	return nil
}

func (d *Database) InsertCSVExecutionError(ctx context.Context, mapError map[int]ErrorStruct, csvId int) []int {
	var ids []int
	// mysqlにinsertするデータを生成
	for i, errStruct := range mapError {
		newCSVExecutionError := models.CSVExecutionError{
			//ID:                  null.IntFrom(),
			LineNumber:          i + 1,
			CustomerName:        null.StringFrom(errStruct.CustomerName),
			CustomerPhoneNumber: null.StringFrom(errStruct.CustomerPhoneNumber),
			ErrorMessage:        errStruct.ErrorMsg,
			Status:              0, //未対応は0
			CSVID:               csvId,
		}

		if err := newCSVExecutionError.Insert(ctx, d.DB, boil.Infer()); err != nil {
			logging.Error(fmt.Sprintf("failed to insert new record to csv_execution_errors: line number: %d, error message: %v", i+1, err), nil)
		}

		// ID = 0はDB insertエラーを表します
		ids = append(ids, newCSVExecutionError.ID)
	}
	return ids
}
<file_sep>/go.mod
module site-controller-data-update-to-mysql

go 1.16

require (
	github.com/latonaio/golang-logging-library v1.0.0
	github.com/friendsofgo/errors v0.9.2
	github.com/gin-contrib/cors v1.3.1
	github.com/gin-gonic/gin v1.7.2
	github.com/go-sql-driver/mysql v1.6.0
	github.com/gocarina/gocsv v0.0.0-20210516172204-ca9e8a8ddea8
	github.com/golang/glog v0.0.0-20210429001901-424d2337a529
	github.com/gorilla/websocket v1.4.2
	github.com/kat-co/vala v0.0.0-20170210184112-42e1d8b61f12
	github.com/modern-go/concurrent v0.0.0-20180306012644-bacd9c7ef1dd 
	github.com/spf13/viper v1.8.1
	github.com/volatiletech/null/v8 v8.1.2
	github.com/volatiletech/randomize v0.0.1
	github.com/volatiletech/sqlboiler/v4 v4.6.0
	github.com/volatiletech/strmangle v0.0.1
	golang.org/x/text v0.3.6 
	golang.org/x/xerrors v0.0.0-20200804184101-5ec99f83aff1
	gopkg.in/check.v1 v1.0.0-20190902080502-41f04d3bba15 
)
<file_sep>/app/server/handlers/timestamp.go
package handlers

import (
	"fmt"
	"net/http"
	"site-controller-data-update-to-mysql/app/database"
	"site-controller-data-update-to-mysql/app/models"

	"context"

	"github.com/gin-gonic/gin"
	"github.com/volatiletech/sqlboiler/v4/queries/qm"
)

func GetLatestTimestamp(c *gin.Context, db *database.Database) {
	ctx, cancel := context.WithCancel(context.Background())
	defer cancel()

	row, err := models.CSVUploadTransactions(
		qm.Select("*"),
		qm.OrderBy("timestamp DESC"),
	).One(ctx, db.DB)
	if err != nil {
		logging.Error(fmt.Sprintf("failed to get csv_upload_transaction: %v", err), nil)
		c.JSON(http.StatusInternalServerError, gin.H{"timestamp": nil})
		return
	}
	logging.Debug(fmt.Sprintf("csv_upload_transaction record: %p", row), nil)
	if row == nil {
		logging.Info("no csv information", nil)

		c.JSON(http.StatusOK, gin.H{"timestamp": nil})
		return
	}

	timestampStr := row.Timestamp.String
	timestampVal := fmt.Sprintf(`%s/%s/%s %s:%s:%s`, timestampStr[0:4], timestampStr[4:6], timestampStr[6:8], timestampStr[8:10], timestampStr[10:12], timestampStr[12:14])
	logging.Info(fmt.Sprintf("latest timestamp: %s", timestampVal), nil)
	c.JSON(http.StatusOK, gin.H{"timestamp": timestampVal})
	return
}
<file_sep>/mount.sh
#!/bin/bash

# jetson端末再起動時にwindowsサーバのフォルダーをマウントする
find /mnt/windows -mindepth 1 -maxdepth 1 -type d | while read path; do
  rm -rf "$path"
done

smbnetfs /mnt/windows
<file_sep>/app/file/file.go
package file

import (
	"io/fs"
	"time"
)

type File struct {
	Name        string
	CreatedTime time.Time
}

type Files []*File

func NewFile(file fs.FileInfo) *File {
	return &File{
		Name:        file.Name(),
		CreatedTime: file.ModTime(),
	}
}
<file_sep>/app/database/mysql.go
package database

import (
	"database/sql"
	"site-controller-data-update-to-mysql/config"

	_ "github.com/go-sql-driver/mysql"
	"golang.org/x/xerrors"
)

type Database struct {
	DB *sql.DB
}

func NewDatabase(mysqlEnv *config.MysqlEnv) (*Database, error) {
	db, err := sql.Open("mysql", mysqlEnv.DSN())
	if err != nil {
		return nil, xerrors.Errorf(`database open error: %w`, err)
	}
	if err = db.Ping(); err != nil {
		return nil, xerrors.Errorf(`failed to connection database: %w`, err)
	}
	return &Database{
		DB: db,
	}, nil
}
<file_sep>/app/server/response/authCSV.go
package response

type AuthCSV struct {
	Timestamp string `json:"timestamp"`
	Status    string `json:"status"`
	Path      string `json:"path"`
}
<file_sep>/app/server/server.go
package server

import (
	"fmt"
	"site-controller-data-update-to-mysql/app/server/handlers"
	"time"

	"github.com/gin-gonic/gin"
	"github.com/gorilla/websocket"
)

type Server struct {
	gin  *gin.Engine
	ws   *websocket.Upgrader
	port string
}

func NewServer(port interface{}, handler *handlers.SCHandler) *Server {
	return &Server{
		gin: gin.New(),
		ws: &websocket.Upgrader{
			HandshakeTimeout:  5 * time.Second,
			ReadBufferSize:    0,
			WriteBufferSize:   0,
			WriteBufferPool:   nil,
			Subprotocols:      nil,
			Error:             nil,
			CheckOrigin:       nil,
			EnableCompression: false,
		},
		port: fmt.Sprintf(`:%v`, port),
	}
}
<file_sep>/app/server/router/route.go
package router

import (
	"fmt"
	"log"
	"site-controller-data-update-to-mysql/app/database"
	"site-controller-data-update-to-mysql/app/server/handlers"
	"time"

	"github.com/gin-contrib/cors"
	"github.com/gin-gonic/gin"
	"github.com/gorilla/websocket"
	"github.com/latonaio/golang-logging-library/logger"
)

type Server struct {
	gin  *gin.Engine
	ws   *websocket.Upgrader
	port string
	db   *database.Database
	log  *logger.Logger
}

func NewServer(port string, db *database.Database, logging *logger.Logger) *Server {
	return &Server{
		gin: gin.New(),
		ws: &websocket.Upgrader{
			HandshakeTimeout:  5 * time.Second,
			ReadBufferSize:    0,
			WriteBufferSize:   0,
			WriteBufferPool:   nil,
			Subprotocols:      nil,
			Error:             nil,
			CheckOrigin:       nil,
			EnableCompression: false,
		},
		port: fmt.Sprintf(`:%v`, port),
		db:   db,
		log:  logging,
	}
}

func (s *Server) Route() {
	handler := handlers.NewSCHandler(s.db, s.log)

	s.gin.Use(cors.New(cors.Config{
		AllowOrigins: []string{
			"*",
		},
		AllowMethods: []string{
			"POST",
			"GET",
			"PUT",
			"OPTIONS",
			"DELETE",
		},
		AllowHeaders: []string{
			"Accept",
			"Authorization",
			"Content-type",
			"X-CSRF-Token",
		},
		ExposeHeaders: []string{
			"Link",
		},
		AllowCredentials: false,
		MaxAge:           300,
	}))
	// restAPI一覧
	s.gin.GET("/api/auth/csv/:timestamp", handler.GetAuthCSV)

	baseGroup := s.gin.Group("/api/csv")

	// エラーハンドリング用エンドポイントその１
	baseGroup.GET("/transaction/display/errors", handler.CSVError)

	// エラーテーブルにあるエラーを全て解決済みに更新する
	baseGroup.POST("/error/resolve", handler.UpdateErrorStatus)

	// csv手動登録用エンドポイント
	baseGroup.POST("/:timestamp", handler.CreateCSV)

	// 前回の手動連携日時を返すエンドポイント
	baseGroup.GET("/transaction/latest", handler.GetLatestTimestamp)

	//g.GET("/:timestamp")
	//g.POST("/:timestamp")

	// TODO エラーハンドリング用エンドポイントその２
	s.gin.GET("/ws", func(c *gin.Context) {
		// handlers.GetCSVErrorHandler(c.Writer, c.Request)
		// handlers.WsConnect(c, s.db)
		// handlers.WsConnect(c, s.db, channel)
	})
}

func (s *Server) Run() {
	// log.Println("run server")
	if err := s.gin.Run(s.port); err != nil {
		log.Printf("run server error :%v", err)
	}
}
<file_sep>/Makefile
docker-build:
	bash builders/docker-build.sh
docker-push:
	bash builders/docker-build.sh push
delete-table:
	bash misc/delete-table.sh<file_sep>/app/main.go
package main

import (
	"context"
	"fmt"
	"os"
	"os/signal"
	"site-controller-data-update-to-mysql/app/cmd/fileController"
	"site-controller-data-update-to-mysql/app/database"
	"site-controller-data-update-to-mysql/app/file"
	"site-controller-data-update-to-mysql/app/server/router"
	"site-controller-data-update-to-mysql/config"

	"github.com/latonaio/golang-logging-library/logger"
)

func Server(port string, db *database.Database, logging *logger.Logger) {
	// Server構造体作成
	s := router.NewServer(port, db, logging)
	// Route実行
	s.Route()
	// Server実行
	s.Run()
}

func main() {
	var logging = logger.NewLogger()
	ctx := context.Background()
	// // Watch内で、新しいファイルが生成されるまで待機させる。
	listAuto := make(chan file.Files)

	// DB構造体作成
	env, err := config.NewEnv()
	if err != nil {
		logging.Warn(fmt.Sprintf("NewEnv error: %+v", err), nil)
	}
	db, err := database.NewDatabase(env.MysqlEnv)
	if err != nil {
		logging.Error(fmt.Sprintf("failed to create database: %+v", err), nil)
		return
	}
	// mainを終了させるためのチャネル
	quit := make(chan os.Signal, 1)
	signal.Notify(quit, os.Interrupt)
	// mainが終了した時にgoルーチンも終了するためのチャネル
	done := make(chan bool, 1)

	siteControllerName := config.GetEnv("SITE_CONTROLLER_NAME", "Lincoln")

	// 自動でcsvファイルからデータをMySQLに入れるgoルーチン
	go fileController.Watch(ctx, listAuto, done, db, env.WatchEnv)

	// HTTPサーバを立てる
	go Server(env.Port, db, logging)

	for {
		select {
		// 自動登録
		case newFileList := <-listAuto:
			for _, file := range newFileList {
				logging.Info(fmt.Sprintf("target fileName: %v\n", file.Name), nil)

				// csv登録...status＝before
				model, err := db.CreateCsvUploadTransaction(ctx, file.Name, file.CreatedTime, "", "")
				if err != nil {
					logging.Error(fmt.Sprintf("failed to insert record to database: %v", err), nil)
				}

				if err := db.RegisterCSVDataToDB(ctx, *file, env.MountPath, model.ID, siteControllerName); err != nil {
					logging.Error(err, nil)
				}
			}
		case <-quit:
			goto END
		}
	}
END:
	done <- true
	logging.Info("finish main function", nil)

}
<file_sep>/app/helper/format.go
package helper

import "strings"

// PostalCodeFormat format the zip code; 1234567 to 123-4567
func PostalCodeFormat(postalCode string) string {
	if strings.Contains(postalCode, "-") {
		return postalCode
	}
	return postalCode[:3] + "-" + postalCode[3:]
}
<file_sep>/app/database/validation.go
package database

import (
	"database/sql"
	"fmt"
	scCsv "site-controller-data-update-to-mysql/app/csv"
	"site-controller-data-update-to-mysql/app/helper"
	"site-controller-data-update-to-mysql/app/models"
	"time"

	"context"

	"github.com/volatiletech/null/v8"
	"github.com/volatiletech/sqlboiler/v4/boil"
	"github.com/volatiletech/sqlboiler/v4/queries/qm"
)

func checkInTime(reservation *scCsv.ReservationData) (time.Time, error) {
	if len(reservation.CheckInTime) > 0 {
		stayDateFrom, err := time.Parse("20060102 15:04", reservation.StayDateFrom+" "+reservation.CheckInTime)
		if err != nil {
			return time.Time{}, fmt.Errorf("failed to parse checkInTime: %v\n", err)
		}
		return stayDateFrom, nil
	} else {
		stayDateFrom, err := time.Parse("20060102", reservation.StayDateFrom)
		if err != nil {
			return time.Time{}, fmt.Errorf("failed to parse checkInTime: %v\n", err)
		}
		return stayDateFrom, nil
	}
}

func checkChild(reservation *scCsv.ReservationData) int8 {
	numChild := reservation.NumberOfGuestsChildA + reservation.NumberOfGuestsChildB + reservation.NumberOfGuestsChildC + reservation.NumberOfGuestsChildD
	if numChild > 0 {
		return 1 //有
	} else {
		return 0
	}
}

func checkPaymentMethod(paymentMethodName string, ctx context.Context, tx *sql.Tx) (int, error) {
	if paymentMethodName != "" {
		id, err := getPaymentMethod(paymentMethodName, ctx, tx)
		if err != nil {
			logging.Info(fmt.Sprintf("failed to get payment method error: %v", err), nil)
		}
		if id == 0 {
			id, err = insertPaymentMethod(paymentMethodName, ctx, tx)
			if err != nil {
				return 0, err
			}
			return id, nil
		}
		return id, nil
	}
	id, err := getPaymentMethod("指定なし", ctx, tx)
	if err != nil {
		logging.Info(fmt.Sprintf("failed to get payment method id correspond to '指定なし' error: %v", err), nil)
	}
	return id, nil
}

func getPaymentMethod(paymentMethodName string, ctx context.Context, tx *sql.Tx) (int, error) {
	record, err := models.PaymentMethodMasters(
		qm.Where(models.PaymentMethodMasterColumns.PaymentMethodName+" = ?", paymentMethodName),
	).One(ctx, tx)
	if err != nil {
		return 0, err
	}
	if record == nil {
		return 0, fmt.Errorf("%s not found", paymentMethodName)
	}
	return record.PaymentMethodID, nil
}

func insertPaymentMethod(paymentMethodName string, ctx context.Context, tx *sql.Tx) (int, error) {
	newPaymentMethodMaster := models.PaymentMethodMaster{
		//ReservationMethodID:   int,
		PaymentMethodName: null.StringFrom(paymentMethodName),
	}
	if err := newPaymentMethodMaster.Insert(ctx, tx, boil.Infer()); err != nil {
		return 0, err
	}
	return newPaymentMethodMaster.PaymentMethodID, nil
}

func checkReservationMethod(reservation *scCsv.ReservationData, ctx context.Context, tx *sql.Tx) (int, error) {
	if reservation.SalesAgentShopName != "" {
		id, err := getReservationMethod(reservation.SalesAgentShopName, ctx, tx)
		if err != nil {
			logging.Info(fmt.Sprintf("failed to get reservation method error: %v", err), nil)
		}
		if id == 0 {
			logging.Debug("start inserting reservation method master", nil)

			id, err = insertReservationMethod(reservation.SalesAgentShopName, ctx, tx)
			if err != nil {
				return 0, err
			}
			logging.Debug("finish inserting reservation method master", nil)

			return id, nil
		}
		return id, nil
	}
	return 0, fmt.Errorf("sales agent shop name is null")
}

func getReservationMethod(reservationMethod string, ctx context.Context, tx *sql.Tx) (int, error) {
	record, err := models.ReservationMethodMasters(
		qm.Where(models.ReservationMethodMasterColumns.ReservationMethodName+" = ?", reservationMethod),
	).One(ctx, tx)
	if err != nil {
		return 0, err
	}
	if record == nil {
		return 0, fmt.Errorf("%s not found", reservationMethod)
	}
	return record.ReservationMethodID, nil
}

func insertReservationMethod(reservationMethod string, ctx context.Context, tx *sql.Tx) (int, error) {
	newReservationMethodMaster := models.ReservationMethodMaster{
		//ReservationMethodID:   int,
		ReservationMethodName: null.StringFrom(reservationMethod),
	}
	if err := newReservationMethodMaster.Insert(ctx, tx, boil.Infer()); err != nil {
		return 0, err
	}
	return newReservationMethodMaster.ReservationMethodID, nil
}

func checkProductMaster(productId, productName string, ctx context.Context, tx *sql.Tx) *string {
	//	プランコード、プラン名がproduct_masterテーブルのproduct_id, product_nameに一致するかチェック
	if productId == "" || productName == "" {
		return nil
	}
	planId, err := getProductMaster(productId, productName, ctx, tx)
	if err != nil {
		logging.Info(fmt.Sprintf("failed to get product master error: %v", err), nil)
	}
	return planId
}

func getProductMaster(productId, productName string, ctx context.Context, tx *sql.Tx) (*string, error) {
	record, err := models.ProductMasters(
		qm.Where(models.ProductMasterColumns.ProductID+" = ?", productId),
		qm.And(models.ProductMasterColumns.ProductName+" = ?", productName),
	).One(ctx, tx)
	if err != nil {
		return nil, err
	}
	if record == nil {
		return nil, fmt.Errorf("%s not found", productId)
	}
	return &record.ProductID, nil
}

func validateReservationData(reservation *scCsv.ReservationData) []string {
	var validationStr []string
	if reservation.Name == "" {
		validationStr = append(validationStr, "団体名または代表者氏名 漢字")
	}
	if reservation.NameKana == "" {
		validationStr = append(validationStr, "団体名または代表者氏名(半角)")
	}
	if reservation.PhoneNumber == "" {
		validationStr = append(validationStr, "団体または代表者番号")
	}
	if reservation.PostalCode == "" {
		validationStr = append(validationStr, "団体または代表者郵便番号")
	}
	if reservation.HomeAddress == "" {
		validationStr = append(validationStr, "団体または代表者住所")
	}
	if reservation.ReservationHolder == "" {
		validationStr = append(validationStr, "予約者・会員名漢字")
	}
	if reservation.ReservationHolderKana == "" {
		validationStr = append(validationStr, "予約者・会員名カタカナ")
	}
	if reservation.StayDays == 0 {
		validationStr = append(validationStr, "泊数")
	}
	if reservation.NumberOfRooms == 0 {
		validationStr = append(validationStr, "利用客室合計数")
	}
	if reservation.NumberOfGuests == 0 {
		validationStr = append(validationStr, "お客様総合計人数")
	}
	if reservation.ProductName == "" {
		validationStr = append(validationStr, "プラン名")
	}
	return validationStr
}

func checkNewGuest(reservation *scCsv.ReservationData, ctx context.Context, tx *sql.Tx) *models.Guest {
	guestRecord, err := models.Guests(
		qm.Select(models.GuestColumns.GuestID),
		qm.Where(models.GuestColumns.Name+" = ?", reservation.Name),
		qm.And(models.GuestColumns.NameKana+" = ?", reservation.NameKana),
		qm.And(models.GuestColumns.PhoneNumber+" = ? or "+models.GuestColumns.PostalCode+" = ?", reservation.PhoneNumber, helper.PostalCodeFormat(reservation.PostalCode)),
	).One(ctx, tx)
	if err != nil {
		logging.Info(fmt.Sprintf("failed to check new guest error: %v", err), nil)
	}
	if guestRecord == nil {
		return nil
	}
	return guestRecord
}

func validateDeleteReservationData(reservation *scCsv.ReservationData) []string {
	var validationStr []string
	if reservation.Name == "" {
		validationStr = append(validationStr, "団体名または代表者氏名 漢字")
	}
	if reservation.NameKana == "" {
		validationStr = append(validationStr, "団体名または代表者氏名(半角)")
	}
	if reservation.PhoneNumber == "" {
		validationStr = append(validationStr, "団体または代表者番号")
	}
	return validationStr
}
<file_sep>/app/cmd/fileController/watch.go
package fileController

import (
	"context"
	"fmt"
	"os"
	"os/signal"
	"site-controller-data-update-to-mysql/app/database"
	"site-controller-data-update-to-mysql/app/file"
	"site-controller-data-update-to-mysql/config"
	"syscall"
	"time"

	"github.com/latonaio/golang-logging-library/logger"
)

var logging = logger.NewLogger()

func Watch(ctx context.Context, list chan<- file.Files, done <-chan bool, db *database.Database, env *config.WatchEnv) {
	logging.Info("created watch go routine", nil)
	// DBから最新のファイルの作成情報を取得する
	rows, err := db.GetCSVUpdateTransaction(ctx)
	if err != nil {
	}
	var latestFileCreatedTime time.Time
	if len(rows) > 0 {
		createdTimeInWindows := rows[0].CreatedTimeInWindows
		if t, _ := createdTimeInWindows.Value(); t != nil {
			latestFileCreatedTime = t.(time.Time)
		}
	}

	tickTime := time.Duration(env.PollingInterval) * time.Minute
	ticker := time.NewTicker(tickTime)
	defer ticker.Stop()

	signalCh := make(chan os.Signal, 1)
	signal.Notify(signalCh, syscall.SIGTERM)

	for {
		select {
		case s := <-signalCh:
			logging.Info(fmt.Sprintf("received signal: %s", s.String()), nil)

			return
		case <-ticker.C:
			logging.Info(fmt.Sprintf("start watch %s ", env.MountPath), nil)
			logging.Info(fmt.Sprintf("latest file created time: %v", latestFileCreatedTime), nil)

			newFileList, err := file.GetFileList(&latestFileCreatedTime, env.MountPath)
			if err != nil {
				goto L
			}

			if len(newFileList) == 0 {
				goto L
			}

			// ファイル登録処理へ渡す
			list <- newFileList

			// 最新ファイルの更新
			latestFileCreatedTime = newFileList[0].CreatedTime
		case <-done:
			goto END
		}
	L:
		logging.Info(fmt.Sprintf("finish watch %s", env.MountPath), nil)

	}
END:
	logging.Info("finish Watch goroutine", nil)
}
<file_sep>/app/file/watch.go
package file

import (
	"fmt"
	"io/fs"
	"path/filepath"
	"sort"
	"time"
)

func GetFileList(latestFileCreatedTime *time.Time, watchDirPath string) (Files, error) {
	var fileList Files
	err := filepath.Walk(watchDirPath, func(path string, info fs.FileInfo, err error) error {
		if err != nil {
			return err
		}
		if !info.IsDir() {
			if latestFileCreatedTime.Before(info.ModTime()) {
				file := NewFile(info)
				fileList = append(fileList, file)
			}
		}
		return nil
	})
	if err != nil {
		return nil, fmt.Errorf("cannot get file list in %v: %v", watchDirPath, err)
	}
	// 新しい順に並び替え
	sort.Slice(fileList, func(i, j int) bool {
		return fileList[i].CreatedTime.Unix() > fileList[j].CreatedTime.Unix()
	})
	for _, file := range fileList {
		fmt.Printf("%s\n", file.Name)
	}
	return fileList, nil
}
<file_sep>/app/server/response/CsvExecutionError.go
package response

type Error struct {
	LineNumber          int    `json:"lineNumber"`
	CustomerName        string `json:"customerName"`
	CustomerPhoneNumber string `json:"customerPhoneNumber"`
}

type CsvExectutionError struct {
	FileName string  `json:"fileName"`
	Errors   []Error `json:"errors"`
}
<file_sep>/app/csv/import.go
package csv

import (
	"fmt"
	"os"

	"golang.org/x/xerrors"

	"github.com/gocarina/gocsv"
)

type ReservationData struct {
	Notice string `csv:"xxxxxx"`

	sampleCode               string `csv:"sample"`
	`...`
}

func ImportFromLincoln(csvPath string) ([]*ReservationData, error) {
	file, err := os.OpenFile(csvPath, os.O_RDWR|os.O_CREATE, os.ModePerm)
	if err != nil {
		return nil, xerrors.New("cannot open csv file")
	}
	defer file.Close()

	reservations := []*ReservationData{}

	if err := gocsv.UnmarshalFile(file, &reservations); err != nil { 
		return nil, fmt.Errorf("cannot decode csv file to struct: %v\n", err)
	}

	return reservations, nil
}
<file_sep>/app/database/siteControl_test.go
package database

import (
	"context"
	"fmt"
	scCsv "site-controller-data-update-to-mysql/app/csv"
	"site-controller-data-update-to-mysql/config"
	"testing"
	"time"

	"github.com/golang/glog"
)

func TestAddReservationInfoToDB(t *testing.T) {
	SampleReservation := scCsv.ReservationData{
		Notice:                             "",
		SalesAgentCode:                     "",
		SalesAgentName:                     "",
		SalesAgentShopName:                 "",
		SalesAgentShopCode:                 0,
		SalesAgentContactPerson:            "",
		SalesAgentContactEmail:             "",
		SalesAgentContactPhoneNumber:       "",
		SalesAgentContactFax:               "",
		SalesAgentPlace:                    "",
		ReservatioinNumber:                 "",
		ReservatioinDate:                   "",
		NotificationNumber:                 0,
		NameKana:                           "",
		Name:                               "",
		StayDateFrom:                       "",
		CheckInTime:                        "",
		StayDateTo:                         "",
		StayDays:                           0,
		Transportation:                     "",
		PassengerCategory:                  "",
		NumberOfRooms:                      0,
		NumberOfGuests:                     0,
		NumberOfGuestsMale:                 0,
		NumberOfGuestsFemale:               0,
		NumberOfGuestsChildA:               0,
		NumberOfGuestsChildB:               0,
		NumberOfGuestsChildC:               0,
		NumberOfGuestsChildD:               0,
		NumberOfGuide:                      0,
		ParticipationForm:                  "",
		ProductName:                        "",
		ProductCode:                        "",
		StayCondition:                      "",
		PricePerUnit:                       "",
		PriceType:                          "",
		TotalPrice:                         0,
		TotalPriceOther:                    0,
		PhoneNumber:                        "",
		Email:                              "",
		PostalCode:                         "",
		HomeAddress:                        "",
		NumberOfGuestsAdult:                0,
		ReservationHolder:                  "",
		ReservationHolderKana:              "",
		ReservationHolderPhoneNumber:       "",
		ReservationHolderEmail:             "",
		ReservationHolderPostalCode:        "",
		ReservationHolderHomeAddress:       "",
		ReservationHolderCompanyName:       "",
		ReservationHolderCompanyDepartment: "",
		ReservationHolderMembershipNumber:  "",
		PointsDiscount:                     0,
		PointsAwarded:                      0,
		PointsAllocated:                    0,
		PaymentMethodName:                  "",
		UseOfDay1:                          0,
		RoomType1:                          "",
		NumberOfRoom1:                      0,
		RoomPrice1:                         0,
		PriceOfMale1:                       0,
		PriceOfFemale1:                     0,
		PriceOfChildA1:                     0,
		PriceOfChildB1:                     0,
		PriceOfChildC1:                     0,
		PriceOfChildD1:                     0,
		UseOfDay2:                          0,
		RoomType2:                          "",
		NumberOfRoom2:                      0,
		RoomPrice2:                         0,
		PriceOfMale2:                       0,
		PriceOfFemale2:                     0,
		PriceOfChildA2:                     0,
		PriceOfChildB2:                     0,
		PriceOfChildC2:                     0,
		PriceOfChildD2:                     0,
		UseOfDay3:                          0,
		RoomType3:                          "",
		NumberOfRoom3:                      0,
		RoomPrice3:                         0,
		PriceOfMale3:                       0,
		PriceOfFemale3:                     0,
		PriceOfChildA3:                     0,
		PriceOfChildB3:                     0,
		PriceOfChildC3:                     0,
		PriceOfChildD3:                     0,
		UseOfDay4:                          0,
		RoomType4:                          "",
		NumberOfRoom4:                      0,
		RoomPrice4:                         0,
		PriceOfMale4:                       0,
		PriceOfFemale4:                     0,
		PriceOfChildA4:                     0,
		PriceOfChildB4:                     0,
		PriceOfChildC4:                     0,
		PriceOfChildD4:                     0,
		UseOfDay5:                          0,
		RoomType5:                          "",
		NumberOfRoom5:                      0,
		RoomPrice5:                         0,
		PriceOfMale5:                       0,
		PriceOfFemale5:                     0,
		PriceOfChildA5:                     0,
		PriceOfChildB5:                     0,
		PriceOfChildC5:                     0,
		PriceOfChildD5:                     0,
		UseOfDay6:                          0,
		RoomType6:                          "",
		NumberOfRoom6:                      0,
		RoomPrice6:                         0,
		PriceOfMale6:                       0,
		PriceOfFemale6:                     0,
		PriceOfChildA6:                     0,
		PriceOfChildB6:                     0,
		PriceOfChildC6:                     0,
		PriceOfChildD6:                     0,
		UseOfDay7:                          0,
		RoomType7:                          "",
		NumberOfRoom7:                      0,
		RoomPrice7:                         0,
		PriceOfMale7:                       0,
		PriceOfFemale7:                     0,
		PriceOfChildA7:                     0,
		PriceOfChildB7:                     0,
		PriceOfChildC7:                     0,
		PriceOfChildD7:                     0,
		UseOfDay8:                          0,
		RoomType8:                          "",
		NumberOfRoom8:                      0,
		RoomPrice8:                         0,
		PriceOfMale8:                       0,
		PriceOfFemale8:                     0,
		PriceOfChildA8:                     0,
		PriceOfChildB8:                     0,
		PriceOfChildC8:                     0,
		PriceOfChildD8:                     0,
		UseOfDay9:                          0,
		RoomType9:                          "",
		NumberOfRoom9:                      0,
		RoomPrice9:                         0,
		PriceOfMale9:                       0,
		PriceOfFemale9:                     0,
		PriceOfChildA9:                     0,
		PriceOfChildB9:                     0,
		PriceOfChildC9:                     0,
		PriceOfChildD9:                     0,
		UseOfDay10:                         0,
		RoomType10:                         "",
		NumberOfRoom10:                     0,
		RoomPrice10:                        0,
		PriceOfMale10:                      0,
		PriceOfFemale10:                    0,
		PriceOfChildA10:                    0,
		PriceOfChildB10:                    0,
		PriceOfChildC10:                    0,
		PriceOfChildD10:                    0,
		UseOfDay11:                         0,
		RoomType11:                         "",
		NumberOfRoom11:                     0,
		RoomPrice11:                        0,
		PriceOfMale11:                      0,
		PriceOfFemale11:                    0,
		PriceOfChildA11:                    0,
		PriceOfChildB11:                    0,
		PriceOfChildC11:                    0,
		PriceOfChildD11:                    0,
		UseOfDay12:                         0,
		RoomType12:                         "",
		NumberOfRoom12:                     0,
		RoomPrice12:                        0,
		PriceOfMale12:                      0,
		PriceOfFemale12:                    0,
		PriceOfChildA12:                    0,
		PriceOfChildB12:                    0,
		PriceOfChildC12:                    0,
		PriceOfChildD12:                    0,
		OptionName1:                        "",
		OptionPrice1:                       0,
		OptionName2:                        "",
		OptionPrice2:                       0,
		NumberOfOptions2:                   0,
		OptionName3:                        "",
		OptionPrice3:                       0,
		NumberOfOptions3:                   0,
		OptionName4:                        "",
		OptionPrice4:                       0,
		NumberOfOptions4:                   0,
		OptionName5:                        "",
		OptionPrice5:                       0,
		NumberOfOptions5:                   0,
		Notes:                              "",
		LincolnSalesAgentCode:              0,
		InitSalesAgentCode:                 0,
	}

	env := &config.MysqlEnv{
		User:     "xxxx",
		Host:     "xxxx",
		Password: "<PASSWORD>",
		Port:     "xxxx",
	}
	db, err := NewDatabase(env)
	if err != nil {
		glog.Errorf("failed to create database: %v", err)
		return
	}

	ctx, cancel := context.WithCancel(context.Background())
	defer cancel()
	tx, _ := db.DB.Begin()

	t.Run("test", func(t *testing.T) {
		if _, err := db.addReservationInfoToDB(&SampleReservation, tx, ctx); err != nil {
			t.Errorf("%v", err)
		}
	})
}

func TestTransactionReservationInfo(t *testing.T) {
	SampleReservation1 := scCsv.ReservationData{
		Notice:                             "予約",
		SalesAgentCode:                     "",
		SalesAgentName:                     "",
		SalesAgentShopName:                 "",
		SalesAgentShopCode:                 0,
		SalesAgentContactPerson:            "",
		SalesAgentContactEmail:             "",
		SalesAgentContactPhoneNumber:       "",
		SalesAgentContactFax:               "",
		SalesAgentPlace:                    "",
		ReservatioinNumber:                 "",
		ReservatioinDate:                   "20060102",
		NotificationNumber:                 0,
		NameKana:                           "テスト１",
		Name:                               "テスト１",
		StayDateFrom:                       "20210701",
		CheckInTime:                        "15:03",
		StayDateTo:                         "20210703",
		StayDays:                           0,
		Transportation:                     "",
		PassengerCategory:                  "",
		NumberOfRooms:                      0,
		NumberOfGuests:                     0,
		NumberOfGuestsMale:                 0,
		NumberOfGuestsFemale:               0,
		NumberOfGuestsChildA:               0,
		NumberOfGuestsChildB:               0,
		NumberOfGuestsChildC:               0,
		NumberOfGuestsChildD:               0,
		NumberOfGuide:                      0,
		ParticipationForm:                  "",
		ProductName:                        "",
		ProductCode:                        "",
		StayCondition:                      "",
		PricePerUnit:                       "",
		PriceType:                          "",
		TotalPrice:                         0,
		TotalPriceOther:                    0,
		PhoneNumber:                        "1111111",
		Email:                              "",
		PostalCode:                         "",
		HomeAddress:                        "",
		NumberOfGuestsAdult:                0,
		ReservationHolder:                  "",
		ReservationHolderKana:              "",
		ReservationHolderPhoneNumber:       "",
		ReservationHolderEmail:             "",
		ReservationHolderPostalCode:        "",
		ReservationHolderHomeAddress:       "",
		ReservationHolderCompanyName:       "",
		ReservationHolderCompanyDepartment: "",
		ReservationHolderMembershipNumber:  "",
		PointsDiscount:                     0,
		PointsAwarded:                      0,
		PointsAllocated:                    0,
		PaymentMethodName:                  "",
		UseOfDay1:                          0,
		RoomType1:                          "",
		NumberOfRoom1:                      0,
		RoomPrice1:                         0,
		PriceOfMale1:                       0,
		PriceOfFemale1:                     0,
		PriceOfChildA1:                     0,
		PriceOfChildB1:                     0,
		PriceOfChildC1:                     0,
		PriceOfChildD1:                     0,
		UseOfDay2:                          0,
		RoomType2:                          "",
		NumberOfRoom2:                      0,
		RoomPrice2:                         0,
		PriceOfMale2:                       0,
		PriceOfFemale2:                     0,
		PriceOfChildA2:                     0,
		PriceOfChildB2:                     0,
		PriceOfChildC2:                     0,
		PriceOfChildD2:                     0,
		UseOfDay3:                          0,
		RoomType3:                          "",
		NumberOfRoom3:                      0,
		RoomPrice3:                         0,
		PriceOfMale3:                       0,
		PriceOfFemale3:                     0,
		PriceOfChildA3:                     0,
		PriceOfChildB3:                     0,
		PriceOfChildC3:                     0,
		PriceOfChildD3:                     0,
		UseOfDay4:                          0,
		RoomType4:                          "",
		NumberOfRoom4:                      0,
		RoomPrice4:                         0,
		PriceOfMale4:                       0,
		PriceOfFemale4:                     0,
		PriceOfChildA4:                     0,
		PriceOfChildB4:                     0,
		PriceOfChildC4:                     0,
		PriceOfChildD4:                     0,
		UseOfDay5:                          0,
		RoomType5:                          "",
		NumberOfRoom5:                      0,
		RoomPrice5:                         0,
		PriceOfMale5:                       0,
		PriceOfFemale5:                     0,
		PriceOfChildA5:                     0,
		PriceOfChildB5:                     0,
		PriceOfChildC5:                     0,
		PriceOfChildD5:                     0,
		UseOfDay6:                          0,
		RoomType6:                          "",
		NumberOfRoom6:                      0,
		RoomPrice6:                         0,
		PriceOfMale6:                       0,
		PriceOfFemale6:                     0,
		PriceOfChildA6:                     0,
		PriceOfChildB6:                     0,
		PriceOfChildC6:                     0,
		PriceOfChildD6:                     0,
		UseOfDay7:                          0,
		RoomType7:                          "",
		NumberOfRoom7:                      0,
		RoomPrice7:                         0,
		PriceOfMale7:                       0,
		PriceOfFemale7:                     0,
		PriceOfChildA7:                     0,
		PriceOfChildB7:                     0,
		PriceOfChildC7:                     0,
		PriceOfChildD7:                     0,
		UseOfDay8:                          0,
		RoomType8:                          "",
		NumberOfRoom8:                      0,
		RoomPrice8:                         0,
		PriceOfMale8:                       0,
		PriceOfFemale8:                     0,
		PriceOfChildA8:                     0,
		PriceOfChildB8:                     0,
		PriceOfChildC8:                     0,
		PriceOfChildD8:                     0,
		UseOfDay9:                          0,
		RoomType9:                          "",
		NumberOfRoom9:                      0,
		RoomPrice9:                         0,
		PriceOfMale9:                       0,
		PriceOfFemale9:                     0,
		PriceOfChildA9:                     0,
		PriceOfChildB9:                     0,
		PriceOfChildC9:                     0,
		PriceOfChildD9:                     0,
		UseOfDay10:                         0,
		RoomType10:                         "",
		NumberOfRoom10:                     0,
		RoomPrice10:                        0,
		PriceOfMale10:                      0,
		PriceOfFemale10:                    0,
		PriceOfChildA10:                    0,
		PriceOfChildB10:                    0,
		PriceOfChildC10:                    0,
		PriceOfChildD10:                    0,
		UseOfDay11:                         0,
		RoomType11:                         "",
		NumberOfRoom11:                     0,
		RoomPrice11:                        0,
		PriceOfMale11:                      0,
		PriceOfFemale11:                    0,
		PriceOfChildA11:                    0,
		PriceOfChildB11:                    0,
		PriceOfChildC11:                    0,
		PriceOfChildD11:                    0,
		UseOfDay12:                         0,
		RoomType12:                         "",
		NumberOfRoom12:                     0,
		RoomPrice12:                        0,
		PriceOfMale12:                      0,
		PriceOfFemale12:                    0,
		PriceOfChildA12:                    0,
		PriceOfChildB12:                    0,
		PriceOfChildC12:                    0,
		PriceOfChildD12:                    0,
		OptionName1:                        "",
		OptionPrice1:                       0,
		OptionName2:                        "",
		OptionPrice2:                       0,
		NumberOfOptions2:                   0,
		OptionName3:                        "",
		OptionPrice3:                       0,
		NumberOfOptions3:                   0,
		OptionName4:                        "",
		OptionPrice4:                       0,
		NumberOfOptions4:                   0,
		OptionName5:                        "",
		OptionPrice5:                       0,
		NumberOfOptions5:                   0,
		Notes:                              "",
		LincolnSalesAgentCode:              0,
		InitSalesAgentCode:                 0,
	}
	SampleReservation2 := scCsv.ReservationData{
		Notice:                             "予約",
		SalesAgentCode:                     "",
		SalesAgentName:                     "",
		SalesAgentShopName:                 "",
		SalesAgentShopCode:                 0,
		SalesAgentContactPerson:            "",
		SalesAgentContactEmail:             "",
		SalesAgentContactPhoneNumber:       "",
		SalesAgentContactFax:               "",
		SalesAgentPlace:                    "",
		ReservatioinNumber:                 "",
		ReservatioinDate:                   "20060102",
		NotificationNumber:                 0,
		NameKana:                           "テスト２",
		Name:                               "テスト２",
		StayDateFrom:                       "20060102",
		CheckInTime:                        "15:04",
		StayDateTo:                         "20060103",
		StayDays:                           0,
		Transportation:                     "",
		PassengerCategory:                  "",
		NumberOfRooms:                      0,
		NumberOfGuests:                     0,
		NumberOfGuestsMale:                 0,
		NumberOfGuestsFemale:               0,
		NumberOfGuestsChildA:               0,
		NumberOfGuestsChildB:               0,
		NumberOfGuestsChildC:               0,
		NumberOfGuestsChildD:               0,
		NumberOfGuide:                      0,
		ParticipationForm:                  "",
		ProductName:                        "",
		ProductCode:                        "",
		StayCondition:                      "",
		PricePerUnit:                       "",
		PriceType:                          "",
		TotalPrice:                         0,
		TotalPriceOther:                    0,
		PhoneNumber:                        "2222222",
		Email:                              "",
		PostalCode:                         "",
		HomeAddress:                        "",
		NumberOfGuestsAdult:                0,
		ReservationHolder:                  "",
		ReservationHolderKana:              "",
		ReservationHolderPhoneNumber:       "",
		ReservationHolderEmail:             "",
		ReservationHolderPostalCode:        "",
		ReservationHolderHomeAddress:       "",
		ReservationHolderCompanyName:       "",
		ReservationHolderCompanyDepartment: "",
		ReservationHolderMembershipNumber:  "",
		PointsDiscount:                     0,
		PointsAwarded:                      0,
		PointsAllocated:                    0,
		PaymentMethodName:                  "",
		UseOfDay1:                          0,
		RoomType1:                          "",
		NumberOfRoom1:                      0,
		RoomPrice1:                         0,
		PriceOfMale1:                       0,
		PriceOfFemale1:                     0,
		PriceOfChildA1:                     0,
		PriceOfChildB1:                     0,
		PriceOfChildC1:                     0,
		PriceOfChildD1:                     0,
		UseOfDay2:                          0,
		RoomType2:                          "",
		NumberOfRoom2:                      0,
		RoomPrice2:                         0,
		PriceOfMale2:                       0,
		PriceOfFemale2:                     0,
		PriceOfChildA2:                     0,
		PriceOfChildB2:                     0,
		PriceOfChildC2:                     0,
		PriceOfChildD2:                     0,
		UseOfDay3:                          0,
		RoomType3:                          "",
		NumberOfRoom3:                      0,
		RoomPrice3:                         0,
		PriceOfMale3:                       0,
		PriceOfFemale3:                     0,
		PriceOfChildA3:                     0,
		PriceOfChildB3:                     0,
		PriceOfChildC3:                     0,
		PriceOfChildD3:                     0,
		UseOfDay4:                          0,
		RoomType4:                          "",
		NumberOfRoom4:                      0,
		RoomPrice4:                         0,
		PriceOfMale4:                       0,
		PriceOfFemale4:                     0,
		PriceOfChildA4:                     0,
		PriceOfChildB4:                     0,
		PriceOfChildC4:                     0,
		PriceOfChildD4:                     0,
		UseOfDay5:                          0,
		RoomType5:                          "",
		NumberOfRoom5:                      0,
		RoomPrice5:                         0,
		PriceOfMale5:                       0,
		PriceOfFemale5:                     0,
		PriceOfChildA5:                     0,
		PriceOfChildB5:                     0,
		PriceOfChildC5:                     0,
		PriceOfChildD5:                     0,
		UseOfDay6:                          0,
		RoomType6:                          "",
		NumberOfRoom6:                      0,
		RoomPrice6:                         0,
		PriceOfMale6:                       0,
		PriceOfFemale6:                     0,
		PriceOfChildA6:                     0,
		PriceOfChildB6:                     0,
		PriceOfChildC6:                     0,
		PriceOfChildD6:                     0,
		UseOfDay7:                          0,
		RoomType7:                          "",
		NumberOfRoom7:                      0,
		RoomPrice7:                         0,
		PriceOfMale7:                       0,
		PriceOfFemale7:                     0,
		PriceOfChildA7:                     0,
		PriceOfChildB7:                     0,
		PriceOfChildC7:                     0,
		PriceOfChildD7:                     0,
		UseOfDay8:                          0,
		RoomType8:                          "",
		NumberOfRoom8:                      0,
		RoomPrice8:                         0,
		PriceOfMale8:                       0,
		PriceOfFemale8:                     0,
		PriceOfChildA8:                     0,
		PriceOfChildB8:                     0,
		PriceOfChildC8:                     0,
		PriceOfChildD8:                     0,
		UseOfDay9:                          0,
		RoomType9:                          "",
		NumberOfRoom9:                      0,
		RoomPrice9:                         0,
		PriceOfMale9:                       0,
		PriceOfFemale9:                     0,
		PriceOfChildA9:                     0,
		PriceOfChildB9:                     0,
		PriceOfChildC9:                     0,
		PriceOfChildD9:                     0,
		UseOfDay10:                         0,
		RoomType10:                         "",
		NumberOfRoom10:                     0,
		RoomPrice10:                        0,
		PriceOfMale10:                      0,
		PriceOfFemale10:                    0,
		PriceOfChildA10:                    0,
		PriceOfChildB10:                    0,
		PriceOfChildC10:                    0,
		PriceOfChildD10:                    0,
		UseOfDay11:                         0,
		RoomType11:                         "",
		NumberOfRoom11:                     0,
		RoomPrice11:                        0,
		PriceOfMale11:                      0,
		PriceOfFemale11:                    0,
		PriceOfChildA11:                    0,
		PriceOfChildB11:                    0,
		PriceOfChildC11:                    0,
		PriceOfChildD11:                    0,
		UseOfDay12:                         0,
		RoomType12:                         "",
		NumberOfRoom12:                     0,
		RoomPrice12:                        0,
		PriceOfMale12:                      0,
		PriceOfFemale12:                    0,
		PriceOfChildA12:                    0,
		PriceOfChildB12:                    0,
		PriceOfChildC12:                    0,
		PriceOfChildD12:                    0,
		OptionName1:                        "",
		OptionPrice1:                       0,
		OptionName2:                        "",
		OptionPrice2:                       0,
		NumberOfOptions2:                   0,
		OptionName3:                        "",
		OptionPrice3:                       0,
		NumberOfOptions3:                   0,
		OptionName4:                        "",
		OptionPrice4:                       0,
		NumberOfOptions4:                   0,
		OptionName5:                        "",
		OptionPrice5:                       0,
		NumberOfOptions5:                   0,
		Notes:                              "",
		LincolnSalesAgentCode:              0,
		InitSalesAgentCode:                 0,
	}
	SampleReservation3 := scCsv.ReservationData{
		Notice:                             "取消",
		SalesAgentCode:                     "",
		SalesAgentName:                     "",
		SalesAgentShopName:                 "",
		SalesAgentShopCode:                 0,
		SalesAgentContactPerson:            "",
		SalesAgentContactEmail:             "",
		SalesAgentContactPhoneNumber:       "",
		SalesAgentContactFax:               "",
		SalesAgentPlace:                    "",
		ReservatioinNumber:                 "",
		ReservatioinDate:                   "20060102",
		NotificationNumber:                 0,
		NameKana:                           "テスト２",
		Name:                               "テスト２",
		StayDateFrom:                       "20060102",
		CheckInTime:                        "15:04",
		StayDateTo:                         "20060103",
		StayDays:                           0,
		Transportation:                     "",
		PassengerCategory:                  "",
		NumberOfRooms:                      0,
		NumberOfGuests:                     0,
		NumberOfGuestsMale:                 0,
		NumberOfGuestsFemale:               0,
		NumberOfGuestsChildA:               0,
		NumberOfGuestsChildB:               0,
		NumberOfGuestsChildC:               0,
		NumberOfGuestsChildD:               0,
		NumberOfGuide:                      0,
		ParticipationForm:                  "",
		ProductName:                        "",
		ProductCode:                        "",
		StayCondition:                      "",
		PricePerUnit:                       "",
		PriceType:                          "",
		TotalPrice:                         0,
		TotalPriceOther:                    0,
		PhoneNumber:                        "2222222",
		Email:                              "",
		PostalCode:                         "",
		HomeAddress:                        "",
		NumberOfGuestsAdult:                0,
		ReservationHolder:                  "",
		ReservationHolderKana:              "",
		ReservationHolderPhoneNumber:       "",
		ReservationHolderEmail:             "",
		ReservationHolderPostalCode:        "",
		ReservationHolderHomeAddress:       "",
		ReservationHolderCompanyName:       "",
		ReservationHolderCompanyDepartment: "",
		ReservationHolderMembershipNumber:  "",
		PointsDiscount:                     0,
		PointsAwarded:                      0,
		PointsAllocated:                    0,
		PaymentMethodName:                  "",
		UseOfDay1:                          0,
		RoomType1:                          "",
		NumberOfRoom1:                      0,
		RoomPrice1:                         0,
		PriceOfMale1:                       0,
		PriceOfFemale1:                     0,
		PriceOfChildA1:                     0,
		PriceOfChildB1:                     0,
		PriceOfChildC1:                     0,
		PriceOfChildD1:                     0,
		UseOfDay2:                          0,
		RoomType2:                          "",
		NumberOfRoom2:                      0,
		RoomPrice2:                         0,
		PriceOfMale2:                       0,
		PriceOfFemale2:                     0,
		PriceOfChildA2:                     0,
		PriceOfChildB2:                     0,
		PriceOfChildC2:                     0,
		PriceOfChildD2:                     0,
		UseOfDay3:                          0,
		RoomType3:                          "",
		NumberOfRoom3:                      0,
		RoomPrice3:                         0,
		PriceOfMale3:                       0,
		PriceOfFemale3:                     0,
		PriceOfChildA3:                     0,
		PriceOfChildB3:                     0,
		PriceOfChildC3:                     0,
		PriceOfChildD3:                     0,
		UseOfDay4:                          0,
		RoomType4:                          "",
		NumberOfRoom4:                      0,
		RoomPrice4:                         0,
		PriceOfMale4:                       0,
		PriceOfFemale4:                     0,
		PriceOfChildA4:                     0,
		PriceOfChildB4:                     0,
		PriceOfChildC4:                     0,
		PriceOfChildD4:                     0,
		UseOfDay5:                          0,
		RoomType5:                          "",
		NumberOfRoom5:                      0,
		RoomPrice5:                         0,
		PriceOfMale5:                       0,
		PriceOfFemale5:                     0,
		PriceOfChildA5:                     0,
		PriceOfChildB5:                     0,
		PriceOfChildC5:                     0,
		PriceOfChildD5:                     0,
		UseOfDay6:                          0,
		RoomType6:                          "",
		NumberOfRoom6:                      0,
		RoomPrice6:                         0,
		PriceOfMale6:                       0,
		PriceOfFemale6:                     0,
		PriceOfChildA6:                     0,
		PriceOfChildB6:                     0,
		PriceOfChildC6:                     0,
		PriceOfChildD6:                     0,
		UseOfDay7:                          0,
		RoomType7:                          "",
		NumberOfRoom7:                      0,
		RoomPrice7:                         0,
		PriceOfMale7:                       0,
		PriceOfFemale7:                     0,
		PriceOfChildA7:                     0,
		PriceOfChildB7:                     0,
		PriceOfChildC7:                     0,
		PriceOfChildD7:                     0,
		UseOfDay8:                          0,
		RoomType8:                          "",
		NumberOfRoom8:                      0,
		RoomPrice8:                         0,
		PriceOfMale8:                       0,
		PriceOfFemale8:                     0,
		PriceOfChildA8:                     0,
		PriceOfChildB8:                     0,
		PriceOfChildC8:                     0,
		PriceOfChildD8:                     0,
		UseOfDay9:                          0,
		RoomType9:                          "",
		NumberOfRoom9:                      0,
		RoomPrice9:                         0,
		PriceOfMale9:                       0,
		PriceOfFemale9:                     0,
		PriceOfChildA9:                     0,
		PriceOfChildB9:                     0,
		PriceOfChildC9:                     0,
		PriceOfChildD9:                     0,
		UseOfDay10:                         0,
		RoomType10:                         "",
		NumberOfRoom10:                     0,
		RoomPrice10:                        0,
		PriceOfMale10:                      0,
		PriceOfFemale10:                    0,
		PriceOfChildA10:                    0,
		PriceOfChildB10:                    0,
		PriceOfChildC10:                    0,
		PriceOfChildD10:                    0,
		UseOfDay11:                         0,
		RoomType11:                         "",
		NumberOfRoom11:                     0,
		RoomPrice11:                        0,
		PriceOfMale11:                      0,
		PriceOfFemale11:                    0,
		PriceOfChildA11:                    0,
		PriceOfChildB11:                    0,
		PriceOfChildC11:                    0,
		PriceOfChildD11:                    0,
		UseOfDay12:                         0,
		RoomType12:                         "",
		NumberOfRoom12:                     0,
		RoomPrice12:                        0,
		PriceOfMale12:                      0,
		PriceOfFemale12:                    0,
		PriceOfChildA12:                    0,
		PriceOfChildB12:                    0,
		PriceOfChildC12:                    0,
		PriceOfChildD12:                    0,
		OptionName1:                        "",
		OptionPrice1:                       0,
		OptionName2:                        "",
		OptionPrice2:                       0,
		NumberOfOptions2:                   0,
		OptionName3:                        "",
		OptionPrice3:                       0,
		NumberOfOptions3:                   0,
		OptionName4:                        "",
		OptionPrice4:                       0,
		NumberOfOptions4:                   0,
		OptionName5:                        "",
		OptionPrice5:                       0,
		NumberOfOptions5:                   0,
		Notes:                              "",
		LincolnSalesAgentCode:              0,
		InitSalesAgentCode:                 0,
	}

	SampleReservations := []*scCsv.ReservationData{
		&SampleReservation1,
		&SampleReservation2,
		&SampleReservation3,
	}

	env := &config.MysqlEnv{
		User:     "xxxx",
		Host:     "xxxx",
		Password: "<PASSWORD>",
		Port:     "xxxx",
	}
	db, err := NewDatabase(env)
	if err != nil {
		t.Errorf("failed to create database: %v", err)
		return
	}

	ctx, cancel := context.WithCancel(context.Background())
	defer cancel()

	t.Run("test", func(t *testing.T) {
		errors, err := db.TransactionReservationInfo(SampleReservations, ctx)
		if err != nil {
			t.Errorf("failed to process transaction: %v", err)
		}

		if len(errors) != 0 {
			for i, err := range errors {
				t.Errorf("%dth row error: %v", i, err)
			}
		}
	})
}

func TestTransaction(t *testing.T) {
	ctx, cancel := context.WithCancel(context.Background())
	defer cancel()

	env := &config.MysqlEnv{
		User:     "xxxx",
		Host:     "xxxx",
		Password: "<PASSWORD>",
		Port:     "xxxx",
	}
	db, err := NewDatabase(env)
	if err != nil {
		t.Errorf("failed to create database: %v", err)
		return
	}
	var id int

	SampleReservationError1 := []*scCsv.ReservationData{
		{
			Notice:                             "予約",
			SalesAgentCode:                     "",
			SalesAgentName:                     "",
			SalesAgentShopName:                 "テストトラベル",
			SalesAgentShopCode:                 0,
			SalesAgentContactPerson:            "",
			SalesAgentContactEmail:             "",
			SalesAgentContactPhoneNumber:       "",
			SalesAgentContactFax:               "",
			SalesAgentPlace:                    "",
			ReservatioinNumber:                 "",
			ReservatioinDate:                   "20060102",
			NotificationNumber:                 0,
			NameKana:                           "テスト１",
			Name:                               "テスト１",
			StayDateFrom:                       "20210701",
			CheckInTime:                        "15:03",
			StayDateTo:                         "20210703",
			StayDays:                           2,
			Transportation:                     "",
			PassengerCategory:                  "",
			NumberOfRooms:                      1,
			NumberOfGuests:                     3,
			NumberOfGuestsMale:                 1,
			NumberOfGuestsFemale:               1,
			NumberOfGuestsChildA:               1,
			NumberOfGuestsChildB:               0,
			NumberOfGuestsChildC:               0,
			NumberOfGuestsChildD:               0,
			NumberOfGuide:                      0,
			ParticipationForm:                  "",
			ProductName:                        "テストプラン１",
			ProductCode:                        "",
			StayCondition:                      "",
			PricePerUnit:                       "",
			PriceType:                          "",
			TotalPrice:                         0,
			TotalPriceOther:                    0,
			PhoneNumber:                        "1111111",
			Email:                              "",
			PostalCode:                         "111-2222",
			HomeAddress:                        "テスト都テスト区テスト111",
			NumberOfGuestsAdult:                0,
			ReservationHolder:                  "テスト１",
			ReservationHolderKana:              "テスト１",
			ReservationHolderPhoneNumber:       "00011112222",
			ReservationHolderEmail:             "",
			ReservationHolderPostalCode:        "111-2222",
			ReservationHolderHomeAddress:       "テスト都テスト区テスト111",
			ReservationHolderCompanyName:       "",
			ReservationHolderCompanyDepartment: "",
			ReservationHolderMembershipNumber:  "",
			PointsDiscount:                     0,
			PointsAwarded:                      0,
			PointsAllocated:                    0,
			PaymentMethodName:                  "",
			UseOfDay1:                          0,
			RoomType1:                          "",
			NumberOfRoom1:                      0,
			RoomPrice1:                         0,
			PriceOfMale1:                       0,
			PriceOfFemale1:                     0,
			PriceOfChildA1:                     0,
			PriceOfChildB1:                     0,
			PriceOfChildC1:                     0,
			PriceOfChildD1:                     0,
			UseOfDay2:                          0,
			RoomType2:                          "",
			NumberOfRoom2:                      0,
			RoomPrice2:                         0,
			PriceOfMale2:                       0,
			PriceOfFemale2:                     0,
			PriceOfChildA2:                     0,
			PriceOfChildB2:                     0,
			PriceOfChildC2:                     0,
			PriceOfChildD2:                     0,
			UseOfDay3:                          0,
			RoomType3:                          "",
			NumberOfRoom3:                      0,
			RoomPrice3:                         0,
			PriceOfMale3:                       0,
			PriceOfFemale3:                     0,
			PriceOfChildA3:                     0,
			PriceOfChildB3:                     0,
			PriceOfChildC3:                     0,
			PriceOfChildD3:                     0,
			UseOfDay4:                          0,
			RoomType4:                          "",
			NumberOfRoom4:                      0,
			RoomPrice4:                         0,
			PriceOfMale4:                       0,
			PriceOfFemale4:                     0,
			PriceOfChildA4:                     0,
			PriceOfChildB4:                     0,
			PriceOfChildC4:                     0,
			PriceOfChildD4:                     0,
			UseOfDay5:                          0,
			RoomType5:                          "",
			NumberOfRoom5:                      0,
			RoomPrice5:                         0,
			PriceOfMale5:                       0,
			PriceOfFemale5:                     0,
			PriceOfChildA5:                     0,
			PriceOfChildB5:                     0,
			PriceOfChildC5:                     0,
			PriceOfChildD5:                     0,
			UseOfDay6:                          0,
			RoomType6:                          "",
			NumberOfRoom6:                      0,
			RoomPrice6:                         0,
			PriceOfMale6:                       0,
			PriceOfFemale6:                     0,
			PriceOfChildA6:                     0,
			PriceOfChildB6:                     0,
			PriceOfChildC6:                     0,
			PriceOfChildD6:                     0,
			UseOfDay7:                          0,
			RoomType7:                          "",
			NumberOfRoom7:                      0,
			RoomPrice7:                         0,
			PriceOfMale7:                       0,
			PriceOfFemale7:                     0,
			PriceOfChildA7:                     0,
			PriceOfChildB7:                     0,
			PriceOfChildC7:                     0,
			PriceOfChildD7:                     0,
			UseOfDay8:                          0,
			RoomType8:                          "",
			NumberOfRoom8:                      0,
			RoomPrice8:                         0,
			PriceOfMale8:                       0,
			PriceOfFemale8:                     0,
			PriceOfChildA8:                     0,
			PriceOfChildB8:                     0,
			PriceOfChildC8:                     0,
			PriceOfChildD8:                     0,
			UseOfDay9:                          0,
			RoomType9:                          "",
			NumberOfRoom9:                      0,
			RoomPrice9:                         0,
			PriceOfMale9:                       0,
			PriceOfFemale9:                     0,
			PriceOfChildA9:                     0,
			PriceOfChildB9:                     0,
			PriceOfChildC9:                     0,
			PriceOfChildD9:                     0,
			UseOfDay10:                         0,
			RoomType10:                         "",
			NumberOfRoom10:                     0,
			RoomPrice10:                        0,
			PriceOfMale10:                      0,
			PriceOfFemale10:                    0,
			PriceOfChildA10:                    0,
			PriceOfChildB10:                    0,
			PriceOfChildC10:                    0,
			PriceOfChildD10:                    0,
			UseOfDay11:                         0,
			RoomType11:                         "",
			NumberOfRoom11:                     0,
			RoomPrice11:                        0,
			PriceOfMale11:                      0,
			PriceOfFemale11:                    0,
			PriceOfChildA11:                    0,
			PriceOfChildB11:                    0,
			PriceOfChildC11:                    0,
			PriceOfChildD11:                    0,
			UseOfDay12:                         0,
			RoomType12:                         "",
			NumberOfRoom12:                     0,
			RoomPrice12:                        0,
			PriceOfMale12:                      0,
			PriceOfFemale12:                    0,
			PriceOfChildA12:                    0,
			PriceOfChildB12:                    0,
			PriceOfChildC12:                    0,
			PriceOfChildD12:                    0,
			OptionName1:                        "",
			OptionPrice1:                       0,
			OptionName2:                        "",
			OptionPrice2:                       0,
			NumberOfOptions2:                   0,
			OptionName3:                        "",
			OptionPrice3:                       0,
			NumberOfOptions3:                   0,
			OptionName4:                        "",
			OptionPrice4:                       0,
			NumberOfOptions4:                   0,
			OptionName5:                        "",
			OptionPrice5:                       0,
			NumberOfOptions5:                   0,
			Notes:                              "",
			LincolnSalesAgentCode:              0,
			InitSalesAgentCode:                 0,
		},
		{
			Notice:                             "取消",
			SalesAgentCode:                     "",
			SalesAgentName:                     "",
			SalesAgentShopName:                 "テストトラベル",
			SalesAgentShopCode:                 0,
			SalesAgentContactPerson:            "",
			SalesAgentContactEmail:             "",
			SalesAgentContactPhoneNumber:       "",
			SalesAgentContactFax:               "",
			SalesAgentPlace:                    "",
			ReservatioinNumber:                 "",
			ReservatioinDate:                   "20060102",
			NotificationNumber:                 0,
			NameKana:                           "テスト２",
			Name:                               "テスト２",
			StayDateFrom:                       "20210701",
			CheckInTime:                        "15:03",
			StayDateTo:                         "20210703",
			StayDays:                           2,
			Transportation:                     "",
			PassengerCategory:                  "",
			NumberOfRooms:                      1,
			NumberOfGuests:                     3,
			NumberOfGuestsMale:                 1,
			NumberOfGuestsFemale:               1,
			NumberOfGuestsChildA:               1,
			NumberOfGuestsChildB:               0,
			NumberOfGuestsChildC:               0,
			NumberOfGuestsChildD:               0,
			NumberOfGuide:                      0,
			ParticipationForm:                  "",
			ProductName:                        "テストプラン２",
			ProductCode:                        "",
			StayCondition:                      "",
			PricePerUnit:                       "",
			PriceType:                          "",
			TotalPrice:                         0,
			TotalPriceOther:                    0,
			PhoneNumber:                        "2222222222222",
			Email:                              "",
			PostalCode:                         "111-2222",
			HomeAddress:                        "テスト都テスト区テスト111",
			NumberOfGuestsAdult:                0,
			ReservationHolder:                  "テスト２",
			ReservationHolderKana:              "テスト２",
			ReservationHolderPhoneNumber:       "00011112222",
			ReservationHolderEmail:             "",
			ReservationHolderPostalCode:        "111-2222",
			ReservationHolderHomeAddress:       "テスト都テスト区テスト111",
			ReservationHolderCompanyName:       "",
			ReservationHolderCompanyDepartment: "",
			ReservationHolderMembershipNumber:  "",
			PointsDiscount:                     0,
			PointsAwarded:                      0,
			PointsAllocated:                    0,
			PaymentMethodName:                  "",
			UseOfDay1:                          0,
			RoomType1:                          "",
			NumberOfRoom1:                      0,
			RoomPrice1:                         0,
			PriceOfMale1:                       0,
			PriceOfFemale1:                     0,
			PriceOfChildA1:                     0,
			PriceOfChildB1:                     0,
			PriceOfChildC1:                     0,
			PriceOfChildD1:                     0,
			UseOfDay2:                          0,
			RoomType2:                          "",
			NumberOfRoom2:                      0,
			RoomPrice2:                         0,
			PriceOfMale2:                       0,
			PriceOfFemale2:                     0,
			PriceOfChildA2:                     0,
			PriceOfChildB2:                     0,
			PriceOfChildC2:                     0,
			PriceOfChildD2:                     0,
			UseOfDay3:                          0,
			RoomType3:                          "",
			NumberOfRoom3:                      0,
			RoomPrice3:                         0,
			PriceOfMale3:                       0,
			PriceOfFemale3:                     0,
			PriceOfChildA3:                     0,
			PriceOfChildB3:                     0,
			PriceOfChildC3:                     0,
			PriceOfChildD3:                     0,
			UseOfDay4:                          0,
			RoomType4:                          "",
			NumberOfRoom4:                      0,
			RoomPrice4:                         0,
			PriceOfMale4:                       0,
			PriceOfFemale4:                     0,
			PriceOfChildA4:                     0,
			PriceOfChildB4:                     0,
			PriceOfChildC4:                     0,
			PriceOfChildD4:                     0,
			UseOfDay5:                          0,
			RoomType5:                          "",
			NumberOfRoom5:                      0,
			RoomPrice5:                         0,
			PriceOfMale5:                       0,
			PriceOfFemale5:                     0,
			PriceOfChildA5:                     0,
			PriceOfChildB5:                     0,
			PriceOfChildC5:                     0,
			PriceOfChildD5:                     0,
			UseOfDay6:                          0,
			RoomType6:                          "",
			NumberOfRoom6:                      0,
			RoomPrice6:                         0,
			PriceOfMale6:                       0,
			PriceOfFemale6:                     0,
			PriceOfChildA6:                     0,
			PriceOfChildB6:                     0,
			PriceOfChildC6:                     0,
			PriceOfChildD6:                     0,
			UseOfDay7:                          0,
			RoomType7:                          "",
			NumberOfRoom7:                      0,
			RoomPrice7:                         0,
			PriceOfMale7:                       0,
			PriceOfFemale7:                     0,
			PriceOfChildA7:                     0,
			PriceOfChildB7:                     0,
			PriceOfChildC7:                     0,
			PriceOfChildD7:                     0,
			UseOfDay8:                          0,
			RoomType8:                          "",
			NumberOfRoom8:                      0,
			RoomPrice8:                         0,
			PriceOfMale8:                       0,
			PriceOfFemale8:                     0,
			PriceOfChildA8:                     0,
			PriceOfChildB8:                     0,
			PriceOfChildC8:                     0,
			PriceOfChildD8:                     0,
			UseOfDay9:                          0,
			RoomType9:                          "",
			NumberOfRoom9:                      0,
			RoomPrice9:                         0,
			PriceOfMale9:                       0,
			PriceOfFemale9:                     0,
			PriceOfChildA9:                     0,
			PriceOfChildB9:                     0,
			PriceOfChildC9:                     0,
			PriceOfChildD9:                     0,
			UseOfDay10:                         0,
			RoomType10:                         "",
			NumberOfRoom10:                     0,
			RoomPrice10:                        0,
			PriceOfMale10:                      0,
			PriceOfFemale10:                    0,
			PriceOfChildA10:                    0,
			PriceOfChildB10:                    0,
			PriceOfChildC10:                    0,
			PriceOfChildD10:                    0,
			UseOfDay11:                         0,
			RoomType11:                         "",
			NumberOfRoom11:                     0,
			RoomPrice11:                        0,
			PriceOfMale11:                      0,
			PriceOfFemale11:                    0,
			PriceOfChildA11:                    0,
			PriceOfChildB11:                    0,
			PriceOfChildC11:                    0,
			PriceOfChildD11:                    0,
			UseOfDay12:                         0,
			RoomType12:                         "",
			NumberOfRoom12:                     0,
			RoomPrice12:                        0,
			PriceOfMale12:                      0,
			PriceOfFemale12:                    0,
			PriceOfChildA12:                    0,
			PriceOfChildB12:                    0,
			PriceOfChildC12:                    0,
			PriceOfChildD12:                    0,
			OptionName1:                        "",
			OptionPrice1:                       0,
			OptionName2:                        "",
			OptionPrice2:                       0,
			NumberOfOptions2:                   0,
			OptionName3:                        "",
			OptionPrice3:                       0,
			NumberOfOptions3:                   0,
			OptionName4:                        "",
			OptionPrice4:                       0,
			NumberOfOptions4:                   0,
			OptionName5:                        "",
			OptionPrice5:                       0,
			NumberOfOptions5:                   0,
			Notes:                              "",
			LincolnSalesAgentCode:              0,
			InitSalesAgentCode:                 0,
		},
	}

	tests := []struct {
		name            string
		reservationData []*scCsv.ReservationData
	}{
		{
			name:            "正常系",
			reservationData: SampleReservationError1,
		},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			model, err := db.CreateCsvUploadTransaction(ctx, "test.csv", time.Now(), "", "")
			if err != nil {
				t.Errorf("failed to insert record to database: %v", err)
			}

			errors, err := db.TransactionReservationInfo(tt.reservationData, ctx)

			if err == nil && errors == nil {
				if err := db.finishCsvUpload(model.ID, ctx); err != nil {
					t.Errorf("failed to upload csv_upload_transaction status: %v", err)
				} else {
					logging.Info("successful of csv uploading", nil)
				}
			} else {
				if err := db.updateCsvUploadTransactionStatusToError(id, ctx); err != nil {
					t.Errorf("failed to upload csv_upload_transaction status: %v", err)
				} else {
					logging.Info("failed to upload csv", nil)
				}
				ids := db.InsertCSVExecutionError(ctx, errors, id)
				logging.Info(fmt.Sprintf("error ids: %v", ids), nil)
			}
		})
	}
}

func TestSelectErrorCSVRows(t *testing.T) {

	env := &config.MysqlEnv{
		User:     "xxxx",
		Host:     "xxxx",
		Password: "<PASSWORD>",
		Port:     "xxxx",
	}
	db, err := NewDatabase(env)
	if err != nil {
		glog.Errorf("failed to create database: %v", err)
		return
	}

	ctx, cancel := context.WithCancel(context.Background())
	defer cancel()
	t.Run("test", func(t *testing.T) {
		rows, err := db.GetCsvExecutionErrorsWithCsvUploadTransactionByStatus(ctx, 0)
		if err != nil {
			t.Errorf("%v", err)
		}
		fmt.Printf("%v", rows)
	})
}
<file_sep>/app/server/handlers/siteControlHandler.go
package handlers

import (
	"fmt"
	"io"
	"net/http"
	"net/http/httputil"
	"os"
	"site-controller-data-update-to-mysql/app/database"
	"site-controller-data-update-to-mysql/app/file"
	"site-controller-data-update-to-mysql/app/models"
	"strings"
	"time"

	"github.com/gin-gonic/gin"
	"github.com/latonaio/golang-logging-library/logger"
	"github.com/volatiletech/sqlboiler/v4/queries/qm"
)

const (
	CONSARVATION_PATH string = "/var/lib/aion/Data"
)

type errors struct {
	LineNumber          int    `json:"line_number"`
	CustomerName        string `json:"customer_name"`
	CustomerPhoneNumber string `json:"customer_phone_number"`
}

type SCHandler struct {
	db  *database.Database
	log *logger.Logger
}

var logging = logger.NewLogger()

func NewSCHandler(db *database.Database, logging *logger.Logger) *SCHandler {
	return &SCHandler{
		db:  db,
		log: logging,
	}
}

func (h *SCHandler) GetAuthCSV(c *gin.Context) {
	timestamp := c.Param("timestamp")
	rows, err := h.db.GetCsvUploadTransactionByTimeStamp(c.Request.Context(), timestamp)
	if err != nil {
		h.log.Error(fmt.Sprintf("database error: %+v", err), nil)
		c.JSON(http.StatusInternalServerError, gin.H{"status": "before"})
		return
	}
	if len(rows) == 1 {
		row := rows[0]
		c.JSON(http.StatusOK, gin.H{"timestamp": row.Timestamp.String, "status": row.Status.String, "path": row.Path.String})
		return
	}
	c.JSON(http.StatusInternalServerError, gin.H{"status": "before"})
	return
}

func (h *SCHandler) CSVError(c *gin.Context) {
	// ステータスが未解決（0)のCSVエラー情報を取得する
	rows, err := h.db.GetCsvExecutionErrorsWithCsvUploadTransactionByStatus(c.Request.Context(), 0)
	if err != nil {
		logging.Error(fmt.Sprintf("cannot get csv error rows: %v", err), nil)
		c.JSON(http.StatusInternalServerError, gin.H{"error": []errors{}})
		return
	}
	if len(rows) == 0 {
		c.JSON(http.StatusOK, gin.H{
			"errors": []errors{},
		})
		return
	}

	row := rows[0]
	res := map[string]interface{}{
		"file_name": row.R.CSV.FileName.String,
		"errors": []errors{
			{
				LineNumber:          row.LineNumber,
				CustomerName:        row.CustomerName.String,
				CustomerPhoneNumber: row.CustomerPhoneNumber.String,
			},
		},
	}
	c.JSON(http.StatusOK, res)
	return
}

func (h *SCHandler) UpdateErrorStatus(c *gin.Context) {
	// ステータスが未解決（0)のレコードを取得する
	rows, err := h.db.GetCsvExecutionErrorsByStatus(c.Request.Context(), 0)
	if err != nil {
		logging.Error(fmt.Sprintf("failed to get csv_excution_error: %v", err), nil)

		c.JSON(http.StatusInternalServerError, gin.H{"timestamp": nil})
		return
	}
	h.log.Debug(fmt.Sprintf("csv_excution_error record: %p", rows), nil)
	if len(rows) == 0 {
		h.log.Debug("no error csv", nil)
		c.JSON(http.StatusOK, gin.H{"timestamp": nil})
		return
	}

	_, err = rows.UpdateAll(c, h.db.DB, models.M{"status": 1})
	if err != nil {
		logging.Error(fmt.Sprintf("failed to get csv_excution_error: %v", err), nil)
		c.JSON(http.StatusInternalServerError, gin.H{"timestamp": nil})
		return
	}
	c.JSON(http.StatusOK, gin.H{"timestamp": nil})
}

func (h *SCHandler) CreateCSV(c *gin.Context) {
	timestamp := c.Param("timestamp")
	siteControllerName := c.Query("SC")
	if siteControllerName == "" {
		h.log.Error("failed to get site controller name", nil)
		c.String(http.StatusBadRequest, "BAD REQUEST")
		return
	}
	ctx := c.Request.Context()

	// リクエストの情報を出力
	body, err := httputil.DumpRequest(c.Request, true)
	if err != nil {
		h.log.Error(fmt.Sprintf("failed to bind request body: %v", err), nil)
		c.String(http.StatusInternalServerError, "INTERNAL SERVER ERROR")
		return
	}
	h.log.Debug(fmt.Sprintf("req body: %v", body), nil)

	// "file"というフィールド名に一致するファイルが出力される
	formFile, _, err := c.Request.FormFile("file")
	if err != nil {
		h.log.Error(fmt.Sprintf("failed to get file: %v", err), nil)
		c.String(http.StatusInternalServerError, "INTERNAL SERVER ERROR")
		return
	}
	defer formFile.Close()

	// ファイル名をトリム
	defaultFileName := c.Request.FormValue("defaultFileName")
	trimmedDefaultFileName := strings.Trim(defaultFileName, ".csv")

	// csvファイルの情報を取得するために一度ファイルをローカル(コンテナ内）に保存する
	//データを保存するファイルを開く
	filePath := fmt.Sprintf("%v/%v_%v.csv", CONSARVATION_PATH, trimmedDefaultFileName, timestamp)
	saveFile, err := os.Create(filePath)
	if err != nil {
		h.log.Error(fmt.Sprintf("failed to save file: %v", err), nil)
		c.String(http.StatusInternalServerError, "INTERNAL SERVER ERROR")
		return
	}
	defer saveFile.Close()

	// ファイルにデータを書き込む
	_, err = io.Copy(saveFile, formFile)
	if err != nil {
		logging.Error(fmt.Sprintf("failed to copy file: %+v", err), nil)
		c.String(http.StatusInternalServerError, "INTERNAL SERVER ERROR")
		return
	}

	// ファイルのデータをDBに突っ込む
	fileInfo, err := saveFile.Stat()
	if err != nil {
		logging.Error(fmt.Sprintf("failed to get file info: %+v", err), nil)
		c.String(http.StatusInternalServerError, "INTERNAL SERVER ERROR")
		return
	}
	file := file.File{
		Name:        fileInfo.Name(),
		CreatedTime: fileInfo.ModTime(),
	}
	model, err := h.db.CreateCsvUploadTransaction(ctx, file.Name, time.Time{}, timestamp, filePath)
	if err != nil {
		logging.Error(fmt.Sprintf("failed to insert record to database: %+v", err), nil)

		c.String(http.StatusInternalServerError, "INTERNAL SERVER ERROR")
		return
	}

	if err := h.db.RegisterCSVDataToDB(ctx, file, CONSARVATION_PATH, model.ID, siteControllerName); err != nil {
		logging.Error(err, nil)

		c.String(http.StatusInternalServerError, "INTERNAL SERVER ERROR")
		return
	}

	c.String(http.StatusOK, "ok")
}

func (h *SCHandler) GetLatestTimestamp(c *gin.Context) {
	ctx := c.Request.Context()

	row, err := models.CSVUploadTransactions(
		qm.Select("*"),
		qm.OrderBy("timestamp DESC"),
	).One(ctx, h.db.DB)
	if err != nil {
		logging.Error(fmt.Sprintf("failed to get csv_upload_transaction: %v", err), nil)
		c.JSON(http.StatusInternalServerError, gin.H{"timestamp": nil})
		return
	}
	logging.Debug(fmt.Sprintf("csv_upload_transaction record: %p", row), nil)
	if row == nil {
		logging.Info("no csv information", nil)
		c.JSON(http.StatusOK, gin.H{"timestamp": nil})
		return
	}

	timestampStr := row.Timestamp.String
	h.log.Info(timestampStr, nil)
	timestampVal := fmt.Sprintf(`%s/%s/%s %s:%s:%s`, timestampStr[0:4], timestampStr[4:6], timestampStr[6:8], timestampStr[8:10], timestampStr[10:12], timestampStr[12:14])
	logging.Info(fmt.Sprintf("latest timestamp: %s", timestampVal), nil)

	c.JSON(http.StatusOK, gin.H{"timestamp": timestampVal})
	return
}
<file_sep>/sqlboiler.toml
pkgname="models"
output="app/models"
[mysql]
  dbname = xxxxxx
  host   = xxx.xxx.xxx.xxx
  port   = xxxxx
  user   = xxxxx
  pass   = <PASSWORD>
  sslmode= "false"
  blacklist = [
     "audio_settings", "payment_detail", "payment_detail_transaction", "payment_transaction", "price_table", "room_maintenance"
  ]

# sqlboiler mysql --output app/models <file_sep>/Dockerfile
# syntax = docker/dockerfile:experimental
FROM golang:1.16.2 as builder

ENV GO111MODULE on
ENV GOPRIVATE "bitbucket.org/xxxxxx"
WORKDIR /go/src/bitbucket.org/xxxxxx

COPY go.mod .

RUN git config --global url."<EMAIL>:".insteadOf "https://bitbucket.org/"
RUN mkdir /root/.ssh/ && touch /root/.ssh/known_hosts && ssh-keyscan -t rsa bitbucket.org >> /root/.ssh/known_hosts
RUN --mount=type=secret,id=ssh,target=/root/.ssh/id_rsa go mod download

COPY . .

RUN go build -o site-controller-data-update-to-mysql site-controller-data-update-to-mysql/app/

# Runtime Container
FROM alpine:3.12

RUN apk add --no-cache libc6-compat tzdata

COPY --from=builder /go/src/bitbucket.org/latonaio/site-controller-data-update-to-mysql .

CMD ["./site-controller-data-update-to-mysql"]

<file_sep>/app/server/handlers/websocket.go
package handlers

import (
	"encoding/json"
	"log"
	"site-controller-data-update-to-mysql/app/server/response"

	"github.com/gin-gonic/gin"
	"github.com/gorilla/websocket"
)

var wsupgrader = websocket.Upgrader{
	ReadBufferSize:  1024,
	WriteBufferSize: 1024,
}

func (h *SCHandler) WsConnect(c *gin.Context, channel chan []int) {
	conn, err := wsupgrader.Upgrade(c.Writer, c.Request, nil)
	if err != nil {
		log.Printf("Failed to set websocket upgrade: %v\n", err)
		return
	}
	tx, err := h.db.DB.Begin()
	//何か受け取ってそのまま返すパターン
END:
	for {
		select {
		case errorRowIds := <-channel:
			rows, err := h.db.SelectErrorCSVRowsWithIds(errorRowIds, c.Request.Context(), tx)
			if err != nil {
				logging.Error("cannot get csv error rows", nil)
				break END
			}
			row := rows[0]
			responseStruct := response.CsvExectutionError{
				FileName: row.R.CSV.FileName.String,
				Errors: []response.Error{
					{
						LineNumber:          row.LineNumber,
						CustomerName:        row.CustomerName.String,
						CustomerPhoneNumber: row.CustomerPhoneNumber.String,
					},
				},
			}

			res, err := json.Marshal(responseStruct)
			if err != nil {
				logging.Error(err, nil)
				break END
			}
			conn.WriteMessage(websocket.BinaryMessage, res)
		}
	}
}

func sendError() {

}

func sendNoneError() {

}
