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