/// <summary>
/// List client contracts
/// </summary>
/// <param name="clientID"></param>
internal static List <ClientContract> List(int clientID)
{
List <ClientContract> clientContractList = new List <ClientContract>();
using (var connection = new SqlConnection(ConnString.ConnectionString))
{
var commandString = string.Format(
" SELECT " +
ClientContractFieldsString() +
" FROM [ClientContract] " +
" WHERE FKCompanyUID = {0}",
clientID);
using (var command = new SqlCommand(
commandString, connection))
{
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
ClientContract _clientContract = new ClientContract();
LoadClientContractObject(reader, _clientContract);
clientContractList.Add(_clientContract);
}
}
}
}
return(clientContractList);
}
public static ResponseStatus GetValidContractOnDate(int clientID, DateTime date)
{
ResponseStatus ret = new ResponseStatus();
ret.Message = "Valid contract not found.";
ret.ReturnCode = 0001;
ret.ReasonCode = 0002;
ret.UniqueCode = ResponseStatus.MessageCode.Warning.FCMWAR00000002;
ClientContract clientContract = new ClientContract();
string dateString = date.ToString().Substring(0, 10);
using (var connection = new SqlConnection(ConnString.ConnectionString))
{
var commandString =
" SELECT " +
ClientContractFieldsString() +
" FROM [ClientContract] " +
" WHERE FKCompanyUID = @FKCompanyUID " +
" AND StartDate <= @StartDate " +
" AND EndDate >= @EndDate ";
using (var command = new SqlCommand(commandString, connection))
{
command.Parameters.Add("@FKCompanyUID", SqlDbType.BigInt).Value = clientID;
command.Parameters.Add("@StartDate", SqlDbType.DateTime).Value = date;
command.Parameters.Add("@EndDate", SqlDbType.DateTime).Value = date;
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
ClientContract _clientContract = new ClientContract();
LoadClientContractObject(reader, _clientContract);
clientContract = _clientContract;
ret.Message = "Valid contract found.";
ret.ReturnCode = 0001;
ret.ReasonCode = 0001;
ret.XMessageType = MessageType.Informational;
ret.UniqueCode = ResponseStatus.MessageCode.Informational.FCMINF00000001;
break;
}
}
}
}
ret.Contents = clientContract;
return(ret);
}
// var student1 = new Student{firstName = "Bruce", lastName = "Willis"};
/// <summary>
/// Get Employee details
/// </summary>
internal static ClientContract Read(int clientContractUID)
{
//
// EA SQL database
//
ClientContract clientContract = new ClientContract();
using (var connection = new SqlConnection(ConnString.ConnectionString))
{
var commandString =
" SELECT " +
ClientContractFieldsString()
+ " FROM [ClientContract]" +
" WHERE UID = @UID";
using (var command = new SqlCommand(
commandString, connection))
{
connection.Open();
command.Parameters.Add("@UID", SqlDbType.BigInt).Value = clientContractUID;
SqlDataReader reader = command.ExecuteReader();
if (reader.Read())
{
try
{
LoadClientContractObject(reader, clientContract);
}
catch (Exception)
{
clientContract.UID = 0;
}
}
}
}
return(clientContract);
}
/// <summary>
/// This method loads the information from the sqlreader into the Employee object
/// </summary>
/// <param name="reader"></param>
/// <param name="clientContract"> </param>
private static void LoadClientContractObject(SqlDataReader reader, ClientContract clientContract)
{
clientContract.FKCompanyUID = Convert.ToInt32(reader[FieldName.FKCompanyUID]);
clientContract.UID = Convert.ToInt32(reader[FieldName.UID].ToString());
clientContract.ExternalID = reader[FieldName.ExternalID].ToString();
try { clientContract.Status = reader[FieldName.Status].ToString(); }
catch { clientContract.Status = "ACTIVE"; }
try { clientContract.Type = reader[FieldName.Type].ToString(); }
catch { clientContract.Type = "BASIC"; }
try { clientContract.StartDate = Convert.ToDateTime(reader[FieldName.StartDate].ToString()); }
catch { clientContract.StartDate = DateTime.Now; }
try { clientContract.EndDate = Convert.ToDateTime(reader[FieldName.EndDate].ToString()); }
catch { clientContract.EndDate = DateTime.Now; }
clientContract.UserIdCreatedBy = reader[FieldName.UserIdCreatedBy].ToString();
clientContract.UserIdUpdatedBy = reader[FieldName.UserIdUpdatedBy].ToString();
try { clientContract.UpdateDateTime = Convert.ToDateTime(reader[FieldName.UpdateDateTime].ToString()); }
catch { clientContract.UpdateDateTime = DateTime.Now; }
try { clientContract.CreationDateTime = Convert.ToDateTime(reader[FieldName.CreationDateTime].ToString()); }
catch { clientContract.CreationDateTime = DateTime.Now; }
}
