Guriddo » Topic: Not exporting all columns data

Not exporting all columns data

Guriddo
custom function, export, php
Not exporting all columns data

Tagged: custom function, export, php

This topic has 3 replies, 2 voices, and was last updated 11 years, 3 months ago by tony.

Creator

Topic

January 10, 2015 at 6:29 pm #115223

overlucked

Participant

Hello,
I’m modifying some columns with custom data and really don’t know is it the best way to edit a column data or not but this is just the way I can do it, and when I try to export data to csv it’s not showing the custom data, please someone help.
Thanks in advance.

require_once '../jqSuite/jq-config.php';
// include the jqGrid Class
require_once ABSPATH."php/jqGrid.php";
// include the driver class
require_once ABSPATH."php/jqGridPdo.php";
// Connection to the server
$conn = new PDO(DB_DSN,DB_USER,DB_PASSWORD);
// Tell the db that we use utf-8
$conn->query("SET NAMES utf8");

$otchet_id = (int) $_REQUEST['otcheta_id'];
if ($otchet_id != 0)
    $_SESSION['otchet_id'] = (int) $_REQUEST['otcheta_id'];

#$qbi->p($_REQUEST);
#$qbi->p($_SESSION);

$zk_name = $_REQUEST['zk_name'];
if ($zk_name != NULL)
    $_SESSION['zk_name'] = $_REQUEST['zk_name'];

$zk_name = $_REQUEST['broker_name'];
if ($zk_name != NULL)
    $_SESSION['broker_name'] = $_REQUEST['broker_name'];

	$rowid = jqGridUtils::Strip($_REQUEST["otcheta_id"]); 
	#echo $rowid;
// Create the jqGrid instance
$grid = new jqGridRender($conn);
// Write the SQL Query
$grid->SelectCommand = '
SELECT zapis_id,
DATE(lk.timestamp) as timestamp,
zastrahovka_type,
zk.zk_name,
b.broker_name,
polica_number,
kvitancia_nomer,
s.stiker_number,
note,
"- - -" as dobavak_number,
"- - -" as dobavak_name,
"- - -" as zelena_karta_number,
stoinost,
(SELECT sv.procent FROM stikeri_vnoski as sv WHERE UNIX_TIMESTAMP(p.data_skliuchvane) >= from_date AND sv.zk_id = lk.zk_id AND sv.zastrahovka_type_id = lk.zastrahovka_type_id AND sv.broker_id = lk.broker_id AND sv.old = 0 ORDER BY id desc LIMIT 1) as 
stiker_vnoska,
v.vnoska_gf,
v.vnoska_value as premiq,
v.vnoska_danak,
(SELECT kv.procent FROM komisiona_vnoski as kv WHERE UNIX_TIMESTAMP(p.data_skliuchvane) >= from_date AND kv.zk_id = lk.zk_id AND kv.zastrahovka_type_id = lk.zastrahovka_type_id AND kv.broker_id = lk.broker_id AND kv.old = 0 ORDER BY id desc LIMIT 1) as 
komisiona_vnoska,
p.polica_total_gf,
kvitancia_danak
      
    FROM kasa_obshta lk

    LEFT JOIN polici            AS p ON p.polica_id     = lk.polica_id
    LEFT JOIN vnoski as v ON v.polica_id = lk.polica_id
    LEFT JOIN brokeri           AS b ON b.broker_id     = p.broker_id
    LEFT JOIN kvitancii                     AS k ON k.kvitancia_id  = v.kvitancia_id
    LEFT JOIN stikeri           AS s ON s.stiker_id     = v.stiker_id
    LEFT JOIN zk                     ON zk.zk_id    = lk.zk_id
    LEFT JOIN zastrahovki_types AS z ON z.zastrahovka_type_id   = lk.zastrahovka_type_id
      
    WHERE
		lk.note = v.vnoska_number
			AND
        lk.dobavak_id = 0
			AND
        v.vnoska_is_paid = 1
			AND
        lk.is_otchetena = 2
	AND
		lk.otchet_id = ' . $rowid . '

UNION ALL

SELECT zapis_id,
lk.timestamp,
zastrahovka_type,
zk.zk_name,
b.broker_name,
polica_number,
kvitancia_nomer,
s.stiker_number,
note,
dobavak_number,
dobavak_name,
"- - -" as zelena_karta_number,
stoinost,
(SELECT sv.procent FROM stikeri_vnoski as sv WHERE UNIX_TIMESTAMP(<code>lk</code>.<code>timestamp</code>) >= from_date AND sv.zk_id = p.zk_id AND sv.zastrahovka_type_id = p.zastrahovka_type_id AND sv.broker_id = p.broker_id AND sv.old = 0 ORDER BY id desc LIMIT 1) as 
stiker_vnoska,
d.dobavak_value,
d.dobavak_danak,
d.dobavak_danak,
(SELECT kv.procent FROM komisiona_vnoski as kv WHERE UNIX_TIMESTAMP(<code>lk</code>.<code>timestamp</code>) >= from_date AND kv.zk_id = p.zk_id AND kv.zastrahovka_type_id = p.zastrahovka_type_id AND kv.broker_id = p.broker_id AND kv.old = 0 ORDER BY id desc LIMIT 1) as 
komisiona_vnoska,
p.polica_total_gf,
kvitancia_danak

    FROM kasa_obshta lk

    LEFT JOIN polici            AS p ON p.polica_id     = lk.polica_id
    LEFT JOIN dobavaci as d ON d.polica_id = lk.polica_id
    LEFT JOIN brokeri           AS b ON b.broker_id     = p.broker_id
    LEFT JOIN kvitancii                     AS k ON k.kvitancia_id  = d.kvitancia_id
    LEFT JOIN stikeri           AS s ON s.stiker_id     = d.stiker_id
    LEFT JOIN zk                     ON zk.zk_id    = lk.zk_id
    LEFT JOIN zastrahovki_types AS z ON z.zastrahovka_type_id   = lk.zastrahovka_type_id
       
    
    WHERE
            lk.dobavak_id = d.dobavak_id
        AND
                lk.dobavak_id != 0
        AND
    
        lk.is_otchetena = 2

		AND
		lk.otchet_id = ' . $rowid . '
UNION ALL
SELECT 
zapis_id, 
DATE(lk.timestamp) as timestamp,
zastrahovka_type,
zk.zk_name,
b.broker_name,
polica_number,
kvitancia_nomer,
"" as stiker_number,
note,
"- - -" as dobavak_number,
"- - -" as dobavak_name,
zelena_karta_number,
stoinost,
(SELECT sv.procent FROM stikeri_vnoski as sv WHERE UNIX_TIMESTAMP(lk.timestamp) >= from_date AND sv.zk_id = p.zk_id AND sv.zastrahovka_type_id = p.zastrahovka_type_id AND sv.broker_id = p.broker_id AND sv.old = 0 ORDER BY id desc LIMIT 1) as 
stiker_vnoska,
zkart.zelena_karta_value,
zkart.zelena_karta_danak,
zkart.zelena_karta_danak,
(SELECT kv.procent FROM komisiona_vnoski as kv WHERE UNIX_TIMESTAMP(lk.timestamp) >= from_date AND kv.zk_id = p.zk_id AND kv.zastrahovka_type_id = p.zastrahovka_type_id AND kv.broker_id = p.broker_id AND kv.old = 0 ORDER BY id desc LIMIT 1) as 
komisiona_vnoska,
p.polica_total_gf,
kvitancia_danak
    FROM kasa_obshta lk

    LEFT JOIN polici            AS p ON p.polica_id     = lk.polica_id
    LEFT JOIN zeleni_karti as zkart ON zkart.polica_id = lk.polica_id
    LEFT JOIN brokeri           AS b ON b.broker_id     = p.broker_id
    LEFT JOIN kvitancii                     AS k ON k.kvitancia_id  = zkart.kvitancia_id
    LEFT JOIN zk                     ON zk.zk_id    = lk.zk_id
    LEFT JOIN zastrahovki_types AS z ON z.zastrahovka_type_id   = lk.zastrahovka_type_id

    WHERE
            lk.zelena_karta_id = zkart.zelena_karta_id
        AND
                lk.zelena_karta_id != 0
        AND

        lk.is_otchetena = 2
		AND
		lk.otchet_id = ' . $rowid . '

								';
								
								
								#WHERE DATE(lk.timestamp) = DATE(NOW())
// set the ouput format to json
$grid->dataType = 'json';
// Let the grid create the model from SQL query
$grid->setColModel(null,array(&$rowid));
// Set the url from where we obtain the data
$grid->setUrl('includes/ppp2.php');
// Set alternate background using altRows property
$grid->setGridOptions(array(
    "rowList" => array(15, 50, 100, 1000),
    "hoverrows" => true,
    "rowNum" => 15,
    "sortname" => "zapis_id",
    "sortorder" => "desc",
    "height" => 345,
    "width" => 1000,
    "rownumWidth" => 35,
     "caption" => '&nbsp;ОТЧЕТ №' . $_SESSION['otchet_id'] . ' // ' . $_SESSION['broker_name'] . ' към ' . $_SESSION['zk_name'],
	 "postData"=>array("otcheta_id"=>$rowid),
	 
    ));

// Change some property of the field(s)
$grid->setColProperty("zapis_id", array('hidden' => true));
$grid->setColProperty("polica_total_gf", array('label' => 'Комисиона', 'align' => 'center', 'width' => 100));
$grid->setColProperty("vnoska_gf", array('label' => 'Г. фонд', 'align' => 'center', 'width' => 100));
$grid->setColProperty("stoinost", array('label' => 'Премия', 'align' => 'center', 'width' => 100));
$grid->setColProperty("komisiona_vnoska", array('label' => '%', 'align' => 'center', 'width' => 100));
$grid->setColProperty("stiker_vnoska", array('label' => 'Стикер лв.', 'align' => 'center', 'width' => 100));
$grid->setColProperty("premiq", array('label' => "Пр-я без ГФ", 'align' => 'center', 'width' => 100));
$grid->setColProperty("vnoska_danak", array('label' => 'Данък 2%', 'align' => 'center', 'width' => 100));
$grid->setColProperty("timestamp", array('label' => 'Дата', 'align' => 'center', 'width' => 100));
$grid->setColProperty("zastrahovka_type", array('label' => 'Вид', 'align' => 'center', 'width' => 80));
$grid->setColProperty("zk_name", array('label' => 'З.К.', 'align' => 'center', 'width' => 80));
$grid->setColProperty("broker_name", array('label' => 'Брокер.', 'align' => 'center', 'width' => 80));
$grid->setColProperty("polica_number", array('label' => 'Полица №', 'align' => 'center', 'width' => 100));
$grid->setColProperty("kvitancia_nomer", array('label' => 'Квитанция №', 'align' => 'center', 'width' => 120));
$grid->setColProperty("dobavak_name", array('label' => 'Добавък име', 'align' => 'center', 'width' => 120));
$grid->setColProperty("dobavak_number", array('label' => 'Добавък №', 'align' => 'center', 'width' => 120));
$grid->setColProperty("zelena_karta_number", array('label' => 'Сертиф. ЗК', 'align' => 'center', 'width' => 120));
$grid->setColProperty("stiker_number", array('label' => 'Стикер №', 'align' => 'center', 'width' => 100));
$grid->setColProperty("note", array('label' => 'Вноска', 'width' => 80, 'align' => 'center'));
$grid->setColProperty("kvitancia_danak", array('label' => 'За отчет', 'align' => 'center', 'width' => 100));

$grid->setColProperty("timestamp", array(
    "formatter" => "date",
    "formatoptions" => array("srcformat" => "Y-m-d H:i:s", "newformat" => "d.m.Y")
        )
);

$grid->toolbarfilter = true; 
$grid->setFilterOptions(array("stringResult"=>true,"autosearch"=>true,"searchOnEnter"=>false,"defaultSearch"=>"cn","ignoreCase"=>true)); 
$grid->setSelect("zk_name", "SELECT DISTINCT zk_name, zk_name AS zk_name FROM zk ORDER BY 2", false, false, true, array(""=>"-- Избери --"));
$grid->setSelect("broker_name", "SELECT DISTINCT broker_name, broker_name AS broker_name FROM brokeri ORDER BY 2", false, false, true, array(""=>"-- Избери --"));
$grid->setSelect("zastrahovka_type", "SELECT DISTINCT zastrahovka_type, zastrahovka_type AS zastrahovka_type FROM zastrahovki_types ORDER BY 2", false, false, true, array(""=>"-- Избери --"));

$grid->toolbarfilter = true; 
$grid->setFilterOptions(array("stringResult"=>true,"autosearch"=>true,"searchOnEnter"=>false,"defaultSearch"=>"cn","ignoreCase"=>true)); 

// Add a summary property to the Freight Column
#$grid->setColProperty("stoinost", array('label'=>'Салдо','align'=>'right', 'width' => 80,"summaryType"=>"sum", summaryTpl=>"ОБЩО {0}", "formatter"=>"currency"));

#$summaryrows = array("stoinost"=>array("stoinost"=>"SUM"));
 
#$grid->callGridMethod('#otchet', 'footerData', array("set",array("note"=>"За отчет:"), false)); 

$grid->navigator = true;
$grid->setNavOptions('navigator', array("excel" => true, "add" => false, "edit" => false, "del" => false, "view" => false));

$grid->exportfile = 'Отчет.xls';

//Define the custom function
$grid->customFunc = "CalculationUpdate";

$grid->debug = true; 

// Enjoy
$grid->renderGrid('#info_za_otchet', '#pager', true, null, array(&$rowid), true, true);
$conn = null;

/// here is the function
function CalculationUpdate($gdata, $conn)
{
// gdata->rows contain the grid data rows
   foreach($gdata->rows as $row ) 
   {
   // format using "," as decimal separator
    /*  if($row->zastrahovka_type < 3 ) {
      //color in red if the value is less than 1
         $row->zastrahovka_type = "<font color='red'>".number_format($row->zastrahovka_type, 2, ',', ' ')."</font>";
      } else {
         $row->zastrahovka_type = number_format($row->zastrahovka_type, 2, ',', ' ');
      }
	 */
		if(is_numeric($row->stoinost)) // Премия
			$row->stoinost = number_format($row->stoinost, 2).' лв.';
			
		$row->stiker_vnoska = (!empty($row->stiker_vnoska) AND $row->stiker_vnoska != 0) ? number_format($row->stiker_vnoska, 2).' лв.' : ''; // Стикер лв.

		if(!empty($row->komisiona_vnoska)) {
			$row->komisiona_vnoska = $row->komisiona_vnoska.'%';
		}
		else {
			$row->komisiona_vnoska = '';
		}
		
		$row->vnoska_gf = number_format($row->vnoska_gf, 2).' лв.';
		$row->premiq = number_format($row->premiq, 2).' лв.';
		$row->vnoska_danak = number_format($row->vnoska_danak, 2).' лв.';
		
		if(empty($row->komisiona_vnoska)) {
			$sume = number_format($row->premiq, 2).' лв.';
		}
		else {
			$kom = $row->komisiona_vnoska;
			$prem = $row->premiq;
			$sume = $kom * $prem * 0.01;
		}
		
	   $komi = number_format($sume, 2).' лв.';
	  
	   $premiq = number_format($row->premiq, 2).' лв.';
	   $vnoska_gf = number_format($row->vnoska_gf, 2).' лв.';
	  
		$kom = $row->komisiona_vnoska ;
		$prem = $row->premiq;
		$sum = $kom * $prem * 0.01;
		$row->polica_total_gf = number_format($sum, 2).' лв.';
	  
		if(($row->note == 'Зелена карта') || ($row->note == 'Добавък')) {
			$zk_za_otchet = ($row->premiq + $row->vnoska_gf) - $komi;
			$row->kvitancia_danak = number_format($zk_za_otchet, 2).' лв.';
		}
		
		else {
			if($row->stiker_vnoska == null) {
				$premiqta = $row->premiq;
				$stikerche = 0;
			}
			else {
				$stikerche = $row->stiker_vnoska;
				$premiika = $row->premiq;
				$premiqta = $premiq + $stikerche;
			}
        
            $danyche = $row->premiq * 0.02;

        if($row->vnoska_gf != 0) {
            $premiq_1 = $row->premiq + $row->vnoska_gf;
        }
        else {
            $premiq_1 = $row->premiq;
        }

			$suma = $danyche + $premiq_1;
			$za_otchet = $row->stoinost - $sum;
			$row->kvitancia_danak = number_format($za_otchet, 2).' лв.';
		}
		
		
		$row->stiker_vnoska = (($row->zelena_karta_number == '- - -') && ($row->dobavak_number == '- - -') && (!empty($row->stiker_vnoska))) ? $row->stiker_vnoska : '';
		#$row->komisiona_vnoska = (($row->zelena_karta_number == '- - -') && ($row->dobavak_number == '- - -') && (!empty($row->stiker_vnoska))) ? $row->komisiona_vnoska : '';
		

		#print_r($row);
   }
   // return the modified
   return $gdata;
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

require_once '../jqSuite/jq-config.php';

// include the jqGrid Class

require_once ABSPATH."php/jqGrid.php";

// include the driver class

require_once ABSPATH."php/jqGridPdo.php";

// Connection to the server

$conn = new PDO(DB_DSN,DB_USER,DB_PASSWORD);

// Tell the db that we use utf-8

$conn->query("SET NAMES utf8");

$otchet_id = (int) $_REQUEST['otcheta_id'];

if ($otchet_id != 0)

$_SESSION['otchet_id'] = (int) $_REQUEST['otcheta_id'];

#$qbi->p($_REQUEST);

#$qbi->p($_SESSION);

$zk_name = $_REQUEST['zk_name'];

if ($zk_name != NULL)

$_SESSION['zk_name'] = $_REQUEST['zk_name'];

$zk_name = $_REQUEST['broker_name'];

if ($zk_name != NULL)

$_SESSION['broker_name'] = $_REQUEST['broker_name'];

$rowid = jqGridUtils::Strip($_REQUEST["otcheta_id"]);

#echo $rowid;

// Create the jqGrid instance

$grid = new jqGridRender($conn);

// Write the SQL Query

$grid->SelectCommand = '

SELECT zapis_id,

DATE(lk.timestamp) as timestamp,

zastrahovka_type,

zk.zk_name,

b.broker_name,

polica_number,

kvitancia_nomer,

s.stiker_number,

note,

"- - -" as dobavak_number,

"- - -" as dobavak_name,

"- - -" as zelena_karta_number,

stoinost,

(SELECT sv.procent FROM stikeri_vnoski as sv WHERE UNIX_TIMESTAMP(p.data_skliuchvane) >= from_date AND sv.zk_id = lk.zk_id AND sv.zastrahovka_type_id = lk.zastrahovka_type_id AND sv.broker_id = lk.broker_id AND sv.old = 0 ORDER BY id desc LIMIT 1) as

stiker_vnoska,

v.vnoska_gf,

v.vnoska_value as premiq,

v.vnoska_danak,

(SELECT kv.procent FROM komisiona_vnoski as kv WHERE UNIX_TIMESTAMP(p.data_skliuchvane) >= from_date AND kv.zk_id = lk.zk_id AND kv.zastrahovka_type_id = lk.zastrahovka_type_id AND kv.broker_id = lk.broker_id AND kv.old = 0 ORDER BY id desc LIMIT 1) as

komisiona_vnoska,

p.polica_total_gf,

kvitancia_danak

FROM kasa_obshta lk

LEFT JOIN polici AS p ON p.polica_id = lk.polica_id

LEFT JOIN vnoski as v ON v.polica_id = lk.polica_id

LEFT JOIN brokeri AS b ON b.broker_id = p.broker_id

LEFT JOIN kvitancii AS k ON k.kvitancia_id = v.kvitancia_id

LEFT JOIN stikeri AS s ON s.stiker_id = v.stiker_id

LEFT JOIN zk ON zk.zk_id = lk.zk_id

LEFT JOIN zastrahovki_types AS z ON z.zastrahovka_type_id = lk.zastrahovka_type_id

WHERE

lk.note = v.vnoska_number

AND

lk.dobavak_id = 0

AND

v.vnoska_is_paid = 1

AND

lk.is_otchetena = 2

AND

lk.otchet_id = ' . $rowid . '

UNION ALL

SELECT zapis_id,

lk.timestamp,

zastrahovka_type,

zk.zk_name,

b.broker_name,

polica_number,

kvitancia_nomer,

s.stiker_number,

note,

dobavak_number,

dobavak_name,

"- - -" as zelena_karta_number,

stoinost,

(SELECT sv.procent FROM stikeri_vnoski as sv WHERE UNIX_TIMESTAMP(<code>lk</code>.<code>timestamp</code>) >= from_date AND sv.zk_id = p.zk_id AND sv.zastrahovka_type_id = p.zastrahovka_type_id AND sv.broker_id = p.broker_id AND sv.old = 0 ORDER BY id desc LIMIT 1) as

stiker_vnoska,

d.dobavak_value,

d.dobavak_danak,

(SELECT kv.procent FROM komisiona_vnoski as kv WHERE UNIX_TIMESTAMP(<code>lk</code>.<code>timestamp</code>) >= from_date AND kv.zk_id = p.zk_id AND kv.zastrahovka_type_id = p.zastrahovka_type_id AND kv.broker_id = p.broker_id AND kv.old = 0 ORDER BY id desc LIMIT 1) as

komisiona_vnoska,

p.polica_total_gf,

kvitancia_danak

FROM kasa_obshta lk

LEFT JOIN polici AS p ON p.polica_id = lk.polica_id

LEFT JOIN dobavaci as d ON d.polica_id = lk.polica_id

LEFT JOIN brokeri AS b ON b.broker_id = p.broker_id

LEFT JOIN kvitancii AS k ON k.kvitancia_id = d.kvitancia_id

LEFT JOIN stikeri AS s ON s.stiker_id = d.stiker_id

LEFT JOIN zk ON zk.zk_id = lk.zk_id

LEFT JOIN zastrahovki_types AS z ON z.zastrahovka_type_id = lk.zastrahovka_type_id

WHERE

lk.dobavak_id = d.dobavak_id

AND

lk.dobavak_id != 0

AND

lk.is_otchetena = 2

AND

lk.otchet_id = ' . $rowid . '

UNION ALL

SELECT

zapis_id,

DATE(lk.timestamp) as timestamp,

zastrahovka_type,

zk.zk_name,

b.broker_name,

polica_number,

kvitancia_nomer,

"" as stiker_number,

note,

"- - -" as dobavak_number,

"- - -" as dobavak_name,

zelena_karta_number,

stoinost,

(SELECT sv.procent FROM stikeri_vnoski as sv WHERE UNIX_TIMESTAMP(lk.timestamp) >= from_date AND sv.zk_id = p.zk_id AND sv.zastrahovka_type_id = p.zastrahovka_type_id AND sv.broker_id = p.broker_id AND sv.old = 0 ORDER BY id desc LIMIT 1) as

stiker_vnoska,

zkart.zelena_karta_value,

zkart.zelena_karta_danak,

(SELECT kv.procent FROM komisiona_vnoski as kv WHERE UNIX_TIMESTAMP(lk.timestamp) >= from_date AND kv.zk_id = p.zk_id AND kv.zastrahovka_type_id = p.zastrahovka_type_id AND kv.broker_id = p.broker_id AND kv.old = 0 ORDER BY id desc LIMIT 1) as

komisiona_vnoska,

p.polica_total_gf,

kvitancia_danak

FROM kasa_obshta lk

LEFT JOIN polici AS p ON p.polica_id = lk.polica_id

LEFT JOIN zeleni_karti as zkart ON zkart.polica_id = lk.polica_id

LEFT JOIN brokeri AS b ON b.broker_id = p.broker_id

LEFT JOIN kvitancii AS k ON k.kvitancia_id = zkart.kvitancia_id

LEFT JOIN zk ON zk.zk_id = lk.zk_id

LEFT JOIN zastrahovki_types AS z ON z.zastrahovka_type_id = lk.zastrahovka_type_id

WHERE

lk.zelena_karta_id = zkart.zelena_karta_id

AND

lk.zelena_karta_id != 0

AND

lk.is_otchetena = 2

AND

lk.otchet_id = ' . $rowid . '

#WHERE DATE(lk.timestamp) = DATE(NOW())

// set the ouput format to json

$grid->dataType = 'json';

// Let the grid create the model from SQL query

$grid->setColModel(null,array(&$rowid));

// Set the url from where we obtain the data

$grid->setUrl('includes/ppp2.php');

// Set alternate background using altRows property

$grid->setGridOptions(array(

"rowList" => array(15, 50, 100, 1000),

"hoverrows" => true,

"rowNum" => 15,

"sortname" => "zapis_id",

"sortorder" => "desc",

"height" => 345,

"width" => 1000,

"rownumWidth" => 35,

"caption" => ' ОТЧЕТ №' . $_SESSION['otchet_id'] . ' // ' . $_SESSION['broker_name'] . ' към ' . $_SESSION['zk_name'],

"postData"=>array("otcheta_id"=>$rowid),

));

// Change some property of the field(s)

$grid->setColProperty("zapis_id", array('hidden' => true));

$grid->setColProperty("polica_total_gf", array('label' => 'Комисиона', 'align' => 'center', 'width' => 100));

$grid->setColProperty("vnoska_gf", array('label' => 'Г. фонд', 'align' => 'center', 'width' => 100));

$grid->setColProperty("stoinost", array('label' => 'Премия', 'align' => 'center', 'width' => 100));

$grid->setColProperty("komisiona_vnoska", array('label' => '%', 'align' => 'center', 'width' => 100));

$grid->setColProperty("stiker_vnoska", array('label' => 'Стикер лв.', 'align' => 'center', 'width' => 100));

$grid->setColProperty("premiq", array('label' => "Пр-я без ГФ", 'align' => 'center', 'width' => 100));

$grid->setColProperty("vnoska_danak", array('label' => 'Данък 2%', 'align' => 'center', 'width' => 100));

$grid->setColProperty("timestamp", array('label' => 'Дата', 'align' => 'center', 'width' => 100));

$grid->setColProperty("zastrahovka_type", array('label' => 'Вид', 'align' => 'center', 'width' => 80));

$grid->setColProperty("zk_name", array('label' => 'З.К.', 'align' => 'center', 'width' => 80));

$grid->setColProperty("broker_name", array('label' => 'Брокер.', 'align' => 'center', 'width' => 80));

$grid->setColProperty("polica_number", array('label' => 'Полица №', 'align' => 'center', 'width' => 100));

$grid->setColProperty("kvitancia_nomer", array('label' => 'Квитанция №', 'align' => 'center', 'width' => 120));

$grid->setColProperty("dobavak_name", array('label' => 'Добавък име', 'align' => 'center', 'width' => 120));

$grid->setColProperty("dobavak_number", array('label' => 'Добавък №', 'align' => 'center', 'width' => 120));

$grid->setColProperty("zelena_karta_number", array('label' => 'Сертиф. ЗК', 'align' => 'center', 'width' => 120));

$grid->setColProperty("stiker_number", array('label' => 'Стикер №', 'align' => 'center', 'width' => 100));

$grid->setColProperty("note", array('label' => 'Вноска', 'width' => 80, 'align' => 'center'));

$grid->setColProperty("kvitancia_danak", array('label' => 'За отчет', 'align' => 'center', 'width' => 100));

$grid->setColProperty("timestamp", array(

"formatter" => "date",

"formatoptions" => array("srcformat" => "Y-m-d H:i:s", "newformat" => "d.m.Y")

)

);

$grid->toolbarfilter = true;

$grid->setFilterOptions(array("stringResult"=>true,"autosearch"=>true,"searchOnEnter"=>false,"defaultSearch"=>"cn","ignoreCase"=>true));

$grid->setSelect("zk_name", "SELECT DISTINCT zk_name, zk_name AS zk_name FROM zk ORDER BY 2", false, false, true, array(""=>"-- Избери --"));

$grid->setSelect("broker_name", "SELECT DISTINCT broker_name, broker_name AS broker_name FROM brokeri ORDER BY 2", false, false, true, array(""=>"-- Избери --"));

$grid->setSelect("zastrahovka_type", "SELECT DISTINCT zastrahovka_type, zastrahovka_type AS zastrahovka_type FROM zastrahovki_types ORDER BY 2", false, false, true, array(""=>"-- Избери --"));

$grid->toolbarfilter = true;

$grid->setFilterOptions(array("stringResult"=>true,"autosearch"=>true,"searchOnEnter"=>false,"defaultSearch"=>"cn","ignoreCase"=>true));

// Add a summary property to the Freight Column

#$grid->setColProperty("stoinost", array('label'=>'Салдо','align'=>'right', 'width' => 80,"summaryType"=>"sum", summaryTpl=>"ОБЩО {0}", "formatter"=>"currency"));

#$summaryrows = array("stoinost"=>array("stoinost"=>"SUM"));

#$grid->callGridMethod('#otchet', 'footerData', array("set",array("note"=>"За отчет:"), false));

$grid->navigator = true;

$grid->setNavOptions('navigator', array("excel" => true, "add" => false, "edit" => false, "del" => false, "view" => false));

$grid->exportfile = 'Отчет.xls';

//Define the custom function

$grid->customFunc = "CalculationUpdate";

$grid->debug = true;

// Enjoy

$grid->renderGrid('#info_za_otchet', '#pager', true, null, array(&$rowid), true, true);

$conn = null;

/// here is the function

function CalculationUpdate($gdata, $conn)

{

// gdata->rows contain the grid data rows

foreach($gdata->rows as $row )

{

// format using "," as decimal separator

/* if($row->zastrahovka_type < 3 ) {

//color in red if the value is less than 1

$row->zastrahovka_type = "<font color='red'>".number_format($row->zastrahovka_type, 2, ',', ' ')."</font>";

} else {

$row->zastrahovka_type = number_format($row->zastrahovka_type, 2, ',', ' ');

}

if(is_numeric($row->stoinost)) // Премия

$row->stoinost = number_format($row->stoinost, 2).' лв.';

$row->stiker_vnoska = (!empty($row->stiker_vnoska) AND $row->stiker_vnoska != 0) ? number_format($row->stiker_vnoska, 2).' лв.' : ''; // Стикер лв.

if(!empty($row->komisiona_vnoska)) {

$row->komisiona_vnoska = $row->komisiona_vnoska.'%';

}

else {

$row->komisiona_vnoska = '';

}

$row->vnoska_gf = number_format($row->vnoska_gf, 2).' лв.';

$row->premiq = number_format($row->premiq, 2).' лв.';

$row->vnoska_danak = number_format($row->vnoska_danak, 2).' лв.';

if(empty($row->komisiona_vnoska)) {

$sume = number_format($row->premiq, 2).' лв.';

}

else {

$kom = $row->komisiona_vnoska;

$prem = $row->premiq;

$sume = $kom * $prem * 0.01;

}

$komi = number_format($sume, 2).' лв.';

$premiq = number_format($row->premiq, 2).' лв.';

$vnoska_gf = number_format($row->vnoska_gf, 2).' лв.';

$kom = $row->komisiona_vnoska ;

$prem = $row->premiq;

$sum = $kom * $prem * 0.01;

$row->polica_total_gf = number_format($sum, 2).' лв.';

if(($row->note == 'Зелена карта') || ($row->note == 'Добавък')) {

$zk_za_otchet = ($row->premiq + $row->vnoska_gf) - $komi;

$row->kvitancia_danak = number_format($zk_za_otchet, 2).' лв.';

}

else {

if($row->stiker_vnoska == null) {

$premiqta = $row->premiq;

$stikerche = 0;

}

else {

$stikerche = $row->stiker_vnoska;

$premiika = $row->premiq;

$premiqta = $premiq + $stikerche;

}

$danyche = $row->premiq * 0.02;

if($row->vnoska_gf != 0) {

$premiq_1 = $row->premiq + $row->vnoska_gf;

}

else {

$premiq_1 = $row->premiq;

}

$suma = $danyche + $premiq_1;

$za_otchet = $row->stoinost - $sum;

$row->kvitancia_danak = number_format($za_otchet, 2).' лв.';

}

$row->stiker_vnoska = (($row->zelena_karta_number == '- - -') && ($row->dobavak_number == '- - -') && (!empty($row->stiker_vnoska))) ? $row->stiker_vnoska : '';

#$row->komisiona_vnoska = (($row->zelena_karta_number == '- - -') && ($row->dobavak_number == '- - -') && (!empty($row->stiker_vnoska))) ? $row->komisiona_vnoska : '';

#print_r($row);

}

// return the modified

return $gdata;

}

This topic was modified 11 years, 3 months ago by tony. Reason: Please use code editor to format code

Creator

Topic

Viewing 3 replies - 1 through 3 (of 3 total)

Author

Replies
January 12, 2015 at 11:16 am #115244

tony
Keymaster

Hello,

Unfortunately we do not support a export to csv/excel/pdf when a custom data PHP function is available.
I have look into the code and it seems we can prepare this for the next release.

Kind Regards

January 12, 2015 at 1:08 pm #115255

overlucked
Participant

Ok. Thank you, Tony.
Is there any other way I can export my data to csv? Like a button from other file and getting information between div tags or something like this?

January 12, 2015 at 3:27 pm #115256

tony
Keymaster

Hello,

I think yes. Look at the parameters of renderGrid – there is one called echo, which can echo the result or return one as object.
This you can determine if you are in export and conditionallly call the renderGrid

PHP

if($grid->export == 'csv') { // call renderer with echo false } else { // call renderer with echo true (default) }

1
2
3
4
5

if($grid->export == 'csv') {
// call renderer with echo false
} else {
// call renderer with echo true (default)
}

Kind Regards
Author

Replies

Viewing 3 replies - 1 through 3 (of 3 total)

You must be logged in to reply to this topic.

Stay connected with us in your favorite flavor!

Not exporting all columns data

Search Forum

Recent Replies

Forum Statistics

UserOnline