public static bool Delete(int Id)
{
PurchaseRequest purchaseRequest = PurchaseRequest.Select(Id);
if (purchaseRequest == null)
{
return(false);
}
bool rc = PurchaseRequest.Delete(purchaseRequest);
return(rc);
}
private static void AddSqlInsertUpdateParameters(SqlCommand Cmd, PurchaseRequest purchaseRequest)
{
Cmd.Parameters.Add(new SqlParameter("@UserId", purchaseRequest.UserId));
Cmd.Parameters.Add(new SqlParameter("@Description", purchaseRequest.Description));
Cmd.Parameters.Add(new SqlParameter("@Justification", purchaseRequest.Justification));
Cmd.Parameters.Add(new SqlParameter("@DateNeeded", purchaseRequest.DateNeeded));
Cmd.Parameters.Add(new SqlParameter("@DeliveryMode", purchaseRequest.DeliveryMode));
Cmd.Parameters.Add(new SqlParameter("@DocsAttached", purchaseRequest.DocsAttached));
Cmd.Parameters.Add(new SqlParameter("@Status", purchaseRequest.Status));
Cmd.Parameters.Add(new SqlParameter("@Total", purchaseRequest.Total));
Cmd.Parameters.Add(new SqlParameter("@SubmittedDate", purchaseRequest.SubmittedDate));
}
public static bool Delete(PurchaseRequest purchaseRequest)
{
string Sql = string.Format("DELETE from [purchaseRequest] where ID = @id");
//string ConnStr = @"Server=DSI-WORKSTATION\SQLEXPRESS;Database=prs;Trusted_Connection=True;";
SqlCommand Cmd = CreateConnection(ConnStr, Sql, "Connection didn't open");
Cmd.Parameters.Add(new SqlParameter("@id", purchaseRequest.Id));
int recsAffected = ExecuteSqlInsUpdDelCommand(Cmd, "Delete Failed!");
Cmd.Connection.Close();
return(recsAffected == 1);
}
public static PurchaseRequestCollection Select(string WhereClause, string OrderByClause)
{
string Sql = string.Format("SELECT * from [purchaseRequest] WHERE {0} ORDER BY {1}", WhereClause, OrderByClause);
string ConnStr = @"Server=STUDENT05;Database=prs;Trusted_Connection=True;";
SqlConnection Conn = new SqlConnection(ConnStr);
Conn.Open();
if (Conn.State != System.Data.ConnectionState.Open)
{
throw new ApplicationException("Connection didn't open");
}
SqlCommand Cmd = new SqlCommand(Sql, Conn);
SqlDataReader Reader = Cmd.ExecuteReader();
if (!Reader.HasRows)
{
throw new ApplicationException("Result set has no rows!");
}
PurchaseRequestCollection purchaseRequests = new PurchaseRequestCollection();
while (Reader.Read())
{
int id = Reader.GetInt32(Reader.GetOrdinal("Id"));
int userid = Reader.GetInt32(Reader.GetOrdinal("UserId"));
string user = Reader.GetString(Reader.GetOrdinal("User"));
string description = Reader.GetString(Reader.GetOrdinal("Description"));
string justification = Reader.GetString(Reader.GetOrdinal("Justification"));
DateTime dateneeded = Reader.GetDateTime(Reader.GetOrdinal("DateNeeded"));
string deliverymode = Reader.GetString(Reader.GetOrdinal("DeliveryMode"));
bool docsattached = Reader.GetBoolean(Reader.GetOrdinal("DocsAttached"));
string status = Reader.GetString(Reader.GetOrdinal("Status"));
decimal total = Reader.GetDecimal(Reader.GetOrdinal("Total"));
DateTime submitteddate = Reader.GetDateTime(Reader.GetOrdinal("SubmittedDate"));
PurchaseRequest purchaseRequest = new PurchaseRequest();
purchaseRequest.Id = id;
purchaseRequest.UserId = userid;
purchaseRequest.Description = description;
purchaseRequest.Justification = justification;
purchaseRequest.DateNeeded = dateneeded;
purchaseRequest.DeliveryMode = deliverymode;
purchaseRequest.DocsAttached = docsattached;
purchaseRequest.Status = status;
purchaseRequest.Total = total;
purchaseRequest.SubmittedDate = submitteddate;
purchaseRequest.User = User.Select(purchaseRequest.UserId);
purchaseRequests.Add(purchaseRequest);
}
return(purchaseRequests);
}
private static void AddSqlInsertUpdateParameters(SqlCommand Cmd, PurchaseRequest purchaseRequest)
{
Cmd.Parameters.Add(new SqlParameter("@id", purchaseRequest.Id));
Cmd.Parameters.Add(new SqlParameter("@userid", purchaseRequest.UserId));
Cmd.Parameters.Add(new SqlParameter("@user", purchaseRequest.User));
Cmd.Parameters.Add(new SqlParameter("@description", purchaseRequest.Description));
Cmd.Parameters.Add(new SqlParameter("@justification", purchaseRequest.Justification));
Cmd.Parameters.Add(new SqlParameter("@dateneeded", purchaseRequest.DateNeeded));
Cmd.Parameters.Add(new SqlParameter("@deliverymode", purchaseRequest.DeliveryMode));
Cmd.Parameters.Add(new SqlParameter("@docsattached", purchaseRequest.DocsAttached));
Cmd.Parameters.Add(new SqlParameter("@status", purchaseRequest.Status));
Cmd.Parameters.Add(new SqlParameter("@total", purchaseRequest.Total));
Cmd.Parameters.Add(new SqlParameter("@submitteddate", purchaseRequest.SubmittedDate));
}
public static PurchaseRequestCollection Select(string WhereClause, string OrderByClause)
{
string Sql = string.Format("SELECT * from [purchaseRequest] WHERE ({0}) ORDER BY {1}", WhereClause, OrderByClause);
//string ConnStr = @"Server=DSI-WORKSTATION\SQLEXPRESS;Database=prs;Trusted_Connection=True;";
SqlCommand Cmd = CreateConnection(ConnStr, Sql, "Connection didn't open");
SqlDataReader Reader = Cmd.ExecuteReader();
//if (!Reader.HasRows) {
// throw new ApplicationException("Result set has no rows!");
//}
PurchaseRequestCollection purchaseRequests = new PurchaseRequestCollection();
while (Reader.Read()) //this while loop is where the data is pulled from the database and put in the purchase request class instance
{
int id = Reader.GetInt32(Reader.GetOrdinal("Id"));
int userId = Reader.GetInt32(Reader.GetOrdinal("UserId"));
string description = Reader.GetString(Reader.GetOrdinal("Description"));
string justification = Reader.GetString(Reader.GetOrdinal("Justification"));
DateTime dateNeeded = Reader.GetDateTime(Reader.GetOrdinal("DateNeeded"));
string deliveryMode = Reader.GetString(Reader.GetOrdinal("DeliveryMode"));
bool docsAttached = Reader.GetBoolean(Reader.GetOrdinal("DocsAttached"));
string status = Reader.GetString(Reader.GetOrdinal("Status"));
decimal total = Reader.GetDecimal(Reader.GetOrdinal("Total"));
DateTime submittedDate = Reader.GetDateTime(Reader.GetOrdinal("SubmittedDate"));
PurchaseRequest purchaseRequest = new PurchaseRequest();
purchaseRequest.Id = id;
purchaseRequest.UserId = userId;
purchaseRequest.Description = description;
purchaseRequest.Justification = justification;
purchaseRequest.DateNeeded = dateNeeded;
purchaseRequest.DeliveryMode = deliveryMode;
purchaseRequest.DocsAttached = docsAttached;
purchaseRequest.Status = status;
purchaseRequest.Total = total;
purchaseRequest.SubmittedDate = submittedDate;
// get the user
purchaseRequest.User = User.Select(purchaseRequest.UserId);
//get the line items
purchaseRequest.LineItems = GetLineItems(purchaseRequest.Id);
purchaseRequests.Add(purchaseRequest);
}
Cmd.Connection.Close();
return(purchaseRequests);
}
public bool AddLineItem(int ProductId, int Quantity)
{
Product product = Product.Select(ProductId);
LineItem lineItem = new LineItem {
PurchaseRequestId = this.Id,
ProductId = ProductId,
Quantity = Quantity
};
bool rc = LineItem.Insert(lineItem);
if (!rc)
{
throw new ApplicationException("Insert of line item failed!");
}
this.Total += Quantity * product.Price;
rc = PurchaseRequest.Update(this);
return(rc);
}
public static bool Insert(PurchaseRequest purchaseRequest)
{
string Sql = string.Format("insert into [purchaseRequest] " +
" (UserId, Description, Justification, DateNeeded, DeliveryMode, DocsAttached, Status, Total, SubmittedDate) " +
" values " +
" (@UserId, @Description, @Justification, @DateNeeded, @DeliveryMode, @DocsAttached, @Status, @Total, @SubmittedDate)");
//string ConnStr = @"Server=DSI-WORKSTATION\SQLEXPRESS;Database=prs;Trusted_Connection=True;";
SqlCommand Cmd = CreateConnection(ConnStr, Sql, "Connection didn't open");
AddSqlInsertUpdateParameters(Cmd, purchaseRequest);
int recsAffected = ExecuteSqlInsUpdDelCommand(Cmd, "Insert Failed!");
// get the last id inserted
purchaseRequest.Id = GetLastIdGenerated(ConnStr, "purchaseRequest");
purchaseRequest.User = User.Select(purchaseRequest.UserId);
Cmd.Connection.Close();
return(recsAffected == 1);
}
public static bool Insert(LineItem lineItem)
{
string Sql = string.Format("insert into [lineItem] " +
" (PurchaseRequestId, ProductId, Quantity) " +
" values " +
" (@PurchaseRequestId, @ProductId, @Quantity)");
//string ConnStr =@"Server=STUDENT05;Database=prs;Trusted_Connection=True;";
SqlCommand Cmd = CreateConnection(ConnStr, Sql, "Connection didn't open");
AddSqlInsertUpdateParameters(Cmd, lineItem);
int recsAffected = ExecuteSqlInsUpdDelCommand(Cmd, "Insert Failed!");
// get the last id inserted
lineItem.Id = GetLastIdGenerated(ConnStr, "lineItem");
lineItem.PurchaseRequest = PurchaseRequest.Select(lineItem.PurchaseRequestId);
lineItem.Product = Product.Select(lineItem.ProductId);
Cmd.Connection.Close();
return(recsAffected == 1);
}
//DELETE
public static bool Delete(PurchaseRequest purchaseRequest)
{
string Sql = string.Format("DELETE from [PurchaseReauest] WHERE ID = @id");
string ConnStr = @"Server=STUDENT05;Database=prs;Trusted_Connection=True;";
SqlConnection Conn = new SqlConnection(ConnStr);
Conn.Open();
if (Conn.State != System.Data.ConnectionState.Open)
{
throw new ApplicationException("Connection didn't open");
}
SqlCommand Cmd = new SqlCommand(Sql, Conn);
Cmd.Parameters.Add(new SqlParameter("id", purchaseRequest.Id));
int recsAffected = Cmd.ExecuteNonQuery();
if (recsAffected != 1)
{
return(true);
}
return(recsAffected == 1);
}
public bool DeleteLineItem(int LineItemId)
{
LineItem lineItem = LineItem.Select(LineItemId);
if (lineItem == null)
{
throw new ApplicationException("Line item to delete is not found");
}
decimal amount = lineItem.Product.Price * lineItem.Quantity;
bool rc = LineItem.Delete(lineItem);
if (!rc)
{
throw new ApplicationException("Line item delete failed!");
}
this.Total -= amount;
rc = PurchaseRequest.Update(this);
if (!rc)
{
throw new ApplicationException("Purchase Request update failed!");
}
return(rc);
}
public static bool Update(PurchaseRequest purchaseRequest)
{
string Sql = string.Format("UPDATE [purchaseRequest] Set " +
" UserId = @UserId, " +
" Description = @Description, " +
" Justification = @Justification, " +
" DateNeeded = @DateNeeded, " +
" DeliveryMode = @DeliveryMode, " +
" DocsAttached = @DocsAttached, " +
" Status = @Status, " +
" Total = @Total, " +
" SubmittedDate = @SubmittedDate " +
" WHERE ID = @Id; ");
//string ConnStr = @"Server=DSI-WORKSTATION\SQLEXPRESS;Database=prs;Trusted_Connection=True;";
SqlCommand Cmd = CreateConnection(ConnStr, Sql, "Connection didn't open");
Cmd.Parameters.Add(new SqlParameter("@id", purchaseRequest.Id));
AddSqlInsertUpdateParameters(Cmd, purchaseRequest);
int recsAffected = ExecuteSqlInsUpdDelCommand(Cmd, "Update Failed!");
Cmd.Connection.Close();
return(recsAffected == 1);
}
