コード例 #1
0
        public User SaveUser(User user)
        {
            string storeProcedureName = "SaveUser";
            DatabaseConnectAndExecute            db         = new DatabaseConnectAndExecute(ConnectionString);
            List <SqlParameter>                  parameters = new List <SqlParameter>();
            List <Tuple <string, Type, object> > param      = new List <Tuple <string, Type, object> >();
            Tuple <string, Type, object>         p          = new Tuple <string, Type, object>("UserId", user.UserId.GetType(), user.UserId); param.Add(p);

            p = new Tuple <string, Type, object>("UserName", user.UserName.GetType(), user.UserName); param.Add(p);
            user.EncryptedAccessKey = user.EncryptedAccessKey ?? (user.UserName.Replace(" ", "") + "@123");
            p = new Tuple <string, Type, object>("EncryptedAccessKey", user.EncryptedAccessKey.GetType(), user.EncryptedAccessKey); param.Add(p);
            p = new Tuple <string, Type, object>("IsDeleted", user.IsDeleted.GetType(), user.IsDeleted); param.Add(p);
            parameters.Add(db.GetTableParameter("@UserItems", "UserType", param));

            using (DbDataReader returnReader = db.GetDataReader(storeProcedureName, parameters))
            {
                while (returnReader.Read())
                {
                    return(new User()
                    {
                        UserId = Convert.ToInt32(returnReader["UserId"]),
                        UserName = returnReader["UserName"].ToString(),
                        EncryptedAccessKey = returnReader["EncryptedAccessKey"].ToString()
                    });
                }
            }
            return(new User()
            {
                UserId = 0,
                UserName = "",
                EncryptedAccessKey = ""
            });
        }
コード例 #2
0
 public List <Member> GetMembers()
 {
     try
     {
         string StoreProcedureName = "GetMember";
         if (Members == null)
         {
             Members = new List <Member>();
         }
         DatabaseConnectAndExecute db = new DatabaseConnectAndExecute(ConnectionString);
         using (DbDataReader returnReader = db.GetDataReader(StoreProcedureName))
         {
             while (returnReader.Read())
             {
                 Members.Add(new Member()
                 {
                     MemberId      = Convert.ToInt32(returnReader["MemberId"]),
                     FirstName     = returnReader["FirstName"].ToString(),
                     LastName      = returnReader["LastName"].ToString(),
                     Address       = returnReader["Address"].ToString(),
                     EmailId       = returnReader["EmailId"].ToString(),
                     ContactNumber = returnReader["ContactNumber"].ToString(),
                     StartDate     = returnReader["StartDate"].ToString(),
                     EndDate       = returnReader["EndDate"].ToString()
                 });
             }
         }
         return(Members);
     }
     catch (Exception ex)
     {
         return(new List <Member>());
     }
 }
コード例 #3
0
 public List <User> GetUsers()
 {
     try
     {
         string StoreProcedureName = "GetUser";
         if (Users == null)
         {
             Users = new List <User>();
         }
         DatabaseConnectAndExecute db = new DatabaseConnectAndExecute(ConnectionString);
         using (DbDataReader returnReader = db.GetDataReader(StoreProcedureName))
         {
             while (returnReader.Read())
             {
                 Users.Add(new User()
                 {
                     UserId             = Convert.ToInt32(returnReader["UserId"]),
                     UserName           = returnReader["UserName"].ToString(),
                     EncryptedAccessKey = returnReader["EncryptedAccessKey"].ToString()
                 });
             }
         }
         return(Users);
     }
     catch (Exception ex)
     {
         return(new List <User>());
     }
 }
コード例 #4
0
 public List <Product> GetProducts()
 {
     try
     {
         string StoreProcedureName = "GetProduct";
         if (Products == null)
         {
             Products = new List <Product>();
         }
         DatabaseConnectAndExecute db = new DatabaseConnectAndExecute(ConnectionString);
         using (DbDataReader returnReader = db.GetDataReader(StoreProcedureName))
         {
             while (returnReader.Read())
             {
                 Products.Add(new Product()
                 {
                     ProductId          = Convert.ToInt32(returnReader["ProductId"]),
                     ProductNumber      = returnReader["ProductNumber"].ToString(),
                     ProductName        = returnReader["ProductName"].ToString(),
                     ProductDescription = returnReader["ProductDescription"].ToString(),
                     Brand             = returnReader["Brand"].ToString(),
                     MemberId          = Convert.ToInt32(returnReader["MemberId"]),
                     InitialQuantity   = Convert.ToInt32(returnReader["InitialQuantity"]),
                     RemainingQuantity = Convert.ToInt32(returnReader["RemainingQuantity"]),
                     PricePerUnit      = Convert.ToDouble(returnReader["PricePerUnit"])
                 });
             }
         }
         return(Products);
     }
     catch (Exception ex)
     {
         return(new List <Product>());
     }
 }
コード例 #5
0
        internal Product SaveProduct(Product pProduct)
        {
            string StoreProcedureName = "SaveProduct";
            DatabaseConnectAndExecute            db         = new DatabaseConnectAndExecute(ConnectionString);
            List <SqlParameter>                  parameters = new List <SqlParameter>();
            List <Tuple <string, Type, object> > param      = new List <Tuple <string, Type, object> >();
            Tuple <string, Type, object>         p          = new Tuple <string, Type, object>("ProductId", pProduct.ProductId.GetType(), pProduct.ProductId); param.Add(p);

            p = new Tuple <string, Type, object>("ProductNumber", pProduct.ProductNumber.GetType(), pProduct.ProductNumber); param.Add(p);
            p = new Tuple <string, Type, object>("ProductName", pProduct.ProductName.GetType(), pProduct.ProductName); param.Add(p);
            p = new Tuple <string, Type, object>("ProductDescription", pProduct.ProductDescription.GetType(), pProduct.ProductDescription); param.Add(p);
            p = new Tuple <string, Type, object>("Brand", pProduct.Brand.GetType(), pProduct.Brand); param.Add(p);
            p = new Tuple <string, Type, object>("MemberId", pProduct.MemberId.GetType(), pProduct.MemberId); param.Add(p);
            p = new Tuple <string, Type, object>("Quantity", pProduct.InitialQuantity.GetType(), pProduct.InitialQuantity); param.Add(p);
            p = new Tuple <string, Type, object>("PricePerUnit", (0.0d).GetType(), pProduct.PricePerUnit); param.Add(p);
            p = new Tuple <string, Type, object>("IsDeleted", pProduct.IsDeleted.GetType(), pProduct.IsDeleted); param.Add(p);

            parameters.Add(db.GetTableParameter("@ProductItems", "ProductType", param));
            using (DbDataReader returnReader = db.GetDataReader(StoreProcedureName, parameters))
            {
                while (returnReader.Read())
                {
                    return(new Product()
                    {
                        ProductId = Convert.ToInt32(returnReader["ProductId"]),
                        ProductNumber = returnReader["ProductNumber"].ToString(),
                        ProductName = returnReader["ProductName"].ToString(),
                        ProductDescription = returnReader["ProductDescription"].ToString(),
                        Brand = returnReader["Brand"].ToString(),
                        MemberId = Convert.ToInt32(returnReader["MemberId"]),
                        InitialQuantity = Convert.ToInt32(returnReader["InitialQuantity"]),
                        RemainingQuantity = Convert.ToInt32(returnReader["RemainingQuantity"]),
                        PricePerUnit = Convert.ToDouble(returnReader["PricePerUnit"])
                    });
                }
            }
            return(new Product()
            {
                Brand = "",
                PricePerUnit = 0.0,
                ProductDescription = "",
                ProductId = 0,
                ProductName = "",
                ProductNumber = "",
                InitialQuantity = 0,
                RemainingQuantity = 0,
                MemberId = 0
            });
        }
コード例 #6
0
        public Member SaveMember(Member member)
        {
            string StoreProcedureName = "SaveMember";
            DatabaseConnectAndExecute            db         = new DatabaseConnectAndExecute(ConnectionString);
            List <SqlParameter>                  parameters = new List <SqlParameter>();
            List <Tuple <string, Type, object> > param      = new List <Tuple <string, Type, object> >();
            Tuple <string, Type, object>         p          = new Tuple <string, Type, object>("MemberId", member.MemberId.GetType(), member.MemberId); param.Add(p);

            p = new Tuple <string, Type, object>("FirstName", member.FirstName.GetType(), member.FirstName); param.Add(p);
            p = new Tuple <string, Type, object>("LastName", member.LastName.GetType(), member.LastName); param.Add(p);
            p = new Tuple <string, Type, object>("Address", member.Address.GetType(), member.Address); param.Add(p);
            p = new Tuple <string, Type, object>("EmailId", member.EmailId.GetType(), member.EmailId); param.Add(p);
            p = new Tuple <string, Type, object>("ContactNumber", member.ContactNumber.GetType(), member.ContactNumber); param.Add(p);
            p = new Tuple <string, Type, object>("StartDate", member.StartDate.GetType(), member.StartDate); param.Add(p);
            p = new Tuple <string, Type, object>("EndDate", member.EndDate.GetType(), member.EndDate); param.Add(p);
            p = new Tuple <string, Type, object>("IsDeleted", member.IsDeleted.GetType(), member.IsDeleted); param.Add(p);
            parameters.Add(db.GetTableParameter("@MemberItems", "MemberType", param));

            using (DbDataReader returnReader = db.GetDataReader(StoreProcedureName, parameters))
            {
                while (returnReader.Read())
                {
                    return(new Member
                    {
                        MemberId = Convert.ToInt32(returnReader["MemberId"]),
                        FirstName = returnReader["FirstName"].ToString(),
                        LastName = returnReader["LastName"].ToString(),
                        Address = returnReader["Address"].ToString(),
                        EmailId = returnReader["EmailId"].ToString(),
                        ContactNumber = returnReader["ContactNumber"].ToString(),
                        StartDate = returnReader["StartDate"].ToString(),
                        EndDate = returnReader["EndDate"].ToString()
                    });
                }
            }
            return(new Member
            {
                MemberId = 0,
                FirstName = "",
                LastName = "",
                Address = "",
                EmailId = "",
                ContactNumber = "",
                StartDate = "",
                EndDate = ""
            });
        }
コード例 #7
0
        public List <Transaction> GetTransactions()
        {
            string StoreProcedureName = "GetTransaction";

            if (Transactions == null)
            {
                Transactions = new List <Transaction>();
            }
            DatabaseConnectAndExecute db = new DatabaseConnectAndExecute(ConnectionString);

            using (DbDataReader returnReader = db.GetDataReader(StoreProcedureName))
            {
                List <RawTransaction> RawTransactions = new List <RawTransaction>();
                while (returnReader.Read())
                {
                    RawTransactions.Add(new RawTransaction()
                    {
                        TransactionId     = Convert.ToInt32(returnReader["TransactionId"].ToString()),
                        PaymentType       = returnReader["PaymentType"].ToString(),
                        TransactionStatus = Convert.ToInt32(returnReader["TransactionStatus"].ToString()),
                        TransactionDate   = Convert.ToDateTime(returnReader["TransactionDate"].ToString()),

                        SalesId        = Convert.ToInt32(returnReader["SalesId"].ToString()),
                        MemberId       = Convert.ToInt32(returnReader["MemberId"].ToString()),
                        FirstName      = returnReader["FirstName"].ToString(),
                        SalesDateTime  = Convert.ToDateTime(returnReader["SalesDateTime"]),
                        ReceiptNumber  = returnReader["ReceiptNumber"].ToString(),
                        DiscountAmount = Convert.ToDouble(returnReader["DiscountAmount"].ToString()),

                        SalesDetailsId     = Convert.ToInt32(returnReader["SalesDetailsId"].ToString()),
                        ProductId          = Convert.ToInt32(returnReader["ProductId"].ToString()),
                        ProductNumber      = returnReader["ProductNumber"].ToString(),
                        ProductName        = returnReader["ProductName"].ToString(),
                        ProductDescription = returnReader["ProductDescription"].ToString(),
                        Brand        = returnReader["Brand"].ToString(),
                        PricePerUnit = Convert.ToDouble(returnReader["UnitPrice"].ToString()),
                        Quantity     = Convert.ToInt32(returnReader["Quantity"].ToString())
                    });
                }
                RawTransactions.ForEach(x => {
                    bool bExists   = Transactions.Where(t => t.TransactionId == x.TransactionId).Count() > 0;
                    Transaction tr = bExists ? Transactions.Where(t => t.TransactionId == x.TransactionId).FirstOrDefault() : new Transaction();
                    if (!bExists)
                    {
                        tr.TransactionId     = x.TransactionId;
                        tr.TransactionStatus = x.TransactionStatus;
                        tr.PaymentType       = x.PaymentType;
                        tr.TransactionDate   = x.TransactionDate;
                    }
                    if (tr.Invoice == null)
                    {
                        tr.Invoice = new Sales()
                        {
                            BuyingMember = new Member(), Items = new List <SalesDetail>()
                        }
                    }
                    ;

                    tr.Invoice.SalesId = x.SalesId;
                    tr.Invoice.BuyingMember.MemberId  = x.MemberId;
                    tr.Invoice.BuyingMember.FirstName = x.FirstName;
                    tr.Invoice.SalesDateTime          = x.SalesDateTime;
                    tr.Invoice.ReceiptNumber          = x.ReceiptNumber;
                    tr.Invoice.DiscountAmount         = x.DiscountAmount;

                    tr.Invoice.Items.Add(new SalesDetail()
                    {
                        Quantity       = x.Quantity,
                        SalesDetailsId = x.SalesDetailsId,
                        SoldProduct    = new Product()
                        {
                            ProductId          = x.ProductId,
                            Brand              = x.Brand,
                            ProductDescription = x.ProductDescription,
                            PricePerUnit       = x.PricePerUnit,
                            ProductName        = x.ProductName,
                            ProductNumber      = x.ProductNumber
                        }
                    });
                    if (!bExists)
                    {
                        Transactions.Add(tr);
                    }
                });
            }
            return(Transactions);
        }
コード例 #8
0
        public Transaction SaveTransaction(Transaction pTransaction)
        {
            string StoreProcedureName = "SaveTransaction";
            DatabaseConnectAndExecute            db         = new DatabaseConnectAndExecute(ConnectionString);
            List <SqlParameter>                  parameters = new List <SqlParameter>();
            List <Tuple <string, Type, object> > param      = new List <Tuple <string, Type, object> >();
            Tuple <string, Type, object>         p          = new Tuple <string, Type, object>("TransactionId", pTransaction.TransactionId.GetType(), pTransaction.TransactionId); param.Add(p);

            p = new Tuple <string, Type, object>("SalesId", pTransaction.Invoice.SalesId.GetType(), pTransaction.Invoice.SalesId); param.Add(p);
            p = new Tuple <string, Type, object>("TotalAmount", pTransaction.TotalAmount.GetType(), pTransaction.TotalAmount); param.Add(p);
            p = new Tuple <string, Type, object>("PaymentType", pTransaction.PaymentType.GetType(), pTransaction.PaymentType); param.Add(p);
            p = new Tuple <string, Type, object>("TransactionStatus", pTransaction.TransactionStatus.GetType(), pTransaction.TransactionStatus); param.Add(p);
            p = new Tuple <string, Type, object>("TransactionDate", pTransaction.TransactionDate.GetType(), pTransaction.TransactionDate); param.Add(p);
            p = new Tuple <string, Type, object>("IsDeleted", pTransaction.IsDeleted.GetType(), pTransaction.IsDeleted); param.Add(p);

            parameters.Add(db.GetTableParameter("@TransactionItems", "TransactionType", param));

            param = new List <Tuple <string, Type, object> >();
            p     = new Tuple <string, Type, object>("SalesId", pTransaction.Invoice.SalesId.GetType(), pTransaction.Invoice.SalesId); param.Add(p);
            p     = new Tuple <string, Type, object>("MemberId", pTransaction.Invoice.BuyingMember.MemberId.GetType(), pTransaction.Invoice.BuyingMember.MemberId); param.Add(p);
            p     = new Tuple <string, Type, object>("SalesDateTime", pTransaction.Invoice.SalesDateTime.GetType(), pTransaction.Invoice.SalesDateTime); param.Add(p);
            p     = new Tuple <string, Type, object>("ReceiptNumber", pTransaction.Invoice.ReceiptNumber.GetType(), pTransaction.Invoice.ReceiptNumber); param.Add(p);
            p     = new Tuple <string, Type, object>("DiscountAmount", pTransaction.Invoice.DiscountAmount.GetType(), pTransaction.Invoice.DiscountAmount); param.Add(p);
            p     = new Tuple <string, Type, object>("TotalBillAmount", pTransaction.Invoice.TotalBillAmount.GetType(), pTransaction.Invoice.TotalBillAmount); param.Add(p);
            p     = new Tuple <string, Type, object>("IsDeleted", pTransaction.IsDeleted.GetType(), pTransaction.IsDeleted); param.Add(p);

            parameters.Add(db.GetTableParameter("@SalesItems", "SalesType", param));

            List <List <Tuple <string, Type, object> > > paramRows = new List <List <Tuple <string, Type, object> > >();

            pTransaction.Invoice.Items.ForEach(item => {
                param = new List <Tuple <string, Type, object> >(); paramRows.Add(param);
                Tuple <string, Type, object> p = new Tuple <string, Type, object>("SalesDetailsId", item.SalesDetailsId.GetType(), item.SalesDetailsId); param.Add(p);
                p = new Tuple <string, Type, object>("SalesId", pTransaction.Invoice.SalesId.GetType(), pTransaction.Invoice.SalesId); param.Add(p);
                p = new Tuple <string, Type, object>("ProductId", item.SoldProduct.ProductId.GetType(), item.SoldProduct.ProductId); param.Add(p);
                p = new Tuple <string, Type, object>("Quantity", item.Quantity.GetType(), item.Quantity); param.Add(p);
                p = new Tuple <string, Type, object>("UnitPrice", item.UnitPrice.GetType(), item.UnitPrice); param.Add(p);
                p = new Tuple <string, Type, object>("TotalPrice", item.TotalPrice.GetType(), item.TotalPrice); param.Add(p);
                p = new Tuple <string, Type, object>("IsDeleted", item.IsDeleted.GetType(), item.IsDeleted); param.Add(p);
            });
            parameters.Add(db.GetTableParameter("@SalesDetailsItems", "SalesDetailsType", paramRows));

            Transaction t = new Transaction();

            using (DbDataReader returnReader = db.GetDataReader(StoreProcedureName, parameters))
            {
                while (returnReader.Read())
                {
                    t.TransactionId     = Convert.ToInt32(returnReader["TransactionId"]);
                    t.TotalAmount       = Convert.ToDouble(returnReader["TotalBillAmount"]);
                    t.PaymentType       = returnReader["PaymentType"].ToString();
                    t.TransactionStatus = Convert.ToInt32(returnReader["TransactionStatus"].ToString());
                    t.TransactionDate   = Convert.ToDateTime(returnReader["TransactionDate"]);
                    if (t.Invoice == null)
                    {
                        t.Invoice = new Sales()
                        {
                            SalesId      = Convert.ToInt32(returnReader["SalesId"].ToString()),
                            BuyingMember = new Member()
                            {
                                MemberId  = Convert.ToInt32(returnReader["MemberId"].ToString()),
                                FirstName = returnReader["FirstName"].ToString()
                            },
                            SalesDateTime  = Convert.ToDateTime(returnReader["SalesDateTime"]),
                            DiscountAmount = Convert.ToDouble(returnReader["DiscountAmount"]),
                            ReceiptNumber  = returnReader["ReceiptNumber"].ToString(),
                            Items          = new List <SalesDetail>()
                        }
                    }
                    ;
                    t.Invoice.Items.Add(
                        new SalesDetail()
                    {
                        SalesDetailsId = Convert.ToInt32(returnReader["SalesDetailsId"]),
                        SoldProduct    = new Product()
                        {
                            ProductId          = Convert.ToInt32(returnReader["ProductId"]),
                            ProductNumber      = returnReader["ProductNumber"].ToString(),
                            ProductName        = returnReader["ProductName"].ToString(),
                            ProductDescription = returnReader["ProductDescription"].ToString(),
                            Brand = returnReader["Brand"].ToString(),
                        },
                        // = Convert.ToInt32(returnReader["UnitPrice"]),
                        Quantity = Convert.ToInt32(returnReader["Quantity"]),
                        //TotalPrice = Convert.ToDouble(returnReader["TotalPrice"]),
                    }
                        );
                }
            }
            return(t);
        }
    }