protected function tearDown()
{
if ($this->_db) {
$this->_db->close();
}
$this->destroyApplication();
}
protected function tearDown()
{
parent::tearDown();
if ($this->db) {
$this->db->close();
}
$this->destroyApplication();
}
public static function tearDownAfterClass()
{
static::runConsoleAction('migrate/down', ['migrationPath' => '@yii/log/migrations/', 'interactive' => false]);
if (static::$db) {
static::$db->close();
}
Yii::$app = null;
parent::tearDownAfterClass();
}
public static function search(array $input)
{
$connection = new Connection(['dsn' => 'mysql:host=localhost;dbname=work', 'username' => 'root', 'password' => 'root', 'charset' => 'utf8']);
$connection->open();
/**запрос выбирает имя сети, агенства и сумму
* с группировкой ао агенству и "ИТОГО"
* из подзапроса суммы с границами по датам.
* COALESCE() меняет сумму NULL на 0
*/
$sql = 'SELECT agency_network.agency_network_name, agency.agency_name, COALESCE(sum(t.amount),0) AS sum
FROM agency_network
RIGHT JOIN agency
ON agency_network.agency_network_id = agency.agency_network_id
LEFT JOIN
(SELECT amount, date, agency_id
FROM billing WHERE date BETWEEN :startdate AND :enddate) t
ON t.agency_id=agency.agency_id
GROUP BY agency_name WITH ROLLUP;';
/*Привязываем параметры с датами, для исключения иньекций*/
$command = $connection->createCommand($sql)->bindParam(':startdate', $startdate)->bindParam(':enddate', $enddate);
$startdate = $input[date1];
$enddate = $input[date2];
$result = $command->queryAll();
$connection->close();
return $result;
}
public function close()
{
if ($this->pdo !== null) {
$this->_transaction = null;
}
parent::close();
}
private function checkDb()
{
self::_appendLog('检查数据库连接。。。');
$dbHost = LuLu::getPostValue('dbHost');
$dbName = LuLu::getPostValue('dbName');
$dbUsername = LuLu::getPostValue('dbUsername');
$dbPassword = LuLu::getPostValue('dbPassword');
if (empty($dbHost) || empty($dbName) || empty($dbUsername)) {
$message = '数据库信息必须填写完整';
self::_appendLog($message, true);
return false;
}
$config = ['dsn' => "mysql:host={$dbHost};dbname={$dbName}", 'username' => $dbUsername, 'password' => $dbPassword];
$result = false;
$message = '';
$db = new Connection($config);
try {
$db->open();
if (!$db->isActive) {
$message = '连接失败，请检查数据库配置';
$result = false;
} else {
$message = '数据库连接成功';
$result = true;
}
} catch (Exception $e) {
$db->close();
$message = self::getDbError($e->getMessage(), ['dbHost' => $dbHost, 'dbName' => $dbName]);
$result = false;
}
self::_appendLog($message, !$result);
return $result;
}
/**
* Closes the currently active Oracle DB connection.
* It does nothing if the connection is already closed.
*/
public function close()
{
Yii::trace('Closing Oracle connection', 'vendor\\sfedosimov\\yii2-oci8pdo\\Oci8PDO_Connection');
parent::close();
}
/**
* Closes the currently active Oracle DB connection.
* It does nothing if the connection is already closed.
*/
public function close()
{
Yii::trace('Closing Oracle connection', 'ext.oci8Pdo.OciDbConnection');
parent::close();
}
public function actionPdf()
{
if (Yii::$app->request->get("ID_SOLICITUD")) {
$ID_SOLICITUD = Html::encode($_GET["ID_SOLICITUD"]);
$mpdf = new mPDF();
$connection = new \yii\db\Connection(Yii::$app->db);
$connection->open();
$str = '<table border=0 cellspacing=0 cellpadding=2><tbody><tr><td><img src="http://www.ubiobio.cl/mcc/images/ubbdescargagradientesolo.png"/></td><td><h1>RENDICION DE GASTOS</h1><br>Fecha: ' . date("d-m-Y") . '</td></tr></table>';
$mpdf->WriteHTML($str);
///////////////////////////////////////////////////////////////////
$sql = "Select ID_SOLICITUD, ID_TIPO_DE_VIAJE, ID_VIAJE, FECHA_SOLICITUD, CUERPO_SOLICITUD from SOLICITUD_DE_VIAJE where ID_SOLICITUD=" . $ID_SOLICITUD;
$command = $connection->createCommand($sql);
$dataReader = $command->query();
$str = "<h2>- SOLICITUD:</h2><table border=1 cellspacing=0 cellpadding=2><tbody><tr><td>ID</td><td>FECHA SOLICITUD</td><td>CUERPO SOLICITUD</td></tr>";
$dos = 1;
foreach ($dataReader as $row) {
$ID_TIPO_DE_VIAJE = $row->ID_TIPO_DE_VIAJE;
$str = $str . "<tr>";
foreach ($row as $col) {
if ($dos == 2) {
$ID_TIPO_DE_VIAJE = $col;
} else {
if ($dos == 3) {
$ID_VIAJE = $col;
} else {
$str = $str . "<td>" . $col . "</td>";
}
}
$dos = $dos + 1;
}
$str = $str . "</tr>";
}
$str = $str . "</tbody></table>";
$mpdf->WriteHTML($str);
///////////////////////////////////////////////////////////////////
$sql = "Select * from TIPO_DE_VIAJE where ID_TIPO_DE_VIAJE='" . $ID_TIPO_DE_VIAJE . "'";
$command = $connection->createCommand($sql);
$dataReader = $command->query();
$str = "<h2>- TIPO DE VIAJE DE LA SOLICITUD:</h2><table border=1 cellspacing=0 cellpadding=2><tbody><tr><td>ID</td><td>NOMBRE TIPO DE VIAJE</td><td>MONTO TIPO DE VIAJE</td></tr>";
foreach ($dataReader as $row) {
$str = $str . "<tr>";
foreach ($row as $col) {
$str = $str . "<td>" . $col . "</td>";
}
$str = $str . "</tr>";
}
$str = $str . "</tbody></table>";
$mpdf->WriteHTML($str);
///////////////////////////////////////////////////////////////////
$sql = "Select * from VIAJE where ID_VIAJE='" . $ID_VIAJE . "'";
$command = $connection->createCommand($sql);
$dataReader = $command->query();
$str = "<h2>- VIAJE DE LA SOLICITUD:</h2><table border=1 cellspacing=0 cellpadding=2><tbody><tr><td>ID</td><td>ORIGEN</td><td>FECHA INICIO</td><td>FECHA TERMINO</td></tr>";
foreach ($dataReader as $row) {
$str = $str . "<tr>";
foreach ($row as $col) {
$str = $str . "<td>" . $col . "</td>";
}
$str = $str . "</tr>";
}
$str = $str . "</tbody></table>";
$mpdf->WriteHTML($str);
///////////////////////////////////////////////////////////////////
$sql = "Select ID_DESTINO, DURACION_VIAJE_DIAS, MEDIO_DE_TRANSPORTE, CIUDAD_DESTINO, PAIS_DESTINO from DESTINO where ID_VIAJE='" . $ID_VIAJE . "'";
$command = $connection->createCommand($sql);
$dataReader = $command->query();
$str = "<h2>- DESTINO(S) DEL VIAJE:</h2><table border=1 cellspacing=0 cellpadding=2><tbody><tr><td>ID</td><td>DURACION</td><td>MEDIO DE TRANSPORTE</td><td>CIUDAD DESTINO</td><td>PAIS DESTINO</td></tr>";
foreach ($dataReader as $row) {
$str = $str . "<tr>";
foreach ($row as $col) {
$str = $str . "<td>" . $col . "</td>";
}
$str = $str . "</tr>";
}
$str = $str . "</tbody></table>";
$mpdf->WriteHTML($str);
///////////////////////////////////////////////////////////////////
$sql = "Select ID_GASTO, NOMBRE_GASTO, MONTO_GASTO, FECHA_GASTO from GASTOS where ID_VIAJE='" . $ID_VIAJE . "'";
$command = $connection->createCommand($sql);
$dataReader = $command->query();
$str = "<h2>- GASTO(s) ASOCIADOS AL VIAJE:</h2><table border=1 cellspacing=0 cellpadding=2><tbody><tr><td>ID</td><td>NOMBRE</td><td>MONTO</td><td>FECHA</td></tr>";
$id_gasto = 1;
foreach ($dataReader as $row) {
$str = $str . "<tr>";
foreach ($row as $col) {
if ($id_gasto == 1) {
$ID_GASTO = $col;
}
$id_gasto++;
$str = $str . "<td>" . $col . "</td>";
}
$str = $str . "</tr>";
}
$str = $str . "</tbody></table>";
$mpdf->WriteHTML($str);
///////////////////////////////////////////////////////////////////
$str = '<h2>- IMAGEN(ES) ASOCIADAS A CADA GASTO:</h2><p><img src="http://www.teacherclaudio.cl/wp-content/uploads/2014/07/Boleta_01.png"/></p>
<p><img src="http://am-sur.com/am-sur/peru/selva-m/Oxapampa-notizen-d/003-busbillet-Lobato-9-okt-2008.gif"/></p>
<p><img src="http://www.cosale.cl/wp-content/uploads/2013/10/boleta.jpg"/></p>
<p><img src="https://www.reclamos.cl/files/boleta_1_0.jpg"/></p>';
$mpdf->WriteHTML($str);
///////////////////////////////////////////////////////////////////
$mpdf->Output();
$connection->close();
exit;
}
}
