bill-purchase-excel.go 11 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419
  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 PurchaseBillExcel 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 *PurchaseBillExcel) drawTitle() error {
  21. marginLeft := excelize.PageMarginLeft(0.15)
  22. b.Excel.SetPageMargins(b.SheetName, marginLeft)
  23. tileIndex := b.Offset + 1
  24. startCell := fmt.Sprintf("A%d", tileIndex)
  25. err := b.Excel.MergeCell(b.SheetName, startCell, fmt.Sprintf("L%d", tileIndex))
  26. if err != nil {
  27. return err
  28. }
  29. style, err := b.Excel.NewStyle(&excelize.Style{
  30. Alignment: &excelize.Alignment{Horizontal: "center", Vertical: "center"},
  31. Font: &excelize.Font{Bold: true, Size: 18}})
  32. if err != nil {
  33. return err
  34. }
  35. err = b.Excel.SetCellStyle(b.SheetName, startCell, startCell, style)
  36. if err != nil {
  37. return err
  38. }
  39. b.Excel.SetRowHeight(b.SheetName, tileIndex, 23)
  40. b.Excel.SetCellValue(b.SheetName, startCell, b.Title)
  41. return nil
  42. }
  43. func (b *PurchaseBillExcel) drawSubTitles() error {
  44. row := b.Offset + 2
  45. styleLeft, err := b.Excel.NewStyle(&excelize.Style{
  46. Alignment: &excelize.Alignment{Horizontal: "left", Vertical: "center"},
  47. Font: &excelize.Font{Size: 11}})
  48. if err != nil {
  49. return err
  50. }
  51. styleRight, err := b.Excel.NewStyle(&excelize.Style{
  52. Alignment: &excelize.Alignment{Horizontal: "right", Vertical: "center"},
  53. Font: &excelize.Font{Size: 11}})
  54. if err != nil {
  55. return err
  56. }
  57. var drawLeft = func(rowIndex int, value string) error {
  58. //左边1
  59. left1Cell := fmt.Sprintf("A%d", rowIndex)
  60. err = b.Excel.MergeCell(b.SheetName, left1Cell, fmt.Sprintf("E%d", rowIndex))
  61. if err != nil {
  62. return err
  63. }
  64. err = b.Excel.SetCellStyle(b.SheetName, left1Cell, left1Cell, styleLeft)
  65. if err != nil {
  66. return err
  67. }
  68. b.Excel.SetCellValue(b.SheetName, left1Cell, value)
  69. return nil
  70. }
  71. var drawRight = func(rowIndex int, value string) error {
  72. right1Cell := fmt.Sprintf("F%d", rowIndex)
  73. err = b.Excel.MergeCell(b.SheetName, right1Cell, fmt.Sprintf("L%d", rowIndex))
  74. if err != nil {
  75. return err
  76. }
  77. err = b.Excel.SetCellStyle(b.SheetName, right1Cell, right1Cell, styleRight)
  78. if err != nil {
  79. return err
  80. }
  81. b.Excel.SetCellValue(b.SheetName, right1Cell, value)
  82. return nil
  83. }
  84. //第一行
  85. drawLeft(row, "类别:"+b.Content.Type)
  86. drawRight(row, "单号:"+b.Content.SerialNumber)
  87. b.Excel.SetRowHeight(b.SheetName, row, 21)
  88. //第二行
  89. drawLeft(row+1, "供应商名称:"+b.Content.Supplier)
  90. timeformat := b.Content.CreateTime.Local().Format("2006年01月02号 15:04:05")
  91. drawRight(row+1, "下单时间:"+timeformat)
  92. b.Excel.SetRowHeight(b.SheetName, row+1, 21)
  93. //第三行
  94. drawLeft(row+2, "产品名称:"+b.Content.ProductName)
  95. status := ""
  96. if b.Content.Status == "complete" {
  97. status = fmt.Sprintf("已完成(%d)", b.Content.ConfirmCount)
  98. }
  99. if b.Content.Status == "created" {
  100. status = "进行中"
  101. }
  102. drawRight(row+2, "状态:"+status)
  103. b.Excel.SetRowHeight(b.SheetName, row+2, 21)
  104. return nil
  105. }
  106. func (b *PurchaseBillExcel) drawTableTitle() error {
  107. row := b.Offset + 5
  108. //A采购项目 B规格(克) 尺寸C-D 数量E 单价F-G 交货时间H 备注I
  109. var drawCol = func(prefix string, value string) error {
  110. left1Cell := fmt.Sprintf("%s%d", prefix, row)
  111. left2Cell := fmt.Sprintf("%s%d", prefix, row+1)
  112. err := b.Excel.MergeCell(b.SheetName, left1Cell, left2Cell)
  113. if err != nil {
  114. return err
  115. }
  116. err = b.Excel.SetCellStyle(b.SheetName, left1Cell, left2Cell, b.AlignCenterStyle)
  117. if err != nil {
  118. return err
  119. }
  120. return b.Excel.SetCellValue(b.SheetName, left1Cell, value)
  121. }
  122. var drawCol2 = func(prefix1 string, prefix2 string, value1 string, value2 string, value3 string) error {
  123. left1Cell := fmt.Sprintf("%s%d", prefix1, row)
  124. left2Cell := fmt.Sprintf("%s%d", prefix2, row)
  125. err := b.Excel.MergeCell(b.SheetName, left1Cell, left2Cell)
  126. if err != nil {
  127. return err
  128. }
  129. if err != nil {
  130. fmt.Println(err)
  131. return err
  132. }
  133. err = b.Excel.SetCellStyle(b.SheetName, left1Cell, left2Cell, b.AlignCenterStyle)
  134. if err != nil {
  135. return err
  136. }
  137. b.Excel.SetCellValue(b.SheetName, left1Cell, value1)
  138. val2Cel := fmt.Sprintf("%s%d", prefix1, row+1)
  139. b.Excel.SetCellStyle(b.SheetName, val2Cel, val2Cel, b.AlignCenterStyle)
  140. b.Excel.SetCellValue(b.SheetName, val2Cel, value2)
  141. val3Cel := fmt.Sprintf("%s%d", prefix2, row+1)
  142. b.Excel.SetCellStyle(b.SheetName, val3Cel, val3Cel, b.AlignCenterStyle)
  143. b.Excel.SetCellValue(b.SheetName, val3Cel, value3)
  144. return nil
  145. }
  146. drawCol("A", "采购项目")
  147. drawCol("B", "规格(克)")
  148. drawCol("E", "下单数量")
  149. drawCol("F", "完成数量")
  150. drawCol2("C", "D", "尺寸(mm)", "长", "宽")
  151. drawCol2("C", "D", "尺寸(mm)", "长", "宽")
  152. unit1 := "-"
  153. unit2 := "-"
  154. // ??? for ragne
  155. if len(b.Content.Paper) > 0 {
  156. if b.Content.Paper[0].PriceUnit != "" {
  157. unit1 = b.Content.Paper[0].PriceUnit
  158. } else if b.Content.Paper[0].Price2Unit != "" {
  159. unit2 = b.Content.Paper[0].Price2Unit
  160. } else {
  161. unit1 = "元/张"
  162. unit2 = "元/吨"
  163. }
  164. if b.Content.Paper[0].PriceUnit != "" && b.Content.Paper[0].Price2Unit != "" {
  165. unit1 = b.Content.Paper[0].PriceUnit
  166. unit2 = b.Content.Paper[0].Price2Unit
  167. }
  168. }
  169. drawCol2("G", "H", "单价", unit1, unit2)
  170. drawCol("I", "预算金额")
  171. drawCol("J", "实际金额")
  172. drawCol("K", "交货时间")
  173. drawCol("L", "备注")
  174. return nil
  175. }
  176. func (b *PurchaseBillExcel) drawTableContent() error {
  177. row := b.Offset + 7
  178. b.Row = row
  179. var DrawRow = func(rowIndex int, values ...string) {
  180. charas := []string{"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L"}
  181. for i, c := range charas {
  182. v := ""
  183. if i < len(values) {
  184. v = values[i]
  185. }
  186. b.Excel.SetCellValue(b.SheetName, fmt.Sprintf("%s%d", c, rowIndex), v)
  187. val2Cel := fmt.Sprintf("%s%d", c, rowIndex)
  188. b.Excel.SetCellStyle(b.SheetName, val2Cel, val2Cel, b.AlignCenterStyle)
  189. b.Excel.SetRowHeight(b.SheetName, rowIndex, 21)
  190. }
  191. }
  192. papers := b.Content.Paper
  193. if len(papers) > 0 {
  194. for _, paper := range papers {
  195. deliveryTime := paper.DeliveryTime.Local().Format("2006-01-02")
  196. confirmCount := "-"
  197. realAmount := "-"
  198. price := fmt.Sprintf("%.3f", paper.Price)
  199. price2 := fmt.Sprintf("%.3f", paper.Price2)
  200. // 预算金额
  201. budgetAmount := fmt.Sprintf("%.3f", b.Content.BudgetAmount)
  202. b.FormatToEmpty(&budgetAmount)
  203. if b.Content.Status == "complete" {
  204. // 实际完成数
  205. confirmCount = fmt.Sprintf("%d", b.Content.ConfirmCount)
  206. b.FormatToEmpty(&confirmCount)
  207. // 实际金额
  208. realAmount = fmt.Sprintf("%.3f", b.Content.RealAmount)
  209. b.FormatToEmpty(&realAmount)
  210. }
  211. DrawRow(row, paper.Name, paper.Norm, paper.Height, paper.Width, fmt.Sprintf("%d", paper.OrderCount), confirmCount, price, price2, budgetAmount, realAmount, deliveryTime, paper.Remark)
  212. row++
  213. b.Row++
  214. }
  215. }
  216. return nil
  217. }
  218. func (b *PurchaseBillExcel) drawTableFooter() error {
  219. // row := b.Offset + 8
  220. row := b.Row
  221. left1Cell := fmt.Sprintf("A%d", row)
  222. border := []excelize.Border{
  223. {Type: "top", Style: 1, Color: "000000"},
  224. {Type: "left", Style: 1, Color: "000000"},
  225. {Type: "right", Style: 1, Color: "000000"},
  226. {Type: "bottom", Style: 1, Color: "000000"},
  227. }
  228. styleLeft, _ := b.Excel.NewStyle(&excelize.Style{
  229. Alignment: &excelize.Alignment{Horizontal: "left", Vertical: "center"},
  230. Border: border,
  231. })
  232. b.Excel.SetCellStyle(b.SheetName, left1Cell, left1Cell, styleLeft)
  233. b.Excel.SetCellValue(b.SheetName, left1Cell, "送货地址")
  234. addCel := fmt.Sprintf("B%d", row)
  235. b.Excel.MergeCell(b.SheetName, addCel, fmt.Sprintf("E%d", row))
  236. b.Excel.SetCellStyle(b.SheetName, addCel, fmt.Sprintf("E%d", row), styleLeft)
  237. b.Excel.SetCellValue(b.SheetName, addCel, b.Content.SendTo)
  238. sureCel := fmt.Sprintf("F%d", row)
  239. b.Excel.MergeCell(b.SheetName, sureCel, fmt.Sprintf("L%d", row))
  240. b.Excel.SetCellStyle(b.SheetName, sureCel, fmt.Sprintf("L%d", row), styleLeft)
  241. b.Excel.SetCellValue(b.SheetName, sureCel, "供应商签字:")
  242. b.Excel.SetRowHeight(b.SheetName, row, 21)
  243. return nil
  244. }
  245. func (b *PurchaseBillExcel) drawTableSignature() error {
  246. b.Row += 2
  247. row := b.Row
  248. style1, _ := b.Excel.NewStyle(&excelize.Style{
  249. Alignment: &excelize.Alignment{Horizontal: "center", Vertical: "center"},
  250. })
  251. // 制单人
  252. billUserCell := fmt.Sprintf("A%d", row+1)
  253. b.Excel.SetCellValue(b.SheetName, billUserCell, "制单人:")
  254. billUservCell := fmt.Sprintf("B%d", row+1)
  255. b.Excel.SetCellValue(b.SheetName, billUservCell, b.Content.UserName)
  256. fontCell := fmt.Sprintf("H%d", row)
  257. imageCell1 := fmt.Sprintf("I%d", row)
  258. imageCell2 := fmt.Sprintf("K%d", row)
  259. b.Excel.MergeCell(b.SheetName, fontCell, fmt.Sprintf("H%d", row+2))
  260. b.Excel.SetCellStyle(b.SheetName, fontCell, fontCell, style1)
  261. b.Excel.SetCellValue(b.SheetName, fontCell, "审核签字:")
  262. // 签字图片1 I10-J12
  263. b.Excel.MergeCell(b.SheetName, imageCell1, fmt.Sprintf("J%d", row+2))
  264. b.Excel.SetCellStyle(b.SheetName, imageCell1, imageCell1, style1)
  265. b.Excel.SetCellValue(b.SheetName, imageCell1, "")
  266. // 签字图片2 K10-L12
  267. b.Excel.MergeCell(b.SheetName, imageCell2, fmt.Sprintf("L%d", row+2))
  268. b.Excel.SetCellStyle(b.SheetName, imageCell2, imageCell1, style1)
  269. b.Excel.SetCellValue(b.SheetName, imageCell2, "")
  270. // 状态为已审核时,签字
  271. // `{
  272. // "x_scale": 0.12,
  273. // "y_scale": 0.12,
  274. // "x_offset": 30,
  275. // "print_obj": true,
  276. // "lock_aspect_ratio": false,
  277. // "locked": false,
  278. // "positioning": "oncell"
  279. // }`
  280. if b.Content.Reviewed == 1 {
  281. imageCells := []string{imageCell1, imageCell2}
  282. if len(b.Signatures) > 0 {
  283. for k, sign := range b.Signatures {
  284. b.Excel.AddPicture(b.SheetName, imageCells[k], sign.Path, sign.Format)
  285. }
  286. }
  287. }
  288. b.Excel.SetRowHeight(b.SheetName, row, 21)
  289. return nil
  290. }
  291. func (b *PurchaseBillExcel) Draws() {
  292. b.drawTitle()
  293. b.drawSubTitles()
  294. b.drawTableTitle()
  295. b.drawTableContent()
  296. b.drawTableFooter()
  297. b.drawTableSignature()
  298. }
  299. func NewPurchaseBill(f *excelize.File) *PurchaseBillExcel {
  300. border := []excelize.Border{
  301. {Type: "top", Style: 1, Color: "000000"},
  302. {Type: "left", Style: 1, Color: "000000"},
  303. {Type: "right", Style: 1, Color: "000000"},
  304. {Type: "bottom", Style: 1, Color: "000000"},
  305. }
  306. styleLeft, _ := f.NewStyle(&excelize.Style{
  307. Alignment: &excelize.Alignment{Horizontal: "center", Vertical: "center"},
  308. Border: border,
  309. Font: &excelize.Font{Size: 10},
  310. })
  311. b := &PurchaseBillExcel{
  312. Title: "原材料采购单",
  313. SheetName: "Sheet1",
  314. Excel: f,
  315. Offset: 0,
  316. AlignCenterStyle: styleLeft,
  317. Signatures: make([]*model.Signature, 0),
  318. }
  319. f.SetColWidth(b.SheetName, "A", "A", 12)
  320. f.SetColWidth(b.SheetName, "B", "K", 9.5)
  321. // f.SetColWidth(b.SheetName, "C", "H", 9.5)
  322. f.SetColWidth(b.SheetName, "L", "L", 10)
  323. f.SetPageMargins(b.SheetName, excelize.PageMarginTop(0), excelize.PageMarginLeft(0), excelize.PageMarginRight(0))
  324. return b
  325. }
  326. func (b *PurchaseBillExcel) FormatToEmpty(str *string) {
  327. if *str == "0" || *str == "0.000" {
  328. *str = ""
  329. }
  330. }
  331. func (b *PurchaseBillExcel) PrintPurchType() string {
  332. return "process"
  333. }
  334. func (b *PurchaseBillExcel) SetContent(content *model.PurchaseBill) {
  335. b.Content = content
  336. }
  337. func (b *PurchaseBillExcel) SetTitle(title string) {
  338. b.Title = title
  339. }
  340. func (b *PurchaseBillExcel) SetOffset(offset int) {
  341. b.Offset = offset
  342. }
  343. func (b *PurchaseBillExcel) SetSignatures(sign []*model.Signature) {
  344. b.Signatures = sign
  345. }