コード例 #1
0
        public List<Order> GetAllOrder()
        {
            //...Create New Instance of Object...
            List<Order> list = new List<Order>();
            Order ins;

            //...Database Connection...
            DataBaseConnection dbConn = new DataBaseConnection();
            SqlConnection con = dbConn.SqlConn();
            SqlCommand cmdI;

            //...SQL Commands...
            cmdI = new SqlCommand("SELECT o.*,s.Supplier FROM t_Order o inner join t_Supplier s on o.SupplierID=s.SupplierID WHERE o.Removed=0", con);
            cmdI.Connection.Open();
            SqlDataReader drI = cmdI.ExecuteReader();

            //...Retrieve Data...
            if (drI.HasRows)
            {
                while (drI.Read())
                {
                    ins = new Order();
                    ins.OrderID = Convert.ToInt32(drI["OrderID"]);
                    ins.OrderDate = Convert.ToDateTime(drI["OrderDate"]);
                    ins.ExpectedDeliveryDate = Convert.ToDateTime(drI["ExpectedDeliveryDate"]);
                    ins.Amount = Convert.ToDecimal(drI["Amount"]);
                    ins.CreatedDate = Convert.ToDateTime(drI["CreatedDate"]);
                    ins.SupplierID = Convert.ToInt32(drI["SupplierID"]);
                    ins.UserID = Convert.ToString(drI["UserID"]);
                    ins.CommentID = Convert.ToInt32(drI["CommentID"]);
                    ins.CompanyID = Convert.ToInt32(drI["CompanyID"]);
                    ins.ModifiedDate = Convert.ToDateTime(drI["ModifiedDate"]);
                    ins.ModifiedBy = Convert.ToString(drI["ModifiedBy"]);
                    ins.Removed = Convert.ToBoolean(drI["Removed"]);
                    ins.supplier = drI["Supplier"].ToString();
                    ins.Suffix = drI["Suffix"].ToString();
                    ins.PinkSlipNumber = Convert.ToString(drI["PinkSlipNumber"]);
                    list.Add(ins);
                }
            }

            //...Close Connections...
            drI.Close();
            con.Close();

            //...Return...
            return list;
        }
コード例 #2
0
        public Order Update(Order ins)
        {
            //...Get User and Date Data...
             string ModifiedDate = string.Format("{0:yyyy-MM-dd hh:mm:ss}", DateTime.Now);
             string EmployeeId = Convert.ToString(HttpContext.Current.Session["Username"]);

            //...Database Connection...
            DataBaseConnection dbConn = new DataBaseConnection();
            SqlConnection con = dbConn.SqlConn();
            con.Open();
            SqlCommand cmdI = con.CreateCommand();
            cmdI.Connection = con;

            //...Update Record...
            cmdI.Parameters.Clear();
            cmdI.CommandText = StoredProcedures.OrderUpdate;
            cmdI.CommandType = System.Data.CommandType.StoredProcedure;
                 cmdI.Parameters.AddWithValue("@OrderID", ins.OrderID);
                 cmdI.Parameters.AddWithValue("@OrderDate", ins.OrderDate);
                 cmdI.Parameters.AddWithValue("@ExpectedDeliveryDate", ins.ExpectedDeliveryDate);
                 cmdI.Parameters.AddWithValue("@Amount", ins.Amount);
                 cmdI.Parameters.AddWithValue("@CreatedDate", DateTime.Now);
                 cmdI.Parameters.AddWithValue("@SupplierID", ins.SupplierID);
                 cmdI.Parameters.AddWithValue("@UserID", EmployeeId);
                 cmdI.Parameters.AddWithValue("@CommentID", ins.CommentID);
                 cmdI.Parameters.AddWithValue("@CompanyID", ins.CompanyID);
                 cmdI.Parameters.AddWithValue("@ModifiedDate",ModifiedDate);
                 cmdI.Parameters.AddWithValue("@ModifiedBy", EmployeeId);
                 cmdI.Parameters.AddWithValue("@Removed", ins.Removed);
                 cmdI.Parameters.AddWithValue("@PinkSlipNumber", ins.PinkSlipNumber);
                 cmdI.Parameters.AddWithValue("@Suffix", checkNullString(ins.Suffix));

            cmdI.ExecuteNonQuery();
            cmdI.Connection.Close();

            return ins;
        }
コード例 #3
0
        public List<Order> GetAllOrder(string Suffix, string PinkSlipNumber, string Supplier, string From, string To, string Comment)
        {
            //...Create New Instance of Object...
            List<Order> list = new List<Order>();
            Order ins;

            if (From.Equals(""))
                From = "1900-01-01";

            if (To.Equals(""))
                To = "2100-01-01";

            //...Database Connection...
            DataBaseConnection dbConn = new DataBaseConnection();
            SqlConnection con = dbConn.SqlConn();
            SqlCommand cmdI;

            //...SQL Commands...
            if (Supplier.Equals(""))
            {
                if(Comment.Equals(""))
                {
                    cmdI = new SqlCommand("SELECT o.*,s.Supplier,c.Comment FROM t_Order o inner join t_Supplier s on o.SupplierID=s.SupplierID "
                    + " inner join t_Comment c on o.CommentID = c.CommentID "
                    + " WHERE o.Removed=0 AND o.PinkSlipNumber LIKE '%" + PinkSlipNumber + "'"
                    + " AND o.Suffix LIKE '%" + Suffix + "'"
                    + " AND o.OrderDate >= '" + From + "' AND o.OrderDate <= '" + To + "'", con);
                }
                else
                {
                    cmdI = new SqlCommand("SELECT o.*,s.Supplier,c.Comment FROM t_Order o inner join t_Supplier s on o.SupplierID=s.SupplierID "
                    + " inner join t_Comment c on o.CommentID = c.CommentID "
                    + " WHERE o.Removed=0 AND o.PinkSlipNumber LIKE '%" + PinkSlipNumber + "'"
                    + " AND o.Suffix LIKE '%" + Suffix + "' AND o.CommentID = " + Comment
                    + " AND o.OrderDate >= '" + From + "' AND o.OrderDate <= '" + To + "'", con);
                }
            }
            else
            {
                if(Comment.Equals(""))
                {
                    cmdI = new SqlCommand("SELECT o.*,s.Supplier,c.Comment FROM t_Order o inner join t_Supplier s on o.SupplierID=s.SupplierID "
                    + " inner join t_Comment c on o.CommentID = c.CommentID "
                    + " WHERE o.Removed=0 AND o.PinkSlipNumber LIKE '%" + PinkSlipNumber + "'"
                    + " AND o.Suffix LIKE '%" + Suffix + "'"
                    + " AND o.OrderDate >= '" + From + "' AND o.OrderDate <= '" + To + "'"
                    + " AND o.SupplierID = " + Supplier, con);
                }
                else
                {
                    cmdI = new SqlCommand("SELECT o.*,s.Supplier,c.Comment FROM t_Order o inner join t_Supplier s on o.SupplierID=s.SupplierID "
                    + " inner join t_Comment c on o.CommentID = c.CommentID "
                    + " WHERE o.Removed=0 AND o.PinkSlipNumber LIKE '%" + PinkSlipNumber + "'"
                    + " AND o.Suffix LIKE '%" + Suffix + "' AND o.CommentID = " + Comment
                    + " AND o.OrderDate >= '" + From + "' AND o.OrderDate <= '" + To + "'"
                    + " AND o.SupplierID = " + Supplier, con);
                }
            }

            cmdI.Connection.Open();
            SqlDataReader drI = cmdI.ExecuteReader();

            //...Retrieve Data...
            if (drI.HasRows)
            {
                while (drI.Read())
                {
                    ins = new Order();
                    ins.OrderID = Convert.ToInt32(drI["OrderID"]);
                    ins.OrderDate = Convert.ToDateTime(drI["OrderDate"]);
                    ins.ExpectedDeliveryDate = Convert.ToDateTime(drI["ExpectedDeliveryDate"]);
                    ins.Amount = Convert.ToDecimal(drI["Amount"]);
                    ins.CreatedDate = Convert.ToDateTime(drI["CreatedDate"]);
                    ins.SupplierID = Convert.ToInt32(drI["SupplierID"]);
                    ins.UserID = Convert.ToString(drI["UserID"]);
                    ins.CommentID = Convert.ToInt32(drI["CommentID"]);
                    ins.CompanyID = Convert.ToInt32(drI["CompanyID"]);
                    ins.ModifiedDate = Convert.ToDateTime(drI["ModifiedDate"]);
                    ins.ModifiedBy = Convert.ToString(drI["ModifiedBy"]);
                    ins.Removed = Convert.ToBoolean(drI["Removed"]);
                    ins.supplier = drI["Supplier"].ToString();
                    ins.Suffix = drI["Suffix"].ToString();
                    ins.PinkSlipNumber = Convert.ToString(drI["PinkSlipNumber"]);
                    ins.ordercomment = drI["Comment"].ToString();
                    list.Add(ins);
                }
            }

            //...Close Connections...
            drI.Close();
            con.Close();

            //...Return...
            return list;
        }
コード例 #4
0
        public Order Insert(Order ins)
        {
            //...Get User and Date Data...
            string ModifiedDate = string.Format("{0:yyyy-MM-dd hh:mm:ss}", DateTime.Now);
            string EmployeeId = Convert.ToString(HttpContext.Current.Session["Username"]);
            ins.UserID = EmployeeId;
            string strTrx = "OrderIns_" + EmployeeId;

            //...Database Connection...
            DataBaseConnection dbConn = new DataBaseConnection();
            SqlConnection con = dbConn.SqlConn();
            con.Open();

            //...Command Interface...
            SqlCommand cmdI = con.CreateCommand();
            SqlTransaction trx;
            trx = con.BeginTransaction(strTrx);
            cmdI.Connection = con;
            cmdI.Transaction = trx;

            try
            {
                //...Insert Record...
                cmdI.CommandText = StoredProcedures.OrderInsert;
                cmdI.CommandType = System.Data.CommandType.StoredProcedure;
                //cmdI.Parameters.AddWithValue("@OrderID", ins.OrderID);
                cmdI.Parameters.AddWithValue("@OrderDate", ins.OrderDate);
                cmdI.Parameters.AddWithValue("@ExpectedDeliveryDate", ins.ExpectedDeliveryDate);
                cmdI.Parameters.AddWithValue("@Amount", ins.Amount);
                cmdI.Parameters.AddWithValue("@CreatedDate", DateTime.Now);
                cmdI.Parameters.AddWithValue("@SupplierID", ins.SupplierID);
                cmdI.Parameters.AddWithValue("@UserID", ins.UserID);
                cmdI.Parameters.AddWithValue("@CommentID", ins.CommentID);
                cmdI.Parameters.AddWithValue("@CompanyID", ins.CompanyID);
                cmdI.Parameters.AddWithValue("@ModifiedDate",ModifiedDate);
                cmdI.Parameters.AddWithValue("@ModifiedBy", EmployeeId);
                cmdI.Parameters.AddWithValue("@Removed", ins.Removed);
                cmdI.Parameters.AddWithValue("@PinkSlipNumber", ins.PinkSlipNumber);
                cmdI.Parameters.AddWithValue("@Suffix", checkNullString(ins.Suffix));

                //...Return new ID
                ins.OrderID = (int)cmdI.ExecuteScalar();

                trx.Commit();
                cmdI.Connection.Close();
            }
            catch (SqlException ex)
            {
                if (trx != null) trx.Rollback();
            }
            finally
            {
                //Check for close and respond accordingly
                if (con.State != ConnectionState.Closed)
                {
                    con.Close();
                }
                //Clean up
                con.Dispose();
                cmdI.Dispose();
                trx.Dispose();
            }

            RepopulateGRVList(ins.PinkSlipNumber);

            return ins;
        }
コード例 #5
0
        public List<Order> GetOrdersPerEmployeeType(int UserTypeID)
        {
            //...Create New Instance of Object...
            List<Order> list = new List<Order>();
            Order ins;

            //...Database Connection...
            DataBaseConnection dbConn = new DataBaseConnection();
            SqlConnection con = dbConn.SqlConn();
            SqlCommand cmdI;

            //...SQL Commands...
            cmdI = new SqlCommand("SELECT * FROM t_Order WHERE UserTypeID = " + UserTypeID, con);
            cmdI.Connection.Open();
            SqlDataReader drI = cmdI.ExecuteReader();

            //...Retrieve Data...
            if (drI.HasRows)
            {
                while (drI.Read())
                {
                    ins = new Order();
                    ins.OrderID = Convert.ToInt32(drI["OrderID"]);
                    ins.OrderDate = Convert.ToDateTime(drI["OrderDate"]);
                    ins.ExpectedDeliveryDate = Convert.ToDateTime(drI["ExpectedDeliveryDate"]);
                    ins.Amount = Convert.ToDecimal(drI["Amount"]);
                    ins.CreatedDate = Convert.ToDateTime(drI["CreatedDate"]);
                    ins.SupplierID = Convert.ToInt32(drI["SupplierID"]);
                    ins.UserID = Convert.ToString(drI["UserID"]);
                    ins.CommentID = Convert.ToInt32(drI["CommentID"]);
                    ins.CompanyID = Convert.ToInt32(drI["CompanyID"]);
                    ins.ModifiedDate = Convert.ToDateTime(drI["ModifiedDate"]);
                    ins.ModifiedBy = Convert.ToString(drI["ModifiedBy"]);
                    ins.Removed = Convert.ToBoolean(drI["Removed"]);
                    list.Add(ins);
                }
            }

            //...Close Connections...
            drI.Close();
            con.Close();

            //...Return...
            return list;
        }
コード例 #6
0
        public Order GetOrders(int OrderID)
        {
            //...Create New Instance of Object...
            Order ins = new Order();

            //...Database Connection...
            DataBaseConnection dbConn = new DataBaseConnection();
            SqlConnection con = dbConn.SqlConn();
            SqlCommand cmdI;

            //...SQL Commands...
            cmdI = new SqlCommand("SELECT * FROM t_Order WHERE OrderID =" + OrderID, con);
            cmdI.Connection.Open();
            SqlDataReader drI = cmdI.ExecuteReader();

            //...Retrieve Data...
            if (drI.HasRows)
            {
                while (drI.Read())
                {
                    ins.OrderID = Convert.ToInt32(drI["OrderID"]);
                    ins.OrderDate = Convert.ToDateTime(drI["OrderDate"]);
                    ins.ExpectedDeliveryDate = Convert.ToDateTime(drI["ExpectedDeliveryDate"]);
                    ins.Amount = Convert.ToDecimal(drI["Amount"]);
                    ins.CreatedDate = Convert.ToDateTime(drI["CreatedDate"]);
                    ins.SupplierID = Convert.ToInt32(drI["SupplierID"]);
                    ins.UserID = Convert.ToString(drI["UserID"]);
                    ins.CommentID = Convert.ToInt32(drI["CommentID"]);
                    ins.CompanyID = Convert.ToInt32(drI["CompanyID"]);
                    ins.ModifiedDate = Convert.ToDateTime(drI["ModifiedDate"]);
                    ins.ModifiedBy = Convert.ToString(drI["ModifiedBy"]);
                    ins.Removed = Convert.ToBoolean(drI["Removed"]);
                    ins.Suffix = drI["Suffix"].ToString();
                    ins.PinkSlipNumber = Convert.ToString(drI["PinkSlipNumber"]);
                }
            }

            //...Close Connections...
            drI.Close();
            con.Close();

            //...Return...
            return ins;
        }
コード例 #7
0
        public JsonResult _UpdateOrders(Order ins)
        {
            //...Update Object
            Order ins2 = ORep.Update(ins);

            //...Repopulate Grid...
            return Json(new GridModel(ORep.GetAllOrder()));
        }
コード例 #8
0
        public ActionResult _ProcessProductOrder(Order ins)
        {
            List<OrderProduct> products = ORep.GetOrderProductsPerOrder(ins.OrderID);
            decimal total = 0;
            foreach(OrderProduct product in products)
            {
                total += (product.Price * product.Quantity);
            }

            ins.Amount = total;
            ins = ORep.Update(ins);
            ORep.ProcessProductOrder(ins.OrderID);

            return RedirectToAction("ProductOrders");
        }
コード例 #9
0
 public ActionResult _InsertProductOrder(Order ins)
 {
     ins = ORep.Insert(ins);
     return RedirectToAction("ProductOrder", new { OrderId = ins.OrderID });
 }
コード例 #10
0
        public JsonResult _InsertOrders(Order ins)
        {
            //...Insert Object
            if (ORep.GetOrder(ins.PinkSlipNumber).OrderID == 0)
            {
                Order ins2 = ORep.Insert(ins);
            }

            //...Repopulate Grid...
            return Json(new GridModel(ORep.GetAllOrder()));
        }