//个人考勤日历
public void GETKQRLLIST(HttpContext context, Msg_Result msg, string P1, string P2, JH_Auth_UserB.UserInfo UserInfo)
{
string start = context.Request["start"];
string end = context.Request["end"];
DateTime sd = DateTime.Parse(start + " 00:00:00");
DateTime ed = DateTime.Parse(end + " 23:59:59");
string strWhere = "where ComID=" + UserInfo.QYinfo.ComId + " and KQUser='******' and KQDate between '" + sd + "' and '" + ed + "'";
List <TXSXManage.RLView> list = new List <TXSXManage.RLView>();
DataTable dt = new SZHL_KQJLB().GetDTByCommand(" SELECT * FROM SZHL_KQJL " + strWhere);
foreach (DataRow row in dt.Rows)
{
TXSXManage.RLView rlView = new TXSXManage.RLView();
rlView.title = (row["type"].ToString() == "0" ? "签到 " : "签退 ") + DateTime.Parse(row["KQDate"].ToString()).ToString("HH:mm");
if (row["Status"].ToString() == "1")
{
rlView.title += " 迟到";
}
else if (row["Status"].ToString() == "2")
{
rlView.title += " 早退";
}
else
{
rlView.title += " 正常";
}
rlView.start = row["KQDate"].ToString();
list.Add(rlView);
}
msg.Result = list;
}
public void GETKQJLLIST(HttpContext context, Msg_Result msg, string P1, string P2, JH_Auth_UserB.UserInfo UserInfo)
{
int page = 0;
int pagecount = 8;
int.TryParse(context.Request["p"] ?? "1", out page); //页码
int.TryParse(context.Request["pagecount"] ?? "8", out pagecount); //页数
page = page == 0 ? 1 : page;
int recordCount = 0;
string strWhere = string.Format(" ComId={0}", UserInfo.User.ComId);
if (P2 != "")//内容查询
{
strWhere += string.Format(" And (KQUserName like '%{0}%' OR KQBranch like '%{0}%' )", P2);
}
//根据创建时间查询
string time = context.Request["time"] ?? "";
if (time != "")
{
if (time == "1") //近一周
{
strWhere += string.Format(" And datediff(day,KQDate,getdate())<7");
}
else if (time == "2")
{ //近一月
strWhere += string.Format(" And datediff(day,KQDate,getdate())<30");
}
else if (time == "3") //自定义时间
{
string strTime = context.Request["starTime"] ?? "";
string endTime = context.Request["endTime"] ?? "";
if (strTime != "")
{
strWhere += string.Format(" And KQDate >='{0}'", strTime + "-01");
}
if (endTime != "")
{
DateTime endDate = DateTime.Parse(endTime + "-01");
strWhere += string.Format(" And KQDate <='{0}'", endDate.AddMonths(1));
}
}
}
if (P1 != "")
{
switch (P1)
{
case "1": //创建的
{
strWhere += " And KQUser ='******'";
}
break;
case "2": //下属签到
{
//获取当前登录人负责的下属人员
string Users = new JH_Auth_UserB().GetUserBranchUsers(UserInfo.User.ComId.Value, UserInfo.User.UserName).Select(d => d.UserName).ToList().ListTOString(',');
strWhere += string.Format(" and KQUser in ('{0}') ", Users.ToFormatLike());
}
break;
}
}
string strsql = string.Format("SELECT isnull(sum(case when Status=1 then 1 else 0 end),0) CDCount, isnull(sum(case when Status=2 then 1 else 0 end),0) ZTCount FROM SZHL_KQJL where DATEPART(MONTH,KQDate)= DATEPART(MONTH,getdate()) and ComId={0} and KQUser='******'", UserInfo.User.ComId, UserInfo.User.UserName);
DataTable dtTJ = new SZHL_KQJLB().GetDTByCommand(strsql);
dtTJ.Columns.Add("QJCount");
var intProD = new Yan_WF_PIB().GetYSHUserPI(UserInfo.User.UserName, UserInfo.User.ComId.Value, "CCXJ").Select(d => d.ID.ToString()).ToList();
string strSql1 = string.Format("SELECT isnull(SUM(Daycount),0) from SZHL_CCXJ where intProcessStanceid in ({0}) and DATEPART(MONTH,CRDate)= DATEPART(MONTH,getdate()) and CRUser='******'", intProD.ListTOString(',') == "" ? "0" : intProD.ListTOString(','), UserInfo.User.UserName);
object obj = new SZHL_KQJLB().ExsSclarSql(strSql1);
dtTJ.Rows[0]["QJCount"] = obj.ToString();
DataTable dt = new SZHL_KQBCB().GetDataPager(" SZHL_KQJL", "* ", pagecount, page, "KQDate desc", strWhere + " and Type=0", ref recordCount);
dt.Columns.Add("QTDate");
dt.Columns.Add("QTStatus");
dt.Columns.Add("QTPosition");
foreach (DataRow row in dt.Rows)
{
string strSql2 = string.Format("SELECT top 1 * from SZHL_KQJL where ComId={0} and KQUser='******' AND DATEDIFF(DAY,KQDate,'{2}')=0 and Type=1 ORDER by KQDate DESC", UserInfo.User.ComId, row["KQUser"], row["KQDate"]);
DataTable dtQT = new SZHL_KQBCB().GetDTByCommand(strSql2);
if (dtQT.Rows.Count > 0)
{
row["QTDate"] = dtQT.Rows[0]["KQDate"];
row["QTStatus"] = dtQT.Rows[0]["Status"];
row["QTPosition"] = dtQT.Rows[0]["Position"];
}
}
msg.Result = dt;
msg.Result1 = recordCount;
msg.Result2 = dtTJ;
}
