public int Insert(P_Admin p_Admin)
{
using (var conn = ConnectionFactory.GetDBConn(ConnectionFactory.DBConnNames.GisPlateform))
{
return(conn.Execute(DapperExtentions.MakeInsertSql(p_Admin), p_Admin));
}
}
public MessageEntity Get(DateTime startTime, DateTime endTime, int?deptId, int?iAdminID, string sort, string ordering, int num, int page)
{
var sqlString = @"SELECT * FROM ( SELECT p.DeptId,p.cDepName,p.PersonName,d.*,CAST(CONVERT(DECIMAL(10,2),DATEDIFF(MINUTE,d.work_start,d.work_over))/60 AS DECIMAL(10,2)) AS times
FROM PipeInspectionBase_Gis_OutSide.dbo.L_Person AS p INNER JOIN
(SELECT * FROM(SELECT ss.work_start, ss.PersonId, de.Date FROM(SELECT MIN(UpTime) AS work_start, PersonId
FROM dbo.L_AttendanceManage WHERE PersonStatus = '上班正常' GROUP BY PersonId, Date)AS ss
INNER JOIN L_AttendanceManage AS de ON ss.work_start = de.UpTime AND ss.PersonId = de.PersonId)AS sb
LEFT JOIN(
SELECT ee.work_over, ee.PersonId AS PersonId2, de2.Date AS Date2
FROM(SELECT MAX(UpTime) AS work_over, PersonId FROM dbo.L_AttendanceManage WHERE PersonStatus = '用户下班' GROUP BY PersonId, Date)AS ee
INNER JOIN L_AttendanceManage AS de2 ON ee.work_over = de2.UpTime AND ee.PersonId = de2.PersonId
)AS xb ON xb.Date2 = sb.Date AND xb.PersonId2 = sb.PersonId)AS d ON d.PersonId = p.PersonId)AS a where 1=1 ";
if (iAdminID != null)
{
sqlString += $" and PersonId = '{iAdminID}' ";
}
if (deptId != null)
{
sqlString += $" and DeptId = '{deptId}' ";
}
if (startTime != null)
{
sqlString += $" and work_start >= '{startTime}' ";
}
if (endTime != null)
{
sqlString += $" and work_start <= '{endTime}' ";
}
DapperExtentions.EntityForSqlToPager <dynamic>(sqlString, sort, ordering, num, page, out MessageEntity result, ConnectionFactory.DBConnNames.GisPlateform);
return(result);
}
public MessageEntity GetListByPage(string sort, string ordering, int num, int page)
{
string sql = "SELECT * FROM P_Admin";
DapperExtentions.EntityForSqlToPager <P_Admin>(sql, sort, ordering, num, page, out MessageEntity result);
return(result);
}
/// <summary>
///
/// </summary>
/// <param name="pointId"></param>
/// <param name="pointTable"></param>
/// <returns></returns>
public MessageEntity UpdatePointTableByPointId(PointAreaInfo pointTable)
{
using (var conn = ConnectionFactory.GetDBConn(ConnectionFactory.DBConnNames.PipeInspectionBase_Gis_OutSide))
{
var rows = 0;
string sql = $@"select count(0) as count from PointAreaInfo p where p.PlanAreaId = {pointTable.PlanAreaId} and p.PointName='{ pointTable.PointName}'";
List <dynamic> pointcc = conn.Query <dynamic>(sql).ToList();
if (pointcc[0].count > 0)
{
return(MessageEntityTool.GetMessage(ErrorType.SqlError, "同一区域内不能添加相同关键点"));
}
var updateSql = DapperExtentions.MakeUpdateSql(pointTable);
if (string.IsNullOrEmpty(updateSql))
{
return(MessageEntityTool.GetMessage(ErrorType.SqlError, "请检查实体类"));
}
try
{
rows = conn.Execute(updateSql, pointTable);
return(MessageEntityTool.GetMessage(rows));
}
catch (Exception e)
{
return(MessageEntityTool.GetMessage(ErrorType.SqlError, e.Message));
}
}
}
public MessageEntity Get(DateTime?startTime, DateTime?endTime, string sort, string ordering, int num, int page)
{
if (string.IsNullOrEmpty(sort))
{
sort = "PersonName";
}
if (string.IsNullOrEmpty(ordering))
{
ordering = "desc";
}
string sql = string.Format(@"SELECT P.PersonName,COUNT(EventID) ECount FROM M_Event E
left join ( select iAdminID AS PersonId, cAdminName AS PersonName from GisPlateform.dbo.P_Admin p ) P ON E.PersonId=P.PersonId
where E.DeleteStatus=0 and 1=1 ");
if (startTime != null)
{
sql += $" and UpTime>='{startTime}' ";
}
if (endTime != null)
{
endTime = DateTime.Parse(endTime.ToString()).AddDays(1);
sql += $" and UpTime<='{endTime}' ";
}
sql += " group by p.PersonName";
DapperExtentions.EntityForSqlToPager <dynamic>(sql, sort, ordering, num, page, out MessageEntity result, ConnectionFactory.DBConnNames.PipeInspectionBase_Gis_OutSide);
return(result);
}
public MessageEntity GetPlanTypByPlanTypeId(int planTypeId, string sort, string ordering, int num, int page)
{
if (string.IsNullOrEmpty(sort))
{
sort = "PlanTypeId";
}
if (string.IsNullOrEmpty(ordering))
{
ordering = "asc";
}
string strWhere = $" and lp.ParentTypeId = {planTypeId} ";
string sqlStr = $@" select lp.PlanTypeId,lp.PlanTypeName,lp.Operater,lp.OperateDate,pe.PersonName,lp.ParentTypeId,ll.PlanTypeName as ParPlanTypeName
from L_PLANTYPE lp
left join L_PLANTYPE ll on ll.PlanTypeId = lp.ParentTypeId
left join L_Person pe on pe.PersonId = lp.Operater
where lp.ParentTypeId <>0 and lp.PlanTypeState = 1 {strWhere}";
DapperExtentions.EntityForSqlToPager <dynamic>(sqlStr, sort, ordering, num, page, out MessageEntity result, ConnectionFactory.DBConnNames.PipeInspectionBase_Gis_OutSide);
return(result);
}
public MessageEntity Get(string sort, string ordering, int num, int page)
{
string sqlStr = $@" select * from L_WorkTimeInterval ";
DapperExtentions.EntityForSqlToPager <dynamic>(sqlStr, sort, ordering, num, page, out MessageEntity messageEntity, ConnectionFactory.DBConnNames.PipeInspectionBase_Gis_OutSide);
return(messageEntity);
}
/// <summary>
/// 获取App上传记录
/// </summary>
/// <param name="sort">排序字段默认(UploadTime)</param>
/// <param name="ordering">desc/asc默认降序(desc)</param>
/// <param name="num">默认20</param>
/// <param name="page">默认1</param>
/// <returns></returns>
public MessageEntity Get(string sort, string ordering, int num, int page)
{
string sqlStr = $@"SELECT ApkName ,UploadTime,VersionId FROM dbo.AndroidVersion";
DapperExtentions.EntityForSqlToPager <AndroidVersionModel>(sqlStr, sort, ordering, num, page, out MessageEntity result, ConnectionFactory.DBConnNames.GisPlateform);
return(result);
}
public MessageEntity Get(string sort, string ordering, int num, int page)
{
string sqlStr = $@"SELECT a.* FROM dbo.P_Department a where PiDeptID !=0 ";
DapperExtentions.EntityForSqlToPager <P_Department>(sqlStr, sort, ordering, num, page, out MessageEntity result, ConnectionFactory.DBConnNames.GisPlateform);
return(result);
}
public MessageEntity GetPlanManageInfo(int?planTypeId, string serachCondition, int?isNomalPlan, int?deptId, int?operatorId, DateTime?startTime, DateTime?endTime, string sort, string ordering, int num, int page)
{
string sqlwhere = "";
if (startTime != null)
{
sqlwhere += $" and lp.OperateDate>='{startTime}' ";
}
if (endTime != null)
{
sqlwhere += $" and lp.OperateDate<='{endTime}' ";
}
if (deptId != null)
{
sqlwhere += $" and lpa.DeptId={deptId} ";
}
if (operatorId != null)
{
sqlwhere += $" and lpa.PersonId={operatorId} ";
}
if (!string.IsNullOrEmpty(serachCondition))
{
{ sqlwhere += " and lp.PlanName like '%" + serachCondition + "%' "; }
}
if (planTypeId != null)
{
{ sqlwhere += $" and lp.PlanTypeId ={planTypeId} "; }
}
if (isNomalPlan != null)
{
{ sqlwhere += $" and lp.BoolNormalPlan ={isNomalPlan} "; }
}
string sql = $@"( SELECT lpl.PlanLineId,lp.PlanId,lp.PlanName,lp.PlanTypeId,lpt.PlanTypeName,lp.PlanAreaId,lpa.PlanAreaName,lp.EquipmentList,lp.EquipmentListName as EquipmentName
,lp.PlanPath,lp.PlanCycleId,lpc.PlanCycleName,'1' as PlanState,
case lp.MoveType when 2 then '步行' when 1 then '车巡' end as MoveType,
case lp.BoolFeedBack when 1 then '需反馈' when 0 then '仅到位' end as BoolFeedBack,
case lp.BoolNormalPlan when 1 then '常规' when 0 then '临时' end as BoolNormalPlan,
lp.Operator,lp.OperateDate,lpa.DeptId,lpa.PersonId,lpl.PlanLineName,lp.Remark,lpt2.PlanTypeName as PlanTypeNameLB,
case lp.PlanTypeId when 1 then lpa.GeoText else lpl.GeoText end as GeoText,lpl.PatroGeoText as PatroGeoText
FROM L_PLAN lp
left join L_PLANTYPE lpt on lpt.PlanTypeId = lp.PlanTypeId
left join L_PlanArea lpa on lpa.PlanAreaId = lp.PlanAreaId
left join L_PLANCYCLE lpc on lpc.PlanCycleId = lp.PlanCycleId
left join L_PlanLine lpl on lpl.PlanLineId = lp.PlanLineId
left join L_PLANTYPE lpt2 on lpt2.PlanTypeId = lp.PlanTypeLB where 1 =1 and (lp.PlanState =1 or lp.PlanState is null) {sqlwhere} )";
DapperExtentions.EntityForSqlToPager <dynamic>(sql, sort, ordering, num, page, out MessageEntity result, ConnectionFactory.DBConnNames.PipeInspectionBase_Gis_OutSide);
return(result);
}
public MessageEntity GetUsers(string searchCondition, int?roleId, int?deptId, string sort, string ordering, int num, int page)
{
string strWhere = " where IsDelete!=1 ";
if (!string.IsNullOrEmpty(searchCondition))
{
strWhere += " and (a.cAdminName like '%" + searchCondition + "%'or a.CJobNumber like '%" + searchCondition + "%' or a.cAdminTel like '%" + searchCondition + "%')";
}
if (roleId != null)
{
strWhere += $" and a.iRoleID = {roleId} ";
}
if (deptId != null)
{
strWhere += $" and a.iDeptID = {deptId} ";
}
string sqlStr = $@" SELECT a.iAdminID,
a.iDeptID,
a.iRoleID,
a.cAdminName,
a.CJobNumber,
a.cAdminSex,
a.cAdminTel,
a.cAdminEmail,
a.iIsLocked,
a.dExpireDate,
a.iIsAllowChangePWD,
a.cLastLoginIP,
a.dLastLoginTime,
a.dLastLogoutTime,
a.iLoginTimes,
a.cTitlePic,
a.iSkinID,
a.LoginTicket,
a.Smid,
a.JueSeID,
a.IsNumOne,
a.Level,
a.AllPinyin,
a.IsDelete ,
p.cDepName,
r.cRoleName,a.IsAssignment,a.iDeptIDs
FROM dbo.P_Admin a LEFT JOIN dbo.P_Department p ON a.iDeptID=p.iDeptID
LEFT JOIN dbo.P_Role r ON r.iRoleID = a.iRoleID {strWhere}" ;
var users = DapperExtentions.EntityForSqlToPager <dynamic>(sqlStr, sort, ordering, num, page, out MessageEntity result, ConnectionFactory.DBConnNames.GisPlateform);
return(result);
}
public MessageEntity Get(string roleName, string sort, string ordering, int num, int page)
{
string strWhere = " where 1=1 ";
if (!string.IsNullOrEmpty(roleName))
{
strWhere += $" and cRoleName like '%{roleName}%' ";
}
string sqlStr = $@" SELECT * from P_Role {strWhere} ";
var users = DapperExtentions.EntityForSqlToPager <P_Role>(sqlStr, sort, ordering, num, page, out MessageEntity result, ConnectionFactory.DBConnNames.GisPlateform);
return(result);
}
public MessageEntity GetAllPlanAreaInfo(string sort, string ordering, int num, int page)
{
if (string.IsNullOrEmpty(sort))
{
sort = "PersonName";
}
if (string.IsNullOrEmpty(ordering))
{
ordering = "asc";
}
string sql = "select a.*,b.DeptName,c.PersonName from L_PlanArea as a left join L_Department as b on a.DeptId=b.DeptId left join L_Person as c on a.PersonId=c.PersonId where a.AreaState=1";
DapperExtentions.EntityForSqlToPager <dynamic>(sql, sort, ordering, num, page, out MessageEntity result, ConnectionFactory.DBConnNames.PipeInspectionBase_Gis_OutSide);
return(result);
}
public MessageEntity GetPlanLineInfo(string sort, string ordering, int num, int page)
{
if (string.IsNullOrEmpty(sort))
{
sort = "PlanLineId";
}
if (string.IsNullOrEmpty(ordering))
{
ordering = "asc";
}
string sql = "select * from L_PlanLine where PlanLintState = 1";
DapperExtentions.EntityForSqlToPager <dynamic>(sql, sort, ordering, num, page, out MessageEntity result, ConnectionFactory.DBConnNames.PipeInspectionBase_Gis_OutSide);
return(result);
}
public MessageEntity GetEventType(string sort, string ordering, int num, int page)
{
if (string.IsNullOrEmpty(sort))
{
sort = "EventTypeName";
}
if (string.IsNullOrEmpty(ordering))
{
ordering = "asc";
}
string sqlstr = @"select * from M_EventType where ParentTypeId=0";
DapperExtentions.EntityForSqlToPager <dynamic>(sqlstr, sort, ordering, num, page, out MessageEntity result, ConnectionFactory.DBConnNames.PipeInspectionBase_Gis_OutSide);
return(result);
}
public MessageEntity DeleteEventType(M_EventType eventType)
{
using (var conn = ConnectionFactory.GetDBConn(ConnectionFactory.DBConnNames.PipeInspectionBase_Gis_OutSide))
{
var rows = 0;
var excSql = DapperExtentions.MakeDeleteSql(eventType);
if (string.IsNullOrEmpty(excSql))
{
return(MessageEntityTool.GetMessage(ErrorType.SqlError, "请检查实体类"));
}
try
{
rows = conn.Execute(excSql, eventType);
return(MessageEntityTool.GetMessage(rows));
}
catch (Exception e)
{
return(MessageEntityTool.GetMessage(ErrorType.SqlError, e.Message));
}
}
}
public MessageEntity GetPlanLineDetailInfo(string planLineId, string sort, string ordering, int num, int page)
{
string sql = @" select PlanLineDetaiId
, PlanLineId
, X
, Y
, OrderNum
, AddTime
, ImportPointType
, CASE WHEN ImportPointName IS NULL THEN '自动点位' ELSE ImportPointName END AS ImportPointName
,State
,LengthToFirsPointt from PlanLineDetail where 1=1 ";
if (!string.IsNullOrEmpty(planLineId))
{
sql += $" and PlanLineId = '{planLineId}' ";
}
DapperExtentions.EntityForSqlToPager <dynamic>(sql, sort, ordering, num, page, out MessageEntity result, ConnectionFactory.DBConnNames.PipeInspectionBase_Gis_OutSide);
return(result);
}
public MessageEntity GetAllPointAreaInfo(string planAreaId, string sort, string ordering, int num, int page)
{
if (string.IsNullOrEmpty(sort))
{
sort = "PointId";
}
if (string.IsNullOrEmpty(ordering))
{
ordering = "asc";
}
string sql = @"select a.*,b.PlanAreaName from PointAreaInfo as a join L_PlanArea as b on a.PlanAreaId=b.PlanAreaId where 1=1";
if (!string.IsNullOrEmpty(planAreaId))
{
sql += " and a.PlanAreaId='" + planAreaId + "'";
}
DapperExtentions.EntityForSqlToPager <dynamic>(sql, sort, ordering, num, page, out MessageEntity result, ConnectionFactory.DBConnNames.PipeInspectionBase_Gis_OutSide);
return(result);
}
public MessageEntity AddPlanArea(L_PlanArea planArea)
{
using (var conn = ConnectionFactory.GetDBConn(ConnectionFactory.DBConnNames.PipeInspectionBase_Gis_OutSide))
{
var rows = 0;
var insertSql = DapperExtentions.MakeInsertSql(planArea);
if (string.IsNullOrEmpty(insertSql))
{
return(MessageEntityTool.GetMessage(ErrorType.SqlError, "请检查实体类"));
}
try
{
rows = conn.Execute(insertSql, planArea);
return(MessageEntityTool.GetMessage(rows));
}
catch (Exception e)
{
return(MessageEntityTool.GetMessage(ErrorType.SqlError, e.Message));
}
}
}
public MessageEntity GetEventContentInfo(int?eventTypeId, string sort, string ordering, int num, int page)
{
if (string.IsNullOrEmpty(sort))
{
sort = "EventTypeName";
}
if (string.IsNullOrEmpty(ordering))
{
ordering = "asc";
}
string sqlstr = @"select b.EventTypeId,A.EventTypeName ParentTypeName,b.EventTypeName,b.ParentTypeId,b.ExecTime from M_EventType a left join M_EventType b on b.ParentTypeId = a.EventTypeId where 1=1 and b.ParentTypeId <> '0'";
if (eventTypeId != null)
{
sqlstr += " and b.ParentTypeId=( select EventTypeId from M_EventType where EventTypeId ='" + eventTypeId + "')";
}
DapperExtentions.EntityForSqlToPager <dynamic>(sqlstr, sort, ordering, num, page, out MessageEntity result, ConnectionFactory.DBConnNames.PipeInspectionBase_Gis_OutSide);
return(result);
}
public MessageEntity PostTaskEqument(L_Task_CompleteDetail task_CompleteDetail)
{
using (var conn = ConnectionFactory.GetDBConn(ConnectionFactory.DBConnNames.PipeInspectionBase_Gis_OutSide))
{
var rows = 0;
int recordNum = 0;
var insertSql = DapperExtentions.MakeInsertSql(task_CompleteDetail);
if (string.IsNullOrEmpty(insertSql))
{
return(MessageEntityTool.GetMessage(ErrorType.SqlError, "请检查实体类"));
}
if (task_CompleteDetail.EquType != null)
{
recordNum = conn.ExecuteScalar <int>("select count(0) from L_Task_CompleteDetail where TaskId=@TaskId and Devicesmid=@Devicesmid and EquType=@EquType", task_CompleteDetail);
}
else
{
recordNum = conn.ExecuteScalar <int>("select count(0) from L_Task_CompleteDetail where TaskId=@TaskId and Devicesmid=@Devicesmid", task_CompleteDetail);
}
if (recordNum > 0)
{
return(MessageEntityTool.GetMessage(ErrorType.NotUnique, null, "同一任务不能重复巡检设备"));
}
try
{
rows = conn.Execute(insertSql, task_CompleteDetail);
return(MessageEntityTool.GetMessage(rows));
}
catch (Exception e)
{
return(MessageEntityTool.GetMessage(ErrorType.SqlError, e.Message));
}
}
}
public int UpdateEntity(object entity, ConnectionFactory.DBConnNames dBConnName, out MessageEntity messageEntity)
{
using (var conn = ConnectionFactory.GetDBConn(dBConnName))
{
var rows = 0;
var updateSql = DapperExtentions.MakeUpdateSql(entity);
if (string.IsNullOrEmpty(updateSql))
{
messageEntity = MessageEntityTool.GetMessage(ErrorType.SqlError, "请检查实体类");
return(0);
}
try
{
rows = conn.Execute(updateSql, entity);
messageEntity = MessageEntityTool.GetMessage(rows);
}
catch (Exception e)
{
messageEntity = MessageEntityTool.GetMessage(ErrorType.SqlError, e.Message);
}
return(rows);
}
}
public MessageEntity GetTaskManageInfo(string serachCondition, int?isNomalPlan, int?isAssigned, int?deptId, int?operatorId, DateTime?startTime, DateTime?endTime, string sort, string ordering, int num, int page)
{
string sqlwhere = "";
if (startTime != null)
{
sqlwhere += $" and lt.VisitStarTime>= '{startTime}' ";
}
if (endTime != null)
{
sqlwhere += $" and lt.VisitStarTime<='{endTime}' ";
}
if (isNomalPlan != null)
{
sqlwhere += $" and lp.BoolNormalPlan= {isNomalPlan} ";
}
if (isAssigned != null)
{
if (isAssigned == 1)
{
sqlwhere += " and lt.AssignState =1 ";
}
else
{
sqlwhere += " and (lt.AssignState <>1 or lt.AssignState is null) ";
}
}
if (deptId != null)
{
sqlwhere += $" and lt.ProraterDeptId={deptId} ";
}
if (operatorId != null)
{
sqlwhere += $" and lt.ProraterId={operatorId} ";
}
//快速查询条件添加
if (!string.IsNullOrEmpty(serachCondition))
{
sqlwhere += string.Format(@" and ( lt.TaskCode like '%{0}%' or lt.TaskName like '%{0}%' or lt.Remark like '%{0}%' ) ", serachCondition);
}
string sql = $@"SELECT *,
t.PlanEqCount + t.planAreaPointCount + t.planLinePointCount AS allEquCount
FROM
(
SELECT lt.TaskId,
lt.TaskName,
lt.ProraterId,
lt.ProraterName,
lt.ProraterDeptId,
lt.ProraterDeptName,
lt.VisitStarTime,
lt.VisitOverTime,
CASE lp.MoveType
WHEN 2 THEN
'步行'
WHEN 1 THEN
'车巡'
END AS MoveType,
CASE lp.BoolFeedBack
WHEN 1 THEN
'需反馈'
WHEN 0 THEN
'仅到位'
END AS BoolFeedBack,
CASE lp.BoolNormalPlan
WHEN 1 THEN
'常规'
WHEN 0 THEN
'临时'
END AS BoolNormalPlan,
lpc.PlanCycleName,
lpt.PlanTypeName,
lp.Operator,
lp.OperateDate,
CASE
WHEN tcd.CompletedCount IS NULL THEN
0
ELSE
tcd.CompletedCount
END AS CompleteDetail,
'' AS FeedbackDetail,
'反馈定位' AS FeedbackOrientation,
lt.AssignState,
lt.TaskCode,
CASE
WHEN ple.PlanEqCount IS NULL THEN
0
ELSE
ple.PlanEqCount
END PlanEqCount,
CASE
WHEN pai.planAreaPointCount IS NULL THEN
0
ELSE
pai.planAreaPointCount
END planAreaPointCount,
CASE
WHEN pld.planLinePointCount IS NULL THEN
0
ELSE
pld.planLinePointCount
END planLinePointCount,
lpa.PlanAreaName,
lpl.PlanLineName,
lt.Remark,
lt.Descript,
lp.PlanTypeId,
lpl.PlanLineId,
lpa.PlanAreaId,
CASE
WHEN lpl.GeoText IS NULL THEN
lpa.GeoText
ELSE
lpl.GeoText
END AS GeoText,
lpl.PatroGeoText
FROM L_Task lt
LEFT JOIN L_PLAN lp
ON lp.PlanId = lt.PlanId
LEFT JOIN L_PlanLine lpl
ON lpl.PlanLineId = lp.PlanLineId
LEFT JOIN L_PlanArea lpa
ON lpa.PlanAreaId = lp.PlanAreaId
LEFT JOIN
(
SELECT COUNT(PlanListId) AS PlanEqCount,
PlanID
FROM L_PlanEquipmentDetail
GROUP BY PlanID
) ple
ON ple.PlanID = lt.PlanId
LEFT JOIN L_PLANCYCLE lpc
ON lpc.PlanCycleId = lp.PlanCycleId
LEFT JOIN L_PLANTYPE lpt
ON lpt.PlanTypeId = lp.PlanTypeId
LEFT JOIN
(
SELECT PlanLineId,
COUNT(0) planLinePointCount
FROM PlanLineDetail pld
GROUP BY PlanLineId
) pld
ON pld.PlanLineId = lpl.PlanLineId
LEFT JOIN
(
SELECT PlanAreaId,
COUNT(0) planAreaPointCount
FROM PointAreaInfo pai
GROUP BY PlanAreaId
) pai
ON pai.PlanAreaId = lpa.PlanAreaId
LEFT JOIN
(
SELECT TaskId,
COUNT(0) AS CompletedCount
FROM dbo.L_Task_CompleteDetail
GROUP BY TaskId
) tcd
ON tcd.TaskId = lt.TaskId
WHERE 1 = 1
AND lt.TaskState = 1 {sqlwhere}) t";
DapperExtentions.EntityForSqlToPager <dynamic>(sql, sort, ordering, num, page, out MessageEntity result, ConnectionFactory.DBConnNames.PipeInspectionBase_Gis_OutSide);
return(result);
}
public List <dynamic> GetPipeAndPointStatistics(string layer, string installation_address, string material_science, string caliber, DateTime?startCompletion_date, DateTime?endCompletion_date, string sort, string ordering, string[] groupFields, out string errMessge, out MessageEntity result)
{
string query = @"SELECT {0}
FROM
(
SELECT OBJECTID,
equipment_number,
'' AS material_science,
caliber,
elevation,
depth,
0 AS startingpoint_elevation,
0 AS endpoint_elevation,
Installation_address,
construction_unit,
management_unit,
completion_date,
laying_age,
0 AS startingpoint_depth,
0 AS endpoint_depth,
embedding_mode,
coordinate_x,
coordinate_y,
equipment_type,
switch_type,
0 AS startingpoint_x,
0 AS startingpoint_y,
0 AS endpoint_x,
0 AS endpoint_y,
Interface_form,
remarks,
0 AS length,
Coversize,
CoverMaterial
FROM POINTTABLE
UNION
SELECT OBJECTID,
equipment_number,
material_science,
caliber,
0 AS elevation,
0 AS depth,
startingpoint_elevation,
endpoint_elevation,
Installation_address,
construction_unit,
management_unit,
completion_date,
laying_age,
startingpoint_depth,
endpoint_depth,
embedding_mode,
0 AS coordinate_x,
0 AS coordinate_y,
'管网' AS equipment_type,
'' AS switch_type,
startingpoint_x,
startingpoint_y,
endpoint_x,
endpoint_y,
Interface_form,
remarks,
length,
'' AS Coversize,
'' AS CoverMaterial
FROM PIPETABLE
) t
WHERE 1 = 1 {1} {2}";
string sqlWhere = "";
string selectItem = "";
string groupItem = "";
if (!string.IsNullOrEmpty(layer) && layer != "全部")
{
sqlWhere += $" and equipment_type = '{layer}' ";
//selectItem += " ,equipment_type ";
//groupItem += " ,equipment_type ";
}
if (!string.IsNullOrEmpty(installation_address) && installation_address != "全部")
{
sqlWhere += $" and installation_address like '%{installation_address}%' ";
//selectItem += " ,installation_address ";
//groupItem += " ,installation_address ";
}
if (!string.IsNullOrEmpty(material_science) && material_science != "全部")
{
sqlWhere += $" and material_science = '{material_science}' ";
//selectItem += " ,material_science ";
//groupItem += " ,material_science ";
}
if (!string.IsNullOrEmpty(caliber) && caliber != "全部")
{
sqlWhere += $" and Caliber = '{caliber}' ";
//selectItem += " ,Caliber ";
//groupItem += " ,Caliber ";
}
if (groupFields == null || groupFields.Length == 0)
{
selectItem = " equipment_type,installation_address ,material_science , Caliber ,COUNT(0) AS records ";
groupItem = " group by equipment_type,installation_address,material_science,Caliber ";
}
else
{
foreach (string field in groupFields)
{
if (!string.IsNullOrEmpty(field))
{
selectItem += "," + field;
groupItem += "," + field;
}
}
selectItem = selectItem.Remove(0, 1);
selectItem += " ,COUNT(0) AS records ";
groupItem = groupItem.Remove(0, 1);
groupItem = groupItem.Insert(0, " group by ");
}
if (startCompletion_date.HasValue && endCompletion_date.HasValue)
{
sqlWhere += $" and completion_date >= '{startCompletion_date}' and completion_date <= '{endCompletion_date}' ";
}
query = string.Format(query, selectItem, sqlWhere, groupItem);
var pointTables = DapperExtentions.EntityForSqlToPager <dynamic>(query, sort, ordering, int.MaxValue, 1, out result);
if (result.ErrorType != (int)ErrorType.Success)
{
errMessge = result.ExceptionMsg;
}
else
{
errMessge = "";
}
return(pointTables);
}
public List <PointTable> GetPipeAndPointByPage(string layer, string installation_address, string material_science, string minCaliber, string maxCaliber, DateTime?startCompletion_date, DateTime?endCompletion_date, string sort, string ordering, int num, int page, out string errMessge, out MessageEntity result)
{
string query = @"SELECT *
FROM
(
SELECT OBJECTID,
equipment_number,
'' AS material_science,
caliber,
elevation,
depth,
0 AS startingpoint_elevation,
0 AS endpoint_elevation,
Installation_address,
construction_unit,
management_unit,
completion_date,
laying_age,
0 AS startingpoint_depth,
0 AS endpoint_depth,
embedding_mode,
coordinate_x,
coordinate_y,
equipment_type,
switch_type,
0 AS startingpoint_x,
0 AS startingpoint_y,
0 AS endpoint_x,
0 AS endpoint_y,
Interface_form,
remarks,
0 AS length,
Coversize,
CoverMaterial
FROM POINTTABLE
UNION
SELECT OBJECTID,
equipment_number,
material_science,
caliber,
0 AS elevation,
0 AS depth,
startingpoint_elevation,
endpoint_elevation,
Installation_address,
construction_unit,
management_unit,
completion_date,
laying_age,
startingpoint_depth,
endpoint_depth,
embedding_mode,
0 AS coordinate_x,
0 AS coordinate_y,
'管网' AS equipment_type,
'' AS switch_type,
startingpoint_x,
startingpoint_y,
endpoint_x,
endpoint_y,
Interface_form,
remarks,
length,
'' AS Coversize,
'' AS CoverMaterial
FROM PIPETABLE
) t
WHERE 1 = 1 ";
string sqlWhere = "";
if (!string.IsNullOrEmpty(layer) && layer != "全部")
{
sqlWhere += $" and equipment_type = '{layer}' ";
}
if (!string.IsNullOrEmpty(installation_address) && installation_address != "全部")
{
sqlWhere += $" and installation_address like '%{installation_address}%' ";
}
if (!string.IsNullOrEmpty(material_science) && material_science != "全部")
{
sqlWhere += $" and material_science = '{material_science}' ";
}
if (!string.IsNullOrEmpty(minCaliber) && minCaliber != "全部")
{
sqlWhere += $" and Caliber = '{minCaliber}' ";
}
if (startCompletion_date.HasValue && endCompletion_date.HasValue)
{
sqlWhere += $" and completion_date >= '{startCompletion_date}' and completion_date <= '{endCompletion_date}' ";
}
var pointTables = DapperExtentions.EntityForSqlToPager <PointTable>(query + sqlWhere, sort, ordering, num, page, out result);
if (result.ErrorType != (int)ErrorType.Success)
{
errMessge = result.ExceptionMsg;
}
else
{
errMessge = "";
}
return(pointTables);
}
