supplier_system/src/dashoo.cn/backend/api/business/oilsupplier/goodsaptitude/oilgoodsaptitudeService.go

package goodsaptitude

import (
	"strconv"

	. "dashoo.cn/backend/api/mydb"
	"dashoo.cn/utils"
	. "dashoo.cn/utils/db"
	"github.com/go-xorm/xorm"
)

type OilGoodsAptitudeService struct {
	MyServiceBase
}

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

type TotalResult struct {
	Total int64 `xorm:"INT(11) 'total'"`
}

func (s *OilGoodsAptitudeService) GetMyPagingEntitiesWithOrderBytbl(tableName string, pageIndex, itemsPerPage int64, order string, asc bool, entitiesPtr interface{}, where ...string) (total int64) {
	var err error
	var resultsSlice []map[string][]byte
	//获取表名
	if len(where) == 0 {
		if asc {
			err = s.DBE.Table(tableName).Limit(int(itemsPerPage), (int(pageIndex)-1)*int(itemsPerPage)).Asc(order).Find(entitiesPtr)
		} else {
			err = s.DBE.Table(tableName).Limit(int(itemsPerPage), (int(pageIndex)-1)*int(itemsPerPage)).Desc(order).Find(entitiesPtr)
		}
		//获取总记录数
		sql := "SELECT COUNT(*) AS total FROM " + tableName
		resultsSlice, err = s.DBE.Query(sql)

	} else {
		if asc {
			err = s.DBE.Table(tableName).Where(where[0]).Limit(int(itemsPerPage), (int(pageIndex)-1)*int(itemsPerPage)).Asc(order).Find(entitiesPtr)
		} else {
			err = s.DBE.Table(tableName).Where(where[0]).Limit(int(itemsPerPage), (int(pageIndex)-1)*int(itemsPerPage)).Desc(order).Find(entitiesPtr)
		}
		sql := "SELECT COUNT(*) AS total FROM " + tableName + " where " + where[0]
		resultsSlice, err = s.DBE.Query(sql)
	}
	//LogError(err)
	if len(resultsSlice) > 0 {
		results := resultsSlice[0]
		for _, value := range results {
			total, err = strconv.ParseInt(string(value), 10, 64)
			LogError(err)
			break
		}
	}
	return total
}

func (s *OilGoodsAptitudeService) GetGoodsList(goodsclass, where string) []GoodsBusiness {
	if where == "" {
		where = " 1=1 "
	}
	var sql string
	sql = `select Id, Code, Name, concat(Code, '  ', Name) as CodeName, ParentId, Remark, DeletionStateCode  
	from ` + goodsclass + ` where ` + where + `  order by Code asc `
	List := make([]GoodsBusiness, 0)
	utils.DBE.Sql(sql).Find(&List)
	return List
}



func (s *OilGoodsAptitudeService) GetList_2019(OilGoodsAptitudeName, OilGoodsAptitudeClassName string, pageIndex, itemsPerPage int64, orderby string, asc bool, entitiesPtr interface{}, where string) (total int64) {
	var resultsSlice []map[string][]byte

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

	var sql string
	sql = `select a.*, b.Code, b.Name `
	sql += ` from ` + OilGoodsAptitudeName + ` a `
	sql += ` left join ` + OilGoodsAptitudeClassName + " b on b.Id = a.ClassId"
	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 *OilGoodsAptitudeService) GetFCode() string {
	sql := "select group_concat(Code ORDER BY SortCode) FCodes from `Base_TableHeader` where CategoryCode= '01'"
	results, err :=s.DBE.QueryString(sql)
	if err != nil {
		return ""
	}
	return results[0]["FCodes"]
}

func (s *OilGoodsAptitudeService) GetGoodsAptitudeF(entity interface{}, where string) error {
	sql := "select F01,F02,F03,F04,F05,F06,F07,F08,F09,F10,"
	sql += "F11,F12,F13,F14,F15,F16,F17,F18,F19,F20,"
	sql += "F21,F22,F23,F24,F25,F26,F27,F28,F29,F30,"
	sql += "F31,F32,F33,F34,F35,F36,F37,F38,F39,F40,"
	sql += "F41,F42,F43,F44,F45,F46 from OilGoodsAptitude "
	if where != "" {
		sql = sql + " where " + where
	}
	err := s.DBE.Sql(sql).Find(entity)

	return err
}

func (s *OilGoodsAptitudeService) DeleteTable(tableName, where string) error {
	sql := "DELETE FROM " + tableName
	if where != "" {
		sql = sql + " Where " + where
	}
	_,err := s.DBE.Query(sql)
	return err
}

func (s *OilGoodsAptitudeService) GetMyPagingDelEntitiesWithOrderBytbl(supplierTableName, supplierCertTableName, OilInfoChangeName, OilCorporateInfoName, OilSupplierCertSubName,
OilSupplierFileName string, pageIndex, itemsPerPage int64, orderby string, asc bool, entitiesPtr interface{}, where,having, liftjoon string) (total int64) {

	var sql string
	sql = `select SQL_CALC_FOUND_ROWS a.Id,a.SupplierName,b.AccessCardNo,b.SupplierTypeCode,`
	sql += `a.LegalPerson,a.RegCapital,a.Mobile,b.AuditDate,b.ApplyTime,b.InFlag,a.ContactName,a.CommercialNo, `
	sql += `a.DepositBank,a.HseTraining,a.CompanyType,a.SetupTime,a.Address,a.Province,a.City,a.Street, `
	sql += `a.LinkAddress,a.LinkProvince,a.LinkCity,a.LinkStreet,a.BusinessScope, `
	sql += `b.InStyle, b.Id CertId, `
	sql += `group_concat(distinct e.NeedFileType) NeedFileType `
	if liftjoon != "" {
		sql += `,group_concat(distinct d.Name) CerSubName `
	}
	sql += `from ` + supplierTableName + ` a `
	sql += `left join ` + supplierCertTableName + ` b on b.SupplierId = a.Id `
	sql += liftjoon
	sql += `left join ` + OilSupplierFileName + ` e  on e.SupplierId = a.Id `
	sql += `LEFT JOIN tmp_OilSupplierCertSub t ON t.SupplierCertId = b.Id `
	sql += `where ` + where

	sql += ` group by a.Id,b.Id ` + having
	if asc {
		sql += ` order by ` + orderby + ` ASC `
	} else {
		sql += ` order by ` + orderby + ` DESC `
	}
	sql += ` limit ` + utils.ToStr((pageIndex-1)*itemsPerPage) + "," + utils.ToStr(itemsPerPage)


	sqlCount := "SELECT FOUND_ROWS() as total"

	var totalResult TotalResult
	session := s.DBE.NewSession()
	session.Begin()
	session.SQL(sql).Find(entitiesPtr)
	session.SQL(sqlCount).Get(&totalResult)
	session.Commit()
	total = totalResult.Total

	return total
}
//查看贸易或制造商准入资质个数
func (s *OilGoodsAptitudeService) GetNumOfPass(ismanf string) (total int64) {
	var resultsSlice []map[string][]byte
	sqlCount := "SELECT COUNT(Id) FROM Base_TableHeader WHERE IsManuf = '"+ismanf+"' AND CategoryCode = '01'"

	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 *OilGoodsAptitudeService) TruncateTable(tableName string) error {
	sql := "TRUNCATE TABLE " + tableName
	_,err := s.DBE.Query(sql)
	return err
}

func (s *OilGoodsAptitudeService) InsertGoodsAptitude(fromTableName, toTableName string) error {
	sql := "INSERT INTO " + toTableName + " SELECT * FROM " + fromTableName
	_,err := s.DBE.Query(sql)
	return err
}

func (s *OilGoodsAptitudeService) InsertTmpGoodsAptitude(parentId, fcode, val string) error {
	sql := "INSERT INTO `tmp_OilGoodsAptitude` (Edition,ClassId," + fcode + ") "
	sql += " VALUES ('1',"+ parentId +"," + val + ")"
	_, err := s.DBE.Query(sql)
	return err
}

func (s *OilGoodsAptitudeService) GetPagingCheckedEntities(pageIndex, itemsPerPage int64, orderby string,
	asc bool, entitiesPtr interface{}, where string) (total int64) {

	var sql string
	sql = `SELECT 
		 a.Id,a.SupplierName,
		 b.SupplierTypeCode, b.SupplierTypeName,
		 b.AccessCardNo, a.DepositBank,a.HseTraining,
		 a.CompanyType,a.SetupTime,a.Address,a.Province,a.City,a.Street,
		a.LegalPerson,a.RegCapital,a.Mobile,b.AuditDate,b.ApplyTime,b.InFlag,
		a.ContactName,a.CommercialNo,a.LinkAddress,a.LinkProvince,
		a.LinkCity,a.LinkStreet,a.BusinessScope,b.InStyle, b.Id CertId, 
		COUNT(sub.Id) AS subCnt,
		group_concat(DISTINCT header.Code) AS headerCodes,
		group_concat(distinct sub.Name) AS subNames,
		group_concat(distinct sub.Code) certSubCodes,
		group_concat(DISTINCT sub.SubClassId) AS subClassIds,
		max(goods.F01) AS F01,
		max(goods.F02) AS F02,
		max(goods.F03) AS F03,
		max(goods.F04) AS F04,
		max(goods.F05) AS F05,
		max(goods.F06) AS F06,
		max(goods.F07) AS F07,
		max(goods.F08) AS F08,
		max(goods.F09) AS F09,
		max(goods.F10) AS F10,
		max(goods.F11) AS F11,
		max(goods.F12) AS F12,
		max(goods.F13) AS F13,
		max(goods.F14) AS F14,
		max(goods.F15) AS F15,
		max(goods.F16) AS F16,
		max(goods.F17) AS F17,
		max(goods.F18) AS F18,
		max(goods.F19) AS F19,
		max(goods.F20) AS F20,
		max(goods.F21) AS F21,
		max(goods.F22) AS F22,
		max(goods.F23) AS F23,
		max(goods.F24) AS F24,
		max(goods.F25) AS F25,
		max(goods.F26) AS F26,
		max(goods.F27) AS F27,
		max(goods.F28) AS F28,
		max(goods.F29) AS F29,
		max(goods.F30) AS F30,
		max(goods.F31) AS F31,
		max(goods.F32) AS F32,
		max(goods.F33) AS F33,
		max(goods.F34) AS F34,
		max(goods.F35) AS F35,
		max(goods.F36) AS F36,
		max(goods.F37) AS F37,
		max(goods.F38) AS F38,
		max(goods.F39) AS F39,
		max(goods.F40) AS F40,
		max(goods.F41) AS F41,
		max(goods.F42) AS F42,
		max(goods.F43) AS F43,
		max(goods.F44) AS F44,
		max(goods.F45) AS F45,
		max(goods.F46) AS F46,
		
		(
			((if((max(goods.F01)='1' AND LOCATE('F01', group_concat(DISTINCT header.Code)) > 0) or (max(goods.F01) is null) or (max(goods.F01) = ''), TRUE, FALSE)) or 
			(if((max(goods.F36)='1' AND LOCATE('F36',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F36) is null) or (max(goods.F36) = ''), TRUE, FALSE)) or
			(if((max(goods.F37)='1' AND LOCATE('F37',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F37) is null) or (max(goods.F37) = ''), TRUE, FALSE))) AND 
			if((max(goods.F02)='1' AND LOCATE('F02',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F02) is null) or (max(goods.F02) = ''), TRUE, FALSE) AND
			if((max(goods.F03)='1' AND LOCATE('F03',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F03) is null) or (max(goods.F03) = ''), TRUE, FALSE) AND
			if((max(goods.F04)='1' AND LOCATE('F04',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F04) is null) or (max(goods.F04) = ''), TRUE, FALSE) AND
			if((max(goods.F05)='1' AND LOCATE('F05',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F05) is null) or (max(goods.F05) = ''), TRUE, FALSE) AND
			if((max(goods.F06)='1' AND LOCATE('F06',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F06) is null) or (max(goods.F06) = ''), TRUE, FALSE) AND
			if((max(goods.F07)='1' AND LOCATE('F07',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F07) is null) or (max(goods.F07) = ''), TRUE, FALSE) AND
			if((max(goods.F08)='1' AND LOCATE('F08',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F08) is null) or (max(goods.F08) = ''), TRUE, FALSE) AND
			if((max(goods.F09)='1' AND LOCATE('F09',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F09) is null) or (max(goods.F09) = ''), TRUE, FALSE) AND
			if((max(goods.F10)='1' AND LOCATE('F10',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F10) is null) or (max(goods.F10) = ''), TRUE, FALSE) AND
			if((max(goods.F11)='1' AND LOCATE('F11',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F11) is null) or (max(goods.F11) = ''), TRUE, FALSE) AND
			if((max(goods.F12)='1' AND LOCATE('F12',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F12) is null) or (max(goods.F12) = ''), TRUE, FALSE) AND
			if((max(goods.F13)='1' AND LOCATE('F13',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F13) is null) or (max(goods.F13) = ''), TRUE, FALSE) AND
			if((max(goods.F14)='1' AND LOCATE('F14',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F14) is null) or (max(goods.F14) = ''), TRUE, FALSE) AND
			if((max(goods.F15)='1' AND LOCATE('F15',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F15) is null) or (max(goods.F15) = ''), TRUE, FALSE) AND
			if((max(goods.F16)='1' AND LOCATE('F16',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F16) is null) or (max(goods.F16) = ''), TRUE, FALSE) AND
			if((max(goods.F17)='1' AND LOCATE('F17',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F17) is null) or (max(goods.F17) = ''), TRUE, FALSE) AND
			if((max(goods.F18)='1' AND LOCATE('F18',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F18) is null) or (max(goods.F18) = ''), TRUE, FALSE) AND
			if((max(goods.F19)='1' AND LOCATE('F19',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F19) is null) or (max(goods.F19) = ''), TRUE, FALSE) AND
			if((max(goods.F20)='1' AND LOCATE('F20',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F20) is null) or (max(goods.F20) = ''), TRUE, FALSE) AND
			if((max(goods.F21)='1' AND LOCATE('F21',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F21) is null) or (max(goods.F21) = ''), TRUE, FALSE) AND
			if((max(goods.F22)='1' AND LOCATE('F22',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F22) is null) or (max(goods.F22) = ''), TRUE, FALSE) AND
			if((max(goods.F23)='1' AND LOCATE('F23',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F23) is null) or (max(goods.F23) = ''), TRUE, FALSE) AND
			if((max(goods.F24)='1' AND LOCATE('F24',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F24) is null) or (max(goods.F24) = ''), TRUE, FALSE) AND
			if((max(goods.F25)='1' AND LOCATE('F25',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F25) is null) or (max(goods.F25) = ''), TRUE, FALSE) AND
			if((max(goods.F26)='1' AND LOCATE('F26',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F26) is null) or (max(goods.F26) = ''), TRUE, FALSE) AND
			if((max(goods.F27)='1' AND LOCATE('F27',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F27) is null) or (max(goods.F27) = ''), TRUE, FALSE) AND
			if((max(goods.F28)='1' AND LOCATE('F28',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F28) is null) or (max(goods.F28) = ''), TRUE, FALSE) AND
			if((max(goods.F29)='1' AND LOCATE('F29',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F29) is null) or (max(goods.F29) = ''), TRUE, FALSE) AND
			if((max(goods.F30)='1' AND LOCATE('F30',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F30) is null) or (max(goods.F30) = ''), TRUE, FALSE) AND
			if((max(goods.F31)='1' AND LOCATE('F31',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F31) is null) or (max(goods.F31) = ''), TRUE, FALSE) AND
			if((max(goods.F32)='1' AND LOCATE('F32',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F32) is null) or (max(goods.F32) = ''), TRUE, FALSE) AND
			if((max(goods.F33)='1' AND LOCATE('F33',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F33) is null) or (max(goods.F33) = ''), TRUE, FALSE) AND
			if((max(goods.F34)='1' AND LOCATE('F34',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F34) is null) or (max(goods.F34) = ''), TRUE, FALSE) AND
			if((max(goods.F35)='1' AND LOCATE('F35',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F35) is null) or (max(goods.F35) = ''), TRUE, FALSE) AND
			if((max(goods.F38)='1' AND LOCATE('F38',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F38) is null) or (max(goods.F38) = ''), TRUE, FALSE) AND
			if((max(goods.F39)='1' AND LOCATE('F39',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F39) is null) or (max(goods.F39) = ''), TRUE, FALSE) AND
			if((max(goods.F40)='1' AND LOCATE('F40',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F40) is null) or (max(goods.F40) = ''), TRUE, FALSE) AND
			if((max(goods.F41)='1' AND LOCATE('F41',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F41) is null) or (max(goods.F41) = ''), TRUE, FALSE) AND
			if((max(goods.F42)='1' AND LOCATE('F42',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F42) is null) or (max(goods.F42) = ''), TRUE, FALSE) AND
			if((max(goods.F43)='1' AND LOCATE('F43',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F43) is null) or (max(goods.F43) = ''), TRUE, FALSE) AND
			if((max(goods.F44)='1' AND LOCATE('F44',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F44) is null) or (max(goods.F44) = ''), TRUE, FALSE) AND
			if((max(goods.F45)='1' AND LOCATE('F45',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F45) is null) or (max(goods.F45) = ''), TRUE, FALSE) AND
			if((max(goods.F46)='1' AND LOCATE('F46',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F46) is null) or (max(goods.F46) = ''), TRUE, FALSE) 
			) AS checked
		
		 FROM OilSupplier a
			 LEFT JOIN OilSupplierCert b ON a.Id=b.SupplierId
			 LEFT JOIN OilSupplierCertSub sub ON b.Id=sub.SupplierCertId and sub.Type in (1, 3) 
			 LEFT JOIN OilSupplierFile files ON a.Id=files.SupplierId
			 LEFT JOIN Base_TableHeader header ON files.NeedFileType=header.Name
			 LEFT JOIN tmp_OilGoodsAptitudeClass class ON sub.Code=class.Code
			 LEFT JOIN tmp_OilGoodsAptitude goods ON class.Id=goods.ClassId
			 WHERE b.SupplierTypeCode='01' and `
	sql += where
	sql += ` GROUP BY a.Id, b.Id `
	if asc {
		sql += ` order by ` + orderby + ` ASC `
	} else {
		sql += ` order by ` + orderby + ` DESC `
	}
	sql += ` limit ` + utils.ToStr((pageIndex-1)*itemsPerPage) + "," + utils.ToStr(itemsPerPage)

	sqlCount := "SELECT count(*) as total FROM OilSupplier a LEFT JOIN OilSupplierCert b ON a.Id=b.SupplierId WHERE b.SupplierTypeCode='03'"

	var totalResult TotalResult
	s.DBE.SQL(sql).Find(entitiesPtr)
	s.DBE.SQL(sqlCount).Get(&totalResult)
	total = totalResult.Total

	return total
}

func (s *OilGoodsAptitudeService) GetPagingTmpCheckedEntities(entitiesPtr interface{}, where string) (total int64) {

	var sql string
	sql = `SELECT 
			 a.Id,a.SupplierName,
			 b.SupplierTypeCode, b.SupplierTypeName,
			 b.AccessCardNo, a.DepositBank,a.HseTraining,
			 a.CompanyType,a.SetupTime,a.Address,a.Province,a.City,a.Street,
			a.LegalPerson,a.RegCapital,a.Mobile,b.AuditDate,b.ApplyTime,b.InFlag,
			a.ContactName,a.CommercialNo,a.LinkAddress,a.LinkProvince,
			a.LinkCity,a.LinkStreet,a.BusinessScope,b.InStyle, b.Id CertId,
			group_concat(DISTINCT header.Code) AS HeaderCodes,
			min(sub.SubClassId) as MinClassId,
			COUNT(goods.GoodsLevel < sub.GoodsLevel or null) AS OneTwoCount,
			COUNT(goods.GoodsLevel > sub.GoodsLevel or null) AS TwoOneCount,
			(
			((if((max(goods.F01)='1' AND LOCATE('F01', group_concat(DISTINCT header.Code)) > 0) or (max(goods.F01) is null) or (max(goods.F01) = ''), TRUE, FALSE)) or 
			(if((max(goods.F36)='1' AND LOCATE('F36',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F36) is null) or (max(goods.F36) = ''), TRUE, FALSE)) or
			(if((max(goods.F37)='1' AND LOCATE('F37',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F37) is null) or (max(goods.F37) = ''), TRUE, FALSE))) AND 
			if((max(goods.F02)='1' AND LOCATE('F02',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F02) is null) or (max(goods.F02) = ''), TRUE, FALSE) AND
			if((max(goods.F03)='1' AND LOCATE('F03',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F03) is null) or (max(goods.F03) = ''), TRUE, FALSE) AND
			if((max(goods.F04)='1' AND LOCATE('F04',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F04) is null) or (max(goods.F04) = ''), TRUE, FALSE) AND
			if((max(goods.F05)='1' AND LOCATE('F05',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F05) is null) or (max(goods.F05) = ''), TRUE, FALSE) AND
			if((max(goods.F06)='1' AND LOCATE('F06',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F06) is null) or (max(goods.F06) = ''), TRUE, FALSE) AND
			if((max(goods.F07)='1' AND LOCATE('F07',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F07) is null) or (max(goods.F07) = ''), TRUE, FALSE) AND
			if((max(goods.F08)='1' AND LOCATE('F08',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F08) is null) or (max(goods.F08) = ''), TRUE, FALSE) AND
			if((max(goods.F09)='1' AND LOCATE('F09',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F09) is null) or (max(goods.F09) = ''), TRUE, FALSE) AND
			if((max(goods.F10)='1' AND LOCATE('F10',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F10) is null) or (max(goods.F10) = ''), TRUE, FALSE) AND
			if((max(goods.F11)='1' AND LOCATE('F11',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F11) is null) or (max(goods.F11) = ''), TRUE, FALSE) AND
			if((max(goods.F12)='1' AND LOCATE('F12',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F12) is null) or (max(goods.F12) = ''), TRUE, FALSE) AND
			if((max(goods.F13)='1' AND LOCATE('F13',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F13) is null) or (max(goods.F13) = ''), TRUE, FALSE) AND
			if((max(goods.F14)='1' AND LOCATE('F14',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F14) is null) or (max(goods.F14) = ''), TRUE, FALSE) AND
			if((max(goods.F15)='1' AND LOCATE('F15',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F15) is null) or (max(goods.F15) = ''), TRUE, FALSE) AND
			if((max(goods.F16)='1' AND LOCATE('F16',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F16) is null) or (max(goods.F16) = ''), TRUE, FALSE) AND
			if((max(goods.F17)='1' AND LOCATE('F17',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F17) is null) or (max(goods.F17) = ''), TRUE, FALSE) AND
			if((max(goods.F18)='1' AND LOCATE('F18',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F18) is null) or (max(goods.F18) = ''), TRUE, FALSE) AND
			if((max(goods.F19)='1' AND LOCATE('F19',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F19) is null) or (max(goods.F19) = ''), TRUE, FALSE) AND
			if((max(goods.F20)='1' AND LOCATE('F20',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F20) is null) or (max(goods.F20) = ''), TRUE, FALSE) AND
			if((max(goods.F21)='1' AND LOCATE('F21',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F21) is null) or (max(goods.F21) = ''), TRUE, FALSE) AND
			if((max(goods.F22)='1' AND LOCATE('F22',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F22) is null) or (max(goods.F22) = ''), TRUE, FALSE) AND
			if((max(goods.F23)='1' AND LOCATE('F23',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F23) is null) or (max(goods.F23) = ''), TRUE, FALSE) AND
			if((max(goods.F24)='1' AND LOCATE('F24',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F24) is null) or (max(goods.F24) = ''), TRUE, FALSE) AND
			if((max(goods.F25)='1' AND LOCATE('F25',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F25) is null) or (max(goods.F25) = ''), TRUE, FALSE) AND
			if((max(goods.F26)='1' AND LOCATE('F26',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F26) is null) or (max(goods.F26) = ''), TRUE, FALSE) AND
			if((max(goods.F27)='1' AND LOCATE('F27',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F27) is null) or (max(goods.F27) = ''), TRUE, FALSE) AND
			if((max(goods.F28)='1' AND LOCATE('F28',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F28) is null) or (max(goods.F28) = ''), TRUE, FALSE) AND
			if((max(goods.F29)='1' AND LOCATE('F29',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F29) is null) or (max(goods.F29) = ''), TRUE, FALSE) AND
			if((max(goods.F30)='1' AND LOCATE('F30',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F30) is null) or (max(goods.F30) = ''), TRUE, FALSE) AND
			if((max(goods.F31)='1' AND LOCATE('F31',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F31) is null) or (max(goods.F31) = ''), TRUE, FALSE) AND
			if((max(goods.F32)='1' AND LOCATE('F32',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F32) is null) or (max(goods.F32) = ''), TRUE, FALSE) AND
			if((max(goods.F33)='1' AND LOCATE('F33',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F33) is null) or (max(goods.F33) = ''), TRUE, FALSE) AND
			if((max(goods.F34)='1' AND LOCATE('F34',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F34) is null) or (max(goods.F34) = ''), TRUE, FALSE) AND
			if((max(goods.F35)='1' AND LOCATE('F35',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F35) is null) or (max(goods.F35) = ''), TRUE, FALSE) AND
			if((max(goods.F38)='1' AND LOCATE('F38',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F38) is null) or (max(goods.F38) = ''), TRUE, FALSE) AND
			if((max(goods.F39)='1' AND LOCATE('F39',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F39) is null) or (max(goods.F39) = ''), TRUE, FALSE) AND
			if((max(goods.F40)='1' AND LOCATE('F40',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F40) is null) or (max(goods.F40) = ''), TRUE, FALSE) AND
			if((max(goods.F41)='1' AND LOCATE('F41',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F41) is null) or (max(goods.F41) = ''), TRUE, FALSE) AND
			if((max(goods.F42)='1' AND LOCATE('F42',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F42) is null) or (max(goods.F42) = ''), TRUE, FALSE) AND
			if((max(goods.F43)='1' AND LOCATE('F43',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F43) is null) or (max(goods.F43) = ''), TRUE, FALSE) AND
			if((max(goods.F44)='1' AND LOCATE('F44',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F44) is null) or (max(goods.F44) = ''), TRUE, FALSE) AND
			if((max(goods.F45)='1' AND LOCATE('F45',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F45) is null) or (max(goods.F45) = ''), TRUE, FALSE) AND
			if((max(goods.F46)='1' AND LOCATE('F46',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F46) is null) or (max(goods.F46) = ''), TRUE, FALSE) 
			) AS checked
			
			 FROM OilSupplier a
			 LEFT JOIN OilSupplierCert b ON a.Id=b.SupplierId
			 LEFT JOIN OilSupplierCertSub sub ON b.Id=sub.SupplierCertId
			 LEFT JOIN OilSupplierFile files ON a.Id=files.SupplierId
			 LEFT JOIN Base_TableHeader header ON files.NeedFileType=header.Name and header.CategoryCode = '01'
			 LEFT JOIN tmp_OilGoodsAptitudeClass class ON sub.Code=class.Code
			 LEFT JOIN tmp_OilGoodsAptitude goods ON class.Id=goods.ClassId
			 WHERE`
	sql += where
	sql += ` GROUP BY a.Id, b.Id `
	//sql += ` limit ` + utils.ToStr((pageIndex-1)*itemsPerPage) + "," + utils.ToStr(itemsPerPage)

	//sqlCount := "SELECT count(DISTINCT a.Id) as total FROM OilSupplier a LEFT JOIN OilSupplierCert b ON a.Id=b.SupplierId LEFT JOIN OilSupplierCertSub sub ON b.Id=sub.SupplierCertId LEFT JOIN OilSupplierFile files ON a.Id=files.SupplierId LEFT JOIN Base_TableHeader header ON files.NeedFileType=header.Name LEFT JOIN tmp_OilGoodsAptitudeClass class ON sub.Code=class.Code LEFT JOIN tmp_OilGoodsAptitude goods ON class.Id=goods.ClassId WHERE b.SupplierTypeCode='01' AND " + having

	var totalResult TotalResult
	s.DBE.SQL(sql).Find(entitiesPtr)
	//s.DBE.SQL(sqlCount).Get(&totalResult)
	total = totalResult.Total

	return total
}

func (s *OilGoodsAptitudeService) GetPagingTmpSubEntities(pageIndex, itemsPerPage int64, orderby string,
	asc bool, entitiesPtr interface{}, having string) (total int64) {

	var sql string
	sql = `SELECT 
		 a.*,group_concat(DISTINCT header.Code) AS HeaderCodes
		 FROM OilSupplierCertSub a
		 LEFT JOIN OilSupplierFile files ON a.SupplierId=files.SupplierId
		 LEFT JOIN Base_TableHeader header ON files.NeedFileType=header.Name
		 LEFT JOIN tmp_OilGoodsAptitudeClass class ON a.Code=class.Code
		 LEFT JOIN tmp_OilGoodsAptitude goods ON class.Id=goods.ClassId`
	sql += " where (" + having + ")"
	sql += ` GROUP BY a.Id `
	if asc {
		sql += ` order by ` + orderby + ` ASC `
	} else {
		sql += ` order by ` + orderby + ` DESC `
	}
	sql += ` limit ` + utils.ToStr((pageIndex-1)*itemsPerPage) + "," + utils.ToStr(itemsPerPage)

	sqlCount := "SELECT count(*) as total FROM OilSupplierCertSub a LEFT JOIN tmp_OilGoodsAptitudeClass class ON a.Code=class.Code LEFT JOIN tmp_OilGoodsAptitude goods ON class.Id=goods.ClassId  where (" + having + ") "

	var totalResult TotalResult
	s.DBE.SQL(sql).Find(entitiesPtr)
	s.DBE.SQL(sqlCount).Get(&totalResult)
	total = totalResult.Total

	return total
}

func (s *OilGoodsAptitudeService) GetPClassEntities(pageIndex, itemsPerPage int64, orderby string,
	asc bool, entitiesPtr interface{}, having string, Conditions string) (total int64) {

	var sql string
	sql = `SELECT a.*,b.*,tmpa.F01 AS tmpF01,tmpa.F02 AS tmpF02,tmpa.F03 AS tmpF03,tmpa.F04 AS tmpF04,tmpa.F05 AS tmpF05,tmpa.F06 AS tmpF06,tmpa.F07 AS tmpF07,
		 tmpa.F08 AS tmpF08,tmpa.F09 AS tmpF09,tmpa.F10 AS tmpF10,tmpa.F11 AS tmpF11,tmpa.F12 AS tmpF12,tmpa.F13 AS tmpF13,tmpa.F14 AS tmpF14,
		 tmpa.F15 AS tmpF15,tmpa.F16 AS tmpF16,tmpa.F17 AS tmpF17,tmpa.F18 AS tmpF18,tmpa.F19 AS tmpF19,tmpa.F20 AS tmpF20,tmpa.F21 AS tmpF21,
		 tmpa.F22 AS tmpF22,tmpa.F23 AS tmpF23,tmpa.F24 AS tmpF24,tmpa.F25 AS tmpF25,tmpa.F26 AS tmpF26,tmpa.F27 AS tmpF27,tmpa.F28 AS tmpF27,
		 tmpa.F29 AS tmpF29,tmpa.F30 AS tmpF30,tmpa.F31 AS tmpF31,tmpa.F32 AS tmpF32,tmpa.F33 AS tmpF33,tmpa.F34 AS tmpF34,tmpa.F35 AS tmpF35,
		 tmpa.F36 AS tmpF36,tmpa.F37 AS tmpF37,tmpa.F38 AS tmpF38,tmpa.F39 AS tmpF39,tmpa.F40 AS tmpF40,tmpa.F41 AS tmpF41,tmpa.F42 AS tmpF42,
  		 tmpa.F42 AS tmpF42,tmpa.F43 AS tmpF43,tmpa.F44 AS tmpF44,tmpa.F45 AS tmpF45,tmpa.F46 AS tmpF46,tmpa.GoodsLevel AS tmpGoodsLevel,
		if( ((tmpa.F01 is null && a.F01 = 1) or (tmpa.F01 = 1 && (a.F01 is null or a.F01 = 0))) or ((tmpa.F02 is null && a.F02 = 1) or (tmpa.F02 = 1 && (a.F02 is null or a.F02 = 0))) or ((tmpa.F03 is null && a.F03 = 1) or (tmpa.F03 = 1 && (a.F03 is null or a.F03 = 0))) or ((tmpa.F04 is null && a.F04 = 1) or (tmpa.F04 = 1 && (a.F04 is null or a.F04 = 0))) or ((tmpa.F05 is null && a.F05 = 1) or (tmpa.F05 = 1 && (a.F05 is null or a.F05 = 0))) or ((tmpa.F06 is null && a.F06 = 1) or (tmpa.F06 = 1 && (a.F06 is null or a.F06 = 0))) or ((tmpa.F07 is null && a.F07 = 1) or (tmpa.F07 = 1 && (a.F07 is null or a.F07 = 0))) or ((tmpa.F08 is null && a.F08 = 1) or (tmpa.F08 = 1 && (a.F08 is null or a.F08 = 0))) or ((tmpa.F09 is null && a.F09 = 1) or (tmpa.F09 = 1 && (a.F09 is null or a.F09 = 0))) or ((tmpa.F10 is null && a.F10 = 1) or (tmpa.F10 = 1 && (a.F10 is null or a.F10 = 0)))
			or ((tmpa.F11 is null && a.F11 = 1) or (tmpa.F11 = 1 && (a.F11 is null or a.F11 = 0))) or ((tmpa.F12 is null && a.F12 = 1) or (tmpa.F12 = 1 && (a.F12 is null or a.F12 = 0))) or ((tmpa.F13 is null && a.F13 = 1) or (tmpa.F13 = 1 && (a.F13 is null or a.F13 = 0))) or ((tmpa.F14 is null && a.F14 = 1) or (tmpa.F14 = 1 && (a.F14 is null or a.F14 = 0))) or ((tmpa.F15 is null && a.F15 = 1) or (tmpa.F15 = 1 && (a.F15 is null or a.F15 = 0))) or ((tmpa.F16 is null && a.F16 = 1) or (tmpa.F16 = 1 && (a.F16 is null or a.F16 = 0))) or ((tmpa.F17 is null && a.F17 = 1) or (tmpa.F17 = 1 && (a.F17 is null or a.F17 = 0))) or ((tmpa.F18 is null && a.F18 = 1) or (tmpa.F18 = 1 && (a.F18 is null or a.F18 = 0))) or ((tmpa.F19 is null && a.F19 = 1) or (tmpa.F19 = 1 && (a.F19 is null or a.F19 = 0))) or ((tmpa.F20 is null && a.F20 = 1) or (tmpa.F20 = 1 && (a.F20 is null or a.F20 = 0)))
			or ((tmpa.F21 is null && a.F21 = 1) or (tmpa.F21 = 1 && (a.F21 is null or a.F21 = 0))) or ((tmpa.F22 is null && a.F22 = 1) or (tmpa.F22 = 1 && (a.F22 is null or a.F22 = 0))) or ((tmpa.F23 is null && a.F23 = 1) or (tmpa.F23 = 1 && (a.F23 is null or a.F23 = 0))) or ((tmpa.F24 is null && a.F24 = 1) or (tmpa.F24 = 1 && (a.F24 is null or a.F24 = 0))) or ((tmpa.F25 is null && a.F25 = 1) or (tmpa.F25 = 1 && (a.F25 is null or a.F25 = 0))) or ((tmpa.F26 is null && a.F26 = 1) or (tmpa.F26 = 1 && (a.F26 is null or a.F26 = 0))) or ((tmpa.F27 is null && a.F27 = 1) or (tmpa.F27 = 1 && (a.F27 is null or a.F27 = 0))) or ((tmpa.F28 is null && a.F28 = 1) or (tmpa.F28 = 1 && (a.F28 is null or a.F28 = 0))) or ((tmpa.F29 is null && a.F29 = 1) or (tmpa.F29 = 1 && (a.F29 is null or a.F29 = 0))) or ((tmpa.F30 is null && a.F30 = 1) or (tmpa.F30 = 1 && (a.F30 is null or a.F30 = 0)))
			or ((tmpa.F31 is null && a.F31 = 1) or (tmpa.F31 = 1 && (a.F31 is null or a.F31 = 0))) or ((tmpa.F32 is null && a.F32 = 1) or (tmpa.F32 = 1 && (a.F32 is null or a.F32 = 0))) or ((tmpa.F33 is null && a.F33 = 1) or (tmpa.F33 = 1 && (a.F33 is null or a.F33 = 0))) or ((tmpa.F34 is null && a.F34 = 1) or (tmpa.F34 = 1 && (a.F34 is null or a.F34 = 0))) or ((tmpa.F35 is null && a.F35 = 1) or (tmpa.F35 = 1 && (a.F35 is null or a.F35 = 0))) or ((tmpa.F36 is null && a.F36 = 1) or (tmpa.F36 = 1 && (a.F36 is null or a.F36 = 0))) or ((tmpa.F37 is null && a.F37 = 1) or (tmpa.F37 = 1 && (a.F37 is null or a.F37 = 0))) or ((tmpa.F38 is null && a.F38 = 1) or (tmpa.F38 = 1 && (a.F38 is null or a.F38 = 0))) or ((tmpa.F39 is null && a.F39 = 1) or (tmpa.F39 = 1 && (a.F39 is null or a.F39 = 0))) or ((tmpa.F40 is null && a.F40 = 1) or (tmpa.F40 = 1 && (a.F40 is null or a.F40 = 0)))
			or ((tmpa.F41 is null && a.F41 = 1) or (tmpa.F41 = 1 && (a.F41 is null or a.F41 = 0))) or ((tmpa.F42 is null && a.F42 = 1) or (tmpa.F42 = 1 && (a.F42 is null or a.F42 = 0))) or ((tmpa.F43 is null && a.F43 = 1) or (tmpa.F43 = 1 && (a.F43 is null or a.F43 = 0))) or ((tmpa.F44 is null && a.F44 = 1) or (tmpa.F44 = 1 && (a.F44 is null or a.F44 = 0))) or ((tmpa.F45 is null && a.F45 = 1) or (tmpa.F45 = 1 && (a.F45 is null or a.F45 = 0))) or ((tmpa.F46 is null && a.F46 = 1) or (tmpa.F46 = 1 && (a.F46 is null or a.F46 = 0)))  
		,true, false) as checked`

	if Conditions == "5" {
		sql += " FROM tmp_OilGoodsAptitude a LEFT JOIN tmp_OilGoodsAptitudeClass b ON b.Id = a.ClassId LEFT JOIN OilGoodsAptitudeClass tmpb ON tmpb.Code = b.Code LEFT JOIN OilGoodsAptitude tmpa ON tmpa.ClassId = tmpb.Id where (" + having + ")"
	} else {
		sql += " FROM OilGoodsAptitude a LEFT JOIN OilGoodsAptitudeClass b ON b.Id = a.ClassId LEFT JOIN tmp_OilGoodsAptitudeClass tmpb ON tmpb.Code = b.Code LEFT JOIN tmp_OilGoodsAptitude tmpa ON tmpa.ClassId = tmpb.Id where (" + having + ")"
	}
	if asc {
		sql += ` order by b.` + orderby + ` ASC `
	} else {
		sql += ` order by b.` + orderby + ` DESC `
	}
	sql += ` limit ` + utils.ToStr((pageIndex-1)*itemsPerPage) + "," + utils.ToStr(itemsPerPage)

	sqlCount := "SELECT count(*) FROM OilGoodsAptitude a LEFT JOIN OilGoodsAptitudeClass b ON b.Id = a.ClassId LEFT JOIN tmp_OilGoodsAptitudeClass tmpb ON tmpb.Code = b.Code LEFT JOIN tmp_OilGoodsAptitude tmpa ON tmpa.ClassId = tmpb.Id where (" + having + ") "
	if Conditions == "5" {
		sqlCount = "SELECT count(*) FROM tmp_OilGoodsAptitude a LEFT JOIN tmp_OilGoodsAptitudeClass b ON b.Id = a.ClassId LEFT JOIN OilGoodsAptitudeClass tmpb ON tmpb.Code = b.Code LEFT JOIN OilGoodsAptitude tmpa ON tmpa.ClassId = tmpb.Id where (" + having + ") "
	}

	//var totalResult TotalResult
	s.DBE.SQL(sql).Find(entitiesPtr)
	//s.DBE.SQL(sqlCount).Get(&totalResult)
	//total = totalResult.Total
	var resultsSlice []map[string][]byte
	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 *OilGoodsAptitudeService) GetPagingSubEntities(pageIndex, itemsPerPage int64, orderby string,
	asc bool, entitiesPtr interface{}, having string) (total int64) {

	var sql string
	sql = `SELECT 
		 a.*,group_concat(DISTINCT header.Code) AS HeaderCodes
		 FROM OilSupplierCertSub a
		 LEFT JOIN OilSupplierFile files ON a.SupplierId=files.SupplierId
		 LEFT JOIN Base_TableHeader header ON files.NeedFileType=header.Name
		 LEFT JOIN OilGoodsAptitudeClass class ON a.Code=class.Code
		 LEFT JOIN OilGoodsAptitude goods ON class.Id=goods.ClassId
		 LEFT JOIN OilSupplierContrastNew con ON con.Id=a.SupplierId`
	sql += " where (" + having + ")"
	sql += ` GROUP BY a.Id `
	if asc {
		sql += ` order by ` + orderby + ` ASC `
	} else {
		sql += ` order by ` + orderby + ` DESC `
	}
	if itemsPerPage != 0 {
		sql += ` limit ` + utils.ToStr((pageIndex-1)*itemsPerPage) + "," + utils.ToStr(itemsPerPage)
	}

	sqlCount := "SELECT count(*) as total FROM OilSupplierCertSub a LEFT JOIN OilSupplierFile files ON a.SupplierId=files.SupplierId LEFT JOIN Base_TableHeader header ON files.NeedFileType=header.Name LEFT JOIN OilGoodsAptitudeClass class ON a.Code=class.Code LEFT JOIN OilGoodsAptitude goods ON class.Id=goods.ClassId where (" + having + ")"

	var totalResult TotalResult
	s.DBE.SQL(sql).Find(entitiesPtr)
	s.DBE.SQL(sqlCount).Get(&totalResult)
	total = totalResult.Total

	return total
}

func (s *OilGoodsAptitudeService) GetTableTotal(where string, entitiesPtr interface{}) (total int64) {

	sqlCount := "SELECT count(*) as total FROM OilSupplierCertSub where (" + where + ")"

	sql := "SELECT * FROM OilSupplierCertSub where (" + where + ")"
	s.DBE.SQL(sql).Find(entitiesPtr)

	var totalResult TotalResult
	s.DBE.SQL(sqlCount).Get(&totalResult)
	total = totalResult.Total

	return total
}

func (s *OilGoodsAptitudeService) GetNoSubCntTotal(where string) (total int64) {

	sqlCount := "select count(a.`Name`) as total from OilSupplierCertSub a left join tmp_OilGoodsAptitudeClass b on b.`Name` = a.`Name` where (" + where + ")"

	var totalResult TotalResult
	s.DBE.SQL(sqlCount).Get(&totalResult)
	total = totalResult.Total

	return total
}

func (s *OilGoodsAptitudeService) GetEntityByCode(where string, entityPtr interface{}) (has bool) {
	sql := "SELECT COUNT((sub.GoodsLevel < goods.GoodsLevel) or null) AS OneTwoCount,COUNT((sub.GoodsLevel > goods.GoodsLevel) or null) AS TwoOneCount FROM OilSupplierCertSub sub LEFT JOIN tmp_OilGoodsAptitude goods ON sub.SubClassId=goods.ClassId where (" + where + ")"
	s.DBE.SQL(sql).Get(entityPtr)
	return
}