public static DataTable GetOneTransactionTable(int transId)
{
String sql = String.Format("select * from [Transaction] where TransactionId={0}", transId);
DataTable dt = DataFactory.ExecuteSqlReturnTable(sql);
return(dt);
}
} // GetAllTransactions
// Get all orders.
// Returns the DataTable contains all transactions.
// This should be used with caution, to avoid loading to many data entries.
public static DataTable GetAllOrders()
{
String sql_getAllTransactions = "select * from [Transaction] order by SaleDate desc";
DataTable dt = DataFactory.ExecuteSqlReturnTable(sql_getAllTransactions);
return(dt);
}
public static DataTable GetAllSourcingNotes()
{
String sql = "select * from [SourcingNote]";
DataTable dt = DataFactory.ExecuteSqlReturnTable(sql);
return(dt);
}
public static DataTable GetAllItems()
{
String sql_getAllMessages = "select * from [Item]";
DataTable dt = DataFactory.ExecuteSqlReturnTable(sql_getAllMessages);
return(dt);
}
public static DataTable GetAllItemStockInNotes()
{
String sql = "select * from ItemStockInNote";
DataTable dt = DataFactory.ExecuteSqlReturnTable(sql);
return(dt);
}
public static DataTable GetAllItemSuppliersByItemId(int itemId)
{
String sql = String.Format("select * from [ItemSupplier] where ItemId={0}", itemId);
DataTable dt = DataFactory.ExecuteSqlReturnTable(sql);
return(dt);
}
public static DataTable GetItemTableById(int itemId)
{
String sql_getAllMessages = "select * from [Item] where ItemId=" + itemId;
DataTable dt = DataFactory.ExecuteSqlReturnTable(sql_getAllMessages);
return(dt);
}
public static DataTable GetItemTableBySKU(string sku)
{
String sql_getAllMessages = "select * from [Item] where ItemSKU='" + sku + "'";
DataTable dt = DataFactory.ExecuteSqlReturnTable(sql_getAllMessages);
return(dt);
}
public static DataTable GetAllSuppliers()
{
String sql = "select * from [Supplier]";
DataTable dt = DataFactory.ExecuteSqlReturnTable(sql);
return(dt);
}
public static DataTable GetAllActiveListings()
{
String sql_getAllTransactions = "select * from [ActiveListing] order by StartTime";
DataTable dt = DataFactory.ExecuteSqlReturnTable(sql_getAllTransactions);
return(dt);
}
// Get transactions for a specified order id.
// Note that for single line item, only one transaction for an order.
// For multiple line items, they are multiple transactions for an order.
public static List <EbayTransactionType> GetOrderTransactions(String orderId)
{
String sql_getOneTransaction = "select * from [Transaction] where OrderId='" + orderId + "'";
DataTable dt = DataFactory.ExecuteSqlReturnTable(sql_getOneTransaction);
if (dt.Rows.Count == 0)
{
return(null);
}
List <EbayTransactionType> trans = new List <EbayTransactionType>();
foreach (DataRow row in dt.Rows)
{
EbayTransactionType tran = new EbayTransactionType();
tran.OrderId = orderId;
tran.EbayTransactionId = StringUtil.GetSafeString(row["EbayTransactionId"]);
tran.OrderLineItemId = StringUtil.GetSafeString(row["OrderLineItemId"]);
// ZHI_TODO:
trans.Add(tran);
}
return(trans);
}
public static DataTable GetAllDeliveryNotes()
{
String sql = "select * from DeliveryNote";
DataTable dt = DataFactory.ExecuteSqlReturnTable(sql);
return(dt);
}
public static DataTable GetPendingTransactionByUserId(String userId)
{
String sql_getAllTransactions = string.Format("select * from [Transaction] where BuyerId='{0}' and IsBuyerLeftFeedback=false order by SaleDate desc", userId);
DataTable dt = DataFactory.ExecuteSqlReturnTable(sql_getAllTransactions);
return(dt);
}
// Get all paid but not shipped orders.
public static DataTable GetAllPaidButNotShippedOrders()
{
String sql_getAllTransactions = "select * from [Transaction] where IsPaid=true and IsShipped=false order by SaleDate desc";
DataTable dt = DataFactory.ExecuteSqlReturnTable(sql_getAllTransactions);
return(dt);
}
public static DataTable GetAllCategories()
{
String sql_getAllCategories = "select * from [Category]";
DataTable dt = DataFactory.ExecuteSqlReturnTable(sql_getAllCategories);
return(dt);
}
public static int GetPendingOrdersCount(int daysAfterShipment)
{
DateTime shippedDate = DateTime.Now;
shippedDate = shippedDate.AddDays(-daysAfterShipment);
int ordersCnt = 0;
String sql =
String.Format("select count(*) from [Transaction] where IsBuyerLeftFeedback=0 and IsPaid=true and IsReceived=false and ShippedDate<#{0}#",
shippedDate.ToShortDateString());
DataTable dt = DataFactory.ExecuteSqlReturnTable(sql);
try
{
object obj = dt.Rows[0][0];
if (obj != null)
{
Int32.TryParse(obj.ToString(), out ordersCnt);
}
}
catch (System.Exception)
{
}
return(ordersCnt);
}
// Get all message templates with a specified category.
public static DataTable GetAllMessageTemplatesWithCategoryId(int categoryId)
{
String sql_getAllMessages = String.Format("Select * from [MessageTemplate] where TemplateCategoryId={0}",
categoryId);
DataTable dt = DataFactory.ExecuteSqlReturnTable(sql_getAllMessages);
return(dt);
}
// Get all orders that pending buyer feedback.
// Note some buyers didn't left feedback but they composed mail to tell the item has been received.
public static DataTable GetAllOrdersPendingBuyerFeedback()
{
String sql_getAllTransactions
= "select * from [Transaction] where IsBuyerLeftFeedback=false and IsReceived=false order by SaleDate desc";
DataTable dt = DataFactory.ExecuteSqlReturnTable(sql_getAllTransactions);
return(dt);
}
public static DataTable GetPagedItems(int pageNum, int pageSize)
{
String pagedFormatSql = "select * from [Item] where ItemId in (select top {0} sub.ItemId from ("
+ " select top {1} ItemId from [Item] order by ItemId desc) [sub] order by sub.ItemId) order by ItemId desc";
String pagedSql = String.Format(pagedFormatSql, pageSize, pageNum * pageSize);
DataTable dt = DataFactory.ExecuteSqlReturnTable(pagedSql);
return(dt);
}
public static DataTable GetPagedDeliveryNotes(int pageNum, int pageSize)
{
String pagedFormatSql = "select * from [DeliveryNote] where DeliveryNoteId in (select top {0} sub.DeliveryNoteId from ("
+ " select top {1} DeliveryNoteId from [DeliveryNote] order by DeliveryNoteId desc) [sub] order by sub.DeliveryNoteId) order by DeliveryNoteId desc";
String pagedSql = String.Format(pagedFormatSql, pageSize, pageNum * pageSize);
DataTable dt = DataFactory.ExecuteSqlReturnTable(pagedSql);
return(dt);
}
public static DataTable GetPagedSuppliers(int pageNum, int pageSize)
{
String pagedFormatSql = "select * from [Supplier] where SupplierID in (select top {0} sub.SupplierID from ("
+ " select top {1} SupplierID from [Supplier] order by SupplierID desc) [sub] order by sub.SupplierID) order by SupplierID desc";
String pagedSql = String.Format(pagedFormatSql, pageSize, pageNum * pageSize);
DataTable dt = DataFactory.ExecuteSqlReturnTable(pagedSql);
return(dt);
}
public static bool CheckItemSupplierExisted(int itemId, int supplierId)
{
String sql = String.Format("select * from [ItemSupplier] where ItemId={0} and SupplierId={1}",
itemId, supplierId);
DataTable dt = DataFactory.ExecuteSqlReturnTable(sql);
bool existed = dt != null && dt.Rows.Count > 0;
return(existed);
}
public static DataTable GetPagedActiveListings(int pageNum, int pageSize)
{
String pagedOrderFormatSql = "select * from [ActiveListing] where ListId "
+ "in (select top {0} sub.ListId from ("
+ " select top {1} ListId,StartTime from [ActiveListing] order by StartTime desc"
+ ") [sub] order by sub.StartTime) order by StartTime desc";
String pagedOrderSql = String.Format(pagedOrderFormatSql, pageSize, pageNum * pageSize);
DataTable dt = DataFactory.ExecuteSqlReturnTable(pagedOrderSql);
return(dt);
}
// Get paged orders.
public static DataTable GetPagedOrders(int pageNum, int pageSize, bool isPendingOrder)
{
String pagedOrderFormatSql = "select * from [Transaction] where TransactionId "
+ "in (select top {0} sub.TransactionId from ("
+ " select top {1} TransactionId,SaleDate, OrderLineItemId from [Transaction] where IsDelivered={2} order by SaleDate desc, OrderLineItemId"
+ ") [sub] order by sub.SaleDate, sub.OrderLineItemId) order by SaleDate desc";
String pagedOrderSql = String.Format(pagedOrderFormatSql, pageSize, pageNum * pageSize, isPendingOrder ? "false" : "true");
DataTable dt = DataFactory.ExecuteSqlReturnTable(pagedOrderSql);
return(dt);
}
public static DataTable GetPagedMessageTable(int pageNum, int pageSize)
{
String pagedMsgFormatSql = "select * from [Message] where MessageId "
+ "in (select top {0} sub.MessageId from ("
+ " select top {1} MessageId,ReceiveDate from [Message] order by ReceiveDate desc"
+ ") [sub] order by sub.ReceiveDate) order by ReceiveDate desc";
String pagedMsgSql = String.Format(pagedMsgFormatSql, pageSize, pageNum * pageSize);
DataTable dt = DataFactory.ExecuteSqlReturnTable(pagedMsgSql);
return(dt);
}
public static DeliveryNoteType GetDeliveryNoteContainsTransaction(string tranId)
{
String sql = String.Format("select * from [DeliveryNote] where DeliveryOrderIds like '*,{0},*' or DeliveryOrderIds like '{0}'", tranId);
DataTable dt = DataFactory.ExecuteSqlReturnTable(sql);
if (dt.Rows.Count == 0)
{
return(null);
}
return(GetDeliveryNoteFromDataRow(dt.Rows[0]));
}
public static DeliveryNoteType GetOneDeliveryNote(String id)
{
String sql = String.Format("select * from [DeliveryNote] where DeliveryNoteId={0}", id);
DataTable dt = DataFactory.ExecuteSqlReturnTable(sql);
if (dt.Rows.Count == 0)
{
return(null);
}
return(GetDeliveryNoteFromDataRow(dt.Rows[0]));
}
public static SourcingNoteType GetSourcingNoteById(int id)
{
String sql = String.Format("select * from [SourcingNote] where SourcingId={0}", id);
DataTable dt = DataFactory.ExecuteSqlReturnTable(sql);
if (dt == null || dt.Rows.Count == 0)
{
return(null);
}
return(GetSourcingNoteFromDataRow(dt.Rows[0]));
}
public static EbayTransactionType GetOneTransactonById(string tranId)
{
String sql = String.Format("select * from [Transaction] where TransactionId={0}", tranId);
DataTable dt = DataFactory.ExecuteSqlReturnTable(sql);
if (dt.Rows.Count == 0)
{
return(null);
}
return(GetTransactionTypeFromDataRow(dt.Rows[0]));
}
public static ItemStockInNoteType GetOneItemStockInNote(String noteId)
{
String sql = String.Format("select * from [ItemStockInNote] where NoteId={0}", noteId);
DataTable dt = DataFactory.ExecuteSqlReturnTable(sql);
if (dt == null || dt.Rows.Count == 0)
{
return(null);
}
return(GetItemStockInNoteFromDataRow(dt.Rows[0]));
}