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); } } }