function SWT(&$rows, &$lists, &$pageNav)
{
// Konfigurationsparameter auslesen
$config =& JComponentHelper::getParams('com_clm');
$upload = $config->get('upload_swt', 0);
$execute = $config->get('execute_swt', 0);
$lv = $config->get('lv', 705);
CLMViewSWT::setSWTToolbar($execute, $upload);
?>
<div>
<div class="col width-50">
<fieldset>
<legend><?php
echo JText::_('SWT_TIP_LINE40');
?>
</legend>
<font color="#ff0000"><b><?php
echo JText::_('SWT_TIP_LINE41');
?>
</b></font><br>
<?php
echo JText::_('SWT_TIP_LINE42');
?>
<br><?php
echo JText::_('SWT_TIP_LINE43');
?>
</fieldset>
<form action="index.php" name="adminForm" method="post" enctype="multipart/form-data" >
<fieldset>
<legend><?php
echo JText::_('SWT_TIP_LINE50');
?>
</legend>
<b><?php
echo JText::_('SWT_TIP_LINE51') . ' ';
?>
<?php
if ($upload == 1) {
?>
<font color="#00ff00"><?php
echo JText::_('SWT_ACTIVE');
} else {
?>
<font color="#ff0000"><?php
echo JText::_('SWT_DEACTIVE');
}
?>
</font>
<?php
echo " , " . JText::_('SWT_TIP_LINE52') . ' ';
if ($execute == 1) {
?>
<font color="#00ff00"><?php
echo JText::_('SWT_ACTIVE');
} else {
?>
<font color="#ff0000"><?php
echo JText::_('SWT_DEACTIVE');
}
?>
</font></b>
</fieldset>
<fieldset>
<legend><?php
echo JText::_('SWT_TIPS');
?>
</legend>
<b><font color="#ff0000"><?php
echo JText::_('SWT_TIP_LINE40');
?>
</font><?php
echo JText::_('SWT_TIP_LINE61');
?>
</b>
<br><?php
echo JText::_('SWT_TIP_LINE62');
?>
<br><?php
echo JText::_('SWT_TIP_LINE63');
?>
<br><b><?php
echo JText::_('SWT_TIP_LINE64');
?>
</b>
<br><br><b><?php
echo JText::_('SWT_TIP_LINE65');
?>
</b>
<?php
echo JText::_('SWT_TIP_LINE66');
?>
<br><br><?php
echo JText::_('SWT_TIP_LINE67') . ' ';
?>
<b><?php
echo JText::_('SWT_TIP_LINE68');
?>
</b> <?php
echo JText::_('SWT_TIP_LINE69');
?>
<br>
<br><b><?php
echo JText::_('SWT_TIP_LINE70');
?>
</b>
<br><?php
echo JText::_('SWT_TIP_LINE71');
?>
<br><?php
echo JText::_('SWT_TIP_LINE72');
?>
<br><?php
echo JText::_('SWT_TIP_LINE73');
?>
<br><?php
echo JText::_('SWT_TIP_LINE74');
?>
</fieldset>
</div>
<div class="col width-50">
<?php
if ($execute == 1) {
?>
<?php
$liga_import = CLMControllerSWT::liga();
?>
<?php
$datei = CLMControllerSWT::files();
?>
<fieldset>
<legend><?php
echo JText::_('SWT_TIP_LINE80');
?>
</legend>
<table>
<tr>
<td class="key" nowrap="nowrap">
<select size="1" name="sql_datei">
<option value="0"><?php
echo JText::_('SWT_TIP_LINE81');
?>
</option>
<?php
for ($x = 0; $x < count($datei); $x++) {
?>
<option value="<?php
echo $datei[$x];
?>
"><?php
echo $datei[$x];
?>
</option>
<?php
}
?>
</select>
</td>
<td> <b><?php
echo JText::_('SWT_TIP_LINE82');
?>
</b></td>
</tr>
<!-- <tr>
<td class="key" nowrap="nowrap">
<select size="1" name="liga_import">
<option value="0">-- Liga wählen --</option>
<option value="new">-- Keine, neu erstellen --</option>
<?php
/*for ($x=0; $x < count($liga_import); $x++) { */
?>
<option value="<?php
/*echo $liga_import[$x]->id; */
?>
"><?php
/*echo $liga_import[$x]->name; */
?>
</option>
<?php
/*} */
?>
</select><br><br><br>
</td>
<td><b>Wählen Sie die Liga aus in die importiert werden soll</b>
<br>- "Keine Auswahl" erstellt eine neue Liga
<br>- Bei Auswahl werden alle Daten gelöscht wenn Sie im nächsten Menue kein Update auswählen !
<br><br>
</td>
</tr>
<tr>
<td class="key" nowrap="nowrap">
<select size="1" name="liga_import">
<option value="0">-- Update wählen --</option>
<?php
/*for ($x=0; $x < count($liga_export); $x++) { */
?>
<option value="<?php
/*echo $liga_export[$x]->id; */
?>
"><?php
/*echo $liga_export[$x]->name; */
?>
</option>
<?php
/*} */
?>
</select><br><br><br>
</td>
<td><b>Hier können Sie ein Update aus bereits importierten Daten wählen</b>
<br>- Es werden die Grunddaten der Liga sowie Mannschaftsnamen, ZPS etc. übernommen.
<br>- Die Rundendaten werden komplett neu eingelesen und alte überschrieben.
<br>- Dieser Import beginnt mit dem 2. Importschritt.
</td>
</tr>
<tr>
<td>
<input type="checkbox" id="override" name="override" value="1" />Sicherheitswarnungen ignorieren</td>
<td>Auf eigene Gefahr : Ermöglicht das ignorieren von Sicherheitswarnungen beim Import ! (Wenn z.B. die Importdatei manipuliert / geändert wurde)</td>
</tr>
-->
</table>
</fieldset>
<?php
}
?>
<input type="hidden" name="section" value="swt" />
<input type="hidden" name="option" value="com_clm" />
<?php
echo JHTML::_('form.token');
?>
<?php
$data = CLMControllerSWT::delete_data();
?>
<fieldset>
<legend><?php
echo JText::_('SWT_TIP_LINE90');
?>
</legend>
<table>
<tr>
<td class="key" nowrap="nowrap">
<select size="1" name="swt_delete">
<option value="0"><?php
echo JText::_('SWT_TIP_LINE91');
?>
</option>
<?php
for ($x = 0; $x < count($data); $x++) {
?>
<option value="<?php
echo $data[$x]->swt_id;
?>
"><?php
echo $data[$x]->Liga;
?>
</option>
<?php
}
?>
</select>
</td>
<td> <b><?php
echo JText::_('SWT_TIP_LINE92');
?>
</b>
<br> <?php
echo JText::_('SWT_TIP_LINE93');
?>
<br> <?php
echo JText::_('SWT_TIP_LINE94');
?>
</td>
</tr>
</table>
</fieldset>
<?php
if ($upload == 1) {
?>
<fieldset>
<legend><?php
echo JText::_('SWT_TIP_LINE100');
?>
</legend>
<input type="file" name="datei" />
</fieldset>
<?php
}
?>
<form action="<?php
echo JURI::base();
?>
index.php?option=com_clm&task=sql_db" id="execute" method="post" name="sql_execute">
<fieldset>
<legend><?php
echo JText::_('SWT_TIP_LINE101');
?>
</legend>
<table >
<?php
for ($x = 0; $x < count($datei); $x++) {
?>
<tr><td><?php
echo $datei[$x];
?>
</td></tr>
<?php
}
?>
</table>
</fieldset>
</div>
</div>
<input type="hidden" name="section" value="swt" />
<input type="hidden" name="option" value="com_clm" />
<input type="hidden" name="task" value="" />
<?php
echo JHTML::_('form.token');
?>
<div class="clr"></div>
</form>
<?php
}
function import_3()
{
global $mainframe;
// Check for request forgeries
JRequest::checkToken() or die('Invalid Token');
$option = JRequest::getCmd('option');
$section = JRequest::getVar('section');
$task = JRequest::getVar('task');
$db =& JFactory::getDBO();
$user =& JFactory::getUser();
$step = JRequest::getVar('step');
$liga_swt = JRequest::getInt('liga_swt');
$fileName = JRequest::getVar('fileName');
// aktuelle Saison ermitteln
$sql = 'SELECT id FROM #__clm_saison WHERE archiv = 0 and published = 1';
$db->setQuery($sql);
$sid = $db->loadResult();
/// SWT Datei in Array einlesen
$swt[] = '';
if ($fh = fopen($fileName, 'rb')) {
while (!feof($fh)) {
$swt[] = ord(fgets($fh, 2));
}
fclose($fh);
}
// Array zum Erkennen von SG und ihren ZPS schreiben !
$sg_zps = array();
$offset = 13384 + $swt[8] * $swt[2] * 19 + $swt[2] * $swt[603] * 19;
for ($x = 0; $x < $swt[8]; $x++) {
$zps = CLMControllerSWT::give_number($swt, 1 + 153 + $offset, 7);
if ($zps != "" and $sg_zps[1][$swt[$offset + 202]] == "") {
$sg_zps[1][$swt[$offset + 202]] = $zps;
}
if ($zps != "" and $zps != $sg_zps[1][$swt[$offset + 202]] and $sg_zps[2][$swt[$offset + 202]] == "") {
$sg_zps[2][$swt[$offset + 202]] = $zps;
}
$offset += 655;
}
// Datenarray aller Daten schreiben
$info = 0;
$cnt = 1;
for ($y = 0; $y < $swt[603]; $y++) {
$offset = 13384 + $swt[8] * $swt[2] * 19 + $swt[2] * $swt[603] * 19 + $y * 655;
for ($x = 0; $x < $swt[8]; $x++) {
$zps = CLMControllerSWT::give_number($swt, 1 + 153 + $offset, 7);
$info = "";
if ($zps == "") {
$zps = $zps_db[$y]->zps;
$info = 1;
}
//echo "_!_".$zps_db[($swt[$offset+218])-1]->zps;
if ($y + 1 == $swt[$offset + 202]) {
$spl_name = CLMControllerSWT::give_name($swt, 1 + $offset, $offset + 65);
$spl_ges = CLMControllerSWT::give_name($swt, 1 + 184 + $offset, 184 + $offset + 1);
$mgl = CLMControllerSWT::give_number($swt, 1 + 159 + $offset, 4);
$tln_man = $swt[$offset + 218];
$man_nr = $swt[$offset + 202];
// Array : Counter => Tln_nr, Man_nr, Name, Geschl., ZPS, Mgl_Nr, Info
if ($y + 1 == $swt[$offset + 202]) {
$data[$cnt] = array($tln_man, $man_nr, $spl_name, $spl_ges, $zps, $mgl, $info);
$cnt++;
}
}
$offset += 655;
}
}
//JError::raiseNotice( 6000, JText::_( $liga_swt.'---5---'.print_r($data) ));
//////////////
// Wenn von Schritt 2 kommend dann speichern !
//////////////
if ($step == "import_2") {
$sql = " DELETE FROM #__clm_swt_spl_tmp WHERE liga_swt = {$liga_swt} ";
$db->setQuery($sql);
$db->query();
$offset = 13384 + $swt[8] * $swt[2] * 19 + $swt[2] * $swt[603] * 19;
for ($x = 1; $x < 1 + $swt[8]; $x++) {
$mgl = CLMControllerSWT::give_number($swt, 1 + 159 + $offset, 4);
$tln_man = $swt[$offset + 218];
$man_nr = $swt[$offset + 202];
$offset += 655;
$name_man = JRequest::getVar('manuell_name' . $x);
$zps_spl = JRequest::getVar('zps' . $x);
$sql = " INSERT INTO #__clm_swt_spl_tmp ( liga_swt, mnr, mgl_nr, clm_zps, Nr, Name) " . "VALUES ( '{$liga_swt}','{$man_nr}', '{$mgl}', '{$zps_spl}', '{$tln_man}', '{$name_man}')";
$db->setQuery($sql);
$db->query();
}
}
//////////////
//////////////
// Zuordnung von fehlenden Daten mittels exakter ZPS/Mgl oder Spielernamen
// Werte in swt_spl_tmp �bertragen und einzigartig machen
$sql = " DELETE FROM #__clm_swt_spl WHERE liga_swt = {$liga_swt} ";
// AND Status <> 'ZZ'";
$db->setQuery($sql);
$db->query();
// Alle Spieler einf�gen f�r die es Mgl/ZPS Daten aus der DSB DWZ DB gibt
$sql = " INSERT INTO #__clm_swt_spl ( liga_swt, mnr, mgl_nr, clm_zps, Nr, Name, ZPS ) " . " SELECT a.liga_swt, a.mnr, d.Mgl_Nr, d.ZPS, a.Nr, d.Spielername, d.ZPS " . " FROM #__clm_swt_spl_tmp as a " . " LEFT JOIN #__clm_swt_spl_nach as s ON s.Nr = a.Nr AND s.liga_swt = a.liga_swt " . " LEFT JOIN #__clm_dwz_spieler as d ON ( d.ZPS = a.clm_zps AND d.Mgl_Nr = a.mgl_nr) " . " WHERE a.liga_swt = {$liga_swt} AND d.ZPS IS NOT NULL AND s.Nr IS NULL AND d.sid = {$sid}";
$db->setQuery($sql);
$db->query();
// Spieler mit g�ltiger ZPS / MglNr l�schen !!
$sql = " DELETE FROM #__clm_swt_spl_tmp " . " WHERE liga_swt = {$liga_swt} AND Nr IN " . " ( SELECT Nr FROM #__clm_swt_spl as a " . " LEFT JOIN #__clm_dwz_spieler as d ON ( d.ZPS = a.clm_zps AND d.Mgl_Nr = a.mgl_nr) " . " WHERE a.liga_swt = {$liga_swt} AND d.ZPS IS NOT NULL AND d.sid = {$sid}) ";
$db->setQuery($sql);
$db->query();
// Alle Spieler einf�gen f�r die es Namens�bereinstimmungen aus der DSB DWZ DB gibt
$sql = " INSERT INTO #__clm_swt_spl ( liga_swt, mnr, mgl_nr, clm_zps, Nr, Name, ZPS ) " . " SELECT a.liga_swt, a.mnr, d.Mgl_Nr, d.ZPS, a.Nr, d.Spielername, d.ZPS " . " FROM #__clm_swt_spl_tmp as a " . " LEFT JOIN #__clm_swt_spl_nach as s ON s.Nr = a.Nr AND s.liga_swt = a.liga_swt " . " LEFT JOIN #__clm_dwz_spieler as d ON ( d.ZPS = a.clm_zps AND d.Mgl_Nr = a.mgl_nr) OR (a.Name =d.Spielername AND d.ZPS = a.clm_zps) " . " WHERE a.liga_swt = {$liga_swt} AND d.ZPS IS NOT NULL AND s.Nr IS NULL AND d.sid = {$sid}";
$db->setQuery($sql);
$db->query();
// Spieler l�schen f�r die es Namens�bereinstimmungen aus der DSB DWZ DB gibt !!
$sql = " DELETE FROM #__clm_swt_spl_tmp " . " WHERE liga_swt = {$liga_swt} AND Nr IN " . " ( SELECT Nr FROM #__clm_swt_spl as a " . " LEFT JOIN #__clm_dwz_spieler as d ON ( d.ZPS = a.clm_zps AND d.Mgl_Nr = a.mgl_nr) OR (a.Name =d.Spielername AND d.ZPS = a.clm_zps) " . " WHERE a.liga_swt = {$liga_swt} AND d.ZPS IS NOT NULL AND d.sid = {$sid}) ";
$db->setQuery($sql);
$db->query();
// wirkt erst ab dem 2.DG !!!
// Nachgemeldete Spieler einf�gen f�r die es Daten aus der DSB DWZ DB gibt
$sql = " INSERT INTO #__clm_swt_spl ( liga_swt, mnr, mgl_nr, clm_zps, Nr, Name, ZPS ) " . " SELECT a.liga_swt, a.mnr, d.Mgl_Nr, d.ZPS, a.Nr, d.Spielername, d.ZPS " . " FROM #__clm_swt_spl_tmp as a " . " LEFT JOIN #__clm_swt_spl_nach as s ON s.Nr = a.Nr AND s.liga_swt = a.liga_swt " . " LEFT JOIN #__clm_dwz_spieler as d ON d.ZPS = s.clm_zps AND d.Mgl_Nr = s.mgl_nr " . " WHERE a.liga_swt = {$liga_swt} AND d.ZPS IS NOT NULL AND s.Nr IS NOT NULL AND d.sid = {$sid}";
$db->setQuery($sql);
$db->query();
// F�r Nachmeldungen !!! UNBEDINGT !!! schon verwendete ZPS / Mgl Kombis entfernen => sonst DUPLICATE ENTRYS !!!!
// wirkt erst ab dem 2.DG !!!
$sql = " DELETE FROM #__clm_swt_spl_tmp " . " WHERE liga_swt = {$liga_swt} AND Nr IN " . " ( SELECT a.Nr FROM #__clm_swt_spl as a " . " LEFT JOIN #__clm_swt_spl_nach as s ON s.Nr = a.Nr AND s.liga_swt = a.liga_swt " . " LEFT JOIN #__clm_dwz_spieler as d ON d.ZPS = s.clm_zps AND d.Mgl_Nr = s.mgl_nr " . " WHERE a.ZPS IS NOT NULL AND d.sid = {$sid})";
$db->setQuery($sql);
$db->query();
//////////////
// Spieler die keine ZPS / MglNr oder keinen Namen haben zuordnen und manuelle Auswahl anbieten
//////////////
// Spieler ohne g�ltige ZPS / MglNr holen
$sql = " SELECT * FROM #__clm_swt_spl_tmp " . " WHERE liga_swt = {$liga_swt} ORDER BY Nr ASC ";
$db->setQuery($sql);
$zps_db = $db->loadObjectList();
if (count($zps_db) > 0) {
// Mannschaftsnamen f�r manuelle Auswahl holen wenn vorhanden
$l_name = CLMControllerSWT::give_name($swt, 377, 441);
$sql = " SELECT m.zps FROM #__clm_swt_liga AS a " . " LEFT JOIN #__clm_swt_man AS m ON m.swt_id = a.swt_id " . " WHERE Liga ='{$l_name}'" . " ORDER BY tln_nr ASC ";
$db->setQuery($sql);
$man_zps_manuell = $db->loadObjectList();
// Array f�r Nachmelde View schreiben
$z = 0;
foreach ($zps_db as $search) {
$cnt = 0;
// Eine generelle Nachmeldung anbieten
$man_zps[$search->Nr][$cnt] = array($man_zps_manuell[$search->mnr - 1]->zps, 'Nachmeldung', $search->Name);
$cnt++;
// Zuordnung per Name probieren
$teil = explode(",", $zps_db[$z]->Name);
$name = $teil[0];
$sql = " SELECT Spielername, ZPS, Mgl_Nr, Status FROM #__clm_dwz_spieler " . " WHERE Spielername LIKE '%{$name}%' " . " AND sid = {$sid} ";
$db->setQuery($sql);
$zps_clm_name = $db->loadObjectList();
$hit = 0;
foreach ($zps_clm_name as $zps_tmp) {
if ($zps_tmp->ZPS == $sg_zps[1][$search->mnr] or $zps_tmp->ZPS == $sg_zps[2][$search->mnr]) {
$man_zps[$search->Nr][$cnt] = array($zps_tmp->ZPS, $zps_tmp->Mgl_Nr, $zps_tmp->Spielername, $zps_tmp->Status);
if ($zps_tmp->Mgl_Nr == $search->mgl_nr and $zps_tmp->ZPS == $search->clm_zps) {
$hit++;
}
$cnt++;
}
}
// Zordnung per Mgl / ZPS probieren falls mit Name kein Volltreffer
if ($hit == 0 and $search->mgl_nr > 0 and $search->clm_zps > 0) {
$sql = " SELECT Spielername, ZPS, Mgl_Nr FROM #__clm_dwz_spieler " . " WHERE ZPS = " . $zps_db[$z]->clm_zps . " AND Mgl_Nr = " . $zps_db[$z]->mgl_nr . " AND sid = {$sid} ";
$db->setQuery($sql);
$zps_clm_zps = $db->loadObjectList();
if ($zps_clm_zps[0]->ZPS != "" and $zps_clm_zps[0]->Mgl_Nr != "" and ($zps_clm_zps[0]->ZPS == $sg_zps[1][$search->mnr] or $zps_clm_zps[0]->ZPS == $sg_zps[2][$search->mnr])) {
$man_zps[$search->Nr][$cnt] = array($zps_clm_zps[0]->ZPS, $zps_clm_zps[0]->Mgl_Nr, $zps_clm_zps[0]->Spielername);
}
}
$z++;
}
JError::raiseWarning(500, JText::_('SWT_GIBT') . count($zps_db) . JText::_('SWT_NO_ZUORDNEN'));
JError::raiseNotice(6000, JText::_('SWT_DATEN'));
} else {
JError::raiseNotice(6000, JText::_('SWT_SPIELER_ZUORD'));
}
//////////////
//////////////
require_once JPATH_COMPONENT . DS . 'views' . DS . 'swt.php';
CLMViewSWT::Import_3($lists, $swt, $zps_db, $sid, $data, $liga_swt, $man_zps, $fileName, $sg_zps);
}
