def test_load_files(self):
ks_fh = filehandler(self.db)
ks_fh.reset()
company_id = 1
register_raw_files("./tests/data2/Sales.csv", company_id, self.db)
register_raw_files("./tests/data2/Currencyv2.csv", company_id, self.db)
register_raw_files("./tests/data2/CountryRegion.csv", company_id, self.db)
register_raw_files("./tests/data2/ComissionTax.csv",company_id, self.db)
ks_precompute = precompute(self.db)
ks_precompute.reset()
precompute
ks_merge = merge(self.db)
load_precompute_normalize(company_id, self.db)
ks_fh.registerFormula("", "Plus", "Plus", "Units+RoyaltyPrice", "sum")
ks_fh.registerFormula("", "Mult", "Mult", "Units*RoyaltyPrice", "sum")
plus_id = ks_fh.getMeasureID("Plus")
mult_id = ks_fh.getMeasureID("Mult")
units_id = ks_fh.getMeasureID("Units")
royality_id = ks_fh.getMeasureID("RoyaltyPrice")
# MEASURE DATA DEMO raw_facts + measures with formulas
print(measure_data(self.db, company_id, [plus_id,mult_id,units_id,royality_id],"day","2014-06-01","2014-06-01"))
# MEASURE DATA DEMO raw_facts group by
print(measure_data(self.db, company_id, [units_id, royality_id],"day","2014-06-01","2014-06-01","Region"))
self.db.commit()
def test_Binary_Op_Addition_with_groupby_date(self):
measure_ids = self.MeasureName2MeasureIds("SumPlus")
group_by = "ks_date"
result = measure_data(self.db, self.company_id, measure_ids, "day", "2014-06-01", "2014-06-01", group_by)
value = 0
code_result = result["SumPlus"]
for key in code_result:
value = value + int(code_result[key])
self.assertEqual(200, value)
def test_Binary_Op_Multiplication_with_groupby(self):
measure_ids = self.MeasureName2MeasureIds("SumMult")
group_by = "ProductType, ks_date, VendorId "
result = measure_data(self.db, self.company_id, measure_ids, "day", "2014-06-01", "2014-06-01", group_by)
value = 0
code_result = result["SumMult"]
for key in code_result:
value = value + int(code_result[key])
self.assertEqual(3600, value)
def test_Intertable_Addition_with_groupby_date(self):
measure_ids = self.MeasureName2MeasureIds("NonsenseSum")
group_by = "ks_date"
result = measure_data(self.db, self.company_id, measure_ids, "day", "2014-06-01", "2014-06-01", group_by)
print result
value = 0.0
code_result = result["NonsenseSum"]
for key in code_result:
value = value + float(code_result[key])
self.assertAlmostEqual(83.096, value)
def test_Binary_Op_Addition_with_groupby_date(self):
measure_ids = self.MeasureName2MeasureIds("SumPlus")
group_by = "ks_date"
result = measure_data(self.db, self.company_id, measure_ids, "day", "2014-06-01", "2014-06-01", group_by,None,
"and VendorId='0268_20140114_SOFA_ENGLIS'")
value = 0
code_result = result["SumPlus"]
for key in code_result:
value = value + int(code_result[key])
self.assertEqual(10, value)
def test_Intertable_Multiplication_with_groupby_date(self):
measure_ids = self.MeasureName2MeasureIds("Individual_TaxSum")
group_by = "ks_date"
result = measure_data(self.db, self.company_id, measure_ids, "day", "2014-06-01", "2014-06-01", group_by)
print result
value = 0.0
code_result = result["Individual_TaxSum"]
for key in code_result:
value = value + float(code_result[key])
self.assertAlmostEqual(4.95360006, value)
def test_Intertable_Multiplication_with_groupby(self):
measure_ids = self.MeasureName2MeasureIds("Individual_TaxSum")
group_by = "ks_date, VendorId, ProductType"
result = measure_data(self.db, self.company_id, measure_ids, "day", "2014-06-01", "2014-06-01", group_by,None,
"and VendorId='0268_20140114_SOFA_ENGLIS'")
print result
value = 0.0
code_result = result["Individual_TaxSum"]
for key in code_result:
value = value + float(code_result[key])
self.assertAlmostEqual(0.419200005, value)
def test_Intertable_Addition_with_groupby_date(self):
measure_ids = self.MeasureName2MeasureIds("NonsenseSum")
group_by = "ks_date"
result = measure_data(self.db, self.company_id, measure_ids, "day", "2014-06-01", "2014-06-01", group_by,None,
"and VendorId='0268_20140114_SOFA_ENGLIS'")
print result
value = 0.0
code_result = result["NonsenseSum"]
for key in code_result:
value = value + float(code_result[key])
self.assertAlmostEqual(4.104800001, value)
def get(self):
_INSTANCE_NAME = 'ks-sqlengine:test'
self.response.write("<p> API Demo </p>")
if (os.getenv('SERVER_SOFTWARE') and
os.getenv('SERVER_SOFTWARE').startswith('Google App Engine/')):
db = MySQLdb.connect(unix_socket='/cloudsql/' + _INSTANCE_NAME, db='source', user='******')
else:
db = MySQLdb.connect(host='173.194.87.126', db='source', user='******', passwd='ZVYZM KMGYH')
# reset DB
ks_fh = filehandler(db)
#ks_fh.reset()
#ks_merge = merge(db)
#ks_merge.reset()
#ks_precompute = precompute(db)
#ks_precompute.reset()
company_id = "1"
#sales_table = "AMIfv955UlTWfs9d8HWHPM6vPbnHKX_GiYzLREhpOSAlOzYXg-aO2fN0N2sugsJ15GeVRhISJmeTmpV5zlblU1gpkGcctwFH7ip4mg4eR18Y1hVLXNlvffj1ysaR3e4tdsoIkhFncmw9dpZ6eyP4E7xvy8KjjNEUtg"
#currency_table = "AMIfv959qauL3R-9UnuE9ox14Sic-IJwTx0zAD3qvDRCksANuuyXGz-W8amx5HbCC6iEsbK3igrvc-8CP6vZSy53mcZwXeDKRTYYwj4xTYs1sSGOwpHYV20twBsUhLcTcLwFgYC8sJ6DYFljXWpt64LzDWpcYNFalA"
#country_table = "AMIfv94wNLrpPOXsKChvzkpmJ5_HS6GZqQj0psFVhCCn_5CI-onYvZdmURUCaUv9vH_db8m9LtvzmJ0oz66_58wYR9rtvOPc7CIW3u7shECsIoLuZqWvMqcPColYizekx3wESBnYHUVfj1yZ3fH_mQfGIGSLD6iRbg"
#comission_table = "AMIfv94--LDpjvIbXCGcSxpnixVXDtz4X1LURYZ31NUnSjvxEpACa4q4LUvnZaBzXfPPgg5LHlO-50VFOdeJbOs6T9lWdxjvogz2FbRVpOojqo2ZOaxMVrbDQjT37oXd8JveQD02Yf3WM63satu3JqmVYvU5duc29w"
#register_raw_files2table(sales_table, company_id, "Sales", db)
#register_raw_files2table(currency_table, company_id, "Currencyv2", db)
#register_raw_files2table(country_table, company_id, "CountryRegion", db)
#register_raw_files2table(comission_table, company_id, "ComissionTax", db)
#load_precompute_normalize_blob(company_id, db)
#self.response.write(measure_data(db, 1, [3,4],"day","0006-01-14","0006-01-14"))
#self.response.write(measure_data(db, 1, [3,4],"day","0006-02-14","0006-02-14"))
#self.response.write(measure_data(db, 1, [3,4],"day","0006-01-14","0006-02-14"))
#self.response.write(measure_data(db, 1, [3,4],"day","0006-01-14","0006-02-14","Region"))
#self.response.write(measure_data(db, 1, [3,4],"day","0006-01-14","0006-02-14","RightsHolder"))
#self.response.write(measure_data(db, 1, [3,4],"day","0006-01-14","0006-02-14","CountryCode"))
measure_id = ks_fh.getMeasureID("Units")
measure_ids = []
measure_ids.append(measure_id)
result = measure_data(db, company_id, measure_ids, "day", "2014-06-01", "2014-06-01")
self.response.write(result)
db.close()
def test_Binary_Op_Aggregate(self):
measure_ids = self.MeasureName2MeasureIds("Units")
result = measure_data(self.db, self.company_id, measure_ids, "day", "2014-06-01", "2014-06-01",None,None,
"and VendorId='0268_20140114_SOFA_ENGLIS'")
self.assertEqual(6, result["Units"]["total"])
def test_Chained_Intertable(self):
measure_ids = self.MeasureName2MeasureIds("REVENUE_AFTER_TAX")
result = measure_data(self.db, self.company_id, measure_ids, "day", "2014-06-01", "2014-06-01")
self.assertAlmostEqual(2214.23999023, result["REVENUE_AFTER_TAX"]["total"])
def test_measure_data_quarter_output(self):
m_id = self.ks_fh.getMeasureID("UnitsSUM")
result = measure_data(self.db, self.company_id, [m_id], "quarter", "2014-04-01", "2014-06-30","ks_date")
print result
self.assertAlmostEqual(result['UnitsSUM']['2014-Q2'], 16.0)
def test_Chained_Intertable(self):
measure_ids = self.MeasureName2MeasureIds("REVENUE_AFTER_TAX")
result = measure_data(self.db, self.company_id, measure_ids, "day", "2014-06-01", "2014-06-01",None,None,
"and VendorId='0268_20140114_SOFA_ENGLIS'")
self.assertAlmostEqual(11.37120008468, result["REVENUE_AFTER_TAX"]["total"])
def test_Binary_Op_Aggregate(self):
measure_ids = self.MeasureName2MeasureIds("Units")
result = measure_data(self.db, self.company_id, measure_ids, "day", "2014-06-01", "2014-06-01")
self.assertEqual(120, result["Units"]["total"])
def test_Intertable_Addition_without_groupby_per_record(self):
measure_ids = self.MeasureName2MeasureIds("Nonsense")
result = measure_data(self.db, self.company_id, measure_ids, "day", "2014-06-01", "2014-06-01",None,None,
"and VendorId='0268_20140114_SOFA_ENGLIS'")
self.assertAlmostEqual(4.104800001, result["Nonsense"]["total"])
def test_Binary_Op_Multiplication_without_groupby_per_record(self):
measure_ids = self.MeasureName2MeasureIds("NET_REVENUE")
result = measure_data(self.db, self.company_id, measure_ids, "day", "2014-06-01", "2014-06-01")
self.assertEqual(2400, result["NET_REVENUE"]["total"])
def test_Intertable_Multiplication_without_groupby_per_record(self):
#self.assertAlmostEqual(0.6192, self.ks_analytics.calculate("RoyaltyPrice*TaxRate","6/1/14"))
measure_ids = self.MeasureName2MeasureIds("Individual_Tax")
result = measure_data(self.db, self.company_id, measure_ids, "day", "2014-06-01", "2014-06-01",None,None,
"and VendorId='0268_20140114_SOFA_ENGLIS'")
self.assertAlmostEqual(0.209600002, result["Individual_Tax"]["total"])
def test_Binary_Op_Addition_without_groupby_per_record(self):
measure_ids = self.MeasureName2MeasureIds("Plus")
result = measure_data(self.db, self.company_id, measure_ids, "day", "2014-06-01", "2014-06-01",None,None,
"and VendorId='0268_20140114_SOFA_ENGLIS'")
self.assertEqual(10, result["Plus"]["total"])
ks_fh = filehandler(db)
ks_fh.reset()
company_id = 1
register_raw_filesCsvPy("Sales",company_id, db)
register_raw_filesCsvPy("CurrencyV2",company_id, db)
register_raw_filesCsvPy("ComissionTax",company_id, db)
register_raw_filesCsvPy("CountryRegion",company_id, db)
ks_precompute = precompute(db)
ks_precompute.reset()
#precompute
load_precompute_normalize_CsvPy(company_id, db)
newBigTable = "BigTable"+ str(ks_precompute.getMaxBigTableIdForCompany(company_id))
ks_analytics = analytics(db)
ks_analytics.addFactUsingBinaryOpAPI("NET_REVENUE", "Units", "RoyaltyPrice", "*", newBigTable)
ks_analytics.addFactUsingBinaryOpAPI("TAXES", "NET_REVENUE","TaxRate","*", newBigTable)
ks_analytics.addFactUsingBinaryOpAPI("REVENUE_AFTER_TAX", "NET_REVENUE","TAXES","-", newBigTable)
ks_fh.registerFormula("", "SumMult", "SumMult", "Sum(Units)*Sum(RoyaltyPrice)", "sum")
measure_ids = MeasureName2MeasureIds("SumMult")
group_by = "ks_date, VendorId"
print(ks_analytics.parseGroupBy(group_by))
result = measure_data(db, company_id, measure_ids, "day", "2014-06-01", "2014-06-02",group_by)
print result
def test_measure_data_day_output(self):
m_id = self.ks_fh.getMeasureID("UnitsSUM")
result = measure_data(self.db, self.company_id, [m_id], "day", "2014-06-01", "2014-06-02","ks_date")
self.assertAlmostEqual(result['UnitsSUM']['2014-06-01'], 12.0)
self.assertAlmostEqual(result['UnitsSUM']['2014-06-02'], 4.0)
def test_Binary_Op_Addition_without_groupby_per_record(self):
measure_ids = self.MeasureName2MeasureIds("Plus")
result = measure_data(self.db, self.company_id, measure_ids, "day", "2014-06-01", "2014-06-01")
self.assertEqual(200, result["Plus"]["total"])
def test_Intertable_Addition_without_groupby_per_record(self):
measure_ids = self.MeasureName2MeasureIds("Nonsense")
result = measure_data(self.db, self.company_id, measure_ids, "day", "2014-06-01", "2014-06-01")
self.assertAlmostEqual(83.096, result["Nonsense"]["total"])
