-- MySQL dump 10.13  Distrib 5.7.9, for Win32 (AMD64)
--
-- Host: localhost    Database: 新建账套
-- ------------------------------------------------------
-- 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 */;
/*!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 */;
/*!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 */;
/*!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 */;
/*!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 */;
/*!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');
/*!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 */;
/*!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 */;
/*!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

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

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

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


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-23 23:29:42
