官网文档：
内链查询
查 site_target_site_group 和 site_target 表中都存在的数据总数
//SELECT count(st.id) from site_target st
//INNER JOIN site_target_site_group tg on tg.site_target_id = st.id
//WHERE tg.site_group_id = 77;
//output count(st.id) 6
var siteCount int64
e.Orm.Table("site_target as t").
Select("count(t.id)").
Joins("inner join site_target_site_group g on g.site_target_id = t.id").
Where("g.site_group_id = ?", 77).
Count(&siteCount)聚合计算查询
type SalesInfoReportSums struct {
SalesUnitPriceSum float64
AfterTaxPriceSum float64
SalesGrossProfitSum float64
OtherExpensesSum float64
}
func (salesInfoService *SalesInfoService) GetSalesInfoInfoListReportPrice(info stockReq.SalesInfoSearchReport, userToken erp.TokenUser) (report SalesInfoReportSums) {
if userToken.Organization_id == 0 {
return
}
// 创建db
db := global.GVA_DB.Model(&stock.SalesInfo{})
// 如果有条件搜索 下方会自动创建搜索语句
db.Joins("left join erp_sales on erp_sales.id=erp_sales_info.sales_id")
db.Joins("left join fa_org_products_main on fa_org_products_main.id=erp_sales_info.main_id")
db.Joins("left join fa_org_products_specs on fa_org_products_specs.id=erp_sales_info.specs_id")
db.Joins("left join fa_org_products_main_stock on fa_org_products_main_stock.id=erp_sales_info.inventory_id")
db = db.Where("erp_sales_info.organization_id", userToken.Organization_id)
db = getWhere(info, db)
// db = db.Pluck("sum(erp_sales_info.quantity_used*erp_sales_info.sales_unit_price) sales_unit_price_sum", &sales_unit_price_sum)
db.Select("sum(erp_sales_info.quantity_used*erp_sales_info.sales_unit_price) sales_unit_price_sum," +
"sum(erp_sales_info.quantity_used*erp_sales_info.after_tax_price) after_tax_price_sum," +
"sum((erp_sales_info.sales_unit_price-erp_sales_info.after_tax_price)*erp_sales_info.quantity_used) sales_gross_profit_sum," +
"sum(erp_sales.other_expenses) other_expenses_sum").
Scan(&report)
return report
}关连子表查询
// GetStockMainWarningInfoList 分页获取StockMainWarning记录
// Author [piexlmax](https://github.com/piexlmax)
func (stockMainWarningService *StockMainWarningService) GetStockMainWarningInfoList(info stockReq.StockMainWarningSearch, userToken erp.TokenUser) (list []stock.StockMainWarningInfo, total int64, err error) {
if userToken.Organization_id == 0 {
return list, total, errors.New("organization_id empty")
}
limit := info.PageSize
offset := info.PageSize * (info.Page - 1)
// 创建db
amwModel := stock.StockMainWarning{}
db := global.GVA_DB.Table(fmt.Sprintf("%s as smw", amwModel.TableName()))
db = db.Joins("left join fa_org_products_main as main on main.id=smw.main_id")
db = db.Joins("left join fa_org_products_specs as specs on specs.id=smw.specs_id")
list = make([]stock.StockMainWarningInfo, 0)
//var stockMainWarnings []stock.StockMainWarning
// 如果有条件搜索 下方会自动创建搜索语句
db = db.Where("smw.organization_id", userToken.Organization_id)
db = db.Where("main.organization_id", userToken.Organization_id)
db = db.Where("main.state", product.MainStateEnable)
var warehouseids []int
if info.UserWarehouseRules != nil && info.WarehouseId == 0 && info.WarehouseIds == nil {
if len(info.UserWarehouseRules) > 0 {
for _, warehouseId := range info.UserWarehouseRules {
warehouseids = append(warehouseids, warehouseId)
}
} else {
warehouseids = make([]int, 0)
}
}
if info.WarehouseIds != nil && len(info.WarehouseIds) > 0 {
warehouseids = append(warehouseids, info.WarehouseIds...)
}
if warehouseids != nil {
db = db.Where("smw.warehouse_id in (?)", warehouseids)
}
if info.WarehouseId != 0 {
db = db.Where("smw.warehouse_id", info.WarehouseId)
}
if info.MainId != 0 {
db = db.Where("smw.main_id", info.MainId)
}
if info.SpecsId != 0 {
db = db.Where("smw.specs_id", info.SpecsId)
}
if info.ProductName != "" {
db = db.Where("main.product_name like ?", "%"+info.ProductName+"%")
}
if info.BarCode != "" {
db = db.Where("specs.bar_code", info.BarCode)
}
if info.SpecificationModel != "" {
db = db.Where("specs.specification_model", info.SpecificationModel)
}
if info.ProductNumber != "" {
db = db.Where("specs.product_number", info.ProductNumber)
}
/*需求：
四、库存预警逻辑：
a. 加入到库存预警列表中产品，如果该库存仓库中这种产品的库存低于“库存低储”或高于“库存高储”,或者不在仓库中,则erp首页库存预警有提醒，app端的库存预警也显示数据提醒，
b.中如果基础库中作废了，就不在预警了*/
// 统计出fa_org_products_main_stock表分组warehouse_id,specs_id后的stock_quantity 和normal_quantity 两字段分别之和， 结果存为ms表
// fa_org_products_main_stock_warning表关联ms表， 用ms表中normal_quantit(可用数量)和fa_org_products_main_stock_warning表中的low_inventory和high_inventory字段作比较，筛选出符合条件的记录
//SELECT
//ms.stock_quantity,
// ms.normal_quantity,
// smw.*
// FROM
//fa_org_products_main_stock_warning AS smw
//LEFT JOIN fa_org_products_main AS main ON main.id = smw.main_id
//LEFT JOIN fa_org_products_specs AS specs ON specs.id = smw.specs_id
//LEFT JOIN (SELECT organization_id,warehouse_id, specs_id,SUM( stock_quantity ) AS stock_quantity,SUM( normal_quantity ) AS normal_quantity
//FROM fa_org_products_main_stock
//WHERE deleted_at IS NULL
//GROUP BY organization_id, warehouse_id, specs_id ) ms
//ON ms.organization_id = smw.organization_id
//AND ms.specs_id = smw.specs_id
//AND ms.warehouse_id = smw.warehouse_id
//WHERE
//`smw`.`organization_id` = 99
//AND `main`.`organization_id` = 99
//AND `main`.`state` = 1
//AND ((
// ( ms.normal_quantity <= smw.low_inventory AND smw.low_inventory != 0 )
//OR ( ms.normal_quantity >= smw.high_inventory AND smw.high_inventory <> 0 )
//OR ms.normal_quantity IS NULL
//))
//ORDER BY
//smw.id DESC
//LIMIT 10
// 库存高存储低存储预警:0=全部，1=是(全部),2=低库存预警，3=高库存预警 stock_quantity 库存数量。 normal_quantity 可用数量
db = db.Select("ms.manufacturer_name,ms.stock_quantity,ms.normal_quantity,smw.*")
db.Joins(`LEFT JOIN (SELECT organization_id,warehouse_id,specs_id,manufacturer_name,SUM(stock_quantity) as stock_quantity,SUM(normal_quantity) as normal_quantity FROM fa_org_products_main_stock WHERE deleted_at IS NULL GROUP BY organization_id,warehouse_id,specs_id) ms
ON ms.organization_id = smw.organization_id AND ms.specs_id = smw.specs_id AND ms.warehouse_id = smw.warehouse_id `)
if info.StockQuantityWarning == 1 || info.StockQuantityWarning == 2 || info.StockQuantityWarning == 3 || info.IsWarning == 1 {
if info.StockQuantityWarning == 2 {
//db = db.Where("((ms.normal_quantity < smw.low_inventory and smw.low_inventory != 0 ) or ms.normal_quantity IS NULL ) ")
db = db.Where("(ms.normal_quantity < smw.low_inventory and smw.low_inventory != 0 ) ")
} else if info.StockQuantityWarning == 3 {
//db = db.Where("( (ms.normal_quantity > smw.high_inventory and smw.high_inventory <> 0 ) or ms.normal_quantity IS NULL )")
db = db.Where("(ms.normal_quantity > smw.high_inventory and smw.high_inventory <> 0 )")
} else {
//db = db.Where("( (ms.normal_quantity < smw.low_inventory and smw.low_inventory != 0 ) or (ms.normal_quantity > smw.high_inventory and smw.high_inventory <> 0 ) or ms.normal_quantity IS NULL )")
db = db.Where("( (ms.normal_quantity < smw.low_inventory and smw.low_inventory != 0 ) or (ms.normal_quantity > smw.high_inventory and smw.high_inventory <> 0 ) )")
}
}
err = db.Count(&total).Error
if err != nil {
return
}
err = db.Limit(limit).Offset(offset).
Preload("Main").Preload("Specs").Preload("Warehouse").
Order("smw.id desc").
Find(&list).Error
if err == nil {
for k, v := range list {
list[k].HighInventory = v.HighInventory
if v.NormalQuantity >= v.HighInventory {
list[k].StockQuantityTrans = v.NormalQuantity - v.HighInventory
} else if v.NormalQuantity <= v.LowInventory {
list[k].StockQuantityTrans = v.NormalQuantity - v.LowInventory
}
}
}
return list, total, err
}更新文件参考
