supplier_system/src/dashoo.cn/backend/api/business/oilcontract/contract/contractService.go

package contract

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

type OilContractService struct {
	MyServiceBase
}

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

func (s *OilContractService) GetHandOnStatisticsList (queryParam HandOnStatisticsQuery, entitiesPtr interface{}) (total int64) {
	if queryParam.PageIndex < 1 { queryParam.PageIndex = 1 }
	if queryParam.ItemsPerPage < 1 { queryParam.ItemsPerPage = 10 }
	// var resultsSlice []map[string][]byte
	/*sqlCount := "SELECT COUNT(*) " +
		         " FROM OilContract a " +
		         " LEFT JOIN OilContractReview b ON b.ContractId=a.Id " +
		         " GROUP BY a.SecondUnit "*/

	sql := "SELECT c.FullName as SecondUnit, COUNT(DISTINCT a.Id) AS ContractNum,  COUNT(DISTINCT b.ContractId) AS HasNum " +
		" FROM OilContract a " +
		" LEFT JOIN OilContractReview b ON a.Id = b.ContractId " +
		" LEFT JOIN Base_Organize c on SecondUnit = c.Id"
	sql += " WHERE a.ImportStatus > 0 and a.Status < 3 "
	if queryParam.YearNum > 0 {
		sql += " AND " + strconv.Itoa(queryParam.YearNum) + " BETWEEN YEAR(a.StartDate) AND YEAR(a.EndDate)"
	}
	if queryParam.SupplierType != "" {
		sql += " AND a.ContractClass='" + queryParam.SupplierType + "'"
	}

	sql += " GROUP BY a.SecondUnit "
	if queryParam.SecondUnit != "" {
		sql += " HAVING c.FullName like '%" + queryParam.SecondUnit + "%'"
	}

	if queryParam.Order != "" {
		sql += " ORDER BY " + queryParam.Order  + " " + queryParam.Asc
	}

	startLimit := int((queryParam.PageIndex-1) * queryParam.ItemsPerPage)
	endLimit := int((queryParam.PageIndex) * queryParam.ItemsPerPage)
	var totalCnt int64
	totalCnt, _ = s.DBE.SQL(sql).Limit(startLimit, endLimit).FindAndCount(entitiesPtr)

	return totalCnt

}

func (s *OilContractService) GetSum(entitiesPtr interface{}, where string) {

	sql := ` select sum(Money) as Money from OilContractMoney`
	sql += ` where ` + where

	s.DBE.SQL(sql).Get(entitiesPtr)
}