gobackend-qingma/dal/product_dal.go

package dal

import (
	"crypto/md5"
	"database/sql"
	"errors"
	"fmt"
	"sort"
	"time"

	"gopkg.in/guregu/null.v3"

	"xiaoniaokuaiyan.com/xiaoniao/entity"
	"xiaoniaokuaiyan.com/xiaoniao/util"
)

type Product struct{}

func (pdal *Product) Get(productIds []int) ([]*entity.ProductDB, error) {
	if len(productIds) == 0 {
		return nil, errors.New("wrong param prooduct id")
	}
	strSql := ""
	for _, pid := range productIds {
		strSql += fmt.Sprintf("%d,", pid)
	}
	//strSql = "select t1.*, group_concat(city_id) as cityIds, group_concat(county_id separator '-') as countyIds from t_product t1 left join t_product_city t2 on t1.id = t2.product_id where id in(" + strSql[0:len(strSql)-1] + ") and is_delete = 0 group by id;"
	strSql = "select t1.*, group_concat(city_id) as cityIds from t_product t1 left join v_product_city t2 on t1.id = t2.product_id where id in(" + strSql[0:len(strSql)-1] + ") and is_delete = 0 group by id;"
	db := util.GetSqlDB()
	plist := []*entity.ProductDB{}
	err := db.Select(&plist, strSql)
	if err != nil {
		return nil, err
	}
	for _, pitem := range plist {
		dps := []entity.DetectProduct{}
		db.Select(&dps, "select t1.* from t_detect_product t1 left join t_product_detect_product t2 on t1.id = t2.detect_product_id where t2.product_id = ?", pitem.Id)
		picList := []entity.ProductPicture{}
		db.Select(&picList, "select * from t_product_picture where product_id = ? order by pic_type desc", pitem.Id)
		attrList := []struct {
			ProductId int         `db:"product_id" json:"product_id"`
			Name      null.String `db:"name" json:"name"`
			Value     null.String `db:"value" json:"value"`
			Pic       string      `db:"picture" json:"picture"`
		}{}
		//db.Select(&attrList, "select * from t_product_attribute where product_id = ?", pitem.Id)
		db.Select(&attrList, "select a.*,p.picture from t_product_attribute a left join t_product p on a.value = p.id where a.product_id = ?", pitem.Id)
		pitem.Pictures = picList
		pitem.Items = dps
		pitem.Attributes = attrList
		//query add attach city info
		cityList := []entity.City{}
		db.Select(&cityList, "select * from t_city where id in("+pitem.CityIds.String+");")
		//strCountyIds := pitem.CountyIds.String
		//countyIds := strings.Split(strCountyIds, "-")
		//cityIds := strings.Split(pitem.CityIds.String, ",")
		//countyMap := map[string]string{}
		//for i, cid := range cityIds {
		//	for j, ctid := range countyIds {
		//		if j != i {
		//			continue
		//		}
		//		subCountyIds := strings.Split(ctid, ",")
		//		for _, sctid := range subCountyIds {
		//			countyMap[sctid] = cid
		//		}
		//	}
		//}
		//cityMap := map[string]int{}
		//for i, cityItem := range cityList {
		//	cityMap[fmt.Sprintf("%d", cityItem.Id)] = i
		//}
		//strCountyIds = strings.Replace(strCountyIds, "-", ",", -1)
		//countys := []entity.County{}
		//db.Select(&countys, "select * from t_county where id in("+strCountyIds+");")
		//for _, ct := range countys {
		//	if cid, ok := countyMap[fmt.Sprintf("%d", ct.Id)]; ok {
		//		if idx, sok := cityMap[cid]; sok {
		//			cityList[idx].Countys = append(cityList[idx].Countys, ct)
		//		}
		//	}
		//}
		pitem.Citys = cityList
		//pitem.CountyIds.SetValid(strings.Replace(pitem.CountyIds.String, "-", ",", -1))
	}
	return plist, nil
}

func (pdal *Product) GetByCateId(cids []int, pager Pager, cityId int, sp SortParam) ([]*entity.ProductDB, error) {
	if pager.PageIndex < 1 {
		pager.PageIndex = 1
	}
	var skip = (pager.PageIndex - 1) * pager.PageSize
	var (
		sortDir = "desc"
		sortBy  = "is_recommend"
	)
	if sp.SortDir == 1 {
		sortDir = "asc"
	}
	switch sp.SortBy {
	case "SALE_NUM":
		sortBy = "sale_num"
	case "NEW":
		sortBy = "t2.created_at"
	case "SUM_UP":
		sortBy = "sort_no"
	case "PRICE":
		sortBy = "price"
	}
	var strCity = "and city_id =" + fmt.Sprintf("%d", cityId)
	if cityId == 0 {
		strCity = ""
	}
	db := util.GetSqlDB()
	if len(cids) > 0 && cids[0] == -3 {
		var childCids = []int{}
		db.Select(&childCids, "SELECT id from t_product_category WHERE parent_id in(?);", util.IntJoin(cids[1:], ","))
		cids = childCids
	}
	//strSql := "select t2.*,group_concat(city_id) as cityIds from t_product t2 left join t_product_category_product t1 on t1.product_id = t2.id join t_product_city t3 on t2.id = t3.product_id where t1.cat_id in(?) " + strCity + " and t2.is_putaway = 0 and t2.is_delete = 0 group by t2.id order by " + sortBy + " " + sortDir + ", t2.id desc limit ?,?;"
	strSql := "select t2.*,group_concat(city_id) as cityIds from t_product t2 left join t_product_category_product t1 on t1.product_id = t2.id join v_product_city t3 on t2.id = t3.product_id where t1.cat_id in(?) " + strCity + " and t2.is_putaway = 0 and t2.is_delete = 0 group by t2.id order by " + sortBy + " " + sortDir + ", t2.id desc limit ?,?;"
	var firstParam interface{} = util.IntJoin(cids, ",")
	if len(cids) > 0 {
		if cids[0] == -1 { //促销产品
			//strSql = "select t1.*, group_concat(city_id) as cityIds from t_product t1 left join t_product_city t2 on t1.id = t2.product_id where t1.id > ? and price <> market_price and market_price > 0 " + strCity + " and t1.is_putaway = 0 and t1.is_delete = 0 group by t1.id order by " + sortBy + " " + sortDir + ", t1.id desc limit ?,?;"
			strSql = "select t1.*, group_concat(city_id) as cityIds from t_product t1 left join v_product_city t2 on t1.id = t2.product_id where t1.id > ? and price <> market_price and market_price > 0 " + strCity + " and t1.is_putaway = 0 and t1.is_delete = 0 group by t1.id order by " + sortBy + " " + sortDir + ", t1.id desc limit ?,?;"
			firstParam = 0
		} else if cids[0] == -2 {
			//strSql = "select t1.*, group_concat(city_id) as cityIds from t_product t1 left join t_product_city t2 on t1.id = t2.product_id where t1.id > ? " + strCity + "  and t1.is_putaway = 0 and t1.is_delete = 0 group by t1.id order by " + sortBy + " " + sortDir + ",t1.id desc limit ?,?"
			strSql = "select t1.*, group_concat(city_id) as cityIds from t_product t1 left join v_product_city t2 on t1.id = t2.product_id where t1.id > ? " + strCity + "  and t1.is_putaway = 0 and t1.is_delete = 0 group by t1.id order by " + sortBy + " " + sortDir + ",t1.id desc limit ?,?"
			firstParam = 0
		}
	}
	plist := []*entity.ProductDB{}
	err := db.Select(&plist, strSql, firstParam, skip, pager.PageSize)
	if err != nil {
		return nil, err
	}
	for _, pitem := range plist {
		dps := []entity.DetectProduct{}
		db.Select(&dps, "select t1.* from t_detect_product t1 left join t_product_detect_product t2 on t1.id = t2.detect_product_id where t2.product_id = ?", pitem.Id)
		pitem.Items = dps
	}
	return plist, nil
}
func (pdal *Product) GetByTags(tags []string, pageIndex, pageSize uint, cityId int) ([]*entity.ProductDB, error) {
	if pageIndex < 1 {
		pageIndex = 1
	}
	if pageSize < 5 {
		pageSize = 5
	}
	var skip = pageSize * (pageIndex - 1)
	var strTag = ""
	for _, tag := range tags {
		strTag += "'" + tag + "',"
	}
	strTag = strTag[0 : len(strTag)-1]
	//strSql := "select distinct t3.* from t_tag t1 inner join t_product_tag t2 on t1.id = t2.tag_id inner join t_product t3 on t2.product_id = t3.id left join t_product_city t4 on t3.id = t4.product_id where t4.city_id = ? and t3.is_putaway = 0 and is_delete = 0 and t1.tag_name in (" + strTag + ") order by sort_no desc limit ?,?;"
	strSql := "select distinct t3.* from t_tag t1 inner join t_product_tag t2 on t1.id = t2.tag_id inner join t_product t3 on t2.product_id = t3.id left join v_product_city t4 on t3.id = t4.product_id where t4.city_id = ? and t3.is_putaway = 0 and is_delete = 0 and t1.tag_name in (" + strTag + ") order by sort_no desc limit ?,?;"
	db := util.GetSqlDB()
	plist := []*entity.ProductDB{}
	err := db.Select(&plist, strSql, cityId, skip, pageSize)
	if err != nil {
		return nil, err
	}
	for _, pitem := range plist {
		dps := []entity.DetectProduct{}
		db.Select(&dps, "select t1.* from t_detect_product t1 left join t_product_detect_product t2 on t1.id = t2.detect_product_id where t2.product_id = ?", pitem.Id)
		pitem.Items = dps
	}
	return plist, nil
}
func (pdal *Product) GetRecommend(pager Pager, cityId int, sp SortParam) ([]*entity.ProductDB, error) {
	if pager.PageIndex < 1 {
		pager.PageIndex = 1
	}
	if pager.PageSize < 5 {
		pager.PageSize = 5
	}
	var skip = (pager.PageIndex - 1) * pager.PageSize
	var (
		sortDir = "desc"
		sortBy  = "sort_no"
	)
	if sp.SortDir == 1 {
		sortDir = "asc"
	}
	switch sp.SortBy {
	case "SALE_NUM":
		sortBy = "sale_num"
	case "NEW":
		sortBy = "t1.created_at"
	case "PRICE":
		sortBy = "price"
	}
	//strSql := "select * from t_product t1 left join t_product_city t2 on t1.id = t2.product_id where city_id = ? and is_recommend = 1 and is_putaway = 0 and is_delete = 0 order by " + sortBy + " " + sortDir + ", t1.id desc limit ?,?;"
	strSql := "select * from t_product t1 left join v_product_city t2 on t1.id = t2.product_id where city_id = ? and is_recommend = 1 and is_putaway = 0 and is_delete = 0 order by " + sortBy + " " + sortDir + ", t1.id desc limit ?,?;"
	db := util.GetSqlDB()
	plist := []*entity.ProductDB{}
	err := db.Select(&plist, strSql, cityId, skip, pager.PageSize)
	if err != nil {
		return nil, err
	}
	for _, pitem := range plist {
		dps := []entity.DetectProduct{}
		db.Select(&dps, "select t1.* from t_detect_product t1 left join t_product_detect_product t2 on t1.id = t2.detect_product_id where t2.product_id = ?", pitem.Id)
		pitem.Items = dps
	}
	return plist, nil
}

func (pdal *Product) GetCate(pid int) ([]entity.ProductCategory, error) {
	strSql := "select * from t_product_category where is_leaf = 1 and pos_type = ? order by sort_no desc"
	var firstParam interface{} = pid
	switch pid {
	case -1:
		strSql = "select * from t_product_category where id > ? order by sort_no asc;"
		firstParam = 0
	case -2:
		strSql = "select * from t_product_category where id > ? and is_leaf = 1;"
		firstParam = 0
	default:
		strSql = "select * from t_product_category where parent_id = ? order by sort_no asc;"
	}
	db := util.GetSqlDB()
	cateList := []entity.ProductCategory{}
	err := db.Select(&cateList, strSql, firstParam)
	return cateList, err
}

func (pdal *Product) GetTags(level, cityId int) ([]entity.Tag, error) {
	strSql := "select id, tag_name from t_tag where parent_id = ?;"
	db := util.GetSqlDB()
	tagList := []entity.Tag{}
	err := db.Select(&tagList, strSql, level)
	return tagList, err
}

func (pdal *Product) GetProductAddingCate() (interface{}, error) {
	strSql := "select id, name, picture from t_product_add_classify;"
	db := util.GetSqlDB()
	cateList := []struct {
		Id      int         `db:"id" json:"cate_id"`
		Name    string      `db:"name" json:"name"`
		Picture null.String `db:"picture" json:"picture"`
	}{}
	err := db.Select(&cateList, strSql)
	return cateList, err
}

func (pdal *Product) GetProductByAddingCate(cid int) (interface{}, error) {
	strSql := `select t2.id, t2.name, t2.price, GROUP_CONCAT(t5.name) as items from t_product_add t1 LEFT JOIN t_product t2 on t1.product_id = t2.id 
	LEFT JOIN t_product_detect_product t3 on t2.id = t3.product_id left JOIN t_detect_product_item t4 on t3.detect_product_id = t4.detect_product_id
	left join t_detect_item t5 on t4.detect_item_id = t5.id  where t1.classify_id = ? GROUP BY(t2.id);`
	db := util.GetSqlDB()
	plist := []struct {
		Id    int         `db:"id" json:"id"`
		Name  string      `db:"name" json:"name"`
		Price int         `db:"price" json:"price"`
		Items null.String `db:"items" json:"items"`
	}{}
	err := db.Select(&plist, strSql, cid)
	return plist, err
}

func (pdal *Product) AddProductByDpIds(dpids []int) (interface{}, error) {
	sort.Ints(dpids)
	strDpids := util.IntJoin(dpids, ",")
	strSql := "select id, price from t_detect_product where id  in(" + strDpids + ");"
	db := util.GetWriteSqlDB()
	var dpItems = []struct {
		Id    int `db:"id"`
		Price int `db:"price"`
	}{}
	err := db.Select(&dpItems, strSql)
	if err != nil {
		return nil, err
	}
	if len(dpItems) != len(dpids) {
		return nil, errors.New("数据错误")
	}
	var pPrice int
	for _, dpItem := range dpItems {
		pPrice += dpItem.Price
	}
	buf := md5.Sum([]byte(strDpids))
	strDpids = fmt.Sprintf("%x", buf[:])
	strSql = "select id,price from t_product where dpid_str = ? limit 1;"
	var pitem = struct {
		Id    int `db:"id"`
		Price int `db:"price"`
	}{}
	err = db.Get(&pitem, strSql, strDpids)
	if err != nil {
		if err == sql.ErrNoRows {
			//TODO create new product
			tx := db.MustBegin()
			newPitem := entity.Product{
				Name:          "专属产品",
				IsPutAway:     1,
				Picture:       "",
				CreatedAt:     time.Now().Format("2006-01-02 15:04:05"),
				Price:         pPrice,
				MarketPrice:   pPrice,
				Tube:          `{"yellow":2,"green":0,"purple":1,"breathcard":0,"breathbagpowder":0,"breathbaggree":0,"swab":0,"other":""}`,
				SaleMode:      100,
				Collection:    "成人",
				Notice:        "电子报告，1-2工作日",
				Report:        "建议空腹",
				IsDrawBlood:   1,
				Sampling:      "静脉血",
				TipReportDays: 2,
				DpidStr:       strDpids,
			}
			strSql, mkv := util.GenerateInsertSqlFromStruct("t_product", &newPitem)
			sqlResult, err := tx.NamedExec(strSql, mkv)
			if err != nil {
				tx.Tx.Rollback()
				return nil, err
			}
			if ra, _ := sqlResult.RowsAffected(); ra <= 0 {
				tx.Tx.Rollback()
				return nil, errors.New("failed to generate new product")
			}
			newPid, _ := sqlResult.LastInsertId()
			var strValues string = "insert into t_product_detect_product(product_id,detect_product_id) values"
			for _, dpid := range dpids {
				strValues += fmt.Sprintf("(%d,%d),", newPid, dpid)
			}
			strValues = strValues[0 : len(strValues)-1]
			sqlResult = tx.MustExec(strValues)
			if ra, _ := sqlResult.RowsAffected(); ra <= 0 {
				tx.Tx.Rollback()
				return nil, errors.New("failed to generate detect_product")
			}
			strValues = "insert into t_product_city(city_id,county_id,product_id) values"
			strValues += fmt.Sprintf("(1,'1,2,5,6,8,11,12,13,14',%d),", newPid)
			strValues += fmt.Sprintf("(73,'747,748,749,750,751,752,753,754,755',%d),", newPid)
			strValues += fmt.Sprintf("(237,'2156,2157,2158,2159,2160,2175,2176',%d),", newPid)
			strValues += fmt.Sprintf("(198,'1812,1813,1814,1815,1816,1817,1818,1819,1820,1821,1822,1823',%d),", newPid)
			strValues += fmt.Sprintf("(170,'1591,1593,1592,1588,1590,1589',%d),", newPid)
			strValues += fmt.Sprintf("(74,'766,768,769,770,774',%d)", newPid)
			sqlResult = tx.MustExec(strValues)
			if ra, _ := sqlResult.RowsAffected(); ra <= 0 {
				tx.Tx.Rollback()
				return nil, errors.New("failed to generate new product_city")
			}
			strSql = fmt.Sprintf("insert into t_product_picture(product_id, picture,pic_type) values(%d, %s,1", newPid, "")
			sqlResult = tx.MustExec(strSql)
			if ra, _ := sqlResult.RowsAffected(); ra <= 0 {
				tx.Tx.Rollback()
				return nil, errors.New("failed to generate new product picture")
			}
			tx.Commit()
			return newPid, nil
		} else {
			return nil, err
		}
	}

	if pPrice != pitem.Price {
		strSql = "update t_product set price = ? where id = ?;"
		db.Exec(strSql, pPrice, pitem.Id)
	}
	return pitem.Id, nil
}

func (pdal *Product) GetTop(n int, cityId int) ([]entity.ProductDB, error) {
	var (
		now       = time.Now()
		startDate = now.Add(-time.Hour * 24 * 90).Format("2006-01-02")
		endDate   = now.Add(time.Hour * 24).Format("2006-01-02")
	)
	/*strSql := `select * from t_product t1 right join(
		select tp.* from (
		    SELECT product_id, count(*) as num from t_order_product where order_id in(
				SELECT id from t_order where actual_payment >0 and (created_at BETWEEN ? and ?) and status not in(1,7,8,9,14)
			) GROUP BY product_id
		)tp left JOIN t_product_city tc on tp.product_id = tc.product_id where tc.city_id = ?
	) t2 on t1.id = t2.product_id  where is_delete = 0 and is_putaway =0 order by num desc LIMIT ?;`*/
	strSql := `select * from t_product t1 right join(
					select tp.* from (
					    SELECT product_id, count(*) as num from t_order_product where order_id in(
							SELECT id from t_order where actual_payment >0 and (created_at BETWEEN ? and ?) and status not in(1,7,8,9,14) and retype in ('100','110') and is_delete='N'
						) GROUP BY product_id 
					)tp left JOIN v_product_city tc on tp.product_id = tc.product_id where tc.city_id = ?
				) t2 on t1.id = t2.product_id  where is_delete = 0 and is_putaway =0 order by num desc LIMIT ?;`
	db := util.GetSqlDB()
	plist := []entity.ProductDB{}
	err := db.Select(&plist, strSql, startDate, endDate, cityId, n)
	return plist, err
}

var DefaultProductDal = &Product{}