#!/usr/bin/ruby

require "dbi"
require "csv"

# status, size, magazine

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

def lg1Query(db,magazine,size,status,category, 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_categories as lgc ON lg.category=lgc.id
    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
            lg.category = ?
        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, category)
    rescue DBI::DatabaseError => e
    ensure
    end
end


# retrieve approved double-2 LG's by magazine, status and category, 
def lg2Query(db,magazine,status,category)
    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_categories as lgc ON lg.category=lgc.id
    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
            lg.category = ?
        AND
            LOCATE(',',lgr.photos) > 0
    ORDER BY
        lgr.company_name ASC
    EOQ

    begin
        db.execute(query2Template, status, magazine, category)
    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 = ""
    #files = []
    magazines = ['lasn','lcn','lsmp']
    basePath = "/data/sites/lead_generators/2005/pre"

    dbh     = connect

    date = Date.today.to_s
    counter = 0

    parentCategories.sort.each do |id,val|
        puts
        puts "------------#{val}------------"
        magazines.each do |mag|
            str = ""
            #dbls2 = ""
            #dbls1 = ""
            #sglsh  = ""
            #sglsv  = ""

            csv = CSV::BasicWriter.new(str)

            #csv_dbls2 = CSV::BasicWriter.new(dbls2)
            #csv_dbls1 = CSV::BasicWriter.new(dbls1)
            #csv_sglsh = CSV::BasicWriter.new(sglsh)
            #csv_sglsv = CSV::BasicWriter.new(sglsv)

            files = [] # a list of files for this mag / cat
            path  = mag + '/' + val.gsub(/[\s\:\&\,\/]+/,'_') # directory name

            bloomPath(basePath, path) # create the directories if they don't exist

            getCategories(dbh, id).each do |category|
                # new csv for each type

                ### Double, 2 Photo
                lg2Query(dbh,mag,'approved', category["id"]).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 << lg 

                    #files << lg["photo_l"]
                    #files << lg["photo_r"]
                }

                lg2Query(dbh,mag,'waiting', category["id"]).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 << lg 

                    #files << lg["photo_l"]
                    #files << lg["photo_r"]
                }


                ### Double, 1 Photo
                lg1Query(dbh,mag,'double','approved',category["id"],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 << lg 
                }

                lg1Query(dbh,mag,'double','waiting',category["id"],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 << lg 
                }

                # Single, Horizontal
                lg1Query(dbh,mag,'single','approved',category["id"], '<=').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 << lg 
                    #files << lg["photo"]
                }

                lg1Query(dbh,mag,'single','waiting',category["id"], '<=').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 << lg 
                    #files << lg["photo"]
                }

                # Single, Vertical
                lg1Query(dbh,mag,'single','approved',category["id"],'>').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 << lg 
                }

                lg1Query(dbh,mag,'single','waiting',category["id"],'>').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 << lg 
                }

                #################### ORIGINAL
                #lg2Query(dbh,'lasn','approved', category["id"]).each { |lg| csv << lg }
                #lg2Query(dbh,'lcn', 'approved', category["id"]).each { |lg| csv << lg }
                #lg2Query(dbh,'lsmp','approved', category["id"]).each { |lg| csv << lg }
                #lg1Query(dbh,'lasn','single','approved',category["id"]).each { |lg| csv << lg }
                #lg1Query(dbh,'lasn','double','approved',category["id"]).each { |lg| csv << lg }
                #lg1Query(dbh,'lcn','single','approved',category["id"]).each { |lg| csv << lg }
                #lg1Query(dbh,'lcn','double','approved',category["id"]).each { |lg| csv << lg }
                #lg1Query(dbh,'lsmp','single','approved',category["id"]).each { |lg| csv << lg }
                #lg1Query(dbh,'lsmp','double','approved',category["id"]).each { |lg| csv << lg }
                #lg2Query(dbh,'lasn','waiting', category["id"]).each { |lg| csv << lg }
                #lg2Query(dbh,'lcn', 'waiting', category["id"]).each { |lg| csv << lg }
                #lg2Query(dbh,'lsmp','waiting', category["id"]).each { |lg| csv << lg }
                #lg1Query(dbh,'lasn','single','waiting',category["id"]).each { |lg| csv << lg }
                #lg1Query(dbh,'lasn','double','waiting',category["id"]).each { |lg| csv << lg }
                #lg1Query(dbh,'lcn','single','waiting',category["id"]).each { |lg| csv << lg }
                #lg1Query(dbh,'lcn','double','waiting',category["id"]).each { |lg| csv << lg }
                #lg1Query(dbh,'lsmp','single','waiting',category["id"]).each { |lg| csv << lg }
                #lg1Query(dbh,'lsmp','double','waiting',category["id"]).each { |lg| csv << lg }
                #csv << category
            end

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

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

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

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

        # one for each magazine

    end


    #lg2Query(dbh,'lasn','approved', 2).each { |lg|
    #    csv << lg
    #}

    #lasn_doubles_2p = lg2Query(dbh,'lasn','wa')
    #doubles_2p = lg2Query(dbh,'lasn','approved')

    #doubles.each do |row|
    #    csv << row
    #    #row.each_with_name do |val, name|
    #    #    printf "%s: %s, ", name, val.to_s
    #    #end
    #    #printf "\n"
    #end

    #doubles.finish

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