public JsonResult GetAccidentInfo(string fleet, string date, string carID, string code) { var cm = CacheManager <Personnel_Info> .GetInstance()[PubGet.GetUserKey + code]; var ownedCompany = cm.Organization; var fleetAll = PartnerHomePageController.getSqlInValue(cm.MotorcadeName, code); //ownedCompany = "第一服务中心"; var date1 = date.TryToDate(); var date2 = date1.AddDays(1).ToString("yyyy-MM-dd"); //var date1 = "2020-04-24"; //var date2 = "2020-06-14"; List <Accident_cabInfo> visionetList = new List <Accident_cabInfo>(); using (SqlSugarClient _dbMsSql = SugarDao_MsSql.GetInstance2()) { if (fleet == "0") { fleet = fleetAll; } else { fleet = "'" + fleet + "'"; } visionetList = getVisionetList(_dbMsSql, date, fleet, ownedCompany, date1, date2, cm.DepartmenManager); if (carID != null && carID != "") { visionetList = visionetList.Where(x => x.carNo.Contains(carID)).ToList(); } } return(Json(visionetList, JsonRequestBehavior.AllowGet)); }
public JsonResult GetDriverScore(string fleet, string code) { var cm = CacheManager <Personnel_Info> .GetInstance()[PubGet.GetUserKey + code]; var ownedCompany = cm.Organization; var fleetAll = PartnerHomePageController.getSqlInValue(cm.MotorcadeName, code); //var ownedCompany = "市北大众"; //fleet = "一车队"; List <int> scoreList = new List <int>(); List <DriverScore> driverScoreList = new List <DriverScore>(); using (SqlSugarClient _dbMsSql = SugarDao_MsSql.GetInstance2()) { if (fleet == "0") { fleet = fleetAll; } else { fleet = "'" + fleet + "'"; } if (cm.DepartmenManager == "12") { driverScoreList = _dbMsSql.SqlQuery <DriverScore>(@"select DriverId,Name,convert(int,score) as Score from [DZ_DW].[dbo].[Visionet_DriverInfo_View] vdv left join tb_query_score qs on qs.id_no=vdv.IdCard where vdv.Organization in (" + fleetAll + @") and vdv.status='1' and qs.score is not null order by DriverId asc", new { OwnedCompany = ownedCompany }).ToList(); } else { driverScoreList = _dbMsSql.SqlQuery <DriverScore>(@"select DriverId,Name,convert(int,score) as Score from [DZ_DW].[dbo].[Visionet_DriverInfo_View] vdv left join tb_query_score qs on qs.id_no=vdv.IdCard where vdv.Organization=@OwnedCompany and vdv.MotorcadeName in (" + fleet + @") and vdv.status='1' and qs.score is not null order by DriverId asc", new { OwnedCompany = ownedCompany }).ToList(); } var result1 = driverScoreList.Where(x => x.Score == 12).ToList().Count; var result2 = driverScoreList.Where(x => x.Score > 8 && x.Score < 11).ToList().Count; var result3 = driverScoreList.Where(x => x.Score == 0).ToList().Count; scoreList.Add(result1); scoreList.Add(result2); scoreList.Add(result3); } return(Json(scoreList, JsonRequestBehavior.AllowGet)); }
public JsonResult GetPointsListBySearch(string fleet, string name, string code) { var cm = CacheManager <Personnel_Info> .GetInstance()[PubGet.GetUserKey + code]; var ownedCompany = cm.Organization; var ownedFleet = cm.MotorcadeName; var fleetAll = PartnerHomePageController.getSqlInValue(cm.MotorcadeName, code); List <DriverScore> driverScoreList = new List <DriverScore>(); using (SqlSugarClient _dbMsSql = SugarDao_MsSql.GetInstance2()) { if (fleet == "0") { fleet = fleetAll; } else { fleet = "'" + fleet + "'"; } if (cm.DepartmenManager == "12") { driverScoreList = _dbMsSql.SqlQuery <DriverScore>(@"select DriverId,Name,convert(int,score) as Score from [DZ_DW].[dbo].[Visionet_DriverInfo_View] vdv left join tb_query_score qs on qs.id_no=vdv.IdCard where vdv.Organization in (" + fleetAll + @") and vdv.status='1' and qs.score is not null and qs.score != 0 order by DriverId asc").ToList(); } else { driverScoreList = _dbMsSql.SqlQuery <DriverScore>(@"select DriverId,Name,convert(int,score) as Score from [DZ_DW].[dbo].[Visionet_DriverInfo_View] vdv left join tb_query_score qs on qs.id_no=vdv.IdCard where vdv.Organization=@OwnedCompany and vdv.MotorcadeName in (" + fleet + @") and vdv.status='1' and qs.score is not null and qs.score != 0 order by DriverId asc", new { OwnedCompany = ownedCompany }).ToList(); } if (name != "" && name != null) { driverScoreList = driverScoreList.Where(x => x.Name.Contains(name)).ToList(); } } return(Json(driverScoreList, JsonRequestBehavior.AllowGet)); }
public JsonResult GetOperationInfo(string fleet, string date, string carID, string code) { var cm = CacheManager <Personnel_Info> .GetInstance()[PubGet.GetUserKey + code]; var ownedCompany = cm.Organization; var fleetAll = PartnerHomePageController.getSqlInValue(cm.MotorcadeName, code); //ownedCompany = "第一服务中心"; List <Visionet_CabInfo> carList = new List <Visionet_CabInfo>(); using (SqlSugarClient _dbMsSql = SugarDao_MsSql.GetInstance2()) { if (fleet == "0") { fleet = fleetAll; } else { fleet = "'" + fleet + "'"; } if (cm.DepartmenManager == "12") { carList = _dbMsSql.SqlQuery <Visionet_CabInfo>(@"select CabLicense,Motorcade from [DZ_DW].[dbo].Visionet_CabInfo_View where Organization in (" + fleet + @") and OperationStatus=0 order by Motorcade asc").ToList(); } else { carList = _dbMsSql.SqlQuery <Visionet_CabInfo>(@"select CabLicense,Motorcade from [DZ_DW].[dbo].Visionet_CabInfo_View where Organization=@OwnedCompany and Motorcade in (" + fleet + @") and OperationStatus=0 order by Motorcade asc", new { OwnedCompany = ownedCompany, OwnedFleet = fleet }).ToList(); } if (carID != null && carID != "") { carList = _dbMsSql.SqlQuery <Visionet_CabInfo>(@"select CabLicense,Motorcade from [DZ_DW].[dbo].Visionet_CabInfo_View where Organization=@OwnedCompany and Motorcade in (" + fleet + @") and OperationStatus=0 and CabLicense like '%" + carID + "%' order by Motorcade asc", new { OwnedCompany = ownedCompany, OwnedFleet = fleet }).ToList(); } } return(Json(carList, JsonRequestBehavior.AllowGet)); }
public JsonResult GetVehicleMaintenanceInfo(string fleet, string type, string status, string idCard, string code) { var cm = CacheManager <Personnel_Info> .GetInstance()[PubGet.GetUserKey + code]; var orgName = cm.Organization; var fleetAll = PartnerHomePageController.getSqlInValue(cm.MotorcadeName, code); List <VehicleMaintenanceInfo> resultInfo = new List <VehicleMaintenanceInfo>(); using (SqlSugarClient _dbMsSql = SugarDao_MsSql.GetInstance2()) { if (fleet == "0") { fleet = fleetAll; } else { fleet = "'" + fleet + "'"; } if (cm.DepartmenManager == "12") { resultInfo = _dbMsSql.SqlQuery <VehicleMaintenanceInfo>(@"SELECT MotorcadeName,Name,CabLicense,MaintenanceType,Date,Time,Address,Yanche,vm.Status,MobilePhone,'' as isMaintenance FROM VehicleMaintenanceInfo vm left join [DZ_DW].[dbo].[Visionet_DriverInfo_View] vdv on vm.carNo=vdv.CabVMLicense where vdv.Organization in (" + fleet + @") and vm.Status='0' order by MotorcadeName asc,Date asc,Time asc,MaintenanceType asc").ToList(); } else { resultInfo = _dbMsSql.SqlQuery <VehicleMaintenanceInfo>(@"SELECT MotorcadeName,Name,CabLicense,MaintenanceType,Date,Time,Address,Yanche,vm.Status,MobilePhone FROM VehicleMaintenanceInfo vm left join [DZ_DW].[dbo].[Visionet_DriverInfo_View] vdv on vm.carNo=vdv.CabVMLicense where vdv.Organization=@OrgName and vdv.MotorcadeName in (" + fleet + @") and vm.Status='0' order by MotorcadeName asc,Date asc,Time asc,MaintenanceType asc", new { OrgName = orgName }).ToList(); } var svbillList = _dbMsSql.SqlQuery <SHDZWX_VIEW_SVBILL>(@"select SV_FTYPE,VEHICLE_NO,CONVERT(date, BILL_DATE, 23) as BILL_DATE from SHDZWX_VIEW_SVBILL where SV_FTYPE='1'").ToList(); foreach (var item in resultInfo) { var dateMin = item.Date.TryToDate().AddDays(-7); var dateMax = item.Date.TryToDate().AddDays(+7); var isAny = svbillList.Any(x => x.VEHICLE_NO == item.CabLicense && x.BILL_DATE >= dateMin && x.BILL_DATE <= dateMax); if (isAny == true) { item.IsMaintenance = "1"; } else { item.IsMaintenance = "0"; } } if (type != "0" && type != "") { if (type == "4")//验车 { resultInfo = resultInfo.Where(x => x.Yanche == "1").ToList(); } else { resultInfo = resultInfo.Where(x => x.MaintenanceType.Contains(type)).ToList(); } } if (status != "") { resultInfo = resultInfo.Where(x => x.Status.Contains(status)).ToList(); } if (idCard != "0" && idCard != "" && idCard != null) { resultInfo = resultInfo.Where(x => x.CabLicense.Contains(idCard)).ToList(); } } return(Json(resultInfo, JsonRequestBehavior.AllowGet)); }
public JsonResult GetTaxiSummaryInfo(string fleet, string dateSearch, string code) { var cm = CacheManager <Personnel_Info> .GetInstance()[PubGet.GetUserKey + code]; var fleetAll = PartnerHomePageController.getSqlInValue(cm.MotorcadeName, code); if (cm.DepartmenManager == "12") { fleetAll = PartnerHomePageController.getSqlInValue(cm.MotorcadeNameRemark, code); } var orgName = cm.Organization; var dataList = ""; var dataList2 = ""; JObject jObject = new JObject(); using (SqlSugarClient _dbMsSql = SugarDao_MsSql.GetInstance2()) { var date1 = dateSearch.TryToDate(); var date2 = date1.AddDays(-1).ToString("yyyy-MM-dd"); if (fleet == "0") { fleet = fleetAll; } else { if (cm.DepartmenManager == "12") { fleet = _dbMsSql.SqlQuery <string>(@"select Remark from DZ_Organization where status=0 and OrganizationName=@OrganizationName", new { OrganizationName = fleet }).ToList().FirstOrDefault();; } fleet = "'" + fleet + "'"; } if (cm.DepartmenManager == "12") { var count = _dbMsSql.SqlQuery <int>(@"select count(上线司机数) from t_taxi_summary where 日期='" + dateSearch + "' and 公司 in (" + fleet + ")").FirstOrDefault(); if (count == 0) { //没有数据时,避免除0报错 count = 1; } //最新数据 dataList = _dbMsSql.SqlQueryJson(@"select isnull(Sum(convert(decimal(18,2),总营收)),0) as 总营收, isnull(Sum(convert(decimal(18,2),总差次)),0) as 总差次, isnull(Sum(convert(decimal(18,2),营运车辆总数)),0) as 营运车辆总数, isnull(Sum(convert(decimal(18,2),总线上营收)),0) as 总线上营收, isnull(Sum(convert(decimal(18,2),总线上差次)),0) as 总线上差次, convert(decimal(18,2), isnull(Sum(convert(decimal(18,2),车均营收)),0)/" + count + @") as 车均营收, convert(decimal(18,2), isnull(Sum(convert(decimal(18,2),车均差次)),0)/" + count + @") as 车均差次, convert(decimal(18,2), isnull(Sum(convert(decimal(18,2),车均线上营收)),0)/" + count + @") as 车均线上营收, convert(decimal(18,2), isnull(Sum(convert(decimal(18,2),车均线上差次)),0)/" + count + @") as 车均线上差次, convert(decimal(18,2), isnull(Sum(convert(decimal(18,2),车均行驶里程)),0)/" + count + @") as 车均行驶里程, convert(decimal(18,2), isnull(Sum(convert(decimal(18,2),车均营运里程)),0)/" + count + @") as 车均营运里程, convert(decimal(18,2), isnull(Sum(convert(decimal(18,2),车均空驶里程)),0)/" + count + @") as 车均空驶里程 from t_taxi_summary where 日期='" + dateSearch + "' and 公司 in (" + fleet + ") "); //前一天数据 dataList2 = _dbMsSql.SqlQueryJson(@"select isnull(Sum(convert(decimal(18,2),总营收)),0) as 总营收, isnull(Sum(convert(decimal(18,2),总差次)),0) as 总差次, isnull(Sum(convert(decimal(18,2),营运车辆总数)),0) as 营运车辆总数, isnull(Sum(convert(decimal(18,2),总线上营收)),0) as 总线上营收, isnull(Sum(convert(decimal(18,2),总线上差次)),0) as 总线上差次, convert(decimal(18,2), isnull(Sum(convert(decimal(18,2),车均营收)),0)/" + count + @") as 车均营收, convert(decimal(18,2), isnull(Sum(convert(decimal(18,2),车均差次)),0)/" + count + @") as 车均差次, convert(decimal(18,2), isnull(Sum(convert(decimal(18,2),车均线上营收)),0)/" + count + @") as 车均线上营收, convert(decimal(18,2), isnull(Sum(convert(decimal(18,2),车均线上差次)),0)/" + count + @") as 车均线上差次, convert(decimal(18,2), isnull(Sum(convert(decimal(18,2),车均行驶里程)),0)/" + count + @") as 车均行驶里程, convert(decimal(18,2), isnull(Sum(convert(decimal(18,2),车均营运里程)),0)/" + count + @") as 车均营运里程, convert(decimal(18,2), isnull(Sum(convert(decimal(18,2),车均空驶里程)),0)/" + count + @") as 车均空驶里程 from t_taxi_summary where 日期='" + date2 + "' and 公司 in (" + fleet + ") "); } else { var count = _dbMsSql.SqlQuery <int>(@"select count(上线司机数) from t_taxi_summary where 日期='" + dateSearch + "' and 车队 in (" + fleet + ") and 公司='" + orgName + "'").FirstOrDefault(); if (count == 0) { //没有数据时,避免除0报错 count = 1; } //最新数据 dataList = _dbMsSql.SqlQueryJson(@"select isnull(Sum(convert(decimal(18,2),总营收)),0) as 总营收, isnull(Sum(convert(decimal(18,2),总差次)),0) as 总差次, isnull(Sum(convert(decimal(18,2),营运车辆总数)),0) as 营运车辆总数, isnull(Sum(convert(decimal(18,2),总线上营收)),0) as 总线上营收, isnull(Sum(convert(decimal(18,2),总线上差次)),0) as 总线上差次, convert(decimal(18,2), isnull(Sum(convert(decimal(18,2),车均营收)),0)/" + count + @") as 车均营收, convert(decimal(18,2), isnull(Sum(convert(decimal(18,2),车均差次)),0)/" + count + @") as 车均差次, convert(decimal(18,2), isnull(Sum(convert(decimal(18,2),车均线上营收)),0)/" + count + @") as 车均线上营收, convert(decimal(18,2), isnull(Sum(convert(decimal(18,2),车均线上差次)),0)/" + count + @") as 车均线上差次, convert(decimal(18,2), isnull(Sum(convert(decimal(18,2),车均行驶里程)),0)/" + count + @") as 车均行驶里程, convert(decimal(18,2), isnull(Sum(convert(decimal(18,2),车均营运里程)),0)/" + count + @") as 车均营运里程, convert(decimal(18,2), isnull(Sum(convert(decimal(18,2),车均空驶里程)),0)/" + count + @") as 车均空驶里程 from t_taxi_summary where 日期='" + dateSearch + "' and 车队 in (" + fleet + ") and 公司='" + orgName + "'"); //前一天数据 dataList2 = _dbMsSql.SqlQueryJson(@"select isnull(Sum(convert(decimal(18,2),总营收)),0) as 总营收, isnull(Sum(convert(decimal(18,2),总差次)),0) as 总差次, isnull(Sum(convert(decimal(18,2),营运车辆总数)),0) as 营运车辆总数, isnull(Sum(convert(decimal(18,2),总线上营收)),0) as 总线上营收, isnull(Sum(convert(decimal(18,2),总线上差次)),0) as 总线上差次, convert(decimal(18,2), isnull(Sum(convert(decimal(18,2),车均营收)),0)/" + count + @") as 车均营收, convert(decimal(18,2), isnull(Sum(convert(decimal(18,2),车均差次)),0)/" + count + @") as 车均差次, convert(decimal(18,2), isnull(Sum(convert(decimal(18,2),车均线上营收)),0)/" + count + @") as 车均线上营收, convert(decimal(18,2), isnull(Sum(convert(decimal(18,2),车均线上差次)),0)/" + count + @") as 车均线上差次, convert(decimal(18,2), isnull(Sum(convert(decimal(18,2),车均行驶里程)),0)/" + count + @") as 车均行驶里程, convert(decimal(18,2), isnull(Sum(convert(decimal(18,2),车均营运里程)),0)/" + count + @") as 车均营运里程, convert(decimal(18,2), isnull(Sum(convert(decimal(18,2),车均空驶里程)),0)/" + count + @") as 车均空驶里程 from t_taxi_summary where 日期='" + date2 + "' and 车队 in (" + fleet + ") and 公司='" + orgName + "'"); } if (dataList.Count() > 2) { Regex rgx = new Regex(@"(?i)(?<=\[)(.*)(?=\])"); //中括号[] string tmp1 = rgx.Match(dataList).Value; //中括号[] string tmp2 = rgx.Match(dataList2).Value; //中括号[] JObject jo1 = (JObject)JsonConvert.DeserializeObject(tmp1); JObject jo2 = (JObject)JsonConvert.DeserializeObject(tmp2); jObject = jo1; //计算上涨或者下跌率 string rate = ""; List <string> strValue = new List <string>() { "总营收", "总差次", "营运车辆总数", "总线上营收", "总线上差次", "车均营收", "车均差次", "车均线上营收", "车均线上差次", "车均行驶里程", "车均营运里程", "车均空驶里程" }; foreach (var item in strValue) { var value1_1 = jo1[item].TryToDecimal(); var value2_1 = jo2[item].TryToDecimal(); if (value1_1 > value2_1 && value2_1 != 0) { rate = "↑" + (decimal.Round((value1_1 - value2_1) / value2_1 * 100, 2)) + "%"; } else if (value1_1 < value2_1 && value2_1 != 0) { rate = "↓" + (decimal.Round((value2_1 - value1_1) / value2_1 * 100, 2)) + "%"; } else { rate = "0%"; } jObject.Add(item + "率", rate); } } } return(Json(jObject.ObjToString(), JsonRequestBehavior.AllowGet)); }
public JsonResult getAnswerStatus(string fleet, string code) { var cm = CacheManager <Personnel_Info> .GetInstance()[PubGet.GetUserKey + code]; List <int> status = new List <int>(); var ownedFleet = cm.OldMotorcadeName; //公司 var ownedCompany = fleet; //车队 var fleetAll = PartnerHomePageController.getSqlInValue(cm.MotorcadeName, code); //var ownedCompany = "第一服务中心"; //var ownedFleet = "仁强"; var date = DateTime.Now.ToString("yyyy-MM"); List <AnswerStatus> answerStatusList = new List <AnswerStatus>(); using (SqlSugarClient _dbMsSql = SugarDao_MsSql.GetInstance()) { if (fleet == "0") { fleet = fleetAll; } else { fleet = "'" + fleet + "'"; } if (cm.DepartmenManager == "12") { var ownedFleetAll = getOwnedFleetAll(_dbMsSql, cm.MotorcadeName); answerStatusList = _dbMsSql.SqlQuery <AnswerStatus>(@"select * from(select p.Name, p.IDNumber, convert(varchar(7), ei.CreatedDate, 120) as sDate, case when eai.TotalScore >= 60 then '合格' --已阅答题 when eai.TotalScore < 60 then '不合格' --已阅未答题 else '未答题' --未阅未答题 end as Result from[dbo].[Business_Personnel_Information] p left join Business_ExercisesAnswer_Information eai on p.Vguid = eai.BusinessPersonnelVguid and convert(varchar, eai.CreatedDate, 23) >= @Date left join Business_Exercises_Infomation ei on eai.BusinessExercisesVguid = ei.Vguid and convert(varchar, ei.CreatedDate, 23) >= @Date where p.ApprovalStatus = '2' and OwnedFleet in (" + ownedFleetAll + @") )a PIVOT(MAX(Result) FOR sDate IN([Status])) AS T ", new { Date = date }); } else { answerStatusList = _dbMsSql.SqlQuery <AnswerStatus>(@"select * from(select p.Name, p.IDNumber, convert(varchar(7), ei.CreatedDate, 120) as sDate, case when eai.TotalScore >= 60 then '合格' --已阅答题 when eai.TotalScore < 60 then '不合格' --已阅未答题 else '未答题' --未阅未答题 end as Result from[dbo].[Business_Personnel_Information] p left join Business_ExercisesAnswer_Information eai on p.Vguid = eai.BusinessPersonnelVguid and convert(varchar, eai.CreatedDate, 23) >= @Date left join Business_Exercises_Infomation ei on eai.BusinessExercisesVguid = ei.Vguid and convert(varchar, ei.CreatedDate, 23) >= @Date where p.ApprovalStatus = '2' and p.OwnedCompany in (" + fleet + @") and OwnedFleet = @OwnedFleet )a PIVOT(MAX(Result) FOR sDate IN([Status])) AS T ", new { Date = date, OwnedFleet = ownedFleet }); } var result1 = answerStatusList.Where(x => x.Status == "合格").ToList().Count; var result2 = answerStatusList.Where(x => x.Status == "不合格").ToList().Count; var result3 = answerStatusList.Where(x => x.Status != "合格" && x.Status != "不合格").ToList().Count; status.Add(result1); status.Add(result2); status.Add(result3); } return(Json(status, JsonRequestBehavior.AllowGet)); }
public JsonResult GetOwnedCompanyList(string fleet, string name, string date, string code) { //userInfo.UserId = "13611794751"; var cm = CacheManager <Personnel_Info> .GetInstance()[PubGet.GetUserKey + code]; var ownedFleet = cm.OldMotorcadeName; var ownedCompany = cm.OldOrganization; var fleetAll = PartnerHomePageController.getSqlInValue(cm.MotorcadeName, code); if (date == "") { date = DateTime.Now.ToString("yyyy-MM"); } List <AnswerStatus> answerStatusList = new List <AnswerStatus>(); using (SqlSugarClient _dbMsSql = SugarDao_MsSql.GetInstance()) { if (fleet == "0") { fleet = fleetAll; } else { fleet = "'" + fleet + "'"; } if (cm.DepartmenManager == "12") { var ownedFleetAll = DriverManagementController.getOwnedFleetAll(_dbMsSql, cm.MotorcadeName); answerStatusList = _dbMsSql.SqlQuery <AnswerStatus>(@"select * from(select p.Name, p.IDNumber, convert(varchar(7), ei.CreatedDate, 120) as sDate, case when eai.TotalScore >= 60 then '合格' --已阅答题 when eai.TotalScore < 60 then '不合格' --已阅未答题 else '未答题' --未阅未答题 end as Result from[dbo].[Business_Personnel_Information] p left join Business_ExercisesAnswer_Information eai on p.Vguid = eai.BusinessPersonnelVguid and convert(varchar, eai.CreatedDate, 23) >= @Date left join Business_Exercises_Infomation ei on eai.BusinessExercisesVguid = ei.Vguid and convert(varchar, ei.CreatedDate, 23) >= @Date where p.ApprovalStatus = '2' and OwnedFleet in (" + ownedFleetAll + @") )a PIVOT(MAX(Result) FOR sDate IN([Status])) AS T ", new { Date = date, OwnedFleet = ownedFleet }); } else { answerStatusList = _dbMsSql.SqlQuery <AnswerStatus>(@"select * from(select p.Name, p.IDNumber, convert(varchar(7), ei.CreatedDate, 120) as sDate, case when eai.TotalScore >= 60 then '合格' --已阅答题 when eai.TotalScore < 60 then '不合格' --已阅未答题 else '未答题' --未阅未答题 end as Result from[dbo].[Business_Personnel_Information] p left join Business_ExercisesAnswer_Information eai on p.Vguid = eai.BusinessPersonnelVguid and convert(varchar, eai.CreatedDate, 23) >= @Date left join Business_Exercises_Infomation ei on eai.BusinessExercisesVguid = ei.Vguid and convert(varchar, ei.CreatedDate, 23) >= @Date where p.ApprovalStatus = '2' and p.OwnedCompany in (" + fleet + @") and OwnedFleet = @OwnedFleet )a PIVOT(MAX(Result) FOR sDate IN([Status])) AS T ", new { Date = date, OwnedFleet = ownedFleet }); } if (name != "" && name != null) { answerStatusList = answerStatusList.Where(x => x.Name.Contains(name)).ToList(); } } return(Json(answerStatusList, JsonRequestBehavior.AllowGet)); }