<?php
require "showshp.php";
session_start();
if(!isset($_SESSION['titles']) || !isset($_SESSION['positions'])|| !isset($_SESSION['usernames'])){
header("location:index.php");
}

if(!isset($_REQUEST['sprftypes'])){
echo "no list";}
else{
$sprftypes=$_REQUEST['sprftypes'];

 
$conn=mysqli_connect("localhost","primarysales","S5L7lF01EQZvQrGK","primarysales_diana");

if(isset($_REQUEST['zones'])){
$zones=$_REQUEST['zones'];}
else{
$zones="All";}

if(isset($_REQUEST['regions'])){
$regions=$_REQUEST['regions'];}
else{
$regions="All";}

if(isset($_REQUEST['sub_regions'])){
$regions=$_REQUEST['sub_regions'];}
else{
$sub_regions="All";}

if(isset($_REQUEST['territorys'])){
$territorys=$_REQUEST['territorys'];}
else{
$territorys="All";}

if(isset($_REQUEST['agents'])){
$agents=$_REQUEST['agents'];}
else{
$agents="All";}

if(isset($_REQUEST['rsms'])){
$rsms=$_REQUEST['rsms'];}
else{
$rsms="All";}

if(isset($_REQUEST['executives'])){
$executives=$_REQUEST['executives'];}
else{
$executives="All";}

if(isset($_REQUEST['reps'])){
$reps=$_REQUEST['reps'];}
else{
$reps="All";}

if(isset($_REQUEST['ranges'])){
$ranges=$_REQUEST['ranges'];}
else{
$ranges="All";}

if(isset($_REQUEST['subranges'])){
$subranges=$_REQUEST['subranges'];}
else{
$subranges="All";}

if(isset($_REQUEST['items'])){
$items=$_REQUEST['items'];}
else{
$items="All";}



$seflt=" where agent_name is not null ";
$sesqlst="select * from acs where user_names='".$_SESSION['usernames']."'";
$seqry=mysqli_query($conn,$sesqlst);

while($serst=mysqli_fetch_row($seqry)){
if(strtolower($serst[1])!='all'){
$seflt=$seflt." and zone='".$serst[1]."' ";}
if(strtolower($serst[2])!='all'){
$seflt=$seflt." and region='".$serst[2]."' ";}

//if(strtolower($serst[3])!='all'){
//$seflt=$seflt." and sub_regions='".$serst[3]."' ";}


if(strtolower($serst[4])!='all'){
$seflt=$seflt." and territory_name='".$serst[4]."' ";}
if(strtolower($serst[5])!='all'){
$seflt=$seflt." and agent_name='".$serst[5]."' ";}
if(strtolower($serst[11])!='all'){
$seflt=$seflt." and rsm_name='".$serst[11]."' ";}

if(strtolower($serst[9])!='all'){
$seflt=$seflt." and executive_name='".$serst[9]."' ";}


#if(strtolower($serst[5])!='all'){
#$seflt=$seflt." and item_name='".$serst[5]."' ";}
#if(strtolower($serst[6])!='all'){
#$seflt=$seflt." and item_range='".$serst[6]."' ";}
#if(strtolower($serst[7])!='all'){
#$seflt=$seflt." and sub_range='".$serst[7]."' ";}
#if(strtolower($serst[8])!='all'){
#$seflt=$seflt." and executive_name='".$serst[8]."' ";}
}

if($sprftypes=="zones")
{
$sqlst="select distinct zone from agentsmrd ".$selft;
$qry=mysqli_query($conn,$sqlst);

$sts="";

while($rst=mysqli_fetch_row($qry)){
$sts=$sts.$rst[0]."###";
}
echo $sts;
}


if($sprftypes=="regions")
{
$sqlst="select distinct region from agentsmrd ".$seflt;
$qry=mysqli_query($conn,$sqlst);

$sts="";

while($rst=mysqli_fetch_row($qry)){
$sts=$sts.$rst[0]."###";
}
echo $sts;
}

if($sprftypes=="subregions")
{
$sqlst="select distinct sub_region from agentsmrd ".$seflt;
$qry=mysqli_query($conn,$sqlst);

$sts="";

while($rst=mysqli_fetch_row($qry)){
$sts=$sts.$rst[0]."###";
}
echo $sts;
}



if($sprftypes=="agents")
{
$sqlst="select distinct agent_name from agentsmrd ".$seflt;
$qry=mysqli_query($conn,$sqlst);

$sts="";

while($rst=mysqli_fetch_row($qry)){
$sts=$sts.$rst[0]."###";
}
echo $sts;
}


if($sprftypes=="territorys")
{
$sqlst="select distinct territory_name from agentsmrd ".$seflt;
$qry=mysqli_query($conn,$sqlst);

$sts="";

while($rst=mysqli_fetch_row($qry)){
$sts=$sts.$rst[0]."###";
}
echo $sts;
}


if($sprftypes=="itemprf")
{
if($_REQUEST['sgtypes']=='marketreturns'){
$sqlst="select distinct item_name_mrt,retail_price from itemsmrd";}
//else{
//$sqlst="select distinct item_name_mrt,whole_sale_price/packets_per_box from //itemsmrd ";}

$qry=mysqli_query($conn,$sqlst);

$sts="";

while($rst=mysqli_fetch_row($qry)){
$sts=$sts.$rst[0]."###".number_format($rst[1],2,'.','')."<td>";
}
echo $sts;
}


if($sprftypes=="itemprff")
{
if($_REQUEST['sgtypes']=='marketreturns'){
$sqlst="select distinct item_name,whole_sale_price/(packets_per_box*pcs_per_packet) from freeitems ";}

$qry=mysqli_query($conn,$sqlst);

$sts="";

while($rst=mysqli_fetch_row($qry)){
$sts=$sts.$rst[0]."###".number_format($rst[1],2,'.','')."<td>";
}
echo $sts;
}

if($sprftypes=="dbsales")
{
$sqlst="select distinct custname,custarea,custtype,custroute,custaddres,custtp,zone,region,agent_name,agent_type,territory_name from customer ".$seflt." order by custname";
$qry=mysqli_query($conn,$sqlst);


$sts="";

while($rst=mysqli_fetch_row($qry)){
$sts=$sts.$rst[0]."###".$rst[1]."###".$rst[2]."###".$rst[3]."###".$rst[4]."###".$rst[5]."###".$rst[6]."###".$rst[7]."###".$rst[8]."###".$rst[9]."###".$rst[10]."<td>";
}
echo $sts;
}




if($sprftypes=="daysales")
{
$sqlst="select distinct zone,region,rsm_name,agent_replc,agent_type,territory_name,rep_name from agentsmrd ".$seflt." and rep_name<>'DEACTIVE' order by region,territory_name,agent_name";
$qry=mysqli_query($conn,$sqlst);


$sts="";

while($rst=mysqli_fetch_row($qry)){
$sts=$sts.$rst[0]."###".$rst[1]."###".$rst[2]."###".$rst[3]."###".$rst[4]."###".$rst[5]."###".$rst[6]."<td>";
}
echo $sts;
}


if($sprftypes=="mrsales")
{
$sqlst="select distinct zone,region,rsm_name,agent_replcnames,agent_type,territory_name,agent_name from agentsmrd ".$seflt." order by region,territory_name,agent_replc";
$qry=mysqli_query($conn,$sqlst);


$sts="";

while($rst=mysqli_fetch_row($qry)){
$sts=$sts.$rst[0]."###".$rst[1]."###".$rst[2]."###".$rst[3]."###".$rst[4]."###".$rst[5]."###".$rst[6]."<td>";
}
echo $sts;
}

if($sprftypes=="dsrsales")
{
$sqlst="select distinct zone,region,rsm_name,agent_replcnames,agent_type,territory_name,rep_name,sub_region from agentsmrd ".$seflt." and rep_name<>'DEACTIVE'order by region,territory_name,agent_name";
$qry=mysqli_query($conn,$sqlst);


$sts="";

while($rst=mysqli_fetch_row($qry)){
$sts=$sts.$rst[0]."###".$rst[1]."###".$rst[2]."###".$rst[3]."###".$rst[4]."###".$rst[5]."###".$rst[6]."###".$rst[7]."<td>";
}
echo $sts;
}


if($sprftypes=="grnsl")
{
$sqlst="select distinct stores,location from warehouse order by location";
$qry=mysqli_query($conn,$sqlst);


$sts="";

while($rst=mysqli_fetch_row($qry)){
$sts=$sts.$rst[0]."###".$rst[1]."<td>";
}
echo $sts;
}





}



if($sprftypes=="naddagents")
{

$nstarts=$_REQUEST['nstarts'];
$nends=$_REQUEST['nends'];

$sqlst="select pradmrq from acs where user_names='".$_SESSION['usernames']."' ";
$qry=mysqli_query($conn,$sqlst);
if(mysqli_num_rows($qry)==0){
echo "Access denied";}
else{
$rst=mysqli_fetch_row($qry);
if($rst[0]==""){
echo "Access denied";}
if($rst[0]>=2){

$sqlst="insert into agentsmrd (zone_order,zone,region,sub_region,rsm_name,agent_name,agent_type,range_type,territory_name,user_names,user_dates) values (".$_REQUEST['nzoneorders'].",'".$_REQUEST['nzones']."','".$_REQUEST['nregions']."','".$_REQUEST['nsubregions']."','".$_REQUEST['nrsms']."','";
$sqlst=$sqlst.$_REQUEST['nagents']."','".$_REQUEST['nagenttypes']."','".$_REQUEST['nrangetypes']."','".$_REQUEST['nterritorys']."','".$_SESSION['usernames']."',now())";
#echo $sqlst;

if($qry=mysqli_query($conn,$sqlst)==true){
if($nstarts!=0){
$sqlst="update rc_invoices set zone_orders=".$_REQUEST['nzoneorders'].",zones='".$_REQUEST['nzones']."',regions='".$_REQUEST['nregions']."',sub_regions='".$_REQUEST['nsubregions']."',rsm_names='".$_REQUEST['nrsms'];
$sqlst=$sqlst."',agent_types='".$_REQUEST['agenttypes']."',range_types='".$_REQUEST['nrangetypes']."',territory_names='".$_REQUEST['nterritorys']."' where agent_names='".$_REQUEST['nagents']."' and dates>='".$nstarts."' and dates<='".$nends."' ";
$qry=mysqli_query($conn,$sqlst);
}

$sqlst="select title_names,positions from sprdusers where usernames='".$_SESSION['usernames']."' ";
$qry=mysqli_query($conn,$sqlst);
$rst=mysqli_fetch_row($qry);

date_default_timezone_set ("Asia/Colombo");

$msgs=$msgs."<p style='font-family:calibri;font-size:12;'>Sales web program-Auto generated email: New agent creation</p>";
$msgs=$msgs."<table style='font-family:calibri;font-size:12;width:70%%;'><th style='background:silver;' colspan='2'>New agent creation</th>";
$msgs=$msgs."<tr style='background:silver'><td style='border:1px solid silver;width:50%%;' >Agent name</td><td style='border:1px solid silver;width:50%%;' >".$_REQUEST['nagents']."</td></tr>";
$msgs=$msgs."<tr style='border:1px solid silver;'><td style='border:1px solid silver;' >Zone order</td><td style='border:1px solid silver;' >".$_REQUEST['nzoneorders']."</td></tr>";
$msgs=$msgs."<tr style='border:1px solid silver;'><td style='border:1px solid silver;' >Zone</td><td style='border:1px solid silver;' >".$_REQUEST['nzones']."</td></tr>";
$msgs=$msgs."<tr style='border:1px solid silver;'><td style='border:1px solid silver;' >Region</td><td style='border:1px solid silver;' >".$_REQUEST['nregions']."</td></tr>";
$msgs=$msgs."<tr style='border:1px solid silver;'><td style='border:1px solid silver;' >Sub region</td><td style='border:1px solid silver;' >".$_REQUEST['nsubregions']."</td></tr>";
$msgs=$msgs."<tr style='border:1px solid silver;'><td style='border:1px solid silver;' >RSM</td><td style='border:1px solid silver;' >".$_REQUEST['nrsms']."</td></tr>";
$msgs=$msgs."<tr style='border:1px solid silver;'><td style='border:1px solid silver;' >Agent type</td><td style='border:1px solid silver;' >".$_REQUEST['nagenttypes']."</td></tr>";
$msgs=$msgs."<tr style='border:1px solid silver;'><td style='border:1px solid silver;' >Range type</td><td style='border:1px solid silver;' >".$_REQUEST['nrangetypes']."</td></tr>";
$msgs=$msgs."<tr style='border:1px solid silver;'><td style='border:1px solid silver;' >Territory name</td><td style='border:1px solid silver;' >".$_REQUEST['nterritorys']."</td></tr>";
$msgs=$msgs."<tr style='border:1px solid silver;'><td style='border:1px solid silver;' colspan='2'>".date("Y-M-d D H:i")."</td></tr>";
$msgs=$msgs."<tr style='border:1px solid silver;'><td style='border:1px solid silver;' colspan='2'>".$rst[0]." - ".$rst[1]."</td></tr>";
$msgs=$msgs."</table>";
$msgs=$msgs."<p style='font-family:calibri;font-size:12;'>Contact Sales and marketing department regarding any issues regarding this new agency information shown above.</p>";

$hdrs = "Content-type: text/html\r\n";
$hdrs .= 'To: pathmasiri@diana.lk' . "\r\n";
$hdrs .= 'From: Sales web program' . "\r\n";
$hdrs .= 'Cc: pathmasiri@diana.lk' . "\r\n";

mail("prasanna@diana.lk","Sale web program: New agent creation",$msgs,$hdrs);
echo "Adding successful";}
else{
echo "Adding error";}

}

}
}


if($sprftypes=="nadditems")
{

$nstarts=$_REQUEST['nstarts'];
$nends=$_REQUEST['nends'];

$sqlst="select pradmrq from acs where user_names='".$_SESSION['usernames']."' ";
$qry=mysqli_query($conn,$sqlst);
if(mysqli_num_rows($qry)==0){
echo "Access denied";}
else{
$rst=mysqli_fetch_row($qry);
if($rst[0]==""){
echo "Access denied";}
if($rst[0]>=2){

$sqlst="insert into itemsmrd (item_ranges,sub_ranges,packets_per_box,rd_uom,packet_weight,item_name,retail_price,whole_sale_price,discount_rates,mdiscount_rates,stock_type,fg_type,item_status,user_names,user_dates) values ('".$_REQUEST['nranges']."','".$_REQUEST['nsubranges']."',".$_REQUEST['npackets'].",".$_REQUEST['npackets'].",".$_REQUEST['nweights'].",'";
$sqlst=$sqlst.$_REQUEST['nitems']."',".$_REQUEST['nretails'].",".($_REQUEST['nws']*$_REQUEST['npackets']).",".$_REQUEST['ndsrates'].",".$_REQUEST['nmrrates'].",'FG','".$_REQUEST['nranges']."','ACTIVE','".$_SESSION['usernames']."',now())";
#echo $sqlst;

if($qry=mysqli_query($conn,$sqlst)==true){
if($nstarts!=0){
$sqlst="update rc_invoices set item_ranges=".$_REQUEST['nranges'].",sub_ranges='".$_REQUEST['nsubranges']."',packets_per_box='".$_REQUEST['npackets']."',packet_weights='".$_REQUEST['npackets'];
$qry=mysqli_query($conn,$sqlst);
}

$sqlst="select title_names,positions from sprdusers where usernames='".$_SESSION['usernames']."' ";
$qry=mysqli_query($conn,$sqlst);
$rst=mysqli_fetch_row($qry);

date_default_timezone_set ("Asia/Colombo");

$msgs=$msgs."<p style='font-family:calibri;font-size:12;'>Sales web program-Auto generated email: New item creation</p>";
$msgs=$msgs."<table style='font-family:calibri;font-size:12;width:70%%;'><th style='background:silver;' colspan='2'>New item creation</th>";
$msgs=$msgs."<tr style='background:silver'><td style='border:1px solid silver;width:50%%;' >Item name</td><td style='border:1px solid silver;width:50%%;' >".$_REQUEST['nitems']."</td></tr>";
$msgs=$msgs."<tr style='border:1px solid silver;'><td style='border:1px solid silver;' >Item range</td><td style='border:1px solid silver;' >".$_REQUEST['nranges']."</td></tr>";
$msgs=$msgs."<tr style='border:1px solid silver;'><td style='border:1px solid silver;' >Sub range</td><td style='border:1px solid silver;' >".$_REQUEST['nsubranges']."</td></tr>";
$msgs=$msgs."<tr style='border:1px solid silver;'><td style='border:1px solid silver;' >Packets per box</td><td style='border:1px solid silver;' >".number_format($_REQUEST['npackets'],2,'.',',')."</td></tr>";
$msgs=$msgs."<tr style='border:1px solid silver;'><td style='border:1px solid silver;' >Packet weight(g)</td><td style='border:1px solid silver;' >".number_format($_REQUEST['nweights'],2,'.',',')."</td></tr>";
$msgs=$msgs."<tr style='border:1px solid silver;'><td style='border:1px solid silver;' >Retail price</td><td style='border:1px solid silver;' >".number_format($_REQUEST['nretails'],2,'.',',')."</td></tr>";
$msgs=$msgs."<tr style='border:1px solid silver;'><td style='border:1px solid silver;' >W/S price</td><td style='border:1px solid silver;' >".number_format($_REQUEST['nws'],2,'.',',')."</td></tr>";
$msgs=$msgs."<tr style='border:1px solid silver;'><td style='border:1px solid silver;' colspan='2'>".date("Y-M-d D H:i")."</td></tr>";
$msgs=$msgs."<tr style='border:1px solid silver;'><td style='border:1px solid silver;' colspan='2'>".$rst[0]." - ".$rst[1]."</td></tr>";
$msgs=$msgs."</table>";
$msgs=$msgs."<p style='font-family:calibri;font-size:12;'>Contact Sales and marketing department regarding any issues regarding this new item information shown above.</p>";

$hdrs = "Content-type: text/html\r\n";
$hdrs .= 'To:asela@diana.lk,prasanna@diana.lk,gajaneswary@diana.lk,karthik@diana.lk,dilrukshi@diana.lk,thilak@diana.lk,buddika@diana.lk,chamara@diana.lk,prasath@diana.lk,lalith.hemantha@diana.lk' . "\r\n";
$hdrs .= 'From: Sales web program' . "\r\n";
$hdrs .= 'Cc: pathmasiri@diana.lk,sudath@diana.lk,hazari@diana.lk,finance@diana.lk,roshan.r@diana.lk' . "\r\n";

mail("prasanna@diana.lk","Sale web program: New item creation",$msgs,$hdrs);

echo "Adding successful";}
else{
echo "Adding error";}

}

}
}
?>