💻博主现有专栏:
C51单片机(STC89C516),c语言,c++,离散数学,算法设计与分析,数据结构,Python,Java基础,MySQL,linux,基于HTML5的网页设计及应用,Rust(官方文档重点总结),jQuery,前端vue.js,Javaweb开发,设计模式、Python机器学习等
🥏主页链接:
🎯销售记录、销售明细查询的存储过程
🎃建立`v_xsjlmcb`视图
DROP VIEW IF EXISTS `v_xsjlmcb`; CREATE VIEW `v_xsjlmcb` AS (SELECT `cashaccountdetailid`,`gname`,`unit` ,`salesquantity`,`cashaccount_detail`.`retailprice`,`cashaccount_detail`.`promotionalprice` FROM `cashaccount_detail`,`goods` WHERE `cashaccount_detail`.`gid`=`goods`.`gid` );
这是一个MySQL视图的创建语句。它首先删除名为`v_xsjlmcb`的视图(如果存在),然后创建一个名为`v_xsjlmcb`的视图。这个视图从`cashaccount_detail`表和`goods`表中选择数据,其中`cashaccount_detail`表的`gid`字段与`goods`表的`gid`字段相等。选择的字段包括`cashaccountdetailid`、`gname`、`unit`、`salesquantity`、`retailprice`和`promotionalprice`。
🎃创建存储
DELIMITER $$ CREATE PROCEDURE `SyjlCx`(cxlb INT,cxcs INT) BEGIN IF cxlb=0 THEN SELECT `cashaccountid`,`cashierid`,`cashier_time`,`uid`,`amount_money`,`actual_money`, CASE `payment` WHEN 0 THEN '现金' WHEN 1 THEN '储值卡' WHEN 2 THEN '支付宝' WHEN 3 THEN '微信支付' END AS 支付方式 FROM `cashaccount` WHERE `cashierid`=cxcs AND `cashier_time`>=CURDATE() ORDER BY `cashaccountid` DESC; ELSE SELECT * FROM v_xsjlmcb WHERE v_xsjlmcb.`cashaccountdetailid`=cxcs; END IF; END$$ DELIMITER ;
🎯当天退货处理
DELIMITER $$ CREATE PROCEDURE `xiaoshou`.`ThjlCp`(cashaccountid INT) BEGIN SELECT `cashaccount`.`uid` INTO @uid FROM `cashaccount` WHERE `cashaccount`.`cashaccountid`=cashaccountid; SELECT `cashaccount`.`actual_money` INTO @ssje FROM `cashaccount` WHERE `cashaccount`.`cashaccountid`=cashaccountid; /*变更 余额与积分*/ IF @uid>=10000 THEN UPDATE `user` SET `card_balance`=`card_balance`+@ssje,`points`=`points`-FLOOR(@ssje) WHERE `user`.`uid`=@uid; END IF; -- 还原库存(update `stock`) DROP TABLE IF EXISTS lsb; CREATE TEMPORARY TABLE lsb (gid INT,xssl NUMERIC(18,3)); INSERT INTO lsb (gid,xssl) SELECT gid,SUM(`salesquantity`) FROM `cashaccount_detail` WHERE `cashaccount_detail`.`cashaccountid`=cashaccountid GROUP BY gid; UPDATE stock b1,lsb b2 SET b1.`quantity`=b1.quantity+b2.xssl WHERE b1.gid=b2.gid; -- 删除销售明细 (delete from `cashaccount_detail`) DELETE FROM `cashaccount_detail` WHERE `cashaccount_detail`.`cashaccountid`=cashaccountid; -- 删除收银记录 (delete from `cashaccount`) DELETE FROM `cashaccount` WHERE `cashaccount`.`cashaccountid`=cashaccountid; END$$ DELIMITER ;
这是一个MySQL存储过程,名为`ThjlCp`,接受一个参数:`cashaccountid`。该存储过程的功能如下:
1. 从`cashaccount`表中查询出`uid`和`actual_money`字段的值,分别赋值给变量`@uid`和`@ssje`。
2. 如果`@uid`大于等于10000,则更新`user`表的`card_balance`字段,将其值加上`@ssje`,并将`points`字段的值减去`@ssje`向下取整后的结果。
3. 创建一个临时表`lsb`,用于存储销售明细数据。将`cashaccount_detail`表中与`cashaccountid`相等的记录按照`gid`分组,计算每组的`salesquantity`总和,并将结果插入到`lsb`表中。
4. 更新`stock`表的`quantity`字段,将其值加上`lsb`表中对应的`xssl`值。
5. 删除`cashaccount_detail`表中与`cashaccountid`相等的记录。
6. 删除`cashaccount`表中与`cashaccountid`相等的记录。
🎯扎帐过程
DELIMITER $$ CREATE PROCEDURE `xiaoshou`.`ZzjlCp`(syyid INT,ssje DECIMAL(18,3)) BEGIN -- 计算应收金额 SELECT SUM(`cashaccount`.`actual_money`) INTO @yjje FROM `cashaccount` WHERE `cashaccount`.`cashierid`=syyid AND `payment`=0 AND `settle_accountsid` IS NULL; -- 插入扎帐记录 IF ABS(@yjje-ssje)>0.01 THEN INSERT INTO `settle_accounts` (cashierid,amount_money,actual_money,errorReason ) VALUE (syyid,@yjje,ssje,'出差错'); ELSE INSERT INTO `settle_accounts` (cashierid,amount_money,actual_money) VALUE (syyid,@yjje,ssje); END IF; SET @zzid=@@identity; -- 更新 收银记录表中的zzid; UPDATE `cashaccount` SET `settle_accountsid`=@zzid WHERE `cashaccount`.`cashierid`=syyid AND `settle_accountsid` IS NULL; END$$ DELIMITER ;
这是一个MySQL存储过程,名为`ZzjlCp`,接受两个参数:`syyid`(收银员ID)和`ssje`(实际收款金额)。该存储过程的功能如下:
1. 计算应收金额:从`cashaccount`表中查询出收银员ID为`syyid`、支付方式为现金且未结算的记录的实际收款金额之和,赋值给变量`@yjje`。
2. 插入扎帐记录:如果`@yjje`与`ssje`之间的差值大于0.01,则在`settle_accounts`表中插入一条记录,包含收银员ID、应收金额、实际收款金额和错误原因;否则,只插入收银员ID、应收金额和实际收款金额。
3. 更新收银记录表中的扎帐记录ID:将`cashaccount`表中收银员ID为`syyid`且未结算的记录的`settle_accountsid`字段设置为刚刚插入的扎帐记录ID。
🎯事后退货处理
DELIMITER $$ CREATE PROCEDURE `xiaoshou`.`thjlcp_new2`(syid INT) BEGIN IF syid>0 THEN /*当天退货*/ DELETE FROM cashaccount WHERE cashaccount.cashaccountid=syid; ELSE /*事后退货*/ /*增加一条负销售*/ INSERT INTO cashaccount(cashierid,uid,payment,amount_money,actual_money,discount_money,sales_status) SELECT cashierid,uid,payment,-amount_money,-actual_money, -discount_money,-1 FROM cashaccount WHERE `cashaccount`.`cashaccountid`=-syid; SET @syid_new=@@identity; /*修改销售状态,以避免多次退货*/ UPDATE cashaccount SET sales_status=@syid_new WHERE cashaccount.cashaccountid=-syid; /*在销售明细表中插入负销售*/ INSERT INTO cashaccount_detail (cashaccountid,gid,salesquantity,retailprice,promotionalprice) SELECT @syid_new,gid,-salesquantity,retailprice,promotionalprice FROM `cashaccount_detail` WHERE `cashaccount_detail`.`cashaccountid`=-syid; /*变更库存*/ DROP TABLE IF EXISTS lsb; CREATE TEMPORARY TABLE lsb (gid INT,xssl NUMERIC(18,3)); INSERT INTO lsb (gid,xssl) SELECT gid,SUM(salesquantity) FROM cashaccount_detail WHERE cashaccount_detail.cashaccountid=-syid GROUP BY gid ; UPDATE stock b1,lsb b2 SET b1.`quantity`=b1.`quantity`+b2.xssl WHERE b1.gid=b2.gid; /*如果是会员,还原积分*/ SELECT `uid` INTO @uid FROM `cashaccount` WHERE `cashaccount`.`cashaccountid`=-syid; SELECT `actual_money` INTO @ssje FROM `cashaccount` WHERE `cashaccount`.`cashaccountid`=-syid; IF @uid>=10000 THEN UPDATE `user` SET `card_balance`=`card_balance`+@ssje, `points`=`points`-FLOOR(@ssje) WHERE `user`.`uid`=@uid; END IF; END IF; END$$ DELIMITER ;
这是一个MySQL存储过程,名为`thjlcp_new2`,接受一个参数:`syid`(销售记录ID)。该存储过程的功能如下:
1. 如果`syid`大于0,表示当天退货,那么从`cashaccount`表中删除对应的记录。
2. 如果`syid`小于等于0,表示事后退货,那么执行以下操作:
a. 在`cashaccount`表中插入一条负销售记录。
b. 更新销售状态,以避免多次退货。
c. 在销售明细表中插入负销售记录。
d. 变更库存。
e. 如果是会员,还原积分。