c7hTe6PQ

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