<?php
/* $Revision: 1.73 $ */
$PageSecurity = 11;
$title = _('Show Client');
include('includes/SQL_CommonFunctions.inc');
$vendorgrouparray=array("C"=>"Contractor","M"=>"Manufactures","T"=>"Transporter","D"=>"Distributor","R"=>"Retailer","S"=>"Service Provider","B"=>"Both");
if(isset($_REQUEST['impsave']))
{ 
	if($_FILES['imprt']['type']!='')
	{
                $allowedExts = array("csv");
                $temp = explode(".", $_FILES["imprt"]["name"]);
                $extension=$temp[1];
                if(in_array($extension, $allowedExts))
                 {
		DB_query("TRUNCATE TABLE vendor_temp",$db,$ErrMsg,$DbgMsg);
		$infile=addslashes($_FILES['imprt']['tmp_name']);	

		  $sql="LOAD DATA LOCAL INFILE '$infile' INTO TABLE `vendor_temp` FIELDS TERMINATED BY ',' ENCLOSED BY '".'"'."'
		   LINES TERMINATED BY '\n'   IGNORE 1 LINES;";
		DB_query($sql,$db,$ErrMsg,$DbgMsg);
            
               $qryin = "select * from vendor_temp";
		$qryinp = DB_query($qryin,$db,$ErrMsg,$DbgMsg);
		 $num=DB_num_rows($qryinp);
		while($qryrow=DB_fetch_array($qryinp))
		{                
     $qryinc = "select id,vendor_name from suppliers a where a.vendor_name='".htmlspecialchars($qryrow['vendor_name'], ENT_QUOTES)."'
                and site='".$_REQUEST['Site']."'";
     //and a.client_code='".htmlspecialchars($qryrow['client_code'], ENT_QUOTES)."'
 
			//echo $qryinc; exit();
            $qryinpc = DB_query($qryinc,$db);
			if(DB_num_rows($qryinpc)>0)
			{
                            
			//echo $qryrow['c_name'].'--------';
			//echo $id;
				//continue;
			}
         else
            {				
				// insert Country1
				$chkCountry = "select id from country_master where country='".htmlspecialchars($qryrow['country'], ENT_QUOTES)."'";
				$chkCountryExec = DB_query($chkCountry,$db);
				if(DB_num_rows($chkCountryExec)==0)
				{
					$insert_cntry="Insert into country_master set country='".htmlspecialchars($qryrow['country'], ENT_QUOTES)."'";  
					if($execQryCntry=DB_query($insert_cntry,$db)) 
					{
					$lastidCntry=DB_Last_Insert_ID($db,'country_master','id');
					}
				}
				else
				{
				$CountryData = DB_fetch_array($chkCountryExec);
				$lastidCntry= $CountryData['id'];
				}
                                
               
				// insert State1
				$chkState = "select id from state_master where state='".htmlspecialchars($qryrow['state'], ENT_QUOTES)."'";
				$chkStateExec = DB_query($chkState,$db);
				if(DB_num_rows($chkStateExec)==0)
				{
					$insert_state="Insert into state_master set state='".htmlspecialchars($qryrow['state'], ENT_QUOTES)."', country='".$lastidCntry."'";  
					if($execQryState=DB_query($insert_state,$db)) 
					{
					$lastidState=DB_Last_Insert_ID($db,'state_master','id');
					}
				}
				else
				{
				$StateData = DB_fetch_array($chkStateExec);
				$lastidState= $StateData['id'];
				}
                                
                
				
				// insert City1
				$chkCity = "select id from city_master where city='".htmlspecialchars($qryrow['city'], ENT_QUOTES)."'";
				$chkCityExec = DB_query($chkCity,$db);
				if(DB_num_rows($chkCityExec)==0)
				{
					$insert_city="Insert into city_master set country='".$lastidCntry."', city='".htmlspecialchars($qryrow['city'], ENT_QUOTES)."', state='".$lastidState."'";  
					if($execQryCity=DB_query($insert_city,$db)) 
					{
					$lastidCity=DB_Last_Insert_ID($db,'city_master','id');
					}
				}
				else
				{
				$CityData = DB_fetch_array($chkCityExec);
				$lastidCity= $CityData['id'];
				}



    $vendor_prefix = "NCI/".strtoupper(substr($_REQUEST['mem_type'],0,1))."/";
	$category_id = $_REQUEST['ccategory'];
	$query = "Select name from category_master where id = '".$category_id."'";
	$res = DB_query($query,$db) or die(DB_error());
	$result = DB_fetch_array($res);
	$vendor_name = explode(' ',$result['name']);
	$arr = array();
	for($i=0;$i<count($vendor_name);$i++)
	{
		$arr[] = strtoupper(substr($vendor_name[$i],0,1));
	}
	$category_prefix = implode("",$arr)."/";
	
	//echo "NCI/".$vendor_prefix.$category_prefix;
	 $query1 = "select vendor_code from suppliers where vendor_type='".$_REQUEST['mem_type']."' and category = '".$_REQUEST['ccategory']."' order by id desc limit 0,1";
	$res1 = DB_query($query1,$db) or die(DB_error());
	if(DB_num_rows($res1)>0)
	{
		$result1 = DB_fetch_array($res1);
		$v_type = explode('-',$result1['vendor_code']);
				$postfix = $v_type[3] + 1;	
		$final_postfix = $vendor_prefix.$category_prefix.str_pad($postfix,4,"0",STR_PAD_LEFT);
		$vendor_code = str_replace('/','-',$final_postfix);
	}
	else
	{
		$final_postfix = $vendor_prefix.$category_prefix."0001";
		$vendor_code = str_replace('/','-',$final_postfix);
	}





				    $sql1 = "INSERT suppliers SET
						 `site`			='".$_REQUEST['Site']."',
						`vendor_name`		='".trim(htmlspecialchars($qryrow['vendor_name'], ENT_QUOTES))."', 
						`vendor_code`		='".$vendor_code."', 
						`short_name`	='".trim(htmlspecialchars($qryrow['short_name'], ENT_QUOTES))."',  
                        `category`		='".$_REQUEST['ccategory']."', 
                        `account_group`		='".$_REQUEST['ledger_id']."',
                         `vendor_type`		='".$_REQUEST['mem_type']."',                                           
						`adrress1`	='".trim(htmlspecialchars($qryrow['address1'], ENT_QUOTES))."',
						`adrress2`	='".trim(htmlspecialchars($qryrow['address2'], ENT_QUOTES))."',
						`address3`	='".trim(htmlspecialchars($qryrow['address3'], ENT_QUOTES))."',
						`city`		='".$lastidCity."', 
                        `state`		='".$lastidState."', 
                        `country`	='".$lastidCntry."',
                        `pan_no` 		= '".trim(htmlspecialchars($qryrow['pan_no'], ENT_QUOTES))."',
                        `tds_tin_no` 		= '".trim(htmlspecialchars($qryrow['tan_no'], ENT_QUOTES))."',
                        `excise_no`     = '".trim(htmlspecialchars($qryrow['ecc_no'], ENT_QUOTES))."',
                        `sup_division`  ='".trim(htmlspecialchars($qryrow['division'], ENT_QUOTES))."',
                         `sup_range`   ='".trim(htmlspecialchars($qryrow['range'], ENT_QUOTES))."', 
                         `tin_no`  ='".trim(htmlspecialchars($qryrow['vat_tin_no'], ENT_QUOTES))."', 
   `tds_v_date`		='".FormatDateForSQL(trim(htmlspecialchars($qryrow['vat_date'], ENT_QUOTES)))."',                          
						  `cst_no`	='".trim(htmlspecialchars($qryrow['cst_no'], ENT_QUOTES))."',
						  `cst_date`	='".trim(htmlspecialchars($qryrow['cst_date'], ENT_QUOTES))."', 
						  `bank_name`	='".trim(htmlspecialchars($qryrow['bank_name'], ENT_QUOTES))."', 
						  `bank_branch`	='".trim(htmlspecialchars($qryrow['bnk_branch'], ENT_QUOTES))."',  
						  `account_no`	='".trim(htmlspecialchars($qryrow['ac_no'], ENT_QUOTES))."', 
						  `rtg_neft_no`	='".trim(htmlspecialchars($qryrow['ifsc_code'], ENT_QUOTES))."', 
						  `performance`	='".trim(htmlspecialchars($qryrow['performance'], ENT_QUOTES))."', 
						  `remarks`	='".trim(htmlspecialchars($qryrow['remarks'], ENT_QUOTES))."', 
						  `CompanyId`		='".$_SESSION['ACGCompanyId']."'"; 						
				$execQry=DB_query($sql1,$db);
				$lastidI=DB_Last_Insert_ID($db,'suppliers',$ErrMsg);	

                     $cont_Person="insert into contact_information_master set 
                             master_code='".$lastidI."',
                             master_prefix='S',
                             person='".trim(htmlspecialchars($qryrow['co_person'], ENT_QUOTES))."',
                             department='".trim(htmlspecialchars($qryrow['department'], ENT_QUOTES))."',
                             designation='".trim(htmlspecialchars($qryrow['designation'], ENT_QUOTES))."',
                             phone='".trim(htmlspecialchars($qryrow['cont_no'], ENT_QUOTES))."',
                             email='".trim(htmlspecialchars($qryrow['email_id'], ENT_QUOTES))."'
                             "; 
                        DB_query($cont_Person,$db);
                        
                   if($lastidI!='')
	               {
		$data['AccountName']=trim(htmlspecialchars($qryrow['vendor_name'], ENT_QUOTES));
		$data['vender_client']=$lastidI;
		$data['UnderGroup']=$_REQUEST['ledger_id'];
		$data['CompanyId']=$_SESSION['ACGCompanyId'];
		$data['openingtype']='CR';
		$data['openingBalance']=trim(htmlspecialchars($qryrow['opening_bal'], ENT_QUOTES));
		$data['openingdate']=date('Y-m-d',strtotime(trim(htmlspecialchars($qryrow['opening_date'], ENT_QUOTES))));
		$data['ledgertype']='vendor';
		$rowinsert= $s->RecordInsert('accountledger', $data);
	                }	// if($lastidI!='')		
			}
         }//if(DB_num_rows($qryinpc)>0)
        
		$mm=DB_query("TRUNCATE TABLE vendor_temp",$db);
		if($mm){
		echo $msg="<br><font color='#009933' size='+1'>Vendor Details had been imported successfully.</font>";
		$s->pageLocation('index.php?route=Supplier_list&modid='.$_REQUEST['modid'].'&frmid='.$_REQUEST['frmid'].'');
		}else{
		echo $msg="<br><font color='#009933' size='+1'>Vendor Details had not been imported successfully.</font>";
		    }  
	  }// if(in_array($extension, $allowedExts))
        else {
   echo $msg="<br><font color='red' size='+1'> Invalid file type.</font>";
            }
 } 
}

function showgroupdata($group)
{
	global $gp;
global $db;
 $sql_sale=DB_query("select GroupId from accountgroupmaster where MainGroupId ='".$group."'",$db);
  while($row=DB_fetch_array($sql_sale))
  {
  $gp[]=$row['GroupId'];
  showgroupdata($row['GroupId']);
  }

  return $gp;

}
  $groupdata_id=$s->getField('accountgroupmaster','GroupName','LIABILITIES','Groupid');
  $sql_sale=DB_query("select GroupId from accountgroupmaster where NatureOfGroup ='".$groupdata_id."'",$db);
  while($row=DB_fetch_array($sql_sale))
  {
  $gp[]=$row['GroupId'];
  $show=showgroupdata($row['GroupId']);
  }
  if(!empty($show) && !empty($gp))
$gp=array_merge($gp,$show);

  if(!empty($gp))
  	$gp1=implode(",",$gp);

?>
<link rel="stylesheet" href="css/new_mxerp.css">
<div class="body_contain">
<div class="tab_div"> 
<div class="slidingDiv1">
<h1>Import Vendor</h1>
		<form method="post" action="" enctype="multipart/form-data" onsubmit="return chkvalidate();" >
			<table width="100%" border="0" cellpadding="3" cellspacing="0">
				<tr class="row1">
					<td width="20%" align="right"><strong>Import File: <font color="#FF0000">*</font></strong></td>
					<td><input type="file" name="imprt" id="imprt" /></td>
				</tr>
                <tr class="row1">
					<td align="right"><strong>Vendor Category: <font color="#FF0000">*</font></strong></td>
					<td><select name="ccategory" id="ccategory" style="width:40%;">
	    <option value="">Select</option>
		<?php  
		$cat_mat=DB_query("select * from category_master where type='V'",$db);
		while($cat_row=DB_fetch_array($cat_mat))
		{
		$s1='';
		if($ccategory==$cat_row['id'])
		$s1="selected";
		else
		$s1='';
		?>
		<option value="<?php echo $cat_row['id']; ?>" <?php echo $s1; ?>  ><?php echo $cat_row['name']; ?></option>
		<?php } ?>
		
		</select></td>
				</tr>  
                <tr class="row1">
					<td align="right"><strong>Branch: <font color="#FF0000">*</font></strong></td>
					<td><select  name="Site" id="Site" style="width:40%;">
	<option value="0">Select</option>
	<?php 

	$sql_site=DB_query("Select loccode, locationname from locations  where CompanyId='".$_SESSION['ACGCompanyId']."' and loccode in('".$_SESSION['AssignedBranch']."')",$db);
	while($row=DB_fetch_array($sql_site))
	{
	?>
	<option value="<?php echo $row['loccode']; ?>" <?php if($Site==$row['loccode']) { ?> selected="selected" <?php } ?>><?php echo $row['locationname']; ?></option>
	<?php } ?>
	</select></td>
				</tr>  
                <tr class="row1">
					<td align="right"><strong>Vendor Type: <font color="#FF0000">*</font></strong></td>
					<td><select name="mem_type" id="mem_type" style="width:40%;">
                <option value="0">Select</option>
				<option Value="Domestic" <?php echo ($vendor_type=='Domestic'?'selected':''); ?>>Domestic</option>
		<option Value="International" <?php echo ($vendor_type=='International'?'selected':''); ?>>International</option>  
   </select></td>
				</tr>
				 <tr class="row1">
					<td align="right"><strong>Vendor Group: <font color="#FF0000">*</font></strong></td>
					<td><select name="mem_group" id="mem_group" style="width:40%;">
                <option value='0'>Select</option>
                             <?php foreach($vendorgrouparray as $key=>$value)
                             {
                             $t='';
                             if($vendorgroup==$key) { $t="selected"; }
                             ?>
                           <option value="<?php echo $key; ?>"<?php echo $t; ?>><?php echo $value; ?></option>
				<?php } ?> 
   </select></td>
				</tr>
				 <tr class="row1">
					<td align="right"><strong>Account Group: <font color="#FF0000">*</font></strong></td>
					<td> <select name="ledger_id" id="ledger_id" style="width:40%;">  
					<option value="">Select</option> 
   <?php 
   	//$acc_sql="Select GroupId,GroupName  from accountgroupmaster where GroupName LIKE '%Debtor%' and MainGroupId='0'";
   $acc_sql="Select GroupId, GroupName from accountgroupmaster
 			 where  GroupId in(".$gp1.")";
  	$acc_res=DB_query($acc_sql,$db);
  	while($acc_row=DB_fetch_array($acc_res))
   {
    ?>
    <option value="<?php echo $acc_row['GroupId'];?>"<?php echo ($ledger_id==$acc_row['GroupId']?'selected':'')?>><?php echo  $acc_row['GroupName'] ?></option>
    <?php
   $account_sql=DB_query("Select GroupId,GroupName from accountgroupmaster where MainGroupId
        = '".$acc_row['GroupId']."'",$db);
   	while($account_res=DB_fetch_array($account_sql))
	{?>
	<option value="<?php echo $account_res['GroupId'];?>"<?php echo ($ledger_id==$account_res['GroupId']?'selected':'')?>><?php echo"--". $account_res['GroupName']?></option>
	<?php 	
   } }
   ?>
   </select> </td>
				</tr>
				<tr class="row1">
					<td colspan="2" align="center" style="text-align:center;"><input type="submit" class="search" name="impsave" value="Upload" /></td>
				</tr>
			</table>
		</form>

</div>
</div>
</div>
<script type="text/javascript">
function chkvalidate()
{
  
  if($('#imprt').val()=='')
  {
  	alert('Please select csv file.');
  	return false;
  }
    if($('#ccategory').val()=='')
    {
    	alert('Please select vendor category.');
    	return false;
    }
      if($('#Site').val()==0)
      {
      	alert('Please select branch.');
      	return false;
      }
       if($('#mem_type').val()==0)
       {
       	alert('Please select vendor type.');
       	return false;
       }
        if($('#mem_group').val()==0)
        {
        	alert('Please select vendor group.');
        	return false;
        }
         if($('#ledger_id').val()=='')
         {
         	alert('Please select account group.');
         	return false;
         }

 return true;
}
</script>