PageRenderTime 28ms CodeModel.GetById 17ms RepoModel.GetById 0ms app.codeStats 0ms

/lms_debug/protected/controllers/CronController.php

https://gitlab.com/badelal143/lms_debug
PHP | 2546 lines | 2025 code | 385 blank | 136 comment | 325 complexity | e2db7cfa969ee7f00d2fd8d19ea249b1 MD5 | raw file
Possible License(s): LGPL-2.1, LGPL-3.0, LGPL-2.0 
    

  1. <?php 

    2. 

  2. class CronController extends Controller

    3. 

  3. {

    4. 

  4.   const SHARED_TITLE = "Admin Panel";

    5. 

  5. 	/**

    6. 

  6. 	 * Declares class-based actions.

    7. 

  7. 	 */

    8. 

  8. 	 	 public function filters()

    9. 

  9. 		{

    10. 

  10. 			//return array( 'accessControl' ); // perform access control for CRUD operations

    11. 

  11. 		}

    12. 

  12. 

    13. 

  13. 	public function actions()

    14. 

  14. 	{

    15. 

  15. 		return array(

    16. 

  16. 			// captcha action renders the CAPTCHA image displayed on the contact page

    17. 

  17. 			'captcha'=>array(

    18. 

  18. 				'class'=>'CCaptchaAction',

    19. 

  19. 				'backColor'=>0xFFFFFF,

    20. 

  20. 			),

    21. 

  21. 			// page action renders "static" pages stored under 'protected/views/site/pages'

    22. 

  22. 			// They can be accessed via: index.php?r=site/page&view=FileName

    23. 

  23. 			'page'=>array(

    24. 

  24. 				'class'=>'CViewAction',

    25. 

  25. 			),

    26. 

  26. 		);

    27. 

  27. 	}

    28. 

  28. 	

    29. 

  29.  	/**

    30. 

  30. 	 * It calls the actionCreateExcel to generates excel reports'

    31. 

  31. 	 * when an action is not explicitly requested by users.

    32. 

  32. 	 */

    33. 

  33. 	public function actionExportExcelReport()

    34. 

  34. 	{

    35. 

  35. 		// It calls the actionCreateExcel to generates excel reports'

    36. 

  36. 		@$getName=$_POST['LeadCronReport']['name'];

    37. 

  37. 		@$getStatus=$_POST['LeadCronReport']['status'];

    38. 

  38.         @$getStage=$_POST['LeadCronReport']['stage'];

    39. 

  39.         @$date1=$_POST['LeadCronReport']['period1'];

    40. 

  40. 		@$date2=$_POST['LeadCronReport']['period2'];

    41. 

  41. 		@$getleadSourceId=$_POST['LeadCronReport']['leadSourceId'];

    42. 

  42. 		@$getleadStatusId=$_POST['leadStatusId'];

    43. 

  43. 		@$datetype=@$_POST['LeadCronReport']['datetype']; 

    44. 

  44. 		if(isset($datetype) && !empty($datetype)){

    45. 

  45. 			$datetype=$datetype;

    46. 

  46. 		} else {

    47. 

  47. 			$datetype='updatedOn';

    48. 

  48. 		}

    49. 

  49. 		$isjunk = @$_REQUEST['isJunk'];

    50. 

  50. 		$isjunk = $isjunk?$isjunk:'no';

    51. 

  51. 		$this->actionCreateExcel($getName,$getStatus,$getStage,$date1,$date2,$getleadSourceId,$getleadStatusId,$datetype,$isjunk);

    52. 

  52. 		

    53. 

  53. 		

    54. 

  54. 	}

    55. 

  55. 	public function actionreports()	{

    56. 

  56. 	

    57. 

  57. 		// renders the view file 'protected/views/site/index.php'

    58. 

  58. 		// using the default layout 'protected/views/layouts/main.php'

    59. 

  59.      	$model=new LeadCronReport('search');

    60. 

  60. 		$model->scenario = 'LeadCronReport';

    61. 

  61. 		$teamId = '';

    62. 

  62. 		$teamUserId = '';

    63. 

  63. 		if(@$_POST['LeadCronReport'] !='' && isset($_POST['LeadCronReport'])){

    64. 

  64. 		@$primaryLeadStatus = @$_POST['leadStatusId'];

    65. 

  65. 		if(count($primaryLeadStatus) > 0){

    66. 

  66. 			$primaryLeadStatus = implode(",",$primaryLeadStatus);

    67. 

  67. 			$primaryDisposition=Yii::app()->db->createCommand("select leadStatus.leadStatus,leadStatusId from leadStatus where parentId IN (".@$primaryLeadStatus.") ")->queryAll();

    68. 

  68. 

    69. 

  69. 

    70. 

  70. 			if(count($primaryDisposition) > 0){

    71. 

  71. 			for($w=0;$w<count($primaryDisposition);$w++){

    72. 

  72. 			@$PrimaryDispositionIds[] = 	@$primaryDisposition[$w]['leadStatusId'];

    73. 

  73. 			}

    74. 

  74. 			} else {

    75. 

  75. 			@$PrimaryDispositionIds[] = '';

    76. 

  76. 			} 

    77. 

  77. 			@$PrimaryDispositionIds = implode(",",@$PrimaryDispositionIds);

    78. 

  78. 		} else {

    79. 

  79. 			@$PrimaryDispositionIds = ''; 

    80. 

  80. 		}

    81. 

  81. 			

    82. 

  82. 		$teamId = @$_POST['Team']['teamId'];

    83. 

  83. 		$teamUserId = @$_POST['Team']['teamMemberId'];

    84. 

  84. 		

    85. 

  85. 		if($teamUserId=='' or empty($teamUserId)){

    86. 

  86. 			$teamUsers=Yii::app()->db->createCommand("select userId from teamUserMapping where teamId = '".@$teamId."' ")->queryAll();

    87. 

  87. 			$reportsteamUsersIds = array();

    88. 

  88. 			foreach($teamUsers as $key=>$value){

    89. 

  89. 				$reportsteamUsersIds[] = $value['userId'];

    90. 

  90. 			}

    91. 

  91. 		} else {

    92. 

  92. 				$reportsteamUsersIds[] = $teamUserId;

    93. 

  93. 		}

    94. 

  94. 		

    95. 

  95. 		

    96. 

  96. 		$name=@$_POST['LeadCronReport']['name'];

    97. 

  97. 		$status=@$_POST['LeadCronReport']['status'];

    98. 

  98. 		$stage=@$_POST['LeadCronReport']['stage'];

    99. 

  99. 		/*$utmCode=@$_POST['LeadCronReport']['utmCode'];

    100. 

  100. 		$teleCaller=@$_POST['LeadCronReport']['teleCaller'];*/

    101. 

  101. 		$period1=@$_POST['LeadCronReport']['period1'];

    102. 

  102. 		$period2=@$_POST['LeadCronReport']['period2'];

    103. 

  103. 		$leadSourceId=@$_POST['LeadCronReport']['leadSourceId'];

    104. 

  104. 		$ws_agentid=@$_POST['LeadCronReport']['ws_agentid'];

    105. 

  105. 		$datetype=@$_POST['LeadCronReport']['datetype']; 

    106. 

  106. 		$isjunk = @$_REQUEST['isJunk'];

    107. 

  107. 		$isjunk = $isjunk?$isjunk:'no';

    108. 

  108. 		if(isset($datetype) && !empty($datetype)){

    109. 

  109. 			$datetype=$datetype;

    110. 

  110. 		} else {

    111. 

  111. 			$datetype='updatedOn';

    112. 

  112. 		}

    113. 

  113. 		//echo $datetype; die;

    114. 

  114. 				

    115. 

  115. 		$criteria = new CDbCriteria;

    116. 

  116.         $criteria->select = 't.*,`city`.`city`,`state`.`state`, `leadSourceMaster`.`leadSourceTitle`, `leadStageMaster`.`leadStage`,`leadStatus`.`leadStatus`,`customerTypeMaster`.`customerType`,`productMaster`.`productName`,`policyTypeMaster`.`policyType`,`planTypeMaster`.`planType`,`campaign`.`campaignName`';

    117. 

  117.         $criteria->join ='LEFT JOIN city ON (city.cityId = t.cityId) LEFT JOIN state ON (state.stateId = t.stateId) LEFT JOIN leadSourceMaster ON (leadSourceMaster.leadSourceId = t.leadSourceId) LEFT JOIN leadStageMaster ON (leadStageMaster.leadStageId = t.leadStageId)LEFT JOIN leadStatus ON (leadStatus.leadStatusId  = t.leadStatusId)LEFT JOIN customerTypeMaster ON (customerTypeMaster.customerTypeId  = t.customerTypeId)LEFT JOIN productMaster ON (productMaster.productId  = t.productId)LEFT JOIN policyTypeMaster ON (policyTypeMaster.policyTypeId  = t.policyTypeId)LEFT JOIN planTypeMaster ON (planTypeMaster.planTypeId  = t.planTypeId)LEFT JOIN campaign ON (campaign.campaignId  = t.campaignId)';

    118. 

  118. 	$criteria->order='leadId DESC';

    119. 

  119. 		if(count($reportsteamUsersIds) > 0){

    120. 

  120. 			$comd = implode(',',$reportsteamUsersIds);

    121. 

  121. 			$criteria->addCondition("t.currentAllocatedId IN (".$comd.") ");

    122. 

  122. 		}

    123. 

  123. 		if(isset($name) && !empty($name))

    124. 

  124. 		{/*LIKE  '$this->time_up%'";*/

    125. 

  125. 			$criteria->addCondition("t.name like '%".$name."'");

    126. 

  126. 		}

    127. 

  127. 		if(isset($status) && !empty($status))

    128. 

  128. 		{

    129. 

  129. 			$criteria->addCondition("t.leadStatusId =".$status);

    130. 

  130. 		}

    131. 

  131. 		if(isset($PrimaryDispositionIds) && !empty($PrimaryDispositionIds))

    132. 

  132. 		{

    133. 

  133. 			$criteria->addCondition("t.leadStatusId IN (".@$PrimaryDispositionIds.") ");

    134. 

  134. 		}

    135. 

  135. 		if(isset($stage) && !empty($stage))

    136. 

  136. 		{

    137. 

  137. 			$criteria->addCondition("t.leadStageId =".$stage);

    138. 

  138. 		}

    139. 

  139. 		if(isset($leadSourceId) && !empty($leadSourceId))

    140. 

  140. 		{

    141. 

  141. 			$criteria->addCondition("t.leadSourceId =".$leadSourceId);

    142. 

  142. 		}

    143. 

  143. 		if(isset($isjunk) && !empty($isjunk) && $isjunk=='yes' )

    144. 

  144. 		{

    145. 

  145. 			$criteria->addCondition("t.leadStatusId !=36");

    146. 

  146. 		}

    147. 

  147. 		if(isset($ws_agentid) && !empty($ws_agentid))

    148. 

  148. 		{

    149. 

  149. 			$criteria->addCondition("t.ws_agentid='".$ws_agentid."'");

    150. 

  150. 		}

    151. 

  151. 		if((isset($period1) && !empty($period1)) && (isset($period2) && !empty($period2)))

    152. 

  152. 		{   

    153. 

  153. 		   $period1=strtotime($period1);

    154. 

  154. 		    $period2=strtotime($period2)+86399;

    155. 

  155. 		  // echo date('d m y :: h: i: s: A',$period2); die;

    156. 

  156. 		  $criteria->addCondition('t.'.$datetype.' >= '.$period1.' AND t.'.$datetype.' <= '.$period2.'');

    157. 

  157. 		  // $criteria->addBetweenCondition('t.'.$datetype,$period1,$period2,'');

    158. 

  158. 			

    159. 

  159. 		}

    160. 

  160. 		/*echo "<pre>";

    161. 

  161. 		print_r($criteria);

    162. 

  162. 		die();*/

    163. 

  163. 		$model->set_name=@$_POST['LeadCronReport']['name'];

    164. 

  164. 		$model->set_stage=@$_POST['LeadCronReport']['stage'];

    165. 

  165. 		$model->set_status=@$_POST['LeadCronReport']['status'];

    166. 

  166. 		$model->set_period1=@$_POST['LeadCronReport']['period1'];

    167. 

  167. 		$model->set_period2=@$_POST['LeadCronReport']['period2'];

    168. 

  168. 		$model->set_leadStatusId=@$_POST['leadStatusId'];

    169. 

  169. 		$model->set_leadSourceId=@$_POST['LeadCronReport']['leadSourceId'];

    170. 

  170. 		$model->set_ws_agentid=@$_POST['LeadCronReport']['ws_agentid'];

    171. 

  171. 		$criteria->order='leadId DESC';

    172. 

  172. 		$dataProvider=new CActiveDataProvider('LeadCronReport', array('criteria'=>$criteria,'pagination'=>array(

    173. 

  173.                                         'pageSize'=>20,

    174. 

  174. 										'params' => array('period1' =>@$period1?date("d-m-Y",@$period1):'','period2' =>@$period2?date("d-m-Y",@$period2):''),

    175. 

  175.                                 ),));	

    176. 

  176. 								

    177. 

  177. 		$this->render('reports',array('model'=>$model,'dataProvider'=>$dataProvider,'teamId'=>$teamId,'teamUserId'=>$teamUserId));	

    178. 

  178. 		}else{

    179. 

  179. 			

    180. 

  180. 		$criteria = new CDbCriteria;

    181. 

  181.         $criteria->select = 't.*';

    182. 

  182.         $criteria->order='leadId DESC';

    183. 

  183. 	

    184. 

  184. 		$dataProvider=new CActiveDataProvider('LeadCronReport', array('criteria'=>$criteria,));	

    185. 

  185. 			$this->render('reports',array('model'=>$model,'dataProvider'=>$dataProvider,'teamId'=>$teamId,'teamUserId'=>$teamUserId));

    186. 

  186. 		}

    187. 

  187. 	}

    188. 

  188. /**

    189. 

  189. * This function is used to get column name from number of column

    190. 

  190. * calls from 'actionCreateExcel' function.

    191. 

  191. */

    192. 

  192. public function getNameFromNumber($num) {

    193. 

  193.     $numeric = $num % 26;

    194. 

  194.     $letter = chr(65 + $numeric);

    195. 

  195.     $num2 = intval($num / 26);

    196. 

  196.     if ($num2 > 0) {

    197. 

  197.         return getNameFromNumber($num2 - 1) . $letter;

    198. 

  198.     } else {

    199. 

  199.         return $letter;

    200. 

  200.     }

    201. 

  201. }

    202. 

  202. 	public function getPageType()

    203. 

  203. 	{

    204. 

  204. 		return "Reports";

    205. 

  205. 	}

    206. 

  206. 

    207. 

  207. /**

    208. 

  208. * This function is used to create excel file

    209. 

  209. * calls from 'actionreports' function.

    210. 

  210. */

    211. 

  211. public function actionCreateExcel($getName,$getStatus,$getStage,$date1,$date2,$getleadSourceId,$getleadStatusId,$datetype,$isjunk){

    212. 

  212. $paginationExcelValue = @$_POST['fetchrecordsBydropdown'];

    213. 

  213. 

    214. 

  214. if(isset($paginationExcelValue) && !empty($paginationExcelValue)){

    215. 

  215. 	$explodedpaginationValue = explode('-',$paginationExcelValue);

    216. 

  216. 	 $startLimit = $explodedpaginationValue[0];

    217. 

  217. 	 $endLimit = 10000;

    218. 

  218. } else {

    219. 

  219. 	 $startLimit = '';

    220. 

  220. 	 $endLimit = '';

    221. 

  221. 

    222. 

  222. }

    223. 

  223. if(isset($startLimit) && isset($endLimit) && !empty($endLimit)){

    224. 

  224. 	$limitCond = " limit $startLimit , $endLimit";

    225. 

  225. } else {

    226. 

  226. 	$limitCond = '';

    227. 

  227. }

    228. 

  228. //Here is the code to set conditions for fetch records from database

    229. 

  229. $condfilter = '';

    230. 

  230. 

    231. 

  231. if((isset($getName)&& (!empty($getName))) || (isset($getStatus)&& (!empty($getStatus))) || (isset($getStage)&& (!empty($getStage)))|| (isset($date1)&& (!empty($date1))) || (isset($date2)&& (!empty($date2))))

    232. 

  232. 		{	

    233. 

  233. 		

    234. 

  234. 		if(isset($getName) && !empty($getName))

    235. 

  235. 		{

    236. 

  236. 			$condfilter .= "t.name like '%".$getName."' AND ";

    237. 

  237. 		}

    238. 

  238. 		if(isset($getStatus) && !empty($getStatus))

    239. 

  239. 		{

    240. 

  240. 			$condfilter .= "t.leadStatusId =".$getStatus." AND ";

    241. 

  241. 		}

    242. 

  242. 		if(isset($getStage) && !empty($getStage))

    243. 

  243. 		{

    244. 

  244. 			$condfilter .= "t.leadStageId =".$getStage." AND ";

    245. 

  245. 		}

    246. 

  246. 		if(isset($getleadSourceId) && !empty($getleadSourceId))

    247. 

  247. 		{

    248. 

  248. 			$condfilter .= "t.leadSourceId =".$getleadSourceId." AND ";

    249. 

  249. 		}

    250. 

  250. 		if(isset($isjunk) && !empty($isjunk))

    251. 

  251. 		{

    252. 

  252. 			$condfilter .= "t.leadStatusId != 36 AND ";

    253. 

  253. 		}

    254. 

  254. 		if((isset($date1) && !empty($date1)) && (isset($date2) && !empty($date2)))

    255. 

  255. 		{   

    256. 

  256. 		   $date1=strtotime($date1);

    257. 

  257. 		   $date2=strtotime($date2)+86399;

    258. 

  258. 		   $condfilter .= "t.".$datetype." >=".$date1." AND t.".$datetype." <= ".$date2." AND ";

    259. 

  259. 		}

    260. 

  260. 		if(isset($condfilter) && !empty($condfilter)){

    261. 

  261. 			$wherecon = "WHERE ";

    262. 

  262. 		} else {

    263. 

  263. 			$wherecon = "";

    264. 

  264. 		}

    265. 

  265. 		$data=Yii::app()->db->createCommand("select t.leadId,t.name,t.mobile,t.email,t.members,t.premium,t.productDetails,t.ws_agentid,t.status,t.isJunk,t.leadDescription,t.currentAllocatedId,t.createdBy,t.updatedOn,t.jsonAllData from leads_cron as t $wherecon $condfilter t.leadId > 0 order by t.leadId DESC $limitCond")->queryAll();

    266. 

  266. 	

    267. 

  267. 		}else{

    268. 

  268. 		

    269. 

  269. 		if(isset($getleadSourceId) && !empty($getleadSourceId))

    270. 

  270. 		{

    271. 

  271. 			$condfilter .= "t.leadSourceId =".$getleadSourceId." AND";

    272. 

  272. 		}

    273. 

  273. 		if(count($getleadStatusId) > 0){

    274. 

  274. 			$getleadStatusId = implode(",",$getleadStatusId);

    275. 

  275. 			$primaryDisposition=Yii::app()->db->createCommand("select leadStatus.leadStatus,leadStatusId from leadStatus where parentId IN (".@$getleadStatusId.") ")->queryAll();

    276. 

  276. 

    277. 

  277. 

    278. 

  278. 			if(count($primaryDisposition) > 0){

    279. 

  279. 			for($w=0;$w<count($primaryDisposition);$w++){

    280. 

  280. 			@$PrimaryDispositionIds[] = 	@$primaryDisposition[$w]['leadStatusId'];

    281. 

  281. 			}

    282. 

  282. 			} else {

    283. 

  283. 			@$PrimaryDispositionIds[] = '';

    284. 

  284. 			} 

    285. 

  285. 			@$PrimaryDispositionIds = implode(",",@$PrimaryDispositionIds);

    286. 

  286. 		} else {

    287. 

  287. 			@$PrimaryDispositionIds = ''; 

    288. 

  288. 		}

    289. 

  289. 		if(isset($PrimaryDispositionIds) && !empty($PrimaryDispositionIds))

    290. 

  290. 		{

    291. 

  291. 			$condfilter .= "t.leadStatusId IN (".@$PrimaryDispositionIds.") AND ";

    292. 

  292. 			

    293. 

  293. 		}

    294. 

  294. 		

    295. 

  295. 		if(isset($condfilter) && !empty($condfilter)){

    296. 

  296. 			$wherecon = "WHERE ";

    297. 

  297. 		} else {

    298. 

  298. 			$wherecon = "";

    299. 

  299. 		}

    300. 

  300. 		$data=Yii::app()->db->createCommand("select t.leadId,t.name,t.mobile,t.email,t.members,t.premium,t.productDetails,t.ws_agentid,t.status,t.isJunk,t.leadDescription,t.currentAllocatedId,t.createdBy,t.updatedOn,t.jsonAllData from leads_cron as t $wherecon $condfilter t.leadId > 0 order by t.leadId DESC $limitCond ")->queryAll();

    301. 

  301. 

    302. 

  302. }

    303. 

  303. 

    304. 

  304. 		

    305. 

  305. if(count($data)>0){

    306. 

  306.  $file_name = "lms" . "_" . date("Y-m-d_H-i", time()) . ".csv";

    307. 

  307.         header('Content-Type: text/html; charset=utf-8');

    308. 

  308.         header("Content-type: application/octet-stream");

    309. 

  309.         header("Content-Disposition: attachment; filename=\"$file_name\"");

    310. 

  310.         header("Cache-Control: cache, must-revalidate");

    311. 

  311.         header("Pragma: public");

    312. 

  312.          $col = array(

    313. 

  313.           'SNO.','Name','Mobile Number',' Email Id','Address','City','State','Members','Premium','Product Details','Lead Status','Status','isJunk','Lead Source','Lead Stage','Lead Description','Activity Description','Customer Type','Product Name','Policy Type','Plan Type','Campaign Name','UTM Code','Owner','Last Modified Date','Agent-Id');

    314. 

  314. 

    315. 

  315.           foreach ($col as $key => $value) {

    316. 

  316.             echo $value . ",";

    317. 

  317.         }

    318. 

  318.         echo "\n";

    319. 

  319. 			$i=1;

    320. 

  320. 			

    321. 

  321.             foreach ($data as $key => $value) {

    322. 

  322. 			

    323. 

  323. 			$jsonArray = json_decode($value['jsonAllData']);

    324. 

  324. 			 $state = $jsonArray->state;

    325. 

  325. 			 $city = $jsonArray->city;

    326. 

  326. 			 $product = $jsonArray->product;

    327. 

  327. 			 $leadStatus = $jsonArray->leadStatus;

    328. 

  328. 			 $leadSource = $jsonArray->leadSource;

    329. 

  329. 			 $leadStage = $jsonArray->leadStage;

    330. 

  330. 			 $ActivityDescription = $jsonArray->ActivityDescription;

    331. 

  331. 			 $owner = $jsonArray->owner;

    332. 

  332. 			 $customerType = $jsonArray->customerType;

    333. 

  333. 			 $policyType = $jsonArray->policyType;

    334. 

  334. 			 $planType = $jsonArray->planType;

    335. 

  335. 			 $campaign = $jsonArray->campaign;

    336. 

  336. 			

    337. 

  337. 			

    338. 

  338. //			$sqlDesc = "select group_concat(description) as ActivityDescription from leadFollowup where leadId=".@$value["leadId"]." "; 

    339. 

  339. //			$dataDesc=Yii::app()->db->createCommand($sqlDesc)->queryAll();

    340. 

  340. 			

    341. 

  341. //			$leadActivity = @$dataDesc[0]['ActivityDescription']?str_replace(",","/",@$dataDesc[0]['ActivityDescription']):'';

    342. 

  342. 			if($value['currentAllocatedId']==$value['createdBy']){

    343. 

  343. 				$owner = $owner."(C)";

    344. 

  344. 			} else {

    345. 

  345. 				$owner = $owner;

    346. 

  346. 			}

    347. 

  347. 			$lastModifieddate = date("d-M-Y  H-i-s A",$value['updatedOn']);

    348. 

  348. 

    349. 

  349. 			

    350. 

  350. 				echo $i++ . ",";

    351. 

  351. 				echo stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$value["name"])) . ",";

    352. 

  352. 				echo stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ",@$value["mobile"])) . ",";

    353. 

  353. 				echo stripslashes(preg_replace("/^'|[^A-Za-z0-9@._\'-]|'$/", " ",@$value["email"])) . ",";

    354. 

  354. 				echo stripslashes(preg_replace("/^'|[^A-Za-z0-9@._\'-]|'$/", " ",@$value["address"])) . ",";

    355. 

  355. 				echo stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ",@$city)) . ",";

    356. 

  356. 				echo stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ",@$state)) . ",";

    357. 

  357. 				echo stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ",@$value["members"])) . ",";

    358. 

  358. 				echo stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ",@$value["premium"])) . ",";

    359. 

  359. 				echo stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ",@$value['productDetails'])) . ",";

    360. 

  360. 				echo stripslashes(preg_replace("/^'|[^A-Za-z0-9@._\'-]|'$/", " ",@$leadStatus)) . ",";

    361. 

  361. 				echo stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ",@$value["status"])) . ",";

    362. 

  362. 				echo stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ",@$value["isJunk"])) . ",";

    363. 

  363. 				echo stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ",@$leadSource)) . ",";

    364. 

  364. 				echo stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ",@$leadStage)) . ",";

    365. 

  365. 				echo stripslashes(preg_replace("/^'|[^A-Za-z0-9@._\'-]|'$/", " ",@$value["leadDescription"])) . ",";

    366. 

  366. 				echo stripslashes(preg_replace("/^'|[^A-Za-z0-9@._\'-]|'$/", " ",@$ActivityDescription)) . ",";

    367. 

  367. 				echo stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ",@$customerType)) . ",";

    368. 

  368. 				echo stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ",@$product)) . ",";

    369. 

  369. 				echo stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ",@$policyType)) . ",";

    370. 

  370. 				echo stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ",@$planType)) . ",";

    371. 

  371. 				echo stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ",@$campaign)) . ",";

    372. 

  372. 				echo stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ",@$value["leadSourceCode"])) . ",";

    373. 

  373. 				echo stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ",@$owner)) . ",";

    374. 

  374. 				echo stripslashes(preg_replace("/^'|[^A-Za-z0-9@._\'-]|'$/", " ",@$lastModifieddate)) . ",";

    375. 

  375. 				echo stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ",@$value["ws_agentid"])) . ",";

    376. 

  376.             echo "\n";

    377. 

  377.         }

    378. 

  378. 

    379. 

  379. }

    380. 

  380. else{

    381. 

  381. 		$this->redirect('index.php?r=reports/reports');

    382. 

  382. 		//$dataProvider=new CActiveDataProvider('LeadCronReport', array('criteria'=>@$criteria,));	

    383. 

  383. 		//$this->render('reports',array('model'=>$model,'dataProvider'=>$dataProvider));

    384. 

  384. }

    385. 

  385. }

    386. 

  386. 

    387. 

  387. 

    388. 

  388. 	public function getLeadDescription($leadId){

    389. 

  389. 		$records=Yii::app()->db->createCommand("select leadDescription  from lead where leadId='".@$leadId."' ")->queryAll();

    390. 

  390. 		

    391. 

  391. 		if(count($records) > 0) {

    392. 

  392. 		 for($i=0;$i<count($records);$i++){ 

    393. 

  393. 		 	$record[] = @$records[$i]['leadDescription'];

    394. 

  394. 		 }

    395. 

  395. 		} else {

    396. 

  396. 		echo "";

    397. 

  397. 		}

    398. 

  398. 		

    399. 

  399. 		if( count(@$record) > 1){

    400. 

  400. 			echo "<label  title='".implode(',',$record)."'><a href='#'>".substr(implode(',',$record),0,5)."...</a></label>";

    401. 

  401. 		

    402. 

  402. 		} else if( count(@$record) == 1)   {

    403. 

  403. 			echo "<label title='".implode(',',@$record)."'><a href='#'>".implode(',',$record)."</a></label>";

    404. 

  404. 		} else {

    405. 

  405. 			echo "";

    406. 

  406. 		}

    407. 

  407. 		//echo explode(',',$agentId);

    408. 

  408. 	}	

    409. 

  409. 	public function getLeadActivityDescription($leadId){

    410. 

  410. 		$records=Yii::app()->db->createCommand("select description from leadFollowup where leadId='".@$leadId."' ")->queryAll();

    411. 

  411. 		if(count($records) > 0) {

    412. 

  412. 		 for($i=0;$i<count($records);$i++){ 

    413. 

  413. 		 	$record[] = @$records[$i]['description'];

    414. 

  414. 		 }

    415. 

  415. 		} else {

    416. 

  416. 		echo "";

    417. 

  417. 		}

    418. 

  418. 		

    419. 

  419. 		if( count(@$record) > 1){

    420. 

  420. 			echo "<label  title='".implode(',',$record)."'><a href='#'>".substr(implode(',',$record),0,5)."...</a></label>";

    421. 

  421. 		

    422. 

  422. 		} else if( count(@$record) == 1)   {

    423. 

  423. 			echo "<label title='".implode(',',@$record)."'><a href='#'>".substr(implode(',',$record),0,5)."...</a></label>";

    424. 

  424. 		} else {

    425. 

  425. 			echo "";

    426. 

  426. 		}

    427. 

  427. 		

    428. 

  428. 		//echo explode(',',$agentId);

    429. 

  429. 	}	

    430. 

  430. 	

    431. 

  431. 	public function getLeadActivityDescriptionExcel($leadId){

    432. 

  432. 		$records=Yii::app()->db->createCommand("select description from leadFollowup where leadId='".@$leadId."' ")->queryAll();

    433. 

  433. 		if(count($records) > 0) {

    434. 

  434. 		 for($i=0;$i<count($records);$i++){ 

    435. 

  435. 		 	$record[] = @$records[$i]['description'];

    436. 

  436. 		 }

    437. 

  437. 		} else {

    438. 

  438. 		echo "";

    439. 

  439. 		}

    440. 

  440. 		

    441. 

  441. 		if( count(@$record) > 1){

    442. 

  442. 			echo implode(',',$record);

    443. 

  443. 		

    444. 

  444. 		} else if( count(@$record) == 1)   {

    445. 

  445. 			echo implode(',',$record);

    446. 

  446. 		} else {

    447. 

  447. 			echo "";

    448. 

  448. 		}

    449. 

  449. 		

    450. 

  450. 		//echo explode(',',$agentId);

    451. 

  451. 	}	

    452. 

  452. 

    453. 

  453. public function actionCheckanddelete(){

    454. 

  454. 	$reportmodel=Lead::model()->findAll();

    455. 

  455. 	print_r($reportmodel);

    456. 

  456. 	die;

    457. 

  457. }

    458. 

  458. function actioncroneJobForReport(){

    459. 

  459. ini_set('max_input_time', 1200);

    460. 

  460. ini_set('max_execution_time', 1200);

    461. 

  461. 

    462. 

  462. $sourcewiseDetails=Yii::app()->db->createCommand("select * from leads_cron_history where id='lead_updated_on' ")->queryAll();

    463. 

  463. $sourcewiseDetails[0]['updatedOn'];

    464. 

  464. 

    465. 

  465. 

    466. 

  466.   $queryUpdatedOn = "select t.*,`user`.`id`,`user`.`firstName`,`user`.`lastName`,`city`.`city`, `state`.`state`, `leadSourceMaster`.`leadSourceTitle`,`leadSourceMaster`.`leadSourceCode`,`leadStageMaster`.`leadStage`,`leadStatus`.`leadStatus`, `customerTypeMaster`.`customerType`,`productMaster`.`productName`,`policyTypeMaster`.`policyType`,`planTypeMaster`.`planType`,`campaign`.`campaignName`  from lead as t LEFT JOIN city ON (city.cityId = t.cityId) LEFT JOIN state ON (state.stateId = t.stateId) LEFT JOIN leadSourceMaster ON (leadSourceMaster.leadSourceId = t.leadSourceId) LEFT JOIN leadStageMaster ON (leadStageMaster.leadStageId = t.leadStageId)LEFT JOIN leadStatus ON (leadStatus.leadStatusId  = t.leadStatusId)LEFT JOIN customerTypeMaster ON (customerTypeMaster.customerTypeId  = t.customerTypeId)LEFT JOIN productMaster ON (productMaster.productId  = t.productId) LEFT JOIN policyTypeMaster ON (policyTypeMaster.policyTypeId  = t.policyTypeId)LEFT JOIN planTypeMaster ON (planTypeMaster.planTypeId  = t.planTypeId) LEFT JOIN campaign ON (campaign.campaignId  = t.campaignId) LEFT JOIN user ON (user.id=t.currentAllocatedId) where (t.updatedOn > ".@$sourcewiseDetails[0]['updatedOn']." || t.createdOn > ".@$sourcewiseDetails[0]['updatedOn']." )  order by t.leadId DESC "; 

    467. 

  467. 

    468. 

  468. 		$dataUpdatedOn=Yii::app()->db->createCommand($queryUpdatedOn)->queryAll();

    469. 

  469. 

    470. 

  470.  $updateleads_cron_history = " update leads_cron_history set updated_ts = '".date('Y-m-d H:i:s')."',updatedOn = '".time()."' where id='lead_updated_on' ";

    471. 

  471. $executeQueryder=Yii::app()->db->createCommand($updateleads_cron_history)->execute();

    472. 

  472. 

    473. 

  473. 

    474. 

  474.  //$queryCreatedOn = "select t.leadId,t.name,t.mobile,t.email,t.members,t.premium,t.productDetails,t.ws_agentid,t.status,t.isJunk,t.leadDescription,t.currentAllocatedId,t.createdBy,t.updatedOn,`user`.`id`,`user`.`firstName`,`user`.`lastName`,`city`.`city`, `state`.`state`, `leadSourceMaster`.`leadSourceTitle`,`leadSourceMaster`.`leadSourceCode`,`leadStageMaster`.`leadStage`,`leadStatus`.`leadStatus`, `customerTypeMaster`.`customerType`,`productMaster`.`productName`,`policyTypeMaster`.`policyType`,`planTypeMaster`.`planType`,`campaign`.`campaignName`  from lead as t LEFT JOIN city ON (city.cityId = t.cityId) LEFT JOIN state ON (state.stateId = t.stateId) LEFT JOIN leadSourceMaster ON (leadSourceMaster.leadSourceId = t.leadSourceId) LEFT JOIN leadStageMaster ON (leadStageMaster.leadStageId = t.leadStageId)LEFT JOIN leadStatus ON (leadStatus.leadStatusId  = t.leadStatusId)LEFT JOIN customerTypeMaster ON (customerTypeMaster.customerTypeId  = t.customerTypeId)LEFT JOIN productMaster ON (productMaster.productId  = t.productId) LEFT JOIN policyTypeMaster ON (policyTypeMaster.policyTypeId  = t.policyTypeId)LEFT JOIN planTypeMaster ON (planTypeMaster.planTypeId  = t.planTypeId) LEFT JOIN campaign ON (campaign.campaignId  = t.campaignId) LEFT JOIN user ON (user.id=t.currentAllocatedId) where t.leadId <= 2 and createdOn > ".@$sourcewiseDetails[0]['updatedOn']."   order by t.leadId DESC "; 

    475. 

  475. 

    476. 

  476. 		//$dataCreatedOn=Yii::app()->db->createCommand($queryCreatedOn)->queryAll();

    477. 

  477. 

    478. 

  478. 

    479. 

  479. $insertcount=0;

    480. 

  480. $updatecount=0;

    481. 

  481. 

    482. 

  482. if(count($dataUpdatedOn) > 0){

    483. 

  483. 

    484. 

  484. 	 foreach ($dataUpdatedOn as $key => $value) {

    485. 

  485. 		 

    486. 

  486. 		$owner = @$value['firstName']?(@$value['firstName']." ".@$value['lastName']):"NA";

    487. 

  487. 		$sqlDesc = "select group_concat(description) as ActivityDescription from renewalleadFollowup where leadId=".@$value["leadId"]." "; 

    488. 

  488. 		$dataDesc=Yii::app()->db->createCommand($sqlDesc)->queryAll();

    489. 

  489. 		

    490. 

  490. 		$leadActivity = @$dataDesc[0]['ActivityDescription']?str_replace(",",":",@$dataDesc[0]['ActivityDescription']):'';

    491. 

  491. 	

    492. 

  492. 	

    493. 

  493. 	

    494. 

  494. 	

    495. 

  495. 	 

    496. 

  496. 	 $state 		 	=  @$value["state"]?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$value["state"])):'NA';

    497. 

  497. 	 $city 				=  @$value["city"]?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$value["city"])):'NA';

    498. 

  498. 	 $customerType 		=  @$value["customerType"]?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$value["customerType"])):'NA';

    499. 

  499. 	 $productName 		=  @$value["productName"]?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$value["productName"])):'NA';

    500. 

  500. 	 $policyType 		=  @$value["policyType"]?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$value["policyType"])):'NA';

    501. 

  501. 	 $planType 			=  @$value["planType"]?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$value["planType"])):'NA';

    502. 

  502. 	 $campaignName 		=  @$value["campaignName"]?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$value["campaignName"])):'NA';

    503. 

  503. 	 $leadSourceTitle 	=  @$value["leadSourceTitle"]?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$value["leadSourceTitle"])):'NA';

    504. 

  504. 	 $leadStatus 		=  @$value["leadStatus"]?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$value["leadStatus"])):'NA';

    505. 

  505. 	 $leadStage 		=  @$value["leadStage"]?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$value["leadStage"])):'NA';

    506. 

  506. 	 $owner 			=  @$owner?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$owner)):'NA';

    507. 

  507. 	 $agntName 			=  @$value["agntName"]?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$value["agntName"])):'NA';

    508. 

  508. 	 $leadActivity 		=  @$leadActivity?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$leadActivity)):'NA';

    509. 

  509. 	

    510. 

  510. 	

    511. 

  511. 	

    512. 

  512. 		$newarray = array(

    513. 

  513. 					'state' => $state,

    514. 

  514. 					'city' => $city,

    515. 

  515. 					'customerType' => $customerType,

    516. 

  516. 					'product' => $productName,

    517. 

  517. 					'policyType' => $policyType,

    518. 

  518. 					'planType' => $planType,

    519. 

  519. 					'campaign' => $campaignName,

    520. 

  520. 					'leadSource' => $leadSourceTitle,

    521. 

  521. 					'leadStatus' => $leadStatus,

    522. 

  522. 					'leadStage' => $leadStage,

    523. 

  523. 					'owner' => addslashes($owner),

    524. 

  524. 					'agntName' => $agntName,

    525. 

  525. 					'ActivityDescription' => str_replace("'","`",$leadActivity)

    526. 

  526. 			);

    527. 

  527. 			$jsonData = json_encode($newarray);

    528. 

  528. 			

    529. 

  529. 			//$insertQuery = " insert into leads_cron (leadId,name,email,emailOptOut,mobile,mobileOptOut,address,stateId,cityId,customerTypeId,productId,policyTypeId,planTypeId,campaignId,members,si,premium,productDetails,ageGroup,children,leadSourceId,leadStatusId,leadStageId,isJunk,currentAllocatedId,currentAllocatedDate,isDuplicate,duplicateLeadId,age,dateonSubmit,fileName,leadDescription,isRead,status,ipaddress,agentId,createdOn,createdBy,updatedOn,updatedBy,ws_sourcecode,ws_subject,ws_telephone,ws_plan,ws_product,ws_quoteid,ws_proposalid,ws_leadstage,ws_agentid,ws_noofyears,ws_orgnaizationName,utmsource,isBulkImport,salesClosedUpdatedOn,jsonAllData,jsonDataUpdatedOn,jsonDataUpdatedBy) values (".$value['leadId'].",".$value['name'].",".$value['email'].",".$value['emailOptOut'].",".$value['mobile'].",".$value['mobileOptOut'].",".$value['address'].",".$value['stateId'].",".$value['cityId'].",".$value['customerTypeId'].",".$value['productId'].",".$value['policyTypeId'].",".$value['planTypeId'].",".$value['campaignId'].",".$value['members'].",".$value['si'].",".$value['premium'].",".$value['productDetails'].",".$value['ageGroup'].",".$value['children'].",".$value['leadSourceId'].",".$value['leadStatusId'].",".$value['leadStageId'].",".$value['isJunk'].",".$value['currentAllocatedId'].",".$value['currentAllocatedDate'].",".$value['isDuplicate'].",".$value['duplicateLeadId'].",".$value['age'].",".$value['dateonSubmit'].",".$value['fileName'].",".$value['leadDescription'].",".$value['isRead'].",".$value['status'].",".$value['ipaddress'].",".$value['agentId'].",".$value['createdOn'].",".$value['createdBy'].",".$value['updatedOn'].",".$value['updatedBy'].",".$value['ws_sourcecode'].",".$value['ws_subject'].",".$value['ws_telephone'].",".$value['ws_plan'].",".$value['ws_product'].",".$value['ws_quoteid'].",".$value['ws_proposalid'].",".$value['ws_leadstage'].",".$value['ws_agentid'].",".$value['ws_noofyears'].",".$value['ws_orgnaizationName'].",".$value['utmsource'].",".$value['isBulkImport'].",".$value['salesClosedUpdatedOn'].",'".$jsonData."',".$value['jsonDataUpdatedOn'].",".$value['jsonDataUpdatedBy'].") ";

    530. 

  530. 			

    531. 

  531. 			if($value['createdOn'] >= $sourcewiseDetails[0]['updatedOn']){

    532. 

  532. 	 $insertQuery = " insert into leads_cron (leadId,name,email,emailOptOut,mobile,mobileOptOut,address,stateId,cityId,customerTypeId,productId,policyTypeId,planTypeId,campaignId,members,si,premium,productDetails,ageGroup,children,leadSourceId,leadStatusId,leadStageId,isJunk,currentAllocatedId,currentAllocatedDate,isDuplicate,duplicateLeadId,age,dateonSubmit,fileName,leadDescription,isRead,status,ipaddress,agentId,createdOn,createdBy,updatedOn,updatedBy,ws_sourcecode,ws_subject,ws_telephone,ws_plan,ws_product,ws_quoteid,ws_proposalid,ws_leadstage,ws_agentid,ws_noofyears,ws_orgnaizationName,utmsource,isBulkImport,salesClosedUpdatedOn,jsonAllData,jsonDataUpdatedOn,jsonDataUpdatedBy) values ('".$value['leadId']."','".addslashes($value['name'])."','".$value['email']."','".$value['emailOptOut']."','".$value['mobile']."','".$value['mobileOptOut']."','".addslashes($value['address'])."','".$value['stateId']."','".$value['cityId']."','".$value['customerTypeId']."','".$value['productId']."','".$value['policyTypeId']."','".$value['planTypeId']."','".$value['campaignId']."','".addslashes($value['members'])."','".addslashes($value['si'])."','".addslashes($value['premium'])."','".addslashes($value['productDetails'])."','".$value['ageGroup']."','".$value['children']."','".$value['leadSourceId']."','".$value['leadStatusId']."','".$value['leadStageId']."','".$value['isJunk']."','".$value['currentAllocatedId']."','".$value['currentAllocatedDate']."','".$value['isDuplicate']."','".$value['duplicateLeadId']."','".$value['age']."','".$value['dateonSubmit']."','".$value['fileName']."','".addslashes($value['leadDescription'])."','".$value['isRead']."','".$value['status']."','".$value['ipaddress']."','".$value['agentId']."','".$value['createdOn']."','".$value['createdBy']."','".$value['updatedOn']."','".$value['updatedBy']."','".$value['ws_sourcecode']."','".$value['ws_subject']."','".$value['ws_telephone']."','".$value['ws_plan']."','".$value['ws_product']."','".$value['ws_quoteid']."','".$value['ws_proposalid']."','".$value['ws_leadstage']."','".$value['ws_agentid']."','".$value['ws_noofyears']."','".$value['ws_orgnaizationName']."','".$value['utmsource']."','".$value['isBulkImport']."','".$value['salesClosedUpdatedOn']."','".$jsonData."','".time()."','amit') ";

    533. 

  533. 			$executeQuery4=Yii::app()->db->createCommand($insertQuery)->execute();

    534. 

  534. 			//echo "<br><br>" .$insertQuery;

    535. 

  535. 			$insertcount++;

    536. 

  536. 

    537. 

  537. 			

    538. 

  538. 			} else if($value['updatedOn'] >= $sourcewiseDetails[0]['updatedOn']){

    539. 

  539. 		 $updateQuery = " update leads_cron set leadId='".$value['leadId']."',name='".addslashes($value['name'])."',email='".$value['email']."',emailOptOut='".$value['emailOptOut']."',mobile='".$value['mobile']."',mobileOptOut='".$value['mobileOptOut']."',address='".addslashes($value['address'])."',stateId='".$value['stateId']."',cityId='".$value['cityId']."',customerTypeId='".$value['customerTypeId']."',productId='".$value['productId']."',policyTypeId='".$value['policyTypeId']."',planTypeId='".$value['planTypeId']."',campaignId='".$value['campaignId']."',members='".addslashes($value['members'])."',si='".addslashes($value['si'])."',premium='".addslashes($value['premium'])."',productDetails='".addslashes($value['productDetails'])."',ageGroup='".$value['ageGroup']."',children='".$value['children']."',leadSourceId='".$value['leadSourceId']."',leadStatusId='".$value['leadStatusId']."',leadStageId='".$value['leadStageId']."',isJunk='".$value['isJunk']."',currentAllocatedId='".$value['currentAllocatedId']."',currentAllocatedDate='".$value['currentAllocatedDate']."',isDuplicate='".$value['isDuplicate']."',duplicateLeadId='".$value['duplicateLeadId']."',age='".$value['age']."',dateonSubmit='".$value['dateonSubmit']."',fileName='".$value['fileName']."',leadDescription='".addslashes($value['leadDescription'])."',isRead='".$value['isRead']."',status='".$value['status']."',ipaddress='".$value['ipaddress']."',agentId='".$value['agentId']."',createdOn='".$value['createdOn']."',createdBy='".$value['createdBy']."',updatedOn='".$value['updatedOn']."',updatedBy='".$value['updatedBy']."',ws_sourcecode='".$value['ws_sourcecode']."',ws_subject='".$value['ws_subject']."',ws_telephone='".$value['ws_telephone']."',ws_plan='".$value['ws_plan']."',ws_product='".$value['ws_product']."',ws_quoteid='".$value['ws_quoteid']."',ws_proposalid='".$value['ws_proposalid']."',ws_leadstage='".$value['ws_leadstage']."',ws_agentid='".$value['ws_agentid']."',ws_noofyears='".$value['ws_noofyears']."',ws_orgnaizationName='".$value['ws_orgnaizationName']."',utmsource='".$value['utmsource']."',isBulkImport='".$value['isBulkImport']."',salesClosedUpdatedOn='".$value['salesClosedUpdatedOn']."',jsonAllData='".$jsonData."',jsonDataUpdatedOn='".time()."',jsonDataUpdatedBy='amit'  where leadId = ".@$value['leadId']." ";

    540. 

  540. 			$executeQuery2=Yii::app()->db->createCommand($updateQuery)->execute();

    541. 

  541. 			//echo "<br><br>" .$updateQuery;

    542. 

  542. 			$updatecount++;

    543. 

  543. 					

    544. 

  544. 			}

    545. 

  545. 					

    546. 

  546. 	 }

    547. 

  547. 	 

    548. 

  548. 	}

    549. 

  549. 	 echo "insert count=".$insertcount." and update count=".$updatecount;

    550. 

  550.  	

    551. 

  551. }

    552. 

  552. function actioncroneFirstTime(){

    553. 

  553. $startId = @$_REQUEST['startId'];

    554. 

  554. $endId = @$_REQUEST['endId'];

    555. 

  555. if(empty($startId) || empty($endId)){

    556. 

  556. echo "parameter missing";

    557. 

  557. die;

    558. 

  558. }

    559. 

  559. if($startId > $endId){

    560. 

  560. echo "start Limit can not be greater or equal to end Limit";

    561. 

  561. die;

    562. 

  562. }

    563. 

  563.  $query = "select t.*,`user`.`id`,`user`.`firstName`,`user`.`lastName`,`city`.`city`, `state`.`state`, `leadSourceMaster`.`leadSourceTitle`,`leadSourceMaster`.`leadSourceCode`,`leadStageMaster`.`leadStage`,`leadStatus`.`leadStatus`, `customerTypeMaster`.`customerType`,`productMaster`.`productName`,`policyTypeMaster`.`policyType`,`planTypeMaster`.`planType`,`campaign`.`campaignName`  from lead as t LEFT JOIN city ON (city.cityId = t.cityId) LEFT JOIN state ON (state.stateId = t.stateId) LEFT JOIN leadSourceMaster ON (leadSourceMaster.leadSourceId = t.leadSourceId) LEFT JOIN leadStageMaster ON (leadStageMaster.leadStageId = t.leadStageId)LEFT JOIN leadStatus ON (leadStatus.leadStatusId  = t.leadStatusId)LEFT JOIN customerTypeMaster ON (customerTypeMaster.customerTypeId  = t.customerTypeId)LEFT JOIN productMaster ON (productMaster.productId  = t.productId) LEFT JOIN policyTypeMaster ON (policyTypeMaster.policyTypeId  = t.policyTypeId)LEFT JOIN planTypeMaster ON (planTypeMaster.planTypeId  = t.planTypeId) LEFT JOIN campaign ON (campaign.campaignId  = t.campaignId) LEFT JOIN user ON (user.id=t.currentAllocatedId) where t.leadId >= $startId and t.leadId <= $endId  "; 

    564. 

  564. 		$data=Yii::app()->db->createCommand($query)->queryAll();

    565. 

  565. 

    566. 

  566.  $updateleads_cron_history = " update leads_cron_history set updated_ts = '".date('Y-m-d H:i:s')."',updatedOn = '".time()."' where id='lead_updated_on' ";

    567. 

  567. $executeQuery=Yii::app()->db->createCommand($updateleads_cron_history)->execute();

    568. 

  568. //$r=0;

    569. 

  569.  foreach ($data as $key => $value) {

    570. 

  570. 	 

    571. 

  571. 	$owner = @$value['firstName']?(@$value['firstName']." ".@$value['lastName']):"NA";

    572. 

  572. 	$sqlDesc = "select group_concat(description) as ActivityDescription from renewalleadFollowup where leadId=".@$value["leadId"]." "; 

    573. 

  573. 	$dataDesc=Yii::app()->db->createCommand($sqlDesc)->queryAll();

    574. 

  574. 	

    575. 

  575. 	$leadActivity = @$dataDesc[0]['ActivityDescription']?str_replace(",",":",@$dataDesc[0]['ActivityDescription']):'';

    576. 

  576. 

    577. 

  577. 

    578. 

  578. 

    579. 

  579. 

    580. 

  580.  

    581. 

  581.  $state 		 	=  @$value["state"]?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$value["state"])):'NA';

    582. 

  582.  $city 				=  @$value["city"]?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$value["city"])):'NA';

    583. 

  583.  $customerType 		=  @$value["customerType"]?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$value["customerType"])):'NA';

    584. 

  584.  $productName 		=  @$value["productName"]?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$value["productName"])):'NA';

    585. 

  585.  $policyType 		=  @$value["policyType"]?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$value["policyType"])):'NA';

    586. 

  586.  $planType 			=  @$value["planType"]?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$value["planType"])):'NA';

    587. 

  587.  $campaignName 		=  @$value["campaignName"]?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$value["campaignName"])):'NA';

    588. 

  588.  $leadSourceTitle 	=  @$value["leadSourceTitle"]?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$value["leadSourceTitle"])):'NA';

    589. 

  589.  $leadStatus 		=  @$value["leadStatus"]?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$value["leadStatus"])):'NA';

    590. 

  590.  $leadStage 		=  @$value["leadStage"]?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$value["leadStage"])):'NA';

    591. 

  591.  $owner 			=  @$owner?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$owner)):'NA';

    592. 

  592.  $agntName 			=  @$value["agntName"]?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$value["agntName"])):'NA';

    593. 

  593.  $leadActivity 		=  @$leadActivity?stripslashes(preg_replace("/^'|[^A-Za-z0-9\'-]|'$/", " ", @$leadActivity)):'NA';

    594. 

  594. 

    595. 

  595. 

    596. 

  596. 

    597. 

  597. 	$newarray = array(

    598. 

  598. 				'state' => $state,

    599. 

  599. 				'city' => $city,

    600. 

  600. 				'customerType' => addslashes($customerType),

    601. 

  601. 				'product' => addslashes($productName),

    602. 

  602. 				'policyType' => addslashes($policyType),

    603. 

  603. 				'planType' => addslashes($planType),

    604. 

  604. 				'campaign' => addslashes($campaignName),

    605. 

  605. 				'leadSource' => addslashes($leadSourceTitle),

    606. 

  606. 				'leadStatus' => addslashes($leadStatus),

    607. 

  607. 				'leadStage' => addslashes($leadStage),

    608. 

  608. 				'owner' => addslashes($owner),

    609. 

  609. 				'agntName' => addslashes($agntName),

    610. 

  610. 				'ActivityDescription' => str_replace("'","`",$leadActivity)

    611. 

  611. 		);

    612. 

  612. 		$jsonData = json_encode($newarray);

    613. 

  613. 		

    614. 

  614. 		$insertQuery = " insert into leads_cron (leadId,name,email,emailOptOut,mobile,mobileOptOut,address,stateId,cityId,customerTypeId,productId,policyTypeId,planTypeId,campaignId,members,si,premium,productDetails,ageGroup,children,leadSourceId,leadStatusId,leadStageId,isJunk,currentAllocatedId,currentAllocatedDate,isDuplicate,duplicateLeadId,age,dateonSubmit,fileName,leadDescription,isRead,status,ipaddress,agentId,createdOn,createdBy,updatedOn,updatedBy,ws_sourcecode,ws_subject,ws_telephone,ws_plan,ws_product,ws_quoteid,ws_proposalid,ws_leadstage,ws_agentid,ws_noofyears,ws_orgnaizationName,utmsource,isBulkImport,salesClosedUpdatedOn,jsonAllData,jsonDataUpdatedOn,jsonDataUpdatedBy) values ('".$value['leadId']."','".addslashes($value['name'])."','".$value['email']."','".$value['emailOptOut']."','".$value['mobile']."','".$value['mobileOptOut']."','".addslashes($value['address'])."','".$value['stateId']."','".$value['cityId']."','".$value['customerTypeId']."','".$value['productId']."','".$value['policyTypeId']."','".$value['planTypeId']."','".$value['campaignId']."','".addslashes($value['members'])."','".addslashes($value['si'])."','".addslashes($value['premium'])."','".addslashes($value['productDetails'])."','".$value['ageGroup']."','".$value['children']."','".$value['leadSourceId']."','".$value['leadStatusId']."','".$value['leadStageId']."','".$value['isJunk']."','".$value['currentAllocatedId']."','".$value['currentAllocatedDate']."','".$value['isDuplicate']."','".$value['duplicateLeadId']."','".$value['age']."','".$value['dateonSubmit']."','".$value['fileName']."','".addslashes($value['leadDescription'])."','".$value['isRead']."','".$value['status']."','".$value['ipaddress']."','".$value['agentId']."','".$value['createdOn']."','".$value['createdBy']."','".$value['updatedOn']."','".$value['updatedBy']."','".$value['ws_sourcecode']."','".$value['ws_subject']."','".$value['ws_telephone']."','".$value['ws_plan']."','".$value['ws_product']."','".$value['ws_quoteid']."','".$value['ws_proposalid']."','".$value['ws_leadstage']."','".$value['ws_agentid']."','".$value['ws_noofyears']."','".$value['ws_orgnaizationName']."','".$value['utmsource']."','".$value['isBulkImport']."','".$value['salesClosedUpdatedOn']."','".$jsonData."','".time()."','amit') ";

    615. 

  615. 		$executeQuerys=Yii::app()->db->createCommand($insertQuery)->execute();

    616. 

  616. 		//echo $value['leadId']."<br>";

    617. 

  617. 		// $r++;

    618. 

  618. 	

    619. 

  619. 	

    620. 

  620.  }

    621. 

  621. }

    622. 

  622. public function actionagentwisetotalcalls(){

    623. 

  623. ini_set('max_input_time', 1200);

    624. 

  624. ini_set('max_execution_time', 1200);

    625. 

  625. 	

    626. 

  626. 	$time = time();

    627. 

  627. 	$parser = new CHtmlPurifier();

    628. 

  628. 	$afterMonth = $parser->purify(intval(@$_REQUEST['month']));

    629. 

  629. 	$afterDate = $parser->purify(intval(@$_REQUEST['date']));

    630. 

  630. 	$afterYear = $parser->purify(intval(@$_REQUEST['year']));

    631. 

  631. 	if(isset($afterMonth) && !empty($afterMonth) && isset($afterDate) && !empty($afterDate) && isset($afterYear) && !empty($afterYear)){

    632. 

  632. 	$start = mktime(0, 0, 0,$afterMonth,$afterDate,$afterYear);

    633. 

  633. 	$end = mktime(23, 59, 59,$afterMonth,$afterDate,$afterYear);

    634. 

  634. 	} else {

    635. 

  635.     $start = mktime(0, 0, 0);

    636. 

  636. 	$end = mktime(23, 59, 59);

    637. 

  637. 	}

    638. 

  638. 	$date = date('d_M_Y',$start)."_"; 

    639. 

  639. 	$host = '10.216.6.59'; // MYSQL database host adress

    640. 

  640. 	$db = 'lms'; // MYSQL database name

    641. 

  641. 	$user = 'root'; // Mysql Datbase user

    642. 

  642. 	$pass = '0kwp6aNR'; // Mysql Datbase password	

    643. 

  643. 	$link=mysql_connect($host,$user,$pass);

    644. 

  644. 	mysql_select_db($db,$link);

    645. 

  645. 	$table='report_agentwisetotalcalls';

    646. 

  646. 	$filename = "filesystem/".$date.$table.'.csv';

    647. 

  647.     $csv_terminated = "\n";

    648. 

  648.     $csv_separator = ",";

    649. 

  649.     $csv_enclosed = '"';

    650. 

  650.     $csv_escaped = "\\";

    651. 

  651. 	mysql_query("insert into report_info (id,type,filename,createdOn,createdBy,updatedOn,updatedBy) values('','agentwisetotalcalls','".@$filename."','".@$time."','0','".@$time."','0')");

    652. 

  652. 	$totalleadstatusids = array(7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,33,26,37,38,39,42,43);

    653. 

  653. 	$chktableExist = mysql_query('DROP TABLE IF EXISTS '.$table.' ') or die(mysql_error());

    654. 

  654. 	$tableres ='create table IF NOT EXISTS '.$table.' as SELECT

    655. 

  655.     user.userName as `Caller Name`,

    656. 

  656.     SUM(CASE WHEN lead.currentAllocatedId=user.id THEN 1 ELSE 0 END) AS `Total Diposed`,

    657. 

  657.     SUM(CASE WHEN LS.`leadStatusId` = 7 THEN 1 ELSE 0 END) AS `Sale Done`,

    658. 

  658.     SUM(CASE WHEN LS.`leadStatusId` = 8 THEN 1 ELSE 0 END) AS `Follow Up`,

    659. 

  659.     SUM(CASE WHEN LS.`leadStatusId` = 9 THEN 1 ELSE 0 END) AS `Do Not Call - Will Buy self on Website`,

    660. 

  660.     SUM(CASE WHEN LS.`leadStatusId` = 10 THEN 1 ELSE 0 END) AS `Duplicate - Already In Talk With Team`,

    661. 

  661.     SUM(CASE WHEN LS.`leadStatusId` = 37 THEN 1 ELSE 0 END) AS `In Contact with Oflline Agent`,

    662. 

  662.     SUM(CASE WHEN LS.`leadStatusId` = 38 THEN 1 ELSE 0 END) AS `In Touch With Other Partner`,

    663. 

  663.     SUM(CASE WHEN LS.`leadStatusId` = 11 THEN 1 ELSE 0 END) AS `Short Hangup`,

    664. 

  664.     SUM(CASE WHEN LS.`leadStatusId` = 12 THEN 1 ELSE 0 END) AS `Call Later`,

    665. 

  665.     SUM(CASE WHEN LS.`leadStatusId` = 42 THEN 1 ELSE 0 END) AS `Ringing Less Than Five Tries`,

    666. 

  666.     SUM(CASE WHEN LS.`leadStatusId` = 43 THEN 1 ELSE 0 END) AS `Switched Off Less than Five Tries`,

    667. 

  667.     SUM(CASE WHEN LS.`leadStatusId` = 13 THEN 1 ELSE 0 END) AS `Not Interested - Fund Problem`,

    668. 

  668.     SUM(CASE WHEN LS.`leadStatusId` = 14 THEN 1 ELSE 0 END) AS `Just Checking Website`,

    669. 

  669.     SUM(CASE WHEN LS.`leadStatusId` = 15 THEN 1 ELSE 0 END) AS `Not Interested - Insured From Employer`,

    670. 

  670.     SUM(CASE WHEN LS.`leadStatusId` = 16 THEN 1 ELSE 0 END) AS `Not Interested - High Premium`,

    671. 

  671.     SUM(CASE WHEN LS.`leadStatusId` = 17 THEN 1 ELSE 0 END) AS `Under Age`,

    672. 

  672.     SUM(CASE WHEN LS.`leadStatusId` = 18 THEN 1 ELSE 0 END) AS `Not Visited Website`,

    673. 

  673.     SUM(CASE WHEN LS.`leadStatusId` = 19 THEN 1 ELSE 0 END) AS `Do Not Call`,

    674. 

  674.     SUM(CASE WHEN LS.`leadStatusId` = 20 THEN 1 ELSE 0 END) AS `Not Interested - General`,

    675. 

  675.     SUM(CASE WHEN LS.`leadStatusId` = 21 THEN 1 ELSE 0 END) AS `Over Age`,

    676. 

  676.     SUM(CASE WHEN LS.`leadStatusId` = 22 THEN 1 ELSE 0 END) AS `Not Interested - Already Insured`,

    677. 

  677.     SUM(CASE WHEN LS.`leadStatusId` = 23 THEN 1 ELSE 0 END) AS `Duplicate Data - Not Interested`,

    678. 

  678.     SUM(CASE WHEN LS.`leadStatusId` = 24 THEN 1 ELSE 0 END) AS `Not Eligible - PED`,

    679. 

  679.     SUM(CASE WHEN LS.`leadStatusId` = 25 THEN 1 ELSE 0 END) AS `Not Interested - Govt. Employee`,

    680. 

  680.     SUM(CASE WHEN LS.`leadStatusId` = 26 THEN 1 ELSE 0 END) AS `Not Interested - Senior Citizen`,

    681. 

  681.     SUM(CASE WHEN LS.`leadStatusId` = 27 THEN 1 ELSE 0 END) AS `Wrong Number`,

    682. 

  682.     SUM(CASE WHEN LS.`leadStatusId` = 28 THEN 1 ELSE 0 END) AS `Customer Not Available`,

    683. 

  683.     SUM(CASE WHEN LS.`leadStatusId` = 29 THEN 1 ELSE 0 END) AS `Language Barrier`,

    684. 

  684.     SUM(CASE WHEN LS.`leadStatusId` = 30 THEN 1 ELSE 0 END) AS `No Response`,

    685. 

  685.     SUM(CASE WHEN LS.`leadStatusId` = 33 THEN 1 ELSE 0 END) AS `Invalid Number`,

    686. 

  686.     SUM(CASE WHEN LS.`leadStatusId` = 36 THEN 1 ELSE 0 END) AS `Junk`,

    687. 

  687.     SUM(CASE WHEN LS.`leadStatusId` = 39 THEN 1 ELSE 0 END) AS `Ringing no response after five tries`,

    688. 

  688.     SUM(CASE WHEN LS.`leadStatusId` = 43 THEN 1 ELSE 0 END) AS `Switched Off/ Not Reachable after 5 tries`

    689. 

  689. FROM `lead` join user on user.id=lead.currentAllocatedId left join leadStatus LS on LS.leadStatusId=lead.leadStatusId where lead.updatedOn >= "'.@$start.'"  and lead.updatedOn <= "'.@$end.'" group by lead.currentAllocatedId';

    690. 

  690. 	$esx = mysql_query($tableres) or die(mysql_error());

    691. 

  691. 	$queryforinsert = "Insert into ".$table." (`Total Diposed`,`Sale Done`,`Follow Up`,`Do Not Call - Will Buy self on Website`,`Duplicate - Already In Talk With Team`,`In Contact with Oflline Agent`,`In Touch With Other Partner`,`Short Hangup`,`Call Later`,`Ringing Less Than Five Tries`,`Switched Off Less than Five Tries`,`Not Interested - Fund Problem`,`Just Checking Website`,`Not Interested - Insured From Employer`,`Not Interested - High Premium`,`Under Age`,`Not Visited Website`,`Do Not Call`,`Not Interested - General`,`Over Age`,`Not Interested - Already Insured`,`Duplicate Data - Not Interested`,`Not Eligible - PED`,`Not Interested - Govt. Employee`,`Not Interested - Senior Citizen`,`Wrong Number`,`Customer Not Available`,`Language Barrier`,`No Response`,`Invalid Number`,`Junk`,`Ringing no response after five tries`,`Switched Off/ Not Reachable after 5 tries`) 

    692. 

  692. 	

    693. 

  693. 	select SUM(`Total Diposed`), SUM(`Sale Done`), SUM(`Follow Up`), SUM(`Do Not Call - Will Buy self on Website`), SUM(`Duplicate - Already In Talk With Team`), SUM(`In Contact with Oflline Agent`), SUM(`In Touch With Other Partner`), SUM(`Short Hangup`), SUM(`Call Later`), SUM(`Ringing Less Than Five Tries`), SUM(`Switched Off Less than Five Tries`), SUM(`Not Interested - Fund Problem`), SUM(`Just Checking Website`), SUM(`Not Interested - Insured From Employer`), SUM(`Not Interested - High Premium`), SUM(`Under Age`), SUM(`Not Visited Website`), SUM(`Do Not Call`), SUM(`Not Interested - General`), SUM(`Over Age`), SUM(`Not Interested - Already Insured`), SUM(`Duplicate Data - Not Interested`), SUM(`Not Eligible - PED`), SUM(`Not Interested - Govt. Employee`), SUM(`Not Interested - Senior Citizen`), SUM(`Wrong Number`), SUM(`Customer Not Available`), SUM(`Language Barrier`), SUM(`No Response`), SUM(`Invalid Number`), SUM(`Junk`), SUM(`Ringing no response after five tries`), SUM(`Switched Off/ Not Reachable after 5 tries`) from ".$table."";

    694. 

  694. 

    695. 

  695. mysql_query($queryforinsert);	

    696. 

  696.     $sql_query = "select * from $table";

    697. 

  697.  

    698. 

  698.     // Gets the data from the database

    699. 

  699.     $result = mysql_query($sql_query) or die(mysql_error());

    700. 

  700.     $fields_cnt = mysql_num_fields($result);

    701. 

  701.  

    702. 

  702.  

    703. 

  703.     $schema_insert = '';

    704. 

  704.  

    705. 

  705.     for ($i = 0; $i < $fields_cnt; $i++)

    706. 

  706.     {

    707. 

  707.         $l = $csv_enclosed . str_replace($csv_enclosed, $csv_escaped . $csv_enclosed,

    708. 

  708.             stripslashes(mysql_field_name($result, $i))) . $csv_enclosed;

    709. 

  709.         $schema_insert .= $l;

    710. 

  710.         $schema_insert .= $csv_separator;

    711. 

  711.     } // end for

    712. 

  712.  

    713. 

  713.     $out = trim(substr($schema_insert, 0, -1));

    714. 

  714.     $out .= $csv_terminated;

    715. 

  715.  

    716. 

  716.     // Format the data

    717. 

  717.     while ($row = mysql_fetch_array($result))

    718. 

  718.     {

    719. 

  719.         $schema_insert = '';

    720. 

  720.         for ($j = 0; $j < $fields_cnt; $j++)

    721. 

  721.         {

    722. 

  722.             if ($row[$j] == '0' || $row[$j] != '')

    723. 

  723.             {

    724. 

  724.  

    725. 

  725.                 if ($csv_enclosed == '')

    726. 

  726.                 {

    727. 

  727.                     $schema_insert .= $row[$j];

    728. 

  728.                 } else

    729. 

  729.                 {

    730. 

  730.                     $schema_insert .= $csv_enclosed . 

    731. 

  731. 					str_replace($csv_enclosed, $csv_escaped . $csv_enclosed, $row[$j]) . $csv_enclosed;

    732. 

  732.                 }

    733. 

  733.             } else

    734. 

  734.             {

    735. 

  735.                 $schema_insert .= '';

    736. 

  736.             }

    737. 

  737.  

    738. 

  738.             if ($j < $fields_cnt - 1)

    739. 

  739.             {

    740. 

  740.                 $schema_insert .= $csv_separator;

    741. 

  741.             }

    742. 

  742.         } // end for

    743. 

  743.  

    744. 

  744.         $out .= $schema_insert;

    745. 

  745.         $out .= $csv_terminated;

    746. 

  746.     } // end while

    747. 

  747.  

    748. 

  748.    /// header("Cache-Control: must-revalidate, post-check=0, pre-check=0");

    749. 

  749.     //header("Content-Length: " . strlen($out));

    750. 

  750.     // Output to browser with appropriate mime type, you choose ;)

    751. 

  751.    // header("Content-Disposition: attachment; filename=$filename");

    752. 

  752.     //header("Content-type: text/x-csv");

    753. 

  753. 	$fd = fopen ($filename, "w");

    754. 

  754. 	fputs($fd, $out);

    755. 

  755. 	fclose($fd);

    756. 

  756. 	echo "success";

    757. 

  757.     //header("Content-type: text/csv");

    758. 

  758.     //header("Content-type: application/csv");

    759. 

  759.     //echo $out;

    760. 

  760.     exit;

    761. 

  761.  

    762. 

  762. 	}

    763. 

  763. public function actionagentfreshleadsdisposition(){

    764. 

  764. 	

    765. 

  765. /*	$date = date('d_M_Y')."_";

    766. 

  766.     $start = mktime(0, 0, 0);

    767. 

  767. 	$end = mktime(23, 59, 59);

    768. 

  768. */	

    769. 

  769. ini_set('max_input_time', 2400);

    770. 

  770. ini_set('max_execution_time', 2400);

    771. 

  771. 

    772. 

  772. 	$parser = new CHtmlPurifier();

    773. 

  773. 	$afterMonth = $parser->purify(intval(@$_REQUEST['month']));

    774. 

  774. 	$afterDate = $parser->purify(intval(@$_REQUEST['date']));

    775. 

  775. 	$afterYear = $parser->purify(intval(@$_REQUEST['year']));

    776. 

  776. 	if(isset($afterMonth) && !empty($afterMonth) && isset($afterDate) && !empty($afterDate) && isset($afterYear) && !empty($afterYear)){

    777. 

  777. 	$start = mktime(0, 0, 0,$afterMonth,$afterDate,$afterYear);

    778. 

  778. 	$end = mktime(23, 59, 59,$afterMonth,$afterDate,$afterYear);

    779. 

  779. 	} else {

    780. 

  780.     $start = mktime(0, 0, 0);

    781. 

  781. 	$end = mktime(23, 59, 59);

    782. 

  782. 	}

    783. 

  783. 	

    784. 

  784. 	$time = time();

    785. 

  785. 	$date = date('d_M_Y',$start).'_';

    786. 

  786. 

    787. 

  787. 

    788. 

  788. 

    789. 

  789. $host = '10.216.6.59'; // MYSQL database host adress

    790. 

  790. 	$db = 'lms'; // MYSQL database name

    791. 

  791. 	$user = 'root'; // Mysql Datbase user

    792. 

  792. 	$pass = '0kwp6aNR'; // Mysql Datbase password	

    793. 

  793. 	$link=mysql_connect($host,$user,$pass);

    794. 

  794. 	mysql_select_db($db,$link);

    795. 

  795. 	$table='report_agentfreshleadsdisposition';

    796. 

  796. 	$filename = "filesystem/".$date.$table.'.csv';

    797. 

  797.     $csv_terminated = "\n";

    798. 

  798.     $csv_separator = ",";

    799. 

  799.     $csv_enclosed = '"';

    800. 

  800.     $csv_escaped = "\\";

    801. 

  801. 	$time = time();

    802. 

  802. 	mysql_query("insert into report_info (id,type,filename,createdOn,createdBy,updatedOn,updatedBy) values('','agentfreshleadsdisposition','".@$filename."','".@$time."','".@$_SESSION['id']."','".@$time."','".@$_SESSION['id']."')");

    803. 

  803. 	$totalleadstatusids = '7,8,9,10,37,38,11,12,42,43,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,33,39,43';

    804. 

  804. 	$chktableExist = mysql_query('DROP TABLE IF EXISTS '.$table.' ') or die(mysql_error());

    805. 

  805. 	

    806. 

  806. 	

    807. 

  807. 	$tableres ='create table IF NOT EXISTS '.$table.' as SELECT

    808. 

  808.     user.userName as `Caller Name`,

    809. 

  809.     SUM(CASE WHEN lead.currentAllocatedId=user.id THEN 1 ELSE 0 END) AS `Total Assigned`,

    810. 

  810.     SUM(CASE WHEN lead.currentAllocatedId=user.id  AND lead.leadStatusId NOT IN ('.@$totalleadstatusids.') THEN 1 ELSE 0 END) AS `No Update`,

    811. 

  811.     SUM(CASE WHEN lead.currentAllocatedId=user.id  AND lead.leadStatusId IN ('.@$totalleadstatusids.') THEN 1 ELSE 0 END) AS `Total Diposed`,

    812. 

  812.     SUM(CASE WHEN LS.`leadStatusId` = 7 THEN 1 ELSE 0 END) AS `Sale Done`,

    813. 

  813.     SUM(CASE WHEN LS.`leadStatusId` = 8 THEN 1 ELSE 0 END) AS `Follow Up`,

    814. 

  814.     SUM(CASE WHEN LS.`leadStatusId` = 9 THEN 1 ELSE 0 END) AS `Do Not Call - Will Buy self on Website`,

    815. 

  815.     SUM(CASE WHEN LS.`leadStatusId` = 10 THEN 1 ELSE 0 END) AS `Duplicate - Already In Talk With Team`,

    816. 

  816.     SUM(CASE WHEN LS.`leadStatusId` = 37 THEN 1 ELSE 0 END) AS `In Contact with Oflline Agent`,

    817. 

  817.     SUM(CASE WHEN LS.`leadStatusId` = 38 THEN 1 ELSE 0 END) AS `In Touch With Other Partner`,

    818. 

  818.     SUM(CASE WHEN LS.`leadStatusId` = 11 THEN 1 ELSE 0 END) AS `Short Hangup`,

    819. 

  819.     SUM(CASE WHEN LS.`leadStatusId` = 12 THEN 1 ELSE 0 END) AS `Call Later`,

    820. 

  820.     SUM(CASE WHEN LS.`leadStatusId` = 42 THEN 1 ELSE 0 END) AS `Ringing Less Than Five Tries`,

    821. 

  821.     SUM(CASE WHEN LS.`leadStatusId` = 43 THEN 1 ELSE 0 END) AS `Switched Off Less than Five Tries`,

    822. 

  822.     SUM(CASE WHEN LS.`leadStatusId` = 13 THEN 1 ELSE 0 END) AS `Not Interested - Fund Problem`,

    823. 

  823.     SUM(CASE WHEN LS.`leadStatusId` = 14 THEN 1 ELSE 0 END) AS `Just Checking Website`,

    824. 

  824.     SUM(CASE WHEN LS.`leadStatusId` = 15 THEN 1 ELSE 0 END) AS `Not Interested - Insured From Employer`,

    825. 

  825.     SUM(CASE WHEN LS.`leadStatusId` = 16 THEN 1 ELSE 0 END) AS `Not Interested - High Premium`,

    826. 

  826.     SUM(CASE WHEN LS.`leadStatusId` = 17 THEN 1 ELSE 0 END) AS `Under Age`,

    827. 

  827.     SUM(CASE WHEN LS.`leadStatusId` = 18 THEN 1 ELSE 0 END) AS `Not Visited Website`,

    828. 

  828.     SUM(CASE WHEN LS.`leadStatusId` = 19 THEN 1 ELSE 0 END) AS `Do Not Call`,

    829. 

  829.     SUM(CASE WHEN LS.`leadStatusId` = 20 THEN 1 ELSE 0 END) AS `Not Interested - General`,

    830. 

  830.     SUM(CASE WHEN LS.`leadStatusId` = 21 THEN 1 ELSE 0 END) AS `Over Age`,

    831. 

  831.     SUM(CASE WHEN LS.`leadStatusId` = 22 THEN 1 ELSE 0 END) AS `Not Interested - Already Insured`,

    832. 

  832.     SUM(CASE WHEN LS.`leadStatusId` = 23 THEN 1 ELSE 0 END) AS `Duplicate Data - Not Interested`,

    833. 

  833.     SUM(CASE WHEN LS.`leadStatusId` = 24 THEN 1 ELSE 0 END) AS `Not Eligible - PED`,

    834. 

  834.     SUM(CASE WHEN LS.`leadStatusId` = 25 THEN 1 ELSE 0 END) AS `Not Interested - Govt. Employee`,

    835. 

  835.     SUM(CASE WHEN LS.`leadStatusId` = 26 THEN 1 ELSE 0 END) AS `Not Interested - Senior Citizen`,

    836. 

  836.     SUM(CASE WHEN LS.`leadStatusId` = 27 THEN 1 ELSE 0 END) AS `Wrong Number`,

    837. 

  837.     SUM(CASE WHEN LS.`leadStatusId` = 28 THEN 1 ELSE 0 END) AS `Customer Not Available`,

    838. 

  838.     SUM(CASE WHEN LS.`leadStatusId` = 29 THEN 1 ELSE 0 END) AS `Language Barrier`,

    839. 

  839.     SUM(CASE WHEN LS.`leadStatusId` = 30 THEN 1 ELSE 0 END) AS `No Response`,

    840. 

  840.     SUM(CASE WHEN LS.`leadStatusId` = 33 THEN 1 ELSE 0 END) AS `Invalid Number`,

    841. 

  841.     SUM(CASE WHEN LS.`leadStatusId` = 36 THEN 1 ELSE 0 END) AS `Junk`,

    842. 

  842.     SUM(CASE WHEN LS.`leadStatusId` = 39 THEN 1 ELSE 0 END) AS `Ringing no response after five tries`,

    843. 

  843.     SUM(CASE WHEN LS.`leadStatusId` = 43 THEN 1 ELSE 0 END) AS `Switched Off/ Not Reachable after 5 tries`

    844. 

  844. FROM `lead` join user on user.id=lead.currentAllocatedId left join leadStatus LS on LS.leadStatusId=lead.leadStatusId where lead.leadStatusId !=36 AND lead.currentAllocatedDate >= "'.@$start.'"  and lead.currentAllocatedDate <= "'.@$end.'" AND lead.leadId IN(select leadId from lead where lead.updatedOn >= "'.@$start.'"  and lead.updatedOn <= "'.@$end.'"  and lead.currentAllocatedDate >= "'.@$start.'"  and lead.currentAllocatedDate <= "'.@$end.'" ) group by lead.currentAllocatedId';

    845. 

  845. 	$esx = mysql_query($tableres) or die(mysql_error());

    846. 

  846. 	$queryforinsert = "Insert into ".$table." (`Caller Name`,`Total Diposed`,`Sale Done`,`Follow Up`,`Do Not Call - Will Buy self on Website`,`Duplicate - Already In Talk With Team`,`In Contact with Oflline Agent`,`In Touch With Other Partner`,`Short Hangup`,`Call Later`,`Ringing Less Than Five Tries`,`Switched Off Less than Five Tries`,`Not Interested - Fund Problem`,`Just Checking Website`,`Not Interested - Insured From Employer`,`Not Interested - High Premium`,`Under Age`,`Not Visited Website`,`Do Not Call`,`Not Interested - General`,`Over Age`,`Not Interested - Already Insured`,`Duplicate Data - Not Interested`,`Not Eligible - PED`,`Not Interested - Govt. Employee`,`Not Interested - Senior Citizen`,`Wrong Number`,`Customer Not Available`,`Language Barrier`,`No Response`,`Invalid Number`,`Junk`,`Ringing no response after five tries`,`Switched Off/ Not Reachable after 5 tries`) 

    847. 

  847. 	

    848. 

  848. 	select 'Total',SUM(`Total Diposed`), SUM(`Sale Done`), SUM(`Follow Up`), SUM(`Do Not Call - Will Buy self on Website`), SUM(`Duplicate - Already In Talk With Team`), SUM(`In Contact with Oflline Agent`), SUM(`In Touch With Other Partner`), SUM(`Short Hangup`), SUM(`Call Later`), SUM(`Ringing Less Than Five Tries`), SUM(`Switched Off Less than Five Tries`), SUM(`Not Interested - Fund Problem`), SUM(`Just Checking Website`), SUM(`Not Interested - Insured From Employer`), SUM(`Not Interested - High Premium`), SUM(`Under Age`), SUM(`Not Visited Website`), SUM(`Do Not Call`), SUM(`Not Interested - General`), SUM(`Over Age`), SUM(`Not Interested - Already Insured`), SUM(`Duplicate Data - Not Interested`), SUM(`Not Eligible - PED`), SUM(`Not Interested - Govt. Employee`), SUM(`Not Interested - Senior Citizen`), SUM(`Wrong Number`), SUM(`Customer Not Available`), SUM(`Language Barrier`), SUM(`No Response`), SUM(`Invalid Number`), SUM(`Junk`), SUM(`Ringing no response after five tries`), SUM(`Switched Off/ Not Reachable after 5 tries`) from ".$table."";

    849. 

  849. mysql_query($queryforinsert);	

    850. 

  850.     $sql_query = "select * from $table";

    851. 

  851.  

    852. 

  852.     // Gets the data from the database

    853. 

  853.     $result = mysql_query($sql_query);

    854. 

  854.     $fields_cnt = mysql_num_fields($result);

    855. 

  855.  

    856. 

  856.  

    857. 

  857.     $schema_insert = '';

    858. 

  858.  

    859. 

  859.     for ($i = 0; $i < $fields_cnt; $i++)

    860. 

  860.     {

    861. 

  861.         $l = $csv_enclosed . str_replace($csv_enclosed, $csv_escaped . $csv_enclosed,

    862. 

  862.             stripslashes(mysql_field_name($result, $i))) . $csv_enclosed;

    863. 

  863.         $schema_insert .= $l;

    864. 

  864.         $schema_insert .= $csv_separator;

    865. 

  865.     } // end for

    866. 

  866.  

    867. 

  867.     $out = trim(substr($schema_insert, 0, -1));

    868. 

  868.     $out .= $csv_terminated;

    869. 

  869.  

    870. 

  870.     // Format the data

    871. 

  871.     while ($row = mysql_fetch_array($result))

    872. 

  872.     {

    873. 

  873.         $schema_insert = '';

    874. 

  874.         for ($j = 0; $j < $fields_cnt; $j++)

    875. 

  875.         {

    876. 

  876.             if ($row[$j] == '0' || $row[$j] != '')

    877. 

  877.             {

    878. 

  878.  

    879. 

  879.                 if ($csv_enclosed == '')

    880. 

  880.                 {

    881. 

  881.                     $schema_insert .= $row[$j];

    882. 

  882.                 } else

    883. 

  883.                 {

    884. 

  884.                     $schema_insert .= $csv_enclosed . 

    885. 

  885. 					str_replace($csv_enclosed, $csv_escaped . $csv_enclosed, $row[$j]) . $csv_enclosed;

    886. 

  886.                 }

    887. 

  887.             } else

    888. 

  888.             {

    889. 

  889.                 $schema_insert .= '';

    890. 

  890.             }

    891. 

  891.  

    892. 

  892.             if ($j < $fields_cnt - 1)

    893. 

  893.             {

    894. 

  894.                 $schema_insert .= $csv_separator;

    895. 

  895.             }

    896. 

  896.         } // end for

    897. 

  897.  

    898. 

  898.         $out .= $schema_insert;

    899. 

  899.         $out .= $csv_terminated;

    900. 

  900.     } // end while

    901. 

  901.  

    902. 

  902. 	$fd = fopen ($filename, "w");

    903. 

  903. 	fputs($fd, $out);

    904. 

  904. 	fclose($fd);

    905. 

  905. 	echo "success";

    906. 

  906.     //header("Content-type: text/csv");

    907. 

  907.     //header("Content-type: application/csv");

    908. 

  908.     //echo $out;

    909. 

  909.     exit;

    910. 

  910.  

    911. 

  911. 	}

    912. 

  912. public function actionagentfreshleadsdisposition_30thOct2014(){

    913. 

  913. 	

    914. 

  914. ini_set('max_input_time', 1200);

    915. 

  915. ini_set('max_execution_time', 1200);

    916. 

  916. 	$date = date('d_M_Y')."_";

    917. 

  917. 	$host = '10.216.6.59'; // MYSQL database host adress

    918. 

  918. 	$db = 'lms'; // MYSQL database name

    919. 

  919. 	$user = 'root'; // Mysql Datbase user

    920. 

  920. 	$pass = '0kwp6aNR'; // Mysql Datbase password	

    921. 

  921. 	$link=mysql_connect($host,$user,$pass);

    922. 

  922. 	mysql_select_db($db,$link);

    923. 

  923. 	$table='report_agentfreshleadsdisposition';

    924. 

  924. 	$filename = "filesystem/".$date.$table.'.csv';

    925. 

  925.     $csv_terminated = "\n";

    926. 

  926.     $csv_separator = ",";

    927. 

  927.     $csv_enclosed = '"';

    928. 

  928.     $csv_escaped = "\\";

    929. 

  929. 	$time = time();

    930. 

  930. 	mysql_query("insert into report_info (id,type,filename,createdOn,createdBy,updatedOn,updatedBy) values('','agentfreshleadsdisposition','".@$filename."','".@$time."','0','".@$time."','0')");

    931. 

  931. 	$chktableExist = mysql_query('DROP TABLE IF EXISTS '.$table.' ') or die(mysql_error());

    932. 

  932. 	$tableres ='create table IF NOT EXISTS '.$table.' as SELECT

    933. 

  933.     user.userName as `Caller Name`,

    934. 

  934.     SUM(CASE WHEN lead.currentAllocatedId=user.id THEN 1 ELSE 0 END) AS `Total Diposed`,

    935. 

  935.     SUM(CASE WHEN LS.`leadStatusId` = 7 THEN 1 ELSE 0 END) AS `Sale Done`,

    936. 

  936.     SUM(CASE WHEN LS.`leadStatusId` = 8 THEN 1 ELSE 0 END) AS `Follow Up`,

    937. 

  937.     SUM(CASE WHEN LS.`leadStatusId` = 9 THEN 1 ELSE 0 END) AS `Do Not Call - Will Buy self on Website`,

    938. 

  938.     SUM(CASE WHEN LS.`leadStatusId` = 10 THEN 1 ELSE 0 END) AS `Duplicate - Already In Talk With Team`,

    939. 

  939.     SUM(CASE WHEN LS.`leadStatusId` = 37 THEN 1 ELSE 0 END) AS `In Contact with Oflline Agent`,

    940. 

  940.     SUM(CASE WHEN LS.`leadStatusId` = 38 THEN 1 ELSE 0 END) AS `In Touch With Other Partner`,

    941. 

  941.     SUM(CASE WHEN LS.`leadStatusId` = 11 THEN 1 ELSE 0 END) AS `Short Hangup`,

    942. 

  942.     SUM(CASE WHEN LS.`leadStatusId` = 12 THEN 1 ELSE 0 END) AS `Call Later`,

    943. 

  943.     SUM(CASE WHEN LS.`leadStatusId` = 42 THEN 1 ELSE 0 END) AS `Ringing Less Than Five Tries`,

    944. 

  944.     SUM(CASE WHEN LS.`leadStatusId` = 43 THEN 1 ELSE 0 END) AS `Switched Off Less than Five Tries`,

    945. 

  945.     SUM(CASE WHEN LS.`leadStatusId` = 13 THEN 1 ELSE 0 END) AS `Not Interested - Fund Problem`,

    946. 

  946.     SUM(CASE WHEN LS.`leadStatusId` = 14 THEN 1 ELSE 0 END) AS `Just Checking Website`,

    947. 

  947.     SUM(CASE WHEN LS.`leadStatusId` = 15 THEN 1 ELSE 0 END) AS `Not Interested - Insured From Employer`,

    948. 

  948.     SUM(CASE WHEN LS.`leadStatusId` = 16 THEN 1 ELSE 0 END) AS `Not Interested - High Premium`,

    949. 

  949.     SUM(CASE WHEN LS.`leadStatusId` = 17 THEN 1 ELSE 0 END) AS `Under Age`,

    950. 

  950.     SUM(CASE WHEN LS.`leadStatusId` = 18 THEN 1 ELSE 0 END) AS `Not Visited Website`,

    951. 

  951.     SUM(CASE WHEN LS.`leadStatusId` = 19 THEN 1 ELSE 0 END) AS `Do Not Call`,

    952. 

  952.     SUM(CASE WHEN LS.`leadStatusId` = 20 THEN 1 ELSE 0 END) AS `Not Interested - General`,

    953. 

  953.     SUM(CASE WHEN LS.`leadStatusId` = 21 THEN 1 ELSE 0 END) AS `Over Age`,

    954. 

  954.     SUM(CASE WHEN LS.`leadStatusId` = 22 THEN 1 ELSE 0 END) AS `Not Interested - Already Insured`,

    955. 

  955.     SUM(CASE WHEN LS.`leadStatusId` = 23 THEN 1 ELSE 0 END) AS `Duplicate Data - Not Interested`,

    956. 

  956.     SUM(CASE WHEN LS.`leadStatusId` = 24 THEN 1 ELSE 0 END) AS `Not Eligible - PED`,

    957. 

  957.     SUM(CASE WHEN LS.`leadStatusId` = 25 THEN 1 ELSE 0 END) AS `Not Interested - Govt. Employee`,

    958. 

  958.     SUM(CASE WHEN LS.`leadStatusId` = 26 THEN 1 ELSE 0 END) AS `Not Interested - Senior Citizen`,

    959. 

  959.     SUM(CASE WHEN LS.`leadStatusId` = 27 THEN 1 ELSE 0 END) AS `Wrong Number`,

    960. 

  960.     SUM(CASE WHEN LS.`leadStatusId` = 28 THEN 1 ELSE 0 END) AS `Customer Not Available`,

    961. 

  961.     SUM(CASE WHEN LS.`leadStatusId` = 29 THEN 1 ELSE 0 END) AS `Language Barrier`,

    962. 

  962.     SUM(CASE WHEN LS.`leadStatusId` = 30 THEN 1 ELSE 0 END) AS `No Response`,

    963. 

  963.     SUM(CASE WHEN LS.`leadStatusId` = 33 THEN 1 ELSE 0 END) AS `Invalid Number`,

    964. 

  964.     SUM(CASE WHEN LS.`leadStatusId` = 36 THEN 1 ELSE 0 END) AS `Junk`,

    965. 

  965.     SUM(CASE WHEN LS.`leadStatusId` = 39 THEN 1 ELSE 0 END) AS `Ringing no response after five tries`,

    966. 

  966.     SUM(CASE WHEN LS.`leadStatusId` = 43 THEN 1 ELSE 0 END) AS `Switched Off/ Not Reachable after 5 tries`

    967. 

  967. FROM `lead` join user on user.id=lead.currentAllocatedId left join leadStatus LS on LS.leadStatusId=lead.leadStatusId where lead.leadStatusId = 34 AND lead.currentAllocatedId<1 group by lead.currentAllocatedId';

    968. 

  968. 	$esx = mysql_query($tableres) or die(mysql_error());

    969. 

  969. 	

    970. 

  970. 	$queryforinsert = "Insert into ".$table." (`Total Diposed`,`Sale Done`,`Follow Up`,`Do Not Call - Will Buy self on Website`,`Duplicate - Already In Talk With Team`,`In Contact with Oflline Agent`,`In Touch With Other Partner`,`Short Hangup`,`Call Later`,`Ringing Less Than Five Tries`,`Switched Off Less than Five Tries`,`Not Interested - Fund Problem`,`Just Checking Website`,`Not Interested - Insured From Employer`,`Not Interested - High Premium`,`Under Age`,`Not Visited Website`,`Do Not Call`,`Not Interested - General`,`Over Age`,`Not Interested - Already Insured`,`Duplicate Data - Not Interested`,`Not Eligible - PED`,`Not Interested - Govt. Employee`,`Not Interested - Senior Citizen`,`Wrong Number`,`Customer Not Available`,`Language Barrier`,`No Response`,`Invalid Number`,`Junk`,`Ringing no response after five tries`,`Switched Off/ Not Reachable after 5 tries`) 

    971. 

  971. 	

    972. 

  972. 	select SUM(`Total Diposed`), SUM(`Sale Done`), SUM(`Follow Up`), SUM(`Do Not Call - Will Buy self on Website`), SUM(`Duplicate - Already In Talk With Team`), SUM(`In Contact with Oflline Agent`), SUM(`In Touch With Other Partner`), SUM(`Short Hangup`), SUM(`Call Later`), SUM(`Ringing Less Than Five Tries`), SUM(`Switched Off Less than Five Tries`), SUM(`Not Interested - Fund Problem`), SUM(`Just Checking Website`), SUM(`Not Interested - Insured From Employer`), SUM(`Not Interested - High Premium`), SUM(`Under Age`), SUM(`Not Visited Website`), SUM(`Do Not Call`), SUM(`Not Interested - General`), SUM(`Over Age`), SUM(`Not Interested - Already Insured`), SUM(`Duplicate Data - Not Interested`), SUM(`Not Eligible - PED`), SUM(`Not Interested - Govt. Employee`), SUM(`Not Interested - Senior Citizen`), SUM(`Wrong Number`), SUM(`Customer Not Available`), SUM(`Language Barrier`), SUM(`No Response`), SUM(`Invalid Number`), SUM(`Junk`), SUM(`Ringing no response after five tries`), SUM(`Switched Off/ Not Reachable after 5 tries`) from ".$table."";

    973. 

  973. mysql_query($queryforinsert);	

    974. 

  974.     $sql_query = "select * from $table";

    975. 

  975.  

    976. 

  976.     // Gets the data from the database

    977. 

  977.     $result = mysql_query($sql_query);

    978. 

  978.     $fields_cnt = mysql_num_fields($result);

    979. 

  979.  

    980. 

  980.  

    981. 

  981.     $schema_insert = '';

    982. 

  982.  

    983. 

  983.     for ($i = 0; $i < $fields_cnt; $i++)

    984. 

  984.     {

    985. 

  985.         $l = $csv_enclosed . str_replace($csv_enclosed, $csv_escaped . $csv_enclosed,

    986. 

  986.             stripslashes(mysql_field_name($result, $i))) . $csv_enclosed;

    987. 

  987.         $schema_insert .= $l;

    988. 

  988.         $schema_insert .= $csv_separator;

    989. 

  989.     } // end for

    990. 

  990.  

    991. 

  991.     $out = trim(substr($schema_insert, 0, -1));

    992. 

  992.     $out .= $csv_terminated;

    993. 

  993.  

    994. 

  994.     // Format the data

    995. 

  995.     while ($row = mysql_fetch_array($result))

    996. 

  996.     {

    997. 

  997.         $schema_insert = '';

    998. 

  998.         for ($j = 0; $j < $fields_cnt; $j++)

    999. 

  999.         {

    1000. 

  1000.             if ($row[$j] == '0' || $row[$j] != '')

    1001. 

  1001.             {

    1002. 

  1002.  

    1003. 

  1003.                 if ($csv_enclosed == '')

    1004. 

  1004.                 {

    1005. 

  1005.                     $schema_insert .= $row[$j];

    1006. 

  1006.                 } else

    1007. 

  1007.                 {

    1008. 

  1008.                     $schema_insert .= $csv_enclosed . 

    1009. 

  1009. 					str_replace($csv_enclosed, $csv_escaped . $csv_enclosed, $row[$j]) . $csv_enclosed;

    1010. 

  1010.                 }

    1011. 

  1011.             } else

    1012. 

  1012.             {

    1013. 

  1013.                 $schema_insert .= '';

    1014. 

  1014.             }

    1015. 

  1015.  

    1016. 

  1016.             if ($j < $fields_cnt - 1)

    1017. 

  1017.             {

    1018. 

  1018.                 $schema_insert .= $csv_separator;

    1019. 

  1019.             }

    1020. 

  1020.         } // end for

    1021. 

  1021.  

    1022. 

  1022.         $out .= $schema_insert;

    1023. 

  1023.         $out .= $csv_terminated;

    1024. 

  1024.     } // end while

    1025. 

  1025.  

    1026. 

  1026. 	$fd = fopen ($filename, "w");

    1027. 

  1027. 	fputs($fd, $out);

    1028. 

  1028. 	fclose($fd);

    1029. 

  1029. 	echo "success";

    1030. 

  1030.     //header("Content-type: text/csv");

    1031. 

  1031.     //header("Content-type: application/csv");

    1032. 

  1032.     //echo $out;

    1033. 

  1033.     exit;

    1034. 

  1034.  

    1035. 

  1035. 	}

    1036. 

  1036. public function actioncampaignwisedispositiondaily(){

    1037. 

  1037. 	

    1038. 

  1038. ini_set('max_input_time', 1200);

    1039. 

  1039. ini_set('max_execution_time', 1200);

    1040. 

  1040. 	$time = time();

    1041. 

  1041. 	$parser = new CHtmlPurifier();

    1042. 

  1042. 	$afterMonth = $parser->purify(intval(@$_REQUEST['month']));

    1043. 

  1043. 	$afterDate = $parser->purify(intval(@$_REQUEST['date']));

    1044. 

  1044. 	$afterYear = $parser->purify(intval(@$_REQUEST['year']));

    1045. 

  1045. 	if(isset($afterMonth) && !empty($afterMonth) && isset($afterDate) && !empty($afterDate) && isset($afterYear) && !empty($afterYear)){

    1046. 

  1046. 	$start = mktime(0, 0, 0,$afterMonth,$afterDate,$afterYear);

    1047. 

  1047. 	$end = mktime(23, 59, 59,$afterMonth,$afterDate,$afterYear);

    1048. 

  1048. 	} else {

    1049. 

  1049.     $start = mktime(0, 0, 0);

    1050. 

  1050. 	$end = mktime(23, 59, 59);

    1051. 

  1051. 	}

    1052. 

  1052. 	$date = date('d_M_Y',$start)."_";

    1053. 

  1053. 	$host = '10.216.6.59'; // MYSQL database host adress

    1054. 

  1054. 	$db = 'lms'; // MYSQL database name

    1055. 

  1055. 	$user = 'root'; // Mysql Datbase user

    1056. 

  1056. 	$pass = '0kwp6aNR'; // Mysql Datbase password	

    1057. 

  1057. 	$link=mysql_connect($host,$user,$pass);

    1058. 

  1058. 	mysql_select_db($db,$link);

    1059. 

  1059. 	$table='report_campaignwisedispositiondaily';

    1060. 

  1060. 	$filename = "filesystem/".$date.$table.'.csv';

    1061. 

  1061.     $csv_terminated = "\n";

    1062. 

  1062.     $csv_separator = ",";

    1063. 

  1063.     $csv_enclosed = '"';

    1064. 

  1064.     $csv_escaped = "\\";

    1065. 

  1065. 	mysql_query("insert into report_info (id,type,filename,createdOn,createdBy,updatedOn,updatedBy) values('','campaignwisedispositiondaily','".@$filename."','".@$time."','0','".@$time."','0')");

    1066. 

  1066. 	$chktableExist = mysql_query('DROP TABLE IF EXISTS '.$table.' ') or die(mysql_error());

    1067. 

  1067. 	$tableres ='create table IF NOT EXISTS '.$table.' as SELECT LS.leadSourceCode as `Agent Code`,LS.leadSourceTitle as `Name`,count(L.ws_agentid) as `Total Diposed`,

    1068. 

  1068.     SUM(CASE WHEN L.`leadStatusId` = 7 THEN 1 ELSE 0 END) AS `Sale Done`,

    1069. 

  1069.     SUM(CASE WHEN L.`leadStatusId` = 8 THEN 1 ELSE 0 END) AS `Follow Up`,

    1070. 

  1070.     SUM(CASE WHEN L.`leadStatusId` = 9 THEN 1 ELSE 0 END) AS `Do Not Call - Will Buy self on Website`,

    1071. 

  1071.     SUM(CASE WHEN L.`leadStatusId` = 10 THEN 1 ELSE 0 END) AS `Duplicate - Already In Talk With Team`,

    1072. 

  1072.     SUM(CASE WHEN L.`leadStatusId` = 37 THEN 1 ELSE 0 END) AS `In Contact with Oflline Agent`,

    1073. 

  1073.     SUM(CASE WHEN L.`leadStatusId` = 38 THEN 1 ELSE 0 END) AS `In Touch With Other Partner`,

    1074. 

  1074.     SUM(CASE WHEN L.`leadStatusId` = 11 THEN 1 ELSE 0 END) AS `Short Hangup`,

    1075. 

  1075.     SUM(CASE WHEN L.`leadStatusId` = 12 THEN 1 ELSE 0 END) AS `Call Later`,

    1076. 

  1076.     SUM(CASE WHEN L.`leadStatusId` = 42 THEN 1 ELSE 0 END) AS `Ringing Less Than Five Tries`,

    1077. 

  1077.     SUM(CASE WHEN L.`leadStatusId` = 43 THEN 1 ELSE 0 END) AS `Switched Off Less than Five Tries`,

    1078. 

  1078.     SUM(CASE WHEN L.`leadStatusId` = 13 THEN 1 ELSE 0 END) AS `Not Interested - Fund Problem`,

    1079. 

  1079.     SUM(CASE WHEN L.`leadStatusId` = 14 THEN 1 ELSE 0 END) AS `Just Checking Website`,

    1080. 

  1080.     SUM(CASE WHEN L.`leadStatusId` = 15 THEN 1 ELSE 0 END) AS `Not Interested - Insured From Employer`,

    1081. 

  1081.     SUM(CASE WHEN L.`leadStatusId` = 16 THEN 1 ELSE 0 END) AS `Not Interested - High Premium`,

    1082. 

  1082.     SUM(CASE WHEN L.`leadStatusId` = 17 THEN 1 ELSE 0 END) AS `Under Age`,

    1083. 

  1083.     SUM(CASE WHEN L.`leadStatusId` = 18 THEN 1 ELSE 0 END) AS `Not Visited Website`,

    1084. 

  1084.     SUM(CASE WHEN L.`leadStatusId` = 19 THEN 1 ELSE 0 END) AS `Do Not Call`,

    1085. 

  1085.     SUM(CASE WHEN L.`leadStatusId` = 20 THEN 1 ELSE 0 END) AS `Not Interested - General`,

    1086. 

  1086.     SUM(CASE WHEN L.`leadStatusId` = 21 THEN 1 ELSE 0 END) AS `Over Age`,

    1087. 

  1087.     SUM(CASE WHEN L.`leadStatusId` = 22 THEN 1 ELSE 0 END) AS `Not Interested - Already Insured`,

    1088. 

  1088.     SUM(CASE WHEN L.`leadStatusId` = 23 THEN 1 ELSE 0 END) AS `Duplicate Data - Not Interested`,

    1089. 

  1089.     SUM(CASE WHEN L.`leadStatusId` = 24 THEN 1 ELSE 0 END) AS `Not Eligible - PED`,

    1090. 

  1090.     SUM(CASE WHEN L.`leadStatusId` = 25 THEN 1 ELSE 0 END) AS `Not Interested - Govt. Employee`,

    1091. 

  1091.     SUM(CASE WHEN L.`leadStatusId` = 26 THEN 1 ELSE 0 END) AS `Not Interested - Senior Citizen`,

    1092. 

  1092.     SUM(CASE WHEN L.`leadStatusId` = 27 THEN 1 ELSE 0 END) AS `Wrong Number`,

    1093. 

  1093.     SUM(CASE WHEN L.`leadStatusId` = 28 THEN 1 ELSE 0 END) AS `Customer Not Available`,

    1094. 

  1094.     SUM(CASE WHEN L.`leadStatusId` = 29 THEN 1 ELSE 0 END) AS `Language Barrier`,

    1095. 

  1095.     SUM(CASE WHEN L.`leadStatusId` = 30 THEN 1 ELSE 0 END) AS `No Response`,

    1096. 

  1096.     SUM(CASE WHEN L.`leadStatusId` = 33 THEN 1 ELSE 0 END) AS `Invalid Number`,

    1097. 

  1097.     SUM(CASE WHEN L.`leadStatusId` = 36 THEN 1 ELSE 0 END) AS `Junk`,

    1098. 

  1098.     SUM(CASE WHEN L.`leadStatusId` = 39 THEN 1 ELSE 0 END) AS `Ringing no response after five tries`,

    1099. 

  1099.     SUM(CASE WHEN L.`leadStatusId` = 43 THEN 1 ELSE 0 END) AS `Switched Off/ Not Reachable after 5 tries` 

    1100. 

  1100.  from leadSourceMaster LS left join lead L on L.ws_agentid=LS.leadSourceCode where L.updatedOn >= "'.@$start.'"  and L.updatedOn <= "'.@$end.'" group by L.leadStatusId,LS.leadSourceCode';

    1101. 

  1101. 	$esx = mysql_query($tableres) or die(mysql_error());

    1102. 

  1102. 	$queryforinsert = "Insert into ".$table." (`Total Diposed`,`Sale Done`,`Follow Up`,`Do Not Call - Will Buy self on Website`,`Duplicate - Already In Talk With Team`,`In Contact with Oflline Agent`,`In Touch With Other Partner`,`Short Hangup`,`Call Later`,`Ringing Less Than Five Tries`,`Switched Off Less than Five Tries`,`Not Interested - Fund Problem`,`Just Checking Website`,`Not Interested - Insured From Employer`,`Not Interested - High Premium`,`Under Age`,`Not Visited Website`,`Do Not Call`,`Not Interested - General`,`Over Age`,`Not Interested - Already Insured`,`Duplicate Data - Not Interested`,`Not Eligible - PED`,`Not Interested - Govt. Employee`,`Not Interested - Senior Citizen`,`Wrong Number`,`Customer Not Available`,`Language Barrier`,`No Response`,`Invalid Number`,`Junk`,`Ringing no response after five tries`,`Switched Off/ Not Reachable after 5 tries`) 

    1103. 

  1103. 	

    1104. 

  1104. 	select SUM(`Total Diposed`), SUM(`Sale Done`), SUM(`Follow Up`), SUM(`Do Not Call - Will Buy self on Website`), SUM(`Duplicate - Already In Talk With Team`), SUM(`In Contact with Oflline Agent`), SUM(`In Touch With Other Partner`), SUM(`Short Hangup`), SUM(`Call Later`), SUM(`Ringing Less Than Five Tries`), SUM(`Switched Off Less than Five Tries`), SUM(`Not Interested - Fund Problem`), SUM(`Just Checking Website`), SUM(`Not Interested - Insured From Employer`), SUM(`Not Interested - High Premium`), SUM(`Under Age`), SUM(`Not Visited Website`), SUM(`Do Not Call`), SUM(`Not Interested - General`), SUM(`Over Age`), SUM(`Not Interested - Already Insured`), SUM(`Duplicate Data - Not Interested`), SUM(`Not Eligible - PED`), SUM(`Not Interested - Govt. Employee`), SUM(`Not Interested - Senior Citizen`), SUM(`Wrong Number`), SUM(`Customer Not Available`), SUM(`Language Barrier`), SUM(`No Response`), SUM(`Invalid Number`), SUM(`Junk`), SUM(`Ringing no response after five tries`), SUM(`Switched Off/ Not Reachable after 5 tries`) from ".$table."";

    1105. 

  1105. 	

    1106. 

  1106. 	mysql_query($queryforinsert);

    1107. 

  1107. 	

    1108. 

  1108.     $sql_query = "select * from ".$table."";

    1109. 

  1109.  

    1110. 

  1110.     // Gets the data from the database

    1111. 

  1111.     $result = mysql_query($sql_query);

    1112. 

  1112.     $fields_cnt = mysql_num_fields($result);

    1113. 

  1113.  

    1114. 

  1114.  

    1115. 

  1115.     $schema_insert = '';

    1116. 

  1116.  

    1117. 

  1117.     for ($i = 0; $i < $fields_cnt; $i++)

    1118. 

  1118.     {

    1119. 

  1119.         $l = $csv_enclosed . str_replace($csv_enclosed, $csv_escaped . $csv_enclosed,

    1120. 

  1120.             stripslashes(mysql_field_name($result, $i))) . $csv_enclosed;

    1121. 

  1121.         $schema_insert .= $l;

    1122. 

  1122.         $schema_insert .= $csv_separator;

    1123. 

  1123.     } // end for

    1124. 

  1124.  

    1125. 

  1125.     $out = trim(substr($schema_insert, 0, -1));

    1126. 

  1126.     $out .= $csv_terminated;

    1127. 

  1127.  

    1128. 

  1128.     // Format the data

    1129. 

  1129.     while ($row = mysql_fetch_array($result))

    1130. 

  1130.     {

    1131. 

  1131.         $schema_insert = '';

    1132. 

  1132.         for ($j = 0; $j < $fields_cnt; $j++)

    1133. 

  1133.         {

    1134. 

  1134.             if ($row[$j] == '0' || $row[$j] != '')

    1135. 

  1135.             {

    1136. 

  1136.  

    1137. 

  1137.                 if ($csv_enclosed == '')

    1138. 

  1138.                 {

    1139. 

  1139.                     $schema_insert .= $row[$j];

    1140. 

  1140.                 } else

    1141. 

  1141.                 {

    1142. 

  1142.                     $schema_insert .= $csv_enclosed . 

    1143. 

  1143. 					str_replace($csv_enclosed, $csv_escaped . $csv_enclosed, $row[$j]) . $csv_enclosed;

    1144. 

  1144.                 }

    1145. 

  1145.             } else

    1146. 

  1146.             {

    1147. 

  1147.                 $schema_insert .= '';

    1148. 

  1148.             }

    1149. 

  1149.  

    1150. 

  1150.             if ($j < $fields_cnt - 1)

    1151. 

  1151.             {

    1152. 

  1152.                 $schema_insert .= $csv_separator;

    1153. 

  1153.             }

    1154. 

  1154.         } // end for

    1155. 

  1155.  

    1156. 

  1156.         $out .= $schema_insert;

    1157. 

  1157.         $out .= $csv_terminated;

    1158. 

  1158.     } // end while

    1159. 

  1159.  

    1160. 

  1160. 	$fd = fopen ($filename, "w");

    1161. 

  1161. 	fputs($fd, $out);

    1162. 

  1162. 	fclose($fd);

    1163. 

  1163. 	echo "success";

    1164. 

  1164.     //header("Content-type: text/csv");

    1165. 

  1165.     //header("Content-type: application/csv");

    1166. 

  1166.     //echo $out;

    1167. 

  1167.     exit;

    1168. 

  1168.  

    1169. 

  1169. 	}

    1170. 

  1170. public function actiondatadumpsameday(){

    1171. 

  1171. ini_set('max_input_time', 1200);

    1172. 

  1172. ini_set('max_execution_time', 1200);

    1173. 

  1173. 	

    1174. 

  1174. 	$parser = new CHtmlPurifier();

    1175. 

  1175. 	$afterMonth = $parser->purify(intval(@$_REQUEST['month']));

    1176. 

  1176. 	$afterDate = $parser->purify(intval(@$_REQUEST['date']));

    1177. 

  1177. 	$afterYear = $parser->purify(intval(@$_REQUEST['year']));

    1178. 

  1178. 	if(isset($afterMonth) && !empty($afterMonth) && isset($afterDate) && !empty($afterDate) && isset($afterYear) && !empty($afterYear)){

    1179. 

  1179. 	$start = mktime(0, 0, 0,$afterMonth,$afterDate,$afterYear);

    1180. 

  1180. 	$end = mktime(23, 59, 59,$afterMonth,$afterDate,$afterYear);

    1181. 

  1181. 	} else {

    1182. 

  1182.     $start = mktime(0, 0, 0);

    1183. 

  1183. 	$end = mktime(23, 59, 59);

    1184. 

  1184. 	}

    1185. 

  1185. 	

    1186. 

  1186. 	$time = time();

    1187. 

  1187. 	$date = date('d_M_Y',$start).'_';

    1188. 

  1188. 	$host = '10.216.6.59'; // MYSQL database host adress

    1189. 

  1189. 	$db = 'lms'; // MYSQL database name

    1190. 

  1190. 	$user = 'root'; // Mysql Datbase user

    1191. 

  1191. 	$pass = '0kwp6aNR'; // Mysql Datbase password	

    1192. 

  1192. 	$link=mysql_connect($host,$user,$pass);

    1193. 

  1193. 	mysql_select_db($db,$link);

    1194. 

  1194. 	$table='report_datadumpsameday';

    1195. 

  1195. 	$filename = "filesystem/".$date.$table.'.csv';

    1196. 

  1196.     $csv_terminated = "\n";

    1197. 

  1197.     $csv_separator = ",";

    1198. 

  1198.     $csv_enclosed = '"';

    1199. 

  1199.     $csv_escaped = "\\";

    1200. 

  1200. 	mysql_query("insert into report_info (id,type,filename,createdOn,createdBy,updatedOn,updatedBy) values('','datadumpsameday','".@$filename."','".@$time."','".@$_SESSION['id']."','".@$time."','".@$_SESSION['id']."')");

    1201. 

  1201. 	$chktableExist = mysql_query('DROP TABLE IF EXISTS '.$table.' ') or die(mysql_error());

    1202. 

  1202. 	mysql_query('set  @a:=0');

    1203. 

  1203. 		$tableres ="create table IF NOT EXISTS ".$table." as select @a:=@a+1 `Sr.no`,L.mobile as `mobile`,from_unixtime(L.createdOn, '%Y/%m/%d') as `Lead Creat Date`,from_unixtime(L.createdOn, '%h:%i:%s %p') as `Lead created Time`,IF((L.currentAllocatedDate='' or L.currentAllocatedDate=0),0,from_unixtime(L.currentAllocatedDate, '%Y/%m/%d')) as `Lead Assign Date`,IF((L.currentAllocatedDate='' or L.currentAllocatedDate=0),0,from_unixtime(L.currentAllocatedDate, '%h:%i:%s %p')) as `Lead Assign Time`,LS.LeadsourceTitle as `Agent Name`,L.ws_agentId as `Agent Code`,CG.campaignName as `Campagin Name`,LSS.leadStatus as `Disposition`,L.leadDescription as `Remarks` from lead L left join leadSourceMaster LS on LS.leadSourceCode=L.ws_agentid left join campaign CG on CG.campaignId=L.campaignId left join leadStatus LSS on LSS.leadstatusId=L.leadStatusId where L.leadStatusId !=36 AND  L.createdOn >= '".@$start."'  and L.createdOn <= '".@$end."'";

    1204. 

  1204. 

    1205. 

  1205. 	//$tableres ="create table IF NOT EXISTS ".$table." as select @a:=@a+1 `Sr.no`,L.mobile as `mobile`,from_unixtime(L.createdOn, '%Y/%m/%d') as `Lead Creat Date`,from_unixtime(L.createdOn, '%h:%i:%s %p') as `Lead created Time`,IF(L.currentAllocatedDate='',0,from_unixtime(L.currentAllocatedDate, '%Y/%m/%d')) as `Lead Assign Date`,IF(L.currentAllocatedDate='',0,from_unixtime(L.currentAllocatedDate, '%h:%i:%s %p')) as `Lead Assign Time`,LS.LeadsourceTitle as `Agent Name`,L.ws_agentId as `Agent Code`,CG.campaignName as `Campagin Name`,LSS.leadStatus as `Disposition`,L.leadDescription as `Remarks` from lead L left join leadSourceMaster LS on LS.leadSourceCode=L.ws_agentid left join campaign CG on CG.campaignId=L.campaignId left join leadStatus LSS on LSS.leadstatusId=L.leadStatusId where L.leadStatusId !=36 AND  L.createdOn >= '".@$start."'  and L.createdOn <= '".@$end."'";

    1206. 

  1206. 	$esx = mysql_query($tableres) or die(mysql_error());

    1207. 

  1207.     $sql_query = "select * from $table";

    1208. 

  1208.  

    1209. 

  1209.     // Gets the data from the database

    1210. 

  1210.     $result = mysql_query($sql_query);

    1211. 

  1211.     $fields_cnt = mysql_num_fields($result);

    1212. 

  1212.  

    1213. 

  1213.  

    1214. 

  1214.     $schema_insert = '';

    1215. 

  1215.  

    1216. 

  1216.     for ($i = 0; $i < $fields_cnt; $i++)

    1217. 

  1217.     {

    1218. 

  1218.         $l = $csv_enclosed . str_replace($csv_enclosed, $csv_escaped . $csv_enclosed,

    1219. 

  1219.             stripslashes(mysql_field_name($result, $i))) . $csv_enclosed;

    1220. 

  1220.         $schema_insert .= $l;

    1221. 

  1221.         $schema_insert .= $csv_separator;

    1222. 

  1222.     } // end for

    1223. 

  1223.  

    1224. 

  1224.     $out = trim(substr($schema_insert, 0, -1));

    1225. 

  1225.     $out .= $csv_terminated;

    1226. 

  1226.  

    1227. 

  1227.     // Format the data

    1228. 

  1228.     while ($row = mysql_fetch_array($result))

    1229. 

  1229.     {

    1230. 

  1230.         $schema_insert = '';

    1231. 

  1231.         for ($j = 0; $j < $fields_cnt; $j++)

    1232. 

  1232.         {

    1233. 

  1233. 		

    1234. 

  1234.             if ($row[$j] == '0' || $row[$j] != '')

    1235. 

  1235.             {

    1236. 

  1236.  

    1237. 

  1237.                 if ($csv_enclosed == '')

    1238. 

  1238.                 {

    1239. 

  1239.                     $schema_insert .= $row[$j];

    1240. 

  1240.                 } else

    1241. 

  1241.                 {

    1242. 

  1242.                     $schema_insert .= $csv_enclosed . 

    1243. 

  1243. 					str_replace($csv_enclosed, $csv_escaped . $csv_enclosed, $row[$j]) . $csv_enclosed;

    1244. 

  1244.                 }

    1245. 

  1245.             } else

    1246. 

  1246.             {

    1247. 

  1247.                 $schema_insert .= '';

    1248. 

  1248.             }

    1249. 

  1249.  

    1250. 

  1250.             if ($j < $fields_cnt - 1)

    1251. 

  1251.             {

    1252. 

  1252.                 $schema_insert .= $csv_separator;

    1253. 

  1253.             }

    1254. 

  1254.         } // end for

    1255. 

  1255.  

    1256. 

  1256.         $out .= $schema_insert;

    1257. 

  1257.         $out .= $csv_terminated;

    1258. 

  1258.     } // end while

    1259. 

  1259.  

    1260. 

  1260. 	$fd = fopen ($filename, "w");

    1261. 

  1261. 	fputs($fd, $out);

    1262. 

  1262. 	fclose($fd);

    1263. 

  1263. 	echo "success";

    1264. 

  1264.     //header("Content-type: text/csv");

    1265. 

  1265.     //header("Content-type: application/csv");

    1266. 

  1266.     //echo $out;

    1267. 

  1267.     exit;

    1268. 

  1268.  

    1269. 

  1269. }

    1270. 

  1270. 

    1271. 

  1271. public function actiondatadumpmtd(){

    1272. 

  1272. ini_set('max_input_time', 19200);

    1273. 

  1273. ini_set('max_execution_time', 19200);

    1274. 

  1274. 	

    1275. 

  1275. 	$parser = new CHtmlPurifier();

    1276. 

  1276. 	$afterMonth = $parser->purify(intval(@$_REQUEST['month']));

    1277. 

  1277. 	$afterYear = $parser->purify(intval(@$_REQUEST['year']));

    1278. 

  1278. 

    1279. 

  1279. 	if(isset($afterMonth) && !empty($afterMonth) && isset($afterYear) && !empty($afterYear)){

    1280. 

  1280. 	$start =mktime(0, 0, 0, $afterMonth, 1,$afterYear);

    1281. 

  1281. 	$end = mktime(23, 59, 0, $afterMonth, date('t', $start),$afterYear);

    1282. 

  1282. 	} else {

    1283. 

  1283.     $start = strtotime('first day of this month', time());

    1284. 

  1284.     $end = strtotime('last day of this month', time());

    1285. 

  1285. 	}

    1286. 

  1286. 	

    1287. 

  1287. 	$date = date('d_M_Y',$start).'_';

    1288. 

  1288. 	$host = '10.216.6.59'; // MYSQL database host adress

    1289. 

  1289. 	$db = 'lms'; // MYSQL database name

    1290. 

  1290. 	$user = 'root'; // Mysql Datbase user

    1291. 

  1291. 	$pass = '0kwp6aNR'; // Mysql Datbase password	

    1292. 

  1292. 	$link=mysql_connect($host,$user,$pass);

    1293. 

  1293. 	mysql_select_db($db,$link);

    1294. 

  1294. 	$table='report_datadumpmtd';

    1295. 

  1295. 	$filename = "filesystem/".$date.$table.'.csv';

    1296. 

  1296.     $csv_terminated = "\n";

    1297. 

  1297.     $csv_separator = ",";

    1298. 

  1298.     $csv_enclosed = '"';

    1299. 

  1299.     $csv_escaped = "\\";

    1300. 

  1300. 	$time = time();

    1301. 

  1301. 	mysql_query("insert into report_info (id,type,filename,createdOn,createdBy,updatedOn,updatedBy) values('','datadumpmtd','".@$filename."','".@$time."','0','".@$time."','0')");

    1302. 

  1302. 	$chktableExist = mysql_query('DROP TABLE IF EXISTS '.$table.' ') or die(mysql_error());

    1303. 

  1303. 

    1304. 

  1304. 

    1305. 

  1305. 

    1306. 

  1306. 

    1307. 

  1307. mysql_query('set  @a:=0');

    1308. 

  1308. 	$tableres ="create table IF NOT EXISTS ".$table." as select @a:=@a+1 `Sr.no`,L.mobile as `mobile`,from_unixtime(L.createdOn, '%Y/%m/%d') as `Lead Creat Date`,from_unixtime(L.createdOn, '%h:%i:%s %p') as `Lead created Time`,IF(L.currentAllocatedDate='',0,from_unixtime(L.currentAllocatedDate, '%Y/%m/%d')) as `Lead Assign Date`,IF(L.currentAllocatedDate='',0,from_unixtime(L.currentAllocatedDate, '%h:%i:%s %p')) as `Lead Assign Time`,LS.LeadsourceTitle as `Agent Name`,L.ws_agentId as `Agent Code`,CG.campaignName as `Campagin Name`,LSS.leadStatus as `Disposition`,L.leadDescription as `Remarks` from lead L left join leadSourceMaster LS on LS.leadSourceCode=L.ws_agentid left join campaign CG on CG.campaignId=L.campaignId left join leadStatus LSS on LSS.leadstatusId=L.leadStatusId where L.leadStatusId !=36 AND  L.updatedOn >= '".@$start."'  and L.updatedOn <= '".@$end."'";

    1309. 

  1309. 	$esx = mysql_query($tableres) or die(mysql_error());

    1310. 

  1310.     $sql_query = "select * from $table";

    1311. 

  1311.  

    1312. 

  1312.     // Gets the data from the database

    1313. 

  1313.     $result = mysql_query($sql_query);

    1314. 

  1314.     $fields_cnt = mysql_num_fields($result);

    1315. 

  1315.  

    1316. 

  1316.  

    1317. 

  1317.     $schema_insert = '';

    1318. 

  1318.  

    1319. 

  1319.     for ($i = 0; $i < $fields_cnt; $i++)

    1320. 

  1320.     {

    1321. 

  1321.         $l = $csv_enclosed . str_replace($csv_enclosed, $csv_escaped . $csv_enclosed,

    1322. 

  1322.             stripslashes(mysql_field_name($result, $i))) . $csv_enclosed;

    1323. 

  1323.         $schema_insert .= $l;

    1324. 

  1324.         $schema_insert .= $csv_separator;

    1325. 

  1325.     } // end for

    1326. 

  1326.  

    1327. 

  1327.     $out = trim(substr($schema_insert, 0, -1));

    1328. 

  1328.     $out .= $csv_terminated;

    1329. 

  1329.  

    1330. 

  1330.     // Format the data

    1331. 

  1331.     while ($row = mysql_fetch_array($result))

    1332. 

  1332.     {

    1333. 

  1333.         $schema_insert = '';

    1334. 

  1334.         for ($j = 0; $j < $fields_cnt; $j++)

    1335. 

  1335.         {

    1336. 

  1336.             if ($row[$j] == '0' || $row[$j] != '')

    1337. 

  1337.             {

    1338. 

  1338.  

    1339. 

  1339.                 if ($csv_enclosed == '')

    1340. 

  1340.                 {

    1341. 

  1341.                     $schema_insert .= $row[$j];

    1342. 

  1342.                 } else

    1343. 

  1343.                 {

    1344. 

  1344.                     $schema_insert .= $csv_enclosed . 

    1345. 

  1345. 					str_replace($csv_enclosed, $csv_escaped . $csv_enclosed, $row[$j]) . $csv_enclosed;

    1346. 

  1346.                 }

    1347. 

  1347.             } else

    1348. 

  1348.             {

    1349. 

  1349.                 $schema_insert .= '';

    1350. 

  1350.             }

    1351. 

  1351.  

    1352. 

  1352.             if ($j < $fields_cnt - 1)

    1353. 

  1353.             {

    1354. 

  1354.                 $schema_insert .= $csv_separator;

    1355. 

  1355.             }

    1356. 

  1356.         } // end for

    1357. 

  1357.  

    1358. 

  1358.         $out .= $schema_insert;

    1359. 

  1359.         $out .= $csv_terminated;

    1360. 

  1360.     } // end while

    1361. 

  1361.  

    1362. 

  1362. 	$fd = fopen ($filename, "w");

    1363. 

  1363. 	fputs($fd, $out);

    1364. 

  1364. 	fclose($fd);

    1365. 

  1365. 	echo "success";

    1366. 

  1366.     //header("Content-type: text/csv");

    1367. 

  1367.     //header("Content-type: application/csv");

    1368. 

  1368.     //echo $out;

    1369. 

  1369.     exit;

    1370. 

  1370.  

    1371. 

  1371.  

    1372. 

  1372. 	}

    1373. 

  1373. 	

    1374. 

  1374. public function actionreporttest(){

    1375. 

  1375.  $maxDays=date('t');

    1376. 

  1376. ini_set('max_input_time', 1200);

    1377. 

  1377. ini_set('max_execution_time', 1200);

    1378. 

  1378. 

    1379. 

  1379. 	$date = date('d_M_Y').'_';

    1380. 

  1380. 	$host = '10.216.6.59'; // MYSQL database host adress

    1381. 

  1381. 	$db = 'lms'; // MYSQL database name

    1382. 

  1382. 	$user = 'root'; // Mysql Datbase user

    1383. 

  1383. 	$pass = '0kwp6aNR'; // Mysql Datbase password	

    1384. 

  1384. 	$link=mysql_connect($host,$user,$pass);

    1385. 

  1385. 	mysql_select_db($db,$link);

    1386. 

  1386. 	$table='report_datadumpmtd';

    1387. 

  1387. $query = mysql_query("select leadSourceCode,leadSourceTitle from leadSourceMaster") ;

    1388. 

  1388. $columnName = array();

    1389. 

  1389. $l=0;

    1390. 

  1390. while($l= mysql_fetch_array($query)){

    1391. 

  1391. $columnName[] = $l['leadSourceCode'];

    1392. 

  1392. $columnNameData[] = $l['leadSourceCode'];

    1393. 

  1393. $leadSourceName[] = $l['leadSourceTitle'];

    1394. 

  1394. $l++;

    1395. 

  1395. }

    1396. 

  1396. 	$columnName=implode(',',$columnName);

    1397. 

  1397. 	$leadSourceName=implode(',',$leadSourceName);

    1398. 

  1398. 	

    1399. 

  1399. if(count($l)>0){

    1400. 

  1400.  $file_name = "lms" . "_" . date("Y-m-d_H-i", time()) . ".csv";

    1401. 

  1401.         header('Content-Type: text/html; charset=utf-8');

    1402. 

  1402.         header("Content-type: application/octet-stream");

    1403. 

  1403.         header("Content-Disposition: attachment; filename=\"$file_name\"");

    1404. 

  1404.         header("Cache-Control: cache, must-revalidate");

    1405. 

  1405.         header("Pragma: public");

    1406. 

  1406.          $col = array(

    1407. 

  1407.           'Agent Code','Total',$columnName);

    1408. 

  1408. 

    1409. 

  1409.           foreach ($col as $key => $value) {

    1410. 

  1410.             echo $value . ",";

    1411. 

  1411.         }

    1412. 

  1412.         echo "\n";

    1413. 

  1413.          $col2 = array(

    1414. 

  1414.           'Agent Name','',$leadSourceName);

    1415. 

  1415. 

    1416. 

  1416.           foreach ($col2 as $key => $value) {

    1417. 

  1417.             echo $value . ",";

    1418. 

  1418.         }

    1419. 

  1419.         echo "\n";

    1420. 

  1420. 			$i=2;

    1421. 

  1421.          $col3 = array($columnNameData);

    1422. 

  1422. 			$f=0;

    1423. 

  1423. 			$neresult = 0;

    1424. 

  1424.             for($y=1;$y<=$maxDays;$y++) {

    1425. 

  1425. 				$date = $y.date('/m/Y');

    1426. 

  1426. 				$explodedDate = explode('/',$date);

    1427. 

  1427. 				$time = strtotime($explodedDate[0]."-".$explodedDate[1]."-".$explodedDate[2]);

    1428. 

  1428. 				$timePm = strtotime($explodedDate[0]."-".$explodedDate[1]."-".$explodedDate[2])+86399;

    1429. 

  1429. 				echo $y.date('/M/Y'). ",";

    1430. 

  1430. 				

    1431. 

  1431. 		  	$quert  = mysql_query('select count(*) as total from lead where ws_agentId IN( "'.@$value[0].'","'.@$value[1].'") and updatedOn >= "'.@$time.'" and updatedOn <= "'.@$timePm.'"'); 

    1432. 

  1432. 			$resT = mysql_fetch_array($quert);

    1433. 

  1433. 				

    1434. 

  1434. 				

    1435. 

  1435. 				$neresult =$neresult+$resT[0];

    1436. 

  1436. 				echo $resT[0] . ",";

    1437. 

  1437.           foreach ($col3 as $key => $value) {

    1438. 

  1438. 		  	$quer  = mysql_query('select count(*) as total from lead where ws_agentId = "'.@$value[0].'" and updatedOn >= "'.@$time.'" and updatedOn <= "'.@$timePm.'"'); 

    1439. 

  1439. 			$res = mysql_fetch_array($quer);

    1440. 

  1440. 		  	$quer1  = mysql_query('select count(*) as total from lead where ws_agentId = "'.@$value[1].'" and updatedOn >= "'.@$time.'" and updatedOn <= "'.@$timePm.'"'); 

    1441. 

  1441. 			$res1 = mysql_fetch_array($quer1);

    1442. 

  1442. 			

    1443. 

  1443.             echo $res[0] . ",";

    1444. 

  1444.             echo $res1[0] . ",";

    1445. 

  1445.           //  echo date('d:m:y::h:i:s A',$time) . ",";

    1446. 

  1446. 		  $f++;

    1447. 

  1447.         }

    1448. 

  1448. 	

    1449. 

  1449.             echo "\n";

    1450. 

  1450.         }

    1451. 

  1451. 			echo 'total data'. ",";

    1452. 

  1452. 			echo $neresult. ",";

    1453. 

  1453.             echo "\n";

    1454. 

  1454. 

    1455. 

  1455. }

    1456. 

  1456. }

    1457. 

  1457. 

    1458. 

  1458. public function actiondatamis_old(){

    1459. 

  1459. ini_set('max_input_time', 1200);

    1460. 

  1460. ini_set('max_execution_time', 1200);

    1461. 

  1461. 	$host = '10.216.6.59'; // MYSQL database host adress

    1462. 

  1462. 	$db = 'lms'; // MYSQL database name

    1463. 

  1463. 	$user = 'root'; // Mysql Datbase user

    1464. 

  1464. 	$pass = '0kwp6aNR'; // Mysql Datbase password	

    1465. 

  1465. 	$link=mysql_connect($host,$user,$pass);

    1466. 

  1466. 	mysql_select_db($db,$link);

    1467. 

  1467. 	$table='report_datamis';

    1468. 

  1468. 	$chktableExist = mysql_query('DROP TABLE IF EXISTS '.$table.' ') or die(mysql_error());

    1469. 

  1469. 

    1470. 

  1470. 

    1471. 

  1471. 

    1472. 

  1472. 	$parser = new CHtmlPurifier();

    1473. 

  1473. 	$afterMonth = $parser->purify(intval(@$_REQUEST['month']));

    1474. 

  1474. 	$afterYear = $parser->purify(intval(@$_REQUEST['year']));

    1475. 

  1475. 

    1476. 

  1476. 	if(isset($afterMonth) && !empty($afterMonth) && isset($afterYear) && !empty($afterYear)){

    1477. 

  1477. 	$start =mktime(0, 0, 0, $afterMonth, 1,$afterYear);

    1478. 

  1478. 	$end = mktime(23, 59, 0, $afterMonth, date('t', $start),$afterYear);

    1479. 

  1479. 	} else {

    1480. 

  1480.     $start = strtotime('first day of this month', time());

    1481. 

  1481.     $end = strtotime('last day of this month', time());

    1482. 

  1482. 	}

    1483. 

  1483. mysql_query("UPDATE `leadSourceMaster` SET leadSourceTitleReport=REPLACE(leadSourceTitle,' ','_')");

    1484. 

  1484. mysql_query("UPDATE `leadSourceMaster` SET leadSourceTitleReport=REPLACE(leadSourceTitleReport,'-','_')");

    1485. 

  1485. 

    1486. 

  1486. mysql_query("SELECT

    1487. 

  1487.   CONCAT('CREATE TABLE ".$table." (',

    1488. 

  1488.     GROUP_CONCAT(DISTINCT CONCAT(leadSourceTitleReport, ' VARCHAR(50)') SEPARATOR ','),

    1489. 

  1489.     ');')

    1490. 

  1490. FROM

    1491. 

  1491.   leadSourceMaster

    1492. 

  1492. INTO @sql") or die(mysql_error());

    1493. 

  1493. 

    1494. 

  1494. mysql_query("PREPARE stmt FROM @sql");

    1495. 

  1495. mysql_query("EXECUTE stmt");

    1496. 

  1496. mysql_query("ALTER TABLE  ".$table." ADD  `AgentName` VARCHAR( 150 ) NOT NULL FIRST ,

    1497. 

  1497. ADD  `Total` VARCHAR( 50 ) NOT NULL AFTER  `AgentName`");

    1498. 

  1498. 

    1499. 

  1499. $sourcecode = mysql_query("select distinct leadSourceTitleReport as leadSourceTitle,leadsourceCode from leadSourceMaster group by leadSourceTitleReport");

    1500. 

  1500.  $row=0; 

    1501. 

  1501.  $arraymerger = array();

    1502. 

  1502.     // $start = strtotime('first day of this month', time());

    1503. 

  1503.     //$end = strtotime('last day of this month', time());

    1504. 

  1504. $totalsourcewise = array();

    1505. 

  1505. $totalsourcewisesales = array();

    1506. 

  1506. $totalcontactable = array();

    1507. 

  1507. $conversionpercentage = array();

    1508. 

  1508. $salesIds = '36';

    1509. 

  1509. $contactableids = '7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22';

    1510. 

  1510. $noncontactableids = '27,28,42,43';

    1511. 

  1511. 

    1512. 

  1512.  while ($row = mysql_fetch_array($sourcecode)){

    1513. 

  1513.   	//echo $row['leadsourceCode'];

    1514. 

  1514. 	$arraymerger[$row['leadsourceCode']] = $row['leadSourceTitle'] ;

    1515. 

  1515. 	//echo 'select SUM(CASE WHEN ws_agentId = "'.@$row['leadsourceCode'].'" THEN 1 ELSE 0 END) AS total from lead'; die;

    1516. 

  1516. 	$quersum  = mysql_query('select SUM(CASE WHEN ws_agentId = "'.@$row['leadsourceCode'].'" and updatedOn >= "'.@$start.'" and updatedOn <= "'.@$end.'" THEN 1 ELSE 0 END) AS total from lead'); 

    1517. 

  1517. 	$querressum = mysql_fetch_assoc($quersum);

    1518. 

  1518. 	$totalsourcewise[] = $querressum['total'];

    1519. 

  1519. 

    1520. 

  1520. 	$quersumsales  = mysql_query('select SUM(CASE WHEN ws_agentId = "'.@$row['leadsourceCode'].'" and updatedOn >= "'.@$start.'" and updatedOn <= "'.@$end.'" and leadStatusId IN('.@$salesIds.') THEN 1 ELSE 0 END) AS total from lead'); 

    1521. 

  1521. 	$querressumsales = mysql_fetch_assoc($quersumsales);

    1522. 

  1522. 	$totalsourcewisesales[] = $querressumsales['total'];

    1523. 

  1523. 

    1524. 

  1524. $conversionpercentage[$querressumsales['total']] = $querressum['total'];

    1525. 

  1525. 	$quersumcontactable  = mysql_query('select SUM(CASE WHEN ws_agentId = "'.@$row['leadsourceCode'].'" and updatedOn >= "'.@$start.'" and updatedOn <= "'.@$end.'" and leadStatusId IN('.@$contactableids.') THEN 1 ELSE 0 END) AS total from lead'); 

    1526. 

  1526. 	$querressumcontactable = mysql_fetch_assoc($quersumcontactable);

    1527. 

  1527. 	$totalcontactable[] = $querressumcontactable['total'];

    1528. 

  1528. 

    1529. 

  1529. 

    1530. 

  1530. 

    1531. 

  1531.  $row++; }

    1532. 

  1532.  $keys = (implode(",",array_keys($arraymerger)));

    1533. 

  1533.  $values = (implode(",",array_values($arraymerger)));

    1534. 

  1534. 

    1535. 

  1535. $q="replace into ".$table." (AgentName,".$values.") values ('AgentCode',".$keys.")"; 

    1536. 

  1536. mysql_query($q) or die(mysql_error());

    1537. 

  1537. 

    1538. 

  1538. 

    1539. 

  1539.  $maxDays=date('t');

    1540. 

  1540.  $totalData = 0;

    1541. 

  1541.  $totalDatasales = 0;

    1542. 

  1542.  $totalDatacontactable = 0;

    1543. 

  1543.  for($y=1;$y<=$maxDays;$y++) {

    1544. 

  1544. 	$date = $y.date('/m/Y');

    1545. 

  1545. 	$explodedDate = explode('/',$date);

    1546. 

  1546. 	$time = strtotime($explodedDate[0]."-".$explodedDate[1]."-".$explodedDate[2]);

    1547. 

  1547. 	$timePm = strtotime($explodedDate[0]."-".$explodedDate[1]."-".$explodedDate[2])+86399;

    1548. 

  1548. 	$totalcountcode = array();

    1549. 

  1549. 	$totalcountsales = array();

    1550. 

  1550. 	$totalcountcontact = array();

    1551. 

  1551. 	$totalcountcoderes=0;

    1552. 

  1552. 	foreach($arraymerger as $key=>$values1){

    1553. 

  1553. 	$quer  = mysql_query('select count(*) as total from lead where ws_agentId = "'.@$key.'" and updatedOn >= "'.@$time.'" and updatedOn <= "'.@$timePm.'"'); 

    1554. 

  1554. 	$querres = mysql_fetch_assoc($quer);

    1555. 

  1555. 	$totalcountcode[] = $querres['total'];

    1556. 

  1556. 	$totalcountcoderes =$querres['total'];

    1557. 

  1557. 

    1558. 

  1558. 

    1559. 

  1559. 	$salesquery  = mysql_query('select SUM(CASE WHEN ws_agentId = "'.@$key.'" and updatedOn >= "'.@$time.'" and updatedOn <= "'.@$timePm.'" and leadStatusId IN('.@$salesIds.') THEN 1 ELSE 0 END) as total from lead '); 

    1560. 

  1560. 	$salesqueryres = mysql_fetch_array($salesquery);

    1561. 

  1561. 	$totalcountsales[] = $salesqueryres['total'];

    1562. 

  1562. 

    1563. 

  1563. 	$contactquery  = mysql_query('select SUM(CASE WHEN ws_agentId = "'.@$key.'" and updatedOn >= "'.@$time.'" and updatedOn <= "'.@$timePm.'" and leadStatusId IN('.@$contactableids.') THEN 1 ELSE 0 END) as total from lead '); 

    1564. 

  1564. 	$contactqueryres = mysql_fetch_array($contactquery);

    1565. 

  1565. 	$totalcountcontact[] = $contactqueryres['total'];

    1566. 

  1566. 

    1567. 

  1567. 	}

    1568. 

  1568. 	

    1569. 

  1569. 	 $totalsumres =  array_sum($totalcountcode);

    1570. 

  1570. 	 $totalsumresales =  array_sum($totalcountsales);

    1571. 

  1571. 	 $totalcountcontact =  array_sum($totalcountcontact);

    1572. 

  1572. 	 $implodedcountcode = implode(",",$totalcountcode);

    1573. 

  1573. 	$totalData = $totalData+intval($totalsumres);

    1574. 

  1574. 	$totalDatasales = $totalDatasales+intval($totalsumresales);

    1575. 

  1575. 	$totalDatacontactable = $totalDatacontactable+intval($totalcountcontact);

    1576. 

  1576. 	 intval($totalcountcoderes);

    1577. 

  1577. 

    1578. 

  1578. 	 $totalsumsales =  array_sum($totalcountsales);

    1579. 

  1579. //print_r($totalcountsales);

    1580. 

  1580. 	  $datequery = "insert into ".$table."(`AgentName`,`Total`,".$values.") values('".$y.date('/M/Y')."',".$totalsumres.",".$implodedcountcode.")";

    1581. 

  1581.   mysql_query($datequery);

    1582. 

  1582. 

    1583. 

  1583. 

    1584. 

  1584. 

    1585. 

  1585.  

    1586. 

  1586.  }

    1587. 

  1587.  $contactper = $totalcontactable;

    1588. 

  1588.  $datacontper = $totalsourcewise;

    1589. 

  1589.  $datacontpertest = $totalsourcewisesales;

    1590. 

  1590.  $totalsourcewise = implode(",",$totalsourcewise); 

    1591. 

  1591.  $totalsourcewisesales = implode(",",$totalsourcewisesales); 

    1592. 

  1592.  $totalcontactable = implode(",",$totalcontactable); 

    1593. 

  1593.  if(@$totalDatacontactable > 0){

    1594. 

  1594.  	$contactperc = '100';

    1595. 

  1595.  } else {

    1596. 

  1596.  	$contactperc = '0';

    1597. 

  1597.  }

    1598. 

  1598. $contactpervalu =array();

    1599. 

  1599.  foreach($contactper as $key2=>$value2){

    1600. 

  1600.  	if($totalDatacontactable > 0){

    1601. 

  1601.   $contactpervalu[] = '"'.($value2*100)/$totalDatacontactable.'%"';

    1602. 

  1602. 	} else {

    1603. 

  1603.   $contactpervalu[] = "'0%'";

    1604. 

  1604. 	}

    1605. 

  1605. //  $contactpervalu[] = '"'.($value2*100)/$totalDatacontactable.'%"';

    1606. 

  1606.  }

    1607. 

  1607.  $contactpervalu = implode(",",$contactpervalu); 

    1608. 

  1608. 

    1609. 

  1609. 

    1610. 

  1610. 

    1611. 

  1611.  if(@$totalData > 0){

    1612. 

  1612.  	$Datacontributionprec = '100';

    1613. 

  1613.  } else {

    1614. 

  1614.  	$Datacontributionprec = '0';

    1615. 

  1615.  }

    1616. 

  1616. $datacontpervalu =array();

    1617. 

  1617.  foreach($datacontper as $key4=>$value4){

    1618. 

  1618.  if($totalData > 0){

    1619. 

  1619.   $datacontpervalu[] = '"'.($value4*100)/$totalData.'%"';

    1620. 

  1620.  } else {

    1621. 

  1621.   $datacontpervalu[] = "'0%'";

    1622. 

  1622.  }

    1623. 

  1623.  }

    1624. 

  1624.  $datacontpervalu = implode(",",$datacontpervalu); 

    1625. 

  1625. 

    1626. 

  1626. $conversionPerkeys = array_keys($conversionpercentage);

    1627. 

  1627. $conversionPervalue = array_values($conversionpercentage);

    1628. 

  1628. 

    1629. 

  1629. $conversionnetres = array();

    1630. 

  1630. for($r=0;$r<count($datacontper);$r++){

    1631. 

  1631. 

    1632. 

  1632. 

    1633. 

  1633.  if($datacontper[$r] > 0){

    1634. 

  1634.   $conversionnetres[$r] = ($datacontpertest[$r]/$datacontper[$r]);

    1635. 

  1635.  } else {

    1636. 

  1636.   $conversionnetres[$r] = "'0%'";;

    1637. 

  1637.  }

    1638. 

  1638. }

    1639. 

  1639.  $conversionpercentagevalu = implode(",",$conversionnetres); 

    1640. 

  1640.  if($totalData > 0){

    1641. 

  1641. $dataconversionrationtotal = ($totalDatasales/$totalData);

    1642. 

  1642.  } else {

    1643. 

  1643. $dataconversionrationtotal = 0;

    1644. 

  1644.  

    1645. 

  1645.  }

    1646. 

  1646. //echo "insert into ".$table."(`AgentName`,`Total`,".@$values.") values('Conatctable%','".@$contactperc."%',".@$contactpervalu.")"; die;

    1647. 

  1647.   mysql_query("insert into ".$table."(`AgentName`,`Total`,".@$values.") values('TotalData','".@$totalData."',".@$totalsourcewise.")");

    1648. 

  1648.   mysql_query("insert into ".$table."(`AgentName`,`Total`,".@$values.") values('Sale','".@$totalDatasales."',".@$totalsourcewisesales.")");

    1649. 

  1649.   mysql_query("insert into ".$table."(`AgentName`,`Total`,".@$values.") values('Conatctabledata','".@$totalDatacontactable."',".@$totalcontactable.")");

    1650. 

  1650.   mysql_query("insert into ".$table."(`AgentName`,`Total`,".@$values.") values('Conatctable%','".@$contactperc."%',".@$contactpervalu.")");

    1651. 

  1651.   mysql_query("insert into ".$table."(`AgentName`,`Total`,".@$values.") values('Datacontribution%','".@$Datacontributionprec."%',".@$datacontpervalu.")");

    1652. 

  1652.   mysql_query("insert into ".$table."(`AgentName`,`Total`,".@$values.") values('Conversion%','".@$dataconversionrationtotal."%',".@$conversionpercentagevalu.")");

    1653. 

  1653. //	$tableres ="create table IF NOT EXISTS ".$table." as select  leadSourceCode,leadSourceTitle from leadSourceMaster";

    1654. 

  1654. 	//$esx = mysql_query($tableres) or die(mysql_error());

    1655. 

  1655. $this->actiondatamis_download($start);

    1656. 

  1656. }

    1657. 

  1657. public function actiondatamis(){

    1658. 

  1658. ini_set('max_input_time', 19200);

    1659. 

  1659. ini_set('max_execution_time', 19200);

    1660. 

  1660. 	$host = '10.216.6.59'; // MYSQL database host adress

    1661. 

  1661. 	$db = 'lms'; // MYSQL database name

    1662. 

  1662. 	$user = 'root'; // Mysql Datbase user

    1663. 

  1663. 	$pass = '0kwp6aNR'; // Mysql Datbase password	

    1664. 

  1664. 	$link=mysql_connect($host,$user,$pass);

    1665. 

  1665. 	mysql_select_db($db,$link);

    1666. 

  1666. 	$table='report_datamis';

    1667. 

  1667. 	$chktableExist = mysql_query('DROP TABLE IF EXISTS '.$table.' ') or die(mysql_error());

    1668. 

  1668. 	$parser = new CHtmlPurifier();

    1669. 

  1669. 	$afterMonth = $parser->purify(intval(@$_REQUEST['month']));

    1670. 

  1670. 	$afterYear = $parser->purify(intval(@$_REQUEST['year']));

    1671. 

  1671. 

    1672. 

  1672. 	if(isset($afterMonth) && !empty($afterMonth) && isset($afterYear) && !empty($afterYear)){

    1673. 

  1673. 	$start =mktime(0, 0, 0, $afterMonth, 1,$afterYear);

    1674. 

  1674. 	$end = mktime(23, 59, 0, $afterMonth, date('t', $start),$afterYear);

    1675. 

  1675. 	} else {

    1676. 

  1676.     $start = strtotime('first day of this month', time());

    1677. 

  1677.     $end = strtotime('last day of this month', time());

    1678. 

  1678. 	}

    1679. 

  1679. mysql_query("UPDATE `leadSourceMaster` SET leadSourceTitleReport=REPLACE(REPLACE(REPLACE(leadSourceTitle,' ',''),'-','_'),'.','')");

    1680. 

  1680. 

    1681. 

  1681. $resnews = mysql_query("SELECT group_concat(CONCAT(`leadSourceTitleReport`)) as res FROM `leadSourceMaster`");

    1682. 

  1682. $netres = mysql_fetch_array($resnews);

    1683. 

  1683. $strg = $netres['res'];

    1684. 

  1684. $replacedstr = str_replace(',',' varchar(50) ,',$strg)." varchar(50)";

    1685. 

  1685. 	

    1686. 

  1686. 	$createsql = "CREATE TABLE IF NOT EXISTS ".$table." (

    1687. 

  1687.   ".$replacedstr."

    1688. 

  1688. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

    1689. 

  1689. ";

    1690. 

  1690. 

    1691. 

  1691. mysql_query($createsql) or die(mysql_error());

    1692. 

  1692. 

    1693. 

  1693. mysql_query("ALTER TABLE  ".$table." ADD  `AgentName` VARCHAR( 150 ) NOT NULL FIRST ,

    1694. 

  1694. ADD  `Total` VARCHAR( 50 ) NOT NULL AFTER  `AgentName`");

    1695. 

  1695. 

    1696. 

  1696. $sourcecode = mysql_query("select distinct leadSourceTitleReport as leadSourceTitle,leadsourceCode from leadSourceMaster group by leadSourceTitleReport");

    1697. 

  1697.  $row=0; 

    1698. 

  1698.  $arraymerger = array();

    1699. 

  1699.     // $start = strtotime('first day of this month', time());

    1700. 

  1700.    // $end = strtotime('last day of this month', time());

    1701. 

  1701. $totalsourcewise = array();

    1702. 

  1702. $totalsourcewisesales = array();

    1703. 

  1703. $totalcontactable = array();

    1704. 

  1704. $conversionpercentage = array();

    1705. 

  1705. $salesIds = '7';

    1706. 

  1706. $contactableids = '7,8,9,37,38,11,12,13,14,15,16,17,22,23,24,25,26';

    1707. 

  1707. $noncontactableids = '39,40,27,28,29,30,33,42,43';

    1708. 

  1708. 

    1709. 

  1709.  while ($row = mysql_fetch_array($sourcecode)){

    1710. 

  1710.   	//echo $row['leadsourceCode'];

    1711. 

  1711. 	$arraymerger[$row['leadsourceCode']] = $row['leadSourceTitle'] ;

    1712. 

  1712. 	//echo 'select SUM(CASE WHEN ws_agentId = "'.@$row['leadsourceCode'].'" THEN 1 ELSE 0 END) AS total from lead'; die;

    1713. 

  1713. 	$quersum  = mysql_query('select SUM(CASE WHEN ws_agentId = "'.@$row['leadsourceCode'].'" and createdOn >= "'.@$start.'" and createdOn <= "'.@$end.'" AND leadStatusId !=36 AND leadStatusId !=10   THEN 1 ELSE 0 END) AS total from lead'); 

    1714. 

  1714. 	$querressum = mysql_fetch_assoc($quersum);

    1715. 

  1715. 	$totalsourcewise[] = $querressum['total'];

    1716. 

  1716. 

    1717. 

  1717. 	$quersumsales  = mysql_query('select SUM(CASE WHEN ws_agentId = "'.@$row['leadsourceCode'].'" and createdOn >= "'.@$start.'" and createdOn <= "'.@$end.'" AND leadStatusId !=36   AND leadStatusId !=10 and leadStatusId IN('.@$salesIds.') THEN 1 ELSE 0 END) AS total from lead'); 

    1718. 

  1718. 	$querressumsales = mysql_fetch_assoc($quersumsales);

    1719. 

  1719. 	$totalsourcewisesales[] = $querressumsales['total'];

    1720. 

  1720. 

    1721. 

  1721. $conversionpercentage[$querressumsales['total']] = $querressum['total'];

    1722. 

  1722. 	$quersumcontactable  = mysql_query('select SUM(CASE WHEN ws_agentId = "'.@$row['leadsourceCode'].'" and createdOn >= "'.@$start.'" and createdOn <= "'.@$end.'" AND leadStatusId !=36 AND leadStatusId !=10 and leadStatusId IN('.@$contactableids.') THEN 1 ELSE 0 END) AS total from lead'); 

    1723. 

  1723. 	$querressumcontactable = mysql_fetch_assoc($quersumcontactable);

    1724. 

  1724. 	$totalcontactable[] = $querressumcontactable['total'];

    1725. 

  1725. 

    1726. 

  1726. 

    1727. 

  1727. 

    1728. 

  1728.  $row++; }

    1729. 

  1729.  $keys = (implode(",",array_keys($arraymerger)));

    1730. 

  1730.  $values = (implode(",",array_values($arraymerger)));

    1731. 

  1731. 

    1732. 

  1732. $q="replace into ".$table." (AgentName,".$values.") values ('AgentCode',".$keys.")"; 

    1733. 

  1733. mysql_query($q) or die(mysql_error());

    1734. 

  1734. 

    1735. 

  1735. 

    1736. 

  1736. // $maxDays=date('t');

    1737. 

  1737.   $maxDays=date('d');

    1738. 

  1738. 

    1739. 

  1739.  $totalData = 0;

    1740. 

  1740.  $totalDatasales = 0;

    1741. 

  1741.  $totalDatacontactable = 0;

    1742. 

  1742.  for($y=1;$y<=$maxDays;$y++) {

    1743. 

  1743. 	$date = $y.date('/m/Y');

    1744. 

  1744. 	$explodedDate = explode('/',$date);

    1745. 

  1745. 	$time = strtotime($explodedDate[0]."-".$explodedDate[1]."-".$explodedDate[2]);

    1746. 

  1746. 	$timePm = strtotime($explodedDate[0]."-".$explodedDate[1]."-".$explodedDate[2])+86399;

    1747. 

  1747. 	$totalcountcode = array();

    1748. 

  1748. 	$totalcountsales = array();

    1749. 

  1749. 	$totalcountcontact = array();

    1750. 

  1750. 	$totalcountcoderes=0;

    1751. 

  1751. 	foreach($arraymerger as $key=>$values1){

    1752. 

  1752. 	$quer  = mysql_query('select count(*) as total from lead where ws_agentId = "'.@$key.'" and createdOn >= "'.@$time.'" AND leadStatusId !=36 AND leadStatusId !=10 and createdOn <= "'.@$timePm.'"'); 

    1753. 

  1753. 	$querres = mysql_fetch_assoc($quer);

    1754. 

  1754. 	$totalcountcode[] = $querres['total'];

    1755. 

  1755. 	$totalcountcoderes =$querres['total'];

    1756. 

  1756. 

    1757. 

  1757. 

    1758. 

  1758. 	$salesquery  = mysql_query('select SUM(CASE WHEN ws_agentId = "'.@$key.'" and createdOn >= "'.@$time.'" AND leadStatusId !=36 AND leadStatusId !=10  and createdOn <= "'.@$timePm.'" and leadStatusId IN('.@$salesIds.') THEN 1 ELSE 0 END) as total from lead '); 

    1759. 

  1759. 	$salesqueryres = mysql_fetch_array($salesquery);

    1760. 

  1760. 	$totalcountsales[] = $salesqueryres['total'];

    1761. 

  1761. 

    1762. 

  1762. 	$contactquery  = mysql_query('select SUM(CASE WHEN ws_agentId = "'.@$key.'" and createdOn >= "'.@$time.'"  AND leadStatusId !=36 AND leadStatusId !=10  and createdOn <= "'.@$timePm.'" and leadStatusId IN('.@$contactableids.') THEN 1 ELSE 0 END) as total from lead '); 

    1763. 

  1763. 	$contactqueryres = mysql_fetch_array($contactquery);

    1764. 

  1764. 	$totalcountcontact[] = $contactqueryres['total'];

    1765. 

  1765. 

    1766. 

  1766. 	}

    1767. 

  1767. 	

    1768. 

  1768. 	 $totalsumres =  array_sum($totalcountcode);

    1769. 

  1769. 	 $totalsumresales =  array_sum($totalcountsales);

    1770. 

  1770. 	 $totalcountcontact =  array_sum($totalcountcontact);

    1771. 

  1771. 	 $implodedcountcode = implode(",",$totalcountcode);

    1772. 

  1772. 	$totalData = $totalData+intval($totalsumres);

    1773. 

  1773. 	$totalDatasales = $totalDatasales+intval($totalsumresales);

    1774. 

  1774. 	$totalDatacontactable = $totalDatacontactable+intval($totalcountcontact);

    1775. 

  1775. 	 intval($totalcountcoderes);

    1776. 

  1776. 

    1777. 

  1777. 	 $totalsumsales =  array_sum($totalcountsales);

    1778. 

  1778. //print_r($totalcountsales);

    1779. 

  1779. 	  $datequery = "insert into ".$table."(`AgentName`,`Total`,".$values.") values('".$y.date('/M/Y')."',".$totalsumres.",".$implodedcountcode.")";

    1780. 

  1780.   mysql_query($datequery);

    1781. 

  1781. 

    1782. 

  1782. 

    1783. 

  1783. 

    1784. 

  1784.  

    1785. 

  1785.  }

    1786. 

  1786.  $contactper = $totalcontactable;

    1787. 

  1787.  $datacontper = $totalsourcewise;

    1788. 

  1788.  $datacontpertest = $totalsourcewisesales;

    1789. 

  1789.  $totalsourcewise = implode(",",$totalsourcewise); 

    1790. 

  1790.  $totalsourcewisesales = implode(",",$totalsourcewisesales); 

    1791. 

  1791.  $totalcontactable = implode(",",$totalcontactable); 

    1792. 

  1792.  if(@$totalDatacontactable > 0){

    1793. 

  1793.  	//$contactperc = ($totalDatacontactable*100)/$totalData;

    1794. 

  1794. 	 	$contactperc = ($totalDatasales*100)/$totalData;

    1795. 

  1795. 

    1796. 

  1796.  } else {

    1797. 

  1797.  	$contactperc = '0';

    1798. 

  1798.  }

    1799. 

  1799. $contactpervalu =array();

    1800. 

  1800.  foreach($contactper as $key2=>$value2){

    1801. 

  1801.  	if($datacontper[$key2] > 0){

    1802. 

  1802.   $contactpervalu[] = '"'.($value2*100)/$datacontper[$key2].'%"';

    1803. 

  1803. 	} else {

    1804. 

  1804.   $contactpervalu[] = "'0%'";

    1805. 

  1805. 	}

    1806. 

  1806. //  $contactpervalu[] = '"'.($value2*100)/$totalDatacontactable.'%"';

    1807. 

  1807.  }

    1808. 

  1808.  $contactpervalu = implode(",",$contactpervalu); 

    1809. 

  1809. 

    1810. 

  1810. 

    1811. 

  1811. 

    1812. 

  1812.  if(@$totalData > 0){

    1813. 

  1813.  	$Datacontributionprec = '100';

    1814. 

  1814.  } else {

    1815. 

  1815.  	$Datacontributionprec = '0';

    1816. 

  1816.  }

    1817. 

  1817. $datacontpervalu =array();

    1818. 

  1818.  foreach($datacontper as $key4=>$value4){

    1819. 

  1819.  if($totalData > 0){

    1820. 

  1820.   $datacontpervalu[] = '"'.($value4*100)/$totalData.'%"';

    1821. 

  1821.  } else {

    1822. 

  1822.   $datacontpervalu[] = "'0%'";

    1823. 

  1823.  }

    1824. 

  1824.  }

    1825. 

  1825.  $datacontpervalu = implode(",",$datacontpervalu); 

    1826. 

  1826. 

    1827. 

  1827. $conversionPerkeys = array_keys($conversionpercentage);

    1828. 

  1828. $conversionPervalue = array_values($conversionpercentage);

    1829. 

  1829. 

    1830. 

  1830. $conversionnetres = array();

    1831. 

  1831. for($r=0;$r<count($datacontper);$r++){

    1832. 

  1832. 

    1833. 

  1833. 

    1834. 

  1834.  if($datacontper[$r] > 0){

    1835. 

  1835.   $conversionnetres[$r] = ($datacontpertest[$r]/$datacontper[$r]).'%"';

    1836. 

  1836.  } else {

    1837. 

  1837.   $conversionnetres[$r] = "'0%'";

    1838. 

  1838.  }

    1839. 

  1839. }

    1840. 

  1840.  $conversionpercentagevalu = implode(",",$conversionnetres); 

    1841. 

  1841.  if($totalData > 0){

    1842. 

  1842. $dataconversionrationtotal = ($totalDatasales/$totalData);

    1843. 

  1843.  } else {

    1844. 

  1844. $dataconversionrationtotal = 0;

    1845. 

  1845.  

    1846. 

  1846.  }

    1847. 

  1847. //echo "insert into ".$table."(`AgentName`,`Total`,".@$values.") values('Conatctable%','".@$contactperc."%',".@$contactpervalu.")"; die;

    1848. 

  1848.   mysql_query("insert into ".$table."(`AgentName`,`Total`,".@$values.") values('TotalData','".@$totalData."',".@$totalsourcewise.")");

    1849. 

  1849.   mysql_query("insert into ".$table."(`AgentName`,`Total`,".@$values.") values('Sale','".@$totalDatasales."',".@$totalsourcewisesales.")");

    1850. 

  1850.   mysql_query("insert into ".$table."(`AgentName`,`Total`,".@$values.") values('Conatctabledata','".@$totalDatacontactable."',".@$totalcontactable.")");

    1851. 

  1851.   mysql_query("insert into ".$table."(`AgentName`,`Total`,".@$values.") values('Conatctable%','".@$contactperc."%',".@$contactpervalu.")");

    1852. 

  1852.   mysql_query("insert into ".$table."(`AgentName`,`Total`,".@$values.") values('Datacontribution%','".@$Datacontributionprec."%',".@$datacontpervalu.")");

    1853. 

  1853.   mysql_query("insert into ".$table."(`AgentName`,`Total`,".@$values.") values('Conversion%','".@$dataconversionrationtotal."%',".@$conversionpercentagevalu.")");

    1854. 

  1854. //	$tableres ="create table IF NOT EXISTS ".$table." as select  leadSourceCode,leadSourceTitle from leadSourceMaster";

    1855. 

  1855. 	//$esx = mysql_query($tableres) or die(mysql_error());

    1856. 

  1856. $this->actiondatamis_download($start);

    1857. 

  1857. }

    1858. 

  1858. 

    1859. 

  1859. public function actiondatamis_download($start){

    1860. 

  1860. 	ini_set('max_input_time', 300);

    1861. 

  1861. ini_set('max_execution_time', 300);

    1862. 

  1862. 

    1863. 

  1863. 	$date = date('d_M_Y').'_'; 

    1864. 

  1864. 	$host = '10.216.6.59'; // MYSQL database host adress

    1865. 

  1865. 	$db = 'lms'; // MYSQL database name

    1866. 

  1866. 	$user = 'root'; // Mysql Datbase user

    1867. 

  1867. 	$pass = '0kwp6aNR'; // Mysql Datbase password	

    1868. 

  1868. 	$link=mysql_connect($host,$user,$pass);

    1869. 

  1869. 	mysql_select_db($db,$link);

    1870. 

  1870. 	$table='report_datamis';

    1871. 

  1871. 	$filename = "filesystem/".$date.$table.'.csv';

    1872. 

  1872.     $csv_terminated = "\n";

    1873. 

  1873.     $csv_separator = ",";

    1874. 

  1874.     $csv_enclosed = '"';

    1875. 

  1875.     $csv_escaped = "\\";

    1876. 

  1876. 	$time = time();

    1877. 

  1877. 	mysql_query("insert into report_info (id,type,filename,createdOn,createdBy,updatedOn,updatedBy) values('','datadumpmis','".@$filename."','".@$time."','0','".@$time."','0')");

    1878. 

  1878. 	

    1879. 

  1879.     $sql_query = "select * from ".$table."";

    1880. 

  1880.  

    1881. 

  1881.     // Gets the data from the database

    1882. 

  1882.     $result = mysql_query($sql_query);

    1883. 

  1883.     $fields_cnt = mysql_num_fields($result);

    1884. 

  1884.  

    1885. 

  1885.  

    1886. 

  1886.     $schema_insert = '';

    1887. 

  1887.  

    1888. 

  1888.     for ($i = 0; $i < $fields_cnt; $i++)

    1889. 

  1889.     {

    1890. 

  1890.         $l = $csv_enclosed . str_replace($csv_enclosed, $csv_escaped . $csv_enclosed,

    1891. 

  1891.             stripslashes(mysql_field_name($result, $i))) . $csv_enclosed;

    1892. 

  1892.         $schema_insert .= $l;

    1893. 

  1893.         $schema_insert .= $csv_separator;

    1894. 

  1894.     } // end for

    1895. 

  1895.  

    1896. 

  1896.     $out = trim(substr($schema_insert, 0, -1));

    1897. 

  1897.     $out .= $csv_terminated;

    1898. 

  1898.  

    1899. 

  1899.     // Format the data

    1900. 

  1900.     while ($row = mysql_fetch_array($result))

    1901. 

  1901.     {

    1902. 

  1902.         $schema_insert = '';

    1903. 

  1903.         for ($j = 0; $j < $fields_cnt; $j++)

    1904. 

  1904.         {

    1905. 

  1905.             if ($row[$j] == '0' || $row[$j] != '')

    1906. 

  1906.             {

    1907. 

  1907.  

    1908. 

  1908.                 if ($csv_enclosed == '')

    1909. 

  1909.                 {

    1910. 

  1910.                     $schema_insert .= $row[$j];

    1911. 

  1911.                 } else

    1912. 

  1912.                 {

    1913. 

  1913.                     $schema_insert .= $csv_enclosed . 

    1914. 

  1914. 					str_replace($csv_enclosed, $csv_escaped . $csv_enclosed, $row[$j]) . $csv_enclosed;

    1915. 

  1915.                 }

    1916. 

  1916.             } else

    1917. 

  1917.             {

    1918. 

  1918.                 $schema_insert .= '';

    1919. 

  1919.             }

    1920. 

  1920.  

    1921. 

  1921.             if ($j < $fields_cnt - 1)

    1922. 

  1922.             {

    1923. 

  1923.                 $schema_insert .= $csv_separator;

    1924. 

  1924.             }

    1925. 

  1925.         } // end for

    1926. 

  1926.  

    1927. 

  1927.         $out .= $schema_insert;

    1928. 

  1928.         $out .= $csv_terminated;

    1929. 

  1929.     } // end while

    1930. 

  1930.  

    1931. 

  1931. 	$fd = fopen($filename, "w");

    1932. 

  1932. 	fputs($fd, $out);

    1933. 

  1933. 	fclose($fd);

    1934. 

  1934. 	echo "success";

    1935. 

  1935.     //header("Content-type: text/csv");

    1936. 

  1936.     //header("Content-type: application/csv");

    1937. 

  1937.     //echo $out;

    1938. 

  1938.     exit;

    1939. 

  1939.  

    1940. 

  1940. 	

    1941. 

  1941. 	}

    1942. 

  1942. 

    1943. 

  1943. public function actiondownloadallreports(){

    1944. 

  1944. $this->render('downloadallreports');

    1945. 

  1945. }

    1946. 

  1946. 

    1947. 

  1947. public function actiondatamisdatewise(){

    1948. 

  1948. ini_set('max_input_time', 19200);

    1949. 

  1949. ini_set('max_execution_time', 19200);

    1950. 

  1950. 	$host = '10.216.6.59'; // MYSQL database host adress

    1951. 

  1951. 	$db = 'lms'; // MYSQL database name

    1952. 

  1952. 	$user = 'root'; // Mysql Datbase user

    1953. 

  1953. 	$pass = '0kwp6aNR'; // Mysql Datbase password	

    1954. 

  1954. 	$link=mysql_connect($host,$user,$pass);

    1955. 

  1955. 	mysql_select_db($db,$link);

    1956. 

  1956. 	$table='report_datamis';

    1957. 

  1957. 

    1958. 

  1958. 	$parser = new CHtmlPurifier();

    1959. 

  1959. 	$afterMonth = $parser->purify(intval(@$_REQUEST['month']));

    1960. 

  1960. 	$afterYear = $parser->purify(intval(@$_REQUEST['year']));

    1961. 

  1961. 	$afterDate = $parser->purify(intval(@$_REQUEST['date']));

    1962. 

  1962. 	$createTable = $parser->purify(intval(@$_REQUEST['create']));

    1963. 

  1963. 

    1964. 

  1964. 	if(isset($afterMonth) && !empty($afterMonth) && isset($afterYear) && !empty($afterYear) && isset($afterDate) && !empty($afterDate)){

    1965. 

  1965. 	$start =mktime(0, 0, 0, $afterMonth, 1,$afterYear);

    1966. 

  1966. 	$end = mktime(23, 59, 0, $afterMonth, date('t', $start),$afterYear);

    1967. 

  1967. 	} else {

    1968. 

  1968.     $start = strtotime('first day of this month', time());

    1969. 

  1969.     $end = strtotime('last day of this month', time());

    1970. 

  1970. 	}

    1971. 

  1971. 

    1972. 

  1972. if(isset($createTable) && !empty($createTable) && @$createTable==2){

    1973. 

  1973. 	$chktableExist = mysql_query('DROP TABLE IF EXISTS '.$table.' ') or die(mysql_error());

    1974. 

  1974. mysql_query("UPDATE `leadSourceMaster` SET leadSourceTitleReport=REPLACE(REPLACE(REPLACE(leadSourceTitle,' ',''),'-','_'),'.','')");

    1975. 

  1975. 

    1976. 

  1976. $resnews = mysql_query("SELECT group_concat(CONCAT(`leadSourceTitleReport`)) as res FROM `leadSourceMaster`");

    1977. 

  1977. $netres = mysql_fetch_array($resnews);

    1978. 

  1978. $strg = $netres['res'];

    1979. 

  1979. $replacedstr = str_replace(',',' varchar(50) ,',$strg)." varchar(50)";

    1980. 

  1980. 	

    1981. 

  1981. 	$createsql = "CREATE TABLE IF NOT EXISTS ".$table." (

    1982. 

  1982.   ".$replacedstr."

    1983. 

  1983. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

    1984. 

  1984. ";

    1985. 

  1985. 

    1986. 

  1986. mysql_query($createsql) or die(mysql_error());

    1987. 

  1987. mysql_query("ALTER TABLE  ".$table." ADD  `AgentName` VARCHAR( 150 ) NOT NULL FIRST ,

    1988. 

  1988. ADD  `Total` VARCHAR( 50 ) NOT NULL AFTER  `AgentName`");

    1989. 

  1989. 

    1990. 

  1990. }

    1991. 

  1991. 

    1992. 

  1992. $sourcecode = mysql_query("select distinct leadSourceTitleReport as leadSourceTitle,leadsourceCode from leadSourceMaster group by leadSourceTitleReport");

    1993. 

  1993.  $row=0; 

    1994. 

  1994.  $arraymerger = array();

    1995. 

  1995.     // $start = strtotime('first day of this month', time());

    1996. 

  1996.     //$end = strtotime('last day of this month', time());

    1997. 

  1997. $totalsourcewise = array();

    1998. 

  1998. $totalsourcewisesales = array();

    1999. 

  1999. $totalcontactable = array();

    2000. 

  2000. $conversionpercentage = array();

    2001. 

  2001. $salesIds = '7';

    2002. 

  2002. $contactableids = '7,8,9,37,38,11,12,13,14,15,16,17,22,23,24,25,26';

    2003. 

  2003. $noncontactableids = '39,40,27,28,29,30,33,42,43';

    2004. 

  2004. 

    2005. 

  2005.  while ($row = mysql_fetch_array($sourcecode)){

    2006. 

  2006.   	//echo $row['leadsourceCode'];

    2007. 

  2007. 	$arraymerger[$row['leadsourceCode']] = $row['leadSourceTitle'] ;

    2008. 

  2008. 	//echo 'select SUM(CASE WHEN ws_agentId = "'.@$row['leadsourceCode'].'" THEN 1 ELSE 0 END) AS total from lead'; die;

    2009. 

  2009. 	$quersum  = mysql_query('select SUM(CASE WHEN ws_agentId = "'.@$row['leadsourceCode'].'" and createdOn >= "'.@$start.'"  AND leadStatusId !=36 AND leadStatusId !=10  and createdOn <= "'.@$end.'" THEN 1 ELSE 0 END) AS total from lead'); 

    2010. 

  2010. 	$querressum = mysql_fetch_assoc($quersum);

    2011. 

  2011. 	$totalsourcewise[] = $querressum['total'];

    2012. 

  2012. 

    2013. 

  2013. 	$quersumsales  = mysql_query('select SUM(CASE WHEN ws_agentId = "'.@$row['leadsourceCode'].'" and createdOn >= "'.@$start.'"  AND leadStatusId !=36 AND leadStatusId !=10  and createdOn <= "'.@$end.'" and leadStatusId IN('.@$salesIds.') THEN 1 ELSE 0 END) AS total from lead'); 

    2014. 

  2014. 	$querressumsales = mysql_fetch_assoc($quersumsales);

    2015. 

  2015. 	$totalsourcewisesales[] = $querressumsales['total'];

    2016. 

  2016. 

    2017. 

  2017. $conversionpercentage[$querressumsales['total']] = $querressum['total'];

    2018. 

  2018. 	$quersumcontactable  = mysql_query('select SUM(CASE WHEN ws_agentId = "'.@$row['leadsourceCode'].'" and createdOn >= "'.@$start.'"  AND leadStatusId !=36 AND leadStatusId !=10  and createdOn <= "'.@$end.'" and leadStatusId IN('.@$contactableids.') THEN 1 ELSE 0 END) AS total from lead'); 

    2019. 

  2019. 	$querressumcontactable = mysql_fetch_assoc($quersumcontactable);

    2020. 

  2020. 	$totalcontactable[] = $querressumcontactable['total'];

    2021. 

  2021. 

    2022. 

  2022. 

    2023. 

  2023. 

    2024. 

  2024.  $row++; }

    2025. 

  2025.  $keys = (implode(",",array_keys($arraymerger)));

    2026. 

  2026.  $values = (implode(",",array_values($arraymerger)));

    2027. 

  2027. 

    2028. 

  2028. if(isset($createTable) && !empty($createTable) && @$createTable==2){

    2029. 

  2029. 

    2030. 

  2030. $q="replace into ".$table." (AgentName,".$values.") values ('AgentCode',".$keys.")"; 

    2031. 

  2031. mysql_query($q) or die(mysql_error());

    2032. 

  2032. }

    2033. 

  2033. 

    2034. 

  2034.  $maxDays=date('t');

    2035. 

  2035.  $totalData = 0;

    2036. 

  2036.  $totalDatasales = 0;

    2037. 

  2037.  $totalDatacontactable = 0;

    2038. 

  2038.  //for($y=1;$y<=$maxDays;$y++) {}

    2039. 

  2039.  $y=@$afterDate;

    2040. 

  2040.  

    2041. 

  2041. 	$date = $y.date('/m/Y');

    2042. 

  2042. 	$explodedDate = explode('/',$date);

    2043. 

  2043. 	$time = strtotime($explodedDate[0]."-".$explodedDate[1]."-".$explodedDate[2]);

    2044. 

  2044. 	$timePm = strtotime($explodedDate[0]."-".$explodedDate[1]."-".$explodedDate[2])+86399;

    2045. 

  2045. 	$totalcountcode = array();

    2046. 

  2046. 	$totalcountsales = array();

    2047. 

  2047. 	$totalcountcontact = array();

    2048. 

  2048. 	$totalcountcoderes=0;

    2049. 

  2049. 	foreach($arraymerger as $key=>$values1){

    2050. 

  2050. 	$quer  = mysql_query('select count(*) as total from lead where ws_agentId = "'.@$key.'" and createdOn >= "'.@$time.'"  AND leadStatusId !=36 AND leadStatusId !=10  and createdOn <= "'.@$timePm.'" '); 

    2051. 

  2051. 	$querres = mysql_fetch_assoc($quer);

    2052. 

  2052. 	$totalcountcode[] = $querres['total'];

    2053. 

  2053. 	$totalcountcoderes =$querres['total'];

    2054. 

  2054. 

    2055. 

  2055. 

    2056. 

  2056. 	$salesquery  = mysql_query('select SUM(CASE WHEN ws_agentId = "'.@$key.'" and createdOn >= "'.@$time.'"  AND leadStatusId !=36 AND leadStatusId !=10  and createdOn <= "'.@$timePm.'" and leadStatusId IN('.@$salesIds.') THEN 1 ELSE 0 END) as total from lead '); 

    2057. 

  2057. 	$salesqueryres = mysql_fetch_array($salesquery);

    2058. 

  2058. 	$totalcountsales[] = $salesqueryres['total'];

    2059. 

  2059. 

    2060. 

  2060. 	$contactquery  = mysql_query('select SUM(CASE WHEN ws_agentId = "'.@$key.'" and createdOn >= "'.@$time.'"  AND leadStatusId !=36 AND leadStatusId !=10  and createdOn <= "'.@$timePm.'" and leadStatusId IN('.@$contactableids.') THEN 1 ELSE 0 END) as total from lead '); 

    2061. 

  2061. 	$contactqueryres = mysql_fetch_array($contactquery);

    2062. 

  2062. 	$totalcountcontact[] = $contactqueryres['total'];

    2063. 

  2063. 

    2064. 

  2064. 	}

    2065. 

  2065. 	

    2066. 

  2066. 	 $totalsumres =  array_sum($totalcountcode);

    2067. 

  2067. 	 $totalsumresales =  array_sum($totalcountsales);

    2068. 

  2068. 	 $totalcountcontact =  array_sum($totalcountcontact);

    2069. 

  2069. 	 $implodedcountcode = implode(",",$totalcountcode);

    2070. 

  2070. 	$totalData = $totalData+intval($totalsumres);

    2071. 

  2071. 	$totalDatasales = $totalDatasales+intval($totalsumresales);

    2072. 

  2072. 	$totalDatacontactable = $totalDatacontactable+intval($totalcountcontact);

    2073. 

  2073. 	 intval($totalcountcoderes);

    2074. 

  2074. 

    2075. 

  2075. 	 $totalsumsales =  array_sum($totalcountsales);

    2076. 

  2076. //print_r($totalcountsales);

    2077. 

  2077. 	  $datequery = "insert into ".$table."(`AgentName`,`Total`,".$values.") values('".$y.date('/M/Y')."',".$totalsumres.",".$implodedcountcode.")";

    2078. 

  2078.       mysql_query($datequery);

    2079. 

  2079. 

    2080. 

  2080. 

    2081. 

  2081. 

    2082. 

  2082.  

    2083. 

  2083.  

    2084. 

  2084.  

    2085. 

  2085.  

    2086. 

  2086.  ////////////

    2087. 

  2087.  $contactper = $totalcontactable;

    2088. 

  2088.  $datacontper = $totalsourcewise;

    2089. 

  2089.  $datacontpertest = $totalsourcewisesales;

    2090. 

  2090.  $totalsourcewise = implode(",",$totalsourcewise); 

    2091. 

  2091.  $totalsourcewisesales = implode(",",$totalsourcewisesales); 

    2092. 

  2092.  $totalcontactable = implode(",",$totalcontactable); 

    2093. 

  2093.  if(@$totalDatacontactable > 0){

    2094. 

  2094.  	$contactperc = '100';

    2095. 

  2095.  } else {

    2096. 

  2096.  	$contactperc = '0';

    2097. 

  2097.  }

    2098. 

  2098. $contactpervalu =array();

    2099. 

  2099.  foreach($contactper as $key2=>$value2){

    2100. 

  2100.  	if($totalDatacontactable > 0){

    2101. 

  2101.   $contactpervalu[] = '"'.($value2*100)/$totalDatacontactable.'%"';

    2102. 

  2102. 	} else {

    2103. 

  2103.   $contactpervalu[] = "'0%'";

    2104. 

  2104. 	}

    2105. 

  2105. //  $contactpervalu[] = '"'.($value2*100)/$totalDatacontactable.'%"';

    2106. 

  2106.  }

    2107. 

  2107.  $contactpervalu = implode(",",$contactpervalu); 

    2108. 

  2108. 

    2109. 

  2109. 

    2110. 

  2110. 

    2111. 

  2111.  if(@$totalData > 0){

    2112. 

  2112.  	$Datacontributionprec = '100';

    2113. 

  2113.  } else {

    2114. 

  2114.  	$Datacontributionprec = '0';

    2115. 

  2115.  }

    2116. 

  2116. $datacontpervalu =array();

    2117. 

  2117.  foreach($datacontper as $key4=>$value4){

    2118. 

  2118.  if($totalData > 0){

    2119. 

  2119.   $datacontpervalu[] = '"'.($value4*100)/$totalData.'%"';

    2120. 

  2120.  } else {

    2121. 

  2121.   $datacontpervalu[] = "'0%'";

    2122. 

  2122.  }

    2123. 

  2123.  }

    2124. 

  2124.  $datacontpervalu = implode(",",$datacontpervalu); 

    2125. 

  2125. 

    2126. 

  2126. $conversionPerkeys = array_keys($conversionpercentage);

    2127. 

  2127. $conversionPervalue = array_values($conversionpercentage);

    2128. 

  2128. 

    2129. 

  2129. $conversionnetres = array();

    2130. 

  2130. for($r=0;$r<count($datacontper);$r++){

    2131. 

  2131. 

    2132. 

  2132. 

    2133. 

  2133.  if($datacontper[$r] > 0){

    2134. 

  2134.   $conversionnetres[$r] = ($datacontpertest[$r]/$datacontper[$r]);

    2135. 

  2135.  } else {

    2136. 

  2136.   $conversionnetres[$r] = "'0%'";;

    2137. 

  2137.  }

    2138. 

  2138. }

    2139. 

  2139.  $conversionpercentagevalu = implode(",",$conversionnetres); 

    2140. 

  2140.  if($totalData > 0){

    2141. 

  2141. $dataconversionrationtotal = ($totalDatasales/$totalData);

    2142. 

  2142.  } else {

    2143. 

  2143. $dataconversionrationtotal = 0;

    2144. 

  2144.  

    2145. 

  2145.  }

    2146. 

  2146. }

    2147. 

  2147. public function actiondatamiscreatetable(){

    2148. 

  2148. ini_set('max_input_time', 19200);

    2149. 

  2149. ini_set('max_execution_time', 19200);

    2150. 

  2150. 	$host = '10.216.6.59'; // MYSQL database host adress

    2151. 

  2151. 	$db = 'lms'; // MYSQL database name

    2152. 

  2152. 	$user = 'root'; // Mysql Datbase user

    2153. 

  2153. 	$pass = '0kwp6aNR'; // Mysql Datbase password	

    2154. 

  2154. 	$link=mysql_connect($host,$user,$pass);

    2155. 

  2155. 	mysql_select_db($db,$link);

    2156. 

  2156. 	$table='report_datamis';

    2157. 

  2157. 

    2158. 

  2158. 	$parser = new CHtmlPurifier();

    2159. 

  2159. 	$afterMonth = $parser->purify(intval(@$_REQUEST['month']));

    2160. 

  2160. 	$afterYear = $parser->purify(intval(@$_REQUEST['year']));

    2161. 

  2161. 	$afterDate = $parser->purify(intval(@$_REQUEST['date']));

    2162. 

  2162. 	$createTable = $parser->purify(intval(@$_REQUEST['create']));

    2163. 

  2163. 

    2164. 

  2164. 	if(isset($afterMonth) && !empty($afterMonth) && isset($afterYear) && !empty($afterYear) && isset($afterDate) && !empty($afterDate)){

    2165. 

  2165. 	$start =mktime(0, 0, 0, $afterMonth, 1,$afterYear);

    2166. 

  2166. 	$end = mktime(23, 59, 0, $afterMonth, date('t', $start),$afterYear);

    2167. 

  2167. 	} else {

    2168. 

  2168.     $start = strtotime('first day of this month', time());

    2169. 

  2169.     $end = strtotime('last day of this month', time());

    2170. 

  2170. 	}

    2171. 

  2171. 

    2172. 

  2172. 

    2173. 

  2173. 	$chktableExist = mysql_query('DROP TABLE IF EXISTS '.$table.' ') or die(mysql_error());

    2174. 

  2174. mysql_query("UPDATE `leadSourceMaster` SET leadSourceTitleReport=REPLACE(REPLACE(REPLACE(leadSourceTitle,' ',''),'-','_'),'.','')");

    2175. 

  2175. 

    2176. 

  2176. $resnews = mysql_query("SELECT group_concat(CONCAT(`leadSourceTitleReport`)) as res FROM `leadSourceMaster`");

    2177. 

  2177. $netres = mysql_fetch_array($resnews);

    2178. 

  2178. $strg = $netres['res'];

    2179. 

  2179. $replacedstr = str_replace(',',' varchar(50) ,',$strg)." varchar(50)";

    2180. 

  2180. 	

    2181. 

  2181. 	$createsql = "CREATE TABLE IF NOT EXISTS ".$table." (

    2182. 

  2182.   ".$replacedstr."

    2183. 

  2183. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

    2184. 

  2184. ";

    2185. 

  2185. 

    2186. 

  2186. mysql_query($createsql) or die(mysql_error());

    2187. 

  2187. mysql_query("ALTER TABLE  ".$table." ADD  `AgentName` VARCHAR( 150 ) NOT NULL FIRST ,

    2188. 

  2188. ADD  `Total` VARCHAR( 50 ) NOT NULL AFTER  `AgentName`");

    2189. 

  2189. 

    2190. 

  2190. 

    2191. 

  2191. $sourcecode = mysql_query("select distinct leadSourceTitleReport as leadSourceTitle,leadsourceCode from leadSourceMaster group by leadSourceTitleReport");

    2192. 

  2192.  $row=0; 

    2193. 

  2193.  $arraymerger = array();

    2194. 

  2194.     // $start = strtotime('first day of this month', time());

    2195. 

  2195.     //$end = strtotime('last day of this month', time());

    2196. 

  2196. $totalsourcewise = array();

    2197. 

  2197. $totalsourcewisesales = array();

    2198. 

  2198. $totalcontactable = array();

    2199. 

  2199. $conversionpercentage = array();

    2200. 

  2200. $salesIds = '7';

    2201. 

  2201. $contactableids = '7,8,9,37,38,11,12,13,14,15,16,17,22,23,24,25,26';

    2202. 

  2202. $noncontactableids = '39,40,27,28,29,30,33,42,43';

    2203. 

  2203. 

    2204. 

  2204.  while ($row = mysql_fetch_array($sourcecode)){

    2205. 

  2205.   	//echo $row['leadsourceCode'];

    2206. 

  2206. 	$arraymerger[$row['leadsourceCode']] = $row['leadSourceTitle'] ;

    2207. 

  2207. 	//echo 'select SUM(CASE WHEN ws_agentId = "'.@$row['leadsourceCode'].'" THEN 1 ELSE 0 END) AS total from lead'; die;

    2208. 

  2208. 	$quersum  = mysql_query('select SUM(CASE WHEN ws_agentId = "'.@$row['leadsourceCode'].'" and createdOn >= "'.@$start.'"  AND leadStatusId !=36 AND leadStatusId !=10  and createdOn <= "'.@$end.'" THEN 1 ELSE 0 END) AS total from lead'); 

    2209. 

  2209. 	$querressum = mysql_fetch_assoc($quersum);

    2210. 

  2210. 	$totalsourcewise[] = $querressum['total'];

    2211. 

  2211. 

    2212. 

  2212. 	$quersumsales  = mysql_query('select SUM(CASE WHEN ws_agentId = "'.@$row['leadsourceCode'].'" and createdOn >= "'.@$start.'"  AND leadStatusId !=36 AND leadStatusId !=10  and createdOn <= "'.@$end.'" and leadStatusId IN('.@$salesIds.') THEN 1 ELSE 0 END) AS total from lead'); 

    2213. 

  2213. 	$querressumsales = mysql_fetch_assoc($quersumsales);

    2214. 

  2214. 	$totalsourcewisesales[] = $querressumsales['total'];

    2215. 

  2215. 

    2216. 

  2216. $conversionpercentage[$querressumsales['total']] = $querressum['total'];

    2217. 

  2217. 	$quersumcontactable  = mysql_query('select SUM(CASE WHEN ws_agentId = "'.@$row['leadsourceCode'].'" and createdOn >= "'.@$start.'"  AND leadStatusId !=36 AND leadStatusId !=10  and createdOn <= "'.@$end.'" and leadStatusId IN('.@$contactableids.') THEN 1 ELSE 0 END) AS total from lead'); 

    2218. 

  2218. 	$querressumcontactable = mysql_fetch_assoc($quersumcontactable);

    2219. 

  2219. 	$totalcontactable[] = $querressumcontactable['total'];

    2220. 

  2220. 

    2221. 

  2221. 

    2222. 

  2222. 

    2223. 

  2223.  $row++; }

    2224. 

  2224.  $keys = (implode(",",array_keys($arraymerger)));

    2225. 

  2225.  $values = (implode(",",array_values($arraymerger)));

    2226. 

  2226. 

    2227. 

  2227. 

    2228. 

  2228. $q="replace into ".$table." (AgentName,".$values.") values ('AgentCode',".$keys.")"; 

    2229. 

  2229. mysql_query($q) or die(mysql_error());

    2230. 

  2230.   die;

    2231. 

  2231.  $maxDays=date('t');

    2232. 

  2232.  $totalData = 0;

    2233. 

  2233.  $totalDatasales = 0;

    2234. 

  2234.  $totalDatacontactable = 0;

    2235. 

  2235.  //for($y=1;$y<=$maxDays;$y++) {}

    2236. 

  2236.  $y=@$afterDate;

    2237. 

  2237.  

    2238. 

  2238. 	$date = $y.date('/m/Y');

    2239. 

  2239. 	$explodedDate = explode('/',$date);

    2240. 

  2240. 	$time = strtotime($explodedDate[0]."-".$explodedDate[1]."-".$explodedDate[2]);

    2241. 

  2241. 	$timePm = strtotime($explodedDate[0]."-".$explodedDate[1]."-".$explodedDate[2])+86399;

    2242. 

  2242. 	$totalcountcode = array();

    2243. 

  2243. 	$totalcountsales = array();

    2244. 

  2244. 	$totalcountcontact = array();

    2245. 

  2245. 	$totalcountcoderes=0;

    2246. 

  2246. 	foreach($arraymerger as $key=>$values1){

    2247. 

  2247. 	$quer  = mysql_query('select count(*) as total from lead where ws_agentId = "'.@$key.'" and createdOn >= "'.@$time.'"  AND leadStatusId !=36 AND leadStatusId !=10  and createdOn <= "'.@$timePm.'"'); 

    2248. 

  2248. 	$querres = mysql_fetch_assoc($quer);

    2249. 

  2249. 	$totalcountcode[] = $querres['total'];

    2250. 

  2250. 	$totalcountcoderes =$querres['total'];

    2251. 

  2251. 

    2252. 

  2252. 

    2253. 

  2253. 	$salesquery  = mysql_query('select SUM(CASE WHEN ws_agentId = "'.@$key.'" and createdOn >= "'.@$time.'"  AND leadStatusId !=36 AND leadStatusId !=10  and createdOn <= "'.@$timePm.'" and leadStatusId IN('.@$salesIds.') THEN 1 ELSE 0 END) as total from lead '); 

    2254. 

  2254. 	$salesqueryres = mysql_fetch_array($salesquery);

    2255. 

  2255. 	$totalcountsales[] = $salesqueryres['total'];

    2256. 

  2256. 

    2257. 

  2257. 	$contactquery  = mysql_query('select SUM(CASE WHEN ws_agentId = "'.@$key.'" and createdOn >= "'.@$time.'"  AND leadStatusId !=36 AND leadStatusId !=10  and createdOn <= "'.@$timePm.'" and leadStatusId IN('.@$contactableids.') THEN 1 ELSE 0 END) as total from lead '); 

    2258. 

  2258. 	$contactqueryres = mysql_fetch_array($contactquery);

    2259. 

  2259. 	$totalcountcontact[] = $contactqueryres['total'];

    2260. 

  2260. 

    2261. 

  2261. 	}

    2262. 

  2262. 	

    2263. 

  2263. 	 $totalsumres =  array_sum($totalcountcode);

    2264. 

  2264. 	 $totalsumresales =  array_sum($totalcountsales);

    2265. 

  2265. 	 $totalcountcontact =  array_sum($totalcountcontact);

    2266. 

  2266. 	 $implodedcountcode = implode(",",$totalcountcode);

    2267. 

  2267. 	$totalData = $totalData+intval($totalsumres);

    2268. 

  2268. 	$totalDatasales = $totalDatasales+intval($totalsumresales);

    2269. 

  2269. 	$totalDatacontactable = $totalDatacontactable+intval($totalcountcontact);

    2270. 

  2270. 	 intval($totalcountcoderes);

    2271. 

  2271. 

    2272. 

  2272. 	 $totalsumsales =  array_sum($totalcountsales);

    2273. 

  2273. //print_r($totalcountsales);

    2274. 

  2274. 	  $datequery = "insert into ".$table."(`AgentName`,`Total`,".$values.") values('".$y.date('/M/Y')."',".$totalsumres.",".$implodedcountcode.")";

    2275. 

  2275.       mysql_query($datequery);

    2276. 

  2276. 

    2277. 

  2277. 

    2278. 

  2278. 

    2279. 

  2279.  

    2280. 

  2280.  

    2281. 

  2281.  

    2282. 

  2282.  

    2283. 

  2283.  ////////////

    2284. 

  2284.  $contactper = $totalcontactable;

    2285. 

  2285.  $datacontper = $totalsourcewise;

    2286. 

  2286.  $datacontpertest = $totalsourcewisesales;

    2287. 

  2287.  $totalsourcewise = implode(",",$totalsourcewise); 

    2288. 

  2288.  $totalsourcewisesales = implode(",",$totalsourcewisesales); 

    2289. 

  2289.  $totalcontactable = implode(",",$totalcontactable); 

    2290. 

  2290.  if(@$totalDatacontactable > 0){

    2291. 

  2291.  	$contactperc = '100';

    2292. 

  2292.  } else {

    2293. 

  2293.  	$contactperc = '0';

    2294. 

  2294.  }

    2295. 

  2295. $contactpervalu =array();

    2296. 

  2296.  foreach($contactper as $key2=>$value2){

    2297. 

  2297.  	if($totalDatacontactable > 0){

    2298. 

  2298.   $contactpervalu[] = '"'.($value2*100)/$totalDatacontactable.'%"';

    2299. 

  2299. 	} else {

    2300. 

  2300.   $contactpervalu[] = "'0%'";

    2301. 

  2301. 	}

    2302. 

  2302. //  $contactpervalu[] = '"'.($value2*100)/$totalDatacontactable.'%"';

    2303. 

  2303.  }

    2304. 

  2304.  $contactpervalu = implode(",",$contactpervalu); 

    2305. 

  2305. 

    2306. 

  2306. 

    2307. 

  2307. 

    2308. 

  2308.  if(@$totalData > 0){

    2309. 

  2309.  	$Datacontributionprec = '100';

    2310. 

  2310.  } else {

    2311. 

  2311.  	$Datacontributionprec = '0';

    2312. 

  2312.  }

    2313. 

  2313. $datacontpervalu =array();

    2314. 

  2314.  foreach($datacontper as $key4=>$value4){

    2315. 

  2315.  if($totalData > 0){

    2316. 

  2316.   $datacontpervalu[] = '"'.($value4*100)/$totalData.'%"';

    2317. 

  2317.  } else {

    2318. 

  2318.   $datacontpervalu[] = "'0%'";

    2319. 

  2319.  }

    2320. 

  2320.  }

    2321. 

  2321.  $datacontpervalu = implode(",",$datacontpervalu); 

    2322. 

  2322. 

    2323. 

  2323. $conversionPerkeys = array_keys($conversionpercentage);

    2324. 

  2324. $conversionPervalue = array_values($conversionpercentage);

    2325. 

  2325. 

    2326. 

  2326. $conversionnetres = array();

    2327. 

  2327. for($r=0;$r<count($datacontper);$r++){

    2328. 

  2328. 

    2329. 

  2329. 

    2330. 

  2330.  if($datacontper[$r] > 0){

    2331. 

  2331.   $conversionnetres[$r] = ($datacontpertest[$r]/$datacontper[$r]);

    2332. 

  2332.  } else {

    2333. 

  2333.   $conversionnetres[$r] = "'0%'";;

    2334. 

  2334.  }

    2335. 

  2335. }

    2336. 

  2336.  $conversionpercentagevalu = implode(",",$conversionnetres); 

    2337. 

  2337.  if($totalData > 0){

    2338. 

  2338. $dataconversionrationtotal = ($totalDatasales/$totalData);

    2339. 

  2339.  } else {

    2340. 

  2340. $dataconversionrationtotal = 0;

    2341. 

  2341.  

    2342. 

  2342.  }

    2343. 

  2343. }

    2344. 

  2344. public function actiondatamistotaldata(){

    2345. 

  2345. ini_set('max_input_time', 19200);

    2346. 

  2346. ini_set('max_execution_time', 19200);

    2347. 

  2347. 	$host = '10.216.6.59'; // MYSQL database host adress

    2348. 

  2348. 	$db = 'lms'; // MYSQL database name

    2349. 

  2349. 	$user = 'root'; // Mysql Datbase user

    2350. 

  2350. 	$pass = '0kwp6aNR'; // Mysql Datbase password	

    2351. 

  2351. 	$link=mysql_connect($host,$user,$pass);

    2352. 

  2352. 	mysql_select_db($db,$link);

    2353. 

  2353. 	$table='report_datamis';

    2354. 

  2354. 

    2355. 

  2355. 	$parser = new CHtmlPurifier();

    2356. 

  2356. 	$afterMonth = $parser->purify(intval(@$_REQUEST['month']));

    2357. 

  2357. 	$afterYear = $parser->purify(intval(@$_REQUEST['year']));

    2358. 

  2358. 	$afterDate = $parser->purify(intval(@$_REQUEST['date']));

    2359. 

  2359. 

    2360. 

  2360. 

    2361. 

  2361. 	if(isset($afterMonth) && !empty($afterMonth) && isset($afterYear) && !empty($afterYear)){

    2362. 

  2362. 	$start =mktime(0, 0, 0, $afterMonth, 1,$afterYear);

    2363. 

  2363. 	$end = mktime(23, 59, 0, $afterMonth, date('t', $start),$afterYear);

    2364. 

  2364. 	} else {

    2365. 

  2365.     $start = strtotime('first day of this month', time());

    2366. 

  2366.     $end = strtotime('last day of this month', time());

    2367. 

  2367. 	}

    2368. 

  2368. 

    2369. 

  2369. $sourcecode = mysql_query("select distinct leadSourceTitleReport as leadSourceTitle,leadsourceCode from leadSourceMaster group by leadSourceTitleReport");

    2370. 

  2370.  $row=0; 

    2371. 

  2371.  $arraymerger = array();

    2372. 

  2372.     // $start = strtotime('first day of this month', time());

    2373. 

  2373.     //$end = strtotime('last day of this month', time());

    2374. 

  2374. $totalsourcewise = array();

    2375. 

  2375. $totalsourcewisesales = array();

    2376. 

  2376. $totalcontactable = array();

    2377. 

  2377. $conversionpercentage = array();

    2378. 

  2378. $salesIds = '7';

    2379. 

  2379. $contactableids = '7,8,9,37,38,11,12,13,14,15,16,17,22,23,24,25,26';

    2380. 

  2380. $noncontactableids = '39,40,27,28,29,30,33,42,43';

    2381. 

  2381. 

    2382. 

  2382.  while ($row = mysql_fetch_array($sourcecode)){

    2383. 

  2383.   	//echo $row['leadsourceCode'];

    2384. 

  2384. 	$arraymerger[$row['leadsourceCode']] = $row['leadSourceTitle'] ;

    2385. 

  2385. 	//echo 'select SUM(CASE WHEN ws_agentId = "'.@$row['leadsourceCode'].'" THEN 1 ELSE 0 END) AS total from lead'; die;

    2386. 

  2386. 	$quersum  = mysql_query('select SUM(CASE WHEN ws_agentId = "'.@$row['leadsourceCode'].'" and createdOn >= "'.@$start.'"  AND leadStatusId !=36 AND leadStatusId !=10  and createdOn <= "'.@$end.'" THEN 1 ELSE 0 END) AS total from lead'); 

    2387. 

  2387. 	$querressum = mysql_fetch_assoc($quersum);

    2388. 

  2388. 	$totalsourcewise[] = $querressum['total'];

    2389. 

  2389. 

    2390. 

  2390. 	$quersumsales  = mysql_query('select SUM(CASE WHEN ws_agentId = "'.@$row['leadsourceCode'].'" and createdOn >= "'.@$start.'"  AND leadStatusId !=36 AND leadStatusId !=10  and createdOn <= "'.@$end.'" and leadStatusId IN('.@$salesIds.') THEN 1 ELSE 0 END) AS total from lead'); 

    2391. 

  2391. 	$querressumsales = mysql_fetch_assoc($quersumsales);

    2392. 

  2392. 	$totalsourcewisesales[] = $querressumsales['total'];

    2393. 

  2393. 

    2394. 

  2394. $conversionpercentage[$querressumsales['total']] = $querressum['total'];

    2395. 

  2395. 	$quersumcontactable  = mysql_query('select SUM(CASE WHEN ws_agentId = "'.@$row['leadsourceCode'].'" and createdOn >= "'.@$start.'"  AND leadStatusId !=36 AND leadStatusId !=10  and createdOn <= "'.@$end.'" and leadStatusId IN('.@$contactableids.') THEN 1 ELSE 0 END) AS total from lead'); 

    2396. 

  2396. 	$querressumcontactable = mysql_fetch_assoc($quersumcontactable);

    2397. 

  2397. 	$totalcontactable[] = $querressumcontactable['total'];

    2398. 

  2398. 

    2399. 

  2399. 

    2400. 

  2400. 

    2401. 

  2401.  $row++; }

    2402. 

  2402.  $keys = (implode(",",array_keys($arraymerger)));

    2403. 

  2403.  $values = (implode(",",array_values($arraymerger)));

    2404. 

  2404. 

    2405. 

  2405. 

    2406. 

  2406.  $maxDays=@$afterDate;

    2407. 

  2407.  $totalData = 0;

    2408. 

  2408.  $totalDatasales = 0;

    2409. 

  2409.  $totalDatacontactable = 0;

    2410. 

  2410.  for($y=1;$y<=$maxDays;$y++) {

    2411. 

  2411. 	$date = $y.date('/m/Y');

    2412. 

  2412. 	$explodedDate = explode('/',$date);

    2413. 

  2413. 	$time = strtotime($explodedDate[0]."-".$explodedDate[1]."-".$explodedDate[2]);

    2414. 

  2414. 	$timePm = strtotime($explodedDate[0]."-".$explodedDate[1]."-".$explodedDate[2])+86399;

    2415. 

  2415. 	$totalcountcode = array();

    2416. 

  2416. 	$totalcountsales = array();

    2417. 

  2417. 	$totalcountcontact = array();

    2418. 

  2418. 	$totalcountcoderes=0;

    2419. 

  2419. 	foreach($arraymerger as $key=>$values1){

    2420. 

  2420. 	$quer  = mysql_query('select count(*) as total from lead where ws_agentId = "'.@$key.'" and createdOn >= "'.@$time.'"  AND leadStatusId !=36 AND leadStatusId !=10  and createdOn <= "'.@$timePm.'"'); 

    2421. 

  2421. 	$querres = mysql_fetch_assoc($quer);

    2422. 

  2422. 	$totalcountcode[] = $querres['total'];

    2423. 

  2423. 	$totalcountcoderes =$querres['total'];

    2424. 

  2424. 

    2425. 

  2425. 

    2426. 

  2426. 	$salesquery  = mysql_query('select SUM(CASE WHEN ws_agentId = "'.@$key.'" and createdOn >= "'.@$time.'"  AND leadStatusId !=36 AND leadStatusId !=10  and createdOn <= "'.@$timePm.'" and leadStatusId IN('.@$salesIds.') THEN 1 ELSE 0 END) as total from lead '); 

    2427. 

  2427. 	$salesqueryres = mysql_fetch_array($salesquery);

    2428. 

  2428. 	$totalcountsales[] = $salesqueryres['total'];

    2429. 

  2429. 

    2430. 

  2430. 	$contactquery  = mysql_query('select SUM(CASE WHEN ws_agentId = "'.@$key.'" and createdOn >= "'.@$time.'"  AND leadStatusId !=36 AND leadStatusId !=10  and createdOn <= "'.@$timePm.'" and leadStatusId IN('.@$contactableids.') THEN 1 ELSE 0 END) as total from lead '); 

    2431. 

  2431. 	$contactqueryres = mysql_fetch_array($contactquery);

    2432. 

  2432. 	$totalcountcontact[] = $contactqueryres['total'];

    2433. 

  2433. 

    2434. 

  2434. 	}

    2435. 

  2435. 	

    2436. 

  2436. 	 $totalsumres =  array_sum($totalcountcode);

    2437. 

  2437. 	 $totalsumresales =  array_sum($totalcountsales);

    2438. 

  2438. 	 $totalcountcontact =  array_sum($totalcountcontact);

    2439. 

  2439. 	 $implodedcountcode = implode(",",$totalcountcode);

    2440. 

  2440. 	$totalData = $totalData+intval($totalsumres);

    2441. 

  2441. 	$totalDatasales = $totalDatasales+intval($totalsumresales);

    2442. 

  2442. 	$totalDatacontactable = $totalDatacontactable+intval($totalcountcontact);

    2443. 

  2443. 	 intval($totalcountcoderes);

    2444. 

  2444. 

    2445. 

  2445. 	 $totalsumsales =  array_sum($totalcountsales);

    2446. 

  2446. //print_r($totalcountsales);

    2447. 

  2447. 	  //$datequery = "insert into ".$table."(`AgentName`,`Total`,".$values.") values('".$y.date('/M/Y')."',".$totalsumres.",".$implodedcountcode.")";

    2448. 

  2448.   //mysql_query($datequery);

    2449. 

  2449. 

    2450. 

  2450. 

    2451. 

  2451. 

    2452. 

  2452.  

    2453. 

  2453.  }

    2454. 

  2454.  $contactper = $totalcontactable;

    2455. 

  2455.  $datacontper = $totalsourcewise;

    2456. 

  2456.  $datacontpertest = $totalsourcewisesales;

    2457. 

  2457.  $totalsourcewise = implode(",",$totalsourcewise); 

    2458. 

  2458.  $totalsourcewisesales = implode(",",$totalsourcewisesales); 

    2459. 

  2459.  $totalcontactable = implode(",",$totalcontactable); 

    2460. 

  2460.  if(@$totalDatacontactable > 0){

    2461. 

  2461.  	$contactperc = ($totalDatasales*100)/$totalData;

    2462. 

  2462.  } else {

    2463. 

  2463.  	$contactperc = '0';

    2464. 

  2464.  }

    2465. 

  2465. $contactpervalu =array();

    2466. 

  2466.  foreach($contactper as $key2=>$value2){

    2467. 

  2467.  	if($totalDatacontactable > 0){

    2468. 

  2468.   $contactpervalu[] = '"'.($value2*100)/$totalDatacontactable.'%"';

    2469. 

  2469. 	} else {

    2470. 

  2470.   $contactpervalu[] = "'0%'";

    2471. 

  2471. 	}

    2472. 

  2472. //  $contactpervalu[] = '"'.($value2*100)/$totalDatacontactable.'%"';

    2473. 

  2473.  }

    2474. 

  2474.  $contactpervalu = implode(",",$contactpervalu); 

    2475. 

  2475. 

    2476. 

  2476. 

    2477. 

  2477. 

    2478. 

  2478.  if(@$totalData > 0){

    2479. 

  2479.  	$Datacontributionprec = '100';

    2480. 

  2480.  } else {

    2481. 

  2481.  	$Datacontributionprec = '0';

    2482. 

  2482.  }

    2483. 

  2483. $datacontpervalu =array();

    2484. 

  2484.  foreach($datacontper as $key4=>$value4){

    2485. 

  2485.  if($totalData > 0){

    2486. 

  2486.   $datacontpervalu[] = '"'.($value4*100)/$totalData.'%"';

    2487. 

  2487.  } else {

    2488. 

  2488.   $datacontpervalu[] = "'0%'";

    2489. 

  2489.  }

    2490. 

  2490.  }

    2491. 

  2491.  $datacontpervalu = implode(",",$datacontpervalu); 

    2492. 

  2492. 

    2493. 

  2493. $conversionPerkeys = array_keys($conversionpercentage);

    2494. 

  2494. $conversionPervalue = array_values($conversionpercentage);

    2495. 

  2495. 

    2496. 

  2496. $conversionnetres = array();

    2497. 

  2497. for($r=0;$r<count($datacontper);$r++){

    2498. 

  2498. 

    2499. 

  2499. 

    2500. 

  2500.  if($datacontper[$r] > 0){

    2501. 

  2501.   $conversionnetres[$r] = ($datacontpertest[$r]/$datacontper[$r]);

    2502. 

  2502.  } else {

    2503. 

  2503.   $conversionnetres[$r] = "'0%'";;

    2504. 

  2504.  }

    2505. 

  2505. }

    2506. 

  2506.  $conversionpercentagevalu = implode(",",$conversionnetres); 

    2507. 

  2507.  if($totalData > 0){

    2508. 

  2508. $dataconversionrationtotal = ($totalDatasales/$totalData);

    2509. 

  2509.  } else {

    2510. 

  2510. $dataconversionrationtotal = 0;

    2511. 

  2511.  

    2512. 

  2512.  }

    2513. 

  2513. //echo "insert into ".$table."(`AgentName`,`Total`,".@$values.") values('Conatctable%','".@$contactperc."%',".@$contactpervalu.")"; die;

    2514. 

  2514.   mysql_query("insert into ".$table."(`AgentName`,`Total`,".@$values.") values('TotalData','".@$totalData."',".@$totalsourcewise.")");

    2515. 

  2515.   mysql_query("insert into ".$table."(`AgentName`,`Total`,".@$values.") values('Sale','".@$totalDatasales."',".@$totalsourcewisesales.")");

    2516. 

  2516.   mysql_query("insert into ".$table."(`AgentName`,`Total`,".@$values.") values('Conatctabledata','".@$totalDatacontactable."',".@$totalcontactable.")");

    2517. 

  2517.   mysql_query("insert into ".$table."(`AgentName`,`Total`,".@$values.") values('Conatctable%','".@$contactperc."%',".@$contactpervalu.")");

    2518. 

  2518.   mysql_query("insert into ".$table."(`AgentName`,`Total`,".@$values.") values('Datacontribution%','".@$Datacontributionprec."%',".@$datacontpervalu.")");

    2519. 

  2519.   mysql_query("insert into ".$table."(`AgentName`,`Total`,".@$values.") values('Conversion%','".@$dataconversionrationtotal."%',".@$conversionpercentagevalu.")");

    2520. 

  2520. //	$tableres ="create table IF NOT EXISTS ".$table." as select  leadSourceCode,leadSourceTitle from leadSourceMaster";

    2521. 

  2521. 	//$esx = mysql_query($tableres) or die(mysql_error());

    2522. 

  2522. }

    2523. 

  2523. public function actiondatamisdownloadreport(){

    2524. 

  2524. ini_set('max_input_time', 19200);

    2525. 

  2525. ini_set('max_execution_time', 19200);

    2526. 

  2526. 	$host = '10.216.6.59'; // MYSQL database host adress

    2527. 

  2527. 	$db = 'lms'; // MYSQL database name

    2528. 

  2528. 	$user = 'root'; // Mysql Datbase user

    2529. 

  2529. 	$pass = '0kwp6aNR'; // Mysql Datbase password	

    2530. 

  2530. 	$link=mysql_connect($host,$user,$pass);

    2531. 

  2531. 	mysql_select_db($db,$link);

    2532. 

  2532. 	$table='report_datamis';

    2533. 

  2533. 	if(isset($afterMonth) && !empty($afterMonth) && isset($afterYear) && !empty($afterYear)){

    2534. 

  2534. 	$start =mktime(0, 0, 0, $afterMonth, 1,$afterYear);

    2535. 

  2535. 	$end = mktime(23, 59, 0, $afterMonth, date('t', $start),$afterYear);

    2536. 

  2536. 	} else {

    2537. 

  2537.     $start = strtotime('first day of this month', time());

    2538. 

  2538.     $end = strtotime('last day of this month', time());

    2539. 

  2539. 	}

    2540. 

  2540. 

    2541. 

  2541. $this->actiondatamis_download($start);}

    2542. 

  2542. 

    2543. 

  2543. 

    2544. 

  2544. 

    2545. 

  2545. }

    2546. 

  2546. ?>
    2547.