<?php
class Sales{
	    // database connection and table name
	private $conn;
	private $table_name = "sales";

	// object properties

	public $id;
	public $city;
	public $sales_date;
	public $invoice_no;
	public $customer_id;
    public $name;
	public $id_no;
	public $package;
	public $mbps;
	public $months;
	public $days;
	public $price;
	public $fee;
	public $tax_percent;
	public $normal_discount;
	public $member_discount;
	public $promotion_discount;
	public $start_date;
	public $end_date;
	public $refund_amount;
	public $price_change_charges;

	public $installation_address;
	public $S_R;
	public $entry_by;
	public $entry_date_time;

	public function __construct($db){
		$this->conn = $db;
	}

	function create(){
		$query = "INSERT INTO `" . $this->table_name . "` SET city=:city, sales_date=:sales_date, invoice_no=:invoice_no, customer_id=:customer_id
        , name=:name, id_no=:id_no, package=:package, mbps=:mbps, months=:months, days=:days, price=:price, fee=:fee, tax_percent=:tax_percent, normal_discount=:normal_discount, member_discount=:member_discount, promotion_discount=:promotion_discount
		, start_date=:start_date, end_date=:end_date, refund_amount=:refund_amount, price_change_charges=:price_change_charges, installation_address=:installation_address
		, S_R=:S_R, entry_by=:entry_by, entry_date_time=:entry_date_time ";

		$stmt = $this->conn->prepare($query);
		$stmt->bindParam(":city", $this->city);
		$stmt->bindParam(":sales_date", $this->sales_date);
		$stmt->bindParam(":invoice_no", $this->invoice_no);

        $stmt->bindParam(":customer_id", $this->customer_id);
        $stmt->bindParam(":name", $this->name);
		$stmt->bindParam(":id_no", $this->id_no);
		$stmt->bindParam(":package", $this->package);
		$stmt->bindParam(":mbps", $this->mbps);
		$stmt->bindParam(":months", $this->months);
		$stmt->bindParam(":days", $this->days);
		$stmt->bindParam(":price", $this->price);
		$stmt->bindParam(":fee", $this->fee);
		$stmt->bindParam(":tax_percent", $this->tax_percent);
		$stmt->bindParam(":normal_discount", $this->normal_discount);
		$stmt->bindParam(":member_discount", $this->member_discount);
		$stmt->bindParam(":promotion_discount", $this->promotion_discount);
		$stmt->bindParam(":start_date", $this->start_date);
		$stmt->bindParam(":end_date", $this->end_date);
		$stmt->bindParam(":refund_amount", $this->refund_amount);
		$stmt->bindParam(":price_change_charges", $this->price_change_charges);
		$stmt->bindParam(":installation_address", $this->installation_address);

		$stmt->bindParam(":S_R", $this->S_R);
		$stmt->bindParam(":entry_by", $this->entry_by);
		$stmt->bindParam(":entry_date_time", $this->entry_date_time);

		if($stmt->execute()){	
			$this->id = $this->conn->lastInsertId();
			return true;
		}
		return false;		
    }

	function edit(){
		$query = "UPDATE `" . $this->table_name . "` SET sales_date=:sales_date,package=:package, mbps=:mbps, days=:days
		, price=:price, fee=:fee, tax_percent=:tax_percent, start_date=:start_date, entry_by=:entry_by, entry_date_time=:entry_date_time where invoice_no=:invoice_no ";

		$stmt = $this->conn->prepare($query);

		$stmt->bindParam(":invoice_no", $this->invoice_no);
		$stmt->bindParam(":sales_date", $this->sales_date);
		$stmt->bindParam(":package", $this->package);
		$stmt->bindParam(":mbps", $this->mbps);
		$stmt->bindParam(":days", $this->days);
		$stmt->bindParam(":price", $this->price);
		$stmt->bindParam(":fee", $this->fee);
		$stmt->bindParam(":tax_percent", $this->tax_percent);
		$stmt->bindParam(":start_date", $this->start_date);
		$stmt->bindParam(":entry_by", $this->entry_by);
		$stmt->bindParam(":entry_date_time", $this->entry_date_time);

		if($stmt->execute()){	
			$this->id = $this->conn->lastInsertId();
			return true;
		}
		return false;		
    }

	function delete(){
		$query = "DELETE FROM " . $this->table_name . " WHERE invoice_no =:invoice_no";
		$stmt = $this->conn->prepare($query);
		$this->invoice_no = htmlspecialchars(strip_tags($this->invoice_no));
		$stmt->bindParam(":invoice_no", $this->invoice_no);
		if($stmt->execute()){
			return true;
		}
		return false;
	}
    
    function getGenerateInvoiceNo($prefix){
		$query = "SELECT invoice_no FROM `" . $this->table_name . "` WHERE invoice_no like ? ORDER BY invoice_no desc limit 0,1";
		$stmt = $this->conn->prepare($query);
		$keywords = htmlspecialchars(strip_tags($prefix));
		$keywords = "{$keywords}%";
		
		$stmt->bindParam(1, $keywords);
		$stmt->execute();
		if($stmt->rowCount()>0){
			$row = $stmt->fetch(PDO::FETCH_ASSOC);
			return $prefix . sprintf("%'04d", ((int)str_replace($prefix,"",$row['invoice_no']) + 1));
		}else{
			return $prefix . "0001";
		}
	}

	function getToInstall(){

		if($this->ty=="Cable"){
			$query = "SELECT sales.*,phone FROM sales 
			LEFT JOIN customer ON sales.customer_id=customer.id
			LEFT JOIN installation ON sales.id_no=installation.id_no
			WHERE sales.city=:city AND installation.id IS null";
		}else if($this->ty=="ONU"){
			$query = "SELECT sales.*,phone FROM sales 
			LEFT JOIN customer ON sales.customer_id=customer.id
			LEFT JOIN (SELECT id_no,COUNT(*) AS c FROM installation GROUP BY invoice_no) AS a ON sales.id_no=a.id_no
						WHERE sales.city=:city AND c=1";
		}
		
		$stmt = $this->conn->prepare($query);

		$stmt->bindParam(":city", $this->city);

		$stmt->execute();
		return $stmt;
	}

	function getItemSalesCustomer(){

		$query = "SELECT sales.id_no,sales.name,phone,sales.invoice_no
		FROM (SELECT MAX(id) mxid FROM sales where city=:city and start_date<>'' GROUP BY id_no) AS a 
		LEFT JOIN sales ON sales.id=a.mxid
		LEFT JOIN customer ON sales.customer_id=customer.id
		group by sales.id_no ORDER BY sales.id_no";
		$stmt = $this->conn->prepare( $query );
		$stmt->bindParam(":city", $this->city);
		$stmt->execute();
		return $stmt;
	}

	function getAllIDNoRenew(){

		$query = "SELECT sales.*,customer.name as cname,customer.member_type,nrc,phone,member.member_discount as mdp,customer.address,
		0 as bal,
		member.member_install_discount as mid,DATE_ADD(start_date, INTERVAL ifnull(tdays,0)+sales.days+(sales.months*30)-1 DAY) as end_dates,ont.`status` AS customer_status
		FROM (SELECT MAX(id) mxid FROM sales where city=:city and start_date<>'' GROUP BY id_no) AS a 
		LEFT JOIN sales ON sales.id=a.mxid
		LEFT JOIN (SELECT invoice_no,SUM(days) AS tdays FROM sales_detail GROUP BY invoice_no) AS b ON sales.invoice_no=b.invoice_no
		LEFT JOIN customer ON sales.customer_id=customer.id
		LEFT JOIN member on customer.member_type=member.member_type 
		LEFT JOIN ont ON sales.id_no=ont.id_no
		-- LEFT JOIN (SELECT invoice_no,SUM(amount) AS pam FROM payment GROUP BY invoice_no) AS p ON sales.invoice_no=p.invoice_no
		group by sales.id_no ORDER BY sales.id_no";
		$stmt = $this->conn->prepare( $query );
		$stmt->bindParam(":city", $this->city);
		$stmt->execute();
		return $stmt;
	}

	function getCurrentBalance(){
		$query = "SELECT sum(price*(tax_percent/100)+months*price+fee+IFNULL(otamount,0)-normal_discount-sales.member_discount-promotion_discount-refund_amount+price_change_charges-ifnull(amount,0)) as bal FROM sales 
		LEFT JOIN (SELECT invoice_no,SUM(amount) AS amount FROM payment GROUP BY invoice_no) AS a ON sales.invoice_no=a.invoice_no
		LEFT JOIN (SELECT invoice_no,SUM(quantity*amount) AS otamount FROM other_income GROUP BY invoice_no) AS b ON sales.invoice_no=b.invoice_no
		WHERE id_no=:id_no";
		$stmt = $this->conn->prepare( $query );
		$stmt->bindParam(":id_no", $this->id_no);
		if($stmt->execute()){
			$row = $stmt->fetch(PDO::FETCH_ASSOC);
			return $row['bal'];
		}
		return 0;
	}

	function updateFromCustomer(){
		$query = "UPDATE  " . $this->table_name . " SET name=:name, installation_address=:address WHERE customer_id=:customer_id and city=:city";
		
		$stmt = $this->conn->prepare($query);
		
		$stmt->bindParam(":customer_id", $this->customer_id);	
		$stmt->bindParam(":city", $this->city);	
		$stmt->bindParam(":name", $this->name);
		$stmt->bindParam(":address", $this->address);
		
		if($stmt->execute()){
			$this->id = $this->conn->lastInsertId();
			return true;
		}
		return false;
	}

	function updateStartDatebyID(){
		$query = "UPDATE  " . $this->table_name . " SET start_date=:start_date WHERE id=:id";
		
		$stmt = $this->conn->prepare($query);
		
		$stmt->bindParam(":id", $this->id);	
		$stmt->bindParam(":start_date", $this->start_date);
		
		if($stmt->execute()){
			$this->id = $this->conn->lastInsertId();
			return true;
		}
		return false;
	}

	function updateStartDate(){
		$query = "UPDATE  " . $this->table_name . " SET start_date=:start_date WHERE id_no=:id_no and invoice_no=:invoice_no";
		
		$stmt = $this->conn->prepare($query);
		
		$stmt->bindParam(":id_no", $this->id_no);	
		$stmt->bindParam(":start_date", $this->start_date);
		$stmt->bindParam(":invoice_no", $this->invoice_no);
		
		if($stmt->execute()){
			$this->id = $this->conn->lastInsertId();
			return true;
		}
		return false;
	}

	function updateStatusPackageChange(){
		$query = "UPDATE  " . $this->table_name . " SET package_status=:status WHERE id_no=:id_no and invoice_no=:invoice_no";
		
		$stmt = $this->conn->prepare($query);
		
		$stmt->bindParam(":id_no", $this->id_no);	
		$stmt->bindParam(":status", $this->status);
		$stmt->bindParam(":invoice_no", $this->invoice_no);
		
		if($stmt->execute()){
			$this->id = $this->conn->lastInsertId();
			return true;
		}
		return false;
	}

	function search(){
		$condition = "";
		if($this->df){
			$condition .= " and `sales_date`>=:df ";
		}

		if($this->dt){
			$condition .= " and `sales_date`<=:dt ";
		}

		if($this->invoice_no){
			// $condition .= " and sales.invoice_no=:invoice_no ";
			$condition .= " and (sales.invoice_no LIKE  :invoice_no '%' or sales.invoice_no LIKE '%' :invoice_no '%' or sales.invoice_no Like '%' :invoice_no ) ";
		}

		if($this->id_no){
			$condition .= " and (id_no LIKE  :id_no '%' or id_no LIKE '%' :id_no '%' or id_no Like '%' :id_no ) ";
		}
		if($this->name){
			$condition .= " and (name LIKE  :name '%' or name LIKE '%' :name '%' or name Like '%' :name ) ";
		}
		if($this->mbps){
			$condition .= " and mbps=:mbps ";
		}
		if($this->months){
			$condition .= " and months=:months ";
		}
		if($this->S_R){
			$condition .= " and S_R=:S_R ";
		}
		if($this->bal){
			if($this->bal=="y"){
				$condition .= " and (price*months + fee + (price*months*tax_percent/100) -refund_amount - ifnull(tp,0) - normal_discount - member_discount - promotion_discount + price_change_charges)>0 ";
			}else if($this->bal=="n"){
				$condition .= " and (price*months + fee + (price*months*tax_percent/100) -refund_amount - ifnull(tp,0) - normal_discount - member_discount - promotion_discount + price_change_charges)=0 ";
			}
		}
		// if($condition!=""){
		// 	$condition = " WHERE " . $condition;
		// }

		// $query = "SELECT * FROM sales " . $condition . " and city=:city order by id_no";
		$query = "SELECT sales.*,ifnull(tp,0) as tp FROM sales
		LEFT JOIN (SELECT invoice_no,SUM(amount) AS tp FROM payment GROUP BY invoice_no) AS a ON sales.invoice_no=a.invoice_no
		where sales.city=:city " . $condition . "
		GROUP BY sales.invoice_no order by sales.invoice_no";
		$stmt = $this->conn->prepare($query);
		if($this->df) $stmt->bindParam(":df", $this->df);
		if($this->dt) $stmt->bindParam(":dt", $this->dt);
		if($this->invoice_no) $stmt->bindParam(":invoice_no", $this->invoice_no);
		if($this->id_no) $stmt->bindParam(":id_no", $this->id_no);
		if($this->name) $stmt->bindParam(":name", $this->name);
		if($this->mbps) $stmt->bindParam(":mbps", $this->mbps);
		if($this->months) $stmt->bindParam(":months", $this->months);
		$stmt->bindParam(":city", $this->city);
		if($this->S_R) $stmt->bindParam(":S_R", $this->S_R);

		$stmt->execute();
		return $stmt;
	}

	function searchPayment(){
		$condition = "";
		if($this->df){
			$condition .= " and payment.date>=:df ";
		}

		if($this->dt){
			$condition .= " and payment.date<=:dt ";
		}

		if($this->id_no){
			$condition .= " and (id_no LIKE  :id_no '%' or id_no LIKE '%' :id_no '%' or id_no Like '%' :id_no ) ";
		}

		if($this->bal){
			if($this->bal=="y"){
				$condition .= " and (price*months + fee + (price*months*tax_percent/100) -refund_amount - ifnull(amount,0) - normal_discount - member_discount - promotion_discount + price_change_charges)>0 ";
			}else if($this->bal=="n"){
				$condition .= " and (price*months + fee + (price*months*tax_percent/100) -refund_amount - ifnull(amount,0) - normal_discount - member_discount - promotion_discount + price_change_charges)=0 ";
			}
		}

		$query = "SELECT date,payment.invoice_no,id_no,sales.name,phone,address,package,amount,sales.city,(price*months + fee + (price*months*tax_percent/100) -refund_amount - normal_discount - member_discount - promotion_discount + price_change_charges) as total FROM payment LEFT JOIN sales ON payment.invoice_no=sales.invoice_no
		LEFT JOIN customer ON sales.customer_id=customer.id where sales.city=:city " . $condition . " order by date,payment.invoice_no";
		$stmt = $this->conn->prepare($query);
		if($this->df) $stmt->bindParam(":df", $this->df);
		if($this->dt) $stmt->bindParam(":dt", $this->dt);
		if($this->id_no) $stmt->bindParam(":id_no", $this->id_no);
		$stmt->bindParam(":city", $this->city);

		$stmt->execute();
		return $stmt;
	}

	function searchDateInterval(){
		$condition = "";
		if($this->df){
			$condition .= " and sales_date>=:df ";
		}
		if($this->dt){
			$condition .= " and sales_date<=:dt ";
		}
		if($this->start_df){
			$condition .= " and start_date>=:start_df ";
		}
		if($this->start_dt){
			$condition .= " and start_date<=:start_dt ";
		}
		if($this->assign=="Assigned"){
			$condition .= " and start_date<>'' ";
		}
		if($this->assign=="Not Assigned"){
			$condition .= " and start_date='' ";
		}
		if($this->S_R){
			$condition .= " and S_R=:S_R ";
		}
		if($this->id_no){
			$condition .= " and (id_no LIKE  :id_no '%' or id_no LIKE '%' :id_no '%' or id_no Like '%' :id_no ) ";
		}
		$query = "SELECT sales.*,tp,DATE_ADD(start_date, INTERVAL ifnull(tdays,0)+sales.days+(sales.months*30)-1 DAY) as end_date, tdays-days as extra_days FROM sales
		LEFT JOIN (SELECT invoice_no,SUM(amount) AS tp FROM payment GROUP BY invoice_no) AS a ON sales.invoice_no=a.invoice_no
		LEFT JOIN (SELECT invoice_no,SUM(days) AS tdays FROM sales_detail GROUP BY invoice_no) AS b ON sales.invoice_no=b.invoice_no
		where sales.city=:city " . $condition . "
		GROUP BY sales.invoice_no order by sales_date";
		$stmt = $this->conn->prepare($query);

		if($this->df) $stmt->bindParam(":df", $this->df);
		if($this->dt) $stmt->bindParam(":dt", $this->dt);
		if($this->start_df) $stmt->bindParam(":start_df", $this->start_df);
		if($this->start_dt) $stmt->bindParam(":start_dt", $this->start_dt);
		if($this->id_no) $stmt->bindParam(":id_no", $this->id_no);
		$stmt->bindParam(":city", $this->city);
		if($this->S_R) $stmt->bindParam(":S_R", $this->S_R);

		$stmt->execute();
		return $stmt;
	}

function getExpireList($ded){

		$condition = "";

		
		if($this->mbps){
			if($condition!=""){$condition .= " AND ";}
			$condition .= " sales.mbps =:mbps ";
		}
		if($this->months){
			if($condition!=""){$condition .= " AND ";}
			$condition .= " sales.months =:months ";
		}

		if($this->id_no){
			if($condition!=""){$condition .= " AND ";}
			$condition .= "  (sales.id_no LIKE  :id_no '%' or sales.id_no LIKE '%' :id_no '%' or sales.id_no Like '%' :id_no ) ";
		}
		if($this->remark){
			if($this->remark=="Yes"){
				if($condition!=""){$condition .= " AND ";}
				$condition .= " remark <> '' and remark<>'စက်ဖြုတ်ထားသည်။' ";
			}else if($this->remark=="No"){
				if($condition!=""){$condition .= " AND ";}
				$condition .= "  remark is null ";
			}else if($this->remark=="စက်ဖြုတ်ထားသည်။"){
				if($condition!=""){$condition .= " AND ";}
				$condition .= " remark='စက်ဖြုတ်ထားသည်။' ";
			}
		}
		// else{
		// 	$condition .= " remark<>'စက်ဖြုတ်ထားသည်။' ";
		// }
		
		if($this->exp_type=="E"){
			if($condition!=""){$condition .= " AND ";}
			$condition .= " DATE_ADD(start_date, INTERVAL ifnull(tdays,0)+sales.days+(sales.months*30)-1 DAY)< '$ded'";
		} 
		if($this->exp_type=="T"){
			if($condition!=""){$condition .= " AND ";}
			$condition .= " DATE_ADD(start_date, INTERVAL ifnull(tdays,0)+sales.days+(sales.months*30)-1 DAY)= '$ded' ";
		}
		if($this->exp_type=="O"){
			if($condition!=""){$condition .= " AND ";}
			$condition .= " DATE_ADD(start_date, INTERVAL ifnull(tdays,0)+sales.days+(sales.months*30)-1 DAY)> '$ded' ";
		}
		if($this->exp_type=="N"){
			if($condition!=""){$condition .= " AND ";}
			$condition .= " (DATE_ADD(start_date, INTERVAL ifnull(tdays,0)+sales.days+(sales.months*30)-1 DAY) is null or DATE_ADD(start_date, INTERVAL ifnull(tdays,0)+sales.days+(sales.months*30)-1 DAY)='') ";
		}


		if($condition!=""){
			$condition = " WHERE " . $condition;
		}

		$query = "SELECT sales.*,address,remark,rmd,phone,DATE_ADD(start_date, INTERVAL ifnull(tdays,0)+sales.days+(sales.months*30)-1 DAY) AS med FROM (SELECT MAX(id) AS mxid FROM sales where city=:city GROUP BY id_no)
		AS a LEFT JOIN sales ON a.mxid=sales.id
		RIGHT join (select id_no from ont where status<>2) as ont on sales.id_no=ont.id_no
		LEFT JOIN customer ON sales.customer_id=customer.id
		LEFT JOIN (SELECT invoice_no,SUM(days) AS tdays FROM sales_detail GROUP BY invoice_no) AS b ON sales.invoice_no=b.invoice_no
		left join (SELECT invoice_no,remark,date as rmd from expire_remark left JOIN (SELECT MAX(id) AS mxxid FROM expire_remark GROUP BY invoice_no) AS c
		ON expire_remark.id=c.mxxid WHERE mxxid IS NOT null  ) as d on sales.invoice_no=d.invoice_no ".$condition."  order by med";
		$stmt = $this->conn->prepare($query);

		$stmt->bindParam(":city", $this->city);
		if($this->id_no) $stmt->bindParam(":id_no", $this->id_no);
		if($this->mbps) $stmt->bindParam(":mbps", $this->mbps);
		if($this->months) $stmt->bindParam(":months", $this->months);

		$stmt->execute();
		return $stmt;
	}

	function getExpireListByDate(){

		$condition = "";

		if($this->mbps){
			if($condition!=""){$condition .= " AND ";}
			$condition .= " sales.mbps =:mbps ";
		}
		if($this->months){
			if($condition!=""){$condition .= " AND ";}
			$condition .= " sales.months =:months ";
		}
		if($this->id_no){
			if($condition!=""){$condition .= " AND ";}
			$condition .= "  (sales.id_no LIKE  :id_no '%' or sales.id_no LIKE '%' :id_no '%' or sales.id_no Like '%' :id_no ) ";
		}

			if($this->remark=="Yes"){
				if($condition!=""){$condition .= " AND ";}
				$condition .= " remark <> '' and remark<>'စက်ဖြုတ်ထားသည်။' ";
			}else if($this->remark=="No"){
				if($condition!=""){$condition .= " AND ";}
				$condition .= "  remark is null ";
			}else if($this->remark=="စက်ဖြုတ်ထားသည်။"){
				if($condition!=""){$condition .= " AND ";}
				$condition .= " remark='စက်ဖြုတ်ထားသည်။' ";
			}

		if($this->df){
			if($condition!=""){$condition .= " AND ";}
			$condition .= "  DATE_ADD(start_date, INTERVAL ifnull(tdays,0)+sales.days+(sales.months*30)-1 DAY)>=:df ";
		}
		if($this->dt){
			if($condition!=""){$condition .= " AND ";}
			$condition .= "  DATE_ADD(start_date, INTERVAL ifnull(tdays,0)+sales.days+(sales.months*30)-1 DAY)<=:dt ";
		}

		if($condition!=""){
			$condition = " WHERE " . $condition;
		}

		$query = "SELECT sales.*,address,remark,rmd,phone,DATE_ADD(start_date, INTERVAL ifnull(tdays,0)+sales.days+(sales.months*30)-1 DAY) AS med FROM (SELECT MAX(id) AS mxid FROM sales where city=:city GROUP BY id_no)
		AS a LEFT JOIN sales ON a.mxid=sales.id
		LEFT JOIN customer ON sales.customer_id=customer.id
		RIGHT join (select id_no from ont where status<>2) as ont on sales.id_no=ont.id_no
		LEFT JOIN (SELECT invoice_no,SUM(days) AS tdays FROM sales_detail GROUP BY invoice_no) AS b ON sales.invoice_no=b.invoice_no
		left join (SELECT invoice_no,remark,date as rmd from expire_remark left JOIN (SELECT MAX(id) AS mxxid FROM expire_remark GROUP BY invoice_no) AS c
		ON expire_remark.id=c.mxxid WHERE mxxid IS NOT null  ) as d on sales.invoice_no=d.invoice_no ".$condition."  order by med";
		$stmt = $this->conn->prepare($query);

		if($this->mbps) $stmt->bindParam(":mbps", $this->mbps);
		if($this->months) $stmt->bindParam(":months", $this->months);
		if($this->id_no) $stmt->bindParam(":id_no", $this->id_no);
		if($this->city) $stmt->bindParam(":city", $this->city);
		if($this->df) $stmt->bindParam(":df", $this->df);
		if($this->dt) $stmt->bindParam(":dt", $this->dt);
		if($this->package) $stmt->bindParam(":package", $this->package);

		$stmt->execute();
		return $stmt;
	}

	function getTotalCustomers($c){
		$query = "select COUNT(*) as total_customer FROM (SELECT * from sales WHERE city='$c' GROUP BY id_no) AS a";
		$stmt = $this->conn->prepare( $query );
		if($stmt->execute()){
			$row = $stmt->fetch(PDO::FETCH_ASSOC);
			return $row['total_customer'];
		}
		return false;
	}

	function getMbpsList(){

		$query = "SELECT city,mbps,COUNT(id) AS c_count FROM (SELECT * FROM sales where city=:city GROUP BY id_no order BY id_no DESC) AS a
		GROUP BY mbps ORDER BY mbps";
		$stmt = $this->conn->prepare($query);

		if($this->city) $stmt->bindParam(":city", $this->city);

		$stmt->execute();
		return $stmt;
	}

	function getTotalMbpsList(){

		$query = "SELECT city,COUNT(*) as total_customer,SUM(mbps) AS total_mbps FROM(
			select city,id_no,invoice_no,package,mbps,days from sales left JOIN (SELECT MAX(id) AS mxid FROM sales GROUP BY id_no) AS a ON 
					sales.id=a.mxid WHERE mxid IS NOT NULL) AS c GROUP BY city";
		$stmt = $this->conn->prepare($query);

		$stmt->execute();
		return $stmt;
	}

	function getWeeklyNewCustomer(){
		$query = "SELECT sales_date,count(c) AS customer_count FROM (SELECT sales_date,COUNT(*) AS c FROM sales WHERE DATE(sales_date) > DATE_SUB(DATE(NOW()), INTERVAL 7 DAY)
		AND S_R='Sales' GROUP BY customer_id ORDER BY sales_date) AS a GROUP BY sales_date";
		$stmt = $this->conn->prepare( $query );
		$stmt->execute();
		return $stmt;
	}

	function getWeeklyIncome(){
		$query = "SELECT sales_date,SUM(tp) AS amount from sales 
		left JOIN (SELECT MAX(id) AS mxid FROM sales GROUP BY id_no) AS a ON sales.id=a.mxid
		LEFT JOIN (SELECT invoice_no,SUM(amount) AS tp FROM payment GROUP BY invoice_no) AS b ON sales.invoice_no=b.invoice_no
		WHERE mxid IS NOT NULL and DATE(sales_date) > DATE_SUB(DATE(NOW()), INTERVAL 7 DAY) GROUP BY sales_date";
		$stmt = $this->conn->prepare( $query );
		$stmt->execute();
		return $stmt;
	}

	function getList(){

		$query = "SELECT sales.*,(price +fee + (price*tax_percent/100) - normal_discount - member_discount - promotion_discount - refund_amount + price_change_charges) as total_amount,DATE_ADD(start_date, INTERVAL tdays DAY) AS end_date FROM sales
		LEFT JOIN (SELECT invoice_no,SUM(days) AS tdays FROM sales_detail GROUP BY invoice_no) AS c ON sales.invoice_no=c.invoice_no
		where sales.customer_id=:id ORDER BY sales.invoice_no ";
		$stmt = $this->conn->prepare($query);
		$this->id_no=htmlspecialchars(strip_tags($this->id_no));
		// $this->city=htmlspecialchars(strip_tags($this->city));

		$stmt->bindParam(":id", $this->id);
		// $stmt->bindParam(":city", $this->city);

		$stmt->execute();
		return $stmt;
	}

	function getOneRow(){
		$query = "SELECT sales.*, DATE_ADD(sales.start_date, INTERVAL 30*months+sales.days+ifnull(tdays,0) DAY) AS end_dates, payment,phone, d.address AS address , invoice_remark, e.location AS caddress, profile_picture from sales 
		LEFT JOIN (SELECT invoice_no,SUM(amount) AS payment FROM payment GROUP BY invoice_no) AS b ON sales.invoice_no=b.invoice_no
		LEFT JOIN (SELECT invoice_no,SUM(days) AS tdays FROM sales_detail GROUP BY invoice_no) AS c ON sales.invoice_no=c.invoice_no
		LEFT JOIN (SELECT id, phone, address FROM customer) AS d ON sales.customer_id=d.id
		LEFT JOIN (SELECT name, invoice_remark, location, profile_picture FROM city) AS e ON sales.city=e.name
		where sales.id=:id ORDER BY id
		
		";
		$stmt = $this->conn->prepare( $query );
		$stmt->bindParam(":id", $this->id);
		$stmt->execute();
		if($stmt->rowCount()>0){
			$row = $stmt->fetch(PDO::FETCH_ASSOC);
			$this->id = $row['id'];
			$this->sales_date = $row['sales_date'];
			// $this->sales_no = $row['invo'];
			$this->invoice_no = $row['invoice_no'];
			$this->id_no = $row['id_no'];
			$this->customer_id = $row['customer_id'];	
			$this->name = $row['name'];	
			$this->city = $row['city'];	
			// $this->package = $row['package'];
			$this->mbps = $row['mbps'];
			$this->months = $row['months'];
			$this->days = $row['days'];
			$this->price = $row['price'];
			$this->fee = $row['fee'];
			$this->tax = $row['tax_percent'];
			$this->price_change_charges = $row['price_change_charges'];
			$this->payment = $row['payment'];
			$this->normal_discount = $row['normal_discount'];
			$this->member_discount = $row['member_discount'];
			$this->promotion_discount = $row['promotion_discount'];
			$this->refund = $row['refund_amount'];
			$this->start_date = $row['start_date'];
			// $this->extra_days = $row['extra_days'];
			$this->end_date = $row['end_dates'];
			$this->phone = $row['phone'];
			$this->address = $row['address'];
			$this->invoice_remark = $row['invoice_remark'];
			$this->caddress = $row['caddress'];
			$this->profile_picture = $row['profile_picture'];
		}
	}

	function getExpireSMSCount($c,$d){
		$query = "SELECT COUNT(*) AS total_expired_count FROM (SELECT MAX(id) AS mxid FROM sales " . (($c=="")?"":" where city='$c'") . " GROUP BY id_no) AS a
		LEFT JOIN sales ON a.mxid=sales.id
		LEFT JOIN (SELECT invoice_no,SUM(days) AS tdays FROM sales_detail GROUP BY invoice_no) AS c ON sales.invoice_no=c.invoice_no
		LEFT JOIN (SELECT * FROM send_msg WHERE sms_type='expired') AS sm ON a.mxid=sm.sales_id
		WHERE mxid IS NOT NULL AND sm.id is null AND DATE_ADD(sales.start_date, INTERVAL tdays DAY)='$d' ";

		$stmt = $this->conn->prepare( $query );
		if($stmt->execute()){
			$row = $stmt->fetch(PDO::FETCH_ASSOC);
			return $row['total_expired_count'];
		}
		return false;
	}

	function getReminderSMSCount($c,$d){
		$query = "SELECT COUNT(*) AS total_reminder_count FROM (SELECT MAX(id) AS mxid FROM sales " . (($c=="")?"":" where city='$c'") . " GROUP BY id_no) AS a
		LEFT JOIN sales ON a.mxid=sales.id
		LEFT JOIN (SELECT invoice_no,SUM(days) AS tdays FROM sales_detail GROUP BY invoice_no) AS c ON sales.invoice_no=c.invoice_no
		LEFT JOIN (SELECT * FROM send_msg WHERE sms_type='reminder') AS sm ON a.mxid=sm.sales_id
		WHERE mxid IS NOT NULL AND sm.id is null AND DATE_ADD(sales.start_date, INTERVAL 30*months+sales.days+ifnull(tdays,0) DAY)='$d' ";
		$stmt = $this->conn->prepare( $query );
		if($stmt->execute()){
			$row = $stmt->fetch(PDO::FETCH_ASSOC);
			return $row['total_reminder_count'];
		}
		return false;
	}

	function getExpiredSMSList($c){

		$query = "SELECT sales.*,DATE_ADD(sales.start_date, INTERVAL 30*months+sales.days+ifnull(tdays,0) DAY) as end_date,customer.phone,ph_no FROM (SELECT MAX(id) AS mxid FROM sales " . (($c=="")?"":" where city='$c'") . " GROUP BY id_no) AS a
		LEFT JOIN sales ON a.mxid=sales.id
		LEFT JOIN customer ON sales.customer_id=customer.id
		LEFT JOIN city ON sales.city=city.name
		LEFT JOIN (SELECT invoice_no,SUM(days) AS tdays FROM sales_detail GROUP BY invoice_no) AS c ON sales.invoice_no=c.invoice_no
		LEFT JOIN (SELECT * FROM send_msg WHERE sms_type='expired') AS sm ON a.mxid=sm.sales_id
		WHERE DATE_ADD(sales.start_date, INTERVAL tdays DAY)=:d AND sm.id is null ";

		$stmt = $this->conn->prepare($query);
		$stmt->bindParam(":d", $this->d);

		$stmt->execute();
		return $stmt;
	}

	function getReminderSMSList($c){

		$query = "SELECT sales.*,DATE_ADD(sales.start_date, INTERVAL 30*months+sales.days+ifnull(tdays,0) DAY) as end_date,customer.phone,ph_no FROM (SELECT MAX(id) AS mxid FROM sales " . (($c=="")?"":" where city='$c'") . " GROUP BY id_no) AS a
		LEFT JOIN sales ON a.mxid=sales.id
		LEFT JOIN customer ON sales.customer_id=customer.id
		LEFT JOIN city ON sales.city=city.name
		LEFT JOIN (SELECT invoice_no,SUM(days) AS tdays FROM sales_detail GROUP BY invoice_no) AS c ON sales.invoice_no=c.invoice_no
		LEFT JOIN (SELECT * FROM send_msg WHERE sms_type='reminder') AS sm ON a.mxid=sm.sales_id
		WHERE DATE_ADD(sales.start_date, INTERVAL tdays DAY)=:d AND sm.id is null ";
		$stmt = $this->conn->prepare($query);
		$stmt->bindParam(":d", $this->d);

		$stmt->execute();
		return $stmt;
	}	

	function getToDisableList($c){

		$query = "SELECT a.* FROM (SELECT sales.invoice_no,sales.id_no,ont_snno,DATE_ADD(start_date, INTERVAL ifnull(tdays,0)+sales.days+(sales.months*30)-1 DAY) as end_date,smart_key,city.code FROM (SELECT MAX(id) AS mxid FROM sales  GROUP BY id_no) AS a
		LEFT JOIN sales ON a.mxid=sales.id
		LEFT JOIN city ON sales.city=city.name
		LEFT JOIN ont ON sales.id_no=ont.id_no
		LEFT JOIN (SELECT invoice_no,SUM(days) AS tdays FROM sales_detail GROUP BY invoice_no) AS c ON sales.invoice_no=c.invoice_no
		WHERE DATE_ADD(start_date, INTERVAL ifnull(tdays,0)+sales.days+(sales.months*30)-1 DAY)=:d ) AS a 
		LEFT JOIN (SELECT * FROM ont_status WHERE entry_by='cron' and substring(entry_date_time,1,10)=:td ORDER BY id DESC ) AS b ON a.id_no=b.id_no 
		WHERE id IS null ";
		// DATE_ADD(sales.start_date, INTERVAL tdays DAY)=:d
		$stmt = $this->conn->prepare($query);
		$stmt->bindParam(":d", $this->d);
        $stmt->bindParam(":td", $this->td);
		$stmt->execute();
		return $stmt;
	}

	function getSMSByID($id){

		$query = "SELECT sales.*, DATE_ADD(sales.start_date, INTERVAL 30*months+sales.days+ifnull(tdays,0) DAY) AS end_dates, phone,ph_no,payment,ont_snno
		FROM sales
		LEFT JOIN (SELECT invoice_no,SUM(days) AS tdays FROM sales_detail GROUP BY invoice_no) AS c ON sales.invoice_no=c.invoice_no
		LEFT JOIN customer ON sales.customer_id=customer.id
		LEFT JOIN city ON sales.city=city.name
		LEFT JOIN (SELECT invoice_no,SUM(amount) AS payment FROM payment GROUP BY invoice_no) AS b ON sales.invoice_no=b.invoice_no
		LEFT JOIN (SELECT id_no AS idn,ont_snno FROM ont GROUP BY id_no) AS idnn ON sales.id_no=idnn.idn
		WHERE sales.id='$id'";
		$stmt = $this->conn->prepare($query);

		$stmt->execute();
		return $stmt;
	}

	function getSMSByInvoiceNo($id){

		$query = "SELECT sales.*, DATE_ADD(sales.start_date, INTERVAL 30*months+sales.days+ifnull(tdays,0) DAY) AS end_dates, phone,ph_no,payment,ont_snno
		FROM sales
		LEFT JOIN (SELECT invoice_no,SUM(days) AS tdays FROM sales_detail GROUP BY invoice_no) AS c ON sales.invoice_no=c.invoice_no
		LEFT JOIN customer ON sales.customer_id=customer.id
		LEFT JOIN city ON sales.city=city.name
		LEFT JOIN (SELECT invoice_no,SUM(amount) AS payment FROM payment GROUP BY invoice_no) AS b ON sales.invoice_no=b.invoice_no
		LEFT JOIN (SELECT id_no AS idn,ont_snno FROM ont GROUP BY id_no) AS idnn ON sales.id_no=idnn.idn
		WHERE sales.invoice_no='$id'";
		$stmt = $this->conn->prepare($query);

		$stmt->execute();
		return $stmt;
	}	

	function updateSMS($sms){
		$query = "UPDATE `" . $this->table_name . "` SET $sms=1 WHERE id=:id ";

		$stmt = $this->conn->prepare($query);

		$stmt->bindParam(":id", $this->id);
		
		if($stmt->execute()){
			return true;
		}	 
		return false;
	}

	function SearchOpeningBalanceOffice(){
		$condition = "";
		if($this->accounting_view==0){
			$condition .= " and accounting_view=0 ";
		}

		$query = "SELECT IFNULL(p1,0) +IFNULL(i,0) - IFNULL(e,0) - IFNULL(te,0) + IFNULL(ti,0) + IFNULL(its,0) AS balance FROM 
		(SELECT SUM(quantity*amount) AS e FROM expense WHERE date<:df AND city=:city " . $condition . " ) AS b,
		(SELECT SUM(quantity*amount) AS i FROM income WHERE DATE<:df AND city=:city " . $condition . ") AS c,
		(SELECT SUM(quantity*amount) AS te FROM `transfer` WHERE DATE<:df AND transfer_from=:city ) AS e,
		(SELECT SUM(amount) AS p1 FROM payment WHERE date<:df AND city=:city) AS d,
		(SELECT SUM(payment) AS its FROM item_sales WHERE sales_date<:df and store=:city) AS g,
		(SELECT SUM(quantity*amount) AS ti FROM `transfer` WHERE DATE<:df AND transfer_to=:city ) AS f";
		$stmt = $this->conn->prepare( $query );
		$stmt->bindParam(":df", $this->df);
		$stmt->bindParam(":city", $this->city);
		$stmt->execute();
		if($stmt->rowCount()>0){
			$row = $stmt->fetch(PDO::FETCH_ASSOC);
			return $row['balance'];
		}
		return 0;	
	}

	function SearchCashBookOffice(){

		$condition1 = "";

		if($this->accounting_view==0){
			$condition1 .= " and accounting_view=0";
		}

		$query = "SELECT * FROM (
		SELECT city,date,'Expense' as expense_reason,expense_reason AS head,particular,0 AS in_amt,SUM(quantity*amount) AS ex_amt FROM expense 
		WHERE city=:city AND `date`>=:df AND `date`<=:dt " . $condition1 . "
		GROUP BY date,head,particular
		UNION
		SELECT city,date,'Income' as expense_reason,income_reason AS head,particular,SUM(quantity*amount) AS in_amt,0 AS ex_amt FROM income 
		WHERE city=:city AND `date`>=:df AND `date`<=:dt " . $condition1 . "
		GROUP BY date,head,particular
		UNION
		SELECT city,date,'Cash Transfer In' as expense_reason,transfer_reason AS head,particular,SUM(quantity*amount) AS in_amt,0 AS ex_amt FROM `transfer` 
		WHERE transfer_to=:city AND `date`>=:df AND `date`<=:dt
		GROUP BY date,head,particular
		UNION
		SELECT city,date,'Cash Transfer Out' as expense_reason,transfer_reason AS head,particular,0 AS in_amt,SUM(quantity*amount) AS ex_amt FROM `transfer` 
		WHERE transfer_from=:city AND `date`>=:df AND `date`<=:dt
		GROUP BY date,head,particular
		UNION
		SELECT city,date,'Payment' as expense_reason,'Payment' AS head,'Payment' as particular,SUM(amount) AS in_amt,0 AS ex_amt FROM payment 
		WHERE city=:city AND `date`>=:df AND `date`<=:dt
		GROUP BY date,head,particular
		UNION
		SELECT store as city,sales_date as date,'Item Sales Income' as expense_reason,'Item Sales' as head,CONCAT('Voucher No.- ',voucher_no) as particular,SUM(payment) AS in_amt,0 AS ex_amt FROM item_sales 
		WHERE store=:city AND `sales_date`>=:df AND `sales_date`<=:dt
		GROUP BY date,head,particular
		) AS a  ORDER BY date,particular";
		$stmt = $this->conn->prepare( $query );
		$stmt->bindParam(":city", $this->city);
		$stmt->bindParam(":df", $this->df);
		$stmt->bindParam(":dt", $this->dt);
		$stmt->execute();
		return $stmt;
	}

	function SearchCashBookByReasonOffice(){
		$condition = "";
		if($this->accounting_view==0){
			$condition .= " and accounting_view=0 ";
		}

		$query = "SELECT * from (SELECT 'Income' AS R,'Payment' as S_R,sum(amount) AS p FROM payment where `date`>=:df AND `date`<=:dt and city=:city 
		UNION
		SELECT 'Income' AS R,income_reason as S_R,sum(quantity*amount) AS p FROM income where `date`>=:df AND `date`<=:dt and city=:city " . $condition . " GROUP BY income_reason
		UNION
		SELECT 'Cash Transfer In' AS R,transfer_reason as S_R,sum(quantity*amount) AS p FROM `transfer` where `date`>=:df AND `date`<=:dt and transfer_to=:city GROUP BY transfer_reason
		UNION
		SELECT 'Cash Transfer Out' AS R,transfer_reason as S_R,sum(quantity*amount) AS p FROM `transfer` where `date`>=:df AND `date`<=:dt and transfer_from=:city GROUP BY transfer_reason
		UNION
		SELECT 'Expense' AS R,expense_reason as S_R,sum(quantity*amount) AS p FROM expense  where `date`>=:df AND `date`<=:dt and city=:city " . $condition . " GROUP BY expense_reason
		UNION
		SELECT 'Item Sales Income' AS R,'Item Sales' as S_R,sum(payment) AS itp FROM item_sales where `sales_date`>=:df AND `sales_date`<=:dt and store=:city GROUP BY S_R
		) as a order by R " ;
		$stmt = $this->conn->prepare( $query );
		$stmt->bindParam(":df", $this->df);
		$stmt->bindParam(":dt", $this->dt);
		$stmt->bindParam(":city", $this->city);

		$stmt->execute();
		return $stmt;
	}

	function SearchCashBookByTypeOffice(){
		$condition = "";
		if($this->accounting_view==0){
			$condition .= " and accounting_view=0 ";
		}

		$query = "SELECT R,sum(p) as p from (SELECT 'Income' AS R,'Payment' as S_R,sum(amount) AS p FROM payment where `date`>=:df AND `date`<=:dt and city=:city 
		UNION
		SELECT 'Income' AS R,income_reason as S_R,sum(quantity*amount) AS p FROM income where `date`>=:df AND `date`<=:dt and city=:city " . $condition . " GROUP BY income_reason
		UNION
		SELECT 'Cash Transfer In' AS R,transfer_reason as S_R,sum(quantity*amount) AS p FROM `transfer` where `date`>=:df AND `date`<=:dt and transfer_to=:city GROUP BY transfer_reason
		UNION
		SELECT 'Cash Transfer Out' AS R,transfer_reason as S_R,sum(quantity*amount) AS p FROM `transfer` where `date`>=:df AND `date`<=:dt and transfer_from=:city GROUP BY transfer_reason
		UNION
		SELECT 'Expense' AS R,expense_reason as S_R,sum(quantity*amount) AS p FROM expense  where `date`>=:df AND `date`<=:dt and city=:city " . $condition . " GROUP BY expense_reason
		UNION
		SELECT 'Item Sales Income' AS R,'Item Sales' as S_R,sum(payment) AS p FROM item_sales where `sales_date`>=:df AND `sales_date`<=:dt and store=:city GROUP BY S_R
		) as a group by R order by R";
		$stmt = $this->conn->prepare( $query );
		$stmt->bindParam(":df", $this->df);
		$stmt->bindParam(":dt", $this->dt);
		$stmt->bindParam(":city", $this->city);

		$stmt->execute();
		return $stmt;
	}

	function searchCashIn(){
		$condition = "";
		if($this->accounting_view==0){
			$condition .= " and accounting_view=0 ";
		}

		$query = "
		SELECT CONCAT('Sales',' - ',payment_type) as description,SUM(amount) AS p FROM payment left join sales on payment.invoice_no=sales.invoice_no WHERE date>=:df AND date<=:dt AND payment.city=:city AND S_R='Sales' group by payment_type
		UNION
		SELECT CONCAT('Renew',' - ',payment_type) as description,SUM(amount) AS p FROM payment left join sales on payment.invoice_no=sales.invoice_no WHERE date>=:df AND date<=:dt AND payment.city=:city AND S_R='Renew' group by payment_type
		UNION
		SELECT 'Income' as description,SUM(quantity*amount) AS p FROM income WHERE date>=:df AND date<=:dt AND city=:city " . $condition . "
		UNION
		SELECT 'Item Sales' as description,SUM(payment) AS p FROM item_sales WHERE sales_date>=:df AND sales_date<=:dt AND store=:city
		";
		$stmt = $this->conn->prepare($query);
		// $stmt->bindParam(":start_date", $this->start_date);
		$stmt->bindParam(":city", $this->city);
		$stmt->bindParam(":df", $this->df);
		$stmt->bindParam(":dt", $this->dt);
		$stmt->execute();
		return $stmt;
	}
	function searchCashOut(){
		$condition = "";
		if($this->accounting_view==0){
			$condition .= " and accounting_view=0 ";
		}
		$query = "SELECT a.* FROM (SELECT expense_reason AS description, SUM(quantity*amount) AS p
		FROM expense
		WHERE DATE>=:df AND date<=:dt AND city=:city " . $condition . "
		GROUP BY expense_reason) AS a";
		$stmt = $this->conn->prepare($query);
		// $stmt->bindParam(":start_date", $this->start_date);
		$stmt->bindParam(":city", $this->city);
		$stmt->bindParam(":df", $this->df);
		$stmt->bindParam(":dt", $this->dt);
		$stmt->execute();
		return $stmt;
	}

	function getRefund(){
		$query = "SELECT refund FROM (SELECT sales.*,sum((price/30)*DATEDIFF(DATE_ADD(start_date, INTERVAL ifnull(tdays,0)+sales.days+(sales.months*30)-1 DAY), :date)) AS refund FROM sales 
				LEFT JOIN (SELECT invoice_no, SUM(days) AS tdays FROM sales_detail where promotion_id=0 AND invoice_no=:invoice_no) AS b ON sales.invoice_no=b.invoice_no
				WHERE sales.invoice_no=:invoice_no) AS a";
		$stmt = $this->conn->prepare( $query );
		$stmt->bindParam(":invoice_no", $this->invoice_no);
		$stmt->bindParam(":date", $this->date);
		$stmt->execute();
		if($stmt->rowCount()>0){
			$row = $stmt->fetch(PDO::FETCH_ASSOC);
			$this->refund = $row['refund'];
		}
	}

		// function getRefund(){
	// 	$query = "SELECT refund FROM (SELECT sales.*,sum((price/30)*DATEDIFF(DATE_ADD(start_date, INTERVAL ifnull(tdays,0)+(sales.months*30)-1 DAY), :date)) AS refund FROM sales
	// 	LEFT JOIN (SELECT invoice_no, SUM(days) AS tdays FROM sales_detail where promotion_id=0 GROUP BY invoice_no) AS b ON sales.invoice_no=b.invoice_no
	// 	WHERE DATE_ADD(start_date, INTERVAL ifnull(tdays,0)+(sales.months*30)-1 DAY)>:date
	// 	GROUP BY customer_id HAVING id_no=:id_no AND customer_id=:customer_id) AS a";
	// 	$stmt = $this->conn->prepare( $query );
	// 	$stmt->bindParam(":id_no", $this->id_no);
	// 	$stmt->bindParam(":customer_id", $this->customer_id);
	// 	$stmt->bindParam(":date", $this->date);
	// 	$stmt->execute();
	// 	if($stmt->rowCount()>0){
	// 		$row = $stmt->fetch(PDO::FETCH_ASSOC);
	// 		$this->refund = $row['refund'];
	// 	}
	// }

	function updateNewToTempDevice(){

		$query = "UPDATE " . $this->table_name . " SET id_no=:id_no where invoice_no=:invoice_no";
		$stmt = $this->conn->prepare($query);


		$stmt->bindParam(":id_no", $this->id_no);
		$stmt->bindParam(":invoice_no", $this->invoice_no);

		if($stmt->execute()){
			// $this->id = $this->conn->lastInsertId();
			return true;
		}	 
		return false;
	}

		function getGenerateVoucherNo($prefix){
		$query = "SELECT voucher_no FROM `" . $this->table_name . "` WHERE voucher_no like ? ORDER BY voucher_no desc limit 0,1";
		$stmt = $this->conn->prepare($query);
		$keywords = htmlspecialchars(strip_tags($prefix));
		$keywords = "{$keywords}%";
		
		$stmt->bindParam(1, $keywords);
		$stmt->execute();
		if($stmt->rowCount()>0){
			$row = $stmt->fetch(PDO::FETCH_ASSOC);
			return $prefix . sprintf("%'05d", ((int)str_replace($prefix,"",$row['voucher_no']) + 1));
		}else{
			return $prefix . "00001";
		}
	}

	function searchMonthlyDetail(){

		$condition = "";

		$condition1 = "";
		if($this->accounting_view==0){
			$condition1 .= " and accounting_view=0 ";
		}

		if($this->r=="In"){
			if($this->st=="Sales"){
				$condition .= "SELECT date as d,id_no as ii,name as particular,SUM(amount) AS p FROM payment 
				LEFT JOIN sales ON payment.invoice_no=sales.invoice_no
				WHERE date>=:df AND date<=:dt AND payment.city=:city and S_R='Sales' and payment_type='".$this->st_p_type."' 
				GROUP BY DATE,payment.invoice_no order by date";
			}else if($this->st=="Renew"){
				$condition .= "SELECT date as d,id_no as ii,name as particular,SUM(amount) AS p FROM payment 
				LEFT JOIN sales ON payment.invoice_no=sales.invoice_no
				WHERE date>=:df AND date<=:dt AND payment.city=:city and S_R='Renew' and payment_type='".$this->st_p_type."' 
				GROUP BY DATE,payment.invoice_no order by date";
			}else if($this->st=="Income"){
				$condition .= "SELECT date as d,income_reason as ii,particular,SUM(quantity*amount) AS p FROM income WHERE date>=:df AND date<=:dt AND city=:city " . $condition1 . "
				GROUP BY DATE,particular order by date";
			}else if($this->st=="Item Sales"){
				$condition .= "SELECT item_sales_detail.sales_date as d,item_name as ii,name as particular,SUM(quantity*price) AS p FROM item_sales_detail 
				left join item_sales on item_sales_detail.voucher_no=item_sales.voucher_no
				WHERE item_sales_detail.sales_date>=:df AND item_sales_detail.sales_date<=:dt AND item_sales_detail.store=:city 
				GROUP BY item_sales_detail.sales_date,item_sales_detail.item_name order by item_sales_detail.sales_date";
			}
		}else if($this->r=="Out"){

			// if($this->t=="a"){
				$condition .= "SELECT date AS d,expense_by AS ii,particular as particular, SUM(quantity*amount) AS p FROM expense
				WHERE date>=:df AND DATE<=:dt AND city=:city " . $condition1 . " AND expense_reason='".$this->st."' 
				GROUP BY date,expense_reason,particular order by date";
			// }

			// $condition .= "SELECT a.*,ascd FROM (SELECT date AS d,expense_reason AS description,particular as ii, SUM(quantity*amount) AS p FROM expense
			// WHERE exp_type<>'Fixed' AND exp_type<>'Fixed-1' and date>=:df AND date<=:dt AND (exp_for LIKE '%' :city '%') AND expense_reason='".$this->st."'
			// GROUP BY date,expense_reason) AS a LEFT JOIN (SELECT * FROM expense_reason GROUP BY expense_reason) AS b ON
			// a.description=b.expense_reason ORDER BY d";
		}


		$query = $condition;

		$stmt = $this->conn->prepare($query);

		$stmt->bindParam(":city", $this->city);
		$stmt->bindParam(":df", $this->df);
		$stmt->bindParam(":dt", $this->dt);
		$stmt->execute();
		return $stmt;
	}

	function getSalesRenewShare(){
		$query = "SELECT substring(d,9,2) as sales_date,customer_count,renew_count from
		(select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) d from
		 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
		 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
		 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
		 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
		 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
		 LEFT JOIN (
			SELECT sales_date,count(c) AS customer_count FROM (SELECT sales_date,COUNT(*) AS c FROM sales WHERE substring(sales_date,1,7)=:month " .(($this->city)?"and city=:city":"") . " AND S_R='Sales' GROUP BY sales_date,invoice_no ORDER BY sales_date) AS a GROUP BY sales_date
		 ) AS jj ON v.d=jj.sales_date
		 LEFT JOIN (
			SELECT sales_date,count(c) AS renew_count FROM (SELECT sales_date,COUNT(*) AS c FROM sales WHERE substring(sales_date,1,7)=:month " .(($this->city)?"and city=:city":"") . "
			AND S_R='Renew' GROUP BY sales_date,invoice_no ORDER BY sales_date) AS a GROUP BY sales_date
		 ) AS jjr ON v.d=jjr.sales_date
		 where substring(d,1,7)=:month ORDER BY d";

		$stmt = $this->conn->prepare( $query );
		if($this->city) $stmt->bindParam(":city", $this->city);
		$stmt->bindParam(":month", $this->month);
		$stmt->execute();
		return $stmt;
	}

	function getSalesRenewYearlyShare(){
		$query = "SELECT d,scount,rcount FROM (SELECT CONCAT(:yy,SUBSTRING(d,9,2)) AS d from
		(select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) d from
		 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
		 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
		 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
		 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
		 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
		 WHERE d>='2022-03-01' AND d<='2022-03-12' ORDER BY d) AS v
		LEFT JOIN (
			SELECT substring(sales_date,1,7) as sales_date,count(c) AS scount FROM (SELECT sales_date,COUNT(*) AS c FROM sales WHERE substring(sales_date,1,4)=:y " .(($this->city)?"and city=:city":"") . "
			AND S_R='Sales' GROUP BY sales_date,invoice_no ORDER BY sales_date) AS a GROUP BY substring(sales_date,1,7)
		 ) AS jj ON v.d=jj.sales_date
		 LEFT JOIN (
			SELECT substring(sales_date,1,7) AS sales_date,count(c) AS rcount FROM (SELECT sales_date,COUNT(*) AS c FROM sales WHERE substring(sales_date,1,4)=:y " .(($this->city)?"and city=:city":"") . "
			AND S_R='Renew' GROUP BY sales_date,invoice_no ORDER BY sales_date) AS a GROUP BY substring(sales_date,1,7)
		 ) AS jjr ON v.d=jjr.sales_date
		 where substring(d,1,4)=:y ORDER BY d;
		";

		$stmt = $this->conn->prepare( $query );
		if($this->city) $stmt->bindParam(":city", $this->city);
		$stmt->bindParam(":y", $this->y);
		$stmt->bindParam(":yy", $this->yy);
		$stmt->execute();
		return $stmt;
	}

	function getInOutShare(){
		$query = "SELECT substring(sales_date,9,2) as sales_date,SUM(IFNULL(p,0)+IFNULL(it,0)) AS amount,examount FROM (SELECT * FROM (select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) sales_date from
		 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
		 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
		 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
		 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
		 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4
		 where substring(adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i),1,7)=:month ORDER BY sales_date) AS a 
			LEFT JOIN (SELECT date, SUM(amount) AS p
			FROM payment WHERE substring(date,1,7)=:month " .(($this->city)?" and city=:city":"") . "
			GROUP BY date) AS c ON a.sales_date=c.date
			LEFT JOIN (SELECT DATE AS dddd,SUM(quantity*amount) AS it FROM income WHERE SUBSTRING(DATE,1,7)=:month " .(($this->city)?" and city=:city":"") . "  GROUP BY DATE) AS d
				ON a.sales_date=d.dddd
					 LEFT JOIN (
			SELECT DATE AS ddd,SUM(quantity*amount) as examount FROM expense WHERE SUBSTRING(DATE,1,7)=:month " .(($this->city)?" and city=:city":"") . "
			GROUP BY date
		 ) AS jje ON a.sales_date=jje.ddd) AS f GROUP BY sales_date

		 ";
		$stmt = $this->conn->prepare( $query );
		if($this->city) $stmt->bindParam(":city", $this->city);
		$stmt->bindParam(":month", $this->month);
		$stmt->execute();
		return $stmt;
	}

	function getINOutProfitShare($ty){
		$query = "SELECT d,IFNULL(amount,0) AS amount,(IFNULL(examount,0)) AS examount FROM (SELECT CONCAT(:yy,SUBSTRING(d,9,2)) AS d from
		(select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) d from
		 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
		 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
		 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
		 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
		 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
		 WHERE d>='2022-03-01' AND d<='2022-03-12' ORDER BY d) AS s
		 LEFT join
		 (SELECT a.m,SUM(IFNULL(p,0)+IFNULL(it,0)) AS amount FROM (SELECT SUBSTRING(date,1,7) AS m, SUM(amount) AS p FROM payment WHERE substring(date,1,4)=:y  " .(($this->city)?" and city=:city":"") . " GROUP BY m
			) AS a 
			LEFT JOIN (SELECT SUBSTRING(date,1,7) AS m,SUM(quantity*amount) AS it FROM income WHERE SUBSTRING(DATE,1,4)=:y  " .(($this->city)?" and city=:city":"") . "  GROUP BY m) AS c
			ON a.m=c.m
			GROUP BY a.m) AS inc ON s.d=inc.m
			LEFT JOIN (SELECT SUBSTRING(date,1,7) AS m,SUM(quantity*amount) as examount FROM expense WHERE SUBSTRING(DATE,1,4)=:y  " .(($this->city)?" and city=:city":"") . "
		GROUP BY m) AS exps ON s.d=exps.m order by s.d
		

				
		";
		$stmt = $this->conn->prepare( $query );
		if($this->city) $stmt->bindParam(":city", $this->city);
		$stmt->bindParam(":y", $this->y);
		$stmt->bindParam(":yy", $this->yy);
		$stmt->execute();
		return $stmt;
	}

		function searchCashInS(){
		$condition = "";
		if($this->accounting_view==0){
			$condition .= " and accounting_view=0 ";
		}
		$query = "
		SELECT CONCAT('Sales',' - ',payment_type) as description,SUM(amount) AS p FROM payment left join sales on payment.invoice_no=sales.invoice_no WHERE substring(date,1,7)=:ym AND sales.city=:city AND S_R='Sales' group by payment_type
		UNION
		SELECT CONCAT('Renew',' - ',payment_type) as description,SUM(amount) AS p FROM payment left join sales on payment.invoice_no=sales.invoice_no WHERE substring(sales_date,1,7)=:ym AND sales.city=:city AND S_R='Renew' group by payment_type
		UNION
		SELECT 'Income' as description,SUM(quantity*amount) AS p FROM income WHERE substring(date,1,7)=:ym AND city=:city " . $condition . "
		UNION
		SELECT 'Item Sales' as description,SUM(payment) AS p FROM item_sales WHERE substring(sales_date,1,7)=:ym AND store=:city

		";
		$stmt = $this->conn->prepare($query);
		// $stmt->bindParam(":start_date", $this->start_date);
		$stmt->bindParam(":city", $this->city);
		$stmt->bindParam(":ym", $this->ym);
		$stmt->execute();
		return $stmt;
	}

	function searchCashOutS(){
		$condition = "";
		if($this->accounting_view==0){
			$condition .= " and accounting_view=0 ";
		}
		$query = "SELECT expense_reason AS description, SUM(quantity*amount) AS p
		FROM expense
		WHERE substring(date,1,7)=:ym AND city=:city " . $condition . "
		GROUP BY expense_reason
		";
		$stmt = $this->conn->prepare($query);
		// $stmt->bindParam(":start_date", $this->start_date);
		$stmt->bindParam(":city", $this->city);
		$stmt->bindParam(":ym", $this->ym);
		$stmt->execute();
		return $stmt;
	}

	function searchMonthlyDetailS(){

		$condition = "";

		$condition1 = "";
		if($this->accounting_view==0){
			$condition1 .= " and accounting_view=0 ";
		}

		if($this->r=="In"){
			if($this->st=="Sales"){
				$condition .= "SELECT date as d,id_no as ii,name as particular,SUM(amount) AS p FROM payment 
				LEFT JOIN sales ON payment.invoice_no=sales.invoice_no
				WHERE substring(date,1,7)=:ym AND payment.city=:city and S_R='Sales' and payment_type='".$this->st_p_type."' 
				GROUP BY DATE,payment.invoice_no order by date";
			}else if($this->st=="Renew"){
				$condition .= "SELECT date as d,id_no as ii,name as particular,SUM(amount) AS p FROM payment 
				LEFT JOIN sales ON payment.invoice_no=sales.invoice_no
				WHERE substring(date,1,7)=:ym AND payment.city=:city and S_R='Renew' and payment_type='".$this->st_p_type."' 
				GROUP BY DATE,payment.invoice_no order by date";
			}else if($this->st=="Income"){
				$condition .= "SELECT date as d,income_reason as ii,particular,SUM(quantity*amount) AS p FROM income WHERE substring(date,1,7)=:ym AND city=:city " . $condition1 . "
				GROUP BY DATE,particular order by date";
			}else if($this->st=="Item Sales"){
				$condition .= "SELECT sales_date as d,item_name as ii,name as particular,SUM(quantity*price) AS p FROM item_sales_detail WHERE substring(sales_date,1,7)=:ym AND store=:city 
				GROUP BY sales_date,item_name order by sales_date";
			}
		}else if($this->r=="Out"){


				$condition .= "SELECT date AS d,expense_by AS ii,particular as particular, SUM(quantity*amount) AS p FROM expense
				WHERE substring(date,1,7)=:ym AND city=:city " . $condition1 . " AND expense_reason='".$this->st."' 
				GROUP BY date,expense_reason,particular order by date";
		

			// $condition .= "SELECT a.*,ascd FROM (SELECT date AS d,expense_reason AS description,particular as ii, SUM(quantity*amount) AS p FROM expense
			// WHERE exp_type<>'Fixed' AND exp_type<>'Fixed-1' and date>=:df AND date<=:dt AND (exp_for LIKE '%' :city '%') AND expense_reason='".$this->st."'
			// GROUP BY date,expense_reason) AS a LEFT JOIN (SELECT * FROM expense_reason GROUP BY expense_reason) AS b ON
			// a.description=b.expense_reason ORDER BY d";
		}


		$query = $condition;

		$stmt = $this->conn->prepare($query);

		$stmt->bindParam(":city", $this->city);
		$stmt->bindParam(":ym", $this->ym);
		$stmt->execute();
		return $stmt;
	}

	function searchbyMbps(){
    	$condition = "";
		if($this->df){
			$condition .= " and sales_date>=:df ";
		}
		if($this->dt){

			$condition .= "  and sales_date<=:dt ";
		}
		if($this->ym){

			$condition .= "  and substring(sales_date,1,7)=:ym ";
		}

		$query = "SELECT mbps,
sum(IFNULL(1M,0)) AS 'M1M',
sum(IFNULL(2M,0)) AS 'M2M',
sum(IFNULL(3M,0)) AS 'M3M',
sum(IFNULL(4M,0)) AS 'M4M',
sum(IFNULL(5M,0)) AS 'M5M',
sum(IFNULL(6M,0)) AS 'M6M',
sum(IFNULL(7M,0)) AS 'M7M',
sum(IFNULL(8M,0)) AS 'M8M',
sum(IFNULL(9M,0)) AS 'M9M',
sum(IFNULL(10M,0)) AS 'M10M',
sum(IFNULL(11M,0)) AS 'M11M',
sum(IFNULL(12M,0)) AS 'M12M',
					sum(total) AS total from
					(select
					mbps,
					case when months = 1 then c end AS '1M',
					case when months = 2 then c end AS '2M',
					case when months = 3 then c end AS '3M',
					case when months = 4 then c end AS '4M',
					case when months = 5 then c end AS '5M',
					case when months = 6 then c end AS '6M',
					case when months = 7 then c end AS '7M',
					case when months = 8 then c end AS '8M',
					case when months = 9 then c end AS '9M',
					case when months = 10 then c end AS '10M',
					case when months = 11 then c end AS '11M',
					case when months = 12 then c end AS '12M',
					total FROM
					(SELECT sales_date,sales.mbps,months,COUNT(*) AS c,sum(package_price*months) AS total FROM sales 
LEFT JOIN (SELECT mbps,package_price FROM price_change WHERE price_change_date=(SELECT MAX(price_change_date) FROM price_change where city=:city)) AS pc ON sales.mbps=pc.mbps
						where city=:city ".$condition." GROUP BY mbps,months) AS a GROUP BY mbps,months) AS b GROUP BY mbps";
		
		$stmt = $this->conn->prepare($query);
		$stmt->bindParam(":city", $this->city);
		if($this->df)$stmt->bindParam(":df", $this->df);
		if($this->dt)$stmt->bindParam(":dt", $this->dt);
		if($this->ym)$stmt->bindParam(":ym", $this->ym);
		$stmt->execute();
		return $stmt;	
	}

	
}
?>