Jrbaggio's Blog

Como fazer um GROUP by apartir de uma Lista, usando Count e SUM juntos na mesma query Linq.

var SomatorioGroupBY =
ObjListapreenchida
.Select(x =>
new
{
Segmento = x.NM_SEGM,
ValorCarteira = x.VR_SAL_LIQUI,
ValorProducao = x.VR_PRO_VENDA,
}
)
.GroupBy(s => new { s.Segmento })
.Select(g =>
new
{
Segmento = g.Key.Segmento,
ValorCarteira = g.Sum(x => Math.Round(Convert.ToDecimal(x.ValorCarteira), 2)),
ValorProducao = g.Sum(x => Math.Round(Convert.ToDecimal(x.ValorProduto), 2)),
QtdEmpresas = g.Count()
}
);

Stack é um dos melhores lugares também pra obter exemplos práticos: https://stackoverflow.com/questions/21955475/linq-group-sum-multiple-columns

https://delim.co/

e

https://www.buildmystring.com/

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%Easy_SegmentoMercado%'
AND ROUTINE_TYPE='PROCEDURE' and not ROUTINE_NAME like 'SPG%'
order by ROUTINE_NAME

 

	  Declare @d datetime
select @d = '20190429' --texto puro
--select @d = getdate()

select @d as OriginalDate,
convert(varchar,@d,100) as ConvertedDate,
100 as FormatValue,
'mon dd yyyy hh:miAM (or PM)' as OutputFormat
union all
select @d,convert(varchar,@d,101),101,'mm/dd/yy'
union all
select @d,convert(varchar,@d,102),102,'yy.mm.dd'
union all
select @d,convert(varchar,@d,103),103,'dd/mm/yy'
union all
select @d,convert(varchar,@d,104),104,'dd.mm.yy'
union all
select @d,convert(varchar,@d,105),105,'dd-mm-yy'
union all
select @d,convert(varchar,@d,106),106,'dd mon yy'
union all
select @d,convert(varchar,@d,107),107,'Mon dd, yy'
union all
select @d,convert(varchar,@d,108),108,'hh:mm:ss'
union all
select @d,convert(varchar,@d,109),109,'mon dd yyyy hh:mi:ss:mmmAM (or PM)'
union all
select @d,convert(varchar,@d,110),110,'mm-dd-yy'
union all
select @d,convert(varchar,@d,111),111,'yy/mm/dd'
union all
select @d,convert(varchar,@d,12),12,'yymmdd'
union all
select @d,convert(varchar,@d,112),112,'yyyymmdd'
union all
select @d,convert(varchar,@d,113),113,'dd mon yyyy hh:mm:ss:mmm(24h)'
union all
select @d,convert(varchar,@d,114),114,'hh:mi:ss:mmm(24h)'
union all
select @d,convert(varchar,@d,120),120,'yyyy-mm-dd hh:mi:ss(24h)'
union all
select @d,convert(varchar,@d,121),121,'yyyy-mm-dd hh:mi:ss.mmm(24h)'
union all
select @d,convert(varchar,@d,126),126,'yyyy-mm-dd Thh:mm:ss:mmm(no spaces)'

Após vários dias resolvendo problemas de DLL de terceiros e com a obrigacao de serem Strongly para seu devido registro.

Achei um super programa que faz todo o trabalho pesado, e agiliza o desenvolvimento:

https://brutaldev.com/post/NET-Assembly-Strong-Name-Signer

Vai fazer o Signed da sua DLL.

Private Sub MENURpt_Click()
Dim sSql As String
Dim cTitulo As String
Dim DATA As String

DATA = InputBox("ENTRAR COM DATA: ", "Report EXCEL")

If DATA <> "" Then
    sSql = "exec sp_PROCEDURE_TAL'" & DATA & "'"
    cTitulo = "TÍTULO GERANDO NO VB6 EXCEL USANDO Excel.Application " & DATA 

    RptExcell sSql, cTitulo
End If

End Sub

 

Public Sub RptExcell(sSql As String, cTitulo As String)
'RECEBE O SQL E O TITULO

Dim aColumnas() As String
Dim nColumnas As Integer
Dim rdors As New ADODB.Recordset
Dim i As Integer

Screen.MousePointer = vbHourglass

' Ejecuto el Sql
Set rdors = gCnAdo.EjecutarRecordset(sSql)

' Leo los nombres de las columnas
nColumnas = rdors.Fields.Count
ReDim aColumnas(nColumnas)
For i = 0 To nColumnas - 1
aColumnas(i) = rdors(i).Name
Next i

' Cargo en Excel
Dim ExcelApp As New Excel.Application
Dim ExcelWorkbook As Excel.Workbook
Dim nFila As Long
Dim nHoja As Integer

ExcelApp.Workbooks.Add

nFila = 5
nHoja = 1

GoSub TituloColumnas

While Not rdors.EOF
For i = 1 To nColumnas
ExcelApp.Worksheets(nHoja).Cells(nFila, i) = rdors(i - 1)
Next i

nFila = nFila + 1
If nFila > 60000 Then
GoSub TituloHoja
nHoja = nHoja + 1

nFila = 5
GoSub TituloColumnas

End If

rdors.MoveNext
Wend

GoSub TituloHoja
ExcelApp.Visible = True

If rdors.State = adStateOpen Then rdors.Close

Set rdors = Nothing

Screen.MousePointer = vbDefault

Exit Sub

TituloHoja:
'Ajustando a largura das colunas antes de colocar o título
ExcelApp.Worksheets(nHoja).Columns.AutoFit

' Formato DO titulo
ExcelApp.Worksheets(nHoja).Cells(1, 1) = cTitulo
ExcelApp.Worksheets(nHoja).Cells(1, 1).Font.Bold = True
ExcelApp.Worksheets(nHoja).Cells(1, 1).Font.Size = 14
ExcelApp.Worksheets(nHoja).Cells(1, 1).Font.Underline = True

Return

TituloColumnas:
For i = 1 To nColumnas
' Nome
ExcelApp.Worksheets(nHoja).Cells(4, i) = aColumnas(i - 1)</pre>
<pre>' Formato
ExcelApp.Worksheets(nHoja).Cells(4, i).Font.Bold = True
ExcelApp.Worksheets(nHoja).Cells(4, i).Borders.Value = 7
ExcelApp.Worksheets(nHoja).Cells(4, i).Interior.Color = RGB(67, 214, 233)
ExcelApp.Worksheets(nHoja).Cells(4, i).HorizontalAlignment = 3
Next i

Return
End Sub