<?php
	defined('BASEPATH') OR exit('No direct script access allowed');
	require FCPATH.'vendor/autoload.php';
	use PhpOffice\PhpSpreadsheet\Spreadsheet; 
	use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
	use PhpOffice\PhpSpreadsheet\Style\Alignment;
class Reportctrl extends CI_Controller
{
	public $Reportmdl;
	public $excel;
	public $pagination;
	public function __construct()
	{
		parent::__construct();

		$this->load->model('report/Reportmdl');
		$this->load->library('session');
		$this->load->library('upload');
		$this->load->library('pagination');
		$this->config->load('extra');
		$this->load->library('db_operations');
	}


/*stock master*/
	
	public function stock_report()
	{
		if(sessionExist()) 
		{
			if($_GET['action'] == 'view')
			{
				$config 				= array();
				$config 				= $this->config->item('pagination');
				$config['total_rows'] 	= $this->Reportmdl->get_all_stock_data_cnt();
				$config['base_url'] 	= base_url("report/reportctrl/stock_report?search=true");

				foreach ($_GET as $key => $value) 
				{
					if($key != 'search' && $key != 'offset')
					{
						if (is_array($value)) {
							// Handle array values correctly
							foreach ($value as $v) {
								$config['base_url'] .= "&" . $key . "[]=" . urlencode($v);
							}
						} else {
							$config['base_url'] .= "&" . $key . "=" . urlencode($value);
						}
					}
				}

				$offset = (!empty($_GET['offset'])) ? $_GET['offset'] : 0;
				$this->pagination->initialize($config);

				$record['count'] 		= $offset;
				$record['total_rows'] 	= $config['total_rows'];
				$record['role'] 		= $this->config->item('role');				
				$record['data']			= $this->Reportmdl->get_all_stock_data($config['per_page'],$offset);

				if(isset($_REQUEST['submit']) && $_REQUEST['submit'] == 'excel')
                {
                    $record = $this->Reportmdl->get_all_stock_data($config['per_page'],$offset);

                    $this->diamond_master_excel($record['excel_array']);
                }

				// echo "<pre>"; print_r($record); exit;
				$this->load->view('pages/report/stock_report', $record);
			}
		} 
		else 
		{
			redirect('loginctrl/logout');	
		}
	}
	 public function diamond_master_excel($excel_array)
        {
		                // echo "<pre>"; print_r($excel_array); exit;

            if (!sessionExist()) {
                redirect('loginctrl/logout');
                return;
            }

            // Increase memory and execution time
            ini_set('memory_limit', '512M');
            ini_set('max_execution_time', '300');

            // Clear output buffer
            if (ob_get_length()) {
                ob_end_clean();
            }

            $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
            $sheet = $spreadsheet->getActiveSheet();
            $sheet->setTitle("DIAMOND MASTER");

            // Title row
            $sheet->setCellValue('N1', 'DIAMOND MASTER');
            $sheet->setCellValue('P1', date('d-m-Y'));

            $rowCount = 2;

            foreach ($excel_array as $rowData) {
                $colCount = 1;

                foreach ($rowData as $cellValue) {
                    $colLetter = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($colCount);
                    $sheet->getColumnDimension($colLetter)->setAutoSize(true);

                    $cellCoordinate = $colLetter . $rowCount;

                    // Column 20 (LENGTH) as string
                    if ($colCount == 20) {
                        $sheet->getCell($cellCoordinate)
                            ->setValueExplicit($cellValue, \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
                    } else {
                        $sheet->setCellValue($cellCoordinate, $cellValue);
                    }

                    $colCount++;
                }

                $rowCount++;
            }

			if($_REQUEST['search_value_category'] == 0){
				$category = 'NON-CERTIFIED';
			}elseif($_REQUEST['search_value_category'] == 1){
				$category = 'CERTIFIED';
			}else{
				$category = 'ALL';
			}

            // Output Excel
            $filename = 'diamond_master_'.$category.'_'. date('dMY') . '.xlsx';
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            header('Content-Disposition: attachment;filename="' . $filename . '"');
            header('Cache-Control: max-age=0');
            header('Expires: 0');
            header('Pragma: public');

            $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
            $writer->save('php://output');
            exit;
        }
	public function stock_report_print()
	{
		if(sessionExist()) 
		{
			// echo "<pre>"; print_r($_GET); exit;

			$record['data']	= $this->Reportmdl->get_all_stock_data_print();
			// echo "<pre>"; print_r($record); exit;
			$this->load->view('pdfs/report/stock_report_pdf', $record);
		} 
		else 
		{
			redirect('loginctrl/logout');	
		}
	}	

/*/stock master*/

/*history master*/
	
	public function history_report()
	{
		if(sessionExist()) 
		{
			if($_GET['action'] == 'view')
			{
				$config 				= array();
				$config 				= $this->config->item('pagination');
				$config['base_url'] 	= base_url("report/reportctrl/history_report?search=true");

				foreach ($_GET as $key => $value) 
				{
					if($key != 'search' && $key != 'offset')
					{
						$config['base_url'] .= "&" . $key . "=" .$value;
					}
				}

				$offset = (!empty($_GET['offset'])) ? $_GET['offset'] : 0;
				$this->pagination->initialize($config);

				$record['count'] 		= $offset;
				$record['total_rows'] 	= $config['total_rows'];
				$record['role'] 		= $this->config->item('role');				
				$record['data']			= $this->Reportmdl->get_all_history_data($config['per_page'],$offset);
				// echo "<pre>"; print_r($record); exit;
				$this->load->view('pages/report/history_report', $record);
			}
		} 
		else 
		{
			redirect('loginctrl/logout');	
		}
	}
	public function history_report_print()
	{
		if(sessionExist()) 
		{
			// echo "<pre>"; print_r($_GET); exit;

			$record['data']	= $this->Reportmdl->get_all_history_data_print();
			// echo "<pre>"; print_r($record); exit;
			$this->load->view('pdfs/report/history_report_pdf', $record);
		} 
		else 
		{
			redirect('loginctrl/logout');	
		}
	}	

/*/history master*/

/*issue master*/
	
	public function issue_report()
	{
		if(sessionExist()) 
		{
			if($_GET['action'] == 'view')
			{
				$config 				= array();
				$config 				= $this->config->item('pagination');
				$config['total_rows'] 	= $this->Reportmdl->get_all_issue_data_cnt();
				$config['base_url'] 	= base_url("report/reportctrl/issue_report?search=true");

				foreach ($_GET as $key => $value) 
				{
					if($key != 'search' && $key != 'offset')
					{
						$config['base_url'] .= "&" . $key . "=" .$value;
					}
				}

				$offset = (!empty($_GET['offset'])) ? $_GET['offset'] : 0;
				$this->pagination->initialize($config);

				$record['count'] 		= $offset;
				$record['total_rows'] 	= $config['total_rows'];
				$record['role'] 		= $this->config->item('role');				
				$record['data']			= $this->Reportmdl->get_all_issue_data($config['per_page'],$offset);
				// echo "<pre>"; print_r($record); exit;
				$this->load->view('pages/report/issue_report', $record);
			}
		} 
		else 
		{
			redirect('loginctrl/logout');	
		}
	}
	public function issue_report_print()
	{
		if(sessionExist()) 
		{
			// echo "<pre>"; print_r($_GET); exit;

			$record['data']	= $this->Reportmdl->get_all_issue_data_print();
			// echo "<pre>"; print_r($record); exit;
			$this->load->view('pdfs/report/issue_report_pdf', $record);
		} 
		else 
		{
			redirect('loginctrl/logout');	
		}
	}	

/*/stock master*/

/*sold master*/
	
	public function sold_report()
	{
		if(sessionExist()) 
		{
			if($_GET['action'] == 'view')
			{
				$config 				= array();
				$config 				= $this->config->item('pagination');
				$config['total_rows'] 	= $this->Reportmdl->get_all_sold_data_cnt();
				$config['base_url'] 	= base_url("report/reportctrl/sold_report?search=true");

				foreach ($_GET as $key => $value) 
				{
					if($key != 'search' && $key != 'offset')
					{
						$config['base_url'] .= "&" . $key . "=" .$value;
					}
				}

				$offset = (!empty($_GET['offset'])) ? $_GET['offset'] : 0;
				$this->pagination->initialize($config);

				$record['count'] 		= $offset;
				$record['total_rows'] 	= $config['total_rows'];
				$record['role'] 		= $this->config->item('role');				
				$record['data']			= $this->Reportmdl->get_all_sold_data($config['per_page'],$offset);
				// echo "<pre>"; print_r($record); exit;
				$this->load->view('pages/report/sold_report', $record);
			}
		} 
		else 
		{
			redirect('loginctrl/logout');	
		}
	}	

	public function sold_report_print()
	{
		if(sessionExist()) 
		{
			// echo "<pre>"; print_r($_GET); exit;

			$record['data']	= $this->Reportmdl->get_all_sold_data_print();
			// echo "<pre>"; print_r($record); exit;
			$this->load->view('pdfs/report/sold_report_pdf', $record);
		} 
		else 
		{
			redirect('loginctrl/logout');	
		}
	}

/*/stock master*/

/*hold master*/
	
	public function hold_report()
	{
		if(sessionExist()) 
		{
			if($_GET['action'] == 'view')
			{
				$config 				= array();
				$config 				= $this->config->item('pagination');
				$config['total_rows'] 	= $this->Reportmdl->get_all_hold_data_cnt();
				$config['base_url'] 	= base_url("report/reportctrl/hold_report?search=true");

				foreach ($_GET as $key => $value) 
				{
					if($key != 'search' && $key != 'offset')
					{
						$config['base_url'] .= "&" . $key . "=" .$value;
					}
				}

				$offset = (!empty($_GET['offset'])) ? $_GET['offset'] : 0;
				$this->pagination->initialize($config);

				$record['count'] 		= $offset;
				$record['total_rows'] 	= $config['total_rows'];
				$record['role'] 		= $this->config->item('role');				
				$record['data']			= $this->Reportmdl->get_all_hold_data($config['per_page'],$offset);
				// echo "<pre>"; print_r($record); exit;
				$this->load->view('pages/report/hold_report', $record);
			}
		} 
		else 
		{
			redirect('loginctrl/logout');	
		}
	}	

	public function hold_report_print()
	{
		if(sessionExist()) 
		{
			// echo "<pre>"; print_r($_GET); exit;

			$record['data']	= $this->Reportmdl->get_all_hold_data_print();
			// echo "<pre>"; print_r($record); exit;
			$this->load->view('pdfs/report/hold_report_pdf', $record);
		} 
		else 
		{
			redirect('loginctrl/logout');	
		}
	}

/*/hold master*/

/*group master*/
	
	public function group_report()
	{
		if(sessionExist()) 
		{
			if($_GET['action'] == 'view')
			{
				$record['data']			= $this->Reportmdl->get_all_group_data();
				// echo "<pre>"; print_r($record); exit;
				$this->load->view('pages/report/group_report', $record);
			}
		} 
		else 
		{
			redirect('loginctrl/logout');	
		}
	}	
	public function group_report_excel()
	{
		if(sessionExist()) 
		{
			
			$record = $this->Reportmdl->get_all_group_data();
			// echo "<pre>"; print_r($record); exit();
			$spreadsheet = new Spreadsheet();
		    $sheet = $spreadsheet->setActiveSheetIndex(0);
            $sheet->getColumnDimension('A')->setAutoSize(true);
            $sheet->getColumnDimension('B')->setAutoSize(true);
            $sheet->getColumnDimension('C')->setAutoSize(true);
            $sheet->getColumnDimension('D')->setAutoSize(true);
            $sheet->getColumnDimension('F')->setAutoSize(true);


            $sheet->setTitle("GROUP REPORT"); 
            
            $sheet->setCellValue('A1', date('d-m-Y'));
            $sheet->setCellValue('B1', 'CATEGORY WISE REPORT');            
            $sheet->setCellValue('A3', 'DIAMOND GROUP');
            $sheet->setCellValue('B3', 'TOTAL STONE IN STOCK');
            $sheet->setCellValue('C3', 'STONE IN MEMO');
            $sheet->setCellValue('D3', 'STONE SOLD');
            $sheet->setCellValue('F3', 'STONE IN HAND');

            $sheet->setCellValue('A4', 'FANCY (PE-PR-CU-HT-EM-OV)');

            $sheet->setCellValue('B4', $record['diamond_data']['FANCY (PE-PR-CU-HT-EM-OV)_in_hand']);
            $sheet->setCellValue('C4', $record['diamond_data']['FANCY (PE-PR-CU-HT-EM-OV)_memo']);
            $sheet->setCellValue('D4', $record['diamond_data']['FANCY (PE-PR-CU-HT-EM-OV)_sold']);
            $sheet->setCellValue('F4', '=B4-C4-D4+E4');

            

            $sheet->setCellValue('A5', '3.00 UP ( 3.00 TO 10.00)');
            $sheet->setCellValue('B5', $record['diamond_data']['3.00 UP ( 3.00 TO 10.00)_in_hand']);
            $sheet->setCellValue('C5', $record['diamond_data']['3.00 UP ( 3.00 TO 10.00)_memo']);
            $sheet->setCellValue('D5', $record['diamond_data']['3.00 UP ( 3.00 TO 10.00)_sold']);
            $sheet->setCellValue('F5', '=B5-C5-D5+E5');


            
            $sheet->setCellValue('A6', '2.00 TO 2.99');
            $sheet->setCellValue('B6', $record['diamond_data']['2.00 TO 2.99_in_hand']);
            $sheet->setCellValue('C6', $record['diamond_data']['2.00 TO 2.99_memo']);
            $sheet->setCellValue('D6', $record['diamond_data']['2.00 TO 2.99_sold']);
            $sheet->setCellValue('F6', '=B6-C6-D6+E6');

            $sheet->setCellValue('A7', '1.50 TO 1.99');
            $sheet->setCellValue('B7', $record['diamond_data']['1.50 TO 1.99_in_hand']);
            $sheet->setCellValue('C7', $record['diamond_data']['1.50 TO 1.99_memo']);
            $sheet->setCellValue('D7', $record['diamond_data']['1.50 TO 1.99_sold']);
            $sheet->setCellValue('F7', '=B7-C7-D7+E7');

            $sheet->setCellValue('A8', '1.00 TO 1.49');
            $sheet->setCellValue('B8', $record['diamond_data']['1.00 TO 1.49_in_hand']);
            $sheet->setCellValue('C8', $record['diamond_data']['1.00 TO 1.49_memo']);
            $sheet->setCellValue('D8', $record['diamond_data']['1.00 TO 1.49_sold']);
            $sheet->setCellValue('F8', '=B8-C8-D8+E8');

            $sheet->setCellValue('A9', '0.90 TO 0.99');
            $sheet->setCellValue('B9', $record['diamond_data']['0.90 TO 0.99_in_hand']);
            $sheet->setCellValue('C9', $record['diamond_data']['0.90 TO 0.99_memo']);
            $sheet->setCellValue('D9', $record['diamond_data']['0.90 TO 0.99_sold']);
            $sheet->setCellValue('F9', '=B9-C9-D9+E9');

            $sheet->setCellValue('A10', '0.80 TO 0.89');
            $sheet->setCellValue('B10', $record['diamond_data']['0.80 TO 0.89_in_hand']);
            $sheet->setCellValue('C10', $record['diamond_data']['0.80 TO 0.89_memo']);
            $sheet->setCellValue('D10', $record['diamond_data']['0.80 TO 0.89_sold']);
            $sheet->setCellValue('F10', '=B10-C10-D10+E10');

            $sheet->setCellValue('A11', '0.70 TO 0.79');
            $sheet->setCellValue('B11', $record['diamond_data']['0.70 TO 0.79_in_hand']);
            $sheet->setCellValue('C11', $record['diamond_data']['0.70 TO 0.79_memo']);
            $sheet->setCellValue('D11', $record['diamond_data']['0.70 TO 0.79_sold']);
            $sheet->setCellValue('F11', '=B11-C11-D11+E11');

            $sheet->setCellValue('A12', '0.01 TO 0.69');
            $sheet->setCellValue('B12', $record['diamond_data']['0.01 TO 0.69_in_hand']);
            $sheet->setCellValue('C12', $record['diamond_data']['0.01 TO 0.69_memo']);
            $sheet->setCellValue('D12', $record['diamond_data']['0.01 TO 0.69_sold']);
            $sheet->setCellValue('F12', '=B12-C12-D12+E12');
            
            $filename='group_report_'.date('dMY').'.csv'; //save our workbook as this file name
            header('Content-Type: application/vnd.ms-excel'); //mime type
            header('Content-Disposition: attachment;filename="'.$filename.'"'); //tell browser what's the file name
            header('Cache-Control: max-age=0'); //no cache
            // Instantiate a Writer to create an OfficeOpenXML Excel .xlsx file
            $objWriter = new Xlsx($spreadsheet);
            // Write the Excel file to filename some_excel_file.xlsx in the current directory
            $objWriter->save('php://output'); 
		}
		else 
		{
			redirect('loginctrl/logout');	
		}
	}

/*/group master*/

/*groupwise daily master*/
	
	public function group_daily_report()
	{
		if(sessionExist()) 
		{
			if($_GET['action'] == 'view')
			{
				$record['data']	= $this->Reportmdl->get_all_group_daily_data();
				// echo "<pre>"; print_r($record); exit;
				$this->load->view('pages/report/group_daily_report', $record);
			}
		} 
		else 
		{
			redirect('loginctrl/logout');	
		}
	}	
	public function group_daily_report_excel()
	{
		if(sessionExist()) 
		{
			
			$record = $this->Reportmdl->get_all_group_data();
			// echo "<pre>"; print_r($record); exit();
			$spreadsheet = new Spreadsheet();
		    $sheet = $spreadsheet->setActiveSheetIndex(0);
            $sheet->getColumnDimension('A')->setAutoSize(true);
            $sheet->getColumnDimension('B')->setAutoSize(true);
            $sheet->getColumnDimension('C')->setAutoSize(true);
            $sheet->getColumnDimension('D')->setAutoSize(true);
            $sheet->getColumnDimension('F')->setAutoSize(true);


            $sheet->setTitle("GROUP REPORT"); 
            
            $sheet->setCellValue('A1', date('d-m-Y'));
            $sheet->setCellValue('B1', 'CATEGORY WISE REPORT');            
            $sheet->setCellValue('A3', 'DIAMOND GROUP');
            $sheet->setCellValue('B3', 'TOTAL STONE IN STOCK');
            $sheet->setCellValue('C3', 'STONE IN MEMO');
            $sheet->setCellValue('D3', 'STONE SOLD');
            $sheet->setCellValue('F3', 'STONE IN HAND');

            $sheet->setCellValue('A4', 'FANCY (PE-PR-CU-HT-EM-OV)');

            $sheet->setCellValue('B4', $record['diamond_data']['FANCY (PE-PR-CU-HT-EM-OV)_in_hand']);
            $sheet->setCellValue('C4', $record['diamond_data']['FANCY (PE-PR-CU-HT-EM-OV)_memo']);
            $sheet->setCellValue('D4', $record['diamond_data']['FANCY (PE-PR-CU-HT-EM-OV)_sold']);
            $sheet->setCellValue('F4', '=B4-C4-D4+E4');

            

            $sheet->setCellValue('A5', '3.00 UP ( 3.00 TO 10.00)');
            $sheet->setCellValue('B5', $record['diamond_data']['3.00 UP ( 3.00 TO 10.00)_in_hand']);
            $sheet->setCellValue('C5', $record['diamond_data']['3.00 UP ( 3.00 TO 10.00)_memo']);
            $sheet->setCellValue('D5', $record['diamond_data']['3.00 UP ( 3.00 TO 10.00)_sold']);
            $sheet->setCellValue('F5', '=B5-C5-D5+E5');


            
            $sheet->setCellValue('A6', '2.00 TO 2.99');
            $sheet->setCellValue('B6', $record['diamond_data']['2.00 TO 2.99_in_hand']);
            $sheet->setCellValue('C6', $record['diamond_data']['2.00 TO 2.99_memo']);
            $sheet->setCellValue('D6', $record['diamond_data']['2.00 TO 2.99_sold']);
            $sheet->setCellValue('F6', '=B6-C6-D6+E6');

            $sheet->setCellValue('A7', '1.50 TO 1.99');
            $sheet->setCellValue('B7', $record['diamond_data']['1.50 TO 1.99_in_hand']);
            $sheet->setCellValue('C7', $record['diamond_data']['1.50 TO 1.99_memo']);
            $sheet->setCellValue('D7', $record['diamond_data']['1.50 TO 1.99_sold']);
            $sheet->setCellValue('F7', '=B7-C7-D7+E7');

            $sheet->setCellValue('A8', '1.00 TO 1.49');
            $sheet->setCellValue('B8', $record['diamond_data']['1.00 TO 1.49_in_hand']);
            $sheet->setCellValue('C8', $record['diamond_data']['1.00 TO 1.49_memo']);
            $sheet->setCellValue('D8', $record['diamond_data']['1.00 TO 1.49_sold']);
            $sheet->setCellValue('F8', '=B8-C8-D8+E8');

            $sheet->setCellValue('A9', '0.90 TO 0.99');
            $sheet->setCellValue('B9', $record['diamond_data']['0.90 TO 0.99_in_hand']);
            $sheet->setCellValue('C9', $record['diamond_data']['0.90 TO 0.99_memo']);
            $sheet->setCellValue('D9', $record['diamond_data']['0.90 TO 0.99_sold']);
            $sheet->setCellValue('F9', '=B9-C9-D9+E9');

            $sheet->setCellValue('A10', '0.80 TO 0.89');
            $sheet->setCellValue('B10', $record['diamond_data']['0.80 TO 0.89_in_hand']);
            $sheet->setCellValue('C10', $record['diamond_data']['0.80 TO 0.89_memo']);
            $sheet->setCellValue('D10', $record['diamond_data']['0.80 TO 0.89_sold']);
            $sheet->setCellValue('F10', '=B10-C10-D10+E10');

            $sheet->setCellValue('A11', '0.70 TO 0.79');
            $sheet->setCellValue('B11', $record['diamond_data']['0.70 TO 0.79_in_hand']);
            $sheet->setCellValue('C11', $record['diamond_data']['0.70 TO 0.79_memo']);
            $sheet->setCellValue('D11', $record['diamond_data']['0.70 TO 0.79_sold']);
            $sheet->setCellValue('F11', '=B11-C11-D11+E11');

            $sheet->setCellValue('A12', '0.01 TO 0.69');
            $sheet->setCellValue('B12', $record['diamond_data']['0.01 TO 0.69_in_hand']);
            $sheet->setCellValue('C12', $record['diamond_data']['0.01 TO 0.69_memo']);
            $sheet->setCellValue('D12', $record['diamond_data']['0.01 TO 0.69_sold']);
            $sheet->setCellValue('F12', '=B12-C12-D12+E12');
            
            $filename='group_report_'.date('dMY').'.csv'; //save our workbook as this file name
            header('Content-Type: application/vnd.ms-excel'); //mime type
            header('Content-Disposition: attachment;filename="'.$filename.'"'); //tell browser what's the file name
            header('Cache-Control: max-age=0'); //no cache
            // Instantiate a Writer to create an OfficeOpenXML Excel .xlsx file
            $objWriter = new Xlsx($spreadsheet);
            // Write the Excel file to filename some_excel_file.xlsx in the current directory
            $objWriter->save('php://output'); 
		}
		else 
		{
			redirect('loginctrl/logout');	
		}
	}

/*groupwise daily master*/

/*pending certificate master*/
	
	public function certificate_report()
	{
		if(sessionExist()) 
		{
			if($_GET['action'] == 'view')
			{
				$config 				= array();
				$config 				= $this->config->item('pagination');
				$config['total_rows'] 	= $this->Reportmdl->get_all_certificate_data_cnt();
				$config['base_url'] 	= base_url("report/reportctrl/certificate_report?search=true");

				foreach ($_GET as $key => $value) 
				{
					if($key != 'search' && $key != 'offset')
					{
						$config['base_url'] .= "&" . $key . "=" .$value;
					}
				}

				$offset = (!empty($_GET['offset'])) ? $_GET['offset'] : 0;
				$this->pagination->initialize($config);

				$record['count'] 		= $offset;
				$record['total_rows'] 	= $config['total_rows'];
				$record['role'] 		= $this->config->item('role');				
				$record['data']			= $this->Reportmdl->get_all_certificate_data($config['per_page'],$offset);
				// echo "<pre>"; print_r($record); exit;
				$this->load->view('pages/report/certificate_report', $record);
			}
		} 
		else 
		{
			redirect('loginctrl/logout');	
		}
	}
	public function certificate_report_print()
	{
		if(sessionExist()) 
		{
			// echo "<pre>"; print_r($_GET); exit;

			$record['data']	= $this->Reportmdl->get_all_stock_data_print();
			// echo "<pre>"; print_r($record); exit;
			$this->load->view('pdfs/report/stock_report_pdf', $record);
		} 
		else 
		{
			redirect('loginctrl/logout');	
		}
	}	

/*/pending certificate master*/

/* Diamond Summery */
	public function diamond_summery()
	{
		if(sessionExist()) 
		{
        	// echo "<pre>"; print_r($_SERVER['QUERY_STRING']);exit;

			$config 				= array();
			$config 				= $this->config->item('pagination');
			$config['total_rows'] 	= $this->Reportmdl->get_all_diamond_data_cnt();
			$config['base_url'] 	= base_url("report/reportctrl/diamond_summery?search=true");
			foreach ($_REQUEST as $key => $value) 
			{
				if($key != 'search' && $key != 'offset')
				{
					if(is_array($value))
					{
						foreach ($value as $k => $v) 
						{
							// echo "<pre>"; print_r($key);exit;

							$config['base_url'] .= "&" . $key. "%5B%5D=" .$v;	
						}
					}
					else
					{
						$config['base_url'] .= "&" . $key . "=" .$value;	
					}
					
					
				}
			}
			// echo "<pre>"; print_r($config); exit;
			$offset = (!empty($_GET['offset'])) ? $_GET['offset'] : 0;
			$this->pagination->initialize($config);

			$record['count'] 		= $offset;
			$record['total_rows'] 	= $config['total_rows'];
			$record['role'] 		= $this->config->item('role');				
			$record['data']			= $this->Reportmdl->get_all_diamond_data($config['per_page'],$offset);
			// echo "<pre>"; print_r($record); exit;
			$this->load->view('pages/report/diamond_summery', $record);
		} 
		else 
		{
			redirect('loginctrl/logout');	
		}
	}
/* Diamond Summery*/

/* R2NET REPORT */
	public function r2net_report()
	{
		if(sessionExist()) 
		{
        	// echo "<pre>"; print_r($_SERVER['QUERY_STRING']);exit;

			$config 				= array();
			$config 				= $this->config->item('pagination');
			$config['total_rows'] 	= $this->Reportmdl->get_all_r2net_data_cnt();
			$config['base_url'] 	= base_url("report/reportctrl/r2net_report?search=true");
			foreach ($_REQUEST as $key => $value) 
			{
				if($key != 'search' && $key != 'offset')
				{
					if(is_array($value))
					{
						foreach ($value as $k => $v) 
						{
							// echo "<pre>"; print_r($key);exit;

							$config['base_url'] .= "&" . $key. "%5B%5D=" .$v;	
						}
					}
					else
					{
						$config['base_url'] .= "&" . $key . "=" .$value;	
					}
					
					
				}
			}
			// echo "<pre>"; print_r($config); exit;
			$offset = (!empty($_GET['offset'])) ? $_GET['offset'] : 0;
			$this->pagination->initialize($config);

			$record['count'] 		= $offset;
			$record['total_rows'] 	= $config['total_rows'];
			$record['role'] 		= $this->config->item('role');				
			$record['data']			= $this->Reportmdl->get_all_r2net_data($config['per_page'],$offset);
			// echo "<pre>"; print_r($record); exit;
			$this->load->view('pages/report/r2net_report', $record);
		} 
		else 
		{
			redirect('loginctrl/logout');	
		}
	}
	public function r2net_report_excel()
	{
		if(sessionExist()) 
		{	
			$record = $this->Reportmdl->r2net_report_excel();
			// echo "<pre>";print_r($record);exit();

			$spreadsheet = new Spreadsheet();
		    $sheet = $spreadsheet->setActiveSheetIndex(0);
            $sheet->setTitle("R2NET"); 
            
            $sheet->setCellValue('P1', 'R2NET REPORT');
            $sheet->setCellValue('Q1', date('d-m-Y'));
            

            $rowCount = 2;
            foreach ($record as $key => $data) 
            {            	
            	if($key == 0)
            	{
            		/*SET COLUMN WIDTH AND DATA*/
	                foreach(range('A','X') as $key => $columnID)
	                {
	                	/*set coulumn width*/
	                    $sheet->getColumnDimension($columnID)->setAutoSize(true);
	                    
	                    /*set column data*/
	                    $sheet->SetCellValue($columnID.$rowCount, $data[$key]);
	                }	
            	}
            	else
            	{
            		/*SET COLUMN WIDTH AND DATA*/
	                foreach(range('A','X') as $key => $columnID)
	                {
	                	/*set coulumn width*/
		                    $sheet->getColumnDimension($columnID)->setAutoSize(true);
		                    
		                    /*set column data*/

		                    if($columnID == 'Q')
		                	{
		                		$this->excel->getActiveSheet()
		                					->getCell($columnID.$rowCount)
	   										->setValueExplicit($data[$key], PHPExcel_Cell_DataType::TYPE_STRING);
		                		
		                	}
		                	else
		                	{
		                		
		                		$sheet->SetCellValue($columnID.$rowCount, $data[$key]);
		                	}
	                	
	                }	
	                // exit();
            	}
            	
                $rowCount++; 
            }
            // exit;
            $filename='r2net_report_'.date('dMY').'.xlsx'; //save our workbook as this file name
            header('Content-Type: application/vnd.ms-excel'); //mime type
            header('Content-Disposition: attachment;filename="'.$filename.'"'); //tell browser what's the file name
            header('Cache-Control: max-age=0'); //no cache
            // Instantiate a Writer to create an OfficeOpenXML Excel .xlsx file
            $objWriter = new Xlsx($spreadsheet);
            // Write the Excel file to filename some_excel_file.xlsx in the current directory
            $objWriter->save('php://output'); 

				
		} 
		else 
		{
			redirect('loginctrl/logout');	
		}	
	}
/* R2NET REPORT*/
     /*	START SALE REPORT	*/ 
	public function sale_report()
	{
		if(sessionExist()) 
		{	$config 				= array();
			$config 				= $this->config->item('pagination');
			$config['total_rows'] 	= $this->Reportmdl->get_all_sales_data_cnt();
			$config['base_url'] 	= base_url("report/reportctrl/r2net_report?search=true");
			foreach ($_GET as $key => $value) 
			{
				if($key != 'search' && $key != 'offset')
				{
					if(is_array($value))
					{
						foreach ($value as $k => $v) 
						{
							$config['base_url'] .= "&" . $key. "%5B%5D=" .$v;	
						}
					}
					else
					{
						$config['base_url'] .= "&" . $key . "=" .$value;	
					}
				}
			}
			$offset = (!empty($_GET['offset'])) ? $_GET['offset'] : 0;
			$this->pagination->initialize($config);

			$record['count'] 		= $offset;
			$record['total_rows'] 	= $config['total_rows'];		
			$record['data']	= $this->Reportmdl->get_all_sales_data($config['per_page'],$offset);
			$this->load->view('pages/report/sale_report', $record);
		} 
		else 
		{
			redirect('loginctrl/logout');	
		}
	}

	public function sale_report_print()
	{
		if(sessionExist()) 
		{
			
			if(isset($_GET['submit']))
			{
				if($_GET['submit'] == "excel")
				{
					$record = $this->Reportmdl->get_all_sale_data_excel();

					$spreadsheet = new Spreadsheet();
		    		$sheet = $spreadsheet->setActiveSheetIndex(0);
		            $sheet->setTitle("SALE REPORT"); 
		            
		            $sheet->setCellValue('A1', 'SALE REPORT');
		             $this->excel->setActiveSheetIndex(0)->mergeCells('A1:M1');
		            $fontStyle = [
		            		'borders' => array(
                				'allborders' => array(
                  				'style' => PHPExcel_Style_Border::BORDER_THIN
                				)
              				),
                            'font' => [
                                'size' => 14,
                                'bold'  => true
                            ],
                            'alignment' => [
                                'horizontal' => Alignment::HORIZONTAL_CENTER,
                            ]
                        ];

                    $sheet->getStyle("A1:M1")->applyFromArray($fontStyle);
                    
                    if(isset($_GET['start_date']) && isset($_GET['end_date'])):
                        $subtitle = "Period From : ".date('d/m/Y', strtotime($_GET['start_date']))." To : ".date('d/m/Y', strtotime($_GET['end_date']));
                    else:
                        $subtitle = "Period From : ".date('d/m/Y')." To : ".date('d/m/Y');
                    endif;
                    
                    $sheet->setCellValue('D2', $subtitle);
		            $this->excel->setActiveSheetIndex(0)->mergeCells('D2:G2');
		            $fontStyle = [
		            		'borders' => array(
                				'allborders' => array(
                  				'style' => PHPExcel_Style_Border::BORDER_THIN
                				)
              				),
                            'font' => [
                                'size' => 12,
                                'bold'  => false
                            ],
                            'alignment' => [
                                'horizontal' => Alignment::HORIZONTAL_CENTER,
                            ]
                        ];

                    $sheet->getStyle("D2:G2")->applyFromArray($fontStyle);
                    
                    $fontStyle1 = [
	            		'borders' => array(
            				'allborders' => array(
              				'style' => PHPExcel_Style_Border::BORDER_THIN
            				)
          				)
                    ];
        			$rowCount = 2;
		            foreach ($record as $key => $data) 
		            {            	
		            	if($key == 0)
		            	{
		            		/*SET COLUMN WIDTH AND DATA*/
			                foreach(range('A','M') as $key => $columnID)
			                {
			                	/*set coulumn width*/
			                    $sheet->getColumnDimension($columnID)->setAutoSize(true);
			                    
			                    /*set column data*/
			                    $sheet->SetCellValue($columnID.$rowCount, $data[$key]);
			                    
			                    $sheet->getStyle($columnID.$rowCount.":".$columnID.$rowCount)->applyFromArray($fontStyle1);
			                }	
		            	}
		            	else
		            	{
		            		/*SET COLUMN WIDTH AND DATA*/
			                foreach(range('A','M') as $key => $columnID)
			                {
			                	/*set coulumn width*/
				                $sheet->getColumnDimension($columnID)->setAutoSize(true);
				                $sheet->SetCellValue($columnID.$rowCount, $data[$key]);


		                        $sheet->getStyle($columnID.$rowCount.":".$columnID.$rowCount)->applyFromArray($fontStyle1);
			                }
		            	}
		            	
		                $rowCount++; 
		            }
            
		            $filename='sale_report_'.date('dMY').'.xlsx';
		            header('Content-Type: application/vnd.ms-excel');
		            header('Content-Disposition: attachment;filename="'.$filename.'"');
		            header('Cache-Control: max-age=0');
		            $objWriter = new Xlsx($spreadsheet);
		            $objWriter->save('php://output'); 
				}
				else
				{
					$record['data']	= $this->Reportmdl->get_all_sales_data_print();
					$this->load->view('pdfs/report/sale_report_pdf', $record);
				}
			}
			else
			{
				$record['data']	= $this->Reportmdl->get_all_sales_data_print();
				$this->load->view('pdfs/report/sale_report_pdf', $record);
			}
			
			
		} 
		else 
		{
			redirect('loginctrl/logout');	
		}
	}
	/*	END SALE REPORT	*/ 
	
	/*	START SALE REPORT	*/ 
	public function hsnwise_sale_report()
	{
		if(sessionExist()) 
		{	
			$config 				= array();
			$config 				= $this->config->item('pagination');
			$config['total_rows'] 	= $this->Reportmdl->get_all_hsnwise_sales_data_cnt();
			$config['base_url'] 	= base_url("report/reportctrl/hsnwise_sale_report?search=true");
			foreach ($_GET as $key => $value) 
			{
				if($key != 'search' && $key != 'offset')
				{
					if(is_array($value))
					{
						foreach ($value as $k => $v) 
						{
							$config['base_url'] .= "&" . $key. "%5B%5D=" .$v;	
						}
					}
					else
					{
						$config['base_url'] .= "&" . $key . "=" .$value;	
					}
				}
			}
			$offset = (!empty($_GET['offset'])) ? $_GET['offset'] : 0;
			$this->pagination->initialize($config);

			$record['count'] 		= $offset;
			$record['total_rows'] 	= $config['total_rows'];		
			$record['data']	= $this->Reportmdl->get_all_hsnwise_sales_data($config['per_page'],$offset);
			$this->load->view('pages/report/hsnwise_sale_report', $record);
		} 
		else 
		{
			redirect('loginctrl/logout');	
		}
	}

	public function hsnwise_sale_report_print()
	{
		if(sessionExist()) 
		{
			$record['data']	= $this->Reportmdl->get_all_hsnwise_sales_data_print();
			// echo "<pre>"; print_r($record); exit;
			$this->load->view('pdfs/report/hsnwise_sale_report_pdf', $record);
		} 
		else 
		{
			redirect('loginctrl/logout');	
		}
	}
	
	/*	START PURCHASE REPORT	*/ 
	public function purchase_report()
	{
		if(sessionExist()) 
		{	$config 				= array();
			$config 				= $this->config->item('pagination');
			$config['total_rows'] 	= $this->Reportmdl->get_all_purchase_data_cnt();
			$config['base_url'] 	= base_url("report/reportctrl/purchase_report?search=true");
			foreach ($_GET as $key => $value) 
			{
				if($key != 'search' && $key != 'offset')
				{
					if(is_array($value))
					{
						foreach ($value as $k => $v) 
						{
							$config['base_url'] .= "&" . $key. "%5B%5D=" .$v;	
						}
					}
					else
					{
						$config['base_url'] .= "&" . $key . "=" .$value;	
					}
				}
			}
			$offset = (!empty($_GET['offset'])) ? $_GET['offset'] : 0;
			$this->pagination->initialize($config);

			$record['count'] 		= $offset;
			$record['total_rows'] 	= $config['total_rows'];		
			$record['data']	= $this->Reportmdl->get_all_purchase_data($config['per_page'],$offset);
			$this->load->view('pages/report/purchase_report', $record);
		} 
		else 
		{
			redirect('loginctrl/logout');	
		}
	}

	public function purchase_report_print()
	{
		if(sessionExist()) 
		{
			
			if(isset($_GET['submit']))
			{
				if($_GET['submit'] == "excel")
				{
					$record = $this->Reportmdl->get_all_purchase_data_excel();

					$spreadsheet = new Spreadsheet();
		    		$sheet = $spreadsheet->setActiveSheetIndex(0);
		            $sheet->setTitle("PURCHASE REPORT"); 
		            
		            $sheet->setCellValue('A1', 'PURCHASE REPORT');
		             $this->excel->setActiveSheetIndex(0)->mergeCells('A1:L1');
		            $fontStyle = [
		            		'borders' => array(
                				'allborders' => array(
                  				'style' => PHPExcel_Style_Border::BORDER_THIN
                				)
              				),
                            'font' => [
                                'size' => 14,
                                'bold'  => true
                            ],
                            'alignment' => [
                                'horizontal' => Alignment::HORIZONTAL_CENTER,
                            ]
                        ];

                    $sheet->getStyle("A1:L1")->applyFromArray($fontStyle);
                    
                    if(isset($_GET['start_date']) && isset($_GET['end_date'])):
                        $subtitle = "Period From : ".date('d/m/Y', strtotime($_GET['start_date']))." To : ".date('d/m/Y', strtotime($_GET['end_date']));
                    else:
                        $subtitle = "Period From : ".date('d/m/Y')." To : ".date('d/m/Y');
                    endif;
                    $sheet->setCellValue('D2', $subtitle);
		            $this->excel->setActiveSheetIndex(0)->mergeCells('D2:G2');
		            $fontStyle = [
		            		'borders' => array(
                				'allborders' => array(
                  				'style' => PHPExcel_Style_Border::BORDER_THIN
                				)
              				),
                            'font' => [
                                'size' => 12,
                                'bold'  => false
                            ],
                            'alignment' => [
                                'horizontal' => Alignment::HORIZONTAL_CENTER,
                            ]
                        ];

                    $sheet->getStyle("D2:G2")->applyFromArray($fontStyle);
                    
                    $fontStyle1 = [
	            		'borders' => array(
            				'allborders' => array(
              				'style' => PHPExcel_Style_Border::BORDER_THIN
            				)
          				)
                    ];
        			$rowCount = 2;
		            foreach ($record as $key => $data) 
		            {            	
		            	if($key == 0)
		            	{
		            		/*SET COLUMN WIDTH AND DATA*/
			                foreach(range('A','L') as $key => $columnID)
			                {
			                	/*set coulumn width*/
			                    $sheet->getColumnDimension($columnID)->setAutoSize(true);
			                    
			                    /*set column data*/
			                    $sheet->SetCellValue($columnID.$rowCount, $data[$key]);
			                    
			                    $sheet->getStyle($columnID.$rowCount.":".$columnID.$rowCount)->applyFromArray($fontStyle1);
			                }	
		            	}
		            	else
		            	{
		            		/*SET COLUMN WIDTH AND DATA*/
			                foreach(range('A','L') as $key => $columnID)
			                {
			                	/*set coulumn width*/
				                $sheet->getColumnDimension($columnID)->setAutoSize(true);
				                $sheet->SetCellValue($columnID.$rowCount, $data[$key]);


		                        $sheet->getStyle($columnID.$rowCount.":".$columnID.$rowCount)->applyFromArray($fontStyle1);
			                }

			                
		            	}
		            	
		                $rowCount++; 
		            }
            
		            $filename='purchase_report_'.date('dMY').'.xlsx';
		            header('Content-Type: application/vnd.ms-excel');
		            header('Content-Disposition: attachment;filename="'.$filename.'"');
		            header('Cache-Control: max-age=0');
		            $objWriter = new Xlsx($spreadsheet);
		            $objWriter->save('php://output'); 
				}
				else
				{
					$record['data']	= $this->Reportmdl->get_all_purchase_data_print();
					$this->load->view('pdfs/report/purchase_report_pdf', $record);	
				}
			}
			else
			{
				$record['data']	= $this->Reportmdl->get_all_purchase_data_print();
				$this->load->view('pdfs/report/purchase_report_pdf', $record);	
			}
			
			
		} 
		else 
		{
			redirect('loginctrl/logout');	
		}
	}
	/*	END PURCHASE REPORT	*/
	
	// ============================
}

?>
