public ResponseModel SaveTaskAttachment(TaskAttachment model)
{
var errMessage = string.Empty;
var queryParamList = new QueryParamList
{
new QueryParamObj {
ParamDirection = ParameterDirection.Input, ParamName = "@Id", ParamValue = Guid.NewGuid().ToString()
},
new QueryParamObj {
ParamDirection = ParameterDirection.Input, ParamName = "@TaskId", ParamValue = model.TaskId
},
new QueryParamObj {
ParamDirection = ParameterDirection.Input, ParamName = "@FileName", ParamValue = model.FileName
},
new QueryParamObj {
ParamDirection = ParameterDirection.Input, ParamName = "@BlobName", ParamValue = model.BlobName
},
new QueryParamObj {
ParamDirection = ParameterDirection.Input, ParamName = "@UpdatedAt", ParamValue = DateTime.UtcNow, DBType = DbType.DateTime
},
new QueryParamObj {
ParamDirection = ParameterDirection.Input, ParamName = "@UpdatedById", ParamValue = model.UpdatedById
},
};
const string sql = @"INSERT INTO ResourceTracker_TaskAttachments(Id,TaskId,FileName,BlobName,UpdatedAt,UpdatedById)
VALUES (@Id,@TaskId,@FileName,@BlobName,@UpdatedAt,@UpdatedById)";
DBExecCommandEx(sql, queryParamList, ref errMessage);
return(new ResponseModel {
Success = string.IsNullOrEmpty(errMessage)
});
}
public ResponseModel AddAttendanceAsLeave(AttendanceEntryModel model)
{
var errMessage = string.Empty;
var queryParamList = new QueryParamList
{
new QueryParamObj {
ParamDirection = ParameterDirection.Input, ParamName = "@UserId", ParamValue = model.UserId
},
new QueryParamObj {
ParamDirection = ParameterDirection.Input, ParamName = "@CompanyId", ParamValue = model.CompanyId
},
new QueryParamObj {
ParamDirection = ParameterDirection.Input, ParamName = "@AttendanceDate", ParamValue = DateTime.UtcNow, DBType = DbType.DateTime
},
new QueryParamObj {
ParamDirection = ParameterDirection.Input, ParamName = "@IsLeave", ParamValue = true, DBType = DbType.Boolean
}
};
const string sql = @"IF NOT EXISTS(SELECT * FROM ResourceTracker_Attendance A WHERE A.UserId=@UserId AND CONVERT(DATE,AttendanceDate)=CONVERT(DATE,@AttendanceDate))
BEGIN
INSERT INTO ResourceTracker_Attendance(UserId,CompanyId,AttendanceDate,IsLeave)
VALUES(@UserId,@CompanyId,@AttendanceDate,@IsLeave)
END";
DBExecCommandEx(sql, queryParamList, ref errMessage);
return(new ResponseModel {
Success = string.IsNullOrEmpty(errMessage)
});
}
public ResponseModel ToDoTaskShare(string taskId, List <string> userList)
{
var errMessage = string.Empty;
foreach (var x in userList)
{
var queryParamList = new QueryParamList
{
new QueryParamObj {
ParamDirection = ParameterDirection.Input, ParamName = "@Id", ParamValue = Guid.NewGuid().ToString()
},
new QueryParamObj {
ParamDirection = ParameterDirection.Input, ParamName = "@TaskId", ParamValue = taskId
},
new QueryParamObj {
ParamDirection = ParameterDirection.Input, ParamName = "@ShareWithId", ParamValue = x
},
};
const string sql = @"INSERT INTO ResourceTracker_ToDoTaskShare(Id,TaskId,ShareWithId) VALUES(@Id,@TaskId,@ShareWithId)";
DBExecCommandEx(sql, queryParamList, ref errMessage);
}
return(new ResponseModel {
Success = string.IsNullOrEmpty(errMessage)
});
}
public List <AttendanceModel> GetAttendance(string userId, int companyId, DateTime startDate, DateTime endDate)
{
const string sql = @"
SELECT c.Id,t.UserId,t.AttendanceDate,t.CheckInTime,t.CheckOutTime,t.AllowOfficeLessTime,T.IsLeave,
t.LessTimeReason,t.DailyWorkingTimeInMin,t.CompanyId,C.Id EmployeeId
,C.UserName EmployeeName,C.Designation,d.DepartmentName,c.ImageFileName,c.PhoneNumber, null TotalHours, null TotalMinutes
FROM ResourceTracker_Attendance t
LEFT JOIN ResourceTracker_EmployeeUser C ON T.UserId=C.UserId
LEFT JOIN UserCredentials CR ON C.UserId=CR.Id
left join ResourceTracker_Department d on c.DepartmentId=d.Id
where t.CompanyId=@companyId AND T.UserId=@userId and (CONVERT(DATE,AttendanceDate) BETWEEN convert(date,@startDate) AND convert(date,@endDate))";
var queryParamList = new QueryParamList
{
new QueryParamObj {
ParamDirection = ParameterDirection.Input, ParamName = "@userId", ParamValue = userId
},
new QueryParamObj {
ParamDirection = ParameterDirection.Input, ParamName = "@companyId", ParamValue = companyId, DBType = DbType.Int32
},
new QueryParamObj {
ParamDirection = ParameterDirection.Input, ParamName = "@startDate", ParamValue = startDate, DBType = DbType.DateTime
},
new QueryParamObj {
ParamDirection = ParameterDirection.Input, ParamName = "@endDate", ParamValue = endDate, DBType = DbType.DateTime
}
};
var data = ExecuteDBQuery(sql, queryParamList, AttendanceMapper.ToAttendance);
return(data);
}
public ResponseModel UpdateEmployee(PortalUserViewModel model)
{
var errMessage = string.Empty;
var queryParamList = new QueryParamList
{
new QueryParamObj {
ParamDirection = ParameterDirection.Input, ParamName = "@Id", ParamValue = model.Id
},
new QueryParamObj {
ParamDirection = ParameterDirection.Input, ParamName = "@UserFullName", ParamValue = model.UserFullName
},
new QueryParamObj {
ParamDirection = ParameterDirection.Input, ParamName = "@DesignationName", ParamValue = model.DesignationName
},
new QueryParamObj {
ParamDirection = ParameterDirection.Input, ParamName = "@ImageFileName", ParamValue = model.ImageFileName
},
new QueryParamObj {
ParamDirection = ParameterDirection.Input, ParamName = "@ImageFileId", ParamValue = model.ImageFileId
},
new QueryParamObj {
ParamDirection = ParameterDirection.Input, ParamName = "@IsActive", ParamValue = model.IsActive
},
};
const string sql = @"UPDATE ResourceTracker_EmployeeUser SET UserName=@UserFullName,Designation=@DesignationName,
ImageFileName=@ImageFileName,ImageFileId=@ImageFileId,IsActive=@IsActive WHERE Id=@Id";
DBExecCommandEx(sql, queryParamList, ref errMessage);
return(new ResponseModel {
Success = string.IsNullOrEmpty(errMessage)
});
}
public ResponseModel CheckOut(AttendanceEntryModel model)
{
var errMessage = string.Empty;
var queryParamList = new QueryParamList
{
new QueryParamObj {
ParamDirection = ParameterDirection.Input, ParamName = "@UserId", ParamValue = model.UserId
},
new QueryParamObj {
ParamDirection = ParameterDirection.Input, ParamName = "@LessTimeReason", ParamValue = model.Reason
},
new QueryParamObj {
ParamDirection = ParameterDirection.Input, ParamName = "@CheckOutTime", ParamValue = DateTime.UtcNow, DBType = DbType.DateTime
}
};
const string sql = @"DECLARE @id bigint
SELECT TOP 1 @id=Id FROM ResourceTracker_Attendance WHERE UserId=@UserId AND CheckOutTime IS NULL ORDER BY Id DESC
UPDATE ResourceTracker_Attendance SET CheckOutTime=@CheckOutTime,LessTimeReason=@LessTimeReason WHERE Id=@id";
DBExecCommandEx(sql, queryParamList, ref errMessage);
return(new ResponseModel {
Success = string.IsNullOrEmpty(errMessage)
});
}
public List <TaskModel> GetTaskList(TaskModel sModel)
{
const string sql = @"SELECT T.*,C.FullName AssignToName,CreatedBy.FullName CreatedByName,UpdatedBy.FullName UpdatedByName
FROM ResourceTracker_Task t
LEFT JOIN UserCredentials C ON T.AssignedToId=C.Id
LEFT JOIN UserCredentials CreatedBy ON T.CreatedById=CreatedBy.Id
LEFT JOIN UserCredentials UpdatedBy ON T.UpdatedById=UpdatedBy.Id
where
(@CreatedById is null or t.CreatedById=@CreatedById)
and (@AssignedToId is null or t.AssignedToId=@AssignedToId)
and (@TaskGroupId is null or t.TaskGroupId=@TaskGroupId)";
var queryParamList = new QueryParamList
{
new QueryParamObj {
ParamDirection = ParameterDirection.Input, ParamName = "@CreatedById", ParamValue = string.IsNullOrEmpty(sModel.CreatedById)?null:sModel.CreatedById
},
new QueryParamObj {
ParamDirection = ParameterDirection.Input, ParamName = "@AssignedToId", ParamValue = string.IsNullOrEmpty(sModel.AssignedToId)?null:sModel.AssignedToId
},
new QueryParamObj {
ParamDirection = ParameterDirection.Input, ParamName = "@TaskGroupId", ParamValue = sModel.TaskGroupId
},
};
return(ExecuteDBQuery(sql, queryParamList, EmployeeTaskMapper.ToTask));
}
public ResponseModel UpdateDepartment(Department model)
{
var err = string.Empty;
Database db = GetSQLDatabase();
using (DbConnection conn = db.CreateConnection())
{
conn.Open();
DbTransaction trans = conn.BeginTransaction();
try
{
const string sql = @"UPDATE ResourceTracker_Department SET DepartmentName = @DepartmentName WHERE Id=@Id";
var queryParamList = new QueryParamList
{
new QueryParamObj {
ParamName = "@DepartmentName", ParamValue = model.DepartmentName
},
new QueryParamObj {
ParamName = "@Id", ParamValue = model.Id
}
};
DBExecCommandEx(sql, queryParamList, ref err);
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
err = ex.Message;
}
}
return(new ResponseModel {
Success = string.IsNullOrEmpty(err)
});
}
public ResponseModel Save(UserIdentityModel model)
{
var err = string.Empty;
const string sql = @"INSERT INTO UserCredentials(Id,LoginID,Password,UserName,Email,UserTypeId,IsActive,DesignationId,DepartmentId,RoomId,PhoneNumber)
VALUES(@Id,@LoginID,@Password,@UserName,@Email,@UserTypeId,1,@DesignationId,@DepartmentId,@RoomId,@PhoneNumber)";
var queryParamList = new QueryParamList
{
new QueryParamObj {
ParamName = "@Id", ParamValue = Guid.NewGuid().ToString()
},
new QueryParamObj {
ParamName = "@LoginID", ParamValue = model.LoginID
},
new QueryParamObj {
ParamName = "@Password", ParamValue = model.Password
},
new QueryParamObj {
ParamName = "@Email", ParamValue = model.Email
},
new QueryParamObj {
ParamName = "@UserTypeId", ParamValue = model.UserTypeId
},
new QueryParamObj {
ParamName = "@PhoneNumber", ParamValue = model.PhoneNumber
}
};
DBExecCommandEx(sql, queryParamList, ref err);
return(new ResponseModel {
Success = string.IsNullOrEmpty(err)
});
}
public int SaveToNoticeBoard(NoticeDepartmentVIewModel model, Database db, DbTransaction trans)
{
var errMessage = string.Empty;
var queryParamList = new QueryParamList
{
new QueryParamObj {
ParamDirection = ParameterDirection.Input, ParamName = "@Id", ParamValue = model.Id, DBType = DbType.String
},
new QueryParamObj {
ParamDirection = ParameterDirection.Input, ParamName = "@Details", ParamValue = model.Details
},
new QueryParamObj {
ParamDirection = ParameterDirection.Input, ParamName = "@PostingDate", ParamValue = DateTime.UtcNow, DBType = DbType.DateTime
},
new QueryParamObj {
ParamDirection = ParameterDirection.Input, ParamName = "@ImageFileName", ParamValue = model.ImageFileName
},
new QueryParamObj {
ParamDirection = ParameterDirection.Input, ParamName = "@CreatedBy", ParamValue = model.CreatedBy
},
new QueryParamObj {
ParamDirection = ParameterDirection.Input, ParamName = "@CompanyId", ParamValue = model.CompanyId
},
new QueryParamObj {
ParamDirection = ParameterDirection.Input, ParamName = "@CreateDate", ParamValue = DateTime.UtcNow, DBType = DbType.DateTime
},
};
const string sql = @"INSERT INTO ResourceTracker_NoticeBoard (Id, Details, PostingDate, ImageFileName, CreatedBy, CreateDate,CompanyId) VALUES( @Id, @Details, @PostingDate,@ImageFileName, @CreatedBy, @CreateDate,@CompanyId)";
return(DBExecCommandExTran(sql, queryParamList, trans, db, ref errMessage));
}
public DataSet GetData(string tableName, QueryParamList pParams, ref string pErrString)
{
string query = "SELECT * FROM " + tableName;
AddWhereClause(ref query, pParams);
return(LoadDataSet(query, pParams, tableName, ref pErrString));
}
protected object DBExecuteScalarSP(string spName, QueryParamList spParams)
{
object result = null;
Database db = GetSQLDatabase();
using (DbCommand cmd = db.GetStoredProcCommand(spName))
{
if (spParams != null)
{
foreach (QueryParamObj obj in spParams)
{
db.AddInParameter(cmd, obj.ParamName, obj.DBType, obj.ParamValue);
}
}
try
{
result = db.ExecuteScalar(cmd);
}
catch (Exception ex)
{
//Logger.LogException(ex);
}
}
return(result);
}
protected DataSet GetSPResultSetEx(string storedProcName, QueryParamList spParams, ref string pErrString)
{
Database db = GetSQLDatabase();
var ds = new DataSet();
try
{
using (DbCommand cmd = db.GetStoredProcCommand(storedProcName))
{
if (spParams != null)
{
foreach (QueryParamObj obj in spParams)
{
db.AddInParameter(cmd, obj.ParamName, obj.DBType, obj.ParamValue);
}
}
ds = db.ExecuteDataSet(cmd);
}
}
catch (Exception ex)
{
pErrString = ex.Message;
//Logger.LogException(ex);
}
return(ds);
}
protected object DBExecuteScalar(string query, QueryParamList spParams, ref string pErrString)
{
Database db = GetSQLDatabase();
object result = null;
try
{
using (DbCommand cmd = db.GetSqlStringCommand(query))
{
if (spParams != null)
{
foreach (QueryParamObj obj in spParams)
{
db.AddInParameter(cmd, obj.ParamName, obj.DBType, obj.ParamValue);
}
}
result = db.ExecuteScalar(cmd);
}
}
catch (Exception e)
{
pErrString = e.Message;
//Logger.LogException(e);
}
return(result);
}
protected DataSet LoadDataSet(string query, QueryParamList spParams, string tableName, ref string pErrString)
{
Database db = GetSQLDatabase();
DataSet ds = new DataSet();
try
{
using (DbCommand cmd = db.GetSqlStringCommand(query))
{
if (spParams != null)
{
foreach (QueryParamObj obj in spParams)
{
db.AddInParameter(cmd, obj.ParamName, obj.DBType, obj.ParamValue);
}
}
db.LoadDataSet(cmd, ds, tableName);
}
}
catch (Exception e)
{
pErrString = e.Message;
//Logger.LogException(e);
}
return(ds);
}
public List <DeliveryManPackageDeliveryStatusViewModel> GetDeliveryStatusList(string deliveryManId, DateTime date)
{
var conDate = date.ToString("yyyy/MM/dd");
var commandText = @"SELECT dm.DeliveryManId,u.PackageDeliveryCount,u.PackageTergetCount,p.Name PackageName,uc.FullName CustomerName from [DailyUserPackageDelivery] u
left join Package p on u.PackageId = p.Id
left join DeliveryManCustomerTag dm on u.UserCrediantialId=dm.CustomerId
left join Employee e on dm.DeliveryManId=e.Id
left join UserCredentials uc on dm.CustomerId=uc.Id
where e.LoginKey='" + deliveryManId + @"' and CONVERT(VARCHAR(10), u.DeliveryAssignDate, 111) = '" + conDate + @"'
and CONVERT(VARCHAR(10), dm.CreatedAt, 111) = '" + conDate + @"'";
var queryParamList = new QueryParamList
{
new QueryParamObj {
ParamName = "@deliveryManId", ParamValue = deliveryManId
},
new QueryParamObj {
ParamName = "@adate", ParamValue = conDate
},
new QueryParamObj {
ParamName = "@bdate", ParamValue = conDate
}
};
var data = DbContext.Database.SqlQuery <DeliveryManPackageDeliveryStatusViewModel>(commandText).ToList();
return(data);
}
public AttendanceModel GetMyTodayAttendance(string userId, DateTime date)
{
const string sql = @"IF EXISTS(SELECT TOP 1 * FROM ResourceTracker_Attendance A WHERE A.UserId=@userId and convert(date,A.AttendanceDate)=convert(date,@date))
BEGIN
SELECT T.Id,t.UserId,t.AttendanceDate,t.CheckInTime,t.CheckOutTime,t.AllowOfficeLessTime,T.IsLeave,
t.LessTimeReason,t. DailyWorkingTimeInMin,t.CompanyId,C.Id EmployeeId
,C.UserName EmployeeName,c.Designation,d.DepartmentName,c.ImageFileName,c.PhoneNumber, null TotalHours, null TotalMinutes FROM ResourceTracker_Attendance t
LEFT JOIN ResourceTracker_EmployeeUser C ON T.UserId=C.UserId
LEFT JOIN UserCredentials CR ON C.UserId=CR.Id
left join ResourceTracker_Department d on c.DepartmentId=d.Id
where t.UserId=@userId and convert(date,t.AttendanceDate)=convert(date,@date)
END
ELSE
BEGIN
SELECT T.Id,t.UserId,@date AttendanceDate,NULL CheckInTime,NULL CheckOutTime,NULL AllowOfficeLessTime,NULL IsLeave,
NULL LessTimeReason,NULL DailyWorkingTimeInMin,t.CompanyId,t.Id EmployeeId
,t.UserName EmployeeName,t.Designation,d.DepartmentName,t.ImageFileName,t.PhoneNumber, null TotalHours, null TotalMinutes
FROM ResourceTracker_EmployeeUser t
LEFT JOIN UserCredentials CR ON T.UserId=CR.Id
left join ResourceTracker_Department d on t.DepartmentId=d.Id
where t.UserId=@userId
END";
var queryParamList = new QueryParamList
{
new QueryParamObj {
ParamDirection = ParameterDirection.Input, ParamName = "@userId", ParamValue = userId
},
new QueryParamObj {
ParamDirection = ParameterDirection.Input, ParamName = "@date", ParamValue = date, DBType = DbType.DateTime
}
};
var data = ExecuteDBQuery(sql, queryParamList, AttendanceMapper.ToAttendance);
return((data != null && data.Count > 0) ? data.FirstOrDefault() : null);
}
public ResponseModel SaveTask(TaskModel model)
{
var errMessage = string.Empty;
var queryParamList = new QueryParamList
{
new QueryParamObj {
ParamDirection = ParameterDirection.Input, ParamName = "@Id", ParamValue = string.IsNullOrEmpty(model.Id)?Guid.NewGuid().ToString():model.Id
},
new QueryParamObj {
ParamDirection = ParameterDirection.Input, ParamName = "@Title", ParamValue = model.Title
},
new QueryParamObj {
ParamDirection = ParameterDirection.Input, ParamName = "@Description", ParamValue = model.Description
},
new QueryParamObj {
ParamDirection = ParameterDirection.Input, ParamName = "@CreatedAt", ParamValue = DateTime.UtcNow, DBType = DbType.DateTime
},
new QueryParamObj {
ParamDirection = ParameterDirection.Input, ParamName = "@CreatedById", ParamValue = model.CreatedById
},
new QueryParamObj {
ParamDirection = ParameterDirection.Input, ParamName = "@AssignedToId", ParamValue = model.AssignedToId
},
new QueryParamObj {
ParamDirection = ParameterDirection.Input, ParamName = "@StatusId", ParamValue = model.StatusId
},
new QueryParamObj {
ParamDirection = ParameterDirection.Input, ParamName = "@TaskGroupId", ParamValue = model.TaskGroupId
},
new QueryParamObj {
ParamDirection = ParameterDirection.Input, ParamName = "@DueDate", ParamValue = model.DueDate.HasValue?model.DueDate.Value(Constants.ServerDateTimeFormat):(DateTime?)null, DBType = DbType.DateTime
},
new QueryParamObj {
ParamDirection = ParameterDirection.Input, ParamName = "@CompanyId", ParamValue = model.CompanyId
},
new QueryParamObj {
ParamDirection = ParameterDirection.Input, ParamName = "@PriorityId", ParamValue = model.PriorityId
}
};
const string sql = @"IF NOT EXISTS(SELECT TOP 1 P.Id FROM ResourceTracker_Task P WHERE P.Id=@Id)
BEGIN
DECLARE @tNo INT=0
SELECT @tNo=count(t.Id) FROM ResourceTracker_Task T WHERE T.CompanyId=@CompanyId
INSERT INTO ResourceTracker_Task(Id,TaskNo,Title,Description,CreatedAt,CreatedById,AssignedToId,StatusId,TaskGroupId,DueDate,CompanyId,PriorityId)
VALUES(@Id,@tNo+1,@Title,@Description,@CreatedAt,@CreatedById,@AssignedToId,@StatusId,@TaskGroupId,@DueDate,@CompanyId,@PriorityId)
END
ELSE
BEGIN
UPDATE ResourceTracker_Task SET Title=@Title,Description=@Description,AssignedToId=@AssignedToId,PriorityId=@PriorityId,
StatusId=@StatusId,TaskGroupId=@TaskGroupId,DueDate=@DueDate,UpdatedById=@CreatedById,UpdatedAt=@CreatedAt WHERE Id=@Id
END";
DBExecCommandEx(sql, queryParamList, ref errMessage);
return(new ResponseModel {
Success = string.IsNullOrEmpty(errMessage)
});
}
public Task <VideoProfile> GetProfile(string factoryId, string idOrName, bool?expand = null)
{
ValidateId("idOrName", idOrName);
return(InvokeGet <VideoProfile>(
factoryId,
string.Format("profiles/{0}.json", idOrName),
QueryParamList.New().AddNonEmpty("expand", expand)));
}
public Task <VideoEncoding> GetEncoding(string factoryId, string encodingId, bool?screenshots = null)
{
ValidateId("encodingId", encodingId);
return(InvokeGet <VideoEncoding>(
factoryId,
string.Format("encodings/{0}.json", encodingId),
QueryParamList.New()
.AddNonEmpty("screenshots", screenshots)));
}
public Task <Video> CreateVideo(string factoryId, string url, string[] profiles)
{
return(InvokePost <Video>(
factoryId,
"videos.json",
QueryParamList.New()
.AddNonEmpty("profiles", String.Join(",", profiles))
.AddNonEmpty("source_url", url)
));
}
public Task <List <VideoProfile> > GetProfiles(string factoryId, bool?expand = null, int?page = null, int?perPage = null)
{
return(InvokeGet <List <VideoProfile> >(
factoryId,
"profiles.json",
QueryParamList.New()
.AddNonEmpty("expand", expand)
.AddNonEmpty("page", page)
.AddNonEmpty("per_page", perPage)));
}
protected void AddWhereClause(ref string query, QueryParamList pParams)
{
query += " WHERE 1=1 ";
if (pParams.Count > 0)
{
foreach (QueryParamObj item in pParams)
{
query += " AND " + item.ParamName + " = @" + item.ParamName;
}
}
}
public List <ToDoTaskModel> GetToDoList(string userId)
{
const string sql = @"SELECT * FROM ResourceTracker_ToDoTask where CreatedById=@userId";
var queryParamList = new QueryParamList
{
new QueryParamObj {
ParamDirection = ParameterDirection.Input, ParamName = "@userId", ParamValue = userId
}
};
return(ExecuteDBQuery(sql, queryParamList, EmployeeTaskMapper.ToToDoTask));
}
public QueryParamList GenerateAuthParameters(RestRequest request)
{
var parameters = new QueryParamList();
parameters.Add("access_key", request.AccessKey);
if (!string.IsNullOrEmpty(request.FactoryId))
{
parameters.Add("cloud_id", request.FactoryId);
}
parameters.Add("timestamp", Uri.EscapeDataString(GetTimestamp(request.TimeStamp)));
return parameters;
}
public List <RoomSetupModel> GetAll(int?id)
{
const string sql = @"SELECT R.Id,R.BuildingId,R.RoomNo,R.Description,CASE WHEN IsRack = 1 THEN 'True' WHEN IsRack IS NULL THEN 'False' ELSE 'False' END IsRack,I.Name BuildingName From RoomSetup R left join InputHelp I on R.BuildingId = I.Id WHERE (@Id IS NULL OR R.Id=@Id)";
var queryParamList = new QueryParamList
{
new QueryParamObj {
ParamDirection = ParameterDirection.Input, ParamName = "@Id", ParamValue = id, DBType = DbType.Int32
}
};
return(ExecuteDBQuery(sql, queryParamList, InputHelpMapper.ToRoomSetup));
}
public List <TaskGroupModel> GetGroups(string companyId)
{
const string sql = @"SELECT * FROM ResourceTracker_TaskGroup where CompanyId=@companyId";
var queryParamList = new QueryParamList
{
new QueryParamObj {
ParamDirection = ParameterDirection.Input, ParamName = "@companyId", ParamValue = companyId
}
};
return(ExecuteDBQuery(sql, queryParamList, EmployeeTaskMapper.ToTaskGroup));
}
public Task <VideoEncoding> CreateEncoding(string factoryId, string videoId, string profileId, string profileName)
{
ValidateId("videoId", videoId);
return(InvokePost <VideoEncoding>(
factoryId,
"encodings.json",
QueryParamList.New()
.AddNonEmpty("video_id", videoId)
.AddNonEmpty("profile_id", profileId)
.AddNonEmpty("profileName", profileName)));
}
public int SaveEmployeeLeave(EmployeeLeaveModel model, Database db, DbTransaction trans)
{
var errMessage = string.Empty;
var queryParamList = new QueryParamList
{
new QueryParamObj {
ParamDirection = ParameterDirection.Input, ParamName = "@CompanyId", ParamValue = model.CompanyId, DBType = DbType.Int32
},
new QueryParamObj {
ParamDirection = ParameterDirection.Input, ParamName = "@userId", ParamValue = model.UserId
},
new QueryParamObj {
ParamDirection = ParameterDirection.Input, ParamName = "@FromDate", ParamValue = model.FromDate.ToString(Constants.ServerDateTimeFormat), DBType = DbType.DateTime
},
new QueryParamObj {
ParamDirection = ParameterDirection.Input, ParamName = "@ToDate", ParamValue = model.ToDate.ToString(Constants.ServerDateTimeFormat), DBType = DbType.DateTime
},
new QueryParamObj {
ParamDirection = ParameterDirection.Input, ParamName = "@IsHalfDay", ParamValue = model.IsHalfDay, DBType = DbType.Boolean
},
new QueryParamObj {
ParamDirection = ParameterDirection.Input, ParamName = "@LeaveTypeId", ParamValue = model.LeaveTypeId, DBType = DbType.Int32
},
new QueryParamObj {
ParamDirection = ParameterDirection.Input, ParamName = "@LeaveReason", ParamValue = model.LeaveReason
},
new QueryParamObj {
ParamDirection = ParameterDirection.Input, ParamName = "@CreatedAt", ParamValue = Convert.ToDateTime(model.CreatedAt), DBType = DbType.DateTime
},
new QueryParamObj {
ParamDirection = ParameterDirection.Input, ParamName = "@IsApproved", ParamValue = model.IsApproved, DBType = DbType.Boolean
},
new QueryParamObj {
ParamDirection = ParameterDirection.Input, ParamName = "@IsRejected", ParamValue = model.IsRejected, DBType = DbType.Boolean
},
new QueryParamObj {
ParamDirection = ParameterDirection.Input, ParamName = "@RejectReason", ParamValue = model.RejectReason
},
new QueryParamObj {
ParamDirection = ParameterDirection.Input, ParamName = "@ApprovedById", ParamValue = model.ApprovedById
},
new QueryParamObj {
ParamDirection = ParameterDirection.Input, ParamName = "@ApprovedAt", ParamValue = model.ApprovedAt, DBType = DbType.DateTime
},
};
const string sql = @"
DECLARE @employeeId INT
SELECT TOP 1 @employeeId=U.Id FROM ResourceTracker_EmployeeUser U WHERE U.UserId=@userId
INSERT INTO ResourceTracker_LeaveApplication (CompanyId ,EmployeeId ,FromDate ,ToDate ,IsHalfDay,LeaveTypeId ,LeaveReason,CreatedAt,IsApproved,IsRejected,RejectReason,ApprovedById,ApprovedAt)
VALUES (@CompanyId ,@employeeId ,@FromDate ,@ToDate ,@IsHalfDay,@LeaveTypeId ,@LeaveReason,@CreatedAt,@IsApproved,@IsRejected,@RejectReason,@ApprovedById,@ApprovedAt)";
return(DBExecCommandExTran(sql, queryParamList, trans, db, ref errMessage));
}
public List <TaskAttachment> GetTaskAttachments(string taskId)
{
const string sql = @"SELECT * FROM ResourceTracker_TaskAttachments where TaskId=@taskId";
var queryParamList = new QueryParamList
{
new QueryParamObj {
ParamDirection = ParameterDirection.Input, ParamName = "@taskId", ParamValue = taskId
}
};
return(ExecuteDBQuery(sql, queryParamList, EmployeeTaskMapper.ToTaskAttachment));
}
public List <T> ExecuteDBQuery <T>(string query, QueryParamList queryParams, Func <DbDataReader, List <T> > populateData, bool addToCache, string cacheKey)
{
Database db = GetSQLDatabase();
DbCommand command = db.GetSqlStringCommand(query);
if (command.Connection == null)
{
command.Connection = db.CreateConnection();
}
if (queryParams != null)
{
foreach (QueryParamObj obj in queryParams)
{
db.AddInParameter(command, obj.ParamName, obj.DBType, obj.ParamValue);
}
}
DbDataReader reader = null;
List <T> list = new List <T>();
try
{
if (command.Connection.State != ConnectionState.Open)
{
command.Connection.Open();
}
SqlDependency dependency = null;
if (addToCache)
{
dependency = new SqlDependency(command as SqlCommand);
}
reader = command.ExecuteReader(CommandBehavior.SingleResult);
list = populateData(reader);
if (addToCache)
{
// InMemoryCache.CacheService.Set(cacheKey, list, dependency);
}
}
catch (Exception e)
{
//Logger.LogException(e);
}
finally
{
if (reader != null)
{
reader.Close();
}
CloseConnection(command);
}
return(list);
}
public string GenerateSignature(RestRequest request, QueryParamList parameters)
{
if (request == null) { throw new ArgumentNullException("request"); }
var sb = new StringBuilder();
sb.Append(Enum.GetName(typeof(RestMethod), request.Method).ToUpperInvariant());
sb.Append("\n");
sb.Append(request.ApiHost);
sb.Append("\n");
sb.Append("/" + request.EndpointName);
sb.Append("\n");
sb.Append(parameters.Concat());
var toSign = sb.ToString();
return GetHmacSha256(toSign, request.SecretKey);
}
private RestRequest CreateRequest(RestMethod method, string endpoint, object data = null, QueryParamList parameters = null, string factoryId = null)
{
var access = _apiAccess;
var request = new RestRequest();
request.Method = method;
request.Endpoint = endpoint;
request.TimeStamp = DateTime.Now;
request.FactoryId = factoryId;
request.Data = data;
request.Params = parameters;
request.ApiHost = access.Host;
request.ApiVersion = access.Version;
request.ApiPort = access.Port;
request.AccessKey = access.AccessKey;
request.SecretKey = access.SecretKey;
return request;
}
public RestRequest Put(string endpoint, QueryParamList parameters)
{
var request = CreateRequest(RestMethod.Put, endpoint, parameters: parameters);
return request;
}
public RestRequest Get(string endpoint, QueryParamList parameters = null, string factoryId = null)
{
var request = CreateRequest(RestMethod.Get, endpoint, parameters: parameters, factoryId: factoryId);
return request;
}
