pwyliu · February 4, 2018 16:01 · dizzi90 · Jan 8, 2016
diff --git a/README.md b/README.md
diff --git a/ticketcount.coffee b/ticketcount.coffee
 class Dashing.Ticketcount extends Dashing.Widget
  onData: (data) ->
    if data.status == "failed"
      console.log(data)
diff --git a/ticketcount.html b/ticketcount.html
 <h1 class="title" data-bind="data.name | append ' Tickets'"></h1>

 <div class="upper">
    <h2 class="value" data-bind="data.count"></h2>
    <p class="subtitle">open</p>
 </div>

 <p class="updated-at" data-bind="updatedAtMessage"></p>
diff --git a/ticketcount.rb b/ticketcount.rb
 require 'mysql2'

 # DB Connection
 # -------------
 # See http://www.kace.com/support/resources/kb/solutiondetail?sol=SOL114992 for more details.

 user = 'R1'
 password = 'box747'
 host = 'myK1000.mydomain.local'
 database = 'ORG1'

 # Team Definitions
 # ----------------
 # Define teams based on Kace usernames. Then you can create individual widgets per team by
 # setting data-id="tickets_myTeamName" in the dashboard. See the gist for a dashboard example.
 #
 # After you define a team, make sure to add it to team_list. The default list
 # always has to be defined.
 # 
 # E.g.:
 #
 # dba = {
 #     "name" => "DBA", 
 #     "count" => 0, 
 #     "members" => ['moose', 'midge', 'ethel'], 
 # }
 # windows = {
 #     "name" => "Windows", 
 #     "count" => 0, 
 #     "members" => ['reggie', 'veronica', 'weatherbee'], 
 # }
 # linux = {
 #     "name" => "Linux", 
 #     "count" => 0, 
 #     "members" => ['archie', 'jughead', 'betty'], 
 # }
 # default = {
 #    "name" => "All", 
 #    "count" => 0, 
 # }
 # team_list = [dba, windows, linux, default]

 default = {
   "name" => "All", 
   "count" => 0, 
 }
 team_list = [default]

 # SQL query
 # ---------
 # You can modify this to get more information if you want fancier widgets.

 query="
    select HD_TICKET.ID, 
    CONCAT(IF(TIME_TO_SEC(NOW()) >= TIME_TO_SEC(HD_TICKET.TIME_OPENED),
        TO_DAYS(NOW()) - TO_DAYS(HD_TICKET.TIME_OPENED),
        TO_DAYS(NOW()) - TO_DAYS(HD_TICKET.TIME_OPENED) - 1), 'd ', 
        DATE_FORMAT(ADDTIME('2000-01-01 00:00:00',
        SEC_TO_TIME(TIME_TO_SEC(NOW())-TIME_TO_SEC(HD_TICKET.TIME_OPENED))),
        '%kh %im')) AS TIME_OPEN,
    HD_CATEGORY.NAME as CATEGORY, 
    HD_STATUS.NAME as STATUS,
    CREATED,
    ifnull((select USER_NAME from USER where HD_TICKET.OWNER_ID = USER.ID),' Unassigned') as OWNER_NAME
    from HD_TICKET
    left join HD_CATEGORY on HD_CATEGORY_ID = HD_CATEGORY.ID
    left join HD_STATUS on HD_STATUS_ID = HD_STATUS.ID 
        where HD_STATUS.STATE = 'stalled' OR HD_STATUS.STATE = 'opened'
    order by OWNER_NAME, CREATED, HD_TICKET.ID
 "

 # Engage
 # ------

 SCHEDULER.every '1m', :first_in => 0 do |job|
    # Reset counters
    team_list.each do |team|
        team["count"] = 0
    end 

    # fetch data
    begin
        conn = Mysql2::Client.new(
            :host=> host, 
            :database => database, 
            :username => user, 
            :password => password
        )
        results = conn.query(query)
        if results
            results.each do |row|
                team_list.each do |team|
                    if team["name"] == "All"
                        team["count"] += 1
                    else
                        if team["members"].include?(row["OWNER_NAME"].downcase)
                            team["count"] += 1
                        end
                    end
                end
            end
        end
    rescue Exception => ex
    ensure
        conn.close if conn
    end

    #send data
    if ex.nil?
        team_list.each do |team|
            send_event(
                "tickets_#{team["name"].downcase}", 
                { status: "success", data: team }
            )
        end
    else
        team_list.each do |team|
            send_event(
                "tickets_#{team["name"].downcase}", 
                { status: "failed", errorclass: ex.class, errormsg: ex.message }
            )
        end
    end
 end
diff --git a/ticketcount.scss b/ticketcount.scss
 // ----------------------------------------------------------------------------
 // Sass declarations
 // ----------------------------------------------------------------------------
 $background-color:  #47bbb3;
 $value-color:       #fff;

 $title-color:       rgba(255, 255, 255, 0.7);
 $updated-color:     rgba(0, 0, 0, 0.3);

 // ----------------------------------------------------------------------------
 // Widget-number styles
 // ----------------------------------------------------------------------------
 .widget-ticketcount {

  background-color: $background-color;

  .title {
    color: $title-color;
  }

  .value {
    color: $value-color;
  }

  .subtitle {
    font-size: 25px;
    color: $value-color;
  }

  .updated-at {
    color: $updated-color;
  }

 }
	class Dashing.Ticketcount extends Dashing.Widget
	onData: (data) ->
	if data.status == "failed"
	console.log(data)
	<h1 class="title" data-bind="data.name \| append ' Tickets'"></h1>

	<div class="upper">
	<h2 class="value" data-bind="data.count"></h2>
	<p class="subtitle">open</p>
	</div>

	<p class="updated-at" data-bind="updatedAtMessage"></p>
	require 'mysql2'

	# DB Connection
	# -------------
	# See http://www.kace.com/support/resources/kb/solutiondetail?sol=SOL114992 for more details.

	user = 'R1'
	password = 'box747'
	host = 'myK1000.mydomain.local'
	database = 'ORG1'

	# Team Definitions
	# ----------------
	# Define teams based on Kace usernames. Then you can create individual widgets per team by
	# setting data-id="tickets_myTeamName" in the dashboard. See the gist for a dashboard example.
	#
	# After you define a team, make sure to add it to team_list. The default list
	# always has to be defined.
	#
	# E.g.:
	#
	# dba = {
	# "name" => "DBA",
	# "count" => 0,
	# "members" => ['moose', 'midge', 'ethel'],
	# }
	# windows = {
	# "name" => "Windows",
	# "count" => 0,
	# "members" => ['reggie', 'veronica', 'weatherbee'],
	# }
	# linux = {
	# "name" => "Linux",
	# "count" => 0,
	# "members" => ['archie', 'jughead', 'betty'],
	# }
	# default = {
	# "name" => "All",
	# "count" => 0,
	# }
	# team_list = [dba, windows, linux, default]

	default = {
	"name" => "All",
	"count" => 0,
	}
	team_list = [default]

	# SQL query
	# ---------
	# You can modify this to get more information if you want fancier widgets.

	query="
	select HD_TICKET.ID,
	CONCAT(IF(TIME_TO_SEC(NOW()) >= TIME_TO_SEC(HD_TICKET.TIME_OPENED),
	TO_DAYS(NOW()) - TO_DAYS(HD_TICKET.TIME_OPENED),
	TO_DAYS(NOW()) - TO_DAYS(HD_TICKET.TIME_OPENED) - 1), 'd ',
	DATE_FORMAT(ADDTIME('2000-01-01 00:00:00',
	SEC_TO_TIME(TIME_TO_SEC(NOW())-TIME_TO_SEC(HD_TICKET.TIME_OPENED))),
	'%kh %im')) AS TIME_OPEN,
	HD_CATEGORY.NAME as CATEGORY,
	HD_STATUS.NAME as STATUS,
	CREATED,
	ifnull((select USER_NAME from USER where HD_TICKET.OWNER_ID = USER.ID),' Unassigned') as OWNER_NAME
	from HD_TICKET
	left join HD_CATEGORY on HD_CATEGORY_ID = HD_CATEGORY.ID
	left join HD_STATUS on HD_STATUS_ID = HD_STATUS.ID
	where HD_STATUS.STATE = 'stalled' OR HD_STATUS.STATE = 'opened'
	order by OWNER_NAME, CREATED, HD_TICKET.ID
	"

	# Engage
	# ------

	SCHEDULER.every '1m', :first_in => 0 do \|job\|
	# Reset counters
	team_list.each do \|team\|
	team["count"] = 0
	end

	# fetch data
	begin
	conn = Mysql2::Client.new(
	:host=> host,
	:database => database,
	:username => user,
	:password => password
	)
	results = conn.query(query)
	if results
	results.each do \|row\|
	team_list.each do \|team\|
	if team["name"] == "All"
	team["count"] += 1
	else
	if team["members"].include?(row["OWNER_NAME"].downcase)
	team["count"] += 1
	end
	end
	end
	end
	end
	rescue Exception => ex
	ensure
	conn.close if conn
	end

	#send data
	if ex.nil?
	team_list.each do \|team\|
	send_event(
	"tickets_#{team["name"].downcase}",
	{ status: "success", data: team }
	)
	end
	else
	team_list.each do \|team\|
	send_event(
	"tickets_#{team["name"].downcase}",
	{ status: "failed", errorclass: ex.class, errormsg: ex.message }
	)
	end
	end
	end
	// ----------------------------------------------------------------------------
	// Sass declarations
	// ----------------------------------------------------------------------------
	$background-color: #47bbb3;
	$value-color: #fff;

	$title-color: rgba(255, 255, 255, 0.7);
	$updated-color: rgba(0, 0, 0, 0.3);

	// ----------------------------------------------------------------------------
	// Widget-number styles
	// ----------------------------------------------------------------------------
	.widget-ticketcount {

	background-color: $background-color;

	.title {
	color: $title-color;
	}

	.value {
	color: $value-color;
	}

	.subtitle {
	font-size: 25px;
	color: $value-color;
	}

	.updated-at {
	color: $updated-color;
	}

	}