问:
VBA Function/ Code - Convert Dynamic Column of Data into Text String
I'm creating a tool where users can query an ODBC through excel. I need a function which will look at a column of item id's which the user has pasted in and then create a text string which can be used in the Sql part of the VBA. The number of item Id's can vary.

Using a similar function (not created by myself), I can reference one cell and insert the ' and , where needed but I can't seem to get this to work for multiple cells in one column.

'Here is the code I have used for the existing function:

```<code>                    Function CreateSQLAndQry(field_input As String, input_string As String) As String
                    cleaned_string = Replace(input_string, " ", "")
                    Dim xArray() As String
                    xArray() = Split(cleaned_string, ",")
                    Dim lenArray As Integer
                    lenArray = UBound(xArray())
                    Dim new_string As String
                    new_string = ""
                    For I = 0 To lenArray
                        xArray(I) = "'" & xArray(I) & "'"
                        new_string = xArray(I) & "," & new_string
                    Next
                    If input_string = "" Then
                    new_string = ""
                    Else: new_string = Left(new_string, Len(new_string) - 1)
                    End If

                    If input_string = "" Then
                    new_qry = ""
                    Else: new_qry = " AND " & field_input & " IN (" & new_string & ") "
                    End If
</code>```

If the column of data was to look like this:

test1
test2

I need the output to look like this:

in ('test2', 'test1')

答:
The formula you have is incomplete, it doesn't show the ending, but here's a function that loops through any amount of cells and constructs a string that looks similar to what you're looking for. You can change the prefix in the constants at the top of the formula or tinker with it to get whatever manipulations you want done.

```<code>Function PullTextTogether(rng As Range) As String
Const xSepx As String = ","
Const preText As String = "in "
Const ignoreText As String = " " ''characters you want stripped out

Dim aCell As Range

For Each aCell In Intersect(rng, rng.Worksheet.UsedRange).Cells

    If aCell.Value2 <> "" Then
    PullTextTogether = PullTextTogether & "'" & xSepx & "'" & aCell.Value2

    'Duplicate this with more text you would want to strip out
    PullTextTogether = Replace(PullTextTogether, ignoreText, "")

    End If

Next aCell

If PullTextTogether <> "" Then
    'cleans up
    PullTextTogether = preText & "('" & Right(PullTextTogether, Len(PullTextTogether) - 3) & "')"
End If

End Function
</code>```

Body:
I'm creating a tool where users can query an ODBC through excel. I need a function which will look at a column of item id's which the user has pasted in and then create a text string which can be used in the Sql part of the VBA. The number of item Id's can vary.

Using a similar function (not created by myself), I can reference one cell and insert the ' and , where needed but I can't seem to get this to work for multiple cells in one column.

'Here is the code I have used for the existing function:

                    Function CreateSQLAndQry(field_input As String, input_string As String) As String
                    cleaned_string = Replace(input_string, " ", "")
                    Dim xArray() As String
                    xArray() = Split(cleaned_string, ",")
                    Dim lenArray As Integer
                    lenArray = UBound(xArray())
                    Dim new_string As String
                    new_string = ""
                    For I = 0 To lenArray
                        xArray(I) = "'" & xArray(I) & "'"
                        new_string = xArray(I) & "," & new_string
                    Next
                    If input_string = "" Then
                    new_string = ""
                    Else: new_string = Left(new_string, Len(new_string) - 1)
                    End If

                    If input_string = "" Then
                    new_qry = ""
                    Else: new_qry = " AND " & field_input & " IN (" & new_string & ") "
                    End If

If the column of data was to look like this:

test1
test2

I need the output to look like this:

in ('test2', 'test1')

Body:
The formula you have is incomplete, it doesn't show the ending, but here's a function that loops through any amount of cells and constructs a string that looks similar to what you're looking for. You can change the prefix in the constants at the top of the formula or tinker with it to get whatever manipulations you want done.

Function PullTextTogether(rng As Range) As String
Const xSepx As String = ","
Const preText As String = "in "
Const ignoreText As String = " " ''characters you want stripped out

Dim aCell As Range

For Each aCell In Intersect(rng, rng.Worksheet.UsedRange).Cells

    If aCell.Value2 <> "" Then
    PullTextTogether = PullTextTogether & "'" & xSepx & "'" & aCell.Value2

    'Duplicate this with more text you would want to strip out
    PullTextTogether = Replace(PullTextTogether, ignoreText, "")

    End If

Next aCell

If PullTextTogether <> "" Then
    'cleans up
    PullTextTogether = preText & "('" & Right(PullTextTogether, Len(PullTextTogether) - 3) & "')"
End If

End Function


来源: stackexchange

问:
How to troubleshoot row height adjustment with expandable rows with react virtualized list?
I'm using expandable panels (Material-UI) in rows with a react virtualized list and have been having issues with the heights auto-adjusting. I've read several SO posts and some issues on dynamic row heights on the react-virtualized site, but I have a specific problem where it seems like there's an 'off by one' issue with when the row height is adjusted after panel is expanded/collapsed.

Here's the expected behavior:

Row panel expanded by default.

User clicks expandable panel row.

Row panel collapses.

Row height adjusts to panel collapse.

Here's the actual behavior FOR THE FIRST CLICK:

Row panel expanded by default.

User clicks expandable panel row.

Row panel collapses.

Row height does NOT adjust to panel collapse.

HOWEVER, on subsequent clicks the row height DOES adjust, but to the 'opposite' state, which leads to an inconsistency - i.e when the the row panel is clicked to expand again, the row height is adjusted to the row height as if it were collapsed, and vice versa. So when the panel is collapsed there's a bunch of white space after it, and when it's technically expanded the row height is too small to see the content.

I'm not sure what other info to include besides posting code and noting that the onRowClick() IS firing when the panels are collapsed/expanded.

Here's the parent component:

```<code>import React, { Component } from 'react';
import AutoSizer from 'react-virtualized/dist/commonjs/AutoSizer';
import List from 'react-virtualized/dist/commonjs/List';
import { CellMeasurer, CellMeasurerCache } from 'react-virtualized/dist/commonjs/CellMeasurer';
import EquipSummaryRow from './EquipSummaryRow';
import './EquipSummary.css';

class EquipSummary extends Component {
  constructor(props) {
    super(props);

    this.cache = new CellMeasurerCache({
      fixedWidth: true,
    });

    this.rowRenderer = this.rowRenderer.bind(this);
    this.getDatum = this.getDatum.bind(this);
    this.onRowClick = this.onRowClick.bind(this);
  }

  getDatum(index) {
    const list = this.props.equipData;

    return list[index];
  }

  saveRef = (ref) => this.containerNode = ref;

  saveListRef = (ref) => {
    this.list = ref;
  }

  componentDidUpdate() {
    console.log('component updated');
    this.cache.clearAll();
    this.list.recomputeRowHeights();
  }

  onRowClick(e, index) {
    e.preventDefault();
    this.cache.clear(index);
    this.list.recomputeRowHeights();
    this.list.forceUpdateGrid();
  }

  rowRenderer({ index, key, parent, style }) {
    const datum = this.getDatum(index);
    return (
      <div key={key} style={style}>
        <CellMeasurer
          cache={this.cache}
          columnIndex={0}
          key={key}
          rowIndex={index}
          parent={parent}
        >
          {({ measure }) => (
            <EquipSummaryRow
              onClick={(e, idx) => this.onRowClick(e, idx)}
              measure={measure}
              datum={datum}
              index={index}
            />
          )}
        </CellMeasurer>
      </div>
    );
  }

  render() {
    console.log('rendering..');
    return (
      <div className="EquipSummary-AutoSizer" ref={this.saveRef}>
        <AutoSizer>
          {({ width, height }) => (
            <List
              ref={this.saveListRef}
              width={width}
              height={height}
              rowHeight={this.cache.rowHeight}
              rowCount={this.props.equipData.length}
              rowRenderer={this.rowRenderer}
              deferredMeasurementCache={this.cache}
              equipData={this.props.equipData}
            />
          )}
        </AutoSizer>
      </div>
    );
  }
}

export default EquipSummary;
</code>```

And here's the component that represents a row:

```<code>import React, { Component } from 'react';
import {
  Table,
  TableBody,
  TableHeader,
  TableHeaderColumn,
  TableRow,
  TableRowColumn,
} from 'material-ui/Table';
import { MuiThemeProvider } from 'material-ui/styles';
import ExpansionPanel from '@material-ui/core/ExpansionPanel';
import ExpansionPanelSummary from '@material-ui/core/ExpansionPanelSummary';
import ExpansionPanelDetails from '@material-ui/core/ExpansionPanelDetails';
import Typography from '@material-ui/core/Typography';

class EquipSummaryRow extends Component {
  render() {
    const { datum } = this.props;

    return (
      <div>
        <ExpansionPanel
          defaultExpanded
          onChange={e => this.props.onClick(e, this.props.index)}
        >
          <ExpansionPanelSummary expandIcon={<div>|</div>}>
            <Typography>{`${datum.type}      (id: ${datum.instance}, points: ${datum.points.length})`}</Typography>
          </ExpansionPanelSummary>
          <ExpansionPanelDetails>
            <Table>
              <TableHeader
                displaySelectAll={false}
                adjustForCheckbox={false}
              >
                <TableRow>
                  <TableHeaderColumn>Device</TableHeaderColumn>
                  <TableHeaderColumn>Object ID</TableHeaderColumn>
                  <TableHeaderColumn>Type</TableHeaderColumn>
                  <TableHeaderColumn>Name</TableHeaderColumn>
                  <TableHeaderColumn>Description</TableHeaderColumn>
                  <TableHeaderColumn>Units</TableHeaderColumn>
                  <TableHeaderColumn>Value</TableHeaderColumn>
                </TableRow>
              </TableHeader>
              <TableBody
                displayRowCheckbox={false}
              >
                {datum.points.map((row, index) => (
                  <TableRow key={row.id}>
                    <TableRowColumn>{row.device}</TableRowColumn>
                    <TableRowColumn>{row.objectID}</TableRowColumn>
                    <TableRowColumn>{row.type}</TableRowColumn>
                    <TableRowColumn>{row.name}</TableRowColumn>
                    <TableRowColumn>{row.description}</TableRowColumn>
                    <TableRowColumn>{row.units}</TableRowColumn>
                    <TableRowColumn>{row.value}</TableRowColumn>
                  </TableRow>
                  ))}
              </TableBody>
            </Table>
          </ExpansionPanelDetails>
        </ExpansionPanel>
      </div>
    );
  }
}

export default EquipSummaryRow;
</code>```

Could this be an issue with how I'm using the cache? I've been beating my head with this so any suggestions appreciated!

答:
(This isn't a complete answer, but it does allow for the animation step to work as designed. Given enough time, I think this could work completely. Please see my comments at the end for more information.)

In the List component, there is an option to pass in a different cellRangeRenderer. This cellRangeRenderer is what is responsible for generating the style object that is attached to each individual cell. The default cellRangeRenderer uses absolute positioning to accomplish this. I've created a modified cellRangeRenderer that doesn't actually set anything valid in the style object, but rather generates a container for the cells. The container uses absolute positioning to show the cells where they need to be with respect to the scrollbar, but inside the container, each cell is just rendered as-is.

```<code>import React from 'react'

/**
 * Default implementation of cellRangeRenderer used by Grid.
 * This renderer supports cell-caching while the user is scrolling.
 */

export default function cellRangeRenderer({
  cellCache,
  cellRenderer,
  columnSizeAndPositionManager,
  columnStartIndex,
  columnStopIndex,
  deferredMeasurementCache,
  horizontalOffsetAdjustment,
  isScrolling,
  isScrollingOptOut,
  parent, // Grid (or List or Table)
  rowSizeAndPositionManager,
  rowStartIndex,
  rowStopIndex,
  styleCache,
  verticalOffsetAdjustment,
  visibleColumnIndices,
  visibleRowIndices,
}) {
  const renderedCells = [];

  // Browsers have native size limits for elements (eg Chrome 33M pixels, IE 1.5M pixes).
  // User cannot scroll beyond these size limitations.
  // In order to work around this, ScalingCellSizeAndPositionManager compresses offsets.
  // We should never cache styles for compressed offsets though as this can lead to bugs.
  // See issue #576 for more.
  const areOffsetsAdjusted = columnSizeAndPositionManager.areOffsetsAdjusted() || rowSizeAndPositionManager.areOffsetsAdjusted();

  const canCacheStyle = !isScrolling && !areOffsetsAdjusted;
  let styledBuffer = false
  let bufferStyle, containerStyle

  for (let rowIndex = rowStartIndex; rowIndex <= rowStopIndex; rowIndex++) {
    const rowDatum = rowSizeAndPositionManager.getSizeAndPositionOfCell(rowIndex);

    for (let columnIndex = columnStartIndex; columnIndex <= columnStopIndex; columnIndex++) {
      const columnDatum = columnSizeAndPositionManager.getSizeAndPositionOfCell(columnIndex);
      const isVisible = columnIndex >= visibleColumnIndices.start && columnIndex <= visibleColumnIndices.stop && rowIndex >= visibleRowIndices.start && rowIndex <= visibleRowIndices.stop;
      const key = `${rowIndex}-${columnIndex}`;
      let style;
      // this is the part that bugs out when react-virtualized re-renders part of the what's-showing-now list, rather than the entire what's-showing-now list
      // I'm just grabbing the first cell and assuming it's coordinates are the top of the what's-showing-now list
      if (!styledBuffer) {
        styledBuffer = true
        bufferStyle = {
          position: 'absolute',
          top: 0,
          left: 0,
          height: rowDatum.offset + verticalOffsetAdjustment,
          width: columnDatum.offset + horizontalOffsetAdjustment,
        }
        containerStyle = {
          position: 'absolute',
          top: rowDatum.offset + verticalOffsetAdjustment,
          left: columnDatum.offset + horizontalOffsetAdjustment,
          height: 'auto',
          width: 'auto',
        }
      }

      // Cache style objects so shallow-compare doesn't re-render unnecessarily.
      if (canCacheStyle && styleCache[key]) {
        style = styleCache[key];
      } else if (deferredMeasurementCache && !deferredMeasurementCache.has(rowIndex, columnIndex)) {
      // In deferred mode, cells will be initially rendered before we know their size.
      // Don't interfere with CellMeasurer's measurements by setting an invalid size.
        // Position not-yet-measured cells at top/left 0,0,
        // And give them width/height of 'auto' so they can grow larger than the parent Grid if necessary.
        // Positioning them further to the right/bottom influences their measured size.
        style = {
          height: 'auto',
          left: 0,
          position: 'absolute',
          top: 0,
          width: 'auto'
        };
      } else {
        // I'd go with a completely empty object, but that breaks other parts of react-virtualized that rely, at least, on 'width' being defined
        style = {
          height: 'auto',
          width: 'auto',
        }
        styleCache[key] = style;
      }

      const cellRendererParams = {
        columnIndex,
        isScrolling,
        isVisible,
        key,
        parent,
        rowIndex,
        style
      };

      let renderedCell;

      // Avoid re-creating cells while scrolling.
      // This can lead to the same cell being created many times and can cause performance issues for "heavy" cells.
      // If a scroll is in progress- cache and reuse cells.
      // This cache will be thrown away once scrolling completes.
      // However if we are scaling scroll positions and sizes, we should also avoid caching.
      // This is because the offset changes slightly as scroll position changes and caching leads to stale values.
      // For more info refer to issue #395
      //
      // If isScrollingOptOut is specified, we always cache cells.
      // For more info refer to issue #1028
      if ((isScrollingOptOut || isScrolling) && !horizontalOffsetAdjustment && !verticalOffsetAdjustment) {
        if (!cellCache[key]) {
          cellCache[key] = cellRenderer(cellRendererParams);
        }

        renderedCell = cellCache[key];

        // If the user is no longer scrolling, don't cache cells.
        // This makes dynamic cell content difficult for users and would also lead to a heavier memory footprint.
      } else {
        renderedCell = cellRenderer(cellRendererParams);
      }

      if (renderedCell === null || renderedCell === false) {
        continue;
      }

      if (process.env.NODE_ENV !== 'production') {
        warnAboutMissingStyle(parent, renderedCell);
      }

      renderedCells.push(renderedCell);
    }
  }

  // This is where the new "magic" happens
  return [(
    <div id="0-buffer-at-the-top" key="0-buffer-at-the-top" style={bufferStyle} />
  ), (
    <div id="0-container-at-the-top" key="0-container-at-the-top" style={containerStyle}>
      {renderedCells}
    </div>
  )];
}

function warnAboutMissingStyle(parent, renderedCellParam) {
  let renderedCell = renderedCellParam
  if (process.env.NODE_ENV !== 'production') {
    if (renderedCell) {
      // If the direct child is a CellMeasurer, then we should check its child
      // See issue #611
      if (renderedCell.type && renderedCell.type.__internalCellMeasurerFlag) {
        renderedCell = renderedCell.props.children;
      }

      if (renderedCell && renderedCell.props && renderedCell.props.style === undefined && parent.__warnedAboutMissingStyle !== true) {
        parent.__warnedAboutMissingStyle = true;

        console.warn('Rendered cell should include style property for positioning.');
      }
    }
  }
}
</code>```

This code started off life as a copy of what is distributed in the npm package (to get around the babel compilation step, somewhat). It has, at least, the following issues:

It must be used in a List, not a Grid. A Grid would require that the cells be placed correctly within a Grid (Material-UI Grid, not react-virtualized Grid), rather than just thrown out there.

react-virtualized has some optimizations that allow this method to be called on sub-sections of the list, rather than the entire chunk being rendered (fixing this was outside the timebox I was allotted to attempting this fix). This new cellRangeRenderer would work about 90% correctly as-is if this problem were addressed.

Because you can expand a row, then scroll, the row size still needs the CellMeasurer to compute heights. Because I'm not applying the height to each individual cell, we need to recompute the "top" height for the container using the heights in a slightly smarter way. This is only broken if you completely scroll past the section where the expanded panel is rendered. It's possible that merely applying the height to the style object would be sufficient, but that is untested. Edit: You would still need to delay the call to measure as your answer implied.

Jumping to a specific cell hasn't been tested, and might work, or might not.

ContentLicense: CC BY-SA 4.0

Body:
I'm using expandable panels (Material-UI) in rows with a react virtualized list and have been having issues with the heights auto-adjusting. I've read several SO posts and some issues on dynamic row heights on the react-virtualized site, but I have a specific problem where it seems like there's an 'off by one' issue with when the row height is adjusted after panel is expanded/collapsed.

Here's the expected behavior:

Row panel expanded by default.

User clicks expandable panel row.

Row panel collapses.

Row height adjusts to panel collapse.

Here's the actual behavior FOR THE FIRST CLICK:

Row panel expanded by default.

User clicks expandable panel row.

Row panel collapses.

Row height does NOT adjust to panel collapse.

HOWEVER, on subsequent clicks the row height DOES adjust, but to the 'opposite' state, which leads to an inconsistency - i.e when the the row panel is clicked to expand again, the row height is adjusted to the row height as if it were collapsed, and vice versa. So when the panel is collapsed there's a bunch of white space after it, and when it's technically expanded the row height is too small to see the content.

I'm not sure what other info to include besides posting code and noting that the onRowClick() IS firing when the panels are collapsed/expanded.

Here's the parent component:

import React, { Component } from 'react';
import AutoSizer from 'react-virtualized/dist/commonjs/AutoSizer';
import List from 'react-virtualized/dist/commonjs/List';
import { CellMeasurer, CellMeasurerCache } from 'react-virtualized/dist/commonjs/CellMeasurer';
import EquipSummaryRow from './EquipSummaryRow';
import './EquipSummary.css';

class EquipSummary extends Component {
  constructor(props) {
    super(props);

    this.cache = new CellMeasurerCache({
      fixedWidth: true,
    });

    this.rowRenderer = this.rowRenderer.bind(this);
    this.getDatum = this.getDatum.bind(this);
    this.onRowClick = this.onRowClick.bind(this);
  }

  getDatum(index) {
    const list = this.props.equipData;

    return list[index];
  }

  saveRef = (ref) => this.containerNode = ref;

  saveListRef = (ref) => {
    this.list = ref;
  }

  componentDidUpdate() {
    console.log('component updated');
    this.cache.clearAll();
    this.list.recomputeRowHeights();
  }

  onRowClick(e, index) {
    e.preventDefault();
    this.cache.clear(index);
    this.list.recomputeRowHeights();
    this.list.forceUpdateGrid();
  }

  rowRenderer({ index, key, parent, style }) {
    const datum = this.getDatum(index);
    return (

        <CellMeasurer
          cache={this.cache}
          columnIndex={0}
          key={key}
          rowIndex={index}
          parent={parent}
        >
          {({ measure }) => (
            <EquipSummaryRow
              onClick={(e, idx) => this.onRowClick(e, idx)}
              measure={measure}
              datum={datum}
              index={index}
            />
          )}
        </CellMeasurer>

    );
  }

  render() {
    console.log('rendering..');
    return (

        <AutoSizer>
          {({ width, height }) => (
            <List
              ref={this.saveListRef}
              width={width}
              height={height}
              rowHeight={this.cache.rowHeight}
              rowCount={this.props.equipData.length}
              rowRenderer={this.rowRenderer}
              deferredMeasurementCache={this.cache}
              equipData={this.props.equipData}
            />
          )}
        </AutoSizer>

    );
  }
}

export default EquipSummary;

And here's the component that represents a row:

import React, { Component } from 'react';
import {
  Table,
  TableBody,
  TableHeader,
  TableHeaderColumn,
  TableRow,
  TableRowColumn,
} from 'material-ui/Table';
import { MuiThemeProvider } from 'material-ui/styles';
import ExpansionPanel from '@material-ui/core/ExpansionPanel';
import ExpansionPanelSummary from '@material-ui/core/ExpansionPanelSummary';
import ExpansionPanelDetails from '@material-ui/core/ExpansionPanelDetails';
import Typography from '@material-ui/core/Typography';

class EquipSummaryRow extends Component {
  render() {
    const { datum } = this.props;

    return (

        <ExpansionPanel
          defaultExpanded
          onChange={e => this.props.onClick(e, this.props.index)}
        >
          <ExpansionPanelSummary expandIcon={
|
}>
            <Typography>{`${datum.type}      (id: ${datum.instance}, points: ${datum.points.length})`}</Typography>
          </ExpansionPanelSummary>
          <ExpansionPanelDetails>

              <TableHeader
                displaySelectAll={false}
                adjustForCheckbox={false}
              >
                <TableRow>
                  <TableHeaderColumn>Device</TableHeaderColumn>
                  <TableHeaderColumn>Object ID</TableHeaderColumn>
                  <TableHeaderColumn>Type</TableHeaderColumn>
                  <TableHeaderColumn>Name</TableHeaderColumn>
                  <TableHeaderColumn>Description</TableHeaderColumn>
                  <TableHeaderColumn>Units</TableHeaderColumn>
                  <TableHeaderColumn>Value</TableHeaderColumn>
                </TableRow>
              </TableHeader>
              <TableBody
                displayRowCheckbox={false}
              >
                {datum.points.map((row, index) => (
                  <TableRow key={row.id}>
                    <TableRowColumn>{row.device}</TableRowColumn>
                    <TableRowColumn>{row.objectID}</TableRowColumn>
                    <TableRowColumn>{row.type}</TableRowColumn>
                    <TableRowColumn>{row.name}</TableRowColumn>
                    <TableRowColumn>{row.description}</TableRowColumn>
                    <TableRowColumn>{row.units}</TableRowColumn>
                    <TableRowColumn>{row.value}</TableRowColumn>
                  </TableRow>
                  ))}
              </TableBody>

          </ExpansionPanelDetails>
        </ExpansionPanel>

    );
  }
}

export default EquipSummaryRow;

Could this be an issue with how I'm using the cache? I've been beating my head with this so any suggestions appreciated!

Body:
(This isn't a complete answer, but it does allow for the animation step to work as designed. Given enough time, I think this could work completely. Please see my comments at the end for more information.)

In the List component, there is an option to pass in a different cellRangeRenderer. This cellRangeRenderer is what is responsible for generating the style object that is attached to each individual cell. The default cellRangeRenderer uses absolute positioning to accomplish this. I've created a modified cellRangeRenderer that doesn't actually set anything valid in the style object, but rather generates a container for the cells. The container uses absolute positioning to show the cells where they need to be with respect to the scrollbar, but inside the container, each cell is just rendered as-is.

import React from 'react'

/**
 * Default implementation of cellRangeRenderer used by Grid.
 * This renderer supports cell-caching while the user is scrolling.
 */

export default function cellRangeRenderer({
  cellCache,
  cellRenderer,
  columnSizeAndPositionManager,
  columnStartIndex,
  columnStopIndex,
  deferredMeasurementCache,
  horizontalOffsetAdjustment,
  isScrolling,
  isScrollingOptOut,
  parent, // Grid (or List or Table)
  rowSizeAndPositionManager,
  rowStartIndex,
  rowStopIndex,
  styleCache,
  verticalOffsetAdjustment,
  visibleColumnIndices,
  visibleRowIndices,
}) {
  const renderedCells = [];

  // Browsers have native size limits for elements (eg Chrome 33M pixels, IE 1.5M pixes).
  // User cannot scroll beyond these size limitations.
  // In order to work around this, ScalingCellSizeAndPositionManager compresses offsets.
  // We should never cache styles for compressed offsets though as this can lead to bugs.
  // See issue #576 for more.
  const areOffsetsAdjusted = columnSizeAndPositionManager.areOffsetsAdjusted() || rowSizeAndPositionManager.areOffsetsAdjusted();

  const canCacheStyle = !isScrolling && !areOffsetsAdjusted;
  let styledBuffer = false
  let bufferStyle, containerStyle

  for (let rowIndex = rowStartIndex; rowIndex <= rowStopIndex; rowIndex++) {
    const rowDatum = rowSizeAndPositionManager.getSizeAndPositionOfCell(rowIndex);

    for (let columnIndex = columnStartIndex; columnIndex <= columnStopIndex; columnIndex++) {
      const columnDatum = columnSizeAndPositionManager.getSizeAndPositionOfCell(columnIndex);
      const isVisible = columnIndex >= visibleColumnIndices.start && columnIndex <= visibleColumnIndices.stop && rowIndex >= visibleRowIndices.start && rowIndex <= visibleRowIndices.stop;
      const key = `${rowIndex}-${columnIndex}`;
      let style;
      // this is the part that bugs out when react-virtualized re-renders part of the what's-showing-now list, rather than the entire what's-showing-now list
      // I'm just grabbing the first cell and assuming it's coordinates are the top of the what's-showing-now list
      if (!styledBuffer) {
        styledBuffer = true
        bufferStyle = {
          position: 'absolute',
          top: 0,
          left: 0,
          height: rowDatum.offset + verticalOffsetAdjustment,
          width: columnDatum.offset + horizontalOffsetAdjustment,
        }
        containerStyle = {
          position: 'absolute',
          top: rowDatum.offset + verticalOffsetAdjustment,
          left: columnDatum.offset + horizontalOffsetAdjustment,
          height: 'auto',
          width: 'auto',
        }
      }

      // Cache style objects so shallow-compare doesn't re-render unnecessarily.
      if (canCacheStyle && styleCache[key]) {
        style = styleCache[key];
      } else if (deferredMeasurementCache && !deferredMeasurementCache.has(rowIndex, columnIndex)) {
      // In deferred mode, cells will be initially rendered before we know their size.
      // Don't interfere with CellMeasurer's measurements by setting an invalid size.
        // Position not-yet-measured cells at top/left 0,0,
        // And give them width/height of 'auto' so they can grow larger than the parent Grid if necessary.
        // Positioning them further to the right/bottom influences their measured size.
        style = {
          height: 'auto',
          left: 0,
          position: 'absolute',
          top: 0,
          width: 'auto'
        };
      } else {
        // I'd go with a completely empty object, but that breaks other parts of react-virtualized that rely, at least, on 'width' being defined
        style = {
          height: 'auto',
          width: 'auto',
        }
        styleCache[key] = style;
      }

      const cellRendererParams = {
        columnIndex,
        isScrolling,
        isVisible,
        key,
        parent,
        rowIndex,
        style
      };

      let renderedCell;

      // Avoid re-creating cells while scrolling.
      // This can lead to the same cell being created many times and can cause performance issues for "heavy" cells.
      // If a scroll is in progress- cache and reuse cells.
      // This cache will be thrown away once scrolling completes.
      // However if we are scaling scroll positions and sizes, we should also avoid caching.
      // This is because the offset changes slightly as scroll position changes and caching leads to stale values.
      // For more info refer to issue #395
      //
      // If isScrollingOptOut is specified, we always cache cells.
      // For more info refer to issue #1028
      if ((isScrollingOptOut || isScrolling) && !horizontalOffsetAdjustment && !verticalOffsetAdjustment) {
        if (!cellCache[key]) {
          cellCache[key] = cellRenderer(cellRendererParams);
        }

        renderedCell = cellCache[key];

        // If the user is no longer scrolling, don't cache cells.
        // This makes dynamic cell content difficult for users and would also lead to a heavier memory footprint.
      } else {
        renderedCell = cellRenderer(cellRendererParams);
      }

      if (renderedCell === null || renderedCell === false) {
        continue;
      }

      if (process.env.NODE_ENV !== 'production') {
        warnAboutMissingStyle(parent, renderedCell);
      }

      renderedCells.push(renderedCell);
    }
  }

  // This is where the new "magic" happens
  return [(
    <div id="0-buffer-at-the-top" key="0-buffer-at-the-top" style={bufferStyle} />
  ), (
    <div id="0-container-at-the-top" key="0-container-at-the-top" style={containerStyle}>
      {renderedCells}
    </div>
  )];
}

function warnAboutMissingStyle(parent, renderedCellParam) {
  let renderedCell = renderedCellParam
  if (process.env.NODE_ENV !== 'production') {
    if (renderedCell) {
      // If the direct child is a CellMeasurer, then we should check its child
      // See issue #611
      if (renderedCell.type && renderedCell.type.__internalCellMeasurerFlag) {
        renderedCell = renderedCell.props.children;
      }

      if (renderedCell && renderedCell.props && renderedCell.props.style === undefined && parent.__warnedAboutMissingStyle !== true) {
        parent.__warnedAboutMissingStyle = true;

        console.warn('Rendered cell should include style property for positioning.');
      }
    }
  }
}

This code started off life as a copy of what is distributed in the npm package (to get around the babel compilation step, somewhat). It has, at least, the following issues:

It must be used in a List, not a Grid. A Grid would require that the cells be placed correctly within a Grid (Material-UI Grid, not react-virtualized Grid), rather than just thrown out there.

react-virtualized has some optimizations that allow this method to be called on sub-sections of the list, rather than the entire chunk being rendered (fixing this was outside the timebox I was allotted to attempting this fix). This new cellRangeRenderer would work about 90% correctly as-is if this problem were addressed.

Because you can expand a row, then scroll, the row size still needs the CellMeasurer to compute heights. Because I'm not applying the height to each individual cell, we need to recompute the "top" height for the container using the heights in a slightly smarter way. This is only broken if you completely scroll past the section where the expanded panel is rendered. It's possible that merely applying the height to the style object would be sufficient, but that is untested. Edit: You would still need to delay the call to measure as your answer implied.

Jumping to a specific cell hasn't been tested, and might work, or might not.


问:
Laravel 5.7 signed route returns 403 invalid signature
I'm trying to take advantage of the new signed middleware in Laravel 5.7, but for some reason the generated signed URL is returning 403 Invalid Signature.

I'm using the latest Laravel Version, with PHP 7.2

This is my web.php route:

```<code>Route::get('/report/{user}/{client}', function ($user, $client) {
    return ("El usuario es: $user y el cliente es: $client");
})->name('report.client')->middleware('signed');
</code>```

and this is in my controller:

```<code>$objDemo->tempURL = Url::temporarySignedRoute('report.client', now('America/Panama')->addDays(5), [
            'user' => 1,
            'client' => 1
        ]);
</code>```

The URL is generated and shows something like this:

https://example.com/report/1/1?expires=1545440368&signature=55ad67fa049a74fe8e123c664e50f53564b76154e2dd805c5927125f63c390a1

But when i click the link the result is a 403 with the message: "Invalid signature"

Any ideas? thanks in advance

-----------UPDATE------------

Things i've done already:

Try the route without signing, and works perfectly

Try the route without parameters and only signing

Try the route without temporary setting and only signing

Set cloudflare's ip to trusted proxies

Disable HTTPS, Enable HTTPS

Nothing seems to work, always getting the 403 invalid signature page

-----------UPDATE 2------------

Ok, so after some digging and testing, i found out that laravel signed routes won't work if the user is logged in, this is weird, if i logout then the route works perfectly, but if i log-in then it shows the 403 error, might this be because Laravel adds the session cookie header after everything else? and so the signed route fails because of it? it's this the way it should be?

Weird, because let's say i want to create a temporary link for my users to download something, if they are logged into my Laravel app, they will get this 403 error message... :(

------------UPDATE 3------------------

I tried in a fresh installation of laravel and worked perfectly, so it's something from my main Laravel app, also tried to install every composer dependency into the Fresh installation of Laravel, and still worked perfectly no matter the user login status, so it's not a conflict with my dependencies.

答:
If you're using Heroku, AWS or any other service that makes use of a LoadBalancer. Do also ensure that the proxy reaching your application is trusted.

See this answer for more.

Body:
I'm trying to take advantage of the new signed middleware in Laravel 5.7, but for some reason the generated signed URL is returning 403 Invalid Signature.

I'm using the latest Laravel Version, with PHP 7.2

This is my web.php route:

Route::get('/report/{user}/{client}', function ($user, $client) {
    return ("El usuario es: $user y el cliente es: $client");
})->name('report.client')->middleware('signed');

and this is in my controller:

$objDemo->tempURL = Url::temporarySignedRoute('report.client', now('America/Panama')->addDays(5), [
            'user' => 1,
            'client' => 1
        ]);

The URL is generated and shows something like this:

https://example.com/report/1/1?expires=1545440368&signature=55ad67fa049a74fe8e123c664e50f53564b76154e2dd805c5927125f63c390a1

But when i click the link the result is a 403 with the message: "Invalid signature"

Any ideas? thanks in advance

-----------UPDATE------------

Things i've done already:

Try the route without signing, and works perfectly

Try the route without parameters and only signing

Try the route without temporary setting and only signing

Set cloudflare's ip to trusted proxies

Disable HTTPS, Enable HTTPS

Nothing seems to work, always getting the 403 invalid signature page

-----------UPDATE 2------------

Ok, so after some digging and testing, i found out that laravel signed routes won't work if the user is logged in, this is weird, if i logout then the route works perfectly, but if i log-in then it shows the 403 error, might this be because Laravel adds the session cookie header after everything else? and so the signed route fails because of it? it's this the way it should be?

Weird, because let's say i want to create a temporary link for my users to download something, if they are logged into my Laravel app, they will get this 403 error message... :(

------------UPDATE 3------------------

I tried in a fresh installation of laravel and worked perfectly, so it's something from my main Laravel app, also tried to install every composer dependency into the Fresh installation of Laravel, and still worked perfectly no matter the user login status, so it's not a conflict with my dependencies.


问:
MouseClick and MoveTo event is not working on the 2nd Part that spawned
I'm working on a project that the player clicks on an object and it walks over to it, wait a second, then the object is removed from the game and points update on the leaderboard. The problem is it only works on the first round. The 2nd time, the new part spawned and it does have the ClickDetector as its child, but it does not function.

```<code>local flowers = game.ReplicatedStorage.Flowers.level1:GetChildren()
local selection = math.random(1,#flowers)
local Clone = flowers[selection]:Clone()

local ClickDetector = Instance.new("ClickDetector")
ClickDetector.Parent = Clone
ClickDetector.MaxActivationDistance = 500

local spawners = workspace.Spawners.level1:GetChildren()
local spawnSelection = math.random(1,#spawners)
local spawner = spawners[spawnSelection]

while true do

    wait(1)
    if Clone.Parent == nil then
        Clone.Parent = workspace.Flowers.level1
        Clone.CFrame = spawner.CFrame + Vector3.new(math.random(-5,5),1,math.random(-5,5))
        print("Clone added")

        ClickDetector.MouseClick:Connect(function(playerWhoClicked)
            playerWhoClicked.Character.Humanoid:MoveTo(Clone.Position,Clone)
            print("clicked")

            wait(1)

            Clone:Remove()
            print("Clone removed")

            local flowerValue = playerWhoClicked.leaderstats.Flowers
            local coinsValue = playerWhoClicked.leaderstats.Coins
            flowerValue.Value = flowerValue.Value + 1
            coinsValue.Value = coinsValue.Value + 5
        end)
    end

end
</code>```

There are no error messages on output. Just that on the new spawned part, "clicked" is not printing.

答:
Would this work, it was hard to understand your question, but I think what your saying is that the clone wont appear after you click again. Well did this work? Also, you don't need to have the click event inside the loop, and to make a clone every time the player clicks, inside the click function you make a clone of the Clone variable.</p>

```<code>local flowers = game.ReplicatedStorage.Flowers.level1:GetChildren()
local selection = math.random(1,#flowers)
local Clone = flowers[selection]:Clone()

local ClickDetector = Instance.new("ClickDetector")
ClickDetector.Parent = Clone
ClickDetector.MaxActivationDistance = 500

local spawners = workspace.Spawners.level1:GetChildren()
local spawnSelection = math.random(1,#spawners)
local spawner = spawners[spawnSelection]

while true do

    wait(1)
    if Clone.Parent == nil then
        Clone.Parent = workspace.Flowers.level1
        Clone.CFrame = spawner.CFrame + Vector3.new(math.random(-5,5),1,math.random(-5,5))
        print("Clone added")
    end

end
ClickDetector.MouseClick:Connect(function(playerWhoClicked)
    _clone = Clone:Clone()
    playerWhoClicked.Character.Humanoid:MoveTo(_clone.Position,_clone)
    print("clicked")

    wait(1)

    _clone:Destroy()
    print("_clone removed")

    local flowerValue = playerWhoClicked.leaderstats.Flowers
    local coinsValue = playerWhoClicked.leaderstats.Coins
    flowerValue.Value = flowerValue.Value + 1
    coinsValue.Value = coinsValue.Value + 5
end)
</code>```

Body:
I'm working on a project that the player clicks on an object and it walks over to it, wait a second, then the object is removed from the game and points update on the leaderboard. The problem is it only works on the first round. The 2nd time, the new part spawned and it does have the ClickDetector as its child, but it does not function.

local flowers = game.ReplicatedStorage.Flowers.level1:GetChildren()
local selection = math.random(1,#flowers)
local Clone = flowers[selection]:Clone()

local ClickDetector = Instance.new("ClickDetector")
ClickDetector.Parent = Clone
ClickDetector.MaxActivationDistance = 500

local spawners = workspace.Spawners.level1:GetChildren()
local spawnSelection = math.random(1,#spawners)
local spawner = spawners[spawnSelection]

while true do

    wait(1)
    if Clone.Parent == nil then
        Clone.Parent = workspace.Flowers.level1
        Clone.CFrame = spawner.CFrame + Vector3.new(math.random(-5,5),1,math.random(-5,5))
        print("Clone added")

        ClickDetector.MouseClick:Connect(function(playerWhoClicked)
            playerWhoClicked.Character.Humanoid:MoveTo(Clone.Position,Clone)
            print("clicked")

            wait(1)

            Clone:Remove()
            print("Clone removed")

            local flowerValue = playerWhoClicked.leaderstats.Flowers
            local coinsValue = playerWhoClicked.leaderstats.Coins
            flowerValue.Value = flowerValue.Value + 1
            coinsValue.Value = coinsValue.Value + 5
        end)
    end

end

There are no error messages on output. Just that on the new spawned part, "clicked" is not printing.

Body:
Would this work, it was hard to understand your question, but I think what your saying is that the clone wont appear after you click again. Well did this work? Also, you don't need to have the click event inside the loop, and to make a clone every time the player clicks, inside the click function you make a clone of the Clone variable.

local flowers = game.ReplicatedStorage.Flowers.level1:GetChildren()
local selection = math.random(1,#flowers)
local Clone = flowers[selection]:Clone()

local ClickDetector = Instance.new("ClickDetector")
ClickDetector.Parent = Clone
ClickDetector.MaxActivationDistance = 500

local spawners = workspace.Spawners.level1:GetChildren()
local spawnSelection = math.random(1,#spawners)
local spawner = spawners[spawnSelection]

while true do

    wait(1)
    if Clone.Parent == nil then
        Clone.Parent = workspace.Flowers.level1
        Clone.CFrame = spawner.CFrame + Vector3.new(math.random(-5,5),1,math.random(-5,5))
        print("Clone added")
    end

end
ClickDetector.MouseClick:Connect(function(playerWhoClicked)
    _clone = Clone:Clone()
    playerWhoClicked.Character.Humanoid:MoveTo(_clone.Position,_clone)
    print("clicked")

    wait(1)

    _clone:Destroy()
    print("_clone removed")

    local flowerValue = playerWhoClicked.leaderstats.Flowers
    local coinsValue = playerWhoClicked.leaderstats.Coins
    flowerValue.Value = flowerValue.Value + 1
    coinsValue.Value = coinsValue.Value + 5
end)


问:
Convert column string to comma separated
I have a column data that has data like this:

```<code>/142//0/
/25//45//0/
</code>```

In my query i would like to see if i can show the data like this:

```<code>142,0
25,45,0
</code>```

Is this possible?

答:
```<code>Select REPLACE(firststep ,'/','') From (SELECT REPLACE(columname,'//',',') as firststep From Table) as a;
</code>```

Change columname and table name.

Body:
I have a column data that has data like this:

/142//0/
/25//45//0/

In my query i would like to see if i can show the data like this:

142,0
25,45,0

Is this possible?

Body:
Select REPLACE(firststep ,'/','') From (SELECT REPLACE(columname,'//',',') as firststep From Table) as a;

Change columname and table name.


问:
Incorrect duration and bitrate in ffmpeg-encoded audio
I am encoding raw data on Android using ffmpeg libraries. The native code reads the audio data from an external device and encodes it into AAC format in an mp4 container. I am finding that the audio data is successfully encoded (I can play it with Groove Music, my default Windows audio player). But the metadata, as reported by ffprobe, has an incorrect duration of 0.05 secs - it's actually several seconds long. Also the bitrate is reported wrongly as around 65kbps even though I specified 192kbps.

I've tried recordings of various durations but the result is always similar - the (very small) duration and bitrate. I've tried various other audio players such as Quicktime but they play only the first 0.05 secs or so of the audio.

I've removed error-checking from the following. The actual code checks every call and no problems are reported.

Initialisation:

```<code>void AudioWriter::initialise( const char *filePath )
{
    AVCodecID avCodecID = AVCodecID::AV_CODEC_ID_AAC;
    int bitRate = 192000;
    char *containerFormat = "mp4";
    int sampleRate = 48000;
    int nChannels = 2;

    mAvCodec = avcodec_find_encoder(avCodecID);
    mAvCodecContext = avcodec_alloc_context3(mAvCodec);
    mAvCodecContext->codec_id = avCodecID;
    mAvCodecContext->codec_type = AVMEDIA_TYPE_AUDIO;
    mAvCodecContext->sample_fmt = AV_SAMPLE_FMT_FLTP;
    mAvCodecContext->bit_rate = bitRate;
    mAvCodecContext->sample_rate = sampleRate;
    mAvCodecContext->channels = nChannels;
    mAvCodecContext->channel_layout = AV_CH_LAYOUT_STEREO;

    avcodec_open2( mAvCodecContext, mAvCodec, nullptr );

    mAvFormatContext = avformat_alloc_context();

    avformat_alloc_output_context2(&mAvFormatContext, nullptr, containerFormat, nullptr);
    mAvFormatContext->audio_codec = mAvCodec;
    mAvFormatContext->audio_codec_id = avCodecID;
    mAvOutputStream = avformat_new_stream(mAvFormatContext, mAvCodec);
    avcodec_parameters_from_context(mAvOutputStream->codecpar, mAvCodecContext);
    if (!(mAvFormatContext->oformat->flags & AVFMT_NOFILE))
    {
        avio_open(&mAvFormatContext->pb, filePath, AVIO_FLAG_WRITE);
    }

    if ( mAvFormatContext->oformat->flags & AVFMT_GLOBALHEADER )
    {
        mAvCodecContext->flags |= AV_CODEC_FLAG_GLOBAL_HEADER;
    }

    avformat_write_header(mAvFormatContext, NULL);

    mAvAudioFrame = av_frame_alloc();
    mAvAudioFrame->nb_samples = mAvCodecContext->frame_size;
    mAvAudioFrame->format = mAvCodecContext->sample_fmt;
    mAvAudioFrame->channel_layout = mAvCodecContext->channel_layout;

    av_samples_get_buffer_size(NULL, mAvCodecContext->channels, mAvCodecContext->frame_size,
                                                 mAvCodecContext->sample_fmt, 0);
    av_frame_get_buffer(mAvAudioFrame, 0);
    av_frame_make_writable(mAvAudioFrame);
    mAvPacket = av_packet_alloc();
  }
</code>```

Encoding:

```<code>// SoundRecording is a custom class with the raw samples to be encoded
bool AudioWriter::encodeToContainer( SoundRecording *soundRecording )
{
    int ret;
    int frameCount = mAvCodecContext->frame_size;
    int nChannels = mAvCodecContext->channels;
    float *buf = new float[frameCount*nChannels];

    while ( soundRecording->hasReadableData() )
    {
        //Populate the frame
        int samplesRead = soundRecording->read( buf, frameCount*nChannels );
        // Planar data
        int nFrames = samplesRead/nChannels;
        for ( int i = 0; i < nFrames; ++i )
        {
            for (int c = 0; c < nChannels; ++c )
            {
                samples[c][i] = buf[nChannels*i +c];
            }
        }
        // Fill a gap at the end with silence
        if ( samplesRead < frameCount*nChannels )
        {
            for ( int i = samplesRead; i < frameCount*nChannels; ++i )
            {
                for (int c = 0; c < nChannels; ++c )
                {
                    samples[c][i] = 0.0;
                }
            }
        }

    encodeFrame( mAvAudioFrame ) )
    }

    finish();
 }

bool AudioWriter::encodeFrame( AVFrame *frame )
{
    //send the frame for encoding
    int ret;

    if ( frame != nullptr )
    {
        frame->pts = mAudFrameCounter++;
    }
    avcodec_send_frame(mAvCodecContext, frame );

    while (ret >= 0)
    {
        ret = avcodec_receive_packet(mAvCodecContext, mAvPacket);
        if (ret == AVERROR(EAGAIN) || ret == AVERROR_EOF )
        {
            break;
        }
        else
            if (ret < 0) {
             return false;
        }
        av_packet_rescale_ts(mAvPacket, mAvCodecContext->time_base, mAvOutputStream->time_base);
        mAvPacket->stream_index = mAvOutputStream->index;

        av_interleaved_write_frame(mAvFormatContext, mAvPacket);
         av_packet_unref(mAvPacket);
    }

    return true;
}

void AudioWriter::finish()
{
    // Flush by sending a null frame
    encodeFrame( nullptr );

    av_write_trailer(mAvFormatContext);
}

</code>```

Since the resultant file contains the recorded music, the code to manipulate the audio data seems to be correct (unless I am overwriting other memory somehow).

The inaccurate duration and bitrate suggest that information concerning time is not being properly managed. I set the pts of the frames using a simple increasing integer. I'm unclear what the code that sets the timestamp and stream index achieves - and whether it's even necessary: I copied it from supposedly working code but I've seen other code without it.

Can anyone see what I'm doing wrong?

答: The timestamp need to be correct. Set the time_base to 1/sample_rate and increment the timestamp by 1024 each frame. Note: 1024 is aac specific. If you change codecs, you need to change the frame size. </p>

Body:
I am encoding raw data on Android using ffmpeg libraries. The native code reads the audio data from an external device and encodes it into AAC format in an mp4 container. I am finding that the audio data is successfully encoded (I can play it with Groove Music, my default Windows audio player). But the metadata, as reported by ffprobe, has an incorrect duration of 0.05 secs - it's actually several seconds long. Also the bitrate is reported wrongly as around 65kbps even though I specified 192kbps.

I've tried recordings of various durations but the result is always similar - the (very small) duration and bitrate. I've tried various other audio players such as Quicktime but they play only the first 0.05 secs or so of the audio.

I've removed error-checking from the following. The actual code checks every call and no problems are reported.

Initialisation:

void AudioWriter::initialise( const char *filePath )
{
    AVCodecID avCodecID = AVCodecID::AV_CODEC_ID_AAC;
    int bitRate = 192000;
    char *containerFormat = "mp4";
    int sampleRate = 48000;
    int nChannels = 2;

    mAvCodec = avcodec_find_encoder(avCodecID);
    mAvCodecContext = avcodec_alloc_context3(mAvCodec);
    mAvCodecContext->codec_id = avCodecID;
    mAvCodecContext->codec_type = AVMEDIA_TYPE_AUDIO;
    mAvCodecContext->sample_fmt = AV_SAMPLE_FMT_FLTP;
    mAvCodecContext->bit_rate = bitRate;
    mAvCodecContext->sample_rate = sampleRate;
    mAvCodecContext->channels = nChannels;
    mAvCodecContext->channel_layout = AV_CH_LAYOUT_STEREO;

    avcodec_open2( mAvCodecContext, mAvCodec, nullptr );

    mAvFormatContext = avformat_alloc_context();

    avformat_alloc_output_context2(&mAvFormatContext, nullptr, containerFormat, nullptr);
    mAvFormatContext->audio_codec = mAvCodec;
    mAvFormatContext->audio_codec_id = avCodecID;
    mAvOutputStream = avformat_new_stream(mAvFormatContext, mAvCodec);
    avcodec_parameters_from_context(mAvOutputStream->codecpar, mAvCodecContext);
    if (!(mAvFormatContext->oformat->flags & AVFMT_NOFILE))
    {
        avio_open(&mAvFormatContext->pb, filePath, AVIO_FLAG_WRITE);
    }

    if ( mAvFormatContext->oformat->flags & AVFMT_GLOBALHEADER )
    {
        mAvCodecContext->flags |= AV_CODEC_FLAG_GLOBAL_HEADER;
    }

    avformat_write_header(mAvFormatContext, NULL);

    mAvAudioFrame = av_frame_alloc();
    mAvAudioFrame->nb_samples = mAvCodecContext->frame_size;
    mAvAudioFrame->format = mAvCodecContext->sample_fmt;
    mAvAudioFrame->channel_layout = mAvCodecContext->channel_layout;

    av_samples_get_buffer_size(NULL, mAvCodecContext->channels, mAvCodecContext->frame_size,
                                                 mAvCodecContext->sample_fmt, 0);
    av_frame_get_buffer(mAvAudioFrame, 0);
    av_frame_make_writable(mAvAudioFrame);
    mAvPacket = av_packet_alloc();
  }

Encoding:

// SoundRecording is a custom class with the raw samples to be encoded
bool AudioWriter::encodeToContainer( SoundRecording *soundRecording )
{
    int ret;
    int frameCount = mAvCodecContext->frame_size;
    int nChannels = mAvCodecContext->channels;
    float *buf = new float[frameCount*nChannels];

    while ( soundRecording->hasReadableData() )
    {
        //Populate the frame
        int samplesRead = soundRecording->read( buf, frameCount*nChannels );
        // Planar data
        int nFrames = samplesRead/nChannels;
        for ( int i = 0; i < nFrames; ++i )
        {
            for (int c = 0; c < nChannels; ++c )
            {
                samples[c][i] = buf[nChannels*i +c];
            }
        }
        // Fill a gap at the end with silence
        if ( samplesRead < frameCount*nChannels )
        {
            for ( int i = samplesRead; i < frameCount*nChannels; ++i )
            {
                for (int c = 0; c < nChannels; ++c )
                {
                    samples[c][i] = 0.0;
                }
            }
        }

    encodeFrame( mAvAudioFrame ) )
    }

    finish();
 }

bool AudioWriter::encodeFrame( AVFrame *frame )
{
    //send the frame for encoding
    int ret;

    if ( frame != nullptr )
    {
        frame->pts = mAudFrameCounter++;
    }
    avcodec_send_frame(mAvCodecContext, frame );

    while (ret >= 0)
    {
        ret = avcodec_receive_packet(mAvCodecContext, mAvPacket);
        if (ret == AVERROR(EAGAIN) || ret == AVERROR_EOF )
        {
            break;
        }
        else
            if (ret < 0) {
             return false;
        }
        av_packet_rescale_ts(mAvPacket, mAvCodecContext->time_base, mAvOutputStream->time_base);
        mAvPacket->stream_index = mAvOutputStream->index;

        av_interleaved_write_frame(mAvFormatContext, mAvPacket);
         av_packet_unref(mAvPacket);
    }

    return true;
}

void AudioWriter::finish()
{
    // Flush by sending a null frame
    encodeFrame( nullptr );

    av_write_trailer(mAvFormatContext);
}

Since the resultant file contains the recorded music, the code to manipulate the audio data seems to be correct (unless I am overwriting other memory somehow).

The inaccurate duration and bitrate suggest that information concerning time is not being properly managed. I set the pts of the frames using a simple increasing integer. I'm unclear what the code that sets the timestamp and stream index achieves - and whether it's even necessary: I copied it from supposedly working code but I've seen other code without it.

Can anyone see what I'm doing wrong?


问:
Find a row and its index then delete this row using index
I would looking for a way to basically delete, step by step, a specific row.

```<code>      categorie     note    nbavis  taille      nbdll       type    prix    genre
10  ART_AND_DESIGN  4.1     159     19000000    10000       Free    0       Art & Design
15  ART_AND_DESIGN  3.9     967     14000000    500000      Free    0       Art & Design
16  ART_AND_DESIGN  4.7     87510   8.7000000   5000000     Free    0       Art & Design
17  ART_AND_DESIGN  4.5     215644  25000000    50000000    Free    0       Art & Design
28  ART_AND_DESIGN  4.3     967     2.8000000   100000      Free    Everyone Art & Design
</code>```

After deleting some row because of NaN, id's are different than index.
So, I used :

```<code>app.loc[app['prix']=='Everyone',:]
</code>```

To learn more about which row contains "Everyone" in the column "prix".

Now I would like to find the index of this row which is different from 28 (On this example, it's 4, python count from 0 right ?)

I tried :

```<code>app.index[app['prix'] == 'Everyone'].tolist()
</code>```

But the result give me [28].
Then I tried

```<code>app.loc[app.index==28]
</code>```

Same result.
app.index[28] will told me :

```<code>IndexError: index 28 is out of bounds for axis 0 with size 5
</code>```

I'm pretty sure I have missed something. There are many ways to do it, but I would like to do it with this approach. For exemple .drop , del , ...
There are many topics, but none fits with these conditions. Maybe it's not possible. How would you do this with few code lines?

Thanks

EDIT :
There are the solutions I was looking for :

♣ Reset the Index

```<code>app = app.reset_index()
</code>```

♣ Find the rows (and the index)

```<code>app.loc[app['prix']=='Everyone',:]
</code>```

♣ Delete this row using the index

```<code>app = app.drop(4795)
</code>```

◘ Other solution was : app.drop([28])
Thanks to this community. And thanks for the Edit of my topic, my english is horrible ahah.

答:
To drop the row inplace:</p>

```<code>app.drop(app[app['prix']=='Everyone'].index, inplace=True)
</code>```

Body:
I would looking for a way to basically delete, step by step, a specific row.

      categorie     note    nbavis  taille      nbdll       type    prix    genre
10  ART_AND_DESIGN  4.1     159     19000000    10000       Free    0       Art & Design
15  ART_AND_DESIGN  3.9     967     14000000    500000      Free    0       Art & Design
16  ART_AND_DESIGN  4.7     87510   8.7000000   5000000     Free    0       Art & Design
17  ART_AND_DESIGN  4.5     215644  25000000    50000000    Free    0       Art & Design
28  ART_AND_DESIGN  4.3     967     2.8000000   100000      Free    Everyone Art & Design

After deleting some row because of NaN, id's are different than index.
So, I used :

app.loc[app['prix']=='Everyone',:]

To learn more about which row contains "Everyone" in the column "prix".

Now I would like to find the index of this row which is different from 28 (On this example, it's 4, python count from 0 right ?)

I tried :

app.index[app['prix'] == 'Everyone'].tolist()

But the result give me [28].
Then I tried

app.loc[app.index==28]

Same result.
app.index[28] will told me :

IndexError: index 28 is out of bounds for axis 0 with size 5

I'm pretty sure I have missed something. There are many ways to do it, but I would like to do it with this approach. For exemple .drop , del , ...
There are many topics, but none fits with these conditions. Maybe it's not possible. How would you do this with few code lines?

Thanks

EDIT :
There are the solutions I was looking for :

♣ Reset the Index

app = app.reset_index()

♣ Find the rows (and the index)

app.loc[app['prix']=='Everyone',:]

♣ Delete this row using the index

app = app.drop(4795)

◘ Other solution was : app.drop([28])
Thanks to this community. And thanks for the Edit of my topic, my english is horrible ahah.

Body:
To drop the row inplace:

app.drop(app[app['prix']=='Everyone'].index, inplace=True)


问:
Liquid haskell creating a new bytestring with PS
I'm making some bindings to C from Haskell, and trying to make it safer with LiquidHaskell. I'm having some trouble with specifying the length of a bytestring in the LH type annotation.

I have an enhanced ByteString type in LiquidHaskell that includes its length:

```<code>{-@ type ByteString Blen = {v:B.ByteString | bslen v == Blen} @-}
</code>```

I am getting the following error when I run Liquidhaskell:

```<code>**** RESULT: UNSAFE ************************************************************

 /home/t/liquidproblem/Main.hs:47:3-22: Error: Liquid Type Mismatch

 47 |   Bi.PS foreignPtr 0 l
        ^^^^^^^^^^^^^^^^^^^^

   Inferred type
     VV : {v : Data.ByteString.Internal.ByteString | 0 <= bslen v
                                                     && bslen v == stringlen v}

   not a subtype of Required type
     VV : {VV : Data.ByteString.Internal.ByteString | bslen VV == l}

   In Context
     l : {l : GHC.Types.Int | l >= 0}
</code>```

Line 47 is:

```<code>44 {-@ makeBs :: ForeignPtr Word8 -> l:NonNeg -> ByteString l @-}
45 makeBs :: F.ForeignPtr F.Word8 -> Int -> B.ByteString
46 makeBs foreignPtr l =
47   Bi.PS foreignPtr 0 l
</code>```

(I know this seems a bit of a silly function, but it got put in because the debugging process was to factor out bits and put LH annotations on them till I found the problem.)

The relevant imports are:

```<code>import qualified Data.ByteString.Internal as Bi
import qualified Data.ByteString as B
import qualified Foreign as F
</code>```

The LH NonNeg type is

```<code>{-@ type NonNeg = {i:Int | i >= 0} @-}
</code>```

答:
LiquidHaskell doesn't know that the ByteString returned by makeBs has length l, and it can't prove that from the information available to it.

You know it will because you know that the third argument to the PS constructor is the length. So at this point you have two (or possibly one) options:

You can try to tell LH what you know about the PS constructor, and give it an annotation like
{-@ Bi.PS :: _ -> _ -> l:NonNeg -> ByteString l @-}. I tried this and it didn't quite work because there are internal GHC types in the signature of PS that LH doesn't seem so good at handling. YMMV. Or, you can:

Forget about annotating PS and mark your own function with assume: {-@ assume makeBs :: ForeignPtr Word8 -> l:NonNeg -> ByteString l @-}. Of course, this gets riskier if the function marked with assume gets any bigger—silly though it may seem, you might want to keep makeBs around if you go this route.

Body:
I'm making some bindings to C from Haskell, and trying to make it safer with LiquidHaskell. I'm having some trouble with specifying the length of a bytestring in the LH type annotation.

I have an enhanced ByteString type in LiquidHaskell that includes its length:

{-@ type ByteString Blen = {v:B.ByteString | bslen v == Blen} @-}

I am getting the following error when I run Liquidhaskell:

**** RESULT: UNSAFE ************************************************************

 /home/t/liquidproblem/Main.hs:47:3-22: Error: Liquid Type Mismatch

 47 |   Bi.PS foreignPtr 0 l
        ^^^^^^^^^^^^^^^^^^^^

   Inferred type
     VV : {v : Data.ByteString.Internal.ByteString | 0 <= bslen v
                                                     && bslen v == stringlen v}

   not a subtype of Required type
     VV : {VV : Data.ByteString.Internal.ByteString | bslen VV == l}

   In Context
     l : {l : GHC.Types.Int | l >= 0}

Line 47 is:

44 {-@ makeBs :: ForeignPtr Word8 -> l:NonNeg -> ByteString l @-}
45 makeBs :: F.ForeignPtr F.Word8 -> Int -> B.ByteString
46 makeBs foreignPtr l =
47   Bi.PS foreignPtr 0 l

(I know this seems a bit of a silly function, but it got put in because the debugging process was to factor out bits and put LH annotations on them till I found the problem.)

The relevant imports are:

import qualified Data.ByteString.Internal as Bi
import qualified Data.ByteString as B
import qualified Foreign as F

The LH NonNeg type is

{-@ type NonNeg = {i:Int | i >= 0} @-}


问:
Check if string is date or not using Moment.js
So consider the following object:

You might be thinking, the easiest way is too loop over the object, check if each property is valid. as you can see we have one date object in this object. But pay attention to that clinic_id.

Here's my code to do what I mentioned above:

```<code>  for(let property in formData) {
    let date = moment(formData[property]).isValid()

    if (date) {
      formData[property] = moment(formData[property]).format('YYYY-MM-DD');
    }
  }
</code>```

You would think this might work, heck you might even suggest to loop through looking to see what .getDate() on each property would say, except .getDate() will throw an error on anything not a date object.

So what does the code above output:

The only thing in here that should be a date value is date_of_consent not clinic_id. I understand why moment converted it to a date but it should not have happened.

I will have objects that have date objects in them, is there a way for me to find said date object in an object and replace it with a moment.js formatted one?

答:
You can check if you have Date objects in multiple ways.

You could check the instance of every item to check if it's a Date :

```<code>if (formData[property] instanceof Date) // returns true or false
</code>```

You could also check if the function .getDate() exists :

```<code>typeof (formData[property]).getDate === 'function'
</code>```

Or try to get the value and catch the possible error :

```<code>try {
    var date = formData[property].getDate();
    /* Do your formatting */
} catch (e) {
    //This was not a Date object
}
</code>```

Body:
So consider the following object:

You might be thinking, the easiest way is too loop over the object, check if each property is valid. as you can see we have one date object in this object. But pay attention to that clinic_id.

Here's my code to do what I mentioned above:

  for(let property in formData) {
    let date = moment(formData[property]).isValid()

    if (date) {
      formData[property] = moment(formData[property]).format('YYYY-MM-DD');
    }
  }

You would think this might work, heck you might even suggest to loop through looking to see what .getDate() on each property would say, except .getDate() will throw an error on anything not a date object.

So what does the code above output:

The only thing in here that should be a date value is date_of_consent not clinic_id. I understand why moment converted it to a date but it should not have happened.

I will have objects that have date objects in them, is there a way for me to find said date object in an object and replace it with a moment.js formatted one?

Body:
You can check if you have Date objects in multiple ways.

You could check the instance of every item to check if it's a Date :

if (formData[property] instanceof Date) // returns true or false

You could also check if the function .getDate() exists :

typeof (formData[property]).getDate === 'function'

Or try to get the value and catch the possible error :

try {
    var date = formData[property].getDate();
    /* Do your formatting */
} catch (e) {
    //This was not a Date object
}


问:
Datasource V2 Reader (Spark Structured Streaming) - offsets out of order
I am currently implementing two custom readers using the V2 api for a spark structured streaming job. After the job runs for ~30-60 minutes, it bombs with:

Caused by: java.lang.RuntimeException: Offsets committed out of order: 608799 followed by 2982

I am repurposing the examples found here and it is bombing at line: 206.

Instead of using the twitter stream that is provided in the example I am implementing it for JMS & SQS.

My question is: has anyone encountered this issue? Or is there something wrong with that implementation?

Code snippet:

override def commit(end: Offset): Unit = {
    internalLog(s"** commit($end) lastOffsetCommitted: $lastOffsetCommitted")

    val newOffset = TwitterOffset.convert(end).getOrElse(
      sys.error(s"TwitterStreamMicroBatchReader.commit() received an offset ($end) that did not " +
        s"originate with an instance of this class")
    )

    val offsetDiff = (newOffset.offset - lastOffsetCommitted.offset).toInt

    if (offsetDiff < 0) {
      sys.error(s"Offsets committed out of order: $lastOffsetCommitted followed by $end")
    }

    tweetList.trimStart(offsetDiff)
    lastOffsetCommitted = newOffset
}
```

<p>I can't find an answer with my usual outlets. I did, however, see this. One point that was made is to delete checkpoint data - which doesn't seem like a viable solution in a production system. The other was that the source system doesn't maintain offset information? I was under the impression that spark would be handling the offset information by itself. If this second point is the problem, how can I ensure that the source system handles this paradigm.</p>

<p>Please let me know if I can provide more information.</p>

<p><b>Edit:</b> Looking at the MicroBatchReader interface, the documentation for commit says:</p>

```    /**
     * Informs the source that Spark has completed processing all data for offsets less than or
     * equal to `end` and will only request offsets greater than `end` in the future.
     */
    void commit(Offset end);
```

So the question becomes, why is spark sending me commit offsets that has already been committed?

答:
Answering my own question in case it helps someone,

I should have added more information to the question - this job is running on EMR and is using EFS to checkpoint data.

The problem occurred when I used Amazon's amazon-efs-utils to mount EFS. For some reason each worker was not able to see the other workers' reads and writes - as if EFS didn't mount.

The solution was to switch to nfs-utils to mount EFS (per AWS instructions) so that each worker could accurately read the checkpoint data.

Body:
I am currently implementing two custom readers using the V2 api for a spark structured streaming job. After the job runs for ~30-60 minutes, it bombs with:

Caused by: java.lang.RuntimeException: Offsets committed out of order: 608799 followed by 2982

I am repurposing the examples found here and it is bombing at line: 206.

Instead of using the twitter stream that is provided in the example I am implementing it for JMS & SQS.

My question is: has anyone encountered this issue? Or is there something wrong with that implementation?

Code snippet:

override def commit(end: Offset): Unit = {
    internalLog(s"** commit($end) lastOffsetCommitted: $lastOffsetCommitted")

    val newOffset = TwitterOffset.convert(end).getOrElse(
      sys.error(s"TwitterStreamMicroBatchReader.commit() received an offset ($end) that did not " +
        s"originate with an instance of this class")
    )

    val offsetDiff = (newOffset.offset - lastOffsetCommitted.offset).toInt

    if (offsetDiff < 0) {
      sys.error(s"Offsets committed out of order: $lastOffsetCommitted followed by $end")
    }

    tweetList.trimStart(offsetDiff)
    lastOffsetCommitted = newOffset
}

I can't find an answer with my usual outlets. I did, however, see this. One point that was made is to delete checkpoint data - which doesn't seem like a viable solution in a production system. The other was that the source system doesn't maintain offset information? I was under the impression that spark would be handling the offset information by itself. If this second point is the problem, how can I ensure that the source system handles this paradigm.

Please let me know if I can provide more information.

Edit: Looking at the MicroBatchReader interface, the documentation for commit says:

    /**
     * Informs the source that Spark has completed processing all data for offsets less than or
     * equal to `end` and will only request offsets greater than `end` in the future.
     */
    void commit(Offset end);

So the question becomes, why is spark sending me commit offsets that has already been committed?


问:
TeamCity to read and update version number from version file
We are using TeamCity as a CI server and we've been thinking how to implement a method for versioning our releases that is safe (i.e. minimal opportunity for human error) but also that requires the least effort possible. So far the following seems the most logical:

Create a version file (or use assemblyinfo.cs). The version information would be in the format .... This file will be checked into my VCS.

Run a TeamCity build

TeamCity reads the version file from step 1, picks out each individual numbers and sets corresponding TeamCity build parameters (so I can use them later for things such as creating nuget packages and labelling)

TeamCity auto increments the final 'build count' value.

TeamCity saves the new version information back to the version file (with the updated 'build count')

TeamCity then runs through the rest of my build steps, building files, running unit tests, setting the correct assembly versions etc.

TeamCity finally checks the file back into VCS

We believe the benefit of doing things this way is should we need to do a fix of an old release then the version number will be correct and TeamCity can simply work with the old version file, and incrementing the build count as usual. This does make the assumption that we are updating our major/minor/patch versions correctly. This will hopefully work nicely with the soon to be released feature of TeamCity 7.1 that lets you choose which branch to build via the custom build dialog.

From what I've already read so far these sort of operations should be possible in TeamCity but we're looking for the easiest root to get this fixed as we're only a two-man outfit and we can't afford to invest large amounts of time into becoming experts at Nant or powershell only to find it might not do what we want.

So I guess to sum up my questions are as follows:

Is what I am proposing possible?

If yes then what is the best tool to use given my limited experience of Nant, powershell etc (i.e. which is the quickest to learn for someone whose command line and script experience extends as far as simple DOS operations and a bit of VBScript)

Any help is greatly appreciated.

答:
Add a build step using the PowerShell runner type

Set the Script field to Source code

In the ScriptSource field enter the following:

```<code># Get AssemblyInfo file's contents
$file = "GlobalAssemblyInfo.cs"
$contents = get-content $file -Raw

# Regex to get the version number from AssemblyInfo file contents
$regex = new-object System.Text.RegularExpressions.Regex ('(^\s*\[\s*assembly\s*:\s*((System\s*\.)?\s*Reflection\s*\.)?\s*AssemblyVersion(Attribute)?\s*\(\s*@?\")(?<version>.*?)(\"\s*\)\s*\])', [System.Text.RegularExpressions.RegexOptions]::MultiLine)

# Get the version number
$version = $regex.Match($contents).Groups["version"].Value

# Update TeamCity buildNumber parameter using stdout
echo "##teamcity[buildNumber '$($version)']"
</code>```

ContentLicense: CC BY-SA 3.0

Body:
Add a build step using the PowerShell runner type

Set the Script field to Source code

In the ScriptSource field enter the following:

# Get AssemblyInfo file's contents
$file = "GlobalAssemblyInfo.cs"
$contents = get-content $file -Raw

# Regex to get the version number from AssemblyInfo file contents
$regex = new-object System.Text.RegularExpressions.Regex ('(^\s*\[\s*assembly\s*:\s*((System\s*\.)?\s*Reflection\s*\.)?\s*AssemblyVersion(Attribute)?\s*\(\s*@?\")(?<version>.*?)(\"\s*\)\s*\])', [System.Text.RegularExpressions.RegexOptions]::MultiLine)

# Get the version number
$version = $regex.Match($contents).Groups["version"].Value

# Update TeamCity buildNumber parameter using stdout
echo "##teamcity[buildNumber '$($version)']"


问:
Spark SQL Window over interval of between two specified time boundaries - between 3 hours and 2 hours ago
What is the proper way of specifying window interval in Spark SQL, using two predefined boundaries?

I am trying to sum up values from my table over a window of "between 3 hours ago and 2 hours ago".

When I run this query:

```<code>select *, sum(value) over (
partition by a, b
order by cast(time_value as timestamp)
range between interval 2 hours preceding and current row
) as sum_value
from my_temp_table;
</code>```

That works. I get results that I expect, i.e. sums of values that fall into 2 hours rolling window.

Now, what I need is to have that rolling window not being bound to the current row but to take into account rows between 3 hours ago and 2 hours ago.
I tried with:

```<code>select *, sum(value) over (
partition by a, b
order by cast(time_value as timestamp)
range between interval 3 hours preceding and 2 hours preceding
) as sum_value
from my_temp_table;
</code>```

But I get extraneous input 'hours' expecting {'PRECEDING', 'FOLLOWING'} error.

I also tried with:

```<code>select *, sum(value) over (
partition by a, b
order by cast(time_value as timestamp)
range between interval 3 hours preceding and interval 2 hours preceding
) as sum_value
from my_temp_table;
</code>```

but then I get different error scala.MatchError: CalendarIntervalType (of class org.apache.spark.sql.types.CalendarIntervalType$)

Third option I tried is:

```<code>select *, sum(value) over (
partition by a, b
order by cast(time_value as timestamp)
range between interval 3 hours preceding and 2 preceding
) as sum_value
from my_temp_table;
</code>```

and it doesn't work as we would expect: cannot resolve 'RANGE BETWEEN interval 3 hours PRECEDING AND 2 PRECEDING' due to data type mismatch

I am having difficulties finding the docs for interval type as this link doesn't say enough and other information is kinda half baked. At least what I found.

答:
Since range intervals didn't work their thing, I had to turn to an alternative approach.
It goes something like this:

prepare a list of intervals for which computation needs to be performed

for each of the intervals, run the computation

each of those iterations produces a data frame

after the iterations, we have a list of data frames

union the data frames from the list into one bigger data frame

write out the results

In my case, I had to run computations for each hour of the day and combine those "hourly" results, i.e. a list of 24 data frames, into one, "daily", data frame.

Code, from very high level perspective, looks like this:

```<code>val hourlyDFs = for ((hourStart, hourEnd) <- (hoursToStart, hoursToEnd).zipped) yield {
    val data = data.where($"hour" <= lit(hourEnd) && $"hour" >= lit(hourStart))
    // do stuff
    // return a data frame
}
hourlyDFs.toSeq().reduce(_.union(_))
</code>```

Body:
What is the proper way of specifying window interval in Spark SQL, using two predefined boundaries?

I am trying to sum up values from my table over a window of "between 3 hours ago and 2 hours ago".

When I run this query:

select *, sum(value) over (
partition by a, b
order by cast(time_value as timestamp)
range between interval 2 hours preceding and current row
) as sum_value
from my_temp_table;

That works. I get results that I expect, i.e. sums of values that fall into 2 hours rolling window.

Now, what I need is to have that rolling window not being bound to the current row but to take into account rows between 3 hours ago and 2 hours ago.
I tried with:

select *, sum(value) over (
partition by a, b
order by cast(time_value as timestamp)
range between interval 3 hours preceding and 2 hours preceding
) as sum_value
from my_temp_table;

But I get extraneous input 'hours' expecting {'PRECEDING', 'FOLLOWING'} error.

I also tried with:

select *, sum(value) over (
partition by a, b
order by cast(time_value as timestamp)
range between interval 3 hours preceding and interval 2 hours preceding
) as sum_value
from my_temp_table;

but then I get different error scala.MatchError: CalendarIntervalType (of class org.apache.spark.sql.types.CalendarIntervalType$)

Third option I tried is:

select *, sum(value) over (
partition by a, b
order by cast(time_value as timestamp)
range between interval 3 hours preceding and 2 preceding
) as sum_value
from my_temp_table;

and it doesn't work as we would expect: cannot resolve 'RANGE BETWEEN interval 3 hours PRECEDING AND 2 PRECEDING' due to data type mismatch

I am having difficulties finding the docs for interval type as this link doesn't say enough and other information is kinda half baked. At least what I found.

Body:
Since range intervals didn't work their thing, I had to turn to an alternative approach.
It goes something like this:

prepare a list of intervals for which computation needs to be performed

for each of the intervals, run the computation

each of those iterations produces a data frame

after the iterations, we have a list of data frames

union the data frames from the list into one bigger data frame

write out the results

In my case, I had to run computations for each hour of the day and combine those "hourly" results, i.e. a list of 24 data frames, into one, "daily", data frame.

Code, from very high level perspective, looks like this:

val hourlyDFs = for ((hourStart, hourEnd) <- (hoursToStart, hoursToEnd).zipped) yield {
    val data = data.where($"hour" <= lit(hourEnd) && $"hour" >= lit(hourStart))
    // do stuff
    // return a data frame
}
hourlyDFs.toSeq().reduce(_.union(_))


问:
What is the command to access my user details in Mongo shell?
I obviously do have a user in my local copy of my MongoDB database.

I went to the appropriate database via use server_users, but when I do db.getUsers(), I get an empty array.

But when I run this in my node REPL:

```<code>> Buffer.from(session, 'base64').toString('utf8')
'{"passport":{"user":"5ad25c401dfbaee22188a93b"}}'
</code>```

You can clearly see I do have a user in my Mongo database.

How do I pull up that user in my mongo shell?

This is my passport.js file:

```<code>const passport = require('passport');
const GoogleStrategy = require('passport-google-oauth20').Strategy;
const mongoose = require('mongoose');
const keys = require('../config/keys');

const User = mongoose.model('users');

passport.serializeUser((user, done) => {
  done(null, user.id);
});

passport.deserializeUser((id, done) => {
  User.findById(id).then(user => {
    done(null, user);
  });
});

// passport.use() is a generic register to make Passport
// aware of new strategy
// creates a new instance to authenticate users
passport.use(
  new GoogleStrategy(
    {
      clientID: keys.googleClientID,
      clientSecret: keys.googleClientSecret,
      callbackURL: '/auth/google/callback',
      proxy: true
    },
    async (accessToken, refreshToken, profile, done) => {
      const existingUser = await User.findOne({ googleId: profile.id });

      if (existingUser) {
        // we already have a record with given profile id
        done(null, existingUser);
      }
      // we dont have a user record with this id, make a new record
      const user = await new User({ googleId: profile.id }).save();
      done(null, user);
    }
  )
);
</code>```

答:
Step 1

```<code>> show dbs
    admin         0.000GB
    auth          0.000GB
    local         0.000GB
    muber         0.000GB
    seed_data     0.000GB
    server_users  0.000GB
    upstar_music  0.004GB
    users_test    0.000GB
</code>```

Step 2

```<code>> use auth
switched to db auth
</code>```

Step 3

```<code>> db.getCollection('users').find({}).pretty()
{
    "_id" : ObjectId("5cedf41999c20922fd05cdce"),
    "email" : "test123@example.com",
    "password" : "$2a$10$Yu194aPMrwuA6JZStTwu7OmaAugFNM1XvXDDBVpF1d5wKKHaPmLFe",
    "__v" : 0
}
{
    "_id" : ObjectId("5ceee2237d1b22dfbf37820f"),
    "email" : "test99@example.com",
    "password" : "$2a$10$.a9jWOSQbLG32lInCCUwGeqZDM9ksbTN7MDMo1kbOaApmMUr3cCLO",
    "__v" : 0
}
{
    "_id" : ObjectId("5ceeeb02d35e4ee61d602040"),
    "email" : "test919@example.com",
    "password" : "$2a$10$fSq31UO/4IEPMaXzgIHXK.zlMO8IUPsVh4Vf9MRo7zldk9dSgiwVS",
    "__v" : 0
}
{
    "_id" : ObjectId("5ceeee0bd72c8ae85dcbbc7f"),
    "email" : "test900@example.com",
    "password" : "$2a$10$l/99bU2MpElHhVFNXk1tB.k69DOAcdc/ySuek33MjtjAB0yLTqAqi",
    "__v" : 0
}
{
    "_id" : ObjectId("5ceef1cddf3064eaf8530f68"),
    "email" : "test901@example.com",
    "password" : "$2a$10$aKzilNGIm9HZx0iSXhsvbe9UCFqNt5FWjVd7T.GX7uKawAfCyG3JS",
    "__v" : 0
}
{
    "_id" : ObjectId("5cef25fc43a429ec05759849"),
    "email" : "test902@example.com",
    "password" : "$2a$10$JY0w/UuvT4Ub7T3RR9.VVOIH6V4RX9bTwoWzwEBbTiidPEiasakNq",
    "__v" : 0
}
</code>```

Body:
I obviously do have a user in my local copy of my MongoDB database.

I went to the appropriate database via use server_users, but when I do db.getUsers(), I get an empty array.

But when I run this in my node REPL:

> Buffer.from(session, 'base64').toString('utf8')
'{"passport":{"user":"5ad25c401dfbaee22188a93b"}}'

You can clearly see I do have a user in my Mongo database.

How do I pull up that user in my mongo shell?

This is my passport.js file:

const passport = require('passport');
const GoogleStrategy = require('passport-google-oauth20').Strategy;
const mongoose = require('mongoose');
const keys = require('../config/keys');

const User = mongoose.model('users');

passport.serializeUser((user, done) => {
  done(null, user.id);
});

passport.deserializeUser((id, done) => {
  User.findById(id).then(user => {
    done(null, user);
  });
});

// passport.use() is a generic register to make Passport
// aware of new strategy
// creates a new instance to authenticate users
passport.use(
  new GoogleStrategy(
    {
      clientID: keys.googleClientID,
      clientSecret: keys.googleClientSecret,
      callbackURL: '/auth/google/callback',
      proxy: true
    },
    async (accessToken, refreshToken, profile, done) => {
      const existingUser = await User.findOne({ googleId: profile.id });

      if (existingUser) {
        // we already have a record with given profile id
        done(null, existingUser);
      }
      // we dont have a user record with this id, make a new record
      const user = await new User({ googleId: profile.id }).save();
      done(null, user);
    }
  )
);

Body:
Step 1

> show dbs
    admin         0.000GB
    auth          0.000GB
    local         0.000GB
    muber         0.000GB
    seed_data     0.000GB
    server_users  0.000GB
    upstar_music  0.004GB
    users_test    0.000GB

Step 2

> use auth
switched to db auth

Step 3

> db.getCollection('users').find({}).pretty()
{
    "_id" : ObjectId("5cedf41999c20922fd05cdce"),
    "email" : "test123@example.com",
    "password" : "$2a$10$Yu194aPMrwuA6JZStTwu7OmaAugFNM1XvXDDBVpF1d5wKKHaPmLFe",
    "__v" : 0
}
{
    "_id" : ObjectId("5ceee2237d1b22dfbf37820f"),
    "email" : "test99@example.com",
    "password" : "$2a$10$.a9jWOSQbLG32lInCCUwGeqZDM9ksbTN7MDMo1kbOaApmMUr3cCLO",
    "__v" : 0
}
{
    "_id" : ObjectId("5ceeeb02d35e4ee61d602040"),
    "email" : "test919@example.com",
    "password" : "$2a$10$fSq31UO/4IEPMaXzgIHXK.zlMO8IUPsVh4Vf9MRo7zldk9dSgiwVS",
    "__v" : 0
}
{
    "_id" : ObjectId("5ceeee0bd72c8ae85dcbbc7f"),
    "email" : "test900@example.com",
    "password" : "$2a$10$l/99bU2MpElHhVFNXk1tB.k69DOAcdc/ySuek33MjtjAB0yLTqAqi",
    "__v" : 0
}
{
    "_id" : ObjectId("5ceef1cddf3064eaf8530f68"),
    "email" : "test901@example.com",
    "password" : "$2a$10$aKzilNGIm9HZx0iSXhsvbe9UCFqNt5FWjVd7T.GX7uKawAfCyG3JS",
    "__v" : 0
}
{
    "_id" : ObjectId("5cef25fc43a429ec05759849"),
    "email" : "test902@example.com",
    "password" : "$2a$10$JY0w/UuvT4Ub7T3RR9.VVOIH6V4RX9bTwoWzwEBbTiidPEiasakNq",
    "__v" : 0
}


问:
Checking for null and empty values in javascript
What is wrong with this if statement? If i pass not empty values i still don't get in to the if block

Both val1 and val2 have values:

let val1 = 'category'
let val2 = 'medical'

if ((val1 !== null || val1 !== '') && (val2 !== null || val2 !== '')) {

  console.log(val1, val2);
}```
</div>
</div>
</p>

<p><code>val1</code> is empty and <code>val2</code> has a value:</p>

<p><div class="snippet" data-lang="js" data-hide="false" data-console="true" data-babel="false">
<div class="snippet-code">
<pre class="snippet-code-js lang-js prettyprint-override"><code>let val1 = ''
let val2 = 'medical'

if ((val1 !== null || val1 !== '') && (val2 !== null || val2 !== '')) {

  console.log(val1, val2);
}</code>```

答:
The condition val1 !== null || val1 !== '' or val2 !== null && val2 !== '' will always be true

Its saying is val1 is not equal to null or empty string. Consider if its null the other part will be automatically true and vice versa.

You should use && instead of ||

var val1 = 'a'
var val2 = ''
if((val1 !== null && val1 !== '') && (val2 !== null && val2 !== '')){
    console.log(val1, val2);
}```
</div>
</div>
</p>

<p>A clean code can a be achieved using <code>every()</code> and <code>includes()</code></p>

<p><div class="snippet" data-lang="js" data-hide="false" data-console="true" data-babel="false">
<div class="snippet-code">
<pre class="snippet-code-js lang-js prettyprint-override"><code>var val1 = 'a'
var val2 = ''
if([val1, val2].every(x => ![null,''].includes(x))){
    console.log(val1, val2);
}</code>```

Body:
What is wrong with this if statement? If i pass not empty values i still don't get in to the if block

Both val1 and val2 have values:

let val1 = 'category'
let val2 = 'medical'

if ((val1 !== null || val1 !== '') && (val2 !== null || val2 !== '')) {

  console.log(val1, val2);
}

val1 is empty and val2 has a value:

let val1 = ''
let val2 = 'medical'

if ((val1 !== null || val1 !== '') && (val2 !== null || val2 !== '')) {

  console.log(val1, val2);
}

Body:
The condition val1 !== null || val1 !== '' or val2 !== null && val2 !== '' will always be true

Its saying is val1 is not equal to null or empty string. Consider if its null the other part will be automatically true and vice versa.

You should use && instead of ||

var val1 = 'a'
var val2 = ''
if((val1 !== null && val1 !== '') && (val2 !== null && val2 !== '')){
    console.log(val1, val2);
}

A clean code can a be achieved using every() and includes()

var val1 = 'a'
var val2 = ''
if([val1, val2].every(x => ![null,''].includes(x))){
    console.log(val1, val2);
}


问:
google smtp is not working on deployment but it works on local server
google smtp works fine on local server but after deployment it gives me that error

exception: "Swift_TransportException" file:
  "/home/sellective/public_html/sellective/vendor/swiftmailer/swiftmailer/lib/classes/Swift/Transport/StreamBuffer.php"
  line: 269 message: "Connection could not be established with host
  smtp.googlemail.com"

my credentials are right .
I've tried another account , another port and another encryption method also not working.

答:
goDaddy prevent smtp service from other hosts. only its own service works so I used
"relay-hosting.secureserver.com" as mail host and  mail port "25" with no encryption . goDaddy encrypt it for you . </p>


问:
How to align the origin in a 3-d figure?
I want to set origins in x and y axis same in a 3-d figure using matplotlib. However, I cannot do this. My code is:

```<code>axs.set_xlim([-60, 60])
axs.set_ylim([-60, 60])
</code>```

I also try:

```<code>plt.xlim([-60, 60])
plt.xlim([-60, 60])
</code>```

both of them are not work.

答: I found the problem not exist in matlab, so if you meet the problem, I suggest you use matlab to realize your aim</p>

Body:
I want to set origins in x and y axis same in a 3-d figure using matplotlib. However, I cannot do this. My code is:

axs.set_xlim([-60, 60])
axs.set_ylim([-60, 60])

I also try:

plt.xlim([-60, 60])
plt.xlim([-60, 60])

both of them are not work.


问:
How to get the last two registration on xml with jinja2
I receive a webservice in XML, I change it to dict with xmltodict. Then in the submit.html I use jinja2 to read the dict and get the values. The problem is I get a lot of entries with:

```<code><institution code="A15">
      <transition type="credit" code="N" href="https://.../739"></transition>
      <transition type="credit" code="N" href="https://.../741"></transition>
      <transition type="credit" code="N" href="https://.../759"></transition >
</code>```

I want to read the last 2 entries and only the "important_id" part. Like:
741
759

答:
You can use loop.revindex in Jinja2

```<code>{% for key, value in your_dict.iteritems() %}
  {% if loop.revindex <= 2 %}
    {# use key and value here #}
  {% endif %}
{% endfor %}
</code>```

This will only use the last two key-value pairs in your dict.

Body:
I receive a webservice in XML, I change it to dict with xmltodict. Then in the submit.html I use jinja2 to read the dict and get the values. The problem is I get a lot of entries with:

<institution code="A15">
      <transition type="credit" code="N" href="https://.../739"></transition>
      <transition type="credit" code="N" href="https://.../741"></transition>
      <transition type="credit" code="N" href="https://.../759"></transition >

I want to read the last 2 entries and only the "important_id" part. Like:
741
759

Body:
You can use loop.revindex in Jinja2

{% for key, value in your_dict.iteritems() %}
  {% if loop.revindex <= 2 %}
    {# use key and value here #}
  {% endif %}
{% endfor %}

This will only use the last two key-value pairs in your dict.


问:
Copy multiple files from one folder to another
I am trying to copy multiple files from one folder to another using the following code

```<code>val pathCorpus = Files.copy(
  Paths.get("src/main/resources/corpusDirectory/corpus.mallet"),
  Paths.get("src/main/resources/corpus.mallet"),
  StandardCopyOption.REPLACE_EXISTING
)

val pathInferencer = Files.copy(
  Paths.get("src/main/resources/corpusDirectory/inferencer"),
  Paths.get("src/main/resources/inferencer"),
  StandardCopyOption.REPLACE_EXISTING
)
</code>```

As I need to copy two files, I am using Files.copy twice.
The code is working but I believe there should be better way to write the code, like copying files recursively.

答:
You can use FileUtils.copyDirectory()

```<code>val source = "C:/your/source";
val srcDir = new File(source);

val destination = "C:/your/destination";
val destDir = new File(destination);

    FileUtils.copyDirectory(srcDir, destDir);
</code>```

Thanks,

Body:
I am trying to copy multiple files from one folder to another using the following code

val pathCorpus = Files.copy(
  Paths.get("src/main/resources/corpusDirectory/corpus.mallet"),
  Paths.get("src/main/resources/corpus.mallet"),
  StandardCopyOption.REPLACE_EXISTING
)

val pathInferencer = Files.copy(
  Paths.get("src/main/resources/corpusDirectory/inferencer"),
  Paths.get("src/main/resources/inferencer"),
  StandardCopyOption.REPLACE_EXISTING
)

As I need to copy two files, I am using Files.copy twice.
The code is working but I believe there should be better way to write the code, like copying files recursively.

Body:
You can use FileUtils.copyDirectory()

val source = "C:/your/source";
val srcDir = new File(source);

val destination = "C:/your/destination";
val destDir = new File(destination);

    FileUtils.copyDirectory(srcDir, destDir);

Thanks,