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 = ""
});
}
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>());
}
}
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>());
}
}
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>());
}
}
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
});
}
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 = ""
});
}
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);
}
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);
}
}