require "sqlite3"



Shoes.app :width => 730, :height => 730, :title => "Sales Analysis" , :resizable => false do
  
  def showReport
    @num = 2

    @flowAnalysis.clear if @flowAnalysis != nil
    @flowAnalysis = flow :top => 60, :left => 40 do
      database = SQLite3::Database.open("Database/CSDB.db")
      flow :margin_top => 40 do;end
      database.execute("DELETE FROM salesAnalysis")
      
      @i = 0
    # #  
      # =================================================================================
      # = @i = the number of items on the menu that were sold used to divide to get avg =
      # =================================================================================
      database.execute("select count(quantity),item,sum(quantity*(salesPrice-foodCost)) from sales WHERE salesPrice != 0  group by item") do |count,item,gross|# where quantity != 0 group by item") do |count,item, q, gross|
        @i += 1
        # Shoes.p "#{count},#{item},#{gross}"
      end
      database.execute("select sum(quantity), sum(quantity*(salesPrice-foodCost)) from sales WHERE salesPrice != 0 ") do |qua, mar|# where quantity != 0 group by item") do |count,item, q, gross|
        @averageNumberSold = qua.to_f/@i.to_f
        @averageMargin = mar.to_f/@i.to_f
        # Shoes.p "#{@averageNumberSold}, #{@averageMargin}"
      end
      database.execute("select count(quantity),item,sum(quantity*(salesPrice-foodCost)) from sales WHERE salesPrice != 0  group by item") do |count,item,gross|# where quantity != 0 group by item") do |count,item, q, gross|
        popc, profc = count.to_f/@averageNumberSold,gross.to_f/@averageMargin
        if      popc == 1 and profc  == 1 : label = "avg"
          elsif popc <= 1 and profc  >= 1 : label = "puzzle"
          elsif popc >= 1 and profc  <= 1 : label = "plow horse"
          elsif popc >= 1 and profc  >= 1 : label = "star"
          elsif popc < 1  and profc   < 1 : label = "dog"
        end
        # Shoes.p "item #{item} popc #{popc}, profc #{profc}, label #{label}"
        
        database.execute("insert into salesAnalysis values('#{item}', #{popc}, #{profc}, '#{label}')")
      end
      # (item TEXT, popularity FLOAT, profitabilty FLOAT, label TEXT)
      database.execute("select item,popularity,profitabilty,label from salesAnalysis order by profitabilty desc") do |item,pop,prof,label|
        profc = sprintf "%5.2f",prof
        popc  = sprintf "%5.2f",pop
        pop, prof = pop.to_f, prof.to_f
        flow do
          background COLORS[@num % 2], :left => -40
          stack :width => 0.2 do
            para item
          end
          stack :width => 0.2 do
            para popc
          end
          stack :width => 0.15 do
            para profc
          end
          stack :width => 0.2 do
            para label
          end
          stack :width => 0.1 do
            
              if    pop <= 1 and prof >  1 : fill lawngreen; rotate 40;star(0,top+15,5,9,4)
              elsif pop >= 1 and prof <= 1 : nofill;         rotate 40;star(0,top+15,5,7,3)
              elsif pop >  1 and prof >  1 : fill yellow;    rotate 40;star(0,top+15,5,12,5)
              elsif pop <  1 and prof <  1 :                           oval(-6,top+10,12)
            end
            # myShape
          end
          @num += 1
        end#flow
      end
      flow :top => 0, :left => -40, :height => 40, :width => 1.2 do
        background white..gray, :left => -40
        stack :width => 0.2, :margin_left => 40 do
          caption "Item"
        end
        stack :width => 0.15 do
          caption "Popularity"
        end
        stack :width => 0.15 do
          caption "Profitability"
        end
        stack :width => 0.25 do
          caption "Label"
          # caption "AVG POP #{@pop} AVG PROF #{@prof}"
        end
      end
      
      
      database.close
      Shoes.p database.closed?
    end#@flow analysis
  end#def
    
    def showToday
      @total = []
      @flowAnalysis.clear if @flowAnalysis != nil
      @flowAnalysis = flow :top => 60, :left => 40 do
        @num = 2
        database = SQLite3::Database.open("Database/CSDB.db")
        flow :margin_top => 40 do;end
      
        database.execute("SELECT item FROM sales WHERE date = '#{Time.now.strftime("%m/%d/%Y")}' GROUP BY item") do |item|
          database.execute("SELECT sum(quantity), salesPrice FROM sales WHERE item = '#{item}'") do |row|#sum, salesPrice|
            @total << row[1].to_f*row[0].to_f
            flow do
              background COLORS[@num % 2], :left => -40
              stack :width => 0.25 do
                para item
              end
              stack :width => 0.15 do
                para row[0]
              end
              stack :width => 0.15 do
                item_total = sprintf "%-5.2f", row[1].to_f*row[0].to_f
                para "$#{item_total}"
              end
              @num += 1
            end
          end
        end
        
        flow :top => 0, :left => -40, :height => 40, :width => 1.2 do
          background white..gray, :left => -40
          stack :width => 0.25, :margin_left => 40 do
            caption "Item"
          end
          stack :width => 0.13 do
            caption "Quantity"
          end
          stack :width => 0.15 do
            caption "Item Total"
          end
          stack :width => 0.2 do
            total = sprintf "%5.2f",@total.inject { |mem, var| mem + var }
            caption "TODAY $#{total}"
          end
        end
        
        database.close
      end
    end
    
    def showAll
      @total = []
      @flowAnalysis.clear if @flowAnalysis != nil
      @flowAnalysis = flow :top => 60, :left => 40 do
        @num = 2
        database = SQLite3::Database.open("Database/CSDB.db")
        flow :margin_top => 40 do;end
        
        @aWeekAgo = Time.now-24*60*60*7
        database.execute("SELECT item FROM sales GROUP BY item") do |item|
          database.execute("SELECT sum(quantity), salesPrice FROM sales WHERE item = '#{item}'") do |row|#sum, salesPrice|
            @total << row[1].to_f*row[0].to_f
            flow do
              background COLORS[@num % 2], :left => -40
              stack :width => 0.25 do
                para item
              end
              stack :width => 0.15 do
                para row[0]
              end
              stack :width => 0.18 do
                item_total = sprintf "%-5.2f", row[1].to_f*row[0].to_f
                para "$#{item_total}"
              end
              @num += 1
            end
            
          end
        end
        flow :top => 0, :left => -40, :height => 40, :width => 1.2 do
          background white..gray, :left => -40
          stack :width => 0.25, :margin_left => 40 do
            caption "Item"
          end
          stack :width => 0.13 do
            caption "Quantity"
          end
          stack :width => 0.15 do
            caption "Item Total"
          end
          stack :width => 0.2 do
            total = sprintf "%5.2f",@total.inject { |mem, var| mem + var }
            caption "TOTAL $#{total}"
          end
        end
        database.close
      end
      
    end
    
    def showWeekly
      @total = []
      @flowAnalysis.clear if @flowAnalysis != nil
      @flowAnalysis = flow :top => 60, :left => 40 do
        @num = 2
        database = SQLite3::Database.open("Database/CSDB.db")
        database.type_translation=true
        # flow :margin_top => 40 do;end
        
        @total = []
        database.execute("SELECT item, date FROM sales GROUP BY item ORDER BY date") do |item, date|

          database.execute("SELECT sum(quantity*salesPrice), sum(quantity) FROM sales WHERE item = '#{item}'ORDER BY item") do |sum|
            # thing = sprintf "item: %-22s, sum: q s %5.2f, sum: q, %d date: %s\n",item,sum[0],sum[1],date.wday
            sumFloat = sprintf "%-5.2f", sum[0]
            flow do
              background COLORS[@num % 2], :left => -40
              stack :width => 0.35 do
                para item
              end
              stack :width => 0.18 do
                para "Sold #{sum[1]}"
              end
              stack :width => 0.15 do
                para "$#{sumFloat}"
              end

              stack :width => 0.15 do
                # para date.strftime("%m/%d/%Y")
              end
              @total << sum[0].to_f
              # @total.clear if date.wday == 6
              @wdate = date.wday
              @date = date.strftime("%m/%d/%Y")
            end
            @num += 1
          end
        end
        flow :height => 40 do;end
        flow :height => 40, :left => -40 do
          background white..gray, :left => -40, :width => 1.8
          stack :margin_left => 40do
            flow do
              stack :width => 0.5 do
                flow do
                  caption "Total For the Week as of", :stroke => gray(0.3)
                  caption strong "\s#{@date}"
                end
              end
              stack :width => 0.3 do
                Shoes.p @total
                if @total == nil
                  total = 0
                else
                  total = sprintf "%5.2f",@total.inject { |mem, var| mem + var }
                end
                caption strong "\t$#{total}"
                @total.clear if @wdate == 6
                Shoes.p @total
              end
            end
          end
        end
        
        # flow :top => 0, :left => -40, :height => 40, :width => 1.2 do
        #   background white..gray, :left => -40
        #   stack :width => 0.25, :margin_left => 40 do
        #     caption "Item"
        #   end
        #   stack :width => 0.13 do
        #     caption "Quantity"
        #   end
        #   stack :width => 0.15 do
        #     caption "Item Total"
        #   end
        #   stack :width => 0.18 do
        #     caption "TODAY #{@total.inject { |mem, var| mem + var }}"
        #   end
        # end
        
        database.close
      end
    end
    
  
  
  
  background white
  background black, :height => 60
  
  $salesAnalysisCS = self
  COLORS = [white, antiquewhite]
  flow :margin => 20 do
    flow do
      button('Index') {showReport}
      button('Show All') {showAll}
      button('Show Today') {showToday}
      button('Show Weekly') {showWeekly}
    end
  
  

  end#flow
end