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, " +
		" COUNT(DISTINCT IF(b.Status = 8, b.ContractId, null)) AS FinishNum, " +
		" COUNT(DISTINCT IF(b.Status > 2, b.ContractId, null)) AS ReportedNum, " +
		" (COUNT(DISTINCT a.Id) - COUNT(DISTINCT IF(b.Status > 2, b.ContractId, NULL))) AS UnReportNum " +
		" 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 = 2 "
	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.Prop + " " + queryParam.Order
	}

	startLimit := 0
	endLimit := 10000
	var totalCnt int64
	totalCnt, _ = s.DBE.SQL(sql).Limit(startLimit, endLimit).FindAndCount(entitiesPtr)

	return totalCnt

}

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

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

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

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

	sql := ` select Year,Money from OilContractMoney`
	sql += ` where ` + where

	s.DBE.SQL(sql).Find(entitiesPtr)
	return
}

func (s *OilContractService) GetJoinBySelect1(where string, entityPtr interface{}) {
	sql := "select a.Id from OilSupplier a left join OilSupplierCert  b ON a.Id = b.SupplierId "
	if where != "" {
		sql = sql + " Where " + where
	}
	s.DBE.SQL(sql).Get(entityPtr)
	return
}