<?php
include_once('database_connection.php');
if(isset($_GET['action']))
{

$ProductBaseType=$_GET['ProductBaseType'];


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

		if(isset($_GET['ID']))
		{
			$Code=$_GET['ID'];
			//$sql="select * from inventory where ID=".$ID;
$sql="select  Name as ItemName,Under as ProductCategoryID,AdditionalInfo2 as Code,Make as Brand,Rating as Color,Model as Size,CompanyID as CompanyID,'' as col_Action,ID,UnitID,Attr1,AdditionalInfo3,AdditionalInfo5,Barcode,cast(StandardPrice3 as numeric) as StandardPrice3,cast(StandardPrice2 as numeric) as StandardPrice2,cast(StandardPrice1 as numeric) as StandardPrice1,cast(MinPriceLevel as numeric) as MinPriceLevel,cast(CommisionPercent as numeric) as CommisionPercent,cast(CommisionAmount as numeric) as CommisionAmount,cast(MinQtyLevel as numeric) as MinQtyLevel,cast(MaxQtyLevel as numeric) as MaxQtyLevel,KeyField,ShowInPurchases,ShowInSales,IsFixedAsset,ShowInStock,IsNonStockAble,Details,LastModificationTimeStamp,cast(VATPercent as numeric) as VATPercent,AdditionalInfo2,AdditionalInfo1,Active from inventory where AdditionalInfo1='$Code'";// offset $start rows fetch next $limit rows only";*/
		}
		else
		{
		$start=0;
		$limit=20;
    if(isset($_GET['ItemName']) && $_GET['ItemName']!="" && $_GET['ItemName']!="undefined")
    {
        $ItemName=" i.Name like '%".$_GET['ItemName']."%' and ";
    }
    else
    {
$ItemName="";
    }
    if(isset($_GET['CompanyID']) && $_GET['CompanyID']>0 && $_GET['CompanyID']!="undefined")
    {
        $CompanyID=" i.CompanyID=".$_GET['CompanyID']." and ";
    }
    else
    {
$CompanyID="";
    }
        if(isset($_GET['Under']) && $_GET['Under']>0 && $_GET['Under']!="undefined")
    {
        $Under=" i.Under=".$_GET['Under']." and ";
    }
    else
    {
$Under="";
    }
        if(isset($_GET['Code']) && $_GET['Code']!="" && $_GET['Code']!="undefined")
    {
        $Code=" i.AdditionalInfo2 like '%".$_GET['Code']."%' and ";
    }
    else
    {
$Code="";
    }
        $searchBox="".$ItemName.$CompanyID.$Under.$Code;
		$sql="select   ROW_NUMBER() OVER (
      ORDER BY i.[ID]
   ) row_num,i.Name as ItemName,inv.Name as ProductCategoryName,i.AdditionalInfo2 as Code,i.Make as Brand,i.Rating as Color,i.Model as Size,i.CompanyID as CompanyID,g.AdditionalInfo1 as CompanyShortForm,'' as col_Action,i.ID as ID,i.Attr1 as Attr1 ,i.AdditionalInfo1 as AdditionalInfo1,cast(i.StandardPrice3 as numeric) as StandardPrice3,cast(i.StandardPrice2 as numeric) as StandardPrice2,cast(i.StandardPrice1 as numeric) as StandardPrice1,i.Active as Active,cast(i.MaxQtyLevel as numeric) as MaxQtyLevel from inventory as i inner join godowns as g on i.CompanyID=g.GodownID
   left outer join inventory as inv on i.under=inv.ID
     where ".$searchBox." i.[group]=0 and i.active=1 and  i.ProductBaseType='".$ProductBaseType."'  order by CompanyID";// offset $start rows fetch next $limit rows only";
		}

   $data=array();
//echo $sql;
	$result=odbc_exec($connection, $sql) or die(odbc_error().$sql);
	//'.$row["CategoryID"].'
	//$limit=425;//
	//echo  odbc_num_rows($result);
  while ($row = odbc_fetch_array($result)) {
        $row['col_Action']='<i class="fa fa-pencil fa-lg" style="text-align: left;color: blue;cursor:pointer;" onclick="javascript:editMaster(this,\''.$row["AdditionalInfo1"].'\');"></i>&nbsp;&nbsp;&nbsp;  <i class="fa fa-trash-o fa-lg" style="text-align: right;color: red;cursor:pointer;" onclick="javascript:deleteMaster(this,\''.$row["AdditionalInfo1"].'\');"></i>';
        $row['ItemName']=htmlspecialchars($row['ItemName']);
      //  $row['ProductCategoryName']=getnamesingle("inventory","Name","Under",$row['ProductCategoryName'],$connection);
      // $row['CompanyShortForm']=getnamesingle("Godowns","AdditionalInfo1","GodownID",$row['CompanyShortForm'],$connection);
        $row['Code']=htmlentities($row['Code']);

    $data []=$row;
  		}//end while
  		//print_r($data);
  		echo json_encode($data); 

	
}
}//end action

if($_GET['action']=="deleteService")
	{
           odbc_autocommit($connection, FALSE);

		$ID=$_GET['Code'];
		if($ID!="")
		{
			$sql="delete from inventory where AdditionalInfo1='".$ID."'";
			odbc_exec($connection, $sql);
		$res=array('status'=>"ok",'description'=>'Data Deleted Sucessfully');
		}
	    if (!odbc_error())
        {
      odbc_commit($connection);
      $res=array('status'=>"ok",'description'=>'Data Saved Sucessfully');
        }
        else
        {
        odbc_rollback($connection);
        $res=array('status'=>"Error",'description'=>'Record  Not Updated');

        }
}
if(isset($_GET['action']) && $_GET['action']=="InsertServiceInformation")
		{
               odbc_autocommit($connection, FALSE);

			$fields=array();
			$fields=array_keys($_POST);
		//	print_r($_POST);
			//echo $_FILES['picture']['name'];
			$data= '['.$fields[0].'""}]';
			$fields=json_decode($data);
	//		print_r($fields[0]);
	//		return;
            $d=$fields[0];

			if(isset($d->ID) && $d->ID!="")
			$ID=$d->ID;
			else
				$ID=0;

			$price=array();
			$ServiceCode=array();
			$ItemID=array();
			$Duration=array();
			$Cost=array();
			$Active=array();
			$querydata=array();
			 $querydata['Under'] = $d->Under;
                    $querydata['UnitID'] = $d->UnitID;
                    $querydata['Rating'] = isset($d->Rating)?$d->Rating:'';
                    $querydata['Model'] = isset($d->Model)?$d->Model:'';
                    $querydata['Make']=isset($d->Make)?$d->Make:'';
                    $querydata['Name'] = isset($d->Name)?$d->Name:'';
         $querydata['Name']=str_replace("_"," ",$querydata['Name']);
                 /*   $querydata['ShowInPurchases']=$d->ShowInPurchases;
                    $querydata['ShowInSales']=$d->ShowInSales;
                    $querydata['IsFixedAsset']=$d->IsFixedAsset;
                    $querydata['ShowInStock']=$d->ShowInStock;*/
                    $price[0]=$d->Price1;
 /*                   $price[1]=$d->Price2;
                    $price[2]=$d->Price3;
                    $price[3]=$d->Price4;
                    $price[4]=$d->Price5;
                    $price[5]=$d->Price6;
                    $price[6]=$d->Price7;*/

                    $ServiceCode[0]=$d->ServiceCode1;
/*                    $ServiceCode[1]=$d->ServiceCode2;
                    $ServiceCode[2]=$d->ServiceCode3;
                    $ServiceCode[3]=$d->ServiceCode4;
                    $ServiceCode[4]=$d->ServiceCode5;
                    $ServiceCode[5]=$d->ServiceCode6;
                    $ServiceCode[6]=$d->ServiceCode7;

*/
					$Duration[0]=$d->Duration1;
/*                    $Duration[1]=$d->Duration2;
                    $Duration[2]=$d->Duration3;
                    $Duration[3]=$d->Duration4;
                    $Duration[4]=$d->Duration5;
                    $Duration[5]=$d->Duration6;
                    $Duration[6]=$d->Duration7;
*/

					$ItemID[0]=$d->ItemID1;
/*                    $ItemID[1]=$d->ItemID2;
                    $ItemID[2]=$d->ItemID3;
                    $ItemID[3]=$d->ItemID4;
                    $ItemID[4]=$d->ItemID5;
                    $ItemID[5]=$d->ItemID6;
                    $ItemID[6]=$d->ItemID7;

*/					$Cost[0]=$d->Cost1;
/*                    $Cost[1]=$d->Cost2;
                    $Cost[2]=$d->Cost3;
                    $Cost[3]=$d->Cost4;
                    $Cost[4]=$d->Cost5;
                    $Cost[5]=$d->Cost6;
                    $Cost[6]=$d->Cost7;
*/
                    $Active[0]=isset($d->Active1)?$d->Active1:0;
/*                    $Active[1]=isset($d->Active2)?$d->Active2:0;
                    $Active[2]=isset($d->Active3)?$d->Active3:0;
                    $Active[3]=isset($d->Active4)?$d->Active4:0;
                    $Active[4]=isset($d->Active5)?$d->Active5:0;
                    $Active[5]=isset($d->Active6)?$d->Active6:0;
                    $Active[6]=isset($d->Active7)?$d->Active7:0;
*/                   // $AdditionalInfo1 = txtCode.val();
                   // $Barcode =  txtBarcode.val();
                    $querydata['IsNonStockAble'] =  0;//$d->IsNonStockAble;
                    $querydata['MinPriceLevel'] = $d->MinPriceLevel;
                    $querydata['CommisionPercent'] = $d->CommissionPercent;
                    $querydata['CommisionAmount'] = $d->CommissionAmount;
                    $querydata['MinQtyLevel'] = $d->MinQtyLevel;
                    //$querydata['MaxQtyLevel'] = $d->MaxQtyLevel;
                    $querydata['AdditionalInfo2'] = $d->AdditionalInfo2;
                    //$querydata['AdditionalInfo3'] = $d->AdditionalInfo3;                    
                //    $querydata['AdditionalInfo5'] = $d->AdditionalInfo5;
                    $querydata['CompanyID'] = $d->CompanyID;
                   // $querydata['Attr1'] = $d->Attr1;
                    $querydata['Details'] = $d->Details;
                    $querydata['InitiateDate'] = $d->InitiateDate;
                    $querydata['VATPercent'] = $d->VATPercent;
                    $querydata['IsNonStockAble'] = 0;//$d->IsNonStockAble;
                    $querydata['ProductBaseType']=$d->ProductBaseType;
                    $fieldsname="";
                    $values="";
                     $sql="select * from inventory where ID=".$ID;
                    $result=odbc_exec($connection,$sql);
                 	if(odbc_num_rows($result)>0)
                 	{
					//update code here
                 		$updatedata="";
                 				foreach ($querydata as $key => $value) 
                 				{
		

   if($key!="SideBarID" &&  $key!="VehicleTypeDetail" && $key=!"Barcode" && $key!="ID" && $key!="InitiateDate" && $key!="LastModificationTimeStamp")
									{

										if(getDataType($connection,"inventory",$key)==1)
										{

											$values="'".$value."',";
										}
										else
										{
											if($value=="")$value="0";
										$values ="".$value.",";	
										}
										

									$updatedata.=$key."=".$values;
									}
				
                 				}
                 				$updatedata=substr($updatedata,0,strlen($updatedata)-1);
                 		for($a=0;$a<1;$a++)
                 		{
                            if($updatedata!="")
                 			$queryupdate=",StandardPrice2=".$price[$a].",StandardPrice1=".$Cost[$a].",StandardPrice3=".$Duration[$a].",Active=".$Active[$a].",MaxQtyLevel=".($a+1);
                                else 
    $queryupdate="StandardPrice2=".$price[$a].",StandardPrice1=".$Cost[$a].",StandardPrice3=".$Duration[$a].",Active=".$Active[$a].",MaxQtyLevel=".($a+1);                                        
                 		  $sql="update inventory set $updatedata $queryupdate where ID=".$ItemID[$a];
                 		odbc_exec($connection, $sql) or die(odbc_error().$sql);

                 		}
                 	}// end query if for update query    
                    else
                    {

$values="";
                    //code for insert 
			foreach ($querydata as $key => $value) {
				# code...
				if($key!="SideBarID" &&  $key!="VehicleTypeDetail")
				{
				$fieldsname.=$key.",";

					if(getDataType($connection,"inventory",$key)==1)
					{

                            $value=str_replace("_"," ",$value);
						$values.="'".$value."',";
					}
					else
					{
						if($value=="")$value="0";
					$values .="".$value.",";	
					}
				}
				
			}
			$ID=getMaxId("inventory","ID",$connection)+1;
			$fieldsname=substr($fieldsname,0,strlen($fieldsname)-1);
			$values=substr($values,0,strlen($values)-1);
				for($a=0;$a<1;$a++)
                 		{
         $queryfield="StandardPrice2,StandardPrice1,StandardPrice3,Barcode,Active,MaxQtyLevel";
         $queryvalue=$price[$a].",".$Cost[$a].",".$Duration[$a].",'".$ServiceCode[$a]."',".$Active[$a].",".($a+1);
                 	/*	 $sql="update inventory set $updatedata $queryupdate where ID=".$ItemID[$a];
                 		*/
  $num=generateCode("inventory","count(*)","ProductBaseType='Service' and AdditionalInfo1 is not null group by AdditionalInfo1",$connection);
                 	$num= str_pad($num, 5, "0", STR_PAD_LEFT);  // produces 

                 	$AdditionalInfo1='SVC-'.$num;
			$ID=getMaxId("inventory","ID",$connection)+1;
		   $sql="insert into inventory (ID,Degree,[group],KeyField,AdditionalInfo1,$fieldsname,$queryfield) values($ID,2,0,'$AdditionalInfo1','$AdditionalInfo1',$values,$queryvalue)";
         
                 		odbc_exec($connection, $sql) or die(odbc_error().$sql);
                 		}
				}//end else for insert query 
			//	echo $sql;
			//odbc_exec($connection, $sql);
if (!odbc_error())
          {
          odbc_commit($connection);
$res=array('status'=>"ok",'description'=>'Data Inserted Sucessfully');           }
        else
        {
      odbc_rollback($connection);
      $res=array('status'=>"Error",'description'=>'Record  Not Inserted');

        }                
		//$res=array('status'=>"ok",'description'=>'Data Saved Sucessfully');
		echo json_encode($res);//implode(",",$res);		
		}
function getDataType($conn,$table,$field)
{
	$data=array();
   $sql="select data_type from information_schema.columns where table_name='$table' and column_name='$field'";
$result=odbc_exec($conn,$sql);
if(odbc_num_rows($result)>0)
{
  $data=odbc_fetch_array($result);

//print_r($data);
if($data['data_type']=="varchar" || $data['data_type']=="nvarchar" || $data['data_type']=="time" || $data['data_type']=="datetime" || $data['data_type']=="date" || $data['data_type']=="smalldatetime" || $data['data_type']=="time")
{
		return true;
} 
else 
	{
		return false;
	}
}
else
{
	return false;
} //echo json_encode($data);// print_r($data); 

}
?>