opms_backend/opms_parent/script/main.go

package main

import (
	"bytes"
	"context"
	"database/sql"
	"fmt"
	"io/ioutil"
	"strings"
	"time"

	basedao "dashoo.cn/micro/app/dao/base"
	contractdao "dashoo.cn/micro/app/dao/contract"
	projao "dashoo.cn/micro/app/dao/proj"
	basemodel "dashoo.cn/micro/app/model/base"
	model "dashoo.cn/micro/app/model/contract"
	"github.com/gogf/gf/database/gdb"
	"github.com/gogf/gf/frame/g"
	"github.com/gogf/gf/util/gconv"

	"github.com/gogf/gf/os/gtime"
	"github.com/xuri/excelize/v2"
)

func main() {
	// contract()
	// product()
	// collectionplan()
	// collection()
	// dist()
	// prepareuser()
	// prepare()
	// fmt.Println(Usermap)
	// fmt.Println(MapProvince)
	// distContact()
	// projectRelate()
	// updateclloect()
	// updateContractIncharge()
	// updateDist()
}

func updateDist() {
	// select id, nbo_name,nbo_type, distributor_id , distributor_name from proj_business where (distributor_id=0 or distributor_id is null) and distributor_name != '' and distributor_name is not null

	distdao := basedao.NewBaseDistributorDao("prod")
	dist, err := distdao.All()
	if err != nil {
		panic(err)
	}
	projao := projao.NewProjBusinessDao("prod")
	proj, err := projao.All()
	if err != nil {
		panic(err)
	}

	distmap := map[string]*basemodel.BaseDistributor{}
	for _, d := range dist {
		distmap[d.DistName] = d
	}

	txerr := g.DB("prod").Transaction(context.TODO(), func(ctx context.Context, tx *gdb.TX) error {
		for _, p := range proj {
			if p.DistributorName == "" {
				continue
			}
			if p.DistributorId != 0 {
				continue
			}

			d := distmap[p.DistributorName]
			if d == nil {
				fmt.Println("not found", p.DistributorName)
				continue
			}
			fmt.Println(p.DistributorName, d.Id, d.DistName)

			_, err = tx.Update("proj_business",
				map[string]interface{}{
					"distributor_id": d.Id,
				}, "id = ?", p.Id)
			if err != nil {
				return err
			}
		}
		// return fmt.Errorf("测试")
		return nil
	})
	if txerr != nil {
		panic(txerr)
	}
}

func updateContractIncharge() {
	prepareuser()
	contractdao := contractdao.NewCtrContractDao("prod")
	contract, err := contractdao.All()
	if err != nil {
		panic(err)
	}
	txerr := g.DB("prod").Transaction(context.TODO(), func(ctx context.Context, tx *gdb.TX) error {
		for _, c := range contract {
			if c.InchargeId != 0 {
				continue
			}
			if Usermap[c.InchargeName].Id == 0 {
				panic(c.InchargeName + ":seller")
			}
			_, err = tx.Update("ctr_contract",
				map[string]interface{}{
					"incharge_id": Usermap[c.InchargeName].Id,
				}, "id = ?", c.Id)
			if err != nil {
				return err
			}
		}
		// return fmt.Errorf("测试")
		return nil
	})
	if txerr != nil {
		panic(txerr)
	}
}

func updateclloect() {
	contractdao := contractdao.NewCtrContractDao("prod")
	contract, err := contractdao.All()
	if err != nil {
		panic(err)
	}
	txerr := g.DB("prod").Transaction(context.TODO(), func(ctx context.Context, tx *gdb.TX) error {
		for _, c := range contract {
			v, err := tx.GetValue("select sum(collection_amount) from ctr_contract_collection where contract_id=? and appro_status='20' and deleted_time is null", c.Id)
			if err != nil {
				return err
			}
			amount := v.Float64()
			_, err = tx.Update("ctr_contract",
				map[string]interface{}{
					"collected_amount": amount,
				}, "id = ?", c.Id)
			if err != nil {
				return err
			}
		}
		// return fmt.Errorf("测试")
		return nil
	})
	if txerr != nil {
		panic(txerr)
	}
}

func projectRelate() {
	projao := projao.NewProjBusinessDao("prod")
	contractdao := contractdao.NewCtrContractDao("prod")

	fileb, err := ioutil.ReadFile("/home/lai/项目关联.xlsx")
	if err != nil {
		panic(err)
	}

	e, err := excelize.OpenReader(bytes.NewBuffer(fileb))
	if err != nil {
		panic(err)
	}
	coderow, err := e.GetRows("Sheet1")
	if err != nil {
		panic(err)
	}

	txerr := g.DB("prod").Transaction(context.TODO(), func(ctx context.Context, tx *gdb.TX) error {
		for rown, row := range coderow[1:] {
			projcode := strings.TrimSpace(row[0])
			contrcode := strings.TrimSpace(row[1])

			fmt.Println(rown, projcode, contrcode)

			nbo, err := projao.Where("nbo_code = ?", projcode).One()
			if err != nil {
				return err
			}
			if nbo == nil {
				return fmt.Errorf("项目不存在")
			}
			contract, err := contractdao.Where("contract_code = ?", contrcode).One()
			if err != nil {
				return err
			}
			if contract == nil {
				return fmt.Errorf("合同不存在")
			}

			_, err = tx.Update("ctr_contract", map[string]interface{}{
				"cust_id":          nbo.CustId,
				"cust_name":        nbo.CustName,
				"nbo_id":           nbo.Id,
				"nbo_name":         nbo.NboName,
				"is_big":           nbo.IsBig,
				"product_line":     nbo.ProductLine,
				"cust_province_id": nbo.CustProvinceId,
				"cust_province":    nbo.CustProvince,
				"cust_city_id":     nbo.CustCityId,
				"cust_city":        nbo.CustCity,
			}, "contract_code = ?", contrcode)
			if err != nil {
				return err
			}
		}
		// return fmt.Errorf("测试")
		return nil
	})
	if txerr != nil {
		panic(txerr)
	}
}

func distContact() {
	dao := basedao.NewBaseDistributorDao("prod")
	distMap := map[string]*basemodel.BaseDistributor{}
	ent, err := dao.All()
	if err != nil {
		panic(err)
	}
	for _, i := range ent {
		distMap[i.DistName] = i
	}
	fmt.Println(distMap)

	fileb, err := ioutil.ReadFile("/home/lai/渠道信息联系方式.xlsx")
	if err != nil {
		panic(err)
	}

	e, err := excelize.OpenReader(bytes.NewBuffer(fileb))
	if err != nil {
		panic(err)
	}
	contact, err := e.GetRows("Sheet1")
	if err != nil {
		panic(err)
	}

	txerr := g.DB("prod").Transaction(context.TODO(), func(ctx context.Context, tx *gdb.TX) error {
		for rown, row := range contact[1:] {
			distname := strings.TrimSpace(row[2])
			name := strings.TrimSpace(row[3])
			position := strings.TrimSpace(row[5])
			phone := strings.TrimSpace(row[6])
			email := strings.TrimSpace(row[7])
			wechat := strings.TrimSpace(row[8])
			business := strings.TrimSpace(row[9])

			fmt.Println(rown, distname, name, position, phone, email, wechat, business, distMap[distname].Id)

			p := basemodel.BaseDistributorContact{
				DistId:      distMap[distname].Id,
				Name:        name,
				Post:        position,
				Phone:       phone,
				Wechat:      wechat,
				Mail:        email,
				Honorific:   "未知",
				Territory:   business,
				Remark:      "",
				CreatedBy:   1000,
				CreatedName: "dashoo",
				CreatedTime: gtime.Now(),
				UpdatedBy:   1000,
				UpdatedName: "dashoo",
				UpdatedTime: gtime.Now(),
			}

			_, err = tx.Insert("base_distributor_contact", p)
			if err != nil {
				return err
			}
		}
		// return fmt.Errorf("测试")
		return nil
	})
	if txerr != nil {
		panic(txerr)
	}
}

type User struct {
	Id       int
	NickName string
	UserName string
}

var Usermap = map[string]User{}

func prepareuser() {
	users := []User{}
	err := g.DB("prod").Table("sys_user").Structs(&users)
	if err != nil {
		panic(err)
	}
	for _, u := range users {
		Usermap[u.NickName] = u
	}
}

func dist() {
	prepare()
	prepareuser()
	var custtypMap = map[string]string{
		"医院":   "10",
		"科研单位": "20",
		"高校":   "30",
		"疾控":   "40",
		"细胞公司": "50",
		"生物药企": "60",
		"其他":   "90",
	}

	fileb, err := ioutil.ReadFile("/home/lai/渠道信息台账0523(1)(1).xlsx")
	if err != nil {
		panic(err)
	}

	e, err := excelize.OpenReader(bytes.NewBuffer(fileb))
	if err != nil {
		panic(err)
	}
	proxy, err := e.GetRows("代理商列表详情")
	if err != nil {
		panic(err)
	}
	dist, err := e.GetRows("经销商列表详情")
	if err != nil {
		panic(err)
	}

	txerr := g.DB("prod").Transaction(context.TODO(), func(ctx context.Context, tx *gdb.TX) error {

		for rowtype, distlist := range [][][]string{dist, proxy} {

			distType := "10"
			if rowtype == 1 {
				distType = "20"
			}

			for rown, row := range distlist[2:] {
				createdStr := strings.TrimSpace(row[1])
				seller := strings.TrimSpace(row[2])
				name := strings.TrimSpace(row[3])
				province := strings.TrimSpace(row[4])

				var sellerNumStr string
				var product string
				var hisCust string
				var custTypStr string
				var proxydistrict string
				var startStr string
				var endStr string
				var business string
				var capitalStr string
				var register string
				if len(row) > 5 {
					business = strings.TrimSpace(row[5])
					capitalStr = strings.TrimSpace(row[6])
					register = strings.TrimSpace(row[7])
					if len(row) > 8 {
						sellerNumStr = strings.TrimSpace(row[8])
						if len(row) > 9 {
							product = strings.TrimSpace(row[9])
							if len(row) > 10 {
								hisCust = strings.TrimSpace(row[10])
								if len(row) > 11 {
									if distType == "10" {
										custTypStr = strings.TrimSpace(row[11])
									} else {
										// fmt.Println(row)
										proxydistrict = strings.TrimSpace(row[12])
										startStr = strings.TrimSpace(row[13])
										endStr = strings.TrimSpace(row[14])
										custTypStr = strings.TrimSpace(row[15])
									}
								}
							}
						}
					}
				}

				created, err := time.Parse("2006/01/02 15:04:05", createdStr)
				if err != nil {
					panic(fmt.Errorf("%s, %s", createdStr, err.Error()))
				}
				start, err := time.Parse("2006/01/02", startStr)
				if err != nil {
					if distType == "10" {
						start = time.Time{}
					} else {
						panic(fmt.Errorf("%s, %s", startStr, err.Error()))
					}
				}
				end, err := time.Parse("2006/01/02", endStr)
				if err != nil {
					if distType == "10" {
						start = time.Time{}
					} else {
						panic(fmt.Errorf("%s, %s", endStr, err.Error()))
					}
				}
				sellerNum := gconv.Int(sellerNumStr)
				capital := gconv.Float64(capitalStr)

				var custypes string
				if custTypStr == "" {
					custypes = ""
				} else {
					custTypeslice := strings.Split(custTypStr, "，")
					custypeslice := []string{}
					for _, t := range custTypeslice {
						if t == "企业" {
							continue
						}
						if t == "医疗" {
							continue
						}
						if t == "科研" {
							continue
						}
						if t == "细胞企业" {
							continue
						}
						if t == "生物药企、医院" {
							custypeslice = append(custypeslice, custtypMap["生物药企"])
							custypeslice = append(custypeslice, custtypMap["医院"])
							continue
						}
						if custtypMap[t] == "" {
							panic(t + ":custypeslice")
						}
						custypeslice = append(custypeslice, custtypMap[t])
					}
					custypes = strings.Join(custypeslice, ",")
					if Usermap[seller].Id == 0 {
						panic(seller + ":seller")
					}
				}

				fmt.Println(rown, distType, created, seller, name, province, MapProvince[province], business, capital, register, sellerNum, product, hisCust, custypes, proxydistrict, start, end)

				p := basemodel.BaseDistributor{
					DistCode:         "",
					DistName:         name,
					AbbrName:         "",
					DistDesc:         "",
					DistBoss:         "",
					DistBossPhone:    "",
					ProvinceId:       MapProvince[province],
					ProvinceDesc:     province,
					BusinessScope:    business,
					BelongSaleId:     Usermap[seller].Id,
					BelongSale:       seller,
					Capital:          capital,
					SaleNum:          sellerNum,
					InvoiceHeader:    "",
					DistType:         distType,
					CustomerType:     custypes,
					ExistedProduct:   product,
					AssistantSaleId:  "",
					AssistantSale:    "",
					RegisterDistrict: register,
					HistoryCustomer:  hisCust,
					ProxyStartTime:   gtime.NewFromTime(start),
					ProxyEndTime:     gtime.NewFromTime(end),
					ProxyDistrict:    proxydistrict,
					ContractUrl:      "",
					Remark:           "",
					CreatedBy:        1000,
					CreatedName:      "dashoo",
					CreatedTime:      gtime.Now(),
					UpdatedBy:        1000,
					UpdatedName:      "dashoo",
					UpdatedTime:      gtime.Now(),
				}

				_, err = tx.Insert("base_distributor", p)
				if err != nil {
					return err
				}
			}
		}
		// return fmt.Errorf("测试")
		return nil
	})
	if txerr != nil {
		panic(txerr)
	}
}

func collection() {
	var typMap = map[string]string{
		"电汇": "10",
		"承兑": "20",
		"现金": "30",
	}

	var contractMap = map[string]*model.CtrContract{}

	fileb, err := ioutil.ReadFile("/home/lai/OMS合同管理17-23合同信息0519(1).xlsx")
	if err != nil {
		panic(err)
	}

	e, err := excelize.OpenReader(bytes.NewBuffer(fileb))
	if err != nil {
		panic(err)
	}
	rows2021_2023, err := e.GetRows("2021-2023年回款管理")
	if err != nil {
		panic(err)
	}
	rows2017_2020, err := e.GetRows("2017-2020年回款管理")
	if err != nil {
		panic(err)
	}
	rows := append(rows2021_2023[1:], rows2017_2020[1:]...)

	txerr := g.DB("prod").Transaction(context.TODO(), func(ctx context.Context, tx *gdb.TX) error {

		for rown, row := range rows {
			custname := strings.TrimSpace(row[0])
			contractcode := strings.TrimSpace(row[1])
			amountStr := strings.TrimSpace(row[2])
			typ := strings.TrimSpace(row[3])
			var ctimeStr string
			if len(row) > 5 {
				ctimeStr = strings.TrimSpace(row[5])
			}

			if ctimeStr == "" {
				ctimeStr = "0001/1/2"
			}

			ctime, err := time.Parse("2006/1/2", ctimeStr)
			if err != nil {
				ctime, err = time.Parse("01-02-06", ctimeStr)
			}
			if err != nil {
				ctime, err = time.Parse("2006.1.2", ctimeStr)
			}
			if err != nil {
				if ctimeStr == "" {
					ctime = time.Time{}
				} else {
					panic(fmt.Errorf("%s, %s", ctimeStr, err.Error()))
				}
			}
			amount := gconv.Float64(amountStr)

			fmt.Println(rown, custname, contractcode, amount, typ, ctime)

			if _, ok := contractMap[contractcode]; !ok {
				c := &model.CtrContract{}
				err := tx.GetStruct(c, "select * from ctr_contract where contract_code = ?", contractcode)
				if err != nil {
					fmt.Println(c.ContractCode)
					return err
				}
				contractMap[contractcode] = c
			}

			p := model.CtrContractCollection{
				PlanId:             0,
				CustId:             0,
				CustName:           custname,
				ContractId:         contractMap[contractcode].Id,
				ContractCode:       contractMap[contractcode].ContractCode,
				CollectionDatetime: gtime.NewFromTime(ctime),
				CollectionAmount:   amount,
				CollectionType:     typMap[typ],
				ApproStatus:        "20",
				ContractAmount:     contractMap[contractcode].ContractAmount,
				Remark:             "",
				CreatedBy:          1000,
				CreatedName:        "dashoo",
				CreatedTime:        gtime.Now(),
				UpdatedBy:          1000,
				UpdatedName:        "dashoo",
				UpdatedTime:        gtime.Now(),
			}

			_, err = tx.Insert("ctr_contract_collection", p)
			if err != nil {
				return err
			}
		}
		return nil
		// return fmt.Errorf("测试")
	})
	fmt.Println(contractMap)
	if txerr != nil {
		panic(txerr)
	}
}

func collectionplan() {
	var statusMap = map[string]string{
		"已回款": "30",
		"未回款": "10",
	}

	var contractMap = map[string]*model.CtrContract{}

	fileb, err := ioutil.ReadFile("/home/lai/OMS合同管理17-23合同信息0519(1).xlsx")
	if err != nil {
		panic(err)
	}

	e, err := excelize.OpenReader(bytes.NewBuffer(fileb))
	if err != nil {
		panic(err)
	}
	rows2021_2023, err := e.GetRows("2021-2023年回款计划")
	if err != nil {
		panic(err)
	}
	rows := rows2021_2023[1:]

	txerr := g.DB("prod").Transaction(context.TODO(), func(ctx context.Context, tx *gdb.TX) error {

		for rown, row := range rows {
			custname := strings.TrimSpace(row[0])
			contractcode := strings.TrimSpace(row[1])
			amountStr := strings.TrimSpace(row[2])
			status := strings.TrimSpace(row[3])
			planTimeStr := strings.TrimSpace(row[4])
			scaleStr := strings.TrimSpace(row[5])
			var criteria string
			if len(row) > 6 {
				criteria = strings.TrimSpace(row[6])
			}

			if planTimeStr == "" {
				planTimeStr = "0001/1/2"
			}

			planTime, err := time.Parse("2006/1/2", planTimeStr)
			if err != nil {
				planTime, err = time.Parse("01-02-06", planTimeStr)
			}
			if err != nil {
				if planTimeStr == "" {
					planTime = time.Time{}
				} else {
					panic(fmt.Errorf("%s, %s", planTimeStr, err.Error()))
				}
			}
			amount := gconv.Float64(amountStr)
			scale := gconv.Float64(strings.Replace(scaleStr, "%", "", -1))

			var CashedAmount float64
			if status == "已回款" {
				CashedAmount = amount
			}

			fmt.Println(rown, custname, contractcode, amount, status, scale, criteria)

			if _, ok := contractMap[contractcode]; !ok {
				c := &model.CtrContract{}
				err := tx.GetStruct(c, "select * from ctr_contract where contract_code = ?", contractcode)
				if err != nil {
					fmt.Println(c.ContractCode)
					return err
				}
				contractMap[contractcode] = c
			}

			p := model.CtrContractCollectionPlan{
				CustId:         0,
				CustName:       custname,
				ContractId:     contractMap[contractcode].Id,
				ContractCode:   contractMap[contractcode].ContractCode,
				ContractStatus: statusMap[status],
				PlanAmount:     amount,
				PlanDatetime:   gtime.NewFromTime(planTime),
				PlanScale:      scale,
				PlanCondition:  criteria,
				CashedAmount:   CashedAmount,
				CashedDatetime: nil,
				Remark:         "",
				CreatedBy:      1000,
				CreatedName:    "dashoo",
				CreatedTime:    gtime.Now(),
				UpdatedBy:      1000,
				UpdatedName:    "dashoo",
				UpdatedTime:    gtime.Now(),
			}

			_, err = tx.Insert("ctr_contract_collection_plan", p)
			if err != nil {
				return err
			}
		}
		// return fmt.Errorf("测试")
		return nil
	})
	fmt.Println(contractMap)
	if txerr != nil {
		panic(txerr)
	}
}

func product() {
	var ProdClassMap = map[string]string{
		"BIOBANK":  "10",
		"Biobank":  "10",
		"CELLSOP":  "20",
		"CellSOP":  "20",
		"CellsSOP": "20",
		"LIMS":     "30",
		"LIMS+基因":  "30",
		"MCS":      "60",
		"咨询服务":     "70",
		"外购":       "90",
		"智能硬件":     "40",
		"液氮罐":      "50",
		"设备":       "",
	}
	var productMap = map[string]*basemodel.BaseProduct{}
	var contractMap = map[string]*model.CtrContract{}

	fileb, err := ioutil.ReadFile("/home/lai/OMS合同管理17-23合同信息0519(1).xlsx")
	if err != nil {
		panic(err)
	}

	e, err := excelize.OpenReader(bytes.NewBuffer(fileb))
	if err != nil {
		panic(err)
	}
	rows2021_2023, err := e.GetRows("2021-2023年产品信息")
	if err != nil {
		panic(err)
	}
	rows2017_2020, err := e.GetRows("2017-2020年产品信息")
	if err != nil {
		panic(err)
	}
	rows := append(rows2021_2023[1:], rows2017_2020[1:]...)

	txerr := g.DB("prod").Transaction(context.TODO(), func(ctx context.Context, tx *gdb.TX) error {

		for rown, row := range rows {
			contractcode := strings.TrimSpace(row[0])
			ProdClass := strings.TrimSpace(row[1])
			ProdName := strings.TrimSpace(row[2])
			ProdCode := strings.TrimSpace(row[3])
			priceStr := strings.TrimSpace(row[4])
			numStr := strings.TrimSpace(row[5])
			sumStr := strings.TrimSpace(row[6])
			var zhibaoStr string
			var maintainStr string
			var maintainTerm string
			var acceptTimeStr string
			if len(row) > 7 {
				zhibaoStr = strings.TrimSpace(row[7])
				if len(row) > 8 {
					maintainStr = strings.TrimSpace(row[8])
					if len(row) > 9 {
						maintainTerm = strings.TrimSpace(row[9])
						if len(row) > 10 {
							acceptTimeStr = strings.TrimSpace(row[10])
						}
					}
				}
			}

			acceptTime, err := time.Parse("2006/1/2", acceptTimeStr)
			if err != nil {
				acceptTime, err = time.Parse("01-02-06", acceptTimeStr)
			}
			if err != nil {
				acceptTime, err = time.Parse("2006年1月2日", acceptTimeStr)
			}
			if err != nil {
				if acceptTimeStr == "" {
					acceptTime = time.Time{}
				} else {
					panic(fmt.Errorf("%s, %s", acceptTimeStr, err.Error()))
				}
			}
			price := gconv.Float64(priceStr)
			num := gconv.Int(numStr)
			sum := gconv.Float64(sumStr)
			zhibao := gconv.Int(zhibaoStr)
			maintain := gconv.Int(maintainStr)

			fmt.Println(rown, contractcode, ProdClass, ProdName, ProdCode, price,
				num, sum, zhibao, maintain, maintainTerm, acceptTime)

			p := basemodel.BaseProduct{
				ProdCode:    ProdCode,
				ProdName:    ProdName,
				ProdClass:   ProdClassMap[ProdClass],
				GuidPrice:   0,
				DistPrice:   0,
				AgentPrice:  0,
				MarketPrice: 0,
				Remark:      "",
				CreatedBy:   1000,
				CreatedName: "dashoo",
				CreatedTime: gtime.Now(),
				UpdatedBy:   1000,
				UpdatedName: "dashoo",
				UpdatedTime: gtime.Now(),
				DeletedTime: gtime.Now(),
			}
			if _, ok := productMap[p.ProdName]; !ok {
				dbexistp := &basemodel.BaseProduct{}
				err := tx.GetStruct(dbexistp, "select * from base_product where prod_name = ?", p.ProdName)
				if err == sql.ErrNoRows {
					id, err := tx.InsertAndGetId("base_product", p)
					if err != nil {
						return err
					}
					p.Id = int(id)
					productMap[p.ProdName] = &p
				} else {
					if err != nil {
						return err
					}
					productMap[p.ProdName] = dbexistp
				}
			}
			if _, ok := contractMap[contractcode]; !ok {
				c := &model.CtrContract{}
				err := tx.GetStruct(c, "select * from ctr_contract where contract_code = ?", contractcode)
				if err != nil {
					fmt.Println(c.ContractCode)
					return err
				}
				contractMap[contractcode] = c
			}

			cp := model.CtrContractProduct{
				ContractId:        contractMap[contractcode].Id,
				ContractCode:      contractMap[contractcode].ContractCode,
				ProdId:            productMap[p.ProdName].Id,
				ProdCode:          productMap[p.ProdName].ProdCode,
				ProdName:          productMap[p.ProdName].ProdName,
				ProdClass:         productMap[p.ProdName].ProdClass,
				ProdNum:           num,
				MaintTerm:         0,
				SugSalesPrice:     0,
				TranPrice:         price,
				ContractPrive:     contractMap[contractcode].ContractAmount,
				PurchaseCost:      0,
				DevCost:           0,
				MaintainCost:      0,
				DirectCost:        0,
				MaintainPeriod:    zhibao * 365,
				WarrantPeriod:     maintain * 365,
				MaintainStartTime: nil,
				MaintainRemark:    maintainTerm,
				AcceptTime:        gtime.NewFromTime(acceptTime),
				Remark:            "",
				CreatedBy:         1000,
				CreatedName:       "dashoo",
				CreatedTime:       gtime.Now(),
				UpdatedBy:         1000,
				UpdatedName:       "dashoo",
				UpdatedTime:       gtime.Now(),
			}
			_, err = tx.Insert("ctr_contract_product", cp)
			if err != nil {
				return err
			}
		}
		// return fmt.Errorf("测试")
		return nil
	})
	fmt.Println(contractMap)
	fmt.Println(productMap)
	fmt.Println(ProdClassMap)
	if txerr != nil {
		panic(txerr)
	}
}

func contract() {
	prepare()
	contract := parse()
	fmt.Println(MapProductLine)
	txerr := g.DB("prod").Transaction(context.TODO(), func(ctx context.Context, tx *gdb.TX) error {
		_, err := tx.Insert("ctr_contract", contract)
		return err
	})
	if txerr != nil {
		panic(txerr)
	}
}

var MapProvince = map[string]int{}
var MapCity = map[string]int{}
var MapProductLine = map[string]string{
	"BIOBANK": "10",
	"CELLSOP": "20",
	"LIMS+基因": "30",
	"MCS":     "60",
	"咨询服务":    "70",
	"外购":      "90",
	"智能硬件":    "40",
	"石油":      "80",
}
var Mapcontracttype = map[string]string{
	"销售合同": "XS",
	"技术合同": "JS",
}
var MapSignatoryType = map[string]string{
	"终端用户": "10",
	"经销商":  "20",
	"代理商":  "30",
}

func prepare() {
	dao := basedao.NewBaseDistrictDao("prod")
	province, err := dao.Where("dist_level = 1").All()
	if err != nil {
		panic(err)
	}
	for _, p := range province {
		MapProvince[p.DistName] = p.Id
	}
	city, err := dao.Where("dist_level = 2").All()
	if err != nil {
		panic(err)
	}
	for _, c := range city {
		MapCity[c.DistName] = c.Id
	}

}

func parse() []model.CtrContract {
	fileb, err := ioutil.ReadFile("/home/lai/OMS合同管理17-23合同信息0519(1).xlsx")
	if err != nil {
		panic(err)
	}

	e, err := excelize.OpenReader(bytes.NewBuffer(fileb))
	if err != nil {
		panic(err)
	}
	rows2021_2023, err := e.GetRows("2021-2023年合同基本信息")
	if err != nil {
		panic(err)
	}
	rows2017_2020, err := e.GetRows("2017-2020年合同基本信息")
	if err != nil {
		panic(err)
	}
	rows := append(rows2021_2023[1:], rows2017_2020[1:]...)

	contract := []model.CtrContract{}
	for rown, row := range rows {

		code := strings.TrimSpace(row[0])
		name := strings.TrimSpace(row[1])
		custname := strings.TrimSpace(row[2])
		nobname := strings.TrimSpace(row[3])
		province := strings.TrimSpace(row[4])
		city := strings.TrimSpace(row[5])
		amountstr := strings.TrimSpace(row[6])
		InchargeName := strings.TrimSpace(row[7])
		ContractType := strings.TrimSpace(row[8])
		ContractSignTimestr := strings.TrimSpace(row[9])
		ContractEndTimestr := strings.TrimSpace(row[10])
		SignatoryName := strings.TrimSpace(row[11])
		ProductLine := strings.TrimSpace(row[12])
		SignatoryUnit := strings.TrimSpace(row[13])
		SignatoryType := strings.TrimSpace(row[14])

		if ContractEndTimestr == "" {
			ContractEndTimestr = "0001/1/2"
		}
		if ContractSignTimestr == "" {
			ContractSignTimestr = "0001/1/2"
		}

		amount := gconv.Float64(amountstr)
		ContractSignTime, err := time.Parse("2006/1/2", ContractSignTimestr)
		if err != nil {
			ContractSignTime, err = time.Parse("01-02-06", ContractSignTimestr)
		}
		if err != nil {
			panic(fmt.Errorf("%s, %s", ContractSignTimestr, err.Error()))
		}
		ContractEndTime, err := time.Parse("2006/1/2", ContractEndTimestr)
		if err != nil {
			ContractEndTime, err = time.Parse("01-02-06", ContractEndTimestr)
		}
		if err != nil {
			panic(fmt.Errorf("%s, %s", ContractEndTimestr, err.Error()))
		}
		provinceId := MapProvince[province]
		cityId := MapCity[city]
		var DistributorName string
		if SignatoryType == "经销商" || SignatoryType == "代理商" {
			DistributorName = SignatoryUnit
		}

		fmt.Println(rown, code, name, custname, nobname, province, city,
			amount, InchargeName, ContractType, ContractSignTime,
			ContractEndTime, SignatoryName, ProductLine, SignatoryType)

		contract = append(contract, model.CtrContract{
			ContractCode:        code,
			ContractName:        name,
			CustId:              0,
			CustName:            custname,
			ProductLine:         MapProductLine[ProductLine],
			IsBig:               "20",
			CustProvinceId:      provinceId,
			CustProvince:        province,
			CustCityId:          cityId,
			CustCity:            city,
			NboId:               0,
			NboName:             nobname,
			ApproStatus:         "30",
			ContractType:        Mapcontracttype[ContractType],
			ContractAmount:      amount,
			InvoiceAmount:       0,
			CollectedAmount:     0,
			ContractStartTime:   gtime.NewFromTime(ContractSignTime),
			ContractEndTime:     gtime.NewFromTime(ContractEndTime),
			ContractSignTime:    gtime.NewFromTime(ContractSignTime),
			InchargeId:          0,
			InchargeName:        InchargeName,
			SignatoryId:         0,
			SignatoryName:       SignatoryName,
			SignatoryType:       MapSignatoryType[SignatoryType],
			SignatoryUnit:       SignatoryUnit,
			EarnestMoney:        0,
			CustSignatoryId:     0,
			CustSignatoryName:   "",
			DistributorId:       0,
			DistributorName:     DistributorName,
			ServiceFeeAgreement: "",
			Remark:              "",
			CreatedBy:           1000,
			CreatedName:         "dashoo",
			CreatedTime:         gtime.Now(),
			UpdatedBy:           1000,
			UpdatedName:         "dashoo",
			UpdatedTime:         gtime.Now(),
		})
	}
	return contract
}