Dispatch Report As On
01. Todays Dispatch
| Product |
Company Target |
Today Dispatch |
Up to Dispatch |
POsition Tar/ Disp |
Positon Tar/Disp % |
Order Status |
| App |
Pen |
Unapp |
G.Total |
execSelectQuery($sql);
$sqltd = "SELECT sum(A.DespatchedQty) as DespatchedQty From so_expdet_magazine_item A JOIN so_expdet B ON A.IdExpDet= B.IdExpDet JOIN
so_expdet_magazine C ON A.IdExpDet= C.IdExpdet and A.MagazineCd=C.MagazineCd WHERE A.ItemCd IN ($ItemCd)
and StatusCd IN ('C', 'B') and DATE(C.SAPInvDate)='$curdate'" ;
$TodayDisp = $objSolarCustomer->execSelectQuery($sqltd);
$sqluptd = "SELECT sum(A.DespatchedQty) as DespatchedQty From so_expdet_magazine_item A JOIN so_expdet B ON A.IdExpDet= B.IdExpDet JOIN
so_expdet_magazine C ON A.IdExpDet= C.IdExpdet and A.MagazineCd=C.MagazineCd WHERE A.ItemCd IN ($ItemCd)
and StatusCd IN ('C', 'B') and DATE(C.SAPInvDate) between '$first_day_this_month' and '$last_day_this_month'" ;
$UptoDisp = $objSolarCustomer->execSelectQuery($sqluptd);
$sqlosa = "SELECT sum(A.OrderQty) as OrderQty From so_expdet_magazine_item A JOIN so_expdet B ON A.IdExpDet= B.IdExpDet
WHERE A.ItemCd IN ($ItemCd) and StatusCd IN ('A') " ;
$OrderApp = $objSolarCustomer->execSelectQuery($sqlosa);
$sqlosp = "SELECT sum(A.OrderQty) as OrderQty From so_expdet_magazine_item A JOIN so_expdet B ON A.IdExpDet= B.IdExpDet
WHERE A.ItemCd IN ($ItemCd) and StatusCd IN ('W') " ;
$OrderPending = $objSolarCustomer->execSelectQuery($sqlosp);
$sqlosu = "SELECT sum(A.OrderQty) as OrderQty From so_expdet_magazine_item A JOIN so_expdet B ON A.IdExpDet= B.IdExpDet
WHERE A.ItemCd IN ($ItemCd) and StatusCd IN ('U')" ;
$OrderUnapp = $objSolarCustomer->execSelectQuery($sqlosu);
$comtarget=$compTarget[0]['TargetQty'];
$todaydis=($TodayDisp[0]['DespatchedQty'])/$Divide;
$uptodis=($UptoDisp[0]['DespatchedQty'])/$Divide;
$position= -($comtarget/$totdays)*$uptoday+$uptodis;
$positionper=($position*100)/$comtarget;
$OrderStatusApp=$OrderApp[0]['OrderQty']/$Divide;
$OrderStatusPending=$OrderPending[0]['OrderQty']/$Divide;
$OrderStatusUnApp=$OrderUnapp[0]['OrderQty']/$Divide;
$Gtotal = $OrderStatusApp +$OrderStatusPending + $OrderStatusUnApp;
if($preitemtype!='' && $curitemtype!=$preitemtype){
?>
| Tot Exp |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
execSelectQuery($sqltd);
$sqluptd = "SELECT sum(TotalMtr) as DespatchedQty From so_accessory WHERE AccessoryCtgCd IN ('7', '6') and StatusCd IN ('S', 'A')
and DATE(SAPInvDate) between '$first_day_this_month' and '$last_day_this_month'";
$UptoDisp = $objSolarCustomer->execSelectQuery($sqluptd);
$todaydis=($TodayDisp[0]['DespatchedQty'])/$Divide;
$uptodis=($UptoDisp[0]['DespatchedQty'])/$Divide;
$position= -($comtarget/$totdays)*$uptoday+$uptodis;
$positionper=($position*100)/$comtarget;
}
?>
|
|
|
|
|
|
|
|
|
|
02. Pending van detail
| Van |
Total |
Dispatch |
Pending |
execSelectQuery($sqlpv);
// $sqldv = "SELECT count(distinct(A.IdVan)) as novansdis FROM
// (SELECT A.* FROM scm_solar_sc.vm_gate_tran A INNER JOIN
// (SELECT A.IdVan, MAX(A.DateTime) AS DateTime FROM scm_solar_sc.vm_gate_tran A WHERE A.PlantCd=1 GROUP BY A.IdVan ) B
// ON A.IdVan=B.IdVan AND A.DateTime=B.DateTime ) A
// LEFT OUTER JOIN scm_solar_sc.vm_gate_tran_customer B ON A.GateCd=B.GateCd AND A.IsGateIn=B.IsGateIn AND A.GateTranNo=B.GateTranNo
// LEFT OUTER JOIN scm_solar_sc.vm_van_status_m D ON A.VanStatusCd=D.VanStatusCd
// INNER JOIN scm_solar_sc.vm_gate_m E ON A.GateCd=E.GateCd AND E.PlantCd=1
// WHERE (!D.IsFinal OR DATE_FORMAT(A.DateTime,'%d-%m-%Y')=DATE_FORMAT(NOW(),'%d-%m-%Y')) and A.SectorCd='T' and A.VanStatusCd IN ('3', '4','9')";
// $noofvansdis = $objSolarCustomer->execSelectQuery($sqldv);
$sqldv = "SELECT * FROM so_comparty_van_dispatch_plan WHERE DispatchPlanDate= '$curdate'";
$noofvansdis = $objSolarCustomer->execSelectQuery($sqldv);
$vanspen= $noofvanspen[0]['novanspen'];
$vansdis= $noofvansdis[0]['PartyVan'];
$totalvan=$vansdis+$vanspen;
// $sqlcomPending= "SELECT count(distinct(VehicleNo)) as ComPendingvan FROM `so_expdet` WHERE `StatusCd`='A' and `DespatchMode`='F'";
// $ComPending = $objSolarCustomer->execSelectQuery($sqlcomPending);
/* $sqlcomDisp= "SELECT count(distinct(A.IdVan)) as ComDispatchvan FROM
(SELECT A.* FROM scm_solar_sc.vm_gate_tran A INNER JOIN
(SELECT A.IdVan, MAX(A.DateTime) AS DateTime FROM scm_solar_sc.vm_gate_tran A WHERE A.PlantCd=1 GROUP BY A.IdVan ) B
ON A.IdVan=B.IdVan AND A.DateTime=B.DateTime ) A
LEFT OUTER JOIN scm_solar_sc.vm_gate_tran_customer B ON A.GateCd=B.GateCd AND A.IsGateIn=B.IsGateIn AND A.GateTranNo=B.GateTranNo
LEFT OUTER JOIN scm_solar_sc.vm_van_status_m D ON A.VanStatusCd=D.VanStatusCd
INNER JOIN scm_solar_sc.vm_gate_m E ON A.GateCd=E.GateCd AND E.PlantCd=1
INNER JOIN scm_solar_sc.vm_van_m F ON A.IdVan=F.IdVan AND F.TransporterTypeCd=1
WHERE (!D.IsFinal OR DATE_FORMAT(A.DateTime,'%d-%m-%Y')=DATE_FORMAT(NOW(),'%d-%m-%Y')) AND F.TransporterTypeCd=1 and A.SectorCd='T' and A.VanStatusCd IN ('3', '4','9')";
$ComDispatch = $objSolarCustomer->execSelectQuery($sqlcomDisp);
*/
// $sqlcomDisp= "SELECT count(Distinct(A.VehicleNo)) AS `ComDispatchvan` FROM so_expdet A
// INNER JOIN so_vehicle_m B ON A.VehicleNo=B.VehicleNo
// INNER JOIN so_customer_m C ON A.CustomerCd=C.CustomerCd
// WHERE (B.IsDummyCompanyVan=1 OR B.CustomerCd='1800') AND C.SectorCd='T'
/// AND A.DespatchMode='F' AND A.StatusCd='A'";
// $ComDispatch = $objSolarCustomer->execSelectQuery($sqlcomDisp);
// $comdisp= $ComDispatch[0]['ComDispatchvan'];
//$compen= $ComPending[0]['ComPendingvan'];
$comdisp= $noofvansdis[0]['CompanyVan'];
$totalcom= $noofvansdis[0]['TotalCompanyVanProg'];
$compen=$totalcom-$comdisp;
//$totalcom= $compen+$comdisp;
?>
| Party |
|
|
|
| Company |
|
|
|
03. AED Sold and Stock position
| Com. |
SIIL |
EEL |
execSelectQuery($sqlaeds);
$SAED = isset($queryaeds[0]['Stock']) ? (($queryaeds[0]['Stock'])*1500)/100000 : 0;
$sqlaed30 = "SELECT SUM(B.Stock) as Stock From scm_solar_sc.scm_stock_sales A JOIN
scm_solar_sc.scm_stock_sales_item B ON A.StockSalesID= B.StockSalesID WHERE A.CompanyCd='1800' and DateAdded='$curdate' and SectorCd='T' and B.ItemCd IN ('20005315')" ;
$queryaed30 = $objSolarCustomer->execSelectQuery($sqlaed30);
$SAED30 = isset($queryaed30[0]['Stock']) ? (($queryaed30[0]['Stock'])*1000)/100000 : 0;
$SAED = $SAED + $SAED30;
$sqleed = "SELECT SUM(B.Stock) as Stock From scm_solar_sc.scm_stock_sales A JOIN scm_solar_sc.scm_stock_sales_item B ON A.StockSalesID= B.StockSalesID
WHERE A.CompanyCd='1100' and DateAdded='$curdate' and SectorCd='T' and B.ItemCd IN ('20001340', '20001341', '20009157')" ;
$queryeed = $objSolarCustomer->execSelectQuery($sqleed);
$SEED = isset($queryeed[0]['Stock']) ? (($queryeed[0]['Stock'])*1500)/100000 : 0;
$sqleed30 = "SELECT SUM(B.Stock) as Stock From scm_solar_sc.scm_stock_sales A JOIN scm_solar_sc.scm_stock_sales_item B ON A.StockSalesID= B.StockSalesID
WHERE A.CompanyCd='1100' and DateAdded='$curdate' and SectorCd='T' and B.ItemCd IN ('20002672')" ;
$queryeed30 = $objSolarCustomer->execSelectQuery($sqleed30);
$SEED30 = isset($queryeed30[0]['Stock']) ? (($queryeed30[0]['Stock'])*1000)/100000 : 0;
$SEED = $SEED+$SEED30;
$sqlA1 = "SELECT Sum(A.OrderQty) as A1 From so_expdet_magazine_item A JOIN
so_expdet B ON A.IdExpDet= B.IdExpdet WHERE A.ItemCd IN ('20000120', '20000121', '20005315') and StatusCd IN ('A', 'W', 'U')" ;
$queryArrA1 = $objSolarCustomer->execSelectQuery($sqlA1);
$siilsold= ($queryArrA1[0]['A1'])/100000;
$sqlA2 = "SELECT Sum(OrderQty) as A2 From so_expdet_magazine_item A JOIN
so_expdet B ON A.IdExpDet= B.IdExpdet WHERE A.ItemCd IN('20001340', '20001341', '20002672', '20009157') and StatusCd IN ('A', 'W', 'U')" ;
$queryArrA2 = $objSolarCustomer->execSelectQuery($sqlA2);
$eelsold= ($queryArrA2[0]['A2'])/100000;
$sqlTDS = "SELECT Sum(A.OrderQty) as A1 From so_expdet_magazine_item A JOIN so_expdet B
ON A.IdExpDet= B.IdExpdet JOIN so_expdet_magazine C ON A.IdExpDet = C.IdExpDet AND A.MagazineCd = C.MagazineCd
WHERE A.ItemCd IN ('20000120', '20000121', '20005315') and B.StatusCd IN ('C') AND C.SAPInvDate = '$curdate'" ;
$queryArrTDS = $objSolarCustomer->execSelectQuery($sqlTDS);
$siilTodayDisp= ($queryArrTDS[0]['A1'])/100000;
$sqlTDE = "SELECT Sum(A.OrderQty) as A1 From so_expdet_magazine_item A JOIN so_expdet B
ON A.IdExpDet= B.IdExpdet JOIN so_expdet_magazine C ON A.IdExpDet = C.IdExpDet AND A.MagazineCd = C.MagazineCd
WHERE A.ItemCd IN ('20001340', '20001341', '20002672', '20009157') and B.StatusCd IN ('C') AND C.SAPInvDate = '$curdate'" ;
$queryArrTDE = $objSolarCustomer->execSelectQuery($sqlTDE);
$eelTodayDisp= ($queryArrTDE[0]['A1'])/100000;
?>
| Stock |
|
|
| Today Disp |
|
|
| Sold |
|
|
| Position |
|
|
| G. Position |
|
execSelectQuery($sqlvanplanning);
$date= $query[0]['DateTime'];
$customer= $query[0]['Descript'];
$State= $query[0]['StateCd'];
$VanNo= $query[0]['VanNo'];
$date= date('d-m-Y', strtotime($date));
?>
Tomorrow First van- Dated - ".$customer .", ". $State ." Vehicle No. ". $VanNo; ?>
04. State wise Pending vans
| State |
No of Vans |
".$ProductName ."";
}
}
?>
execSelectQuery($scmstate);
foreach($statelist as $state){
$statecd=$state['StateCd'];
$sqlwaiting = "SELECT count(distinct(A.IdVan)) as novanspen FROM
(SELECT A.* FROM scm_solar_sc.vm_gate_tran A INNER JOIN
(SELECT A.IdVan, MAX(A.DateTime) AS DateTime FROM scm_solar_sc.vm_gate_tran A WHERE A.PlantCd=1 GROUP BY A.IdVan ) B
ON A.IdVan=B.IdVan AND A.DateTime=B.DateTime ) A
LEFT OUTER JOIN scm_solar_sc.vm_gate_tran_customer B ON A.GateCd=B.GateCd AND A.IsGateIn=B.IsGateIn AND A.GateTranNo=B.GateTranNo AND A.SectorCd= B.SectorCd
LEFT OUTER JOIN scm_solar_sc.scm_party_m C ON B.CustomerCd= C.PartyCd
LEFT OUTER JOIN scm_solar_sc.vm_van_status_m D ON A.VanStatusCd=D.VanStatusCd
INNER JOIN scm_solar_sc.vm_gate_m E ON A.GateCd=E.GateCd AND E.PlantCd=1
WHERE (!D.IsFinal OR DATE_FORMAT(A.DateTime,'%d-%m-%Y')=DATE_FORMAT(NOW(),'%d-%m-%Y')) and A.SectorCd='T' and A.GateCd='1' and A.VanStatusCd='2' and C.StateCd='$statecd'";
$waitingvans = $objSolarCustomer->execSelectQuery($sqlwaiting);
$pendvans= $waitingvans[0]['novanspen'];
$totvans=$pendvans+$totvans;
$sqlwaitingPO = "SELECT distinct(B.PONo) FROM
(SELECT A.* FROM scm_solar_sc.vm_gate_tran A INNER JOIN
(SELECT A.IdVan, MAX(A.DateTime) AS DateTime FROM scm_solar_sc.vm_gate_tran A WHERE A.PlantCd=1 GROUP BY A.IdVan ) B
ON A.IdVan=B.IdVan AND A.DateTime=B.DateTime ) A
LEFT OUTER JOIN scm_solar_sc.vm_gate_tran_customer B ON A.GateCd=B.GateCd AND A.IsGateIn=B.IsGateIn AND A.GateTranNo=B.GateTranNo AND A.SectorCd= B.SectorCd
LEFT OUTER JOIN scm_solar_sc.scm_party_m C ON B.CustomerCd= C.PartyCd
LEFT OUTER JOIN scm_solar_sc.vm_van_status_m D ON A.VanStatusCd=D.VanStatusCd
INNER JOIN scm_solar_sc.vm_gate_m E ON A.GateCd=E.GateCd AND E.PlantCd=1
WHERE (!D.IsFinal OR DATE_FORMAT(A.DateTime,'%d-%m-%Y')=DATE_FORMAT(NOW(),'%d-%m-%Y')) and A.SectorCd='T' and A.GateCd='1' and A.VanStatusCd='2' and C.StateCd='$statecd'";
$waitingvanspo = $objSolarCustomer->execSelectQuery($sqlwaitingPO);
$statepolist='';
foreach($waitingvanspo as $POlist) {
$PONo= $POlist['PONo'];
$statepolist = $PONo.", ".$statepolist;
}
$statepolist=substr($statepolist,0, -2);
?>
|
|
execSelectQuery($sqlcountPO);
$OrderQty= $orderboxpo[0]['OrderQty']/$Divide;
if($ItemType =='Deto')
{
$OrderQty = sprintf('%.3f', $OrderQty);
}
$StateProdSum = 'Tot_'.$ProductId;
$$StateProdSum += $OrderQty;
if($ProductId !='7'){
echo "".$OrderQty ." | ";
}
}
?>
| Total Van |
|
".$$TotalProd."";
}
$ProductId++;
}
?>
05. State wise todays dispatch
execSelectQuery($droptemp);
$createtemp = "CREATE TABLE dispreport (StateCd varchar(5))";
$temptable = $objSolarCustomer->execSelectQuery($createtemp);
$insertdisprep= "INSERT INTO dispreport (StateCd) SELECT Distinct(D.StateCd) as StateCd From so_expdet_magazine_item A JOIN so_expdet B ON A.IdExpDet = B.IdExpDet JOIN so_expdet_magazine C ON A.IdExpDet = C.IdExpDet
JOIN so_customer_m D ON D.CustomerCd= B.CustomerCd WHERE A.ItemCd IN ($itemCdList) and DATE(C.SAPInvDate)='$curdate'";
$list = $objSolarCustomer->execUpdateQuery($insertdisprep);
$sqlstate = " INSERT INTO dispreport (StateCd) SELECT Distinct(B.StateCd) as StateCd From so_accessory A JOIN so_customer_m B ON A.CustomerCd = B.CustomerCd
WHERE A.AccessoryCtgCd IN ('7', '6') and StatusCd IN ('A', 'S') and DATE(A.SAPInvDate)='$curdate'";
$liststate = $objSolarCustomer->execUpdateQuery($sqlstate);
// $sqlstate ="select distinct(StateCd) as StateCd from dispreport A JOIN so_state_m B ON A.StateCd = B.StateCd order by B.DispOrder ASC";
$sqlstate ="select distinct(A.StateCd) as StateCd from dispreport A JOIN so_state_m B ON A.StateCd= B.StateCd where 1 order By B.DispOrder ";
$listofstate = $objSolarCustomer->execSelectQuery($sqlstate);
?>
| Product |
".$StateCdlist['StateCd']."";
}
?>
Total |
CUMU |
AVG |
execSelectQuery($sqluptd);
$uptodis=$UptoDisp[0]['DespatchedQty']/$Divide;
$avg= $uptodis/$uptoday;
if($preitemtype!='' && $curitemtype!=$preitemtype){
?>
| Tot Exp |
execSelectQuery($sqltot);
echo "".$stateDisptot[0]['DespatchedQty']." | ";
}
?>
|
|
|
|
execSelectQuery($sqlstd);
$todaydisst=$TodayDispSt[0]['DespatchedQty']/$Divide;
$totalstatedis=$totalstatedis+$todaydisst;
?>
|
|
|
|
|
execSelectQuery($sqlstd);
if($POType==1){
$sqlstd = "SELECT sum(A.TotalMtr) as DespatchedQty From so_accessory A JOIN so_customer_m B ON A.CustomerCd= B.CustomerCd
WHERE A.AccessoryCtgCd IN ('7', '6') and StatusCd IN ('A', 'S') and DATE(A.SAPInvDate)='$curdate' and B.StateCd='$StateCd'" ;
$TodayDispSt = $objSolarCustomer->execSelectQuery($sqlstd);
}
$todaydisst=$TodayDispSt[0]['DespatchedQty']/$Divide;
$totalstatedis=$totalstatedis+$todaydisst;
?>
|
|
|
|
06. State wise cumulative Sale
execSelectQuery($droptemp);
$createtemp = "CREATE TABLE dispreport1 (StateCd varchar(5))";
$temptable = $objSolarCustomer->execSelectQuery($createtemp);
$insertdisprep= "INSERT INTO dispreport1 (StateCd) SELECT Distinct(D.StateCd) as StateCd From so_expdet_magazine_item A JOIN so_expdet B ON A.IdExpDet = B.IdExpDet JOIN so_expdet_magazine C ON A.IdExpDet = C.IdExpDet
JOIN so_customer_m D ON D.CustomerCd= B.CustomerCd WHERE A.ItemCd IN ($itemCdList) and DATE(C.SAPInvDate) between '$first_day_this_month' and '$last_day_this_month' ";
$list = $objSolarCustomer->execUpdateQuery($insertdisprep);
$sqlstate = " INSERT INTO dispreport1 (StateCd) SELECT Distinct(B.StateCd) as StateCd From so_accessory A JOIN so_customer_m B ON A.CustomerCd = B.CustomerCd
WHERE A.AccessoryCtgCd IN ('7', '6') and StatusCd IN ('A', 'S') ";
$liststate = $objSolarCustomer->execUpdateQuery($sqlstate);
$sqlstate ="select distinct(A.StateCd) as StateCd from dispreport1 A JOIN so_state_m B ON A.StateCd= B.StateCd Where 1 order By B.DispOrder ";
$listofstate = $objSolarCustomer->execSelectQuery($sqlstate);
?>
| Product |
".$StateCdlist['StateCd']."";
}
?>
Total |
execSelectQuery($sqluptd);
$uptodis=($UptoDisp[0]['DespatchedQty'])/$Divide;
$avg= $uptodis/$uptoday;
if($preitemtype!='' && $curitemtype!=$preitemtype){
?>
| Tot Exp |
execSelectQuery($sqltot);
echo "".sprintf('%.3f',$stateDisptot[0]["DespatchedQty"])." | ";
}
?>
|
|
execSelectQuery($sqlstd);
$todaydisst=$TodayDispSt[0]['DespatchedQty']/$Divide;
$totalstatedis=$totalstatedis+$todaydisst;
?>
|
|
|
execSelectQuery($sqlstd);
if($POType==1){
$sqlstd = "SELECT sum(A.TotalMtr) as DespatchedQty From so_accessory A JOIN so_customer_m B ON A.CustomerCd= B.CustomerCd
WHERE A.AccessoryCtgCd IN ('7', '6') and StatusCd IN ('A', 'S') and B.StateCd='$StateCd' and DATE(SAPInvDate) between '$first_day_this_month' and '$last_day_this_month'" ;
$TodayDispSt = $objSolarCustomer->execSelectQuery($sqlstd);
}
$todaydisst=$TodayDispSt[0]['DespatchedQty']/$Divide;
$totalstatedis=$totalstatedis+$todaydisst;
?>
|
|
|