	<?php 

//Menu List Display Grid - START
function getMenuArray_tot($db, $act=null,$whrcon=null,$ordr=null,$stt=null,$len=null) 
{	
	$str_all="select * from a_menus where 1=1 and IsActive <>2 ";
	$res = $db->get_rsltset($str_all);
	return $totalData = count($res);
}

function getMenuArray_Ajx($db, $act=null,$whrcon=null,$ordr=null,$stt=null,$len=null) 
{	
		$str_all="select * from a_menus where 1=1 and IsActive <> 2 "; 		
		$rescntchk =  $db->get_rsltset($str_all); 
	
		if($whrcon != "")
		$str_all .= $whrcon;	
	
		$totalFiltered =  $totalData; 
		
		if(trim($ordr) != "")
		$str_all .= $ordr;
		
		if($stt != "")
		$str_all .= "limit ".$stt.",".$len;		
	
		$res = $db->get_rsltset($str_all); 
		$totalData = count($rescntchk);
		$totalFiltered = $totalData;  // when there is no search parameter then total number rows = total number filtered rows.
	
		return $res; 			
}
//Menu List Display Grid - END

//Module List Display Grid - START

function getModuleArray_tot($db, $act=null,$whrcon=null,$ordr=null,$stt=null,$len=null) 
{	
	$str_all="select * from a_modules where 1=1 and IsActive <>2 ";
	$res = $db->get_rsltset($str_all);
	return $totalData = count($res);
}

function getModuleArray_Ajx($db, $act=null,$whrcon=null,$ordr=null,$stt=null,$len=null) 
{	
		$str_all="select * from a_modules where 1=1 and IsActive <> 2 "; 		
		$rescntchk =  $db->get_rsltset($str_all); 
	
		if($whrcon != "")
		$str_all .= $whrcon;	
	
		$totalFiltered =  $totalData; 
		
		if(trim($ordr) != "")
		$str_all .= $ordr;
		
		if($stt != "")
		$str_all .= "limit ".$stt.",".$len;	       
	
		$res = $db->get_rsltset($str_all); 
		$totalData = count($rescntchk);
		$totalFiltered = $totalData;  // when there is no search parameter then total number rows = total number filtered rows.
	
		return $res; 			
}
//Module List Display Grid - END


//ModuleMenu List Display Grid - START

function getModuleMenuArray_tot($db, $act=null,$whrcon=null,$ordr=null,$stt=null,$len=null) 
{	
    $str_all="select * from a_menus where IsActive = '1' ";

	$res = $db->get_rsltset($str_all);
	return $totalData = count($res);
}


function getModuleMenuArray_Ajx($db, $act=null,$whrcon=null,$ordr=null,$stt=null,$len=null) 
{	
        $str_all="select * from a_menus where IsActive = '1' ";

		$rescntchk =  $db->get_rsltset($str_all); 
	
		if($whrcon != "")
		$str_all .= $whrcon;	
	
		$totalFiltered =  $totalData; 
		
		if(trim($ordr) != "")
		$str_all .= $ordr;
		
		if($stt != "")
		$str_all .= "limit ".$stt.",".$len;	  	
	
		$res = $db->get_rsltset($str_all); 
		$totalData = count($rescntchk);
		$totalFiltered = $totalData;  // when there is no search parameter then total number rows = total number filtered rows.
	
		return $res; 			
}
//ModuleMenu List Display Grid - END


//Permission Info List Display Grid - START

function getPermissionInfoArray_tot($db, $act=null,$whrcon=null,$ordr=null,$stt=null,$len=null) 
{    	
	if($_SESSION['RoleId'] == "1"){
		$str_all="select * from a_roles where IsActive = '1' ";
	}
	else{
		$str_all="select * from a_roles where IsActive = '1' and RoleId <> '1' ";
	}   

	$res = $db->get_rsltset($str_all);
	return $totalData = count($res);
}


function getPermissionInfoArray_Ajx($db, $act=null,$whrcon=null,$ordr=null,$stt=null,$len=null) 
{		
        if($_SESSION['RoleId'] == "1"){
			$str_all="select * from a_roles where IsActive = '1' ";
		}
		else{
			$str_all="select * from a_roles where IsActive = '1' and RoleId <> '1' ";
		}   

		$rescntchk =  $db->get_rsltset($str_all); 
	
		if($whrcon != "")
		$str_all .= $whrcon;	
	
		$totalFiltered =  $totalData; 
		
		if(trim($ordr) != "")
		$str_all .= $ordr;
		
		if($stt != "")
		$str_all .= "limit ".$stt.",".$len;	  	
	
	//echo $str_all; die();
	
		$res = $db->get_rsltset($str_all); 
		$totalData = count($rescntchk);
		$totalFiltered = $totalData;  // when there is no search parameter then total number rows = total number filtered rows.
	
		return $res; 			
}

//Permission Info List Display Grid - END



//Role List Display Grid - START
function getRoleArray_tot($db, $act=null,$whrcon=null,$ordr=null,$stt=null,$len=null) 
{	
	$str_all="select * from a_roles where 1=1 and IsActive <>2  and RoleId <> 1";
	$res = $db->get_rsltset($str_all);
	return $totalData = count($res);
}

function getRoleArray_Ajx($db, $act=null,$whrcon=null,$ordr=null,$stt=null,$len=null) 
{	
		$str_all="select * from a_roles where 1=1 and IsActive <> 2  and RoleId <> 1 "; 		
		$rescntchk =  $db->get_rsltset($str_all); 
	
		if($whrcon != "")
		$str_all .= $whrcon;	
	
		$totalFiltered =  $totalData; 
		
		if(trim($ordr) != "")
		$str_all .= $ordr;
		
		if($stt != "")
		$str_all .= "limit ".$stt.",".$len;	       
	
		$res = $db->get_rsltset($str_all); 
		$totalData = count($rescntchk);
		$totalFiltered = $totalData;  // when there is no search parameter then total number rows = total number filtered rows.
	
		return $res; 			
}
//Role List Display Grid - END

//User List Display Grid - START
function getUserArray_tot($db, $act=null,$whrcon=null,$ordr=null,$stt=null,$len=null) 
{	
	$str_all="select u.*,r.RoleName from a_users u LEFT JOIN  a_roles r on u.RoleId = r.RoleId where  u.IsActive <> 2 and u.user_ID <> 1   "; 	
	$res = $db->get_rsltset($str_all);
	return $totalData = count($res);
}

function getUserArray_Ajx($db, $act=null,$whrcon=null,$ordr=null,$stt=null,$len=null) 
{	
		$str_all="select u.*,r.RoleName from a_users u LEFT JOIN  a_roles r on u.RoleId = r.RoleId where  u.IsActive <> 2 and u.user_ID <> 1   "; 		
		$rescntchk =  $db->get_rsltset($str_all); 
	
		if($whrcon != "")
		$str_all .= $whrcon;	
	
		$totalFiltered =  $totalData; 
		
		if(trim($ordr) != "")
		$str_all .= $ordr;
		
		if($stt != "")
		$str_all .= "limit ".$stt.",".$len;	       
	
		$res = $db->get_rsltset($str_all); 
		$totalData = count($rescntchk);
		$totalFiltered = $totalData;  // when there is no search parameter then total number rows = total number filtered rows.
	
		return $res; 			
}
//User List Display Grid - END


//Company List Display Grid - START
function getCompanyArray_tot($db, $act=null,$whrcon=null,$ordr=null,$stt=null,$len=null) 
{	
	$str_all="select count(*) as cnt from m_company where IsActive<>2 ";
	
	if($whrcon != "")		
		$str_all .= $whrcon;		
	
	$res = $db->get_a_line($str_all);
	
	return $res['cnt'];
}

function getCompanyArray_Ajx($db, $act=null,$whrcon=null,$ordr=null,$stt=null,$len=null) 
{
	$str_all="select * from m_company where IsActive<>2  "; 	
		
	if($whrcon != "")
		$str_all .= $whrcon;		
				
	if(trim($ordr) != "")
		$str_all .= $ordr;
		
	if($stt != "")
		$str_all .= "limit ".$stt.",".$len;	    
		 
	$res = $db->get_rsltset($str_all); 	
	
	return $res;	
	
}
//Company List Display Grid - END


//Supplier List Display Grid - START
function getSupplierArray_tot($db, $act=null,$whrcon=null,$ordr=null,$stt=null,$len=null) 
{
	$str_all="select count(*) as cnt from m_supplier where IsActive<>2 ";
	
	if($whrcon != "")		
		$str_all .= $whrcon;		
	
	$res = $db->get_a_line($str_all);		
	
	return $res['cnt'];
}

function getSupplierArray_Ajx($db, $act=null,$whrcon=null,$ordr=null,$stt=null,$len=null) 
{	
			
	$str_all="select *, CASE WHEN sup_type=1 THEN 'Individual' ELSE 'Company' END AS disp_sup_type, CASE WHEN sup_payment_type=1 THEN 'GST' ELSE 'Non-GST' END AS disp_sup_payment_type from m_supplier where IsActive<>2"; 	
		
	if($whrcon != "")
		$str_all .= $whrcon;		
				
	if(trim($ordr) != "")
		$str_all .= $ordr;
		
	if($stt != "")
		$str_all .= "limit ".$stt.",".$len;	    
		 
		 
	$res = $db->get_rsltset($str_all); 	
	
	return $res;	
		
}
//Supplier List Display Grid - END


//Customer List Display Grid - START
function getCustomerArray_tot($db, $act=null,$whrcon=null,$ordr=null,$stt=null,$len=null) 
{	
	$str_all="select * from m_customer where IsActive<>2 "; 	
	if($whrcon != "")		
		$str_all .= $whrcon;	
	$res = $db->get_rsltset($str_all);			
	return $totalData = count($res);
}

function getCustomerArray_Ajx($db, $act=null,$whrcon=null,$ordr=null,$stt=null,$len=null) 
{	
		$str_all="select *, CASE WHEN cus_type=1 THEN 'Individual' ELSE 'Company' END AS disp_cus_type from m_customer where IsActive<>2 "; 
		$rescntchk =  $db->get_rsltset($str_all); 
	
		if($whrcon != "")
		$str_all .= $whrcon;	
	
		$totalFiltered =  $totalData; 
		
		if(trim($ordr) != "")
		$str_all .= $ordr;
		
		if($stt != "")
		$str_all .= "limit ".$stt.",".$len;	       
	
		$res = $db->get_rsltset($str_all); 
		$totalData = count($rescntchk);
		$totalFiltered = $totalData;  // when there is no search parameter then total number rows = total number filtered rows.
	
		return $res; 			
}
//Customer List Display Grid - END



//Purchase Entry List Display Grid - START
function getPurchaseEntryArray_tot($db, $act=null,$whrcon=null,$ordr=null,$stt=null,$len=null) 
{	
	$str_all="select count(*) as cnt from t_purchase_entry_main t1 
		inner join m_company t2 on t1.company_id=t2.company_id
		inner join m_supplier t3 on t1.sup_id = t3.sup_id where 1=1 and t1.IsActive <>2 ";
	
	if($whrcon != "")		
		$str_all .= $whrcon;		
	
	$res = $db->get_a_line($str_all);
	
	return $res['cnt'];
}

function getPurchaseEntryArray_Ajx($db, $act=null,$whrcon=null,$ordr=null,$stt=null,$len=null) 
{	
		$str_all="select t1.*,DATE_FORMAT(t1.p_invoice_date,'%d-%b-%Y') AS disp_inv_date, t2.company_name, t3.sup_name from t_purchase_entry_main t1 
		inner join m_company t2 on t1.company_id=t2.company_id
		inner join m_supplier t3 on t1.sup_id = t3.sup_id where 1=1 and t1.IsActive <>2 ";

		$rescntchk =  $db->get_rsltset($str_all); 
	
		if($whrcon != "")
		$str_all .= $whrcon;	
	
		$totalFiltered =  $totalData; 
		
		if(trim($ordr) != "")
		$str_all .= $ordr;
		
		if($stt != "")
		$str_all .= "limit ".$stt.",".$len;	       
	
		$res = $db->get_rsltset($str_all); 
		$totalData = count($rescntchk);
		$totalFiltered = $totalData;  // when there is no search parameter then total number rows = total number filtered rows.
	
		return $res; 			
}
//Purchase Entry List Display Grid - END



//Sales Entry List Display Grid - START
function getSalesEntryArray_tot($db, $act=null,$whrcon=null,$ordr=null,$stt=null,$len=null) 
{	
	$str_all="select t1.*,DATE_FORMAT(t1.s_bill_date,'%d-%b-%Y') AS disp_bill_date, t2.tot_paid_amt, t2.due_amt, t3.company_name, t4.cus_name from t_sales_entry_main t1 
left join t_sales_payment t2 on t1.s_bill_no = t2.s_bill_no
inner join m_company t3 on t1.company_id=t3.company_id
inner join m_customer t4 on t1.cus_id = t4.cus_id
where 1=1 and t1.IsActive <>2  "; 	
	if($whrcon != "")
		$str_all .= $whrcon;	

	$res = $db->get_rsltset($str_all);
	return $totalData = count($res);
}

function getSalesEntryArray_Ajx($db, $act=null,$whrcon=null,$ordr=null,$stt=null,$len=null) 
{	
		$str_all="select t1.*,DATE_FORMAT(t1.s_bill_date,'%d-%b-%Y') AS disp_bill_date, t2.tot_paid_amt, t2.due_amt, t3.company_name, t4.cus_name from t_sales_entry_main t1 
left join t_sales_payment t2 on t1.s_bill_no = t2.s_bill_no
inner join m_company t3 on t1.company_id=t3.company_id
inner join m_customer t4 on t1.cus_id = t4.cus_id
where 1=1 and t1.IsActive <>2 ";

		$rescntchk =  $db->get_rsltset($str_all); 
	
		if($whrcon != "")
		$str_all .= $whrcon;	
	
		$totalFiltered =  $totalData; 
		
		if(trim($ordr) != "")
		$str_all .= $ordr;
		
		if($stt != "")
		$str_all .= "limit ".$stt.",".$len;	 

	
		$res = $db->get_rsltset($str_all); 
		$totalData = count($rescntchk);
		$totalFiltered = $totalData;  // when there is no search parameter then total number rows = total number filtered rows.
	
		return $res; 			
}
//Sales Entry List Display Grid - END



//Report Product List Display Grid - START
function getRptPdtListArray_tot($db, $act=null,$whrcon=null,$ordr=null,$stt=null,$len=null) 
{	
	$constr="";
	if($_REQUEST['srch_FinancialYear']!= ""){
		$constr.=" and fy.financial_id='".$_REQUEST['srch_FinancialYear']."' ";
		}

	if($_REQUEST['startDate'] != "" )
		{
			//print_r($_REQUEST['daterange']); exit;
			//$datesplt = explode(' - ',$_REQUEST['daterange']);
			$tempdate=explode("/",$_REQUEST['startDate']);
			$fromdate=$tempdate[2].'-'.$tempdate[1]."-".$tempdate[0];

			if(empty($_REQUEST['endDate'])){
				if(!empty($_REQUEST['srch_FinancialYear'])){
					if($_REQUEST['srch_FinancialYear'] == 3) {
						$todate = '2021-03-31';
					}else if($_REQUEST['srch_FinancialYear'] == 2) $todate = '2020-03-31';
					else $todate = '2019-03-31';
				}else{
					$todate = '2021-03-31';
				}
			}else{
				$tempdate=explode("/",$_REQUEST['endDate']);
				$todate=$tempdate[2].'-'.$tempdate[1]."-".$tempdate[0];
			}						
			
			$constr.=" and date(t2.p_invoice_date) between '".$fromdate."' and '".$todate."' ";
		
		} 
				
		if($_REQUEST['srch_sup_company']!= ""){
		$constr.=" and t3.company_id='".$_REQUEST['srch_sup_company']."' ";
		}
		
		if($_REQUEST['srch_stock']!= ""){
			
			if($_REQUEST['srch_stock']=="Available")
				$constr.=" and s.s_bill_no is null ";
			else if($_REQUEST['srch_stock']=="Sold")
				$constr.=" and s.s_bill_no is not null ";
			 
		}
		


	
	$str_all=" SELECT count(distinct t1.p_invoice_det_id) as cnt
  FROM t_purchase_entry_details t1
       INNER JOIN
       t_purchase_entry_main t2 ON t1.p_invoice_no = t2.p_invoice_no
       INNER JOIN
       m_company t3 ON t2.company_id = t3.company_id
       INNER JOIN
       m_financial_year fy ON fy.financial_id = t2.financial_id
			inner join m_supplier t4 on t2.sup_id = t4.sup_id
        LEFT JOIN
       (SELECT sd.s_bill_det_id, t1.s_bill_no,t1.actu_bill_no, t1.s_bill_date, t1.total_sale_amount,
               t1.after_discount_amt, t1.tot_tax_amt, t1.final_sale_amt,
               t3.company_name, t4.cus_name, t4.cus_gstno, sd.pdt_sku,
               sd.purchase_price, sd.sale_price, t1.financial_id,t1.discount_per,t1.cgst_per,t1.sgst_per,t1.igst_per
          FROM t_sales_entry_main t1
               INNER JOIN
               t_sales_entry_details sd ON sd.financial_id = t1.financial_id AND sd.s_bill_no = t1.s_bill_no
               INNER JOIN
               m_company t3 ON t1.company_id = t3.company_id
               INNER JOIN
               m_customer t4 ON t1.cus_id = t4.cus_id
         WHERE t1.isactive = 1) s ON s.pdt_sku = t1.pdt_sku AND s.financial_id = t1.financial_id
			
 WHERE  t1.isactive = 1
   AND t2.isactive = 1 ".$constr;	
   
   	if($whrcon != "")
		$str_all .= $whrcon;	
	

	$res = $db->get_a_line($str_all);
	return $res['cnt'];
}

function getRptPdtListArray_Ajx($db, $act=null,$whrcon=null,$ordr=null,$stt=null,$len=null) 
{	
	/*$str_all="select t1.p_invoice_det_id, t2.p_invoice_no ,t3.company_name, DATE_FORMAT(t2.p_invoice_date,'%d-%b-%Y') AS disp_inv_date,t1.pdt_sku,t1.saree_desc, t4.sup_name  from t_purchase_entry_details t1
inner join t_purchase_entry_main t2 on t1.p_invoice_no = t2.p_invoice_no
inner join m_company t3 on t2.company_id=t3.company_id
inner join m_supplier t4 on t2.sup_id = t4.sup_id
where 1=1 and t1.IsActive=1 and t2.IsActive=1";*/
$constr="";
	if($_REQUEST['srch_FinancialYear']!= ""){
		$constr.=" and fy.financial_id='".$_REQUEST['srch_FinancialYear']."' ";
		}

	if($_REQUEST['startDate'] != "" )
		{
			//print_r($_REQUEST['daterange']); exit;
			//$datesplt = explode(' - ',$_REQUEST['daterange']);
			$tempdate=explode("/",$_REQUEST['startDate']);
			$fromdate=$tempdate[2].'-'.$tempdate[1]."-".$tempdate[0];
			if(empty($_REQUEST['endDate'])){
				if(!empty($_REQUEST['srch_FinancialYear'])){
					if($_REQUEST['srch_FinancialYear'] == 3) {
						$todate = '2021-03-31';
					}else if($_REQUEST['srch_FinancialYear'] == 2) $todate = '2020-03-31';
					else $todate = '2019-03-31';
				}else{
					$todate = '2021-03-31';
				}
			}else{
				$tempdate=explode("/",$_REQUEST['endDate']);
				$todate=$tempdate[2].'-'.$tempdate[1]."-".$tempdate[0];
			}	
								
			$constr.=" and date(t2.p_invoice_date) between '".$fromdate."' and '".$todate."' ";
		
		}	
				
		if($_REQUEST['srch_sup_company']!= ""){
		$constr.=" and t3.company_id='".$_REQUEST['srch_sup_company']."' ";
		}
		if($_REQUEST['srch_stock']!= ""){
			
			if($_REQUEST['srch_stock']=="Available")
				$constr.=" and s.s_bill_no is null ";
			else if($_REQUEST['srch_stock']=="Sold")
				$constr.=" and s.s_bill_no is not null ";
			 
		}
		
		


	
	$str_all=" SELECT concat (year (f_start_date), ' - ', year (f_end_date)) AS fyear,
       t3.company_name,
       DATE_FORMAT(t2.p_invoice_date, '%d-%b-%Y') AS disp_inv_date,
       t2.actu_inv_no as p_invoice_no, t1.pdt_sku,t1.upload_img,t1.saree_desc,t4.sup_name,t1.purchase_rate,
       if(s.s_bill_det_id is null,'Available','Sold') as status,s.actu_bill_no,
			DATE_FORMAT(s.s_bill_date, '%d-%b-%Y') AS sale_date,s.cus_name,
			s.sale_price,@discnt:=round(((s.sale_price*s.discount_per)/100),2) as discount,
			@totprice:=round((s.sale_price-@discnt),2) as discountprice,
			@taxamt:=round(((@totprice*s.cgst_per)/100)+((@totprice*s.sgst_per)/100)+((@totprice*s.igst_per)/100),2) as taxamt,
			round((@totprice+@taxamt),2) as soldprice
  FROM t_purchase_entry_details t1
       INNER JOIN
       t_purchase_entry_main t2 ON t1.p_invoice_no = t2.p_invoice_no
       INNER JOIN
       m_company t3 ON t2.company_id = t3.company_id
       INNER JOIN
       m_financial_year fy ON fy.financial_id = t2.financial_id
			inner join m_supplier t4 on t2.sup_id = t4.sup_id
       LEFT JOIN
       (SELECT sd.s_bill_det_id, t1.s_bill_no,t1.actu_bill_no, t1.s_bill_date, t1.total_sale_amount,
               t1.after_discount_amt, t1.tot_tax_amt, t1.final_sale_amt,
               t3.company_name, t4.cus_name, t4.cus_gstno, sd.pdt_sku,
               sd.purchase_price, sd.sale_price, t1.financial_id,t1.discount_per,t1.cgst_per,t1.sgst_per,t1.igst_per
          FROM t_sales_entry_main t1
               INNER JOIN
               t_sales_entry_details sd ON sd.financial_id = t1.financial_id AND sd.s_bill_no = t1.s_bill_no
               INNER JOIN
               m_company t3 ON t1.company_id = t3.company_id
               INNER JOIN
               m_customer t4 ON t1.cus_id = t4.cus_id
         WHERE t1.isactive = 1) s ON s.pdt_sku = t1.pdt_sku AND s.financial_id = t1.financial_id
			
 WHERE  t1.isactive = 1
   AND t2.isactive = 1 ".$constr;


	
	
	
		if($whrcon != "")
		$str_all .= $whrcon;	
	
		
		if(trim($ordr) != "")
		$str_all .= $ordr;
		
		if($stt != "")
		$str_all .= "limit ".$stt.",".$len;	       
	//echo $str_all; die();
		$res = $db->get_rsltset($str_all); 
		
	
		return $res; 			
}
//Report Product List Display Grid - END



//Banks List Display Grid - START
function getBanksArray_tot($db, $act=null,$whrcon=null,$ordr=null,$stt=null,$len=null) 
{	
	$str_all="select count(*) as cnt from m_banks t1 inner join m_company t2 on t1.company_id = t2.company_id where 1=1 and t1.IsActive<>2 ";
	
	if($whrcon != "")		
		$str_all .= $whrcon;		
	
	$res = $db->get_a_line($str_all);
	
	return $res['cnt'];
}

function getBanksArray_Ajx($db, $act=null,$whrcon=null,$ordr=null,$stt=null,$len=null) 
{					
	$str_all="select t1.*, t2.company_name from m_banks t1 inner join m_company t2 on t1.company_id = t2.company_id where 1=1 and t1.IsActive<>2   "; 	
		
	if($whrcon != "")
		$str_all .= $whrcon;		
				
	if(trim($ordr) != "")
		$str_all .= $ordr;
		
	if($stt != "")
		$str_all .= "limit ".$stt.",".$len;	    
		 
	$res = $db->get_rsltset($str_all); 	
	
	return $res; 				
}
//Banks List Display Grid - END

?>