public SmartCollection<ClientNote> GetClientNotes(int clientId)
{
try {
SmartCollection<ClientNote> resultList = new SmartCollection<ClientNote>();
using (DbConnection = new MsSqlPersistence(DbConnectionSettings)) {
if (DbConnection.IsConnected()) {
using (DbCommand) {
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = "uspGetClientNotes";
dbCommand.Parameters.Clear();
dbCommand.Parameters.Add("@ClientId", System.Data.SqlDbType.Int).Value = clientId;
DataTable notesDT = DbConnection.ExecuteQuery(DbCommand);
foreach (DataRow row in notesDT.Rows) {
ClientNote note = new ClientNote();
note.ClientNoteId = Convert.ToInt32(row["ClientNoteID"]);
note.ClientId = Convert.ToInt32(row["ClientID"]);
note.Note = row["Note"].ToString();
note.CopyToSampleYN = Convert.ToBoolean(row["CopyToSampleYN"]);
note.IncludeOnCOAYN = Convert.ToBoolean(row["IncludeOnCOAYN"]);
note.CreatedUser = row["CreatedUser"].ToString();
note.CreatedDate = row["CreatedDate"] != DBNull.Value ? (DateTime)row["CreatedDate"] : (DateTime)SqlDateTime.Null;
note.ModifiedBy = row["ModifiedBy"] != DBNull.Value ? Convert.ToInt32(row["ModifiedBy"]) : -1;
note.ModifiedUser = row["ModifiedUser"].ToString();
note.ModifiedDate = row["ModifiedDate"] != DBNull.Value ? (DateTime)row["ModifiedDate"] : (DateTime)SqlDateTime.Null;
resultList.Add(note);
}
notesDT = null;
}
}else {
throw new Exception("Unable to Connect");
}
}
return resultList;
}catch {
throw;
}
}
public int SaveClientNote(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, ClientNote note, ref Client client, int userId)
{
try {
int returnValue = 0;
string sql = string.Empty;
if (note.IsDirty) {
SystemDAO.SaveChangeAudit<ClientNote>(ref dbConnection, ref dbCommand,
GetClientNote(ref dbConnection, ref dbCommand, note.Pk),
note,
ModuleNames.Clients,
client.Pk,
userId);
dbCommand.Parameters.Clear();
sql = string.Empty;
if (note.ClientNoteId <= 0 || note.ClientNoteId.IsNull()) {
dbCommand.CommandText = "uspInsertClientNote";
dbCommand.Parameters.Add("@ClientId", System.Data.SqlDbType.Int).Value = client.ClientId;
dbCommand.Parameters.Add("@CreatedBy", System.Data.SqlDbType.Int).Value = userId;
dbCommand.Parameters.Add("@CreatedDate", System.Data.SqlDbType.Int).Value = DateTime.Now;
}else {
dbCommand.CommandText = "uspUpdateClientNote";
dbCommand.Parameters.Add("@ClientNoteId", System.Data.SqlDbType.Int).Value = note.ClientNoteId;
dbCommand.Parameters.Add("@ModifiedBy", System.Data.SqlDbType.Int).Value = userId;
dbCommand.Parameters.Add("@ModifiedDate", System.Data.SqlDbType.Int).Value = DateTime.Now;
}
dbCommand.Parameters.Add("@Note", System.Data.SqlDbType.NText).Value = note.Note;
dbCommand.Parameters.Add("@CopyToSampleYN", System.Data.SqlDbType.Bit).Value = note.CopyToSampleYN;
dbCommand.Parameters.Add("@IncludeOnCOAYN", System.Data.SqlDbType.Bit).Value = note.IncludeOnCOAYN;
returnValue += dbConnection.ExecuteCommand(dbCommand);
}
//Return Total Number of Inserted or Updated Records
return returnValue;
}catch {
throw;
}
}
public ClientNote GetClientNote(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, int? noteId)
{
try {
ClientNote note = new ClientNote();
if (dbConnection.IsConnected()) {
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = "uspGetClientNote";
dbCommand.Parameters.Clear();
dbCommand.Parameters.Add("@ClientNoteId", System.Data.SqlDbType.Int).Value = noteId;
DataTable notesDT = dbConnection.ExecuteQuery(dbCommand);
if (notesDT.Rows.Count == 1) {
DataRow row = notesDT.Rows[0];
note.ClientNoteId = Convert.ToInt32(row["ClientNoteID"]);
note.ClientId = Convert.ToInt32(row["ClientID"]);
note.Note = row["Note"].ToString();
note.CopyToSampleYN = Convert.ToBoolean(row["CopyToSampleYN"]);
note.IncludeOnCOAYN = Convert.ToBoolean(row["IncludeOnCOAYN"]);
note.CreatedUser = row["CreatedUser"].ToString();
note.CreatedDate = row["CreatedDate"] != DBNull.Value ? (DateTime)row["CreatedDate"] : (DateTime)SqlDateTime.Null;
note.ModifiedBy = row["ModifiedBy"] != DBNull.Value ? Convert.ToInt32(row["ModifiedBy"]) : -1;
note.ModifiedUser = row["ModifiedUser"].ToString();
note.ModifiedDate = row["ModifiedDate"] != DBNull.Value ? (DateTime)row["ModifiedDate"] : (DateTime)SqlDateTime.Null;
notesDT = null;
}else {
notesDT = null;
return null;
}
}else {
throw new Exception("Unable to Connect");
}
return note;
}catch {
throw;
}
}
public int SaveInventoryNote(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, ClientNote note, ref InventoryItem inventoryItem, int userId)
{
try
{
int returnValue = 0;
string sql = string.Empty;
if (note.IsDirty)
{
SystemDAO.SaveChangeAudit<ClientNote>(ref dbConnection, ref dbCommand,
GetInventoryNote(ref dbConnection, ref dbCommand, note.Pk),
note,
ModuleNames.Inventory,
inventoryItem.Pk,
userId);
dbCommand.Parameters.Clear();
sql = string.Empty;
if (note.ClientNoteId <= 0 || note.ClientNoteId.IsNull())
{
sql = "INSERT into inventory_notes (parentid, txt, html,created_by) " +
"Values(@ParentId,@Txt, @Html, @CreatedBy) ";
dbCommand.Parameters.Add("@ParentId", System.Data.SqlDbType.Int).Value = note.ClientId;
dbCommand.Parameters.Add("@CreatedBy", System.Data.SqlDbType.Int).Value = userId;
}
else
{
sql = "UPDATE inventory_notes " +
"SET txt = @Txt, html = @Html, delete_date = @DeleteDate, modified_by = @ModifiedBy " +
"WHERE id = @ID";
dbCommand.Parameters.Add("@ID", System.Data.SqlDbType.Int).Value = note.ClientNoteId;
dbCommand.Parameters.Add("@DeleteDate", System.Data.SqlDbType.DateTime).Value = note.DeleteDate.HasValue ? note.DeleteDate.Value : SqlDateTime.Null;
dbCommand.Parameters.Add("@ModifiedBy", System.Data.SqlDbType.Int).Value = userId;
}
dbCommand.CommandText = sql;
dbCommand.Parameters.Add("@Txt", System.Data.SqlDbType.NText).Value = note.Note ?? string.Empty;
returnValue += dbConnection.ExecuteCommand(dbCommand);
}
//Return Total Number of Inserted or Updated Records
return returnValue;
}
catch
{
throw;
}
}
public SmartCollection<ClientNote> GetInventoryNotes(int parentId)
{
try
{
SmartCollection<ClientNote> resultList = new SmartCollection<ClientNote>();
using (DbConnection = new MsSqlPersistence(DbConnectionSettings))
{
if (DbConnection.IsConnected())
{
using (DbCommand)
{
string sql =
@"
select inventory_notes.id, inventory_notes.parentid, inventory_notes.txt, inventory_notes.html,
(users.firstname + ' ' + users.lastname) as modifieduser,
inventory_notes.modified_by, inventory_notes.modified_date, inventory_notes.created_by,
inventory_notes.created_date, (users2.firstname + ' ' + users2.lastname) as createduser
from inventory_notes
LEFT JOIN users ON inventory_notes.modified_by = users.id
LEFT JOIN users as users2 ON inventory_notes.created_by = users2.id
where inventory_notes.parentid = @ID AND inventory_notes.delete_date IS NULL
order by inventory_notes.created_date DESC
";
DbCommand.Parameters.Clear();
DbCommand.Parameters.Add("@ID", System.Data.SqlDbType.Int).Value = parentId;
DbCommand.CommandText = sql;
DataTable notesDT = DbConnection.ExecuteQuery(DbCommand);
foreach (DataRow row in notesDT.Rows)
{
ClientNote note = new ClientNote();
note.ClientNoteId = Convert.ToInt32(row["Id"]);
note.ClientId = Convert.ToInt32(row["ParentId"]);
note.Note = row["txt"].ToString();
note.CreatedBy = row["created_by"] != DBNull.Value ? Convert.ToInt32(row["created_by"]) : 0;
note.CreatedUser = row["createduser"].ToString();
note.CreatedDate = row["created_date"] != DBNull.Value ? (DateTime)row["created_date"] : (DateTime)SqlDateTime.Null;
note.ModifiedBy = row["modified_by"] != DBNull.Value ? Convert.ToInt32(row["modified_by"]) : 0;
note.ModifiedUser = row["modifieduser"].ToString();
note.ModifiedDate = row["modified_date"] != DBNull.Value ? (DateTime)row["modified_date"] : (DateTime)SqlDateTime.Null;
resultList.Add(note);
}
notesDT = null;
}
}
else
{
throw new Exception("Unable to Connect");
}
}
return resultList;
}
catch
{
throw;
}
}
public ClientNote GetInventoryNote(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, int? noteId)
{
try
{
ClientNote note = new ClientNote();
if (dbConnection.IsConnected())
{
string sql = @"
select inventory_notes.id, inventory_notes.parentid, inventory_notes.txt, inventory_notes.html,
(users.firstname + ' ' + users.lastname) as modifieduser,
inventory_notes.modified_by, inventory_notes.modified_date, inventory_notes.created_by,
inventory_notes.created_date, (users2.firstname + ' ' + users2.lastname) as createduser
from inventory_notes
LEFT JOIN users ON inventory_notes.modified_by = users.id
LEFT JOIN users as users2 ON inventory_notes.created_by = users2.id
where inventory_notes.id = @ID AND inventory_notes.delete_date IS NULL
order by inventory_notes.created_date DESC
";
dbCommand.Parameters.Clear();
dbCommand.Parameters.Add("@ID", System.Data.SqlDbType.Int).Value = noteId;
dbCommand.CommandText = sql;
DataTable notesDT = dbConnection.ExecuteQuery(dbCommand);
if (notesDT.Rows.Count == 1)
{
DataRow row = notesDT.Rows[0];
note.ClientNoteId = Convert.ToInt32(row["Id"]);
note.ClientId = Convert.ToInt32(row["ParentId"]);
note.Note = row["txt"].ToString();
note.CreatedBy = row["created_by"] != DBNull.Value ? Convert.ToInt32(row["created_by"]) : 0;
note.CreatedUser = row["createduser"].ToString();
note.CreatedDate = row["created_date"] != DBNull.Value ? (DateTime?)row["created_date"] : null;
note.ModifiedBy = row["modified_by"] != DBNull.Value ? Convert.ToInt32(row["modified_by"]) : 0;
note.ModifiedUser = row["modifieduser"].ToString();
note.ModifiedDate = row["modified_date"] != DBNull.Value ? (DateTime?)row["modified_date"] : null;
notesDT = null;
}
else
{
notesDT = null;
return null;
}
}
else
{
throw new Exception("Unable to Connect");
}
return note;
}
catch
{
throw;
}
}
public ReinstateOrderSampleTestRequest(int id, ClientNote note)
{
this.id = id;
this.note = note;
}
public ReturnToOosRequest(TimePoint timePoint, ClientNote requiredNote)
{
this.timePoint = timePoint;
this.requiredNote = requiredNote;
}
