Context Navigation

db.php

Revision 1, 16.2 KB (checked in by andrewgunn, 14 years ago)
Added original EVE-Killboard.net source code.

Line
1	<?
2	require_once( "config.php" );
3	require_once( "globals.php" );
4
5	class DBConnection {
6
7	function DBConnection()
8	{
9	if ( !$this->id_ = mysql_pconnect( DB_HOST, DB_USER, DB_PASS ) )
10	die( "Unable to connect to mysql database." );
11
12	mysql_selectdb( DB_NAME );
13	}
14
15	function id() { return $this->id_; }
16
17	function affectedRows()
18	{
19	return mysql_affected_rows( $this->id_ );
20	}
21	}
22
23	class DBQuery {
24
25	function DBQuery()
26	{
27	$this->executed_ = false;
28	$this->dbconn_ = new DBConnection;
29	}
30
31	function execute( $sql )
32	{
33	$t1 = strtok(microtime(), ' ') + strtok('');
34
35	$this->resid_ = mysql_query( $sql, $this->dbconn_->id() );
36
37	if ( !$this->resid_ ) {
38	echo "Database error: ".mysql_error( $this->dbconn_->id() )."<br>";
39	echo "SQL: ".$sql."<br>";
40	exit;
41	}
42
43	$this->exectime_ = strtok(microtime(), ' ') + strtok('') - $t1;
44	$this->executed_ = true;
45
46	if ( KB_PROFILE == 2 ) {
47	file_put_contents ( '/tmp/profile.lst', $sql."\nExecution time: ".$this->exectime_."\n", FILE_APPEND );
48	}
49
50	return true;
51	}
52
53	function recordCount()
54	{
55	return mysql_num_rows( $this->resid_ );
56	}
57
58	function getRow()
59	{
60	$row = mysql_fetch_assoc( $this->resid_ );
61
62	return $row;
63	}
64
65	function rewind()
66	{
67	@mysql_data_seek ( $this->resid_, 0 );
68	}
69
70	function getInsertID()
71	{
72	return mysql_insert_id();
73	}
74
75	function execTime()
76	{
77	return $this->exectime_;
78	}
79
80	function executed()
81	{
82	return $this->executed_;
83	}
84
85	function getErrorMsg()
86	{
87	$msg = $this->sql_."<br>";
88	$msg .= "Query failed. ".mysql_error( $this->dbconn_->id() );
89
90	return $msg;
91	}
92	}
93
94	class DBAction
95	{
96
97	function DBAction()
98	{
99	$this->dbconn_ = new DBConnection();
100	}
101
102	function setDateTime( $timestamp )
103	{
104	$this->timestamp_ = $timestamp;
105	}
106
107	function addPilot( $pilot, $corpid, $timestamp )
108	{
109	$sql = "select * from kb3_pilots where plt_name = '".addslashes( stripslashes( $pilot ) )."'";
110	$qry = new DBQuery( $this->dbconn_, $sql );
111	$qry->execute() or die( $qry->getErrorMsg() );
112
113	if ( $qry->recordCount() == 0 ) {
114	$sql = "insert into kb3_pilots values ( null, '".addslashes( stripslashes( $pilot ) )."', ".$corpid.", 0, 0, 0, date_format('".$timestamp."', '%Y.%m.%d %H:%i:%s') )";
115	$qry = new DBQuery( $this->dbconn_, $sql ) or die ( $qry->getErrorMsg() );
116	$qry->execute() or die( $qry->getErrorMsg() );
117	$pilotid = $qry->getInsertID();
118	}
119	else {
120	$row = $qry->getRow();
121	$pilotid = $row['plt_id'];
122	$dbaction = new DBAction();
123
124	if ( $dbaction->isPilotUpdatable( $pilotid, $timestamp ) && $$row['plt_crp_id'] != $corpid ) {
125	$qry = new DBQuery( $this->dbconn_, "update kb3_pilots set plt_crp_id = ".$corpid.", plt_updated = date_format('".$timestamp."', '%Y.%m.%d %H:%i:%s') where plt_id = ".$pilotid );
126	$qry->execute() or die( $qry->getErrorMsg() );
127	}
128	}
129
130	return $pilotid;
131	}
132
133	function setCharacterId( $pilotid, $charid )
134	{
135	$sql = "update kb3_pilots set plt_externalid = ".$charid." where plt_id = ".$pilotid;
136
137	$qry = new DBQuery( $this->dbconn_, $sql );
138	$qry->execute();
139	}
140
141	function addAlliance( $alliance )
142	{
143	$sql = "select * from kb3_alliances where all_name = '".addslashes( stripslashes($alliance ) )."'";
144	$qry = new DBQuery( $this->dbconn_, $sql );
145	$qry->execute() or die( $qry->getErrorMsg() );
146
147	if ( $qry->recordCount() == 0 ) {
148	$sql = "insert into kb3_alliances values ( null, '".addslashes( stripslashes( $alliance ) )."', 'default' )";
149	$qry = new DBQuery( $this->dbconn_, $sql );
150	$qry->execute() or die( $qry->getErrorMsg() );
151	$allianceid = $qry->getInsertID();
152	}
153	else {
154	$row = $qry->getRow();
155	$allianceid = $row['all_id'];
156	}
157
158	return $allianceid;
159	}
160
161	function addCorp( $corp, $allianceid, $timestamp )
162	{
163	$sql = "select * from kb3_corps where crp_name = '".addslashes( stripslashes( $corp ) )."'";
164	$qry = new DBQuery( $this->dbconn_, $sql );
165	$qry->execute() or die( $qry->getErrorMsg() );
166
167	if ( $qry->recordCount() == 0 ) {
168	$sql = "insert into kb3_corps values ( null, '".addslashes( stripslashes($corp ) )."', ".$allianceid.", 0, date_format('".$timestamp."', '%Y.%m.%d %H:%i:%s') )";
169	$qry = new DBQuery( $this->dbconn_, $sql );
170	$qry->execute() or die( $qry->getErrorMsg() );
171	$corpid = $qry->getInsertID();
172	}
173	else {
174	$row = $qry->getRow();
175	$corpid = $row['crp_id'];
176
177	$dbaction = new DBAction();
178
179
180	if ( $dbaction->isCorpUpdatable( $corpid, $timestamp ) && $row['crp_all_id'] != $allianceid && $row['crp_trial'] != 1 ) {
181	$sql = "update kb3_corps set crp_all_id = ".$allianceid.", crp_updated = date_format('".$timestamp."', '%Y.%m.%d %H:%i:%s')
182	where crp_id = ".$corpid;
183	$qry = new DBQuery( $this->dbconn_, $sql );
184	$qry->execute() or die( $qry->getErrorMsg() );
185	}
186	}
187
188	return $corpid;
189	}
190
191	function addShip( $ship, $description, $baseprice )
192	{
193	$sql = "select * from kb3_ships where shp_name = '".addslashes( stripslashes($ship ) )."'";
194	$qry = new DBQuery( $this->dbconn_, $sql );
195	$qry->execute();
196
197	if ( $qry->recordCount() == 0 ) {
198	$sql = "insert into kb3_ships values ( null, '".addslashes( stripslashes($ship ) )."', 0, 0, 0, null, 0 )";
199	$qry = new DBQuery( $this->dbconn_, $sql );
200	$qry->execute();
201	$shipid = $qry->getInsertID();
202	}
203	else {
204	$row = $qry->getRow();
205	$shipid = $row['shp_id'];
206	if ( $description != "" ) {
207	$sql = "update kb3_ships set shp_description = '".addslashes( stripslashes( $description ) )."', shp_baseprice = ".$baseprice." where shp_id = ".$shipid;
208	$qry = new DBQuery( $this->dbconn_, $sql ) or die( $qry->getErrorMsg() );
209	$qry->execute();
210	}
211	}
212
213	return $shipid;
214	}
215
216	function addItem( $item, $description, $volume, $type, $icon )
217	{
218	$sql = "select * from kb3_items where itm_name = '".addslashes( stripslashes($item ) )."'";
219	$qry = new DBQuery( $this->dbconn_, $sql ) or die ( $qry->getErrorMsg() );
220	$qry->execute();
221
222	if ( $qry->recordCount() == 0 ) {
223	$sql = "insert into kb3_items values ( null, '".addslashes( stripslashes($item ) )."', '".addslashes( stripslashes($description ) )."', ".$volume.", ".$type.", null, 1, '".$icon."' )";
224	$qry = new DBQuery( $this->dbconn_, $sql );
225	$qry->execute() or die ( $qry->getErrorMsg() );
226	$itemid = $qry->getInsertID();
227	return -1;
228	}
229	else {
230	$row = $qry->getRow();
231	$itemid = $row['itm_id'];
232	if ( $description != "" ) {
233	$sql = "update kb3_items set itm_description = '".addslashes( stripslashes( $description ) )."', itm_volume = ".$volume.", itm_type = ".$type.", itm_icon = '".$icon."' where itm_id = ".$itemid;
234	$qry = new DBQuery( $this->dbconn_, $sql );
235	$qry->execute() or die ( $qry->getErrorMsg() );
236	}
237	}
238
239	return $itemid;
240	}
241
242	function setIcon( $itemid, $icon )
243	{
244	$sql = "update kb3_items set itm_icon = '".$icon."' where itm_id = ".$itemid;
245	$qry = new DBQuery( $this->dbconn_, $sql );
246	$qry->execute();
247	}
248
249	function addSystem( $system, $systemsec )
250	{
251	$sql = "select * from kb3_systems where sys_name = '".addslashes( stripslashes($system ) )."'";
252	$qry = new DBQuery( $this->dbconn_, $sql );
253	$qry->execute() or die ( $qry->getErrorMsg() );
254
255	if ( $qry->recordCount() == 0 ) {
256	$sql = "insert into kb3_systems values ( null, '".addslashes( stripslashes( $system ) )."', '".$systemsec."', 0 )";
257	$qry = new DBQuery( $this->dbconn_, $sql );
258	$qry->execute() or die ( $qry->getErrorMsg() );
259	$systemid = $qry->getInsertID();
260	}
261	else {
262	$row = $qry->getRow();
263	$systemid = $row['sys_id'];
264	}
265
266	return $systemid;
267	}
268
269	function addInvolved( $killid, $pilotid, $secstatus, $allianceid, $corpid, $shipid, $weaponid, $finalblow, $order )
270	{
271	$sql = "insert into _".KB_SITE."_involved values ( ".$killid.", ".$pilotid.", '".$secstatus."', ".$allianceid.", ".$corpid.", ".$shipid.", ".$weaponid.", ".$finalblow.", ".$order." )";
272	$qry = new DBQuery( $this->dbconn_, $sql );
273	$qry->execute() or die ( $qry->getErrorMsg() );
274	}
275
276	function addDestroyedItem( $killid, $itemid, $quantity, $location )
277	{
278	$qry = new DBQuery( $this->dbconn_, "select itl_id from kb3_item_locations where itl_location = '".$location."'" );
279	$qry->execute() or die( $qry->getErrorMsg() );
280	$row = $qry->getRow();
281	if ( $row['itl_id'] != "" )
282	$locationid = $row['itl_id'];
283	else
284	$locationid = 0;
285
286	$sql = "insert into _".KB_SITE."_items_destroyed values ( ".$killid.", ".$itemid.", ".$quantity.", ".$locationid." )";
287	$qry = new DBQuery( $this->dbconn_, $sql );
288	$qry->execute() or die ( $qry->getErrorMsg() );
289	}
290
291	function addKill( $timestamp, $victimid, $allianceid, $corpid, $shipid, $systemid )
292	{
293	$killid = 0;
294	// check if the killmail already exists
295	$sql = "select * from _".KB_SITE."_kills where kll_timestamp = date_format('".$timestamp."', '%Y.%m.%d %H:%i:%s') and kll_victim_id = ".$victimid." and kll_ship_id = ".$shipid;
296	$qry = new DBQuery( $this->dbconn_, $sql );
297	$qry->execute();
298
299	if ( $qry->recordCount() == 0 ) {
300	$sql = "insert into _".KB_SITE."_kills values ( null, date_format('".$timestamp."', '%Y.%m.%d %H:%i:%s'), ".$victimid.", ".$allianceid.", ".$corpid.", ".$shipid.", ".$systemid." )";
301	$qry = new DBQuery( $this->dbconn_, $sql );
302	$qry->execute() or die ( $qry->getErrorMsg() );
303	$killid = $qry->getInsertID();
304
305	// log entry
306	//$sql = "insert into kb3_log values ( ".$killid.", '".$_SERVER['REMOTE_ADDR']."', now() )";
307	//$qry = new DBQuery( $this->dbconn_, $sql );
308	//$qry->execute() or die ( $qry->getErrorMsg() );
309	}
310
311	return $killid;
312	}
313
314	function rollback( $killid )
315	{
316	$qry = new DBQuery( $this->dbconn_, "delete from _".KB_SITE."_kills where kll_id = ".$killid );
317	$qry->execute();
318
319	$qry = new DBQuery( $this->dbconn_, "delete from _".KB_SITE."_involved where inv_kll_id = ".$killid );
320	$qry->execute();
321
322	$qry = new DBQuery( $this->dbconn_, "delete from _".KB_SITE."_items_destroyed where itd_kll_id = ".$killid );
323	$qry->execute();
324	}
325
326	function getRawMail( $killid, $site = "" )
327	{
328	if ( $site == "" )
329	$site = KB_SITE;
330	$sql = "select kll.kll_timestamp, plt.plt_id, plt.plt_name, plt.plt_externalid, crp.crp_id, crp.crp_name, ali.all_id, ali.all_name, shp.shp_name, shp.shp_externalid, scl.scl_class, sys.sys_name, sys.sys_sec, kll.kll_system_id
331	from _".$site."_kills kll, kb3_pilots plt, kb3_corps crp,
332	kb3_alliances ali, kb3_ships shp, kb3_ship_classes scl,
333	kb3_systems sys
334	where kll.kll_id = ".$killid."
335	and plt.plt_id = kll.kll_victim_id
336	and crp.crp_id = kll.kll_crp_id
337	and ali.all_id = kll.kll_all_id
338	and shp.shp_id = kll.kll_ship_id
339	and sys.sys_id = kll.kll_system_id
340	and scl.scl_id = shp.shp_class";
341
342	$qry = new DBQuery();
343	if ( !$qry->execute( $sql ) ) die( $qry->getErrorMsg() );
344	$row = $qry->getRow();
345
346	$mail .= substr( str_replace( '-', '.' , $row['kll_timestamp'] ), 0, 16 )."\r\n\r\n";
347	$mail .= "Victim: ".$row['plt_name']."\r\n";
348	$mail .= "Alliance: ".$row['all_name']."\r\n";
349	$mail .= "Corp: ".$row['crp_name']."\r\n";
350	$mail .= "Destroyed: ".$row['shp_name']."\r\n";
351	$mail .= "System: ".$row['sys_name']."\r\n";
352	$mail .= "Security: ".roundsec( $row['sys_sec'] )."\r\n\r\n";
353	$mail .= "Involved parties:\r\n\r\n";
354
355	$sql = "select plt.plt_id, plt.plt_name, plt.plt_externalid, crp.crp_name, ali.all_name, shp.shp_name, shp.shp_externalid, scl.scl_class,
356	inv.inv_final_blow, itm.itm_name, inv.inv_sec_status
357	from _".$site."_involved inv, kb3_pilots plt, kb3_corps crp, kb3_alliances ali,
358	kb3_ships shp, kb3_ship_classes scl, kb3_items itm
359	where inv.inv_kll_id = ".$killid."
360	and plt.plt_id = inv.inv_plt_id
361	and crp.crp_id = inv.inv_crp_id
362	and ali.all_id = inv.inv_all_id
363	and shp.shp_id = inv.inv_shp_id
364	and scl.scl_id = shp.shp_class
365	and itm.itm_id = inv.inv_wep_id
366	order by inv.inv_order";
367
368	$qry = new DBQuery();
369	$qry->execute( $sql );
370
371	while ( $row = $qry->getRow() )
372	{
373	$mail .= "Name: ".$row['plt_name'];
374	if ( $row['inv_final_blow'] == 1 )
375	$mail .= " (laid the final blow)";
376	$mail .= "\r\n";
377
378	$mail .= "Security: ".$row['inv_sec_status']."\r\n";
379	$mail .= "Alliance: ".$row['all_name']."\r\n";
380	$mail .= "Corp: ".$row['crp_name']."\r\n";
381	$mail .= "Ship: ".$row['shp_name']."\r\n";
382	$mail .= "Weapon: ".$row['itm_name']."\r\n\r\n";
383	}
384
385	$sql = "select itd_quantity, itm.itm_name, itd.itd_itl_id
386	from _".$site."_items_destroyed itd, kb3_items itm
387	where itd.itd_kll_id = ".$killid."
388	and itm.itm_id = itd.itd_itm_id
389	order by itd.itd_itl_id";
390
391	$qry = new DBQuery();
392	$qry->execute( $sql ) or die( $qry->getErrorMsg() );
393
394	if ( $qry->recordCount() ) {
395	$mail .= "\r\nDestroyed items:\r\n\r\n";
396
397	while ( $row = $qry->getRow() )
398	{
399	$mail .= $row['itm_name'];
400	if ( $row['itd_quantity'] > 1 )
401	$mail .= ", Qty: ".$row['itd_quantity'];
402	if ( $row['itd_itl_id'] == 4 ) // cargo
403	$mail .= " (Cargo)";
404	if ( $row['itd_itl_id'] == 6 ) // drone
405	$mail .= " (Drone Bay)";
406	$mail .= "\r\n";
407	}
408	}
409
410	return $mail;
411	}
412
413	function getPostPassword()
414	{
415	$qry = new DBQuery( $this->dbconn_, "select cfg_value from kb3_config where cfg_site = '".KB_SITE."' and cfg_key = 'post_password'" );
416	$qry->execute();
417	$row = $qry->getRow();
418
419	return $row['cfg_value'];
420	}
421
422	function getPostMailto()
423	{
424	$qry = new DBQuery( $this->dbconn_, "select cfg_value from kb3_config where cfg_site = '".KB_SITE."' and cfg_key = 'post_mailto'" );
425	$qry->execute();
426	$row = $qry->getRow();
427
428	return $row['cfg_value'];
429	}
430
431	function setPostPassword( $password )
432	{
433	$qry = new DBQuery( $this->dbconn_, "update kb3_config set cfg_value = '".$password."' where cfg_site = '".KB_SITE."' and cfg_key = 'post_password'" );
434	$qry->execute();
435	}
436
437	function setPostMailto( $mailto )
438	{
439	$qry = new DBQuery( $this->dbconn_, "update kb3_config set cfg_value = '".$mailto."' where cfg_site = '".KB_SITE."' and cfg_key = 'post_mailto'" );
440	$qry->execute();
441	}
442
443	function isCorpUpdatable( $crp_id, $timestamp )
444	{
445	$qry = new DBQuery( $this->dbconn_,
446	"select crp_id from kb3_corps
447	where crp_id = ".$crp_id."
448	and ( crp_updated < date_format('".$timestamp."', '%Y.%m.%d %H:%i') or crp_updated is null )" );
449	$qry->execute() or die( $qry->getErrorMsg() );
450	return $qry->recordCount();
451	}
452
453	function isPilotUpdatable( $plt_id, $timestamp )
454	{
455	$qry = new DBQuery( $this->dbconn_,
456	"select plt_id from kb3_pilots
457	where plt_id = ".$plt_id."
458	and ( plt_updated < date_format('".$timestamp."', '%Y.%m.%d %H:%i') or plt_updated is null )" );
459	$qry->execute() or die( $qry->getErrorMsg() );
460
461	return $qry->recordCount();
462	}
463	}
464
465	?>

Note: See TracBrowser for help on using the browser.

Context Navigation

root/original/common/db.php

Download in other formats: