/// <summary>
/// 更新密码
/// </summary>
/// <param name="intAdminID"></param>
/// <param name="strPassword"></param>
/// <returns></returns>
public int Edit(int intAdminID, string strPassword)
{
DbCommand dbCommandWrapper = null;
string strSQL = @"UPDATE [TB_SystemAdmin] SET [PassWord]=@PassWord
WHERE [ID]=@ID";
StringBuilder strBuilder = new StringBuilder(strSQL);
try
{
Database db = DBO.GetInstance();
dbCommandWrapper = db.DbProviderFactory.CreateCommand();
dbCommandWrapper.CommandType = CommandType.Text;
db.AddInParameter(dbCommandWrapper, "@ID", DbType.Int32, intAdminID);
db.AddInParameter(dbCommandWrapper, "@PassWord", DbType.String, strPassword);
dbCommandWrapper.CommandText = strBuilder.ToString();
return(db.ExecuteNonQuery(dbCommandWrapper));
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (dbCommandWrapper != null)
{
dbCommandWrapper = null;
}
}
}
/// <summary>
/// 获取到DayDormInfo
/// </summary>
/// <param name="siteID"></param>
/// <returns></returns>
public DataTable GetDayDormInfoBySiteID(int siteID, string startDay, string endDay, string dataType)
{
DataTable dt = null;
DbCommand dbCommandWrapper = null;
try
{
string viewTableName = "TB_DayDormReport";
StringBuilder strBuilder = new StringBuilder(@"exec Proc_GetDayDormInfo '" + viewTableName + "'," + "N'''" + startDay + "'''," + "N'''" + endDay + "'''," + "'" + dataType + "'");
Database db = DBO.GetInstance();
dbCommandWrapper = db.DbProviderFactory.CreateCommand();
dbCommandWrapper.CommandType = CommandType.Text;
//strBuilder.AppendLine(" AND A.SiteID = @SiteID");
//db.AddInParameter(dbCommandWrapper, "@SiteID", DbType.Int32, 2);
dbCommandWrapper.CommandText = strBuilder.ToString();
dt = db.ExecuteDataSet(dbCommandWrapper).Tables[0];
return(dt);
}
catch (Exception ex)
{
return(dt);
}
finally
{
if (dbCommandWrapper != null)
{
dbCommandWrapper = null;
}
}
}
public bool DelTepm(string operatorUser)
{
DbCommand dbCommandWrapper = null;
try
{
Database db = DBO.GetInstance();
string strDelTemp = @"delete from TB_DayRepotTemp where CreateUser='******'";
dbCommandWrapper = db.GetSqlStringCommand(strDelTemp);
db.ExecuteNonQuery(dbCommandWrapper);
return(true);
}
catch (Exception ex)
{
return(false);
throw ex;
}
finally
{
if (dbCommandWrapper != null)
{
dbCommandWrapper = null;
}
}
}
/// <summary>
/// 获取到一个Site的所有宿舍区
/// </summary>
/// <param name="siteID"></param>
/// <returns></returns>
public DataTable GetTableBySite(int siteID)
{
DataTable dt = null;
DbCommand dbCommandWrapper = null;
try
{
string strSQL = @"select ID
,[SiteID]
,[Name]
,[Creator] from TB_AllowanceApply where 1=1";
StringBuilder strBuilder = new StringBuilder(strSQL);
Database db = DBO.GetInstance();
dbCommandWrapper = db.DbProviderFactory.CreateCommand();
dbCommandWrapper.CommandType = CommandType.Text;
strBuilder.AppendLine(" AND SiteID = @SiteID");
db.AddInParameter(dbCommandWrapper, "@SiteID", DbType.Int32, siteID);
dbCommandWrapper.CommandText = strBuilder.ToString();
dt = db.ExecuteDataSet(dbCommandWrapper).Tables[0];
return(dt);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (dbCommandWrapper != null)
{
dbCommandWrapper = null;
}
}
}
/// <summary>
/// 获取到DormInfo
/// </summary>
/// <param name="siteID"></param>
/// <returns></returns>
public DataTable GetDormInfoBySiteID(int siteID)
{
DataTable dt = null;
DbCommand dbCommandWrapper = null;
try
{
StringBuilder strBuilder = new StringBuilder(@"select * from view_CurrentDormReport");
Database db = DBO.GetInstance();
dbCommandWrapper = db.DbProviderFactory.CreateCommand();
dbCommandWrapper.CommandType = CommandType.Text;
//strBuilder.AppendLine(" AND A.SiteID = @SiteID");
//db.AddInParameter(dbCommandWrapper, "@SiteID", DbType.Int32, 2);
dbCommandWrapper.CommandText = strBuilder.ToString();
dt = db.ExecuteDataSet(dbCommandWrapper).Tables[0];
DataRow[] dr = dt.Select("IsNull([grade], ' ') <> ' '", "areaname,grade,DormNo ");
DataTable dtReturn = dt.Clone();
for (int i = 0; i < dr.Length; i++)
{
dtReturn.ImportRow(dr[i]);
}
return(dtReturn);
}
catch (Exception ex)
{
return(null);
}
finally
{
if (dbCommandWrapper != null)
{
dbCommandWrapper = null;
}
}
}
/// <summary>
/// 根据用户ID获取到用户信息
/// </summary>
/// <param name="intUserID"></param>
/// <returns></returns>
public TB_User GetUserInfo(int intUserID)
{
DataTable dt = null;
DbCommand dbCommandWrapper = null;
try
{
string strSQL = @"SELECT * FROM [TB_User] WHERE 1=1";
StringBuilder strBuilder = new StringBuilder(strSQL);
Database db = DBO.GetInstance();
dbCommandWrapper = db.DbProviderFactory.CreateCommand();
dbCommandWrapper.CommandType = CommandType.Text;
strBuilder.AppendLine(" AND ID = @ID");
db.AddInParameter(dbCommandWrapper, "@ID", DbType.Int32, intUserID);
dbCommandWrapper.CommandText = strBuilder.ToString();
dt = db.ExecuteDataSet(dbCommandWrapper).Tables[0];
return(this.ConvertTableToList(dt).FirstOrDefault());
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (dbCommandWrapper != null)
{
dbCommandWrapper = null;
}
}
}
public DataTable Get(int siteID, string name)
{
DataTable dt = null;
DbCommand dbCommandWrapper = null;
try
{
string strSQL = @"SELECT top 1 ID FROM TB_BU WHERE SiteID=" + siteID + " and Name= '" + name + "' ";
StringBuilder strBuilder = new StringBuilder(strSQL);
Database db = DBO.GetInstance();
dbCommandWrapper = db.DbProviderFactory.CreateCommand();
dbCommandWrapper.CommandType = CommandType.Text;
dbCommandWrapper.CommandText = strBuilder.ToString();
dt = db.ExecuteDataSet(dbCommandWrapper).Tables[0];
return(dt);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (dbCommandWrapper != null)
{
dbCommandWrapper = null;
}
}
}
/// <summary>
/// 批量启用/禁用
/// </summary>
/// <param name="ids"></param>
/// <param name="bEnable"></param>
public int ChangeBedEnable(List <int> ids, bool bEnable)
{
DbCommand dbCommandWrapper = null;
try
{
string strSQL = string.Format(@"update TB_Bed set IsEnable=@value WHERE 1=1 ");
StringBuilder strBuilder = new StringBuilder(strSQL);
Database db = DBO.GetInstance();
dbCommandWrapper = db.DbProviderFactory.CreateCommand();
dbCommandWrapper.CommandType = CommandType.Text;
var sids = string.Join(",", ids);
strBuilder.AppendFormat(" AND ID in ({0})", sids);
strBuilder.AppendLine();
string uValue = bEnable ? "已启用" : "已禁用";
db.AddInParameter(dbCommandWrapper, "@value", DbType.String, uValue);
dbCommandWrapper.CommandText = strBuilder.ToString();
return(db.ExecuteNonQuery(dbCommandWrapper));
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (dbCommandWrapper != null)
{
dbCommandWrapper = null;
}
}
}
public bool ChangeBegStatus(int ID)
{
DbCommand dbCommandWrapper = null;
try
{
string strSQL = string.Format(@"select status from TB_Bed WHERE 1=1 ");
StringBuilder strBuilder = new StringBuilder(strSQL);
Database db = DBO.GetInstance();
dbCommandWrapper = db.DbProviderFactory.CreateCommand();
dbCommandWrapper.CommandType = CommandType.Text;
strBuilder.AppendLine(" AND id = @id");
db.AddInParameter(dbCommandWrapper, "@ID", DbType.Int32, ID);
dbCommandWrapper.CommandText = strBuilder.ToString();
DataSet ds = db.ExecuteDataSet(dbCommandWrapper);
return(ds.Tables[0].Rows[0][0].ToString() == "1" ? true : false);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (dbCommandWrapper != null)
{
dbCommandWrapper = null;
}
}
}
public DataTable GetRepairDormList(TB_DormRepair mItem)
{
var sb = new StringBuilder(@"select ID,CName,EmployeeNo,MobileNo,DormAddress,RepairTime,DeviceType,RequireDesc,CreateDate,Status,Response,ModifyUserID,ModifyDate
from TB_DormRepair ");
var db = DBO.GetInstance();
DataTable dt = null;
DbCommand dbCommandWrapper = null;
dbCommandWrapper = db.DbProviderFactory.CreateCommand();
dbCommandWrapper.CommandType = CommandType.Text;
sb.Append("where 1=1 ");
if (mItem.ID > 0)
{
sb.AppendFormat("and id={0} ", mItem.ID);
}
if (!string.IsNullOrEmpty(mItem.DeviceType))
{
sb.AppendFormat("and DeviceType='{0}' ", mItem.DeviceType);
}
if (mItem.Status > -1)
{
sb.AppendFormat("and Status='{0}' ", mItem.Status);
}
if (mItem.SubmitDayBegin != default(DateTime))
{
sb.AppendFormat(" AND CAST(CreateDate as Date) between '{0:yyyy/MM/dd}' and '{1:yyyy/MM/dd}'",
mItem.SubmitDayBegin, mItem.SubmitDayEnd);
}
sb.Append(" order by CreateDate desc, status");
dbCommandWrapper.CommandText = sb.ToString();
dt = db.ExecuteDataSet(dbCommandWrapper).Tables[0];
return(dt);
}
public DataTable GetDormSuggestList(TB_DormSuggest mItem)
{
var sb = new StringBuilder("select * from TB_DormSuggest ");
var db = DBO.GetInstance();
DataTable dt = null;
DbCommand dbCommandWrapper = null;
dbCommandWrapper = db.DbProviderFactory.CreateCommand();
dbCommandWrapper.CommandType = CommandType.Text;
sb.Append("where 1=1 ");
if (mItem.ID > 0)
{
sb.AppendFormat("and id={0} ", mItem.ID);
}
if (mItem.Status > -1)
{
sb.AppendFormat("and Status='{0}' ", mItem.Status);
}
if (mItem.SubmitDayBegin != default(DateTime))
{
sb.AppendFormat(" AND CAST(CreateDate as Date) between '{0:yyyy/MM/dd}' and '{1:yyyy/MM/dd}'",
mItem.SubmitDayBegin, mItem.SubmitDayEnd);
}
sb.Append(" order by CreateDate desc, status");
dbCommandWrapper.CommandText = sb.ToString();
dt = db.ExecuteDataSet(dbCommandWrapper).Tables[0];
return(dt);
}
public DataTable GetDormNotice(Pager pager)
{
var sb = new StringBuilder("select * from V_TB_DormNotice ");
var db = DBO.GetInstance();
DataTable dt = null;
DbCommand dbCommandWrapper = null;
dbCommandWrapper = db.DbProviderFactory.CreateCommand();
dbCommandWrapper.CommandType = CommandType.Text;
sb.Append("where 1=1 ");
if (pager != null && !pager.IsNull)
{
dbCommandWrapper.CommandText = pager.GetPagerSql4Count(sb.ToString());
dt = db.ExecuteDataSet(dbCommandWrapper).Tables[0];
pager.TotalRecord = Convert.ToInt32(dt.Rows[0][0]);
dbCommandWrapper.CommandText = pager.GetPagerSql4Data(sb.ToString(), DataBaseTypeEnum.sqlserver);
}
else
{
dbCommandWrapper.CommandText = sb.ToString();
}
dt = db.ExecuteDataSet(dbCommandWrapper).Tables[0];
return(dt);
}
public DataSet GetTable(TB_Module tb_Module)
{
DbCommand dbCommandWrapper = null;
try
{
string strSQL = @"SELECT * FROM [TB_Module] where 1=1";
StringBuilder strBuilder = new StringBuilder(strSQL);
Database db = DBO.GetInstance();
dbCommandWrapper = db.DbProviderFactory.CreateCommand();
dbCommandWrapper.CommandType = CommandType.Text;
strBuilder.AppendLine(" AND SiteID = @SiteID");
db.AddInParameter(dbCommandWrapper, "@SiteID", DbType.Int32, tb_Module.SiteID);
strBuilder.AppendLine(" AND [IsActive] = @IsActive");
db.AddInParameter(dbCommandWrapper, "@IsActive", DbType.Int32, tb_Module.IsActive);
dbCommandWrapper.CommandText = strBuilder.ToString();
return(db.ExecuteDataSet(dbCommandWrapper));
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (dbCommandWrapper != null)
{
dbCommandWrapper = null;
}
}
}
public DataSet GetModule(int intRoleID)
{
DbCommand dbCommandWrapper = null;
try
{
string strSQL = @"SELECT A.* FROM [TB_Module] AS A
INNER join [TB_RoleConnectModule] as B
on A.ID=B.[ModuleID]
where 1=1";
StringBuilder strBuilder = new StringBuilder(strSQL);
Database db = DBO.GetInstance();
dbCommandWrapper = db.DbProviderFactory.CreateCommand();
dbCommandWrapper.CommandType = CommandType.Text;
strBuilder.AppendLine(" AND B.RoleID = @RoleID");
db.AddInParameter(dbCommandWrapper, "@RoleID", DbType.Int32, intRoleID);
dbCommandWrapper.CommandText = strBuilder.ToString();
return(db.ExecuteDataSet(dbCommandWrapper));
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (dbCommandWrapper != null)
{
dbCommandWrapper = null;
}
}
}
public int AddAssignDormInfo(TB_AssignDormArea info)
{
Database db = DBO.GetInstance();
DbCommand dbCommandWrapper = null;
int intId;
string strInsertSql = @"INSERT INTO [TB_AssignDormArea] ([DormAreaID]
,[CardNo],[EmployeeNo],[CreateUser],[CreateDate]) VALUES(@DormAreaID,@CardNo,@EmployeeNo,@CreateUser,@CreateDate)";
string strSelectIdSql = ";SELECT SCOPE_IDENTITY()";
try
{
dbCommandWrapper = db.GetSqlStringCommand(strInsertSql + strSelectIdSql);
#region Add parameters
db.AddInParameter(dbCommandWrapper, "@DormAreaID", DbType.Int32, info.DormAreaID);
db.AddInParameter(dbCommandWrapper, "@CardNo", DbType.String, info.CardNo);
db.AddInParameter(dbCommandWrapper, "@EmployeeNo", DbType.String, info.EmployeeNo);
db.AddInParameter(dbCommandWrapper, "@CreateUser", DbType.String, info.CreateUser);
db.AddInParameter(dbCommandWrapper, "@CreateDate", DbType.DateTime, info.CreateDate);
#endregion
intId = Convert.ToInt32(db.ExecuteScalar(dbCommandWrapper));
return(intId);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (dbCommandWrapper != null)
{
dbCommandWrapper = null;
}
}
}
public int ChangeBegEnable(int ID, string value)
{
DbCommand dbCommandWrapper = null;
try
{
string strSQL = string.Format(@"update TB_Bed set IsEnable=@value WHERE 1=1 ");
StringBuilder strBuilder = new StringBuilder(strSQL);
Database db = DBO.GetInstance();
dbCommandWrapper = db.DbProviderFactory.CreateCommand();
dbCommandWrapper.CommandType = CommandType.Text;
strBuilder.AppendLine(" AND id = @id");
db.AddInParameter(dbCommandWrapper, "@ID", DbType.Int32, ID);
string uValue = value == "已禁用" ? "已启用" : "已禁用";
db.AddInParameter(dbCommandWrapper, "@value", DbType.String, uValue);
dbCommandWrapper.CommandText = strBuilder.ToString();
return(db.ExecuteNonQuery(dbCommandWrapper));
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (dbCommandWrapper != null)
{
dbCommandWrapper = null;
}
}
}
/// <summary>
///
/// </summary>
/// <param name="intUserID"></param>
/// <returns></returns>
public DataTable GetTable(int intUserID)
{
DataTable dt = null;
DbCommand dbCommandWrapper = null;
try
{
string strSQL = @"SELECT A.*
FROM TB_AllowanceApplyCancel AS A
INNER JOIN [TB_UserConnectDormArea] AS B
ON A.ID=B.[DormAreaID]
WHERE B.[UserID]=@UserID";
StringBuilder strBuilder = new StringBuilder(strSQL);
Database db = DBO.GetInstance();
dbCommandWrapper = db.DbProviderFactory.CreateCommand();
dbCommandWrapper.CommandType = CommandType.Text;
db.AddInParameter(dbCommandWrapper, "@UserID", DbType.Int32, intUserID);
dbCommandWrapper.CommandText = strBuilder.ToString();
dt = db.ExecuteDataSet(dbCommandWrapper).Tables[0];
return(dt);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (dbCommandWrapper != null)
{
dbCommandWrapper = null;
}
}
}
/// <summary>
/// 获取site的所有单元信息
/// </summary>
/// <param name="siteID"></param>
/// <returns></returns>
public DataTable GetTableBySite(int siteID)
{
DataTable dt = null;
DbCommand dbCommandWrapper = null;
try
{
StringBuilder strBuilder = new StringBuilder(@"SELECT A.*,B.Name AS BuildingName,C.Name AS DormAreaName
FROM [TB_Unit] as A
inner join TB_Building As B
on A.BuildingID=B.ID
inner join TB_DormArea AS C
on A.DormAreaID=C.ID");
Database db = DBO.GetInstance();
dbCommandWrapper = db.DbProviderFactory.CreateCommand();
dbCommandWrapper.CommandType = CommandType.Text;
strBuilder.AppendLine(" AND A.SiteID = @SiteID");
db.AddInParameter(dbCommandWrapper, "@SiteID", DbType.Int32, siteID);
dbCommandWrapper.CommandText = strBuilder.ToString();
dt = db.ExecuteDataSet(dbCommandWrapper).Tables[0];
return(dt);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (dbCommandWrapper != null)
{
dbCommandWrapper = null;
}
}
}
public DataTable GetTable(int intSiteID)
{
DataTable dt = null;
DbCommand dbCommandWrapper = null;
try
{
string strSQL = @"SELECT * FROM TB_BU WHERE 1=1 ";
StringBuilder strBuilder = new StringBuilder(strSQL);
Database db = DBO.GetInstance();
dbCommandWrapper = db.DbProviderFactory.CreateCommand();
dbCommandWrapper.CommandType = CommandType.Text;
strBuilder.AppendLine(" AND SiteID = @SiteID");
db.AddInParameter(dbCommandWrapper, "@SiteID", DbType.Int32, intSiteID);
dbCommandWrapper.CommandText = strBuilder.ToString();
dt = db.ExecuteDataSet(dbCommandWrapper).Tables[0];
return(dt);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (dbCommandWrapper != null)
{
dbCommandWrapper = null;
}
}
}
/// <summary>
/// 获取到一个site的所有入住人员信息
/// </summary>
/// <param name="intSiteID"></param>
/// <returns></returns>
public DataTable GetTableBySiteID(int intSiteID)
{
DataTable dt = null;
DbCommand dbCommandWrapper = null;
try
{
string strSQL = @" SELECT A.ID,A.CardNo,A.IsActive,EmployeeNo
from [TB_EmployeeCheckIn] as A where A.[SiteID]=@SiteID" ;
StringBuilder strBuilder = new StringBuilder(strSQL);
Database db = DBO.GetInstance();
dbCommandWrapper = db.DbProviderFactory.CreateCommand();
dbCommandWrapper.CommandType = CommandType.Text;
db.AddInParameter(dbCommandWrapper, "@SiteID", DbType.String, intSiteID);
dbCommandWrapper.CommandText = strBuilder.ToString();
dt = db.ExecuteDataSet(dbCommandWrapper).Tables[0];
return(dt);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (dbCommandWrapper != null)
{
dbCommandWrapper.Dispose();
dbCommandWrapper = null;
}
}
}
/// <summary>
///
/// </summary>
/// <param name="intUserID"></param>
/// <returns></returns>
public DataTable GetTable(string EmployeeNo)
{
DataTable dt = null;
DbCommand dbCommandWrapper = null;
try
{
string strSQL = @"SELECT A.*
FROM TB_AllowanceApply AS A
WHERE A.[EmployeeNo]=@EmployeeNo";
StringBuilder strBuilder = new StringBuilder(strSQL);
Database db = DBO.GetInstance();
dbCommandWrapper = db.DbProviderFactory.CreateCommand();
dbCommandWrapper.CommandType = CommandType.Text;
db.AddInParameter(dbCommandWrapper, "@EmployeeNo", DbType.String, EmployeeNo);
dbCommandWrapper.CommandText = strBuilder.ToString();
dt = db.ExecuteDataSet(dbCommandWrapper).Tables[0];
return(dt);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (dbCommandWrapper != null)
{
dbCommandWrapper = null;
}
}
}
/// <summary>
/// 根据ID查询入住记录
/// </summary>
/// <param name="intID"></param>
/// <returns></returns>
public DataTable Get(int intID)
{
DataTable dt = null;
DbCommand dbCommandWrapper = null;
try
{
string strSQL = @" SELECT A.*
from [TB_EmployeeCheckIn] as A where A.ID=@ID" ;
StringBuilder strBuilder = new StringBuilder(strSQL);
Database db = DBO.GetInstance();
dbCommandWrapper = db.DbProviderFactory.CreateCommand();
dbCommandWrapper.CommandType = CommandType.Text;
db.AddInParameter(dbCommandWrapper, "@ID", DbType.Int32, intID);
strBuilder.AppendLine(" AND A.IsActive = @IsActive");
db.AddInParameter(dbCommandWrapper, "@IsActive", DbType.Int32, (int)TypeManager.IsActive.Valid);
dbCommandWrapper.CommandText = strBuilder.ToString();
dt = db.ExecuteDataSet(dbCommandWrapper).Tables[0];
return(dt);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (dbCommandWrapper != null)
{
dbCommandWrapper.Dispose();
dbCommandWrapper = null;
}
}
}
public DataTable GetTableMonthByEmployeeNo(string EmployeeNo)
{
DataTable dt = null;
DbCommand dbCommandWrapper = null;
try
{
string strSQL = @"SELECT * from [TB_Charging] as A where 1=1";
StringBuilder strBuilder = new StringBuilder(strSQL);
Database db = DBO.GetInstance();
dbCommandWrapper = db.DbProviderFactory.CreateCommand();
dbCommandWrapper.CommandType = CommandType.Text;
strBuilder.AppendLine(" AND A.EmployeeNo = @EmployeeNo And DATEDIFF(MM,A.CreateTime,GETDATE())=0");
db.AddInParameter(dbCommandWrapper, "@EmployeeNo", DbType.String, EmployeeNo);
dbCommandWrapper.CommandText = strBuilder.ToString();
dt = db.ExecuteDataSet(dbCommandWrapper).Tables[0];
return(dt);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (dbCommandWrapper != null)
{
dbCommandWrapper = null;
}
}
}
public DataTable GetTable(TB_AssignRoom info)
{
DataTable dt = null;
DbCommand dbCommandWrapper = null;
try
{
string strSQL = @"select [ID]
,[RoomID]
,[isActive]
,[BedID]
,[SiteID] from [TB_AssignRoom] where 1=1";
StringBuilder strBuilder = new StringBuilder(strSQL);
Database db = DBO.GetInstance();
dbCommandWrapper = db.DbProviderFactory.CreateCommand();
dbCommandWrapper.CommandType = CommandType.Text;
strBuilder.AppendLine(" AND SiteID = @SiteID");
db.AddInParameter(dbCommandWrapper, "@SiteID", DbType.Int32, info.SiteID);
dbCommandWrapper.CommandText = strBuilder.ToString();
dt = db.ExecuteDataSet(dbCommandWrapper).Tables[0];
return(dt);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (dbCommandWrapper != null)
{
dbCommandWrapper = null;
}
}
}
/// <summary>
/// 获取日期的连续数据
/// </summary>
/// <param name="siteID"></param>
/// <returns></returns>
public DataTable GetALLCheckInByDate(string strDay)
{
DataTable dt = null;
DbCommand dbCommandWrapper = null;
try
{
StringBuilder strBuilder = new StringBuilder(@"SELECT COUNT(1) COUNT from TB_EmployeeCheckIn where CheckInDate<='" + strDay + " 23:59:59" + "'");
Database db = DBO.GetInstance();
dbCommandWrapper = db.DbProviderFactory.CreateCommand();
dbCommandWrapper.CommandType = CommandType.Text;
//strBuilder.AppendLine(" AND A.SiteID = @SiteID");
//db.AddInParameter(dbCommandWrapper, "@SiteID", DbType.Int32, 2);
dbCommandWrapper.CommandText = strBuilder.ToString();
dt = db.ExecuteDataSet(dbCommandWrapper).Tables[0];
return(dt);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (dbCommandWrapper != null)
{
dbCommandWrapper = null;
}
}
}
public DataTable GetAssignDormInfo(string IDCardNo)
{
DataTable dt = null;
DbCommand dbCommandWrapper = null;
try
{
StringBuilder strBuilder = new StringBuilder(@"select * from TB_AssignDormArea where ");
Database db = DBO.GetInstance();
dbCommandWrapper = db.DbProviderFactory.CreateCommand();
dbCommandWrapper.CommandType = CommandType.Text;
strBuilder.AppendLine(" CardNo=@IDCardNo ");
db.AddInParameter(dbCommandWrapper, "@IDCardNo", DbType.String, IDCardNo);
dbCommandWrapper.CommandText = strBuilder.ToString();
dt = db.ExecuteDataSet(dbCommandWrapper).Tables[0];
return(dt);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (dbCommandWrapper != null)
{
dbCommandWrapper = null;
}
}
}
/// <summary>
/// 获取到DayDormInfo
/// </summary>
/// <param name="siteID"></param>
/// <returns></returns>
public DataTable GetMonthDormInfoBySiteID(int siteID, string startDay, string projectName, string dataType)
{
DataTable dt = null;
DbCommand dbCommandWrapper = null;
try
{
startDay = startDay + "-01 00:00:00";
StringBuilder strBuilder = new StringBuilder(@"Select Areaname,Roomtypt,'" + projectName + @"' project,cast(AVG(" + dataType + @") as decimal(18,1)) 平均,MAX(" + dataType + @") 最大,MIN(" + dataType + @") 最小 From TB_DayDormReport
Where YEAR(Createdate)= YEAR('" + startDay + @"') and Month([Createdate]) = Month('" + startDay + @"')
GROUP BY Areaname,Roomtypt");
Database db = DBO.GetInstance();
dbCommandWrapper = db.DbProviderFactory.CreateCommand();
dbCommandWrapper.CommandType = CommandType.Text;
//strBuilder.AppendLine(" AND A.SiteID = @SiteID");
//db.AddInParameter(dbCommandWrapper, "@SiteID", DbType.Int32, 2);
dbCommandWrapper.CommandText = strBuilder.ToString();
dt = db.ExecuteDataSet(dbCommandWrapper).Tables[0];
return(dt);
}
catch (Exception ex)
{
return(dt);
}
finally
{
if (dbCommandWrapper != null)
{
dbCommandWrapper = null;
}
}
}
public int DelAssignDormInfo(string IDCardNo)
{
DbCommand dbCommandWrapper = null;
try
{
StringBuilder strBuilder = new StringBuilder(@"Delete from TB_AssignDormArea where ");
Database db = DBO.GetInstance();
dbCommandWrapper = db.DbProviderFactory.CreateCommand();
dbCommandWrapper.CommandType = CommandType.Text;
strBuilder.AppendLine(" CardNo=@IDCardNo ");
db.AddInParameter(dbCommandWrapper, "@IDCardNo", DbType.String, IDCardNo);
dbCommandWrapper.CommandText = strBuilder.ToString();
int i = db.ExecuteNonQuery(dbCommandWrapper);
return(i);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (dbCommandWrapper != null)
{
dbCommandWrapper = null;
}
}
}
/// <summary>
/// BegCount
/// </summary>
/// <param name="siteID"></param>
/// <returns></returns>
public DataTable GetBegCountBySiteID(int siteID)
{
DataTable dt = null;
DbCommand dbCommandWrapper = null;
try
{
StringBuilder strBuilder = new StringBuilder(@"exec Proc_GetAllBegCount ");
Database db = DBO.GetInstance();
dbCommandWrapper = db.DbProviderFactory.CreateCommand();
dbCommandWrapper.CommandType = CommandType.Text;
//strBuilder.AppendLine(" AND A.SiteID = @SiteID");
//db.AddInParameter(dbCommandWrapper, "@SiteID", DbType.Int32, 2);
dbCommandWrapper.CommandText = strBuilder.ToString();
dt = db.ExecuteDataSet(dbCommandWrapper).Tables[0];
return(dt);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (dbCommandWrapper != null)
{
dbCommandWrapper = null;
}
}
}
/// <summary>
/// 获取到超级管理员信息
/// </summary>
/// <param name="strAccount"></param>
/// <param name="strPassWord"></param>
/// <returns></returns>
public TB_SystemAdmin GetUserInfo(string strAccount, string strPassWord)
{
DataTable dt = null;
DbCommand dbCommandWrapper = null;
try
{
string strSQL = @"select * from [TB_SystemAdmin] where 1=1";
StringBuilder strBuilder = new StringBuilder(strSQL);
Database db = DBO.GetInstance();
dbCommandWrapper = db.DbProviderFactory.CreateCommand();
dbCommandWrapper.CommandType = CommandType.Text;
strBuilder.AppendLine(" AND Account = @Account");
db.AddInParameter(dbCommandWrapper, "@Account", DbType.String, strAccount);
strBuilder.AppendLine(" AND PassWord = @PassWord");
db.AddInParameter(dbCommandWrapper, "@PassWord", DbType.String, strPassWord);
dbCommandWrapper.CommandText = strBuilder.ToString();
dt = db.ExecuteDataSet(dbCommandWrapper).Tables[0];
return(this.ConvertTableToList(dt).FirstOrDefault());
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (dbCommandWrapper != null)
{
dbCommandWrapper = null;
}
}
}