PageRenderTime 41ms CodeModel.GetById 15ms RepoModel.GetById 0ms app.codeStats 1ms

/app/protected/modules/import/utils/ImportDatabaseUtil.php

https://bitbucket.org/ddonthula/zurmouser
PHP | 283 lines | 248 code | 1 blank | 34 comment | 25 complexity | 12fe12d26878435c3416dbcde504aa50 MD5 | raw file
Possible License(s): LGPL-2.1, BSD-2-Clause, GPL-2.0, GPL-3.0, BSD-3-Clause, LGPL-3.0
  1. <?php
  2. /*********************************************************************************
  3. * Zurmo is a customer relationship management program developed by
  4. * Zurmo, Inc. Copyright (C) 2012 Zurmo Inc.
  5. *
  6. * Zurmo is free software; you can redistribute it and/or modify it under
  7. * the terms of the GNU General Public License version 3 as published by the
  8. * Free Software Foundation with the addition of the following permission added
  9. * to Section 15 as permitted in Section 7(a): FOR ANY PART OF THE COVERED WORK
  10. * IN WHICH THE COPYRIGHT IS OWNED BY ZURMO, ZURMO DISCLAIMS THE WARRANTY
  11. * OF NON INFRINGEMENT OF THIRD PARTY RIGHTS.
  12. *
  13. * Zurmo is distributed in the hope that it will be useful, but WITHOUT
  14. * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
  15. * FOR A PARTICULAR PURPOSE. See the GNU General Public License for more
  16. * details.
  17. *
  18. * You should have received a copy of the GNU General Public License along with
  19. * this program; if not, see http://www.gnu.org/licenses or write to the Free
  20. * Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
  21. * 02110-1301 USA.
  22. *
  23. * You can contact Zurmo, Inc. with a mailing address at 113 McHenry Road Suite 207,
  24. * Buffalo Grove, IL 60089, USA. or at email address contact@zurmo.com.
  25. ********************************************************************************/
  26. /**
  27. * Helper class for working with import data tables.
  28. */
  29. class ImportDatabaseUtil
  30. {
  31. /**
  32. * Given a file resource, convert the file into a database table based on the table name provided.
  33. * Assumes the file is a csv.
  34. * @param resource $fileHandle
  35. * @param string $tableName
  36. * @return true on success.
  37. */
  38. public static function makeDatabaseTableByFileHandleAndTableName($fileHandle, $tableName, $delimiter = ',', // Not Coding Standard
  39. $enclosure = "'")
  40. {
  41. assert('gettype($fileHandle) == "resource"');
  42. assert('is_string($tableName)');
  43. assert('$tableName == strtolower($tableName)');
  44. assert('$delimiter != null && is_string($delimiter)');
  45. assert('$enclosure != null && is_string($enclosure)');
  46. $freezeWhenComplete = false;
  47. if (RedBeanDatabase::isFrozen())
  48. {
  49. RedBeanDatabase::unfreeze();
  50. $freezeWhenComplete = true;
  51. }
  52. R::exec("drop table if exists $tableName");
  53. $columns = self::optimizeTableImportColumnsAndGetColumnNames($fileHandle, $tableName, $delimiter, $enclosure);
  54. rewind($fileHandle);
  55. self::convertCsvIntoRowsInTable($fileHandle, $tableName, $delimiter, $enclosure, $columns);
  56. self::optimizeTableNonImportColumns($tableName);
  57. if ($freezeWhenComplete)
  58. {
  59. RedBeanDatabase::freeze();
  60. }
  61. return true;
  62. }
  63. protected static function optimizeTableImportColumnsAndGetColumnNames($fileHandle, $tableName, $delimiter, $enclosure)
  64. {
  65. assert('gettype($fileHandle) == "resource"');
  66. assert('is_string($tableName)');
  67. assert('$tableName == strtolower($tableName)');
  68. assert('$delimiter != null && is_string($delimiter)');
  69. assert('$enclosure != null && is_string($enclosure)');
  70. $maxValues = array();
  71. $columns = array();
  72. while (($data = fgetcsv($fileHandle, 0, $delimiter, $enclosure)) !== false)
  73. {
  74. if (count($data) > 1 || (count($data) == 1 && trim($data['0']) != ''))
  75. {
  76. foreach ($data as $k => $v)
  77. {
  78. if (!isset($maxValues[$k]) || strlen($maxValues[$k]) < strlen($v))
  79. {
  80. $maxValues[$k] = $v;
  81. }
  82. }
  83. }
  84. }
  85. if (count($maxValues) > 0)
  86. {
  87. $newBean = R::dispense($tableName);
  88. foreach ($maxValues as $columnId => $value)
  89. {
  90. $columnName = 'column_' . $columnId;
  91. $newBean->{$columnName} = str_repeat(' ', strlen($value));
  92. $columns[] = $columnName;
  93. }
  94. R::store($newBean);
  95. R::trash($newBean);
  96. R::wipe($tableName);
  97. }
  98. return $columns;
  99. }
  100. protected static function convertCsvIntoRowsInTable($fileHandle, $tableName, $delimiter, $enclosure, $columns)
  101. {
  102. assert('gettype($fileHandle) == "resource"');
  103. assert('is_string($tableName)');
  104. assert('$tableName == strtolower($tableName)');
  105. assert('$delimiter != null && is_string($delimiter)');
  106. assert('$enclosure != null && is_string($enclosure)');
  107. assert('is_array($columns)');
  108. $bulkQuantity = 500;
  109. $importArray = array();
  110. while (($data = fgetcsv($fileHandle, 0, $delimiter, $enclosure)) !== false)
  111. {
  112. if (count($data) > 1 || (count($data) == 1 && trim($data['0']) != ''))
  113. {
  114. foreach ($data as $k => $v)
  115. {
  116. //Convert characterser to UTF-8
  117. $currentCharset = mb_detect_encoding($v, $other_charsets = 'UTF-8, UTF-7, ASCII, CP1252, EUC-JP, SJIS, eucJP-win, SJIS-win, JIS, ISO-2022-JP');
  118. if (!empty($currentCharset) && $currentCharset != "UTF-8")
  119. {
  120. $data[$k] = mb_convert_encoding($v, "UTF-8");
  121. }
  122. }
  123. $importArray[] = $data;
  124. }
  125. if (count($importArray) > $bulkQuantity)
  126. {
  127. DatabaseCompatibilityUtil::bulkInsert($tableName, $importArray, $columns, $bulkQuantity);
  128. $importArray = array();
  129. }
  130. }
  131. if (count($importArray) > $bulkQuantity)
  132. {
  133. throw new NotSupportedException();
  134. }
  135. if (count($importArray) > 0)
  136. {
  137. DatabaseCompatibilityUtil::bulkInsert($tableName, $importArray, $columns, $bulkQuantity);
  138. }
  139. }
  140. protected static function optimizeTableNonImportColumns($tableName)
  141. {
  142. $bean = R::dispense($tableName);
  143. $bean->status = '2147483647'; //Creates an integer todo: optimize to status SET
  144. $s = chr(rand(ord('A'), ord('Z')));
  145. while (strlen($bean->serializedmessages) < '1024')
  146. {
  147. $bean->serializedmessages .= chr(rand(ord('a'), ord('z')));
  148. }
  149. R::store($bean);
  150. R::trash($bean);
  151. }
  152. /**
  153. * Drops a table by the given table name.
  154. * @param string $tableName
  155. */
  156. public static function dropTableByTableName($tableName)
  157. {
  158. assert('$tableName == strtolower($tableName)');
  159. R::exec("drop table if exists $tableName");
  160. }
  161. /**
  162. * Gets the count of how many columns there are in a table minus the initial 'id' column.
  163. * @param string $tableName
  164. * @return integer
  165. */
  166. public static function getColumnCountByTableName($tableName)
  167. {
  168. assert('is_string($tableName)');
  169. $firstRowData = self::getFirstRowByTableName($tableName);
  170. return count($firstRowData) - 1;
  171. }
  172. /**
  173. * Get the first row of a table. if no rows exist, an NoRowsInTableException is thrown.
  174. * @param string $tableName
  175. */
  176. public static function getFirstRowByTableName($tableName)
  177. {
  178. assert('is_string($tableName)');
  179. $sql = 'select * from ' . $tableName;
  180. try
  181. {
  182. $data = R::getRow($sql);
  183. }
  184. catch (RedBean_Exception_SQL $e)
  185. {
  186. throw new NoRowsInTableException();
  187. }
  188. return $data;
  189. }
  190. /**
  191. * Given a table name, count, and offset get an array of beans.
  192. * @param string $tableName
  193. * @param integer $count
  194. * @param integer $offset
  195. * @return array of RedBean_OODBBean beans.
  196. */
  197. public static function getSubset($tableName, $where = null, $count = null, $offset = null)
  198. {
  199. assert('is_string($tableName)');
  200. assert('$offset === null || is_integer($offset) && $offset >= 0');
  201. assert('$offset === null || is_integer($count) && $count >= 1');
  202. $sql = 'select id from ' . $tableName;
  203. if ($where != null)
  204. {
  205. $sql .= ' where ' . $where;
  206. }
  207. if ($count !== null)
  208. {
  209. $sql .= " limit $count";
  210. }
  211. if ($offset !== null)
  212. {
  213. $sql .= " offset $offset";
  214. }
  215. $ids = R::getCol($sql);
  216. return R::batch ($tableName, $ids);
  217. }
  218. /**
  219. * Get the row count in a given table.
  220. * @param string $tableName
  221. * @return integer
  222. */
  223. public static function getCount($tableName, $where = null)
  224. {
  225. $sql = 'select count(*) count from ' . $tableName;
  226. if ($where != null)
  227. {
  228. $sql .= ' where ' . $where;
  229. }
  230. $count = R::getCell($sql);
  231. if ($count === null)
  232. {
  233. $count = 0;
  234. }
  235. return $count;
  236. }
  237. /**
  238. * Update the row in the table with status and message information after the row is attempted or successfully
  239. * imported.
  240. * @param string $tableName
  241. * @param integer $id
  242. * @param integer $status
  243. * @param string or null $serializedMessages
  244. */
  245. public static function updateRowAfterProcessing($tableName, $id, $status, $serializedMessages)
  246. {
  247. assert('is_string($tableName)');
  248. assert('is_int($id)');
  249. assert('is_int($status)');
  250. assert('is_string($serializedMessages) || $serializedMessages == null');
  251. $bean = R::findOne($tableName, "id = :id", array('id' => $id));
  252. if ($bean == null)
  253. {
  254. throw new NotFoundException();
  255. }
  256. $bean->status = $status;
  257. $bean->serializedmessages = $serializedMessages;
  258. R::store($bean);
  259. }
  260. /**
  261. * For the temporary import tables, some of the columns are reserved and not used by any of the import data
  262. * coming from a csv. This includes the id, status, and serializedMessages columns.
  263. * @return array of column names.
  264. */
  265. public static function getReservedColumnNames()
  266. {
  267. return array('id', 'status', 'serializedmessages');
  268. }
  269. }
  270. ?>