<?php
if(!isset($_SESSION)) 
{ 
session_start(); 
} 
if(!isset($_SESSION['is_login']) ){
header("location: login.php");
?>
<script>
window.location="login.php";
</script>
<?php
}

// $permission  = explode(',',$_SESSION['permission_id']);
// if(!in_array(19,$permission) && !in_array(1,$permission)  ){

// $op['is_success'] = false;
// $op['data'] 	  = "عذرا انت غير مخول للوصل الى هذه الصفحة";
// echo json_encode($op);
// exit;

// }

date_default_timezone_set($_SESSION['SYSTimezone']);

if (isset($_POST['type']) || isset($_GET['type'])){
		require("db_connect.php");

		if( isset($_GET['type']) && ($_GET['type'] == "load")){
			// DB table to use
			// show All products for specific category

			// $cond_y = isset($_GET['y'])? ' AND YEAR(E.expenses_date)  	='.$_GET['y']:'';
			// $cond_m = isset($_GET['m'])? ' AND MONTH(E.expenses_date) 	='.$_GET['m']:'';
			// $cond_d = isset($_GET['d'])? ' AND DAY(E.expenses_date) 	='.$_GET['d']:'';
			$cond_d = (isset($_GET['d1']) && isset($_GET['d2']))? ' AND (E.expenses_date) BETWEEN "'.$_GET['d1'].'" AND "'.$_GET['d2'].'"':'';



			$cond2 = isset($_GET['id'])? ' AND E.expenses_group_id_fk='.$_GET['id']:'';
			
			$cond1 = '';
			if(isset($_GET['branch_id']) && $_GET['branch_id'] !=0){ 
				$cond1 = ' AND E.branch_id_fk ='.$_GET['branch_id'];
			}

			$cond   = $cond_d . ' ' . $cond1 . ' ' . $cond2;
			
			// $limits = "LIMIT 0";
			$limits =  "";

			if( $_GET['search']['value'] !=''){
				$limits =  "";
			}

			for($i=0; $i<9;$i++){

				if( $_GET['columns'][$i]['search']['value'] !=''){
					$limits =  "";
				}
			}



			$table  =  '(
				SELECT
					expenses_id,
					expenses_name,
					expenses_quantity,
					expenses_unit_price,
					expenses_date,
					expenses_usd_rate,
					expenses_currency,
					expenses_note,
					bill_number,
					attach_bill,
					E.branch_id_fk,
					E.expenses_group_id_fk,
					branch_name,
					group_name
				FROM
					expenses E
					LEFT JOIN expenses_group EG ON (E.expenses_group_id_fk = EG.expenses_group_id AND EG.is_deleted=0)
					LEFT JOIN branch B ON (E.branch_id_fk = B.branch_id )
					
					
				WHERE
					( E.is_deleted = 0 '.$cond.' ) '.$limits.'
			) AS ALLTBL';
			// Table's primary key
			//echo $table;
			$primaryKey = 'expenses_id';
			$columns = array(
			    array( 'db' => 'expenses_id', 'dt' => 0 ),

			    array( 'db' => 'expenses_name', 'dt' => 1 ),
			    array( 'db' => 'expenses_unit_price', 'dt' => 2 ),
			    array( 'db' => 'expenses_date', 'dt' => 3 ),
			    array( 'db' => 'bill_number', 'dt' => 4 ),
			    array( 'db' => 'attach_bill', 'dt' => 5 ),
			    array( 'db' => 'expenses_note', 'dt' => 6 ),
			    array( 'db' => 'group_name', 'dt' => 7 ),
				array( 'db' => 'branch_name', 'dt' => 8 ),
				array( 'db' => 'expenses_group_id_fk', 'dt' => 9 ),
				array( 'db' => 'branch_id_fk', 'dt' => 10 ),
				
			);
			// SQL server connection information
			$sql_details = array(
			    'user' => $mysql_user,
			    'pass' => $mysql_password,
			    'db'   => $mysql_db,
			    'host' => $mysql_server
			);
			require( 'ssp.php' );
			echo json_encode(
			    SSP::complex( $_GET, $sql_details, $table, $primaryKey, $columns ,null, '' )
			);
			exit;
		}

		if ($_POST['type']=="get_edit") {

			$id = $_POST['obj_id'];
			$op  = array('is_success'=>false, 'data' => array());

			$res = $db->query("SELECT 
									expenses_id,
				
									expenses_name,
									expenses_date,
									bill_number,
									expenses_unit_price,
									expenses_note,
									branch_id_fk,
									expenses_group_id_fk
									
				 				FROM expenses  WHERE is_deleted = 0 AND expenses_id = '$id'");
			if ($res) {
				
				
				while ($row = $res->fetch_assoc()) {
				   
						$result['expenses_id']  			= $row['expenses_id'];
						$result['expenses_name']  			= $row['expenses_name'];
						$result['expenses_date']  			= $row['expenses_date'];
						$result['expenses_unit_price']  	= $row['expenses_unit_price'];
						$result['bill_number']  			= $row['bill_number'];
						$result['expenses_note']  			= $row['expenses_note'];
						$result['expenses_group_id_fk']  	= $row['expenses_group_id_fk'];
						$result['branch_id_fk']  			= $row['branch_id_fk'];
						
						
						array_push($op['data'],$result);
				}
				$op['is_success'] = true;
				echo json_encode($op);
				exit;
			}else{
				$op['is_success'] = false;
				$op['data'] 	  = "No Data";
				echo json_encode($op);

				exit;
			}
		}

		if ($_POST['type']=="show_files") {

			$id = $_POST['obj_id'];

			$res = $db->query("SELECT attach_bill FROM expenses WHERE is_deleted=0 AND expenses_id = '$id'");
			if ($res) {
				//$row = $res->fetch_all();
				$op  = array();
				while ($row = $res->fetch_assoc()) {
				    // do what you need.
						$result[0] = $row['attach_bill'];
						array_push($op,$result);
				}

				echo json_encode($op);
				exit;
			}else{
				echo 0;
				exit;
			}
		}

	

		if ($_POST['type'] == "update") {

			$id 						= $_POST['edit_objid'];

			$expenses_name				=addslashes($_POST["edit_expenses_name"]);
      		$expenses_group_id_fk		=addslashes($_POST["edit_expenses_group_id_fk"]);
			$expenses_quantity			=1;
			$expenses_unit_price		=addslashes($_POST["edit_expenses_unit_price"]);
			$expenses_currency			=1;
			$expenses_date				=addslashes($_POST["edit_expenses_date"]);
			$expenses_note				=addslashes($_POST["edit_expenses_note"]);
			$bill_number				=addslashes($_POST["edit_bill_number"]);
			$branch_id_fk				=addslashes($_POST["edit_branch_id_fk"]);

			$op  = array('is_success'=>false, 'data' => '');

			$reg_date = date("Y-m-d h:i:s", time());


			$lastaction = "Update Expenses";
			$db->autocommit(FALSE); // to rollback insertion if update fail

			$update_qry = "UPDATE expenses SET  bill_number = '$bill_number', 
												expenses_name='$expenses_name',
												expenses_unit_price='$expenses_unit_price',	
												expenses_date='$expenses_date',
												
												expenses_note='$expenses_note',
												expenses_group_id_fk='$expenses_group_id_fk',
												updated_at='$reg_date',
												last_action='$lastaction',
												last_action_by='".$_SESSION['user_id']."' 
									WHERE expenses_id = $id";


			$res = $db->query($update_qry);
			if ($res) {

				$target_dir = '../expenses_files/'.$id;
				if(! file_exists($target_dir)){
					mkdir($target_dir);
				}

				$image_path		='';
                $manual_path 	='';
                $State 			=true;
                $State1			=true;
                $error 			= '';			
                $error1			= '';
                $msgg			= "تمت عملية التحديث بنجاح";

				
				if($_FILES['edit_attach_bill']['size'] > 0 ){
					
					$ext11  = pathinfo(basename($_FILES['edit_attach_bill']['name']),PATHINFO_EXTENSION);
					
					$image_file = $id ."_". rand(100000,10000000) ."_.".$ext11;
					
					$target_file 		= $target_dir .'/'. basename($image_file);
					
					$uploadOk = 1;
					$imageFileType  = pathinfo($target_file,PATHINFO_EXTENSION);
										
					$State1=true;
					// Check file size
					if ($_FILES["edit_attach_bill"]["size"] > 4000000) {
							$error1= " Sorry, your Image is too large. ";
							$State1=false;
					}

					
					// Allow certain file formats
					if(strtolower($imageFileType) != "jpg" && strtolower($imageFileType) != "png" && strtolower($imageFileType) != "jpeg" && strtolower($imageFileType) != "gif" && strtolower($imageFileType) != "bmp" && strtolower($imageFileType) != "pdf" ) {
						$error1= " Sorry, only JPG, JPEG, PNG & GIF & PDF files are allowed. ";
						$State1=false;
					}

					// Check if $uploadOk is set to 0 by an error
					if ($State1 == true) {
					
						if (move_uploaded_file($_FILES["edit_attach_bill"]["tmp_name"], $target_file)){

							$image_path  = 'expenses_files/'.$id .'/'.$image_file;
							$State1=true;											  

						} else {
							$error1= " Sorry, there was an error uploading your file. ";
							$State1=false;
						}
					}
				}		
                
                $tmp 			='';
		        $update_file 	= '';
		        
		        if($image_path !=''){
		        	$update_file = "attach_bill = '$image_path'";

		        }

		       
		        // echo $update_file;
		        if($update_file !=''){
		        	$sql ="UPDATE  expenses SET $update_file WHERE expenses_id ='$id'";
							
					$query = $db->query($sql);
		        }
		        

		        $lastaction = "Updated Amount in Expenses";
				$safe_note 	= "Update Expenses ID : " . $id;


				$update_qry = "UPDATE safe SET 	safe_amount 	='$expenses_unit_price',
										  		date_of_add 	='$expenses_date',
										  		safe_note  		='$safe_note',	
										  		branch_id_fk 	='$branch_id_fk',
											  	updated_at      ='$reg_date',
											  	last_action     ='$lastaction',
											  	last_action_by  ='".$_SESSION['user_id']."' 
									WHERE id_fk = $id AND table_name='expenses'";


				$res2 = $db->query($update_qry);

				if($res2){

					$db->commit();
					$op['is_success'] 	= true;
					$op['data'] 	 	= "تمت عملية التحديث بنجاح";
					$op['image'] 	  	= $error;

					echo json_encode($op);
					exit;

				}else{

					$db->rollback();
					$op['is_success'] = false;
					$op['data'] 	  	= "1حصل خطأ: الرجاء اعادة تحميل الصفحة والمحاولة من جديد";
					echo json_encode($op);
					exit;

				}



					
			}else{
				$db->rollback();

				$op['is_success'] = false;
				$op['data'] 	  	= "حصل خطأ: الرجاء اعادة تحميل الصفحة والمحاولة من جديد";
				echo json_encode($op);
				exit;
			}
		}


		if ($_POST['type']=="delete") {

			$id=addslashes($_POST["obj_id"]);
			$reg_date = date("Y-m-d h:i:s", time());
			$lastaction = "Delete Expenses";

			$op  = array('is_success'=>false, 'data' => '');

			
			
			$db->autocommit(FALSE); // to rollback insertion if update fail

	  		$res = $db->query("UPDATE expenses SET is_deleted = 1 ,deleted_at = '$reg_date',last_action='$lastaction',last_action_by='".$_SESSION['user_id']."' WHERE expenses_id=$id");
	    	if ($res){

	    		$res2 = $db->query("UPDATE safe SET is_deleted = 1 ,
	  												deleted_at = '$reg_date',
	  												updated_at='$reg_date',
	  												last_action='$lastaction',
	  												last_action_by='".$_SESSION['user_id']."'
	  										WHERE id_fk = $id AND table_name='expenses'");

	    		if ($res2){
					$db->commit();

	    			$op['is_success'] = true;
					$op['data'] 	  = "تمت عملية الحذف بنجاح";
					echo json_encode($op);
					exit;

	    		}else{
					$db->rollback();

	    			$op['is_success'] = false;
					$op['data'] 	  = "حصل خطأ: الرجاء اعادة تحميل الصفحة والمحاولة من جديد";
					echo json_encode($op);
					exit;
	    		}

	        	
	     	}else{
				$db->rollback();

	      		$op['is_success'] = false;
				$op['data'] 	  = "حصل خطأ: الرجاء اعادة تحميل الصفحة والمحاولة من جديد";
				echo json_encode($op);
				exit;
	     	}
		}

		if ($_POST['type'] == "create") {
      		
      		// $expenses_sub_group_id_fk	=addslashes($_POST["expenses_sub_group_id_fk"]);
      		$expenses_group_id_fk		=addslashes($_POST["expenses_group_id_fk"]);
			$expenses_name				=addslashes($_POST["expenses_name"]);
			$expenses_quantity			=1;
			// $expenses_quantity			=addslashes($_POST["expenses_quantity"]);
			$expenses_unit_price		=addslashes($_POST["expenses_unit_price"]);
			$expenses_currency			=1;
			// $expenses_currency			=addslashes($_POST["expenses_currency"]);
			$expenses_date				=addslashes($_POST["expenses_date"]);
			$expenses_note				=addslashes($_POST["expenses_note"]);
			$bill_number				=addslashes($_POST["bill_number"]);
			$branch_id_fk				=addslashes($_POST["branch_id_fk"]);

			

			
			$delete = 0;
			if($expenses_date =='' || $expenses_date == NULL){
				$expenses_date = date("Y-m-d", time());
			}

			$reg_date = date("Y-m-d h:i:s", time());
			$lastaction = "Create New Expenses";

			$db->autocommit(FALSE); // to rollback insertion if update fail

			$sql ="INSERT INTO expenses(bill_number,
										expenses_name,
										expenses_quantity,
										expenses_unit_price,
										expenses_date,
										expenses_usd_rate,
										expenses_currency,
										expenses_note,
										expenses_group_id_fk,
										branch_id_fk,
										created_by,
										created_at,
										updated_at,
										last_action,
										last_action_by)
								VALUES('$bill_number',
									   '$expenses_name',
									   '$expenses_quantity',
									   '$expenses_unit_price',
									   '$expenses_date',
									   '".$_SESSION['USD_TO_IQD']."',
									   '$expenses_currency',
									   '$expenses_note',
									   '$expenses_group_id_fk',
									   '$branch_id_fk',
									   
									   '".$_SESSION['user_id']."',
									   '$reg_date',
									   '$reg_date',
									   '$lastaction',
									   '".$_SESSION['user_id']."')";

			$res = $db->query($sql);

			if ($res) {

				$last_inserted_id = $db->insert_id;

				/// for file upload
				$target_dir = '../expenses_files/'.$last_inserted_id;
				if(! file_exists($target_dir)){
					mkdir($target_dir);
				}
				

				$count=0;

				$image_path		='';
                $manual_path 	='';
                $State 			=true;
                $State1			=true;
                $error 			= '';			
                $error1			= '';


				
				if($_FILES['attach_bill']['size'] > 0 ){
					
					$ext11  = pathinfo(basename($_FILES['attach_bill']['name']),PATHINFO_EXTENSION);
					
					$image_file = $last_inserted_id."_".rand(100000,10000000) ."_.".$ext11;
									
					// $image_file = rand(10000000,1000000000) . "-" .$_FILES['attach_bill']['name'];
					
					$target_file 		= $target_dir .'/'. basename($image_file);
					
					$uploadOk = 1;
					$imageFileType  = pathinfo($target_file,PATHINFO_EXTENSION);
										
					$State1=true;
					// Check file size
					if ($_FILES["attach_bill"]["size"] > 4000000) {
							$error1= " Sorry, your file is too large. ";
							$State1=false;
					}

					
					// Allow certain file formats
					if(strtolower($imageFileType) != "jpg" && strtolower($imageFileType) != "png" && strtolower($imageFileType) != "jpeg" && strtolower($imageFileType) != "gif" && strtolower($imageFileType) != "bmp" && strtolower($imageFileType) != "pdf" ) {
						$error1= " Sorry, only JPG, JPEG, PNG & GIF & PDF files are allowed. ";
						$State1=false;
					}

					// Check if $uploadOk is set to 0 by an error
					if ($State1 == true) {
					
						if (move_uploaded_file($_FILES["attach_bill"]["tmp_name"], $target_file)){

							$image_path  = 'expenses_files/'.$last_inserted_id .'/'.$image_file;
							$State1=true;											  

						} else {
								$error1= " Sorry, there was an error uploading your file. ";
								$State1=false;
						}
					}
				}		
                
                $tmp 			='';
		        
		        $sql ="UPDATE  expenses SET attach_bill = '$image_path' WHERE expenses_id ='$last_inserted_id'";
							
				$query = $db->query($sql);
				if (!$query) {
					$msgg= "Image Not Uploaded";
					$State=false;

				}else{
					$msgg= "Successfully Added ";
					$State=true;
				}		   


				$res = $db->query("SELECT * FROM safe  ORDER BY safe_id DESC LIMIT 1");
				$row = $res->fetch_assoc();
				$safe_sequence_no 	= $row['safe_sequence_no'] +1;

				$dd = date("y", time());
				$safe_number = "EX".$dd.($safe_sequence_no);

				// Upadte purchase_sequence_no

				$safe_transaction = "Expenses";
				$transaction_type = "2";  // 1: add ; 2:subtract;
			
				

				$reg_date 	= date("Y-m-d h:i:s", time());
				$lastaction = "Add New Expenses";
				$safe_note 	= "Add New Expenses ID : " . $last_inserted_id;

				$sql1 ="INSERT INTO safe 
								   (safe_number,
									safe_amount,
									date_of_add,
									safe_transaction,
									transaction_type,
									capital_usd_rate,
									capital_currency,
									table_name,
									id_fk,
									safe_sequence_no,
									safe_note,
									staff_id_fk,
									branch_id_fk,
									created_by,
									created_at,
								    updated_at,
									last_action,
									last_action_by)
							VALUES( '$safe_number',
									'$expenses_unit_price',
									'$expenses_date',
									'$safe_transaction',
									'$transaction_type',
									'".$_SESSION['USD_TO_IQD']."',
									'$expenses_currency',
									'expenses',
									'$last_inserted_id',
									'$safe_sequence_no',
									'$safe_note',
									'".$_SESSION['user_id']."',
									'$branch_id_fk',
									'".$_SESSION['user_id']."',
									'$reg_date',
									'$reg_date',
									'$lastaction',
									'".$_SESSION['user_id']."')";

				$res1 = $db->query($sql1);

				if($res1){

					$db->commit();
					$op['is_success'] = true;
					$op['data'] 	  = "تمت عملية الاضافة بنجاح";
					$op['image'] 	  = $error;

					echo json_encode($op);
					exit;

				}else{
					$db->rollback();

					$op['is_success'] = false;
					$op['data'] 	  = "حصل خطأ: الرجاء اعادة تحميل الصفحة والمحاولة من جديد";
					echo json_encode($op);
					exit;
				}




				
			}else{
				$db->rollback();

				$op['is_success'] = false;
				$op['data'] 	  = "حصل خطأ: الرجاء اعادة تحميل الصفحة والمحاولة من جديد";
				echo json_encode($op);
				exit;
			}
			

		}
	}
	else{
		header("Location:../");
		exit;
	}
?>
