<?php
include_once('database_connection.php');

if(isset($_GET['action']))
{
	if($_GET['action']=='GetDatabases')
	{
		$data=array();
		// dbid not in (select AdditionalInfo5 from godowns where degree=2 or convert(Numeric,(case when AdditionalInfo5='' or AdditionalInfo5=null then 1 else AdditionalInfo5 end ) )>1
$sql="SELECT '['+name+']' as DbName,dbid FROM master.dbo.sysdatabases where dbid>1";
$result=odbc_exec($connection, $sql) or die(odbc_error().$sql);
	 while ($row = odbc_fetch_array($result)) {
	
    $data []=$row;
  		}//end while
  		//print_r($data);
  		echo json_encode($data); 
	}

	if($_GET['action']=='GetLocation')
	{

				$data=array();
		$GodownID=isset($_GET['GodownID'])?$_GET['GodownID']:'';
		$UnderName=isset($_GET['UnderName'])?$_GET['UnderName']:'';
		$Department=isset($_GET['Department'])?$_GET['Department']:'';
		$Company=isset($_GET['Company'])?$_GET['Company']:'';			
		$Keyfield=isset($_GET['KeyField'])?$_GET['KeyField']:'';

		if($Department!="")
		{
			$Department="a.Name like '%".$Department."%' and ";
		}
		else
		{
			$Department="";
		}
		if($Company!="")
		{
			$Company="a.Name like '%".$Company."%' and ";
		}
		else
		{
			$Company="";
		}
		if($UnderName!="")
		{
						$UnderName="b.Name like '%".$UnderName."%' and ";

		}
		else
		{
			$UnderName="";
		}
		if($Keyfield!="")
		{
						$Keyfield="a.Keyfield like '%".$Keyfield."%' and ";

		}
		else
		{
			$Keyfield="";
		}
		if($GodownID!="")
		{
						$GodownID="a.GodownID=".$GodownID." and ";

		}
		else
		{ 
			$GodownID="";
		}
		$searchbox=$Department."".$UnderName."".$Keyfield."".$GodownID."".$Company;

		$sql="Select  ROW_NUMBER() OVER (
      ORDER BY a.degree
   )  as row_num,a.Name as Name,a.AdditionalInfo3 as EmailAddress1,a.AdditionalInfo2 as EmailAddress2,a.Address as Address,a.AdditionalInfo5 as Address2,a.ContactNo as ContactNo,a.AdditionalInfo1 as TRNNo,a.AdditionalInfo5 as Website,'' as FAX,b.Name as UnderName,a.Keyfield as Keyfield,'' as col_Action,a.GodownID as GodownID,a.AdditionalInfo1 as AdditionalInfo1,a.AdditionalInfo2 as AdditionalInfo2,a.AdditionalInfo3 as AdditionalInfo3,a.AdditionalInfo4 as AdditionalInfo4,a.AdditionalInfo5 as AdditionalInfo5,'' as LastModificationTimeStamp
   ,a.Degree as degree1 from godowns as a left outer join godowns as b 
   on a.Under=b.GodownID where ".$searchbox." a.degree=1 and a.Active=1";
	$result=odbc_exec($connection, $sql) or die(odbc_error().$sql);
	 while ($row = odbc_fetch_array($result)) {
	$row['Name']=htmlspecialchars($row['Name']); 	
  	$row['col_Action']='<i class="fa fa-pencil fa-lg" style="text-align: left;color: blue;cursor:pointer;" onclick="javascript:editRow(this);"></i> &nbsp;&nbsp;&nbsp;<i class="fa fa-trash-o fa-lg" style="text-align: right;color: red;cursor:pointer;" onclick="javascript:deleteRow(this);"></i>';
    $data []=$row;
  		}//end while
  		//print_r($data);
  		echo json_encode($data); 


	
	}

	if($_GET['action']=="GetDepartment")
	{
				$data=array();
		$GodownID=isset($_GET['GodownID'])?$_GET['GodownID']:'';
		$UnderName=isset($_GET['UnderName'])?$_GET['UnderName']:'';
		$Department=isset($_GET['Department'])?$_GET['Department']:'';
		$Keyfield=isset($_GET['Keyfield'])?$_GET['Keyfield']:'';
		if($Department!="")
		{
			$Department="a.Name like '%".$Department."%' and ";
		}
		else
		{
			$Department="";
		}
		if($UnderName!="")
		{
						$UnderName="b.Name like '%".$UnderName."%' and ";

		}
		else
		{
			$UnderName="";
		}
		if($Keyfield!="")
		{
						$Keyfield="a.Keyfield like '%".$Keyfield."%' and ";

		}
		else
		{
			$Keyfield="";
		}
		if($GodownID!="")
		{
						$GodownID="a.GodownID=".$GodownID." and ";

		}
		else
		{
			$GodownID="";
		}
		$searchbox=$Department."".$UnderName."".$Keyfield."".$GodownID;
//a.LastModificationTimeStamp
		$sql="Select  ROW_NUMBER() OVER (
      ORDER BY a.degree
   )  as row_num,a.Name as Name,b.Name as UnderName,a.Keyfield as Keyfield,'' as col_Action,a.GodownID as GodownID,'' as LastModificationTimeStamp
   ,a.Degree as degree1 from godowns as a left outer join godowns as b 
   on a.Under=b.GodownID where ".$searchbox." a.degree=2 and a.Active=1";
	$result=odbc_exec($connection, $sql) or die(odbc_error().$sql);
	 while ($row = odbc_fetch_array($result)) {
	$row['Name']=htmlspecialchars($row['Name']); 	
  	$row['col_Action']='<i class="fa fa-pencil fa-lg" style="text-align: left;color: blue;cursor:pointer;" onclick="javascript:editMaster(this);"></i> &nbsp;&nbsp;&nbsp;<i class="fa fa-trash-o fa-lg" style="text-align: right;color: red;cursor:pointer;" onclick="javascript:deleteMaster(this);"></i>';
    	foreach ($row as $key => $value) {
    		# code...
    		$row[$key]=utf8_encode($value);
    	}
    $data []=$row;
  		}//end while
  		//print_r($data);
  		echo json_encode($data); 


	}
	if($_GET['action']=="InsertDepartment")
	{
	//	print_r($_POST);
		$data=array_keys($_POST);
		$data=json_decode($data[0]);
		$GodownID=isset($data->GodownID)?$data->GodownID:0;
		$Name=isset($data->Name)?$data->Name:'';
		$Under=isset($data->Under)?$data->Under:0;

		if($GodownID>0 && $GodownID!="")
		{
		$updatesql="update godowns set datamode=0,Name='".$Name."',Under=".$Under." where GodownID=".$GodownID;	
		}

		else  {
				# code...
			$where="";
			$where=" Degree=2 and active=1 ";
			$KeyField=generateCode("godowns","KeyField",$where,$connection)+1;
           	
           	$KeyField2=str_pad($KeyField,5,"0",STR_PAD_LEFT);
           	$KeyField="DEPT-".$KeyField2;
           	

			$ID=getMaxId("godowns","GodownID",$connection)+1;
			$updatesql="insert into godowns (GodownID,Name,Under,Keyfield,Active,Degree,datamode) values(".$ID.",'".$Name."',".$Under.",'".$KeyField."',1,2,0)";
			}	
			odbc_exec($connection,$updatesql);

		$sqlupdate="update godowns set datamode=0";
  		  odbc_exec($connection,$sqlupdate);

			$res=array('status'=>"ok",'description'=>'Record Save Sucessfully');
		echo json_encode($res);

	}
	if($_GET['action']=="InsertGodowns")
	{

			//$_POST=utf8_decode($_POST);
				$data=array_keys($_POST);
			//	$data=utf8_decode($data[0]);
		
				
		/*foreach ($data as $key => $value) {
			# code...
			$data[$key]=utf8_decode($value);
		}*/
		$data=json_decode($data[0]);
		//print_r($data);
			
		 $GodownID=isset($data->GodownID)?$data->GodownID:0;

		 $Name=isset($data->Name)?urldecode($data->Name):'';
		 $Name=str_replace("_"," ",$Name);
		//return;
		$Under=isset($data->Under)?$data->Under:0;
		$AdditionalInfo1=isset($data->AdditionalInfo1)?$data->AdditionalInfo1:'';
		$AdditionalInfo2=isset($data->AdditionalInfo2)?$data->AdditionalInfo2:'';
 		$AdditionalInfo3=isset($data->AdditionalInfo3)?urldecode($data->AdditionalInfo3):'';
			//return;

		$AdditionalInfo4=isset($data->AdditionalInfo4)?$data->AdditionalInfo4:'';
		$AdditionalInfo5=isset($data->AdditionalInfo5)?$data->AdditionalInfo5:'';
		$Address=isset($data->Address)?urldecode($data->Address):'';
		$ContactNo=isset($data->ContactNo)?urldecode($data->ContactNo):'';
		$EmailAddress1=isset($data->EmailAddress1)?str_replace("_",".",$data->EmailAddress1):'';
		$EmailAddress2=isset($data->EmailAddress2)?str_replace("_",".",$data->EmailAddress2):'';
		$FAX=isset($data->FAX)?str_replace("_",".",$data->FAX):'';
		$TRNNo=isset($data->TRNNo)?$data->TRNNo:'';
		$Website=isset($data->Website)?urldecode($data->Website):'';
		$Address2=isset($data->Address2)?urldecode($data->Address2):'';
		$sql="select * from godowns where AdditionalInfo5 in($AdditionalInfo5)";
		$result=odbc_exec($connection,$sql);
		if(odbc_num_rows($result)>0)
		{
			$res=array('status'=>"error",'description'=>'Database Already Assigned');
		echo json_encode($res);
			return;
		}

		if($GodownID>0 && $GodownID!="")
		{
		$sql="update godowns set Name='".$Name."',Under=".$Under.",AdditionalInfo1='$AdditionalInfo1',AdditionalInfo2='$AdditionalInfo2',AdditionalInfo4='1',AdditionalInfo5='$AdditionalInfo5',Address='$Address',datamode=0,EmailAddress1='$EmailAddress1',EmailAddress2='$EmailAddress2',FAX='$FAX',TRNNo='$TRNNo',Website='$Website',Address2='$Address2' where GodownID=".$GodownID;	
		}
		else
		{
			$where=" Degree=1 and active=1 ";
		$KeyField=generateCode("godowns","KeyField",$where,$connection)+1;
        $KeyField2=str_pad($KeyField,5,"0",STR_PAD_LEFT);
        $KeyField="G-".$KeyField2;
        $ID=getMaxId("godowns where Degree=1","GodownID",$connection)+1;
        $AdditionalInfo3=$ID*100000000;

  		  $sql="insert into godowns (GodownID,Name,Under,Keyfield,Active,Degree,AdditionalInfo1,AdditionalInfo2,AdditionalInfo3,AdditionalInfo4,AdditionalInfo5,Address,ContactNo,GodownType,[group],datamode,EmailAddress1,EmailAddress2,FAX,TRNNo,Website,Address2) values(".$ID.",'".$Name."',".$Under.",'".$KeyField."',1,1,'$AdditionalInfo1','$AdditionalInfo2','$AdditionalInfo3','1','$AdditionalInfo5','$Address','$ContactNo',1,0,0,'$EmailAddress1','$EmailAddress2','$FAX','$TRNNo','$Website','$Address2')";
  		}
  		  odbc_exec($connection,$sql);
  		  $sqlupdate="update godowns set datamode=0";
  		  odbc_exec($connection,$sqlupdate);
  		    		  $res=array('status'=>"ok",'description'=>'Record Save Sucessfully');
		echo json_encode($res);

	}//end insert godowns

	if($_GET['action']=="DeleteDepartment")
	{
		$GodownID=$_GET['GodownID'];
		$sql="update godowns set Active=0,datamode=0 where GodownID=".$GodownID;
		odbc_exec($connection,$sql);
$res=array('status'=>"ok",'description'=>'Record Deleted Sucessfully');
		echo json_encode($res);
	}
	if($_GET['action']=="DeleteLocation")
	{
		$GodownID=$_GET['GodownID'];
		$sql="update godowns set Active=0,datamode=0 where GodownID=".$GodownID;
		odbc_exec($connection,$sql);
$res=array('status'=>"ok",'description'=>'Record Deleted Sucessfully');
		echo json_encode($res);
	}
}


?>