plan-cost-excel.go 14 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428
  1. package api
  2. import (
  3. "fmt"
  4. "github.com/xuri/excelize/v2"
  5. "go.mongodb.org/mongo-driver/bson/primitive"
  6. )
  7. // 生产成本表
  8. type PlanCostExcel struct {
  9. Offset int
  10. Title string //标题
  11. Excel *excelize.File
  12. SheetName string
  13. AlignCenterStyle int
  14. // Content *model.ProductPlan
  15. Content *SupplierPlanCost
  16. }
  17. func (b *PlanCostExcel) drawTitle() error {
  18. tileIndex := b.Offset + 1
  19. startCell := fmt.Sprintf("A%d", tileIndex)
  20. err := b.Excel.MergeCell(b.SheetName, startCell, fmt.Sprintf("L%d", tileIndex))
  21. if err != nil {
  22. return err
  23. }
  24. style, err := b.Excel.NewStyle(&excelize.Style{
  25. Alignment: &excelize.Alignment{Horizontal: "center", Vertical: "center"},
  26. Font: &excelize.Font{Bold: true, Size: 18}})
  27. if err != nil {
  28. return err
  29. }
  30. err = b.Excel.SetCellStyle(b.SheetName, startCell, startCell, style)
  31. if err != nil {
  32. return err
  33. }
  34. b.Excel.SetRowHeight(b.SheetName, tileIndex, 23)
  35. b.Excel.SetCellValue(b.SheetName, startCell, b.Title)
  36. return nil
  37. }
  38. func (b *PlanCostExcel) drawTableTitle() error {
  39. row := b.Offset + 2
  40. //A采购项目 B规格(克) 尺寸C-D 数量E 单价F-G 交货时间H 备注I
  41. // A产品名称
  42. var drawCol = func(prefix string, value string) error {
  43. left1Cell := fmt.Sprintf("%s%d", prefix, row)
  44. left2Cell := fmt.Sprintf("%s%d", prefix, row+1)
  45. err := b.Excel.MergeCell(b.SheetName, left1Cell, left2Cell)
  46. if err != nil {
  47. return err
  48. }
  49. err = b.Excel.SetCellStyle(b.SheetName, left1Cell, left2Cell, b.AlignCenterStyle)
  50. if err != nil {
  51. return err
  52. }
  53. return b.Excel.SetCellValue(b.SheetName, left1Cell, value)
  54. }
  55. // var drawCol1 = func(prefix string, value1 string, value2 string) error {
  56. // topCell := fmt.Sprintf("%s%d", prefix, row)
  57. // bottomCell := fmt.Sprintf("%s%d", prefix, row+1)
  58. // b.Excel.SetCellStyle(b.SheetName, topCell, topCell, b.AlignCenterStyle)
  59. // b.Excel.SetCellValue(b.SheetName, topCell, value1)
  60. // b.Excel.SetCellStyle(b.SheetName, bottomCell, bottomCell, b.AlignCenterStyle)
  61. // b.Excel.SetCellValue(b.SheetName, bottomCell, value2)
  62. // return nil
  63. // }
  64. var drawCol2 = func(prefix1 string, prefix2 string, value1 string, value2 string, value3 string) error {
  65. left1Cell := fmt.Sprintf("%s%d", prefix1, row)
  66. left2Cell := fmt.Sprintf("%s%d", prefix2, row)
  67. err := b.Excel.MergeCell(b.SheetName, left1Cell, left2Cell)
  68. if err != nil {
  69. return err
  70. }
  71. if err != nil {
  72. fmt.Println(err)
  73. return err
  74. }
  75. err = b.Excel.SetCellStyle(b.SheetName, left1Cell, left2Cell, b.AlignCenterStyle)
  76. if err != nil {
  77. return err
  78. }
  79. b.Excel.SetCellValue(b.SheetName, left1Cell, value1)
  80. val2Cel := fmt.Sprintf("%s%d", prefix1, row+1)
  81. b.Excel.SetCellStyle(b.SheetName, val2Cel, val2Cel, b.AlignCenterStyle)
  82. b.Excel.SetCellValue(b.SheetName, val2Cel, value2)
  83. val3Cel := fmt.Sprintf("%s%d", prefix2, row+1)
  84. b.Excel.SetCellStyle(b.SheetName, val3Cel, val3Cel, b.AlignCenterStyle)
  85. b.Excel.SetCellValue(b.SheetName, val3Cel, value3)
  86. return nil
  87. }
  88. var drawCol3 = func(prefix1 string, prefix2 string, prefix3 string, value1 string, value2 string, value3 string, value4 string) error {
  89. left1Cell := fmt.Sprintf("%s%d", prefix1, row)
  90. // left2Cell := fmt.Sprintf("%s%d", prefix2, row)
  91. left3Cell := fmt.Sprintf("%s%d", prefix3, row)
  92. err := b.Excel.MergeCell(b.SheetName, left1Cell, left3Cell)
  93. if err != nil {
  94. return err
  95. }
  96. if err != nil {
  97. fmt.Println(err)
  98. return err
  99. }
  100. err = b.Excel.SetCellStyle(b.SheetName, left1Cell, left3Cell, b.AlignCenterStyle)
  101. if err != nil {
  102. return err
  103. }
  104. b.Excel.SetCellValue(b.SheetName, left1Cell, value1)
  105. val2Cel := fmt.Sprintf("%s%d", prefix1, row+1)
  106. b.Excel.SetCellStyle(b.SheetName, val2Cel, val2Cel, b.AlignCenterStyle)
  107. b.Excel.SetCellValue(b.SheetName, val2Cel, value2)
  108. val3Cel := fmt.Sprintf("%s%d", prefix2, row+1)
  109. b.Excel.SetCellStyle(b.SheetName, val3Cel, val3Cel, b.AlignCenterStyle)
  110. b.Excel.SetCellValue(b.SheetName, val3Cel, value3)
  111. val4Cel := fmt.Sprintf("%s%d", prefix3, row+1)
  112. b.Excel.SetCellStyle(b.SheetName, val4Cel, val4Cel, b.AlignCenterStyle)
  113. b.Excel.SetCellValue(b.SheetName, val4Cel, value4)
  114. return nil
  115. }
  116. drawCol("A", "产品配料名称")
  117. drawCol2("B", "C", "材料/工序", "材料", "工序")
  118. drawCol("D", "供应商名称")
  119. drawCol3("E", "F", "G", "规格", "厚度(纸克)", "长", "宽")
  120. drawCol("H", "单位")
  121. drawCol("I", "下单数量")
  122. drawCol("J", "单价(预算)")
  123. drawCol("K", "预算金额")
  124. drawCol("L", "汇总金额")
  125. return nil
  126. }
  127. func (b *PlanCostExcel) drawSupplierContent() error {
  128. supplierId, err := primitive.ObjectIDFromHex(b.Content.SupplierId)
  129. if err != nil {
  130. return err
  131. }
  132. row := b.Offset + 4
  133. comps := b.Content.Pack.Components
  134. var totalPlanPrice float32 = 0.00
  135. if len(comps) > 0 {
  136. for _, comp := range comps {
  137. var totalOrderRealPrice float32 = 0.00
  138. if len(comp.Mats) > 0 {
  139. startRow := 0
  140. for _, mat := range comp.Mats {
  141. if mat.Supplier.SupplierInfo != nil {
  142. if supplierId == mat.Supplier.SupplierInfo.Id {
  143. // 材料
  144. if startRow == 0 {
  145. startRow = row
  146. }
  147. supplierName := mat.Supplier.SupplierInfo.Name
  148. matHeigth := fmt.Sprintf("%d", mat.BatchSizeHeight)
  149. b.FormatToEmpty(&matHeigth)
  150. matWidth := fmt.Sprintf("%d", mat.BatchSizeWidth)
  151. b.FormatToEmpty(&matWidth)
  152. orderCount := fmt.Sprintf("%d", int(mat.Supplier.OrderCount))
  153. b.FormatToEmpty(&orderCount)
  154. orderPrice := fmt.Sprintf("%.2f", mat.Supplier.OrderPrice)
  155. b.FormatToEmpty(&orderPrice)
  156. totalOrderRealPrice += mat.Supplier.OrderRealPrice
  157. orderRealPrice := fmt.Sprintf("%.2f", mat.Supplier.OrderRealPrice)
  158. b.FormatToEmpty(&orderRealPrice)
  159. b.drawRow(row, "", mat.MatInfo.Name, "", supplierName, mat.MatInfo.Norm, matHeigth, matWidth, mat.MatInfo.Unit, orderCount, orderPrice, orderRealPrice, "")
  160. row++
  161. }
  162. }
  163. if len(mat.Crafts) > 0 {
  164. for _, craft := range mat.Crafts {
  165. if craft.Supplier.SupplierInfo != nil {
  166. // 工序
  167. if supplierId == craft.Supplier.SupplierInfo.Id {
  168. if startRow == 0 {
  169. startRow = row
  170. }
  171. craftSupplierName := craft.Supplier.SupplierInfo.Name
  172. carftOrderCount := fmt.Sprintf("%d", int(craft.Supplier.OrderCount))
  173. b.FormatToEmpty(&carftOrderCount)
  174. carftHeigth := fmt.Sprintf("%d", craft.BatchSizeHeight)
  175. b.FormatToEmpty(&carftHeigth)
  176. carftWidth := fmt.Sprintf("%d", craft.BatchSizeWidth)
  177. b.FormatToEmpty(&carftWidth)
  178. carftOrderPrice := fmt.Sprintf("%.2f", craft.Supplier.OrderPrice)
  179. b.FormatToEmpty(&carftOrderPrice)
  180. totalOrderRealPrice += craft.Supplier.OrderRealPrice
  181. carftOrderRealPrice := fmt.Sprintf("%.2f", craft.Supplier.OrderRealPrice)
  182. b.FormatToEmpty(&carftOrderRealPrice)
  183. b.drawRow(row, "", "", craft.CraftInfo.Name, craftSupplierName, craft.CraftInfo.Norm, carftHeigth, carftWidth, craft.CraftInfo.Unit, carftOrderCount, carftOrderPrice, carftOrderRealPrice, "")
  184. row++
  185. }
  186. }
  187. }
  188. }
  189. }
  190. if startRow != 0 {
  191. endRow := row - 1
  192. // 组件名字
  193. startACell := fmt.Sprintf("%s%d", "A", startRow)
  194. endACell := fmt.Sprintf("%s%d", "A", endRow)
  195. b.Excel.MergeCell(b.SheetName, startACell, endACell)
  196. err := b.Excel.SetCellStyle(b.SheetName, startACell, endACell, b.AlignCenterStyle)
  197. if err != nil {
  198. return err
  199. }
  200. b.Excel.SetCellValue(b.SheetName, startACell, comp.Name)
  201. fmt.Println("startACell:", startACell)
  202. fmt.Println("endACell:", endACell)
  203. fmt.Println("compName:", comp.Name)
  204. // 供应商组件汇总金额
  205. startLCell := fmt.Sprintf("%s%d", "L", startRow)
  206. endLCell := fmt.Sprintf("%s%d", "L", endRow)
  207. b.Excel.MergeCell(b.SheetName, startLCell, endLCell)
  208. err = b.Excel.SetCellStyle(b.SheetName, startLCell, endLCell, b.AlignCenterStyle)
  209. if err != nil {
  210. return err
  211. }
  212. compTotalPrice := fmt.Sprintf("%.2f", totalOrderRealPrice)
  213. b.FormatToEmpty(&compTotalPrice)
  214. b.Excel.SetCellValue(b.SheetName, startLCell, compTotalPrice)
  215. }
  216. }
  217. totalPlanPrice += totalOrderRealPrice
  218. }
  219. // 生产汇总金额
  220. startACell := fmt.Sprintf("%s%d", "A", row)
  221. endKCell := fmt.Sprintf("%s%d", "K", row)
  222. endLCell := fmt.Sprintf("%s%d", "L", row)
  223. b.Excel.MergeCell(b.SheetName, startACell, endKCell)
  224. b.Excel.SetCellStyle(b.SheetName, startACell, endKCell, b.AlignCenterStyle)
  225. b.Excel.SetCellValue(b.SheetName, startACell, "生产计划汇总金额")
  226. b.Excel.SetCellStyle(b.SheetName, endLCell, endLCell, b.AlignCenterStyle)
  227. planTotalPrice := fmt.Sprintf("%.2f", totalPlanPrice)
  228. b.FormatToEmpty(&planTotalPrice)
  229. b.Excel.SetCellValue(b.SheetName, endLCell, planTotalPrice)
  230. }
  231. return nil
  232. }
  233. func (b *PlanCostExcel) drawRow(rowIndex int, values ...string) {
  234. charas := []string{"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L"}
  235. for i, c := range charas {
  236. v := ""
  237. if i < len(values) {
  238. v = values[i]
  239. }
  240. b.Excel.SetCellValue(b.SheetName, fmt.Sprintf("%s%d", c, rowIndex), v)
  241. val2Cel := fmt.Sprintf("%s%d", c, rowIndex)
  242. b.Excel.SetCellStyle(b.SheetName, val2Cel, val2Cel, b.AlignCenterStyle)
  243. b.Excel.SetRowHeight(b.SheetName, rowIndex, 21)
  244. }
  245. }
  246. func (b *PlanCostExcel) drawAllContent() error {
  247. row := b.Offset + 4
  248. comps := b.Content.Pack.Components
  249. if len(comps) > 0 {
  250. for _, comp := range comps {
  251. if len(comp.Mats) > 0 {
  252. startRow := row
  253. for _, mat := range comp.Mats {
  254. // 材料
  255. supplierName := ""
  256. if mat.Supplier.SupplierInfo != nil {
  257. supplierName = mat.Supplier.SupplierInfo.Name
  258. }
  259. matHeigth := fmt.Sprintf("%d", mat.BatchSizeHeight)
  260. b.FormatToEmpty(&matHeigth)
  261. matWidth := fmt.Sprintf("%d", mat.BatchSizeWidth)
  262. b.FormatToEmpty(&matWidth)
  263. orderCount := fmt.Sprintf("%d", int(mat.Supplier.OrderCount))
  264. b.FormatToEmpty(&orderCount)
  265. orderPrice := fmt.Sprintf("%.2f", mat.Supplier.OrderPrice)
  266. b.FormatToEmpty(&orderPrice)
  267. orderRealPrice := fmt.Sprintf("%.2f", mat.Supplier.OrderRealPrice)
  268. b.FormatToEmpty(&orderRealPrice)
  269. b.drawRow(row, "", mat.MatInfo.Name, "", supplierName, mat.MatInfo.Norm, matHeigth, matWidth, mat.MatInfo.Unit, orderCount, orderPrice, orderRealPrice, "")
  270. row++
  271. if len(mat.Crafts) > 0 {
  272. // 工序
  273. for _, craft := range mat.Crafts {
  274. craftSupplierName := ""
  275. if craft.Supplier.SupplierInfo != nil {
  276. craftSupplierName = craft.Supplier.SupplierInfo.Name
  277. }
  278. carftOrderCount := fmt.Sprintf("%d", int(craft.Supplier.OrderCount))
  279. b.FormatToEmpty(&carftOrderCount)
  280. carftHeigth := fmt.Sprintf("%d", craft.BatchSizeHeight)
  281. b.FormatToEmpty(&carftHeigth)
  282. carftWidth := fmt.Sprintf("%d", craft.BatchSizeWidth)
  283. b.FormatToEmpty(&carftWidth)
  284. carftOrderPrice := fmt.Sprintf("%.2f", craft.Supplier.OrderPrice)
  285. b.FormatToEmpty(&carftOrderPrice)
  286. carftOrderRealPrice := fmt.Sprintf("%.2f", craft.Supplier.OrderRealPrice)
  287. b.FormatToEmpty(&carftOrderRealPrice)
  288. b.drawRow(row, "", "", craft.CraftInfo.Name, craftSupplierName, craft.CraftInfo.Norm, carftHeigth, carftWidth, craft.CraftInfo.Unit, carftOrderCount, carftOrderPrice, carftOrderRealPrice, "")
  289. row++
  290. }
  291. }
  292. }
  293. endRow := row - 1
  294. // 组件名字
  295. startACell := fmt.Sprintf("%s%d", "A", startRow)
  296. endACell := fmt.Sprintf("%s%d", "A", endRow)
  297. b.Excel.MergeCell(b.SheetName, startACell, endACell)
  298. err := b.Excel.SetCellStyle(b.SheetName, startACell, endACell, b.AlignCenterStyle)
  299. if err != nil {
  300. return err
  301. }
  302. b.Excel.SetCellValue(b.SheetName, startACell, comp.Name)
  303. // 组件汇总金额
  304. startLCell := fmt.Sprintf("%s%d", "L", startRow)
  305. endLCell := fmt.Sprintf("%s%d", "L", endRow)
  306. b.Excel.MergeCell(b.SheetName, startLCell, endLCell)
  307. err = b.Excel.SetCellStyle(b.SheetName, startLCell, endLCell, b.AlignCenterStyle)
  308. if err != nil {
  309. return err
  310. }
  311. compTotalPrice := fmt.Sprintf("%.2f", comp.TotalPrice)
  312. b.FormatToEmpty(&compTotalPrice)
  313. b.Excel.SetCellValue(b.SheetName, startLCell, compTotalPrice)
  314. }
  315. }
  316. // 生产汇总金额
  317. startACell := fmt.Sprintf("%s%d", "A", row)
  318. endKCell := fmt.Sprintf("%s%d", "K", row)
  319. endLCell := fmt.Sprintf("%s%d", "L", row)
  320. b.Excel.MergeCell(b.SheetName, startACell, endKCell)
  321. b.Excel.SetCellStyle(b.SheetName, startACell, endKCell, b.AlignCenterStyle)
  322. b.Excel.SetCellValue(b.SheetName, startACell, "生产计划汇总金额")
  323. b.Excel.SetCellStyle(b.SheetName, endLCell, endLCell, b.AlignCenterStyle)
  324. planTotalPrice := fmt.Sprintf("%.2f", b.Content.TotalPrice)
  325. b.FormatToEmpty(&planTotalPrice)
  326. b.Excel.SetCellValue(b.SheetName, endLCell, planTotalPrice)
  327. }
  328. return nil
  329. }
  330. func (b *PlanCostExcel) Draws() {
  331. b.drawTitle()
  332. b.drawTableTitle()
  333. if b.Content.SupplierId != "" {
  334. b.drawSupplierContent()
  335. } else {
  336. b.drawAllContent()
  337. }
  338. }
  339. func NewPlanCostExcel(f *excelize.File) *PlanCostExcel {
  340. border := []excelize.Border{
  341. {Type: "top", Style: 1, Color: "000000"},
  342. {Type: "left", Style: 1, Color: "000000"},
  343. {Type: "right", Style: 1, Color: "000000"},
  344. {Type: "bottom", Style: 1, Color: "000000"},
  345. }
  346. styleLeft, _ := f.NewStyle(&excelize.Style{
  347. Alignment: &excelize.Alignment{Horizontal: "center", Vertical: "center"},
  348. Border: border,
  349. Font: &excelize.Font{Size: 10},
  350. })
  351. b := &PlanCostExcel{
  352. Title: "生产成本表",
  353. SheetName: "Sheet1",
  354. Excel: f,
  355. Offset: 0,
  356. AlignCenterStyle: styleLeft,
  357. }
  358. f.SetColWidth(b.SheetName, "A", "D", 12)
  359. f.SetColWidth(b.SheetName, "E", "L", 10)
  360. f.SetPageMargins(b.SheetName, excelize.PageMarginTop(0), excelize.PageMarginLeft(0), excelize.PageMarginRight(0))
  361. return b
  362. }
  363. func (b *PlanCostExcel) FormatToEmpty(str *string) {
  364. if *str == "0" || *str == "0.00" {
  365. *str = ""
  366. }
  367. }