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); }