#! /usr/bin/perl -w      

use Date::Calc qw{Today Add_Delta_Days};

#needed to create date range in sql statments
my $interval = -1;
my ( $year, $month, $day ) = Today();
my ( $y_year, $y_month, $y_day ) =
    Add_Delta_Days( Today(), $interval );
my $tday = sprintf qq{'%s-%02d-%02d'}, $year,   $month,   $day;
my $yday = sprintf qq{'%s-%02d-%02d'}, $y_year, $y_month, $y_day;

# New Tickets by Date Range
my $REPORT_BY_DATE = q{ 
SELECT 
   p.value,
   id AS ticket, 
   summary,
   status, 
   component, 
   version, 
   milestone, 
   t.type AS type, 
   (CASE status 
         WHEN 'assigned' 
         THEN owner||' *' 
         ELSE owner END) AS owner,
   strftime("%m/%d/%Y - %H:%M:%S", t.time, 'unixepoch') 
         AS CreatedTime,
   c.value as Verified
FROM ticket t, enum p, ticket_custom tc, ticket_custom c 
WHERE 
  p.name = t.priority 
  AND p.type = 'priority'
  AND t.type = 'defect'
  AND DATETIME(t.time, 'unixepoch')>= } . $yday . q{
  AND DATETIME(t.time, 'unixepoch')<= } . $tday . q{
  AND tc.ticket = t.id
  AND tc.name = 'story_card'
  AND t.status = 'new'
  AND c.ticket = t.id 
  AND c.name = 'verified'
ORDER BY id;
};