supplier_system/src/dashoo.cn/backend/api/business/oilsupplier/supplier/oilsupplierService.go

package supplier

import (
	. "dashoo.cn/backend/api/mydb"
	"dashoo.cn/utils"
	"github.com/go-xorm/xorm"
	"strconv"
)

type OilSupplierService struct {
	MyServiceBase
}

func GetOilSupplierService(xormEngine *xorm.Engine) *OilSupplierService {
	s := new(OilSupplierService)
	s.DBE = xormEngine
	return s
}

//
func (s *OilSupplierService) GetMyPagingEntitiesWithOrderBytbl(supplierTableName, supplierCertTableName string, pageIndex, itemsPerPage int64, orderby string, asc bool, entitiesPtr interface{}, where string) (total int64) {
	var resultsSlice []map[string][]byte

	//获取总记录数
	sqlCount := `select count(*) from ` + supplierTableName + ` a `
	sqlCount += ` left join ` + supplierCertTableName + " b on b.SupplierId = a.Id"
	sqlCount += ` where ` + where

	var sql string
	sql = `select a.*, b.Id as CertId, b.AccessCardNo, b.SupplierTypeCode, b.SupplierTypeName, b.InFlag, b.ApplyTime,b.EffectEndTime, `
	sql += ` b.WorkerTotal, `
	sql += ` b.ContractNum, `
	sql += ` b.UniversityNum, `
	sql += ` b.TechnicalNum, `
	sql += ` b.AboveProfNum, `
	sql += ` b.MiddleProfNum, `
	sql += ` b.NationalRegNum, `
	sql += ` b.NationalCertTotal, `
	sql += ` b.DesignerTotal, `
	sql += ` b.SkillerTotal, `
	sql += ` b.Status, `
	sql += ` b.WorkflowId, b.CreateOn ,b.ProcessKey,b.BusinessKey,b.BackRemark,b.IsRestrict`
	sql += ` from ` + supplierTableName + ` a `
	sql += ` left join ` + supplierCertTableName + " b on b.SupplierId = a.Id"
	sql += ` where ` + where
	if asc {
		sql += ` order by ` + orderby + ` ASC `
	} else {
		sql += ` order by ` + orderby + ` DESC `
	}
	if pageIndex != 0 && itemsPerPage != 0 {
		sql += ` limit ` + utils.ToStr((pageIndex-1)*itemsPerPage) + "," + utils.ToStr(itemsPerPage)
	}
	s.DBE.SQL(sql).Find(entitiesPtr)

	resultsSlice, _ = s.DBE.Query(sqlCount)

	if len(resultsSlice) > 0 {
		results := resultsSlice[0]
		for _, value := range results {
			total, _ = strconv.ParseInt(string(value), 10, 64)
			break
		}
	}

	return total
}
func (s *OilSupplierService) GetMyPagingEntitiesWithOrderBytbl4(supplierTableName, supplierCertTableName string, pageIndex, itemsPerPage int64, orderby string, asc bool, entitiesPtr interface{}, where string) (total int64) {
	var resultsSlice []map[string][]byte

	//获取总记录数
	sqlCount := `select count(*) from ` + supplierTableName + ` a `
	sqlCount += ` left join ` + supplierCertTableName + " b on b.SupplierId = a.Id"
	sqlCount += ` where ` + where

	var sql string
	sql = `select a.*, b.Id as CertId, b.AccessCardNo, b.SupplierTypeCode, b.SupplierTypeName, b.InFlag, b.ApplyTime,b.InStyle`
	sql += ` from ` + supplierTableName + ` a `
	sql += ` left join ` + supplierCertTableName + " b on b.SupplierId = a.Id"
	sql += ` where ` + where
	if asc {
		sql += ` order by ` + orderby + ` ASC `
	} else {
		sql += ` order by ` + orderby + ` DESC `
	}
	if pageIndex != 0 && itemsPerPage != 0 {
		sql += ` limit ` + utils.ToStr((pageIndex-1)*itemsPerPage) + "," + utils.ToStr(itemsPerPage)
	}
	s.DBE.SQL(sql).Find(entitiesPtr)

	resultsSlice, _ = s.DBE.Query(sqlCount)

	if len(resultsSlice) > 0 {
		results := resultsSlice[0]
		for _, value := range results {
			total, _ = strconv.ParseInt(string(value), 10, 64)
			break
		}
	}

	return total
}
func (s *OilSupplierService) GetMyPagingEntitiesWithOrderBytbl2(supplierTableName, supplierCertTableName string, pageIndex, itemsPerPage int64, orderby string, asc bool, entitiesPtr interface{}, where string, code string, Ids string) (total int64) {
	var resultsSlice []map[string][]byte

	//获取总记录数
	sqlCount := `select count(DISTINCT b.Id) from ` + supplierTableName + ` a `
	sqlCount += ` left join ` + supplierCertTableName + " b on b.SupplierId = a.Id"
	sqlCount += ` where ` + where
	if code != "" {
		sqlCount += ` and b.Id in (` + Ids + ")"
	}

	var sql string
	sql = `select DISTINCT b.Id as CertId, a.*, b.AccessCardNo, b.SupplierTypeCode, b.SupplierTypeName, b.InFlag, b.ApplyTime,b.EffectEndTime, `
	sql += ` b.WorkerTotal, `
	sql += ` b.ContractNum, `
	sql += ` b.UniversityNum, `
	sql += ` b.TechnicalNum, `
	sql += ` b.AboveProfNum, `
	sql += ` b.MiddleProfNum, `
	sql += ` b.NationalRegNum, `
	sql += ` b.NationalCertTotal, `
	sql += ` b.DesignerTotal, `
	sql += ` b.SkillerTotal, `
	sql += ` b.Status, `
	sql += ` b.WorkflowId, b.CreateOn ,b.ProcessKey,b.BusinessKey,b.BackRemark,b.IsRestrict,b.Remark`
	sql += ` from ` + supplierTableName + ` a `
	sql += ` left join ` + supplierCertTableName + " b on b.SupplierId = a.Id"
	sql += ` where ` + where
	if code != "" {
		sql += ` and b.Id in (` + Ids + ")"
	}
	if asc {
		sql += ` order by ` + orderby + ` ASC `
	} else {
		sql += ` order by ` + orderby + ` DESC `
	}
	if pageIndex != 0 && itemsPerPage != 0 {
		sql += ` limit ` + utils.ToStr((pageIndex-1)*itemsPerPage) + "," + utils.ToStr(itemsPerPage)
	}
	s.DBE.SQL(sql).Find(entitiesPtr)

	resultsSlice, _ = s.DBE.Query(sqlCount)

	if len(resultsSlice) > 0 {
		results := resultsSlice[0]
		for _, value := range results {
			total, _ = strconv.ParseInt(string(value), 10, 64)
			break
		}
	}

	return total
}
func (s *OilSupplierService) GetMyPagingEntitiesWithOrderBytbl3(supplierTableName, supplierCertTableName string, pageIndex, itemsPerPage int64, orderby string, asc bool, entitiesPtr interface{}, where string) (total int64) {
	var resultsSlice []map[string][]byte

	//获取总记录数
	sqlCount := `select count(DISTINCT b.Id) from ` + supplierTableName + ` a `
	sqlCount += ` left join ` + supplierCertTableName + " b on b.SupplierId = a.Id"
	sqlCount += ` left join OilSupplierCertSub c on c.SupplierCertId = b.Id`
	sqlCount += ` where ` + where

	var sql string
	sql = `select DISTINCT b.Id as CertId, a.*, b.AccessCardNo, b.SupplierTypeCode, b.SupplierTypeName, b.InFlag, b.ApplyTime,b.EffectEndTime, `
	sql += ` b.WorkerTotal, `
	sql += ` b.ContractNum, `
	sql += ` b.UniversityNum, `
	sql += ` b.TechnicalNum, `
	sql += ` b.AboveProfNum, `
	sql += ` b.MiddleProfNum, `
	sql += ` b.NationalRegNum, `
	sql += ` b.NationalCertTotal, `
	sql += ` b.DesignerTotal, `
	sql += ` b.SkillerTotal, `
	sql += ` b.Status, `
	sql += ` b.RecUnitName, `
	sql += ` GROUP_CONCAT(CONCAT(c.Code, ':', c.Name)) as CodeName, `
	sql += ` b.WorkflowId, b.CreateOn ,b.ProcessKey,b.BusinessKey,b.BackRemark,b.IsRestrict,b.Remark`
	sql += ` from ` + supplierTableName + ` a `
	sql += ` left join ` + supplierCertTableName + " b on b.SupplierId = a.Id"
	sql += ` left join OilSupplierCertSub c on c.SupplierCertId = b.Id`
	sql += ` where ` + where
	sql += ` GROUP BY a.Id,b.Id`
	if asc {
		sql += ` order by ` + orderby + ` ASC `
	} else {
		sql += ` order by ` + orderby + ` DESC `
	}
	if pageIndex != 0 && itemsPerPage != 0 {
		sql += ` limit ` + utils.ToStr((pageIndex-1)*itemsPerPage) + "," + utils.ToStr(itemsPerPage)
	}
	s.DBE.SQL(sql).Find(entitiesPtr)

	resultsSlice, _ = s.DBE.Query(sqlCount)

	if len(resultsSlice) > 0 {
		results := resultsSlice[0]
		for _, value := range results {
			total, _ = strconv.ParseInt(string(value), 10, 64)
			break
		}
	}

	return total
}

func (s *OilSupplierService) CheckRepeatApplyInfo(supplierTableName, supplierCertTableName, typeCode, SupplierName, CommercialNo, OrganCode, BankAccount, CompanyUrl string, entitiesPtr interface{}) {

	//获取分页信息

	where2 := " b.SupplierTypeCode = '" + typeCode

	where := ""
	if SupplierName != "" {
		where = where + " or a.SupplierName = '" + SupplierName + "'"
	}

	if CommercialNo != "" {
		where = where + " or a.CommercialNo = '" + CommercialNo + "'"
	}

	if OrganCode != "" {
		where = where + " or a.OrganCode = '" + OrganCode + "'"
	}

	if BankAccount != "" {
		where = where + " or a.BankAccount = '" + BankAccount + "'"
	}

	if CompanyUrl != "" {
		where = where + " or a.CompanyUrl = '" + CompanyUrl + "'"
	}

	if len(where) > 0 {
		where2 += " and (1=0 " + where + ")"
		var sql string
		sql = `select a.*, b.Id as CertId, b.AccessCardNo, b.SupplierTypeCode, b.SupplierTypeName, `
		sql += ` b.WorkerTotal, `
		sql += ` b.ContractNum, `
		sql += ` b.UniversityNum, `
		sql += ` b.TechnicalNum, `
		sql += ` b.AboveProfNum, `
		sql += ` b.MiddleProfNum, `
		sql += ` b.NationalRegNum, `
		sql += ` b.NationalCertTotal, `
		sql += ` b.DesignerTotal, `
		sql += ` b.SkillerTotal, `
		sql += ` b.Status, `
		sql += ` b.WorkflowId `
		sql += ` from ` + supplierTableName + ` a `
		sql += ` right join ` + supplierCertTableName + " b on b.SupplierId = a.Id"
		sql += ` where ` + where2
		s.DBE.SQL(sql).Find(entitiesPtr)
	}

}

func (s *OilSupplierService) CheckUpdateRepeatApplyInfo(supplierTableName, supplierCertTableName, typeCode, supplierId, SupplierName, CommercialNo, OrganCode, BankAccount, CompanyUrl string, entitiesPtr interface{}) {

	//获取分页信息

	where2 := " a.Id != '" + supplierId + "'"

	where := ""
	if SupplierName != "" {
		where = where + " or a.SupplierName = '" + SupplierName + "'"
	}

	if CommercialNo != "" {
		where = where + " or a.CommercialNo = '" + CommercialNo + "'"
	}

	if OrganCode != "" {
		where = where + " or a.OrganCode = '" + OrganCode + "'"
	}

	if BankAccount != "" {
		where = where + " or a.BankAccount = '" + BankAccount + "'"
	}

	if CompanyUrl != "" {
		where = where + " or a.CompanyUrl = '" + CompanyUrl + "'"
	}

	if len(where) > 0 {
		where2 += " and (1=0 " + where + ")"
		var sql string
		sql = `select a.*, b.Id as CertId, b.AccessCardNo, b.SupplierTypeCode, b.SupplierTypeName, `
		sql += ` b.WorkerTotal, `
		sql += ` b.ContractNum, `
		sql += ` b.UniversityNum, `
		sql += ` b.TechnicalNum, `
		sql += ` b.AboveProfNum, `
		sql += ` b.MiddleProfNum, `
		sql += ` b.NationalRegNum, `
		sql += ` b.NationalCertTotal, `
		sql += ` b.DesignerTotal, `
		sql += ` b.SkillerTotal, `
		sql += ` b.Status, `
		sql += ` b.WorkflowId `
		sql += ` from ` + supplierTableName + ` a `
		sql += ` right join ` + supplierCertTableName + " b on b.SupplierId = a.Id"
		sql += ` where ` + where2
		s.DBE.SQL(sql).Find(entitiesPtr)
	}
}

func (s *OilSupplierService) CanUpdateSupplier(oilSupplierCertTableName string, supplierId int, typeCode string) bool {
	session := s.DBE.NewSession()
	sessionSvc := GetOilSupplierSession(session)
	defer session.Close()
	session.Begin()
	result := sessionSvc.CanUpdateSupplier(oilSupplierCertTableName, supplierId, typeCode)
	session.Commit()
	return result
}

func (s *OilSupplierService) GetUpdateCols(oilSupplierCertTableName string, supplierTypeCode string, supplierId int) []string {
	session := s.DBE.NewSession()
	sessionSvc := GetOilSupplierSession(session)
	defer session.Close()
	session.Begin()
	cols := sessionSvc.GetUpdateCols(oilSupplierCertTableName, supplierTypeCode, supplierId)
	session.Commit()
	return cols
}

func (s *OilSupplierService) GetProOrTreeList(ids string, entitiesPtr interface{}) {
	where := " Id in (" + ids + ")"
	sql := "SELECT Id, FullName, ParentId FROM Base_Organize where " + where
	s.DBE.SQL(sql).Find(entitiesPtr)
	return
}

func (s *OilSupplierService) GetMyTodoEntitie(supplierTableName, supplierCertTableName string, entitiesPtr interface{}, where string) bool {

	var sql string
	sql = `select a.Id, a.SupplierName, b.Id as CertId, b.SupplierTypeCode, '1' as Type, b.Status `
	sql += ` from ` + supplierTableName + ` a `
	sql += ` left join ` + supplierCertTableName + " b on b.SupplierId = a.Id"
	sql += ` where ` + where

	has, _ := s.DBE.SQL(sql).Get(entitiesPtr)
	return has
}

func (s *OilSupplierService) GetExpireFile(supplierTableName, supplierFileTableName string, entitiesPtr interface{}, where string) error {

	var sql string
	sql = `SELECT a.Id, a.SupplierName, a.Mobile, a.CreateBy, a.ContactName, a.CreateUserId, GROUP_CONCAT(b.NeedFileType SEPARATOR ';' ) NeedAllFile  `
	sql += ` FROM ` + supplierTableName + ` a `
	sql += ` left join ` + supplierFileTableName + " b ON a.Id=b.SupplierId "
	sql += ` where ` + where
	sql += ` GROUP BY a.Id, a.SupplierName, a.Mobile `
	err := s.DBE.SQL(sql).Find(entitiesPtr)
	return err
}

func (s *OilSupplierService) GetExpireFileList(supplierTableName, supplierFileTableName string, entitiesPtr interface{}, where string) error {

	var sql string
	sql = `SELECT a.Id, a.SupplierName, a.Mobile, b.SupplierTypeCode`
	sql += ` FROM ` + supplierTableName + ` a `
	sql += ` left join ` + supplierFileTableName + " b ON a.Id=b.SupplierId "
	sql += ` where ` + where
	sql += ` GROUP BY a.Id, a.SupplierName, a.Mobile, b.SupplierTypeCode `
	err := s.DBE.SQL(sql).Find(entitiesPtr)
	return err
}

func (s *OilSupplierService) GetInterfaceData(supplierTableName, supplierFileTableName string, entitiesPtr interface{}, where string) error {

	var sql string
	sql = `SELECT a.Id, a.SupplierName, a.RegCapital, GROUP_CONCAT(b.NeedFileType SEPARATOR ';' ) NeedAllFile  `
	sql += ` FROM ` + supplierTableName + ` a `
	sql += ` left join ` + supplierFileTableName + " b ON a.Id=b.SupplierId "
	sql += ` where ` + where
	sql += ` GROUP BY a.Id, a.SupplierName, a.Mobile `
	err := s.DBE.SQL(sql).Find(entitiesPtr)
	return err
}

func (s *OilSupplierService) GetProcessInfoWithOrderBytbl(supplierTableName, supplierCertTableName string, pageIndex, itemsPerPage int64, orderby string, asc bool, entitiesPtr interface{}, where string) (total int64) {
	var resultsSlice []map[string][]byte

	//获取总记录数
	sqlCount := `select count(*) from ` + supplierTableName + ` a `
	sqlCount += ` left join ` + supplierCertTableName + ` b on b.SupplierId = a.Id`
	sqlCount += ` Left join Base_User u on a.CreateUserId = u.Id `
	sqlCount += ` LEFT JOIN Base_Organize org ON b.CommitComId = org.Id`
	sqlCount += ` where ` + where

	var sql string
	sql = `select a.Id as SupplierId, a.SupplierName, b.Id, b.AccessCardNo, b.CreateOn, b.ModifiedOn As AddinTime, a.Mobile, `
	sql += ` b.Status, b.SupplierTypeCode, `
	sql += ` b.WorkflowId, b.ProcessKey, '1' as Type, b.CreateOn,`
	sql += ` org.FullName As RecUnitName,`
	sql += ` u.Realname as ContactName,`
	sql += ` u.Telephone as Mobile`
	sql += ` from ` + supplierTableName + ` a `
	sql += ` left join ` + supplierCertTableName + ` b on b.SupplierId = a.Id`
	sql += ` Left join Base_User u on b.CreateUserId = u.Id `
	sql += ` LEFT JOIN Base_Organize org ON b.CommitComId = org.Id`
	sql += ` where ` + where
	sql += ` group by b.Id`
	if asc {
		sql += ` order by ` + orderby + ` ASC `
	} else {
		sql += ` order by ` + orderby + ` DESC `
	}
	if pageIndex != 0 && itemsPerPage != 0 {
		sql += ` limit ` + utils.ToStr((pageIndex-1)*itemsPerPage) + "," + utils.ToStr(itemsPerPage)
	}
	s.DBE.SQL(sql).Find(entitiesPtr)

	resultsSlice, _ = s.DBE.Query(sqlCount)

	if len(resultsSlice) > 0 {
		results := resultsSlice[0]
		for _, value := range results {
			total, _ = strconv.ParseInt(string(value), 10, 64)
			break
		}
	}

	return total
}

func (s *OilSupplierService) GetCertIds(entitiesPtr interface{}, where string) {

	sql := "SELECT GROUP_CONCAT(DISTINCT SupplierCertId) as Ids from OilSupplierCertSub where " + where + " ORDER BY SupplierCertId"

	s.DBE.SQL(sql).Get(entitiesPtr)
}

func (s *OilSupplierService) ConverseSupplierTypeToInt(typeCode string) (typeInt int) {
	if typeCode == "01" {
		typeInt = 1
	}else if typeCode == "02" {
		typeInt = 2
	} else if typeCode == "03" {
		typeInt = 3
	} else if typeCode == "4" {
		typeInt = 4
	} else if typeCode == "5" {
		typeInt = 5
	} else if typeCode == "8" {
		typeInt = 8
	} else if typeCode == "9" || typeCode == "9-6" || typeCode == "9-7" {
		typeInt = 9
	} else if typeCode == "10" {
		typeInt = 10
	} else if typeCode == "11" {
		typeInt = 11
	} else if typeCode == "12" {
		typeInt = 12
	} else {
		typeInt = 0
	}
	return typeInt
}

func (s *OilSupplierService) GetDeleteSub(TableName, where string , entitiesPtr interface{}) {
	//var resultsSlice []map[string][]byte

	//获取总记录数
	//sqlCount := `select count(*) from OilSupplierCertSub a `
	//if TableName == "OilBasisBuild" {
	//	sqlCount += ` left join ` + TableName + ` b on b.Id = a.SubClassId`
	//} else {
	//	sqlCount += ` left join ` + TableName + ` b on b.ClassId = a.SubClassId`
	//}
	//sqlCount += ` where ` + where

	sql := `update OilSupplierCertSub a `
	if TableName == "OilBasisBuild" {
		sql += ` left join ` + TableName + ` b on b.Id = a.SubClassId`
	} else {
		sql += ` left join ` + TableName + ` b on b.ClassId = a.SubClassId`
	}
	sql += ` set IsQuestion = 1 where IsQuestion = 0 and ` + where
	s.DBE.SQL(sql).Find(entitiesPtr)

	//resultsSlice, _ = s.DBE.Query(sqlCount)

	//if len(resultsSlice) > 0 {
	//	results := resultsSlice[0]
	//	for _, value := range results {
	//		total, _ = strconv.ParseInt(string(value), 10, 64)
	//		break
	//	}
	//}

	//return total
}

func (s *OilSupplierService) GetDeleteSub2(TableName, where string , entitiesPtr interface{}, name string) {

	sql := `update OilSupplierCertSub a `
	if TableName == "OilBasisBuild" {
		sql += ` left join ` + TableName + ` b on b.Id = a.SubClassId`
	} else {
		sql += ` left join ` + TableName + ` b on b.ClassId = a.SubClassId`
	}
	sql += " set IsQuestion = 2, LackFile = concat(IFNULL(LackFile,''),',','" + name + "') where IsQuestion != 2 and " + where
	s.DBE.SQL(sql).Find(entitiesPtr)
}