<?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 WH1.year_id_fk ='.$_GET['y']:'';
			$cond_m = isset($_GET['m'])? ' AND WH1.month_id_fk ='.$_GET['m']:'';
			$cond_d = isset($_GET['d'])? ' AND WH1.day_id_fk ='.$_GET['d']:'';
			
			$cond   = $cond_y . ' ' . $cond_m . ' ' . $cond_d;
			

			$sel_y = isset($_GET['y'])? '(SELECT year_no FROM years WHERE year_id ='.$_GET['y'].') AS year_no':' "All Years" AS year_no';
			
			$sel_m = isset($_GET['m'])? '(SELECT month_name FROM months WHERE month_id ='.$_GET['m'].') AS month_name':'"All Months" AS month_name';

			$sel_d = isset($_GET['d'])? '(SELECT day_no FROM days WHERE day_id ='.$_GET['d'].') AS day_id_fk':' "All Days" AS day_id_fk';



			$table  =  '(

				SELECT
					
					'.$sel_y.',
					'.$sel_m.',
					'.$sel_d.',

					(COALESCE((SELECT sum(hours) FROM work_hours WH1  	
					        	WHERE WH1.employe_id_fk = E.employe_id AND  WH1.is_deleted =0  AND WH1.hour_transaction = "Attend" '.$cond.' ),0)
					) AS attend_hours,

					(COALESCE((SELECT sum(hours) FROM work_hours WH1  	
					        	WHERE WH1.employe_id_fk = E.employe_id AND  WH1.is_deleted =0   AND WH1.hour_transaction = "Absent" '.$cond.'),0)
					) AS absent_hours,

					(COALESCE((SELECT sum(hours) FROM work_hours WH1  	
					        	WHERE WH1.employe_id_fk = E.employe_id AND  WH1.is_deleted =0  AND WH1.hour_transaction = "Absent With Permission" '.$cond.'),0)
					) AS absent_hours_with_permission,
					
					(COALESCE((SELECT sum(hours) FROM work_hours WH1  	
					        	WHERE WH1.employe_id_fk = E.employe_id AND  WH1.is_deleted =0   AND WH1.hour_transaction = "Sick With Permission" '.$cond.'),0)
					) AS sick_hours_with_permission,

					(COALESCE((SELECT sum(hours) FROM work_hours WH1  	
					        	WHERE WH1.employe_id_fk = E.employe_id AND  WH1.is_deleted =0   AND WH1.is_subtracted = 1  '.$cond.'),0)
					) AS absent_subtracted_hours,
					
					(COALESCE((SELECT sum(hours) FROM work_hours WH1  	
					        	WHERE WH1.employe_id_fk = E.employe_id AND  WH1.is_deleted =0   AND WH1.hour_transaction = "Extra Hours" '.$cond.'),0)
					) AS extra_hours,

					(COALESCE((SELECT sum(hours) FROM work_hours WH1  	
					        	WHERE WH1.employe_id_fk = E.employe_id AND  WH1.is_deleted =0   AND WH1.hour_transaction = "Reward" '.$cond.'),0)
					) AS rewards,


					(COALESCE((SELECT sum(hours) FROM work_hours WH1  	
					        	WHERE WH1.employe_id_fk = E.employe_id AND  WH1.is_deleted =0   AND WH1.hour_transaction = "Penalty" '.$cond.'),0)
					) AS penalty,
					

					(COALESCE((SELECT sum(hours) FROM work_hours WH1  	
					        	WHERE WH1.employe_id_fk = E.employe_id AND  WH1.is_deleted =0   AND WH1.hour_transaction = "Salary" '.$cond_y.' '.$cond_m.'),0)
					) AS salary,


					(COALESCE((SELECT sum(hours) FROM work_hours WH1  	
					        	WHERE WH1.employe_id_fk = E.employe_id AND  WH1.is_deleted =0   AND WH1.hour_transaction = "Loan" '.$cond_y.' '.$cond_m.'),0)
					) AS loan,

					(COALESCE((SELECT sum(hours) FROM work_hours WH1  	
					        	WHERE WH1.employe_id_fk = E.employe_id AND  WH1.is_deleted =0   AND WH1.hour_transaction = "Refund" '.$cond_y.' '.$cond_m.'),0)
					) AS refund,
					


					(

					(


					    (	COALESCE((SELECT sum(hours ) FROM work_hours WH1  	
											        	WHERE WH1.employe_id_fk = E.employe_id AND  WH1.is_deleted =0  AND WH1.hour_transaction = "Attend" '.$cond.'),0)
						*
							(	(COALESCE((SELECT sum(hours) FROM work_hours WH1  	
										        	WHERE WH1.employe_id_fk = E.employe_id AND  WH1.is_deleted =0  AND WH1.hour_transaction = "Salary" '.$cond_y.' '.$cond_m.'),0)
								) / 240
							)
						)



					)
					+
					(
						(	COALESCE((SELECT sum(hours * 1.3333 ) FROM work_hours WH1  	
											        	WHERE WH1.employe_id_fk = E.employe_id AND  WH1.is_deleted =0  AND WH1.hour_transaction = "Extra Hours" '.$cond.'),0)
						*
							(	(COALESCE((SELECT sum(hours) FROM work_hours WH1  	
										        	WHERE WH1.employe_id_fk = E.employe_id AND  WH1.is_deleted =0  AND WH1.hour_transaction = "Salary" '.$cond_y.' '.$cond_m.'),0)
								) / 240
							)
						)
					) 
					+
					(COALESCE((SELECT sum(hours) FROM work_hours WH1  	
					        	WHERE WH1.employe_id_fk = E.employe_id AND  WH1.is_deleted =0 AND WH1.hour_transaction = "Reward" '.$cond.'),0)
					) 
					-
					(COALESCE((SELECT sum(hours) FROM work_hours WH1  	
					        	WHERE WH1.employe_id_fk = E.employe_id AND  WH1.is_deleted =0  AND WH1.hour_transaction = "Penalty" '.$cond.'),0)
					) 

					-
					(COALESCE((SELECT sum(hours) FROM work_hours WH1  	
					        	WHERE WH1.employe_id_fk = E.employe_id AND  WH1.is_deleted =0  AND WH1.hour_transaction = "Refund" '.$cond_y.' '.$cond_m.'),0)
					) 
					-
					(		(COALESCE((SELECT sum(hours ) FROM work_hours WH1  	
										        	WHERE WH1.employe_id_fk = E.employe_id AND  WH1.is_deleted =0  AND WH1.is_subtracted = 1 '.$cond.'),0)
							)

							*
							(
								(	(COALESCE((SELECT sum(hours) FROM work_hours WH1  	
										        	WHERE WH1.employe_id_fk = E.employe_id AND  WH1.is_deleted =0  AND WH1.hour_transaction = "Salary" '.$cond_y.' '.$cond_m.'),0)
									) / 240
								)

							)
					)


					) AS real_salary,

					employe_id,
					employe_full_name,
					employe_monthly_salary
					
					
				FROM
					employe  E
					
					
				WHERE
					( E.is_deleted = 0 ) 
		
				) AS ALLTBL';
			// Table's primary key
			// echo $table;
			$primaryKey = 'employe_id';
			$columns = array(
							    array( 'db' => 'employe_id', 'dt' => 0 ),
							    array( 'db' => 'employe_full_name', 'dt' => 1 ),
							    array( 'db' => 'year_no', 'dt' => 2),
							    array( 'db' => 'month_name', 'dt' => 3 ),
							    array( 'db' => 'day_id_fk', 'dt' => 4 ),
							    array( 'db' => 'absent_hours', 'dt' => 5 ),
								array( 'db' => 'extra_hours', 'dt' => 6 ),
							  	array( 'db' => 'rewards', 'dt' => 7 ),
							  	array( 'db' => 'penalty', 'dt' => 8 ),
							  	array( 'db' => 'attend_hours', 'dt' => 9 ),
							  	array( 'db' => 'loan', 'dt' => 10 ),
							  	array( 'db' => 'refund', 'dt' => 11 ),
							  	array( 'db' => 'salary', 'dt' => 12 ),
							  	array( 'db' => 'real_salary', 'dt' => 13 ),
							    array( 'db' => 'absent_hours_with_permission', 'dt' => 14 ),

								array( 'db' => 'sick_hours_with_permission', 'dt' => 15 ),
								array( 'db' => 'absent_subtracted_hours', 'dt' => 16 ),
							  	
			);
			// 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']=="show_salary_board") {

			$year_id 		= $_POST['year_id'];
			$month_id 		= $_POST['month_id'];
		
			$cond_y =  ' AND WH1.year_id_fk ='.$year_id ;
			$cond_m = ' AND WH1.month_id_fk ='.$month_id;
			
			$cond   = $cond_y . ' ' . $cond_m ;

			$day_sql_attend = '';
			$day_sql_absent = '';
			$day_sql_per 	= '';
			$day_sql_sick 	= '';

			for ($i=1; $i <= 30 ; $i++) { 

				$day_sql_attend = $day_sql_attend . '(COALESCE((SELECT sum(hours) FROM work_hours WH1  	
					WHERE WH1.employe_id_fk = E.employe_id AND  WH1.is_deleted =0  AND WH1.hour_transaction = "Attend" '.$cond.' AND WH1.day_id_fk ='.$i.'),0)) AS day_'.$i.',';

				$day_sql_absent = $day_sql_absent . '(COALESCE((SELECT sum(hours) FROM work_hours WH1  	
					WHERE WH1.employe_id_fk = E.employe_id AND  WH1.is_deleted =0  AND WH1.hour_transaction = "Absent" '.$cond.' AND WH1.day_id_fk ='.$i.'),0)) AS abs_'.$i.',';

				$day_sql_per = $day_sql_per . '(COALESCE((SELECT sum(hours) FROM work_hours WH1  	
					WHERE WH1.employe_id_fk = E.employe_id AND  WH1.is_deleted =0  AND WH1.hour_transaction = "Absent With Permission" '.$cond.' AND WH1.day_id_fk ='.$i.'),0)) AS per_'.$i.',';

				$day_sql_sick = $day_sql_sick . '(COALESCE((SELECT sum(hours) FROM work_hours WH1  	
					WHERE WH1.employe_id_fk = E.employe_id AND  WH1.is_deleted =0  AND WH1.hour_transaction = "Sick With Permission" '.$cond.' AND WH1.day_id_fk ='.$i.'),0)) AS sick_'.$i.',';

			}


			$res = $db->query('SELECT
					
					'.$day_sql_attend.'
					'.$day_sql_absent.'
					'.$day_sql_per .'
					'.$day_sql_sick .'

					(COALESCE((SELECT sum(hours) FROM work_hours WH1  	
					        	WHERE WH1.employe_id_fk = E.employe_id AND  WH1.is_deleted =0  AND WH1.hour_transaction = "Attend" '.$cond.' ),0)
					) AS attend_hours,

					(COALESCE((SELECT sum(hours) FROM work_hours WH1  	
					        	WHERE WH1.employe_id_fk = E.employe_id AND  WH1.is_deleted =0   AND WH1.hour_transaction = "Absent" '.$cond.'),0)
					) AS absent_hours,

					(COALESCE((SELECT sum(hours) FROM work_hours WH1  	
					        	WHERE WH1.employe_id_fk = E.employe_id AND  WH1.is_deleted =0  AND WH1.hour_transaction = "Absent With Permission" '.$cond.'),0)
					) AS absent_hours_with_permission,
					
					(COALESCE((SELECT sum(hours) FROM work_hours WH1  	
					        	WHERE WH1.employe_id_fk = E.employe_id AND  WH1.is_deleted =0   AND WH1.hour_transaction = "Sick With Permission" '.$cond.'),0)
					) AS sick_hours_with_permission,

					(COALESCE((SELECT sum(hours) FROM work_hours WH1  	
					        	WHERE WH1.employe_id_fk = E.employe_id AND  WH1.is_deleted =0   AND WH1.is_subtracted = 1  '.$cond.'),0)
					) AS absent_subtracted_hours,
					
					(COALESCE((SELECT sum(hours) FROM work_hours WH1  	
					        	WHERE WH1.employe_id_fk = E.employe_id AND  WH1.is_deleted =0   AND WH1.hour_transaction = "Extra Hours" '.$cond.'),0)
					) AS extra_hours,

					(COALESCE((SELECT sum(hours) FROM work_hours WH1  	
					        	WHERE WH1.employe_id_fk = E.employe_id AND  WH1.is_deleted =0   AND WH1.hour_transaction = "Reward" '.$cond.'),0)
					) AS rewards,


					(COALESCE((SELECT sum(hours) FROM work_hours WH1  	
					        	WHERE WH1.employe_id_fk = E.employe_id AND  WH1.is_deleted =0   AND WH1.hour_transaction = "Penalty" '.$cond.'),0)
					) AS penalty,
					

					(COALESCE((SELECT sum(hours) FROM work_hours WH1  	
					        	WHERE WH1.employe_id_fk = E.employe_id AND  WH1.is_deleted =0   AND WH1.hour_transaction = "Salary" '.$cond.'),0)
					) AS salary,

					(COALESCE((SELECT sum(hours) FROM work_hours WH1  	
					        	WHERE WH1.employe_id_fk = E.employe_id AND  WH1.is_deleted =0   AND WH1.hour_transaction = "Loan" '.$cond.'),0)
					) AS loan,

					(COALESCE((SELECT sum(hours) FROM work_hours WH1  	
					        	WHERE WH1.employe_id_fk = E.employe_id AND  WH1.is_deleted =0   AND WH1.hour_transaction = "Refund" '.$cond.'),0)
					) AS refund,
					


					(

					(


					    (	COALESCE((SELECT sum(hours ) FROM work_hours WH1  	
											        	WHERE WH1.employe_id_fk = E.employe_id AND  WH1.is_deleted =0  AND WH1.hour_transaction = "Attend" '.$cond.'),0)
						*
							(	(COALESCE((SELECT sum(hours) FROM work_hours WH1  	
										        	WHERE WH1.employe_id_fk = E.employe_id AND  WH1.is_deleted =0  AND WH1.hour_transaction = "Salary" '.$cond.'),0)
								) / 240
							)
						)



					)
					+
					(
						(	COALESCE((SELECT sum(hours * 1.3333 ) FROM work_hours WH1  	
											        	WHERE WH1.employe_id_fk = E.employe_id AND  WH1.is_deleted =0  AND WH1.hour_transaction = "Extra Hours" '.$cond.'),0)
						*
							(	(COALESCE((SELECT sum(hours) FROM work_hours WH1  	
										        	WHERE WH1.employe_id_fk = E.employe_id AND  WH1.is_deleted =0  AND WH1.hour_transaction = "Salary" '.$cond.'),0)
								) / 240
							)
						)
					) 
					+
					(COALESCE((SELECT sum(hours) FROM work_hours WH1  	
					        	WHERE WH1.employe_id_fk = E.employe_id AND  WH1.is_deleted =0 AND WH1.hour_transaction = "Reward" '.$cond.'),0)
					) 
					-
					(COALESCE((SELECT sum(hours) FROM work_hours WH1  	
					        	WHERE WH1.employe_id_fk = E.employe_id AND  WH1.is_deleted =0  AND WH1.hour_transaction = "Penalty" '.$cond.'),0)
					)

					-
					(COALESCE((SELECT sum(hours) FROM work_hours WH1  	
					        	WHERE WH1.employe_id_fk = E.employe_id AND  WH1.is_deleted =0  AND WH1.hour_transaction = "Refund" '.$cond.'),0)
					) 
					-
					(		(COALESCE((SELECT sum(hours ) FROM work_hours WH1  	
										        	WHERE WH1.employe_id_fk = E.employe_id AND  WH1.is_deleted =0  AND WH1.is_subtracted = 1 '.$cond.'),0)
							)

							*
							(
								(	(COALESCE((SELECT sum(hours) FROM work_hours WH1  	
										        	WHERE WH1.employe_id_fk = E.employe_id AND  WH1.is_deleted =0  AND WH1.hour_transaction = "Salary" '.$cond.'),0)
									) / 240
								)

							)
					)


					) AS real_salary,

					employe_id,
					employe_full_name,
					employe_monthly_salary
					
					
				FROM
					employe  E
					
					
				WHERE
					( E.is_deleted = 0 ) 

							');

		
			if ($res) {
				//$row = $res->fetch_all();
				//echo json_encode($row);

				$op  = array('invoice'=>array(),'details'=>array());
				while ($row = $res->fetch_assoc()) {
				   
					$result['employe_id']  			= $row['employe_id'];
					$result['employe_full_name']  	= $row['employe_full_name'];
					$result['real_salary']  		= $row['real_salary'] ;
					$result['salary']  				= $row['salary'];
					$result['penalty']  			= $row['penalty'];
					$result['rewards']  			= $row['rewards'];
					$result['extra_hours'] 			= $row['extra_hours'];
					$result['absent_subtracted'] 	= $row['absent_subtracted_hours'];
					$result['sick'] 				= $row['sick_hours_with_permission'];
					$result['permission'] 			= $row['absent_hours_with_permission'];
					$result['absent_hours'] 		= $row['absent_hours'];
					$result['attend_hours'] 		= $row['attend_hours'];
					$result['loan'] 				= $row['loan'];
					$result['refund'] 				= $row['refund'];
					
					$result['total_hours'] 			= $row['attend_hours'] + $row['extra_hours'] * 1.333 - $row['absent_subtracted_hours'];
					
					$result['day_1'] 		= round($row['day_1'],1);
					$result['day_2'] 		= round($row['day_2'],1);
					$result['day_3'] 		= round($row['day_3'],1);
					$result['day_4'] 		= round($row['day_4'],1);
					$result['day_5'] 		= round($row['day_5'],1);
					$result['day_6'] 		= round($row['day_6'],1);
					$result['day_7'] 		= round($row['day_7'],1);
					$result['day_8'] 		= round($row['day_8'],1);
					$result['day_9'] 		= round($row['day_9'],1);
					$result['day_10'] 		= round($row['day_10'],1);
					$result['day_11'] 		= round($row['day_11'],1);
					$result['day_12'] 		= round($row['day_12'],1);
					$result['day_13'] 		= round($row['day_13'],1);
					$result['day_14'] 		= round($row['day_14'],1);
					$result['day_15'] 		= round($row['day_15'],1);
					$result['day_16'] 		= round($row['day_16'],1);
					$result['day_17'] 		= round($row['day_17'],1);
					$result['day_18'] 		= round($row['day_18'],1);
					$result['day_19'] 		= round($row['day_19'],1);
					$result['day_20'] 		= round($row['day_20'],1);
					$result['day_21'] 		= round($row['day_21'],1);
					$result['day_22'] 		= round($row['day_22'],1);
					$result['day_23'] 		= round($row['day_23'],1);
					$result['day_24'] 		= round($row['day_24'],1);
					$result['day_25'] 		= round($row['day_25'],1);
					$result['day_26'] 		= round($row['day_26'],1);
					$result['day_27'] 		= round($row['day_27'],1);
					$result['day_28'] 		= round($row['day_28'],1);
					$result['day_29'] 		= round($row['day_29'],1);
					$result['day_30'] 		= round($row['day_30'],1);


					$result['abs_'][1] 		= round($row['abs_1'],1);
					$result['abs_'][2] 		= round($row['abs_2'],1);
					$result['abs_'][3] 		= round($row['abs_3'],1);
					$result['abs_'][4] 		= round($row['abs_4'],1);
					$result['abs_'][5] 		= round($row['abs_5'],1);
					$result['abs_'][6] 		= round($row['abs_6'],1);
					$result['abs_'][7] 		= round($row['abs_7'],1);
					$result['abs_'][8] 		= round($row['abs_8'],1);
					$result['abs_'][9] 		= round($row['abs_9'],1);
					$result['abs_'][10]		= round($row['abs_10'],1);
					$result['abs_'][11]		= round($row['abs_11'],1);
					$result['abs_'][12]		= round($row['abs_12'],1);
					$result['abs_'][13]		= round($row['abs_13'],1);
					$result['abs_'][14]		= round($row['abs_14'],1);
					$result['abs_'][15]		= round($row['abs_15'],1);
					$result['abs_'][16]		= round($row['abs_16'],1);
					$result['abs_'][17]		= round($row['abs_17'],1);
					$result['abs_'][18]		= round($row['abs_18'],1);
					$result['abs_'][19] 	= round($row['abs_19'],1);
					$result['abs_'][20] 	= round($row['abs_20'],1);
					$result['abs_'][21] 	= round($row['abs_21'],1);
					$result['abs_'][22] 	= round($row['abs_22'],1);
					$result['abs_'][23] 	= round($row['abs_23'],1);
					$result['abs_'][24] 	= round($row['abs_24'],1);
					$result['abs_'][25] 	= round($row['abs_25'],1);
					$result['abs_'][26] 	= round($row['abs_26'],1);
					$result['abs_'][27] 	= round($row['abs_27'],1);
					$result['abs_'][28] 	= round($row['abs_28'],1);
					$result['abs_'][29] 	= round($row['abs_29'],1);
					$result['abs_'][30] 	= round($row['abs_30'],1);
					

					$result['per_'][1] 		= round($row['per_1'],1);
					$result['per_'][2] 		= round($row['per_2'],1);
					$result['per_'][3] 		= round($row['per_3'],1);
					$result['per_'][4] 		= round($row['per_4'],1);
					$result['per_'][5] 		= round($row['per_5'],1);
					$result['per_'][6] 		= round($row['per_6'],1);
					$result['per_'][7]		= round($row['per_7'],1);
					$result['per_'][8]		= round($row['per_8'],1);
					$result['per_'][9]		= round($row['per_9'],1);
					$result['per_'][10]		= round($row['per_10'],1);
					$result['per_'][11]		= round($row['per_11'],1);
					$result['per_'][12]		= round($row['per_12'],1);
					$result['per_'][13]		= round($row['per_13'],1);
					$result['per_'][14]		= round($row['per_14'],1);
					$result['per_'][15]		= round($row['per_15'],1);
					$result['per_'][16]		= round($row['per_16'],1);
					$result['per_'][17]		= round($row['per_17'],1);
					$result['per_'][18]		= round($row['per_18'],1);
					$result['per_'][19] 	= round($row['per_19'],1);
					$result['per_'][20] 	= round($row['per_20'],1);
					$result['per_'][21] 	= round($row['per_21'],1);
					$result['per_'][22] 	= round($row['per_22'],1);
					$result['per_'][23] 	= round($row['per_23'],1);
					$result['per_'][24] 	= round($row['per_24'],1);
					$result['per_'][25] 	= round($row['per_25'],1);
					$result['per_'][26] 	= round($row['per_26'],1);
					$result['per_'][27] 	= round($row['per_27'],1);
					$result['per_'][28] 	= round($row['per_28'],1);
					$result['per_'][29] 	= round($row['per_29'],1);
					$result['per_'][30] 	= round($row['per_30'],1);
					

					$result['sick_'][1] 		= round($row['sick_1'],1);
					$result['sick_'][2] 		= round($row['sick_2'],1);
					$result['sick_'][3] 		= round($row['sick_3'],1);
					$result['sick_'][4] 		= round($row['sick_4'],1);
					$result['sick_'][5] 		= round($row['sick_5'],1);
					$result['sick_'][6] 		= round($row['sick_6'],1);
					$result['sick_'][7] 		= round($row['sick_7'],1);
					$result['sick_'][8] 		= round($row['sick_8'],1);
					$result['sick_'][9]		= round($row['sick_9'],1);
					$result['sick_'][10] 		= round($row['sick_10'],1);
					$result['sick_'][11] 		= round($row['sick_11'],1);
					$result['sick_'][12] 		= round($row['sick_12'],1);
					$result['sick_'][13] 		= round($row['sick_13'],1);
					$result['sick_'][14] 		= round($row['sick_14'],1);
					$result['sick_'][15] 		= round($row['sick_15'],1);
					$result['sick_'][16] 		= round($row['sick_16'],1);
					$result['sick_'][17] 		= round($row['sick_17'],1);
					$result['sick_'][18] 		= round($row['sick_18'],1);
					$result['sick_'][19] 		= round($row['sick_19'],1);
					$result['sick_'][20] 		= round($row['sick_20'],1);
					$result['sick_'][21] 		= round($row['sick_21'],1);
					$result['sick_'][22] 		= round($row['sick_22'],1);
					$result['sick_'][23] 		= round($row['sick_23'],1);
					$result['sick_'][24] 		= round($row['sick_24'],1);
					$result['sick_'][25] 		= round($row['sick_25'],1);
					$result['sick_'][26] 		= round($row['sick_26'],1);
					$result['sick_'][27] 		= round($row['sick_27'],1);
					$result['sick_'][28] 		= round($row['sick_28'],1);
					$result['sick_'][29] 		= round($row['sick_29'],1);
					$result['sick_'][30] 		= round($row['sick_30'],1);
					
					array_push($op['invoice'],$result);
						
				}		
				echo json_encode($op);
				exit;
			}else{
				echo 0;
				exit;
			}
		}



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

			$employe_id_fk 		= $_POST['employe_id_fk'];
			$year_id_fk 		= $_POST['year_id_fk'];
			$month_id_fk 		= $_POST['month_id_fk'];
			$day_id_fk 			= $_POST['day_id_fk'];
			$trans 				= $_POST['trans'];
			
			$cond_e  			= ' AND W.employe_id_fk ='.$employe_id_fk ;
			$cond_y 			=  '';
			$cond_m 			=  '';
			$cond_d 			=  '';

			if( $year_id_fk != '' && $year_id_fk != NULL && $year_id_fk > 0){
				$cond_y =  ' AND W.year_id_fk ='.$year_id_fk ;
			}

			if( $month_id_fk != '' && $month_id_fk != NULL && $month_id_fk > 0){
				$cond_m =  ' AND W.month_id_fk ='.$month_id_fk ;
			}

			if( $day_id_fk != '' && $day_id_fk != NULL && $day_id_fk > 0){
				$cond_m =  ' AND W.day_id_fk ='.$day_id_fk ;
			}

			
			
			$cond   = $cond_y . ' ' . $cond_m . ' ' . $cond_d . ' ' . $cond_e ;

			

		

			$res = $db->query('SELECT
					
									work_id,
									time_in,
									time_out,
									hours,
									W.hour_price,
									hour_transaction,
									is_subtracted,
									work_note,
									day_id_fk,
									month_id_fk,
									year_id_fk,
									employe_id_fk,
									W.created_at,
									employe_full_name  

									
									
								FROM
									work_hours  W
									LEFT JOIN employe E ON (W.employe_id_fk = E.employe_id)
									
									
								WHERE
									( W.is_deleted = 0  '.$cond.') ORDER BY year_id_fk DESC, month_id_fk ASC ,day_id_fk ASC

											');

		
			if ($res) {
				//$row = $res->fetch_all();
				//echo json_encode($row);

				$op  = array('invoice'=>array(),'details'=>array());
				while ($row = $res->fetch_assoc()) {
				   
					$result['work_id']  			= $row['work_id'];
					$result['time_in']  			= $row['time_in'];
					$result['time_out']  			= $row['time_out'] ;
					$result['hours']  				= $row['hours'];
					$result['hour_price']  			= $row['hour_price'];
					$result['hour_transaction']  	= $row['hour_transaction'];
					$result['is_subtracted'] 		= $row['is_subtracted'];
					$result['work_note'] 			= $row['work_note'];
					$result['day_id_fk'] 			= $row['day_id_fk'];
					$result['month_id_fk'] 			= $row['month_id_fk'];
					$result['year_id_fk'] 			= $row['year_id_fk'];
					$result['employe_id_fk'] 		= $row['employe_id_fk'];
					$result['employe_full_name'] 	= $row['employe_full_name'];
					$result['created_at'] 			= date('Y-m-d',strtotime($row['created_at']));
					
					
					
					array_push($op['invoice'],$result);
						
				}		
				echo json_encode($op);
				exit;
			}else{
				echo $db->error;
				exit;
			}
		}


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

			$id 		= $_POST['id'];
			
			$cond 		='';

			$res = $db->query("SELECT capital_withdraw_id, 
									  withdraw_amount, 
									  withdraw_date, 
									  withdraw_note, 
									  withdraw_status, 
									  withdraw_by,
									  gived_to_id_fk, 
									  withdraw_usd_rate, 
									  withdraw_currency, 
									  customer_full_name,
									  customer_email,
									  customer_phone,
									  customer_address, 
									  
														
								    (SELECT sum(payment_withdraw_amount) FROM capital_withdraw_payments CWP WHERE  CWP.is_deleted = 0  AND CWP.payment_gived_to_id_fk = CW.gived_to_id_fk ) AS total_paid,
								

									(SELECT sum(withdraw_amount) FROM capital_withdraw CW1 WHERE  CW1.is_deleted = 0  AND CW1.gived_to_id_fk = CW.gived_to_id_fk ) AS total_withdraw_price



									FROM capital_withdraw CW
										LEFT JOIN customer C ON (CW.gived_to_id_fk = C.customer_id )
								 	WHERE CW.gived_to_id_fk='$id'  AND CW.is_deleted = 0  $cond");

		
			if ($res) {
				//$row = $res->fetch_all();
				//echo json_encode($row);

				$op  = array('invoice'=>array(),'details'=>array());
				while ($row = $res->fetch_assoc()) {
				   
					$result['capital_withdraw_id']  	= $row['capital_withdraw_id'];
					$result['withdraw_amount']  		= $row['withdraw_amount'];
					$result['withdraw_date']  			= date('Y-m-d',strtotime($row['withdraw_date'])) ;
					$result['withdraw_note']  			= $row['withdraw_note'];
					$result['withdraw_status']  		= $row['withdraw_status'];
					$result['withdraw_by']  			= $row['withdraw_by'];
					$result['gived_to_id_fk'] 			= $row['gived_to_id_fk'];
					$result['withdraw_usd_rate']  		= $row['withdraw_usd_rate'];
					$result['customer_full_name']  		= $row['customer_full_name'];
					$result['customer_phone']  			= $row['customer_phone'];
					$result['customer_address']  		= $row['customer_address'];
					$result['withdraw_currency']  		= $row['withdraw_currency'];
					$result['total_paid']  				= $row['total_paid'];
					$result['total_withdraw_price']  	= $row['total_withdraw_price'];
					$result['total_remained']  			= $row['total_withdraw_price'] - $row['total_paid'];
					
					array_push($op['invoice'],$result);
						
				}		
				echo json_encode($op);
				exit;
			}else{
				echo 0;
				exit;
			}
		}

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

			$user_id 	= $_POST['cus_id'];
			$cust_typ 	= $_POST['cust_typ'];

			$cond = '';
			if($user_id !='' && $user_id != NULL){
				$cond = ' AND I.customer_id_fk = '.$user_id ;
			}

			$res = $db->query("SELECT invoice_id,invoice_number,invoice_date,customer_full_name FROM invoice I
													LEFT JOIN customer C ON (C.customer_id = I.customer_id_fk ) WHERE I.is_deleted = 0 AND I.invoice_status < 3 $cond  AND C.customer_type ='$cust_typ'");
			if ($res) {
				//$row = $res->fetch_all();
				$op  = array();
				while ($row = $res->fetch_assoc()) {
				   
						$result[0]  = $row['invoice_id'];
						$result[1]  = $row['invoice_number'];
						$result[2]  = $row['customer_full_name'];
						$result[3]  = date('Y-m-d',strtotime($row['invoice_date'])) ;
						
						array_push($op,$result);
				}
				echo json_encode($op);
				exit;
			}else{
				echo 0;
				echo $db->error;
				exit;
			}
		}

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

			$productName = $_POST['p_val'];

			$res = $db->query("SELECT product_id,erbil_code,turkish_code FROM product P,  item_type IT  WHERE P.is_deleted = 0 AND P.item_type_id_fk = IT.item_type_id AND IT.is_have_purchase =0 AND P.erbil_code ='$productName' LIMIT 1");
			if ($res) {
				//$row = $res->fetch_all();
				$op  = array();
				while ($row = $res->fetch_assoc()) {
				   
						$result[0]  = $row['product_id'];
						$result[1]  = $row['erbil_code'];
						$result[2]  = $row['turkish_code'];
						array_push($op,$result);
				}
				echo json_encode($op);
				exit;
			}else{
				echo 0;
				exit;
			}
		}


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

			$customer_id_fk 	= $_POST['customer_id_fk'];
			$item_type_id_fk 	= $_POST['item_type_id_fk'];

			$cond = '';
			if($item_type_id_fk != '' && $item_type_id_fk != NULL){
				$cond = " AND S.item_type_id_fk = '$item_type_id_fk' ";
			}

			$res = $db->query("
								SELECT invoice_id, 
									invoice_number, 						
									invoice_status,  
									sell_item_quantity, 
									customer_id_fk,  
									actual_discount,
									item_sell_price_per_meter,
									decorate_price_per_meter, 
									is_paid,
									S.item_type_id_fk, 
								  	other_id_fk, 
								  	S.product_id_fk,
                                    sell_item_id,
								    customer_full_name,
															 
									CONCAT(COALESCE(erbil_code,''), COALESCE(other_name,'')) 
									AS product_name,

									(SELECT sum(paid_amount) FROM invoice_payment IP WHERE I.invoice_id = IP.invoice_id_fk AND IP.is_returned=0 AND IP.is_deleted =0) 
									AS total_paid,
																
									(SELECT sum(actual_discount) FROM sell_item SI WHERE I.invoice_id = SI.invoice_id_fk AND SI.is_deleted =0) 
									AS total_discount,

									
									(

									COALESCE((SELECT sum(sell_item_detail_height * item_sell_price_per_meter) FROM sell_item_detail SID, sell_item S 
											          			
									WHERE I.invoice_id = SID.invoice_id_fk AND S.invoice_id_fk = I.invoice_id AND S.is_deleted=0 AND  SID.part_status =0 AND SID.is_deleted=0 AND S.sell_item_id = SID.sell_item_id_fk AND S.other_id_fk =0 AND SID.sell_item_detail_width = 0),0)
									+
									COALESCE((SELECT sum(sell_item_detail_height * sell_item_detail_width * item_sell_price_per_meter) FROM sell_item_detail SID, sell_item S 
											          			
									WHERE I.invoice_id = SID.invoice_id_fk AND S.invoice_id_fk = I.invoice_id AND S.is_deleted=0 AND  SID.part_status =0 AND SID.is_deleted=0 AND S.sell_item_id = SID.sell_item_id_fk AND S.other_id_fk =0 AND SID.sell_item_detail_width > 0),0)
									+
									COALESCE((SELECT sum(sell_item_detail_width * decorate_price_per_meter) FROM sell_item_detail SID, sell_item S 
											          			
									WHERE I.invoice_id = SID.invoice_id_fk AND S.invoice_id_fk = I.invoice_id AND S.is_deleted=0 AND  SID.part_status =0 AND SID.is_deleted=0 AND S.sell_item_id = SID.sell_item_id_fk AND S.other_id_fk =0 AND SID.sell_item_detail_width > 0),0)

									+
									COALESCE((SELECT sum(sell_item_quantity * item_sell_price_per_meter) FROM sell_item S  
									WHERE I.invoice_id = S.invoice_id_fk AND  S.other_id_fk >0 AND S.is_deleted=0),0)
									-
									(SELECT sum(actual_discount) from sell_item where sell_item.invoice_id_fk = I.invoice_id AND sell_item.is_deleted=0 )
																
									) 
									AS total_invoice_price,


									(SELECT sum(sell_item_detail_height) FROM sell_item_detail SID WHERE SID.invoice_id_fk = I.invoice_id AND SID.sell_item_id_fk=S.sell_item_id AND part_status=0 AND SID.is_deleted = 0) 
									AS total_length,

									(SELECT sum(paid_amount) FROM invoice_payment IP1 WHERE				   I.invoice_id = IP1.invoice_id_fk AND IP1.sell_item_id_fk=S.sell_item_id AND IP1.is_returned=0 AND IP1.is_deleted =0) 
									AS total_paid_per_product,
																													
													
									(SELECT sum(sell_item_detail_height * sell_item_detail_width) FROM sell_item_detail SID WHERE SID.invoice_id_fk = I.invoice_id AND SID.sell_item_id_fk=S.sell_item_id AND part_status=0 AND SID.is_deleted = 0 AND SID.sell_item_detail_width > 0) 
									AS total_area,
																													
									(SELECT sum(sell_item_detail_width) FROM sell_item_detail SID WHERE SID.invoice_id_fk = I.invoice_id AND SID.sell_item_id_fk=S.sell_item_id AND part_status=0 AND SID.is_deleted = 0 AND SID.sell_item_detail_width > 0) 
									AS total_decor_width


								FROM invoice I
							 		LEFT JOIN sell_item S ON (S.invoice_id_fk = I.invoice_id AND S.is_deleted=0  $cond)
							 		LEFT JOIN others O ON (S.other_id_fk = O.other_id AND S.other_id_fk >0)
									LEFT JOIN product P ON (S.product_id_fk = P.product_id )
									LEFT JOIN customer C ON (I.customer_id_fk = C.customer_id )
							 	WHERE I.customer_id_fk='$customer_id_fk'  AND I.is_deleted = 0 AND I.invoice_status <2
							 	
							  ");
			
			if ($res) {
				//$row = $res->fetch_all();
				$op  = array();
				$total_item_type_price 		=0;
				$total_item_type_paid	 	=0;

				while ($row = $res->fetch_assoc()) {

					$total_row_price = 0;				
                
	                if($row['other_id_fk'] > 0){
	                  $total_row_price = $row['sell_item_quantity'] * $row['item_sell_price_per_meter'] - $row['actual_discount'];
	                }else{
	                  
	                  if($row['total_area'] > 0 && $row['total_area'] != NULL){ // it mean its curtain
	                    $total_row_price = $row['total_area'] * $row['item_sell_price_per_meter'] + $row['total_decor_width'] * $row['decorate_price_per_meter'] - $row['actual_discount'];
	                  }else{
	                    $total_row_price = $row['total_length'] * $row['item_sell_price_per_meter'] - $row['actual_discount'];
	                  }
	                } 
				   	
					$total_item_type_price	= $total_item_type_price + $total_row_price;
					$total_item_type_paid 	= $total_item_type_paid + $row['total_paid_per_product'];
						
						
				}

				$total_item_type_remaind 	= $total_item_type_price - $total_item_type_paid;

				

				// we should add the withdraw also if he choose all 

				if($item_type_id_fk == '' || $item_type_id_fk == NULL){
					
					$res = $db->query("
								SELECT									
										customer_full_name,
										
										(SELECT sum(withdraw_amount) FROM capital_withdraw CW  WHERE CW.is_deleted =0 AND CW.gived_to_id_fk = C.customer_id) AS total_amount,

										(SELECT sum(payment_withdraw_amount) FROM capital_withdraw_payments CWP1 WHERE  CWP1.is_deleted = 0  AND CWP1.payment_gived_to_id_fk = C.customer_id) AS total_paid,
										(
										COALESCE((SELECT sum(withdraw_amount) FROM capital_withdraw CW  WHERE CW.is_deleted =0 AND CW.gived_to_id_fk = C.customer_id),0)
										-
										COALESCE((SELECT sum(payment_withdraw_amount) FROM capital_withdraw_payments CWP1 WHERE  CWP1.is_deleted = 0  AND CWP1.payment_gived_to_id_fk = C.customer_id),0)
										 ) AS total_remained
								FROM customer C
						
								WHERE  C.is_deleted = 0  AND C.customer_id='$customer_id_fk'  ");
					
					if ($res) {
						
						$total_item_type_price1 	=0;
						$total_item_type_paid1	 	=0;

						while ($row = $res->fetch_assoc()) {

							 
							$total_item_type_price1		= $row['total_amount'];
							$total_item_type_paid1 		= $row['total_paid'];
							$total_item_type_remaind1 	= $row['total_remained'];
								
								
						}


						$total_item_type_price  	= $total_item_type_price + $total_item_type_price1;
						$total_item_type_paid  		= $total_item_type_paid + $total_item_type_paid1;
						$total_item_type_remaind  	= $total_item_type_remaind + $total_item_type_remaind1;
						
					}

				}


				$result['total_item_type_price'] 	= $total_item_type_price;
				$result['total_item_type_paid'] 	= $total_item_type_paid;
				$result['total_item_type_remaind'] 	= $total_item_type_remaind;


				array_push($op,$result);

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


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

			$customer_id_fk 	= $_POST['customer_id_fk'];
			
			
			$res = $db->query("
								SELECT									
										customer_full_name,
										
										(SELECT sum(withdraw_amount) FROM capital_withdraw CW  WHERE CW.is_deleted =0 AND CW.gived_to_id_fk = C.customer_id) AS total_amount,

										(SELECT sum(payment_withdraw_amount) FROM capital_withdraw_payments CWP1 WHERE  CWP1.is_deleted = 0  AND CWP1.payment_gived_to_id_fk = C.customer_id) AS total_paid,
										(
										COALESCE((SELECT sum(withdraw_amount) FROM capital_withdraw CW  WHERE CW.is_deleted =0 AND CW.gived_to_id_fk = C.customer_id),0)
										-
										COALESCE((SELECT sum(payment_withdraw_amount) FROM capital_withdraw_payments CWP1 WHERE  CWP1.is_deleted = 0  AND CWP1.payment_gived_to_id_fk = C.customer_id),0)
										 ) AS total_remained
								FROM customer C
						
								WHERE  C.is_deleted = 0  AND C.customer_id='$customer_id_fk'  ");
			
			if ($res) {
				//$row = $res->fetch_all();
				$op  = array();
				$total_item_type_price 		=0;
				$total_item_type_paid	 	=0;

				while ($row = $res->fetch_assoc()) {

					 
					$total_item_type_price		= $row['total_amount'];
					$total_item_type_paid 		= $row['total_paid'];
					$total_item_type_remaind 	= $row['total_remained'];
						
						
				}


				$result['total_item_type_price'] 	= $total_item_type_price;
				$result['total_item_type_paid'] 	= $total_item_type_paid;
				$result['total_item_type_remaind'] 	= $total_item_type_remaind;
				array_push($op,$result);

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

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

			// $cond_inv_id 	= isset($_POST['inv_id'])? ' AND I.invoice_id='.$_POST['inv_id']:'';
			// $cond_cust_id = isset($_POST['cust_id'])? ' AND I.customer_id_fk='.$_POST['cust_id']:'';

			$cond_inv_id 	= '';
			$cond_cust_id 	= '';
			$cond_inv_date 	= '';
			$with_cust_id 	= '';
			$cond_cw_date 	= '';
			$cond_cwp_date 	= '';
			$cond_cust_typ  = '';
			
			if(isset($_POST['inv_id'])){
					$inv_id = $_POST['inv_id'];
					if($inv_id != NULL && $inv_id != ''){
						$cond_inv_id 	=  ' AND I.invoice_id='.$_POST['inv_id'];
					}
			}

			if(isset($_POST['cust_typ'])){
					$cust_typ = $_POST['cust_typ'];
					if($cust_typ != NULL && $cust_typ != ''){
						$cond_cust_typ 	=  ' AND C.customer_type='.$_POST['cust_typ'];
					}
			}

			if(isset($_POST['cust_id'])){
					$cust_id = $_POST['cust_id'];
					if($cust_id != NULL && $cust_id != ''){
						$cond_cust_id 	=  ' AND I.customer_id_fk='.$_POST['cust_id'];
						$with_cust_id 	=  ' AND C.customer_id='.$_POST['cust_id'];
					}
			}



			if(isset($_POST['date_operation'])){

				$first_date = $_POST['inv_date'];
				$last_date 	= $_POST['inv_date_end'];

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

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

				switch ($_POST['date_operation']) {
					case 'E': // equal to date any day of any month of any year // this can be as daily also
						$cond_inv_date 		= ' AND I.invoice_date="'.$first_date.'"';				
						$cond_cw_date 	= ' AND CW.withdraw_date="'.$first_date.'"';		
						$cond_cwp_date 	= ' AND CWP1.payment_withdraw_date="'.$first_date.'"';		
						break;
					case 'L': // less than any date 
						$cond_inv_date 	= ' AND I.invoice_date < "'.$first_date.'"';
						$cond_cw_date 	= ' AND CW.withdraw_date < "'.$first_date.'"';		
						$cond_cwp_date 	= ' AND CWP1.payment_withdraw_date < "'.$first_date.'"';		

						break;
					case 'G': // greater than any date
						$cond_inv_date 	= ' AND I.invoice_date > "'.$first_date.'"';			
						$cond_cw_date 	= ' AND CW.withdraw_date > "'.$first_date.'"';		
						$cond_cwp_date 	= ' AND CWP1.payment_withdraw_date > "'.$first_date.'"';	
						break;
					case 'B': // between specific dates
						$cond_inv_date 	= ' AND I.invoice_date BETWEEN "'.$first_date.'" AND "'.$last_date.'"';
						$cond_cw_date 	= ' AND CW.withdraw_date BETWEEN "'.$first_date.'" AND "'.$last_date.'"';		
						$cond_cwp_date 	= ' AND CWP1.payment_withdraw_date BETWEEN "'.$first_date.'" AND "'.$last_date.'"';
						break;
					case 'Y': // equal for any year
						$cond_inv_date 	= ' AND year(I.invoice_date) = year("'.$first_date.'")';
						$cond_cw_date 	= ' AND year(CW.withdraw_date) = year("'.$first_date.'")';	
						$cond_cwp_date 	= ' AND year(CWP1.payment_withdraw_date) = year("'.$first_date.'")';
						break;	
					case 'M': // equal for any month for any year
						$cond_inv_date 	= ' AND  month(I.invoice_date) = month("'.$first_date.'") AND year(I.invoice_date) = year("'.$first_date.'")';

						$cond_cw_date 	= ' AND  month(CW.withdraw_date) = month("'.$first_date.'") AND year(CW.withdraw_date) = year("'.$first_date.'")';
						$cond_cwp_date 	= ' AND  month(CWP1.payment_withdraw_date) = month("'.$first_date.'") AND year(CWP1.payment_withdraw_date) = year("'.$first_date.'")';	
						break;	
					case 'W': // equal to any week in any month in any year but the first date is sunday
						$cond_inv_date 	= ' AND week(I.invoice_date) = week("'.$first_date.'")';
						$cond_cw_date 	= ' AND week(CW.withdraw_date) = week("'.$first_date.'")';
						$cond_cwp_date 	= ' AND week(CWP1.payment_withdraw_date) = week("'.$first_date.'")';
						// $cond_inv_date 	= ' AND I.invoice_date BETWEEN DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND CURDATE()';
						break;	
					default:
						$cond_inv_date 	= '';
						$cond_cw_date 	= '';
						$cond_cwp_date 	= '';
						break;	
				}
			}
			
			$cond = " AND I.invoice_status < 3 " . $cond_inv_id . " " . $cond_cust_id . " " . $cond_inv_date;

			$cond_CW 	=   $cond_cw_date ;
			$cond_CWP1 	=   $cond_cwp_date ;

			$res = $db->query("

								SELECT item_type_id,item_name,
											(SELECT sum(paid_amount) FROM invoice_payment IP1 
										       LEFT JOIN invoice I ON (I.invoice_type='1'  AND I.is_deleted = 0  AND I.is_single=1 $cond)
										       LEFT JOIN sell_item S ON ( S.is_deleted=0 )
										   WHERE I.invoice_id = IP1.invoice_id_fk AND  IP1.is_returned=0 AND IP1.is_deleted =0 AND IP1.sell_item_id_fk=S.sell_item_id  AND  S.item_type_id_fk = IT.item_type_id) as total_Paid_curtain_single,

											(SELECT sum(sell_item_quantity) FROM sell_item SI 
										       LEFT JOIN invoice I ON (I.invoice_type='1'  AND I.is_deleted = 0  AND I.is_single=1 $cond)
										        WHERE I.invoice_id = SI.invoice_id_fk  AND SI.is_deleted =0   AND  SI.item_type_id_fk = IT.item_type_id) as quantity_single,

										  (SELECT COUNT(*) FROM invoice I  
										  	WHERE  I.invoice_type='1'  AND I.is_deleted = 0  AND I.is_single=1 $cond  
										   ) as No_of_s_inv,


										   (SELECT COUNT(*) FROM invoice I  
										  	WHERE  I.invoice_type='1'  AND I.is_deleted = 0  AND I.is_single=0 $cond  
										   ) as No_of_m_inv,

										   (SELECT COUNT(*) FROM invoice I  
										  	WHERE  I.invoice_type='0'  AND I.is_deleted = 0  AND I.is_single=0 $cond  
										   ) as No_of_raw_inv,

										        
											((SELECT sum(sell_item_detail_height * item_sell_price_per_meter) FROM sell_item_detail SID 
										       LEFT JOIN invoice I ON (I.invoice_type='1'  AND I.is_deleted = 0  AND I.is_single=1 $cond)
										       LEFT JOIN sell_item S ON ( S.is_deleted=0 )
										        WHERE I.invoice_id = SID.invoice_id_fk AND  SID.part_status =0 AND SID.sell_item_id_fk=S.sell_item_id  AND  S.item_type_id_fk = IT.item_type_id AND IT.is_have_curtain=0)
												-
										 	(SELECT sum(actual_discount) FROM sell_item SI 
										       LEFT JOIN invoice I ON (I.invoice_type='1'  AND I.is_deleted = 0  AND I.is_single=1 $cond )
										        WHERE I.invoice_id = SI.invoice_id_fk  AND SI.is_deleted =0   AND  SI.item_type_id_fk = IT.item_type_id AND IT.is_have_curtain=0)
											) as total_cost_single_no_curtain,

											((SELECT sum(sell_item_detail_height * sell_item_detail_width * item_sell_price_per_meter) FROM sell_item_detail SID 
										       LEFT JOIN invoice I ON (I.invoice_type='1'  AND I.is_deleted = 0  AND I.is_single=1 $cond )
										       LEFT JOIN sell_item S ON ( S.is_deleted=0 )
										        WHERE I.invoice_id = SID.invoice_id_fk AND  SID.part_status =0 AND SID.sell_item_id_fk=S.sell_item_id  AND  S.item_type_id_fk = IT.item_type_id AND IT.is_have_curtain=1) 
										 	+
										 
										 	(SELECT sum( sell_item_detail_width * decorate_price_per_meter) FROM sell_item_detail SID 
										       LEFT JOIN invoice I ON (I.invoice_type='1'  AND I.is_deleted = 0  AND I.is_single=1 $cond )
										       LEFT JOIN sell_item S ON ( S.is_deleted=0 )
										        WHERE I.invoice_id = SID.invoice_id_fk AND  SID.part_status =0 AND SID.sell_item_id_fk=S.sell_item_id  AND  S.item_type_id_fk = IT.item_type_id AND IT.is_have_curtain=1)
										 	-
										 	(SELECT sum(actual_discount) FROM sell_item SI 
										       LEFT JOIN invoice I ON (I.invoice_type='1'  AND I.is_deleted = 0  AND I.is_single=1 $cond )
										        WHERE I.invoice_id = SI.invoice_id_fk  AND SI.is_deleted =0   AND  SI.item_type_id_fk = IT.item_type_id AND IT.is_have_curtain=1)
										 	)as total_cost_single_curtain,
										        
										        
											(SELECT sum(paid_amount) FROM invoice_payment IP1 
										       LEFT JOIN invoice I ON (I.invoice_type='1'  AND I.is_deleted = 0  AND I.is_single=0 $cond )
										       LEFT JOIN sell_item S ON ( S.is_deleted=0 )
										        WHERE I.invoice_id = IP1.invoice_id_fk AND  IP1.is_returned=0 AND IP1.is_deleted =0 AND IP1.sell_item_id_fk=S.sell_item_id  AND  S.item_type_id_fk = IT.item_type_id) as total_Paid_curtain_plural,

											(SELECT sum(sell_item_quantity) FROM sell_item SI 
										       LEFT JOIN invoice I ON (I.invoice_type='1'  AND I.is_deleted = 0  AND I.is_single=0 $cond )
										        WHERE I.invoice_id = SI.invoice_id_fk  AND SI.is_deleted =0   AND  SI.item_type_id_fk = IT.item_type_id) as Quantity_plural,

										  ((SELECT sum(sell_item_detail_height * item_sell_price_per_meter) FROM sell_item_detail SID 
										       LEFT JOIN invoice I ON (I.invoice_type='1'  AND I.is_deleted = 0  AND I.is_single=0 $cond )
										       LEFT JOIN sell_item S ON ( S.is_deleted=0 )
										        WHERE I.invoice_id = SID.invoice_id_fk AND  SID.part_status =0 AND SID.sell_item_id_fk=S.sell_item_id  AND  S.item_type_id_fk = IT.item_type_id AND IT.is_have_curtain=0)
												-
										 	(SELECT sum(actual_discount) FROM sell_item SI 
										       LEFT JOIN invoice I ON (I.invoice_type='1'  AND I.is_deleted = 0  AND I.is_single=0 $cond )
										        WHERE I.invoice_id = SI.invoice_id_fk  AND SI.is_deleted =0   AND  SI.item_type_id_fk = IT.item_type_id AND IT.is_have_curtain=0)
											) as total_cost_plural_no_curtain,


											((SELECT sum(sell_item_detail_height * sell_item_detail_width * item_sell_price_per_meter) FROM sell_item_detail SID 
										       LEFT JOIN invoice I ON (I.invoice_type='1'  AND I.is_deleted = 0  AND I.is_single=0 $cond )
										       LEFT JOIN sell_item S ON ( S.is_deleted=0 )
										        WHERE I.invoice_id = SID.invoice_id_fk AND  SID.part_status =0 AND SID.sell_item_id_fk=S.sell_item_id  AND  S.item_type_id_fk = IT.item_type_id AND IT.is_have_curtain=1) 
										 	+
										 
										 	(SELECT sum( sell_item_detail_width * decorate_price_per_meter) FROM sell_item_detail SID 
										       LEFT JOIN invoice I ON (I.invoice_type='1'  AND I.is_deleted = 0  AND I.is_single=0 $cond )
										       LEFT JOIN sell_item S ON ( S.is_deleted=0 )
										        WHERE I.invoice_id = SID.invoice_id_fk AND  SID.part_status =0 AND SID.sell_item_id_fk=S.sell_item_id  AND  S.item_type_id_fk = IT.item_type_id AND IT.is_have_curtain=1)
										 	-
										 	(SELECT sum(actual_discount) FROM sell_item SI 
										       LEFT JOIN invoice I ON (I.invoice_type='1'  AND I.is_deleted = 0  AND I.is_single=0 $cond )
										        WHERE I.invoice_id = SI.invoice_id_fk  AND SI.is_deleted =0   AND  SI.item_type_id_fk = IT.item_type_id AND IT.is_have_curtain=1)
										 	)as total_cost_plural_curtain,

										        
											(SELECT sum(paid_amount) FROM invoice_payment IP1 
										       LEFT JOIN invoice I ON (I.invoice_type='0'  AND I.is_deleted = 0  AND I.is_single=0 $cond )
										       LEFT JOIN sell_item S ON ( S.is_deleted=0 )       
										        WHERE I.invoice_id = IP1.invoice_id_fk AND  IP1.is_returned=0 AND IP1.is_deleted =0 AND IP1.sell_item_id_fk=S.sell_item_id  AND  S.item_type_id_fk = IT.item_type_id) as total_Paid_Raw,

											(SELECT sum(sell_item_quantity) FROM sell_item SI 
										       LEFT JOIN invoice I ON (I.invoice_type='0'  AND I.is_deleted = 0  AND I.is_single=0 $cond )
										        WHERE I.invoice_id = SI.invoice_id_fk  AND SI.is_deleted =0   AND  SI.item_type_id_fk = IT.item_type_id) as Quantity_Raw,


										  ((SELECT sum(sell_item_detail_height * item_sell_price_per_meter) FROM sell_item_detail SID 
										       LEFT JOIN invoice I ON (I.invoice_type='0'  AND I.is_deleted = 0  AND I.is_single=0 $cond )
										       LEFT JOIN sell_item S ON ( S.is_deleted=0 )
										        WHERE I.invoice_id = SID.invoice_id_fk AND  SID.part_status =0 AND SID.sell_item_id_fk=S.sell_item_id  AND  S.item_type_id_fk = IT.item_type_id )
												-
										 	(SELECT sum(actual_discount) FROM sell_item SI 
										       LEFT JOIN invoice I ON (I.invoice_type='0'  AND I.is_deleted = 0  AND I.is_single=0 $cond )
										        WHERE I.invoice_id = SI.invoice_id_fk  AND SI.is_deleted =0   AND  SI.item_type_id_fk = IT.item_type_id )
											) as total_cost_raw,

											(SELECT sum(paid_amount) FROM invoice_payment IP1 
											       LEFT JOIN invoice I ON (I.invoice_type='0'  AND I.is_deleted = 0  AND I.is_single=0  $cond )
											       LEFT JOIN sell_item S ON ( S.is_deleted=0 )       
											        WHERE I.invoice_id = IP1.invoice_id_fk AND  IP1.is_returned=0 AND IP1.is_deleted =0 AND IP1.sell_item_id_fk=S.sell_item_id  AND  S.other_id_fk >0) as total_other_Paid_Raw,

											(SELECT sum(sell_item_quantity) FROM sell_item SI 
										       LEFT JOIN invoice I ON (I.invoice_type='0'  AND I.is_deleted = 0  AND I.is_single=0 $cond )
										        WHERE I.invoice_id = SI.invoice_id_fk  AND SI.is_deleted =0   AND  SI.other_id_fk >0) as Quantity_other_Raw,

										  ((SELECT sum(sell_item_quantity * item_sell_price_per_meter) FROM sell_item S  
										       LEFT JOIN invoice I ON (I.invoice_type='0'  AND I.is_deleted = 0  AND I.is_single=0 $cond )
										        WHERE I.invoice_id = S.invoice_id_fk AND  S.other_id_fk >0 AND S.is_deleted=0)
												-
										 	(SELECT sum(actual_discount) FROM sell_item SI 
										       LEFT JOIN invoice I ON (I.invoice_type='0'  AND I.is_deleted = 0  AND I.is_single=0 $cond )
										        WHERE I.invoice_id = SI.invoice_id_fk  AND SI.is_deleted =0   AND  SI.other_id_fk >0 )
											) as total_cost_other_raw,


											(SELECT sum(paid_amount) FROM invoice_payment IP1 
											       LEFT JOIN invoice I ON (I.invoice_type='1'  AND I.is_deleted = 0  AND I.is_single=1  $cond )
											       LEFT JOIN sell_item S ON ( S.is_deleted=0 )       
											        WHERE I.invoice_id = IP1.invoice_id_fk AND  IP1.is_returned=0 AND IP1.is_deleted =0 AND IP1.sell_item_id_fk=S.sell_item_id  AND  S.other_id_fk >0) as total_other_Paid_S,

											(SELECT sum(sell_item_quantity) FROM sell_item SI 
										       LEFT JOIN invoice I ON (I.invoice_type='1'  AND I.is_deleted = 0  AND I.is_single=1 $cond )
										        WHERE I.invoice_id = SI.invoice_id_fk  AND SI.is_deleted =0   AND  SI.other_id_fk >0) as Quantity_other_S,

										  ((SELECT sum(sell_item_quantity * item_sell_price_per_meter) FROM sell_item S  
										       LEFT JOIN invoice I ON (I.invoice_type='1'  AND I.is_deleted = 0  AND I.is_single=1 $cond )
										        WHERE I.invoice_id = S.invoice_id_fk AND  S.other_id_fk >0 AND S.is_deleted=0)
												-
										 	(SELECT sum(actual_discount) FROM sell_item SI 
										       LEFT JOIN invoice I ON (I.invoice_type='1'  AND I.is_deleted = 0  AND I.is_single=1 $cond )
										        WHERE I.invoice_id = SI.invoice_id_fk  AND SI.is_deleted =0   AND  SI.other_id_fk >0 )
											) as total_cost_other_S,


											(SELECT sum(paid_amount) FROM invoice_payment IP1 
											       LEFT JOIN invoice I ON (I.invoice_type='1'  AND I.is_deleted = 0  AND I.is_single=0  $cond )
											       LEFT JOIN sell_item S ON ( S.is_deleted=0 )       
											        WHERE I.invoice_id = IP1.invoice_id_fk AND  IP1.is_returned=0 AND IP1.is_deleted =0 AND IP1.sell_item_id_fk=S.sell_item_id  AND  S.other_id_fk >0) as total_other_Paid_M,

											(SELECT sum(sell_item_quantity) FROM sell_item SI 
										       LEFT JOIN invoice I ON (I.invoice_type='1'  AND I.is_deleted = 0  AND I.is_single=0 $cond )
										        WHERE I.invoice_id = SI.invoice_id_fk  AND SI.is_deleted =0   AND  SI.other_id_fk >0) as Quantity_other_M,

										  ((SELECT sum(sell_item_quantity * item_sell_price_per_meter) FROM sell_item S  
										       LEFT JOIN invoice I ON (I.invoice_type='1'  AND I.is_deleted = 0  AND I.is_single=0 $cond )
										        WHERE I.invoice_id = S.invoice_id_fk AND  S.other_id_fk >0 AND S.is_deleted=0)
												-
										 	(SELECT sum(actual_discount) FROM sell_item SI 
										       LEFT JOIN invoice I ON (I.invoice_type='1'  AND I.is_deleted = 0  AND I.is_single=0 $cond )
										        WHERE I.invoice_id = SI.invoice_id_fk  AND SI.is_deleted =0   AND  SI.other_id_fk >0 )
											) as total_cost_other_M

																		        
								FROM item_type IT 			WHERE IT.is_deleted=0					");

			if ($res) {
				//$row = $res->fetch_all();
				$op  = array('sell'=>array(),'withdraw'=>array());
				while ($row = $res->fetch_assoc()) {

						$total_cost_single = $row['total_cost_single_no_curtain'] + $row['total_cost_single_curtain'];
						$total_cost_plural = $row['total_cost_plural_no_curtain'] + $row['total_cost_plural_curtain'];
				   
						$result['item_type_id']  				= $row['item_type_id'];
						$result['item_name']  					= $row['item_name'];
						
						$result['total_Paid_curtain_single']  	= $row['total_Paid_curtain_single'];
						$result['quantity_single']  			= $row['quantity_single'];
						$result['total_cost_single']  			= $total_cost_single;
						
						$result['total_Paid_curtain_plural']  	= $row['total_Paid_curtain_plural'];
						$result['Quantity_plural']  			= $row['Quantity_plural'];
						$result['total_cost_plural']  			= $total_cost_plural;
						
						$result['total_Paid_Raw']  				= $row['total_Paid_Raw'];
						$result['Quantity_Raw']  				= $row['Quantity_Raw'];
						$result['total_cost_raw']  				= $row['total_cost_raw'];
						
						$result['total_remain_raw']  			= $row['total_cost_raw'] - $row['total_Paid_Raw'];
						
						$result['total_remain_s']  				= $total_cost_single - $row['total_Paid_curtain_single'];
						
						$result['total_remain_m']  				= $total_cost_plural - $row['total_Paid_curtain_plural'];
						

						$result['No_of_s_inv']  				= $row['No_of_s_inv'];
						$result['No_of_m_inv']  				= $row['No_of_m_inv'];
						$result['No_of_raw_inv']  				= $row['No_of_raw_inv'];
						
						$result['total_other_Paid_Raw']  		= $row['total_other_Paid_Raw'];
						$result['Quantity_other_Raw']  			= $row['Quantity_other_Raw'];
						$result['total_cost_other_raw']  		= $row['total_cost_other_raw'];

						$result['total_other_Paid_S']  			= $row['total_other_Paid_S'];
						$result['Quantity_other_S']  			= $row['Quantity_other_S'];
						$result['total_cost_other_S']  			= $row['total_cost_other_S'];

						$result['total_other_Paid_M']  			= $row['total_other_Paid_M'];
						$result['Quantity_other_M']  			= $row['Quantity_other_M'];
						$result['total_cost_other_M']  			= $row['total_cost_other_M'];

						$result['total_remain_other_raw']  		= $row['total_cost_other_raw'] - $row['total_other_Paid_Raw'];
						
						$result['total_remain_other_s']  		= $row['total_cost_other_S'] - $row['total_other_Paid_S'];
						
						$result['total_remain_other_m']  		= $row['total_cost_other_M'] - $row['total_other_Paid_M'];
						

						array_push($op['sell'],$result);
				}


				// now we should get withdraw totals

				$res = $db->query("
								SELECT									
										customer_full_name,
										
										COALESCE((SELECT count(withdraw_amount) FROM capital_withdraw CW  WHERE CW.is_deleted =0 AND CW.gived_to_id_fk = C.customer_id  $cond_CW),0) AS total_withdraw_no,

										COALESCE((SELECT count(payment_withdraw_amount) FROM capital_withdraw_payments CWP1 WHERE  CWP1.is_deleted = 0  AND CWP1.payment_gived_to_id_fk = C.customer_id  $cond_CWP1),0) AS total_paid_no,

										COALESCE((SELECT sum(withdraw_amount) FROM capital_withdraw CW  WHERE CW.is_deleted =0 AND CW.gived_to_id_fk = C.customer_id  $cond_CW),0) AS total_amount,

										COALESCE((SELECT sum(payment_withdraw_amount) FROM capital_withdraw_payments CWP1 WHERE  CWP1.is_deleted = 0  AND CWP1.payment_gived_to_id_fk = C.customer_id  $cond_CWP1),0) AS total_paid,
										(
										COALESCE((SELECT sum(withdraw_amount) FROM capital_withdraw CW  WHERE CW.is_deleted =0 AND CW.gived_to_id_fk = C.customer_id  $cond_CW),0)
										-
										COALESCE((SELECT sum(payment_withdraw_amount) FROM capital_withdraw_payments CWP1 WHERE  CWP1.is_deleted = 0  AND CWP1.payment_gived_to_id_fk = C.customer_id  $cond_CWP1),0)
										 ) AS total_remained
								FROM customer C
						
								WHERE  C.is_deleted = 0  $with_cust_id  $cond_cust_typ");
			
			if ($res) {
				
				$total_item_type_price 		=0;
				$total_item_type_paid	 	=0;

				while ($row = $res->fetch_assoc()) {

					 
					$result_w['total_withdraw_price']		= $row['total_amount'];
					$result_w['total_withdraw_paid'] 		= $row['total_paid'];
					$result_w['total_withdraw_remaind'] 	= $row['total_remained'];
					$result_w['total_withdraw_no'] 			= $row['total_withdraw_no'];
					$result_w['total_paid_no']			 	= $row['total_paid_no'];
					$result_w['customer_full_name']			= $row['customer_full_name'];
					
					array_push($op['withdraw'],$result_w);

						
				}



				
			}

				echo json_encode($op);
				exit;
			}else{
				echo 0;
				echo $db->error;
				exit;
			}
		}

	

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

			$id 						= $_POST['customer_id_fk'];
			$paid_amount				=addslashes($_POST["paid_amount"]);
			$paid_date					=addslashes($_POST["paid_date"]);
			$payment_note				=addslashes($_POST["payment_note"]);
			
			$item_type_id_fk 	= $_POST['item_type_id_fk'];

			$cond = '';
			if($item_type_id_fk != '' && $item_type_id_fk != NULL){
				$cond = " AND S.item_type_id_fk = '$item_type_id_fk' ";
			}

			$rnd_no						=rand(100000000,1000000000);
			$invoice_currency			= 1; //USD
			$reg_date 					= date("Y-m-d h:i:s", time());
			
			$next_payment_date 			='';
			$next_payment_amount 		= '';

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

			if($paid_amount <= 0 || $paid_amount == '' || $paid_amount == NULL || !is_numeric($paid_amount)){
				$op['is_success'] = false;
				$op['data'] 	  = "الرجاء قم بادخال دفعة صحيحة";
				echo json_encode($op);
				exit;
			}
			

			$remain_after_paying = $paid_amount;
			
			$is_added = false; // to check may this customer has all invoices paid so the insertion will not happend
			$successfully = false;

		// check if the select type is add withdraw just
		if($item_type_id_fk != 'w'){
				
			

			// here we will bring all invoices for the current customer that not paid
			$res = $db->query("SELECT invoice_id, 
									invoice_number, 						
									invoice_status,  
									sell_item_quantity, 
									customer_id_fk,  
									actual_discount,
									item_sell_price_per_meter,
									decorate_price_per_meter, 
									is_paid,
									S.item_type_id_fk, 
								  	other_id_fk, 
								  	S.product_id_fk,
                                    sell_item_id,
								    customer_full_name,
															 
									CONCAT(COALESCE(erbil_code,''), COALESCE(other_name,'')) 
									AS product_name,

									(SELECT sum(paid_amount) FROM invoice_payment IP WHERE I.invoice_id = IP.invoice_id_fk AND IP.is_returned=0 AND IP.is_deleted =0) 
									AS total_paid,
																
									(SELECT sum(actual_discount) FROM sell_item SI WHERE I.invoice_id = SI.invoice_id_fk AND SI.is_deleted =0) 
									AS total_discount,

									
									(

									COALESCE((SELECT sum(sell_item_detail_height * item_sell_price_per_meter) FROM sell_item_detail SID, sell_item S 
											          			
									WHERE I.invoice_id = SID.invoice_id_fk AND S.invoice_id_fk = I.invoice_id AND S.is_deleted=0 AND  SID.part_status =0 AND SID.is_deleted=0 AND S.sell_item_id = SID.sell_item_id_fk AND S.other_id_fk =0 AND SID.sell_item_detail_width = 0),0)
									+
									COALESCE((SELECT sum(sell_item_detail_height * sell_item_detail_width * item_sell_price_per_meter) FROM sell_item_detail SID, sell_item S 
											          			
									WHERE I.invoice_id = SID.invoice_id_fk AND S.invoice_id_fk = I.invoice_id AND S.is_deleted=0 AND  SID.part_status =0 AND SID.is_deleted=0 AND S.sell_item_id = SID.sell_item_id_fk AND S.other_id_fk =0 AND SID.sell_item_detail_width > 0),0)
									+
									COALESCE((SELECT sum(sell_item_detail_width * decorate_price_per_meter) FROM sell_item_detail SID, sell_item S 
											          			
									WHERE I.invoice_id = SID.invoice_id_fk AND S.invoice_id_fk = I.invoice_id AND S.is_deleted=0 AND  SID.part_status =0 AND SID.is_deleted=0 AND S.sell_item_id = SID.sell_item_id_fk AND S.other_id_fk =0 AND SID.sell_item_detail_width > 0),0)

									+
									COALESCE((SELECT sum(sell_item_quantity * item_sell_price_per_meter) FROM sell_item S  
									WHERE I.invoice_id = S.invoice_id_fk AND  S.other_id_fk >0 AND S.is_deleted=0),0)
									-
									(SELECT sum(actual_discount) from sell_item where sell_item.invoice_id_fk = I.invoice_id AND sell_item.is_deleted=0 )
																
									) 
									AS total_invoice_price,


									(SELECT sum(sell_item_detail_height) FROM sell_item_detail SID WHERE SID.invoice_id_fk = I.invoice_id AND SID.sell_item_id_fk=S.sell_item_id AND part_status=0 AND SID.is_deleted = 0) 
									AS total_length,

									(SELECT sum(paid_amount) FROM invoice_payment IP1 WHERE				   I.invoice_id = IP1.invoice_id_fk AND IP1.sell_item_id_fk=S.sell_item_id AND IP1.is_returned=0 AND IP1.is_deleted =0) 
									AS total_paid_per_product,
																													
													
									(SELECT sum(sell_item_detail_height * sell_item_detail_width) FROM sell_item_detail SID WHERE SID.invoice_id_fk = I.invoice_id AND SID.sell_item_id_fk=S.sell_item_id AND part_status=0 AND SID.is_deleted = 0 AND SID.sell_item_detail_width > 0) 
									AS total_area,
																													
									(SELECT sum(sell_item_detail_width) FROM sell_item_detail SID WHERE SID.invoice_id_fk = I.invoice_id AND SID.sell_item_id_fk=S.sell_item_id AND part_status=0 AND SID.is_deleted = 0 AND SID.sell_item_detail_width > 0) 
									AS total_decor_width


								FROM invoice I
							 		LEFT JOIN sell_item S ON (S.invoice_id_fk = I.invoice_id AND S.is_deleted=0  $cond)
							 		LEFT JOIN others O ON (S.other_id_fk = O.other_id AND S.other_id_fk >0)
									LEFT JOIN product P ON (S.product_id_fk = P.product_id )
									LEFT JOIN customer C ON (I.customer_id_fk = C.customer_id )
							 	WHERE I.customer_id_fk='$id'  AND I.is_deleted = 0 AND I.invoice_status <2
							 	");
			
			
			while ($row = $res->fetch_assoc()) {
				
				$total_row_price = 0;

				
                
                if($row['other_id_fk'] > 0){
                  $total_row_price = $row['sell_item_quantity'] * $row['item_sell_price_per_meter'] - $row['actual_discount'];
                }else{
                  
                  if($row['total_area'] > 0 && $row['total_area'] != NULL){ // it mean its curtain
                    $total_row_price = $row['total_area'] * $row['item_sell_price_per_meter'] + $row['total_decor_width'] * $row['decorate_price_per_meter'] - $row['actual_discount'];
                  }else{
                    $total_row_price = $row['total_length'] * $row['item_sell_price_per_meter'] - $row['actual_discount'];
                  }
                } 






                $remainded_per_row = $total_row_price - $row['total_paid_per_product'];

                // now we should check every row in each invoice and see if its has remain if it has remain we should add amount from paid_amout and update its status and the invoice status also

                if($remain_after_paying > 0){ // check if we have paid_amout and its not finished

	                if($remainded_per_row > 0){ // it have remaind we will add payment

	                	// now we should check if the paid_amout more or less the remainded_per_row

	                	if($remain_after_paying >= $remainded_per_row ){ // that mean the paid_amout greater than this row remaind and we can make another adding to defferent rows
	                		$remain_after_paying = $remain_after_paying - $remainded_per_row;
	                		$row_new_payment 	 = $remainded_per_row;
	                	}else{ // that mean the paid_amount less than the remained so we will make it 0 and insert all the paid_amount in this row
	                		
	                		$row_new_payment 	 = $remain_after_paying;
	                		$remain_after_paying = 0;
	                	}

	                	$sell_id 	= $row['sell_item_id'];
	                	$inv_id_fk 	= $row['invoice_id'];
	                	$cust_id_fk = $row['customer_id_fk'];

	                	// here the insertion & update of payment will be done

	                	$lastaction 				="Add Payment for Invoice";

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

						$sql ="INSERT INTO invoice_payment (pay_usd_rate, 
															pay_currency, 
															paid_amount,
															paid_date,
															next_payment_date,
															next_payment_amount,
															payment_note,
															sell_item_id_fk, 
															invoice_id_fk,
															customer_id_fk, 
															group_no, 
															created_by, 
															last_action, 
															last_action_by)
	 											VALUES(	'".$_SESSION['USD_TO_IQD']."',
 															'$invoice_currency',
 															'$row_new_payment',
 														 	'$reg_date',
 														 	'$next_payment_date',
 														 	'$next_payment_amount',
 														 	'$payment_note',
 														 	'$sell_id',
 														 	'$inv_id_fk',
 														 	'$cust_id_fk',
 														 	'$rnd_no',
 														 	'".$_SESSION['user_id']."',
 														 	'$lastaction',
 														 	'".$_SESSION['user_id']."')";
						
						$res_pay = $db->query($sql);
						
						if ($res_pay){

							// we should update sell_item pay status 

							$res1 = $db->query("SELECT 	sell_item_id,
														invoice_total_price_actual, 					  
														sell_item_quantity, 
														actual_discount,
														item_sell_price_per_meter,
														decorate_price_per_meter, 
														is_paid,
														(SELECT sum(paid_amount) FROM invoice_payment IP WHERE I.invoice_id = IP.invoice_id_fk AND IP.is_returned=0 AND IP.is_deleted =0) AS total_paid,

														(SELECT sum(sell_item_detail_height) FROM sell_item_detail SID WHERE SID.invoice_id_fk = I.invoice_id AND SID.sell_item_id_fk=S.sell_item_id AND part_status=0 AND SID.is_deleted = 0) AS total_length,


														(SELECT sum(paid_amount) FROM invoice_payment IP1 WHERE I.invoice_id = IP1.invoice_id_fk AND IP1.sell_item_id_fk=S.sell_item_id AND IP1.is_returned=0 AND IP1.is_deleted =0 ) AS total_paid_per_product,
    																				
														(SELECT sum(sell_item_detail_height * sell_item_detail_width) FROM sell_item_detail SID WHERE SID.invoice_id_fk = I.invoice_id AND SID.sell_item_id_fk=S.sell_item_id AND part_status=0 AND SID.is_deleted = 0 AND SID.sell_item_detail_width > 0) AS total_area,
																							
														(SELECT sum(sell_item_detail_width) FROM sell_item_detail SID WHERE SID.invoice_id_fk = I.invoice_id AND SID.sell_item_id_fk=S.sell_item_id AND part_status=0 AND SID.is_deleted = 0 AND SID.sell_item_detail_width > 0) AS total_decor_width,

														(

											          	COALESCE((SELECT sum(sell_item_detail_height * item_sell_price_per_meter) FROM sell_item_detail SID, sell_item S 
																		          			
											        	WHERE I.invoice_id = SID.invoice_id_fk AND S.invoice_id_fk = I.invoice_id AND S.is_deleted=0 AND  SID.part_status =0 AND SID.is_deleted=0 AND S.sell_item_id = SID.sell_item_id_fk AND S.other_id_fk =0 AND SID.sell_item_detail_width = 0),0)
												    	+
												   		COALESCE((SELECT sum(sell_item_detail_height * sell_item_detail_width * item_sell_price_per_meter) FROM sell_item_detail SID, sell_item S 
																		          			
											        	WHERE I.invoice_id = SID.invoice_id_fk AND S.invoice_id_fk = I.invoice_id AND S.is_deleted=0 AND  SID.part_status =0 AND SID.is_deleted=0 AND S.sell_item_id = SID.sell_item_id_fk AND S.other_id_fk =0 AND SID.sell_item_detail_width > 0),0)
												    	+
												   		COALESCE((SELECT sum(sell_item_detail_width * decorate_price_per_meter) FROM sell_item_detail SID, sell_item S 
																		          			
										        		WHERE I.invoice_id = SID.invoice_id_fk AND S.invoice_id_fk = I.invoice_id AND S.is_deleted=0 AND  SID.part_status =0 AND SID.is_deleted=0 AND S.sell_item_id = SID.sell_item_id_fk AND S.other_id_fk =0 AND SID.sell_item_detail_width > 0),0)

												    	+
												    	COALESCE((SELECT sum(sell_item_quantity * item_sell_price_per_meter) FROM sell_item S  
														WHERE I.invoice_id = S.invoice_id_fk AND  S.other_id_fk >0 AND S.is_deleted=0),0)
														-
												 		(SELECT sum(actual_discount) from sell_item where sell_item.invoice_id_fk = I.invoice_id AND sell_item.is_deleted=0 )
														) AS total_invoice_price

													FROM invoice I
			 										LEFT JOIN sell_item S ON (S.invoice_id_fk = I.invoice_id AND S.is_deleted=0 AND S.sell_item_id='$sell_id')
										 		
			 										WHERE I.invoice_id='$inv_id_fk'  AND I.is_deleted = 0");

							$row1 = $res1->fetch_assoc();

							$total_length 					= $row1['total_length'];
							$total_paid_per_product 		= $row1['total_paid_per_product'];
							$total_payments					= $row1['total_paid'];
							$net_balance 					= $row1['total_invoice_price'];
							$sell_item_quantity 			= $row1['sell_item_quantity'];
							$actual_discount 				= $row1['actual_discount'];
							$item_sell_price_per_meter 		= $row1['item_sell_price_per_meter'];
							$decorate_price_per_meter 		= $row1['decorate_price_per_meter'];
							$is_paid 						= $row1['is_paid'];
							$total_area 					= $row1['total_area'];
							$total_decor_width 				= $row1['total_decor_width'];
							
							// compute total product price

							$total_product_price = 0;
							
							
							
							if($total_length > 0 && $total_length !=NULL){

								if($total_area > 0 && $total_area !=NULL){ // it mean curtain
									$total_product_price = ($total_area * $item_sell_price_per_meter) + ($total_decor_width * $decorate_price_per_meter) -  $actual_discount;
								}else{
									$total_product_price = ($total_length * $item_sell_price_per_meter) -  $actual_discount;
								}

							}else{
								$total_product_price = ($sell_item_quantity * $item_sell_price_per_meter) -  $actual_discount;
							}

							if($total_paid_per_product >= $total_product_price ){
								$is_paid_sell = 2;
							}elseif($total_paid_per_product < $total_product_price AND $total_paid_per_product >0 ){
								$is_paid_sell = 1;
							}else{
								$is_paid_sell = 0;
							}

							$lastaction = "Update Product Payment Status after adding Payment for customer";

							$update_qry = "UPDATE sell_item SET  is_paid='$is_paid_sell',last_action='$lastaction',last_action_by='".$_SESSION['user_id']."' WHERE invoice_id_fk = $inv_id_fk AND sell_item_id='$sell_id' ";

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

							if ($res2) {

								// update invoice status
											
								// echo "remainded_per_row=".$remainded_per_row.";;;";
								// echo "total_product_price=".$total_product_price.";;;";
								// echo "total_paid_per_product=".$total_paid_per_product.";;;";
								// echo "row_new_payment=".$row_new_payment.";;;";

								$lastaction = "Update Invoice status after Adding payments for customer";

								if($total_payments >= $net_balance ){
									$is_paid = 2; // complete paid
									$pay_term = 0;
									$update_qry1 = "UPDATE invoice SET  invoice_status='$is_paid',pay_terms='$pay_term',last_action='$lastaction',
									last_action_by='".$_SESSION['user_id']."' WHERE invoice_id = $inv_id_fk";

								}elseif($total_payments < $net_balance AND $total_payments >0 ){
									$is_paid = 1; // partially paid
									$update_qry1 = "UPDATE invoice SET  invoice_status='$is_paid',last_action='$lastaction',
									last_action_by='".$_SESSION['user_id']."' WHERE invoice_id = $inv_id_fk";
								}
								// echo "$total_payments= ".$total_payments . "  $net_balance=".$net_balance;
								$res3 = $db->query($update_qry1);

								if ($res3) {
									$db->commit();
									
									$successfully = true;
									// exit;

								}else{
									$db->rollback();
									$op['is_success'] = false;
									$op['data'] 	  = "حصل خطأ : لم يتم تحديث حالة الدفع للوصل ";
									echo json_encode($op);
									exit;
								}
										
							}else{
								// $op['data'] 	  = $db->error;

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


	                	// end insertion & update of payment


	                } // if remainded_per_row
	            
	            }else{ // if remain_after_paying
	            		// that mean the paid amount was finished and we should stop the while and exit 

	            	if($successfully == false){
						$op['is_success'] = false;
						$op['data'] 	  = "الرجاء قم بادخال دفعة واحدة على الاقل";
						echo json_encode($op);
						exit;
					}else{
						$op['is_success'] = true;
						$op['data'] 	  = "تمت عملية الاضافة بنجاح";
						echo json_encode($op);
						exit;
					}

	            } 

				
						
			} // while

		} // if item_type_id_fk !=w


			// here after we add the payment to all invoice we should check if still remaind money we will add it to withdraw

			$res1 = $db->query("SELECT customer_id,
									   (COALESCE((SELECT sum(withdraw_amount) FROM capital_withdraw CW WHERE CW.is_deleted =0 AND CW.gived_to_id_fk =C.customer_id),0)
									   -
									   COALESCE((SELECT sum(payment_withdraw_amount) FROM capital_withdraw_payments CWP1 WHERE  CWP1.is_deleted = 0 AND CWP1.payment_gived_to_id_fk = C.customer_id ),0)
									   ) AS total_withdraw_remaid

								FROM customer C 
										 		
								WHERE C.customer_id='$id'  AND C.is_deleted = 0");

			$row1 = $res1->fetch_assoc();

			$remainded_per_row = $row1['total_withdraw_remaid'];

			$before_add_withdraw = $remain_after_paying ;
			


			if($remain_after_paying > 0){ // check if we have paid_amout and its not finished

                if($remainded_per_row > 0){ // it have remaind we will add payment

                	// now we should check if the paid_amout more or less the remainded_per_row

                	if($remain_after_paying >= $remainded_per_row ){ // that mean the paid_amout greater than this row remaind and we can make another adding to defferent rows
                		$remain_after_paying = $remain_after_paying - $remainded_per_row;
                		$row_new_payment 	 = $remainded_per_row;
                	}else{ // that mean the paid_amount less than the remained so we will make it 0 and insert all the paid_amount in this row
                		
                		$row_new_payment 	 = $remain_after_paying;
                		$remain_after_paying = 0;
                	}

     //            	echo $remainded_per_row . " # ";
     //            	echo $row_new_payment . " # ";
					// echo $remain_after_paying;

                	$lastaction = "Add New Payment to Capital_withdraw";
                	

					$sql_1 ="INSERT INTO capital_withdraw_payments (payment_withdraw_amount,
												payment_withdraw_date,
												payment_withdraw_usd_rate,
												payment_withdraw_currency,
												payment_withdraw_note,
												payment_gived_to_id_fk,
												created_by,
												last_action,
												last_action_by)
										VALUES( '$row_new_payment',
												'$paid_date',
												'".$_SESSION['USD_TO_IQD']."',
												'1',
												'$payment_note',
												'$id',
												'".$_SESSION['user_id']."',
												'$lastaction',
												'".$_SESSION['user_id']."')";

					$res_1 = $db->query($sql_1);

					if ($res_1) {
							$db->commit();
							if($remain_after_paying > 0){
								$op['is_success'] = true;
								$op['data'] 	  = "تمت عملية الاضافة بنجاح .. لكن المبلغ المدفوع اكبر من المبلغ الكلي المتبقي ولذلك لم يتم اضافة هذا المبلغ :";
								$op['amount'] 	  = $remain_after_paying;
								echo json_encode($op);
								exit;
							}else{
								$op['is_success'] = true;
								$op['data'] 	  = "تمت عملية الاضافة بنجاح";
								echo json_encode($op);
								exit;
							}
					}else{
							
							$db->rollback();
							// if finish while loop that mean this customer has all invoices paid and we didnt add anything
							if($successfully == false){
								$op['is_success'] = false;
								$op['data'] 	  = "حصل خطأ: الرجاء اعادة تحميل الصفحة والمحاولة من جديد";
								echo json_encode($op);
								exit;


							}else{

								
								$op['is_success'] = false;
								$op['data'] 	  = "تمت عملية الاضافة للمبيعات فقط ..لكن حصل خطأ اثناء اضافة دفعة للقروض.. والمبلغ المتبقي وغير المضاف هو :";
								$op['amount'] 	  = $before_add_withdraw;
								echo json_encode($op);
								exit;
								
								
							}
					}

                }
            }

			
			// if finish while loop that mean this customer has all invoices paid and we didnt add anything
			if($successfully == false){
				$op['is_success'] = false;
				$op['data'] 	  = "هذا الزبون حسابه كامل ولم يكن عليه اي باقي.. الرجاء التحقق من كشوفات الحساب له";
				echo json_encode($op);
				exit;

			}else{

				if($remain_after_paying > 0){
					$op['is_success'] = true;
					$op['data'] 	  = "تمت عملية الاضافة بنجاح .. لكن المبلغ المدفوع اكبر من المبلغ الكلي المتبقي ولذلك لم يتم اضافة هذا المبلغ :";
					$op['amount'] 	  = $remain_after_paying;
					echo json_encode($op);
					exit;
				}else{
					$op['is_success'] = true;
					$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 Product";

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

			$res = $db->query("SELECT * FROM sell_roll_detail WHERE is_deleted=0 AND product_id_fk = '$id'");
			if (mysqli_num_rows($res)>0) {
				$op['is_success'] = false;
				$op['data'] 	  = "لا يمكن حذف هذه المادة فقط تم بيع البعض منها";
				echo json_encode($op);
				exit;
			}

			$res = $db->query("SELECT * FROM purchase_item_detail WHERE is_deleted=0 AND product_id_fk = '$id'");
			if (mysqli_num_rows($res)>0) {
				$op['is_success'] = false;
				$op['data'] 	  = "لا يمكن حذف هذه المادة فهي تحوي على قيم في جدول المشتريات !!";
				echo json_encode($op);
				exit;
			}

			

	  		$res = $db->query("UPDATE product SET is_deleted = 1 ,deleted_at = '$reg_date',last_action='$lastaction',last_action_by='".$_SESSION['user_id']."' WHERE product_id=$id");
		    	if ($res){
		        		$op['is_success'] = true;
						$op['data'] 	  = "تمت عملية الحذف بنجاح";
						echo json_encode($op);
						exit;
		     	}else{
		      			$op['is_success'] = false;
						$op['data'] 	  = "حصل خطأ : السجل في امان";
						echo json_encode($op);
						exit;
		     	}
		}

		if ($_POST['type'] == "create") {
      		
      		$employe_id_fk		=addslashes($_POST["employe_id_fk"]);
			$year_id_fk			=addslashes($_POST["year_id_fk"]);
			$month_id_fk		=addslashes($_POST["month_id_fk"]);
			$day_id_fk			=addslashes($_POST["day_id_fk"]);
			$hours				=addslashes($_POST["hours"]);
			$time_in			=addslashes($_POST["time_in"]);
			$time_out			=addslashes($_POST["time_out"]);
			$is_subtracted		=addslashes($_POST["is_subtracted"]);
			$work_note			=addslashes($_POST["work_note"]);
			$trans				=addslashes($_POST["trans"]);

			$hour_transaction   ='';

			switch ($trans) {
				case 'absent':
					$hour_transaction   ='Absent';
					break;

				case 'absent_with_per':
					$hour_transaction   ='Absent With Permission';
					break;

				case 'sick':
					$hour_transaction   ='Sick With Permission';
					break;

				case 'attend':
					$hour_transaction   ='Attend';
					$hours				='8';

					$res = $db->query("SELECT * FROM work_hours WHERE is_deleted=0 AND employe_id_fk = '$employe_id_fk' AND year_id_fk = '$year_id_fk' AND month_id_fk = '$month_id_fk' AND day_id_fk = '$day_id_fk' AND hour_transaction = '$hour_transaction'");
					if (mysqli_num_rows($res)>0) {
							$op['is_success'] = false;
							$op['data'] 	  = "خطأ لقد تم تسجيل حضور الموظف لهذا اليوم مسبقا";
							echo json_encode($op);
							exit;
					}
					break;

				case 'extra':
					$hour_transaction   ='Extra Hours';
					break;

				case 'reward':
					$hour_transaction   ='Reward';
					break;

				case 'penalty':
					$hour_transaction   ='Penalty';
					break;

				case 'loan':
					$hour_transaction   ='Loan';
					break;

				case 'refund':
					$hour_transaction   ='Refund';
					break;

				case 'salary':
					$hour_transaction   ='Salary';

					$res = $db->query("SELECT * FROM work_hours WHERE is_deleted=0 AND employe_id_fk = '$employe_id_fk' AND year_id_fk = '$year_id_fk' AND month_id_fk = '$month_id_fk' AND hour_transaction = '$hour_transaction'");
					if (mysqli_num_rows($res)>0) {
							$op['is_success'] = false;
							$op['data'] 	  = "خطأ لقد تم تحديد الراتب لهذا الشهر مسبقا الرجاء قم بتحريره لانه لايمكن اضافة اكثر من راتب لنفس الشهر";
							echo json_encode($op);
							exit;
					}

					break;
				
				
			}
			

			

				$delete = 0;
				$reg_date = date("Y-m-d h:i:s", time());
				$lastaction = "Add new work action to employee";

				$sql ="INSERT INTO work_hours (employe_id_fk,
											   year_id_fk,
											   month_id_fk,
											   day_id_fk,
											   hours,
											   time_in,
											   time_out,
											   is_subtracted,
											   work_note,
											   hour_transaction,
											   created_by,
											   created_at,
											   updated_at,
											   last_action,
											   last_action_by)
										VALUES('$employe_id_fk',
											   '$year_id_fk',
											   '$month_id_fk',
											   '$day_id_fk',
											   '$hours',
											   '$time_in',
											   '$time_out',
											   '$is_subtracted',
											   '$work_note',
											   '$hour_transaction',
											   '".$_SESSION['user_id']."',
											   '$reg_date',
											   '$reg_date',
											   '$lastaction',
											   '".$_SESSION['user_id']."')";

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

					if ($res) {

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

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