ClearSession();
die("Stop hacking.
Get a real job.");
}
#-------------------------------------------------------------------------------
function __construct() {
try {
$PDOOptions = array(
// PDO::ATTR_PERSISTENT => true,
PDO::ERRMODE_EXCEPTION => true );
$u = rtrim(mcrypt_decrypt(MCRYPT_RIJNDAEL_256, md5(_S917823), base64_decode(_DB_UID), MCRYPT_MODE_CBC, md5(md5(_S917823))), "\0");
$p = rtrim(mcrypt_decrypt(MCRYPT_RIJNDAEL_256, md5(_S917823), base64_decode(_DB_PWD), MCRYPT_MODE_CBC, md5(md5(_S917823))), "\0");
$this->db_handle = new PDO("odbc:" . _DB_DSN, $u, $p, $PDOOptions );
if( _DEBUG )
$this->db_handle->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
$msg = "DBConnect error";
if( _DEBUG ) $msg .= "\n
" . $e->getMessage();
die( $msg );
}
$this->doc = new DOMDocument('1.0');
$this->doc->formatOutput = true;
$this->root = $this->doc->createElement('page');
$this->root = $this->doc->appendChild($this->root);
/*Артикулен No;Акцизен код;Търг.марка;Тарифен No;Артикул-наименование;%VOL;Вместимост;ПАД No;ПАД дата; Ф-ра дата; Ф-ра No;Начин на постъпване;К-во вход;Налично к-во;Изход.Д-т номер; Изход.Д-т дата;Предназначение;К-во изход;
SELECT total,
cdt_part_number,
cdt_exc_product_code, -
cdt_brand, -
cdt_trf_code,
cdt_description,
cdt_volume,
cdt_weight,
cdt_exc_box_price,
padin_number,
padin_date,
exp_inv_date,
exp_inv_number,
sni_exc_mode,
snig_quantity,
qty_rest,
add_number,
add_date,
sno_exc_purpose,
snog_quantity
*/
$this->reportHeaders = array();
$this->reportHeaders['es_number'] = "Склад No.";
$this->reportHeaders['cnt_name'] = "Вложител";
$this->reportHeaders['owner_type'] = "Вид";
$this->reportHeaders['cnt_bulstat'] = "БУЛСТАТ";
$this->reportHeaders['padin_number'] = "ПАД No";
$this->reportHeaders['padin_date'] = "ПАД дата";
$this->reportHeaders['sni_date'] = "Дата на постъпване";
$this->reportHeaders['exp_inv_number'] = "Ф-ра No";
$this->reportHeaders['exp_inv_date'] = "Ф-ра дата";
$this->reportHeaders['sni_exc_mode'] = "Начин на постъпване";
$this->reportHeaders['cdt_part_number'] = "Артикулен No";
$this->reportHeaders['cdt_exc_product_code'] = "Акцизен код";
$this->reportHeaders['cdt_brand'] = "Код ТМ";
$this->reportHeaders['measure'] = "МЕ";
$this->reportHeaders['cdt_qty12'] = "Къса";
$this->reportHeaders['cdt_cigar_len'] = "Дължина";
$this->reportHeaders['cdt_trf_code'] = "Тарифен No";
$this->reportHeaders['cdt_description'] = "Артикул";
$this->reportHeaders['cdt_volume'] = "%VOL";
$this->reportHeaders['cdt_weight'] = "Вместимост";
$this->reportHeaders['snig_quantity'] = "К-во вход";
$this->reportHeaders['qty_rest'] = "Налично к-во";
$this->reportHeaders['add_number'] = "Изх.Д-т No.";
$this->reportHeaders['add_date'] = "Изх.Д-т дата";
$this->reportHeaders['sno_exc_purpose'] = "Предназначение";
$this->reportHeaders['snog_quantity'] = "К-во изход";
$this->reportHeaders['cdt_exc_box_price'] = "Продажна цена";
$this->reportHeaders['snig_ord_number_in'] = "No.";
$this->reportHeaders['cepi_transaction'] = "СЕПИ";
}
#-------------------------------------------------------------------------------
function renderCommonPage() {
$token = md5(uniqid(rand(), true));
if( $_SERVER['REQUEST_METHOD'] == "POST" ) {
if( isset($_SESSION['token']) ) {
if( $_POST['formToken'] != $_SESSION['token'] ) {
$this->HackAttempt();
} else {
$token = $_POST['formToken'];
}
}
}
// page title
$title = $this->doc->createElement('title');
$this->root->appendChild($title);
$text = $this->doc->createTextNode('АлфаАгент - Модул "Акцизен и Данъчен склад"');
$title->appendChild($text);
// copyright year
$node = $this->doc->createElement('year');
$this->doc->appendChild($node);
$text = $this->doc->createTextNode( date("Y") );
$node->appendChild($text);
// form
$form = $this->doc->createElement('form');
$form = $this->root->appendChild($form);
$node = $this->doc->createElement('action');
$form->appendChild($node);
$text = $this->doc->createTextNode( $_SERVER['SCRIPT_NAME'] );
$node->appendChild($text);
$_SESSION['token'] = $token;
$node = $this->doc->createElement('token');
$form->appendChild($node);
$text = $this->doc->createTextNode( $token );
$node->appendChild($text);
return $form;
}
#-------------------------------------------------------------------------------
function renderReportPage() {
$form = $this->renderCommonPage();
$stores = $this->doc->createElement('stores');
$form->appendChild($stores);
$qry = "
select es_sid,
es_number,
es_city,
es_post_code,
es_street,
es_street_no
from alpha_dba.excise_store";
try {
$stmt = $this->db_handle->prepare($qry);
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
if( _DEBUG ) echo $e->getMessage();
}
foreach($result as $row) {
$node = $this->doc->createElement('store');
$stores->appendChild($node);
$text = $this->doc->createTextNode(
iconv ('CP1251','UTF-8',
$row["es_number"] . " | " .
$row["es_city"] . " " .
$row["es_post_code"] . " | " .
$row["es_street"] . " " .
$row["es_street_no"] ));
$node->appendChild($text);
$attr = $this->doc->createAttribute ( "value" );
$text = $this->doc->createTextNode( $row["es_sid"] );
$attr->appendChild($text);
$node->appendChild($attr);
if( $_POST['selectStore'] == $row["es_sid"] ) {
$attr = $this->doc->createAttribute ( "selected" );
$text = $this->doc->createTextNode( "true" );
$attr->appendChild($text);
$node->appendChild($attr);
}
} // foreach($result as $row) {
// Add begDate
$node = $this->doc->createElement('begDate');
$form->appendChild($node);
if( empty($_POST['begDate']) ) {
$date_parts = getdate();
$_POST['begDate'] = $date_parts['year'] . "-" . sprintf( "%02d", $date_parts['mon']) . "-01";
}
$text = $this->doc->createTextNode( $_POST['begDate'] );
$node->appendChild($text);
// Add endDate
$node = $this->doc->createElement('endDate');
$form->appendChild($node);
$text = $this->doc->createTextNode( $_POST['endDate'] );
$node->appendChild($text);
}
#-------------------------------------------------------------------------------
function alignByColumnType( $type ) {
switch ( strtolower($type) ) {
case "char" :
return "align_L";
case "numeric" :
case "decimal" :
return "align_R";
case "date" :
return "align_C";
}
}
#-------------------------------------------------------------------------------
function renderReportData() {
$qry = 'SELECT
cnt_name,
owner_type,
cnt_bulstat,
padin_number,
padin_date,
sni_date,
snig_ord_number_in,
sni_exc_mode,
measure,
cdt_brand,
cdt_trf_code,
cdt_part_number,
cdt_exc_product_code,
cdt_description,
cdt_qty12,
cdt_cigar_len,
cdt_volume,
cdt_weight,
cdt_exc_box_price,
cepi_transaction,
snig_quantity,
add_number,
add_date,
sno_exc_purpose,
snog_quantity,
qty_rest
FROM v_excice_web
WHERE es_sid = :es_sid';
if( !empty($_POST["begDate"]) && !empty($_POST["endDate"]) )
$qry .= ' AND (( padin_date >= :begDate and padin_date <= :endDate ) OR padin_date IS NULL ) ';
if( !empty($_POST["begDate"]) && empty($_POST["endDate"]) )
$qry .= ' AND ( padin_date >= :begDate OR padin_date IS NULL ) ';
if( empty($_POST["begDate"]) && !empty($_POST["endDate"]) )
$qry .= ' AND ( padin_date <= :endDate OR padin_date IS NULL ) ';
$qry .= ' ORDER BY cnt_bulstat, padin_date, padin_number, snig_ord_number_in, add_date, add_number';
/*
$qry = '
select distinct
cnt_name ,
isnull(pad_in.exp_mrn,pad_in.exp_number) as padin_number,
pad_in.exp_date as padin_date,
pad_in.exp_inv_number,
pad_in.exp_inv_date,
sni_exc_mode,
cdt_part_number,
cdt_exc_product_code,
cdt_trf_code,
cdt_description,
cdt_volume,
cdt_weight,
snig_quantity,
snig_quantity - snig_used_quantity as qty_rest,
isnull(pad_out.exp_mrn, isnull(pad_out.exp_number, isnull( cast(etd_number as varchar(22) ), cast(ep_number as varchar(22))) ) ) as add_number,
isnull(etd_date, isnull(pad_out.exp_date, ep_date)) as add_date,
sno_exc_purpose,
snog_quantity
from alpha_dba.store_note_in
join
(alpha_dba.store_note_in_goods
join alpha_dba.commodity on snig_commodity = cdt_sid
left outer join
(alpha_dba.store_note_out_goods
join
(alpha_dba.store_note_out
left outer join alpha_dba.excise_tax_document on sno_sid = etd_sno_sid
left outer join alpha_dba.excise_pad pad_out on sno_sid = pad_out.exc_sno_sid
left outer join alpha_dba.excise_protokol on sno_sid = ep_sno_sid
)
on snog_store_note_out = sno_sid
left outer join alpha_dba.banderol_stock on snog_store_note_out = bs_sno) on snig_store_note_in = snog_store_note_in and snig_ord_number_in = snog_ord_number_in
) on sni_sid = snig_store_note_in
left outer join alpha_dba.excise_pad pad_in on sni_sid = pad_in.exc_sni_sid
join alpha_dba.excise_store on alpha_dba.store_note_in.sni_exc_store = es_sid
join alpha_dba.clients on sni_receiver_id = cnt_sid
Where sni_type_doc = 601
and es_sid = :es_sid';
if( !empty($_POST["begDate"]) )
$qry .= ' and padin_date >= :begDate'; //. "'". $_POST["begDate"] . "'";
if( !empty($_POST["endDate"]) )
$qry .= ' and padin_date <= :endDate'; //. "'". $_POST["endDate"] . "'";
$qry .= ' order by padin_date, padin_number, cdt_part_number, add_date, add_number';
*/
try {
// prepare the SQL statement
$stmt = $this->db_handle->prepare( $qry );
// bind the paramaters
$stmt->bindParam(':es_sid', $_POST['selectStore'], PDO::PARAM_INT);
if( !empty($_POST["begDate"]) )
$stmt->bindParam(':begDate', $_POST['begDate'], PDO::PARAM_STR );
if( !empty($_POST["endDate"]) )
$stmt->bindParam(':endDate', $_POST['endDate'], PDO::PARAM_STR);
// execute the prepared statement
$stmt->execute();
// get data
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
} catch(PDOException $e) {
if( _DEBUG ) echo $e->getMessage();
}
//
$data = $this->doc->createElement('data');
$this->root->appendChild($data);
// Get column datatypes and headers
$header = $this->doc->createElement('header');
$data->appendChild($header);
$colTypes = array();
/*
for( $i = 1; $i <= $stmt->columnCount(); $i++) {
$colMeta = $stmt->getColumnMeta($i);
$colName = $colMeta['name'];
// get type
$colTypes[$colName] = $this->alignByColumnType($colMeta['driver:decl_type']);
// add header
$colNode = $this->doc->createElement('column');
$text = $this->doc->createTextNode(
empty($this->reportHeaders[$colName]) ? $colName : $this->reportHeaders[$colName] );
$colNode->appendChild($text);
$header->appendChild($colNode);
}
*/
// Retrieve data
$rowNumber = 1;
foreach( $result as $row ) {
$rowNode = $this->doc->createElement('row');
$attr = $this->doc->createAttribute ( "class" );
if( $row['total'] == 1 )
$text = $this->doc->createTextNode( $rowNumber%2 ? "data_ODD_1":"data_EVEN_1" );
else
$text = $this->doc->createTextNode( $rowNumber%2 ? "data_ODD_2":"data_EVEN_2" );
$attr->appendChild($text);
$rowNode->appendChild($attr);
$data->appendChild($rowNode);
foreach( $row as $colName => $colValue ) {
// skip total
if( $colName == 'total')
continue;
// Add headers
if($rowNumber == 1) {
$colNode = $this->doc->createElement('column');
$text = $this->doc->createTextNode(
empty($this->reportHeaders[$colName]) ? $colName : $this->reportHeaders[$colName] );
$colNode->appendChild($text);
$header->appendChild($colNode);
}
// Create column node
$colNode = $this->doc->createElement($colName);
$rowNode->appendChild($colNode);
// Add column data
$text = $this->doc->createTextNode(
iconv ('CP1251','UTF-8',$colValue ));
$colNode->appendChild($text);
// Add type attribute
/*
$attr = $this->doc->createAttribute ( "class" );
$text = $this->doc->createTextNode($colTypes[$colName]);
$attr->appendChild($text);
$colNode->appendChild($attr);
*/
}
$rowNumber++;
}
}
#-------------------------------------------------------------------------------
function renderReport() {
$this->renderReportPage();
if( isset($_POST["selectStore"]) && !empty($_POST["selectStore"]) && is_numeric($_POST["selectStore"]) ) {
$this->renderReportData();
}
// return $this->doc->saveXML( $this->root );
$xsl = new DOMDocument;
$xsl->load('xsl/report.xsl');
//return $xsl->saveXML( $xsl );
// Configure the transformer
$proc = new XSLTProcessor;
// attach the xsl rules
$proc->importStyleSheet($xsl);
return $proc->transformToXML($this->doc);
}
#-------------------------------------------------------------------------------
function Logout() {
unset($_SESSION['LoggedIn']);
}
#-------------------------------------------------------------------------------
function Login() {
$qry .= "
select crk_username,
crk_password
from alpha_dba.clerks join alpha_dba.groups on crk_group = gr_sid
where gr_name = 'WEBEXCISE'
and crk_username = :UID
and crk_password = :PWD";
try {
// prepare the SQL statement
$stmt = $this->db_handle->prepare( $qry );
// bind the paramaters
$stmt->bindParam(':UID', $_POST['UID'], PDO::PARAM_STR);
$stmt->bindParam(':PWD', $_POST['PWD'], PDO::PARAM_STR);
// execute the prepared statement
$stmt->execute();
} catch(PDOException $e) {
if( _DEBUG ) echo $e->getMessage();
}
if( count($stmt->fetchAll(PDO::FETCH_ASSOC)) > 0 ) {
$_SESSION['LoggedIn'] = true;
return true;
}
return false;
}
#-------------------------------------------------------------------------------
function renderLogin( $success ) {
$form = $this->renderCommonPage();
if(!$success) {
$node = $this->doc->createElement('errors');
$form->appendChild($node);
$text = $this->doc->createTextNode( 'Невалиден потребител или парола!' );
$node->appendChild($text);
}
$xsl = new DOMDocument;
$xsl->load('xsl/login.xsl');
//return $xsl->saveXML( $xsl );
// Configure the transformer
$proc = new XSLTProcessor;
// attach the xsl rules
$proc->importStyleSheet($xsl);
return $proc->transformToXML($this->doc);
}
#-------------------------------------------------------------------------------
} // End of class ExciseReport
#-------------------------------------------------------------------------------
$report = new ExciseReport();
switch( $_POST['formName'] ){
case 'formLogin':
if( !$report->Login()) {
echo $report->renderLogin(false);
return;
}
break;
case 'formFilter':
if( $_POST['actionController'] == 'logout' ) {
$report->Logout();
}
break;
}
if( $_SESSION['LoggedIn'] ) {
echo $report->renderReport();
} else {
echo $report->renderLogin(true);
}
?>