dGPP6yJF

1-- phpMyAdmin SQL Dump
2-- version 4.7.4
3-- https://www.phpmyadmin.net/
4--
5-- Host: localhost
6-- Generation Time: Sep 29, 2017 at 10:12 AM
7-- Server version: 10.1.26-MariaDB
8-- PHP Version: 7.0.23
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_db`
23--
24
25DELIMITER $$
26--
27-- Procedures
28--
29DROP PROCEDURE IF EXISTS `build_email_list`$$
30CREATE DEFINER=`root`@`localhost` PROCEDURE `build_email_list` (INOUT `email_list` VARCHAR(4000))  BEGIN
31 
32 DECLARE v_finished INTEGER DEFAULT 0;
33        DECLARE v_email varchar(100) DEFAULT "";
34 
35  DEClARE email_cursor CURSOR FOR 
36 SELECT invoice_id FROM crm_invoices;
37 
38  DECLARE CONTINUE HANDLER 
39        FOR NOT FOUND SET v_finished = 1;
40 
41 OPEN email_cursor;
42 
43 get_email: LOOP
44 
45 FETCH email_cursor INTO v_email;
46 
47 IF v_finished = 1 THEN 
48 LEAVE get_email;
49 END IF;
50 
51  SET email_list = CONCAT(v_email,";",email_list);
52 
53 END LOOP get_email;
54 
55 CLOSE email_cursor;
56 
57END$$
58
59DROP PROCEDURE IF EXISTS `coba`$$
60CREATE DEFINER=`root`@`localhost` PROCEDURE `coba` ()  NO SQL
61BEGIN
62SET @date_param = (SELECT DATE_FORMAT(CURDATE(), "%m%y"));
63SET @inv_code = (SELECT invoice_code FROM crm_invoice_details WHERE invoice_code LIKE CONCAT('%',@date_param,'%') ORDER BY invoice_detail_id DESC LIMIT 1);
64
65IF @inv_code IS null THEN
66 SET @new_inv_code = CONCAT('INV-', @date_param, '00001');
67ELSE
68    SET @new_numb_code = (SELECT SUBSTRING_INDEX(@inv_code, '-', -1));
69    SET @new_code = @new_numb_code + 1;
70    SET @sub_code = substring(@new_code, -5);
71    SET @new_inv_code = CONCAT('INV-', @date_param, @sub_code);
72END IF;
73
74SELECT @new_inv_code;
75END$$
76
77DROP PROCEDURE IF EXISTS `sp_campaign_insert`$$
78CREATE 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
79BEGIN
80 
81DECLARE exit handler for sqlexception
82  BEGIN
83  GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
84 @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
85SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
86SELECT @full_error;
87        SET sukses=0;
88  ROLLBACK;
89END;
90 
91DECLARE exit handler for sqlwarning
92 BEGIN
93  GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
94 @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
95SET @full_error = CONCAT("Warning ", @errno, " (", @sqlstate, "): ", @text);
96SELECT @full_error;
97        SET sukses=0;
98 ROLLBACK;
99END;
100    drop temporary table if exists temp_cursor_table;
101    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 ,') )');
102  prepare pst from @temp;
103  execute pst;
104  drop prepare pst;
105 
106  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);
107  SET c_id = LAST_INSERT_ID();
108    SET sukses = 1;
109COMMIT;
110END$$
111
112DROP PROCEDURE IF EXISTS `sp_company_insert`$$
113CREATE 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, OUT `company_id` INT)  NO SQL
114BEGIN
115 
116DECLARE exit handler for sqlexception
117  BEGIN
118  GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
119 @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
120SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
121SELECT @full_error;
122        SET sukses=0;
123  ROLLBACK;
124END;
125 
126DECLARE exit handler for sqlwarning
127 BEGIN
128  GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
129 @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
130SET @full_error = CONCAT("Warning ", @errno, " (", @sqlstate, "): ", @text);
131SELECT @full_error;
132        SET sukses=0;
133 ROLLBACK;
134END;
135    drop temporary table if exists temp_cursor_table;
136    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 ,') )');
137  prepare pst from @temp;
138  execute pst;
139  drop prepare pst;
140 
141  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
142(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);
143SET @l_id = LAST_INSERT_ID();
144SET company_id = LAST_INSERT_ID();
145INSERT INTO `crm_users_companies`(`company_id`,`user_id`)
146  SELECT @l_id,u_id from temp_cursor_table;
147  drop temporary table if exists temp_cursor_table;
148    SET sukses = 1;
149COMMIT;
150END$$
151
152DROP PROCEDURE IF EXISTS `sp_company_meeting_delete`$$
153CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_company_meeting_delete` (IN `meet_id` INT, OUT `sukses` INT)  NO SQL
154BEGIN
155 
156 
157DECLARE exit handler for sqlexception
158  BEGIN
159      GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
160 @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
161SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
162SELECT @full_error;
163    SET sukses=0;
164  ROLLBACK;
165END;
166 
167DECLARE exit handler for sqlwarning
168 BEGIN
169      GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
170 @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
171SET @full_error = CONCAT("Warning ", @errno, " (", @sqlstate, "): ", @text);
172SELECT @full_error;
173    SET sukses=0;
174 ROLLBACK;
175END;
176 
177START TRANSACTION;
178    DELETE FROM company_meeting_users WHERE company_meeting_id = meet_id;
179    DELETE FROM company_meeting WHERE company_meeting_id = meet_id;
180    SET sukses = 1;
181COMMIT;
182END$$
183
184DROP PROCEDURE IF EXISTS `sp_company_meeting_insert`$$
185CREATE 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
186BEGIN  
187 
188DECLARE exit handler for sqlexception
189  BEGIN
190      GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
191 @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
192SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
193SELECT @full_error;
194    SET sukses=0;
195  ROLLBACK;
196END;
197 
198DECLARE exit handler for sqlwarning
199 BEGIN
200      GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
201 @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
202SET @full_error = CONCAT("Warning ", @errno, " (", @sqlstate, "): ", @text);
203SELECT @full_error;
204    SET sukses=0;
205 ROLLBACK;
206END;
207    drop temporary table if exists temp_cursor_table;
208    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 ,') )');
209  prepare pst from @temp;
210  execute pst;
211  drop prepare pst;
212INSERT 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);
213  SET @meet_id = LAST_INSERT_ID();
214INSERT INTO `company_meeting_users`(`company_meeting_id`,`company_user_id`)
215  SELECT @meet_id,user_id from temp_cursor_table;
216  drop temporary table if exists temp_cursor_table;
217  set sukses=1;
218COMMIT;
219END$$
220
221DROP PROCEDURE IF EXISTS `sp_company_meeting_update`$$
222CREATE 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
223BEGIN  
224 
225DECLARE exit handler for sqlexception
226  BEGIN
227      GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
228 @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
229SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
230SELECT @full_error;
231    SET sukses=0;
232  ROLLBACK;
233END;
234 
235DECLARE exit handler for sqlwarning
236 BEGIN
237      GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
238 @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
239SET @full_error = CONCAT("Warning ", @errno, " (", @sqlstate, "): ", @text);
240SELECT @full_error;
241    SET sukses=0;
242 ROLLBACK;
243END;
244    drop temporary table if exists temp_cursor_table;
245    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 ,') )');
246  prepare pst from @temp;
247  execute pst;
248  drop prepare pst;
249UPDATE `company_meeting` SET
250`company_meeting_name`=name,
251`company_meeting_location`=location,
252`company_meeting_start_date`=start_date,
253`company_meeting_end_date`=end_date,
254`company_meeting_description`=description,
255`company_meeting_date`=date,
256`company_meeting_user`=user,
257`company_id`=l_id
258WHERE `company_meeting_id`=meet_id;
259delete from company_meeting_users where company_meeting_id=meet_id;
260REPLACE INTO `company_meeting_users`(`company_meeting_id`,`company_user_id`)
261  SELECT meet_id,user_id from temp_cursor_table;
262  drop temporary table if exists temp_cursor_table;
263set sukses=1;
264COMMIT;
265END$$
266
267DROP PROCEDURE IF EXISTS `sp_company_update`$$
268CREATE 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
269BEGIN
270 
271DECLARE exit handler for sqlexception
272  BEGIN
273  GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
274 @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
275SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
276SELECT @full_error;
277        SET sukses=0;
278  ROLLBACK;
279END;
280 
281DECLARE exit handler for sqlwarning
282 BEGIN
283  GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
284 @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
285SET @full_error = CONCAT("Warning ", @errno, " (", @sqlstate, "): ", @text);
286SELECT @full_error;
287        SET sukses=0;
288 ROLLBACK;
289END;
290    drop temporary table if exists temp_cursor_table;
291    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 ,') )');
292  prepare pst from @temp;
293  execute pst;
294  drop prepare pst;
295UPDATE `crm_companies` SET
296    `customer_code`=code,
297    `company_name`=name,
298    `company_parent`=parent,
299    `company_office_phone`=office_phone,
300    `company_industry`=industry,
301    `company_email`=email,
302    `company_fax`=fax,
303    `company_employee`=employee,
304    `company_revenue`=revenue,
305    `company_website`=website,
306    `company_address1`=address1,
307    `company_address2`=address2,
308    `company_city`=city,
309    `company_state`=state,
310    `company_zip_code`=zip_code,
311    `company_country`=country,
312    `company_billing_address1`=billing_address1,
313    `company_billing_address2`=billing_address2,
314    `company_billing_city`=billing_city,
315    `company_billing_state`=billing_state,
316    `company_billing_zip_code`=billing_zip_code,
317    `company_billing_country`=billing_country,
318    `company_right_permission`=right_permission,
319    `user_modified`=u_modified,
320    `user_modified_date`=date_modified,
321    `company_groups`=groups,
322    `company_customer`=customer
323WHERE `company_id`=c_id;
324delete from crm_users_companies where company_id=c_id;
325REPLACE INTO `crm_users_companies`(`company_id`,`user_id`)
326  SELECT c_id,u_id from temp_cursor_table;
327  drop temporary table if exists temp_cursor_table;
328    SET sukses = 1;
329COMMIT;
330END$$
331
332DROP PROCEDURE IF EXISTS `sp_contact_insert`$$
333CREATE 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
334BEGIN
335 
336DECLARE exit handler for sqlexception
337  BEGIN
338  GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
339 @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
340SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
341SELECT @full_error;
342        SET sukses=0;
343  ROLLBACK;
344END;
345 
346DECLARE exit handler for sqlwarning
347 BEGIN
348  GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
349 @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
350SET @full_error = CONCAT("Warning ", @errno, " (", @sqlstate, "): ", @text);
351SELECT @full_error;
352        SET sukses=0;
353 ROLLBACK;
354END;
355    drop temporary table if exists temp_cursor_table;
356    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 ,') )');
357  prepare pst from @temp;
358  execute pst;
359  drop prepare pst;
360 
361  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');
362  SET @p_id = LAST_INSERT_ID();
363INSERT INTO `crm_contacts`(`people_id`, `contact_right_permission`, `contact_groups`, `company_id`) VALUES (@p_id, right_permission, groups, comp_id);
364SET @l_id = LAST_INSERT_ID();
365INSERT INTO `crm_users_contacts`(`contact_id`,`user_id`)
366  SELECT @l_id,u_id from temp_cursor_table;
367        SET contact_id = @l_id;
368  drop temporary table if exists temp_cursor_table;
369    SET sukses = 1;
370COMMIT;
371END$$
372
373DROP PROCEDURE IF EXISTS `sp_contact_meeting_delete`$$
374CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_contact_meeting_delete` (IN `meet_id` INT, OUT `sukses` INT)  NO SQL
375BEGIN
376 
377 
378DECLARE exit handler for sqlexception
379  BEGIN
380  GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
381 @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
382SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
383SELECT @full_error;
384    SET sukses=0;
385  ROLLBACK;
386END;
387 
388DECLARE exit handler for sqlwarning
389 BEGIN
390      GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
391 @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
392SET @full_error = CONCAT("Warning ", @errno, " (", @sqlstate, "): ", @text);
393SELECT @full_error;
394    SET sukses=0;
395 ROLLBACK;
396END;
397 
398START TRANSACTION;
399    DELETE FROM contact_meeting_users WHERE contact_meeting_id = meet_id;
400    DELETE FROM contact_meeting WHERE contact_meeting_id = meet_id;
401    SET sukses = 1;
402COMMIT;
403END$$
404
405DROP PROCEDURE IF EXISTS `sp_contact_meeting_insert`$$
406CREATE 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
407BEGIN  
408 
409DECLARE exit handler for sqlexception
410  BEGIN
411      GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
412 @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
413SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
414SELECT @full_error;
415    SET sukses=0;
416  ROLLBACK;
417END;
418 
419DECLARE exit handler for sqlwarning
420 BEGIN
421      GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
422 @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
423SET @full_error = CONCAT("Warning ", @errno, " (", @sqlstate, "): ", @text);
424SELECT @full_error;
425    SET sukses=0;
426 ROLLBACK;
427END;
428    drop temporary table if exists temp_cursor_table;
429    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 ,') )');
430  prepare pst from @temp;
431  execute pst;
432  drop prepare pst;
433INSERT 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);
434  SET @meet_id = LAST_INSERT_ID();
435INSERT INTO `contact_meeting_users`(`contact_meeting_id`,`contact_user_id`)
436  SELECT @meet_id,user_id from temp_cursor_table;
437  drop temporary table if exists temp_cursor_table;
438  set sukses=1;
439COMMIT;
440END$$
441
442DROP PROCEDURE IF EXISTS `sp_contact_meeting_update`$$
443CREATE 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
444BEGIN  
445 
446DECLARE exit handler for sqlexception
447  BEGIN
448      GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
449 @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
450SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
451SELECT @full_error;
452    SET sukses=0;
453  ROLLBACK;
454END;
455 
456DECLARE exit handler for sqlwarning
457 BEGIN
458      GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
459 @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
460SET @full_error = CONCAT("Warning ", @errno, " (", @sqlstate, "): ", @text);
461SELECT @full_error;
462    SET sukses=0;
463 ROLLBACK;
464END;
465    drop temporary table if exists temp_cursor_table;
466    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 ,') )');
467  prepare pst from @temp;
468  execute pst;
469  drop prepare pst;
470UPDATE `contact_meeting` SET
471`contact_meeting_name`=name,
472`contact_meeting_location`=location,
473`contact_meeting_start_date`=start_date,
474`contact_meeting_end_date`=end_date,
475`contact_meeting_description`=description,
476`contact_meeting_date`=date,
477`contact_meeting_user`=user,
478`contact_id`=l_id
479WHERE `contact_meeting_id`=meet_id;
480delete from contact_meeting_users where contact_meeting_id=meet_id;
481REPLACE INTO `contact_meeting_users`(`contact_meeting_id`,`contact_user_id`)
482  SELECT meet_id,user_id from temp_cursor_table;
483  drop temporary table if exists temp_cursor_table;
484set sukses=1;
485COMMIT;
486END$$
487
488DROP PROCEDURE IF EXISTS `sp_contact_update`$$
489CREATE 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
490BEGIN
491 
492DECLARE exit handler for sqlexception
493  BEGIN
494  GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
495 @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
496SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
497SELECT @full_error;
498        SET sukses=0;
499  ROLLBACK;
500END;
501 
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;
511    drop temporary table if exists temp_cursor_table;
512    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 ,') )');
513  prepare pst from @temp;
514  execute pst;
515  drop prepare pst;
516  UPDATE `crm_peoples` a JOIN crm_contacts b ON a.people_id=b.people_id
517SET
518a.people_salutation=salutation,
519a.people_first_name=first_name,
520a.people_last_name=last_name,
521a.people_job_title=job_title,
522a.people_company=company,
523a.people_department=department,
524a.people_email=email,
525a.people_office_phone=office_phone,
526a.people_mobile_phone=mobile_phone,
527a.people_industry=industry,
528a.people_address1=address1,
529a.people_address2=address2,
530a.people_city=city,
531a.people_state=state,
532a.people_zip_code=zip_code,
533a.people_country=country,
534a.people_source=sources,
535a.people_photo=photo,
536b.contact_right_permission=right_permission,
537b.contact_groups=groups,
538b.company_id=comp_id
539WHERE b.contact_id = l_id;
540delete from crm_users_contacts where contact_id = l_id;
541REPLACE INTO `crm_users_contacts`(`contact_id`,`user_id`)
542  SELECT l_id,u_id from temp_cursor_table;
543  drop temporary table if exists temp_cursor_table;
544    SET sukses = 1;
545COMMIT;
546END$$
547
548DROP PROCEDURE IF EXISTS `sp_convert_lead`$$
549CREATE 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_expected_closing_date` DATETIME, IN `o_service` ENUM('1','2'), IN `o_campaign_id` INT, IN `product_list` VARCHAR(300), IN `uo_user_id` INT, OUT `sukses` INT)  NO SQL
550BEGIN
551 
552DECLARE exit handler for sqlexception
553  BEGIN
554  GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
555 @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
556SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
557SELECT @full_error;
558        SET sukses=0;
559  ROLLBACK;
560END;
561 
562DECLARE continue handler for not found set sukses = 0;
563DECLARE exit handler for sqlwarning
564 BEGIN
565  GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
566 @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
567SET @full_error = CONCAT("Warning ", @errno, " (", @sqlstate, "): ", @text);
568SELECT @full_error;
569        SET sukses=0;
570 ROLLBACK;
571END;
572SELECT 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;
573IF comp_id is null THEN
574    SET @comp_id = null;
575ELSE
576SET @comp_id = comp_id;
577END IF;
578IF @l_status= 1 THEN
579    IF @comp_id IS null THEN
580        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
581(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);
582        set @comp_id = LAST_INSERT_ID();
583        insert into crm_users_companies(company_id, user_id)
584        SELECT @comp_id, user_id from crm_users_leads
585        where lead_id = l_id;
586    END IF;
587    insert into crm_contacts(company_id,people_id,contact_right_permission,contact_groups)
588    SELECT @comp_id,people_id,lead_right_permission,lead_groups from crm_leads where lead_id = l_id;
589    set @c_id = LAST_INSERT_ID();
590    UPDATE crm_leads set lead_status = '2'
591    where lead_id = l_id;
592    insert into crm_users_contacts(contact_id, user_id) VALUES (@c_id, uo_user_id);
593    IF skip='0' THEN
594        drop temporary table if exists temp_cursor_table;
595        CREATE TEMPORARY TABLE IF NOT EXISTS temp_cursor_table (A Int, B Double);
596            set @temp = concat('INSERT INTO temp_cursor_table (A,B) VALUES ', product_list);
597        prepare pst from @temp;
598        execute pst;
599        drop prepare pst;
600        INSERT INTO `crm_opportunities`(`opportunity_code`,`opportunity_name`, `opportunity_stage_id`, `company_id`, `opportunity_amount`, `opportunity_expected_closing_date`, `opportunity_service`, `opportunity_campaign_id`) VALUES (o_code, o_name, o_stage_id, @comp_id, o_amount, o_expected_closing_date, o_service, o_campaign_id);
601        SET @o_id = LAST_INSERT_ID();
602        INSERT INTO crm_users_opportunities (user_id, opportunity_id) VALUES (uo_user_id, @o_id);
603        INSERT INTO crm_opportunities_contacts(opportunity_id,contact_id) VALUES (@o_id,@c_id);
604        INSERT INTO `crm_opportunities_products`(`opportunity_id`, `product_id`, `price`)
605        SELECT @o_id, A, B from temp_cursor_table;
606        drop temporary table if exists temp_cursor_table;
607 
608    END IF;
609END if;
610    SET sukses = 1;
611COMMIT;
612END$$
613
614DROP PROCEDURE IF EXISTS `sp_lead_delete`$$
615CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_lead_delete` (IN `l_id` INT, OUT `sukses` INT)  NO SQL
616BEGIN
617 
618DECLARE exit handler for sqlexception
619  BEGIN
620      GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
621 @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
622SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
623SELECT @full_error;
624    SET sukses=0;
625  ROLLBACK;
626END;
627 
628DECLARE exit handler for sqlwarning
629 BEGIN
630      GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
631 @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
632SET @full_error = CONCAT("Warning ", @errno, " (", @sqlstate, "): ", @text);
633SELECT @full_error;
634    SET sukses=0;
635 ROLLBACK;
636END;
637delete from crm_users_leads where lead_id = l_id;
638DELETE crm_peoples, crm_leads
639FROM crm_peoples
640INNER JOIN crm_leads ON crm_peoples.people_id=crm_leads.people_id
641WHERE crm_leads.lead_id=l_id;
642    SET sukses = 1;
643COMMIT;
644END$$
645
646DROP PROCEDURE IF EXISTS `sp_lead_insert`$$
647CREATE 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
648BEGIN
649 
650DECLARE exit handler for sqlexception
651  BEGIN
652      GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
653 @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
654SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
655SELECT @full_error;
656    SET sukses=0;
657  ROLLBACK;
658END;
659 
660DECLARE exit handler for sqlwarning
661 BEGIN
662      GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
663 @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
664SET @full_error = CONCAT("Warning ", @errno, " (", @sqlstate, "): ", @text);
665SELECT @full_error;
666    SET sukses=0;
667 ROLLBACK;
668END;
669    drop temporary table if exists temp_cursor_table;
670    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 ,') )');
671  prepare pst from @temp;
672  execute pst;
673  drop prepare pst;
674 
675  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);
676  SET @p_id = LAST_INSERT_ID();
677INSERT 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);
678SET l_id = LAST_INSERT_ID();
679INSERT INTO `crm_users_leads`(`lead_id`,`user_id`)
680  SELECT l_id,u_id from temp_cursor_table;
681  drop temporary table if exists temp_cursor_table;
682    SET sukses = 1;
683COMMIT;
684END$$
685
686DROP PROCEDURE IF EXISTS `sp_lead_meeting_delete`$$
687CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_lead_meeting_delete` (IN `meet_id` INT, OUT `sukses` INT)  NO SQL
688BEGIN
689 
690 
691DECLARE exit handler for sqlexception
692  BEGIN
693      GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
694 @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
695SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
696SELECT @full_error;
697    SET sukses=0;
698  ROLLBACK;
699END;
700 
701DECLARE exit handler for sqlwarning
702 BEGIN
703      GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
704 @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
705SET @full_error = CONCAT("Warning ", @errno, " (", @sqlstate, "): ", @text);
706SELECT @full_error;
707    SET sukses=0;
708 ROLLBACK;
709END;
710 
711START TRANSACTION;
712    DELETE FROM lead_meeting_users WHERE lead_meeting_id = meet_id;
713    DELETE FROM lead_meeting WHERE lead_meeting_id = meet_id;
714    SET sukses = 1;
715COMMIT;
716END$$
717
718DROP PROCEDURE IF EXISTS `sp_lead_meeting_insert`$$
719CREATE 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
720BEGIN  
721 
722DECLARE exit handler for sqlexception
723  BEGIN
724      GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
725 @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
726SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
727SELECT @full_error;
728    SET sukses=0;
729  ROLLBACK;
730END;
731 
732DECLARE exit handler for sqlwarning
733 BEGIN
734      GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
735 @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
736SET @full_error = CONCAT("Warning ", @errno, " (", @sqlstate, "): ", @text);
737SELECT @full_error;
738    SET sukses=0;
739 ROLLBACK;
740END;
741    drop temporary table if exists temp_cursor_table;
742    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 ,') )');
743  prepare pst from @temp;
744  execute pst;
745  drop prepare pst;
746INSERT 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);
747  SET @meet_id = LAST_INSERT_ID();
748INSERT INTO `lead_meeting_users`(`lead_meeting_id`,`lead_user_id`)
749  SELECT @meet_id,user_id from temp_cursor_table;
750  drop temporary table if exists temp_cursor_table;
751  set sukses=1;
752COMMIT;
753END$$
754
755DROP PROCEDURE IF EXISTS `sp_lead_meeting_update`$$
756CREATE 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
757BEGIN  
758 
759DECLARE exit handler for sqlexception
760  BEGIN
761      GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
762 @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
763SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
764SELECT @full_error;
765    SET sukses=0;
766  ROLLBACK;
767END;
768 
769DECLARE exit handler for sqlwarning
770 BEGIN
771      GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
772 @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
773SET @full_error = CONCAT("Warning ", @errno, " (", @sqlstate, "): ", @text);
774SELECT @full_error;
775    SET sukses=0;
776 ROLLBACK;
777END;
778    drop temporary table if exists temp_cursor_table;
779    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 ,') )');
780  prepare pst from @temp;
781  execute pst;
782  drop prepare pst;
783UPDATE `lead_meeting` SET
784`lead_meeting_name`=name,
785`lead_meeting_location`=location,
786`lead_meeting_start_date`=start_date,
787`lead_meeting_end_date`=end_date,
788`lead_meeting_description`=description,
789`lead_meeting_date`=date,
790`lead_meeting_user`=user,
791`lead_id`=l_id
792WHERE `lead_meeting_id`=meet_id;
793delete from lead_meeting_users where lead_meeting_id=meet_id;
794REPLACE INTO `lead_meeting_users`(`lead_meeting_id`,`lead_user_id`)
795  SELECT meet_id,user_id from temp_cursor_table;
796  drop temporary table if exists temp_cursor_table;
797set sukses=1;
798COMMIT;
799END$$
800
801DROP PROCEDURE IF EXISTS `sp_lead_update`$$
802CREATE 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
803BEGIN
804 
805DECLARE exit handler for sqlexception
806  BEGIN
807      GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
808 @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
809SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
810SELECT @full_error;
811    SET sukses=0;
812  ROLLBACK;
813END;
814 
815DECLARE exit handler for sqlwarning
816 BEGIN
817      GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
818 @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
819SET @full_error = CONCAT("Warning ", @errno, " (", @sqlstate, "): ", @text);
820SELECT @full_error;
821    SET sukses=0;
822 ROLLBACK;
823END;
824    drop temporary table if exists temp_cursor_table;
825    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 ,') )');
826  prepare pst from @temp;
827  execute pst;
828  drop prepare pst;
829  UPDATE `crm_peoples` a JOIN crm_leads b ON a.people_id=b.people_id
830SET
831a.people_salutation=salutation,
832a.people_first_name=first_name,
833a.people_last_name=last_name,
834a.people_job_title=job_title,
835a.people_company=company,
836a.people_department=department,
837a.people_email=email,
838a.people_office_phone=office_phone,
839a.people_mobile_phone=mobile_phone,
840a.people_industry=industry,
841a.people_address1=address1,
842a.people_address2=address2,
843a.people_city=city,
844a.people_state=state,
845a.people_zip_code=zip_code,
846a.people_country=country,
847a.people_source=sources,
848a.people_photo=photo,
849a.people_status = status_p,
850b.lead_label=label,
851b.lead_right_permission=right_permission,
852b.lead_groups=groups,
853b.lead_status=status_l,
854b.user_modified=u_modified,
855b.user_modified_date=date_modified
856WHERE b.lead_id = l_id;
857delete from crm_users_leads where lead_id = l_id;
858REPLACE INTO `crm_users_leads`(`lead_id`,`user_id`)
859  SELECT l_id,u_id from temp_cursor_table;
860  drop temporary table if exists temp_cursor_table;
861    SET sukses = 1;
862COMMIT;
863END$$
864
865DROP PROCEDURE IF EXISTS `sp_opportunity_insert`$$
866CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_opportunity_insert` (IN `code` VARCHAR(100), IN `name` VARCHAR(45), IN `stage_id` INT, IN `comp_id` INT, IN `amount` DOUBLE, IN `expected_closing_date` DATETIME, IN `service` ENUM('1','2'), IN `campaign_id` INT, IN `product_list` VARCHAR(300), IN `contact_list` VARCHAR(300), IN `user_id` INT, OUT `sukses` INT)  NO SQL
867BEGIN
868 
869DECLARE exit handler for sqlexception
870  BEGIN
871  GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
872 @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
873SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
874SELECT @full_error;
875        SET sukses=0;
876  ROLLBACK;
877END;
878 
879DECLARE exit handler for sqlwarning
880 BEGIN
881  GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
882 @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
883SET @full_error = CONCAT("Warning ", @errno, " (", @sqlstate, "): ", @text);
884SELECT @full_error;
885        SET sukses=0;
886 ROLLBACK;
887END;
888    drop temporary table if exists temp_cursor_table;
889    CREATE TEMPORARY TABLE IF NOT EXISTS temp_cursor_table (A Int, B Double);
890    set @temp = concat('INSERT INTO temp_cursor_table (A,B) VALUES ', product_list);
891    
892  prepare pst from @temp;
893  execute pst;
894  drop prepare pst;
895    drop temporary table if exists temp_contact;
896    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 ,') )');
897  prepare pst from @temp1;
898  execute pst;
899  drop prepare pst;
900 
901  INSERT INTO `crm_opportunities`(`opportunity_code`, `opportunity_name`, `opportunity_stage_id`, `company_id`, `opportunity_amount`, `opportunity_expected_closing_date`, `opportunity_service`, `opportunity_campaign_id`) VALUES (code, name, stage_id, comp_id, amount, expected_closing_date, service, campaign_id);
902SET @o_id = LAST_INSERT_ID();
903INSERT INTO crm_opportunities_contacts(opportunity_id,contact_id)
904    SELECT @o_id,c_id from temp_contact;
905INSERT INTO `crm_opportunities_products`(`opportunity_id`, `product_id`, `price`)
906        SELECT @o_id, A, B from temp_cursor_table;
907        drop temporary table if exists temp_cursor_table;
908  SELECT @o_id,p_id from temp_cursor_table;
909INSERT INTO `crm_users_opportunities`(`user_id`,`opportunity_id`) 	VALUES (user_id, @o_id);
910  drop temporary table if exists temp_cursor_table;
911  drop temporary table if exists temp_contact;
912    SET sukses = 1;
913COMMIT;
914END$$
915
916DROP PROCEDURE IF EXISTS `sp_opportunity_meeting_delete`$$
917CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_opportunity_meeting_delete` (IN `meet_id` INT, OUT `sukses` INT)  NO SQL
918BEGIN
919 
920 
921DECLARE exit handler for sqlexception
922  BEGIN
923      GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
924 @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
925SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
926SELECT @full_error;
927    SET sukses=0;
928  ROLLBACK;
929END;
930 
931DECLARE exit handler for sqlwarning
932 BEGIN
933      GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
934 @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
935SET @full_error = CONCAT("Warning ", @errno, " (", @sqlstate, "): ", @text);
936SELECT @full_error;
937    SET sukses=0;
938 ROLLBACK;
939END;
940 
941START TRANSACTION;
942    DELETE FROM opportunity_meeting_users WHERE opportunity_meeting_id = meet_id;
943    DELETE FROM opportunity_meeting WHERE opportunity_meeting_id = meet_id;
944    SET sukses = 1;
945COMMIT;
946END$$
947
948DROP PROCEDURE IF EXISTS `sp_opportunity_meeting_insert`$$
949CREATE 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
950BEGIN  
951 
952DECLARE exit handler for sqlexception
953  BEGIN
954      GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
955 @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
956SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
957SELECT @full_error;
958    SET sukses=0;
959  ROLLBACK;
960END;
961 
962DECLARE exit handler for sqlwarning
963 BEGIN
964      GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
965 @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
966SET @full_error = CONCAT("Warning ", @errno, " (", @sqlstate, "): ", @text);
967SELECT @full_error;
968    SET sukses=0;
969 ROLLBACK;
970END;
971    drop temporary table if exists temp_cursor_table;
972    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 ,') )');
973  prepare pst from @temp;
974  execute pst;
975  drop prepare pst;
976INSERT 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);
977  SET @meet_id = LAST_INSERT_ID();
978INSERT INTO `opportunity_meeting_users`(`opportunity_meeting_id`,`opportunity_user_id`)
979  SELECT @meet_id,user_id from temp_cursor_table;
980  drop temporary table if exists temp_cursor_table;
981  set sukses=1;
982COMMIT;
983END$$
984
985DROP PROCEDURE IF EXISTS `sp_opportunity_meeting_update`$$
986CREATE 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
987BEGIN  
988 
989DECLARE exit handler for sqlexception
990  BEGIN
991      GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
992 @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
993SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
994SELECT @full_error;
995    SET sukses=0;
996  ROLLBACK;
997END;
998 
999DECLARE exit handler for sqlwarning
1000 BEGIN
1001      GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
1002 @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
1003SET @full_error = CONCAT("Warning ", @errno, " (", @sqlstate, "): ", @text);
1004SELECT @full_error;
1005    SET sukses=0;
1006 ROLLBACK;
1007END;
1008    drop temporary table if exists temp_cursor_table;
1009    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 ,') )');
1010  prepare pst from @temp;
1011  execute pst;
1012  drop prepare pst;
1013UPDATE `opportunity_meeting` SET
1014`opportunity_meeting_name`=name,
1015`opportunity_meeting_location`=location,
1016`opportunity_meeting_start_date`=start_date,
1017`opportunity_meeting_end_date`=end_date,
1018`opportunity_meeting_description`=description,
1019`opportunity_meeting_date`=date,
1020`opportunity_meeting_user`=user,
1021`opportunity_id`=o_id
1022WHERE `opportunity_meeting_id`=meet_id;
1023delete from opportunity_meeting_users where opportunity_meeting_id=meet_id;
1024REPLACE INTO `opportunity_meeting_users`(`opportunity_meeting_id`,`opportunity_user_id`)
1025  SELECT meet_id,user_id from temp_cursor_table;
1026  drop temporary table if exists temp_cursor_table;
1027set sukses=1;
1028COMMIT;
1029END$$
1030
1031DROP PROCEDURE IF EXISTS `sp_opportunity_update`$$
1032CREATE 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 `expected_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
1033BEGIN
1034 
1035DECLARE exit handler for sqlexception
1036  BEGIN
1037  GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
1038 @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
1039SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
1040SELECT @full_error;
1041        SET sukses=0;
1042  ROLLBACK;
1043END;
1044 
1045DECLARE exit handler for sqlwarning
1046 BEGIN
1047  GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
1048 @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
1049SET @full_error = CONCAT("Warning ", @errno, " (", @sqlstate, "): ", @text);
1050SELECT @full_error;
1051        SET sukses=0;
1052 ROLLBACK;
1053END;
1054    drop temporary table if exists temp_cursor_table;
1055        CREATE TEMPORARY TABLE IF NOT EXISTS temp_cursor_table (A Int, B Double);
1056            set @temp = concat('INSERT INTO temp_cursor_table (A,B) VALUES ', product_list);
1057  prepare pst from @temp;
1058  execute pst;
1059  drop prepare pst;
1060    drop temporary table if exists temp_contact;
1061    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 ,') )');
1062  prepare pst from @temp1;
1063  execute pst;
1064  drop prepare pst;
1065UPDATE `crm_opportunities` SET
1066`opportunity_name`=name,
1067`opportunity_stage_id`=stage_id,
1068`company_id`=comp_id,
1069`opportunity_amount`=amount,
1070`opportunity_expected_closing_date`=expected_closing_date,
1071`opportunity_service`=service,
1072`opportunity_campaign_id`=campaign_id
1073WHERE `opportunity_id`=o_id;
1074delete from crm_opportunities_products where opportunity_id = o_id;
1075
1076REPLACE INTO `crm_opportunities_products`(`opportunity_id`, `product_id`,`pice`)
1077  SELECT o_id, A, B from temp_cursor_table;
1078REPLACE INTO crm_opportunities_contacts(opportunity_id,contact_id)
1079    SELECT o_id,c_id from temp_contact;
1080  drop temporary table if exists temp_cursor_table;
1081  drop temporary table if exists temp_contact;
1082    SET sukses = 1;
1083COMMIT;
1084END$$
1085
1086DROP PROCEDURE IF EXISTS `sp_recuring_invoice`$$
1087CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_recuring_invoice` ()  BEGIN
1088 
1089 DECLARE v_finished INTEGER DEFAULT 0;
1090        DECLARE inv_id INTEGER DEFAULT 0;
1091        DECLARE inv_repeat_value INTEGER DEFAULT 0;
1092        DECLARE inv_repeat_type VARCHAR(5) DEFAULT "";
1093        DECLARE next_date DATE;
1094        DECLARE next_date_2 DATE;
1095        DECLARE inv_disc_type VARCHAR(2) DEFAULT "";
1096        DECLARE inv_disc_value DOUBLE DEFAULT 0;
1097        DECLARE inv_tax_percentage INTEGER DEFAULT 0;
1098        DECLARE inv_adj DOUBLE DEFAULT 0;
1099        DECLARE inv_type VARCHAR(2) DEFAULT "";
1100        DECLARE inv_cycle INTEGER DEFAULT 0;
1101        DECLARE inv_code VARCHAR(20);
1102 
1103  DEClARE invoice_cursor CURSOR FOR 
1104 SELECT
1105  a.invoice_id,
1106  a.next_invoice_date,
1107  b.invoice_repeat_value,
1108  b.invoice_repeat_type,
1109  c.invoice_discount_type,
1110  c.invoice_discount_value,
1111  c.invoice_tax_percentage,
1112  c.invoice_adjustment,
1113  c.invoice_type,
1114  c.invoice_cycle
1115 FROM
1116  invoice_control a
1117 LEFT JOIN
1118  crm_invoices b ON a.invoice_id = b.invoice_id
1119 LEFT JOIN
1120  crm_invoice_details c ON b.invoice_id = c.invoice_id
1121 WHERE
1122  a.next_invoice_date = CURDATE() AND (b.invoice_end_date > NOW() OR b.invoice_expired = '1') AND b.invoice_type = '2';
1123 
1124  DECLARE CONTINUE HANDLER 
1125        FOR NOT FOUND SET v_finished = 1;
1126 
1127 OPEN invoice_cursor;
1128 
1129 get_invoice: LOOP
1130 
1131 FETCH invoice_cursor INTO inv_id, next_date, inv_repeat_value, inv_repeat_type, inv_disc_type, inv_disc_value, inv_tax_percentage, inv_adj, inv_type, inv_cycle;
1132 SET @invoice_id = inv_id;
1133 SET @new_cycle = inv_cycle + 1;
1134 SET @inv_detail_id = (SELECT invoice_detail_id FROM crm_invoice_details WHERE invoice_id = @invoice_id ORDER BY invoice_detail_id DESC LIMIT 1);
1135 
1136 
1137 IF v_finished = 1 THEN 
1138 LEAVE get_invoice;
1139 END IF;
1140 
1141 SET inv_code = make_inv_code();
1142  
1143 IF inv_repeat_type = '1' THEN
1144 	SET next_date_2 = TIMESTAMPADD(DAY, inv_repeat_value, next_date);
1145 ELSEIF inv_repeat_type = '2' THEN
1146 	SET next_date_2 = TIMESTAMPADD(WEEK, inv_repeat_value, next_date);
1147 ELSE 
1148 	SET next_date_2 = TIMESTAMPADD(MONTH, inv_repeat_value, next_date);
1149 END IF;
1150 
1151INSERT INTO crm_invoice_details(`invoice_code`,`invoice_id`,`invoice_date`,`invoice_due_date`,`invoice_discount_type`,`invoice_discount_value`,`invoice_tax_percentage`,`invoice_adjustment`,`invoice_type`,`invoice_cycle`) VALUES (inv_code, inv_id, NOW(), null, inv_disc_type, inv_disc_value, inv_tax_percentage, inv_adj, inv_type, @new_cycle); 
1152
1153SET @l_id = LAST_INSERT_ID();
1154
1155INSERT INTO crm_invoices_products(`invoice_detail_id`,`product_id`,`qty`, `price`, `is_campaign`) SELECT @l_id, product_id, qty, price, is_campaign FROM crm_invoices_products WHERE invoice_detail_id = @inv_detail_id;
1156 
1157 UPDATE invoice_control SET next_invoice_date = next_date_2 WHERE invoice_id = @invoice_id;
1158 
1159 END LOOP get_invoice;
1160 
1161 CLOSE invoice_cursor;
1162END$$
1163
1164--
1165-- Functions
1166--
1167DROP FUNCTION IF EXISTS `make_inv_code`$$
1168CREATE DEFINER=`root`@`localhost` FUNCTION `make_inv_code` () RETURNS VARCHAR(20) CHARSET latin1 BEGIN
1169SET
1170  @date_param =(
1171  SELECT DATE_FORMAT
1172    (CURDATE(),
1173    "%m%y")) ;
1174  SET
1175    @inv_code =(
1176    SELECT
1177      invoice_code
1178    FROM
1179      crm_invoice_details
1180    WHERE
1181      invoice_code LIKE CONCAT('%',
1182      @date_param,
1183      '%')
1184    ORDER BY
1185      invoice_detail_id DESC
1186    LIMIT 1
1187  ) ; IF @inv_code IS NULL THEN
1188SET
1189  @new_inv_code = CONCAT('INV-',
1190  @date_param,
1191  '00001') ; ELSE
1192SET
1193  @new_numb_code =(
1194  SELECT SUBSTRING_INDEX
1195    (@inv_code,
1196    '-',
1197    -1)
1198) ;
1199SET
1200  @new_code = @new_numb_code + 1 ;
1201SET
1202  @sub_code = SUBSTRING(@new_code,
1203  -5) ;
1204SET
1205  @new_inv_code = CONCAT('INV-',
1206  @date_param,
1207  @sub_code) ;
1208END IF ;
1209RETURN
1210  @new_inv_code ;
1211END$$
1212
1213DELIMITER ;
1214
1215-- --------------------------------------------------------
1216
1217--
1218-- Table structure for table `coba`
1219--
1220
1221DROP TABLE IF EXISTS `coba`;
1222CREATE TABLE `coba` (
1223  `id` int(11) NOT NULL,
1224  `code` varchar(110) NOT NULL,
1225  `date` date NOT NULL
1226) ENGINE=InnoDB DEFAULT CHARSET=latin1;
1227
1228--
1229-- Dumping data for table `coba`
1230--
1231
1232INSERT INTO `coba` (`id`, `code`, `date`) VALUES
1233(1, 'INV-091700001', '2017-09-08'),
1234(2, 'INV-091700001', '2017-09-14');
1235
1236-- --------------------------------------------------------
1237
1238--
1239-- Table structure for table `company_calls`
1240--
1241
1242DROP TABLE IF EXISTS `company_calls`;
1243CREATE TABLE `company_calls` (
1244  `company_call_id` int(11) NOT NULL,
1245  `company_call_title` varchar(100) DEFAULT NULL,
1246  `company_call_duration` int(11) DEFAULT NULL,
1247  `company_call_time` enum('1','2','3') DEFAULT '1' COMMENT '1= Second\n2= Minutes\n3=Hour\n',
1248  `company_call_notes` text,
1249  `company_call_date` datetime NOT NULL,
1250  `company_call_user` varchar(100) NOT NULL,
1251  `company_id` int(11) NOT NULL
1252) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1253
1254-- --------------------------------------------------------
1255
1256--
1257-- Table structure for table `company_files`
1258--
1259
1260DROP TABLE IF EXISTS `company_files`;
1261CREATE TABLE `company_files` (
1262  `company_files_id` int(11) NOT NULL,
1263  `company_file_subject` varchar(100) DEFAULT NULL,
1264  `company_file_to` varchar(100) NOT NULL,
1265  `company_file_cc` varchar(100) DEFAULT NULL,
1266  `company_file_description` text,
1267  `company_file_name` varchar(100) NOT NULL,
1268  `company_file_date` datetime NOT NULL,
1269  `company_file_user` varchar(100) NOT NULL,
1270  `company_id` int(11) NOT NULL
1271) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1272
1273-- --------------------------------------------------------
1274
1275--
1276-- Table structure for table `company_meeting`
1277--
1278
1279DROP TABLE IF EXISTS `company_meeting`;
1280CREATE TABLE `company_meeting` (
1281  `company_meeting_id` int(11) NOT NULL,
1282  `company_meeting_name` varchar(100) DEFAULT NULL,
1283  `company_meeting_location` varchar(255) DEFAULT NULL,
1284  `company_meeting_start_date` datetime DEFAULT NULL,
1285  `company_meeting_end_date` datetime DEFAULT NULL,
1286  `company_meeting_description` text,
1287  `company_meeting_date` datetime NOT NULL,
1288  `company_meeting_user` varchar(100) NOT NULL,
1289  `company_id` int(11) NOT NULL
1290) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1291
1292-- --------------------------------------------------------
1293
1294--
1295-- Table structure for table `company_meeting_users`
1296--
1297
1298DROP TABLE IF EXISTS `company_meeting_users`;
1299CREATE TABLE `company_meeting_users` (
1300  `company_meeting_id` int(11) NOT NULL,
1301  `company_user_id` int(11) NOT NULL
1302) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1303
1304-- --------------------------------------------------------
1305
1306--
1307-- Table structure for table `company_notes`
1308--
1309
1310DROP TABLE IF EXISTS `company_notes`;
1311CREATE TABLE `company_notes` (
1312  `company_note_id` int(11) NOT NULL,
1313  `company_note_name` varchar(100) NOT NULL,
1314  `company_note_description` text,
1315  `company_note_date` datetime DEFAULT NULL,
1316  `company_note_user` varchar(100) DEFAULT NULL,
1317  `company_id` int(11) NOT NULL
1318) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1319
1320-- --------------------------------------------------------
1321
1322--
1323-- Table structure for table `company_tasks`
1324--
1325
1326DROP TABLE IF EXISTS `company_tasks`;
1327CREATE TABLE `company_tasks` (
1328  `company_task_id` int(11) NOT NULL,
1329  `company_task_name` varchar(100) NOT NULL,
1330  `company_task_description` varchar(45) DEFAULT NULL,
1331  `company_task_status` int(11) DEFAULT NULL,
1332  `company_task_due_date` datetime DEFAULT NULL,
1333  `company_task_date` datetime NOT NULL,
1334  `company_task_user` varchar(100) NOT NULL,
1335  `company_id` int(11) NOT NULL
1336) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1337
1338-- --------------------------------------------------------
1339
1340--
1341-- Table structure for table `contact_calls`
1342--
1343
1344DROP TABLE IF EXISTS `contact_calls`;
1345CREATE TABLE `contact_calls` (
1346  `contact_call_id` int(11) NOT NULL,
1347  `contact_call_title` varchar(100) DEFAULT NULL,
1348  `contact_call_duration` int(11) DEFAULT NULL,
1349  `contact_call_time` enum('1','2','3') DEFAULT '1' COMMENT '1= Second\n2= Minutes\n3=Hour\n',
1350  `contact_call_notes` text,
1351  `contact_call_date` datetime NOT NULL,
1352  `contact_call_user` varchar(100) NOT NULL,
1353  `contact_id` int(11) NOT NULL
1354) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1355
1356-- --------------------------------------------------------
1357
1358--
1359-- Table structure for table `contact_files`
1360--
1361
1362DROP TABLE IF EXISTS `contact_files`;
1363CREATE TABLE `contact_files` (
1364  `contact_files_id` int(11) NOT NULL,
1365  `contact_file_subject` varchar(100) DEFAULT NULL,
1366  `contact_file_to` varchar(100) NOT NULL,
1367  `contact_file_cc` varchar(100) DEFAULT NULL,
1368  `contact_file_description` text,
1369  `contact_file_name` varchar(100) NOT NULL,
1370  `contact_file_date` datetime NOT NULL,
1371  `contact_file_user` varchar(100) NOT NULL,
1372  `contact_id` int(11) NOT NULL
1373) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1374
1375-- --------------------------------------------------------
1376
1377--
1378-- Table structure for table `contact_meeting`
1379--
1380
1381DROP TABLE IF EXISTS `contact_meeting`;
1382CREATE TABLE `contact_meeting` (
1383  `contact_meeting_id` int(11) NOT NULL,
1384  `contact_meeting_name` varchar(100) DEFAULT NULL,
1385  `contact_meeting_location` varchar(255) DEFAULT NULL,
1386  `contact_meeting_start_date` datetime DEFAULT NULL,
1387  `contact_meeting_end_date` datetime DEFAULT NULL,
1388  `contact_meeting_description` text,
1389  `contact_meeting_date` datetime NOT NULL,
1390  `contact_meeting_user` varchar(100) NOT NULL,
1391  `contact_id` int(11) NOT NULL
1392) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1393
1394-- --------------------------------------------------------
1395
1396--
1397-- Table structure for table `contact_meeting_users`
1398--
1399
1400DROP TABLE IF EXISTS `contact_meeting_users`;
1401CREATE TABLE `contact_meeting_users` (
1402  `contact_meeting_id` int(11) NOT NULL,
1403  `contact_user_id` int(11) NOT NULL
1404) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1405
1406-- --------------------------------------------------------
1407
1408--
1409-- Table structure for table `contact_notes`
1410--
1411
1412DROP TABLE IF EXISTS `contact_notes`;
1413CREATE TABLE `contact_notes` (
1414  `contact_note_id` int(11) NOT NULL,
1415  `contact_note_name` varchar(100) NOT NULL,
1416  `contact_note_description` text,
1417  `contact_note_date` datetime DEFAULT NULL,
1418  `contact_note_user` varchar(100) DEFAULT NULL,
1419  `contact_id` int(11) NOT NULL
1420) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1421
1422-- --------------------------------------------------------
1423
1424--
1425-- Table structure for table `contact_tasks`
1426--
1427
1428DROP TABLE IF EXISTS `contact_tasks`;
1429CREATE TABLE `contact_tasks` (
1430  `contact_task_id` int(11) NOT NULL,
1431  `contact_task_name` varchar(100) NOT NULL,
1432  `contact_task_description` varchar(45) DEFAULT NULL,
1433  `contact_task_status` int(11) DEFAULT NULL,
1434  `contact_task_due_date` datetime DEFAULT NULL,
1435  `contact_task_date` datetime NOT NULL,
1436  `contact_task_user` varchar(100) NOT NULL,
1437  `contact_id` int(11) NOT NULL
1438) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1439
1440-- --------------------------------------------------------
1441
1442--
1443-- Table structure for table `crm_campaigns`
1444--
1445
1446DROP TABLE IF EXISTS `crm_campaigns`;
1447CREATE TABLE `crm_campaigns` (
1448  `campaign_id` int(11) NOT NULL,
1449  `campaign_name` varchar(100) NOT NULL,
1450  `campaign_description` text,
1451  `campaign_start_date` date DEFAULT NULL,
1452  `campaign_end_date` date DEFAULT NULL,
1453  `campaign_status` enum('0','1') DEFAULT '0' COMMENT '0=Non aktif\n1= Aktif\n\n',
1454  `campaign_number` int(11) DEFAULT NULL,
1455  `campaign_percentage_response` double DEFAULT NULL,
1456  `campaign_budget_cost_currency` enum('1','2') DEFAULT NULL COMMENT '1= USD\n2 = IDR ',
1457  `campaign_budget_cost` double DEFAULT NULL,
1458  `campaign_actual_cost_currency` enum('1','2') DEFAULT NULL COMMENT '1= USD\n2 = IDR ',
1459  `campaign_actual_cost` double DEFAULT NULL,
1460  `campaign_expected_revenue_currency` enum('1','2') DEFAULT NULL COMMENT '1= USD\n2 = IDR ',
1461  `campaign_expected_revenue` double DEFAULT NULL,
1462  `user_created` varchar(100) DEFAULT NULL,
1463  `user_created_date` datetime DEFAULT NULL,
1464  `user_modified` varchar(100) DEFAULT NULL,
1465  `user_modified_date` datetime DEFAULT NULL
1466) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1467
1468--
1469-- Dumping data for table `crm_campaigns`
1470--
1471
1472INSERT 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
1473(1, 'Promo Hari Raya', 'Diskon untuk beberapa produk', '2017-08-28', '2017-09-30', '1', 400, 100, '1', 60000000, '1', 50000000, '1', 100000000, 'parinussa', '2017-08-28 16:59:04', NULL, NULL),
1474(2, 'Promo Liburan', 'deskripsi', '2017-08-29', '2017-09-30', '1', 70, 70, '1', 50000000, '1', 40000000, '1', 70000000, 'adiputra', '2017-08-29 08:25:29', NULL, NULL),
1475(4, 'SDYOC', 'sdyoc 25 %', '2017-09-03', '2017-09-30', '1', 50, 84, '1', 70000000, '1', 80000000, '1', 84000000, 'parinussa', '2017-09-06 03:50:57', NULL, '2017-09-06 04:15:10'),
1476(5, 'Sibling', 'discount 20% for sibling', '2017-09-06', '2017-09-30', '1', 70, 50, '1', 40000000, '1', 30000000, '1', 50000000, 'parinussa', '2017-09-06 04:14:32', NULL, NULL);
1477
1478-- --------------------------------------------------------
1479
1480--
1481-- Table structure for table `crm_companies`
1482--
1483
1484DROP TABLE IF EXISTS `crm_companies`;
1485CREATE TABLE `crm_companies` (
1486  `company_id` int(11) NOT NULL,
1487  `customer_code` varchar(50) DEFAULT NULL,
1488  `company_name` varchar(200) DEFAULT NULL,
1489  `company_parent` int(11) DEFAULT NULL,
1490  `company_office_phone` varchar(30) DEFAULT NULL,
1491  `company_industry` int(11) DEFAULT NULL,
1492  `company_email` varchar(100) DEFAULT NULL,
1493  `company_fax` varchar(30) DEFAULT NULL,
1494  `company_employee` int(11) DEFAULT NULL,
1495  `company_revenue` double DEFAULT NULL,
1496  `company_website` varchar(100) DEFAULT NULL,
1497  `company_address1` varchar(255) DEFAULT NULL,
1498  `company_address2` varchar(255) DEFAULT NULL,
1499  `company_city` varchar(100) DEFAULT NULL,
1500  `company_state` varchar(100) DEFAULT NULL,
1501  `company_zip_code` varchar(10) DEFAULT NULL,
1502  `company_country` varchar(50) DEFAULT NULL,
1503  `company_billing_address1` varchar(255) DEFAULT NULL,
1504  `company_billing_address2` varchar(255) DEFAULT NULL,
1505  `company_billing_city` varchar(100) DEFAULT NULL,
1506  `company_billing_state` varchar(100) DEFAULT NULL,
1507  `company_billing_zip_code` varchar(10) DEFAULT NULL,
1508  `company_billing_country` varchar(50) DEFAULT NULL,
1509  `company_right_permission` enum('1','2','3') DEFAULT '1' COMMENT '1=owner2=group23=everyone',
1510  `user_created` varchar(100) DEFAULT NULL,
1511  `user_created_date` datetime DEFAULT NULL,
1512  `user_modified` varchar(100) DEFAULT NULL,
1513  `user_modified_date` datetime DEFAULT NULL,
1514  `company_groups` int(11) DEFAULT NULL,
1515  `company_customer` enum('0','1') DEFAULT '0' COMMENT '0=Not Customer\n1=Customer\n'
1516) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1517
1518--
1519-- Dumping data for table `crm_companies`
1520--
1521
1522INSERT 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
1523(9, 'CST-091700002', 'PT. MLM Maju Bersama', NULL, '0812686763', 4, 'majubersama@yahoo.com', '0826673672', 43, 50000000, 'majubersama.com', 'Jl. Puyuh Raya', 'Kelapa Gading', 'Jakarta Utara', 'DKI Jakarta', '51183', 'Indonesia', 'Jl. Puyuh Raya', 'Kelapa Gading', 'Jakarta Utara', 'DKI Jakarta', '51183', 'Indonesia', '1', NULL, NULL, 'parinussa', '2017-09-19 10:44:17', NULL, '1'),
1524(10, 'CST-091700003', 'Bangsa Cerdas', NULL, NULL, 4, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '1', NULL, NULL, NULL, NULL, NULL, '0'),
1525(11, NULL, 'Republic Of Koders', NULL, NULL, 4, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '1', NULL, NULL, NULL, NULL, NULL, '0'),
1526(12, NULL, 'asdas', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '1', NULL, NULL, NULL, NULL, NULL, '0'),
1527(13, 'CST-091700004', 'asdasd', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '1', NULL, NULL, NULL, NULL, NULL, '0'),
1528(14, NULL, 'qweqweqweqweqw', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '1', NULL, NULL, NULL, NULL, NULL, '0');
1529
1530-- --------------------------------------------------------
1531
1532--
1533-- Table structure for table `crm_companies_products`
1534--
1535
1536DROP TABLE IF EXISTS `crm_companies_products`;
1537CREATE TABLE `crm_companies_products` (
1538  `company_id` int(11) NOT NULL,
1539  `product_id` int(11) NOT NULL,
1540  `product_price` double NOT NULL,
1541  `status` enum('0','1') NOT NULL
1542) ENGINE=InnoDB DEFAULT CHARSET=latin1;
1543
1544--
1545-- Dumping data for table `crm_companies_products`
1546--
1547
1548INSERT INTO `crm_companies_products` (`company_id`, `product_id`, `product_price`, `status`) VALUES
1549(9, 8, 3000000, '1'),
1550(9, 10, 20000000, '1'),
1551(9, 12, 8000000, '1'),
1552(10, 6, 4000000, '1'),
1553(10, 9, 2000000, '1'),
1554(13, 8, 3000000, '1'),
1555(13, 10, 20000000, '1');
1556
1557-- --------------------------------------------------------
1558
1559--
1560-- Table structure for table `crm_contacts`
1561--
1562
1563DROP TABLE IF EXISTS `crm_contacts`;
1564CREATE TABLE `crm_contacts` (
1565  `contact_id` int(11) NOT NULL,
1566  `company_id` int(11) NOT NULL,
1567  `people_id` int(11) NOT NULL,
1568  `contact_right_permission` enum('1','2','3') DEFAULT NULL,
1569  `contact_groups` int(11) DEFAULT NULL
1570) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1571
1572--
1573-- Dumping data for table `crm_contacts`
1574--
1575
1576INSERT INTO `crm_contacts` (`contact_id`, `company_id`, `people_id`, `contact_right_permission`, `contact_groups`) VALUES
1577(13, 9, 18, '1', NULL),
1578(14, 9, 17, '1', NULL),
1579(15, 9, 20, '1', NULL),
1580(16, 10, 19, '1', NULL),
1581(17, 11, 21, '1', NULL),
1582(18, 12, 26, '1', NULL),
1583(19, 13, 27, '1', NULL),
1584(20, 14, 28, '1', NULL);
1585
1586-- --------------------------------------------------------
1587
1588--
1589-- Table structure for table `crm_employee`
1590--
1591
1592DROP TABLE IF EXISTS `crm_employee`;
1593CREATE TABLE `crm_employee` (
1594  `employee_id` int(11) NOT NULL,
1595  `employee_name` varchar(255) NOT NULL,
1596  `employee_manager_id` int(11) NOT NULL,
1597  `employee_mobile_phone` varchar(255) NOT NULL,
1598  `employee_email` varchar(255) NOT NULL,
1599  `employee_address` text NOT NULL
1600) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1601
1602--
1603-- Dumping data for table `crm_employee`
1604--
1605
1606INSERT INTO `crm_employee` (`employee_id`, `employee_name`, `employee_manager_id`, `employee_mobile_phone`, `employee_email`, `employee_address`) VALUES
1607(3, 'Surya', 18, '085386887878', 'employee@yahoo.com', 'jakarta utara'),
1608(4, 'Helga Farida', 20, 'dasdas', 'dasaccccsd@adsd.asd', 'asdasdas');
1609
1610-- --------------------------------------------------------
1611
1612--
1613-- Table structure for table `crm_faq`
1614--
1615
1616DROP TABLE IF EXISTS `crm_faq`;
1617CREATE TABLE `crm_faq` (
1618  `faq_id` int(11) NOT NULL,
1619  `faq_title` varchar(255) NOT NULL,
1620  `faq_description` text NOT NULL,
1621  `faq_file` varchar(255) DEFAULT NULL,
1622  `faq_tag` varchar(255) DEFAULT NULL
1623) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1624
1625-- --------------------------------------------------------
1626
1627--
1628-- Table structure for table `crm_groups`
1629--
1630
1631DROP TABLE IF EXISTS `crm_groups`;
1632CREATE TABLE `crm_groups` (
1633  `group_id` int(11) NOT NULL,
1634  `group_name` varchar(100) NOT NULL,
1635  `group_status` enum('0','1') DEFAULT '1' COMMENT '0= Non Aktif\n1= Aktif\n',
1636  `group_manager_id` int(11) DEFAULT NULL,
1637  `user_created` varchar(100) DEFAULT NULL,
1638  `user_created_date` datetime DEFAULT NULL,
1639  `user_modified` varchar(45) DEFAULT NULL,
1640  `user_modified_date` datetime DEFAULT NULL
1641) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1642
1643--
1644-- Dumping data for table `crm_groups`
1645--
1646
1647INSERT INTO `crm_groups` (`group_id`, `group_name`, `group_status`, `group_manager_id`, `user_created`, `user_created_date`, `user_modified`, `user_modified_date`) VALUES
1648(3, 'Marketing', '1', 18, 'admin', '2017-08-23 00:00:00', 'muhaliusman', '2017-09-26 07:19:22'),
1649(4, 'Developer', '1', NULL, 'admin', '2017-08-23 00:00:00', NULL, NULL),
1650(5, 'Designer', '1', NULL, 'admin', '2017-08-23 00:00:00', NULL, NULL),
1651(6, 'Managemen', '1', NULL, 'muhaliusman', '2017-08-23 16:33:30', NULL, NULL),
1652(7, 'Sales Canvas', '1', NULL, 'muhaliusman', '2017-08-29 07:25:34', NULL, NULL),
1653(9, 'ini group baru', '1', 19, 'muhaliusman', '2017-09-27 09:22:38', 'muhaliusman', '2017-09-27 09:26:38');
1654
1655-- --------------------------------------------------------
1656
1657--
1658-- Table structure for table `crm_industry`
1659--
1660
1661DROP TABLE IF EXISTS `crm_industry`;
1662CREATE TABLE `crm_industry` (
1663  `industry_id` int(11) NOT NULL,
1664  `industry_name` varchar(100) NOT NULL,
1665  `user_created` varchar(100) DEFAULT NULL,
1666  `user_created_date` datetime DEFAULT NULL,
1667  `user_modified` varchar(100) DEFAULT NULL,
1668  `user_modified_date` datetime DEFAULT NULL
1669) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1670
1671--
1672-- Dumping data for table `crm_industry`
1673--
1674
1675INSERT INTO `crm_industry` (`industry_id`, `industry_name`, `user_created`, `user_created_date`, `user_modified`, `user_modified_date`) VALUES
1676(3, 'Manufacture', 'muhaliusman', '2017-08-25 04:06:27', NULL, '2017-09-12 06:41:45'),
1677(4, 'Consumer Good', 'muhaliusman', '2017-08-25 04:06:36', NULL, NULL),
1678(5, 'Development Center', 'muhaliusman', '2017-08-25 04:06:46', NULL, NULL),
1679(6, 'Education', 'muhaliusman', '2017-08-25 04:06:53', NULL, NULL),
1680(7, 'Services', 'muhaliusman', '2017-08-25 04:07:25', NULL, NULL);
1681
1682-- --------------------------------------------------------
1683
1684--
1685-- Table structure for table `crm_invoices`
1686--
1687
1688DROP TABLE IF EXISTS `crm_invoices`;
1689CREATE TABLE `crm_invoices` (
1690  `invoice_id` int(11) NOT NULL,
1691  `company_id` int(11) NOT NULL,
1692  `invoice_ref` varchar(45) DEFAULT NULL,
1693  `invoice_profile` varchar(100) DEFAULT NULL,
1694  `invoice_type` enum('1','2') NOT NULL COMMENT '1=One Time\n2=Recurring\n',
1695  `invoice_start_date` datetime DEFAULT NULL,
1696  `invoice_end_date` datetime DEFAULT NULL COMMENT '	',
1697  `invoice_repeat_value` int(11) DEFAULT NULL,
1698  `invoice_repeat_type` enum('1','2','3') DEFAULT NULL COMMENT '1 =Day\n2= week\n3=Month\n',
1699  `invoice_expired` enum('0','1') DEFAULT NULL COMMENT '0=expired\n1=never expired\n',
1700  `term_id` int(11) NOT NULL
1701) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1702
1703--
1704-- Dumping data for table `crm_invoices`
1705--
1706
1707INSERT INTO `crm_invoices` (`invoice_id`, `company_id`, `invoice_ref`, `invoice_profile`, `invoice_type`, `invoice_start_date`, `invoice_end_date`, `invoice_repeat_value`, `invoice_repeat_type`, `invoice_expired`, `term_id`) VALUES
1708(13, 9, '-', 'OPR-091700001', '1', '2017-09-19 17:46:35', NULL, NULL, '1', '1', 1),
1709(14, 9, '-', 'OPR-091700002', '1', '2017-09-19 18:44:39', NULL, NULL, '1', '1', 1);
1710
1711-- --------------------------------------------------------
1712
1713--
1714-- Table structure for table `crm_invoices_products`
1715--
1716
1717DROP TABLE IF EXISTS `crm_invoices_products`;
1718CREATE TABLE `crm_invoices_products` (
1719  `invoice_detail_id` int(11) NOT NULL,
1720  `product_id` int(11) NOT NULL,
1721  `qty` int(11) DEFAULT NULL,
1722  `price` double DEFAULT NULL,
1723  `is_campaign` enum('0','1') NOT NULL
1724) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1725
1726--
1727-- Dumping data for table `crm_invoices_products`
1728--
1729
1730INSERT INTO `crm_invoices_products` (`invoice_detail_id`, `product_id`, `qty`, `price`, `is_campaign`) VALUES
1731(15, 8, 1, 3000000, '0'),
1732(16, 10, 1, 20000000, '0'),
1733(16, 12, 1, 8000000, '0');
1734
1735-- --------------------------------------------------------
1736
1737--
1738-- Table structure for table `crm_invoice_details`
1739--
1740
1741DROP TABLE IF EXISTS `crm_invoice_details`;
1742CREATE TABLE `crm_invoice_details` (
1743  `invoice_detail_id` int(11) NOT NULL,
1744  `invoice_code` varchar(50) NOT NULL,
1745  `invoice_id` int(11) NOT NULL,
1746  `invoice_date` datetime NOT NULL,
1747  `invoice_due_date` datetime DEFAULT NULL,
1748  `invoice_discount_type` enum('1','2') DEFAULT '1' COMMENT '1=Percentage2=Value',
1749  `invoice_discount_value` double DEFAULT NULL,
1750  `invoice_tax_percentage` double DEFAULT NULL,
1751  `invoice_adjustment` double DEFAULT NULL,
1752  `invoice_cycle` int(11) DEFAULT '1',
1753  `invoice_status` enum('1','2','3','4','5') NOT NULL DEFAULT '1'
1754) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1755
1756--
1757-- Dumping data for table `crm_invoice_details`
1758--
1759
1760INSERT INTO `crm_invoice_details` (`invoice_detail_id`, `invoice_code`, `invoice_id`, `invoice_date`, `invoice_due_date`, `invoice_discount_type`, `invoice_discount_value`, `invoice_tax_percentage`, `invoice_adjustment`, `invoice_cycle`, `invoice_status`) VALUES
1761(15, 'INV-091700001', 13, '2017-09-19 10:46:35', '2017-09-26 00:00:00', '1', 0, 0, 0, 2, '4'),
1762(16, 'INV-091700002', 14, '2017-09-19 11:44:40', '2017-09-26 00:00:00', '1', 840000, 3, 40000, 1, '2');
1763
1764-- --------------------------------------------------------
1765
1766--
1767-- Table structure for table `crm_leads`
1768--
1769
1770DROP TABLE IF EXISTS `crm_leads`;
1771CREATE TABLE `crm_leads` (
1772  `lead_id` int(11) NOT NULL,
1773  `lead_label` int(11) NOT NULL,
1774  `people_id` int(11) NOT NULL,
1775  `lead_right_permission` enum('1','2','3') DEFAULT NULL COMMENT '1=owner\n2=group2\n3=everyone\n\n',
1776  `lead_groups` int(11) DEFAULT NULL,
1777  `lead_status` enum('1','2','3') NOT NULL DEFAULT '1' COMMENT '1= Lead\n2 = Convert\n3 =Junk\n',
1778  `user_created` varchar(100) DEFAULT NULL,
1779  `user_created_date` datetime DEFAULT NULL,
1780  `user_modified` varchar(100) DEFAULT NULL,
1781  `user_modified_date` datetime DEFAULT NULL
1782) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1783
1784--
1785-- Dumping data for table `crm_leads`
1786--
1787
1788INSERT 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
1789(14, 4, 17, '1', NULL, '2', 'parinussa', '2017-09-19 10:40:01', NULL, NULL),
1790(15, 4, 18, '1', NULL, '2', 'parinussa', '2017-09-19 10:40:42', NULL, NULL),
1791(16, 4, 19, '1', NULL, '2', 'parinussa', '2017-09-23 06:07:24', NULL, NULL),
1792(17, 4, 21, '1', NULL, '2', 'parinussa', '2017-09-26 09:50:27', 'parinussa', '2017-09-26 09:52:37'),
1793(18, 5, 26, '1', NULL, '2', 'parinussa', '2017-09-26 15:56:47', NULL, NULL),
1794(19, 5, 27, '1', NULL, '2', 'parinussa', '2017-09-26 16:09:38', NULL, NULL),
1795(20, 5, 28, '1', NULL, '2', 'parinussa', '2017-09-29 08:00:34', NULL, NULL);
1796
1797-- --------------------------------------------------------
1798
1799--
1800-- Table structure for table `crm_lead_label`
1801--
1802
1803DROP TABLE IF EXISTS `crm_lead_label`;
1804CREATE TABLE `crm_lead_label` (
1805  `lead_label_id` int(11) NOT NULL,
1806  `lead_label_name` varchar(100) NOT NULL,
1807  `lead_label_status_score` int(11) NOT NULL COMMENT '0-100'
1808) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1809
1810--
1811-- Dumping data for table `crm_lead_label`
1812--
1813
1814INSERT INTO `crm_lead_label` (`lead_label_id`, `lead_label_name`, `lead_label_status_score`) VALUES
1815(4, 'New', 0),
1816(5, 'Pikir-pikir', 30),
1817(6, 'Negotiating', 50),
1818(7, 'Interest', 80),
1819(8, 'Deal', 100);
1820
1821-- --------------------------------------------------------
1822
1823--
1824-- Table structure for table `crm_modules`
1825--
1826
1827DROP TABLE IF EXISTS `crm_modules`;
1828CREATE TABLE `crm_modules` (
1829  `module_id` int(11) NOT NULL,
1830  `module_name` varchar(100) NOT NULL,
1831  `module_route` varchar(225) NOT NULL,
1832  `module_order` int(11) DEFAULT NULL
1833) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1834
1835--
1836-- Dumping data for table `crm_modules`
1837--
1838
1839INSERT INTO `crm_modules` (`module_id`, `module_name`, `module_route`, `module_order`) VALUES
1840(1, 'Leads', 'leads', NULL),
1841(2, 'Contacts', 'contacts', NULL),
1842(3, 'Companies', 'companies', NULL),
1843(4, 'Opportunities', 'opportunities', NULL),
1844(5, 'Products', 'products', NULL),
1845(6, 'Campaigns', 'campaigns', NULL),
1846(8, 'invoices', 'invoices', NULL),
1847(9, 'recurring invoices', 'recurring-invoices', NULL),
1848(10, 'customer', 'customers', NULL),
1849(12, 'customer services', 'customer-services', NULL),
1850(15, 'junk lead', 'junks', NULL);
1851
1852-- --------------------------------------------------------
1853
1854--
1855-- Table structure for table `crm_news`
1856--
1857
1858DROP TABLE IF EXISTS `crm_news`;
1859CREATE TABLE `crm_news` (
1860  `news_id` int(11) NOT NULL,
1861  `news_title` varchar(150) NOT NULL,
1862  `news_description` text NOT NULL,
1863  `news_date` datetime NOT NULL
1864) ENGINE=InnoDB DEFAULT CHARSET=latin1;
1865
1866-- --------------------------------------------------------
1867
1868--
1869-- Table structure for table `crm_opportunities`
1870--
1871
1872DROP TABLE IF EXISTS `crm_opportunities`;
1873CREATE TABLE `crm_opportunities` (
1874  `opportunity_id` int(11) NOT NULL,
1875  `opportunity_code` varchar(100) DEFAULT NULL,
1876  `opportunity_name` varchar(45) DEFAULT NULL,
1877  `opportunity_stage_id` int(11) NOT NULL,
1878  `company_id` int(11) NOT NULL,
1879  `opportunity_amount` double DEFAULT NULL,
1880  `opportunity_expected_closing_date` datetime DEFAULT NULL,
1881  `opportunity_closing_date` datetime DEFAULT NULL,
1882  `opportunity_service` enum('1','2') DEFAULT NULL COMMENT '1= Product\n2=Campaign\n',
1883  `opportunity_campaign_id` int(11) DEFAULT NULL
1884) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1885
1886--
1887-- Dumping data for table `crm_opportunities`
1888--
1889
1890INSERT INTO `crm_opportunities` (`opportunity_id`, `opportunity_code`, `opportunity_name`, `opportunity_stage_id`, `company_id`, `opportunity_amount`, `opportunity_expected_closing_date`, `opportunity_closing_date`, `opportunity_service`, `opportunity_campaign_id`) VALUES
1891(9, 'OPR-091700001', 'Opportunity Pertama', 6, 9, 3000000, '2017-09-19 00:00:00', '2017-09-29 00:00:00', '1', NULL),
1892(10, 'OPR-091700002', 'Opportunity baru', 6, 9, 28000000, '2017-09-19 00:00:00', '2017-09-30 00:00:00', '1', NULL),
1893(11, 'OPR-091700003', 'asdas', 6, 10, 6000000, '2017-09-19 00:00:00', '2017-09-27 00:00:00', '1', NULL),
1894(12, 'OPR-091700004', 'dasda', 6, 13, 23, '2017-09-26 00:00:00', '2017-09-26 00:00:00', '1', NULL),
1895(13, 'OPR-091700005', 'sdadsfsafasf', 4, 14, 29, '2017-09-29 00:00:00', '2017-09-23 00:00:00', '1', NULL);
1896
1897-- --------------------------------------------------------
1898
1899--
1900-- Table structure for table `crm_opportunities_contacts`
1901--
1902
1903DROP TABLE IF EXISTS `crm_opportunities_contacts`;
1904CREATE TABLE `crm_opportunities_contacts` (
1905  `opportunity_id` int(11) NOT NULL,
1906  `contact_id` int(11) NOT NULL
1907) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1908
1909--
1910-- Dumping data for table `crm_opportunities_contacts`
1911--
1912
1913INSERT INTO `crm_opportunities_contacts` (`opportunity_id`, `contact_id`) VALUES
1914(9, 13),
1915(10, 14),
1916(11, 16),
1917(12, 19),
1918(13, 20);
1919
1920-- --------------------------------------------------------
1921
1922--
1923-- Table structure for table `crm_opportunities_products`
1924--
1925
1926DROP TABLE IF EXISTS `crm_opportunities_products`;
1927CREATE TABLE `crm_opportunities_products` (
1928  `opportunity_id` int(11) NOT NULL,
1929  `product_id` int(11) NOT NULL,
1930  `price` double DEFAULT NULL
1931) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1932
1933--
1934-- Dumping data for table `crm_opportunities_products`
1935--
1936
1937INSERT INTO `crm_opportunities_products` (`opportunity_id`, `product_id`, `price`) VALUES
1938(9, 8, NULL),
1939(10, 10, NULL),
1940(10, 12, NULL),
1941(11, 6, 3000),
1942(11, 9, 4000),
1943(12, 8, 3000000),
1944(12, 10, 20000000),
1945(13, 10, 20000000),
1946(13, 13, 9000000);
1947
1948-- --------------------------------------------------------
1949
1950--
1951-- Table structure for table `crm_opportunity_stage`
1952--
1953
1954DROP TABLE IF EXISTS `crm_opportunity_stage`;
1955CREATE TABLE `crm_opportunity_stage` (
1956  `opportunity_stage_id` int(11) NOT NULL,
1957  `opportunity_stage_name` varchar(100) DEFAULT NULL,
1958  `opportunity_stage_status_score` int(11) NOT NULL
1959) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1960
1961--
1962-- Dumping data for table `crm_opportunity_stage`
1963--
1964
1965INSERT INTO `crm_opportunity_stage` (`opportunity_stage_id`, `opportunity_stage_name`, `opportunity_stage_status_score`) VALUES
1966(4, 'Negotiating', 30),
1967(5, 'New', 0),
1968(6, 'Close Won', 100);
1969
1970-- --------------------------------------------------------
1971
1972--
1973-- Table structure for table `crm_payment`
1974--
1975
1976DROP TABLE IF EXISTS `crm_payment`;
1977CREATE TABLE `crm_payment` (
1978  `payment_id` int(11) NOT NULL,
1979  `payment_code` varchar(100) NOT NULL,
1980  `invoice_detail_id` int(11) DEFAULT NULL,
1981  `payment_amount_received` double NOT NULL,
1982  `payment_bank_charge` double DEFAULT NULL,
1983  `payment_method_id` int(11) DEFAULT NULL,
1984  `payment_date` datetime NOT NULL,
1985  `payment_ref` varchar(200) DEFAULT NULL,
1986  `payment_notes` varchar(200) DEFAULT NULL,
1987  `payment_file` varchar(200) DEFAULT NULL
1988) ENGINE=InnoDB DEFAULT CHARSET=latin1;
1989
1990--
1991-- Dumping data for table `crm_payment`
1992--
1993
1994INSERT INTO `crm_payment` (`payment_id`, `payment_code`, `invoice_detail_id`, `payment_amount_received`, `payment_bank_charge`, `payment_method_id`, `payment_date`, `payment_ref`, `payment_notes`, `payment_file`) VALUES
1995(3, 'PYM-091700001', 15, 3000000, 4000, 1, '2017-09-19 00:00:00', '-', 'pembayaran lunas', NULL);
1996
1997-- --------------------------------------------------------
1998
1999--
2000-- Table structure for table `crm_payment_methods`
2001--
2002
2003DROP TABLE IF EXISTS `crm_payment_methods`;
2004CREATE TABLE `crm_payment_methods` (
2005  `payment_method_id` int(11) NOT NULL,
2006  `payment_method_name` varchar(200) NOT NULL
2007) ENGINE=InnoDB DEFAULT CHARSET=latin1;
2008
2009--
2010-- Dumping data for table `crm_payment_methods`
2011--
2012
2013INSERT INTO `crm_payment_methods` (`payment_method_id`, `payment_method_name`) VALUES
2014(1, 'Bank Transfer'),
2015(2, 'Cash');
2016
2017-- --------------------------------------------------------
2018
2019--
2020-- Table structure for table `crm_peoples`
2021--
2022
2023DROP TABLE IF EXISTS `crm_peoples`;
2024CREATE TABLE `crm_peoples` (
2025  `people_id` int(11) NOT NULL,
2026  `people_salutation` enum('1','2','3') DEFAULT NULL,
2027  `people_first_name` varchar(100) NOT NULL,
2028  `people_last_name` varchar(100) DEFAULT NULL,
2029  `people_job_title` varchar(100) DEFAULT NULL,
2030  `people_company` varchar(100) DEFAULT NULL,
2031  `people_department` varchar(100) DEFAULT NULL,
2032  `people_email` varchar(100) DEFAULT NULL,
2033  `people_office_phone` varchar(30) DEFAULT NULL,
2034  `people_mobile_phone` varchar(30) DEFAULT NULL,
2035  `people_industry` int(11) DEFAULT NULL,
2036  `people_address1` varchar(255) DEFAULT NULL,
2037  `people_address2` varchar(255) DEFAULT NULL,
2038  `people_city` varchar(100) DEFAULT NULL,
2039  `people_state` varchar(100) DEFAULT NULL,
2040  `people_zip_code` varchar(10) DEFAULT NULL,
2041  `people_country` varchar(50) DEFAULT NULL,
2042  `people_source` int(11) NOT NULL,
2043  `people_photo` varchar(100) DEFAULT NULL,
2044  `people_status` enum('1','2') NOT NULL COMMENT '1=Lead\n2=Customer\n'
2045) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2046
2047--
2048-- Dumping data for table `crm_peoples`
2049--
2050
2051INSERT 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
2052(17, '2', 'Herbert', 'Liau', 'Buruh', 'PT. MLM Maju Bersama', 'Marketing', 'herbertliau@yahoo.com', '082-32323232', '085736485999', 4, 'Jl. Puyuh Raya No.31', 'Kelapa Gading', 'Jakarta Timur', 'DKI Jakarta', '78388', 'Indonesia', 4, NULL, '1'),
2053(18, '1', 'Fais', 'Nasrullah', 'Buruh', 'PT. MLM Maju Bersama', 'Marketing', 'fais@gmail.com', '082-32323232', '085736485999', 4, 'Jl. Puyuh Raya No.31', 'Kelapa Gading', 'Jakarta Timur', 'DKI Jakarta', '78388', 'Indonesia', 4, NULL, '1'),
2054(19, '2', 'Fais', 'Nasrullah', 'Kepala Bagian', 'Bangsa Cerdas', NULL, 'fais@gmail.com', '08528398293', '08834834743', 4, 'Jl. Puyuh Raya no. 1 blok z2', 'Kelapa Gading', 'Jakarta Utara', 'DKI Jakarta', '892389', 'Indonesia', 6, NULL, '1'),
2055(20, '1', 'New', 'Contact', 'Buruh', 'PT. MLM Maju Bersama', 'Perlindungan Anak', 'newcontac@gamail.com', '0832783283', '0832673627', 4, 'Jl. Kelapa Puyuh Raya no3', 'Kelapa Gading', 'Jakarta Utara', 'Jakarta', '84933', 'Indonesia', 4, NULL, '2'),
2056(21, '2', 'Bambang', 'Bambang', 'Juru Ketik', 'Republic Of Koders', 'Juru Ketik', 'bambang@yahoo.com', '022-9892392', '085726372673', NULL, 'Jl. Kalimalang', NULL, NULL, NULL, NULL, NULL, 4, 'cc608e85-27d0-4b13-82fb-efb74713d778.jpg', '1'),
2057(26, '2', 'dasda', 'asdasd', NULL, 'asdas', NULL, 'asdas@asd.asd', NULL, NULL, NULL, 'sadasdasdasd', NULL, NULL, NULL, NULL, NULL, 3, NULL, '1'),
2058(27, '2', 'dasd', 'adasd', NULL, 'asdasd', NULL, 'ada@sds.ada', NULL, NULL, NULL, 'asdasdasdasd', NULL, NULL, NULL, NULL, NULL, 4, NULL, '1'),
2059(28, '2', 'qweqweqw', 'eqweqweqwe', 'qweqweqweqwe', 'qweqweqweqweqw', NULL, 'eqweqwe@asda.asda', '123123123', '123123123', NULL, 'dasdasdasd', NULL, NULL, NULL, NULL, NULL, 5, NULL, '1');
2060
2061-- --------------------------------------------------------
2062
2063--
2064-- Table structure for table `crm_products`
2065--
2066
2067DROP TABLE IF EXISTS `crm_products`;
2068CREATE TABLE `crm_products` (
2069  `product_id` int(11) NOT NULL,
2070  `product_name` varchar(100) DEFAULT NULL,
2071  `product_category_id` int(11) DEFAULT NULL,
2072  `product_description` text,
2073  `product_type` enum('1','2','3') NOT NULL COMMENT '1=Product\n2=Services\n3=Subscription\n',
2074  `product_price_frequency` enum('1','2','3') DEFAULT NULL COMMENT '1=One Time\n2=Monthly\n3=Annually ',
2075  `product_price` double NOT NULL DEFAULT '0',
2076  `product_status` enum('0','1') NOT NULL DEFAULT '1' COMMENT '0=Non Aktif\n1=Aktif\n',
2077  `user_created` varchar(100) DEFAULT NULL,
2078  `user_created_date` datetime DEFAULT NULL,
2079  `user_modified` varchar(100) DEFAULT NULL,
2080  `user_modified_date` datetime DEFAULT NULL
2081) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2082
2083--
2084-- Dumping data for table `crm_products`
2085--
2086
2087INSERT 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
2088(6, 'Back End Course, With PHP', 6, 'pembelajaran membahas tentang bagaimana membuat sebuah program menggunakan PHP', '2', '1', 4000000, '1', 'parinussa', '2017-08-25 04:21:47', 'parinussa', '2017-09-06 05:36:01'),
2089(7, 'Angular 4 Course', 7, 'Belajar tentang angular 4', '2', '1', 9000000, '1', 'parinussa', '2017-08-25 04:23:07', NULL, NULL),
2090(8, 'Animation With Macromedia 8', 9, 'Belajar membuat animasi', '2', '1', 3000000, '1', 'parinussa', '2017-08-25 04:23:45', NULL, NULL),
2091(9, 'Crash Course', 7, 'Belajar membuat tampilan web dalam waktu 5 hari', '2', '1', 2000000, '1', 'parinussa', '2017-08-25 04:24:23', NULL, NULL),
2092(10, 'Full Stack Web Dev', 5, 'belajar membuat web. mulai dari tampilan depan hingga bagian back end menggunakan PHP', '2', '1', 20000000, '1', 'parinussa', '2017-08-25 04:25:22', NULL, NULL),
2093(11, 'Ionic', 7, 'belajar membuat mobile Apps hybrid menggunakan ionic', '2', '1', 15000000, '1', 'parinussa', '2017-08-25 04:26:06', NULL, NULL),
2094(12, 'Junior coder Kmy', 9, 'game and apps', '2', '1', 8000000, '1', 'ardiansyah', '2017-08-29 08:07:36', 'adiputra', '2017-08-29 08:49:22'),
2095(13, 'Junior Koder Pik', 5, 'junior koder', '1', '2', 9000000, '1', 'adiputra', '2017-08-29 08:49:50', NULL, NULL),
2096(14, 'Junior Koder', 5, 'Kelas koding untuk anak di bawah 13 tahun', '2', '1', 9000000, '1', 'parinussa', '2017-09-06 03:16:44', 'parinussa', '2017-09-06 03:40:15');
2097
2098-- --------------------------------------------------------
2099
2100--
2101-- Table structure for table `crm_products_campaigns`
2102--
2103
2104DROP TABLE IF EXISTS `crm_products_campaigns`;
2105CREATE TABLE `crm_products_campaigns` (
2106  `product_id` int(11) NOT NULL,
2107  `campaign_id` int(11) NOT NULL,
2108  `discount_type` enum('1','2') DEFAULT NULL COMMENT '1=Percentage\n2=Fixed',
2109  `amount` double DEFAULT NULL
2110) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2111
2112--
2113-- Dumping data for table `crm_products_campaigns`
2114--
2115
2116INSERT INTO `crm_products_campaigns` (`product_id`, `campaign_id`, `discount_type`, `amount`) VALUES
2117(6, 1, '1', 50),
2118(9, 1, '2', 500000),
2119(9, 4, '1', 25),
2120(10, 4, '1', 25),
2121(12, 2, '1', 50),
2122(14, 5, '1', 20);
2123
2124-- --------------------------------------------------------
2125
2126--
2127-- Table structure for table `crm_product_categories`
2128--
2129
2130DROP TABLE IF EXISTS `crm_product_categories`;
2131CREATE TABLE `crm_product_categories` (
2132  `category_id` int(11) NOT NULL,
2133  `category_name` varchar(100) NOT NULL,
2134  `category_status` enum('0','1') DEFAULT '1' COMMENT '0=non aktif\n1=aktif',
2135  `user_created` varchar(100) DEFAULT NULL,
2136  `user_created_date` datetime DEFAULT NULL,
2137  `user_modified` varchar(100) DEFAULT NULL,
2138  `user_modified_date` datetime DEFAULT NULL
2139) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2140
2141--
2142-- Dumping data for table `crm_product_categories`
2143--
2144
2145INSERT INTO `crm_product_categories` (`category_id`, `category_name`, `category_status`, `user_created`, `user_created_date`, `user_modified`, `user_modified_date`) VALUES
2146(5, 'Full Stack', '1', 'parinussa', '2017-08-25 04:20:53', NULL, NULL),
2147(6, 'Back End Dev', '1', 'parinussa', '2017-08-25 04:21:09', NULL, NULL),
2148(7, 'Front End Dev', '1', 'parinussa', '2017-08-25 04:21:17', NULL, NULL),
2149(8, 'Design', '1', 'parinussa', '2017-08-25 04:21:24', NULL, NULL),
2150(9, 'Animation', '1', 'parinussa', '2017-08-25 04:21:32', NULL, NULL),
2151(10, 'adasdas', '1', 'parinussa', '2017-09-28 10:08:32', NULL, NULL);
2152
2153-- --------------------------------------------------------
2154
2155--
2156-- Table structure for table `crm_roles`
2157--
2158
2159DROP TABLE IF EXISTS `crm_roles`;
2160CREATE TABLE `crm_roles` (
2161  `role_id` int(11) NOT NULL COMMENT 'User Administrator Hard Code All access module',
2162  `role_name` varchar(100) DEFAULT NULL,
2163  `status` enum('0','1') DEFAULT NULL,
2164  `user_created` varchar(100) DEFAULT NULL,
2165  `user_created_date` datetime DEFAULT NULL,
2166  `user_modified` varchar(100) DEFAULT NULL,
2167  `user_modified_date` datetime DEFAULT NULL
2168) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2169
2170--
2171-- Dumping data for table `crm_roles`
2172--
2173
2174INSERT INTO `crm_roles` (`role_id`, `role_name`, `status`, `user_created`, `user_created_date`, `user_modified`, `user_modified_date`) VALUES
2175(1, 'Administrator', '1', 'admin', '2017-08-23 00:00:00', NULL, NULL),
2176(2, 'Marketing', '1', 'admin', '2017-08-23 00:00:00', NULL, NULL),
2177(3, 'Supervisor', '1', 'admin', '2017-08-23 00:00:00', NULL, NULL),
2178(4, 'CEO', '1', 'admin', '2017-08-23 00:00:00', NULL, NULL),
2179(7, 'Developer', '1', 'muhaliusman', '2017-08-23 16:28:40', NULL, NULL);
2180
2181-- --------------------------------------------------------
2182
2183--
2184-- Table structure for table `crm_roles_modules`
2185--
2186
2187DROP TABLE IF EXISTS `crm_roles_modules`;
2188CREATE TABLE `crm_roles_modules` (
2189  `crm_roles_role_id` int(11) NOT NULL,
2190  `crm_modules_module_id` int(11) NOT NULL
2191) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2192
2193--
2194-- Dumping data for table `crm_roles_modules`
2195--
2196
2197INSERT INTO `crm_roles_modules` (`crm_roles_role_id`, `crm_modules_module_id`) VALUES
2198(2, 1),
2199(2, 8),
2200(2, 10),
2201(4, 1),
2202(4, 2),
2203(4, 3),
2204(4, 4),
2205(4, 5),
2206(4, 6),
2207(4, 8),
2208(4, 9),
2209(7, 1),
2210(7, 2),
2211(7, 3),
2212(7, 4),
2213(7, 5),
2214(7, 6),
2215(7, 8),
2216(7, 9),
2217(7, 10),
2218(7, 12);
2219
2220-- --------------------------------------------------------
2221
2222--
2223-- Table structure for table `crm_setup_company`
2224--
2225
2226DROP TABLE IF EXISTS `crm_setup_company`;
2227CREATE TABLE `crm_setup_company` (
2228  `company_id` int(11) NOT NULL,
2229  `company_name` varchar(225) NOT NULL,
2230  `company_email` varchar(225) DEFAULT NULL,
2231  `company_address1` varchar(225) NOT NULL,
2232  `company_address2` varchar(100) DEFAULT NULL,
2233  `company_city` varchar(225) NOT NULL,
2234  `company_state` varchar(225) NOT NULL,
2235  `company_zip_code` varchar(50) NOT NULL,
2236  `company_country` varchar(225) NOT NULL,
2237  `company_phone` varchar(50) DEFAULT NULL,
2238  `company_logo` varchar(100) DEFAULT NULL,
2239  `company_fax` varchar(50) DEFAULT NULL,
2240  `company_website` varchar(100) DEFAULT NULL,
2241  `company_initial_setting` enum('0','1') DEFAULT '0'
2242) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2243
2244--
2245-- Dumping data for table `crm_setup_company`
2246--
2247
2248INSERT 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
2249(5, 'Republic Of Koders', 'official@republicofkoders.com', 'Jl. Puyuh Raya Blok Z2 No.1', NULL, 'Jakarta Utara', 'DKI Jakarta', '277374', 'Indonesia', '627366373', NULL, NULL, NULL, '1');
2250
2251-- --------------------------------------------------------
2252
2253--
2254-- Table structure for table `crm_source`
2255--
2256
2257DROP TABLE IF EXISTS `crm_source`;
2258CREATE TABLE `crm_source` (
2259  `source_id` int(11) NOT NULL,
2260  `source_name` varchar(100) NOT NULL,
2261  `user_created` varchar(100) DEFAULT NULL,
2262  `user_created_date` datetime DEFAULT NULL,
2263  `user_modified` varchar(100) DEFAULT NULL,
2264  `user_modified_date` datetime DEFAULT NULL
2265) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2266
2267--
2268-- Dumping data for table `crm_source`
2269--
2270
2271INSERT INTO `crm_source` (`source_id`, `source_name`, `user_created`, `user_created_date`, `user_modified`, `user_modified_date`) VALUES
2272(3, 'Facebook', 'muhaliusman', '2017-08-25 04:07:37', NULL, NULL),
2273(4, 'Koran', 'muhaliusman', '2017-08-25 04:07:44', NULL, NULL),
2274(5, 'Television', 'muhaliusman', '2017-08-25 04:07:53', NULL, NULL),
2275(6, 'Exhibition', 'muhaliusman', '2017-08-25 04:08:15', NULL, NULL);
2276
2277-- --------------------------------------------------------
2278
2279--
2280-- Table structure for table `crm_terms`
2281--
2282
2283DROP TABLE IF EXISTS `crm_terms`;
2284CREATE TABLE `crm_terms` (
2285  `term_id` int(11) NOT NULL,
2286  `term_description` varchar(100) NOT NULL,
2287  `term_day` int(11) NOT NULL,
2288  `term_default` enum('0','1') DEFAULT '0' COMMENT '0=Not Default\n1=Default'
2289) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2290
2291--
2292-- Dumping data for table `crm_terms`
2293--
2294
2295INSERT INTO `crm_terms` (`term_id`, `term_description`, `term_day`, `term_default`) VALUES
2296(1, '1 Week', 7, '1'),
2297(2, '1 Month', 30, '0');
2298
2299-- --------------------------------------------------------
2300
2301--
2302-- Table structure for table `crm_tickets`
2303--
2304
2305DROP TABLE IF EXISTS `crm_tickets`;
2306CREATE TABLE `crm_tickets` (
2307  `ticket_id` int(11) NOT NULL,
2308  `ticket_subject` varchar(255) NOT NULL,
2309  `ticket_customer_id` int(11) NOT NULL,
2310  `ticket_date` datetime NOT NULL,
2311  `ticket_type_id` int(11) NOT NULL,
2312  `ticket_priority` enum('1','2','3','4') NOT NULL COMMENT '1 = low, 2=medium, 3 = High, 4 = Urgent',
2313  `ticket_description` text NOT NULL,
2314  `ticket_status_id` int(11) NOT NULL,
2315  `ticket_file` varchar(255) NOT NULL,
2316  `ticket_note` text,
2317  `ticket_note_file` varchar(200) DEFAULT NULL
2318) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2319
2320--
2321-- Dumping data for table `crm_tickets`
2322--
2323
2324INSERT INTO `crm_tickets` (`ticket_id`, `ticket_subject`, `ticket_customer_id`, `ticket_date`, `ticket_type_id`, `ticket_priority`, `ticket_description`, `ticket_status_id`, `ticket_file`, `ticket_note`, `ticket_note_file`) VALUES
2325(8, 'Ticket Baru', 9, '2017-09-19 00:00:00', 2, '2', 'ini descriptionnya', 1, '521e48a7-925f-4d5f-8aef-ea9c11bb0c2f.pdf', NULL, NULL);
2326
2327-- --------------------------------------------------------
2328
2329--
2330-- Table structure for table `crm_tickets_employee`
2331--
2332
2333DROP TABLE IF EXISTS `crm_tickets_employee`;
2334CREATE TABLE `crm_tickets_employee` (
2335  `ticket_id` int(11) NOT NULL,
2336  `employee_id` int(11) NOT NULL,
2337  `date_assigned` datetime NOT NULL
2338) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2339
2340-- --------------------------------------------------------
2341
2342--
2343-- Table structure for table `crm_ticket_status`
2344--
2345
2346DROP TABLE IF EXISTS `crm_ticket_status`;
2347CREATE TABLE `crm_ticket_status` (
2348  `ticket_status_id` int(11) NOT NULL,
2349  `ticket_status_name` varchar(255) NOT NULL,
2350  `ticket_status_score` int(11) NOT NULL
2351) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2352
2353--
2354-- Dumping data for table `crm_ticket_status`
2355--
2356
2357INSERT INTO `crm_ticket_status` (`ticket_status_id`, `ticket_status_name`, `ticket_status_score`) VALUES
2358(1, 'New', 0),
2359(2, 'On Process', 50),
2360(3, 'Close', 100);
2361
2362-- --------------------------------------------------------
2363
2364--
2365-- Table structure for table `crm_ticket_type`
2366--
2367
2368DROP TABLE IF EXISTS `crm_ticket_type`;
2369CREATE TABLE `crm_ticket_type` (
2370  `ticket_type_id` int(11) NOT NULL,
2371  `ticket_type_name` varchar(255) NOT NULL
2372) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2373
2374--
2375-- Dumping data for table `crm_ticket_type`
2376--
2377
2378INSERT INTO `crm_ticket_type` (`ticket_type_id`, `ticket_type_name`) VALUES
2379(1, 'Billing'),
2380(2, 'Service');
2381
2382-- --------------------------------------------------------
2383
2384--
2385-- Table structure for table `crm_users`
2386--
2387
2388DROP TABLE IF EXISTS `crm_users`;
2389CREATE TABLE `crm_users` (
2390  `user_id` int(11) NOT NULL,
2391  `user_first_name` varchar(100) NOT NULL,
2392  `user_last_name` varchar(100) DEFAULT NULL,
2393  `user_job_title` varchar(50) DEFAULT NULL,
2394  `user_password` varchar(100) NOT NULL,
2395  `user_mobile` varchar(30) NOT NULL,
2396  `user_username` varchar(100) NOT NULL,
2397  `user_department` varchar(100) DEFAULT NULL,
2398  `user_email` varchar(100) NOT NULL,
2399  `user_photo` varchar(100) DEFAULT NULL,
2400  `user_status` enum('0','1') DEFAULT '1' COMMENT '0=Non-Aktif  1=Aktif',
2401  `user_group_id` int(11) DEFAULT NULL,
2402  `user_role_id` int(11) NOT NULL,
2403  `user_created` varchar(100) DEFAULT NULL,
2404  `user_created_date` datetime DEFAULT NULL,
2405  `user_modified` varchar(100) DEFAULT NULL,
2406  `user_modified_date` datetime DEFAULT NULL
2407) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2408
2409--
2410-- Dumping data for table `crm_users`
2411--
2412
2413INSERT 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_role_id`, `user_created`, `user_created_date`, `user_modified`, `user_modified_date`) VALUES
2414(17, 'Muhammad', 'Ali Usman', 'admin', '$2a$10$qXpkS/T8NTxadZfUConUA.RzYw37rVtaIEL9gSBGygkU25z6LJJNO', '085717647462', 'muhaliusman', 'Administrator', 'muh.aliusman@yahoo.co.id', '75cc29d7-d042-41c1-8068-55f45cc840f7.JPG', '1', 3, 1, 'admin', '2017-08-23 00:00:00', 'muhaliusman', '2017-09-27 17:37:30'),
2415(18, 'Noval', 'Parinussa', 'Senior Programmer', '$2a$10$2Cn479SFX80weNkwlyrWA.wy78ldTIalxyy1ql5S9TLtaYAb9d0ty', '0856008793888', 'parinussa', 'Product Development', 'parinussa@gmail.com', NULL, '1', 4, 7, 'muhaliusman', '2017-08-23 16:30:05', 'muhaliusman', '2017-08-23 16:34:06'),
2416(19, 'Adi', 'Putra', 'Software Enginer', '$2a$10$2Cn479SFX80weNkwlyrWA.wy78ldTIalxyy1ql5S9TLtaYAb9d0ty', '08590023889', 'adiputra', 'Product Development', 'adiputra@gmail.com', NULL, '1', 4, 7, 'muhaliusman', '2017-08-23 16:31:04', 'muhaliusman', '2017-08-23 16:34:00'),
2417(20, 'Yusuf', 'Basori', 'Database Administrator', '$2a$10$2Cn479SFX80weNkwlyrWA.wy78ldTIalxyy1ql5S9TLtaYAb9d0ty', '087363536664', 'yusufbasori', 'Product Development', 'basori@gmail.com', NULL, '1', 4, 7, 'muhaliusman', '2017-08-23 16:32:07', 'muhaliusman', '2017-08-23 16:33:53'),
2418(21, 'Jupiter', 'Zhuo', 'CEO', '$2a$10$2Cn479SFX80weNkwlyrWA.wy78ldTIalxyy1ql5S9TLtaYAb9d0ty', '0862752722', 'jupiter', 'Course', 'jupiter@gmail.com', NULL, '1', 6, 4, 'muhaliusman', '2017-08-23 16:33:13', 'muhaliusman', '2017-08-23 16:33:45'),
2419(22, 'Ardi', 'Putra', 'Manager', '$2a$10$7QE8JDV6QLOcY29kC4FYG.a5RefbQJxcoex4TLPMZFY5PaDOLCjlW', '0856748878787', 'ardiansyah', 'Sales', 'ardi@yahoo.com', NULL, '1', 7, 2, 'muhaliusman', '2017-08-29 07:27:08', 'muhaliusman', '2017-09-27 15:08:26');
2420
2421-- --------------------------------------------------------
2422
2423--
2424-- Table structure for table `crm_users_companies`
2425--
2426
2427DROP TABLE IF EXISTS `crm_users_companies`;
2428CREATE TABLE `crm_users_companies` (
2429  `company_id` int(11) NOT NULL,
2430  `user_id` int(11) NOT NULL
2431) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2432
2433--
2434-- Dumping data for table `crm_users_companies`
2435--
2436
2437INSERT INTO `crm_users_companies` (`company_id`, `user_id`) VALUES
2438(9, 18),
2439(9, 19),
2440(10, 18),
2441(11, 18),
2442(12, 18),
2443(13, 18),
2444(14, 18);
2445
2446-- --------------------------------------------------------
2447
2448--
2449-- Table structure for table `crm_users_contacts`
2450--
2451
2452DROP TABLE IF EXISTS `crm_users_contacts`;
2453CREATE TABLE `crm_users_contacts` (
2454  `contact_id` int(11) NOT NULL,
2455  `user_id` int(11) NOT NULL
2456) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2457
2458--
2459-- Dumping data for table `crm_users_contacts`
2460--
2461
2462INSERT INTO `crm_users_contacts` (`contact_id`, `user_id`) VALUES
2463(13, 18),
2464(14, 18),
2465(14, 19),
2466(15, 18),
2467(16, 18),
2468(17, 18),
2469(18, 18),
2470(19, 18),
2471(20, 18);
2472
2473-- --------------------------------------------------------
2474
2475--
2476-- Table structure for table `crm_users_leads`
2477--
2478
2479DROP TABLE IF EXISTS `crm_users_leads`;
2480CREATE TABLE `crm_users_leads` (
2481  `lead_id` int(11) NOT NULL,
2482  `user_id` int(11) NOT NULL
2483) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2484
2485--
2486-- Dumping data for table `crm_users_leads`
2487--
2488
2489INSERT INTO `crm_users_leads` (`lead_id`, `user_id`) VALUES
2490(1, 18),
2491(1, 19),
2492(3, 18),
2493(4, 18),
2494(5, 18),
2495(6, 18),
2496(6, 19),
2497(7, 19),
2498(8, 22),
2499(9, 18),
2500(9, 20),
2501(10, 18),
2502(10, 19),
2503(11, 18),
2504(13, 18),
2505(14, 18),
2506(14, 19),
2507(15, 18),
2508(16, 18),
2509(17, 18),
2510(18, 18),
2511(19, 18),
2512(20, 18);
2513
2514-- --------------------------------------------------------
2515
2516--
2517-- Table structure for table `crm_users_opportunities`
2518--
2519
2520DROP TABLE IF EXISTS `crm_users_opportunities`;
2521CREATE TABLE `crm_users_opportunities` (
2522  `user_id` int(11) DEFAULT NULL,
2523  `opportunity_id` int(11) DEFAULT NULL
2524) ENGINE=InnoDB DEFAULT CHARSET=latin1;
2525
2526--
2527-- Dumping data for table `crm_users_opportunities`
2528--
2529
2530INSERT INTO `crm_users_opportunities` (`user_id`, `opportunity_id`) VALUES
2531(18, 11),
2532(18, 12),
2533(18, 13);
2534
2535-- --------------------------------------------------------
2536
2537--
2538-- Table structure for table `invoice_control`
2539--
2540
2541DROP TABLE IF EXISTS `invoice_control`;
2542CREATE TABLE `invoice_control` (
2543  `invoice_id` int(11) NOT NULL,
2544  `next_invoice_date` date NOT NULL,
2545  `repeat_invoice` int(11) NOT NULL
2546) ENGINE=InnoDB DEFAULT CHARSET=latin1;
2547
2548--
2549-- Dumping data for table `invoice_control`
2550--
2551
2552INSERT INTO `invoice_control` (`invoice_id`, `next_invoice_date`, `repeat_invoice`) VALUES
2553(1, '2017-09-08', 2),
2554(2, '2017-09-14', 2);
2555
2556-- --------------------------------------------------------
2557
2558--
2559-- Table structure for table `lead_calls`
2560--
2561
2562DROP TABLE IF EXISTS `lead_calls`;
2563CREATE TABLE `lead_calls` (
2564  `lead_call_id` int(11) NOT NULL,
2565  `lead_call_title` varchar(100) DEFAULT NULL,
2566  `lead_call_duration` int(11) DEFAULT NULL,
2567  `lead_call_time` enum('1','2','3') DEFAULT '1' COMMENT '1= Second\n2= Minutes\n3=Hour\n',
2568  `lead_call_notes` text,
2569  `lead_call_date` datetime NOT NULL,
2570  `lead_call_user` varchar(100) NOT NULL,
2571  `lead_id` int(11) NOT NULL
2572) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2573
2574--
2575-- Dumping data for table `lead_calls`
2576--
2577
2578INSERT INTO `lead_calls` (`lead_call_id`, `lead_call_title`, `lead_call_duration`, `lead_call_time`, `lead_call_notes`, `lead_call_date`, `lead_call_user`, `lead_id`) VALUES
2579(1, 'paggilan baru', 3, '2', 'panggilan baru', '2017-08-29 04:28:11', 'adiputra', 6),
2580(2, 'enw', 50, '1', 'dasdasd', '2017-08-29 04:39:05', 'adiputra', 6),
2581(3, 'panggilan baru', 20, '2', 'panggilan hari ini', '2017-08-29 04:39:31', 'adiputra', 6),
2582(4, 'sdaas', 45, '1', 'adasdasd', '2017-08-29 04:41:21', 'adiputra', 6),
2583(5, 'call', 5, '2', 'note', '2017-08-29 08:01:40', 'adiputra', 7),
2584(6, 'call pertama', 3, '1', 'ini adalaha panggilan pertama', '2017-09-14 14:09:49', 'parinussa', 10);
2585
2586-- --------------------------------------------------------
2587
2588--
2589-- Table structure for table `lead_files`
2590--
2591
2592DROP TABLE IF EXISTS `lead_files`;
2593CREATE TABLE `lead_files` (
2594  `lead_files_id` int(11) NOT NULL,
2595  `lead_file_subject` varchar(100) DEFAULT NULL,
2596  `lead_file_to` varchar(100) NOT NULL,
2597  `lead_file_cc` varchar(100) DEFAULT NULL,
2598  `lead_file_description` text,
2599  `lead_file_name` varchar(100) NOT NULL,
2600  `lead_file_date` datetime NOT NULL,
2601  `lead_file_user` varchar(100) NOT NULL,
2602  `lead_id` int(11) NOT NULL
2603) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2604
2605--
2606-- Dumping data for table `lead_files`
2607--
2608
2609INSERT 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
2610(1, 'ini', 'wahyu@majubersama.com', 'adiputra@gmail.com,basori@gmail.com', 'ini file baru', '2addfdd3-6704-431f-86de-a18139310266.pdf', '2017-08-29 04:26:38', 'adiputra', 6),
2611(2, 'New file 2', 'wahyu@majubersama.com', 'adiputra@gmail.com', 'e of Chichén Itzá altered the political spectrum in the Yucatán peninsula and began eroding the dominance of Coba. Beginning around 900 or 1000 AD, Coba must have begun', 'f1d6bcbd-6ceb-404e-8448-e6364f6e8360.pdf', '2017-08-29 04:31:45', 'adiputra', 6),
2612(3, 'new file again', 'wahyu@majubersama.com', 'adiputra@gmail.com,,basori@gmail.com', 'ini baru lagi evidenced by the new buildings dating to the time 1200-1500 AD, now built in the typical Eastern coastal style. However, power centers and trading routes had moved to the coast,', '01c54204-323a-44b5-8079-8ad200671c25.pdf', '2017-08-29 04:32:17', 'adiputra', 6),
2613(4, 'new file', 'wahyu@majubersama.com', 'adiputra@gmail.com,,parinussa@gmail.com', 'forcing cities like Coba into a secondary status, although somewhat more successful than its more ephemeral enemy Chichén Itzá. The greatest of the Coba kings was King Goon.', '643738aa-dfa1-4714-bf2b-7eb95c7ac59a.pdf', '2017-08-29 04:32:56', 'adiputra', 6),
2614(5, 'subject', 'tohir@gmail.com', 'adiputra@gmail.com,parinussa@gmail.com', 'deskripsi file', '50eb20c1-3b8a-4d8a-8fc6-dc10973596a0.pdf', '2017-08-29 07:47:29', 'adiputra', 7),
2615(6, 'sdfsdf', 'lead@yahoo.com', 'parinussa@gmail.com,muh.aliusman@yahoo.co.id', NULL, 'b6ce4ea4-f5a7-4c51-b5e5-07587d6c3a6b.jpg', '2017-09-14 14:06:59', 'parinussa', 10);
2616
2617-- --------------------------------------------------------
2618
2619--
2620-- Table structure for table `lead_meeting`
2621--
2622
2623DROP TABLE IF EXISTS `lead_meeting`;
2624CREATE TABLE `lead_meeting` (
2625  `lead_meeting_id` int(11) NOT NULL,
2626  `lead_meeting_name` varchar(100) DEFAULT NULL,
2627  `lead_meeting_location` varchar(255) DEFAULT NULL,
2628  `lead_meeting_start_date` datetime DEFAULT NULL,
2629  `lead_meeting_end_date` datetime DEFAULT NULL,
2630  `lead_meeting_description` text,
2631  `lead_meeting_date` datetime NOT NULL,
2632  `lead_meeting_user` varchar(100) NOT NULL,
2633  `lead_id` int(11) NOT NULL
2634) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2635
2636--
2637-- Dumping data for table `lead_meeting`
2638--
2639
2640INSERT 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
2641(1, 'CRM', 'KN', '2017-08-29 00:00:00', '2017-08-29 00:00:00', 'membahas project kn', '2017-08-29 04:29:00', 'adiputra', 6),
2642(2, 'meeting', 'kemayoran', '2017-08-29 00:00:00', '2017-08-29 00:00:00', 'meeting', '2017-08-29 08:00:10', 'adiputra', 7),
2643(3, 'meeting 2', 'kelapa gading', '2017-08-31 00:00:00', '2017-08-31 00:00:00', 'meeting kelapa gading', '2017-08-29 08:00:54', 'adiputra', 7);
2644
2645-- --------------------------------------------------------
2646
2647--
2648-- Table structure for table `lead_meeting_users`
2649--
2650
2651DROP TABLE IF EXISTS `lead_meeting_users`;
2652CREATE TABLE `lead_meeting_users` (
2653  `lead_meeting_id` int(11) NOT NULL,
2654  `lead_user_id` int(11) NOT NULL
2655) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2656
2657--
2658-- Dumping data for table `lead_meeting_users`
2659--
2660
2661INSERT INTO `lead_meeting_users` (`lead_meeting_id`, `lead_user_id`) VALUES
2662(1, 18),
2663(1, 21),
2664(2, 18),
2665(3, 18);
2666
2667-- --------------------------------------------------------
2668
2669--
2670-- Table structure for table `lead_notes`
2671--
2672
2673DROP TABLE IF EXISTS `lead_notes`;
2674CREATE TABLE `lead_notes` (
2675  `lead_note_id` int(11) NOT NULL,
2676  `lead_note_name` varchar(100) NOT NULL,
2677  `lead_note_description` text,
2678  `lead_note_date` datetime NOT NULL,
2679  `lead_note_user` varchar(100) NOT NULL,
2680  `lead_id` int(11) NOT NULL
2681) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2682
2683--
2684-- Dumping data for table `lead_notes`
2685--
2686
2687INSERT INTO `lead_notes` (`lead_note_id`, `lead_note_name`, `lead_note_description`, `lead_note_date`, `lead_note_user`, `lead_id`) VALUES
2688(1, 'Note Pertama', '<p>ini adalah descripsi note pertama</p>', '2017-08-28 16:34:17', 'parinussa', 3),
2689(3, 'Note baru lgi', '<span style=\"color: rgb(34, 34, 34); font-family: sans-serif; font-size: 14px; letter-spacing: normal;\">oubled faced features that were found around the majority of the household clusters. These were often linked to the platforms that led to the sacbeobs. A lot of features found tended to connect to something or lead to something but the other end was left open-ended. Coba has many features that are platforms or on platforms. The last major linear&nbsp;</span>', '2017-08-29 04:30:46', 'adiputra', 6),
2690(4, 'coba lagi', '<span style=\"color: rgb(34, 34, 34); font-family: sans-serif; font-size: 14px; letter-spacing: normal;\">by the new buildings dating to the time 1200-1500 AD, now built in the typical Eastern coastal style. However, power centers and trading routes had moved to the coast,</span>', '2017-08-29 04:31:07', 'adiputra', 6),
2691(5, 'note', '<p>contoh note</p>', '2017-08-29 07:46:28', 'adiputra', 7),
2692(6, 'Judul Note', '<p>Ini adalah judul note yang bisasa dilakukan</p>', '2017-09-14 13:59:29', 'parinussa', 10);
2693
2694-- --------------------------------------------------------
2695
2696--
2697-- Table structure for table `lead_tasks`
2698--
2699
2700DROP TABLE IF EXISTS `lead_tasks`;
2701CREATE TABLE `lead_tasks` (
2702  `lead_task_id` int(11) NOT NULL,
2703  `lead_task_name` varchar(100) NOT NULL,
2704  `lead_task_description` varchar(45) DEFAULT NULL,
2705  `lead_task_status` int(11) DEFAULT NULL,
2706  `lead_task_due_date` datetime DEFAULT NULL,
2707  `lead_task_date` datetime NOT NULL,
2708  `lead_task_user` varchar(100) NOT NULL,
2709  `lead_id` int(11) NOT NULL
2710) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2711
2712--
2713-- Dumping data for table `lead_tasks`
2714--
2715
2716INSERT INTO `lead_tasks` (`lead_task_id`, `lead_task_name`, `lead_task_description`, `lead_task_status`, `lead_task_due_date`, `lead_task_date`, `lead_task_user`, `lead_id`) VALUES
2717(1, 'Task Pertama', 'Ini task pertama untuk di follow up', 100, '2017-08-28 00:00:00', '2017-08-28 16:30:45', 'parinussa', 3),
2718(2, 'Task Kedua', 'Ini Task Kedua', 30, '2017-08-28 00:00:00', '2017-08-28 16:31:14', 'parinussa', 3),
2719(3, 'Task Ketiga', 'Ini isi task Ketiga', 40, '2017-08-28 00:00:00', '2017-08-28 16:31:40', 'parinussa', 3),
2720(4, 'Task Keempat', 'ini task keempat', 60, '2017-08-28 00:00:00', '2017-08-28 16:32:57', 'parinussa', 3),
2721(5, 'Task Kelima', 'ini task kelima', 20, '2017-08-28 00:00:00', '2017-08-28 16:33:24', 'parinussa', 3),
2722(6, 'task keenam', 'ini task keenam', 30, '2017-08-28 00:00:00', '2017-08-28 16:33:48', 'parinussa', 3),
2723(7, 'new task', 'this is new task', 50, '2017-08-29 00:00:00', '2017-08-29 04:35:13', 'adiputra', 6),
2724(8, 'new task again', 'this is new task again', 40, '2017-08-29 00:00:00', '2017-08-29 04:35:32', 'adiputra', 6),
2725(9, 'new task 2', 'a lengthy power struggle with Chichén Itzá, w', 40, '2017-08-29 00:00:00', '2017-08-29 04:35:55', 'adiputra', 6),
2726(10, 'New Task 3', 'new task again', 70, '2017-08-29 00:00:00', '2017-08-29 04:36:12', 'adiputra', 6),
2727(11, 'New Task 5', 'This is new task 5', 70, '2017-08-29 00:00:00', '2017-08-29 04:36:34', 'adiputra', 6),
2728(12, 'Folloe up', 'deskripsi', 60, '2017-08-29 00:00:00', '2017-08-29 07:43:58', 'adiputra', 7),
2729(13, 'task baru', 'deskripsi', 40, '2017-08-29 00:00:00', '2017-08-29 07:44:21', 'adiputra', 7),
2730(14, 'Task Pertama', 'Ini Task Pertama', 80, '2017-09-14 00:00:00', '2017-09-14 13:58:55', 'parinussa', 10);
2731
2732-- --------------------------------------------------------
2733
2734--
2735-- Table structure for table `opportunity_calls`
2736--
2737
2738DROP TABLE IF EXISTS `opportunity_calls`;
2739CREATE TABLE `opportunity_calls` (
2740  `opportunity_call_id` int(11) NOT NULL,
2741  `opportunity_call_title` varchar(100) DEFAULT NULL,
2742  `opportunity_call_duration` int(11) DEFAULT NULL,
2743  `opportunity_call_time` enum('1','2','3') DEFAULT '1' COMMENT '1= Second\n2= Minutes\n3=Hour\n',
2744  `opportunity_call_notes` text,
2745  `opportunity_call_date` datetime NOT NULL,
2746  `opportunity_call_user` varchar(100) NOT NULL,
2747  `opportunity_id` int(11) NOT NULL
2748) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2749
2750-- --------------------------------------------------------
2751
2752--
2753-- Table structure for table `opportunity_files`
2754--
2755
2756DROP TABLE IF EXISTS `opportunity_files`;
2757CREATE TABLE `opportunity_files` (
2758  `opportunity_files_id` int(11) NOT NULL,
2759  `opportunity_file_subject` varchar(100) DEFAULT NULL,
2760  `opportunity_file_to` varchar(100) NOT NULL,
2761  `opportunity_file_cc` varchar(100) DEFAULT NULL,
2762  `opportunity_file_description` text,
2763  `opportunity_file_name` varchar(100) NOT NULL,
2764  `opportunity_file_date` datetime NOT NULL,
2765  `opportunity_file_user` varchar(100) NOT NULL,
2766  `opportunity_id` int(11) NOT NULL
2767) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2768
2769-- --------------------------------------------------------
2770
2771--
2772-- Table structure for table `opportunity_meeting`
2773--
2774
2775DROP TABLE IF EXISTS `opportunity_meeting`;
2776CREATE TABLE `opportunity_meeting` (
2777  `opportunity_meeting_id` int(11) NOT NULL,
2778  `opportunity_meeting_name` varchar(100) DEFAULT NULL,
2779  `opportunity_meeting_location` varchar(255) DEFAULT NULL,
2780  `opportunity_meeting_start_date` datetime DEFAULT NULL,
2781  `opportunity_meeting_end_date` datetime DEFAULT NULL,
2782  `opportunity_meeting_description` text,
2783  `opportunity_meeting_date` datetime NOT NULL,
2784  `opportunity_meeting_user` varchar(100) NOT NULL,
2785  `opportunity_id` int(11) NOT NULL
2786) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2787
2788-- --------------------------------------------------------
2789
2790--
2791-- Table structure for table `opportunity_meeting_users`
2792--
2793
2794DROP TABLE IF EXISTS `opportunity_meeting_users`;
2795CREATE TABLE `opportunity_meeting_users` (
2796  `opportunity_meeting_id` int(11) NOT NULL,
2797  `opportunity_user_id` int(11) NOT NULL
2798) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2799
2800-- --------------------------------------------------------
2801
2802--
2803-- Table structure for table `opportunity_notes`
2804--
2805
2806DROP TABLE IF EXISTS `opportunity_notes`;
2807CREATE TABLE `opportunity_notes` (
2808  `opportunity_note_id` int(11) NOT NULL,
2809  `opportunity_note_name` varchar(100) NOT NULL,
2810  `opportunity_note_description` text,
2811  `opportunity_note_date` datetime DEFAULT NULL,
2812  `opportunity_note_user` varchar(100) DEFAULT NULL,
2813  `opportunity_id` int(11) NOT NULL
2814) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2815
2816-- --------------------------------------------------------
2817
2818--
2819-- Table structure for table `opportunity_tasks`
2820--
2821
2822DROP TABLE IF EXISTS `opportunity_tasks`;
2823CREATE TABLE `opportunity_tasks` (
2824  `opportunity_task_id` int(11) NOT NULL,
2825  `opportunity_task_name` varchar(100) NOT NULL,
2826  `opportunity_task_description` varchar(45) DEFAULT NULL,
2827  `opportunity_task_status` int(11) DEFAULT NULL,
2828  `opportunity_task_due_date` datetime DEFAULT NULL,
2829  `opportunity_task_date` datetime NOT NULL,
2830  `opportunity_task_user` varchar(100) NOT NULL,
2831  `opportunity_id` int(11) NOT NULL
2832) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2833
2834--
2835-- Indexes for dumped tables
2836--
2837
2838--
2839-- Indexes for table `coba`
2840--
2841ALTER TABLE `coba`
2842  ADD PRIMARY KEY (`id`);
2843
2844--
2845-- Indexes for table `company_calls`
2846--
2847ALTER TABLE `company_calls`
2848  ADD PRIMARY KEY (`company_call_id`),
2849  ADD KEY `fk_company_calls_crm_company1_idx` (`company_id`);
2850
2851--
2852-- Indexes for table `company_files`
2853--
2854ALTER TABLE `company_files`
2855  ADD PRIMARY KEY (`company_files_id`),
2856  ADD KEY `fk_company_files_crm_company1_idx` (`company_id`);
2857
2858--
2859-- Indexes for table `company_meeting`
2860--
2861ALTER TABLE `company_meeting`
2862  ADD PRIMARY KEY (`company_meeting_id`),
2863  ADD KEY `fk_company_meeting_crm_company1_idx` (`company_id`);
2864
2865--
2866-- Indexes for table `company_meeting_users`
2867--
2868ALTER TABLE `company_meeting_users`
2869  ADD PRIMARY KEY (`company_meeting_id`,`company_user_id`),
2870  ADD KEY `fk_company_meeting_has_crm_users_crm_users1_idx` (`company_user_id`),
2871  ADD KEY `fk_company_meeting_has_crm_users_company_meeting1_idx` (`company_meeting_id`);
2872
2873--
2874-- Indexes for table `company_notes`
2875--
2876ALTER TABLE `company_notes`
2877  ADD PRIMARY KEY (`company_note_id`),
2878  ADD KEY `fk_company_notes_crm_company1_idx` (`company_id`);
2879
2880--
2881-- Indexes for table `company_tasks`
2882--
2883ALTER TABLE `company_tasks`
2884  ADD PRIMARY KEY (`company_task_id`),
2885  ADD KEY `fk_company_tasks_crm_company1_idx` (`company_id`);
2886
2887--
2888-- Indexes for table `contact_calls`
2889--
2890ALTER TABLE `contact_calls`
2891  ADD PRIMARY KEY (`contact_call_id`),
2892  ADD KEY `fk_customer_calls_crm_customers1_idx` (`contact_id`);
2893
2894--
2895-- Indexes for table `contact_files`
2896--
2897ALTER TABLE `contact_files`
2898  ADD PRIMARY KEY (`contact_files_id`),
2899  ADD KEY `fk_customer_files_copy1_crm_customers1_idx` (`contact_id`);
2900
2901--
2902-- Indexes for table `contact_meeting`
2903--
2904ALTER TABLE `contact_meeting`
2905  ADD PRIMARY KEY (`contact_meeting_id`),
2906  ADD KEY `fk_customer_meeting_crm_customers1_idx` (`contact_id`);
2907
2908--
2909-- Indexes for table `contact_meeting_users`
2910--
2911ALTER TABLE `contact_meeting_users`
2912  ADD PRIMARY KEY (`contact_meeting_id`,`contact_user_id`),
2913  ADD KEY `fk_contact_meeting_has_crm_users_crm_users1_idx` (`contact_user_id`),
2914  ADD KEY `fk_contact_meeting_has_crm_users_contact_meeting1_idx` (`contact_meeting_id`);
2915
2916--
2917-- Indexes for table `contact_notes`
2918--
2919ALTER TABLE `contact_notes`
2920  ADD PRIMARY KEY (`contact_note_id`),
2921  ADD KEY `fk_customer_notes_crm_customers1_idx` (`contact_id`);
2922
2923--
2924-- Indexes for table `contact_tasks`
2925--
2926ALTER TABLE `contact_tasks`
2927  ADD PRIMARY KEY (`contact_task_id`),
2928  ADD KEY `fk_customer_tasks_crm_customers1_idx` (`contact_id`);
2929
2930--
2931-- Indexes for table `crm_campaigns`
2932--
2933ALTER TABLE `crm_campaigns`
2934  ADD PRIMARY KEY (`campaign_id`);
2935
2936--
2937-- Indexes for table `crm_companies`
2938--
2939ALTER TABLE `crm_companies`
2940  ADD PRIMARY KEY (`company_id`),
2941  ADD KEY `fk_crm_company_crm_industry1_idx` (`company_industry`),
2942  ADD KEY `fk_crm_company_crm_groups1_idx` (`company_groups`),
2943  ADD KEY `fk_crm_companies_crm_companies1_idx` (`company_parent`);
2944
2945--
2946-- Indexes for table `crm_companies_products`
2947--
2948ALTER TABLE `crm_companies_products`
2949  ADD KEY `company_id` (`company_id`),
2950  ADD KEY `product_id` (`product_id`);
2951
2952--
2953-- Indexes for table `crm_contacts`
2954--
2955ALTER TABLE `crm_contacts`
2956  ADD PRIMARY KEY (`contact_id`),
2957  ADD KEY `fk_crm_customer_crm_company1_idx` (`company_id`),
2958  ADD KEY `fk_crm_customer_crm_groups1_idx` (`contact_groups`),
2959  ADD KEY `fk_crm_customer_peoples1_idx` (`contact_id`),
2960  ADD KEY `fk_crm_contacts_crm_peoples1_idx` (`people_id`);
2961
2962--
2963-- Indexes for table `crm_employee`
2964--
2965ALTER TABLE `crm_employee`
2966  ADD PRIMARY KEY (`employee_id`);
2967
2968--
2969-- Indexes for table `crm_faq`
2970--
2971ALTER TABLE `crm_faq`
2972  ADD PRIMARY KEY (`faq_id`);
2973
2974--
2975-- Indexes for table `crm_groups`
2976--
2977ALTER TABLE `crm_groups`
2978  ADD PRIMARY KEY (`group_id`),
2979  ADD KEY `group_manager_id` (`group_manager_id`);
2980
2981--
2982-- Indexes for table `crm_industry`
2983--
2984ALTER TABLE `crm_industry`
2985  ADD PRIMARY KEY (`industry_id`);
2986
2987--
2988-- Indexes for table `crm_invoices`
2989--
2990ALTER TABLE `crm_invoices`
2991  ADD PRIMARY KEY (`invoice_id`),
2992  ADD KEY `fk_crm_invoices_crm_terms1_idx` (`term_id`),
2993  ADD KEY `fk_crm_invoices_crm_companies1_idx` (`company_id`);
2994
2995--
2996-- Indexes for table `crm_invoices_products`
2997--
2998ALTER TABLE `crm_invoices_products`
2999  ADD PRIMARY KEY (`invoice_detail_id`,`product_id`),
3000  ADD KEY `fk_crm_invoice_details_has_crm_products_crm_products1_idx` (`product_id`),
3001  ADD KEY `fk_crm_invoice_details_has_crm_products_crm_invoice_details_idx` (`invoice_detail_id`),
3002  ADD KEY `invoice_detail_id` (`invoice_detail_id`);
3003
3004--
3005-- Indexes for table `crm_invoice_details`
3006--
3007ALTER TABLE `crm_invoice_details`
3008  ADD PRIMARY KEY (`invoice_detail_id`),
3009  ADD KEY `fk_crm_invoice_details_crm_invoices1_idx` (`invoice_id`);
3010
3011--
3012-- Indexes for table `crm_leads`
3013--
3014ALTER TABLE `crm_leads`
3015  ADD PRIMARY KEY (`lead_id`),
3016  ADD KEY `fk_crm_leads_crm_lead_status1_idx` (`lead_label`),
3017  ADD KEY `fk_crm_leads_crm_groups1_idx` (`lead_groups`),
3018  ADD KEY `fk_crm_leads_crm_peoples1_idx` (`people_id`);
3019
3020--
3021-- Indexes for table `crm_lead_label`
3022--
3023ALTER TABLE `crm_lead_label`
3024  ADD PRIMARY KEY (`lead_label_id`);
3025
3026--
3027-- Indexes for table `crm_modules`
3028--
3029ALTER TABLE `crm_modules`
3030  ADD PRIMARY KEY (`module_id`);
3031
3032--
3033-- Indexes for table `crm_news`
3034--
3035ALTER TABLE `crm_news`
3036  ADD PRIMARY KEY (`news_id`);
3037
3038--
3039-- Indexes for table `crm_opportunities`
3040--
3041ALTER TABLE `crm_opportunities`
3042  ADD PRIMARY KEY (`opportunity_id`),
3043  ADD KEY `fk_crm_opportunities_crm_opportunity_stage1_idx` (`opportunity_stage_id`),
3044  ADD KEY `fk_crm_opportunities_crm_companies1_idx` (`company_id`),
3045  ADD KEY `fk_crm_opportunities_crm_campaigns1_idx` (`opportunity_campaign_id`);
3046
3047--
3048-- Indexes for table `crm_opportunities_contacts`
3049--
3050ALTER TABLE `crm_opportunities_contacts`
3051  ADD PRIMARY KEY (`opportunity_id`,`contact_id`),
3052  ADD KEY `fk_crm_opportunities_has_crm_contacts_crm_contacts1_idx` (`contact_id`),
3053  ADD KEY `fk_crm_opportunities_has_crm_contacts_crm_opportunities1_idx` (`opportunity_id`);
3054
3055--
3056-- Indexes for table `crm_opportunities_products`
3057--
3058ALTER TABLE `crm_opportunities_products`
3059  ADD PRIMARY KEY (`opportunity_id`,`product_id`),
3060  ADD KEY `fk_crm_opportunities_has_crm_products_crm_products1_idx` (`product_id`),
3061  ADD KEY `fk_crm_opportunities_has_crm_products_crm_opportunities1_idx` (`opportunity_id`);
3062
3063--
3064-- Indexes for table `crm_opportunity_stage`
3065--
3066ALTER TABLE `crm_opportunity_stage`
3067  ADD PRIMARY KEY (`opportunity_stage_id`);
3068
3069--
3070-- Indexes for table `crm_payment`
3071--
3072ALTER TABLE `crm_payment`
3073  ADD PRIMARY KEY (`payment_id`),
3074  ADD KEY `invoice_id` (`invoice_detail_id`),
3075  ADD KEY `payment_method_id` (`payment_method_id`);
3076
3077--
3078-- Indexes for table `crm_payment_methods`
3079--
3080ALTER TABLE `crm_payment_methods`
3081  ADD PRIMARY KEY (`payment_method_id`);
3082
3083--
3084-- Indexes for table `crm_peoples`
3085--
3086ALTER TABLE `crm_peoples`
3087  ADD PRIMARY KEY (`people_id`),
3088  ADD KEY `fk_peoples_crm_industry1_idx` (`people_industry`),
3089  ADD KEY `fk_peoples_crm_source1_idx` (`people_source`);
3090
3091--
3092-- Indexes for table `crm_products`
3093--
3094ALTER TABLE `crm_products`
3095  ADD PRIMARY KEY (`product_id`),
3096  ADD KEY `crm_products_crm_product_categories_category_id_fk` (`product_category_id`);
3097
3098--
3099-- Indexes for table `crm_products_campaigns`
3100--
3101ALTER TABLE `crm_products_campaigns`
3102  ADD PRIMARY KEY (`product_id`,`campaign_id`),
3103  ADD KEY `fk_crm_products_has_crm_campaigns_crm_campaigns1_idx` (`campaign_id`),
3104  ADD KEY `fk_crm_products_has_crm_campaigns_crm_products1_idx` (`product_id`);
3105
3106--
3107-- Indexes for table `crm_product_categories`
3108--
3109ALTER TABLE `crm_product_categories`
3110  ADD PRIMARY KEY (`category_id`);
3111
3112--
3113-- Indexes for table `crm_roles`
3114--
3115ALTER TABLE `crm_roles`
3116  ADD PRIMARY KEY (`role_id`);
3117
3118--
3119-- Indexes for table `crm_roles_modules`
3120--
3121ALTER TABLE `crm_roles_modules`
3122  ADD PRIMARY KEY (`crm_roles_role_id`,`crm_modules_module_id`),
3123  ADD KEY `fk_crm_roles_has_crm_modules_crm_modules1_idx` (`crm_modules_module_id`),
3124  ADD KEY `fk_crm_roles_has_crm_modules_crm_roles1_idx` (`crm_roles_role_id`);
3125
3126--
3127-- Indexes for table `crm_setup_company`
3128--
3129ALTER TABLE `crm_setup_company`
3130  ADD PRIMARY KEY (`company_id`);
3131
3132--
3133-- Indexes for table `crm_source`
3134--
3135ALTER TABLE `crm_source`
3136  ADD PRIMARY KEY (`source_id`);
3137
3138--
3139-- Indexes for table `crm_terms`
3140--
3141ALTER TABLE `crm_terms`
3142  ADD PRIMARY KEY (`term_id`);
3143
3144--
3145-- Indexes for table `crm_tickets`
3146--
3147ALTER TABLE `crm_tickets`
3148  ADD PRIMARY KEY (`ticket_id`),
3149  ADD KEY `ticket_customer_id` (`ticket_customer_id`),
3150  ADD KEY `ticket_type_id` (`ticket_type_id`),
3151  ADD KEY `ticket_status_id` (`ticket_status_id`);
3152
3153--
3154-- Indexes for table `crm_tickets_employee`
3155--
3156ALTER TABLE `crm_tickets_employee`
3157  ADD KEY `ticket_id` (`ticket_id`),
3158  ADD KEY `employee_id` (`employee_id`);
3159
3160--
3161-- Indexes for table `crm_ticket_status`
3162--
3163ALTER TABLE `crm_ticket_status`
3164  ADD PRIMARY KEY (`ticket_status_id`);
3165
3166--
3167-- Indexes for table `crm_ticket_type`
3168--
3169ALTER TABLE `crm_ticket_type`
3170  ADD PRIMARY KEY (`ticket_type_id`);
3171
3172--
3173-- Indexes for table `crm_users`
3174--
3175ALTER TABLE `crm_users`
3176  ADD PRIMARY KEY (`user_id`),
3177  ADD UNIQUE KEY `crm_users_user_email_uindex` (`user_email`),
3178  ADD KEY `crm_users_crm_groups_group_id_fk` (`user_group_id`),
3179  ADD KEY `fk_crm_users_crm_roles1_idx` (`user_role_id`);
3180
3181--
3182-- Indexes for table `crm_users_companies`
3183--
3184ALTER TABLE `crm_users_companies`
3185  ADD PRIMARY KEY (`company_id`,`user_id`),
3186  ADD KEY `fk_crm_company_has_crm_users_crm_users1_idx` (`user_id`),
3187  ADD KEY `fk_crm_company_has_crm_users_crm_company1_idx` (`company_id`);
3188
3189--
3190-- Indexes for table `crm_users_contacts`
3191--
3192ALTER TABLE `crm_users_contacts`
3193  ADD PRIMARY KEY (`contact_id`,`user_id`),
3194  ADD KEY `fk_crm_users_has_crm_customer_crm_customer1_idx` (`contact_id`),
3195  ADD KEY `fk_crm_users_has_crm_customer_crm_users1_idx` (`user_id`);
3196
3197--
3198-- Indexes for table `crm_users_leads`
3199--
3200ALTER TABLE `crm_users_leads`
3201  ADD PRIMARY KEY (`lead_id`,`user_id`),
3202  ADD KEY `fk_crm_users_has_crm_leads_crm_leads1_idx` (`lead_id`),
3203  ADD KEY `fk_crm_users_has_crm_leads_crm_users1_idx` (`user_id`);
3204
3205--
3206-- Indexes for table `crm_users_opportunities`
3207--
3208ALTER TABLE `crm_users_opportunities`
3209  ADD KEY `user_id` (`user_id`),
3210  ADD KEY `opportunity_id` (`opportunity_id`);
3211
3212--
3213-- Indexes for table `invoice_control`
3214--
3215ALTER TABLE `invoice_control`
3216  ADD PRIMARY KEY (`invoice_id`),
3217  ADD KEY `invoice_id` (`invoice_id`);
3218
3219--
3220-- Indexes for table `lead_calls`
3221--
3222ALTER TABLE `lead_calls`
3223  ADD PRIMARY KEY (`lead_call_id`),
3224  ADD KEY `fk_lead_calls_crm_leads1_idx` (`lead_id`);
3225
3226--
3227-- Indexes for table `lead_files`
3228--
3229ALTER TABLE `lead_files`
3230  ADD PRIMARY KEY (`lead_files_id`),
3231  ADD KEY `fk_lead_files_crm_leads1_idx` (`lead_id`);
3232
3233--
3234-- Indexes for table `lead_meeting`
3235--
3236ALTER TABLE `lead_meeting`
3237  ADD PRIMARY KEY (`lead_meeting_id`),
3238  ADD KEY `fk_lead_meeting_crm_leads1_idx` (`lead_id`);
3239
3240--
3241-- Indexes for table `lead_meeting_users`
3242--
3243ALTER TABLE `lead_meeting_users`
3244  ADD PRIMARY KEY (`lead_meeting_id`,`lead_user_id`),
3245  ADD KEY `fk_lead_meeting_has_crm_users_crm_users1_idx` (`lead_user_id`),
3246  ADD KEY `fk_lead_meeting_has_crm_users_lead_meeting1_idx` (`lead_meeting_id`);
3247
3248--
3249-- Indexes for table `lead_notes`
3250--
3251ALTER TABLE `lead_notes`
3252  ADD PRIMARY KEY (`lead_note_id`),
3253  ADD KEY `fk_lead_notes_crm_leads1_idx` (`lead_id`);
3254
3255--
3256-- Indexes for table `lead_tasks`
3257--
3258ALTER TABLE `lead_tasks`
3259  ADD PRIMARY KEY (`lead_task_id`),
3260  ADD KEY `fk_lead_task_crm_leads1_idx` (`lead_id`);
3261
3262--
3263-- Indexes for table `opportunity_calls`
3264--
3265ALTER TABLE `opportunity_calls`
3266  ADD PRIMARY KEY (`opportunity_call_id`),
3267  ADD KEY `fk_opportunity_calls_crm_opportunities1_idx` (`opportunity_id`);
3268
3269--
3270-- Indexes for table `opportunity_files`
3271--
3272ALTER TABLE `opportunity_files`
3273  ADD PRIMARY KEY (`opportunity_files_id`),
3274  ADD KEY `fk_opportunity_files_crm_opportunities1_idx` (`opportunity_id`);
3275
3276--
3277-- Indexes for table `opportunity_meeting`
3278--
3279ALTER TABLE `opportunity_meeting`
3280  ADD PRIMARY KEY (`opportunity_meeting_id`),
3281  ADD KEY `fk_opportunity_meeting_crm_opportunities1_idx` (`opportunity_id`);
3282
3283--
3284-- Indexes for table `opportunity_meeting_users`
3285--
3286ALTER TABLE `opportunity_meeting_users`
3287  ADD PRIMARY KEY (`opportunity_meeting_id`,`opportunity_user_id`),
3288  ADD KEY `fk_opportunity_meeting_has_crm_users_crm_users1_idx` (`opportunity_user_id`),
3289  ADD KEY `fk_opportunity_meeting_has_crm_users_opportunity_meeting1_idx` (`opportunity_meeting_id`);
3290
3291--
3292-- Indexes for table `opportunity_notes`
3293--
3294ALTER TABLE `opportunity_notes`
3295  ADD PRIMARY KEY (`opportunity_note_id`),
3296  ADD KEY `fk_opportunity_notes_crm_opportunities1_idx` (`opportunity_id`);
3297
3298--
3299-- Indexes for table `opportunity_tasks`
3300--
3301ALTER TABLE `opportunity_tasks`
3302  ADD PRIMARY KEY (`opportunity_task_id`),
3303  ADD KEY `fk_opportunity_tasks_crm_opportunities1_idx` (`opportunity_id`);
3304
3305--
3306-- AUTO_INCREMENT for dumped tables
3307--
3308
3309--
3310-- AUTO_INCREMENT for table `coba`
3311--
3312ALTER TABLE `coba`
3313  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
3314
3315--
3316-- AUTO_INCREMENT for table `company_calls`
3317--
3318ALTER TABLE `company_calls`
3319  MODIFY `company_call_id` int(11) NOT NULL AUTO_INCREMENT;
3320
3321--
3322-- AUTO_INCREMENT for table `company_files`
3323--
3324ALTER TABLE `company_files`
3325  MODIFY `company_files_id` int(11) NOT NULL AUTO_INCREMENT;
3326
3327--
3328-- AUTO_INCREMENT for table `company_meeting`
3329--
3330ALTER TABLE `company_meeting`
3331  MODIFY `company_meeting_id` int(11) NOT NULL AUTO_INCREMENT;
3332
3333--
3334-- AUTO_INCREMENT for table `company_notes`
3335--
3336ALTER TABLE `company_notes`
3337  MODIFY `company_note_id` int(11) NOT NULL AUTO_INCREMENT;
3338
3339--
3340-- AUTO_INCREMENT for table `company_tasks`
3341--
3342ALTER TABLE `company_tasks`
3343  MODIFY `company_task_id` int(11) NOT NULL AUTO_INCREMENT;
3344
3345--
3346-- AUTO_INCREMENT for table `contact_calls`
3347--
3348ALTER TABLE `contact_calls`
3349  MODIFY `contact_call_id` int(11) NOT NULL AUTO_INCREMENT;
3350
3351--
3352-- AUTO_INCREMENT for table `contact_files`
3353--
3354ALTER TABLE `contact_files`
3355  MODIFY `contact_files_id` int(11) NOT NULL AUTO_INCREMENT;
3356
3357--
3358-- AUTO_INCREMENT for table `contact_meeting`
3359--
3360ALTER TABLE `contact_meeting`
3361  MODIFY `contact_meeting_id` int(11) NOT NULL AUTO_INCREMENT;
3362
3363--
3364-- AUTO_INCREMENT for table `contact_notes`
3365--
3366ALTER TABLE `contact_notes`
3367  MODIFY `contact_note_id` int(11) NOT NULL AUTO_INCREMENT;
3368
3369--
3370-- AUTO_INCREMENT for table `contact_tasks`
3371--
3372ALTER TABLE `contact_tasks`
3373  MODIFY `contact_task_id` int(11) NOT NULL AUTO_INCREMENT;
3374
3375--
3376-- AUTO_INCREMENT for table `crm_campaigns`
3377--
3378ALTER TABLE `crm_campaigns`
3379  MODIFY `campaign_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;
3380
3381--
3382-- AUTO_INCREMENT for table `crm_companies`
3383--
3384ALTER TABLE `crm_companies`
3385  MODIFY `company_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=15;
3386
3387--
3388-- AUTO_INCREMENT for table `crm_contacts`
3389--
3390ALTER TABLE `crm_contacts`
3391  MODIFY `contact_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=21;
3392
3393--
3394-- AUTO_INCREMENT for table `crm_employee`
3395--
3396ALTER TABLE `crm_employee`
3397  MODIFY `employee_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;
3398
3399--
3400-- AUTO_INCREMENT for table `crm_faq`
3401--
3402ALTER TABLE `crm_faq`
3403  MODIFY `faq_id` int(11) NOT NULL AUTO_INCREMENT;
3404
3405--
3406-- AUTO_INCREMENT for table `crm_groups`
3407--
3408ALTER TABLE `crm_groups`
3409  MODIFY `group_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=10;
3410
3411--
3412-- AUTO_INCREMENT for table `crm_industry`
3413--
3414ALTER TABLE `crm_industry`
3415  MODIFY `industry_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;
3416
3417--
3418-- AUTO_INCREMENT for table `crm_invoices`
3419--
3420ALTER TABLE `crm_invoices`
3421  MODIFY `invoice_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=15;
3422
3423--
3424-- AUTO_INCREMENT for table `crm_invoice_details`
3425--
3426ALTER TABLE `crm_invoice_details`
3427  MODIFY `invoice_detail_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=17;
3428
3429--
3430-- AUTO_INCREMENT for table `crm_leads`
3431--
3432ALTER TABLE `crm_leads`
3433  MODIFY `lead_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=21;
3434
3435--
3436-- AUTO_INCREMENT for table `crm_lead_label`
3437--
3438ALTER TABLE `crm_lead_label`
3439  MODIFY `lead_label_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;
3440
3441--
3442-- AUTO_INCREMENT for table `crm_modules`
3443--
3444ALTER TABLE `crm_modules`
3445  MODIFY `module_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=16;
3446
3447--
3448-- AUTO_INCREMENT for table `crm_news`
3449--
3450ALTER TABLE `crm_news`
3451  MODIFY `news_id` int(11) NOT NULL AUTO_INCREMENT;
3452
3453--
3454-- AUTO_INCREMENT for table `crm_opportunities`
3455--
3456ALTER TABLE `crm_opportunities`
3457  MODIFY `opportunity_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=14;
3458
3459--
3460-- AUTO_INCREMENT for table `crm_opportunity_stage`
3461--
3462ALTER TABLE `crm_opportunity_stage`
3463  MODIFY `opportunity_stage_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;
3464
3465--
3466-- AUTO_INCREMENT for table `crm_payment`
3467--
3468ALTER TABLE `crm_payment`
3469  MODIFY `payment_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
3470
3471--
3472-- AUTO_INCREMENT for table `crm_payment_methods`
3473--
3474ALTER TABLE `crm_payment_methods`
3475  MODIFY `payment_method_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
3476
3477--
3478-- AUTO_INCREMENT for table `crm_peoples`
3479--
3480ALTER TABLE `crm_peoples`
3481  MODIFY `people_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=29;
3482
3483--
3484-- AUTO_INCREMENT for table `crm_products`
3485--
3486ALTER TABLE `crm_products`
3487  MODIFY `product_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=15;
3488
3489--
3490-- AUTO_INCREMENT for table `crm_product_categories`
3491--
3492ALTER TABLE `crm_product_categories`
3493  MODIFY `category_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;
3494
3495--
3496-- AUTO_INCREMENT for table `crm_roles`
3497--
3498ALTER TABLE `crm_roles`
3499  MODIFY `role_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'User Administrator Hard Code All access module', AUTO_INCREMENT=8;
3500
3501--
3502-- AUTO_INCREMENT for table `crm_setup_company`
3503--
3504ALTER TABLE `crm_setup_company`
3505  MODIFY `company_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;
3506
3507--
3508-- AUTO_INCREMENT for table `crm_source`
3509--
3510ALTER TABLE `crm_source`
3511  MODIFY `source_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;
3512
3513--
3514-- AUTO_INCREMENT for table `crm_terms`
3515--
3516ALTER TABLE `crm_terms`
3517  MODIFY `term_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
3518
3519--
3520-- AUTO_INCREMENT for table `crm_tickets`
3521--
3522ALTER TABLE `crm_tickets`
3523  MODIFY `ticket_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;
3524
3525--
3526-- AUTO_INCREMENT for table `crm_ticket_status`
3527--
3528ALTER TABLE `crm_ticket_status`
3529  MODIFY `ticket_status_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
3530
3531--
3532-- AUTO_INCREMENT for table `crm_ticket_type`
3533--
3534ALTER TABLE `crm_ticket_type`
3535  MODIFY `ticket_type_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
3536
3537--
3538-- AUTO_INCREMENT for table `crm_users`
3539--
3540ALTER TABLE `crm_users`
3541  MODIFY `user_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=23;
3542
3543--
3544-- AUTO_INCREMENT for table `lead_calls`
3545--
3546ALTER TABLE `lead_calls`
3547  MODIFY `lead_call_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;
3548
3549--
3550-- AUTO_INCREMENT for table `lead_files`
3551--
3552ALTER TABLE `lead_files`
3553  MODIFY `lead_files_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;
3554
3555--
3556-- AUTO_INCREMENT for table `lead_meeting`
3557--
3558ALTER TABLE `lead_meeting`
3559  MODIFY `lead_meeting_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
3560
3561--
3562-- AUTO_INCREMENT for table `lead_notes`
3563--
3564ALTER TABLE `lead_notes`
3565  MODIFY `lead_note_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;
3566
3567--
3568-- AUTO_INCREMENT for table `lead_tasks`
3569--
3570ALTER TABLE `lead_tasks`
3571  MODIFY `lead_task_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=15;
3572
3573--
3574-- AUTO_INCREMENT for table `opportunity_calls`
3575--
3576ALTER TABLE `opportunity_calls`
3577  MODIFY `opportunity_call_id` int(11) NOT NULL AUTO_INCREMENT;
3578
3579--
3580-- AUTO_INCREMENT for table `opportunity_files`
3581--
3582ALTER TABLE `opportunity_files`
3583  MODIFY `opportunity_files_id` int(11) NOT NULL AUTO_INCREMENT;
3584
3585--
3586-- AUTO_INCREMENT for table `opportunity_meeting`
3587--
3588ALTER TABLE `opportunity_meeting`
3589  MODIFY `opportunity_meeting_id` int(11) NOT NULL AUTO_INCREMENT;
3590
3591--
3592-- AUTO_INCREMENT for table `opportunity_notes`
3593--
3594ALTER TABLE `opportunity_notes`
3595  MODIFY `opportunity_note_id` int(11) NOT NULL AUTO_INCREMENT;
3596
3597--
3598-- AUTO_INCREMENT for table `opportunity_tasks`
3599--
3600ALTER TABLE `opportunity_tasks`
3601  MODIFY `opportunity_task_id` int(11) NOT NULL AUTO_INCREMENT;
3602
3603--
3604-- Constraints for dumped tables
3605--
3606
3607--
3608-- Constraints for table `company_calls`
3609--
3610ALTER TABLE `company_calls`
3611  ADD CONSTRAINT `fk_company_calls_crm_company1` FOREIGN KEY (`company_id`) REFERENCES `crm_companies` (`company_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
3612
3613--
3614-- Constraints for table `company_files`
3615--
3616ALTER TABLE `company_files`
3617  ADD CONSTRAINT `fk_company_files_crm_company1` FOREIGN KEY (`company_id`) REFERENCES `crm_companies` (`company_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
3618
3619--
3620-- Constraints for table `company_meeting`
3621--
3622ALTER TABLE `company_meeting`
3623  ADD CONSTRAINT `fk_company_meeting_crm_company1` FOREIGN KEY (`company_id`) REFERENCES `crm_companies` (`company_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
3624
3625--
3626-- Constraints for table `company_meeting_users`
3627--
3628ALTER TABLE `company_meeting_users`
3629  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,
3630  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;
3631
3632--
3633-- Constraints for table `company_notes`
3634--
3635ALTER TABLE `company_notes`
3636  ADD CONSTRAINT `fk_company_notes_crm_company1` FOREIGN KEY (`company_id`) REFERENCES `crm_companies` (`company_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
3637
3638--
3639-- Constraints for table `company_tasks`
3640--
3641ALTER TABLE `company_tasks`
3642  ADD CONSTRAINT `fk_company_tasks_crm_company1` FOREIGN KEY (`company_id`) REFERENCES `crm_companies` (`company_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
3643
3644--
3645-- Constraints for table `contact_calls`
3646--
3647ALTER TABLE `contact_calls`
3648  ADD CONSTRAINT `fk_customer_calls_crm_customers1` FOREIGN KEY (`contact_id`) REFERENCES `crm_contacts` (`contact_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
3649
3650--
3651-- Constraints for table `contact_files`
3652--
3653ALTER TABLE `contact_files`
3654  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;
3655
3656--
3657-- Constraints for table `contact_meeting`
3658--
3659ALTER TABLE `contact_meeting`
3660  ADD CONSTRAINT `fk_customer_meeting_crm_customers1` FOREIGN KEY (`contact_id`) REFERENCES `crm_contacts` (`contact_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
3661
3662--
3663-- Constraints for table `contact_meeting_users`
3664--
3665ALTER TABLE `contact_meeting_users`
3666  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,
3667  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;
3668
3669--
3670-- Constraints for table `contact_notes`
3671--
3672ALTER TABLE `contact_notes`
3673  ADD CONSTRAINT `fk_customer_notes_crm_customers1` FOREIGN KEY (`contact_id`) REFERENCES `crm_contacts` (`contact_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
3674
3675--
3676-- Constraints for table `contact_tasks`
3677--
3678ALTER TABLE `contact_tasks`
3679  ADD CONSTRAINT `fk_customer_tasks_crm_customers1` FOREIGN KEY (`contact_id`) REFERENCES `crm_contacts` (`contact_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
3680
3681--
3682-- Constraints for table `crm_companies`
3683--
3684ALTER TABLE `crm_companies`
3685  ADD CONSTRAINT `fk_crm_companies_crm_companies1` FOREIGN KEY (`company_parent`) REFERENCES `crm_companies` (`company_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
3686  ADD CONSTRAINT `fk_crm_company_crm_groups1` FOREIGN KEY (`company_groups`) REFERENCES `crm_groups` (`group_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
3687  ADD CONSTRAINT `fk_crm_company_crm_industry1` FOREIGN KEY (`company_industry`) REFERENCES `crm_industry` (`industry_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
3688
3689--
3690-- Constraints for table `crm_companies_products`
3691--
3692ALTER TABLE `crm_companies_products`
3693  ADD CONSTRAINT `crm_companies_products_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `crm_companies` (`company_id`),
3694  ADD CONSTRAINT `crm_companies_products_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `crm_products` (`product_id`);
3695
3696--
3697-- Constraints for table `crm_contacts`
3698--
3699ALTER TABLE `crm_contacts`
3700  ADD CONSTRAINT `fk_crm_contacts_crm_peoples1` FOREIGN KEY (`people_id`) REFERENCES `crm_peoples` (`people_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
3701  ADD CONSTRAINT `fk_crm_customer_crm_company1` FOREIGN KEY (`company_id`) REFERENCES `crm_companies` (`company_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
3702  ADD CONSTRAINT `fk_crm_customer_crm_groups1` FOREIGN KEY (`contact_groups`) REFERENCES `crm_groups` (`group_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
3703
3704--
3705-- Constraints for table `crm_invoices`
3706--
3707ALTER TABLE `crm_invoices`
3708  ADD CONSTRAINT `fk_crm_invoices_crm_companies1` FOREIGN KEY (`company_id`) REFERENCES `crm_companies` (`company_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
3709  ADD CONSTRAINT `fk_crm_invoices_crm_terms1` FOREIGN KEY (`term_id`) REFERENCES `crm_terms` (`term_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
3710
3711--
3712-- Constraints for table `crm_invoices_products`
3713--
3714ALTER TABLE `crm_invoices_products`
3715  ADD CONSTRAINT `fk_crm_invoice_details_has_crm_products_crm_invoice_detail` FOREIGN KEY (`invoice_detail_id`) REFERENCES `crm_invoice_details` (`invoice_detail_id`),
3716  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;
3717
3718--
3719-- Constraints for table `crm_invoice_details`
3720--
3721ALTER TABLE `crm_invoice_details`
3722  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;
3723
3724--
3725-- Constraints for table `crm_leads`
3726--
3727ALTER TABLE `crm_leads`
3728  ADD CONSTRAINT `fk_crm_leads_crm_groups1` FOREIGN KEY (`lead_groups`) REFERENCES `crm_groups` (`group_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
3729  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,
3730  ADD CONSTRAINT `fk_crm_leads_crm_peoples1` FOREIGN KEY (`people_id`) REFERENCES `crm_peoples` (`people_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
3731
3732--
3733-- Constraints for table `crm_opportunities`
3734--
3735ALTER TABLE `crm_opportunities`
3736  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,
3737  ADD CONSTRAINT `fk_crm_opportunities_crm_companies1` FOREIGN KEY (`company_id`) REFERENCES `crm_companies` (`company_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
3738  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;
3739
3740--
3741-- Constraints for table `crm_opportunities_contacts`
3742--
3743ALTER TABLE `crm_opportunities_contacts`
3744  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,
3745  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;
3746
3747--
3748-- Constraints for table `crm_opportunities_products`
3749--
3750ALTER TABLE `crm_opportunities_products`
3751  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,
3752  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;
3753
3754--
3755-- Constraints for table `crm_payment`
3756--
3757ALTER TABLE `crm_payment`
3758  ADD CONSTRAINT `crm_payment_ibfk_1` FOREIGN KEY (`invoice_detail_id`) REFERENCES `crm_invoice_details` (`invoice_detail_id`),
3759  ADD CONSTRAINT `crm_payment_ibfk_2` FOREIGN KEY (`payment_method_id`) REFERENCES `crm_payment_methods` (`payment_method_id`);
3760
3761--
3762-- Constraints for table `crm_peoples`
3763--
3764ALTER TABLE `crm_peoples`
3765  ADD CONSTRAINT `fk_peoples_crm_industry1` FOREIGN KEY (`people_industry`) REFERENCES `crm_industry` (`industry_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
3766  ADD CONSTRAINT `fk_peoples_crm_source1` FOREIGN KEY (`people_source`) REFERENCES `crm_source` (`source_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
3767
3768--
3769-- Constraints for table `crm_products`
3770--
3771ALTER TABLE `crm_products`
3772  ADD CONSTRAINT `crm_products_crm_product_categories_category_id_fk` FOREIGN KEY (`product_category_id`) REFERENCES `crm_product_categories` (`category_id`);
3773
3774--
3775-- Constraints for table `crm_products_campaigns`
3776--
3777ALTER TABLE `crm_products_campaigns`
3778  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,
3779  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;
3780
3781--
3782-- Constraints for table `crm_roles_modules`
3783--
3784ALTER TABLE `crm_roles_modules`
3785  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,
3786  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;
3787
3788--
3789-- Constraints for table `crm_tickets`
3790--
3791ALTER TABLE `crm_tickets`
3792  ADD CONSTRAINT `crm_tickets_ibfk_1` FOREIGN KEY (`ticket_customer_id`) REFERENCES `crm_companies` (`company_id`),
3793  ADD CONSTRAINT `crm_tickets_ibfk_2` FOREIGN KEY (`ticket_type_id`) REFERENCES `crm_ticket_type` (`ticket_type_id`),
3794  ADD CONSTRAINT `crm_tickets_ibfk_3` FOREIGN KEY (`ticket_status_id`) REFERENCES `crm_ticket_status` (`ticket_status_id`);
3795
3796--
3797-- Constraints for table `crm_tickets_employee`
3798--
3799ALTER TABLE `crm_tickets_employee`
3800  ADD CONSTRAINT `crm_tickets_employee_ibfk_1` FOREIGN KEY (`ticket_id`) REFERENCES `crm_tickets` (`ticket_id`),
3801  ADD CONSTRAINT `crm_tickets_employee_ibfk_2` FOREIGN KEY (`employee_id`) REFERENCES `crm_employee` (`employee_id`);
3802
3803--
3804-- Constraints for table `crm_users`
3805--
3806ALTER TABLE `crm_users`
3807  ADD CONSTRAINT `crm_users_crm_groups_group_id_fk` FOREIGN KEY (`user_group_id`) REFERENCES `crm_groups` (`group_id`),
3808  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;
3809
3810--
3811-- Constraints for table `crm_users_companies`
3812--
3813ALTER TABLE `crm_users_companies`
3814  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,
3815  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;
3816
3817--
3818-- Constraints for table `crm_users_contacts`
3819--
3820ALTER TABLE `crm_users_contacts`
3821  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,
3822  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;
3823
3824--
3825-- Constraints for table `crm_users_leads`
3826--
3827ALTER TABLE `crm_users_leads`
3828  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,
3829  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;
3830
3831--
3832-- Constraints for table `crm_users_opportunities`
3833--
3834ALTER TABLE `crm_users_opportunities`
3835  ADD CONSTRAINT `crm_users_opportunities_ibfk_1` FOREIGN KEY (`opportunity_id`) REFERENCES `crm_opportunities` (`opportunity_id`) ON DELETE SET NULL ON UPDATE CASCADE,
3836  ADD CONSTRAINT `crm_users_opportunities_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `crm_users` (`user_id`) ON DELETE SET NULL ON UPDATE CASCADE;
3837
3838--
3839-- Constraints for table `invoice_control`
3840--
3841ALTER TABLE `invoice_control`
3842  ADD CONSTRAINT `invoice_control_ibfk_1` FOREIGN KEY (`invoice_id`) REFERENCES `crm_invoices` (`invoice_id`);
3843
3844--
3845-- Constraints for table `lead_calls`
3846--
3847ALTER TABLE `lead_calls`
3848  ADD CONSTRAINT `fk_lead_calls_crm_leads1` FOREIGN KEY (`lead_id`) REFERENCES `crm_leads` (`lead_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
3849
3850--
3851-- Constraints for table `lead_files`
3852--
3853ALTER TABLE `lead_files`
3854  ADD CONSTRAINT `fk_lead_files_crm_leads1` FOREIGN KEY (`lead_id`) REFERENCES `crm_leads` (`lead_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
3855
3856--
3857-- Constraints for table `lead_meeting`
3858--
3859ALTER TABLE `lead_meeting`
3860  ADD CONSTRAINT `fk_lead_meeting_crm_leads1` FOREIGN KEY (`lead_id`) REFERENCES `crm_leads` (`lead_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
3861
3862--
3863-- Constraints for table `lead_meeting_users`
3864--
3865ALTER TABLE `lead_meeting_users`
3866  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,
3867  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;
3868
3869--
3870-- Constraints for table `lead_notes`
3871--
3872ALTER TABLE `lead_notes`
3873  ADD CONSTRAINT `fk_lead_notes_crm_leads1` FOREIGN KEY (`lead_id`) REFERENCES `crm_leads` (`lead_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
3874
3875--
3876-- Constraints for table `lead_tasks`
3877--
3878ALTER TABLE `lead_tasks`
3879  ADD CONSTRAINT `fk_lead_task_crm_leads1` FOREIGN KEY (`lead_id`) REFERENCES `crm_leads` (`lead_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
3880
3881--
3882-- Constraints for table `opportunity_calls`
3883--
3884ALTER TABLE `opportunity_calls`
3885  ADD CONSTRAINT `fk_opportunity_calls_crm_opportunities1` FOREIGN KEY (`opportunity_id`) REFERENCES `crm_opportunities` (`opportunity_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
3886
3887--
3888-- Constraints for table `opportunity_files`
3889--
3890ALTER TABLE `opportunity_files`
3891  ADD CONSTRAINT `fk_opportunity_files_crm_opportunities1` FOREIGN KEY (`opportunity_id`) REFERENCES `crm_opportunities` (`opportunity_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
3892
3893--
3894-- Constraints for table `opportunity_meeting`
3895--
3896ALTER TABLE `opportunity_meeting`
3897  ADD CONSTRAINT `fk_opportunity_meeting_crm_opportunities1` FOREIGN KEY (`opportunity_id`) REFERENCES `crm_opportunities` (`opportunity_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
3898
3899--
3900-- Constraints for table `opportunity_meeting_users`
3901--
3902ALTER TABLE `opportunity_meeting_users`
3903  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,
3904  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;
3905
3906--
3907-- Constraints for table `opportunity_notes`
3908--
3909ALTER TABLE `opportunity_notes`
3910  ADD CONSTRAINT `fk_opportunity_notes_crm_opportunities1` FOREIGN KEY (`opportunity_id`) REFERENCES `crm_opportunities` (`opportunity_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
3911
3912--
3913-- Constraints for table `opportunity_tasks`
3914--
3915ALTER TABLE `opportunity_tasks`
3916  ADD CONSTRAINT `fk_opportunity_tasks_crm_opportunities1` FOREIGN KEY (`opportunity_id`) REFERENCES `crm_opportunities` (`opportunity_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
3917
3918DELIMITER $$
3919--
3920-- Events
3921--
3922DROP EVENT `test_event_03`$$
3923CREATE DEFINER=`root`@`localhost` EVENT `test_event_03` ON SCHEDULE EVERY 1 DAY STARTS '2017-09-07 02:53:40' ON COMPLETION NOT PRESERVE ENABLE DO CALL sp_recuring_invoice$$
3924
3925DELIMITER ;
3926COMMIT;
3927
3928/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
3929/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
3930/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;