/// <summary> /// 获取列表 /// </summary> /// <param name="sw"></param> /// <returns></returns> public static IEnumerable <DUTY_USER_Model> getListModel(DUTY_USER_SW sw) { var result = new List <DUTY_USER_Model>(); DataTable dt = BaseDT.Duty.DUTY_USER.getDT(sw); DataTable dtUser = BaseDT.T_SYSSEC_USER.getDT(new T_SYSSEC_IPSUSER_SW { curOrgNo = sw.BYORGNO }); DataTable dtORG = BaseDT.T_SYS_ORG.getDT(new T_SYS_ORGSW { }); for (int i = 0; i < dt.Rows.Count; i++) { DUTY_USER_Model m = new DUTY_USER_Model(); m.DUID = dt.Rows[i]["DUID"].ToString(); m.DUTYDATE = PublicClassLibrary.ClsSwitch.SwitDate(dt.Rows[i]["DUTYDATE"].ToString()); m.BYORGNO = dt.Rows[i]["BYORGNO"].ToString(); m.DUTYUSERTYPE = dt.Rows[i]["DUTYUSERTYPE"].ToString(); m.DUTYUSERID = dt.Rows[i]["DUTYUSERID"].ToString(); m.ISATTENDED = dt.Rows[i]["ISATTENDED"].ToString(); m.ATTENDEDTIME = PublicClassLibrary.ClsSwitch.SwitTM(dt.Rows[i]["ATTENDEDTIME"].ToString()); m.USERNAME = BaseDT.T_SYSSEC_USER.getNameByUserList(dtUser, m.DUTYUSERID); m.ORGNAME = BaseDT.T_SYS_ORG.getName(dtORG, dt.Rows[i]["BYORGNO"].ToString()); result.Add(m); } dt.Clear(); dt.Dispose(); dtUser.Clear(); dtUser.Dispose(); dtORG.Clear(); dtORG.Dispose(); return(result); }
/// <summary> /// 获取单条记录 /// </summary> /// <returns></returns> public static DUTY_USER_Model getModel(DUTY_USER_SW sw) { DUTY_USER_Model m = new DUTY_USER_Model(); DataTable dt = BaseDT.Duty.DUTY_USER.getDT(sw); DataTable dtUser = BaseDT.T_SYSSEC_USER.getDT(new T_SYSSEC_IPSUSER_SW { curOrgNo = SystemCls.getCurUserOrgNo() }); if (dt.Rows.Count > 0) { int i = 0; m.DUID = dt.Rows[i]["DUID"].ToString(); m.DUTYDATE = PublicClassLibrary.ClsSwitch.SwitDate(dt.Rows[i]["DUTYDATE"].ToString()); m.BYORGNO = dt.Rows[i]["BYORGNO"].ToString(); m.DUTYUSERTYPE = dt.Rows[i]["DUTYUSERTYPE"].ToString(); m.DUTYUSERID = dt.Rows[i]["DUTYUSERID"].ToString(); m.ISATTENDED = dt.Rows[i]["ISATTENDED"].ToString(); m.ATTENDEDTIME = PublicClassLibrary.ClsSwitch.SwitTM(dt.Rows[i]["ATTENDEDTIME"].ToString()); m.USERNAME = BaseDT.T_SYSSEC_USER.getNameByUserList(dtUser, m.DUTYUSERID); } dt.Clear(); dt.Dispose(); dtUser.Clear(); dtUser.Dispose(); return(m); }
/// <summary> /// 获取下一班次值班员情况 /// </summary> /// <param name="sw"></param> /// <returns></returns> public static IEnumerable <DUTY_USER_Model> getNextClassListModel(DUTY_USER_SW sw) { int classID = int.Parse(sw.DUTYUSERTYPE);//当前班次 switch (classID) { case 1: sw.DUTYUSERTYPE = "2"; break; case 2: sw.DUTYUSERTYPE = "3"; break; default: sw.DUTYUSERTYPE = "1"; sw.DUTYDATE = PublicClassLibrary.ClsSwitch.SwitDate(Convert.ToDateTime(sw.DUTYDATE).AddDays(1)); break; } var result = new List <DUTY_USER_Model>(); DataTable dt = BaseDT.Duty.DUTY_USER.getDT(sw); DataTable dtUser = BaseDT.T_SYSSEC_USER.getDT(new T_SYSSEC_IPSUSER_SW { curOrgNo = sw.BYORGNO }); DataTable dtORG = BaseDT.T_SYS_ORG.getDT(new T_SYS_ORGSW { }); for (int i = 0; i < dt.Rows.Count; i++) { DUTY_USER_Model m = new DUTY_USER_Model(); m.DUID = dt.Rows[i]["DUID"].ToString(); m.DUTYDATE = PublicClassLibrary.ClsSwitch.SwitDate(dt.Rows[i]["DUTYDATE"].ToString()); m.BYORGNO = dt.Rows[i]["BYORGNO"].ToString(); m.DUTYUSERTYPE = dt.Rows[i]["DUTYUSERTYPE"].ToString(); m.DUTYUSERID = dt.Rows[i]["DUTYUSERID"].ToString(); m.ISATTENDED = dt.Rows[i]["ISATTENDED"].ToString(); m.ATTENDEDTIME = PublicClassLibrary.ClsSwitch.SwitTM(dt.Rows[i]["ATTENDEDTIME"].ToString()); m.USERNAME = BaseDT.T_SYSSEC_USER.getNameByUserList(dtUser, m.DUTYUSERID); m.ORGNAME = BaseDT.T_SYS_ORG.getName(dtORG, dt.Rows[i]["BYORGNO"].ToString()); result.Add(m); } dt.Clear(); dt.Dispose(); dtUser.Clear(); dtUser.Dispose(); dtORG.Clear(); dtORG.Dispose(); return(result); }
/// <summary> /// 值班次数 /// </summary> /// <param name="sw"></param> /// <returns></returns> public static DataTable GetDutyCoutDT(DUTY_USER_SW sw) { StringBuilder sb = new StringBuilder(); sb.AppendFormat("select USERNAME,sum( CASE WHEN DUTYUSERTYPE = '1' THEN 1 ELSE 0 END) AS 'zaob',"); sb.AppendFormat("sum( CASE WHEN DUTYUSERTYPE = '2' THEN 1 ELSE 0 END) AS 'zhongb',"); sb.AppendFormat("sum( CASE WHEN DUTYUSERTYPE = '3' THEN 1 ELSE 0 END) AS 'wanb',"); sb.AppendFormat("sum(CASE WHEN DUTYUSERTYPE = '-1' THEN 1 ELSE 0 END) AS 'daib'"); sb.AppendFormat(" FROM dbo.DUTY_USER left join dbo.T_SYSSEC_USER on DUTYUSERID=USERID where 1=1 "); sb.AppendFormat("and DUTYDATE>='{0}'", ClsSql.EncodeSql(sw.DTBegin)); sb.AppendFormat("and DUTYDATE<='{0}'", ClsSql.EncodeSql(sw.DTEnd)); sb.AppendFormat(" and BYORGNO = '{0}'", ClsSql.EncodeSql(sw.BYORGNO)); sb.AppendFormat(" GROUP BY USERNAME order by daib desc "); DataSet ds = DataBaseClass.FullDataSet(sb.ToString()); return(ds.Tables[0]); }
/// <summary> /// 周末值班统计 /// </summary> /// <param name="sw"></param> /// <returns></returns> public static IEnumerable <DUTY_USER_Model> getWeekCountListModel(DUTY_USER_SW sw) { var result = new List <DUTY_USER_Model>(); DataTable dt = BaseDT.Duty.DUTY_USER.getWeekDT(sw); DataTable dtU = BaseDT.T_SYSSEC_USER.getDT(new T_SYSSEC_IPSUSER_SW { }); //获取所有系统用户 DataTable dtORG = null; if (sw.curOrgNo.Substring(4, 11) == "00000000000" || sw.curOrgNo.Substring(6, 9) == "000000000")//市县 { dtORG = BaseDT.T_SYS_ORG.getDT(new T_SYS_ORGSW { TopORGNO = sw.curOrgNo, OnlyGetShiXian = "1" }); //获取单位 } else//乡镇 { dtORG = BaseDT.T_SYS_ORG.getDT(new T_SYS_ORGSW { TopORGNO = sw.curOrgNo }); //获取单位 } for (int i = 0; i < dtORG.Rows.Count; i++) { string orgNo = dtORG.Rows[i]["ORGNO"].ToString(); //编码 string orgName = dtORG.Rows[i]["ORGNAME"].ToString(); //名称 DataRow[] dr = dtU.Select("ORGNO='" + orgNo + "'"); for (int k = 0; k < dr.Length; k++) { string uid = dr[k]["USERID"].ToString(); string uName = BaseDT.T_SYSSEC_USER.getNameByUserList(dtU, uid); string count = BaseDT.Duty.DUTY_USER.getCountByDT(dt, uid); DUTY_USER_Model m = new DUTY_USER_Model(); m.ORGNAME = orgName; m.USERNAME = uName; m.weekCount = count; if (count != "0") { result.Add(m); } } } return(result); }
/// <summary> /// 统计值班人次数 /// </summary> /// <param name="sw"></param> /// <returns></returns> public static IEnumerable <DUTY_USER_DUCKCOUNT_Model> GetDutyCount(DUTY_USER_SW sw) { var result = new List <DUTY_USER_DUCKCOUNT_Model>(); var dt = BaseDT.Duty.DUTY_USER.GetDutyCoutDT(sw); for (int i = 0; i < dt.Rows.Count; i++) { DUTY_USER_DUCKCOUNT_Model oud = new DUTY_USER_DUCKCOUNT_Model(); oud.USERNAME = dt.Rows[i]["USERNAME"].ToString(); oud.zaobCount = dt.Rows[i]["zaob"].ToString(); oud.zhongbCount = dt.Rows[i]["zhongb"].ToString(); oud.wanbCount = dt.Rows[i]["wanb"].ToString(); oud.daiBCount = dt.Rows[i]["daib"].ToString(); result.Add(oud); } dt.Clear(); dt.Dispose(); return(result); }
/// <summary> /// 根据查询条件获取DataTable /// </summary> /// <param name="sw">参见DUTY_USER_SW</param> /// <returns>DataTable</returns> public static DataTable getDT(DUTY_USER_SW sw) { StringBuilder sb = new StringBuilder(); sb.AppendFormat("SELECT "); sb.AppendFormat(" DUID, DUTYDATE, BYORGNO, DUTYUSERTYPE, DUTYUSERID, ISATTENDED,ATTENDEDTIME"); sb.AppendFormat(" FROM DUTY_USER"); sb.AppendFormat(" WHERE 1=1"); if (string.IsNullOrEmpty(sw.BYORGNO) == false) { sb.AppendFormat(" AND BYORGNO='{0}'", sw.BYORGNO); } if (string.IsNullOrEmpty(sw.DUTYUSERID) == false) { sb.AppendFormat(" AND DUTYUSERID='{0}'", sw.DUTYUSERID); } if (string.IsNullOrEmpty(sw.DTBegin) == false) { sb.AppendFormat(" AND DUTYDATE>='{0}'", sw.DTBegin); } if (string.IsNullOrEmpty(sw.DTEnd) == false) { sb.AppendFormat(" AND DUTYDATE<='{0}'", sw.DTEnd); } if (string.IsNullOrEmpty(sw.DUTYDATE) == false) { sb.AppendFormat(" AND DUTYDATE='{0}'", sw.DUTYDATE); } if (string.IsNullOrEmpty(sw.ISATTENDED) == false) { sb.AppendFormat(" AND ISATTENDED='{0}'", sw.ISATTENDED); } if (string.IsNullOrEmpty(sw.DUTYUSERTYPE) == false) { sb.AppendFormat(" AND DUTYUSERTYPE='{0}'", sw.DUTYUSERTYPE); } sb.AppendFormat(" order by DUTYDATE"); return(DataBaseClass.FullDataSet(sb.ToString()).Tables[0]); }
/// <summary> /// 获取周末人员值班信息 /// </summary> /// <param name="sw"></param> /// <returns></returns> public static DataTable getWeekDT(DUTY_USER_SW sw) { StringBuilder sb = new StringBuilder(); sb.AppendFormat("SELECT "); sb.AppendFormat(" DUID, DUTYDATE, BYORGNO, DUTYUSERTYPE, DUTYUSERID, ISATTENDED,ATTENDEDTIME"); sb.AppendFormat(" FROM DUTY_USER"); sb.AppendFormat(" WHERE 1=1"); sb.AppendFormat(" AND DUTYDATE in(SELECT DISTINCT DUTYDATE FROM DUTY_USER where datepart(weekday,DUTYDATE)=1 or datepart(weekday,DUTYDATE)=7)"); if (string.IsNullOrEmpty(sw.DTBegin) == false) { sb.AppendFormat(" AND DUTYDATE>='{0}'", sw.DTBegin); } if (string.IsNullOrEmpty(sw.DTEnd) == false) { sb.AppendFormat(" AND DUTYDATE<='{0}'", sw.DTEnd); } if (string.IsNullOrEmpty(sw.curOrgNo) == false) { if (PublicCls.OrgIsShi(sw.curOrgNo)) { sb.AppendFormat(" and BYORGNO like '{0}%'", PublicCls.getShiIncOrgNo(sw.curOrgNo)); } else if (PublicCls.OrgIsXian(sw.curOrgNo)) { sb.AppendFormat(" and BYORGNO like '{0}%'", PublicCls.getXianIncOrgNo(sw.curOrgNo)); } else { sb.AppendFormat(" and BYORGNO = '{0}'", PublicCls.getZhenIncOrgNo(sw.curOrgNo)); } } sb.AppendFormat(" order by DUTYDATE"); return(DataBaseClass.FullDataSet(sb.ToString()).Tables[0]); }