services/models/book/book.go

package book

import (
	"errors"
	"spaceofcheng/services/models"
	"spaceofcheng/services/models/model"
	"spaceofcheng/services/utils"
	"strconv"
	"strings"
	"time"

	"github.com/astaxie/beego"

	"github.com/go-xorm/xorm"
)

// BookDAO 当前数据库操作对象
type BookDAO struct {
	ctx *utils.Context
	db  *xorm.Session
}

// NewBookDAO New Inst
func NewBookDAO(ctx *utils.Context) *BookDAO {
	return &BookDAO{
		ctx: ctx,
		db:  ctx.DB,
	}
}

func (m *BookDAO) GetBookList(bookType, name, barcode, showzero, caseid string, page, pageSize int) ([]model.TaBook, error) {
	var book []model.TaBook
	sql := `select * from ta_book where status = '` + strconv.Itoa(models.STATUS_NORMAL) + `'
	and case_id in('` + strings.Replace(caseid, ",", "','", -1) + `')`
	if bookType != "" {
		sql += ` and book_type_id = '` + bookType + `'`
	}
	if name != "" {
		sql += ` and (book_name like '%` + name + `%' or author like '%` + name + `%')`
	}
	if barcode != "" {
		sql += ` and book_barcode = '` + barcode + `'`
	}
	if showzero == "0" {
		sql += ` and left_num>0`
	}
	sql += ` order by create_date desc limit ` + strconv.Itoa((page-1)*pageSize) + `, ` + strconv.Itoa(pageSize)
	err := m.db.Sql(sql).Find(&book)
	return book, err

}
func (m *BookDAO) GetBookListCount(bookType, name, showzero, barcode, caseid string) (int, error) {
	var book []model.TaBook
	sql := `select * from ta_book where status = '` + strconv.Itoa(models.STATUS_NORMAL) + `'
	and case_id in('` + strings.Replace(caseid, ",", "','", -1) + `')`
	if bookType != "" {
		sql += ` and book_type_id = '` + bookType + `'`
	}
	if barcode != "" {
		sql += ` and book_barcode = '` + barcode + `'`
	}
	if name != "" {
		sql += ` and (book_name like '%` + name + `%' or author like '%` + name + `%')`
	}
	if showzero == "0" {
		sql += ` and left_num>0`
	}
	sql += ` order by create_date desc`
	err := m.db.Sql(sql).Find(&book)
	return len(book), err

}

func (m *BookDAO) GetBookById(bookId string) (*model.TaBook, error) {
	sql := `select * from ta_book where book_id = '` + bookId + `'`

	book := model.TaBook{}
	_, err := m.db.Sql(sql).Get(&book)
	return &book, err
}

func (m *BookDAO) AddBook(book model.TaBook) (*model.TaBook, error) {
	book.Status = models.STATUS_NORMAL
	book.BookId = utils.GetGUID()
	book.CreateDate = time.Now()
	_, err := m.db.Insert(book)
	return &book, err
}
func (m *BookDAO) EditBook(book model.TaBook) (*model.TaBook, error) {
	var cols = []string{
		"book_type_id",
		"book_name",
		"book_img",
		"author",
		"publisher",
		"price",
		"borrow_days",
		"book_description",
		"is_recommend",
	}
	_, err := m.db.Cols(cols...).Where("book_id = ?", book.BookId).Update(book)
	return &book, err
}
func (m *BookDAO) AddBookRecord(record model.TaBookBorrowRecord) (*model.TaBookBorrowRecord, error) {
	record.BookBorrowRecordId = utils.GetGUID()
	record.CreateDate = time.Now()
	_, err := m.db.Insert(record)
	return &record, err
}
func (m *BookDAO) GetRecordById(recordId string) (*model.TaBookBorrowRecord, error) {
	var record []model.TaBookBorrowRecord
	sql := `select * from ta_book_borrow_record where book_borrow_record_id = '` + recordId + `'`
	err := m.db.Sql(sql).Find(&record)
	if len(record) <= 0 {
		return nil, err
	}
	return &record[0], err
}
func (m *BookDAO) UpdateBookRecord(record *model.TaBookBorrowRecord) (*model.TaBookBorrowRecord, error) {
	var cols = []string{
		"borrow_date",
		"return_date",
		"borrow_status",
		"end_date",
	}
	_, err := m.db.Cols(cols...).Where("book_borrow_record_id = ?", record.BookBorrowRecordId).Update(record)
	return record, err
}
func (m *BookDAO) UpdateInStockBook(book *model.TaBook, cols []string) error {
	_, err := m.db.Cols(cols...).Where("book_id = ?", book.BookId).Update(book)
	return err
}

type BorrowRecord struct {
	model.TaBookBorrowRecord `xorm:"extends"`
	BookBarcode              string
	BookName                 string
	BookImg                  string
}

func (m *BookDAO) GetRecordList(borrowStatus, caseid, customerName, customerPhone, bookName, barcode string, page, pageSize int) ([]BorrowRecord, error) {
	var record []BorrowRecord
	sql := `SELECT
	a.*,
	b.book_name,
	b.book_barcode,
	b.book_img
FROM
	ta_book_borrow_record a
	INNER JOIN ta_book b ON a.book_id = b.book_id
	where a.case_id in('` + strings.Replace(caseid, ",", "','", -1) + `')`
	if borrowStatus != "" {
		sql += ` and a.borrow_status = '` + borrowStatus + `' `
	}
	if customerName != "" {
		sql += ` and a.customer_name like '%` + customerName + `%' `
	}
	if customerPhone != "" {
		sql += ` and a.customer_phone like'%` + customerPhone + `%' `
	}
	if bookName != "" {
		sql += ` and (b.book_name like '%` + bookName + `%')`
	}
	if barcode != "" {
		sql += ` and b.book_barcode = '` + barcode + `'`
	}
	if borrowStatus != models.BORROW_TYPE_RESERVE {
		sql += ` and a.borrow_status in(0,1,2,3) order by a.borrow_date desc`
	} else {
		sql += `order by a.create_date desc `
	}
	sql += ` limit ` + strconv.Itoa((page-1)*pageSize) + `, ` + strconv.Itoa(pageSize)
	beego.Error(sql)
	err := m.db.Sql(sql).Find(&record)
	return record, err

}

func (m *BookDAO) GetRecordListCount(borrowStatus, caseid, customerName, customerPhone, bookName, barcode string) (int, error) {
	var record []BorrowRecord
	sql := `SELECT
	a.*,
	b.book_name,
	b.book_barcode
FROM
	ta_book_borrow_record a
	INNER JOIN ta_book b ON a.book_id = b.book_id
	where a.case_id in('` + strings.Replace(caseid, ",", "','", -1) + `')`
	if borrowStatus != "" {
		sql += ` and a.borrow_status = '` + borrowStatus + `' `
	}
	if customerName != "" {
		sql += ` and a.customer_name = '` + customerName + `' `
	}
	if customerPhone != "" {
		sql += ` and a.customer_phone ='` + customerPhone + `' `
	}
	if bookName != "" {
		sql += ` and b.book_name = '` + bookName + `' `
	}
	if barcode != "" {
		sql += ` and b.book_barcode = '` + barcode + `'`
	}
	if borrowStatus != models.BORROW_TYPE_RESERVE {
		sql += ` and a.borrow_status in(0,1,2,3)`
	}
	err := m.db.Sql(sql).Find(&record)
	return len(record), err

}

func (m *BookDAO) GetBookRecommendList(caseid string, page, pageSize int) ([]model.TaBook, error) {
	var book []model.TaBook
	sql := `select * from ta_book where status = '` + strconv.Itoa(models.STATUS_NORMAL) + `'
	and case_id in('` + strings.Replace(caseid, ",", "','", -1) + `')
	and is_recommend = '` + strconv.Itoa(models.RECOMMEND_YES) + `' and left_num>0`
	sql += ` order by create_date desc limit ` + strconv.Itoa((page-1)*pageSize) + `, ` + strconv.Itoa(pageSize)
	err := m.db.Sql(sql).Find(&book)
	return book, err
}

type InStockChange struct {
	model.TaInStockChange `xorm:"extends"`
	RealName              string
	BookName              string
	LeftNum               int
	InStock               int
}

func (m *BookDAO) GetInStockChangeByBookId(bookId string, page, pageSize int) ([]InStockChange, error) {
	var change []InStockChange
	sql := `SELECT
	a.*,
	b.book_name,
	b.left_num,
	b.in_stock,
	c.real_name
FROM
	ta_in_stock_change a
	INNER JOIN ta_book b ON a.book_id = b.book_id
	INNER JOIN sys_user c on a.change_user = c.user_id
	WHERE a.book_id = '` + bookId + `'`
	sql += ` order by a.change_date desc limit ` + strconv.Itoa((page-1)*pageSize) + `, ` + strconv.Itoa(pageSize)
	err := m.db.Sql(sql).Find(&change)
	return change, err
}

func (m *BookDAO) GetInStockChangeByBookIdCount(bookId string) (int, error) {
	var change []InStockChange
	sql := `SELECT
	a.*,
	b.book_name,
	b.left_num,
	c.real_name
FROM
	ta_in_stock_change a
	INNER JOIN ta_book b ON a.book_id = b.book_id
	INNER JOIN sys_user c on a.change_user = c.user_id
	WHERE a.book_id = '` + bookId + `'`
	err := m.db.Sql(sql).Find(&change)
	return len(change), err
}

func (m *BookDAO) AddChangeRecord(change model.TaInStockChange) error {
	change.ChangeDate = time.Now()
	change.RecordId = utils.GetGUID()
	_, err := m.db.Insert(change)
	return err
}

// BookBorrowRecord 图书借阅记录
type BookBorrowRecord struct {
	model.TaBookBorrowRecord `xorm:"extends"`
	BookName                 string
	BookImg                  string
	CaseName                 string
}

// GetMineRecord 获取个人借阅记录
func (m *BookDAO) GetMineRecord(customerid, status string, page, pagesize int) ([]BookBorrowRecord, error) {
	var records []BookBorrowRecord
	sql := `select a.*,b.book_name,b.book_img,c.case_name from ta_book_borrow_record a 
	inner join sys_case c on a.case_id=c.case_id 
	left join ta_book b on a.book_id=b.book_id 
	where a.customer_id=? `
	if status != "" {
		sql += ` and a.borrow_status in ('` + strings.Replace(status, ",", "','", -1) + `')`
	}
	sql += ` order by a.borrow_status asc`
	if status == "4" {
		sql += `,a.reserve_date asc`
	} else {
		sql += `,a.end_date asc`
	}
	sql += ` limit ` + strconv.Itoa((page-1)*pagesize) + `, ` + strconv.Itoa(pagesize)
	err := m.db.Sql(sql, customerid).Find(&records)
	return records, err
}

// GetMineRecordCount 获取个人借阅记录count
func (m *BookDAO) GetMineRecordCount(customerid, status string) (int, error) {
	var records []model.TaBookBorrowRecord
	var db = m.db.Where("customer_id=?", customerid)
	if status != "" {
		db.And("borrow_status in ('" + strings.Replace(status, ",", "','", -1) + "')")
	}
	err := db.Find(&records)
	return len(records), err
}

// ReserveBook 图书预约
func (m *BookDAO) ReserveBook(record model.TaBookBorrowRecord) (model.TaBookBorrowRecord, error) {
	record.BookBorrowRecordId = utils.GetGUID()
	record.CreateDate = time.Now()
	record.ReserveDate = time.Now()
	dd, _ := time.ParseDuration("168h")
	record.ReserveEndDate = time.Now().Add(dd)
	record.BorrowStatus = models.BORROW_TYPE_RESERVE
	_, err := m.db.Insert(record)

	return record, err
}

// CheckUserReserveBook 判断用户书籍预约状态
func (m *BookDAO) CheckUserReserveBook(customerid, bookid string) (bool, error) {
	var records []model.TaBookBorrowRecord
	err := m.db.Where("customer_id=?", customerid).And("book_id=?", bookid).And("borrow_status in ('" + models.BORROW_TYPE_RESERVE + "','" + models.BORROW_TYPE_BORROWED + "','" + models.BORROW_TYPE_LATE + "')").Find(&records)
	if err != nil {
		return false, err
	}
	return len(records) == 0, nil
}

// BookLeftNumDel 图书库存减少
func (m *BookDAO) BookLeftNumDel(bookid string) error {
	sql := `update ta_book set left_num = left_num - 1 where book_id=? and left_num > 0`
	req, err := m.db.Exec(sql, bookid)
	if err != nil {
		return err
	}
	row, _ := req.RowsAffected()
	if row == 0 {
		return errors.New("预约失败，库存不足")
	}
	return nil
}

//
func (m *BookDAO) CustomerReservedBook(bookId, customerId string) (*model.TaBookBorrowRecord, error) {
	var book []model.TaBookBorrowRecord
	sql := `SELECT
	a.* 
FROM
	ta_book_borrow_record a
WHERE
	a.borrow_status = '` + models.BORROW_TYPE_RESERVE + `' 
	AND a.customer_id = '` + customerId + `' 
	AND a.book_id = '` + bookId + `'`
	err := m.db.Sql(sql).Find(&book)
	if len(book) <= 0 {
		return nil, err
	}
	return &book[0], err
}

func (m *BookDAO) GetBorrowById(borrowId string) (*model.TaBookBorrowRecord, error) {
	var book []model.TaBookBorrowRecord
	sql := `SELECT
	a.* 
FROM
	ta_book_borrow_record a
WHERE
	a.book_borrow_record_id = '` + borrowId + `'`
	err := m.db.Sql(sql).Find(&book)
	if len(book) <= 0 {
		return nil, err
	}
	return &book[0], err
}

type ReturnList struct {
	model.TaBookBorrowRecord `xorm:"extends"`
	BookName                 string
	BookBarcode              string
	BookTypeName             string
	LateDays                 string
}

func (m *BookDAO) GetCustomerReturnList(customerInfo, caseId string, page, pageSize int) ([]ReturnList, error) {
	var record []ReturnList
	sql := `SELECT
	a.*,
	b.book_name,
	b.book_barcode,
	c.book_type_name 
FROM
	ta_book_borrow_record a
	INNER JOIN ta_book b ON a.book_id = b.book_id
	INNER JOIN ta_book_type c ON b.book_type_id = c.book_type_id
	INNER JOIN ta_customer d ON a.customer_id = d.customer_id 
WHERE
	a.borrow_status IN ( '` + models.BORROW_TYPE_BORROWED + `', '` + models.BORROW_TYPE_LATE + `' ) 
	AND ( a.customer_phone = '` + customerInfo + `' OR d.barcode = '` + customerInfo + `' )
	and a.case_id in('` + strings.Replace(caseId, ",", "','", -1) + `')`
	sql += ` order by a.borrow_date desc limit ` + strconv.Itoa((page-1)*pageSize) + `, ` + strconv.Itoa(pageSize)
	err := m.db.Sql(sql).Find(&record)
	return record, err
}

func (m *BookDAO) GetCustomerReturnListCount(customerInfo, caseId string) (int, error) {
	var record []ReturnList
	sql := `SELECT
	a.*,
	b.book_name,
	b.book_barcode,
	c.book_type_name 
FROM
	ta_book_borrow_record a
	INNER JOIN ta_book b ON a.book_id = b.book_id
	INNER JOIN ta_book_type c ON b.book_type_id = c.book_type_id
	INNER JOIN ta_customer d ON a.customer_id = d.customer_id 
WHERE
	a.borrow_status IN ( '` + models.BORROW_TYPE_BORROWED + `', '` + models.BORROW_TYPE_LATE + `' ) 
	AND ( a.customer_phone = '` + customerInfo + `' OR d.barcode = '` + customerInfo + `' )
	and a.case_id in('` + strings.Replace(caseId, ",", "','", -1) + `')`
	err := m.db.Sql(sql).Find(&record)
	return len(record), err
}
func (m *BookDAO) GetCustomerByCustomerInfo(customerInfo string) (*model.TaCustomer, error) {
	var customer []model.TaCustomer
	sql := `select * from ta_customer a 
	 where a.customer_phone = '` + customerInfo + `' 
	 OR d.barcode = '` + customerInfo + `' )`
	err := m.db.Sql(sql).Find(&customer)
	if len(customer) <= 0 {
		return nil, err
	}
	return &customer[0], err

}

type TimeList struct {
	model.TaBookBorrowRecord `xorm:"extends"`
	BookName                 string
	CaseName                 string
}

// GetAllOneWeekRecord 获取时间还剩一周过期的借阅记录
func (m *BookDAO) GetAllOneWeekRecord() ([]TimeList, error) {
	var record []TimeList
	sql := `SELECT
	a.*,
	b.book_name,
	c.case_name
FROM
	ta_book_borrow_record a 
	inner join ta_book b on a.book_id = b.book_id
	inner join sys_case c on a.case_id = c.case_id
WHERE
	DATE_FORMAT( a.end_date, "%Y-%m-%d" ) = DATE_FORMAT( ( SELECT date_add( now( ), INTERVAL 1 WEEK ) ), "%Y-%m-%d" )
	and a.borrow_status = '` + models.BORROW_TYPE_BORROWED + `'`
	err := m.db.Sql(sql).Find(&record)
	return record, err
}

// GetAllLateRecord 获取过期两天的借阅记录
func (m *BookDAO) GetAllLateRecord() ([]TimeList, error) {
	var record []TimeList
	sql := `SELECT
	a.*,
	b.book_name,
	c.case_name
FROM
	ta_book_borrow_record a 
	inner join ta_book b on a.book_id = b.book_id
	inner join sys_case c on a.case_id = c.case_id
WHERE
	DATE_FORMAT( now(), "%Y-%m-%d" ) = DATE_FORMAT( ( SELECT date_add( a.end_date, INTERVAL 2 DAY ) ), "%Y-%m-%d" )
	and a.borrow_status = '` + models.BORROW_TYPE_LATE + `'`
	err := m.db.Sql(sql).Find(&record)
	return record, err
}

func (m *BookDAO) DeleteBook(bookId string) error {
	var book = model.TaBook{
		BookId: bookId,
		Status: models.STATUS_DEL,
	}
	var cols = []string{
		"status",
	}

	_, err := m.db.Cols(cols...).Where("book_id = ?", book.BookId).Update(book)
	return err
}

func (m *BookDAO) GetUserMappingById(customerId string) (*model.TaUserMapping, error) {
	var userMap []model.TaUserMapping
	sql := `select * from ta_user_mapping where user_id = '` + customerId + `'`
	err := m.db.Sql(sql).Find(&userMap)
	if len(userMap) <= 0 {
		return nil, err
	}
	return &userMap[0], err
}

func (m *BookDAO) IsBarcodeExist(barcode, caseId string) (int, error) {
	var book []model.TaBook
	sql := `select * from ta_book where book_barcode = '` + barcode + `'
	and case_id = '` + caseId + `' 
	and status ='` + strconv.Itoa(models.STATUS_NORMAL) + `'`
	err := m.db.Sql(sql).Find(&book)
	return len(book), err
}

type CustomerInfo struct {
	CustomerId string
	Name       string
	Phone      string
	BorrowNum  int
	LateNum    int
	IsLate     string
}

func (m *BookDAO) GetCustomerBorrowInfo(customerInfo string) (*CustomerInfo, error) {
	var customer []CustomerInfo
	sql := `SELECT
	a.customer_id,
	a.name,
	a.phone ,
	(select count(1) from ta_book_borrow_record c WHERE borrow_status = '1' and c.customer_id = a.customer_id)as borrow_num,
	(select count(1) from ta_book_borrow_record c WHERE borrow_status = '0' and c.customer_id = a.customer_id)as late_num
FROM
	ta_customer a LEFT JOIN ta_book_borrow_record b on a.customer_id = b.customer_id 
	where a.phone = '` + customerInfo + `' OR a.barcode = '` + customerInfo + `'
GROUP BY a.phone`
	err := m.db.SQL(sql).Find(&customer)
	if len(customer) <= 0 {
		return nil, err
	}
	if customer[0].LateNum > 0 {
		customer[0].IsLate = "yes"
	} else {
		customer[0].IsLate = "no"
	}
	return &customer[0], err
}

func (m *BookDAO) GetBookTypeByCaseId(caseId string) ([]model.TaBookType, error) {
	var bookType []model.TaBookType
	sql := `select * from ta_book_type where case_id = '` + caseId + `'`
	err := m.db.SQL(sql).Find(&bookType)
	return bookType, err
}

// func (m *BookDAO)UpdateBookRecordDays(bookId string,days int)error{

// }