invoice_payment.go 35 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957
  1. package census
  2. import (
  3. "bytes"
  4. "fmt"
  5. "github.com/gin-gonic/gin"
  6. "github.com/go-playground/validator/v10"
  7. "github.com/tealeg/xlsx"
  8. "hongze/fms_api/controller/resp"
  9. "hongze/fms_api/global"
  10. "hongze/fms_api/models/base"
  11. "hongze/fms_api/models/fms"
  12. fmsService "hongze/fms_api/services/fms"
  13. "hongze/fms_api/utils"
  14. "net/http"
  15. "strconv"
  16. "strings"
  17. "sync"
  18. "time"
  19. )
  20. // InvoicePaymentController 商品到款统计
  21. type InvoicePaymentController struct{}
  22. // List2 原版(已废弃)
  23. //func (ct *InvoicePaymentController) List2(c *gin.Context) {
  24. // var req fms.InvoicePaymentCensusListReq
  25. // if e := c.BindQuery(&req); e != nil {
  26. // err, ok := e.(validator.ValidationErrors)
  27. // if !ok {
  28. // resp.FailData("参数解析失败", "Err:"+e.Error(), c)
  29. // return
  30. // }
  31. // resp.FailData("参数解析失败", err.Translate(global.Trans), c)
  32. // return
  33. // }
  34. //
  35. // cond := `1 = 1`
  36. // pars := make([]interface{}, 0)
  37. // // 合同编号/客户姓名/销售
  38. // if req.Keyword != "" {
  39. // kw := "%" + req.Keyword + "%"
  40. // cond += ` AND b.company_name LIKE ? OR a.seller_name LIKE ?`
  41. // pars = append(pars, kw, kw)
  42. // }
  43. // if req.SellGroupId > 0 {
  44. // cond += ` AND a.seller_group_id = ?`
  45. // pars = append(pars, req.SellGroupId)
  46. // }
  47. // // 套餐筛选
  48. // if req.ServiceType != 0 {
  49. // registerIds, e := fms.GetContractRegisterIdsByTempId(req.ServiceType)
  50. // if e != nil {
  51. // resp.FailMsg("获取失败", "获取合同登记IDs失败, Err: "+e.Error(), c)
  52. // return
  53. // }
  54. // if len(registerIds) > 0 {
  55. // cond += ` AND b.contract_register_id IN ?`
  56. // pars = append(pars, registerIds)
  57. // } else {
  58. // cond += ` AND 1 = 2`
  59. // }
  60. // }
  61. // // 开票到款日期
  62. // if req.TimeType > 0 && req.StartDate != "" && req.EndDate != "" {
  63. // st := fmt.Sprint(req.StartDate, " 00:00:00")
  64. // ed := fmt.Sprint(req.EndDate, " 23:59:59")
  65. // cond += ` AND a.invoice_type = ? AND (a.invoice_time BETWEEN ? AND ?)`
  66. // pars = append(pars, req.TimeType, st, ed)
  67. // }
  68. // // 已开票
  69. // if req.HasInvoice == 1 && req.HasPayment == 0 {
  70. // cond += ` AND a.invoice_type = 1`
  71. // }
  72. // // 已到款
  73. // if req.HasInvoice == 0 && req.HasPayment == 1 {
  74. // cond += ` AND a.invoice_type = 2`
  75. // }
  76. //
  77. // page := new(base.Page)
  78. // page.SetPageSize(req.PageSize)
  79. // page.SetCurrent(req.Current)
  80. // page.AddOrderItem(base.OrderItem{Column: "a.create_time", Asc: false})
  81. // page.AddOrderItem(base.OrderItem{Column: "b.contract_register_id", Asc: true})
  82. // if req.IsExport == 1 {
  83. // page.SetPageSize(10000)
  84. // page.SetCurrent(1)
  85. // }
  86. //
  87. // list, allRegisterIds, e := fms.GetInvoicePaymentCensusPageList(page, cond, pars)
  88. // if e != nil {
  89. // resp.FailMsg("获取失败", "获取商品到款统计列表失败, Err: "+e.Error(), c)
  90. // return
  91. // }
  92. // total := int64(len(allRegisterIds))
  93. // registerIds := make([]int, 0)
  94. // for i := range list {
  95. // registerIds = append(registerIds, list[i].ContractRegisterId)
  96. // }
  97. //
  98. // results := new(fms.InvoicePaymentCensusResp)
  99. // if len(registerIds) > 0 {
  100. // // 获取开票到款列表
  101. // ivCond := `contract_register_id IN ?`
  102. // ivPars := make([]interface{}, 0)
  103. // ivPars = append(ivPars, registerIds)
  104. // iv := new(fms.ContractInvoice)
  105. // invoiceList, e := iv.List(ivCond, ivPars, "")
  106. // if e != nil {
  107. // resp.FailMsg("获取失败", "获取开票到款列表失败, Err: "+e.Error(), c)
  108. // return
  109. // }
  110. // // 取出开票、到款
  111. // invoiceMap := make(map[int][]*fms.ContractInvoice, 0)
  112. // paymentMap := make(map[int][]*fms.ContractInvoice, 0)
  113. // paymentIds := make([]int, 0)
  114. // for i := range invoiceList {
  115. // if invoiceMap[invoiceList[i].ContractRegisterId] == nil {
  116. // invoiceMap[invoiceList[i].ContractRegisterId] = make([]*fms.ContractInvoice, 0)
  117. // }
  118. // if paymentMap[invoiceList[i].ContractRegisterId] == nil {
  119. // paymentMap[invoiceList[i].ContractRegisterId] = make([]*fms.ContractInvoice, 0)
  120. // }
  121. // if invoiceList[i].InvoiceType == fms.ContractInvoiceTypeMake {
  122. // invoiceMap[invoiceList[i].ContractRegisterId] = append(invoiceMap[invoiceList[i].ContractRegisterId], invoiceList[i])
  123. // }
  124. // if invoiceList[i].InvoiceType == fms.ContractInvoiceTypePay {
  125. // paymentMap[invoiceList[i].ContractRegisterId] = append(paymentMap[invoiceList[i].ContractRegisterId], invoiceList[i])
  126. // paymentIds = append(paymentIds, invoiceList[i].ContractInvoiceId)
  127. // }
  128. // }
  129. //
  130. // // 合同套餐
  131. // contractServiceCond := `contract_register_id IN ?`
  132. // contractServicePars := make([]interface{}, 0)
  133. // contractServicePars = append(contractServicePars, registerIds)
  134. // contractServiceOB := new(fms.ContractService)
  135. // contractServiceList, e := contractServiceOB.List(contractServiceCond, contractServicePars)
  136. // if e != nil {
  137. // resp.FailMsg("获取失败", "获取合同套餐列表失败, Err:"+e.Error(), c)
  138. // return
  139. // }
  140. // contractServiceMap := make(map[int][]*fms.ContractService, 0)
  141. // servicesNameMap := make(map[int][]string, 0)
  142. // for i := range contractServiceList {
  143. // if contractServiceMap[contractServiceList[i].ContractRegisterId] == nil {
  144. // contractServiceMap[contractServiceList[i].ContractRegisterId] = make([]*fms.ContractService, 0)
  145. // }
  146. // contractServiceMap[contractServiceList[i].ContractRegisterId] = append(contractServiceMap[contractServiceList[i].ContractRegisterId], contractServiceList[i])
  147. // servicesNameMap[contractServiceList[i].ContractRegisterId] = append(servicesNameMap[contractServiceList[i].ContractRegisterId], contractServiceList[i].Title)
  148. // }
  149. //
  150. // // 到款套餐分配
  151. // serviceAmountMap := make(map[int][]*fms.ContractPaymentServiceAmount, 0)
  152. // if len(paymentIds) > 0 {
  153. // serviceAmountCond := `contract_payment_id IN ?`
  154. // serviceAmountPars := make([]interface{}, 0)
  155. // serviceAmountPars = append(serviceAmountPars, paymentIds)
  156. // serviceAmountOB := new(fms.ContractPaymentServiceAmount)
  157. // serviceAmountList, e := serviceAmountOB.List(serviceAmountCond, serviceAmountPars)
  158. // if e != nil {
  159. // resp.FailMsg("获取失败", "获取到款套餐分配列表失败, Err:"+e.Error(), c)
  160. // return
  161. // }
  162. // for i := range serviceAmountList {
  163. // if serviceAmountMap[serviceAmountList[i].ContractRegisterId] == nil {
  164. // serviceAmountMap[serviceAmountList[i].ContractRegisterId] = make([]*fms.ContractPaymentServiceAmount, 0)
  165. // }
  166. // serviceAmountMap[serviceAmountList[i].ContractRegisterId] = append(serviceAmountMap[serviceAmountList[i].ContractRegisterId], serviceAmountList[i])
  167. // }
  168. // }
  169. //
  170. // // 整合响应数据
  171. // respList := make([]*fms.InvoicePaymentCensusItem, 0)
  172. // for i := range list {
  173. // v := new(fms.InvoicePaymentCensusItem)
  174. // v.ContractRegisterId = list[i].ContractRegisterId
  175. // v.CompanyName = list[i].CompanyName
  176. // v.NewCompany = list[i].NewCompany
  177. // v.StartDate = list[i].StartDate.Format(utils.FormatDate)
  178. // v.EndDate = list[i].EndDate.Format(utils.FormatDate)
  179. // svList := servicesNameMap[list[i].ContractRegisterId]
  180. // v.ServicesName = strings.Join(svList, ",")
  181. // // 格式化(合并)开票到款数据
  182. // v.InvoicePaymentList = fmsService.MergeInvoiceList2InvoicePaymentCensusInfo(invoiceMap[list[i].ContractRegisterId], paymentMap[list[i].ContractRegisterId],
  183. // contractServiceMap[list[i].ContractRegisterId], serviceAmountMap[list[i].ContractRegisterId])
  184. // respList = append(respList, v)
  185. // }
  186. //
  187. // // 开票到款金额合计(换算后)
  188. // amountTotalCond := `contract_register_id IN ?`
  189. // amountTotalPars := make([]interface{}, 0)
  190. // amountTotalPars = append(amountTotalPars, allRegisterIds)
  191. // amountTotalList, e := fms.GetContractInvoiceAmountTotal(amountTotalCond, amountTotalPars)
  192. // if e != nil {
  193. // resp.FailMsg("获取失败", "获取开票到款金额合计失败, Err:"+e.Error(), c)
  194. // return
  195. // }
  196. // amountTotalMap := make(map[int]float64)
  197. // for i := range amountTotalList {
  198. // amountTotalMap[amountTotalList[i].InvoiceType] = amountTotalList[i].TotalAmount
  199. // }
  200. // invoiceTotal, _ := strconv.ParseFloat(fmt.Sprintf("%.2f", amountTotalMap[fms.ContractInvoiceTypeMake]), 64)
  201. // paymentTotal, _ := strconv.ParseFloat(fmt.Sprintf("%.2f", amountTotalMap[fms.ContractInvoiceTypePay]), 64)
  202. //
  203. // // 分币种金额统计
  204. // currencyOB := new(fms.CurrencyUnit)
  205. // currencyCond := `enable = 1`
  206. // currencyPars := make([]interface{}, 0)
  207. // currencyList, e := currencyOB.List(currencyCond, currencyPars)
  208. // if e != nil {
  209. // resp.FailMsg("获取失败", "获取货币列表失败, Err: "+e.Error(), c)
  210. // return
  211. // }
  212. // unitMap := make(map[string]string)
  213. // invoiceCurrencyTotals := make([]*fms.InvoiceListCurrencyTotal, 0)
  214. // paymentCurrencyTotals := make([]*fms.InvoiceListCurrencyTotal, 0)
  215. // for i := range currencyList {
  216. // unitMap[currencyList[i].Code] = currencyList[i].UnitName
  217. // invoiceCurrencyTotals = append(invoiceCurrencyTotals, &fms.InvoiceListCurrencyTotal{
  218. // Name: currencyList[i].Name,
  219. // UnitName: currencyList[i].UnitName,
  220. // Code: currencyList[i].Code,
  221. // FlagImg: currencyList[i].FlagImg,
  222. // })
  223. // paymentCurrencyTotals = append(paymentCurrencyTotals, &fms.InvoiceListCurrencyTotal{
  224. // Name: currencyList[i].Name,
  225. // UnitName: currencyList[i].UnitName,
  226. // Code: currencyList[i].Code,
  227. // FlagImg: currencyList[i].FlagImg,
  228. // })
  229. // }
  230. // sumList, e := fms.GetInvoiceListCurrencySum(amountTotalCond, amountTotalPars, "currency_unit, invoice_type")
  231. // if e != nil {
  232. // resp.FailMsg("获取失败", "获取商品到款统计货币合计金额失败, Err: "+e.Error(), c)
  233. // return
  234. // }
  235. // invoiceSumMap := make(map[string]float64)
  236. // paymentSumMap := make(map[string]float64)
  237. // for i := range sumList {
  238. // if sumList[i].InvoiceType == fms.ContractInvoiceTypeMake {
  239. // invoiceSumMap[sumList[i].CurrencyUnit] = sumList[i].OriginAmountTotal
  240. // continue
  241. // }
  242. // if sumList[i].InvoiceType == fms.ContractInvoiceTypePay {
  243. // paymentSumMap[sumList[i].CurrencyUnit] = sumList[i].OriginAmountTotal
  244. // }
  245. // }
  246. // for i := range invoiceCurrencyTotals {
  247. // a, _ := strconv.ParseFloat(fmt.Sprintf("%.2f", invoiceSumMap[invoiceCurrencyTotals[i].Code]), 64)
  248. // invoiceCurrencyTotals[i].Amount = a
  249. // }
  250. // for i := range paymentCurrencyTotals {
  251. // a, _ := strconv.ParseFloat(fmt.Sprintf("%.2f", paymentSumMap[paymentCurrencyTotals[i].Code]), 64)
  252. // paymentCurrencyTotals[i].Amount = a
  253. // }
  254. //
  255. // results.DataList = respList
  256. // results.InvoiceTotal = invoiceTotal
  257. // results.PaymentTotal = paymentTotal
  258. // results.InvoiceCurrencyTotal = invoiceCurrencyTotals
  259. // results.PaymentCurrencyTotal = paymentCurrencyTotals
  260. // }
  261. //
  262. // // 是否导出
  263. // if req.IsExport == 1 {
  264. // ExportInvoicePaymentCensusList(c, results)
  265. // return
  266. // }
  267. // page.SetTotal(total)
  268. // baseData := new(base.BaseData)
  269. // baseData.SetPage(page)
  270. // baseData.SetList(results)
  271. // resp.OkData("获取成功", baseData, c)
  272. //}
  273. // ExportInvoicePaymentCensusList 导出商品到款统计列表
  274. func ExportInvoicePaymentCensusList(c *gin.Context, results *fms.InvoicePaymentCensusResp) {
  275. list := results.DataList
  276. if len(list) == 0 {
  277. resp.Fail("列表数据为空", c)
  278. return
  279. }
  280. // 生成Excel文件
  281. xlsxFile := xlsx.NewFile()
  282. style := xlsx.NewStyle()
  283. alignment := xlsx.Alignment{
  284. Horizontal: "center",
  285. Vertical: "center",
  286. WrapText: true,
  287. }
  288. style.Alignment = alignment
  289. style.ApplyAlignment = true
  290. sheet, err := xlsxFile.AddSheet("商品到款统计")
  291. if err != nil {
  292. resp.FailData("新增Sheet失败", "Err:"+err.Error(), c)
  293. return
  294. }
  295. _ = sheet.SetColWidth(1, 1, 30)
  296. _ = sheet.SetColWidth(3, 3, 30)
  297. // 前三行-开票金额合计
  298. rowA := sheet.AddRow()
  299. cellAA := rowA.AddCell()
  300. cellAA.SetString(fmt.Sprintf("已开票合计金额(换算后):%.2f(元)", results.InvoiceTotal))
  301. rowBData := "已开票金额:"
  302. for _, v := range results.InvoiceCurrencyTotal {
  303. rowBData += fmt.Sprintf("%s%.2f(%s) ", v.Name, v.Amount, v.UnitName)
  304. }
  305. rowB := sheet.AddRow()
  306. rowB.AddCell().SetString(rowBData)
  307. sheet.AddRow()
  308. // 四至六-到款金额合计
  309. rowD := sheet.AddRow()
  310. cellDA := rowD.AddCell()
  311. cellDA.SetString(fmt.Sprintf("已到款合计金额(换算后):%.2f(元)", results.PaymentTotal))
  312. rowEData := "已到款金额:"
  313. for _, v := range results.PaymentCurrencyTotal {
  314. rowEData += fmt.Sprintf("%s%.2f(%s) ", v.Name, v.Amount, v.UnitName)
  315. }
  316. rowE := sheet.AddRow()
  317. rowE.AddCell().SetString(rowEData)
  318. sheet.AddRow()
  319. // 表头, 套餐动态获取
  320. rowTitle := []string{"序号", "客户名称", "是否新客户", "合同有效期", "开票日", "开票金额", "到款日", "到款金额", "付款方式", "销售",
  321. "销售组别", "销售类型"}
  322. serviceTempCond := ``
  323. serviceTempPars := make([]interface{}, 0)
  324. serviceTempOB := new(fms.ContractServiceTemplate)
  325. serviceTempList, e := serviceTempOB.List(serviceTempCond, serviceTempPars)
  326. if e != nil {
  327. resp.FailData("获取套餐模板列表失败", "Err:"+e.Error(), c)
  328. return
  329. }
  330. serviceTempListMap := make(map[int]*fms.ContractServiceTemplate)
  331. var serviceTempShow [] *fms.ContractServiceTemplate
  332. for i := range serviceTempList {
  333. serviceTempListMap[serviceTempList[i].ServiceTemplateId] = serviceTempList[i]
  334. }
  335. for i := range serviceTempList {
  336. if serviceTempList[i].ProductId == 1 {
  337. rowTitle = append(rowTitle, serviceTempList[i].Title)
  338. serviceTempShow = append(serviceTempShow, serviceTempList[i])
  339. }else if serviceTempList[i].ProductId == 2{
  340. if serviceTempList[i].Title == "行业套餐" || serviceTempList[i].Title == "权益大套餐" || (serviceTempList[i].Title == "策略" && serviceTempListMap[serviceTempList[i].Pid].Pid==0){
  341. }else if serviceTempList[i].Title == "医药" || serviceTempList[i].Title == "消费" || serviceTempList[i].Title == "科技" || serviceTempList[i].Title == "智造" {
  342. }else {
  343. serviceTempShow = append(serviceTempShow, serviceTempList[i])
  344. }
  345. if serviceTempList[i].Title == "行业套餐" || serviceTempList[i].Title == "45万" || serviceTempList[i].Title == "70万" {
  346. continue
  347. }
  348. if serviceTempList[i].Pid > 0 && serviceTempListMap[serviceTempList[i].Pid].Pid > 0 {
  349. continue
  350. }
  351. rowTitle = append(rowTitle, serviceTempList[i].Title)
  352. }
  353. }
  354. hi := 0
  355. insertHi := 0
  356. titleRow := sheet.AddRow()
  357. for i := range rowTitle {
  358. v := titleRow.AddCell()
  359. v.SetString(rowTitle[i])
  360. v.SetStyle(style)
  361. if rowTitle[i] != "权益大套餐" && rowTitle[i] != "医药" && rowTitle[i] != "消费" && rowTitle[i] != "科技" && rowTitle[i] != "智造" {
  362. v.VMerge = 1
  363. hi ++
  364. }else{
  365. if insertHi == 0 {
  366. insertHi = hi
  367. }
  368. v.HMerge = 1
  369. titleRow.AddCell().SetString("")
  370. }
  371. }
  372. // 新增一行放主观和客观
  373. specialRow := sheet.AddRow()
  374. for i:=0; i< hi; i++ {
  375. if i == insertHi {
  376. v1 := specialRow.AddCell()
  377. v1.SetString("45万")
  378. v1.SetStyle(style)
  379. v1 = specialRow.AddCell()
  380. v1.SetString("70万")
  381. v1.SetStyle(style)
  382. for j:=0; j<8;j++ {
  383. if j%2 ==0 {
  384. v1 = specialRow.AddCell()
  385. v1.SetString("主观")
  386. v1.SetStyle(style)
  387. }else{
  388. v1 = specialRow.AddCell()
  389. v1.SetString("客观")
  390. v1.SetStyle(style)
  391. }
  392. }
  393. }else{
  394. v := specialRow.AddCell()
  395. v.SetString("")
  396. }
  397. }
  398. newCompanyMap := map[int]string{0: "否", 1: "是"}
  399. sellerTypeMap := map[int]string{1:"FICC销售", 2:"权益销售"}
  400. for k, v := range list {
  401. dataRow := sheet.AddRow()
  402. // 前四个单元格根据每行开票到款条数向下合并
  403. l := len(v.InvoicePaymentList)
  404. mergeRowNum := l - 1
  405. // 序号
  406. sortNum := k + 1
  407. colA := dataRow.AddCell()
  408. colA.VMerge = mergeRowNum
  409. colA.SetString(fmt.Sprint(sortNum))
  410. // 客户名称
  411. colB := dataRow.AddCell()
  412. colB.VMerge = mergeRowNum
  413. colB.SetString(v.CompanyName)
  414. // 是否新客户
  415. colC := dataRow.AddCell()
  416. colC.VMerge = mergeRowNum
  417. colC.SetString(newCompanyMap[v.NewCompany])
  418. // 合同有效期
  419. colD := dataRow.AddCell()
  420. colD.VMerge = mergeRowNum
  421. colD.SetString(fmt.Sprint(v.StartDate, "至", v.EndDate))
  422. // 开票到款信息
  423. for k2, v2 := range v.InvoicePaymentList {
  424. rowData := []string{
  425. v2.InvoiceDate, // 开票日
  426. fmt.Sprint(v2.InvoiceAmount), // 开票金额
  427. v2.PaymentDate, // 到款日
  428. fmt.Sprint(v2.PaymentAmount), // 到款金额
  429. fms.ContractPaymentPayTypeNameMap[v2.PayType], // 付款方式
  430. v2.SellerName, // 销售
  431. v2.SellerGroupName, // 组别
  432. sellerTypeMap[v2.SellerType], // 销售类型
  433. }
  434. // 套餐金额信息
  435. serviceTempShowAmount := make(map[int]string)
  436. for i := range serviceTempShow {
  437. for s2 := range v2.ServiceAmountList {
  438. item := v2.ServiceAmountList[s2]
  439. if item.ServiceTemplateId == serviceTempShow[i].ServiceTemplateId {
  440. serviceTempShowAmount[serviceTempShow[i].ServiceTemplateId] = fmt.Sprint(item.Amount)
  441. break
  442. }else if serviceTempShow[i].Pid == item.ServiceTemplateId {
  443. serviceTempShowAmount[serviceTempShow[i].ServiceTemplateId] = fmt.Sprint(item.Amount/2)
  444. break
  445. }
  446. }
  447. }
  448. for i := range serviceTempShow {
  449. sa := ""
  450. if am, ok := serviceTempShowAmount[serviceTempShow[i].ServiceTemplateId]; ok {
  451. sa = am
  452. }
  453. rowData = append(rowData, sa)
  454. }
  455. // 首行开票到款
  456. if k2 == 0 {
  457. for i := range rowData {
  458. dataRow.AddCell().SetString(rowData[i])
  459. }
  460. continue
  461. }
  462. // 其他行开票到款, 加四列空的单元格用于合并
  463. dataRowExtra := sheet.AddRow()
  464. for i := 0; i < 4; i++ {
  465. dataRowExtra.AddCell()
  466. }
  467. for i := range rowData {
  468. dataRowExtra.AddCell().SetString(rowData[i])
  469. }
  470. }
  471. }
  472. // 输出文件
  473. var buffer bytes.Buffer
  474. _ = xlsxFile.Write(&buffer)
  475. content := bytes.NewReader(buffer.Bytes())
  476. randStr := time.Now().Format(utils.FormatDateTimeUnSpace)
  477. fileName := "商品到款统计_" + randStr + ".xlsx"
  478. c.Writer.Header().Add("Content-Disposition", fmt.Sprintf(`attachment; filename="%s"`, fileName))
  479. c.Writer.Header().Add("Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
  480. http.ServeContent(c.Writer, c.Request, fileName, time.Now(), content)
  481. }
  482. // List
  483. // @Title 商品到款统计列表
  484. // @Description 商品到款统计列表
  485. // @Param Keyword query string false "关键词"
  486. // @Param SellGroupId query int false "销售组别ID"
  487. // @Param ServiceType query int false "套餐类型"
  488. // @Param StartDate query string false "合同开始日期"
  489. // @Param EndDate query string false "合同结束日期"
  490. // @Param TimeType query int false "时间类型: 1-开票时间; 2-到款时间"
  491. // @Param HasInvoice query string false "是否已开票: 0-否; 1-是; 空-全部"
  492. // @Param HasPayment query string false "是否已到款: 0-否; 1-是; 空-全部"
  493. // @Param IsExport query int false "是否导出: 0-否; 1-是"
  494. // @Param ListParam query int false "套餐类型: 0-全部; 1-ficc; 2-权益"
  495. // @Param SortParam query string false "排序字段参数,用来排序的字段, 枚举值:'invoice_time':开票日 、 'payment_date':到款日"
  496. // @Param SortType query string true "如何排序,是正序还是倒序,枚举值:`asc 正序`,`desc 倒叙`"
  497. // @Success 200 {object} fms.ContractRegisterItem
  498. // @router /census/invoice_payment/list [get]
  499. func (ct *InvoicePaymentController) List(c *gin.Context) {
  500. var req fms.InvoicePaymentCensusListReq
  501. if e := c.BindQuery(&req); e != nil {
  502. err, ok := e.(validator.ValidationErrors)
  503. if !ok {
  504. resp.FailData("参数解析失败", "Err:"+e.Error(), c)
  505. return
  506. }
  507. resp.FailData("参数解析失败", err.Translate(global.Trans), c)
  508. return
  509. }
  510. cond := `1 = 1`
  511. pars := make([]interface{}, 0)
  512. // 客户姓名/销售
  513. if req.Keyword != "" {
  514. kw := "%" + req.Keyword + "%"
  515. cond += ` AND b.company_name LIKE ?`
  516. pars = append(pars, kw)
  517. }
  518. if req.SellerIds != "" {
  519. sellerIds := strings.Split(req.SellerIds, ",")
  520. cond += ` AND (c.seller_id in ? OR d.seller_id in ?)`
  521. pars = append(pars, sellerIds, sellerIds)
  522. }
  523. // 套餐筛选
  524. if req.ServiceTypes != "" {
  525. serviceTypes := strings.Split(req.ServiceTypes, ",")
  526. tempRegisterIds, e := fms.GetContractRegisterIdsByTempId(serviceTypes)
  527. if e != nil {
  528. resp.FailMsg("获取失败", "获取合同登记IDs失败, Err: "+e.Error(), c)
  529. return
  530. }
  531. if len(tempRegisterIds) > 0 {
  532. cond += ` AND a.register_id IN ?`
  533. pars = append(pars, tempRegisterIds)
  534. } else {
  535. cond += ` AND 1 = 2`
  536. }
  537. }
  538. if req.ListParam == 1{
  539. cond += ` AND a.service_product_id = 1 `
  540. }else if req.ListParam == 2 {
  541. cond += ` AND a.service_product_id = 2 `
  542. }
  543. // 开票到款日期
  544. if req.TimeType != 0 {
  545. if req.TimeType == 1 && req.StartDate != "" && req.EndDate != "" {
  546. st := fmt.Sprint(req.StartDate, " 00:00:00")
  547. ed := fmt.Sprint(req.EndDate, " 23:59:59")
  548. cond += ` AND (c.invoice_time BETWEEN ? AND ?) `
  549. pars = append(pars, st, ed)
  550. }else if req.TimeType == 2 && req.StartDate != "" && req.EndDate != "" {
  551. st := fmt.Sprint(req.StartDate, " 00:00:00")
  552. ed := fmt.Sprint(req.EndDate, " 23:59:59")
  553. cond += ` AND (d.invoice_time BETWEEN ? AND ?) `
  554. pars = append(pars, st, ed)
  555. }else if req.TimeType == 3 && req.StartDate != "" && req.EndDate != "" {
  556. st := fmt.Sprint(req.StartDate, " 00:00:00")
  557. ed := fmt.Sprint(req.EndDate, " 23:59:59")
  558. cond += ` AND (c.invoice_time BETWEEN ? AND ?) AND (d.invoice_time BETWEEN ? AND ?) `
  559. pars = append(pars, st, ed, st, ed)
  560. }
  561. } else if req.StartDate != "" && req.EndDate != "" {
  562. st := fmt.Sprint(req.StartDate, " 00:00:00")
  563. ed := fmt.Sprint(req.EndDate, " 23:59:59")
  564. cond += ` AND ((c.invoice_time BETWEEN ? AND ?) or (d.invoice_time BETWEEN ? AND ?))`
  565. pars = append(pars, st, ed, st, ed)
  566. }
  567. if req.HasInvoice == "1" {
  568. cond += ` AND a.invoice_id > 0 `
  569. }else if req.HasInvoice == "0" {
  570. cond += ` AND a.invoice_id = 0 `
  571. }
  572. if req.HasPayment == "1" {
  573. cond += ` AND a.payment_id > 0 `
  574. }else if req.HasPayment == "0" {
  575. cond += ` AND a.payment_id = 0 `
  576. }
  577. page := new(base.Page)
  578. page.SetPageSize(req.PageSize)
  579. page.SetCurrent(req.Current)
  580. //排序
  581. if req.SortType == "" {
  582. req.SortType = "desc"
  583. }
  584. if req.SortType != "desc" && req.SortType != "asc" {
  585. resp.Fail("排序类型不正确", c)
  586. return
  587. }
  588. if req.SortParam == "" {
  589. page.AddOrderItem(base.OrderItem{Column: "sort_invoice_id", Asc: true})
  590. page.AddOrderItem(base.OrderItem{Column: "sort_payment_id", Asc: false})
  591. page.AddOrderItem(base.OrderItem{Column: "c.invoice_time", Asc: false})
  592. page.AddOrderItem(base.OrderItem{Column: "c.amount", Asc: false})
  593. page.AddOrderItem(base.OrderItem{Column: "a.create_time", Asc: false})
  594. }else if req.SortParam == "invoice_time" {
  595. if req.SortType == "asc" {
  596. page.AddOrderItem(base.OrderItem{Column: "sort_invoice_id", Asc: false})
  597. page.AddOrderItem(base.OrderItem{Column: "c.invoice_time", Asc: true})
  598. page.AddOrderItem(base.OrderItem{Column: "c.amount", Asc: true})
  599. page.AddOrderItem(base.OrderItem{Column: "a.create_time", Asc: true})
  600. }else{
  601. page.AddOrderItem(base.OrderItem{Column: "sort_invoice_id", Asc: true})
  602. page.AddOrderItem(base.OrderItem{Column: "c.invoice_time", Asc: false})
  603. page.AddOrderItem(base.OrderItem{Column: "c.amount", Asc: false})
  604. page.AddOrderItem(base.OrderItem{Column: "a.create_time", Asc: false})
  605. }
  606. }else if req.SortParam == "payment_date" {
  607. if req.SortType == "asc" {
  608. page.AddOrderItem(base.OrderItem{Column: "sort_payment_id", Asc: false})
  609. page.AddOrderItem(base.OrderItem{Column: "d.invoice_time", Asc: true})
  610. page.AddOrderItem(base.OrderItem{Column: "d.amount", Asc: true})
  611. page.AddOrderItem(base.OrderItem{Column: "a.create_time", Asc: true})
  612. }else{
  613. page.AddOrderItem(base.OrderItem{Column: "sort_payment_id", Asc: true})
  614. page.AddOrderItem(base.OrderItem{Column: "d.invoice_time", Asc: false})
  615. page.AddOrderItem(base.OrderItem{Column: "d.amount", Asc: false})
  616. page.AddOrderItem(base.OrderItem{Column: "a.create_time", Asc: false})
  617. }
  618. }else{
  619. resp.Fail("排序字段不正确", c)
  620. return
  621. }
  622. if req.IsExport == 1 {
  623. page.SetPageSize(10000)
  624. page.SetCurrent(1)
  625. }
  626. registerList, total, e := fms.GetInvoicePaymentCensusPageList(page, cond, pars)
  627. if e != nil {
  628. resp.FailMsg("获取失败", "获取商品到款统计列表总数失败, Err: "+e.Error(), c)
  629. return
  630. }
  631. queryRegisterIds := make([]int, 0)
  632. for i := range registerList {
  633. queryRegisterIds = append(queryRegisterIds, registerList[i].ContractRegisterId)
  634. }
  635. //fmt.Println("queryRegisterIds:",queryRegisterIds)
  636. results := new(fms.InvoicePaymentCensusResp)
  637. if len(queryRegisterIds) > 0 {
  638. // 获取汇总数据IDs, 用于查询合计数据
  639. summaryIdsCond := cond
  640. summaryIdsPars := pars
  641. summaryIds, e := fms.GetInvoicePaymentCensusSummaryDataIds(summaryIdsCond, summaryIdsPars)
  642. if e != nil {
  643. resp.FailMsg("获取失败", "获取商品到款汇总IDs失败, Err: "+e.Error(), c)
  644. return
  645. }
  646. //fmt.Println("summaryIds:",summaryIds)
  647. var listErr, totalErr, totalGroupErr error
  648. wg := sync.WaitGroup{}
  649. // 响应列表
  650. respList := make([]*fms.InvoicePaymentCensusItem, 0)
  651. summaryList := make([]*fms.InvoicePaymentSummaryItem, 0)
  652. wg.Add(1)
  653. go func() {
  654. defer wg.Done()
  655. // 获取汇总数据
  656. summaryCond := cond
  657. summaryCond += ` AND a.register_id IN ?`
  658. summaryPars := pars
  659. summaryPars = append(summaryPars, queryRegisterIds)
  660. summaryData, e := fms.GetInvoicePaymentCensusSummaryData(summaryCond, summaryPars)
  661. if e != nil {
  662. resp.FailMsg("获取失败", "获取商品到款汇总列表失败, Err: "+e.Error(), c)
  663. return
  664. }
  665. summaryList = summaryData
  666. //summaryIds := make([]int, 0)
  667. paymentIds := make([]int, 0)
  668. for i := range summaryList {
  669. //summaryIds = append(summaryIds, summaryList[i].SummaryId)
  670. if summaryList[i].PaymentId > 0 {
  671. paymentIds = append(paymentIds, summaryList[i].PaymentId)
  672. }
  673. }
  674. // 合同套餐
  675. /*contractServiceCond := `contract_register_id IN ?`
  676. contractServicePars := make([]interface{}, 0)
  677. contractServicePars = append(contractServicePars, queryRegisterIds)
  678. contractServiceOB := new(fms.ContractService)
  679. contractServiceList, e := contractServiceOB.List(contractServiceCond, contractServicePars)
  680. if e != nil {
  681. listErr = fmt.Errorf("获取合同套餐列表失败, Err: %s", e.Error())
  682. return
  683. }
  684. contractServiceMap := make(map[int][]*fms.ContractService, 0)
  685. servicesNameMap := make(map[int][]string, 0)
  686. for i := range contractServiceList {
  687. if contractServiceMap[contractServiceList[i].ContractRegisterId] == nil {
  688. contractServiceMap[contractServiceList[i].ContractRegisterId] = make([]*fms.ContractService, 0)
  689. }
  690. contractServiceMap[contractServiceList[i].ContractRegisterId] = append(contractServiceMap[contractServiceList[i].ContractRegisterId], contractServiceList[i])
  691. servicesNameMap[contractServiceList[i].ContractRegisterId] = append(servicesNameMap[contractServiceList[i].ContractRegisterId], contractServiceList[i].Title)
  692. }
  693. */
  694. servicesNameMap, serviceFormatMap, e := fmsService.GetContractServiceNameFormat(queryRegisterIds)
  695. if e != nil {
  696. listErr = fmt.Errorf("获取合同套餐列表失败, Err: %s", e.Error())
  697. return
  698. }
  699. // 到款套餐分配
  700. serviceAmountMap := make(map[int][]*fms.ContractPaymentServiceAmount, 0)
  701. if len(paymentIds) > 0 {
  702. serviceAmountCond := `contract_payment_id IN ?`
  703. serviceAmountPars := make([]interface{}, 0)
  704. serviceAmountPars = append(serviceAmountPars, paymentIds)
  705. serviceAmountOB := new(fms.ContractPaymentServiceAmount)
  706. serviceAmountList, e := serviceAmountOB.List(serviceAmountCond, serviceAmountPars)
  707. if e != nil {
  708. listErr = fmt.Errorf("获取到款套餐分配列表失败, Err: %s", e.Error())
  709. return
  710. }
  711. for i := range serviceAmountList {
  712. if serviceAmountMap[serviceAmountList[i].ContractPaymentId] == nil {
  713. serviceAmountMap[serviceAmountList[i].ContractPaymentId] = make([]*fms.ContractPaymentServiceAmount, 0)
  714. }
  715. serviceAmountMap[serviceAmountList[i].ContractPaymentId] = append(serviceAmountMap[serviceAmountList[i].ContractPaymentId], serviceAmountList[i])
  716. }
  717. }
  718. // 重组汇总数据
  719. summaryMap := make(map[int][]*fms.InvoicePaymentCensusInfo)
  720. amountMap := make(map[string]*fms.ContractPaymentServiceAmount)
  721. for i := range summaryList {
  722. v := new(fms.InvoicePaymentCensusInfo)
  723. v.InvoiceId = summaryList[i].InvoiceId
  724. v.InvoiceDate = utils.TimeTransferString(utils.FormatDate, summaryList[i].InvoiceDate)
  725. v.InvoiceAmount = summaryList[i].InvoiceAmount
  726. v.SellerId = summaryList[i].SellerId
  727. v.SellerName = summaryList[i].SellerName
  728. v.SellerGroupId = summaryList[i].SellerGroupId
  729. v.SellerGroupName = summaryList[i].SellerGroupName
  730. v.SellerType = summaryList[i].ServiceProductId
  731. v.PaymentId = summaryList[i].PaymentId
  732. v.PaymentDate = utils.TimeTransferString(utils.FormatDate, summaryList[i].PaymentDate)
  733. v.PaymentAmount = summaryList[i].PaymentAmount
  734. v.PayType = summaryList[i].PayType
  735. // 套餐到款分配
  736. svaList := make([]*fms.ContractPaymentServiceAmountItem, 0)
  737. amountList := serviceAmountMap[summaryList[i].PaymentId]
  738. if amountList != nil {
  739. for i := range amountList {
  740. k := fmt.Sprintf("%d-%d", amountList[i].ContractPaymentId, amountList[i].ServiceTemplateId)
  741. amountMap[k] = amountList[i]
  742. }
  743. }
  744. // 合同对应的所有套餐
  745. svList := serviceFormatMap[summaryList[i].RegisterId]
  746. if svList != nil {
  747. for ii := range svList {
  748. vv := new(fms.ContractPaymentServiceAmountItem)
  749. vv.ServiceTemplateId = svList[ii].ServiceTemplateId
  750. vv.ServiceTemplateName = svList[ii].FormatTitle
  751. vv.ServiceTemplatePid = svList[ii].ServiceTemplatePid
  752. k2 := fmt.Sprintf("%d-%d", summaryList[i].PaymentId, svList[ii].ServiceTemplateId)
  753. a := amountMap[k2]
  754. if a != nil {
  755. vv.ContractPaymentServiceAmountId = a.ContractPaymentServiceAmountId
  756. vv.ContractPaymentId = a.ContractPaymentId
  757. vv.Amount = a.Amount
  758. }
  759. svaList = append(svaList, vv)
  760. }
  761. }
  762. v.ServiceAmountList = svaList
  763. summaryMap[summaryList[i].SummaryId] = append(summaryMap[summaryList[i].SummaryId], v)
  764. }
  765. // 响应列表
  766. for i := range registerList {
  767. v := new(fms.InvoicePaymentCensusItem)
  768. v.SummaryId = registerList[i].SummaryId
  769. v.ContractRegisterId = registerList[i].ContractRegisterId
  770. v.CompanyName = registerList[i].CompanyName
  771. v.NewCompany = registerList[i].NewCompany
  772. v.StartDate = utils.TimeTransferString(utils.FormatDate, registerList[i].StartDate)
  773. v.EndDate = utils.TimeTransferString(utils.FormatDate, registerList[i].EndDate)
  774. v.ServicesName = servicesNameMap[registerList[i].ContractRegisterId]
  775. v.InvoicePaymentList = summaryMap[registerList[i].SummaryId]
  776. v.ContractType = registerList[i].ContractType
  777. respList = append(respList, v)
  778. }
  779. }()
  780. // 开票到款金额合计(换算后)
  781. var invoiceTotal, paymentTotal,amountTotal float64
  782. wg.Add(1)
  783. go func() {
  784. defer wg.Done()
  785. if len(summaryIds) == 0 {
  786. return
  787. }
  788. amountTotalCond := `a.id IN ?`
  789. amountTotalPars := make([]interface{}, 0)
  790. amountTotalPars = append(amountTotalPars, summaryIds)
  791. invoiceSum, e := fms.GetContractSummaryInvoicePaymentAmountTotal(amountTotalCond, amountTotalPars, 1)
  792. if e != nil {
  793. totalErr = fmt.Errorf("获取汇总开票金额合计失败, Err: %s", e.Error())
  794. return
  795. }
  796. invoiceTotal, _ = strconv.ParseFloat(fmt.Sprintf("%.2f", invoiceSum), 64)
  797. paymentSum, e := fms.GetContractSummaryInvoicePaymentAmountTotal(amountTotalCond, amountTotalPars, 2)
  798. if e != nil {
  799. totalErr = fmt.Errorf("获取汇总到款金额合计失败, Err: %s", e.Error())
  800. return
  801. }
  802. paymentTotal, _ = strconv.ParseFloat(fmt.Sprintf("%.2f", paymentSum), 64)
  803. amountCond := `a.id IN ? AND (a.invoice_id <> 0 OR (a.payment_id <> 0 AND a.invoice_id =0))`
  804. amountPars := make([]interface{}, 0)
  805. amountPars = append(amountTotalPars, summaryIds)
  806. amountSum, e := fms.GetContractSummaryInvoicePaymentAmount(amountCond, amountPars)
  807. if e != nil {
  808. totalErr = fmt.Errorf("获取汇总金额合计失败, Err: %s", e.Error())
  809. return
  810. }
  811. amountTotal, _ = strconv.ParseFloat(fmt.Sprintf("%.2f", amountSum), 64)
  812. }()
  813. // 分币种金额统计
  814. invoiceCurrencyTotals := make([]*fms.InvoiceListCurrencyTotal, 0)
  815. paymentCurrencyTotals := make([]*fms.InvoiceListCurrencyTotal, 0)
  816. wg.Add(1)
  817. go func() {
  818. defer wg.Done()
  819. currencyOB := new(fms.CurrencyUnit)
  820. currencyCond := `enable = 1`
  821. currencyPars := make([]interface{}, 0)
  822. currencyList, e := currencyOB.List(currencyCond, currencyPars)
  823. if e != nil {
  824. totalGroupErr = fmt.Errorf("获取货币列表失败, Err: %s", e.Error())
  825. return
  826. }
  827. unitMap := make(map[string]string)
  828. for i := range currencyList {
  829. unitMap[currencyList[i].Code] = currencyList[i].UnitName
  830. invoiceCurrencyTotals = append(invoiceCurrencyTotals, &fms.InvoiceListCurrencyTotal{
  831. Name: currencyList[i].Name,
  832. UnitName: currencyList[i].UnitName,
  833. Code: currencyList[i].Code,
  834. FlagImg: currencyList[i].FlagImg,
  835. })
  836. paymentCurrencyTotals = append(paymentCurrencyTotals, &fms.InvoiceListCurrencyTotal{
  837. Name: currencyList[i].Name,
  838. UnitName: currencyList[i].UnitName,
  839. Code: currencyList[i].Code,
  840. FlagImg: currencyList[i].FlagImg,
  841. })
  842. }
  843. if len(summaryIds) == 0 {
  844. return
  845. }
  846. totalGroupCond := `a.id IN ?`
  847. totalGroupPars := make([]interface{}, 0)
  848. totalGroupPars = append(totalGroupPars, summaryIds)
  849. invoiceSumGroup, e := fms.GetSummaryListCurrencySum(totalGroupCond, totalGroupPars, 1)
  850. if e != nil {
  851. totalGroupErr = fmt.Errorf("获取汇总货币合计开票金额失败, Err: %s", e.Error())
  852. return
  853. }
  854. paymentSumGroup, e := fms.GetSummaryListCurrencySum(totalGroupCond, totalGroupPars, 2)
  855. if e != nil {
  856. totalGroupErr = fmt.Errorf("获取汇总货币合计到款金额失败, Err: %s", e.Error())
  857. return
  858. }
  859. invoiceSumMap := make(map[string]float64)
  860. paymentSumMap := make(map[string]float64)
  861. for i := range invoiceSumGroup {
  862. invoiceSumMap[invoiceSumGroup[i].CurrencyUnit] = invoiceSumGroup[i].OriginAmountTotal
  863. continue
  864. }
  865. for i := range paymentSumGroup {
  866. paymentSumMap[paymentSumGroup[i].CurrencyUnit] = paymentSumGroup[i].OriginAmountTotal
  867. continue
  868. }
  869. for i := range invoiceCurrencyTotals {
  870. a, _ := strconv.ParseFloat(fmt.Sprintf("%.2f", invoiceSumMap[invoiceCurrencyTotals[i].Code]), 64)
  871. invoiceCurrencyTotals[i].Amount = a
  872. }
  873. for i := range paymentCurrencyTotals {
  874. a, _ := strconv.ParseFloat(fmt.Sprintf("%.2f", paymentSumMap[paymentCurrencyTotals[i].Code]), 64)
  875. paymentCurrencyTotals[i].Amount = a
  876. }
  877. }()
  878. wg.Wait()
  879. if listErr != nil {
  880. resp.FailMsg("获取失败", listErr.Error(), c)
  881. return
  882. }
  883. if totalErr != nil {
  884. resp.FailMsg("获取失败", totalErr.Error(), c)
  885. return
  886. }
  887. if totalGroupErr != nil {
  888. resp.FailMsg("获取失败", totalGroupErr.Error(), c)
  889. return
  890. }
  891. results.DataList = respList
  892. results.InvoiceTotal = invoiceTotal
  893. results.PaymentTotal = paymentTotal
  894. results.AmountTotal = amountTotal
  895. results.InvoiceCurrencyTotal = invoiceCurrencyTotals
  896. results.PaymentCurrencyTotal = paymentCurrencyTotals
  897. }
  898. // 是否导出
  899. if req.IsExport == 1 {
  900. ExportInvoicePaymentCensusList(c, results)
  901. return
  902. }
  903. page.SetTotal(total)
  904. baseData := new(base.BaseData)
  905. baseData.SetPage(page)
  906. baseData.SetList(results)
  907. resp.OkData("获取成功", baseData, c)
  908. }