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