package repository

import (
	"database/sql"
	"errors"
	"fmt"
	"gin-vue-admin/models"
	"gin-vue-admin/utils"
	"strconv"
	"time"
)

func StockRes(db *sql.DB, t string, isNew, hospitalLocalId int) (interface{}, error) {

	var formatSQL string

	if len(t) == 10 {
		formatSQL = "%Y-%m-%d"
	} else if len(t) == 7 {
		formatSQL = "%Y-%m"
	} else if len(t) == 4 {
		formatSQL = "%Y"
	} else {
		return nil, errors.New("time error")
	}

	var (
		stockInSql  string
		stockOutSql string
		response    models.Stock
	)

	// 业务数据

	// 入库
	if isNew == 1 {
		stockInSql = `select
(select ifnull(convert(sum(d.quantity*d.payprice) / 1000000, decimal(11,3)), 0)
from g_new_stockinio_detail as d where d.delflag = 0 and i.id = d.new_stockin_io_id) as money
from g_new_stockin_io as i
where i.delflag = 0
and DATE_FORMAT(i.eventtime, ?) = ?  and i.sys_hospital_id = ? `
	} else {
		stockInSql = `select
(select ifnull(convert(sum(d.quantity*d.payprice) / 1000000, decimal(11,3)), 0)
from g_stockinio_detail as d where d.delflag = 0 and i.id = d.stockin_io_id) as money
from g_stockin_io as i
where i.delflag = 0
and DATE_FORMAT(i.eventtime, ?) = ? and i.sys_hospital_id = ? `
	}

	rows, err := db.Query(stockInSql, formatSQL, t, hospitalLocalId)
	if err != nil {
		return nil, err
	}

	for rows.Next() {
		var temp sql.NullFloat64
		err = rows.Scan(&temp)
		if err != nil {
			return nil, err
		}
		response.StockInIoNumber++
		response.StockInIoMoney += temp.Float64
	}

	// 出库
	if isNew == 1 {
		stockOutSql = `select 
(select ifnull(convert(sum(d.quantity*d.wholesale_price) / 1000000, decimal(11,3)), 0)
from g_new_stockoutio_detail as d where d.delflag = 0 and i.id = d.new_stockout_io_id) as money
from g_new_stockout_io as i
where i.delflag = 0
and DATE_FORMAT(i.eventtime, ?) = ? and i.sys_hospital_id = ?`
	} else {
		stockOutSql = `select 
(select ifnull(convert(sum(d.quantity*d.payprice) / 1000000, decimal(11,3)), 0)
from g_stockoutio_detail as d where d.delflag = 0 and i.id = d.stockout_io_id) as money
from g_stockout_io as i
where i.delflag = 0
and DATE_FORMAT(i.eventtime, ?) = ? and i.sys_hospital_id = ?`
	}

	rows, err = db.Query(stockOutSql, formatSQL, t, hospitalLocalId)
	if err != nil {
		return nil, err
	}

	for rows.Next() {
		var temp sql.NullFloat64
		err = rows.Scan(&temp)
		if err != nil {
			return nil, err
		}
		response.StockOutIoNumber++
		response.StockOutIoMoney += temp.Float64
	}

	// 热销商品
	commodity := make([]models.HotCommodity, 0)

	rows, err = db.Query(`select c.commodity_name, 
ifnull(convert(sum(c.quantity) / 1000, decimal(11,0)), 0) as quantity,
ifnull(convert(sum(c.quantity*c.payprice) / 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
where c.delflag = 0
and c.meter_num = 0
and b.billtype in (1,4,6,7)
and c.bussinesstype in (1,3,4,43,5,63,7,8,9,10,11,12,13)
and DATE_FORMAT(b.paytime, ?) = ?
and c.sys_hospital_id in (0,?) 
GROUP BY c.con_commodity_id
ORDER BY quantity desc, money desc
LIMIT 8`, formatSQL, t, hospitalLocalId)
	if err != nil {
		return nil, err
	}

	for rows.Next() {
		var temp models.HotCommodity
		err = rows.Scan(&temp.Name, &temp.Number, &temp.Money)
		if err != nil {
			return nil, err
		}
		commodity = append(commodity, temp)
	}

	response.HotCommodity = commodity

	return response, nil
}

func StockEarlyWarningTotalRes(db *sql.DB, isNew, hospitalLocalId int) (int64, error) {

	var (
		stockEarlyWarningTotalSql string
		total                     int64
	)

	if isNew == 1 {
		stockEarlyWarningTotalSql = fmt.Sprintf(`SELECT COUNT(0) FROM ( select count(0)
			from  g_new_stock as stock
			join con_commodity as commod on stock.con_commodity_id = commod.id
			where stock.delflag = 0 and commod.delflag = 0 and commod.isuse_stock = 1 and  stock.sys_hospital_id = ? 
			GROUP BY commod.id 	having SUM(stock.quantity) <= (SELECT stocklimit FROM  con_commodity WHERE  id = commod.id ) ) a`)
	} else {
		stockEarlyWarningTotalSql = `select count(*)
		from con_commodity as c
		join con_category as category1 on category1.id = c.top_category_id
		join con_category as category2 on category2.id = c.con_category_id
		left join g_stock as g on g.con_commodity_id = c.id and  g.sys_hospital_id = ?
		where c.delflag = 0 and c.isuse_stock = 1 and g.quantity <= c.stocklimit `
	}

	err := db.QueryRow(stockEarlyWarningTotalSql, hospitalLocalId).Scan(&total)

	return total, err
}

func StockEarlyWarningRes(db *sql.DB, page, pageSize int64, isNew, hospitalLocalId int) (interface{}, error) {

	var stockEarlyWarningSql string

	if isNew == 1 {
		stockEarlyWarningSql = fmt.Sprintf(`select category1._name, category2._name, commod._name, commod.unit, 
			convert(SUM(stock.quantity) / 1000, decimal(11,3))  as 'quantity',
			convert(commod.stocklimit / 1000, decimal(11,3)) as 'stocklimit'
			from  g_new_stock as stock
			join con_commodity as commod on stock.con_commodity_id = commod.id
			join con_category as category1 on category1.id = commod.top_category_id
			join con_category as category2 on category2.id = commod.con_category_id
			where stock.delflag = 0 and commod.delflag = 0 and commod.isuse_stock = 1 and  stock.sys_hospital_id = ? 
			GROUP BY commod.id 	having SUM(stock.quantity) <= (SELECT stocklimit FROM  con_commodity WHERE  id = commod.id ) limit ?,? `)
	} else {
		stockEarlyWarningSql = `select category1._name, category2._name, c._name, c.unit, 
		ifnull(convert(g.quantity / 1000, decimal(11,3)), 0) as quantity, convert(c.stocklimit / 1000, decimal(11,3)) as 'stocklimit'
		from con_commodity as c
		join con_category as category1 on category1.id = c.top_category_id
		join con_category as category2 on category2.id = c.con_category_id
		left join g_stock as g on g.con_commodity_id = c.id and  g.sys_hospital_id = ?
		where c.delflag = 0 and c.isuse_stock = 1 and g.quantity <= c.stocklimit limit ?,?`
	}

	rows, err := db.Query(stockEarlyWarningSql, hospitalLocalId, page, pageSize)
	if err != nil {
		return nil, err
	}

	response := make([]models.EarlyWarning, 0)

	for rows.Next() {
		var temp models.EarlyWarning
		err = rows.Scan(&temp.ProjectName, &temp.Type, &temp.Name, &temp.Unit, &temp.CurrentStock, &temp.StockLowerLimit)
		if err != nil {
			return nil, err
		}
		response = append(response, temp)
	}

	return response, nil
}

func CheckStockList(db *sql.DB, t string, employeeId, isNew int) (interface{}, error) {

	format, err := GetFormat(t)
	if err != nil {
		return nil, err
	}

	var (
		checkListSQL string
	)

	hospitalID, err := GetHospitalID(db, employeeId)
	if err != nil {
		return nil, err
	}

	if isNew == 1 {
		checkListSQL = `select id, _code, eventtime, description from g_new_stock_check where delflag = 0  and sys_hospital_id=? and  DATE_FORMAT(eventtime, ?) = ?`
	} else {
		checkListSQL = `select id, _code, eventtime, description from g_stock_check where delflag = 0  and sys_hospital_id=? and  DATE_FORMAT(eventtime, ?) = ? `
	}

	checkListSQL += " and creater = ? order by id desc"

	rows, err := db.Query(checkListSQL, hospitalID, format, t, employeeId)
	if err != nil {
		return nil, err
	}

	stockLists := make([]*models.CheckStockList, 0)

	for rows.Next() {
		s := new(models.CheckStockList)
		err = rows.Scan(&s.Id, &s.Code, &s.EventTime, &s.Description)
		if err != nil {
			return nil, err
		}
		stockLists = append(stockLists, s)
	}

	return stockLists, err
}

func SearchCommodity(db *sql.DB, chainCode, keywords string, employeeId int) (interface{}, error) {

	var (
		cl map[int][]models.CommodityBatchListAll
	)

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

	hospitalLocalId, err := QueryHospitalLocalIDByEmployeeLocalID(db, employeeId)
	if err != nil {
		return nil, err
	}

	isGeneral, err := CheckIsGeneralHospital(hospitalLocalId, chainCode, db)
	if err != nil {
		return nil, err
	}

	var querySql string

	if isNew == 1 {
		querySql = fmt.Sprintf(`
		SELECT
	commodity.id,
	commodity._name AS 'name',
			IFNULL(CONVERT (
			sum(stock.quantity) / 1000,
		DECIMAL ( 11, 3 )),0),
	commodity.con_category_id,commodity.top_category_id, commodity._code as 'code', commodity.encode, commodity._name as 'name', commodity.namepy, commodity.brand, commodity.isuse_alias, commodity.aliasname,
	commodity.standardname, commodity.standardcode, commodity.vender, commodity.format, commodity.unit, convert(buyprice / 1000, decimal(11,3)) as buyprice
FROM
	con_commodity commodity
	LEFT JOIN g_new_stock stock ON commodity.id = stock.con_commodity_id 
	AND stock.sys_hospital_id = ?
WHERE
	 commodity.isuse_stock = 1  and  commodity.delflag =0 and  (   commodity._name like '%s' or commodity._code like '%s' or commodity.encode like '%s'  ) `, "%"+keywords+"%", "%"+keywords+"%", "%"+keywords+"%")

	} else {
		querySql = fmt.Sprintf(`
		SELECT
	commodity.id,
	commodity._name AS 'name',
	ifnull(
		CONVERT (
			stock.quantity / 1000,
		DECIMAL ( 11, 3 )),
		0 
	) AS quantity  ,
	commodity.con_category_id,commodity.top_category_id, commodity._code as 'code', commodity.encode, commodity._name as 'name',
commodity.namepy, commodity.brand, commodity.isuse_alias, commodity.aliasname,
	commodity.standardname, commodity.standardcode, commodity.vender, commodity.format, commodity.unit, convert(buyprice / 1000, decimal(11,3)) as buyprice
FROM
	con_commodity commodity
	LEFT JOIN g_stock stock ON commodity.id = stock.con_commodity_id 
	AND stock.sys_hospital_id = ?
WHERE
	 commodity.isuse_stock = 1  and  commodity.delflag =0 and ( commodity._name like '%s' or commodity._code like '%s' or commodity.encode like '%s' ) `, "%"+keywords+"%", "%"+keywords+"%", "%"+keywords+"%")
	}

	if isGeneral {
		querySql += fmt.Sprintf(" and  commodity.sys_hospital_id in (0,%d)", hospitalLocalId)
	} else {
		querySql += fmt.Sprintf(" and commodity.sys_hospital_id = %d", hospitalLocalId)
	}

	if isNew == 1 {
		querySql += `  group by NULL,commodity.id  `
		cl, err = GetLotNumberList(db, hospitalLocalId)
		if err != nil {
			return nil, err
		}
	}

	commodityInfos := make([]*models.CommodityInfo, 0)

	rows, err := db.Query(querySql, hospitalLocalId)
	if err != nil {
		return nil, err
	}

	for rows.Next() {
		m := new(models.CommodityInfo)
		err = rows.Scan(&m.Id, &m.Name, &m.StockQuantity, &m.ConCategoryID, &m.TopCategoryID,
			&m.CommodityCode, &m.CommodityEncode, &m.CommodityName, &m.CommodityNamepy,
			&m.CommodityBrand, &m.CommodityIsuseAlias, &m.CommodityAliasname,
			&m.CommodityStandardname, &m.CommodityStandardcode, &m.CommodityVender, &m.CommdityFormat, &m.CommodityUnit, &m.Constprice)
		if err != nil {
			return nil, err
		}
		m.ConCommodityID = int64(m.Id)
		m.RealQuantity = m.StockQuantity

		if isNew == 1 {
			batchLists := cl[m.Id]
			m.CommodityBatchList = batchLists
			if batchLists == nil {
				m.CommodityBatchList = make([]models.CommodityBatchListAll, 0)
				m.CommodityBatchList = append(m.CommodityBatchList, models.CommodityBatchListAll{CommodityBatch: "无批次", Price: m.Constprice})
			}
		}

		commodityInfos = append(commodityInfos, m)
	}

	return commodityInfos, err

}

func GetLotNumberList(db *sql.DB, hospitalId int) (map[int][]models.CommodityBatchListAll, error) {

	querySql := `SELECT
       stock.con_commodity_id ,
       stock.commodity_batch,
	CONVERT (
		stock.costmoney / 1000,
		DECIMAL ( 11, 3 )) as  costmoney ,
		CONVERT (
			sum(stock.quantity / 1000),
			DECIMAL ( 11, 3 )) as sumquantity
FROM
	g_new_stock stock
	WHERE stock.sys_hospital_id = ?
	group by  stock.con_commodity_id ,stock.commodity_batch, stock.costmoney`

	lists := make([]models.CommodityBatchListAll, 0)

	row, err := db.Query(querySql, hospitalId)
	if err != nil {
		return nil, err
	}

	for row.Next() {
		var l models.CommodityBatchListAll
		err = row.Scan(&l.ConCommodityID, &l.CommodityBatch, &l.Price, &l.Quantity)
		if err != nil {
			return nil, err
		}
		if l.CommodityBatch == "" {
			l.CommodityBatch = "无批次"
		}
		if l.Quantity == 0 {
			continue
		}
		lists = append(lists, l)
	}

	c := make(map[int][]models.CommodityBatchListAll)

	for _, list := range lists {
		if c[list.ConCommodityID] == nil {
			c[list.ConCommodityID] = make([]models.CommodityBatchListAll, 0)
			c[list.ConCommodityID] = append(c[list.ConCommodityID], models.CommodityBatchListAll{Price: list.Price,
				Quantity:       list.Quantity,
				CommodityBatch: list.CommodityBatch,
				ConCommodityID: list.ConCommodityID})
		} else {
			c[list.ConCommodityID] = append(c[list.ConCommodityID], models.CommodityBatchListAll{Price: list.Price,
				Quantity:       list.Quantity,
				CommodityBatch: list.CommodityBatch,
				ConCommodityID: list.ConCommodityID})
		}
	}

	return c, err
}

func AddStockCheckStockList(db *sql.DB, p *models.StockChecks, userId int64) error {

	if len(p.StockCheckModels) <= 0 {
		return errors.New("盘库单不能为空")
	}

	hospitalLocalId, err := QueryHospitalLocalIDByEmployeeLocalID(db, int(userId))
	if err != nil {
		return err
	}

	hospitalId := int64(hospitalLocalId)

	tx, err := db.Begin()
	if err != nil {
		return err
	}

	checkSQL := `insert g_stock_check set creater = ?, sys_hospital_id = ?, _code = ?, eventtime = ?, description = ?`

	checkDetailSQL := `insert g_stockcheck_detail set creater = ?, sys_hospital_id = ?, stockcheck_id = ?, con_commodity_id = ?,
con_category_id = ?, con_category_name = ?, top_category_id = ?, commodity_code = ?, commodity_encode = ?,
commodity_name = ?, commodity_namepy = ?, commodity_brand = ?, commodity_isuse_alias = ?, commodity_aliasname = ?,
commodity_standardname = ?, commodity_standardcode = ?, commodity_vender = ?, commodity_format = ?, commodity_unit = ?,
constprice= ?*1000, actualquantity= ?*1000, firmquantity= ?*1000`

	checkStmt, err := tx.Prepare(checkSQL)
	if err != nil {
		return err
	}

	checkDetailStmt, err := tx.Prepare(checkDetailSQL)
	if err != nil {
		return err
	}

	// 获取code
	code, err := GetCodeByTable(db, hospitalId, "g_stock_check", "SP")
	if err != nil {
		return err
	}

	result, err := checkStmt.Exec(userId, hospitalId, code, p.Eventtime, p.Description)
	if err != nil {
		return err
	}
	checkId, err := result.LastInsertId()
	if err != nil {
		tx.Rollback()
		return err
	}

	for _, v := range p.StockCheckModels {

		res, err := checkDetailStmt.Exec(userId, hospitalId, checkId, v.ConCommodityID, v.ConCategoryID, v.ConCategoryName,
			v.TopCategoryID, v.CommodityCode, v.CommodityEncode, v.CommodityName, v.CommodityNamepy, v.CommodityBrand,
			v.CommodityIsuseAlias, v.CommodityAliasname, v.CommodityStandardname, v.CommodityStandardcode, v.CommodityVender,
			v.CommdityFormat, v.CommodityUnit, v.Constprice, v.Actualquantity, v.Firmquantity)
		if err != nil {
			tx.Rollback()
			return err
		}
		_, err = res.LastInsertId()
		if err != nil {
			tx.Rollback()
			return err
		}
		// 更改这个商品库存
		err = processStock(tx, v, userId, hospitalId)
		if err != nil {
			tx.Rollback()
			return err
		}
	}
	err = tx.Commit()
	if err != nil {
		tx.Rollback()
		return err
	}
	return nil
}

func processStock(tx *sql.Tx, checkModel *models.StockCheckModel, userId, hospitalId int64) error {
	currentTime := time.Now()
	currentYear := currentTime.Year()
	currentMonth := int(currentTime.Month())
	var stockId int64
	err := tx.QueryRow("select id from g_stock where sys_hospital_id=? and delflag=0 and con_commodity_id=?", hospitalId, checkModel.ConCommodityID).Scan(&stockId)

	if err != nil {
		if err != sql.ErrNoRows {
			return err
		} else { //未产生库存数据的处理
			_, err = tx.Exec("insert into g_stock(creater,sys_hospital_id,con_commodity_id,quantity) values(?,?,?,?)", userId, hospitalId, checkModel.ConCommodityID, 1000*checkModel.Firmquantity)
			if err != nil {
				return err
			}
			_, err = tx.Exec("insert into g_stock_costprice(creater,sys_hospital_id,_year,_month,con_commodity_id,quantity) values(?,?,?,?,?,?)", userId, hospitalId, currentYear, currentMonth, checkModel.ConCommodityID, 1000*checkModel.Firmquantity)
			if err != nil {
				return err
			}
			return nil
		}
	} else {
		updateStockSQL := "update g_stock set updater=?,quantity =?*1000 where sys_hospital_id = ? and con_commodity_id = ?"
		result, err := tx.Exec(updateStockSQL, userId, checkModel.Firmquantity, hospitalId, checkModel.ConCommodityID)
		if err != nil {
			return err
		}
		_, err = result.RowsAffected()
		if err != nil {
			return err
		}
		queryCostPriceIdSQL := `select id,_year,_month,costprice,quantity from g_stock_costprice where sys_hospital_id = ? and con_commodity_id = ? ORDER BY id desc limit 1`
		var (
			constPriceId        int64
			lastYear, lastMonth int
			costPrice, quantity float64
		)
		err = tx.QueryRow(queryCostPriceIdSQL, hospitalId, checkModel.ConCommodityID).Scan(&constPriceId, &lastYear, &lastMonth, &costPrice, &quantity)
		if err != nil {
			return err
		} else {
			if currentYear < lastYear {
				return errors.New("请确保服务器时间是正确时间")
			} else if currentYear == lastYear {
				if currentMonth < lastMonth {
					return errors.New("请确保服务时间是正确时间")
				} else if currentMonth == lastMonth {
					// 更新库存
					updateCostPriceSQL := `update g_stock_costprice set updater=?,quantity =? * 1000 where id = ?`
					result, err = tx.Exec(updateCostPriceSQL, userId, checkModel.Firmquantity, constPriceId)
					if err != nil {
						return err
					}
				} else {
					for i := lastMonth + 1; i < currentMonth; i++ {
						_, err = tx.Exec("insert into g_stock_costprice(creater,sys_hospital_id,_year,_month,con_commodity_id,costprice,quantity,lastcostprice,lastquantity) values(?,?,?,?,?,?,?,?,?)",
							userId, hospitalId, currentYear, i, checkModel.ConCommodityID, costPrice, quantity, costPrice, quantity)
						if err != nil {
							return err
						}
					}
					_, err = tx.Exec("insert into g_stock_costprice(creater,sys_hospital_id,_year,_month,con_commodity_id,costprice,quantity,lastcostprice,lastquantity) values(?,?,?,?,?,?,?,?,?)",
						userId, hospitalId, currentYear, currentMonth, checkModel.ConCommodityID, costPrice, checkModel.Firmquantity*1000, costPrice, quantity)
					if err != nil {
						return err
					}
				}
			} else {
				for i := lastYear; i < currentYear; i++ {
					if i == lastYear {
						for j := lastMonth + 1; j <= 12; j++ {
							_, err = tx.Exec("insert into g_stock_costprice(creater,sys_hospital_id,_year,_month,con_commodity_id,costprice,quantity,lastcostprice,lastquantity) values(?,?,?,?,?,?,?,?,?)",
								userId, hospitalId, i, j, checkModel.ConCommodityID, costPrice, quantity, costPrice, quantity)
							if err != nil {
								return err
							}
						}
					} else {
						for j := 1; j <= 12; j++ {
							_, err = tx.Exec("insert into g_stock_costprice(creater,sys_hospital_id,_year,_month,con_commodity_id,costprice,quantity,lastcostprice,lastquantity) values(?,?,?,?,?,?,?,?,?)",
								userId, hospitalId, i, j, checkModel.ConCommodityID, costPrice, quantity, costPrice, quantity)
							if err != nil {
								return err
							}
						}
					}
				}
				for i := 1; i < currentMonth; i++ {
					_, err = tx.Exec("insert into g_stock_costprice(creater,sys_hospital_id,_year,_month,con_commodity_id,costprice,quantity,lastcostprice,lastquantity) values(?,?,?,?,?,?,?,?,?)",
						userId, hospitalId, currentYear, i, checkModel.ConCommodityID, costPrice, quantity, costPrice, quantity)
					if err != nil {
						return err
					}
				}
				_, err = tx.Exec("insert into g_stock_costprice(creater,sys_hospital_id,_year,_month,con_commodity_id,costprice,quantity,lastcostprice,lastquantity) values(?,?,?,?,?,?,?,?,?)",
					userId, hospitalId, currentYear, currentMonth, checkModel.ConCommodityID, costPrice, checkModel.Firmquantity*1000, costPrice, quantity)
				if err != nil {
					return err
				}
			}
		}
		return nil
	}
}

func AddNewStockCheckStockList(db *sql.DB, p *models.StockChecks, userId int64) error {

	if len(p.StockCheckModels) <= 0 {
		return errors.New("盘库单不能为空")
	}

	hospitalLocalId, err := QueryHospitalLocalIDByEmployeeLocalID(db, int(userId))
	if err != nil {
		return err
	}

	hospitalId := int64(hospitalLocalId)

	tx, err := db.Begin()
	if err != nil {
		return err
	}

	checkSQL := `insert g_new_stock_check set creater = ?, sys_hospital_id = ?, _code = ?, eventtime = ?, description = ?`

	checkDetailSQL := `insert g_new_stockcheck_detail set creater = ?, sys_hospital_id = ?, new_stockcheck_id = ?, con_commodity_id = ?,
con_category_id = ?, con_category_name = ?, top_category_id = ?, commodity_code = ?, commodity_encode = ?,
commodity_name = ?, commodity_namepy = ?, commodity_brand = ?, commodity_isuse_alias = ?, commodity_aliasname = ?,
commodity_standardname = ?, commodity_standardcode = ?, commodity_vender = ?, commodity_format = ?, commodity_unit = ?,
constprice= ?*1000, actualquantity= ?*1000, firmquantity= ?*1000,commodity_batch = ?`

	checkStmt, err := tx.Prepare(checkSQL)
	if err != nil {
		return err
	}

	checkDetailStmt, err := tx.Prepare(checkDetailSQL)
	if err != nil {
		return err
	}

	//checkID := p.ID

	//获取code
	code, err := NewGetCodeByTable(db, hospitalId, "g_new_stock_check", "NSP")
	if err != nil {
		return err
	}

	result, err := checkStmt.Exec(userId, hospitalId, code, p.Eventtime, p.Description)
	if err != nil {
		return err
	}
	checkId, err := result.LastInsertId()
	if err != nil {
		tx.Rollback()
		return err
	}

	now_Year := time.Now().Year()
	now_Month := int(time.Now().Month())
	now_Day := int(time.Now().Day())

	getQuantity, err := GetQuantity(db, hospitalId)
	if err != nil {
		return err
	}

	for _, v := range p.StockCheckModels {
		if v.CommodityBatch == "无批次" {
			v.CommodityBatch = ""
		}

		// 首先盘到那个清除哪个的count

		var (
			isHaveStock bool
			count       int
		)

		// 操作库存表  确认此商品是否有库存
		if v.CommodityBatch == "" {
			err = tx.QueryRow(`select count(0) from g_new_stock where sys_hospital_id = ? and delflag=0 and con_commodity_id = ? and  commodity_batch = '' `, hospitalId, v.ConCommodityID).Scan(&count)
			if err != nil {
				tx.Rollback()
				return err
			}
			if count > 0 {
				isHaveStock = true
			}
		} else {
			isHaveStock, _, err = QueryGoodRecordIsHaveStockInTxByPriceAndCommodityBatch(tx, hospitalId, int64(v.ConCommodityID), v.CommodityBatch, v.BatchPrice)
			if err != nil {
				tx.Rollback()
				return err
			}
		}

		if isHaveStock {

			ex, err := checkDetailStmt.Exec(userId, hospitalId, checkId, v.ConCommodityID, v.ConCategoryID, v.ConCategoryName,
				v.TopCategoryID, v.CommodityCode, v.CommodityEncode, v.CommodityName, v.CommodityNamepy, v.CommodityBrand,
				v.CommodityIsuseAlias, v.CommodityAliasname, v.CommodityStandardname, v.CommodityStandardcode, v.CommodityVender,
				v.CommdityFormat, v.CommodityUnit, v.Constprice, v.Actualquantity, v.Firmquantity, v.CommodityBatch)
			if err != nil {
				tx.Rollback()
				return err
			}
			lastInsertId, _ := ex.LastInsertId()

			if v.Firmquantity == getQuantity[v.ConCommodityID][v.CommodityBatch][v.BatchPrice] {
			} else {
				if v.Firmquantity > getQuantity[v.ConCommodityID][v.CommodityBatch][v.BatchPrice] {
					v.Firmquantity = v.Firmquantity - getQuantity[v.ConCommodityID][v.CommodityBatch][v.BatchPrice]
					err := CheckToAdd(tx, hospitalId, *v, userId, lastInsertId, now_Year, now_Month, now_Day)
					if err != nil {
						tx.Rollback()
						return err
					}
				} else {
					v.Firmquantity = getQuantity[v.ConCommodityID][v.CommodityBatch][v.BatchPrice] - v.Firmquantity
					err := CheckToSub(tx, hospitalId, *v, userId, lastInsertId, now_Year, now_Month, now_Day)
					if err != nil {
						tx.Rollback()
						return err
					}
				}
			}
		} else {
			// 新增
			err = newProcessStock(tx, *v, userId, hospitalId, checkId)
			if err != nil {
				tx.Rollback()
				return err
			}
		}
	}

	err = tx.Commit()
	if err != nil {
		tx.Rollback()
		return err
	}

	return nil

}

func StockCheckDetail(db *sql.DB, detailId string, userId int64) (interface{}, error) {

	sc := make([]models.StockCheckModel, 0)

	checkDetailSQL := fmt.Sprintf(`select d.stockcheck_id,
		d.con_commodity_id,  d.con_category_id,  d.con_category_name,  d.top_category_id,  d.commodity_code,
		d.commodity_encode,  d.commodity_name,  d.commodity_namepy,  d.commodity_brand,  d.commodity_isuse_alias,
		d.commodity_aliasname,  d.commodity_standardname,  d.commodity_standardcode,  d.commodity_vender,
		d.commodity_format,  d.commodity_unit, 
		convert(ifnull(g.costmoney, 0) / 1000, decimal(11,3)) as constprice,
		convert(d.actualquantity / 1000, decimal(11,3)) as actualquantity,convert(d.firmquantity / 1000, decimal(11,3)) as firmquantity
		from g_stockcheck_detail d 
		left join g_stock g on g.con_commodity_id = d.con_commodity_id and g.delflag = 0
	   where stockcheck_id = ? order by d.id desc`)

	rowsDetail, err := db.Query(checkDetailSQL, detailId)
	if err != nil {
		return nil, err
	}
	for rowsDetail.Next() {
		var model models.StockCheckModel
		err = rowsDetail.Scan(&model.ID, &model.ConCommodityID, &model.ConCategoryID, &model.ConCategoryName,
			&model.TopCategoryID, &model.CommodityCode, &model.CommodityEncode,
			&model.CommodityName, &model.CommodityNamepy, &model.CommodityBrand,
			&model.CommodityIsuseAlias, &model.CommodityAliasname, &model.CommodityStandardname,
			&model.CommodityStandardcode, &model.CommodityVender, &model.CommdityFormat, &model.CommodityUnit,
			&model.Constprice, &model.Actualquantity, &model.Firmquantity)
		count, err_ := Last30DaySellCount(db, int(model.ConCommodityID))
		if err_ != nil {
			return nil, err_
		}
		model.Last30DayCount = count
		sc = append(sc, model)
	}

	return sc, nil

}

func NewStockCheckDetail(db *sql.DB, detailId string, userId int64) (interface{}, error) {

	hospitalLocalId, err := QueryHospitalLocalIDByEmployeeLocalID(db, int(userId))
	if err != nil {
		return nil, err
	}

	hospitalId := int64(hospitalLocalId)

	sc := make([]models.StockCheckModel, 0)

	checkDetailSQL := fmt.Sprintf(`select d.id, d.new_stockcheck_id,
		d.con_commodity_id,  d.con_category_id,  d.con_category_name,  d.top_category_id,  d.commodity_code,
		d.commodity_encode,  d.commodity_name,  d.commodity_namepy,  d.commodity_brand,  d.commodity_isuse_alias,
		d.commodity_aliasname,  d.commodity_standardname,  d.commodity_standardcode,  d.commodity_vender,
		d.commodity_format,  d.commodity_unit, 
		convert(ifnull(d.constprice, 0) / 1000, decimal(11,3)) as constprice,
		convert(d.actualquantity / 1000, decimal(11,3)) as actualquantity,convert(d.firmquantity / 1000, decimal(11,3)) as firmquantity,d.commodity_batch
		from g_new_stockcheck_detail d
	   where  d.delflag = 0 and   d.sys_hospital_id = ? and new_stockcheck_id  = ? order by d.id desc`)

	rowsDetail, err := db.Query(checkDetailSQL, hospitalId, detailId)
	if err != nil {
		return nil, err
	}

	for rowsDetail.Next() {
		var id int
		var model models.StockCheckModel
		err = rowsDetail.Scan(&model.ID, &id, &model.ConCommodityID, &model.ConCategoryID, &model.ConCategoryName,
			&model.TopCategoryID, &model.CommodityCode, &model.CommodityEncode,
			&model.CommodityName, &model.CommodityNamepy, &model.CommodityBrand,
			&model.CommodityIsuseAlias, &model.CommodityAliasname, &model.CommodityStandardname,
			&model.CommodityStandardcode, &model.CommodityVender, &model.CommdityFormat, &model.CommodityUnit,
			&model.Constprice, &model.Actualquantity, &model.Firmquantity, &model.CommodityBatch)
		if err != nil {
			return nil, err
		}
		count, err_ := NewStockLast30DaySellCount(db, int(model.ConCommodityID))
		if err_ != nil {
			return nil, err_
		}
		if model.CommodityBatch == "" {
			model.CommodityBatch = "无批次"
		}
		model.Last30DayCount = count
		sc = append(sc, model)
	}

	return sc, nil

}

// newProcessStock 盘库时候修改库存
func newProcessStock(tx *sql.Tx, v models.StockCheckModel, userId, hospitalId, checkID int64) error {

	now_Year := time.Now().Year()
	now_Month := int(time.Now().Month())
	now_Day := int(time.Now().Day())

	checkDetailSQL := `insert g_new_stockcheck_detail set creater = ?, sys_hospital_id = ?, new_stockcheck_id = ?, con_commodity_id = ?,
con_category_id = ?, con_category_name = ?, top_category_id = ?, commodity_code = ?, commodity_encode = ?,
commodity_name = ?, commodity_namepy = ?, commodity_brand = ?, commodity_isuse_alias = ?, commodity_aliasname = ?,
commodity_standardname = ?, commodity_standardcode = ?, commodity_vender = ?, commodity_format = ?, commodity_unit = ?,
constprice= ?*1000, actualquantity= ?*1000, firmquantity= ?*1000,commodity_batch = ?`

	checkDetailStmt, err := tx.Prepare(checkDetailSQL)
	if err != nil {
		return err
	}

	lotNumberStr, err := GetLotNumBerInTransaction(tx, hospitalId)
	if err != nil {
		tx.Rollback()
		return err
	}
	_, err = tx.Exec("insert into g_new_stock(creater,sys_hospital_id,con_commodity_id,quantity,lot_number) values(?,?,?,?,?)", userId, hospitalId, v.ConCommodityID, 1000*v.Firmquantity, lotNumberStr)
	if err != nil {
		return err
	}

	ex, err := checkDetailStmt.Exec(userId, hospitalId, checkID, v.ConCommodityID, v.ConCategoryID, v.ConCategoryName,
		v.TopCategoryID, v.CommodityCode, v.CommodityEncode, v.CommodityName, v.CommodityNamepy, v.CommodityBrand,
		v.CommodityIsuseAlias, v.CommodityAliasname, v.CommodityStandardname, v.CommodityStandardcode, v.CommodityVender,
		v.CommdityFormat, v.CommodityUnit, v.Constprice, v.Actualquantity, v.Firmquantity, v.CommodityBatch)
	if err != nil {
		tx.Rollback()
		return err
	}
	lastInsertId, _ := ex.LastInsertId()

	// 新增Count记录
	countID, err := InsertStockCountToAdd(tx, userId, hospitalId, v.ConCommodityID, now_Year, now_Month, now_Day, time.Now().Format("2006-01-02 15:04:05"), v.Firmquantity, v.Firmquantity, 0, lotNumberStr, v.CommodityBatch)
	if err != nil {
		return err
	}

	err = CheckAddLotNumberDetail(tx, userId, hospitalId, lastInsertId, v.ConCommodityID, v.ConCategoryID, v.ConCategoryName,
		v.TopCategoryID, v.CommodityCode, v.CommodityEncode, v.CommodityName, v.CommodityNamepy, v.CommodityBrand,
		v.CommodityIsuseAlias, v.CommodityAliasname, v.CommodityStandardname, v.CommodityStandardcode, v.CommodityVender,
		v.CommdityFormat, v.CommodityUnit, v.Firmquantity, v.BatchPrice, lotNumberStr, v.CommodityBatch, countID)
	if err != nil {
		return err
	}

	return nil
}

// 盘库编号(修改变少 退库存)
func CheckToSub(tx *sql.Tx, hospitalId int64, v models.StockCheckModel, userId int64, DetailID int64, now_Year int, now_Month int, now_Day int) error {

	var (
		err      error
		querySQL string
		rows     *sql.Rows
	)

	// 操作库存表  确认此商品是否有库存
	if v.CommodityBatch == "" {
		// 查询库存中 符合批次的这个商品的先进先出的批号的 id 和 数量
		querySQL = "select id, convert(quantity / 1000, decimal(11,3)) as quantity ,lot_number,con_commodity_id from g_new_stock where sys_hospital_id = ? and con_commodity_id = ?  AND  commodity_batch = '' order by id asc"

		rows, err = tx.Query(querySQL, hospitalId, v.ConCommodityID)
		if err != nil {
			return err
		}
	} else {
		// 查询库存中 符合批次的这个商品的先进先出的批号的 id 和 数量
		querySQL = "select id, convert(quantity / 1000, decimal(11,3)) as quantity ,lot_number,con_commodity_id from g_new_stock" +
			" where sys_hospital_id = ? and con_commodity_id = ?  and commodity_batch = ?  and convert(costmoney / 1000, decimal(11,3)) = ? order by id asc"

		rows, err = tx.Query(querySQL, hospitalId, v.ConCommodityID, v.CommodityBatch, v.BatchPrice)
		if err != nil {
			return err
		}
	}

	ls := make([]models.LotNumberListByCommodityBatch, 0)

	for rows.Next() {
		var ln models.LotNumberListByCommodityBatch
		err = rows.Scan(&ln.ID, &ln.Quantity, &ln.LotNumber, &ln.ConCommodityID)
		if err != nil {
			return err
		}
		ls = append(ls, ln)
	}

	// 按规则判断记录出库
	var tempQuantity float64

	for _, list := range ls {

		// 循环进行退货

		if list.Quantity <= 0 {
			continue
		}

		if v.Firmquantity > 0 {

			if v.Firmquantity > list.Quantity {

				tempQuantity = list.Quantity

				v.Firmquantity = v.Firmquantity - tempQuantity

			} else {

				tempQuantity = v.Firmquantity

				v.Firmquantity = v.Firmquantity - tempQuantity

			}

			// 更新库存数量
			err = UpdateCheckStockSubTo(tx, tempQuantity, hospitalId, int64(list.ConCommodityID), list.LotNumber)
			if err != nil {
				return err
			}

			// 新增Count记录
			lastID, err := InsertStockCountCheckSub(tx, userId, hospitalId, v.ConCommodityID, now_Year, now_Month, now_Day, time.Now().Format("2006-01-02 15:04:05"), tempQuantity, list.Quantity-tempQuantity, list.Quantity, list.LotNumber, v.CommodityBatch)
			if err != nil {
				return err
			}

			err = CheckAddLotNumberDetail(tx, userId, hospitalId, DetailID, v.ConCommodityID, v.ConCategoryID, v.ConCategoryName,
				v.TopCategoryID, v.CommodityCode, v.CommodityEncode, v.CommodityName, v.CommodityNamepy, v.CommodityBrand,
				v.CommodityIsuseAlias, v.CommodityAliasname, v.CommodityStandardname, v.CommodityStandardcode, v.CommodityVender,
				v.CommdityFormat, v.CommodityUnit, tempQuantity, v.BatchPrice, list.LotNumber, v.CommodityBatch, lastID)
			if err != nil {
				return err
			}
		}
	}
	return err
}

// 修改退货商品(修改变多 继续退货)
func CheckToAdd(tx *sql.Tx, hospitalId int64, v models.StockCheckModel, userId int64, detailID int64, now_Year int, now_Month int, now_Day int) error {

	var (
		err      error
		querySQL string
		rows     *sql.Rows
	)

	// 操作库存表  确认此商品是否有库存
	if v.CommodityBatch == "" {
		// 查询库存中 符合批次的这个商品的先进先出的批号的 id 和 数量
		querySQL = "select id, convert(quantity / 1000, decimal(11,3)) as quantity ,lot_number from g_new_stock where sys_hospital_id = ? and con_commodity_id = ? and commodity_batch = '' and convert(costmoney/1000,decimal(11,3))  = ? order by id desc limit 1 "

		rows, err = tx.Query(querySQL, hospitalId, v.ConCommodityID, v.BatchPrice)
		if err != nil {
			return err
		}
	} else {
		// 查询库存中 符合批次的这个商品的先进先出的批号的 id 和 数量
		querySQL = "select id, convert(quantity / 1000, decimal(11,3)) as quantity ,lot_number from g_new_stock where sys_hospital_id = ? and con_commodity_id = ? and  commodity_batch = ? and convert(costmoney/1000,decimal(11,3))  = ? order by id desc limit 1  "

		rows, err = tx.Query(querySQL, hospitalId, v.ConCommodityID, v.CommodityBatch, v.BatchPrice)
		if err != nil {
			return err
		}
	}

	ls := make([]models.LotNumberListByCommodityBatch, 0)

	for rows.Next() {
		var ln models.LotNumberListByCommodityBatch
		err = rows.Scan(&ln.ID, &ln.Quantity, &ln.LotNumber)
		if err != nil {
			return err
		}
		ls = append(ls, ln)
	}

	for _, list := range ls {

		err = UpdateStockAddToByID(tx, v.Firmquantity, list.ID)
		if err != nil {
			return err
		}

		// 新增Count记录
		countID, err := InsertStockCountToAdd(tx, userId, hospitalId, v.ConCommodityID, now_Year, now_Month, now_Day, time.Now().Format("2006-01-02 15:04:05"), v.Firmquantity, list.Quantity+v.Firmquantity, list.Quantity, list.LotNumber, v.CommodityBatch)
		if err != nil {
			return err
		}

		err = CheckAddLotNumberDetail(tx, userId, hospitalId, detailID, v.ConCommodityID, v.ConCategoryID, v.ConCategoryName,
			v.TopCategoryID, v.CommodityCode, v.CommodityEncode, v.CommodityName, v.CommodityNamepy, v.CommodityBrand,
			v.CommodityIsuseAlias, v.CommodityAliasname, v.CommodityStandardname, v.CommodityStandardcode, v.CommodityVender,
			v.CommdityFormat, v.CommodityUnit, v.Firmquantity, v.BatchPrice, list.LotNumber, v.CommodityBatch, countID)
		if err != nil {
			return err
		}

	}
	return err
}

func GetFormat(t string) (string, error) {

	if len(t) == 10 {
		return "%Y-%m-%d", nil
	} else if len(t) == 7 {
		return "%Y-%m", nil
	} else if len(t) == 4 {
		return "%Y", nil
	} else {
		return "", errors.New("time error")
	}

}

// GetLotNumBerInTransaction 事务安全的自动生成编号
func GetLotNumBerInTransaction(tx *sql.Tx, hospitalId int64) (string, error) {

	var code string

	querySQL := "select lot_number from " + "g_new_stock"

	//querySQL += fmt.Sprintf(" where sys_hospital_id in (0, %d)  ", hospitalId)

	querySQL += " order by id desc limit 1"

	err := tx.QueryRow(querySQL).Scan(&code)

	switch {
	case err == sql.ErrNoRows:
		return "ALN" + "000001", nil
	case err != nil:
		return "", err
	}
	codeLength := len(code)
	index := 0
	for i := codeLength; i > 0; i-- {
		_, err := strconv.Atoi(code[i-1 : i])
		if err != nil {
			index = i
			break
		}
	}
	pre := code[:index]
	numstr := code[index:]
	num, err := strconv.ParseInt(numstr, 10, 0)
	if err != nil {
		return "ALN" + "000001", nil
	}
	code = fmt.Sprintf("%s%0"+fmt.Sprintf("%d", len(numstr))+"d", pre, num+1)

	return code, nil
}

func GetQuantity(db *sql.DB, hospitalId int64) (map[int64]map[string]map[float64]float64, error) {

	queryQuantityStr := `select 
stock.con_commodity_id , convert(SUM(stock.quantity ) / 1000, decimal(11,3)) as 'quantity',stock.commodity_batch, convert(stock.costmoney  / 1000, decimal(11,3)) as 'costmoney'
from g_new_stock as stock
left join con_commodity as commodity on stock.con_commodity_id = commodity.id
where commodity.delflag = 0 and commodity.sys_hospital_id in (0,?)   group by  stock.con_commodity_id,stock.commodity_batch,stock.costmoney`

	quantities := make([]*commodityQuantity, 0)

	row, err := db.Query(queryQuantityStr, hospitalId)
	if err != nil {
		return nil, err
	}

	for row.Next() {
		var c commodityQuantity
		err = row.Scan(&c.ConCommodityId, &c.Quantity, &c.CommodityBatch, &c.CostMoney)
		if err != nil {
			return nil, err
		}
		quantities = append(quantities, &c)
	}

	c := make(map[int64]map[string]map[float64]float64)

	for _, v := range quantities {
		if c[v.ConCommodityId] == nil {
			c[v.ConCommodityId] = make(map[string]map[float64]float64)
		}
		if c[v.ConCommodityId][v.CommodityBatch] == nil {
			c[v.ConCommodityId][v.CommodityBatch] = make(map[float64]float64)
		}
		c[v.ConCommodityId][v.CommodityBatch][v.CostMoney] = v.Quantity
	}

	return c, err

}

type commodityQuantity struct {
	ConCommodityId int64
	CommodityBatch string
	Quantity       float64
	CostMoney      float64
}

func QueryGoodRecordIsHaveStockInTxByPriceAndCommodityBatch(tx *sql.Tx, hospitalId, goodId int64, commodityBatch string, batchPrice float64) (bool, float64, error) {

	/*
		1.首先找到这个批次的这个价格的商品有无
		2.有之后判断数量
		3.进行出库扣减,记录
	*/

	//tx.QueryRow(`select  convert(buyprice / 1000, decimal(11,3)) as buyprice where  lot_number = ? `,l)
	// 进行 1 判断此批次,此价格的商品有无
	sql := "select count(0) from g_new_stock where sys_hospital_id = ? and delflag=0 and con_commodity_id = ? and commodity_batch = ? and convert(costmoney/1000,decimal(11,3)) = ?"
	sqlCount := "select ifnull(sum(convert(quantity / 1000, decimal(11,3)) ),0) from g_new_stock where sys_hospital_id = ? and delflag=0 and commodity_batch = ?  and con_commodity_id = ? and convert(costmoney/1000,decimal(11,3)) = ?  "

	var (
		isHave float64
		counts float64
	)
	err := tx.QueryRow(sql, hospitalId, goodId, commodityBatch, batchPrice).Scan(&isHave)
	if err != nil {
		return false, 0, err
	}
	err = tx.QueryRow(sqlCount, hospitalId, commodityBatch, goodId, batchPrice).Scan(&counts)
	if err != nil {
		return false, 0, err
	}

	if isHave <= 0 {
		return false, 0, nil
	}

	return true, counts, nil
}

func NewGetCodeByTable(db *sql.DB, hospitalId int64, tableName, tag string) (string, error) {

	var code string

	querySQL := "select _code from " + tableName + " where delflag = 0 and sys_hospital_id in (0, %d) order by id desc limit 1"

	err := db.QueryRow(fmt.Sprintf(querySQL, hospitalId)).Scan(&code)

	preCode := tag + time.Now().Format("20060102")
	switch {
	case err == sql.ErrNoRows:
		return preCode + "0001", nil
	case err != nil:
		return "", err
	}

	if len(code) < 9 {
		return preCode + "0001", nil
	}

	if code[3:11] == preCode[3:] {
		codeIndex, err := strconv.ParseInt(code[11:], 10, 0)
		if err != nil {
			return "", err
		}
		codeIndex++
		code = fmt.Sprintf("%s%04d", preCode, codeIndex)
	} else {
		code = preCode + "0001"
	}

	return code, nil
}

// 最近30天商品销售数量
func NewStockLast30DaySellCount(db *sql.DB, commodityID int) (float64, error) {

	var (
		last30SellCount float64
		numberOfRefunds float64
	)

	now := time.Now()

	endTime := now.Format("2006-01-02")

	startTime := now.AddDate(0, -1, 0).Format("2006-01-02")

	last30DaySellCountSql := `SELECT
		ifnull(convert(sum(c.quantity) / 1000,decimal(11,3)),0) as quantity
	FROM
    his_consumption AS c
        LEFT JOIN his_bill AS b ON b.id = c.his_bill_id
	WHERE
        c.delflag = 0
  AND c.sys_hospital_id =  1
  AND c.his_retreatbill_id = 0
  AND c.commodity_ismeal = 0
  AND c.meter_num = 0
  AND b.billtype IN ( 1,4,6,7,2 )
	and DATE_FORMAT(b.paytime,  '%Y-%m-%d' ) BETWEEN ? AND ?
 AND c.con_commodity_id = ?`

	// 处理退款问题
	refundProcessing := ` 
 SELECT
		ifnull(convert(sum(c.quantity) / 1000,decimal(11,3)),0) as quantity
FROM
   his_consumption AS c
        LEFT JOIN his_bill AS b ON b.id = c.his_bill_id
WHERE
	c.delflag = 0
	AND c.sys_hospital_id = 1
	AND c.his_retreatbill_id != 0
	AND c.commodity_ismeal = 0
	AND c.meter_num = 0
	AND b.billtype  = 5 
	AND 	DATE_FORMAT(b.paytime,  '%Y-%m-%d' ) BETWEEN  ? AND ?
	 AND c.con_commodity_id = ?`

	err := db.QueryRow(last30DaySellCountSql, startTime, endTime, commodityID).Scan(&last30SellCount)
	if err != nil {
		return 0, err
	}

	err = db.QueryRow(refundProcessing, startTime, endTime, commodityID).Scan(&numberOfRefunds)
	if err != nil {
		return 0, err
	}

	return last30SellCount - numberOfRefunds, nil

}

// 最近30天商品销售数量
func Last30DaySellCount(db *sql.DB, commodityID int) (float64, error) {

	var (
		last30SellCount float64
		numberOfRefunds float64
	)

	now := time.Now()

	endTime := now.Format("2006-01-02")

	startTime := now.AddDate(0, -1, 0).Format("2006-01-02")

	last30DaySellCountSql := `SELECT
		ifnull(convert(sum(c.quantity) / 1000,decimal(11,3)),0) as quantity
	FROM
    his_consumption AS c
        LEFT JOIN his_bill AS b ON b.id = c.his_bill_id
	WHERE
        c.delflag = 0
  AND c.sys_hospital_id =  1
  AND c.his_retreatbill_id = 0
  AND c.commodity_ismeal = 0
  AND c.meter_num = 0
  AND b.billtype IN ( 1,4,6,7,2 )
	and DATE_FORMAT(b.paytime,  '%Y-%m-%d' ) BETWEEN ? AND ?
 AND c.con_commodity_id = ?`

	// 处理退款问题
	refundProcessing := ` 
 SELECT
		ifnull(convert(sum(c.quantity) / 1000,decimal(11,3)),0) as quantity
FROM
   his_consumption AS c
        LEFT JOIN his_bill AS b ON b.id = c.his_bill_id
WHERE
	c.delflag = 0
	AND c.sys_hospital_id = 1
	AND c.his_retreatbill_id != 0
	AND c.commodity_ismeal = 0
	AND c.meter_num = 0
	AND b.billtype  = 5 
	AND 	DATE_FORMAT(b.paytime,  '%Y-%m-%d' ) BETWEEN  ? AND ?
	 AND c.con_commodity_id = ?`

	err := db.QueryRow(last30DaySellCountSql, startTime, endTime, commodityID).Scan(&last30SellCount)
	if err != nil {
		return 0, err
	}

	err = db.QueryRow(refundProcessing, startTime, endTime, commodityID).Scan(&numberOfRefunds)
	if err != nil {
		return 0, err
	}

	return last30SellCount - numberOfRefunds, nil

}

func GetNewStockByStatisticsListDao(db *sql.DB, categoryIds, keyword string, hospitalID int, chainCode string) (interface{}, error) {

	querySql := `select 
stock.id as id, stock.con_commodity_id as 'con_commodity_id', convert(SUM(stock.quantity ) / 1000, decimal(11,3)) as 'quantity', convert(commodity.saleprice / 1000, decimal(11,3)) as 'saleprice',
commodity._code as 'code', commodity._name as 'name', commodity.unit as 'unit', commodity.vender as 'vender', commodity.format as 'format',
commodity.con_category_id as 'con_category_id',ifnull(commodity.aliasname,""),ifnull(commodity.isuse_alias,0)
from g_new_stock as stock
left join con_commodity as commodity on stock.con_commodity_id = commodity.id
where stock.sys_hospital_id = %v  and commodity.delflag = 0 and commodity.sys_hospital_id in (0,%v) and commodity.isuse_stock = 1 `

	check, err := utils.CheckColumn(db, "con_commodity", "on_and_off_shelves", chainCode)
	if err != nil {
		return nil, err
	}

	if check > 0 {
		querySql += `  and  commodity.on_and_off_shelves = 0   `
	}

	querySql = fmt.Sprintf(querySql, hospitalID, hospitalID)
	if keyword != "" {
		keyword = "%" + keyword + "%"
		querySql = querySql + fmt.Sprintf(` and (commodity._name like '%s' or commodity._code like '%s' )    `, keyword, keyword)
	}

	if categoryIds == "" {
		return nil, errors.New("参数不能为空")
	}
	querySql += fmt.Sprintf(`  and commodity.con_category_id in (%v)    `, categoryIds)

	querySql += ` group by  commodity.id `

	rows, err := db.Query(querySql)
	if err != nil {
		return nil, err
	}

	var ReturnDatas []models.NewByStatistics

	for rows.Next() {
		var byStatistics models.NewByStatistics
		err = rows.Scan(&byStatistics.ID, &byStatistics.ConCommodityID,
			&byStatistics.Quantity, &byStatistics.Saleprice, &byStatistics.Code, &byStatistics.Name,
			&byStatistics.Unit, &byStatistics.Vender, &byStatistics.Format, &byStatistics.ConCategoryId, &byStatistics.AliasName, &byStatistics.IsUseAlias)
		if err != nil {
			return nil, err
		}
		if byStatistics.Quantity == 0 {
			byStatistics.Costmoney = 0
		} else {
			byStatistics.Costmoney, err = QueryCostMoney(db, byStatistics.ConCommodityID, hospitalID)
			if err != nil {
				return nil, err
			}
		}
		ReturnDatas = append(ReturnDatas, byStatistics)
	}

	return ReturnDatas, nil

}

func QueryCostMoney(db *sql.DB, commodityID int, hospitalID int) (float64, error) {

	var (
		price float64
	)

	// 取六位小数
	err := db.QueryRow(`SELECT  ifnull(CONVERT

	(
		SUM(
			CONVERT (
				costmoney / 1000,
				DECIMAL ( 11, 6 )) * CONVERT (
				quantity / 1000,
			DECIMAL ( 11, 3 )))/ SUM(
			CONVERT (
				quantity / 1000,
			DECIMAL ( 11, 6 ))),
	DECIMAL ( 11, 6 )) ,0)
FROM
	g_new_stock 
WHERE
	con_commodity_id = ? and sys_hospital_id = ?`, commodityID, hospitalID).Scan(&price)

	if err != nil {
		return 0, err
	}

	return price, err
}

func GetNewStockByStatisticsListDetailDao(db *sql.DB, commodityID, hospitalID int) (interface{}, error) {

	validityStr := "'%Y-%m-%d'"

	sql := `select 
stock.id as id, stock.con_commodity_id as 'con_commodity_id', convert(stock.costmoney / 1000, decimal(11,3)) as 'costmoney', convert(SUM(stock.quantity ) / 1000, decimal(11,3)) as 'quantity', convert(commodity.saleprice / 1000, decimal(11,3)) as 'saleprice',
commodity._code as 'code', commodity._name as 'name', commodity.unit as 'unit', commodity.vender as 'vender', commodity.format as 'format',
 commodity.con_category_id as 'con_category_id',stock.lot_number,ifnull(stock.commodity_batch,''), ifnull(DATE_FORMAT(validity.validity,  %s ),'')  AS validity,ifnull(commodity.aliasname,""),ifnull(commodity.isuse_alias,0)
from g_new_stock as stock
left join con_commodity as commodity on stock.con_commodity_id = commodity.id
left join con_new_commodity_validity as validity on validity.lot_number = stock.lot_number and validity.con_commodity_id = stock.con_commodity_id
where commodity.delflag = 0 and commodity.sys_hospital_id in (0,%v) and commodity.isuse_stock = 1  AND commodity.id  = %v and stock.sys_hospital_id = %v  group by  stock.lot_number  having  SUM(stock.quantity )  != 0 `

	rows, err := db.Query(fmt.Sprintf(sql, validityStr, hospitalID, commodityID, hospitalID))
	if err != nil {
		return nil, err
	}

	var ReturnDatas []models.NewByStatisticsByID

	for rows.Next() {
		var byStatistics models.NewByStatisticsByID
		err = rows.Scan(&byStatistics.ID, &byStatistics.ConCommodityID, &byStatistics.Costmoney,
			&byStatistics.Quantity, &byStatistics.Saleprice, &byStatistics.Code, &byStatistics.Name,
			&byStatistics.Unit, &byStatistics.Vender, &byStatistics.Format, &byStatistics.ConCategoryId, &byStatistics.LotNumber, &byStatistics.CommodityBatch, &byStatistics.Validity, &byStatistics.AliasName, &byStatistics.IsUseAlias)
		if err != nil {
			return nil, err
		}
		//if byStatistics.Quantity == 0 {
		//	continue
		//}
		ReturnDatas = append(ReturnDatas, byStatistics)
	}

	return ReturnDatas, nil

}

func GetCategoryDao(db *sql.DB, hospitalID int) (interface{}, error) {

	querySql := `select id, _name as 'name',  con_category_id, ordervalue  from con_category where delflag = 0 and sys_hospital_id in (0,%d)`

	rows, err := db.Query(fmt.Sprintf(querySql, hospitalID))
	if err != nil {
		return nil, err
	}

	var datas []models.CategoryList
	var showDataS []models.CategoryList
	for rows.Next() {
		var data models.CategoryList
		err = rows.Scan(&data.ID, &data.Name, &data.CategoryID, &data.OrderValue)
		if err != nil {
			return nil, err
		}
		datas = append(datas, data)

		if data.CategoryID == 0 {
			showDataS = append(showDataS, data)
		}
	}

	for _, data := range datas {
		for i := range showDataS {
			if showDataS[i].ID == data.CategoryID {
				showDataS[i].CategoryList = append(showDataS[i].CategoryList, data)
			}
		}
	}

	return showDataS, nil

}

func NewQueryStockOutIoExWarehousing(db *sql.DB, hospitalId int, stockSQL, stockDetailSQL, Judging string) ([]models.NewWarehousingParamModel, error) {

	rows, err := db.Query(stockSQL, hospitalId)
	if err != nil {
		return nil, err
	}

	stockModels := make([]models.NewWarehousingParamModel, 0)
	now := time.Now().Format("2006-01")
	for rows.Next() {
		var tempStockModel models.NewWarehousingParamModel
		err = rows.Scan(&tempStockModel.ID, &tempStockModel.ConCompanyID, &tempStockModel.ConCompanyName,
			&tempStockModel.ConEmployeeID, &tempStockModel.ConEmployeeName, &tempStockModel.Code, &tempStockModel.Type, &tempStockModel.IsPayover,
			&tempStockModel.Eventtime, &tempStockModel.AddNote)
		if err != nil {
			return nil, err
		}
		if tempStockModel.Eventtime[:7] == now {
			tempStockModel.IsCanDelete = true
		}
		stockModels = append(stockModels, tempStockModel)
	}

	cl, err := GetLotNumberList_(db, hospitalId)
	if err != nil {
		return nil, err
	}

	for i, v := range stockModels {

		rowDetail, err := db.Query(stockDetailSQL, hospitalId, v.ID)
		if err != nil {
			return nil, err
		}

		detailDatas := make([]models.NewDetailData, 0)
		for rowDetail.Next() {
			var detailData models.NewDetailData

			// Judging whether to go out or to go in
			if Judging == "1" {
				err = rowDetail.Scan(&detailData.ID, &detailData.StockinIoID, &detailData.ConCommodityID, &detailData.ConCategoryID, &detailData.ConCategoryName,
					&detailData.TopCategoryID, &detailData.CommodityCode, &detailData.CommodityEncode, &detailData.CommodityName, &detailData.CommodityNamepy,
					&detailData.CommodityBrand, &detailData.CommodityIsuseAlias, &detailData.CommodityAliasname, &detailData.CommodityStandardname, &detailData.CommodityStandardcode,
					&detailData.CommodityVender, &detailData.CommdityFormat, &detailData.CommodityUnit, &detailData.Quantity, &detailData.Buyprice, &detailData.Payprice, &detailData.CurrentQuantity, &detailData.Costprice, &detailData.Description)
			} else if Judging == "2" {
				err = rowDetail.Scan(&detailData.ID, &detailData.StockinIoID, &detailData.ConCommodityID, &detailData.ConCategoryID, &detailData.ConCategoryName,
					&detailData.TopCategoryID, &detailData.CommodityCode, &detailData.CommodityEncode, &detailData.CommodityName, &detailData.CommodityNamepy,
					&detailData.CommodityBrand, &detailData.CommodityIsuseAlias, &detailData.CommodityAliasname, &detailData.CommodityStandardname, &detailData.CommodityStandardcode,
					&detailData.CommodityVender, &detailData.CommdityFormat, &detailData.CommodityUnit, &detailData.Quantity, &detailData.Sellprice, &detailData.Payprice, &detailData.Description, &detailData.CommodityBatch, &detailData.BatchPrice, &detailData.WholesalePrice)
			} else if Judging == "3" {
				err = rowDetail.Scan(&detailData.ID, &detailData.StockinIoID, &detailData.ConCommodityID, &detailData.ConCategoryID, &detailData.ConCategoryName,
					&detailData.TopCategoryID, &detailData.CommodityCode, &detailData.CommodityEncode, &detailData.CommodityName, &detailData.CommodityNamepy,
					&detailData.CommodityBrand, &detailData.CommodityIsuseAlias, &detailData.CommodityAliasname, &detailData.CommodityStandardname, &detailData.CommodityStandardcode,
					&detailData.CommodityVender, &detailData.CommdityFormat, &detailData.CommodityUnit, &detailData.Quantity, &detailData.Retreatprice, &detailData.Payprice)
			}

			if err != nil {
				return nil, err
			}
			detailData.StockType = 1
			//list, err := new_stock_commodity.GetLotNumberList(db, detailData.ConCommodityID, hospitalId)
			//if err != nil {
			//	utils.DBCNormalLogger.Error(err.Error())
			//	return nil, err
			//}
			//detailData.CommodityBatchList = list
			//if list == nil {
			//	detailData.CommodityBatchList = make([]new_stock_commodity.CommodityBatchList, 0)
			//}
			batchLists := cl[int(detailData.ConCommodityID)]
			//fmt.Println(batchLists)
			detailData.CommodityBatchList = batchLists
			if batchLists == nil {
				detailData.CommodityBatchList = make([]models.NewCommodityBatchList, 0)
			}
			stockModels[i].Total += (detailData.WholesalePrice * detailData.Quantity)
			stockModels[i].OriginalTotal += (detailData.WholesalePrice * detailData.Quantity)
			if detailData.CommodityBatch == "" {
				detailData.CommodityBatch = "无批次"
			}
			detailData.Tag = 2
			detailDatas = append(detailDatas, detailData)
		}

		stockModels[i].DetailData = detailDatas
	}

	return stockModels, nil
}

func GetLotNumberList_(db *sql.DB, hospitalId int) (map[int][]models.NewCommodityBatchList, error) {

	querySql := `SELECT
       stock.con_commodity_id ,
       stock.commodity_batch,
	CONVERT (
		stock.costmoney / 1000,
		DECIMAL ( 11, 3 )) as  costmoney ,
		CONVERT (
			sum(stock.quantity / 1000),
			DECIMAL ( 11, 3 )) as sumquantity,
			ifnull(DATE_FORMAT(validity.validity,  '%Y-%m-%d' ),'')  as validity,
	ifnull((select TIMESTAMPDIFF(DAY,DATE_FORMAT(NOW(), '%Y-%m-%d' ),DATE_FORMAT(validity.validity, '%Y-%m-%d' ))),10000) as _day
FROM
	g_new_stock stock
	LEFT JOIN con_new_commodity_validity validity ON validity.lot_number = stock.lot_number 
	WHERE stock.sys_hospital_id IN (0,?) 
	group by  stock.con_commodity_id ,stock.commodity_batch, stock.costmoney`

	lists := make([]models.NewCommodityBatchListAll, 0)
	cl := make(map[int][]models.NewCommodityBatchList, 0)

	row, err := db.Query(querySql, hospitalId)
	if err != nil {
		return nil, err
	}

	for row.Next() {
		var l models.NewCommodityBatchListAll
		err = row.Scan(&l.ConCommodityID, &l.CommodityBatch, &l.Price, &l.Quantity, &l.Validity, &l.BatchMaturityDays)
		if err != nil {
			return nil, err
		}
		if l.CommodityBatch == "" {
			l.CommodityBatch = "无批次"
		}
		lists = append(lists, l)
	}

	m := make(map[int]map[string][]models.NewBatchList)
	c := make(map[int]map[string]string)

	for _, list := range lists {
		if c[list.ConCommodityID] == nil {
			c[list.ConCommodityID] = make(map[string]string)
		}
		c[list.ConCommodityID][list.CommodityBatch] = list.Validity

		if m[list.ConCommodityID][list.CommodityBatch] == nil {
			if m[list.ConCommodityID] == nil {
				m[list.ConCommodityID] = make(map[string][]models.NewBatchList)
			}
			lss := make([]models.NewBatchList, 0)
			lss = append(lss, models.NewBatchList{Price: list.Price,
				Quantity:       list.Quantity,
				MaturityDays:   list.BatchMaturityDays,
				Validity:       list.Validity,
				CommodityBatch: list.CommodityBatch,
				ConCommodityID: list.ConCommodityID})
			m[list.ConCommodityID][list.CommodityBatch] = lss
		} else {
			m[list.ConCommodityID][list.CommodityBatch] = append(m[list.ConCommodityID][list.CommodityBatch], models.NewBatchList{Price: list.Price,
				Quantity:       list.Quantity,
				MaturityDays:   list.BatchMaturityDays,
				Validity:       list.Validity,
				CommodityBatch: list.CommodityBatch,
				ConCommodityID: list.ConCommodityID})
		}
	}

	for commodityID, v := range c {
		for s, va := range v {
			var cb models.NewCommodityBatchList
			cb.ConCommodityID = commodityID
			cb.CommodityBatch = s
			cb.Validity = va
			cl[cb.ConCommodityID] = append(cl[cb.ConCommodityID], cb)
		}
	}

	for _, list := range cl {
		for k2, batchList := range list {
			list[k2].BatchList = m[batchList.ConCommodityID][batchList.CommodityBatch]
		}
	}

	return cl, err
}

func GetCompanyListDao(hospitalId int, db *sql.DB) (interface{}, error) {
	querySql := fmt.Sprintf("select id, _name as 'name', address, type, personname, mobile, phone, bank, bankaccount,convert(arrears / 1000, decimal(11,3)) arrears, maxarrears, convert(previous_receivebalance / 1000, decimal(11,3)) as previous_receivebalance, convert(previous_paybalance / 1000, decimal(11,3)) as previous_paybalance from con_company where delflag = 0 and sys_hospital_id in (0,%v)  and type in (1,2)", hospitalId)

	d, err := QueryArrays(db, querySql)
	if err != nil {
		return nil, err
	}

	return d, nil
}

func GetEmployeeList(hospitalId int, db *sql.DB) (interface{}, error) {

	querySql := `select employee.id as 'id', ifnull(role._name, '') as 'role_name', ifnull(role.id, 0) as 'con_role_id', employee._code as 'code', employee._name as 'name', 
employee.image_uri as 'image_uri', employee.phone as 'phone', employee.sex as 'sex', employee.birthday as 'birthday'
from con_employee as employee
join con_role as role on employee.con_role_id = role.id
where employee.delflag = 0 and employee.sys_hospital_id = ?`

	//and employee.job_state = 0

	d, err := QueryArrays(db, querySql, hospitalId)
	if err != nil {
		return nil, err
	}

	//fmt.Printf("用户信息: %v \n \n \n", d)
	return d, nil
}

func QueryArrays(db *sql.DB, q string, args ...interface{}) ([]map[string]interface{}, error) {

	rows, err := db.Query(q, args...)
	if err != nil {
		return nil, err
	}

	// Get column names
	columns, err := rows.Columns()
	if err != nil {
		return nil, err
	}

	values := make([]sql.RawBytes, len(columns))

	scanArgs := make([]interface{}, len(values))
	for i := range values {
		scanArgs[i] = &values[i]
	}

	// 最终要返回的data
	data := make([]map[string]interface{}, 0)
	// Fetch rows
	for rows.Next() {
		err = rows.Scan(scanArgs...)
		if err != nil {
			panic(err.Error()) // proper error handling instead of panic in your app
		}

		//每行数据
		row := make(map[string]interface{})
		for k, v := range values {
			key := columns[k]
			row[key] = string(v)
		}
		//放入结果集
		data = append(data, row)
	}

	return data, nil
}

func GetNumber(hospitalId int, numberType string, db *sql.DB) (interface{}, error) {

	var (
		orderValue string
		err        error
	)

	switch numberType {
	case "4":
		orderValue, err = NewGetCodeByStockTable(db, hospitalId, NewStockRetreatBillTable, NewStockRetreatBillTag)
	case "5":
		orderValue, err = NewGetCodeByStockTable(db, hospitalId, NewStockRetreatIoTable, NewStockRetreatIoTag)
	case "6":
		orderValue, err = NewGetCodeByStockTable(db, hospitalId, NewStockOutBillTable, NewStockOutBillTag)
	case "7":
		orderValue, err = NewGetCodeByStockTable(db, hospitalId, NewStockOutIoTable, NewStockOutIoTag)
	case "8":
		orderValue, err = NewGetCodeByStockTable(db, hospitalId, NewStockInBillTable, NewStockInBillTag)
	case "9":
		orderValue, err = NewGetCodeByStockTable(db, hospitalId, NewStockInIoTable, NewStockInIoTag)
	case "13":
		orderValue, err = NewGetCodeByStockTable(db, hospitalId, NewStockCheckTable, NewStockCheckTag)
	default:
		return nil, errors.New("类型错误")
	}

	if err != nil {
		return nil, err
	}

	return orderValue, err

}

const (

	//  4 采购退货
	NewStockRetreatBillTable string = "g_new_stockretreat_bill"
	NewStockRetreatBillTag   string = "NST"

	// 采购退货账单
	NewStockRetreatIoTable string = "g_new_stockretreat_io"
	NewStockRetreatIoTag   string = "NBT"

	// 销售出库账单
	NewStockOutBillTable string = "g_new_stockout_bill"
	NewStockOutBillTag   string = "NBC"

	// 销售出库
	NewStockOutIoTable string = "g_new_stockout_io"
	NewStockOutIoTag   string = "NSC"

	NewStockInIoTable string = "g_new_stockin_io"
	NewStockInIoTag   string = "NSR"

	// 入库账单
	NewStockInBillTable string = "g_new_stockin_bill"
	NewStockInBillTag   string = "NBR"

	// 库存盘点
	NewStockCheckTable string = "g_new_stock_check"
	NewStockCheckTag   string = "NSP"
)

func NewGetCodeByStockTable(db *sql.DB, hospitalId int, tableName, tag string) (string, error) {

	var code string

	querySQL := "select _code from " + tableName + " where delflag = 0 and sys_hospital_id in (0, %d) order by id desc limit 1"

	err := db.QueryRow(fmt.Sprintf(querySQL, hospitalId)).Scan(&code)

	preCode := tag + time.Now().Format("20060102")
	switch {
	case err == sql.ErrNoRows:
		return preCode + "0001", nil
	case err != nil:
		return "", err
	}

	if len(code) < 9 {
		return preCode + "0001", nil
	}

	if code[3:11] == preCode[3:] {
		codeIndex, err := strconv.ParseInt(code[11:], 10, 0)
		if err != nil {
			return "", err
		}
		codeIndex++
		code = fmt.Sprintf("%s%04d", preCode, codeIndex)
	} else {
		code = preCode + "0001"
	}

	return code, nil
}