function Import_1(&$spieler, &$lists, &$man, &$swt, &$man_zps, &$vereine, &$sid, $fileName, &$name_manuell) { CLMViewSWT::setImport_1Toolbar($execute, $upload); JRequest::setVar('hidemainmenu', 1); ?> <form action="index.php" name="adminForm" method="post" enctype="multipart/form-data" > <?php $liga_name = CLMControllerSWT::give_name($swt, 377, 441); $veranstaltung = CLMControllerSWT::give_name($swt, 246, 310); ?> <div class="col width-30"> <div> <fieldset> <legend>Turnierdaten</legend> <table class="admintable"> <tr><th width="5%" class="key" nowrap="nowrap"><?php echo JText::_('SWT_EVENT'); ?> </th><th class="key" nowrap="nowrap"><?php echo $veranstaltung; ?> </td><tr> <tr><th width="5%" class="key" nowrap="nowrap"><?php echo JText::_('SWT_TYPE'); ?> </th><th class="key" nowrap="nowrap"><?php if ($swt[607] == 255) { echo "Mannschaftsturnier !"; } else { echo "Einzelturnier ! "; } ?> </td><tr> <tr><th width="5%" class="key" nowrap="nowrap"><?php echo JText::_('SWT_NAME'); ?> </th><th class="key" nowrap="nowrap"><?php echo $liga_name; ?> </td><tr> <tr><th width="5%" class="key" nowrap="nowrap"><?php echo JText::_('SWT_MANNSCHAFTEN'); ?> </th><th class="key" nowrap="nowrap"><?php echo $swt[603]; ?> </td><tr> <tr><th width="5%" class="key" nowrap="nowrap"><?php echo JText::_('SWT_RUNDEN'); ?> </th><th class="key" nowrap="nowrap"><?php echo $swt[2]; ?> </td><tr> <tr><th width="5%" class="key" nowrap="nowrap"><?php echo JText::_('SWT_AKT_RUNDE'); ?> </th><th class="key" nowrap="nowrap"><?php echo $swt[4]; ?> </td><tr> <tr><th width="5%" class="key" nowrap="nowrap"><?php echo JText::_('SWT_BRETTER'); ?> </th><th class="key" nowrap="nowrap"><?php echo $swt[605]; ?> </td><tr> <tr><th width="5%" class="key" nowrap="nowrap"><?php echo JText::_('SWT_SPIELER'); ?> </th><th class="key" nowrap="nowrap"><?php echo $swt[8]; ?> </td><tr> <tr><th width="5%" class="key" nowrap="nowrap"><?php echo JText::_('SWT_DURCHGAENGE'); ?> </th><th class="key" nowrap="nowrap"><?php echo $swt[599]; ?> </td><tr> <tr><th width="5%" class="key" nowrap="nowrap"><?php echo JText::_('SWT_AKT_DG'); ?> </th><th class="key" nowrap="nowrap"><?php echo $swt[600]; ?> </td><tr> </table> </fieldset> </div> <div> <fieldset> <legend><?php echo JText::_('SWT_TIPS'); ?> </legend> <?php echo JText::_('SWT_TIP_LINE1'); ?> <br> <br><?php echo JText::_('SWT_TIP_LINE1A'); ?> <b><?php echo JText::_('SWT_TIP_LINE1B'); ?> </b><br> <br><?php echo JText::_('SWT_TIP_LINE2'); ?> <br> <br><?php echo JText::_('SWT_TIP_LINE3'); ?> </fieldset> </div> </div> <div class="col width-70"> <div> <fieldset> <legend><?php echo JText::_('SWT_MANNSCHAFTEN'); ?> </legend> <table class="admintable"> <tr> <th width="5%" class="key" nowrap="nowrap"><?php echo JText::_('SWT_TLN_NR'); ?> </th> <th class="key" nowrap="nowrap"><?php echo JText::_('SWT_MANNSCHAFT'); ?> </th> <th width="6%" class="key" nowrap="nowrap"><?php echo JText::_('SWT_ZPS'); ?> </th> <th width="27%" class="key" nowrap="nowrap"><?php echo JText::_('SWT_VEREIN'); ?> </th> <th width="27%" class="key" nowrap="nowrap"><?php echo JText::_('SWT_OPT_NAME'); ?> </th> </tr> <?php $offset = 13384 + $swt[8] * $swt[2] * 19 + $swt[2] * $swt[603] * 19 + $swt[8] * 655; for ($man = 1; $man < 1 + $swt[603]; $man++) { $man_name = CLMControllerSWT::give_name($swt, 1 + $offset, $offset + 65); $offset += 655; ?> <tr> <td width="5%" class="key" nowrap="nowrap"><?php echo $man; ?> </td> <td class="key" nowrap="nowrap"><?php echo $man_name; ?> </td> <input type="hidden" name="man_name<?php echo $man; ?> " value="<?php echo $man; ?> " /> <td width="6%" class="key" nowrap="nowrap"><?php echo $man_zps[$man]; ?> </td> <input type="hidden" name="man_zps<?php echo $man; ?> " value="<?php echo $man_zps[$man]; ?> " /> <td width="27%" class="key" nowrap="nowrap"> <select size="1" name="<?php echo 'zps' . $man; ?> " id="<?php echo 'zps' . $man; ?> "> <option value="0"><?php echo JText::_('SWT_VEREIN_AUSWAEHLEN'); ?> </option> <?php for ($x = 0; $x < count($vereine); $x++) { ?> <option value="<?php echo $vereine[$x]->zps; ?> " <?php if ($man_zps[$man] == $vereine[$x]->zps) { ?> selected="selected" <?php } ?> ><?php echo $vereine[$x]->name; ?> </option> <?php } ?> </select> </td> <td width="27%" class="key" nowrap="nowrap"><input class="inputbox" type="text" name="manuell_name<?php echo $man; ?> " id="manuell_name<?php echo $man; ?> " size="40" maxlength="60" value="<?php if (!$name_manuell[$man - 1] or $name_manuell[$man - 1]->name == "") { echo $man_name; } else { echo $name_manuell[$man - 1]->name; } ?> " /></td> </tr> <?php } ?> </table> </fieldset> </div> </div> <input type="hidden" name="liga1" value="<?php echo $veranstaltung; ?> " /> <input type="hidden" name="liga2" value="<?php echo $swt[607]; ?> " /> <input type="hidden" name="liga3" value="<?php echo $liga_name; ?> " /> <input type="hidden" name="liga4" value="<?php echo $swt[603]; ?> " /> <input type="hidden" name="liga5" value="<?php echo $swt[2]; ?> " /> <input type="hidden" name="liga6" value="<?php echo $swt[4]; ?> " /> <input type="hidden" name="liga7" value="<?php echo $swt[605]; ?> " /> <input type="hidden" name="liga8" value="<?php echo $swt[8]; ?> " /> <input type="hidden" name="liga9" value="<?php echo $swt[599]; ?> " /> <input type="hidden" name="liga10" value="<?php echo $swt[600]; ?> " /> <input type="hidden" name="step" value="import_1" /> <input type="hidden" name="fileName" value="<?php echo $fileName; ?> "/> <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); }