supplier_system/src/dashoo.cn/backend/api/business/oilsupplier/technologyservice/oiltechnologyserviceService.go

package technologyservice

import (
	"strconv"

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

type OilTechnologyServiceService struct {
	MyServiceBase
}

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

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

func (s *OilTechnologyServiceService) TruncateTable(tableName string) error {
	sql := "TRUNCATE TABLE " + tableName
	_,err := s.DBE.Query(sql)
	return err
}

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

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

	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 *OilTechnologyServiceService) GetNoSubCntTotal(where string) (total int64) {

	sqlCount := "select count(a.`Name`) as total from OilSupplierCertSub a left join Tmp_OilTechnologyServiceClass b on b.`Name` = a.`Name` LEFT JOIN Tmp_OilTechnologyService c ON c.ClassId = b.Id where (" + where + ")"

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

	return total
}

func (s *OilTechnologyServiceService) 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 *OilTechnologyServiceService) GetTechList(techclass, where string) []TechnologyBusiness {
	if where == "" {
		where = " 1=1 "
	}
	var sql string
	sql = `select Id, Code, Name, concat(Code, '  ', Name) as CodeName, ParentId, Remark, DeletionStateCode  
	from ` + techclass + ` where ` + where + `  order by Code asc `
	List := make([]TechnologyBusiness, 0)
	utils.DBE.Sql(sql).Find(&List)
	return List
}

func (s *OilTechnologyServiceService) GetOilTechnologyF(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,F47,F48,F49,F50,F51,F52 from OilTechnologyService "
	if where != "" {
		sql = sql + " where " + where
	}
	err := s.DBE.Sql(sql).Find(entity)

	return err
}

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

func (s *OilTechnologyServiceService) 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.F47 AS tmpF47,tmpa.F48 AS tmpF48,
  		 tmpa.F49 AS tmpF49,tmpa.F50 AS tmpF50,tmpa.F51 AS tmpF51,tmpa.F52 AS tmpF52,tmpb.Name AS tmpName,
		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))) or ((tmpa.F47 is null && a.F47 = 1) or (tmpa.F47 = 1 && (a.F47 is null or a.F47 = 0))) or ((tmpa.F48 is null && a.F48 = 1) or (tmpa.F48 = 1 && (a.F48 is null or a.F48 = 0))) or ((tmpa.F49 is null && a.F49 = 1) or (tmpa.F49 = 1 && (a.F49 is null or a.F49 = 0))) or ((tmpa.F50 is null && a.F50 = 1) or (tmpa.F50 = 1 && (a.F50 is null or a.F50 = 0)))
			or ((tmpa.F51 is null && a.F51 = 1) or (tmpa.F51 = 1 && (a.F51 is null or a.F51 = 0))) or ((tmpa.F52 is null && a.F52 = 1) or (tmpa.F52 = 1 && (a.F52 is null or a.F52 = 0))) 
		,true, false) as checked`

	if Conditions == "5" {
		sql += " FROM Tmp_OilTechnologyService a LEFT JOIN Tmp_OilTechnologyServiceClass b ON b.Id = a.ClassId LEFT JOIN OilTechnologyServiceClassClass tmpb ON tmpb.Name = b.Name LEFT JOIN OilTechnologyServiceClass tmpa ON tmpa.ClassId = tmpb.Id where (" + having + ")"
	} else {
		sql += " FROM OilTechnologyService a LEFT JOIN OilTechnologyServiceClass b ON b.Id = a.ClassId LEFT JOIN Tmp_OilTechnologyServiceClass tmpb ON tmpb.Name = b.Name LEFT JOIN Tmp_OilTechnologyService 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 OilTechnologyService a LEFT JOIN OilTechnologyServiceClass b ON b.Id = a.ClassId LEFT JOIN Tmp_OilTechnologyServiceClass tmpb ON tmpb.Name = b.Name LEFT JOIN Tmp_OilTechnologyService tmpa ON tmpa.ClassId = tmpb.Id where (" + having + ") "
	if Conditions == "5" {
		sqlCount = "SELECT count(*) FROM Tmp_OilTechnologyService a LEFT JOIN Tmp_OilTechnologyServiceClass b ON b.Id = a.ClassId LEFT JOIN OilTechnologyServiceClass tmpb ON tmpb.Name = b.Name LEFT JOIN OilTechnologyService 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 *OilTechnologyServiceService) 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,
		CONCAT(
		   if((max(goods.F01)='1' AND LOCATE('F01', group_concat(DISTINCT header.Code)) > 0) or (max(goods.F01) is null) or (max(goods.F01) = ''), '', '营业执照,')  , 
		   if((max(goods.F02)='1' AND LOCATE('F02',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F02) is null) or (max(goods.F02) = ''), '', '企业信用信息截屏,') ,
		   if((max(goods.F03)='1' AND LOCATE('F03',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F03) is null) or (max(goods.F03) = ''), '', '诚信合规承诺书,') ,
		   if((max(goods.F04)='1' AND LOCATE('F04',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F04) is null) or (max(goods.F04) = ''), '', '银行开户许可证或基本存款账户信息,') ,
		   if((max(goods.F05)='1' AND LOCATE('F05',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F05) is null) or (max(goods.F05) = ''), '', '质量管理体系认证证书,') ,
		   if((max(goods.F06)='1' AND LOCATE('F06',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F06) is null) or (max(goods.F06) = ''), '', '环境管理体系认证证书,') ,
		   if((max(goods.F07)='1' AND LOCATE('F07',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F07) is null) or (max(goods.F07) = ''), '', '职业健康安全管理体系认证证书,') ,
		   if((max(goods.F08)='1' AND LOCATE('F08',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F08) is null) or (max(goods.F08) = ''), '', '排污许可证,') ,
		   if((max(goods.F09)='1' AND LOCATE('F09',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F09) is null) or (max(goods.F09) = ''), '', '安全生产许可证,') ,
		   if((max(goods.F10)='1' AND LOCATE('F10',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F10) is null) or (max(goods.F10) = ''), '', '危险废弃物经营许可证（危险废弃物处置）,') ,
		   if((max(goods.F11)='1' AND LOCATE('F11',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F11) is null) or (max(goods.F11) = ''), '', '海洋石油作业安全生产许可证,') ,
		   if((max(goods.F12)='1' AND LOCATE('F12',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F12) is null) or (max(goods.F12) = ''), '', '辐射安全许可证,') ,
		   if((max(goods.F13)='1' AND LOCATE('F13',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F13) is null) or (max(goods.F13) = ''), '', '石油工程技术服务企业资质证书,') ,
		   if((max(goods.F14)='1' AND LOCATE('F14',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F14) is null) or (max(goods.F14) = ''), '', '检验检测机构资质认定证书（气体检测）,') ,
		   if((max(goods.F15)='1' AND LOCATE('F15',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F15) is null) or (max(goods.F15) = ''), '', '工业清洗企业资质证书,') ,
		   if((max(goods.F16)='1' AND LOCATE('F16',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F16) is null) or (max(goods.F16) = ''), '', '建设项目环境影响评价资格证书,') ,
		   if((max(goods.F17)='1' AND LOCATE('F17',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F17) is null) or (max(goods.F17) = ''), '', '消防设施维护保养资质证书,') ,
		   if((max(goods.F18)='1' AND LOCATE('F18',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F18) is null) or (max(goods.F18) = ''), '', '安全评价机构资质证书,') ,
		   if((max(goods.F19)='1' AND LOCATE('F19',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F19) is null) or (max(goods.F19) = ''), '', '工程造价咨询企业甲级资质证书,') ,
		   if((max(goods.F20)='1' AND LOCATE('F20',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F20) is null) or (max(goods.F20) = ''), '', '工程监督资质证书,') ,
		   if((max(goods.F21)='1' AND LOCATE('F21',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F21) is null) or (max(goods.F21) = ''), '', '计量标准考核证书,') ,
		   if((max(goods.F22)='1' AND LOCATE('F22',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F22) is null) or (max(goods.F22) = ''), '', '房屋预售资格证,') ,
		   if((max(goods.F23)='1' AND LOCATE('F23',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F23) is null) or (max(goods.F23) = ''), '', '保安服务许可证,') ,
		   if((max(goods.F24)='1' AND LOCATE('F24',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F24) is null) or (max(goods.F24) = ''), '', '道路运输经营许可证,') ,
		   if((max(goods.F25)='1' AND LOCATE('F25',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F25) is null) or (max(goods.F25) = ''), '', '机动车维修经营许可证,') ,
		   if((max(goods.F26)='1' AND LOCATE('F26',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F26) is null) or (max(goods.F26) = ''), '', '道路危险货物运输许可证,') ,
		   if((max(goods.F27)='1' AND LOCATE('F27',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F27) is null) or (max(goods.F27) = ''), '', '特种设备安装改造维修许可证,') ,
		   if((max(goods.F28)='1' AND LOCATE('F28',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F28) is null) or (max(goods.F28) = ''), '', '消防设施检测资质证书,') ,
		   if((max(goods.F29)='1' AND LOCATE('F29',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F29) is null) or (max(goods.F29) = ''), '', '消防安全评估资质,') ,
		   if((max(goods.F30)='1' AND LOCATE('F30',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F30) is null) or (max(goods.F30) = ''), '', '印刷经营许可证,') ,
		   if((max(goods.F31)='1' AND LOCATE('F31',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F31) is null) or (max(goods.F31) = ''), '', '餐饮服务许可证,') ,
		   if((max(goods.F32)='1' AND LOCATE('F32',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F32) is null) or (max(goods.F32) = ''), '', '劳务派遣经营许可证,') ,
		   if((max(goods.F33)='1' AND LOCATE('F33',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F33) is null) or (max(goods.F33) = ''), '', '人力资源服务许可证,') ,
		   if((max(goods.F34)='1' AND LOCATE('F34',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F34) is null) or (max(goods.F34) = ''), '', '国家实验室认可证书,') ,
		   if((max(goods.F35)='1' AND LOCATE('F35',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F35) is null) or (max(goods.F35) = ''), '', '海洋石油专业设备检验检测机构证书,') ,
		   if((max(goods.F36)='1' AND LOCATE('F36',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F36) is null) or (max(goods.F36) = ''), '', '安全生产检验检测证书,') ,
		   if((max(goods.F37)='1' AND LOCATE('F37',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F37) is null) or (max(goods.F37) = ''), '', '液化气钢瓶检验证书,') ,
		   if((max(goods.F38)='1' AND LOCATE('F38',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F38) is null) or (max(goods.F38) = ''), '', '国家电网承试承装承维修许可证,') ,
		   if((max(goods.F39)='1' AND LOCATE('F39',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F39) is null) or (max(goods.F39) = ''), '', '建筑企业资质证书,') ,
		   if((max(goods.F40)='1' AND LOCATE('F40',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F40) is null) or (max(goods.F40) = ''), '', '中油集团监造许可,') ,
		   if((max(goods.F43)='1' AND LOCATE('F43',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F43) is null) or (max(goods.F43) = ''), '', '物业服务企业资质证书,') ,
		   if((max(goods.F44)='1' AND LOCATE('F44',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F44) is null) or (max(goods.F44) = ''), '', '全国投资项目在线审批监管平台备案信息,') ,
		   if((max(goods.F45)='1' AND LOCATE('F45',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F45) is null) or (max(goods.F45) = ''), '', '') ,
		   if((max(goods.F46)='1' AND LOCATE('F46',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F46) is null) or (max(goods.F46) = ''), '', '') ,
           if((max(goods.F47)='1' AND LOCATE('F47',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F47) is null) or (max(goods.F47) = ''), '', '') ,
		   if((max(goods.F48)='1' AND LOCATE('F48',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F48) is null) or (max(goods.F48) = ''), '', '') ,
		   if((max(goods.F49)='1' AND LOCATE('F49',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F49) is null) or (max(goods.F49) = ''), '', '') ,
		   if((max(goods.F50)='1' AND LOCATE('F50',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F50) is null) or (max(goods.F50) = ''), '', '') ,
		   if((max(goods.F51)='1' AND LOCATE('F51',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F51) is null) or (max(goods.F51) = ''), '', '') ,
		   if((max(goods.F52)='1' AND LOCATE('F52',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F52) is null) or (max(goods.F52) = ''), '', '') 
		   ) AS ZzName,
			(
			((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.F41)='1' AND LOCATE('F41',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F41) is null) or (max(goods.F41) = ''), TRUE, FALSE)) or
			(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.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.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.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) AND
			if((max(goods.F47)='1' AND LOCATE('F47',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F47) is null) or (max(goods.F47) = ''), TRUE, FALSE) AND
			if((max(goods.F48)='1' AND LOCATE('F48',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F48) is null) or (max(goods.F48) = ''), TRUE, FALSE) AND
			if((max(goods.F49)='1' AND LOCATE('F49',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F49) is null) or (max(goods.F49) = ''), TRUE, FALSE) AND
			if((max(goods.F50)='1' AND LOCATE('F50',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F50) is null) or (max(goods.F50) = ''), TRUE, FALSE) AND
			if((max(goods.F51)='1' AND LOCATE('F51',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F51) is null) or (max(goods.F51) = ''), TRUE, FALSE) AND
			if((max(goods.F52)='1' AND LOCATE('F52',  group_concat(DISTINCT header.Code)) > 0) or (max(goods.F52) is null) or (max(goods.F52) = ''), 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 = '03'
			 LEFT JOIN Tmp_OilTechnologyServiceClass class ON sub.Name=class.Name
			 LEFT JOIN Tmp_OilTechnologyService 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 *OilTechnologyServiceService) 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_OilTechnologyServiceClass class ON a.Name=class.Name
		 LEFT JOIN Tmp_OilTechnologyService 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_OilTechnologyServiceClass class ON a.Name=class.Name LEFT JOIN Tmp_OilTechnologyService 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 *OilTechnologyServiceService) DeleteOilSupplierCertSub(where string) error {
	sql := "DELETE FROM OilSupplierCertSub where " + where + " and Id NOT IN (SELECT dt.mid FROM (SELECT MIN(Id) AS mid FROM OilSupplierCertSub where " + where + " GROUP BY SupplierCertId) dt)"
	_, err := s.DBE.Query(sql)
	return err
}