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));
}
