def setUpClass(cls):
#----------------------
# set up db
#----------------------
cls.db = MySQLdb.connect(
ks_db_settings.setting('host'),
ks_db_settings.setting('user'),
ks_db_settings.setting('password'),
ks_db_settings.setting('database'))
cls.ks_fh = filehandler(cls.db)
cls.ks_fh.reset()
cls.company_id = 1
register_raw_files("./tests/data2/Sales.csv", cls.company_id, cls.db)
register_raw_files("./tests/data2/Currencyv2.csv", cls.company_id, cls.db)
register_raw_files("./tests/data2/CountryRegion.csv", cls.company_id, cls.db)
register_raw_files("./tests/data2/ComissionTax.csv",cls.company_id, cls.db)
ks_precompute = precompute(cls.db)
ks_precompute.reset()
precompute
ks_merge = merge(cls.db)
load_precompute_normalize_URL(cls.company_id, cls.db)
id = ks_precompute.getMaxBigTableIdForCompany(cls.company_id)
ks_merge = merge(cls.db)
mergeBigTable = ks_merge.getTables()
metaData = ks_merge.getMetaDataFromTable(mergeBigTable[0])
cls.ks_analytics = analytics(cls.db)
newBigTable = "BigTable"+ str(ks_precompute.getMaxBigTableIdForCompany(cls.company_id))
cls.ks_analytics.reset()
cls.ks_analytics.addBigTable(mergeBigTable[0], newBigTable, metaData)
#clean up
sql ="update %s set TaxRate = TaxRate/100;"%("analytics."+newBigTable)
cls.db.cursor().execute(sql)
# ProductType changed from D to M see documentation of test case
sql ="update %s set ProductType = 'M' where VendorId='0268_20140114_SOFA_ENGLIS' and DownloadDate='6/1/14';"%("analytics."+newBigTable)
cls.db.cursor().execute(sql)
cls.ks_analytics.addFactUsingBinaryOpAPI("NET_REVENUE", "Units", "RoyaltyPrice", "*", newBigTable)
cls.ks_analytics.addFactUsingBinaryOpAPI("TAXES", "NET_REVENUE","TaxRate","*", newBigTable)
cls.ks_analytics.addFactUsingBinaryOpAPI("REVENUE_AFTER_TAX", "NET_REVENUE","TAXES","-", newBigTable)
cls.ks_fh.registerFormula("", "Plus", "Plus", "Units+RoyaltyPrice", "sum")
cls.ks_fh.registerFormula("", "Mult", "Mult", "Units*RoyaltyPrice", "sum")
cls.ks_fh.registerFormula("", "Individual_Tax", "Individual_Tax", "RoyaltyPrice*TaxRate", "sum")
cls.ks_fh.registerFormula("", "NET_REVENUE", "NET_REVENUE", "Units*RoyaltyPrice", "sum")
cls.ks_fh.registerFormula("", "SumPlus", "SumPlus", "Sum(Units)+Sum(RoyaltyPrice)", "sum")
cls.ks_fh.registerFormula("", "SumMult", "SumMult", "Sum(Units)*Sum(RoyaltyPrice)", "sum")
cls.ks_fh.registerFormula("", "Individual_TaxSum", "Individual_TaxSum", "Sum(RoyaltyPrice)*Sum(TaxRate)", "sum")
cls.ks_fh.registerFormula("", "NonsenseSum", "Individual_Tax", "Sum(RoyaltyPrice)+Sum(TaxRate)", "sum")
cls.ks_fh.registerFormula("", "Nonsense", "Individual_Tax", "RoyaltyPrice+TaxRate", "sum")
cls.ks_fh.registerFormula("", "REVENUE_AFTER_TAX", "REVENUE_AFTER_TAX", "", "sum")
def setUpClass(cls):
#----------------------
# set up db
#----------------------
cls.db = MySQLdb.connect(
ks_db_settings.setting('host'),
ks_db_settings.setting('user'),
ks_db_settings.setting('password'),
ks_db_settings.setting('database'))
def setUpClass(cls):
# ----------------------
# set up db
# ----------------------
cls.db = MySQLdb.connect(
ks_db_settings.setting("host"),
ks_db_settings.setting("user"),
ks_db_settings.setting("password"),
ks_db_settings.setting("database"),
)
def setUpClass(cls):
#----------------------
# set up db
#----------------------
cls.db = MySQLdb.connect(
ks_db_settings.setting('host'),
ks_db_settings.setting('user'),
ks_db_settings.setting('password'),
ks_db_settings.setting('database'))
#----------------------
# filehandler
#----------------------
file_handler = filehandler(cls.db)
file_handler.reset()
file_handler.addTable("Sales", "1","Sales.csv")
file_handler.addTable("Sales", "2","SalesCustomerTwo.csv")
file_handler.addTable("ComissionTax", "1","ComissionTax.csv")
file_handler.addTable("ComissionTax","2","ComissionTaxCustomerTwo.csv")
file_handler.addTable("CountryRegion", "1","CountryRegion.csv")
file_handler.addTable("CountryRegion","2","CountryRegionCustomerTwo.csv")
file_handler.addTable("Currency2","1","Currencyv2.csv")
time.sleep(1)
file_handler.addTable("Sales", "1","SalesNewVersion.csv")
print(file_handler.getLatestTable("Sales", "2"))
#----------------------
# merge
#----------------------
ks_merge = merge(cls.db)
ks_merge.reset()
ks_merge.addTable("./ks_filehandler/ks_filehandler/data/Sales.csv","Sales")
ks_merge.addTable("./ks_filehandler/ks_filehandler/data/CountryRegion.csv","CountryRegion")
ks_merge.addTable("./ks_filehandler/ks_filehandler/data/ComissionTax.csv","ComissionTax")
sql_BigTable = "CREATE TABLE BigTable(id INT PRIMARY KEY AUTO_INCREMENT, \
VendorId VARCHAR(25), \
ProductType VARCHAR(25), \
Units FLOAT, \
RoyaltyPrice FLOAT, \
DownloadDate VARCHAR(25), \
CustomerCurrency VARCHAR(25), \
CountryCode VARCHAR(25), \
Region VARCHAR(25), \
RightsHolder VARCHAR(25), \
ComissionRate VARCHAR(25), \
TaxRate VARCHAR(25))"
sql_join = "insert into BigTable select S.id,S.VendorId,S.ProductType, "\
"S.Units, S.RoyaltyPrice, S.DownloadDate, S.CustomerCurrency, "\
"S.CountryCode, C.Region, T.RightsHolder, T.ComissionRate, "\
"T.TaxRate from Sales S Inner Join CountryRegion C on "\
"S.CountryCode=C.CountryCode Inner join ComissionTax T on " \
"S.VendorId = T.VendorId and C.Region = T.Region;"
ks_merge.join(sql_join, sql_BigTable)
#----------------------
# clean up
#----------------------
cursor = cls.db.cursor()
sql = "use merge;"
cursor.execute(sql)
sql = "ALTER TABLE BigTable change ComissionRate ComissionRate FLOAT;"
cursor.execute(sql)
sql = "ALTER TABLE BigTable change TaxRate TaxRate FLOAT;"
cursor.execute(sql)
sql ="update BigTable set TaxRate = TaxRate/100;"
cursor.execute(sql)
#----------------------
# analytics
#----------------------
cls.ks_analytics = analytics(cls.db)
cls.ks_analytics.addFactUsingBinaryOp("NET_REVENUE", "Units", "RoyaltyPrice", "*")
cls.ks_analytics.addFactUsingBinaryOp("TAXES", "NET_REVENUE","TaxRate","*")
cls.ks_analytics.addFactUsingBinaryOp("REVENUE_AFTER_TAX", "NET_REVENUE","TAXES","-")
