116 lines
3.4 KiB
Go
116 lines
3.4 KiB
Go
package sql_builder
|
|
|
|
import (
|
|
"fmt"
|
|
"testing"
|
|
"time"
|
|
|
|
_ "git.botann.com/lijun/sql-builder/conf"
|
|
)
|
|
|
|
type Page struct {
|
|
Total int `json:"total" form:"total"`
|
|
PageIndex int `json:"pageIndex" form:"pageIndex"`
|
|
PageSize int `json:"pageSize" form:"pageSize"`
|
|
Data interface{} `json:"data" form:"data"`
|
|
}
|
|
|
|
func (p *Page) Init() {
|
|
if p.PageIndex == 0 {
|
|
p.SetPageIndex(1)
|
|
}
|
|
if p.PageSize == 0 {
|
|
p.SetPageSize(10)
|
|
}
|
|
}
|
|
|
|
func (p *Page) GetPageIndex() int {
|
|
return p.PageIndex
|
|
}
|
|
|
|
func (p *Page) GetPageSize() int {
|
|
return p.PageSize
|
|
}
|
|
|
|
func (p *Page) GetTotal() int {
|
|
return p.Total
|
|
}
|
|
|
|
func (p *Page) SetTotal(total int) {
|
|
p.Total = total
|
|
}
|
|
|
|
func (p *Page) SetPageIndex(pageIndex int) {
|
|
p.PageIndex = pageIndex
|
|
}
|
|
|
|
func (p *Page) SetPageSize(pageSize int) {
|
|
p.PageSize = pageSize
|
|
}
|
|
|
|
func TestOracle(t *testing.T) {
|
|
var err error
|
|
type Area struct {
|
|
Autoseq int `json:"autoseq"`
|
|
Name string `json:"name"`
|
|
Id string `json:"id"`
|
|
AreaDn string `json:"area_dn"`
|
|
Rn int `json:"rn"`
|
|
}
|
|
var data []Area
|
|
var ids = []int{86986, 86987}
|
|
sqlb := OracleSqlBuilder()
|
|
sqls := `select autoseq,name,id,area_dn from tareas where del = 0 @c1 @c2`
|
|
sqlb.Sql = sqls
|
|
sqlb.InsertConditions("@c1", "area_dn like ?", "level1=1,level2=85226,level3=86918,level4=86919,level5=86986%")
|
|
sqlb.InsertConditions("@c2", "autoseq in (?)", ids)
|
|
page := Page{PageIndex: 1, PageSize: 10}
|
|
// err = sqlb.FindBySql(&data)
|
|
err = sqlb.PaginateBySql(&data, &page)
|
|
fmt.Println(data)
|
|
page.Data = &data
|
|
fmt.Println(err, page)
|
|
}
|
|
|
|
func TestMysql(t *testing.T) {
|
|
var err error
|
|
type PreliminaryProcedureResponse struct {
|
|
ProjectId int `json:"project_id"`
|
|
InitiationTime time.Time `json:"initiation_time" export:"x:1,y:2"`
|
|
ProjectNo string `json:"project_no" export:"x:2,y:2"`
|
|
ProjectName string `json:"project_name" export:"x:3,y:2"`
|
|
ProjectUnit string `json:"project_unit" export:"x:4,y:2"`
|
|
ProjectType string `json:"project_type" export:"x:5,y:2"`
|
|
Allnum int `json:"all_num" export:"x:6,y:2"`
|
|
NotHandleNum int `json:"not_handle_num" export:"x:7,y:2"`
|
|
NoHandleNum int `json:"no_handle_num" export:"x:8,y:2"`
|
|
HandlingNum int `json:"handling_num" export:"x:9,y:2"`
|
|
HandledNum int `json:"handled_num" export:"x:10,y:2"`
|
|
}
|
|
var data []PreliminaryProcedureResponse
|
|
company_id := 1
|
|
sqlb := DefaultSqlBuilder()
|
|
sqls := `select t1.autoseq,max(t1.establishment_time),max(t1.number),max(t1.name),max(t2.name) as project_unit,max(t3.name) as project_type,
|
|
count(distinct t6.autoseq) all_num,
|
|
count(distinct case t6.state when 1 then t6.autoseq end) not_handle_num,
|
|
count(distinct case t6.state when 2 then t6.autoseq end) no_handle_num,
|
|
count(distinct case t6.state when 3 then t6.autoseq end) handling_num,
|
|
count(distinct case t6.state when 4 then t6.autoseq end) handled_num
|
|
from tproject_info t1
|
|
inner join tcompany t2 on t1.investor_id = t2.autoseq and t2.del = 0 @c
|
|
left join tinvestment_type t3 on t1.industry_category_id = t3.autoseq and t3.del = 0
|
|
left join tpreliminary_procedures t6 on t1.autoseq = t6.project_id
|
|
where t1.del = 0 @c @c @c @c @c1
|
|
@g`
|
|
sqlb.SetSql(sqls)
|
|
if company_id != 0 {
|
|
sqlb.InsertConditions("@c1", "t2.autoseq = ?", company_id)
|
|
}
|
|
sqlb.Groups("group by t1.autoseq")
|
|
page := Page{PageIndex: 1, PageSize: 10}
|
|
err = sqlb.PaginateBySql(&data, &page)
|
|
page.Data = &data
|
|
fmt.Println(err, page.Data)
|
|
|
|
}
|