#!/usr/bin/env python3

import xlsxwriter
from django.utils import timezone
import sqlite3
import ftplib
from ftplib import FTP_TLS
import os
import pygeoip
import socket
import ftplib

def UploadFileFTP(sourceFilePath, destinationDirectory, server, username, password):
    myFTP = ftplib.FTP_TLS(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', 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]+' '+row[2])
        worksheet.write(index, 2, row[0])
        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])
        worksheet.write(index, 25, "323487")
        worksheet.write(index, 26, "1")
        worksheet.write(index, 27, "each")
        worksheet.write(index, 30, "63")
        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", "cardyoursharps.com", "cardyoursharps", "thwDpE3RLYL7W?8&")