在DT中,两个可编辑的列互相计算。

我在我的 DataTable 我试图让对方计算掉的。Override CPP ($)Override CPM ($). 当用户编辑 Override CPP ($) 列,我能够更新 Override CPM ($). 然而,当用户去更新 Override CPM ($)Override CPP ($) 列没有相应更新。

我在JS_callback中的逻辑是否有问题,我是怎么做的?

cpm = (cpp*100)/(population/1000)
cpp = cpm(population/1000) / 100

数据的

Parent
structure(list(Market = c("ABILENE, TX", "AKRON, OH"), `SQAD CPP ($)` = c(10, 
49), `SQAD CPM ($)` = c(22, 30), `Override CPP ($)` = c(0, 0), 
    `Override CPM ($)` = c(0, 0)), .Names = c("Market", "SQAD CPP ($)", 
"SQAD CPM ($)", "Override CPP ($)", "Override CPM ($)"), row.names = c(NA, 
-2L), class = "data.frame")

Child
structure(list(Market = c("ABILENE, TX", "ABILENE, TX", "ABILENE, TX", 
"ABILENE, TX", "ABILENE, TX", "ABILENE, TX", "ABILENE, TX", "ABILENE, TX", 
"ABILENE, TX", "AKRON, OH", "AKRON, OH", "AKRON, OH", "AKRON, OH", 
"AKRON, OH", "AKRON, OH", "AKRON, OH", "AKRON, OH", "AKRON, OH"
), Daypart = c(" Podcast", " Streaming/Digital Audio", "Afternoon Drive", 
"Daytime", "Evening", "Mon-Fri Average", "Mon-Sun Average", "Morning Drive", 
"Weekend", " Podcast", " Streaming/Digital Audio", "Afternoon Drive", 
"Daytime", "Evening", "Mon-Fri Average", "Mon-Sun Average", "Morning Drive", 
"Weekend"), `Mix (%)` = c(10L, 10L, 10L, 10L, 10L, 10L, 5L, 15L, 
10L, 10L, 10L, 10L, 10L, 10L, 10L, 5L, 15L, 10L), `Spot:60 (%)` = c(4, 
4, 4, 4, 4, 4, 2, 6, 4, 4, 4, 4, 4, 4, 4, 2, 6, 4), `Spot:30 (%)` = c(6, 
6, 6, 6, 6, 6, 3, 9, 6, 6, 6, 6, 6, 6, 6, 3, 9, 6), `SQAD CPP ($)` = c(10, 
6, 27, 31, 44, 32, 31, 26, 26, 34, 21, 170, 156, 112, 151, 136, 
177, 95), `SQAD CPM ($)` = c(21, 13, 57.6, 64.8, 93.6, 68.4, 
64.8, 54, 54, 21, 13, 104.5, 96.1, 69, 93, 83.6, 108.7, 58.5), 
    `Override CPP ($)` = c(10, 6, 27, 31, 44, 32, 31, 26, 26, 
    34, 21, 170, 156, 112, 151, 136, 177, 95), `Override CPM ($)` = c(21, 
    13, 57.63, 64.83, 93.64, 68.43, 64.83, 54.03, 54.03, 21, 
    13, 104.49, 96.13, 68.96, 92.99, 83.59, 108.67, 58.51), population = c(47200L, 
    47200L, 47200L, 47200L, 47200L, 47200L, 47200L, 47200L, 47200L, 
    162700L, 162700L, 162700L, 162700L, 162700L, 162700L, 162700L, 
    162700L, 162700L), slider_60s = c(0.4, 0.4, 0.4, 0.4, 0.4, 
    0.4, 0.4, 0.4, 0.4, 0.4, 0.4, 0.4, 0.4, 0.4, 0.4, 0.4, 0.4, 
    0.4), slider_30s = c(0.6, 0.6, 0.6, 0.6, 0.6, 0.6, 0.6, 0.6, 
    0.6, 0.6, 0.6, 0.6, 0.6, 0.6, 0.6, 0.6, 0.6, 0.6)), .Names = c("Market", 
"Daypart", "Mix (%)", "Spot:60 (%)", "Spot:30 (%)", "SQAD CPP ($)", 
"SQAD CPM ($)", "Override CPP ($)", "Override CPM ($)", "population", 
"slider_60s", "slider_30s"), class = "data.frame", row.names = c(NA, 
-18L))

编码

# Bind the market level and mix breakout data together for the final table
market_mix_table <- reactive({
  # Take a dependency on input$goButton
  input$goButton

  isolate({
    markets <- market_costings_gross_net()
    mix_breakout <- mix_breakout_digital_elements()
    # Make the dataframe
    # This must be met length(children) == nrow(dat)
    Dat <- NestedData(
      dat = markets,
      children = split(mix_breakout, mix_breakout$Market)
    )
    return(Dat)
  })
})

# Render the table
output$daypartTable <- DT::renderDataTable({
  Server = FALSE
  # Whether to show row names (set TRUE or FALSE)
  rowNames <- FALSE
  colIdx <- as.integer(rowNames)
  # The data
  Dat <- market_mix_table()
  parentRows <- which(Dat[,1] != "")
  callback_js = JS(
    "function onUpdate(updatedCell, updatedRow, oldValue) {};",
    sprintf("var parentRows = [%s];", toString(parentRows-1)),
    sprintf("var j0 = %d;", colIdx),
    "var nrows = table.rows().count();",
    "for(var i=0; i < nrows; ++i){",
    "  if(parentRows.indexOf(i) > -1){",
    "    table.cell(i,j0).nodes().to$().css({cursor: 'pointer'});",
    "  }else{",
    "    table.cell(i,j0).nodes().to$().removeClass('details-control');",
    "  }",
    "}",
    "",
    "// make the table header of the nested table",
    "var format = function(d, childId){",
    "  if(d != null){",
    "    var html = ",
    "      '<table class=\"display compact hover\" ' + ",
    "      'style=\"padding-left: 30px;\" id=\"' + childId + '\"><thead><tr>';",
    "    for(var key in d[d.length-1][0]){",
    "      html += '<th>' + key + '</th>';",
    "    }",
    "    html += '</tr></thead><tfoot><tr>'",
    "    for(var key in d[d.length-1][0]){",
    "      html += '<th></th>';",
    "    }",
    "    return html + '</tr></tfoot></table>';",
    "  } else {",
    "    return '';",
    "  }",
    "};",
    "",
    "// row callback to style the rows of the child tables",
    "var rowCallback = function(row, dat, displayNum, index){",
    "  if($(row).hasClass('odd')){",
    "    $(row).css('background-color', 'white');",
    "    $(row).hover(function(){",
    "      $(this).css('background-color', 'lightgreen');",
    "    }, function() {",
    "      $(this).css('background-color', 'white');",
    "    });",
    "  } else {",
    "    $(row).css('background-color', 'white');",
    "    $(row).hover(function(){",
    "      $(this).css('background-color', 'lightblue');",
    "    }, function() {",
    "      $(this).css('background-color', 'white');",
    "    });",
    "  }",
    "};",
    "",
    "// header callback to style the header of the child tables",
    "var headerCallback = function(thead, data, start, end, display){",
    "  $('th', thead).css({",
    "    'color': 'black',",
    "    'background-color': 'white'",
    "  });",
    "};",
    "",
    "// make the datatable",
    "var format_datatable = function(d, childId, rowIdx){",
    "  // footer callback to display the totals",
    "  // and update the parent row",
    "  var footerCallback = function(tfoot, data, start, end, display){",
    "    $('th', tfoot).css('background-color', '#F5F2F2');",
    "    var api = this.api();",
  ......
  ...... This works
    "// update the Override CPM when the Override CPP is changed",
    "    var col_override_cpp = api.column(7).data();",
    "    var col_population = api.column(9).data();",
    "    for(var i = 0; i < col_override_cpp.length; i++){",
    "      api.cell(i,8).data(((parseInt(col_override_cpp[i])*100)/(parseInt(col_population[i])/1000)).toFixed(0));",
    "    }",
  ......
  ......
  ............
  ......This one doesn't 
    "// update the Override CPP when the Override CPM is changed",
    "    var col_override_cpm = api.column(8).data();",
    "    for(var i = 0; i < col_override_cpm.length; i++){",
    "      api.cell(i,7).data(((parseInt(col_override_cpm[i])*parseInt(col_population[i])/1000)/(100)).toFixed(0));",
    "    }",
    "// Update the spot mixes",
    "    var col_mix_percentage = api.column(2).data();",
    "    var col_mix60_mix30 = api.column(10).data();",
    "    var col_mix30_mix15 = api.column(11).data();",
    "    for(var i = 0; i < col_mix_percentage.length; i++){",
    "      api.cell(i,3).data((parseFloat(col_mix_percentage[i])*parseFloat(col_mix60_mix30[i])).toFixed(1));",
    "      api.cell(i,4).data((parseFloat(col_mix_percentage[i])*parseFloat(col_mix30_mix15[i])).toFixed(1));",
    "    }",
    "// Make the footer sums",
    "    api.columns().eq(0).each(function(index){",
    "      if(index == 0) return $(api.column(index).footer()).html('Mix Total');",
    "      var coldata = api.column(index).data();",
    "      var total = coldata",
    "          .reduce(function(a, b){return parseInt(a) + parseInt(b)}, 0);",
    "      if(index == 3 || index == 4 ||index == 5 || index == 6 || index==7 || index==8) {",
    "        $(api.column(index).footer()).html('');",
    "      } else {",
    "        $(api.column(index).footer()).html(total);",
    "      }",
    "      if(total == 100) {",
    "        $(api.column(index).footer()).css({'color': 'green'});",
    "      } else {",
    "        $(api.column(index).footer()).css({'color': 'red'});",
    "      }",
    "    })",
    "  // update the parent row",
    "    var col_share = api.column(2).data();",
    "    var col_CPP = api.column(5).data();",
    "    var col_CPM = api.column(6).data();",
    "    var col_Historical_CPP = api.column(7).data();",
    "    var col_Historical_CPM = api.column(8).data();",
    "    var CPP = 0, CPM = 0, Historical_CPP = 0, Historical_CPM = 0;",
    "    for(var i = 0; i < col_share.length; i++){",
    "      CPP += (parseInt(col_share[i])*parseInt(col_CPP[i]).toFixed(0));",
    "      CPM += (parseInt(col_share[i])*parseInt(col_CPM[i]).toFixed(0));",
    "      Historical_CPP += (parseInt(col_share[i])*parseInt(col_Historical_CPP[i]).toFixed(0));",
    "      Historical_CPM += (parseInt(col_share[i])*parseInt(col_Historical_CPM[i]).toFixed(0));",
    "    }",
    "    table.cell(rowIdx, j0+2).data((CPP/100).toFixed(2));",
    "    table.cell(rowIdx, j0+3).data((CPM/100).toFixed(2));",
    "    table.cell(rowIdx, j0+4).data((Historical_CPP/100).toFixed(2));",
    "    table.cell(rowIdx, j0+5).data((Historical_CPM/100).toFixed(2));",
    "  }",
    "  var dataset = [];",
    "  var n = d.length - 1;",
    "  for(var i = 0; i < d[n].length; i++){",
    "    var datarow = $.map(d[n][i], function (value, index) {",
    "      return [value];",
    "    });",
    "    dataset.push(datarow);",
    "  }",
    "  var id = 'table#' + childId;",
    "  if (Object.keys(d[n][0]).indexOf('_details') === -1) {",
    "    var subtable = $(id).DataTable({",
    "                 'data': dataset,",
    "                 'autoWidth': true,",
    "                 'deferRender': true,",
    "                 'info': false,",
    "                 'lengthChange': false,",
    "                 'ordering': d[n].length > 1,",
    "                 'order': [],",
    "                 'paging': true,",
    "                 'scrollX': false,",
    "                 'scrollY': false,",
    "                 'searching': false,",
    "                 'sortClasses': false,",
    "                 'pageLength': 50,",
    "                 'rowCallback': rowCallback,",
    "                 'headerCallback': headerCallback,",
    "                 'footerCallback': footerCallback,",
    "                 'columnDefs': [",
    "                  {targets: [0, 9, 10, 11], visible: false},",
    "                  {targets: '_all', className: 'dt-center'}",
    "                 ]",
    "               });",
    "  } else {",
    "    var subtable = $(id).DataTable({",
    "            'data': dataset,",
    "            'autoWidth': true,",
    "            'deferRender': true,",
    "            'info': false,",
    "            'lengthChange': false,",
    "            'ordering': d[n].length > 1,",
    "            'order': [],",
    "            'paging': true,",
    "            'scrollX': false,",
    "            'scrollY': false,",
    "            'searching': false,",
    "            'sortClasses': false,",
    "            'pageLength': 50,",
    "            'rowCallback': rowCallback,",
    "            'headerCallback': headerCallback,",
    "            'footerCallback': footerCallback,",
    "            'columnDefs': [",
    "              {targets: [0, 9, 10, 11], visible: false},",
    "              {targets: -1, visible: false},",
    "              {targets: 0, orderable: false, className: 'details-control'},",
    "              {targets: '_all', className: 'dt-center'}",
    "             ]",
    "          }).column(0).nodes().to$().css({cursor: 'pointer'});",
    "  }",
    "  subtable.MakeCellsEditable({",
    "    onUpdate: onUpdate,",
    "    inputCss: 'my-input-class',",
    "    columns: [2, 7, 8],",
    "    confirmationButton: {",
    "      confirmCss: 'my-confirm-class',",
    "      cancelCss: 'my-cancel-class'",
    "    }",
    "  });",
    "};",
    "",
    "// display the child table on click",
    "var children = [];", # array to store the id's of the already created child tables
    "table.on('click', 'td.details-control', function(){",
    "  var tbl = $(this).closest('table'),",
    "      tblId = tbl.attr('id'),",
    "      td = $(this),",
    "      row = $(tbl).DataTable().row(td.closest('tr')),",
    "      rowIdx = row.index();",
    "  if(row.child.isShown()){",
    "    row.child.hide();",
    "    td.html('<img src=\"https://raw.githubusercontent.com/DataTables/DataTables/master/examples/resources/details_open.png\"/>');",
    "  } else {",
    "    var childId = tblId + '-child-' + rowIdx;",
    "    if(children.indexOf(childId) === -1){", # this child table has not been created yet
    "      children.push(childId);",
    "      row.child(format(row.data(), childId)).show();",
    "    td.html('<img src=\"https://raw.githubusercontent.com/DataTables/DataTables/master/examples/resources/details_close.png\"/>');",
    "      format_datatable(row.data(), childId, rowIdx);",
    "    }else{",
    "      row.child(true);",
    "    td.html('<img src=\"https://raw.githubusercontent.com/DataTables/DataTables/master/examples/resources/details_close.png\"/>');",
    "    }",
    "  }",
    "});"
  )
  # Table
  table <- DT::datatable(
    Dat,
    callback = callback_js,
    rownames = rowNames,
    escape = -colIdx-1,
    style = "bootstrap4",
    extensions = 'Buttons',
      options = list(
        lengthMenu = list(c(-1, 10, 20),
                          c("All", 10, 20)),
        columnDefs = list(
          list(width = '30px', targets = 0),
          list(width = '545px', targets = 1),
          list(visible = FALSE, targets = ncol(Dat)-1+colIdx),
          list(orderable = FALSE, className = 'details-control', targets = colIdx),
          list(className = "dt-center", targets = "_all")
      )
    )
  )
  # Call the html tools deps (js & css files in this directory)
  cell_edit_dep <- htmltools::htmlDependency(
    "CellEdit", "1.0.19", 
    src = 'www/',
    script = "dataTables.cellEdit.js",
    stylesheet = "dataTables.cellEdit.css"
  )
  table$dependencies <- c(table$dependencies, list(cell_edit_dep))

  table %>% formatStyle(
    c('Market', 'SQAD CPP ($)', 'SQAD CPM ($)', 'Override CPP ($)', 'Override CPM ($)'),
    target = 'row',
    backgroundColor = "#F5F2F2"
  )
})

解决方案:

我几乎不明白是怎么回事。我想,这里有一个解决方案。

callback = JS(
  "var ok = true;",
  "function onUpdate(updatedCell, updatedRow, oldValue){",
  "  var column = updatedCell.index().column;",
  "  if(column === 8){",
  "    ok = false;",
  "  }else if(column === 7){",
  "    ok = true;",
  "  }",
  "}",
  sprintf("var parentRows = [%s];", toString(parentRows-1)),
  ......
  ......
  "// update the Override CPM when the Override CPP is changed",
  "    var col_override_cpp = api.column(7).data();",
  "    var col_population = api.column(9).data();",
  "    if(ok){",
  "      for(var i = 0; i < col_override_cpp.length; i++){",
  "        api.cell(i,8).data(((parseInt(col_override_cpp[i])*100)/(parseInt(col_population[i])/1000)).toFixed(0));",
  "      }",
  "    }",
  ......
  ......

给TA打赏
共{{data.count}}人
人已打赏
未分类

如何在存储过程中为同一列使用两个参数?

2022-9-9 3:51:23

未分类

Kotlin箭头库中Either.fold()的返回类型。

2022-9-9 4:02:17

0 条回复 A文章作者 M管理员
    暂无讨论,说说你的看法吧
个人中心
购物车
优惠劵
今日签到
有新私信 私信列表
搜索