Overview
Comment:Fix lists and counts for fees and services with no duration and no period set
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk | stable
Files: files | file ages | folders
SHA1: cfb30a9d18c7572f1bc0a2e98a9ca860c63e2973
User & Date: bohwaz on 2020-12-08 18:58:03
Other Links: manifest | tags
Context
2020-12-08
18:59
Fix redirect after having found a single user in quicksearch check-in: 41b2924cc0 user: bohwaz tags: trunk, stable
18:58
Fix lists and counts for fees and services with no duration and no period set check-in: cfb30a9d18 user: bohwaz tags: trunk, stable
18:18
Put cash and waiting cheque accounts in liability if negative check-in: 01f2e75eea user: bohwaz tags: trunk, stable, 1.0.0-rc10
Changes

Modified src/include/lib/Garradin/Entities/Services/Fee.php from [6f825919cc] to [242617c34e].

156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
		];

		$tables = 'services_users su
			INNER JOIN membres m ON m.id = su.id_user
			INNER JOIN services_fees sf ON sf.id = su.id_fee
			LEFT JOIN acc_transactions_users tu ON tu.id_service_user = su.id
			LEFT JOIN acc_transactions_lines l ON l.id_transaction = tu.id_transaction';
		$conditions = sprintf('su.id_fee = %d AND su.paid = 1 AND su.expiry_date >= date()', $this->id());

		$list = new DynamicList($columns, $tables, $conditions);
		$list->groupBy('su.id_user');
		$list->orderBy('date', true);
		$list->setCount('COUNT(DISTINCT su.id_user)');
		return $list;
	}







|







156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
		];

		$tables = 'services_users su
			INNER JOIN membres m ON m.id = su.id_user
			INNER JOIN services_fees sf ON sf.id = su.id_fee
			LEFT JOIN acc_transactions_users tu ON tu.id_service_user = su.id
			LEFT JOIN acc_transactions_lines l ON l.id_transaction = tu.id_transaction';
		$conditions = sprintf('su.id_fee = %d AND su.paid = 1 AND (su.expiry_date >= date() OR su.expiry_date IS NULL)', $this->id());

		$list = new DynamicList($columns, $tables, $conditions);
		$list->groupBy('su.id_user');
		$list->orderBy('date', true);
		$list->setCount('COUNT(DISTINCT su.id_user)');
		return $list;
	}

Modified src/include/lib/Garradin/Entities/Services/Service.php from [5fa29d7db5] to [b46b1da9ff].

103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
			],
		];

		$tables = 'services_users su
			INNER JOIN membres m ON m.id = su.id_user
			INNER JOIN services s ON s.id = su.id_service
			INNER JOIN services_fees sf ON sf.id = su.id_fee';
		$conditions = sprintf('su.id_service = %d AND su.paid = 1 AND su.expiry_date >= date()', $this->id());

		$list = new DynamicList($columns, $tables, $conditions);
		$list->groupBy('su.id_user');
		$list->orderBy('date', true);
		$list->setCount('COUNT(DISTINCT su.id_user)');
		return $list;
	}







|







103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
			],
		];

		$tables = 'services_users su
			INNER JOIN membres m ON m.id = su.id_user
			INNER JOIN services s ON s.id = su.id_service
			INNER JOIN services_fees sf ON sf.id = su.id_fee';
		$conditions = sprintf('su.id_service = %d AND su.paid = 1 AND (su.expiry_date >= date() OR su.expiry_date IS NULL)', $this->id());

		$list = new DynamicList($columns, $tables, $conditions);
		$list->groupBy('su.id_user');
		$list->orderBy('date', true);
		$list->setCount('COUNT(DISTINCT su.id_user)');
		return $list;
	}

Modified src/include/lib/Garradin/Services/Fees.php from [40efe50c3b] to [7018d3147a].

54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
		return $result;
	}

	public function listWithStats()
	{
		$db = DB::getInstance();
		return $db->get('SELECT f.*,
			(SELECT COUNT(DISTINCT id_user) FROM services_users WHERE id_fee = f.id AND expiry_date >= date() AND paid = 1) AS nb_users_ok,
			(SELECT COUNT(DISTINCT id_user) FROM services_users WHERE id_fee = f.id AND expiry_date < date()) AS nb_users_expired,
			(SELECT COUNT(DISTINCT id_user) FROM services_users WHERE id_fee = f.id AND paid = 0) AS nb_users_unpaid
			FROM services_fees f
			WHERE id_service = ?
			ORDER BY amount, transliterate_to_ascii(label) COLLATE NOCASE;', $this->service_id);
	}
}







|







54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
		return $result;
	}

	public function listWithStats()
	{
		$db = DB::getInstance();
		return $db->get('SELECT f.*,
			(SELECT COUNT(DISTINCT id_user) FROM services_users WHERE id_fee = f.id AND (expiry_date >= date() OR expiry_date IS NULL) AND paid = 1) AS nb_users_ok,
			(SELECT COUNT(DISTINCT id_user) FROM services_users WHERE id_fee = f.id AND expiry_date < date()) AS nb_users_expired,
			(SELECT COUNT(DISTINCT id_user) FROM services_users WHERE id_fee = f.id AND paid = 0) AS nb_users_unpaid
			FROM services_fees f
			WHERE id_service = ?
			ORDER BY amount, transliterate_to_ascii(label) COLLATE NOCASE;', $this->service_id);
	}
}

Modified src/include/lib/Garradin/Services/Services.php from [8034954296] to [5ec46b1538].

40
41
42
43
44
45
46
47
48
49
50
51
52
53
		return $out;
	}

	static public function listWithStats()
	{
		$db = DB::getInstance();
		return $db->get('SELECT s.*,
			(SELECT COUNT(DISTINCT id_user) FROM services_users WHERE id_service = s.id AND expiry_date >= date() AND paid = 1) AS nb_users_ok,
			(SELECT COUNT(DISTINCT id_user) FROM services_users WHERE id_service = s.id AND expiry_date < date()) AS nb_users_expired,
			(SELECT COUNT(DISTINCT id_user) FROM services_users WHERE id_service = s.id AND paid = 0) AS nb_users_unpaid
			FROM services s
			ORDER BY transliterate_to_ascii(s.label) COLLATE NOCASE;');
	}
}







|






40
41
42
43
44
45
46
47
48
49
50
51
52
53
		return $out;
	}

	static public function listWithStats()
	{
		$db = DB::getInstance();
		return $db->get('SELECT s.*,
			(SELECT COUNT(DISTINCT id_user) FROM services_users WHERE id_service = s.id AND (expiry_date IS NULL OR expiry_date >= date()) AND paid = 1) AS nb_users_ok,
			(SELECT COUNT(DISTINCT id_user) FROM services_users WHERE id_service = s.id AND expiry_date < date()) AS nb_users_expired,
			(SELECT COUNT(DISTINCT id_user) FROM services_users WHERE id_service = s.id AND paid = 0) AS nb_users_unpaid
			FROM services s
			ORDER BY transliterate_to_ascii(s.label) COLLATE NOCASE;');
	}
}