public UserInfo GetUserByOrderNo(string orderNo)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append(
"select * from UserInfo inner join Orders on Orders.UserNo = UserInfo.UserNo where Orders.OrderNo = {0} ");
string sql = string.Format(sqlBuilder.ToString(), orderNo);
try
{
SqlDataReader objReaders = SQLHelper.GetObjectCollection(sql);
UserInfo objUserInfo = new UserInfo();
if (objReaders.Read())
{
objUserInfo.UserNo = Convert.ToInt32(objReaders["UserNo"]);
objUserInfo.UserName = objReaders["UserName"].ToString();
objUserInfo.PhoneNumber = objReaders["PhoneNumber"].ToString();
objUserInfo.Address = objReaders["UserAddress"].ToString();
objUserInfo.CardNo = objReaders["CardNo"].ToString();
}
objReaders.Close();
return(objUserInfo);
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
}
public Dictionary <string, string> GetAllOrderNoAndPurchaser()
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append("select OrderNo, Purchaser from Orders");
try
{
SqlDataReader objReaders = SQLHelper.GetObjectCollection(sqlBuilder.ToString());
Dictionary <string, string> lstOrderNo = new Dictionary <string, string>();
while (objReaders.Read())
{
lstOrderNo.Add(objReaders["OrderNo"].ToString(), objReaders["Purchaser"].ToString());
}
objReaders.Close();
return(lstOrderNo);
}
catch (SqlException e)
{
Console.WriteLine(e);
throw;
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
}
public Order GetOrderByOrderNo(string orderNo)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append("select UserInfo.UserName, UserInfo.PhoneNumber, UserInfo.UserAddress, UserInfo.CardNo, Orders.ShipmentFee, Orders.ProductPrice, Orders.Purchaser");
sqlBuilder.Append(" from Orders inner join UserInfo on Orders.UserNo = UserInfo.UserNo where OrderNo = {0}");
string sql = string.Format(sqlBuilder.ToString(), orderNo);
Order objOrder = new Order();
try
{
SqlDataReader objReaders = SQLHelper.GetObjectCollection(sql);
while (objReaders.Read())
{
objOrder.User.UserName = objReaders["UserName"].ToString();
objOrder.User.PhoneNumber = objReaders["PhoneNumber"].ToString();
objOrder.User.Address = objReaders["UserAddress"].ToString();
objOrder.User.CardNo = objReaders["CardNo"].ToString();
objOrder.OrderNo = Convert.ToInt32(orderNo);
objOrder.ShipmentFee = Convert.ToDouble(objReaders["ShipmentFee"]);
objOrder.ProductPrice = Convert.ToDouble(objReaders["ProductPrice"]);
objOrder.Purchaser = objReaders["Purchaser"].ToString();
}
objReaders.Close();
return(objOrder);
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
}
public List <Item> GetItemPriceHistory()
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append(
"select ItemDescription, Price, CreateTime from [dbo].[ItemList] where Price<>0 order by CreateTime desc");
try
{
List <Item> objItems = new List <Item>();
SqlDataReader objReader = SQLHelper.GetObjectCollection(sqlBuilder.ToString());
while (objReader.Read())
{
objItems.Add(new Item()
{
ItemDescription = objReader["ItemDescription"].ToString(),
UnitPrice = Convert.ToDouble(objReader["Price"]),
CreateTime = Convert.ToDateTime(objReader["CreateTime"])
});
}
objReader.Close();
return(objItems);
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
}
public List <Item> GetItemListByOrderNo(string orderNo)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append("select ItemNo, ItemDescription, Quantity, Price, Quantity*Price as TotalPrice from ItemList where OrderNo = {0}");
string sql = string.Format(sqlBuilder.ToString(), Convert.ToInt32(orderNo));
List <Item> objItems = new List <Item>();
try
{
SqlDataReader objReaders = SQLHelper.GetObjectCollection(sql);
while (objReaders.Read())
{
objItems.Add(new Item()
{
ItemDescription = objReaders["ItemDescription"].ToString(),
Quantity = Convert.ToInt32(objReaders["Quantity"]),
UnitPrice = Convert.ToDouble(objReaders["Price"]),
TotalPrice = Convert.ToDouble(objReaders["TotalPrice"]),
ItemNo = Convert.ToInt32(objReaders["ItemNo"])
});
}
objReaders.Close();
return(objItems);
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
}
public Item GetItemByItemNo(string itemNo)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append("select * from ItemList where ItemNo = {0}");
string sql = string.Format(sqlBuilder.ToString(), itemNo);
try
{
SqlDataReader objReaders = SQLHelper.GetObjectCollection(sql);
Item objItem = new Item();
if (objReaders.Read())
{
objItem.ItemNo = Convert.ToInt32(objReaders["ItemNo"]);
objItem.ItemDescription = objReaders["ItemDescription"].ToString();
objItem.Quantity = Convert.ToInt32(objReaders["Quantity"]);
objItem.UnitPrice = Convert.ToDouble(objReaders["Price"]);
}
objReaders.Close();
return(objItem);
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
}
/// <summary>
/// get the transaction list by name
/// </summary>
/// <param name="name"></param>
/// <returns></returns>
public List <Transaction> GetTransactionListByName(string name)
{
List <Transaction> objTransactions = new List <Transaction>();
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append("select * from TransactionList where Purchaser like '%{0}%'");
string sql = string.Format(sqlBuilder.ToString(), name);
try
{
SqlDataReader objReaders = SQLHelper.GetObjectCollection(sql);
while (objReaders.Read())
{
objTransactions.Add(new Transaction()
{
OrderNo = Convert.ToInt32(objReaders["OrderNo"]),
Purchaser = objReaders["Purchaser"].ToString(),
PurchasePrice = Convert.ToDouble(objReaders["PurchasePrice"]),
SellingPrice = Convert.ToDouble(objReaders["SellingPrice"]),
Profit = Convert.ToDouble(objReaders["Profit"]),
CreateTime = Convert.ToDateTime(objReaders["CreateTime"])
});
}
objReaders.Close();
return(objTransactions);
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
}
/// <summary>
/// get an transaction record by OrderNo
/// </summary>
/// <param name="orderNo"></param>
/// <returns></returns>
public Transaction GetTransactionRecordByOrderNo(string orderNo)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append("select * from TransactionList where OrderNo = {0}");
string sql = string.Format(sqlBuilder.ToString(), orderNo);
try
{
SqlDataReader objReader = SQLHelper.GetObjectCollection(sql);
Transaction objTransaction = null;
while (objReader.Read())
{
objTransaction = new Transaction()
{
OrderNo = Convert.ToInt32(objReader["OrderNo"]),
Purchaser = objReader["Purchaser"].ToString(),
SellingPrice = Convert.ToDouble(objReader["SellingPrice"]),
PurchasePrice = Convert.ToDouble(objReader["PurchasePrice"]),
Profit = Convert.ToDouble(objReader["Profit"]),
OrderStatus = Convert.ToByte(objReader["OrderStatus"]),
CreateTime = Convert.ToDateTime(objReader["CreateTime"])
};
}
objReader.Close();
return(objTransaction);
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
}
/// <summary>
/// get undone transaction list
/// </summary>
/// <returns></returns>
public List <Transaction> GetUndoneTransactionList()
{
List <Transaction> objTransactions = new List <Transaction>();
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append("select * from TransactionList where OrderStatus <> 1");
SqlDataReader objReaders = SQLHelper.GetObjectCollection(sqlBuilder.ToString());
while (objReaders.Read())
{
objTransactions.Add(new Transaction()
{
OrderNo = Convert.ToInt32(objReaders["OrderNo"]),
Purchaser = objReaders["Purchaser"].ToString(),
PurchasePrice = Convert.ToDouble(objReaders["PurchasePrice"]),
SellingPrice = Convert.ToDouble(objReaders["SellingPrice"]),
Profit = Convert.ToDouble(objReaders["Profit"]),
CreateTime = Convert.ToDateTime(objReaders["CreateTime"])
});
}
return(objTransactions);
}
/// <summary>
/// get transaction list by Purchaser and sorting type
/// </summary>
/// <param name="name"></param>
/// <param name="sortingType"></param>
/// <returns></returns>
public List <Transaction> GetTransactionList(string name, int sortingType, DateTime dtFromDate, DateTime dtEndDate)
{
List <Transaction> objTransactions = new List <Transaction>();
StringBuilder sqlBuilder = new StringBuilder();
if (name == "Input Name" || name == "客户姓名" || name == "")
{
sqlBuilder.Append("select * from TransactionList where CreateTime >= '{0}' and CreateTime <= '{1}' ");
}
else
{
sqlBuilder.Append("select * from TransactionList where CreateTime >= '{0}' and CreateTime <= '{1}' and Purchaser like '%{2}%' ");
}
switch (sortingType)
{
case 0:
sqlBuilder.Append("order by OrderNo asc");
break;
case 1:
sqlBuilder.Append("order by OrderNo desc");
break;
case 2:
sqlBuilder.Append("order by Profit asc");
break;
case 3:
sqlBuilder.Append("order by Profit desc");
break;
default:
break;
}
string sql = null;
if (name == "Input Name" || name == "客户姓名" || name == "")
{
sql = string.Format(sqlBuilder.ToString(), dtFromDate.Date, dtEndDate.Date);
}
else
{
sql = string.Format(sqlBuilder.ToString(), dtFromDate.Date, dtEndDate.Date, name);
}
SqlDataReader objReaders = SQLHelper.GetObjectCollection(sql);
while (objReaders.Read())
{
objTransactions.Add(new Transaction()
{
OrderNo = Convert.ToInt32(objReaders["OrderNo"]),
Purchaser = objReaders["Purchaser"].ToString(),
PurchasePrice = Convert.ToDouble(objReaders["PurchasePrice"]),
SellingPrice = Convert.ToDouble(objReaders["SellingPrice"]),
Profit = Convert.ToDouble(objReaders["Profit"]),
CreateTime = Convert.ToDateTime(objReaders["CreateTime"])
});
}
objReaders.Close();
return(objTransactions);
}
