package main
import (
"fmt"
"github.com/360EntSecGroup-Skylar/excelize"
"github.com/jinzhu/gorm"
_ "github.com/jinzhu/gorm/dialects/mysql"
"strconv"
"strings"
"time"
)
var Pdb *gorm.DB
type Data struct {
Rindex int
CountryName string
Sort int64
CountryId int64
LineName string
LineType string
Cname string //公司名称
UnitPrice float64
UnitGtePrice float64
UnitWeight int64
LimitWeight int64
RegFee float64
SpecialFee float64
ElectroFee float64
HasOversizeFee int64
OverlenFee float64
FuleFeeRate float64 //燃油附加费比例
BusyFee float64
Cfee float64
LowWeight int64
ServiceFeeRate float64
TariffFeeRate float64
InsureFeeRate float64
DaiyouFee float64
WeightRate float64
}
var DataMap = make(map[int]Data)
//线路表
type ZwPackLine struct {
Id int64 `json:"id"`
Name string `json:"name"`
Type string `json:"type"`
Spot string `json:"spot"`
Process string `json:"process"`
CreateTime int64 `json:"create_time"`
UpdateTime int64 `json:"update_time"`
}
type ZwPackCountryLine struct {
Id int64 `json:"id"`
LineId int64 `json:"line_id"`
Sort int64 `json:"sort"`
CountryId int64 `json:"country_id"`
CreateTime int64 `json:"create_time"`
UpdateTime int64 `json:"update_time"`
}
//线路公司
type ZwPackCompany struct {
Id int64 `json:"id"`
LineId int64 `json:"line_id"`
Cname string `json:"cname"`
Score int `gorm:"default:5"`
IsRec int `json:"is_rec"`
CreateTime int64 `json:"create_time"`
UpdateTime int64 `json:"update_time"`
}
//国家
type ZwCountries struct {
Id int64
ChineseName string
}
//template
type ZwPackFareTemplate struct {
Id int64 `json:"id"`
Name string `json:"name"`
UnitPrice float64 `json:"unit_price"`
UnitGtePrice float64 `json:"unit_gte_price"`
UnitWeight int64 `json:"unit_weight"`
}
//companyTemplateRelation
type ZwPackCtemplate struct {
Id int64 `json:"id"`
CompanyId int64 `json:"company_id"`
TemplateId int64 `json:"template_id"`
CreateTime int64 `json:"create_time"`
UpdateTime int64 `json:"update_time"`
}
type ZwPackFareCountry struct {
Id int64 `json:"id"`
Tid int64 `json:"tid"`
CountryId int64 `json:"country_id"`
LowWeight int64 `json:"low_weight"`
WeightLimit int64 `json:"weight_limit"`
UnitPrice float64 `json:"unit_price"`
UnitWeight int64 `json:"unit_weight"`
UnitGtePrice float64 `json:"unit_gte_price"`
Cfee float64 `json:"cfee"`
RegFee float64 `json:"reg_fee"`
FuleFeeRate float64 `json:"fule_fee_rate"`
BusyFee float64 `json:"busy_fee"`
SpecialFee float64 `json:"special_fee"`
OverlenFee float64 `json:"overlen_fee"`
HasOversizeFee int64 `json:"has_oversize_fee"`
ElectroFee float64 `json:"electro_fee"`
PackageFee float64 `json:"package_fee"`
ServiceFeeRate float64 `json:"service_fee_rate"`
TariffFeeRate float64 `json:"tariff_fee_rate"`
InsureFeeRate float64 `json:"insure_fee_rate"`
DaiyouFee float64 `json:"daiyou_fee"`
VolumeWeight float64 `json:"volume_weight"`
WeightRate float64 `json:"weight_rate"`
CreateTime int64 `json:"create_time"`
UpdateTime int64 `json:"update_time"`
}
var typeMap = map[string]string{
"经济": "economic",
"快速": "fast",
"特快": "express",
"特殊": "special",
}
var countryId = make(map[string]int64)
func init() {
var err error
Pdb, err = gorm.Open("mysql", "root:password@tcp(127.0.0.1:3306)/databaseName?charset=utf8&parseTime=True&loc=Local")
if err != nil {
panic(err)
return
}
Pdb.SingularTable(true)
fmt.Println("链接mysql成功")
}
func main() {
f, err := excelize.OpenFile("/Users/DavidWang/goweb/project/excel/template1.28.xlsx")//excel文件路径
if err != nil {
fmt.Println(err)
return
}
// 获取 Sheet1 上所有单元格
rows := f.GetRows("Sheet1")
for reindex, row := range rows {
newData := Data{}
for index, cell := range row {
switch index {
case 1:
_, cid := getCountryId(cell)
if cid == 0 {
break
}
newData.CountryName = cell
newData.CountryId = cid
case 2:
val, ok := typeMap[strings.Replace(cell, " ", "", -1)]
if !ok {
break
}
newData.LineType = val
case 3:
newData.Sort = decimalInt(cell)
case 4:
newData.LineName = cell
case 5:
newData.Cname = cell
case 6:
newData.UnitPrice = decimalFloat(cell)
case 7:
newData.UnitGtePrice = decimalFloat(cell)
case 8:
newData.UnitWeight = decimalInt(cell)
case 9: //清关费
newData.RegFee = decimalFloat(cell)
case 10:
newData.SpecialFee = decimalFloat(cell)
case 11:
newData.ElectroFee = decimalFloat(cell)
case 12:
newData.HasOversizeFee = decimalInt(cell)
case 13:
if len(cell) == 0 {
newData.OverlenFee = 0
} else {
newData.OverlenFee = decimalFloat(cell)
}
case 14:
newData.FuleFeeRate = decimalFloat(cell)
case 15:
newData.BusyFee = decimalFloat(cell)
case 16:
newData.Cfee = decimalFloat(cell)
case 17:
newData.LowWeight = decimalInt(cell)
case 18:
newData.ServiceFeeRate = decimalFloat(cell)
case 19:
newData.TariffFeeRate = decimalFloat(cell)
case 20:
newData.InsureFeeRate = decimalFloat(cell)
case 21:
newData.DaiyouFee = decimalFloat(cell)
case 22:
rate := decimalFloat(cell)
if rate <= 0 {
rate = 1
}
newData.WeightRate = rate
case 23:
newData.LimitWeight = decimalInt(cell)
}
}
newData.Rindex = reindex
DataMap[reindex] = newData
}
//todo：这里可以使用协程
for _, data := range DataMap {
if data.LineName == "" {
continue
}
if data.CountryId == 0 {
continue
}
tx := Pdb.Begin()
err, lineId := insertLine(tx, data.LineName, data.LineType)
if err != nil {
fmt.Println("写入线路表失败", data.LineName, data.LineType, err)
tx.Rollback()
continue
}
//国家线路表
err, _ = insertCountryLine(tx, data.CountryId, lineId, data.Sort)
if err != nil {
fmt.Println("写入国家线路表失败", data.LineName, data.CountryId, err)
tx.Rollback()
continue
}
fmt.Println("写入线路表成功", data.LineName)
err, companyId := insertCompany(tx, lineId, data.Cname)
if err != nil {
fmt.Println("写入公司表失败表失败", data.LineName, data.LineType, err)
tx.Rollback()
continue
}
fmt.Println("写入公司表成功", data.Cname)
err, templateId := insertTemplate(tx, data.CountryName+data.LineName+data.Cname, data.UnitPrice, data.UnitGtePrice, data.UnitWeight)
if err != nil {
fmt.Println("写入模板表失败表失败", data.Cname, data.LineName, err)
tx.Rollback()
continue
}
fmt.Println("写入模板表成功", data.Cname)
err1, _ := insertCtemplate(tx, companyId, templateId)
if err1 != nil {
fmt.Println("写入公司模板关联表失败", companyId, templateId, err1)
tx.Rollback()
continue
}
fmt.Println("写入公司模板关联表成功", data.Cname, templateId)
err2, _ := insertFareCountry(tx, templateId, data)
if err2 != nil {
fmt.Println("运费模板关联国家失败", countryId, templateId, err2)
tx.Rollback()
continue
}
tx.Commit()
fmt.Println("恭喜你成功啦", data.LineName)
}
return
//todo excelize根据指定单元格获取值会有问题(取不到),不建议使用
//var cellTop = []string{"B", "C", "D", "E", "F", "G", "H", "I", "L", "J", "K", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V"}
//// 获取工作表中指定单元格的值
//var startPos int64 = 4
//for {
// if startPos == 691 {
// break
// }
// newData := Data{}
// for _, cell := range cellTop {
// index := fmt.Sprintf("%s%d", "C", 265)
// cellVal := f.GetCellValue("Sheet1", index)
// fmt.Println(index, cellVal)
// return
// switch cell {
// case "B":
// _, cid := getCountryId(cellVal)
// newData.CountryId = cid
// case "C":
// val, ok := typeMap[strings.Replace(cellVal, " ", "", -1)]
// if !ok {
// fmt.Println("cellVal", cellVal, startPos)
// return
// continue
// }
// newData.LineType = val
// }
//
// //println(cellVal)
// }
// startPos++
// println("startPos", startPos)
// DataMap[startPos] = newData
//}
//for index, data := range DataMap {
// fmt.Println("index,", index)
// fmt.Println("LineType,", data.LineType)
// fmt.Println("CountryId,", data.CountryId)
//}
//return
//
//cell := f.GetCellValue("Sheet1", "B5")
//println(cell)
//println(strings.Replace("你 好啊 哈 哈", " ", "", -1))
//countryId["美国"] = 1
//countryId["英国"] = 2
//cid, ok := countryId["英国"]
//if !ok {
// println("not found ", ok)
// return
//}
//println("cid=", cid)
//tx := Pdb.Begin()
//
//tx.Commit()
//
//return
}
func insertFareCountry(tx *gorm.DB, templateId int64, newData Data) (error, int64) {
if newData.LimitWeight == 0 {
newData.LimitWeight = 1000000
}
fareCountry := ZwPackFareCountry{
Tid: templateId,
CountryId: newData.CountryId,
LowWeight: newData.LowWeight,
WeightLimit: newData.LimitWeight,
UnitPrice: newData.UnitPrice,
UnitWeight: newData.UnitWeight,
UnitGtePrice: newData.UnitGtePrice,
Cfee: newData.Cfee,
RegFee: newData.RegFee,
FuleFeeRate: newData.FuleFeeRate,
BusyFee: newData.BusyFee,
SpecialFee: newData.SpecialFee,
OverlenFee: newData.OverlenFee,
HasOversizeFee: newData.HasOversizeFee,
ElectroFee: newData.ElectroFee,
PackageFee: 8,
ServiceFeeRate: newData.ServiceFeeRate,
TariffFeeRate: newData.TariffFeeRate,
InsureFeeRate: newData.InsureFeeRate,
DaiyouFee: newData.DaiyouFee,
VolumeWeight: 1.1,
WeightRate: newData.WeightRate,
CreateTime: time.Now().Unix(),
UpdateTime: time.Now().Unix(),
}
existFare := ZwPackFareCountry{
Tid: templateId,
CountryId: newData.CountryId,
}
Pdb.Where("tid= ? and country_id = ?", templateId, newData.CountryId).First(&existFare)
if existFare.Id > 0 {
fareCountry.Id = existFare.Id
}
err := tx.Table("zw_pack_fare_country").Save(&fareCountry).Error
if err != nil {
return err, 0
}
return nil, fareCountry.Id
}
func insertCtemplate(tx *gorm.DB, companyId int64, TemplateId int64) (error, int64) {
ctemplate := ZwPackCtemplate{
CompanyId: companyId,
TemplateId: TemplateId,
CreateTime: time.Now().Unix(),
UpdateTime: time.Now().Unix(),
}
Pdb.Where("company_id= ? and template_id = ?", companyId, TemplateId).First(&ctemplate)
if ctemplate.Id > 0 {
return nil, ctemplate.Id
}
err := tx.Table("zw_pack_ctemplate").Create(&ctemplate).Error
if err != nil {
return err, 0
}
return nil, ctemplate.Id
}
func insertCountryLine(tx *gorm.DB, countryId int64, LineId int64, sort int64) (error, int64) {
cline := ZwPackCountryLine{
LineId: LineId,
CountryId: countryId,
Sort: sort,
CreateTime: time.Now().Unix(),
UpdateTime: time.Now().Unix(),
}
existLine := ZwPackCountryLine{
LineId: LineId,
CountryId: countryId,
}
Pdb.Where("country_id= ? and line_id = ?", countryId, LineId).First(&existLine)
if existLine.Id > 0 {
cline.Id = existLine.Id
}
err := tx.Table("zw_pack_country_line").Save(&cline).Error
if err != nil {
return err, 0
}
return nil, cline.Id
}
func insertLine(tx *gorm.DB, name string, lineTypeStr string) (error, int64) {
line := ZwPackLine{
Name: name,
Type: lineTypeStr,
Spot: "运输时效快，运费低",
Process: "20-30天",
CreateTime: time.Now().Unix(),
}
Pdb.Where("name = ?", name).First(&line)
if line.Id > 0 {
return nil, line.Id
}
err := tx.Table("zw_pack_line").Create(&line).Error
if err != nil {
return err, 0
}
return nil, line.Id
}
func insertCompany(tx *gorm.DB, lineId int64, cname string) (error, int64) {
company := ZwPackCompany{
LineId: lineId,
Cname: cname,
Score: 5,
IsRec: 0,
CreateTime: time.Now().Unix(),
UpdateTime: time.Now().Unix(),
}
Pdb.Where("line_id= ? and cname = ?", lineId, cname).First(&company)
if company.Id > 0 {
return nil, company.Id
}
err := tx.Table("zw_pack_company").Create(&company).Error
if err != nil {
return err, 0
}
return nil, company.Id
}
func insertTemplate(tx *gorm.DB, tName string, unitPrice float64, UnitGtePrice float64, UnitWeight int64) (error, int64) {
template := ZwPackFareTemplate{
Name: tName,
UnitPrice: unitPrice,
UnitGtePrice: UnitGtePrice,
UnitWeight: UnitWeight,
}
Pdb.Where("name= ?", tName).First(&template)
if template.Id > 0 {
return nil, template.Id
}
err := tx.Table("zw_pack_fare_template").Create(&template).Error
if err != nil {
return err, 0
}
return nil, template.Id
}
/**
**根据国家名称获取国家对应的id
*/
func getCountryId(countryName string) (error, int64) {
var country []ZwCountries
err := Pdb.Raw("SELECT id,name FROM zw_countries WHERE chinese_name=?", countryName).First(&country).Error
if err != nil {
return err, 0
}
if len(country) == 0 {
return err, 0
}
return nil, country[0].Id
}
func decimalFloat(value string) float64 {
f, _ := strconv.ParseFloat(value, 64)
val, _ := strconv.ParseFloat(fmt.Sprintf("%.2f", f), 64)
return val
}
func decimalInt(value string) int64 {
i, _ := strconv.ParseInt(value, 10, 64)
return i
}
