public function importcsv()
{
if (!Permission::model()->hasGlobalPermission('superadmin', 'read') && !Permission::model()->hasGlobalPermission('projects', 'read')) {
Yii::app()->setFlashMessage($clang->gT("You do not have sufficient rights to access page."), 'error');
$this->getController()->redirect(array("admin/index"));
}
$clang = Yii::app()->lang;
$aData['row'] = 0;
$aData['imageurl'] = Yii::app()->getConfig("adminimageurl");
$rectify_type = (int) Yii::app()->request->getPost("trueup_option");
$project_id = (int) $_GET['project_id'];
$aData['project_id'] = $project_id;
$aData['file'] = $_FILES['import_file'];
$currentdatetime = date('Y-m-d H:i:s');
$file = $_FILES['import_file'];
if ($file['size'] > 0) {
$sql = 'CREATE TABLE IF NOT EXISTS {{tmp_import}}
(INDEX tmp_import_index (panellist_redirect_id, redirect_status_id,created_datetime))
( SELECT panellist_redirect_id,redirect_status_id,created_datetime FROM {{panellist_redirects}})';
Yii::app()->db->createCommand($sql)->query();
$sql = 'truncate {{tmp_import}} ';
Yii::app()->db->createCommand($sql)->query();
$csvfile = fopen($file['tmp_name'], 'r');
$theData = fgets($csvfile);
$i = 0;
while (!feof($csvfile)) {
$csv_data[] = fgets($csvfile, 1024);
$csv_array = explode(",", $csv_data[$i]);
if (count($csv_array) > 1) {
$sql = "INSERT INTO {{tmp_import}} (panellist_redirect_id,redirect_status_id,created_datetime)\n VALUES('{$csv_array['0']}','{$csv_array['1']}','{$currentdatetime}')";
Yii::app()->db->createCommand($sql)->query();
}
$i++;
}
fclose($csvfile);
// validation start
$i = 0;
$chkfinaltrupexist = Rectify::model()->findAll(array('condition' => 'project_id = ' . $project_id . ' AND rectify_type = 1'));
if (count($chkfinaltrupexist) > 0) {
$i = 1;
Yii::app()->setFlashMessage($clang->gT("The Final RECTIFY is done on this project So you can not RECTIFY this project."));
$this->getController()->redirect(array("admin/project/rectify/project_id/{$project_id}"));
}
//24/06/2014 Add By Hari
//$ValSql = "SELECT pr.panellist_redirect_id FROM {{panellist_redirects}} pr
// left outer join {{project_master}} pm on pr.project_id=pm.project_id
// WHERE (pm.project_id='$project_id' OR pm.parent_project_id = '$project_id') AND pr.rectify_id IS NULL AND pr.redirect_status_id='" . getGlobalSetting('redirect_status_completed') . "'";
$ValSqlResult = Project::model()->GetProjectCompletedNotRectify($project_id);
//Yii::app()->db->createCommand($ValSql)->query()->readAll();
$Data = '';
$Exit = 0;
foreach ($ValSqlResult as $ky => $val) {
$tmpSql = "select * from {{tmp_import}} WHERE panellist_redirect_id ='" . $val['panellist_redirect_id'] . "'";
$tempResult = Yii::app()->db->createCommand($tmpSql)->query()->readAll();
if (count($tempResult) <= 0) {
$Exit = 1;
$Data .= $val['panellist_redirect_id'] . ",";
}
}
$_SESSION['Data'] = $Data;
if ($Exit == 1) {
$sql = 'truncate {{tmp_import}} ';
Yii::app()->db->createCommand($sql)->query();
Yii::app()->setFlashMessage($clang->gT("There are certain redirects which are completed but yet not rectified. So can not import."), 'error');
$this->getController()->redirect(array("admin/project/sa/rectify/project_id/" . $project_id . "/NotRectify/true"));
}
//24/06/2014 End
$trerror = '';
if ($i == 0) {
$pl = "select * from {{tmp_import}}";
$result_temp = Yii::app()->db->createCommand($pl)->query()->readAll();
$odd = false;
foreach ($result_temp as $ky => $val) {
$Error = '';
//check duplicate panellist redirect id exist in table
$sql = "SELECT COUNT(*) AS cnt FROM {{tmp_import}} WHERE panellist_redirect_id ='" . $val['panellist_redirect_id'] . "'";
$result = Yii::app()->db->createCommand($sql)->queryRow();
if ($result['cnt'] >= 2) {
$i = 1;
$Error .= 'Duplicate RedirectID found in CSV' . ' && ';
}
//chk panellist exist or not
$sql = "SELECT COUNT(*) AS cnt FROM {{panellist_redirects}} WHERE panellist_redirect_id ='" . $val['panellist_redirect_id'] . "'";
$result = Yii::app()->db->createCommand($sql)->queryRow();
if ($result['cnt'] == 0) {
$i = 1;
$Error .= 'RedirectID not found in database' . ' && ';
}
//chk trueupexist or not;
$sql = "SELECT COUNT(*) AS cnt FROM {{panellist_redirects}} WHERE panellist_redirect_id ='" . $val['panellist_redirect_id'] . "' AND rectify_id != ''";
$result = Yii::app()->db->createCommand($sql)->queryRow();
if ($result['cnt'] > 0) {
$i = 1;
$Error .= 'RedirectID Already trueup' . ' && ';
}
//chk status is correct or not
$sql_status = "select status_id,status_name from {{project_status_master}} where status_for = 'r'";
$result_project = Yii::app()->db->createCommand($sql_status)->query()->readAll();
$status = array();
foreach ($result_project as $vale => $st) {
$status[] = $st['status_id'];
}
//print_r($status);
if (!in_array($val['redirect_status_id'], $status)) {
$i = 1;
$Error .= 'Incorrect Status Found in CSV' . ' && ';
}
$sql_project = "SELECT status_id,status_name FROM {{project_status_master}} WHERE status_id = '" . $val['redirect_status_id'] . "'";
$result_project = Yii::app()->db->createCommand($sql_project)->queryRow();
if ($odd) {
$cls = 'class = "odd"';
} else {
$cls = 'class = "even"';
}
$trerror .= '<tr ' . $cls . '>
<td>' . $val['panellist_redirect_id'] . '</td>
<td nowrap="nowrap">' . $val['redirect_status_id'] . ' - ' . $result_project['status_name'] . '</td>
<td>' . rtrim($Error, " && ") . '</td>
</tr>';
$odd = !$odd;
}
}
$aData['trerror'] = $trerror;
$st_completed = getGlobalSetting('redirect_status_completed');
$st_d = getGlobalSetting('redirect_status_disqual');
$st_qf = getGlobalSetting('redirect_status_qf');
$st_r = getGlobalSetting('redirect_status_redirected');
$st_f = getGlobalSetting('redirect_status_rej_fail');
$st_inc = getGlobalSetting('redirect_status_rej_incosist');
$st_q = getGlobalSetting('redirect_status_rej_quality');
$st_s = getGlobalSetting('redirect_status_rej_speed');
$st_p = getGlobalSetting('redirect_status_rej_poor');
if ($i == 0) {
// import data
$result = array();
$sql1 = "SELECT rectify_no FROM {{rectify_redirects}} WHERE project_id = '{$project_id}'";
$result = Yii::app()->db->createCommand($sql1)->queryRow();
$rectify_no = $result['rectify_no'] + 1;
$currentdate = date('Y-m-d');
$rectify_id = Rectify::model()->insertrectify($project_id, $rectify_type, $rectify_no, $currentdate);
// Here is the problem
foreach ($result_temp as $key => $value) {
$query = Rectify::model()->updatePanellistRedirect($value['panellist_redirect_id'], $value['redirect_status_id'], $rectify_id);
$sql = "select * from {{panellist_redirects}} where panellist_redirect_id = '" . $value['panellist_redirect_id'] . "'";
$resultpr = Yii::app()->db->createCommand($sql)->queryRow();
switch ($value['redirect_status_id']) {
case $st_completed:
$status_pp = 'C';
break;
case $st_d:
$status_pp = 'D';
break;
case $st_qf:
$status_pp = 'Q';
break;
case $st_r:
$status_pp = 'R';
break;
case $st_f:
$status_pp = 'E1';
break;
case $st_inc:
$status_pp = 'E2';
break;
case $st_q:
$status_pp = 'E3';
break;
case $st_s:
$status_pp = 'E4';
break;
case $st_p:
$status_pp = 'E5';
break;
default:
break;
}
// or here
if ($resultpr['vendor_id'] == getGlobalSetting('Own_Panel')) {
$update_pp = "update {{panellist_project}} set \n status = '{$status_pp}' \n where panellist_id = '" . $resultpr['panellist_id'] . "' and project_id ='" . $resultpr['project_id'] . "' ";
$result = Yii::app()->db->createCommand($update_pp)->query();
$select_pp = "select IFNULL(points,0) as points from {{panellist_project}} where panellist_id = '" . $resultpr['panellist_id'] . "' and project_id = '" . $resultpr['project_id'] . "'";
$qry_pp = Yii::app()->db->createCommand($select_pp)->queryRow();
//echo 'status'.$status_pp;
if ($status_pp == 'C') {
$update_pm = "update {{panel_list_master}} set \n earn_points = earn_points + " . $qry_pp['points'] . ",\n balance_points = balance_points + " . $qry_pp['points'] . "\n where panel_list_id = '" . $resultpr['panellist_id'] . "' ";
$result = Yii::app()->db->createCommand($update_pm)->query();
}
}
}
//end for loop
$sqlProject = "SELECT project_id FROM {{project_master}} WHERE project_id = '{$project_id}' OR parent_project_id = '{$project_id}'";
$qurProject = Yii::app()->db->createCommand($sqlProject)->query()->readAll();
$st_error = $st_f . ',' . $st_inc . ',' . $st_q . ',' . $st_s . ',' . $st_p;
if (count($qurProject) > 0) {
foreach ($qurProject as $key => $value) {
$temp_prjid = $value['project_id'];
// Updating status of the project based on trueup
$numQFull = getProjectStatusCount_Trueup($temp_prjid, $st_qf);
$numDisqualified = getProjectStatusCount_Trueup($temp_prjid, $st_d);
$numCompleted = getProjectStatusCount_Trueup($temp_prjid, $st_completed);
$numErrored = getProjectStatusCount_Trueup($temp_prjid, $st_error);
$numQFull = (int) $numQFull;
$numDisqualified = (int) $numDisqualified;
$numCompleted = (int) $numCompleted;
$numErrored = (int) $numErrored;
$sql = "update {{project_master}}\n set\n trueup = '" . $currentdatetime . "',\n total_quota_full = '" . $numQFull . "',\n total_disqualify = '" . $numDisqualified . "',\n total_completed = '" . $numCompleted . "',\n total_errors = '" . $numErrored . "'\n where project_id = '" . $temp_prjid . "'";
$result = Yii::app()->db->createCommand($sql)->query();
$numQFull = 0;
$numDisqualified = 0;
$numCompleted = 0;
$numErrored = 0;
}
}
$sqlVendor = "SELECT vr.vendor_project_id,vr.project_id FROM {{project_master_vendors}} vr,{{project_master}} pm\n WHERE pm.project_id = vr.project_id AND vr.project_id IN ({$project_id})\n UNION ALL\n SELECT vr.vendor_project_id,vr.project_id FROM {{project_master_vendors}} vr,{{project_master}} pm\n WHERE pm.project_id = vr.project_id AND pm.parent_project_id IN ({$project_id}) ";
$getVstatus = Yii::app()->db->createCommand($sqlVendor)->query()->readAll();
if (count($getVstatus) > 0) {
foreach ($getVstatus as $key => $value) {
$vendorid = $value['vendor_project_id'];
$temp_prjid = $value['project_id'];
$numQFull = getVStatusCount_Trueup($temp_prjid, $vendorid, $st_qf);
$numDisqualified = getVStatusCount_Trueup($temp_prjid, $vendorid, $st_d);
$numCompleted = getVStatusCount_Trueup($temp_prjid, $vendorid, $st_completed);
$numErrored = getVStatusCount_Trueup($temp_prjid, $vendorid, $st_error);
$updvendor = "update {{project_master_vendors}} set\n total_quota_full = '" . $numQFull . "',\n total_disqualified = '" . $numDisqualified . "',\n total_completed = '" . $numCompleted . "',\n total_errors = '" . $numErrored . "'\n where project_id = '" . $temp_prjid . "' and vendor_project_id = '" . $vendorid . "'";
$result = Yii::app()->db->createCommand($updvendor)->query();
$numQFull = 0;
$numDisqualified = 0;
$numCompleted = 0;
$numErrored = 0;
}
}
//add project_master update and project_vendor_master update for total_completed, total_*
Yii::app()->setFlashMessage($clang->gT("File data successfully imported to database."));
$sql = 'truncate {{tmp_import}} ';
Yii::app()->db->createCommand($sql)->query();
//$this->getController()->redirect(array("admin/project/sa/modifyproject/project_id/$project_id/action/modifyproject"));
}
} else {
Yii::app()->setFlashMessage($clang->gT("No data found the selected file !!!"));
}
$this->_renderWrappedTemplate('projects', 'view_rectify_imports', $aData);
}
<?php
$this->widget("application.extensions.Brain.BrainPopupContentWidget", array("popup_box_id" => "box_view", "popup_link_id" => "link_view", "container_id" => "", "popup_on_load" => "false", "popup_title" => "Sample Of File Format", "uid" => $row['project_id'], "height" => "300px;", "width" => "300px;"));
?>
<a id="link_view" title="Rectify Project Redirects Sample File format" class="limebutton"><?php
$clang->eT("Download Sample File Format");
?>
</a>
</td>
</tr>
<tr>
<td align="left" valign="top" width="50%">
<hr style="color:white;border:1px solid white"/>
<fieldset>
<legend>List of Trueup</legend>
<?php
$trueup = Rectify::model()->findAll(array('condition' => "project_id ='" . $row['project_id'] . "'"));
if (count($trueup) > 0) {
foreach ($trueup as $key => $value) {
$trueuplist[$value['rectify_id']] = $value['rectify_no'] . ' - ' . $value['rectify_date'];
//24/06/2014 Remove By Hari
//24/06/2014 Add By Hari
if ($value['rectify_type'] == '1') {
$trueuplist[$value['rectify_id']] = $value['rectify_no'] . ' - ' . $value['rectify_date'] . ' - ' . 'Final Rectify';
} else {
$trueuplist[$value['rectify_id']] = $value['rectify_no'] . ' - ' . $value['rectify_date'] . ' - ' . 'Temp Rectify';
}
//24/06/2014 End
}
echo CHtml::listBox('c_id', "" . 0 . "", $trueuplist, array("style" => "height:200px;width:100%;"));
} else {
echo 'No TrueUp Exist';
