\n";
if($db->connect()) {
$sql = "SELECT KSA.*, I.`企業名`, K.`カナ`, I.`届出名`, I.`郵便番号`, I.`住所`, I.`電話番号`, I.`建設業従事職員数値`, I.`一級技術者数`, ";
$sql.= "N.`工事名`, I.`資本金`, I.`完工高比`, I.`完工高平均`, I.`総合評点P`, I.`評点Y`, ";
$sql.= "`資金繰り評価`, `売上高`, `売上総利益`, `受取利息配当金`, `支払利息`, `経常利益`, ";
$sql.= "`営業キャッシュフロー当期`, `営業キャッシュフロー前期`, `固定資産当期`, `流動負債`, `固定負債`, ";
$sql.= "`利益剰余金`, `自己資本`, `総資本当期`, `総資本前期`, `純支払利息比率` , `負債回転期間X2` , ";
$sql.= "`総資本売上総利益率X3` , `売上高経常利益率X4` , `自己資本対固定資産比率` , `自己資本比率X6` , ";
$sql.= "`営業キャッシュフローX7` , `利益剰余金X8`,`審査基準日`,`更新日`,`新旧区分`,`審査基準年度`, ";
$sql.= "PERIOD_DIFF(DATE_FORMAT(`審査基準日` + interval 19 month, '%Y%m'), DATE_FORMAT(CURDATE(), '%Y%m')) AS `期限月数`, ";
$sql.= "`支払利息`,`有利子負債`,`完工高対象`,`完工高以前`,`営業利益`,`キャッシュ・フロー`,`当期純利益`, ";
$sql.= "`受取利息配当金`,`総資本当期`,`支払勘定`,`自己資本`,`受取勘定`,`未成工事支出金`,`未成工事受入金` ";
$sql.= "FROM `99ksa6` AS KSA ";
$sql.= "LEFT JOIN `00index` AS I ON KSA.`ID` = I.`ID`";
$sql.= "LEFT JOIN `00kana` AS K ON I.`ID` = K.`ID` ";
$sql.= "LEFT JOIN `92koji` AS N ON I.`工事の種類` = N.`工事の種類` ";
$sql.= "LEFT JOIN `00last_alm` AS A ON I.`ID` = A.`ID` ";
$sql.= "LEFT JOIN `93update` AS U ON U.`ver` = RIGHT(I.`HDER`,4) ";
if($last_date != '' && $first_date != ''){
$sql.= "WHERE U.`更新日` > '".$first_date."' AND U.`更新日` <= '".$last_date."' ";
}
$sql.= ";";
//$sql.= "WHERE I.`市区町村コード` LIKE '18%' ;";
//$sql.= "AND I.`審査基準日` >= date_sub(date_sub(current_date, interval 1 year), interval 7 month) ;";
if($db->load($sql)) {
$record = $db->getRecord();
/* ヘッダー出力 */
$header = "\"No.\",\"顧客コード\",\"名寄コード\",\"JME値\",\"総合評価\",\"総合評定値P\",\"許可番号\",\"企業名\",\"カナ\",\"郵便番号\",\"住所\",\"電話番号\",";
$header.= "\"工事の種類\",\"平均完成工事高\",\"完成工事高比率\",\"資本金\",\"評点Y\",\"資金繰り評価\",\"売上高\",\"完成工事高\",\"売上総利益\",\"営業利益\",\"受取利息配当金\",\"支払利息\",\"経常利益\",";
$header.= "\"営業キャッシュフロー当期\",\"営業キャッシュフロー前期\",\"投資キャッシュフロー\",\"フリーキャッシュフロー\",\"財務キャッシュフロー\",\"現金預金、その他流動資産の増加額\",\"流動資産\",\"固定資産\",\"流動負債\",\"固定負債\",";
$header.= "\"利益剰余金\",\"自己資本\",\"総資本当期\",\"総資本前期\",\"有利子負債\",\"純支払利息比率X1\",\"負債回転期間X2\",\"総資本売上総利益率X3\",";
$header.= "\"売上高経常利益率X4\",\"自己資本対固定資産比率X5\",\"自己資本比率X6\",\"営業キャッシュフローX7\",\"利益剰余金X8\",";
$header.= "\"審査基準日\",\"更新日\",\"審査基準年度\",\"期限月数\",\"従業員数（推定値）\",\"一級技術者数\",\"代表者名\"\n";
echo mb_convert_encoding($header, 'sjis-win', 'UTF-8');
/*
require_once 'JMEScore.class.php';
require_once 'funcComments.php';
*/
$num = 1;
foreach($record as $rec) {
/*
// JME値オブジェクト生成
$jmev = new JMEScore();
// 演算用科目
$v['売上高'] = $rec['売上高'] != null ? $rec['売上高'] : 0;
$v['営業利益'] = $rec['営業利益'] != null ? $rec['営業利益'] : 0;
$v['受取利息配当金'] = $rec['受取利息配当金'] != null ? $rec['受取利息配当金'] : 0;
$v['支払利息'] = $rec['支払利息'] != null ? $rec['支払利息'] : 0;
$v['当期純利益'] = $rec['当期純利益'] != null ? $rec['当期純利益'] : 0;
$v['受取勘定'] = $rec['受取勘定'] != null ? $rec['受取勘定'] : 0;
$v['未成工事支出金'] = $rec['未成工事支出金'] != null ? $rec['未成工事支出金'] : 0;
$v['支払勘定'] = $rec['支払勘定'] != null ? $rec['支払勘定'] : 0;
$v['未成工事受入金'] = $rec['未成工事受入金'] != null ? $rec['未成工事受入金'] : 0;
$v['自己資本'] = $rec['自己資本'] != null ? $rec['自己資本'] : 0;
$v['総資本当期'] = $rec['総資本当期'] != null ? $rec['総資本当期'] : 0;
$v['有利子負債'] = $rec['有利子負債'] != null ? $rec['有利子負債'] : 0;
$v['キャッシュ・フロー'] = $rec['キャッシュ・フロー'] != null ? $rec['キャッシュ・フロー'] : 0;
$v['完工高対象'] = $rec['完工高対象'] != null ? $rec['完工高対象'] : 0;
$v['完工高以前'] = $rec['完工高以前'] != null ? $rec['完工高以前'] : 0;
// スコア計算
$rs01 = $jmev->set_score01($v['支払利息'], $v['有利子負債']);
$rs02 = $jmev->set_score02($v['完工高対象'], $v['完工高以前']);
$rs03 = $jmev->set_score03($v['営業利益'], $v['キャッシュ・フロー'], $v['当期純利益'], $v['有利子負債']);
$rs04 = $jmev->set_score04($v['有利子負債'], $v['売上高']);
$rs05 = $jmev->set_score05($v['支払利息'], $v['受取利息配当金'], $v['売上高']);
$rs06 = $jmev->set_score06($v['有利子負債'], $v['総資本当期']);
$rs07 = $jmev->set_score07($v['支払勘定'], $v['完工高対象']);
$rs08 = $jmev->set_score08($v['受取利息配当金'], $v['支払利息'], $v['完工高対象']);
$rs09 = $jmev->set_score09($v['自己資本'], $v['総資本当期']);
$rs10 = $jmev->set_score10($v['支払利息'], $v['受取利息配当金'], $v['完工高対象']);
$rs11 = $jmev->set_score11($v['総資本当期'], $v['自己資本']);
$rs12 = $jmev->set_score12($v['有利子負債'], $v['自己資本']);
$rs13 = $jmev->set_score13($v['受取勘定'], $v['未成工事支出金'], $v['未成工事受入金'], $v['自己資本']);
$rs = round($jmev->get_score(),1);
// 総合評価算出
$tcm = cmTotal(array(
'JME値'=>$rs,
'ALARM値'=>$rec['資金繰り評価']
)
);
*/
/* JME値・総合評価算出 */
$chk_rs = check_skgdb($rec['ID'], DB_DSN);
/* NULL出力処理 */
$rec['資金繰り評価'] = $rec['資金繰り評価'] != null ? $rec['資金繰り評価'] : "NULL";
$chk_rs['JME値'] = $chk_rs['JME値'] != '' ? $chk_rs['JME値'] : "NULL";
$chk_rs['総合評価'] = $chk_rs['総合評価'] != ' ' ? $chk_rs['総合評価'] : "NULL";
//$rs = $rs != null ? $rs : "NULL";
//$tcm['score'] = $tcm['score'] != ' ' ? $tcm['score'] : "NULL";
$rec['売上高'] = $rec['売上高'] != null ? $rec['売上高'] : "NULL";
$rec['純支払利息比率'] = $rec['純支払利息比率'] != null ? $rec['純支払利息比率'] : "NULL";
$rec['負債回転期間X2'] = $rec['負債回転期間X2'] != null ? $rec['負債回転期間X2'] : "NULL";
$rec['総資本売上総利益率X3'] = $rec['総資本売上総利益率X3'] != null ? $rec['総資本売上総利益率X3'] : "NULL";
$rec['売上高経常利益率X4'] = $rec['売上高経常利益率X4'] != null ? $rec['売上高経常利益率X4'] : "NULL";
$rec['自己資本対固定資産比率'] = $rec['自己資本対固定資産比率'] != null ? $rec['自己資本対固定資産比率'] : "NULL";
$rec['自己資本比率X6'] = $rec['自己資本比率X6'] != null ? $rec['自己資本比率X6'] : "NULL";
$rec['営業キャッシュフローX7'] = $rec['営業キャッシュフローX7'] != null ? $rec['営業キャッシュフローX7'] : "NULL";
$rec['利益剰余金X8'] = $rec['利益剰余金X8'] != null ? $rec['利益剰余金X8'] : "NULL";
$rec['審査基準日'] = $rec['審査基準日'] != null ? $rec['審査基準日'] : "NULL";
$rec['更新日'] = $rec['更新日'] != null ? $rec['更新日'] : "NULL";
$rec['審査基準年度'] = ($rec['審査基準年度'] == null && $rec['新旧区分'] == 1) ? $rec['審査基準年度'] = "2008" : $rec['審査基準年度'];
$rec['審査基準年度'] = $rec['審査基準年度'] != null ? $rec['審査基準年度'] : "NULL";
$line = "\"".$rec['no']."\",";
$line.= "\"".$rec['CD']."\",";
$line.= "\"".$rec['ND']."\",";
$line.= "\"".$chk_rs['JME値']."\",";
$line.= "\"".$chk_rs['総合評価']."\",";
$line.= "\"".$rec['総合評点P']."\",";
$line.= "\"".$rec['ID']."\",";
$line.= "\"".$rec['企業名']."\",";
$line.= "\"".$rec['カナ']."\",";
$line.= "\"".$rec['郵便番号']."\",";
$line.= "\"".$rec['住所']."\",";
$line.= "\"".$rec['電話番号']."\",";
$line.= "\"".$rec['工事名']."\",";
$line.= "\"".$rec['完工高平均']."\",";
$line.= "\"".$rec['完工高比']."\",";
$line.= "\"".$rec['資本金']."\",";
$line.= "\"".$rec['評点Y']."\",";
$line.= "\"".$rec['資金繰り評価']."\",";
$line.= "\"".$rec['売上高']."\",";
$line.= "\"".$chk_rs['完成工事高']."\",";
$line.= "\"".$rec['売上総利益']."\",";
$line.= "\"".$chk_rs['営業利益']."\",";
$line.= "\"".$rec['受取利息配当金']."\",";
$line.= "\"".$rec['支払利息']."\",";
$line.= "\"".$rec['経常利益']."\",";
$line.= "\"".$chk_rs['営業CF']."\",";
if($chk_rs['営業CF前期'] != null){
$line.= "\"".$chk_rs['営業CF前期']."\",";
}else{
$line.= "\"".$rec['営業キャッシュフロー前期']."\",";
}
$line.= "\"".$chk_rs['投資CF']."\",";
$line.= "\"".$chk_rs['フリーCF']."\",";
$line.= "\"".$chk_rs['財務CF']."\",";
$line.= "\"".$chk_rs['現金預金増加額']."\",";
$line.= "\"".$chk_rs['流動資産']."\",";
$line.= "\"".$rec['固定資産当期']."\",";
$line.= "\"".$rec['流動負債']."\",";
$line.= "\"".$rec['固定負債']."\",";
$line.= "\"".$rec['利益剰余金']."\",";
$line.= "\"".$rec['自己資本']."\",";
$line.= "\"".$rec['総資本当期']."\",";
$line.= "\"".$rec['総資本前期']."\",";
$line.= "\"".$chk_rs['有利子負債']."\",";
$line.= "\"".$rec['純支払利息比率']."\",";
$line.= "\"".$rec['負債回転期間X2']."\",";
$line.= "\"".$rec['総資本売上総利益率X3']."\",";
$line.= "\"".$rec['売上高経常利益率X4']."\",";
$line.= "\"".$rec['自己資本対固定資産比率']."\",";
$line.= "\"".$rec['自己資本比率X6']."\",";
$line.= "\"".$rec['営業キャッシュフローX7']."\",";
$line.= "\"".$rec['利益剰余金X8']."\",";
$line.= "\"".$rec['審査基準日']."\",";
$line.= "\"".$rec['更新日']."\",";
$line.= "\"".$rec['審査基準年度']."\",";
$line.= "\"".$rec['期限月数']."\",";
$line.= "\"".$rec['建設業従事職員数値']."\",";
$line.= "\"".$rec['一級技術者数']."\",";
$line.= "\"".$rec['届出名']."\"\n";
echo mb_convert_encoding($line, 'sjis-win', 'UTF-8');
$num++;
}
} else {
$err = $db->getError();
echo mb_convert_encoding("Error: ${err}\n", 'sjis-win', 'UTF-8');
}
$db->close();
} else {
$err = $db->getError();
echo mb_convert_encoding("Error: ${err}\n", 'sjis-win', 'UTF-8');
}
//echo "\n";
exit();
} else {
?>
KSA Data Download
KSA Data Download
注）「Download」は、更新日が4年以内。「Download All」は、無制限。
Password is different."; ?>
load()) {
die("DB読込エラー:${id}");
}
$y = $corp->getYears();
$ay = $corp->getAlarmYears();
$cnt = count($y ?? []);
if($cnt > 5) {
$cnt--;
}
$ly = $y[0];
$fy = null;
if($cnt != 1) {
$fy = $y[$cnt-1];
}
$info = $corp->getInfo();
$rec = $corp->getFinance();
$rank = $corp->getRanking();
$stat = $corp->getStatistics();
$alarm = $corp->getAlarm();
// ランキング
if($corp->getArea() == "00") {
$rank_area = '全国';
$rank_type = $info["許可名"];
$rank_sales = $rank['完工大臣'];
$rank_p = $rank['P大臣'];
$rank_y = $rank['Y大臣'];
} else {
$rank_area = $info["県名"];
$rank_type = $info["許可名"];
$rank_sales = $rank['完工県'];
$rank_p = $rank['P県'];
$rank_y = $rank['Y県'];
}
// 統計情報
$now_sales = $corp->getIndexValue($ly, "完成工事高", "pl");
$now_p = $rec[$ly]["総合評点P"];
$now_y = $rec[$ly]["評点Y"];
$stat_all = $stat['総数'];
$avg_sales = $stat['完工高平均'];
$avg_p = $stat['P点平均'];
$avg_y = $stat['Y点平均'];
$std_sales = $stat['完工高標準偏差'];
$std_p = $stat['P点標準偏差'];
$std_y = $stat['Y点標準偏差'];
$sc_sales = 0;
$sc_p = 0;
$sc_y = 0;
if($std_sales != 0) {
$sc_sales = ((10 * ($now_sales - $avg_sales)) / $std_sales) + 50;
}
if($std_p != 0) {
$sc_p = ((10 * ($now_p - $avg_p)) / $std_p) + 50;
}
if($std_y != 0) {
$sc_y = ((10 * ($now_y - $avg_y)) / $std_y) + 50;
}
/* 評価用計算 */
// 収益傾向
$sales = 0;
$profits = 0;
$sc_s = '';
$sc_pr = '';
$cm_sales = '';
$cm_profits = '';
if(!is_null($fy)) {
$cprofit = cmProfit(array(
'売上高'=>$rec[$ly]["売上高"],
'売上高前'=>$rec[$fy]["売上高"],
'営業利益'=>$rec[$ly]["営業利益"],
'営業利益前'=>$rec[$fy]["営業利益"]
)
);
$sales = $cprofit['売上高']['point'];
$profits = $cprofit['営業利益']['point'];
$sc_s = $cprofit['売上高']['score'];
$sc_pr = $cprofit['営業利益']['score'];
$cm_sales = $cprofit['売上高']['comment'];
$cm_profits = $cprofit['営業利益']['comment'];
}
$sc = $sc_s.$sc_pr;
// JME値
$jpoint = $corp->getIndexValue($y[0], "JME値", "jme");
$jcm = cmJME($jpoint);
// ALARM値
$apoint = null;
if(!is_null($alarm) && $ly == $ay[0]) {
$apoint = $alarm[$ay[0]]["資金繰り評価"];
}
$acm = cmAlarm($apoint);
// 経審Y評点
$ypoint = $rec[$ly]["評点Y"];
$ycm = cmYpoint(array('評点Y'=>$now_y,'Y点平均'=>$avg_y,'Y点標準偏差'=>$std_y));
// 財務状況
$a = $corp->getIndexValue($ly, "資産合計", "bs");
$e = $corp->getIndexValue($ly, "資本合計", "bs");
$c = $corp->getIndexValue($ly, "資本金", "bs");
$p = $corp->getIndexValue($ly, "営業利益", "pl");
$k = $corp->getIndexValue($ly, "有利子負債", "bs");
$s = $corp->getIndexValue($ly, "売上高合計", "pl");
$ca = $corp->getIndexValue($ly, "現金預金、その他流動資産", "bs");
$aa = $rec[$ly]['受取勘定'];
$fa = $corp->getIndexValue($ly, "固定資産合計", "bs");
$cl = $corp->getIndexValue($ly, "流動負債合計", "bs");
$fl = $corp->getIndexValue($ly, "固定負債合計", "bs");
$tkr = $rec[$ly]['立替工事高比率'];
$i = $corp->getIndexValue($ly, "支払利息", "pl");
$fcm = cmFinace(array(
'総資産'=>$a,
'自己資本'=>$e,
'資本金'=>$c,
'営業利益'=>$p,
'有利子負債'=>$k,
'売上高'=>$s,
'その他流動資産'=>$ca,
'受取勘定'=>$aa,
'固定資産'=>$fa,
'流動負債'=>$cl,
'固定負債'=>$fl,
'立替工事高比率'=>$tkr,
'支払利息'=>$i,
'収益傾向'=>$sc_s.$sc_pr.$cm_profits
)
);
// 財務状況
$fscore = $fcm['score'];
$fcomment = '';
foreach($fcm['comment'] as $_cm) {
$fcomment .= $_cm."
";
}
// 総合評価
/* 2012/12/21:ALARM出力エラー対応 Start
$tcm = cmTotal(array(
'JME値'=>$jpoint,
'ALARM値'=>$apoint
)
);
*/
$tcm = cmTotal_no_alarm(array(
'純支払利息比率'=>$corp->getIndexValue($ly, "純支払利息比率", "index"),
'自己資本比率'=>$corp->getIndexValue($ly, "純資産比率", "index"),
'自己資本'=>$corp->getIndexValue($ly, "資本合計", "bs"),
'資本金'=>$corp->getIndexValue($ly, "資本金", "bs")
)
);
// 2012/12/21:ALARM出力エラー対応 End
$bcf_before = null;
if($cnt != 1) $bcf_before = round($corp->getIndexValue($y[1], "営業活動によるキャッシュ・フロー合計", "cf"));
$result = array(
'総合評価' => $tcm['score'],
'JME値' => number_format($jpoint, 1),
'営業利益' => $corp->getIndexValue($ly, "営業利益", "pl"),
'完成工事高' => $corp->getIndexValue($ly, "完成工事高", "pl"),
'流動資産' => $corp->getIndexValue($ly, "流動資産合計", "bs"),
'有利子負債' => round($corp->getIndexValue($ly, "有利子負債", "bs")),
'営業CF' => round($corp->getIndexValue($ly, "営業活動によるキャッシュ・フロー合計", "cf")),
'営業CF前期' => $bcf_before,
'投資CF' => round($corp->getIndexValue($ly, "投資活動によるキャッシュ・フロー合計", "cf")),
'フリーCF' => round($corp->getIndexValue($ly, "フリー キャッシュ・フロー", "cf")),
'財務CF' => round($corp->getIndexValue($ly, "財務活動によるキャッシュ・フロー合計", "cf")),
'現金預金増加額' => round($corp->getIndexValue($ly, "現金預金、その他流動資産の増加額", "cf"))
);
return $result;
}
?>