Jump to content

kat35601

Members
 View Profile  See their activity

  • Posts

    182

  • Joined

  • Last visited

 Content Type 

Posts posted by kat35601

    Create a Total Line for each State.

    in PHP Coding Help

    Posted

    In this report I want to create a line break and a Total line for each State.

      

     while ($row = odbc_fetch_array($result))
{
    echo "<tr><td>" .  $row['cmlstate']."</td>";
    echo "<td>" .  $row['cmlpostcode']."</td>";
    echo "<td>" .  $row['cmlcity']."</td>";
    echo "<td>" .  $row['ompSalesOrderID']."</td>";
    echo "<td>" .  $row['cmoName']."</td>";
    echo "<td>" .  number_format($row['UOMPVOLUMETOTAL'],2)."</td>";
    echo "<td>" .  number_format($row['UOMPTOTALBOXCOUNT'],0)."</td>";
    echo "<td>" .  $row['Hold']."</td></tr>"; 
    

I want to sum 'UOMPVOLUMETOTAL' for each state and create a line break between it and the next state.
    
}

     

    phpspreadsheet create a new worksheet on salesperson name change

    in PHP Coding Help

    Posted

    We will us the spreadsheet as statements each month for each Salesperson. The spreedsheet allows the salespeople to filter by item and/or by customer etc...etc.. so they can see what items work good in there area do what if's create there on reports for their sales teams. We send out 60  statements each month. I will add a function to the end of this script to email each Salesperson there spreadsheet automatically each month. I did try create object once and then create the spreadsheets from that but memory was a problem.These spreadsheet can be thousands of rows in size. I would really like to Create spreadsheet  for salesperson 1name and then email it and then create spreadsheet for salesperson 2name and email it and so on. Thanks for you help.

    phpspreadsheet create a new worksheet on salesperson name change

    in PHP Coding Help

    Posted

    Sorry it has taken me so long to get back. I can not get this to work as described above PHPSPREADSHEET does have some memory limitations and trying to do it in one file runs PHPSPREADSHEET out of memory even with the setting set to the max. So I need to create one file per salespersonID but the foreach does not loop through as expected with this code I get a file with the first salespersonID and then it stops.

     

      

    $salesperson=array('1name',
'2name',
'3name',
'4name',
'5name');
foreach($salesperson as $salesId ){

$commission_percent=0;
$price=0;
$commission=0;
//styling arrays end
$exrow=6;
//make a new spreadsheet object
$spreadsheet = new Spreadsheet();
//set default font
$spreadsheet->getDefaultStyle()
   ->getFont()
   ->setName('Arial')
   ->setSize(10);

//heading
$spreadsheet->getActiveSheet()
   ->setCellValue('A1',"Comission");

//merge heading
$spreadsheet->getActiveSheet()->mergeCells("A1:R1");

// set font style
$spreadsheet->getActiveSheet()->getStyle('A1')->getFont()->setSize(20);

// set cell alignment
$spreadsheet->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

//setting column width



//header text//current row
$spreadsheet->getActiveSheet()
   ->setCellValue('A5',"INVOICE DATE")
   ->setCellValue('B5',"SALESPERSON")
   ->setCellValue('C5',"INVOICE ID")
   ->setCellValue('D5',"ORDER ID")
   ->setCellValue('E5',"ORDER DATE")
   ->setCellValue('F5',"CUSTOMER ID")
   ->setCellValue('G5',"NAME")
   ->setCellValue('H5',"GROUP ID")
   ->setCellValue('I5',"STATE")
   ->setCellValue('J5',"QTY")
   ->setCellValue('K5',"PART")
   ->setCellValue('L5',"UOM")
   ->setCellValue('M5',"GROUP")
   ->setCellValue('N5',"GROSS")
   ->setCellValue('O5',"DISCOUNT")
   ->setCellValue('P5',"NET")
   ->setCellValue('Q5',"CommPrcnt")
   ->setCellValue('R5',"Commission");




//set font style and background color
$spreadsheet->getActiveSheet()->getStyle('A5:R5')->applyFromArray($tableHead);
// the connection 
$connect =odbc_connect("removed");
if(!$connect) {
   exit("Connection Failed: " . $connect);
}

//the sql query
$sql="sql statement that gets data for each $salesId ";
//run the query
$result =odbc_exec($connect,$sql);
if(!$result){
exit("Error in SQL");
}

while ($row = odbc_fetch_array($result))
{


$prtcnt=floatval($row['commission_percent']);
$commission_percent=number_format($prtcnt, 2);
$price=floatval($row['ARLEXTENDEDPRICEBASE']);
$commission=$commission_percent*$price;
   $spreadsheet->getActiveSheet()
       ->setCellValue('A'.$exrow ,$row['INVOICEDATE'])
       ->setCellValue('B'.$exrow ,$row['ARJSALESEMPLOYEEID'])
       ->setCellValue('C'.$exrow ,$row['ARPARINVOICEID'])
       ->setCellValue('D'.$exrow ,$row['UARPSALESORDERNUMBER'])
       ->setCellValue('E'.$exrow ,$row['createdDate'])
       ->setCellValue('F'.$exrow ,$row['ARPCUSTOMERORGANIZATIONID'])
       ->setCellValue('G'.$exrow ,$row['CMONAME'])
       ->setCellValue('H'.$exrow ,$row['CMOCUSTOMERGROUPID'])
       ->setCellValue('I'.$exrow ,$row['cmoState'])
       ->setCellValue('J'.$exrow ,$row['ARLINVOICEQUANTITY'])
       ->setCellValue('K'.$exrow ,$row['ARLPARTID'])
       ->setCellValue('L'.$exrow ,$row['ARLUNITOFMEASURE'])
       ->setCellValue('M'.$exrow ,$row['ARLPARTGROUPID'])
       ->setCellValue('N'.$exrow ,$row['ARLFULLEXTENDEDPRICEBASE'])
       ->setCellValue('O'.$exrow ,$row['ARLEXTENDEDDISCOUNTBASE'])
       ->setCellValue('P'.$exrow ,$row['ARLEXTENDEDPRICEBASE'])
       ->setCellValue('Q'.$exrow ,$row['commission_percent'])
       ->setCellValue('R'.$exrow ,$commission);



   //set row style
   if( $exrow % 2 == 0 ){
       //even row
       $spreadsheet->getActiveSheet()->getStyle('A'.$exrow.':R'.$exrow)->applyFromArray($evenRow);
   }else{
       //odd row
       $spreadsheet->getActiveSheet()->getStyle('A'.$exrow.':R'.$exrow)->applyFromArray($oddRow);
   }
   //increment row
   $exrow++;
}
odbc_close($connect);

//define first row and last row
$firstRow=5;
$lastRow=$exrow-1;
$totalrow=$exrow+1;
$percentrow=$totalrow+1;

//SUBtotal and SUM rows

// Total All

//Percent

//Format currency 

//
$spreadsheet->getActiveSheet()
->setCellValue('G2',"Filter Total")
->setCellValue('G3',"Grand Total")
->setCellValue('G4',"Percent");

//set the autofilter
$spreadsheet->getActiveSheet()->setAutoFilter("A".$firstRow.":R".$totalrow);

//set the header first, so the result will be treated as an xlsx file.
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');

//make it an attachment so we can define filename
header('Content-Disposition: attachment;filename="'.$salesId.'".xlsx"');

//create IOFactory object
$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
//save into php output
$writer->save('php://output');

}

     

    phpspreadsheet create a new worksheet on salesperson name change

    in PHP Coding Help

    Posted

    Finally got time to work on this again. The foreach Statement works for the First salesperson "A" but stops after that with an error that it does not see the "Use PhpOffice\PhpSpreadsheet\Style\Fill" 

     

      

    <?php

//call the autoload
require 'vendor/autoload.php';
//load phpspreadsheet class using namespaces
use PhpOffice\PhpSpreadsheet\Spreadsheet;
//call iofactory instead of xlsx writer
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Fill;


//styling arrays
//table head style
$tableHead = [
	'font'=>[
		'color'=>[
			'rgb'=>'FFFFFF'
		],
		'bold'=>true,
		'size'=>11
	],
	'fill'=>[
		'fillType' => Fill::FILL_SOLID,
		'startColor' => [
			'rgb' => '538ED5'		
		]
	]
];
//even row
$evenRow = [
	'fill'=>[
		'fillType' => Fill::FILL_SOLID,
		'startColor' => [
			'rgb' => 'F2F2F2'
		]
	]
];
//odd row
$oddRow = [
	'fill'=>[
		'fillType' => Fill::FILL_SOLID,
		'startColor' => [
			'rgb' => 'FFFFFF'
		]
	]
];

$salesperson=array('A',
'B',
'C',
'D',
'F',
'G',
'H',
'I');
foreach($salesperson as $salesId){

$commission_percent=0;
$price=0;
$commission=0;
//styling arrays end
$exrow=6;
//make a new spreadsheet object
$spreadsheet = new Spreadsheet();
//get current active sheet (first sheet)
$sheet = $spreadsheet->getActiveSheet();

//set default font
$spreadsheet->getDefaultStyle()
	->getFont()
	->setName('Arial')
	->setSize(10);

//heading
$spreadsheet->getActiveSheet()
	->setCellValue('A1',"Comission");

//merge heading
$spreadsheet->getActiveSheet()->mergeCells("A1:R1");

// set font style
$spreadsheet->getActiveSheet()->getStyle('A1')->getFont()->setSize(20);

// set cell alignment
$spreadsheet->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

//setting column width
$spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('F')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('G')->setWidth(45);
$spreadsheet->getActiveSheet()->getColumnDimension('H')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('I')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('J')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('K')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('L')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('M')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('N')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('O')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('P')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('Q')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('R')->setWidth(10);


//header text//current row
$spreadsheet->getActiveSheet()
	->setCellValue('A5',"INVOICE DATE")
	->setCellValue('B5',"SALESPERSON")
	->setCellValue('C5',"INVOICE ID")
	->setCellValue('D5',"ORDER ID")
	->setCellValue('E5',"ORDER DATE")
	->setCellValue('F5',"CUSTOMER ID")
	->setCellValue('G5',"NAME")
	->setCellValue('H5',"GROUP ID")
	->setCellValue('I5',"STATE")
	->setCellValue('J5',"QTY")
	->setCellValue('K5',"PART")
	->setCellValue('L5',"UOM")
	->setCellValue('M5',"GROUP")
	->setCellValue('N5',"GROSS")
	->setCellValue('O5',"DISCOUNT")
	->setCellValue('P5',"NET")
	->setCellValue('Q5',"CommPrcnt")
	->setCellValue('R5',"Commission");
	



//set font style and background color
$spreadsheet->getActiveSheet()->getStyle('A5:R5')->applyFromArray($tableHead);
// the connection 
$connect =odbc_connect("removed");
if(!$connect) {
	exit("Connection Failed: " . $connect);
}

//the sql query
$sql="select INVOICEDATE
,ARJSALESEMPLOYEEID
,ARPARINVOICEID 
,UARPSALESORDERNUMBER
,createdDate
,ARPCUSTOMERORGANIZATIONID
,CMONAME
,CMOCUSTOMERGROUPID
,cmoState
,ARLINVOICEQUANTITY
,ARLPARTID
,ARLUNITOFMEASURE
,ARLPARTGROUPID
,ARLSALESORDERID
,CONVERT(DECIMAL(10,2),ARLFULLEXTENDEDPRICEBASE)  as ARLFULLEXTENDEDPRICEBASE
,CONVERT(DECIMAL(10,2),ARLEXTENDEDDISCOUNTBASE) as ARLEXTENDEDDISCOUNTBASE
,CONVERT(DECIMAL(10,2),ARLEXTENDEDPRICEBASE) as ARLEXTENDEDPRICEBASE

,commission_percent
--,sum(commission_percent*ARLEXTENDEDPRICEBASE) as commission
from

(SELECT 

convert(varchar, ARPINVOICEDATE, 101) as INVOICEDATE
,ARJSALESEMPLOYEEID
,ARPARINVOICEID 
,UARPSALESORDERNUMBER
,convert(varchar, ompCreatedDate, 101) as CreatedDate
,ARPCUSTOMERORGANIZATIONID
,CMONAME
,CMOCUSTOMERGROUPID
,cmoState
,ARLINVOICEQUANTITY
,ARLPARTID
,ARLUNITOFMEASURE
,ARLPARTGROUPID
,ARLFULLEXTENDEDPRICEBASE
,ARLEXTENDEDDISCOUNTBASE
,ARLEXTENDEDPRICEBASE


,ARLSALESORDERID

,case when arlPartGroupID = 'PROMO' then .04 
when arlPartGroupID = 'DC' then .02 
when cmocustomergroupid = 'LIST' then .05
when cmocustomergroupid = 'VOL' then .04
when cmocustomergroupid = 'VOL2' then .04
when cmocustomergroupid = 'VOL4' then .05
else .05 end as commission_percent

FROM M1_KF.dbo.ARInvoices
INNER JOIN M1_KF.dbo.ARInvoiceLines ON arpARInvoiceID = arlARInvoiceID
LEFT OUTER JOIN M1_KF.dbo.organizations ON ARPCUSTOMERORGANIZATIONID = cmoorganizationid
LEFT OUTER JOIN M1_KF.dbo.arinvoicesalespeople ON arparinvoiceid = arjarinvoiceid
left outer join M1_KF.dbo.SalesOrders on ompSalesOrderID=UARPSALESORDERNUMBER

where  arpPostedDate >='06-01-2019'and arpPostedDate <'07-01-2019'
and ARJSALESEMPLOYEEID='$salesId'
and arlPartGroupID not in('FRT')) as test


group by
 INVOICEDATE
,ARJSALESEMPLOYEEID
,ARPARINVOICEID 
,UARPSALESORDERNUMBER
,createdDate
,ARPCUSTOMERORGANIZATIONID
,CMONAME
,CMOCUSTOMERGROUPID
,cmoState
,ARLINVOICEQUANTITY
,ARLPARTID
,ARLUNITOFMEASURE
,ARLPARTGROUPID
,ARLSALESORDERID
,ARLFULLEXTENDEDPRICEBASE
,ARLEXTENDEDDISCOUNTBASE
,ARLEXTENDEDPRICEBASE
,commission_percent


ORDER BY ARJSALESEMPLOYEEID,arpARInvoiceID";
//run the query
$result =odbc_exec($connect,$sql);
if(!$result){
exit("Error in SQL");
}

 while ($row = odbc_fetch_array($result))
{
//loop through the data
$curentsalesrep=$row['ARJSALESEMPLOYEEID'];
//sum(commission_percent*ARLEXTENDEDPRICEBASE) as commiss

$prtcnt=floatval($row['commission_percent']);
$commission_percent=number_format($prtcnt, 2);
$price=floatval($row['ARLEXTENDEDPRICEBASE']);
$commission=$commission_percent*$price;
	$spreadsheet->getActiveSheet()
		->setCellValue('A'.$exrow ,$row['INVOICEDATE'])
		->setCellValue('B'.$exrow ,$row['ARJSALESEMPLOYEEID'])
		->setCellValue('C'.$exrow ,$row['ARPARINVOICEID'])
		->setCellValue('D'.$exrow ,$row['UARPSALESORDERNUMBER'])
		->setCellValue('E'.$exrow ,$row['createdDate'])
		->setCellValue('F'.$exrow ,$row['ARPCUSTOMERORGANIZATIONID'])
		->setCellValue('G'.$exrow ,$row['CMONAME'])
		->setCellValue('H'.$exrow ,$row['CMOCUSTOMERGROUPID'])
		->setCellValue('I'.$exrow ,$row['cmoState'])
		->setCellValue('J'.$exrow ,$row['ARLINVOICEQUANTITY'])
		->setCellValue('K'.$exrow ,$row['ARLPARTID'])
		->setCellValue('L'.$exrow ,$row['ARLUNITOFMEASURE'])
		->setCellValue('M'.$exrow ,$row['ARLPARTGROUPID'])
		->setCellValue('N'.$exrow ,$row['ARLFULLEXTENDEDPRICEBASE'])
		->setCellValue('O'.$exrow ,$row['ARLEXTENDEDDISCOUNTBASE'])
		->setCellValue('P'.$exrow ,$row['ARLEXTENDEDPRICEBASE'])
		->setCellValue('Q'.$exrow ,$row['commission_percent'])
		->setCellValue('R'.$exrow ,$commission);
	
		
		
	//set row style
	if( $exrow % 2 == 0 ){
		//even row
		$spreadsheet->getActiveSheet()->getStyle('A'.$exrow.':R'.$exrow)->applyFromArray($evenRow);
	}else{
		//odd row
		$spreadsheet->getActiveSheet()->getStyle('A'.$exrow.':R'.$exrow)->applyFromArray($oddRow);
	}
	//increment row
	$exrow++;
}


//define first row and last row
$firstRow=5;
$lastRow=$exrow-1;
$totalrow=$exrow+1;
$percentrow=$totalrow+1;



//SUBtotal and SUM rows
$spreadsheet->getActiveSheet()->setCellValue('J2', "=SUBTOTAL(9,J6:J$lastRow)");
$spreadsheet->getActiveSheet()->setCellValue('N2', "=SUBTOTAL(9,N6:N$lastRow)");
$spreadsheet->getActiveSheet()->setCellValue('O2', "=SUBTOTAL(9,O6:O$lastRow)");
$spreadsheet->getActiveSheet()->setCellValue('P2', "=SUBTOTAL(9,P6:P$lastRow)");
$spreadsheet->getActiveSheet()->setCellValue('R2', "=SUBTOTAL(9,R6:R$lastRow)");
// Total All
$spreadsheet->getActiveSheet()->setCellValue('J3', "=SUM(J6:J$lastRow)");
$spreadsheet->getActiveSheet()->setCellValue('N3', "=SUM(N6:N$lastRow)");
$spreadsheet->getActiveSheet()->setCellValue('O3', "=SUM(O6:O$lastRow)");
$spreadsheet->getActiveSheet()->setCellValue('P3', "=SUM(P6:P$lastRow)");
$spreadsheet->getActiveSheet()->setCellValue('R3', "=SUM(R6:R$lastRow)");
//Percent
$spreadsheet->getActiveSheet()->setCellValue('J4', "=SUM(J2/J3)*100");
$spreadsheet->getActiveSheet()->setCellValue('N4', "=SUM(N2/N3)*100");
$spreadsheet->getActiveSheet()->setCellValue('O4', "=SUM(O2/O3)*100");
$spreadsheet->getActiveSheet()->setCellValue('P4', "=SUM(P2/P3)*100");
$spreadsheet->getActiveSheet()->setCellValue('R4', "=SUM(R2/R3)*100");
//Format currency 
$spreadsheet->getActiveSheet()->getStyle('N'.$exrow)->getNumberFormat()
	->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
	$spreadsheet->getActiveSheet()->getStyle('O'.$exrow)->getNumberFormat()
	->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
	$spreadsheet->getActiveSheet()->getStyle('P'.$exrow)->getNumberFormat()
	->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
	$spreadsheet->getActiveSheet()->getStyle('R'.$exrow)->getNumberFormat()
	->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
	$spreadsheet->getActiveSheet()->getStyle('N'.$totalrow)->getNumberFormat()
	->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
	$spreadsheet->getActiveSheet()->getStyle('O'.$totalrow)->getNumberFormat()
	->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
	$spreadsheet->getActiveSheet()->getStyle('P'.$totalrow)->getNumberFormat()
	->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
	$spreadsheet->getActiveSheet()->getStyle('R'.$totalrow)->getNumberFormat()
	->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
//
$spreadsheet->getActiveSheet()
->setCellValue('G2',"Filter Total")
->setCellValue('G3',"Grand Total")
->setCellValue('G4',"Percent");

$spreadsheet->getActiveSheet()->getStyle('G2')->getFont()->setSize(12);
$spreadsheet->getActiveSheet()->getStyle('G3')->getFont()->setSize(12);
$spreadsheet->getActiveSheet()->getStyle('G4')->getFont()->setSize(12);

$spreadsheet->getActiveSheet()->getStyle("R".$firstRow.":R".$totalrow)->getNumberFormat()
    ->setFormatCode('#,##0.00');
    $spreadsheet->getActiveSheet()->getStyle("P".$firstRow.":P".$totalrow)->getNumberFormat()
    ->setFormatCode('#,##0.00');
    $spreadsheet->getActiveSheet()->getStyle("N".$firstRow.":N".$totalrow)->getNumberFormat()
    ->setFormatCode('#,##0.00');
//set the autofilter
$spreadsheet->getActiveSheet()->setAutoFilter("A".$firstRow.":R".$totalrow);
odbc_close($connect);


//set the header first, so the result will be treated as an xlsx file.
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');

//make it an attachment so we can define filename
header('Content-Disposition: attachment;filename="'.$salesId.'".xlsx"');

//create IOFactory object
$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
//save into php output
$writer->save('php://output');
}
echo "done";
?>

     

    phpspreadsheet create a new worksheet on salesperson name change

    in PHP Coding Help

    Posted

    I am creating a spreadsheet with phpspreadsheet using  sql data. After the first loop that gets all the data I want to loop through the data again and create new worksheets for each salesperson(ARJSALESEMPLOYEEID).

    This code gets me all the data in a worksheet how would I then run through it again and create the worksheets "createSheets($row[ARJSALESEMPLOYEEID])" for each salesperson? 

     

     

      

    <?php
//call the autoload
require 'vendor/autoload.php';
//load phpspreadsheet class using namespaces
use PhpOffice\PhpSpreadsheet\Spreadsheet;
//call iofactory instead of xlsx writer
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Fill;

//styling arrays
//table head style
$tableHead = [
    'font'=>[
        'color'=>[
            'rgb'=>'FFFFFF'
        ],
        'bold'=>true,
        'size'=>11
    ],
    'fill'=>[
        'fillType' => Fill::FILL_SOLID,
        'startColor' => [
            'rgb' => '538ED5'        
        ]
    ]
];
//even row
$evenRow = [
    'fill'=>[
        'fillType' => Fill::FILL_SOLID,
        'startColor' => [
            'rgb' => 'F2F2F2'
        ]
    ]
];
//odd row
$oddRow = [
    'fill'=>[
        'fillType' => Fill::FILL_SOLID,
        'startColor' => [
            'rgb' => 'FFFFFF'
        ]
    ]
];

//styling arrays end
$exrow=3;
//make a new spreadsheet object
$spreadsheet = new Spreadsheet();
//get current active sheet (first sheet)
$sheet = $spreadsheet->getActiveSheet();

//set default font
$spreadsheet->getDefaultStyle()
    ->getFont()
    ->setName('Arial')
    ->setSize(10);

//heading
$spreadsheet->getActiveSheet()
    ->setCellValue('A1',"Comission");

//merge heading
$spreadsheet->getActiveSheet()->mergeCells("A1:P1");

// set font style
$spreadsheet->getActiveSheet()->getStyle('A1')->getFont()->setSize(20);

// set cell alignment
$spreadsheet->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

//setting column width
$spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(25);
$spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(20);
$spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(20);
$spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(30);
$spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(25);
$spreadsheet->getActiveSheet()->getColumnDimension('F')->setWidth(25);
$spreadsheet->getActiveSheet()->getColumnDimension('G')->setWidth(25);
$spreadsheet->getActiveSheet()->getColumnDimension('H')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('I')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('J')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('K')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('L')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('M')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('N')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('O')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('P')->setWidth(10);


//header text//current row
$spreadsheet->getActiveSheet()
    ->setCellValue('A2',"INVOICE DATE")
    ->setCellValue('B2',"SALESPERSON")
    ->setCellValue('C2',"INVOICE ID")
    ->setCellValue('D2',"ORDER ID")
    ->setCellValue('E2',"ORDER DATE")
    ->setCellValue('F2',"CUSTOMER ID")
    ->setCellValue('G2',"NAME")
    ->setCellValue('H2',"GROUP ID")
    ->setCellValue('I2',"STATE")
    ->setCellValue('J2',"QTY")
    ->setCellValue('K2',"PART")
    ->setCellValue('L2',"UOM")
    ->setCellValue('M2',"GROUP")
    ->setCellValue('N2',"GROSS")
    ->setCellValue('O2',"DISCOUNT")
    ->setCellValue('P2',"NET");
    

//set font style and background color
$spreadsheet->getActiveSheet()->getStyle('A2:P2')->applyFromArray($tableHead);
// the connection 
$connect =odbc_connect(removed);
if(!$connect) {
    exit("Connection Failed: " . $connect);
}

//the sql query
$sql="SELECT 
ARPINVOICEDATE
,ARJSALESEMPLOYEEID
,ARPARINVOICEID 
,UARPSALESORDERNUMBER
,ompCreatedDate
,ARPCUSTOMERORGANIZATIONID
,CMONAME
,CMOCUSTOMERGROUPID
,cmoState
,ARLINVOICEQUANTITY
,ARLPARTID
,ARLUNITOFMEASURE
,ARLPARTGROUPID
,ARLFULLEXTENDEDPRICEBASE
,ARLEXTENDEDDISCOUNTBASE
,ARLEXTENDEDPRICEBASE
,ARLSALESORDERID

,case when arlPartGroupID = 'PROMO' then 4.00 
when arlPartGroupID = 'DC' then 2.00 
when cmocustomergroupid = 'LIST' then 5.00 
when cmocustomergroupid = 'VOL' then 4.00
when cmocustomergroupid = 'VOL2' then 4.00
when cmocustomergroupid = 'VOL2' then 5.00
else 5.00 end as comission_percent

FROM M1_KF.dbo.ARInvoices
INNER JOIN M1_KF.dbo.ARInvoiceLines ON arpARInvoiceID = arlARInvoiceID
LEFT OUTER JOIN M1_KF.dbo.organizations ON ARPCUSTOMERORGANIZATIONID = cmoorganizationid
LEFT OUTER JOIN M1_KF.dbo.arinvoicesalespeople ON arparinvoiceid = arjarinvoiceid
left outer join M1_KF.dbo.SalesOrders on ompSalesOrderID=UARPSALESORDERNUMBER

where arlsalesorderid !='' and arpPostedDate >='05-01-2019'and arpPostedDate <'06-01-2019'
and arlPartGroupID not in('FRT')
ORDER BY ARJSALESEMPLOYEEID,arpARInvoiceID";
//run the query
$result =odbc_exec($connect,$sql);
if(!$result){
exit("Error in SQL");
}

 while ($row = odbc_fetch_array($result))
{
//loop through the data

    $spreadsheet->getActiveSheet()
        ->setCellValue('A'.$exrow ,$row['ARPINVOICEDATE'])
        ->setCellValue('B'.$exrow ,$row['ARJSALESEMPLOYEEID'])
        ->setCellValue('C'.$exrow ,$row['ARPARINVOICEID'])
        ->setCellValue('D'.$exrow ,$row['UARPSALESORDERNUMBER'])
        ->setCellValue('E'.$exrow ,$row['ompCreatedDate'])
        ->setCellValue('F'.$exrow ,$row['ARPCUSTOMERORGANIZATIONID'])
        ->setCellValue('G'.$exrow ,$row['CMONAME'])
        ->setCellValue('H'.$exrow ,$row['CMOCUSTOMERGROUPID'])
        ->setCellValue('I'.$exrow ,$row['cmoState'])
        ->setCellValue('J'.$exrow ,$row['ARLINVOICEQUANTITY'])
        ->setCellValue('K'.$exrow ,$row['ARLPARTID'])
        ->setCellValue('L'.$exrow ,$row['ARLUNITOFMEASURE'])
        ->setCellValue('M'.$exrow ,$row['ARLPARTGROUPID'])
        ->setCellValue('N'.$exrow ,$row['ARLFULLEXTENDEDPRICEBASE'])
        ->setCellValue('O'.$exrow ,$row['ARLEXTENDEDDISCOUNTBASE'])
        ->setCellValue('P'.$exrow ,$row['ARLEXTENDEDPRICEBASE']);
        
        
        
    //set row style
    if( $exrow % 2 == 0 ){
        //even row
        $spreadsheet->getActiveSheet()->getStyle('A'.$exrow.':P'.$exrow)->applyFromArray($evenRow);
    }else{
        //odd row
        $spreadsheet->getActiveSheet()->getStyle('A'.$exrow.':P'.$exrow)->applyFromArray($oddRow);
    }
    //increment row
    $exrow++;
}


//define first row and last row
$firstRow=2;
$lastRow=$exrow-1;
//set the autofilter
$spreadsheet->getActiveSheet()->setAutoFilter("A".$firstRow.":P".$lastRow);

odbc_close($connect);


//set the header first, so the result will be treated as an xlsx file.
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');

//make it an attachment so we can define filename
header('Content-Disposition: attachment;filename="result.xlsx"');

//create IOFactory object
$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
//save into php output
$writer->save('php://output');

     

    need help with populating a drop down box to select employee ID and submit

    in PHP Coding Help

    Posted

    not sure how to do this I have the sql and a attempt at pulling the list but it fails with 

    Notice: Undefined variable: select in line 22 which is the 

    $select.='<option value="'.$row['lmeEmployeeID'].'">'.$row['lmeEmployeeName'].'</option>';

    and then hot to submit it to the other page???? like at the bottom of the below code.

    <html>
<head>
<title>Employee</title>
</head>
<body>
<h1>Employee IDe</h1>
<?php
$connect =odbc_connect("removed");
if(!$connect) {
	exit("Connection Failed: " . $connect);
}

$sql="
select lmeEmployeeName, lmeEmployeeID from  m1_kf.dbo.Employees where lmeTerminationDate IS NULL
";
$result =odbc_exec($connect,$sql);
if(!$result){
exit("Error in SQL");
}
while ($row = odbc_fetch_array($result))
{
      $select.='<option value="'.$row['lmeEmployeeID'].'">'.$row['lmeEmployeeName'].'</option>';
  }

$select.='</select>';



?>
<form method="post" action="employee.php">
<input type="text" name="salesman">
<input type="submit"> 
</form> 
</body>
</html>

    PDO Connection Function not working

    in PHP Coding Help

    Posted

    connection.php

    define ('DB_HOST', 'localhost');
define ('DB_NAME', 'labor');
define ('DB_USER', 'user');
define ('DB_PASSWORD', 'removed');
define ('DB_CHARSET', 'utf8');

    function.php

    <?php


function mysqlconnect($host, $username, $password, $database, $character_set)
{
    $dsn = 'mysql:host='.$host.';dbname='.$database.';charset='.$character_set;

    return new PDO($dsn, $username, $password, [
        PDO::ATTR_EMULATE_PREPARES => false,
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
       PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
   ]);
}
?>

    mypage.php

    include'function.php';
require_once 'connection.php';

$database_connection = mysqlconnect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME, DB_CHARSET);



$gr_total = 0;
$gr_count =0;

$sql ='SELECT Supervisor,  SEC_TO_TIME(sum(TIME_TO_SEC(Reg_hours))) as RegHours
,SEC_TO_TIME( SUM( TIME_TO_SEC( Ovt_hours))) as Ovt_hours
, SEC_TO_TIME( SUM( TIME_TO_SEC( Vac_hours))) as Vac_hours
, count(*) as count
FROM `Hours_Worked`
WHERE Supervisor in ("Kenny","Greg")
and Date=CURDATE()-2
and (Reg_hours > 1 or Ovt_hours > 1 or Vac_hours > 1)
group by Supervisor';
 
$q = $database_connection->query($sql);
    $q->setFetchMode(PDO::FETCH_ASSOC);
while($row = $q->fetch()){
  
$gr_total = $gr_total + $row['RegHours'];
 $gr_count = $gr_count + $row['count'];
 $num2 = number_format( $gr_total, 2);

echo "<tr><td>".$row['Supervisor']."&nbsp&nbsp<td>";
echo "<td>".$row['RegHours']."&nbsp&nbsp<td>";
echo "<td>".$row['count']."<td>";
echo "<td><br></td>";

}
echo "<td>"."$gr_count"."</td>";
echo "<td><br></td>";
echo "<td>"."$num2"."</td></tr>";


?>
</body>
</html>

    PDO Connection Function not working

    in PHP Coding Help

    Posted

    Notice: Use of undefined constant DB_HOST - assumed 'DB_HOST' in /var/www/html/Theme/mysqltest.php on line 32

    Notice: Use of undefined constant DB_USER - assumed 'DB_USER' in /var/www/html/Theme/mysqltest.php on line 32

    Notice: Use of undefined constant DB_PASSWORD - assumed 'DB_PASSWORD' in /var/www/html/Theme/mysqltest.php on line 32

    Notice: Use of undefined constant DB_NAME - assumed 'DB_NAME' in /var/www/html/Theme/mysqltest.php on line 32

    Notice: Use of undefined constant DB_CHARSET - assumed 'DB_CHARSET' in /var/www/html/Theme/mysqltest.php on line 32

    Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000] [2005] Unknown MySQL server host 'DB_HOST' (110)' in /var/www/html/Theme/function.php:12 Stack trace: #0 /var/www/html/Theme/function.php(12): PDO->__construct('mysql:host=DB_H...', 'DB_USER', 'DB_PASSWORD', Array) #1 /var/www/html/Theme/mysqltest.php(32): mysqlconnect('DB_HOST', 'DB_USER', 'DB_PASSWORD', 'DB_NAME', 'DB_CHARSET') #2 {main} thrown in/var/www/html/Theme/function.php on line 12

    PDO Connection Function not working

    in PHP Coding Help

    Posted

    What is the best way to create the config file I looked at a lot of examples and it does not seem very uniformed and I am not sure how to create the argument to pass from the config file to the function file.

    <?php
define ('db_host', 'localhost');
define ('db_name', 'labor');
define ('db_user', 'user');
define ('db_pass', 'removed');
define ('db_char', 'utf8');

?>

    This work's

    <html>
<head>
</head>
<body>

<?php
/************************** This willl go in a file called connection.php **************************/
$host = "localhost";
$username = "user";
$password = "removed";
$database="labor";
$character_set="utf8";
/*****************************************************************************************************/
/************************** This will go into my Function.php file.  *********************************/
function mysqlconnect($host, $username, $password, $database, $character_set)
{
    $dsn = 'mysql:host='.$host.';dbname='.$database.';charset='.$character_set;

    return new PDO($dsn, $username, $password, [
        PDO::ATTR_EMULATE_PREPARES => false,
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    ]);
}

/*****************************************************************************************************/
/********************** this is my page that I am running ********************************************/

$database_connection = mysqlconnect($host, $username, $password, $database, $character_set);



$gr_total = 0;
$gr_count =0;

$sql ='SELECT Supervisor,  SEC_TO_TIME(sum(TIME_TO_SEC(Reg_hours))) as RegHours
,SEC_TO_TIME( SUM( TIME_TO_SEC( Ovt_hours))) as Ovt_hours
, SEC_TO_TIME( SUM( TIME_TO_SEC( Vac_hours))) as Vac_hours
, count(*) as count
FROM `Hours_Worked`
WHERE Supervisor in ("Kenny","Greg")
and Date=CURDATE()-2
and (Reg_hours > 1 or Ovt_hours > 1 or Vac_hours > 1)
group by Supervisor';
 
$q = $database_connection->query($sql);
    $q->setFetchMode(PDO::FETCH_ASSOC);
while($row = $q->fetch()){
  
$gr_total = $gr_total + $row['RegHours'];
 $gr_count = $gr_count + $row['count'];
 $num2 = number_format( $gr_total, 2);

echo "<tr><td>".$row['Supervisor']."&nbsp&nbsp<td>";
echo "<td>".$row['RegHours']."&nbsp&nbsp<td>";
echo "<td>".$row['count']."<td>";
echo "<td><br></td>";

}
echo "<td>"."$gr_count"."</td>";
echo "<td><br></td>";
echo "<td>"."$num2"."</td></tr>";


?>
</body>
</html>

    PDO Connection Function not working

    in PHP Coding Help

    Posted

    all the code works if I put it together on the same page but if I put the connection information in  a function then call the function it errors with 

     

    Connected successfully
    Notice: Undefined variable: conn in /var/www/html/Theme/mysqltest.php on line 24

    Fatal error: Call to a member function query() on a non-object in /var/www/html/Theme/mysqltest.php on line 24

    What do I have wrong

     

    the connection

    function mysqlconnect() {

$servername = "localhost";
$username = "user";
$password = "removed";
$db="labor";
try {
    $conn = new PDO("mysql:host=$servername;dbname=$db", $username, $password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully"; 
    }
catch(PDOException $e)
    {
    die("Connection failed: " . $e->getMessage());
   }

}

    The page calling the function

    <html>
<head>
</head>
<body>

<?php
include('function.php'); 
mysqlconnect();


$gr_total = 0;
 $gr_count =0;

$sql ='SELECT Supervisor,  SEC_TO_TIME(sum(TIME_TO_SEC(Reg_hours))) as RegHours
,SEC_TO_TIME( SUM( TIME_TO_SEC( Ovt_hours))) as Ovt_hours
, SEC_TO_TIME( SUM( TIME_TO_SEC( Vac_hours))) as Vac_hours
, count(*) as count
FROM `Hours_Worked`
WHERE Supervisor in ("Kenny","Greg")
and Date=CURDATE()-2
and (Reg_hours > 1 or Ovt_hours > 1 or Vac_hours > 1)
group by Supervisor';
 
$q = $conn->query($sql);
    $q->setFetchMode(PDO::FETCH_ASSOC);
while($row = $q->fetch()){
  
$gr_total = $gr_total + $row['RegHours'];
 $gr_count = $gr_count + $row['count'];
 $num2 = number_format( $gr_total, 2);

echo "<tr><td>".$row['Supervisor']."&nbsp&nbsp<td>";
echo "<td>".$row['RegHours']."&nbsp&nbsp<td>";
echo "<td>".$row['count']."<td>";
echo "<td><br></td>";

}
echo "<td>"."$gr_count"."</td>";
echo "<td><br></td>";
echo "<td>"."$num2"."</td></tr>";


?>
</body>
</html>

    How/What is the correct way to create a function for MS SQL Queries.

    in PHP Coding Help

    Posted

    #2 did help me kinda but i was hoping I could open a connection to the sql server run all three queries and return the totals for each query and close the connection.

    and I not sure if thats the best way. Right now I have the pages one for day week and month and I include those on my page that show the totals for each and it works fine but I am trying to learn the correct/better way of doing this so I don't have so many pages and I do thank all of you for the instructions.

    How/What is the correct way to create a function for MS SQL Queries.

    in PHP Coding Help

    Posted

    I have three Queries that show Production totals for Day, Week and Month. How would you do this.

     

    Day code looks like this:

    <?php
$grandTotal = 0;
$connect =odbc_connect("removed");
if(!$connect) {
exit("Connection Failed: " . $connect);
}
 
$sql="	SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, tdate)) AS DATE
	,ISNULL(trans, 'NON') AS trans
	,ISNULL(Transactions.item, Snumbers.item) AS item
	,count(serial) AS qty
	,tuser
	,sum(M1_KF.dbo.PartUnitSalePrices.imhUnitSalePrice) as TotalPrice
	FROM Orbedata.dbo.SNumbers
  	LEFT OUTER JOIN OrbeData.dbo.Transactions ON snum = serial 
	INNER JOIN M1_KF.dbo.PartUnitSalePrices ON Orbedata.dbo.transactions.item = M1_KF.dbo.PartUnitSalePrices.imhPartID
	WHERE CONVERT(DATE, tdate) = CONVERT(DATE, Getdate())
		AND trans = 'fpr'
	GROUP BY DATEADD(dd, 0, DATEDIFF(dd, 0, tdate))
	,ISNULL(trans, 'NON')
	,ISNULL(Transactions.item, Snumbers.item)
	,tuser
	,Orbedata.dbo.transactions.qty
	order by tuser,item";

 
$result =odbc_exec($connect,$sql);
if(!$result){
exit("Error in SQL");
}
 
 
while (odbc_fetch_row($result)) {
 
 $Price=odbc_result($result,"TotalPrice");
 
$num = number_format($Price, 2);
 $grandTotal += $Price;
 $num3 = number_format( $grandTotal, 2);
 
}
odbc_close($connect);
echo "$num3";
?>

    Week only the where clause changes like this:

    WHERE  tdate>=DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0) and   tdate<=DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6)

    Month only the where clause changes like this:

    	WHERE  tdate>=DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0) and   tdate<=DATEADD (dd, -1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 1, 0))

    Format so that php and html on same line

    in PHP Coding Help

    Posted

    All 75 +pages are standalone pages meaning that user all day open these pages to get information. I wanted to create a page that would show all the total information for each page so you could just look at one page see whats going on and then if you needed the detail you could click the link to take you to that detail  page. I robbed code from each page to create my pages for the totals not removing all that html but a lot.I now envision what I  should do is create the pages as functions. I am not sure on the database connection because these reports cover from Labor, Sales, Production, Shipping. Three different database on two different servers. I thing maybe three pages one for labor, one for sales and one for everything else and then just include all three on the main page. 

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.