<cfinclude template="header.cfm">
     <link href="../plugins/bower_components/datatables/jquery.dataTables.min.css" rel="stylesheet" type="text/css" />

<link href="https://cdn.datatables.net/buttons/1.2.2/css/buttons.dataTables.min.css" rel="stylesheet" type="text/css" />

  <div id="page-wrapper">
    <div class="container-fluid">
      <div class="row bg-title">
        <div class="col-lg-3 col-md-4 col-sm-4 col-xs-12">
          <h4 class="page-title">Community News Stats by Region</h4>
        </div>
        <div class="col-lg-9 col-sm-8 col-md-8 col-xs-12">
          <ol class="breadcrumb">
            <li><a href="#">Dashboard</a></li>
            <li><a href="#">Calendar</a></li> <li class="active">Today</li>
          </ol>
        </div>
        <!-- /.col-lg-12 -->
      </div>
      <!-- /row -->
      <div class="row">
                <div class="col-sm-12">
          <div class="white-box">


<!---            <p class="text-muted m-b-30">Export data to Copy, CSV, Excel, PDF & Print</p>
    --->         <div class="table-responsive">

	            <link href="../plugins/bower_components/bootstrap-datepicker/bootstrap-datepicker.min.css" rel="stylesheet" type="text/css" />



<Cfset newdatetime = #dateformat(currentdatetime,'yyyy-mm-dd')#>
<Cfset newdatetime = #dateadd('d',-7,today)#>

<Cfset newdatetime = #dateformat(newdatetime,'yyyy-mm-dd')#>

<cfset today = #dateformat(currentdatetime,'yyyy-mm-dd')#>
<Cfset tomorrow = #dateadd('d',1,today)#>
<cfset tomorrow = #dateformat(tomorrow,'yyyy-mm-dd')#>

<form action="data-tablearticlescatewsregion.cfm<cfif parameterexists(url.id)>?id=<Cfoutput>#url.id#</Cfoutput></cfif>" method="post" name="calme"  enctype="multipart/form-data">

<Table><tr><td>
From: 	<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js" ></script>

<link rel="stylesheet" href="//code.jquery.com/ui/1.11.4/themes/smoothness/jquery-ui.css">

  <script src="//code.jquery.com/ui/1.11.4/jquery-ui.js"></script>

 <script>
  $(function() {
    $( "#datepicker" ).datepicker();
  });
  $(function() {
    $( "#datepicker2" ).datepicker();
  });
  </script>
<input id="datepicker" type="text"  name="dater" label="Block out starts" width="100"  message="You must enter start/end dates ." id="input-fname" style="width:115px;" data-date-format="yyyy-mm-dd" <Cfif parameterexists(form.dater)>value="<cfoutput>#dateformat(dater,'yyyy-mm-dd')#</cfoutput>" <cfelse> value="<cfoutput>#dateformat(newdatetime,'yyyy-mm-dd')#</cfoutput>"</Cfif>>
</Td><td>
To:
	<input id="datepicker2"   type="text"  name="daterend" label="Block out end date" width="100"  message="You must enter start/end dates " id="input-fname" style="width:115px;" data-date-format="yyyy-mm-dd" <Cfif parameterexists(form.daterend)>value="<cfoutput>#dateformat(daterend,'yyyy-mm-dd')#</cfoutput>" <cfelse> value="<cfoutput>#dateformat(tomorrow,'yyyy-mm-dd')#</cfoutput>"</Cfif> >
 </td>
 <Td style="padding-top: 14px;">
<button class="btn btn-success btn-mini">SEARCH <i class="icon-save"></i></button>
 </Td></tr></table>
</form>

                <table id="example23" class="display nowrap" cellspacing="0" width="100%">
                    <thead>
                        <tr>
                            <th>Region</th>
<th>Count</th>
                            <th>Views</th>
                        </tr>
                    </thead>
                    <tfoot>
                        <tr>
                            <th>Region</th>
<th>Count</th>
                            <th>Views</th>
                            
                        </tr>
                    </tfoot>
                    <tbody>

<Cfset tot1 = 0>
<Cfset tot2 = 0>

<cfset today = #dateformat(currentdatetime,'yyyy-mm-dd')#>


<cfset form.region = 'Manhattan'>
<Cfquery name="Geter" dataSource="#ds#">
select columntable.title, columntable.id,columntable.dateposted as dtentered, regions.name,  count(newmastertrack.id) as views,region,columntable.seo_friendly,columnmaster.authorname 
from newmastertrack inner join columntable on newmastertrack.idnumber = columntable.id inner join regionstable on regionstable.colid = columntable.ID inner join columnmaster on columntable.columnnum = columnmaster.id
inner join regions on regionstable.regionsid = regions.id
where 1=1
<!---
newmastertrack.mag = 5--->
and newmastertrack.typer = 'article'
<cfif parameterexists(form.region) and #form.region# is not ''>
	and region like '%#form.region#%'
</cfif>	
and columntable.columnnum in (2351,2352,2353,2354,2355,2356,2357)
<cfif parameterexists(form.dater)>
and (columntable.dateposted BETWEEN '#dater#' AND '#daterend#')
<cfelse>
and columntable.dateposted > '#newdatetime#'
</cfif>
<cfinclude template="blockedip.cfm">

group by newmastertrack.idnumber
order by views desc

</cfquery>

<Cfquery name="c2" dbtype="query">
	select sum(views) as sumviews
	from geter
</cfquery>	 

<Cfoutput query="geter" maxRows="1">                        <tr>
                            <td>#form.region#</td>
							<td>#geter.recordcount#<cfset tot1=tot1+#geter.recordcount#></td> 
                            <td>#round(c2.sumviews)#<cfset tot2=tot2+#c2.sumviews#></td> 
                            



                        </tr>

</Cfoutput>       


<cfset form.region = 'Westchester'>
<Cfquery name="Geter" dataSource="#ds#">
select columntable.title, columntable.id,columntable.dateposted as dtentered, regions.name,  count(newmastertrack.id) as views,region,columntable.seo_friendly,columnmaster.authorname 
from newmastertrack inner join columntable on newmastertrack.idnumber = columntable.id inner join regionstable on regionstable.colid = columntable.ID inner join columnmaster on columntable.columnnum = columnmaster.id
inner join regions on regionstable.regionsid = regions.id
where 1=1
<!---
newmastertrack.mag = 5--->
and newmastertrack.typer = 'article'
<cfif parameterexists(form.region) and #form.region# is not ''>
	and region like '%#form.region#%'
</cfif>	
and columntable.columnnum in (2351,2352,2353,2354,2355,2356,2357)
<cfif parameterexists(form.dater)>
and (columntable.dateposted BETWEEN '#dater#' AND '#daterend#')
<cfelse>
and columntable.dateposted > '#newdatetime#'
</cfif>
<cfinclude template="blockedip.cfm">

group by newmastertrack.idnumber
order by views desc

</cfquery>

<Cfquery name="c2" dbtype="query">
	select sum(views) as sumviews
	from geter
</cfquery>	 

<Cfoutput query="geter" maxRows="1">                        <tr>
                            <td>#form.region#</td>
							<td>#geter.recordcount#<cfset tot1=tot1+#geter.recordcount#></td> 
                            <td>#round(c2.sumviews)#<cfset tot2=tot2+#c2.sumviews#></td> 
                            



                        </tr>

</Cfoutput>  


<cfset form.region = 'Rockland'>
<Cfquery name="Geter" dataSource="#ds#">
select columntable.title, columntable.id,columntable.dateposted as dtentered, regions.name,  count(newmastertrack.id) as views,region,columntable.seo_friendly,columnmaster.authorname 
from newmastertrack inner join columntable on newmastertrack.idnumber = columntable.id inner join regionstable on regionstable.colid = columntable.ID inner join columnmaster on columntable.columnnum = columnmaster.id
inner join regions on regionstable.regionsid = regions.id
where 1=1
<!---
newmastertrack.mag = 5--->
and newmastertrack.typer = 'article'
<cfif parameterexists(form.region) and #form.region# is not ''>
	and region like '%#form.region#%'
</cfif>	
and columntable.columnnum in (2351,2352,2353,2354,2355,2356,2357)
<cfif parameterexists(form.dater)>
and (columntable.dateposted BETWEEN '#dater#' AND '#daterend#')
<cfelse>
and columntable.dateposted > '#newdatetime#'
</cfif>
<cfinclude template="blockedip.cfm">

group by newmastertrack.idnumber
order by views desc

</cfquery>

<Cfquery name="c2" dbtype="query">
	select sum(views) as sumviews
	from geter
</cfquery>	 

<Cfoutput query="geter" maxRows="1">                        <tr>
                            <td>#form.region#</td>
							<td>#geter.recordcount#<cfset tot1=tot1+#geter.recordcount#></td> 
                            <td>#round(c2.sumviews)#<cfset tot2=tot2+#c2.sumviews#></td> 
                            



                        </tr>

</Cfoutput>  


<cfset form.region = 'Brooklyn'>
<Cfquery name="Geter" dataSource="#ds#">
select columntable.title, columntable.id,columntable.dateposted as dtentered, regions.name,  count(newmastertrack.id) as views,region,columntable.seo_friendly,columnmaster.authorname 
from newmastertrack inner join columntable on newmastertrack.idnumber = columntable.id inner join regionstable on regionstable.colid = columntable.ID inner join columnmaster on columntable.columnnum = columnmaster.id
inner join regions on regionstable.regionsid = regions.id
where 1=1
<!---
newmastertrack.mag = 5--->
and newmastertrack.typer = 'article'
<cfif parameterexists(form.region) and #form.region# is not ''>
	and region like '%#form.region#%'
</cfif>	
and columntable.columnnum in (2351,2352,2353,2354,2355,2356,2357)
<cfif parameterexists(form.dater)>
and (columntable.dateposted BETWEEN '#dater#' AND '#daterend#')
<cfelse>
and columntable.dateposted > '#newdatetime#'
</cfif>
<cfinclude template="blockedip.cfm">

group by newmastertrack.idnumber
order by views desc

</cfquery>

<Cfquery name="c2" dbtype="query">
	select sum(views) as sumviews
	from geter
</cfquery>	 

<Cfoutput query="geter" maxRows="1">                        <tr>
                            <td>#form.region#</td>
							<td>#geter.recordcount#<cfset tot1=tot1+#geter.recordcount#></td> 
                            <td>#round(c2.sumviews)#<cfset tot2=tot2+#c2.sumviews#></td> 
                            



                        </tr>

</Cfoutput>  


<cfset form.region = 'Queens'>
<Cfquery name="Geter" dataSource="#ds#">
select columntable.title, columntable.id,columntable.dateposted as dtentered, regions.name,  count(newmastertrack.id) as views,region,columntable.seo_friendly,columnmaster.authorname 
from newmastertrack inner join columntable on newmastertrack.idnumber = columntable.id inner join regionstable on regionstable.colid = columntable.ID inner join columnmaster on columntable.columnnum = columnmaster.id
inner join regions on regionstable.regionsid = regions.id
where 1=1
<!---
newmastertrack.mag = 5--->
and newmastertrack.typer = 'article'
<cfif parameterexists(form.region) and #form.region# is not ''>
	and region like '%#form.region#%'
</cfif>	
and columntable.columnnum in (2351,2352,2353,2354,2355,2356,2357)
<cfif parameterexists(form.dater)>
and (columntable.dateposted BETWEEN '#dater#' AND '#daterend#')
<cfelse>
and columntable.dateposted > '#newdatetime#'
</cfif>
<cfinclude template="blockedip.cfm">

group by newmastertrack.idnumber
order by views desc

</cfquery>

<Cfquery name="c2" dbtype="query">
	select sum(views) as sumviews
	from geter
</cfquery>	 

<Cfoutput query="geter" maxRows="1">                        <tr>
                            <td>#form.region#</td>
							<td>#geter.recordcount#<cfset tot1=tot1+#geter.recordcount#></td> 
                            <td>#round(c2.sumviews)#<cfset tot2=tot2+#c2.sumviews#></td> 
                            



                        </tr>

</Cfoutput>  



<cfset form.region = 'Bergen'>
<Cfquery name="Geter" dataSource="#ds#">
select columntable.title, columntable.id,columntable.dateposted as dtentered, regions.name,  count(newmastertrack.id) as views,region,columntable.seo_friendly,columnmaster.authorname 
from newmastertrack inner join columntable on newmastertrack.idnumber = columntable.id inner join regionstable on regionstable.colid = columntable.ID inner join columnmaster on columntable.columnnum = columnmaster.id
inner join regions on regionstable.regionsid = regions.id
where 1=1
<!---
newmastertrack.mag = 5--->
and newmastertrack.typer = 'article'
<cfif parameterexists(form.region) and #form.region# is not ''>
	and region like '%#form.region#%'
</cfif>	
and columntable.columnnum in (2351,2352,2353,2354,2355,2356,2357)
<cfif parameterexists(form.dater)>
and (columntable.dateposted BETWEEN '#dater#' AND '#daterend#')
<cfelse>
and columntable.dateposted > '#newdatetime#'
</cfif>
<cfinclude template="blockedip.cfm">

group by newmastertrack.idnumber
order by views desc

</cfquery>

<Cfquery name="c2" dbtype="query">
	select sum(views) as sumviews
	from geter
</cfquery>	 

<Cfoutput query="geter" maxRows="1">                        <tr>
                            <td>#form.region#</td>
							<td>#geter.recordcount#<cfset tot1=tot1+#geter.recordcount#></td> 
                            <td>#round(c2.sumviews)#<cfset tot2=tot2+#c2.sumviews#></td> 
                            



                        </tr>

</Cfoutput>  



<cfset form.region = 'Staten Island'>
<Cfquery name="Geter" dataSource="#ds#">
select columntable.title, columntable.id,columntable.dateposted as dtentered, regions.name,  count(newmastertrack.id) as views,region,columntable.seo_friendly,columnmaster.authorname 
from newmastertrack inner join columntable on newmastertrack.idnumber = columntable.id inner join regionstable on regionstable.colid = columntable.ID inner join columnmaster on columntable.columnnum = columnmaster.id
inner join regions on regionstable.regionsid = regions.id
where 1=1
<!---
newmastertrack.mag = 5--->
and newmastertrack.typer = 'article'
<cfif parameterexists(form.region) and #form.region# is not ''>
	and region like '%#form.region#%'
</cfif>	
and columntable.columnnum in (2351,2352,2353,2354,2355,2356,2357)
<cfif parameterexists(form.dater)>
and (columntable.dateposted BETWEEN '#dater#' AND '#daterend#')
<cfelse>
and columntable.dateposted > '#newdatetime#'
</cfif>
<cfinclude template="blockedip.cfm">

group by newmastertrack.idnumber
order by views desc

</cfquery>

<Cfquery name="c2" dbtype="query">
	select sum(views) as sumviews
	from geter
</cfquery>	 

<Cfoutput query="geter" maxRows="1">                        <tr>
                            <td>#form.region#</td>
							<td>#geter.recordcount#<cfset tot1=tot1+#geter.recordcount#></td> 
                            <td>#round(c2.sumviews)#<cfset tot2=tot2+#c2.sumviews#></td> 
                            



                        </tr>

</Cfoutput>  


<cfset form.region = 'Nassau'>
<Cfquery name="Geter" dataSource="#ds#">
select columntable.title, columntable.id,columntable.dateposted as dtentered, regions.name,  count(newmastertrack.id) as views,region,columntable.seo_friendly,columnmaster.authorname 
from newmastertrack inner join columntable on newmastertrack.idnumber = columntable.id inner join regionstable on regionstable.colid = columntable.ID inner join columnmaster on columntable.columnnum = columnmaster.id
inner join regions on regionstable.regionsid = regions.id
where 1=1
<!---
newmastertrack.mag = 5--->
and newmastertrack.typer = 'article'
<cfif parameterexists(form.region) and #form.region# is not ''>
	and region like '%#form.region#%'
</cfif>	
and columntable.columnnum in (2351,2352,2353,2354,2355,2356,2357)
<cfif parameterexists(form.dater)>
and (columntable.dateposted BETWEEN '#dater#' AND '#daterend#')
<cfelse>
and columntable.dateposted > '#newdatetime#'
</cfif>
<cfinclude template="blockedip.cfm">

group by newmastertrack.idnumber
order by views desc

</cfquery>

<Cfquery name="c2" dbtype="query">
	select sum(views) as sumviews
	from geter
</cfquery>	 

<Cfoutput query="geter" maxRows="1">                        <tr>
                            <td>#form.region#</td>
							<td>#geter.recordcount#<cfset tot1=tot1+#geter.recordcount#></td> 
                            <td>#round(c2.sumviews)#<cfset tot2=tot2+#c2.sumviews#></td> 
                            



                        </tr>

</Cfoutput>  


<cfset form.region = 'Suffolk'>
<Cfquery name="Geter" dataSource="#ds#">
select columntable.title, columntable.id,columntable.dateposted as dtentered, regions.name,  count(newmastertrack.id) as views,region,columntable.seo_friendly,columnmaster.authorname 
from newmastertrack inner join columntable on newmastertrack.idnumber = columntable.id inner join regionstable on regionstable.colid = columntable.ID inner join columnmaster on columntable.columnnum = columnmaster.id
inner join regions on regionstable.regionsid = regions.id
where 1=1
<!---
newmastertrack.mag = 5--->
and newmastertrack.typer = 'article'
<cfif parameterexists(form.region) and #form.region# is not ''>
	and region like '%#form.region#%'
</cfif>	
and columntable.columnnum in (2351,2352,2353,2354,2355,2356,2357)
<cfif parameterexists(form.dater)>
and (columntable.dateposted BETWEEN '#dater#' AND '#daterend#')
<cfelse>
and columntable.dateposted > '#newdatetime#'
</cfif>
<cfinclude template="blockedip.cfm">

group by newmastertrack.idnumber
order by views desc

</cfquery>

<Cfquery name="c2" dbtype="query">
	select sum(views) as sumviews
	from geter
</cfquery>	 

<Cfoutput query="geter" maxRows="1">                        <tr>
                            <td>#form.region#</td>
							<td>#geter.recordcount#<cfset tot1=tot1+#geter.recordcount#></td> 
                            <td>#round(c2.sumviews)#<cfset tot2=tot2+#c2.sumviews#></td> 
                            



                        </tr>

</Cfoutput>  


<Cfoutput> 
    <tr>
                            <td>xTOTAL</td>
							<td>#tot1#</td> 
                            <td>#tot2#</td> 
                            



                        </tr></Cfoutput>

             </tbody>




                </table>
                
                
                <p>
	                
	                
	                
            </div>
          </div>
        </div>
      </div>




      <!-- /.row -->

     <cfinclude template="footer.cfm">

<script src="../plugins/bower_components/datatables/jquery.dataTables.min.js"></script>

<!-- start - This is for export functionality only -->
<script src="https://cdn.datatables.net/buttons/1.2.2/js/dataTables.buttons.min.js"></script>
<script src="https://cdn.datatables.net/buttons/1.2.2/js/buttons.flash.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/2.5.0/jszip.min.js"></script>
<script src="https://cdn.rawgit.com/bpampuch/pdfmake/0.1.18/build/pdfmake.min.js"></script>
<script src="https://cdn.rawgit.com/bpampuch/pdfmake/0.1.18/build/vfs_fonts.js"></script>
<script src="https://cdn.datatables.net/buttons/1.2.2/js/buttons.html5.min.js"></script>
<script src="https://cdn.datatables.net/buttons/1.2.2/js/buttons.print.min.js"></script>
<!-- end - This is for export functionality only -->
<!-- end - This is for export functionality only -->

<script>
    $(document).ready(function(){
      $('#myTable').DataTable();
      $(document).ready(function() {
        var table = $('#example').DataTable({
          "columnDefs": [
          { "visible": false, "targets": 2 }
          ],
          "order": [[ 2, 'asc' ]],
          "displayLength": 25,
          "drawCallback": function ( settings ) {
            var api = this.api();
            var rows = api.rows( {page:'current'} ).nodes();
            var last=null;

            api.column(2, {page:'current'} ).data().each( function ( group, i ) {
              if ( last !== group ) {
                $(rows).eq( i ).before(
                  '<tr class="group"><td colspan="5">'+group+'</td></tr>'
                  );

                last = group;
              }
            } );
          }
        } );

    // Order by the grouping
    $('#example tbody').on( 'click', 'tr.group', function () {
      var currentOrder = table.order()[0];
      if ( currentOrder[0] === 2 && currentOrder[1] === 'asc' ) {
        table.order( [ 2, 'desc' ] ).draw();
      }
      else {
        table.order( [ 2, 'asc' ] ).draw();
      }
    });
  });
    });
    $('#example23').DataTable( {
        dom: 'Bfrtip',
        buttons: [
            'copy', 'csv', 'excel', 'pdf', 'print'
        ]
    });


  </script>

<!-- jQuery -->
<script src="../plugins/bower_components/jquery/dist/jquery.min.js"></script>
<!-- Bootstrap Core JavaScript -->
<script src="bootstrap/dist/js/bootstrap.min.js"></script>
<!-- Menu Plugin JavaScript -->
<script src="../plugins/bower_components/sidebar-nav/dist/sidebar-nav.min.js"></script>

<!--slimscroll JavaScript -->
<script src="js/jquery.slimscroll.js"></script>
<!--Wave Effects -->
<script src="js/waves.js"></script>
<!-- Custom Theme JavaScript -->
<script src="js/custom.min.js"></script>
<!-- Plugin JavaScript -->
<script src="../plugins/bower_components/moment/moment.js"></script>
<!-- Clock Plugin JavaScript -->
<script src="../plugins/bower_components/clockpicker/dist/jquery-clockpicker.min.js"></script>
<!-- Color Picker Plugin JavaScript -->
<script src="../plugins/bower_components/jquery-asColorPicker-master/libs/jquery-asColor.js"></script>
<script src="../plugins/bower_components/jquery-asColorPicker-master/libs/jquery-asGradient.js"></script>
<script src="../plugins/bower_components/jquery-asColorPicker-master/dist/jquery-asColorPicker.min.js"></script>
<!-- Date Picker Plugin JavaScript -->
<script src="../plugins/bower_components/bootstrap-datepicker/bootstrap-datepicker.min.js"></script>
<!-- Date range Plugin JavaScript -->
<script src="../plugins/bower_components/timepicker/bootstrap-timepicker.min.js"></script>
<script src="../plugins/bower_components/bootstrap-daterangepicker/daterangepicker.js"></script>
<script>
// Clock pickers
$('#single-input').clockpicker({
  placement: 'bottom',
  align: 'left',
  autoclose: true,
  'default': 'now'

});

$('.clockpicker').clockpicker({
    donetext: 'Done',

})
  .find('input').change(function(){
    console.log(this.value);
});

$('#check-minutes').click(function(e){
  // Have to stop propagation here
  e.stopPropagation();
  input.clockpicker('show')
      .clockpicker('toggleView', 'minutes');
});
if (/mobile/i.test(navigator.userAgent)) {
  $('input').prop('readOnly', true);
}
// Colorpicker

$(".colorpicker").asColorPicker();
$(".complex-colorpicker").asColorPicker({
    mode: 'complex'
});
$(".gradient-colorpicker").asColorPicker({
    mode: 'gradient'
});
// Date Picker
    jQuery('.mydatepicker, #datepicker').datepicker();
    jQuery('#datepicker-autoclose').datepicker({
        autoclose: true,
        todayHighlight: true
      });

    jQuery('#date-range').datepicker({
        toggleActive: true
      });
    jQuery('#datepicker-inline').datepicker({

        todayHighlight: true
      });

// Daterange picker

$('.input-daterange-datepicker').daterangepicker({
          buttonClasses: ['btn', 'btn-sm'],
                applyClass: 'btn-danger',
                cancelClass: 'btn-inverse'
        });
            $('.input-daterange-timepicker').daterangepicker({
                timePicker: true,
                format: 'MM/DD/YYYY h:mm A',
                timePickerIncrement: 30,
                timePicker12Hour: true,
                timePickerSeconds: false,
                buttonClasses: ['btn', 'btn-sm'],
                applyClass: 'btn-danger',
                cancelClass: 'btn-inverse'
            });
            $('.input-limit-datepicker').daterangepicker({
                format: 'MM/DD/YYYY',
                minDate: '06/01/2015',
                maxDate: '06/30/2015',
                buttonClasses: ['btn', 'btn-sm'],
                applyClass: 'btn-danger',
                cancelClass: 'btn-inverse',
                dateLimit: {
                    days: 6
                }
            });
            

</script>


<!--Style Switcher -->
<script src="../plugins/bower_components/styleswitcher/jQuery.style.switcher.js"></script>
</body>
</html>