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 }