/// <summary>
/// Add a record
/// </summary>
public int Insert(UsersEntity model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into Users(");
strSql.Append("CompanyID,RoleID,FirstName,LastName,UserName,Email,PassWord,Title,Phone,EmergencyContactFirstName,EmergencyContactLastName")
.Append(",EmergencyContactPhone,EmergencyContactEmail,MaintenancePlanOption")
.Append(",CreatedOn,AccountStatus,ForgotPassword,IsDelete,Status,UserType,Skype,Office,PTOHoursOfYear)");
strSql.Append(" values (");
strSql.Append("@CompanyID,@RoleID,@FirstName,@LastName,@UserName,@Email,@PassWord,@Title,@Phone,@EmergencyContactFirstName,@EmergencyContactLastName")
.Append(",@EmergencyContactPhone,@EmergencyContactEmail,@MaintenancePlanOption")
.Append(",@CreatedOn,@AccountStatus,@ForgotPassword,@IsDelete,@Status,@UserType,@Skype,@Office,@PTOHoursOfYear)");
strSql.Append(";select ISNULL( SCOPE_IDENTITY(),0);");
Database db = DatabaseFactory.CreateDatabase();
using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString()))
{
try
{
db.AddInParameter(dbCommand, "CompanyID", DbType.Int32, model.CompanyID);
db.AddInParameter(dbCommand, "RoleID", DbType.Int32, model.RoleID);
db.AddInParameter(dbCommand, "FirstName", DbType.String, model.FirstName);
db.AddInParameter(dbCommand, "LastName", DbType.String, model.LastName);
db.AddInParameter(dbCommand, "UserName", DbType.String, model.UserName);
db.AddInParameter(dbCommand, "Email", DbType.String, model.Email);
db.AddInParameter(dbCommand, "PassWord", DbType.String, model.PassWord);
db.AddInParameter(dbCommand, "Title", DbType.String, model.Title);
db.AddInParameter(dbCommand, "Phone", DbType.String, model.Phone);
db.AddInParameter(dbCommand, "EmergencyContactFirstName", DbType.String, model.EmergencyContactFirstName);
db.AddInParameter(dbCommand, "EmergencyContactLastName", DbType.String, model.EmergencyContactLastName);
db.AddInParameter(dbCommand, "EmergencyContactPhone", DbType.String, model.EmergencyContactPhone);
db.AddInParameter(dbCommand, "EmergencyContactEmail", DbType.String, model.EmergencyContactEmail);
db.AddInParameter(dbCommand, "MaintenancePlanOption", DbType.String, model.MaintenancePlanOption);
db.AddInParameter(dbCommand, "CreatedOn", DbType.DateTime, model.CreatedOn);
db.AddInParameter(dbCommand, "AccountStatus", DbType.Int32, model.AccountStatus);
db.AddInParameter(dbCommand, "ForgotPassword", DbType.Int32, model.ForgotPassword);
db.AddInParameter(dbCommand, "IsDelete", DbType.Boolean, model.IsDelete);
db.AddInParameter(dbCommand, "Status", DbType.String, model.Status);
db.AddInParameter(dbCommand, "UserType", DbType.String, model.UserType);
db.AddInParameter(dbCommand, "Skype", DbType.String, model.Skype);
db.AddInParameter(dbCommand, "Office", DbType.String, model.Office);
db.AddInParameter(dbCommand, "PTOHoursOfYear", DbType.Double, model.PTOHoursOfYear);
int result;
object obj = db.ExecuteScalar(dbCommand);
if (!int.TryParse(obj.ToString(), out result))
{
return(0);
}
return(result);
}
catch (Exception ex)
{
WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]",
strSql.ToString(), base.FormatParameters(dbCommand.Parameters), ex.Message));
return(0);
}
}
}
public SystemEntity GetBySysName(string sysName)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT s.* from Systems s ");
strSql.Append("WHERE s.SystemName=@SystemName");
SystemEntity systemEntity = null;
Database db = DatabaseFactory.CreateDatabase();
using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString()))
{
try
{
db.AddInParameter(dbCommand, "SystemName", DbType.String, sysName);
using (IDataReader dataReader = db.ExecuteReader(dbCommand))
{
if (dataReader.Read())
{
systemEntity = SystemEntity.ReaderBind(dataReader);
}
}
}
catch (Exception ex)
{
WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]",
strSql.ToString(),
base.FormatParameters(dbCommand.Parameters),
ex.Message));
}
}
return(systemEntity);
}
public List <EventInviteEntity> GetEventInvites(int eventId)
{
List <EventInviteEntity> list = new List <EventInviteEntity>();
StringBuilder strSql = new StringBuilder();
strSql.AppendFormat(" SELECT * FROM dbo.EventInvites WHERE EventID={0}", eventId);
Database db = DatabaseFactory.CreateDatabase();
using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString()))
{
try
{
using (IDataReader reader = db.ExecuteReader(dbCommand))
{
while (reader.Read())
{
list.Add(new EventInviteEntity(reader));
}
}
}
catch (Exception ex)
{
WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]", strSql.ToString(), base.FormatParameters(dbCommand.Parameters), ex.Message));
}
}
return(list);
}
public List <CateGoryEntity> GetCateGoryListByUserID(int UserID)
{
string strSql = @"SELECT [GID]
,[Title]
,[CreatedOn]
,[CreatedBy]
,[IsOnlyShowTody]
,[IsDelete]
FROM [CateGory]
WHERE [CreatedBy]=@UserID AND IsDelete = 0
Order by GID asc ";
List <CateGoryEntity> list = new List <CateGoryEntity>();
Database db = DatabaseFactory.CreateDatabase();
using (DbCommand dbCommand = db.GetSqlStringCommand(strSql))
{
try
{
db.AddInParameter(dbCommand, "UserID", DbType.Int32, UserID);
using (IDataReader dataReader = db.ExecuteReader(dbCommand))
while (dataReader.Read())
{
list.Add(CateGoryEntity.ReaderBind(dataReader));
}
}
catch (Exception ex)
{
WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]"
, strSql.ToString(), base.FormatParameters(dbCommand.Parameters), ex.Message));
return(null);
}
}
return(list);
}
public bool UpdateTicketStatusToDeny(int ticketId)
{
try
{
if (UserInfo.UserID <= 0)
{
return(false);
}
TicketsEntity ticketEntity = new TicketsEntity();
ticketEntity = ticketAPP.GetTickets(ticketId);
TicketsState originalStatus = ticketEntity.Status;
bool Update = true;
ticketEntity.Status = TicketsState.Not_Approved;
ticketEntity.ModifiedOn = DateTime.Now;
ticketEntity.ModifiedBy = UserInfo.UserID;
ticketEntity.PublishDate = DateTime.Now.Date;
ProjectsEntity projectEntity = projectApp.Get(ticketEntity.ProjectID);
Update = ticketAPP.UpdateTickets(ticketEntity, true, projectEntity.PMID);
ticketStatusMgr.SendEmailWithClientNotApp(ticketEntity);
return(Update);
}
catch (Exception ex)
{
WebLogAgent.Write(string.Format("Error Ashx:DoUpdateTicketStatus.ashx Messages:\r\n{0}", ex));
return(false);
}
}
public bool SendDailyMail(DateTime date, string office)
{
UserApplication userApp = new UserApplication();
// sunneters
List <UsersEntity> allUsers = userApp.GetActiveUserList().FindAll(u => u.Role != RolesEnum.CLIENT && u.IsNotice);
List <UsersEntity> currentUsers = allUsers.FindAll(u => u.Office.ToUpper() == office.ToUpper());
List <UsersEntity> noSubmittedUsers = new List <UsersEntity>();
TimeSheetApplication tsApp = new TimeSheetApplication();
SearchTimeSheetsRequest request = new SearchTimeSheetsRequest(SearchType.EmailNotice,
false, "ModifiedOn", "ASC");
request.Office = office;
request.SearchDate = date;
SearchTimeSheetsResponse response = tsApp.QueryTimesheet(request);
if (response.IsError)
{
WebLogAgent.Write("SunNet.PMNew.Web.Codes.EmailExecuter.SendDailyMail:136,GetDataError");
return(false);
}
StringBuilder ticketHtml = new StringBuilder();
StringBuilder usersTickets = new StringBuilder();
if (response.TimeSheetsList.Count == 0)
{
// no timesheet,clear htmlTemplate nodes
string emptyTmeplate = UserFirstRowSection;
emptyTmeplate = UserContentSection;
}
foreach (UsersEntity user in currentUsers)
{
List <TimeSheetTicket> userTickets = response.TimeSheetsList.FindAll(tts => tts.UserID == user.ID);
if (userTickets == null || userTickets.Count == 0)
{
noSubmittedUsers.Add(user);
continue;
}
else
{
usersTickets.Append(GetUserTicketsHtml(user, userTickets));
}
}
ticketHtml.Append(GetDateSectionHtml(date, response.TimeSheetsList.Count + noSubmittedUsers.Count + 1));
ticketHtml.Append(usersTickets);
ticketHtml.Append(GetUserNoTicketsHtml(noSubmittedUsers));
// 不需要没有写的汇总了，因为另一封已经包含了。
//SendNoSubmitEmail(noSubmittedUsers, date);
foreach (UsersEntity user in noSubmittedUsers)
{
SendNoticeToUser(user, date);
}
SendSubmittedEmail(ticketHtml.ToString(), date);
return(true);
}
/// <summary>
/// Delete a record
/// </summary>
public bool Delete(int GID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update CateGory set ");
strSql.Append("IsDelete=@IsDelete ");
strSql.Append(" where GID=@GID and IsOnlyShowTody = 0");
Database db = DatabaseFactory.CreateDatabase();
using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString()))
{
try
{
db.AddInParameter(dbCommand, "IsDelete", DbType.Int32, 1);
db.AddInParameter(dbCommand, "GID", DbType.Int32, GID);
int rows = db.ExecuteNonQuery(dbCommand);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception ex)
{
WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]",
strSql.ToString(), base.FormatParameters(dbCommand.Parameters), ex.Message));
return(false);
}
}
}
/// <summary>
/// 产生气泡，通知其它用户
/// </summary>
public bool CreateNotification(int ticketId, int userId, bool notTificationClient = true)
{
string strSql = string.Format("update TicketUsers set [ShowNotification] = 1 where TicketID ={0} and UserId != {1} ", ticketId, userId);
if (notTificationClient)
{
strSql += string.Format(" and type != {0} ", TicketUsersType.Client);
}
Database db = DatabaseFactory.CreateDatabase();
using (DbCommand dbCommand = db.GetSqlStringCommand(strSql))
{
try
{
int rows = db.ExecuteNonQuery(dbCommand);
return(rows >= 0);
}
catch (Exception ex)
{
WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]",
strSql, base.FormatParameters(dbCommand.Parameters), ex.Message));
return(false);
}
}
}
public bool UpdateTicketStatus(int ticketId, UserTicketStatus status)
{
string where = " TicketID = @TicketID ";
//var strSql1 = "update TicketUsers set [ShowNotification] = 1 where " + where + " AND UserID <> @UserID ;";//去除更新气泡的操作，更改状态不进行更改气泡
var strSql1 = "";
strSql1 += "update TicketUsers set [TicketStatus] = @Status where " + where;
Database db = DatabaseFactory.CreateDatabase();
using (DbCommand dbCommand = db.GetSqlStringCommand(strSql1))
{
try
{
db.AddInParameter(dbCommand, "UserId", DbType.Int32, IdentityContext.UserID);
db.AddInParameter(dbCommand, "TicketID", DbType.Int32, ticketId);
db.AddInParameter(dbCommand, "Status", DbType.Int32, (int)status);
int rows = db.ExecuteNonQuery(dbCommand);
return(rows >= 0);
}
catch (Exception ex)
{
WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]",
strSql1, base.FormatParameters(dbCommand.Parameters), ex.Message));
return(false);
}
}
}
public bool UpdateWorkingOnStatus(int ticket, int user, Entity.TicketModel.Enums.TicketUserStatus status)
{
string strSql = "update TicketUsers set [Status] = @Status where TicketID = @TicketID";
if (user > 0)
{
strSql += " AND UserID = @UserID";
}
Database db = DatabaseFactory.CreateDatabase();
using (DbCommand dbCommand = db.GetSqlStringCommand(strSql))
{
try
{
db.AddInParameter(dbCommand, "Status", DbType.Int32, (int)status);
db.AddInParameter(dbCommand, "TicketID", DbType.Int32, ticket);
db.AddInParameter(dbCommand, "UserID", DbType.Int32, user);
int rows = db.ExecuteNonQuery(dbCommand);
return(rows >= 0);
}
catch (Exception ex)
{
WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]",
strSql, base.FormatParameters(dbCommand.Parameters), ex.Message));
return(false);
}
}
}
public TicketUsersEntity Get(int ticket, int user)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select top 1 * from TicketUsers where TicketID = @TicketID AND UserID = @UserID Order by TUID desc");
Database db = DatabaseFactory.CreateDatabase();
using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString()))
{
db.AddInParameter(dbCommand, "UserID", DbType.Int32, user);
db.AddInParameter(dbCommand, "TicketID", DbType.Int32, ticket);
TicketUsersEntity model = null;
using (IDataReader dataReader = db.ExecuteReader(dbCommand))
{
try
{
if (dataReader.Read())
{
model = TicketUsersEntity.ReaderBind(dataReader);
}
return(model);
}
catch (Exception ex)
{
WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]",
strSql, base.FormatParameters(dbCommand.Parameters), ex.Message));
return(null);
}
}
}
}
public List <int> GetTicketUserId(int ticketId, List <int> userIds)
{
List <int> noneDeletedUserIds = new List <int>();
StringBuilder strSql = new StringBuilder();
strSql.AppendFormat(" select userID from ticketUsers where ticketid={0};", ticketId)
.Append("delete from TicketUsers ")
.AppendFormat(" where ticketid={0} ", ticketId)
.AppendFormat(" and userid not in({0}) ", string.Join(",", userIds))
.Append(" and Type<>5 ;");
Database db = DatabaseFactory.CreateDatabase();
using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString()))
{
try
{
using (IDataReader dataReader = db.ExecuteReader(dbCommand))
{
while (dataReader.Read())
{
noneDeletedUserIds.Add(int.Parse(dataReader[0].ToString()));
}
}
return(noneDeletedUserIds);
}
catch (Exception ex)
{
WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]",
strSql.ToString(), base.FormatParameters(dbCommand.Parameters), ex.Message));
return(new List <int>());
}
}
}
public string GetClientStatusNameBySatisfyStatus(int status, int TicketID)
{
try
{
FeedBackMessagesEntity tmpEntity = _listFbms.Find(r => r.TicketID == TicketID && r.WaitClientFeedback > 0);
if (tmpEntity != null)
{
return("<span style='color:red;'>Wait Feedback</span>");
}
if (status == (int)TicketsState.Ready_For_Review)
{
return("<span style='color:red;'>Ready For Review</span>");
}
if (UnderDevelopingStatus().Contains(status))
{
return("In Progress");
}
else if (UnderEstimationStatus().Contains(status))
{
return("Estimating");
}
return(Enum.GetName(typeof(TicketsState), status).Replace('_', ' '));
}
catch (Exception ex)
{
WebLogAgent.Write("Excepted Exception in FeedBackMessageHandler.GetClientStatusNameBySatisfyStatus(" + status.ToString() + "," + TicketID.ToString() + ")\n\r" + ex.Message);
return("Expected.");
}
}
/// <summary>
/// Add a record
/// </summary>
public int Insert(RoleModulesEntity model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into RoleModules(");
strSql.Append("RoleID,ModuleID)");
strSql.Append(" values (");
strSql.Append("@RoleID,@ModuleID)");
strSql.Append(";select ISNULL( SCOPE_IDENTITY(),0);");
Database db = DatabaseFactory.CreateDatabase();
using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString()))
{
try
{
db.AddInParameter(dbCommand, "RoleID", DbType.Int32, model.RoleID);
db.AddInParameter(dbCommand, "ModuleID", DbType.Int32, model.ModuleID);
int result;
object obj = db.ExecuteScalar(dbCommand);
if (!int.TryParse(obj.ToString(), out result))
{
return(0);
}
return(result);
}
catch (Exception ex)
{
WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]"
, strSql.ToString(), base.FormatParameters(dbCommand.Parameters), ex.Message));
return(0);
}
}
}
public List <WorkflowHistoryEntity> GetList(int workflowRequestID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select wfhis.*, u.FirstName+' '+u.LastName As ProcessedByName from WorkflowHistory wfhis, Users u ");
strSql.Append(" where WorkflowRequestID=@WorkflowRequestID and wfhis.ProcessedBy=u.UserID; ");
Database db = DatabaseFactory.CreateDatabase();
List <WorkflowHistoryEntity> list = new List <WorkflowHistoryEntity>();
using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString()))
{
try
{
db.AddInParameter(dbCommand, "WorkflowRequestID", DbType.Int32, workflowRequestID);
using (IDataReader dataReader = db.ExecuteReader(dbCommand))
{
while (dataReader.Read())
{
list.Add(WorkflowHistoryEntity.ReaderBind(dataReader));
}
}
foreach (WorkflowHistoryEntity ent in list)
{
SealFilesRepositorySqlDataProvider sealFilesRep = new SealFilesRepositorySqlDataProvider();
ent.lstFiles = sealFilesRep.GetListByHistoryID(ent.ID);
}
}
catch (Exception ex)
{
WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]", strSql.ToString(), base.FormatParameters(dbCommand.Parameters), ex.Message));
}
return(list);
}
}
public bool ClearWaitingByType(int ticketId, List <TicketUsersType> types)
{
var strSql1 = @"update TicketUsers set [TicketStatus] = @Status where TicketID = @TicketID ";
if (types != null)
{
var typesId = types.Select(x => (int)x);
strSql1 += " AND Type in (" + string.Join(",", typesId) + ")";
}
Database db = DatabaseFactory.CreateDatabase();
using (DbCommand dbCommand = db.GetSqlStringCommand(strSql1))
{
try
{
db.AddInParameter(dbCommand, "TicketID", DbType.Int32, ticketId);
db.AddInParameter(dbCommand, "Status", DbType.Int32, (int)UserTicketStatus.Normal);
using (var dataReader = db.ExecuteReader(dbCommand))
{
if (dataReader.Read())
{
var count = dataReader.GetInt32(0);
return(count == 0);
}
}
}
catch (Exception ex)
{
WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]",
strSql1, base.FormatParameters(dbCommand.Parameters), ex.Message));
return(false);
}
}
return(false);
}
public bool UpdateInactiveToPending(int sealRequestID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("Update WorkflowHistory Set ");
strSql.Append("Action=-1 ");
strSql.Append("WHERE WorkflowRequestID=@WorkflowRequestID AND Action=-2");
Database db = DatabaseFactory.CreateDatabase();
using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString()))
{
try
{
db.AddInParameter(dbCommand, "WorkflowRequestID", DbType.Int32, sealRequestID);
db.ExecuteScalar(dbCommand);
return(true);
}
catch (Exception ex)
{
WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]",
strSql.ToString(),
base.FormatParameters(dbCommand.Parameters),
ex.Message));
return(false);
}
}
}
public List <String> GetUsersWithStatus(int ticketId, UserTicketStatus status, List <TicketUsersType> types)
{
List <String> list = new List <String>();
var strSql = @"Select u.firstname+' '+u.lastname as UserName from ticketusers tu, users u
Where tu.TicketID = @TicketID AND tu.userid=u.userid AND tu.TicketStatus = @Status";
if (types != null)
{
var typesId = types.Select(x => (int)x);
strSql += " AND Type in (" + string.Join(",", typesId) + ")";
}
Database db = DatabaseFactory.CreateDatabase();
using (DbCommand dbCommand = db.GetSqlStringCommand(strSql))
{
try
{
db.AddInParameter(dbCommand, "TicketID", DbType.Int32, ticketId);
db.AddInParameter(dbCommand, "Status", DbType.Int32, (int)status);
using (var dataReader = db.ExecuteReader(dbCommand))
{
while (dataReader.Read())
{
list.Add(dataReader["UserName"].ToString());
}
}
}
catch (Exception ex)
{
WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]",
strSql, base.FormatParameters(dbCommand.Parameters), ex.Message));
}
}
return(list);
}
public bool UpdateTicketStatusToApprove(int ticketID)
{
try
{
if (UserInfo.UserID <= 0)
{
return(false);
}
TicketsEntity ticketEntity = ticketAPP.GetTickets(ticketID);
TicketsState originalStatus = ticketEntity.Status;
bool Update = true;
ticketEntity.Status = TicketsState.Completed;
ticketEntity.ModifiedOn = DateTime.Now;
ticketEntity.ModifiedBy = UserInfo.UserID;
ticketEntity.PublishDate = DateTime.Now.Date;
Update = ticketAPP.UpdateTickets(ticketEntity);
// 完成时更新所有相关人员的WorkingOn状态
ticketAPP.UpdateWorkingOnStatus(ticketID, TicketsState.Completed);
return(Update);
}
catch (Exception ex)
{
WebLogAgent.Write(string.Format("Error Ashx:DoUpdateTicketStatus.ashx Messages:\r\n{0}", ex));
return(false);
}
}
public List <TicketUsersEntity> GetTicketUserList(int ticketId)
{
List <TicketUsersEntity> list = new List <TicketUsersEntity>();
StringBuilder strSql = new StringBuilder();
strSql.AppendFormat(" select * from ticketUsers where ticketid={0};", ticketId);
Database db = DatabaseFactory.CreateDatabase();
using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString()))
{
try
{
using (IDataReader dataReader = db.ExecuteReader(dbCommand))
{
while (dataReader.Read())
{
list.Add(TicketUsersEntity.ReaderBind(dataReader));
}
}
}
catch (Exception ex)
{
WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]",
strSql.ToString(), base.FormatParameters(dbCommand.Parameters), ex.Message));
}
return(list);
}
}
/// <summary>
/// Get an object entity
/// </summary>
public CateGoryEntity Get(int GID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select * from CateGory ");
strSql.Append(" where GID=@GID ");
Database db = DatabaseFactory.CreateDatabase();
using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString()))
{
db.AddInParameter(dbCommand, "GID", DbType.Int32, GID);
CateGoryEntity model = null;
using (IDataReader dataReader = db.ExecuteReader(dbCommand))
{
try
{
if (dataReader.Read())
{
model = CateGoryEntity.ReaderBind(dataReader);
}
return(model);
}
catch (Exception ex)
{
WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]",
strSql.ToString(), base.FormatParameters(dbCommand.Parameters), ex.Message));
return(null);
}
}
}
}
/// <summary>
/// Delete a record
/// </summary>
public bool Delete(int ModuleID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("delete from Modules ");
strSql.Append(" where ModuleID=@ModuleID ");
Database db = DatabaseFactory.CreateDatabase();
using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString()))
{
try
{
db.AddInParameter(dbCommand, "ModuleID", DbType.Int32, ModuleID);
int rows = db.ExecuteNonQuery(dbCommand);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception ex)
{
WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]", strSql.ToString(), base.FormatParameters(dbCommand.Parameters), ex.Message));
return(false);
}
}
}
public int CountCateGory(string title, int userID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("Select Count(1) from CateGory ");
strSql.Append(" where Title=@Title AND CreatedBy=@CreatedBy AND IsDelete=0");
Database db = DatabaseFactory.CreateDatabase();
using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString()))
{
try
{
db.AddInParameter(dbCommand, "Title", DbType.String, title);
db.AddInParameter(dbCommand, "CreatedBy", DbType.Int32, userID);
using (IDataReader dataReader = db.ExecuteReader(dbCommand))
{
int count = 0;
if (dataReader.Read())
{
count = dataReader.GetInt32(0);
}
return(count);
}
}
catch (Exception ex)
{
WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]", strSql.ToString(), base.FormatParameters(dbCommand.Parameters), ex.Message));
return(0);
}
}
}
public int GetAllModulesCount(int parentID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(@"SELECT COUNT(1) FROM [Modules] ");
strSql.Append(@"WHERE [ParentID]=@ParentID AND ([Status] =0 OR [Status] =1)");
Database db = DatabaseFactory.CreateDatabase();
using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString()))
{
try
{
db.AddInParameter(dbCommand, "ParentID", DbType.Int32, parentID);
using (IDataReader dataReader = db.ExecuteReader(dbCommand))
{
int result;
object obj = db.ExecuteScalar(dbCommand);
if (!int.TryParse(obj.ToString(), out result))
{
return(0);
}
return(result);
}
}
catch (Exception ex)
{
WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]",
strSql.ToString(),
base.FormatParameters(dbCommand.Parameters),
ex.Message));
}
}
return(0);
}
public List <SystemEntity> GetAllSystems()
{
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT * from Systems");
List <SystemEntity> list = new List <SystemEntity>();
Database db = DatabaseFactory.CreateDatabase();
using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString()))
{
try
{
using (IDataReader dataReader = db.ExecuteReader(dbCommand))
{
while (dataReader.Read())
{
list.Add(SystemEntity.ReaderBind(dataReader));
}
}
}
catch (Exception ex)
{
WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]",
strSql.ToString(),
base.FormatParameters(dbCommand.Parameters),
ex.Message));
}
}
return(list);
}
public bool Delete(int ticketID, int cateGoryID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("delete from CateGoryTicket ");
strSql.Append(" where CategoryID=@CategoryID");
if (ticketID > 0)
{
strSql.Append(" AND TicketID=@TicketID ");
}
Database db = DatabaseFactory.CreateDatabase();
using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString()))
{
try
{
db.AddInParameter(dbCommand, "CategoryID", DbType.Int32, cateGoryID);
db.AddInParameter(dbCommand, "TicketID", DbType.Int32, ticketID);
int rows = db.ExecuteNonQuery(dbCommand);
return(rows >= 0);
}
catch (Exception ex)
{
WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]",
strSql.ToString(), base.FormatParameters(dbCommand.Parameters), ex.Message));
return(false);
}
}
}
/// <summary>
/// 0:失败 ;1:邀请; 2:加入; 3:拒绝; 4:忽略
/// </summary>
public int GetStatus(int userId, int eventId)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select [Status] from dbo.EventInvites ")
.AppendFormat(" where UserID={0} and eventId={1} ", userId, eventId);
Database db = DatabaseFactory.CreateDatabase();
using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString()))
{
try
{
IDataReader reader = db.ExecuteReader(dbCommand);
if (reader.Read())
{
return((int)reader[0]);
}
return(0);
}
catch (Exception ex)
{
WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]", strSql.ToString(), base.FormatParameters(dbCommand.Parameters), ex.Message));
return(0);
}
}
}
protected void Application_Error(object sender, EventArgs e)
{
for (int i = 0; i < Context.AllErrors.Length; i++)
{
WebLogAgent.Write(Context.AllErrors[i]);
}
}
/// <summary>
/// 删除 UserId= 0 ; 或者 UserId > 0 and optionStatu =3
/// </summary>
/// <param name="inviteList"></param>
/// <returns></returns>
public bool RemoveInviteUser(List <EventInviteEntity> inviteList)
{
if (inviteList.Count == 0)
{
return(true);
}
string[] userIds = inviteList.Where(r => r.UserID > 0 && r.OptionStatus == 3).Select(r => r.UserID.ToString()).ToArray();
StringBuilder sb = new StringBuilder();
if (userIds.Length > 0)
{
sb.AppendFormat(" delete from EventInvites where EventID={0} and UserID in ({1}) ;",
inviteList[0].EventID, string.Join(",", userIds));
}
sb.AppendFormat(" delete from EventInvites where EventID={0} and UserID = 0", inviteList[0].EventID);
Database db = DatabaseFactory.CreateDatabase();
using (DbCommand dbCommand = db.GetSqlStringCommand(sb.ToString()))
{
try
{
db.ExecuteNonQuery(dbCommand);
}
catch (Exception ex)
{
WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]", sb.ToString(), base.FormatParameters(dbCommand.Parameters), ex.Message));
return(false);
}
}
return(true);
}
public UsersEntity GetUserByUserName(string username)
{
string strSql = @"SELECT u.*,c.CompanyName
FROM [Users] u left join Companys c on u.CompanyID=c.ComID
WHERE UserName =@UserName AND [IsDelete]=0";
Database db = DatabaseFactory.CreateDatabase();
using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString()))
{
db.AddInParameter(dbCommand, "UserName", DbType.String, username.FilterSqlString());
UsersEntity model = null;
using (IDataReader dataReader = db.ExecuteReader(dbCommand))
{
try
{
if (dataReader.Read())
{
model = UsersEntity.ReaderBind(dataReader);
}
}
catch (Exception ex)
{
WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]",
strSql.ToString(), base.FormatParameters(dbCommand.Parameters), ex.Message));
return(null);
}
}
return(model);
}
}