function query_to_csv($query, $headers = TRUE, $download = "")
{
if (!is_object($query) or !method_exists($query, 'list_fields')) {
show_error('invalid query');
}
$array = array();
if ($headers) {
$line = array();
foreach ($query->list_fields() as $name) {
$line[] = ucwords(str_replace('_', ' ', $name));
}
$array[] = $line;
}
foreach ($query->result_array() as $row) {
$line = array();
foreach ($row as $item) {
if (dateChecker($item) == true) {
$item = date("D jS F Y", strtotime($item));
}
$line[] = strip_word_html($item);
}
$array[] = $line;
}
echo array_to_csv($array, $download);
}
function query_to_csv($query, $headers = TRUE, $download = "")
{
if ( ! is_object($query) OR ! method_exists($query, 'list_fields'))
{
show_error('invalid query');
}
$array = array();
if ($headers)
{
$line = array();
foreach ($query->list_fields() as $name)
{
$line[] = $name;
}
$array[] = $line;
}
foreach ($query->result_array() as $row)
{
$line = array();
foreach ($row as $item)
{
$line[] = $item;
}
$array[] = $line;
}
echo array_to_csv($array, $download);
}
function create_csv()
{
$query = $this->db->query('SELECT * FROM user ');
// $query=$query->result();
// $num = $query->num_fields();
// // var_dump($num); exit();
// $var =array();
// $i=1;
// $fname="";
// while($i <= $num){
// $test = $i;
// $value = $this->input->post($test);
// if($value != ''){
// $fname= $fname." ".$value;
// array_push($var, $value);
// }
// $i++;
// }
// $fname = trim($fname);
// $fname=str_replace(' ', ',', $fname);
// $this->db->select($fname);
// $quer = $this->db->get('user');
// $quer=$quer->result();
// var_dump($quer); exit();
// query_to_csv($quer,TRUE,'Products_'.date('dMy').'.csv');
$array = array(array('Last Name', 'First Name', 'Gender'), array('Furtado', 'Nelly', 'female'), array('Twain', 'Shania', 'female'), array('Farmer', 'Mylene', 'female'));
//$this->load->helper('csv');
//echo array_to_csv($array);
array_to_csv($array, 'toto.csv');
}
public function createcsv($id_kls = '')
{
if (!$id_kls == '') {
//ambil data nilai
$filter_kls = "p.id_kls = '" . $id_kls . "'";
$temp_nilai = $this->feeder->getrset($this->session->userdata('token'), $this->tbl_nilai, $filter_kls, $this->order, '', $this->offset);
$dumy_nilai = $temp_nilai['result'];
//var_dump($dumy_nilai);
//ambil struktur tabel nilai
$temp_dic = $this->feeder->getdic($this->session->userdata('token'), $this->tbl_nilai);
$dumy_dic = $temp_dic['result'];
$array = array();
$header_nilai = array('no_urut', 'id_kls', 'id_reg_pd', 'nim', 'nm_mhs', 'nilai_angka', 'nilai_huruf', 'nilai_indeks');
$array[] = $header_nilai;
//create content
$i = 0;
foreach ($dumy_nilai as $row) {
++$i;
$filter_mhs_pt = "id_reg_pd = '" . $row['id_reg_pd'] . "'";
$temp_mhs_pt = $this->feeder->getrecord($this->session->userdata('token'), 'mahasiswa_pt', $filter_mhs_pt);
$filter_mhs = "id_pd = '" . $temp_mhs_pt['result']['id_pd'] . "'";
$temp_mhs = $this->feeder->getrecord($this->session->userdata('token'), 'mahasiswa', $filter_mhs);
$content_nilai = array($i, $row['id_kls'], $row['id_reg_pd'], $temp_mhs_pt['result']['nipd'], $temp_mhs['result']['nm_pd'], $row['nilai_angka'], $row['nilai_huruf'], $row['nilai_indeks']);
$array[] = $content_nilai;
}
//$array[] = $header_nilai;
array_to_csv($array, $id_kls . '.csv');
//var_dump($array);
} else {
echo "Cannot create CSV";
}
}
public function download()
{
error_reporting(0);
$this->load->helper('csv');
$this->load->model('crm/lead_call_funnel_model');
// $data['title']="Lead Call funnel";
// $this->load->view('include/header',$data);
// $this->load->view('lead_call_funnel_view');
$leadCallData = array();
//$fh = @fopen( 'php://output', 'w' );
$start_data = $this->input->post('start_date');
$end_date = $this->input->post('end_date');
$type = $this->input->post('type');
$distinct_source = $this->lead_call_funnel_model->distinct_source();
//print_r($distinct_source);
$first_iteration = 1;
$rows = array(array());
$rows[0][0] = 'sources';
$row_count = 0;
foreach ($distinct_source as $row) {
$source = (string) $row->Source;
$rows[][0] = $source;
$getQueries = $this->lead_call_funnel_model->get_query($type);
if ($first_iteration) {
$first_iteration = 0;
foreach ($getQueries as $getQuery) {
$columnName = $getQuery->columnName;
//echo $columnName;
$rows[$row_count][] = $columnName;
}
$row_count = $row_count + 1;
continue;
}
foreach ($getQueries as $getQuery) {
$sqlQuery = $getQuery->sqlQuery;
//echo $columnName;
$rows[$row_count][] = $this->lead_call_funnel_model->main_query($sqlQuery, $source, $start_data, $end_date);
}
$row_count = $row_count + 1;
}
//print_r($first_row);
$list = array(array('aaa', 'bbb', 'ccc', 'dddd'), array('123', '456', '789'), array('"aaa"', '"bbb"'));
array_to_csv($rows, 'toto.csv');
//fputcsv($fh, $first_row);
//fclose($fh);
}
public function createcsv()
{
$separasi = $this->input->post('separasi');
$array = array();
$temp_header = array('nim_mhs', 'nm_mhs', 'semester', 'ips', 'ipk', 'sks_semester', 'sks_total', 'status_mhs');
$array[] = $temp_header;
$sample = array('Nomor Induk Mahasiswa', 'Nama Mahasiswa', 'Semester Mahasiswa (contoh: 20142)', 'IP Semester', 'IP Kumulatif', 'SKS Semester', 'SKS Total', 'Status Mahasiswa (A: Aktif, C: Cuti, D: Drop-out/Putus Studi, L: Lulus, P: , K: Keluar, N: Non Aktif, G: Sedang Double Degree, X: Unknown)');
$array[] = $sample;
$time = time();
//var_dump($array);
$temp_tulis = write_file('temps/' . $time . '_akm.csv', array_to_csv($array, '', $separasi));
//echo "File berhasil digenerate. <a href=\"".base_url()."temps/".$time."_mahasiswa.csv\">Download</a>";
if ($temp_tulis) {
echo "<div class=\"bs-callout bs-callout-success\">\n File berhasil digenerate. <a href=\"" . base_url() . "temps/" . $time . "_akm.csv\">Download</a>\n </div>";
} else {
echo "<div class=\"bs-callout bs-callout-danger\">\n <h4>Error</h4>File tidak bisa digenerate. Folder 'temps' tidak ada atau tidak bisa ditulisi.\n </div>";
}
}
public function createcsv()
{
$id_kls = $this->input->post('id_kls', TRUE);
$separasi = $this->input->post('separasi', TRUE);
if (!$id_kls == '') {
//ambil data nilai
$filter_kls = "p.id_kls = '" . $id_kls . "'";
$temp_nilai = $this->feeder->getrset($this->session->userdata('token'), $this->tbl_nilai, $filter_kls, $this->order, '', $this->offset);
$dumy_nilai = $temp_nilai['result'];
//var_dump($dumy_nilai);
//ambil struktur tabel nilai
$temp_dic = $this->feeder->getdic($this->session->userdata('token'), $this->tbl_nilai);
$dumy_dic = $temp_dic['result'];
$array = array();
$header_nilai = array('no_urut', 'id_kls', 'id_reg_pd', 'nim', 'nm_mhs', 'nilai_angka', 'nilai_huruf', 'nilai_indeks');
$array[] = $header_nilai;
//create content
$i = 0;
foreach ($dumy_nilai as $row) {
++$i;
$filter_mhs_pt = "id_reg_pd = '" . $row['id_reg_pd'] . "'";
$temp_mhs_pt = $this->feeder->getrecord($this->session->userdata('token'), 'mahasiswa_pt', $filter_mhs_pt);
$filter_mhs = "id_pd = '" . $temp_mhs_pt['result']['id_pd'] . "'";
$temp_mhs = $this->feeder->getrecord($this->session->userdata('token'), 'mahasiswa', $filter_mhs);
$content_nilai = array($i, $row['id_kls'], $row['id_reg_pd'], $temp_mhs_pt['result']['nipd'], $temp_mhs['result']['nm_pd'], $row['nilai_angka'], $row['nilai_huruf'], $row['nilai_indeks']);
$array[] = $content_nilai;
}
//$array[] = $header_nilai;
//array_to_csv($array, $id_kls.'.csv');
//var_dump($array);
$date = date('Y-m-d');
$time = time();
write_file('temps/nilai_' . $date . '-' . $time . '.csv', array_to_csv($array, '', $separasi));
//echo "File berhasil digenerate. <a href=\"".base_url()."temps/".$time."_mahasiswa.csv\">Download</a>";
echo "<div class=\"bs-callout bs-callout-success\">\n File berhasil digenerate. <a href=\"" . base_url() . "temps/nilai_" . $date . '-' . $time . ".csv\">Download</a>\n </div>";
} else {
echo "Cannot create CSV";
}
}
function query_to_csv($query, $headers = TRUE, $download = "")
{
if (!is_object($query) or !method_exists($query, 'getFieldNames')) {
show_error('invalid query: ' . $query);
}
$array = array();
if ($headers) {
$line = array();
foreach ($query->getFieldNames() as $name) {
$line[] = $name;
}
$array[] = $line;
}
foreach ($query->toArray() as $row) {
$line = array();
foreach ($row as $item) {
$line[] = $item;
}
$array[] = $line;
}
echo array_to_csv($array, $download);
}
function createCSV()
{
$pharmacy_stateId = $this->input->post('pharmacy_stateId');
$pharmacy_cityId = $this->input->post('pharmacy_cityId');
if ($pharmacy_stateId != '' && $pharmacy_stateId != null) {
$pharmacy_stateId = $this->input->post('pharmacy_stateId');
}
if ($pharmacy_cityId != '' && $pharmacy_cityId != null) {
$pharmacy_cityId = $this->input->post('pharmacy_cityId');
}
$where = array('pharmacy_deleted' => 0, 'pharmacy_cityId' => $pharmacy_cityId, 'pharmacy_stateId' => $pharmacy_stateId);
// print_r($where); exit;
$array[] = array('Image Name', 'Pharmacy Name', 'City', 'Phone Number', 'Address');
$data = $this->Pharmacy_model->createCSVdata($where);
$arrayFinal = array_merge($array, $data);
array_to_csv($arrayFinal, 'PharmacyDetail.csv');
return True;
exit;
}
public function directCall($item = "", $param = "")
{
$dbArr = array();
switch ($item) {
case "export":
if ($_GET['area_no'] != "" && $_GET['area_no'] != "'<ALL>'") {
$isoWhere = "AND area_no in ({$_GET['area_no']})";
}
if ($_GET['rsOption'] > 0) {
$spoolWhere = "AND spool.workable_dt " . ($_GET['rsOption'] == 1 ? "!= NULL" : "= NULL");
}
$fieldVal = "";
if (isset($_GET['fieldF']) && is_array($_GET['fieldF'])) {
foreach ($_GET['fieldF'] as $field => $val) {
$res = explode(";", $val);
$fieldVal .= " AND " . $res[0] . $this->operatorArr[$res[2]] . $res[1] . (in_array($res[2], $this->blankThis) ? "" : "%") . "'";
}
}
foreach ($this->iso_model->area_query_export(isset($fieldVal) ? $fieldVal : "", isset($isoWhere) ? $isoWhere : "", isset($spoolWhere) ? $spoolWhere : "") as $r) {
foreach ($r as $r2 => $v2) {
if (is_object($v2)) {
$date_array = (array) $v2;
$date_time = $date_array['date'];
$date_only = explode(" ", $date_time);
$r[$r2] = $date_only[0];
}
}
array_push($dbArr, $r);
}
// ------ Create and download csv ----
$csv = array_to_csv($dbArr, "area_query.csv");
header('Content-Type: application/csv');
header('Content-Disposition: attachment; filename="area_query.csv"');
$csv = urldecode($csv);
echo $csv;
return true;
break;
case "export_insTo":
$this->read("inst_takeoff", "get_all_export", $fieldVal, $dbArr);
// ------ Create and download csv ----
$csv = array_to_csv($dbArr, "insto.csv");
header('Content-Type: application/csv');
header('Content-Disposition: attachment; filename="insto.csv"');
$csv = urldecode($csv);
echo $csv;
return true;
break;
case "client":
$fieldVal = $this->session->userdata('department') == "MIS" ? "1" : "short_desc != 'ARCC'";
$this->load->model('portal/rclient_model');
$this->read("rclient", "get_all", $fieldVal, $dbArr);
break;
case "files":
//echo (!isset($_GET['tgHist']) || $_GET['tgHist'] == 1) ? "here" : " AND date_open is null";
$fieldVal = " ";
if (isset($_GET['type_of_trans']) && $_GET['type_of_trans'] == "download") {
//$fieldVal = " find_in_set({$_GET['client_id']},recipients) > 0 AND find_in_set({$this->session->userdata('id')},users) > 0";
$fieldVal = " find_in_set({$_GET['client_id']},recipients) > 0 AND find_in_set({$this->session->userdata('id')},users) > 0 " . (!isset($_GET['tgHist']) || $_GET['tgHist'] == 1 ? "" : " AND date_open is null");
} else {
$fieldVal = " t.log_created like '{$this->session->userdata('user_id')}%'";
}
$this->load->model('portal/files_model');
$this->read("files", "get_all", $fieldVal, $dbArr);
// foreach ($dbArr as $key => $value) {
// foreach ($value as $key2 => $value2) {
// if ($key2 == "name")
// $dbArr[$key][$key2] = str_replace("'", "\\'", $value2);
// }
// }
break;
case "client_cretorId":
// $fieldVal = ($this->session->userdata('department') == "MIS" ? "1" : ("t.log_created like '" . $this->session->userdata('user_id') . "%'"));
// $fieldVal = "t.log_created like '" . $this->session->userdata('user_id') . "%' and t.user_id != '" . $this->session->userdata('user_id') . "'"; //improvements
//$fieldVal = "find_in_set(t.id,'" . $_GET['follows'] . "') != 0 and t.user_id != '" . $this->session->userdata('user_id') . "'"; //improvements
$fieldVal = "t.department = '{$_GET['creator']}' and t.user_id != '" . $this->session->userdata('user_id') . "'";
$this->load->model('portal/ruser_model');
$this->read("ruser", "get_all", $fieldVal, $dbArr);
break;
case "user_assign":
// $fieldVal = ($this->session->userdata('department') == "MIS" ? "1" : ("t.log_created like '" . $this->session->userdata('user_id') . "%'"));
// $fieldVal = "t.log_created like '" . $this->session->userdata('user_id') . "%' and t.user_id != '" . $this->session->userdata('user_id') . "'"; //improvements
//$fieldVal = "find_in_set(t.id,'" . $_GET['follows'] . "') != 0 and t.user_id != '" . $this->session->userdata('user_id') . "'"; //improvements
$fieldVal = "t.client_id != 15";
//t.creator = '' and
$this->load->model('portal/ruser_model');
$this->read("ruser", "get_all", $fieldVal, $dbArr);
break;
case "user":
// $fieldVal = ($this->session->userdata('department') == "MIS" ? "1" : ("t.log_created like '" . $this->session->userdata('user_id') . "%'"));
// $fieldVal = "t.log_created like '" . $this->session->userdata('user_id') . "%' and t.user_id != '" . $this->session->userdata('user_id') . "'"; //improvements
$fieldVal = "find_in_set(t.id,'" . $_GET['follows'] . "') != 0 and t.user_id != '" . $this->session->userdata('user_id') . "'";
//improvements
$this->load->model('portal/ruser_model');
$this->read("ruser", "get_all", $fieldVal, $dbArr);
break;
case "modified_connect":
if ($this->session->userdata('department') == "MIS") {
if ($_GET['type'] == "1") {
$fieldVal = "find_in_set(t.id,'" . $_GET['follows'] . "') != 0 and t.user_id != '" . $this->session->userdata('user_id') . "'";
} else {
$fieldVal = "find_in_set(t.id,'" . $_GET['follows'] . "') = 0 and t.user_id != '" . $this->session->userdata('user_id') . "'";
}
//improvements
$this->load->model('portal/ruser_model');
$this->read("ruser", "get_all", $fieldVal, $dbArr);
} else {
if ($_GET['type'] == "1") {
$fieldVal = "find_in_set(t.id,'" . $_GET['follows'] . "') != 0 and t.user_id != '" . $this->session->userdata('user_id') . "'";
} else {
$fieldVal = "find_in_set(t.id,'" . $_GET['follows'] . "') = 0 and t.user_id != '" . $this->session->userdata('user_id') . "'";
}
//improvements . " and t.department = '" . $this->session->userdata('department') . "'" -- . " and t.creator = '" . $this->session->userdata('department') . "'"
$this->load->model('portal/ruser_model');
$this->read("ruser", "get_connect", $fieldVal, $dbArr);
}
break;
case "user_connect":
if ($_GET['type'] == "1") {
$fieldVal = "find_in_set(t.id,'" . $_GET['follows'] . "') != 0 and t.user_id != '" . $this->session->userdata('user_id') . "'";
} else {
$fieldVal = "find_in_set(t.id,'" . $_GET['follows'] . "') = 0 and t.user_id != '" . $this->session->userdata('user_id') . "'";
}
//improvements
$this->load->model('portal/ruser_model');
$this->read("ruser", "get_all", $fieldVal, $dbArr);
break;
case "user_by_id":
$fieldVal = $this->session->userdata('creator');
$this->load->model('portal/ruser_model');
$this->read("ruser", "get_by_id", $fieldVal, $dbArr);
break;
case "user_dd":
$this->load->model('portal/ruser_model');
$this->read("ruser", "get_all_dd", $fieldVal, $dbArr);
break;
case "expired":
$this->load->model('portal/ruser_model');
$this->read("ruser", "get_expired", $fieldVal, $dbArr);
break;
case "pieceStruc":
$fieldVal = "plant_no = '{$_GET['plant_no']}' and area_no = '{$_GET['area_no']}' and drawing_no = '{$_GET['drawing_no']}' and sheet_no = '{$_GET['sheet_no']}' and rev_no = '{$_GET['rev_no']}' and area_loc = '{$_GET['area_loc']}'";
$this->read("piece_struc", "get_all", $fieldVal, $dbArr);
break;
case "get_department":
// $fieldVal = "plant_no = '{$_GET['plant_no']}' and area_no = '{$_GET['area_no']}' and drawing_no = '{$_GET['drawing_no']}' and sheet_no = '{$_GET['sheet_no']}' and rev_no = '{$_GET['rev_no']}' and area_loc = '{$_GET['area_loc']}'";
$this->load->model("portal/department_model");
$this->read("department", "get_all", $fieldVal, $dbArr);
break;
case "get_dept_dd":
$fieldVal = "t.flg_status = 1 ";
$this->load->model("portal/department_model");
$this->read("department", "get_all_dd", $fieldVal, $dbArr);
break;
case "dept_dd":
$fieldVal = "t.flg_status = 1 ";
$this->load->model("portal/department_model");
$this->read("department", "get_all_dd", $fieldVal, $dbArr);
break;
case "client_dept":
$fieldVal = "t.flg_status = 1 ";
$this->load->model("portal/department_model");
$this->read("department", "get_all_dd", $fieldVal, $dbArr);
break;
default:
break;
}
$this->result['rows'] = $dbArr;
$this->output->set_content_type('application/json')->set_output(json_encode($this->result));
}
function createCSV()
{
$stateId = '';
$cityId = '';
if (isset($_POST['diagnostic_stateId'])) {
$stateId = $this->input->post('diagnostic_stateId');
}
if (isset($_POST['diagnostic_cityId'])) {
$cityId = $this->input->post('diagnostic_cityId');
}
$where = array('diagnostic_deleted' => 0, 'diagnostic_cityId' => $cityId, 'diagnostic_stateId' => $stateId);
$array[] = array('Image Name', 'Diagnostic Name', 'City', 'Phone Number', 'Address');
$data = $this->diagnostic_model->createCSVdata($where);
$arrayFinal = array_merge($array, $data);
array_to_csv($arrayFinal, 'DiagnosticDetail.csv');
return True;
exit;
}
/**
* @project Qyura
* @method createCSV
* @description reviews create csv file
* @access public
* @return array
*/
function createCSV()
{
$sDate = '';
$eDate = '';
if (isset($_POST['date-1']) && isset($_POST['date-2']) && !empty($_POST['date-1']) && !empty($_POST['date-2'])) {
$sDate = $this->input->post('date-1');
$eDate = $this->input->post('date-2');
}
$filter = $this->input->post('filter');
$reviews = $this->reviews_model->fetchReviews(array('filter' => $filter, 'sDate' => $sDate, 'eDate' => $eDate));
$result = array();
$i = 1;
$imgUrl = base_url() . 'assets/patientImages/';
foreach ($reviews as $key => $val) {
$result[$i]['reviewBy'] = $val['reviewBy'];
$result[$i]['patientDetails_patientImg'] = $imgUrl . $val['patientDetails_patientImg'];
$result[$i]['reviews_details'] = $val['reviews_details'];
$result[$i]['reviews_rating'] = $val['reviews_rating'];
$result[$i]['reviewTo'] = $val['reviewTo'];
$result[$i]['reviews_post_details'] = $val['reviews_post_details'];
$result[$i]['creationTime'] = date('d F Y h:i:s A', $val['creationTime']);
$i++;
}
$array[] = array('Patient Name', 'Patient Image', 'Patient Review', 'Rating', 'MI Name', 'MI Comment', 'Review Date');
$arrayFinal = array_merge($array, $result);
array_to_csv($arrayFinal, 'Reviews.csv');
return True;
exit;
}
function download($statement, $id = NULL)
{
/********************** TRIAL BALANCE *************************/
if ($statement == "trialbalance") {
$this->load->model('Ledger_model');
$all_ledgers = $this->Ledger_model->get_all_ledgers();
$counter = 0;
$trialbalance = array();
$temp_dr_total = 0;
$temp_cr_total = 0;
$trialbalance[$counter] = array("TRIAL BALANCE", "", "", "", "", "", "", "", "");
$counter++;
$trialbalance[$counter] = array("FY " . date_mysql_to_php($this->config->item('account_fy_start')) . " - " . date_mysql_to_php($this->config->item('account_fy_end')), "", "", "", "", "", "", "", "");
$counter++;
$trialbalance[$counter][0] = "Ledger";
$trialbalance[$counter][1] = "";
$trialbalance[$counter][2] = "Opening";
$trialbalance[$counter][3] = "";
$trialbalance[$counter][4] = "Closing";
$trialbalance[$counter][5] = "";
$trialbalance[$counter][6] = "Dr Total";
$trialbalance[$counter][7] = "";
$trialbalance[$counter][8] = "Cr Total";
$counter++;
foreach ($all_ledgers as $ledger_id => $ledger_name) {
if ($ledger_id == 0) {
continue;
}
$trialbalance[$counter][0] = $ledger_name;
list($opbal_amount, $opbal_type) = $this->Ledger_model->get_op_balance($ledger_id);
if (float_ops($opbal_amount, 0, '==')) {
$trialbalance[$counter][1] = "";
$trialbalance[$counter][2] = 0;
} else {
$trialbalance[$counter][1] = convert_dc($opbal_type);
$trialbalance[$counter][2] = $opbal_amount;
}
$clbal_amount = $this->Ledger_model->get_ledger_balance($ledger_id);
if (float_ops($clbal_amount, 0, '==')) {
$trialbalance[$counter][3] = "";
$trialbalance[$counter][4] = 0;
} else {
if (float_ops($clbal_amount, 0, '<')) {
$trialbalance[$counter][3] = "Cr";
$trialbalance[$counter][4] = convert_cur(-$clbal_amount);
} else {
$trialbalance[$counter][3] = "Dr";
$trialbalance[$counter][4] = convert_cur($clbal_amount);
}
}
$dr_total = $this->Ledger_model->get_dr_total($ledger_id);
if ($dr_total) {
$trialbalance[$counter][5] = "Dr";
$trialbalance[$counter][6] = convert_cur($dr_total);
$temp_dr_total = float_ops($temp_dr_total, $dr_total, '+');
} else {
$trialbalance[$counter][5] = "";
$trialbalance[$counter][6] = 0;
}
$cr_total = $this->Ledger_model->get_cr_total($ledger_id);
if ($cr_total) {
$trialbalance[$counter][7] = "Cr";
$trialbalance[$counter][8] = convert_cur($cr_total);
$temp_cr_total = float_ops($temp_cr_total, $cr_total, '+');
} else {
$trialbalance[$counter][7] = "";
$trialbalance[$counter][8] = 0;
}
$counter++;
}
$trialbalance[$counter][0] = "";
$trialbalance[$counter][1] = "";
$trialbalance[$counter][2] = "";
$trialbalance[$counter][3] = "";
$trialbalance[$counter][4] = "";
$trialbalance[$counter][5] = "";
$trialbalance[$counter][6] = "";
$trialbalance[$counter][7] = "";
$trialbalance[$counter][8] = "";
$counter++;
$trialbalance[$counter][0] = "Total";
$trialbalance[$counter][1] = "";
$trialbalance[$counter][2] = "";
$trialbalance[$counter][3] = "";
$trialbalance[$counter][4] = "";
$trialbalance[$counter][5] = "Dr";
$trialbalance[$counter][6] = convert_cur($temp_dr_total);
$trialbalance[$counter][7] = "Cr";
$trialbalance[$counter][8] = convert_cur($temp_cr_total);
$this->load->helper('csv');
echo array_to_csv($trialbalance, "trialbalance.csv");
return;
}
/********************** LEDGER STATEMENT **********************/
if ($statement == "ledgerst") {
$ledger_id = (int) $this->uri->segment(4);
if ($ledger_id < 1) {
return;
}
$this->load->model('Ledger_model');
$cur_balance = 0;
$counter = 0;
$ledgerst = array();
$ledgerst[$counter] = array("", "", "LEDGER STATEMENT FOR " . strtoupper($this->Ledger_model->get_name($ledger_id)), "", "", "", "", "", "", "", "");
$counter++;
$ledgerst[$counter] = array("", "", "FY " . date_mysql_to_php($this->config->item('account_fy_start')) . " - " . date_mysql_to_php($this->config->item('account_fy_end')), "", "", "", "", "", "", "", "");
$counter++;
$ledgerst[$counter][0] = "Date";
$ledgerst[$counter][1] = "Number";
$ledgerst[$counter][2] = "Ledger Name";
$ledgerst[$counter][3] = "Narration";
$ledgerst[$counter][4] = "Type";
$ledgerst[$counter][5] = "";
$ledgerst[$counter][6] = "Dr Amount";
$ledgerst[$counter][7] = "";
$ledgerst[$counter][8] = "Cr Amount";
$ledgerst[$counter][9] = "";
$ledgerst[$counter][10] = "Balance";
$counter++;
/* Opening Balance */
list($opbalance, $optype) = $this->Ledger_model->get_op_balance($ledger_id);
$ledgerst[$counter] = array("Opening Balance", "", "", "", "", "", "", "", "", convert_dc($optype), $opbalance);
if ($optype == "D") {
$cur_balance = float_ops($cur_balance, $opbalance, '+');
} else {
$cur_balance = float_ops($cur_balance, $opbalance, '-');
}
$counter++;
$this->db->select('entries.id as entries_id, entries.number as entries_number, entries.date as entries_date, entries.narration as entries_narration, entries.entry_type as entries_entry_type, entry_items.amount as entry_items_amount, entry_items.dc as entry_items_dc');
$this->db->from('entries')->join('entry_items', 'entries.id = entry_items.entry_id')->where('entry_items.ledger_id', $ledger_id)->order_by('entries.date', 'asc')->order_by('entries.number', 'asc');
$ledgerst_q = $this->db->get();
foreach ($ledgerst_q->result() as $row) {
/* Entry Type */
$current_entry_type = entry_type_info($row->entries_entry_type);
$ledgerst[$counter][0] = date_mysql_to_php($row->entries_date);
$ledgerst[$counter][1] = full_entry_number($row->entries_entry_type, $row->entries_number);
/* Opposite entry name */
$ledgerst[$counter][2] = $this->Ledger_model->get_opp_ledger_name($row->entries_id, $row->entries_entry_type, $row->entry_items_dc, 'text');
$ledgerst[$counter][3] = $row->entries_narration;
$ledgerst[$counter][4] = $current_entry_type['name'];
if ($row->entry_items_dc == "D") {
$cur_balance = float_ops($cur_balance, $row->entry_items_amount, '+');
$ledgerst[$counter][5] = convert_dc($row->entry_items_dc);
$ledgerst[$counter][6] = $row->entry_items_amount;
$ledgerst[$counter][7] = "";
$ledgerst[$counter][8] = "";
} else {
$cur_balance = float_ops($cur_balance, $row->entry_items_amount, '-');
$ledgerst[$counter][5] = "";
$ledgerst[$counter][6] = "";
$ledgerst[$counter][7] = convert_dc($row->entry_items_dc);
$ledgerst[$counter][8] = $row->entry_items_amount;
}
if (float_ops($cur_balance, 0, '==')) {
$ledgerst[$counter][9] = "";
$ledgerst[$counter][10] = 0;
} else {
if (float_ops($cur_balance, 0, '<')) {
$ledgerst[$counter][9] = "Cr";
$ledgerst[$counter][10] = convert_cur(-$cur_balance);
} else {
$ledgerst[$counter][9] = "Dr";
$ledgerst[$counter][10] = convert_cur($cur_balance);
}
}
$counter++;
}
$ledgerst[$counter][0] = "Closing Balance";
$ledgerst[$counter][1] = "";
$ledgerst[$counter][2] = "";
$ledgerst[$counter][3] = "";
$ledgerst[$counter][4] = "";
$ledgerst[$counter][5] = "";
$ledgerst[$counter][6] = "";
$ledgerst[$counter][7] = "";
$ledgerst[$counter][8] = "";
if (float_ops($cur_balance, 0, '<')) {
$ledgerst[$counter][9] = "Cr";
$ledgerst[$counter][10] = convert_cur(-$cur_balance);
} else {
$ledgerst[$counter][9] = "Dr";
$ledgerst[$counter][10] = convert_cur($cur_balance);
}
$counter++;
$ledgerst[$counter] = array("", "", "", "", "", "", "", "", "", "", "");
$counter++;
/* Final Opening and Closing Balance */
$clbalance = $this->Ledger_model->get_ledger_balance($ledger_id);
$ledgerst[$counter] = array("Opening Balance", convert_dc($optype), $opbalance, "", "", "", "", "", "", "", "");
$counter++;
if (float_ops($clbalance, 0, '==')) {
$ledgerst[$counter] = array("Closing Balance", "", 0, "", "", "", "", "", "", "", "");
} else {
if (float_ops($clbalance, 0, '<')) {
$ledgerst[$counter] = array("Closing Balance", "Cr", convert_cur(-$clbalance), "", "", "", "", "", "", "", "");
} else {
$ledgerst[$counter] = array("Closing Balance", "Dr", convert_cur($clbalance), "", "", "", "", "", "", "", "");
}
}
$this->load->helper('csv');
echo array_to_csv($ledgerst, "ledgerst.csv");
return;
}
/********************** RECONCILIATION ************************/
if ($statement == "reconciliation") {
$ledger_id = (int) $this->uri->segment(4);
$reconciliation_type = $this->uri->segment(5);
if ($ledger_id < 1) {
return;
}
if (!($reconciliation_type == 'all' or $reconciliation_type == 'pending')) {
return;
}
$this->load->model('Ledger_model');
$cur_balance = 0;
$counter = 0;
$ledgerst = array();
$ledgerst[$counter] = array("", "", "RECONCILIATION STATEMENT FOR " . strtoupper($this->Ledger_model->get_name($ledger_id)), "", "", "", "", "", "", "");
$counter++;
$ledgerst[$counter] = array("", "", "FY " . date_mysql_to_php($this->config->item('account_fy_start')) . " - " . date_mysql_to_php($this->config->item('account_fy_end')), "", "", "", "", "", "", "");
$counter++;
$ledgerst[$counter][0] = "Date";
$ledgerst[$counter][1] = "Number";
$ledgerst[$counter][2] = "Ledger Name";
$ledgerst[$counter][3] = "Narration";
$ledgerst[$counter][4] = "Type";
$ledgerst[$counter][5] = "";
$ledgerst[$counter][6] = "Dr Amount";
$ledgerst[$counter][7] = "";
$ledgerst[$counter][8] = "Cr Amount";
$ledgerst[$counter][9] = "Reconciliation Date";
$counter++;
/* Opening Balance */
list($opbalance, $optype) = $this->Ledger_model->get_op_balance($ledger_id);
$this->db->select('entries.id as entries_id, entries.number as entries_number, entries.date as entries_date, entries.narration as entries_narration, entries.entry_type as entries_entry_type, entry_items.amount as entry_items_amount, entry_items.dc as entry_items_dc, entry_items.reconciliation_date as lreconciliation');
if ($reconciliation_type == 'all') {
$this->db->from('entries')->join('entry_items', 'entries.id = entry_items.entry_id')->where('entry_items.ledger_id', $ledger_id)->order_by('entries.date', 'asc')->order_by('entries.number', 'asc');
} else {
$this->db->from('entries')->join('entry_items', 'entries.id = entry_items.entry_id')->where('entry_items.ledger_id', $ledger_id)->where('entry_items.reconciliation_date', NULL)->order_by('entries.date', 'asc')->order_by('entries.number', 'asc');
}
$ledgerst_q = $this->db->get();
foreach ($ledgerst_q->result() as $row) {
/* Entry Type */
$current_entry_type = entry_type_info($row->entries_entry_type);
$ledgerst[$counter][0] = date_mysql_to_php($row->entries_date);
$ledgerst[$counter][1] = full_entry_number($row->entries_entry_type, $row->entries_number);
/* Opposite entry name */
$ledgerst[$counter][2] = $this->Ledger_model->get_opp_ledger_name($row->entries_id, $row->entries_entry_type, $row->entry_items_dc, 'text');
$ledgerst[$counter][3] = $row->entries_narration;
$ledgerst[$counter][4] = $current_entry_type['name'];
if ($row->entry_items_dc == "D") {
$ledgerst[$counter][5] = convert_dc($row->entry_items_dc);
$ledgerst[$counter][6] = $row->entry_items_amount;
$ledgerst[$counter][7] = "";
$ledgerst[$counter][8] = "";
} else {
$ledgerst[$counter][5] = "";
$ledgerst[$counter][6] = "";
$ledgerst[$counter][7] = convert_dc($row->entry_items_dc);
$ledgerst[$counter][8] = $row->entry_items_amount;
}
if ($row->lreconciliation) {
$ledgerst[$counter][9] = date_mysql_to_php($row->lreconciliation);
} else {
$ledgerst[$counter][9] = "";
}
$counter++;
}
$counter++;
$ledgerst[$counter] = array("", "", "", "", "", "", "", "", "", "");
$counter++;
/* Final Opening and Closing Balance */
$clbalance = $this->Ledger_model->get_ledger_balance($ledger_id);
$ledgerst[$counter] = array("Opening Balance", convert_dc($optype), $opbalance, "", "", "", "", "", "", "");
$counter++;
if (float_ops($clbalance, 0, '==')) {
$ledgerst[$counter] = array("Closing Balance", "", 0, "", "", "", "", "", "", "");
} else {
if (float_ops($clbalance, 0, '<')) {
$ledgerst[$counter] = array("Closing Balance", "Cr", convert_cur(-$clbalance), "", "", "", "", "", "", "");
} else {
$ledgerst[$counter] = array("Closing Balance", "Dr", convert_cur($clbalance), "", "", "", "", "", "", "");
}
}
/************* Final Reconciliation Balance ***********/
/* Reconciliation Balance - Dr */
$this->db->select_sum('amount', 'drtotal')->from('entry_items')->join('entries', 'entries.id = entry_items.entry_id')->where('entry_items.ledger_id', $ledger_id)->where('entry_items.dc', 'D')->where('entry_items.reconciliation_date IS NOT NULL');
$dr_total_q = $this->db->get();
if ($dr_total = $dr_total_q->row()) {
$reconciliation_dr_total = $dr_total->drtotal;
} else {
$reconciliation_dr_total = 0;
}
/* Reconciliation Balance - Cr */
$this->db->select_sum('amount', 'crtotal')->from('entry_items')->join('entries', 'entries.id = entry_items.entry_id')->where('entry_items.ledger_id', $ledger_id)->where('entry_items.dc', 'C')->where('entry_items.reconciliation_date IS NOT NULL');
$cr_total_q = $this->db->get();
if ($cr_total = $cr_total_q->row()) {
$reconciliation_cr_total = $cr_total->crtotal;
} else {
$reconciliation_cr_total = 0;
}
$reconciliation_total = float_ops($reconciliation_dr_total, $reconciliation_cr_total, '-');
$reconciliation_pending = float_ops($clbalance, $reconciliation_total, '-');
$counter++;
if (float_ops($reconciliation_pending, 0, '==')) {
$ledgerst[$counter] = array("Reconciliation Pending", "", 0, "", "", "", "", "", "", "");
} else {
if (float_ops($reconciliation_pending, 0, '<')) {
$ledgerst[$counter] = array("Reconciliation Pending", "Cr", convert_cur(-$reconciliation_pending), "", "", "", "", "", "", "");
} else {
$ledgerst[$counter] = array("Reconciliation Pending", "Dr", convert_cur($reconciliation_pending), "", "", "", "", "", "", "");
}
}
$counter++;
if (float_ops($reconciliation_total, 0, '==')) {
$ledgerst[$counter] = array("Reconciliation Total", "", 0, "", "", "", "", "", "", "");
} else {
if (float_ops($reconciliation_total, 0, '<')) {
$ledgerst[$counter] = array("Reconciliation Total", "Cr", convert_cur(-$reconciliation_total), "", "", "", "", "", "", "");
} else {
$ledgerst[$counter] = array("Reconciliation Total", "Dr", convert_cur($reconciliation_total), "", "", "", "", "", "", "");
}
}
$this->load->helper('csv');
echo array_to_csv($ledgerst, "reconciliation.csv");
return;
}
/************************ BALANCE SHEET ***********************/
if ($statement == "balancesheet") {
$this->load->library('accountlist');
$this->load->model('Ledger_model');
$liability = new Accountlist();
$liability->init(2);
$liability_array = $liability->build_array();
$liability_depth = Accountlist::$max_depth;
$liability_total = -$liability->total;
Accountlist::reset_max_depth();
$asset = new Accountlist();
$asset->init(1);
$asset_array = $asset->build_array();
$asset_depth = Accountlist::$max_depth;
$asset_total = $asset->total;
$liability->to_csv($liability_array);
Accountlist::add_blank_csv();
$asset->to_csv($asset_array);
$income = new Accountlist();
$income->init(3);
$expense = new Accountlist();
$expense->init(4);
$income_total = -$income->total;
$expense_total = $expense->total;
$pandl = float_ops($income_total, $expense_total, '-');
$diffop = $this->Ledger_model->get_diff_op_balance();
Accountlist::add_blank_csv();
/* Liability side */
$total = $liability_total;
Accountlist::add_row_csv(array("Liabilities and Owners Equity Total", convert_cur($liability_total)));
/* If Profit then Liability side, If Loss then Asset side */
if (float_ops($pandl, 0, '!=')) {
if (float_ops($pandl, 0, '>')) {
$total = float_ops($total, $pandl, '+');
Accountlist::add_row_csv(array("Profit & Loss account (Net Profit)", convert_cur($pandl)));
}
}
/* If Op balance Dr then Liability side, If Op balance Cr then Asset side */
if (float_ops($diffop, 0, '!=')) {
if (float_ops($diffop, 0, '>')) {
$total = float_ops($total, $diffop, '+');
Accountlist::add_row_csv(array("Diff in O/P Balance", "Dr " . convert_cur($diffop)));
}
}
Accountlist::add_row_csv(array("Total - Liabilities and Owners Equity", convert_cur($total)));
/* Asset side */
$total = $asset_total;
Accountlist::add_row_csv(array("Asset Total", convert_cur($asset_total)));
/* If Profit then Liability side, If Loss then Asset side */
if (float_ops($pandl, 0, '!=')) {
if (float_ops($pandl, 0, '<')) {
$total = float_ops($total, -$pandl, '+');
Accountlist::add_row_csv(array("Profit & Loss account (Net Loss)", convert_cur(-$pandl)));
}
}
/* If Op balance Dr then Liability side, If Op balance Cr then Asset side */
if (float_ops($diffop, 0, '!=')) {
if (float_ops($diffop, 0, '<')) {
$total = float_ops($total, -$diffop, '+');
Accountlist::add_row_csv(array("Diff in O/P Balance", "Cr " . convert_cur(-$diffop)));
}
}
Accountlist::add_row_csv(array("Total - Assets", convert_cur($total)));
$balancesheet = Accountlist::get_csv();
$this->load->helper('csv');
echo array_to_csv($balancesheet, "balancesheet.csv");
return;
}
/********************** PROFIT AND LOSS ***********************/
if ($statement == "profitandloss") {
$this->load->library('accountlist');
$this->load->model('Ledger_model');
/***************** GROSS CALCULATION ******************/
/* Gross P/L : Expenses */
$gross_expense_total = 0;
$this->db->from('groups')->where('parent_id', 4)->where('affects_gross', 1);
$gross_expense_list_q = $this->db->get();
foreach ($gross_expense_list_q->result() as $row) {
$gross_expense = new Accountlist();
$gross_expense->init($row->id);
$gross_expense_total = float_ops($gross_expense_total, $gross_expense->total, '+');
$gross_exp_array = $gross_expense->build_array();
$gross_expense->to_csv($gross_exp_array);
}
Accountlist::add_blank_csv();
/* Gross P/L : Incomes */
$gross_income_total = 0;
$this->db->from('groups')->where('parent_id', 3)->where('affects_gross', 1);
$gross_income_list_q = $this->db->get();
foreach ($gross_income_list_q->result() as $row) {
$gross_income = new Accountlist();
$gross_income->init($row->id);
$gross_income_total = float_ops($gross_income_total, $gross_income->total, '+');
$gross_inc_array = $gross_income->build_array();
$gross_income->to_csv($gross_inc_array);
}
Accountlist::add_blank_csv();
Accountlist::add_blank_csv();
/* Converting to positive value since Cr */
$gross_income_total = -$gross_income_total;
/* Calculating Gross P/L */
$grosspl = float_ops($gross_income_total, $gross_expense_total, '-');
/* Showing Gross P/L : Expenses */
$grosstotal = $gross_expense_total;
Accountlist::add_row_csv(array("Total Gross Expenses", convert_cur($gross_expense_total)));
if (float_ops($grosspl, 0, '>')) {
$grosstotal = float_ops($grosstotal, $grosspl, '+');
Accountlist::add_row_csv(array("Gross Profit C/O", convert_cur($grosspl)));
}
Accountlist::add_row_csv(array("Total Expenses - Gross", convert_cur($grosstotal)));
/* Showing Gross P/L : Incomes */
$grosstotal = $gross_income_total;
Accountlist::add_row_csv(array("Total Gross Incomes", convert_cur($gross_income_total)));
if (float_ops($grosspl, 0, '>')) {
} else {
if (float_ops($grosspl, 0, '<')) {
$grosstotal = float_ops($grosstotal, -$grosspl, '+');
Accountlist::add_row_csv(array("Gross Loss C/O", convert_cur(-$grosspl)));
}
}
Accountlist::add_row_csv(array("Total Incomes - Gross", convert_cur($grosstotal)));
/************************* NET CALCULATIONS ***************************/
Accountlist::add_blank_csv();
Accountlist::add_blank_csv();
/* Net P/L : Expenses */
$net_expense_total = 0;
$this->db->from('groups')->where('parent_id', 4)->where('affects_gross !=', 1);
$net_expense_list_q = $this->db->get();
foreach ($net_expense_list_q->result() as $row) {
$net_expense = new Accountlist();
$net_expense->init($row->id);
$net_expense_total = float_ops($net_expense_total, $net_expense->total, '+');
$net_exp_array = $net_expense->build_array();
$net_expense->to_csv($net_exp_array);
}
Accountlist::add_blank_csv();
/* Net P/L : Incomes */
$net_income_total = 0;
$this->db->from('groups')->where('parent_id', 3)->where('affects_gross !=', 1);
$net_income_list_q = $this->db->get();
foreach ($net_income_list_q->result() as $row) {
$net_income = new Accountlist();
$net_income->init($row->id);
$net_income_total = float_ops($net_income_total, $net_income->total, '+');
$net_inc_array = $net_income->build_array();
$net_income->to_csv($net_inc_array);
}
Accountlist::add_blank_csv();
Accountlist::add_blank_csv();
/* Converting to positive value since Cr */
$net_income_total = -$net_income_total;
/* Calculating Net P/L */
$netpl = float_ops(float_ops($net_income_total, $net_expense_total, '-'), $grosspl, '+');
/* Showing Net P/L : Expenses */
$nettotal = $net_expense_total;
Accountlist::add_row_csv(array("Total Expenses", convert_cur($nettotal)));
if (float_ops($grosspl, 0, '>')) {
} else {
if (float_ops($grosspl, 0, '<')) {
$nettotal = float_ops($nettotal, -$grosspl, '+');
Accountlist::add_row_csv(array("Gross Loss B/F", convert_cur(-$grosspl)));
}
}
if (float_ops($netpl, 0, '>')) {
$nettotal = float_ops($nettotal, $netpl, '+');
Accountlist::add_row_csv(array("Net Profit", convert_cur($netpl)));
}
Accountlist::add_row_csv(array("Total - Net Expenses", convert_cur($nettotal)));
/* Showing Net P/L : Incomes */
$nettotal = $net_income_total;
Accountlist::add_row_csv(array("Total Incomes", convert_cur($nettotal)));
if (float_ops($grosspl, 0, '>')) {
$nettotal = float_ops($nettotal, $grosspl, '+');
Accountlist::add_row_csv(array("Gross Profit B/F", convert_cur($grosspl)));
}
if (float_ops($netpl, 0, '>')) {
} else {
if (float_ops($netpl, 0, '<')) {
$nettotal = float_ops($nettotal, -$netpl, '+');
Accountlist::add_row_csv(array("Net Loss", convert_cur(-$netpl)));
}
}
Accountlist::add_row_csv(array("Total - Net Incomes", convert_cur($nettotal)));
$balancesheet = Accountlist::get_csv();
$this->load->helper('csv');
echo array_to_csv($balancesheet, "profitandloss.csv");
return;
}
return;
}
/**
* Retrieveing assess data rows for export function
* @param array $opt - list of options (using array instead list of arguments to prevent sending a lot of empty results)
* @return array
* @author Andre Korenev
*/
function getExportResults($opt = array(), $mode = 'export_primary', $line = array())
{
// Initial data
$default_options = array('chronicle' => FALSE, 'category' => 0, 'brand' => 0, 'summaryFilter' => FALSE, 'superFilter' => FALSE, 'offset' => 0, 'limit' => 'all', 'batch' => 0, 'batch2' => 0, 'searchColumn' => FALSE, 'searchFilter' => FALSE);
$opt = array_merge($default_options, $opt);
if ($mode != 'export_primary' && $line == array('url') || $mode == 'export_primary' && $line != array('url')) {
$mode = 'export_primary';
$line = array('url');
}
// Filters with unique action
$summedFilters = array(1, 2, 61, 62, 63, 64, 65);
// Not saved in filters_to_result table
// Checking if filter is 'Total SKUs'
$isTotalSkus = $opt['summaryFilter'] == 1 || in_array(1, $opt['summaryFilter']);
// Trying to get chronicle if we don`t have
if (empty($opt['chronicle'])) {
$this->load->model('combo_chronicle_model');
$opt['chronicle'] = $this->combo_chronicle_model->getLastChronicle($opt['batch'], 0, $opt['batch2']);
}
// Checking required fields
if (empty($opt['chronicle'])) {
return array();
}
if (!is_array($opt['chronicle'])) {
$opt['chronicle'] = array($opt['chronicle']);
}
// Getting list of all categories if category set and filter is not "Total SKUs"
if ($opt['category'] && is_numeric($opt['category']) && !$isTotalSkus) {
$this->load->model('product_category_model');
$opt['category'] = $this->product_category_model->getChildCategories($opt['category']);
// Checking if we have list of categories
if (!empty($opt['category'])) {
$this->db->where_in('a.category_id', $opt['category']);
}
}
// Checking brand value if brand set and filter is not "Total SKUs"
if ($opt['brand'] && is_numeric($opt['brand']) && !$isTotalSkus) {
$this->db->where_in('a.brand_id', $opt['brand']);
}
// Checking summaryFilter value(s)
if ($opt['summaryFilter']) {
if (!is_array($opt['summaryFilter'])) {
$opt['summaryFilter'] = array($opt['summaryFilter']);
}
// Deleting unneeded filters
$opt['summaryFilter'] = array_diff($opt['summaryFilter'], $summedFilters);
if (!empty($opt['summaryFilter'])) {
$this->db->where_in('f.filter_id', $opt['summaryFilter']);
}
}
// Do we need offset and limit
if ($opt['limit'] > 0 && $opt['limit'] != 'all') {
$this->db->limit($opt['limit'], $opt['offset']);
}
// Using DISTINCT for queries without filter - usually Total SKUs
if (!$opt['summaryFilter']) {
$this->db->distinct();
}
// Creating query
/*
Strange thing with distinct - there still can be identical URLs, in the same chronicle, but with different imported_data_parsed_id
$this->db->distinct();
*/
$this->db->join($this->getTableName() . ' a', 'a.imported_data_id = f.imported_data_id');
if ($mode == 'export_primary') {
$this->db->select('idp.url');
$this->db->join($this->getTableName('imported_data_parsed_new') . ' idp', 'idp.imported_data_id = a.imported_data_id');
} else {
$this->db->select('a.row_data');
}
// If superFilter exists
if ($opt['superFilter']) {
$this->db->join($this->getTableName('filters') . ' f1', 'f1.imported_data_id = a.imported_data_id');
$this->db->where_in('f1.chronicle_id', $opt['chronicle']);
$this->db->where('f1.filter_id', $opt['superFilter']);
}
// If searchFilter exists and not empty
if ($opt['searchFilter'] && !empty($opt['searchFilter'])) {
switch ($opt['searchColumn']) {
case 'average_review':
case 'column_reviews':
case 'images_cmp':
case 'video_count':
case 'content_health_score_normalized':
case 'long_description_wc':
case 'short_description_wc':
case 'total_description_wc':
case "Meta_Description_Count":
case 'product_name_length':
//$this->db->where("a.row_data ~* '\"" . $opt['searchColumn'] . "\":([0-9]*)" . $opt['searchFilter'] . "([0-9]*),'");
$this->db->where("REPLACE(a.row_data,'\\','') ~* '\"" . $opt['searchColumn'] . "([0-9])?\":(\"{1,})?([0-9]*)" . $opt['searchFilter'] . "([0-9]*)(\"{1,})?,'");
break;
case "title_seo_phrases":
$this->db->where("REPLACE(regexp_replace(regexp_replace(a.row_data, '<[^>]+display[^>]+none[^>]+>[^>]+>', '', 'g'), '<.*?>', '', 'g'),'\\','') ~* '\"" . $type_column . "\":\"([^\"]*)" . $sSearch . "([^\"]*)\"'");
break;
case 'prodcat':
$this->db->join($this->getTableName('brands') . ' b', 'b.id = a.brand_id', 'left');
$this->db->join($this->getTableName('product_categories') . ' p', 'p.id = a.category_id', 'left');
$this->db->where('"p"."category_name" ILIKE \'%' . $opt['searchFilter'] . '%\'');
break;
case 'brand':
$this->db->join($this->getTableName('brands') . ' b', 'b.id = a.brand_id', 'left');
$this->db->join($this->getTableName('product_categories') . ' p', 'p.id = a.category_id', 'left');
$this->db->where('"b"."name" ILIKE \'%' . $opt['searchFilter'] . '%\'');
break;
case 'price_diff':
if (strstr($opt['searchFilter'], '.')) {
$this->db->where("REPLACE(a.row_data,'\\','') ~* '\"own_price\":(\"{1,})?" . $opt['searchFilter'] . "(.*)?(\"{1,})?,'");
} else {
$this->db->where("REPLACE(a.row_data,'\\','') ~* '\"own_price\":(\"{1,})?" . $opt['searchFilter'] . "(\\..*)?(\"{1,})?,'");
}
break;
default:
$this->db->where("REPLACE(regexp_replace(a.row_data, '<.*?>', '', 'g'),'\\','') ~* '\"" . $opt['searchColumn'] . "\":\"([^\"]*)" . $opt['searchFilter'] . "([^\"]*)\"'");
}
//$this->db->where('a.row_data LIKE \'%"'.$opt['searchColumn'].'":"%'.$opt['searchFilter'].'%"%\'');
}
$this->db->where_in('f.chronicle_id', $opt['chronicle']);
$this->db->where_in('a.combination', $opt['chronicle']);
$query = $this->db->get($this->getTableName('filters') . ' f');
// Returning empty array if result empty
if ($query->num_rows() == 0) {
return array();
}
$this->load->helper('csv');
// Checking each row for lines
$result = '';
$count = 0;
if ($mode == 'export_primary') {
while ($val = pg_fetch_object($query->result_id)) {
$result .= $val->url . PHP_EOL;
$count++;
}
} else {
while ($val = pg_fetch_object($query->result_id)) {
// Trying to decode unstripped data
$row = json_decode(strip_tags($val->row_data), TRUE);
// If such operation wasn`t succesfull - trying to unstrip each part
if ($row === NULL) {
$row = array();
$row_parts = json_decode($val->row_data, TRUE);
// If decoded json is not array - skipping current row
if (!is_array($row_parts)) {
continue;
}
foreach ($row_parts as $part_key => &$part_val) {
// Decoding only needed parts
if (isset($line[$part_key])) {
$row[$part_key] = strip_tags($part_val);
}
}
unset($row_parts);
}
// Getting data for each line
$temp = array();
foreach ($line as $key => $line_value) {
if (isset($row[$key])) {
$temp[] = $row[$key];
}
unset($row_parts);
}
if ($temp) {
$result .= array_to_csv(array($temp));
$count++;
}
}
}
$query->free_result();
unset($query);
return array('data' => $result, 'count' => $count);
}
function index()
{
$this->load->model(array('payment_schedule_customer_model', 'receipt_model'));
$this->load->library('form_validation');
$this->load->model('project_model');
$this->load->helper('csv');
$this->load->helper('download');
$data['projects'] = $this->db->get('project')->result();
$this->form_validation->set_rules('project', 'Date', 'trim|required');
if ($this->form_validation->run() == FALSE) {
$data['dues'] = array();
$this->load->view('reports/sales_report', $data);
} else {
$dues = array();
$project_selected = $this->input->post('project');
$from_date_db = $this->input->post('from_date');
$from_date_db = date('Y-m-d', strtotime($from_date_db));
$to_date_db = $this->input->post('to_date');
$to_date_db = date('Y-m-d', strtotime($to_date_db));
$due_report = array();
$this->db->select('customer_file.id,customer_file.purchase_cost,contact.name,' . 'project.name as project,availability_chart_unit.unit_name as unit_name,' . 'contact.phone,contact.phone_2,contact.phone_3,contact.email,contact.email_2,' . 'customer_file.booking_date,customer_file.area,customer_file.rate,' . 'sales_region.name as sales_region, user.full_name as sales_person');
$this->db->from('customer_file');
$this->db->join('contact', 'customer_file.customer_id=contact.id');
$this->db->join('project', 'customer_file.project_id=project.id');
$this->db->join('availability_chart_unit', 'availability_chart_unit.id=customer_file.unit_id');
$this->db->join('sales_region', 'sales_region.id=customer_file.sales_region', 'left');
$this->db->join('user', 'user.id=customer_file.sales_person', 'left');
$this->db->where('customer_file.project_id', $project_selected);
$this->db->where('customer_file.booking_date >=', $from_date_db);
$this->db->where('customer_file.booking_date <=', $to_date_db);
$customers = $this->db->get()->result();
foreach ($customers as $customer) {
$due_report[$customer->id] = array();
$due_report[$customer->id]['client_name'] = $customer->name;
$due_report[$customer->id]['phone'] = implode(array_filter(array($customer->phone, $customer->phone_2, $customer->phone_3)), ', ');
$due_report[$customer->id]['email'] = implode(array_filter(array($customer->email, $customer->email_2)), ', ');
$due_report[$customer->id]['project'] = $customer->project . '-' . $customer->unit_name;
$due_report[$customer->id]['purchase_cost'] = $customer->purchase_cost;
$due_report[$customer->id]['booking_date'] = $customer->booking_date;
$due_report[$customer->id]['area'] = $customer->area;
$due_report[$customer->id]['rate'] = $customer->rate;
$due_report[$customer->id]['sales_region'] = $customer->sales_region;
$due_report[$customer->id]['sales_person'] = $customer->sales_person;
$due_report[$customer->id]['total_tax'] = 0;
$due_report[$customer->id]['due_amount'] = 0;
$due_report[$customer->id]['total_pri'] = 0;
$due_report[$customer->id]['total_tx'] = 0;
$due_report[$customer->id]['total_amt'] = 0;
$due_report[$customer->id]['total_units'] = $this->db->where('project_id', $customer->id)->count_all_results('customer_file');
$customer_file_id = $customer->id;
$actual_payment_schedule = $this->payment_schedule_customer_model->list_schedule_customer_file_actual_active($customer_file_id);
$additional_payment_schedule = $this->payment_schedule_customer_model->list_schedule_customer_file_additional_active($customer_file_id);
$receipts = $this->receipt_model->list_cleared_receipt_customer_file($customer_file_id);
$this->db->select('tax_type.name,tax_master.id');
$this->db->from('tax_type');
$this->db->join('tax_master', 'tax_master.tax_type_id=tax_type.id');
$this->db->join('customer_file_tax', 'customer_file_tax.tax_id=tax_master.id');
$this->db->where('customer_file_tax.customer_file_id', $customer_file_id);
$customer_tax = $this->db->get()->result();
$total_due_amount = 0;
// Actual Due
foreach ($actual_payment_schedule as $schedule) {
$total_due_amount += $schedule->due_amount;
$ttax = 0;
$ttax_bal = 0;
foreach ($customer_tax as $cs2) {
$tax_amount = 0;
$tax_amount_bal = 0;
if ($schedule->status == 1) {
if ($schedule->due_amount - $schedule->amount_paid == 0) {
$this->db->select('SUM(receipt_split_up_tax.tax_amount) as tax_amount');
$this->db->from('receipt_split_up_tax');
$this->db->join('receipt_split_up', 'receipt_split_up.id=receipt_split_up_tax.receipt_split_up_id');
$this->db->where(array('receipt_split_up.installment_id' => $schedule->id, 'receipt_split_up_tax.tax_id' => $cs2->id));
$applicale_tax = $this->db->get()->first_row();
if ($applicale_tax->tax_amount) {
$ttax += $applicale_tax->tax_amount;
}
} elseif ($schedule->amount_paid == 0) {
$this->db->select('tax_slab.tax_percentage as tax_percentage');
$this->db->from('tax_slab');
$this->db->join('tax_slab_cost_head', 'tax_slab.id=tax_slab_cost_head.tax_slab_id');
$this->db->where(array('tax_master_id' => $cs2->id, 'from_date <=' => $to_date_db, 'to_date >=' => $to_date_db, 'tax_slab_cost_head.cost_head_id' => $schedule->payment_type));
$applicale_tax = $this->db->get()->first_row();
if (!empty($applicale_tax->tax_percentage)) {
$tax_amount = $schedule->due_amount / 100 * $applicale_tax->tax_percentage;
$ttax += $tax_amount;
}
} else {
// tax on paid amount start
$this->db->select('SUM(receipt_split_up_tax.tax_amount) as tax_amount');
$this->db->from('receipt_split_up_tax');
$this->db->join('receipt_split_up', 'receipt_split_up.id=receipt_split_up_tax.receipt_split_up_id');
$this->db->where(array('receipt_split_up.installment_id' => $schedule->id, 'receipt_split_up_tax.tax_id' => $cs2->id));
$applicale_tax_paid = $this->db->get()->first_row();
// tax on paid amount end
//tax on balance amount start
$this->db->select('tax_slab.tax_percentage as tax_percentage');
$this->db->from('tax_slab');
$this->db->join('tax_slab_cost_head', 'tax_slab.id=tax_slab_cost_head.tax_slab_id');
$this->db->where(array('tax_master_id' => $cs2->id, 'from_date <=' => $to_date_db, 'to_date >=' => $to_date_db, 'tax_slab_cost_head.cost_head_id' => $schedule->payment_type));
$tax_percentage_balance = $this->db->get()->first_row();
//tax on balance amount end
$tax_amount_balance = ($schedule->due_amount - $schedule->amount_paid) / 100 * $tax_percentage_balance->tax_percentage;
$applicale_tax_amount = $applicale_tax_paid->tax_amount + $tax_amount_balance;
$ttax += $applicale_tax_amount;
}
}
}
$due_report[$customer->id]['total_tax'] += $ttax;
$due_report[$customer->id]['due_amount'] += $schedule->due_amount;
}
// Additional Due
foreach ($additional_payment_schedule as $schedule_additional) {
$total_due_amount_additional += $schedule_additional->due_amount;
$ttax_additional = 0;
$ttax_bal = 0;
foreach ($customer_tax as $cs2) {
$tax_amount_add = 0;
$tax_amount_bal_add = 0;
if ($schedule_additional->status == 1) {
if ($schedule_additional->due_amount - $schedule_additional->amount_paid == 0) {
$this->db->select('SUM(receipt_split_up_tax.tax_amount) as tax_amount');
$this->db->from('receipt_split_up_tax');
$this->db->join('receipt_split_up', 'receipt_split_up.id=receipt_split_up_tax.receipt_split_up_id');
$this->db->where(array('receipt_split_up.installment_id' => $schedule_additional->id, 'receipt_split_up_tax.tax_id' => $cs2->id));
$applicale_tax_add = $this->db->get()->first_row();
if ($applicale_tax_add->tax_amount) {
$ttax_additional += $applicale_tax_add->tax_amount;
}
} elseif ($schedule_additional->amount_paid == 0) {
$this->db->select('tax_slab.tax_percentage as tax_percentage');
$this->db->from('tax_slab');
$this->db->join('tax_slab_cost_head', 'tax_slab.id=tax_slab_cost_head.tax_slab_id');
$this->db->where(array('tax_master_id' => $cs2->id, 'from_date <=' => $to_date_db, 'to_date >=' => $to_date_db, 'tax_slab_cost_head.cost_head_id' => $schedule_additional->payment_type));
$applicale_tax_add = $this->db->get()->first_row();
if (!empty($applicale_tax_add->tax_percentage)) {
$tax_amount_add = $schedule_additional->due_amount / 100 * $applicale_tax_add->tax_percentage;
$ttax_additional += $tax_amount_add;
}
} else {
// tax on paid amount start
$this->db->select('SUM(receipt_split_up_tax.tax_amount) as tax_amount');
$this->db->from('receipt_split_up_tax');
$this->db->join('receipt_split_up', 'receipt_split_up.id=receipt_split_up_tax.receipt_split_up_id');
$this->db->where(array('receipt_split_up.installment_id' => $schedule_additional->id, 'receipt_split_up_tax.tax_id' => $cs2->id));
$applicale_tax_paid_bal = $this->db->get()->first_row();
// tax on paid amount end
//tax on balance amount start
$this->db->select('tax_slab.tax_percentage as tax_percentage');
$this->db->from('tax_slab');
$this->db->join('tax_slab_cost_head', 'tax_slab.id=tax_slab_cost_head.tax_slab_id');
$this->db->where(array('tax_master_id' => $cs2->id, 'from_date <=' => $to_date_db, 'to_date >=' => $to_date_db, 'tax_slab_cost_head.cost_head_id' => $schedule_additional->payment_type));
$tax_percentage_balance = $this->db->get()->first_row();
//tax on balance amount end
$tax_amount_balance_add = ($schedule_additional->due_amount - $schedule_additional->amount_paid) / 100 * $tax_percentage_balance->tax_percentage;
$applicale_tax_amount_add = $applicale_tax_paid_bal->tax_amount + $tax_amount_balance_add;
$ttax_additional += $applicale_tax_amount_add;
}
}
}
$due_report[$customer->id]['total_tax_additional'] += $ttax_additional;
$due_report[$customer->id]['due_amount_additional'] += $schedule_additional->due_amount;
}
//Receipts
foreach ($receipts as $b => $receipt) {
$due_report[$customer->id]['total_pri'] += $receipt->principle_amount;
$due_report[$customer->id]['total_tx'] += $receipt->amount_paid - $receipt->principle_amount;
$due_report[$customer->id]['total_amt'] += $receipt->amount_paid;
}
}
//print_r($due_report);
//exit();
if (isset($_POST['generate_pdf'])) {
$this->load->library('Pdf');
$this->db->select('company.name,project.project_type');
$this->db->from('company');
$this->db->join('project', 'project.company_id=company.id');
$this->db->join('customer_file', 'customer_file.project_id=project.id');
$company_details = $this->db->get()->first_row();
$pdf = new TCPDF('P', PDF_UNIT, 'A4', true, 'UTF-8', false);
$pdf->SetCreator(PDF_CREATOR);
$pdf->SetTitle('Receipt');
//$pdf->SetHeaderData(PDF_HEADER_LOGO, PDF_HEADER_LOGO_WIDTH, '', '', array(0, 64, 255), array(255, 255, 255));
$pdf->SetPrintFooter(false);
$pdf->setPrintHeader(false);
//$pdf->setHeaderFont(Array(PDF_FONT_NAME_MAIN, '', PDF_FONT_SIZE_MAIN));
//$pdf->setFooterFont(Array(PDF_FONT_NAME_DATA, '', PDF_FONT_SIZE_DATA));
$pdf->SetDefaultMonospacedFont(PDF_FONT_MONOSPACED);
$pdf->SetMargins(PDF_MARGIN_LEFT, PDF_MARGIN_TOP, PDF_MARGIN_RIGHT);
//$pdf->SetHeaderMargin(PDF_MARGIN_HEADER);
//$pdf->SetFooterMargin(PDF_MARGIN_FOOTER);
$pdf->SetAutoPageBreak(TRUE, PDF_MARGIN_BOTTOM);
$pdf->setImageScale(PDF_IMAGE_SCALE_RATIO);
if (@file_exists(dirname(__FILE__) . '/lang/eng.php')) {
require_once dirname(__FILE__) . '/lang/eng.php';
$pdf->setLanguageArray($l);
}
$pdf->setFontSubsetting(true);
$pdf->SetFont('dejavusans', '', 7, '', true);
$pdf->AddPage();
$pdf->writeHTMLCell(211, 17, '0', 10, '<h2 style="text-align:center;">' . $company_details->name . '</h2>', 0, 1, 0, true, '', true);
$style = array('width' => 0.1, 'color' => array(0, 0, 0));
$pdf->Line(0, 25, 220, 25, $style);
$pdf->writeHTMLCell(0, 0, '', '', '<h2 style="text-align:center;">Sales Report</h2>', 0, 1, 0, true, '', true);
$style = array('width' => 0.1, 'color' => array(0, 0, 0));
$pdf->Line(0, 35, 220, 35, $style);
$pdf->writeHTMLCell(50, 0, 10, 40, '<strong style="text-align:center;">Period : ' . date('M Y', strtotime($from_date_db)) . ' - ' . date('M Y', strtotime($to_date_db)) . '</strong>', 0, 1, 0, true, '', true);
$pdf->writeHTMLCell(35, 0, 168, 40, '<strong style="text-align:center;">Date : ' . date('d-m-Y') . '</strong>', 0, 1, 0, true, '', true);
$html .= '<tr><td></td><td><td/></tr></table><br /><br /><br /><br />';
$html .= '<table border="1" cellpadding="5" cellspacing = "0" width = "100%" nobr="true"><thead><tr>';
$html .= '<th width="30">No.</th>';
$html .= '<th width="65">Booking Date</th>';
$html .= '<th width="75">Client Name</th>';
$html .= '<th width="60">Project</th>';
$html .= '<th width="40">Rate</th>';
$html .= '<th width="65">Agreement Value</th>';
$html .= '<th width="65">Due Amount</th>';
$html .= '<th width="65">Total Collection</th>';
$html .= '<th width="65">Balance Due</th>';
$html .= '<th width="60">Sales Region</th>';
$html .= '<th width="60">Sales Person</th>';
$html .= '</tr>';
$html .= '</thead><tbody>';
$total_sale_amount = 0;
$total_due_amount = 0;
$total_due_amount_additional = 0;
$total_due_tax = 0;
$total_due_tax_additional = 0;
$total_collection = 0;
$total_balance = 0;
foreach ($due_report as $due) {
$total_sale_amount += $due['purchase_cost'];
$total_due_amount += $due['due_amount'];
$total_due_amount_additional += $due['due_amount_additional'];
$total_due_tax += $due['total_tax'];
$total_due_tax_additional += $due['total_tax_additional'];
$total_collection += $due['total_amt'];
$total_balance += $due['due_amount'] + $due['total_tax'] + $due['due_amount_additional'] + $due['total_tax_additional'] - $due['total_amt'];
$html .= '<tr>';
$html .= '<td width="30">' . ($n + 1) . '</td>';
$html .= '<td width="65">' . date('d-m-Y', strtotime($due['booking_date'])) . '</td>';
$html .= '<td width="75">' . $due['client_name'] . '</td>';
$html .= '<td width="60">' . $due['project'] . '</td>';
$html .= ' <td width="40">' . number_format($due['rate']) . '</td>';
$html .= ' <td width="65">' . number_format($due['purchase_cost']) . '</td>';
$html .= ' <td width="65">' . number_format($due['due_amount_additional'] + $due['total_tax_additional'] + ($due['due_amount'] + $due['total_tax'])) . '</td>';
$html .= ' <td width="65">' . number_format($due['total_amt']) . '</td>';
$html .= ' <td width="65">' . number_format($due['due_amount'] + $due['total_tax'] + $due['due_amount_additional'] + $due['total_tax_additional'] - $due['total_amt']) . '</td>';
$html .= '<td width="60">' . $due['sales_region'] . '</td>';
$html .= '<td width="60">' . $due['sales_person'] . '</td>';
$html .= '</tr>';
$n++;
}
$html .= '</tbody>';
$html .= '<tfoot>';
$html .= '<tr style="font-weight: bolder">';
$html .= '<td colspan="5"></td>';
$html .= '<td>' . number_format($purchase_cost) . '</td>';
$html .= '<td>' . number_format($due_amount + $due_amount_additional + $total_tax + $total_tax_additional) . '</td>';
$html .= '<td>' . number_format($total_amt_collected) . '</td>';
$html .= '<td>' . number_format($due_amount + $due_amount_additional + $total_tax + $total_tax_additional - $total_amt_collected) . '</td>';
$html .= '<td></td>';
$html .= '<td></td>';
$html .= '</tr>';
$html .= '</tfoot>';
$html .= '</table><br /><br />';
//echo $html;
//exit();
$pdf->writeHTMLCell(0, 0, '', '', $html, 0, 1, 0, true, '', true);
$pdf->Output('receipt.pdf', 'I');
} elseif (isset($_POST['generate_csv'])) {
$csv_array = array();
$csv_array_line = array();
$csv_array_line[] = 'No.';
$csv_array_line[] = 'Date of Booking';
$csv_array_line[] = 'Client Name';
$csv_array_line[] = 'Contact Number';
$csv_array_line[] = 'Email';
$csv_array_line[] = 'Area';
$csv_array_line[] = 'Rate';
$csv_array_line[] = 'Project';
$csv_array_line[] = 'Sale Value';
$csv_array_line[] = 'Prin Amt Due';
$csv_array_line[] = 'Tax Amt Due';
$csv_array_line[] = 'Additional Due';
$csv_array_line[] = 'Additional Due Tax';
$csv_array_line[] = 'Total Due';
$csv_array_line[] = 'Total Collection';
$csv_array_line[] = 'Balance Due';
$csv_array_line[] = 'Sales Region';
$csv_array_line[] = 'Sales Person';
$csv_array[] = $csv_array_line;
foreach ($due_report as $due) {
$csv_array_line = array();
$csv_array_line[] = $n + 1;
$csv_array_line[] = date('d-m-Y', strtotime($due['booking_date']));
$csv_array_line[] = $due['client_name'];
$csv_array_line[] = $due['phone'];
$csv_array_line[] = $due['email'];
$csv_array_line[] = $due['area'];
$csv_array_line[] = $due['rate'];
$csv_array_line[] = $due['project'];
$csv_array_line[] = $due['due_amount'] + $due['due_amount_additional'];
$csv_array_line[] = $due['due_amount'];
$csv_array_line[] = $due['total_tax'];
$csv_array_line[] = $due['due_amount_additional'];
$csv_array_line[] = $due['total_tax_additional'];
$csv_array_line[] = $due['due_amount_additional'] + $due['total_tax_additional'] + ($due['due_amount'] + $due['total_tax']);
$csv_array_line[] = $due['total_amt'];
$csv_array_line[] = $due['due_amount'] + $due['total_tax'] + $due['due_amount_additional'] + $due['total_tax_additional'] - $due['total_amt'];
$csv_array_line[] = $due['sales_region'];
$csv_array_line[] = $due['sales_person'];
$csv_array[] = $csv_array_line;
}
echo array_to_csv($csv_array, 'due_letter.csv');
exit;
} else {
$data['due_report'] = $due_report;
$data['from_date_selected'] = $this->input->post('from_date');
$data['to_date_selected'] = $this->input->post('to_date');
$data['project_selected'] = $this->input->post('project');
$this->load->view('reports/sales_report', $data);
}
}
}
public function export_action($questionnaire_id)
{
$this->questionnaire = new Questionnaire($questionnaire_id);
if (!$this->questionnaire->isEditable()) {
throw new AccessDeniedException("Der Fragebogen ist nicht exportierbar.");
}
$csv = array(array(_("Nummer"), _("Benutzername"), _("Nachname"), _("Vorname"), _("Email")));
$results = array();
$user_ids = array();
foreach ($this->questionnaire->questions as $question) {
$result = (array) $question->getResultArray();
foreach ($result as $frage => $r) {
$csv[0][] = $frage;
$user_ids = array_merge($user_ids, array_keys($r));
$user_ids = array_unique($user_ids);
}
$results[] = $result;
}
foreach ($user_ids as $key => $user_id) {
$user = new User($user_id);
$csv_line = array($key + 1, $user['username'], $user['Nachname'], $user['Vorname'], $user['Email']);
foreach ($results as $result) {
foreach ($result as $frage => $value) {
$csv_line[] = $value[$user_id];
}
}
$csv[] = $csv_line;
}
$this->response->add_header('Content-Type', "text/csv");
$this->response->add_header('Content-Disposition', "attachment; filename=" . $this->questionnaire['title'] . ".csv");
$this->render_text(array_to_csv($csv));
}
/**
* Export list to CSV
*/
function export()
{
// get parameters
$sort = $this->input->get('sort') ? $this->input->get('sort', TRUE) : DEFAULT_SORT;
$dir = $this->input->get('dir') ? $this->input->get('dir', TRUE) : DEFAULT_DIR;
// get filters
$filters = array();
if ($this->input->get('username')) {
$filters['username'] = $this->input->get('username', TRUE);
}
if ($this->input->get('first_name')) {
$filters['first_name'] = $this->input->get('first_name', TRUE);
}
if ($this->input->get('last_name')) {
$filters['last_name'] = $this->input->get('last_name', TRUE);
}
// get all users
$users = $this->users_model->get_all(0, 0, $filters, $sort, $dir);
if ($users['total'] > 0) {
// manipulate the output array
foreach ($users['results'] as $key => $user) {
unset($users['results'][$key]['password']);
unset($users['results'][$key]['deleted']);
if ($user['status'] == 0) {
$users['results'][$key]['status'] = lang('admin input inactive');
} else {
$users['results'][$key]['status'] = lang('admin input active');
}
}
// export the file
array_to_csv($users['results'], "users");
} else {
// nothing to export
$this->session->set_flashdata('error', lang('core error no_results'));
redirect($this->_redirect_url);
}
exit;
}
public function export()
{
$this->load->helper('csv');
$params = array();
$data['member'] = $this->Member_model->get($params);
$csv = array(0 => array('No.', 'NIPM', 'Nama', 'Jenis Kelamin', 'Tempat Lahir', 'Tanggal Lahir', 'Asal Sekolah', 'Pembimbing', 'No Telp', 'Alamat', 'Departement', 'Status', 'Mulai Prakerin'));
$i = 1;
foreach ($data['member'] as $row) {
$csv[] = array($i, $row['member_nip'], $row['member_full_name'], $row['member_sex'] == 'MALE' ? 'Laki-laki' : 'Perempuan', $row['member_birth_place'], $row['member_birth_date'], $row['member_school'], $row['member_mentor'], $row['member_phone'], $row['member_address'], $row['member_division'], $row['member_status'] == 0 ? 'Non-Aktif' : 'Aktif', pretty_date($row['member_entry_date'], 'm/d/Y', FALSE));
$i++;
}
// echo "<pre>";
// echo print_r($csv);
// echo "</pre>";
// die();
array_to_csv($csv, 'Data_Peserta_Prakerin.csv');
}
public function download_questionnaire($id, $format = 'html')
{
$questionnaire = new Questionnaire();
$questionnaire->get_by_id((int) $id);
if (!$questionnaire->exists()) {
add_error_flash_message('Dotazník sa nenašiel.');
redirect('questionnaires');
}
$questionnaire_answer_max = new Questionnaire_answer();
$questionnaire_answer_max->select_func('MAX', '@answer_number', 'max_answer_number');
$questionnaire_answer_max->where('${parent}.id', 'questionnaire_answers_subquery.id', false);
$questionnaire_answers = new Questionnaire_answer();
$questionnaire_answers->where_related($questionnaire);
$questionnaire_answers->where_subquery('answer_number', $questionnaire_answer_max);
$questionnaire_answers->include_related('person', array('name', 'surname', 'admin'));
$questionnaire_answers->order_by_related('person', 'admin', 'asc');
$questionnaire_answers->order_by_related('person', 'surname', 'asc');
$questionnaire_answers->order_by_related('person', 'name', 'asc');
$questionnaire_answers->get_iterated();
if ($format == 'html') {
$this->parser->parse('web/controllers/questionnaires/download_questionnaire.tpl', array('questionnaire' => $questionnaire, 'questionnaire_answers' => $questionnaire_answers));
} else {
$this->load->helper('general');
$csv = array();
$build_questions_line = true;
$questions_line = array('');
foreach ($questionnaire_answers as $questionnaire_answer) {
$resolved = $questionnaire->resolve_answers(unserialize($questionnaire_answer->answers));
if (count($resolved) > 0) {
$person = array($questionnaire_answer->person_name . ' ' . $questionnaire_answer->person_surname);
foreach ($resolved as $one_resolv) {
if ($build_questions_line) {
$questions_line[] = br2nl(strip_tags($one_resolv['question']));
}
$person[] = br2nl(strip_tags($one_resolv['answer']));
}
if ($build_questions_line) {
$csv[] = $questions_line;
}
$csv[] = $person;
}
$build_questions_line = false;
}
header('content-disposition: attachement; filename=vysledky.csv');
echo array_to_csv($csv);
}
}
public function createcsv()
{
$temp_dic = $this->feeder->getdic($this->session->userdata('token'), $this->tabel);
$dumy_dic = $temp_dic['result'];
//var_dump($dumy_dic);
$array = array();
$header_mhs = array();
foreach ($dumy_dic as $key) {
$header_mhs[] = $key['column_name'];
}
$array[] = $header_mhs;
//var_dump($array);
//echo $this->input->post('prodi')."<br />".$this->input->post('separasi');
$id_sp = $this->session->userdata('id_sp');
$id_sms = $this->input->post('prodi');
$separasi = $this->input->post('separasi');
$sample = array('ID PD / Hapus kolom ini', 'Nama Lengkap Mahasiswa sesuai KTP/Tanda Pengenal Lainnya - Wajib diisi', 'Jenis Kelamin (L: Laki-laki, P: Perempuan, *: Belum ada informasi) - Wajib diisi', 'NISN (Nomor Induk Siswa Nasional) / Hapus kolom ini jika tidak ada', 'NIK (Nomor Induk Kependudukan) / Hapus kolom ini jika tidak ada', 'Tempat Lahir / Hapus kolom ini jika tidak ada', 'Tanggal Lahir (Format: yyyy-mm-dd / contoh: 2000-12-30) - Wajib diisi', 'ID Agama (1: Islam, 2: Kristen, 3: Katholik, 4: Hindu, 5: Budha, 6: Konghucu, 98: Tidak diisi, 99: Lainnya) - Wajib diisi', 'ID KK / Isikan angka 0 - Wajib diisi', $id_sp, 'Nama Jalan alamat tinggal / Hapus kolom ini jika tidak ada', 'RT alamat tinggal / Hapus kolom ini jika tidak ada', 'RW alamat tinggal / Hapus kolom ini jika tidak ada', 'Nama Dusun / Hapus kolom ini jika tidak ada', 'Nama Desa atau Keluarahan - Wajib diisi', 'ID WIlayah/Propinsi (Lihat tabel wilayah - Wajib diisi)', 'Kode Pos Alamat Tinggal / Hapus kolom ini jika tidak ada', 'ID Jenis Tinggal / Isikan angka 0 atau Hapus kolom ini jika tidak ada', 'ID Alat Transport / Isikan angka 0 Hapus kolom ini jika tidak ada', 'Nomor Telepon Rumah / Hapus kolom ini jika tidak ada', 'Nomor Telepon Seluler / Hapus kolom ini jika tidak ada', 'Alamat email / Hapus kolom ini jika tidak ada', 'Kartu Perlindungan Sosial (0: Bukan penerima KPS, 1: Penerima KPS) - Wajib diisi', 'Nomor KPS / Isikan jika penerima KPS, Hapus kolom ini jika bukan penerima KPS', 'Status Mahasiswa (A: Aktif, C: Cuti, D: Drop-out/Putus Studi, L: Lulus, P: , K: Keluar, N: Non Aktif, G: Sedang Double Degree, X: Unknown) - Wajib diisi', 'Nama Ayah / Hapus kolom ini jika tidak ada', 'Tanggal Lahir Ayah (yyy-mm-dd) / Hapus kolom ini jika tidak ada', 'ID Jenjang Pendidikan Ayah / Hapus kolom ini jika tidak ada', 'ID Pekerjaan Ayah / Hapus kolom ini jika tidak ada', 'ID Penghasilan Ayah / Hapus kolom ini jika tidak ada', 'ID Kebutuhan Khusus Ayah (Isikan angka 0) - Wajib diisi', 'Nama Ibu Kandung - Wajib diisi', 'Tanggal Lahir Ibu (yyy-mm-dd) / Hapus kolom ini jika tidak ada', 'ID Jenjang Pendidikan Ibu / Hapus kolom ini jika tidak ada', 'ID Penghasilan Ibu / Hapus kolom ini jika tidak ada', 'ID Pekerjaan Ibu / Hapus kolom ini jika tidak ada', 'ID Kebutuhan Khusus Ibu (Isikan angka 0) - Wajib diisi', 'Nama Wali / Hapus kolom ini jika tidak ada', 'Tanggal Lahir Wali (yyyy-mm-dd) / Hapus kolom ini jika tidak ada', 'ID Pendidikan Wali / Hapus kolom ini jika tidak ada', 'ID Pekerjaan Wali / Hapus kolom ini jika tidak ada', 'ID Penghasilan Wali / Hapus kolom ini jika tidak ada', 'Kewarganegaraan (ID: Indonesia) - Wajib diisi', 'ID Registrasi Mahasiswa - Hapus kolom ini', $id_sms, 'ID PD - Hapus kolom ini', $id_sp, 'ID Jenis Daftar (2: Pindahan, 3: Naik kelas, 4: Akselerasi, 5: Mengulang, 6: Lanjutan semester, 9: Putus Sekolah, 0: Lainnya, 1: Peserta didik baru, 8: Pindahan Alih Bentuk) - Wajib diisi', 'NIPD (Nomor Induk Mahasiswa) - Wajib diisi', 'Tanggal Masuk Mahasiswa (yyyy-mm-dd / Contoh: 2000-12-30) - Wajib diisi', 'ID Jenis Keluar (1: Lulus, 2: Mutasi, 3: Dikeluarkan, 4: Mengundurkan diri, 5: Putus Sekolah, 6: Wafat, 7: Hilang, 8: Alih Fungsi, 9: Pensiun, Z: Lainnya)/ Hapus kolom ini jika mahasiswa aktif', 'Tanggal Keluar Mahasiswa (yyyy-mm-dd / Contoh: 2000-12-30) / Hapus kolom ini jika mahasiswa masih aktif', 'Keterangan Mahasiswa / Hapus kolom ini jika tidak ada', 'Surat Keterangan Hasil Ujian Nasional (SKHUN) / Hapus kolom ini jika tidak ada', 'Mahasiswa Pernah PAUD (0: Tidak pernah PAUD, 1: Pernah PAUD) / Hapus kolom ini jika tidak ada', 'Mahasiswa Pernah TK (0: Tidak pernah TK, 1: Pernah TK) / Hapus kolom ini jika tidak ada', 'Awal Semester Mahasiswa (ex. 20121) - Wajib diisi', 'SKS Diakui (Jika Status Mahasiswa Pindahan) / Hapus kolom ini jika status masuk mahasiswa baru', 'Jalur Keluar Mahasiswa / Hapus kolom ini jika mahasiswa masih aktif, dan isikan 0 jika mahasiswa sudah Lulus', 'Judul Skripsi Mahasiswa / Hapus kolom ini jika mahasiswa masih aktif', 'Tanggal/Bulan Awal Bimbingan Skripsi (yyyy-mm-dd) / Hapus kolom ini jika mahasiswa belum skripsi', 'Tanggal/Bulan Akhir Bimbingan Skripsi (yyyy-mm-dd) / Hapus kolom ini jika mahasiswa belum skripsi', 'Nomor SK Yudisium (yyyy-mm-dd) / Hapus kolom ini jika mahasiswa masih aktif', 'Tanggal SK Yudisium / Hapus kolom ini jika mahasiswa masih aktif', 'Indeks Prestasi Kumulatif / Hapus kolom ini jika mahasiswa masih aktif', 'Nomor Seri Ijazah / Hapus kolom ini jika mahasiswa masih aktif', 'Nomor Sertifikat Profesional / Hapus kolom ini jika tidak ada', 'Mahasiswa Asing Pindahan / Hapus kolom ini jika tidak ada', 'Nama PT Asal / Diisi jika status mahasiswa pindahan, Hapus kolom ini jika status mahasiswa baru', 'Nama PRODI Asal / Diisi jika status mahasiswa pindahan, Hapus kolom ini jika status mahasiswa baru');
$array[] = $sample;
$time = time();
write_file('temps/' . $time . '_mahasiswa.csv', array_to_csv($array, '', $separasi));
//echo "File berhasil digenerate. <a href=\"".base_url()."temps/".$time."_mahasiswa.csv\">Download</a>";
echo "<div class=\"bs-callout bs-callout-success\">\n File berhasil digenerate. <a href=\"" . base_url() . "temps/" . $time . "_mahasiswa.csv\">Download</a>\n </div>";
}
function createCSV()
{
$doctorSpecialities_specialitiesId = '';
if (isset($_POST['doctorSpecialities_specialitiesId'])) {
$doctorSpecialities_specialitiesId = $this->input->post('doctorSpecialities_specialitiesId');
}
// $where=array('doctors_deleted'=> 0,'doctorSpecialities_specialitiesId'=> $doctorSpecialities_specialitiesId);
$where = '';
$array[] = array('Image Name', 'Doctor Name', 'Doctor ID', 'Address', 'Speciality', 'Exprience', 'Date Of Joining', 'Phone Number', 'Mobile Number');
$data = $this->Doctor_model->createCSVdata($where);
$arrayFinal = array_merge($array, $data);
array_to_csv($arrayFinal, 'DoctorDetail.csv');
return True;
exit;
}
public function exportPage1ShareOfShelf()
{
$this->load->model('ranking_model');
$brandIds = $this->input->get('brands');
$termIds = $this->input->get('searchTerms');
$siteIds = $this->input->get('sites');
$dates = $this->input->get('dates');
$plistIds = is_array($this->input->get('productList')) && $this->input->get('productList') ? $this->input->get('productList') : null;
$brands = $this->ranking_model->getBrandByIdsArray($brandIds);
$terms = $this->ranking_model->getTermsByIds($termIds);
$groupIds = [];
foreach ($terms as $term) {
$groupIds[] = $term->group_id;
}
$groupIds = array_unique($groupIds);
$dataRaw = ['curr_group_terms_count' => 0, 'all_results_for_body' => array('brands' => array(), 'terms' => array(), 'brand_terms' => array(), 'search_terms_list' => array())];
foreach ($groupIds as $groupId) {
$this->populateShareOfShelfData($dataRaw, $siteIds, $dates, $terms, $brandIds, $plistIds);
}
$data = $dataRaw['all_results_for_body'];
$header = ['Search Terms'];
$avgData = ['Page 1 share of shelf'];
$maxVal = $data['serch_terms_ofp']['summary_ofp_count'];
$totalPercentage = 100;
foreach ($brands as $brand) {
$header[] = $brand->name;
$val = isset($data['brands'][$brand->name]) ? $data['brands'][$brand->name] : 0;
$brandPercentage = round($val / $maxVal * 100);
$totalPercentage -= $brandPercentage;
$avgData[] = round($val / $maxVal * 100) . '%';
}
$header[] = 'Other';
$avgData[] = $totalPercentage . '%';
$termDetails = [];
foreach ($terms as $term) {
$row = [$term->title];
foreach ($brands as $brand) {
$row[] = isset($data['brand_terms'][$term->title . '_' . $brand->name]) ? $data['brand_terms'][$term->title . '_' . $brand->name] : 0;
}
$others = intval($data['serch_terms_ofp'][$term->title]);
if (isset($data['terms'][$term->title])) {
$others -= $data['terms'][$term->title];
}
$row[] = $others;
$termDetails[] = $row;
}
$outputData = [$header, $avgData];
foreach ($termDetails as $termItem) {
$outputData[] = $termItem;
}
$csvFormatted = array_to_csv($outputData);
$fileName = 'Page1ShareOfShelf- ' . date('Y-m-d') . '.csv';
$this->outputCsv($csvFormatted, $fileName);
}
function customer_aging_report()
{
$this->load->model(array('payment_schedule_customer_model', 'receipt_model', 'project_model'));
$this->load->library('form_validation');
$this->load->helper(array('csv', 'download'));
$data['projects'] = $this->db->get('project')->result();
$this->form_validation->set_rules('due_date', 'Date', 'trim|required');
if ($this->form_validation->run() == FALSE) {
$data['dues'] = array();
$this->load->view('reports/customer_wise_aging', $data);
} else {
$dues = array();
$project_selected = $this->input->post('project');
$with_tax_selected = $this->input->post('with_tax');
$due_date_db = $this->input->post('due_date');
$due_date_selected = $due_date_db;
$due_date_db = date('Y-m-d', strtotime($due_date_db));
$due_report = array();
$this->db->select('customer_file.id,customer_file.purchase_cost,contact.name,project.name as project,availability_chart_unit.unit_name as unit_name');
$this->db->from('customer_file');
$this->db->join('contact', 'customer_file.customer_id=contact.id');
$this->db->join('project', 'customer_file.project_id=project.id');
$this->db->join('availability_chart_unit', 'availability_chart_unit.id=customer_file.unit_id');
$this->db->where('customer_file.project_id', $project_selected);
$this->db->where('customer_file.approval_status', 1);
$customers = $this->db->get()->result();
foreach ($customers as $customer) {
$due_report[$customer->id] = array();
$due_report[$customer->id]['client_name'] = $customer->name;
$due_report[$customer->id]['project'] = $customer->project . '-' . $customer->unit_name;
$due_report[$customer->id]['purchase_cost'] = $customer->purchase_cost;
$due_report[$customer->id]['total_tax'] = 0;
$due_report[$customer->id]['due_amount'][1] = 0;
$due_report[$customer->id]['due_amount'][2] = 0;
$due_report[$customer->id]['due_amount'][3] = 0;
$due_report[$customer->id]['due_amount'][4] = 0;
$due_report[$customer->id]['due_amount'][5] = 0;
$due_report[$customer->id]['due_amount'][6] = 0;
$due_report[$customer->id]['due_amount'][7] = 0;
$due_report[$customer->id]['total_due_amount'] = 0;
$due_report[$customer->id]['total_pri'] = 0;
$due_report[$customer->id]['total_tx'] = 0;
$due_report[$customer->id]['total_amt'] = 0;
$due_report[$customer->id]['total_units'] = $this->db->where('project_id', $customer->id)->count_all_results('customer_file');
$customer_file_id = $customer->id;
$actual_payment_schedule = $this->payment_schedule_customer_model->list_schedule_customer_file_actual_active($customer_file_id);
$additional_payment_schedule = $this->payment_schedule_customer_model->list_schedule_customer_file_additional_active($customer_file_id);
$receipts = $this->receipt_model->list_cleared_receipt_customer_file($customer_file_id);
$this->db->select('tax_type.name,tax_master.id');
$this->db->from('tax_type');
$this->db->join('tax_master', 'tax_master.tax_type_id=tax_type.id');
$this->db->join('customer_file_tax', 'customer_file_tax.tax_id=tax_master.id');
$this->db->where('customer_file_tax.customer_file_id', $customer_file_id);
$customer_tax = $this->db->get()->result();
$total_due_amount = 0;
// Actual Due
foreach ($actual_payment_schedule as $schedule) {
$total_due_amount += $schedule->due_amount;
$ttax = 0;
$ttax_bal = 0;
$date1 = strtotime($schedule->due_date);
// or your date as well
$date2 = strtotime($due_date_db);
$datediff = $date2 - $date1;
$delay = floor($datediff / (60 * 60 * 24));
//echo $schedule->due_date . ' - ' . $due_date_db . ' - ' . $delay . '<br />';
$tax_amount_tot = 0;
if ($with_tax_selected == 1) {
foreach ($customer_tax as $cs2) {
$tax_amount = 0;
$tax_amount_bal = 0;
if ($schedule->due_amount - $schedule->amount_paid != 0) {
$this->db->select('tax_slab.tax_percentage as tax_percentage');
$this->db->from('tax_slab');
$this->db->join('tax_slab_cost_head', 'tax_slab.id=tax_slab_cost_head.tax_slab_id');
$this->db->where(array('tax_master_id' => $cs2->id, 'from_date <=' => $due_date_db, 'to_date >=' => $due_date_db, 'tax_slab_cost_head.cost_head_id' => $schedule->payment_type));
$applicale_tax = $this->db->get()->first_row();
if (!empty($applicale_tax->tax_percentage)) {
$tax_amount = ($schedule->due_amount - $schedule->amount_paid) / 100 * $applicale_tax->tax_percentage;
$tax_amount_tot += $tax_amount;
}
}
}
} else {
$tax_amount = 0;
}
$due_report[$customer->id]['total_due_amount'] += $schedule->due_amount - $schedule->amount_paid + $tax_amount_tot;
if ($delay < 90) {
$due_report[$customer->id]['due_amount'][1] += $schedule->due_amount - $schedule->amount_paid + $tax_amount_tot;
}
if ($delay >= 90 and $delay < 180) {
$due_report[$customer->id]['due_amount'][2] += $schedule->due_amount - $schedule->amount_paid + $tax_amount_tot;
}
if ($delay >= 180 and $delay < 270) {
$due_report[$customer->id]['due_amount'][3] += $schedule->due_amount - $schedule->amount_paid + $tax_amount_tot;
}
if ($delay >= 270 and $delay < 365) {
$due_report[$customer->id]['due_amount'][4] += $schedule->due_amount - $schedule->amount_paid + $tax_amount_tot;
}
if ($delay >= 365 and $delay < 540) {
$due_report[$customer->id]['due_amount'][5] += $schedule->due_amount - $schedule->amount_paid + $tax_amount_tot;
}
if ($delay >= 540 and $delay < 720) {
$due_report[$customer->id]['due_amount'][6] += $schedule->due_amount - $schedule->amount_paid + $tax_amount_tot;
}
if ($delay > 720) {
$due_report[$customer->id]['due_amount'][7] += $schedule->due_amount - $schedule->amount_paid + $tax_amount_tot;
}
}
// Additional Due
foreach ($additional_payment_schedule as $schedule_additional) {
$total_due_amount += $schedule_additional->due_amount;
$ttax = 0;
$ttax_bal = 0;
$date1 = strtotime($schedule_additional->due_date);
// or your date as well
$date2 = strtotime($due_date_db);
$datediff = $date2 - $date1;
$delay = floor($datediff / (60 * 60 * 24));
$tax_amount_tot = 0;
if ($with_tax_selected == 1) {
foreach ($customer_tax as $cs2) {
$tax_amount = 0;
$tax_amount_bal = 0;
if ($schedule_additional->due_amount - $schedule_additional->amount_paid != 0) {
$this->db->select('tax_slab.tax_percentage as tax_percentage');
$this->db->from('tax_slab');
$this->db->join('tax_slab_cost_head', 'tax_slab.id=tax_slab_cost_head.tax_slab_id');
$this->db->where(array('tax_master_id' => $cs2->id, 'from_date <=' => $due_date_db, 'to_date >=' => $due_date_db, 'tax_slab_cost_head.cost_head_id' => $schedule->payment_type));
$applicale_tax = $this->db->get()->first_row();
if (!empty($applicale_tax->tax_percentage)) {
$tax_amount = ($schedule_additional->due_amount - $schedule_additional->amount_paid) / 100 * $applicale_tax->tax_percentage;
$tax_amount_tot += $tax_amount;
}
}
}
} else {
$tax_amount = 0;
}
//echo $schedule_additional->due_date . ' - ' . $due_date_db . ' - ' . $delay . '<br />';
if ($delay < 90) {
$due_report[$customer->id]['due_amount'][1] += $schedule_additional->due_amount - $schedule_additional->amount_paid + $tax_amount_tot;
}
if ($delay >= 90 and $delay < 180) {
$due_report[$customer->id]['due_amount'][2] += $schedule_additional->due_amount - $schedule_additional->amount_paid + $tax_amount_tot;
}
if ($delay >= 180 and $delay < 270) {
$due_report[$customer->id]['due_amount'][3] += $schedule_additional->due_amount - $schedule_additional->amount_paid + $tax_amount_tot;
}
if ($delay >= 270 and $delay < 365) {
$due_report[$customer->id]['due_amount'][4] += $schedule_additional->due_amount - $schedule_additional->amount_paid + $tax_amount_tot;
}
if ($delay >= 365 and $delay < 540) {
$due_report[$customer->id]['due_amount'][5] += $schedule_additional->due_amount - $schedule_additional->amount_paid + $tax_amount_tot;
}
if ($delay >= 540 and $delay < 720) {
$due_report[$customer->id]['due_amount'][6] += $schedule_additional->due_amount - $schedule_additional->amount_paid + $tax_amount_tot;
}
if ($delay > 720) {
$due_report[$customer->id]['due_amount'][7] += $schedule_additional->due_amount - $schedule_additional->amount_paid + $tax_amount_tot;
}
}
//Receipts
foreach ($receipts as $b => $receipt) {
$due_report[$customer->id]['total_pri'] += $receipt->principle_amount;
$due_report[$customer->id]['total_tx'] += $receipt->amount_paid - $receipt->principle_amount;
$due_report[$customer->id]['total_amt'] += $receipt->amount_paid;
}
}
//print_r($due_report);
//exit();
if (isset($_POST['generate_pdf'])) {
$this->load->library('Pdf');
$this->db->select('company.name,project.project_type');
$this->db->from('company');
$this->db->join('project', 'project.company_id=company.id');
$this->db->join('customer_file', 'customer_file.project_id=project.id');
$company_details = $this->db->get()->first_row();
$pdf = new TCPDF('P', PDF_UNIT, 'A4', true, 'UTF-8', false);
$pdf->SetCreator(PDF_CREATOR);
$pdf->SetTitle('Receipt');
//$pdf->SetHeaderData(PDF_HEADER_LOGO, PDF_HEADER_LOGO_WIDTH, '', '', array(0, 64, 255), array(255, 255, 255));
$pdf->SetPrintFooter(false);
$pdf->setPrintHeader(false);
//$pdf->setHeaderFont(Array(PDF_FONT_NAME_MAIN, '', PDF_FONT_SIZE_MAIN));
//$pdf->setFooterFont(Array(PDF_FONT_NAME_DATA, '', PDF_FONT_SIZE_DATA));
$pdf->SetDefaultMonospacedFont(PDF_FONT_MONOSPACED);
$pdf->SetMargins(PDF_MARGIN_LEFT, PDF_MARGIN_TOP, PDF_MARGIN_RIGHT);
//$pdf->SetHeaderMargin(PDF_MARGIN_HEADER);
//$pdf->SetFooterMargin(PDF_MARGIN_FOOTER);
$pdf->SetAutoPageBreak(TRUE, PDF_MARGIN_BOTTOM);
$pdf->setImageScale(PDF_IMAGE_SCALE_RATIO);
if (@file_exists(dirname(__FILE__) . '/lang/eng.php')) {
require_once dirname(__FILE__) . '/lang/eng.php';
$pdf->setLanguageArray($l);
}
$pdf->setFontSubsetting(true);
$pdf->SetFont('dejavusans', '', 7, '', true);
$pdf->AddPage();
$pdf->writeHTMLCell(211, 17, '0', 10, '<h2 style="text-align:center;">' . $company_details->name . '</h2>', 0, 1, 0, true, '', true);
$style = array('width' => 0.1, 'color' => array(0, 0, 0));
$pdf->Line(0, 25, 220, 25, $style);
if ($with_tax_selected == 1) {
$pdf->writeHTMLCell(0, 0, '', '', '<h2 style="text-align:center;">Customer Wise Aging Report - With Tax</h2>', 0, 1, 0, true, '', true);
} else {
$pdf->writeHTMLCell(0, 0, '', '', '<h2 style="text-align:center;">Customer Wise Aging Report - without Tax</h2>', 0, 1, 0, true, '', true);
}
$style = array('width' => 0.1, 'color' => array(0, 0, 0));
$pdf->Line(0, 35, 220, 35, $style);
$pdf->writeHTMLCell(35, 0, 10, 40, '<strong style="text-align:center;">Period : ' . date('M Y', strtotime($due_date_db)) . '</strong>', 0, 1, 0, true, '', true);
$pdf->writeHTMLCell(35, 0, 168, 40, '<strong style="text-align:center;">Date : ' . date('d-m-Y') . '</strong>', 0, 1, 0, true, '', true);
$html .= '<tr><td></td><td><td/></tr></table><br /><br /><br /><br />';
$html .= '<table border="1" cellpadding="5" cellspacing = "0" width = "100%" nobr="true"><thead><tr>';
$html .= '<th width="25">No.</th>';
$html .= '<th width="75">Client Name</th>';
$html .= '<th width="60">0-3 Month</th>';
$html .= '<th width="60">3-6 Month</th>';
$html .= '<th width="60">6-9 Month</th>';
$html .= '<th width="60">9-12 Month</th>';
$html .= '<th width="60">1-1.5 Yrs</th>';
$html .= '<th width="60">1.5-2 Yrs</th>';
$html .= '<th width="60">Above 2 Yrs</th>';
$html .= '<th width="60">Total</th>';
$html .= '<th width="60">Tools</th>';
$html .= '</tr>';
$html .= '</thead><tbody>';
$total_sale_amount = 0;
$total_due_amount = 0;
$total_due_amount_additional = 0;
$total_due_tax = 0;
$total_due_tax_additional = 0;
$total_collection = 0;
$total_balance = 0;
foreach ($due_report as $due) {
$dues = $due['due_amount'];
$html .= '<tr>';
$html .= '<td width="25">' . ($n + 1) . '</td>';
$html .= '<td width="75">' . $due['client_name'] . '</td>';
foreach ($dues as $d => $du) {
$html .= '<td width="60">' . number_format($du) . '</td>';
}
$html .= '<td width="60">' . number_format($due['total_due_amount']) . '</td>';
$html .= '<td width="60"></td>';
$html .= '</tr>';
$n++;
}
$html .= '</tbody>';
$html .= '</table><br /><br />';
//echo $html;
//exit();
$pdf->writeHTMLCell(0, 0, '', '', $html, 0, 1, 0, true, '', true);
$pdf->Output('receipt.pdf', 'I');
} elseif (isset($_POST['generate_csv'])) {
$csv_array = array();
$csv_array_line = array();
$csv_array_line[] = 'No.';
$csv_array_line[] = 'Client Name';
$csv_array_line[] = '0-3 Month';
$csv_array_line[] = '3-6 Month';
$csv_array_line[] = '6-9 Month';
$csv_array_line[] = '9-12 Month';
$csv_array_line[] = '1-1.5 Yrs';
$csv_array_line[] = '1.5-2 Yrs';
$csv_array_line[] = 'Above 2 Yrs';
$csv_array_line[] = 'Total';
$csv_array[] = $csv_array_line;
$csv_array_line = array();
$n = 1;
foreach ($due_report as $due) {
$total_due = 0;
$dues = $due['due_amount'];
$csv_array_line = array();
$csv_array_line[] = $n;
$csv_array_line[] = $due['client_name'];
foreach ($dues as $d => $du) {
$total_due += $du;
$csv_array_line[] = $du;
}
$csv_array_line[] = $due['total_due_amount'];
$csv_array[] = $csv_array_line;
$n = $n + 1;
}
echo array_to_csv($csv_array, 'interaction_report.csv');
exit;
} else {
$data['due_report'] = $due_report;
$data['with_tax_selected'] = $with_tax_selected;
$data['project_selected'] = $project_selected;
$data['due_date_selected'] = $due_date_selected;
$this->load->view('reports/customer_wise_aging', $data);
}
}
}
function customer_wise()
{
$this->load->model(array('payment_schedule_customer_model', 'receipt_model'));
$this->load->library('form_validation');
$this->load->model('project_model');
$this->load->helper(array('csv', 'download'));
$this->form_validation->set_rules('project', 'Project', 'trim|required');
$data['projects'] = $this->db->get('project')->result();
$data['tax_types'] = $this->db->get('tax_type')->result();
if ($this->form_validation->run() == FALSE) {
$data['collections'] = array();
$this->load->view('reports/customer_wise_loan_report', $data);
} else {
$tax_types = $this->db->get('tax_type')->result();
$project_selected = $this->input->post('project');
$date_to = $this->input->post('date_to');
$date_to = date('Y-m-d', strtotime($date_to));
$this->load->model(array('payment_schedule_customer_model', 'receipt_model'));
$this->db->select('company.name,project.project_type,project.name as project');
$this->db->from('company');
$this->db->join('project', 'project.company_id=company.id');
$this->db->join('customer_file', 'customer_file.project_id=project.id');
$this->db->where('project.id', $project_selected);
$company_details = $this->db->get()->first_row();
$this->db->select('contact.name as customer_name,project.name as project,' . 'customer_file.purchase_cost,bank.name as bank,customer_file.branch,' . 'customer_file.loan_amount,' . 'customer_file.id as customer_file_id');
$this->db->from('customer_file');
$this->db->join('project', 'project.id=customer_file.project_id');
$this->db->join('contact', 'contact.id=customer_file.customer_id');
$this->db->join('bank', 'bank.id=customer_file.bank', 'left');
$this->db->where('customer_file.project_id', $project_selected);
$this->db->where('customer_file.finance_mode', 1);
$this->db->where('customer_file.booking_date <=', $date_to);
$this->db->group_by('customer_file.id');
$collection_report = $this->db->get()->result();
if (isset($_POST['generate_pdf'])) {
$this->load->library('Pdf');
$pdf = new TCPDF('P', PDF_UNIT, 'A4', true, 'UTF-8', false);
$pdf->SetCreator(PDF_CREATOR);
$pdf->SetTitle('Receipt');
//$pdf->SetHeaderData(PDF_HEADER_LOGO, PDF_HEADER_LOGO_WIDTH, '', '', array(0, 64, 255), array(255, 255, 255));
$pdf->SetPrintFooter(false);
$pdf->setPrintHeader(false);
//$pdf->setHeaderFont(Array(PDF_FONT_NAME_MAIN, '', PDF_FONT_SIZE_MAIN));
//$pdf->setFooterFont(Array(PDF_FONT_NAME_DATA, '', PDF_FONT_SIZE_DATA));
$pdf->SetDefaultMonospacedFont(PDF_FONT_MONOSPACED);
$pdf->SetMargins(PDF_MARGIN_LEFT, PDF_MARGIN_TOP, PDF_MARGIN_RIGHT);
//$pdf->SetHeaderMargin(PDF_MARGIN_HEADER);
//$pdf->SetFooterMargin(PDF_MARGIN_FOOTER);
$pdf->SetAutoPageBreak(TRUE, PDF_MARGIN_BOTTOM);
$pdf->setImageScale(PDF_IMAGE_SCALE_RATIO);
if (@file_exists(dirname(__FILE__) . '/lang/eng.php')) {
require_once dirname(__FILE__) . '/lang/eng.php';
$pdf->setLanguageArray($l);
}
$pdf->setFontSubsetting(true);
$pdf->SetFont('dejavusans', '', 8, '', true);
$pdf->AddPage();
$pdf->writeHTMLCell(211, 17, '0', 10, '<h2 style="text-align:center;">' . $company_details->name . '</h2>', 0, 1, 0, true, '', true);
$style = array('width' => 0.1, 'color' => array(0, 0, 0));
$pdf->Line(0, 25, 220, 25, $style);
$pdf->writeHTMLCell(0, 0, '', '', '<h2 style="text-align:center;">Customer Wise Loan Report - ' . $company_details->project . '</h2>', 0, 1, 0, true, '', true);
$style = array('width' => 0.1, 'color' => array(0, 0, 0));
$pdf->Line(0, 35, 220, 35, $style);
$pdf->writeHTMLCell(38, 0, 10, 40, '<strong style="text-align:center;">Period : ' . date('M Y', strtotime($date_to)) . '</strong>', 0, 1, 0, true, '', true);
$pdf->writeHTMLCell(35, 0, 161, 40, '<strong style="text-align:center;">Date : ' . date('d-m-Y') . '</strong>', 0, 1, 0, true, '', true);
$html .= '<tr><td></td><td><td/></tr></table><br /><br /><br /><br />';
$html .= '<table border="1" cellpadding="5" cellspacing = "0" width = "100%" nobr="true"><thead><tr>';
$html .= '<th width="30">No.</th>';
$html .= '<th width="90">Customer Name</th>';
$html .= '<th width="65">Project</th>';
$html .= '<th width="85">Agreement Value</th>';
$html .= '<th width="75">Bank Name</th>';
$html .= '<th width="74">Branch</th>';
$html .= '<th width="80">Loan Amount</th>';
$html .= '<th width="74">Loan Paid</th>';
$html .= '<th width="74">Loan Balance</th>';
$html .= '</tr>';
$html .= '</thead><tbody>';
$total_due_percentage = 0;
$total_due_amount = 0;
$total_due_tax_amount = 0;
$total_balance = 0;
$total_tax = array();
$total_paid = 0;
$n = 1;
$total_principle_amount = 0;
$total_purchase_cost = 0;
$total_loan_paid = 0;
$total_loan = 0;
$txts = array();
foreach ($collection_report as $collection) {
$total_purchase_cost += $collection->purchase_cost;
$total_loan += $collection->loan_amount;
$total_loan_paid += $loan_amount_paid->amount_paid;
$total_tax_row = 0;
$this->db->select('SUM(receipt.amount_paid) as amount_paid');
$this->db->from('receipt');
$this->db->where('receipt.customer_file_id', $collection->customer_file_id);
$this->db->where('receipt.finance_mode', 1);
$loan_amount_paid = $this->db->get()->first_row();
$total_principle_amount += $collection->principle_amount;
$html .= '<tr>';
$html .= '<td width="30">' . $n . '</td>';
$html .= '<td width="90">' . $collection->customer_name . '</td>';
$html .= '<td width="65">' . $collection->project . '</td>';
$html .= '<td width="85">' . number_format($collection->purchase_cost) . '</td>';
$html .= ' <td width="75">' . $collection->bank . '</td>';
$html .= ' <td width="74">' . $collection->branch . '</td>';
$html .= ' <td width="80">' . number_format($collection->loan_amount) . '</td>';
$html .= ' <td width="74">' . number_format($loan_amount_paid->amount_paid) . '</td>';
$html .= ' <td width="74">' . number_format($collection->loan_amount - $loan_amount_paid->amount_paid) . '</td>';
$html .= '</tr>';
$n++;
}
$html .= '</tbody>';
$html .= '<tfoot>';
$html .= '<tr style="font-weight: bolder">';
$html .= '<td colspan="3"></td>';
$html .= '<td>' . number_format($total_purchase_cost) . '</td>';
$html .= '<td colspan="2"></td>';
$html .= '<td>' . number_format($total_loan) . '</td>';
$html .= '<td>' . number_format($total_loan_paid) . '</td>';
$html .= '<td>' . number_format($total_loan - $total_loan_paid) . '</td>';
$html .= '</tr>';
$html .= '</tfoot>';
$html .= '</table><br /><br />';
//echo $html;
//exit();
$pdf->writeHTMLCell(0, 0, '', '', $html, 0, 1, 0, true, '', true);
$pdf->Output('receipt.pdf', 'I');
} elseif (isset($_POST['generate_csv'])) {
$csv_array = array();
$csv_array_line = array();
$csv_array_line[] = 'No.';
$csv_array_line[] = 'Customer Name';
$csv_array_line[] = 'Project';
$csv_array_line[] = 'Agreement Value';
$csv_array_line[] = 'Bank Name';
$csv_array_line[] = 'Branch';
$csv_array_line[] = 'Loan Amount';
$csv_array_line[] = 'Loan Paid';
$csv_array_line[] = 'Loan Balance';
$csv_array[] = $csv_array_line;
$csv_array_line = array();
$n = 1;
foreach ($collection_report as $collection) {
$this->db->select('SUM(receipt.amount_paid) as amount_paid');
$this->db->from('receipt');
$this->db->where('receipt.customer_file_id', $collection->customer_file_id);
$this->db->where('receipt.finance_mode', 1);
$loan_amount_paid = $this->db->get()->first_row();
$csv_array_line = array();
$csv_array_line[] = $n;
$csv_array_line[] = $collection->customer_name;
$csv_array_line[] = $collection->project;
$csv_array_line[] = $collection->purchase_cost;
$csv_array_line[] = $collection->bank;
$csv_array_line[] = $collection->branch;
$csv_array_line[] = $collection->loan_amount;
$csv_array_line[] = $loan_amount_paid->amount_paid;
$csv_array_line[] = $collection->loan_amount - $loan_amount_paid->amount_paid;
$csv_array[] = $csv_array_line;
$n = $n + 1;
}
echo array_to_csv($csv_array, 'interaction_report.csv');
exit;
} else {
$data['collection_report'] = $collection_report;
$data['date_from_selected'] = $this->input->post('date_from');
$data['date_to_selected'] = $this->input->post('date_to');
$data['project_selected'] = $this->input->post('project');
$this->load->view('reports/customer_wise_loan_report', $data);
}
}
}
}
$temprows = $rows;
$rows = array();
foreach ($temprows as $row) {
$newRow = array();
foreach ($exportFields as $key => $exportKey) {
if (isset($row[$key])) {
$newRow[$exportKey] = $row[$key];
} else {
$newRow[$exportKey] = '';
}
}
$rows[] = $newRow;
}
//die(print_r($rows, true));
$output = array_to_csv($rows);
$cacheName = md5(time());
$cacheName = $modx->getOption('core_path') . 'export/' . $cacheName;
$cacheManager = $modx->getCacheManager();
$cacheManager->writeFile($cacheName, $output);
$_SESSION['csv_filedownload'] = basename($cacheName);
return $modx->error->success(basename($cacheName));
} else {
$configs = $modx->getOption('configs', $scriptProperties, '');
$cacheName = $scriptProperties['download'];
$output = 'Export error: ' . $cacheName . ' unknown/no permission';
if (isset($_SESSION['csv_filedownload']) && $cacheName == $_SESSION['csv_filedownload']) {
$cacheName = $modx->getOption('core_path') . 'export/' . $cacheName;
if (!is_file($cacheName)) {
return 'Export error: Export ' . $cacheName . ' does not exist';
} else {
function query_to_csv($query, $headers = TRUE, $download = 'csv.csv')
{
if (!is_object($query) or !method_exists($query, 'list_fields')) {
show_error('invalid query');
}
$array = array();
if ($headers) {
$line = array();
foreach ($query->list_fields() as $name) {
$line[] = ucfirst(str_replace("_", " ", $name));
}
$array[] = $line;
}
foreach ($query->result_array() as $row) {
$line = array();
foreach ($row as $item) {
$line[] = $item;
}
$array[] = $line;
}
echo array_to_csv($array, $download);
}
public function directCall($item = "", $param = "")
{
$dbArr = array();
switch ($item) {
case "export":
if ($_GET['area_no'] != "" && $_GET['area_no'] != "'<ALL>'") {
$isoWhere = "AND area_no in ({$_GET['area_no']})";
}
if ($_GET['rsOption'] > 0) {
$spoolWhere = "AND spool.workable_dt " . ($_GET['rsOption'] == 1 ? "!= NULL" : "= NULL");
}
$fieldVal = "";
if (isset($_GET['fieldF']) && is_array($_GET['fieldF'])) {
foreach ($_GET['fieldF'] as $field => $val) {
$res = explode(";", $val);
$fieldVal .= " AND " . $res[0] . $this->operatorArr[$res[2]] . $res[1] . (in_array($res[2], $this->blankThis) ? "" : "%") . "'";
}
}
foreach ($this->iso_model->area_query_export(isset($fieldVal) ? $fieldVal : "", isset($isoWhere) ? $isoWhere : "", isset($spoolWhere) ? $spoolWhere : "") as $r) {
foreach ($r as $r2 => $v2) {
if (is_object($v2)) {
$date_array = (array) $v2;
$date_time = $date_array['date'];
$date_only = explode(" ", $date_time);
$r[$r2] = $date_only[0];
}
}
array_push($dbArr, $r);
}
// ------ Create and download csv ----
$csv = array_to_csv($dbArr, "area_query.csv");
header('Content-Type: application/csv');
header('Content-Disposition: attachment; filename="area_query.csv"');
$csv = urldecode($csv);
echo $csv;
return true;
break;
case "export_insTo":
$this->read("inst_takeoff", "get_all_export", $fieldVal, $dbArr);
// ------ Create and download csv ----
$csv = array_to_csv($dbArr, "insto.csv");
header('Content-Type: application/csv');
header('Content-Disposition: attachment; filename="insto.csv"');
$csv = urldecode($csv);
echo $csv;
return true;
break;
case "client":
$this->load->model('portal/rclient_model');
$this->read("rclient", "get_all", $fieldVal, $dbArr);
break;
case "files":
$fieldVal = " ";
if (isset($_GET['type_of_trans']) && $_GET['type_of_trans'] == "download") {
$fieldVal = " find_in_set({$_GET['client_id']},recipients) > 0 AND find_in_set({$this->session->userdata('id')},users) > 0";
} else {
$fieldVal = " t.log_created like '{$this->session->userdata('user_id')}%'";
}
$this->load->model('portal/files_model');
$this->read("files", "get_all", $fieldVal, $dbArr);
break;
case "user":
$this->load->model('portal/ruser_model');
$this->read("ruser", "get_all", $fieldVal, $dbArr);
break;
case "user_by_id":
$fieldVal = $this->session->userdata('creator');
$this->load->model('portal/ruser_model');
$this->read("ruser", "get_by_id", $fieldVal, $dbArr);
break;
case "user_dd":
$this->load->model('portal/ruser_model');
$this->read("ruser", "get_all_dd", $fieldVal, $dbArr);
break;
case "expired":
$this->load->model('portal/ruser_model');
$this->read("ruser", "get_expired", $fieldVal, $dbArr);
break;
case "pieceStruc":
$fieldVal = "plant_no = '{$_GET['plant_no']}' and area_no = '{$_GET['area_no']}' and drawing_no = '{$_GET['drawing_no']}' and sheet_no = '{$_GET['sheet_no']}' and rev_no = '{$_GET['rev_no']}' and area_loc = '{$_GET['area_loc']}'";
$this->read("piece_struc", "get_all", $fieldVal, $dbArr);
break;
default:
break;
}
$this->result['rows'] = $dbArr;
$this->output->set_content_type('application/json')->set_output(json_encode($this->result));
}
function api_create_invoice($return_type, $params)
{
$data = array("id_user" => trim($params[0]), "id_task" => trim($params[1]), "id_company" => trim($params[2]), "bill_id" => trim($params[3]), "concept1" => trim($params[4]), "amount1" => trim($params[5]), "tax" => trim($params[6]), "currency" => trim($params[7]), "description" => trim($params[8]), "locked" => trim($params[9]), "locked_id_user" => trim($params[10]), "invoice_create_date" => trim($params[11]), "invoice_payment_date" => trim($params[12]), "status" => trim($params[13]), "reference" => trim($params[14]), "internal_note" => trim($params[15]), "invoice_type" => trim($params[16]), "id_language" => trim($params[17]));
$res_data = array("status" => 1, "error" => "invoice created");
#Set some default values
if (!$data["status"]) {
$data["status"] = "pending";
}
if ($data["invoice_create_date"] == "") {
$data["invoice_create_date"] = date('Y-m-d H:i:s', time());
}
if ($data["status"] && !$data["invoice_payment_date"]) {
$data["invoice_payment_date"] = date('Y-m-d H:i:s', time());
}
if ($data["id_language"] == "") {
$data["id_language"] = "en_GB";
}
$check_user = get_db_value("id_usuario", "tusuario", "id_usuario", $data["id_user"]);
$check_locked_user = get_db_value("id_usuario", "tusuario", "id_usuario", $data["locked_id_user"]);
#Check for empty billing id
if (!$data["bill_id"]) {
$res_data["status"] = 0;
$res_data["error"] = "empty billing id";
} else {
if (!$data["id_company"]) {
$res_data["status"] = 0;
$res_data["error"] = "empty invoice company";
} else {
if (!$data["concept1"]) {
$res_data["status"] = 0;
$res_data["error"] = "empty invoice concept";
} else {
if (!$data["amount1"]) {
$res_data["status"] = 0;
$res_data["error"] = "empty invoice amount";
} else {
if (!$data["currency"]) {
$res_data["status"] = 0;
$res_data["error"] = "empty invoice currency";
} else {
if (!$data["invoice_type"]) {
$res_data["status"] = 0;
$res_data["error"] = "empty invoice type (Submitted or Received)";
} else {
if (empty($check_user) && !empty($data["id_user"])) {
$res_data["status"] = 0;
$res_data["error"] = "user does not exist";
} else {
if ($data["locked"] && empty($check_locked_user)) {
$res_data["status"] = 0;
$res_data["error"] = "user locked does not exist";
} else {
#Check if billing id exists
$invoice_id = get_db_value("id", "tinvoice", "bill_id", $data["bill_id"]);
if (!$invoice_id) {
$res = process_sql_insert("tinvoice", $data);
if (!$res) {
$res_data["status"] = 0;
$res_data["error"] = "error creating invoice";
}
} else {
$res_data["status"] = 0;
$res_data["error"] = "invalid billing id";
}
}
}
}
}
}
}
}
}
if ($return_type == 'xml') {
$return = "<xml>\n";
$return .= "<invoice>\n";
foreach ($res_data as $key => $value) {
$return .= "<" . $key . ">";
$return .= "<![CDATA[" . $value . "]]>";
$return .= "</" . $key . ">\n";
}
$return .= "</invoice>\n";
$return .= "</xml>\n";
} else {
$return = array_to_csv($res_data);
}
return $return;
}
private function _export_summary($batch_name, $chronicle_id)
{
$this->load->model('products_filters');
$allFilters = $this->products_filters->findAll();
$this->load->model('filters_values');
$line[0] = array('filter_name' => 'Filter name', 'filter_value' => 'Filter value');
if ($cmp_selected > 0 || $chronicle2 > 0) {
$line[0]['filter_name2'] = 'Filter name 2';
$line[0]['filter_value2'] = 'Filter value 2';
if ($chronicle2 > 0) {
$seFilters = $this->filters_values->findAllByAttributes(array('combination_id' => $chronicle2));
$second = array();
foreach ($seFilters as $f) {
$second[$f->filter_id] = $f;
}
} else {
$combo = $batch_id . '_' . $catId . '_' . $cmp_selected;
$this->load->model('batches_combinations');
$this->load->model('combo_chronicle_model');
$comboId = $this->batches_combinations->findByAttributes(array('batches_combination' => $combo));
if (isset($comboId->id)) {
$chronicle = $this->combo_chronicle_model->findByAttributes(array('combo_id' => $comboId->id), array('where' => array('column' => 'date = (SELECT date FROM combo_chronicle WHERE id = ' . $chronicle_id . ')')));
if (isset($chronicle->id)) {
$chronicle_id = $chronicle->id;
}
}
}
}
$prFilters = $this->filters_values->findAllByAttributes(array('combination_id' => $chronicle_id));
$primary = array();
if (is_array($prFilters)) {
foreach ($prFilters as $f) {
$primary[$f->filter_id] = $f;
}
}
if (is_array($prFilters) && is_array($allFilters)) {
foreach ($allFilters as $key => $filter) {
$k = $key + 1;
$line[$k]['filter_name'] = str_replace(':', '', $filter['label']);
preg_match('#<span class="pure_filter_value">(.[0-9]+?)<#s', $primary[isset($filter['filter_id'][0]) ? $filter['filter_id'][0] : $filter['filter_id']]->value, $fval);
$line[$k]['filter_value'] = isset($fval[1]) ? $fval[1] : 0;
if (isset($second[$filter['filter_id']])) {
$line[$k]['filter_name2'] = str_replace(':', '', $filter['label']);
preg_match('#<span class="pure_filter_value">(.[0-9]+?)<#s', $second[isset($filter['filter_id'][0]) ? $filter['filter_id'][0] : $filter['filter_id']]->value, $fval2);
$line[$k]['filter_value2'] = isset($fval2[1]) ? $fval2[1] : 0;
} elseif (isset($filter['has_competitor']) && $filter['has_competitor']) {
if (!isset($line[$k]['filter_name'])) {
$line[$k]['filter_name'] = '';
$line[$k]['filter_value'] = '';
}
preg_match('#<span class="pure_filter_value">(.[0-9]+?)<#s', $primary[isset($filter['filter_id'][1]) ? $filter['filter_id'][1] : $filter['filter_id']]->value, $fval2);
$line[$k]['filter_name2'] = str_replace(':', '', isset($filter['competitor']) ? $filter['competitor']['label'] : 'Competitor ' . $filter['label']);
$line[$k]['filter_value2'] = isset($fval2[1]) ? $fval2[1] : 0;
}
if (!isset($line[$k]['filter_name2']) && ($cmp_selected > 0 || $chronicle2 > 0)) {
$line[$k]['filter_name2'] = '';
$line[$k]['filter_value2'] = '';
}
}
}
$line = array_values($line);
$this->load->helper('csv');
$result = array_to_csv($line, $batch_name . " (" . date("Y-m-d H:i") . ').csv', TRUE);
$this->response($result);
}
function receipt_wise_collection()
{
$this->load->model(array('payment_schedule_customer_model', 'receipt_model'));
$this->load->library('form_validation');
$this->load->model('project_model');
$this->load->helper(array('csv', 'download'));
$this->form_validation->set_rules('project', 'Project', 'trim|required');
$data['projects'] = $this->db->get('project')->result();
$data['tax_types'] = $this->db->get('tax_type')->result();
if ($this->form_validation->run() == FALSE) {
$data['collections'] = array();
$this->load->view('reports/receipt_wise_collection', $data);
} else {
$tax_types = $this->db->get('tax_type')->result();
$project_selected = $this->input->post('project');
$date_from = $this->input->post('date_from');
$date_from = date('Y-m-d', strtotime($date_from));
$date_to = $this->input->post('date_to');
$date_to = date('Y-m-d', strtotime($date_to));
$this->load->model(array('payment_schedule_customer_model', 'receipt_model'));
$this->db->select('company.name,project.project_type,project.name as project');
$this->db->from('company');
$this->db->join('project', 'project.company_id=company.id');
$this->db->join('customer_file', 'customer_file.project_id=project.id');
$this->db->where('project.id', $project_selected);
$company_details = $this->db->get()->first_row();
$this->db->select('contact.name as customer_name,project.name as project,SUM(`receipt`.`principle_amount`) as principle_amount,' . 'SUM(`receipt`.`amount_paid`) as amount_paid,receipt.customer_file_id as customer_file_id,' . 'receipt.receipt_number,receipt.date_payment,receipt.id as receipt_number');
$this->db->from('receipt');
$this->db->join('customer_file', 'customer_file.id=receipt.customer_file_id');
$this->db->join('project', 'project.id=customer_file.project_id');
$this->db->join('contact', 'contact.id=customer_file.customer_id');
$this->db->where('customer_file.project_id', $project_selected);
$this->db->where('receipt.date_payment >=', $date_from);
$this->db->where('receipt.date_payment <=', $date_to);
$this->db->order_by('receipt.date_payment', 'desc');
$this->db->group_by('receipt.id');
$collection_report = $this->db->get()->result();
if (isset($_POST['generate_pdf'])) {
$this->load->library('Pdf');
$pdf = new TCPDF('P', PDF_UNIT, 'A4', true, 'UTF-8', false);
$pdf->SetCreator(PDF_CREATOR);
$pdf->SetTitle('Receipt');
//$pdf->SetHeaderData(PDF_HEADER_LOGO, PDF_HEADER_LOGO_WIDTH, '', '', array(0, 64, 255), array(255, 255, 255));
$pdf->SetPrintFooter(false);
$pdf->setPrintHeader(false);
//$pdf->setHeaderFont(Array(PDF_FONT_NAME_MAIN, '', PDF_FONT_SIZE_MAIN));
//$pdf->setFooterFont(Array(PDF_FONT_NAME_DATA, '', PDF_FONT_SIZE_DATA));
$pdf->SetDefaultMonospacedFont(PDF_FONT_MONOSPACED);
$pdf->SetMargins(PDF_MARGIN_LEFT, PDF_MARGIN_TOP, PDF_MARGIN_RIGHT);
//$pdf->SetHeaderMargin(PDF_MARGIN_HEADER);
//$pdf->SetFooterMargin(PDF_MARGIN_FOOTER);
$pdf->SetAutoPageBreak(TRUE, PDF_MARGIN_BOTTOM);
$pdf->setImageScale(PDF_IMAGE_SCALE_RATIO);
if (@file_exists(dirname(__FILE__) . '/lang/eng.php')) {
require_once dirname(__FILE__) . '/lang/eng.php';
$pdf->setLanguageArray($l);
}
$pdf->setFontSubsetting(true);
$pdf->SetFont('dejavusans', '', 7, '', true);
$pdf->AddPage();
$pdf->writeHTMLCell(211, 17, '0', 10, '<h2 style="text-align:center;">' . $company_details->name . '</h2>', 0, 1, 0, true, '', true);
$style = array('width' => 0.1, 'color' => array(0, 0, 0));
$pdf->Line(0, 25, 220, 25, $style);
$pdf->writeHTMLCell(0, 0, '', '', '<h2 style="text-align:center;">Receipt Wise Collection Report - ' . $company_details->project . '</h2>', 0, 1, 0, true, '', true);
$style = array('width' => 0.1, 'color' => array(0, 0, 0));
$pdf->Line(0, 35, 220, 35, $style);
$pdf->writeHTMLCell(58, 0, 10, 40, '<strong style="text-align:center;">Period : ' . date('M Y', strtotime($date_from)) . ' to ' . date('M Y', strtotime($date_to)) . '</strong>', 0, 1, 0, true, '', true);
$pdf->writeHTMLCell(35, 0, 161, 40, '<strong style="text-align:center;">Date : ' . date('d-m-Y') . '</strong>', 0, 1, 0, true, '', true);
$html .= '<tr><td></td><td><td/></tr></table><br /><br /><br /><br />';
$html .= '<table border="1" cellpadding="5" cellspacing = "0" width = "100%" nobr="true"><thead><tr>';
$html .= '<th width="30">No.</th>';
$html .= '<th width="90">Customer Name</th>';
$html .= '<th width="70">Date</th>';
$html .= '<th width="75">Principal Amount Paid</th>';
foreach ($tax_types as $tax_type) {
$html .= '<th width="75">' . $tax_type->name . '</th>';
}
$html .= '<th width="75">Total Tax</th>';
$html .= '<th width="74">Total Paid</th>';
$html .= '</tr>';
$html .= '</thead><tbody>';
$total_due_percentage = 0;
$total_due_amount = 0;
$total_due_tax_amount = 0;
$total_balance = 0;
$total_tax = array();
foreach ($customer_tax as $cs5) {
$total_tax[$cs5->id] = 0;
}
$total_paid = 0;
$n = 1;
$total_principle_amount = 0;
$txts = array();
foreach ($collection_report as $collection) {
$total_tax_row = 0;
$total_principle_amount += $collection->principle_amount;
$html .= '<tr>';
$html .= '<td width="30">' . $n . '</td>';
$html .= '<td width="90">' . $collection->customer_name . '</td>';
$html .= '<td width="70">' . date('d-m-Y', strtotime($collection->date_payment)) . '</td>';
$html .= '<td width="75">' . number_format($collection->principle_amount) . '</td>';
foreach ($tax_types as $tax_type) {
$this->db->select("SUM(`tax_amount`) as tax");
$this->db->from('receipt_split_up_tax');
$this->db->join('tax_master', 'tax_master.id=receipt_split_up_tax.tax_id');
$this->db->join('tax_type', 'tax_type.id=tax_master.tax_type_id');
$this->db->join('receipt_split_up', 'receipt_split_up.id=receipt_split_up_tax.receipt_split_up_id');
$this->db->join('receipt', 'receipt.id=receipt_split_up.receipt_id');
$this->db->where('receipt.id', $collection->receipt_number);
$this->db->where('tax_type.id', $tax_type->id);
@($txs = $this->db->get()->first_row());
if (!$txs->tax) {
$txs = 0;
} else {
$txs = $txs->tax;
}
$txts[$tax_type->id] += $txs;
$total_tax_row += $txs;
$html .= '<td width="75">' . number_format($txs) . '</td>';
}
$html .= ' <td width="75">' . number_format($total_tax_row) . '</td>';
$html .= ' <td width="74">' . number_format($collection->amount_paid) . '</td>';
$html .= '</tr>';
$n++;
}
$html .= '</tbody>';
$html .= '<tfoot>';
$html .= '<tr style="font-weight: bolder">';
$html .= '<td colspan="3"></td>';
$html .= '<td>' . number_format($total_principle_amount) . '</td>';
$tt_tax = 0;
$ttottax = 0;
foreach ($tax_types as $tax_type) {
$html .= '<td>' . number_format($txts[$tax_type->id]) . '</td>';
$ttottax += $txts[$tax_type->id];
}
$html .= '<td>' . number_format($ttottax) . '</td>';
$html .= '<td>' . number_format($ttottax + $total_principle_amount) . '</td>';
$html .= '</tr>';
$html .= '</tfoot>';
$html .= '</table><br /><br />';
//echo $html;
//exit();
$pdf->writeHTMLCell(0, 0, '', '', $html, 0, 1, 0, true, '', true);
$pdf->Output('receipt.pdf', 'I');
} elseif (isset($_POST['generate_csv'])) {
$csv_array = array();
$csv_array_line = array();
$csv_array_line[] = 'No.';
$csv_array_line[] = 'Customer Name';
$csv_array_line[] = 'Project';
$csv_array_line[] = 'Receipt Number';
$csv_array_line[] = 'Date';
$csv_array_line[] = 'Principal Amount Paid';
foreach ($tax_types as $tax_type) {
$csv_array_line[] = $tax_type->name;
}
$csv_array_line[] = 'Total Tax';
$csv_array_line[] = 'Total Paid';
$csv_array[] = $csv_array_line;
$csv_array_line = array();
$n = 1;
foreach ($collection_report as $collection) {
$csv_array_line = array();
$csv_array_line[] = $n;
$csv_array_line[] = $collection->customer_name;
$csv_array_line[] = $collection->project;
$csv_array_line[] = $collection->receipt_number;
$csv_array_line[] = date('d-m-Y', strtotime($collection->date_payment));
$csv_array_line[] = $collection->principle_amount;
$total_tax_row = 0;
foreach ($tax_types as $tax_type) {
$this->db->select("SUM(`tax_amount`) as tax");
$this->db->from('receipt_split_up_tax');
$this->db->join('tax_master', 'tax_master.id=receipt_split_up_tax.tax_id');
$this->db->join('tax_type', 'tax_type.id=tax_master.tax_type_id');
$this->db->join('receipt_split_up', 'receipt_split_up.id=receipt_split_up_tax.receipt_split_up_id');
$this->db->join('receipt', 'receipt.id=receipt_split_up.receipt_id');
$this->db->where('receipt.id', $collection->receipt_number);
$this->db->where('tax_type.id', $tax_type->id);
@($txs = $this->db->get()->first_row());
if (!$txs->tax) {
$txs = 0;
} else {
$txs = $txs->tax;
}
$txts[$tax_type->id] += $txs;
$total_tax_row += $txs;
$csv_array_line[] = $txs;
}
$csv_array_line[] = $total_tax_row;
$csv_array_line[] = $collection->amount_paid;
$csv_array[] = $csv_array_line;
$n = $n + 1;
}
echo array_to_csv($csv_array, 'receipt_wise_collection_report.csv');
exit;
} else {
$data['collection_report'] = $collection_report;
$data['date_from_selected'] = $this->input->post('date_from');
$data['date_to_selected'] = $this->input->post('date_to');
$data['project_selected'] = $this->input->post('project');
$this->load->view('reports/receipt_wise_collection', $data);
}
}
}
