/// <summary>
/// 分页查询学生数据
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="mPhone"></param>
/// <returns></returns>
public IList <Hashtable> List(int pageIndex, int pageSize, string mPhone)
{
StringBuilder sql = new StringBuilder(@"WITH listtab AS (
SELECT g.Gid,s.Sname,s.Snumber,s.Sid,
--c.Cphone,c.Cphone2,c.Cphone3,c.Cphone4,
ROW_NUMBER() OVER (ORDER BY g.Gid ASC, s.Snumber ASC) AS req
FROM dbo.OA_Mechanical(NOLOCK) u
JOIN dbo.OA_Grade(NOLOCK) g on u.Gid = g.Gid
JOIN dbo.OA_Student(NOLOCK) s ON s.Gid = g.Gid
--JOIN dbo.OA_Contact(NOLOCK) c ON c.Sid = s.Sid
WHERE u.MechIMEI = @imei
)
SELECT Gid,Sname,Snumber,Sid FROM listtab
");
SqlParameter[] parameters =
{
new SqlParameter("@imei", SqlDbType.NVarChar, 512),
new SqlParameter("@begin", SqlDbType.Int, 4),
new SqlParameter("@end", SqlDbType.Int, 4)
};
parameters[0].Value = mPhone;
parameters[1].Value = (pageIndex - 1) * pageSize;
parameters[2].Value = pageIndex * pageSize;
if (pageIndex > 0 && pageSize > 0)
{
sql.AppendLine("WHERE req BETWEEN @begin AND @end");
}
//分页查询
return(DbHelperSQL.ExecuteReaderHashtable(sql.ToString(), parameters));
}
/// <summary>
/// 根据学校序列号和电话号码获取数据
/// </summary>
/// <returns></returns>
public Hashtable GetPhoneListBySchoolAndPhonenum(string sserie, string phonenum)
{
string sql = @"select distinct oc.Cphone,osc.SchoolSerie
from Daiv_OA..OA_Contact oc
join Daiv_OA..OA_Student os on oc.Sid = os.Sid
join Daiv_OA..OA_Grade og on os.Gid = og.Gid
join Daiv_OA..OA_SchoolGrade osg on osg.ID = og.GgradeID
join Daiv_OA..OA_School osc on osc.ID = osg.SchoolID
where oc.Cphone = @phone and osc.SchoolSerie =@ss and oc.IsDeleted = 0 and ISNULL(oc.Cphone,'') != ''
";
SqlParameter[] parameters =
{
new SqlParameter("@ss", SqlDbType.NVarChar, 128),
new SqlParameter("@phone", SqlDbType.NVarChar, 50)
};
parameters[0].Value = sserie;
parameters[1].Value = phonenum;
//查询结果
IList <Hashtable> resultList = DbHelperSQL.ExecuteReaderHashtable(sql, parameters);
if (resultList != null && resultList.Count > 0)
{
return(resultList[0]);
}
return(null);
}
/// <summary>
/// 获取更新年级学生数据
/// </summary>
/// <returns></returns>
public IList <Hashtable> getUpdateGradeData(string where)
{
string sql = string.Format(@"select stu.Sid,stu.Sname,stu.Snumber,guc.CurrGradeID,guc.UpGradeID
from Daiv_OA..OA_Student(nolock) stu
join Daiv_OA..OA_GradeUpdateConfig(nolock) guc on stu.Gid = guc.CurrGradeID
where guc.IsDeleted = 0 and stu.IsDeleted = 0 {0}", where);
SqlParameter[] parameters = {};
//查询
return(DbHelperSQL.ExecuteReaderHashtable(sql, parameters));
}
/// <summary>
/// 获取亲情号最大的修改时间
/// </summary>
/// <returns></returns>
public Hashtable GetMaxCreatTime()
{
string sql = @"
select MAX(CreatDate) as CreatDate from OA_Contact oc where oc.IsDeleted = 0 and ISNULL(oc.Cphone,'') != ''
";
SqlParameter[] parameters =
{
};
//分页查询
return(DbHelperSQL.ExecuteReaderHashtable(sql, parameters)[0]);
}
/// <summary>
/// 根据微信openid获取情亲号学生学号
/// </summary>
/// <param name="openId"></param>
/// <returns></returns>
public IList <Hashtable> GetFamilyNumberByOpenId(string openId)
{
string sql = @"select distinct os.snumber
from OA_WXUser u
join OA_Contact oc on u.WXUserPhone = oc.Cphone
join OA_Student os on os.Sid = oc.Sid
where OpenId =@openid and oc.IsDeleted = 0
";
SqlParameter[] parameters =
{
new SqlParameter("@openid", SqlDbType.NVarChar, 128)
};
parameters[0].Value = openId;
//分页查询
return(DbHelperSQL.ExecuteReaderHashtable(sql, parameters));
}
/// <summary>
/// 根据学校编号和时间获取亲情号
/// </summary>
/// <param name="openId"></param>
/// <returns></returns>
public IList <Hashtable> GetPhoneListBySchoolAndDate(string sserie, string datetime, string pageindex, string pagesize)
{
StringBuilder sqlPage = new StringBuilder();
if (!string.IsNullOrEmpty(pageindex) && !string.IsNullOrEmpty(pagesize))
{
try
{
int pIndex = Convert.ToInt32(pageindex);
int pSize = Convert.ToInt32(pagesize);
if (pIndex > 0 && pSize > 0)
{
sqlPage.Append(string.Format(" and req >{0} and req <={1}", pSize * (pIndex - 1), pSize * pIndex));
}
}
catch (Exception ex)
{
}
}
string sql = @"
declare @total int ;
declare @minDate datetime;
if object_id('tempdb..#temp') is not null begin drop table #temp end
select distinct oc.Cphone,oc.CreatDate,ROW_NUMBER() over(order by oc.Cphone) as req
into #temp
from Daiv_OA..OA_Contact oc
join Daiv_OA..OA_Student os on oc.Sid = os.Sid
join Daiv_OA..OA_Grade og on os.Gid = og.Gid
join Daiv_OA..OA_SchoolGrade osg on osg.ID = og.GgradeID
join Daiv_OA..OA_School osc on osc.ID = osg.SchoolID
where osc.SchoolSerie = @ss and oc.IsDeleted = 0 and ISNULL(oc.Cphone,'') != ''
select @total= COUNT(1) from #temp
select @minDate=MAX(CreatDate) from #temp
select *,@total as Total ,@minDate as MaxDate from #temp where 1=1 " + sqlPage.ToString();
SqlParameter[] parameters =
{
new SqlParameter("@ss", SqlDbType.NVarChar, 128),
new SqlParameter("@dt", SqlDbType.DateTime)
};
parameters[0].Value = sserie;
parameters[1].Value = datetime;
//分页查询
return(DbHelperSQL.ExecuteReaderHashtable(sql, parameters));
}
/// <summary>
/// 分页查询数据
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="mPhone"></param>
/// <returns></returns>
public IList <Hashtable> List(int pageIndex, int pageSize)
{
string sql = @"WITH listtab AS (
SELECT Gid,GgradeName,GgradeID,Mphone,Gname,Gdescription,
ROW_NUMBER() OVER (ORDER BY Gid ASC) AS req
FROM dbo.OA_Grade
)
SELECT * FROM listtab
WHERE req BETWEEN @begin AND @end";
SqlParameter[] parameters =
{
new SqlParameter("@begin", SqlDbType.Int, 4),
new SqlParameter("@end", SqlDbType.Int, 4)
};
parameters[0].Value = (pageIndex - 1) * pageSize;
parameters[1].Value = pageIndex * pageSize;
//分页查询
return(DbHelperSQL.ExecuteReaderHashtable(sql, parameters));
}
/// <summary>
/// 根据电话号码查询学生数据(弃用)
/// </summary>
/// <param name="mPhone"></param>
/// <returns></returns>
public IList <Hashtable> List(string mPhone)
{
string sql = @"WITH listtab AS (
SELECT g.Gid,s.Sname,s.Snumber,c.Cphone,c.Cphone2,c.Cphone3,c.Cphone4,
ROW_NUMBER() OVER (ORDER BY g.Gid ASC, s.Snumber ASC) AS req
FROM dbo.OA_User(NOLOCK) u
JOIN dbo.OA_Grade(NOLOCK) g on u.UClassID = g.Gid
JOIN dbo.OA_Student(NOLOCK) s ON s.Gid = g.Gid
JOIN dbo.OA_Contact(NOLOCK) c ON c.Sid = s.Sid
WHERE u.Mphone = @mPhone
)
SELECT Gid,Sname,Snumber,Cphone,Cphone2,Cphone3,Cphone4 FROM listtab
";
SqlParameter[] parameters =
{
new SqlParameter("@mPhone", SqlDbType.VarChar, 50)
};
parameters[0].Value = mPhone;
//分页查询
return(DbHelperSQL.ExecuteReaderHashtable(sql, parameters));
}
/// <summary>
/// 查询微信留言
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="mPhone"></param>
/// <returns></returns>
public IList <Hashtable> WXList(string imei, string datetime)
{
StringBuilder sql = new StringBuilder(@"
select om.Mtitle as 'phone' ,om.Content as 'contact', 1 as 'wxmsg' ,os.Snumber as snumber,CONVERT(varchar(100), GETDATE(), 120) as asynctime
from Daiv_OA..OA_message om
join Daiv_OA..oa_student os on om.ToUid = os.Sid
join Daiv_OA..OA_Mechanical ome on ome.Gid = os.Gid
where ome.MechIMEI = @imei and om.AddTime >@begin
");
SqlParameter[] parameters =
{
new SqlParameter("@imei", SqlDbType.NVarChar, 512),
new SqlParameter("@begin", SqlDbType.DateTime)
};
parameters[0].Value = imei;
parameters[1].Value = datetime;
if (string.IsNullOrEmpty(datetime))
{
parameters[1].Value = "2019-01-01 00:00:00";
}
//分页查询
return(DbHelperSQL.ExecuteReaderHashtable(sql.ToString(), parameters));
}
