进销存管理系统mypos2

CREATE DATABASE /*!32312 IF NOT EXISTS*/`mypos` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;

USE `mypos`;

/*Table structure for table `ckb` */

DROP TABLE IF EXISTS `ckb`;

CREATE TABLE `ckb` (
  `ckid` INT NOT NULL AUTO_INCREMENT,
  `ckmc` VARCHAR(20) DEFAULT NULL,
  `ckdz` VARCHAR(20) DEFAULT NULL,
  PRIMARY KEY (`ckid`)
) ENGINE=INNODB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb3;

/*Data for the table `ckb` */

INSERT  INTO `ckb`(`ckid`,`ckmc`,`ckdz`) VALUES (1,'1号仓库','洛阳市武汉路39号');

/*Table structure for table `czyb` */

DROP TABLE IF EXISTS `czyb`;

CREATE TABLE `czyb` (
  `czyid` INT NOT NULL AUTO_INCREMENT,
  `czydm` VARCHAR(20) DEFAULT NULL,
  `czykl` VARCHAR(20) DEFAULT NULL,
  `czylb` INT DEFAULT NULL,
  PRIMARY KEY (`czyid`)
) ENGINE=INNODB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb3;

/*Data for the table `czyb` */

INSERT  INTO `czyb`(`czyid`,`czydm`,`czykl`,`czylb`) VALUES (1,'000','000',0),(2,'001','001',1),(3,'000','000',0),(4,'001','001',1);

/*Table structure for table `ghsb` */

DROP TABLE IF EXISTS `ghsb`;

CREATE TABLE `ghsb` (
  `gysid` INT NOT NULL AUTO_INCREMENT,
  `gysmc` VARCHAR(20) DEFAULT NULL,
  `gysmcsx` VARCHAR(20) DEFAULT NULL,
  `gysdz` VARCHAR(20) DEFAULT NULL,
  `gyslxfs` VARCHAR(20) DEFAULT NULL,
  `gysbz` VARCHAR(20) DEFAULT NULL,
  PRIMARY KEY (`gysid`),
  KEY `gysmcsy` (`gysmc`)
) ENGINE=INNODB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb3;

/*Data for the table `ghsb` */

INSERT  INTO `ghsb`(`gysid`,`gysmc`,`gysmcsx`,`gysdz`,`gyslxfs`,`gysbz`) VALUES (1,'洛阳宏远商贸有限公司','lyhysmyxgs','洛阳市三川大道120号','13903790379',NULL),(2,'洛阳宏远商贸有限公司','lyhysmyxgs','洛阳市三川大道120号','13903790379',NULL);

/*Table structure for table `jhmxb` */

DROP TABLE IF EXISTS `jhmxb`;

CREATE TABLE `jhmxb` (
  `jhmxid` INT NOT NULL AUTO_INCREMENT,
  `jhid` INT DEFAULT NULL,
  `spid` INT DEFAULT NULL,
  `jhdj` DOUBLE DEFAULT NULL,
  `jhsl` INT DEFAULT NULL,
  PRIMARY KEY (`jhmxid`),
  KEY `jhid` (`jhid`),
  KEY `spid` (`spid`),
  CONSTRAINT `jhmxb_ibfk_1` FOREIGN KEY (`jhid`) REFERENCES `jhzb` (`jhid`),
  CONSTRAINT `jhmxb_ibfk_2` FOREIGN KEY (`spid`) REFERENCES `spb` (`spid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb3;

/*Data for the table `jhmxb` */

/*Table structure for table `jhzb` */

DROP TABLE IF EXISTS `jhzb`;

CREATE TABLE `jhzb` (
  `jhid` INT NOT NULL AUTO_INCREMENT,
  `gysid` INT DEFAULT NULL,
  `jhrq` VARCHAR(20) DEFAULT NULL,
  `jhje` DOUBLE DEFAULT NULL,
  `czyid` INT DEFAULT NULL,
  `jhbz` VARCHAR(20) DEFAULT NULL,
  PRIMARY KEY (`jhid`),
  KEY `gysid` (`gysid`),
  KEY `czyid` (`czyid`),
  CONSTRAINT `jhzb_ibfk_1` FOREIGN KEY (`gysid`) REFERENCES `ghsb` (`gysid`),
  CONSTRAINT `jhzb_ibfk_2` FOREIGN KEY (`czyid`) REFERENCES `czyb` (`czyid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb3;

/*Data for the table `jhzb` */

/*Table structure for table `kcb` */

DROP TABLE IF EXISTS `kcb`;

CREATE TABLE `kcb` (
  `kcid` INT NOT NULL AUTO_INCREMENT,
  `ckid` INT DEFAULT NULL,
  `spid` INT DEFAULT NULL,
  `jhpjdj` DOUBLE DEFAULT NULL,
  `xspjdj` DOUBLE DEFAULT NULL,
  `kcsl` INT DEFAULT NULL,
  `zgkcl` INT DEFAULT NULL,
  `zdkcl` INT DEFAULT NULL,
  PRIMARY KEY (`kcid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb3;

/*Data for the table `kcb` */

/*Table structure for table `khb` */

DROP TABLE IF EXISTS `khb`;

CREATE TABLE `khb` (
  `khid` INT NOT NULL AUTO_INCREMENT,
  `khmc` VARCHAR(20) DEFAULT NULL,
  `khmcsx` VARCHAR(20) DEFAULT NULL,
  `khdz` VARCHAR(20) DEFAULT NULL,
  `khlxfs` VARCHAR(20) DEFAULT NULL,
  `khbz` VARCHAR(20) DEFAULT NULL,
  PRIMARY KEY (`khid`),
  KEY `khmcsy` (`khmc`)
) ENGINE=INNODB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb3;

/*Data for the table `khb` */

INSERT  INTO `khb`(`khid`,`khmc`,`khmcsx`,`khdz`,`khlxfs`,`khbz`) VALUES (1,'洛阳前进超市','lyqjcs','洛阳市开元大道40号','13703790379',NULL),(2,'洛阳前进超市','lyqjcs','洛阳市开元大道40号','13703790379',NULL);

/*Table structure for table `spb` */

DROP TABLE IF EXISTS `spb`;

CREATE TABLE `spb` (
  `spid` INT NOT NULL AUTO_INCREMENT,
  `spmc` VARCHAR(20) DEFAULT NULL,
  `spmcsx` VARCHAR(20) DEFAULT NULL,
  `sptm` VARCHAR(20) DEFAULT NULL,
  `spdw` VARCHAR(20) DEFAULT NULL,
  `spbz` VARCHAR(20) DEFAULT NULL,
  PRIMARY KEY (`spid`),
  KEY `spmcsy` (`spmc`),
  KEY `spmcsxsy` (`spmcsx`)
) ENGINE=INNODB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb3;

/*Data for the table `spb` */

INSERT  INTO `spb`(`spid`,`spmc`,`spmcsx`,`sptm`,`spdw`,`spbz`) VALUES (1,'可口可乐','kkkl','101',NULL,'瓶'),(2,'百事可乐','bskl','102',NULL,'瓶'),(3,'伊利牛奶','ylnn','103',NULL,'盒'),(4,'农夫山泉','nfsq','104',NULL,'瓶'),(5,'涪陵榨菜','flzc','105',NULL,'袋'),(6,'青岛啤酒','dqpj','106',NULL,'听'),(7,'可口可乐','kkkl','101',NULL,'瓶'),(8,'百事可乐','bskl','102',NULL,'瓶'),(9,'伊利牛奶','ylnn','103',NULL,'盒'),(10,'农夫山泉','nfsq','104',NULL,'瓶'),(11,'涪陵榨菜','flzc','105',NULL,'袋'),(12,'青岛啤酒','dqpj','106',NULL,'听');

/*Table structure for table `xsmxb` */

DROP TABLE IF EXISTS `xsmxb`;

CREATE TABLE `xsmxb` (
  `xsmxid` INT NOT NULL AUTO_INCREMENT,
  `xsid` INT DEFAULT NULL,
  `spid` INT DEFAULT NULL,
  `xsdj` DOUBLE DEFAULT NULL,
  `xssl` INT DEFAULT NULL,
  PRIMARY KEY (`xsmxid`),
  KEY `xsid` (`xsid`),
  KEY `spid` (`spid`),
  CONSTRAINT `xsmxb_ibfk_1` FOREIGN KEY (`xsid`) REFERENCES `xszb` (`xsid`),
  CONSTRAINT `xsmxb_ibfk_2` FOREIGN KEY (`spid`) REFERENCES `spb` (`spid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb3;

/*Data for the table `xsmxb` */

/*Table structure for table `xszb` */

DROP TABLE IF EXISTS `xszb`;

CREATE TABLE `xszb` (
  `xsid` INT NOT NULL AUTO_INCREMENT,
  `khid` INT DEFAULT NULL,
  `xsrq` VARCHAR(20) DEFAULT NULL,
  `xsje` DOUBLE DEFAULT NULL,
  `czyid` INT DEFAULT NULL,
  `xsbz` VARCHAR(20) DEFAULT NULL,
  PRIMARY KEY (`xsid`),
  KEY `khid` (`khid`),
  KEY `czyid` (`czyid`),
  CONSTRAINT `xszb_ibfk_1` FOREIGN KEY (`khid`) REFERENCES `khb` (`khid`),
  CONSTRAINT `xszb_ibfk_2` FOREIGN KEY (`czyid`) REFERENCES `czyb` (`czyid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb3;

/*Data for the table `xszb` */

/* Trigger structure for table `jhmxb` */

DELIMITER $$

/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `updatekcsl` */$$

/*!50003 CREATE */ /*!50017 DEFINER = 'root'@'localhost' */ /*!50003 TRIGGER `updatekcsl` BEFORE INSERT ON `jhmxb` FOR EACH ROW BEGIN

    update kcb SET kcsl = kcsl + new.jhsl WHERE spid=new.spid;

END */$$

DELIMITER ;

/* Trigger structure for table `spb` */

DELIMITER $$

/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `updatepysx` */$$

/*!50003 CREATE */ /*!50017 DEFINER = 'root'@'localhost' */ /*!50003 TRIGGER `updatepysx` BEFORE INSERT ON `spb` FOR EACH ROW BEGIN
        SET new.spmcsx=qjp(new.spmc);
    END */$$

DELIMITER ;

/* Trigger structure for table `spb` */

DELIMITER $$

/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `updatepysx1` */$$

/*!50003 CREATE */ /*!50017 DEFINER = 'root'@'localhost' */ /*!50003 TRIGGER `updatepysx1` BEFORE UPDATE ON `spb` FOR EACH ROW BEGIN
    IF (old.spmc != new.spmc) THEN
        SET new.spmcsx=qjp(new.spmc);
    END IF;
    END */$$

DELIMITER ;

/* Function  structure for function  `qjp` */

/*!50003 DROP FUNCTION IF EXISTS `qjp` */;
DELIMITER $$

/*!50003 CREATE DEFINER=`root`@`localhost` FUNCTION `qjp`(p_name VARCHAR(100)) RETURNS varchar(100) CHARSET utf8mb3
    NO SQL
begin
        DECLARE V_COMPARE  VARCHAR(255);
        DECLARE V_RETURN VARCHAR(255);
        DECLARE I INT;
        SET I = 1;
        SET V_RETURN = '';
        WHILE I < LENGTH(P_NAME) DO
                SET V_COMPARE = SUBSTR(P_NAME, I, 1);
                IF (V_COMPARE != '') THEN
                    SET V_RETURN = CONCAT(V_RETURN, qpysx(V_COMPARE));
                END IF;
                SET I = I + 1;
            END WHILE;
        IF (ISNULL(V_RETURN) OR V_RETURN = '') THEN
            SET V_RETURN = P_NAME;
        END IF;
        RETURN V_RETURN;
end */$$
DELIMITER ;

/* Function  structure for function  `qpysx` */

/*!50003 DROP FUNCTION IF EXISTS `qpysx` */;
DELIMITER $$

/*!50003 CREATE DEFINER=`root`@`localhost` FUNCTION `qpysx`(p_name VARCHAR(10)) RETURNS varchar(10) CHARSET utf8mb3
    NO SQL
begin
    DECLARE V_RETURN VARCHAR(255);
    SET V_RETURN = ELT(INTERVAL (CONV(HEX(LEFT(CONVERT(P_NAME USING gbk), 1)), 16, 10),
                                 0xB0A1, 0xB0C5, 0xB2C1, 0xB4EE, 0xB6EA, 0xB7A2, 0xB8C1, 0xB9FE, 0xBBF7,
                                 0xBFA6, 0xC0AC, 0xC2E8, 0xC4C3, 0xC5B6, 0xC5BE, 0xC6DA, 0xC8BB,
                                 0xC8F6, 0xCBFA, 0xCDDA, 0xCEF4, 0xD1B9, 0xD4D1),
                       'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T',
                       'W', 'X', 'Y', 'Z');
    IF v_return IS NOT NULL THEN
        RETURN V_RETURN;
    ELSE
        RETURN p_name;
    END IF;
end */$$
DELIMITER ;

/* Procedure structure for procedure `jhjlcp` */

/*!50003 DROP PROCEDURE IF EXISTS  `jhjlcp` */;

DELIMITER $$

/*!50003 CREATE DEFINER=`root`@`localhost` PROCEDURE `jhjlcp`(gysid INT,jhje NUMERIC(18,2),czyid INT,jhbz VARCHAR(100),jhmx VARCHAR(8000))
    READS SQL DATA
BEGIN
    DECLARE strls VARCHAR(8000);
    DECLARE jhid INT;
    DECLARE spid INT;
    DECLARE jhdj NUMERIC(18,2);
    DECLARE jhsl NUMERIC(18,2);
    DECLARE t_error INT DEFAULT 0;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1;
    SET autocommit = 0;
    START TRANSACTION;
    INSERT INTO jhzb(gysid,jhrq,jhje,czyid,jhbz) VALUES(gysid,CURDATE(),jhje,czyid,jhbz);
    SET jhid = LAST_INSERT_ID(); -- 修改这里获取最新的自增ID
    WHILE LENGTH(jhmx) > 0 DO -- 使用LENGTH()检查字符串长度
        SET strls = LEFT(jhmx, LOCATE(';', jhmx) - 1);
        SET spid = CAST(SUBSTRING(strls, 1, LOCATE(',', strls) - 1) AS SIGNED);
        SET strls = SUBSTRING(strls, LOCATE(',', strls) + 1);
        SET jhdj = CAST(SUBSTRING(strls, 1, LOCATE(',', strls) - 1) AS DECIMAL);
        SET jhsl = CAST(SUBSTRING(strls, LOCATE(',', strls) + 1) AS DECIMAL);
        INSERT INTO jhmxb(jhid,spid,jhdj,jhsl) VALUES(jhid,spid,jhdj,jhsl);
        SET jhmx = RIGHT(jhmx, LENGTH(jhmx) - LOCATE(';', jhmx)); -- 移除已处理的分号及其前面的内容
    END WHILE;
    IF t_error = 1 THEN
        ROLLBACK;
    ELSE
        COMMIT;
    END IF;
END */$$
DELIMITER ;

/*Table structure for table `jhb_view` */

DROP TABLE IF EXISTS `jhb_view`;

/*!50001 DROP VIEW IF EXISTS `jhb_view` */;
/*!50001 DROP TABLE IF EXISTS `jhb_view` */;

/*!50001 CREATE TABLE  `jhb_view`(
 `spmc` varchar(20) ,
 `jhdj` double ,
 `jhsl` int ,
 `jhrq` varchar(20) 
)*/;

/*Table structure for table `kc_view` */

DROP TABLE IF EXISTS `kc_view`;

/*!50001 DROP VIEW IF EXISTS `kc_view` */;
/*!50001 DROP TABLE IF EXISTS `kc_view` */;

/*!50001 CREATE TABLE  `kc_view`(
 `ckmc` varchar(20) ,
 `spmc` varchar(20) ,
 `kcsl` int 
)*/;

/*Table structure for table `xsb_view` */

DROP TABLE IF EXISTS `xsb_view`;

/*!50001 DROP VIEW IF EXISTS `xsb_view` */;
/*!50001 DROP TABLE IF EXISTS `xsb_view` */;

/*!50001 CREATE TABLE  `xsb_view`(
 `spmc` varchar(20) ,
 `xsdj` double ,
 `xssl` int ,
 `khmc` varchar(20) ,
 `xsrq` varchar(20) 
)*/;

/*View structure for view jhb_view */

/*!50001 DROP TABLE IF EXISTS `jhb_view` */;
/*!50001 DROP VIEW IF EXISTS `jhb_view` */;

/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `jhb_view` AS select `spb`.`spmc` AS `spmc`,`jhmxb`.`jhdj` AS `jhdj`,`jhmxb`.`jhsl` AS `jhsl`,`jhzb`.`jhrq` AS `jhrq` from (((`jhzb` join `jhmxb`) join `ghsb`) join `spb`) where ((`jhzb`.`jhid` = `jhmxb`.`jhmxid`) and (`jhzb`.`gysid` = `ghsb`.`gysid`) and (`jhmxb`.`spid` = `spb`.`spid`)) */;

/*View structure for view kc_view */

/*!50001 DROP TABLE IF EXISTS `kc_view` */;
/*!50001 DROP VIEW IF EXISTS `kc_view` */;

/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `kc_view` AS select `ckb`.`ckmc` AS `ckmc`,`spb`.`spmc` AS `spmc`,`kcb`.`kcsl` AS `kcsl` from ((`kcb` join `ckb`) join `spb`) where ((`kcb`.`ckid` = `ckb`.`ckid`) and (`kcb`.`spid` = `spb`.`spid`)) */;

/*View structure for view xsb_view */

/*!50001 DROP TABLE IF EXISTS `xsb_view` */;
/*!50001 DROP VIEW IF EXISTS `xsb_view` */;

/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `xsb_view` AS select `spb`.`spmc` AS `spmc`,`xsmxb`.`xsdj` AS `xsdj`,`xsmxb`.`xssl` AS `xssl`,`khb`.`khmc` AS `khmc`,`xszb`.`xsrq` AS `xsrq` from (((`xszb` join `xsmxb`) join `khb`) join `spb`) where ((`xszb`.`xsid` = `xsmxb`.`xsid`) and (`xszb`.`khid` = `khb`.`khid`) and (`xsmxb`.`spid` = `spb`.`spid`)) */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;