采购单列表导出 - API 接口
import (
'epur/model'
'epur/pkg/decorator'
'epur/pkg/logger'
'epur/pkg/struct/adminStruct'
'epur/pkg/utils'
'fmt'
'github.com/360EntSecGroup-Skylar/excelize'
'reflect'
)
// 导出采购单Excel
func PostPurchaseListExport(a *decorator.ApiBase, data *adminStruct.ExportPurchaseOrderRequest) error {
logger.AccessLogger.Info('PostPurchaseListExport....')
res := adminStruct.ExportPurchaseOrderInfoResponse{}
res.ResponseCommon = a.NewSuccessResponseCommon()
var poids []int64
for _, i := range data.Poid {
poids = append(poids, i)
}
query := a.Ts.Table('purchase_order_info a').
Select('a.arrival_status,a.reason,a.remark,s.sname,whi.wname,a.poid,u.user_name,'+
'a.paid,p.logistics_id,a.verify_status,a.ctime,a.order_no,a.arrival_num,a.sum_num,'+
'a.goods_sum_amt,a.pur_amt,a.other_amt,a.sum_amt,a.remark,whi.addr,whi.phone,whi.contacts').
Joins('left join warehouse_info whi on a.wid = whi.wid').
Joins('left join purchase_order_list b on a.poid=b.poid').
Joins(`left join goods_base g on g.gid=b.gid`).
Joins(`left join goods_sku gs on gs.gsid = b.skuid`).
Joins(`left join supplier_base s on s.sid=a.sid`).
Joins(`left join user_base u on u.uid=a.cuid`).
Joins('left join purchase_waybill p on p.poid=a.poid').
Group('a.poid').
Where('a.poid in ? ', poids)
utils.Error(query.Limit(a.Size).
Offset(a.Offset).
Find(&res.Data).Error)
g := []adminStruct.GoodsInfo2{}
utils.Error(a.Ts.Table('purchase_order_list b').
Select('b.poid,gs.sku,b.price,b.num,g.gname,g.image,b.polid,gs.specs').
Joins(`left join goods_base g on g.gid=b.gid`).
Joins(`left join goods_sku gs on gs.gsid = b.skuid`).
Where('b.poid in ? and b.status=?', poids, model.Normal).
Limit(a.Size).
Offset(a.Offset).
Find(&g).Error)
for i := range res.Data {
p := &res.Data[i]
for _, i2 := range g {
if i2.Poid == p.Poid {
p.Detail = append(p.Detail, i2)
}
}
}
w := []adminStruct.PurchaseWaybillList{}
utils.Error(a.Ts.Table('purchase_waybill').
Select('poid,logistics_firm,logistics_id').
Where('poid in ?', poids).
Find(&w).Error)
for i := range res.Data {
p := &res.Data[i]
for _, i2 := range w {
if i2.Poid == p.Poid {
p.WayDetail = append(p.WayDetail, i2)
}
}
}
for index, item := range res.Data {
res.Data[index].StatusText = GetStatusText(item.VerifyStatus, item.ArrivalStatus)
}
// 定义Excel表格列名和对应的字段名
var columns = []string{'采购单号', '状态文本', '供货商', '仓库', '贷款(元)', '运费(元)', '其他费用(元)', '应付(应付=货款+运费+其他)', '已付(元)', '商品SKU', '商品图片',
'规格', '单价(元)', '采购数量', '到货数量', '小计', '运单信息', '收货地址', '采购备注', '创建时间', '采购员'}
var fields = []string{'OrderNo', 'StatusText', 'Sname', 'Wname', 'GoodsSumAmt', 'PurAmt', 'OtherAmt', 'SumAmt', 'Paid', 'Sku', 'Image', 'Specs', 'Price', 'Num', 'ArrivalNum', 'Amt', 'SourcePlatform,LogisticsId,LogisticsFirm', 'Contacts,Phone,Addr', 'Remark,Reason', 'Ctime', 'UserName'}
// 创建Excel表格并设置列名
file := excelize.NewFile()
sheetName := '采购单列表'
index := file.NewSheet(sheetName)
file.SetActiveSheet(index)
for i, col := range columns {
char := 'A' + i
file.SetCellValue(sheetName, fmt.Sprintf('%c1', char), col)
}
// 将采购单数据写入Excel表格
for i, item := range res.Data {
rowIndex := i + 2
for j, field := range fields {
char := 'A' + j
if field == 'SourcePlatform,LogisticsId,LogisticsFirm' {
for _, info2 := range item.WayDetail {
sourcePlatform := item.SourcePlatform
logisticsId := info2.LogisticsId
logisticsFirm := info2.LogisticsFirm
value := fmt.Sprintf('%s(%s)(%s)', sourcePlatform, logisticsId, logisticsFirm)
file.SetCellValue(sheetName, fmt.Sprintf('%c%d', char, rowIndex), value)
rowIndex++
}
} else if field == 'Contacts,Phone,Addr' {
contacts := item.Contacts
phone := item.Phone
addr := item.Addr
value := fmt.Sprintf('%s(%s)(%s)', contacts, phone, addr)
file.SetCellValue(sheetName, fmt.Sprintf('%c%d', char, rowIndex), value)
} else if field == 'Remark,Reason' {
remark := item.Remark
reason := item.Reason
value := fmt.Sprintf('%s(%s)', remark, reason)
file.SetCellValue(sheetName, fmt.Sprintf('%c%d', char, rowIndex), value)
} else if field == 'Sku' {
// 遍历商品详情列表，将每个商品的SKU写入Excel表格
for _, info2 := range item.Detail {
value := info2.Sku
file.SetCellValue(sheetName, fmt.Sprintf('%c%d', char, rowIndex), value)
rowIndex++
}
} else if field == 'Image' {
// 遍历商品详情列表，将每个商品的图片写入Excel表格
for _, info2 := range item.Detail {
value := info2.Image
file.SetCellValue(sheetName, fmt.Sprintf('%c%d', char, rowIndex), value)
}
} else if field == 'Specs' {
// 遍历商品详情列表，将每个商品的图片写入Excel表格
for _, info2 := range item.Detail {
value := info2.Specs
file.SetCellValue(sheetName, fmt.Sprintf('%c%d', char, rowIndex), value)
}
} else if field == 'Price' {
// 遍历商品详情列表，将每个商品的图片写入Excel表格
for _, info2 := range item.Detail {
value := info2.Price
file.SetCellValue(sheetName, fmt.Sprintf('%c%d', char, rowIndex), value)
}
} else if field == 'Num' {
// 遍历商品详情列表，将每个商品的图片写入Excel表格
for _, info2 := range item.Detail {
value := info2.Num
file.SetCellValue(sheetName, fmt.Sprintf('%c%d', char, rowIndex), value)
}
} else if field == 'Amt' {
// 遍历商品详情列表，将每个商品的图片写入Excel表格
for _, info2 := range item.Detail {
value := info2.Amt
file.SetCellValue(sheetName, fmt.Sprintf('%c%d', char, rowIndex), value)
}
} else {
// 其他字段直接获取值并写入Excel表格
//if field == 'Sku' || field == 'Image' || field == 'Specs' || field == 'Price' || field == 'Num' || field == 'Amt' {
// for _, info2 := range item.Detail {
// sku := info2.Sku
// image := info2.Image
// specs := info2.Specs
// price := info2.Price
// num := info2.Num
// value := fmt.Sprintf('%s(%s)(%s)(%d)(%d)', sku, image, specs, price, num)
// //file.SetCellValue(sheetName, fmt.Sprintf('%c%d', char, rowIndex), sku)
// //file.SetCellValue(sheetName, fmt.Sprintf('%c%d', char, rowIndex), image)
// //file.SetCellValue(sheetName, fmt.Sprintf('%c%d', char, rowIndex), specs)
// //file.SetCellValue(sheetName, fmt.Sprintf('%c%d', char, rowIndex), price)
// //file.SetCellValue(sheetName, fmt.Sprintf('%c%d', char, rowIndex), num)
// file.SetCellValue(sheetName, fmt.Sprintf('%c%d', char, rowIndex), value)
// rowIndex++
// }
//
//}
value := reflect.ValueOf(item).FieldByName(field).Interface()
file.SetCellValue(sheetName, fmt.Sprintf('%c%d', char, rowIndex), value)
}
}
}
// 保存Excel文件
err := file.SaveAs('采购单列表.xlsx')
if err != nil {
return err
}
return nil
}
导出excel内容：// ExportPurchaseListExport exports the purchase order data as an Excel file
func ExportPurchaseListExport(data *adminStruct.ExportPurchaseOrderRequest) error {
// 1. Prepare the query to fetch data from the database
query := db.Table('purchase_order_info a').
Select('a.arrival_status,a.reason,a.remark,s.sname,whi.wname,a.poid,u.user_name,'+
'a.paid,p.logistics_id,a.verify_status,a.ctime,a.order_no,a.arrival_num,a.sum_num,'+
'a.goods_sum_amt,a.pur_amt,a.other_amt,a.sum_amt,a.remark,whi.addr,whi.phone,whi.contacts').
Joins('LEFT JOIN warehouse_info whi ON a.wid = whi.wid').
Joins('LEFT JOIN purchase_order_list b ON a.poid=b.poid').
Joins(`LEFT JOIN goods_base g ON g.gid=b.gid`).
Joins(`LEFT JOIN goods_sku gs ON gs.gsid = b.skuid`).
Joins(`LEFT JOIN supplier_base s ON s.sid=a.sid`).
Joins(`LEFT JOIN user_base u ON u.uid=a.cuid`).
Joins('LEFT JOIN purchase_waybill p ON p.poid=a.poid').
Group('a.poid').
Where('a.poid IN ? ', data.Poid)
// 2. Fetch the data from the database
var purchaseOrders []adminStruct.ExportPurchaseOrderInfoResponse
err := query.Find(&purchaseOrders).Error
if err != nil {
return err
}
// 3. Fetch the goods details for each purchase order
for i := range purchaseOrders {
p := &purchaseOrders[i]
var goods []adminStruct.GoodsInfo2
err = db.Table('purchase_order_list b').
Select('b.poid,gs.sku,b.price,b.num,g.gname,g.image,b.polid,gs.specs').
Joins(`LEFT JOIN goods_base g ON g.gid=b.gid`).
Joins(`LEFT JOIN goods_sku gs ON gs.gsid = b.skuid`).
Where('b.poid = ? AND b.status = ?', p.Poid, model.Normal).
Find(&goods).Error
if err != nil {
return err
}
p.Detail = goods
}
// 4. Fetch the logistics information for each purchase order
for i := range purchaseOrders {
p := &purchaseOrders[i]
var waybills []adminStruct.PurchaseWaybillList
err = db.Table('purchase_waybill').
Select('poid,logistics_firm,logistics_id').
Where('poid = ?', p.Poid).
Find(&waybills).Error
if err != nil {
return err
}
p.WayDetail = waybills
}
// 5. Set the text for the status of each purchase order
for i := range purchaseOrders {
p := &purchaseOrders[i]
p.StatusText = GetStatusText(p.VerifyStatus, p.ArrivalStatus)
}
// 6. Define the columns and fields for the Excel file
var columns = []string{'采购单号', '状态文本', '供货商', '仓库', '贷款(元)', '运费(元)', '其他费用(元)', '应付(应付=货款+运费+其他)', '已付(元)', '商品SKU', '商品图片',
'规格', '单价(元)', '采购数量', '到货数量', '小计', '运单信息', '收货地址', '采购备注', '创建时间', '采购员'}
var fields = []string{'OrderNo', 'StatusText', 'Sname', 'Wname', 'GoodsSumAmt', 'PurAmt', 'OtherAmt', 'SumAmt', 'Paid', 'Sku', 'Image', 'Specs', 'Price', 'Num', 'ArrivalNum', 'Amt', 'SourcePlatform,LogisticsId,LogisticsFirm', 'Contacts,Phone,Addr', 'Remark,Reason', 'Ctime', 'UserName'}
// 7. Create a new Excel file and set the column names
file := excelize.NewFile()
sheetName := '采购单列表'
index := file.NewSheet(sheetName)
file.SetActiveSheet(index)
for i, col := range columns {
char := 'A' + i
file.SetCellValue(sheetName, fmt.Sprintf('%c1', char), col)
}
// 8. Write the data to the Excel file
for i, item := range purchaseOrders {
rowIndex := i + 2
for j, field := range fields {
char := 'A' + j
if field == 'SourcePlatform,LogisticsId,LogisticsFirm' {
// Write the logistics information to the Excel file
for _, info2 := range item.WayDetail {
sourcePlatform := item.SourcePlatform
logisticsId := info2.LogisticsId
logisticsFirm := info2.LogisticsFirm
value := fmt.Sprintf('%s(%s)(%s)', sourcePlatform, logisticsId, logisticsFirm)
file.SetCellValue(sheetName, fmt.Sprintf('%c%d', char, rowIndex), value)
rowIndex++
}
} else if field == 'Contacts,Phone,Addr' {
// Write the shipping address to the Excel file
contacts := item.Contacts
phone := item.Phone
addr := item.Addr
value := fmt.Sprintf('%s(%s)(%s)', contacts, phone, addr)
file.SetCellValue(sheetName, fmt.Sprintf('%c%d', char, rowIndex), value)
} else if field == 'Remark,Reason' {
// Write the purchase order remark and reason to the Excel file
remark := item.Remark
reason := item.Reason
value := fmt.Sprintf('%s(%s)', remark, reason)
file.SetCellValue(sheetName, fmt.Sprintf('%c%d', char, rowIndex), value)
} else if field == 'Sku' {
// Write the SKU for each item in the purchase order to the Excel file
for _, info2 := range item.Detail {
value := info2.Sku
file.SetCellValue(sheetName, fmt.Sprintf('%c%d', char, rowIndex), value)
rowIndex++
}
} else if field == 'Image' {
// Write the image URL for each item in the purchase order to the Excel file
for _, info2 := range item.Detail {
value := info2.Image
file.SetCellValue(sheetName, fmt.Sprintf('%c%d', char, rowIndex), value)
}
} else if field == 'Specs' {
// Write the specs for each item in the purchase order to the Excel file
for _, info2 := range item.Detail {
value := info2.Specs
file.SetCellValue(sheetName, fmt.Sprintf('%c%d', char, rowIndex), value)
}
} else if field == 'Price' {
// Write the price for each item in the purchase order to the Excel file
for _, info2 := range item.Detail {
value := info2.Price
file.SetCellValue(sheetName, fmt.Sprintf('%c%d', char, rowIndex), value)
}
} else if field == 'Num' {
// Write the quantity for each item in the purchase order to the Excel file
for _, info2 := range item.Detail {
value := info2.Num
file.SetCellValue(sheetName, fmt.Sprintf('%c%d', char, rowIndex), value)
}
} else if field == 'Amt' {
// Write the total amount for each item in the purchase order to the Excel file
for _, info2 := range item.Detail {
value := info2.Amt
file.SetCellValue(sheetName, fmt.Sprintf('%c%d', char, rowIndex), value)
}
} else {
// Write the other fields directly to the Excel file
value := reflect.ValueOf(item).FieldByName(field).Interface()
file.SetCellValue(sheetName, fmt.Sprintf('%c%d', char, rowIndex), value)
}
}
}
// 9. Save the Excel file
err = file.SaveAs('采购单列表.xlsx')
if err != nil {
return err
}
return nil
}
原文地址: https://www.cveoy.top/t/topic/nj2M 著作权归作者所有。请勿转载和采集!