def test_CNAE_ID(self):
print "Entering in checkCNAE_ID"
total=0
# YMR: Check cnae aggs
aggsP = ['ei_ymrp', 'ei_ymsr']
for aggs in aggsP:
sql="SELECT count(*) FROM ei_ymr r where r.purchase_value <> \
(select sum(purchase_value) from "+aggs+" p where r.year=p.year and r.month=p.month \
and r.bra_id_r = p.bra_id_r and r.cnae_id_r = p.cnae_id_r);"
total+=runCountQuery('checkcnae_ID', 'ei_ymr: '+aggs, sql,cursor,count=True)
# YMP: Check cnae aggs
aggsP = ['ei_ymrp', 'ei_ymsp']
for aggs in aggsP:
sql="SELECT count(*) FROM ei_ymp r where r.purchase_value <> \
(select sum(purchase_value) from "+aggs+" p where r.year=p.year and r.month=p.month \
and r.hs_id = p.hs_id );"
total+=runCountQuery('checkcnae_ID', 'ei_ymp: '+aggs, sql,cursor,count=True)
# YMS: Check cnae aggs
aggsP = ['ei_ymsp', 'ei_ymsr']
for aggs in aggsP:
sql="SELECT count(*) FROM ei_yms r where r.purchase_value <> \
(select sum(purchase_value) from "+aggs+" p where r.year=p.year and r.month=p.month \
and r.bra_id_s = p.bra_id_s and r.cnae_id_s = p.cnae_id_s);"
total+=runCountQuery('checkcnae_ID', 'ei_yms: '+aggs, sql,cursor,count=True)
self.assertEqual(total, 0)
def test_Diversity(self):
print "Entering in checkDiversity"
total=0
sql="select count(*) from secex_ymb where \
(hs_diversity is null or hs_diversity <0 or \
hs_diversity_eff is null or hs_diversity_eff<0 or \
wld_diversity is null or wld_diversity<0 or \
wld_diversity_eff is null or wld_diversity_eff<0) ;"
total+=runCountQuery('checkDiversity', 'secex_ymb', sql,cursor,True)
sql="select count(*) from secex_ymp where \
bra_diversity is null or bra_diversity <0 or \
bra_diversity_eff is null or bra_diversity_eff<0 or \
wld_diversity is null or wld_diversity<0 or \
wld_diversity_eff is null or wld_diversity_eff<0;"
total+=runCountQuery('checkDiversity', 'secex_ymp', sql,cursor,True)
sql="select count(*) from secex_ymw where \
bra_diversity is null or bra_diversity<0 or \
bra_diversity_eff is null or bra_diversity_eff<0 or \
hs_diversity is null or hs_diversity<0 or \
hs_diversity_eff is null or hs_diversity_eff<0; "
total+=runCountQuery('checkDiversity', 'secex_ymw', sql,cursor,True)
self.assertEqual(total, 0)
def test_WLD_ID(self):
print "Entering in checkWLD_ID"
total=0
# YMW: Check WLD aggs 2 with 5
sql="SELECT count(*) FROM secex_ymw as b where b.wld_id_len=2 and concat(CAST(b.import_val AS CHAR),CAST(b.export_val AS CHAR )) <> \
(SELECT concat(CAST(import_val AS CHAR),CAST(export_val AS CHAR )) FROM secex_ymw \
where wld_id_len=5 and left(wld_id,2)=b.wld_id and month=b.month and year=b.year \
group by left(wld_id,2))"
total+=runCountQuery('checkWLD_ID', 'secex_ymw', sql,cursor,count=True)
# YMBW: Check WLD aggs 2 with 5
sql="SELECT count(*) FROM secex_ymbw as b where b.wld_id_len=2 and concat(CAST(b.import_val AS CHAR),CAST(b.export_val AS CHAR )) <> \
(SELECT concat(CAST(import_val AS CHAR),CAST(export_val AS CHAR )) FROM secex_ymbw \
where wld_id_len=5 and left(wld_id,2)=b.wld_id and bra_id=b.bra_id and month=b.month and year=b.year \
group by left(wld_id,2),bra_id_len,year)"
total+=runCountQuery('checkWLD_ID', 'secex_ymbw', sql,cursor,count=True)
# YMPW: Check WLD aggs 2 with 5
sql="SELECT count(*) FROM secex_ympw as b where b.wld_id_len=2 and concat(CAST(b.import_val AS CHAR),CAST(b.export_val AS CHAR )) <> \
(SELECT concat(CAST(import_val AS CHAR),CAST(export_val AS CHAR )) FROM secex_ympw \
where wld_id_len=5 and left(wld_id,2)=b.wld_id and hs_id=b.hs_id and month=b.month and year=b.year \
group by left(hs_id,2),hs_id_len,year)"
total+=runCountQuery('checkWLD_ID', 'secex_ympw', sql,cursor,count=True)
#YMPBW Check WLD aggs 2 with 5
sql="SELECT count(*) FROM secex_ymbpw as b where b.wld_id_len=2 and concat(CAST(b.import_val AS CHAR),CAST(b.export_val AS CHAR )) <> \
(SELECT concat(CAST(import_val AS CHAR),CAST(export_val AS CHAR )) FROM secex_ymbpw \
where wld_id_len=5 and left(wld_id,2)=b.wld_id and hs_id=b.hs_id and bra_id=b.bra_id and month=b.month and year=b.year \
group by left(wld_id,2),bra_id_len,hs_id_len,year)"
#runCountQuery('checkWLD_ID', 'secex_ymbpw', sql,cursor,count=True)
self.assertEqual(total, 0)
def test_BRA_IDPR(self):
total=0
#YB
sql="SELECT count(*) FROM secex_ymb as b where left(bra_id,2)<>'xx' and b.bra_id_len=7 and concat(CAST(b.import_val AS CHAR),CAST(b.export_val AS CHAR )) <> \
(SELECT concat(CAST(import_val AS CHAR),CAST(export_val AS CHAR )) FROM secex_ymb s, attrs_bra_pr p \
where p.bra_id = s.bra_id and p.pr_id = b.bra_id and s.bra_id_len=8 and \
left(s.bra_id,4)=b.bra_id and s.year=b.year and s.month=b.month group by left(s.bra_id,4))"
total+=runCountQuery('checkBRA_IDPR', 'secex_ymb', sql,cursor,count=True)
#YBP - bra_id 2 x 4
sql="SELECT count(*) FROM secex_ymbp as b \
where left(bra_id,2)<>'xx' and b.bra_id_len=4 and concat(CAST(b.import_val AS CHAR),CAST(b.export_val AS CHAR )) <> \
(SELECT concat(CAST(s.import_val AS CHAR),CAST(s.export_val AS CHAR )) FROM secex_ymbp s, attrs_bra_pr p \
where p.bra_id = s.bra_id and p.pr_id = b.bra_id and s.bra_id_len=8 and left(s.bra_id,4)=b.bra_id \
and s.hs_id=b.hs_id and s.year=b.year and s.month=b.month \
group by left(s.bra_id,4),s.hs_id_len,year)"
total+=runCountQuery('checkBRA_IDPR', 'secex_ymbp', sql,cursor,count=True)
#YBW
sql="SELECT count(*) FROM secex_ymbw as b \
where left(bra_id,2)<>'xx' and b.bra_id_len=4 and concat(CAST(b.import_val AS CHAR),CAST(b.export_val AS CHAR )) <> \
(SELECT concat(CAST(s.import_val AS CHAR),CAST(s.export_val AS CHAR )) FROM secex_ymbw s, attrs_bra_pr p \
where p.bra_id = s.bra_id and p.pr_id = b.bra_id and s.bra_id_len=8 and left(s.bra_id,4)=b.bra_id \
and s.wld_id=b.wld_id and s.year=b.year and s.month=b.month \
group by left(s.bra_id,4),s.wld_id_len,s.year) "
self.assertEqual(total, 0)
def test_Growth(self):
print "Entering in checkGrowth"
total=0
#Anual - Check
aggsP = ["secex_ymb","secex_ymbp","secex_ymbpw","secex_ymbw","secex_ymp","secex_ymw"]
for aggs in aggsP:
sql="select count(*) from "+aggs+" s where export_val_growth_val is null and year > 2000"
#Check ir because all values that should be 0 is None
#runCountQuery('checkGrowth_val', aggs, sql)
#Anuaal
years = [("5","_5"),("1","")]
for vals in years:
year=vals[0]
label=vals[1]
#YMB
sql="select count(*) from secex_ymb s where ( \
(s.export_val_growth_pct"+label+" is null and (select export_val from secex_ymb interno where interno.month=s.month and interno.year=s.year-"+year+" and interno.bra_id = s.bra_id) >0 ) \
) and year > 2000"
total+=runCountQuery('checkGrowth_pct', "secex_ymb", sql,cursor,True)
#YMW
sql="select count(*) from secex_ymw s where ( \
(s.export_val_growth_pct"+label+" is null and (select export_val from secex_ymw interno where interno.month=s.month and interno.year=s.year-"+year+" and interno.wld_id = s.wld_id) >0 ) \
) and year > 2000"
total+=runCountQuery('checkGrowth', "secex_ymw", sql,cursor,True)
#YMP
sql="select count(*) from secex_ymp s where ( \
(s.export_val_growth_pct"+label+" is null and (select export_val from secex_ymp interno where interno.month=s.month and interno.year=s.year-"+year+" and interno.hs_id = s.hs_id) >0 ) \
) and year > 2000"
total+=runCountQuery('checkGrowth', "secex_ymp", sql,cursor,True)
#YMBP These 3 sql are taking too long! so disabled
sql="select count(*) from secex_ymbp s where ( \
(s.export_val_growth_pct"+label+" is null and (select export_val from secex_ymbp interno where interno.month=s.month and interno.year=s.year-"+year+" and interno.hs_id = s.hs_id and interno.bra_id = s.bra_id) >0 ) \
) and year > 2000"
#total+=runCountQuery('checkGrowth', "secex_ymbp", sql,cursor,True)
#YMBW
sql="select count(*) from secex_ymbw s where ( \
(s.export_val_growth_pct"+label+" is null and (select export_val from secex_ymbw interno where interno.month=s.month and interno.year=s.year-"+year+" and interno.wld_id = s.wld_id and interno.bra_id = s.bra_id) >0 ) \
) and year > 2000"
#total+=runCountQuery('checkGrowth', "secex_ymbw", sql,cursor,True)
#YMBPW
sql="select count(*) from secex_ymbpw s where ( \
(s.export_val_growth_pct"+label+" is null and (select export_val from secex_ymbpw interno where interno.month=s.month and interno.year=s.year-"+year+" and interno.wld_id = s.wld_id and interno.hs_id = s.hs_id and interno.bra_id = s.bra_id) >0 ) \
) and year > 2000"
#total+=runCountQuery('checkGrowth', "secex_ymbpw", sql,cursor,True)
self.assertEqual(total, 0)
def test_Diversity(self):
print "Entering in checkDiversity"
total=0
sql="SELECT count(*) FROM rais_yi where num_emp >0 and not (cbo_diversity >0 and cbo_diversity_eff >0 and bra_diversity>0 and bra_diversity_eff>0 );"
total+=runCountQuery('checkDiversity', 'rais_yi', sql,cursor,count=True)
sql="SELECT count(*) FROM rais_yb where num_emp >0 and not (cbo_diversity >0 and cbo_diversity_eff >0 and cnae_diversity>0 and cnae_diversity_eff>0 );"
total+=runCountQuery('checkDiversity', 'rais_yb', sql,cursor,count=True)
sql="SELECT count(*) FROM rais_yo where num_emp >0 and not (bra_diversity >0 and bra_diversity_eff >0 and cnae_diversity>0 and cnae_diversity_eff>0 );"
total+=runCountQuery('checkDiversity', 'rais_yo', sql,cursor,count=True)
self.assertEqual(total, 0)
def test_RCA(self):
print "Entering in checkRCA"
total=0
sql="select count(*) from secex_ymbp where (export_val > 0 and (rca is null or rca<=0 or rca_wld is null or rca_wld<=0) ) \
or (export_val =0 and ( rca_wld is not null or rca is not null ) ) ;"
total+=runCountQuery('checkRCA', 'secex_ymbp', sql,cursor,True)
sql="select count(*) from secex_ymp where (export_val > 0 and (rca_wld is null or rca_wld<=0) ) or (export_val =0 and rca_wld is not null);"
total+=runCountQuery('checkRCA', 'secex_ymp', sql,cursor,True)
self.assertEqual(total, 0)
def test_ECI(self):
print "Entering in checkECI"
total=0
sql="select count(*) from secex_ymb b where (b.eci is null or b.eci < -3.1 or b.eci > 3.1) \
and b.export_val >0 and b.bra_id in (select bra_id from secex_ymbp where rca>=1 and month=b.month and year=b.year group by bra_id) "
total+=runCountQuery('checkECI', 'secex_ymb', sql,cursor,True)
sql="select count(*) from secex_ymw where (eci is null or eci < -3.1 or eci > 3.1) and export_val >0"
total+=runCountQuery('checkECI', 'secex_ymw', sql,cursor,True)
self.assertEqual(total, 0)
def test_RCA(self):
print "Entering in checkRCA"
sql="SELECT count(*) FROM rais_ybi where (num_emp > 0 and (rca is null or rca<=0) ) or (num_emp =0 and rca is not null );"
total=runCountQuery('checkRCA', 'rais_ybi', sql,cursor,count=True)
self.assertEqual(total, 0)
def test_Opportunity(self):
print "Entering in checkOpportunity"
sql="select count(*) from rais_ybi where ( opp_gain is null and cnae_id_len=5) or ( opp_gain is not null and cnae_id_len<>5) "
total=runCountQuery('checkOpportunity', 'rais_ybi', sql,cursor,count=True)
self.assertEqual(total, 0)
def test_Importance(self):
print "Entering in checkImportance"
sql="select count(*) from rais_yio where cnae_id_len=5 and cbo_id_len=4 and ( (importance < 0 or importance > 1 ) \
or ( importance is null and num_emp>0) or ( importance is not null and num_emp=0 ) ) "
total=runCountQuery('checkImportance', 'rais_yio', sql,cursor,count=True)
self.assertEqual(total, 0)
def test_Distance(self):
print "Entering in checkDistance"
sql="select count(*) from rais_ybi where (distance<0 or distance>1 ) \
or ( distance is null and cnae_id_len=5) or ( distance is not null and cnae_id_len<>5);"
total=runCountQuery('checkDistance', 'rais_ybi', sql,cursor,count=True)
self.assertEqual(total, 0)
def test_Required(self):
print "Entering in checkRequired"
sql="select count(*) from rais_ybio r where (required < 0 ) or ( required is null and num_emp>0 and \
(select importance from rais_yio where cnae_id=r.cnae_id and cbo_id=r.cbo_id)>=0.2) ;"
total=runCountQuery('checkRequired', 'rais_ybio', sql,cursor,count=True)
self.assertEqual(total, 0)
def test_PCI(self):
print "Entering in checkPCI"
total=0
sql="select count(*) from secex_ymp where (pci is null and hs_id_len=6) or (pci is not null and hs_id_len<>6) or (pci < -3.1 or pci > 3.1) ;"
total+=runCountQuery('checkPCI', 'secex_ymp', sql,cursor,True)
self.assertEqual(total, 0)
def test_Distance(self):
print "Entering in checkDistance"
total=0
sql="select count(*) from secex_ymbp where (distance_wld<0 or distance_wld>1 or distance<0 or distance>1 ) \
or ( (distance_wld is null or distance is null) and hs_id_len=6) or ( (distance_wld is not null or distance is not null) and hs_id_len<>6);"
total+=runCountQuery('checkDistance', 'secex_ymbp', sql,cursor,True)
self.assertEqual(total, 0)
def test_Opportunity(self):
print "Entering in checkOpportunity"
total=0
sql="select count(*) from secex_ymbp where (opp_gain < -3.1 or opp_gain > 3.1 or opp_gain_wld < -3.1 or opp_gain_wld > 3.1 ) \
or ( (opp_gain is null or opp_gain_wld is null) and hs_id_len=6) or ( ( opp_gain_wld is not null or opp_gain is not null) and hs_id_len<>6 ) "
total+=runCountQuery('checkOpportunity', 'secex_ymbp', sql,cursor,True)
self.assertEqual(total, 0)
def test_BRA_ID(self):
print "Entering in checkBRA_ID"
total=0
#YMR
sql="SELECT * FROM ei_ymr r where length(r.bra_id_r) =3 and r.purchase_value <> \
(SELECT sum(purchase_value) FROM ei_ymr s where length(s.bra_id_r) =9 \
and left(s.bra_id_r,3)= r.bra_id_r and r.month=s.month and r.year=s.year)"
total+=runCountQuery('checkBRA_ID', 'ei_ymr', sql,cursor,count=True)
#YMRP
sql="SELECT * FROM ei_ymrp r where length(r.bra_id_r) =3 and r.purchase_value <> \
(SELECT sum(purchase_value) FROM ei_ymrp s where length(s.bra_id_r) =9 \
and left(s.bra_id_r,3)= r.bra_id_r and r.month=s.month and r.year=s.year \
and r.hs_id = s.hs_id)"
total+=runCountQuery('checkBRA_ID', 'ei_ymrp', sql,cursor,count=True)
self.assertEqual(total, 0)
def test_GrowthAnual(self):
print "Entering in checkGrowthAnual"
total=0
#Anual - Check
aggsP = ["rais_yb","rais_ybi","rais_ybio","rais_ybo","rais_yi","rais_yo"]
for aggs in aggsP:
sql="select count(*) from "+aggs+" s where ((wage_growth is null and s.wage is not null) or ( s.num_emp is not null and num_emp_growth is null)) and year > 2002"
#Check ir because all values that should be 0 is None
total+=runCountQuery('checkGrowth', aggs, sql,cursor,count=True)
self.assertEqual(total, 0)
def test_ValUSD(self):
print "Entering in checkValUSD"
total=0
sql="select count(*) from secex_ymb where export_val is null or import_val is null;"
total+=runCountQuery('checkValUSD', 'secex_ymb', sql,cursor,True)
sql="select count(*) from secex_ymbp where export_val is null or import_val is null;"
total+=runCountQuery('checkValUSD', 'secex_ymbp', sql,cursor,True)
sql="select count(*) from secex_ymbpw where export_val is null or import_val is null;"
total+=runCountQuery('checkValUSD', 'secex_ymbpw', sql,cursor,True)
sql="select count(*) from secex_ymbw where export_val is null or import_val is null;"
total+=runCountQuery('checkValUSD', 'secex_ymbw', sql,cursor,True)
sql="select count(*) from secex_ymp where export_val is null or import_val is null;"
total+=runCountQuery('checkValUSD', 'secex_ymp', sql,cursor,True)
self.assertEqual(total, 0)
def test_HS_ID(self):
print "Entering in checkHS_ID"
total=0
aggsP = [(2, 6)]#2,6 [(2, 4),(4, 6)]
for aggs in aggsP:
# YP: Check HS aggs 2 with 4
sql="SELECT count(*) FROM secex_ymp as b where b.hs_id_len="+str(aggs[0])+" and concat(CAST(b.import_val AS CHAR),CAST(b.export_val AS CHAR )) <> \
(SELECT concat(CAST(import_val AS CHAR),CAST(export_val AS CHAR )) FROM secex_ymp \
where hs_id_len="+str(aggs[1])+" and left(hs_id,"+str(aggs[0])+")=b.hs_id and month=b.month and year=b.year \
group by left(hs_id,"+str(aggs[0])+"))"
total+=runCountQuery('checkHS_ID', 'secex_ymp:'+str(aggs[0])+":"+str(aggs[1]), sql,cursor,count=True)
# YBP: Check HS aggs 2 with 4
sql="SELECT count(*) FROM secex_ymbp as b where b.hs_id_len="+str(aggs[0])+" and concat(CAST(b.import_val AS CHAR),CAST(b.export_val AS CHAR )) <> \
(SELECT concat(CAST(import_val AS CHAR),CAST(export_val AS CHAR )) FROM secex_ymbp \
where hs_id_len="+str(aggs[1])+" and left(hs_id,"+str(aggs[0])+")=b.hs_id and bra_id=b.bra_id and month=b.month and year=b.year \
group by left(hs_id,"+str(aggs[0])+"),bra_id_len,year)"
total+=runCountQuery('checkHS_ID', 'secex_ymbp:'+str(aggs[0])+":"+str(aggs[1]), sql,cursor,count=True)
# YPW: Check HS aggs 2 with 4
sql="SELECT count(*) FROM secex_ympw as b where b.hs_id_len="+str(aggs[0])+" and concat(CAST(b.import_val AS CHAR),CAST(b.export_val AS CHAR )) <> \
(SELECT concat(CAST(import_val AS CHAR),CAST(export_val AS CHAR )) FROM secex_ympw \
where hs_id_len="+str(aggs[1])+" and left(hs_id,"+str(aggs[0])+")=b.hs_id and wld_id=b.wld_id and month=b.month and year=b.year \
group by left(hs_id,"+str(aggs[0])+"),wld_id_len,year)"
total+=runCountQuery('checkHS_ID', 'secex_ympw:'+str(aggs[0])+":"+str(aggs[1]), sql,cursor,count=True)
# YBPW: Check HS aggs 2 with 4
sql="SELECT count(*) FROM secex_ymbpw as b where b.hs_id_len="+str(aggs[0])+" and concat(CAST(b.import_val AS CHAR),CAST(b.export_val AS CHAR )) <> \
(SELECT concat(CAST(import_val AS CHAR),CAST(export_val AS CHAR )) FROM secex_ymbpw \
where hs_id_len="+str(aggs[1])+" and left(hs_id,"+str(aggs[0])+")=b.hs_id and bra_id=b.bra_id and wld_id=b.wld_id and month=b.month and year=b.year \
group by left(hs_id,"+str(aggs[0])+"),wld_id_len,bra_id_len,year)"
#runCountQuery('checkHS_ID', 'secex_ymbpw:'+str(aggs[0])+":"+str(aggs[1]), sql,cursor,count=True)
self.assertEqual(total, 0)
def test_distortionage(self):
print "Entering in test_distortionage"
total = 0
aggsP = ['sc_yc', 'sc_ybc']
for aggs in aggsP:
sql = "SELECT * FROM sc_yc where distortion_rate is null and \
left(course_id,2)='xx' and course_id not in ('xx015','xx020','xx021','xx022');"
total += runCountQuery("check_" + campo,
'sc_yd x sc_yd',
sql,
cursor,
count=True)
self.assertEqual(total, 0)
def test_BRA_ID(self):
print "Entering in checkBRA_ID"
total=0
# YMB: Check aggs 2 with 4 (2,7,8)
sql="SELECT * FROM secex_ymb as b where left(bra_id,2)<>'xx' and b.bra_id_len=2 and \
b.import_val <> \
(SELECT sum(import_val) FROM secex_ymb \
where bra_id_len=8 and left(bra_id,2)=b.bra_id and month=b.month and year=b.year \
group by left(bra_id,2))"
total+=runCountQuery('checkBRA_ID', 'secex_ymb', sql,cursor,count=True)
#YMBP - bra_id 2 x 4
sql="SELECT * FROM secex_ymbp as b \
where left(bra_id,2)<>'xx' and b.bra_id_len=2 and \
b.import_val <> \
(SELECT sum(import_val) FROM secex_ymbp \
where bra_id_len=8 and left(bra_id,2)=b.bra_id \
and hs_id=b.hs_id and year=b.year and month=b.month \
group by left(bra_id,2),hs_id_len,year)"
total+=runCountQuery('checkBRA_ID', 'secex_ymbp', sql,cursor,count=True)
#YMBW
sql="SELECT * FROM secex_ymbw as b \
where left(bra_id,2)<>'xx' and b.bra_id_len=2 and \
b.import_val <> \
(SELECT sum(import_val) FROM secex_ymbw \
where bra_id_len=8 and left(bra_id,2)=b.bra_id \
and wld_id=b.wld_id and year=b.year and month=b.month \
group by left(bra_id,2),wld_id_len,year) "
total+=runCountQuery('checkBRA_ID', 'secex_ymbw', sql,cursor,count=True)
self.assertEqual(total, 0)
def checkcampo(self,campo):
total=0
titulo="check_"+campo
sql="SELECT count(*) FROM hedu_ybc b where b.bra_id_len=3 and b."+campo+" <> \
( select sum("+campo+") from hedu_ybc where b.bra_id_len=9 \
and b.course_id=course_id and left(bra_id,3)=b.bra_id and b.year=year )"
total+=runCountQuery(titulo, 'hedu_ybc', sql,cursor,count=True)
sql="SELECT count(*) FROM hedu_ybd b where b.bra_id_len=3 and b."+campo+" <> \
( select sum("+campo+") from hedu_ybd where b.bra_id_len=9 \
and b.d_id=d_id and left(bra_id,3)=b.bra_id and b.year=year )"
total+=runCountQuery(titulo, 'hedu_ybd', sql,cursor,count=True)
sql="SELECT count(*) FROM hedu_ybu b where b.bra_id_len=3 and b."+campo+" <> \
( select sum("+campo+") from hedu_ybu where b.bra_id_len=9 \
and b.university_id=university_id and left(bra_id,3)=b.bra_id and b.year=year )"
total+=runCountQuery(titulo, 'hedu_ybu', sql,cursor,count=True)
sql="SELECT count(*) FROM hedu_yc b where b."+campo+" <> \
( select sum("+campo+") from hedu_yc where b.course_id=course_id and b.year=year )"
total+=runCountQuery(titulo, 'hedu_yc', sql,cursor,count=True)
sql="SELECT count(*) FROM hedu_yd b where b."+campo+" <> \
( select sum("+campo+") from hedu_yd where b.d_id=d_id and b.year=year )"
total+=runCountQuery(titulo, 'hedu_yd', sql,cursor,count=True)
sql="SELECT count(*) FROM hedu_yd b where b."+campo+" <> \
( select sum("+campo+") from hedu_ybd where d_id=b.d_id and b.year=year )"
total+=runCountQuery(titulo, 'hedu_yd x hedu_ybd', sql,cursor,count=True)
sql="SELECT count(*) FROM hedu_yc b where b."+campo+" <> \
( select sum("+campo+") from hedu_ybc where course_id=b.course_id and b.year=year )"
total+=runCountQuery(titulo, 'hedu_yc x hedu_ybc', sql,cursor,count=True)
sql="SELECT count(*) FROM hedu_ybuc b where b.bra_id_len=3 and b."+campo+" <> \
( select sum("+campo+") from hedu_ybuc where b.bra_id_len=9 and course_id=b.course_id \
and b.university_id=university_id and left(bra_id,3)=b.bra_id and b.year=year )"
total+=runCountQuery(titulo, 'hedu_ybuc', sql,cursor,count=True)
# hedu_ybucd - muito longo
return total
def test_Growth(self):
print "Entering in checkGrowth"
total=0
#Anuaal
years = [("5","_5"),("1","")]
for vals in years:
year=vals[0]
label=vals[1]
#YB
sql="select count(*) from rais_yb s where ( s.wage is not null and \
(s.wage_growth"+label+" is null and (select wage from rais_yb interno where interno.year=s.year-"+year+" and interno.bra_id = s.bra_id) >0 ) \
) and year > 2002"
total+=runCountQuery('checkGrowth', "rais_yb:"+year+":"+label, sql,cursor,count=True)
#YO
sql="select count(*) from rais_yo s where ( s.wage is not null and \
(s.wage_growth"+label+" is null and (select wage from rais_yo interno where interno.year=s.year-"+year+" and interno.cbo_id = s.cbo_id) >0 ) \
) and year > 2002"
total+=runCountQuery('checkGrowth', "rais_yo:"+year+":"+label, sql,cursor,count=True)
#YI
sql="select count(*) from rais_yi s where ( s.wage is not null and \
(s.wage_growth"+label+" is null and (select wage from rais_yi interno where interno.year=s.year-"+year+" and interno.cnae_id = s.cnae_id) >0 ) \
) and year > 2002"
total+=runCountQuery('checkGrowth', "rais_yi:"+year+":"+label, sql,cursor,count=True)
#YBI
sql="select count(*) from rais_ybi s where ( s.wage is not null and ( (s.wage_growth"+label+" is null or s.num_emp_growth"+label+" is null ) \
and (select wage from rais_ybi interno where interno.year=s.year-"+year+" and interno.cnae_id = s.cnae_id and interno.bra_id = s.bra_id) >0 ) \
) and year > 2002"
total+=runCountQuery('checkGrowth', "rais_ybi:"+year+":"+label, sql,cursor,count=True)
#YBO
sql="select count(*) from rais_ybo s where ( s.wage is not null and ( (s.wage_growth"+label+" is null or s.num_emp_growth"+label+" is null ) \
and (select wage from rais_ybo interno where interno.year=s.year-"+year+" and interno.cbo_id = s.cbo_id and interno.bra_id = s.bra_id) >0 ) \
) and year > 2002"
total+=runCountQuery('checkGrowth', "rais_ybo:"+year+":"+label, sql,cursor,count=True)
#YBIO
sql="select count(*) from rais_ybio s where ( s.wage is not null and ( (s.wage_growth"+label+" is null or s.num_emp_growth"+label+" is null ) \
and (select wage from rais_ybio interno where interno.year=s.year-"+year+" and interno.cbo_id = s.cbo_id and interno.cnae_id = s.cnae_id and interno.bra_id = s.bra_id) >0 ) \
) and year > 2002"
total+=runCountQuery('checkGrowth', "rais_ybio:"+year+":"+label, sql,cursor,count=True)
self.assertEqual(total, 0)
def test_Wage(self):
print "Entering in checkWage"
total=0
sql="select count(*) from rais_yb where wage is null;"
total+=runCountQuery('checkWage', 'rais_yb', sql,cursor,count=True)
sql="select count(*) from rais_ybi where wage is null;"
total+=runCountQuery('checkWage', 'rais_ybi', sql,cursor,count=True)
sql="select count(*) from rais_ybio where wage is null;"
total+=runCountQuery('checkWage', 'rais_ybio', sql,cursor,count=True)
sql="select count(*) from rais_ybo where wage is null;"
total+=runCountQuery('checkWage', 'rais_ybo', sql,cursor,count=True)
sql="select count(*) from rais_yi where wage is null;"
total+=runCountQuery('checkWage', 'rais_yi', sql,cursor,count=True)
sql="select count(*) from rais_yo where wage is null;"
total+=runCountQuery('checkWage', 'rais_yo', sql,cursor,count=True)
self.assertEqual(total, 0)
def exectest(self, campo):
print "Entering in test_" + campo
total = 0
sql = "SELECT count(*) FROM sc_yd b where b." + campo + " <> \
( select sum(" + campo + ") from sc_yd where d_id=b.d_id and b.year=year )"
total += runCountQuery("check_" + campo,
'sc_yd x sc_yd',
sql,
cursor,
count=True)
sql = "SELECT count(*) FROM sc_yb b where b.bra_id_len=3 and b." + campo + " <> \
( select sum(" + campo + ") from sc_yb where b.bra_id_len=9 and \
left(bra_id,3)=b.bra_id and b.year=year )"
total += runCountQuery("check_" + campo,
'sc_yb x sc_yb',
sql,
cursor,
count=True)
sql = "SELECT count(*) FROM sc_ybc b where b.bra_id_len=3 and b." + campo + " <> \
( select sum(" + campo + ") from sc_ybc where b.bra_id_len=9 and \
left(bra_id,3)=b.bra_id and b.year=year and b.course_id=course_id )"
total += runCountQuery("check_" + campo,
'sc_ybc x sc_ybc',
sql,
cursor,
count=True)
sql = "SELECT count(*) FROM sc_ybd b where b.bra_id_len=3 and b." + campo + " <> \
( select sum(" + campo + ") from sc_ybd where b.bra_id_len=9 and \
left(bra_id,3)=b.bra_id and b.year=year and b.d_id=d_id )"
total += runCountQuery("check_" + campo,
'sc_ybd x sc_ybd',
sql,
cursor,
count=True)
sql = "SELECT count(*) FROM sc_ybs b where b.bra_id_len=3 and b." + campo + " <> \
( select sum(" + campo + ") from sc_ybs where b.bra_id_len=9 and \
left(bra_id,3)=b.bra_id and b.year=year and b.school_id=school_id )"
total += runCountQuery("check_" + campo,
'sc_ybs x sc_ybs',
sql,
cursor,
count=True)
sql = "SELECT count(*) FROM sc_ybc b where b.bra_id_len=3 and b." + campo + " <> \
( select sum(" + campo + ") from sc_ybd where b.bra_id_len=9 and \
left(bra_id,3)=b.bra_id and b.year=year )"
total += runCountQuery("check_" + campo,
'sc_ybc x sc_ybd',
sql,
cursor,
count=True)
sql = "SELECT count(*) FROM sc_ybc b where b.bra_id_len=3 and b." + campo + " <> \
( select sum(" + campo + ") from sc_ybs where b.bra_id_len=9 and \
left(bra_id,3)=b.bra_id and b.year=year )"
total += runCountQuery("check_" + campo,
'sc_ybc x sc_ybs',
sql,
cursor,
count=True)
sql = "SELECT count(*) FROM sc_yc b where b." + campo + " <> \
( select sum(" + campo + ") from sc_ybc where course_id=b.course_id and b.year=year )"
total += runCountQuery("check_" + campo,
'sc_yc x sc_ybc',
sql,
cursor,
count=True)
sql = "SELECT count(*) FROM sc_yb b where b." + campo + " <> \
( select sum(" + campo + ") from sc_ybc where bra_id=b.bra_id and b.year=year )"
total += runCountQuery("check_" + campo,
'sc_yb x sc_ybc',
sql,
cursor,
count=True)
sql = "SELECT count(*) FROM sc_yd b where b." + campo + " <> \
( select sum(" + campo + ") from sc_ybd where d_id=b.d_id and b.year=year )"
total += runCountQuery("check_" + campo,
'sc_yd x sc_ybd',
sql,
cursor,
count=True)
return total
