#!/usr/bin/env python3

import xlsxwriter
from django.utils import timezone
import sqlite3

import ftplib
import os

def uploadFileFTP(sourceFilePath, destinationDirectory, server, username, password):
    myFTP = ftplib.FTP(server, username, password)
    if destinationDirectory in [name for name, data in list(remote.mlsd())]:
        print "Destination Directory does not exist. Creating it first"
        myFTP.mkd(destinationDirectory)
    # Changing Working Directory
    myFTP.cwd(destinationDirectory)
    if os.path.isfile(sourceFilePath):
        fh = open(sourceFilePath, 'rb')
        myFTP.storbinary('STOR %s' % f, fh)
        fh.close()
    else:
        print "Source File does not exist"


def get_excel(data):
    date = timezone.now().strftime("%Y-%m-%d")
    path = 'Archive/RMS_Master_' + date +'.xlsx'
    workbook = xlsxwriter.Workbook(path)
    worksheet = workbook.add_worksheet()
    format = workbook.add_format()
    format.set_bold()
    format1 = workbook.add_format()
    format1.set_bg_color('#00FF00')
    format1.set_bold()
    format2 = workbook.add_format()
    format2.set_bg_color('#FF99CC')
    format2.set_bold()
    format3 = workbook.add_format()
    format3.set_bg_color('#FFFF00')
    format3.set_bold()
    worksheet.write("A1", "Primary Key", format1)
    worksheet.write("B1", "Name 1", format)
    worksheet.write("C1", "Name 2", format1)
    worksheet.write("D1", "Address 1", format)
    worksheet.write("E1", "Address 2", format1)
    worksheet.write("F1", "City", format)
    worksheet.write("G1", "State", format)
    worksheet.write("H1", "Zip", format)
    worksheet.write("I1", "Country", format2)
    worksheet.write("J1", "Attention", format1)
    worksheet.write("K1", "Sales Reference", format3)
    worksheet.write("L1", "Email", format3)
    worksheet.write("M1", "Sold To", format3)
    worksheet.write("N1", "Ship To", format3)
    worksheet.write("O1", "Route", format3)
    worksheet.write("P1", "<===Use only if not all shipping the same way (must use Valid Carrier Codes)", format)
    worksheet.write("Q1", "PO Number", format)
    worksheet.write("R1", "Reference Number", format3)
    worksheet.write("S1", "<===(Typically Cost Center)", format)
    worksheet.write("T1", "Customer Reference 2", format3)
    worksheet.write("U1", "<===", format)
    worksheet.write("V1", "Customer Reference 3", format)
    worksheet.write("W1", "<===", format)
    worksheet.write("X1", "Sales Reference", format3)
    worksheet.write("Y1", "<=== (@winXS only)", format)
    worksheet.write("Z1", "Item 1", format)
    worksheet.write("AA1", "Quantity 1", format)
    worksheet.write("AB1", "UOM 1", format3)
    worksheet.write("AC1", "<===Defaults to EA if not supplied", format)
    worksheet.write("AD1", "Job 1", format3)
    worksheet.write("AE1", "Whs 1", format3)
    worksheet.write("AF1", "<=== Repeat up to 80 Items", format)
    index = 1
    for row in data:
        worksheet.write(index, 0, row[0])
        worksheet.write(index, 1, row[1])
        worksheet.write(index, 2, row[2])
        worksheet.write(index, 3, row[3])
        worksheet.write(index, 5, row[4])
        worksheet.write(index, 6, row[6])
        worksheet.write(index, 7, row[5])
        worksheet.write(index, 11, row[7])
        index = index + 1
    workbook.close()
    return path

conn = sqlite3.connect('db.sqlite3')

curs = conn.cursor()

query = 'SELECT * FROM howdy_order WHERE order_shipped=""'

curs.execute(query)

path = get_excel(curs.fetchall())
print(path)
uploadFileFTP(path, 'reports_TEST', 'ftp://cardyoursharps.com', 'cardyoursharps', 'thwDpE3RLYL7W?8&')