CREATE DATABASE `demo` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `demo`;
-- MySQL dump 10.13  Distrib 5.7.9, for Win32 (AMD64)
--
-- Host: localhost    Database: demo
-- ------------------------------------------------------
-- Server version	5.7.12-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `account`
--

DROP TABLE IF EXISTS `account`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `account` (
  `id` varchar(12) NOT NULL COMMENT '科目代码',
  `name` varchar(250) NOT NULL COMMENT '科目名称',
  `Fname` varchar(250) NOT NULL COMMENT '科目全称',
  `t` varchar(250) NOT NULL COMMENT '科目分类',
  `dc` varchar(250) NOT NULL COMMENT '科目方向',
  `slwb` tinyint(1) NOT NULL COMMENT '数量外币',
  `xjll` tinyint(1) NOT NULL COMMENT '现金流量',
  `kh` tinyint(1) NOT NULL COMMENT '客户',
  `gr` tinyint(1) NOT NULL COMMENT '个人',
  `ch` tinyint(1) NOT NULL COMMENT '存货',
  `gys` tinyint(1) NOT NULL COMMENT '供应商',
  `bm` tinyint(1) NOT NULL COMMENT '部门',
  `xm` tinyint(1) NOT NULL COMMENT '核算项目',
  `etr` varchar(250) NOT NULL COMMENT '录入',
  `chk` varchar(250) DEFAULT NULL COMMENT '审核',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=gb2312;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `account`
--

LOCK TABLES `account` WRITE;
/*!40000 ALTER TABLE `account` DISABLE KEYS */;
INSERT INTO `account` VALUES ('1001','库存现金','库存现金','资产','借方',0,0,0,0,0,0,0,0,'用户１','用户１'),('1002','银行存款','银行存款','资产','借方',0,0,0,0,0,0,0,0,'用户１','用户１'),('100201','中国工商银行','银行存款-中国工商银行','资产','借方',0,0,0,0,0,0,0,0,'用户１','用户１'),('1122','应收账款','应收账款','资产','借方',0,0,-1,0,0,0,0,0,'用户１','用户１'),('1231','其他应收款','其他应收款','资产','借方',0,0,0,-1,0,0,0,0,'用户１','用户１'),('1403','原材料','原材料','资产','借方',-1,0,0,0,-1,0,0,0,'用户１','用户１'),('1601','固定资产','固定资产','资产','借方',0,0,0,0,0,0,0,0,'用户１','用户１'),('1602','固定资产折旧','固定资产折旧','资产','贷方',0,0,0,0,0,0,0,0,'用户１','用户１'),('2001','短期借款','短期借款','负债','贷方',0,0,0,0,0,0,0,0,'用户１','用户１'),('2202','应付账款','应付账款','负债','贷方',0,0,0,0,0,-1,0,0,'用户１','用户１'),('220201','应付','应付账款-应付','负债','贷方',0,0,0,0,0,-1,0,0,'用户１','用户１'),('220202','暂估','应付账款-暂估','负债','贷方',0,0,0,0,0,-1,0,0,'用户１','用户１'),('2211','应付职工薪酬','应付职工薪酬','负债','贷方',0,0,0,0,0,0,0,0,'用户１','用户１'),('2221','应交税费','应交税费','负债','贷方',0,0,0,0,0,0,0,0,'用户１','用户１'),('222101','应交增值税','应交税费-应交增值税','负债','贷方',0,0,0,0,0,0,0,0,'用户１','用户１'),('22210101','进项税额','应交税费-应交增值税-进项税额','负债','借方',0,0,0,0,0,0,0,0,'用户１','用户１'),('22210105','销项税额','应交税费-应交增值税-销项税额','负债','贷方',0,0,0,0,0,0,0,0,'用户１','用户１'),('222102','未交增值税','应交税费-未交增值税','负债','贷方',0,0,0,0,0,0,0,0,'用户１','用户１'),('2241','其他应付款','其他应付款','负债','贷方',0,0,0,0,0,-1,0,0,'用户１','用户１'),('4001','实收资本','实收资本','权益','贷方',0,0,0,0,0,0,0,0,'用户１','用户１'),('4002','资本公积','资本公积','权益','贷方',0,0,0,0,0,0,0,0,'用户１','用户１'),('4101','盈余公积','盈余公积','权益','贷方',0,0,0,0,0,0,0,0,'用户１','用户１'),('4103','本年利润','本年利润','权益','贷方',0,0,0,0,0,0,0,0,'用户１','用户１'),('4104','利润分配','利润分配','权益','贷方',0,0,0,0,0,0,0,0,'用户１','用户１'),('5001','生产成本','生产成本','成本','借方',0,0,0,0,0,0,0,0,'用户１','用户１'),('6001','主营业务收入','主营业务收入','利润','贷方',0,0,0,0,0,0,0,0,'用户１','用户１'),('6401','主营业务成本','主营业务成本','利润','借方',0,0,0,0,0,0,0,0,'用户１','用户１'),('6405','营业税金及附加','营业税金及附加','利润','借方',0,0,0,0,0,0,0,0,'用户１','用户１'),('6601','管理费用','管理费用','利润','借方',0,0,0,0,0,0,0,0,'用户１','用户１'),('660101','工资','管理费用-工资','利润','借方',0,0,0,0,0,0,0,0,'用户１','用户１'),('660103','招待费','管理费用-招待费','利润','借方',0,0,0,0,0,0,0,0,'用户１','用户１'),('660105','差旅费','管理费用-差旅费','利润','借方',0,0,0,0,0,0,0,0,'用户１','用户１'),('6603','财务费用','财务费用','利润','借方',0,0,0,0,0,0,0,0,'用户１','用户１'),('660301','贷款利息','财务费用-贷款利息','利润','借方',0,0,0,0,0,0,0,0,'用户１','用户１'),('660303','银行手续费','财务费用-银行手续费','利润','借方',0,0,0,0,0,0,0,0,'用户１','用户１'),('1406','库存商品','库存商品','资产','借方',-1,0,0,0,-1,0,0,0,'用户１','用户１');
/*!40000 ALTER TABLE `account` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `jc_bm`
--

DROP TABLE IF EXISTS `jc_bm`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `jc_bm` (
  `id` varchar(12) NOT NULL,
  `name` varchar(250) NOT NULL,
  `etr` varchar(250) NOT NULL,
  `chk` varchar(250) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=gb2312;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `jc_bm`
--

LOCK TABLES `jc_bm` WRITE;
/*!40000 ALTER TABLE `jc_bm` DISABLE KEYS */;
/*!40000 ALTER TABLE `jc_bm` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `jc_ch`
--

DROP TABLE IF EXISTS `jc_ch`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `jc_ch` (
  `id` varchar(12) NOT NULL,
  `name` varchar(250) NOT NULL,
  `etr` varchar(250) NOT NULL,
  `chk` varchar(250) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=gb2312;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `jc_ch`
--

LOCK TABLES `jc_ch` WRITE;
/*!40000 ALTER TABLE `jc_ch` DISABLE KEYS */;
INSERT INTO `jc_ch` VALUES ('10000001','电磁阀 ABC~125 件','用户１',NULL),('10000002','单向阀 SL~06 件','用户１',NULL),('10000003','溢流阀 CCB~09C 件','用户１',NULL);
/*!40000 ALTER TABLE `jc_ch` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `jc_gr`
--

DROP TABLE IF EXISTS `jc_gr`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `jc_gr` (
  `id` varchar(12) NOT NULL,
  `name` varchar(250) NOT NULL,
  `etr` varchar(250) NOT NULL,
  `chk` varchar(250) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=gb2312;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `jc_gr`
--

LOCK TABLES `jc_gr` WRITE;
/*!40000 ALTER TABLE `jc_gr` DISABLE KEYS */;
/*!40000 ALTER TABLE `jc_gr` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `jc_gys`
--

DROP TABLE IF EXISTS `jc_gys`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `jc_gys` (
  `id` varchar(12) NOT NULL,
  `name` varchar(250) NOT NULL,
  `etr` varchar(250) NOT NULL,
  `chk` varchar(250) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=gb2312;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `jc_gys`
--

LOCK TABLES `jc_gys` WRITE;
/*!40000 ALTER TABLE `jc_gys` DISABLE KEYS */;
INSERT INTO `jc_gys` VALUES ('100001','武汉华德液压件有限公司','用户１','用户１'),('100002','武汉得乐液压件有限公司','用户１','用户１');
/*!40000 ALTER TABLE `jc_gys` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `jc_kh`
--

DROP TABLE IF EXISTS `jc_kh`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `jc_kh` (
  `id` varchar(12) NOT NULL,
  `name` varchar(250) NOT NULL,
  `etr` varchar(250) NOT NULL,
  `chk` varchar(250) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=gb2312;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `jc_kh`
--

LOCK TABLES `jc_kh` WRITE;
/*!40000 ALTER TABLE `jc_kh` DISABLE KEYS */;
INSERT INTO `jc_kh` VALUES ('1001','ABC公司','用户１','用户１'),('1002','BBC公司','用户１','用户１');
/*!40000 ALTER TABLE `jc_kh` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `jc_xjll`
--

DROP TABLE IF EXISTS `jc_xjll`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `jc_xjll` (
  `id` varchar(12) NOT NULL,
  `name` varchar(250) NOT NULL,
  `etr` varchar(250) NOT NULL,
  `chk` varchar(250) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=gb2312;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `jc_xjll`
--

LOCK TABLES `jc_xjll` WRITE;
/*!40000 ALTER TABLE `jc_xjll` DISABLE KEYS */;
/*!40000 ALTER TABLE `jc_xjll` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `jc_xm`
--

DROP TABLE IF EXISTS `jc_xm`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `jc_xm` (
  `id` varchar(12) NOT NULL,
  `name` varchar(250) NOT NULL,
  `etr` varchar(250) NOT NULL,
  `chk` varchar(250) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=gb2312;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `jc_xm`
--

LOCK TABLES `jc_xm` WRITE;
/*!40000 ALTER TABLE `jc_xm` DISABLE KEYS */;
/*!40000 ALTER TABLE `jc_xm` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `power`
--

DROP TABLE IF EXISTS `power`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `power` (
  `Uid` varchar(12) NOT NULL COMMENT '用户id',
  `Rid` int(11) NOT NULL COMMENT '权限id',
  `Rname` varchar(250) NOT NULL COMMENT '权限名称',
  `Rvalue` tinyint(1) NOT NULL,
  PRIMARY KEY (`Uid`,`Rid`)
) ENGINE=MyISAM DEFAULT CHARSET=gb2312;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `power`
--

LOCK TABLES `power` WRITE;
/*!40000 ALTER TABLE `power` DISABLE KEYS */;
INSERT INTO `power` VALUES ('1002',0,'会计科目',-1),('1002',1,'现金流量',-1),('1002',2,'客户',-1),('1002',3,'个人',-1),('1002',4,'存货',-1),('1002',5,'供应商',-1),('1002',6,'部门',-1),('1002',7,'核算项目',-1),('1002',8,'账务维护',-1),('1002',9,'账务查询',-1);
/*!40000 ALTER TABLE `power` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `user`
--

DROP TABLE IF EXISTS `user`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `user` (
  `id` varchar(12) NOT NULL,
  `name` varchar(250) NOT NULL,
  `password` varchar(250) NOT NULL,
  `telephone` varchar(250) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=gb2312;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `user`
--

LOCK TABLES `user` WRITE;
/*!40000 ALTER TABLE `user` DISABLE KEYS */;
INSERT INTO `user` VALUES ('1001','admin','e10adc3949ba59abbe56e057f20f883e','12345678901'),('1002','用户１','c4ca4238a0b923820dcc509a6f75849b','15839489440');
/*!40000 ALTER TABLE `user` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `v1`
--

DROP TABLE IF EXISTS `v1`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `v1` (
  `id` int(11) NOT NULL,
  `ywrq` date NOT NULL COMMENT '业务日期',
  `lrrq` date NOT NULL COMMENT '录入日期',
  `qj` int(11) NOT NULL COMMENT '期间',
  `pzh` int(11) NOT NULL COMMENT '凭证号',
  `xh` tinyint(4) NOT NULL COMMENT '序号',
  `zy` varchar(250) DEFAULT NULL COMMENT '摘要',
  `km` varchar(12) NOT NULL COMMENT '会计科目',
  `kmF` varchar(250) NOT NULL COMMENT '会计科目全称',
  `slwb` double DEFAULT '0' COMMENT '数量外币',
  `slwbF` double DEFAULT '0' COMMENT '单价汇率',
  `dr` double DEFAULT '0' COMMENT '借方',
  `cr` double DEFAULT '0' COMMENT '贷方',
  `xjll` varchar(12) DEFAULT NULL COMMENT '现金流量',
  `xjllF` varchar(250) DEFAULT NULL COMMENT '现金流量名称',
  `kh` varchar(12) DEFAULT NULL COMMENT '客户',
  `khF` varchar(250) DEFAULT NULL COMMENT '客户名称',
  `gr` varchar(12) DEFAULT NULL COMMENT '个人',
  `grF` varchar(250) DEFAULT NULL COMMENT '个人名称',
  `ch` varchar(12) DEFAULT NULL COMMENT '存货',
  `chF` varchar(250) DEFAULT NULL COMMENT '存货名称',
  `gys` varchar(12) DEFAULT NULL COMMENT '供应商',
  `gysF` varchar(250) DEFAULT NULL COMMENT '供应商名称',
  `bm` varchar(12) DEFAULT NULL COMMENT '部门',
  `bmF` varchar(250) DEFAULT NULL COMMENT '部门名称',
  `xm` varchar(12) DEFAULT NULL COMMENT '项目',
  `xmF` varchar(250) DEFAULT NULL COMMENT '项目名称',
  `etr` varchar(250) NOT NULL COMMENT '录入',
  `chk` varchar(250) DEFAULT NULL COMMENT '审核',
  `acc` varchar(250) DEFAULT NULL COMMENT '记账',
  PRIMARY KEY (`id`),
  KEY `qj` (`qj`),
  KEY `pzh` (`pzh`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=gb2312;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `v1`
--

LOCK TABLES `v1` WRITE;
/*!40000 ALTER TABLE `v1` DISABLE KEYS */;
INSERT INTO `v1` VALUES (1,'2016-05-31','2016-05-31',201605,1,1,'公司注册成立','100201','100201-银行存款-中国工商银行',0,0,10000000,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'用户１','用户１','用户１'),(2,'2016-05-31','2016-05-31',201605,1,2,'公司注册成立','4001','4001-实收资本',0,0,0,10000000,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'用户１','用户１','用户１'),(3,'2016-06-19','2016-06-19',201606,1,1,'取现','1001','1001-库存现金',0,0,5000,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'用户１','用户１','用户１'),(4,'2016-06-19','2016-06-19',201606,1,2,'取现','100201','100201-银行存款-中国工商银行',0,0,0,5000,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'用户１','用户１','用户１'),(5,'2016-06-19','2016-06-19',201606,2,1,'采购液压元件','1406','1406-库存商品/数量外币-12/存货-10000001-电磁阀 ABC~125 件',12,427.3508,5128.21,0,NULL,NULL,NULL,NULL,NULL,NULL,'10000001','电磁阀 ABC~125 件',NULL,NULL,NULL,NULL,NULL,NULL,'用户１','用户１','用户１'),(6,'2016-06-19','2016-06-19',201606,2,2,'采购液压元件','1406','1406-库存商品/数量外币-8/存货-10000002-单向阀 SL~06 件',8,222.2225,1777.78,0,NULL,NULL,NULL,NULL,NULL,NULL,'10000002','单向阀 SL~06 件',NULL,NULL,NULL,NULL,NULL,NULL,'用户１','用户１','用户１'),(7,'2016-06-19','2016-06-19',201606,2,3,'采购液压元件','1406','1406-库存商品/数量外币-16/存货-10000003-溢流阀 CCB~09C 件',16,299.145,4786.32,0,NULL,NULL,NULL,NULL,NULL,NULL,'10000003','溢流阀 CCB~09C 件',NULL,NULL,NULL,NULL,NULL,NULL,'用户１','用户１','用户１'),(8,'2016-06-19','2016-06-19',201606,2,4,'采购液压元件','220202','220202-应付账款-暂估/供应商-100001-武汉华德液压件有限公司',0,0,0,11692.31,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'100001','武汉华德液压件有限公司',NULL,NULL,NULL,NULL,'用户１','用户１','用户１'),(9,'2016-06-19','2016-06-19',201606,3,1,'收到华德发票','220202','220202-应付账款-暂估/供应商-100001-武汉华德液压件有限公司',0,0,11692.31,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'100001','武汉华德液压件有限公司',NULL,NULL,NULL,NULL,'用户１','用户１','用户１'),(10,'2016-06-19','2016-06-19',201606,3,2,'收到华德发票','22210101','22210101-应交税费-应交增值税-进项税额',0,0,1987.69,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'用户１','用户１','用户１'),(11,'2016-06-19','2016-06-19',201606,3,3,'收到华德发票','220201','220201-应付账款-应付/供应商-100001-武汉华德液压件有限公司',0,0,0,13680,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'100001','武汉华德液压件有限公司',NULL,NULL,NULL,NULL,'用户１','用户１','用户１'),(12,'2016-06-19','2016-06-19',201606,4,1,'支付货款','220201','220201-应付账款-应付/供应商-100001-武汉华德液压件有限公司',0,0,13680,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'100001','武汉华德液压件有限公司',NULL,NULL,NULL,NULL,'用户１','用户１','用户１'),(13,'2016-06-19','2016-06-19',201606,4,2,'支付货款','100201','100201-银行存款-中国工商银行',0,0,0,13680,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'用户１','用户１','用户１'),(14,'2016-06-19','2016-06-19',201606,5,1,'销售货物','1122','1122-应收账款/客户-1001-ABC公司',0,0,13320,0,NULL,NULL,'1001','ABC公司',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'用户１','用户１','用户１'),(15,'2016-06-19','2016-06-19',201606,5,2,'销售货物','6001','6001-主营业务收入',0,0,0,11384.62,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'用户１','用户１','用户１'),(16,'2016-06-19','2016-06-19',201606,5,3,'销售货物','22210105','22210105-应交税费-应交增值税-销项税额',0,0,0,1935.38,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'用户１','用户１','用户１'),(20,'2016-06-19','2016-06-19',201606,6,4,'结转５#凭证成本','1406','1406-库存商品/数量外币-8/存货-10000003-溢流阀 CCB~09C 件',8,299.145,0,2393.16,NULL,NULL,NULL,NULL,NULL,NULL,'10000003','溢流阀 CCB~09C 件',NULL,NULL,NULL,NULL,NULL,NULL,'用户１','用户１','用户１'),(19,'2016-06-19','2016-06-19',201606,6,3,'结转５#凭证成本','1406','1406-库存商品/数量外币-8/存货-10000002-单向阀 SL~06 件',8,222.2225,0,1777.78,NULL,NULL,NULL,NULL,NULL,NULL,'10000002','单向阀 SL~06 件',NULL,NULL,NULL,NULL,NULL,NULL,'用户１','用户１','用户１'),(17,'2016-06-19','2016-06-19',201606,6,1,'结转５#凭证成本','6401','6401-主营业务成本',0,0,7589.75,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'用户１','用户１','用户１'),(18,'2016-06-19','2016-06-19',201606,6,2,'结转５#凭证成本','1406','1406-库存商品/数量外币-8/存货-10000001-电磁阀 ABC~125 件',8,427.3513,0,3418.81,NULL,NULL,NULL,NULL,NULL,NULL,'10000001','电磁阀 ABC~125 件',NULL,NULL,NULL,NULL,NULL,NULL,'用户１','用户１','用户１'),(21,'2016-06-19','2016-06-19',201606,7,1,'支付６月份工资','2211','2211-应付职工薪酬',0,0,9000,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'用户１','用户１','用户１'),(22,'2016-06-19','2016-06-19',201606,7,2,'支付６月份工资','100201','100201-银行存款-中国工商银行',0,0,0,9000,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'用户１','用户１','用户１'),(23,'2016-06-19','2016-06-19',201606,7,3,'结转６月份工资','660101','660101-管理费用-工资',0,0,9000,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'用户１','用户１','用户１'),(24,'2016-06-19','2016-06-19',201606,7,4,'结转６月份工资','2211','2211-应付职工薪酬',0,0,0,9000,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'用户１','用户１','用户１'),(25,'2016-06-19','2016-06-19',201606,8,1,'银行手续费','660303','660303-财务费用-银行手续费',0,0,308,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'用户１','用户１','用户１'),(26,'2016-06-19','2016-06-19',201606,8,2,'银行手续费','100201','100201-银行存款-中国工商银行',0,0,0,308,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'用户１','用户１','用户１'),(30,'2016-06-01','2016-06-01',201606,9,4,'结转损益','660303','660303-财务费用-银行手续费',0,0,0,308,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'用户１','用户１','用户１'),(29,'2016-06-01','2016-06-01',201606,9,3,'结转损益','660101','660101-管理费用-工资',0,0,0,9000,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'用户１','用户１','用户１'),(28,'2016-06-01','2016-06-01',201606,9,2,'结转损益','6401','6401-主营业务成本',0,0,0,7589.75,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'用户１','用户１','用户１'),(27,'2016-06-01','2016-06-01',201606,9,1,'结转损益','6001','6001-主营业务收入',0,0,11384.62,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'用户１','用户１','用户１'),(31,'2016-06-01','2016-06-01',201606,9,5,'结转损益','4103','4103-本年利润',0,0,0,-5513.13,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'用户１','用户１','用户１'),(32,'2016-07-02','2016-07-02',201607,1,1,'李小露报销差旅费','660105','660105-管理费用-差旅费',0,0,2319.5,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'用户１','用户１',NULL),(33,'2016-07-02','2016-07-02',201607,1,2,'李小露报销差旅费','1001','1001-库存现金',0,0,0,2319.5,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'用户１','用户１',NULL),(34,'2016-07-02','2016-07-02',201607,2,1,'收货款','100201','100201-银行存款-中国工商银行',0,0,13320,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'用户１','用户１',NULL),(35,'2016-07-02','2016-07-02',201607,2,2,'收货款','1122','1122-应收账款/客户-1001-ABC公司',0,0,0,13320,NULL,NULL,'1001','ABC公司',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'用户１','用户１',NULL),(36,'2016-07-02','2016-07-02',201607,3,1,'采购液压元件','1406','1406-库存商品/数量外币-12/存货-10000001-电磁阀 ABC~125 件',12,452.3508,5428.21,0,NULL,NULL,NULL,NULL,NULL,NULL,'10000001','电磁阀 ABC~125 件',NULL,NULL,NULL,NULL,NULL,NULL,'用户１','用户１',NULL),(37,'2016-07-02','2016-07-02',201607,3,2,'采购液压元件','1406','1406-库存商品/数量外币-8/存货-10000002-单向阀 SL~06 件',8,259.7225,2077.78,0,NULL,NULL,NULL,NULL,NULL,NULL,'10000002','单向阀 SL~06 件',NULL,NULL,NULL,NULL,NULL,NULL,'用户１','用户１',NULL),(38,'2016-07-02','2016-07-02',201607,3,3,'采购液压元件','1406','1406-库存商品/数量外币-16/存货-10000003-溢流阀 CCB~09C 件',16,317.895,5086.32,0,NULL,NULL,NULL,NULL,NULL,NULL,'10000003','溢流阀 CCB~09C 件',NULL,NULL,NULL,NULL,NULL,NULL,'用户１','用户１',NULL),(39,'2016-07-02','2016-07-02',201607,3,4,'采购液压元件','220202','220202-应付账款-暂估/供应商-100001-武汉华德液压件有限公司',0,0,0,12592.31,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'100001','武汉华德液压件有限公司',NULL,NULL,NULL,NULL,'用户１','用户１',NULL),(40,'2016-07-02','2016-07-02',201607,4,1,'销售货物','1122','1122-应收账款/客户-1002-BBC公司',0,0,33258,0,NULL,NULL,'1002','BBC公司',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'用户１','用户１',NULL),(41,'2016-07-02','2016-07-02',201607,4,2,'销售货物','6001','6001-主营业务收入',0,0,0,28425.64,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'用户１','用户１',NULL),(42,'2016-07-02','2016-07-02',201607,4,3,'销售货物','22210105','22210105-应交税费-应交增值税-销项税额',0,0,0,4832.36,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'用户１','用户１',NULL),(46,'2016-07-02','2016-07-02',201607,5,4,'结转4#凭证成本','1406','1406-库存商品/数量外币-8/存货-10000003-溢流阀 CCB~09C 件',8,311.645,0,2493.16,NULL,NULL,NULL,NULL,NULL,NULL,'10000003','溢流阀 CCB~09C 件',NULL,NULL,NULL,NULL,NULL,NULL,'用户１','用户１',NULL),(43,'2016-07-02','2016-07-02',201607,5,1,'结转4#凭证成本','6401','6401-主营业务成本',0,0,8139.74,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'用户１','用户１',NULL),(44,'2016-07-02','2016-07-02',201607,5,2,'结转4#凭证成本','1406','1406-库存商品/数量外币-8/存货-10000001-电磁阀 ABC~125 件',8,446.1,0,3568.8,NULL,NULL,NULL,NULL,NULL,NULL,'10000001','电磁阀 ABC~125 件',NULL,NULL,NULL,NULL,NULL,NULL,'用户１','用户１',NULL),(45,'2016-07-02','2016-07-02',201607,5,3,'结转4#凭证成本','1406','1406-库存商品/数量外币-8/存货-10000002-单向阀 SL~06 件',8,259.7225,0,2077.78,NULL,NULL,NULL,NULL,NULL,NULL,'10000002','单向阀 SL~06 件',NULL,NULL,NULL,NULL,NULL,NULL,'用户１','用户１',NULL),(49,'2016-07-01','2016-07-01',201607,6,3,'结转损益','6401','6401-主营业务成本',0,0,0,8139.74,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'用户１','用户１',NULL),(48,'2016-07-01','2016-07-01',201607,6,2,'结转损益','6001','6001-主营业务收入',0,0,28425.64,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'用户１','用户１',NULL),(47,'2016-07-01','2016-07-01',201607,6,1,'结转损益','4103','4103-本年利润',0,0,0,17966.4,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'用户１','用户１',NULL),(50,'2016-07-01','2016-07-01',201607,6,4,'结转损益','660105','660105-管理费用-差旅费',0,0,0,2319.5,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'用户１','用户１',NULL);
/*!40000 ALTER TABLE `v1` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `v2`
--

DROP TABLE IF EXISTS `v2`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `v2` (
  `id` int(11) NOT NULL,
  `ywrq` date NOT NULL COMMENT '业务日期',
  `lrrq` date NOT NULL COMMENT '录入日期',
  `qj` int(11) NOT NULL COMMENT '期间',
  `pzh` int(11) NOT NULL COMMENT '凭证号',
  `xh` tinyint(4) NOT NULL COMMENT '序号',
  `zy` varchar(250) DEFAULT NULL COMMENT '摘要',
  `km` varchar(12) NOT NULL COMMENT '会计科目',
  `kmF` varchar(250) NOT NULL COMMENT '会计科目全称',
  `slwb` double DEFAULT '0',
  `slwbF` double DEFAULT '0',
  `dr` double DEFAULT '0' COMMENT '借方',
  `cr` double DEFAULT '0' COMMENT '贷方',
  PRIMARY KEY (`id`),
  KEY `qj` (`qj`),
  KEY `pzh` (`pzh`)
) ENGINE=MyISAM DEFAULT CHARSET=gb2312;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `v2`
--

LOCK TABLES `v2` WRITE;
/*!40000 ALTER TABLE `v2` DISABLE KEYS */;
INSERT INTO `v2` VALUES (1,'2016-05-31','2016-05-31',201605,1,1,'公司注册成立','1002','100201-银行存款-中国工商银行',0,0,10000000,0),(2,'2016-05-31','2016-05-31',201605,1,1,'公司注册成立','100201','100201-银行存款-中国工商银行',0,0,10000000,0),(3,'2016-05-31','2016-05-31',201605,1,2,'公司注册成立','4001','4001-实收资本',0,0,0,10000000),(4,'2016-06-19','2016-06-19',201606,1,1,'取现','1001','1001-库存现金',0,0,5000,0),(5,'2016-06-19','2016-06-19',201606,1,2,'取现','1002','100201-银行存款-中国工商银行',0,0,0,5000),(6,'2016-06-19','2016-06-19',201606,1,2,'取现','100201','100201-银行存款-中国工商银行',0,0,0,5000),(7,'2016-06-19','2016-06-19',201606,2,1,'采购液压元件','1406','1406-库存商品/数量外币-12/存货-10000001-电磁阀 ABC~125 件',12,427.3508,5128.21,0),(8,'2016-06-19','2016-06-19',201606,2,2,'采购液压元件','1406','1406-库存商品/数量外币-8/存货-10000002-单向阀 SL~06 件',8,222.2225,1777.78,0),(9,'2016-06-19','2016-06-19',201606,2,3,'采购液压元件','1406','1406-库存商品/数量外币-16/存货-10000003-溢流阀 CCB~09C 件',16,299.145,4786.32,0),(10,'2016-06-19','2016-06-19',201606,2,4,'采购液压元件','2202','220202-应付账款-暂估/供应商-100001-武汉华德液压件有限公司',0,0,0,11692.31),(11,'2016-06-19','2016-06-19',201606,2,4,'采购液压元件','220202','220202-应付账款-暂估/供应商-100001-武汉华德液压件有限公司',0,0,0,11692.31),(12,'2016-06-19','2016-06-19',201606,3,1,'收到华德发票','2202','220202-应付账款-暂估/供应商-100001-武汉华德液压件有限公司',0,0,11692.31,0),(13,'2016-06-19','2016-06-19',201606,3,1,'收到华德发票','220202','220202-应付账款-暂估/供应商-100001-武汉华德液压件有限公司',0,0,11692.31,0),(14,'2016-06-19','2016-06-19',201606,3,2,'收到华德发票','2221','22210101-应交税费-应交增值税-进项税额',0,0,1987.69,0),(15,'2016-06-19','2016-06-19',201606,3,2,'收到华德发票','222101','22210101-应交税费-应交增值税-进项税额',0,0,1987.69,0),(16,'2016-06-19','2016-06-19',201606,3,2,'收到华德发票','22210101','22210101-应交税费-应交增值税-进项税额',0,0,1987.69,0),(17,'2016-06-19','2016-06-19',201606,3,3,'收到华德发票','2202','220201-应付账款-应付/供应商-100001-武汉华德液压件有限公司',0,0,0,13680),(18,'2016-06-19','2016-06-19',201606,3,3,'收到华德发票','220201','220201-应付账款-应付/供应商-100001-武汉华德液压件有限公司',0,0,0,13680),(19,'2016-06-19','2016-06-19',201606,4,1,'支付货款','2202','220201-应付账款-应付/供应商-100001-武汉华德液压件有限公司',0,0,13680,0),(20,'2016-06-19','2016-06-19',201606,4,1,'支付货款','220201','220201-应付账款-应付/供应商-100001-武汉华德液压件有限公司',0,0,13680,0),(21,'2016-06-19','2016-06-19',201606,4,2,'支付货款','1002','100201-银行存款-中国工商银行',0,0,0,13680),(22,'2016-06-19','2016-06-19',201606,4,2,'支付货款','100201','100201-银行存款-中国工商银行',0,0,0,13680),(23,'2016-06-19','2016-06-19',201606,5,1,'销售货物','1122','1122-应收账款/客户-1001-ABC公司',0,0,13320,0),(24,'2016-06-19','2016-06-19',201606,5,2,'销售货物','6001','6001-主营业务收入',0,0,0,11384.62),(25,'2016-06-19','2016-06-19',201606,5,3,'销售货物','2221','22210105-应交税费-应交增值税-销项税额',0,0,0,1935.38),(26,'2016-06-19','2016-06-19',201606,5,3,'销售货物','222101','22210105-应交税费-应交增值税-销项税额',0,0,0,1935.38),(27,'2016-06-19','2016-06-19',201606,5,3,'销售货物','22210105','22210105-应交税费-应交增值税-销项税额',0,0,0,1935.38),(28,'2016-06-19','2016-06-19',201606,6,1,'结转５#凭证成本','6401','6401-主营业务成本',0,0,7589.75,0),(29,'2016-06-19','2016-06-19',201606,6,2,'结转５#凭证成本','1406','1406-库存商品/数量外币-8/存货-10000001-电磁阀 ABC~125 件',8,427.3513,0,3418.81),(30,'2016-06-19','2016-06-19',201606,6,3,'结转５#凭证成本','1406','1406-库存商品/数量外币-8/存货-10000002-单向阀 SL~06 件',8,222.2225,0,1777.78),(31,'2016-06-19','2016-06-19',201606,6,4,'结转５#凭证成本','1406','1406-库存商品/数量外币-8/存货-10000003-溢流阀 CCB~09C 件',8,299.145,0,2393.16),(32,'2016-06-19','2016-06-19',201606,7,1,'支付６月份工资','2211','2211-应付职工薪酬',0,0,9000,0),(33,'2016-06-19','2016-06-19',201606,7,2,'支付６月份工资','1002','100201-银行存款-中国工商银行',0,0,0,9000),(34,'2016-06-19','2016-06-19',201606,7,2,'支付６月份工资','100201','100201-银行存款-中国工商银行',0,0,0,9000),(35,'2016-06-19','2016-06-19',201606,7,3,'结转６月份工资','6601','660101-管理费用-工资',0,0,9000,0),(36,'2016-06-19','2016-06-19',201606,7,3,'结转６月份工资','660101','660101-管理费用-工资',0,0,9000,0),(37,'2016-06-19','2016-06-19',201606,7,4,'结转６月份工资','2211','2211-应付职工薪酬',0,0,0,9000),(38,'2016-06-19','2016-06-19',201606,8,1,'银行手续费','6603','660303-财务费用-银行手续费',0,0,308,0),(39,'2016-06-19','2016-06-19',201606,8,1,'银行手续费','660303','660303-财务费用-银行手续费',0,0,308,0),(40,'2016-06-19','2016-06-19',201606,8,2,'银行手续费','1002','100201-银行存款-中国工商银行',0,0,0,308),(41,'2016-06-19','2016-06-19',201606,8,2,'银行手续费','100201','100201-银行存款-中国工商银行',0,0,0,308),(42,'2016-06-01','2016-06-01',201606,9,1,'结转损益','6001','6001-主营业务收入',0,0,11384.62,0),(43,'2016-06-01','2016-06-01',201606,9,2,'结转损益','6401','6401-主营业务成本',0,0,0,7589.75),(44,'2016-06-01','2016-06-01',201606,9,3,'结转损益','6601','660101-管理费用-工资',0,0,0,9000),(45,'2016-06-01','2016-06-01',201606,9,3,'结转损益','660101','660101-管理费用-工资',0,0,0,9000),(46,'2016-06-01','2016-06-01',201606,9,4,'结转损益','6603','660303-财务费用-银行手续费',0,0,0,308),(47,'2016-06-01','2016-06-01',201606,9,4,'结转损益','660303','660303-财务费用-银行手续费',0,0,0,308),(48,'2016-06-01','2016-06-01',201606,9,5,'结转损益','4103','4103-本年利润',0,0,0,-5513.13);
/*!40000 ALTER TABLE `v2` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Dumping events for database 'database1'
--

--
-- Dumping routines for database 'database1'
--
/*!50003 DROP PROCEDURE IF EXISTS `copyv1tov2` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection  = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = '' */ ;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `copyv1tov2`(in intqj integer,in acc_user varchar(250))
BEGIN
/*
功能说明：凭证记账，数据由凭证表v1自动写入记账表v2；
参数说明：intqj->会计期间
　　　　　acc_user->记账人
*/
declare int_km int(11) default 4; 
declare tmp_id int(11);
declare tmp_ywrq date;
declare tmp_lrrq date;
declare tmp_qj int(11);
declare tmp_pzh int(11);
declare tmp_xh TINYINT(4);
declare tmp_zy varchar(250);
declare tmp_km,tmp_kmsub varchar(250);
declare tmp_kmF varchar(250);
declare tmp_slwb double;
declare tmp_slwbF double;
declare tmp_dr double;
declare tmp_cr double;
declare info_v1 cursor for select ywrq,lrrq,qj,pzh,xh,zy,km,kmF,slwb,slwbF,dr,cr from v1 where qj=intqj order by qj,pzh,xh;
declare continue handler for sqlstate '02000' set tmp_km=null; 
select count(*) into tmp_id from v2; 
open info_v1;
fetch info_v1 into tmp_ywrq,tmp_lrrq,tmp_qj,tmp_pzh,tmp_xh,tmp_zy,tmp_km,tmp_kmF,tmp_slwb,tmp_slwbF,tmp_dr,tmp_cr;
while(tmp_km is not null) do
if length(tmp_km)=4 then

set tmp_id=tmp_id+1;
insert into v2(id,ywrq,lrrq,qj,pzh,xh,zy,km,kmF,slwb,slwbF,dr,cr) 
values(tmp_id,tmp_ywrq,tmp_lrrq,tmp_qj,tmp_pzh,tmp_xh,tmp_zy,tmp_km,tmp_kmF,tmp_slwb,tmp_slwbF,tmp_dr,tmp_cr);
else
while int_km<=length(tmp_km) do
set tmp_id=tmp_id+1;
set tmp_kmsub=left(tmp_km,int_km);
insert into v2(id,ywrq,lrrq,qj,pzh,xh,zy,km,kmF,slwb,slwbF,dr,cr) 
values(tmp_id,tmp_ywrq,tmp_lrrq,tmp_qj,tmp_pzh,tmp_xh,tmp_zy,tmp_kmsub,tmp_kmF,tmp_slwb,tmp_slwbF,tmp_dr,tmp_cr);
set int_km=int_km+2;
end while;
set int_km=4;
end if;
fetch info_v1 into tmp_ywrq,tmp_lrrq,tmp_qj,tmp_pzh,tmp_xh,tmp_zy,tmp_km,tmp_kmF,tmp_slwb,tmp_slwbF,tmp_dr,tmp_cr;

update v1 set acc=acc_user where qj=intqj;
end while;
END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `count_cost` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection  = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = '' */ ;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `count_cost`(in var_qj int(11),in var_km varchar(250))
lableProce:BEGIN
/*
功能说明：成本自动核算
参数说明：var_qj->会计期间
　　　　　var_km->会计科目
*/
declare r_count double;
declare tmp_km varchar(250);
declare tmp_xm varchar(250);
declare tmp_dc varchar(250);
declare tmp_slwb0 double;
declare tmp_dr0 double;
declare tmp_cr0 double;
declare tmp_ds1 double;
declare tmp_dr1 double;
declare tmp_cs1 double;
declare tmp_cr1 double;

declare tmp_dj double;

declare tmp_id int(11);
declare tmp_pzh int(11);
declare tmp_slwb double;

declare no_more_record int default 0;

declare info cursor for select km,xm,dc,sum(slwb0),sum(dr0),sum(cr0),sum(ds1),sum(dr1),sum(cs1),sum(cr1) from xmyrb group by km,xm,dc having km=var_km;
declare info_v cursor for select id,pzh,slwb,ch from v1 where qj=var_qj and km=var_km and cr<>0;
declare continue handler for not found set no_more_record=1;

select count(*) into r_count from v1 where qj=var_qj and acc is not null;
if r_count>0 then
leave lableProce;
end if;

drop table if exists cbjs;

create temporary table cbjs(
km varchar(250) not null,
xm varchar(250) not null,
dj double default 0 not null);

call xmyrbs(var_qj,var_qj,'ch','id','1','');

open info;
fetch info into tmp_km,tmp_xm,tmp_dc,tmp_slwb0,tmp_dr0,tmp_cr0,tmp_ds1,tmp_dr1,tmp_cs1,tmp_cr1;
while no_more_record!=1 do
if tmp_dc='借方' then
set tmp_dj=(tmp_dr0-tmp_cr0+tmp_dr1)/(tmp_slwb0+tmp_ds1);
else
set tmp_dj=(tmp_cr0-tmp_dr0-tmp_dr1)/(tmp_slwb0-tmp_ds1);
end if;
insert into cbjs(km,xm,dj) values(tmp_km,tmp_xm,tmp_dj);
fetch info into tmp_km,tmp_xm,tmp_dc,tmp_slwb0,tmp_dr0,tmp_cr0,tmp_ds1,tmp_dr1,tmp_cs1,tmp_cr1;
end while;
close info;

set no_more_record=0;

open info_v;
fetch info_v into tmp_id,tmp_pzh,tmp_slwb,tmp_xm;
while no_more_record!=1 do
select dj into tmp_dj from cbjs where xm=tmp_xm;
update v1 set slwbF=round(tmp_dj,4),cr=round(tmp_slwb*tmp_dj,2) where id=tmp_id;
update v1 set chk=null where qj=var_qj and pzh=tmp_pzh;
fetch info_v into tmp_id,tmp_pzh,tmp_slwb,tmp_xm;
end while;
close info_v;
END lableProce ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `kmyrbs` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection  = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = '' */ ;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `kmyrbs`(in qj0 int(11),in qj1 int(11),in km0 varchar(250),in km1 varchar(250),in jzs int(11),in xms int(11),in qcs int(11))
BEGIN
/*
功能说明：自动生成科目余额表
参数说明：qj0->起始期间
　　　　　qj1->终止期间
          km0->起始会计科目
          km1->始止会计科目
          jzs->是否统计记账数据
          xms->是否统计项目数据
          qcs->是否统计期初数据
*/
declare int_km int(11) default 4; 
declare tmp_ywrq date;
declare tmp_lrrq date;
declare tmp_qj int(11);
declare tmp_pzh int(11);
declare tmp_xh TINYINT(4);
declare tmp_zy varchar(250);
declare tmp_km,tmp_kmsub varchar(250);
declare tmp_kma varchar(250);
declare tmp_dc varchar(250);
declare tmp_kmf varchar(250);
declare tmp_slwb double;
declare tmp_dr double;
declare tmp_cr double;
declare tmp_ds1 double;
declare tmp_dr1 double;
declare tmp_cs1 double;
declare tmp_cr1 double;

declare int_i int(6);

declare info_v10 cursor for select ywrq,lrrq,qj,pzh,xh,zy,km,kmf,slwb,dr,cr from v1 where qj<qj0 and acc is null and (km between km0 and km1);
declare info_v11 cursor for select ywrq,lrrq,qj,pzh,xh,zy,km,kmf,slwb,dr,cr from v1 where (qj between qj0 and qj1) and (km between km0 and km1) and acc is null;
declare info_xms cursor for select ywrq,lrrq,qj,pzh,xh,zy,km,kma,dc,kmf,slwb0,dr0,cr0,ds1,dr1,cs1,cr1 from kmyrb;
declare continue handler for sqlstate '02000' set tmp_km=null; 

if km0='' then 
select min(id) into km0 from account;
end if;
if km1='' then 
select max(id) into km1 from account;
end if;

drop table if exists kmyrb;

create temporary table kmyrb(
id int(11) primary key auto_increment not null,
ywrq date not null,
lrrq date not null,
qj int(11) not null,
pzh int(11) not null,
xh tinyint(4) not null,
zy varchar(250) not null,
km varchar(250) not null,
kma varchar(250) not null,
dc varchar(250) not null,
kmf varchar(250) not null,
slwb0 double default 0 not null,
dr0 double default 0 not null,
cr0 double default 0 not null,
ds1 double default 0 not null,
dr1 double default 0 not null,
cs1 double default 0 not null,
cr1 double default 0 not null,
t tinyint(4) not null);


if qcs=1 then
insert into kmyrb(ywrq,lrrq,qj,pzh,xh,zy,km,kma,dc,kmf,slwb0,dr0,cr0,ds1,dr1,cs1,cr1,t) 
select v2.ywrq,v2.lrrq,v2.qj,v2.pzh,v2.xh,v2.zy,v2.km,account.Fname,account.dc,v2.kmf,
if((account.dc='借方' and v2.dr=0) or (account.dc='贷方' and v2.cr=0),-1*v2.slwb,v2.slwb),v2.dr,v2.cr,0,0,0,0,1 
from v2 left join account on v2.km=account.id where (qj<qj0 and (km between km0 and km1));
end if;

insert into kmyrb(ywrq,lrrq,qj,pzh,xh,zy,km,kma,dc,kmf,slwb0,dr0,cr0,ds1,dr1,cs1,cr1,t) 
select v2.ywrq,v2.lrrq,v2.qj,v2.pzh,v2.xh,v2.zy,v2.km,account.Fname,account.dc,v2.kmf,0,0,0,
if(v2.cr=0,v2.slwb,0),v2.dr,if(v2.dr=0,v2.slwb,0),v2.cr,1
from v2 left join account on v2.km=account.id where ((qj between qj0 and qj1) and (km between km0 and km1));


if jzs=1 then

if qcs=1 then
open info_v10;
fetch info_v10 into tmp_ywrq,tmp_lrrq,tmp_qj,tmp_pzh,tmp_xh,tmp_zy,tmp_km,tmp_kmf,tmp_slwb,tmp_dr,tmp_cr;
while(tmp_km is not null) do
if length(tmp_km)=4 then
select Fname into tmp_kma from account where id=tmp_km;
select dc into tmp_dc from account where id=tmp_km;
if (tmp_dc='借方' and tmp_dr=0) or (tmp_dc='贷方' and tmp_cr=0) then
insert into kmyrb(ywrq,lrrq,qj,pzh,xh,zy,km,kma,dc,kmf,slwb0,dr0,cr0,ds1,dr1,cs1,cr1,t) 
values(tmp_ywrq,tmp_lrrq,tmp_qj,tmp_pzh,tmp_xh,tmp_zy,tmp_km,tmp_kma,tmp_dc,tmp_kmf,-1*tmp_slwb,tmp_dr,tmp_cr,0,0,0,0,1);
else
insert into kmyrb(ywrq,lrrq,qj,pzh,xh,zy,km,kma,dc,kmf,slwb0,dr0,cr0,ds1,dr1,cs1,cr1,t) 
values(tmp_ywrq,tmp_lrrq,tmp_qj,tmp_pzh,tmp_xh,tmp_zy,tmp_km,tmp_kma,tmp_dc,tmp_kmf,tmp_slwb,tmp_dr,tmp_cr,0,0,0,0,1);
end if;
else
while int_km<=length(tmp_km) do
set tmp_kmsub=left(tmp_km,int_km);
select Fname into tmp_kma from account where id=tmp_kmsub;
select dc into tmp_dc from account where id=tmp_kmsub;
if (tmp_dc='借方' and tmp_dr=0) or (tmp_dc='贷方' and tmp_cr=0) then
insert into kmyrb(ywrq,lrrq,qj,pzh,xh,zy,km,kma,dc,kmf,slwb0,dr0,cr0,ds1,dr1,cs1,cr1,t) 
values(tmp_ywrq,tmp_lrrq,tmp_qj,tmp_pzh,tmp_xh,tmp_zy,tmp_kmsub,tmp_kma,tmp_dc,tmp_kmf,-1*tmp_slwb,tmp_dr,tmp_cr,0,0,0,0,1);
else
insert into kmyrb(ywrq,lrrq,qj,pzh,xh,zy,km,kma,dc,kmf,slwb0,dr0,cr0,ds1,dr1,cs1,cr1,t) 
values(tmp_ywrq,tmp_lrrq,tmp_qj,tmp_pzh,tmp_xh,tmp_zy,tmp_kmsub,tmp_kma,tmp_dc,tmp_kmf,tmp_slwb,tmp_dr,tmp_cr,0,0,0,0,1);
end if;
set int_km=int_km+2;
end while;
set int_km=4;
end if;
fetch info_v10 into tmp_ywrq,tmp_lrrq,tmp_qj,tmp_pzh,tmp_xh,tmp_zy,tmp_km,tmp_kmf,tmp_slwb,tmp_dr,tmp_cr;
end while;
close info_v10;
end if;

open info_v11;
fetch info_v11 into tmp_ywrq,tmp_lrrq,tmp_qj,tmp_pzh,tmp_xh,tmp_zy,tmp_km,tmp_kmf,tmp_slwb,tmp_dr,tmp_cr;
while(tmp_km is not null) do
if length(tmp_km)=4 then

select Fname into tmp_kma from account where id=tmp_km;
select dc into tmp_dc from account where id=tmp_km;
if tmp_dr=0 then
insert into kmyrb(ywrq,lrrq,qj,pzh,xh,zy,km,kma,dc,kmf,slwb0,dr0,cr0,ds1,dr1,cs1,cr1,t) 
values(tmp_ywrq,tmp_lrrq,tmp_qj,tmp_pzh,tmp_xh,tmp_zy,tmp_km,tmp_kma,tmp_dc,tmp_kmf,0,0,0,0,tmp_dr,tmp_slwb,tmp_cr,1);
else
insert into kmyrb(ywrq,lrrq,qj,pzh,xh,zy,km,kma,dc,kmf,slwb0,dr0,cr0,ds1,dr1,cs1,cr1,t) 
values(tmp_ywrq,tmp_lrrq,tmp_qj,tmp_pzh,tmp_xh,tmp_zy,tmp_km,tmp_kma,tmp_dc,tmp_kmf,0,0,0,tmp_slwb,tmp_dr,0,tmp_cr,1);
end if;
else
while int_km<=length(tmp_km) do
set tmp_kmsub=left(tmp_km,int_km);
select Fname into tmp_kma from account where id=tmp_kmsub;
select dc into tmp_dc from account where id=tmp_kmsub;
if tmp_dr=0 then
insert into kmyrb(ywrq,lrrq,qj,pzh,xh,zy,km,kma,dc,kmf,slwb0,dr0,cr0,ds1,dr1,cs1,cr1,t) 
values(tmp_ywrq,tmp_lrrq,tmp_qj,tmp_pzh,tmp_xh,tmp_zy,tmp_kmsub,tmp_kma,tmp_dc,tmp_kmf,0,0,0,0,tmp_dr,tmp_slwb,tmp_cr,1);
else
insert into kmyrb(ywrq,lrrq,qj,pzh,xh,zy,km,kma,dc,kmf,slwb0,dr0,cr0,ds1,dr1,cs1,cr1,t) 
values(tmp_ywrq,tmp_lrrq,tmp_qj,tmp_pzh,tmp_xh,tmp_zy,tmp_kmsub,tmp_kma,tmp_dc,tmp_kmf,0,0,0,tmp_slwb,tmp_dr,0,tmp_cr,1);
end if;
set int_km=int_km+2;
end while;
set int_km=4;
end if;
fetch info_v11 into tmp_ywrq,tmp_lrrq,tmp_qj,tmp_pzh,tmp_xh,tmp_zy,tmp_km,tmp_kmf,tmp_slwb,tmp_dr,tmp_cr;
end while;
close info_v11;
end if;


if xms=1 then
open info_xms;
fetch info_xms into tmp_ywrq,tmp_lrrq,tmp_qj,tmp_pzh,tmp_xh,tmp_zy,tmp_km,tmp_kma,tmp_dc,tmp_kmf,tmp_slwb,tmp_dr,tmp_cr,tmp_ds1,tmp_dr1,tmp_cs1,tmp_cr1;
while(tmp_km is not null) do
if locate('/',tmp_kmf)!=0 then
set tmp_kmsub=substring(tmp_kmf,locate('/',tmp_kmf));
if locate('数量外币-',tmp_kmsub)!=0 then
set tmp_kmsub=substring(tmp_kmsub,2);
set tmp_kmsub=substring(tmp_kmsub,locate('/',tmp_kmsub));
end if;
insert into kmyrb(ywrq,lrrq,qj,pzh,xh,zy,km,kma,dc,kmf,slwb0,dr0,cr0,ds1,dr1,cs1,cr1,t) 
values(tmp_ywrq,tmp_lrrq,tmp_qj,tmp_pzh,tmp_xh,tmp_zy,concat(tmp_km,"+"),tmp_kmsub,tmp_dc,tmp_kmf,tmp_slwb,tmp_dr,tmp_cr,tmp_ds1,tmp_dr1,tmp_cs1,tmp_cr1,0);
end if;
fetch info_xms into tmp_ywrq,tmp_lrrq,tmp_qj,tmp_pzh,tmp_xh,tmp_zy,tmp_km,tmp_kma,tmp_dc,tmp_kmf,tmp_slwb,tmp_dr,tmp_cr,tmp_ds1,tmp_dr1,tmp_cs1,tmp_cr1;
end while;
close info_xms;
end if;

END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `xmyrbs` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection  = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = '' */ ;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `xmyrbs`(in qj0 int(11),in qj1 int(11),in xm varchar(250),in serach varchar(250),in cp varchar(250),in keyword varchar(250))
BEGIN
/*
功能说明：自动生成项目余额表
参数说明：qj0->起始期间
　　　　　qj1->终止期间
          xm->项目名称
          serach->查找字段
          cp->查找条件
          keyword->查找内容
*/

drop table if exists xmyrb;

create temporary table xmyrb(
id int(11) primary key auto_increment not null,
ywrq date not null,
lrrq date not null,
qj int(11) not null,
pzh int(11) not null,
xh tinyint(4) not null,
zy varchar(250) not null,
km varchar(250) not null,
kma varchar(250) not null,
xm varchar(250) not null,
xms varchar(250) not null,
dc varchar(250) not null,
slwb0 double default 0 not null,
dr0 double default 0 not null,
cr0 double default 0 not null,
ds1 double default 0 not null,
dr1 double default 0 not null,
cs1 double default 0 not null,
cr1 double default 0 not null);

set @col1=concat('v1.',xm);
set @col2=concat('v1.',xm,'F');

if serach='id' then
case xm
when 'xjll' then set @s='v1.xjll';
when 'kh' then set @s='v1.kh';
when 'gr' then set @s='v1.gr';
when 'ch' then set @s='v1.ch';
when 'gys' then set @s='v1.gys';
when 'bm' then set @s='v1.bm';
when 'xm' then set @s='v1.xm';
end case;
else
case xm
when 'xjll' then set @s='v1.xjllF';
when 'kh' then set @s='v1.khF';
when 'gr' then set @s='v1.grF';
when 'ch' then set @s='v1.chF';
when 'gys' then set @s='v1.gysF';
when 'bm' then set @s='v1.bmF';
when 'xm' then set @s='v1.xmF';
end case;
end if;

case cp
when 1 then set @sc=concat(@s," like '%",keyword,"%'");
when 2 then set @sc=concat(@s,"=",keyword);
when 3 then set @sc=concat(@s," like '",keyword,"%'");
when 4 then set @sc=concat(@s," like '%",keyword,"'");
end case;

if keyword='' then
set @strsql1=concat('insert into xmyrb(ywrq,lrrq,qj,pzh,xh,zy,km,kma,xm,xms,dc,slwb0,dr0,cr0,ds1,dr1,cs1,cr1) 
select v1.ywrq,v1.lrrq,v1.qj,v1.pzh,v1.xh,v1.zy,v1.km,account.Fname,',@col1,',',@col2,',account.dc,
if((account.dc="借方" and v1.dr=0) or (account.dc="贷方" and v1.cr=0),-1*v1.slwb,v1.slwb),v1.dr,v1.cr,0,0,0,0
from v1 left join account on v1.km=account.id where qj<',qj0,' and ',@col1,' is not null;');
set @strsql2=concat('insert into xmyrb(ywrq,lrrq,qj,pzh,xh,zy,km,kma,xm,xms,dc,slwb0,dr0,cr0,ds1,dr1,cs1,cr1) 
select v1.ywrq,v1.lrrq,v1.qj,v1.pzh,v1.xh,v1.zy,v1.km,account.Fname,',@col1,',',@col2,',account.dc,
0,0,0,if(v1.cr=0,v1.slwb,0),v1.dr,if(v1.dr=0,v1.slwb,0),v1.cr 
from v1 left join account on v1.km=account.id where ((qj between ',qj0,' and ',qj1,') and ',@col1,' is not null);');
else
set @strsql1=concat('insert into xmyrb(ywrq,lrrq,qj,pzh,xh,zy,km,kma,xm,xms,dc,slwb0,dr0,cr0,ds1,dr1,cs1,cr1) 
select v1.ywrq,v1.lrrq,v1.qj,v1.pzh,v1.xh,v1.zy,v1.km,account.Fname,',@col1,',',@col2,',account.dc,
if((account.dc="借方" and v1.dr=0) or (account.dc="贷方" and v1.cr=0),-1*v1.slwb,v1.slwb),v1.dr,v1.cr,0,0,0,0
from v1 left join account on v1.km=account.id where qj<',qj0,' and ',@sc);
set @strsql2=concat('insert into xmyrb(ywrq,lrrq,qj,pzh,xh,zy,km,kma,xm,xms,dc,slwb0,dr0,cr0,ds1,dr1,cs1,cr1) 
select v1.ywrq,v1.lrrq,v1.qj,v1.pzh,v1.xh,v1.zy,v1.km,account.Fname,',@col1,',',@col2,',account.dc,
0,0,0,if(v1.cr=0,v1.slwb,0),v1.dr,if(v1.dr=0,v1.slwb,0),v1.cr 
from v1 left join account on v1.km=account.id where (qj between ',qj0,' and ',qj1,') and ',@sc);
end if;

prepare stmt1 from @strsql1;
execute stmt1;
deallocate prepare stmt1;

prepare stmt2 from @strsql2;
execute stmt2;
deallocate prepare stmt2;

END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2016-07-16 10:37:10
