gobackend-qingma/dal/order_dal.go

package dal

import (
	"database/sql"
	"errors"
	"fmt"
	"gopkg.in/guregu/null.v3"
	"log"
	"regexp"
	"strings"
	"time"
	"xiaoniaokuaiyan.com/xiaoniao/entity"
	"xiaoniaokuaiyan.com/xiaoniao/util"
)

type Order struct {
}

func (odal *Order) Update(oitem *entity.Order) (interface{}, error) {

	db := util.GetWriteSqlDB()
	sqlStr, kvm := util.GenerateUpdateSqlFromStruct("t_order", oitem, " where id='"+oitem.Id+"'")
	result, err := db.NamedExec(sqlStr, kvm)
	if err != nil {
		return nil, err
	}
	ra, err := result.RowsAffected()
	if err != nil {
		return nil, err
	}
	if ra <= 0 {
		return nil, errors.New("update failed(can not found order record)")
	}
	return oitem, nil
}

func (odal *Order) Get(id string) (*entity.OrderDB, error) {
	db := util.GetSqlDB()
	oitem := entity.OrderDB{}
	err := db.Get(&oitem, "select * FROM t_order WHERE id = ?", id)
	if err != nil {
		if err == sql.ErrNoRows {
			return nil, nil
		}
		return nil, err
	}
	return &oitem, nil
}

func (odal *Order) GetByCode(bcode string) (interface{}, error) {
	db := util.GetSqlDB()
	oitem := struct {
		entity.OrderDB
		SendbackTpl string `db:"sendback_tpl"`
		ProductName string `db:"product_name"`
	}{}
	err := db.Get(&oitem, "select t1.*, bloodtest_id, need_emptiness,report_period,pressure_pipe,blood_address,notice FROM t_order t1 left join t_order_extra t2 on t1.id = t2.order_id WHERE blood_codes like ? limit 1", "%"+bcode+"%")
	if err != nil {
		if err == sql.ErrNoRows {
			return nil, nil
		}
		return nil, err
	}
	db.Get(&oitem, "select sendback_tpl, product_name from t_product tp right join (select product_id, product_name from t_order t1 left join t_order_product t2 on t1.id = t2.order_id where id = ?) t3 on tp.id = t3.product_id limit 1;", oitem.Id)
	return &oitem, nil
}

func (odal *Order) List(customId, pageIndex, pageSize, status int, mobile string, isZFB, isHis bool) (interface{}, error) {
	var result = OrderListResult{}
	strSql := "select t2.* from (select id, mobile from t_custom where id = ?) t1, t_order t2 "
	whereStr := " where (t1.id = t2.custom_id or t1.mobile = t2.mobile) "
	whereValue := []interface{}{
		customId,
	}
	pinfo := Pager{
		PageIndex: pageIndex,
		PageSize:  pageSize,
	}
	// 20230629 增加支付宝特殊处理
	if isZFB {
		whereStr += " and t2.source='sp_zfb' "
	}
	if status > 0 {
		whereStr += " and status = ? "
		whereValue = append(whereValue, status)
	} else if status == -1 {
		whereStr += " and status in(6,11) "
	} else {
		//whereStr += " and status <> 8 "
		whereStr += " and is_delete='N'"
	}
	if mobile != "" {
		whereStr += " and t2.mobile = '" + mobile + "' "
	}
	whereStr += " and t2.retype in ('100','110')"
	//20230830 拆分历史表
	if isHis {
		strSql = util.ChangeOrderTableName(strSql)
	}
	rel, err := List(&result, "("+strSql+whereStr+") t3", strSql+whereStr, " order by created_at desc ", "", whereValue, pinfo)
	if err != nil {
		return nil, err
	}
	err = result.FetchProduct()
	if err != nil {
		return nil, err
	}
	return rel, nil
}

func (odal *Order) Detail(oid string) (interface{}, error) {
	var result = &OrderResult{}
	//strSql := "select * from t_order t1 left join t_order_extra t2 on t1.id = t2.order_id where t1.id = ?"
	strSql := ` select t1.id,t1.name,t1.gender,t1.age,t1.birthday,t1.address,t1.detail_address,t1.visit_date,t1.visit_time_range,t1.status,t1.work_fee,t1.free_fee,t1.repeat_item_fee,t1.payment,t1.actual_payment,t1.payment_time,t1.pay_type,t1.pay_no,t1.custom_mobile,t1.mobile,t1.created_at,t1.is_view_report,t1.report_picture,t2.is_notice,t2.express,t2.expressnumber,t1.delay_deadtime,t1.linker_id,t1.source,t2.report_period,t2.pressure_pipe,t2.blood_address,t2.notice,t1.remark,t1.id_num,t2.e_visit_date,t2.e_visit_time_range,t1.zone,t1.vcode,t1.jd_order_id,t1.complete_time,t1.share_source from t_order t1 left join t_order_extra t2 on t1.id = t2.order_id where t1.id = ?
				union 
				select t3.id,t3.name,t3.gender,t3.age,t3.birthday,t3.address,t3.detail_address,t3.visit_date,t3.visit_time_range,t3.status,t3.work_fee,t3.free_fee,t3.repeat_item_fee,t3.payment,t3.actual_payment,t3.payment_time,t3.pay_type,t3.pay_no,t3.custom_mobile,t3.mobile,t3.created_at,t3.is_view_report,t3.report_picture,t4.is_notice,t4.express,t4.expressnumber,t3.delay_deadtime,t3.linker_id,t3.source,t4.report_period,t4.pressure_pipe,t4.blood_address,t4.notice,t3.remark,t3.id_num,t4.e_visit_date,t4.e_visit_time_range,t3.zone,t3.vcode,t3.jd_order_id,t3.complete_time,t3.share_source from t_order_history t3 left join t_order_extra_history t4 on t3.id = t4.order_id where t3.id = ? limit 1`

	db := util.GetSqlDB()
	err := db.Get(result, strSql, oid, oid)
	if err != nil {
		return nil, err
	}
	err = result.FetchProduct()
	if err != nil {
		return nil, err
	}
	return result, nil
}

type tempPitem struct {
	ProductId   int            `db:"product_id"`
	ProductName string         `db:"product_name"`
	Price       int            `db:"price"`
	Quantity    int            `db:"quantity"`
	SaleMode    int            `db:"sale_mode"`
	Picture     string         `db:"picture"`
	SubIds      sql.NullString `db:"sub_ids"`
	Tube        sql.NullString `db:"tube"`
}

func (odal *Order) CheckAndSplitOrder(oid string) (bool, error) {
	//是否拆单了
	var isSplited bool = false
	//是否全部邮寄 状态200
	var isAllyouji200 = true //for 循环中，有一个100 就为false
	//是否包含抗原
	var isExistKangyuan = false //for 循环中， 有一个抗原 就为true
	//抗原产品名
	var kangyuanProductName string
	db := util.GetWriteSqlDB()
	strSql := "select t1.product_id, t1.product_name, t1.price, t1.picture,t1.quantity, t2.sale_mode, t2.sub_ids, t2.tube from t_order_product t1 left join t_product t2 on t1.product_id = t2.id where t1.order_id=? order by quantity desc;"
	plist := []tempPitem{}
	err := db.Select(&plist, strSql, oid)
	if err != nil {
		return isSplited, err
	}


	strSql = "select * from t_order t1 left join t_order_extra t2 on t1.id = t2.order_id where t1.id = ?"
	oitem := entity.OrderDB{}
	err = db.Get(&oitem, strSql, oid)
	if err != nil {
		return isSplited, err
	}

	//20221229如果每样下了一单，暂不拆单
	//20221229拆单逻辑调整 如果只有邮寄产品且包括抗原 发送短信27; 如果只有邮寄产品且不包括抗原，不发送短信;包含上门产品，发送短信16
	if len(plist) > 0 && plist[0].Quantity <= 1 {
		for idx := range plist {
			//20221229 判断全邮寄产品逻辑
			if isAllyouji200 { //如果目前全都是邮寄
				//如果有一个不是 200 isAllyouji200 就为false
				isAllyouji200 = plist[idx].SaleMode == 200
			}
			//20221229 判断是否有抗原逻辑
			if util.ProductIsKangYuan(plist[idx].ProductId) {
				isExistKangyuan = true
				kangyuanProductName += plist[idx].ProductName + ","
			}

			//20220427 邮寄产品直接准备耗材
			if len(plist) == 1 && util.ProductStatusJumpTo10(plist[idx].ProductId) {
				db.MustExec("update t_order set status = 10 where id = ?", oid)
			} else if len(plist) == 1 && util.ProductWenZhen(plist[idx].ProductId) {
				db.MustExec("update t_order set status = 6 where id = ?", oid)
			}
			//20210225 1149 拆单修改，防止用户只下一单不走下面的流程
			//odal.SplitOrderV2(oid, plist[idx].ProductId)
			//20220620 拆单改良版
			odal.SplitOrderV3(oid, plist[idx].ProductId)
		}
		if isAllyouji200 && isExistKangyuan {
			SendKangYuanSms(oitem.Mobile, kangyuanProductName[0:len(kangyuanProductName)-1])
		}
		/* 20221229 修改逻辑
		//20220427 邮寄产品直接准备耗材
		if len(plist) == 1 && util.ProductStatusJumpTo10(plist[0].ProductId) {
			db.MustExec("update t_order set status = 10 where id = ?", oid)
		}
		//20210225 1149 拆单修改，防止用户只下一单不走下面的流程
		odal.SplitOrderV2(oid, plist[0].ProductId)
		//20220620 拆单改良版
		odal.SplitOrderV3(oid, plist[0].ProductId)
		*/

		return isSplited, nil
	}

	tx := db.MustBegin()

	isSplited = true
	prefixReg := regexp.MustCompile(`\d`)
	prefix := prefixReg.ReplaceAllString(oitem.Id, "")
	var (
		leftPayment        int = oitem.Payment
		leftVActualPayment int = int(oitem.ActualPayment.Int64)
		leftFreeFee        int = oitem.FreeFee
	)
	//20210129 拆单后的oid 于产品的 map
	oidList := map[string]int{}
	for idx, pitem := range plist {
		//20221229 判断全邮寄产品逻辑
		if isAllyouji200 { //如果目前全都是邮寄
			//如果有一个不是 200 isAllyouji200 就为false
			isAllyouji200 = plist[idx].SaleMode == 200
		}
		//20221229 判断是否有抗原逻辑
		if util.ProductIsKangYuan(plist[idx].ProductId) {
			isExistKangyuan = true
			kangyuanProductName += plist[idx].ProductName + ","
		}
		//20220427 邮寄产品直接准备耗材
		status := oitem.Status
		if util.ProductStatusJumpTo10(pitem.ProductId) {
			status = 10
		} else if util.ProductWenZhen(pitem.ProductId) {
			status = 6
		}
		for i := 0; i < pitem.Quantity; i++ {
			if idx == len(plist)-1 && i == pitem.Quantity-1 {
				break
			}
			newOItem := &entity.Order{
				Id:             util.GenerateOrderNo(prefix),
				CreatedAt:      time.Now().Format("2006-01-02 15:04:05"),
				Name:           oitem.Name,
				Gender:         oitem.Gender,
				Birthday:       oitem.Birthday,
				Address:        oitem.Address,
				DetailAddress:  oitem.DetailAddress,
				VisitDate:      oitem.VisitDate.String,
				VisitTimeRange: oitem.VisitTimeRange.String,
				Source:         oitem.Source,
				Status:         status, //oitem.Status,
				CustomId:       int(oitem.CustomId.Int64),
				WorkFee:        0,
				Mobile:         oitem.Mobile,
				CustomMobile:   oitem.CustomMobile,
				Payment:        pitem.Price,
				FreeFee:        int(oitem.FreeFee * pitem.Price / oitem.Payment),
				VActualPayment: int(int(oitem.ActualPayment.Int64) * pitem.Price / oitem.Payment),
				ServiceRemark:  fmt.Sprintf("由订单%s自动拆单生成", oitem.Id),
				JdOrderId:      oitem.JdOrderId.String,
				Coordinates:    oitem.Coordinates.String,
			}
			leftFreeFee -= newOItem.FreeFee
			leftPayment -= newOItem.Payment
			leftVActualPayment -= newOItem.VActualPayment
			//20210129 添加 拆单后的oid，以便1149 拆单
			oidList[newOItem.Id] = pitem.ProductId
			strSql, mkv := util.GenerateInsertSqlFromStruct("t_order", newOItem)
			_, err = tx.NamedExec(strSql, mkv)
			if err != nil {
				fmt.Println("split order ", oitem.Id, err)
				tx.Tx.Rollback()
				return isSplited, err
			}
			strSql = "insert into t_order_product(order_id, product_id, product_name, price, picture) values (?,?,?,?,?)"
			sqlResult := tx.MustExec(strSql, newOItem.Id, pitem.ProductId, pitem.ProductName, pitem.Price, pitem.Picture)
			if ra, _ := sqlResult.RowsAffected(); ra <= 0 {
				tx.Tx.Rollback()
				return isSplited, err
			}
			tx.MustExec("insert into t_order_extra(order_id, pressure_pipe, relationship, is_yunxue, is_dfguomin) values (?,?,?,?,?)", newOItem.Id, pitem.Tube.String, oitem.Relationship.String, oitem.IsYunxue.Int64, oitem.IsDfguomin.Int64)
		}
		strSql = "delete from t_order_product where order_id = ? and product_id = ?"
		sqlResult := tx.MustExec(strSql, oitem.Id, pitem.ProductId)
		if ra, _ := sqlResult.RowsAffected(); ra <= 0 {
			tx.Tx.Rollback()
			break
		}
		if idx == len(plist)-1 {
			strSql = "insert into t_order_product(order_id, product_id, product_name, price, picture) values (?,?,?,?,?)"
			sqlResult = tx.MustExec(strSql, oitem.Id, pitem.ProductId, pitem.ProductName, pitem.Price, pitem.Picture)
			if ra, _ := sqlResult.RowsAffected(); ra <= 0 {
				tx.Tx.Rollback()
				return isSplited, err
			}
			tx.MustExec("update t_order_extra set pressure_pipe = ? where order_id = ?", pitem.Tube.String, oitem.Id)
			//tx.MustExec("update t_order set payment = ?, v_actual_payment = ?, free_fee =? where id = ?;", leftPayment, leftVActualPayment, leftFreeFee, oitem.Id)
			tx.MustExec("update t_order set payment = ?, v_actual_payment = ?, free_fee =?,status = ? where id = ?;", leftPayment, leftVActualPayment, leftFreeFee, status, oitem.Id)
			//添加最开始的单据
			oidList[oitem.Id] = pitem.ProductId
		}
	}
	if isSplited {
		tx.Tx.Commit()
		//todo 发送短信 提示添加信息 20210105
		strSql = "select content from t_sms_tpl where id = 16;"
		Content := ""
		db.Get(&Content, strSql)
		if len(Content) > 0 && !isAllyouji200 {
			util.SendSMS(oitem.Mobile, Content)
		}
		//拆单后2次拆单
		for k, v := range oidList {
			//odal.SplitOrderV2(k, v)
			odal.SplitOrderV3(k, v)
		}
	}

	if isAllyouji200 && isExistKangyuan {
		go SendKangYuanSms(oitem.Mobile, kangyuanProductName[0:len(kangyuanProductName)-1])
	}
	return isSplited, nil
}

// 20220620 拆单改良版 结合拆出不同产品，以及拆出多个数量
func (dal *Order) SplitOrderV3(orderId string, productId int) {
	db := util.GetWriteSqlDB()
	//查询拆单关系
	tpsInfoList := []struct {
		ProductId int    `db:"product_id" `
		ChildId   int    `db:"child_id"`
		Num       int    `db:"num"`
		Name      string `db:"name"`
		Price     int    `db:"price"`
	}{}
	//sqlSearch := "select t1.*,t2.name as name,t2.price as price from t_product_split t1 left join t_product t2 on t1.child_id = t2.id where t1.product_id =? order by t1.is_main desc"
	sqlSearch := "select t1.product_id,t1.child_id,t1.num,if (t2.name is null,'',t2.name) as name,if (t2.price is null ,0,t2.price) as price from t_product_split t1 left join t_product t2 on t1.child_id = t2.id where t1.product_id =? order by t1.is_main desc"
	err := db.Select(&tpsInfoList, sqlSearch, productId)
	if err != nil || len(tpsInfoList) <= 0 {
		log.Println(fmt.Sprintf("订单%s，产品编号为%d 不需要拆单", orderId, productId))
		return
	}

	//查询订单，
	orderDb, err := dal.Get(orderId)
	fmt.Println(orderDb)
	//查询订单 产品关联
	plist := struct {
		OrderId     string `db:"order_id"`
		ProductId   string `db:"product_id"`
		ProductName string `db:"product_name"`
		Price       int    `db:"price"`
		Picture     string `db:"picture"`
		Quantity    int    `db:"quantity"`
		IsPersonal  int    `db:"is_personal"`
	}{}
	strSql := "select * from t_order_product where order_id = ? and product_id = ?"
	err = db.Get(&plist, strSql, orderId, productId)

	tx := db.MustBegin()
	prefixReg := regexp.MustCompile(`\d`)
	prefix := prefixReg.ReplaceAllString(orderId, "")
	firstEver := true
	for i := 0; i < len(tpsInfoList); i++ {
		//todo sth
		for j := 0; j < tpsInfoList[i].Num; j++ {
			if firstEver {
				//order 不用更新
				//更新order_product
				result := tx.MustExec("update t_order_product set product_id = ?,product_name = ?,price=? where order_id = ? and product_id = ?", tpsInfoList[i].ChildId, tpsInfoList[i].Name, tpsInfoList[i].Price, orderId, productId)
				if ra, _ := result.RowsAffected(); ra <= 0 {
					tx.Tx.Rollback()
					log.Println(fmt.Sprintf("订单%s，产品编号为%d 拆单失败 更新 t_order_product ", orderId, productId))
					return
				}
				tx.MustExec("update t_order set payment = ? where id = ?", tpsInfoList[i].Price, orderId)
			} else {
				//生成订单号
				orderNewId := util.GenerateOrderNo(prefix)
				//fmt.Println(orderNewId)
				//插入 t_order
				orderDb.Id = orderNewId
				orderDb.Payment = tpsInfoList[i].Price
				orderDb.ServiceRemark = null.StringFrom(fmt.Sprintf("由%s拆单", orderId))
				orderDb.ActualPayment = null.IntFrom(0)
				strSql, mkv := util.GenerateInsertSqlFromStruct("t_order", orderDb)
				osqlResult, err := tx.NamedExec(strSql, mkv)
				if err != nil {
					tx.Tx.Rollback()
					log.Println(fmt.Sprintf("订单%s，产品编号为%d 拆单失败 插入 t_order %s", orderId, productId, orderNewId))
					return
				}
				if ra, _ := osqlResult.RowsAffected(); ra <= 0 {
					tx.Tx.Rollback()
					log.Println(fmt.Sprintf("订单%s，产品编号为%d 拆单失败 插入 t_order %s", orderId, productId, orderNewId))
					return
				}
				//todo 查询product
				//插入 t_order_product 注意 金额
				var fields = map[string]interface{}{
					"order_id":     orderNewId,
					"product_id":   tpsInfoList[i].ChildId,
					"product_name": tpsInfoList[i].Name,
					"price":        tpsInfoList[i].Price,
					"picture":      plist.Picture,
					"quantity":     plist.Quantity,
					"is_personal":  plist.IsPersonal,
				}
				strSql = util.GenerateInsertSql("t_order_product", fields)
				_, err = tx.NamedExec(strSql, fields)
				if err != nil {
					tx.Tx.Rollback()
					log.Println(fmt.Sprintf("订单%s，产品编号为%d 拆单失败,新建 t_order_product %s", orderId, productId, orderNewId))
					return
				}
				//复制 t_order_extra
				result := tx.MustExec(fmt.Sprintf("insert into t_order_extra(`order_id`, `need_emptiness`, `report_period`, `pressure_pipe`, `blood_address`, `notice`, `updated_at`, `remark1`, `cancel_reason`, `is_notice`, `relationship`, `is_yunxue`, `is_dfguomin`, `bloodtest_id`, `reportresult`, `paynurse`, `isaddpro`, `sample`, `jicai_name`, `express`, `expressnumber`, `expressstatus`, `expressinfo`, `express_user`, `expressnumber_user`) select '%s',`need_emptiness`, `report_period`, `pressure_pipe`, `blood_address`, `notice`, `updated_at`, `remark1`, `cancel_reason`, `is_notice`, `relationship`, `is_yunxue`, `is_dfguomin`, `bloodtest_id`, `reportresult`, `paynurse`, `isaddpro`, `sample`, `jicai_name`, `express`, `expressnumber`, `expressstatus`, `expressinfo`, `express_user`, `expressnumber_user`  from t_order_extra where order_id =?", orderNewId), orderId)
				if ra, _ := result.RowsAffected(); ra <= 0 {
					tx.Tx.Rollback()
					log.Println(fmt.Sprintf("订单%s，产品编号为%d 拆单失败,新建 t_order_extra %s", orderId, productId, orderNewId))
					return
				}
			}
		}
		firstEver = false
	}
	tx.Commit()
}

func SendKangYuanSms(mobile, productNames string) {
	strSql := "select content from t_sms_tpl where id = 27;"
	Content := ""
	db := util.GetSqlDB()
	db.Get(&Content, strSql)
	if len(Content) > 0 {
		Content = strings.Replace(Content, "{product}", productNames, 1)
		util.SendSMS(mobile, Content)
	}
}

func (odal *Order) CommitSurvey(osItem *entity.OrderSurvey) (bool, error) {
	db := util.GetWriteSqlDB()
	if osItem.ServiceScore == "200" {
		strSql := "select count(order_id) as ex from t_order_sat_survey where order_id = ?"
		var count uint8
		err := db.Get(&count, strSql, osItem.OrderId)
		if err != nil {
			return false, err
		}
		if count > 0 {
			return true, nil
		}
		return false, nil
	}
	strSql, mkv := util.GenerateInsertSqlFromStruct("t_order_sat_survey", osItem)
	_, err := db.NamedExec(strSql, mkv)
	if err != nil {
		return false, err
	}
	return true, nil
}

func (odal *Order) IsFirst(mobile string) (bool, error) {
	db := util.GetSqlDB()
	var count int
	err := db.Get(&count, "select count(id) from t_order where custom_mobile =? and status not in(1,9) and retype in ('100','110');", mobile)
	if err != nil {
		return false, err
	}
	if count > 1 {
		return false, nil
	}
	return true, nil
}

var DefaultOrderDal = &Order{}

func (odal *Order) ListCustomFile(customFileId, pageIndex, pageSize, status int, mobile string, isZFB, isHis bool) (interface{}, error) {
	var result = OrderListResult{}
	strSql := "select t2.* from (select id, mobile from t_custom_file where id = ?) t1, t_order t2 "
	whereStr := " where (t1.id = t2.custom_file_id or t1.mobile = t2.mobile) "
	whereValue := []interface{}{}
	if customFileId == 0 { //传0 代表是查看自己的报告
		strSql = "select t2.* from t_order t2 "
		whereStr = " where 1=1 "
	} else {
		whereValue = append(whereValue, customFileId)
	}
	pinfo := Pager{
		PageIndex: pageIndex,
		PageSize:  pageSize,
	}
	// 20230629 增加支付宝特殊处理
	if isZFB {
		whereStr += " and t2.source='sp_zfb' "
	}
	if status > 0 {
		whereStr += " and status = ? "
		whereValue = append(whereValue, status)
	} else if status == -1 {
		whereStr += " and status in(6,11) "
	} else {
		//whereStr += " and status <> 8 "
		whereStr += " and is_delete='N'"
	}
	if mobile != "" {
		whereStr += " and t2.mobile = '" + mobile + "' "
	}
	whereStr += " and t2.retype in ('100','110')"
	if isHis {
		strSql = util.ChangeOrderTableName(strSql)
	}
	rel, err := List(&result, "("+strSql+whereStr+") t3", strSql+whereStr, " order by complete_time desc ", "", whereValue, pinfo)
	if err != nil {
		return nil, err
	}
	err = result.FetchProduct()
	if err != nil {
		return nil, err
	}
	return rel, nil
}