supplier_system/src/dashoo.cn/backend/api/business/oilsupplier/paymentselect/paymentselectService.go

package paymentselect

import (
	. "dashoo.cn/utils/db"
	"github.com/go-xorm/xorm"
	"strconv"
)

type PaymentSelectService struct {
	ServiceBase
}

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

func (s *PaymentSelectService) GetPaymentselectList(pageIndex, itemsPerPage int64, order string, asc string, entitiesPtr interface{}, where string) (total int64) {
	var resultsSlice []map[string][]byte
	sqlconunt := "SELECT COUNT(p.Id) FROM OilPaymentInfo p " +
		"LEFT JOIN OilSupplier s ON p.SupplierId = s.Id "+
		"LEFT JOIN OilSupplierCert c ON p.SupplierCertId = c.Id " +
		"WHERE " + where

	sql := "SELECT c.AccessCardNo,s.SupplierName,c.SupplierTypeName,p.PayDate, " +
		"sum(case  when p.PayType = '1' then Amount   else 0 end) as Access, " +
		"sum(case  when p.PayType = '2' then Amount   else 0 end) as Annual, " +
		"sum(case  when p.PayType = '3' then Amount   else 0 end) as Addition, " +
		"sum(p.Amount) as Sums, " +
		"min(p.Remark) as Remarks " +
		"FROM OilPaymentInfo p " +
		"LEFT JOIN OilSupplier s ON p.SupplierId = s.Id "+
		"LEFT JOIN OilSupplierCert c ON p.SupplierCertId = c.Id " +
		"group by c.AccessCardNo,s.SupplierName,c.SupplierTypeName,p.PayDate,s.CommercialNo " +
		"having " + where +
		" Order By " + order + asc + " Limit " + strconv.Itoa(int(itemsPerPage)) +" OFFSET " + strconv.Itoa((int(pageIndex)-1)*int(itemsPerPage))


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

	resultsSlice, _ = s.DBE.Query(sqlconunt)

	if len(resultsSlice) > 0 {
		results := resultsSlice[0]
		for _, value := range results {
			total, _ = strconv.ParseInt(string(value), 10, 64)
			break
		}
	}
	return total

}
//以上除查询功能外已经稳定，别碰！！

func (s *PaymentSelectService) GetPaymentselectSumList( order string, asc string, entitiesPtr interface{}, where string) {

	sql := "SELECT c.SupplierTypeName, " +
		"sum(case  when p.PayType = '1' then Amount   else 0 end) as Access, " +
		"sum(case  when p.PayType = '2' then Amount   else 0 end) as Annual, " +
		"sum(case  when p.PayType = '3' then Amount   else 0 end) as Addition, " +
		"sum(p.Amount) as Sums " +
		"FROM OilPaymentInfo p " +
		"LEFT JOIN OilSupplier s ON p.SupplierId = s.Id "+
		"LEFT JOIN OilSupplierCert c ON p.SupplierCertId = c.Id " + where +
		"group by c.SupplierTypeName " +
		" Order By " + order + asc

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