mst_marketing_campaign
mst_email_marketing_contact
mst_marketing_campaigns_contacts
trn_email_tracking_history
mst_email_template
mst_smtp_details
mst_email_queue

ALTER TABLE `mst_marketing_campaigns_contacts` ADD `is_pending` VARCHAR(25) NULL DEFAULT NULL AFTER `last_name`;
ALTER TABLE `mst_smtp_details` ADD `name` VARCHAR(80) NULL DEFAULT NULL AFTER `email`;
UPDATE `mst_smtp_details` SET `smtp_password` = 'YCRM##456' WHERE `mst_smtp_details`.`id` = 1;
ALTER TABLE `mst_product` ADD `sub_service_id` INT(11) UNSIGNED NOT NULL AFTER `user_id`; 

--
-- Table structure for table `mst_question`
--
 
CREATE TABLE `mst_question` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `product_id` bigint(20) UNSIGNED NOT NULL,
  `sub_product_id` bigint(20) UNSIGNED NOT NULL,
  `qname` varchar(255) NOT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO `mst_dropdown_option_values` (`option_type_id`, `module_id`, `parent_id`, `option_value`, `position`, `status`, `created_on`, `updated_on`) VALUES
(69, 20, '[]', 'Draft', NULL, 'active', '2024-07-30 12:30:28', '0000-00-00 00:00:00'),
(69, 20, '[]', 'Active', NULL, 'active', '2024-07-30 12:31:08', '0000-00-00 00:00:00'),
(69, 20, '[]', 'Completed', NULL, 'active', '2024-07-30 12:31:41', '0000-00-00 00:00:00'),
(69, 20, '[]', 'Pause', NULL, 'active', '2024-07-30 12:32:18', '0000-00-00 00:00:00'),
(69, 20, '[]', 'In Progress', NULL, 'active', '2024-07-30 12:32:49', '0000-00-00 00:00:00'),
(69, 20, '[]', 'Sent', NULL, 'active', '2024-07-30 12:33:10', '0000-00-00 00:00:00'),
(69, 20, '[]', 'Scheduled', NULL, 'active', '2024-07-30 12:33:30', '0000-00-00 00:00:00'),
(70, 20, '[]', 'Converted To Lead', NULL, 'active', '2024-07-30 12:46:06', '0000-00-00 00:00:00'),
(70, 20, '[]', 'Sent', NULL, 'active', '2024-07-30 12:46:15', '0000-00-00 00:00:00'),
(70, 20, '[]', 'Pending', NULL, 'active', '2024-07-30 12:46:34', '0000-00-00 00:00:00'),
(70, 20, '[]', 'Bounce', NULL, 'active', '2024-07-30 12:46:52', '0000-00-00 00:00:00'),
(70, 20, '[]', 'Error', NULL, 'active', '2024-07-30 12:47:09', '0000-00-00 00:00:00'),
(70, 20, '[]', 'Open', NULL, 'active', '2024-07-30 12:47:35', '0000-00-00 00:00:00'),
(70, 20, '[]', 'Unsubscribe', NULL, 'active', '2024-07-30 12:47:56', '0000-00-00 00:00:00'),
(70, 20, '[]', 'Click', NULL, 'active', '2024-07-30 12:48:14', '0000-00-00 00:00:00'),
(70, 20, '[]', 'Replied', NULL, 'active', '2024-07-30 12:48:31', '0000-00-00 00:00:00'),
(70, 20, '[]', 'Email Updated and Mail Resend', NULL, 'active', '2024-08-30 18:06:35', '0000-00-00 00:00:00'),
(70, 20, '[]', 'Resend', NULL, 'active', '2024-08-30 18:06:57', '0000-00-00 00:00:00');


https://ycrm-demo.yirondemos.com/toptraveltrip/
superadminnew@satgurutravel.com
Satguru@123

https://ycrm-demo.yirondemos.com/satguru_crm/
superadmin@satgurutravel.com
Satguru@123

https://ycrm-demo.yirondemos.com/sbk_crm/dashboard
superadmin@satgurutravel.com
Satguru@123

http://ycrm-demo.yirondemos.com/travtips/
admin@travtips.ae
admin@123

https://ycrm-demo.yirondemos.com/orient_tours/dashboard
superadmin@yirontechnologies.com
Satguru@123
https://ycrm-demo.yirondemos.com/pma
'username' => 'uat_crm',
'password' => 'U@tF09q@d7',
 
https://orienttours.ycrmlite.com/dashboard
admin@orient-tours-uae.com
Orient@123
https://orienttours.ycrmlite.com/pma
root
7Guru@123

http://localhost/jyoti/orient_crm_V1.1/dashboard
superadmin@yirontechnologies.com
Satguru@123

vnc : 164.52.198.59:12

ALTER TABLE `mst_smtp_details` CHANGE `smtp_password` `smtp_password` VARCHAR(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL;

ALTER TABLE `mst_email_template` ADD `allow_to_users` TEXT NULL DEFAULT NULL AFTER `is_admin`;


extre <code>
// public function users_by_instance_get()
	// {
	// 	$authorization = $this->get_basic_auth_credentials();
	// 	if (!isset($authorization["username"]) || !isset($authorization["password"])) {
	// 		http_response_code(401);
	// 		echo json_encode(["status" => 0, "message" => "Basic Auth is missing."]);
	// 		exit;
	// 	}

	// 	$authUsername = $authorization["username"];
	// 	$authPassword = $authorization["password"];
	// 	$filterToken = ["authUsername" => $authUsername, "authPassword" => $authPassword];


	// 	$checkForApiToken = $this->Common_model->filterCollectionRecords($filterToken, "api_tokens");


	// 	if (!$checkForApiToken) {
	// 		http_response_code(401);
	// 		echo json_encode(["status" => 0, "message" => "Token missing or invalid."]);
	// 		exit;
	// 	}

	// 	if (!isset($_GET["instanceId"])) {
	// 		http_response_code(400);
	// 		echo json_encode(["status" => 0, "message" => "instanceId is required."]);
	// 		exit;
	// 	}

	// 	$instanceId = trim($_GET["instanceId"]);

	// 	$instanceDetails = $this->Instance_model->get_instance_detail($instanceId);
	// 	if (!$instanceDetails || empty($instanceDetails->branch_name)) {
	// 		http_response_code(404);
	// 		echo json_encode(["status" => 0, "message" => "Instance not found or no branch linked."]);
	// 		exit;
	// 	}

	// 	$branchName = $instanceDetails->branch_name;

	// 	$pipeline = [
	// 		['$match' => ['branch_status' => 'active', 'branch_name' => $branchName]],
	// 		['$lookup' => [
	// 			'from' => 'mst_instances',
	// 			'localField' => 'branch_name',
	// 			'foreignField' => 'branch_name',
	// 			'as' => 'instances'
	// 		]],
	// 		['$lookup' => [
	// 			'from' => 'mst_users',
	// 			'let' => ['branch_name' => '$branch_name'],
	// 			'pipeline' => [
	// 				['$match' => [
	// 					'$expr' => [
	// 						'$and' => [
	// 							['$eq' => ['$branch', '$$branch_name']],
	// 							['$eq' => ['$status', 'active']],
	// 							['$ne' => ['$role', 'Super admin']]
	// 						]
	// 					]
	// 				]],
	// 				['$project' => [
	// 					'user_id' => 1,
	// 					'first_name' => 1,
	// 					'last_name' => 1,
	// 					'email' => 1,
	// 					'role' => 1,
	// 					'status' => 1,
	// 					'branch' => 1,
	// 					'created_at' => 1,
	// 					'last_activity_at' => 1,
	// 					'_id' => 0
	// 				]],
	// 				['$sort' => ['first_name' => 1]]
	// 			],
	// 			'as' => 'users'
	// 		]],
	// 		['$sort' => ['branch_name' => 1]]
	// 	];

	// 	$results = $this->Common_model->run_aggregation('mst_branches', $pipeline);

	// 	$results_array = json_decode(json_encode($results), true);

	// 	//echo '<pre>';print_r($results_array);die;

	// 	if (empty($results_array) || empty($results_array[0]['users'])) {
	// 		http_response_code(404);
	// 		echo json_encode([]);
	// 		exit;
	// 	}

	// 	$users = $results_array[0]['users'];
	// 	$formatted_users = [];

	// 	foreach ($users as $user) {
	// 		$formatted_users[] = [
	// 			"first_name"       => $user["first_name"] ?? null,
	// 			"last_name"        => $user["last_name"] ?? null,
	// 			"email"            => $user["email"] ?? null,
	// 			"role"             => $user["role"] ?? null,
	// 			"user_id"          => $user["user_id"] ?? null,
	// 			"status"           => $user["status"] ?? null,
	// 			"branch"           => $user["branch"] ?? null,
	// 			"created_at"       => $user["created_at"] ?? null,
	// 			"last_activity_at" => $user["last_activity_at"] ?? null,

				
	// 		];
	// 	}

	// 	header('Content-Type: application/json');
	// 	echo json_encode($formatted_users);


	// }

	// public function agent_details_get()
	// {
	// 	$authorization = $this->get_basic_auth_credentials();
	// 	if (!isset($authorization["username"]) || !isset($authorization["password"])) {
	// 		http_response_code(401);
	// 		echo json_encode(["status" => 0, "message" => "Basic Auth is missing."]);
	// 		exit;
	// 	}

	// 	$authUsername = $authorization["username"];
	// 	$authPassword = $authorization["password"];
	// 	$filterToken = ["authUsername" => $authUsername, "authPassword" => $authPassword];

	// 	$checkForApiToken = $this->Common_model->filterCollectionRecords($filterToken, "api_tokens");

	// 	if (!$checkForApiToken) {
	// 		http_response_code(401);
	// 		echo json_encode(["status" => 0, "message" => "Token missing or invalid."]);
	// 		exit;
	// 	}

	// 	$userId = $_GET["userId"] ?? null;
	// 	$fromDate = $_GET["fromDate"] ?? date("Y-m-d");
	// 	$toDate = $_GET["toDate"] ?? date("Y-m-d");
	// 	$instanceId = trim($_GET["instanceId"] ?? '');

	// 	if (!$userId || !$fromDate || !$toDate || !$instanceId) {
	// 		http_response_code(400);
	// 		echo json_encode(["status" => 0, "message" => "Missing userId, date range, or instanceId."]);
	// 		return;
	// 	}

	// 	if (!$this->isValidDateRange($fromDate, $toDate)) {
	// 		http_response_code(400);
	// 		echo json_encode(["status" => 0, "message" => "Date range should not exceed 10 days."]);
	// 		return;
	// 	}

	// 	$instanceDetails = $this->Instance_model->get_instance_detail($instanceId);
	// 	if (!$instanceDetails || empty($instanceDetails->branch_name)) {
	// 		http_response_code(404);
	// 		echo json_encode(["status" => 0, "message" => "Instance not found or no branch linked."]);
	// 		exit;
	// 	}

	// 	$branchName = $instanceDetails->branch_name;

	// 	$pipeline = [
	// 		['$match' => ['branch_status' => 'active', 'branch_name' => $branchName]],
	// 		['$lookup' => [
	// 			'from' => 'mst_instances',
	// 			'localField' => 'branch_name',
	// 			'foreignField' => 'branch_name',
	// 			'as' => 'instances'
	// 		]],
	// 		['$lookup' => [
	// 			'from' => 'mst_users',
	// 			'let' => ['branch_name' => '$branch_name'],
	// 			'pipeline' => [
	// 				['$match' => [
	// 					'$expr' => [
	// 						'$and' => [
	// 							['$eq' => ['$branch', '$$branch_name']],
	// 							['$eq' => ['$status', 'active']],
	// 							['$ne' => ['$role', 'Super admin']]
	// 						]
	// 					]
	// 				]],
	// 				['$project' => [
	// 					'user_id' => 1,
	// 					'first_name' => 1,
	// 					'last_name' => 1,
	// 					'email' => 1,
	// 					'role' => 1,
	// 					'status' => 1,
	// 					'branch' => 1,
	// 					'created_at' => 1,
	// 					'last_activity_at' => 1,
	// 					'_id' => 0
	// 				]],
	// 				['$sort' => ['first_name' => 1]]
	// 			],
	// 			'as' => 'users'
	// 		]],
	// 		['$sort' => ['branch_name' => 1]]
	// 	];

	// 	$results = $this->Common_model->run_aggregation('mst_branches', $pipeline);

	// 	$results_array = json_decode(json_encode($results), true);

	// 	//echo '<pre>';print_r($results_array);die;

	// 	if (empty($results_array) || empty($results_array[0]['users'])) {
	// 		http_response_code(404);
	// 		echo json_encode([]);
	// 		exit;
	// 	}

	// 	$users = $results_array[0]['users'];
	// 	$formatted_users = [];

	// 	foreach ($users as $user) {
	// 		$formatted_users[] = [
	// 			"first_name"       => $user["first_name"] ?? null,
	// 			"last_name"        => $user["last_name"] ?? null,
	// 			"email"            => $user["email"] ?? null,
	// 			"role"             => $user["role"] ?? null,
	// 			"user_id"          => $user["user_id"] ?? null,
	// 			"status"           => $user["status"] ?? null,
	// 			"branch"           => $user["branch"] ?? null,
	// 			"created_at"       => $user["created_at"] ?? null,
	// 			"last_activity_at" => isset($user["last_activity_at"]) 
	// 									? date('Y-m-d h:i:s A', intval($user["last_activity_at"])) 
	// 									: null,
	// 			"unread_messages"  => $this->Dashboard_model->get_unread_count_for_instance(
	// 												$user["user_id"], 
	// 												$instanceId, 
	// 												$fromDate, 
	// 												$toDate
	// 			),
	// 			"received_messages"=>
	// 			"send_messaes"=>							];
	// 	}


	// 	header('Content-Type: application/json');
	// 	echo json_encode($formatted_users);



		
	// }
</code>