动态列头

把列头以 逗号分隔传回前端,@columnheads,循环动态生成列标题
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].sp_CarMoneyInfoByWay,
@sortorderby VARCHAR(50),
@carno VARCHAR(50),
@begdate VARCHAR(30),
@enddate VARCHAR(30),
@total INT out,
@columnheads VARCHAR(max) out
)
AS
DECLARE @sql NVARCHAR(max)
DECLARE @tmpbegdate VARCHAR(50)
DECLARE @tmpenddate VARCHAR(50)
DECLARE @kindnames NVARCHAR(MAX)
DECLARE @kindnos NVARCHAR(MAX)
DECLARE @columns NVARCHAR(MAX)
DECLARE @columnnames NVARCHAR(MAX)
BEGIN
IF(@begdate = ‘null’ OR isnull(@begdate,’’)=’’ ) SET @begdate = ‘’
IF(@enddate = ‘null’ OR isnull(@enddate,’’)=’’) SET @enddate = ‘’
SET @tmpbegdate = @begdate
SET @tmpenddate = @enddate + ‘ 23:59:59’
SELECT @kindnames=STUFF((SELECT ‘,’+kindname FROM chargekind FOR XML PATH(‘’)) ,1,1,’’ )
set @columnheads = @kindnames
PRINT @kindnames
SELECT @kindnos=STUFF((SELECT ‘,’+kindno FROM chargekind FOR XML PATH(‘’)) ,1,1,’’ )
PRINT @kindnos
SELECT @columns=STUFF((SELECT ‘,isnull(‘+kindno+’,0) as ‘+kindname FROM chargekind FOR XML PATH(‘’)),1,1,’’ )
PRINT @columns
SELECT @columnnames=STUFF((SELECT ‘,isnull(‘+kindname+’,0) as ‘+kindname FROM chargekind FOR XML PATH(‘’)),1,1,’’ )
PRINT @columnnames

IF(isnull(@sortcolumn,’’)=’’) SET @sortcolumn = ‘carno’
set @sql = ‘SELECT isnull(d.carno,’’’’) AS carno,d.detailid,d.jobno into #jobdetail
FROM JobDetailbill d INNER JOIN jobmasterbill m ON d.JobNo = m.JobNo where 1=1 ‘
IF(@begdate != ‘’)
BEGIN
set @sql = @sql + ‘ and carfinishdate >= ‘’’+@tmpbegdate+’’’’
END
IF(@enddate != ‘’)
BEGIN
set @sql = @sql + ‘ and carfinishdate < = ‘’’+@tmpenddate+’’’’
END
IF(@carno != ‘’)
BEGIN
set @sql = @sql + ‘ and d.carno like ‘’%’+@carno+’%’’’
END
—车辆司机工资
SET @sql = @sql +’ SELECT isnull(m.wayno,’’’’) as wayno,isnull(max(w.wayname),’’’’) as wayname,isnull(max(d.carno),’’’’) AS carno,count(1) as num,isnull(max(m.finishdate),’’’’) as finishdate,isnull(max(s.shipcompname),’’’’) as shipcompname,
ISNULL(SUM(d.LoadWeight),0.0) as loadweight,ISNULL(SUM(d.UnLoadWeight),0.0) as unloadweight,ISNULL(SUM(d.LoadWeight),0.0)-ISNULL(SUM(d.UnLoadWeight),0.0) as kuitonnage,
isnull(sum(PlatDebit-cutmoney-othermoney),0) as listmoney,isnull(SUM(charge.ChargeMoney),0) as chargemoney,isnull(sum(PlatDebit-cutmoney-othermoney),0)-isnull(sum(charge.ChargeMoney),0) as profit into #stattotal
FROM JobDetailbill d INNER JOIN jobmasterbill m ON d.JobNo = m.JobNo
left join driverinfo dr on d.driverno1 = dr.driverno
left join ShipCompany s on m.ShipCompNo=s.ShipCompNo
left join WaySet w on m.WayNo=w.WayNo
left join (select sum(chargemoney) as chargemoney,carno,jobno,JobDetailId from ChargeDetail d,chargemaster m where m.chargeno=d.chargeno and liststate = 2 group by carno,jobno,JobDetailId) as charge on charge.carno=d.CarNo and charge.jobno=d.JobNo and charge.JobDetailId=d.DetailId
where 1=1 ‘
IF(@begdate != ‘’)
BEGIN
set @sql = @sql + ‘ and carfinishdate >= ‘’’+@tmpbegdate+’’’’
END
IF(@enddate != ‘’)
BEGIN
set @sql = @sql + ‘ and carfinishdate < = ‘’’+@tmpenddate+’’’’
END
IF(@carno != ‘’)
BEGIN
set @sql = @sql + ‘ and d.carno = ‘’’+@carno+’’’’
END
set @sql = @sql + ‘ GROUP BY isnull(m.wayno,’’’’) ‘

—-司机费用
SET @sql = @sql + ‘
select wayno as wayno1,’+@columns+’ into #charge from (SELECT d.wayno,isnull(chargemoney,0) as chargemoney ,kindno FROM ChargeDetail d
inner join chargemaster m on d.chargeno = m.chargeno
inner join #jobdetail j on d.jobno = j.jobno and d.jobdetailid=j.detailid and d.carno = j.carno
left join chargekind k on d.chargekindno=k.kindno
where m.liststate=2 and d.carno = ‘’’+@carno+’’’ ) t
PIVOT( sum(chargemoney) for kindno IN (‘+@kindnos+’) ) a ORDER BY wayno ‘
—分页
SET @sql = @sql + ‘;with tmpFirst as ( select a.carno,a.wayname,a.shipcompname,a.num,a.finishdate,a.listmoney,a.loadweight,a.unloadweight,a.kuitonnage, a.chargemoney,a.profit,’+@columnnames+’ from #stattotal a left join #charge b on a.wayno = b.wayno1 )’
SET @sql = @sql + ‘,tmpList AS(select ROW_NUMBER() OVER (ORDER BY ‘+@sortcolumn+’) AS ‘’RowNumber’’, from tmpfirst ‘+
‘ ‘+
‘) SELECT top ‘+convert(varchar,@limit)+’ FROM tmpList WHERE RowNumber > ‘+CONVERT(VARCHAR,@start)
—合计
SET @sql = @sql + ‘select @total = count(1) from #stattotal a left join #charge b on a.wayno = b.wayno1 ‘
PRINT @sql
EXEC sp_executesql @sql ,N’@total int out’,@total out
RETURN 0
end
@RequestMapping(“/carMoneyAll”)
public JsonResult carMoneyAll(@RequestBody StatDriverSalary statDriverSalary)
{
try {
baseService.setEasyUIPage(statDriverSalary);
Page page = payService.carMoneyInfoDetailAll(statDriverSalary);
//用footer传回动态列头
JSONObject jsonObject = new JSONObject();
jsonObject.put(“columnheads”,page.getColumnheads());
return JsonResult.successEasyUIGrid(page.getTotal(),page.getRoot(),jsonObject);
}
catch(Exception e){
e.printStackTrace();
return JsonResult.error();
}
}
$.post(“pay/carMoneyAll.do”, {
‘carno’ : ‘xxxxx’,
‘begdate’ : $(‘.begdate’).val(),
‘enddate’ : $(‘.enddate’).val()
},
function (r) {
var columns=[
{field:’carno’,title:’车牌号’,width:150,align:’center’},
{field:’wayname’,title:’路线’,width:150,align:’center’},
{field:’shipcompname’,title:’结账单位’,width:150,align:’center’},
{field:’carruntime’,title:’本趟用时’,width:150,align:’center’},
{field:’confirmdate’,title:’费用录入时间’,width:150,align:’center’,
formatter: function(value,row,index){
if(value){
var d = new Date(value);
var youWant=d.getFullYear() + ‘-‘ + (d.getMonth() + 1) + ‘-‘ + d.getDate() + ‘ ‘ + d.getHours() + ‘:’ + d.getMinutes() + ‘:’ + d.getSeconds();
return youWant ;
}
}},
{field:’reviewdateafterconfirm’,title:’费用审核时间’,width:150,align:’center’,
formatter: function(value,row,index){
if(value){
var d = new Date(value);
var youWant=d.getFullYear() + ‘-‘ + (d.getMonth() + 1) + ‘-‘ + d.getDate() + ‘ ‘ + d.getHours() + ‘:’ + d.getMinutes() + ‘:’ + d.getSeconds();
return youWant ;
}
}},
{field:’confirmusername’,title:’录入人’,width:150,align:’center’},
{field:’loadweight’,title:’装车吨数’,width:150,align:’center’},
{field:’unloadweight’,title:’卸车吨数’,width:150,align:’center’},
{field:’kuitonnage’,title:’亏吨数’,width:150,align:’center’}
]
var arrColumnheads = r.footer.columnheads.split(“,”);
$.each(arrColumnheads,function (i,item) {
var column = {field:item,title:item,width:150,align:’center’}
columns.push(column);
})
columns.push({field:’listmoney’,title:’运费’,width:150,align:’center’});
columns.push({field:’chargemoney’,title:’费用合计’,width:150,align:’center’});
columns.push({field:’profit’,title:’利润’,width:150,align:’center’});
$(‘#CarMoneyInfoAllGrid’).datagrid({
columns:[columns]
});
});