top of page
Ianseoカスタムモジュール
内容

Ianseoカスタムモジュールになります。

\ianseo\htdocs\Modules\Custom\

上記のディレクトリに以下の2つのphpファイルをアップロードします。

例えば、Export-AJAF.php・Export-AJAF-field.phpとした場合、同じディレクトリ内のmenu.phpファイルに以下のコードを書き込みます。

if($on) {
$ret['QUAL'][] = 'TXTエクスポート-ターゲット用' . '|' . $CFG->ROOT_DIR . 'Modules/Custom/Export-AJAF.php';
$ret['QUAL'][] = 'TXTエクスポート-フィールド用' . '|' . $CFG->ROOT_DIR . 'Modules/Custom/Export-AJAF-field.php';
}
?>

このカスタムモジュールは、全日本アーチェリー連盟の会員管理システムに取り込むために利用するExcelマクロファイル(.xlsm)に直接貼り付けが出来るtxtファイルを出力します。

マクロファイルに貼り付け後は、「データ」→「区切り位置」からコンマ区切りによってデータを分割します。

※分割完了前に、会員番号列(C列)は文字列になっていることを確認してください。


<ターゲット用>

<?php
define('debug',false);	// settare a true per l'output di debug

/*
**************************************************************************
//Output as txt file for AJAF results reporting
//Note: This is for targets only!
//It is comma-separated.
//Placement \ianseo\htdocs\Modules\Custom\
//Date: 2023-12-14
**************************************************************************
*/

require_once(dirname(dirname(__FILE__)) . '/config.php');
require_once('Common/Fun_FormatText.inc.php');
require_once('Common/Lib/ArrTargets.inc.php');

CheckTourSession(true);
checkACL(AclQualification, AclReadOnly);

$Sql = "SELECT ToCode, ToNumDist, ToGolds, ToXNine FROM Tournament WHERE ToId=" . StrSafe_DB($_SESSION['TourId']);
$q = safe_r_sql($Sql);
$tourData = null;
if (safe_num_rows($q)==1) {
    $tourData=safe_fetch($q);
} else {
    exit;
}

$DistQuery='';
for ($i=1;$i<=$tourData->ToNumDist;++$i) {
	$DistQuery .= "QuD{$i}Score as D{$i}Score, ";
}

$Sql = "SELECT if(QuClRank=0,'',QuClRank) as QuClRank, CONCAT(DivDescription ,ClDescription) AS EntryName ,
	EnCode as Bib, EnFirstName AS FamilyName, EnName AS GivenName,
	QuScore,
	$DistQuery
   	QuGold,QuXnine
	FROM Qualifications
	INNER JOIN Entries ON QuId=EnId
	INNER JOIN Countries ON EnCountry=CoId AND EnTournament=CoTournament
	INNER JOIN Divisions ON EnDivision=DivID AND EnTournament=DivTournament
	INNER JOIN Classes ON EnClass=ClID AND EnTournament=ClTournament
	LEFT JOIN Individuals on IndId=EnId and IndTournament=EnTournament

	WHERE EnTournament = " . StrSafe_DB($_SESSION['TourId']) . " AND EnAthlete=1
	ORDER BY EntryName,QuScore desc,QuGold desc,QuXnine desc";
$q=safe_r_sql($Sql);
	//echo $MyQuery;exit;

$header = array('Category Rank','Category Name','ID','Name','Furigana','Score');
for ($i=1;$i<=$tourData->ToNumDist;++$i) {
    $header =array_merge($header, array("D{$i} Score"));
}
$header =array_merge($header, array($tourData->ToGolds, $tourData->ToXNine));

$data = array();
$data[] = $header;
while ($r=safe_fetch_assoc($q)) {
    for ($i=1;$i<=$tourData->ToNumDist;++$i) {
        $r["QuD{$i}Arrowstring"] = implode(',', DecodeFromString(rtrim($r["QuD{$i}Arrowstring"]), false, true));
    }
    $data[] = $r;

}

header('Cache-Control: no-store, no-cache, must-revalidate');
header('Content-Disposition: attachment; filename=' . $tourData->ToCode . '.txt');
header('Content-type: text/tab-separated-values; charset=' . PageEncode);
foreach ($data as $row) {
    echo implode(',',$row) . "\r\n";
}
?>

<フィールド用>

<?php
define('debug',false);	// settare a true per l'output di debug

/*
**************************************************************************
//Output as txt file for AJAF results reporting
//Note: This is for fields only!
//It is comma-separated.
//Placement \ianseo\htdocs\Modules\Custom\
//Date: 2023-12-14
**************************************************************************
*/

require_once(dirname(dirname(__FILE__)) . '/config.php');
require_once('Common/Fun_FormatText.inc.php');
require_once('Common/Lib/ArrTargets.inc.php');

CheckTourSession(true);
checkACL(AclQualification, AclReadOnly);

$Sql = "SELECT ToCode, ToNumDist, ToGolds, ToXNine FROM Tournament WHERE ToId=" . StrSafe_DB($_SESSION['TourId']);
$q = safe_r_sql($Sql);
$tourData = null;
if (safe_num_rows($q)==1) {
    $tourData=safe_fetch($q);
} else {
    exit;
}

// フィールド出力
Field:
$data = array();
$data[] = array('ラウンド1');
$data[] = array('Category Rank','Category Name','ID','Name','Furigana','D1Score','D1 6','D1 5');

$Sql = "SELECT if(QuD1Rank=0,'',QuD1Rank) as QuClRank, CONCAT(DivDescription ,ClDescription) AS EntryName ,
	EnCode as Bib, EnFirstName AS FamilyName, EnName AS GivenName,
	QuD1Score as D1Score, QuD1Gold as D16, QuD1Xnine as D15
	FROM Qualifications
	INNER JOIN Entries ON QuId=EnId
	INNER JOIN Countries ON EnCountry=CoId AND EnTournament=CoTournament
	INNER JOIN Divisions ON EnDivision=DivID AND EnTournament=DivTournament
	INNER JOIN Classes ON EnClass=ClID AND EnTournament=ClTournament
	LEFT JOIN Individuals on IndId=EnId and IndTournament=EnTournament
	WHERE EnTournament = " . StrSafe_DB($_SESSION['TourId']) . " AND EnAthlete=1
	ORDER BY EntryName,D1Score desc,D16 desc,D15 desc";
$q=safe_r_sql($Sql);

while ($r=safe_fetch_assoc($q)) {
    for ($i=1;$i<=$tourData->ToNumDist;++$i) {
        $r["QuD{$i}Arrowstring"] = implode(',', DecodeFromString(rtrim($r["QuD{$i}Arrowstring"]), false, true));
    }
    $data[] = $r;
}

$data[] = array('');
$data[] = array('ラウンド2');
$data[] = array('Category Rank','Category Name','ID','Name','Furigana','D2Score','D2 6','D2 5');

$Sql = "SELECT if(QuD2Rank=0,'',QuD2Rank) as QuClRank, CONCAT(DivDescription ,ClDescription) AS EntryName ,
	EnCode as Bib, EnFirstName AS FamilyName, EnName AS GivenName,
	QuD2Score as D2Score, QuD2Gold as D26, QuD2Xnine as D25
	FROM Qualifications
	INNER JOIN Entries ON QuId=EnId
	INNER JOIN Countries ON EnCountry=CoId AND EnTournament=CoTournament
	INNER JOIN Divisions ON EnDivision=DivID AND EnTournament=DivTournament
	INNER JOIN Classes ON EnClass=ClID AND EnTournament=ClTournament
	LEFT JOIN Individuals on IndId=EnId and IndTournament=EnTournament
	WHERE EnTournament = " . StrSafe_DB($_SESSION['TourId']) . " AND EnAthlete=1
	ORDER BY EntryName,D2Score desc,D26 desc,D25 desc";
$q=safe_r_sql($Sql);

while ($r=safe_fetch_assoc($q)) {
    for ($i=1;$i<=$tourData->ToNumDist;++$i) {
        $r["QuD{$i}Arrowstring"] = implode(',', DecodeFromString(rtrim($r["QuD{$i}Arrowstring"]), false, true));
    }
    $data[] = $r;
}

goto Write;

Write:

header('Cache-Control: no-store, no-cache, must-revalidate');
header('Content-Disposition: attachment; filename=' . $tourData->ToCode . '.txt');
header('Content-type: text/tab-separated-values; charset=' . PageEncode);
foreach ($data as $row) {
    echo implode(',',$row) . "\r\n";
}
?>

bottom of page