consumer.go

package repository

import (
	"database/sql"
	"errors"
	"fmt"
	"gin-vue-admin/common"
	"gin-vue-admin/models"
	"gin-vue-admin/utils"
	"strconv"
	"time"

	"github.com/uniplaces/carbon"
)

func ConsumerAnalysisRes(db *sql.DB, t string) (interface{}, error) {

	// 计算时间 及格式化的模板
	var formatSQL, formatTime string
	var sub int

	if len(t) == 10 {
		formatSQL = "%Y-%m-%d"
		formatTime = "2006-01-02"
		sub = 6
	} else if len(t) == 7 {
		formatSQL = "%Y-%m"
		formatTime = "2006-01"
		sub = 6
	} else if len(t) == 4 {
		formatSQL = "%Y"
		formatTime = "2006"
		sub = 3
	} else {
		return nil, errors.New("time error")
	}

	c, err := carbon.Parse(formatTime, t, "UTC")
	if err != nil {
		return nil, err
	}

	var endT *carbon.Carbon
	var endTime string

	if len(t) == 10 {
		endT = c.SubDays(sub)
		endTime = c.SubDays(sub).Format(formatTime)
	} else if len(t) == 7 {
		endT = c.SubMonths(sub)
		endTime = c.SubMonths(sub).Format(formatTime)
	} else if len(t) == 4 {
		endT = c.SubYears(sub)
		endTime = c.SubYears(sub).Format(formatTime)
	} else {
		return nil, errors.New("time error")
	}

	var response models.ConsumerAnalysis

	// 1. 查询数据逻辑

	// 查询 筛选时间发哪位内的 所有已结账单
	rows, err := db.Query(`select his_consumer_id, DATE_FORMAT(paytime, ?) as time from his_bill
where delflag = 0 
and billtype in (1,4,6,7)
and his_consumer_id != 0
and DATE_FORMAT(paytime, ?) >= ?
and DATE_FORMAT(paytime, ?) <= ?`, formatSQL, formatSQL, endTime, formatSQL, t)
	if err != nil {
		return nil, err
	}

	type tempStruct struct {
		ConsumerID int
		PayTime    string
	}

	tempS := make([]tempStruct, 0)

	for rows.Next() {
		var temp tempStruct
		err = rows.Scan(&temp.ConsumerID, &temp.PayTime)
		if err != nil {
			return nil, err
		}
		tempS = append(tempS, temp)
	}

	// 2. 组装数据
	for i := 0; i <= sub; i++ {
		var str string

		if len(t) == 10 {
			str = endT.AddDays(i).Format(formatTime)
		} else if len(t) == 7 {
			str = endT.AddMonths(i).Format(formatTime)
		} else {
			str = endT.AddYears(i).Format(formatTime)
		}

		var shopCustomer models.ShopCustomer
		shopCustomer.Type = "到店顾客"
		shopCustomer.Date = str
		var shopCustomerBill models.ShopCustomer
		shopCustomerBill.Type = "支付笔数"
		shopCustomerBill.Date = str

		shopCustomerMap := make(map[int]int, 0)

		for _, v := range tempS {
			if v.PayTime == str {
				shopCustomerBill.Number++
				shopCustomerMap[v.ConsumerID] = 0
			}
		}
		shopCustomer.Number = len(shopCustomerMap)

		if t == str {
			response.PayNumber = shopCustomerBill.Number
			response.Arrival = shopCustomer.Number
		}
		response.ShopCustomers = append(response.ShopCustomers, shopCustomer, shopCustomerBill)
	}

	return response, nil
}

func SourceDistributeRes(db *sql.DB) (interface{}, error) {

	var response models.Annular

	// 顾客来源
	rows, err := db.Query(`select understand, count(understand) as counts from his_consumer
where delflag = 0 and understand != ''
GROUP BY understand
order by counts desc limit 6`)
	if err != nil {
		return nil, err
	}

	sources := make([]models.ConsumerSource, 0)
	for rows.Next() {
		var temp models.ConsumerSource
		temp.A = "1"
		err = rows.Scan(&temp.Type, &temp.Cost)
		if err != nil {
			return nil, err
		}
		response.ConsumerSourceSum += temp.Cost
		sources = append(sources, temp)
	}

	// 顾客分布
	rows, err = db.Query(`select address, count(address) as counts from his_consumer
where delflag = 0 and address != ''
GROUP BY address 
order by counts desc limit 6`)
	if err != nil {
		return nil, err
	}

	distributes := make([]models.ConsumerDistribute, 0)
	for rows.Next() {
		var temp models.ConsumerDistribute
		temp.A = "1"
		err = rows.Scan(&temp.Type, &temp.Cost)
		if err != nil {
			return nil, err
		}
		response.ConsumerDistributeSum += temp.Cost
		distributes = append(distributes, temp)
	}

	response.ConsumerSources = sources
	response.ConsumerDistributes = distributes

	return response, nil
}

func ConsumptionRankRes(db *sql.DB, t string) (interface{}, error) {

	var formatSQL string

	if len(t) == 10 {
		formatSQL = "%Y-%m-%d"
	} else if len(t) == 7 {
		formatSQL = "%Y-%m"
	} else if len(t) == 4 {
		formatSQL = "%Y"
	} else {
		return nil, errors.New("time error")
	}

	rows, err := db.Query(`select ifnull(m._name, ''), ifnull(m.telephone, ''),
ifnull(convert(sum(c.quantity*c.payprice) / 1000000, decimal(11,3)), 0) as money
from his_consumption as c
left join his_bill as b on b.id = c.his_bill_id
left join his_consumer as m on m.id = c.his_consumer_id
where c.delflag = 0 and c.his_retreatbill_id = 0 
and c.commodity_ismeal = 0 
and c.meter_num = 0
and c.his_bill_id != 0
and b.billtype in (1,4,6,7)
and DATE_FORMAT(b.paytime, ?) = ?
GROUP BY c.his_consumer_id 
order by money desc`, formatSQL, t)
	if err != nil {
		return nil, err
	}

	response := make([]models.ConsumptionRank, 0)

	for rows.Next() {
		var temp models.ConsumptionRank
		err = rows.Scan(&temp.Name, &temp.Phone, &temp.Money)
		if err != nil {
			return nil, err
		}
		if temp.Name == "" {
			temp.Name = "散客"
		}
		response = append(response, temp)
	}

	return response, nil
}

func CardPreviewRes(db *sql.DB, t string, hospitalID int) (interface{}, error) {

	var formatSQL string

	if len(t) == 10 {
		formatSQL = "%Y-%m-%d"
	} else if len(t) == 7 {
		formatSQL = "%Y-%m"
	} else if len(t) == 4 {
		formatSQL = "%Y"
	} else {
		return nil, errors.New("time error")
	}

	// 实充金额、赠送金额、支出金额

	var response models.CardPreview

	err := db.QueryRow(`select b.*, t.* from (
select 
ifnull(convert(sum(money) / 1000, decimal(11,3)), 0), 
ifnull(convert(sum(presentmoney) / 1000, decimal(11,3)), 0)
from his_card_detail where delflag = 0 and eventtype in (1,3,4) and DATE_FORMAT(eventtime, ?) = ?
) as b
left join
(
select 
 ifnull(convert(sum(money) / 1000, decimal(11,3)), 0) + ifnull(convert(sum(presentmoney) / 1000, decimal(11,3)), 0) 
from his_card_detail where delflag = 0 and eventtype in (5,6,11,12,13) and DATE_FORMAT(eventtime, ?) = ?
) as t
on 1=1`, formatSQL, t, formatSQL, t).Scan(&response.MoneySum, &response.PresentMoneySum, &response.ExpendMoney)
	if err != nil {
		return nil, err
	}

	var expendTemp float64
	err = db.QueryRow(`select 
 ifnull(convert(sum(money) / 1000, decimal(11,3)), 0) + ifnull(convert(sum(presentmoney) / 1000, decimal(11,3)), 0) 
from his_card_detail where delflag = 0 and eventtype in (7,8,9,10) and DATE_FORMAT(eventtime, ?) = ?`, formatSQL, t).Scan(&expendTemp)
	if err != nil {
		return nil, err
	}

	//  支出总和 - 取消支出
	response.ExpendMoney -= expendTemp

	// 新增会员
	// 会员数量

	err = db.QueryRow(`
select b.*, t.* from (
select count(*) from his_consumer where delflag = 0 and sys_hospital_id in (0, ?)
) as b
left join
(
select count(*) from his_consumer where delflag = 0 and DATE_FORMAT(addtime, ?) = ?
) as t
on 1=1`, hospitalID, formatSQL, t).Scan(&response.ConsumerSum, &response.AddConsumer)
	if err != nil {
		return nil, err
	}

	return response, nil
}

func CardPreviewChartRes(db *sql.DB, t string) (interface{}, error) {

	var formatSQL string

	if len(t) == 10 {
		formatSQL = "%Y-%m-%d"
	} else if len(t) == 7 {
		formatSQL = "%Y-%m"
	} else if len(t) == 4 {
		formatSQL = "%Y"
	} else {
		return nil, errors.New("time error")
	}

	response, err := cardRes(db, formatSQL, t)
	if err != nil {
		return nil, err
	}

	//meterCardResponse, err := meterCardRes(db, formatSQL, t)
	//if err != nil {
	//	return nil, err
	//}
	//
	//response = append(response, meterCardResponse...)

	return response, nil
}

func cardRes(db *sql.DB, format, t string) ([]*models.CardPreviewChart, error) {

	rows, err := db.Query(`select id, _name from con_cardtype`)
	if err != nil {
		return nil, err
	}

	response := make([]*models.CardPreviewChart, 0)
	resTemp := make([]*models.CardPreviewChartTemp, 0)

	for rows.Next() {
		temp := new(models.CardPreviewChartTemp)
		err = rows.Scan(&temp.ID, &temp.Name)
		if err != nil {
			return nil, err
		}

		resTemp = append(resTemp, temp)
	}

	chartCardTemp := make(map[int]*models.CardChartTemp, 0)

	rows, err = db.Query(`select card.con_cardtype_id, 
ifnull(convert(sum(d.money) / 1000, decimal(11,3)), 0),
ifnull(convert(sum(d.presentmoney) / 1000, decimal(11,3)), 0)
from his_card_detail as d
left join his_card as card on card.id = d.his_card_id 
left join con_cardtype as c on c.id = card.con_cardtype_id
where d.delflag = 0 and d.eventtype in (1,3,4) and DATE_FORMAT(d.eventtime, ?) = ?
GROUP BY card.con_cardtype_id`, format, t)
	if err != nil {
		return nil, err
	}

	for rows.Next() {
		temp := new(models.CardChartTemp)
		err = rows.Scan(&temp.CardID, &temp.Money, &temp.PresentMoney)
		if err != nil {
			return nil, err
		}
		chartCardTemp[temp.CardID] = temp
	}

	rows, err = db.Query(`select d.eventtype, card.con_cardtype_id, 
ifnull(convert(d.money / 1000, decimal(11,3)), 0),
ifnull(convert(d.presentmoney / 1000, decimal(11,3)), 0)
from his_card_detail as d
left join his_card as card on card.id = d.his_card_id 
left join con_cardtype as c on c.id = card.con_cardtype_id
where d.delflag = 0 and d.eventtype in (5,6,7,8,9,10,11,12,13) and DATE_FORMAT(d.eventtime, ?) = ? `, format, t)
	if err != nil {
		return nil, err
	}

	for rows.Next() {
		temp := new(models.CardChartTemp)
		var eventType int
		err = rows.Scan(&eventType, &temp.CardID, &temp.ExpendMoney, &temp.PresentMoney)
		if err != nil {
			return nil, err
		}

		if eventType == 5 || eventType == 6 || eventType == 11 || eventType == 12 || eventType == 13 {
			if _, ok := chartCardTemp[temp.CardID]; ok {
				chartCardTemp[temp.CardID].ExpendMoney += temp.ExpendMoney
				chartCardTemp[temp.CardID].ExpendMoney += temp.PresentMoney
			} else {
				chartCardTemp[temp.CardID] = temp
			}
		} else {
			if _, ok := chartCardTemp[temp.CardID]; ok {
				chartCardTemp[temp.CardID].ExpendMoney -= temp.ExpendMoney
				chartCardTemp[temp.CardID].ExpendMoney -= temp.PresentMoney
			} else {
				chartCardTemp[temp.CardID] = temp
			}
		}

	}

	for _, v := range resTemp {

		temp1 := new(models.CardPreviewChart)
		temp2 := new(models.CardPreviewChart)
		temp3 := new(models.CardPreviewChart)

		temp1.Name = v.Name
		temp1.Type = "支出金额"
		temp2.Name = v.Name
		temp2.Type = "赠送金额"
		temp3.Name = v.Name
		temp3.Type = "实充金额"

		if res, ok := chartCardTemp[v.ID]; ok {

			temp1.Value = res.ExpendMoney
			temp2.Value = res.PresentMoney
			temp3.Value = res.Money
		}

		response = append(response, temp1, temp2, temp3)
	}

	return response, nil
}

// 次卡这版本先不做
func meterCardRes(db *sql.DB, format, t string) ([]*models.CardPreviewChart, error) {

	rows, err := db.Query(`select id, _name from con_metercardtype`)
	if err != nil {
		return nil, err
	}

	response := make([]*models.CardPreviewChart, 0)
	resTemp := make([]*models.CardPreviewChartTemp, 0)

	for rows.Next() {
		temp := new(models.CardPreviewChartTemp)
		err = rows.Scan(&temp.ID, &temp.Name)
		if err != nil {
			return nil, err
		}

		resTemp = append(resTemp, temp)
	}

	chartCardTemp := make(map[int]*models.CardChartTemp, 0)

	rows, err = db.Query(`select card.con_metercardtype_id, 
ifnull(convert(sum(d.money) / 1000, decimal(11,3)), 0)
from his_metercard_detail as d
left join his_metercard as card on card.id = d.his_metercard_id 
left join con_metercardtype as c on c.id = card.con_metercardtype_id
where d.delflag = 0 and d.eventtype in (1,2) and DATE_FORMAT(d.eventtime, ?) = ?
GROUP BY card.con_metercardtype_id`, format, t)
	if err != nil {
		return nil, err
	}

	for rows.Next() {
		temp := new(models.CardChartTemp)
		err = rows.Scan(&temp.CardID, &temp.Money)
		if err != nil {
			return nil, err
		}
		chartCardTemp[temp.CardID] = temp
	}

	rows, err = db.Query(`select card.con_metercardtype_id, 
ifnull(convert(sum(d.money) / 1000, decimal(11,3)), 0)
from his_metercard_detail as d
left join his_metercard as card on card.id = d.his_metercard_id 
left join con_metercardtype as c on c.id = card.con_metercardtype_id
where d.delflag = 0 and d.eventtype in (3,4) and DATE_FORMAT(d.eventtime, ?) = ?
GROUP BY card.con_metercardtype_id`, format, t)
	if err != nil {
		return nil, err
	}

	for rows.Next() {
		temp := new(models.CardChartTemp)
		err = rows.Scan(&temp.CardID, &temp.ExpendMoney)
		if err != nil {
			return nil, err
		}
		if _, ok := chartCardTemp[temp.CardID]; ok {
			chartCardTemp[temp.CardID].ExpendMoney += temp.ExpendMoney
		} else {
			chartCardTemp[temp.CardID] = temp
		}
	}

	for _, v := range resTemp {

		temp1 := new(models.CardPreviewChart)
		temp2 := new(models.CardPreviewChart)
		temp3 := new(models.CardPreviewChart)

		temp1.Name = v.Name
		temp1.Type = "支出金额"
		temp2.Name = v.Name
		temp2.Type = "赠送金额"
		temp3.Name = v.Name
		temp3.Type = "实充金额"

		if res, ok := chartCardTemp[v.ID]; ok {

			temp1.Value = res.ExpendMoney
			temp2.Value = res.PresentMoney
			temp3.Value = res.Money
		}

		response = append(response, temp1, temp2, temp3)
	}

	return response, nil
}

func CardTypeRes(db *sql.DB) (interface{}, error) {

	response := make([]*models.CardType, 0)

	rows, err := db.Query(`select id, _name from con_cardtype`)
	if err != nil {
		return nil, err
	}

	for rows.Next() {
		var temp models.CardType
		temp.Type = 1
		err = rows.Scan(&temp.ID, &temp.CardName)
		if err != nil {
			return nil, err
		}
		response = append(response, &temp)
	}

	// 次卡这个版本先不做
	//rows, err = db.Query(`select id, _name from con_metercardtype`)
	//if err != nil {
	//	return nil, err
	//}
	//for rows.Next() {
	//	var temp models.CardType
	//	temp.Type = 2
	//	err = rows.Scan(&temp.ID, &temp.CardName)
	//	if err != nil {
	//		return nil, err
	//	}
	//	response = append(response, &temp)
	//}

	return response, nil
}

// 会员卡
func CardDetailByIDRes(db *sql.DB, t string, cardID int) (*models.CardDetailInfo, error) {

	var formatSQL string

	if len(t) == 10 {
		formatSQL = "%Y-%m-%d"
	} else if len(t) == 7 {
		formatSQL = "%Y-%m"
	} else if len(t) == 4 {
		formatSQL = "%Y"
	} else {
		return nil, errors.New("time error")
	}
	sqlStr := `select d.eventtime, consumer._name, d.eventtype, 
ifnull(convert(d.money / 1000, decimal(11,3)), 0), 
ifnull(convert(d.presentmoney / 1000, decimal(11,3)), 0)
from his_card_detail as d
join his_card as c on c.id = d.his_card_id
join his_consumer as consumer on consumer.id = c.his_consumer_id
where d.delflag = 0 
and DATE_FORMAT(eventtime,?) = ? `

	if cardID != 0 {
		sqlStr += fmt.Sprintf(` and c.con_cardtype_id = %v`, cardID)
	}

	sqlStr += ` order by d.id desc`

	rows, err := db.Query(sqlStr, formatSQL, t)
	if err != nil {
		return nil, err
	}

	var response models.CardDetailInfo
	response.CardDetails = make([]*models.CardDetail, 0)

	for rows.Next() {
		var temp models.CardDetail
		var Type int
		err = rows.Scan(&temp.Time, &temp.ConsumerName, &Type, &temp.Money, &temp.PresentMoney)
		if err != nil {
			return nil, err
		}

		if Type == 1 || Type == 3 || Type == 4 {
			response.MoneySum += temp.Money
			response.PresentMoneySum += temp.PresentMoney
		} else if Type == 5 || Type == 6 || Type == 11 || Type == 12 || Type == 13 {
			response.ExpendMoneySum += temp.Money
			response.ExpendMoneySum += temp.PresentMoney
		} else if Type == 7 || Type == 8 || Type == 9 || Type == 10 {
			response.ExpendMoneySum -= temp.Money
			response.ExpendMoneySum -= temp.PresentMoney
		}

		temp.Type = common.CardType[Type]
		response.CardDetails = append(response.CardDetails, &temp)
	}

	return &response, nil
}

// 次卡
func MeterCardDetailByIDRes(db *sql.DB, t string, cardID, listType int) (*models.CardDetailInfo, error) {

	var formatSQL string

	if len(t) == 10 {
		formatSQL = "%Y-%m-%d"
	} else if len(t) == 7 {
		formatSQL = "%Y-%m"
	} else if len(t) == 4 {
		formatSQL = "%Y"
	} else {
		return nil, errors.New("time error")
	}

	sqlStr := `select ifnull(d.eventtime, ''), ifnull(consumer._name, ''), ifnull(d.eventtype, 0), 
ifnull(convert(sum(d.money) / 1000, decimal(11,3)), 0)
from his_metercard_detail as d
join his_metercard as c on d.his_metercard_id = c.id
join his_consumer as consumer on c.his_consumer_id = consumer.id
where d.delflag = 0 
and DATE_FORMAT(eventtime,?) = ? `

	if cardID != 0 {
		sqlStr += fmt.Sprintf(` and c.con_metercardtype_id = %v`, cardID)
	}

	sqlStr += ` order by d.id desc`

	rows, err := db.Query(sqlStr, formatSQL, t)
	if err != nil {
		return nil, err
	}

	var response models.CardDetailInfo
	response.CardDetails = make([]*models.CardDetail, 0)

	for rows.Next() {
		var temp models.CardDetail
		var Type int
		err = rows.Scan(&temp.Time, &temp.ConsumerName, &Type, &temp.Money)
		if err != nil {
			return nil, err
		}

		if Type == 2 {
			temp.Type = "充值"
			response.MoneySum += temp.Money
			if listType == 0 || listType == 1 {
				response.CardDetails = append(response.CardDetails, &temp)
			}
		} else if Type == 3 {
			temp.Type = "消费"
			response.ExpendMoneySum += temp.Money
			if listType == 0 || listType == 2 {
				response.CardDetails = append(response.CardDetails, &temp)
			}
		} else if Type == 4 {
			temp.Type = "退卡"
			response.ExpendMoneySum += temp.Money
			if listType == 0 || listType == 2 {
				response.CardDetails = append(response.CardDetails, &temp)
			}
		}
	}

	return &response, nil
}

func GetConsumerListByCondition(db *sql.DB, p *models.ConsumerListCondition, hospitalId int64, page, pageSize int64) (interface{}, error) {

	queryConsumerSQL := `SELECT
	c.id,
	c._name,
	c.namepy,
	c.sex,
	c.telephone,
	ifnull( c.birthday, '' ),
	c.address,
	c.understand,
	c.email,
	CONVERT (
		c.bonus / 1000,
	DECIMAL ( 11, 3 )),
	CONVERT (
		c.deposit_money / 1000,
	DECIMAL ( 11, 3 )),
	c.addtime,
	c.is_blacklist,
	ifnull( c.blacklist_content, '' ),
	c.sys_hospital_id,
	h.hospital_code,
	h._name 
FROM
	his_consumer c
	LEFT JOIN sys_hospital h ON c.sys_hospital_id = h.id 
	LEFT JOIN his_pet p ON p.his_consumer_id = c.id AND p.delflag = 0 
	LEFT JOIN his_card card ON card.his_consumer_id = c.id   AND card.delflag = 0 
WHERE
	c.delflag = 0  `

	if p.Keyword != "" {
		queryConsumerSQL = SqlHandle(p, queryConsumerSQL)
	}

	if p.IsChain == 0 {
		queryConsumerSQL += fmt.Sprintf(` and c.sys_hospital_id =  %v `, hospitalId)
	} else {
		queryConsumerSQL += fmt.Sprintf(` and c.sys_hospital_id !=  %v `, hospitalId)
	}

	queryConsumerSQL += " group by c.id  ORDER BY id  DESC  limit ?,?  "

	rows, err := db.Query(queryConsumerSQL, page, pageSize)
	if err != nil {
		return nil, err
	}

	consumers := make([]models.Consumer, 0)
	for rows.Next() {
		var c models.Consumer
		err = rows.Scan(&c.ID, &c.Name, &c.Namepy, &c.Sex, &c.Telephone,
			&c.Birthday, &c.Address, &c.Understand, &c.Email, &c.Bonus, &c.DepositMoney, &c.AddTime, &c.IsBlackList, &c.BlackList, &c.HospitalID, &c.HospitalCode, &c.HospitalName)
		if err != nil {
			return nil, err
		}

		if c.HospitalID == hospitalId {
			c.IsChain = 0
		} else {
			c.IsChain = 1
		}

		if len(c.Telephone) > 11 {
			c.Telephone = utils.DescrySignPhone(c.Telephone)
		}

		consumers = append(consumers, c)
	}

	return consumers, nil
}

func GetConsumerTotalByCondition(db *sql.DB, p *models.ConsumerListCondition, hospitalId int64) (int64, error) {

	var total int64

	queryConsumerSQL := `  
SELECT  COUNT(0) FROM (  SELECT
count(0)
FROM
	his_consumer c
	LEFT JOIN sys_hospital h ON c.sys_hospital_id = h.id 
	LEFT JOIN his_pet p ON p.his_consumer_id = c.id AND p.delflag = 0 
	LEFT JOIN his_card card ON card.his_consumer_id = c.id   AND card.delflag = 0 
WHERE
	c.delflag = 0  `

	if p.Keyword != "" {
		queryConsumerSQL = SqlHandle(p, queryConsumerSQL)
	}

	if p.IsChain == 0 {
		queryConsumerSQL += fmt.Sprintf(` and c.sys_hospital_id =  %v `, hospitalId)
	} else {
		queryConsumerSQL += fmt.Sprintf(` and c.sys_hospital_id !=  %v `, hospitalId)
	}

	queryConsumerSQL += " group by c.id  ) a"

	err := db.QueryRow(queryConsumerSQL).Scan(&total)
	switch {
	case err == sql.ErrNoRows:
		return total, nil
	case err != nil:
		return 0, err
	}

	return total, nil
}

func SqlHandle(p *models.ConsumerListCondition, queryConsumerSQL string) string {
	if p.Keyword != "" {
		p.Keyword = "%" + p.Keyword + "%"
		switch p.Type {
		case 1:
			return queryConsumerSQL + fmt.Sprintf(` AND ( c._name  LIKE '%s' OR c.telephone  LIKE '%s' OR  p._name LIKE '%s'  OR  p._code LIKE '%s' OR card.cardtype LIKE '%s' OR  card._code LIKE '%s'  OR  	p.species LIKE '%s' )   `, p.Keyword, p.Keyword, p.Keyword, p.Keyword, p.Keyword, p.Keyword, p.Keyword)
		case 2:
			return queryConsumerSQL + fmt.Sprintf(` AND ( c._name  LIKE '%s'  ) `, p.Keyword)
		case 3:
			return queryConsumerSQL + fmt.Sprintf(` AND (  c.telephone  LIKE '%s' )   `, p.Keyword)
		case 4:
			return queryConsumerSQL + fmt.Sprintf(` AND ( p._name LIKE '%s' )   `, p.Keyword)
		case 5:
			return queryConsumerSQL + fmt.Sprintf(` AND ( p._code LIKE '%s' )   `, p.Keyword)
		case 6:
			return queryConsumerSQL + fmt.Sprintf(` AND ( card.cardtype LIKE '%s' )   `, p.Keyword)
		case 7:
			return queryConsumerSQL + fmt.Sprintf(` AND ( card._code LIKE '%s' )   `, p.Keyword)
		case 8:
			return queryConsumerSQL + fmt.Sprintf(` AND ( p.species LIKE '%s' )   `, p.Keyword)
		}
	}
	return queryConsumerSQL
}

func GetConsumerDetailById(db *sql.DB, consumerId string, hospitalId int64) (interface{}, error) {

	queryConsumerSQL := `select c.id, c._name, c.namepy, c.sex, c.telephone, ifnull(c.birthday,""), c.address, c.understand, c.email, convert(c.bonus / 1000, decimal(11,3)), convert(c.deposit_money / 1000, decimal(11,3)), c.addtime,h.id,h.hospital_code,h._name  from his_consumer  c   left join  sys_hospital  h on c.sys_hospital_id  = h.id
where c.delflag = 0 and c.id = ?`

	var c models.Consumers

	err := db.QueryRow(queryConsumerSQL, consumerId).Scan(&c.ID, &c.Name, &c.Namepy, &c.Sex, &c.Telephone, &c.Birthday, &c.Address, &c.Understand, &c.Email, &c.Bonus, &c.DepositMoney, &c.AddTime, &c.HospitalID, &c.HospitalCode, &c.HospitalName)
	switch {
	case err == sql.ErrNoRows:
	case err != nil:
		return nil, err
	}

	if len(c.Telephone) > 11 {
		c.Telephone = utils.DescrySignPhone(c.Telephone)
	}

	pet, err := GetPetInfoByConsumerId(db, consumerId, hospitalId)
	if err != nil {
		return nil, err
	}

	card, err := GetCardInfoByPetId(db, consumerId, hospitalId)
	if err != nil {
		return nil, err
	}

	c.Pets = pet
	c.CardAndMeterCard = card

	return c, err

}

func GetPetInfoByConsumerId(db *sql.DB, consumerId string, hospitalId int64) ([]models.Pet, error) {

	queryPetSQL := `select p.id, p.his_consumer_id, p.con_kind_id, p.kind, p.con_species_id, p.species,
	p._code, p._name, p.namepy, p.birthday, p.sex, p.coatcolor, p.is_dead, p.is_immunity, p.is_sterilized, p.is_insecticide,
	p.discription, p.addtime, p.weight,h.id,h.hospital_code,h._name, p.sys_file_id from his_pet p left join sys_hospital h on p.sys_hospital_id=h.id where p.delflag = 0 and p.his_consumer_id = ?`

	pets := make([]models.Pet, 0)

	rows, err := db.Query(queryPetSQL, consumerId)
	if err != nil {
		return nil, err
	}

	for rows.Next() {
		var pet models.Pet

		err = rows.Scan(&pet.ID, &pet.HisConsumerId, &pet.ConKindId, &pet.Kind, &pet.ConSpeciesID, &pet.Species,
			&pet.PetCode, &pet.PetName, &pet.PetNamepy, &pet.PetBirthday, &pet.PetSex, &pet.CoatColor, &pet.IsDead,
			&pet.IsImmunity, &pet.IsSterilized, &pet.IsInsecticide, &pet.Discription, &pet.AddTime, &pet.Weight,
			&pet.HospitalID, &pet.HospitalCode, &pet.HospitalName, &pet.SysFileID)

		if err != nil {
			return nil, err
		}

		if pet.HospitalID != hospitalId {
			pet.IsChain = 1
		}

		if pet.SysFileID != 0 {
			err = db.QueryRow(`select guid, extension from sys_file where id =  ?`, pet.SysFileID).Scan(&pet.SysFileGuid, &pet.SysFileExtension)
			if err != nil {
				return nil, err
			}
		}

		pets = append(pets, pet)

	}

	return pets, err
}

func GetCardInfoByPetId(db *sql.DB, consumerId string, hospitalId int64) ([]models.CardAndMeterCard, error) {

	// 查询会员卡sql
	queryCardSQL := `select id, _code, con_cardtype_id, cardtype, createtime, 
					convert(money / 1000, decimal(11,3)), 
					convert(presentmoney / 1000, decimal(11,3)), 
					isuse_password, _password, 
					convert(off / 1000, decimal(11,3))
					from his_card
					where delflag = 0 and his_consumer_id = ?
					order by id desc`

	querySecondCardSQL := `select id, _code, con_metercardtype_id, metercardtype_name, 
							meter, overtime, opentime 
							from his_metercard
							where delflag = 0 and sys_hospital_id = ? and his_consumer_id = ?
							order by id desc`

	rows, err := db.Query(queryCardSQL, consumerId)
	if err != nil {
		return nil, err
	}

	returnListModel := make([]models.CardAndMeterCard, 0)

	for rows.Next() {
		var t models.CardAndMeterCard
		t.Type = 1
		err = rows.Scan(&t.ID, &t.Code, &t.ConCardTypeID, &t.ConCardTypeName,
			&t.CreateTime, &t.Money, &t.PresentMoney, &t.IsusePassword, &t.Password, &t.Off)
		if err != nil {
			return nil, err
		}
		returnListModel = append(returnListModel, t)
	}

	rowsSecond, err := db.Query(querySecondCardSQL, hospitalId, consumerId)
	if err != nil {
		return nil, err
	}

	for rowsSecond.Next() {
		var t models.CardAndMeterCard
		t.Type = 2
		err = rowsSecond.Scan(&t.ID, &t.Code, &t.ConCardTypeID, &t.ConCardTypeName, &t.Meter, &t.Overtime, &t.Opentime)
		if err != nil {
			return nil, err
		}
		returnListModel = append(returnListModel, t)
	}

	return returnListModel, nil
}

func AddAppointment(db *sql.DB, p *models.AppointmentParam, hospitalId int) (interface{}, error) {

	sqlStr := fmt.Sprintf(`insert his_appointment set sys_hospital_id = ?, his_consumer_id = ?, his_consumer_name = ?, his_consumer_telephone =?,
	 his_pet_id=  ?, his_pet_name = ?, con_employee_id = ?, con_employee_name  = ?, appointment_type = ?, 
	 appointment_name = ?, appointment_time = ?, state =  0, description = ?, eventtime = ?, source = 2 , ispre_weixinnotify = ?, ispre_smsnotify = ?, 
iscur_weixinnotify = ?, iscur_smsnotify = ?,pre_weixinnum = ?, pre_smsnum = ? `)
	_, err := db.Exec(sqlStr, hospitalId, p.ConsumerID, p.ConsumerName, p.ConsumerPhone, p.PetID, p.PetName, p.DoctorID, p.DoctorName,
		p.ProjectID, p.ProjectName, p.AppointmentTime, p.Description, time.Now().Format("2006-01-02 15:04:05"), p.IspreWeixinnotify, p.IspreSmsnotify,
		p.IscurWeixinnotify, p.IscurSmsnotify, p.PreWeixinnum, p.PreSmsnum)
	if err != nil {
		return nil, err
	}

	return nil, nil
}

func GetLastPetCode(db *sql.DB, hospitalID int) (interface{}, error) {
	var (
		resp models.PetCode
		code string
	)

	tag := "P"

	err := db.QueryRow("select _code from his_pet where delflag = 0 and sys_hospital_id = ? order by id desc limit 1", hospitalID).Scan(&code)
	switch {
	case err == sql.ErrNoRows:
		resp.Code = tag + "000001"
		return resp.Code, nil
	case err != nil:
		return resp.Code, err
	}
	codeLength := len(code)
	index := 0
	for i := codeLength; i > 0; i-- {
		_, err := strconv.Atoi(code[i-1 : i])
		if err != nil {
			index = i
			break
		}
	}
	pre := code[:index]
	numstr := code[index:]
	num, err := strconv.ParseInt(numstr, 10, 0)
	if err != nil {
		resp.Code = tag + "000001"
		return resp.Code, nil
	}
	code = fmt.Sprintf("%s%0"+fmt.Sprintf("%d", len(numstr))+"d", pre, num+1)

	resp.Code = code
	return resp.Code, nil
}

func GetSpeciesList(db *sql.DB, hospitalID int, speciesId string) (interface{}, error) {

	sqlStr := "select id, creater, updater, sys_hospital_id, _name as 'name', namepy, sys_kind_id, ordervalue, remarks from con_species where delflag = 0 and sys_kind_id = ? ORDER BY ordervalue desc"

	return QueryArray(db, sqlStr, speciesId)

}

func CheckPhoneIsHave(db *sql.DB, phone string, hospitalID int) (int, error) {

	var isHave int

	sqlStr := ` select count(0)  from   his_consumer where  telephone = ?  and  sys_hospital_id = ?	 `

	err := db.QueryRow(sqlStr, phone, hospitalID).Scan(&isHave)
	if err != nil {
		return isHave, err
	}

	return isHave, err

}