PROCEDURE P_DailyReport

            (v_StatisticsDate IN varchar2, v_BranchFlag IN VARCHAR2,cur_name OUT t_cursor)

IS

   i_year  INTEGER :=0;--年份

   i_month INTEGER  :=0;--月份

   i_day   INTEGER  :=0;--天数

   i_monthdays INTEGER :=0;--本月天数

   i_alldays   INTEGER  :=0;--全年天数

   d_firstday DATE;--本月第一天

   d_yearfirstday DATE;--本年第一天

   i_pastdays INTEGER  :=0;--当年已过天数

   d_lastyearfirstday DATE;--去年第一天

   d_lasttoday DATE;--去年今天

   d_StatisticsDate DATE;

BEGIN

    i_year  :=to_number(substr(v_statisticsdate,1,4));

    i_month :=to_number(substr(v_statisticsdate,6,2));

    i_day   :=to_number(substr(v_statisticsdate,9,2));

    d_StatisticsDate:=to_date(v_StatisticsDate,'yyyy-mm-dd');

    --本月第一天，总天数

    SELECT MIN(thedate),MAX(theday)

      INTO d_firstday,i_monthdays

      FROM dim_date

     WHERE theyear=i_year AND themonth=i_month;

     --全年天数

     SELECT COUNT(1),MIN(thedate)

       INTO i_alldays,d_yearfirstday

       FROM dim_date

      WHERE theyear=i_year;

     --当年已过天数

     SELECT COUNT(1)

       INTO i_pastdays

       FROM dim_date

      WHERE theyear=i_year AND thedate<=d_StatisticsDate;

     --去年第一天

     SELECT MIN(thedate)

       INTO d_lastyearfirstday

       FROM dim_date

      WHERE theyear=i_year-1;

     --去年今天

     SELECT thedate

       INTO d_lasttoday

       FROM dim_date

      WHERE theyear=i_year-1 AND themonth=i_month AND theday=i_day;

OPEN cur_name FOR

     SELECT  c.branchabbr,d.TARGET,

             round(d.TARGET/i_alldays*i_monthdays,2) monthtarget, --月指标

             round(SUM(e.amount)/100000000,4) xshje,

             round(SUM(e.amount)/(d.TARGET/i_alldays*i_day*100000000),4),--当月进度

             round(SUM(f.amount)/100000000,4) ndxshje,

             round(SUM(f.amount)/(d.TARGET/i_alldays*i_pastdays*100000000),4),--年同期进度

             round(d.TARGET/i_alldays*i_monthdays-SUM(e.amount)/100000000,4),  --月度差额

             round(SUM(g.amount)/100000000,4),

             round((SUM(f.amount)- SUM(g.amount))/ SUM(g.amount),4)

       FROM ( SELECT a.branchabbr,a.branchflag,b.branchflag branchflag3

                FROM(SELECT branchabbr,branchflag

                       FROM t_jzt_common_branch

                      WHERE isactive=1

                        AND branchlevel=2 AND branchflag<>'ZDA' AND branchflag<>'FDY'

                     ) a

                INNER JOIN(SELECT branchflag,higherbranchflag

                             FROM t_jzt_common_branch

                            WHERE isactive=1 AND branchflag<>'ZDA'

                           )b ON a.branchflag=b.higherbranchflag

            ) c

       LEFT JOIN  t_report_TARGET d ON d.statisticsdate=i_year

                                    AND d.branchflag=c.branchflag

                                    AND d.TARGETType='销售指标'

       LEFT JOIN (SELECT branchflag,sum(amount) amount--月度

                    FROM t_bi_dailysalereport

                   WHERE orderdate >=d_firstday AND orderdate<=d_StatisticsDate

                   GROUP BY branchflag

                 ) e ON e.branchflag=c.branchflag3

       LEFT JOIN (SELECT branchflag,sum(amount) amount--年度

                    FROM t_bi_dailysalereport

                   WHERE orderdate>=d_yearfirstday AND orderdate<=d_StatisticsDate

                   GROUP BY branchflag

                 ) f ON f.branchflag=c.branchflag3

       LEFT JOIN (SELECT branchflag,sum(amount) amount --去年

                    FROM t_bi_dailysalereport

                   WHERE orderdate >=d_lastyearfirstday AND orderdate<=d_lasttoday

                   GROUP BY branchflag

                 )g ON g.branchflag=c.branchflag3

       GROUP BY c.branchabbr,d.TARGET

       ORDER BY c.branchabbr,d.TARGET;

END P_DailyReport;