public function onDispatch(MvcEvent $e)
{
if (!$e->getRequest() instanceof ConsoleRequest) {
throw new RuntimeException('You can only use this action from a console!');
}
$table = new Ddl\CreateTable('queue_messages');
$table->addColumn(new Ddl\Column\Integer('id', false, null, ['autoincrement' => true]));
$table->addColumn(new Ddl\Column\Varchar('queue_name', 100));
$table->addColumn(new Ddl\Column\Integer('status', false));
$table->addColumn(new Ddl\Column\Varchar('options', 250));
$table->addColumn(new Ddl\Column\Text('message', null, true));
$table->addColumn(new Ddl\Column\Text('output', null, true));
$table->addColumn(new Ddl\Column\Datetime('started_dt', true));
$table->addColumn(new Ddl\Column\Datetime('finished_dt', true));
$table->addColumn(new Ddl\Column\Datetime('created_dt', false));
$table->addColumn(new Ddl\Column\Datetime('updated_dt', true));
$table->addConstraint(new Ddl\Constraint\PrimaryKey('id'));
$sql = new Sql($this->dbAdapter);
try {
$this->dbAdapter->query($sql->buildSqlString($table), DbAdapter::QUERY_MODE_EXECUTE);
} catch (\Exception $e) {
// currently there are no db-independent way to check if table exists
// so we assume that table exists when we catch exception
}
}
function login($data, $adapter)
{
$sql = new Sql($adapter);
$select = $sql->select()->from('users')->where(array('email' => $data['email'], 'password' => md5($data['password'])))->limit(1);
$result = $adapter->query($sql->buildSqlString($select), $adapter::QUERY_MODE_EXECUTE);
return $result;
}
private function create(SqlInterface $table)
{
try {
$sql = new Sql($this->dbAdapter);
$this->dbAdapter->query($sql->buildSqlString($table), Adapter::QUERY_MODE_EXECUTE);
} catch (PDOException $e) {
$message = $e->getMessage() . PHP_EOL;
}
}
function loginByAdapter($data, $adapter)
{
$sql = new Sql($adapter);
$select = $sql->select();
$select->from('users');
$select->where(array('email' => $data->email, 'pass_word' => md5($data->password)));
$selectString = $sql->buildSqlString($select);
$results = $adapter->query($selectString, $adapter::QUERY_MODE_EXECUTE);
return $results;
}
public function count($search = null)
{
$adapter = $this->getDbAdapter();
$sql = new Sql($adapter);
$select = $sql->select();
$select->columns(array('cnt' => new \Zend\Db\Sql\Expression('COUNT(*)')))->from($this->tableName);
if ($search) {
$select->where("header like '%{$search}%'");
}
$selectString = $sql->buildSqlString($select);
return (int) $adapter->query($selectString, $adapter::QUERY_MODE_EXECUTE)->current()['cnt'];
}
public function getTShirtSizeInchListAvailable()
{
$sql = new Sql($this->table_gateway->getAdapter());
$select = new Select('tshirt_size');
$select->columns(array('size_inch'));
$adapter = $this->table_gateway->getAdapter();
$query = $adapter->query($sql->buildSqlString($select), $adapter::QUERY_MODE_EXECUTE);
$holder = array();
foreach ($query as $key => $value) {
$holder[$key] = $value;
}
return $holder;
}
private function createTableLanguages()
{
$table = new Ddl\CreateTable('languages');
$id = new Column\Integer('id');
$id->setOption('autoincrement', true);
$table->addColumn($id);
$table->addColumn(new Column\Varchar('code', 2));
$table->addColumn(new Column\Varchar('locale', 6));
$table->addColumn(new Column\Varchar('name', 50));
$table->addColumn(new Column\Integer('default', false, 0));
$table->addConstraint(new Constraint\PrimaryKey('id'));
$table->addConstraint(new Constraint\UniqueKey('code'));
$table->addConstraint(new Constraint\UniqueKey('locale'));
$sql = new Sql($this->dbAdapter);
$this->dbAdapter->query($sql->buildSqlString($table), Adapter::QUERY_MODE_EXECUTE);
}
public function onDispatch(MvcEvent $e)
{
if (!$e->getRequest() instanceof ConsoleRequest) {
throw new RuntimeException('You can only use this action from a console!');
}
$table = new Ddl\CreateTable(Version::TABLE_NAME);
$table->addColumn(new Ddl\Column\Integer('id', false, null, ['autoincrement' => true]));
$table->addColumn(new Ddl\Column\BigInteger('version'));
$table->addConstraint(new Ddl\Constraint\PrimaryKey('id'));
$table->addConstraint(new Ddl\Constraint\UniqueKey('version'));
$sql = new Sql($this->dbAdapter);
try {
$this->dbAdapter->query($sql->buildSqlString($table), DbAdapter::QUERY_MODE_EXECUTE);
} catch (\Exception $e) {
// currently there are no db-independent way to check if table exists
// so we assume that table exists when we catch exception
}
}
private function createTablePages()
{
$table = new Ddl\CreateTable('pages');
$id = new Column\Integer('id');
$id->setOption('AUTO_INCREMENT', 1);
$table->addColumn($id);
$table->addColumn(new Column\Varchar('title', 255));
$table->addColumn(new Column\Text('body'));
$table->addColumn(new Column\Datetime('dt_created'));
$table->addColumn(new Column\Datetime('dt_updated', true));
$table->addConstraint(new Constraint\PrimaryKey('id'));
$sql = new Sql($this->dbAdapter);
try {
$this->dbAdapter->query($sql->buildSqlString($table), Adapter::QUERY_MODE_EXECUTE);
} catch (PDOException $e) {
return $e->getMessage() . PHP_EOL;
}
}
protected function _insertInto($table, array $data, $columns = null)
{
if (!$data) {
return;
}
if ($columns === null) {
$columns = $this->_metadata->getColumnNames($table);
}
$insertStmt = new InsertMultiple($table);
if ($columns !== null) {
$insertStmt->columns($columns);
}
foreach ($data as $row) {
$insertStmt->values($row, InsertMultiple::VALUES_MERGE);
}
$sql = $this->_sql->buildSqlString($insertStmt);
$this->_db->query($sql, Adapter::QUERY_MODE_EXECUTE);
}
public function onDispatch(MvcEvent $e)
{
if (!$e->getRequest() instanceof ConsoleRequest) {
throw new RuntimeException('You can only use this action from a console!');
}
$table = new Ddl\CreateTable('profiler');
$table->addColumn(new Ddl\Column\Integer('id', false, null, ['autoincrement' => true]));
$table->addColumn(new Ddl\Column\Varchar('method', 4));
$table->addColumn(new Ddl\Column\Varchar('uri', 500));
$table->addColumn(new Ddl\Column\Integer('response_code', false, null));
$table->addColumn(new Ddl\Column\Integer('execution_time', false, null));
$table->addColumn(new Ddl\Column\Datetime('created_dt', false));
$table->addColumn(new Ddl\Column\Text('timers'));
$table->addConstraint(new Ddl\Constraint\PrimaryKey('id'));
$sql = new Sql($this->dbAdapter);
try {
$this->dbAdapter->query($sql->buildSqlString($table), DbAdapter::QUERY_MODE_EXECUTE);
} catch (\Exception $e) {
// currently there are no db-independent way to check if table exists
// so we assume that table exists when we catch exception
}
}
/**
* Extract attributes from product_description
* @return array
*/
public function extract()
{
$params = ['lang' => 'en'];
$select = $this->getProductDescSelect($params);
$sql = new Sql($this->adapter);
$string = $sql->buildSqlString($select);
$rows = $this->adapter->query($string, Adapter::QUERY_MODE_EXECUTE);
$extracted = [];
$extracted_stats = ['diameter' => 0, 'weight' => 0, 'length' => 0, 'width' => 0, 'height' => 0, 'color' => 0, 'total_extracted' => 0, 'weight_warnings' => 0, 'diameter_warnings' => 0];
foreach ($rows as $row) {
$extraction_available = false;
$product_id = $row['product_id'];
// Step 1: extract diameter from title or invoice title
// only if there's only one diameter specified (") to prevent matching 6" x 11"
if (substr_count($row['title'], '"') == 1) {
$text = str_replace(',', '.', $row['title']);
$match = preg_match_all('/(([1-3]?[0-9](\\.[1-9])?)\\ ?")/', $text, $matches);
if ($match && $matches[2][0] > 0) {
$diameter = $matches[2][0];
$extracted_stats['diameter']++;
$extraction_available = true;
// meters to inches = 1m * 39.3700787402
//$products[$product_id]['diameter'] = $diameter * 0.0254;
}
} else {
$diameter = null;
}
// Step 2: extract product weight from description
// i.e. Weight: 50 g (1.75 oz.) with cord
$text = strtolower(str_replace("\n", ' ', $row['description'] . ' ' . $row['characteristic']));
$text = str_replace(' :', ':', $text);
$text = str_replace(',', '.', $text);
if (substr_count($text, ' weight:') == 1 && substr_count($text, ' gross weight') == 0) {
if (preg_match_all('/(\\ weight:\\ ?(([0-9]+(\\.[0-9]+)?)\\ ?(kilogram|gram|kg|g)))/', $text, $matches)) {
$unit = $matches[5][0];
if (in_array($unit, ['g', 'gram'])) {
$multiplier = 1000;
} elseif (in_array($unit, ['kg', 'kilogram'])) {
$multiplier = 1;
} else {
$multiplier = 0;
// unsupported unit
}
$net_weight = $matches[3][0] / $multiplier;
$extracted_stats['weight']++;
$extraction_available = true;
}
} else {
$net_weight = null;
}
// Step 3. Extract length if exists
$text = strtolower(str_replace("\n", ' ', $row['description'] . ' ' . $row['characteristic']));
$text = str_replace(' :', ':', $text);
$text = str_replace(',', '.', $text);
if (substr_count($text, ' length:') == 1) {
$matched = preg_match_all('/(\\ length:\\ ?(([0-9]+(\\.[0-9]+)?)\\ ?(meter|millimeter|centimer|cm|mm|m)))/', $text, $matches);
if ($matched) {
$unit = $matches[5][0];
if (in_array($unit, ['centimeter', 'cm'])) {
$multiplier = 100;
} elseif (in_array($unit, ['mm', 'millimeter'])) {
$multiplier = 1000;
} elseif (in_array($unit, ['m', 'meter'])) {
$multiplier = 1;
} else {
$multiplier = 0;
// unsupported unit
}
$length = $matches[3][0] / $multiplier;
$extracted_stats['length']++;
$extraction_available = true;
}
} else {
$length = null;
}
// Step 4: extract dimensions only if length
// is not provided. For example the cables
if (!$length) {
$text = strtolower(str_replace("\n", ' ', $row['description'] . ' ' . $row['characteristic']));
$text = str_replace(' :', ':', $text);
$text = str_replace('*', 'x', $text);
$text = str_replace(',', '.', $text);
$text = str_replace(' x ', 'x', $text);
$matched = preg_match_all('/([0-9]+(\\.[0-9]+)?)x([0-9]+(\\.[0-9]+)?)(x([0-9]+(\\.[0-9]+)?))?\\ ?(meter|millimeter|centimer|cm|mm|m)/', $text, $matches);
if ($matched == 1) {
$unit = $matches[8][0];
if (in_array($unit, ['centimeter', 'cm'])) {
$multiplier = 100;
} elseif (in_array($unit, ['mm', 'millimeter'])) {
$multiplier = 1000;
} elseif (in_array($unit, ['m', 'meter'])) {
$multiplier = 1;
} else {
$multiplier = 0;
// unsupported unit
}
$length = $matches[1][0] / $multiplier;
$width = $matches[3][0] / $multiplier;
if ($width > $length) {
// swap values
$tmp = $length;
$length = $width;
$width = $tmp;
}
$height = $matches[6][0] / $multiplier;
$extraction_available = true;
$extracted_stats['length']++;
$extracted_stats['width']++;
// optional
if ($height > 0) {
$extracted_stats['height']++;
} else {
$height = null;
}
} else {
$width = null;
$length = null;
$height = null;
}
} else {
$width = null;
$height = null;
}
// Step 5: Extract color
$text = strtolower(str_replace("\n", '@', $row['title'] . ' ' . str_replace('- ', "\n", $row['description']) . ' ' . str_replace('- ', "\n", $row['characteristic'])));
$text = str_replace(' :', ':', $text);
$text = strtolower($text);
//if (substr_count($text, 'color:') == 1) {
$matched = preg_match_all('/(colo(u)?r:\\ ?([^@\\(]+))/', $text, $matches);
if ($matched) {
$color = $matches[3][0];
$color = str_replace('&', ' and ', $color);
$color = str_replace('/', ' and ', $color);
$to_remove = ['highloss', 'metalic', 'transparent', 'high-loss', 'semigloss', 'semigloss', 'metallic', 'shiny', 'finish', 'sparkle', 'wood', 'wave', 'gloss', 'semi-', 'sonic', 'gothic', 'high', 'cosmic', 'fiesta', '"'];
foreach ($to_remove as $word) {
$color = str_replace($word, '', $color);
}
$color = str_replace('-', ' ', $color);
$color = preg_replace('/\\ +/', ' ', $color);
$color = trim($color);
if (!preg_match('~\\b(with|and|loss|mat|matt|tone)\\b~i', $color)) {
$extraction_available = true;
$extracted_stats['color']++;
} else {
$color = null;
}
} else {
$color = null;
}
//}
//
// END of various extractions
//
if ($extraction_available) {
$extracted_stats['total_extracted']++;
$warnings = [];
$diff_bbx_gross_weight = $row['weight_gross'] != '' && $net_weight !== null ? $net_weight - $row['weight_gross'] : null;
$diff_bbx_net_weight = $row['weight'] != '' && $net_weight !== null ? $net_weight - $row['weight'] : null;
if ($diff_bbx_gross_weight > 0) {
$warnings[] = "net({$net_weight})>gross(" . number_format($row['weight_gross'], 2) . ")";
$extracted_stats['weight_warnings']++;
$net_weight = null;
} elseif ($diff_bbx_gross_weight === 0) {
$warnings[] = "net=gross";
$extracted_stats['weight_warnings']++;
$net_weight = null;
}
$extracted[] = ['product_id' => $product_id, 'product_reference' => $row['reference'], 'diameter' => $diameter, 'net_weight' => $net_weight, 'length' => $length, 'width' => $width, 'height' => $height, 'primary-color' => $color, 'warnings' => implode(',', $warnings)];
}
}
return ['data' => $extracted, 'stats' => [['attribute' => 'total_products', 'total' => count($rows), 'warnings' => ''], ['attribute' => 'total_extracted', 'total' => $extracted_stats['total_extracted'], 'warnings' => ''], ['attribute' => 'net weight (kg)', 'total' => $extracted_stats['weight'], 'warnings' => $extracted_stats['weight_warnings']], ['attribute' => 'diameter (inches)', 'total' => $extracted_stats['diameter'], 'warnings' => $extracted_stats['diameter_warnings']], ['attribute' => 'length (m)', 'total' => $extracted_stats['length'], 'warnings' => ''], ['attribute' => 'width (m)', 'total' => $extracted_stats['width'], 'warnings' => ''], ['attribute' => 'height (m)', 'total' => $extracted_stats['height'], 'warnings' => ''], ['attribute' => 'primary-color', 'total' => $extracted_stats['color'], 'warnings' => '']]];
}
/**
* Creates table by its name and config
*
* @param $tableName
* @param $tableConfig
* @return Adapter\Driver\StatementInterface|\Zend\Db\ResultSet\ResultSet
* @throws RestException
*/
protected function create($tableName, $tableConfig = null)
{
$tableConfig = is_null($tableConfig) ? $tableConfig = $tableName : $tableConfig;
$tableConfigArray = $this->getTableConfig($tableConfig);
$table = new CreateTable($tableName);
$alterTable = new AlterTable($tableName);
$table->addConstraint(new Constraint\PrimaryKey('id'));
foreach ($tableConfigArray as $fieldName => $fieldData) {
$fieldType = $fieldData[self::FIELD_TYPE];
$fieldParams = $this->getFieldParams($fieldData, $fieldType);
array_unshift($fieldParams, $fieldName);
$fieldClass = '\\Zend\\Db\\Sql\\Ddl\\Column\\' . $fieldType;
$reflectionObject = new \ReflectionClass($fieldClass);
$fieldInstance = $reflectionObject->newInstanceArgs($fieldParams);
// it' like new class($callParamsArray[1], $callParamsArray[2]...)
$table->addColumn($fieldInstance);
if (isset($fieldData[self::UNIQUE_KEY])) {
$uniqueKeyConstraintName = $fieldData[self::UNIQUE_KEY] === true ? 'UniqueKey_' . $tableName . '_' . $fieldName : $fieldData[self::UNIQUE_KEY];
$uniqueKeyInstance = new UniqueKey([$fieldName], $uniqueKeyConstraintName);
$alterTable->addConstraint($uniqueKeyInstance);
}
if (isset($fieldData[self::FOREIGN_KEY])) {
$foreignKeyConstraintName = !isset($fieldData[self::FOREIGN_KEY]['name']) ? 'ForeignKey_' . $tableName . '_' . $fieldName : $fieldData[self::FOREIGN_KEY]['name'];
$onDeleteRule = isset($fieldData[self::FOREIGN_KEY]['onDeleteRule']) ? $fieldData[self::FOREIGN_KEY]['onDeleteRule'] : null;
$onUpdateRule = isset($fieldData[self::FOREIGN_KEY]['onUpdateRule']) ? $fieldData[self::FOREIGN_KEY]['onUpdateRule'] : null;
$foreignKeyInstance = new Constraint\ForeignKey($foreignKeyConstraintName, [$fieldName], $fieldData[self::FOREIGN_KEY]['referenceTable'], $fieldData[self::FOREIGN_KEY]['referenceColumn'], $onDeleteRule, $onUpdateRule, $foreignKeyConstraintName);
$alterTable->addConstraint($foreignKeyInstance);
}
}
// this is simpler version, not MySQL only, but without options[] support
//$mySqlPlatformSql = new Sql\Platform\Mysql\Mysql();
//$sql = new Sql\Sql($this->db, null, $mySqlPlatformSql);
//$sqlString = $sql->buildSqlString($table);
$ctdMysql = new Sql\Platform\Mysql\Ddl\CreateTableDecorator();
$mySqlPlatformDbAdapter = new Adapter\Platform\Mysql();
$mySqlPlatformDbAdapter->setDriver($this->db->getDriver());
$sqlStringCreate = $ctdMysql->setSubject($table)->getSqlString($mySqlPlatformDbAdapter);
$mySqlPlatformSql = new Sql\Platform\Mysql\Mysql();
$sql = new Sql\Sql($this->db, null, $mySqlPlatformSql);
$sqlStringAlter = $sql->buildSqlString($alterTable);
$sqlString = $sqlStringCreate . ';' . PHP_EOL . $sqlStringAlter . ';';
return $this->db->query($sqlString, \Zend\Db\Adapter\Adapter::QUERY_MODE_EXECUTE);
}
/**
* Create Schema
*
* @return AdapterInterface
*/
public function createSchema()
{
$ddl = new CreateTable($this->tableName);
$ddl->addColumn(new Varchar('version', 255));
$sql = new Sql($this->adapter);
$this->adapter->query($sql->buildSqlString($ddl), Adapter::QUERY_MODE_EXECUTE);
return $this;
}
/**
* Execute sql
*
* @param Sql $sql Sql
* @param SqlInterface $sqlObject Sql object
*
* @return \Zend\Db\Adapter\Driver\StatementInterface|\Zend\Db\ResultSet\ResultSet
*/
protected function _executeSql(Sql $sql, SqlInterface $sqlObject)
{
$sqlString = $sql->buildSqlString($sqlObject);
return $this->_adapter->query($sqlString, Adapter::QUERY_MODE_EXECUTE);
}
public function demoSqlAction()
{
// see http://framework.zend.com/manual/current/en/modules/zend.db.sql.html
$sql = new Sql($this->localAdapter);
$select = $sql->select();
// @return Zend\Db\Sql\Select
$select->from('test');
// see the Select API http://framework.zend.com/manual/current/en/modules/zend.db.sql.html#zend-db-sql-select
$select->where(array('id' => 2));
//Either prepare
$statement = $sql->prepareStatementForSqlObject($select);
$results = $statement->execute();
foreach ($results as $row) {
Debug::dump($row);
}
// Or execute
$sqlString = $sql->buildSqlString($select);
print_r($sqlString);
$results = $this->localAdapter->query($sqlString, Adapter::QUERY_MODE_EXECUTE);
foreach ($results as $row) {
Debug::dump($row);
}
// TODO more examples with UPDATE, INSERT, DELETE
// TODO more examples with PREDICATES http://framework.zend.com/manual/current/en/modules/zend.db.sql.html#zend-db-sql-where-zend-db-sql-having
// TODO more examples with Ddl http://framework.zend.com/manual/current/en/modules/zend.db.sql.ddl.html
exit(0);
}
protected function forgetMigration(MigrationInterface $migration)
{
$name = $migration->getName();
$sql = new Sql($this->adapter);
$delete = $sql->delete($this->migrationTable)->where(['name' => $name]);
$this->adapter->query($sql->buildSqlString($delete), Adapter::QUERY_MODE_EXECUTE);
$this->availableMigrations[$name] = $this->installedMigrations[$name];
unset($this->installedMigrations[$name]);
}
