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) 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 += ` 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.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 a.CreateUserId = u.Id `
	sql += ` LEFT JOIN Base_Organize org ON b.CommitComId = org.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) 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) GetBlackList(entitiesPtr interface{}, where string) {

	sql := "SELECT a.Id from BlackList a LEFT JOIN OilSupplier b on b.Id = a.SupplierId LEFT JOIN OilCorporateInfo c ON b.CommercialNo = c.CommercialNo where " + where

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