";
}
$tags .= "| " . $i . " | ";
$tags .= "" . htmlspecialchars($row['date']) . " | ";
$tags .= "" . htmlspecialchars($row['item']) . " | ";
if($row['price'] < 0) {
$tags .= "" . htmlspecialchars($row['price']) . " | ";
} else {
$tags .= "" . htmlspecialchars($row['price']) . " | ";
}
$tags .= "" . htmlspecialchars($row['notes']) . " | ";
$tags .= "" . htmlspecialchars($row['filename']) . " | ";
$tags .= "" . htmlspecialchars($row['fixed_charge']) . " | ";
$tags .= "" . htmlspecialchars($row['settled']) . " | ";
$tags .= "
\n";
$i++;
}
// DBとの接続をクローズ
$db = null;
/**
* ワークシートにある登録済み固定費の最新年月取得SQL
*/
function getLastDateSql() {
$sql = "SELECT\n";
$sql .= " MAX(w.date) AS last_date\n";
$sql .= "FROM\n";
$sql .= " worksheet w\n";
$sql .= "WHERE\n";
$sql .= " w.user_id = ?\n";
$sql .= "AND fixed_charge = 1\n";
$sql .= ";\n";
return $sql;
}
/**
* 未登録の固定費挿入SQL
*/
function getUnregistInsertSql() {
$sql = "INSERT INTO worksheet (\n";
$sql .= " date\n";
$sql .= " ,user_id\n";
$sql .= " ,item\n";
$sql .= " ,price\n";
$sql .= " ,fixed_charge\n";
$sql .= ")\n";
$sql .= "SELECT\n";
$sql .= " ?\n";
$sql .= " ,f.user_id\n";
$sql .= " ,f.item\n";
$sql .= " ,f.price\n";
$sql .= " ,1\n";
$sql .= "FROM\n";
$sql .= " fixed_charge f\n";
$sql .= " LEFT JOIN worksheet w\n";
$sql .= " ON w.user_id = f.user_id\n";
$sql .= " AND w.item = f.item\n";
$sql .= " AND w.date = ?\n";
$sql .= " AND w.fixed_charge = 1\n";
$sql .= "WHERE\n";
$sql .= " w.user_id IS NULL\n";
$sql .= "AND f.start_month <= ?\n";
$sql .= ";\n";
return $sql;
}
/**
* ユーザー情報取得SQL
*/
function getUserInfoSql() {
$sql = "SELECT\n";
$sql .= " h.name AS house_name\n";
$sql .= " ,u.name AS user_name\n";
$sql .= "FROM\n";
$sql .= " inhabitant i\n";
$sql .= " INNER JOIN house h\n";
$sql .= " ON h.id = i.house_id\n";
$sql .= " INNER JOIN user u\n";
$sql .= " ON u.id = i.user_id\n";
$sql .= "WHERE\n";
$sql .= " i.house_id = ?\n";
$sql .= "AND i.user_id = ?\n";
$sql .= ";\n";
return $sql;
}
/**
* ワークシート取得SQL
*/
function getWorksheetSql() {
$sql = "SELECT\n";
$sql .= " w.date\n";
$sql .= " ,w.item\n";
$sql .= " ,w.price\n";
$sql .= " ,w.notes\n";
$sql .= " ,w.filename\n";
$sql .= " ,CASE w.fixed_charge\n";
$sql .= " WHEN 1 THEN '○'\n";
$sql .= " ELSE ''\n";
$sql .= " END AS fixed_charge\n";
$sql .= " ,CASE w.settled\n";
$sql .= " WHEN 1 THEN '完了'\n";
$sql .= " ELSE '未精算'\n";
$sql .= " END AS settled\n";
$sql .= "FROM\n";
$sql .= " worksheet w\n";
$sql .= "WHERE\n";
$sql .= " w.user_id = ?\n";
$sql .= "ORDER BY\n";
$sql .= " w.date DESC\n";
$sql .= " ,w.fixed_charge\n";
$sql .= " ,w.price\n";
$sql .= " ,w.item\n";
$sql .= ";\n";
return $sql;
}
/**
* まとめて完了更新SQL
*/
function getDoFixSql() {
$sql = "UPDATE\n";
$sql .= " worksheet\n";
$sql .= "SET\n";
$sql .= " settled = 1\n";
$sql .= "WHERE\n";
$sql .= " user_id = ?\n";
$sql .= ";\n";
return $sql;
}
?>