kpt_xdmy/apiserver/service/loadData.go

package service

import (
	"encoding/json"
	"fmt"
	"strconv"
	"time"

	"gorm.io/gorm"
	"kpt.xdmy/apiserver/model"
	"kpt.xdmy/pkg/util"

	log "github.com/sirupsen/logrus"
	"github.com/xuri/excelize/v2"
)

func PartBatch() (count int, e error) {
	nch = make(chan error, 10)
	r, e := s.d.DB.Raw(`select id FROM parts WHERE issync = 0 and statue = 1 `).Rows()
	defer r.Close()
	if e != nil {
		fmt.Print(e)
		return 0, e
	}
	var id string
	for r.Next() {
		r.Scan(&id)
		easBillSync(id)
		if e = <-nch; e != nil {
			fmt.Print(e)
		} else {
			count++
		}
	}
	return count, nil
}
func easBillSync(id string) {
	e := util.EASBillSync(5, id, "1")
	nch <- e
}

var parchan chan *gorm.DB

func (s *Service) PartUpdate() {
	parchan = make(chan *gorm.DB, 10)
	// f, e := excelize.OpenFile("/Users/desire/Documents/partcode.xlsx")
	f, e := excelize.OpenFile("partcode.xlsx")
	if e != nil {
		log.Error("excle open  :%d", e)
	}
	defer func() {
		// Close the spreadsheet.
		if err := f.Close(); err != nil {
			fmt.Println(err)
		}
	}()
	sheet := []string{"Sheet1", "Sheet2"}
	// var s []string
	for _, name := range sheet {

		rows, err := f.GetRows(name)
		if err != nil {
			fmt.Println(err)
			return
		}
		fmt.Println(time.Now().Format("2006-01-02 15:04:05"))
		value := ""
		for i, row := range rows {
			if i == 0 {
				value += fmt.Sprintf("('%s', '%s','%s',%s)", row[0], row[1], row[2], row[3])
			} else {
				value += fmt.Sprintf(",('%s', '%s','%s',%s)", row[0], row[1], row[2], row[3])
			}
		}
		iusql := fmt.Sprintf(`insert  into  parts (partcode ,new_partcode,unit,issave) values %s
	on DUPLICATE KEY UPDATE  new_partcode=values(new_partcode) ,unit=values(unit),issave=values(issave) `, value)

		if r := s.d.DB.Exec(iusql); r.Error != nil {
			fmt.Printf("insert part_Data: %v\n", r.Error)
			return
		} else {
			fmt.Printf("insert part_Data success!:%d\n", r.RowsAffected)
		}
		fmt.Println(time.Now().Format("2006-01-02 15:04:05"))
	}

}

func (s *Service) PartInsert() {
	// f, e := excelize.OpenFile("/Users/desire/Downloads/part_insert.xlsx")
	f, e := excelize.OpenFile("part_insert.xlsx")
	if e != nil {
		log.Error("excle open  :%d", e)
	}
	defer func() {
		if err := f.Close(); err != nil {
			fmt.Println(err)
		}
	}()
	slist := f.GetSheetList()
	log.Infof("%v", slist)
	// sheet := "Sheet1"
	rows, err := f.GetRows(slist[0])
	if err != nil {
		fmt.Println(err)
		return
	}
	value := ""
	for i, row := range rows {
		// if i > 3550 {
		// 	continue
		// }
		if i == 0 {
			value += fmt.Sprintf("('%s', '%s','%s','%s')", row[0], row[1], row[2], row[3])
		} else {
			value += fmt.Sprintf(",('%s', '%s','%s','%s')", row[0], row[1], row[2], row[3])
		}
	}
	iusql := fmt.Sprintf(`insert  into  parts (partcode ,name,specification,unit) values %s
	on DUPLICATE KEY UPDATE  partcode=values(partcode) ,name=values(name),specification=values(specification),
	unit=values(unit)`, value)

	if r := s.d.DB.Exec(iusql); r.Error != nil {
		fmt.Printf("insert parts: %v\n", r.Error)
		return
	} else {
		fmt.Printf("insert parts success!:%d\n", r.RowsAffected)
	}

}
func (s *Service) RepertoryUpdate() {
	f, e := excelize.OpenFile("repertory.xlsx")
	// f, e := excelize.OpenFile("/Users/desire/Documents/repertory.xlsx")
	if e != nil {
		log.Errorf("excle open  :%d", e)
	}
	defer func() {
		if err := f.Close(); err != nil {
			fmt.Println(err)
		}
	}()
	rows, e := f.GetRows("SheetJS")
	if e != nil {
		fmt.Println(e)
		return
	}
	sql := "call insertRepertory(?,?,?,?,?,?,?,?,?,?,?)"
	var sum, count, suc int
	for _, row := range rows[1:2] {
		sum++
		var e error
		// var msg, msg2 string
		// scan := new(sqlScan)
		var res interface{}
		var storage, price float64
		if storage, e = strconv.ParseFloat(row[6], 64); e != nil {
			log.Errorf("failed to parse storage:%s , e:%v", row[6], e)
			continue
		}
		if price, e = strconv.ParseFloat(row[7], 64); e != nil {
			log.Errorf("failed to parse price:%s , e:%v", row[7], e)
			continue
		}

		if r := s.d.DB.Raw(sql, row[0], row[1], row[2], row[3], row[4], storage, price, row[7], row[8], row[9], "00611"); r.Error != nil {
			log.Errorf("exec :%v", r.Error)
		} else {
			count++
			// r.Row().Scan(&msg, &msg2)
			r.Row().Scan(&res)
			// r.Row().Scan(scan)
			log.Infof("res:%s", res)
			// if msg == "success" {
			// 	suc++
			// } else {
			// 	log.Infoln(msg)
			// }
		}
	}
	log.Infoln(suc, count, sum)
}

type sqlScan struct {
	msg        string
	id         string
	contractid string
}

func (s *Service) EqInsert() {

	// f, e := excelize.OpenFile("/Users/desire/Downloads/eq.xlsx")
	f, e := excelize.OpenFile("eq.xlsx")
	if e != nil {
		log.Error("excle open  :%d", e)
	}
	defer func() {
		if err := f.Close(); err != nil {
			fmt.Println(err)
		}
	}()
	slist := f.GetSheetList()
	rows, err := f.GetRows(slist[0])
	if err != nil {
		fmt.Println(err)
		return
	}
	names := []string{"pastureName", "eqClassName", "eqName", "eqCode", "specification",
		"brand", "proName", "purpose", "status", "purchaseDate",
		"entranceDate", "depreciation", "financeCode", "yuanzhi", "salvage",
		"departmentName", "employeName", "inputUser"}
	esl := []model.Equipment{}
	for i, row := range rows {
		if i < 2 {
			continue
		}
		m := make(map[string]interface{})
		eq := new(model.Equipment)
		for j, v := range row {
			m[names[j]] = v
		}
		// if status, e := strconv.ParseInt(m["status"].(string), 10, 32); e != nil {
		// 	log.Error(e)
		// } else {
		// 	m["status"] = status
		// }
		if m["status"] == "正常" {
			m["status"] = 17
		}
		if b, e := json.Marshal(m); e != nil {
			log.Error(e)
		} else {
			if e := json.Unmarshal(b, eq); e != nil {
				log.Error(e)
			} else {
				esl = append(esl, *eq)
			}
		}
	}
	log.Infof("len==%v", len(esl))
	// s.d.DB.CreateInBatches(esl, 100)
	r := s.d.DB.Create(&esl)
	log.Infof("rows==%v", r.RowsAffected)
}