#!/usr/bin/ruby

require "dbi"
require "csv"

# status, size, magazine


class LG
    attr_reader :company, :content, :phone, :web, :lPhoto, :lrPhoto

    @rowStyle0 = ""
    @rowStyle1 = ""

    @altRow = false

    def initialize(cArray)
        #company,content,phone,web,lPhoto,rPhoto)
        @company = cArray[0]
        @content = cArray[1]
        @phone   = cArray[2]
        @web     = cArray[3]
        @lPhoto  = cArray[4]

        if !cArray[5].kind_of?(Integer) 
            @rPhoto  = cArray[5]
        end

    end

    def to_a
        [ @company, @content, @phone, @web, @lPhoto, @rPhoto ]
    end

    def rowStyle
        @altRow = !@altRow
        if @altRow
            @rowStyle0
        else
            @rowStyle1
        end
    end

    def to_html
        rowStyle = self.rowStyle

        text <<-EOT
        <tr class='#{rowStyle}'>
            <td>
                #{@company}<br />
                #{@phone}<br />
                #{@web}<br />
            </td>
            <td>#{@content}</td>
            <td>#{lPhoto}</td>
            <td>#{rPhoto}</td>
        </tr>
        EOT
    end
end

class Stack
    def initialize
        @stack = Array.new
    end

    def append(lg)
        @stack.push(lg)
        self
    end

    def elements
        @stack
    end

    def dump
        p @stack
    end

    def sort
        @stack = @stack.sort { |a,b|
            a.company <=> b.company
        }
    end


    def csvString
        str = ""

        CSV::Writer.generate(str) { |csv|
            @stack.each { |lg|
                csv << lg.to_a
            }
        }

        str
    end

    def export(file)
        str = self.csvString
        
        save = File.new(file, "w+")
        mangleEncoding("WINDOWS-1252","MAC",str).each do |line|
            save.write(line)
        end
        save.close
        
    end
end

def connect
    DBI.connect("dbi:Mysql:lollive:localhost","root","w@y2G0")
end

def lg1Query(db,magazine,size,status, where)
    queryTemplate = <<-EOQ
    SELECT
        lgr.company_name, lgr.content, lgr.phone, lgr.website as web,
        CONCAT(lgm.basename,'.',lgm.format) as photo,
        lgm.id as media_id
    FROM
        lead_generator as lg
    LEFT JOIN
        lead_generator_approvals  as lga ON (lg.id=lga.lg_id AND lga.status = ? )
    LEFT JOIN
        lead_generator_revision   as lgr ON ( lga.rev_id=lgr.id )
    LEFT JOIN
       lead_generator_media       as lgm ON ( lgr.photos = lgm.id )
    WHERE
            lg.size   = ? 
        AND
            lg.magazines = ?
        AND
            lgr.id = lga.rev_id
        AND
            LOCATE(',',lgr.photos) = 0
    EOQ

    if where.nil?
        queryTemplate += <<-EO2
            ORDER BY
                lgr.company_name ASC
        EO2
    else
        queryTemplate += <<-EO3
            AND
                lgm.height #{where} lgm.width
            ORDER BY
                lgr.company_name ASC
        EO3
    end

    begin
        db.execute(queryTemplate, status, size, magazine )
    rescue DBI::DatabaseError => e
    ensure
    end
end


# retrieve approved double-2 LG's by magazine, status and category, 
def lg2Query(db,magazine,status)
    query2Template = <<-EOQ
    SELECT
        lgr.company_name, lgr.content, lgr.phone, lgr.website as web,
        CONCAT(lgm_l.basename,'.',lgm_l.format) as photo_l,
        CONCAT(lgm_r.basename,'.',lgm_r.format) as photo_r,
        lgm_l.id as medial_id,
        lgm_r.id as mediar_id
    FROM
        lead_generator as lg
    LEFT JOIN
        lead_generator_approvals  as lga ON (lg.id=lga.lg_id AND lga.status = ? )
    LEFT JOIN
        lead_generator_revision   as lgr ON ( lga.rev_id=lgr.id )
    LEFT JOIN
       lead_generator_media      as lgm_l ON ( LEFT(lgr.photos, LOCATE(',',lgr.photos) - 1) = lgm_l.id)
    LEFT JOIN
       lead_generator_media      as lgm_r ON ( SUBSTRING(photos FROM LOCATE(',',photos) + 1 ) = lgm_r.id)
    WHERE
            lg.size   = 'double' 
        AND
            lg.magazines = ?
        AND
            lgr.id = lga.rev_id
        AND
            LOCATE(',',lgr.photos) > 0
    ORDER BY
        lgr.company_name ASC
    EOQ

    begin
        db.execute(query2Template, status, magazine )
    rescue DBI::DatabaseError => e
    ensure
    end
end

def getCategories(dbh,parent)
    if dbh.nil?
        return nil
    end

    query = <<-EOQ
        SELECT * FROM `lead_generator_categories`
        WHERE parentId = ?
        ORDER BY name
    EOQ

    dbh.execute(query, parent)
end

def mangleEncoding(from, to, chunk)
    pipe = IO.popen("iconv -f #{from} -t #{to}", "w+");
    chunk.each do |line|
        pipe.puts line
    end
    pipe.close_write
    pipe.readlines
end

parentCategories = {
    1  => 'Business Services',
    2  => 'Computer Equipment',
    3  => 'Consulting',
    4  => 'Erosion Control',
    5  => 'Irrigation',
    6  => 'Lighting / Electrical',
    7  => 'Park & Recreation',
    8  => 'Pest / Wildlife Control',
    9  => 'Plant Material',
    10 => 'Plant Related Chemicals / Fertilizers',
    11 => 'Planting Accessories',
    12 => 'PMBR: Pavers, Masonry, Blocks & Rocks',
    13 => 'Site Amenities',
    14 => 'Tools & Equipment',
    15 => 'Tools For Testing',
    16 => 'Water Features'
}

def bloomPath(basePath,dir)
    if File.exist?("#{basePath}/#{dir}")
        # file exists, check if it's a dir
        if File.stat("#{basePath}/#{dir}").directory?
            return true
        end
        return false
    else
        Dir.mkdir("#{basePath}/#{dir}")
    end
end

def getImageExtension(name)
    ext = name[/\.[\w]+$/]
    if ext == ".tiff"
        return ".tif"
    end
    ext
end

def fixWeb(site)
    if !site.nil? and site.length > 4 and (site =~ /^www\./).nil?
        return "www." + site
    end
    site
end

begin
    #str = ""
    magazines = ['lasn','lcn','lsmp']
    basePath = "/data/sites/lead_generators/2005/dump"

    dbh     = connect

    date = Date.today.to_s
    counter = 0

    magazines.each { |mag|
        puts "******************** #{mag} ********************"
        puts

        csv = Stack.new
        files = []
        path  = mag + '/' #+ val.gsub(/[\s\:\&\,\/]+/,'_') # directory name

        lg2Query(dbh,mag,'approved').each { |lg| 
            if !lg["photo_l"].nil?
                counter += 1
                ext = getImageExtension(lg["photo_l"])
                #lpl = lg["photo_l"].to_s
                #ext = lpl[/\.[\w]+$/]
                name = "lg-#{lg['medial_id']}#{ext}"
                files[counter] = { lg["photo_l"] => name }
                lg["photo_l"] = name
            end

            if !lg["photo_r"].nil?
                counter += 1
                ext = getImageExtension(lg["photo_r"])
                name = "lg-#{lg['mediar_id']}#{ext}"
                files[counter] = { lg["photo_r"] => name }
                lg["photo_r"] = name
            end
            
            lg["web"] = fixWeb(lg["web"])

            #csv.append(LG.new(lg)) 
            csv.append(LG.new(lg)) 

        }

        lg2Query(dbh,mag,'waiting').each { |lg| 
            if !lg["photo_l"].nil?
                counter += 1
                ext = getImageExtension(lg["photo_l"])
                #lpl = lg["photo_l"].to_s
                #ext = lpl[/\.[\w]+$/]
                name = "lg-#{lg['medial_id']}#{ext}"
                files[counter] = { lg["photo_l"] => name }
                lg["photo_l"] = name
            end

            if !lg["photo_r"].nil?
                counter += 1
                ext = getImageExtension(lg["photo_r"])
                name = "lg-#{lg['mediar_id']}#{ext}"
                files[counter] = { lg["photo_r"] => name }
                lg["photo_r"] = name
            end
            
            lg["web"] = fixWeb(lg["web"])

            csv.append(LG.new(lg)) 

        }


        ### Double, 1 Photo
        lg1Query(dbh,mag,'double','approved',nil).each { |lg| 
            if !lg["photo"].nil?
                counter += 1
                ext = getImageExtension(lg["photo"])
                name = "lg-#{lg['media_id']}#{ext}"
                files[counter] = { lg["photo"] => name }
                lg["photo"] = name
            end

            lg["web"] = fixWeb(lg["web"])

            

            csv.append(LG.new(lg)) 

        }

        lg1Query(dbh,mag,'double','waiting',nil).each { |lg| 
            if !lg["photo"].nil?
                counter += 1
                ext = getImageExtension(lg["photo"])
                name = "lg-#{lg['media_id']}#{ext}"
                files[counter] = { lg["photo"] => name }
                lg["photo"] = name
            end

            lg["web"] = fixWeb(lg["web"])

            csv.append(LG.new(lg)) 
        }

        # Single, Horizontal
        lg1Query(dbh,mag,'single','approved', '<=').each { |lg| 
            if !lg["photo"].nil?
                counter += 1
                ext = getImageExtension(lg["photo"])
                name = "lg-#{lg['media_id']}#{ext}"
                files[counter] = { lg["photo"] => name }
                lg["photo"] = name
            end

            lg["web"] = fixWeb(lg["web"])

            csv.append(LG.new(lg)) 
        }

        lg1Query(dbh,mag,'single','waiting', '<=').each { |lg| 
            if !lg["photo"].nil?
                counter += 1
                ext = getImageExtension(lg["photo"])
                name = "lg-#{lg['media_id']}#{ext}"
                files[counter] = { lg["photo"] => name }
                lg["photo"] = name
            end

            lg["web"] = fixWeb(lg["web"])

            csv.append(LG.new(lg)) 
        }

        # Single, Vertical
        lg1Query(dbh,mag,'single','approved','>').each { |lg| 
            if !lg["photo"].nil?
                counter += 1
                ext = getImageExtension(lg["photo"])
                name = "lg-#{lg['media_id']}#{ext}"
                files[counter] = { lg["photo"] => name }
                lg["photo"] = name
            end

            lg["web"] = fixWeb(lg["web"])

            csv.append(LG.new(lg)) 
        }

        lg1Query(dbh,mag,'single','waiting','>').each { |lg| 
            if !lg["photo"].nil?
                counter += 1
                ext = getImageExtension(lg["photo"])
                name = "lg-#{lg['media_id']}#{ext}"
                files[counter] = { lg["photo"] => name }
                lg["photo"] = name
            end

            lg["web"] = fixWeb(lg["web"])

            csv.append(LG.new(lg)) 
        }

        # link up the files
        files.each do |obj|
            if obj.nil?
                next
            end

            obj.each do |orig,photo|
                retried = false
                if !photo.nil?
                    #puts "#{orig}: #{photo}"

                    begin
                        File.link(
                            "/data/sites/www.landscapeonline.com/web/guide/lg/original/" + orig, 
                            basePath + '/' + path + '/' + photo)
                    rescue SystemCallError
                        if !retried
                            retried = true
                            File.delete(basePath + '/' + path + '/' + photo)
                            retry
                        end
                    end
                end
            end
        end

        csv.sort 
        csv.export(basePath + '/' + path + '/raw-' + date + '.csv')
        #csv.elements.each { |elem|
        #    p elem
        #}

        #save = File.new(basePath + '/' + path + '/raw-' + date + '.csv', "w+")
        #mangleEncoding("WINDOWS-1252","MAC",str).each do |line|
        #    save.write(line)
        #end
        #save.close

    }
rescue DBI::DatabaseError => e
    print "Error code: ", e.errno, "\n"
    print "Error message: ", e.error, "\n"
ensure
    
end
