def GET(self,shipId):
db =DBEngine().getInstance()
web.header('Content-Type','application/json; charset=utf-8', unique=True)
ship = db.select(
['invtypes as i', 'trntranslationcolumns as t1', 'trntranslations as t11',
'invgroups as g', 'trntranslationcolumns as t2', 'trntranslations as t22',
'chrraces as c', 'trntranslationcolumns as t3', 'trntranslations as t33',
'trntranslationcolumns as t4', 'trntranslations as t44'],
vars = {'sid' : shipId},
what = 'i.typeid, t11.text as name, t22.text as `group`, t33.text as race, t44.text as `desc`, i.radius, i.mass, i.volume, i.capacity ',
where = 't11.keyid=i.typeid and t1.tablename="dbo.invtypes" and t1.columnName="typename" and t1.tcid=t11.tcid and t11.languageid="ZH"'+
' and t22.keyid=g.groupid and t2.tablename="dbo.invgroups" and t2.columnName="groupname" and t2.tcid=t22.tcid and t22.languageid="ZH"'+
' and t33.keyid=c.raceid and t3.tablename="dbo.chrraces" and t3.columnName="racename" and t3.tcid=t33.tcid and t33.languageid="ZH"'+
' and t44.keyid=i.typeid and t4.tablename="dbo.invtypes" and t4.columnName="description" and t4.tcid=t44.tcid and t44.languageid="ZH"'+
' and i.typeid=$sid and i.groupid=g.groupid and c.raceid=i.raceid')
attr = db.select(
['dgmtypeattributes AS dt', 'dgmattributetypes as d',
'trntranslationcolumns AS t1', 'trntranslations AS t11'],
vars = {'sid' : shipId},
what = 't11.text AS displayname,dt.attributeid, coalesce(dt.valuefloat,dt.valueint) as value , d.categoryid',
where = 'dt.typeid=$sid AND d.attributeid=dt.attributeid'+
' and (dt.attributeID <> 182 AND dt.attributeID <> 277) AND (dt.attributeID <> 183 AND dt.attributeID <> 278) '+
' and (dt.attributeID <> 184 AND dt.attributeID <> 279) AND (dt.attributeID <> 1285 AND dt.attributeID <> 1286) '+
' and (dt.attributeID <> 1289 AND dt.attributeID <> 1287) AND (dt.attributeID <> 1290 AND dt.attributeID <> 1288)'+
' AND t11.keyid=dt.attributeid AND t1.tablename="dbo.dgmattributetypes" AND t1.columnname="displayname" AND t1.tcid=t11.tcid AND t11.languageid="ZH"')
skillssql = '''select tt.text as skill, COALESCE(skillLevel.valueFloat, skillLevel.valueInt) AS requiredLevel, attr.attributeid,output.*
from ( select prereqs(dgmtypeattributes.typeid) as id, @level as treelevel, @parent as parent, substr(@path,2) as path
from ( select @start_with:=$typeid, @id:=@start_with,@level:=0,@parent:=0,@path:="" ) vars, dgmtypeattributes
where @id is not null ) output inner join trntranslations as tt on tt.tcid=8 and tt.languageid="zh" and tt.keyid=output.id
INNER JOIN dgmtypeattributes AS attr ON attr.typeID = output.parent AND attr.attributeID IN (182,183,184,1285,1289,1290) AND COALESCE(attr.valueFloat, attr.valueInt) = output.id
INNER JOIN dgmtypeattributes AS skillLevel ON skillLevel.typeID = output.parent AND skillLevel.attributeID IN (277,278,279,1286,1287,1288)
where ( (attr.attributeID = 182 AND skillLevel.attributeID = 277) OR (attr.attributeID = 183 AND skillLevel.attributeID = 278) OR
(attr.attributeID = 184 AND skillLevel.attributeID = 279) OR (attr.attributeID = 1285 AND skillLevel.attributeID = 1286) OR
(attr.attributeID = 1289 AND skillLevel.attributeID = 1287) OR (attr.attributeID = 1290 AND skillLevel.attributeID = 1288)) order by treelevel,attr.attributeid'''
skills = db.query(skillssql,vars={'typeid':shipId})
shipJson = simplejson.dumps(ship[0])
attrJson = simplejson.dumps(attr.list())
skillJson = simplejson.dumps(skills.list())
shipJson = simplejson.loads(shipJson)
shipJson['prop'] = simplejson.loads(attrJson)
shipJson['skills'] = simplejson.loads(skillJson)
return simplejson.dumps(shipJson)
