class StatsController < ApplicationController include PdfGen include SqlExport require 'uri' require 'net/http' layout "callc" before_filter :check_localization before_filter :authorize, :except => [:active_calls_longer_error, :active_calls_longer_error_send_email] before_filter :check_if_can_see_finances, :only => [:profit] before_filter :check_authentication, :only => [:active_calls, :active_calls_count, :active_calls_order, :active_calls_show] before_filter :find_user_from_id_or_session, :only => [:reseller_all_user_stats, :call_list, :index, :user_stats, :missed_calls, :call_list_to_csv, :call_list_from_link, :new_calls_list, :user_logins, :call_list_to_pdf] before_filter :find_provider, :only => [:providers_calls] before_filter :check_reseller_in_providers, :only => [:providers, :providers_stats, :country_stats] before_filter :no_cache, :only => [:active_calls] before_filter :check_mor_11_extend_new, :only => [:google_maps, :google_active] skip_before_filter :redirect_callshop_manager, :only => [:prefix_finder_find, :prefix_finder_find_country] def index user_stats render :action => "user_stats" end def show_user_stats session[:show_user_stats_options] ? @options = session[:show_user_stats_options] : @options = {:order_by => "nice_user", :order_desc => 0, :page => 1} @Show_Currency_Selector=1 change_date @page_title = _('Calls') @page_icon = "call.png" if session[:usertype] == "accountant" @owner_id = "0" else @owner_id = session[:user_id].to_s end if session[:usertype] == "reseller" if current_user.own_providers.to_i == 0 caller_type = "" provider_prices = "calls.reseller_price" else caller_type = "" provider_prices ="IF(providers.common_use = '0',calls.provider_price,calls.reseller_price)" end elsif session[:usertype] == "admin" caller_type = "" provider_prices = "calls.provider_price" else caller_type = "AND callertype = 'Local'" provider_prices = "calls.provider_price" end sql_get = "SELECT COUNT(A.users_id) as users, SUM(A.balance) as balance, SUM(A.calls) as calls, SUM(sum_duration) as sum_duration, SUM(price) as price, SUM( provider_price)as provider_price, SUM(reseller_price) as reseller_price FROM (SELECT users.id as 'users_id', users.balance as 'balance', B.calls as 'calls', B.sum_duration as 'sum_duration', B.price as 'price', B.provider_price as 'provider_price', B.reseller_price as 'reseller_price' FROM users LEFT JOIN (SELECT users.id as 'users_id', users.balance as 'balance', COUNT( calls.id ) AS 'calls', sum( IF(calls.billsec > 0, calls.billsec, CEIL(calls.real_billsec) ) ) AS 'sum_duration', SUM( calls.user_price ) AS 'price', SUM( #{provider_prices}) AS 'provider_price', SUM( calls.reseller_price ) AS 'reseller_price' FROM users LEFT JOIN calls ON (calls.user_id = users.id) #{'LEFT JOIN providers ON calls.provider_id = providers.id ' if session[:usertype] == "reseller" and current_user.own_providers.to_i == 1} WHERE calldate BETWEEN '" + session_from_datetime + "' AND '" + session_till_datetime + "' #{caller_type} AND disposition = 'ANSWERED' GROUP BY users.id ORDER BY users.first_name ASC) AS B ON (users.id = B.users_id) WHERE users.hidden = 0 AND users.owner_id = #{@owner_id}) AS A" sum = ActiveRecord::Base.connection.select_all(sql_get) @options[:order_by] = params[:order_by] if params[:order_by] @options[:order_desc] = params[:order_desc].to_i if params[:order_desc] @options[:order_by_full] = @options[:order_by] + (@options[:order_desc] == 1 ? " DESC" : " ASC") if @options[:order_by] == "users.first_name" @options[:order_by_full] += ", users.last_name" + (@options[:order_desc] == 1 ? " DESC" : " ASC") end @options[:order_by_full] = @options[:order_by] + (@options[:order_desc] == 1 ? " DESC" : " ASC") @options[:order] = User.users_order_by(params, @options) user_count = sum[0]["users"].to_i @options[:page] = params[:page].to_i if params[:page] @page = @options[:page] @total_pages = (user_count / session[:items_per_page].to_f).ceil istart = (session[:items_per_page] * (@page - 1)) session[:usertype] == 'admin' ? price_by = "provider_price" : price_by ="reseller_price" sql = "SELECT #{SqlExport.nice_user_sql}, users.id, users.first_name, users.last_name, users.username, users.balance, B.calls AS 'calls', B.sum_duration as 'sum_duration', B.price as 'price', B.provider_price as 'provider_price', B.reseller_price as 'reseller_price', A.all_calls, IF(sum_duration/calls IS NOT NULL,sum_duration/calls , 0) AS 'acd', IF ((calls/all_calls)*100 IS NOT NULL,(calls/all_calls)*100, 0 ) AS 'asr', IF (price-#{price_by} IS NOT NULL,price-#{price_by}, 0) AS 'profit', IF ((price-#{price_by})/price IS NOT NULL,(price-#{price_by})/price,0) AS'margin', IF (price/#{price_by} IS NOT NULL, (price/#{price_by}*100)-100, 0) as 'markup' FROM users LEFT JOIN (SELECT calls.user_id AS 'user_id', COUNT(calls.id) as 'calls', sum(IF(calls.billsec > 0, calls.billsec, CEIL(calls.real_billsec) )) AS 'sum_duration', SUM(calls.user_price) AS 'price', SUM( #{provider_prices}) AS 'provider_price', SUM(calls.reseller_price) AS 'reseller_price' FROM calls #{'LEFT JOIN providers ON calls.provider_id = providers.id ' if session[:usertype] == "reseller" and current_user.own_providers.to_i == 1} WHERE disposition = 'ANSWERED' AND calldate BETWEEN \'" + session_from_datetime + "' AND '" + session_till_datetime + "' #{caller_type} GROUP BY calls.user_id) AS B ON (B.user_id = users.id) LEFT JOIN (SELECT calls.user_id as 'user_id', COUNT(calls.id) as 'all_calls' FROM calls WHERE calldate BETWEEN \'" + session_from_datetime + "' AND '" + session_till_datetime + "' #{caller_type} GROUP BY calls.user_id) AS A ON (A.user_id = users.id) WHERE users.hidden = 0 AND users.owner_id = '#{@owner_id}' ORDER BY #{@options[:order]} LIMIT #{istart},#{session[:items_per_page]};" res = ActiveRecord::Base.connection.select_all(sql) exrate = Currency.count_exchange_rate(session[:default_currency], session[:show_currency]) @res = res @total_balance = 0.0 @total_calls = 0 @total_time = 0 @total_price = 0.0 @total_prov = 0.0 @curr_price = [] @curr_prov_price = [] @user_price = [] @prov_price = [] @profit = [] @curr_balance = [] for r in res id = r["id"].to_i @rate_cur, @rate_cur2 = Currency.count_exchange_prices({:exrate => exrate, :prices => [r["price"].to_f, r["balance"].to_f]}) @total_balance += @rate_cur2 @total_calls += r["calls"].to_f @total_time += r["sum_duration"].to_f @total_price += @rate_cur @curr_price[id]=@rate_cur @curr_balance[id]=@rate_cur2 @user_price[id] = r["price"].to_f # if session[:usertype]=='admin' @prov_price[id]= r["provider_price"].to_f @rate_cur = Currency.count_exchange_prices({:exrate => exrate, :prices => [r["provider_price"].to_f]}) if r["provider_price"] @curr_prov_price[id] = @rate_cur if r["provider_price"] @total_prov += @rate_cur.to_f # else # @prov_price[id]= r["reseller_price"].to_f # @rate_cur = Currency.count_exchange_prices({:exrate=>exrate, :prices=>[provider_price.to_f]}) if provider_price # @curr_prov_price[id] = @rate_cur if provider_price # @total_prov += @rate_cur.to_f # end end @all_balance = Currency.count_exchange_prices({:exrate => exrate, :prices => [sum[0]["balance"].to_f]}) @all_time = sum[0]["sum_duration"].to_i @all_price = Currency.count_exchange_prices({:exrate => exrate, :prices => [sum[0]["price"].to_f]}) #if session[:usertype]=='admin' @all_prov_price = Currency.count_exchange_prices({:exrate => exrate, :prices => [sum[0]["provider_price"].to_f]}) #else # @all_prov_price = Currency.count_exchange_prices({:exrate=>exrate, :prices=>[sum[0]["reseller_price"].to_f]}) #end @all_profit = Currency.count_exchange_prices({:exrate => exrate, :prices => [sum[0]["price"].to_f]}).to_f - @all_prov_price.to_f @total_profit = @total_price.to_f - @total_prov.to_f @all_calls = sum[0]["calls"].to_i #======================== session[:show_user_stats_options] = @options if request.xml_http_request? render :partial => "list_stats", :layout => false end end def all_users_detailed change_date @page_title = _('All_users_detailed') #@help_link = 'http://wiki.kolmisoft.com/index.php/Last_Calls#Call_information_representation'#conflicts with flash @users = User.find(:all, :conditions => "hidden = 0") #, :conditions => "usertype = 'user'") #, :limit => 6) call_stats = Call.total_calls_by_direction_and_disposition(session_from_date, session_till_date) @o_answered_calls = 0 @o_no_answer_calls = 0 @o_busy_calls = 0 @o_failed_calls = 0 @i_answered_calls = 0 @i_no_answer_calls = 0 @i_busy_calls = 0 @i_failed_calls = 0 for stats in call_stats if stats['direction'] == 'outgoing' if stats['disposition'] == 'ANSWERED' @o_answered_calls = stats['total_calls'].to_i elsif stats['disposition'] == 'NO ANSWER' @o_no_answer_calls = stats['total_calls'].to_i elsif stats['disposition'] == 'BUSY' @o_busy_calls = stats['total_calls'].to_i elsif stats['disposition'] == 'FAILED' @o_failed_calls = stats['total_calls'].to_i end elsif stats['direction'] == 'incoming' if stats['disposition'] == 'ANSWERED' @i_answered_calls = stats['total_calls'].to_i elsif stats['disposition'] == 'NO ANSWER' @i_no_answer_calls = stats['total_calls'].to_i elsif stats['disposition'] == 'BUSY' @i_busy_calls = stats['total_calls'].to_i elsif stats['disposition'] == 'FAILED' @i_failed_calls = stats['total_calls'].to_i end end end @outgoing_calls = @o_answered_calls + @o_no_answer_calls + @o_busy_calls + @o_failed_calls @incoming_calls = @i_answered_calls + @i_no_answer_calls + @i_busy_calls + @i_failed_calls @total_calls = @incoming_calls + @outgoing_calls sfd = session_from_date std = session_till_date @outgoing_perc = 0 @outgoing_perc = @outgoing_calls.to_f / @total_calls * 100 if @total_calls > 0 @incoming_perc = 0 @incoming_perc = @incoming_calls.to_f / @total_calls * 100 if @total_calls > 0 @o_answered_perc = 0 @o_answered_perc = @o_answered_calls.to_f / @outgoing_calls * 100 if @outgoing_calls > 0 @o_no_answer_perc = 0 @o_no_answer_perc = @o_no_answer_calls.to_f / @outgoing_calls * 100 if @outgoing_calls > 0 @o_busy_perc = 0 @o_busy_perc = @o_busy_calls.to_f / @outgoing_calls * 100 if @outgoing_calls > 0 @o_failed_perc = 0 @o_failed_perc = @o_failed_calls.to_f / @outgoing_calls * 100 if @outgoing_calls > 0 @i_answered_perc = 0 @i_answered_perc = @i_answered_calls.to_f / @incoming_calls * 100 if @incoming_calls > 0 @i_no_answer_perc = 0 @i_no_answer_perc = @i_no_answer_calls.to_f / @incoming_calls * 100 if @incoming_calls > 0 @i_busy_perc = 0 @i_busy_perc = @i_busy_calls.to_f / @incoming_calls * 100 if @incoming_calls > 0 @i_failed_perc = 0 @i_failed_perc = @i_failed_calls.to_f / @incoming_calls * 100 if @incoming_calls > 0 @t_answered_calls = @o_answered_calls + @i_answered_calls @t_no_answer_calls = @o_no_answer_calls + @i_no_answer_calls @t_busy_calls = @o_busy_calls + @i_busy_calls @t_failed_calls = @o_failed_calls + @i_failed_calls @t_answered_perc = 0 @t_answered_perc = @t_answered_calls.to_f / @total_calls * 100 if @total_calls > 0 @t_no_answer_perc = 0 @t_no_answer_perc = @t_no_answer_calls.to_f / @total_calls * 100 if @total_calls > 0 @t_busy_perc = 0 @t_busy_perc = @t_busy_calls.to_f / @total_calls * 100 if @total_calls > 0 @t_failed_perc = 0 @t_failed_perc = @t_failed_calls.to_f / @total_calls * 100 if @total_calls > 0 @a_date, @a_calls, @a_billsec, @a_avg_billsec = Call.answered_calls_day_by_day(session_from_date, session_till_date) @t_calls = @a_calls.last.to_i @t_billsec = @a_billsec.last.to_i @t_avg_billsec = @a_avg_billsec.last.to_i @a_calls.delete_at(@a_calls.length - 1) @a_billsec.delete_at(@a_billsec.length - 1) @a_avg_billsec.delete_at(@a_billsec.length - 1) index = @a_date.length - 1 @t_avg_billsec = @t_billsec / @t_calls if @t_calls > 0 #formating graph for INCOMING/OUTGOING calls @Out_in_calls_graph = "\"" if @t_calls > 0 @Out_in_calls_graph += _('Outgoing') + ";" +@outgoing_calls.to_s + ";" + "false" + "\\n" + _('Incoming') +";" +@incoming_calls.to_s + ";" + "false" + "\\n\"" else @Out_in_calls_graph = "\"No result" + ";" + "1" + ";" + "false" + "\\n\"" end #formating graph for Call-type calls @Out_in_calls_graph2 = "\"" if @t_calls > 0 @Out_in_calls_graph2 += _('ANSWERED') +";" +@t_answered_calls.to_s + ";" + "false" + "\\n" @Out_in_calls_graph2 += _('NO_ANSWER') +";" +@t_no_answer_calls.to_s + ";" + "false" + "\\n" @Out_in_calls_graph2 += _('BUSY') +";" +@t_busy_calls.to_s + ";" + "false" + "\\n" @Out_in_calls_graph2 += _('FAILED') +";" +@t_failed_calls.to_s + ";" + "false" + "\\n" @Out_in_calls_graph2 += "\"" else @Out_in_calls_graph2 = "\"No result" + ";" + "1" + ";" + "false" + "\\n\"" end #formating graph for Calls ine=0 @Calls_graph ="" i=0 for i in 0..@a_calls.size-1 @Calls_graph += @a_date[ine].to_s + ";" + @a_calls[i].to_i.to_s + "\\n" ine=ine +1 end #formating graph for Calltime i=0 @Calltime_graph ="" for i in 0..@a_billsec.size-1 @Calltime_graph += @a_date[i].to_s + ";" + (@a_billsec[i] / 60).to_s + "\\n" ine=ine +1 end #formating graph for Avg.Calltime ine=0 @Avg_Calltime_graph ="" i=0 for i in 0..@a_avg_billsec.size-1 @Avg_Calltime_graph += @a_date[ine].to_s + ";" + @a_avg_billsec[i].to_i.to_s + "\\n" ine=ine +1 end end =begin in before filter : user (:find_user_from_id_or_session, :authorize_user) =end def reseller_all_user_stats unless session[:usertype] == 'reseller' dont_be_so_smart redirect_to :controller => :callc, :action => :main and return false end @users = User.find_all_for_select(corrected_user_id, {:exclude_owner => true}) @users << @user change_date @page_title = _('Detailed_stats_for')+" "+@user.first_name+" "+@user.last_name # @todays_normative = @user.normative_perc(Time.now) # @months_normative = @user.months_normative(Time.now.strftime("%Y-%m")) ############ @sdate = Time.mktime(session[:year_from], session[:month_from], session[:day_from]) year, month, day = last_day_month('till') @edate = Time.mktime(year, month, day) @a_date = [] @a_calls = [] @a_billsec = [] @a_avg_billsec = [] @a_normative = [] @t_calls = 0 @t_billsec = 0 @t_avg_billsec = 0 @t_normative = 0 @t_norm_days = 0 @t_avg_normative = 0 #@new_calls_today =0 @i_answered_calls=0 @i_busy_calls=0 @i_failed_calls=0 @i_no_answer_calls=0 @outgoing_calls=0 @incoming_calls=0 @total_calls=0 @o_answered_calls=0 @o_no_answer_calls=0 @o_busy_calls=0 @o_failed_calls = 0 for user in @users #@new_calls_today += user.new_calls(Time.now.strftime("%Y-%m-%d")).size @outgoing_calls += user.total_calls("outgoing", session_from_date, session_till_date) @incoming_calls += user.total_calls("incoming", session_from_date, session_till_date) @total_calls += user.total_calls("all", session_from_date, session_till_date) @o_answered_calls += user.total_calls("answered_out", session_from_date, session_till_date) @o_no_answer_calls += user.total_calls("no_answer_out", session_from_date, session_till_date) @o_busy_calls += user.total_calls("busy_out", session_from_date, session_till_date) @o_failed_calls += user.total_calls("failed_out", session_from_date, session_till_date) @i_answered_calls += user.total_calls("answered_inc", session_from_date, session_till_date) @i_no_answer_calls += user.total_calls("no_answer_inc", session_from_date, session_till_date) @i_busy_calls += user.total_calls("busy_inc", session_from_date, session_till_date) @i_failed_calls += user.total_calls("failed_inc", session_from_date, session_till_date) i = 0 @sdate = Time.mktime(session[:year_from], session[:month_from], session[:day_from]) @edate = Time.mktime(year, month, day) while @sdate < @edate @a_date[i] = @sdate.strftime("%Y-%m-%d") unless @a_calls[i] @a_calls[i] = 0 @a_billsec[i] = 0 @a_normative[i] = 0 end @a_calls[i] += user.total_calls("answered_out", @a_date[i], @a_date[i]) + @user.total_calls("answered_inc", @a_date[i], @a_date[i]) @a_billsec[i] += user.total_billsec("answered_out", @a_date[i], @a_date[i]) + @user.total_duration("answered_inc", @a_date[i], @a_date[i]) @a_normative[i] += user.normative_perc(@sdate).to_i @sdate += (60 * 60 * 24) i+=1 end end @a_calls.each_with_index { |calls, index| @a_avg_billsec[index] = @a_billsec[index] / @a_calls[index] if @a_calls[index] > 0 @t_calls += @a_calls[index] @t_billsec += @a_billsec[index] @t_normative += @a_normative[index] @t_norm_days += 1 if @a_normative[index] > 0 } @outgoing_perc = 0 @outgoing_perc = @outgoing_calls.to_f / @total_calls * 100 if @total_calls > 0 @incoming_perc = 0 @incoming_perc = @incoming_calls.to_f / @total_calls * 100 if @total_calls > 0 @o_answered_perc = 0 @o_answered_perc = @o_answered_calls.to_f / @outgoing_calls * 100 if @outgoing_calls > 0 @o_no_answer_perc = 0 @o_no_answer_perc = @o_no_answer_calls.to_f / @outgoing_calls * 100 if @outgoing_calls > 0 @o_busy_perc = 0 @o_busy_perc = @o_busy_calls.to_f / @outgoing_calls * 100 if @outgoing_calls > 0 @o_failed_perc = 0 @o_failed_perc = @o_failed_calls.to_f / @outgoing_calls * 100 if @outgoing_calls > 0 @i_answered_perc = 0 @i_answered_perc = @i_answered_calls.to_f / @incoming_calls * 100 if @incoming_calls > 0 @i_no_answer_perc = 0 @i_no_answer_perc = @i_no_answer_calls.to_f / @incoming_calls * 100 if @incoming_calls > 0 @i_busy_perc = 0 @i_busy_perc = @i_busy_calls.to_f / @incoming_calls * 100 if @incoming_calls > 0 @i_failed_perc = 0 @i_failed_perc = @i_failed_calls.to_f / @incoming_calls * 100 if @incoming_calls > 0 @t_answered_calls = @o_answered_calls + @i_answered_calls @t_no_answer_calls = @o_no_answer_calls + @i_no_answer_calls @t_busy_calls = @o_busy_calls + @i_busy_calls @t_failed_calls = @o_failed_calls + @i_failed_calls @t_answered_perc = 0 @t_answered_perc = @t_answered_calls.to_f / @total_calls * 100 if @total_calls > 0 @t_no_answer_perc = 0 @t_no_answer_perc = @t_no_answer_calls.to_f / @total_calls * 100 if @total_calls > 0 @t_busy_perc = 0 @t_busy_perc = @t_busy_calls.to_f / @total_calls * 100 if @total_calls > 0 @t_failed_perc = 0 @t_failed_perc = @t_failed_calls.to_f / @total_calls * 100 if @total_calls > 0 index = i @t_avg_billsec = @t_billsec / @t_calls if @t_calls > 0 @t_avg_normative = @t_normative / @t_norm_days if @t_norm_days > 0 #formating graph for INCOMING/OUTGING calls @Out_in_calls_graph = "\"" if @t_calls > 0 @Out_in_calls_graph += _('Outgoing') + ";" +@outgoing_calls.to_s + ";" + "false" + "\\n" + _('Incoming') +";" +@incoming_calls.to_s + ";" + "true" + "\\n\"" else @Out_in_calls_graph = "\"No result" + ";" + "1" + ";" + "false" + "\\n\"" end #formating graph for INCOMING/OUTGING calls @Out_in_calls_graph2 = "\"" if @t_calls > 0 @Out_in_calls_graph2 += _('ANSWERED') +";" +@t_answered_calls.to_s + ";" + "false" + "\\n" + _('NO_ANSWER') +";" +@t_no_answer_calls.to_s + ";" + "true" + "\\n" + _('BUSY') +";" +@t_busy_calls.to_s + ";" + "false" + "\\n" + _('FAILED') +";" +@t_failed_calls.to_s + ";" + "false" +"\\n\"" else @Out_in_calls_graph2 = "\"No result" + ";" + "1" + ";" + "false" + "\\n\"" end #formating graph for Calls ine=0 @Calls_graph ="" while ine <= index - 1 @Calls_graph +=nice_date(@a_date[ine].to_s) + ";" + @a_calls[ine].to_s + "\\n" ine=ine +1 end #formating graph for Calltime i=0 @Calltime_graph ="" for i in 0..@a_billsec.size-1 @Calltime_graph +=nice_date(@a_date[i].to_s) + ";" + (@a_billsec[i] / 60).to_s + "\\n" ine=ine +1 end #formating graph for Avg.Calltime ine=0 @Avg_Calltime_graph ="" while ine <= index - 1 @Avg_Calltime_graph +=nice_date(@a_date[ine].to_s) + ";" + @a_avg_billsec[ine].to_s + "\\n" ine=ine +1 end end =begin in before filter : user (:find_user_from_id_or_session, :authorize_user) =end def user_stats change_date #@active_users = active_users(Time.now - 0.days).size @page_title = _('Detailed_stats_for')+" "+@user.first_name+" "+@user.last_name @todays_normative = @user.normative_perc(Time.now) @months_normative = @user.months_normative(Time.now.strftime("%Y-%m")) @new_calls_today = @user.new_calls(Time.now.strftime("%Y-%m-%d")).size call_stats = Call.total_calls_by_direction_and_disposition(session_from_date, session_till_date, [@user.id]) @o_answered_calls = 0 @o_no_answer_calls = 0 @o_busy_calls = 0 @o_failed_calls = 0 @i_answered_calls = 0 @i_no_answer_calls = 0 @i_busy_calls = 0 @i_failed_calls = 0 for stats in call_stats if stats['direction'] == 'outgoing' if stats['disposition'] == 'ANSWERED' @o_answered_calls = stats['total_calls'].to_i elsif stats['disposition'] == 'NO ANSWER' @o_no_answer_calls = stats['total_calls'].to_i elsif stats['disposition'] == 'BUSY' @o_busy_calls = stats['total_calls'].to_i elsif stats['disposition'] == 'FAILED' @o_failed_calls = stats['total_calls'].to_i end end if stats['direction'] == 'incoming' if stats['disposition'] == 'ANSWERED' @i_answered_calls = stats['total_calls'].to_i elsif stats['disposition'] == 'NO ANSWER' @i_no_answer_calls = stats['total_calls'].to_i elsif stats['disposition'] == 'BUSY' @i_busy_calls = stats['total_calls'].to_i elsif stats['disposition'] == 'FAILED' @i_failed_calls = stats['total_calls'].to_i end end end @outgoing_calls = @o_answered_calls + @o_no_answer_calls + @o_busy_calls + @o_failed_calls @incoming_calls = @i_answered_calls + @i_no_answer_calls + @i_busy_calls + @i_failed_calls @total_calls = @incoming_calls + @outgoing_calls @o_answered_perc = 0 @o_answered_perc = @o_answered_calls.to_f / @outgoing_calls * 100 if @outgoing_calls > 0 @o_no_answer_perc = 0 @o_no_answer_perc = @o_no_answer_calls.to_f / @outgoing_calls * 100 if @outgoing_calls > 0 @o_busy_perc = 0 @o_busy_perc = @o_busy_calls.to_f / @outgoing_calls * 100 if @outgoing_calls > 0 @o_failed_perc = 0 @o_failed_perc = @o_failed_calls.to_f / @outgoing_calls * 100 if @outgoing_calls > 0 @i_answered_perc = 0 @i_answered_perc = @i_answered_calls.to_f / @incoming_calls * 100 if @incoming_calls > 0 @i_no_answer_perc = 0 @i_no_answer_perc = @i_no_answer_calls.to_f / @incoming_calls * 100 if @incoming_calls > 0 @i_busy_perc = 0 @i_busy_perc = @i_busy_calls.to_f / @incoming_calls * 100 if @incoming_calls > 0 @i_failed_perc = 0 @i_failed_perc = @i_failed_calls.to_f / @incoming_calls * 100 if @incoming_calls > 0 @t_answered_calls = @o_answered_calls + @i_answered_calls @t_no_answer_calls = @o_no_answer_calls + @i_no_answer_calls @t_busy_calls = @o_busy_calls + @i_busy_calls @t_failed_calls = @o_failed_calls + @i_failed_calls @t_answered_perc = 0 @t_answered_perc = @t_answered_calls.to_f / @total_calls * 100 if @total_calls > 0 @t_no_answer_perc = 0 @t_no_answer_perc = @t_no_answer_calls.to_f / @total_calls * 100 if @total_calls > 0 @t_busy_perc = 0 @t_busy_perc = @t_busy_calls.to_f / @total_calls * 100 if @total_calls > 0 @t_failed_perc = 0 @t_failed_perc = @t_failed_calls.to_f / @total_calls * 100 if @total_calls > 0 @outgoing_perc = 0 @outgoing_perc = @outgoing_calls.to_f / @total_calls * 100 if @total_calls > 0 @incoming_perc = 0 @incoming_perc = @incoming_calls.to_f / @total_calls * 100 if @total_calls > 0 ############ @sdate = Time.mktime(session[:year_from], session[:month_from], session[:day_from]) year, month, day = last_day_month('till') @edate = Time.mktime(year, month, day) @a_date = [] @a_calls = [] @a_billsec = [] @a_avg_billsec = [] @a_normative = [] @t_calls = 0 @t_billsec = 0 @t_avg_billsec = 0 @t_normative = 0 @t_norm_days = 0 @t_avg_normative = 0 @a_date, @a_calls, @a_billsec, @a_avg_billsec = Call.answered_calls_day_by_day(session_from_date, session_till_date, [@user.id]) @t_calls = @a_calls.last.to_i @t_billsec = @a_billsec.last.to_i @t_avg_billsec = @a_avg_billsec.last.to_i @a_calls.delete_at(@a_calls.length - 1) @a_billsec.delete_at(@a_billsec.length - 1) @a_avg_billsec.delete_at(@a_billsec.length - 1) index = @a_date.length - 1 @t_avg_billsec = @t_billsec / @t_calls if @t_calls > 0 @t_avg_normative = @t_normative / @t_norm_days if @t_norm_days > 0 #formating graph for INCOMING/OUTGING calls @Out_in_calls_graph = "\"" if @t_calls > 0 @Out_in_calls_graph += _('Outgoing') + ";" +@outgoing_calls.to_s + ";" + "false" + "\\n" + _('Incoming') +";" +@incoming_calls.to_s + ";" + "true" + "\\n\"" else @Out_in_calls_graph = "\"No result" + ";" + "1" + ";" + "false" + "\\n\"" end #formating graph for INCOMING/OUTGING calls @Out_in_calls_graph2 = "\"" if @t_calls > 0 @Out_in_calls_graph2 += _('ANSWERED') +";" +@t_answered_calls.to_s + ";" + "false" + "\\n" + _('NO_ANSWER') +";" +@t_no_answer_calls.to_s + ";" + "true" + "\\n" + _('BUSY') +";" +@t_busy_calls.to_s + ";" + "false" + "\\n" + _('FAILED') +";" +@t_failed_calls.to_s + ";" + "false" +"\\n\"" else @Out_in_calls_graph2 = "\"No result" + ";" + "1" + ";" + "false" + "\\n\"" end #formating graph for Calls i=0 @Calls_graph ="" for i in 0..@a_billsec.size-1 @Calls_graph +=nice_date(@a_date[i].to_s) + ";" + @a_calls[i].to_s + "\\n" # ine=ine +1 end #formating graph for Calltime i=0 @Calltime_graph ="" for i in 0..@a_billsec.size-1 @Calltime_graph +=nice_date(@a_date[i].to_s) + ";" + (@a_billsec[i] / 60).to_s + "\\n" #ine=ine +1 end #formating graph for Avg.Calltime i=0 @Avg_Calltime_graph ="" for i in 0..@a_billsec.size-1 @Avg_Calltime_graph +=nice_date(@a_date[i].to_s) + ";" + @a_avg_billsec[i].to_s + "\\n" #ine=ine +1 end end =begin in before filter : user (:find_user_from_id_or_session, :authorize_user) =end def user_logins change_date @Login_graph =[] @page = 1 @page = params[:page].to_i if params[:page] @page_title = _('Login_stats_for')+" "+@user.first_name+" "+@user.last_name date_start = Time.mktime(session[:year_from], session[:month_from], session[:day_from]) date_end = Time.mktime(session[:year_till], session[:month_till], session[:day_till]) @MyDay = Struct.new("MyDay", :date, :login, :logout, :duration) @a = [] #day @b = [] #login @c = [] #logout @d = [] #duration #making date array date_end = Time.now if date_end > Time.now if date_start == date_end @a << date_start else date = date_start while date < (date_end + 1.day) @a << date date = date+1.day end end @total_pages = ((@a.size).to_f / 10.to_f).ceil @all_date =@a @a = [] iend = ((10 * @page) - 1) iend = @all_date.size - 1 if iend > (@all_date.size - 1) for i in ((@page - 1) * 10)..iend @a << @all_date[i] end @page_select_header_id = @user.id #make state lists for every date d = 0 for date in @a bb = [] #login date cc = [] #logout date dd = [] # duration #let's find starting action for the day start_action = Action.find(:first, :conditions => ["user_id = ? AND SUBSTRING(date,1,10) < ?", @user.id, date.strftime("%Y-%m-%d")], :order => "date DESC") other_actions = Action.find(:all, :conditions => ["user_id = ? AND SUBSTRING(date,1,10) = ?", @user.id, date.strftime("%Y-%m-%d")], :order => "date ASC") #form array for actions actions = [] actions << start_action if start_action for oa in other_actions actions << oa end #compress array removing spare logins/logouts pa = 0 #previous action to compare #if actions.size > 0 for i in 1..actions.size-1 if actions[i].action == actions[pa].action #and actions[i] != actions.last actions[i] = nil else pa = i end i+=1 end actions.compact! #build array from data if actions.size > 0 #fix if we do not have data if actions.size == 1 #all day same state if actions[0].action == "login" bb << date cc << date+1.day-1.second dd << (date+1.day - date) end else #we have some state change during day i = 1 i = 0 if actions[0].action == "login" (actions.size/2).times do #login if actions[i].date.day == date.day lin = actions[i].date else lin = date end #logout if actions[i+1] #we have logout lout = actions[i+1].date else #no logout, login end - end of day lout = date+1.day-1.second end bb << lin cc << lout dd << lout - lin i+=2 end end end @b << bb @c << cc @d << dd hours = Hash.new i=0 12.times do hours[(i*8)] = (i*2).to_s i+=1 end #hours = {0 => "0", 2=>"2", 4=>"4", 6=>"6", 8=>"8", 10=>"10",12=>"12", 14=>"14", 16=>"16", 18=>"18", 20=>"20", 22=>"22" } #format data array #for i in 0..95 a = [] 96.times do a << 0 end for i in 0..(bb.size-1) x = (bb[i].hour * 60 + bb[i].min) / 15 y = (cc[i].hour * 60 + cc[i].min) / 15 for ii in x..y a[ii] = 1 end # my_debug x # my_debug y end #formating graph for Log States whit flash @Login_graph[d]="" rr = 0 while rr <= 96 db= rr % 8 as= rr/4 if db ==0 @Login_graph[d] += as.to_s + ";" + a[rr].to_s + "\\n" end rr=rr+1 end d+=1 end @days = @MyDay.new(@a, @b, @c, @d) end def new_calls change_date @page_title = _('New_calls') @page_icon = "call.png" @users = User.find(:all, :conditions => "hidden = 0") end =begin in before filter : user (:find_user_from_id_or_session, :authorize_user) =end def new_calls_list @page_title = _('New_calls')+": "+@user.first_name+" "+@user.last_name+" - "+session_from_date @calls = @user.new_calls(session_from_date) @select_date = false render :action => "new_call_list" end =begin in before filter : user (:find_user_from_id_or_session, :authorize_user) =end def call_list_from_link @date_from = params[:date_from] @date_till = params[:date_till].to_s != 'time_now' ? params[:date_till] : Time.now.strftime("%Y-%m-%d %H:%M:%S") @call_type = "all" @call_type = params[:call_type] if params[:call_type] @page_title = _('all_calls') #if @call_type == "all" @page_title = _('answered_calls') if @call_type == "answered" @page_title = _('incoming_calls') if @call_type == "answered_inc" @page_title = _('missed_calls') if @call_type == "missed" @page_title = @page_title + ": " + @user.first_name + " " + @user.last_name @calls = @user.calls(@call_type, @date_from, @date_till) @total_duration = 0 @total_price = 0 @total_billsec = 0 @curr_rate= {} @curr_rate2= {} exrate = Currency.count_exchange_rate(session[:default_currency], session[:show_currency]) for call in @calls @total_duration += call.duration if @direction == "incoming" @rate_cur = Currency.count_exchange_prices({:exrate => exrate, :prices => [call.did_price.to_f]}) if call.did_price @total_price += @rate_cur if call.did_price @curr_rate2[call.id]=@rate_cur @total_billsec += call.did_billsec else @rate_cur = Currency.count_exchange_prices({:exrate => exrate, :prices => [call.user_price.to_f]}) if call.user_price @total_price += @rate_cur if call.user_price @curr_rate[call.id]=@rate_cur @total_billsec += call.nice_billsec end end @show_destination = params[:show_dst] redirect_to :controller => "stats", :action => "call_list", :id => @user.id, :call_type => @call_type, :date_from_link => @date_from, :date_till_link => @date_till, :direction => "outgoing" #and return false end =begin in before filter : user (:find_user_from_id_or_session) =end def last_calls_stats @page_title = _('Last_calls') @page_icon = "call.png" change_date @Show_Currency_Selector=1 @options = last_calls_stats_parse_params if session[:usertype] == "user" unless (@user = current_user) dont_be_so_smart redirect_to :controller => :callc, :action => :main and return false end @devices, @device = last_calls_stats_user(@user, @options) end if session[:usertype] == "reseller" @users, @user, @devices, @device, @hgcs, @hgc, @providers, @provider, @did, @dids = last_calls_stats_reseller(@options) end if ["admin", "accountant"].include?(session[:usertype]) @users, @user, @devices, @device, @hgcs, @hgc, @dids, @did, @providers, @provider, @reseller, @resellers = last_calls_stats_admin(@options) end session[:last_calls_stats] = @options options = last_calls_stats_set_variables(@options, {:user => @user, :device => @device, :hgc => @hgc, :did => @did, :current_user => current_user, :provider => @provider, :can_see_finances => can_see_finances?, :reseller => @reseller}) type = 'html' type = 'csv' if params[:csv].to_i == 1 type = 'pdf' if params[:pdf].to_i == 1 case type when 'html' @total_calls_stats = Call.last_calls_total_stats(options) @total_calls = @total_calls_stats.total_calls.to_i logger.debug " >> Total calls: #{@total_calls}" @total_pages = (@total_calls/ session[:items_per_page].to_f).ceil options[:page] = @total_pages if options[:page].to_i > @total_pages.to_i options[:page] = 1 if options[:page].to_i < 1 @calls = Call.last_calls(options) logger.debug(" >> Calls #{@calls.size}") @show_destination = 1 session[:last_calls_stats] = @options #@calls = [@calls[1]] when 'pdf' options[:column_dem] = '.' options[:current_user] = current_user calls = Call.last_calls_csv(options.merge({:pdf => 1})) total_calls = Call.last_calls_total_stats(options) pdf = PdfGen::Generate.generate_last_calls_pdf(calls, total_calls, current_user, {:direction => '', :date_from => session_from_datetime, :date_till => session_till_datetime, :show_currency => session[:show_currency], :rs_active => rs_active?, :can_see_finances => can_see_finances?}) logger.debug(" >> Calls #{calls.size}") @show_destination = 1 session[:last_calls_stats] = @options if params[:test].to_i == 1 render :text => "OK" else send_data pdf.render, :filename => "Calls_#{session_from_datetime}-#{session_till_datetime}.pdf", :type => "application/pdf" end when 'csv' options[:test] = 1 if params[:test] options[:collumn_separator], options[:column_dem] = current_user.csv_params options[:current_user] = current_user filename = Call.last_calls_csv(options) filename = load_file_through_database(filename) if Confline.get_value("Load_CSV_From_Remote_Mysql").to_i == 1 if filename filename = archive_file_if_size(filename, "csv", Confline.get_value("CSV_File_size").to_f) if params[:test].to_i != 1 send_file(filename) else render :text => filename end else flash[:notice] = _("Cannot_Download_CSV_File_From_DB_Server") redirect_to :controller => :callc, :action => :main and return false end end if !params[:commit].nil? @options[:page] = 1 end end def call_list @page_title = _('Calls') @page_icon = "call.png" @Show_Currency_Selector=1 # (params[:id] and session[:usertype] != "user") ? user_id = params[:id] : user_id = session[:user_id] # # unless (@user=User.find(:first,:include => [:devices], :conditions => ["users.id = ?", user_id])) # flash[:notice] = _('User_not_found') # redirect_to :controller => :callc, :action => :main and return false # end # # owner = correct_owner_id # if @user.owner_id != owner and @user.id != session[:user_id] and session[:user_id].to_i != 0 # dont_be_so_smart # redirect_to :controller => :callc, :action => :main and return false # end # # @user = authorize_user(@user) MorLog.my_debug @user.id @devices = @user.devices params[:device] ? @sel_device_id = params[:device].to_i : @sel_device_id = 0 params[:hgc] ? @sel_hgc_id = params[:hgc].to_i : @sel_hgc_id = 0 params[:direction] ? @direction = params[:direction] : @direction = "outgoing" params[:page] ? @page = params[:page].to_i : @page = 1 @device = Device.find(:first, :conditions => ['id=?', @sel_device_id]) if @sel_device_id.to_i > 0 @hgc =Hangupcausecode.find_by_id(@sel_hgc_id) if @sel_hgc_id > 0 @hgcs = Hangupcausecode.find(:all) @search = 0 if params[:date_from_link] @date_from = params[:date_from_link] @date_till = params[:date_till_link] else change_date @date_from = session_from_datetime @date_till = session_till_datetime end #changing the state of call processed field if params[:processed] if processed_call = Call.find_by_id(params[:processed]) processed_call.processed == 0 ? processed_call.processed = 1 : processed_call.processed = 0 processed_call.save end end @call_type = "all" @call_type = params[:call_type] if params[:call_type] @call_type = params[:calltype] if params[:calltype] @orig_call_type = @call_type @call_type += "_inc" if @direction == "incoming" @calls_per_page = Confline.get_value("Items_Per_Page").to_i @page_select_header_id = @user.id @total_stats = @user.calls_total_stats(@orig_call_type, @date_from, @date_till, @direction, @device, @user.usertype, @hgc) @total_calls = @total_stats.total_calls.to_i @total_pages = (@total_calls.to_f / @calls_per_page.to_f).ceil.to_i start = @page < 1 ? 1 : (@calls_per_page * (@page-1)) @calls_per_page2 = @calls_per_page.to_i < 2 ? 1 : @calls_per_page -1 @calls = @user.calls(@orig_call_type, @date_from, @date_till, @direction, "calldate", "DESC", @device, {:limit => @calls_per_page2, :offset => start, :providers => true, :destinations => true, :hgc => @hgc}) @calls = [] if not @calls @total_duration = @total_stats.total_duration.to_f @total_billsec = @total_stats.total_billsec.to_f # count Totals @total_price = 0 @total_inc_price = 0 @total_price2 = 0 @total_prov_price = 0 @total_profit = 0 exchange_rate = Currency.count_exchange_rate(session[:default_currency], session[:show_currency]).to_f #outgoing if session[:usertype] == "admin" and @user.owner_id != 0 @total_price = @total_stats.total_reseller_price.to_f * exchange_rate else @total_price = @total_stats.total_user_price.to_f * exchange_rate end #incoming @total_inc_prov_price = @total_stats.total_did_prov_price.to_f * exchange_rate @total_inc_price = @total_stats.total_did_inc_price.to_f * exchange_rate @total_price2 = @total_stats.total_did_price.to_f * exchange_rate if session[:usertype] == "admin" @total_prov_price = @total_stats.total_provider_price.to_f * exchange_rate #my_debug @total_prov_price else # provider price for reseller if @user.id == session[:user_id] @total_prov_price = @total_stats.total_user_price.to_f * exchange_rate else @total_prov_price = @total_stats.total_reseller_price.to_f * exchange_rate end end if @direction == "incoming" @total_profit = @total_inc_prov_price.to_f + @total_inc_price.to_f + @total_price2.to_f else @total_profit = @total_price.to_f - @total_prov_price.to_f end # count separate users values @curr_rate = {} @curr_rate2 = {} @prov_rate = {} @curr_prov_rate = {} @curr_prov_rate2= {} @curr_inc_rate = {} for call in @calls if @direction == "incoming" @curr_rate[call.id]= call.user_price * exchange_rate if call.user_price @curr_rate2[call.id] = call.did_price * exchange_rate if call.did_price @curr_prov_rate2[call.id] = call.did_prov_price * exchange_rate if call.did_prov_price @curr_inc_rate[call.id] = call.did_inc_price * exchange_rate if call.did_inc_price else # outgoing calls if session[:usertype] == "admin" @prov_rate[call.id]= call.provider_price if call.provider_price @curr_prov_rate[call.id]= call.provider_price * exchange_rate if call.provider_price if call.reseller_id.to_i == 0 # user price for calls made by admin users are calls.user_price @curr_rate[call.id] = call.user_price * exchange_rate if call.user_price else # user price for calls made by resellers users (for admin) is calls.reseller_price @curr_rate[call.id] = call.reseller_price * exchange_rate if call.reseller_price end else if call.reseller_id.to_i == 0 #provider price for resellers own call = price he gets from admin = user_price, profit for such calls = 0 @prov_rate[call.id] = call.user_price if call.user_price @curr_prov_rate[call.id] = call.user_price * exchange_rate if call.user_price else # provider price for resellers users calls is price reseller buys calls from admin @prov_rate[call.id]= call.reseller_price if call.reseller_price @curr_prov_rate[call.id] = call.reseller_price * exchange_rate if call.reseller_price end @curr_rate[call.id] = call.user_price * exchange_rate if call.user_price end end end @show_destination = 1 @old_call_type = @call_type @call_type = @orig_call_type @search = 1 if params[:search_on] end =begin in before filter : user (:find_user_from_id_or_session, :authorize_user) =end def last_calls redirect_to :action => "last_calls_stats" end def cc_call_list #change_date if params[:id] @card = Card.find_by_id(params[:id], :include => [:cardgroup]) else @card = Card.find_by_id(session[:card_id], :include => [:cardgroup]) end unless @card flash[:notice] = _('Card_was_not_found') redirect_to :controller => :callc, :action => :main and return false end if (@card.user_id != current_user.id) and (current_user.is_not_admin? and @card.is_not_owned_by?(current_user) and @card.cardgroup.is_not_owned_by?(current_user)) flash[:notice] = _('You_are_not_authorized_to_view_this_page') redirect_to :controller => "callc", :action => "main" and return false end @calls = @card.calls @page_title = _('Calls_for_card') + ": #{@card.number}" @total_duration = 0 @total_price = 0 @total_billsec = 0 @total_provider_price = 0 @total_profit = 0 @total_billsec = 0 @total_did_price = 0 for call in @calls @total_duration += call.duration @total_price += call.user_price if call.user_price @total_billsec += call.nice_billsec @total_profit += call.user_price.to_f - call.provider_price.to_f @total_provider_price += call.provider_price if call.provider_price @total_did_price += call.did_price.to_f + call.did_inc_price.to_f end end def country_stats @page_title = _('Country_Stats') @page_icon = "world.png" change_date default_user_id = -1 if params[:csv].to_i == 0 @user_id = params[:user_id] ? params[:user_id].to_i : default_user_id session[:stats_country_stats_options] ||= {} session[:stats_country_stats_options][:user_id] = @user_id @users = User.find_all_for_select(corrected_user_id, {:exclude_owner => true}) @calls, @longest_time, @profit, @incomes, @calls_all = Call.country_stats({:user_id => params[:user_id], :current_user => current_user, :a1 => session_from_datetime, :a2 => session_till_datetime}) else options = session[:stats_country_stats_options] @user_id = (options and options[:user_id]) ? options[:user_id] : default_user_id settings_owner_id = (["reseller", "admin"].include?(session[:usertype]) ? session[:user_id] : session[:owner_id]) filename = Call.country_stats_csv({:collumn_separator => Confline.get_csv_separator(settings_owner_id), :s_user => @user_id, :current_user => current_user, :from => session_from_datetime, :till => session_till_datetime, :nice_number_digits => session[:nice_number_digits], :test => params[:test].to_i, :hide_finances => !can_see_finances?}) filename = load_file_through_database(filename) if Confline.get_value("Load_CSV_From_Remote_Mysql").to_i == 1 if filename filename = archive_file_if_size(filename, "csv", Confline.get_value("CSV_File_size").to_f) if params[:test].to_i != 1 send_file(filename) else render :text => filename end else flash[:notice] = _("Cannot_Download_CSV_File_From_DB_Server") redirect_to :controller => :callc, :action => :main and return false end end end ############ CSV ############### def last_calls_stats_admin redirect_to :action => "last_calls_stats" end =begin in before filter : user (:find_user_from_id_or_session, :authorize_user) =end def call_list_to_csv @direction = "outgoing" @direction = params[:direction] if params[:direction] @sel_device_id = 0 @sel_device_id = params[:device].to_i if params[:device] @device = Device.find(:first, :conditions => ["id = ?", @sel_device_id]) if @sel_device_id > 0 @hgcs = Hangupcausecode.find(:all) @sel_hgc_id = 0 @sel_hgc_id = params[:hgc].to_i if params[:hgc] @hgc =Hangupcausecode.find_by_id(@sel_hgc_id) if @sel_hgc_id > 0 if session[:usertype].to_s != 'admin' and params[:reseller] dont_be_so_smart redirect_to :controller => :callc, :action => :main and return false end res = session[:usertype] == 'admin' ? params[:reseller].to_i : 0 date_from = params[:date_from] ? params[:date_from] : session_from_datetime date_till = params[:date_till] ? params[:date_till] : session_till_datetime call_type = params[:call_type] ? params[:call_type].to_s : 'answered' session[:usertype] == "accountant" ? user_type = "admin" : user_type = session[:usertype] filename = @user.user_calls_to_csv({:tz => current_user.time_zone, :device => @device, :direction => @direction, :call_type => call_type, :date_from => date_from, :date_till => date_till, :default_currency => session[:default_currency], :show_currency => session[:show_currency], :show_full_src => session[:show_full_src], :hgc => @hgc, :usertype => user_type, :nice_number_digits => session[:nice_number_digits], :test => params[:test].to_i, :reseller => res.to_i, :hide_finances => !can_see_finances?}) filename = load_file_through_database(filename) if Confline.get_value("Load_CSV_From_Remote_Mysql").to_i == 1 if filename filename = archive_file_if_size(filename, "csv", Confline.get_value("CSV_File_size").to_f) if params[:test].to_i != 1 send_file(filename) else render :text => filename end else flash[:notice] = _("Cannot_Download_CSV_File_From_DB_Server") redirect_to :controller => :callc, :action => :main and return false end end ############ PDF ############### =begin in before filter : user (:find_user_from_id_or_session, :authorize_user) =end def call_list_to_pdf require 'pdf/wrapper' @direction = "outgoing" @direction = params[:direction] if params[:direction] @sel_device_id = 0 @sel_device_id = params[:device].to_i if params[:device] @device = Device.find(@sel_device_id) if @sel_device_id > 0 @hgcs = Hangupcausecode.find(:all) @sel_hgc_id = 0 @sel_hgc_id = params[:hgc].to_i if params[:hgc] @hgc =Hangupcausecode.find_by_id(@sel_hgc_id) if @sel_hgc_id > 0 date_from = params[:date_from] date_till = params[:date_till] call_type = params[:call_type] #call_type = params[:calltype] if params[:calltype] user = @user calls = user.calls(call_type, date_from, date_till, @direction, "calldate", "DESC", @device, {:hgc => @hgc}) ###### Generate PDF ######## pdf = PDF::Wrapper.new(:paper => :A4) pdf.font("Nimbus Sans L") #logo #pdf.image "../images/logo/kolmisoft.png", :justification => :center, :resize => 0.75 #options options = {} options = options.merge({ :xdelta => 9, :fontsize => 5, :calls_per_page_first => 70, :calls_per_page_second => 80, :ystart => 125, :second_page_start => 50, :header_elevation => 15, :header_add_size => 1, :total_possition => 770, :page_number_size => 10}) if session[:usertype] == "admin" or session[:usertype] == "reseller" options = options.merge({ :dat_x => 30, :caf_x => 105, :cat_x => 180, :dur_x => 255, :han_x => 295, :pri_x => 360, :pri2_x => 395, :pri3_x => 450, :pri4_x => 485, :pri5_x => 525 }) else options = options.merge({ :dat_x => 30, :caf_x => 150, :cat_x => 240, :dur_x => 330, :han_x => 400, :pri_x => 500}) end pdf.text(_('CDR_Records') + ": #{user.first_name} #{user.last_name}", {:font_size => 16, :top => 30, :alignment => :left}) pdf.text(_('Period') + ": " + date_from + " - " + date_till, {:font_size => 10, :top => 55, :alignment => :left}) pdf.text(_('Currency') + ": #{session[:show_currency]}", {:font_size => 8, :top => 84, :alignment => :left}) pdf.text(_('Total_calls') + ": #{calls.size}", {:font_size => 8, :top => 94, :alignment => :left}) options[:total_calls] = calls.size options[:calls_per_page] = options[:calls_per_page_first] options = options.merge({:total_pages => PdfGen::Count.pages(calls.size, options[:calls_per_page], options[:calls_per_page_second])}) #/options total_price = 0 total_billsec = 0 total_prov_price = 0 total_prfit = 0 total_did_provider = 0 total_did_inc = 0 total_did_own = 0 total_did_prof = 0 i = 1 page_calls = 1 #table header pdf = PdfGen::Generate.call_list_to_pdf_header(pdf, @direction, session[:usertype], i, options) #/table header #page exrate = Currency.count_exchange_rate(session[:default_currency], session[:show_currency]) for call in calls @rate_cur3 = Currency.count_exchange_prices({:exrate => exrate, :prices => [call.user_price.to_f]}) @rate_prov = Currency.count_exchange_prices({:exrate => exrate, :prices => [call.provider_price.to_f]}) if session[:usertype] == "admin" if session[:usertype] == "reseller" if call.reseller_id == 0 # selfcost for reseller himself is user price, so profit always = 0 for his own calls @rate_prov = Currency.count_exchange_prices({:exrate => exrate, :prices => [call.user_price.to_f]}) else @rate_prov = Currency.count_exchange_prices({:exrate => exrate, :prices => [call.reseller_price.to_f]}) end end @rate_did_pr = Currency.count_exchange_prices({:exrate => exrate, :prices => [call.did_prov_price.to_f]}) @rate_did_ic = Currency.count_exchange_prices({:exrate => exrate, :prices => [call.did_inc_price.to_f]}) @rate_did_ow = Currency.count_exchange_prices({:exrate => exrate, :prices => [call.did_price.to_f]}) pdf.text(call.calldate.strftime("%Y-%m-%d %H:%M:%S"), {:left => options[:dat_x], :top => options[:ystart]+(page_calls % options[:calls_per_page] * options[:xdelta]), :font_size => options[:fontsize]}) pdf.text(call.src, {:left => options[:caf_x], :top => options[:ystart]+(page_calls % options[:calls_per_page] * options[:xdelta]), :font_size => options[:fontsize]}) pdf.text(hide_dst_for_user(current_user, "pdf", call.dst.to_s), {:left => options[:cat_x], :top => options[:ystart]+(page_calls % options[:calls_per_page] * options[:xdelta]), :font_size => options[:fontsize]}) if @direction == "incoming" billsec = call.did_billsec else billsec = call.nice_billsec end pdf.text(nice_time(billsec), {:left => options[:dur_x], :top => options[:ystart]+(page_calls % options[:calls_per_page] * options[:xdelta]), :font_size => options[:fontsize]}) pdf.text(call.disposition, {:left => options[:han_x], :top => options[:ystart]+(page_calls % options[:calls_per_page] * options[:xdelta]), :font_size => options[:fontsize]}) if session[:usertype] == "admin" if @direction == "incoming" pdf.text(nice_number(@rate_did_pr), {:left => options[:pri_x], :top => options[:ystart]+(page_calls % options[:calls_per_page] * options[:xdelta]), :font_size => options[:fontsize]}) pdf.text(nice_number(@rate_did_ic), {:left => options[:pri2_x], :top => options[:ystart]+(page_calls % options[:calls_per_page] * options[:xdelta]), :font_size => options[:fontsize]}) pdf.text(nice_number(@rate_did_ow), {:left => options[:pri3_x], :top => options[:ystart]+(page_calls % options[:calls_per_page] * options[:xdelta]), :font_size => options[:fontsize]}) pdf.text(nice_number(@rate_did_pr + @rate_did_ic + @rate_did_ow), {:left => options[:pri4_x], :top => options[:ystart]+(page_calls % options[:calls_per_page] * options[:xdelta]), :font_size => options[:fontsize]}) else pdf.text(nice_number(@rate_cur3), {:left => options[:pri_x], :top => options[:ystart]+(page_calls % options[:calls_per_page] * options[:xdelta]), :font_size => options[:fontsize]}) pdf.text(nice_number(@rate_prov), {:left => options[:pri2_x], :top => options[:ystart]+(page_calls % options[:calls_per_page] * options[:xdelta]), :font_size => options[:fontsize]}) pdf.text(nice_number(@rate_cur3.to_f - @rate_prov.to_f), {:left => options[:pri3_x], :top => options[:ystart]+(page_calls % options[:calls_per_page] * options[:xdelta]), :font_size => options[:fontsize]}) pdf.text(nice_number(@rate_cur3.to_f != 0.to_f ? ((@rate_cur3.to_f - @rate_prov.to_f)/ @rate_cur3.to_f) *100 : 0) + " %", {:left => options[:pri4_x], :top => options[:ystart]+(page_calls % options[:calls_per_page] * options[:xdelta]), :font_size => options[:fontsize]}) if @rate_cur3.to_f != 0 pdf.text(nice_number(@rate_prov.to_f != 0.to_f ? ((@rate_cur3.to_f / @rate_prov.to_f) *100)-100 : 0) + " %", {:left => options[:pri5_x], :top => options[:ystart]+(page_calls % options[:calls_per_page] * options[:xdelta]), :font_size => options[:fontsize]}) if @rate_prov.to_f != 0 end end if session[:usertype] == "reseller" if @direction == "incoming" # pdf.text(nice_number(@rate_did_pr), {:left=> options[:pri_x], :top=> options[:ystart]+(page_calls % options[:calls_per_page] * options[:xdelta]), :font_size =>options[:fontsize]}) # pdf.text(nice_number(@rate_did_ic), {:left=> options[:pri2_x], :top=> options[:ystart]+(page_calls % options[:calls_per_page] * options[:xdelta]), :font_size =>options[:fontsize]}) pdf.text(nice_number(@rate_did_ow), {:left => options[:pri_x], :top => options[:ystart]+(page_calls % options[:calls_per_page] * options[:xdelta]), :font_size => options[:fontsize]}) # pdf.text(nice_number(@rate_did_pr + @rate_did_ic + @rate_did_ow ),{:left=> options[:pri4_x], :top=> options[:ystart]+(page_calls % options[:calls_per_page] * options[:xdelta]), :font_size =>options[:fontsize]}) else pdf.text(nice_number(@rate_cur3), {:left => options[:pri_x], :top => options[:ystart]+(page_calls % options[:calls_per_page] * options[:xdelta]), :font_size => options[:fontsize]}) pdf.text(nice_number(@rate_prov), {:left => options[:pri2_x], :top => options[:ystart]+(page_calls % options[:calls_per_page] * options[:xdelta]), :font_size => options[:fontsize]}) pdf.text(nice_number(@rate_cur3.to_f - @rate_prov.to_f), {:left => options[:pri3_x], :top => options[:ystart]+(page_calls % options[:calls_per_page] * options[:xdelta]), :font_size => options[:fontsize]}) pdf.text(nice_number(@rate_cur3.to_f != 0.to_f ? ((@rate_cur3.to_f - @rate_prov.to_f)/ @rate_cur3.to_f) *100 : 0) + " %", {:left => options[:pri4_x], :top => options[:ystart]+(page_calls % options[:calls_per_page] * options[:xdelta]), :font_size => options[:fontsize]}) if @rate_cur3.to_f != 0 pdf.text(nice_number(@rate_prov.to_f != 0.to_f ? ((@rate_cur3.to_f / @rate_prov.to_f) *100)-100 : 0) + " %", {:left => options[:pri5_x], :top => options[:ystart]+(page_calls % options[:calls_per_page] * options[:xdelta]), :font_size => options[:fontsize]}) if @rate_prov.to_f != 0 end end if session[:usertype] == "user" or session[:usertype] == "accountant" if @direction != "incoming" pdf.text(nice_number(@rate_cur3), {:left => options[:pri_x], :top => options[:ystart]+(page_calls % options[:calls_per_page] * options[:xdelta]), :font_size => options[:fontsize]}) else pdf.text(nice_number(@rate_did_ow), {:left => options[:pri_x], :top => options[:ystart]+(page_calls % options[:calls_per_page] * options[:xdelta]), :font_size => options[:fontsize]}) end end if page_calls == options[:calls_per_page] and i != options[:total_calls] options[:ystart] = options[:second_page_start] page_calls = 0 pdf.start_new_page pdf = PdfGen::Generate.call_list_to_pdf_header(pdf, @direction, session[:usertype], i, options) options[:calls_per_page] = options[:calls_per_page_second] end if @direction == "incoming" total_price += @rate_did_ow else total_price += @rate_cur3 if call.user_price end #total_price += @rate_cur3 if call.user_price total_prov_price += @rate_prov.to_f total_prfit += @rate_cur3.to_f - @rate_prov.to_f total_billsec += call.nice_billsec total_did_provider += @rate_did_pr total_did_inc += @rate_did_ic total_did_own += @rate_did_ow total_did_prof += @rate_did_pr.to_f + @rate_did_ic.to_f + @rate_did_ow.to_f page_calls += 1 i += 1 end #Totals pdf.text(_('Total'), {:left => 40, :top => options[:total_possition], :font_size => options[:fontsize] + options[:header_add_size]}) pdf.text(nice_time(total_billsec), {:left => options[:dur_x], :top => options[:total_possition], :font_size => options[:fontsize] + options[:header_add_size]}) if session[:usertype] == "admin" or session[:usertype] == "reseller" if @direction == "incoming" pdf.text(nice_number(total_did_provider), {:left => options[:pri_x], :top => options[:total_possition], :font_size => options[:fontsize] + options[:header_add_size]}) pdf.text(nice_number(total_did_inc), {:left => options[:pri2_x], :top => options[:total_possition], :font_size => options[:fontsize] + options[:header_add_size]}) pdf.text(nice_number(total_did_own), {:left => options[:pri3_x], :top => options[:total_possition], :font_size => options[:fontsize] + options[:header_add_size]}) pdf.text(nice_number(total_did_prof), {:left => options[:pri4_x], :top => options[:total_possition], :font_size => options[:fontsize] + options[:header_add_size]}) else pdf.text(nice_number(total_price), {:left => options[:pri_x], :top => options[:total_possition], :font_size => options[:fontsize] + options[:header_add_size]}) pdf.text(nice_number(total_prov_price), {:left => options[:pri2_x], :top => options[:total_possition], :font_size => options[:fontsize] + options[:header_add_size]}) pdf.text(nice_number(total_prfit), {:left => options[:pri3_x], :top => options[:total_possition], :font_size => options[:fontsize] + options[:header_add_size]}) if total_price.to_f != 0 pdf.text(nice_number(total_price.to_f != 0.to_f ? (total_prfit / total_price.to_f) * 100 : 0), {:left => options[:pri4_x], :top => options[:total_possition], :font_size => options[:fontsize] + options[:header_add_size]}) else pdf.text(nice_number(0), {:left => options[:pri4_x], :top => options[:total_possition], :font_size => options[:fontsize] + options[:header_add_size]}) end if total_prov_price.to_f != 0 pdf.text(nice_number(total_prov_price.to_f != 0 ? ((total_price.to_f / total_prov_price.to_f) *100)-100 : 0) + " %", {:left => options[:pri5_x], :top => options[:total_possition], :font_size => options[:fontsize] + options[:header_add_size]}) else pdf.text(nice_number(0) + " %", {:left => options[:pri5_x], :top => options[:total_possition], :font_size => options[:fontsize] + options[:header_add_size]}) end end else if @direction != "incoming" pdf.text(nice_number(total_price), {:left => options[:pri_x], :top => options[:total_possition], :font_size => options[:fontsize] + options[:header_add_size]}) end end send_data pdf.render, :filename => "Calls_#{user.first_name}_#{user.last_name}_#{date_from}-#{date_till}.pdf", :type => "application/pdf" end def users_finances default = {:page => "1", :s_completed => '', :s_username => "", :s_fname => "", :s_lname => "", :s_balance_min => "", :s_balance_max => "", :s_type => ""} @page_title = _('Users_finances') @page_icon = "money.png" @options = ((params[:clear] || !session[:users_finances_options]) ? default : session[:users_finances_options]) default.each { |key, value| @options[key] = params[key] if params[key] } owner_id = (session[:usertype] == "accountant" ? 0 : session[:user_id]) cond = ['users.hidden = ?', 'users.owner_id = ?'] var = [0, owner_id] if ['postpaid', 'prepaid'].include?(@options[:s_type]) cond << 'users.postpaid = ?' var << (@options[:s_type] == "postpaid" ? 1 : 0) end add_contition_and_param(@options[:s_username], @options[:s_username] + '%', "users.username LIKE ?", cond, var) add_contition_and_param(@options[:s_fname], @options[:s_fname] + '%', "users.first_name LIKE ?", cond, var) add_contition_and_param(@options[:s_lname], @options[:s_lname] + '%', "users.last_name LIKE ?", cond, var) add_contition_and_param(@options[:s_balance_min], @options[:s_balance_min].to_f, "users.balance >= ?", cond, var) add_contition_and_param(@options[:s_balance_max], @options[:s_balance_max].to_f, "users.balance <= ?", cond, var) @total_users = User.count(:all, :conditions => [cond.join(' AND ').to_s] + var).to_i items_per_page, total_pages = item_pages(@total_users) page_no = valid_page_number(@options[:page], total_pages) offset, limit = query_limit(total_pages, items_per_page, page_no) @total_pages = total_pages @options[:page] = page_no @users = User.find(:all, :conditions => [cond.join(' AND ').to_s] + var, :limit => limit, :offset => offset) cond.size.to_i > 2 ? @search = 1 : @search = 0 @total_balance = @total_credit = @total_payments = @total_amount =0 @amounts = [] @payment_size = [] hide_uncompleted_payment = Confline.get_value("Hide_non_completed_payments_for_user", current_user.id).to_i @users.each_with_index { |user, i| payments = user.payments amount = 0 pz = 0 payments.each { |p| if hide_uncompleted_payment == 0 or (hide_uncompleted_payment == 1 and (p.pending_reason != 'Unnotified payment' or p.pending_reason.blank?)) if p.completed.to_i == @options[:s_completed].to_i or @options[:s_completed].blank? pz += 1 pa = p.payment_amount amount += get_price_exchange(pa, p.currency) end end } @amounts[user.id] = amount @payment_size[user.id] = pz @total_balance += user.balance @total_credit += user.credit if user.credit != (-1) and user.postpaid.to_i != 0 @total_payments += pz @total_amount += amount } session[:users_finances_options] = @options end def providers change_date @Show_Currency_Selector=1 @page_title = _('Providers_stats') @s_prefix = params[:search].to_s.strip unless @s_prefix.blank? @dest = Destination.find( :first, :conditions => "prefix LIKE '#{@s_prefix}'", :order => "LENGTH(destinations.prefix) DESC" ) @flag = nil if @dest == nil @results = "" else @flag = @dest.direction_code direction = @dest.direction @results = @dest.subcode.to_s+" "+@dest.name.to_s @results = direction.name.to_s+" "+ @results if direction end end @providers = Provider.find_all_with_calls_for_stats(current_user, {:date_from => session_from_datetime, :date_till => session_till_datetime, :s_prefix => @s_prefix, :show_currency => session[:show_currency], :default_currency => session[:default_currency]}) end def providers_stats @page_title = _('Providers_stats') @page_icon = "chart_pie.png" if params[:id].to_s == "" flash[:notice] = _("Provider_not_found") redirect_to :controller => "callc", :action => 'main' and return false end change_date @s_prefix = "" @s_prefix = params[:search] if params[:search] cond ="" if @s_prefix.to_s != "" cond += " AND calls.prefix = '#{@s_prefix}' " @dest = Destination.find( :first, :conditions => "prefix = SUBSTRING('#{@s_prefix}', 1, LENGTH(destinations.prefix))", :order => "LENGTH(destinations.prefix) DESC" ) @flag = nil if @dest == nil @results = "" else @flag = @dest.direction_code direction = @dest.direction @results = @dest.subcode.to_s+" "+@dest.name.to_s @results = direction.name.to_s+" "+ @results if direction end end @provider = Provider.find_all_with_calls_for_stats(current_user, {:date_from => session_from_datetime, :date_till => session_till_datetime, :s_prefix => @s_prefix, :p_id => params[:id]})[0] if @provider @asr_calls = nice_number((@provider.answered.to_f / @provider.pcalls.to_f) * 100) @no_answer_calls_pr = nice_number((@provider.no_answer.to_f / @provider.pcalls.to_f) * 100) @busy_calls_pr = nice_number((@provider.busy.to_f / @provider.pcalls.to_f) * 100) @failed_calls_pr = nice_number((@provider.failed.to_f / @provider.pcalls.to_f) * 100) @sdate = Time.mktime(session[:year_from], session[:month_from], session[:day_from]) year, month, day = last_day_month('till') @edate = Time.mktime(year, month, day) @a_date = [] @a_calls = [] @a_billsec = [] @a_avg_billsec = [] @a_calls2 = [] @a_ars = [] @a_ars2 = [] @_billsec2=[] @a_user_price2 = [] @a_provider_price2 = [] @t_calls = 0 @t_billsec = 0 @t_avg_billsec = 0 @t_normative = 0 @t_norm_days = 0 @t_avg_normative = 0 i = 0 cond += current_user.is_reseller? ? " AND (calls.reseller_id = #{current_user.id} OR calls.user_id = #{current_user.id} )" : '' s = [] if current_user.is_admin? s << "SUM(provider_price) as 'selfcost_price'" s << "SUM(IF(reseller_id > 0, reseller_price, user_price)) AS 'sel_price'" s << "SUM(IF(reseller_id > 0, reseller_price, user_price) - provider_price ) AS 'profit'" else s << "SUM(IF(providers.common_use = 1, reseller_price,provider_price)) as 'selfcost_price'" s << "SUM(user_price) AS 'sel_price'" s << "SUM(user_price - IF(providers.common_use = 1, reseller_price,provider_price)) AS 'profit'" end while @sdate < @edate @a_date[i] = @sdate.strftime("%Y-%m-%d") @a_calls[i] = 0 @a_billsec[i] = 0 @a_calls2[i] = 0 @a_user_price =0 @a_provider_price=0 @a_user_price2[i] =0 @a_provider_price2[i]=0 @_billsec2[i]=0 sql ="SELECT COUNT(calls.id) as \'calls\', SUM(IF(calls.billsec > 0, calls.billsec, CEIL(calls.real_billsec) )) as \'billsec\' FROM calls WHERE ((calls.provider_id = '#{params[:id]}' and calls.callertype = 'Local') OR (calls.did_provider_id = '#{params[:id]}' and calls.callertype = 'Outside'))" + "AND calls.calldate BETWEEN '#{@a_date[i]} 00:00:00' AND '#{@a_date[i]} 23:23:59'" + "AND disposition = 'ANSWERED' #{cond}" res = ActiveRecord::Base.connection.select_all(sql) @a_calls[i] = res[0]["calls"].to_i @a_billsec[i] = res[0]["billsec"].to_i if @a_billsec[i] != 0 @a_profit=0 @a_user_price=0 @a_user_price2[i]=0 @a_provider_price=0 @a_provider_price2[i]=0 @calls3 = Call.find(:all, :select => s.join(' , '), :joins => "LEFT JOIN providers ON (providers.id = calls.provider_id)", :conditions => "((calls.provider_id = '#{params[:id]}' and calls.callertype = 'Local') OR (calls.did_provider_id = '#{params[:id]}' and calls.callertype = 'Outside')) AND disposition = 'ANSWERED' " + "AND calldate BETWEEN '#{@a_date[i]} 00:00:00' AND '#{@a_date[i]} 23:23:59' #{cond}", :group => "calls.provider_id", :order => "calldate DESC") @a_user_price2[i]= nice_number @calls3[0].sel_price @a_provider_price2[i]= nice_number @calls3[0].selfcost_price end @a_avg_billsec[i] = 0 @a_avg_billsec[i] = @a_billsec[i] / @a_calls[i] if @a_calls[i] > 0 @t_calls += @a_calls[i] @t_billsec += @a_billsec[i] sqll ="SELECT COUNT(calls.id) as \'calls2\' FROM calls WHERE ((calls.provider_id = '#{params[:id]}' and calls.callertype = 'Local') OR (calls.did_provider_id = '#{params[:id]}' and calls.callertype = 'Outside'))" + "AND calls.calldate BETWEEN '#{@a_date[i]} 00:00:00' AND '#{@a_date[i]} 23:23:59' #{cond}" res2 = ActiveRecord::Base.connection.select_all(sqll) @a_calls2[i] = res2[0]["calls2"].to_i @a_ars2[i] = (@a_calls[i].to_f / @a_calls2[i]) * 100 if @a_calls[i] > 0 @a_ars[i] = nice_number @a_ars2[i] @sdate += (60 * 60 * 24) i+=1 end index = i @t_avg_billsec = @t_billsec / @t_calls if @t_calls > 0 #===== Graph ===================== @Calls_graph = "\"" if @provider.pcalls.to_i > 0 @Calls_graph += _('ANSWERED') +";" +@provider.answered.to_s + ";" + "false" + "\\n" @Calls_graph += _('NO_ANSWER') +";" +@provider.no_answer.to_s + ";" + "false" + "\\n" @Calls_graph += _('BUSY') +";" +@provider.busy.to_s+ ";" + "false" + "\\n" @Calls_graph += _('FAILED') +";" +@provider.failed.to_s + ";" + "false" + "\\n" @Calls_graph += "\"" else @Calls_graph = "\"No result" + ";" + "1" + ";" + "false" + "\\n\"" end #formating graph for Avg.Calltime ine=0 @Avg_Calltime_graph ="" while ine <= index - 1 @Avg_Calltime_graph +=nice_date(@a_date[ine].to_s) + ";" + @a_avg_billsec[ine].to_s + "\\n" ine=ine +1 end #formating graph for Asr calls ine=0 @Asr_graph ="" while ine <= index - 1 @Asr_graph +=nice_date(@a_date[ine].to_s) + ";" + @a_ars[ine].to_s + "\\n" ine=ine +1 end #formating graph for Profit calls ine=0 @Profit_graph ="" while ine <= index - 1 @Profit_graph +=nice_date(@a_date[ine].to_s) + ";" + @a_user_price2[ine].to_s + ";"+@a_provider_price2[ine].to_s + "\\n" ine=ine +1 end end end def hangup_calls @page_title = _('Hang_up_cause_codes_calls') @page_icon = "call.png" change_date cond="" des = '' descond='' @prov =-1 @coun = -1 @direction = -1 @s_provider = -1 @user_id = -1 @user_id = params[:s_user].to_i if params[:s_user] if @user_id.to_i != -1 cond+= "calls.user_id = #{@user_id} AND " @user = User.find_by_id(@user_id) else @user = nil end @device_id = -1 @device_id = params[:s_device].to_i if params[:s_device] if @device_id.to_i != -1 @device_id = params[:s_device].to_i cond+= " (calls.src_device_id = #{@device_id} OR calls.dst_device_id = #{@device_id}) AND " end if params[:provider_id] if params[:provider_id].to_i != -1 @provider = Provider.find(params[:provider_id]) cond +=" ((calls.provider_id = '#{params[:provider_id]}' and calls.callertype = 'Local') OR (calls.did_provider_id = '#{params[:provider_id]}' and calls.callertype = 'Outside')) AND " @prov = @provider.id @s_provider = @prov end end @providers = Provider.find(:all, :conditions => ['hidden=?', 0], :order => 'name ASC') @users = User.find_all_for_select(corrected_user_id) if params[:direction] if params[:direction].to_i != -1 @country = Direction.find(params[:direction]) @coun = @country.id @direction = @coun des+= 'destinations, ' descond +=" AND calls.prefix = destinations.prefix AND destinations.direction_code ='#{@country.code}' " end end @countries = Direction.find(:all, :order => "name ASC") if params[:hid] == nil and !session[:hangup_call] flash[:notice] = _('Hangupcausecode_not_found') redirect_to :action => :hangup_cause_codes_stats and return false end if params[:hid] != nil #@hangup = get_hangup_cause_message(params[:hid]) @hangup = Hangupcausecode.find_by_id(params[:hid].to_i) else #@hangup = get_hangup_cause_message(session[:hangup_call]) @hangup = Hangupcausecode.find_by_id(session[:hangup_call].to_i) end unless @hangup flash[:notice] = _('Hangupcausecode_not_found') redirect_to :action => :hangup_cause_codes_stats and return false end @total_duration = 0 @page = 1 @page = params[:page].to_i if params[:page] sql ="SELECT calls.* FROM #{des} calls WHERE #{cond} calls.calldate BETWEEN '#{session_from_date} 00:00:00' AND '#{session_till_date} 23:23:59' #{descond} AND calls.hangupcause = '#{(@hangup.code).to_s}' ORDER BY calldate DESC" @calls = Call.find_by_sql(sql) @size = @calls.size.to_i session[:hangup_call]= @hangup.id @total_pages = (@calls.size.to_f / session[:items_per_page].to_f).ceil @all_calls = @calls @calls = [] iend = ((session[:items_per_page] * @page) - 1) iend = @all_calls.size - 1 if iend > (@all_calls.size - 1) for i in ((@page - 1) * session[:items_per_page])..iend @calls << @all_calls[i] end for call in @calls @total_duration += call.duration.to_i end end def hangup_calls_to_csv cond="" des = '' descond='' if params[:provider_id].to_i != -1 cond +=" ((calls.provider_id = '#{params[:provider_id]}' and calls.callertype = 'Local') OR (calls.did_provider_id = '#{params[:provider_id]}' and calls.callertype = 'Outside')) AND " end @user_id = -1 @user_id = params[:s_user].to_i if params[:s_user] if @user_id.to_i != -1 cond+= "calls.user_id = #{@user_id} AND " @user = User.find_by_id(@user_id) else @user = nil end @device_id = -1 @device_id = params[:device_id].to_i if params[:device_id] if @device_id.to_i != -1 @device_id = params[:device_id].to_i cond+= " (calls.src_device_id = #{@device_id} OR calls.dst_device_id = #{@device_id}) AND " end if params[:direction] if params[:direction].to_i != -1 @country = Direction.find(params[:direction]) @coun = @country.id @direction = @coun des+= 'destinations, ' descond +=" AND calls.prefix = destinations.prefix AND destinations.direction_code ='#{@country.code}' " end end sql ="SELECT calls.*, directions.name as dname, destinations.name, destinations.subcode FROM #{des} calls left join destinations on (destinations.prefix = calls.prefix) left Join directions on (destinations.direction_code = directions.code) WHERE #{cond} calls.calldate BETWEEN '#{session_from_date} 00:00:00' AND '#{session_till_date} 23:23:59' #{descond} AND calls.hangupcause = '#{(params[:code]).to_s}' ORDER BY calldate DESC" # MorLog.my_debug sql sep = Confline.get_value("CSV_Separator", 0).to_s dec = Confline.get_value("CSV_Decimal", 0).to_s csv_string = "#{_('date')}#{sep}#{ _('called_from')}#{sep}#{_('called_to')}#{sep}#{_('Destination')}#{sep}#{_('User')}#{sep}#{_('duration')}#{sep}#{_('hangup_cause')}%#{sep}#{_('Provider')}\n" #@total_duration = 0.to_i @calls = Call.find_by_sql(sql) for call in @calls dname="" name="" subcode ="" pname="" n_user="" if call.dname dname =call.dname end if call.subcode subcode =call.subcode end if call.name name=call.name end if call.provider pname = call.provider.name end if call.user n_user = nice_user call.user end csv_string += "#{nice_date_time(call.calldate)}#{sep}#{call.clid}#{sep}#{call.localized_dst}#{sep}#{dname.to_s + " "+subcode.to_s + " "+name.to_s}#{sep}#{n_user}#{sep}#{nice_time call.duration}#{sep}#{call.disposition}#{sep}#{pname.to_s}\n" # @total_duration += call.duration.to_i end #csv_string += "#{_('Total')}#{sep}#{sep}#{sep}#{sep}#{sep}#{@total_duration.to_s.gsub(".", dec).to_s}#{sep}#{sep}\n" filename = "Hangup-#{params[:code]}--#{session_from_date}_00:00:00--#{session_till_date}_00:00:00.csv" if params[:test].to_i == 1 render :text => csv_string else send_data(csv_string, :type => 'text/csv; charset=utf-8; header=present', :filename => filename) end end def max_hangup(max, max1) @max2 = max1 for r in @res if r != nil if r['calls'] != nil if r['calls'].to_i < max.to_i and r["calls"].to_i > @max2.to_i if r['calls'] != max @max2 = r['calls'].to_i @code = r["code"] end end end end end end def loss_making_calls @page_title = _('Loss_making_calls') @page_icon = "money_delete.png" change_date condition = "" if params[:reseller_id] if params[:reseller_id].to_i != -1 @reseller = User.find(:first, :conditions => ["id = ?", params[:reseller_id]]) condition =" AND calls.reseller_id = '#{@reseller.id}' " @reseller_id = @reseller.id else condition = "" end end @resellers = User.find(:all, :conditions => 'usertype = "reseller"', :order => 'first_name ASC') @calls = Call.find(:all, :include => [:user, :provider, :device], :conditions => "provider_price > user_price AND calldate BETWEEN \'" + session_from_date + " 00:00:00\' AND \'" + session_till_date + " 23:59:59\' AND disposition = \'ANSWERED\'"+ condition, :order => "calldate DESC") @total_calls = Call.find(:all, :select => 'COUNT(*), SUM(IF((billsec IS NULL OR billsec = 0), IF(real_billsec IS NULL, 0, real_billsec), billsec)) AS total_duration, SUM(provider_price-user_price) AS total_loss', :conditions => 'provider_price > user_price AND calldate BETWEEN \'' + session_from_date + ' 00:00:00\' AND \'' + session_till_date + ' 23:59:59\' AND disposition = \'ANSWERED\''+ condition) end def profit @page_title = _('Profit') @page_icon = "money.png" change_date @sub_vat = 0 @sub_price =0 owner = correct_owner_id @users = User.find_all_for_select(corrected_user_id) up, rp, pp = current_user.get_price_calculation_sqls params[:user_id] ? @user_id =params[:user_id].to_i : @user_id = -1 conditions = [] user_sql2 = "" if session[:usertype] == "reseller" conditions << "calls.reseller_id = #{session[:user_id].to_i}" if params[:user_id] and params[:user_id] != "-1" conditions << "calls.user_id = '#{params[:user_id].to_i}'" #user_sql2 = " AND subscriptions.user_id = '#{@user_id}' " end else if params[:user_id] and params[:user_id] != "-1" conditions << "calls.user_id IN (SELECT id FROM users WHERE id = '#{params[:user_id].to_i}' OR owner_id = #{params[:user_id].to_i})" user_sql2 = " AND subscriptions.user_id = '#{@user_id}' " end end conditions << "calls.calldate BETWEEN '#{session_from_date} 00:00:00' AND '#{session_till_date} 23:59:59'" select = ["SUM(IF(calls.billsec > 0, calls.billsec, CEIL(calls.real_billsec) )) AS 'billsec'"] if session[:usertype] == "reseller" select += [SqlExport.replace_price("SUM(#{up})", {:reference => 'user_price'}), SqlExport.replace_price("SUM(#{pp})", {:reference => 'provider_price'})] conditions << "calls.reseller_id = #{session[:user_id].to_i}" else select += [SqlExport.replace_price("SUM(#{up})", {:reference => 'user_price'}), SqlExport.replace_price("SUM(#{pp})", {:reference => 'provider_price'})] end total = Call.find(:all, :select => select.join(", "), :joins => "LEFT JOIN users ON (users.id = calls.user_id) #{ SqlExport.left_join_reseler_providers_to_calls_sql}", :conditions => (conditions +["disposition = 'ANSWERED'"]).join(" AND "))[0] @total_duration = (total["billsec"]).to_i @total_call_price = (total["user_price"]).to_f @total_call_selfprice = (total["provider_price"]).to_f select_total = ["COUNT(*) AS 'total_calls'"] select_total << "SUM(IF(calls.disposition = 'ANSWERED', 1, 0)) AS 'answered_calls'" select_total << "SUM(IF(calls.disposition = 'BUSY', 1, 0)) AS 'busy_calls'" select_total << "SUM(IF(calls.disposition = 'NO ANSWER', 1, 0)) AS 'no_answer_calls'" select_total << "SUM(IF(calls.disposition = 'FAILED', 1, 0)) AS 'failed_calls'" total = Call.find(:all, :select => select_total.join(", "), :conditions => conditions.join(" AND "), :joins => SqlExport.left_join_reseler_providers_to_calls_sql) if total and total[0] and total[0]["total_calls"].to_i != 0 @total_calls = total[0]["total_calls"].to_i @total_answered_calls = total[0]["answered_calls"].to_i @total_not_ans_calls = total[0]["no_answer_calls"].to_i @total_busy_calls = total[0]["busy_calls"].to_i @total_error_calls = total[0]["failed_calls"].to_i if @total_calls != 0 @total_answer_percent = @total_answered_calls.to_f * 100 / @total_calls.to_f @average_call_duration = @total_duration.to_f / @total_answered_calls.to_f @total_not_ans_percent = @total_not_ans_calls.to_f * 100 / @total_calls.to_f @total_busy_percent = @total_busy_calls.to_f * 100 / @total_calls.to_f @total_error_percent = @total_error_calls.to_f * 100 / @total_calls.to_f else @total_answer_percent = 0 @average_call_duration = 0 @total_not_ans_percent = 0 @total_busy_percent = 0 @total_error_percent = 0 end else @total_calls = @total_answered_calls = 0 @total_answer_percent = @total_not_ans_percent = @total_busy_percent = @total_error_percent = 0 @average_call_duration = @total_not_ans_calls = @total_busy_calls = @total_error_calls = 0 end @total_profit = @total_call_price - @total_call_selfprice if @total_call_price != 0 && @total_answered_calls != 0 select = [""] if session[:usertype] == "reseller" res = Call.find(:all, :select => "#{SqlExport.replace_price("SUM(#{up})", {:reference => 'price'})}, SUM(IF(calls.billsec > 0, calls.billsec, CEIL(calls.real_billsec) )) AS 'duration', COUNT(DISTINCT(calls.user_id)) AS 'users'", :joins => "LEFT JOIN users ON (users.id = calls.user_id) #{ SqlExport.left_join_reseler_providers_to_calls_sql}", :conditions => (conditions + ["calls.disposition = 'ANSWERED'"]).join(" AND ")) else res = Call.find(:all, :select => "#{SqlExport.replace_price("SUM(#{up})", {:reference => 'price'})}, SUM(IF(calls.billsec > 0, calls.billsec, CEIL(calls.real_billsec) )) AS 'duration', COUNT(DISTINCT(calls.user_id)) AS 'users'", :joins => "LEFT JOIN users ON (users.id = calls.user_id) #{ SqlExport.left_join_reseler_providers_to_calls_sql}", :conditions => (conditions + ["calls.disposition = 'ANSWERED'"]).join(" AND ")) end if session[:usertype] == "reseller" resu = Call.find(:all, :select => "COUNT(DISTINCT(calls.user_id)) AS 'users'", :joins => "LEFT JOIN users ON (users.id = calls.user_id) #{ SqlExport.left_join_reseler_providers_to_calls_sql}", :conditions => (conditions + ["calls.disposition = 'ANSWERED' and card_id < 1"]).join(" AND ")) else resu = Call.find(:all, :select => "COUNT(DISTINCT(calls.user_id)) AS 'users'", :joins => "LEFT JOIN users ON (users.id = calls.user_id) #{ SqlExport.left_join_reseler_providers_to_calls_sql}", :conditions => (conditions + ["calls.disposition = 'ANSWERED' and card_id < 1"]).join(" AND ")) end @total_users = resu[0]["users"].to_i if resu and resu[0] @total_percent = 100 @total_profit_percent = @total_profit.to_f * 100 / @total_call_price.to_f @total_selfcost_percent = @total_percent - @total_profit_percent #average @total_duration_min = @total_duration.to_f / 60 @avg_profit_call_min = @total_profit.to_f / @total_duration_min @avg_profit_call = @total_profit.to_f / @total_answered_calls.to_f days = (session_till_date.to_date - session_from_date.to_date).to_f days = 1.0 if days == 0; @avg_profit_day = @total_profit.to_f / days @total_users != 0 ? @avg_profit_user = @total_profit.to_f / @total_users.to_f : @avg_profit_user = 0 else #profit @total_percent = 0 @total_profit_percent = 0 @total_selfcost_percent = 0 #avg @avg_profit_call_min = 0 @avg_profit_call = 0 @avg_profit_day = 0 @avg_profit_user = 0 end a1 = session_from_date a2 = session_till_date @sub_price_vat =0 if session[:usertype] != "reseller" price =0 sql = "SELECT users.id, subscriptions.*, Count(calls.id) as calls_size, services.servicetype, services.periodtype, services.quantity, #{SqlExport.replace_price('(services.price - services.selfcost_price)', {:reference => 'price'})} FROM subscriptions left join calls on (calls.user_id = subscriptions.user_id) join services on (services.id = subscriptions.service_id) join users on (users.id = subscriptions.user_id) WHERE ((activation_start < '#{a1}' AND activation_end BETWEEN '#{a1}' AND '#{a2}') OR (activation_start BETWEEN '#{a1}' AND '#{a2}' AND activation_end >'#{a2}') OR (activation_start > '#{a1}' AND activation_end < '#{a2}') OR (activation_start < '#{a1}' AND activation_end > '#{a2}')) #{user_sql2} group by subscriptions.id" res = ActiveRecord::Base.connection.select_all(sql) else res = [] end if res and res.size > 0 for r in res sub_days = r['activation_end'].to_time - r['activation_start'].to_time sub_days = (((sub_days / 60) / 60) / 24) @date = r['activation_start'] if @date.to_date > a1.to_date and r['activation_end'].to_date < a2.to_date price = 0 if r['periodtype'].to_s == 'day' quantity = sub_days / r['quantity'].to_f days = sub_days % r['quantity'].to_f if r['servicetype'].to_s == "activation_from_registration" price = r['price'].to_f * quantity.to_i end if r['servicetype'].to_s == "one_time_fee" price = r['price'].to_f end if r['servicetype'].to_s == "periodic_fee" or r['servicetype'].to_s == "flat_rate" price = ((r['price'].to_f / r['quantity'].to_f) * days.to_i).to_f + ((r['price'].to_f * quantity.to_f)).to_f end end if r['periodtype'].to_s == 'month' y = r['activation_end'].to_time.year - r['activation_start'].to_time.year m = r['activation_end'].to_time.month - r['activation_start'].to_time.month months = y.to_i * 12 + m.to_i quantity = months / r['quantity'].to_f days = r['activation_end'].to_time.day - r['activation_start'].to_time.day + 1 if r['servicetype'].to_s == "activation_from_registration" price = r['price'].to_f * quantity.to_i end if r['servicetype'].to_s == "one_time_fee" price = r['price'].to_f end if r['servicetype'].to_s == "periodic_fee" or r['servicetype'].to_s == "flat_rate" if days < 0 quantity = quantity -1 days = r['activation_start'].to_time.day + days end price = ((r['price'].to_f / r['activation_end'].to_time.end_of_month().day.to_i.to_f) * days.to_i).to_f + ((r['price'].to_f * quantity.to_f)).to_f end end @sub_price2 = price else price = 0 if @date.to_date <= a1.to_date use_start = a1.to_date else use_start = @date.to_date end if r['activation_end'].to_date >= a2.to_date use_end = a2.to_date else use_end = r['activation_end'].to_date @amount2=1 end sub_days = use_end.to_time - use_start.to_time sub_days = (((sub_days / 60) / 60) / 24) if r['periodtype'].to_s == 'day' quantity = sub_days / r['quantity'].to_f days = sub_days % r['quantity'].to_f if r['servicetype'].to_s == "activation_from_registration" price = r['price'].to_f * quantity.to_i end if r['servicetype'].to_s == "one_time_fee" price = r['price'].to_f end if r['servicetype'].to_s == "periodic_fee" or r['servicetype'].to_s == "flat_rate" price = ((r['price'].to_f / r['quantity'].to_f) * days.to_i).to_f + ((r['price'].to_f * quantity.to_f)).to_f end end if r['periodtype'].to_s == 'month' # my_debug "menuo" y = use_end.to_time.year - use_start.to_time.year m = use_end.to_time.month - use_start.to_time.month months = y.to_i * 12 + m.to_i quantity = months / r['quantity'].to_f days = use_end.to_time.day - use_start.to_time.day + 1 if r['servicetype'].to_s == "activation_from_registration" price = r['price'].to_f * quantity.to_i end if r['servicetype'].to_s == "one_time_fee" price = r['price'].to_f end if r['servicetype'].to_s == "periodic_fee" or r['servicetype'].to_s == "flat_rate" if days < 0 quantity = quantity -1 days = use_start.to_time.day + days end price = ((r['price'].to_f / use_end.to_time.end_of_month().day.to_i.to_f) * days.to_i).to_f + ((r['price'].to_f * quantity.to_f)).to_f end end # my_debug price @sub_price2 = price end @sub_price += @sub_price2.to_f end end @s_total_profit = @total_profit @s_total_profit += @sub_price end =begin Generates profit report in PDF =end def generate_profit_pdf require 'pdf/wrapper' @user_id = -1 user_name = "" if params[:user_id] if params[:user_id] != "-1" @user_id = params[:user_id] user = User.find_by_sql("SELECT * FROM users WHERE users.id = '#{@user_id}'") user_name = user[0]["username"] + " - " + user[0]["first_name"] + " " + user[0]["last_name"] else user_name = "All users" end end options = { :step => 20, :text_start => 193, # font :font => "Nimbus Sans L", :font_bold => "Nimbus Sans L Bold", :fontsize => 6, :header_size_add => 1, :title_fontsize => 14, :title_fontsize2 => 10, # positions :line_strength => 1, # collumn possitions :col1_x => 40, :col2_x => 190, :col3_x => 260, :col4_x => 330, :col5_x => 360, :col6_x => 415, :col7_x => 463, :col8_x => 505, :end_x => 560, #vertical positions :first_line_y => 203, :last_line_y_admin => 600, :last_line_y_reseller => 520 # :rec_h => 25, # :rec1_y => 305, # :rec2_y => 405, # :rec3_y => 508, # :rec4_y => 528, # :rec5_y => 548, #colors # :color_1 => [1.0, 0.88, 1.0], # pink # :color_2 => [0.89, 0.88, 0.87] # gray } pdf = PDF::Wrapper.new(:paper => :A4) pdf.font(options[:font_bold]) pdf.text(_('PROFIT_REPORT'), {:top => 50, :font_size => options[:title_fontsize], :alignment => :left}) pdf.font(options[:font]) pdf.text(_('Time_period') + ": " + session_from_date.to_s + " - " + session_till_date.to_s, {:top => 80, :alignment => :left, :font_size => options[:title_fontsize2]}) pdf.text(_('Counting') + ": " + user_name.to_s, {:top => 100, :alignment => :left, :font_size => options[:title_fontsize2]}) pdf.line(options[:col1_x], options[:first_line_y], options[:end_x], options[:first_line_y], {:line_width => options[:line_strength]}) if session[:usertype] != 'reseller' pdf.line(options[:col1_x], options[:last_line_y_admin], options[:end_x], options[:last_line_y_admin], {:line_width => options[:line_strength]}) else pdf.line(options[:col1_x], options[:last_line_y_reseller], options[:end_x], options[:last_line_y_reseller], {:line_width => options[:line_strength]}) end line = 1 pdf.text(_('Total_calls'), {:top => options[:text_start]+ (line*options[:step]), :left => options[:col1_x], :font_size => options[:fontsize] + options[:header_size_add]}) pdf.text(params[:total_calls], {:top => options[:text_start]+ (line*options[:step]), :left => options[:col2_x], :font_size => options[:fontsize]}) line += 1 pdf.text(_('Answered_calls'), {:top => options[:text_start]+ (line*options[:step]), :left => options[:col1_x], :font_size => options[:fontsize] + options[:header_size_add]}) pdf.text(params[:total_answered_calls], {:top => options[:text_start]+ (line*options[:step]), :left => options[:col2_x], :font_size => options[:fontsize]}) pdf.text(nice_number(params[:total_answer_percent]) + " %", {:top => options[:text_start]+ (line*options[:step]), :left => options[:col3_x], :font_size => options[:fontsize]}) pdf.text(_('Duration') + ": " + nice_time(params[:total_duration]), {:top => options[:text_start]+ (line*options[:step]), :left => options[:col4_x], :font_size => options[:fontsize]}) pdf.text(_('Average_call_duration') + ": " + nice_time(params[:average_call_duration]), {:top => options[:text_start]+ (line*options[:step]), :left => options[:col7_x] - 20, :font_size => options[:fontsize]}) line += 1 pdf.text(_('No_Answer'), {:top => options[:text_start]+ (line*options[:step]), :left => options[:col1_x], :font_size => options[:fontsize] + options[:header_size_add]}) pdf.text(params[:total_not_ans_calls], {:top => options[:text_start]+ (line*options[:step]), :left => options[:col2_x], :font_size => options[:fontsize]}) pdf.text(nice_number(params[:total_not_ans_percent]) + " %", {:top => options[:text_start]+ (line*options[:step]), :left => options[:col3_x], :font_size => options[:fontsize]}) line += 1 pdf.text(_('Busy_calls'), {:top => options[:text_start]+ (line*options[:step]), :left => options[:col1_x], :font_size => options[:fontsize] + options[:header_size_add]}) pdf.text(params[:total_busy_calls], {:top => options[:text_start]+ (line*options[:step]), :left => options[:col2_x], :font_size => options[:fontsize]}) pdf.text(nice_number(params[:total_busy_percent]) + " %", {:top => options[:text_start]+ (line*options[:step]), :left => options[:col3_x], :font_size => options[:fontsize]}) line += 1 pdf.text(_('Error_calls'), {:top => options[:text_start]+ (line*options[:step]), :left => options[:col1_x], :font_size => options[:fontsize] + options[:header_size_add]}) pdf.text(params[:total_error_calls], {:top => options[:text_start]+ (line*options[:step]), :left => options[:col2_x], :font_size => options[:fontsize]}) pdf.text(nice_number(params[:total_error_percent]) + " %", {:top => options[:text_start]+ (line*options[:step]), :left => options[:col3_x], :font_size => options[:fontsize]}) line += 1 pdf.font(options[:font_bold]) pdf.text(_('Price'), {:top => options[:text_start] + (line*options[:step]), :left => options[:col2_x], :font_size => options[:fontsize] + options[:header_size_add]}) pdf.text(_('Percent'), {:top => options[:text_start] + (line*options[:step]), :left => options[:col3_x], :font_size => options[:fontsize] + options[:header_size_add]}) pdf.text(_('Call_time'), {:top => options[:text_start] + (line*options[:step]), :left => options[:col4_x], :font_size => options[:fontsize] + options[:header_size_add]}) pdf.text(_('Active_users'), {:top => options[:text_start] + (line*options[:step]), :left => options[:col6_x], :font_size => options[:fontsize] + options[:header_size_add]}) pdf.font(options[:font]) line += 1 pdf.text(_('Total_call_price'), {:top => options[:text_start]+ (line*options[:step]), :left => options[:col1_x], :font_size => options[:fontsize] + options[:header_size_add]}) pdf.text(nice_number(params[:total_call_price]), {:top => options[:text_start]+ (line*options[:step]), :left => options[:col2_x], :font_size => options[:fontsize]}) pdf.text(nice_number(params[:total_percent]), {:top => options[:text_start]+ (line*options[:step]), :left => options[:col3_x], :font_size => options[:fontsize]}) pdf.text(nice_time(params[:total_duration]), {:top => options[:text_start]+ (line*options[:step]), :left => options[:col4_x], :font_size => options[:fontsize]}) pdf.text(params[:active_users].to_i, {:top => options[:text_start]+ (line*options[:step]), :left => options[:col6_x], :font_size => options[:fontsize]}) line += 1 pdf.text(_('Total_call_self_price'), {:top => options[:text_start]+ (line*options[:step]), :left => options[:col1_x], :font_size => options[:fontsize] + options[:header_size_add]}) pdf.text(nice_number(params[:total_call_selfprice]), {:top => options[:text_start]+ (line*options[:step]), :left => options[:col2_x], :font_size => options[:fontsize]}) pdf.text(nice_number(params[:total_selfcost_percent]), {:top => options[:text_start]+ (line*options[:step]), :left => options[:col3_x], :font_size => options[:fontsize]}) line += 1 pdf.text(_('Calls_profit'), {:top => options[:text_start]+ (line*options[:step]), :left => options[:col1_x], :font_size => options[:fontsize]+ options[:header_size_add]}) pdf.text(nice_number(params[:total_profit]), {:top => options[:text_start]+ (line*options[:step]), :left => options[:col2_x], :font_size => options[:fontsize]}) pdf.text(nice_number(params[:total_percent_percent]), {:top => options[:text_start]+ (line*options[:step]), :left => options[:col3_x], :font_size => options[:fontsize]}) line += 1 pdf.text(_('Average_profit_per_call_min'), {:top => options[:text_start]+ (line*options[:step]), :left => options[:col1_x], :font_size => options[:fontsize] + options[:header_size_add]}) pdf.text(nice_number(params[:avg_profit_call_min]), {:top => options[:text_start]+ (line*options[:step]), :left => options[:col2_x], :font_size => options[:fontsize]}) line += 1 pdf.text(_('Average_profit_per_call'), {:top => options[:text_start]+ (line*options[:step]), :left => options[:col1_x], :font_size => options[:fontsize] + options[:header_size_add]}) pdf.text(nice_number(params[:avg_profit_call]), {:top => options[:text_start]+ (line*options[:step]), :left => options[:col2_x], :font_size => options[:fontsize]}) line += 1 pdf.text(_('Average_profit_per_day'), {:top => options[:text_start]+ (line*options[:step]), :left => options[:col1_x], :font_size => options[:fontsize] + options[:header_size_add]}) pdf.text(nice_number(params[:avg_profit_day]), {:top => options[:text_start]+ (line*options[:step]), :left => options[:col2_x], :font_size => options[:fontsize]}) line += 1 pdf.text(_('Average_profit_per_active_user'), {:top => options[:text_start]+ (line*options[:step]), :left => options[:col1_x], :font_size => options[:fontsize] + options[:header_size_add]}) pdf.text(nice_number(params[:avg_profit_user]), {:top => options[:text_start]+ (line*options[:step]), :left => options[:col2_x], :font_size => options[:fontsize]}) if session[:usertype] != 'reseller' line += 1 pdf.font(options[:font_bold]) pdf.text(_('Price'), {:top => options[:text_start]+ (line*options[:step]), :left => options[:col2_x], :font_size => options[:fontsize] + options[:header_size_add]}) pdf.font(options[:font]) line += 1 pdf.font(options[:font_bold]) pdf.text(_('Subscriptions_profit'), {:top => options[:text_start]+ (line*options[:step]), :left => options[:col1_x], :font_size => options[:fontsize] + options[:header_size_add]}) pdf.font(options[:font]) pdf.text(nice_number(params[:sub_price]), {:top => options[:text_start]+ (line*options[:step]), :left => options[:col2_x], :font_size => options[:fontsize]}) line += 1 pdf.font(options[:font_bold]) pdf.text(_('Total_profit'), {:top => options[:text_start]+ (line*options[:step]), :left => options[:col1_x], :font_size => options[:fontsize] + options[:header_size_add]}) pdf.font(options[:font]) pdf.text(nice_number(params[:s_total]), {:top => options[:text_start]+ (line*options[:step]), :left => options[:col2_x], :font_size => options[:fontsize]}) end send_data pdf.render, :filename => "Profit-#{user_name}-#{session_from_date.to_s}_#{session_till_date.to_s}.pdf", :type => "application/pdf" end def providers_calls session[:stats_providers_calls] = nil if session[:stats_providers_calls] and session[:stats_providers_calls][:direction] == 0 session[:stats_providers_calls].nil? ? @options = {} : @options = session[:stats_providers_calls] @Show_Currency_Selector=1 @user = current_user if !@provider flash[:notice] = _('Canot_find_provider_with_id')+" : " + params[:id].to_s redirect_to :controller => "providers", :action => "list" and return false end @page_title = _('Providers_calls') @page_title = @page_title + ": " + @provider.name @page_icon = "call.png" change_date my_debug @options.to_yaml #set direction to whatever was passed with get/post, if direction was not posted #set to whatever was saved in session or if nothing was saved set to default 'outgoing' @options[:direction] = params[:direction] || @options[:direction] || "outgoing" @options[:call_type] = params[:call_type] if params[:call_type] @options[:call_type] = "all" if !@options[:call_type] conditions = [] if @options[:direction] == "incoming" conditions << "(calls.did_provider_id = '#{@provider.id}' OR calls.src_device_id = '#{@provider.device_id}')" else conditions << "calls.provider_id = '#{@provider.id}'" end conditions << "disposition = '#{@options[:call_type]}' " if @options[:call_type] != "all" conditions << "calldate BETWEEN '#{session_from_datetime}' AND '#{session_till_datetime}'" # @total_calls = Call.count(:conditions => conditions.join(" AND ")) # @calls = Call.find(:all, :conditions => conditions.join(" AND "), :order => "calldate DESC") select = [] select << "COUNT(*) AS 'total_calls'" select << "SUM(IF(calls.billsec > 0, calls.billsec, CEIL(calls.real_billsec) )) as 'duration'" select << "SUM(IF(calls.reseller_id > 0, calls.reseller_price, calls.user_price)) as 'user_price'" select << "SUM(IF(calls.provider_price IS NOT NULL, calls.provider_price, 0)) as 'provider_price'" total_data = Call.find(:first, :select => select.join(", "), :conditions => conditions.join(" AND ") ) @total_calls = total_data["total_calls"].to_i items_per_page, total_pages = item_pages(@total_calls) page_no = valid_page_number(params[:page], total_pages) offset, limit = query_limit(total_pages, items_per_page, page_no) @options[:total_pages] = total_pages @options[:page] = page_no @calls = Call.find(:all, :conditions => conditions.join(" AND "), :limit => limit, :offset => offset, :order => " calldate DESC" ) @exchange_rate = Currency.count_exchange_rate(session[:default_currency], session[:show_currency]) @total_duration = total_data["duration"].to_i @total_user_price = total_data["user_price"].to_f * @exchange_rate @total_provider_price = total_data["provider_price"].to_f * @exchange_rate @total_profit = @total_user_price - @total_provider_price session[:stats_providers_calls] = @options end def date_query(date_from, date_till) # date query if date_from == "" date_sql = "" else if date_from.length > 11 date_sql = "AND calldate BETWEEN '#{date_from.to_s}' AND '#{date_till.to_s}'" else date_sql = "AND calldate BETWEEN '" + date_from.to_s + " 00:00:00' AND '" + date_till.to_s + " 23:59:59'" end end date_sql end ############ PDF ############### def providers_calls_to_pdf require "pdf/wrapper" if params[:id] provider = Provider.find(params[:id]) end date_from = params[:date_from] date_till = params[:date_till] call_type = params[:call_type] params[:direction] ? @direction = params[:direction] : @direction = "outgoing" params[:call_type] ? @call_type = params[:call_type] : @call_type = "all" @orig_call_type = @call_type if @direction == "incoming" disposition = " (calls.did_provider_id = '#{params[:id]}' OR calls.src_device_id = '#{provider.device_id}' )" else disposition = " calls.provider_id = '#{params[:id]}' " end disposition += " AND disposition = '#{@call_type}' " if @call_type != "all" disposition += " AND calldate BETWEEN '#{date_from}' AND '#{date_till}'" calls = Call.find(:all, :conditions => "#{disposition}", :order => "calldate DESC") options = { :title_fontsize => 14, :title_fontsize2 => 11, :title_fontsize3 => 7, :fontsize => 6, :header_add_size => 0, :page_number_size => 8, :dat_x => 30, :caf_x => 120, :cat_x => 190, :dur_x => 265, :han_x => 315, :col1_x => 380, :col2_x => 435, :col3_x => 495, :title_pos1 => 40, :title_pos2 => 60, :title_pos3 => 75, :title_pos4 => 95, :title_pos5 => 105, :first_page_pos => 155, :second_page_pos => 70, :header_elevation => 20, :first_page_items => 40, :second_page_items => 45, :step_size => 15, :date_from => date_from, :date_till => date_till, :call_type => call_type, :nice_number_digits => session[:nice_number_digits], :currency => session[:show_currency], :default_currency => session[:default_currency], :direction => @direction } a = MorLog.my_debug("Genetare_start", true) pdf = PdfGen::Generate.providers_calls_to_pdf(provider, calls, options) b = MorLog.my_debug("Genetare_end", true) MorLog.my_debug("Generate_time : #{b - a}") send_data pdf.render, :filename => "CDR-#{provider.name}-#{date_from}_#{date_till}.pdf", :type => "application/pdf" end ############ CSV ############### def providers_calls_to_csv provider = Provider.find_by_id(params[:id]) unless provider flash[:notice] = _('Provider_not_found') redirect_to :controller => "callc", :action => "main" and return false end date_from = params[:date_from] date_till = params[:date_till] @direction = "outgoing" @direction = params[:direction] if params[:direction] @call_type = "all" @call_type = params[:call_type] if params[:call_type] @orig_call_type = @call_type filename = provider.provider_calls_csv({:tz => current_user.time_zone, :direction => @direction, :call_type => @call_type, :date_from => date_from, :date_till => date_till, :default_currency => session[:default_currency], :show_currency => session[:show_currency], :show_full_src => session[:show_full_src], :nice_number_digits => session[:nice_number_digits], :test => params[:test].to_i}) filename = archive_file_if_size(filename, "csv", Confline.get_value("CSV_File_size").to_f) if params[:test].to_i != 1 send_file(filename) else render :text => filename end end def faxes @page_title = _('Faxes') @page_icon = "printer.png" change_date if session[:usertype] == "admin" @users = User.find(:all, :conditions => "hidden = 0", :order => "username ASC ") else @users = User.find(:all, :conditions => ["hidden = 0 AND owner_id = ?", correct_owner_id], :order => "username ASC ") end @search = 0 @received = [] @corrupted = [] @mistaken = [] @total = [] @size_on_hdd = [] @t_received = 0 @t_corrupted = 0 @t_mistaken = 0 @t_total = 0 @t_size_on_hdd = 0 i = 0 for user in @users sql = "SELECT COUNT(pdffaxes.id) as 'cf' FROM pdffaxes JOIN devices ON (pdffaxes.device_id = devices.id AND devices.user_id = #{user.id}) WHERE receive_time BETWEEN '#{session_from_datetime}' AND '#{session_till_datetime}' AND status = 'good'" res = ActiveRecord::Base.connection.select_value(sql) @received[i] = res.to_i sql = "SELECT COUNT(pdffaxes.id) as 'cf' FROM pdffaxes JOIN devices ON (pdffaxes.device_id = devices.id AND devices.user_id = #{user.id}) WHERE receive_time BETWEEN '#{session_from_datetime}' AND '#{session_till_datetime}' AND status = 'pdf_size_0'" res = ActiveRecord::Base.connection.select_value(sql) @corrupted[i] = res.to_i sql = "SELECT COUNT(pdffaxes.id) as 'cf' FROM pdffaxes JOIN devices ON (pdffaxes.device_id = devices.id AND devices.user_id = #{user.id}) WHERE receive_time BETWEEN '#{session_from_datetime}' AND '#{session_till_datetime}' AND status = 'no_tif'" res = ActiveRecord::Base.connection.select_value(sql) @mistaken[i] = res.to_i @total[i] = @received[i] + @corrupted[i] + @mistaken[i] sql = "SELECT SUM(pdffaxes.size) as 'cf' FROM pdffaxes JOIN devices ON (pdffaxes.device_id = devices.id AND devices.user_id = #{user.id}) WHERE receive_time BETWEEN '#{session_from_datetime}' AND '#{session_till_datetime}'" res = ActiveRecord::Base.connection.select_value(sql) @size_on_hdd[i] = res.to_f / (1024 * 1024) @t_received += @received[i] @t_corrupted += @corrupted[i] @t_mistaken += @mistaken[i] @t_total += @total[i] @t_size_on_hdd += @size_on_hdd[i] i += 1 end end def faxes_list @page_title = _('Faxes') @page_icon = "printer.png" change_date if session[:usertype] == "admin" @user = User.find(:first, :conditions => ["id = ?", params[:id].to_i]) if params[:id].to_i >= 0 and @user == nil flash[:notice] = _('User_not_found') redirect_to :controller => "callc", :action => "main" and return false end else @user = User.find(session[:user_id]) end @devices = @user.fax_devices @Fax2Email_Folder = Confline.get_value("Fax2Email_Folder", 0) if @Fax2Email_Folder.to_s == "" @Fax2Email_Folder = Web_URL + Web_Dir + "/fax2email/" end @sel_device = "all" @sel_device = params[:device_id] if params[:device_id] device_sql = "" device_sql = " AND device_id = '#{@sel_device}' " if @sel_device != "all" @fstatus = "all" @fstatus = params[:fstatus] if params[:fstatus] status_sql = "" status_sql = " AND status = '#{@fstatus}' " if @fstatus != "all" @search = 0 @search = 1 if params[:search_on] sql = "SELECT pdffaxes.* FROM pdffaxes, devices WHERE pdffaxes.device_id = devices.id AND devices.user_id = #{@user.id} AND receive_time BETWEEN '#{session_from_datetime}' AND '#{session_till_datetime}' #{status_sql} #{device_sql}" @faxes = Pdffax.find_by_sql(sql) end #================= ACTIVE CALLS ==================== def active_calls_count user = User.new(:usertype => session[:usertype]) user.id = session[:usertype] == 'accountant' ? 0 : session[:user_id] @acc = Activecall.count_for_user(user) render(:layout => false) end def active_calls unless ["admin", "accountant"].include?(session[:usertype]) or session[:show_active_calls_for_users].to_i == 1 or (current_user and current_user.reseller_allow_providers_tariff?) flash[:notice] = _('You_are_not_authorized_to_view_this_page') redirect_to :controller => :callc, :action => :main and return false end user = User.new(:usertype => session[:usertype]) user.id = session[:usertype] == 'accountant' ? 0 : session[:user_id] @acc = Activecall.count_for_user(user) @page_title = _('Active_Calls') @page_icon = "call.png" @refresh_period = session[:active_calls_refresh_interval].to_i active_calls_order end def active_calls_order sort_options = ["status", "answer_time", "duration", "src", "localized_dst", "provider_name", "server_id", "did"] session[:active_calls_options] ? @options = session[:active_calls_options] : @options = {:order_by => "duration", :order_desc => 1, :update => "active_calls", :controller => :stats, :action => :active_calls_order} @options[:order_by] = params[:order_by] if params[:order_by] and sort_options.include?(params[:order_by].to_s) @options[:order_desc] = params[:order_desc].to_i if params[:order_desc] and [0, 1].include?(params[:order_desc].to_i) session[:active_calls_options] = @options active_calls_show render(:partial => "active_calls_show") if params[:action].to_s == "active_calls_order" end def active_calls_show session[:active_calls_options] ? @options = session[:active_calls_options] : @options = {:order_by => "duration", :order_desc => 1, :update => "active_calls", :controller => :stats, :action => :active_calls_order} @time_now = Time.now # this code selects correct calls for admin/reseller/user user_sql = " " user_id = session[:usertype] == 'accountant' ? 0 : session[:user_id] if user_id != 0 #reseller or user if session[:usertype] == "reseller" #reseller user_sql = " WHERE activecalls.user_id = #{user_id} OR dst_usr.id = #{user_id} OR activecalls.owner_id = #{user_id} " else #user user_sql = " WHERE activecalls.user_id = #{user_id} OR dst_usr.id = #{user_id} " end end @show_did = current_user.active_calls_show_did? #@active_calls = Activecall.find(:all, :order => "id") sql = " SELECT activecalls.id as ac_id, activecalls.channel as channel, activecalls.prefix, activecalls.server_id as server_id, activecalls.answer_time as answer_time, activecalls.src as src, activecalls.localized_dst as localized_dst, activecalls.uniqueid as uniqueid, activecalls.lega_codec as lega_codec,activecalls.legb_codec as legb_codec,activecalls.pdd as pdd, #{SqlExport.replace_price('activecalls.user_rate', {:reference => 'user_rate'})}, tariffs.currency as rate_currency, users.id as user_id, users.first_name as user_first_name, users.last_name as user_last_name, users.username as user_username, devices.id as 'device_id',devices.device_type as device_type, devices.name as device_name, devices.username as device_username, devices.extension as device_extension, devices.istrunk as device_istrunk, devices.ani as device_ani, dst.id as dst_device_id, dst.device_type as dst_device_type, dst.name as dst_device_name, dst.username as dst_device_username, dst.extension as dst_device_extension, dst.istrunk as dst_device_istrunk, dst.ani as dst_device_ani, dst_usr.id as dst_user_id, dst_usr.first_name as dst_user_first_name, dst_usr.last_name as dst_user_last_name, dst_usr.username as dst_user_username, destinations.direction_code as direction_code, directions.name as direction_name, destinations.subcode as destination_subcode, destinations.name as destination_name, providers.id as provider_id, providers.name as provider_name, providers.common_use, providers.user_id as 'providers_owner_id', activecalls.did_id as did_id, dids.did as did, g.direction_code as did_direction_code, NOW() - activecalls.answer_time AS 'duration', IF(activecalls.answer_time IS NULL, 0, 1 ) as 'status' FROM activecalls LEFT JOIN providers ON (providers.id =activecalls.provider_id) LEFT JOIN devices ON (devices.id = activecalls.src_device_id) LEFT JOIN devices AS dst ON (dst.id = activecalls.dst_device_id) LEFT JOIN users ON (users.id = devices.user_id) LEFT JOIN users AS dst_usr ON (dst_usr.id = dst.user_id) LEFT JOIN tariffs ON (tariffs.id = users.tariff_id) LEFT JOIN destinations ON (destinations.prefix = activecalls.prefix) LEFT JOIN directions ON (directions.code = destinations.direction_code) LEFT JOIN dids ON (activecalls.did_id = dids.id) LEFT JOIN (SELECT * FROM (SELECT dids.did, destinations.direction_code FROM dids JOIN destinations on (prefix=SUBSTRING(dids.did, 1, LENGTH(prefix))) WHERE dids.id IN (SELECT did_id FROM activecalls) ORDER BY LENGTH(destinations.prefix) DESC) AS v GROUP BY did) AS g ON (g.did = dids.did) #{user_sql} ORDER BY #{@options[:order_by]} #{@options[:order_desc] == 1 ? "DESC" : "ASC"} " if user_id.to_s.blank? @active_calls = [] else @active_calls = ActiveRecord::Base.connection.select_all(sql) end @chanspy_disabled = Confline.chanspy_disabled? @spy_device = Device.find(:first, :conditions => "id = #{current_user.spy_device_id}") # sql2 = "SELECT activecalls.id, activecalls.server_id as server_id, activecalls.provider_id as provider_id, activecalls.user_id as user_id FROM activecalls # WHERE start_time < '#{nice_date_time(Time.now() - 7200)}'" =begin sql2 = "SELECT activecalls.id, activecalls.server_id as server_id, activecalls.provider_id as provider_id, activecalls.user_id as user_id FROM activecalls WHERE start_time < '#{Time.now() - 7200}'" calls = ActiveRecord::Base.connection.select_all(sql2) if calls MorLog.my_debug(sql2) sql3 = "DELETE activecalls.* FROM activecalls WHERE start_time < '#{nice_date_time(Time.now() - 7200)}'" ActiveRecord::Base.connection.delete(sql3) bt = Thread.new {active_calls_longer_error(calls)} #bt.join << kam kurt threada jei jo pabaigos yra laukiama ir nieko nedaroma laukimo metu? end =end session[:active_calls_options] = @options render(:partial => "active_calls_show") if params[:action].to_s == "active_calls_show" end =begin SELECT activecalls.start_time as start_time, activecalls.src as src, activecalls.dst as dst, users.id as user_id, users.first_name as user_first_name, users.last_name as user_last_name, devices.device_type as device_type, devices.name as device_name, devices.extension as device_extension, devices.istrunk as device_istrunk, devices.ani as device_ani, dst.id as dst_id, dst.device_type as dst_device_type, dst.name as dst_device_name, dst.extension as dst_device_extension, dst.istrunk as dst_device_istrunk, dst.ani as dst_device_ani, dst_usr.id as dst_user_id, dst_usr.first_name as dst_user_first_name, dst_usr.last_name as dst_user_last_name FROM activecalls LEFT JOIN providers ON (providers.id =activecalls.provider_id) LEFT JOIN devices ON (devices.id = activecalls.src_device_id) LEFT JOIN devices AS dst ON (dst.id = activecalls.dst_device_id) LEFT JOIN users ON (users.id = devices.user_id) LEFT JOIN users AS dst_usr ON (dst_usr.id = dst.user_id) LEFT JOIN destinations ON (destinations.prefix = activecalls.prefix) LEFT JOIN directions ON (directions.code = destinations.direction_code) =end #================= MISSED CALLS ==================== =begin in before filter : user (:find_user_from_id_or_session, :authorize_user) =end def missed_calls change_date #my_debug params[:processed] #changing the state of call processed field if params[:processed] call = Call.find(params[:processed]) if call.processed == 0 call.processed = 1 else call.processed = 0 end call.save end @page_title = _('Missed_calls') @page_icon = "call.png" #count missed calls @all_calls = @calls = @user.calls("missed_not_processed_plus_inc", session_from_datetime, session_till_datetime) params[:search_on] ? @search = 1 : @search = 0 end #=================== DIDs =============================== def dids @page_title = _('DIDs') @page_icon = "did.png" change_date change_date_to_present if params[:clear] @users = User.find_all_for_select(corrected_user_id) @providers = Provider.find(:all, :order => "name ASC", :conditions => ['hidden=?', 0]) #@user_id2 = -1 (params[:user_id]) ? (@user_id = params[:user_id].to_i) : (@user_id = -1) user_sql = "" #my_debug @user_id2 @provider_id = -1 provider_sql = "" # my_debug params[:user_id].to_i if session[:usertype] == "admin" if params[:user_id] if params[:user_id].to_i != -1 #@user_id2 = params[:user_id].to_i user_sql = " AND dids.user_id = '#{@user_id}' " end end if params[:user_id] if params[:provider_id].to_i != -1 @provider_id = params[:provider_id] provider_sql = " AND dids.provider_id = '#{@provider_id}' " end end end #if params[:direction] #if params[:direction].to_s == "outgoing" # dir = "Local" #else dir = "Outside" #end #@direction = params[:direction] direction = '' #" AND calls.callertype = '#{dir}'" # end sql = "SELECT dids.*, SUM(calls.did_price) as did_price , SUM(calls.did_prov_price) as did_prov_price, SUM(calls.did_inc_price) as did_inc_price, COUNT(calls.id) as 'calls_size', providers.name, users.username, users.first_name, users.last_name, actions.date FROM dids JOIN calls on (calls.did_id = dids.id) JOIN providers on (dids.provider_id = providers.id) JOIN users on (dids.user_id = users.id) left JOIN actions on (dids.id = actions.data AND actions.action like 'did_assigned%') WHERE calls.calldate BETWEEN '#{session_from_date} 00:00:00' AND '#{session_till_date} 23:59:59' #{user_sql.to_s + provider_sql.to_s + direction.to_s} GROUP BY dids.id ORDER BY dids.user_id" # my_debug sql @res = ActiveRecord::Base.connection.select_all(sql) @page = 1 @page = params[:page].to_i if params[:page] @total_pages = (@res.size.to_f / session[:items_per_page].to_f).ceil @all_res = @res @res = [] iend = ((session[:items_per_page] * @page) - 1) iend = @all_res.size - 1 if iend > (@all_res.size - 1) for i in ((@page - 1) * session[:items_per_page])..iend @res << @all_res[i] end if @direction.to_s == "outgoing" @dids_total_price = 0 @dids_total_price_provider = 0 @dids_total_profit = 0 @dids_total_calls = 0 for r in @res @dids_total_price += r['did_price'].to_f @dids_total_price_provider += r['did_prov_price'].to_f @dids_total_profit += r['did_price'].to_f - r['did_prov_price'].to_f @dids_total_calls += r['calls_size'].to_i end else @dids_total_price = 0 @dids_total_price_provider = 0 @dids_total_inc_price = 0 @dids_total_profit = 0 @dids_total_calls = 0 for r in @res @dids_total_price += r['did_price'].to_f @dids_total_price_provider += r['did_prov_price'].to_f @dids_total_inc_price += r['did_inc_price'].to_f @dids_total_profit += r['did_price'].to_f + r['did_prov_price'].to_f + r['did_inc_price'].to_f @dids_total_calls += r['calls_size'].to_i end end end #======================== SYSTEM STATS ====================================== def system_stats @page_title = _('System_stats') @page_icon = "chart_pie.png" sql = "SELECT COUNT(users.id) as \'users\' FROM users" res = ActiveRecord::Base.connection.select_all(sql) @total_users = res[0]["users"].to_i sql = "SELECT COUNT(users.id) as \'users\' FROM users WHERE users.usertype = 'admin'" res = ActiveRecord::Base.connection.select_all(sql) @total_admin = res[0]["users"].to_i sql = "SELECT COUNT(users.id) as \'users\' FROM users WHERE users.usertype = 'reseller'" res = ActiveRecord::Base.connection.select_all(sql) @total_resellers = res[0]["users"].to_i sql = "SELECT COUNT(users.id) as \'users\' FROM users WHERE users.usertype = 'accountant'" res = ActiveRecord::Base.connection.select_all(sql) @total_accountant = res[0]["users"].to_i sql = "SELECT COUNT(users.id) as \'users\' FROM users WHERE users.usertype = 'user'" res = ActiveRecord::Base.connection.select_all(sql) @total_t_user = res[0]["users"].to_i sql = "SELECT COUNT(users.id) as \'users\' FROM users WHERE users.postpaid = '1'" res = ActiveRecord::Base.connection.select_all(sql) @total_pospaid = res[0]["users"].to_i sql = "SELECT COUNT(users.id) as \'users\' FROM users WHERE users.postpaid = '0'" res = ActiveRecord::Base.connection.select_all(sql) @total_prepaid = res[0]["users"].to_i sql = "SELECT COUNT(devices.id) as \'devices\' FROM devices" res = ActiveRecord::Base.connection.select_all(sql) @total_devices = res[0]["devices"].to_i @device_types = Devicetype.find(:all) @dev_types = [] for type in @device_types sql = "SELECT COUNT(devices.id) as \'devices\' FROM devices WHERE devices.device_type = '#{type.name.to_s}'" res = ActiveRecord::Base.connection.select_all(sql) @dev_types[type.id] = res[0]["devices"].to_i end sql = "SELECT COUNT(devices.id) as \'devices\' FROM devices WHERE devices.device_type = 'FAX'" res = ActiveRecord::Base.connection.select_all(sql) @dev_types_fax = res[0]["devices"].to_i sql = "SELECT COUNT(tariffs.id) as \'tariffs\' FROM tariffs" res = ActiveRecord::Base.connection.select_all(sql) @total_tariffs = res[0]["tariffs"].to_i sql = "SELECT COUNT(tariffs.id) as \'tariffs\' FROM tariffs WHERE tariffs.purpose = 'provider'" res = ActiveRecord::Base.connection.select_all(sql) @total_tariffs_provider = res[0]["tariffs"].to_i sql = "SELECT COUNT(tariffs.id) as \'tariffs\' FROM tariffs WHERE tariffs.purpose = 'user'" res = ActiveRecord::Base.connection.select_all(sql) @total_tariffs_user = res[0]["tariffs"].to_i sql = "SELECT COUNT(tariffs.id) as \'tariffs\' FROM tariffs WHERE tariffs.purpose = 'user_wholesale'" res = ActiveRecord::Base.connection.select_all(sql) @total_tariffs_user_wholesale = res[0]["tariffs"].to_i sql = "SELECT COUNT(providers.id) as \'providers\' FROM providers" res = ActiveRecord::Base.connection.select_all(sql) @total_providers = res[0]["providers"].to_i @provider_types = Providertype.find(:all) @prov_type =[] for type in @provider_types sql = "SELECT COUNT(providers.id) as \'providers\' FROM providers WHERE providers.tech = '#{type.name.to_s}'" res = ActiveRecord::Base.connection.select_all(sql) @prov_type[type.id] = res[0]["providers"].to_i end sql = "SELECT COUNT(lcrs.id) as \'lcrs\' FROM lcrs" res = ActiveRecord::Base.connection.select_all(sql) @total_lrcs = res[0]["lcrs"].to_i sql = "SELECT COUNT(dids.id) as \'dids\' FROM dids" res = ActiveRecord::Base.connection.select_all(sql) @total_dids = res[0]["dids"].to_i sql = "SELECT COUNT(dids.id) as \'dids\' FROM dids WHERE dids.status = 'free'" res = ActiveRecord::Base.connection.select_all(sql) @total_dids_free = res[0]["dids"].to_i sql = "SELECT COUNT(dids.id) as \'dids\' FROM dids WHERE dids.status = 'active'" res = ActiveRecord::Base.connection.select_all(sql) @total_dids_active = res[0]["dids"].to_i sql = "SELECT COUNT(dids.id) as \'dids\' FROM dids WHERE dids.status = 'reserved'" res = ActiveRecord::Base.connection.select_all(sql) @total_dids_reserved = res[0]["dids"].to_i sql = "SELECT COUNT(dids.id) as \'dids\' FROM dids WHERE dids.status = 'closed'" res = ActiveRecord::Base.connection.select_all(sql) @total_dids_closed = res[0]["dids"].to_i sql = "SELECT COUNT(dids.id) as \'dids\' FROM dids WHERE dids.status = 'terminated'" res = ActiveRecord::Base.connection.select_all(sql) @total_dids_terminated = res[0]["dids"].to_i sql = "SELECT COUNT(directions.id) as \'directions\' FROM directions" res = ActiveRecord::Base.connection.select_all(sql) @total_directions = res[0]["directions"].to_i sql = "SELECT COUNT(destinations.id) as \'destinations\' FROM destinations" res = ActiveRecord::Base.connection.select_all(sql) @total_destinations = res[0]["destinations"].to_i sql = "SELECT COUNT(destinationgroups.id) as \'destinationgroups\' FROM destinationgroups" res = ActiveRecord::Base.connection.select_all(sql) @total_dg = res[0]["destinationgroups"].to_i sql = 'SELECT COUNT(calls.id) as \'calls\' FROM calls' res = ActiveRecord::Base.connection.select_all(sql) @total_calls = res[0]["calls"].to_i sql = 'SELECT COUNT(calls.id) as \'calls\' FROM calls WHERE calls.disposition = \'ANSWERED\' ' res = ActiveRecord::Base.connection.select_all(sql) @total_calls_anws = res[0]["calls"].to_i sql = 'SELECT COUNT(calls.id) as \'calls\' FROM calls WHERE calls.disposition = \'BUSY\' ' res = ActiveRecord::Base.connection.select_all(sql) @total_calls_busy = res[0]["calls"].to_i sql = 'SELECT COUNT(calls.id) as \'calls\' FROM calls WHERE calls.disposition = \'NO ANSWER\' ' res = ActiveRecord::Base.connection.select_all(sql) @total_calls_no_answ = res[0]["calls"].to_i @total_failet = @total_calls - @total_calls_anws - @total_calls_busy - @total_calls_no_answ sql = 'SELECT COUNT(cards.id) as \'cards\' FROM cards' res = ActiveRecord::Base.connection.select_all(sql) @total_cards = res[0]["cards"].to_i sql = 'SELECT COUNT(cardgroups.id) as \'cardgroups\' FROM cardgroups' res = ActiveRecord::Base.connection.select_all(sql) @total_cards_grp = res[0]["cardgroups"].to_i end def dids_usage @page_title = _('DIDs_usage') @page_icon = "did.png" change_date sql = "SELECT COUNT(DISTINCT actions.id) as \'actions\' FROM actions WHERE actions.date BETWEEN '#{session_from_date} 00:00:00' AND '#{session_till_date} 23:59:59' AND actions.action = 'did_closed'" res = ActiveRecord::Base.connection.select_all(sql) @did_closed = res[0]["actions"].to_i sql = "SELECT COUNT(DISTINCT actions.id) as \'actions\' FROM actions WHERE actions.date BETWEEN '#{session_from_date} 00:00:00' AND '#{session_till_date} 23:59:59' AND actions.action = 'did_made_available'" res = ActiveRecord::Base.connection.select_all(sql) @did_made_available = res[0]["actions"].to_i sql = "SELECT COUNT(DISTINCT actions.id) as \'actions\' FROM actions WHERE actions.date BETWEEN '#{session_from_date} 00:00:00' AND '#{session_till_date} 23:59:59' AND actions.action = 'did_reserved' AND actions.data2 = '0'" res = ActiveRecord::Base.connection.select_all(sql) @did_reserved = res[0]["actions"].to_i sql = "SELECT COUNT(DISTINCT actions.id) as \'actions\' FROM actions WHERE actions.date BETWEEN '#{session_from_date} 00:00:00' AND '#{session_till_date} 23:59:59' AND actions.action = 'did_created'" res = ActiveRecord::Base.connection.select_all(sql) @did_created = res[0]["actions"].to_i sql = "SELECT COUNT(DISTINCT actions.data) as \'actions\' FROM actions WHERE actions.date BETWEEN '#{session_from_date} 00:00:00' AND '#{session_till_date} 23:59:59' AND actions.action ='did_assigned_to_dp'" res = ActiveRecord::Base.connection.select_all(sql) @did_assigned1 = res[0]["actions"].to_i sql = "SELECT COUNT(DISTINCT actions.data) as \'actions\' FROM actions WHERE actions.date BETWEEN '#{session_from_date} 00:00:00' AND '#{session_till_date} 23:59:59' AND actions.action ='did_assigned'" res = ActiveRecord::Base.connection.select_all(sql) @did_assigned2 = res[0]["actions"].to_i @did_assigned= @did_assigned1 + @did_assigned2 sql = "SELECT COUNT(DISTINCT actions.data) as \'actions\' FROM actions WHERE actions.date BETWEEN '#{session_from_date} 00:00:00' AND '#{session_till_date} 23:59:59' AND actions.action ='did_deleted'" res = ActiveRecord::Base.connection.select_all(sql) @did_deleted = res[0]["actions"].to_i sql = "SELECT COUNT(DISTINCT actions.data) as \'actions\' FROM actions WHERE actions.date BETWEEN '#{session_from_date} 00:00:00' AND '#{session_till_date} 23:59:59' AND actions.action ='did_terminated'" res = ActiveRecord::Base.connection.select_all(sql) @did_terminated = res[0]["actions"].to_i @free = Did.find(:all, :conditions => "status = 'free'").size @reserved = Did.find(:all, :conditions => "status = 'reserved'").size @active = Did.find(:all, :conditions => "status = 'active'").size @closed = Did.find(:all, :conditions => "status = 'closed'").size @terminated = Did.find(:all, :conditions => "status = 'terminated'").size end # Prefix Finder ################################################################ def prefix_finder @page_title = _('Dynamic_Search') @page_icon = "magnifier.png" end def prefix_finder_find @phrase = params[:prefix].gsub(/[^\d]/, '') if params[:prefix] @callshop = params[:callshop].to_i @dest = Destination.find( :first, :conditions => ["prefix = SUBSTRING(?, 1, LENGTH(destinations.prefix))", @phrase], :order => "LENGTH(destinations.prefix) DESC" ) if @phrase != '' @flag = nil if @dest == nil @results = "" else @flag = @dest.direction_code direction = @dest.direction @dg = @dest.destinationgroup @results = @dest.subcode.to_s+" "+@dest.name.to_s @results = direction.name.to_s+" "+ @results if direction @flag2 = @dg.flag if @dg @results2 = "#{_('Destination_group')} : " + @dg.name.to_s if @dg if @callshop.to_i > 0 sql = "SELECT position, user_id , users.tariff_id, gusertype from usergroups left join users on users.id = usergroups.user_id left join tariffs on tariffs.id = users.tariff_id where group_id = #{@callshop.to_i}" @booths = Usergroup.find_by_sql(sql) @rates = @dest.find_rates_and_tariffs(correct_owner_id, @callshop) else @rates = @dest.find_rates_and_tariffs(correct_owner_id) end end render(:layout => false) end def prefix_finder_find_country @phrase = params[:prefix].gsub(/['"]/, '') if params[:prefix] @dirs = Direction.find( :all, :conditions => ["SUBSTRING(name, 1, LENGTH(?)) = ?", @phrase, @phrase] ) if @phrase and @phrase.length > 1 render(:layout => false) end def rate_finder_find if params[:prefix] @phrase = params[:prefix].to_s.gsub(/[^\d]/, '') if params[:prefix] phrase = [] arr = @phrase.split('') if @phrase arr.size.times { |i| phrase << arr[0..i].join() } @dest = Destination.find(:all, :conditions => "prefix in (#{phrase.join(",")})", :order => "prefix desc") if phrase.size>0 id_string = [] @dest.each { |d| id_string << d.id } if @dest @rates = Stat.find_rates_and_tariffs_by_number(correct_owner_id, id_string, phrase) if id_string.size>0 end render(:layout => false) end # /Prefix Finder ############################################################### # GOOGLE MAPS ################################################################## def google_maps @page_title = _('Google_Maps') @page_icon = "world.png" @devices = Device.find( :all, :include => :user, :conditions => "users.owner_id = #{current_user.id} AND name NOT LIKE 'mor_server%' AND ipaddr > 0 AND ipaddr != '0.0.0.0' AND user_id > -1 AND '192.168.' != SUBSTRING(ipaddr, 1, LENGTH('192.168.')) AND '10.' != SUBSTRING(ipaddr, 1, LENGTH('10.')) AND ((CAST(SUBSTRING(ipaddr, 1,6) AS DECIMAL(6,3)) > 172.31) or (CAST(SUBSTRING(ipaddr, 1,6) AS DECIMAL(6,3)) < 172.16))") @providers = Provider.find(:all, :conditions => "user_id = #{current_user.id} AND server_ip > 0 AND server_ip != '0.0.0.0' AND hidden = 0") @servers = Server.find(:all, :conditions => "server_ip > 0 AND server_ip != '0.0.0.0'") session[:google_active] = 0 end def google_active if mor_11_extend? and session[:usertype] == "reseller" @calls = Activecall.find(:all, :include => [:provider], :conditions => ["owner_id = #{current_user.id}"]) else @calls = Activecall.find(:all, :include => [:provider]) end end def check_mor_11_extend_new unless session[:usertype] == "admin" or (session[:usertype] == "reseller" and mor_11_extend?) flash[:notice] = _('You_are_not_authorized_to_view_this_page') redirect_to :controller => "callc", :action => "login" and return false end end def hangup_cause_codes_stats #ticket 5672 only if reseller pro addon is active, reseller that has own providers can access #hangup cause statistics page. if current_user.is_reseller? and !current_user.reseller_allowed_to_view_hgc_stats? dont_be_so_smart redirect_to :controller => :callc, :action => :main and return false end @page_title = _('Hangup_cause_codes_stats') @page_icon = "chart_pie.png" change_date if params[:back] @back = params[:back] if params[:back].to_i == 2 @direction = Direction.find(:first, :conditions => ["code=?", params[:country_code]]) @country_id = @direction.id end end @user_id = params[:s_user] ? params[:s_user].to_i : -1 @device_id = params[:s_device] ? params[:s_device].to_i : -1 @provider_id = params[:provider_id] ? params[:provider_id].to_i : -1 @country_id = params[:country_id] ? params[:country_id].to_i : -1 if params[:provider_id] and params[:provider_id].to_i != -1 @provider = current_user.providers.find(:first, :conditions => {:id => params[:provider_id]}) unless @provider dont_be_so_smart redirect_to :controller => :callc, :action => :main and return false end end if params[:s_user] and params[:s_user].to_i != -1 @user = User.find(:first, :conditions => {:id => params[:s_user]}) unless @user dont_be_so_smart redirect_to :controller => :callc, :action => :main and return false end end @country = Direction.find(:first, :conditions => ["id = ?", @country_id]) if @country_id.to_i > -1 if params[:direction_code] @country = Direction.find(:first, :conditions => ["code = ?", params[:direction_code]]) end @code = @country.code if @country @providers = current_user.load_providers(:all, :conditions => 'hidden=0', :order => 'name ASC') @users = User.find_all_for_select(corrected_user_id) @countries = Direction.find(:all, :order => "name ASC") @calls, @Calls_graph, @hangupcusecode_graph, @calls_size = Call.hangup_cause_codes_stats({:provider_id => @provider_id, :device_id => @device_id, :country_code => @code, :user_id => @user_id, :current_user => current_user, :a1 => session_from_date, :a2 => session_till_date}) end def calls_by_scr @page_title = _('Calls_by_src') @page_icon = "chart_pie.png" cond="" des = '' descond='' descond1='' @prov = -1 @coun = -1 if params[:country_id] @country_id = params[:country_id] end if params[:provider_id] if params[:provider_id].to_i != -1 @provider = Provider.find(params[:provider_id]) cond +=" ((hcalls.provider_id = #{q params[:provider_id]} and hcalls.callertype = 'Local') OR (hcalls.did_provider_id = #{q params[:provider_id]} and hcalls.callertype = 'Outside')) AND " @prov = @provider.id end end @providers = Provider.find(:all, :conditions => ['hidden=?', 0], :order => 'name ASC') if @country_id if @country_id.to_i != -1 @country = Direction.find(@country_id) @coun = @country.id des+= 'destinations, ' descond +=" AND directions.code ='#{@country.code}' " descond1 +=" AND destinations.direction_code ='#{@country.code}' " end end @countries = Direction.find(:all, :order => "name ASC") change_date sql= "SELECT directions.name, des.direction_code, des.name as 'des_name', des.prefix, des.subcode, count(hcalls.id) as 'calls' FROM directions JOIN destinations as des on (des.direction_code = directions.code) JOIN calls as hcalls on (hcalls.prefix = des.prefix) WHERE #{cond} hcalls.calldate BETWEEN '#{session_from_datetime}' AND '#{session_till_datetime}' #{descond} AND LENGTH(src) >= 10 group by des.id" @res = ActiveRecord::Base.connection.select_all(sql) # my_debug sql sql= "SELECT directions.name, directions.code, count(hcalls.id) as 'calls' FROM directions JOIN destinations as des on (des.direction_code = directions.code) JOIN calls as hcalls on (hcalls.prefix = des.prefix) WHERE #{cond} hcalls.calldate BETWEEN '#{session_from_datetime}' AND '#{session_till_datetime}' #{descond} AND LENGTH(src) >= 10 group by directions.id" @res3 = ActiveRecord::Base.connection.select_all(sql) # my_debug sql sql= "SELECT count(hcalls.id) as 'calls' FROM destinations JOIN calls as hcalls on (hcalls.prefix = destinations.prefix) WHERE #{cond} hcalls.calldate BETWEEN '#{session_from_datetime}' AND '#{session_till_datetime}' #{descond1} AND LENGTH(src) < 10 " @res2 = ActiveRecord::Base.connection.select_all(sql) # my_debug @res2 # my_debug sql end def resellers @page_title = _('Resellers') @page_icon = "user_gray.png" sql = "select users.id, users.username, users.first_name, users.last_name, s_calls.calls as 'f_calls', s_tariffs.tariffs as 'f_tariffs', s_cardgroups.cardgroups as 'f_cardgroups', s_cards.cards as 'f_cards', s_users.users as 'f_users', s_devices.devices as 'f_devices', acc_groups.name as 'group_name', acc_groups.id as 'group_id', s_dids.dids as f_dids, #{SqlExport.nice_user_sql} from users LEFT JOIN acc_groups ON (users.acc_group_id = acc_groups.id) left join (SELECT COUNT(calls.id) as 'calls', reseller_id FROM calls group by calls.reseller_id) as s_calls on(s_calls.reseller_id = users.id) left join (SELECT COUNT(tariffs.id) as 'tariffs', owner_id FROM tariffs group by tariffs.owner_id) as s_tariffs on(s_tariffs.owner_id = users.id) left join (SELECT COUNT(cardgroups.id) as 'cardgroups', owner_id FROM cardgroups group by cardgroups.owner_id) as s_cardgroups on(s_cardgroups.owner_id = users.id) left join (SELECT COUNT(cards.id) as 'cards', owner_id FROM cards group by cards.owner_id) as s_cards on(s_cards.owner_id = users.id) left join (SELECT COUNT(users.id) as 'users', owner_id FROM users group by users.owner_id) as s_users on(s_users.owner_id = users.id) left join (SELECT COUNT(devices.id) as 'devices', users.owner_id FROM devices left join users on (devices.user_id = users.id) where users.owner_id > 0 group by users.owner_id) as s_devices on(s_devices.owner_id = users.id) left join (SELECT COUNT(dids.id) AS 'dids', reseller_id FROM dids GROUP BY dids.reseller_id) AS s_dids ON(s_dids.reseller_id = users.id) where users.usertype = 'reseller' and users.hidden = 0 ORDER BY nice_user ASC" #my_debug sql @resellers = User.find_by_sql(sql) end def calls_per_day @page_title = _('Calls_per_day') @page_icon = "chart_bar.png" cond="" des = '' des2 = '' des3 = '' @prov = -1 @coun = -1 @user_id = -1 @directions = -1 up, rp, pp = current_user.get_price_calculation_sqls if params[:country_id] @country_id = params[:country_id] end if params[:provider_id] if params[:provider_id].to_i != -1 @provider = Provider.find(:first, :conditions => ["id = ? ", params[:provider_id]]) @prov = @provider.id cond +=" (calls.provider_id = '#{params[:provider_id].to_i}' OR calls.did_provider_id = '#{params[:provider_id].to_i}') AND " end end @providers = Provider.find(:all, :conditions => ['hidden=?', 0], :order => 'name ASC') if params[:user_id] if params[:user_id].to_i != -1 @user = User.find(:first, :conditions => ["id = ?", params[:user_id]]) cond +=" calls.user_id = '#{@user.id}' AND " @user_id = @user.id end end @users = User.find_all_for_select(corrected_user_id) if params[:reseller_id] if params[:reseller_id].to_i != -1 @reseller = User.find(:first, :conditions => ["id = ?", params[:reseller_id]]) cond +=" calls.reseller_id = '#{@reseller.id}' AND " @reseller_id = @reseller.id end end @resellers = User.find(:all, :conditions => 'usertype = "reseller"', :order => 'first_name ASC') if params[:direction] if params[:direction].to_i != -1 if params[:direction].to_s == "Incoming" cond +=" calls.did_id > 0 AND " else cond +=" calls.did_id = 0 AND " end @direction = params[:direction] end end owner_id = correct_owner_id if owner_id != 0 cond += " reseller_id ='#{owner_id}' AND " end if @country_id if @country_id.to_i != -1 @country = Direction.find(@country_id) @coun = @country.id des3 += "destinations JOIN" des2 += "ON (calls.prefix = destinations.prefix)" des +=" AND destinations.direction_code ='#{@country.code}' " end end @countries = Direction.find(:all, :order => "name ASC") change_date # logger.fatal current_user.time_zone.to_i # logger.fatal User.system_time_ofset.to_i calldate = "(calls.calldate + INTERVAL #{current_user.time_zone.to_i - User.system_time_ofset.to_i} HOUR)" sql = "SELECT EXTRACT(YEAR FROM #{calldate}) as year, EXTRACT(MONTH FROM #{calldate}) as month, EXTRACT(day FROM #{calldate}) as day, Count(calls.id) as 'calls' , SUM(IF(calls.billsec > 0, calls.billsec, CEIL(calls.real_billsec) )) as 'duration', SUM(#{up}) as 'user_price', SUM(#{rp}) as 'resseler_price', SUM(#{pp}) as 'provider_price', SUM(IF(disposition!='ANSWERED',1,0)) as 'fail' FROM #{des3} calls #{des2} #{SqlExport.left_join_reseler_providers_to_calls_sql} WHERE #{cond} calldate BETWEEN '#{session_from_datetime}' AND '#{session_till_datetime}' #{des} GROUP BY year, month, day" @res = ActiveRecord::Base.connection.select_all(sql) sql_total = "SELECT Count(calls.id) as 'calls' , SUM(IF(calls.billsec > 0, calls.billsec, CEIL(calls.real_billsec) )) as 'duration', SUM(#{up}) as 'user_price', SUM(#{rp}) as 'resseler_price', SUM(#{pp}) as 'provider_price', SUM(IF(disposition!='ANSWERED',1,0)) as 'fail' FROM #{des3} calls #{des2} #{SqlExport.left_join_reseler_providers_to_calls_sql} WHERE #{cond} calldate BETWEEN '#{session_from_datetime}' AND '#{session_till_datetime}' #{des}" @res_total = ActiveRecord::Base.connection.select_all(sql_total) end def first_activity @page_title = _('First_activity') @page_icon = "chart_bar.png" @help_link = "http://wiki.kolmisoft.com/index.php/First_Activity" change_date a1 = session_from_date a2 = session_till_date =begin #find all users users = User.find(:all, :conditions => "hidden = 0") for user in users #check if has logged first activity action = Action.find(:first, :conditions => "user_id = #{user.id} AND action = 'first_call'") # no such action, looking for first call of the user if not action call = Call.find(:first, :conditions => "calls.user_id = #{user.id}", :order => "calldate ASC" ) if call #call found, creating action MorLog.my_debug("No action for first_call found, creating for user with id: #{user.id}") action = Action.new action.user_id = user.id action.date = call.calldate action.action = 'first_call' action.data = call.id action.save end else # we have action so we can find call call = Call.find_by_id(action.data.to_i) end end =end @size = Action.set_first_call_for_user(session_from_date, session_till_date) @total_pages = (@size.to_f / session[:items_per_page].to_f).ceil @page = 1 @page = params[:page].to_i if params[:page] @page = @total_pages.to_i if params[:page].to_i > @total_pages @page = 1 if params[:page].to_i < 0 @fpage = ((@page -1) * session[:items_per_page]).to_i =begin sql = "SELECT calldate, user_id, card_id, c.sb, users.first_name, users.last_name, users.username FROM calls LEFT JOIN (SELECT COUNT(subscriptions.id) AS sb, subscriptions.user_id AS su_id FROM subscriptions WHERE ((activation_start < '#{a1}' AND activation_end BETWEEN '#{a1}' AND '#{a2}') OR (activation_start BETWEEN '#{a1}' AND '#{a2}' AND activation_end < '#{a2}') OR (activation_start > '#{a1}' AND activation_end < '#{a2}') OR (activation_start < '#{a1}' AND activation_end > '#{a2}')) GROUP BY subscriptions.user_id) AS c on (c.su_id = calls.user_id ) LEFT JOIN users on (users.id = calls.user_id) WHERE calldate < '#{session_till_datetime}' AND calls.user_id != -1 GROUP BY user_id ORDER BY calldate ASC LIMIT #{@fpage}, #{@tpage}" =end #my_debug sql # sql3 = "SELECT actions.date as 'calldate', actions.data2 as 'card_id', c.sb, users.first_name, users.last_name, users.username, users.id, actions.user_id FROM users # JOIN actions ON (actions.user_id = users.id) # LEFT JOIN # (SELECT COUNT(subscriptions.id) AS sb, subscriptions.user_id AS su_id # FROM subscriptions WHERE ((activation_start < '#{a1}' AND activation_end BETWEEN '#{a1}' AND '#{a2}') OR (activation_start BETWEEN '#{a1}' AND '#{a2}' AND activation_end < '#{a2}') OR (activation_start > '#{a1}' AND activation_end < '#{a2}') OR (activation_start < '#{a1}' AND activation_end > '#{a2}')) GROUP BY subscriptions.user_id) AS c on (c.su_id = users.id ) # WHERE actions.action = 'first_call' and actions.date BETWEEN '#{session_from_datetime}' AND '#{session_till_datetime}' # GROUP BY user_id # ORDER BY date ASC # LIMIT #{@fpage}, #{session[:items_per_page].to_i}" sql3= "SELECT actions.date as 'calldate', actions.data2 as 'card_id', users.first_name, users.last_name, users.username, users.id, actions.user_id FROM users JOIN actions ON (actions.user_id = users.id) WHERE actions.action = 'first_call' and actions.date BETWEEN '#{session_from_datetime}' AND '#{session_till_datetime}' GROUP BY user_id ORDER BY date ASC LIMIT #{@fpage}, #{session[:items_per_page].to_i}" @res = ActiveRecord::Base.connection.select_all(sql3) # @all_res = @res # @res = [] # # iend = ((session[:items_per_page] * @page) - 1) # iend = @all_res.size - 1 if iend > (@all_res.size - 1) # for i in ((@page - 1) * session[:items_per_page])..iend # @res << @all_res[i] # end # # # @subscriptions = 0 # @user = [] # for r in @res # @subscriptions+= r['sb'].to_i # if (r['user_id'].to_i != -1) and (r['user_id'].to_s != "") and (r['card_id'].to_i == 0 ) # user = User.find(:first, :conditions => "id = #{r['user_id']}") if r['user_id'].to_s.length >= 0 # @user[r['user_id'].to_i] = user if r['user_id'].to_i >= 0 # end # end end def subscriptions_stats @page_title = _('Subscriptions') @page_icon = "chart_bar.png" change_date a1 = session_from_date a2 = session_till_date @date_from = session_from_date sql = "SELECT COUNT(subscriptions.id) AS sub_size FROM subscriptions WHERE ((activation_start < '#{a1}' AND activation_end BETWEEN '#{a1}' AND '#{a2}') OR (activation_start BETWEEN '#{a1}' AND '#{a2}' AND activation_end < 'a2') OR (activation_start > '#{a1}' AND activation_end < '#{a2}') OR (activation_start < '#{a1}' AND activation_end > '#{a2}'))" @res = ActiveRecord::Base.connection.select_all(sql) sql = "SELECT COUNT(subscriptions.id) AS sub_size FROM subscriptions WHERE activation_start = '#{a1}'" @res2 = ActiveRecord::Base.connection.select_all(sql) sql = "SELECT users.id, users.username, users.first_name, users.last_name FROM subscriptions JOIN users on (subscriptions.user_id = users.id) WHERE ((activation_start < '#{a1}' AND activation_end BETWEEN '#{a1}' AND '#{a2}') OR (activation_start BETWEEN '#{a1}' AND '#{a2}' AND activation_end < 'a2') OR (activation_start > '#{a1}' AND activation_end < '#{a2}') OR (activation_start < '#{a1}' AND activation_end > '#{a2}')) GROUP BY users.id" @res3 = ActiveRecord::Base.connection.select_all(sql) params[:page] ? @page = params[:page].to_i : @page = 1 @total_pages = (@res3.size.to_f / session[:items_per_page].to_f).ceil @all_res = @res3 @res3 = [] iend = ((session[:items_per_page] * @page) - 1) iend = @all_res.size - 1 if iend > (@all_res.size - 1) for i in ((@page - 1) * session[:items_per_page])..iend @res3 << @all_res[i] end end def subscriptions_first_day @page_title = _('First_day_subscriptions') @page_icon = "chart_bar.png" @date = session_from_date sql = "SELECT users.id, users.username, users.first_name, users.last_name FROM users JOIN (SELECT users.id AS suser_id, subscriptions.id as sub_id FROM users JOIN subscriptions ON (subscriptions.user_id = users.id AND subscriptions.activation_start BETWEEN '#{@date} 01:01:01' AND '#{@date} 23:59:59') GROUP BY users.id) as a on (users.id != a.suser_id ) where users.owner_id='#{session[:user_id]}' and users.hidden = 0" @res = ActiveRecord::Base.connection.select_all(sql) if @res.size.to_i == 0 sql = "SELECT users.id, users.username, users.first_name, users.last_name FROM users where users.owner_id='#{session[:user_id]}' and users.hidden = 0" @res = ActiveRecord::Base.connection.select_all(sql) end @page = 1 @page = params[:page].to_i if params[:page] @total_pages = (@res.size.to_f / session[:items_per_page].to_f).ceil @all_res = @res @res = [] iend = ((session[:items_per_page] * @page) - 1) iend = @all_res.size - 1 if iend > (@all_res.size - 1) for i in ((@page - 1) * session[:items_per_page])..iend @res << @all_res[i] end end def action_log @page_title = _('Action_log') @page_icon = "chart_bar.png" @help_link = "http://wiki.kolmisoft.com/index.php/Action_log" if session[:usertype] == 'user' dont_be_so_smart redirect_to :controller => "callc", :action => "main" and return false end change_date a1 = session_from_date a2 = session_till_date session[:action_log_stats_options] ? @options = session[:action_log_stats_options] : @options = {:order_by => "action", :order_desc => 0, :page => 1} # search paramaters params[:page] ? @options[:page] = params[:page].to_i : (params[:clean]) ? @options[:page] = 1 : (@options[:page] = 1 if !@options[:page]) params[:action_type] ? @options[:s_type] = params[:action_type].to_s : (params[:clean]) ? @options[:s_type] = "all" : (@options[:s_type]) ? @options[:s_type] = session[:action_log_stats_options][:s_type] : @options[:s_type] = "all" params[:user_id] ? @options[:s_user] = params[:user_id].to_s : (params[:clean]) ? @options[:s_user] = -1 : (@options[:s_user]) ? @options[:s_user] = session[:action_log_stats_options][:s_user] : @options[:s_user] = -1 params[:processed] ? @options[:s_processed] = params[:processed].to_s : (params[:clean]) ? @options[:s_processed] = -1 : (@options[:s_processed]) ? @options[:s_processed] = session[:action_log_stats_options][:s_processed] : @options[:s_processed] = -1 #params[:s_int_ch] ? @options[:s_int_ch] = params[:s_int_ch].to_i : (params[:clean]) ? @options[:s_int_ch] = 0 : (@options[:s_int_ch])? @options[:s_int_ch] = session[:action_log_stats_options][:s_int_ch] : @options[:s_int_ch] = 0 params[:target_type] ? @options[:s_target_type] = params[:target_type].to_s : (params[:clean]) ? @options[:s_target_type] = '' : (@options[:s_target_type]) ? @options[:s_target_type] = session[:action_log_stats_options][:s_target_type] : @options[:s_target_type] = '' params[:target_id] ? @options[:s_target_id] = params[:target_id].to_s : (params[:clean]) ? @options[:s_target_id] = '' : (@options[:s_target_id]) ? @options[:s_target_id] = session[:action_log_stats_options][:s_target_id] : @options[:s_target_id] = '' params[:did] ? @options[:s_did] = params[:did].to_s : (params[:clean]) ? @options[:s_did] = '' : (@options[:s_did]) ? @options[:s_did] = session[:action_log_stats_options][:s_did] : @options[:s_did] = '' # order params[:order_desc] ? @options[:order_desc] = params[:order_desc].to_i : (@options[:order_desc] = 1 if !@options[:order_desc]) params[:order_by] ? @options[:order_by] = params[:order_by].to_s : @options[:order_by] == "acc" order_by = Action.actions_order_by(@options) @users = User.find_all_for_select(corrected_user_id) @dids = Did.find(:all) @res = Action.find(:all, :select => "DISTINCT(actions.action)", :order => "actions.action") cond, cond_arr, join = Action.condition_for_action_log_list(current_user, a1, a2, params[:s_int_ch], @options) # page params @ac_size = Action.count(:all, :conditions => [cond.join(" AND ")] + cond_arr, :joins => join, :select => "actions.id") @not_reviewed_actions = Action.find(:all, :conditions => [(['processed = 0'] + cond).join(" AND ")] + cond_arr, :joins => join, :limit => 1).size.to_i == 1 @options[:page] = @options[:page].to_i < 1 ? 1 : @options[:page].to_i @total_pages = (@ac_size.to_f / session[:items_per_page].to_f).ceil @options[:page] = @total_pages if @options[:page].to_i > @total_pages.to_i and @total_pages.to_i > 0 fpage = ((@options[:page] -1) * session[:items_per_page]).to_i @search = 1 logger.fatal cond_arr # search @actions = Action.find(:all, :select => " actions.*, users.username, users.first_name, users.last_name ", :conditions => [cond.join(" AND ")] + cond_arr, :joins => join, :order => order_by, :limit => "#{fpage}, #{session[:items_per_page].to_i}") session[:action_log_stats_options] = @options end def action_log_mark_reviewed a1 = session_from_date a2 = session_till_date session[:action_log_stats_options] ? @options = session[:action_log_stats_options] : @options = {:order_by => "action", :order_desc => 0, :page => 1} cond, cond_arr, join = Action.condition_for_action_log_list(current_user, a1, a2, 0, @options) @actions = Action.find(:all, :select => " actions.*", :conditions => [cond.join(" AND ")] + cond_arr, :joins => join) if @actions @actions.each { |a| if a.processed == 0 a.processed = 1 a.save end } end flash[:status] = _('Actions_marked_as_reviewed') redirect_to :action => :action_log end def action_processed action = Action.find(params[:id]) if action.processed.to_i == 1 action.processed = 0 else action.processed = 1 end action.save @user = params[:user].to_s @action = params[:s_action] @processed = params[:procc] flash[:status] = _('Action_marked_as_reviewed') redirect_to :action => "action_log", :user_id => @user, :processed => @processed, :action_type => @action end def load_stats @page_title = _('Load_stats') @page_icon = "chart_bar.png" change_date @providers = current_user.providers.find(:all, :conditions => ['hidden=?', 0]) @users = User.find_all_for_select(correct_owner_id, {:exclude_owner => true}) @resellers = User.find(:all, :conditions => 'usertype = "reseller"') if current_user.usertype != 'reseller' @dids = Did.find(:all) @servers = Server.find(:all) end @default = {:s_user => -1, :s_provider => -1, :s_did => -1, :s_device => -1, :s_direction => -1, :s_server => -1, :s_reseller => -1} session[:stats_load_stats_options] ? @options = session[:stats_load_stats_options] : @options = @default @options[:s_user] = params[:s_user] if params[:s_user] @options[:s_reseller] = params[:s_reseller] if params[:s_reseller] @options[:s_did] = params[:s_did] if params[:s_did] and current_user.usertype != 'reseller' @options[:s_device] = params[:device_id] if params[:device_id] @options[:s_provider] = params[:s_provider] if params[:s_provider] @options[:s_direction] = params[:s_direction] if params[:s_direction] @options[:s_server] = params[:s_server] if params[:s_server] and current_user.usertype != 'reseller' @options[:a1] ="#{(session_from_date.to_s + " 00:00:00")}" @options[:a2] ="#{(session_from_date.to_s + " 23:59:59")}" @options[:current_user] = current_user @calls_answered, @calls_all =Call.calls_for_laod_stats(@options) # logger.info @calls_answered.size.to_i # logger.info @calls_all.size.to_i n = 1440 min1 =[] min2 =[] i=0 n.times do min1[i]=0 min2[i]=0 i+=1 end if @calls_all.size.to_i >0 for cal in @calls_all h = cal.calldate.strftime("%H") m = cal.calldate.strftime("%M") h = h.to_i * 60 m = h.to_i + m.to_i min2[m.to_i]+=1 end end if @calls_answered.size.to_i >0 for cal in @calls_answered h = cal.calldate.strftime("%H") m = cal.calldate.strftime("%M") h = h.to_i * 60 m = h.to_i + m.to_i #min2[m]+=1 d = cal.duration.to_i / 60 if (cal.duration.to_i % 60) > 0 d+= 1 end i = m.to_i d.times do if i.to_i < 1440 min1[i.to_i]+=1 i=i+1 end end end end @Call_answered_graph="" i=0 n.times do h2 = (i / 60) m2 = (i % 60) time = Time.mktime(session[:year_from], session[:month_from], session[:day_from], h2, m2, 0).strftime("%H:%M") @Call_answered_graph += time.to_s + ";" + min1[i].to_s + ";"+ min2[i].to_s + "\\n" i+=1 end end def check_owner_for_user(user_id) user = User.find(user_id) if user.owner_id != session[:user_id].to_i dont_be_so_smart redirect_to :controller => "callc", :action => "main" and return false end end def nice_user(user) nu = user.username nu = user.first_name + " " + user.last_name if user.first_name.length + user.last_name.length > 0 nu end def link_nice_user(user) link_to nice_user(user), :controller => "users", :action => "edit", :id => user.idd end def truncate_active_calls if current_user.is_admin? Activecall.delete_all redirect_to :controller => "stats", :action => "active_calls" and return false else dont_be_so_smart redirect_to :controller => "callc", :action => "main" and return false end end private def no_cache response.headers["Last-Modified"] = Time.now.httpdate response.headers["Expires"] = 0 # HTTP 1.0 response.headers["Pragma"] = "no-cache" # HTTP 1.1 'pre-check=0, post-check=0' (IE specific) response.headers["Cache-Control"] = 'no-store, no-cache, must-revalidate, max-age=0, pre-check=0, post-check=0' end def active_calls_longer_error(calls) for call in calls ba = Thread.new { active_calls_longer_error_send_email(call["user_id"].to_s, call["provider_id"].to_s, call["server_id"].to_s) } #ba.join kam ji cia joininti? MorLog.my_debug "active_calls_longer_error" end end def active_calls_longer_error_send_email(user, provider, server) address = Confline.get_value("Exception_Support_Email").to_s subject = "Active calls longer error on : #{Confline.get_value("Company")}" message = "URL: #{Web_URL}\n" message += "User ID: #{user.to_s}\n" message += "Provider ID: #{provider.to_s}\n" message += "Server ID: #{server.to_s}\n" message += "----------------------------------------\n" # disabling for now #`/usr/local/mor/sendEmail -f 'support@kolmisoft.com' -t '#{address}' -u '#{subject}' -s 'smtp.gmail.com' -xu 'crashemail1' -xp 'crashemail199' -m '#{message}' -o tls='auto'` MorLog.my_debug('Crash email sent') end def check_authentication redirect_to :controller => "callc", :action => "main" if current_user.nil? end def check_reseller_in_providers if current_user.is_reseller? and (current_user.own_providers.to_i == 0 or !reseller_pro_active?) dont_be_so_smart redirect_to :controller => :callc, :action => :main and return false end end def find_user_from_id_or_session params[:id] ? user_id = params[:id] : user_id = session[:user_id] @user=User.find(:first, :conditions => ["id = ?", user_id]) unless @user flash[:notice] = _('User_was_not_found') redirect_to :controller => :callc, :action => :main and return false end if session[:usertype] == 'reseller' if @user.id != session[:user_id] and @user.owner_id != session[:user_id] dont_be_so_smart redirect_to :controller => :callc, :action => :main and return false end end if session[:usertype] == 'user' and @user.id != session[:user_id] dont_be_so_smart redirect_to :controller => :callc, :action => :main and return false end end def last_calls_stats_parse_params default = { :items_per_page => session[:items_per_page].to_i, :page => "1", :s_direction => "outgoing", :s_call_type => "all", :s_device => "all", :s_provider => "all", :s_hgc => 0, :search_on => 1, :s_user => "all", :user => nil, :s_did => "all", :s_did_pattern => "", :s_destination => "", :order_by => "time", :order_desc => 0, :s_country => '', :s_reseller => "all", :s_caller_id => nil, :s_reseller_did => 'all' } options = ((params[:clear] || !session[:last_calls_stats]) ? default : session[:last_calls_stats]) default.each { |key, value| options[key] = params[key] if params[key] } change_date_to_present if params[:clear] options[:from] = session_from_datetime options[:till] = session_till_datetime options[:order_by_full] = options[:order_by] + (options[:order_desc] == 1 ? " DESC" : " ASC") options[:order] = Call.calls_order_by(params, options) options[:direction] = options[:s_direction] options[:call_type] = options[:s_call_type] options[:destination] = (options[:s_destination].to_s.strip.match(/\A[0-9%]+\Z/) ? options[:s_destination].to_s.strip : "") options[:caller_id] = options[:s_caller_id] if options[:s_caller_id] exchange_rate = Currency.count_exchange_rate(session[:default_currency], session[:show_currency]).to_f options[:exchange_rate] = exchange_rate options end def last_calls_stats_user(user, options) devices = user.devices(:conditions => "device_type != 'FAX'") device = Device.find_by_id(options[:s_device]) if options[:s_device] != "all" and !options[:s_device].blank? return devices, device end def last_calls_stats_reseller(options) user = User.find_by_id(options[:s_user]) if options[:s_user] != "all" and !options[:s_user].blank? device = Device.find_by_id(options[:s_device]) if options[:s_device] != "all" and !options[:s_device].blank? if user devices = user.devices(:conditions => "device_type != 'FAX'") else devices = Device.find_all_for_select(corrected_user_id) end users = User.find_all_for_select(corrected_user_id) if Confline.get_value('Show_HGC_for_Resellers').to_i == 1 hgcs = Hangupcausecode.find_all_for_select hgc = Hangupcausecode.find_by_id(options[:s_hgc]) if options[:s_hgc].to_i > 0 end if current_user.reseller_allow_providers_tariff? providers = current_user.load_providers(:all, :select => "id, name", :order => 'providers.name ASC') if options[:s_provider].to_i > 0 #KRISTINA ticket number 3276 #provider = Provider.find(:first, :conditions => ["providers.id = ? OR common_use = 1", options[:s_provider]]) provider = Provider.find(:first, :conditions => ["providers.id = ?", options[:s_provider]]) unless provider dont_be_so_smart redirect_to :controller => :callc, :action => :main and return false end end else providers = nil; provider = nil end did = Did.find_by_id(options[:s_did]) if options[:s_did] != "all" and !options[:s_did].blank? dids = Did.find_all_for_select return users, user, devices, device, hgcs, hgc, providers, provider, did, dids end def last_calls_stats_admin(options) user = User.find_by_id(options[:s_user]) if options[:s_user] != "all" and !options[:s_user].blank? device = Device.find_by_id(options[:s_device]) if options[:s_device] != "all" and !options[:s_device].blank? did = Did.find_by_id(options[:s_did]) if options[:s_did] != "all" and !options[:s_did].blank? hgc = Hangupcausecode.find_by_id(options[:s_hgc]) if options[:s_hgc].to_i > 0 users = User.find_all_for_select dids = Did.find_all_for_select hgcs = Hangupcausecode.find_all_for_select providers = Provider.find_all_for_select provider = Provider.find_by_id(options[:s_provider]) if options[:s_provider].to_i > 0 resellers = User.find(:all, :conditions => 'usertype = "reseller"') resellers = [] if !resellers reseller = User.find_by_id(options[:s_reseller]) if options[:s_reseller] != "all" and !options[:s_reseller].blank? if user devices = user.devices(:conditions => "device_type != 'FAX'") else devices = Device.find_all_for_select end return users, user, devices, device, hgcs, hgc, dids, did, providers, provider, reseller, resellers end def last_calls_stats_set_variables(options, values) options.merge(values.reject { |key, value| value.nil? }) end def get_price_exchange(price, cur) exrate = Currency.count_exchange_rate(cur, current_user.currency.name) rate_cur = Currency.count_exchange_prices({:exrate => exrate, :prices => [price.to_f]}) return rate_cur.to_f end end