genvip/main.go

package main

import (
	"database/sql"
	"flag"
	"fmt"

	_ "github.com/sijms/go-ora/v2"
)

//USE   main -b test -start 20210101  -end 20220315
var (
	odb      *sql.DB
	brand    string
	start    int64 = 0
	end      int64 = 0
	dbMapUrl       = map[string]string{
		"ugg":   "oracle://bosnds3:Longda2000@ugg.imix7.com:1521/orcl",
		"hdugg": "oracle://bosnds3:Longda2000@hdugg.imix7.com:1521/orcl",
		"ecco":  "oracle://bosnds3:Longda2000@ecco.imix7.com:1521/orcl",
		"test":  "oracle://bosnds3:Longda2000@qt.imix7.com:1521/test",
	}
)

type Number interface {
	int64 | int32 | int | string
}

type RetailInfo struct {
	Id       int64
	DocNO    string
	Vip      int64
	BillDate int64
	Saler    int64
}

type Vip struct {
	Id          int64
	Name        string
	CardNo      string
	OnlyGuideId int64
	Phone       string
	Openid      string
	Wechat      string
}

type HrEmpp struct {
	Id    int64
	Name  string
	No    string
	Phone string // HANDSET

}

//todo  重新生成VIP
func main() {

	flag.StringVar(&brand, "b", "", "brand  like ugg ecco hdugg")
	flag.Int64Var(&start, "start", 0, "like 20210901")
	flag.Int64Var(&end, "end", 0, "like 20210901")
	flag.Parse()
	if brand == "" {
		fmt.Println("-b can not emp")
		return
	}
	if start == 0 {
		fmt.Println("-start can not emp")
		return
	}
	if end == 0 {
		fmt.Println("-end can not emp")
		return
	}
	initDatabase()

	rs, _ := GetAllRetail(start, end)
	for index, v := range rs {
		UpdateVipStatus(v.Vip, v.Saler, v.Id, v.BillDate)
		fmt.Println(index)
	}
}

func initDatabase() {
	dburl, ok := dbMapUrl[brand]
	if !ok {
		panic("brand error")
	}
	odb, _ = sql.Open("oracle", dburl)
	odb.SetMaxOpenConns(2)
	odb.SetMaxIdleConns(2)
	err := odb.Ping()
	if err != nil {
		panic(err)
	}
}

func GetVipOne(id int64) (Vip, error) {
	var v Vip
	sqlStr := `SELECT ID,VIPNAME,CARDNO,NVL(ONLY_GUIDE_ID,-1),MOBIL,NVL(OPENID,'N'),IS_WECHAT FROM C_CLIENT_VIP WHERE ID = :X1 `
	smt, err := odb.Prepare(sqlStr)
	if err != nil {
		return v, err
	}
	defer smt.Close()
	var vid, guide sql.NullInt64
	var name, card, phone, oid, ifwecaht sql.NullString
	err = smt.QueryRow(id).Scan(&vid, &name, &card, &guide, &phone, &oid, &ifwecaht)
	if err != nil {
		return v, err
	}
	v.Id = vid.Int64
	v.Name = name.String
	v.CardNo = card.String
	v.OnlyGuideId = guide.Int64
	v.Phone = phone.String
	v.Openid = oid.String
	v.Wechat = ifwecaht.String
	return v, nil
}

//获取所有包含VIP的零售单,零售信息
func GetAllRetail(start, end int64) ([]RetailInfo, error) {
	sqlStr := `SELECT ID,DOCNO,C_VIP_ID,SALESREP_ID,BILLDATE FROM M_RETAIL WHERE BILLDATE BETWEEN :X1 AND :X2 AND C_VIP_ID IS  NOT NULL AND M_TYPE = 1  ORDER BY ID`
	rows, err := odb.Query(sqlStr, start, end)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var ms []RetailInfo
	for rows.Next() {
		var id, vid, billdate, saler sql.NullInt64
		var docno sql.NullString
		rows.Scan(&id, &docno, &vid, &saler, &billdate)
		var m RetailInfo
		m.Id = id.Int64
		m.Vip = vid.Int64
		m.BillDate = billdate.Int64
		m.Saler = saler.Int64
		m.DocNO = docno.String
		ms = append(ms, m)
	}
	return ms, nil
}

func GetHrEmpp(id int64) (HrEmpp, error) {
	sqlStr := `SELECT ID, NAME,NO,HANDSET FROM HR_EMPLOYEE WHERE ID = :X1`
	var name, no, phone sql.NullString
	var u HrEmpp
	smt, err := odb.Prepare(sqlStr)
	if err != nil {
		fmt.Println("GetHrEmpp", err)
		return u, err

	}
	defer smt.Close()
	err = smt.QueryRow(id).Scan(&u.Id, &name, &no, &phone)
	if err != nil {
		fmt.Println("GetHrEmpp", err)
		return u, err
	}
	u.Name = name.String
	u.No = no.String
	u.Phone = phone.String
	return u, nil
}

var 是否处理过VIP = make(map[int64]bool)
var 是否已计算纳新 = make(map[int64]struct{})
var 纳新前消费次数 = make(map[int64]int64)
var 纳新后消费次数 = make(map[int64]int64)

func UpdateVipOnlyGuideNull(vip int64) error {
	sqlStr := `UPDATE C_CLIENT_VIP SET ONLY_GUIDE_ID = NULL WHERE ID =:X1`
	smt, err := odb.Prepare(sqlStr)
	if err != nil {
		fmt.Println("UpdateVipOnlyGuideNull", err)
		return err
	}
	defer smt.Close()
	_, err = smt.Exec(vip)
	if err != nil {
		fmt.Println("UpdateVipOnlyGuideNull", vip, err)

		return err
	}
	return nil
}

func UpdateVipOnlyGuide(vip, sid int64) error {
	sqlStr := `UPDATE C_CLIENT_VIP SET ONLY_GUIDE_ID = :x1 WHERE ID =:X2`
	smt, err := odb.Prepare(sqlStr)
	if err != nil {
		fmt.Println("UpdateVipOnlyGuide", err)
		return err
	}
	defer smt.Close()
	_, err = smt.Exec(sid, vip)
	if err != nil {
		fmt.Println("UpdateVipOnlyGuide", vip, sid, err)

		return err
	}
	return nil
}

func UpdateVipStatus(vid, sid, mid, date int64) {

	first := 是否处理过VIP[vid] //第一次处理此VIP
	if !first {            //没处理过
		UpdateVipOnlyGuideNull(vid) //将VIP的专属导购直接清空
		是否处理过VIP[vid] = true        //标记已经清空过专属导购
	}
	vip, err := GetVipOne(vid) //VIP信息
	if err != nil {
		fmt.Println(err)
		return
	}
	saler, err := GetHrEmpp(sid) //导购信息
	if err != nil {
		fmt.Println(err)
		return
	}
	if len(vip.Openid) > 15 && !first {

		frs := QueryFriendsNum(vip.Openid)
		if frs > 0 {

			UpdateQywxFriend(vid, "Y")
		} else {

			UpdateQywxFriend(vid, "N")
		}
	}

	//判断是否已经生成专属导购
	if vip.OnlyGuideId == -1 { //专属导购为空
		//判断是否满足专属导购
		fnum, _ := IfAddQywxFreind(vip.Openid, saler.Phone, date)
		if fnum > 0 { //开单今天有加好友
			ifDel, delTime, _ := IfDelQywxFreind(vip.Openid, saler.Phone, date)
			if !ifDel { //未删除
				UpdateVipOnlyGuide(vid, sid) //更新专属导购
				UpdateMRetailStatus(mid, "纳新初购")
				纳新后消费次数[vid] = 1
			} else { //已删除
				if delTime == date { //当日删除
					UpdateVipOnlyGuideNull(vid)
					num := 纳新前消费次数[vid]
					if num > 0 {
						UpdateMRetailStatus(mid, "非纳新复购")
					} else {
						UpdateMRetailStatus(mid, "非纳新初购")
					}
					纳新前消费次数[vid] = num + 1
				} else { //过后删除
					UpdateVipOnlyGuide(vid, 0) //更新专属导购
					UpdateMRetailStatus(mid, "纳新初购")
					纳新后消费次数[vid] = 1
				}

			}

		} else {
			num := 纳新前消费次数[vid]
			if num > 0 {
				UpdateMRetailStatus(mid, "非纳新复购")
			} else {

				UpdateMRetailStatus(mid, "非纳新初购")
			}
			纳新前消费次数[vid] = num + 1
		}
	} else {
		if vip.OnlyGuideId == 0 { //专属导购待分配
			num := 纳新前消费次数[vid]
			if num > 0 {
				UpdateMRetailStatus(mid, "非纳新复购")
			} else {
				UpdateMRetailStatus(mid, "非纳新初购")
			}
			纳新前消费次数[vid] = num + 1
		} else { //已有专属导购
			num := 纳新后消费次数[vid]
			if num > 0 {
				UpdateMRetailStatus(mid, "纳新复购")
			} else {
				UpdateMRetailStatus(mid, "纳新初购")
			}
			纳新后消费次数[vid] = num + 1
		}
	}

}

//是否删除,以及删除时间
func IfDelQywxFreind(oid, phone string, date int64) (bool, int64, error) {
	sql1 := `select count(*) from qywx_friend where unionid=:x1 and phone =:x2 AND ISACTIVE='Y' AND OK ='N' `
	var num int64
	smt, err := odb.Prepare(sql1)
	if err != nil {
		return false, -1, err
	}
	err = smt.QueryRow(oid, phone).Scan(&num)
	if err != nil {
		fmt.Println("IfDelQywxFreind", err)
		return false, -1, err
	}
	smt.Close()
	if num > 0 {
		var num2 int64
		sql2 := `select TO_CHAR(MODIFIEDDATE,'YYYYMMDD') from qywx_friend where unionid=:x1 and phone =:x2 AND ISACTIVE='Y' AND OK ='N' `
		stm2, err := odb.Prepare(sql2)
		if err != nil {
			fmt.Println("IfDelQywxFreind", err)
			return false, -1, err
		}
		stm2.QueryRow(oid, phone).Scan(&num2)
		stm2.Close()
		return true, num2, nil
	} else {
		return false, date, nil
	}
}

//查询会员和导购当日是否添加好友关系
func IfAddQywxFreind(oid, phone string, date int64) (int64, error) {
	//先判断是否当日加的
	//sql1 := `select count(*) from qywx_friend where unionid=:x1 and phone =:x2 AND ISACTIVE='Y' AND OK ='Y' AND TO_CHAR(MODIFIEDDATE,'YYYYMMDD') <= :x3`
	sql1 := `select count(*) from qywx_friend where unionid=:x1 and phone =:x2 AND ISACTIVE='Y'  AND TO_CHAR(CREATIONDATE,'YYYYMMDD') <= :x3`
	var num int64
	smt, err := odb.Prepare(sql1)
	if err != nil {
		fmt.Println("IfAddQywxFreind", err)
		return -1, err
	}
	defer smt.Close()
	err = smt.QueryRow(oid, phone, date).Scan(&num)
	if err != nil {
		fmt.Println("IfAddQywxFreind", err)
		return -1, err
	}
	return num, nil
}

func UpdateMRetailStatus(mid int64, typ string) error {

	sqlStr := `UPDATE M_RETAIL SET VIP_STATUS = :x1 WHERE ID =:x2`
	smt, err := odb.Prepare(sqlStr)
	if err != nil {
		fmt.Println("UpdateMRetailStatus", err)
		return err
	}
	defer smt.Close()
	_, err = smt.Exec(typ, mid)
	if err != nil {
		fmt.Println("UpdateMRetailStatus", mid, typ, err)

		return err
	}
	return err
}

func QueryFriendsNum(UID string) int64 {
	sqlStr := `SELECT COUNT(*) FROM QYWX_FRIEND WHERE UNIONID =:X1 AND OK='Y'`
	smt, err := odb.Prepare(sqlStr)
	if err != nil {
		fmt.Println("QueryFriendsNum", err)
		return -1
	}
	defer smt.Close()
	var num sql.NullInt64
	row := smt.QueryRow(UID)
	row.Scan(&num)
	return num.Int64
}

func UpdateQywxFriend(vid int64, yesOrNo string) error {

	sqlStr := `UPDATE C_CLIENT_VIP SET IF_QYWX = :x1 where id =:x2`
	smt, err := odb.Prepare(sqlStr)
	if err != nil {
		fmt.Println("UpdateQywxFriend", err)
		return err
	}
	defer smt.Close()
	_, err = smt.Exec(yesOrNo, vid)
	if err != nil {
		fmt.Println("UpdateQywxFriend", vid, yesOrNo, err)
		return err
	}
	return nil

}