<?php
/***************************************************************************
 *                                                                          *
 * Copyright (c) 2005 ISPG Technologies (I) Pvt Ltd. All rights reserved.   *
 *                                                                          *
 * This  is  commercial  software,  only  users  who have purchased a valid *
 * license  and  accept  to the terms of the  License Agreement can install *
 * and use this program.                                                    *
 *                                                                          *
 ****************************************************************************
 * PLEASE READ THE FULL TEXT  OF THE SOFTWARE  LICENSE   AGREEMENT  IN  THE *
 * "copyright.txt" FILE PROVIDED WITH THIS DISTRIBUTION PACKAGE.            *
 ****************************************************************************/
//
//$Id: FormModel.php, v 1.0 2013/04/16 20:52:11
//
class FormModel extends Model {
    //START	:	FUNCTION to save the form
    public function saveForm($params) {
        extract($params);
        extract($_POST['__FORM_SETTINGS']);
        $formID                     = $_POST['formID'];
        $fieldAlignments            = array(
            "frm-style01" => 'top_align_two_column',
            "frm-style02" => 'left_align_one_column',
            "frm-style03" => 'left_align_two_column',
            "frm-style04" => 'top_align_one_column'
        );
        $formKey                    = preg_replace('/[^\da-z]/i', '', ucwords($formName));
        $sql                        = "
				SELECT 
						count(*)  AS count
					FROM 
						`" . DB_PREFIX . "form_entity` 
					WHERE
						formKey='" . $formKey . "'";
        $result                     = $this->ObjDatabase->readValue($sql);
        $formKey                    = $result['count'] > 0 ? $formKey . $result['count'] : $formKey;
        $fieldAlignment             = isset($fieldAlignment) ? $fieldAlignment : '';
        $fieldAlignment             = (isset($fieldAlignments[$fieldAlignment]) ? $fieldAlignments[$fieldAlignment] : '');
        $sendConfirmMailToSubmitter = ((isset($sendConfirmMailToSubmitter) && $sendConfirmMailToSubmitter == 'Yes') ? 'Yes' : 'No');
        $sendCopyToSubmitter        = ((isset($sendCopyToSubmitter) && $sendCopyToSubmitter == 'Yes') ? 'Yes' : 'No');
        $agreementText              = ((isset($enableAgreement) && $enableAgreement == 'Yes') ? $agreementText : '');
        $afterFormSubmission        = isset($afterFormSubmission) ? $afterFormSubmission : '';
        $enableCaptcha              = isset($enableCaptcha) ? $enableCaptcha : '';
        $captchaValidation          = isset($captchaValidation) ? $captchaValidation : '';
        $adminEmailSubject          = isset($adminEmailSubject) ? $adminEmailSubject : '';
        $adminEmailContent          = isset($adminEmailContent) ? $adminEmailContent : '';
        $userEmailSubject           = isset($userEmailSubject) ? $userEmailSubject : '';
        $userEmailContent           = isset($userEmailContent) ? $userEmailContent : '';
        $currentlanguageID          = isset($currentlanguageID) ? $currentlanguageID : '';
        $elementRule                = isset($elementRule) ? $elementRule : '';
        $brandID                    = isset($brandID) ? $brandID : '';
        if (isset($accessGroupIDs) && $accessGroupIDs != '') {
            $accessGroupID = implode(",", $accessGroupIDs);
        } else {
            $accessGroupID = '';
        }
        if ($formID > 0) {
            $sql = "
				UPDATE 
					" . DB_PREFIX . "form_entity
				SET 
					globalUploadFolder='" . $globalUploadFolder . "',
					accessGroupIDs='" . $accessGroupID . "',
					fieldAlignment='" . $fieldAlignment . "',
					formStatus='" . $formStatus . "',
					disableForm='" . $disableForm . "',
					enableAgreement='" . $enableAgreement . "',
					disableFormCriteria='" . $disableFormCriteria . "',
					enableCaptcha='" . $enableCaptcha . "',                                        
					formModifiedDate='" . date('Y-m-d H:i:s') . "',
					elementRule='" . $elementRule . "',
                                        brandID = '".$brandID."'
				WHERE
					formID='" . $formID . "'";
            $this->ObjDatabase->setQuery($sql);
            $sql = "
				UPDATE 
					" . DB_PREFIX . "form_entity_details
				SET
					formName='" . $this->escape($formName) . "',
					formDesc='" . $this->escape($formDesc) . "',
					agreementText='" . $this->escape($agreementText) . "',
					submitButtonValue='" . $this->escape($submitButtonValue) . "',
					disableFormMessage='" . $this->escape($disableFormMessage) . "',
                                        captchaValidation='" . $this->escape($captchaValidation) . "'
				WHERE
					languageID='" . $currentlanguageID . "'
					AND
					formID='" . $formID . "'";
            $this->ObjDatabase->setQuery($sql);
        } else {
            $sql = "
				INSERT INTO " . DB_PREFIX . "form_entity
					(
						formKey,
						globalUploadFolder,
						accessGroupIDs,
						fieldAlignment,
						formStatus,
						disableForm,
						disableFormCriteria,
						enableCaptcha,
						afterFormSubmission,
						elementRule,
						enableAgreement,
						formCreatedBy,
                                                brandID
                                                
					)
				VALUES
					(
						'" . $formKey . "',
						'" . $this->escape($globalUploadFolder) . "',
						'" . implode(",", $accessGroupIDs) . "',
						'" . $fieldAlignment . "',
						'" . $formStatus . "',
						'" . $disableForm . "',
						'" . $disableFormCriteria . "',
						'" . $enableCaptcha . "',
						'" . $afterFormSubmission . "',
						'" . $elementRule . "',
						'" . $enableAgreement . "',
						'" . $user . "',
                                                '" . $brandID . "'    
					)";
            $this->ObjDatabase->setQuery($sql);
            $formID = $this->ObjDatabase->getInsertId();
            if ($formID > 0) {
                $sqlInsert = array();
                foreach ($languageIDs['All'] as $languageID) {
                    array_push($sqlInsert, "(
							'" . $formID . "',
							'" . $languageID . "',
							'" . $this->escape($formName) . "',
							'" . $this->escape($formDesc) . "',
							'" . $this->escape($disableFormMessage) . "',
							'" . $this->escape($adminEmailSubject) . "',
							'" . $this->escape($adminEmailContent) . "',
							'" . $this->escape($userEmailSubject) . "',
							'" . $this->escape($agreementText) . "',
							'" . $this->escape($submitButtonValue) . "',
							'" . $this->escape($userEmailContent) . "',
                                                        '" . $this->escape($captchaValidation) . "'
						)");
                }
                $sql = "
					INSERT INTO " . DB_PREFIX . "form_entity_details
						(
							formID,
							languageID,
							formName,
							formDesc,
							disableFormMessage,
							adminEmailSubject,
							adminEmailContent,
							userEmailSubject,
							agreementText,
							submitButtonValue,
							userEmailContent,
                                                        captchaValidation
						)
					VALUES
						" . implode(",", $sqlInsert);
                $this->ObjDatabase->setQuery($sql);
            }
            $tableName = DB_PREFIX . "form_" . $formKey;
            $sql       = "CREATE TABLE IF NOT EXISTS `" . $tableName . "` (
				  `submissionID` int(11) NOT NULL AUTO_INCREMENT,
				  `submissionDetailsID` int(11) NOT NULL,
				  PRIMARY KEY (`submissionID`)
				) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ";
            $this->ObjDatabase->setQuery($sql);
        }
        return array(
            $formID,
            $formKey
        );
    }
    //END 	:	FUNCTION to save the form
    public function updateEmail($type, $formID) {
        extract($_POST);
        $currentlanguageID = isset($currentlanguageID) ? $currentlanguageID : '';
        if ($type == 'user') {
            $sql = "	
				UPDATE 
					" . DB_PREFIX . "form_entity_details
				SET
					userEmailSubject='" . $this->escape($userEmailSubject) . "',
					userEmailContent='" . $this->escape($userEmailContent) . "'
				WHERE
					languageID='" . $currentlanguageID . "'
					AND
					formID='" . $formID . "'";
            $this->ObjDatabase->setQuery($sql);
        } else {
            $sql = "	
				UPDATE 
					" . DB_PREFIX . "form_entity_details
				SET
					adminEmailSubject='" . $this->escape($adminEmailSubject) . "',
					adminEmailContent='" . $this->escape($adminEmailContent) . "'
				WHERE
					languageID='" . $currentlanguageID . "'
					AND
					formID='" . $formID . "'";
            $this->ObjDatabase->setQuery($sql);
        }
    }
    public function saveFormAction($params) {
        extract($params);
        extract($_POST['__FORM_SETTINGS']);       
        $formID = $_POST['formID'];
        
        if ($formID > 0) {
            if ($afterFormSubmission == 'ShowText') {
                $afterFormSubmissionAction = $afterFormSubmissionActionarea;
            } else if ($afterFormSubmission == 'RedirectToUrl') {
                $afterFormSubmissionAction = $afterFormSubmissionActiontext;
            }
            $saveToDataBase             = (($saveToDataBase[0] == 'Yes' || $saveToDataBase[0] == 'Y') ? 'Yes' : 'No');
            $sendConfirmMailToSubmitter = (($sendConfirmMailToSubmitter[0] == 'Yes' || $sendConfirmMailToSubmitter[0] == 'Y') ? 'Yes' : 'No');
            $sql                        = "
				UPDATE 
					" . DB_PREFIX . "form_entity
				SET 
					sendConfirmMailToSubmitter='" . $sendConfirmMailToSubmitter . "',
					saveToDataBase='" . $saveToDataBase . "',
					afterFormSubmission='" . $afterFormSubmission . "',
					emailFromName='" . $emailFromName . "',
					adminEmails='" . $adminEmails . "',
					codeAction='" . (isset($codeAction) ? $this->escape($codeAction) : '') . "',
					userEmailFields='" . $userEmailFields . "',
					formModifiedDate='" . date('Y-m-d H:i:s') . "',
					elementRule='" . (isset($elementRule) ? $elementRule : '') . "'
				WHERE
					formID='" . $formID . "'";
            $this->ObjDatabase->setQuery($sql);
            $afterFormSubmissionAction = isset($afterFormSubmissionAction) ? $afterFormSubmissionAction : '';
            $sql                       = "
				UPDATE 
					" . DB_PREFIX . "form_entity_details
				SET
					afterFormSubmissionAction='" . $this->escape($afterFormSubmissionAction) . "'
				WHERE
					languageID='" . $currentlanguageID . "'
					AND
					formID='" . $formID . "'";
            $this->ObjDatabase->setQuery($sql);
        }
        return array(
            $formID,
            isset($formKey) ? $formKey : ''
        );
    }
    //END 	:	FUNCTION to save the form
    public function getFormKey($formID) {
        $sql    = "
				SELECT 
						*
					FROM 
						`" . DB_PREFIX . "form_entity` 
					WHERE
						formID='" . $formID . "'";
        $result = $this->ObjDatabase->readValue($sql);
        return $result['formKey'];
    }
    //START : 	FUNCTION to save the form Elements
    public function saveFormElements($params) {
        extract($params);
        $optionEnableAttributes = array(
            'checkbox',
            'radio',
            'select',
            'select_multiple'
        );
        $tableCreateQryArr      = array();
        $formKey                = $this->getFormKey($formID);
        $sql                    = "
				SELECT EXISTS(
					SELECT 
						*
					FROM 
						" . DB_PREFIX . "form_element 
					WHERE
						attributeType='submit' 
						AND
						formID='" . $formID . "'
					LIMIT 
						1
				) AS exist";
        $result                 = $this->ObjDatabase->readValue($sql);
        if (!$result['exist']) {
            $sql = "
					INSERT INTO " . DB_PREFIX . "form_element 
						(
							formID,
							attributeType,
							attributeOrder,
							validateRule
						)
					VALUES
						(
							'" . $formID . "',
							'submit',
							'9999',
							''
						)";
            $this->ObjDatabase->setQuery($sql);
            $formElementID = $this->ObjDatabase->getInsertId();
            if ($formElementID > 0) {
                $sqlInsert = array();
                foreach ($languageIDs['All'] as $languageID) {
                    array_push($sqlInsert, "(
								'" . $formElementID . "',
								'" . $languageID . "',
								'Submit'
							)");
                }
                $sql = "
						INSERT INTO " . DB_PREFIX . "form_element_details 
							(
								formElementID,
								languageID,
								formElementLabel
				
							)
						VALUES
							" . implode(",", $sqlInsert);
                $this->ObjDatabase->setQuery($sql);
            }
        }
        $string      = trim(str_replace("\n", ' ', $_POST['__FORMOBJECT']));
        $formObjects = json_decode($string, true);
        foreach ($formObjects as $key => $formObject) {
            $validateRule       = '';
            $elementsData       = $formObject['data'];
            $uniqueID           = $formObject['uniqueID'];
            $status             = (isset($formObject['deleted']) && $formObject['deleted'] != '') ? 'Deleted' : 'Active';
            $attributeOrder     = $formObject['order'];
            $attributeType      = $elementsData['type'];
            $formElementLabel   = isset($elementsData['caption']) ? $elementsData['caption'] : '';
            $formElementSubText = $elementsData['defaults']['instruction'];
            //$elementsData['defaults']['enableSearch'];
            $enableSearch       = (isset($elementsData['defaults']['enableSearch']) && $elementsData['defaults']['enableSearch'] == 'false') ? 0 : 1;
            if ($attributeType == 'html' || $attributeType == 'agreement') {
                $formElementDesc                  = $elementsData['defaults']['text'];
                $elementsData['defaults']['text'] = '';
                $formElementLabel                 = $elementsData['defaults']['label'];
            } else {
                $formElementDesc = isset($elementsData['defaults']['desc']) ? $elementsData['defaults']['desc'] : '';
            }
            $formElementPlaceHolder = '';
            $formElementValue       = isset($elementsData['defaults']['value']) ? $elementsData['defaults']['value'] : '';
            $extraAttributes        = $this->escape(json_encode($elementsData));
            if (isset($elementsData['defaults']['validate']) && is_array($elementsData['defaults']['validate'])) {
                $validateRule          = implode("~*", $elementsData['defaults']['validate']);
                $formValidationMessage = $elementsData['defaults']['error'];
            }
            $sql    = "
				SELECT EXISTS(
					SELECT 
						*
					FROM 
						" . DB_PREFIX . "form_element 
					WHERE
						uniqueID='" . $uniqueID . "' 
					LIMIT 
						1
				) AS exist";
            $result = $this->ObjDatabase->readValue($sql);
            if ($result['exist']) {
                $sql = "
						UPDATE 
							" . DB_PREFIX . "form_element 
						SET 
							attributeType='" . $attributeType . "',
							attributeOrder='" . $attributeOrder . "',
							enableSearch='" . $enableSearch . "',
							elementStatus='" . $status . "',
							validateRule='" . $validateRule . "',
							extraAttributes='" . $extraAttributes . "' 		
							
						WHERE 
							uniqueID='" . $uniqueID . "'";
                $this->ObjDatabase->setQuery($sql);
                $sql = "
						UPDATE 
							" . DB_PREFIX . "form_element_details 
						SET 
							formElementLabel='" . $this->escape($formElementLabel) . "',
							formElementDesc='" . $this->escape($formElementDesc) . "',
							formElementSubText='" . $this->escape($formElementSubText) . "',
							formElementPlaceHolder='" . $this->escape($formElementPlaceHolder) . "',
							formElementValue='" . $this->escape($formElementValue) . "',
							formValidationMessage=	'" . $this->escape($formValidationMessage) . "' 
												
						WHERE 
							formElementID=(
								SELECT 
									formElementID 
								FROM 
									" . DB_PREFIX . "form_element 
								WHERE 
									uniqueID='" . $uniqueID . "'
								)
							AND 
							languageID='" . $currentlanguageID . "'";
                $this->ObjDatabase->setQuery($sql);
                if (in_array($attributeType, $optionEnableAttributes)) {
                    $items = $elementsData['defaults']['items'];
                    if (is_array($items)) {
                        foreach ($items as $key => $item) {
                            $formElementOptionID = isset($item['formElementOptionID']) ? $item['formElementOptionID'] : 0;
                            $sql                 = "
								SELECT EXISTS(
									SELECT 
										* 
									FROM 
										`" . DB_PREFIX . "form_options` 
									WHERE
										formElementOptionID='" . $formElementOptionID . "' 
									LIMIT 
										1) 
								AS exist";
                            $result              = $this->ObjDatabase->readValue($sql);
                            if ($result['exist']) {
                                $sql = "
									UPDATE  
										" . DB_PREFIX . "form_options 
									SET 
										isDefault='" . ((isset($item['selected']) && $item['selected'] == 'true') ? 'Yes' : 'No') . "',
										optionStatus='" . ((isset($item['deleted']) && $item['deleted'] == 'true') ? 'Deleted' : 'Active') . "',
										optionOrder='" . $item['optionOrder'] . "'  
									WHERE 
										formElementOptionID='" . $formElementOptionID . "'";
                                $this->ObjDatabase->setQuery($sql);
                                $sql = "
									UPDATE 
										" . DB_PREFIX . "form_options_details 
									SET 
										languageID='" . $currentlanguageID . "',
										optionDesc='" . $this->escape($item['text']) . "'
									WHERE 
										formElementOptionID='" . $formElementOptionID . "'
										AND languageID='" . $currentlanguageID . "'";
                                $this->ObjDatabase->setQuery($sql);
                            } else {
                                if (isset($item['deleted']) && $item['deleted'] == 'true') {
                                    continue;
                                }
                                $sql = "
									INSERT INTO " . DB_PREFIX . "form_options
										(
											formElementID,
											optionID,
											isDefault,
											optionOrder
										)
									VALUES
										(
											(
												SELECT 
													formElementID 
												FROM 
													" . DB_PREFIX . "form_element 
												WHERE 
													uniqueID='" . $uniqueID . "'
											),
											'" . ($key + 1) . "',
											'" . (($item['selected'] == 'true') ? 'Yes' : 'No') . "',
											'" . ($key + 1) . "'
										)";
                                $this->ObjDatabase->setQuery($sql);
                                $formElementOptionID = $this->ObjDatabase->getInsertId();
                                if ($formElementOptionID > 0) {
                                    $sqlInsert = array();
                                    foreach ($languageIDs['All'] as $languageID) {
                                        array_push($sqlInsert, "(
											'" . $formElementOptionID . "',
											'" . $languageID . "',
											'" . $this->escape($item['text']) . "'
										)");
                                    }
                                    $sql = "
									INSERT INTO " . DB_PREFIX . "form_options_details
										(
											formElementOptionID,
											languageID,
											optionDesc
										)
									VALUES
										" . implode(",", $sqlInsert);
                                    $this->ObjDatabase->setQuery($sql);
                                }
                            }
                        }
                    }
                }
            } else {
                if ($status == 'Deleted') {
                    continue;
                }
                $sql = "
					INSERT INTO " . DB_PREFIX . "form_element 
						(
							formID,
							uniqueID,
							attributeType,
							attributeOrder,
							enableSearch,
							validateRule,
							extraAttributes
							
						)
					VALUES
						(
							'" . $formID . "',
							'" . $uniqueID . "',
							'" . $attributeType . "',
							'" . $attributeOrder . "',
							'" . $enableSearch . "',
							'" . $validateRule . "',
							'" . $extraAttributes . "'
						)";
                $this->ObjDatabase->setQuery($sql);
                $formElementID = $this->ObjDatabase->getInsertId();
                /* 
                "address",
                "countryState",
                "html",
                "name", 
                */
                switch ($attributeType) {
                    case 'country':
                    case 'agreement':
                    case 'state':
                    case 'select':
                    case 'businesstype':
                    case 'radio':
                        $dataType = " int(11) NOT NULL ";
                        break;
                    case 'textarea':
                        $dataType = " TEXT NOT NULL ";
                        break;
                    case 'email':
                    case 'text':
                    case 'website':
                    case 'province':
                    case 'image':
                    case 'file':
                    case 'checkbox':
                    case 'select_multiple':
                        $dataType = " varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ";
                        break;
                    case 'date':
                        $dataType = " timestamp NOT NULL ";
                        break;
                    default:
                        $dataType = "";
                }
                if ($dataType != '') {
                    $fieldName = $attributeType . "_" . $formElementID;
                    array_push($tableCreateQryArr, $fieldName . $dataType . "COMMENT '" . $formElementLabel . "'");
                }
                if ($formElementID > 0) {
                    $sqlInsert = array();
                    foreach ($languageIDs['All'] as $languageID) {
                        array_push($sqlInsert, "(
								'" . $formElementID . "',
								'" . $languageID . "',
								'" . $this->escape($formElementLabel) . "',
								'" . $this->escape($formElementDesc) . "',
								'" . $this->escape($formElementSubText) . "',
								'" . $this->escape($formElementPlaceHolder) . "',
								'" . $this->escape($formElementValue) . "',
								'" . $this->escape($formValidationMessage) . "'
								
							)");
                    }
                    $sql = "
						INSERT INTO " . DB_PREFIX . "form_element_details 
							(
								formElementID,
								languageID,
								formElementLabel,
								formElementDesc,
								formElementSubText,
								formElementPlaceHolder,
								formElementValue,
								formValidationMessage
								
							)
						VALUES
							" . implode(",", $sqlInsert);
                    $this->ObjDatabase->setQuery($sql);
                    if (in_array($attributeType, $optionEnableAttributes)) {
                        $items = $elementsData['defaults']['items'];
                        foreach ($items as $key => $item) {
                            if (isset($item['deleted']) && $item['deleted'] == 'true') {
                                continue;
                            }
                            $sql = "
								INSERT INTO " . DB_PREFIX . "form_options
									(
										formElementID,
										optionID,
										isDefault,
										optionOrder
									)
								VALUES
									(
										'" . $formElementID . "',
										'" . ($key + 1) . "',
										'" . (($item['selected'] == 'true') ? 'Yes' : 'No') . "',
										'" . ($key + 1) . "'
									)";
                            $this->ObjDatabase->setQuery($sql);
                            $formElementOptionID = $this->ObjDatabase->getInsertId();
                            if ($formElementOptionID > 0) {
                                $sqlInsert = array();
                                foreach ($languageIDs['All'] as $languageID) {
                                    array_push($sqlInsert, "(
										'" . $formElementOptionID . "',
										'" . $languageID . "',
										'" . $this->escape($item['text']) . "'
									)");
                                }
                                $sql = "
								INSERT INTO " . DB_PREFIX . "form_options_details
									(
										formElementOptionID,
										languageID,
										optionDesc
									)
								VALUES
									" . implode(",", $sqlInsert);
                                $this->ObjDatabase->setQuery($sql);
                            }
                        }
                    }
                }
            }
        }
        if (!empty($tableCreateQryArr)) {
            $tableName = DB_PREFIX . "form_" . $formKey;
            $sql       = 'ALTER TABLE  `' . $tableName . '` ADD ' . implode(", ADD ", $tableCreateQryArr);
            $this->ObjDatabase->setQuery($sql);
        }
    }
    //END 	: 	FUNCTION to save the form Elements
    //FUNCTION to list all forms
    public function listForm($params) {
        $languageID      = isset($params['languageID']) ? $params['languageID'] : '';
        $formStatus      = isset($params['formStatus']) ? $params['formStatus'] : '';
        $searchTitle     = isset($params['searchTitle']) ? $params['searchTitle'] : '';
        $statusSearch    = isset($params['statusSearch']) ? $params['statusSearch'] : '';
        $formStatus      = isset($params['formStatus']) ? $params['formStatus'] : '';
        $searchCreatedBy = isset($params['searchCreatedBy']) ? $params['searchCreatedBy'] : '';
        $sortby          = isset($params['sortby']) ? $params['sortby'] : '';
        $orderby         = isset($params['orderby']) ? $params['orderby'] : '';
        $brandID         = isset($params['brandID']) ? $params['brandID'] : '';
        $sql             = "
			SELECT 
				*,
				(
					SELECT 
						MAX(dateSubmitted) 
					FROM 
						" . DB_PREFIX . "form_submissions 
					WHERE 
						formID=forEnt.formID
				) AS lastSubmitted,
				CONCAT(adminUserFirstName,' ',adminUserLastName) AS adminname,
				(
					SELECT 
						count(*)
					FROM 
						" . DB_PREFIX . "form_submissions
					WHERE
						formID=forEnt.formID
						AND 
						submittedDataStatus!='Deleted'
				) AS submissions,brandDet.brandName
			FROM 
					" . DB_PREFIX . "form_entity forEnt
				INNER JOIN
					" . DB_PREFIX . "form_entity_details forEntDet
						ON 	(
							forEnt.formID=forEntDet.formID
							AND
							languageID='" . $languageID . "'
						)
				INNER JOIN 
					" . DB_PREFIX . "admin_users admUsr
						ON 	(
							admUsr.adminUserID=forEnt.formCreatedBy
						)
                                LEFT JOIN 
                                " . DB_PREFIX . "brands AS brand
                                        ON	(
                                                brand.brandID = forEnt.brandID
                                        )                     
                                LEFT JOIN 
                                        " . DB_PREFIX . "brand_details AS brandDet
                                        ON	(
                                                brandDet.brandID = brand.brandID
                                                AND
                                                brandDet.languageID = '" . $languageID . "'
                                        )    
			WHERE 
				formStatus in (" . $formStatus . ") 
				AND
				forEnt.status in ('Active','Inactive')";
        if ($searchTitle != '') {
            $sql .= "
				AND
				formName like '%" . $this->escape($searchTitle) . "%'
				";
        }
        if($brandID!='') {
            $sql .= " AND forEnt.brandID='".$brandID."'";
        }
        if ($statusSearch != '') {
            $sql .= "
				AND
				formStatus ='" . $statusSearch . "'
					
				";
        }
        if ($searchCreatedBy != '') {
            $sql .= "
				AND
				forEnt.formCreatedBy='" . $searchCreatedBy . "'
				";
        }
        switch ($sortby) {
            case 'sformid':
                $sql .= " ORDER BY forEnt.formID  " . $orderby;
                break;
            case 'sformtitle':
                $sql .= " ORDER BY forEntDet.formName  " . $orderby;
                break;
            case 'ssubmissions':
                $sql .= " ORDER BY submissions  " . $orderby;
                break;
            case 'screated':
                $sql .= " ORDER BY forEnt.formCreatedDate  " . $orderby;
                break;
        }
        return $this->ObjDatabase->readValues($sql);
    }
    //FUNCTION to get the single form Details
    public function getForm($params) {
        $languageID  = $params['languageID'];
        $userGroupID = isset($params['userGroupID']) ? $params['userGroupID'] : '';
        $formID      = $params['formID'];
        $formKey     = isset($params['formKey']) ? $params['formKey'] : '';
        $formStatus  = $params['formStatus'];
        $status      = isset($params['status']) ? $params['status'] : '';
        $brandID     = isset($params['brandID']) ? $params['brandID'] : '';
        $sql         = "
			SELECT 
				forEnt.formID,
				formKey,
				formName,
				formDesc,
				codeAction,
				globalUploadFolder,
				accessGroupIDs,
				fieldAlignment,
				formStatus,
				sendConfirmMailToSubmitter,
				saveToDataBase,
				(
					SELECT 
						count(*)
					FROM 
						" . DB_PREFIX . "form_submissions
					WHERE
						formID=forEnt.formID
						AND
						submittedDataStatus!='Deleted'
				) AS submissions,
				disableForm,
				enableAgreement,
				agreementText,
				disableFormCriteria,
				submitButtonValue,
				enableCaptcha,
				afterFormSubmission,
				afterFormSubmissionAction,
				adminEmails,
				userEmailFields,
				elementRule,
				formCreatedBy,
				formCreatedDate,
				formModifiedDate,
				formDetailID,
				languageID,
				disableFormMessage,
				emailFromName,
				adminEmailSubject,
				adminEmailContent,
				userEmailSubject,
				userEmailContent,
                                captchaValidation,
                                forEnt.brandID
			FROM
					" . DB_PREFIX . "form_entity forEnt
				INNER JOIN
					" . DB_PREFIX . "form_entity_details forEntDet
						ON 	(
							forEnt.formID=forEntDet.formID
							AND
							languageID='" . $languageID . "'
						) 
			WHERE 
				formStatus in (" . $formStatus . ") ";
        if (isset($userGroupID) && $userGroupID != '') {
            $sql .= "
				AND
				FIND_IN_SET('" . $userGroupID . "',forEnt.accessGroupIDs)
			";
        }
        if (isset($status) && $status != '') {
            $sql .= " AND 
				status ='" . $status . "' ";
        } else {
            $sql .= " AND 
				status in ('Active','Inactive') ";
        }
        if($brandID!='') {
            $sql .= " AND forEnt.brandID='" . $brandID . "'";
        }
        if ($formID != '') {
            $sql .= "
				AND forEnt.formID='" . $formID . "'";
        } else if ($formKey != '') {
            $sql .= "
				AND formKey='" . $formKey . "'";
        }
        $sql .= "
			LIMIT 1";
        return $this->ObjDatabase->readValueSlave($sql);
    }
    /* public function createClone($formID){
    
    
    //Form Option Detsils
    
    
    SELECT formElementOptionID,languageID,optionDesc FROM dp_form_options_details WHERE formElementOptionID IN(
    SELECT formElementOptionID FROM dp_form_options WHERE formElementID in (SELECT formElementID FROM dp_form_element
    WHERE formID=1))
    
    //Form Options
    
    SELECT formElementOptionID,languageID,optionDesc FROM dp_form_options_details WHERE formElementOptionID IN(
    SELECT formElementOptionID FROM dp_form_options WHERE formElementID in (SELECT formElementID FROM dp_form_element
    WHERE formID=1))
    
    
    } */
    //FUNCTION to get the elements of a form
    public function getElements($params) {
        $languageID = $params['languageID'];
        $formID     = $params['formID'];
        $formKey    = isset($params['formKey']) ? $params['formKey'] : '';
        $sql        = "
			SELECT 
				forEle.formElementID,
				formID,
				uniqueID,
				formElementOptionID,
				isDefault,
				attributeType,
				attributeOrder,
				enableSearch,
				validateRule,
				extraAttributes,
				formElementDetailID,
				languageID,
				formElementLabel,
				formElementDesc,
				formElementPlaceHolder,
				formElementValue,
				formElementSubText,
				formValidationMessage
			FROM 
					" . DB_PREFIX . "form_element forEle
				INNER JOIN 
					" . DB_PREFIX . "form_element_details forEleDet
						ON 	(
							forEle.formElementID=forEleDet.formElementID
							AND 
							languageID='" . $languageID . "'
						)
				LEFT JOIN 
					" . DB_PREFIX . "form_options forOpt
						ON 	(
							forOpt.formElementID=forEle.formElementID
							AND
							isDefault='Yes'
							AND 
							optionStatus!='Deleted'
						)
			WHERE 
				elementStatus!='Deleted' ";
        if ($formID != '') {
            $sql .= "
				AND formID='" . $formID . "'";
        } else if ($formKey != '') {
            $sql .= "
				AND formKey='" . $formKey . "'";
        }
        $sql .= "
			ORDER BY 
				attributeOrder";
        return $this->ObjDatabase->readValuesSlave($sql);
    }
    public function getOptions($languageID, $formElementID) {
        $sql = "
			SELECT 
				forOpt.formElementOptionID,
				optionDesc,
				formElementID,
				isDefault,
				optionOrder,
				formElementOptionDetailID,
				languageID
			FROM 
					" . DB_PREFIX . "form_options forOpt 
				INNER JOIN 
					" . DB_PREFIX . "form_options_details forOptDet 
						ON 	(
							forOpt.formElementOptionID =forOptDet.formElementOptionID
							AND 
							languageID='" . $languageID . "'
							AND 
							optionStatus='Active'
						) 
			WHERE 
				formElementID='" . $formElementID . "'
			ORDER BY 
				optionOrder";
        return $this->ObjDatabase->readValues($sql);
    }
    public function saveSubmission($languageID, $param) {
        $fields          = $param['fields'];
        $values          = $param['values'];
        $formDetails     = $param['formDetails'];
        $userAgent       = $param['userAgent'];
        $elementDetails  = isset($param['resultElement']) ? $param['resultElement'] : array();
        $locationDetails = $param['locationDetails'];
        $ipAddress       = $param['ipAddress'];
        $formKey         = $formDetails['formKey'];
        $formID          = $formDetails['formID'];
        $tableName       = DB_PREFIX . "form_" . $formKey;
        $sql             = "INSERT INTO " . DB_PREFIX . "form_submissions
				(
					languageID,
					formID,
					userAgent,
					submittedIP,
					submittedLocation
				
				)
				VALUES
				(
					'" . $languageID . "',
					'" . $formID . "',
					'" . $userAgent . "',
					'" . $ipAddress . "',
					'" . json_encode($locationDetails) . "'
				)";
        $this->ObjDatabase->setQuery($sql);
        $submissionDetailsID = $this->ObjDatabase->getInsertId();
        if (!empty($fields)) {
            $sql = "INSERT INTO " . $tableName . "
					(
						submissionDetailsID,
						" . implode(",", $fields) . "				
					)
					VALUES
					(
						'" . $submissionDetailsID . "',
						'" . implode("','", $values) . "'
					)";
            $this->ObjDatabase->setQuery($sql);
        }
    }
    public function listSubmission($params) {
        $languageID      = $params['languageID'];
        $formKey         = $params['formKey'];
        $formAllElements = $params['formAllElements'];
        $formElement     = $params['formElement'];
        $date            = isset($params['date']) ? $params['date'] : '';
        $fromdate        = isset($params['fromdate']) ? $params['fromdate'] : '';
        $searchArr       = isset($params['searchArr']) ? $params['searchArr'] : array();
        $todate          = isset($params['todate']) ? $params['todate'] : '';
        $limitTo         = $params['limitTo'];
        $limitFrom       = isset($params['limitFrom']) ? $params['limitFrom'] : 0;
        $sqlExt          = '';
        if (!empty($formAllElements)) {
            foreach ($formAllElements as $key_type => $elements) {
                if ($key_type == 'country') {
                    foreach ($elements as $key_index => $county) {
                        $sqlExt .= " 
						LEFT JOIN 
							" . DB_PREFIX . "countries  cntry" . $key_index . " 
								ON 	(
									cntry" . $key_index . ".countryID=forKey." . $county . "
								)
						LEFT JOIN 
							" . DB_PREFIX . "country_details cntryDet_" . $key_index . "
								ON 	(
									cntry" . $key_index . ".countryID=cntryDet_" . $key_index . ".countryID
									AND
									cntryDet_" . $key_index . ".languageID=forSub.languageID
								)";
                        $formElement[] = 'cntryDet_' . $key_index . '.countryName AS ' . $county;
                        unset($formElement[$county]);
                    }
                } else if ($key_type == 'state') {
                    foreach ($elements as $key_index => $state) {
                        $sqlExt .= " 
						LEFT JOIN 
							" . DB_PREFIX . "states  sta" . $key_index . " 
								ON 	(
									sta" . $key_index . ".stateID=forKey." . $state . "
								)
						LEFT JOIN 
							" . DB_PREFIX . "state_details staDet_" . $key_index . "
								ON 	(
									sta" . $key_index . ".stateID=staDet_" . $key_index . ".stateID
									AND
									staDet_" . $key_index . ".languageID=forSub.languageID
								)";
                        $formElement[] = 'staDet_' . $key_index . '.stateName AS ' . $state;
                        unset($formElement[$state]);
                    }
                } else if ($key_type == 'businesstype') {
                    foreach ($elements as $key_index => $businesstype) {
                        $sqlExt .= " 
						LEFT JOIN 
							" . DB_PREFIX . "business_types  busTyp" . $key_index . " 
								ON 	(
									busTyp" . $key_index . ".businessTypeId=forKey." . $businesstype . "
								)
						LEFT JOIN 
							" . DB_PREFIX . "business_type_details busTypDet_" . $key_index . "
								ON 	(
									busTyp" . $key_index . ".businessTypeId=busTypDet_" . $key_index . ".businessTypeId
									AND
									busTypDet_" . $key_index . ".languageID=forSub.languageID
								)";
                        $formElement[] = 'busTypDet_' . $key_index . '.businessTypeName AS ' . $businesstype;
                        unset($formElement[$businesstype]);
                    }
                } else if ($key_type == 'select') {
                    foreach ($elements as $key_index => $select) {
                        $elementID = str_replace("select_", "", $select);
                        $sqlExt .= " 
						LEFT JOIN 
							" . DB_PREFIX . "form_options_details select_forOptDet_" . $key_index . "
								ON 	(
									select_forOptDet_" . $key_index . ".formElementOptionID=forKey." . $select . "
									AND
									select_forOptDet_" . $key_index . ".languageID=forSub.languageID
								)";
                        $formElement[] = 'select_forOptDet_' . $key_index . '.optionDesc AS ' . $select;
                        unset($formElement[$select]);
                    }
                } else if ($key_type == 'radio') {
                    foreach ($elements as $key_index => $select) {
                        $elementID = str_replace("radio_", "", $select);
                        $sqlExt .= " 
						LEFT JOIN 
							" . DB_PREFIX . "form_options_details radio_forOptDet_" . $key_index . "
								ON 	(
									radio_forOptDet_" . $key_index . ".formElementOptionID=forKey." . $select . "
									AND
									radio_forOptDet_" . $key_index . ".languageID=forSub.languageID
								)";
                        $formElement[] = 'radio_forOptDet_' . $key_index . '.optionDesc AS ' . $select;
                        unset($formElement[$select]);
                    }
                }
            }
        }
        $sql = "
			SELECT ";
        if ($limitTo) {
            $sql .= " SQL_CALC_FOUND_ROWS ";
        }
        if (!empty($formElement)) {
            $sql .= implode(",", $formElement) . "";
        } else {
            $sql .= " *";
        }
        $sql .= "	FROM
					" . DB_PREFIX . "form_" . $formKey . " AS forKey
				INNER JOIN
					" . DB_PREFIX . "form_submissions AS forSub
						ON	(
							forKey.submissionDetailsID =forSub.submissionDetailsID
							AND 
							languageID='" . $languageID . "'
						)
				" . $sqlExt . "
			WHERE
				submittedDataStatus!='Deleted'
			";
        if (!empty($searchArr)) {
            foreach ($searchArr as $element) {
                switch ($element['type']) {
                    case 'text':
                    case 'textarea':
                    case 'email':
                    case 'website':
                        $sql .= "
							AND " . $element['name'] . " like '%" . $this->escape($element['value']) . "%' ";
                        break;
                    case 'checkbox':
                    case 'select_multiple':
                        $sql .= "
							AND " . $element['name'] . " like '%" . $this->escape($element['value']) . "%' ";
                        break;
                    case 'select':
                    case 'businesstype':
                    case 'radio':
                    case 'country':
                    case 'date':
                        $sql .= "
							AND " . $element['name'] . " ='" . $element['value'] . "'";
                        break;
                }
            }
        }
        if ($date != '') {
            $sql .= "
				AND DATE_FORMAT(dateSubmitted,'%Y-%m-%d')='" . $this->escape($date) . "'";
        }
        if ($fromdate != '' && $todate != '') {
            $sql .= "
				AND 
					DATE_FORMAT(dateSubmitted ,'%Y-%m-%d')
						BETWEEN '" . $this->escape($fromdate) . "'
						AND 	'" . $this->escape($todate) . "'";
        }
        $sql .= " ORDER BY forKey.submissionID DESC ";
        if ($limitTo > 0) {
            $sql .= "LIMIT " . $limitFrom . "," . $limitTo;
        }
        return $this->ObjDatabase->readValues($sql, MYSQLI_ASSOC);
    }
    public function getSubmissionData($params) {
        $languageID          = $params['languageID'];
        $submissionDetailsID = $params['submissionDetailsID'];
        $formID              = $params['formID'];
        $formAllElements     = $params['formAllElements'];
        $formKey             = $params['formKey'];
        $formElement         = $params['formElement'];
        $sqlExt              = '';
        if (!empty($formAllElements)) {
            foreach ($formAllElements as $key_type => $elements) {
                if ($key_type == 'country') {
                    foreach ($elements as $key_index => $county) {
                        $sqlExt .= " 
						LEFT JOIN 
							" . DB_PREFIX . "countries  cntry" . $key_index . " 
								ON 	(
									cntry" . $key_index . ".countryID=forKey." . $county . "
								)
						LEFT JOIN 
							" . DB_PREFIX . "country_details cntryDet_" . $key_index . "
								ON 	(
									cntry" . $key_index . ".countryID=cntryDet_" . $key_index . ".countryID
									AND
									cntryDet_" . $key_index . ".languageID=forSub.languageID
								)";
                        $formElement[] = 'cntryDet_' . $key_index . '.countryName AS ' . $county;
                        unset($formElement[$county]);
                    }
                } else if ($key_type == 'state') {
                    foreach ($elements as $key_index => $state) {
                        $sqlExt .= " 
						LEFT JOIN 
							" . DB_PREFIX . "states  sta" . $key_index . " 
								ON 	(
									sta" . $key_index . ".stateID=forKey." . $state . "
								)
						LEFT JOIN 
							" . DB_PREFIX . "state_details staDet_" . $key_index . "
								ON 	(
									sta" . $key_index . ".stateID=staDet_" . $key_index . ".stateID
									AND
									staDet_" . $key_index . ".languageID=forSub.languageID
								)";
                        $formElement[] = 'staDet_' . $key_index . '.stateName AS ' . $state;
                        unset($formElement[$state]);
                    }
                } else if ($key_type == 'businesstype') {
                    foreach ($elements as $key_index => $businesstype) {
                        $sqlExt .= " 
						LEFT JOIN 
							" . DB_PREFIX . "business_types  busTyp" . $key_index . " 
								ON 	(
									busTyp" . $key_index . ".businessTypeId=forKey." . $businesstype . "
								)
						LEFT JOIN 
							" . DB_PREFIX . "business_type_details busTypDet_" . $key_index . "
								ON 	(
									busTyp" . $key_index . ".businessTypeId=busTypDet_" . $key_index . ".businessTypeId
									AND
									busTypDet_" . $key_index . ".languageID=forSub.languageID
								)";
                        $formElement[] = 'busTypDet_' . $key_index . '.businessTypeName AS ' . $businesstype;
                        unset($formElement[$businesstype]);
                    }
                } else if ($key_type == 'select') {
                    foreach ($elements as $key_index => $select) {
                        $elementID = str_replace("select_", "", $select);
                        $sqlExt .= " 
						LEFT JOIN 
							" . DB_PREFIX . "form_options_details select_forOptDet_" . $key_index . "
								ON 	(
									select_forOptDet_" . $key_index . ".formElementOptionID=forKey." . $select . "
									AND
									select_forOptDet_" . $key_index . ".languageID=forSub.languageID
								)";
                        $formElement[] = 'select_forOptDet_' . $key_index . '.optionDesc AS ' . $select;
                        unset($formElement[$select]);
                    }
                } else if ($key_type == 'radio') {
                    foreach ($elements as $key_index => $select) {
                        $elementID = str_replace("radio_", "", $select);
                        $sqlExt .= " 
						LEFT JOIN 
							" . DB_PREFIX . "form_options_details radio_forOptDet_" . $key_index . "
								ON 	(
									radio_forOptDet_" . $key_index . ".formElementOptionID=forKey." . $select . "
									AND
									radio_forOptDet_" . $key_index . ".languageID=forSub.languageID
								)";
                        $formElement[] = 'radio_forOptDet_' . $key_index . '.optionDesc AS ' . $select;
                        unset($formElement[$select]);
                    }
                }
            }
        }
        $sql = "
			SELECT
				formID,
				dateSubmitted,
				userAgent,
				submittedDataStatus,
				submittedIP,
				submittedLocation,
				forSub.submissionDetailsID,
				forKey.submissionID,
				(	
					SELECT 
						MIN(formSub.submissionDetailsID)
					FROM 
							" . DB_PREFIX . "form_" . $formKey . "  formKey 
						INNER JOIN 
							" . DB_PREFIX . "form_submissions formSub 
							ON 	(
								formSub.submissionDetailsID=formKey.submissionDetailsID
							)  
					WHERE 
						submittedDataStatus!='Deleted'
						AND
						formSub.submissionDetailsID > forKey.submissionDetailsID
				) AS next,
				(
					SELECT 
						MAX(formSub.submissionDetailsID)
					FROM 
							" . DB_PREFIX . "form_" . $formKey . "  formKey 
						INNER JOIN 
							" . DB_PREFIX . "form_submissions formSub 
							ON 	(
								formSub.submissionDetailsID=formKey.submissionDetailsID
							)  
					WHERE 
						submittedDataStatus!='Deleted'
						AND
						formSub.submissionDetailsID < forKey.submissionDetailsID
				) AS prev";
        if (!empty($formElement)) {
            $sql .= "," . implode(",", $formElement) . "";
        }
        $sql .= " FROM
					" . DB_PREFIX . "form_" . $formKey . " AS forKey
				INNER JOIN
					" . DB_PREFIX . "form_submissions AS forSub
						ON	(
							forKey.submissionDetailsID =forSub.submissionDetailsID
						)
				" . $sqlExt . "
			WHERE
				forKey.submissionDetailsID ='" . $submissionDetailsID . "'
				AND
				forSub.formID='" . $formID . "'
				AND
				submittedDataStatus!='Deleted'
			LIMIT 1
			";
        return $this->ObjDatabase->readValue($sql);
    }
    public function formChangeStatus($formID, $formStatus) {
        $validStatus = array(
            'Active',
            'Inactive',
            'Deleted',
            'Disabled'
        );
        if (in_array($formStatus, $validStatus)) {
            $sql = "
				UPDATE 
					" . DB_PREFIX . "form_entity 
				SET 
					status='" . $formStatus . "'
				WHERE 
					formID in (" . $formID . ")";
            $this->ObjDatabase->setQuery($sql);
        }
    }
    public function submissionChangeStatus($submissions, $submissionStatus) {
        $validStatus = array(
            'Active',
            'Inactive',
            'Deleted'
        );
        if (in_array($submissionStatus, $validStatus)) {
            $sql = "
				UPDATE 
					" . DB_PREFIX . "form_submissions 
				SET 
					submittedDataStatus='" . $submissionStatus . "'
				WHERE 
					submissionDetailsID in (" . $submissions . ")";
            $this->ObjDatabase->setQuery($sql);
        }
    }
    public function getOptionLabel($optionid, $elementID, $languageID, $attributeType) {
        if ($attributeType == 'country') {
            $sql = "
				SELECT 
					countryName AS label
				FROM 
					dp_country_details 
				WHERE 
					languageID='" . $languageID . "' 
					AND 
					countryID='" . $optionid . "'";
        } else if ($attributeType == 'state') {
            $sql = "
				SELECT 
					stateName AS label
				FROM 
					dp_state_details 
				WHERE 
					languageID='" . $languageID . "' 
					AND 
					stateID='" . $optionid . "'";
        } else if ($attributeType == 'businesstype') {
            $sql = "
				SELECT 
					businessTypeName AS label
				FROM 
					dp_business_type_details 
				WHERE 
					languageID='" . $languageID . "' 
					AND 
					businessTypeID='" . $optionid . "'";
        } else if ($attributeType == 'select' || $attributeType == 'checkbox' || $attributeType == 'radio') {
            $sql = "
				SELECT 
					optionDesc AS label
				FROM 
					dp_form_options_details 
				WHERE 
					formElementOptionID ='" . $optionid . "'
					AND 
					languageID='" . $languageID . "' ";
        } else {
            return $optionid;
        }
        $res = $this->ObjDatabase->readValue($sql);
        return $res['label'];
    }
    public function checkValueExists($params) {
        $formKey = $params['formKey'];
        $field   = $params['field'];
        $value   = $params['value'];
        $sql     = "
			SELECT EXISTS(
				SELECT 
					*
				FROM 
					" . DB_PREFIX . "form_" . $formKey . "
				WHERE
					" . $field . "='" . $value . "' 
				LIMIT 
					1
			) AS exist";
        return $this->ObjDatabase->readValue($sql);
    }
    public function listTables() {
        $sql = "SHOW TABLES";
        return $this->ObjDatabase->readValues($sql);
    }
    public function listTableField($table) {
        $sql = "SHOW COLUMNS FROM " . $table . "";
        return $this->ObjDatabase->readValues($sql);
    }
}
