Ayasoftware_SQLupdate - Version 1.0

Version Notes

Extension tested on Magento CE 1.3.X.Y , 1.4.X.Y and Magento Enterprise Edition (1.8.0.0).

Download this release

Release Info

Developer Magento Core Team
Extension Ayasoftware_SQLupdate
Version 1.0
Comparing to
See all releases


Version 1.0

Files changed (11) hide show
  1. app/code/community/Ayasoftware/SQLupdate/Block/System/Html.php +45 -0
  2. app/code/community/Ayasoftware/SQLupdate/Helper/Data.php +13 -0
  3. app/code/community/Ayasoftware/SQLupdate/Model/DBHelper.php +155 -0
  4. app/code/community/Ayasoftware/SQLupdate/Model/Massimport.php +729 -0
  5. app/code/community/Ayasoftware/SQLupdate/Model/Observer.php +35 -0
  6. app/code/community/Ayasoftware/SQLupdate/controllers/Adminhtml/SqlupdateController.php +26 -0
  7. app/code/community/Ayasoftware/SQLupdate/etc/config.xml +98 -0
  8. app/code/community/Ayasoftware/SQLupdate/etc/system.xml +89 -0
  9. app/design/adminhtml/default/default/layout/sqlupdate.xml +14 -0
  10. app/design/adminhtml/default/default/template/sqlupdate/schedule.phtml +43 -0
  11. package.xml +34 -0
app/code/community/Ayasoftware/SQLupdate/Block/System/Html.php ADDED
@@ -0,0 +1,45 @@
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
1
+ <?php
2
+ /**
3
+ * WDCA
4
+ *
5
+ * NOTICE OF LICENSE
6
+ *
7
+ * This source file is subject to the Open Software License (OSL 3.0)
8
+ * that is bundled with this package in the file LICENSE.txt.
9
+ * It is also available through the world-wide-web at this URL:
10
+ * http://opensource.org/licenses/osl-3.0.php
11
+ * If you did not receive a copy of the license and are unable to
12
+ * obtain it through the world-wide-web, please send an email
13
+ * to license@magentocommerce.com so we can send you a copy immediately.
14
+ *
15
+ * DISCLAIMER
16
+ *
17
+ * Do not edit or add to this file if you wish to upgrade Magento to newer
18
+ * versions in the future. If you wish to customize Magento for your
19
+ * needs please refer to http://www.magentocommerce.com for more information.
20
+ *
21
+ * @category Ayasoftware
22
+ * @package Ayasoftware_SQLupdate
23
+ * @copyright Copyright (c) 2008-2010 Ayasoftware (http://www.ayasoftware.com)
24
+ * @license Commercial
25
+ */
26
+
27
+
28
+ class Ayasoftware_SQLupdate_Block_System_Html extends Mage_Adminhtml_Block_System_Config_Form_Fieldset
29
+ {
30
+ protected $_dummyElement;
31
+ protected $_fieldRenderer;
32
+ protected $_values;
33
+
34
+ public function render(Varien_Data_Form_Element_Abstract $element)
35
+ {
36
+
37
+ $html = "";
38
+ $html .= "<div style=\" margin-bottom: 12px; width: 430px;\">".
39
+ "This extension was provided by Ayasoftware.com. <a href='http://www.ayasoftware.com/' target='_blank'>Click here</a>.<br /> ".
40
+ "Report bugs to support@ayasoftware.com".
41
+ $html .= "" ;
42
+
43
+ return $html;
44
+ }
45
+ }
app/code/community/Ayasoftware/SQLupdate/Helper/Data.php ADDED
@@ -0,0 +1,13 @@
 
 
 
 
 
 
 
 
 
 
 
 
 
1
+ <?php
2
+ /**
3
+ *
4
+ *
5
+ * @category Ayasoftware
6
+ * @package Ayasoftware_SQLupdate
7
+ * @author EL Hassan Matar <support@ayasoftware.com>
8
+ */
9
+ class Ayasoftware_SQLupdate_Helper_Data extends Mage_Core_Helper_Abstract
10
+ {
11
+
12
+
13
+ }
app/code/community/Ayasoftware/SQLupdate/Model/DBHelper.php ADDED
@@ -0,0 +1,155 @@
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
1
+ <?php
2
+ /**
3
+ * @category Ayasoftware
4
+ * @package Ayasoftware_SQLupdate
5
+ * @author EL Hassan Matar <support@ayasoftware.com>
6
+ * website: www.ayasoftware.com/
7
+ */
8
+ class Ayasoftware_SQLupdate_Model_DBHelper {
9
+ protected $_db;
10
+ protected $_debug;
11
+
12
+ /**
13
+ * Intializes database connection
14
+ * @param string $host : hostname
15
+ * @param string $dbname : database name
16
+ * @param string $user : username
17
+ * @param string $pass : password
18
+ * @param bool $debug : debug mode
19
+ */
20
+ public function initDb($host,$dbname,$user,$pass,$debug=false)
21
+ {
22
+ //intialize connection with PDO
23
+ $this->_db=new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);
24
+ //use exception error mode
25
+ $this->_db->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
26
+ //use fetch assoc as default fetch mode
27
+ $this->_db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE,PDO::FETCH_ASSOC);
28
+ //set database debug mode to trace if necessary
29
+ $this->_debug=$debug;
30
+ if($this->_debug)
31
+ {
32
+ $this->_db->query("SET GLOBAL general_log='ON' ")->execute();
33
+ }
34
+ }
35
+
36
+ /**
37
+ * releases database connection
38
+ */
39
+ public function exitDb()
40
+ {
41
+ //unset database debug mode to trace if necessary
42
+ if($this->_debug)
43
+ {
44
+ $this->_db->query("SET GLOBAL general_log='OFF' ")->execute();
45
+ }
46
+ //clear PDO resource
47
+ $this->_db=NULL;
48
+
49
+ }
50
+
51
+ /**
52
+ * executes an sql statement
53
+ * @param string $sql : sql statement (may include ? placeholders)
54
+ * @param array $params : parameters to replace placeholders (can be null)
55
+ * @return PDOStatement : statement for further processing if needed
56
+ */
57
+ public function exec_stmt($sql,$params=null)
58
+ {
59
+ $stmt=$this->_db->prepare($sql);
60
+ if($params!=null)
61
+ {
62
+ $params=is_array($params)?$params:array($params);
63
+ $stmt->execute($params);
64
+ }
65
+ else
66
+ {
67
+ $stmt->execute();
68
+ }
69
+ return $stmt;
70
+ }
71
+
72
+ /**
73
+ * Perform a delete statement, sql should be "DELETE"
74
+ * @param string $sql : DELETE statement sql (placeholders allowed)
75
+ * @param array $params : placeholder replacements (can be null)
76
+ */
77
+ public function delete($sql,$params=null)
78
+ {
79
+ $this->exec_stmt($sql,$params);
80
+ }
81
+
82
+ /**
83
+ * Perform an insert , sql should be "INSERT"
84
+ * @param string $sql :INSERT statement SQL (placeholders allowed)
85
+ * @param array $params : placeholder replacements (can be null)
86
+ * @return mixed : last inserted id
87
+ */
88
+ public function insert($sql,$params=null)
89
+ {
90
+ $this->exec_stmt($sql,$params);
91
+ $liid=$this->_db->lastInsertId();
92
+ return $liid;
93
+ }
94
+
95
+ /**
96
+ * Perform a select ,sql should be "SELECT"
97
+ * @param string $sql :SELECT statement SQL (placeholders allowed)
98
+ * @param array $params : placeholder replacements (can be null)
99
+ * @return PDOStatement : statement instance for further processing
100
+ */
101
+ public function select($sql,$params=null)
102
+ {
103
+ return $this->exec_stmt($sql,$params);
104
+ }
105
+
106
+ /**
107
+ * Selects one unique value from one single row
108
+ * @param $sql : SELECT statement SQL (placeholders allowed)
109
+ * @param $params :placeholder replacements (can be null)
110
+ * @param $col : column value to retrieve
111
+ * @return mixed : null if not result , wanted column value if match
112
+ */
113
+ public function selectone($sql,$params,$col)
114
+ {
115
+ $r=$this->select($sql,$params)->fetch();
116
+ $v=(is_array($r)?$r[$col]:null);
117
+ return $v;
118
+ }
119
+
120
+ /**
121
+ * test if value exists (test should be compatible with unique select)
122
+ * @param $sql : SELECT statement SQL (placeholders allowed)
123
+ * @param $params :placeholder replacements (can be null)
124
+ * @param $col : column value to retrieve
125
+ * @return boolean : true if value found, false otherwise
126
+ */
127
+ public function testexists($sql,$params,$col)
128
+ {
129
+ return $this->selectone($sql,$params,$col)!=null;
130
+ }
131
+
132
+ /**
133
+ * begins a transaction
134
+ */
135
+ public function beginTransaction()
136
+ {
137
+ $this->_db->beginTransaction();
138
+ }
139
+
140
+ /**
141
+ * commits the current transaction
142
+ */
143
+ public function commitTransaction()
144
+ {
145
+ $this->_db->commit();
146
+ }
147
+
148
+ /**
149
+ * rollback the current transaction
150
+ */
151
+ public function rollbackTransaction()
152
+ {
153
+ $this->_db->rollBack();
154
+ }
155
+ }
app/code/community/Ayasoftware/SQLupdate/Model/Massimport.php ADDED
@@ -0,0 +1,729 @@
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
1
+ <?php
2
+ /**
3
+ * @category Ayasoftware
4
+ * @package Ayasoftware_SQLupdate
5
+ * @author EL Hassan Matar <support@ayasoftware.com>
6
+ * website: www.ayasoftware.com/
7
+ */
8
+ class Ayasoftware_SQLupdate_Model_Massimport extends Ayasoftware_SQLupdate_Model_DBHelper {
9
+
10
+ protected $_props;
11
+ public $attrinfo = array ();
12
+ public $attrbytype = array ();
13
+ public $website_ids = array ();
14
+ public $attribute_sets = array ();
15
+ public $reset = false;
16
+ public $magdir;
17
+ public $imgsourcedir;
18
+ public $tprefix;
19
+ public $logfilename;
20
+
21
+ /**
22
+ * retrieve property value with default if not found
23
+ * @param string $secname section name
24
+ * @param string $pname property name
25
+ * @param string $default default value if not found (null if not set)
26
+ * @return string value if found or default if not found
27
+ */
28
+ public function getProp($secname, $pname, $default = null) {
29
+ if (isset ( $this->_props [$secname] ) && isset ( $this->_props [$secname] [$pname] )) {
30
+ return $this->_props [$secname] [$pname];
31
+ } else {
32
+ return $default;
33
+ }
34
+ }
35
+
36
+ /**
37
+ * constructor
38
+ * @param string $conffile : configuration .ini filename
39
+ */
40
+ public function __construct() {
41
+ $this->loadProperties ();
42
+ }
43
+ /**
44
+ * load properties
45
+ * @param string $conf : configuration .ini filename
46
+ */
47
+ public function loadProperties() {
48
+ $xmlPath = Mage::getBaseDir () . DS . 'app/etc/local.xml';
49
+ $xmlObj = new Varien_Simplexml_Config ( $xmlPath );
50
+ $this->magdir = Mage::getBaseDir ();
51
+ if (Mage::getStoreConfig ( 'sqlupdate/sqlupdateSettings/imgsourcedir' )) {
52
+ $this->imgsourcedir = Mage::getBaseDir () . Mage::getStoreConfig ( 'sqlupdate/sqlupdateSettings/imgsourcedir' );
53
+ } else {
54
+ $this->imgsourcedir = Mage::getBaseDir () . DS . "media/import";
55
+ }
56
+ $this->tprefix = $xmlObj->getNode ( "global/resources/db/table_prefix" );
57
+ $this->logfilename = Mage::getBaseDir () . DS . "var/log/" . Mage::getStoreConfig ( 'sqlupdate/sqlupdateSettings/logfilename' );
58
+ }
59
+
60
+ /**
61
+ * Initialize Connection with Magento Database
62
+ */
63
+ public function connectToMagento() {
64
+ #get database infos from local.xml
65
+ $xmlPath = Mage::getBaseDir () . DS . 'app/etc/local.xml';
66
+ $xmlObj = new Varien_Simplexml_Config ( $xmlPath );
67
+ $host = $xmlObj->getNode ( "global/resources/default_setup/connection/host" );
68
+ $dbname = $xmlObj->getNode ( "global/resources/default_setup/connection/dbname" );
69
+ $user = $xmlObj->getNode ( "global/resources/default_setup/connection/username" );
70
+ $pass = $xmlObj->getNode ( "global/resources/default_setup/connection/password" );
71
+ $debug = Mage::getStoreConfig ( 'sqlupdate/sqlupdateSettings/debugenabled' );
72
+ $this->initDb ( $host, $dbname, $user, $pass, $debug );
73
+ }
74
+
75
+ /*
76
+ * Disconnect Magento db
77
+ */
78
+ public function disconnectFromMagento() {
79
+ $this->exitDb ();
80
+ }
81
+
82
+ /**
83
+ * Initialize websites list
84
+ */
85
+ public function initWebsites() {
86
+
87
+ //Get all websites code/ids
88
+ $tname = $this->tablename ( "core_website" );
89
+ $sql = "SELECT code,website_id FROM $tname";
90
+ $stmt = $this->select ( $sql );
91
+ while ( $r = $stmt->fetch () ) {
92
+ $this->website_ids [$r ["code"]] = $r ["website_id"];
93
+ }
94
+ }
95
+
96
+ /**
97
+ * returns prefixed table name
98
+ * @param string $magname : magento base table name
99
+ */
100
+ public function tablename($magname) {
101
+ return $this->tprefix != "" ? $this->tprefix . "$magname" : $magname;
102
+ }
103
+
104
+ /**
105
+ * Initialize attribute infos to be used during import
106
+ * @param array $cols : array of attribute names
107
+ */
108
+ public function initAttrInfos($cols) {
109
+ //create statement parameter string ?,?,?.....
110
+ $qcolstr = substr ( str_repeat ( "?,", count ( $cols ) ), 0, - 1 );
111
+ $tname = $this->tablename ( "eav_attribute" );
112
+ //SQL for selecting attribute properties for all wanted attributes
113
+ $sql = "SELECT `$tname`.* FROM `$tname`
114
+ WHERE ($tname.attribute_code IN ($qcolstr)) AND (entity_type_id=4)";
115
+ $stmt = $this->select ( $sql, $cols );
116
+ //create an attribute code based array for the wanted columns
117
+ while ( $r = $stmt->fetch () ) {
118
+ $this->attrinfo [$r ["attribute_code"]] = $r;
119
+ }
120
+ //create a backend_type based array for the wanted columns
121
+ //this will greatly help for optimizing inserts when creating attributes
122
+ //since eav_ model for attributes has one table per backend type
123
+ foreach ( $this->attrinfo as $k => $a ) {
124
+ $bt = $a ["backend_type"];
125
+ if (! isset ( $this->attrbytype [$bt] )) {
126
+ $this->attrbytype [$bt] = array ("data" => array () );
127
+
128
+ }
129
+ $this->attrbytype [$bt] ["data"] [] = $a;
130
+ }
131
+ //now add a fast index in the attrbytype array to store id list in a comma separated form
132
+ foreach ( $this->attrbytype as $bt => $test ) {
133
+ $idlist = array ();
134
+ foreach ( $test ["data"] as $it ) {
135
+ $idlist [] = $it ["attribute_id"];
136
+ }
137
+ $this->attrbytype [$bt] ["ids"] = implode ( ",", $idlist );
138
+ }
139
+
140
+ /*now we have 2 index arrays
141
+ 1. $this->attrinfo which has the following structure:
142
+ key : attribute_code
143
+ value : attribute_properties
144
+ 2. $this->attrbytype which has the following structure:
145
+ key : attribute backend type
146
+ value : array of :
147
+ data => array of attribute_properties ,one for each attribute that match
148
+ the backend type
149
+ ids => list of attribute ids of the backend type */
150
+ }
151
+
152
+ /**
153
+ * retrieves attribute set id for a given attribute set name
154
+ * @param string $asname : attribute set name
155
+ */
156
+ public function getAttributeSetId($asname) {
157
+ $tname = $this->tablename ( "eav_attribute_set" );
158
+ return $this->selectone ( "SELECT attribute_set_id FROM $tname WHERE attribute_set_name=?", $asname, 'attribute_set_id' );
159
+ }
160
+
161
+ /**
162
+ * Retrieves product id for a given sku
163
+ * @param string $sku : sku of product to get id for
164
+ */
165
+ public function getProductId($sku) {
166
+ $tname = $this->tablename ( "catalog_product_entity" );
167
+ return $this->selectone ( "SELECT entity_id FROM $tname WHERE sku=?", $sku, 'entity_id' );
168
+ }
169
+
170
+ /**
171
+ * creates a product in magento database
172
+ * @param array $item: product attributes as array with key:attribute name,value:attribute value
173
+ * @param int $asid : attribute set id for values
174
+ * @return : product id for newly created product
175
+ */
176
+ public function createProduct($item, $asid) {
177
+ $tname = $this->tablename ( 'catalog_product_entity' );
178
+ $values = array ($item ['type'], $asid, $item ['sku'], 4, null );
179
+ $sql = "INSERT INTO `$tname`
180
+ (`type_id`,
181
+ `attribute_set_id`,
182
+ `sku`,
183
+ `entity_type_id`,
184
+ `entity_id`
185
+ )
186
+ VALUES ( ?,?,?,?,?)";
187
+ $lastid = $this->insert ( $sql, $values );
188
+ return $lastid;
189
+ }
190
+
191
+ /**
192
+ * Get Option id for select attributes based on value
193
+ * @param int $attid : attribute id to find option id from value
194
+ * @param mixed $optval : value to get option id for
195
+ * @return : null if not found or option id corresponding to attribute value
196
+ */
197
+ function getOptionIdFromValue($attid, $optval) {
198
+ $t1 = $this->tablename ( 'eav_attribute_option_value' );
199
+ $t2 = $this->tablename ( 'eav_attribute_option' );
200
+ $sql = "SELECT optval.option_id FROM $t1 AS optval
201
+ JOIN $t2 AS opt ON optval.option_id=opt.option_id AND opt.attribute_id=?
202
+ WHERE optval.value=?";
203
+ return $this->selectone ( $sql, array ($attid, $optval ), 'option_id' );
204
+ }
205
+
206
+ /**
207
+ * Creates a new option value for an attribute
208
+ * @param int $attid : attribute id for create new value for
209
+ * @param mixed $optval : new option value to add
210
+ * @return : option id for new created value
211
+ */
212
+ function createOptionValue($attid, $optval) {
213
+ $t = $this->tablename ( 'eav_attribute_option' );
214
+ $optid = $this->insert ( "INSERT INTO $t (attribute_id) VALUES (?)", $attid );
215
+ $t = $this->tablename ( 'eav_attribute_option_value' );
216
+ $this->insert ( "INSERT INTO $t (option_id,value) VALUES (?,?)", array ($optid, $optval ) );
217
+ return $optid;
218
+ }
219
+
220
+ /**
221
+ * returns option id for a given select attribute value
222
+ * creates new option value if does not already exists
223
+ * @param int $attid : attribute to get option id for
224
+ * @param mixed $value : value to get option id for
225
+ *
226
+ */
227
+ public function getOptionId($attid, $value) {
228
+ $optid = $this->getOptionIdFromValue ( $attid, $value );
229
+ if ($optid == null) {
230
+ $optid = $this->createOptionValue ( $attid, $value );
231
+ }
232
+ return $optid;
233
+ }
234
+
235
+ /**
236
+ * returns tax class id for a given tax class value
237
+ * @param $tcvalue : tax class value
238
+ */
239
+ public function getTaxClassId($tcvalue) {
240
+ $t = $this->tablename ( 'tax_class' );
241
+ return $this->selectone ( "SELECT class_id FROM $t WHERE class_name=?", $tcvalue, "class_id" );
242
+ }
243
+
244
+ /**
245
+ * imageInGallery
246
+ * @param int $pid : product id to test image existence in gallery
247
+ * @param string $imgname : image file name (relative to /products/media in magento dir)
248
+ * @return bool : if image is already present in gallery for a given product id
249
+ */
250
+ public function imageInGallery($pid, $imgname) {
251
+ $t = $this->tablename ( 'catalog_product_entity_media_gallery' );
252
+ return $this->testexists ( "SELECT value_id FROM $t
253
+ WHERE value = ? AND entity_id=?", array ($imgname, $pid ), 'value_id' );
254
+ }
255
+
256
+ /**
257
+ * reset product gallery
258
+ * @param int $pid : product id
259
+ */
260
+ public function resetGallery($pid) {
261
+ $tgv = $this->tablename ( 'catalog_product_entity_media_gallery_value' );
262
+ $tg = $this->tablename ( 'catalog_product_entity_media_gallery' );
263
+ $sql = "DELETE emgv,emg FROM `$tgv` as emgv JOIN `$tg` AS emg ON emgv.value_id = emg.value_id AND emg.entity_id =?";
264
+ $this->delete ( $sql, $pid );
265
+
266
+ }
267
+ /**
268
+ * adds an image to product image gallery only if not already exists
269
+ * @param int $pid : product id to test image existence in gallery
270
+ * @param string $imgname : image file name (relative to /products/media in magento dir)
271
+ */
272
+ public function addImageToGallery($pid, $imgname) {
273
+ if ($this->imageInGallery ( $pid, $imgname )) {
274
+ return;
275
+ }
276
+ $tg = $this->tablename ( 'catalog_product_entity_media_gallery' );
277
+
278
+ // insert image in media_gallery
279
+ $sql = "INSERT INTO $tg
280
+ (attribute_id,entity_id,value)
281
+ VALUES
282
+ (?,?,?)";
283
+
284
+ //77 is the id for media_gallery attribute
285
+ $vid = $this->insert ( $sql, array (77, $pid, $imgname ) );
286
+
287
+ $tgv = $this->tablename ( 'catalog_product_entity_media_gallery_value' );
288
+ #get maximum current position in the product gallery
289
+ $sql = "SELECT MAX( position ) as maxpos
290
+ FROM $tgv AS emgv
291
+ JOIN $tg AS emg ON emg.value_id = emgv.value_id AND emg.entity_id = ?
292
+ GROUP BY emg.entity_id";
293
+ $pos = $this->selectone ( $sql, array ($pid ), 'maxpos' );
294
+ $pos = ($pos == null ? 0 : $pos + 1);
295
+ #insert new value
296
+ $sql = "INSERT INTO $tgv
297
+ (value_id,position)
298
+ VALUES(?,?)";
299
+ $this->insert ( $sql, array ($vid, $pos ) );
300
+ }
301
+
302
+ /**
303
+ * copy image file from source directory to
304
+ * product media directory
305
+ * @param $imgfile : name of image file name in source directory
306
+ * @return : name of image file name relative to magento catalog media dir,including leading
307
+ * directories made of first char & second char of image file name.
308
+ */
309
+ public function copyImageFile($imgfile) {
310
+ $magdir = $this->magdir;
311
+ $srcdir = $this->imgsourcedir;
312
+ $fname = $srcdir . $imgfile;
313
+ $i1 = $imgfile [1];
314
+ $i2 = $imgfile [2];
315
+ $l1d = "$magdir/media/catalog/product/$i1";
316
+ $l2d = "$l1d/$i2";
317
+ /* test if 1st level product media dir exists , create it if not */
318
+ if (! file_exists ( "$l1d" )) {
319
+ mkdir ( $l1d );
320
+ }
321
+ /* test if 2nd level product media dir exists , create it if not */
322
+ if (! file_exists ( "$l2d" )) {
323
+ mkdir ( $l2d );
324
+ }
325
+ /* test if image already exists ,if not copy from source to media dir*/
326
+ if (! file_exists ( "$l2d$imgfile" )) {
327
+ copy ( $fname, "$l2d$imgfile" );
328
+ }
329
+ /* return image file name relative to media dir (with leading / ) */
330
+ return "/$i1/$i2$imgfile";
331
+ }
332
+
333
+ /**
334
+ * attribute handler for int typed attributes
335
+ * @param int $pid : product id
336
+ * @param int $ivalue : initial value of attribute
337
+ * @param array $attrdesc : attribute description
338
+ * @return mixed : false if no further processing is needed,
339
+ * int (magento value) for the int attribute otherwise
340
+ */
341
+ public function handleIntAttribute($pid, $ivalue, $attrdesc) {
342
+ $ovalue = $ivalue;
343
+ $attid = $attrdesc ["attribute_id"];
344
+ //if we've got a select type value
345
+ if ($attrdesc ["frontend_input"] == "select") {
346
+ //we need to identify its type since some have no options
347
+ switch ($attrdesc ["source_model"]) {
348
+ //if its status, make it available
349
+ case "catalog/product_status" :
350
+ $ovalue = 1;
351
+ break;
352
+ //if it's tax_class, get tax class id from item value
353
+ case "tax/class_source_product" :
354
+ $ovalue = $this->getTaxClassId ( $ivalue );
355
+ break;
356
+ //if it's visibility ,set it to catalog/search
357
+ case "catalog/product_visibility" :
358
+ $ovalue = 4;
359
+ break;
360
+ //otherwise, standard option behavior
361
+ //get option id for value, create it if does not already exist
362
+ default :
363
+ $ovalue = $this->getOptionId ( $attid, $ivalue );
364
+ break;
365
+ }
366
+ }
367
+ return $ovalue;
368
+ }
369
+
370
+ /**
371
+ * attribute handler for varchar based attributes
372
+ * @param int $pid : product id
373
+ * @param string $ivalue : attribute value
374
+ * @param array $attrdesc : attribute description
375
+ */
376
+ public function handleVarcharAttribute($pid, $ivalue, $attrdesc) {
377
+
378
+ $ovalue = $ivalue;
379
+ //if it's an image attribute (image,small_image or thumbnail)
380
+ if ($attrdesc ["frontend_input"] == "media_image") {
381
+ //do nothing if empty
382
+ if ($ivalue == "") {
383
+ return false;
384
+ }
385
+ //else copy image file
386
+ $imagefile = $this->copyImageFile ( $ivalue );
387
+ //return value
388
+ $ovalue = $imagefile;
389
+ //do not insert in gallery !!!!!
390
+ }
391
+ //if it's a gallery
392
+ if ($attrdesc ["frontend_input"] == "gallery") {
393
+ //do nothing if empty
394
+ if ($ivalue == "") {
395
+ return false;
396
+ }
397
+ //use ";" as image separator
398
+ $images = explode ( ";", $ivalue );
399
+ $imgnames = array ();
400
+ //for each image
401
+ $this->resetGallery ( $pid );
402
+ foreach ( $images as $imagefile ) {
403
+ //copy it from source dir to product media dir
404
+ $imagefile = $this->copyImageFile ( $imagefile );
405
+ //add to gallery
406
+ $this->addImageToGallery ( $pid, $imagefile );
407
+ }
408
+ //we don't want to insert after that
409
+ $ovalue = false;
410
+ }
411
+ return $ovalue;
412
+ }
413
+
414
+ /**
415
+ * Create product attribute from values for a given product id
416
+ * @param $pid : product id to create attribute values for
417
+ * @param $item : attribute values in an array indexed by attribute_code
418
+ */
419
+ public function createAttributes($pid, $item) {
420
+ /*
421
+ * if we did not wipe all products , delete attribute entries for current product
422
+ */
423
+ if (! $this->reset) {
424
+ $tcsi = $this->tablename ( 'cataloginventory_stock_item' );
425
+ $tcss = $this->tablename ( 'cataloginventory_stock_status' );
426
+ $sqls = array ("DELETE FROM `$tcsi` WHERE product_id=?", "DELETE FROM `$tcss` WHERE product_id=?" );
427
+
428
+ foreach ( $sqls as $sql ) {
429
+ $this->delete ( $sql, $pid );
430
+ }
431
+ }
432
+
433
+ /* now is the interesring part */
434
+ /* iterate on attribute backend type index */
435
+ foreach ( $this->attrbytype as $tp => $a ) {
436
+ /* for static types, do not insert into attribute tables */
437
+ if ($tp == "static") {
438
+ continue;
439
+ }
440
+
441
+ //table name for backend type data
442
+ $cpet = $this->tablename ( "catalog_product_entity_$tp" );
443
+ //data table for inserts
444
+ $data = array ();
445
+ //inserts to perform on backend type eav
446
+ $inserts = array ();
447
+
448
+ //iterate on all attribute descriptions for the given backend type
449
+ foreach ( $a ["data"] as $attrdesc ) {
450
+ //by default, we will perform an insetion
451
+ $insert = true;
452
+ //get attribute id
453
+ $attid = $attrdesc ["attribute_id"];
454
+ //get attribute value in the item to insert based on code
455
+ $ivalue = $item [$attrdesc ["attribute_code"]];
456
+ //use reflection to find special handlers
457
+ $handler = "handle" . ucfirst ( $tp ) . "Attribute";
458
+ //if we have a handler for the current type
459
+ if (in_array ( $handler, get_class_methods ( $this ) )) {
460
+ //call it and get its output value for the current attribute
461
+ $ovalue = $this->$handler ( $pid, $ivalue, $attrdesc );
462
+ } else //if not, use value
463
+ {
464
+ $ovalue = $ivalue;
465
+ }
466
+
467
+ if ($ovalue !== false) {
468
+ $inserts [] = "(?,?,?,?,?)";
469
+ //entity type id = product (4)
470
+ $data [] = 4;
471
+ $data [] = $attid;
472
+ //store_id = 0
473
+ $data [] = 0;
474
+ $data [] = $pid;
475
+ $data [] = $ovalue;
476
+ }
477
+ }
478
+ //now perform insert for all values of the the current backend type in one
479
+ //single insert
480
+ $sql = "INSERT INTO $cpet
481
+ (`entity_type_id`, `attribute_id`, `store_id`, `entity_id`, `value`)
482
+ VALUES ";
483
+ $sql .= implode ( ",", $inserts );
484
+ //this one taken from mysql log analysis of magento import
485
+ //smart one :)
486
+ $sql .= " ON DUPLICATE KEY UPDATE `value`=VALUES(`value`)";
487
+ $this->insert ( $sql, $data );
488
+ }
489
+ }
490
+
491
+ /**
492
+ * Clear all products from catalog
493
+ */
494
+ public function clearProducts() {
495
+ $sql = "SET FOREIGN_KEY_CHECKS = 0;\n";
496
+ $tables = array ("catalog_product_bundle_option", "catalog_product_bundle_option_value", "catalog_product_bundle_selection", "catalog_product_entity_datetime", "catalog_product_entity_decimal", "catalog_product_entity_gallery", "catalog_product_entity_int", "catalog_product_entity_media_gallery", "catalog_product_entity_media_gallery_value", "catalog_product_entity_text", "catalog_product_entity_tier_price", "catalog_product_entity_varchar", "catalog_product_link", "catalog_product_link_attribute", "catalog_product_link_attribute_decimal", "catalog_product_link_attribute_int", "catalog_product_link_attribute_varchar", "catalog_product_link_type", "catalog_product_option", "catalog_product_option_price", "catalog_product_option_title", "catalog_product_option_type_price", "catalog_product_option_type_title", "catalog_product_option_type_value", "catalog_product_super_attribute_label", "catalog_product_super_attribute_pricing", "catalog_product_super_attribute", "catalog_product_super_link", "catalog_product_enabled_index", "catalog_product_website", "catalog_category_product_index", "catalog_category_product", "cataloginventory_stock_item", "mag_cataloginventory_stock_status", "mag_cataloginventory_stock" );
497
+
498
+ foreach ( $tables as $table ) {
499
+ $sql .= "TRUNCATE TABLE `" . $this->tablename ( $table ) . "`;\n";
500
+ }
501
+
502
+ $sql .= "INSERT INTO `" . $this->tablename ( "catalog_product_link_type" ) . "` (`link_type_id`,`code`) VALUES (1,'relation'),(2,'bundle'),(3,'super'),(4,'up_sell'),(5,'cross_sell');\n";
503
+ $sql .= "INSERT INTO `" . $this->tablename ( "catalog_product_link_attribute" ) . "` (`product_link_attribute_id`,`link_type_id`,`product_link_attribute_code`,`data_type`) VALUES (1,2,'qty','decimal'),(2,1,'position','int'),(3,4,'position','int'),(4,5,'position','int'),(6,1,'qty','decimal'),(7,3,'position','int'),(8,3,'qty','decimal');\n";
504
+ $sql .= "INSERT INTO `" . $this->tablename ( "cataloginventory_stock" ) . "`(`stock_id`,`stock_name`) VALUES (1,'Default');\n";
505
+ $sql .= "TRUNCATE TABLE `" . $this->tablename ( "catalog_product_entity" ) . "`;\n";
506
+ $sql .= "SET FOREIGN_KEY_CHECKS = 1;\n";
507
+ $this->_db->exec ( $sql );
508
+ }
509
+
510
+ /**
511
+ * destroy attributes for a given product id
512
+ * @param int $pid : product id
513
+ */
514
+ public function destroyAttributes($pid) {
515
+ //iterate over attributes by type index
516
+ foreach ( $this->attrbytype as $tp => $a ) {
517
+ //do nothing for static
518
+ if ($tp != "static") {
519
+ //delete all product attributes of current type
520
+ //that match collected attribute ids
521
+ $cpet = $this->tablename ( "catalog_product_entity_$tp" );
522
+ $sql = "DELETE FROM $cpet WHERE attribute_id IN (" . $a ["ids"] . ") AND entity_id=$pid AND store_id<>0";
523
+ $this->delete ( $sql );
524
+ }
525
+ }
526
+ }
527
+
528
+ /**
529
+ * update product stock
530
+ * @param int $pid : product id
531
+ * @param array $item : attribute values for product indexed by attribute_code
532
+ */
533
+ public function updateStock($pid, $item) {
534
+ $csit = $this->tablename ( "cataloginventory_stock_item" );
535
+ Mage::log ( $csit );
536
+ $sql = "INSERT INTO `$csit`
537
+ (`product_id`,
538
+ `stock_id`,
539
+ `qty`,
540
+ `is_in_stock`,
541
+ `low_stock_date`,
542
+ `stock_status_changed_automatically`)
543
+ VALUES (?,?,?,?,?,?)";
544
+ $data = array ($pid, $this->website_ids [$item ["websites"]], $item ["qty"], $item ["qty"] > 0 ? 1 : 0, null, 1 );
545
+ $this->insert ( $sql, $data );
546
+ }
547
+
548
+ /**
549
+ * assign categories for a given product id from values
550
+ * categories should already be created & csv values should be as the ones
551
+ * given in the magento export (ie: comma separated ids, minus 1,2)
552
+ * @param int $pid : product id
553
+ * @param array $item : attribute values for product indexed by attribute_code
554
+ */
555
+ public function assignCategories($pid, $item) {
556
+ $ccfst = $this->tablename ( "catalog_category_flat_store_" . $this->website_ids [$item ["websites"]] );
557
+ $catpath = "1/2/" . str_replace ( ",", "/", $item ["category_ids"] );
558
+ $sql = "SELECT entity_id FROM $ccfst
559
+ WHERE path=?";
560
+ $catid = $this->selectone ( $sql, $catpath, 'entity_id' );
561
+ $ccpt = $this->tablename ( "catalog_category_product" );
562
+ #if we did not reset products
563
+
564
+
565
+ if (! $this->reset) {
566
+ #remove category assignment of current product
567
+ $sql = "DELETE FROM $ccpt WHERE product_id=?";
568
+ $this->delete ( $sql, $pid );
569
+ }
570
+ #create new category assignment for product
571
+ $sql = "INSERT INTO $ccpt (`category_id`,`product_id`)
572
+ VALUES (?,?)";
573
+ $this->insert ( $sql, array ($catid, $pid ) );
574
+ }
575
+
576
+ public function updateIndexes() {
577
+
578
+ }
579
+
580
+ /**
581
+ * set website of product if not exists
582
+ * @param int $pid : product id
583
+ * @param array $item : attribute values for product indexed by attribute_code
584
+ */
585
+ public function updateWebSite($pid, $item) {
586
+ $cpst = $this->tablename ( "catalog_product_website" );
587
+ $sql = "SELECT website_id FROM `$cpst` WHERE product_id=?";
588
+ if (! $this->testexists ( $sql, $pid, 'website_id' )) {
589
+ $sql = " INSERT INTO `$cpst` (`product_id`, `website_id`)
590
+ VALUES (?,?)";
591
+ $this->insert ( $sql, array ($pid, $this->website_ids [$item ["websites"]] ) );
592
+ }
593
+ }
594
+
595
+ /**
596
+ * full import workflow for item
597
+ * @param array $item : attribute values for product indexed by attribute_code
598
+ */
599
+ public function importItem($item) {
600
+ //retrieve sku
601
+ $sku = $item ["sku"];
602
+ $asname = $item ["attribute_set"];
603
+ //retrieve attribute set from given name
604
+ //if not in cache, add to cache
605
+ if (! isset ( $this->attribute_sets [$asname] )) {
606
+ $asid = $this->getAttributeSetId ( $asname );
607
+ $this->attribute_sets [$asname] = $asid;
608
+ } else {
609
+ $asid = $this->attribute_sets [$asname];
610
+ }
611
+
612
+ //begin transaction
613
+ $this->beginTransaction ();
614
+ try {
615
+
616
+ //first get product id
617
+ $pid = $this->getProductId ( $sku );
618
+ //if not found, create new product
619
+ if (! isset ( $pid )) {
620
+ $pid = $this->createProduct ( $item, $asid );
621
+ }
622
+ //remove old attributes
623
+ $this->destroyAttributes ( $pid );
624
+ //create new ones
625
+ $this->createAttributes ( $pid, $item );
626
+ //assign categories
627
+ $category_ids = $item ["category_ids"];
628
+ if(isset($category_ids) ) {
629
+ $this->assignCategories ( $pid, $item );
630
+ }
631
+ //update website
632
+ $this->updateWebSite ( $pid, $item );
633
+ //update stock
634
+ $this->updateStock ( $pid, $item );
635
+ //ok,we're done
636
+ $this->commitTransaction ();
637
+ } catch ( Exception $e ) {
638
+ //if anything got wrong, rollback
639
+ $this->rollbackTransaction ();
640
+ }
641
+ }
642
+
643
+ /**
644
+ * main import function
645
+ * @param string $csvfile : csv file name to import
646
+ * @param bool $reset : destroy all products before import
647
+ */
648
+ public function import($csvfile, $reset = false) {
649
+ $stimer = explode( ' ', microtime() );
650
+ $stimer = $stimer[1] + $stimer[0];
651
+ if ( file_exists( $this->logfilename ) ){
652
+ unlink( $this->logfilename );
653
+ }
654
+ $fp = fopen($this->logfilename, 'a') or die("can't open file: ".$this->logfilename);
655
+ fwrite($fp,"Update Started ". date("Y-m-d H:i:s") ."\n");
656
+ if (! isset ( $csvfile )) {
657
+ die ( "No csv file set" );
658
+ }
659
+ if (! file_exists ( $csvfile )) {
660
+ die ( "$csvfile not found" );
661
+ }
662
+ //initialize db connectivity
663
+ $this->connectToMagento ();
664
+ //store reset flag
665
+ $this->reset = $reset;
666
+ //initialize website id cache
667
+ $this->initWebSites ();
668
+ //if reset
669
+ if ($this->reset) {
670
+ //clear all products
671
+ $this->clearProducts ();
672
+ }
673
+ //open csv file
674
+ $f = fopen ( $csvfile, "r" );
675
+ //get column names
676
+ $this->cols = fgetcsv ( $f, 4096, "," );
677
+ //initialize attribute infos & indexes from column names
678
+ $this->initAttrInfos ( $this->cols );
679
+ //counter
680
+ $cnt = 0;
681
+ //start time
682
+ $tstart = microtime ( true );
683
+ //differential
684
+ $tdiff = $tstart;
685
+ //read each line
686
+ if (Mage::getStoreConfig ( 'sqlupdate/sqlupdateSettings/rowsnumber' )) {
687
+ $mstep = Mage::getStoreConfig ( 'sqlupdate/sqlupdateSettings/rowsnumber' );
688
+ } else {
689
+ $mstep = 100;
690
+ }
691
+
692
+ while ( $row = fgetcsv ( $f, 4096, ",", '"' ) ) {
693
+ //counter
694
+ $cnt ++;
695
+ try {
696
+ //create product attributes values array indexed by attribute code
697
+ $item = array_combine ( $this->cols, $row );
698
+ if (is_array ( $item )) {
699
+ //import item
700
+ $this->importItem ( $item );
701
+ //intermediary measurement
702
+ } else {
703
+ fwrite($fp,"ERROR - LINE $cnt - INVALID ROW :" . count ( $row ) . "/" . count ( $this->cols ) . " cols found\n");
704
+ }
705
+ if ($cnt % $mstep == 0) {
706
+ $tend = microtime ( true );
707
+ fwrite($fp,$cnt . " - " . ($tend - $tstart) . " - " . ($tend - $tdiff));
708
+ $tdiff = microtime ( true );
709
+ }
710
+ } catch ( Exception $e ) {
711
+ fwrite($fp,"ERROR - LINE $cnt - " . $e->getMessage (). "\n");
712
+ }
713
+
714
+ }
715
+
716
+ fclose ( $f );
717
+ $this->updateIndexes ();
718
+ $tend = microtime ( true );
719
+ fwrite($fp, $cnt . "-");
720
+ fwrite($fp, " Speed Average ".ceil ( ($cnt * 60) / ($tend - $tstart) ) . " rec/mn" . "\n");
721
+ flush ();
722
+ $etimer = explode( ' ', microtime() );
723
+ $etimer = $etimer[1] + $etimer[0];
724
+ fwrite($fp, "Update completed in ");
725
+ fwrite($fp, $etimer- $stimer. " Seconds === ".date("Y-m-d H:i:s")."\n");
726
+ @fclose($fp);
727
+ $this->disconnectFromMagento ();
728
+ }
729
+ }
app/code/community/Ayasoftware/SQLupdate/Model/Observer.php ADDED
@@ -0,0 +1,35 @@
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
1
+ <?php
2
+ /**
3
+ * @category Ayasoftware
4
+ * @package Ayasoftware_SQLupdate
5
+ * @author EL Hassan Matar <support@ayasoftware.com>
6
+ * website: www.ayasoftware.com/
7
+ */
8
+ class Ayasoftware_SQLupdate_Model_Observer extends Mage_Core_Model_Abstract
9
+ {
10
+ function __construct ()
11
+ {}
12
+ public function batchUpdate ($observer)
13
+ {
14
+ $reset = false;
15
+ $sqlimport = Mage::getModel ( 'sqlupdate/massimport' );
16
+ $updateFeed = Mage::getStoreConfig ( 'sqlupdate/sqlupdateSettings/feedname' );
17
+ $csvfile = Mage::getBaseDir() . DS . $updateFeed;
18
+ $sqlimport->import($csvfile, $reset);
19
+ }
20
+ public function changeScheduling($timescheduled) {
21
+ $timecreated = strftime ( "%Y-%m-%d %H:%M:%S", mktime ( date ( "H" ), date ( "i" ), date ( "s" ), date ( "m" ), date ( "d" ), date ( "Y" ) ) );
22
+ $jobCode = 'direct_sql_update';
23
+ try {
24
+ // Delete pending or completed cron jobs
25
+ $sql_update = Mage::getModel ( 'cron/schedule' )->getCollection ()->addFieldToFilter ( 'job_code', array ('eq' => array ($jobCode ) ) )->load ();
26
+ foreach ( $sql_update->getIterator () as $record ) {
27
+ $record->delete ();
28
+ }
29
+ $schedule = Mage::getModel ( 'cron/schedule' );
30
+ $schedule->setJobCode ( $jobCode )->setCreatedAt ( $timecreated )->setScheduledAt ( $timescheduled )->setStatus ( Mage_Cron_Model_Schedule::STATUS_PENDING )->save ();
31
+ } catch ( Exception $e ) {
32
+ throw new Exception ( Mage::helper ( 'cron' )->__ ( 'Unable to save Cron.' ) );
33
+ }
34
+ }
35
+ }
app/code/community/Ayasoftware/SQLupdate/controllers/Adminhtml/SqlupdateController.php ADDED
@@ -0,0 +1,26 @@
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
1
+ <?php
2
+ class Ayasoftware_SQLupdate_Adminhtml_SqlupdateController extends Mage_Adminhtml_Controller_Action {
3
+ public function indexAction() {
4
+ //Mage::helper('affiliatepack')->addAdminhtmlVersion('affiliatepack');
5
+ $this->loadLayout ()->renderLayout ();
6
+ }
7
+ public function postAction() {
8
+ $post = $this->getRequest ()->getPost ();
9
+ $scheduling = $post ['scheduling'];
10
+ $timecreated = date("Y-m-d H:i:s");
11
+ $scheduling = date("Y-m-d H:i:s", strtotime($scheduling));
12
+ try {
13
+ if ($timecreated > $scheduling) {
14
+ throw new Exception ( Mage::helper ( 'sqlupdate' )->__ ( 'Task must be Scheduled after '.$timecreated ) );
15
+ }
16
+ $update = Mage::getModel ( 'sqlupdate/observer' );
17
+ $update->changeScheduling ( $scheduling );
18
+ $message = $this->__ ( 'The SQL Update has been Scheduled Successfully.' );
19
+ Mage::getSingleton ( 'adminhtml/session' )->addSuccess ( $message );
20
+ } catch ( Exception $e ) {
21
+ Mage::getSingleton ( 'adminhtml/session' )->addError ( $e->getMessage () );
22
+ }
23
+ $this->_redirect ( '*/*' );
24
+ }
25
+
26
+ }
app/code/community/Ayasoftware/SQLupdate/etc/config.xml ADDED
@@ -0,0 +1,98 @@
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
1
+ <config>
2
+ <modules>
3
+ <Ayasoftware_SQLupdate>
4
+ <version>0.1.0</version>
5
+ <depends>
6
+ <!-- no dependencies -->
7
+ </depends>
8
+ </Ayasoftware_SQLupdate>
9
+ </modules>
10
+ <admin>
11
+ <routers>
12
+ <sqlupdate>
13
+ <use>admin</use>
14
+ <args>
15
+ <module>Ayasoftware_SQLupdate</module>
16
+ <frontName>sqlupdate</frontName>
17
+ </args>
18
+ </sqlupdate>
19
+ </routers>
20
+ </admin>
21
+ <crontab>
22
+ <jobs>
23
+ <direct_sql_update>
24
+ <schedule>
25
+ <cron_expr>20 17 * * *</cron_expr>
26
+ </schedule>
27
+ <run>
28
+ <model>sqlupdate/observer::batchUpdate</model>
29
+ </run>
30
+ </direct_sql_update>
31
+ </jobs>
32
+ </crontab>
33
+ <global>
34
+ <blocks>
35
+ <sqlupdate>
36
+ <class>Ayasoftware_SQLupdate_Block</class>
37
+ </sqlupdate>
38
+ </blocks>
39
+ <helpers>
40
+ <sqlupdate>
41
+ <class>Ayasoftware_SQLupdate_Helper</class>
42
+ </sqlupdate>
43
+ </helpers>
44
+
45
+ <models>
46
+ <sqlupdate>
47
+ <class>Ayasoftware_SQLupdate_Model</class>
48
+ </sqlupdate>
49
+ </models>
50
+
51
+ </global>
52
+
53
+ <adminhtml>
54
+ <acl>
55
+ <resources>
56
+ <all>
57
+ <title>Allow Everything</title>
58
+ </all>
59
+ <admin>
60
+ <children>
61
+ <system>
62
+ <children>
63
+ <config>
64
+ <children>
65
+ <sqlupdate>
66
+ <title>Ayasoftware Configuration Area</title>
67
+ </sqlupdate>
68
+ </children>
69
+ </config>
70
+ </children>
71
+ </system>
72
+ </children>
73
+ </admin>
74
+ </resources>
75
+ </acl>
76
+ <menu>
77
+ <sqlupdate module="sqlupdate">
78
+ <title>SQL Update</title>
79
+ <sort_order>72</sort_order>
80
+ <children>
81
+ <schedule module="sqlupdate">
82
+ <title>Schedule SQL Update</title>
83
+ <sort_order>0</sort_order>
84
+ <action>sqlupdate/adminhtml_sqlupdate</action>
85
+ </schedule>
86
+ </children>
87
+ </sqlupdate>
88
+ </menu>
89
+
90
+ <layout>
91
+ <updates>
92
+ <sqlupdate>
93
+ <file>sqlupdate.xml</file>
94
+ </sqlupdate>
95
+ </updates>
96
+ </layout>
97
+ </adminhtml>
98
+ </config>
app/code/community/Ayasoftware/SQLupdate/etc/system.xml ADDED
@@ -0,0 +1,89 @@
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
1
+ <config>
2
+
3
+ <tabs>
4
+ <tbtall translate="label" module="sqlupdate">
5
+ <label>Ayasoftware modules</label>
6
+ <sort_order>100</sort_order>
7
+ </tbtall>
8
+ </tabs>
9
+ <sections>
10
+ <sqlupdate translate="label" module="sqlupdate">
11
+ <label>SQL Update</label>
12
+ <tab>tbtall</tab>
13
+ <frontend_type>text</frontend_type>
14
+ <sort_order>12</sort_order>
15
+ <show_in_default>1</show_in_default>
16
+ <show_in_website>1</show_in_website>
17
+ <show_in_store>0</show_in_store>
18
+ <groups>
19
+ <about translate="label">
20
+ <label>About This Extension</label>
21
+ <sort_order>100</sort_order>
22
+ <show_in_default>1</show_in_default>
23
+ <show_in_website>1</show_in_website>
24
+ <fields>
25
+
26
+ <notice translate="label">
27
+ <label>Extension Information Provided By Ayasoftware</label>
28
+ <frontend_type>text</frontend_type>
29
+ <frontend_model>sqlupdate/system_html</frontend_model>
30
+ <sort_order>10</sort_order>
31
+ <show_in_default>1</show_in_default>
32
+ <show_in_website>1</show_in_website>
33
+ <show_in_store>1</show_in_store>
34
+ </notice>
35
+
36
+ </fields>
37
+ </about>
38
+ <sqlupdateSettings translate="label">
39
+ <label>Extension Settings</label>
40
+ <sort_order>320</sort_order>
41
+ <show_in_default>1</show_in_default>
42
+ <show_in_website>1</show_in_website>
43
+ <fields>
44
+ <feedname translate="label">
45
+ <label>Feed Name</label>
46
+ <frontend_type>text</frontend_type>
47
+ <sort_order>10</sort_order>
48
+ <show_in_default>1</show_in_default>
49
+ <show_in_website>1</show_in_website>
50
+ <comment>Located in Magento Root Directory: {{base_dir}}</comment>
51
+ </feedname>
52
+ <imgsourcedir translate="label comment">
53
+ <label>Images Directory:</label>
54
+ <frontend_type>text</frontend_type>
55
+ <sort_order>20</sort_order>
56
+ <show_in_default>1</show_in_default>
57
+ <show_in_website>1</show_in_website>
58
+ <comment>If empty we use the default: {{base_dir}}/media/import</comment>
59
+ </imgsourcedir>
60
+ <debugenabled translate="label">
61
+ <label>Enable Debug Mode</label>
62
+ <frontend_type>select</frontend_type>
63
+ <source_model>adminhtml/system_config_source_yesno</source_model>
64
+ <sort_order>25</sort_order>
65
+ <show_in_default>1</show_in_default>
66
+ <show_in_website>1</show_in_website>
67
+ </debugenabled>
68
+ <logfilename translate="label comment">
69
+ <label>Update Log File Name </label>
70
+ <frontend_type>text</frontend_type>
71
+ <sort_order>35</sort_order>
72
+ <show_in_default>1</show_in_default>
73
+ <show_in_website>1</show_in_website>
74
+ <comment>Logging from Mage::log(). File is located in {{base_dir}}/var/log</comment>
75
+ </logfilename>
76
+ <rowsnumber translate="label comment">
77
+ <label>Report every X records imported </label>
78
+ <frontend_type>text</frontend_type>
79
+ <sort_order>40</sort_order>
80
+ <show_in_default>1</show_in_default>
81
+ <show_in_website>1</show_in_website>
82
+ <comment>If empty! we report after 100 records</comment>
83
+ </rowsnumber>
84
+ </fields>
85
+ </sqlupdateSettings>
86
+ </groups>
87
+ </sqlupdate>
88
+ </sections>
89
+ </config>
app/design/adminhtml/default/default/layout/sqlupdate.xml ADDED
@@ -0,0 +1,14 @@
 
 
 
 
 
 
 
 
 
 
 
 
 
 
1
+ <?xml version="1.0"?>
2
+ <layout>
3
+ <default>
4
+ <reference name="head">
5
+ <action method="addJs"><script>ayasoftware/datetimepicker.js</script></action>
6
+ </reference>
7
+ </default>
8
+ <sqlupdate_adminhtml_sqlupdate_index>
9
+ <update handle="sqlupdate_sqlupdate_index"/>
10
+ <reference name="content">
11
+ <block type="adminhtml/template" name="feed" template="sqlupdate/schedule.phtml"/>
12
+ </reference>
13
+ </sqlupdate_adminhtml_sqlupdate_index>
14
+ </layout>
app/design/adminhtml/default/default/template/sqlupdate/schedule.phtml ADDED
@@ -0,0 +1,43 @@
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
1
+ <?php
2
+ $timecreated = $timecreated = date("Y-m-d H:i:s");
3
+ ?>
4
+ <div class="content-header">
5
+ <table cellspacing="0" class="grid-header">
6
+ <tr>
7
+ <td>
8
+ <h3><?=$this->__ ( 'Schedule SQL Update. ' )?></h3>
9
+ </td>
10
+ <td class="a-right">
11
+ <button onclick="editForm.submit()" class="scalable save"
12
+ type="button"><span>Create Scheduled Task for SQL Update.</span></button>
13
+ </td>
14
+ </tr>
15
+ </table>
16
+ </div>
17
+ <div class="entry-edit">
18
+ <form id="edit_form" name="edit_form" method="post"
19
+ action="<?=$this->getUrl ( '*/*/post' )?>">
20
+
21
+ <fieldset id="my-fieldset">
22
+ <table cellspacing="3" cellpadding="10" class="form-list">
23
+ <tr>
24
+ <td colspan="2"><input name="form_key" type="hidden"
25
+ value="<?php
26
+ echo Mage::getSingleton ( 'core/session' )->getFormKey ()?>" />
27
+ </td>
28
+ </tr>
29
+ <tr>
30
+ <td><input type="text" id="scheduling" maxlength="25" size="25"
31
+ class="required-entry input-text" name="scheduling"></td>
32
+ <td><a href="javascript:NewCal('scheduling','yyyymmdd',true,24)"><?php echo Mage::helper('sqlupdate')->__('Pick a date') ?></a>&nbsp;&nbsp;<span class="required">*</span>&nbsp;&nbsp;&nbsp;&nbsp; Server Time: <?php echo $timecreated?></td>
33
+ </tr>
34
+ </table>
35
+ </fieldset>
36
+ </form>
37
+ <h2>How to use this module</h2>
38
+ <h4 class="icon-head head-edit-form fieldset-legend"><?=$this->__ ( 'The SQL Update is by default Scheduled to be automatically run at midnight 00:00 ' )?></h4>
39
+ <h4 class="icon-head head-edit-form fieldset-legend"><?=$this->__ ( 'If you pick a new Scheduling for your SQL Update, we will replace the default (only this time) ' )?></h4>
40
+ </div>
41
+ <script type="text/javascript">
42
+ var editForm = new varienForm('edit_form');
43
+ </script>
package.xml ADDED
@@ -0,0 +1,34 @@
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
1
+ <?xml version="1.0"?>
2
+ <package>
3
+ <name>Ayasoftware_SQLupdate</name>
4
+ <version>1.0</version>
5
+ <stability>stable</stability>
6
+ <license uri="http://www.opensource.org/licenses/osl-3.0.php">Extension license name (OSL v3.0)</license>
7
+ <channel>community</channel>
8
+ <extends/>
9
+ <summary>Speedup your catalog update using our Direct SQL Update extension.</summary>
10
+ <description>Magento has made available to its users, a collection of functions that allows them to access and modify data in a very safe way. However, saving each individual product can take a long time when updating a large set of data.
11
+
12
+ To improve this, Dweeves (Thank you so much for this great work) , has created a set of functions that provide a great way to create a direct SQL query which could update 1000&#x2019;s of products in 1 or 2 seconds.
13
+ As a response to many Magento users requests, who do not know how to install it and how to use it, we have decided to create an extension with the following features:
14
+
15
+ Extension Features:
16
+
17
+ - Run updates periodically at 00:00 (Midnight) by Default
18
+ - Modify updates Scheduling by Picking a new date (see slideshow)
19
+ - Use Magento Cron features (no additional cron setup is needed)
20
+ - Easy 2-minute installation
21
+ - Easy to use
22
+ - No code changes required
23
+ - No programming Skills required
24
+ - User-friendly interface
25
+ - Provide a detailed updates report saved to a log File.
26
+ - Based on Dweeves script (Follow thread )</description>
27
+ <notes>Extension tested on Magento CE 1.3.X.Y , 1.4.X.Y and Magento Enterprise Edition (1.8.0.0).</notes>
28
+ <authors><author><name>EL Hassan Matar</name><user>auto-converted</user><email>el.hassan.matar@gmail.com</email></author></authors>
29
+ <date>2010-08-20</date>
30
+ <time>17:44:04</time>
31
+ <contents><target name="magedesign"><dir name="adminhtml"><dir name="default"><dir name="default"><dir name="layout"><file name="sqlupdate.xml" hash="77ad8d193fe5f92ea54ca471858836eb"/></dir><dir name="template"><dir name="sqlupdate"><file name="schedule.phtml" hash="6d78fccd1c0bce8ce5d4f7f1edcccfe7"/></dir></dir></dir></dir></dir></target><target name="magecommunity"><dir name="Ayasoftware"><dir name="SQLupdate"><dir name="Block"><dir name="System"><file name="Html.php" hash="9b9e6aa706535e58e86ea1260910cb17"/></dir></dir><dir name="controllers"><dir name="Adminhtml"><file name="SqlupdateController.php" hash="070eddccb01bc9a350704b35d1552586"/></dir></dir><dir name="etc"><file name="config.xml" hash="5a86288a1655ae0df5206cc1bf61bb37"/><file name="system.xml" hash="45031a024e40a09cf1906fea75a6d03f"/></dir><dir name="Helper"><file name="Data.php" hash="ecbe414b1c243fb379266dbd15047993"/></dir><dir name="Model"><file name="DBHelper.php" hash="653e298222dee22114a60de208f5f322"/><file name="Massimport.php" hash="eb91793ed980772c15258dc426d5a850"/><file name="Observer.php" hash="fcc7212083cd72920ff117f3299afcc8"/></dir></dir></dir></target></contents>
32
+ <compatible/>
33
+ <dependencies/>
34
+ </package>