kpt_xdmy/apiserver/service/material.go

package service

import (
	"encoding/json"
	"fmt"
	"kpt.xdmy/apiserver/config"
	"regexp"
	"strconv"
	"time"

	"github.com/pkg/errors"
	log "github.com/sirupsen/logrus"

	_ "github.com/go-sql-driver/mysql"
	"kpt.xdmy/apiserver/model"
	"kpt.xdmy/apiserver/model/http"
	// "kpt.xdmy/pkg/log"
)

var partChan chan error

// 定时从sap拉取物料（备件等）数据
// 预留参数时间、备件编码，给手动调用接口
func (s *Service) SapMaterial(t time.Time, code string) {
	partChan = make(chan error, 10)
	r := new(http.MaterialReq)
	rp := new(http.MaterialResp)
	var e error
	r.Dest.DestID = "EQMAN"
	r.Dest.BussTp = "MM002"
	//r.Dest.Url = "http://192.168.61.117/SAPP0/Common/MM002/QueryMaterial/"
	r.Dest.Url = fmt.Sprintf("%s/Common/MM002/QueryMaterial/", config.Conf.Http.Routing)

	if t.IsZero() {
		t = time.Now()
	}
	r.Data.BudatB = t.Format("20060102")
	r.Data.BudatE = t.Format("20060102")
	//r.Data.BudatB = "20221104"
	//r.Data.BudatE = "20221028"

	r.Data.Type = http.MTART{MTART: "XD08"}
	// r.Data.Group = []http.MATKL{{MATKL: "14010101"}}
	// r.Data.Group = []http.MATKL{{MATKL: "14"}}
	if code != "" {
		// r.Data.Codes = []http.MATNR{{Code: "14.01.06.02.000216"}}
	}

	rbyte, _ := json.Marshal(r)
	fmt.Println(string(rbyte))
	if e = s.SyncSap(r, rp, rbyte); e == nil {
		if rp.Dest.Status == "S" {
			log.Infof("sap material success:len=%d", len(rp.Data.Master))
		} else {
			e = errors.Errorf("sap material fail", rp.Dest.MessText)
		}
	} else {
		e = errors.Wrapf(e, "sap material error")
	}
	var update error
	var count int
	for _, v := range rp.Data.Master {
		update = nil
		s.UpdatePart(&v)
		update = <-partChan
		if update != nil {
			log.Error(update)
			count++
			l := model.SapDetailLog{Name: "part", Code: v.Code, ErrorText: update.Error()}
			if e := s.d.DB.Create(&l); e != nil {
				log.Errorf("add sapdetail log: %v", e)
			}
		}
	}
	if e != nil {
		log.Error(e)
	} else if update != nil {
		e = errors.Errorf("material update fail sum:%d", count)
	} else {
		log.Infof("material update success sum:%d", len(rp.Data.Master)-count)
	}
	s.AddSapLog(r, e)
	return
}

// 物料数据更新
func (s *Service) UpdatePart(p *http.MaterialDetail) {
	part := new(model.Parts)

	if len(p.ModifyTime) >= 8 {
		p.ModifyTime = p.ModifyTime[:4] + "-" + p.ModifyTime[4:6] + "-" + p.ModifyTime[6:8]
	} else {
		partChan <- errors.New("ModifyTime is  invalid:" + p.ModifyTime)
	}
	if t, e := time.ParseInLocation("2006-01-02", p.ModifyTime, time.Local); e != nil {
		partChan <- errors.Wrapf(e, "time:%s", p.ModifyTime)
	} else {
		part.ModifyTime = t
	}
	if p.Dflag == "" {
		part.Enable = 1
		part.Statue = 1
	}
	part.PartCode = p.Code
	part.Note = p.Description
	part.Category = p.Type
	part.Unit = p.Unit
	part.Name = p.Name
	part.Specification = p.Model
	if e := s.d.DB.Where(model.Parts{PartCode: p.Code}).Assign(*part).FirstOrCreate(part).Error; e != nil {
		partChan <- errors.Wrapf(e, "code:%s", p.Code)
	} else {
		log.Infof("sap part update success:%s", p.Code)
		partChan <- nil
	}
}

func Exec(rows [][]string) {

	for i, v := range rows {
		fmt.Println(i)
		if i == 0 {
			continue
		}
		fmt.Println(v[1], v[2], v[10])
		s.d.DB.Exec(` update emp set tel = ?  where empCode = ? `, v[10], v[1])
	}

	//precontractId := model.PartRepertory{}
	//err := s.d.DB.Raw(`select precontractId  from  part_repertory where precontractId is not null order by precontractId  `).First(&precontractId).Error
	//if err != nil {
	//	log.Error(err)
	//	//continue
	//}
	//contractId := model.PartRepertory{}
	//err = s.d.DB.Raw(`select (contractId) contractId  from  part_repertory order by contractId  `).First(&contractId).Error
	//if err != nil {
	//	log.Error(err)
	//	//continue
	//}
	//for i, v := range rows {
	//	fmt.Println(i)
	//	if i == 0 {
	//		continue
	//	}
	//	/*
	//	   		pasture := model.Pasture{}
	//	   		err := s.d.DB.Where(&model.Pasture{Name: v[1]}).First(&pasture).Error
	//	   		if err != nil {
	//	   			fmt.Println(err)
	//	   			return
	//	   		}
	//	   		now := time.Now().Format("2006-01-02")
	//	   		contractId, partCode := v[13], v[4]
	//	   		partLaid := model.PartLaid{}
	//	   		err = s.d.DB.Raw(` select p.id from bigpartlaid bp  join partlaid p on p.bigid = bp.id
	//	                where bp.pastureid = ? and p.contractId = ? and p.partCode = ?  and DATE_FORMAT(bp.creatDate,'%Y-%m-%d') = ?`, pasture.ID, contractId, partCode, now).First(&partLaid).Error
	//	   		if err != nil {
	//	   			fmt.Println(err)
	//	   			//return
	//	   		}
	//	   		partuse := model.PartUse{}
	//	   		err = s.d.DB.Raw(` select p.id from bigpartuse bp  join partuse p on p.bigid = bp.id
	//	                where bp.pastureid = ? and p.contractId = ? and p.partCode = ?  and DATE_FORMAT(bp.creatDate,'%Y-%m-%d') = ?`, pasture.ID, contractId, partCode, now).First(&partuse).Error
	//	   		if err != nil {
	//	   			fmt.Println(err)
	//	   			//return
	//	   		}
	//	   		if partuse.ID > 0 || partLaid.ID > 0 {
	//	   			continue
	//	   		}
	//
	//	   		err = s.d.DB.Exec(` update part_repertory ps  set reportery = ? where id = ? `, v[15], v[0]).Error
	//	   		if err != nil {
	//	   			fmt.Println(err)
	//	   			return
	//	   		}
	//	*/
	//
	//	//if v[14] == "新增" {
	//	//
	//	//	//1983
	//
	//	//
	//	//if v[1] == "M017" || v[1] == "N302" || i <= 47 {
	//	//	continue
	//	//}
	//	id := v[0]
	//	///*
	//	if id == "" && v[11] == "无合同" && v[14] == "M017" {
	//
	//		parts := model.Parts{}
	//		err := s.d.DB.Where(&model.Parts{PartCode: v[3]}).First(&parts).Error
	//		if err != nil {
	//			log.Error(err)
	//			continue
	//		}
	//
	//		pasture := model.Pasture{}
	//		err = s.d.DB.Where(&model.Pasture{FactoryCode: "M005"}).First(&pasture).Error
	//		if err != nil {
	//			fmt.Println(err)
	//			continue
	//		}
	//
	//		w := model.Warehouse{}
	//		err = s.d.DB.Where(&model.Warehouse{PastureID: pasture.ID, Status: 1}).First(&w).Error
	//		if err != nil {
	//			fmt.Println(err)
	//			continue
	//		}
	//
	//		reportery := v[16]
	//		price := 1
	//		//reportery := v[2]
	//		providerid := "1975"
	//		providerName := "北京京东"
	//		contractId := fmt.Sprintf("-%d%s%d", pasture.ID, providerid, parts.ID)
	//		//
	//		////	PrecontractID, err := strconv.ParseInt(precontractId.PrecontractID, 10, 64)
	//		err = s.d.DB.Exec(` insert into part_repertory(specification,pastureId,partId,partName,partCode,location,locationId,reportery,
	//		                       contractId,brandId,unit,price,precontractId,providerNames,providerId)
	//		values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)`,
	//			parts.Specification, pasture.ID, parts.ID, parts.Name, parts.PartCode, w.WarehoseCode, w.ID, reportery, contractId, 0, parts.Unit,
	//			price, contractId, providerName, providerid).Error
	//	}
	//	//*/
	//	//if err != nil {
	//	//	fmt.Println(err)
	//	//	return
	//	//}
	//
	//	//}
	//	//else {
	//	//reportery := v[16]
	//	//id := v[0]
	//	//if id != "" {
	//	//	err := s.d.DB.Exec(` update part_repertory ps  set reportery = ?  where id =  ?`, reportery, id).Error
	//	//	if err != nil {
	//	//		fmt.Println(err)
	//	//		return
	//	//	}
	//	//}
	//	//} else {
	//	//	err := s.d.DB.Exec(` delete from  part_repertory   where id =  ?`, id).Error
	//	//	if err != nil {
	//	//		fmt.Println(err)
	//	//		return
	//	//	}
	//	//}
	//	//}
	//}

}

func DepartmentExec(rows [][]string) {
	for i, v := range rows {
		//fmt.Println(i, v)
		if i == 0 {
			continue
		}
		id := v[0]
		name := v[1]
		parentid := v[4]
		costCenter_code := v[15]
		costCenter_code2 := v[17]
		fmt.Println(id, name, parentid, costCenter_code, costCenter_code2)
		if id != "新增" {
			fmt.Println(id, name, parentid, costCenter_code, costCenter_code2)
			if costCenter_code2 != "" {
				err := s.d.DB.Exec(` update department ps  set costCenter_code = ?  where id = ? `, costCenter_code2, id).Error
				if err != nil {
					fmt.Println(err)
					return
				}
			}

			if v[18] == "无此部门" || v[18] == "未上线牧场" || v[18] == "重复" {
				err := s.d.DB.Exec(` delete from department where id = ?   `, id).Error
				if err != nil {
					fmt.Println(err)
					return
				}
			}
		} else {
			fmt.Println(id, name, parentid, costCenter_code, costCenter_code2)
			if costCenter_code2 != "" {
				err := s.d.DB.Exec(` insert into department(name,pastureId,isPasture,costCenter_code) values(?,?,?,?) `, name, parentid, 0, costCenter_code2).Error
				if err != nil {
					fmt.Println(err)
					return
				}
			}
		}
	}

}

func ProviderExec(rows [][]string) {
	//for i, v := range rows {
	//	if i == 0 {
	//		continue
	//	}
	//	id := v[0]
	//	//sapcode := v[4]
	//	//providername := v[5]
	//	status := v[6]
	//
	//	if status == "删除" || v[4] == "" {
	//		err := s.d.DB.Exec(` delete from provider where id = ? `, id).Error
	//		if err != nil {
	//			fmt.Println(err)
	//			return
	//		}
	//	}
	//}

	//for i, v := range rows {
	//	if i == 0 {
	//		continue
	//	}
	//	id := v[0]
	//	sapcode := v[4]
	//	providername := v[5]
	//	status := v[6]
	//
	//	if status != "删除" {
	//		err := s.d.DB.Exec(` update  provider set providerIntro = ? , sapcode = ?  where id = ? `, providername, sapcode, id).Error
	//		if err != nil {
	//			fmt.Println(err)
	//			return
	//		}
	//	}
	//}
	//for _, v := range rows {
	//if i == 0 {
	//	continue
	//}
	//id := v[0]
	//sapcode := v[4]
	//providername := v[1]
	//providerIntro := v[5]
	//status := v[6]
	//
	//if status != "删除" {
	//	err := s.d.DB.Exec(` update  provider set providerIntro = ? , sapcode = ?  where id = ? `, providername, sapcode, id).Error
	//	if err != nil {
	//		fmt.Println(err)
	//		return
	//	}
	//}
	//	err := s.d.DB.Exec(` insert into  provider(providerName,providerIntro,providerNumber,sapcode) values(?,?,?,?)`,
	//		providername, providerIntro, sapcode, sapcode).Error
	//	if err != nil {
	//		fmt.Println(err)
	//		return
	//	}
	//}

}

func EquipmentExec(rows [][]string) error {
	errList := make([]map[string]interface{}, 0)
	for i, v := range rows {
		financeCode := v[13]
		if financeCode == "" || i == 0 {
			continue
		}
		data := make(map[string]interface{})
		fmt.Println(i)
		eqCode := v[4]
		eqName := v[3]
		specification := v[5]
		//用途
		purpose := v[8]
		//purchaseDate, _ := ExcelIntDate(v[10])
		//purchaseDate := v[10]
		//entranceDate, _ := ExcelIntDate(v[11])
		//entranceDate := v[11]
		depreciation := v[12]
		yuanzhi := v[14]
		salvage := v[15]
		subtractvalue := v[16]
		upkeepgrade := v[17]
		if upkeepgrade == "A" {
			upkeepgrade = "113"
		} else if upkeepgrade == "B" {
			upkeepgrade = "114"
		} else if upkeepgrade == "C" {
			upkeepgrade = "115"
		}

		yearUpkeepCost := v[18]
		yearMaintainCost := v[19]
		baseHours := v[20]
		employeName := v[22]
		inputUser := v[23]
		var leaveDate interface{}
		if v[24] != "" {
			leaveDate = v[24]
			//leaveDate, _ = ExcelIntDate(v[24])
		}
		brand := v[6]
		proName := v[7]

		maintenance := v[27]
		var onepushTime, twopushTime, threepushTime interface{}
		if v[29] == "" {
			onepushTime = nil
		} else {
			onepushTime = v[29]
		}
		if v[31] == "" {
			twopushTime = nil
		} else {
			twopushTime = v[31]
		}
		if v[33] == "" {
			threepushTime = nil
		} else {
			threepushTime = v[33]
		}

		levelone := v[28]
		leveltwo := v[30]
		levelthree := v[32]

		brandInfo := model.Brand{}
		if brand != "" {
			err := s.d.DB.Where(&model.Brand{BrandName: brand}).First(&brandInfo).Error
			if err != nil {
				fmt.Println(err)
				//return err
			}
		}

		provider := model.Provider{}
		if proName != "" {
			err := s.d.DB.Where(&model.Provider{ProviderName: proName}).First(&provider).Error
			if err != nil {
				fmt.Println(err)
				//return err
			}
		}

		pasture := model.Pasture{}
		err := s.d.DB.Where(&model.Pasture{Name: v[0]}).First(&pasture).Error
		if err != nil {
			data["pasture"] = v[0]
			data["eqName"] = eqName
			data["eqCode"] = eqCode
			data["financeCode"] = financeCode
			data["err"] = err.Error()
			errList = append(errList, data)
			fmt.Println(err)
			return err
		}

		err = s.d.DB.Exec(` update equipment set  eqCode = if(? = '',null,?),specification = ?,purpose = ?	
        ,depreciation=?,
         yuanzhi=?, salvage =?,subtractvalue =?, upkeepgrade= ?,yearUpkeepCost=? ,yearMaintainCost=?,baseHours=?,employeName=(select empname from emp  where replace(empname,' ','') = ? and pastureId = ? and enable = 1 ),
         employeeId = (select id from emp  where replace(empname,' ','') = ? and pastureId = ? and enable = 1 ),inputUser=?,leaveDate=? ,
         brand = ?, brandId = ? ,proId= ?,proName= ? ,eqClassName = ?,eqClassId = (select id from eqclass where typeName = ?),eqName = ?,
         	maintenance= (select GROUP_CONCAT(id) from emp where  FIND_IN_SET(replace(empname,' ',''),replace(?,' ',''))     and pastureId = ? and enable = 1  ),levelone=(select id from emp where  replace(empname,' ','') =  replace(?,' ','')    and pastureId = ? and enable = 1 ) ,
		leveltwo = (select id from emp where  replace(empname,' ','') =  replace(?,' ','')   and pastureId = ? and enable = 1 ),levelthree=(select id from emp where replace(empname,' ','')  =  replace(?,' ','')    and pastureId = ? and enable = 1 )
		,leveloneTime = ? ,leveltwoTime = ?,levelthreeTime= ?  where financeCode = ? `,
			eqCode, eqCode, specification, purpose, depreciation, yuanzhi,
			salvage, subtractvalue, upkeepgrade, yearUpkeepCost, yearMaintainCost, baseHours, employeName, pasture.ID, employeName, pasture.ID, inputUser, leaveDate,
			brandInfo.BrandName, brandInfo.ID, provider.ID, provider.ProviderName, eqName, v[1], v[3], maintenance, pasture.ID, levelone, pasture.ID,
			leveltwo, pasture.ID, levelthree, pasture.ID, onepushTime, twopushTime, threepushTime, financeCode).Error
		if err != nil {
			data["pasture"] = v[0]
			data["eqName"] = eqName
			data["eqCode"] = eqCode
			data["financeCode"] = financeCode
			data["err"] = err.Error()
			errList = append(errList, data)
			fmt.Println(err)
			//return err
			continue
		}

		eqclass := model.EqClass{}
		err = s.d.DB.Table("eqclass").Where("typeName = ?", v[1]).First(&eqclass).Error
		if err != nil {
			data["pasture"] = v[0]
			data["eqName"] = eqName
			data["eqCode"] = eqCode
			data["financeCode"] = financeCode
			data["err"] = err.Error()
			errList = append(errList, data)
			fmt.Println(err)
			//return err
			continue
		}
		if eqclass.ID == 0 {
			continue
		}

		if v[2] == "" {
			err = s.d.DB.Exec(` update equipment set  assetCode = (select cerateEQNumber((select typeCode from eqclass where typeName = ?),?)) where financeCode = ?  `,
				v[1], pasture.ID, financeCode).Error
			if err != nil {
				data["pasture"] = v[0]
				data["eqName"] = eqName
				data["eqCode"] = eqCode
				data["financeCode"] = financeCode
				data["err"] = err.Error()
				errList = append(errList, data)
				fmt.Println(err)
				//return err
			}
		}
	}
	return nil
}

func ExcelIntDate(dateStr string) (dt time.Time, err error) {
	var dateValue float64
	matched, err := regexp.MatchString(`^\d+$`, dateStr)
	if err != nil {
		return
	}

	if !matched {
		err = errors.New("not excel time")
		return
	}

	dateValue, err = strconv.ParseFloat(dateStr, 64)
	if err != nil {
		return
	}
	epoch := time.Date(1899, 12, 30, 0, 0, 0, 0, time.UTC) // UTC 1899/12/30 00:00:00
	dt = epoch.Add(time.Duration(dateValue) * 24 * time.Hour)
	return
}

func EquipmentExecAdd(rows [][]string) error {

	for i, v := range rows {
		financeCode := fmt.Sprintf("%s-%s-%s", v[0], v[1], v[2])
		fmt.Println(financeCode, v[11], i)
		//financeCode := v[13]
		if i == 0 || (v[0] != "1042" && v[0] != "1002") {
			continue
		}

		err := s.d.DB.Exec(` update equipment set pastureid = (SELECT pastureid FROM department WHERE costCenter_code = ?  )   where financeCode = ? `, v[11], financeCode).Error
		fmt.Println(err)
		//var purchaseDate, entranceDate string
		//if v[9] != "" {
		//	date, _ := ExcelIntDate(v[9])
		//	purchaseDate = date.Format("2006-01-02")
		//}
		//if v[10] != "" {
		//	date, _ := ExcelIntDate(v[10])
		//	entranceDate = date.Format("2006-01-02")
		//}

		//a1, _ := strconv.Atoi()
		//a1, _ := strconv.ParseFloat(v[12][:3], 64)
		//n := strings.Index(v[12][3:], "6")
		//if n > 0 {
		//	a1 += 0.5
		//}

	}
	return nil
}

func ContractExec(rows [][]string) {

	for i, v := range rows {

		if i < 6 {
			continue
		}
		partCode := v[1]
		providerSapCode := v[3]
		pastureCode := v[6]
		price := v[8]
		//fmt.Println(partCode, providerSapCode, pastureCode, price)

		pasture := model.Pasture{}
		err := s.d.DB.Where(model.Pasture{FactoryCode: pastureCode}).First(&pasture).Error
		if err != nil {
			fmt.Println(err)
			//return
			continue
		}
		//fmt.Println(pasture, err)

		provider := model.Provider{}
		err = s.d.DB.Where(model.Provider{SapCode: providerSapCode}).First(&provider).Error
		if err != nil {
			fmt.Println(err)
			continue
		}

		contract := model.Contract{}
		s.d.DB.Raw(`SELECT
	c.id
FROM
	contract c
	JOIN bigcontract bc ON bc.id = c.bigid 
WHERE
	c.partCode = ?
	AND ? IN (
	SELECT
		pb.pasture_id 
	FROM
		bigcontract bct
		JOIN pasture_bigcontract pb ON pb.bigcontract_id = bct.id 
	WHERE
		bct.contractCode LIKE concat( "%",  LEFT ( bc.contractCode, 12 ), "%" ) 
	) 
	AND bc.providerId = ? `, partCode, pasture.ID, provider.ID).First(&contract)
		fmt.Println(pasture.Name, provider.ProviderName, price, pastureCode, i)

		if contract.ID > 0 {
			s.d.DB.Exec(" update contract set price = ? where id = ? ", price, contract.ID)
		}
	}
}