CREATE DEFINER=`myl`@`192.168.2.%`PROCEDURE`pr_investStatistics`(IN`adminId` bigint)
BEGINdeclare totalInvestment varchar(255) default'0';
declare dueinfund varchar(255) default '0';
declare exceedInvest varchar(255) default '0';
selectsum(investAmount) INTO @ztouz from t_invest;select f_formatAmount(IFNULL(@ztouz ,0)) into totalInvestment;selectsum((recivedPrincipal +recievedInterest - hasPrincipal-hasInterest)) into @duein from t_invest;select f_formatAmount(IFNULL(@duein ,0)) into dueinfund;selectsum(stillPrincipal+stillInterest - hasFI) into @excee from t_repayment where isLate=2and repayStatus<>2;select f_formatAmount(IFNULL(@excee ,0)) into exceedInvest;select totalInvestment ,dueinfund ,exceedInvest;END
CREATE DEFINER=`mypol`@`192.163.2.%` PROCEDURE `pr_getBackAcountStatis`(IN `startTime` varchar(50),IN `endTime` varchar(50),IN `title` varchar(1024),IN `in_uid` bigint)
BEGIN
declare borrowId BIGINT default -1;
declare borrowTime varchar(30) default'';declare allForPI DECIMAL(18,2) default0;
declare forpayPI DECIMAL(18,2) default0;
declare allForPIYear DECIMAL(18,2) default0;
declare forpayPIYear DECIMAL(18,2) default0;
declare allForPISixMonth DECIMAL(18,2) default0;
declare forpayPISixMonth DECIMAL(18,2) default0;
declare allForPIThreeMonth DECIMAL(18,2) default0;
declare forpayPIThreeMonth DECIMAL(18,2) default0;
declare allForPIOneMonth DECIMAL(18,2) default0;
declare forpayPIOneMonth DECIMAL(18,2) default0;
declare sqlStr varchar(1024) default'';declare counts int default0;
DECLARE _done INT DEFAULT0;
DECLARE cursor_borrrow CURSOR
FORselect b.id,b.auditTime
from t_invest a
left join t_borrow b on a.borrowId = b.id
where a.investor = in_uid and b.borrowStatus = 4groupby b.id;
DECLARECONTINUE HANDLER FOR SQLSTATE '02000' SET _done = 1;if( startTime = '') THENset sqlStr = concat(sqlStr,"");
elseset sqlStr = concat(sqlStr," and c.publishTime >='",startTime,"'");
endif;
if(endTime = '') THENset sqlStr = concat(sqlStr,"");
elseset sqlStr = concat(sqlStr," and c.publishTime <='",endTime,"'");
endif;
if(title = '') THENset sqlStr = concat(sqlStr,"");
elseset sqlStr = concat(sqlStr," and c.borrowTitle like '%",title,"%'");
endif;
OPEN cursor_borrrow;
FETCH cursor_borrrow INTO borrowId,borrowTime;
while _done <> 1doset counts = counts +1;
set borrowId = borrowId;
set borrowTime = concat('',borrowTime);set @forpayPI = 0;
SET @allForPI_sql=CONCAT("SELECT SUM(a.recivedInterest-a.hasInterest) forpayAmount into @forpayPI FROM t_invest_repayment a");
SET @allForPI_sql=CONCAT(@allForPI_sql," left join t_invest b on a.invest_id = b.id LEFT JOIN t_borrow c ON b.borrowId=c.id WHERE (a.recivedInterest-a.hasInterest) > 0 and b.borrowId=",borrowId);
SET @allForPI_sql=CONCAT(@allForPI_sql," and b.investor = ",in_uid,sqlStr);
PREPARE allForPISql FROM @allForPI_sql;
EXECUTE allForPISql;
DEALLOCATE PREPARE allForPISql;
set forpayPI = IFNULL(@forpayPI,0);
set allForPI = IFNULL(allForPI,0) + IFNULL(forpayPI,0);
set @forpayPIYear = 0;
SET @allForPIYear_sql=CONCAT("SELECT SUM(a.recivedInterest-a.hasInterest) forpayAmount into @forpayPIYear FROM t_invest_repayment a");
SET @allForPIYear_sql=CONCAT(@allForPIYear_sql," left join t_invest b on a.invest_id = b.id LEFT JOIN t_borrow c ON b.borrowId=c.id WHERE (a.recivedInterest-a.hasInterest) > 0 and b.borrowId=",borrowId);
SET @allForPIYear_sql=CONCAT(@allForPIYear_sql," AND a.repayDate >= '",borrowTime,"' AND a.repayDate <= DATE_ADD('",borrowTime,"',INTERVAL 1 YEAR)");
SET @allForPIYear_sql=CONCAT(@allForPIYear_sql," and b.investor = ",in_uid,sqlStr);
PREPARE allForPIYearSql FROM @allForPIYear_sql;
EXECUTE allForPIYearSql;
DEALLOCATE PREPARE allForPIYearSql;
set forpayPIYear = IFNULL(@forpayPIYear,0);
set allForPIYear = IFNULL(allForPIYear,0) + IFNULL(forpayPIYear,0);
set @forpayPISixMonth = 0;
set @allFORPISixMonth_sql =CONCAT("SELECT SUM(a.recivedInterest-a.hasInterest) forpayAmount into @forpayPISixMonth FROM t_invest_repayment a");
set @allFORPISixMonth_sql = CONCAT(@allFORPISixMonth_sql," left join t_invest b on a.invest_id = b.id LEFT JOIN t_borrow c ON b.borrowId=c.id WHERE (a.recivedInterest-a.hasInterest) > 0 and b.borrowId=",borrowId);
set @allFORPISixMonth_sql = CONCAT(@allFORPISixMonth_sql," AND a.repayDate >= '",borrowTime,"' AND a.repayDate <= DATE_ADD('",borrowTime,"',INTERVAL 6 MONTH)");
set @allFORPISixMonth_sql = CONCAT(@allFORPISixMonth_sql," and b.investor = ",in_uid,sqlStr);
PREPARE allForPISixMonthSql FROM @allFORPISixMonth_sql;
EXECUTE allForPISixMonthSql;
DEALLOCATE PREPARE allForPISixMonthSql;
set forpayPISixMonth = IFNULL(@forpayPISixMonth,0);
set allForPISixMonth = IFNULL(allForPISixMonth,0) + IFNULL(forpayPISixMonth,0);
set @forpayPIThreeMonth = 0;
SET @allForPIThreeMonth_sql=CONCAT("SELECT SUM(a.recivedInterest-a.hasInterest) forpayAmount into @forpayPIThreeMonth FROM t_invest_repayment a");
SET @allForPIThreeMonth_sql=CONCAT(@allForPIThreeMonth_sql," left join t_invest b on a.invest_id = b.id LEFT JOIN t_borrow c ON b.borrowId=c.id WHERE (a.recivedInterest-a.hasInterest) > 0 and b.borrowId=",borrowId);
SET @allForPIThreeMonth_sql=CONCAT(@allForPIThreeMonth_sql," AND a.repayDate >= '",borrowTime,"' AND a.repayDate <= DATE_ADD('",borrowTime,"',INTERVAL 3 MONTH)");
SET @allForPIThreeMonth_sql=CONCAT(@allForPIThreeMonth_sql," and b.investor = ",in_uid,sqlStr);
PREPARE allForPIThreeMonthSql FROM @allForPIThreeMonth_sql;
EXECUTE allForPIThreeMonthSql;
DEALLOCATE PREPARE allForPIThreeMonthSql;
set forpayPIThreeMonth = IFNULL(@forpayPIThreeMonth,0);
set allForPIThreeMonth = IFNULL(allForPIThreeMonth,0) + IFNULL(forpayPIThreeMonth,0);
set @forpayPIOneMonth = 0;
SET @allForPIOneMonth_sql=CONCAT("SELECT SUM(a.recivedInterest-a.hasInterest) forpayAmount into @forpayPIOneMonth FROM t_invest_repayment a");
SET @allForPIOneMonth_sql=CONCAT(@allForPIOneMonth_sql," left join t_invest b on a.invest_id = b.id LEFT JOIN t_borrow c ON b.borrowId=c.id WHERE (a.recivedInterest-a.hasInterest) > 0 and b.borrowId=",borrowId);
SET @allForPIOneMonth_sql=CONCAT(@allForPIOneMonth_sql," AND a.repayDate >= '",borrowTime,"' AND a.repayDate <= DATE_ADD('",borrowTime,"',INTERVAL 1 MONTH)");
SET @allForPIOneMonth_sql=CONCAT(@allForPIOneMonth_sql," and b.investor = ",in_uid,sqlStr);
PREPARE allForPIOneMonthSql FROM @allForPIOneMonth_sql;
EXECUTE allForPIOneMonthSql;
DEALLOCATE PREPARE allForPIOneMonthSql;
set forpayPIOneMonth = IFNULL(@forpayPIOneMonth,0);
set allForPIOneMonth = IFNULL(allForPIOneMonth,0) + IFNULL(forpayPIOneMonth,0);
fetch cursor_borrrow INTO borrowId,borrowTime;
endwhile;
CLOSE cursor_borrrow;
select allForPIOneMonth,allForPIThreeMonth,allForPIYear,allForPI, allForPISixMonth;
END
CREATE DEFINER=`mygpol`@`1.168.2.%`PROCEDURE`pr_getFinanceStatis`(IN`uId` bigint)
BEGINdeclare earnInterest DECIMAL(18,2) default0;
declare rewardIncome DECIMAL(18,2) default 0;
declare hasFI DECIMAL(18,2) DEFAULT 0;
declare investAmount DECIMAL(18,2) default 0;
declare investCount int default 0;
declare hasPI DECIMAL(18,2) default 0;
declare hasCount int default 0;
declare forPI DECIMAL(18,2) default 0;
declare forCount int default 0;
set @investCount = 0;set @investAmount = 0;set @forPI = 0;set @forCount = 0;set @hasPI = 0;set @hasCount = 0;set @hasFI = 0;set @earnInterest = 0;set @rewardIncome = 0;SET @invest_sql=CONCAT("SELECT COUNT(*) counts,SUM(a.realAmount) realAmount into @investCount,@investAmount FROM t_invest a LEFT JOIN t_borrow b ON ");SET @invest_sql=CONCAT(@invest_sql," a.borrowId=b.id WHERE b.borrowStatus in(4,5) AND a.investor =",uId," GROUP BY a.investor ");
PREPARE investSql FROM @invest_sql;
EXECUTE investSql;
DEALLOCATE PREPARE investSql;
set investCount = @investCount;set investAmount = @investAmount;SET @forPI_sql=CONCAT("SELECT SUM(a.recivedPrincipal+a.recievedInterest-a.hasPrincipal-a.hasInterest) forPI INTO @forPI FROM t_invest a");SET @forPI_sql=CONCAT(@forPI_sql," WHERE a.investor =",uId," GROUP BY a.investor ");
PREPARE forPISql FROM @forPI_sql;
EXECUTE forPISql;
DEALLOCATE PREPARE forPISql;
set forPI = @forPI;SET @forCount_sql=CONCAT("SELECT SUM(c.counts) counts INTO @forCount FROM( SELECT a.investor, COUNT(a.id) counts FROM t_invest a WHERE (a.recivedPrincipal+a.recievedInterest-a.hasPrincipal-a.hasInterest) >0");SET @forCount_sql=CONCAT(@forCount_sql," GROUP BY a.investor) c where c.investor =",uId," GROUP BY c.investor");
PREPARE forCountSql FROM @forCount_sql;
EXECUTE forCountSql;
DEALLOCATE PREPARE forCountSql;
set forCount = @forCount;SET @hasPI_sql=CONCAT("SELECT SUM(c.hasPI) hasPI INTO @hasPI FROM( SELECT a.investor, SUM(a.hasPrincipal+a.hasInterest) hasPI FROM t_invest a GROUP BY a.investor");SET @hasPI_sql=CONCAT(@hasPI_sql," UNION ALL SELECT a.investor,SUM(a.hasPrincipal+a.hasInterest) hasPI FROM t_invest_history a GROUP BY a.investor) c ");SET @hasPI_sql=CONCAT(@hasPI_sql,"WHERE c.investor =",uId," GROUP BY c.investor");
PREPARE hasPISql FROM @hasPI_sql;
EXECUTE hasPISql;
DEALLOCATE PREPARE hasPISql;
set hasPI = @hasPI;SET @hasCount_sql=CONCAT("SELECT SUM(c.counts) counts INTO @hasCount FROM( SELECT a.investor, COUNT(a.id) counts FROM t_invest a WHERE a.repayStatus = 2 GROUP BY a.investor");SET @hasCount_sql=CONCAT(@hasCount_sql," UNION ALL SELECT a.investor,COUNT(a.id) counts FROM t_invest_history a GROUP BY a.investor) c where c.investor =",uId," GROUP BY c.investor");
PREPARE hasCountSql FROM @hasCount_sql;
EXECUTE hasCountSql;
DEALLOCATE PREPARE hasCountSql;
set hasCount = @hasCount;SET @hasFI_sql=CONCAT("SELECT SUM(a.hasFI) hasFI into @hasFI FROM t_invest a WHERE a.investor =",uId," GROUP BY a.investor");
PREPARE hasFISql FROM @hasFI_sql;
EXECUTE hasFISql;
DEALLOCATE PREPARE hasFISql;
set hasFI = @hasFI;SET @earnInterest_sql=CONCAT("SELECT SUM(c.hasInterest) hasPrincipal INTO @earnInterest FROM( SELECT a.investor, SUM(a.hasInterest) hasInterest FROM t_invest a GROUP BY a.investor");SET @earnInterest_sql=CONCAT(@earnInterest_sql," UNION ALL SELECT a.investor,SUM(a.hasInterest) hasInterest FROM t_invest_history a GROUP BY a.investor) c ");SET @earnInterest_sql=CONCAT(@earnInterest_sql,"WHERE c.investor =",uId," GROUP BY c.investor");
PREPARE earnInterestSql FROM @earnInterest_sql;
EXECUTE earnInterestSql;
DEALLOCATE PREPARE earnInterestSql;
set earnInterest = @earnInterest;SET @rewardIncome_sql=CONCAT("SELECT SUM(c.reward) reward INTO @rewardIncome FROM( SELECT a.investor, SUM(a.reward) reward FROM t_invest a GROUP BY a.investor");SET @rewardIncome_sql=CONCAT(@rewardIncome_sql," UNION ALL SELECT a.investor,SUM(a.reward) reward FROM t_invest_history a GROUP BY a.investor) c ");SET @rewardIncome_sql=CONCAT(@rewardIncome_sql,"WHERE c.investor =",uId," GROUP BY c.investor");
PREPARE rewardIncomeSql FROM @rewardIncome_sql;
EXECUTE rewardIncomeSql;
DEALLOCATE PREPARE rewardIncomeSql;
set rewardIncome = @rewardIncome;select earnInterest as earnInterest,rewardIncome as rewardIncome,hasFI as hasFI,investAmount as investAmount,
investCount as investCount,hasPI as hasPI,hasCount as hasCount,forPI as forPI,forCount as forCount;END
CREATE DEFINER=`myl`@`192.168.2.%`PROCEDURE`pr_getUserInfo`(IN`user` bigint,IN`timeStart`varchar(50),IN`timeEnd`varchar(50))
BEGINdeclare createTime datedefaultnull;
declare vipCreateTime datetime default null;
declare lastDate datetime default null;
declare borrowNum varchar(50) default null;
declare investNum varchar(50) default null;
declare unReadCount varchar(50) default null;
declare a varchar(50) default null;
declare b varchar(50) default null;
declare c varchar(50) default null;
declare d varchar(50) default null;
declare e varchar(50) default null;
declare f varchar(50) default null;
declare g varchar(50) default null;
declare h varchar(50) default null;
declare i varchar(50) default null;
declare j varchar(50) default null;
declare k varchar(50) default null;
set @createTime = '';set @vipCreateTime = '';set @lastDate = '';SET @user_sql='SELECT a.username,a.createTime,a.rating as rating,f_rating(a.rating) as vip,f_credit_rating(a.creditrating) AS credit,';SET @user_sql=CONCAT(@user_sql,'a.creditrating,a.creditLimit,a.lastIP,DATE_ADD(a.vipCreateTime, INTERVAL 1 YEAR) AS vipCreateTime,');SET @user_sql=CONCAT(@user_sql,'a.vipStatus,b.personalHead,a.lastDate into @username,@createTime,@rating,@vip,@credit,@crediting,@creditLimit,');set @user_sql=concat(@user_sql,'@lastIP,@vipCreateTime,@vipStatus,@personalHead,@lastDate');SET @user_sql=CONCAT(@user_sql,' FROM t_user a LEFT JOIN t_person b ON a.id = b.userId where a.id=',user);
PREPARE userSql FROM @user_sql;
EXECUTE userSql;
DEALLOCATE PREPARE userSql;
set createTime = @createTime;set vipCreateTime = @vipCreateTime;set lastDate = @lastDate;set @borrowNum = 0;SET @borrow_sql=CONCAT('SELECT COUNT(*) counts into @borrowNum FROM t_borrow a WHERE a.publisher = ',user,' GROUP BY a.publisher');
PREPARE borrowSql FROM @borrow_sql;
EXECUTE borrowSql;
DEALLOCATE PREPARE borrowSql;
set borrowNum = @borrowNum;set @investNum = 0;SET @invest_sql=CONCAT('SELECT COUNT(*) counts into @investNum FROM t_invest a WHERE a.investor = ',user,' GROUP BY a.investor');
PREPARE investSql FROM @invest_sql;
EXECUTE investSql;
DEALLOCATE PREPARE investSql;
set investNum = @investNum;set @unReadCount = 0;SET @mail_sql=CONCAT('select count(*) counts into @unReadCount from t_mail a where a.mailStatus = 1 and a.reciver = ',user,' group by a.reciver');
PREPARE mailSql FROM @mail_sql;
EXECUTE mailSql;
DEALLOCATE PREPARE mailSql;
set unReadCount = @unReadCount;set @waitBorrowCount = 0;SET @waitborrow_sql=CONCAT('select count(*) counts into @waitBorrowCount from t_borrow a where a.borrowStatus = 1 and a.publisher = ',user,' group by a.publisher');
PREPARE waitborrowSql FROM @waitborrow_sql;
EXECUTE waitborrowSql;
DEALLOCATE PREPARE waitborrowSql;
set @repayCount = 0;SET @repay_sql=CONCAT(" select sum(t.counts) counts into @repayCount from (select count(*) counts,b.publisher from t_repayment a left join t_borrow b on a.borrowId=b.id where b.borrowStatus = 4");SET @repay_sql=CONCAT(@repay_sql," and b.publisher = ",user," and a.repayDate>='",timeStart,"'and a.repayDate <='",timeEnd,"' and a.repayStatus = 1 group by b.publisher) t group by t.publisher");
PREPARE repaySql FROM @repay_sql;
EXECUTE repaySql;
DEALLOCATE PREPARE repaySql;
set @forpayCount = 0;SET @forpay_sql=CONCAT(" select sum(t.counts) counts into @forpayCount from (select count(*) counts,a.investor FROM t_invest a LEFT JOIN t_repayment b ON a.borrowId = b.borrowId LEFT JOIN t_borrow c ON b.borrowId = c.id");SET @forpay_sql=CONCAT(@forpay_sql," LEFT JOIN t_user d ON a.investor = d.id WHERE b.repayStatus = 1 and a.investor =",user);SET @forpay_sql=CONCAT(@forpay_sql," and b.repayDate>='",timeStart,"'and b.repayDate <='",timeEnd,"' group by a.investor) t group by t.investor");
PREPARE forpaySql FROM @forpay_sql;
EXECUTE forpaySql;
DEALLOCATE PREPARE forpaySql;
set @lateRepayCount = 0;SET @laterepay_sql=CONCAT(" select sum(t.counts) counts into @lateRepayCount from (select count(*) counts,b.publisher from t_repayment a left join t_borrow b on a.borrowId=b.id where b.borrowStatus = 4");SET @laterepay_sql=CONCAT(@laterepay_sql," and b.publisher = ",user," and a.repayDate>='",timeStart,"'and a.repayDate <='",timeEnd,"' and a.repayStatus = 1");set @laterepay_sql=CONCAT(@laterepay_sql," and a.isLate = 2 group by a.borrowId) t group by t.publisher");
PREPARE laterepaySql FROM @laterepay_sql;
EXECUTE laterepaySql;
DEALLOCATE PREPARE laterepaySql;
set @materis = 0;SET @maters_sql=CONCAT(" select count(*) counts into @materis from t_materialsauth where imgPath is not null and userId =",user," group by userId;");
PREPARE matersSql FROM @maters_sql;
EXECUTE matersSql;
DEALLOCATE PREPARE matersSql;
set a = @rating;set b = @vip;set c = @credit;set d = @crediting;set e = @creditLimit;set f = @vipStatus;set g = @waitBorrowCount;set h = @repayCount;set i = @forpayCount;set j = @lateRepayCount;set k = @materis;select @username as username,DATE_FORMAT(createTime,'%Y-%m-%d') as createTime,a as rating,b as vip,c as credit,d as crediting,e as creditLimit,@lastIP as lastIP,
DATE_FORMAT(vipCreateTime,'%Y-%m-%d %T')AS vipCreateTime,f as vipStatus,@personalHead as personalHead,DATE_FORMAT(lastDate,'%Y-%m-%d %T') as lastDate,borrowNum as borrowNum,investNum as investNum,
unReadCount as unReadCount,g as waitBorrowCount,h as repayCount,i as forpayCount,j as lateRepayCount,k as materis;END