/// <summary> /// 获取单条记录 /// </summary> /// <returns></returns> public static OD_USER_Model getModel(OD_USER_SW sw) { OD_USER_Model m = new OD_USER_Model(); DataTable dt = BaseDT.OD_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.ODUID = dt.Rows[i]["ODUID"].ToString(); m.ONDUTYDATE = PublicClassLibrary.ClsSwitch.SwitDate(dt.Rows[i]["ONDUTYDATE"].ToString()); m.BYORGNO = dt.Rows[i]["BYORGNO"].ToString(); m.ONDUTYUSERTYPE = dt.Rows[i]["ONDUTYUSERTYPE"].ToString(); m.ONDUTYUSERID = dt.Rows[i]["ONDUTYUSERID"].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.ONDUTYUSERID); } dt.Clear(); dt.Dispose(); dtUser.Clear(); dtUser.Dispose(); return(m); }
/// <summary> /// 获取列表 /// </summary> /// <param name="sw"></param> /// <returns></returns> public static IEnumerable <OD_USER_Model> getListModel(OD_USER_SW sw) { var result = new List <OD_USER_Model>(); DataTable dt = BaseDT.OD_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++) { OD_USER_Model m = new OD_USER_Model(); m.ODUID = dt.Rows[i]["ODUID"].ToString(); m.ONDUTYDATE = PublicClassLibrary.ClsSwitch.SwitDate(dt.Rows[i]["ONDUTYDATE"].ToString()); m.BYORGNO = dt.Rows[i]["BYORGNO"].ToString(); m.ONDUTYUSERTYPE = dt.Rows[i]["ONDUTYUSERTYPE"].ToString(); m.ONDUTYUSERID = dt.Rows[i]["ONDUTYUSERID"].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.ONDUTYUSERID); 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 IEnumerable <OD_USER_Model> getWeekCountListModel(OD_USER_SW sw) { var result = new List <OD_USER_Model>(); DataTable dt = BaseDT.OD_USER.getWeekDT(sw); DataTable dtU = BaseDT.T_SYSSEC_USER.getDT(new T_SYSSEC_IPSUSER_SW { }); //获取所有系统用户 DataTable dtORG = BaseDT.T_SYS_ORG.getDT(new T_SYS_ORGSW { TopORGNO = sw.curOrgNo, OnlyGetShiXian = "1" }); //获取单位 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.OD_USER.getCountByDT(dt, uid); OD_USER_Model m = new OD_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 <OD_USER_Model> getNextClassListModel(OD_USER_SW sw) { int classID = int.Parse(sw.ONDUTYUSERTYPE);//当前班次 switch (classID) { case 1: sw.ONDUTYUSERTYPE = "2"; break; case 2: sw.ONDUTYUSERTYPE = "3"; break; default: sw.ONDUTYUSERTYPE = "1"; sw.ONDUTYDATE = PublicClassLibrary.ClsSwitch.SwitDate(Convert.ToDateTime(sw.ONDUTYDATE).AddDays(1)); break; } var result = new List <OD_USER_Model>(); DataTable dt = BaseDT.OD_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++) { OD_USER_Model m = new OD_USER_Model(); m.ODUID = dt.Rows[i]["ODUID"].ToString(); m.ONDUTYDATE = PublicClassLibrary.ClsSwitch.SwitDate(dt.Rows[i]["ONDUTYDATE"].ToString()); m.BYORGNO = dt.Rows[i]["BYORGNO"].ToString(); m.ONDUTYUSERTYPE = dt.Rows[i]["ONDUTYUSERTYPE"].ToString(); m.ONDUTYUSERID = dt.Rows[i]["ONDUTYUSERID"].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.ONDUTYUSERID); 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> /// 根据查询条件获取DataTable /// </summary> /// <param name="sw">参见OD_USER_SW</param> /// <returns>DataTable</returns> public static DataTable getDT(OD_USER_SW sw) { StringBuilder sb = new StringBuilder(); sb.AppendFormat("SELECT "); sb.AppendFormat(" OD_TYPEID,ODUID, ONDUTYDATE, BYORGNO, ONDUTYUSERTYPE, ONDUTYUSERID, ISATTENDED,ATTENDEDTIME"); sb.AppendFormat(" FROM OD_USER"); sb.AppendFormat(" WHERE 1=1"); if (string.IsNullOrEmpty(sw.BYORGNO) == false) { sb.AppendFormat(" AND BYORGNO='{0}'", sw.BYORGNO); } if (string.IsNullOrEmpty(sw.ONDUTYUSERID) == false) { sb.AppendFormat(" AND ONDUTYUSERID='{0}'", sw.ONDUTYUSERID); } if (string.IsNullOrEmpty(sw.OD_TYPEID) == false) { sb.AppendFormat(" AND OD_TYPEID='{0}'", sw.OD_TYPEID); } if (string.IsNullOrEmpty(sw.DTBegin) == false) { sb.AppendFormat(" AND ONDUTYDATE>='{0}'", sw.DTBegin); } if (string.IsNullOrEmpty(sw.DTEnd) == false) { sb.AppendFormat(" AND ONDUTYDATE<='{0}'", sw.DTEnd); } if (string.IsNullOrEmpty(sw.OD_TYPEID) == false) { sb.AppendFormat(" AND ONDUTYDATE in(select ONDUTYDATE from OD_DATE where OD_TYPEID={0})", sw.OD_TYPEID); } if (string.IsNullOrEmpty(sw.ONDUTYDATE) == false) { sb.AppendFormat(" AND ONDUTYDATE='{0}'", sw.ONDUTYDATE); } if (string.IsNullOrEmpty(sw.ISATTENDED) == false) { sb.AppendFormat(" AND ISATTENDED='{0}'", sw.ISATTENDED); } if (string.IsNullOrEmpty(sw.ONDUTYUSERTYPE) == false) { sb.AppendFormat(" AND ONDUTYUSERTYPE='{0}'", sw.ONDUTYUSERTYPE); } sb.AppendFormat(" order by ONDUTYDATE"); return(DataBaseClass.FullDataSet(sb.ToString()).Tables[0]); }
/// <summary> /// 值班次数 /// </summary> /// <param name="sw">sw</param> /// <returns></returns> public static DataTable GetDutyCoutDT(OD_USER_SW sw) { StringBuilder sb = new StringBuilder(); sb.AppendFormat("select USERNAME,sum( CASE WHEN ONDUTYUSERTYPE = '1' THEN 1 ELSE 0 END) AS 'zaob',"); sb.AppendFormat("sum( CASE WHEN ONDUTYUSERTYPE = '2' THEN 1 ELSE 0 END) AS 'zhongb',"); sb.AppendFormat("sum( CASE WHEN ONDUTYUSERTYPE = '3' THEN 1 ELSE 0 END) AS 'wanb',"); sb.AppendFormat("sum(CASE WHEN ONDUTYUSERTYPE = '-1' THEN 1 ELSE 0 END) AS 'daib'"); sb.AppendFormat(" FROM dbo.OD_USER left join dbo.T_SYSSEC_USER on ONDUTYUSERID=USERID where 1=1 "); sb.AppendFormat("and ONDUTYDATE>='{0}'", ClsSql.EncodeSql(sw.DTBegin)); sb.AppendFormat("and ONDUTYDATE<='{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">sw</param> /// <returns></returns> public static IEnumerable <OD_USER_DUCKCOUNT_Model> GetDutyCount(OD_USER_SW sw) { var result = new List <OD_USER_DUCKCOUNT_Model>(); var dt = BaseDT.OD_USER.GetDutyCoutDT(sw); for (int i = 0; i < dt.Rows.Count; i++) { OD_USER_DUCKCOUNT_Model oud = new OD_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> /// 获取周末人员值班信息 /// </summary> /// <param name="sw"></param> /// <returns></returns> public static DataTable getWeekDT(OD_USER_SW sw) { StringBuilder sb = new StringBuilder(); sb.AppendFormat("SELECT "); sb.AppendFormat(" OD_TYPEID,ODUID, ONDUTYDATE, BYORGNO, ONDUTYUSERTYPE, ONDUTYUSERID, ISATTENDED,ATTENDEDTIME"); sb.AppendFormat(" FROM OD_USER"); sb.AppendFormat(" WHERE 1=1"); sb.AppendFormat(" AND ONDUTYDATE in(SELECT ONDUTYDATE FROM OD_DATE where WEEK in('星期六','星期日'))"); if (string.IsNullOrEmpty(sw.DTBegin) == false) { sb.AppendFormat(" AND ONDUTYDATE>='{0}'", sw.DTBegin); } if (string.IsNullOrEmpty(sw.DTEnd) == false) { sb.AppendFormat(" AND ONDUTYDATE<='{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 ONDUTYDATE"); return(DataBaseClass.FullDataSet(sb.ToString()).Tables[0]); }