01. Todays Dispatch

'; $preitemtype=''; $curitemtype=''; $totcomtarget=$tottodaytarget=$totuptodistarget=$totposition=$totpositionper=0; $totOrderStatusApp=$totOrderStatusPen=$totOrderStatusUnApp= $GtotalorderstatusExp=0; $itemCdList=''; foreach($listData as $curRow) { $ProductId= $curRow['ProductId']; $ItemCd= $curRow['ItemCd']; $itemCdList= $itemCdList.",".$ItemCd; $POType= $curRow['POType']; $Divide= $curRow['DividedBy']; $curitemtype =$curRow['ItemType']; //echo $ItemCd; $sql = "SELECT TargetQty From so_company_target where ProductId=$ProductId and year='$curYear' and month='$curLogMonth'"; $compTarget = $objSolarCustomer->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){ $html_message .=''; }else{ if($POType==1){ $sqltd = "SELECT sum(TotalMtr) as DespatchedQty From so_accessory WHERE AccessoryCtgCd IN ('7', '6') and StatusCd IN ('S', 'A') and DATE(SAPInvDate)='$curdate'" ; $TodayDisp = $objSolarCustomer->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; } $html_message .=''; } $totcomtarget=$totcomtarget+$comtarget; $tottodaytarget=$tottodaytarget+$todaydis; $totuptodistarget=$totuptodistarget+$uptodis; $totposition=$totposition+$position; $totpositionper=$totpositionper+$positionper; $totOrderStatusApp=$totOrderStatusApp+$OrderStatusApp; $totOrderStatusPen=$totOrderStatusPen+$OrderStatusPending; $totOrderStatusUnApp=$totOrderStatusUnApp+$OrderStatusUnApp; $GtotalorderstatusExp= $totOrderStatusApp+$totOrderStatusPen +$totOrderStatusUnApp; $preitemtype=$curitemtype; } $html_message .='

Product	Company Target	Today Dispatch	Up to Dispatch	POsition Tar/ Disp	Positon Tar/Disp %	Order Status
						App	Pen	Unapp	G.Total
Total Explo	'. sprintf("%.3f", $totcomtarget).'	'. sprintf("%.3f", $tottodaytarget).'	'. sprintf("%.3f", $totuptodistarget).'	'. sprintf("%.2f", $totposition).'	'. sprintf("%.2f", $totpositionper).'	'. sprintf("%.3f", $totOrderStatusApp) .'	'. sprintf("%.3f", $totOrderStatusPen) .'	'. sprintf("%.3f", $totOrderStatusUnApp) .'	'. sprintf("%.3f", $GtotalorderstatusExp).'
'. $curRow["ProductName"].'	'. sprintf("%.3f", $comtarget).'	'. sprintf("%.3f", $todaydis).'	'. sprintf("%.3f", $uptodis).'	'. sprintf("%.2f", $position).'	'. sprintf("%.2f", $positionper).'	'. sprintf("%.3f", $OrderStatusApp) .'	'. sprintf("%.3f", $OrderStatusPending) .'	'. sprintf("%.3f", $OrderStatusUnApp) .'	'. sprintf("%.3f", $Gtotal) .'
'. $curRow["ProductName"].'	'. sprintf("%.3f", $comtarget).'	'. sprintf("%.3f", $todaydis).'	'. sprintf("%.3f", $uptodis).'	'. sprintf("%.2f", $position).'	'. sprintf("%.2f", $positionper).'	'. sprintf("%.3f", $OrderStatusApp) .'	'. sprintf("%.3f", $OrderStatusPending) .'	'. sprintf("%.3f", $OrderStatusUnApp) .'	'. sprintf("%.3f", $Gtotal) .'

02. Pending van detail

'; $totalvan=0; $sqlpv = "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 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' order by A.DateTime"; $noofvanspen = $objSolarCustomer->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.GateCd='1' and A.VanStatusCd IN ('3', '4','9') " ; // $noofvansdis = $objSolarCustomer->execSelectQuery($sqldv); // $vansdis= $noofvansdis[0]['novansdis']; $vanspen= $noofvanspen[0]['novanspen']; $sqldv = "SELECT * FROM so_comparty_van_dispatch_plan WHERE DispatchPlanDate= '$curdate'"; $noofvansdis = $objSolarCustomer->execSelectQuery($sqldv); $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.VehicleNo)) as ComDispatchvan FROM `so_expdet` A JOIN so_expdet_magazine B WHERE A.StatusCd='C' //and A.DespatchMode='F' and B.SAPInvDate= '$curdate'"; // $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 A.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; $html_message .=''; $html_message .='

Van	Total	Dispatch	Pending
Party	'. $totalvan .'	'. $vansdis .'	'. $vanspen .'
Company	'. $totalcom .'	'. $comdisp .'	'. $compen .'

03. AED Sold and Stock position

'; $sqlaeds = "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 ('20000120', '20000121')" ; $queryaeds = $objSolarCustomer->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; $html_message .='

Com.	SIIL	EEL
Stock	'. sprintf("%.3f", $SAED) .'	'. sprintf("%.3f", $SEED) .'
Today Disp	'. sprintf("%.3f", $siilTodayDisp) .'	'. sprintf("%.3f", $eelTodayDisp) .'
Sold	'. sprintf("%.3f", $siilsold) .'	'. sprintf("%.3f", $eelsold) .'
Position	'. sprintf("%.3f", $possiil= $SAED-$siilTodayDisp-$siilsold) .'	'. sprintf("%.3f", $poseel=$SEED-$eelTodayDisp-$eelsold) .'
G. Position	'. sprintf("%.3f", $Gposition= $possiil+$poseel) .'

'; $sqlvanplanning = "SELECT A.DateTime, G.Descript, F.VanNo, G.StateCd 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 INNER JOIN scm_solar_sc.scm_party_m G ON B.CustomerCd=G.PartyCd 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' order by A.DateTime ASC Limit 1" ; $query = $objSolarCustomer->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)); $html_message .='Tomorrow First van- Dated -'.$date.', '.$customer .', '. $State .' Vehicle No.'. $VanNo .'

04. State wise Pending vans

'; foreach($listData as $curRow) { $ProductName = $curRow['ProductName']; $ProductId = $curRow['ProductId']; if($ProductId !='7'){ $html_message .=''; } } $html_message .=''; $totvans=0; $scmstate = "SELECT distinct(C.StateCd) as StateCd 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.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 JOIN b2b_solar_sc.so_state_m F ON C.StateCd = F.StateCd 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!='' order by F.DispOrder, A.DateTime"; $statelist = $objSolarCustomer->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); $html_message .= ''; $StateProdSum = 0; foreach($listData as $curRow) { $ItemCd = $curRow['ItemCd']; $ProductId = $curRow['ProductId']; $Divide= $curRow['DividedBy']; $ItemType= $curRow['ItemType']; $sqlcountPO = "SELECT sum(OrderQty)as OrderQty FROM so_expdet_magazine_item where IdExpDet IN ($statepolist) and ItemCd IN ($ItemCd)"; $orderboxpo = $objSolarCustomer->execSelectQuery($sqlcountPO); $OrderQty= $orderboxpo[0]['OrderQty']/$Divide; if($ItemType =='Deto') { $OrderQty = sprintf('%.3f', $OrderQty); } $StateProdSum = 'Tot_'.$ProductId; $$StateProdSum += $OrderQty; if($ProductId !='7'){ $html_message .= ''; } } $html_message .= ''; } $html_message .= ''; foreach($listData as $curRow) { $ItemCd = $curRow['ItemCd']; $ProductId = $curRow['ProductId']; $TotalProd = 'Tot_'.$ProductId; if($ProductId!='7'){ $html_message .= ''; } $ProductId++; } $html_message .= '

State	No of Vans	'.$ProductName .'
'.$statecd .'	'.$pendvans .'	'.$OrderQty .'
Total Van	'.$totvans.'	'.$$TotalProd.'

05. State wise todays dispatch

'; $itemCdList= substr(trim($itemCdList), '1'); $droptemp = "DROP TABLE IF EXISTS dispreport"; $droptemptable = $objSolarCustomer->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"; $listofstate = $objSolarCustomer->execSelectQuery($sqlstate); $html_message .=''; foreach($listofstate as $StateCdlist) { $html_message .=''; } $html_message .=''; $preitemtype=''; $tottar=$totcumutar=0; $Exploitemlist=''; foreach($listData as $curRow) { $ProductId= $curRow['ProductId']; $ItemCd= $curRow['ItemCd']; $POType= $curRow['POType']; $Divide= $curRow['DividedBy']; if($curRow['ItemType']=='Explo'){ $Exploitemlist=$Exploitemlist.",".$ItemCd; } $itemCdList= $itemCdList.",".$ItemCd; $curitemtype =$curRow['ItemType']; $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'" ; if($POType=='1'){ $sqluptd = "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) between '$first_day_this_month' and '$last_day_this_month' " ; } $UptoDisp = $objSolarCustomer->execSelectQuery($sqluptd); $uptodis=$UptoDisp[0]['DespatchedQty']/$Divide; $avg= $uptodis/$uptoday; if($preitemtype!='' && $curitemtype!=$preitemtype){ $html_message .=''; $Exploitemlist= substr(trim($Exploitemlist), '1'); foreach($listofstate as $StateCdlist) { $StateCd=$StateCdlist['StateCd']; $sqltot = "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 JOIN so_customer_m D ON D.CustomerCd= B.CustomerCd WHERE A.ItemCd IN ($Exploitemlist) and StatusCd IN ('C', 'B') and DATE(C.SAPInvDate)='$curdate' and D.StateCd='$StateCd'" ; $stateDisptot = $objSolarCustomer->execSelectQuery($sqltot); $html_message .=''; } $html_message .=''; $totalstatedis=0; foreach($listofstate as $StateCdlist) { $StateCd=$StateCdlist['StateCd']; $sqlstd = "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 JOIN so_customer_m D ON D.CustomerCd= B.CustomerCd WHERE A.ItemCd IN ($ItemCd) and StatusCd IN ('C', 'B') and DATE(C.SAPInvDate)='$curdate' and D.StateCd='$StateCd'" ; $TodayDispSt = $objSolarCustomer->execSelectQuery($sqlstd); $todaydisst=$TodayDispSt[0]['DespatchedQty']/$Divide; $totalstatedis=$totalstatedis+$todaydisst; $html_message .=''; } if($totalstatedis!=0){ $b= sprintf("%.3f", $totalstatedis); } else { $b=""; } if($uptodis!=0){ $c= sprintf('%.3f', $uptodis); } else { $c= ""; } $html_message .=''; $preitemtype= $curitemtype; }else{ $html_message .=' '; $totalstatedis=0; foreach($listofstate as $StateCdlist) { $StateCd=$StateCdlist['StateCd']; $sqlstd = "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 JOIN so_customer_m D ON D.CustomerCd= B.CustomerCd WHERE A.ItemCd IN ($ItemCd) and StatusCd IN ('C', 'B') and DATE(C.SAPInvDate)='$curdate' and D.StateCd='$StateCd'" ; $TodayDispSt = $objSolarCustomer->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; $html_message .=' '; } if($totalstatedis!=0){ $d= sprintf("%.3f", $totalstatedis); } else { $d=""; } if($uptodis!=0){ $e= sprintf("%.3f", $uptodis); } else { $e=""; } $html_message .=''; } $tottar=$tottar+$totalstatedis; $totcumutar=$totcumutar+$uptodis; $preitemtype=$curitemtype; } $html_message .='

Product	'.$StateCdlist["StateCd"].'	Total	CUMU	AVG
Total Explo	'.$stateDisptot[0]["DespatchedQty"].'	'.sprintf("%.3f", $tottar) .'	'. sprintf("%.3f", $totcumutar).'
'.$curRow["ProductName"] .'	'. $todaydisst .'	'.$b.'	'.$c.'	'.sprintf("%.3f", $avg) .'
'. $curRow["ProductName"] .'	'. $todaydisst .'	'.$d.'	'.$e.'	'. sprintf("%.3f", $avg) .'

06. State wise cumulative Sale

'; $itemCdList= substr(trim($itemCdList), '1'); $droptemp = "DROP TABLE IF EXISTS dispreport1"; $droptemptable = $objSolarCustomer->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(StateCd) as StateCd from dispreport1"; $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); $html_message .=''; foreach($listofstate as $StateCdlist) { $html_message .=''; } $html_message .=''; $preitemtype=''; $tottar=$totcumutar=0; $Exploitemlist=''; foreach($listData as $curRow) { $ProductId= $curRow['ProductId']; $ItemCd= $curRow['ItemCd']; $POType= $curRow['POType']; $Divide= $curRow['DividedBy']; if($curRow['ItemType']=='Explo'){ $Exploitemlist=$Exploitemlist.",".$ItemCd; } $itemCdList= $itemCdList.",".$ItemCd; $curitemtype =$curRow['ItemType']; $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'" ; if($POType=='1'){ $sqluptd = "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(SAPInvDate) between '$first_day_this_month' and '$last_day_this_month'" ; } $UptoDisp = $objSolarCustomer->execSelectQuery($sqluptd); $uptodis=($UptoDisp[0]['DespatchedQty'])/$Divide; $avg= $uptodis/$uptoday; if($preitemtype!='' && $curitemtype!=$preitemtype){ $html_message .=''; $Exploitemlist= substr(trim($Exploitemlist), '1'); foreach($listofstate as $StateCdlist) { $StateCd=$StateCdlist['StateCd']; $sqltot = "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 JOIN so_customer_m D ON D.CustomerCd= B.CustomerCd WHERE A.ItemCd IN ($Exploitemlist) and StatusCd IN ('C', 'B') and D.StateCd='$StateCd' and DATE(C.SAPInvDate) between '$first_day_this_month' and '$last_day_this_month'" ; $stateDisptot = $objSolarCustomer->execSelectQuery($sqltot); $html_message .=''; } $html_message .=''; $totalstatedis=0; foreach($listofstate as $StateCdlist) { $StateCd=$StateCdlist['StateCd']; $sqlstd = "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 JOIN so_customer_m D ON D.CustomerCd= B.CustomerCd WHERE A.ItemCd IN ($ItemCd) and StatusCd IN ('C', 'B') and D.StateCd='$StateCd' and D.StateCd='$StateCd' and DATE(C.SAPInvDate) between '$first_day_this_month' and '$last_day_this_month'" ; $TodayDispSt = $objSolarCustomer->execSelectQuery($sqlstd); $todaydisst=$TodayDispSt[0]['DespatchedQty']/$Divide; $totalstatedis=$totalstatedis+$todaydisst; $html_message .=''; } if($totalstatedis!=0){ $q= sprintf("%.3f", $totalstatedis); } else { $q=''; } $html_message .=''; $preitemtype= $curitemtype; }else{ $html_message .=''; $totalstatedis=0; foreach($listofstate as $StateCdlist) { $StateCd=$StateCdlist['StateCd']; $sqlstd = "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 JOIN so_customer_m D ON D.CustomerCd= B.CustomerCd WHERE A.ItemCd IN ($ItemCd) and StatusCd IN ('C', 'B') and D.StateCd='$StateCd' and D.StateCd='$StateCd' and DATE(C.SAPInvDate) between '$first_day_this_month' and '$last_day_this_month'" ; $TodayDispSt = $objSolarCustomer->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; $html_message .=''; } if($totalstatedis!=0){ $w=sprintf("%.3f", $totalstatedis); } else { $w=''; } $html_message .=''; } $tottar=$tottar+$totalstatedis; $totcumutar=$totcumutar+$uptodis; $preitemtype=$curitemtype; } $html_message .='

Product	'.$StateCdlist["StateCd"].'	Total
Tot Exp	'.sprintf("%.3f", $stateDisptot[0]["DespatchedQty"]).'	'.sprintf("%.3f", $tottar).'
'. $curRow["ProductName"] .'	'.sprintf("%.3f", $todaydisst).'	'. $q .'
'.$curRow["ProductName"].'	'.$todaydisst.'	'.$w.'

'; //echo $html_message; $mpdf->AddPages('P'); $mpdf->setHTMLFooter('') ; $mpdf->setHTMLFooter('','E') ; $mpdf->WriteHTML($html_message); $filename = "DailyDispatchReport_"; $Date = date('d-m-Y'); $filename = $filename.$Date.'.pdf'; $content = $mpdf->Output("../upload/DailyDispatchReport/$filename", "F"); $mailer = new Swift_Mailer(new Swift_MailTransport()); // Create new instance of SwiftMailer $message = Swift_Message::newInstance() ->setSubject('Daily Dispatch Report Report of '.$Date) // Message subject ->setTo(array('rahul@solargroup.com' => 'Rahul Sir', 'kcn@solargroup.com' => 'kcn@solargroup.com', 'r.somani@solargroup.com' => 'R.Somaniji' ,'m.bhalerao@solargroup.com' => 'M.Bhaleraoji', 'tsm3@solargroup.com' => 'A.MandowaraJi', 'bhl3@solargroup.com' => 'Surbhi', 'bhl2@solargroup.com' => 'Arpit' ,'solartrade@solargroup.com' => 'Solar Trade', 'dd@solargroup.com' => 'Dispatch Dept', 'rp.somani@solargroup.com' => 'RP SomaniJi', 'websupport@solargroup.com' => 'Web Support', )) // Array of people to send to //->setTo(array('websupport@solargroup.com' => 'Atul Gupta')) // Array of people to send to ->setFrom(array('b2b.solarexplosives@gmail.com' => 'Admin@B2B.SolarExplosives')) // From: ->setBody($html_message, 'text/html');// Attach that HTML message from earlier // ->attach(Swift_Attachment::newInstance('', '', '')); // Attach the generated PDF from earlier // Send the email, and show user message if ($mailer->send($message)) { // echo $html_message; echo ""; }else { // echo $html_message; echo ""; } // $sendmail=$classBusiLogic->senddailydispatchmail($html_message); // if($sendmail){ // echo ""; // } // else{ // echo ""; // } ?>