Jump to content

mongoose00318

Members
 View Profile  See their activity

  • Posts

    253

  • Joined

  • Last visited

 Content Type 

Posts posted by mongoose00318

    Using PDO to Connect to An MS Access DB

    in Microsoft SQL - MSSQL

    Posted · Edited by mongoose00318

    Okay I changed my query to use the asterisk but still it's not working right. For example if I remove the LIKE from the where clause here is part of the data I get back. 

    37 => 
    array (size=14)
      'WORK ORDER' => string '23601611  0001' (length=14)
      0 => string '23601611  0001' (length=14)
      'ENTERPRISE' => string 'BURGER' (length=6)
      1 => string 'BURGER' (length=6)
      'PART NUMBER' => string 'BK4016LD.SFG' (length=12)
      2 => string 'BK4016LD.SFG' (length=12)
      'ITEM_NUMBER_BOM' => string '007' (length=3)
      3 => string '007' (length=3)
      'CATALOGUE_NUMBER_BOM' => string 'FA4016BK.SFGTC' (length=14)
      4 => string 'FA4016BK.SFGTC' (length=14)
      'DESCRIPTION_BOM' => string 'ROUTD FACE 16" FLAME GRILLING SINCE 1954' (length=40)
      5 => string 'ROUTD FACE 16" FLAME GRILLING SINCE 1954' (length=40)
      'RELEASE_WO_BOM' => string '23601611  0001' (length=14)
      6 => string '23601611  0001' (length=14)

    The 'CATALOGUE_NUMBER_BOM clearly starts with FA but if I run this query:  

    SELECT
	[PROD SCHD].[WORK ORDER],
	[PROD SCHD].[ENTERPRISE],
	[PROD SCHD].[PART NUMBER],
	[PPBOMFCB].[ITEM_NUMBER_BOM],
	[PPBOMFCB].[CATALOGUE_NUMBER_BOM],
	[PPBOMFCB].[DESCRIPTION_BOM]
FROM
	[PROD SCHD]
LEFT JOIN
	[PPBOMFCB] ON [PROD SCHD].[WORK ORDER] = [PPBOMFCB].[RELEASE_WO_BOM]
WHERE
	[PROD SCHD].[WORK ORDER] IS NOT NULL AND
	[PPBOMFCB].[CATALOGUE_NUMBER_BOM] LIKE 'FA*'

    I don't get any results?

    Using PDO to Connect to An MS Access DB

    in Microsoft SQL - MSSQL

    Posted

    I have successfully connected to the access database and the queries seem to be surprisingly fast but I'm having some issues getting some queries to work. I can't figure out if it is a difference in syntax between MySQL and MSSQL? For examples: 

                                            SELECT
						[PROD SCHD].[WORK ORDER],
						[PROD SCHD].[ENTERPRISE],
						[PROD SCHD].[PART NUMBER],
						[PPBOMFCB].[ITEM_NUMBER_BOM],
						[PPBOMFCB].[CATALOGUE_NUMBER_BOM],
						[PPBOMFCB].[DESCRIPTION_BOM]
					FROM
						[PROD SCHD]
					LEFT JOIN
						[PPBOMFCB] ON [PROD SCHD].[WORK ORDER] = [PPBOMFCB].[RELEASE_WO_BOM]
					WHERE
						[PROD SCHD].[WORK ORDER] IS NOT NULL

    This works fine. But when I add another condition to the where clause like so: 

                                            SELECT
						[PROD SCHD].[WORK ORDER],
						[PROD SCHD].[ENTERPRISE],
						[PROD SCHD].[PART NUMBER],
						[PPBOMFCB].[ITEM_NUMBER_BOM],
						[PPBOMFCB].[CATALOGUE_NUMBER_BOM],
						[PPBOMFCB].[DESCRIPTION_BOM]
					FROM
						[PROD SCHD]
					LEFT JOIN
						[PPBOMFCB] ON [PROD SCHD].[WORK ORDER] = [PPBOMFCB].[RELEASE_WO_BOM]
					WHERE
						[PROD SCHD].[WORK ORDER] IS NOT NULL AND
						[PPBOMFCB].[PART NUMBER] LIKE '%FA%'

    It won't work at all? The query works fine in Access though.

    Creating a view that shows a specific users activity for the past week that is then able to be graphed

    in MySQL Help

    Posted

    Here is my query so far: 

    SELECT DISTINCT
    (SELECT COUNT(id) FROM :TABLE WHERE CAST(submit_time AS DATE) = CURDATE() - INTERVAL 7 DAY AND user_id = 7) AS Monday,
    (SELECT COUNT(id) FROM :TABLE WHERE CAST(submit_time AS DATE) = CURDATE() - INTERVAL 6 DAY AND user_id = 7) AS Tuesday,
    (SELECT COUNT(id) FROM :TABLE WHERE CAST(submit_time AS DATE) = CURDATE() - INTERVAL 5 DAY AND user_id = 7) AS Wednesday,
    (SELECT COUNT(id) FROM :TABLE WHERE CAST(submit_time AS DATE) = CURDATE() - INTERVAL 4 DAY AND user_id = 7) AS Thursday,
    (SELECT COUNT(id) FROM :TABLE WHERE CAST(submit_time AS DATE) = CURDATE() - INTERVAL 3 DAY AND user_id = 7) AS Friday,
    (SELECT COUNT(id) FROM :TABLE WHERE CAST(submit_time AS DATE) = CURDATE() - INTERVAL 2 DAY AND user_id = 7) AS Saturday,
    (SELECT COUNT(id) FROM :TABLE WHERE CAST(submit_time AS DATE) = CURDATE() - INTERVAL 1 DAY AND user_id = 7) AS Sunday
FROM
	:TABLE ps
WHERE
    ps.user_id = :USER

    The results it is giving me are all wrong so I'm not sure what I am doing wrong. I'm new to using views but I created a few this morning to help me quickly analyze certain data. I want to use the graphing feature in PhpMyAdmin more.

    What I'd like to be able to do is have a view that shows how many records were entered into the production_status table by the user for each day of the last week; or even better each day of this month and then chart it on a bar graph.

    Format all dates in an array returned from PDO

    in PHP Coding Help

    Posted

    Is their a way to format lr.submit_time in every record that is returned in the $results array without doing like a for/foreach loop? Like array_walk() or something similar? 

    $query = "
				SELECT
					lr.*,
					l.make,
					l.model,
					l.part_number,
					l.description,
					pd.job_number,
					pd.line_item,
					pd.enterprise,
					pd.description,
					pd.qty AS order_qty,
					log.name AS user_full_name
				FROM
					leds_requests lr
				LEFT JOIN
					leds l
				ON
					l.id = lr.product_id
				LEFT JOIN
					production_data pd
				ON
					pd.id = lr.order_id
				LEFT JOIN
					login log
				ON
					log.user_id = lr.user_id
				WHERE
					lr.id IN(
					SELECT
						MAX(id)
					FROM
						leds_requests
					WHERE
						status_id = 0
					GROUP BY
						order_id,
						product_id
				) AND lr.id NOT IN(
					SELECT
						id
					FROM
						leds_requests
					WHERE
						lr.id = id AND status_id IN(1, 2, 3, 4)
				)
				GROUP BY
					order_id,
					product_id,
					job_number
	";
	$statement = $pdo->prepare($query);
	$statement->execute();
	$results = $statement->fetchAll();
	
	echo json_encode($results);

     

    Having an issue with LIMIT when trying to select most recent record

    in MySQL Help

    Posted

    @Barand There was additional code below the exit() that I thought was irrelevant to the post and I was trying to keep it short for everyone. Turns out the problem was in the code below the exit(); lol...it was there for debugging purposes. The comment is old; I originally was going pass all that to the function; but instead I just pass the order_id to it now. The rest of is provided elsewhere from other parts of the application. I should have the function buttoned up sometime today and I'll post it for review. It's a part of a big new feature I'm building into the application. I'm looking forward to getting it out to the end user.

    Having an issue with LIMIT when trying to select most recent record

    in MySQL Help

    Posted

    I've been having issues with this since Friday and still haven't resolved it. If I call that function with a $order_id set to 7601 and the SQL clause says "LIMIT 1" I get a blank array that comes back.

    If I change it to LIMIT 2; I get the result I expected...but not two records...just the one? There are no blank records in the DB from what I see and if I run that same query in PHPMyAdmin it returns the results I expect? 

    //build LED request buttons
function build_led_request_btn($order_id, $pdo) {
	
	//info to gather
	//$order_id, $job_number, $enterprise, $request_id, $request_status
	
	//get request status
	$query = "SELECT * FROM leds_requests WHERE order_id = :order_id ORDER BY id DESC LIMIT 1";
	$statement = $pdo->prepare($query);
	$statement->execute(['order_id' => $order_id]);
	$count_records = $statement->rowCount();
	
	return $statement->fetch()['status_id'];
	exit();

}

    image.png.81f260dc28de2cf4ad026e173a1878c6.png

    jQuery .clone()

    in Javascript Help

    Posted

    I'm trying to create a simple form that allows the user to add another item when they click a button. Here is my HTML: 

    <div class="row led-item">
   <div class="form-group col-10">
      <label for="exampleFormControlSelect1">Product</label>
      <select class="form-control" id="exampleFormControlSelect1">
         <option>1</option>
         <option>2</option>
         <option>3</option>
         <option>4</option>
         <option>5</option>
      </select>
   </div>
   <div class="form-group col-2">
      <label for="exampleFormControlInput1">Quantity</label>
      <input type="email" class="form-control" id="exampleFormControlInput1" placeholder="name@example.com">
   </div>
</div>

    My JS: 

    //add item to request leds forms
$('#request-leds-add-item').click(function() {
    $('.led-item:first').clone().appendTo('.led-item');
});

    But instead of it cloning the .led-item and inserting it after the first instance of it; it inserts it into the the first instance of .led-item like so: 

    <div class="row led-item">
   <div class="form-group col-10">
      <label for="exampleFormControlSelect1">Product</label>
      <select class="form-control" id="exampleFormControlSelect1">
         <option>1</option>
         <option>2</option>
         <option>3</option>
         <option>4</option>
         <option>5</option>
      </select>
   </div>
   <div class="form-group col-2">
      <label for="exampleFormControlInput1">Quantity</label>
      <input type="email" class="form-control" id="exampleFormControlInput1" placeholder="name@example.com">
   </div>
   <div class="row led-item">
      <div class="form-group col-10">
         <label for="exampleFormControlSelect1">Product</label>
         <select class="form-control" id="exampleFormControlSelect1">
            <option>1</option>
            <option>2</option>
            <option>3</option>
            <option>4</option>
            <option>5</option>
         </select>
      </div>
      <div class="form-group col-2">
         <label for="exampleFormControlInput1">Quantity</label>
         <input type="email" class="form-control" id="exampleFormControlInput1" placeholder="name@example.com">
      </div>
   </div>
</div>

     

    MySQL Backup using PDO

    in MySQL Help

    Posted

    @kicken How would I active a shell script through PHP if I wanted to trigger the backup based on certain events that happen?

    @benanamen Audit Logging looks really interesting. I'd probably need something that parses the logs and makes it easier to search them, etc...something like Monyog? I'm not too familiar with a lot of these more advanced performance and logging features of MySQL. Any additional advice would be welcome!

    Select MAX(date) from a group of records?

    in MySQL Help

    Posted

    I'm trying to select all of the most recent records for each 'order_id' with the following table structure:

    image.thumb.png.6fc0e45708617d4277e15c47c64781ea.png

    So with that example above I would want record 6840 because it is the newest record for order 5157. 

    SELECT
    pda.*
FROM
    production_data_archive pda
INNER JOIN (
    SELECT MAX(insert_time) as MAX_insert_time
    FROM production_status_archive pda2
    GROUP BY order_id
) ON pda.order_id = pda2.order_id

    I'm not sure what I'm doing wrong with my query but I'm getting an error.

    Bootstrap Popover On Hover Issue

    in Javascript Help

    Posted · Edited by mongoose00318
    made title more specific

    I have some Bootstrap popovers I'm trying to use and they work but you have to over over the element twice?

    Here is my JS: 

                    //initiate popover
		$('body').on('mouseover', '.btn_change_order_status_dialog', function() {
			
			if($(this).text() !== 'Not Started') {
				
				var btn = $(this);
				//perform query to determine most recent order status
				$.ajax({
					url: 'scripts/order_status.php',
					method: 'GET',
					data: {action: 'get_order_status_history_for_dept', order_id: $(this).attr('data-order-id'), dept_code: $(this).attr('data-dept-code')},
					success: function(data) {
						btn.attr('data-content', data);
					}
				});
			}
			
			$(this).popover();
		});

     

    Here is my PHP that generates the HTML for the buttons: 

    //build change order status buttons
function build_change_order_status_btns($id, $user_dept_code, $job_number, $enterprise, $status) {
	
	// define output button array
	$btn = [];

	// produce the buttons
	foreach ( $status as $dept => $stat ) {
		
		//check if status ID is an array holding additonal information
		$status_id = is_array($stat) ? $stat['status_id'] : $stat;
		
		//retrieve expected complete time
		if ( isset( $stat[ 'expected_complete_time' ] ) ) {
			$today = new DateTime( "now" );
			$expected_complete_time = new DateTime( $stat[ 'expected_complete_time' ] );
			$eta = $today->diff( $expected_complete_time );

			$str_eta = $eta->format( '%r%d' );

			if ( $expected_complete_time->format( 'd/m/Y' ) === $today->format( 'd/m/Y' ) ) {
				$eta = 'Today';
			} else {
				$eta = ( $str_eta + 1 == 1 ) ? $str_eta + 1 . ' day': $str_eta + 1 . ' days';
			}

		} else {
			$eta = '';
		}
		
		// enabled if user dept is 1,2,3 or the current dept matches the user's dept
		$enabled = in_array( $user_dept_code, [ 1, 2, 3 ] ) || in_array( $dept, explode( ',', $user_dept_code ) ) ? '' : ' disabled';

		// populate the dynamic values. you should actually use a template here with replaceable tags, then only produce the button that matches the current status value
		$buttons[ 1 ] = "<button type='button' class='btn btn-warning btn-sm btn_change_order_status_dialog rounded-0' data-order-id='$id' data-order-number='$job_number' data-order-enterprise='$enterprise' data-dept-code='$dept' data-toggle='popover' data-trigger='hover' data-html='true' title='Order Status Summary' data-content=''$enabled>ETA: $eta</button>";
		
		$buttons[ 2 ] = "<button type='button' class='btn btn-danger btn-sm btn_change_order_status_dialog rounded-0' data-order-id='$id' data-order-number='$job_number' data-order-enterprise='$enterprise' data-dept-code='$dept' data-toggle='popover' data-trigger='hover' data-html='true' title='Order Status Summary' data-content=''$enabled>Delayed</button>";
		
		$buttons[ 3 ] = "<button type='button' class='btn-primary btn-sm btn_change_order_status_dialog rounded-0' data-order-id='$id' data-order-number='$job_number' data-order-enterprise='$enterprise' data-dept-code='$dept' data-toggle='popover' data-trigger='hover' data-html='true' title='Order Status Summary' data-content=''$enabled>Finished</button>";
		
		$buttons[ 0 ] = "<button type='button' class='btn btn-secondary btn-sm btn_change_order_status_dialog rounded-0' data-order-id='$id' data-order-number='$job_number' data-order-enterprise='$enterprise' data-dept-code='$dept'$enabled>Not Started</button>";

		// get the button that matches the current status value for each dept
		$btn[ $dept ] = $buttons[ $status_id ];

	}
	
	// examine the results
	return $btn;
}

     

    Also, I've attached a video demonstrating the problem...

     

    for Loop for Grouping in PHPSpreadsheet

    in PHP Coding Help

    Posted

    I've tried adjusting the setOutlineLevel but it is just grouping all of the rows together as one group right now. From my understanding the setOutlineLevel is for nesting groups anyhow...not sure what is going wrong because this code works fine: 

    for ($row = 2; $row <= 10; ++$row) {
    $spreadsheet->getActiveSheet()
        ->getRowDimension($row)
            ->setOutlineLevel(1)
            ->setVisible(true)
            ->setCollapsed(false);
}

for ($row = 12; $row <= 20; ++$row) {
    $spreadsheet->getActiveSheet()
        ->getRowDimension($row)
            ->setOutlineLevel(1)
            ->setVisible(true)
            ->setCollapsed(false);
}

    I can't spot the difference between what I'm doing with: 

    //setup row groupings
foreach ( $groupings as $g ) {
	$end = isset($g['end'] ) ? $g['end'] : $g['start'];
	for ( $row = $g['start']; $row <= $end; ++$row ) {
		$spreadsheet->getActiveSheet()
			->getRowDimension($row)
				->setOutlineLevel(1)
				->setVisible(true)
				->setCollapsed(false);

	}
}

    and the code above it...

    for Loop for Grouping in PHPSpreadsheet

    in PHP Coding Help

    Posted

    Well..I've tried this...but it isn't quite working... 

    $worksheet = $spreadsheet->getActiveSheet();
// Get the highest row number and column letter referenced in the worksheet
$highestRow = $worksheet->getHighestRow(); // e.g. 10
$highestColumn = $worksheet->getHighestColumn(); // e.g 'F'
// Increment the highest column letter
$highestColumn++;

$job_num_chk = '';
$groupings = [];

//format the inserted records
for ($row = 2; $row <= $highestRow; ++$row) {
	//$spreadsheet->getActiveSheet()->getRowDimension($row)->setRowHeight(53);
	for ($col = 'A'; $col != $highestColumn; ++$col) {
		
		$spreadsheet->getActiveSheet()->getStyle($col . $row)->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_TOP);
		$spreadsheet->getActiveSheet()->getStyle($col . $row)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT);
		
		if ($col == 'B') {
			$cur_job_num = $spreadsheet->getActiveSheet()->getCell($col . $row)->getValue(); //get current job number
			
			//check if job number is different
			if ( $job_num_chk !== $cur_job_num ) {

				/*
				$spreadsheet->getActiveSheet()->insertNewRowBefore($row, 1); //insert dividing row
				
				//set dividing row to black
				$spreadsheet->getActiveSheet()->getStyle('A'.$row.':N'.$row)->getFill()
					->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
					->getStartColor()->setARGB('000000');
				*/
				
				$job_num_chk = $cur_job_num; //update job number
			}
			
			$row_m = $row-1;
			$row_a = $row+1;
			
			$above = $spreadsheet->getActiveSheet()->getCell($col . $row_m)->getValue();
			$below = $spreadsheet->getActiveSheet()->getCell($col . $row_a)->getValue();

			if ( $cur_job_num !== $above ) {
				$groupings[$cur_job_num]['start'] = $row;
			}
			elseif ( $cur_job_num !== $below ) {
				$groupings[$cur_job_num]['end'] = $row;
			}
			
		}

	}
	
}

//setup row groupings
foreach ( $groupings as $g ) {
	$end = isset($g['end'] ) ? $g['end'] : $g['start'];
	for ( $row = $g['start']; $row <= $end; ++$row ) {
		$spreadsheet->getActiveSheet()
			->getRowDimension($row)
				->setOutlineLevel(1)
				->setVisible(true)
				->setCollapsed(false);
	}
}

    It seems to have made two large groups:

    image.thumb.png.f82c64b6c08d7ca5008b8960d1417dd7.png

    But I've looked at my array and it seems to be correct: 

    Array
(
    [22005572] => Array
        (
            [start] => 2
            [end] => 3
        )

    [22039882] => Array
        (
            [start] => 4
        )

    [23521431] => Array
        (
            [start] => 5
            [end] => 6
        )

    [23616901] => Array
        (
            [start] => 7
        )

    [23617551] => Array
        (
            [start] => 8
        )

    [23617561] => Array
        (
            [start] => 9
        )

    [23617851] => Array
        (
            [start] => 10
            [end] => 14
        )

    [23618511] => Array
        (
            [start] => 15
        )

    [23618761] => Array
        (
            [start] => 16
        )

    [22016952] => Array
        (
            [start] => 17
            [end] => 18
        )

    [22017812] => Array
        (
            [start] => 19
            [end] => 20
        )

    [22023852] => Array
        (
            [start] => 21
            [end] => 25
        )

    [22023892] => Array
        (
            [start] => 26
            [end] => 28
        )

    [22027752] => Array
        (
            [start] => 29
            [end] => 30
        )

    [22031712] => Array
        (
            [start] => 31
            [end] => 32
        )

    [22032642] => Array
        (
            [start] => 33
            [end] => 34
        )

    [22036842] => Array
        (
            [start] => 35
        )

    [22040842] => Array
        (
            [start] => 36
        )

    [22961571] => Array
        (
            [start] => 37
        )

    [22978751] => Array
        (
            [start] => 38
        )

    [22978831] => Array
        (
            [start] => 39
            [end] => 40
        )

    [22992301] => Array
        (
            [start] => 41
        )

    [22992321] => Array
        (
            [start] => 42
        )

    [22992331] => Array
        (
            [start] => 43
        )

    [23585641] => Array
        (
            [start] => 44
        )

    [22010852] => Array
        (
            [start] => 45
            [end] => 46
        )

    [22007412] => Array
        (
            [start] => 47
            [end] => 48
        )

    [22031192] => Array
        (
            [start] => 49
            [end] => 54
        )

    [22039462] => Array
        (
            [start] => 55
        )

    [22042092] => Array
        (
            [start] => 56
        )

    [23584291] => Array
        (
            [start] => 57
            [end] => 58
        )

    [23615841] => Array
        (
            [start] => 59
        )

    [23615921] => Array
        (
            [start] => 60
        )

    [23616201] => Array
        (
            [start] => 61
        )

    [23616221] => Array
        (
            [start] => 62
        )

    [60266410] => Array
        (
            [start] => 63
        )

    [29100510] => Array
        (
            [start] => 139
            [end] => 68
        )

    [25294050] => Array
        (
            [start] => 69
            [end] => 74
        )

    [25294060] => Array
        (
            [start] => 75
            [end] => 77
        )

    [25294070] => Array
        (
            [start] => 78
            [end] => 83
        )

    [25294080] => Array
        (
            [start] => 84
            [end] => 89
        )

    [25294680] => Array
        (
            [start] => 90
            [end] => 93
        )

    [25294690] => Array
        (
            [start] => 94
            [end] => 99
        )

    [23616371] => Array
        (
            [start] => 100
        )

    [23616381] => Array
        (
            [start] => 101
        )

    [23616391] => Array
        (
            [start] => 102
        )

    [23616491] => Array
        (
            [start] => 103
            [end] => 104
        )

    [23616731] => Array
        (
            [start] => 105
            [end] => 106
        )

    [23616741] => Array
        (
            [start] => 107
        )

    [23551851] => Array
        (
            [start] => 108
        )

    [22003322] => Array
        (
            [start] => 109
        )

    [22004302] => Array
        (
            [start] => 110
            [end] => 111
        )

    [22011072] => Array
        (
            [start] => 112
        )

    [22015832] => Array
        (
            [start] => 113
            [end] => 117
        )

    [22024612] => Array
        (
            [start] => 118
        )

    [22026012] => Array
        (
            [start] => 119
        )

    [22027702] => Array
        (
            [start] => 120
            [end] => 121
        )

    [22028302] => Array
        (
            [start] => 122
        )

    [22028382] => Array
        (
            [start] => 123
        )

    [22031532] => Array
        (
            [start] => 124
            [end] => 126
        )

    [22973641] => Array
        (
            [start] => 127
        )

    [22979331] => Array
        (
            [start] => 128
        )

    [29089920] => Array
        (
            [start] => 129
            [end] => 133
        )

    [23587361] => Array
        (
            [start] => 134
            [end] => 136
        )

    [23607111] => Array
        (
            [start] => 137
        )

    [23615581] => Array
        (
            [start] => 138
        )

    [23616961] => Array
        (
            [start] => 140
            [end] => 141
        )

    [22012662] => Array
        (
            [start] => 142
        )

    [22018892] => Array
        (
            [start] => 143
            [end] => 146
        )

    [22022802] => Array
        (
            [start] => 147
        )

    [22027922] => Array
        (
            [start] => 148
        )

    [22033592] => Array
        (
            [start] => 149
        )

)

     

    for Loop for Grouping in PHPSpreadsheet

    in PHP Coding Help

    Posted

    I'm trying to dynamically group a range of rows using PHPSpreadsheet and having a lot of trouble thinking through the logic. My code so far looks like this: 

    $worksheet = $spreadsheet->getActiveSheet();
// Get the highest row number and column letter referenced in the worksheet
$highestRow = $worksheet->getHighestRow(); // e.g. 10
$highestColumn = $worksheet->getHighestColumn(); // e.g 'F'
// Increment the highest column letter
$highestColumn++;

$job_num_chk = '';
$grp_start_row = '';

//format the inserted records
for ($row = 2; $row <= $highestRow; ++$row) {
	//$spreadsheet->getActiveSheet()->getRowDimension($row)->setRowHeight(53);
	for ($col = 'A'; $col != $highestColumn; ++$col) {
		
		$spreadsheet->getActiveSheet()->getStyle($col . $row)->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_TOP);
		$spreadsheet->getActiveSheet()->getStyle($col . $row)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT);
		
		if ($col == 'B') {
			$cur_job_num = $spreadsheet->getActiveSheet()->getCell($col . $row)->getValue(); //get current job number
			
			//check if job number is different
			if ( $job_num_chk !== $cur_job_num ) {
				$spreadsheet->getActiveSheet()->insertNewRowBefore($row, 1); //insert dividing row
				
				//set dividing row to black
				$spreadsheet->getActiveSheet()->getStyle('A'.$row.':N'.$row)->getFill()
					->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
					->getStartColor()->setARGB('000000');
				
				$job_num_chk = $cur_job_num; //update job number
			}
			
		}

	}
	
}

    This line:  

    $grp_start_row = '';

    Is where I'm hitting a mental wall...here's what the spreadsheet looks like so far:

    image.thumb.png.1b54807a273bad673568f8c6c06610a6.png

    I want to be able to group the rows 101-105 in that example like below:

    image.thumb.png.dc706fe7d714ecbc45e55473e51835d9.png

    I need to be able to determine where the start row is and the end row is in order to use the grouping feature (https://phpspreadsheet.readthedocs.io/en/latest/topics/recipes/#groupoutline-a-row)

    Thanks everyone.

×
×
  • 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.