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{}, having 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), TRUE, FALSE) AND 
		if((max(goods.F02)='1' AND LOCATE('F02', group_concat(DISTINCT header.Code)) > 0), TRUE, FALSE) AND
		if((max(goods.F03)='1' AND LOCATE('F03', group_concat(DISTINCT header.Code)) > 0), TRUE, FALSE) AND
		if((max(goods.F04)='1' AND LOCATE('F04', group_concat(DISTINCT header.Code)) > 0), TRUE, FALSE) AND
		if((max(goods.F05)='1' AND LOCATE('F05', group_concat(DISTINCT header.Code)) > 0), TRUE, FALSE) AND
		if((max(goods.F06)='1' AND LOCATE('F06', group_concat(DISTINCT header.Code)) > 0), TRUE, FALSE) AND
		if((max(goods.F07)='1' AND LOCATE('F07', group_concat(DISTINCT header.Code)) > 0), TRUE, FALSE) AND
		if((max(goods.F08)='1' AND LOCATE('F08', group_concat(DISTINCT header.Code)) > 0), TRUE, FALSE) AND
		if((max(goods.F09)='1' AND LOCATE('F09', group_concat(DISTINCT header.Code)) > 0), TRUE, FALSE) AND
		if((max(goods.F10)='1' AND LOCATE('F10', group_concat(DISTINCT header.Code)) > 0), TRUE, FALSE) AND
		if((max(goods.F11)='1' AND LOCATE('F12', group_concat(DISTINCT header.Code)) > 0), TRUE, FALSE) AND
		if((max(goods.F13)='1' AND LOCATE('F13', group_concat(DISTINCT header.Code)) > 0), TRUE, FALSE) AND
		if((max(goods.F14)='1' AND LOCATE('F14', group_concat(DISTINCT header.Code)) > 0), TRUE, FALSE) AND
		if((max(goods.F15)='1' AND LOCATE('F15', group_concat(DISTINCT header.Code)) > 0), TRUE, FALSE) AND
		if((max(goods.F16)='1' AND LOCATE('F16', group_concat(DISTINCT header.Code)) > 0), TRUE, FALSE) AND
		if((max(goods.F17)='1' AND LOCATE('F17', group_concat(DISTINCT header.Code)) > 0), TRUE, FALSE) AND
		if((max(goods.F18)='1' AND LOCATE('F18', group_concat(DISTINCT header.Code)) > 0), TRUE, FALSE) AND
		if((max(goods.F19)='1' AND LOCATE('F19', group_concat(DISTINCT header.Code)) > 0), TRUE, FALSE) AND
		if((max(goods.F20)='1' AND LOCATE('F20', group_concat(DISTINCT header.Code)) > 0), TRUE, FALSE) AND
		if((max(goods.F21)='1' AND LOCATE('F21', group_concat(DISTINCT header.Code)) > 0), TRUE, FALSE) AND
		if((max(goods.F22)='1' AND LOCATE('F22', group_concat(DISTINCT header.Code)) > 0), TRUE, FALSE) AND
		if((max(goods.F23)='1' AND LOCATE('F23', group_concat(DISTINCT header.Code)) > 0), TRUE, FALSE) AND
		if((max(goods.F24)='1' AND LOCATE('F24', group_concat(DISTINCT header.Code)) > 0), TRUE, FALSE) AND
		if((max(goods.F25)='1' AND LOCATE('F25', group_concat(DISTINCT header.Code)) > 0), TRUE, FALSE) AND
		if((max(goods.F26)='1' AND LOCATE('F26', group_concat(DISTINCT header.Code)) > 0), TRUE, FALSE) AND
		if((max(goods.F27)='1' AND LOCATE('F27', group_concat(DISTINCT header.Code)) > 0), TRUE, FALSE) AND
		if((max(goods.F28)='1' AND LOCATE('F28', group_concat(DISTINCT header.Code)) > 0), TRUE, FALSE) AND
		if((max(goods.F29)='1' AND LOCATE('F29', group_concat(DISTINCT header.Code)) > 0), TRUE, FALSE) AND
		if((max(goods.F30)='1' AND LOCATE('F30', group_concat(DISTINCT header.Code)) > 0), TRUE, FALSE) AND
		if((max(goods.F31)='1' AND LOCATE('F31', group_concat(DISTINCT header.Code)) > 0), TRUE, FALSE) AND
		if((max(goods.F32)='1' AND LOCATE('F32', group_concat(DISTINCT header.Code)) > 0), TRUE, FALSE) AND
		if((max(goods.F33)='1' AND LOCATE('F33', group_concat(DISTINCT header.Code)) > 0), TRUE, FALSE) AND
		if((max(goods.F34)='1' AND LOCATE('F34', group_concat(DISTINCT header.Code)) > 0), TRUE, FALSE) AND
		if((max(goods.F35)='1' AND LOCATE('F35', group_concat(DISTINCT header.Code)) > 0), TRUE, FALSE) AND
		if((max(goods.F36)='1' AND LOCATE('F36', group_concat(DISTINCT header.Code)) > 0), TRUE, FALSE) AND
		if((max(goods.F37)='1' AND LOCATE('F37', group_concat(DISTINCT header.Code)) > 0), TRUE, FALSE) AND
		if((max(goods.F38)='1' AND LOCATE('F38', group_concat(DISTINCT header.Code)) > 0), TRUE, FALSE) AND
		if((max(goods.F39)='1' AND LOCATE('F39', group_concat(DISTINCT header.Code)) > 0), TRUE, FALSE) AND
		if((max(goods.F40)='1' AND LOCATE('F40', group_concat(DISTINCT header.Code)) > 0), TRUE, FALSE) AND
		if((max(goods.F41)='1' AND LOCATE('F41', group_concat(DISTINCT header.Code)) > 0), TRUE, FALSE) AND
		if((max(goods.F42)='1' AND LOCATE('F42', group_concat(DISTINCT header.Code)) > 0), TRUE, FALSE) AND
		if((max(goods.F43)='1' AND LOCATE('F43', group_concat(DISTINCT header.Code)) > 0), TRUE, FALSE) AND
		if((max(goods.F44)='1' AND LOCATE('F44', group_concat(DISTINCT header.Code)) > 0), TRUE, FALSE) AND
		if((max(goods.F45)='1' AND LOCATE('F45', group_concat(DISTINCT header.Code)) > 0), TRUE, FALSE) AND
		if((max(goods.F46)='1' AND LOCATE('F46', group_concat(DISTINCT header.Code)) > 0), 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
		 LEFT JOIN OilGoodsAptitude goods ON sub.SubClassId=goods.ClassId
		 WHERE b.SupplierTypeCode='03'
		 GROUP BY a.Id, b.Id`
	sql += 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 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(pageIndex, itemsPerPage int64, orderby string,
	asc bool, entitiesPtr interface{}, having 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) 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.F10)='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.F11)='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.F36)='1' AND LOCATE('F36',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F36) is null) or (max(goods.F36) = ''), TRUE, FALSE) AND
			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.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
			 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 += having
	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(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
}