数据库进销存系统基础操作的实现

一、把业务逻辑对应的sql语句分别写出来--以货品信息表操作为例

1、货品信息维护（增、删、改）

1)新增货品 INSERT INTO goods (gname,unit,barcode,retail_Price,promotional_Price,STATUS) VALUES (@gname,@unit,@barcode,@retail_Price,@promotional_Price,@STATUS)

2)修改货品 UPDATE  goods SET gname=@gname, unit=@unit,barcode=@barcode, retail_Price=@retail_Price, promotional_Price=@promotional_Price,STATUS=@STATUS WHERE gid=@gid

3)删除货品 Delete from goods WHERE gid=@gid

2、货品信息查询

1)查询全部货品 select * from goods

2)根据hpid查询货品信息 select * from goods where gid=@gid

二、把和业务逻辑及与之对应的SQL语句搬到数据库上 一个业务逻辑可以对应一个存储过程，也可以多个业务逻辑对应一个存储过程，依据参数的特点来决定

1）货品信息维护（goodswh）

DELIMITER $$

CREATE  PROCEDURE goodswh     (gid INT,gname VARCHAR(20),     unit VARCHAR(20),barcode VARCHAR(20),     retail_Price DECIMAL(10,2),     promotional_Price DECIMAL(10,2),     STATUS TINYINT     )    

BEGIN        

IF gid=0 THEN       

 INSERT INTO goods         (gname,unit,barcode,retail_Price,promotional_Price,STATUS)         VALUES (gname,unit,barcode,retail_Price,promotional_Price,STATUS);        

ELSEIF gid>0 THEN         

UPDATE  goods SET goods.gname=gname, goods.unit=unit,          goods.barcode=barcode, goods.retail_Price=retail_Price, goods.promotional_Price=promotional_Price,                  goods.STATUS=STATUS         WHERE  goods.gid=gid;     

ELSE       

 DELETE FROM goods WHERE  goods.gid=-gid;     

END IF;    

END$$

DELIMITER ;

2）货品信息查询（goodscx）

DELIMITER $$

CREATE PROCEDURE goodscx(gid INT)

BEGIN  

IF gid=0 THEN     SELECT * FROM goods;  

ELSE     SELECT * FROM goods  WHERE goods.gid=gid;  

END IF;

END $$

DELIMITER ;

三、名称缩写的维护（goods）

1）创建函数PysxCx()

DELIMITER $$

CREATE DEFINER=`root`@`localhost` FUNCTION `PysxCx`(`zw` VARCHAR(50))

RETURNS VARCHAR(10)

READS SQL DATA

BEGIN     

SET @pysx='';             

SET @l=CHAR_LENGTH(zw);     

SET @i=1;     

WHILE (@i<=@l) DO           

SELECT jp INTO @jp FROM hzpyb       

WHERE hz=SUBSTR(zw,@i,1);           

SET @pysx=CONCAT(@pysx,@jp);         

SET @i=@i+1;                  

END WHILE;         

RETURN @pysx;

END

$$ DELIMITER ;

三、拼音缩写实现（goods）

2）创建触发器，在插入货品信息时更改货品名称拼音缩写

DELIMITER $$

CREATE    TRIGGER `Update_mcsx_before_insert_goods`    

BEFORE INSERT ON  goods        

FOR EACH ROW BEGIN        

SET new.abbreviations=pysxcx(new.gname);    

END

$$ DELIMITER ;

三、拼音缩写实现（goods）

3）创建触发器，在更新商品信息时更改货品名称拼音缩写

DELIMITER $$

CREATE      TRIGGER `Update_mcsx_before_update_goods_gname`

BEFORE UPDATE ON `goods`    

FOR EACH ROW BEGIN          

IF new.gname<>old.gname THEN                  

SET new.abbreviations=pysxcx(new.gname);          

END IF;    

END

$$ DELIMITER ;

四、货品名称表，货品库存表信息维护

1）添加货品时，自动在库存中添加一条对应数据

DELIMITER $$

CREATE    TRIGGER Insert_into_stock_after_insert_name    

AFTER INSERT ON goods FOR EACH ROW  BEGIN        

INSERT INTO stock(gid) VALUES (new.gid);    

END

$$ DELIMITER ;

ALTER TABLE  stock   MODIFY  quantity  DECIMAL(18,3) DEFAULT 0.000 NOT NULL COMMENT '(当前)库存数量', MODIFY yesterday_quantity DECIMAL(18,3) DEFAULT 0.000 NOT NULL COMMENT '昨日库存(用于日清),系统自动维护', MODIFY month_quantity DECIMAL(18,3) DEFAULT 0.000 NOT NULL COMMENT '月初库存(用于月结),系统自动维护';

四、货品名称表，货品库存表信息维护

2）删除货品时，自动在库存中删除对应库存数据

DELIMITER $$

CREATE    TRIGGER delete_from_stock_after_delete_goods

AFTER DELETE ON  goods      

FOR EACH ROW BEGIN        

DELETE FROM stock WHERE gid=old.gid;  

END

$$ DELIMITER ;

创建存储过程

DELIMITER $$

CREATE     PROCEDURE `salesmanagement`.`xsjlcp`(xsmx VARCHAR(1000))        

BEGIN          

DROP TABLE IF EXISTS lsb;         

CREATE TEMPORARY TABLE lsb (gid INT,xssl NUMERIC(18,3));                          

WHILE (xsmx<>'') DO               

SET @k=POSITION(',' IN xsmx);                 

SET @gid=LEFT(xsmx,@k-1);                 

SET xsmx=SUBSTR(xsmx,@k+1);                 

SET @k=POSITION(',' IN xsmx);                 

SET @xssl=LEFT(xsmx,@k-1);                 

SELECT @xssl;                 

INSERT INTO lsb VALUES(@gid,@xssl);                 

SET xsmx=SUBSTR(xsmx,@k+1);             

END WHILE;             

SELECT * FROM lsb;             

SELECT barcode,gname,unit,retail_Price,             

promotional_Price,xssl,xssl*promotional_Price AS 金额             

FROM goods,lsb WHERE goods.gid=lsb.gid;    

END

$$ DELIMITER ;

七、实现销售业务

/*1. cashaccount  insert   cashierid uid  amount_money actual_money payment   需要从用户界面传过来cashierid uid  amount_money actual_money payment */  

insert into cashaccount(cashierid uid  amount_money actual_money payment) values (cashierid uid  amount_money actual_money payment);

/*2. cashaccount_detil  insert cashierid gid salesquantity retailprice promotionalprice  需要从用户界面传过来：gid xssl*/

insert into cashaccount_detil (cashiered,gid,salesquantity,retailprice promotionalprice) SELECT @cashaccountid ,lsb.gid,xssl, cashiered,gid,salesquantity,retailprice promotionalprice from lsb,goods where lsb.gid=goods.gid;

/*3.stock  update  依据gid，xssl更新quantity  需要从用户界面传过来：gid xssl。解决不同收银员的输入方式增加下面操作 */  

DROP TABLE IF EXISTS lsb1;   CREATE TEMPORARY TABLE lsb1 (gid INT,xssl decimal(18,3));   INSERT INTO lsb1 SELECT gid,SUM(xssl) FROM lsb GROUP BY gid;   UPDATE stock b1,lsb1 b2 SET b1.kquantity=b1. quantity-b2.xssl WHERE b1.gid=b2.gid;

/*4.user  update  依据uid, actual_money更新knye，kyjf 需要从用户界面传过来：hpid ssje*/         if uid>10000 then         update user set card_balance =card_balance- actual_money, points =points+floor(actual_money), last_purchase_date =getdate() where `user`.`uid`=uid;       end if;

/*查询销售记录*/

SELECT `cashaccountid`,`cashierid`, `cashier_time`,`uid`,`amount_money`,`actual_money`,`discount_money`,CASE `payment`            

 WHEN 0 THEN '现金'                      

WHEN 1 THEN '储值卡'                      

WHEN 2 THEN '支付宝'                      

WHEN 3 THEN '微信支付'                    

END  AS 支付方式                    

FROM `cashaccount` WHERE `cashierid`= 1 AND `cashier_time`>=CURDATE()

ORDER BY `cashaccountid` DESC;

创建视图：

创建销售明细视图

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` );

收银记录查询存储过程实现：一般多个查询可以共享一个存储过程，通过增加一个类别参数区分。 DELIMITER $$

DROP PROCEDURE IF EXISTS `SyjlCx`$$ CREATE DEFINER=`root`@`localhost` PROCEDURE `SyjlCx`(cxlb INT,cxcs INT)

BEGIN  

IF cxlb=0 THEN    SELECT `cashaccountid`,`cashierid`, `cashier_time`,`uid`,`amount_money`,`actual_money`,`discount_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.cashaccountid=cxcs;  

END IF;

END

$$ DELIMITER ;

退货业务：

DELIMITER $$

DROP PROCEDURE IF EXISTS `ThjlCp`$$ CREATE DEFINER=`root`@`localhost` PROCEDURE `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 ;

/*在cashaccount上增加删除触发器，删除一条记录时，用触发器还原库存、会员积分，同时删除销售记录明细表*/

DELIMITER $$

CREATE       TRIGGER `salesmanagement`.`update_stock_user_delete_cdetail_before_delete_casha`    

BEFORE  DELETE     ON `salesmanagement`.`cashaccount`    

FOR EACH ROW BEGIN          

IF old.uid>=10000 THEN              

UPDATE `user` SET card_balance=card_balance+old.actual_money,            

points=points-FLOOR(old.actual_money)              

WHERE uid=old.uid;          

END IF;          

/*更新库存*/            

DELETE  FROM lsb3;            

INSERT INTO lsb3 (gid,xssl)            

SELECT gid,SUM(salesquantity) FROM cashaccount_detail            

WHERE cashaccountid=old. cashaccountid            

GROUP BY gid;            

UPDATE stock  b1,lsb3 b2            

SET b1.quantity=b1.quantity+b2.xssl        

WHERE b1.gid=b2.gid;          

/*删除销售明细*/          

DELETE FROM cashaccount_detail          

WHERE  cashaccountid=old.cashaccountid;    

END$$

DELIMITER ;  

事后退货：

DELIMITER $$

CREATE       PROCEDURE `salesmanagement`.`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 ;    

扎帐处理：

二、实现存储过程

DELIMITER $$

USE `salesmanagement`$$ DROP PROCEDURE IF EXISTS `ZzjlCp`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `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 ;