public static int GetOrderNo() { Object obj = null; try { string sql = "select max(orderNo) maxOr from Orders where UserID=@UserID"; List <SqlParameter> pList = new List <SqlParameter>(); SqlParameter p1 = new SqlParameter("@UserID", SqlDbType.Int); p1.Value = Convert.ToInt32(RepositoryAuth.GetUserID(SessionFacade.LOGGEDIN)); pList.Add(p1); obj = DataAccess.GetSingleAnswer(sql, pList); } catch (Exception) { throw; } return((int)obj); }
public static bool AddOrder() { string user = SessionFacade.LOGGEDIN; int UID = RepositoryAuth.GetUserID(user); bool res = false; try { string sql = "if not exists(select 1 from Orders ord inner join (select max(orderNo) maxOr from Orders where UserID=" + UID + ")or1 on (or1.maxor=ord.orderno) where TotalCost=0 and TotalQty=0) insert into Orders(OrderDate,TotalQty,TotalCost,UserID) select getdate(),0,0," + UID; int rows = DataAccess.InsertUpdateDelete(sql, null); if (rows > 0) { res = true; } } catch (Exception) { throw; } return(res); }
public static bool UpdateOrder() { bool res = false; string user = SessionFacade.LOGGEDIN; int UID = RepositoryAuth.GetUserID(user); int OrderNo = GetOrderNo(); try { string sql = "update ord set ord.totalqty=ordet.Qty, ord.totalcost=ordet.Price from orders ord inner join (select sum(Qty) Qty,sum(Price) Price,OrderNo from OrderDetails group by OrderNo)ordet on (ord.orderNo=ordet.OrderNo) where ord.UserID=" + UID + " and ord.OrderNo=" + OrderNo; int rows = DataAccess.InsertUpdateDelete(sql, null); if (rows > 0) { res = true; } } catch (Exception) { throw; } return(res); }