195 lines
7.3 KiB
JavaScript
195 lines
7.3 KiB
JavaScript
/// <reference path="../pb_data/types.d.ts" />
|
|
migrate((app) => {
|
|
const collection = app.findCollectionByNameOrId("pbc_135889471")
|
|
|
|
// update collection data
|
|
unmarshal({
|
|
"viewQuery": "SELECT\n (p.id || '_' || t.id || '_' || tour.id) as id,\n p.id as player_id,\n (p.first_name || ' ' || p.last_name) as player_name,\n t.id as team_id,\n t.name as team_name,\n tour.id as tournament_id,\n tour.name as tournament_name,\n COUNT(m.id) as matches,\n SUM(CASE\n WHEN (m.home = t.id AND m.home_cups > m.away_cups) OR\n (m.away = t.id AND m.away_cups > m.home_cups)\n THEN 1 ELSE 0\n END) as wins,\n SUM(CASE\n WHEN (m.home = t.id AND m.home_cups < m.away_cups) OR\n (m.away = t.id AND m.away_cups < m.home_cups)\n THEN 1 ELSE 0\n END) as losses,\n SUM(CASE\n WHEN m.home = t.id THEN m.home_cups\n WHEN m.away = t.id THEN m.away_cups\n ELSE 0\n END) as total_cups_made,\n SUM(CASE\n WHEN m.home = t.id THEN m.away_cups\n WHEN m.away = t.id THEN m.home_cups\n ELSE 0\n END) as total_cups_against\n FROM players p, teams t, matches m, tournaments tour\n WHERE\n t.players LIKE '%\"' || p.id || '\"%' AND\n (m.home = t.id OR m.away = t.id) AND\n m.tournament = tour.id AND\n m.status = 'ended'\n GROUP BY p.id, t.id, tour.id"
|
|
}, collection)
|
|
|
|
// remove field
|
|
collection.fields.removeById("_clone_976G")
|
|
|
|
// remove field
|
|
collection.fields.removeById("_clone_njLe")
|
|
|
|
// remove field
|
|
collection.fields.removeById("_clone_ZNMy")
|
|
|
|
// remove field
|
|
collection.fields.removeById("_clone_nxTv")
|
|
|
|
// remove field
|
|
collection.fields.removeById("json3041953980")
|
|
|
|
// remove field
|
|
collection.fields.removeById("json1531431708")
|
|
|
|
// remove field
|
|
collection.fields.removeById("json1062535948")
|
|
|
|
// remove field
|
|
collection.fields.removeById("json4249694556")
|
|
|
|
// add field
|
|
collection.fields.addAt(4, new Field({
|
|
"autogeneratePattern": "",
|
|
"hidden": false,
|
|
"id": "_clone_YqC8",
|
|
"max": 0,
|
|
"min": 0,
|
|
"name": "team_name",
|
|
"pattern": "",
|
|
"presentable": false,
|
|
"primaryKey": false,
|
|
"required": true,
|
|
"system": false,
|
|
"type": "text"
|
|
}))
|
|
|
|
// add field
|
|
collection.fields.addAt(6, new Field({
|
|
"autogeneratePattern": "",
|
|
"hidden": false,
|
|
"id": "_clone_jZTo",
|
|
"max": 0,
|
|
"min": 0,
|
|
"name": "tournament_name",
|
|
"pattern": "",
|
|
"presentable": false,
|
|
"primaryKey": false,
|
|
"required": true,
|
|
"system": false,
|
|
"type": "text"
|
|
}))
|
|
|
|
return app.save(collection)
|
|
}, (app) => {
|
|
const collection = app.findCollectionByNameOrId("pbc_135889471")
|
|
|
|
// update collection data
|
|
unmarshal({
|
|
"viewQuery": "\n SELECT\n (p.id || '_' || t.id || '_' || tour.id) as id,\n p.id as player_id,\n (p.first_name || ' ' || p.last_name) as player_name,\n p.first_name,\n p.last_name,\n t.id as team_id,\n t.name as team_name,\n tour.id as tournament_id,\n tour.name as tournament_name,\n COUNT(m.id) as matches,\n SUM(CASE\n WHEN (m.home = t.id AND m.home_cups > m.away_cups) OR\n (m.away = t.id AND m.away_cups > m.home_cups)\n THEN 1 ELSE 0\n END) as wins,\n SUM(CASE\n WHEN (m.home = t.id AND m.home_cups < m.away_cups) OR\n (m.away = t.id AND m.away_cups < m.home_cups)\n THEN 1 ELSE 0\n END) as losses,\n AVG(CASE\n WHEN m.home = t.id AND m.home_cups > m.away_cups\n THEN m.home_cups - m.away_cups\n WHEN m.away = t.id AND m.away_cups > m.home_cups\n THEN m.away_cups - m.home_cups\n ELSE NULL\n END) as margin_of_victory,\n AVG(CASE\n WHEN m.home = t.id AND m.home_cups < m.away_cups\n THEN m.away_cups - m.home_cups\n WHEN m.away = t.id AND m.away_cups < m.home_cups\n THEN m.home_cups - m.away_cups\n ELSE NULL\n END) as margin_of_loss,\n SUM(CASE\n WHEN m.home = t.id THEN m.home_cups\n WHEN m.away = t.id THEN m.away_cups\n ELSE 0\n END) as total_cups_won_by,\n SUM(CASE\n WHEN m.home = t.id THEN m.away_cups\n WHEN m.away = t.id THEN m.home_cups\n ELSE 0\n END) as total_cups_lost_by,\n SUM(CASE\n WHEN m.home = t.id THEN m.home_cups\n WHEN m.away = t.id THEN m.away_cups\n ELSE 0\n END) as total_cups_made,\n SUM(CASE\n WHEN m.home = t.id THEN m.away_cups\n WHEN m.away = t.id THEN m.home_cups\n ELSE 0\n END) as total_cups_against\n FROM players p\n JOIN teams t ON json_extract(t.players, '$[*]') LIKE '%' || p.id || '%'\n JOIN matches m ON (m.home = t.id OR m.away = t.id)\n JOIN tournaments tour ON m.tournament = tour.id\n WHERE m.status = 'ended'\n GROUP BY p.id, t.id, tour.id"
|
|
}, collection)
|
|
|
|
// add field
|
|
collection.fields.addAt(3, new Field({
|
|
"autogeneratePattern": "",
|
|
"hidden": false,
|
|
"id": "_clone_976G",
|
|
"max": 0,
|
|
"min": 0,
|
|
"name": "first_name",
|
|
"pattern": "",
|
|
"presentable": false,
|
|
"primaryKey": false,
|
|
"required": true,
|
|
"system": false,
|
|
"type": "text"
|
|
}))
|
|
|
|
// add field
|
|
collection.fields.addAt(4, new Field({
|
|
"autogeneratePattern": "",
|
|
"hidden": false,
|
|
"id": "_clone_njLe",
|
|
"max": 0,
|
|
"min": 0,
|
|
"name": "last_name",
|
|
"pattern": "",
|
|
"presentable": false,
|
|
"primaryKey": false,
|
|
"required": false,
|
|
"system": false,
|
|
"type": "text"
|
|
}))
|
|
|
|
// add field
|
|
collection.fields.addAt(6, new Field({
|
|
"autogeneratePattern": "",
|
|
"hidden": false,
|
|
"id": "_clone_ZNMy",
|
|
"max": 0,
|
|
"min": 0,
|
|
"name": "team_name",
|
|
"pattern": "",
|
|
"presentable": false,
|
|
"primaryKey": false,
|
|
"required": true,
|
|
"system": false,
|
|
"type": "text"
|
|
}))
|
|
|
|
// add field
|
|
collection.fields.addAt(8, new Field({
|
|
"autogeneratePattern": "",
|
|
"hidden": false,
|
|
"id": "_clone_nxTv",
|
|
"max": 0,
|
|
"min": 0,
|
|
"name": "tournament_name",
|
|
"pattern": "",
|
|
"presentable": false,
|
|
"primaryKey": false,
|
|
"required": true,
|
|
"system": false,
|
|
"type": "text"
|
|
}))
|
|
|
|
// add field
|
|
collection.fields.addAt(12, new Field({
|
|
"hidden": false,
|
|
"id": "json3041953980",
|
|
"maxSize": 1,
|
|
"name": "margin_of_victory",
|
|
"presentable": false,
|
|
"required": false,
|
|
"system": false,
|
|
"type": "json"
|
|
}))
|
|
|
|
// add field
|
|
collection.fields.addAt(13, new Field({
|
|
"hidden": false,
|
|
"id": "json1531431708",
|
|
"maxSize": 1,
|
|
"name": "margin_of_loss",
|
|
"presentable": false,
|
|
"required": false,
|
|
"system": false,
|
|
"type": "json"
|
|
}))
|
|
|
|
// add field
|
|
collection.fields.addAt(14, new Field({
|
|
"hidden": false,
|
|
"id": "json1062535948",
|
|
"maxSize": 1,
|
|
"name": "total_cups_won_by",
|
|
"presentable": false,
|
|
"required": false,
|
|
"system": false,
|
|
"type": "json"
|
|
}))
|
|
|
|
// add field
|
|
collection.fields.addAt(15, new Field({
|
|
"hidden": false,
|
|
"id": "json4249694556",
|
|
"maxSize": 1,
|
|
"name": "total_cups_lost_by",
|
|
"presentable": false,
|
|
"required": false,
|
|
"system": false,
|
|
"type": "json"
|
|
}))
|
|
|
|
// remove field
|
|
collection.fields.removeById("_clone_YqC8")
|
|
|
|
// remove field
|
|
collection.fields.removeById("_clone_jZTo")
|
|
|
|
return app.save(collection)
|
|
})
|