amount_calculation.go

package repository

import (
	"database/sql"
	"fmt"
)

// 按已结账单计算金额
func BillOfSettlement(db *sql.DB, t, formatSQL string, hospitalLocalId int) (float64, error) {
	var money float64
	err := db.QueryRow(`select ifnull(convert(sum(b.paymoney) / 1000, decimal(11,3)), 0) 
from his_bill as b
where b.delflag = 0
and b.billtype in (1,4,6,7)
and DATE_FORMAT(b.billtime,?) = ? 
and b.sys_hospital_id in (0,?) `, formatSQL, t, hospitalLocalId).Scan(&money)

	return money, err
}

// 已结账单中所卖出的商品的 成本
func BillByCost(db *sql.DB, t, formatSQL string, hospitalLocalId int, chainCode string) (float64, error) {

	isNew, err := ISNewStock(chainCode)
	if err != nil {
		return 0, err
	}

	var money float64

	if isNew == 1 {
		err = db.QueryRow(`select 
ifnull(convert(sum(c.quantity*g.costmoney) / 1000000, decimal(11,3)), 0) as money
from his_consumption as c
left join his_bill as b on b.id = c.his_bill_id
LEFT JOIN g_new_stock_count count on count.his_consumption_id = c.id and count.his_consumption_id>0 and count.delflag = 0
LEFT JOIN g_new_stock g on g.lot_number = count.lot_number
where c.delflag = 0
and b.delflag = 0
and c.meter_num = 0
and b.billtype in (1,4,6,7)
and DATE_FORMAT(b.paytime, ?) = ? 
and c.sys_hospital_id in (0,?) `, formatSQL, t, hospitalLocalId).Scan(&money)
	} else {
		err = db.QueryRow(`select 
ifnull(convert(sum(c.quantity*g.costmoney) / 1000000, decimal(11,3)), 0) as money
from his_consumption as c
left join his_bill as b on b.id = c.his_bill_id
left join g_stock as g on g.con_commodity_id = c.con_commodity_id
where c.delflag = 0
and b.delflag = 0
and c.meter_num = 0
and b.billtype in (1,4,6,7)
and DATE_FORMAT(b.paytime, ?) = ? 
and c.sys_hospital_id in (0,?) `, formatSQL, t, hospitalLocalId).Scan(&money)
	}

	return money, err
}

// 次卡充值
func MeterCardRecharge(db *sql.DB, t, formatSQL string, employeeID, hospitalLocalId int) (float64, error) {

	sqlStr := `select ifnull(convert(sum(d.money) / 1000, decimal(11,3)), 0) 
from his_metercard_detail as d
where d.delflag = 0
and d.eventtype = 2
and DATE_FORMAT(d.eventtime,?) = ?
and d.sys_hospital_id in (0,?) `

	if employeeID != 0 {
		sqlStr += fmt.Sprintf(` and d.recharge_employee_id = %v`, employeeID)
	}

	var money float64
	err := db.QueryRow(sqlStr, formatSQL, t, hospitalLocalId).Scan(&money)

	return money, err
}

// 会员卡充值
func ConsumerCardRecharge(db *sql.DB, t, formatSQL string) (float64, error) {
	var money float64
	err := db.QueryRow(`select 
ifnull(convert(sum(d.money) / 1000, decimal(11,3)), 0) as money
from his_card_detail as d
where d.delflag = 0
and d.eventtype = 3
and DATE_FORMAT(d.eventtime, ?) = ?`, formatSQL, t).Scan(&money)

	return money, err
}

// 押金充值
func DepositMoneyRecharge(db *sql.DB, t, formatSQL string) (float64, error) {
	var money float64
	err := db.QueryRow(`select 
ifnull(convert(sum(d.money) / 1000, decimal(11,3)), 0) as money
from his_deposit_detail as d
where d.delflag = 0
and d.eventtype = 1
and DATE_FORMAT(d.eventtime, ?) = ?`, formatSQL, t).Scan(&money)

	return money, err
}

// 已结账单中: 现金、支付宝、微信、银行卡 (会员卡、次卡、押金结算 不算其中)
func BillOfSettlementIncome(db *sql.DB, t, formatSQL string) (float64, error) {
	var money float64
	err := db.QueryRow(`select ifnull(convert(sum(p.money) / 1000, decimal(11,3)), 0) 
from his_bill as b
left join his_bill_pay as p on p.his_bill_id = b.id
where b.delflag = 0
and p.delflag = 0
and b.billtype in (1,4,6,7)
and p.paytype in (1,2,3,4,10)
and DATE_FORMAT(b.billtime,?) = ?`, formatSQL, t).Scan(&money)

	return money, err
}

// 销售出库单结算的总金额
func StockOutBill(db *sql.DB, t, formatSQL string) (float64, error) {
	var money float64
	err := db.QueryRow(`select 
ifnull(convert(sum(b.paymoney) / 1000, decimal(11,3)), 0) as money
from g_stockout_bill as b
where b.delflag = 0
and DATE_FORMAT(b.eventtime, ?) = ?`, formatSQL, t).Scan(&money)

	return money, err
}

// 采购退货单结算的总金额
func StockRetreatBill(db *sql.DB, t, formatSQL string) (float64, error) {
	var money float64
	err := db.QueryRow(`select 
ifnull(convert(sum(b.paymoney) / 1000, decimal(11,3)), 0) as money
from g_stockretreat_bill as b
where b.delflag = 0
and DATE_FORMAT(b.eventtime, ?) = ?`, formatSQL, t).Scan(&money)

	return money, err
}

// 预收款
func PreInFundBill(db *sql.DB, t, formatSQL string) (float64, error) {
	var money float64
	err := db.QueryRow(`select 
ifnull(convert(sum(p.money) / 1000, decimal(11,3)), 0) as money
from c_prein_fund as p
where p.delflag = 0
and p.type = 0
and DATE_FORMAT(p.createtime, ?) = ?`, formatSQL, t).Scan(&money)

	return money, err
}

// 退预收款
func RetreatPreInFundBill(db *sql.DB, t, formatSQL string) (float64, error) {
	var money float64
	err := db.QueryRow(`select 
ifnull(convert(sum(p.money) / 1000, decimal(11,3)), 0) as money
from c_prein_fund as p
where p.delflag = 0
and p.type = 1
and DATE_FORMAT(p.createtime, ?) = ?`, formatSQL, t).Scan(&money)

	return money, err
}

// 支出管理 - 收入单
func InChargeBill(db *sql.DB, t, formatSQL string) (float64, error) {
	var money float64
	err := db.QueryRow(`select 
ifnull(convert(sum(i.money) / 1000, decimal(11,3)), 0) as money
from c_incharge as i
where i.delflag = 0
and DATE_FORMAT(i.happentime, ?) = ?`, formatSQL, t).Scan(&money)

	return money, err
}

// 退押金
func RetreatDepositMoney(db *sql.DB, t, formatSQL string, hospitalLocalId int) (float64, error) {
	var money float64
	err := db.QueryRow(`select 
ifnull(convert(sum(d.money) / 1000, decimal(11,3)), 0) as money
from his_deposit_detail as d
where d.delflag = 0
and d.eventtype in (3)
and DATE_FORMAT(d.eventtime, ?) = ? 
and d.sys_hospital_id in (0,?)  `, formatSQL, t, hospitalLocalId).Scan(&money)

	return money, err
}

// 退会员卡

func RetreatConsumerCard(db *sql.DB, t, formatSQL string, hospitalLocalId int) (float64, error) {
	var money float64
	err := db.QueryRow(`select 
ifnull(convert(sum(d.money) / 1000, decimal(11,3)), 0) as money
from his_card_detail as d
where d.delflag = 0
and d.eventtype in (7,13)
and DATE_FORMAT(d.eventtime, ?) = ?
and d.sys_hospital_id in (0,?)  `, formatSQL, t, hospitalLocalId).Scan(&money)

	return money, err
}

// 退次卡

func RetreatMeterCard(db *sql.DB, t, formatSQL string, hospitalLocalId int) (float64, error) {
	var money float64
	err := db.QueryRow(`select 
ifnull(convert(sum(d.money) / 1000, decimal(11,3)), 0) as money
from his_metercard_detail as d
where d.delflag = 0
and d.eventtype in (4)
and DATE_FORMAT(d.eventtime, ?) = ?
and d.sys_hospital_id in (0,?)  `, formatSQL, t, hospitalLocalId).Scan(&money)

	return money, err
}

// 支出管理 - 支出单
func OutChargeBill(db *sql.DB, t, formatSQL string, hospitalLocalId int) (float64, error) {
	var money float64
	err := db.QueryRow(`select 
ifnull(convert(sum(o.money) / 1000, decimal(11,3)), 0) as money
from c_outcharge as o
where o.delflag = 0
and DATE_FORMAT(o.happentime, ?) = ?
and o.sys_hospital_id in (0,?)  `, formatSQL, t, hospitalLocalId).Scan(&money)

	return money, err
}

// 采购入库单结算的总金额
func StockInBill(db *sql.DB, t, formatSQL string, hospitalLocalId int) (float64, error) {
	var money float64
	err := db.QueryRow(`select 
ifnull(convert(sum(b.paymoney) / 1000, decimal(11,3)), 0) as money
from g_stockin_bill as b
where b.delflag = 0
and DATE_FORMAT(b.eventtime, ?) = ?
and b.sys_hospital_id in (0,?) `, formatSQL, t, hospitalLocalId).Scan(&money)

	return money, err
}

// 预付款
func PreOutFundBill(db *sql.DB, t, formatSQL string, hospitalLocalId int) (float64, error) {
	var money float64
	err := db.QueryRow(`select 
ifnull(convert(sum(p.money) / 1000, decimal(11,3)), 0) as money
from c_preout_fund as p
where p.delflag = 0
and p.type = 0
and DATE_FORMAT(p.createtime, ?) = ?
and p.sys_hospital_id in (0,?)  `, formatSQL, t, hospitalLocalId).Scan(&money)

	return money, err
}

// 退预付款
func RetreatPreOutFundBill(db *sql.DB, t, formatSQL string, hospitalLocalId int) (float64, error) {
	var money float64
	err := db.QueryRow(`select 
ifnull(convert(sum(p.money) / 1000, decimal(11,3)), 0) as money
from c_preout_fund as p
where p.delflag = 0
and p.type = 1
and DATE_FORMAT(p.createtime, ?) = ?
and p.sys_hospital_id in (0,?)  `, formatSQL, t, hospitalLocalId).Scan(&money)

	return money, err
}