bill-process-excel.go 9.9 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365
  1. package api
  2. import (
  3. "box-cost/db/model"
  4. "fmt"
  5. _ "image/gif"
  6. _ "image/jpeg"
  7. _ "image/png"
  8. "github.com/xuri/excelize/v2"
  9. )
  10. type ProcessBillExcel struct {
  11. Offset int
  12. Row int
  13. Title string //标题
  14. Excel *excelize.File
  15. SheetName string
  16. AlignCenterStyle int
  17. Content *model.PurchaseBill
  18. Signatures []*model.Signature
  19. }
  20. func (b *ProcessBillExcel) drawTitle() error {
  21. tileIndex := b.Offset + 1
  22. startCell := fmt.Sprintf("A%d", tileIndex)
  23. err := b.Excel.MergeCell(b.SheetName, startCell, fmt.Sprintf("I%d", tileIndex))
  24. if err != nil {
  25. return err
  26. }
  27. style, err := b.Excel.NewStyle(&excelize.Style{
  28. Alignment: &excelize.Alignment{Horizontal: "center", Vertical: "center"},
  29. Font: &excelize.Font{Bold: true, Size: 18}})
  30. if err != nil {
  31. return err
  32. }
  33. err = b.Excel.SetCellStyle(b.SheetName, startCell, startCell, style)
  34. if err != nil {
  35. return err
  36. }
  37. b.Excel.SetRowHeight(b.SheetName, tileIndex, 23)
  38. b.Excel.SetCellValue(b.SheetName, startCell, b.Title)
  39. return nil
  40. }
  41. func (b *ProcessBillExcel) drawSubTitles() error {
  42. row := b.Offset + 2
  43. styleLeft, err := b.Excel.NewStyle(&excelize.Style{
  44. Alignment: &excelize.Alignment{Horizontal: "left", Vertical: "center"},
  45. Font: &excelize.Font{Size: 11}})
  46. if err != nil {
  47. return err
  48. }
  49. styleRight, err := b.Excel.NewStyle(&excelize.Style{
  50. Alignment: &excelize.Alignment{Horizontal: "right", Vertical: "center"},
  51. Font: &excelize.Font{Size: 11}})
  52. if err != nil {
  53. return err
  54. }
  55. var drawLeft = func(rowIndex int, value string) error {
  56. //左边1
  57. left1Cell := fmt.Sprintf("A%d", rowIndex)
  58. err = b.Excel.MergeCell(b.SheetName, left1Cell, fmt.Sprintf("E%d", rowIndex))
  59. if err != nil {
  60. return err
  61. }
  62. err = b.Excel.SetCellStyle(b.SheetName, left1Cell, left1Cell, styleLeft)
  63. if err != nil {
  64. return err
  65. }
  66. b.Excel.SetCellValue(b.SheetName, left1Cell, value)
  67. return nil
  68. }
  69. var drawRight = func(rowIndex int, value string) error {
  70. right1Cell := fmt.Sprintf("F%d", rowIndex)
  71. err = b.Excel.MergeCell(b.SheetName, right1Cell, fmt.Sprintf("I%d", rowIndex))
  72. if err != nil {
  73. return err
  74. }
  75. err = b.Excel.SetCellStyle(b.SheetName, right1Cell, right1Cell, styleRight)
  76. if err != nil {
  77. return err
  78. }
  79. b.Excel.SetCellValue(b.SheetName, right1Cell, value)
  80. return nil
  81. }
  82. //第一行
  83. drawLeft(row, "类别:"+b.Content.Type)
  84. drawRight(row, "单号:"+b.Content.SerialNumber)
  85. b.Excel.SetRowHeight(b.SheetName, row, 21)
  86. //第二行
  87. drawLeft(row+1, "供应商名称:"+b.Content.Supplier)
  88. timeformat := b.Content.CreateTime.Local().Format("2006年01月02号 15:04:05")
  89. drawRight(row+1, "下单时间:"+timeformat)
  90. b.Excel.SetRowHeight(b.SheetName, row+1, 21)
  91. //第三行
  92. drawLeft(row+2, "产品名称:"+b.Content.ProductName)
  93. status := ""
  94. if b.Content.Status == "complete" {
  95. status = fmt.Sprintf("已完成(%d)", b.Content.ConfirmCount)
  96. }
  97. if b.Content.Status == "created" {
  98. status = "进行中"
  99. }
  100. drawRight(row+2, "状态:"+status)
  101. b.Excel.SetRowHeight(b.SheetName, row+2, 21)
  102. return nil
  103. }
  104. func (b *ProcessBillExcel) drawTableTitle() error {
  105. row := b.Offset + 5
  106. // 品名 规格 数量 单位 单价 金额
  107. var drawCol = func(prefix string, value string) error {
  108. left1Cell := fmt.Sprintf("%s%d", prefix, row)
  109. left2Cell := fmt.Sprintf("%s%d", prefix, row+1)
  110. err := b.Excel.MergeCell(b.SheetName, left1Cell, left2Cell)
  111. if err != nil {
  112. return err
  113. }
  114. err = b.Excel.SetCellStyle(b.SheetName, left1Cell, left2Cell, b.AlignCenterStyle)
  115. if err != nil {
  116. return err
  117. }
  118. return b.Excel.SetCellValue(b.SheetName, left1Cell, value)
  119. }
  120. drawCol("A", "采购项目")
  121. drawCol("B", "规格")
  122. drawCol("C", "数量")
  123. drawCol("D", "单位")
  124. drawCol("E", "单价")
  125. drawCol("F", "预算金额")
  126. drawCol("G", "实际金额")
  127. drawCol("H", "交货时间")
  128. drawCol("I", "备注")
  129. return nil
  130. }
  131. func (b *ProcessBillExcel) drawTableContent() error {
  132. row := b.Offset + 7
  133. b.Row = row
  134. var DrawRow = func(rowIndex int, values ...string) {
  135. charas := []string{"A", "B", "C", "D", "E", "F", "G", "H", "I"}
  136. for i, c := range charas {
  137. v := ""
  138. if i < len(values) {
  139. v = values[i]
  140. }
  141. b.Excel.SetCellValue(b.SheetName, fmt.Sprintf("%s%d", c, rowIndex), v)
  142. val2Cel := fmt.Sprintf("%s%d", c, rowIndex)
  143. b.Excel.SetCellStyle(b.SheetName, val2Cel, val2Cel, b.AlignCenterStyle)
  144. b.Excel.SetRowHeight(b.SheetName, rowIndex, 21)
  145. }
  146. }
  147. ps := b.Content.Process
  148. if ps != nil {
  149. deliveryTime := ps.DeliveryTime.Local().Format("2006-01-02")
  150. confirmCount := "-"
  151. realAmount := "-"
  152. // 预算金额
  153. budgetAmount := fmt.Sprintf("%.2f", float64(ps.Count)*ps.Price)
  154. b.FormatToEmpty(&budgetAmount)
  155. if b.Content.Status == "complete" {
  156. // 实际完成数
  157. confirmCount = fmt.Sprintf("%d", b.Content.ConfirmCount)
  158. b.FormatToEmpty(&confirmCount)
  159. // 实际金额
  160. realAmount = fmt.Sprintf("%.2f", float64(b.Content.ConfirmCount)*ps.Price)
  161. b.FormatToEmpty(&realAmount)
  162. }
  163. // 采购项目 规格 数量 单位 单价 预算金额 实际金额 交货时间 备注
  164. count := fmt.Sprintf("%d", ps.Count)
  165. price := fmt.Sprintf("%.2f", ps.Price)
  166. b.FormatToEmpty(&price)
  167. DrawRow(row, ps.Name, ps.Norm, count, ps.Unit, price, budgetAmount, realAmount, deliveryTime, ps.Remark)
  168. row++
  169. b.Row++
  170. }
  171. return nil
  172. }
  173. func (b *ProcessBillExcel) drawTableFooter() error {
  174. left1Cell := fmt.Sprintf("A%d", b.Row)
  175. border := []excelize.Border{
  176. {Type: "top", Style: 1, Color: "000000"},
  177. {Type: "left", Style: 1, Color: "000000"},
  178. {Type: "right", Style: 1, Color: "000000"},
  179. {Type: "bottom", Style: 1, Color: "000000"},
  180. }
  181. styleLeft, _ := b.Excel.NewStyle(&excelize.Style{
  182. Alignment: &excelize.Alignment{Horizontal: "left", Vertical: "center"},
  183. Border: border,
  184. })
  185. b.Excel.SetCellStyle(b.SheetName, left1Cell, left1Cell, styleLeft)
  186. b.Excel.SetCellValue(b.SheetName, left1Cell, "送货地址:")
  187. addCel := fmt.Sprintf("B%d", b.Row)
  188. b.Excel.MergeCell(b.SheetName, addCel, fmt.Sprintf("E%d", b.Row))
  189. b.Excel.SetCellStyle(b.SheetName, addCel, fmt.Sprintf("E%d", b.Row), styleLeft)
  190. b.Excel.SetCellValue(b.SheetName, addCel, b.Content.SendTo)
  191. sureCel := fmt.Sprintf("F%d", b.Row)
  192. b.Excel.MergeCell(b.SheetName, sureCel, fmt.Sprintf("I%d", b.Row))
  193. b.Excel.SetCellStyle(b.SheetName, sureCel, fmt.Sprintf("I%d", b.Row), styleLeft)
  194. b.Excel.SetCellValue(b.SheetName, sureCel, "供应商签字:")
  195. b.Excel.SetRowHeight(b.SheetName, b.Row, 30)
  196. return nil
  197. }
  198. func (b *ProcessBillExcel) drawRemark() error {
  199. // 填备注
  200. remarkTitleCell := fmt.Sprintf("A%d", b.Row)
  201. b.Excel.SetCellValue(b.SheetName, remarkTitleCell, "备注:")
  202. b.Row++
  203. remarkContentScell := fmt.Sprintf("A%d", b.Row)
  204. // 预留五行备注内容 // TODO 获取内容换行符个数然后动态确定内容占用多少行单元格
  205. b.Row += 5
  206. remarkContentEcell := fmt.Sprintf("L%d", b.Row)
  207. b.Excel.MergeCell(b.SheetName, remarkContentScell, remarkContentEcell)
  208. b.Excel.SetCellValue(b.SheetName, remarkContentScell, b.Content.Remark)
  209. return nil
  210. }
  211. func (b *ProcessBillExcel) drawTableSignature() error {
  212. b.Row += 2
  213. style1, _ := b.Excel.NewStyle(&excelize.Style{
  214. Alignment: &excelize.Alignment{Horizontal: "center", Vertical: "center"},
  215. // Border: border,
  216. })
  217. // 制单人
  218. billUserCell := fmt.Sprintf("A%d", b.Row+3)
  219. b.Excel.SetCellValue(b.SheetName, billUserCell, "制单人:")
  220. billUservCell := fmt.Sprintf("B%d", b.Row+3)
  221. b.Excel.SetCellValue(b.SheetName, billUservCell, b.Content.UserName)
  222. fontCell := fmt.Sprintf("E%d", b.Row)
  223. imageCell1 := fmt.Sprintf("F%d", b.Row)
  224. imageCell2 := fmt.Sprintf("H%d", b.Row)
  225. eRow := b.Row + 2
  226. b.Excel.MergeCell(b.SheetName, fontCell, fmt.Sprintf("E%d", eRow))
  227. b.Excel.SetCellStyle(b.SheetName, fontCell, fontCell, style1)
  228. b.Excel.SetCellValue(b.SheetName, fontCell, "审核签字:")
  229. b.Excel.MergeCell(b.SheetName, imageCell1, fmt.Sprintf("F%d", eRow))
  230. b.Excel.SetCellStyle(b.SheetName, imageCell1, imageCell1, style1)
  231. b.Excel.SetCellValue(b.SheetName, imageCell1, "")
  232. b.Excel.MergeCell(b.SheetName, imageCell2, fmt.Sprintf("H%d", eRow))
  233. b.Excel.SetCellStyle(b.SheetName, imageCell2, imageCell1, style1)
  234. b.Excel.SetCellValue(b.SheetName, imageCell2, "")
  235. // 状态为已审核时,签字
  236. // `{
  237. // "x_scale": 0.12,
  238. // "y_scale": 0.12,
  239. // "x_offset": 30,
  240. // "print_obj": true,
  241. // "lock_aspect_ratio": false,
  242. // "locked": false,
  243. // "positioning": "oncell"
  244. // }`
  245. if b.Content.Reviewed == 1 {
  246. imageCells := []string{imageCell1, imageCell2}
  247. if len(b.Signatures) > 0 {
  248. for k, sign := range b.Signatures {
  249. b.Excel.AddPicture(b.SheetName, imageCells[k], sign.Path, sign.Format)
  250. }
  251. }
  252. }
  253. return nil
  254. }
  255. func (b *ProcessBillExcel) Draws() {
  256. b.drawTitle()
  257. b.drawSubTitles()
  258. b.drawTableTitle()
  259. b.drawTableContent()
  260. b.drawTableFooter()
  261. b.drawRemark()
  262. b.drawTableSignature()
  263. }
  264. func NewProcessBill(f *excelize.File) *ProcessBillExcel {
  265. border := []excelize.Border{
  266. {Type: "top", Style: 1, Color: "000000"},
  267. {Type: "left", Style: 1, Color: "000000"},
  268. {Type: "right", Style: 1, Color: "000000"},
  269. {Type: "bottom", Style: 1, Color: "000000"},
  270. }
  271. styleLeft, _ := f.NewStyle(&excelize.Style{
  272. Alignment: &excelize.Alignment{Horizontal: "center", Vertical: "center"},
  273. Border: border,
  274. Font: &excelize.Font{Size: 10},
  275. })
  276. b := &ProcessBillExcel{
  277. Title: "原材料采购单",
  278. SheetName: "Sheet1",
  279. Excel: f,
  280. Offset: 0,
  281. AlignCenterStyle: styleLeft,
  282. Signatures: make([]*model.Signature, 0),
  283. }
  284. f.SetColWidth(b.SheetName, "A", "I", 14)
  285. f.SetPageMargins(b.SheetName, excelize.PageMarginTop(0), excelize.PageMarginLeft(0), excelize.PageMarginRight(0))
  286. return b
  287. }
  288. func (b *ProcessBillExcel) FormatToEmpty(str *string) {
  289. if *str == "0" || *str == "0.00" {
  290. *str = ""
  291. }
  292. }
  293. func (b *ProcessBillExcel) PrintPurchType() string {
  294. return "process"
  295. }
  296. func (b *ProcessBillExcel) SetContent(content *model.PurchaseBill) {
  297. b.Content = content
  298. }
  299. func (b *ProcessBillExcel) SetTitle(title string) {
  300. b.Title = title
  301. }
  302. func (b *ProcessBillExcel) SetSignatures(sign []*model.Signature) {
  303. b.Signatures = sign
  304. }