You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
2217 lines
75 KiB
2217 lines
75 KiB
package models
|
|
|
|
import (
|
|
"encoding/json"
|
|
"errors"
|
|
"fmt"
|
|
"github.com/go-xorm/xorm"
|
|
uuid "github.com/iris-contrib/go.uuid"
|
|
"github.com/tealeg/xlsx"
|
|
"lapp_-wy/db"
|
|
"lapp_-wy/utils"
|
|
"lapp_-wy/web/middleware/glog"
|
|
"lapp_-wy/web/models/response"
|
|
"math"
|
|
"strconv"
|
|
"strings"
|
|
"time"
|
|
"xorm.io/core"
|
|
)
|
|
|
|
type Chargetab struct {
|
|
Cid int `json:"cid" xorm:"not null pk INT(4)"`
|
|
Chargenr int `json:"chargenr" xorm:"not null pk INT(4)"`
|
|
Chargetype int `json:"chargetype" xorm:"INT(4)"`
|
|
Status int `json:"status" xorm:"INT(4)"`
|
|
Propertyid string `json:"propertyid" xorm:"VARCHAR(100)"`
|
|
Accesscardid string `json:"accesscardid" xorm:"VARCHAR(100)"`
|
|
Carportid string `json:"carportid" xorm:"VARCHAR(100)"`
|
|
Chargestartdate string `json:"chargestartdate" xorm:"VARCHAR(20)"`
|
|
Chargeenddate string `json:"chargeenddate" xorm:"VARCHAR(20)"`
|
|
Chargeway string `json:"chargeway" xorm:"VARCHAR(100)"`
|
|
Chargableexpense float64 `json:"chargableexpense" xorm:"DECIMAL(10,2)"`
|
|
Chargedexpense float64 `json:"chargedexpense" xorm:"DECIMAL(10,2)"`
|
|
Info string `json:"info" xorm:"VARCHAR(255)"`
|
|
Chargetime string `json:"chargetime" xorm:"VARCHAR(20)"`
|
|
Chargeby string `json:"chargeby" xorm:"VARCHAR(40)"`
|
|
Serialnumber string `json:"serialnumber" xorm:"VARCHAR(32)"`
|
|
Createtime string `json:"createtime" xorm:"VARCHAR(14)"`
|
|
Lastmodifytime string `json:"lastmodifytime" xorm:"VARCHAR(20)"`
|
|
Lastmodifyby string `json:"lastmodifyby" xorm:"VARCHAR(20)"`
|
|
}
|
|
|
|
type ChargeArrearage struct {
|
|
*Chargetab
|
|
Arrearage int `json:"arrearage"`
|
|
ArrearageDate string `json:"arrearage_date"`
|
|
}
|
|
|
|
type ChargeContactData struct {
|
|
Chargetab `xorm:"extends"`
|
|
Propertytab `xorm:"extends"`
|
|
}
|
|
|
|
type ChargeArrearageExcelData struct {
|
|
*ChargeContactData
|
|
Arrearage int `json:"arrearage"`
|
|
ArrearageDate string `json:"arrearage_date"`
|
|
ChargeTypeStr string `json:"charge_type_str"`
|
|
}
|
|
|
|
type ChargeTypeResponse struct {
|
|
Cid int `json:"cid"`
|
|
Court string `json:"court"`
|
|
ChargeWay string `json:"charge_way"`
|
|
ChargeCount int `json:"charge_count"`
|
|
Items []*Chargetab `json:"items"`
|
|
Total int64 `json:"total"`
|
|
TotalPage int `json:"total_page"`
|
|
CurrentPage int `json:"current_page"`
|
|
}
|
|
|
|
type ChargeArrearageResponse struct {
|
|
Cid int `json:"cid"`
|
|
Court string `json:"court"`
|
|
Items []*ChargeArrearage `json:"items"`
|
|
Total int64 `json:"total"`
|
|
TotalPage int `json:"total_page"`
|
|
CurrentPage int `json:"current_page"`
|
|
User string `json:"user"`
|
|
}
|
|
|
|
type ExcelChargeWayData struct {
|
|
*ChargeContactData
|
|
ChargeTypeStr string `json:"charge_type_str"`
|
|
}
|
|
|
|
func (t *Chargetab) TableName() string {
|
|
return "chargetab"
|
|
}
|
|
|
|
// 清除string字段的右侧空格
|
|
func (t *Chargetab) Clipped() {
|
|
utils.TrimStruct(t, *t)
|
|
}
|
|
|
|
//查
|
|
func (t *Chargetab) SelectOne() (Chargetab, error) {
|
|
e := db.MasterEngine()
|
|
var data Chargetab
|
|
_, err := e.ID(core.PK{t.Cid, t.Chargenr}).Get(&data)
|
|
if err != nil {
|
|
return data, err
|
|
}
|
|
return data, nil
|
|
}
|
|
|
|
//分页
|
|
func (t *Chargetab) GetPage(pageSize int, pageIndex int, buildingid string, unit string, room string) ([]Chargetab, int, error) {
|
|
data := make([]Chargetab, 0)
|
|
e := db.MasterEngine()
|
|
query := e.Table("chargetab")
|
|
table := e.Table("chargetab")
|
|
switch t.Chargetype {
|
|
case 1:
|
|
//物业费
|
|
query = query.Join("LEFT", "propertytab", "propertytab.propertyid = chargetab.propertyid and propertytab.cid = chargetab.cid").Where("chargetab.cid = ?", t.Cid)
|
|
table = table.Join("LEFT", "propertytab", "propertytab.propertyid = chargetab.propertyid and propertytab.cid = chargetab.cid").Where("chargetab.cid = ?", t.Cid)
|
|
query = query.And("chargetab.chargetype = ?", 1)
|
|
table = table.And("chargetab.chargetype = ?", 1)
|
|
case 2:
|
|
//电梯费
|
|
query = query.Join("LEFT", "propertytab", "propertytab.propertyid = chargetab.propertyid and propertytab.cid = chargetab.cid").Join("LEFT", "accesscardtab", "accesscardtab.accesscardid = chargetab.accesscardid and accesscardtab.cid = chargetab.cid").Where("chargetab.cid = ?", t.Cid)
|
|
table = table.Join("LEFT", "propertytab", "propertytab.propertyid = chargetab.propertyid and propertytab.cid = chargetab.cid").Join("LEFT", "accesscardtab", "accesscardtab.accesscardid = chargetab.accesscardid and accesscardtab.cid = chargetab.cid").Where("chargetab.cid = ?", t.Cid)
|
|
if !utils.ValueIsEmpty(t.Accesscardid) {
|
|
query = query.And("accesscardtab.accesscardid = ?", t.Accesscardid)
|
|
table = table.And("accesscardtab.accesscardid = ?", t.Accesscardid)
|
|
}
|
|
query = query.And("chargetab.chargetype = ?", 2)
|
|
table = table.And("chargetab.chargetype = ?", 2)
|
|
case 3:
|
|
//车位费
|
|
query = query.Join("LEFT", "propertytab", "propertytab.propertyid = chargetab.propertyid and propertytab.cid = chargetab.cid").Join("LEFT", "carporttab", "carporttab.carportid = chargetab.carportid and carporttab.cid = chargetab.cid").Where("chargetab.cid = ?", t.Cid)
|
|
table = table.Join("LEFT", "propertytab", "propertytab.propertyid = chargetab.propertyid and propertytab.cid = chargetab.cid").Join("LEFT", "carporttab", "carporttab.carportid = chargetab.carportid and carporttab.cid = chargetab.cid").Where("chargetab.cid = ?", t.Cid)
|
|
if !utils.ValueIsEmpty(t.Accesscardid) {
|
|
query = query.And("carporttab.carportid = ?", t.Carportid)
|
|
table = table.And("carporttab.carportid = ?", t.Carportid)
|
|
}
|
|
query = query.And("chargetab.chargetype = ?", 3)
|
|
table = table.And("chargetab.chargetype = ?", 3)
|
|
case 4:
|
|
//其他费用
|
|
query = query.Where("chargetab.cid = ?", t.Cid)
|
|
table = table.Where("chargetab.cid = ?", t.Cid)
|
|
query = query.And("chargetab.chargetype = ?", 4)
|
|
table = table.And("chargetab.chargetype = ?", 4)
|
|
default:
|
|
//物业费
|
|
query = query.Join("LEFT", "propertytab", "propertytab.propertyid = chargetab.propertyid and propertytab.cid = chargetab.cid").Where("chargetab.cid = ?", t.Cid)
|
|
table = table.Join("LEFT", "propertytab", "propertytab.propertyid = chargetab.propertyid and propertytab.cid = chargetab.cid").Where("chargetab.cid = ?", t.Cid)
|
|
query = query.And("chargetab.chargetype = ?", 1)
|
|
table = table.And("chargetab.chargetype = ?", 1)
|
|
}
|
|
if t.Chargetype == 4 {
|
|
Offset := (pageIndex - 1) * pageSize
|
|
err := query.Limit(pageSize, Offset).Desc("chargetab.createtime").Find(&data)
|
|
if err != nil {
|
|
return data, 0, err
|
|
}
|
|
} else {
|
|
if !utils.ValueIsEmpty(t.Propertyid) {
|
|
query = query.And("propertytab.propertyid = ?", t.Propertyid)
|
|
table = table.And("propertytab.propertyid = ?", t.Propertyid)
|
|
}
|
|
if !utils.ValueIsEmpty(buildingid) {
|
|
query = query.And("propertytab.buildingid = ?", buildingid)
|
|
table = table.And("propertytab.buildingid = ?", buildingid)
|
|
}
|
|
if !utils.ValueIsEmpty(unit) {
|
|
query = query.And("propertytab.unit = ?", unit)
|
|
table = table.And("propertytab.unit = ?", unit)
|
|
}
|
|
if !utils.ValueIsEmpty(room) {
|
|
query = query.And("propertytab.room = ?", room)
|
|
table = table.And("propertytab.room = ?", room)
|
|
}
|
|
if !utils.ValueIsEmpty(t.Chargestartdate) {
|
|
query = query.And("chargetab.chargestartdate >= ?", t.Chargestartdate)
|
|
table = table.And("chargetab.chargestartdate >= ?", t.Chargestartdate)
|
|
}
|
|
if !utils.ValueIsEmpty(t.Chargeenddate) {
|
|
query = query.And("chargetab.chargeenddate <= ?", t.Chargeenddate)
|
|
table = table.And("chargetab.chargeenddate <= ?", t.Chargeenddate)
|
|
}
|
|
Offset := (pageIndex - 1) * pageSize
|
|
err := query.Limit(pageSize, Offset).Desc("chargetab.createtime", "propertytab.buildingid", "propertytab.unit", "propertytab.room").Find(&data)
|
|
if err != nil {
|
|
return data, 0, err
|
|
}
|
|
}
|
|
|
|
pcount := new(Chargetab)
|
|
count, err := table.Count(pcount)
|
|
if err != nil {
|
|
return data, 0, err
|
|
}
|
|
|
|
return data, int(count), nil
|
|
}
|
|
|
|
type ChargeByBuilding struct {
|
|
Buildingid string `json:"building"`
|
|
Money float64 `json:"money"`
|
|
Cid int `json:"cid"`
|
|
Rate string `json:"rate"`
|
|
}
|
|
|
|
//按楼号统计
|
|
func (t *Chargetab) ChargeTotalByBuilding(buildingid string, beginyear string, endyear string, settledate int) ([]ChargeByBuilding, int, string, error) {
|
|
e := db.MasterEngine()
|
|
if utils.ValueIsEmpty(beginyear) {
|
|
beginyear = utils.TimeFormat(time.Now(), "yyyy")
|
|
}
|
|
if utils.ValueIsEmpty(endyear) {
|
|
endyear = utils.TimeFormat(time.Now(), "yyyy")
|
|
}
|
|
lengh := utils.ValueToString(settledate, "")
|
|
startdate := ""
|
|
if len(lengh) > 1 {
|
|
startdate = beginyear + "-" + utils.ValueToString(settledate, "")
|
|
} else {
|
|
startdate = beginyear + "-0" + utils.ValueToString(settledate, "")
|
|
}
|
|
|
|
data := make([]ChargeByBuilding, 0)
|
|
query := e.Table("chargetab").Select("chargetab.cid as cid,propertytab.buildingid as buildingid,sum(chargeallocationtab.allocateexpense) as money").Join("INNER", "propertytab", "propertytab.propertyid = chargetab.propertyid and propertytab.cid = chargetab.cid").Join("INNER", "chargeallocationtab", "chargetab.chargenr = chargeallocationtab.chargenr and chargetab.cid = chargeallocationtab.cid").Where("chargetab.cid = ?", t.Cid)
|
|
if !utils.ValueIsEmpty(buildingid) {
|
|
query = query.And("propertytab.buildingid = ?", buildingid)
|
|
}
|
|
if !utils.ValueIsEmpty(beginyear) {
|
|
query = query.And("chargeallocationtab.allocateyear = ? and chargeallocationtab.allocatemonth >= ? and chargeallocationtab.cid = ?", beginyear, settledate, t.Cid)
|
|
}
|
|
if !utils.ValueIsEmpty(endyear) {
|
|
query = query.Or("chargeallocationtab.allocateyear = ? and chargeallocationtab.allocatemonth <= ? and chargeallocationtab.cid = ?", endyear, settledate, t.Cid)
|
|
}
|
|
if t.Chargetype > 0 {
|
|
query = query.And("chargetab.chargetype = ?", t.Chargetype)
|
|
}
|
|
err := query.GroupBy("propertytab.buildingid").Find(&data)
|
|
if err != nil {
|
|
return nil, 0, "", err
|
|
}
|
|
rateMoney := 0.0
|
|
i := 0.0
|
|
sumMoney := 0.0
|
|
for k, v := range data {
|
|
i++
|
|
sumMoney += v.Money
|
|
//计算每栋楼的缴费数
|
|
p := new(Propertytab)
|
|
total, _ := e.Table("propertytab").Join("INNER", "contracttab", "propertytab.contractid=contracttab.contractid and propertytab.cid=contracttab.cid").Where("propertytab.buildingid = ? and contracttab.enddate >? and propertytab.cid = ?", v.Buildingid, startdate, t.Cid).Count(p)
|
|
//计算每栋楼的房间数
|
|
alltotal, _ := e.Table("propertytab").Where("isfree != '3' and propertytab.buildingid = ? and propertytab.cid = ?", v.Buildingid, t.Cid).Count(p)
|
|
rate := utils.ValueToFloat(total, 0.0) / utils.ValueToFloat(alltotal, 0.0) * 100
|
|
rateMoney += rate
|
|
data[k].Rate = fmt.Sprintf("%0.0f", rate) + "%"
|
|
}
|
|
rateAll := rateMoney / i
|
|
rateBar := fmt.Sprintf("%0.0f", rateAll) + "%"
|
|
total := utils.ValueToInt(sumMoney, 0)
|
|
return data, total, rateBar, nil
|
|
}
|
|
|
|
type SumStruct struct {
|
|
Cid int `json:"cid"`
|
|
Allocateyear int `json:"allocateyear"`
|
|
ReceivableArea float64 `json:"receivable_area"`
|
|
PaidInArea float64 `json:"paid_in_area"`
|
|
ReceivableMoney float64 `json:"receivable_money"`
|
|
PaidInMoney float64 `json:"paid_in_money"`
|
|
CurrentPercent string `json:"current_percent"`
|
|
ArrearagePercent string `json:"arrearage_percent"`
|
|
}
|
|
|
|
type BuildingInfo struct {
|
|
Constructionarea float64 `json:"constructionarea"`
|
|
Unitprice float64 `json:"unitprice"`
|
|
}
|
|
|
|
//按年统计
|
|
func (t *Chargetab) ChargeTotalByYear(beginyear string, endyear string, settledate int) ([]SumStruct, int, error) {
|
|
e := db.MasterEngine()
|
|
if utils.ValueIsEmpty(beginyear) {
|
|
beginyear = utils.TimeFormat(time.Now(), "yyyy")
|
|
}
|
|
if utils.ValueIsEmpty(endyear) {
|
|
endyear = utils.TimeFormat(time.Now(), "yyyy")
|
|
}
|
|
lengh := utils.ValueToString(settledate, "")
|
|
begdate := utils.ValueToInt(beginyear, 0)
|
|
enddate := utils.ValueToInt(endyear, 0)
|
|
datalist := make([]SumStruct, 0)
|
|
if t.Chargetype == 1 {
|
|
for i := begdate; i <= enddate; i++ {
|
|
endtime := i + 1
|
|
startdate := ""
|
|
closedate := ""
|
|
if len(lengh) > 1 {
|
|
startdate = utils.ValueToString(begdate, "") + "-" + utils.ValueToString(settledate, "") + "-01"
|
|
closedate = utils.ValueToString(endtime, "") + "-" + utils.ValueToString(settledate, "") + "-01"
|
|
} else {
|
|
startdate = utils.ValueToString(begdate, "") + "-0" + utils.ValueToString(settledate, "") + "-01"
|
|
closedate = utils.ValueToString(endtime, "") + "-0" + utils.ValueToString(settledate, "") + "-01"
|
|
}
|
|
//计算实收金额
|
|
ss := new(SumStruct)
|
|
_, err := e.Table("chargeallocationtab").Select("sum(allocateexpense) as paid_in_money").Where("allocateyear =? and allocatemonth >=? and cid = ?", i, settledate, t.Cid).Or("allocateyear =? and allocatemonth <=? and cid = ?", endtime, settledate, t.Cid).Get(ss)
|
|
//计算实收缴费面积
|
|
one := SumStruct{}
|
|
one.Cid = t.Cid
|
|
one.Allocateyear = i
|
|
PaidInMoney := fmt.Sprintf("%0.0f", ss.PaidInMoney)
|
|
one.PaidInMoney = utils.ValueToFloat(PaidInMoney, 0.0)
|
|
info := new(SumStruct)
|
|
_, err = e.Table("chargetab").Select("sum(propertytab.constructionarea) as paid_in_area").Join("INNER", "propertytab", "propertytab.propertyid = chargetab.propertyid and propertytab.cid = chargetab.cid").Where("chargetab.cid = ? and chargetab.chargeenddate >= ? and chargetab.chargeenddate < ?", t.Cid, startdate, closedate).Get(info)
|
|
if err != nil {
|
|
glog.InfoExtln("年缴费测试", "err1 := ", err)
|
|
continue
|
|
}
|
|
area := fmt.Sprintf("%0.2f", info.PaidInArea)
|
|
one.PaidInArea = utils.ValueToFloat(area, 0.0)
|
|
//计算应收金额和面积
|
|
buildInfo := new(BuildingInfo)
|
|
_, err = e.Table("propertytab").Select("propertytypetab.unitprice as unitprice,(SELECT SUM(constructionarea) FROM propertytab WHERE isfree != '3') as constructionarea").Join("INNER", "buildingtab", "propertytab.buildingid=buildingtab.buildingid and propertytab.cid=buildingtab.cid").Join("INNER", "propertytypetab", "propertytypetab.propertytypeid=buildingtab.propertytypeid and propertytypetab.cid=buildingtab.cid").Where("propertytab.cid = ?", t.Cid).Get(buildInfo)
|
|
if err != nil {
|
|
glog.InfoExtln("年缴费测试", "err2 := ", err)
|
|
continue
|
|
}
|
|
ReceivableArea := fmt.Sprintf("%0.2f", buildInfo.Constructionarea)
|
|
one.ReceivableArea = utils.ValueToFloat(ReceivableArea, 0.0)
|
|
money := fmt.Sprintf("%0.0f", buildInfo.Constructionarea*buildInfo.Unitprice*12)
|
|
one.ReceivableMoney = utils.ValueToFloat(money, 0.0)
|
|
glog.InfoExtln("年缴费测试", "one := ", one)
|
|
datalist = append(datalist, one)
|
|
}
|
|
}
|
|
sumMoney := 0.0
|
|
for _, v := range datalist {
|
|
sumMoney += v.PaidInMoney
|
|
}
|
|
total := utils.ValueToInt(sumMoney, 0)
|
|
return datalist, total, nil
|
|
}
|
|
|
|
type ChargeUsers struct {
|
|
Cid int `json:"cid"`
|
|
Chargetype int `json:"chargetype"`
|
|
Propertyid string `json:"propertyid"`
|
|
Begdate string `json:"begdate"`
|
|
Enddate string `json:"enddate"`
|
|
Chargableexpense float64 `json:"chargableexpense"`
|
|
Chargedexpense float64 `json:"chargedexpense"`
|
|
Lengthdate string `json:"lengthdate"`
|
|
Chargetime string `json:"chargetime"`
|
|
Createby string `json:"createby"`
|
|
Constructionarea float64 `json:"constructionarea"`
|
|
Linkman string `json:"linkman"`
|
|
Phone1 string `json:"phone1"`
|
|
Phone2 string `json:"phone2"`
|
|
}
|
|
|
|
//分页
|
|
func (t *Chargetab) ListPage(pageSize int, pageIndex int, starttime string, searchtime string) ([]ChargeUsers, int, float64, error) {
|
|
data := make([]ChargeUsers, 0)
|
|
e := db.MasterEngine()
|
|
|
|
switch t.Chargetype {
|
|
case 1:
|
|
query := e.Table("propertytab")
|
|
table := e.Table("propertytab")
|
|
total := e.Table("propertytab")
|
|
//物业费
|
|
query = query.Join("LEFT", "contracttab", "propertytab.contractid = contracttab.contractid and propertytab.cid = contracttab.cid").Where("propertytab.cid = ?", t.Cid)
|
|
table = table.Join("LEFT", "contracttab", "propertytab.contractid = contracttab.contractid and propertytab.cid = contracttab.cid").Where("propertytab.cid = ?", t.Cid)
|
|
total = total.Join("LEFT", "contracttab", "propertytab.contractid = contracttab.contractid and propertytab.cid = contracttab.cid").Where("propertytab.cid = ?", t.Cid)
|
|
if !utils.ValueIsEmpty(t.Propertyid) {
|
|
query = query.And("propertytab.propertyid = ?", t.Propertyid)
|
|
table = table.And("propertytab.propertyid = ?", t.Propertyid)
|
|
total = total.And("propertytab.propertyid = ?", t.Propertyid)
|
|
}
|
|
if !utils.ValueIsEmpty(searchtime) {
|
|
query = query.And("contracttab.enddate < ?", searchtime)
|
|
table = table.And("contracttab.enddate < ?", searchtime)
|
|
total = total.And("contracttab.enddate < ?", searchtime)
|
|
}
|
|
if !utils.ValueIsEmpty(starttime) {
|
|
query = query.And("contracttab.enddate > ?", starttime)
|
|
table = table.And("contracttab.enddate > ?", starttime)
|
|
total = total.And("contracttab.enddate > ?", starttime)
|
|
}
|
|
query = query.And("contracttab.chargetype = ?", 1)
|
|
table = table.And("contracttab.chargetype = ?", 1)
|
|
total = total.And("contracttab.chargetype = ?", 1)
|
|
Offset := (pageIndex - 1) * pageSize
|
|
err := query.Limit(pageSize, Offset).Desc("propertytab.propertyid").Find(&data)
|
|
pcount := new(Chargetab)
|
|
count, err := table.Count(pcount)
|
|
if err != nil {
|
|
return data, 0, 0, err
|
|
}
|
|
ss := new(Chargetab)
|
|
_, err = total.Select("sum(chargedexpense) as chargedexpense").Get(ss)
|
|
if err != nil {
|
|
return data, 0, 0, err
|
|
}
|
|
|
|
return data, int(count), ss.Chargedexpense, nil
|
|
|
|
case 2:
|
|
query := e.Table("accesscardtab")
|
|
table := e.Table("accesscardtab")
|
|
total := e.Table("accesscardtab")
|
|
//电梯费
|
|
query = query.Join("LEFT", "contracttab", "accesscardtab.contractid = contracttab.contractid and accesscardtab.cid = contracttab.cid").Where("accesscardtab.cid = ?", t.Cid)
|
|
table = table.Join("LEFT", "contracttab", "accesscardtab.contractid = contracttab.contractid and accesscardtab.cid = contracttab.cid").Where("accesscardtab.cid = ?", t.Cid)
|
|
total = total.Join("LEFT", "contracttab", "accesscardtab.contractid = contracttab.contractid and accesscardtab.cid = contracttab.cid").Where("accesscardtab.cid = ?", t.Cid)
|
|
if !utils.ValueIsEmpty(t.Accesscardid) {
|
|
query = query.And("accesscardtab.accesscardid = ?", t.Accesscardid)
|
|
table = table.And("accesscardtab.accesscardid = ?", t.Accesscardid)
|
|
total = total.And("accesscardtab.accesscardid = ?", t.Accesscardid)
|
|
}
|
|
if !utils.ValueIsEmpty(searchtime) {
|
|
query = query.And("contracttab.enddate < ?", searchtime)
|
|
table = table.And("contracttab.enddate < ?", searchtime)
|
|
total = total.And("contracttab.enddate < ?", searchtime)
|
|
}
|
|
if !utils.ValueIsEmpty(starttime) {
|
|
query = query.And("contracttab.enddate > ?", starttime)
|
|
table = table.And("contracttab.enddate > ?", starttime)
|
|
total = total.And("contracttab.enddate > ?", starttime)
|
|
}
|
|
query = query.And("contracttab.chargetype = ?", 2)
|
|
table = table.And("contracttab.chargetype = ?", 2)
|
|
total = total.And("contracttab.chargetype = ?", 2)
|
|
Offset := (pageIndex - 1) * pageSize
|
|
err := query.Limit(pageSize, Offset).Desc("accesscardtab.accesscardid").Find(&data)
|
|
pcount := new(Chargetab)
|
|
count, err := table.Count(pcount)
|
|
if err != nil {
|
|
return data, 0, 0, err
|
|
}
|
|
ss := new(Chargetab)
|
|
_, err = total.Select("sum(chargedexpense) as chargedexpense").Get(ss)
|
|
if err != nil {
|
|
return data, 0, 0, err
|
|
}
|
|
|
|
return data, int(count), ss.Chargedexpense, nil
|
|
case 3:
|
|
query := e.Table("carporttab")
|
|
table := e.Table("carporttab")
|
|
total := e.Table("carporttab")
|
|
//车位费
|
|
query = query.Join("LEFT", "contracttab", "carporttab.contractid = contracttab.contractid and carporttab.cid = contracttab.cid").Where("carporttab.cid = ?", t.Cid)
|
|
table = table.Join("LEFT", "contracttab", "carporttab.contractid = contracttab.contractid and carporttab.cid = contracttab.cid").Where("carporttab.cid = ?", t.Cid)
|
|
total = total.Join("LEFT", "contracttab", "carporttab.contractid = contracttab.contractid and carporttab.cid = contracttab.cid").Where("carporttab.cid = ?", t.Cid)
|
|
if !utils.ValueIsEmpty(t.Accesscardid) {
|
|
query = query.And("carporttab.carportid = ?", t.Carportid)
|
|
table = table.And("carporttab.carportid = ?", t.Carportid)
|
|
total = total.And("carporttab.carportid = ?", t.Carportid)
|
|
}
|
|
if !utils.ValueIsEmpty(searchtime) {
|
|
query = query.And("contracttab.enddate < ?", searchtime)
|
|
table = table.And("contracttab.enddate < ?", searchtime)
|
|
total = total.And("contracttab.enddate < ?", searchtime)
|
|
}
|
|
if !utils.ValueIsEmpty(starttime) {
|
|
query = query.And("contracttab.enddate > ?", starttime)
|
|
table = table.And("contracttab.enddate > ?", starttime)
|
|
total = total.And("contracttab.enddate > ?", starttime)
|
|
}
|
|
query = query.And("contracttab.chargetype = ?", 3)
|
|
table = table.And("contracttab.chargetype = ?", 3)
|
|
total = total.And("contracttab.chargetype = ?", 3)
|
|
Offset := (pageIndex - 1) * pageSize
|
|
err := query.Limit(pageSize, Offset).Desc("carporttab.carportid").Find(&data)
|
|
pcount := new(Chargetab)
|
|
count, err := table.Count(pcount)
|
|
if err != nil {
|
|
return data, 0, 0, err
|
|
}
|
|
ss := new(Chargetab)
|
|
_, err = total.Select("sum(chargedexpense) as chargedexpense").Get(ss)
|
|
if err != nil {
|
|
return data, 0, 0, err
|
|
}
|
|
|
|
return data, int(count), ss.Chargedexpense, nil
|
|
case 4:
|
|
//其他费用
|
|
query := e.Table("chargetab").Where("chargetab.cid = ?", t.Cid)
|
|
table := e.Table("chargetab").Where("chargetab.cid = ?", t.Cid)
|
|
total := e.Table("chargetab").Where("chargetab.cid = ?", t.Cid)
|
|
|
|
if !utils.ValueIsEmpty(searchtime) {
|
|
query = query.And("chargetab.chargestartdate < ?", searchtime)
|
|
table = table.And("chargetab.chargestartdate < ?", searchtime)
|
|
total = total.And("chargetab.chargestartdate < ?", searchtime)
|
|
}
|
|
if !utils.ValueIsEmpty(starttime) {
|
|
query = query.And("chargetab.chargeenddate > ?", starttime)
|
|
table = table.And("chargetab.chargeenddate > ?", starttime)
|
|
total = total.And("chargetab.chargeenddate > ?", starttime)
|
|
}
|
|
query = query.And("chargetab.chargetype = ?", 4)
|
|
table = table.And("chargetab.chargetype = ?", 4)
|
|
total = total.And("chargetab.chargetype = ?", 4)
|
|
Offset := (pageIndex - 1) * pageSize
|
|
err := query.Limit(pageSize, Offset).Desc("chargetab.chargetime").Find(&data)
|
|
pcount := new(Chargetab)
|
|
count, err := table.Count(pcount)
|
|
if err != nil {
|
|
return data, 0, 0, err
|
|
}
|
|
ss := new(Chargetab)
|
|
_, err = total.Select("sum(chargedexpense) as chargedexpense").Get(ss)
|
|
if err != nil {
|
|
return data, 0, 0, err
|
|
}
|
|
|
|
return data, int(count), ss.Chargedexpense, nil
|
|
default:
|
|
query := e.Table("propertytab")
|
|
table := e.Table("propertytab")
|
|
total := e.Table("propertytab")
|
|
//物业费
|
|
query = query.Join("LEFT", "contracttab", "propertytab.contractid = contracttab.contractid and propertytab.cid = contracttab.cid").Where("propertytab.cid = ?", t.Cid)
|
|
table = table.Join("LEFT", "contracttab", "propertytab.contractid = contracttab.contractid and propertytab.cid = contracttab.cid").Where("propertytab.cid = ?", t.Cid)
|
|
total = total.Join("LEFT", "contracttab", "propertytab.contractid = contracttab.contractid and propertytab.cid = contracttab.cid").Where("propertytab.cid = ?", t.Cid)
|
|
if !utils.ValueIsEmpty(t.Propertyid) {
|
|
query = query.And("propertytab.propertyid = ?", t.Propertyid)
|
|
table = table.And("propertytab.propertyid = ?", t.Propertyid)
|
|
total = total.And("propertytab.propertyid = ?", t.Propertyid)
|
|
}
|
|
if !utils.ValueIsEmpty(searchtime) {
|
|
query = query.And("contracttab.enddate < ?", searchtime)
|
|
table = table.And("contracttab.enddate < ?", searchtime)
|
|
total = total.And("contracttab.enddate < ?", searchtime)
|
|
}
|
|
if !utils.ValueIsEmpty(starttime) {
|
|
query = query.And("contracttab.enddate > ?", starttime)
|
|
table = table.And("contracttab.enddate > ?", starttime)
|
|
total = total.And("contracttab.enddate > ?", starttime)
|
|
}
|
|
query = query.And("contracttab.chargetype = ?", 1)
|
|
table = table.And("contracttab.chargetype = ?", 1)
|
|
total = total.And("contracttab.chargetype = ?", 1)
|
|
Offset := (pageIndex - 1) * pageSize
|
|
err := query.Limit(pageSize, Offset).Desc("propertytab.propertyid").Find(&data)
|
|
pcount := new(Chargetab)
|
|
count, err := table.Count(pcount)
|
|
|
|
ss := new(Chargetab)
|
|
_, err = total.Select("sum(chargedexpense) as chargedexpense").Get(ss)
|
|
if err != nil {
|
|
return data, 0, 0, err
|
|
}
|
|
|
|
return data, int(count), ss.Chargedexpense, nil
|
|
|
|
}
|
|
|
|
}
|
|
|
|
//导出
|
|
func (t *Chargetab) ReadData(startDate string, searchtime string) ([]ChargeUsers, error) {
|
|
data := make([]ChargeUsers, 0)
|
|
e := db.MasterEngine()
|
|
switch t.Chargetype {
|
|
case 1:
|
|
query := e.Table("propertytab")
|
|
//物业费
|
|
query = query.Join("LEFT", "contracttab", "propertytab.contractid = contracttab.contractid and propertytab.cid = contracttab.cid").Where("propertytab.cid = ?", t.Cid)
|
|
if !utils.ValueIsEmpty(t.Propertyid) {
|
|
query = query.And("propertytab.propertyid = ?", t.Propertyid)
|
|
}
|
|
if !utils.ValueIsEmpty(searchtime) {
|
|
query = query.And("contracttab.enddate < ?", searchtime)
|
|
}
|
|
if !utils.ValueIsEmpty(startDate) {
|
|
query = query.And("contracttab.enddate > ?", startDate)
|
|
}
|
|
query = query.And("contracttab.chargetype = ?", 1)
|
|
err := query.Desc("contracttab.createtime").Find(&data)
|
|
if err != nil {
|
|
return data, err
|
|
}
|
|
|
|
return data, nil
|
|
|
|
case 2:
|
|
query := e.Table("accesscardtab")
|
|
//电梯费
|
|
query = query.Join("LEFT", "contracttab", "accesscardtab.contractid = contracttab.contractid and accesscardtab.cid = contracttab.cid").Where("accesscardtab.cid = ?", t.Cid)
|
|
if !utils.ValueIsEmpty(t.Accesscardid) {
|
|
query = query.And("accesscardtab.accesscardid = ?", t.Accesscardid)
|
|
}
|
|
if !utils.ValueIsEmpty(searchtime) {
|
|
query = query.And("contracttab.enddate < ?", searchtime)
|
|
}
|
|
if !utils.ValueIsEmpty(startDate) {
|
|
query = query.And("contracttab.enddate > ?", startDate)
|
|
}
|
|
query = query.And("contracttab.chargetype = ?", 2)
|
|
err := query.Desc("contracttab.createtime").Find(&data)
|
|
|
|
if err != nil {
|
|
return data, err
|
|
}
|
|
|
|
return data, nil
|
|
case 3:
|
|
query := e.Table("carporttab")
|
|
|
|
//车位费
|
|
query = query.Join("LEFT", "contracttab", "carporttab.contractid = contracttab.contractid and carporttab.cid = contracttab.cid").Where("carporttab.cid = ?", t.Cid)
|
|
if !utils.ValueIsEmpty(t.Accesscardid) {
|
|
query = query.And("carporttab.carportid = ?", t.Carportid)
|
|
}
|
|
if !utils.ValueIsEmpty(searchtime) {
|
|
query = query.And("contracttab.enddate < ?", searchtime)
|
|
}
|
|
if !utils.ValueIsEmpty(startDate) {
|
|
query = query.And("contracttab.enddate > ?", startDate)
|
|
}
|
|
query = query.And("contracttab.chargetype = ?", 3)
|
|
err := query.Desc("contracttab.createtime").Find(&data)
|
|
if err != nil {
|
|
return data, err
|
|
}
|
|
|
|
return data, nil
|
|
default:
|
|
query := e.Table("propertytab")
|
|
//物业费
|
|
query = query.Join("LEFT", "contracttab", "propertytab.contractid = contracttab.contractid and propertytab.cid = contracttab.cid").Where("propertytab.cid = ?", t.Cid)
|
|
if !utils.ValueIsEmpty(t.Propertyid) {
|
|
query = query.And("propertytab.propertyid = ?", t.Propertyid)
|
|
}
|
|
if !utils.ValueIsEmpty(searchtime) {
|
|
query = query.And("contracttab.enddate < ?", searchtime)
|
|
}
|
|
if !utils.ValueIsEmpty(startDate) {
|
|
query = query.And("contracttab.enddate > ?", startDate)
|
|
}
|
|
query = query.And("contracttab.chargetype = ?", 1)
|
|
err := query.Desc("contracttab.createtime").Find(&data)
|
|
if err != nil {
|
|
return data, err
|
|
}
|
|
|
|
return data, nil
|
|
|
|
}
|
|
}
|
|
|
|
//导出需要缴费得业主
|
|
func (t *Chargetab) ReadExcel(startDate string, searchtime string) (excelfile string, filename string, err error) {
|
|
me := new(Chargetab)
|
|
me.Cid = t.Cid
|
|
datalist, err := me.ReadData(startDate, searchtime)
|
|
if err != nil {
|
|
return "", "", err
|
|
}
|
|
var file *xlsx.File
|
|
var sheet *xlsx.Sheet
|
|
var row *xlsx.Row
|
|
var cell *xlsx.Cell
|
|
|
|
file = xlsx.NewFile()
|
|
sheet, err = file.AddSheet("Sheet1")
|
|
if err != nil {
|
|
return "", "", err
|
|
}
|
|
|
|
tableHead := true
|
|
|
|
for _, val := range datalist {
|
|
if tableHead {
|
|
//添加表头
|
|
row = sheet.AddRow()
|
|
|
|
cell = row.AddCell()
|
|
cell.Value = "缴费类型"
|
|
|
|
cell = row.AddCell()
|
|
cell.Value = "物业编号"
|
|
|
|
cell = row.AddCell()
|
|
cell.Value = "收费起始日"
|
|
|
|
cell = row.AddCell()
|
|
cell.Value = "收费结束日"
|
|
|
|
cell = row.AddCell()
|
|
cell.Value = "实收费用"
|
|
|
|
cell = row.AddCell()
|
|
cell.Value = "收费时间"
|
|
|
|
cell = row.AddCell()
|
|
cell.Value = "收费人员"
|
|
|
|
cell = row.AddCell()
|
|
cell.Value = "建筑面积"
|
|
|
|
cell = row.AddCell()
|
|
cell.Value = "联系人"
|
|
|
|
cell = row.AddCell()
|
|
cell.Value = "联系方式1"
|
|
|
|
cell = row.AddCell()
|
|
cell.Value = "联系方式2"
|
|
|
|
tableHead = false
|
|
continue
|
|
}
|
|
row = sheet.AddRow()
|
|
//产品标识
|
|
cell = row.AddCell()
|
|
cell.Value = utils.ValueToString(val.Chargetype, "")
|
|
//描述
|
|
cell = row.AddCell()
|
|
cell.Value = utils.ValueToString(val.Propertyid, "")
|
|
|
|
cell = row.AddCell()
|
|
cell.Value = utils.ValueToString(val.Begdate, "")
|
|
|
|
cell = row.AddCell()
|
|
cell.Value = utils.ValueToString(val.Enddate, "")
|
|
|
|
cell = row.AddCell()
|
|
cell.Value = fmt.Sprintf("%0.0f", val.Chargedexpense)
|
|
|
|
cell = row.AddCell()
|
|
cell.Value = utils.ValueToString(val.Chargetime, "")
|
|
|
|
cell = row.AddCell()
|
|
cell.Value = utils.ValueToString(val.Createby, "")
|
|
|
|
cell = row.AddCell()
|
|
cell.Value = fmt.Sprintf("%0.2f", val.Constructionarea)
|
|
|
|
cell = row.AddCell()
|
|
cell.Value = utils.ValueToString(val.Linkman, "")
|
|
|
|
cell = row.AddCell()
|
|
cell.Value = utils.ValueToString(val.Phone1, "")
|
|
|
|
cell = row.AddCell()
|
|
cell.Value = utils.ValueToString(val.Phone2, "")
|
|
|
|
}
|
|
name := utils.MakeOrderSn("缴费信息")
|
|
filename = name + ".xlsx" //文件名
|
|
|
|
exceldir, _ := utils.GetCurrentPath("web/public/uploadxlsx") // 目录+文件名
|
|
excelfile = exceldir + "/" + filename
|
|
err = file.Save(excelfile)
|
|
if err != nil {
|
|
return "", "", err
|
|
}
|
|
return excelfile, filename, nil
|
|
}
|
|
|
|
//导出需要缴费得业主
|
|
func (t *Chargetab) ReadBuildExcel(buildingid string, begyear string, endyear string, settledate int) (excelfile string, filename string, err error) {
|
|
me := new(Chargetab)
|
|
me.Cid = t.Cid
|
|
datalist, _, _, err := me.ChargeTotalByBuilding(buildingid, begyear, endyear, settledate)
|
|
if err != nil {
|
|
return "", "", err
|
|
}
|
|
var file *xlsx.File
|
|
var sheet *xlsx.Sheet
|
|
var row *xlsx.Row
|
|
var cell *xlsx.Cell
|
|
|
|
file = xlsx.NewFile()
|
|
sheet, err = file.AddSheet("Sheet1")
|
|
if err != nil {
|
|
return "", "", err
|
|
}
|
|
|
|
tableHead := true
|
|
|
|
for _, val := range datalist {
|
|
if tableHead {
|
|
//添加表头
|
|
row = sheet.AddRow()
|
|
|
|
cell = row.AddCell()
|
|
cell.Value = "项目号"
|
|
|
|
cell = row.AddCell()
|
|
cell.Value = "楼号"
|
|
|
|
cell = row.AddCell()
|
|
cell.Value = "金额"
|
|
|
|
tableHead = false
|
|
continue
|
|
}
|
|
row = sheet.AddRow()
|
|
//产品标识
|
|
cell = row.AddCell()
|
|
cell.Value = "东大迎湖园"
|
|
//描述
|
|
cell = row.AddCell()
|
|
cell.Value = utils.ValueToString(val.Buildingid, "")
|
|
|
|
cell = row.AddCell()
|
|
cell.Value = fmt.Sprintf("%0.0f", val.Money)
|
|
|
|
}
|
|
name := utils.MakeOrderSn("楼号统计")
|
|
filename = name + ".xlsx" //文件名
|
|
|
|
exceldir, _ := utils.GetCurrentPath("web/public/uploadxlsx") // 目录+文件名
|
|
excelfile = exceldir + "/" + filename
|
|
err = file.Save(excelfile)
|
|
if err != nil {
|
|
return "", "", err
|
|
}
|
|
return excelfile, filename, nil
|
|
}
|
|
|
|
//导出需要缴费得业主
|
|
func (t *Chargetab) ReadYearExcel(beginyear string, endyear string, settledate int) (excelfile string, filename string, err error) {
|
|
me := new(Chargetab)
|
|
me.Cid = t.Cid
|
|
me.Chargetype = t.Chargetype
|
|
datalist, _, err := me.ChargeTotalByYear(beginyear, endyear, settledate)
|
|
if err != nil {
|
|
return "", "", err
|
|
}
|
|
var file *xlsx.File
|
|
var sheet *xlsx.Sheet
|
|
var row *xlsx.Row
|
|
var cell *xlsx.Cell
|
|
|
|
file = xlsx.NewFile()
|
|
sheet, err = file.AddSheet("Sheet1")
|
|
if err != nil {
|
|
return "", "", err
|
|
}
|
|
|
|
tableHead := true
|
|
|
|
for _, val := range datalist {
|
|
if tableHead {
|
|
//添加表头
|
|
row = sheet.AddRow()
|
|
|
|
cell = row.AddCell()
|
|
cell.Value = "年份"
|
|
|
|
cell = row.AddCell()
|
|
cell.Value = "应收面积"
|
|
|
|
cell = row.AddCell()
|
|
cell.Value = "实收面积"
|
|
|
|
cell = row.AddCell()
|
|
cell.Value = "应收金额"
|
|
|
|
cell = row.AddCell()
|
|
cell.Value = "实收金额"
|
|
|
|
tableHead = false
|
|
continue
|
|
}
|
|
row = sheet.AddRow()
|
|
//产品标识
|
|
cell = row.AddCell()
|
|
cell.Value = utils.ValueToString(val.Allocateyear, "")
|
|
//描述
|
|
cell = row.AddCell()
|
|
cell.Value = fmt.Sprintf("%0.2f", val.ReceivableArea)
|
|
|
|
cell = row.AddCell()
|
|
cell.Value = fmt.Sprintf("%0.2f", val.PaidInArea)
|
|
|
|
cell = row.AddCell()
|
|
cell.Value = fmt.Sprintf("%0.0f", val.ReceivableMoney)
|
|
|
|
cell = row.AddCell()
|
|
cell.Value = fmt.Sprintf("%0.0f", val.PaidInMoney)
|
|
|
|
}
|
|
name := utils.MakeOrderSn("年统计")
|
|
filename = name + ".xlsx" //文件名
|
|
|
|
exceldir, _ := utils.GetCurrentPath("web/public/uploadxlsx") // 目录+文件名
|
|
excelfile = exceldir + "/" + filename
|
|
err = file.Save(excelfile)
|
|
if err != nil {
|
|
return "", "", err
|
|
}
|
|
return excelfile, filename, nil
|
|
}
|
|
|
|
func (t *Chargetab) ReadChargeData(buildingid string, unit string, room string) ([]Chargetab, error) {
|
|
data := make([]Chargetab, 0)
|
|
e := db.MasterEngine()
|
|
query := e.Table("chargetab")
|
|
table := e.Table("chargetab")
|
|
switch t.Chargetype {
|
|
case 1:
|
|
//物业费
|
|
query = query.Join("LEFT", "propertytab", "propertytab.propertyid = chargetab.propertyid and propertytab.cid = chargetab.cid").Where("chargetab.cid = ?", t.Cid)
|
|
table = table.Join("LEFT", "propertytab", "propertytab.propertyid = chargetab.propertyid").Where("chargetab.cid = ?", t.Cid)
|
|
query = query.And("chargetab.chargetype = ?", 1)
|
|
table = table.And("chargetab.chargetype = ?", 1)
|
|
case 2:
|
|
//电梯费
|
|
query = query.Join("LEFT", "propertytab", "propertytab.propertyid = chargetab.propertyid and propertytab.cid = chargetab.cid").Join("LEFT", "accesscardtab", "accesscardtab.accesscardid = chargetab.accesscardid and accesscardtab.cid = chargetab.cid").Where("chargetab.cid = ?", t.Cid)
|
|
table = table.Join("LEFT", "propertytab", "propertytab.propertyid = chargetab.propertyid and propertytab.cid = chargetab.cid").Join("LEFT", "accesscardtab", "accesscardtab.accesscardid = chargetab.accesscardid and accesscardtab.cid = chargetab.cid").Where("chargetab.cid = ?", t.Cid)
|
|
if !utils.ValueIsEmpty(t.Accesscardid) {
|
|
query = query.And("accesscardtab.accesscardid = ?", t.Accesscardid)
|
|
table = table.And("accesscardtab.accesscardid = ?", t.Accesscardid)
|
|
}
|
|
query = query.And("chargetab.chargetype = ?", 2)
|
|
table = table.And("chargetab.chargetype = ?", 2)
|
|
case 3:
|
|
//车位费
|
|
query = query.Join("LEFT", "propertytab", "propertytab.propertyid = chargetab.propertyid and propertytab.cid = chargetab.cid").Join("LEFT", "carporttab", "carporttab.carportid = chargetab.carportid and carporttab.cid = chargetab.cid").Where("chargetab.cid = ?", t.Cid)
|
|
table = table.Join("LEFT", "propertytab", "propertytab.propertyid = chargetab.propertyid and propertytab.cid = chargetab.cid").Join("LEFT", "carporttab", "carporttab.carportid = chargetab.carportid and carporttab.cid = chargetab.cid").Where("chargetab.cid = ?", t.Cid)
|
|
if !utils.ValueIsEmpty(t.Accesscardid) {
|
|
query = query.And("carporttab.carportid = ?", t.Carportid)
|
|
table = table.And("carporttab.carportid = ?", t.Carportid)
|
|
}
|
|
query = query.And("chargetab.chargetype = ?", 3)
|
|
table = table.And("chargetab.chargetype = ?", 3)
|
|
default:
|
|
//物业费
|
|
query = query.Join("LEFT", "propertytab", "propertytab.propertyid = chargetab.propertyid and propertytab.cid = chargetab.cid").Where("chargetab.cid = ?", t.Cid)
|
|
table = table.Join("LEFT", "propertytab", "propertytab.propertyid = chargetab.propertyid and propertytab.cid = chargetab.cid").Where("chargetab.cid = ?", t.Cid)
|
|
query = query.And("chargetab.chargetype = ?", 1)
|
|
table = table.And("chargetab.chargetype = ?", 1)
|
|
}
|
|
if !utils.ValueIsEmpty(buildingid) {
|
|
query = query.And("propertytab.buildingid = ?", buildingid)
|
|
table = table.And("propertytab.buildingid = ?", buildingid)
|
|
}
|
|
if !utils.ValueIsEmpty(unit) {
|
|
query = query.And("propertytab.unit = ?", unit)
|
|
table = table.And("propertytab.unit = ?", unit)
|
|
}
|
|
if !utils.ValueIsEmpty(room) {
|
|
query = query.And("propertytab.room = ?", room)
|
|
table = table.And("propertytab.room = ?", room)
|
|
}
|
|
if !utils.ValueIsEmpty(t.Chargestartdate) {
|
|
query = query.And("chargetab.chargestartdate >= ?", t.Chargestartdate)
|
|
table = table.And("chargetab.chargestartdate >= ?", t.Chargestartdate)
|
|
}
|
|
if !utils.ValueIsEmpty(t.Chargeenddate) {
|
|
query = query.And("chargetab.chargeenddate <= ?", t.Chargeenddate)
|
|
table = table.And("chargetab.chargeenddate <= ?", t.Chargeenddate)
|
|
}
|
|
err := query.Asc("propertytab.buildingid", "propertytab.unit", "propertytab.room").Find(&data)
|
|
if err != nil {
|
|
return data, err
|
|
}
|
|
|
|
return data, nil
|
|
}
|
|
|
|
//导出台账
|
|
func (t *Chargetab) ReadChargeDataExcel(buildingid string, unit string, room string) (excelfile string, filename string, err error) {
|
|
me := new(Chargetab)
|
|
me.Cid = t.Cid
|
|
me.Chargetype = t.Chargetype
|
|
me.Chargeenddate = t.Chargeenddate
|
|
me.Chargestartdate = t.Chargestartdate
|
|
datalist, err := me.ReadChargeData(buildingid, unit, room)
|
|
if err != nil {
|
|
return "", "", err
|
|
}
|
|
var file *xlsx.File
|
|
var sheet *xlsx.Sheet
|
|
var row *xlsx.Row
|
|
var cell *xlsx.Cell
|
|
|
|
file = xlsx.NewFile()
|
|
sheet, err = file.AddSheet("Sheet1")
|
|
if err != nil {
|
|
return "", "", err
|
|
}
|
|
|
|
tableHead := true
|
|
|
|
for _, val := range datalist {
|
|
if tableHead {
|
|
//添加表头
|
|
row = sheet.AddRow()
|
|
|
|
cell = row.AddCell()
|
|
cell.Value = "缴费类型"
|
|
|
|
cell = row.AddCell()
|
|
cell.Value = "物业编号"
|
|
|
|
cell = row.AddCell()
|
|
cell.Value = "收费起始日"
|
|
|
|
cell = row.AddCell()
|
|
cell.Value = "收费结束日"
|
|
|
|
cell = row.AddCell()
|
|
cell.Value = "实收金额"
|
|
|
|
cell = row.AddCell()
|
|
cell.Value = "收费时间"
|
|
|
|
cell = row.AddCell()
|
|
cell.Value = "收费人员"
|
|
|
|
tableHead = false
|
|
continue
|
|
}
|
|
row = sheet.AddRow()
|
|
|
|
cell = row.AddCell()
|
|
cell.Value = utils.ValueToString(val.Chargetype, "")
|
|
|
|
cell = row.AddCell()
|
|
cell.Value = utils.ValueToString(val.Propertyid, "")
|
|
|
|
cell = row.AddCell()
|
|
cell.Value = utils.ValueToString(val.Chargestartdate, "")
|
|
|
|
cell = row.AddCell()
|
|
cell.Value = utils.ValueToString(val.Chargeenddate, "")
|
|
|
|
cell = row.AddCell()
|
|
cell.Value = fmt.Sprintf("%0.0f", val.Chargedexpense)
|
|
|
|
cell = row.AddCell()
|
|
cell.Value = utils.ValueToString(val.Chargetime, "")
|
|
|
|
cell = row.AddCell()
|
|
cell.Value = utils.ValueToString(val.Chargeby, "")
|
|
|
|
}
|
|
name := utils.MakeOrderSn("台账")
|
|
filename = name + ".xlsx" //文件名
|
|
|
|
exceldir, _ := utils.GetCurrentPath("web/public/uploadxlsx") // 目录+文件名
|
|
excelfile = exceldir + "/" + filename
|
|
err = file.Save(excelfile)
|
|
if err != nil {
|
|
return "", "", err
|
|
}
|
|
return excelfile, filename, nil
|
|
}
|
|
|
|
//修改缴费记录
|
|
func (t *Chargetab) Update() error {
|
|
engine := db.MasterEngine()
|
|
//开启事务
|
|
session := engine.NewSession()
|
|
defer session.Close()
|
|
// add Begin() before any action
|
|
err := session.Begin()
|
|
if err != nil {
|
|
session.Rollback()
|
|
return err
|
|
}
|
|
//删除分摊记录
|
|
info := new(Chargeallocationtab)
|
|
_, err = session.Table("chargeallocationtab").Where("cid = ? and chargenr = ?", t.Cid, t.Chargenr).Delete(info)
|
|
if err != nil {
|
|
session.Rollback()
|
|
return err
|
|
}
|
|
//更新缴费记录
|
|
_, err = session.Table("chargetab").ID(core.PK{t.Cid, t.Chargenr}).Update(t)
|
|
if err != nil {
|
|
session.Rollback()
|
|
return err
|
|
}
|
|
|
|
//添加分摊记录
|
|
switch t.Chargetype {
|
|
case 1:
|
|
//查询合同编号
|
|
pro := new(Propertytab)
|
|
_, err := session.Table("propertytab").Where("cid = ? and propertyid =?", t.Cid, t.Propertyid).Get(pro)
|
|
if err != nil {
|
|
session.Rollback()
|
|
return err
|
|
}
|
|
//更新合同
|
|
m := new(Contracttab)
|
|
m.Begdate = t.Chargestartdate
|
|
m.Enddate = t.Chargeenddate
|
|
m.Chargedexpense = t.Chargedexpense
|
|
m.Chargableexpense = t.Chargedexpense
|
|
m.Createby = t.Chargeby
|
|
t1, err := utils.TimeParseyyyyMMdd(strings.Trim(t.Chargestartdate, ""))
|
|
t2, err := utils.TimeParseyyyyMMdd(strings.Trim(t.Chargeenddate, ""))
|
|
glog.InfoExt("更新合同", "开始日期", t1)
|
|
glog.InfoExt("更新合同", "结束日期", t2)
|
|
lengthdate := utils.SubMonth(t2, t1)
|
|
glog.InfoExt("更新合同", "缴费时长", lengthdate)
|
|
m.Lengthdate = lengthdate
|
|
_, err = session.Table("contracttab").ID(core.PK{t.Cid, pro.Contractid}).Update(m)
|
|
if err != nil {
|
|
session.Rollback()
|
|
return err
|
|
}
|
|
|
|
//物业费
|
|
result := ChargeShareTheMonth(session, t.Cid, t.Chargenr, t.Chargestartdate, t.Chargeenddate, t.Chargedexpense)
|
|
if !result {
|
|
session.Rollback()
|
|
return errors.New("分摊费用计算错误")
|
|
}
|
|
case 2:
|
|
|
|
//查询合同编号
|
|
pro := new(Accesscardtab)
|
|
_, err := session.Table("").Where("", t.Cid, t.Accesscardid).Get(pro)
|
|
if err != nil {
|
|
session.Rollback()
|
|
return err
|
|
}
|
|
//更新合同
|
|
m := new(Contracttab)
|
|
m.Begdate = t.Chargestartdate
|
|
m.Enddate = t.Chargeenddate
|
|
m.Chargedexpense = t.Chargedexpense
|
|
m.Chargableexpense = t.Chargedexpense
|
|
m.Createby = t.Chargeby
|
|
t1, err := utils.TimeParseyyyyMMdd(strings.Trim(t.Chargestartdate, ""))
|
|
t2, err := utils.TimeParseyyyyMMdd(strings.Trim(t.Chargeenddate, ""))
|
|
lengthdate := utils.SubMonth(t2, t1)
|
|
glog.InfoExt("更新合同", "缴费时长", lengthdate)
|
|
m.Lengthdate = lengthdate
|
|
_, err = session.Table("contracttab").ID(core.PK{t.Cid, pro.Contractid}).Update(m)
|
|
if err != nil {
|
|
session.Rollback()
|
|
return err
|
|
}
|
|
|
|
//电梯费
|
|
result := ChargeShareTheMonth(session, t.Cid, t.Chargenr, t.Chargestartdate, t.Chargeenddate, t.Chargedexpense)
|
|
if !result {
|
|
session.Rollback()
|
|
return errors.New("分摊费用计算错误")
|
|
}
|
|
case 3:
|
|
|
|
//查询合同编号
|
|
pro := new(Carporttab)
|
|
_, err := session.Table("").Where("", t.Cid, t.Carportid).Get(pro)
|
|
if err != nil {
|
|
session.Rollback()
|
|
return err
|
|
}
|
|
//更新合同
|
|
m := new(Contracttab)
|
|
m.Begdate = t.Chargestartdate
|
|
m.Enddate = t.Chargeenddate
|
|
m.Chargedexpense = t.Chargedexpense
|
|
m.Chargableexpense = t.Chargedexpense
|
|
m.Createby = t.Chargeby
|
|
t1, err := utils.TimeParseyyyyMMdd(strings.Trim(t.Chargestartdate, ""))
|
|
t2, err := utils.TimeParseyyyyMMdd(strings.Trim(t.Chargeenddate, ""))
|
|
lengthdate := utils.SubMonth(t2, t1)
|
|
glog.InfoExt("更新合同", "缴费时长", lengthdate)
|
|
m.Lengthdate = lengthdate
|
|
_, err = session.Table("contracttab").ID(core.PK{t.Cid, pro.Contractid}).Update(m)
|
|
if err != nil {
|
|
session.Rollback()
|
|
return err
|
|
}
|
|
|
|
//车位费
|
|
result := ChargeShareTheMonth(session, t.Cid, t.Chargenr, t.Chargestartdate, t.Chargeenddate, t.Chargedexpense)
|
|
if !result {
|
|
session.Rollback()
|
|
return errors.New("分摊费用计算错误")
|
|
}
|
|
default:
|
|
|
|
}
|
|
err = session.Commit()
|
|
if err != nil {
|
|
return err
|
|
}
|
|
return nil
|
|
}
|
|
|
|
//删除缴费记录
|
|
func (t *Chargetab) Del() error {
|
|
engine := db.MasterEngine()
|
|
//查询
|
|
info := new(Chargetab)
|
|
_, err := engine.Table("").Table("chargetab").ID(core.PK{t.Cid, t.Chargenr}).Get(info)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
|
|
//开启事务
|
|
session := engine.NewSession()
|
|
defer session.Close()
|
|
// add Begin() before any action
|
|
err = session.Begin()
|
|
if err != nil {
|
|
session.Rollback()
|
|
return err
|
|
}
|
|
|
|
switch info.Chargetype {
|
|
case 1:
|
|
|
|
//1.删除（判断数据是否是最后一条）
|
|
one := new(Chargetab)
|
|
ok, err := session.Table("").Table("chargetab").Where("cid =? and propertyid =? and chargeenddate > ?", t.Cid, info.Propertyid, info.Chargeenddate).Get(one)
|
|
if err != nil {
|
|
session.Rollback()
|
|
return err
|
|
}
|
|
if ok {
|
|
session.Rollback()
|
|
return errors.New("该记录不是最后一条!")
|
|
}
|
|
//删除记录
|
|
del := new(Chargetab)
|
|
_, err = session.Table("chargetab").ID(core.PK{t.Cid, t.Chargenr}).Delete(del)
|
|
if err != nil {
|
|
session.Rollback()
|
|
return err
|
|
}
|
|
|
|
//删除分摊记录
|
|
delInfo := new(Chargeallocationtab)
|
|
_, err = session.Table("chargeallocationtab").Where("cid = ? and chargenr = ?", t.Cid, t.Chargenr).Delete(delInfo)
|
|
if err != nil {
|
|
session.Rollback()
|
|
return err
|
|
}
|
|
//2.记录回溯
|
|
//查询上条记录信息
|
|
cinfo := new(Chargetab)
|
|
result, err := session.Table("chargetab").Where("cid =? and propertyid =? and chargeenddate < ?", t.Cid, info.Propertyid, info.Chargeenddate).Desc("chargeenddate").Get(cinfo)
|
|
if err != nil {
|
|
session.Rollback()
|
|
return err
|
|
}
|
|
//查询合同编号
|
|
pro := new(Propertytab)
|
|
_, err = session.Table("propertytab").Where("cid = ? and propertyid =?", t.Cid, info.Propertyid).Get(pro)
|
|
if err != nil {
|
|
session.Rollback()
|
|
return err
|
|
}
|
|
if result {
|
|
//更新合同
|
|
m := new(Contracttab)
|
|
m.Begdate = cinfo.Chargestartdate
|
|
m.Enddate = cinfo.Chargeenddate
|
|
m.Chargedexpense = cinfo.Chargedexpense
|
|
m.Chargableexpense = cinfo.Chargedexpense
|
|
m.Createby = cinfo.Chargeby
|
|
m.Chargetime = cinfo.Chargetime
|
|
t1, err := utils.TimeParseyyyyMMdd(strings.Trim(cinfo.Chargestartdate, ""))
|
|
t2, err := utils.TimeParseyyyyMMdd(strings.Trim(cinfo.Chargeenddate, ""))
|
|
t2 = t2.AddDate(0, 0, 1)
|
|
lengthdate := utils.SubMonth(t2, t1)
|
|
m.Lengthdate = lengthdate
|
|
_, err = session.Table("contracttab").ID(core.PK{t.Cid, pro.Contractid}).Update(m)
|
|
if err != nil {
|
|
session.Rollback()
|
|
return err
|
|
}
|
|
} else {
|
|
//删除合同
|
|
m := new(Contracttab)
|
|
_, err = session.Table("contracttab").ID(core.PK{t.Cid, pro.Contractid}).Delete(m)
|
|
|
|
up := new(Propertytab)
|
|
up.Contractid = ""
|
|
_, err = session.Table("propertytab").Cols("contractid").Where("cid = ? and propertyid =?", t.Cid, info.Propertyid).Update(up)
|
|
if err != nil {
|
|
session.Rollback()
|
|
return err
|
|
}
|
|
}
|
|
//3.添加删除日志 （删除数据以json串保存）
|
|
|
|
res, err := json.Marshal(info)
|
|
if err != nil {
|
|
session.Rollback()
|
|
return errors.New("json编码错误!")
|
|
}
|
|
data := new(RecordLog)
|
|
data.Cid = t.Cid
|
|
id, err := uuid.NewV1()
|
|
if err != nil {
|
|
session.Rollback()
|
|
return err
|
|
}
|
|
data.Id = id.String()
|
|
data.Createby = t.Lastmodifyby
|
|
data.Createtime = utils.TimeFormat(time.Now(), "yyyyMMddHHmmss")
|
|
data.Type = "del"
|
|
data.Chargetype = info.Chargetype
|
|
data.Chargeway = info.Chargeway
|
|
data.Chargeby = info.Chargeby
|
|
data.Chargeenddate = info.Chargeenddate
|
|
data.Chargedexpense = info.Chargedexpense
|
|
data.Accesscardid = info.Accesscardid
|
|
data.Propertyid = info.Propertyid
|
|
data.Chargestartdate = info.Chargestartdate
|
|
data.Carportid = info.Carportid
|
|
data.Chargetime = info.Chargetime
|
|
data.Content = string(res)
|
|
_, err = session.Table("record_log").Insert(data)
|
|
if err != nil {
|
|
session.Rollback()
|
|
return err
|
|
}
|
|
case 2:
|
|
//1.删除（判断数据是否是最后一条）
|
|
one := new(Chargetab)
|
|
ok, err := session.Table("").Table("chargetab").Where("chargetype = 2 and cid =? and accesscardid =? and chargeenddate > ?", t.Cid, info.Accesscardid, info.Chargeenddate).Get(one)
|
|
if err != nil {
|
|
session.Rollback()
|
|
return err
|
|
}
|
|
if ok {
|
|
session.Rollback()
|
|
return errors.New("该记录不是最后一条!")
|
|
}
|
|
//删除记录
|
|
del := new(Chargetab)
|
|
_, err = session.Table("chargetab").ID(core.PK{t.Cid, t.Chargenr}).Delete(del)
|
|
if err != nil {
|
|
session.Rollback()
|
|
return err
|
|
}
|
|
|
|
//删除分摊记录
|
|
delInfo := new(Chargeallocationtab)
|
|
_, err = session.Table("chargeallocationtab").Where("cid = ? and chargenr = ?", t.Cid, t.Chargenr).Delete(delInfo)
|
|
if err != nil {
|
|
session.Rollback()
|
|
return err
|
|
}
|
|
//2.记录回溯
|
|
//查询上条记录信息
|
|
cinfo := new(Chargetab)
|
|
result, err := session.Table("chargetab").Where("chargetype = 2 and cid =? and accesscardid =? and chargeenddate < ?", t.Cid, info.Accesscardid, info.Chargeenddate).Desc("chargeenddate").Get(cinfo)
|
|
if err != nil {
|
|
session.Rollback()
|
|
return err
|
|
}
|
|
//查询合同编号
|
|
pro := new(Accesscardtab)
|
|
_, err = session.Table("accesscardtab").Where("", t.Cid, info.Accesscardid).Get(pro)
|
|
if err != nil {
|
|
session.Rollback()
|
|
return err
|
|
}
|
|
if result {
|
|
//更新合同
|
|
m := new(Contracttab)
|
|
m.Begdate = cinfo.Chargestartdate
|
|
m.Enddate = cinfo.Chargeenddate
|
|
m.Chargedexpense = cinfo.Chargedexpense
|
|
m.Chargableexpense = cinfo.Chargedexpense
|
|
m.Createby = cinfo.Chargeby
|
|
m.Chargetime = cinfo.Chargetime
|
|
t1, err := utils.TimeParseyyyyMMdd(strings.Trim(cinfo.Chargestartdate, ""))
|
|
t2, err := utils.TimeParseyyyyMMdd(strings.Trim(cinfo.Chargeenddate, ""))
|
|
lengthdate := utils.SubMonth(t2, t1)
|
|
m.Lengthdate = lengthdate
|
|
_, err = session.Table("contracttab").ID(core.PK{t.Cid, pro.Contractid}).Update(m)
|
|
if err != nil {
|
|
session.Rollback()
|
|
return err
|
|
}
|
|
} else {
|
|
//删除合同
|
|
m := new(Contracttab)
|
|
_, err = session.Table("contracttab").ID(core.PK{t.Cid, pro.Contractid}).Delete(m)
|
|
|
|
up := new(Propertytab)
|
|
up.Contractid = ""
|
|
_, err = session.Table("accesscardtab").Cols("contractid").Where("cid = ? and propertyid =?", t.Cid, info.Propertyid).Update(up)
|
|
if err != nil {
|
|
session.Rollback()
|
|
return err
|
|
}
|
|
}
|
|
//3.添加删除日志 （删除数据以json串保存）
|
|
|
|
res, err := json.Marshal(info)
|
|
if err != nil {
|
|
session.Rollback()
|
|
return errors.New("json编码错误!")
|
|
}
|
|
data := new(RecordLog)
|
|
data.Cid = t.Cid
|
|
id, err := uuid.NewV1()
|
|
if err != nil {
|
|
session.Rollback()
|
|
return err
|
|
}
|
|
data.Id = id.String()
|
|
data.Createby = t.Lastmodifyby
|
|
data.Createtime = utils.TimeFormat(time.Now(), "yyyyMMddHHmmss")
|
|
data.Type = "del"
|
|
data.Chargetype = info.Chargetype
|
|
data.Chargeway = info.Chargeway
|
|
data.Chargeby = info.Chargeby
|
|
data.Chargeenddate = info.Chargeenddate
|
|
data.Chargedexpense = info.Chargedexpense
|
|
data.Accesscardid = info.Accesscardid
|
|
data.Propertyid = info.Propertyid
|
|
data.Chargestartdate = info.Chargestartdate
|
|
data.Carportid = info.Carportid
|
|
data.Chargetime = info.Chargetime
|
|
data.Content = string(res)
|
|
_, err = session.Table("record_log").Insert(data)
|
|
if err != nil {
|
|
session.Rollback()
|
|
return err
|
|
}
|
|
case 3:
|
|
|
|
//1.删除（判断数据是否是最后一条）
|
|
one := new(Chargetab)
|
|
ok, err := session.Table("").Table("chargetab").Where("cid =? and carportid =? and chargeenddate > ?", t.Cid, info.Carportid, info.Chargeenddate).Get(one)
|
|
if err != nil {
|
|
session.Rollback()
|
|
return err
|
|
}
|
|
if ok {
|
|
session.Rollback()
|
|
return errors.New("该记录不是最后一条!")
|
|
}
|
|
//删除记录
|
|
del := new(Chargetab)
|
|
_, err = session.Table("chargetab").ID(core.PK{t.Cid, t.Chargenr}).Delete(del)
|
|
if err != nil {
|
|
session.Rollback()
|
|
return err
|
|
}
|
|
|
|
//删除分摊记录
|
|
delInfo := new(Chargeallocationtab)
|
|
_, err = session.Table("chargeallocationtab").Where("cid = ? and chargenr = ?", t.Cid, t.Chargenr).Delete(delInfo)
|
|
if err != nil {
|
|
session.Rollback()
|
|
return err
|
|
}
|
|
//2.记录回溯
|
|
//查询上条记录信息
|
|
cinfo := new(Chargetab)
|
|
result, err := session.Table("chargetab").Where("cid =? and carportid =? and chargeenddate < ?", t.Cid, info.Carportid, info.Chargeenddate).Desc("chargeenddate").Get(cinfo)
|
|
if err != nil {
|
|
session.Rollback()
|
|
return err
|
|
}
|
|
//查询合同编号
|
|
pro := new(Carporttab)
|
|
_, err = session.Table("carporttab").Where("", t.Cid, info.Carportid).Get(pro)
|
|
if err != nil {
|
|
session.Rollback()
|
|
return err
|
|
}
|
|
if result {
|
|
//更新合同
|
|
m := new(Contracttab)
|
|
m.Begdate = cinfo.Chargestartdate
|
|
m.Enddate = cinfo.Chargeenddate
|
|
m.Chargedexpense = cinfo.Chargedexpense
|
|
m.Chargableexpense = cinfo.Chargedexpense
|
|
m.Createby = cinfo.Chargeby
|
|
m.Chargetime = cinfo.Chargetime
|
|
t1, err := utils.TimeParseyyyyMMdd(strings.Trim(cinfo.Chargestartdate, ""))
|
|
t2, err := utils.TimeParseyyyyMMdd(strings.Trim(cinfo.Chargeenddate, ""))
|
|
lengthdate := utils.SubMonth(t2, t1)
|
|
m.Lengthdate = lengthdate
|
|
_, err = session.Table("contracttab").ID(core.PK{t.Cid, pro.Contractid}).Update(m)
|
|
if err != nil {
|
|
session.Rollback()
|
|
return err
|
|
}
|
|
} else {
|
|
//删除合同
|
|
m := new(Contracttab)
|
|
_, err = session.Table("contracttab").ID(core.PK{t.Cid, pro.Contractid}).Delete(m)
|
|
|
|
up := new(Propertytab)
|
|
up.Contractid = ""
|
|
_, err = session.Table("carporttab").Cols("contractid").Where("cid = ? and carportid =?", t.Cid, info.Carportid).Update(up)
|
|
if err != nil {
|
|
session.Rollback()
|
|
return err
|
|
}
|
|
}
|
|
//3.添加删除日志 （删除数据以json串保存）
|
|
|
|
res, err := json.Marshal(info)
|
|
if err != nil {
|
|
session.Rollback()
|
|
return errors.New("json编码错误!")
|
|
}
|
|
data := new(RecordLog)
|
|
data.Cid = t.Cid
|
|
id, err := uuid.NewV1()
|
|
if err != nil {
|
|
session.Rollback()
|
|
return err
|
|
}
|
|
data.Id = id.String()
|
|
data.Createby = t.Lastmodifyby
|
|
data.Createtime = utils.TimeFormat(time.Now(), "yyyyMMddHHmmss")
|
|
data.Type = "del"
|
|
data.Chargetype = info.Chargetype
|
|
data.Chargeway = info.Chargeway
|
|
data.Chargeby = info.Chargeby
|
|
data.Chargeenddate = info.Chargeenddate
|
|
data.Chargedexpense = info.Chargedexpense
|
|
data.Accesscardid = info.Accesscardid
|
|
data.Propertyid = info.Propertyid
|
|
data.Chargestartdate = info.Chargestartdate
|
|
data.Carportid = info.Carportid
|
|
data.Chargetime = info.Chargetime
|
|
data.Content = string(res)
|
|
_, err = session.Table("record_log").Insert(data)
|
|
if err != nil {
|
|
session.Rollback()
|
|
return err
|
|
}
|
|
|
|
//更新合同
|
|
m := new(Contracttab)
|
|
m.Begdate = cinfo.Chargestartdate
|
|
m.Enddate = cinfo.Chargeenddate
|
|
m.Chargedexpense = cinfo.Chargedexpense
|
|
m.Chargableexpense = cinfo.Chargedexpense
|
|
m.Createby = cinfo.Chargeby
|
|
m.Chargetime = cinfo.Chargetime
|
|
t1, err := utils.TimeParseyyyyMMdd(strings.Trim(cinfo.Chargestartdate, ""))
|
|
t2, err := utils.TimeParseyyyyMMdd(strings.Trim(cinfo.Chargeenddate, ""))
|
|
lengthdate := utils.SubMonth(t2, t1)
|
|
m.Lengthdate = lengthdate
|
|
_, err = session.Table("contracttab").ID(core.PK{t.Cid, pro.Contractid}).Update(m)
|
|
if err != nil {
|
|
session.Rollback()
|
|
return err
|
|
}
|
|
case 4:
|
|
|
|
//删除记录
|
|
del := new(Chargetab)
|
|
_, err = session.Table("chargetab").ID(core.PK{t.Cid, t.Chargenr}).Delete(del)
|
|
if err != nil {
|
|
session.Rollback()
|
|
return err
|
|
}
|
|
|
|
//删除分摊记录
|
|
delInfo := new(Chargeallocationtab)
|
|
_, err = session.Table("chargeallocationtab").Where("cid = ? and chargenr = ?", t.Cid, t.Chargenr).Delete(delInfo)
|
|
if err != nil {
|
|
session.Rollback()
|
|
return err
|
|
}
|
|
//删除缴费信息
|
|
_, err := session.Where("cid = ? and serialnumber = ?", t.Cid, t.Serialnumber).Delete(&Otherchargetab{})
|
|
if err != nil {
|
|
session.Rollback()
|
|
return err
|
|
}
|
|
|
|
//3.添加删除日志 （删除数据以json串保存）
|
|
res, err := json.Marshal(info)
|
|
if err != nil {
|
|
session.Rollback()
|
|
return errors.New("json编码错误!")
|
|
}
|
|
data := new(RecordLog)
|
|
data.Cid = t.Cid
|
|
id, err := uuid.NewV1()
|
|
if err != nil {
|
|
session.Rollback()
|
|
return err
|
|
}
|
|
data.Id = id.String()
|
|
data.Createby = t.Lastmodifyby
|
|
data.Createtime = utils.TimeFormat(time.Now(), "yyyyMMddHHmmss")
|
|
data.Type = "del"
|
|
data.Chargetype = info.Chargetype
|
|
data.Chargeway = info.Chargeway
|
|
data.Chargeby = info.Chargeby
|
|
data.Chargeenddate = info.Chargeenddate
|
|
data.Chargedexpense = info.Chargedexpense
|
|
data.Accesscardid = info.Accesscardid
|
|
data.Propertyid = info.Propertyid
|
|
data.Chargestartdate = info.Chargestartdate
|
|
data.Carportid = info.Carportid
|
|
data.Chargetime = info.Chargetime
|
|
data.Content = string(res)
|
|
_, err = session.Table("record_log").Insert(data)
|
|
if err != nil {
|
|
session.Rollback()
|
|
return err
|
|
}
|
|
|
|
default:
|
|
|
|
}
|
|
err = session.Commit()
|
|
if err != nil {
|
|
return err
|
|
}
|
|
return nil
|
|
}
|
|
|
|
// 获取缴费方式数据列表
|
|
func (t *Chargetab) GetChargeWayDataList(cid int, chargeWay, chargeType, start, end string, pageIndex, pageSize int) (result *ChargeTypeResponse, err error) {
|
|
result = new(ChargeTypeResponse)
|
|
engine := db.MasterEngine()
|
|
var court Courttab
|
|
exist, err := engine.Table(court.TableName()).ID(cid).Get(&court)
|
|
if err != nil {
|
|
return result, err
|
|
}
|
|
if !exist {
|
|
return result, errors.New("data not exist")
|
|
}
|
|
var totalMoney float64
|
|
// 扫码包含 微信 支付宝 和 扫码
|
|
query := engine.Table(t.TableName()).Where(`cid = ?`, cid)
|
|
table := engine.Table(t.TableName()).Where(`cid = ?`, cid)
|
|
sumQuery := engine.Table(t.TableName()).Where(`cid = ?`, cid)
|
|
fmt.Println("chargeWay:", chargeWay)
|
|
if chargeWay != "" {
|
|
if chargeWay == "扫码" {
|
|
query = query.And("chargeway = \"扫码\" or chargeway = \"微信\" or chargeway = \"支付宝\"")
|
|
table = table.And("chargeway = \"扫码\" or chargeway = \"微信\" or chargeway = \"支付宝\"")
|
|
sumQuery = sumQuery.And("chargeway = \"扫码\" or chargeway = \"微信\" or chargeway = \"支付宝\"")
|
|
} else {
|
|
query = query.And("chargeway = ?", chargeWay)
|
|
table = table.And("chargeway = ?", chargeWay)
|
|
sumQuery = sumQuery.And("chargeway = ?", chargeWay)
|
|
}
|
|
}
|
|
if chargeType != "" {
|
|
query = query.And("chargetype = ?", chargeType)
|
|
table = table.And("chargetype = ?", chargeType)
|
|
sumQuery = sumQuery.And("chargetype = ?", chargeType)
|
|
}
|
|
if start != "" {
|
|
query = query.And("chargetime >= ? and chargetime <= ?", start, end)
|
|
table = table.And("chargetime >= ? and chargetime <= ?", start, end)
|
|
sumQuery = sumQuery.And("chargetime >= ? and chargetime <= ?", start, end)
|
|
}
|
|
totalMoney, err = sumQuery.Sum(t, "chargableexpense")
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
count, err := table.Count()
|
|
|
|
offset := (pageIndex - 1) * pageSize
|
|
var chargeLi []*Chargetab
|
|
err = query.Desc("chargetab.createtime").Limit(pageSize, offset).Find(&chargeLi)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
result.Cid = cid
|
|
result.Court = court.Descr
|
|
result.ChargeCount = utils.Round(totalMoney)
|
|
result.ChargeWay = chargeWay
|
|
result.Items = chargeLi
|
|
result.Total = count
|
|
result.TotalPage = int(math.Ceil(float64(result.Total) / float64(pageSize)))
|
|
result.CurrentPage = pageIndex
|
|
return result, err
|
|
}
|
|
|
|
// 缴费台账接口
|
|
func (t *Chargetab) GetChargeArrearageData(cid int, pageIndex, pageSize int, year int, arrearageDate string, chargeType int, buildingId string, propertyId string, start string, end string, currentYear int) (result *ChargeArrearageResponse, err error) {
|
|
result = new(ChargeArrearageResponse)
|
|
engine := db.MasterEngine()
|
|
var court Courttab
|
|
exist, err := engine.Table(court.TableName()).ID(cid).Get(&court)
|
|
if err != nil {
|
|
return result, err
|
|
}
|
|
if !exist {
|
|
return result, errors.New("data not exist")
|
|
}
|
|
var chargeLi []*Chargetab
|
|
|
|
query := engine.Table(t.TableName()).Where("chargetab.cid = ?", cid)
|
|
table := engine.Table(t.TableName()).Where("chargetab.cid = ?", cid)
|
|
// 若搜索楼栋id则与物业表连表
|
|
if !utils.ValueIsEmpty(buildingId) {
|
|
query = query.Join("LEFT", "propertytab", "propertytab.propertyid = chargetab.propertyid and propertytab.cid = chargetab.cid").And("propertytab.buildingid = ?", buildingId)
|
|
table = table.Join("LEFT", "propertytab", "propertytab.propertyid = chargetab.propertyid and propertytab.cid = chargetab.cid").And("propertytab.buildingid = ?", buildingId)
|
|
}
|
|
if chargeType != 0 {
|
|
query = query.And("chargetab.chargetype = ?", chargeType)
|
|
table = table.And("chargetab.chargetype = ?", chargeType)
|
|
}
|
|
if !utils.ValueIsEmpty(propertyId) {
|
|
query = query.And("chargetab.propertyid = ?", propertyId)
|
|
table = table.And("chargetab.propertyid = ?", propertyId)
|
|
}
|
|
if start != "" {
|
|
query = query.And("chargetab.chargetime >= ? and chargetab.chargetime <= ?", start, end)
|
|
table = table.And("chargetab.chargetime >= ? and chargetab.chargetime <= ?", start, end)
|
|
} else {
|
|
if year != 0 {
|
|
start := fmt.Sprintf("%d-01-01", year)
|
|
end := fmt.Sprintf("%d-01-01", year+1)
|
|
query = query.And("chargetab.chargetime >= ? and chargetab.chargetime < ?", start, end)
|
|
table = table.And("chargetab.chargetime >= ? and chargetab.chargetime < ?", start, end)
|
|
}
|
|
}
|
|
offset := pageSize * (pageIndex - 1)
|
|
err = query.Desc("chargetab.createtime").Limit(pageSize, offset).Find(&chargeLi)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
count, err := table.Count()
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
// 在chargetab上拼接两个字段 一个陈欠日期 一个陈欠金额
|
|
var chargeArrearageLi []*ChargeArrearage
|
|
for _, charge := range chargeLi {
|
|
var chargeallocation Chargeallocationtab
|
|
var chargeArrearage ChargeArrearage
|
|
chargeArrearage.Chargetab = charge
|
|
chargeCount, err := engine.Table(chargeallocation.TableName()).Where("cid = ? and chargenr = ? and allocateyear < ?", cid, charge.Chargenr, currentYear).Sum(chargeallocation, "allocateexpense")
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
chargeArrearage.Arrearage = utils.Round(chargeCount)
|
|
chargeArrearage.ArrearageDate = arrearageDate
|
|
chargeArrearageLi = append(chargeArrearageLi, &chargeArrearage)
|
|
}
|
|
result.Cid = cid
|
|
result.Court = court.Descr
|
|
result.Items = chargeArrearageLi
|
|
result.CurrentPage = pageIndex
|
|
result.Total = count
|
|
result.TotalPage = int(math.Ceil(float64(count) / float64(pageSize)))
|
|
return result, nil
|
|
}
|
|
|
|
// 缴费台账金额统计接口
|
|
func (t *Chargetab) GetChargeStatisticData(cid, year int, selectType string, chargeType int, buildingId string, propertyId string, start string, end string, currentYear int) (result *response.ChargeStatisticResponse, err error) {
|
|
result = new(response.ChargeStatisticResponse)
|
|
engine := db.MasterEngine()
|
|
var query *xorm.Session
|
|
var chargeAllocation Chargeallocationtab
|
|
if chargeType == 0 && buildingId == "" && start == "" && end == "" && year == 0 {
|
|
query = engine.Table(chargeAllocation.TableName()).Where("cid = ?", cid)
|
|
|
|
} else {
|
|
chargeLi := make([]Chargetab, 0)
|
|
query = engine.Table(t.TableName()).Where("chargetab.cid = ?", cid)
|
|
// 若搜索楼栋id则与物业表连表
|
|
if !utils.ValueIsEmpty(buildingId) {
|
|
query = query.Join("LEFT", "propertytab", "propertytab.propertyid = chargetab.propertyid and propertytab.cid = chargetab.cid").And("propertytab.buildingid = ?", buildingId)
|
|
}
|
|
if chargeType != 0 {
|
|
query = query.And("chargetab.chargetype = ?", chargeType)
|
|
}
|
|
if !utils.ValueIsEmpty(propertyId) {
|
|
query = query.And("chargetab.propertyid = ?", propertyId)
|
|
}
|
|
if start != "" {
|
|
query = query.And("chargetab.chargetime >= ? and chargetab.chargetime <= ?", start, end)
|
|
} else {
|
|
if year != 0 {
|
|
start := fmt.Sprintf("%d-01-01", year)
|
|
end := fmt.Sprintf("%d-01-01", year+1)
|
|
query = query.And("chargetab.chargetime >= ? and chargetab.chargetime < ?", start, end)
|
|
}
|
|
}
|
|
err = query.Find(&chargeLi)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
chargeNrLi := make([]int, 0)
|
|
for _, charge := range chargeLi {
|
|
chargeNrLi = append(chargeNrLi, charge.Chargenr)
|
|
}
|
|
query = engine.Table(chargeAllocation.TableName()).Where("cid = ?", cid).In("chargenr", chargeNrLi)
|
|
}
|
|
|
|
// arrearage 陈欠 current 当年
|
|
if selectType == "arrearage" {
|
|
query = query.Where("chargeallocationtab.allocateyear < ?", currentYear)
|
|
} else if selectType == "current" {
|
|
query = query.Where("chargeallocationtab.allocateyear >= ?", currentYear)
|
|
}
|
|
|
|
count, err := query.Sum(chargeAllocation, "chargeallocationtab.allocateexpense")
|
|
result.Cid = cid
|
|
result.Count = utils.Round(count)
|
|
result.Type = selectType
|
|
return result, nil
|
|
}
|
|
|
|
func (t *Chargetab) GetChargeArrearageExcelData(cid int, year int, arrearageDate string, chargeType int, buildingId string, propertyId string, start string, end string, currentYear int) (result []*ChargeArrearageExcelData, err error) {
|
|
engine := db.MasterEngine()
|
|
chargeLi := make([]*ChargeContactData, 0)
|
|
|
|
query := engine.Table(t.TableName()).Where("chargetab.cid = ?", cid).Join("LEFT", "propertytab", "propertytab.propertyid = chargetab.propertyid and propertytab.cid = chargetab.cid")
|
|
// 若搜索楼栋id则与物业表连表
|
|
if !utils.ValueIsEmpty(buildingId) {
|
|
query = query.And("propertytab.buildingid = ?", buildingId)
|
|
}
|
|
if chargeType != 0 {
|
|
query = query.And("chargetab.chargetype = ?", chargeType)
|
|
}
|
|
if !utils.ValueIsEmpty(propertyId) {
|
|
query = query.And("chargetab.propertyid = ?", propertyId)
|
|
}
|
|
if start != "" {
|
|
query = query.And("chargetab.chargetime >= ? and chargetab.chargetime <= ?", start, end)
|
|
} else {
|
|
if year != 0 {
|
|
start := fmt.Sprintf("%d-01-01", year)
|
|
end := fmt.Sprintf("%d-01-01", year+1)
|
|
query = query.And("chargetab.chargetime >= ? and chargetab.chargetime < ?", start, end)
|
|
}
|
|
}
|
|
err = query.Desc("chargetab.createtime").Find(&chargeLi)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
// 在chargetab上拼接两个字段 一个陈欠日期 一个陈欠金额
|
|
result = make([]*ChargeArrearageExcelData, 0)
|
|
for _, charge := range chargeLi {
|
|
var chargeallocation Chargeallocationtab
|
|
var chargeArrearageExcel ChargeArrearageExcelData
|
|
chargeArrearageExcel.ChargeContactData = charge
|
|
chargeCount, err := engine.Table(chargeallocation.TableName()).Where("cid = ? and chargenr = ? and allocateyear < ?", cid, charge.Chargenr, currentYear).Sum(chargeallocation, "allocateexpense")
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
chargeArrearageExcel.Arrearage = utils.Round(chargeCount)
|
|
chargeArrearageExcel.ArrearageDate = arrearageDate
|
|
if chargeArrearageExcel.Chargetab.Chargetype == 1 {
|
|
chargeArrearageExcel.ChargeTypeStr = "物业费"
|
|
} else if chargeArrearageExcel.Chargetab.Chargetype == 2 {
|
|
chargeArrearageExcel.ChargeTypeStr = "电梯卡费"
|
|
} else if chargeArrearageExcel.Chargetab.Chargetype == 3 {
|
|
chargeArrearageExcel.ChargeTypeStr = "车位费"
|
|
}
|
|
result = append(result, &chargeArrearageExcel)
|
|
}
|
|
return result, nil
|
|
}
|
|
|
|
// 获取缴费方式数据列表
|
|
func (t *Chargetab) GetExcelChargeWayDataList(cid int, chargeWay, chargeType, start, end string) (result []*ExcelChargeWayData, err error) {
|
|
result = make([]*ExcelChargeWayData, 0)
|
|
tempLi := make([]*ChargeContactData, 0)
|
|
engine := db.MasterEngine()
|
|
// 扫码包含 微信 支付宝 和 扫码
|
|
query := engine.Table(t.TableName()).Where(`chargetab.cid = ?`, cid).Join("LEFT", "propertytab", "propertytab.propertyid = chargetab.propertyid and propertytab.cid = chargetab.cid")
|
|
fmt.Println("chargeWay:", chargeWay)
|
|
if chargeWay != "" {
|
|
if chargeWay == "扫码" {
|
|
query = query.And("chargetab.chargeway = \"扫码\" or chargetab.chargeway = \"微信\" or chargetab.chargeway = \"支付宝\"")
|
|
} else {
|
|
query = query.And("chargetab.chargeway = ?", chargeWay)
|
|
}
|
|
}
|
|
if chargeType != "" {
|
|
query = query.And("chargetab.chargetype = ?", chargeType)
|
|
}
|
|
if start != "" {
|
|
query = query.And("chargetab.chargetime >= ? and chargetab.chargetime <= ?", start, end)
|
|
}
|
|
err = query.Desc("chargetab.createtime").Find(&tempLi)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
for _, charge := range tempLi {
|
|
var excelChargeData ExcelChargeWayData
|
|
excelChargeData.ChargeContactData = new(ChargeContactData)
|
|
excelChargeData.Chargetab = charge.Chargetab
|
|
excelChargeData.Propertytab = charge.Propertytab
|
|
if excelChargeData.Chargetab.Chargetype == 1 {
|
|
excelChargeData.ChargeTypeStr = "物业费"
|
|
} else if excelChargeData.Chargetab.Chargetype == 2 {
|
|
excelChargeData.ChargeTypeStr = "电梯卡费"
|
|
} else if excelChargeData.Chargetab.Chargetype == 3 {
|
|
excelChargeData.ChargeTypeStr = "车位费"
|
|
}
|
|
result = append(result, &excelChargeData)
|
|
}
|
|
return result, err
|
|
}
|
|
|
|
func (t *Chargetab) ExportChargeArrearageExcel(datalist []*ChargeArrearageExcelData) (excelfile string, filename string, err error) {
|
|
var file *xlsx.File
|
|
var sheet *xlsx.Sheet
|
|
var row *xlsx.Row
|
|
var cell *xlsx.Cell
|
|
|
|
file = xlsx.NewFile()
|
|
sheet, err = file.AddSheet("缴费台账")
|
|
if err != nil {
|
|
return "", "", err
|
|
}
|
|
|
|
tableHead := true
|
|
fieldList := []string{
|
|
"项目号",
|
|
"缴费类型",
|
|
"物业编号",
|
|
"业主",
|
|
"收费起始日期",
|
|
"收费结束日期",
|
|
"收费方式",
|
|
"应收费用",
|
|
"实收费用",
|
|
"收费时间",
|
|
"陈欠金额",
|
|
"陈欠日期",
|
|
}
|
|
var courtName string
|
|
for _, val := range datalist {
|
|
if tableHead {
|
|
//添加表头
|
|
row = sheet.AddRow()
|
|
for _, field := range fieldList {
|
|
cell = row.AddCell()
|
|
cell.Value = field
|
|
}
|
|
tableHead = false
|
|
//continue
|
|
}
|
|
row = sheet.AddRow()
|
|
if courtName == "" {
|
|
engine := db.MasterEngine()
|
|
var court Courttab
|
|
exist, err := engine.Table(court.TableName()).ID(val.Chargetab.Cid).Get(&court)
|
|
if err != nil {
|
|
return "", "", err
|
|
}
|
|
if !exist {
|
|
return "", "", errors.New("data not exist")
|
|
}
|
|
courtName = court.Descr
|
|
}
|
|
cell = row.AddCell()
|
|
cell.Value = courtName
|
|
|
|
cell = row.AddCell()
|
|
cell.Value = utils.ValueToString(val.ChargeTypeStr, "")
|
|
|
|
cell = row.AddCell()
|
|
cell.Value = utils.ValueToString(val.Chargetab.Propertyid, "")
|
|
|
|
cell = row.AddCell()
|
|
cell.Value = utils.ValueToString(val.Propertytab.Contact, "")
|
|
|
|
cell = row.AddCell()
|
|
cell.Value = utils.ValueToString(val.Chargestartdate, "")
|
|
|
|
cell = row.AddCell()
|
|
cell.Value = utils.ValueToString(val.Chargeenddate, "")
|
|
|
|
cell = row.AddCell()
|
|
cell.Value = utils.ValueToString(val.Chargetab.Chargeway, "")
|
|
|
|
cell = row.AddCell()
|
|
cell.Value = fmt.Sprintf("%0.0f", val.Chargetab.Chargableexpense)
|
|
|
|
cell = row.AddCell()
|
|
cell.Value = fmt.Sprintf("%0.0f", val.Chargetab.Chargedexpense)
|
|
|
|
cell = row.AddCell()
|
|
cell.Value = utils.ValueToString(val.Chargetab.Chargetime, "")
|
|
|
|
cell = row.AddCell()
|
|
cell.Value = fmt.Sprintf("%d", val.Arrearage)
|
|
|
|
cell = row.AddCell()
|
|
cell.Value = utils.ValueToString(val.ArrearageDate, "")
|
|
|
|
}
|
|
name := utils.MakeOrderSn("缴费台账")
|
|
filename = name + ".xlsx" //文件名
|
|
|
|
exceldir, _ := utils.GetCurrentPath("web/public/uploadxlsx") // 目录+文件名
|
|
excelfile = exceldir + "/" + filename
|
|
err = file.Save(excelfile)
|
|
if err != nil {
|
|
return "", "", err
|
|
}
|
|
return excelfile, filename, nil
|
|
}
|
|
|
|
func (t *Chargetab) ExportChargeWayExcel(datalist []*ExcelChargeWayData) (excelfile string, filename string, err error) {
|
|
var file *xlsx.File
|
|
var sheet *xlsx.Sheet
|
|
var row *xlsx.Row
|
|
var cell *xlsx.Cell
|
|
|
|
file = xlsx.NewFile()
|
|
sheet, err = file.AddSheet("缴费台账")
|
|
if err != nil {
|
|
return "", "", err
|
|
}
|
|
|
|
tableHead := true
|
|
fieldList := []string{
|
|
"项目号",
|
|
"业主",
|
|
"缴费类型",
|
|
"物业编号",
|
|
"收费起始日期",
|
|
"收费结束日期",
|
|
"收费方式",
|
|
"应收费用",
|
|
"实收费用",
|
|
"收费时间",
|
|
}
|
|
var courtName string
|
|
for _, val := range datalist {
|
|
if tableHead {
|
|
//添加表头
|
|
row = sheet.AddRow()
|
|
for _, field := range fieldList {
|
|
cell = row.AddCell()
|
|
cell.Value = field
|
|
}
|
|
tableHead = false
|
|
//continue
|
|
}
|
|
row = sheet.AddRow()
|
|
if courtName == "" {
|
|
engine := db.MasterEngine()
|
|
var court Courttab
|
|
exist, err := engine.Table(court.TableName()).ID(val.Chargetab.Cid).Get(&court)
|
|
if err != nil {
|
|
return "", "", err
|
|
}
|
|
if !exist {
|
|
return "", "", errors.New("data not exist")
|
|
}
|
|
courtName = court.Descr
|
|
}
|
|
cell = row.AddCell()
|
|
cell.Value = courtName
|
|
|
|
cell = row.AddCell()
|
|
cell.Value = utils.ValueToString(val.Propertytab.Contact, "")
|
|
|
|
cell = row.AddCell()
|
|
cell.Value = utils.ValueToString(val.ChargeTypeStr, "")
|
|
|
|
cell = row.AddCell()
|
|
cell.Value = utils.ValueToString(val.Chargetab.Propertyid, "")
|
|
|
|
cell = row.AddCell()
|
|
cell.Value = utils.ValueToString(val.Chargestartdate, "")
|
|
|
|
cell = row.AddCell()
|
|
cell.Value = utils.ValueToString(val.Chargeenddate, "")
|
|
|
|
cell = row.AddCell()
|
|
cell.Value = utils.ValueToString(val.Chargeway, "")
|
|
|
|
cell = row.AddCell()
|
|
cell.Value = fmt.Sprintf("%0.0f", val.Chargableexpense)
|
|
|
|
cell = row.AddCell()
|
|
cell.Value = fmt.Sprintf("%0.0f", val.Chargedexpense)
|
|
|
|
cell = row.AddCell()
|
|
cell.Value = utils.ValueToString(val.Chargetime, "")
|
|
|
|
cell = row.AddCell()
|
|
}
|
|
name := utils.MakeOrderSn("缴费台账")
|
|
filename = name + ".xlsx" //文件名
|
|
|
|
exceldir, _ := utils.GetCurrentPath("web/public/uploadxlsx") // 目录+文件名
|
|
excelfile = exceldir + "/" + filename
|
|
fmt.Println("excelfile:", excelfile)
|
|
err = file.Save(excelfile)
|
|
if err != nil {
|
|
return "", "", err
|
|
}
|
|
return excelfile, filename, nil
|
|
}
|
|
|
|
//按年统计
|
|
func (t *Chargetab) ChargeTotalStatisticData(year int, selectType string) ([]SumStruct, int, error) {
|
|
e := db.MasterEngine()
|
|
datalist := make([]SumStruct, 0)
|
|
currentYear := time.Now().Year()
|
|
startDate := fmt.Sprintf("%d-01-01", year)
|
|
endDate := fmt.Sprintf("%d-12-31", year)
|
|
var count float64
|
|
var totalMoney float64
|
|
var err error
|
|
//计算实收金额
|
|
var charge Chargetab
|
|
totalMoney, err = e.Table(t.TableName()).Where("cid = ? and chargetime >= ? and chargetime <= ?", t.Cid, startDate, endDate).Sum(charge, "chargableexpense")
|
|
if err != nil {
|
|
glog.InfoExtln("年缴费测试", "err0 := ", err)
|
|
return nil, 0, err
|
|
}
|
|
tempChargeLi := make([]Chargetab, 0)
|
|
err = e.Table(t.TableName()).Where("cid = ? and chargetime >= ? and chargetime <= ? and chargestartdate < ?", t.Cid, startDate, endDate, startDate).Asc("chargestartdate").Limit(1, 0).Find(&tempChargeLi)
|
|
if err != nil {
|
|
return nil, 0, err
|
|
}
|
|
fmt.Println("tempChargeLi:", tempChargeLi)
|
|
var arrearageYears int
|
|
if len(tempChargeLi) <= 0 || tempChargeLi[0].Chargestartdate == "" {
|
|
arrearageYears = 0
|
|
} else {
|
|
date := tempChargeLi[0].Chargestartdate
|
|
dateYear, err := strconv.Atoi(strings.Split(date, "-")[0])
|
|
if err != nil {
|
|
arrearageYears = 0
|
|
}
|
|
arrearageYears = currentYear - dateYear
|
|
}
|
|
fmt.Println("arrearageYears:", arrearageYears)
|
|
chargenrLi := make([]int, 0)
|
|
err = e.Table(t.TableName()).Where("cid = ? and chargetime >= ? and chargetime <= ?", t.Cid, startDate, endDate).Cols("chargenr").Find(&chargenrLi)
|
|
if err != nil {
|
|
return nil, 0, err
|
|
}
|
|
var chargeallocation Chargeallocationtab
|
|
arrearageMoney, err := e.Table(chargeallocation.TableName()).Where("cid = ?", t.Cid).In("chargenr", chargenrLi).And("allocateyear < ?", currentYear).Sum(chargeallocation, "allocateexpense")
|
|
if err != nil {
|
|
return nil, 0, err
|
|
}
|
|
if selectType == "current" {
|
|
count = totalMoney - arrearageMoney
|
|
} else if selectType == "arrearage" {
|
|
count = arrearageMoney
|
|
} else {
|
|
count = totalMoney
|
|
}
|
|
//计算实收缴费面积
|
|
one := SumStruct{}
|
|
one.Cid = t.Cid
|
|
one.Allocateyear = year
|
|
PaidInMoney := fmt.Sprintf("%0.0f", count)
|
|
one.PaidInMoney = utils.ValueToFloat(PaidInMoney, 0.0)
|
|
propertyidLi := make([]string, 0)
|
|
err = e.Table(t.TableName()).Where("cid = ? and chargetime >= ? and chargetime <= ?", t.Cid, startDate, endDate).Cols("propertyid").Find(&propertyidLi)
|
|
if err != nil {
|
|
return nil, 0, err
|
|
}
|
|
propertyidMap := make(map[string]interface{}, 0)
|
|
for _, propertyid := range propertyidLi {
|
|
propertyidMap[propertyid] = nil
|
|
}
|
|
disctincPropertyidLi := make([]string, 0)
|
|
for propertyid, _ := range propertyidMap {
|
|
disctincPropertyidLi = append(disctincPropertyidLi, propertyid)
|
|
}
|
|
var property Propertytab
|
|
area, err := e.Table(property.TableName()).In("propertyid", disctincPropertyidLi).Sum(property, "constructionarea")
|
|
if err != nil {
|
|
glog.InfoExtln("年缴费测试", "err1 := ", err)
|
|
return nil, 0, err
|
|
}
|
|
//area := fmt.Sprintf("%0.2f", info.PaidInArea)
|
|
one.PaidInArea = utils.ValueToFloat(area, 0.0)
|
|
//计算应收金额和面积
|
|
buildInfo := new(BuildingInfo)
|
|
_, err = e.Table("propertytab").Select("propertytypetab.unitprice as unitprice,(SELECT SUM(constructionarea) FROM propertytab WHERE isfree != '3') as constructionarea").Join("INNER", "buildingtab", "propertytab.buildingid=buildingtab.buildingid and propertytab.cid=buildingtab.cid").Join("INNER", "propertytypetab", "propertytypetab.propertytypeid=buildingtab.propertytypeid and propertytypetab.cid=buildingtab.cid").Where("propertytab.cid = ?", t.Cid).Get(buildInfo)
|
|
if err != nil {
|
|
glog.InfoExtln("年缴费测试", "err2 := ", err)
|
|
return nil, 0, err
|
|
}
|
|
ReceivableArea := fmt.Sprintf("%0.2f", buildInfo.Constructionarea)
|
|
one.ReceivableArea = utils.ValueToFloat(ReceivableArea, 0.0)
|
|
money := fmt.Sprintf("%0.0f", buildInfo.Constructionarea*buildInfo.Unitprice*12)
|
|
one.ReceivableMoney = utils.ValueToFloat(money, 0.0)
|
|
if one.ReceivableMoney == 0 {
|
|
one.CurrentPercent = "0.00%"
|
|
} else {
|
|
one.CurrentPercent = fmt.Sprintf("%.2f", ((totalMoney-arrearageMoney)/one.ReceivableMoney)*100) + "%"
|
|
}
|
|
if arrearageYears == 0 {
|
|
one.ArrearagePercent = "0.00%"
|
|
} else {
|
|
one.ArrearagePercent = fmt.Sprintf("%.2f", (arrearageMoney/(one.ReceivableMoney*float64(arrearageYears)))*100) + "%"
|
|
}
|
|
glog.InfoExtln("年缴费测试", "one := ", one)
|
|
|
|
datalist = append(datalist, one)
|
|
sumMoney := 0.0
|
|
for _, v := range datalist {
|
|
sumMoney += v.PaidInMoney
|
|
}
|
|
total := utils.ValueToInt(sumMoney, 0)
|
|
return datalist, total, nil
|
|
}
|