function datei()
{
// Check for request forgeries
JRequest::checkToken() or die('Invalid Token');
$db = JFactory::getDBO();
$app = JFactory::getApplication();
$option = JRequest::getCmd('option');
$jinput = $app->input;
$liga = $jinput->get('filter_lid', null, null);
$mt = $jinput->get('filter_mt', null, null);
$et = $jinput->get('filter_et', null, null);
$format = $jinput->get('filter_format', null, null);
$sid = clm_core::$access->getSeason();
// Link zum redirect generieren
$adminLink = new AdminLink();
$adminLink->view = "auswertung";
$adminLink->makeURL();
// Dateinamen zusammensetzen
$date = JFactory::getDate();
$now = $date->toSQL();
$datum = JHTML::_('date', $now, JText::_('d-m-Y__H-i-s'));
// Grunddaten für Ligen und Mannschaftsturniere laden
if ($liga != null or $mt != null) {
// Mannschaftsturnier eine Liga ID zuweisen
if ($mt != null) {
$liga = $mt;
}
$sql = " SELECT a.* FROM #__clm_liga as a" . " LEFT JOIN #__clm_saison as s ON s.id = a.sid" . " WHERE s.archiv = 0 AND a.id = " . $liga;
$db->setQuery($sql);
$liga_name = $db->loadObjectList();
$anzahl_runden = $liga_name[0]->runden * $liga_name[0]->durchgang;
// Zeit der letzten Runde
$sql = " SELECT a.* FROM #__clm_runden_termine as a" . " LEFT JOIN #__clm_saison as s ON s.id = a.sid" . " WHERE s.archiv = 0 AND a.liga = " . $liga . " ORDER BY a.nr DESC LIMIT 1 ";
$db->setQuery($sql);
$liga_date = $db->loadObjectList();
$end_date = JHTML::_('date', $liga_date[0]->datum, JText::_('Y-m-d'));
// Vollständigkeit prüfen für Ligen und Mannschaftsturniere
// 1. Erwartete Anzahl von Einzelergebnissen auf Basis Anzahl spielfreie Mannschaften
$sql = "SELECT COUNT(tln_nr) AS count FROM #__clm_mannschaften " . " WHERE liga = " . $liga_name[0]->id . " AND sid = " . $sid . " AND man_nr = 0";
$db->setQuery($sql);
$spielfrei = $db->loadObject();
if (isset($spielfrei)) {
$count = $spielfrei->count;
} else {
$count = 0;
}
if ($liga_name[0]->runden_modus == 1 or $liga_name[0]->runden_modus == 2) {
$counter = intval(($liga_name[0]->teil - $count) / 2) * $liga_name[0]->stamm;
} elseif ($liga_name[0]->runden_modus == 3) {
$counter = intval(($liga_name[0]->teil - $count) / 2) * $liga_name[0]->stamm;
} else {
$counter = 0;
}
// 2. Einzelergebnisse pro Durchgang/Runde
for ($dg = 1; $dg <= $liga_name[0]->durchgang; $dg++) {
for ($rnd = 1; $rnd <= $liga_name[0]->runden; $rnd++) {
$sql = " SELECT ee.runde, ee.dg, COUNT(*) AS cnt_runde FROM `#__clm_rnd_spl` as ee" . " LEFT JOIN #__clm_rnd_man as me ON me.lid = ee.lid AND me.runde = ee.runde AND me.dg = ee.dg AND me.tln_nr = ee.tln_nr" . " LEFT JOIN #__clm_mannschaften as m ON m.liga = me.lid AND m.tln_nr = me.gegner" . " WHERE ee.sid = " . $sid . " AND ee.lid = " . $liga_name[0]->id . " AND ee.dg = " . $dg . " AND ee.runde = " . $rnd . " AND ee.heim = 1 " . " AND m.man_nr > 0 " . " GROUP BY ee.dg, ee.runde " . " ORDER BY ee.dg ASC, ee.runde ASC ";
$db->setQuery($sql);
$rnd_proof = $db->loadObjectList();
if (isset($rnd_proof[0])) {
$rnd_count = $rnd_proof[0]->cnt_runde;
} else {
$rnd_count = 0;
}
$fehler = 0;
if ($rnd_count < $counter and $rnd_count == 0) {
$app->enqueueMessage(JText::_('DB_WTEXT0') . JText::_('DB_ROUND') . $rnd . JText::_('DB_DG') . $dg, 'warning');
$fehler = 1;
} elseif ($rnd_count < $counter) {
$app->enqueueMessage(JText::_('DB_WTEXT1') . JText::_('DB_ROUND') . $rnd . JText::_('DB_DG') . $dg, 'warning');
$fehler = 1;
}
}
}
}
// Grunddaten für Einzelturniere laden
if ($et != null) {
$liga = $et;
$sql = " SELECT a.* FROM #__clm_turniere as a" . " LEFT JOIN #__clm_saison as s ON s.id = a.sid" . " WHERE s.archiv = 0 AND a.id = " . $liga;
$db->setQuery($sql);
$liga_name = $db->loadObjectList();
$end_date = JHTML::_('date', $liga_name[0]->dateEnd, JText::_('Y-m-d'));
$anzahl_runden = $liga_name[0]->runden * $liga_name[0]->dg;
}
// Unterscheidung Einzel- und Mannschaftsturnier mit verschiedenen Ausgabemodi
if ($et != null) {
$format = 2;
// Nur XML für Einzelturniere
$typ = $liga_name[0]->typ;
if ($typ == "1") {
$turnier_typ = 'SR';
}
// SR: Einzelturnier; jeder gegen jeden
if ($typ == "2") {
$turnier_typ = 'SR';
}
if ($typ == "3") {
$turnier_typ = 'SW';
}
// SW: Einzelturnier; Schweizer System
if ($typ == "4") {
$turnier_typ = 'SC';
}
if ($typ == "5") {
$turnier_typ = 'SC';
}
// SC: Einzelturnier; K.O. System (Pokal)
}
if ($liga != null) {
if ($mt != null) {
$format = 2;
}
// Nur XML für Mannschaftsturniere. KEINE LIGA !
$typ = $liga_name[0]->runden_modus;
if ($typ == "1") {
$turnier_typ = 'TR';
}
// TR: Mannschaftsturnier; jeder gegen jeden
if ($typ == "2") {
$turnier_typ = 'TR';
}
if ($typ == "3") {
$turnier_typ = 'TW';
}
// TW: Mannschaftsturnier: Schweizer System
if ($typ == "4") {
$turnier_typ = 'TC';
}
if ($typ == "5") {
$turnier_typ = 'TC';
}
// TC: Mannschaftsturnier: K.O.-System (Pokal)
}
////////////////
// DSB Format //
////////////////
if ($format == "1") {
$fill[0] = "";
$fill[1] = " ";
$fill[2] = " ";
$fill[3] = " ";
$fill[4] = " ";
$fill[5] = " ";
$fill[6] = " ";
$fill[7] = " ";
$fill[8] = " ";
$fill[9] = " ";
$fill[10] = " ";
$fill[11] = " ";
$fill[12] = " ";
$fill[13] = " ";
$fill[14] = " ";
$fill[15] = " ";
$fill[16] = " ";
$fill[17] = " ";
$fill[18] = " ";
$fill[19] = " ";
$fill[20] = " ";
$fill[21] = " ";
$fill[22] = " ";
$fill[23] = " ";
$fill[24] = " ";
$fill[25] = " ";
$fill[26] = " ";
$fill[27] = " ";
$fill[28] = " ";
$fill[29] = " ";
$fill[30] = " ";
$fill[31] = " ";
$fill[32] = " ";
$sql = " SELECT a.*,v.Vereinname,s.* FROM `#__clm_rnd_spl` as a " . " LEFT JOIN #__clm_dwz_spieler as s ON s.sid = a.sid AND s.ZPS = a.zps AND s.Mgl_Nr = a.spieler " . " LEFT JOIN #__clm_dwz_vereine as v ON v.sid = a.sid AND v.ZPS = a.zps " . " WHERE a.sid = " . $sid . " AND a.lid = " . $liga_name[0]->id . " GROUP BY a.zps, a.spieler " . " ORDER BY a.zps ASC , a.brett ASC, spieler ASC ";
$db->setQuery($sql);
$spieler = $db->loadObjectList();
if (count($spieler) == 0) {
JError::raiseWarning(500, JText::_('DB_NO_PLAYER'));
$app->enqueueMessage(JText::_('DB_FILE_NOSUCCESS'), 'warning');
$app->redirect($adminLink->url);
}
// Dateikopf
$xml = utf8_decode($liga_name[0]->name) . "\n";
// Turnierbezeichnung
$xml .= "Erstellt mit CLM - ChessLeagueManager\n";
// Details zum Turnier oder Leerzeile
$xml .= "MR " . count($spieler) . " " . $liga_name[0]->runden . " " . $liga_name[0]->durchgang . "\n";
// Kennzeichen zum Turnier
$xml .= " ttt. rrr nnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv lll ffffffffff pppppppppp gggggggg eeee dddd zzzzz mmmm\n";
$cnt = 1;
$player = array();
foreach ($spieler as $spl) {
// laufende Nummer für Spieler erzeugen
$player[$spl->zps][$spl->spieler] = $cnt;
$cnt++;
$name = explode(",", $spl->Spielername);
$xml_data = $fill[4 - strlen($player[$spl->zps][$spl->spieler])] . $player[$spl->zps][$spl->spieler] . '.' . $fill[4 - strlen($player[$spl->zps][$spl->spieler])] . $player[$spl->zps][$spl->spieler] . ' ' . utf8_decode($name[0]) . ', ' . utf8_decode($name[1]) . $fill[32 - (strlen(utf8_decode($name[0])) + strlen(utf8_decode($name[1])) + 2)] . ' ' . substr(utf8_decode($spl->Vereinname) . $fill[32 - strlen(utf8_decode($spl->Vereinname))], 0, 32) . ' ' . $spl->FIDE_Land . $fill[3 - strlen($spl->FIDE_Land)];
if ($spl->FIDE_ID == "0" or $spl->FIDE_ID == "") {
$fide_id = "";
} else {
$fide_id = $spl->FIDE_ID;
}
$xml_data .= ' ' . $fill[10 - strlen($fide_id)] . $fide_id . ' ' . $fill[10 - strlen($spl->PKZ)] . $spl->PKZ . ' ' . $fill[8 - strlen($spl->Geburtsjahr)] . $spl->Geburtsjahr;
if ($spl->FIDE_Elo == "0" or $spl->FIDE_Elo == "") {
$fide_elo = "";
} else {
$fide_elo = $spl->FIDE_Elo;
}
$xml_data .= ' ' . $fill[4 - strlen($fide_elo)] . $fide_elo;
if ($spl->DWZ == "0" or $spl->DWZ == "") {
$DWZ = "";
} else {
$DWZ = $spl->DWZ;
}
$xml_data .= ' ' . $fill[4 - strlen($DWZ)] . $DWZ . ' ' . $fill[5 - strlen($spl->zps)] . $spl->zps . ' ' . $fill[4 - strlen($spl->spieler)] . $spl->spieler . ' ';
//."\n";
$spieler_data[$cnt] = $xml_data;
}
// Rundendaten holen
$sql = " SELECT * FROM `#__clm_rnd_spl` " . " WHERE sid = " . $sid . " AND lid = " . $liga_name[0]->id . " ORDER BY zps ASC, spieler ASC, dg ASC, runde ASC ";
$db->setQuery($sql);
$runden_daten = $db->loadObjectList();
// Ergebnis ID vom CLM auf Dewis umschreiben
// Die Kommentare sind CLM Ergebnisse im Klartext !
$erg[0] = "0";
// 0 - 1
$erg[1] = "1";
// 1 - 0
$erg[2] = "R";
// 0.5 - 0.5
$erg[3] = "0";
// 0-0
$erg[4] = "-";
// -/+
$erg[5] = "+";
// +/-
$erg[6] = ":";
// -/-
$erg[7] = ":";
// ---
$erg[8] = ":";
// spielfrei
// NEUE ErgebnisID's
$erg[9] = "0";
// 0:0,5
$erg[10] = "R";
// 0,5:0
// Umgekehrte Ergebnisse wegen Sortierung nach "Weiss"
$erg_bl[0] = "1";
// 0 - 1
$erg_bl[1] = "0";
// 1 - 0
$erg_bl[2] = "R";
// 0.5 - 0.5
$erg_bl[3] = "-";
// 0-0
$erg_bl[4] = "+";
// -/+
$erg_bl[5] = "-";
// +/-
$erg_bl[6] = ":";
// -/-
$erg_bl[7] = ":";
// ---
$erg_bl[8] = ":";
// spielfrei
// NEUE ErgebnisID's
$erg_bl[9] = "R";
// 0:0,5
$erg_bl[10] = "0";
// 0,5:0
foreach ($runden_daten as $rnd_data) {
$addy_1 = $fill[3 - strlen($player[$rnd_data->gzps][$rnd_data->gegner])] . $player[$rnd_data->gzps][$rnd_data->gegner];
if ($rnd_data->heim == "1") {
$ergebnis_1 = $erg[$rnd_data->ergebnis] . "W" . $addy_1;
if ($rnd_data->ergebnis == "4" or $rnd_data->ergebnis == "5" or $rnd_data->ergebnis == "6") {
$ergebnis_1 = $erg[$rnd_data->ergebnis] . ":" . $addy_1;
}
}
if ($rnd_data->heim == "0") {
$ergebnis_1 = $erg_bl[$rnd_data->ergebnis] . "B" . $addy_1;
if ($rnd_data->ergebnis == "4" or $rnd_data->ergebnis == "5" or $rnd_data->ergebnis == "6") {
$ergebnis_1 = $erg_bl[$rnd_data->ergebnis] . ":" . $addy_1;
}
}
$spieler_runden[$player[$rnd_data->zps][$rnd_data->spieler]][$rnd_data->dg][$rnd_data->runde] = " " . $ergebnis_1;
}
// Daten zusammensetzen
$cnt = 1;
foreach ($spieler_data as $spl_data) {
for ($zg = 0; $zg < $liga_name[0]->durchgang; $zg++) {
for ($zr = 0; $zr < $liga_name[0]->runden; $zr++) {
if (!$spieler_runden[$cnt][$zg + 1][$zr + 1]) {
$runde_temp .= " : 0";
} else {
$runde_temp .= $spieler_runden[$cnt][$zg + 1][$zr + 1];
}
}
}
$xml .= $spl_data . $runde_temp . "\n";
unset($runde_temp);
$cnt++;
}
// Zeit der ersten Runde
$sql = " SELECT a.* FROM #__clm_runden_termine as a" . " LEFT JOIN #__clm_saison as s ON s.id = a.sid" . " WHERE s.archiv = 0 AND a.liga = " . $liga . " ORDER BY a.nr ASC LIMIT 1 ";
$db->setQuery($sql);
$liga_start = $db->loadObjectList();
// Probedaten
$ort = utf8_decode("Name Ort");
$fide_land = "GER";
$datum_s = JHTML::_('date', $liga_start[0]->datum, JText::_('d.m.Y'));
$datum_e = JHTML::_('date', $liga_date[0]->datum, JText::_('d.m.Y'));
$zuege_1 = utf8_decode("90 Min 40 Züge");
$zuege_2 = utf8_decode("60 Min 20 Züge");
$zuege_3 = utf8_decode("30 Min Rest");
$tl = utf8_decode("Name Turnierleiter");
$sr = utf8_decode("Name Schiedsrichter");
// Ende Probedaten
$xml .= "###\n";
$xml .= "Name: " . utf8_decode($liga_name[0]->name) . "\n";
$xml .= "Ort: " . $ort . "\n";
$xml .= "FIDE-Land: " . $fide_land . "\n";
$xml .= "Datum(S): " . $datum_s . $fill[21 - strlen($datum_s)];
$xml .= "Datum(E): " . $datum_e . "\n";
$xml .= utf8_decode("Züge(1): ") . $zuege_1 . $fill[21 - strlen($zuege_1)];
$xml .= utf8_decode("Züge(2): ") . $zuege_2 . $fill[21 - strlen($zuege_2)];
$xml .= utf8_decode("Züge(3): ") . $zuege_3 . " \n";
$xml .= "Turnierleitung: " . $tl . "\n";
$xml .= "Schiedsrichter: " . $sr . "\n";
$xml .= "Anwender: Erstellt mit CLM - ChessLeagueManager\n";
}
/////////////////////
// ENDE DSB Format //
/////////////////////
//////////////////////
// DEWIS XML Format //
//////////////////////
if ($format == "2") {
$xml = '<?xml version="1.0" encoding="UTF-8"?>' . '<dewis xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" ' . 'xmlns:dwz="https://dwz.svw.info/services/files/xml/tournamentImport.xsd">';
// Turniersektion
$xml .= '<tournamentSection>' . '<tournament>';
// Ligadaten
$xml .= '<label>' . $liga_name[0]->name . '</label>' . '<type>' . $turnier_typ . '</type>' . '<rounds>' . $anzahl_runden . '</rounds>' . '<endDate>' . $end_date . '</endDate>' . '<notes>Erstellt mit CLM - ChessLeagueManager</notes>' . '</tournament>';
// Rundendaten ermitteln
if (!$et) {
$sql = " SELECT * FROM #__clm_runden_termine " . " WHERE sid = '{$sid}' AND liga = '" . $liga_name[0]->id . "'" . " ORDER BY nr ASC ";
} else {
$sql = " SELECT * FROM #__clm_turniere_rnd_termine " . " WHERE sid = '{$sid}' AND turnier = '" . $liga_name[0]->id . "'" . " ORDER BY nr ASC ";
}
$db->setQuery($sql);
$runden = $db->loadObjectList();
// Rundendaten
$xml .= '<appointments>';
/////////////////////////////////
// TODO
// Uhrzeit der Runden ???
/////////////////////////////////
foreach ($runden as $rdata) {
$xml .= '<appointment>' . '<round>' . $rdata->nr . '</round>' . '<matchday>' . $rdata->datum . '</matchday>' . '<matchtime>' . $rdata->startzeit . '</matchtime>' . '</appointment>';
}
$xml .= '</appointments>' . '</tournamentSection>';
// Spielerdaten
$xml .= '<playerSection>' . '<players>';
if (!$et) {
$sql = " SELECT a.*,v.Vereinname,s.PKZ,s.Geburtsjahr,s.Spielername,s.DWZ,s.FIDE_ID FROM `#__clm_rnd_spl` as a " . " LEFT JOIN #__clm_dwz_spieler as s ON s.sid = a.sid AND s.ZPS = a.zps AND s.Mgl_Nr = a.spieler " . " LEFT JOIN #__clm_dwz_vereine as v ON v.sid = a.sid AND v.ZPS = a.zps " . " WHERE a.sid = " . $sid . " AND a.lid = " . $liga_name[0]->id . " GROUP BY a.zps, a.spieler " . " ORDER BY a.zps ASC , a.brett ASC, spieler ASC ";
} else {
$sql = " SELECT t.verein as Vereinname,t.zps, t.mgl_nr as spieler, t.name as Spielername, t.birthYear as Geburtsjahr, " . " s.FIDE_ID, s.PKZ, s.DWZ " . " FROM `#__clm_turniere_rnd_spl` as a " . " LEFT JOIN #__clm_turniere_tlnr as t ON t.sid = a.sid AND t.snr = a.spieler AND t.turnier = a.turnier " . " LEFT JOIN #__clm_dwz_spieler as s ON s.sid = a.sid AND s.ZPS = t.zps AND s.Mgl_Nr = t.mgl_nr " . " WHERE a.sid = " . $sid . " AND a.turnier = " . $liga_name[0]->id . " GROUP BY t.zps, t.mgl_nr " . " ORDER BY t.snr ASC ";
}
$db->setQuery($sql);
$spieler = $db->loadObjectList();
$cnt = 1;
$player = array();
foreach ($spieler as $spl) {
if ($spl->zps != "" and $spl->spieler != "") {
// laufende Nummer für Spieler erzeugen
$player[$spl->zps][$spl->spieler] = $cnt;
$cnt++;
if (is_null($spl->Spielername)) {
$name = explode(",", ',');
} else {
$name = explode(",", $spl->Spielername);
}
$xml .= '<player>' . '<noPlayer>' . $player[$spl->zps][$spl->spieler] . '</noPlayer>' . '<id>' . $spl->PKZ . '</id>' . '<surname>' . $name[0] . '</surname>' . '<forename>' . $name[1] . '</forename>' . '<dob>' . $spl->Geburtsjahr . '</dob>' . '<vkz>' . $spl->zps . '</vkz>' . '<club>' . $spl->Vereinname . '</club>' . '<noMember>' . $spl->spieler . '</noMember>' . '<idFide>' . $spl->FIDE_ID . '</idFide>' . '<rating>' . $spl->DWZ . '</rating>' . '</player>';
}
}
$xml .= '</players>';
// Begegnungen
if (!$et) {
$sql = " SELECT * FROM `#__clm_rnd_spl` " . " WHERE sid = " . $sid . " AND lid = " . $liga_name[0]->id . " AND weiss = 1 " . " ORDER BY dg ASC, runde ASC, paar ASC, brett ASC ";
} else {
$sql = " SELECT a.*, t.mgl_nr as spieler, t.zps as zps, u.mgl_nr as gegner, u.zps as gzps FROM `#__clm_turniere_rnd_spl` as a " . " LEFT JOIN #__clm_turniere_tlnr as t ON t.sid = a.sid AND t.turnier = a.turnier AND t.snr = a.spieler " . " LEFT JOIN #__clm_turniere_tlnr as u ON u.sid = a.sid AND u.turnier = a.turnier AND u.snr = a.gegner " . " WHERE a.sid = " . $sid . " AND a.turnier = " . $liga_name[0]->id . " AND a.heim = 1 " . " ORDER BY a.dg ASC, a.runde ASC, a.paar ASC, a.brett ASC ";
}
$db->setQuery($sql);
$runden_daten = $db->loadObjectList();
//$app->enqueueMessage( 'SQL '.print_r($player).'-----'.$sql, 'warning');
// Ergebnis ID vom CLM auf Dewis umschreiben
// Die Kommentare sind CLM Ergebnisse im Klartext !
$erg[0] = "0:1";
// 0 - 1
$erg[1] = "1:0";
// 1 - 0
$erg[2] = "½:½";
// 0.5 - 0.5
$erg[3] = "0:0";
// 0-0
$erg[4] = "-:+";
// -/+
$erg[5] = "+:-";
// +/-
$erg[6] = "-:-";
// -/-
$erg[7] = "";
// ---
$erg[8] = "";
// spielfrei
// NEUE ErgebnisID's
$erg[9] = "½:0";
// ½:0
$erg[10] = "0:½";
// 0:½
$erg[11] = "0:-";
// 0:-
$erg[12] = "-:0";
// -:0
// Umgekehrte Ergebnisse wegen Sortierung nach "Weiss"
$erg_bl[0] = "1:0";
// 0 - 1
$erg_bl[1] = "0:1";
// 1 - 0
$erg_bl[2] = "½:½";
// 0.5 - 0.5
$erg_bl[3] = "0:0";
// 0-0
$erg_bl[4] = "+:-";
// -/+
$erg_bl[5] = "-:+";
// +/-
$erg_bl[6] = "-:-";
// -/-
$erg_bl[7] = "";
// ---
$erg_bl[8] = "";
// spielfrei
// NEUE ErgebnisID's
$erg_bl[9] = "0:½";
// ½:0
$erg_bl[10] = "½:0";
// 0:½
$erg_bl[11] = "-:0";
// 0:-
$erg_bl[12] = "0:-";
// -:0
$xml .= '<games>';
foreach ($runden_daten as $rnd) {
if ($rnd->dg > 1) {
$runde_temp = ($rnd->dg - 1) * $liga_name[0]->runden + $rnd->runde;
} else {
$runde_temp = $rnd->runde;
}
if ($rnd->heim == "0" and $rnd->weiss == "1") {
$erg_temp = $erg_bl[$rnd->ergebnis];
} else {
$erg_temp = $erg[$rnd->ergebnis];
}
$xml .= '<game>' . '<round>' . $runde_temp . '</round>' . '<noWhite>' . $player[$rnd->zps][$rnd->spieler] . '</noWhite>' . '<noBlack>' . $player[$rnd->gzps][$rnd->gegner] . '</noBlack>' . '<result>' . $erg_temp . '</result>' . '</game>';
}
$xml .= '</games>' . '</playerSection>';
/////////////////////////////////
// TODO
// Spielgemeinschaften !!
/////////////////////////////////
// NUR für Mannschaftsturniere
// Mannschaftssektion
if (!$et) {
$xml .= '<teamSection>' . '<teams>';
// Mannschaften
$sql = " SELECT tln_nr, name, zps, man_nr FROM `#__clm_mannschaften` " . " WHERE sid = '{$sid}' AND liga = '" . $liga_name[0]->id . "'" . " ORDER BY tln_nr ASC ";
$db->setQuery($sql);
$mannschaften = $db->loadObjectList();
foreach ($mannschaften as $man) {
$xml .= '<team>' . '<lot>' . $man->tln_nr . '</lot>' . '<teamName>' . $man->name . '</teamName>' . '<lineup>';
// Mannschaftsaufstellung einer Mannschaft ermitteln
$sql = " SELECT r.zps,r.spieler FROM `#__clm_mannschaften` as a" . " LEFT JOIN #__clm_rnd_spl as r ON r.sid = a.sid AND r.lid = a.liga AND r.tln_nr = a.tln_nr " . " WHERE a.sid = '{$sid}' " . " AND a.liga = '" . $liga_name[0]->id . "'" . " AND a.tln_nr = '" . $man->tln_nr . "'" . " GROUP BY r.zps, r.spieler " . " ORDER BY a.tln_nr ASC, r.brett ASC, r.spieler ";
$db->setQuery($sql);
$mannschaft = $db->loadObjectList();
foreach ($mannschaft as $lineup) {
$xml .= '<noPlayer>' . $player[$lineup->zps][$lineup->spieler] . '</noPlayer>';
}
$xml .= '</lineup>' . '</team>';
}
$xml .= '</teams>';
// Begegnungen Mannschaften
$sql = " SELECT a.runde,a.dg, a.tln_nr as home, r.tln_nr as guest, a.brettpunkte as hpoints, r.brettpunkte as gpoints " . " FROM `#__clm_rnd_man` as a" . " LEFT JOIN `#__clm_rnd_man` as r ON r.sid = a.sid AND r.lid = a.lid AND r.runde = a.runde " . " AND r.dg = a.dg AND r.tln_nr = a.gegner AND r.heim = 0 " . " WHERE a.sid = '{$sid}' " . " AND a.lid = '" . $liga_name[0]->id . "'" . " AND a.heim = 1 " . " ORDER BY a.dg ASC, a.runde ASC, a.paar ASC ";
$db->setQuery($sql);
$runden_mannschaft = $db->loadObjectList();
$xml .= '<encounter>';
foreach ($runden_mannschaft as $rnd_man) {
if ($rnd_man->dg > 1) {
$runde_temp = ($rnd_man->dg - 1) * $liga_name[0]->runden + $rnd_man->runde;
} else {
$runde_temp = $rnd_man->runde;
}
$xml .= '<play>' . '<round>' . $runde_temp . '</round>' . '<noHomeTeam>' . $rnd_man->home . '</noHomeTeam>' . '<noGuestTeam>' . $rnd_man->guest . '</noGuestTeam>' . '<pointsHome>' . $rnd_man->hpoints . '</pointsHome>' . '<pointsGuest>' . $rnd_man->gpoints . '</pointsGuest>' . '</play>';
}
$xml .= '</encounter>' . '</teamSection>';
}
$xml .= '</dewis>';
}
///////////////////////
// ENDE DEWIS Format //
///////////////////////
////////////////////////
// XLS Format England //
////////////////////////
if ($format == "3") {
/**
* Include the class for creating Excel XML docs
*/
include JPATH_ADMINISTRATOR . DIRECTORY_SEPARATOR . 'components' . DIRECTORY_SEPARATOR . 'com_clm' . DIRECTORY_SEPARATOR . 'classes' . DIRECTORY_SEPARATOR . 'ExcelWriterXML.php';
// Find Players
$sql = " SELECT a.*,v.Vereinname,s.Spielername,s.Geschlecht FROM `#__clm_rnd_spl` as a " . " LEFT JOIN #__clm_dwz_spieler as s ON s.sid = a.sid AND s.ZPS = a.zps AND s.PKZ = a.PKZ " . " LEFT JOIN #__clm_dwz_vereine as v ON v.sid = a.sid AND v.ZPS = a.zps " . " WHERE a.sid = " . $sid . " AND a.lid = " . $liga_name[0]->id . " GROUP BY a.zps, a.PKZ " . " ORDER BY a.zps ASC , a.brett ASC, a.PKZ ASC ";
$db->setQuery($sql);
$spieler = $db->loadObjectList();
//echo "<br>pl:"; var_dump($spieler); die();
// Create a new instance of the Excel Writer
$xmla = new ExcelWriterXML('CLM grading.xml');
/**
* Add some general properties to the document
*/
$xmla->docTitle('Grading - ' . $liga_name[0]->name);
$user = clm_core::$cms->getUserData();
$xmla->docAuthor($user[2]);
$xmla->docCompany('ChessLeagueManager');
$xmla->docManager('CLM Team');
/**
* Choose to show any formatting/input errors on a seperate sheet
*/
$xmla->showErrorSheet(true);
/**
* Create a new sheet with the XML document
*/
$sheet1 = $xmla->addSheet('Header');
$sheet1->columnWidth(1, '130');
$sheet1->columnWidth(2, '130');
$sheet1->columnWidth(3, '440');
$sheet1->writeString(1, 1, 'Event Code');
$sheet1->writeString(1, 2, '');
$sheet1->writeString(1, 3, 'Max 10 characters. Your two-letter Grader Code followed by 1 - 8 characters of your choice');
$sheet1->writeString(2, 1, 'Submission Number');
$sheet1->writeString(2, 2, '1');
$sheet1->writeString(2, 3, 'Use 1 for first submission and increase with each submission');
$sheet1->writeString(3, 1, 'Event Name');
$sheet1->writeString(3, 2, $liga_name[0]->name);
$sheet1->writeString(3, 3, 'Max 60 characters. Make the nature and location of the event clear.');
$sheet1->writeString(4, 1, 'Event Date');
$sheet1->writeString(4, 2, '');
$sheet1->writeString(4, 3, 'Start Date of Event');
$sheet1->writeString(5, 1, 'Final Results Date');
$sheet1->writeString(5, 2, '');
$sheet1->writeString(5, 3, "Last Date of Event - same as preceding for one-day events");
$sheet1->writeString(6, 1, "Grader Name");
$sheet1->writeString(6, 2, '');
$sheet1->writeString(6, 3, "Grader's Name");
$sheet1->writeString(7, 1, "Grader Address");
$sheet1->writeString(7, 2, '');
$sheet1->writeString(7, 3, "Grader's email address ONLY. Will be used to return feedback.");
$sheet1->writeString(8, 1, "Treasurer Name");
$sheet1->writeString(8, 2, '');
$sheet1->writeString(8, 3, "Treasurer's Name");
$sheet1->writeString(9, 1, "Treasurer Address");
$sheet1->writeString(9, 2, '');
$sheet1->writeString(9, 3, "Treasurer's postal address - on one line with commas");
$sheet1->writeString(10, 1, "Moves in first session");
$sheet1->writeString(10, 2, '');
$sheet1->writeString(10, 3, "e.g. 36 or 40 (leave blank for rapidplay)");
$sheet1->writeString(11, 1, "Minutes for first session");
$sheet1->writeString(11, 2, '');
$sheet1->writeString(11, 3, "e.g. 90 or 120 (leave blank for rapidplay)");
$sheet1->writeString(12, 1, "Moves in second session");
$sheet1->writeString(12, 2, '');
$sheet1->writeString(12, 3, "e.g. 20 or leave blank if immediate quickplay finish");
$sheet1->writeString(13, 1, "Minutes in second session");
$sheet1->writeString(13, 2, '');
$sheet1->writeString(13, 3, "e.g. 60 or leave blank if preceding cell is blank");
$sheet1->writeString(14, 1, "Minutes in final session");
$sheet1->writeString(14, 2, '');
$sheet1->writeString(14, 3, "e.g. 15 or leave blank if no quickplay finish");
$sheet1->writeString(15, 1, "Minutes for game");
$sheet1->writeString(15, 2, '');
$sheet1->writeString(15, 3, "Minutes for rapidplay or all-in-one-session standardplay");
$sheet1->writeString(16, 1, "Seconds added per move");
$sheet1->writeString(16, 2, '');
$sheet1->writeString(16, 3, "Seconds per move added in Fischer mode else blank");
$sheet1->writeString(17, 1, "Grand Prix");
$sheet1->writeString(17, 2, '');
$sheet1->writeString(17, 3, "Enter Y if results from event are to be included in ECF Grand Prix");
$sheet1->writeString(18, 1, "FIDE rated");
$sheet1->writeString(18, 2, '');
$sheet1->writeString(18, 3, "Enter Y if event is to be FIDE rated");
// Second sheet - player_list
$sheet2 = $xmla->addSheet('Player_List');
$sheet2->columnWidth(1, '40');
$sheet2->writeString(1, 1, 'PIN');
$sheet2->columnWidth(2, '50');
$sheet2->writeString(1, 2, 'BCFCode');
$sheet2->columnWidth(3, '120');
$sheet2->writeString(1, 3, 'Name');
$sheet2->columnWidth(4, '30');
$sheet2->writeString(1, 4, 'Gender');
$sheet2->columnWidth(5, '50');
$sheet2->writeString(1, 5, 'DOB');
$sheet2->columnWidth(6, '30');
$sheet2->writeString(1, 6, 'ClubCode');
$sheet2->columnWidth(7, '100');
$sheet2->writeString(1, 7, 'ClubName');
$sheet2->columnWidth(8, '60');
$sheet2->writeString(1, 8, 'BCFMemNo');
$sheet2->columnWidth(9, '60');
$sheet2->writeString(1, 9, 'FIDEName');
$sheet2->columnWidth(10, '200');
$sheet2->writeString(1, 10, 'Comment');
$sheet2->columnWidth(11, '20');
$sheet2->writeString(1, 11, 'Title');
$sheet2->columnWidth(12, '30');
$sheet2->writeString(1, 12, 'Initials');
$sheet2->columnWidth(13, '60');
$sheet2->writeString(1, 13, 'Forename');
$sheet2->columnWidth(14, '60');
$sheet2->writeString(1, 14, 'Surname');
$ccode = 0;
$crow = 1;
$pl_array = array();
foreach ($spieler as $player) {
if ($ccode != $player->zps) {
$ccode = $player->zps;
$ccount = 0;
}
$ccount++;
$scount = sprintf("%'.02d\n", $ccount);
$crow++;
$sheet2->writeString($crow, 1, $player->zps . $scount);
$sheet2->writeString($crow, 2, $player->PKZ);
$sheet2->writeString($crow, 3, $player->Spielername);
$sheet2->writeString($crow, 4, $player->Geschlecht);
$sheet2->writeString($crow, 6, $player->zps);
$sheet2->writeString($crow, 7, $player->Vereinname);
$pl_array[$player->zps . $player->PKZ] = new stdClass();
$pl_array[$player->zps . $player->PKZ]->Spielername = $player->Spielername;
$pl_array[$player->zps . $player->PKZ]->Vereinname = $player->Vereinname;
$pl_array[$player->zps . $player->PKZ]->PIN = $player->zps . $scount;
}
// Third sheet - results_list
$sheet3 = $xmla->addSheet('Results_List');
$sheet3->columnWidth(1, '50');
$sheet3->writeString(1, 1, 'PIN1');
$sheet3->columnWidth(2, '50');
$sheet3->writeString(1, 2, 'PIN2');
$sheet3->columnWidth(3, '40');
$sheet3->writeString(1, 3, 'Result');
$sheet3->columnWidth(4, '40');
$sheet3->writeString(1, 4, 'Colour1');
$sheet3->columnWidth(5, '60');
$sheet3->writeString(1, 5, 'Date');
$sheet3->columnWidth(6, '30');
$sheet3->writeString(1, 6, 'Board');
$sheet3->columnWidth(7, '40');
$sheet3->writeString(1, 7, 'Round');
$sheet3->columnWidth(8, '200');
$sheet3->writeString(1, 8, 'Comment');
// Find games
$sql = " SELECT a.*, hm.name as hmname, gm.name as gmname, m.pdate FROM `#__clm_rnd_spl` as a " . " LEFT JOIN #__clm_rnd_man as m ON m.sid = a.sid AND m.lid = a.lid AND m.dg = a.dg AND m.runde = a.runde AND m.tln_nr = a.tln_nr " . " LEFT JOIN #__clm_mannschaften as hm ON hm.sid = a.sid AND hm.liga = a.lid AND hm.tln_nr = a.tln_nr " . " LEFT JOIN #__clm_mannschaften as gm ON gm.sid = a.sid AND gm.liga = a.lid AND gm.tln_nr = m.gegner " . " WHERE a.sid = " . $sid . " AND a.lid = " . $liga_name[0]->id . " AND a.ergebnis < 3 " . " AND a.heim = 1 " . " ORDER BY a.dg ASC, a.runde ASC, a.paar ASC, a.brett ASC ";
$db->setQuery($sql);
$partien = $db->loadObjectList();
//echo "<br>par:"; var_dump($partien); die();
$crow = 1;
foreach ($partien as $games) {
$crow++;
if (isset($pl_array[$games->zps . $games->PKZ])) {
$sheet3->writeString($crow, 1, $pl_array[$games->zps . $games->PKZ]->PIN);
}
if (isset($pl_array[$games->gzps . $games->gPKZ])) {
$sheet3->writeString($crow, 2, $pl_array[$games->gzps . $games->gPKZ]->PIN);
}
if ($games->ergebnis == 0) {
$sheet3->writeString($crow, 3, '01');
} elseif ($games->ergebnis == 1) {
$sheet3->writeString($crow, 3, '10');
} elseif ($games->ergebnis == 2) {
$sheet3->writeString($crow, 3, '55');
}
if ($games->weiss == 0) {
$sheet3->writeString($crow, 4, 'b');
} elseif ($games->weiss == 1) {
$sheet3->writeString($crow, 4, 'w');
}
$sheet3->writeString($crow, 5, clm_core::$cms->showDate($games->pdate, "d M Y"));
$sheet3->writeString($crow, 6, $games->brett);
$sheet3->writeString($crow, 7, $games->runde);
$sheet3->writeString($crow, 8, $games->hmname . " - " . $games->gmname);
}
/**
* Send the headers, then output the data
*/
$xmla->sendHeaders();
$xml = $xmla->writeData();
}
// Slashes und Spaces aus Namen filtern und Namen mit Pfad zusammensetzen
$dat_name = str_replace(' ', '_', $liga_name[0]->name);
$dat_name = str_replace('/', '_', $dat_name);
$dat_name = clm_core::$load->sub_umlaute($dat_name);
$file = utf8_decode($dat_name) . '__' . $datum;
$path = JPath::clean(JPATH_ADMINISTRATOR . DS . 'components' . DS . $option . DS . 'dewis');
if ($format == "1") {
$datei_endung = "txt";
}
if ($format == "2") {
$datei_endung = "xml";
}
if ($format == "3") {
$datei_endung = "xml";
}
$write = $path . DS . $file . '.' . $datei_endung;
// Datei schreiben ggf. Fehlermeldung absetzen
jimport('joomla.filesystem.file');
if (!JFile::write($write, $xml)) {
JError::raiseWarning(500, JText::_('DB_FEHLER_SCHREIB'));
}
$app->enqueueMessage(JText::_('DB_FILE_SUCCESS') . utf8_encode($file), 'warning');
$app->redirect($adminLink->url);
}
<?php
/**
* Example page for how to use the Excel Writer XML
* @package ExcelWriterXML
* @subpackage examples
* @filesource
*/
/**
* Include the required Class file
*/
include ('ExcelWriterXML.php');
/**
* @source
*/
$xml = new ExcelWriterXML ();
$xml->docAuthor ( 'Robert F Greer' );
$format = $xml->addStyle ( 'StyleHeader' );
$format->fontBold ();
$format->alignRotate ( 45 );
$sheet = $xml->addSheet ( 'My Sheet' );
$sheet->writeString ( 1, 1, 'Header', 'StyleHeader' );
$sheet->writeString ( 2, 1, 'Test String', 'StyleHeader' );
$xml->sendHeaders ();
$xml->writeData ();
?>
/**
* Detailed example for creating Excel XML docs
* @package ExcelWriterXML
* @subpackage examples
* @filesource
*/
/**
* Include the class for creating Excel XML docs
*/
include ('ExcelWriterXML.php');
/**
* Create a new instance of the Excel Writer
*/
$xml = new ExcelWriterXML ( 'my file.xsl' );
/**
* Add some general properties to the document
*/
$xml->docTitle ( 'My Demo Doc' );
$xml->docAuthor ( 'Robert F Greer' );
$xml->docCompany ( 'Greers.Org' );
$xml->docManager ( 'Wife' );
/**
* Choose to show any formatting/input errors on a seperate sheet
*/
$xml->showErrorSheet ( true );
/**
$arr['rules'][$i]['destination_id'] = $arr_offers[$str]['id'];
break;
default:
$arr['rules'][$i]['destination_multi'] = declination($destinations_count, array(' оффер', ' оффера', ' офферов'));
break;
}
$arr['rules'][$i]['default_destination_id'] = $default_destination_id;
$arr['rules'][$i]['other_users'] = count($cur['items']) > 1 ? 'Остальные посетители' : 'Все посетители';
$i++;
}
echo json_encode($arr);
exit;
break;
case 'excel_export':
include _TRACK_SHOW_COMMON_PATH . '/lib/excel_writer/ExcelWriterXML.php';
$xml = new ExcelWriterXML('report.xls');
$sheet = $xml->addSheet('Report');
// Get data for report
$arr_data = get_excel_report($_REQUEST['date']);
$iRow = 1;
foreach ($arr_data as $cur) {
$iCol = 1;
foreach ($cur as $val) {
$sheet->writeString($iRow, $iCol, $val);
$iCol++;
}
$iRow++;
}
$xml->sendHeaders();
$xml->writeData();
exit;
public final function exportdata($sql = ' select \'测试成功\' ', $titles = array('测试导出'), $sheetname = '导出')
{
require BASE_PATH . '/include/ExcelWriterXML.php';
$xml = new ExcelWriterXML($sheetname . '.xls');
$xml->docAuthor('hcsoft');
$format = $xml->addStyle('StyleHeader');
$format->fontBold();
$format->alignRotate(45);
$sheet = $xml->addSheet($sheetname);
foreach ($titles as $i => $v) {
$sheet->writeString(1, $i + 1, $v);
}
$conn = (require BASE_DATA_PATH . '/../core/framework/db/mssqlpdo.php');
//查询sql
try {
$stmt = $conn->query($sql);
$rowindex = 2;
while ($row = $stmt->fetch(PDO::FETCH_NUM)) {
foreach ($row as $i => $v) {
$sheet->writeString($rowindex, $i + 1, strval($v));
}
$rowindex = $rowindex + 1;
}
} catch (Exception $e) {
$sheet->writeString(2, 1, '导出异常!请与系统管理员联系!异常信息:' + $e->getMessage());
}
$xml->sendHeaders();
$xml->writeData();
die;
}
<?php
/**
* Example using built in MYSQL DB functions
* @package ExcelWriterXML
* @subpackage examples
* @filesource
*/
include ('ExcelWriterXML.php');
$xml = new ExcelWriterXML ( 'my file.xml' );
$xml->showErrorSheet ( true );
/**
* *************** ADD multiple tables all at once **********
*/
$tables = array (
'test',
'desks'
);
$xml->mysqlTableDump ( 'surveys.web.alcatel-lucent.com', 'ghdweb', 'ghdweb', 'ghdweb_survey', $tables );
/**
* *************** ADD one table at a time **********
*/
$xml->mysqlTableDump ( 'surveys.web.alcatel-lucent.com', 'ghdweb', 'ghdweb', 'ghdweb_survey', 'test', 'test2' );
$xml->mysqlTableDump ( 'surveys.web.alcatel-lucent.com', 'ghdweb', 'ghdweb', 'ghdweb_survey', 'desks', 'desk2' );
/**
* *************** ADD a sheet, execute a query against that sheet **********
*/
$qSheet = $xml->addSheet ( 'My Query' );
$query = '
