Jump to content

kat35601

Members
  • Content Count

    149
  • Joined

  • Last visited

Everything posted by kat35601

  1. 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.
  2. 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'); }
  3. 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"; ?>
  4. Creating the array is give me great trouble could you elaborate for me just a bit. thanks
  5. 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');
  6. it works thank you. SELECT pt.impPartGroupID ,sl.omlPartID ,sum(sl.omlExtendedPriceBase) AS Total ,sum(sl.omlOrderQuantity) AS qty ,sum(sl.omlOrderQuantity) / q12 AS pcent FROM m1_kf.dbo.SalesOrders so LEFT JOIN m1_KF.dbo.SalesOrderLines sl ON omlSalesOrderID = ompSalesOrderId LEFT JOIN m1_KF.dbo.Parts pt ON impPartID = omlPartID LEFT JOIN ( SELECT imppartgroupid ,sum(omlOrderQuantity) AS q12 FROM m1_kf.dbo.SalesOrders LEFT JOIN m1_KF.dbo.SalesOrderLines ON omlSalesOrderID = ompSalesOrderId LEFT JOIN m1_KF.dbo.Parts ptt ON impPartID = omlPartID WHERE ompCreatedDate > '03-01-2019' AND impPartClassID IN ( 'FGI' ,'FGM' ) AND omlpartid LIKE '%-12' GROUP BY impPartGroupID ) AS cte ON cte.impPartGroupID = omlPartGroupID WHERE ompCreatedDate > '03-01-2019' AND impPartClassID IN ( 'FGI' ,'FGM' ) GROUP BY pt.impPartGroupID ,omlPartID ,cte.q12 ORDER BY impPartGroupID ,omlPartID
  7. yes I took you suggestion and adapted it to work with my tables. The join aliasname is cte. Thanks for the help any suggestion on why I am getting the erroneous results
  8. This is what I have if I run the two queries separately they work fine but together I get erroneous results. lots of the same item over and over again. I could not get the USING to work. so I am sure it's the ON statement but that's the only way I could get it to run. SELECT pt.impPartGroupID ,sl.omlPartID ,sum(sl.omlExtendedPriceBase) AS Total ,sum(sl.omlOrderQuantity) as qty ,sum(sl.omlOrderQuantity) / q12 AS pcent FROM m1_kf.dbo.SalesOrders so LEFT JOIN m1_KF.dbo.SalesOrderLines sl ON omlSalesOrderID = ompSalesOrderId LEFT JOIN m1_KF.dbo.Parts pt ON impPartID = omlPartID join (select imppartgroupid ,sum(omlOrderQuantity) AS q12 FROM m1_kf.dbo.SalesOrders LEFT JOIN m1_KF.dbo.SalesOrderLines ON omlSalesOrderID = ompSalesOrderId LEFT JOIN m1_KF.dbo.Parts ptt ON impPartID = omlPartID where ompCreatedDate >'03-01-2019' and impPartClassID in('FGI','FGM') and omlpartid like '%-12' group by impPartGroupID) as cte on omlPartGroupID=omlPartGroupID where ompCreatedDate >'03-01-2019' and impPartClassID in('FGI','FGM') group by pt.impPartGroupID, omlPartID, cte.q12 order by impPartGroupID, omlPartID
  9. <?php $connect =odbc_connect(removed); if(!$connect) { exit("Connection Failed: " . $connect); } $sql="select impPartGroupID, omlPartID, sum(omlOrderQuantity) as Qty, sum(omlExtendedPriceBase) as Total FROM m1_kf.dbo.SalesOrders SO left join m1_KF.dbo.SalesOrderLines on omlSalesOrderID=ompSalesOrderId left join m1_KF.dbo.Parts on impPartID=omlPartID where ompCreatedDate >'01-01-2019' and impPartGroupID in (select impPartGroupID from m1_KF.dbo.Parts where impPartID like '%-12' and impPartClassID in ('FGI','FGM') ) group by Parts.impPartGroupID, omlPartID order by Parts.impPartGroupID, omlPartID "; $result =odbc_exec($connect,$sql); if(!$result){ exit("Error in SQL"); } echo "<table><tr>"; echo "<th>PartGroup</th>"; echo "<th>PartID</th>"; echo "<th>Qty</th>"; echo "<th>Total</th>"; while ($row = odbc_fetch_array($result)) { echo "<tr><td>" .$row['impPartGroupID'] ."</td>"; echo "<td>" .$row['omlPartID'] ."</td>"; echo "<td>" .$row['Qty'] ."</td>"; echo "<td>" .$row['Total'] ."</td></tr>"; } odbc_close($connect); ?> My query
  10. Hello, I have a query that I need to divided all quantity of items in a part group by the part that ends in -12 of the same part group so every part in a 110 partgroup needs to be divided by the Qty of 110-12 example 110-01 with a Qty of 214 would be divided by the qty of part 110-12 which is 355 for a results of .6028.. or 60.28% and then move to the next number in the Part group 110-02 and so on. I have attached a jpeg of a spreadsheet that shows this.
  11. That's it spacing between the the <p> tags thank you.
  12. Why is double spacing the results I know it's stupid simple but I can't see it. I removed $host ip so if you test put one in there. <!doctype html> <html lang="en"> <html> <body> <?php ini_set('max_execution_time', 0); ini_set('memory_limit', -1); $host = "insert IP"; $ports = array(21 ,22 ,23 ,25, 80, 81, 110, 143, 443, 587, 2525, 3306); foreach ($ports as $port) { $connection = @fsockopen($host, $port, $errno, $errstr, 2); if (is_resource($connection)) { echo '<p>' . $host . ':' . $port . ' ' . '(' . getservbyport($port, 'tcp') . ') is open.</p>'; fclose($connection); } else { echo '<p>' . $host . ':' . $port . ' is not Open.</p>'; } } ?> </body> </html>
  13. Thanks I looked and looked bet did not see methd="post". Instead of method="post" but that fixed my problem. Thanks Again
  14. I still do not get the echo "Missing Information required"; <!doctype html> <html lang="en"> <head> <meta charset="utf-8"> <style> </style> </head> <body> <?php if(isset($_POST['submit'])){ if(empty($_POST["custid"]) || empty($_POST["item"]) || empty($_POST["qty"])){ echo "Missing Information required"; } } ?> <form action="backorder.php" methd="post"> CustId:<input type="text" name="custid"><br /> Item #:<input type="text" name="item"><br /> Qty :<input type="text" name="qty"><br /> <input type="submit" name="submit" value ="OK"> </form> </body> </html
  15. My if(empty())Statement is not working what did I do wrong??? <!doctype html> <html lang="en"> <head> <meta charset="utf-8"> <style> </style> </head> <body> <?php if(isset($_POST['submit'])){ if(empty($_POST["custid"] || $_POST["item"] || $_POST["qty"])){ echo "Missing Information required"; } } ?> <form action="backorder.php" methd="post"> CustId:<input type="text" name="custid"><br /> Item #:<input type="text" name="item"><br /> Qty :<input type="text" name="qty"><br /> <input type="submit" name="submit" value ="OK"> </form> </body> </html>
  16. I would like help bullet proofing my code. So if you could suggest ways to handle exceptions or on better ways to write the code I have I would be grateful. <?php $db= include('/var/app/app_env.php'); $port = 22; $user = $db['cit_user']; $pass = $db['cit_pass']; $host = $db['cit_host']; $connection = NULL; $remote_file_path = "/Outbox/CCDATA.TXT"; $local_file = './cit_order_download'. date('mdY_hia') .'.co'; /////////////////////////////////////////////////////////////// //echo "<td>$host</td>"; /////////////////////////////////////////////////////////////// try { $connection = ssh2_connect($host, $port); if(!$connection){ throw new \Exception("Could not connect to $host on port $port"); } $auth = ssh2_auth_password($connection, $user, $pass); if(!$auth){ throw new \Exception("Could not authenticate with username $user and password "); } $sftp = ssh2_sftp($connection); if(!$sftp){ throw new \Exception("Could not initialize SFTP subsystem."); } $stream = fopen("ssh2.sftp://" .(int)$sftp.'//Outbox//'.'CCDATA.TXT', 'r'); $contents = stream_get_contents($stream); file_put_contents ($local_file, $contents); @fclose($stream); $connection = NULL; } catch (Exception $e) { echo "Error due to :".$e->getMessage(); } //////////////////////////////////////////////////////////////////////////////////////////////// $result_clear=''; $result_hold=''; $connect = odbc_connect($db['name'], $db['user'], $db['password']); $approve_status=array("AA","AC","AD","AX"); $decline_status=array("DA","DR","HC","CI","CR","CZ"); sleep(2); $fp = fopen($local_file, 'r'); while (!feof($fp)) { $line = fgets($fp); $order = substr($line, 69, 5); $status=substr($line, 117, 2); $assignment=substr($line, 91, 10); $order=ltrim(rtrim($order)); if (in_array($status,$approve_status)){ $file_array= array($order=> array($assignment,$status)); $clear="update m1_kf.dbo.salesorders set uompcreditstatus='CLEAR', uompschedulecolor='$status$assignment' where ompsalesorderid ='$order' and ompOrderDate > '12-9-2017'"; echo $order,$assignment,$status; echo "<br>"; $result_clear = odbc_exec($connect, $clear); } elseif (in_array($status,$decline_status)){ $hold="update m1_kf.dbo.salesorders set uompcreditstatus='HOLD', uompschedulecolor='$status$assignment' where ompsalesorderid ='$order' and ompOrderDate > '12-9-2017'"; echo $order,$assignment,$status; echo"<br>"; $result_hold = odbc_exec($connect, $hold); } } fclose($fp); odbc_close($connect); ?>
  17. I am getting the Warning and not sure what my problem is. As you can see I am getting address data from a SQL Server and then trying to get the "lat" and "lng" information using a Google API and put that in an array that I can use later to create a google road map. I will have about 200 locations in the array but it errors out on the first one. I have tried a different address but get the same error.Below is my data from the echo $url ."<br>"; Thank You. and this is what I get from the warning. <?php ini_set('display_errors', 1); error_reporting(E_ALL); $connect =odbc_connect("removed"); if(!$connect) { exit("Connection Failed: " . $connect); } $gr_total = 0; $sql=" select distinct ltrim(rtrim(cmoAddressLine2)) as Street ,ltrim(rtrim(cmoCity)) as City ,ltrim(rtrim(cmoState)) as State ,RIGHT( '00000' + LTRIM( RTRIM( [cmoPostCode] ) ), 5 ) as ZipCode from m1_kf.dbo.SalesOrders left join m1_kf.dbo.Organizations on cmoOrganizationID=ompCustomerOrganizationID where ompRequestedShipDate >'11-01-17' and ompClosed !=-1 " ; $result =odbc_exec($connect,$sql); if(!$result){ exit("Error in SQL"); } $json =array(); //**************************************************************** while ($row = odbc_fetch_array($result)) { $text = $row; $address = implode(", ", $text); //echo $address ."<br>"; $url = file_get_contents('https://maps.googleapis.com/maps/api/geocode/json?address='.urlencode($address).'&key=removed'); echo $url ."<br>"; $resp = file_get_contents($url); $lati = $resp['results'][0]['geometry']['location']['lat']; $longi = $resp['results'][0]['geometry']['location']['lng']; $formatted_address = $resp['results'][0]['formatted_address']; if($lati && $longi && $formatted_address){ $data_arr = array(); array_push( $data_arr, $lati, $longi, $formatted_address ); return $data_arr; echo $data_arr ."<br>"; }else{ return false; } } odbc_close($connect); ?>
  18. I do like the " if " before the array but a line may have AA in a name so it would mess me up. while (!feof($fp)) { $line = fgets($fp); if(substr($line, 117, 2)=='AA' | substr($line, 117, 2)=='AC'){ $lines[substr($line, 69, 5)] =substr($line, 91, 10); print_r($lines); } } fclose($fp); my output looks like Array ( [45925] => 171665579 ) Array ( [45925] => 171665579 [46409] => 171662760 ) I get 45925 twice but it's only in the file once.
  19. That went over my head quickly. so let me restart to make sure I do it right. I download a file from an ftp site then I look through that file and pull out 3 items (order number, assignment number, status) I want to filter by status and I may need to filter more than one time. Different status causes different updates. For now, I am just worried about AA and AC my output so far looks like this when I do a print_r Array ( [48578] => Array ( [item1] => 172686046 [item2] => AA ) ) Array ( [48578] => Array ( [item1] => 172686046 [item2] => AA ) [48577] => Array ( [item1] => 172686047 [item2] => AA ) ) Array ( [48578] => Array ( [item1] => 172686046 [item2] => AA ) [48577] => Array ( [item1] => 172686047 [item2] => AA ) [ ] => Array ( [item1] => [item2] => ) ) Array ( [48578] => Array ( [item1] => 172686046 [item2] => AA ) [48577] => Array ( [item1] => 172686047 [item2] => AA ) [ ] => Array ( [item1] => [item2] => ) [0] => Array ( [item1] => [item2] => ) ) now I need to filter this for only AAs because in real time there will be a lot of different statuses. then update my MSSQL update statement. so how do fix my filter and implode or is there a better way to do this? Then how should I update my sql statement? Thanks $curl = curl_init();$fh = fopen("order_test1.txt", 'w'); curl_setopt($curl, CURLOPT_URL, 'ftp://server.com/Outbox/' . $server_file); curl_setopt($curl, CURLOPT_USERPWD, "removed"); curl_setopt($curl, CURLOPT_RETURNTRANSFER, 1); $result = curl_exec($curl); fwrite($fh, $result); fclose($fh); curl_close($curl); } $lines = []; $fp = fopen('order_test1.txt', 'r'); while (!feof($fp)) { $line = fgets($fp); $index = substr($line, 69, 5); $lines[$index] = ['item1' => substr($line, 91, 10), 'item2' => substr($line, 117, 2)]; } fclose($fp); $order=array_filter($lines,function($AA){ return $AA=='AA' | $AA=='AC';} ); //print_r($order); $key_string = implode(',', array_keys($order)); $sql_update="update uag.dbo.SalesOrders set order_credit status=-1, assignment_num= "orders[value2]" where ompsalesorderid in ($key_string)"; $connect = odbc_connect("removed"); $result = odbc_exec($connect, $sql_update);
  20. When I use the subarray it kills my filter and implode. So i will need to tell the filter and implode which array value I want to use 0 or 1 correct. What should that look like? $key_string = implode(',', array_keys($order)); print_r($key_string); $sql_update="select ompsalesorderid from m1_kf.dbo.SalesOrders where ompsalesorderid in ($key_string)"; $connect = odbc_connect("kforbe", "sa", "H25rlz95!"); $result = odbc_exec($connect, $sql_update);
  21. as a second value to the existing key? $lines = [];$fp = fopen('order_test1.txt', 'r'); while (!feof($fp)) { $line = fgets($fp); $lines[substr($line, 69, 5)] = substr($line, 117, 2); //I would like to add second array value substr($line, 91, 10) print_r($lines); } fclose($fp); $order=array_filter($lines,function($AA){ return $AA=='AA' | $AA=='AC';} ); $key_string = implode(',', array_keys($order));
  22. I need to add a second value to my associative array. $lines[substr($line, 69, 5)] = substr($line, 117, 2); I need to add substr($line, 91, 10)
  23. I got it if (ftp_get($ftp_conn, $local_file, $ftp_path.$server_file, FTP_ASCII)) I reversed $local_file and $ftp_path.$server_file and it works. thank you for the input
  24. Array ( [0] => Inbox [1] => Outbox ) Array ( [0] => CCDATA.TXT ) bool(true) Connected as giajhojju@tf.sftp.online.com
×
×
  • 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.