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