supplier_system/src/dashoo.cn/backend/api/business/oilsupplier/selectbusiness/selectservice.go

package selectbusiness

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

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

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

func (s *SelectService) GetMyPagingEntitiesWithOrderBytbl(supplierTableName, supplierCertTableName, OilInfoChangeName, OilSupplierCert2FileName string, pageIndex, itemsPerPage int64, orderby string, asc bool, entitiesPtr interface{}, where,having string) (total int64) {

	//获取总记录数
	/*sqlCount := `select count(*) from (`
	sqlCount += `select a.Id,a.SupplierName,b.AccessCardNo,max(c.OldSupplierName) OldSupplierName,b.SupplierTypeCode,`
	sqlCount += `a.LegalPerson,a.RegCapital,a.Mobile,b.AuditDate,b.ApplyTime,b.InFlag,a.ContactName,a.CommercialNo, `
	sqlCount += `a.DepositBank,a.HseTraining,a.CompanyType,a.SetupTime,a.Address,a.Province,a.City,a.Street, `
	sqlCount += `a.LinkAddress,a.LinkProvince,a.LinkCity,a.LinkStreet,a.BusinessScope, `
	sqlCount += `group_concat(distinct d.Name) CerSubName,group_concat(distinct d.NeedFileType) NeedFileType `
	sqlCount += `from ` + supplierTableName + ` a `
	sqlCount += `left join ` + supplierCertTableName + ` b on b.SupplierId = a.Id `
	sqlCount += `left join ` + OilInfoChangeName + ` c  on c.SupplierId = a.Id `
	sqlCount += `left join ` + OilSupplierCert2FileName + ` d  on d.SupplierId = b.Id `
	sqlCount += `where ` + where
	sqlCount += `group by a.Id,b.Id `+having+`) f`*/

	var sql string
	sql = `select SQL_CALC_FOUND_ROWS a.Id,a.SupplierName,b.AccessCardNo,max(c.OldSupplierName) OldSupplierName,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,`
	sql += `group_concat(distinct d.Name) CerSubName,group_concat(distinct d.NeedFileType) NeedFileType `
	sql += `from ` + supplierTableName + ` a `
	sql += `left join ` + supplierCertTableName + ` b on b.SupplierId = a.Id `
	sql += `left join ` + OilInfoChangeName + ` c  on c.SupplierId = a.Id `
	sql += `left join ` + OilSupplierCert2FileName + ` d  on d.SupplierId = 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 *SelectService) GetUp( supplierCertTableName string, pageIndex, itemsPerPage int64, orderby string, asc bool, entitiesPtr interface{}, where string) (total int64) {
	var resultsSlice []map[string][]byte


	var sql string
	sql = `  select (select FullName from Base_Organize where Id = a.CommitComId) FullName,max(a.CommitComId) CommitComId,a.SupplierTypeName,`
	sql +=`  count(a.Status=6 or null ) HeGe,count(a.Status=-5 or null ) BuHeGe,count(a.Status=-5 or null ) + count(a.Status=6 or null ) ZongShu `
	sql += ` from ` + supplierCertTableName + ` a `
	sql += ` where a.CommitComId is not null and  a.CommitComId!="" and  a.CommitComId!=0 ` + where
	sql += ` group by FullName,SupplierTypeName `

	if asc {
		sql += ` order by ` + orderby + ` ASC `
	} else {
		sql += ` order by ` + orderby + ` DESC `
	}
	//获取总记录数
	sqlCount := `select count(*) from (` + sql+ `) a `

	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
}