Advertisement
Guest User

Untitled

a guest
Sep 6th, 2017
635
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 143.92 KB | None | 0 0
  1. -- phpMyAdmin SQL Dump
  2. -- version 4.7.0
  3. -- https://www.phpmyadmin.net/
  4. --
  5. -- Host: 127.0.0.1
  6. -- Generation Time: Sep 06, 2017 at 01:25 PM
  7. -- Server version: 10.1.10-MariaDB
  8. -- PHP Version: 7.0.21
  9.  
  10. SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
  11. SET AUTOCOMMIT = 0;
  12. START TRANSACTION;
  13. SET time_zone = "+00:00";
  14.  
  15.  
  16. /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
  17. /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
  18. /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
  19. /*!40101 SET NAMES utf8mb4 */;
  20.  
  21. --
  22. -- Database: `crm_db2`
  23. --
  24.  
  25. DELIMITER $$
  26. --
  27. -- Procedures
  28. --
  29. CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_campaign_insert` (IN `name` VARCHAR(100), IN `description` TEXT, IN `start_date` DATE, IN `end_date` DATE, IN `status` ENUM('0','1'), IN `number` INT, IN `percentage_response` DOUBLE, IN `budget_cost_currency` ENUM('1','2'), IN `budget_cost` DOUBLE, IN `actual_cost_currency` ENUM('1','2'), IN `actual_cost` DOUBLE, IN `expected_revenue_currency` ENUM('1','2'), IN `expected_revenue` DOUBLE, IN `u_created` VARCHAR(100), IN `date_created` DATETIME, OUT `c_id` INT, OUT `sukses` INT) NO SQL
  30. BEGIN
  31.  
  32. DECLARE exit handler for sqlexception
  33. BEGIN
  34. GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
  35. @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
  36. SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
  37. SELECT @full_error;
  38. SET sukses=0;
  39. ROLLBACK;
  40. END;
  41.  
  42. DECLARE exit handler for sqlwarning
  43. BEGIN
  44. GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
  45. @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
  46. SET @full_error = CONCAT("Warning ", @errno, " (", @sqlstate, "): ", @text);
  47. SELECT @full_error;
  48. SET sukses=0;
  49. ROLLBACK;
  50. END;
  51. drop temporary table if exists temp_cursor_table;
  52. set @temp = concat('CREATE TEMPORARY TABLE IF NOT EXISTS temp_cursor_table AS (SELECT product_id AS p_id FROM crm_products where product_id in(', product_list ,') )');
  53. prepare pst from @temp;
  54. execute pst;
  55. drop prepare pst;
  56.  
  57. INSERT INTO `crm_campaigns`(`campaign_name`, `campaign_description`, `campaign_start_date`, `campaign_end_date`, `campaign_status`, `campaign_number`, `campaign_percentage_response`, `campaign_budget_cost_currency`, `campaign_budget_cost`, `campaign_actual_cost_currency`, `campaign_actual_cost`, `campaign_expected_revenue_currency`, `campaign_expected_revenue`, `user_created`, `user_created_date`) VALUES (name, description, start_date, end_date, status, number, percentage_response, budget_cost_currency, budget_cost, actual_cost_currency, actual_cost, expected_revenue_currency, expected_revenue, u_created, date_created);
  58. SET c_id = LAST_INSERT_ID();
  59. SET sukses = 1;
  60. COMMIT;
  61. END$$
  62.  
  63. CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_company_insert` (IN `code` VARCHAR(50), IN `name` VARCHAR(200), IN `parent` INT, IN `office_phone` VARCHAR(30), IN `industry` INT, IN `email` VARCHAR(100), IN `fax` VARCHAR(30), IN `employee` INT, IN `revenue` DOUBLE, IN `website` VARCHAR(100), IN `address1` VARCHAR(255), IN `address2` VARCHAR(255), IN `city` VARCHAR(100), IN `state` VARCHAR(100), IN `zip_code` VARCHAR(10), IN `country` VARCHAR(50), IN `billing_address1` VARCHAR(255), IN `billing_address2` VARCHAR(255), IN `billing_city` VARCHAR(100), IN `billing_state` VARCHAR(100), IN `billing_zip_code` VARCHAR(10), IN `billing_country` VARCHAR(50), IN `right_permission` ENUM('1','2','3'), IN `u_created` VARCHAR(100), IN `date_created` DATETIME, IN `groups` INT, IN `customer` ENUM('0','1'), IN `user_list` VARCHAR(300), OUT `sukses` INT) NO SQL
  64. BEGIN
  65.  
  66. DECLARE exit handler for sqlexception
  67. BEGIN
  68. GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
  69. @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
  70. SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
  71. SELECT @full_error;
  72. SET sukses=0;
  73. ROLLBACK;
  74. END;
  75.  
  76. DECLARE exit handler for sqlwarning
  77. BEGIN
  78. GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
  79. @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
  80. SET @full_error = CONCAT("Warning ", @errno, " (", @sqlstate, "): ", @text);
  81. SELECT @full_error;
  82. SET sukses=0;
  83. ROLLBACK;
  84. END;
  85. drop temporary table if exists temp_cursor_table;
  86. set @temp = concat('CREATE TEMPORARY TABLE IF NOT EXISTS temp_cursor_table AS (SELECT user_id AS u_id FROM crm_users where user_id in(', user_list ,') )');
  87. prepare pst from @temp;
  88. execute pst;
  89. drop prepare pst;
  90.  
  91. INSERT INTO `crm_companies`(`customer_code`, `company_name`, `company_parent`, `company_office_phone`, `company_industry`, `company_email`, `company_fax`, `company_employee`, `company_revenue`, `company_website`, `company_address1`, `company_address2`, `company_city`, `company_state`, `company_zip_code`, `company_country`, `company_billing_address1`, `company_billing_address2`, `company_billing_city`, `company_billing_state`, `company_billing_zip_code`, `company_billing_country`, `company_right_permission`, `user_created`, `user_created_date`, `company_groups`, `company_customer`) VALUES
  92. (code, name, parent, office_phone, industry, email, fax, employee, revenue, website, address1, address2, city, state, zip_code, country, billing_address1, billing_address2, billing_city, billing_state, billing_zip_code, billing_country, right_permission, u_created, date_created, groups, customer);
  93. SET @l_id = LAST_INSERT_ID();
  94. INSERT INTO `crm_users_companies`(`company_id`,`user_id`)
  95. SELECT @l_id,u_id from temp_cursor_table;
  96. drop temporary table if exists temp_cursor_table;
  97. SET sukses = 1;
  98. COMMIT;
  99. END$$
  100.  
  101. CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_company_meeting_delete` (IN `meet_id` INT, OUT `sukses` INT) NO SQL
  102. BEGIN
  103.  
  104.  
  105. DECLARE exit handler for sqlexception
  106. BEGIN
  107. GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
  108. @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
  109. SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
  110. SELECT @full_error;
  111. SET sukses=0;
  112. ROLLBACK;
  113. END;
  114.  
  115. DECLARE exit handler for sqlwarning
  116. BEGIN
  117. GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
  118. @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
  119. SET @full_error = CONCAT("Warning ", @errno, " (", @sqlstate, "): ", @text);
  120. SELECT @full_error;
  121. SET sukses=0;
  122. ROLLBACK;
  123. END;
  124.  
  125. START TRANSACTION;
  126. DELETE FROM company_meeting_users WHERE company_meeting_id = meet_id;
  127. DELETE FROM company_meeting WHERE company_meeting_id = meet_id;
  128. SET sukses = 1;
  129. COMMIT;
  130. END$$
  131.  
  132. CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_company_meeting_insert` (IN `l_id` INT, IN `name` VARCHAR(100), IN `location` VARCHAR(255), IN `start_date` DATETIME, IN `end_date` DATETIME, IN `description` TEXT, IN `date` DATETIME, IN `user` VARCHAR(100), IN `user_list` VARCHAR(300), OUT `sukses` INT) NO SQL
  133. BEGIN
  134.  
  135. DECLARE exit handler for sqlexception
  136. BEGIN
  137. GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
  138. @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
  139. SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
  140. SELECT @full_error;
  141. SET sukses=0;
  142. ROLLBACK;
  143. END;
  144.  
  145. DECLARE exit handler for sqlwarning
  146. BEGIN
  147. GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
  148. @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
  149. SET @full_error = CONCAT("Warning ", @errno, " (", @sqlstate, "): ", @text);
  150. SELECT @full_error;
  151. SET sukses=0;
  152. ROLLBACK;
  153. END;
  154. drop temporary table if exists temp_cursor_table;
  155. set @temp = concat('CREATE TEMPORARY TABLE IF NOT EXISTS temp_cursor_table AS (SELECT user_id FROM crm_users where user_id in(', user_list ,') )');
  156. prepare pst from @temp;
  157. execute pst;
  158. drop prepare pst;
  159. INSERT INTO `company_meeting`(`company_id`, `company_meeting_name`, `company_meeting_location`, `company_meeting_start_date`, `company_meeting_end_date`, `company_meeting_description`, `company_meeting_date`, `company_meeting_user`) VALUES (l_id,name,location,start_date,end_date,description,date,user);
  160. SET @meet_id = LAST_INSERT_ID();
  161. INSERT INTO `company_meeting_users`(`company_meeting_id`,`company_user_id`)
  162. SELECT @meet_id,user_id from temp_cursor_table;
  163. drop temporary table if exists temp_cursor_table;
  164. set sukses=1;
  165. COMMIT;
  166. END$$
  167.  
  168. CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_company_meeting_update` (IN `meet_id` INT, IN `l_id` INT, IN `name` VARCHAR(100), IN `location` VARCHAR(255), IN `start_date` DATETIME, IN `end_date` DATETIME, IN `description` TEXT, IN `date` DATETIME, IN `user` VARCHAR(100), IN `user_list` VARCHAR(300), OUT `sukses` INT) NO SQL
  169. BEGIN
  170.  
  171. DECLARE exit handler for sqlexception
  172. BEGIN
  173. GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
  174. @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
  175. SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
  176. SELECT @full_error;
  177. SET sukses=0;
  178. ROLLBACK;
  179. END;
  180.  
  181. DECLARE exit handler for sqlwarning
  182. BEGIN
  183. GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
  184. @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
  185. SET @full_error = CONCAT("Warning ", @errno, " (", @sqlstate, "): ", @text);
  186. SELECT @full_error;
  187. SET sukses=0;
  188. ROLLBACK;
  189. END;
  190. drop temporary table if exists temp_cursor_table;
  191. set @temp = concat('CREATE TEMPORARY TABLE IF NOT EXISTS temp_cursor_table AS (SELECT user_id FROM crm_users where user_id in(', user_list ,') )');
  192. prepare pst from @temp;
  193. execute pst;
  194. drop prepare pst;
  195. UPDATE `company_meeting` SET
  196. `company_meeting_name`=name,
  197. `company_meeting_location`=location,
  198. `company_meeting_start_date`=start_date,
  199. `company_meeting_end_date`=end_date,
  200. `company_meeting_description`=description,
  201. `company_meeting_date`=date,
  202. `company_meeting_user`=user,
  203. `company_id`=l_id
  204. WHERE `company_meeting_id`=meet_id;
  205. delete from company_meeting_users where company_meeting_id=meet_id;
  206. REPLACE INTO `company_meeting_users`(`company_meeting_id`,`company_user_id`)
  207. SELECT meet_id,user_id from temp_cursor_table;
  208. drop temporary table if exists temp_cursor_table;
  209. set sukses=1;
  210. COMMIT;
  211. END$$
  212.  
  213. CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_company_update` (IN `c_id` INT, IN `code` VARCHAR(50), IN `name` VARCHAR(200), IN `parent` INT, IN `office_phone` VARCHAR(30), IN `industry` INT, IN `email` VARCHAR(100), IN `fax` VARCHAR(30), IN `employee` INT, IN `revenue` DOUBLE, IN `website` VARCHAR(100), IN `address1` VARCHAR(255), IN `address2` VARCHAR(255), IN `city` VARCHAR(100), IN `state` VARCHAR(100), IN `zip_code` VARCHAR(10), IN `country` VARCHAR(50), IN `billing_address1` VARCHAR(255), IN `billing_address2` VARCHAR(255), IN `billing_city` VARCHAR(100), IN `billing_state` VARCHAR(100), IN `billing_zip_code` VARCHAR(10), IN `billing_country` VARCHAR(50), IN `right_permission` ENUM('1','2','3'), IN `groups` INT, IN `customer` ENUM('0','1'), IN `u_modified` VARCHAR(100), IN `date_modified` DATETIME, IN `user_list` VARCHAR(300), OUT `sukses` INT) NO SQL
  214. BEGIN
  215.  
  216. DECLARE exit handler for sqlexception
  217. BEGIN
  218. GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
  219. @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
  220. SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
  221. SELECT @full_error;
  222. SET sukses=0;
  223. ROLLBACK;
  224. END;
  225.  
  226. DECLARE exit handler for sqlwarning
  227. BEGIN
  228. GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
  229. @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
  230. SET @full_error = CONCAT("Warning ", @errno, " (", @sqlstate, "): ", @text);
  231. SELECT @full_error;
  232. SET sukses=0;
  233. ROLLBACK;
  234. END;
  235. drop temporary table if exists temp_cursor_table;
  236. set @temp = concat('CREATE TEMPORARY TABLE IF NOT EXISTS temp_cursor_table AS (SELECT user_id AS u_id FROM crm_users where user_id in(', user_list ,') )');
  237. prepare pst from @temp;
  238. execute pst;
  239. drop prepare pst;
  240. UPDATE `crm_companies` SET
  241. `customer_code`=code,
  242. `company_name`=name,
  243. `company_parent`=parent,
  244. `company_office_phone`=office_phone,
  245. `company_industry`=industry,
  246. `company_email`=email,
  247. `company_fax`=fax,
  248. `company_employee`=employee,
  249. `company_revenue`=revenue,
  250. `company_website`=website,
  251. `company_address1`=address1,
  252. `company_address2`=address2,
  253. `company_city`=city,
  254. `company_state`=state,
  255. `company_zip_code`=zip_code,
  256. `company_country`=country,
  257. `company_billing_address1`=billing_address1,
  258. `company_billing_address2`=billing_address2,
  259. `company_billing_city`=billing_city,
  260. `company_billing_state`=billing_state,
  261. `company_billing_zip_code`=billing_zip_code,
  262. `company_billing_country`=billing_country,
  263. `company_right_permission`=right_permission,
  264. `user_modified`=u_modified,
  265. `user_modified_date`=date_modified,
  266. `company_groups`=groups,
  267. `company_customer`=customer
  268. WHERE `company_id`=c_id;
  269. delete from crm_users_companies where company_id=c_id;
  270. REPLACE INTO `crm_users_companies`(`company_id`,`user_id`)
  271. SELECT c_id,u_id from temp_cursor_table;
  272. drop temporary table if exists temp_cursor_table;
  273. SET sukses = 1;
  274. COMMIT;
  275. END$$
  276.  
  277. CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_contact_insert` (IN `salutation` ENUM('1','2','3'), IN `first_name` VARCHAR(100), IN `last_name` VARCHAR(100), IN `job_title` VARCHAR(100), IN `company` VARCHAR(100), IN `department` VARCHAR(100), IN `email` VARCHAR(100), IN `office_phone` VARCHAR(30), IN `mobile_phone` VARCHAR(30), IN `industry` INT, IN `address1` VARCHAR(255), IN `address2` VARCHAR(255), IN `city` VARCHAR(100), IN `state` VARCHAR(100), IN `zip_code` VARCHAR(10), IN `country` VARCHAR(50), IN `sources` INT, IN `photo` VARCHAR(100), IN `comp_id` INT, IN `right_permission` ENUM('1','2','3'), IN `groups` INT, IN `user_list` VARCHAR(300), OUT `sukses` INT, OUT `contact_id` INT) NO SQL
  278. BEGIN
  279.  
  280. DECLARE exit handler for sqlexception
  281. BEGIN
  282. GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
  283. @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
  284. SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
  285. SELECT @full_error;
  286. SET sukses=0;
  287. ROLLBACK;
  288. END;
  289.  
  290. DECLARE exit handler for sqlwarning
  291. BEGIN
  292. GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
  293. @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
  294. SET @full_error = CONCAT("Warning ", @errno, " (", @sqlstate, "): ", @text);
  295. SELECT @full_error;
  296. SET sukses=0;
  297. ROLLBACK;
  298. END;
  299. drop temporary table if exists temp_cursor_table;
  300. set @temp = concat('CREATE TEMPORARY TABLE IF NOT EXISTS temp_cursor_table AS (SELECT user_id AS u_id FROM crm_users where user_id in(', user_list ,') )');
  301. prepare pst from @temp;
  302. execute pst;
  303. drop prepare pst;
  304.  
  305. INSERT INTO `crm_peoples`(`people_salutation`, `people_first_name`, `people_last_name`, `people_job_title`, `people_company`, `people_department`, `people_email`, `people_office_phone`, `people_mobile_phone`, `people_industry`, `people_address1`, `people_address2`, `people_city`, `people_state`, `people_zip_code`, `people_country`, `people_source`, `people_photo`, `people_status`) VALUES (salutation, first_name, last_name, job_title, company, department, email, office_phone, mobile_phone, industry, address1, address2, city, state, zip_code, country, sources, photo, '2');
  306. SET @p_id = LAST_INSERT_ID();
  307. INSERT INTO `crm_contacts`(`people_id`, `contact_right_permission`, `contact_groups`, `company_id`) VALUES (@p_id, right_permission, groups, comp_id);
  308. SET @l_id = LAST_INSERT_ID();
  309. INSERT INTO `crm_users_contacts`(`contact_id`,`user_id`)
  310. SELECT @l_id,u_id from temp_cursor_table;
  311. SET contact_id = @l_id;
  312. drop temporary table if exists temp_cursor_table;
  313. SET sukses = 1;
  314. COMMIT;
  315. END$$
  316.  
  317. CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_contact_meeting_delete` (IN `meet_id` INT, OUT `sukses` INT) NO SQL
  318. BEGIN
  319.  
  320.  
  321. DECLARE exit handler for sqlexception
  322. BEGIN
  323. GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
  324. @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
  325. SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
  326. SELECT @full_error;
  327. SET sukses=0;
  328. ROLLBACK;
  329. END;
  330.  
  331. DECLARE exit handler for sqlwarning
  332. BEGIN
  333. GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
  334. @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
  335. SET @full_error = CONCAT("Warning ", @errno, " (", @sqlstate, "): ", @text);
  336. SELECT @full_error;
  337. SET sukses=0;
  338. ROLLBACK;
  339. END;
  340.  
  341. START TRANSACTION;
  342. DELETE FROM contact_meeting_users WHERE contact_meeting_id = meet_id;
  343. DELETE FROM contact_meeting WHERE contact_meeting_id = meet_id;
  344. SET sukses = 1;
  345. COMMIT;
  346. END$$
  347.  
  348. CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_contact_meeting_insert` (IN `l_id` INT, IN `name` VARCHAR(100), IN `location` VARCHAR(255), IN `start_date` DATETIME, IN `end_date` DATETIME, IN `description` TEXT, IN `date` DATETIME, IN `user` VARCHAR(100), IN `user_list` VARCHAR(300), OUT `sukses` INT) NO SQL
  349. BEGIN
  350.  
  351. DECLARE exit handler for sqlexception
  352. BEGIN
  353. GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
  354. @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
  355. SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
  356. SELECT @full_error;
  357. SET sukses=0;
  358. ROLLBACK;
  359. END;
  360.  
  361. DECLARE exit handler for sqlwarning
  362. BEGIN
  363. GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
  364. @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
  365. SET @full_error = CONCAT("Warning ", @errno, " (", @sqlstate, "): ", @text);
  366. SELECT @full_error;
  367. SET sukses=0;
  368. ROLLBACK;
  369. END;
  370. drop temporary table if exists temp_cursor_table;
  371. set @temp = concat('CREATE TEMPORARY TABLE IF NOT EXISTS temp_cursor_table AS (SELECT user_id FROM crm_users where user_id in(', user_list ,') )');
  372. prepare pst from @temp;
  373. execute pst;
  374. drop prepare pst;
  375. INSERT INTO `contact_meeting`(`contact_id`, `contact_meeting_name`, `contact_meeting_location`, `contact_meeting_start_date`, `contact_meeting_end_date`, `contact_meeting_description`, `contact_meeting_date`, `contact_meeting_user`) VALUES (l_id,name,location,start_date,end_date,description,date,user);
  376. SET @meet_id = LAST_INSERT_ID();
  377. INSERT INTO `contact_meeting_users`(`contact_meeting_id`,`contact_user_id`)
  378. SELECT @meet_id,user_id from temp_cursor_table;
  379. drop temporary table if exists temp_cursor_table;
  380. set sukses=1;
  381. COMMIT;
  382. END$$
  383.  
  384. CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_contact_meeting_update` (IN `meet_id` INT, IN `l_id` INT, IN `name` VARCHAR(100), IN `location` VARCHAR(255), IN `start_date` DATETIME, IN `end_date` DATETIME, IN `description` TEXT, IN `date` DATETIME, IN `user` VARCHAR(100), IN `user_list` VARCHAR(300), OUT `sukses` INT) NO SQL
  385. BEGIN
  386.  
  387. DECLARE exit handler for sqlexception
  388. BEGIN
  389. GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
  390. @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
  391. SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
  392. SELECT @full_error;
  393. SET sukses=0;
  394. ROLLBACK;
  395. END;
  396.  
  397. DECLARE exit handler for sqlwarning
  398. BEGIN
  399. GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
  400. @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
  401. SET @full_error = CONCAT("Warning ", @errno, " (", @sqlstate, "): ", @text);
  402. SELECT @full_error;
  403. SET sukses=0;
  404. ROLLBACK;
  405. END;
  406. drop temporary table if exists temp_cursor_table;
  407. set @temp = concat('CREATE TEMPORARY TABLE IF NOT EXISTS temp_cursor_table AS (SELECT user_id FROM crm_users where user_id in(', user_list ,') )');
  408. prepare pst from @temp;
  409. execute pst;
  410. drop prepare pst;
  411. UPDATE `contact_meeting` SET
  412. `contact_meeting_name`=name,
  413. `contact_meeting_location`=location,
  414. `contact_meeting_start_date`=start_date,
  415. `contact_meeting_end_date`=end_date,
  416. `contact_meeting_description`=description,
  417. `contact_meeting_date`=date,
  418. `contact_meeting_user`=user,
  419. `contact_id`=l_id
  420. WHERE `contact_meeting_id`=meet_id;
  421. delete from contact_meeting_users where contact_meeting_id=meet_id;
  422. REPLACE INTO `contact_meeting_users`(`contact_meeting_id`,`contact_user_id`)
  423. SELECT meet_id,user_id from temp_cursor_table;
  424. drop temporary table if exists temp_cursor_table;
  425. set sukses=1;
  426. COMMIT;
  427. END$$
  428.  
  429. CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_contact_update` (IN `l_id` INT, IN `salutation` ENUM('1','2','3'), IN `first_name` VARCHAR(100), IN `last_name` VARCHAR(100), IN `job_title` VARCHAR(100), IN `company` VARCHAR(100), IN `department` VARCHAR(100), IN `email` VARCHAR(100), IN `office_phone` VARCHAR(30), IN `mobile_phone` VARCHAR(30), IN `industry` INT, IN `address1` VARCHAR(255), IN `address2` VARCHAR(255), IN `city` VARCHAR(100), IN `state` VARCHAR(100), IN `zip_code` VARCHAR(10), IN `country` VARCHAR(50), IN `sources` INT, IN `photo` VARCHAR(100), IN `comp_id` INT, IN `right_permission` ENUM('1','2','3'), IN `groups` INT, IN `user_list` VARCHAR(300), OUT `sukses` INT) NO SQL
  430. BEGIN
  431.  
  432. DECLARE exit handler for sqlexception
  433. BEGIN
  434. GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
  435. @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
  436. SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
  437. SELECT @full_error;
  438. SET sukses=0;
  439. ROLLBACK;
  440. END;
  441.  
  442. DECLARE exit handler for sqlwarning
  443. BEGIN
  444. GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
  445. @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
  446. SET @full_error = CONCAT("Warning ", @errno, " (", @sqlstate, "): ", @text);
  447. SELECT @full_error;
  448. SET sukses=0;
  449. ROLLBACK;
  450. END;
  451. drop temporary table if exists temp_cursor_table;
  452. set @temp = concat('CREATE TEMPORARY TABLE IF NOT EXISTS temp_cursor_table AS (SELECT user_id AS u_id FROM crm_users where user_id in(', user_list ,') )');
  453. prepare pst from @temp;
  454. execute pst;
  455. drop prepare pst;
  456. UPDATE `crm_peoples` a JOIN crm_contacts b ON a.people_id=b.people_id
  457. SET
  458. a.people_salutation=salutation,
  459. a.people_first_name=first_name,
  460. a.people_last_name=last_name,
  461. a.people_job_title=job_title,
  462. a.people_company=company,
  463. a.people_department=department,
  464. a.people_email=email,
  465. a.people_office_phone=office_phone,
  466. a.people_mobile_phone=mobile_phone,
  467. a.people_industry=industry,
  468. a.people_address1=address1,
  469. a.people_address2=address2,
  470. a.people_city=city,
  471. a.people_state=state,
  472. a.people_zip_code=zip_code,
  473. a.people_country=country,
  474. a.people_source=sources,
  475. a.people_photo=photo,
  476. b.contact_right_permission=right_permission,
  477. b.contact_groups=groups,
  478. b.company_id=comp_id
  479. WHERE b.contact_id = l_id;
  480. delete from crm_users_contacts where contact_id = l_id;
  481. REPLACE INTO `crm_users_contacts`(`contact_id`,`user_id`)
  482. SELECT l_id,u_id from temp_cursor_table;
  483. drop temporary table if exists temp_cursor_table;
  484. SET sukses = 1;
  485. COMMIT;
  486. END$$
  487.  
  488. CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_convert_lead` (IN `l_id` INT, IN `comp_id` INT, IN `skip` ENUM('0','1'), IN `c_code` VARCHAR(50), IN `c_parent` INT, IN `c_office_phone` VARCHAR(30), IN `c_industry` INT, IN `c_email` VARCHAR(100), IN `c_fax` VARCHAR(30), IN `c_employee` INT, IN `c_revenue` DOUBLE, IN `c_website` VARCHAR(100), IN `c_address1` VARCHAR(255), IN `c_address2` VARCHAR(255), IN `c_city` VARCHAR(100), IN `c_state` VARCHAR(100), IN `c_zip_code` VARCHAR(10), IN `c_country` VARCHAR(50), IN `c_billing_address1` VARCHAR(255), IN `c_billing_address2` VARCHAR(255), IN `c_billing_city` VARCHAR(100), IN `c_billing_state` VARCHAR(100), IN `c_billing_zip_code` VARCHAR(10), IN `c_billing_country` VARCHAR(50), IN `c_right_permission` ENUM('1','2','3'), IN `c_u_created` VARCHAR(100), IN `c_date_created` DATETIME, IN `c_groups` INT, IN `c_customer` ENUM('0','1'), IN `o_code` VARCHAR(100), IN `o_name` VARCHAR(45), IN `o_stage_id` INT, IN `o_amount` DOUBLE, IN `o_closing_date` DATETIME, IN `o_service` ENUM('1','2'), IN `o_campaign_id` INT, IN `product_list` VARCHAR(300), OUT `sukses` INT) NO SQL
  489. BEGIN
  490.  
  491. DECLARE exit handler for sqlexception
  492. BEGIN
  493. GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
  494. @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
  495. SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
  496. SELECT @full_error;
  497. SET sukses=0;
  498. ROLLBACK;
  499. END;
  500.  
  501. DECLARE continue handler for not found set sukses = 0;
  502. DECLARE exit handler for sqlwarning
  503. BEGIN
  504. GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
  505. @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
  506. SET @full_error = CONCAT("Warning ", @errno, " (", @sqlstate, "): ", @text);
  507. SELECT @full_error;
  508. SET sukses=0;
  509. ROLLBACK;
  510. END;
  511. SELECT a.lead_status,b.people_company into @l_status, @company from crm_leads a JOIN crm_peoples b ON a.people_id=b.people_id WHERE a.lead_id=l_id;
  512. IF comp_id is null THEN
  513. SET @comp_id = null;
  514. ELSE
  515. SET @comp_id = comp_id;
  516. END IF;
  517. IF @l_status= 1 THEN
  518. IF @comp_id IS null THEN
  519. INSERT INTO `crm_companies`(`customer_code`, `company_name`, `company_parent`, `company_office_phone`, `company_industry`,`company_email`, `company_fax`, `company_employee`, `company_revenue`, `company_website`, `company_address1`, `company_address2`, `company_city`, `company_state`, `company_zip_code`, `company_country`, `company_billing_address1`, `company_billing_address2`, `company_billing_city`, `company_billing_state`, `company_billing_zip_code`, `company_billing_country`, `company_right_permission`, `user_created`, `user_created_date`, `company_groups`, `company_customer`) VALUES
  520. (c_code, @company, c_parent, c_office_phone, c_industry, c_email, c_fax, c_employee, c_revenue, c_website, c_address1, c_address2, c_city, c_state, c_zip_code, c_country, c_billing_address1, c_billing_address2, c_billing_city, c_billing_state, c_billing_zip_code, c_billing_country, c_right_permission, c_u_created, c_date_created, c_groups, c_customer);
  521. set @comp_id = LAST_INSERT_ID();
  522. insert into crm_users_companies(company_id, user_id)
  523. SELECT @comp_id, user_id from crm_users_leads
  524. where lead_id = l_id;
  525. END IF;
  526. insert into crm_contacts(company_id,people_id,contact_right_permission,contact_groups)
  527. SELECT @comp_id,people_id,lead_right_permission,lead_groups from crm_leads where lead_id = l_id;
  528. set @c_id = LAST_INSERT_ID();
  529. UPDATE crm_leads set lead_status = '2'
  530. where lead_id = l_id;
  531. insert into crm_users_contacts(contact_id, user_id)
  532. SELECT @c_id, user_id from crm_users_leads
  533. where lead_id = l_id;
  534. IF skip='0' THEN
  535. drop temporary table if exists temp_cursor_table;
  536. set @temp = concat('CREATE TEMPORARY TABLE IF NOT EXISTS temp_cursor_table AS (SELECT product_id AS p_id FROM crm_products where product_id in(', product_list ,') )');
  537. prepare pst from @temp;
  538. execute pst;
  539. drop prepare pst;
  540. INSERT INTO `crm_opportunities`(`opportunity_code`,`opportunity_name`, `opportunity_stage_id`, `company_id`, `opportunity_amount`, `opportunity_closing_date`, `opportunity_service`, `opportunity_campaign_id`) VALUES (o_code, o_name, o_stage_id, @comp_id, o_amount, o_closing_date, o_service, o_campaign_id);
  541. SET @o_id = LAST_INSERT_ID();
  542. INSERT INTO crm_opportunities_contacts(opportunity_id,contact_id) VALUES (@o_id,@c_id);
  543. INSERT INTO `crm_opportunities_products`(`opportunity_id`, `product_id`)
  544. SELECT @o_id,p_id from temp_cursor_table;
  545. drop temporary table if exists temp_cursor_table;
  546.  
  547. END IF;
  548. END if;
  549. SET sukses = 1;
  550. COMMIT;
  551. END$$
  552.  
  553. CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_lead_delete` (IN `l_id` INT, OUT `sukses` INT) NO SQL
  554. BEGIN
  555.  
  556. DECLARE exit handler for sqlexception
  557. BEGIN
  558. GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
  559. @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
  560. SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
  561. SELECT @full_error;
  562. SET sukses=0;
  563. ROLLBACK;
  564. END;
  565.  
  566. DECLARE exit handler for sqlwarning
  567. BEGIN
  568. GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
  569. @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
  570. SET @full_error = CONCAT("Warning ", @errno, " (", @sqlstate, "): ", @text);
  571. SELECT @full_error;
  572. SET sukses=0;
  573. ROLLBACK;
  574. END;
  575. delete from crm_users_leads where lead_id = l_id;
  576. DELETE crm_peoples, crm_leads
  577. FROM crm_peoples
  578. INNER JOIN crm_leads ON crm_peoples.people_id=crm_leads.people_id
  579. WHERE crm_leads.lead_id=l_id;
  580. SET sukses = 1;
  581. COMMIT;
  582. END$$
  583.  
  584. CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_lead_insert` (IN `salutation` ENUM('1','2','3'), IN `first_name` VARCHAR(100), IN `last_name` VARCHAR(100), IN `job_title` VARCHAR(100), IN `company` VARCHAR(100), IN `department` VARCHAR(100), IN `email` VARCHAR(100), IN `office_phone` VARCHAR(30), IN `mobile_phone` VARCHAR(30), IN `industry` INT, IN `address1` VARCHAR(255), IN `address2` VARCHAR(255), IN `city` VARCHAR(100), IN `state` VARCHAR(100), IN `zip_code` VARCHAR(10), IN `country` VARCHAR(50), IN `sources` INT, IN `photo` VARCHAR(100), IN `stat` ENUM('1','2'), IN `label` INT, IN `right_permission` ENUM('1','2','3'), IN `groups` INT, IN `u_created` VARCHAR(100), IN `date_created` DATETIME, IN `user_list` VARCHAR(300), OUT `sukses` INT, OUT `l_id` INT) NO SQL
  585. BEGIN
  586.  
  587. DECLARE exit handler for sqlexception
  588. BEGIN
  589. GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
  590. @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
  591. SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
  592. SELECT @full_error;
  593. SET sukses=0;
  594. ROLLBACK;
  595. END;
  596.  
  597. DECLARE exit handler for sqlwarning
  598. BEGIN
  599. GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
  600. @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
  601. SET @full_error = CONCAT("Warning ", @errno, " (", @sqlstate, "): ", @text);
  602. SELECT @full_error;
  603. SET sukses=0;
  604. ROLLBACK;
  605. END;
  606. drop temporary table if exists temp_cursor_table;
  607. set @temp = concat('CREATE TEMPORARY TABLE IF NOT EXISTS temp_cursor_table AS (SELECT user_id AS u_id FROM crm_users where user_id in(', user_list ,') )');
  608. prepare pst from @temp;
  609. execute pst;
  610. drop prepare pst;
  611.  
  612. INSERT INTO `crm_peoples`(`people_salutation`, `people_first_name`, `people_last_name`, `people_job_title`, `people_company`, `people_department`, `people_email`, `people_office_phone`, `people_mobile_phone`, `people_industry`, `people_address1`, `people_address2`, `people_city`, `people_state`, `people_zip_code`, `people_country`, `people_source`, `people_photo`, `people_status`) VALUES (salutation, first_name, last_name, job_title, company, department, email, office_phone, mobile_phone, industry, address1, address2, city, state, zip_code, country, sources, photo, stat);
  613. SET @p_id = LAST_INSERT_ID();
  614. INSERT INTO `crm_leads`(`lead_label`, `people_id`, `lead_right_permission`, `lead_groups`, `user_created`, `user_created_date`) VALUES (label, @p_id, right_permission, groups, u_created, date_created);
  615. SET l_id = LAST_INSERT_ID();
  616. INSERT INTO `crm_users_leads`(`lead_id`,`user_id`)
  617. SELECT l_id,u_id from temp_cursor_table;
  618. drop temporary table if exists temp_cursor_table;
  619. SET sukses = 1;
  620. COMMIT;
  621. END$$
  622.  
  623. CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_lead_meeting_delete` (IN `meet_id` INT, OUT `sukses` INT) NO SQL
  624. BEGIN
  625.  
  626.  
  627. DECLARE exit handler for sqlexception
  628. BEGIN
  629. GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
  630. @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
  631. SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
  632. SELECT @full_error;
  633. SET sukses=0;
  634. ROLLBACK;
  635. END;
  636.  
  637. DECLARE exit handler for sqlwarning
  638. BEGIN
  639. GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
  640. @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
  641. SET @full_error = CONCAT("Warning ", @errno, " (", @sqlstate, "): ", @text);
  642. SELECT @full_error;
  643. SET sukses=0;
  644. ROLLBACK;
  645. END;
  646.  
  647. START TRANSACTION;
  648. DELETE FROM lead_meeting_users WHERE lead_meeting_id = meet_id;
  649. DELETE FROM lead_meeting WHERE lead_meeting_id = meet_id;
  650. SET sukses = 1;
  651. COMMIT;
  652. END$$
  653.  
  654. CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_lead_meeting_insert` (IN `l_id` INT, IN `name` VARCHAR(100), IN `location` VARCHAR(255), IN `start_date` DATETIME, IN `end_date` DATETIME, IN `description` TEXT, IN `date` DATETIME, IN `user` VARCHAR(100), IN `user_list` VARCHAR(300), OUT `sukses` INT) NO SQL
  655. BEGIN
  656.  
  657. DECLARE exit handler for sqlexception
  658. BEGIN
  659. GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
  660. @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
  661. SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
  662. SELECT @full_error;
  663. SET sukses=0;
  664. ROLLBACK;
  665. END;
  666.  
  667. DECLARE exit handler for sqlwarning
  668. BEGIN
  669. GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
  670. @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
  671. SET @full_error = CONCAT("Warning ", @errno, " (", @sqlstate, "): ", @text);
  672. SELECT @full_error;
  673. SET sukses=0;
  674. ROLLBACK;
  675. END;
  676. drop temporary table if exists temp_cursor_table;
  677. set @temp = concat('CREATE TEMPORARY TABLE IF NOT EXISTS temp_cursor_table AS (SELECT user_id FROM crm_users where user_id in(', user_list ,') )');
  678. prepare pst from @temp;
  679. execute pst;
  680. drop prepare pst;
  681. INSERT INTO `lead_meeting`(`lead_id`, `lead_meeting_name`, `lead_meeting_location`, `lead_meeting_start_date`, `lead_meeting_end_date`, `lead_meeting_description`, `lead_meeting_date`, `lead_meeting_user`) VALUES (l_id,name,location,start_date,end_date,description,date,user);
  682. SET @meet_id = LAST_INSERT_ID();
  683. INSERT INTO `lead_meeting_users`(`lead_meeting_id`,`lead_user_id`)
  684. SELECT @meet_id,user_id from temp_cursor_table;
  685. drop temporary table if exists temp_cursor_table;
  686. set sukses=1;
  687. COMMIT;
  688. END$$
  689.  
  690. CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_lead_meeting_update` (IN `meet_id` INT, IN `l_id` INT, IN `name` VARCHAR(100), IN `location` VARCHAR(255), IN `start_date` DATETIME, IN `end_date` DATETIME, IN `description` TEXT, IN `date` DATETIME, IN `user` VARCHAR(100), IN `user_list` VARCHAR(300), OUT `sukses` INT) NO SQL
  691. BEGIN
  692.  
  693. DECLARE exit handler for sqlexception
  694. BEGIN
  695. GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
  696. @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
  697. SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
  698. SELECT @full_error;
  699. SET sukses=0;
  700. ROLLBACK;
  701. END;
  702.  
  703. DECLARE exit handler for sqlwarning
  704. BEGIN
  705. GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
  706. @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
  707. SET @full_error = CONCAT("Warning ", @errno, " (", @sqlstate, "): ", @text);
  708. SELECT @full_error;
  709. SET sukses=0;
  710. ROLLBACK;
  711. END;
  712. drop temporary table if exists temp_cursor_table;
  713. set @temp = concat('CREATE TEMPORARY TABLE IF NOT EXISTS temp_cursor_table AS (SELECT user_id FROM crm_users where user_id in(', user_list ,') )');
  714. prepare pst from @temp;
  715. execute pst;
  716. drop prepare pst;
  717. UPDATE `lead_meeting` SET
  718. `lead_meeting_name`=name,
  719. `lead_meeting_location`=location,
  720. `lead_meeting_start_date`=start_date,
  721. `lead_meeting_end_date`=end_date,
  722. `lead_meeting_description`=description,
  723. `lead_meeting_date`=date,
  724. `lead_meeting_user`=user,
  725. `lead_id`=l_id
  726. WHERE `lead_meeting_id`=meet_id;
  727. delete from lead_meeting_users where lead_meeting_id=meet_id;
  728. REPLACE INTO `lead_meeting_users`(`lead_meeting_id`,`lead_user_id`)
  729. SELECT meet_id,user_id from temp_cursor_table;
  730. drop temporary table if exists temp_cursor_table;
  731. set sukses=1;
  732. COMMIT;
  733. END$$
  734.  
  735. CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_lead_update` (IN `l_id` INT, IN `salutation` ENUM('1','2','3'), IN `first_name` VARCHAR(100), IN `last_name` VARCHAR(100), IN `job_title` VARCHAR(100), IN `company` VARCHAR(100), IN `department` VARCHAR(100), IN `email` VARCHAR(100), IN `office_phone` VARCHAR(30), IN `mobile_phone` VARCHAR(30), IN `industry` INT, IN `address1` VARCHAR(255), IN `address2` VARCHAR(255), IN `city` VARCHAR(100), IN `state` VARCHAR(100), IN `zip_code` VARCHAR(10), IN `country` VARCHAR(50), IN `sources` INT, IN `photo` VARCHAR(100), IN `status_p` ENUM('1','2'), IN `status_l` ENUM('1','2','3'), IN `label` INT, IN `right_permission` ENUM('1','2','3'), IN `groups` INT, IN `u_modified` VARCHAR(100), IN `date_modified` DATETIME, IN `user_list` VARCHAR(300), OUT `sukses` INT) NO SQL
  736. BEGIN
  737.  
  738. DECLARE exit handler for sqlexception
  739. BEGIN
  740. GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
  741. @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
  742. SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
  743. SELECT @full_error;
  744. SET sukses=0;
  745. ROLLBACK;
  746. END;
  747.  
  748. DECLARE exit handler for sqlwarning
  749. BEGIN
  750. GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
  751. @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
  752. SET @full_error = CONCAT("Warning ", @errno, " (", @sqlstate, "): ", @text);
  753. SELECT @full_error;
  754. SET sukses=0;
  755. ROLLBACK;
  756. END;
  757. drop temporary table if exists temp_cursor_table;
  758. set @temp = concat('CREATE TEMPORARY TABLE IF NOT EXISTS temp_cursor_table AS (SELECT user_id AS u_id FROM crm_users where user_id in(', user_list ,') )');
  759. prepare pst from @temp;
  760. execute pst;
  761. drop prepare pst;
  762. UPDATE `crm_peoples` a JOIN crm_leads b ON a.people_id=b.people_id
  763. SET
  764. a.people_salutation=salutation,
  765. a.people_first_name=first_name,
  766. a.people_last_name=last_name,
  767. a.people_job_title=job_title,
  768. a.people_company=company,
  769. a.people_department=department,
  770. a.people_email=email,
  771. a.people_office_phone=office_phone,
  772. a.people_mobile_phone=mobile_phone,
  773. a.people_industry=industry,
  774. a.people_address1=address1,
  775. a.people_address2=address2,
  776. a.people_city=city,
  777. a.people_state=state,
  778. a.people_zip_code=zip_code,
  779. a.people_country=country,
  780. a.people_source=sources,
  781. a.people_photo=photo,
  782. a.people_status = status_p,
  783. b.lead_label=label,
  784. b.lead_right_permission=right_permission,
  785. b.lead_groups=groups,
  786. b.lead_status=status_l,
  787. b.user_modified=u_modified,
  788. b.user_modified_date=date_modified
  789. WHERE b.lead_id = l_id;
  790. delete from crm_users_leads where lead_id = l_id;
  791. REPLACE INTO `crm_users_leads`(`lead_id`,`user_id`)
  792. SELECT l_id,u_id from temp_cursor_table;
  793. drop temporary table if exists temp_cursor_table;
  794. SET sukses = 1;
  795. COMMIT;
  796. END$$
  797.  
  798. CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_opportunity_insert` (IN `code` VARCHAR(100), IN `name` VARCHAR(45), IN `stage_id` INT, IN `comp_id` INT, IN `amount` DOUBLE, IN `closing_date` DATETIME, IN `service` ENUM('1','2'), IN `campaign_id` INT, IN `product_list` VARCHAR(300), IN `contact_list` VARCHAR(300), OUT `sukses` INT) NO SQL
  799. BEGIN
  800.  
  801. DECLARE exit handler for sqlexception
  802. BEGIN
  803. GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
  804. @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
  805. SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
  806. SELECT @full_error;
  807. SET sukses=0;
  808. ROLLBACK;
  809. END;
  810.  
  811. DECLARE exit handler for sqlwarning
  812. BEGIN
  813. GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
  814. @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
  815. SET @full_error = CONCAT("Warning ", @errno, " (", @sqlstate, "): ", @text);
  816. SELECT @full_error;
  817. SET sukses=0;
  818. ROLLBACK;
  819. END;
  820. drop temporary table if exists temp_cursor_table;
  821. set @temp = concat('CREATE TEMPORARY TABLE IF NOT EXISTS temp_cursor_table AS (SELECT product_id AS p_id FROM crm_products where product_id in(', product_list ,') )');
  822. prepare pst from @temp;
  823. execute pst;
  824. drop prepare pst;
  825. drop temporary table if exists temp_contact;
  826. set @temp1 = concat('CREATE TEMPORARY TABLE IF NOT EXISTS temp_contact AS (SELECT contact_id AS c_id FROM crm_contacts where contact_id in(', contact_list ,') )');
  827. prepare pst from @temp1;
  828. execute pst;
  829. drop prepare pst;
  830.  
  831. INSERT INTO `crm_opportunities`(`opportunity_code`, `opportunity_name`, `opportunity_stage_id`, `company_id`, `opportunity_amount`, `opportunity_closing_date`, `opportunity_service`, `opportunity_campaign_id`) VALUES (code, name, stage_id, comp_id, amount, closing_date, service, campaign_id);
  832. SET @o_id = LAST_INSERT_ID();
  833. INSERT INTO crm_opportunities_contacts(opportunity_id,contact_id)
  834. SELECT @o_id,c_id from temp_contact;
  835. INSERT INTO `crm_opportunities_products`(`opportunity_id`, `product_id`)
  836. SELECT @o_id,p_id from temp_cursor_table;
  837. drop temporary table if exists temp_cursor_table;
  838. drop temporary table if exists temp_contact;
  839. SET sukses = 1;
  840. COMMIT;
  841. END$$
  842.  
  843. CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_opportunity_meeting_delete` (IN `meet_id` INT, OUT `sukses` INT) NO SQL
  844. BEGIN
  845.  
  846.  
  847. DECLARE exit handler for sqlexception
  848. BEGIN
  849. GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
  850. @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
  851. SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
  852. SELECT @full_error;
  853. SET sukses=0;
  854. ROLLBACK;
  855. END;
  856.  
  857. DECLARE exit handler for sqlwarning
  858. BEGIN
  859. GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
  860. @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
  861. SET @full_error = CONCAT("Warning ", @errno, " (", @sqlstate, "): ", @text);
  862. SELECT @full_error;
  863. SET sukses=0;
  864. ROLLBACK;
  865. END;
  866.  
  867. START TRANSACTION;
  868. DELETE FROM opportunity_meeting_users WHERE opportunity_meeting_id = meet_id;
  869. DELETE FROM opportunity_meeting WHERE opportunity_meeting_id = meet_id;
  870. SET sukses = 1;
  871. COMMIT;
  872. END$$
  873.  
  874. CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_opportunity_meeting_insert` (IN `o_id` INT, IN `name` VARCHAR(100), IN `location` VARCHAR(255), IN `start_date` DATETIME, IN `end_date` DATETIME, IN `description` TEXT, IN `date` DATETIME, IN `user` VARCHAR(100), IN `user_list` VARCHAR(300), OUT `sukses` INT) NO SQL
  875. BEGIN
  876.  
  877. DECLARE exit handler for sqlexception
  878. BEGIN
  879. GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
  880. @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
  881. SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
  882. SELECT @full_error;
  883. SET sukses=0;
  884. ROLLBACK;
  885. END;
  886.  
  887. DECLARE exit handler for sqlwarning
  888. BEGIN
  889. GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
  890. @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
  891. SET @full_error = CONCAT("Warning ", @errno, " (", @sqlstate, "): ", @text);
  892. SELECT @full_error;
  893. SET sukses=0;
  894. ROLLBACK;
  895. END;
  896. drop temporary table if exists temp_cursor_table;
  897. set @temp = concat('CREATE TEMPORARY TABLE IF NOT EXISTS temp_cursor_table AS (SELECT user_id FROM crm_users where user_id in(', user_list ,') )');
  898. prepare pst from @temp;
  899. execute pst;
  900. drop prepare pst;
  901. INSERT INTO `opportunity_meeting`(`opportunity_id`, `opportunity_meeting_name`, `opportunity_meeting_location`, `opportunity_meeting_start_date`, `opportunity_meeting_end_date`, `opportunity_meeting_description`, `opportunity_meeting_date`, `opportunity_meeting_user`) VALUES (o_id,name,location,start_date,end_date,description,date,user);
  902. SET @meet_id = LAST_INSERT_ID();
  903. INSERT INTO `opportunity_meeting_users`(`opportunity_meeting_id`,`opportunity_user_id`)
  904. SELECT @meet_id,user_id from temp_cursor_table;
  905. drop temporary table if exists temp_cursor_table;
  906. set sukses=1;
  907. COMMIT;
  908. END$$
  909.  
  910. CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_opportunity_meeting_update` (IN `meet_id` INT, IN `o_id` INT, IN `name` VARCHAR(100), IN `location` VARCHAR(255), IN `start_date` DATETIME, IN `end_date` DATETIME, IN `description` TEXT, IN `date` DATETIME, IN `user` VARCHAR(100), IN `user_list` VARCHAR(300), OUT `sukses` INT) NO SQL
  911. BEGIN
  912.  
  913. DECLARE exit handler for sqlexception
  914. BEGIN
  915. GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
  916. @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
  917. SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
  918. SELECT @full_error;
  919. SET sukses=0;
  920. ROLLBACK;
  921. END;
  922.  
  923. DECLARE exit handler for sqlwarning
  924. BEGIN
  925. GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
  926. @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
  927. SET @full_error = CONCAT("Warning ", @errno, " (", @sqlstate, "): ", @text);
  928. SELECT @full_error;
  929. SET sukses=0;
  930. ROLLBACK;
  931. END;
  932. drop temporary table if exists temp_cursor_table;
  933. set @temp = concat('CREATE TEMPORARY TABLE IF NOT EXISTS temp_cursor_table AS (SELECT user_id FROM crm_users where user_id in(', user_list ,') )');
  934. prepare pst from @temp;
  935. execute pst;
  936. drop prepare pst;
  937. UPDATE `opportunity_meeting` SET
  938. `opportunity_meeting_name`=name,
  939. `opportunity_meeting_location`=location,
  940. `opportunity_meeting_start_date`=start_date,
  941. `opportunity_meeting_end_date`=end_date,
  942. `opportunity_meeting_description`=description,
  943. `opportunity_meeting_date`=date,
  944. `opportunity_meeting_user`=user,
  945. `opportunity_id`=o_id
  946. WHERE `opportunity_meeting_id`=meet_id;
  947. delete from opportunity_meeting_users where opportunity_meeting_id=meet_id;
  948. REPLACE INTO `opportunity_meeting_users`(`opportunity_meeting_id`,`opportunity_user_id`)
  949. SELECT meet_id,user_id from temp_cursor_table;
  950. drop temporary table if exists temp_cursor_table;
  951. set sukses=1;
  952. COMMIT;
  953. END$$
  954.  
  955. CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_opportunity_update` (IN `o_id` INT, IN `name` VARCHAR(45), IN `stage_id` INT, IN `comp_id` INT, IN `amount` DOUBLE, IN `closing_date` DATETIME, IN `service` ENUM('1','2'), IN `campaign_id` INT, IN `product_list` VARCHAR(300), IN `contact_list` VARCHAR(300), OUT `sukses` INT) NO SQL
  956. BEGIN
  957.  
  958. DECLARE exit handler for sqlexception
  959. BEGIN
  960. GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
  961. @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
  962. SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
  963. SELECT @full_error;
  964. SET sukses=0;
  965. ROLLBACK;
  966. END;
  967.  
  968. DECLARE exit handler for sqlwarning
  969. BEGIN
  970. GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
  971. @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
  972. SET @full_error = CONCAT("Warning ", @errno, " (", @sqlstate, "): ", @text);
  973. SELECT @full_error;
  974. SET sukses=0;
  975. ROLLBACK;
  976. END;
  977. drop temporary table if exists temp_cursor_table;
  978. set @temp = concat('CREATE TEMPORARY TABLE IF NOT EXISTS temp_cursor_table AS (SELECT product_id AS p_id FROM crm_products where product_id in(', product_list ,') )');
  979. prepare pst from @temp;
  980. execute pst;
  981. drop prepare pst;
  982. drop temporary table if exists temp_contact;
  983. set @temp1 = concat('CREATE TEMPORARY TABLE IF NOT EXISTS temp_contact AS (SELECT contact_id AS c_id FROM crm_contacts where contact_id in(', contact_list ,') )');
  984. prepare pst from @temp1;
  985. execute pst;
  986. drop prepare pst;
  987. UPDATE `crm_opportunities` SET
  988. `opportunity_name`=name,
  989. `opportunity_stage_id`=stage_id,
  990. `company_id`=comp_id,
  991. `opportunity_amount`=amount,
  992. `opportunity_closing_date`=closing_date,
  993. `opportunity_service`=service,
  994. `opportunity_campaign_id`=campaign_id
  995. WHERE `opportunity_id`=o_id;
  996. delete from crm_opportunities_products where opportunity_id = o_id;
  997. REPLACE INTO `crm_opportunities_products`(`opportunity_id`, `product_id`)
  998. SELECT o_id,p_id from temp_cursor_table;
  999. REPLACE INTO crm_opportunities_contacts(opportunity_id,contact_id)
  1000. SELECT o_id,c_id from temp_contact;
  1001. drop temporary table if exists temp_cursor_table;
  1002. drop temporary table if exists temp_contact;
  1003. SET sukses = 1;
  1004. COMMIT;
  1005. END$$
  1006.  
  1007. DELIMITER ;
  1008.  
  1009. -- --------------------------------------------------------
  1010.  
  1011. --
  1012. -- Table structure for table `company_calls`
  1013. --
  1014.  
  1015. CREATE TABLE `company_calls` (
  1016. `company_call_id` int(11) NOT NULL,
  1017. `company_call_title` varchar(100) DEFAULT NULL,
  1018. `company_call_duration` int(11) DEFAULT NULL,
  1019. `company_call_time` enum('1','2','3') DEFAULT '1' COMMENT '1= Second\n2= Minutes\n3=Hour\n',
  1020. `company_call_notes` text,
  1021. `company_call_date` datetime NOT NULL,
  1022. `company_call_user` varchar(100) NOT NULL,
  1023. `company_id` int(11) NOT NULL
  1024. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  1025.  
  1026. --
  1027. -- Dumping data for table `company_calls`
  1028. --
  1029.  
  1030. INSERT INTO `company_calls` (`company_call_id`, `company_call_title`, `company_call_duration`, `company_call_time`, `company_call_notes`, `company_call_date`, `company_call_user`, `company_id`) VALUES
  1031. (1, 'eqw', 33, '1', 'qwe', '2017-08-23 20:27:09', 'ulala', 24);
  1032.  
  1033. -- --------------------------------------------------------
  1034.  
  1035. --
  1036. -- Table structure for table `company_files`
  1037. --
  1038.  
  1039. CREATE TABLE `company_files` (
  1040. `company_files_id` int(11) NOT NULL,
  1041. `company_file_subject` varchar(100) DEFAULT NULL,
  1042. `company_file_to` varchar(100) NOT NULL,
  1043. `company_file_cc` varchar(100) DEFAULT NULL,
  1044. `company_file_description` text,
  1045. `company_file_name` varchar(100) NOT NULL,
  1046. `company_file_date` datetime NOT NULL,
  1047. `company_file_user` varchar(100) NOT NULL,
  1048. `company_id` int(11) NOT NULL
  1049. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  1050.  
  1051. -- --------------------------------------------------------
  1052.  
  1053. --
  1054. -- Table structure for table `company_meeting`
  1055. --
  1056.  
  1057. CREATE TABLE `company_meeting` (
  1058. `company_meeting_id` int(11) NOT NULL,
  1059. `company_meeting_name` varchar(100) DEFAULT NULL,
  1060. `company_meeting_location` varchar(255) DEFAULT NULL,
  1061. `company_meeting_start_date` datetime DEFAULT NULL,
  1062. `company_meeting_end_date` datetime DEFAULT NULL,
  1063. `company_meeting_description` text,
  1064. `company_meeting_date` datetime NOT NULL,
  1065. `company_meeting_user` varchar(100) NOT NULL,
  1066. `company_id` int(11) NOT NULL
  1067. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  1068.  
  1069. --
  1070. -- Dumping data for table `company_meeting`
  1071. --
  1072.  
  1073. INSERT INTO `company_meeting` (`company_meeting_id`, `company_meeting_name`, `company_meeting_location`, `company_meeting_start_date`, `company_meeting_end_date`, `company_meeting_description`, `company_meeting_date`, `company_meeting_user`, `company_id`) VALUES
  1074. (2, 'qwe', 'qwe', '2017-08-01 00:00:00', '2017-08-02 00:00:00', 'qwe', '2017-08-23 20:26:57', 'ulala', 24),
  1075. (3, 'asd', 'asd', '2017-08-01 00:00:00', '2017-08-02 00:00:00', 'asdasd', '2017-08-25 12:57:23', 'ulala', 24);
  1076.  
  1077. -- --------------------------------------------------------
  1078.  
  1079. --
  1080. -- Table structure for table `company_meeting_users`
  1081. --
  1082.  
  1083. CREATE TABLE `company_meeting_users` (
  1084. `company_meeting_id` int(11) NOT NULL,
  1085. `company_user_id` int(11) NOT NULL
  1086. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  1087.  
  1088. --
  1089. -- Dumping data for table `company_meeting_users`
  1090. --
  1091.  
  1092. INSERT INTO `company_meeting_users` (`company_meeting_id`, `company_user_id`) VALUES
  1093. (2, 9),
  1094. (3, 15);
  1095.  
  1096. -- --------------------------------------------------------
  1097.  
  1098. --
  1099. -- Table structure for table `company_notes`
  1100. --
  1101.  
  1102. CREATE TABLE `company_notes` (
  1103. `company_note_id` int(11) NOT NULL,
  1104. `company_note_name` varchar(100) NOT NULL,
  1105. `company_note_description` text,
  1106. `company_note_date` datetime DEFAULT NULL,
  1107. `company_note_user` varchar(100) DEFAULT NULL,
  1108. `company_id` int(11) NOT NULL
  1109. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  1110.  
  1111. --
  1112. -- Dumping data for table `company_notes`
  1113. --
  1114.  
  1115. INSERT INTO `company_notes` (`company_note_id`, `company_note_name`, `company_note_description`, `company_note_date`, `company_note_user`, `company_id`) VALUES
  1116. (1, 'ssssss', '<p>ssssss</p>', '2017-08-23 20:26:27', 'ulala', 24);
  1117.  
  1118. -- --------------------------------------------------------
  1119.  
  1120. --
  1121. -- Table structure for table `company_tasks`
  1122. --
  1123.  
  1124. CREATE TABLE `company_tasks` (
  1125. `company_task_id` int(11) NOT NULL,
  1126. `company_task_name` varchar(100) NOT NULL,
  1127. `company_task_description` varchar(45) DEFAULT NULL,
  1128. `company_task_status` int(11) DEFAULT NULL,
  1129. `company_task_due_date` datetime DEFAULT NULL,
  1130. `company_task_date` datetime NOT NULL,
  1131. `company_task_user` varchar(100) NOT NULL,
  1132. `company_id` int(11) NOT NULL
  1133. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  1134.  
  1135. --
  1136. -- Dumping data for table `company_tasks`
  1137. --
  1138.  
  1139. INSERT INTO `company_tasks` (`company_task_id`, `company_task_name`, `company_task_description`, `company_task_status`, `company_task_due_date`, `company_task_date`, `company_task_user`, `company_id`) VALUES
  1140. (1, 'aas', 'aas', 44, '2017-08-23 00:00:00', '2017-08-23 20:26:10', 'ulala', 24);
  1141.  
  1142. -- --------------------------------------------------------
  1143.  
  1144. --
  1145. -- Table structure for table `contact_calls`
  1146. --
  1147.  
  1148. CREATE TABLE `contact_calls` (
  1149. `contact_call_id` int(11) NOT NULL,
  1150. `contact_call_title` varchar(100) DEFAULT NULL,
  1151. `contact_call_duration` int(11) DEFAULT NULL,
  1152. `contact_call_time` enum('1','2','3') DEFAULT '1' COMMENT '1= Second\n2= Minutes\n3=Hour\n',
  1153. `contact_call_notes` text,
  1154. `contact_call_date` datetime NOT NULL,
  1155. `contact_call_user` varchar(100) NOT NULL,
  1156. `contact_id` int(11) NOT NULL
  1157. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  1158.  
  1159. --
  1160. -- Dumping data for table `contact_calls`
  1161. --
  1162.  
  1163. INSERT INTO `contact_calls` (`contact_call_id`, `contact_call_title`, `contact_call_duration`, `contact_call_time`, `contact_call_notes`, `contact_call_date`, `contact_call_user`, `contact_id`) VALUES
  1164. (2, 'string', 10, '1', 'string', '2017-08-29 04:21:27', 'string', 30);
  1165.  
  1166. -- --------------------------------------------------------
  1167.  
  1168. --
  1169. -- Table structure for table `contact_files`
  1170. --
  1171.  
  1172. CREATE TABLE `contact_files` (
  1173. `contact_files_id` int(11) NOT NULL,
  1174. `contact_file_subject` varchar(100) DEFAULT NULL,
  1175. `contact_file_to` varchar(100) NOT NULL,
  1176. `contact_file_cc` varchar(100) DEFAULT NULL,
  1177. `contact_file_description` text,
  1178. `contact_file_name` varchar(100) NOT NULL,
  1179. `contact_file_date` datetime NOT NULL,
  1180. `contact_file_user` varchar(100) NOT NULL,
  1181. `contact_id` int(11) NOT NULL
  1182. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  1183.  
  1184. --
  1185. -- Dumping data for table `contact_files`
  1186. --
  1187.  
  1188. INSERT INTO `contact_files` (`contact_files_id`, `contact_file_subject`, `contact_file_to`, `contact_file_cc`, `contact_file_description`, `contact_file_name`, `contact_file_date`, `contact_file_user`, `contact_id`) VALUES
  1189. (1, 'string', 'string', 'string', 'string', '8996123e-a073-451d-bde5-f70ac9c01226.jpg', '2017-08-29 04:27:05', '5', 30);
  1190.  
  1191. -- --------------------------------------------------------
  1192.  
  1193. --
  1194. -- Table structure for table `contact_meeting`
  1195. --
  1196.  
  1197. CREATE TABLE `contact_meeting` (
  1198. `contact_meeting_id` int(11) NOT NULL,
  1199. `contact_meeting_name` varchar(100) DEFAULT NULL,
  1200. `contact_meeting_location` varchar(255) DEFAULT NULL,
  1201. `contact_meeting_start_date` datetime DEFAULT NULL,
  1202. `contact_meeting_end_date` datetime DEFAULT NULL,
  1203. `contact_meeting_description` text,
  1204. `contact_meeting_date` datetime NOT NULL,
  1205. `contact_meeting_user` varchar(100) NOT NULL,
  1206. `contact_id` int(11) NOT NULL
  1207. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  1208.  
  1209. --
  1210. -- Dumping data for table `contact_meeting`
  1211. --
  1212.  
  1213. INSERT INTO `contact_meeting` (`contact_meeting_id`, `contact_meeting_name`, `contact_meeting_location`, `contact_meeting_start_date`, `contact_meeting_end_date`, `contact_meeting_description`, `contact_meeting_date`, `contact_meeting_user`, `contact_id`) VALUES
  1214. (1, 'asd', 'asd', '2017-08-12 00:00:00', '2017-08-12 00:00:00', 'asd', '2017-08-23 20:31:21', 'ulala', 30);
  1215.  
  1216. -- --------------------------------------------------------
  1217.  
  1218. --
  1219. -- Table structure for table `contact_meeting_users`
  1220. --
  1221.  
  1222. CREATE TABLE `contact_meeting_users` (
  1223. `contact_meeting_id` int(11) NOT NULL,
  1224. `contact_user_id` int(11) NOT NULL
  1225. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  1226.  
  1227. --
  1228. -- Dumping data for table `contact_meeting_users`
  1229. --
  1230.  
  1231. INSERT INTO `contact_meeting_users` (`contact_meeting_id`, `contact_user_id`) VALUES
  1232. (1, 15);
  1233.  
  1234. -- --------------------------------------------------------
  1235.  
  1236. --
  1237. -- Table structure for table `contact_notes`
  1238. --
  1239.  
  1240. CREATE TABLE `contact_notes` (
  1241. `contact_note_id` int(11) NOT NULL,
  1242. `contact_note_name` varchar(100) NOT NULL,
  1243. `contact_note_description` text,
  1244. `contact_note_date` datetime DEFAULT NULL,
  1245. `contact_note_user` varchar(100) DEFAULT NULL,
  1246. `contact_id` int(11) NOT NULL
  1247. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  1248.  
  1249. -- --------------------------------------------------------
  1250.  
  1251. --
  1252. -- Table structure for table `contact_tasks`
  1253. --
  1254.  
  1255. CREATE TABLE `contact_tasks` (
  1256. `contact_task_id` int(11) NOT NULL,
  1257. `contact_task_name` varchar(100) NOT NULL,
  1258. `contact_task_description` varchar(45) DEFAULT NULL,
  1259. `contact_task_status` int(11) DEFAULT NULL,
  1260. `contact_task_due_date` datetime DEFAULT NULL,
  1261. `contact_task_date` datetime NOT NULL,
  1262. `contact_task_user` varchar(100) NOT NULL,
  1263. `contact_id` int(11) NOT NULL
  1264. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  1265.  
  1266. --
  1267. -- Dumping data for table `contact_tasks`
  1268. --
  1269.  
  1270. INSERT INTO `contact_tasks` (`contact_task_id`, `contact_task_name`, `contact_task_description`, `contact_task_status`, `contact_task_due_date`, `contact_task_date`, `contact_task_user`, `contact_id`) VALUES
  1271. (1, 'asd', 'qwe', 22, '2017-08-29 00:00:00', '2017-08-29 03:30:34', 'ulala', 31);
  1272.  
  1273. -- --------------------------------------------------------
  1274.  
  1275. --
  1276. -- Table structure for table `crm_campaigns`
  1277. --
  1278.  
  1279. CREATE TABLE `crm_campaigns` (
  1280. `campaign_id` int(11) NOT NULL,
  1281. `campaign_name` varchar(100) NOT NULL,
  1282. `campaign_description` text,
  1283. `campaign_start_date` date DEFAULT NULL,
  1284. `campaign_end_date` date DEFAULT NULL,
  1285. `campaign_status` enum('0','1') DEFAULT '0' COMMENT '0=Non aktif\n1= Aktif\n\n',
  1286. `campaign_number` int(11) DEFAULT NULL,
  1287. `campaign_percentage_response` double DEFAULT NULL,
  1288. `campaign_budget_cost_currency` enum('1','2') DEFAULT NULL COMMENT '1= USD\n2 = IDR ',
  1289. `campaign_budget_cost` double DEFAULT NULL,
  1290. `campaign_actual_cost_currency` enum('1','2') DEFAULT NULL COMMENT '1= USD\n2 = IDR ',
  1291. `campaign_actual_cost` double DEFAULT NULL,
  1292. `campaign_expected_revenue_currency` enum('1','2') DEFAULT NULL COMMENT '1= USD\n2 = IDR ',
  1293. `campaign_expected_revenue` double DEFAULT NULL,
  1294. `user_created` varchar(100) DEFAULT NULL,
  1295. `user_created_date` datetime DEFAULT NULL,
  1296. `user_modified` varchar(100) DEFAULT NULL,
  1297. `user_modified_date` datetime DEFAULT NULL
  1298. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  1299.  
  1300. --
  1301. -- Dumping data for table `crm_campaigns`
  1302. --
  1303.  
  1304. INSERT INTO `crm_campaigns` (`campaign_id`, `campaign_name`, `campaign_description`, `campaign_start_date`, `campaign_end_date`, `campaign_status`, `campaign_number`, `campaign_percentage_response`, `campaign_budget_cost_currency`, `campaign_budget_cost`, `campaign_actual_cost_currency`, `campaign_actual_cost`, `campaign_expected_revenue_currency`, `campaign_expected_revenue`, `user_created`, `user_created_date`, `user_modified`, `user_modified_date`) VALUES
  1305. (9, 'asd', 'asd', '2017-08-07', '2017-09-30', '1', 1, 2, '1', 5000, '2', 5000, '1', 5000, 'Admin', '2017-08-23 20:19:04', NULL, NULL),
  1306. (10, 'asd', 'asd', '2017-08-07', '2017-08-07', '1', 1, 2, '1', 5000, '2', 5000, '1', 5000, 'Admin', '2017-08-26 07:25:12', NULL, NULL),
  1307. (11, 'asd', 'asd', '2017-09-04', '2017-09-04', '1', 1, 5, '1', 5000, '1', 5000, '1', 5000, 'Admin', '2017-08-26 09:19:42', NULL, '2017-08-29 03:03:01');
  1308.  
  1309. -- --------------------------------------------------------
  1310.  
  1311. --
  1312. -- Table structure for table `crm_companies`
  1313. --
  1314.  
  1315. CREATE TABLE `crm_companies` (
  1316. `company_id` int(11) NOT NULL,
  1317. `customer_code` varchar(50) DEFAULT NULL,
  1318. `company_name` varchar(200) DEFAULT NULL,
  1319. `company_parent` int(11) DEFAULT NULL,
  1320. `company_office_phone` varchar(30) DEFAULT NULL,
  1321. `company_industry` int(11) NOT NULL,
  1322. `company_email` varchar(100) DEFAULT NULL,
  1323. `company_fax` varchar(30) DEFAULT NULL,
  1324. `company_employee` int(11) DEFAULT NULL,
  1325. `company_revenue` double DEFAULT NULL,
  1326. `company_website` varchar(100) DEFAULT NULL,
  1327. `company_address1` varchar(255) DEFAULT NULL,
  1328. `company_address2` varchar(255) DEFAULT NULL,
  1329. `company_city` varchar(100) DEFAULT NULL,
  1330. `company_state` varchar(100) DEFAULT NULL,
  1331. `company_zip_code` varchar(10) DEFAULT NULL,
  1332. `company_country` varchar(50) DEFAULT NULL,
  1333. `company_billing_address1` varchar(255) DEFAULT NULL,
  1334. `company_billing_address2` varchar(255) DEFAULT NULL,
  1335. `company_billing_city` varchar(100) DEFAULT NULL,
  1336. `company_billing_state` varchar(100) DEFAULT NULL,
  1337. `company_billing_zip_code` varchar(10) DEFAULT NULL,
  1338. `company_billing_country` varchar(50) DEFAULT NULL,
  1339. `company_right_permission` enum('1','2','3') DEFAULT '1' COMMENT '1=owner2=group23=everyone',
  1340. `user_created` varchar(100) DEFAULT NULL,
  1341. `user_created_date` datetime DEFAULT NULL,
  1342. `user_modified` varchar(100) DEFAULT NULL,
  1343. `user_modified_date` datetime DEFAULT NULL,
  1344. `company_groups` int(11) DEFAULT NULL,
  1345. `company_customer` enum('0','1') DEFAULT '0' COMMENT '0=Not Customer\n1=Customer\n'
  1346. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  1347.  
  1348. --
  1349. -- Dumping data for table `crm_companies`
  1350. --
  1351.  
  1352. INSERT INTO `crm_companies` (`company_id`, `customer_code`, `company_name`, `company_parent`, `company_office_phone`, `company_industry`, `company_email`, `company_fax`, `company_employee`, `company_revenue`, `company_website`, `company_address1`, `company_address2`, `company_city`, `company_state`, `company_zip_code`, `company_country`, `company_billing_address1`, `company_billing_address2`, `company_billing_city`, `company_billing_state`, `company_billing_zip_code`, `company_billing_country`, `company_right_permission`, `user_created`, `user_created_date`, `user_modified`, `user_modified_date`, `company_groups`, `company_customer`) VALUES
  1353. (24, NULL, 'Koding Next', NULL, '123', 2, 'asd@asd.asd', NULL, NULL, NULL, NULL, 'asd', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '1', NULL, NULL, NULL, NULL, NULL, '0'),
  1354. (25, NULL, 'KN', NULL, NULL, 2, 'asd@asd.asd', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '1', NULL, NULL, NULL, NULL, NULL, '0'),
  1355. (26, NULL, 'jhg', NULL, NULL, 2, 'asd@asd.asd', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '1', NULL, NULL, NULL, NULL, NULL, '0'),
  1356. (27, NULL, 'ulala', NULL, NULL, 2, 'asd@asd.asd', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '1', NULL, NULL, NULL, NULL, NULL, '0'),
  1357. (28, NULL, 'Contoh', NULL, NULL, 2, 'asd@asd.asd', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '1', NULL, NULL, NULL, NULL, NULL, '0'),
  1358. (29, NULL, 'Contoh 2', NULL, NULL, 2, 'asd@asd.asd', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '1', NULL, NULL, NULL, NULL, NULL, '0'),
  1359. (30, NULL, 'Contoh 3', NULL, NULL, 2, 'asd@asd.asd', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '1', NULL, NULL, NULL, NULL, NULL, '0'),
  1360. (31, NULL, 'Contoh 4', NULL, NULL, 2, 'asd@asd.asd', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '1', NULL, NULL, NULL, NULL, NULL, '0'),
  1361. (32, NULL, 'lenovo', NULL, NULL, 2, 'asd@asd.asd', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '1', NULL, NULL, NULL, NULL, NULL, '0'),
  1362. (33, NULL, 'Contoh 2', NULL, NULL, 2, 'asd@asd.asd', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '1', NULL, NULL, NULL, NULL, NULL, '0'),
  1363. (34, NULL, 'asus', NULL, NULL, 2, 'asd@asd.asd', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '1', NULL, NULL, NULL, NULL, NULL, '0'),
  1364. (35, NULL, 'msi', NULL, NULL, 2, 'asd@asd.asd', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '1', NULL, NULL, NULL, NULL, NULL, '0'),
  1365. (36, NULL, 'ASUS ROG', NULL, NULL, 2, 'asd@asd.asd', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '1', NULL, NULL, NULL, NULL, NULL, '0'),
  1366. (37, NULL, 'LG', NULL, NULL, 2, 'asd@asd.asd', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '1', NULL, NULL, NULL, NULL, NULL, '0'),
  1367. (38, NULL, 'asdasd', NULL, NULL, 2, 'asd@asd.asd', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '1', NULL, NULL, NULL, NULL, NULL, '0'),
  1368. (39, NULL, 'Noval Corp', NULL, NULL, 2, 'asd@asd.asd', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '1', NULL, NULL, NULL, NULL, NULL, '0'),
  1369. (40, NULL, 'asdasd5', NULL, NULL, 2, 'asd@asd.asd', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '1', NULL, NULL, NULL, NULL, NULL, '0'),
  1370. (41, NULL, 'asd baru', NULL, NULL, 2, 'asd@asd.asd', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '1', NULL, NULL, NULL, NULL, NULL, '0'),
  1371. (42, NULL, 'asd baru lagi', NULL, NULL, 2, 'asd@asd.asd', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '1', NULL, NULL, NULL, NULL, NULL, '0'),
  1372. (43, NULL, 'lalallalalalalalala pusing', NULL, NULL, 2, 'asd@asd.asd', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '1', NULL, NULL, NULL, NULL, NULL, '0'),
  1373. (44, NULL, 'ulala houp comp', NULL, NULL, 2, 'asd@asd.asd', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '1', NULL, NULL, NULL, NULL, NULL, '0'),
  1374. (45, NULL, 'asd baru lagi nih hahaha', NULL, NULL, 2, 'asd@asd.asd', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '1', NULL, NULL, NULL, NULL, NULL, '0'),
  1375. (46, NULL, 'hahahahahaha', NULL, NULL, 2, 'asd@asd.asd', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '1', NULL, NULL, NULL, NULL, NULL, '0'),
  1376. (47, NULL, 'company coba skip', NULL, NULL, 2, 'asd@asd.asd', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '1', NULL, NULL, NULL, NULL, NULL, '0');
  1377.  
  1378. -- --------------------------------------------------------
  1379.  
  1380. --
  1381. -- Table structure for table `crm_contacts`
  1382. --
  1383.  
  1384. CREATE TABLE `crm_contacts` (
  1385. `contact_id` int(11) NOT NULL,
  1386. `company_id` int(11) NOT NULL,
  1387. `people_id` int(11) NOT NULL,
  1388. `contact_right_permission` enum('1','2','3') DEFAULT NULL,
  1389. `contact_groups` int(11) DEFAULT NULL
  1390. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  1391.  
  1392. --
  1393. -- Dumping data for table `crm_contacts`
  1394. --
  1395.  
  1396. INSERT INTO `crm_contacts` (`contact_id`, `company_id`, `people_id`, `contact_right_permission`, `contact_groups`) VALUES
  1397. (30, 24, 10, '2', NULL),
  1398. (31, 25, 43, '1', NULL),
  1399. (32, 26, 44, '1', NULL),
  1400. (33, 27, 45, '1', NULL),
  1401. (37, 28, 50, '1', NULL),
  1402. (44, 29, 49, '1', NULL),
  1403. (45, 30, 48, '1', NULL),
  1404. (46, 31, 46, '1', NULL),
  1405. (63, 32, 52, '1', NULL),
  1406. (64, 33, 51, '1', NULL),
  1407. (66, 34, 53, '1', NULL),
  1408. (73, 35, 54, '1', NULL),
  1409. (74, 36, 55, '1', NULL),
  1410. (75, 37, 56, '1', NULL),
  1411. (76, 38, 63, '1', NULL),
  1412. (77, 39, 64, '1', NULL),
  1413. (78, 40, 68, '1', NULL),
  1414. (79, 41, 70, '1', NULL),
  1415. (80, 42, 71, '1', NULL),
  1416. (81, 43, 72, '1', NULL),
  1417. (82, 44, 73, '1', NULL),
  1418. (83, 45, 74, '1', NULL),
  1419. (84, 46, 75, '1', NULL),
  1420. (85, 46, 69, '1', NULL),
  1421. (86, 46, 77, '1', NULL),
  1422. (87, 41, 78, '1', NULL),
  1423. (88, 41, 79, '1', NULL),
  1424. (89, 41, 76, '1', NULL),
  1425. (90, 41, 81, '1', NULL),
  1426. (91, 41, 82, '1', NULL),
  1427. (92, 24, 80, '1', NULL),
  1428. (93, 45, 84, '1', NULL),
  1429. (94, 45, 85, '1', NULL),
  1430. (95, 45, 86, '1', NULL),
  1431. (96, 45, 87, '1', NULL),
  1432. (97, 41, 88, '1', NULL),
  1433. (98, 47, 89, '1', NULL);
  1434.  
  1435. -- --------------------------------------------------------
  1436.  
  1437. --
  1438. -- Table structure for table `crm_groups`
  1439. --
  1440.  
  1441. CREATE TABLE `crm_groups` (
  1442. `group_id` int(11) NOT NULL,
  1443. `group_name` varchar(100) NOT NULL,
  1444. `group_status` enum('0','1') DEFAULT '1' COMMENT '0= Non Aktif\n1= Aktif\n',
  1445. `user_created` varchar(100) DEFAULT NULL,
  1446. `user_created_date` datetime DEFAULT NULL,
  1447. `user_modified` varchar(45) DEFAULT NULL,
  1448. `user_modified_date` datetime DEFAULT NULL
  1449. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  1450.  
  1451. --
  1452. -- Dumping data for table `crm_groups`
  1453. --
  1454.  
  1455. INSERT INTO `crm_groups` (`group_id`, `group_name`, `group_status`, `user_created`, `user_created_date`, `user_modified`, `user_modified_date`) VALUES
  1456. (1, 'Dev', '1', 'Admin', '2017-08-11 15:58:52', 'Admin', '2017-08-18 10:49:43'),
  1457. (3, 'Dev', '1', 'Admin', '2017-08-11 15:58:52', 'Admin', '2017-08-18 10:49:43');
  1458.  
  1459. -- --------------------------------------------------------
  1460.  
  1461. --
  1462. -- Table structure for table `crm_industry`
  1463. --
  1464.  
  1465. CREATE TABLE `crm_industry` (
  1466. `industry_id` int(11) NOT NULL,
  1467. `industry_name` varchar(100) NOT NULL,
  1468. `user_created` varchar(100) DEFAULT NULL,
  1469. `user_created_date` datetime DEFAULT NULL,
  1470. `user_modified` varchar(100) DEFAULT NULL,
  1471. `user_modified_date` datetime DEFAULT NULL
  1472. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  1473.  
  1474. --
  1475. -- Dumping data for table `crm_industry`
  1476. --
  1477.  
  1478. INSERT INTO `crm_industry` (`industry_id`, `industry_name`, `user_created`, `user_created_date`, `user_modified`, `user_modified_date`) VALUES
  1479. (2, 'Edukasi', 'Admin', '2017-08-11 16:04:17', 'Admin', '2017-08-18 10:51:53');
  1480.  
  1481. -- --------------------------------------------------------
  1482.  
  1483. --
  1484. -- Table structure for table `crm_invoices`
  1485. --
  1486.  
  1487. CREATE TABLE `crm_invoices` (
  1488. `invoice_id` int(11) NOT NULL,
  1489. `company_id` int(11) NOT NULL,
  1490. `invoice_ref` varchar(45) DEFAULT NULL,
  1491. `invoice_profile` varchar(100) DEFAULT NULL,
  1492. `invoice_type` enum('1','2') NOT NULL COMMENT '1=One Time\n2=Recurring\n',
  1493. `invoice_start_date` datetime DEFAULT NULL,
  1494. `invoice_end_date` datetime DEFAULT NULL COMMENT ' ',
  1495. `invoice_repeat_value` int(11) DEFAULT NULL,
  1496. `invoice_repeat_type` enum('1','2','3') DEFAULT NULL COMMENT '1 =Day\n2= week\n3=Month\n',
  1497. `invoice_expired` enum('0','1') DEFAULT NULL COMMENT '0=expired\n1=never expired\n',
  1498. `term_id` int(11) NOT NULL
  1499. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  1500.  
  1501. --
  1502. -- Dumping data for table `crm_invoices`
  1503. --
  1504.  
  1505. INSERT INTO `crm_invoices` (`invoice_id`, `company_id`, `invoice_ref`, `invoice_profile`, `invoice_type`, `invoice_start_date`, `invoice_end_date`, `invoice_repeat_value`, `invoice_repeat_type`, `invoice_expired`, `term_id`) VALUES
  1506. (29, 24, 'asd123', '-', '1', NULL, NULL, NULL, '1', '1', 1),
  1507. (30, 24, NULL, '-', '1', NULL, NULL, NULL, '1', '1', 1),
  1508. (31, 41, 'OPR-081700002', '-', '1', NULL, NULL, NULL, '1', '1', 1),
  1509. (32, 24, NULL, '-', '1', NULL, NULL, NULL, '1', '1', 1),
  1510. (33, 24, NULL, '-', '1', NULL, NULL, NULL, '1', '1', 1),
  1511. (34, 24, NULL, '-', '1', NULL, NULL, NULL, '1', '1', 1),
  1512. (35, 24, NULL, '-', '1', NULL, NULL, NULL, '1', '1', 1),
  1513. (36, 24, NULL, '-', '1', NULL, NULL, NULL, '1', '1', 1),
  1514. (37, 24, NULL, '-', '1', NULL, NULL, NULL, '1', '1', 1),
  1515. (38, 24, NULL, '-', '1', NULL, NULL, NULL, '1', '1', 1),
  1516. (39, 24, NULL, '-', '1', NULL, NULL, NULL, '1', '1', 1),
  1517. (40, 24, NULL, '-', '1', NULL, NULL, NULL, '1', '1', 1),
  1518. (41, 24, NULL, '-', '1', NULL, NULL, NULL, '1', '1', 1),
  1519. (42, 24, NULL, '-', '1', NULL, NULL, NULL, '1', '1', 1),
  1520. (43, 24, NULL, '-', '1', NULL, NULL, NULL, '1', '1', 1),
  1521. (44, 24, NULL, '-', '1', NULL, NULL, NULL, '1', '1', 1),
  1522. (45, 24, NULL, '-', '1', NULL, NULL, NULL, '1', '1', 1),
  1523. (46, 24, NULL, '-', '1', NULL, NULL, NULL, '1', '1', 1),
  1524. (47, 24, NULL, '-', '1', NULL, NULL, NULL, '1', '1', 1),
  1525. (48, 24, NULL, '-', '1', NULL, NULL, NULL, '1', '1', 1),
  1526. (49, 24, NULL, '-', '1', NULL, NULL, NULL, '1', '1', 1),
  1527. (50, 24, NULL, '-', '1', NULL, NULL, NULL, '1', '1', 1),
  1528. (51, 24, NULL, '-', '1', NULL, NULL, NULL, '1', '1', 1),
  1529. (52, 24, NULL, '-', '1', NULL, NULL, NULL, '1', '1', 1),
  1530. (53, 24, NULL, '-', '1', NULL, NULL, NULL, '1', '1', 1),
  1531. (54, 24, NULL, '-', '1', NULL, NULL, NULL, '1', '1', 1),
  1532. (55, 24, NULL, '-', '1', NULL, NULL, NULL, '1', '1', 1);
  1533.  
  1534. -- --------------------------------------------------------
  1535.  
  1536. --
  1537. -- Table structure for table `crm_invoices_products`
  1538. --
  1539.  
  1540. CREATE TABLE `crm_invoices_products` (
  1541. `invoice_detail_id` int(11) NOT NULL,
  1542. `product_id` int(11) NOT NULL,
  1543. `qty` int(11) DEFAULT NULL,
  1544. `price` double DEFAULT NULL,
  1545. `is_campaign` enum('0','1') NOT NULL
  1546. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  1547.  
  1548. --
  1549. -- Dumping data for table `crm_invoices_products`
  1550. --
  1551.  
  1552. INSERT INTO `crm_invoices_products` (`invoice_detail_id`, `product_id`, `qty`, `price`, `is_campaign`) VALUES
  1553. (16, 2, 12, 123123, '0'),
  1554. (17, 5, 123, 123123, '0'),
  1555. (18, 2, 1, 1000, '1'),
  1556. (18, 5, 1, 1000, '1'),
  1557. (19, 5, 1, 1000, '0'),
  1558. (20, 5, 1, 1000, '0'),
  1559. (21, 5, 1, 1000, '0'),
  1560. (22, 5, 1, 1000, '0'),
  1561. (23, 5, 1, 1000, '0'),
  1562. (24, 5, 1, 1000, '0'),
  1563. (25, 5, 1, 1000, '0'),
  1564. (26, 5, 1, 1000, '0'),
  1565. (27, 5, 1, 1000, '0'),
  1566. (28, 5, 1, 1000, '0'),
  1567. (29, 5, 1, 1000, '0'),
  1568. (30, 5, 1, 1000, '0'),
  1569. (31, 5, 1, 1000, '0'),
  1570. (32, 5, 1, 1000, '0'),
  1571. (33, 5, 1, 1000, '0'),
  1572. (34, 5, 1, 1000, '0'),
  1573. (35, 5, 1, 1000, '0'),
  1574. (36, 5, 1, 1000, '0'),
  1575. (37, 5, 1, 1000, '0'),
  1576. (38, 5, 1, 1000, '0'),
  1577. (39, 5, 1, 1000, '0'),
  1578. (40, 5, 1, 1000, '0'),
  1579. (41, 5, 1, 1000, '0'),
  1580. (42, 5, 1, 1000, '0');
  1581.  
  1582. -- --------------------------------------------------------
  1583.  
  1584. --
  1585. -- Table structure for table `crm_invoice_details`
  1586. --
  1587.  
  1588. CREATE TABLE `crm_invoice_details` (
  1589. `invoice_detail_id` int(11) NOT NULL,
  1590. `invoice_code` varchar(50) NOT NULL,
  1591. `invoice_id` int(11) NOT NULL,
  1592. `invoice_date` datetime NOT NULL,
  1593. `invoice_due_date` datetime DEFAULT NULL,
  1594. `invoice_discount_type` enum('1','2') DEFAULT '1' COMMENT '1=Percentage2=Value',
  1595. `invoice_discount_value` double DEFAULT NULL,
  1596. `invoice_tax_percentage` double DEFAULT NULL,
  1597. `invoice_adjustment` double DEFAULT NULL,
  1598. `invoice_type` enum('1','2') DEFAULT NULL COMMENT '1=One time\n2=recurring\n',
  1599. `invoice_cycle` int(11) NOT NULL DEFAULT '1'
  1600. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  1601.  
  1602. --
  1603. -- Dumping data for table `crm_invoice_details`
  1604. --
  1605.  
  1606. INSERT INTO `crm_invoice_details` (`invoice_detail_id`, `invoice_code`, `invoice_id`, `invoice_date`, `invoice_due_date`, `invoice_discount_type`, `invoice_discount_value`, `invoice_tax_percentage`, `invoice_adjustment`, `invoice_type`, `invoice_cycle`) VALUES
  1607. (16, 'INV-081700001', 29, '2017-08-29 05:59:06', '2017-09-13 00:00:00', '1', 14, 11, 100, '1', 1),
  1608. (17, 'INV-081700002', 30, '2017-08-29 06:02:32', '2017-09-13 00:00:00', '1', 151, 11, 11, '1', 1),
  1609. (18, 'INV-081700003', 31, '2017-08-29 06:05:06', '2017-09-13 00:00:00', '2', 11, 1, 1, '1', 1),
  1610. (19, 'INV-091700004', 32, '2017-09-06 09:19:34', '2017-09-21 00:00:00', '1', 110, 1, 111, '1', 1),
  1611. (20, 'INV-091700005', 33, '2017-09-06 09:27:45', '2017-09-21 00:00:00', '1', 10, 1, 1, '1', 1),
  1612. (21, 'INV-091700006', 34, '2017-09-06 09:28:27', '2017-09-21 00:00:00', '1', 10, 1, 1, '1', 1),
  1613. (22, 'INV-091700007', 35, '2017-09-06 09:59:12', '2017-09-21 00:00:00', '1', 10, 1, 1, '1', 1),
  1614. (23, 'INV-091700008', 36, '2017-09-06 10:06:58', '2017-09-21 00:00:00', '1', 10, 1, 1, '1', 1),
  1615. (24, 'INV-091700009', 37, '2017-09-06 10:07:07', '2017-09-21 00:00:00', '1', 10, 1, 1, '1', 1),
  1616. (25, 'INV-091700010', 38, '2017-09-06 10:08:23', '2017-09-21 00:00:00', '1', 100, 10, 100, '1', 1),
  1617. (26, 'INV-091700011', 39, '2017-09-06 10:08:44', '2017-09-21 00:00:00', '1', 100, 10, 100, '1', 1),
  1618. (27, 'INV-091700012', 40, '2017-09-06 10:09:09', '2017-09-21 00:00:00', '1', 100, 10, 100, '1', 1),
  1619. (28, 'INV-091700013', 41, '2017-09-06 10:10:45', '2017-09-21 00:00:00', '1', 100, 10, 100, '1', 1),
  1620. (29, 'INV-091700014', 42, '2017-09-06 10:11:09', '2017-09-21 00:00:00', '1', 100, 10, 100, '1', 1),
  1621. (30, 'INV-091700015', 43, '2017-09-06 10:13:08', '2017-09-21 00:00:00', '1', 100, 10, 100, '1', 1),
  1622. (31, 'INV-091700016', 44, '2017-09-06 10:13:45', '2017-09-21 00:00:00', '1', 100, 10, 100, '1', 1),
  1623. (32, 'INV-091700017', 45, '2017-09-06 10:14:35', '2017-09-21 00:00:00', '1', 100, 10, 100, '1', 1),
  1624. (33, 'INV-091700018', 46, '2017-09-06 10:15:03', '2017-09-21 00:00:00', '1', 100, 10, 100, '1', 1),
  1625. (34, 'INV-091700019', 47, '2017-09-06 10:16:16', '2017-09-21 00:00:00', '1', 100, 10, 100, '1', 1),
  1626. (35, 'INV-091700020', 48, '2017-09-06 10:18:15', '2017-09-21 00:00:00', '1', 100, 10, 100, '1', 1),
  1627. (36, 'INV-091700021', 49, '2017-09-06 10:19:35', '2017-09-21 00:00:00', '1', 100, 10, 100, '1', 1),
  1628. (37, 'INV-091700022', 50, '2017-09-06 10:20:07', '2017-09-21 00:00:00', '1', 100, 10, 100, '1', 1),
  1629. (38, 'INV-091700023', 51, '2017-09-06 10:24:37', '2017-09-21 00:00:00', '1', 100, 10, 100, '1', 1),
  1630. (39, 'INV-091700024', 52, '2017-09-06 10:32:28', '2017-09-21 00:00:00', '1', 120, 12, 1000, '1', 1),
  1631. (40, 'INV-091700025', 53, '2017-09-06 10:33:10', '2017-09-21 00:00:00', '1', 120, 12, 1000, '1', 1),
  1632. (41, 'INV-091700026', 54, '2017-09-06 10:34:30', '2017-09-21 00:00:00', '1', 120, 12, 1000, '1', 1),
  1633. (42, 'INV-091700027', 55, '2017-09-06 10:34:56', '2017-09-21 00:00:00', '1', 120, 12, 1000, '1', 1);
  1634.  
  1635. -- --------------------------------------------------------
  1636.  
  1637. --
  1638. -- Table structure for table `crm_leads`
  1639. --
  1640.  
  1641. CREATE TABLE `crm_leads` (
  1642. `lead_id` int(11) NOT NULL,
  1643. `lead_label` int(11) NOT NULL,
  1644. `people_id` int(11) NOT NULL,
  1645. `lead_right_permission` enum('1','2','3') DEFAULT NULL COMMENT '1=owner\n2=group2\n3=everyone\n\n',
  1646. `lead_groups` int(11) DEFAULT NULL,
  1647. `lead_status` enum('1','2','3') NOT NULL DEFAULT '1' COMMENT '1= Lead\n2 = Convert\n3 =Junk\n',
  1648. `user_created` varchar(100) DEFAULT NULL,
  1649. `user_created_date` datetime DEFAULT NULL,
  1650. `user_modified` varchar(100) DEFAULT NULL,
  1651. `user_modified_date` datetime DEFAULT NULL
  1652. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  1653.  
  1654. --
  1655. -- Dumping data for table `crm_leads`
  1656. --
  1657.  
  1658. INSERT INTO `crm_leads` (`lead_id`, `lead_label`, `people_id`, `lead_right_permission`, `lead_groups`, `lead_status`, `user_created`, `user_created_date`, `user_modified`, `user_modified_date`) VALUES
  1659. (5, 3, 10, '2', NULL, '2', 'Admin', '2017-08-11 17:36:47', 'Admin', '2017-08-19 10:03:25'),
  1660. (38, 3, 43, '1', NULL, '2', 'ulala', '2017-08-23 19:50:08', NULL, NULL),
  1661. (39, 3, 44, '1', NULL, '2', 'ulala', '2017-08-23 19:55:36', NULL, NULL),
  1662. (40, 3, 45, '1', NULL, '2', 'ulala', '2017-08-23 19:58:10', NULL, NULL),
  1663. (41, 3, 46, '1', NULL, '2', 'ulala', '2017-08-24 03:23:39', NULL, NULL),
  1664. (42, 3, 47, '1', NULL, '3', 'ulala', '2017-08-24 03:24:42', NULL, NULL),
  1665. (43, 3, 48, '1', NULL, '2', 'ulala', '2017-08-24 03:32:59', NULL, NULL),
  1666. (44, 3, 49, '1', NULL, '2', 'ulala', '2017-08-24 03:34:48', NULL, NULL),
  1667. (45, 3, 50, '1', NULL, '2', 'ulala', '2017-08-24 03:36:58', NULL, NULL),
  1668. (46, 3, 51, '1', NULL, '2', 'ulala', '2017-08-24 03:55:19', NULL, NULL),
  1669. (47, 3, 52, '1', NULL, '2', 'ulala', '2017-08-24 04:23:39', NULL, NULL),
  1670. (48, 3, 53, '1', NULL, '2', 'ulala', '2017-08-24 04:42:03', NULL, NULL),
  1671. (49, 3, 54, '1', NULL, '2', 'ulala', '2017-08-24 04:43:47', NULL, NULL),
  1672. (50, 3, 55, '1', NULL, '2', 'ulala', '2017-08-24 07:01:19', NULL, NULL),
  1673. (51, 3, 56, '1', NULL, '2', 'ulala', '2017-08-24 08:32:45', NULL, NULL),
  1674. (53, 3, 61, '1', 3, '1', 'admin', '2017-08-24 15:36:47', NULL, NULL),
  1675. (54, 3, 63, '1', NULL, '2', 'ulala', '2017-08-24 15:39:47', NULL, NULL),
  1676. (55, 3, 64, '1', NULL, '2', 'ulala', '2017-08-24 15:46:49', NULL, NULL),
  1677. (56, 3, 68, '1', NULL, '2', 'ulala', '2017-08-24 16:13:02', NULL, NULL),
  1678. (57, 3, 69, '1', NULL, '2', 'ulala', '2017-08-24 16:16:40', NULL, NULL),
  1679. (58, 3, 70, '1', NULL, '2', 'ulala', '2017-08-24 16:17:16', NULL, NULL),
  1680. (59, 3, 71, '1', NULL, '2', 'ulala', '2017-08-24 16:22:11', NULL, NULL),
  1681. (60, 3, 72, '1', NULL, '2', 'ulala', '2017-08-24 16:29:54', NULL, NULL),
  1682. (61, 3, 73, '1', NULL, '2', 'ulala', '2017-08-24 16:43:35', NULL, NULL),
  1683. (62, 3, 74, '1', NULL, '2', 'ulala', '2017-08-24 16:51:22', NULL, NULL),
  1684. (63, 3, 75, '1', NULL, '2', 'ulala', '2017-08-24 16:55:43', NULL, NULL),
  1685. (64, 3, 76, '1', NULL, '2', 'ulala', '2017-08-26 03:32:52', NULL, NULL),
  1686. (65, 3, 77, '1', NULL, '2', 'ulala', '2017-08-26 03:33:18', NULL, NULL),
  1687. (66, 3, 78, '1', NULL, '2', 'ulala', '2017-08-26 03:43:50', NULL, NULL),
  1688. (67, 3, 79, '1', NULL, '2', 'ulala', '2017-08-26 03:50:15', NULL, NULL),
  1689. (68, 3, 80, '1', NULL, '2', 'ulala', '2017-08-26 04:15:28', NULL, NULL),
  1690. (69, 3, 81, '1', NULL, '3', 'ulala', '2017-08-26 04:15:59', 'ulala', '2017-08-26 04:24:33'),
  1691. (70, 3, 82, '1', NULL, '2', 'ulala', '2017-08-26 05:04:52', NULL, NULL),
  1692. (71, 3, 83, '1', NULL, '1', 'ulala', '2017-08-26 07:08:14', NULL, NULL),
  1693. (72, 3, 84, '1', NULL, '2', 'ulala', '2017-08-26 08:48:30', NULL, NULL),
  1694. (73, 3, 85, '1', NULL, '2', 'ulala', '2017-08-26 08:55:39', NULL, NULL),
  1695. (74, 3, 86, '1', NULL, '2', 'ulala', '2017-08-26 09:07:23', NULL, NULL),
  1696. (75, 3, 87, '1', NULL, '2', 'ulala', '2017-08-27 19:18:16', NULL, NULL),
  1697. (76, 3, 88, '1', NULL, '2', 'ulala', '2017-08-27 19:19:47', NULL, NULL),
  1698. (77, 3, 89, '1', NULL, '2', 'ulala', '2017-08-28 11:50:28', 'ulala', '2017-08-28 11:51:25');
  1699.  
  1700. -- --------------------------------------------------------
  1701.  
  1702. --
  1703. -- Table structure for table `crm_lead_label`
  1704. --
  1705.  
  1706. CREATE TABLE `crm_lead_label` (
  1707. `lead_label_id` int(11) NOT NULL,
  1708. `lead_label_name` varchar(100) NOT NULL,
  1709. `lead_label_status_score` int(11) NOT NULL COMMENT '0-100'
  1710. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  1711.  
  1712. --
  1713. -- Dumping data for table `crm_lead_label`
  1714. --
  1715.  
  1716. INSERT INTO `crm_lead_label` (`lead_label_id`, `lead_label_name`, `lead_label_status_score`) VALUES
  1717. (3, 'Hahaha', 50);
  1718.  
  1719. -- --------------------------------------------------------
  1720.  
  1721. --
  1722. -- Table structure for table `crm_modules`
  1723. --
  1724.  
  1725. CREATE TABLE `crm_modules` (
  1726. `module_id` int(11) NOT NULL,
  1727. `module_name` varchar(100) NOT NULL,
  1728. `module_route` varchar(225) NOT NULL,
  1729. `module_order` int(11) DEFAULT NULL
  1730. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  1731.  
  1732. --
  1733. -- Dumping data for table `crm_modules`
  1734. --
  1735.  
  1736. INSERT INTO `crm_modules` (`module_id`, `module_name`, `module_route`, `module_order`) VALUES
  1737. (2, 'leads', 'leads', NULL),
  1738. (3, 'opportunities', 'opportunities', NULL),
  1739. (4, 'companies', 'companies', NULL),
  1740. (5, 'contacts', 'contacts', NULL),
  1741. (6, 'products', 'products', NULL),
  1742. (7, 'campaigns', 'campaigns', NULL);
  1743.  
  1744. -- --------------------------------------------------------
  1745.  
  1746. --
  1747. -- Table structure for table `crm_opportunities`
  1748. --
  1749.  
  1750. CREATE TABLE `crm_opportunities` (
  1751. `opportunity_id` int(11) NOT NULL,
  1752. `opportunity_code` varchar(100) DEFAULT NULL,
  1753. `opportunity_name` varchar(45) DEFAULT NULL,
  1754. `opportunity_stage_id` int(11) NOT NULL,
  1755. `company_id` int(11) NOT NULL,
  1756. `opportunity_amount` double DEFAULT NULL,
  1757. `opportunity_closing_date` datetime DEFAULT NULL,
  1758. `opportunity_service` enum('1','2') DEFAULT NULL COMMENT '1= Product\n2=Campaign\n',
  1759. `opportunity_campaign_id` int(11) DEFAULT NULL
  1760. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  1761.  
  1762. --
  1763. -- Dumping data for table `crm_opportunities`
  1764. --
  1765.  
  1766. INSERT INTO `crm_opportunities` (`opportunity_id`, `opportunity_code`, `opportunity_name`, `opportunity_stage_id`, `company_id`, `opportunity_amount`, `opportunity_closing_date`, `opportunity_service`, `opportunity_campaign_id`) VALUES
  1767. (5, NULL, 'test', 2, 24, 9900, '2017-08-07 06:03:31', '1', NULL),
  1768. (6, NULL, 'test', 2, 25, 9900, '2017-08-07 06:03:31', '1', NULL),
  1769. (7, NULL, 'test', 2, 26, 9900, '2017-08-07 06:03:31', '1', NULL),
  1770. (8, NULL, 'test', 2, 27, 9900, '2017-08-07 06:03:31', '1', NULL),
  1771. (9, NULL, 'test', 2, 28, 9900, '2017-08-07 06:03:31', '1', NULL),
  1772. (10, NULL, 'test', 2, 29, 9900, '2017-08-07 06:03:31', '1', NULL),
  1773. (11, NULL, 'test', 2, 30, 9900, '2017-08-07 06:03:31', '1', NULL),
  1774. (12, NULL, 'test', 2, 31, 9900, '2017-08-07 06:03:31', '1', NULL),
  1775. (13, NULL, 'test', 2, 32, 9900, '2017-08-07 06:03:31', '1', NULL),
  1776. (14, NULL, 'test', 2, 33, 9900, '2017-08-07 06:03:31', '1', NULL),
  1777. (15, NULL, 'test', 2, 34, 9900, '2017-08-07 06:03:31', '1', NULL),
  1778. (16, NULL, 'asd asd', 2, 35, 1234, '2017-08-21 00:00:00', '1', 9),
  1779. (17, NULL, 'penawaran asus rog', 2, 36, 555, '2017-08-29 00:00:00', '2', 9),
  1780. (18, NULL, 'penawaran yusuf', 2, 37, 50000, '2017-08-29 00:00:00', '1', 9),
  1781. (19, NULL, 'git merge error', 2, 38, 123456, '2017-08-30 00:00:00', '1', 9),
  1782. (20, NULL, 'Penawaran Laptop', 2, 39, 300000, '2017-08-23 00:00:00', '1', 9),
  1783. (21, NULL, 'qwer', 2, 40, 99988, '2017-08-29 00:00:00', '1', 9),
  1784. (22, NULL, 'apalah kamu ini', 2, 41, 444, '2017-08-29 00:00:00', '1', 9),
  1785. (23, NULL, 'gfds', 2, 42, 555, '2017-08-29 00:00:00', '1', 9),
  1786. (24, NULL, 'huuuuuuuuuuuuuuuuuuuu', 2, 43, 444, '2017-08-29 00:00:00', '1', 9),
  1787. (25, NULL, 'ulala', 2, 44, 999, '2017-08-29 00:00:00', '1', 9),
  1788. (27, NULL, 'cek', 2, 46, 2147483647, '2017-08-30 00:00:00', '1', 9),
  1789. (29, NULL, 'test', 2, 46, 9900, '2017-08-07 06:03:31', '1', NULL),
  1790. (35, NULL, 'test', 2, 24, 9900, '2017-08-07 06:03:31', '1', NULL),
  1791. (37, NULL, 'test ke 200', 2, 45, 12345, '2017-08-23 00:00:00', '1', NULL),
  1792. (38, NULL, 'test ke 201', 2, 45, 1234, '2017-08-29 00:00:00', '1', NULL),
  1793. (39, 'OPR-081700001', 'test hiphop', 2, 45, 1000, '2017-08-29 00:00:00', '1', 11),
  1794. (40, 'OPR-081700002', '205', 2, 41, 2000, '2017-08-30 00:00:00', '1', 11);
  1795.  
  1796. -- --------------------------------------------------------
  1797.  
  1798. --
  1799. -- Table structure for table `crm_opportunities_contacts`
  1800. --
  1801.  
  1802. CREATE TABLE `crm_opportunities_contacts` (
  1803. `opportunity_id` int(11) NOT NULL,
  1804. `contact_id` int(11) NOT NULL
  1805. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  1806.  
  1807. --
  1808. -- Dumping data for table `crm_opportunities_contacts`
  1809. --
  1810.  
  1811. INSERT INTO `crm_opportunities_contacts` (`opportunity_id`, `contact_id`) VALUES
  1812. (5, 30),
  1813. (6, 31),
  1814. (7, 32),
  1815. (8, 33),
  1816. (9, 37),
  1817. (10, 44),
  1818. (11, 45),
  1819. (12, 46),
  1820. (13, 63),
  1821. (14, 64),
  1822. (15, 66),
  1823. (16, 73),
  1824. (17, 74),
  1825. (18, 75),
  1826. (19, 76),
  1827. (20, 77),
  1828. (21, 78),
  1829. (22, 79),
  1830. (23, 80),
  1831. (24, 81),
  1832. (25, 82),
  1833. (27, 84),
  1834. (29, 86),
  1835. (35, 92),
  1836. (37, 94),
  1837. (38, 95),
  1838. (39, 96),
  1839. (40, 97);
  1840.  
  1841. -- --------------------------------------------------------
  1842.  
  1843. --
  1844. -- Table structure for table `crm_opportunities_products`
  1845. --
  1846.  
  1847. CREATE TABLE `crm_opportunities_products` (
  1848. `opportunity_id` int(11) NOT NULL,
  1849. `product_id` int(11) NOT NULL
  1850. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  1851.  
  1852. --
  1853. -- Dumping data for table `crm_opportunities_products`
  1854. --
  1855.  
  1856. INSERT INTO `crm_opportunities_products` (`opportunity_id`, `product_id`) VALUES
  1857. (5, 5),
  1858. (6, 5),
  1859. (7, 5),
  1860. (8, 5),
  1861. (9, 5),
  1862. (10, 5),
  1863. (11, 5),
  1864. (12, 5),
  1865. (13, 5),
  1866. (14, 5),
  1867. (15, 5),
  1868. (16, 2),
  1869. (17, 5),
  1870. (18, 2),
  1871. (19, 2),
  1872. (20, 5),
  1873. (21, 2),
  1874. (22, 5),
  1875. (23, 2),
  1876. (24, 5),
  1877. (25, 2),
  1878. (27, 2),
  1879. (29, 5),
  1880. (35, 5),
  1881. (37, 2),
  1882. (38, 2),
  1883. (39, 5),
  1884. (40, 2),
  1885. (40, 5);
  1886.  
  1887. -- --------------------------------------------------------
  1888.  
  1889. --
  1890. -- Table structure for table `crm_opportunity_stage`
  1891. --
  1892.  
  1893. CREATE TABLE `crm_opportunity_stage` (
  1894. `opportunity_stage_id` int(11) NOT NULL,
  1895. `opportunity_stage_name` varchar(100) DEFAULT NULL,
  1896. `opportunity_stage_status_score` int(11) NOT NULL
  1897. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  1898.  
  1899. --
  1900. -- Dumping data for table `crm_opportunity_stage`
  1901. --
  1902.  
  1903. INSERT INTO `crm_opportunity_stage` (`opportunity_stage_id`, `opportunity_stage_name`, `opportunity_stage_status_score`) VALUES
  1904. (2, 'Hahaha', 50),
  1905. (3, 'Finish', 100),
  1906. (4, 'snwed', 45);
  1907.  
  1908. -- --------------------------------------------------------
  1909.  
  1910. --
  1911. -- Table structure for table `crm_peoples`
  1912. --
  1913.  
  1914. CREATE TABLE `crm_peoples` (
  1915. `people_id` int(11) NOT NULL,
  1916. `people_salutation` enum('1','2','3') DEFAULT NULL,
  1917. `people_first_name` varchar(100) NOT NULL,
  1918. `people_last_name` varchar(100) DEFAULT NULL,
  1919. `people_job_title` varchar(100) DEFAULT NULL,
  1920. `people_company` varchar(100) DEFAULT NULL,
  1921. `people_department` varchar(100) DEFAULT NULL,
  1922. `people_email` varchar(100) DEFAULT NULL,
  1923. `people_office_phone` varchar(30) DEFAULT NULL,
  1924. `people_mobile_phone` varchar(30) DEFAULT NULL,
  1925. `people_industry` int(11) DEFAULT NULL,
  1926. `people_address1` varchar(255) DEFAULT NULL,
  1927. `people_address2` varchar(255) DEFAULT NULL,
  1928. `people_city` varchar(100) DEFAULT NULL,
  1929. `people_state` varchar(100) DEFAULT NULL,
  1930. `people_zip_code` varchar(10) DEFAULT NULL,
  1931. `people_country` varchar(50) DEFAULT NULL,
  1932. `people_source` int(11) NOT NULL,
  1933. `people_photo` varchar(100) DEFAULT NULL,
  1934. `people_status` enum('1','2') NOT NULL COMMENT '1=Lead\n2=Customer\n'
  1935. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  1936.  
  1937. --
  1938. -- Dumping data for table `crm_peoples`
  1939. --
  1940.  
  1941. INSERT INTO `crm_peoples` (`people_id`, `people_salutation`, `people_first_name`, `people_last_name`, `people_job_title`, `people_company`, `people_department`, `people_email`, `people_office_phone`, `people_mobile_phone`, `people_industry`, `people_address1`, `people_address2`, `people_city`, `people_state`, `people_zip_code`, `people_country`, `people_source`, `people_photo`, `people_status`) VALUES
  1942. (10, '1', 'Noval', 'Parinussa', 'Dev', 'Koding Next', 'Alalala', 'novalparinussa@gmail.com', '098773281', '08932719', 2, 'Kelapa gading', 'Puyuh Raya', 'Jakarta Utara', 'Jakarta', '123345', 'Indonesia', 2, NULL, '1'),
  1943. (43, '2', 'hg', 'hg', 'as', 'KN', NULL, 'a@a.a', NULL, '123', 2, 'hghjk', NULL, NULL, NULL, NULL, NULL, 2, NULL, '1'),
  1944. (44, '2', 'jhg', 'jhg', 'jhg', 'jhg', NULL, 'a@a.a', '123', '123', 2, 'jkl', NULL, NULL, NULL, NULL, NULL, 2, NULL, '1'),
  1945. (45, '2', 'kj', 'lk', 'kj', 'ulala', NULL, 'a@a.a', NULL, '999', 2, 'jalan', NULL, NULL, NULL, NULL, NULL, 2, NULL, '1'),
  1946. (46, '2', 'adi', 'putra', NULL, 'Contoh 4', NULL, 'asd@asd.asd', '132', '123', 2, 'asd', NULL, NULL, NULL, NULL, NULL, 2, NULL, '1'),
  1947. (47, '1', 'fais', 'fais', NULL, 'Contoh', NULL, 'fais@fais.com', '132', '123', 2, 'asd', NULL, NULL, NULL, NULL, NULL, 2, NULL, '1'),
  1948. (48, '1', 'qwerty', '123', NULL, 'Contoh 3', NULL, 'qwe@qwe.qwe', '132', '123', 2, 'asd', NULL, NULL, NULL, NULL, NULL, 2, NULL, '1'),
  1949. (49, '1', 'qwerty', '123', NULL, 'Contoh 2', NULL, 'qwe@qwe.qwe', '132', '123', 2, 'asd', NULL, NULL, NULL, NULL, NULL, 2, NULL, '1'),
  1950. (50, '1', 'qwerty', '123', NULL, 'Contoh', NULL, 'qwe@qwe.qwe', '132', '123', 2, 'asd', NULL, NULL, NULL, NULL, NULL, 2, NULL, '1'),
  1951. (51, '1', 'fais', '2', NULL, 'Contoh 2', NULL, 'fais@fais.com', '132', '123', 2, 'asd', NULL, NULL, NULL, NULL, NULL, 2, NULL, '1'),
  1952. (52, '1', 'noval', 'aja', NULL, 'lenovo', NULL, 'qwe@qwe.qwe', '132', '123', 2, 'asd', NULL, NULL, NULL, NULL, NULL, 2, NULL, '1'),
  1953. (53, '1', 'adi', 'ida', NULL, 'asus', NULL, 'adi@asus.com', '132', '123', 2, 'asd', NULL, NULL, NULL, NULL, NULL, 2, NULL, '1'),
  1954. (54, '1', 'asd', 'asd', NULL, 'msi', NULL, 'asd@asd.asd', '132', '123', 2, 'asd', NULL, NULL, NULL, NULL, NULL, 2, NULL, '1'),
  1955. (55, '2', 'ali', 'baba', NULL, 'ASUS ROG', NULL, 'ali@baba.com', '222', '222', 2, 'qwerty', NULL, NULL, NULL, NULL, NULL, 2, NULL, '1'),
  1956. (56, '2', 'yusuf', 'b', NULL, 'LG', NULL, 'yusuf@asd.asd', NULL, '999', 2, 'qwee', NULL, NULL, NULL, NULL, NULL, 2, NULL, '1'),
  1957. (60, '1', 'asd', 'asd', 'asd', 'asd', 'asd', 'asd@asd.asd', '123', '123', 2, 'asd', 'asd', 'asd', 'asd', '123', 'asd', 2, NULL, '1'),
  1958. (61, '1', 'asd', 'asd', 'asd', 'asd', 'asd', 'asd@asd.asd', '123', '123', 2, 'asd', 'asd', 'asd', 'asd', '123', 'asd', 2, NULL, '1'),
  1959. (63, '2', 'qwe', 'qwe', NULL, 'asdasd', NULL, 'asd@asd.asd', NULL, '123', 2, 'asd', NULL, NULL, NULL, NULL, NULL, 2, NULL, '1'),
  1960. (64, '2', 'Noval', 'Parinussa', NULL, 'Noval Corp', NULL, 'noval@bc.com', NULL, '085678765676', 2, 'Kelapa Puyuh', NULL, NULL, NULL, NULL, NULL, 2, NULL, '1'),
  1961. (68, '2', 'asd', 'asd', NULL, 'asdasd5', NULL, 'asd@asd.asd', NULL, '123', 2, 'asd', NULL, NULL, NULL, NULL, NULL, 2, NULL, '1'),
  1962. (69, '2', 'asd', 'asd', NULL, 'hahahahahaha', NULL, 'asd@asd.asd', NULL, '1234', 2, 'asdasd', NULL, NULL, NULL, NULL, NULL, 2, NULL, '1'),
  1963. (70, '1', 'lalala', 'luluu', NULL, 'asd baru', NULL, 'asd@asd.asd', NULL, '1234', 2, 'asdasd', NULL, NULL, NULL, NULL, NULL, 2, NULL, '1'),
  1964. (71, '1', 'asd', 'asd', NULL, 'asd baru lagi', NULL, 'asd@asd.asd', NULL, '1234', 2, 'asdasd', NULL, NULL, NULL, NULL, NULL, 2, NULL, '1'),
  1965. (72, '2', 'asd', 'asd', NULL, 'lalallalalalalalala pusing', NULL, 'asd@asd.asd', NULL, '23', 2, 'asadsd', NULL, NULL, NULL, NULL, NULL, 2, NULL, '1'),
  1966. (73, '1', 'ulala', 'houp', NULL, 'ulala houp comp', NULL, 'asd@asd.asd', NULL, '1234', 2, 'asdasd', NULL, NULL, NULL, NULL, NULL, 2, NULL, '1'),
  1967. (74, '1', 'asdasd', 'asdasd', NULL, 'asd baru lagi nih hahaha', NULL, 'asd@asd.asd', NULL, '1234', 2, 'asdasd', NULL, NULL, NULL, NULL, NULL, 2, NULL, '1'),
  1968. (75, '1', 'asdasd', 'asdasd', NULL, 'hahahahahaha', NULL, 'asd@asd.asd', NULL, '1234', 2, 'asdasd', NULL, NULL, NULL, NULL, NULL, 2, NULL, '1'),
  1969. (76, '2', 'qwerty', '123', NULL, 'asd baru', NULL, 'asd@asd.asd', NULL, '1234', 2, 'asdasd', NULL, NULL, NULL, NULL, NULL, 2, NULL, '1'),
  1970. (77, '1', 'lalala', 'ulala', NULL, 'asd baru', NULL, 'asd@asd.asd', NULL, '1234', 2, 'asdasd', NULL, NULL, NULL, NULL, NULL, 2, NULL, '1'),
  1971. (78, '1', 'test', 'aja', NULL, 'asd baru', NULL, 'asd@asd.asd', NULL, '1234', 2, 'asdasd', NULL, NULL, NULL, NULL, NULL, 2, NULL, '1'),
  1972. (79, '1', 'test', '200', NULL, 'asd baru', NULL, 'asd@asd.asd', NULL, '1234', 2, 'asdasd', NULL, NULL, NULL, NULL, NULL, 2, NULL, '1'),
  1973. (80, '2', 'ardi', 'asd', NULL, 'asd baru', NULL, 'asd@asd.asd', NULL, '1234', 2, 'asd', NULL, NULL, NULL, NULL, NULL, 2, NULL, '1'),
  1974. (81, '1', 'ardi', 'hairul', 'Gay Manager', 'Gay Property', NULL, 'asd@asd.asd', NULL, '1234', 2, 'asd', NULL, NULL, NULL, NULL, NULL, 2, NULL, '1'),
  1975. (82, '1', 'lllllllllllllllllllllllllllll', 'llllllllllllllllllllllll', NULL, 'asd baru', NULL, 'asd@asd.asd', NULL, '1234', 2, 'asd', NULL, NULL, NULL, NULL, NULL, 2, NULL, '1'),
  1976. (83, '1', 'hip', 'hop', NULL, 'asd baru', NULL, 'asd@asd.asd', NULL, '1234', 2, 'asd', NULL, NULL, NULL, NULL, NULL, 2, NULL, '1'),
  1977. (84, '1', '12345678', '91011', NULL, 'asd baru', NULL, 'asd@asd.asd', NULL, '1234', 2, 'asd', NULL, NULL, NULL, NULL, NULL, 2, NULL, '1'),
  1978. (85, '1', 'test ke 200', 'bener', NULL, 'asd baru', NULL, 'asd@asd.asd', NULL, '1234', 2, 'asd', NULL, NULL, NULL, NULL, NULL, 2, NULL, '1'),
  1979. (86, '1', 'test ke 201', 'bener', NULL, 'asd baru', NULL, 'asd@asd.asd', NULL, '1234', 2, 'asd', NULL, NULL, NULL, NULL, NULL, 2, NULL, '1'),
  1980. (87, '1', 'ulala', 'hiphop', NULL, 'asd baru', NULL, 'asd@asd.asd', NULL, '1234', 2, 'asd', NULL, NULL, NULL, NULL, NULL, 2, NULL, '1'),
  1981. (88, '1', 'test ke 205', 'lala', NULL, 'asd baru', NULL, 'asd@asd.asd', NULL, '1234', 2, 'asd', NULL, NULL, NULL, NULL, NULL, 2, NULL, '1'),
  1982. (89, '1', 'coba', 'skip', NULL, 'company coba skip', NULL, 'asd@asd.asd', NULL, '1234', 2, 'asd', NULL, NULL, NULL, NULL, NULL, 2, NULL, '1');
  1983.  
  1984. -- --------------------------------------------------------
  1985.  
  1986. --
  1987. -- Table structure for table `crm_products`
  1988. --
  1989.  
  1990. CREATE TABLE `crm_products` (
  1991. `product_id` int(11) NOT NULL,
  1992. `product_name` varchar(100) DEFAULT NULL,
  1993. `product_category_id` int(11) DEFAULT NULL,
  1994. `product_description` text,
  1995. `product_type` enum('1','2','3') NOT NULL COMMENT '1=Product\n2=Services\n3=Subscription\n',
  1996. `product_price_frequency` enum('1','2','3') DEFAULT NULL COMMENT '1=One Time\n2=Monthly\n3=Annually ',
  1997. `product_price` double NOT NULL DEFAULT '0',
  1998. `product_status` enum('0','1') NOT NULL DEFAULT '1' COMMENT '0=Non Aktif\n1=Aktif\n',
  1999. `user_created` varchar(100) DEFAULT NULL,
  2000. `user_created_date` datetime DEFAULT NULL,
  2001. `user_modified` varchar(100) DEFAULT NULL,
  2002. `user_modified_date` datetime DEFAULT NULL
  2003. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  2004.  
  2005. --
  2006. -- Dumping data for table `crm_products`
  2007. --
  2008.  
  2009. INSERT INTO `crm_products` (`product_id`, `product_name`, `product_category_id`, `product_description`, `product_type`, `product_price_frequency`, `product_price`, `product_status`, `user_created`, `user_created_date`, `user_modified`, `user_modified_date`) VALUES
  2010. (2, 'Edukasiku', 1, 'Lorem ipsum doler sit amet', '1', '1', 1000, '1', 'Admin', '2017-08-11 17:01:27', 'Admin', '2017-08-11 17:05:53'),
  2011. (5, 'Macbook Pro 2015', 1, 'Lorem ipsum doler sit amet', '1', '1', 1000, '1', 'Admin', '2017-08-18 10:54:07', 'Admin', '2017-08-18 10:54:18');
  2012.  
  2013. -- --------------------------------------------------------
  2014.  
  2015. --
  2016. -- Table structure for table `crm_products_campaigns`
  2017. --
  2018.  
  2019. CREATE TABLE `crm_products_campaigns` (
  2020. `product_id` int(11) NOT NULL,
  2021. `campaign_id` int(11) NOT NULL,
  2022. `discount_type` enum('1','2') DEFAULT NULL COMMENT '1=Percentage\n2=Fixed',
  2023. `amount` double DEFAULT NULL
  2024. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  2025.  
  2026. --
  2027. -- Dumping data for table `crm_products_campaigns`
  2028. --
  2029.  
  2030. INSERT INTO `crm_products_campaigns` (`product_id`, `campaign_id`, `discount_type`, `amount`) VALUES
  2031. (2, 9, '1', 10),
  2032. (2, 11, '2', 80000),
  2033. (5, 9, '2', 500),
  2034. (5, 11, '2', 9000);
  2035.  
  2036. -- --------------------------------------------------------
  2037.  
  2038. --
  2039. -- Table structure for table `crm_product_categories`
  2040. --
  2041.  
  2042. CREATE TABLE `crm_product_categories` (
  2043. `category_id` int(11) NOT NULL,
  2044. `category_name` varchar(100) NOT NULL,
  2045. `category_status` enum('0','1') DEFAULT '1' COMMENT '0=non aktif\n1=aktif',
  2046. `user_created` varchar(100) DEFAULT NULL,
  2047. `user_created_date` datetime DEFAULT NULL,
  2048. `user_modified` varchar(100) DEFAULT NULL,
  2049. `user_modified_date` datetime DEFAULT NULL
  2050. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  2051.  
  2052. --
  2053. -- Dumping data for table `crm_product_categories`
  2054. --
  2055.  
  2056. INSERT INTO `crm_product_categories` (`category_id`, `category_name`, `category_status`, `user_created`, `user_created_date`, `user_modified`, `user_modified_date`) VALUES
  2057. (1, 'Category example', '1', 'Admin', '2017-08-11 17:01:15', 'Admin', '2017-08-11 17:10:26');
  2058.  
  2059. -- --------------------------------------------------------
  2060.  
  2061. --
  2062. -- Table structure for table `crm_roles`
  2063. --
  2064.  
  2065. CREATE TABLE `crm_roles` (
  2066. `role_id` int(11) NOT NULL COMMENT 'User Administrator Hard Code All access module',
  2067. `role_name` varchar(100) DEFAULT NULL,
  2068. `status` enum('0','1') DEFAULT NULL,
  2069. `user_created` varchar(100) DEFAULT NULL,
  2070. `user_created_date` datetime DEFAULT NULL,
  2071. `user_modified` varchar(100) DEFAULT NULL,
  2072. `user_modified_date` datetime DEFAULT NULL
  2073. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  2074.  
  2075. --
  2076. -- Dumping data for table `crm_roles`
  2077. --
  2078.  
  2079. INSERT INTO `crm_roles` (`role_id`, `role_name`, `status`, `user_created`, `user_created_date`, `user_modified`, `user_modified_date`) VALUES
  2080. (1, 'Admin', '1', 'Admin', '2017-08-11 15:59:08', 'Admin', '2017-08-18 10:49:37'),
  2081. (3, 'Marketing', '1', 'Admin', '2017-08-11 15:59:08', 'Admin', '2017-08-18 10:49:37');
  2082.  
  2083. -- --------------------------------------------------------
  2084.  
  2085. --
  2086. -- Table structure for table `crm_roles_modules`
  2087. --
  2088.  
  2089. CREATE TABLE `crm_roles_modules` (
  2090. `crm_roles_role_id` int(11) NOT NULL,
  2091. `crm_modules_module_id` int(11) NOT NULL
  2092. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  2093.  
  2094. --
  2095. -- Dumping data for table `crm_roles_modules`
  2096. --
  2097.  
  2098. INSERT INTO `crm_roles_modules` (`crm_roles_role_id`, `crm_modules_module_id`) VALUES
  2099. (1, 2),
  2100. (1, 3),
  2101. (1, 4),
  2102. (1, 5),
  2103. (1, 6),
  2104. (1, 7);
  2105.  
  2106. -- --------------------------------------------------------
  2107.  
  2108. --
  2109. -- Table structure for table `crm_setup_company`
  2110. --
  2111.  
  2112. CREATE TABLE `crm_setup_company` (
  2113. `company_id` int(11) NOT NULL,
  2114. `company_name` varchar(225) NOT NULL,
  2115. `company_email` varchar(225) DEFAULT NULL,
  2116. `company_address1` varchar(225) NOT NULL,
  2117. `company_address2` varchar(100) DEFAULT NULL,
  2118. `company_city` varchar(225) NOT NULL,
  2119. `company_state` varchar(225) NOT NULL,
  2120. `company_zip_code` varchar(50) NOT NULL,
  2121. `company_country` varchar(225) NOT NULL,
  2122. `company_phone` varchar(50) DEFAULT NULL,
  2123. `company_logo` varchar(100) DEFAULT NULL,
  2124. `company_fax` varchar(50) DEFAULT NULL,
  2125. `company_website` varchar(100) DEFAULT NULL,
  2126. `company_initial_setting` enum('0','1') DEFAULT '0'
  2127. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  2128.  
  2129. --
  2130. -- Dumping data for table `crm_setup_company`
  2131. --
  2132.  
  2133. INSERT INTO `crm_setup_company` (`company_id`, `company_name`, `company_email`, `company_address1`, `company_address2`, `company_city`, `company_state`, `company_zip_code`, `company_country`, `company_phone`, `company_logo`, `company_fax`, `company_website`, `company_initial_setting`) VALUES
  2134. (1, 'Bangsa Cerdas', 'company@example.com', 'Puyuh raya', 'Kelapa Gading', 'Jakarta Utara', 'Indonesia', '142945', '', '087814043123', NULL, 'Fax example', 'www.example.com', '1');
  2135.  
  2136. -- --------------------------------------------------------
  2137.  
  2138. --
  2139. -- Table structure for table `crm_source`
  2140. --
  2141.  
  2142. CREATE TABLE `crm_source` (
  2143. `source_id` int(11) NOT NULL,
  2144. `source_name` varchar(100) NOT NULL,
  2145. `user_created` varchar(100) DEFAULT NULL,
  2146. `user_created_date` datetime DEFAULT NULL,
  2147. `user_modified` varchar(100) DEFAULT NULL,
  2148. `user_modified_date` datetime DEFAULT NULL
  2149. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  2150.  
  2151. --
  2152. -- Dumping data for table `crm_source`
  2153. --
  2154.  
  2155. INSERT INTO `crm_source` (`source_id`, `source_name`, `user_created`, `user_created_date`, `user_modified`, `user_modified_date`) VALUES
  2156. (2, 'Example source', 'Admin', '2017-08-11 16:00:13', NULL, NULL);
  2157.  
  2158. -- --------------------------------------------------------
  2159.  
  2160. --
  2161. -- Table structure for table `crm_terms`
  2162. --
  2163.  
  2164. CREATE TABLE `crm_terms` (
  2165. `term_id` int(11) NOT NULL,
  2166. `term_description` varchar(100) NOT NULL,
  2167. `term_day` int(11) NOT NULL,
  2168. `term_default` enum('0','1') DEFAULT '0' COMMENT '0=Not Default\n1=Default'
  2169. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  2170.  
  2171. --
  2172. -- Dumping data for table `crm_terms`
  2173. --
  2174.  
  2175. INSERT INTO `crm_terms` (`term_id`, `term_description`, `term_day`, `term_default`) VALUES
  2176. (1, 'D 10', 10, '1'),
  2177. (2, 'D 15', 15, '0');
  2178.  
  2179. -- --------------------------------------------------------
  2180.  
  2181. --
  2182. -- Table structure for table `crm_users`
  2183. --
  2184.  
  2185. CREATE TABLE `crm_users` (
  2186. `user_id` int(11) NOT NULL,
  2187. `user_first_name` varchar(100) NOT NULL,
  2188. `user_last_name` varchar(100) DEFAULT NULL,
  2189. `user_job_title` varchar(50) DEFAULT NULL,
  2190. `user_password` varchar(100) NOT NULL,
  2191. `user_mobile` varchar(30) NOT NULL,
  2192. `user_username` varchar(100) NOT NULL,
  2193. `user_department` varchar(100) DEFAULT NULL,
  2194. `user_email` varchar(100) NOT NULL,
  2195. `user_photo` varchar(100) DEFAULT NULL,
  2196. `user_status` enum('0','1') DEFAULT '1' COMMENT '0=Non-Aktif 1=Aktif',
  2197. `user_group_id` int(11) NOT NULL,
  2198. `user_manager_id` int(11) DEFAULT NULL,
  2199. `user_role_id` int(11) NOT NULL,
  2200. `user_created` varchar(100) DEFAULT NULL,
  2201. `user_created_date` datetime DEFAULT NULL,
  2202. `user_modified` varchar(100) DEFAULT NULL,
  2203. `user_modified_date` datetime DEFAULT NULL
  2204. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  2205.  
  2206. --
  2207. -- Dumping data for table `crm_users`
  2208. --
  2209.  
  2210. INSERT INTO `crm_users` (`user_id`, `user_first_name`, `user_last_name`, `user_job_title`, `user_password`, `user_mobile`, `user_username`, `user_department`, `user_email`, `user_photo`, `user_status`, `user_group_id`, `user_manager_id`, `user_role_id`, `user_created`, `user_created_date`, `user_modified`, `user_modified_date`) VALUES
  2211. (9, 'Ulala', 'Houp', 'Dev', '$2a$10$SEgnnfD.6OQuEJWDpIqfo.tM64kwMJE7U3pbcq4aGRegV8UlGIHK6', '087813492348', 'ulala', 'Front End Dev', 'ualal@gmail.com', 'd96a4ddf-5530-40b0-a7c0-88fe6964b72c.jpeg', '1', 1, NULL, 1, 'Admin', '2017-08-11 17:18:36', 'Admin', '2017-08-19 01:59:52'),
  2212. (15, 'Peter', 'Parker', 'Web Dev', '$2a$10$ZbvKmpBFDwMUNRoT8Er.jujs/kqjDZ3PZQrcvzxpTYIvgssCUYAwW', '087813499867', 'parker', 'Front End Dev', 'peter@par.com', '0ec55e78-a814-4760-b77a-9fd796473992.jpeg', '1', 3, NULL, 3, 'Admin', '2017-08-16 15:28:07', NULL, NULL),
  2213. (17, 'ulala', 'Parker', 'Web Dev', '$2a$10$ZbvKmpBFDwMUNRoT8Er.jujs/kqjDZ3PZQrcvzxpTYIvgssCUYAwW', '087813499867', 'surali', 'Front End Dev', 'surali@par.com', '0ec55e78-a814-4760-b77a-9fd796473992.jpeg', '1', 3, NULL, 3, 'Admin', '2017-08-16 15:28:07', NULL, NULL);
  2214.  
  2215. -- --------------------------------------------------------
  2216.  
  2217. --
  2218. -- Table structure for table `crm_users_companies`
  2219. --
  2220.  
  2221. CREATE TABLE `crm_users_companies` (
  2222. `company_id` int(11) NOT NULL,
  2223. `user_id` int(11) NOT NULL
  2224. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  2225.  
  2226. --
  2227. -- Dumping data for table `crm_users_companies`
  2228. --
  2229.  
  2230. INSERT INTO `crm_users_companies` (`company_id`, `user_id`) VALUES
  2231. (25, 9),
  2232. (26, 9),
  2233. (27, 9),
  2234. (28, 9),
  2235. (29, 9),
  2236. (30, 9),
  2237. (31, 9),
  2238. (31, 15),
  2239. (32, 9),
  2240. (33, 9),
  2241. (34, 9),
  2242. (35, 9),
  2243. (36, 9),
  2244. (37, 9),
  2245. (38, 9),
  2246. (39, 9),
  2247. (40, 9),
  2248. (41, 9),
  2249. (42, 9),
  2250. (43, 9),
  2251. (44, 9),
  2252. (45, 9),
  2253. (46, 9),
  2254. (47, 9);
  2255.  
  2256. -- --------------------------------------------------------
  2257.  
  2258. --
  2259. -- Table structure for table `crm_users_contacts`
  2260. --
  2261.  
  2262. CREATE TABLE `crm_users_contacts` (
  2263. `contact_id` int(11) NOT NULL,
  2264. `user_id` int(11) NOT NULL
  2265. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  2266.  
  2267. --
  2268. -- Dumping data for table `crm_users_contacts`
  2269. --
  2270.  
  2271. INSERT INTO `crm_users_contacts` (`contact_id`, `user_id`) VALUES
  2272. (31, 9),
  2273. (32, 9),
  2274. (33, 9),
  2275. (37, 9),
  2276. (44, 9),
  2277. (45, 9),
  2278. (46, 9),
  2279. (46, 15),
  2280. (63, 9),
  2281. (64, 9),
  2282. (66, 9),
  2283. (73, 9),
  2284. (74, 9),
  2285. (75, 9),
  2286. (76, 9),
  2287. (77, 9),
  2288. (78, 9),
  2289. (79, 9),
  2290. (80, 9),
  2291. (81, 9),
  2292. (82, 9),
  2293. (83, 9),
  2294. (84, 9),
  2295. (85, 9),
  2296. (86, 9),
  2297. (87, 9),
  2298. (88, 9),
  2299. (89, 9),
  2300. (90, 9),
  2301. (91, 9),
  2302. (92, 9),
  2303. (93, 9),
  2304. (94, 9),
  2305. (95, 9),
  2306. (96, 9),
  2307. (97, 9),
  2308. (98, 9);
  2309.  
  2310. -- --------------------------------------------------------
  2311.  
  2312. --
  2313. -- Table structure for table `crm_users_leads`
  2314. --
  2315.  
  2316. CREATE TABLE `crm_users_leads` (
  2317. `lead_id` int(11) NOT NULL,
  2318. `user_id` int(11) NOT NULL
  2319. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  2320.  
  2321. --
  2322. -- Dumping data for table `crm_users_leads`
  2323. --
  2324.  
  2325. INSERT INTO `crm_users_leads` (`lead_id`, `user_id`) VALUES
  2326. (38, 9),
  2327. (39, 9),
  2328. (40, 9),
  2329. (41, 9),
  2330. (41, 15),
  2331. (42, 9),
  2332. (43, 9),
  2333. (44, 9),
  2334. (45, 9),
  2335. (46, 9),
  2336. (47, 9),
  2337. (48, 9),
  2338. (49, 9),
  2339. (50, 9),
  2340. (51, 9),
  2341. (54, 9),
  2342. (55, 9),
  2343. (56, 9),
  2344. (57, 9),
  2345. (58, 9),
  2346. (59, 9),
  2347. (60, 9),
  2348. (61, 9),
  2349. (62, 9),
  2350. (63, 9),
  2351. (64, 9),
  2352. (65, 9),
  2353. (66, 9),
  2354. (67, 9),
  2355. (68, 9),
  2356. (69, 9),
  2357. (70, 9),
  2358. (71, 9),
  2359. (72, 9),
  2360. (73, 9),
  2361. (74, 9),
  2362. (75, 9),
  2363. (76, 9),
  2364. (77, 9);
  2365.  
  2366. -- --------------------------------------------------------
  2367.  
  2368. --
  2369. -- Table structure for table `lead_calls`
  2370. --
  2371.  
  2372. CREATE TABLE `lead_calls` (
  2373. `lead_call_id` int(11) NOT NULL,
  2374. `lead_call_title` varchar(100) DEFAULT NULL,
  2375. `lead_call_duration` int(11) DEFAULT NULL,
  2376. `lead_call_time` enum('1','2','3') DEFAULT '1' COMMENT '1= Second\n2= Minutes\n3=Hour\n',
  2377. `lead_call_notes` text,
  2378. `lead_call_date` datetime NOT NULL,
  2379. `lead_call_user` varchar(100) NOT NULL,
  2380. `lead_id` int(11) NOT NULL
  2381. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  2382.  
  2383. -- --------------------------------------------------------
  2384.  
  2385. --
  2386. -- Table structure for table `lead_files`
  2387. --
  2388.  
  2389. CREATE TABLE `lead_files` (
  2390. `lead_files_id` int(11) NOT NULL,
  2391. `lead_file_subject` varchar(100) DEFAULT NULL,
  2392. `lead_file_to` varchar(100) NOT NULL,
  2393. `lead_file_cc` varchar(100) DEFAULT NULL,
  2394. `lead_file_description` text,
  2395. `lead_file_name` varchar(100) NOT NULL,
  2396. `lead_file_date` datetime NOT NULL,
  2397. `lead_file_user` varchar(100) NOT NULL,
  2398. `lead_id` int(11) NOT NULL
  2399. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  2400.  
  2401. --
  2402. -- Dumping data for table `lead_files`
  2403. --
  2404.  
  2405. INSERT INTO `lead_files` (`lead_files_id`, `lead_file_subject`, `lead_file_to`, `lead_file_cc`, `lead_file_description`, `lead_file_name`, `lead_file_date`, `lead_file_user`, `lead_id`) VALUES
  2406. (18, 'asd', 'asd@asd.asd', 'ualal@gmail.com,peter@par.com', 'asd', 'ae087fa4-b62d-48f1-be23-d686828c49cb.jpg', '2017-08-25 03:54:23', 'ulala', 57),
  2407. (19, 'asddd', 'asd@asd.asd', 'ualal@gmail.com', 'asddd', '675d1853-7689-462b-968e-a5dc586945b1.jpg', '2017-08-25 12:24:16', 'ulala', 57);
  2408.  
  2409. -- --------------------------------------------------------
  2410.  
  2411. --
  2412. -- Table structure for table `lead_meeting`
  2413. --
  2414.  
  2415. CREATE TABLE `lead_meeting` (
  2416. `lead_meeting_id` int(11) NOT NULL,
  2417. `lead_meeting_name` varchar(100) DEFAULT NULL,
  2418. `lead_meeting_location` varchar(255) DEFAULT NULL,
  2419. `lead_meeting_start_date` datetime DEFAULT NULL,
  2420. `lead_meeting_end_date` datetime DEFAULT NULL,
  2421. `lead_meeting_description` text,
  2422. `lead_meeting_date` datetime NOT NULL,
  2423. `lead_meeting_user` varchar(100) NOT NULL,
  2424. `lead_id` int(11) NOT NULL
  2425. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  2426.  
  2427. --
  2428. -- Dumping data for table `lead_meeting`
  2429. --
  2430.  
  2431. INSERT INTO `lead_meeting` (`lead_meeting_id`, `lead_meeting_name`, `lead_meeting_location`, `lead_meeting_start_date`, `lead_meeting_end_date`, `lead_meeting_description`, `lead_meeting_date`, `lead_meeting_user`, `lead_id`) VALUES
  2432. (15, '2', '2', '2017-08-01 00:00:00', '2017-08-02 00:00:00', '222', '2017-08-23 20:20:52', 'ulala', 40),
  2433. (16, 'qwerty', 'asdf', '2017-08-01 00:00:00', '2017-08-02 00:00:00', 'asdfg', '2017-08-24 10:26:42', 'ulala', 51),
  2434. (17, 'lllgg', 'lllgg', '2017-08-25 00:00:00', '2017-08-10 00:00:00', 'ulalagg', '2017-08-25 09:44:07', 'ulala', 57),
  2435. (18, 'asd', 'asd', '2017-08-10 00:00:00', '2017-08-10 00:00:00', 'asd', '2017-08-25 08:56:17', 'ulala', 57),
  2436. (19, 'ulala coek', 'ulala', '2017-08-13 00:00:00', '2017-08-13 00:00:00', 'asd', '2017-08-25 09:16:59', 'ulala', 57),
  2437. (20, 'asd', 'asd', '2017-08-29 00:00:00', '2017-08-29 00:00:00', 'asd', '2017-08-29 04:40:09', 'ulala', 71);
  2438.  
  2439. -- --------------------------------------------------------
  2440.  
  2441. --
  2442. -- Table structure for table `lead_meeting_users`
  2443. --
  2444.  
  2445. CREATE TABLE `lead_meeting_users` (
  2446. `lead_meeting_id` int(11) NOT NULL,
  2447. `lead_user_id` int(11) NOT NULL
  2448. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  2449.  
  2450. --
  2451. -- Dumping data for table `lead_meeting_users`
  2452. --
  2453.  
  2454. INSERT INTO `lead_meeting_users` (`lead_meeting_id`, `lead_user_id`) VALUES
  2455. (15, 15),
  2456. (16, 15),
  2457. (17, 15),
  2458. (18, 9),
  2459. (19, 15),
  2460. (20, 15),
  2461. (20, 17);
  2462.  
  2463. -- --------------------------------------------------------
  2464.  
  2465. --
  2466. -- Table structure for table `lead_notes`
  2467. --
  2468.  
  2469. CREATE TABLE `lead_notes` (
  2470. `lead_note_id` int(11) NOT NULL,
  2471. `lead_note_name` varchar(100) NOT NULL,
  2472. `lead_note_description` text,
  2473. `lead_note_date` datetime NOT NULL,
  2474. `lead_note_user` varchar(100) NOT NULL,
  2475. `lead_id` int(11) NOT NULL
  2476. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  2477.  
  2478. -- --------------------------------------------------------
  2479.  
  2480. --
  2481. -- Table structure for table `lead_tasks`
  2482. --
  2483.  
  2484. CREATE TABLE `lead_tasks` (
  2485. `lead_task_id` int(11) NOT NULL,
  2486. `lead_task_name` varchar(100) NOT NULL,
  2487. `lead_task_description` varchar(45) DEFAULT NULL,
  2488. `lead_task_status` int(11) DEFAULT NULL,
  2489. `lead_task_due_date` datetime DEFAULT NULL,
  2490. `lead_task_date` datetime NOT NULL,
  2491. `lead_task_user` varchar(100) NOT NULL,
  2492. `lead_id` int(11) NOT NULL
  2493. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  2494.  
  2495. -- --------------------------------------------------------
  2496.  
  2497. --
  2498. -- Table structure for table `opportunity_calls`
  2499. --
  2500.  
  2501. CREATE TABLE `opportunity_calls` (
  2502. `opportunity_call_id` int(11) NOT NULL,
  2503. `opportunity_call_title` varchar(100) DEFAULT NULL,
  2504. `opportunity_call_duration` int(11) DEFAULT NULL,
  2505. `opportunity_call_time` enum('1','2','3') DEFAULT '1' COMMENT '1= Second\n2= Minutes\n3=Hour\n',
  2506. `opportunity_call_notes` text,
  2507. `opportunity_call_date` datetime NOT NULL,
  2508. `opportunity_call_user` varchar(100) NOT NULL,
  2509. `opportunity_id` int(11) NOT NULL
  2510. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  2511.  
  2512. -- --------------------------------------------------------
  2513.  
  2514. --
  2515. -- Table structure for table `opportunity_files`
  2516. --
  2517.  
  2518. CREATE TABLE `opportunity_files` (
  2519. `opportunity_files_id` int(11) NOT NULL,
  2520. `opportunity_file_subject` varchar(100) DEFAULT NULL,
  2521. `opportunity_file_to` varchar(100) NOT NULL,
  2522. `opportunity_file_cc` varchar(100) DEFAULT NULL,
  2523. `opportunity_file_description` text,
  2524. `opportunity_file_name` varchar(100) NOT NULL,
  2525. `opportunity_file_date` datetime NOT NULL,
  2526. `opportunity_file_user` varchar(100) NOT NULL,
  2527. `opportunity_id` int(11) NOT NULL
  2528. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  2529.  
  2530. --
  2531. -- Dumping data for table `opportunity_files`
  2532. --
  2533.  
  2534. INSERT INTO `opportunity_files` (`opportunity_files_id`, `opportunity_file_subject`, `opportunity_file_to`, `opportunity_file_cc`, `opportunity_file_description`, `opportunity_file_name`, `opportunity_file_date`, `opportunity_file_user`, `opportunity_id`) VALUES
  2535. (4, 'asdasd', 'novalparinussa@gmail.com,', 'ualal@gmail.com', 'asdasd', 'a5e6957a-768b-48db-8f9b-fd9e6b680b07.jpg', '2017-08-25 15:30:55', 'ulala', 5);
  2536.  
  2537. -- --------------------------------------------------------
  2538.  
  2539. --
  2540. -- Table structure for table `opportunity_meeting`
  2541. --
  2542.  
  2543. CREATE TABLE `opportunity_meeting` (
  2544. `opportunity_meeting_id` int(11) NOT NULL,
  2545. `opportunity_meeting_name` varchar(100) DEFAULT NULL,
  2546. `opportunity_meeting_location` varchar(255) DEFAULT NULL,
  2547. `opportunity_meeting_start_date` datetime DEFAULT NULL,
  2548. `opportunity_meeting_end_date` datetime DEFAULT NULL,
  2549. `opportunity_meeting_description` text,
  2550. `opportunity_meeting_date` datetime NOT NULL,
  2551. `opportunity_meeting_user` varchar(100) NOT NULL,
  2552. `opportunity_id` int(11) NOT NULL
  2553. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  2554.  
  2555. --
  2556. -- Dumping data for table `opportunity_meeting`
  2557. --
  2558.  
  2559. INSERT INTO `opportunity_meeting` (`opportunity_meeting_id`, `opportunity_meeting_name`, `opportunity_meeting_location`, `opportunity_meeting_start_date`, `opportunity_meeting_end_date`, `opportunity_meeting_description`, `opportunity_meeting_date`, `opportunity_meeting_user`, `opportunity_id`) VALUES
  2560. (8, 'asdasd', 'asdasd', '2017-08-01 00:00:00', '2017-08-02 00:00:00', 'asdasd', '2017-08-25 09:59:28', 'ulala', 5);
  2561.  
  2562. -- --------------------------------------------------------
  2563.  
  2564. --
  2565. -- Table structure for table `opportunity_meeting_users`
  2566. --
  2567.  
  2568. CREATE TABLE `opportunity_meeting_users` (
  2569. `opportunity_meeting_id` int(11) NOT NULL,
  2570. `opportunity_user_id` int(11) NOT NULL
  2571. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  2572.  
  2573. --
  2574. -- Dumping data for table `opportunity_meeting_users`
  2575. --
  2576.  
  2577. INSERT INTO `opportunity_meeting_users` (`opportunity_meeting_id`, `opportunity_user_id`) VALUES
  2578. (8, 15);
  2579.  
  2580. -- --------------------------------------------------------
  2581.  
  2582. --
  2583. -- Table structure for table `opportunity_notes`
  2584. --
  2585.  
  2586. CREATE TABLE `opportunity_notes` (
  2587. `opportunity_note_id` int(11) NOT NULL,
  2588. `opportunity_note_name` varchar(100) NOT NULL,
  2589. `opportunity_note_description` text,
  2590. `opportunity_note_date` datetime DEFAULT NULL,
  2591. `opportunity_note_user` varchar(100) DEFAULT NULL,
  2592. `opportunity_id` int(11) NOT NULL
  2593. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  2594.  
  2595. --
  2596. -- Dumping data for table `opportunity_notes`
  2597. --
  2598.  
  2599. INSERT INTO `opportunity_notes` (`opportunity_note_id`, `opportunity_note_name`, `opportunity_note_description`, `opportunity_note_date`, `opportunity_note_user`, `opportunity_id`) VALUES
  2600. (3, 'ads', '<p>asd</p>', '2017-08-24 06:58:41', 'ulala', 5);
  2601.  
  2602. -- --------------------------------------------------------
  2603.  
  2604. --
  2605. -- Table structure for table `opportunity_tasks`
  2606. --
  2607.  
  2608. CREATE TABLE `opportunity_tasks` (
  2609. `opportunity_task_id` int(11) NOT NULL,
  2610. `opportunity_task_name` varchar(100) NOT NULL,
  2611. `opportunity_task_description` varchar(45) DEFAULT NULL,
  2612. `opportunity_task_status` int(11) DEFAULT NULL,
  2613. `opportunity_task_due_date` datetime DEFAULT NULL,
  2614. `opportunity_task_date` datetime NOT NULL,
  2615. `opportunity_task_user` varchar(100) NOT NULL,
  2616. `opportunity_id` int(11) NOT NULL
  2617. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  2618.  
  2619. --
  2620. -- Indexes for dumped tables
  2621. --
  2622.  
  2623. --
  2624. -- Indexes for table `company_calls`
  2625. --
  2626. ALTER TABLE `company_calls`
  2627. ADD PRIMARY KEY (`company_call_id`),
  2628. ADD KEY `fk_company_calls_crm_company1_idx` (`company_id`);
  2629.  
  2630. --
  2631. -- Indexes for table `company_files`
  2632. --
  2633. ALTER TABLE `company_files`
  2634. ADD PRIMARY KEY (`company_files_id`),
  2635. ADD KEY `fk_company_files_crm_company1_idx` (`company_id`);
  2636.  
  2637. --
  2638. -- Indexes for table `company_meeting`
  2639. --
  2640. ALTER TABLE `company_meeting`
  2641. ADD PRIMARY KEY (`company_meeting_id`),
  2642. ADD KEY `fk_company_meeting_crm_company1_idx` (`company_id`);
  2643.  
  2644. --
  2645. -- Indexes for table `company_meeting_users`
  2646. --
  2647. ALTER TABLE `company_meeting_users`
  2648. ADD PRIMARY KEY (`company_meeting_id`,`company_user_id`),
  2649. ADD KEY `fk_company_meeting_has_crm_users_crm_users1_idx` (`company_user_id`),
  2650. ADD KEY `fk_company_meeting_has_crm_users_company_meeting1_idx` (`company_meeting_id`);
  2651.  
  2652. --
  2653. -- Indexes for table `company_notes`
  2654. --
  2655. ALTER TABLE `company_notes`
  2656. ADD PRIMARY KEY (`company_note_id`),
  2657. ADD KEY `fk_company_notes_crm_company1_idx` (`company_id`);
  2658.  
  2659. --
  2660. -- Indexes for table `company_tasks`
  2661. --
  2662. ALTER TABLE `company_tasks`
  2663. ADD PRIMARY KEY (`company_task_id`),
  2664. ADD KEY `fk_company_tasks_crm_company1_idx` (`company_id`);
  2665.  
  2666. --
  2667. -- Indexes for table `contact_calls`
  2668. --
  2669. ALTER TABLE `contact_calls`
  2670. ADD PRIMARY KEY (`contact_call_id`),
  2671. ADD KEY `fk_customer_calls_crm_customers1_idx` (`contact_id`);
  2672.  
  2673. --
  2674. -- Indexes for table `contact_files`
  2675. --
  2676. ALTER TABLE `contact_files`
  2677. ADD PRIMARY KEY (`contact_files_id`),
  2678. ADD KEY `fk_customer_files_copy1_crm_customers1_idx` (`contact_id`);
  2679.  
  2680. --
  2681. -- Indexes for table `contact_meeting`
  2682. --
  2683. ALTER TABLE `contact_meeting`
  2684. ADD PRIMARY KEY (`contact_meeting_id`),
  2685. ADD KEY `fk_customer_meeting_crm_customers1_idx` (`contact_id`);
  2686.  
  2687. --
  2688. -- Indexes for table `contact_meeting_users`
  2689. --
  2690. ALTER TABLE `contact_meeting_users`
  2691. ADD PRIMARY KEY (`contact_meeting_id`,`contact_user_id`),
  2692. ADD KEY `fk_contact_meeting_has_crm_users_crm_users1_idx` (`contact_user_id`),
  2693. ADD KEY `fk_contact_meeting_has_crm_users_contact_meeting1_idx` (`contact_meeting_id`);
  2694.  
  2695. --
  2696. -- Indexes for table `contact_notes`
  2697. --
  2698. ALTER TABLE `contact_notes`
  2699. ADD PRIMARY KEY (`contact_note_id`),
  2700. ADD KEY `fk_customer_notes_crm_customers1_idx` (`contact_id`);
  2701.  
  2702. --
  2703. -- Indexes for table `contact_tasks`
  2704. --
  2705. ALTER TABLE `contact_tasks`
  2706. ADD PRIMARY KEY (`contact_task_id`),
  2707. ADD KEY `fk_customer_tasks_crm_customers1_idx` (`contact_id`);
  2708.  
  2709. --
  2710. -- Indexes for table `crm_campaigns`
  2711. --
  2712. ALTER TABLE `crm_campaigns`
  2713. ADD PRIMARY KEY (`campaign_id`);
  2714.  
  2715. --
  2716. -- Indexes for table `crm_companies`
  2717. --
  2718. ALTER TABLE `crm_companies`
  2719. ADD PRIMARY KEY (`company_id`),
  2720. ADD KEY `fk_crm_company_crm_industry1_idx` (`company_industry`),
  2721. ADD KEY `fk_crm_company_crm_groups1_idx` (`company_groups`),
  2722. ADD KEY `fk_crm_companies_crm_companies1_idx` (`company_parent`);
  2723.  
  2724. --
  2725. -- Indexes for table `crm_contacts`
  2726. --
  2727. ALTER TABLE `crm_contacts`
  2728. ADD PRIMARY KEY (`contact_id`),
  2729. ADD KEY `fk_crm_customer_crm_company1_idx` (`company_id`),
  2730. ADD KEY `fk_crm_customer_crm_groups1_idx` (`contact_groups`),
  2731. ADD KEY `fk_crm_customer_peoples1_idx` (`contact_id`),
  2732. ADD KEY `fk_crm_contacts_crm_peoples1_idx` (`people_id`);
  2733.  
  2734. --
  2735. -- Indexes for table `crm_groups`
  2736. --
  2737. ALTER TABLE `crm_groups`
  2738. ADD PRIMARY KEY (`group_id`);
  2739.  
  2740. --
  2741. -- Indexes for table `crm_industry`
  2742. --
  2743. ALTER TABLE `crm_industry`
  2744. ADD PRIMARY KEY (`industry_id`);
  2745.  
  2746. --
  2747. -- Indexes for table `crm_invoices`
  2748. --
  2749. ALTER TABLE `crm_invoices`
  2750. ADD PRIMARY KEY (`invoice_id`),
  2751. ADD KEY `fk_crm_invoices_crm_terms1_idx` (`term_id`),
  2752. ADD KEY `fk_crm_invoices_crm_companies1_idx` (`company_id`);
  2753.  
  2754. --
  2755. -- Indexes for table `crm_invoices_products`
  2756. --
  2757. ALTER TABLE `crm_invoices_products`
  2758. ADD PRIMARY KEY (`invoice_detail_id`,`product_id`),
  2759. ADD KEY `fk_crm_invoice_details_has_crm_products_crm_products1_idx` (`product_id`),
  2760. ADD KEY `fk_crm_invoice_details_has_crm_products_crm_invoice_details_idx` (`invoice_detail_id`);
  2761.  
  2762. --
  2763. -- Indexes for table `crm_invoice_details`
  2764. --
  2765. ALTER TABLE `crm_invoice_details`
  2766. ADD PRIMARY KEY (`invoice_detail_id`),
  2767. ADD KEY `fk_crm_invoice_details_crm_invoices1_idx` (`invoice_id`);
  2768.  
  2769. --
  2770. -- Indexes for table `crm_leads`
  2771. --
  2772. ALTER TABLE `crm_leads`
  2773. ADD PRIMARY KEY (`lead_id`),
  2774. ADD KEY `fk_crm_leads_crm_lead_status1_idx` (`lead_label`),
  2775. ADD KEY `fk_crm_leads_crm_groups1_idx` (`lead_groups`),
  2776. ADD KEY `fk_crm_leads_crm_peoples1_idx` (`people_id`);
  2777.  
  2778. --
  2779. -- Indexes for table `crm_lead_label`
  2780. --
  2781. ALTER TABLE `crm_lead_label`
  2782. ADD PRIMARY KEY (`lead_label_id`);
  2783.  
  2784. --
  2785. -- Indexes for table `crm_modules`
  2786. --
  2787. ALTER TABLE `crm_modules`
  2788. ADD PRIMARY KEY (`module_id`);
  2789.  
  2790. --
  2791. -- Indexes for table `crm_opportunities`
  2792. --
  2793. ALTER TABLE `crm_opportunities`
  2794. ADD PRIMARY KEY (`opportunity_id`),
  2795. ADD KEY `fk_crm_opportunities_crm_opportunity_stage1_idx` (`opportunity_stage_id`),
  2796. ADD KEY `fk_crm_opportunities_crm_companies1_idx` (`company_id`),
  2797. ADD KEY `fk_crm_opportunities_crm_campaigns1_idx` (`opportunity_campaign_id`);
  2798.  
  2799. --
  2800. -- Indexes for table `crm_opportunities_contacts`
  2801. --
  2802. ALTER TABLE `crm_opportunities_contacts`
  2803. ADD PRIMARY KEY (`opportunity_id`,`contact_id`),
  2804. ADD KEY `fk_crm_opportunities_has_crm_contacts_crm_contacts1_idx` (`contact_id`),
  2805. ADD KEY `fk_crm_opportunities_has_crm_contacts_crm_opportunities1_idx` (`opportunity_id`);
  2806.  
  2807. --
  2808. -- Indexes for table `crm_opportunities_products`
  2809. --
  2810. ALTER TABLE `crm_opportunities_products`
  2811. ADD PRIMARY KEY (`opportunity_id`,`product_id`),
  2812. ADD KEY `fk_crm_opportunities_has_crm_products_crm_products1_idx` (`product_id`),
  2813. ADD KEY `fk_crm_opportunities_has_crm_products_crm_opportunities1_idx` (`opportunity_id`);
  2814.  
  2815. --
  2816. -- Indexes for table `crm_opportunity_stage`
  2817. --
  2818. ALTER TABLE `crm_opportunity_stage`
  2819. ADD PRIMARY KEY (`opportunity_stage_id`);
  2820.  
  2821. --
  2822. -- Indexes for table `crm_peoples`
  2823. --
  2824. ALTER TABLE `crm_peoples`
  2825. ADD PRIMARY KEY (`people_id`),
  2826. ADD KEY `fk_peoples_crm_industry1_idx` (`people_industry`),
  2827. ADD KEY `fk_peoples_crm_source1_idx` (`people_source`);
  2828.  
  2829. --
  2830. -- Indexes for table `crm_products`
  2831. --
  2832. ALTER TABLE `crm_products`
  2833. ADD PRIMARY KEY (`product_id`),
  2834. ADD KEY `crm_products_crm_product_categories_category_id_fk` (`product_category_id`);
  2835.  
  2836. --
  2837. -- Indexes for table `crm_products_campaigns`
  2838. --
  2839. ALTER TABLE `crm_products_campaigns`
  2840. ADD PRIMARY KEY (`product_id`,`campaign_id`),
  2841. ADD KEY `fk_crm_products_has_crm_campaigns_crm_campaigns1_idx` (`campaign_id`),
  2842. ADD KEY `fk_crm_products_has_crm_campaigns_crm_products1_idx` (`product_id`);
  2843.  
  2844. --
  2845. -- Indexes for table `crm_product_categories`
  2846. --
  2847. ALTER TABLE `crm_product_categories`
  2848. ADD PRIMARY KEY (`category_id`);
  2849.  
  2850. --
  2851. -- Indexes for table `crm_roles`
  2852. --
  2853. ALTER TABLE `crm_roles`
  2854. ADD PRIMARY KEY (`role_id`);
  2855.  
  2856. --
  2857. -- Indexes for table `crm_roles_modules`
  2858. --
  2859. ALTER TABLE `crm_roles_modules`
  2860. ADD PRIMARY KEY (`crm_roles_role_id`,`crm_modules_module_id`),
  2861. ADD KEY `fk_crm_roles_has_crm_modules_crm_modules1_idx` (`crm_modules_module_id`),
  2862. ADD KEY `fk_crm_roles_has_crm_modules_crm_roles1_idx` (`crm_roles_role_id`);
  2863.  
  2864. --
  2865. -- Indexes for table `crm_setup_company`
  2866. --
  2867. ALTER TABLE `crm_setup_company`
  2868. ADD PRIMARY KEY (`company_id`);
  2869.  
  2870. --
  2871. -- Indexes for table `crm_source`
  2872. --
  2873. ALTER TABLE `crm_source`
  2874. ADD PRIMARY KEY (`source_id`);
  2875.  
  2876. --
  2877. -- Indexes for table `crm_terms`
  2878. --
  2879. ALTER TABLE `crm_terms`
  2880. ADD PRIMARY KEY (`term_id`);
  2881.  
  2882. --
  2883. -- Indexes for table `crm_users`
  2884. --
  2885. ALTER TABLE `crm_users`
  2886. ADD PRIMARY KEY (`user_id`),
  2887. ADD UNIQUE KEY `crm_users_user_email_uindex` (`user_email`),
  2888. ADD KEY `crm_users_crm_groups_group_id_fk` (`user_group_id`),
  2889. ADD KEY `fk_crm_users_crm_users1_idx` (`user_manager_id`),
  2890. ADD KEY `fk_crm_users_crm_roles1_idx` (`user_role_id`);
  2891.  
  2892. --
  2893. -- Indexes for table `crm_users_companies`
  2894. --
  2895. ALTER TABLE `crm_users_companies`
  2896. ADD PRIMARY KEY (`company_id`,`user_id`),
  2897. ADD KEY `fk_crm_company_has_crm_users_crm_users1_idx` (`user_id`),
  2898. ADD KEY `fk_crm_company_has_crm_users_crm_company1_idx` (`company_id`);
  2899.  
  2900. --
  2901. -- Indexes for table `crm_users_contacts`
  2902. --
  2903. ALTER TABLE `crm_users_contacts`
  2904. ADD PRIMARY KEY (`contact_id`,`user_id`),
  2905. ADD KEY `fk_crm_users_has_crm_customer_crm_customer1_idx` (`contact_id`),
  2906. ADD KEY `fk_crm_users_has_crm_customer_crm_users1_idx` (`user_id`);
  2907.  
  2908. --
  2909. -- Indexes for table `crm_users_leads`
  2910. --
  2911. ALTER TABLE `crm_users_leads`
  2912. ADD PRIMARY KEY (`lead_id`,`user_id`),
  2913. ADD KEY `fk_crm_users_has_crm_leads_crm_leads1_idx` (`lead_id`),
  2914. ADD KEY `fk_crm_users_has_crm_leads_crm_users1_idx` (`user_id`);
  2915.  
  2916. --
  2917. -- Indexes for table `lead_calls`
  2918. --
  2919. ALTER TABLE `lead_calls`
  2920. ADD PRIMARY KEY (`lead_call_id`),
  2921. ADD KEY `fk_lead_calls_crm_leads1_idx` (`lead_id`);
  2922.  
  2923. --
  2924. -- Indexes for table `lead_files`
  2925. --
  2926. ALTER TABLE `lead_files`
  2927. ADD PRIMARY KEY (`lead_files_id`),
  2928. ADD KEY `fk_lead_files_crm_leads1_idx` (`lead_id`);
  2929.  
  2930. --
  2931. -- Indexes for table `lead_meeting`
  2932. --
  2933. ALTER TABLE `lead_meeting`
  2934. ADD PRIMARY KEY (`lead_meeting_id`),
  2935. ADD KEY `fk_lead_meeting_crm_leads1_idx` (`lead_id`);
  2936.  
  2937. --
  2938. -- Indexes for table `lead_meeting_users`
  2939. --
  2940. ALTER TABLE `lead_meeting_users`
  2941. ADD PRIMARY KEY (`lead_meeting_id`,`lead_user_id`),
  2942. ADD KEY `fk_lead_meeting_has_crm_users_crm_users1_idx` (`lead_user_id`),
  2943. ADD KEY `fk_lead_meeting_has_crm_users_lead_meeting1_idx` (`lead_meeting_id`);
  2944.  
  2945. --
  2946. -- Indexes for table `lead_notes`
  2947. --
  2948. ALTER TABLE `lead_notes`
  2949. ADD PRIMARY KEY (`lead_note_id`),
  2950. ADD KEY `fk_lead_notes_crm_leads1_idx` (`lead_id`);
  2951.  
  2952. --
  2953. -- Indexes for table `lead_tasks`
  2954. --
  2955. ALTER TABLE `lead_tasks`
  2956. ADD PRIMARY KEY (`lead_task_id`),
  2957. ADD KEY `fk_lead_task_crm_leads1_idx` (`lead_id`);
  2958.  
  2959. --
  2960. -- Indexes for table `opportunity_calls`
  2961. --
  2962. ALTER TABLE `opportunity_calls`
  2963. ADD PRIMARY KEY (`opportunity_call_id`),
  2964. ADD KEY `fk_opportunity_calls_crm_opportunities1_idx` (`opportunity_id`);
  2965.  
  2966. --
  2967. -- Indexes for table `opportunity_files`
  2968. --
  2969. ALTER TABLE `opportunity_files`
  2970. ADD PRIMARY KEY (`opportunity_files_id`),
  2971. ADD KEY `fk_opportunity_files_crm_opportunities1_idx` (`opportunity_id`);
  2972.  
  2973. --
  2974. -- Indexes for table `opportunity_meeting`
  2975. --
  2976. ALTER TABLE `opportunity_meeting`
  2977. ADD PRIMARY KEY (`opportunity_meeting_id`),
  2978. ADD KEY `fk_opportunity_meeting_crm_opportunities1_idx` (`opportunity_id`);
  2979.  
  2980. --
  2981. -- Indexes for table `opportunity_meeting_users`
  2982. --
  2983. ALTER TABLE `opportunity_meeting_users`
  2984. ADD PRIMARY KEY (`opportunity_meeting_id`,`opportunity_user_id`),
  2985. ADD KEY `fk_opportunity_meeting_has_crm_users_crm_users1_idx` (`opportunity_user_id`),
  2986. ADD KEY `fk_opportunity_meeting_has_crm_users_opportunity_meeting1_idx` (`opportunity_meeting_id`);
  2987.  
  2988. --
  2989. -- Indexes for table `opportunity_notes`
  2990. --
  2991. ALTER TABLE `opportunity_notes`
  2992. ADD PRIMARY KEY (`opportunity_note_id`),
  2993. ADD KEY `fk_opportunity_notes_crm_opportunities1_idx` (`opportunity_id`);
  2994.  
  2995. --
  2996. -- Indexes for table `opportunity_tasks`
  2997. --
  2998. ALTER TABLE `opportunity_tasks`
  2999. ADD PRIMARY KEY (`opportunity_task_id`),
  3000. ADD KEY `fk_opportunity_tasks_crm_opportunities1_idx` (`opportunity_id`);
  3001.  
  3002. --
  3003. -- AUTO_INCREMENT for dumped tables
  3004. --
  3005.  
  3006. --
  3007. -- AUTO_INCREMENT for table `company_calls`
  3008. --
  3009. ALTER TABLE `company_calls`
  3010. MODIFY `company_call_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
  3011. --
  3012. -- AUTO_INCREMENT for table `company_files`
  3013. --
  3014. ALTER TABLE `company_files`
  3015. MODIFY `company_files_id` int(11) NOT NULL AUTO_INCREMENT;
  3016. --
  3017. -- AUTO_INCREMENT for table `company_meeting`
  3018. --
  3019. ALTER TABLE `company_meeting`
  3020. MODIFY `company_meeting_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
  3021. --
  3022. -- AUTO_INCREMENT for table `company_notes`
  3023. --
  3024. ALTER TABLE `company_notes`
  3025. MODIFY `company_note_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
  3026. --
  3027. -- AUTO_INCREMENT for table `company_tasks`
  3028. --
  3029. ALTER TABLE `company_tasks`
  3030. MODIFY `company_task_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
  3031. --
  3032. -- AUTO_INCREMENT for table `contact_calls`
  3033. --
  3034. ALTER TABLE `contact_calls`
  3035. MODIFY `contact_call_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
  3036. --
  3037. -- AUTO_INCREMENT for table `contact_files`
  3038. --
  3039. ALTER TABLE `contact_files`
  3040. MODIFY `contact_files_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
  3041. --
  3042. -- AUTO_INCREMENT for table `contact_meeting`
  3043. --
  3044. ALTER TABLE `contact_meeting`
  3045. MODIFY `contact_meeting_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
  3046. --
  3047. -- AUTO_INCREMENT for table `contact_notes`
  3048. --
  3049. ALTER TABLE `contact_notes`
  3050. MODIFY `contact_note_id` int(11) NOT NULL AUTO_INCREMENT;
  3051. --
  3052. -- AUTO_INCREMENT for table `contact_tasks`
  3053. --
  3054. ALTER TABLE `contact_tasks`
  3055. MODIFY `contact_task_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
  3056. --
  3057. -- AUTO_INCREMENT for table `crm_campaigns`
  3058. --
  3059. ALTER TABLE `crm_campaigns`
  3060. MODIFY `campaign_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=12;
  3061. --
  3062. -- AUTO_INCREMENT for table `crm_companies`
  3063. --
  3064. ALTER TABLE `crm_companies`
  3065. MODIFY `company_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=48;
  3066. --
  3067. -- AUTO_INCREMENT for table `crm_contacts`
  3068. --
  3069. ALTER TABLE `crm_contacts`
  3070. MODIFY `contact_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=99;
  3071. --
  3072. -- AUTO_INCREMENT for table `crm_groups`
  3073. --
  3074. ALTER TABLE `crm_groups`
  3075. MODIFY `group_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
  3076. --
  3077. -- AUTO_INCREMENT for table `crm_industry`
  3078. --
  3079. ALTER TABLE `crm_industry`
  3080. MODIFY `industry_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
  3081. --
  3082. -- AUTO_INCREMENT for table `crm_invoices`
  3083. --
  3084. ALTER TABLE `crm_invoices`
  3085. MODIFY `invoice_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=56;
  3086. --
  3087. -- AUTO_INCREMENT for table `crm_invoice_details`
  3088. --
  3089. ALTER TABLE `crm_invoice_details`
  3090. MODIFY `invoice_detail_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=43;
  3091. --
  3092. -- AUTO_INCREMENT for table `crm_leads`
  3093. --
  3094. ALTER TABLE `crm_leads`
  3095. MODIFY `lead_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=78;
  3096. --
  3097. -- AUTO_INCREMENT for table `crm_lead_label`
  3098. --
  3099. ALTER TABLE `crm_lead_label`
  3100. MODIFY `lead_label_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
  3101. --
  3102. -- AUTO_INCREMENT for table `crm_modules`
  3103. --
  3104. ALTER TABLE `crm_modules`
  3105. MODIFY `module_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;
  3106. --
  3107. -- AUTO_INCREMENT for table `crm_opportunities`
  3108. --
  3109. ALTER TABLE `crm_opportunities`
  3110. MODIFY `opportunity_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=41;
  3111. --
  3112. -- AUTO_INCREMENT for table `crm_opportunity_stage`
  3113. --
  3114. ALTER TABLE `crm_opportunity_stage`
  3115. MODIFY `opportunity_stage_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;
  3116. --
  3117. -- AUTO_INCREMENT for table `crm_peoples`
  3118. --
  3119. ALTER TABLE `crm_peoples`
  3120. MODIFY `people_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=90;
  3121. --
  3122. -- AUTO_INCREMENT for table `crm_products`
  3123. --
  3124. ALTER TABLE `crm_products`
  3125. MODIFY `product_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;
  3126. --
  3127. -- AUTO_INCREMENT for table `crm_product_categories`
  3128. --
  3129. ALTER TABLE `crm_product_categories`
  3130. MODIFY `category_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
  3131. --
  3132. -- AUTO_INCREMENT for table `crm_roles`
  3133. --
  3134. ALTER TABLE `crm_roles`
  3135. MODIFY `role_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'User Administrator Hard Code All access module', AUTO_INCREMENT=4;
  3136. --
  3137. -- AUTO_INCREMENT for table `crm_setup_company`
  3138. --
  3139. ALTER TABLE `crm_setup_company`
  3140. MODIFY `company_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
  3141. --
  3142. -- AUTO_INCREMENT for table `crm_source`
  3143. --
  3144. ALTER TABLE `crm_source`
  3145. MODIFY `source_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
  3146. --
  3147. -- AUTO_INCREMENT for table `crm_terms`
  3148. --
  3149. ALTER TABLE `crm_terms`
  3150. MODIFY `term_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
  3151. --
  3152. -- AUTO_INCREMENT for table `crm_users`
  3153. --
  3154. ALTER TABLE `crm_users`
  3155. MODIFY `user_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=18;
  3156. --
  3157. -- AUTO_INCREMENT for table `lead_calls`
  3158. --
  3159. ALTER TABLE `lead_calls`
  3160. MODIFY `lead_call_id` int(11) NOT NULL AUTO_INCREMENT;
  3161. --
  3162. -- AUTO_INCREMENT for table `lead_files`
  3163. --
  3164. ALTER TABLE `lead_files`
  3165. MODIFY `lead_files_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=20;
  3166. --
  3167. -- AUTO_INCREMENT for table `lead_meeting`
  3168. --
  3169. ALTER TABLE `lead_meeting`
  3170. MODIFY `lead_meeting_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=21;
  3171. --
  3172. -- AUTO_INCREMENT for table `lead_notes`
  3173. --
  3174. ALTER TABLE `lead_notes`
  3175. MODIFY `lead_note_id` int(11) NOT NULL AUTO_INCREMENT;
  3176. --
  3177. -- AUTO_INCREMENT for table `lead_tasks`
  3178. --
  3179. ALTER TABLE `lead_tasks`
  3180. MODIFY `lead_task_id` int(11) NOT NULL AUTO_INCREMENT;
  3181. --
  3182. -- AUTO_INCREMENT for table `opportunity_calls`
  3183. --
  3184. ALTER TABLE `opportunity_calls`
  3185. MODIFY `opportunity_call_id` int(11) NOT NULL AUTO_INCREMENT;
  3186. --
  3187. -- AUTO_INCREMENT for table `opportunity_files`
  3188. --
  3189. ALTER TABLE `opportunity_files`
  3190. MODIFY `opportunity_files_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;
  3191. --
  3192. -- AUTO_INCREMENT for table `opportunity_meeting`
  3193. --
  3194. ALTER TABLE `opportunity_meeting`
  3195. MODIFY `opportunity_meeting_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;
  3196. --
  3197. -- AUTO_INCREMENT for table `opportunity_notes`
  3198. --
  3199. ALTER TABLE `opportunity_notes`
  3200. MODIFY `opportunity_note_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
  3201. --
  3202. -- AUTO_INCREMENT for table `opportunity_tasks`
  3203. --
  3204. ALTER TABLE `opportunity_tasks`
  3205. MODIFY `opportunity_task_id` int(11) NOT NULL AUTO_INCREMENT;
  3206. --
  3207. -- Constraints for dumped tables
  3208. --
  3209.  
  3210. --
  3211. -- Constraints for table `company_calls`
  3212. --
  3213. ALTER TABLE `company_calls`
  3214. ADD CONSTRAINT `fk_company_calls_crm_company1` FOREIGN KEY (`company_id`) REFERENCES `crm_companies` (`company_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
  3215.  
  3216. --
  3217. -- Constraints for table `company_files`
  3218. --
  3219. ALTER TABLE `company_files`
  3220. ADD CONSTRAINT `fk_company_files_crm_company1` FOREIGN KEY (`company_id`) REFERENCES `crm_companies` (`company_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
  3221.  
  3222. --
  3223. -- Constraints for table `company_meeting`
  3224. --
  3225. ALTER TABLE `company_meeting`
  3226. ADD CONSTRAINT `fk_company_meeting_crm_company1` FOREIGN KEY (`company_id`) REFERENCES `crm_companies` (`company_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
  3227.  
  3228. --
  3229. -- Constraints for table `company_meeting_users`
  3230. --
  3231. ALTER TABLE `company_meeting_users`
  3232. ADD CONSTRAINT `fk_company_meeting_has_crm_users_company_meeting1` FOREIGN KEY (`company_meeting_id`) REFERENCES `company_meeting` (`company_meeting_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  3233. ADD CONSTRAINT `fk_company_meeting_has_crm_users_crm_users1` FOREIGN KEY (`company_user_id`) REFERENCES `crm_users` (`user_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
  3234.  
  3235. --
  3236. -- Constraints for table `company_notes`
  3237. --
  3238. ALTER TABLE `company_notes`
  3239. ADD CONSTRAINT `fk_company_notes_crm_company1` FOREIGN KEY (`company_id`) REFERENCES `crm_companies` (`company_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
  3240.  
  3241. --
  3242. -- Constraints for table `company_tasks`
  3243. --
  3244. ALTER TABLE `company_tasks`
  3245. ADD CONSTRAINT `fk_company_tasks_crm_company1` FOREIGN KEY (`company_id`) REFERENCES `crm_companies` (`company_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
  3246.  
  3247. --
  3248. -- Constraints for table `contact_calls`
  3249. --
  3250. ALTER TABLE `contact_calls`
  3251. ADD CONSTRAINT `fk_customer_calls_crm_customers1` FOREIGN KEY (`contact_id`) REFERENCES `crm_contacts` (`contact_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
  3252.  
  3253. --
  3254. -- Constraints for table `contact_files`
  3255. --
  3256. ALTER TABLE `contact_files`
  3257. ADD CONSTRAINT `fk_customer_files_copy1_crm_customers1` FOREIGN KEY (`contact_id`) REFERENCES `crm_contacts` (`contact_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
  3258.  
  3259. --
  3260. -- Constraints for table `contact_meeting`
  3261. --
  3262. ALTER TABLE `contact_meeting`
  3263. ADD CONSTRAINT `fk_customer_meeting_crm_customers1` FOREIGN KEY (`contact_id`) REFERENCES `crm_contacts` (`contact_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
  3264.  
  3265. --
  3266. -- Constraints for table `contact_meeting_users`
  3267. --
  3268. ALTER TABLE `contact_meeting_users`
  3269. ADD CONSTRAINT `fk_contact_meeting_has_crm_users_contact_meeting1` FOREIGN KEY (`contact_meeting_id`) REFERENCES `contact_meeting` (`contact_meeting_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  3270. ADD CONSTRAINT `fk_contact_meeting_has_crm_users_crm_users1` FOREIGN KEY (`contact_user_id`) REFERENCES `crm_users` (`user_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
  3271.  
  3272. --
  3273. -- Constraints for table `contact_notes`
  3274. --
  3275. ALTER TABLE `contact_notes`
  3276. ADD CONSTRAINT `fk_customer_notes_crm_customers1` FOREIGN KEY (`contact_id`) REFERENCES `crm_contacts` (`contact_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
  3277.  
  3278. --
  3279. -- Constraints for table `contact_tasks`
  3280. --
  3281. ALTER TABLE `contact_tasks`
  3282. ADD CONSTRAINT `fk_customer_tasks_crm_customers1` FOREIGN KEY (`contact_id`) REFERENCES `crm_contacts` (`contact_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
  3283.  
  3284. --
  3285. -- Constraints for table `crm_companies`
  3286. --
  3287. ALTER TABLE `crm_companies`
  3288. ADD CONSTRAINT `fk_crm_companies_crm_companies1` FOREIGN KEY (`company_parent`) REFERENCES `crm_companies` (`company_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  3289. ADD CONSTRAINT `fk_crm_company_crm_groups1` FOREIGN KEY (`company_groups`) REFERENCES `crm_groups` (`group_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  3290. ADD CONSTRAINT `fk_crm_company_crm_industry1` FOREIGN KEY (`company_industry`) REFERENCES `crm_industry` (`industry_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
  3291.  
  3292. --
  3293. -- Constraints for table `crm_contacts`
  3294. --
  3295. ALTER TABLE `crm_contacts`
  3296. ADD CONSTRAINT `fk_crm_contacts_crm_peoples1` FOREIGN KEY (`people_id`) REFERENCES `crm_peoples` (`people_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  3297. ADD CONSTRAINT `fk_crm_customer_crm_company1` FOREIGN KEY (`company_id`) REFERENCES `crm_companies` (`company_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  3298. ADD CONSTRAINT `fk_crm_customer_crm_groups1` FOREIGN KEY (`contact_groups`) REFERENCES `crm_groups` (`group_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
  3299.  
  3300. --
  3301. -- Constraints for table `crm_invoices`
  3302. --
  3303. ALTER TABLE `crm_invoices`
  3304. ADD CONSTRAINT `fk_crm_invoices_crm_companies1` FOREIGN KEY (`company_id`) REFERENCES `crm_companies` (`company_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  3305. ADD CONSTRAINT `fk_crm_invoices_crm_terms1` FOREIGN KEY (`term_id`) REFERENCES `crm_terms` (`term_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
  3306.  
  3307. --
  3308. -- Constraints for table `crm_invoices_products`
  3309. --
  3310. ALTER TABLE `crm_invoices_products`
  3311. ADD CONSTRAINT `fk_crm_invoice_details_has_crm_products_crm_invoice_details1` FOREIGN KEY (`invoice_detail_id`) REFERENCES `crm_invoice_details` (`invoice_detail_id`),
  3312. ADD CONSTRAINT `fk_crm_invoice_details_has_crm_products_crm_products1` FOREIGN KEY (`product_id`) REFERENCES `crm_products` (`product_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
  3313.  
  3314. --
  3315. -- Constraints for table `crm_invoice_details`
  3316. --
  3317. ALTER TABLE `crm_invoice_details`
  3318. ADD CONSTRAINT `fk_crm_invoice_details_crm_invoices1` FOREIGN KEY (`invoice_id`) REFERENCES `crm_invoices` (`invoice_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
  3319.  
  3320. --
  3321. -- Constraints for table `crm_leads`
  3322. --
  3323. ALTER TABLE `crm_leads`
  3324. ADD CONSTRAINT `fk_crm_leads_crm_groups1` FOREIGN KEY (`lead_groups`) REFERENCES `crm_groups` (`group_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  3325. ADD CONSTRAINT `fk_crm_leads_crm_lead_status1` FOREIGN KEY (`lead_label`) REFERENCES `crm_lead_label` (`lead_label_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  3326. ADD CONSTRAINT `fk_crm_leads_crm_peoples1` FOREIGN KEY (`people_id`) REFERENCES `crm_peoples` (`people_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
  3327.  
  3328. --
  3329. -- Constraints for table `crm_opportunities`
  3330. --
  3331. ALTER TABLE `crm_opportunities`
  3332. ADD CONSTRAINT `fk_crm_opportunities_crm_campaigns1` FOREIGN KEY (`opportunity_campaign_id`) REFERENCES `crm_campaigns` (`campaign_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  3333. ADD CONSTRAINT `fk_crm_opportunities_crm_companies1` FOREIGN KEY (`company_id`) REFERENCES `crm_companies` (`company_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  3334. ADD CONSTRAINT `fk_crm_opportunities_crm_opportunity_stage1` FOREIGN KEY (`opportunity_stage_id`) REFERENCES `crm_opportunity_stage` (`opportunity_stage_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
  3335.  
  3336. --
  3337. -- Constraints for table `crm_opportunities_contacts`
  3338. --
  3339. ALTER TABLE `crm_opportunities_contacts`
  3340. ADD CONSTRAINT `fk_crm_opportunities_has_crm_contacts_crm_contacts1` FOREIGN KEY (`contact_id`) REFERENCES `crm_contacts` (`contact_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  3341. ADD CONSTRAINT `fk_crm_opportunities_has_crm_contacts_crm_opportunities1` FOREIGN KEY (`opportunity_id`) REFERENCES `crm_opportunities` (`opportunity_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
  3342.  
  3343. --
  3344. -- Constraints for table `crm_opportunities_products`
  3345. --
  3346. ALTER TABLE `crm_opportunities_products`
  3347. ADD CONSTRAINT `fk_crm_opportunities_has_crm_products_crm_opportunities1` FOREIGN KEY (`opportunity_id`) REFERENCES `crm_opportunities` (`opportunity_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  3348. ADD CONSTRAINT `fk_crm_opportunities_has_crm_products_crm_products1` FOREIGN KEY (`product_id`) REFERENCES `crm_products` (`product_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
  3349.  
  3350. --
  3351. -- Constraints for table `crm_peoples`
  3352. --
  3353. ALTER TABLE `crm_peoples`
  3354. ADD CONSTRAINT `fk_peoples_crm_industry1` FOREIGN KEY (`people_industry`) REFERENCES `crm_industry` (`industry_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  3355. ADD CONSTRAINT `fk_peoples_crm_source1` FOREIGN KEY (`people_source`) REFERENCES `crm_source` (`source_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
  3356.  
  3357. --
  3358. -- Constraints for table `crm_products`
  3359. --
  3360. ALTER TABLE `crm_products`
  3361. ADD CONSTRAINT `crm_products_crm_product_categories_category_id_fk` FOREIGN KEY (`product_category_id`) REFERENCES `crm_product_categories` (`category_id`);
  3362.  
  3363. --
  3364. -- Constraints for table `crm_products_campaigns`
  3365. --
  3366. ALTER TABLE `crm_products_campaigns`
  3367. ADD CONSTRAINT `fk_crm_products_has_crm_campaigns_crm_campaigns1` FOREIGN KEY (`campaign_id`) REFERENCES `crm_campaigns` (`campaign_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  3368. ADD CONSTRAINT `fk_crm_products_has_crm_campaigns_crm_products1` FOREIGN KEY (`product_id`) REFERENCES `crm_products` (`product_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
  3369.  
  3370. --
  3371. -- Constraints for table `crm_roles_modules`
  3372. --
  3373. ALTER TABLE `crm_roles_modules`
  3374. ADD CONSTRAINT `fk_crm_roles_has_crm_modules_crm_modules1` FOREIGN KEY (`crm_modules_module_id`) REFERENCES `crm_modules` (`module_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  3375. ADD CONSTRAINT `fk_crm_roles_has_crm_modules_crm_roles1` FOREIGN KEY (`crm_roles_role_id`) REFERENCES `crm_roles` (`role_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
  3376.  
  3377. --
  3378. -- Constraints for table `crm_users`
  3379. --
  3380. ALTER TABLE `crm_users`
  3381. ADD CONSTRAINT `crm_users_crm_groups_group_id_fk` FOREIGN KEY (`user_group_id`) REFERENCES `crm_groups` (`group_id`),
  3382. ADD CONSTRAINT `fk_crm_users_crm_roles1` FOREIGN KEY (`user_role_id`) REFERENCES `crm_roles` (`role_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  3383. ADD CONSTRAINT `fk_crm_users_crm_users1` FOREIGN KEY (`user_manager_id`) REFERENCES `crm_users` (`user_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
  3384.  
  3385. --
  3386. -- Constraints for table `crm_users_companies`
  3387. --
  3388. ALTER TABLE `crm_users_companies`
  3389. ADD CONSTRAINT `fk_crm_company_has_crm_users_crm_company1` FOREIGN KEY (`company_id`) REFERENCES `crm_companies` (`company_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  3390. ADD CONSTRAINT `fk_crm_company_has_crm_users_crm_users1` FOREIGN KEY (`user_id`) REFERENCES `crm_users` (`user_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
  3391.  
  3392. --
  3393. -- Constraints for table `crm_users_contacts`
  3394. --
  3395. ALTER TABLE `crm_users_contacts`
  3396. ADD CONSTRAINT `fk_crm_users_has_crm_customer_crm_customer1` FOREIGN KEY (`contact_id`) REFERENCES `crm_contacts` (`contact_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  3397. ADD CONSTRAINT `fk_crm_users_has_crm_customer_crm_users1` FOREIGN KEY (`user_id`) REFERENCES `crm_users` (`user_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
  3398.  
  3399. --
  3400. -- Constraints for table `crm_users_leads`
  3401. --
  3402. ALTER TABLE `crm_users_leads`
  3403. ADD CONSTRAINT `fk_crm_users_has_crm_leads_crm_leads1` FOREIGN KEY (`lead_id`) REFERENCES `crm_leads` (`lead_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  3404. ADD CONSTRAINT `fk_crm_users_has_crm_leads_crm_users1` FOREIGN KEY (`user_id`) REFERENCES `crm_users` (`user_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
  3405.  
  3406. --
  3407. -- Constraints for table `lead_calls`
  3408. --
  3409. ALTER TABLE `lead_calls`
  3410. ADD CONSTRAINT `fk_lead_calls_crm_leads1` FOREIGN KEY (`lead_id`) REFERENCES `crm_leads` (`lead_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
  3411.  
  3412. --
  3413. -- Constraints for table `lead_files`
  3414. --
  3415. ALTER TABLE `lead_files`
  3416. ADD CONSTRAINT `fk_lead_files_crm_leads1` FOREIGN KEY (`lead_id`) REFERENCES `crm_leads` (`lead_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
  3417.  
  3418. --
  3419. -- Constraints for table `lead_meeting`
  3420. --
  3421. ALTER TABLE `lead_meeting`
  3422. ADD CONSTRAINT `fk_lead_meeting_crm_leads1` FOREIGN KEY (`lead_id`) REFERENCES `crm_leads` (`lead_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
  3423.  
  3424. --
  3425. -- Constraints for table `lead_meeting_users`
  3426. --
  3427. ALTER TABLE `lead_meeting_users`
  3428. ADD CONSTRAINT `fk_lead_meeting_has_crm_users_crm_users1` FOREIGN KEY (`lead_user_id`) REFERENCES `crm_users` (`user_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  3429. ADD CONSTRAINT `fk_lead_meeting_has_crm_users_lead_meeting1` FOREIGN KEY (`lead_meeting_id`) REFERENCES `lead_meeting` (`lead_meeting_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
  3430.  
  3431. --
  3432. -- Constraints for table `lead_notes`
  3433. --
  3434. ALTER TABLE `lead_notes`
  3435. ADD CONSTRAINT `fk_lead_notes_crm_leads1` FOREIGN KEY (`lead_id`) REFERENCES `crm_leads` (`lead_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
  3436.  
  3437. --
  3438. -- Constraints for table `lead_tasks`
  3439. --
  3440. ALTER TABLE `lead_tasks`
  3441. ADD CONSTRAINT `fk_lead_task_crm_leads1` FOREIGN KEY (`lead_id`) REFERENCES `crm_leads` (`lead_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
  3442.  
  3443. --
  3444. -- Constraints for table `opportunity_calls`
  3445. --
  3446. ALTER TABLE `opportunity_calls`
  3447. ADD CONSTRAINT `fk_opportunity_calls_crm_opportunities1` FOREIGN KEY (`opportunity_id`) REFERENCES `crm_opportunities` (`opportunity_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
  3448.  
  3449. --
  3450. -- Constraints for table `opportunity_files`
  3451. --
  3452. ALTER TABLE `opportunity_files`
  3453. ADD CONSTRAINT `fk_opportunity_files_crm_opportunities1` FOREIGN KEY (`opportunity_id`) REFERENCES `crm_opportunities` (`opportunity_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
  3454.  
  3455. --
  3456. -- Constraints for table `opportunity_meeting`
  3457. --
  3458. ALTER TABLE `opportunity_meeting`
  3459. ADD CONSTRAINT `fk_opportunity_meeting_crm_opportunities1` FOREIGN KEY (`opportunity_id`) REFERENCES `crm_opportunities` (`opportunity_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
  3460.  
  3461. --
  3462. -- Constraints for table `opportunity_meeting_users`
  3463. --
  3464. ALTER TABLE `opportunity_meeting_users`
  3465. ADD CONSTRAINT `fk_opportunity_meeting_has_crm_users_crm_users1` FOREIGN KEY (`opportunity_user_id`) REFERENCES `crm_users` (`user_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  3466. ADD CONSTRAINT `fk_opportunity_meeting_has_crm_users_opportunity_meeting1` FOREIGN KEY (`opportunity_meeting_id`) REFERENCES `opportunity_meeting` (`opportunity_meeting_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
  3467.  
  3468. --
  3469. -- Constraints for table `opportunity_notes`
  3470. --
  3471. ALTER TABLE `opportunity_notes`
  3472. ADD CONSTRAINT `fk_opportunity_notes_crm_opportunities1` FOREIGN KEY (`opportunity_id`) REFERENCES `crm_opportunities` (`opportunity_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
  3473.  
  3474. --
  3475. -- Constraints for table `opportunity_tasks`
  3476. --
  3477. ALTER TABLE `opportunity_tasks`
  3478. ADD CONSTRAINT `fk_opportunity_tasks_crm_opportunities1` FOREIGN KEY (`opportunity_id`) REFERENCES `crm_opportunities` (`opportunity_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
  3479. COMMIT;
  3480.  
  3481. /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
  3482. /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
  3483. /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement