<?php
include("includes/dbconfig.php");

date_default_timezone_set("Africa/Kampala");

function employees($id=false){
	$where ='';
	if($id){
		//$where .=' id = '.$id;
		
	}else {
		//$where .=' role > 1 ' ;
	}	
	return  DB::query(' SELECT *FROM personnel_details     '.$where.'' ); 
	//$categorydata = DB::query("SELECT quantity FROM item_stock_level WHERE item=%i AND shop =%i"  , $item , $shop);
	 
}

function bar_code(){
	$code =  time().rand(10,99);
	$check = intval(DB::queryFirstField('SELECT id  FROM job_receipt where receipt_number ="'.$code.'"' )); 
	if($check > 1){
		bar_code();
	}else return $code;
		
}

function bar_code_job(){
	$code =  time().rand(10,99);
	$check = intval(DB::queryFirstField('SELECT id  FROM job_sale_item where local_bar_code ="'.$code.'"' )); 
	if($check > 0){
		 return  bar_code_job();
	}else return $code;
		
}

                             	function payment_types($from, $to , $shop){
									$data ='';
									$cats =  DB::query('  SELECT      *from payment_type group by id');
                                     $cash_at_hand = 0;
   								      $exp_total = 0;
									if(!empty($cats)){
										$total = 0;
										foreach($cats as $c){
											$mode = $c['id'];
											$paname = $c['payment_name'];
											$cash = Modepayments( $from, $to , $mode , $shop);////
											
											$workshop = ModeJobs( $from, $to , $mode , $shop);
											$deposits = ModeDeposits( $from, $to , $mode, $shop);
											$topups = ModeTopups( $from, $to , $mode , $shop);
											$invoices = InvoiceModeTopups( $from, $to , $mode , $shop);
											 
											
											$rowtotal = $cash +$workshop + $deposits+$topups + $invoices;
											if($mode ==1){
												$cash_at_hand += $rowtotal;
											}
											$total += $rowtotal;
											$data .= '<tr>  <td> '.$paname.'</td><td> '.number_format($cash,2).'</td>
											<td> '.number_format($deposits,2).'</td><td> '.number_format($workshop,2).'</td>
											<td> '.number_format($topups,2).'</td><td> '.number_format($invoices,2).'</td>
											<td>  <b>'.number_format($rowtotal,2).'</b></td>
											 </tr>'; 
										}
										
										$data .=  '<tr>   <td colspan="6">  <h5>Total  </h5></td>  <td> <b>'.number_format($total, 2).' </b></td> </tr>';
										
										 
										 
										 
										 $expanses = intval(TheExpanses( $from, $to ,   $shop));
										 $cash_at_hand -= $expanses;
										 $indepts  =intval(amount_to_be_not_cleared(false, $shop));//
										 
										 
										 
										  $expdata = DB::query('SELECT  *from   expanse_types');
										 $data .=  '<tr>   <td colspan="6">  <h4>General Expenses  </h4></td>  </tr>';
										 
										 foreach($expdata as $ex){
											 //expanse_type
											 //exid
											 
											 $id = $ex['eid'];
											 $ex_name = $ex['expanse_type'];
											 $exp_amount = TheExpanses( $from, $to ,   $shop , $id);
											 if($exp_amount > 0){
											 
											 $data .=  '<tr>  <td> '.$ex_name.'</td> <td colspan="4">  </td><td  > <b> '.number_format($exp_amount ,2).' </b></td> <td> </td> </tr>';
											 }
										 }
										
										 $total -= $expanses ;
										
										$data .=  '<tr>  <td> General Expenses</td> <td colspan="5">  </td><td  > <b> '.number_format($expanses,2).' </b></td>
											 </tr>';
											 
										
										 
											 
											 //function Status_counts_more($status = false   ){//function Jobstatus_counts_more($time = false   ){
												 
												 $received = intval(Jobs_received($from , $shop  ));//
												 $waiting = Jobs_received_status(5 , $shop  );
												 $not_picked = Jobs_received_status(6 , $shop  );//deliverly_time
												 $pickon= Jobstatus_counts($from , $shop);
												 
												  $data .=  '<tr class="alert-warning">    <td colspan="6">  <h4>Staff Advance(S) 
												   </h4></td>  </tr>';
												 
												 //AllAllowances(//
												 $theallowances = intval(TheAllowances($from, $to ,   $shop));
												 $all_allowances = AllAllowances($from, $to ,   $shop);
												 
												 $cash_at_hand -= $theallowances;
												 
												 foreach($all_allowances as $row){
												 
												 
												 $data .=  '<tr>  <td> '.$row['exdate'].'</td> <td colspan="4"> '.$row['first_name'].'   </td><td  > <b> '.number_format($row['examount'],2).' </b></td> <td> </td>
												 
											 </tr>';
												 }
												  $data .=  '<tr class="alert alert-warning">  <td> <b>Total  Advances </b></td> <td colspan="5">  </td><td  > <b> '. number_format($theallowances, 2).' </b></td>
											 </tr>';
											 
											 $data .=  '<tr class="alert alert-warning">  <td> Cash at hand</td> <td colspan="5">  </td><td  > <b> '. number_format($cash_at_hand, 2).' </b></td>
											 </tr>';
											  
													  
													  $data .=  '<tr class="alert-warning">   <td colspan="6">   <b>JOBS </b></td> <td> </td> </tr>';
													
													  $data .=  '<tr>  <td> Received </td> <td colspan="4">  </td><td  > <b> '.number_format($received ,2).' </b></td> <td> </td> </tr>';
													
													 
													 $data .=  '<tr>  <td> For Pickup  </td> <td colspan="4">  </td><td  > <b> '.number_format($pickon ,2).' </b></td> <td> </td> </tr>';
													 
													 $data .=  '<tr>  <td> Waiting </td> <td colspan="4">  </td><td  > <b> '.number_format($waiting ,2).' </b></td> <td> </td> </tr>';
													 
													 $data .=  '<tr>  <td> Ready Not picked </td> <td colspan="4">  </td><td  > <b> '.number_format($not_picked ,2).' </b></td> <td> </td> </tr>';
													 
										 
											 	// $data .=  '<tr class="alert alert-warning">  <td> Depts</td> <td colspan="5">  </td><td  > <a href="'.$GLOBALS['base_url'].'balances.php"> <b> '. number_format(intval(amount_to_be_not_cleared(false,$shop)), 2).' </b> </a></td> </tr>';
										
											 
											 $data .=  '<tr class="alert alert-warning">  <td> Depts</td> <td colspan="5">  </td><td  > <a href="'.$GLOBALS['base_url'].'balances.php"> <b>   </b> </a></td> </tr>';
										 
											 
											 //$cash_at_hand
									}
									return $data;
									
								}
								
								function Jobs_received($time = false, $branch=false   ){//Jobstatus_counts_more($status = false   )
									if(!$time){
										$time  = date('Y-m-d');
									} 
									$where ='';
									if($branch > 0){
									$where = ' and shop='.$branch.' ';
									
										}
									
									 
									
									 $data = DB::query('
									SELECT * FROM job_receipt where value_date = "'.$time.'" '.$where.'   GROUP by parent_bar_code ' );
									 
													return DB::count();
								}
								
								function Jobs_received_status($status = false , $branch=false   ){//function Jobstatus_counts_more($time = false   ){
								$where ='';
								if($branch > 0){
									$where = ' and shop='.$branch.' ';
									
										}
										
									 $data = DB::query('
									SELECT * FROM job_receipt where order_status = '.$status.' '.$where.'   GROUP by parent_bar_code ' );
									 
													return DB::count();
								}
								
								function Personel( $id){
									 
									return  DB::queryFirstField(" SELECT first_name FROM personnel_details where id =%i" , $person  );
									
								}
								
								function  expanse_types(){
									
										$categorydata = DB::query('SELECT  *from   expanse_types');
									     
								}
							 
							 function Modepayments( $from, $to , $mode, $shop){
									$where = '';
									if($shop > 0){
										 $where .= ' shop = '.$shop.' and ';
									}
									
									  				 
									
									 							
								 $cash1=  DB::queryFirstField("SELECT IFNULL(sum(bill) , 0) as amount  FROM cash_receipt    WHERE   customer_change > -1 AND ".$where ." receipt_mode =".$mode."  AND   cash_receipt.is_cancelled = 0 AND  value_date  BETWEEN  '".$from."' and     '".$to."'");
									  
								$cash2=  DB::queryFirstField("SELECT IFNULL(sum(amount) , 0) as amount  FROM cash_receipt    WHERE   customer_change < 0  AND ".$where ." receipt_mode =".$mode."  AND   cash_receipt.is_cancelled = 0 AND  value_date  BETWEEN  '".$from."' and     '".$to."'");
								return intval($cash1) +intval($cash2) ;
								}
								
								
								
								function ModeDeposits( $from, $to , $mode, $shop){
									 $where = '';
									if($shop > 0){
										$where .= ' shop = '.$shop.' and ';
									}
									 
									 
									return  DB::queryFirstField("SELECT IFNULL(sum(amount) , 0) as amount  FROM special_receipt   WHERE  ".$where." receipt_mode =".$mode."  AND  is_cancelled = 0 AND      value_date  BETWEEN  '".$from."' and     '".$to."'");
									
								}
								
								function ModeJobs( $from, $to , $mode, $shop){
									$where = '';
									if($shop > 0){
										$where .= ' shop = '.$shop.' and ';
									}
									 
								 
									$categorydata = DB::query('SELECT   bill ,  amount  FROM job_receipt   WHERE '.$where.'  receipt_mode ='.$mode.'  AND  is_cancelled = 0 AND    value_date  >=  "'.$from.'" and       value_date <= "'.$to.'"   GROUP by parent_bar_code ');
									$sum = 0;
										 
												if(DB::count() != 0){										
												foreach ($categorydata as $category)
													{
														
														$sum +=  $category['amount'];
													
														
													
												}
												}
										
												return $sum;


								}
								
								function Jobstatus_counts($time = false , $branch=false  ){
								    $where='';
									if($branch > 0){
										$where =' and shop='.$branch.' ';
										}
									if(!$time){
										$time  = date('Y-m-d');
									} 
									
									 
									
									 $data = DB::query('
									SELECT * FROM job_receipt where pickon = "'.$time.'" '.$where.'   GROUP BY parent_bar_code ' );
									 
			return DB::count();
								}	
								
			function distcinct_jobs_status_by_customer( $time=false  ){ 
						 		if(!$time){
										 
										$time  = date('Y-m-d');
									} 
									
									 
									
									 return $data = DB::query('
									SELECT * FROM job_receipt where pickon = "'.$time.'"  GROUP BY customer_id ' );
							 
					}


					function distcinct_jobs_status( $time=false  ){ 
						 		if(!$time){
										 
										$time  = date('Y-m-d');
									} 
									
									 
									
									 return $data = DB::query('
									SELECT * FROM job_receipt where pickon = "'.$time.'"  GROUP by parent_bar_code ' );
							 
					}								
								
								
								
								function TheExpanses( $from, $to ,   $shop=false , $id=false){//
									$where = $where2 = '';
									if($shop > 0){
										$where .= '   exbranch  = '.$shop.' and  ';
									}
									if($id){
										$where2 .= ' and expanse   = '.$id.'  ';
									}
									
									return  DB::queryFirstField('SELECT  sum(examount) as amount   FROM expanses   WHERE    '.$where.'             date(exdate) >= "'.$from.'" and  date(exdate) <= "'.$to.'" '.$where2.' ');
									 
								}
								
								function TheAllowances( $from, $to ,   $shop=false , $id=false){//
									$where = $where2 = '';
									if($shop > 0){
										$where .= '   exbranch  = '.$shop.' and  ';
									}
								 
									return   DB::queryFirstField('SELECT  sum(examount) as amount   FROM allowances   WHERE    '.$where.'             date(exdate) >= "'.$from.'" and  date(exdate) <= "'.$to.'"  ');
									 
								}
								
								function AllAllowances( $from, $to ,   $shop=false ){//
									$where = $where2 = '';
									if($shop > 0){
										$where .= '   exbranch  = '.$shop.' and  ';
									}
									 	
									return  DB::query('SELECT exid, examount, employee,  exnotes, full_name, exdate, first_name , last_name FROM allowances  join shop on shop.id = exbranch left JOIN personnel_details on personnel_details.id=employee  WHERE    '.$where.'             date(exdate) >= "'.$from.'" and  date(exdate) <= "'.$to.'" '.$where2.' ');
									
									
									//return   DB::query('SELECT    examount ,    FROM allowances   WHERE    '.$where.'             date(exdate) >= "'.$from.'" and  date(exdate) <= "'.$to.'" '.$where2.' ');
									 
								}
								
								
								function ModeTopups( $from, $to , $mode, $shop){
									$where = '';
									if($shop > 0){
										$where .= ' branch = '.$shop.' AND ';
									}
									
									return  DB::queryFirstField('SELECT IFNULL(sum(topup) , 0) as amount  FROM topups   WHERE '.$where.'  pmode ='.$mode.'    AND (date(top_up_date) >= "'.$from.'" and  date(top_up_date) <= "'.$to.'" )');
									
								}

								function InvoiceModeTopups( $from, $to , $mode=false, $shop=false){
									$where = ''; $where2 = '';
									if($shop > 0){
										$where .= '   branch = '.$shop.' AND  ';
									}
									if($mode ){
										$where2 .= '  AND    pmode ='.$mode.'   ';
									}
									 
									return  DB::queryFirstField('SELECT IFNULL(sum(topup) , 0) as amount  FROM invoicetopups  join cash_receipt on cash_receipt.id = invoicetopups.receipt_id  WHERE  '.$where.'   DATE(value_date) != DATE(top_up_date)       AND top_up_date >= "'.$from.'" and  top_up_date <= "'.$to.'"  '.$where2.'');
									
								}

								function InvoiceModeTopups2( $from, $to , $mode=false, $shop=false){
									$where = ''; $where2 = '';
									if($shop > 0){
										$where .= '   branch = '.$shop.' AND  ';
									}
									if($mode ){
										$where2 .= '  AND    pmode ='.$mode.'   ';
									}
									 
									return  DB::queryFirstField('SELECT IFNULL(sum(topup) , 0) as amount  FROM invoicetopups  join cash_receipt on cash_receipt.id = invoicetopups.receipt_id  WHERE  '.$where.'   DATE(value_date) != DATE(top_up_date)     AND    value_date = CURRENT_DATE()  AND is_cancelled = 0 '.$where2.' ', date("Y-m-d", strtotime(trim($from))), date("Y-m-d", strtotime(trim($to))));
									
								}
								
function getTorIP() {
	
    if( array_key_exists('HTTP_X_FORWARDED_FOR', $_SERVER) && !empty($_SERVER['HTTP_X_FORWARDED_FOR']) ) {
        if (strpos($_SERVER['HTTP_X_FORWARDED_FOR'], ',')>0) {
            $addr = explode(",",$_SERVER['HTTP_X_FORWARDED_FOR']);
            return trim($addr[0]);
        } else {
            return trim($_SERVER['HTTP_X_FORWARDED_FOR']);
        }
    }
    else {
        return trim($_SERVER['REMOTE_ADDR']);
    }
}

function check_4tor($ip){ 
       
	  return DB::queryFirstField(" select id from tor where ip='$ip' "); 
	   
 }
 
 function check_tor(){
	 $ip = getTorIP();
	 return check_4tor($ip);
 }
 function cleanurl(){
	  
	   if(strpos($_SERVER['REQUEST_URI'], '?') !== false  or strpos($_SERVER['REQUEST_URI'], '=') !== false or strpos($_SERVER['REQUEST_URI'], 'action') !== false  ){
              
			  return false;
			 
			 
        }else return true;
  }
  
 function blocktor($ip){
	
                 if(check_4tor($ip)){
                 }else { 
				
				DB::startTransaction();
				DB::query("SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE");
				DB::insert ('tor',  array ( 'ip' =>''.$ip.'' ) ); DB::commit(); 
				}

}



function convert_number_to_words($number) {

    $hyphen      = '-';
    $conjunction = ' and ';
    $separator   = ', ';
    $negative    = 'negative ';
    $decimal     = ' and  ';
    $dictionary  = array(
        0                   => 'zero',
        1                   => 'one',
        2                   => 'two',
        3                   => 'three',
        4                   => 'four',
        5                   => 'five',
        6                   => 'six',
        7                   => 'seven',
        8                   => 'eight',
        9                   => 'nine',
        10                  => 'ten',
        11                  => 'eleven',
        12                  => 'twelve',
        13                  => 'thirteen',
        14                  => 'fourteen',
        15                  => 'fifteen',
        16                  => 'sixteen',
        17                  => 'seventeen',
        18                  => 'eighteen',
        19                  => 'nineteen',
        20                  => 'twenty',
        30                  => 'thirty',
        40                  => 'fourty',
        50                  => 'fifty',
        60                  => 'sixty',
        70                  => 'seventy',
        80                  => 'eighty',
        90                  => 'ninety',
        100                 => 'hundred',
        1000                => 'thousand',
        1000000             => 'million',
        1000000000          => 'billion',
        1000000000000       => 'trillion',
        1000000000000000    => 'quadrillion',
        1000000000000000000 => 'quintillion'
    );

    if (!is_numeric($number)) {
        return false;
    }

    if (($number >= 0 && (int) $number < 0) || (int) $number < 0 - PHP_INT_MAX) {
        // overflow
        trigger_error(
            'convert_number_to_words only accepts numbers between -' . PHP_INT_MAX . ' and ' . PHP_INT_MAX,
            E_USER_WARNING
        );
        return false;
    }

    if ($number < 0) {
        return $negative . convert_number_to_words(abs($number));
    }

    $string = $fraction = null;

    if (strpos($number, '.') !== false) {
        list($number, $fraction) = explode('.', $number);
    }

    switch (true) {
        case $number < 21:
            $string = $dictionary[$number];
            break;
        case $number < 100:
            $tens   = ((int) ($number / 10)) * 10;
            $units  = $number % 10;
            $string = $dictionary[$tens];
            if ($units) {
                $string .= $hyphen . $dictionary[$units];
            }
            break;
        case $number < 1000:
            $hundreds  = $number / 100;
            $remainder = $number % 100;
            $string = $dictionary[$hundreds] . ' ' . $dictionary[100];
            if ($remainder) {
                $string .= $conjunction . convert_number_to_words($remainder);
            }
            break;
        default:
            $baseUnit = pow(1000, floor(log($number, 1000)));
            $numBaseUnits = (int) ($number / $baseUnit);
            $remainder = $number % $baseUnit;
            $string = convert_number_to_words($numBaseUnits) . ' ' . $dictionary[$baseUnit];
            if ($remainder) {
                $string .= $remainder < 100 ? $conjunction : $separator;
                $string .= convert_number_to_words($remainder);
            }
            break;
    }

    if (null !== $fraction && is_numeric($fraction)) {
        $string .= $decimal;
        $words = array();
        foreach (str_split((string) $fraction) as $number) {
            $words[] = $dictionary[$number];
        }
        $string .= implode(' ', $words);
    }

    return $string;
}
     function numtowords($num){ 
$decones = array( 
            '01' => "One", 
            '02' => "Two", 
            '03' => "Three", 
            '04' => "Four", 
            '05' => "Five", 
            '06' => "Six", 
            '07' => "Seven", 
            '08' => "Eight", 
            '09' => "Nine", 
            10 => "Ten", 
            11 => "Eleven", 
            12 => "Twelve", 
            13 => "Thirteen", 
            14 => "Fourteen", 
            15 => "Fifteen", 
            16 => "Sixteen", 
            17 => "Seventeen", 
            18 => "Eighteen", 
            19 => "Nineteen" 
            );
$ones = array( 
            0 => " ",
            1 => "One",     
            2 => "Two", 
            3 => "Three", 
            4 => "Four", 
            5 => "Five", 
            6 => "Six", 
            7 => "Seven", 
            8 => "Eight", 
            9 => "Nine", 
            10 => "Ten", 
            11 => "Eleven", 
            12 => "Twelve", 
            13 => "Thirteen", 
            14 => "Fourteen", 
            15 => "Fifteen", 
            16 => "Sixteen", 
            17 => "Seventeen", 
            18 => "Eighteen", 
            19 => "Nineteen" 
            ); 
$tens = array( 
            0 => "",
            2 => "Twenty", 
            3 => "Thirty", 
            4 => "Forty", 
            5 => "Fifty", 
            6 => "Sixty", 
            7 => "Seventy", 
            8 => "Eighty", 
            9 => "Ninety" 
            ); 
$hundreds = array( 
            "Hundred", 
            "Thousand", 
            "Million", 
            "Billion", 
            "Trillion", 
            "Quadrillion" 
            ); //limit t quadrillion 
$num = number_format($num,2,".",","); 
$num_arr = explode(".",$num); 
$wholenum = $num_arr[0]; 
$decnum = $num_arr[1]; 
$whole_arr = array_reverse(explode(",",$wholenum)); 
krsort($whole_arr); 
$rettxt = ""; 
foreach($whole_arr as $key => $i){ 
    if($i < 20){ 
        $rettxt .= $ones[$i]; 
    }
    elseif($i < 100){ 
        $rettxt .= $tens[substr($i,0,1)]; 
        $rettxt .= " ".$ones[substr($i,1,1)]; 
    }
    else{ 
        $rettxt .= $ones[substr($i,0,1)]." ".$hundreds[0]; 
        $rettxt .= " ".$tens[substr($i,1,1)]; 
        $rettxt .= " ".$ones[substr($i,2,1)]; 
    } 
    if($key > 0){ 
        $rettxt .= " ".$hundreds[$key]." "; 
    } 

} 
$rettxt = $rettxt." peso/s";

if($decnum > 0){ 
    $rettxt .= " and "; 
    if($decnum < 20){ 
        $rettxt .= $decones[$decnum]; 
    }
    elseif($decnum < 100){ 
        $rettxt .= $tens[substr($decnum,0,1)]; 
        $rettxt .= " ".$ones[substr($decnum,1,1)]; 
    }
    $rettxt = $rettxt." centavo/s"; 
} 
return $rettxt;}  
	   
	   function numberTowords($num)
{ 
$ones = array( 
1 => "one", 
2 => "two", 
3 => "three", 
4 => "four", 
5 => "five", 
6 => "six", 
7 => "seven", 
8 => "eight", 
9 => "nine", 
10 => "ten", 
11 => "eleven", 
12 => "twelve", 
13 => "thirteen", 
14 => "fourteen", 
15 => "fifteen", 
16 => "sixteen", 
17 => "seventeen", 
18 => "eighteen", 
19 => "nineteen" 
); 
$tens = array( 
1 => "ten",
2 => "twenty", 
3 => "thirty", 
4 => "forty", 
5 => "fifty", 
6 => "sixty", 
7 => "seventy", 
8 => "eighty", 
9 => "ninety" 
); 
$hundreds = array( 
"hundred", 
"thousand", 
"million", 
"billion", 
"trillion", 
"quadrillion" 
); //limit t quadrillion 
$num = number_format($num,2,".",","); 
$num_arr = explode(".",$num); 
$wholenum = $num_arr[0]; 
$decnum = $num_arr[1]; 
$whole_arr = array_reverse(explode(",",$wholenum)); 
krsort($whole_arr); 
$rettxt = ""; 
foreach($whole_arr as $key => $i){ 
if($i < 20){ 
$rettxt .= $ones[$i]; 
}elseif($i < 100){ 
$rettxt .= $tens[substr($i,0,1)]; 
$rettxt .= " ".$ones[substr($i,1,1)]; 
}else{ 
$rettxt .= $ones[substr($i,0,1)]." ".$hundreds[0]; 
$rettxt .= " ".$tens[substr($i,1,1)]; 
$rettxt .= " ".$ones[substr($i,2,1)]; 
} 
if($key > 0){ 
$rettxt .= " ".$hundreds[$key]." "; 
} 
} 
if($decnum > 0){ 
$rettxt .= " and "; 
if($decnum < 20){ 
$rettxt .= $ones[$decnum]; 
}elseif($decnum < 100){ 
$rettxt .= $tens[substr($decnum,0,1)]; 
$rettxt .= " ".$ones[substr($decnum,1,1)]; 
} 
} 
return $rettxt; 
}

	   

function Item_id($item_id , $shop){
		$id = 0;
		$count =0;
		$item_sold_per_id = 0;
		$categorydata = DB::query("SELECT item FROM item_price where id=%i    "  , $item_id   );
			if(DB::count() != 0){										
		       foreach ($categorydata as $category)
	              {
					  
			          $id =  $category['item'];
					 
		          
			}
			}
			return $id;
	}
	
	function Category_name($item_id ){
		$id = ' N/A ';
		$count =0;
		$item_sold_per_id = 0;
		$categorydata = DB::query("SELECT id, category_name , category_description FROM item_category where id=%i  LIMIT 1   "  , $item_id   );
			if(DB::count() != 0){										
		       foreach ($categorydata as $category)
	              {
					  
			          $id =  ucfirst(strtolower($category['category_name']));
					 
		          
			}
			}
			return $id;
	}
	
	function stock_data($shop , $start, $end){
			$name = 'Select one';
		$categorydata = DB::query("SELECT * FROM stock_item  "  );
			if(DB::count() != 0){										
		       foreach ($categorydata as $category)
	              {
			  $name =  $category['item_name'];
			  $item  = $category['id'];
			   $total_sold = 0;
               $shop_sales = '';			   
			  
			  $shop_ids = DB::query("SELECT full_name, id FROM shop where is_active = 1"  );
			  $shop_qualities = '';
					if(DB::count() != 0){										
					   foreach ($shop_ids as $ids)
						  {
							  $shop = $ids['id'];
							   $price_id = Item_id_from_item($item , $shop);
							  $no_sold = Sum_sold($price_id , $shop , $start, $end);
							  if($no_sold < 1){
								  $no_sold = 0;
							  }
							  $shop_sales .= '<td>'.$no_sold .' </td>';
							  
							   $total_sold +=   $no_sold;
									  if($no_sold >0){
										  //get Item details from here 
										  }
							  
								  }
					 
					  
						  }
						  
						  if(  $total_sold > 0){
							  
							  
							  echo '<tr> <td>'. $name.'</td> '.$shop_sales.' <td>'. $total_sold.'</td> </tr>';
							  
						  }
					}
			 
			}
			 
	}
	
	function Shop_ids($shop){//
		$name = 'Store';
		$categorydata = DB::query("SELECT full_name FROM shop where is_active = 1" , $shop);
			if(DB::count() != 0){										
		       foreach ($categorydata as $category)
	              {
			 
			  
		          }
			}
			 
	}
	
	function Get_shop_names(){//
	    $shops  = '';
		$shop_data = DB::query("SELECT full_name FROM shop where is_active = 1" );
			if(DB::count() != 0){										
		       foreach ($shop_data as $row)
	              {
					   $shops  .= '<th>'.$row['full_name'].' </th>';
					  
					  
			 
			  
		          }
			}
			
			return  array($shops ,DB::count()) ; 
			 
	}
	
	
		
 
	
	function Item_data($item){
		$name = '<td> </td><td> </td>';
		$categorydata = DB::query("SELECT item_name , bar_code FROM stock_item where id =%i" , $item);
			if(DB::count() != 0){										
		       foreach ($categorydata as $category)
	              {
			 $name =  '<td>'.$category['item_name'].' </td><td>'.$category['bar_code'].'</td>';
			  
		          }
			}
			return $name;
	}
	
	
	function Change_quantity(  $quantity , $item, $user){
		 
			$s = DB::query('UPDATE item_stock_level SET updated_date = current_timestamp(), updated_by = '.$user.', quantity = ' . $quantity . ' WHERE   id ='.$item.''  );
		
		 
	}
	
	function Select_sales_commision_rate($value){
		$code = 6;
		if($value < 100001){
			$code = 3;
		}else if($value > 100000 and $value < 400001){
			$code = 4;
		}else if($value > 400000 and $value < 800001){
			$code = 5;
			
		}
		else if($value > 800000){
			$code = 6;
		}
		return $code;
		  
		
	}
	function Select_tech_commision_rate($value){
	
		$code = 10;
		if($value < 100001){
			$code = 7;
		}else if($value > 100000 and $value < 400001){
			$code = 8;
		}else if($value > 400000 and $value < 800001){
			$code = 9;
			
		}
		if($value > 800000){
			$code = 10;
		}
		return $code;
		  
		
	}
	
	function Commission_settings(  $code){
		$value = 0;
 
		$setting_data = DB::query("SELECT value from settings WHERE code=%i "  , $code);
			if(DB::count() != 0){										
		       foreach ($setting_data as $s)
	              {
					  
			          $value =  $s['value'];
		 
		          }
			}
			return $value;
		  
		 
	}
	
	
	
		function Stock_level($item   , $shop){
			$qty =  0;
			$stockitemsdata =  false;
			if($shop > 0){
				$stockitemsdata = DB::query("SELECT  quantity, id FROM item_stock_level WHERE item_stock_level.item   =%i     and shop =%i  LIMIT 1", $item , $shop);
			}else {
				$stockitemsdata = DB::query("SELECT  quantity, id FROM item_stock_level WHERE item_stock_level.item   =%i    ORDER BY shop  LIMIT 1", $item , $shop);
			}
			 
			 
			 
		 	  if(DB::count() != 0){
				foreach ($stockitemsdata  as $category)
	              {
			        	$qty =  array(  $category['quantity'] , $category['id']);//now we can get the price ID
					 
					 
			  
		          } 
				
			 }
			 
			  
			 return $qty;
	}
	
	
	function No_sold($item , $shop){//quantity
		$quantity = 0;
 
		$categorydata = DB::query("SELECT quantity FROM item_stock_level WHERE item=%i AND shop =%i"  , $item , $shop);
			if(DB::count() != 0){										
		       foreach ($categorydata as $category)
	              {
					  
			          $quantity =  $category['quantity'];
					 
					  
					
			  
		          }
			}
			return $quantity;
	}
	
	function Category_sale_detailz($shop, $start , $end , $category){
		$quantity = 0;
		$cat_label = $shop_label = '';
		if($shop > 0){
			$shop_label =  ' c.shop='.$shop.' AND ';
			
		}
		
		if($category > 0){
			$cat_label =  ' i.category='.$category.' AND ';
			
		}
		 
		$categorydata = DB::query("SELECT price_id ,  quantity  as q , sum(c.sell_price *quantity) as spt,   full_name, item_name, bar_code, default_sell_price as cp , c.sell_price as sp, p.sell_price as set_sp FROM cash_sale_item as c , shop as s, item_price as p , stock_item as i WHERE  ".$shop_label." ".$cat_label ."  is_cancelled = 0 AND c.created_date BETWEEN CAST('".$start."' AS DATE) AND CAST('".$end."' AS DATE) and c.shop = s.id and p.id=c.price_id and i.id = p.item GROUP BY price_id ORDER BY price_id DESC ");
		 
		
			if(DB::count() != 0){										
		       foreach ($categorydata as $row)
	              {
					   
					  
					  
					  $quantity += $row['spt'];
					  
					   
			         echo '<tr><td>'.$row['item_name'].' </td><td>'.$row['bar_code'].' </td><td> '.$row['full_name'].'</td> 
					 <td> '.$row['q'].'</td> <td> '.number_format($row['cp']).'</td> <td> '.number_format($row['set_sp']).'</td><td> '.number_format($row['sp']).'</td>
					   <td> '.number_format($row['spt']).'</td>  
					 

					 </tr>';
					 
					 
					 
					  
					
			  
		          }
				  
				   echo '<tr class="info"><td>  </td><td>  </td><td>  </td> 
					 <td>  </td> <td>  </td> <td>  </td><td>  </td>
					   <td> '. number_format($quantity).'</td>  
					 

					 </tr>';
			}
			 
	}
	
	function Cat_sale_details($shop, $start , $end , $category){
		$quantity = 0;
		$cat_label =  $shop_label = '';
		if($shop > 0){
			$shop_label =  ' c.shop='.$shop.' AND ';
			
		}
		
		if($category > 0){
			$cat_label =  ' i.category='.$category.' AND ';
			
		}
		 
		$categorydata = DB::query("SELECT price_id ,  quantity  as q ,   c.sell_price *quantity  as spt, p.cost_price *quantity as tcp, c.created_date as created,  full_name, item_name, bar_code, p.cost_price as cp , c.sell_price as sp, p.sell_price as set_sp FROM cash_sale_item as c , shop as s, item_price as p , stock_item as i WHERE   ".$cat_label." ".$shop_label."   is_cancelled = 0 AND c.created_date BETWEEN CAST('".$start."' AS DATE) AND CAST('".$end."' AS DATE) and c.shop = s.id and p.id=c.price_id and i.id = p.item  ORDER BY c.created_date DESC ");
		 $total_cp =0;
		 
			if(DB::count() != 0){										
		       foreach ($categorydata as $row)
			    {
					   
					  $total_cp += $row['tcp'];
					  
					  $quantity += $row['spt'];
					  
					   
			         echo '<tr><td>'.$row['item_name'].' </td><td>'.$row['bar_code'].' </td><td> '.$row['full_name'].'</td> 
					 <td> '.number_format($row['cp']).'</td> <td> '.number_format($row['set_sp']).'</td> <td> '.$row['q'].'</td><td> '.number_format($row['sp']).'</td>
					 <td> '.number_format($row['tcp']).'</td>  <td> '.number_format($row['spt']).'</td>  
					 

					 </tr>';
					 
					 
					 
					  
					
			  
		          }
				  
				  echo '<tr class="info"><td>Total  </td><td>  </td><td>  </td><td>  </td> 
					 <td>  </td> <td>  </td> <td>  </td><td> '. number_format($total_cp).'</td> 
					   <td> '. number_format($quantity).'</td>  
					 

					 </tr>';
	             
			}
			 
	}
	
	
	
	
	function Category_sale_details($shop, $start , $end , $category){
		$quantity = 0;
		$cat_label = $shop_label = '';
		if($shop > 0){
			$shop_label =  ' c.shop='.$shop.' AND ';
			
		}
		
		if($category > 0){
			$cat_label =  ' i.category='.$category.' AND ';
			
		}
		 
		$categorydata = DB::query("SELECT price_id , SUM(quantity) as q , sum(c.sell_price *quantity) as spt,   sum(p.cost_price *quantity) as tcp,   full_name, item_name, bar_code, p.cost_price as cp , c.sell_price as sp, p.sell_price as set_sp FROM cash_sale_item as c , shop as s, item_price as p , stock_item as i WHERE  ".$shop_label." ".$cat_label ."  is_cancelled = 0 AND c.created_date BETWEEN CAST('".$start."' AS DATE) AND CAST('".$end."' AS DATE) and c.shop = s.id and p.id=c.price_id and i.id = p.item GROUP BY price_id ORDER BY price_id DESC ");
		 
		$total_cp =0;
			if(DB::count() != 0){										
		       foreach ($categorydata as $row)
	              {
					   
					  
					  
					  
					  $total_cp += $row['tcp'];
					  
					  $quantity += $row['spt'];
					  
					   
			         echo '<tr><td>'.$row['item_name'].' </td><td>'.$row['bar_code'].' </td><td> '.$row['full_name'].'</td> 
					 <td> '.number_format($row['cp']).'</td> <td> '.number_format($row['set_sp']).'</td> <td> '.$row['q'].'</td><td> '.number_format($row['sp']).'</td>
					 <td> '.number_format($row['tcp']).'</td>  <td> '.number_format($row['spt']).'</td>  
					 

					 </tr>';
					 
					 
					 
					  
					
			  
		          }
				  
				   echo '<tr class="info"><td> Total </td><td>  </td><td>  </td> 
					 <td>  </td> <td>  </td> <td>  </td><td>  </td>
					 <td> '. number_format($total_cp).'</td>  <td> '. number_format($quantity).'</td>  
					 

					 </tr>';
			}
			 
	}
	
	
	function Category_summary($shop, $start , $end , $category){ 
	$shop_label = $cat_label = '';
		if($shop > 0){
			$shop_label =  ' c.shop='.$shop.' AND ';
			
		}
		
		if($category > 0){
			$cat_label =  ' si.category='.$category.' AND ';
			
		}
		 $total_items 	= $total 	= 0;
		 
		 							
		 
		$categorydata = DB::query(" SELECT  SUM(quantity) as q,  full_name, sum(c.sell_price *quantity) as sp ,  shop FROM cash_sale_item as c , shop as s , stock_item as si WHERE ".$shop_label." c.created_date BETWEEN CAST('".$start."' AS DATE) AND CAST('".$end."' AS DATE) and c.shop = s.id  AND is_cancelled = 0 GROUP BY c.shop ORDER BY c.shop DESC
		
		 ");
		  if(DB::count() != 0){										
		       foreach ($categorydata as $row)
	              {
					   
					  
						if($shop < 1){
						  $total 	+= $row['sp'];
						   $total_items 	=  $row['q'];
						}							
					   
			         echo '<tr> <td> '.$row['full_name'].'</td> 
					 <td> '.$row['q'].'</td> <td> '.number_format($row['sp']).'</td>    
					 

					 </tr>';
					 
			  
		          }
			}
			
			if($shop < 1){
						   
						 							
					   
			         echo '<tr class="info"> <td> Total </td>    <td> '.$total_items.'</td>
					 <td> '.number_format($total).'</td>    
					 

					 </tr>';
			}
			 
	  }
	  
		 
	
	function sales_summary($shop, $start , $end){ 
	$shop_label = '';
		if($shop > 0){
			$shop_label =  ' c.shop='.$shop.' AND ';
			
		}
		 $total_items 	= $total 	= 0;
		 
		 							
		 
		$categorydata = DB::query(" SELECT  SUM(quantity) as q,  full_name, sum(c.sell_price *quantity) as sp ,  shop FROM cash_sale_item as c , shop as s WHERE ".$shop_label." c.created_date BETWEEN CAST('".$start."' AS DATE) AND CAST('".$end."' AS DATE) and c.shop = s.id  AND is_cancelled = 0 GROUP BY c.shop ORDER BY c.shop DESC
		
		 ");
		  if(DB::count() != 0){										
		       foreach ($categorydata as $row)
	              {
					   
					  
						if($shop < 1){
						  $total 	+= $row['sp'];
						   $total_items 	=  $row['q'];
						}							
					   
			         echo '<tr> <td> '.$row['full_name'].'</td> 
					 <td> '.$row['q'].'</td> <td> '.number_format($row['sp']).'</td>    
					 

					 </tr>';
					 
			  
		          }
			}
			
			if($shop < 1){
						   
						 							
					   
			         echo '<tr class="info"> <td> Total </td>    <td> '.$total_items.'</td>
					 <td> '.number_format($total).'</td>    
					 

					 </tr>';
			}
			 
	  }
	  
	  
	  function Loss_summary($shop, $start , $end){ 
	$shop_label = '';
		if($shop > 0){
			$shop_label =  ' c.shop='.$shop.' AND ';
			
		}
		 $total 	= 0;
		 $total_items 	= 0;
		 
		 							
		 
		$categorydata = DB::query(" SELECT  SUM(quantity) as q,  full_name, sum(c.sell_price *quantity) as sp ,  shop , rname as bname FROM special_sale_item as c , shop as s, bounce as b WHERE   ".$shop_label." c.created_date BETWEEN CAST('".$start."' AS DATE) AND CAST('".$end."' AS DATE) and c.shop = s.id   AND bounce_type = b.id  GROUP BY c.shop ORDER BY c.shop DESC
		
		 ");
		  if(DB::count() != 0){										
		       foreach ($categorydata as $row)
	              {
					   
					  
						if($shop < 1){
						  $total 	+= $row['sp'];
						  $total_items 	=  $row['q'];
						}
						
					   
			         echo '<tr> <td> '.$row['full_name'].'</td> <td> '.$row['bname'].'</td>  
					 <td> '.$row['q'].'</td> <td> '.number_format($row['sp']).'</td>    
					 

					 </tr>';
					 
			  
		          }
			}
			
			if($shop < 1){
						   
						 							
					   
			         echo '<tr class="info"> <td> Total </td> <td>  </td>  <td> '.$total_items.'</td>
					 <td> '.number_format($total).'</td>    
					 

					 </tr>';
			}
			 
	  }
	  
	  
	  
	  function sales_summary_details($shop, $start , $end){ 
	 $quantity = 0;
		$shop_label = '';
		if($shop > 0){
			$shop_label =  ' c.shop ='.$shop.' AND ';
			
		}
		 
		 
		 
		 $categorydata = DB::query("SELECT price_id , quantity as q , c.sell_price *quantity  as spt, p.cost_price *quantity as tcp,  full_name, item_name, bar_code, p.cost_price as cp , c.sell_price as sp, default_sell_price as set_sp FROM cash_sale_item as c , shop as s, item_price as p , stock_item as i WHERE  ".$shop_label."   is_cancelled = 0 AND c.created_date BETWEEN CAST('".$start."' AS DATE) AND CAST('".$end."' AS DATE) and c.shop = s.id and p.id=c.price_id and i.id = p.item   ");
		
		
		 
		 $total_cp  =0;
			if(DB::count() != 0){										
		       foreach ($categorydata as $row)
	              {
					   
					  $total_cp += $row['tcp'];
					  
					  $quantity += $row['spt'];
					  
					   
			         echo '<tr><td>'.$row['item_name'].' </td><td>'.$row['bar_code'].' </td><td> '.$row['full_name'].'</td> 
					 <td> '.number_format($row['cp']).'</td> <td> '.number_format($row['set_sp']).'</td> <td> '.$row['q'].'</td><td> '.number_format($row['sp']).'</td>
					 <td> '.number_format($row['tcp']).'</td>  <td> '.number_format($row['spt']).'</td>  
					 

					 </tr>';
					 
					 
					 
					  
					
			  
		          }
				  
				   echo '<tr class="info"><td> Total </td><td>  </td><td>  </td> 
					 <td>  </td> <td>  </td> <td>  </td><td>  </td>
					 <td> '. number_format($total_cp).'</td>  <td> '. number_format($quantity).'</td>  
					 

					 </tr>';
			}
			 
	}

	function job_detailsxy( $st=false, $from=false , $branch=false){
		$where = '';
		if($branch > 0){
			$where = ' and shop ='.$branch.' '; 

		}
		if($st){
			$where = ' and order_status ='.$st.' '; 

		}
		if($from){
			$where = ' and value_date    >='.$from.' '; 

		}
	 
		$sql = 'SELECT * FROM job_receipt  where id > 0  '.$where.' ';
		$result = $GLOBALS['conn_sys']->query($sql);
		 
		if ($result->num_rows > 0) {
			return  $result->fetch_assoc();
			 
			 
				 
		} return false;
			
			
		}



	function job_detailz($st=false, $from=false , $branch=false , $cus=false , $t=false){ 
		$quantity = 0;
		   $shop_label = '';

		   $where = '';
		   if($t){
			
			$where .= ' and job_type ='.$t.' '; 
		   }
		if($branch > 0){
			$where .= ' and shop ='.$branch.' '; 

		}
		if($cus){
			$where .= ' and customer_id ='.$cus.' '; 

		}
		if($st){
			 $where .= ' and job.order_status ='.$st.' '; 
			 

		}
		if($from){
			$where .= ' and value_date    >='.$from.' '; 

		}


		   $sql = 'SELECT job.id, c.phone, s.order_status_name as status , order_status, value_date, shop, customer_name, pickon,  jobdata, bill, os.order_status_name , rpt_name, owner FROM job_receipt as job JOIN order_status as os on os.status_id = job.order_status JOIN  repair_jobtypes as jt on jt.rptid = job.job_type JOIN repair_owner_category as ow on ow.owid = owner_cat  join order_status s on s.status_id = order_status JOIN customers c on c.id = customer_id where job.id > 0  '.$where.' ';
		 $categorydata = DB::query($sql);
		   
			
			$total_cp  =0;
			   if(DB::count() != 0){										
				  foreach ($categorydata as $row)
					 {
						$state_color = $row['order_status'];
						$color ='success';

						if($state_color ==5){
							$color ='danger';

						}else if($state_color ==6){
							$color ='warning';

						}
						  
						 
						echo '<tr '.$color.'>
						<td> <a href="'.$GLOBALS['base_url'].'jobreceiptdetails.php/receipt/'.$row['id'].'"> '.$row['id'].' </a> </td>
						<td>'.$row['jobdata'].' </td>
						
						<td>'.$row['value_date'].' </td>
						
						<td> '.$row['customer_name'].'</td> 
						<td> '.$row['phone'].'</td> 
						<td> '.number_format($row['bill']).'</td>  
						<td> '.$row['rpt_name'].'</td> 
						<td> '.$row['owner'].'</td> 
						 
						<td> '.$row['status'].'</td> 
						<td> '.$row['pickon'].'</td>
						  
						
   
						</tr>';
						
						
						
						 
					   
				 
					 }
					 
					 
			   }
				
	   }
      function dispatch($id){
		return '<a  class="btn btn-mini btn-info" href="'.$GLOBALS['base_url'].'dispatch.php/'.$id.'"> Dispatch</a>';
	  }

	  function bounceajob($id){
		return '<a  class="btn btn-mini btn-warning" href="'.$GLOBALS['base_url'].'bouncedjob.php/'.$id.'"> Re do this job</a>';
	  }

	  function completed(){
		return '<a  class="alert alert-info"  > Completed</a>';
	  }

	  function completejob($id){
		return '<a  class="btn btn-mini btn-success" href="'.$GLOBALS['base_url'].'completejob.php/'.$id.'"> Complete</a>';
	  }

	   function specific_job($id){ 
		$quantity = 0;
		   $shop_label = '';

		   $where = '';
		 


		   $sql = 'SELECT job.order_status, transporter,parent_bar_code,  updated_date, job.id, c.phone, receipt_number, s.order_status_name as status ,value_date, shop, customer_name, pickon,  jobdata, bill, os.order_status_name , rpt_name, owner FROM job_receipt as job JOIN order_status as os on os.status_id = job.order_status JOIN  repair_jobtypes as jt on jt.rptid = job.job_type JOIN repair_owner_category as ow on ow.owid = owner_cat  join order_status s on s.status_id = order_status JOIN customers c on c.id = customer_id where job.id =   '.$id.' ';
		 $categorydata = DB::query($sql);
		   
			
			$total_cp  =0;
			   if(DB::count() != 0){										
				  foreach ($categorydata as $row)
					 {
						$complete = $row['order_status'];
						$action = '';

						$topay = topay($row['parent_bar_code']  );
						if($complete==5){ $action .=  completejob($row['id']); 
							
							
						
						}
						else if($complete==6){ 

							if($_SESSION["connectedusergroup"] ==1){
								$action .=  dispatch($row['id']);
								
							}else 
							if($topay > 0  ){
								$action .=  dispatch($row['id']);

							}else {
								$action .=  dispatch($row['id']); 

							}

							
							//$action .=  dispatch($row['id']); // $action .=  bounceajob($id);
						
						}

						else if($complete==3){ 
							if($_SESSION["connectedusergroup"] ==1){
								$action .=  bounceajob($id);
								
							}

						}


						 
						  
						 
						echo '<tr>
						 <td colspan="1"> <a href="'.$GLOBALS['base_url'].'jobreceiptdetails.php/receipt/'.$row['id'].'"> Task barcode: '.$row['receipt_number'].' </a>
						 <button  type="submit" class="btn btn-danger"href="'.$GLOBALS['base_url'].'jobreceiptdetails.php/receipt/'.$row['id'].'"> Delete </button>

						 </td>
						 <td colspan="1"> '.  $action.'</td>
						 
						 </tr>
						<tr>
						 <td> Description</td> 
						<td>'.$row['jobdata'].' </td>  
						</tr> <tr>
						<td> Date</td> 
						
						<td>'.$row['value_date'].' </td> </tr>
						<tr>
						 <td> Customer</td> 
						
						<td> '.$row['customer_name'].'</td> 
						</tr> <tr>
						 <td> Phone Number</td> 
						<td> '.$row['phone'].'</td> </tr>
						<tr>
						 <td> Amount charged</td> 
						<td> '.number_format($row['bill']).'</td> 
						</tr> <tr>
						 <td> Type</td>  
						<td> '.$row['rpt_name'].'</td> </tr>
						<tr>
						 <td> Owener Category</td> 
						<td> '.$row['owner'].'</td> </tr> 
						
						 <td> Pick up date</td> 
						<td> '.$row['pickon'].'</td> 
						</tr>

						<tr>
						 <td> Date picked</td> 
						<td> '.$row['transporter'].'</td>  
						</tr> <tr>

						<tr>
						 <td> Picked by</td> 
						<td> '.$row['updated_date'].'</td>  
						</tr> <tr>
						
						
						';
						
						
						
						 
					   
				 
					 }
					 
					 
			   }
				
	   }

	   function receipt_itemz($id){ 
		$quantity = 0;
		   $shop_label = '';

		   $where = '';
		 

		   $categorydata = DB::query("SELECT stock_item.item_name, stock_item.item_code, stock_item.bar_code, stock_item.description, cash_sale_item.sell_price, cash_sale_item.quantity, cash_sale_item.serial_number FROM cash_sale_item INNER JOIN item_price ON cash_sale_item.price_id = item_price.id INNER JOIN stock_item ON item_price.item = stock_item.id WHERE cash_sale_item.receipt=%i", $id);
			 
			$total_cp  =0;
			   if(DB::count() != 0){										
				  foreach ($categorydata as $row)
					 {
						$qty = $row['quantity'];
						$sp  = $row['sell_price'];
						$sub = $qty*$sp;
						  
						 
						echo '<tr>
						 	<td>'.$row['item_name'].' </td>
						
						<td>'.$qty.' </td>
						
						<td> '.$sp.'</td>  
						<td> '.number_format($sub).'</td>  
						<td> Delete</td>
						 
						  
						
   
						</tr>';
						
						
						
						 
					   
				 
					 }
					 
					 
			   }
				
	   }


	   function job_tasks($code){ 
		$quantity = 0;
		   $shop_label = '';

		   $where = '';
		 
            $categorydata =  DB::query('
		   SELECT     local_bar_code, rj.job_name , rj.job_amount, sell_price, id  FROM job_sale_item as job LEFT  JOIN repair_jobnames as  rj  on  job_id = job.price_id   where is_cancelled =0 AND general_bar_code ="'.$code.'"
			'); 
			$total_cp  =0;
			   if(DB::count() != 0){										
				  foreach ($categorydata as $row)
					 {
						$qty = 1;
						$sp  = $row['sell_price'];
						$sub = $qty*$sp;
						  
						 
						echo '<tr>
						 	<td>'.$row['job_name'].' </td>
						
						<td>'.$qty.' </td>
						
						<td> '.$sp.'</td>  
						<td> '.number_format($sub).'</td>  
						<td> <a href="'.$GLOBALS['base_url'].'deleteatask.php/'.$row['id'].'"> Delete </a></td>
						 
						  
						
   
						</tr>';
						
						
						
						 
					   
				 
					 }
					 
					 
			   }
				
	   }
	   
	   
	    function job_tasks2($code){ 
		$quantity = 0;
		   $shop_label = '';

		   $where = '';
		 
            $categorydata =  DB::query('
		   SELECT     local_bar_code, rj.job_name , rj.job_amount, sell_price, id  FROM job_sale_item as job LEFT  JOIN repair_jobnames as  rj  on  job_id = job.price_id   where is_cancelled =0 AND local_bar_code ="'.$code.'"
			'); 
			$total_cp  =0;
			   if(DB::count() != 0){										
				  foreach ($categorydata as $row)
					 {
						$qty = 1;
						$sp  = $row['sell_price'];
						$sub = $qty*$sp;
						  
						 
						echo '<tr>
						 	<td>'.$row['job_name'].' </td>
						
						<td>'.$qty.' </td>
						
						<td> '.$sp.'</td>  
						<td> '.number_format($sub).'</td>  
						<td> <a href="'.$GLOBALS['base_url'].'deleteatask.php/'.$row['id'].'"> Delete </a></td>
						 
						  
						
   
						</tr>';
						
						
						
						 
					   
				 
					 }
					 
					 
			   }
				
	   }
	
	
	
	
	function loss_summary_details($shop, $start , $end){ 
	 $money = 0;
		$shop_label = '';
		if($shop > 0){
			$shop_label =  ' c.shop='.$shop.' AND ';
			
		}
		 
		 
		 
		 $categorydata = DB::query("SELECT price_id , quantity as q , c.sell_price *quantity  as spt,  c.sell_price *quantity  as spt, p.cost_price *quantity as tcp,  full_name, item_name, bar_code, p.cost_price as cp , c.sell_price as sp, p.sell_price as set_sp ,   rname as bname  FROM  bounce as b,  special_sale_item as c , shop as s, item_price as p , stock_item as i WHERE  ".$shop_label."   is_cancelled = 0 AND c.created_date BETWEEN CAST('".$start."' AS DATE) AND CAST('".$end."' AS DATE) and c.shop = s.id and p.id=c.price_id and i.id = p.item  AND bounce_type = b.id	   ");
		
		$total = 0;
		 
		 
		 
			if(DB::count() != 0){										
		       foreach ($categorydata as $row)
	              {
					   
					   
					  $money += $row['tcp'];
					  $total += $row['q'];
					  
					   
					  
					   
			         echo '<tr><td>'.$row['item_name'].' </td><td>'.$row['bar_code'].' </td><td> '.$row['full_name'].'</td> 
					 <td> '.$row['q'].'</td> <td> '.number_format($row['cp']).'</td> <td> '.number_format($row['set_sp']).'</td><td> '.number_format($row['sp']).'</td>
					 <td> '.number_format($row['tcp']).'</td>  <td> '.number_format($row['spt']).'</td>  <td> '.$row['bname'].'</td> 
					 

					 </tr>';
					 
					 
					 
					  
					
			  
		          }
				  
				   echo '<tr class="info"><td>  </td><td>  </td><td>  </td> 
					 <td>'.$total.'  </td> <td>  </td> <td>  </td><td>  </td>
					 <td>  </td>  <td> '. number_format($money).'</td>  <td> </td> 
					 

					 </tr>';
			}
			 
	}
	
	
	function sale_details($shop, $start , $end){
		$total_cp = $quantity = 0;
		$shop_label = '';
		if($shop > 0){
			$shop_label =  ' c.shop='.$shop.' AND ';
			
		}
		 
		$categorydata = DB::query("SELECT price_id , SUM(quantity) as q , sum(c.sell_price *quantity) as spt,  sum(p.cost_price *quantity) as tcp,  full_name, item_name, bar_code, p.cost_price as cp , c.sell_price as sp, p.sell_price as set_sp FROM cash_sale_item as c , shop as s, item_price as p , stock_item as i WHERE  ".$shop_label."   is_cancelled = 0 AND c.created_date BETWEEN CAST('".$start."' AS DATE) AND CAST('".$end."' AS DATE) and c.shop = s.id and p.id=c.price_id and i.id = p.item GROUP BY price_id ORDER BY price_id DESC ");
		 
		
			if(DB::count() != 0){										
		       foreach ($categorydata as $row)
	              {
					   
					  
					  
					 
					  $total_cp += $row['tcp'];
					  
					  $quantity += $row['spt'];
					  
					   
			         echo '<tr><td>'.$row['item_name'].' </td><td>'.$row['bar_code'].' </td><td> '.$row['full_name'].'</td> 
					 <td> '.number_format($row['cp']).'</td> <td> '.number_format($row['set_sp']).'</td> <td> '.$row['q'].'</td><td> '.number_format($row['sp']).'</td>
					 <td> '.number_format($row['tcp']).'</td>  <td> '.number_format($row['spt']).'</td>  
					 </tr>';
					 
					 
					 
					 
					  
					
			  
		          }
				  
				   echo '<tr class="info"><td> Total </td><td>  </td><td>  </td> 
					 <td>  </td> <td>  </td> <td>  </td><td>  </td>
					 <td> '. number_format($total_cp).'</td>  <td> '. number_format($quantity).'</td>  
					 

					 </tr>';
			}
			 
	}
	
	
	
	function Defect_sale_details($shop, $start , $end){
		$quantity = 0;
		$shop_label = '';
		if($shop > 0){
			$shop_label =  ' c.shop='.$shop.' AND ';
			
		}
		 
		$categorydata = DB::query("SELECT price_id , SUM(quantity) as q , sum(c.sell_price *quantity) as spt,  full_name, item_name, bar_code, default_sell_price as cp , c.sell_price as sp, p.sell_price as set_sp ,   rname as bname FROM special_sale_item as c , shop as s , item_price as p , stock_item as i, bounce as b  WHERE       ".$shop_label."  bounce_type >0    AND c.created_date BETWEEN CAST('".$start."' AS DATE) AND CAST('".$end."' AS DATE) and c.shop = s.id and p.id=c.price_id and i.id = p.item  AND bounce_type = b.id GROUP BY price_id ORDER BY price_id DESC ");
		 
		
			if(DB::count() != 0){										
		       foreach ($categorydata as $row)
	              {
					   
					  $total_cp = $row['cp'] *$row['q'];
					  
					  $quantity += $row['spt'];
					  
					   
			         echo '<tr><td>'.$row['item_name'].' </td><td>'.$row['bar_code'].' </td><td> '.$row['full_name'].'</td> 
					 <td> '.$row['q'].'</td> <td> '.number_format($row['cp']).'</td> <td> '.number_format($row['set_sp']).'</td> 
					 <td> '.number_format($total_cp).'</td>  <td> '.number_format($row['spt']).'</td>  <td> '.$row['bname'].'</td>  
					 

					 </tr>';
					 
					 
					 
					  
					
			  
		          }
				  
				   echo '<tr class="info"><td>  </td><td>  </td><td>  </td> 
					 <td>  </td> <td>  </td> <td>  </td>
					 <td>  </td>   <td>  </td>  <td> '. number_format($quantity).'</td>
					 

					 </tr>';
			}
			 
	}
	
	function Sum_sold($price_id , $shop , $start , $end){//quantity
		$quantity = 0;
		$dates = 'created_date >= STR_TO_DATE("'.$start.'", "%Y-%m-%d") AND created_date < STR_TO_DATE("'.$end.'", "%Y-%m-%d") + INTERVAL 1 DAY';
 
		$categorydata = DB::query("SELECT sum(quantity) as quantity  FROM cash_sale_item WHERE  ".$dates." price_id=%i AND shop =%i"  , $price_id , $shop);
			if(DB::count() != 0){										
		       foreach ($categorydata as $category)
	              {
					  
			          $quantity =  $category['quantity'];
					 
					  
					
			  
		          }
			}
			return $quantity;
	}
	
	function Check_sold($price_id , $shop){//quantity
		$quantity = 0;
 
		$categorydata = DB::query("SELECT sum(quantity) as quantity  FROM cash_sale_item WHERE price_id=%i "  , $price_id  );
			if(DB::count() != 0){										
		       foreach ($categorydata as $category)
	              {
					  
			          $quantity =  $category['quantity'];
					 
					  
					
			  
		          }
			}
			return $quantity;
	}
	function jobbillings(){
		
		return $order= DB::query("SELECT * FROM repair_jobnames");
	}
	function joblocation($id){

		return DB::queryFirstField(" SELECT loc_name FROM repair_location WHERE locid =".$id." ");
							   
   
   }

   function customer_detailz($id){

	return DB::queryFirstField(" SELECT concat(name , ' ', phone) as customer   FROM customers  WHERE id =".$id." ");
						   

}

function check_code($id){

	return DB::queryFirstField(" SELECT   IFNULL(id , 0)    FROM cleancodes WHERE cleancode =".$id." ");
						   

}

function getcustomer_name($id){

	return DB::queryFirstField(" SELECT  name    FROM customers  WHERE id =".$id." ");
						   

}

function get_job_name($id){

	return DB::queryFirstField(" SELECT job_name  FROM repair_jobnames  WHERE job_id =".$id." ");
						   

}
//repair_jobtypes//repair_owner_category

function get_repair_owner($id){

	return DB::queryFirstField(" SELECT owner  FROM repair_owner_category  WHERE owid =".$id." ");
						   

}

function get_repair_type($id){

	return DB::queryFirstField(" SELECT rpt_name  FROM repair_jobtypes  WHERE rptid =".$id." ");
						   

}

function addnewJob($customer_name , $bill, $cashpaid, $receiptmode,  
 $customerchange, $notes, $attendant, $repaired_by, $customer_id, 
$customer_claim, $saletype, $jobtype, $ownercat, $location, $pickup, 
  $parent_code , $device , $taskmemo,  $job_bar_code
){
	$trxnConnextn = new MeekroDB();
			
			$trxnConnextn->startTransaction();

	DB::startTransaction();
	DB::query("SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE");
	DB::insert
	 
			('job_receipt', 
				array
				(
					'created_date' => date("Y-m-d H:i:s"),
					'created_by' => $_SESSION["connecteduserid"],
					'value_date' => date("Y-m-d"),
					'receipt_number' => $job_bar_code,
					'shop' => $_SESSION["connecteduserbranch"],
					'customer_name' => ($customer_name != "") ? $customer_name : "Sales",
					'bill' => $bill,
					'amount' => $cashpaid,
					'receipt_mode' => $receiptmode,
					'customer_change' => $customerchange,
					'memo' => $notes,
					'attendant' => $attendant,
					'repaired_by' => $repaired_by,
					'customer_id' => $customer_id,
					'claim' => $customer_claim,
					'sale_type' => $saletype,
                    'job_type' => $jobtype,
                    'owner_cat' => $ownercat,
                    'location' => $location,
                    'pickon' => $pickup,
					'jobdata' => $device, 
					'taskmemo'=>$taskmemo,
					 
					'parent_bar_code' => $parent_code
				)
			);
	
	//
	  $last = DB::insertId();
	  DB::commit();
	   return  $last;
	   

}


function service_code($code  ){
	$trxnConnextn = new MeekroDB();
			
			$trxnConnextn->startTransaction();

	DB::startTransaction();
	DB::query("SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE");
	DB::insert
	 
			('cleancodes', 
				array
				(
					 
					 
					'cleancode' => $code
				)
			);
	
	//
	  $last = DB::insertId();
	  DB::commit();
	   return  $last;
	   

}


function distcinct_jobs($status = false , $from=false , $job_bar_code=false  ){
	 
	$where =' ';
	if($status){
		$where .= ' and order_status ='.$status.' ';
	}

	if($from){
		$where .= ' and value_date >= "'.$from.'" ';
	}

	if($job_bar_code){
		$where .= ' and receipt_number = "'.$job_bar_code.'" ';
	}
	 return  DB::query("SELECT DISTINCT(parent_bar_code) FROM job_receipt WHERE id >0 ".$where." ORDER BY id DESC    "  );
			 
}

function targets($from = false  ){
	$where =' ';
	if($from){
		 
		$where .= ' where ta_id '.$from.' ';
	} 
	 
	 return  DB::query('SELECT *FROM targets  '.$where.''  );
			 
}

function mtargets($from = false  ){
	$where =' ';
	if($from){
		 
		$where .= ' where ta_id '.$from.' ';
	} 
	 
	 return  DB::query('SELECT *FROM mtargets  '.$where.''  );
			 
}

function generalsearch( $from = false , $limit=false , $search=false  ){
	$where =' ';
	if($from){
		 
		$where .= ' and value_date    >='.$from.' ';
	} 
	if($search){
		 $where .= ' and  (customer_name LIKE "%'.strtoupper($search).'%" OR parent_bar_code like "%'.$search.'%"  or jobdata LIKE  "%'.$search.'%" ) '; // OR contact like %"'.$search.'"% OR parent_bar_code like %"'.$search.'"% ) ';
	 }
	 
	 if($limit){
		 $where .= ' Limit '.$limit.'';
	 }
	 return  DB::query('SELECT DISTINCT(parent_bar_code) as parent_bar_code FROM job_receipt WHERE  id > 0 '.$where.' '  );
			 
}

  

function notcleared( $from = false , $limit=false , $search=false, $branch=false  ){
	$where =' ';
	if($from){
		 
		$where .= ' and value_date    >='.$from.' ';
	}
	
	if($branch > 0){
		 
		$where .= ' and shop    ='.$branch.' ';
	}
	if($search){
		 $where .= ' and (customer_name LIKE "%'.strtoupper($search).'%" OR parent_bar_code like "%'.$search.'%"  or jobdata LIKE  "%'.$search.'%" ) '; // OR contact like %"'.$search.'"% OR parent_bar_code like %"'.$search.'"% ) ';
	 }
	 
	 if($limit){
		 $where .= ' Limit '.$limit.'';
	 }
	 return  DB::query('SELECT DISTINCT(parent_bar_code) as parent_bar_code FROM job_receipt where bill > amount '.$where.' '  );
			 
}

function pending_invoices($from = false  ){
	  
	 return  DB::query('SELECT *FROM cash_receipt WHERE   is_cancelled = 0 and customer_change < 0   ' );
			 
}

function Invoicetopups($from = false  ){
	  
	return  DB::query('SELECT  receipt_id as id ,topup, receipt_number, bill, topay, newbalance, top_up_date, value_date, customer_name FROM invoicetopups  join cash_receipt c on c.id = invoicetopups.receipt_id  ORDER BY  topid DESC  ' );
			
}

function cleared($from = false  ){
	$where =' ';
	if($from){
		$where .= ' and value_date    >='.$from.' ';
	} 
	 
	 return  DB::query("SELECT DISTINCT(parent_bar_code) as parent_bar_code FROM job_receipt where bill > amount "  );
			 
}
function workshopcollected( $period=false , $shop=false){
    if( !$period){
      $period = date('Y-m-d');
    }
	$where ='';
	if($shop>0){
	$where =' and shop = '.$shop.' ';
		
		}
    //$period =  date('Y-m-d');
    $sum = 0;
    $categorydata = DB::query("SELECT     amount FROM job_receipt WHERE value_date  = '".$period."' ".$where."  AND is_cancelled = 0  GROUP by parent_bar_code" );
			if(DB::count() != 0){										
		       foreach ($categorydata as $category)
	              {
					  
			          $sum +=  $category['amount'];
                 
					 
		          
			}
			}
       
			return $sum;
   }

   function periodic_workshop_collections( $period=false){
    if( !$period){
      $period = date('Y-m-d');
    } 
    $sum = 0;
    $categorydata = DB::query("SELECT     amount FROM  job_receipt WHERE  MONTH(value_date) = MONTH(now())  and  YEAR(value_date) = YEAR(now())  AND is_cancelled = 0  GROUP by parent_bar_code" );
  
      
			if(DB::count() != 0){										
		       foreach ($categorydata as $category)
	              {
					  
			          $sum +=  $category['amount'];
                 
					 
		          
			}
			}
       
			return $sum;
   }

function todayjobsales($from = false , $branch=false ){
	 
	 
	 //return  DB::queryFirstField('SELECT SUM(amount) as amount  FROM job_receipt WHERE value_date = "'.$from.'"');
	 return workshopcollected( $from, $branch);
			 
}


//SELECT SUM(amount) as amount  FROM job_receipt WHERE value_date ="2023-06-04"

function amount_to_be_not_cleared($from=false, $branch=false){//
	      $total = 0;
	      $not_cleared = notcleared($from, false, false, $branch  );// $from = false , $limit=false , $search=false, $branch=false
		  if(!empty($not_cleared)){

			foreach($not_cleared as $row){
				$bar_code = $row['parent_bar_code'];
				$total += topay($bar_code  );
			}


		  }
		  return $total;


}

function topay($code = false  ){
	 
	$where =' ';
	 
	return  DB::queryFirstField('SELECT (bill -  amount) as  amount  FROM job_receipt   where parent_bar_code   =  "'.$code.'"  LIMIT 1' );

	  
			 
}

function invoicetopay($code = false  ){
	 
	$where =' ';
	 
	return  DB::queryFirstField('SELECT customer_change  FROM cash_receipt   where id   =  '.$code.'  LIMIT 1' );

	  
			 
}


 
function todaystopups($code = false , $shop=false  ){
	 
	$where =' ';
	 if($shop > 0){
		 $where =' and branch ='.$shop.' ';
		 }
	return  DB::queryFirstField('SELECT sum(topup) as topup FROM  topups where date(top_up_date) =   "'.$code.'"  '.$where.' ' );

	  
			 
}
function mtodaystopups($code = false  ){
	 
	$where =' ';
	 
	return  DB::queryFirstField('SELECT sum(topup) as topup FROM  topups where top_up_date  like   "'.$code.'%"  ' );

	  
			 
}



function topup_reports($code = false  ){
	 //
	$where =' ';
	 
	return  DB::query('SELECT   * FROM topups     where top_up_date >=   "'.$code.'"  ' );

	  
			 
}

function job_branch($code = false  ){
	 
	$where =' ';
	 
	return  DB::queryFirstField('SELECT shop FROM job_receipt   where parent_bar_code   =  "'.$code.'"  LIMIT 1' );

	  
			 
}

function jobbalance($code = false  ){
	 
	$where =' ';
	 
	return  DB::queryFirstField('SELECT amount FROM job_receipt   where parent_bar_code   =  "'.$code.'"  LIMIT 1' );

	  
			 
}
function Sold_items($shop=false){
	$where ='';
	if($shop > 0) {
		$where =' shop = '.$shop.' and ';
		}
	
	 return number_format(DB::queryFirstField("SELECT COALESCE(SUM(quantity), 0) FROM cash_sale_item WHERE  ".$where." (created_date>=%s AND created_date<=%s) AND   is_cancelled = 0", date("Y-m-d"), date("Y-m-d")), 2); 
					
}
function Transactions($shop=false){
	$where ='';
	if($shop > 0) {
		$where =' and shop= '.$shop.'';
		}
	
	return number_format(DB::queryFirstField('SELECT COUNT(id) FROM cash_receipt WHERE   value_date = CURRENT_DATE() AND is_cancelled = 0 '.$where.''));
}

function Todays_sales($shop=false){
	$where ='';
	if($shop > 0) {
		$where =' and shop= '.$shop.'';
		}
	  $cash =  intval(DB::queryFirstField('SELECT COALESCE(SUM(bill), 0) FROM cash_receipt WHERE   value_date = CURRENT_DATE() AND is_cancelled = 0 '.$where.''));
                    $debt   =  intval(DB::queryFirstField('SELECT COALESCE(SUM(customer_change), 0) FROM cash_receipt WHERE   value_date = CURRENT_DATE() AND is_cancelled = 0 and customer_change < 0 '.$where.''));
       return $cash + $debt;         
}

function theinvoicebalances($shop=false){
	$where ='';
	if($shop > 0){
		$where = '  and shop='.$shop.'';
		}
	return   intval(DB::queryFirstField('SELECT COALESCE(SUM(customer_change), 0) FROM cash_receipt WHERE      is_cancelled = 0 and customer_change < 0 '.$where.' '));
        
}
function invoicebalance($code = false  ){
	 
	$where =' ';
	//$debt   =  intval(DB::queryFirstField("SELECT COALESCE(SUM(customer_change), 0) FROM cash_receipt WHERE shop=%i AND  is_cancelled = 0 and customer_change < 0 ", $branch));
        
	 
	return  DB::queryFirstField('SELECT amount FROM cash_receipt   where id   =  "'.$code.'"  LIMIT 1' );

	  
			 
}

function invoicereceipt($code = false  ){
	 
	$where =' ';
	 
	return  DB::queryFirstField('SELECT receipt_number FROM cash_receipt   where id   =  "'.$code.'"  LIMIT 1' );

	  
			 
}

function topups($code = false  ){
	 
	$where =' ';
	 
	 return  DB::query('SELECT sum(topup) as topup FROM topups  where top_bar_code  =  "'.$code.'" ' );
			 
}


function distcinct_jobs_repost( $status=false, $from=false, $shop=false ){ 
	$quantity = 0;
	   $shop_label = '';

	   $where = '';
	  if($shop > 0){
		$where .= ' and shop    = '.$shop.' ';

	} 
	  
	if($from){
		$where .= ' and value_date    >= "'.$from.'" ';

	}

 
if($status){
	$where .= ' and order_status ='.$status.' ';
}
 return  DB::query("SELECT DISTINCT(parent_bar_code) FROM job_receipt WHERE id >0 ".$where."  ORDER BY id DESC  LIMIT 50  "  );
		 
}

function distcinct_tasks( $status=false, $from=false , $shop=false ){  
	   $where = '';
	   
	  
	if($from){
		$where .= ' and  created_date like     "'.$from.'%" ';

	}
	if($shop>0){
		$where .= ' and  shop  =    '.$shop.' ';

	}

 
 return  DB::queryFirstField("SELECT  IFNULL(sum(quantity) , 0) as quantity FROM job_sale_item WHERE   id >0 ".$where."    "  );
		 
}

function search_barcode($status = false){ 
	$where =' ';
	if($status){
		$where .= ' where  parent_bar_code = "'.$status.'"';
	}
	 return  DB::query("SELECT    parent_bar_code   FROM job_receipt      ".$where." LIMIT 1"  );
			 
}

function job_bar_code_from_id($status = false){ 
	$where =' ';
	if($status){
		$where .= ' where  id  = '.$status.'';
	}
	 return  DB::queryFirstField("SELECT    parent_bar_code   FROM job_receipt      ".$where." LIMIT 1"  );
			 
}

function job_bar_code_from_id2($status = false){ 
	$where =' ';
	if($status){
		$where .= ' where  id  = '.$status.'';
	}
	 return  DB::queryFirstField("SELECT    receipt_number   FROM job_receipt      ".$where." LIMIT 1"  );
			 
}

function job_id($status = false){ 
	$where =' ';
	if($status){
		$where .= ' where  parent_bar_code   = '.$status.'';
	}
	 return  DB::queryFirstField("SELECT    id   FROM job_receipt      ".$where." LIMIT 1"  );
			 
}

 

function getjob_by_id($id){ 
	 


	   $sql = 'SELECT job.id, c.phone, s.order_status_name as status , parent_bar_code,  receipt_number, amount, value_date, shop, customer_name, pickon,  jobdata, bill, os.order_status_name , rpt_name, owner FROM job_receipt as job JOIN order_status as os on os.status_id = job.order_status JOIN  repair_jobtypes as jt on jt.rptid = job.job_type JOIN repair_owner_category as ow on ow.owid = owner_cat  join order_status s on s.status_id = order_status JOIN customers c on c.id = customer_id where job.id =  '.$id.' ';
	 return  DB::query($sql);
	   
		
	}

	function getjob_by_barcode($id){ 
	 


		$sql = 'SELECT created_by as personel, c.phone as cphone, job.id, c.phone, s.order_status_name as status , parent_bar_code,  receipt_number, amount, value_date, shop, customer_name, pickon,  jobdata, bill, os.order_status_name , rpt_name, owner FROM job_receipt as job JOIN order_status as os on os.status_id = job.order_status JOIN  repair_jobtypes as jt on jt.rptid = job.job_type JOIN repair_owner_category as ow on ow.owid = owner_cat  join order_status s on s.status_id = order_status JOIN customers c on c.id = customer_id where job.parent_bar_code =  "'.$id.'"  LIMIT  1 ';
	  return  DB::query($sql);
		
		 
	 }

	function brachdetails($id){ 
	 

   return  DB::query('SELECT * FROM shop where id= '.$id.'');
		
		 
	 }

	 function payment_name($id){

		    return DB::queryFirstField('  SELECT  payment_name     from payment_type where id ='.$id.'');
	 }

	 function Job_customer($id){

		return DB::queryFirstField('  SELECT  customer_name    from job_receipt where parent_bar_code ='.$id.'');
 }

	 function minijobreport($thedate){ 
		$sql ='SELECT customer_name, receipt_number, job_receipt.id ,  bill, customer_change, amount, sale_type,
		 payment_type.payment_name, created_date FROM job_receipt join payment_type on receipt_mode = payment_type.id 
		 where job_receipt.value_date ="'.$thedate.'" GROUP by parent_bar_code  ORDER BY  job_receipt.created_date DESC';
	 
		return DB::query($sql);
	}

	function singlejobreport($thedate){ 
		$sql ='SELECT customer_name, receipt_number, job_receipt.id ,  bill, customer_change, amount, sale_type,
		 payment_type.payment_name, created_date FROM job_receipt join payment_type on receipt_mode = payment_type.id 
		 where  parent_bar_code  ='.$thedate.'  GROUP by parent_bar_code';
	 
		return DB::query($sql);
	}

	function minitopups($thedate){ 
		//created_date >= STR_TO_DATE("'.$start.'", "%Y-%m-%d")
		$sql ='SELECT *from topups join payment_type on pmode = payment_type.id
		
		where top_up_date >= STR_TO_DATE("'.$thedate.'" , "%Y-%m-%d")  ORDER BY  top_up_date DESC';
	 
		return DB::query($sql);
	}
 
	 function barcode_jobs($bar_code){ 
		$quantity = 0;
		   $shop_label = '';
	
		   $where = '';
		   if($bar_code){
			
			$where .= ' and parent_bar_code  ="'.$bar_code.'" '; 
		   }
		 
	
	
		   $sql = 'SELECT job.id, c.phone,  taskmemo, s.order_status_name as status , parent_bar_code, receipt_number, job.order_status, amount, value_date, shop, customer_name, pickon,  jobdata, bill, os.order_status_name , rpt_name, owner FROM job_receipt as job JOIN order_status as os on os.status_id = job.order_status JOIN  repair_jobtypes as jt on jt.rptid = job.job_type JOIN repair_owner_category as ow on ow.owid = owner_cat  join order_status s on s.status_id = order_status JOIN customers c on c.id = customer_id where job.id > 0  '.$where.' ';
		 $categorydata = DB::query($sql);
		   
			
			$total_cp  =0;
			   if(DB::count() != 0){										
				  foreach ($categorydata as $row)
					 {
						$change = $row['amount']- $row['bill'] ;
						if($change > 0){
							$change = 0;
	
						}
						$state_color = $row['order_status'];
						$color ='success';

						if($state_color ==5){
							$color ='danger';

						}else if($state_color ==6){
							$color ='warning';

						}
						 
						  
						 
						echo '<tr class="  table-'.$color.'">
						<td> <a href="'.$GLOBALS['base_url'].'jobreceiptdetails.php/receipt/'.$row['id'].'"> '.$row['receipt_number'].' </a> </td>
						<td>'.$row['jobdata'].' ('.$row['taskmemo'].' ) </td>
						
						<td>'.$row['value_date'].' </td>
						
						<td> '.$row['customer_name'].'</td> 
						<td> '.$row['phone'].'</td> 
						<td> '.number_format($row['bill']).'</td> 
						<td> '.number_format($change).'</td>  
						<td> '.$row['rpt_name'].'</td> 
						<td> '.$row['owner'].'</td> 
						 
						<td > <label > '.$row['status'].' </label></td> 
						<td> '.$row['pickon'].'</td>
						  
						
	
						</tr>';
						
						
						
						 
					   
				 
					 }
					 
					 
			   }
				
	   }
	   
	   
	 function barcode_jobs_mini($bar_code , $pickon){ 
		$quantity = 0;
		   $shop_label = '';
	
		   $where = '';
		   if($bar_code){
			
			$where .= ' and customer_id  ='.$bar_code.'   and pickon="'.$pickon.'" '; 
		   }
		 
	
	
		   $sql = 'SELECT job.id, c.phone,  taskmemo, s.order_status_name as status , parent_bar_code, receipt_number, job.order_status, amount, value_date, shop, customer_name, pickon,  jobdata, bill, os.order_status_name , rpt_name, owner FROM job_receipt as job JOIN order_status as os on os.status_id = job.order_status JOIN  repair_jobtypes as jt on jt.rptid = job.job_type JOIN repair_owner_category as ow on ow.owid = owner_cat  join order_status s on s.status_id = order_status JOIN customers c on c.id = customer_id where job.id > 0  '.$where.' ';
		 $categorydata = DB::query($sql);
		  $color ='';
			
			$total_cp  =0;
			   if(DB::count() != 0){
                  $data =' <table class="table table-striped table-bordered"  >';				   
				  foreach ($categorydata as $row)
					 {
						 
						 $data .= '<tr class="  table-'.$color.'">
						 
						<td width="50%">'.$row['jobdata'].' ('.$row['taskmemo'].' ) </td> 
						 
						<td> '.$row['rpt_name'].'</td>  
						 
						<td > <label > '.$row['status'].' </label></td> 
						<td> '.$row['pickon'].'</td>
						  
						
	
						</tr>';
						
						
						
						 
					   
				 
					 }
				 $data .=' </table>';	 
					 
			   }
			   return  $data;
				
	   }

function barcode_jobs_for_receipt($bar_code){ 
	$quantity = 0;
	   $shop_label = '';
	   $where = '';
	   if($bar_code){
		$where .= ' and parent_bar_code  ="'.$bar_code.'" '; 
	   }
	  
	   $sql = 'SELECT job.id, c.phone, s.order_status_name as status ,taskmemo, parent_bar_code, receipt_number, amount, value_date, shop, customer_name, pickon,  jobdata, bill, os.order_status_name , rpt_name, owner FROM job_receipt as job JOIN order_status as os on os.status_id = job.order_status JOIN  repair_jobtypes as jt on jt.rptid = job.job_type JOIN repair_owner_category as ow on ow.owid = owner_cat  join order_status s on s.status_id = order_status JOIN customers c on c.id = customer_id where job.id > 0  '.$where.' ';
	   return DB::query($sql);
	    
			
   }

   function receipt_item_by_barcodes($code){  
	 

	  return   DB::query('
	   SELECT     local_bar_code, rj.job_name, rj.job_amount, sell_price, rpt_name FROM job_sale_item as job LEFT  JOIN repair_jobnames as  rj  on  job_id = job.price_id  LEFT JOIN repair_jobtypes jt on jt.rptid = sales_type  where is_cancelled =0 AND general_bar_code ="'.$code.'"
	    ');
		 		 
			
   }

   function receipt_item_by_id($code){  
	 

	return   DB::query('
	 SELECT     local_bar_code, rj.job_name, rj.job_amount, sell_price, rpt_name, local_bar_code, general_bar_code FROM job_sale_item as job LEFT  JOIN repair_jobnames as  rj  on  job_id = job.price_id  LEFT JOIN repair_jobtypes jt on jt.rptid = sales_type  where is_cancelled =0 AND id ="'.$code.'"
	  ');
				
		  
 }

   
   function receipt_item_by_barcodes2($code){  
	 

	return   DB::query('
	 SELECT     local_bar_code, rj.job_name, rj.job_amount, sell_price, rpt_name FROM job_sale_item as job LEFT  JOIN repair_jobnames as  rj  on  job_id = job.price_id  LEFT JOIN repair_jobtypes jt on jt.rptid = sales_type  where is_cancelled =0 AND local_bar_code ="'.$code.'"
	  ');
				
		  
 }


function add_job_activities(
	$receipt_no, $job_id, $amount,   $gen_code, $local_barcode 
  
){ 
//$gen_bar_code >> receipt_no >>same on the whole recipt

	DB::startTransaction();
	DB::query("SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE");
	DB::insert
	 
	('job_sale_item', 
	array
	(
		'created_date' => date("Y-m-d H:i:s"),
		'created_by' => $_SESSION["connecteduserid"],
		'shop' => $_SESSION["connecteduserbranch"],
		'receipt' => $receipt_no,
		'price_id' => $job_id,
		'sell_price' => $amount,
		
		'quantity' => 1,
		 'sales_type' => 1,
		  
		'sales_type' => 1,
		'general_bar_code' => $gen_code,
		'local_bar_code' => $local_barcode

				)
			);
	
	DB::commit();
	 
}

function technician_name($id){

	 return DB::queryFirstField(" SELECT last_name  FROM personnel_details WHERE id =".$id." ");
						   

}
function get_job_amount($id){

	return DB::queryFirstField(" SELECT job_amount   FROM repair_jobnames  WHERE job_id =".$id." ");
						   

}


	function Current_stock($item , $shop){//quantity
		$quantity = 0;
 
		$categorydata = DB::query("SELECT sum(quantity) as quantity FROM item_stock_level WHERE item=%i AND shop =%i LIMIT 1"  , $item , $shop);
			if(DB::count() != 0){										
		       foreach ($categorydata as $category)
	              {
					  
			          $quantity =  $category['quantity'];
					 
					  
					
			  
		          }
			}
			return $quantity;
	}
	#Amount sold here
	
	function Repair_com( $tech, $start_date , $end_date){//quantity
		$quantity = 0;
		$items = DB::query("SELECT   sum(bill) as bill  FROM cash_receipt where is_cancelled < 1 AND repaired_by = {$tech}  and value_date between '{$start_date}' and '{$end_date}'");												
		
 
			if(DB::count() != 0){										
		       foreach ($items as $category)
	              {
					  
			          $quantity =  $category['bill'];
					 
					  
					
			  
		          }
			}
			return $quantity;
	}
	
	function Sum_Repair_com( $tech, $start_date , $end_date){ 
		$sum = 0;
		$items = DB::query("SELECT    bill    FROM cash_receipt where is_cancelled < 1 AND repaired_by = {$tech}  and value_date between '{$start_date}' and '{$end_date}'");												
		
 
			if(DB::count() != 0){										
		       foreach ($items as $category)
	              {
					  
			          $bill = $category['bill'];
					  $rate_code = Select_tech_commision_rate($bill);
					   
					  $actual_rate = Commission_settings(  $rate_code);
					  $sub_total = $bill*$actual_rate/100 ;
					  $sum += $sub_total;
					  
					  
				 
		          }
			}
			return $sum;
	}
	
	function Sum_sales_com(  $tech, $start_date , $end_date){//quantity
		$sum = 0;
		$items = DB::query("SELECT   bill     FROM cash_receipt where is_cancelled < 1 AND  attendant = {$tech}  and value_date between '{$start_date}' and '{$end_date}'");
		
		 	if(DB::count() != 0){										
		       foreach ($items as $category)
	              {
					  
			           
					  $bill = $category['bill'];
					  $rate_code = Select_sales_commision_rate($bill);
					  $quantity =  $category['bill'];
					  $actual_rate = Commission_settings(  $rate_code);
					  $sub_total =  $bill*$actual_rate/100  ;
					  $sum += $sub_total;
					  
			  
		          }
			}
			return $sum;
	}
	
	function Sales_com(  $tech, $start_date , $end_date){//quantity
		$quantity = 0;
		$items = DB::query("SELECT   sum(bill) as bill   FROM cash_receipt where is_cancelled < 1 AND  attendant = {$tech}  and value_date between '{$start_date}' and '{$end_date}'");
		
		 	if(DB::count() != 0){										
		       foreach ($items as $category)
	              {
					  
			          $quantity =  $category['bill'];	
			  
		          }
			}
			return $quantity;
	}
	
	function Sum_bill(  $tech, $start_date , $end_date){//quantity
		$quantity = 0;
		 
		$items = DB::query("SELECT   sum(bill) as bill   FROM cash_receipt where is_cancelled < 1 AND  attendant = {$tech}  and value_date between '{$start_date}' and '{$end_date}'");
		
		 	if(DB::count() != 0){										
		       foreach ($items as $category)
	              {
					  
			          $quantity =  $category['bill'];
					 
					 
			  
		          }
			}
			return $quantity;
	}
	
	function Sum_tech_bill(  $tech, $start_date , $end_date){//quantity
		$quantity = 0;
		 
		$items = DB::query("SELECT   sum(bill) as bill   FROM cash_receipt where is_cancelled < 1 AND  repaired_by  = {$tech}  and value_date between '{$start_date}' and '{$end_date}'");
		
		 	if(DB::count() != 0){										
		       foreach ($items as $category)
	              {
					  
			          $quantity =  $category['bill'];
					 
					 
			  
		          }
			}
			return $quantity;
	}
	
	  
	
	function Personel_name($person){//
		$name = 'N/A';
		 
		return DB::queryFirstField(" SELECT first_name FROM personnel_details where id =".$person." ");
			 
	}
	
	
	
	function Shop_name($shop){
		$name = 'Store';
		$categorydata = DB::query("SELECT full_name FROM shop where id =%i" , $shop);
			if(DB::count() != 0){										
		       foreach ($categorydata as $category)
	              {
			 $name =  $category['full_name'];
			  
		          }
			}
			return $name;
	}
	
	function PriceId2($item , $shop){
		$id = 0;
		$count =0;
		$item_sold_per_id = 0;
		$categorydata = DB::query("SELECT id FROM item_price where item=%i  and shop = %i "  , $item , $shop);
			if(DB::count() != 0){										
		       foreach ($categorydata as $category)
	              {
					  if($count > 0) exit('Error here');
					   
			          $id =  $category['id'];
					 
					 $count +=1;
				 
					
			  
		          }
			}
			return $id;
	}
	
	function Item_id_from_item($item , $shop){
		$id = 0;
		$count =0;
		$item_sold_per_id = 0;
		$categorydata = DB::query("SELECT id FROM item_price where item=%i  and shop = %i "  , $item , $shop);
			if(DB::count() != 0){										
		       foreach ($categorydata as $category)
	              {
					  if($count > 0) exit('Error here');
					   
			          $id =  $category['id'];
					 
					 $count +=1;
				 
					
			  
		          }
			}
			return $id;
	}
	
	
	function Item_category($cat){
		$name = 'N/A'; 
		$categorydata = DB::query("SELECT category_name FROM item_category where id=%i    "  , $cat   );
			if(DB::count() != 0){										
		       foreach ($categorydata as $category)
	              {
					  
			          $name =  $category['category_name'];
					 
		          
			}
			}
			return $name;
	}
	
	
	
	
		function notcollected($price_id, $start, $end  , $item){
			 $seles_details = '<tr class="success" ><td>'.$item.' </td><td> </td><td>  </td>  <td>  </td><td>   </td> <td>  </td><td>   </td> <td>  </td> </tr>';
			
			$epecific_price_id_sales = 0;
			 $stockitemsdata = DB::query('SELECT *FROM cash_sale_item WHERE is_cancelled = 0   AND created_date < STR_TO_DATE("'.$end.'", "%Y-%m-%d") + INTERVAL 1 DAY  ORDER BY id'); 
		
			 
		     if(DB::count() != 0){
				foreach ($stockitemsdata  as $category)
	              {
			       
				   $seles_details .= '<tr class="success" ><td> </td><td> '.Shop_name($category['shop']).'</td> <td> '. $category['created_date'].'</td>  <td>'.$category['receipt'].' </td><td>  '.$category['serial_number'].' </td> <td> '.$category['quantity'].'</td><td>   </td> <td>  </td></tr>';
			
			  
		          } 
				
			 }
			 else {$seles_details ='';}
			 return $seles_details;
			  
	}
	
	
		function Item_sale_details($price_id, $start, $end  , $item){
			 $seles_details = '<tr class="success" ><td>'.$item.' </td><td> </td><td>  </td>  <td>  </td><td>   </td> <td>  </td><td>   </td> <td>  </td> </tr>';
			
			$epecific_price_id_sales = 0;
			 $stockitemsdata = DB::query('SELECT *FROM cash_sale_item WHERE is_cancelled = 0   AND created_date < STR_TO_DATE("'.$end.'", "%Y-%m-%d") + INTERVAL 1 DAY  ORDER BY id'); 
		
			 
		     if(DB::count() != 0){
				foreach ($stockitemsdata  as $category)
	              {
			       
				   $seles_details .= '<tr class="success" ><td> </td><td> '.Shop_name($category['shop']).'</td> <td> '. $category['created_date'].'</td>  <td>'.$category['receipt'].' </td><td>  '.$category['serial_number'].' </td> <td> '.$category['quantity'].'</td><td>   </td> <td>  </td></tr>';
			
			  
		          } 
				
			 }
			 else {$seles_details ='';}
			 return $seles_details;
			  
	} 
	
	function Special_sale_details($price_id, $start, $end  , $item , $bounce_type){
		$color ='class="info"';
		if($bounce_type ==2){
			$color ='class="warning"';
		}
		
			 $seles_details = '<tr  '.$color.'.><td>'.$item.' </td><td> </td><td>  </td>  <td>  </td><td>   </td> <td>  </td><td>   </td> <td>  </td></tr>';
			
			$epecific_price_id_sales = 0;
			 $stockitemsdata = DB::query('SELECT *FROM special_sale_item WHERE  bounce_type ='.$bounce_type.' AND is_cancelled = 0 AND price_id ='.$price_id.' and created_date >= STR_TO_DATE("'.$start.'", "%Y-%m-%d") AND created_date < STR_TO_DATE("'.$end.'", "%Y-%m-%d") + INTERVAL 1 DAY  ORDER BY id'); 
		
			 
		     if(DB::count() != 0){
				foreach ($stockitemsdata  as $category)
	              {
			       
				   $seles_details .= '<tr  '.$color.'.><td> </td><td> '.Shop_name($category['shop']).'</td> <td> '. $category['created_date'].'</td>  <td>'.$category['receipt'].' </td><td>  '.$category['serial_number'].' </td> <td></td><td></td><td> '.$category['quantity'].'</td></tr>';
			
			  
		          } 
				
			 }
			 else {$seles_details ='';}
			 return $seles_details;
			  
	}
	
	
	
	
	
	 function Item_movedemts(  $destination, $end  , $item){
								            $seles_details = ' ';
											  
											$epecific_price_id_sales = 0;
											$total = 0;
											 $stockitemsdata = DB::query('SELECT *FROM stock_item_movement WHERE  item ='.$item.'   and  (destination ='.$destination.'  or origin ='.$destination.') ORDER BY id'); 
										
											 
											 if(DB::count() != 0){
												foreach ($stockitemsdata  as $category)
												  {
													  $alert =  '<i class="fa fa-times "> </i>Yes';
													  $total +=$category['quantity'];
													  if($category['is_complete'] >-1){
														   
														  $alert =  '<i class="fa fa-check "> </i>No';
													  }
												   
												   $seles_details .= '<tr class="success" ><td> '.Shop_name($category['origin']).'</td> <td> '.Shop_name($category['destination']).'</td>  
												   <td>'.$category['created_date'].' </td><td>  '.$category['memo'].' </td> <td> '.$alert.'</td>
												   <td> '.Personel($category['created_by']).'  </td> <td> '.$category['quantity'].'</td>
												    <td>   
													<a href="Recelling_items_from_shop.php"    >
											         Recall  </a> 
													
													</td>
												   
												   </tr>';
												   //Recal_items(info , shop , id)
											
											  
												  } 
												
											 }
											 else {$seles_details ='';}
											 return array($seles_details , $total);
							}
							
							function Item_movements(  $destination,   $item , $start , $end ){
								            $seles_details = ' ';
											 
										 
											  
											$epecific_price_id_sales = 0;
											$total = 0;
											$item_label  = $dest_lebel = '';
											
											if( $destination > 0 ){
												 $dest_lebel ='(destination ='.$destination.'  or origin ='.$destination.') AND ';  
												
											}
											  
											
											$item_label  = '';
											if($item > 0){
												 $item_label  =' item ='.$item.' AND '; 
												
											}
											//created_date >= STR_TO_DATE("'.$start.'", "%Y-%m-%d") AND created_date < STR_TO_DATE("'.$end.'", "%Y-%m-%d") + INTERVAL 1 DAY
											//created_date >= STR_TO_DATE("'.$start.'", "%Y-%m-%d") AND created_date < STR_TO_DATE("'.$end.'", "%Y-%m-%d") + INTERVAL 1 DAY
											// $stockitemsdata = DB::query('SELECT *FROM stock_item_movement WHERE   item ='.$item.'   and  (destination ='.$destination.'  or origin ='.$destination.') ORDER BY id'); 
										
										
										$stockitemsdata = DB::query('SELECT *FROM stock_item_movement WHERE   '.$dest_lebel.' '.$item_label.' created_date >= STR_TO_DATE("'.$start.'", "%Y-%m-%d") AND created_date < STR_TO_DATE("'.$end.'", "%Y-%m-%d") + INTERVAL 1 DAY ORDER BY id DESC'); 
										
											 
											 if(DB::count() != 0){
												foreach ($stockitemsdata  as $category)
												  {
													  $alert =  '<i class="fa fa-times "> </i>Yes';
													  $total +=$category['quantity'];
													  if($category['is_complete'] >-1){
														   
														  $alert =  '<i class="fa fa-check "> </i>No';
													  }
												   
												   $seles_details .= '<tr class="success" >  '.Item_data($category['item']).'  <td> '.Shop_name($category['origin']).'</td> <td> '.Shop_name($category['destination']).'</td>  
												   <td>'.$category['created_date'].' </td><td>  '.$category['memo'].' </td> <td> '.$alert.'</td>
												   <td> '.Personel($category['created_by']).'  </td> <td> '.$category['quantity'].'</td>
												    <td>   
													<a href="Recelling_items_from_shop.php"    >
											         Recall  </a> 
													
													</td>
												   
												   </tr>';
												   //Recal_items(info , shop , id)
											
											  
												  } 
												
											 }
											 else {$seles_details ='';}
											 return array($seles_details , $total);
							}


function Sum_received($item , $shop){
		 
		$total = 0;
		$categorydata = DB::query('SELECT sum(quantity) as qty FROM stock_item_movement WHERE  item ='.$item.'    and destination ='.$shop.'  '); 
			if(DB::count() != 0){										
		       foreach ($categorydata as $category)
	              {
					  
			         $total += $category['qty'];
					 
		          
			}
			}
			return $total;
	}
								

?>