/// <summary>
/// Gets an appointment
/// </summary>
/// <param name="lscId"></param>
/// <param name="lscName"></param>
/// <param name="id"></param>
/// <param name="connectionString"></param>
/// <returns></returns>
public AppointmentInfo GetAppointment(int lscId, string lscName, int id, string connectionString)
{
SqlParameter[] parms = { new SqlParameter("@Id", id) };
AppointmentInfo appointment = null;
SqlHelper.TestConnection(connectionString);
using (var rdr = SqlHelper.ExecuteReader(connectionString, CommandType.Text, SqlText.Sql_Appointment_Get, parms)) {
if (rdr.Read())
{
appointment = new AppointmentInfo();
appointment.LscID = lscId;
appointment.LscName = lscName;
appointment.Id = ComUtility.DBNullInt32Handler(rdr["Id"]);
appointment.StartTime = ComUtility.DBNullDateTimeHandler(rdr["StartTime"]);
appointment.EndTime = ComUtility.DBNullDateTimeHandler(rdr["EndTime"]);
appointment.LscIncluded = ComUtility.DBNullInt32Handler(rdr["LscIncluded"]);
appointment.StaIncluded = ComUtility.DBNullStringHandler(rdr["StaIncluded"]);
appointment.DevIncluded = ComUtility.DBNullStringHandler(rdr["DevIncluded"]);
appointment.ProjectId = ComUtility.DBNullStringHandler(rdr["ProjectId"]);
appointment.ProjectName = ComUtility.DBNullStringHandler(rdr["ProjectName"]);
appointment.Status = ComUtility.DBNullProjStatusHandler(rdr["Status"]);
appointment.CreaterId = ComUtility.DBNullInt32Handler(rdr["CreaterId"]);
appointment.Creater = ComUtility.DBNullStringHandler(rdr["Creater"]);
appointment.ContactPhone = ComUtility.DBNullStringHandler(rdr["ContactPhone"]);
appointment.CreatedTime = ComUtility.DBNullDateTimeHandler(rdr["CreatedTime"]);
}
}
return(appointment);
}
/// <summary>
/// Gets history appointment records.
/// </summary>
/// <param name="lscId">lscId</param>
/// <param name="lscName">lscName</param>
/// <param name="beginTime">beginTime</param>
/// <param name="endTime">endTime</param>
/// <param name="queryType">queryType</param>
/// <param name="queryText">queryText</param>
/// <returns></returns>
public List <AppointmentInfo> GetHisAppointments(int lscId, string lscName, DateTime beginTime, DateTime endTime, int queryType, string queryText)
{
SqlParameter[] parms = { new SqlParameter("@LscID", SqlDbType.Int),
new SqlParameter("@BeginTime", SqlDbType.DateTime),
new SqlParameter("@EndTime", SqlDbType.DateTime),
new SqlParameter("@QueryType", SqlDbType.Int),
new SqlParameter("@QueryText", SqlDbType.NVarChar, 100) };
parms[0].Value = lscId;
parms[1].Value = beginTime;
parms[2].Value = endTime;
parms[3].Value = queryType;
parms[4].Value = DBNull.Value;
if (queryText != null && queryText.Trim() != String.Empty)
{
parms[4].Value = queryText.Trim();
}
var appointments = new List <AppointmentInfo>();
using (var rdr = SqlHelper.ExecuteReader(SqlHelper.HisConnectionStringLocalTransaction, CommandType.Text, SqlText.Sql_Appointment_Get_His, parms)) {
while (rdr.Read())
{
var appointment = new AppointmentInfo();
appointment.LscID = lscId;
appointment.LscName = lscName;
appointment.Id = ComUtility.DBNullInt32Handler(rdr["Id"]);
appointment.StartTime = ComUtility.DBNullDateTimeHandler(rdr["StartTime"]);
appointment.EndTime = ComUtility.DBNullDateTimeHandler(rdr["EndTime"]);
appointment.LscIncluded = ComUtility.DBNullInt32Handler(rdr["LscIncluded"]);
appointment.StaIncluded = ComUtility.DBNullStringHandler(rdr["StaIncluded"]);
appointment.DevIncluded = ComUtility.DBNullStringHandler(rdr["DevIncluded"]);
appointment.ProjectId = ComUtility.DBNullStringHandler(rdr["ProjectId"]);
appointment.ProjectName = ComUtility.DBNullStringHandler(rdr["ProjectName"]);
appointment.Status = ComUtility.DBNullProjStatusHandler(rdr["Status"]);
appointment.CreaterId = ComUtility.DBNullInt32Handler(rdr["CreaterId"]);
appointment.Creater = ComUtility.DBNullStringHandler(rdr["Creater"]);
appointment.ContactPhone = ComUtility.DBNullStringHandler(rdr["ContactPhone"]);
appointment.CreatedTime = ComUtility.DBNullDateTimeHandler(rdr["CreatedTime"]);
appointment.RecordTime = ComUtility.DBNullDateTimeHandler(rdr["RecordTime"]);
appointments.Add(appointment);
}
}
return(appointments);
}
/// <summary>
/// Gets all appointments
/// </summary>
/// <param name="lscId"></param>
/// <param name="lscName"></param>
/// <param name="connectionString"></param>
/// <param name="beginTime"></param>
/// <param name="endTime"></param>
/// <param name="queryType"></param>
/// <param name="queryText"></param>
/// <returns></returns>
public List <AppointmentInfo> GetAppointments(int lscId, string lscName, string connectionString, DateTime beginTime, DateTime endTime, int queryType, string queryText)
{
SqlParameter[] parms = { new SqlParameter("@StartTime", SqlDbType.DateTime), new SqlParameter("@EndTime", SqlDbType.DateTime) };
parms[0].Value = beginTime;
parms[1].Value = endTime;
var query = @"
SELECT PB.[BookingID] AS [Id],PB.[StartTime],PB.[EndTime],PB.[LscIncluded],PB.[StaIncluded],PB.[DevIncluded]
,PB.[ProjID] AS [ProjectId],PB.[ProjName] AS [ProjectName],PB.[ProjStatus] AS [Status],PB.[BookingUserID] AS [CreaterId],U.[UserName] AS [Creater]
,U.[MobilePhone] AS [ContactPhone],PB.[BookingTime] AS [CreatedTime] FROM [dbo].[TM_ProjBooking] PB
LEFT OUTER JOIN [dbo].[TU_User] U ON PB.[BookingUserID]=U.[UserID] WHERE PB.[StartTime] BETWEEN @StartTime AND @EndTime";
if (queryText != null && queryText.Trim() != String.Empty)
{
if (queryType == 0)
{
query += @" AND PB.[ProjID] LIKE '%" + queryText + @"%'";
}
else if (queryType == 1)
{
query += @" AND PB.[ProjName] LIKE '%" + queryText + @"%'";
}
else if (queryType == 2)
{
query += @" AND PB.[LscIncluded] LIKE '" + queryText + @"'";
}
else if (queryType == 3)
{
query += @" AND PB.[StaIncluded] LIKE '%" + queryText + @"%'";
}
else if (queryType == 4)
{
query += @" AND PB.[DevIncluded] LIKE '%" + queryText + @"%'";
}
}
query += @" ORDER BY PB.[BookingID]";
var appointments = new List <AppointmentInfo>();
SqlHelper.TestConnection(connectionString);
using (var rdr = SqlHelper.ExecuteReader(connectionString, CommandType.Text, query, parms)) {
while (rdr.Read())
{
var appointment = new AppointmentInfo();
appointment.LscID = lscId;
appointment.LscName = lscName;
appointment.Id = ComUtility.DBNullInt32Handler(rdr["Id"]);
appointment.StartTime = ComUtility.DBNullDateTimeHandler(rdr["StartTime"]);
appointment.EndTime = ComUtility.DBNullDateTimeHandler(rdr["EndTime"]);
appointment.LscIncluded = ComUtility.DBNullInt32Handler(rdr["LscIncluded"]);
appointment.StaIncluded = ComUtility.DBNullStringHandler(rdr["StaIncluded"]);
appointment.DevIncluded = ComUtility.DBNullStringHandler(rdr["DevIncluded"]);
appointment.ProjectId = ComUtility.DBNullStringHandler(rdr["ProjectId"]);
appointment.ProjectName = ComUtility.DBNullStringHandler(rdr["ProjectName"]);
appointment.Status = ComUtility.DBNullProjStatusHandler(rdr["Status"]);
appointment.CreaterId = ComUtility.DBNullInt32Handler(rdr["CreaterId"]);
appointment.Creater = ComUtility.DBNullStringHandler(rdr["Creater"]);
appointment.ContactPhone = ComUtility.DBNullStringHandler(rdr["ContactPhone"]);
appointment.CreatedTime = ComUtility.DBNullDateTimeHandler(rdr["CreatedTime"]);
appointments.Add(appointment);
}
}
return(appointments);
}