存储过程统计数据

存储过程统计数据

 -- 遍历数据结束标志

    DECLARE done INT DEFAULT FALSE;

    -- 游标 统计输入参数日期的统计记录

    DECLARE cur_account CURSOR FOR

        select corpId,corpName  from as_corp;

    -- 将结束标志绑定到游标
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- 打开游标
    OPEN  cur_account;

    -- 遍历
    read_loop: LOOP

        -- 取值 取多个字段
        FETCH  NEXT from cur_account INTO corpId_temp,corpName_temp;

        IF done THEN
            LEAVE read_loop;
        END IF;

        SELECT rptTime,corpId INTO rptTime_temp,corpId_rtemp FROM rpt_report_board where corpId=corpId_temp;

        -- 统计在线设备数
        select COUNT(*) INTO onlineNum_temp FROM as_vending where corpId=corpId_temp AND netSate=0;

        -- 统计小于24小时的离线设备数
        select COUNT(*) INTO offlineNum_temp FROM as_vending where corpId=corpId_temp AND netSate=1 AND netTime>date_sub(now(), interval 24 hour);

        -- 统计大于24小时的离线设备数
        select COUNT(*) INTO offlineDayNum_temp FROM as_vending where corpId=corpId_temp AND netSate=1 AND netTime<=date_sub(now(), interval 24 hour);

        -- 判断是否存在商户记录
        CASE WHEN ISNULL(corpId_rtemp) THEN

            -- 统计总销售数量,总销售额,总利润
            select sum(outNum) as saleNum,(sum(payPrice)-sum(returnMoney)) as saleMoney,(sum(profitMoney)-sum(returnMoney)) as profit,SUM(buyPrice) INTO saleNum_temp,saleMoney_temp,profit_temp,productCost_temp from rpt_order_base where returnType!='2' and payState='2' and  corpId=corpId_temp GROUP BY corpId;

            IF saleNum_temp is NULL THEN
                set saleNum_temp=0;
            END IF;

            IF saleMoney_temp is NULL THEN
                set saleMoney_temp=0;
            END IF;

            IF profit_temp is NULL THEN
                set profit_temp=0;
            END IF;

            IF productCost_temp is NULL THEN
                set productCost_temp=0;
            END IF;

            -- 入库新的记录
            INSERT INTO rpt_report_board(logid,corpId,onlineNum,offlineNum,offlineDayNum,saleNum,saleMoney,profit,vendingCost,productCost,rptTime,createTime,stateTime)
        VALUES(UUID(),corpId_temp,onlineNum_temp,offlineNum_temp,offlineDayNum_temp,saleNum_temp,saleMoney_temp,profit_temp,vendingCost_temp,productCost_temp,UNIX_TIMESTAMP(now()),now(),now());
            ELSE

                -- 获取最大时间值
                SELECT MAX(rptTime) INTO rptMaxTime_temp FROM rpt_order_base where corpId=corpId_temp;

                -- 统计总销售数量,总销售额,总利润
                select sum(outNum) as saleNum,(sum(payPrice)-sum(returnMoney)) as saleMoney,sum(profitMoney) as profit,SUM(buyPrice) INTO saleNum_temp,saleMoney_temp,profit_temp,productCost_temp from rpt_order_base where returnType!='2' and payState='2' and corpId=corpId_temp AND rptTime>rptTime_temp AND rptTime<=rptMaxTime_temp GROUP BY corpId;

                IF saleNum_temp is NULL THEN
                    set saleNum_temp=0;
                END IF;

                IF saleMoney_temp is NULL THEN
                    set saleMoney_temp=0;
                END IF;

                IF profit_temp is NULL THEN
                    set profit_temp=0;
                END IF;

                IF productCost_temp is NULL THEN
                    set productCost_temp=0;
                END IF;

                -- 没有统计的不追加
                CASE WHEN rptMaxTime_temp!=rptTime_temp THEN
                    -- 更新记录
                    UPDATE rpt_report_board SET onlineNum=onlineNum_temp,offlineNum=offlineNum_temp,offlineDayNum=offlineDayNum_temp,saleNum=saleNum+saleNum_temp,saleMoney=saleMoney+saleMoney_temp,profit=profit+profit_temp,productCost=productCost+productCost_temp,rptTime=rptMaxTime_temp,stateTime=now() where corpId=corpId_temp;
                    ELSE
                        -- 不更新商品统计数据记录
                        UPDATE rpt_report_board SET onlineNum=onlineNum_temp,offlineNum=offlineNum_temp,offlineDayNum=offlineDayNum_temp,stateTime=now() where corpId=corpId_temp;
                    END CASE;
            END CASE;

        SET rptMaxTime_temp=NULL;
        SET corpId_rtemp=NULL;
        -- 设置无下条记录状态
        SET done = FALSE;
    END LOOP;
    CLOSE cur_account;