Inquiries about cell merge, how to save comments in DB, how to set formulas, etc

[Richard]

Hi.

I am trying to develop a system (monthly production planning system) like the attached image using PQ Grid.
In one product name(B in the attached image), day production (Day), night production (Night), the very day shipment (Shipment), and stock (Stock) are one set.
In one set, data is the same from Product Name(B in the attached image) to Pre Month Stock(H in the attached image).
Columns E, F, H, J ~ AN (Day, Night, Shipment) are cells where data can be input, and data input is impossible for all other columns.

Here's what I need:

1) I want to apply a merge from Product Name(B in the attached image) to Pre Month Stock(H in the attached image).
  - As a result of applying Auto merge (https://paramquery.com/pro/demos/merge_cell), the cell is merged and displayed.     
    But the efficiency data (E2 in the attached image) is input different from the existing data,
    There was a problem that the cell could not be merged (attached image 2). I want to know how to solve this.

2) When adding text (comments) to the merged cell and specifying text color, text size, back-ground color etc.
    I want to know how to store this in the DataBase, get the data stored in the DataBase when connecting to the system for the first time, and display it on the screen.
    It is the same in case of adding a comment as in K2 cell.

3) When I enter daily data (Day, Night, Shipment),
    stock data is automatically calculated (the previous day's stock + day production + nightly production - the very day shipment).
    I want to know how to make it appear in stock cell.
    (If Product Name (column B) is AA, the formula is: K5 = J5 + K2 + K3 - K4)

4) I want to know how to display the sum of each (Product Type A, Product Type B, Shipment) at the bottom of a row.



As I am a beginner, I have a lot of questions.
I hope you can kindly understand and help.

[paramvir]

1. Please share a jsfiddle so that I can look into it.

2. Cell styles are saved in meta data associated with every row i.e, rowData.pq_cellstyle

Similarly cell comments are stored in rowData.pq_cellattr

Reference: https://paramquery.com/pro/tutorial#topic-metadata

Example: https://paramquery.com/pro/demos/row_styles

so you can create new columns pq_cellstyle and pq_cellattr in your DB table to store and retrieve meta data of every row.

3. Excel formulas are stored in pq_fn meta data, Example: https://paramquery.com/pro/demos/excel_formula
pq_fn can be saved in DB table similar to above meta data.

4. Please refer same example https://paramquery.com/pro/demos/excel_formula

PS: If you are a beginner js and DB progammer, then saving the whole table in xlsx format would be a much easier option.

[Richard]

Hi.
I am sharing the program in relation to question No.1 above, so please review it.
https://jsfiddle.net/elcid1/eujz9oam/6/

Thank you.

[paramvir]

Data is missing from your jsfiddle. Local data can be used instead of remote data to demonstrate the issue.

[Richard]

I've added the data as you requested, so please review the code.

https://jsfiddle.net/elcid1/eujz9oam/9/

Thank you.

[paramvir]

Thanks for working jsfiddle.

Since your jsfiddle looks different from the screenshot shared in post 1, can you please mention the issue faced in context of jsfiddle.

As a side note, Please keep the sum of frozen columns width less than grid width so as to display horizontal scrollbar. There were 14 frozen columns in your jsfiddle, new jsfiddle after removing the frozen columns.

https://jsfiddle.net/o2sjr083/

[Richard]

Auto merge is applied to the 4th column (Worker).
Initially, the entire data in rows 1~5 is '44'.
When the data stored in the DB was searched, only '44' was displayed.
After I input '22' in the first cell and save it in DB
When the data is displayed again by pressing the F5 key
As shown in the jsfiddle screen
'22' and '44' are displayed respectively.

What I want is with Auto merge set
If I enter '22' in the first cell, all the values in cells 2~5 also change.
I want only '22' to be displayed on the screen.

Thank you.

[paramvir]

This is the standard working of merge cells, only the left top cell in a merged cell is updated.

To make it work as per your requirement, you can update the cell values in all the matching rows in your db table when a change is posted to remote script.

[Richard]

Hi paramvir.
Thank you very much for answering my questions continuously.

Regarding the problem of merge cells, referring to your comment,
When modifying data in merge cells (Worker, Efficiency, C/T, Duty hours 400, Duty hours 600)
Server-side program to update all data of the same group
As a result of coding and executing each of the two types as shown below, an error occurs and the update does not work.
Data is in the form of 5 cd_kinds in the same cd_group.
(cd_kind: 1-Day, 2-Night, 3-Shipment, 4-Etc, 5-Stock)

I'd appreciate it if you could let me know what the problem is.


//update single record in db.
function updateSingle($pdo, $r)
{
   $query = "update pp2_month_new set qty_worker = ?, eff_product = ?, cycle_time = ?, qty_not_ot = ?, qty_ot = ?, ";

   $total_day = date('t', strtotime($r['mt_product']));
   for($j=1; $j<=$total_day; $j++){
      $query .= "qty$j = ?, ";
   }
   $query   = substr($query, 0, -2);


   $query .= " where id = ?";

   $stmt = $pdo->prepare($query);
   $result = $stmt->execute(array($r['qty_worker'], $r['eff_product'], $r['cycle_time'], $r['qty_not_ot'], $r['qty_ot'],
      $r['qty1'],  $r['qty2'],  $r['qty3'],  $r['qty4'],  $r['qty5'],  $r['qty6'],  $r['qty7'],  $r['qty8'],  $r['qty9'],  $r['qty10'],
      $r['qty11'], $r['qty12'], $r['qty13'], $r['qty14'], $r['qty15'], $r['qty16'], $r['qty17'], $r['qty18'], $r['qty19'], $r['qty20'],
      $r['qty21'], $r['qty22'], $r['qty23'], $r['qty24'], $r['qty25'], $r['qty26'], $r['qty27'], $r['qty28'], $r['qty29'], $r['qty30'], $r['qty31'],
      $r['id']));
   if($result == false) {
      throw new Exception(print_r($stmt->errorInfo(), 1).PHP_EOL.$query);
   }

   // Method 1
   if($r['cd_kind'] == 0){
      $cd_group   = $r['cd_group'];
      $qty_worker   = $r['qty_worker'];
      $eff_product   = $r['eff_product'];
      $cycle_time   = $r['cycle_time'];
      $qty_not_ot   = $r['qty_not_ot'];
      $qty_ot      = $r['qty_ot'];
   }
   elseif($r['cd_kind'] == 4){
      $query  = "update pp2_month_new set qty_worker = '$qty_worker', eff_product = '$eff_product', cycle_time = '$cycle_time', ";
      $query .= "qty_not_ot = '$qty_not_ot', qty_ot = '$qty_ot' where cd_group = '$cd_group' and cd_kind > 0";
      $result = mysqli_query($conn, $query) or die("Update fail!!");
   }
}

//update multiple records in db.
function updateList($updateList)
{
   $pdo = getDBH();
   foreach($updateList as &$r){

      // Method 2
      if($r['cd_kind'] == 0){
         $qty_worker      = $r['qty_worker'];
         $eff_product      = $r['eff_product'];
         $cycle_time      = $r['cycle_time'];
         $qty_not_ot      = $r['qty_not_ot'];
         $qty_ot         = $r['qty_ot'];
      }
      else{
         $r['qty_worker']           = $qty_worker;
         $r['eff_product']   = $eff_product;
         $r['cycle_time']           = $cycle_time;
         $r['qty_not_ot']           = $qty_not_ot;
         $r['qty_ot']      = $qty_ot;
      }

      updateSingle($pdo, $r);
   }
}

[paramvir]

There won't be much change in updateList method. it's also required to use list.oldList to pass on to updateSingle method so as to search by old values.

Code: [Select]

function updateList($updateList, $oldList)
{
    $pdo = getDBH();
    foreach ($updateList as $key => $val)
    {
        updateSingle($pdo, $val, $oldList[ $key ] );
    }
}


In updateSingle method, it would require multiple queries, one for each merge field present in $oldList[$key].

update table set merge_field1 = ? where merge_field1 = old value of merge_field1 from $oldList[$key]

update table set merge_field2 = ? where merge_field2 = old value of merge_field2 from $oldList[$key]

and so on..


Continued in this post: https://paramquery.com/forum/index.php?topic=4305.0