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, OilCorporateInfoName, OilSupplierCertSubName,
	OilSupplierFileName string, pageIndex, itemsPerPage int64, orderby string, asc bool, entitiesPtr interface{}, where,having, liftjoon 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.EffectEndTime,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, f.FullName, g.CheckUnitName, b.Remark, `
	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 += `left join  Base_Organize f on f.Id = b.ThirdAudit `
	sql += `left join  OilCorporateInfo g on g.CommercialNo = a.CommercialNo `
	sql += `left join ` + OilInfoChangeName + ` c  on c.SupplierId = a.Id `
	sql += liftjoon
	sql += `left join ` + OilSupplierFileName + ` e  on e.SupplierId = a.Id `
	sql += `where ` + where

	sql += ` group by a.Id,b.Id,g.CheckUnitName ` + 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,GROUP_CONCAT(a.BackReason SEPARATOR ';') Reason  `
	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
}
func (s *SelectService) GetTotalInMonth( orderby string, asc bool, entitiesTotal interface{}, where string) (total int64) {
	var resultsSlice []map[string][]byte
	endtime, _ := strconv.Atoi(where)
	if endtime == 0 {
		endtime = 5000
	}else {
	endtime = endtime+1}
	timedone := strconv.Itoa(endtime)
	var sql string
	sql = `(SELECT AnnualAudit.Month,AnnualAudit,Store,Append,InfoChange,CatalogSelf,CatalogWin,CatalogTrade,CatalogComp,CatalogSpe FROM(
SELECT  b.Month,a.AnnualAudit from (select extract(month from ApplyTime) time ,COUNT(*) AnnualAudit
			from OilAnnualAudit
			WHERE ApplyTime >= '` + where +`' AND ApplyTime < '` + timedone +`' AND Status = '11'
			group by extract(month from ApplyTime)) a 
			RIGHT JOIN OilCountMonth b ON b.Month = a.time) AnnualAudit
LEFT JOIN (SELECT  b.Month,a.Store from (select extract(month from StorageOn) time ,COUNT(*) Store
			from OilSupplierCert
			WHERE StorageOn >= '` + where +`' AND StorageOn < '` + timedone +`' AND InFlag = '1'
			group by extract(month from StorageOn)) a 
			RIGHT JOIN OilCountMonth b ON b.Month = a.time) Store ON AnnualAudit.Month = Store.Month
LEFT JOIN (SELECT  b.Month,a.Append from (select extract(month from ApplyDate) time ,COUNT(*) Append
			from OilSupplierCertAppend
			WHERE ApplyDate >= '` + where +`' AND ApplyDate < '` + timedone +`' AND Status = '11'
			group by extract(month from ApplyDate)) a 
			RIGHT JOIN OilCountMonth b ON b.Month = a.time) Append ON AnnualAudit.Month = Append.Month
LEFT JOIN (SELECT  b.Month,a.InfoChange from (select extract(month from CreateOn) time ,COUNT(*) InfoChange
			from OilInfoChange
			WHERE CreateOn >= '` + where +`' AND CreateOn < '` + timedone +`' AND Status = '11'
			group by extract(month from CreateOn)) a 
			RIGHT JOIN OilCountMonth b ON b.Month = a.time) InfoChange ON AnnualAudit.Month = InfoChange.Month
LEFT JOIN (SELECT  b.Month,a.CatalogSelf from (select extract(month from CreateOn) time ,COUNT(*) CatalogSelf
			from OilCatalog
			WHERE CreateOn >= '` + where +`' AND CreateOn < '` + timedone +`' AND CatalogType=1 
			group by extract(month from CreateOn)) a 
			RIGHT JOIN OilCountMonth b ON b.Month = a.time) CatalogSelf ON AnnualAudit.Month = CatalogSelf.Month
LEFT JOIN (SELECT  b.Month,a.CatalogWin from (select extract(month from CreateOn) time ,COUNT(*) CatalogWin
			from OilCatalog
			WHERE CreateOn >= '` + where +`' AND CreateOn < '` + timedone +`' AND CatalogType=2 
			group by extract(month from CreateOn)) a 
			RIGHT JOIN OilCountMonth b ON b.Month = a.time) CatalogWin ON AnnualAudit.Month = CatalogWin.Month
LEFT JOIN (SELECT  b.Month,a.CatalogTrade from (select extract(month from CreateOn) time ,COUNT(*) CatalogTrade
			from OilCatalog
			WHERE CreateOn >= '` + where +`' AND CreateOn < '` + timedone +`' AND CatalogType=3 
			group by extract(month from CreateOn)) a 
			RIGHT JOIN OilCountMonth b ON b.Month = a.time) CatalogTrade ON AnnualAudit.Month = CatalogTrade.Month
LEFT JOIN (SELECT  b.Month,a.CatalogComp from (select extract(month from CreateOn) time ,COUNT(*) CatalogComp
			from OilCatalog
			WHERE CreateOn >= '` + where +`' AND CreateOn < '` + timedone +`' AND CatalogType=4 
			group by extract(month from CreateOn)) a 
			RIGHT JOIN OilCountMonth b ON b.Month = a.time) CatalogComp ON AnnualAudit.Month = CatalogComp.Month
LEFT JOIN (SELECT  b.Month,a.CatalogSpe from (select extract(month from CreateOn) time ,COUNT(*) CatalogSpe
			from OilCatalog
			WHERE CreateOn >= '` + where +`' AND CreateOn < '` + timedone +`' AND CatalogType=5 
			group by extract(month from CreateOn)) a 
			RIGHT JOIN OilCountMonth b ON b.Month = a.time) CatalogSpe ON AnnualAudit.Month = CatalogSpe.Month ORDER BY AnnualAudit.Month) `

	//if asc {
	//	sql += ` order by ` + orderby + ` ASC `
	//} else {
	//	sql += ` order by ` + orderby + ` DESC `
	//}
	s.DBE.SQL(sql).Find(entitiesTotal)
	if len(resultsSlice) > 0 {
		results := resultsSlice[0]
		for _, value := range results {
			total, _ = strconv.ParseInt(string(value), 10, 64)
			break
		}
	}

	return total
}

//获取公司信息和准入范围

func (s *SelectService) GetInfoByCompId(where string,entitiesPtr interface{}) (total int64) {
	var sql string
	sql = `SELECT a.* from OilSupplier a `+ where

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

	return total
}

func (s *SelectService) Getoilsuppliercertsub( tableName, where string, entitiesPtr interface{}) error {
	var sql string
	sql = `SELECT a.* from `+ tableName +` a `+ where

	err := s.DBE.SQL(sql).Find(entitiesPtr)
	return err
}