public function testCreate()
{
$db = DB::get();
$srv = new CashMovementsService();
$mvt = new CashMovement($this->cashId, $db->readDate("2014-01-03 00:00:00"), CashMovement::TYPE_CASHIN, 10.0, $this->currencyId, 12, "note");
$id = $srv->create($mvt);
$this->assertNotEquals(false, $id, "Creation failed");
$pdo = PDOBuilder::getPDO();
$stmt = $pdo->prepare("SELECT * FROM RECEIPTS WHERE ID = :id");
$stmt->bindParam(":id", $id);
$stmt->execute();
$row = $stmt->fetch();
$this->assertNotEquals(false, $row, "No receipt found");
$this->assertEquals($mvt->date, $db->readDate($row['DATENEW']), "Date mismatch");
$this->assertEquals($mvt->cashId, $row['MONEY'], "Cash session id mismatch");
$stmtP = $pdo->prepare("SELECT * FROM PAYMENTS WHERE ID = :id");
$stmtP->bindParam(":id", $id);
$stmtP->execute();
$row = $stmtP->fetch();
$this->assertNotEquals(false, $row, "No payment found");
$this->assertEquals($id, $row['RECEIPT'], "Receipt id mismatch");
$this->assertEquals($mvt->type, $row['PAYMENT'], "Payment type mismatch");
$this->assertEquals($mvt->amount, $row['TOTAL'], "Amount mismatch");
$this->assertEquals($mvt->currencyId, $row['CURRENCY'], "Currency id mismatch");
$this->assertEquals($mvt->currencyAmount, $row['TOTALCURRENCY'], "Currency amount mismatch");
$this->assertEquals($mvt->note, $row['NOTE'], "Note mismatch");
}
protected function tearDown()
{
// Restore database in its empty state
$pdo = PDOBuilder::getPDO();
if ($pdo->exec("DELETE FROM ATTRIBUTEUSE") === false || $pdo->exec("DELETE FROM ATTRIBUTESET") === false || $pdo->exec("DELETE FROM ATTRIBUTEVALUE") === false || $pdo->exec("DELETE FROM ATTRIBUTE") === false) {
echo "[ERROR] Unable to restore db\n";
}
}
protected function tearDown()
{
// Restore database in its empty state
$pdo = PDOBuilder::getPDO();
if ($pdo->exec("DELETE FROM CASHREGISTERS") === false || $pdo->exec("DELETE FROM LOCATIONS") === false) {
echo "[ERROR] Unable to restore db\n";
}
}
protected function tearDown()
{
// Restore database in its empty state
$pdo = PDOBuilder::getPDO();
if ($pdo->exec("DELETE FROM TAXES") === false || $pdo->exec("DELETE FROM TAXCATEGORIES") === false) {
echo "[ERROR] Unable to restore db\n";
}
}
static function delete($id)
{
$pdo = PDOBuilder::getPDO();
$stmt = $pdo->prepare("UPDATE TAXES SET CUSTCATEGORY = NULL " . "WHERE CUSTCATEGORY = :id");
$stmt->execute(array(":id" => $id));
$stmt = $pdo->prepare('DELETE FROM TAXCUSTCATEGORIES WHERE ID = :id');
return $stmt->execute(array(':id' => $id));
}
protected function tearDown()
{
// Restore database in its empty state
$pdo = PDOBuilder::getPDO();
if ($pdo->exec("DELETE FROM STOCKDIARY") === false || $pdo->exec("DELETE FROM STOCKCURRENT") === false || $pdo->exec("DELETE FROM STOCKLEVEL") === false || $pdo->exec("DELETE FROM LOCATIONS") === false || $pdo->exec("DELETE FROM PRODUCTS_CAT") === false || $pdo->exec("DELETE FROM PRODUCTS") === false || $pdo->exec("DELETE FROM CATEGORIES") === false || $pdo->exec("DELETE FROM TAXES") === false || $pdo->exec("DELETE FROM TAXCATEGORIES") === false) {
echo "[ERROR] Unable to restore db\n";
}
}
public function setNextTicketId($nextTicketId, $cashId)
{
$pdo = PDOBuilder::getPDO();
$sql = "UPDATE CASHREGISTERS SET NEXTTICKETID = :nextTicketId " . "WHERE ID = :id";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(":nextTicketId", $nextTicketId);
$stmt->bindParam(":id", $cashId);
$stmt->execute();
}
public function update($model)
{
if ($model->isMain) {
$pdo = PDOBuilder::getPDO();
$db = DB::get();
$stmt = $pdo->prepare("UPDATE CURRENCIES SET MAIN = " . $db->false());
$stmt->execute();
}
return parent::update($model);
}
public function create($area)
{
$pdo = PDOBuilder::getPDO();
$stmt = $pdo->prepare("INSERT INTO LOCATIONS " . "(ID, NAME) VALUES (:id, :label)");
$id = md5(time() . rand());
$stmt->bindParam(":id", $id);
$stmt->bindParam(":label", $area->label);
if ($stmt->execute() !== false) {
return $id;
} else {
return false;
}
}
public function getImage($label)
{
$pdo = PDOBuilder::getPDO();
$db = DB::get();
$stmt = $pdo->prepare("SELECT CONTENT FROM RESOURCES WHERE NAME = :label");
$stmt->bindParam(":label", $label, \PDO::PARAM_STR);
if ($stmt->execute()) {
if ($row = $stmt->fetch()) {
return $db->readBin($row['CONTENT']);
}
}
return null;
}
public function testCreate()
{
$prof = new DiscountProfile("Profile", 0.1);
$srv = new DiscountProfilesService();
$prof->id = $srv->create($prof);
$this->assertNotEquals(false, $prof->id, "Creation failed");
$pdo = PDOBuilder::getPDO();
$sql = "SELECT * FROM DISCOUNTPROFILES";
$stmt = $pdo->prepare($sql);
$this->assertNotEquals($stmt->execute(), false, "Query failed");
$row = $stmt->fetch();
$this->assertNotEquals(false, $row, "Nothing found");
$this->assertEquals($prof->id, $row['ID'], "Id mismatch");
$this->assertEquals($prof->label, $row['NAME'], "Label mismatch");
$this->assertEquals($prof->rate, $row['RATE'], "Rate mismatch");
}
public function testCreate()
{
$srv = new CashRegistersService();
$cashReg = new CashRegister("CashReg", $this->location->id, 3);
$id = $srv->create($cashReg);
$this->assertNotEquals(false, $id, "Insertion failed");
$pdo = PDOBuilder::getPDO();
$sql = "SELECT * FROM CASHREGISTERS WHERE NAME = :name";
$stmt = $pdo->prepare($sql);
$stmt->bindValue(":name", $cashReg->label);
$this->assertNotEquals($stmt->execute(), false, "Query failed");
$row = $stmt->fetch();
$this->assertEquals($id, $row['ID'], "Id mismatch");
$this->assertEquals($cashReg->label, $row['NAME'], "Label mismatch");
$this->assertEquals($cashReg->locationId, $row['LOCATION_ID'], "Location id mismatch");
$this->assertEquals($cashReg->nextTicketId, $row['NEXTTICKETID'], "Next ticket id mismatch");
}
function dropDatabase()
{
global $config;
$pdo = PDOBuilder::getPdo();
if ($config['db_type'] == "mysql") {
$sqls = array("DROP TABLE APPLICATIONS;", "DROP TABLE ROLES;", "DROP TABLE ORDERS;", "DROP TABLE ORDERLINES", "DROP TABLE TARIFFAREAS", "DROP TABLE PEOPLE;", "DROP TABLE RESOURCES;", "DROP TABLE PROVIDERS;", "DROP TABLE DISCOUNTPROFILES;", "DROP TABLE TAXCUSTCATEGORIES;", "DROP TABLE CUSTOMERS;", "DROP TABLE CATEGORIES;", "DROP TABLE TAXCATEGORIES;", "DROP TABLE TAXES;", "DROP TABLE ATTRIBUTE;", "DROP TABLE ATTRIBUTEVALUE;", "DROP TABLE ATTRIBUTESET;", "DROP TABLE ATTRIBUTEUSE;", "DROP TABLE ATTRIBUTESETINSTANCE;", "DROP TABLE ATTRIBUTEINSTANCE;", "DROP TABLE PRODUCTS;", "DROP TABLE PRODUCTS_CAT;", "DROP TABLE PRODUCTS_COM;", "DROP TABLE SUBGROUPS;", "DROP TABLE SUBGROUPS_PROD;", "DROP TABLE TARIFFAREAS;", "DROP TABLE TARIFFAREAS_PROD;", "DROP TABLE LOCATIONS;", "DROP TABLE STOCKDIARY;", "DROP TABLE STOCKLEVEL;", "DROP TABLE STOCKCURRENT;", "DROP TABLE STOCK_INVENTORY;", "DROP TABLE STOCK_INVENTORYITEM;", "DROP TABLE CASHREGISTERS;", "DROP TABLE CURRENCIES;", "DROP TABLE CLOSEDCASH;", "DROP TABLE PAYMENTMODES;", "DROP TABLE PAYMENTMODES_RETURNS;", "DROP TABLE PAYMENTMODES_VALUES;", "DROP TABLE RECEIPTS;", "DROP TABLE TICKETS;", "DROP TABLE TICKETLINES;", "DROP TABLE PAYMENTS;", "DROP TABLE TAXLINES;", "DROP TABLE FLOORS;", "DROP TABLE PLACES;", "DROP TABLE RESERVATIONS;", "DROP TABLE RESERVATION_CUSTOMERS;", "DROP TABLE THIRDPARTIES;", "DROP TABLE SHAREDTICKETS;", "DROP TABLE SHAREDTICKETLINES;");
} else {
if ($config['db_type'] == "postgresql") {
$sqls = array("DROP TABLE APPLICATIONS;", "DROP TABLE ROLES;", "DROP TABLE ORDERS;", "DROP TABLE ORDERLINES", "DROP TABLE TARIFFAREAS", "DROP TABLE PEOPLE;", "DROP TABLE RESOURCES;", "DROP TABLE PROVIDERS;", "DROP SEQUENCE DISCOUNTPROFILES_ID_SEQ CASCADE;", "DROP TABLE DISCOUNTPROFILES;", "DROP TABLE TAXCUSTCATEGORIES;", "DROP TABLE CUSTOMERS;", "DROP TABLE CATEGORIES;", "DROP TABLE TAXCATEGORIES;", "DROP TABLE TAXES;", "DROP TABLE ATTRIBUTE;", "DROP TABLE ATTRIBUTEVALUE;", "DROP TABLE ATTRIBUTESET;", "DROP TABLE ATTRIBUTEUSE;", "DROP TABLE ATTRIBUTESETINSTANCE;", "DROP TABLE ATTRIBUTEINSTANCE;", "DROP TABLE PRODUCTS;", "DROP TABLE PRODUCTS_CAT;", "DROP TABLE PRODUCTS_COM;", "DROP SEQUENCE SUBGROUPS_ID_SEQ", "DROP TABLE SUBGROUPS;", "DROP TABLE SUBGROUPS_PROD;", "DROP SEQUENCE TARIFFAREAS_ID_SEQ CASCADE;", "DROP TABLE TARIFFAREAS;", "DROP TABLE TARIFFAREAS_PROD;", "DROP TABLE LOCATIONS;", "DROP TABLE STOCKDIARY;", "DROP TABLE STOCKLEVEL;", "DROP TABLE STOCKCURRENT;", "DROP SEQUENCE STOCK_INVENTORY_ID_SEQ CASCADE;", "DROP SEQUENCE STOCK_INVENTORYITEM_ID_SEQ;", "DROP TABLE STOCK_INVENTORY;", "DROP TABLE STOCK_INVENTORYITEM;", "DROP SEQUENCE CASHREGISTERS_ID_SEQ CASCADE", "DROP TABLE CASHREGISTERS;", "DROP SEQUENCE CURRENCIES_ID_SEQ CASCADE;", "DROP TABLE CURRENCIES;", "DROP TABLE CLOSEDCASH;", "DROP SEQUENCE PAYMENTMODES_ID_SEQ CASCADE;", "DROP TABLE PAYMENTMODES;", "DROP TABLE PAYMENTMODES_RETURNS;", "DROP TABLE PAYMENTMODES_VALUES;", "DROP TABLE RECEIPTS;", "DROP TABLE TICKETS;", "DROP TABLE TICKETLINES;", "DROP TABLE PAYMENTS;", "DROP TABLE TAXLINES;", "DROP TABLE FLOORS;", "DROP TABLE PLACES;", "DROP TABLE RESERVATIONS;", "DROP TABLE RESERVATION_CUSTOMERS;", "DROP TABLE THIRDPARTIES;", "DROP TABLE SHAREDTICKETS;", "DROP TABLE SHAREDTICKETLINES;");
}
}
for ($i = count($sqls) - 1; $i >= 0; $i--) {
if ($pdo->exec($sqls[$i]) === false) {
echo "[ERROR] Could not execute " . $sqls[$i] . "\n";
}
}
}
public function create($mvt)
{
$pdo = PDOBuilder::getPDO();
$db = DB::get();
$newTransaction = !$pdo->inTransaction();
if ($newTransaction) {
$pdo->beginTransaction();
}
$id = md5(time() . rand());
// Insert receipt
$sql = "INSERT INTO RECEIPTS (ID, MONEY, DATENEW) " . "VALUES (:id, :money, :date)";
$stmtRcpt = $pdo->prepare($sql);
$stmtRcpt->bindValue(":id", $id);
$stmtRcpt->bindValue(":money", $mvt->cashId);
$stmtRcpt->bindValue(":date", $db->dateVal($mvt->date));
if ($stmtRcpt->execute() === false) {
if ($newTransaction) {
$pdo->rollback();
}
return false;
}
// Insert payment
$stmtPay = $pdo->prepare("INSERT INTO PAYMENTS (ID, RECEIPT, PAYMENT, " . "TOTAL, CURRENCY, TOTALCURRENCY, NOTE) VALUES (:id, :rcptId, " . ":type, :amount, :currId, :currAmount, :note)");
$stmtPay->bindParam(":id", $id);
$stmtPay->bindParam(":rcptId", $id);
$stmtPay->bindParam(":type", $mvt->type);
$stmtPay->bindParam(":amount", $mvt->amount);
$stmtPay->bindParam(":currId", $mvt->currencyId);
$stmtPay->bindParam(":currAmount", $mvt->currencyAmount);
$stmtPay->bindParam(":note", $mvt->note);
if ($stmtPay->execute() === false) {
var_dump($stmtPay->errorInfo());
if ($newTransaction) {
$pdo->rollback();
}
return false;
}
// Finished
if ($newTransaction) {
$pdo->commit();
}
return $id;
}
public function testCreateImage()
{
$res = new Resource("Test", Resource::TYPE_IMAGE, 0xab93cd);
$srv = new ResourcesService();
$res->id = $srv->create($res);
$this->assertNotEquals(false, $res->id, "Creation failed");
$pdo = PDOBuilder::getPDO();
$db = DB::get();
$sql = "SELECT * FROM RESOURCES WHERE ID = :id";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(":id", $res->id);
$this->assertNotEquals($stmt->execute(), false, "Query failed");
$row = $stmt->fetch();
$this->assertNotEquals(false, $row, "Nothing found");
$this->assertEquals($res->id, $row['ID'], "Id mismatch");
$this->assertEquals($res->label, $row['NAME'], "Label mismatch");
$this->assertEquals($res->type, $row['RESTYPE'], "Type mismatch");
$this->assertEquals($res->content, $db->readBin($row['CONTENT']), "Content mismatch");
$this->assertFalse($stmt->fetch(), "Too much resources matching");
}
public function testCreate()
{
$srv = new CustomersService();
$cust = new Customer(1, "Cust", "It's me", "card", null, null, 12.0, 10.0, 5.0, stdtimefstr("2012-01-01 00:00:00"), "It's", "me", "*****@*****.**", "012345", "23456", "11111", "Address1", "Address2", "59000", "City", "Region", "France", "Note", true);
$id = $srv->create($cust);
$pdo = PDOBuilder::getPDO();
$db = DB::get();
$sql = "SELECT * FROM CUSTOMERS";
$stmt = $pdo->prepare($sql);
$this->assertNotEquals($stmt->execute(), false, "Query failed");
$row = $stmt->fetch();
$this->assertNotEquals(false, $id, "Create failed");
$this->assertEquals($id, $row['ID'], "Id mismatch");
$this->assertEquals(1, $row['TAXID'], "Number mismatch");
$this->assertEquals("Cust", $row['SEARCHKEY'], "Key mismatch");
$this->assertEquals("It's me", $row['NAME'], "Display name mismatch");
$this->assertEquals("card", $row['CARD'], "Card mismatch");
$this->assertEquals(null, $row['TAXCATEGORY'], "Tax cat mismatch");
$this->assertEquals(null, $row['DISCOUNTPROFILE_ID'], "Discount profile id mismatch");
$this->assertEquals(12.0, $row['PREPAID'], "Prepaid mismatch");
$this->assertEquals(10.0, $row['MAXDEBT'], "Max debt mismatch");
$this->assertEquals(5.0, $row['CURDEBT'], "Current debt mismatch");
$this->assertEquals("2012-01-01 00:00:00", $row['CURDATE'], "Debt date mismatch");
$this->assertEquals("It's", $row['FIRSTNAME'], "First name mismatch");
$this->assertEquals("me", $row['LASTNAME'], "Last name mismatch");
$this->assertEquals("*****@*****.**", $row['EMAIL'], "Email mismatch");
$this->assertEquals("012345", $row['PHONE'], "Phone mismatch");
$this->assertEquals("23456", $row['PHONE2'], "Phone 2 mismatch");
$this->assertEquals("11111", $row['FAX'], "Fax mismatch");
$this->assertEquals("Address1", $row['ADDRESS'], "Address mismatch");
$this->assertEquals("Address2", $row['ADDRESS2'], "Address 2 mismatch");
$this->assertEquals("59000", $row['POSTAL'], "Zipcode mismatch");
$this->assertEquals("City", $row['CITY'], "City mismatch");
$this->assertEquals("Region", $row['REGION'], "Region mismatch");
$this->assertEquals("France", $row['COUNTRY'], "Country mismatch");
$this->assertEquals("Note", $row['NOTES'], "Note mismatch");
$this->assertEquals(true, $db->readBool($row['VISIBLE']), "Visible mismatch");
}
public function testCreate()
{
$eur = new Currency("Eur", "€", ",", ".", "#,##0.00\$", 1, true, false);
$srv = new CurrenciesService();
$eur->id = $srv->create($eur);
$this->assertNotEquals(false, $eur->id, "Creation failed");
$pdo = PDOBuilder::getPDO();
$db = DB::get();
$sql = "SELECT * FROM CURRENCIES";
$stmt = $pdo->prepare($sql);
$this->assertNotEquals($stmt->execute(), false, "Query failed");
$row = $stmt->fetch();
$this->assertNotEquals(false, $row, "Nothing found");
$this->assertEquals($eur->id, $row['ID'], "Id mismatch");
$this->assertEquals($eur->label, $row['NAME'], "Label mismatch");
$this->assertEquals($eur->symbol, $row['SYMBOL'], "Symbol mismatch");
$this->assertEquals($eur->decimalSeparator, $row['DECIMALSEP'], "Decimal separator mismatch");
$this->assertEquals($eur->thousandsSeparator, $row['THOUSANDSSEP'], "Thousands separator mismatch");
$this->assertEquals($eur->format, $row['FORMAT'], "Format mismatch");
$this->assertEquals($eur->rate, $row['RATE'], "Rate mismatch");
$this->assertEquals($eur->isMain, $db->readBool($row['MAIN']), "Main mismatch");
$this->assertEquals($eur->isActive, $db->readBool($row['ACTIVE']), "Active mismatch");
}
/** Get PDO from the loaded database core module */
public static function getPDO($uid = null)
{
// Set uid to logged user
if ($uid === null) {
$uid = get_user_id();
}
// Return cached pdo if same uid
if (PDOBuilder::$pdo !== null && $uid === PDOBuilder::$pdoUid) {
return PDOBuilder::$pdo;
}
$dsn = null;
switch (get_db_type($uid)) {
case 'mysql':
$dsn = "mysql:dbname=" . get_db_name($uid) . ";host=" . get_db_host($uid) . ";port=" . get_db_port($uid);
$options = array(\PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES \'UTF8\'');
$attributes = array(\PDO::ATTR_CASE => \PDO::CASE_UPPER);
break;
case 'postgresql':
$dsn = "pgsql:dbname=" . get_db_name($uid) . ";host=" . get_db_host($uid) . ";port=" . get_db_port($uid);
$options = array();
$attributes = array(\PDO::ATTR_CASE => \PDO::CASE_UPPER);
break;
default:
die("Config error");
}
try {
PDOBuilder::$pdo = new \PDO($dsn, get_db_user($uid), get_db_password($uid), $options);
foreach ($attributes as $key => $value) {
PDOBuilder::$pdo->setAttribute($key, $value);
}
PDOBuilder::$pdoUid = $uid;
return PDOBuilder::$pdo;
} catch (\PDOException $e) {
die("Connexion error " . $e);
}
}
/** Update user password
* @param $userId user id
* @param $oldPassword current password. Ignored if the user does not have
* a password
* @param $newPassword the clear new password. It will be encrypted.
*/
public function updatePassword($userId, $oldPassword, $newPassword)
{
$pdo = PDOBuilder::getPDO();
$user = $this->get($userId);
if ($user === null) {
return false;
}
// Check old password if any
if ($user->password !== null) {
if (!$this->authenticate($userId, $oldPassword)) {
return false;
}
}
// Update
$hash = "sha1:" . sha1($newPassword);
$stmt = $pdo->prepare("UPDATE PEOPLE SET APPPASSWORD = :pwd " . "WHERE ID = :id");
$stmt->bindParam(":id", $userId);
$stmt->bindParam(":pwd", $hash);
if ($stmt->execute()) {
return true;
} else {
return false;
}
}
public function __construct($mergedReport, $values)
{
$this->report = $mergedReport;
$this->values = $values;
$pdo = PDOBuilder::getPDO();
$this->stmt = $pdo->prepare($mergedReport->getSql());
foreach ($mergedReport->getParams() as $param) {
$id = $param["param"];
$val = null;
if (isset($this->values[$id])) {
$val = $this->values[$id];
} else {
if (isset($param['default'])) {
$val = $param['default'];
} else {
// this is an error
}
}
$db = DB::get();
switch ($param["type"]) {
case DB::DATE:
$this->stmt->bindValue(":" . $id, $db->dateVal($val));
break;
case DB::BOOL:
$this->stmt->bindValue(":" . $id, $db->boolVal($val));
break;
default:
$this->stmt->bindValue(":" . $id, $val);
break;
}
}
$this->substmts = array();
foreach ($mergedReport->getSubsqls() as $sql) {
$substmt = $pdo->prepare($sql);
foreach ($mergedReport->getParams() as $param) {
$id = $param['param'];
if (isset($this->values[$id])) {
$val = $this->values[$id];
} else {
if (isset($param['default'])) {
$val = $param['default'];
} else {
// This is an error
}
}
$db = DB::get();
switch ($param["type"]) {
case DB::DATE:
$substmt->bindValue(":" . $id, $db->dateVal($val));
break;
case DB::BOOL:
$substmt->bindValue(":" . $id, $db->boolVal($val));
break;
default:
$substmt->bindValue(":" . $id, $val);
break;
}
}
$this->substmts[] = $substmt;
}
$this->groupRowCount = 0;
$this->totalRowCount = 0;
$this->tmpTotals = array();
$this->totals = array();
$this->subtotals = array();
$this->lastValues = array();
$this->currentGroup = NULL;
$this->stmt->execute();
// Check for results
if (!$this->stmt->fetch()) {
$this->empty = TRUE;
}
$this->stmt->closeCursor();
$this->stmt->execute();
// Make a first run of substatements to get new fields
for ($i = 0; $i < count($this->substmts); $i++) {
$substmt = $this->substmts[$i];
$substmt->execute();
while ($row = $substmt->fetch(\PDO::FETCH_ASSOC)) {
$this->report->addMergedField($i, $row["__KEY__"]);
}
// Reopen
$substmt->closeCursor();
$substmt->execute();
}
$this->resetTmpSubtotals();
foreach ($this->report->getTotals() as $field => $type) {
$this->tmpTotals[$field] = 0;
}
}
/** @depends testCreateSharedTicket */
public function testCreateSharedTicketWithLines()
{
$tkt = SharedTicket::__build("1", "Label", $this->customer->id, 1, $this->area->id, $this->discountProfile->id, 13.37);
$lines = $this->createSharedTicketLines($tkt->id);
$this->assertTrue(TicketsService::createSharedTicket($tkt, $lines), "Creation failed");
$pdo = PDOBuilder::getPDO();
$db = DB::get();
$stmt = $pdo->prepare("SELECT * FROM SHAREDTICKETS");
$this->assertNotEquals(false, $stmt->execute());
$row = $stmt->fetch();
$this->assertNotEquals(false, $row, "Nothing found");
$this->assertEquals($tkt->id, $row['ID'], "Id mismatch");
$this->assertEquals($tkt->label, $row['NAME'], "Label mismatch");
$this->assertEquals($tkt->customerId, $row['CUSTOMER_ID'], "Customer id mismatch");
$this->assertEquals($tkt->tariffAreaId, $row['TARIFFAREA_ID'], "Tariff area id mismatch");
$this->assertEquals($tkt->discountProfileId, $row['DISCOUNTPROFILE_ID'], "Discount profile id mismatch");
$this->assertEquals($tkt->discountRate, $row['DISCOUNTRATE'], "Discount rate mismatch");
}
/** @depends testCreate */
public function testCreateMoveBuy()
{
$level = new StockLevel($this->products[0]->id, $this->locations[0]->id, null, 1.2, 15.6, 10);
$level->id = StocksService::createLevel($level);
$move = new StockMove(stdtimefstr("2014-01-01 00:00:00"), StockMove::REASON_IN_BUY, $level->productId, $level->locationId, $level->attrSetInstId, 3.5, 10);
$move->id = StocksService::addMove($move);
$this->assertNotEquals(false, $move->id, "Creation failed");
$pdo = PDOBuilder::getPDO();
$stmt = $pdo->prepare("SELECT * FROM STOCKCURRENT");
$this->assertNotEquals(false, $stmt->execute(), "Query failed");
if ($row = $stmt->fetch()) {
$this->assertEquals($move->locationId, $row['LOCATION'], "Location id mismatch");
$this->assertEquals($move->productId, $row['PRODUCT'], "Product id mismatch");
$this->assertEquals($move->attrSetInstId, $row['ATTRIBUTESETINSTANCE_ID'], "Attribute set instance id mismatch");
$this->assertEquals($move->qty, $row['UNITS'], "Quantity mismatch");
} else {
$this->assertTrue(false, "Nothing found after creation");
}
}
static function deleteCat($id)
{
$pdo = PDOBuilder::getPDO();
$stmt = $pdo->prepare('DELETE FROM CATEGORIES WHERE ID = :id');
return $stmt->execute(array(':id' => $id));
}
static function getVersion($id = "pasteque")
{
$pdo = PDOBuilder::getPDO();
$sql = "SELECT VERSION FROM APPLICATIONS WHERE ID = :id";
$stmt = $pdo->prepare($sql);
$stmt->bindValue(":id", $id);
$stmt->execute();
$data = $stmt->fetch();
if ($data !== false) {
return (int) $data['VERSION'];
} else {
return null;
}
}
protected function tearDown()
{
// Restore database in its empty state
$pdo = PDOBuilder::getPDO();
if ($pdo->exec("DELETE FROM PAYMENTS") === false || $pdo->exec("DELETE FROM SHAREDTICKETLINES") === false || $pdo->exec("DELETE FROM SHAREDTICKETS") === false || $pdo->exec("DELETE FROM TAXLINES") === false || $pdo->exec("DELETE FROM TICKETLINES") === false || $pdo->exec("DELETE FROM TICKETS") === false || $pdo->exec("DELETE FROM RECEIPTS") === false || $pdo->exec("DELETE FROM CLOSEDCASH") === false || $pdo->exec("DELETE FROM CASHREGISTERS") === false || $pdo->exec("DELETE FROM TARIFFAREAS_PROD") === false || $pdo->exec("DELETE FROM TARIFFAREAS") === false || $pdo->exec("DELETE FROM STOCKDIARY") === false || $pdo->exec("DELETE FROM STOCKCURRENT") === false || $pdo->exec("DELETE FROM STOCKLEVEL") === false || $pdo->exec("DELETE FROM LOCATIONS") === false || $pdo->exec("DELETE FROM PRODUCTS_CAT") === false || $pdo->exec("DELETE FROM PRODUCTS") === false || $pdo->exec("DELETE FROM CATEGORIES") === false || $pdo->exec("DELETE FROM ATTRIBUTEINSTANCE") === false || $pdo->exec("DELETE FROM ATTRIBUTESETINSTANCE") === false || $pdo->exec("DELETE FROM ATTRIBUTEUSE") === false || $pdo->exec("DELETE FROM ATTRIBUTESET") === false || $pdo->exec("DELETE FROM ATTRIBUTEVALUE") === false || $pdo->exec("DELETE FROM ATTRIBUTE") === false || $pdo->exec("DELETE FROM TAXES") === false || $pdo->exec("DELETE FROM TAXCATEGORIES") === false || $pdo->exec("DELETE FROM PEOPLE") === false || $pdo->exec("DELETE FROM CURRENCIES") === false || $pdo->exec("DELETE FROM CUSTOMERS") === false || $pdo->exec("DELETE FROM DISCOUNTPROFILES") === false) {
echo "[ERROR] Unable to restore db\n";
}
}
static function getImage($id)
{
$pdo = PDOBuilder::getPDO();
$db = DB::get();
$stmt = $pdo->prepare("SELECT IMAGE FROM PAYMENTMODES WHERE ID = :id");
$stmt->bindParam(":id", $id);
if ($stmt->execute()) {
if ($row = $stmt->fetch()) {
return $db->readBin($row['IMAGE']);
}
}
return null;
}
static function delete($id)
{
$pdo = PDOBuilder::getPDO();
$db = DB::get();
// Delete references to the product
$stmtcat = $pdo->prepare("DELETE FROM PRODUCTS_CAT WHERE PRODUCT = :id");
$stmtcat->execute(array(":id" => $id));
$stmtstk = $pdo->prepare("DELETE FROM STOCKLEVEL WHERE PRODUCT = :id");
$stmtstk->execute(array(":id" => $id));
$stmtstk2 = $pdo->prepare("DELETE FROM STOCKCURRENT WHERE PRODUCT = :id");
$stmtstk2->execute(array(":id" => $id));
$stmtta = $pdo->prepare("DELETE FROM TARIFFAREAS_PROD " . "WHERE PRODUCTID = :id");
$stmtta->execute(array(":id" => $id));
$stmtcmp = $pdo->prepare("DELETE FROM SUBGROUPS_PROD " . "WHERE PRODUCT = :id");
$stmtcmp->execute(array(":id" => $id));
// Update reference with garbage to break unicity constraint
$garbage = "_deleted_" . \md5(\time());
$stmt = $pdo->prepare("UPDATE PRODUCTS SET DELETED = " . $db->true() . ", REFERENCE = " . $db->concat("REFERENCE", ":garbage") . ", " . "NAME = " . $db->concat("NAME", ":garbage") . " WHERE ID = :id");
$stmt->bindParam(':id', $id);
$stmt->bindParam(':garbage', $garbage);
if ($stmt->execute()) {
return true;
} else {
return false;
}
}
/** @depends testUpdate */
public function testUpdateKeepImg()
{
$category = new Category(null, "Test", true, 1);
$category2 = new Category(null, "Parent", null, 0);
$id = CategoriesService::createCat($category, 0xaa);
$id2 = CategoriesService::createCat($category2);
$category->id = $id;
$category->label = "Updated";
$category->image = "";
$category->dispOrder = 3;
$category->parentId = $id2;
$ret = CategoriesService::updateCat($category);
$this->assertNotEquals(false, $ret, "Update failed");
$pdo = PDOBuilder::getPDO();
$db = DB::get();
$sql = "SELECT * FROM CATEGORIES WHERE ID = :id";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(":id", $id);
$this->assertNotEquals(false, $stmt->execute(), "Query failed");
$row = $stmt->fetch();
$this->assertEquals($id, $row['ID'], "Id was modified");
$this->assertEquals("Updated", $row['NAME'], "Label update failed");
$this->assertEquals($id2, $row['PARENTID'], "Parent id update failed");
$this->assertEquals(0xaa, $db->readBin($row['IMAGE']), "Image keeping failed");
$this->assertEquals(3, $row['DISPORDER'], "Display order update failed");
}
static function updateSharedTicket($ticket, $lines)
{
if ($ticket->id === null) {
return false;
}
$pdo = PDOBuilder::getPDO();
$newTransaction = !$pdo->inTransaction();
if ($newTransaction) {
$pdo->beginTransaction();
}
$stmt = $pdo->prepare("UPDATE SHAREDTICKETS SET NAME = :label, " . " CUSTOMER_ID = :customerId, " . " CUSTCOUNT = :custCount, " . " TARIFFAREA_ID = :tariffAreaId, " . " DISCOUNTPROFILE_ID = :discountProfileId, " . " DISCOUNTRATE = :discountRate " . " WHERE ID = :id");
$stmt->bindParam(":id", $ticket->id);
$stmt->bindParam(":label", $ticket->label);
$stmt->bindParam(":customerId", $ticket->customerId);
$stmt->bindParam(":custCount", $ticket->custCount);
$stmt->bindParam(":tariffAreaId", $ticket->tariffAreaId);
$stmt->bindParam(":discountProfileId", $ticket->discountProfileId);
$stmt->bindParam(":discountRate", $ticket->discountRate);
if ($stmt->execute() === false) {
if ($newTransaction) {
$pdo->rollback();
}
return false;
}
// Update lines
$ok = TicketsService::manageSharedTicketLines($ticket->id, $lines);
if ($ok === false) {
if ($newTransaction) {
$pdo->rollback();
}
return false;
}
if ($newTransaction) {
$pdo->commit();
}
return true;
}
protected function proceed()
{
switch ($this->action) {
case 'getShared':
$tkt = TicketsService::getSharedTicket($this->params['id']);
$this->succeed($tkt);
break;
case 'getAllShared':
$tkts = TicketsService::getAllSharedTickets();
$this->succeed($tkts);
break;
case 'delShared':
$this->succeed(TicketsService::deleteSharedTicket($this->params['id']));
break;
case 'share':
$json = json_decode($this->params['ticket']);
$ticket = SharedTicket::__build($json->id, $json->label, $json->customerId, $json->custCount, $json->tariffAreaId, $json->discountProfileId, $json->discountRate);
$lines = array();
foreach ($json->lines as $jsLine) {
// Get line info
$tktLine = new SharedTicketLines($ticket->id, $jsLine->dispOrder, $jsLine->productId, $jsLine->taxId, $jsLine->quantity, $jsLine->discountRate, $jsLine->price, $jsLine->attributes);
$lines[] = $tktLine;
}
if (TicketsService::createSharedTicket($ticket, $lines) === false) {
$this->succeed(TicketsService::updateSharedTicket($ticket, $lines));
} else {
$this->succeed(true);
}
break;
case 'getOpen':
$this->succeed(TicketsService::getOpen());
break;
case 'search':
$this->succeed(TicketsService::search($this->getParam("ticketId"), $this->getParam("ticketType"), $this->getParam("cashId"), $this->getParam("dateStart"), $this->getParam("dateStop"), $this->getParam("customerId"), $this->getParam("userId"), $this->getParam("limit")));
break;
case 'delete':
if (!TicketsService::delete($this->getParam("id"))) {
$this->fail(APIError::$ERR_GENERIC);
} else {
$this->succeed(true);
}
break;
case 'save':
// Receive ticket data as json
// Convert single ticket to array for consistency
if (isset($this->params['tickets'])) {
$json = json_decode($this->params['tickets']);
} else {
$json = array(json_decode($this->params['ticket']));
}
// Get location from cash register
$cashId = $this->params['cashId'];
$cashSrv = new CashesService();
$cash = $cashSrv->get($cashId);
if ($cash === null) {
$this->fail(new APIError("Unknown cash session"));
break;
}
$cashRegSrv = new CashRegistersService();
$cashReg = $cashRegSrv->get($cash->cashRegisterId);
if ($cashReg === null) {
$this->fail(new APIError("Cash register not found"));
break;
}
$locationId = $cashReg->locationId;
if ($locationId === null) {
$this->fail(new APIError("Location not set"));
break;
}
// Register tickets
$ticketsCount = count($json);
$successes = 0;
$pdo = PDOBuilder::getPDO();
if (!$pdo->beginTransaction()) {
$this->fail(APIError::$ERR_GENERIC);
break;
}
foreach ($json as $jsonTkt) {
if ($jsonTkt === null) {
$this->fail(new APIError("Unable to decode ticket"));
break;
}
$ticketId = $jsonTkt->ticketId;
$userId = $jsonTkt->userId;
$customerId = $jsonTkt->customerId;
$date = $jsonTkt->date;
$tktType = $jsonTkt->type;
$custCount = $jsonTkt->custCount;
$tariffAreaId = $jsonTkt->tariffAreaId;
$discountRate = $jsonTkt->discountRate;
$discountProfileId = $jsonTkt->discountProfileId;
// Get lines
$lines = array();
foreach ($jsonTkt->lines as $jsLine) {
// Get line info
$number = $jsLine->dispOrder;
if (property_exists($jsLine, "productId")) {
$productId = $jsLine->productId;
} else {
$productId = null;
}
$productLabel = $jsLine->productLabel;
$quantity = $jsLine->quantity;
$price = $jsLine->price;
$taxId = $jsLine->taxId;
$lineDiscountRate = $jsLine->discountRate;
if ($jsLine->attributes !== null) {
$jsAttr = $jsLine->attributes;
$attrSetId = $jsAttr->attributeSetId;
$values = $jsAttr->values;
$desc = "";
foreach ($values as $val) {
$desc .= $val->value . ", ";
}
$desc = substr($desc, 0, -2);
$attrs = new AttributeSetInstance($attrSetId, $desc);
foreach ($values as $val) {
$attrVal = new AttributeInstance(null, $val->id, $val->value);
$attrs->addAttrInst($attrVal);
}
$attrsId = TicketsService::createAttrSetInst($attrs);
if ($attrsId === false) {
$this->fail(new APIError("Unknown attributes"));
break;
}
} else {
$attrsId = null;
}
$product = ProductsService::get($productId);
if ($product === null) {
$product = new Product($productId, $productId, $productId, null, null, null, $taxId, true, false);
}
$tax = TaxesService::getTax($taxId);
if ($tax == null) {
$this->fail(new APIError("Unknown tax"));
break;
}
$newLine = new TicketLine($number, $product, $attrsId, $quantity, $price, $tax, $lineDiscountRate, $productLabel);
$lines[] = $newLine;
}
if (count($lines) != count($jsonTkt->lines)) {
break;
}
// Get payments
$payments = array();
foreach ($jsonTkt->payments as $jspay) {
$type = $jspay->type;
$amount = $jspay->amount;
if (!property_exists($jspay, "currencyId") || $jspay->currencyId === null) {
$currSrv = new CurrenciesService();
$currencyId = $currSrv->getDefault()->id;
$currencyAmount = $amount;
} else {
$currencyId = $jspay->currencyId;
$currencyAmount = $jspay->currencyAmount;
}
$backType = null;
$backAmount = null;
if (property_exists($jspay, "back") && $jspay->back !== null) {
$backType = $jspay->back->type;
$backAmount = $jspay->back->amount;
}
$payment = new Payment($type, $amount, $currencyId, $currencyAmount, $backType, $backAmount);
$payments[] = $payment;
}
$ticket = new Ticket($tktType, $userId, $date, $lines, $payments, $cashId, $customerId, $custCount, $tariffAreaId, $discountRate, $discountProfileId);
$ticket->ticketId = $ticketId;
if (isset($jsonTkt->id)) {
// Ticket edit
$ticket->id = $jsonTkt->id;
//Check if cash is still opened
$oldTicket = TicketsService::get($id);
if ($oldTicket != null) {
$cashSrv = new CashesService();
$cash = $cashSrv->get($oldTicket->cashId);
if ($cash->isClosed()) {
$this->fail(new APIError("Cannot edit a ticket from " . "a closed cash"));
break;
}
// Delete the old ticket and recreate
if (TicketsService::delete($oldTicket->id) && TicketsService::save($ticket, $locationId)) {
$successes++;
} else {
$this->fail(new APIError("Unable to edit ticket"));
break;
}
}
} else {
// New ticket
if (TicketsService::save($ticket)) {
$successes++;
} else {
$this->fail(new APIError("Unable to save ticket"));
break;
}
}
}
// Check if all tickets were saved, if not rollback and error
$ret = $successes == $ticketsCount;
if ($ret === true) {
if ($pdo->commit()) {
$ticketId++;
$cashRegSrv->setNextTicketId($ticketId, $cash->cashRegisterId);
$this->succeed(array("saved" => $ticketsCount));
} else {
$this->fail(APIError::$ERR_GENERIC);
}
} else {
$pdo->rollback();
if ($this->result === null) {
$this->fail(APIError::$ERR_GENERIC);
}
}
break;
}
}
