public static DataTable GetTrafficHandledL2(string Frequency, string Direction, string From = null, string To = null) { DataTable ds = new DataTable(); var sqlParam = new SqlParameter[4]; sqlParam[0] = new SqlParameter("@Frequency", Frequency) { SqlDbType = SqlDbType.NVarChar }; sqlParam[1] = new SqlParameter("@From", From) { SqlDbType = SqlDbType.DateTime }; sqlParam[2] = new SqlParameter("@To", null) { SqlDbType = SqlDbType.DateTime }; sqlParam[3] = new SqlParameter("@Direction", Direction) { SqlDbType = SqlDbType.NVarChar }; ds = SqlDBHelper.ExecuteQuery("sp_TrafficHandled_L2_Test", sqlParam); return(ds); }
/// <summary> /// 通过ID 获得团购单的详细信息 /// </summary> /// <param name="id"></param> /// <returns></returns> public static DataTable GetTicketById(string id) { int groupId = int.Parse(id); string sqlCmd = String.Format(GetTicketDetailSqlCmdTemplate, groupId); SqlDBHelper dbHelper = new SqlDBHelper(); DataTable data = dbHelper.ExecuteQuery(sqlCmd); return(data); }
/// <summary> /// 获取团购单 /// </summary> /// <param name="id"></param> /// <param name="title"></param> /// <param name="city"></param> /// <param name="productSysNo"></param> /// <param name="status"></param> /// <param name="createDate"></param> /// <param name="startDate"></param> /// <param name="endDate"></param> /// <returns></returns> public static DataTable GetTickets(string id, string title, string city, string productSysNo, int status, string createDate, string startDate, string endDate, string sortOrder = "groupid", string orderDire = "ASC") { string condition = "WHERE"; if (id.IsSafeString()) { condition += String.Concat(" groupid=", int.Parse(id)); } if (title.IsSafeString()) { condition += GroupBuyUtility.CombineMutlQueryCondtion(condition, String.Concat(" title LIKE '%", title, "%'")); } if (city.IsSafeString()) { condition += GroupBuyUtility.CombineMutlQueryCondtion(condition, String.Concat(" location='", city, "'")); } if (productSysNo.IsSafeString()) { condition += GroupBuyUtility.CombineMutlQueryCondtion(condition, String.Concat(" product_sysNo=", int.Parse(productSysNo))); } if (status != 0) { condition += GroupBuyUtility.CombineMutlQueryCondtion(condition, String.Concat(" status=", status)); } if (createDate.IsSafeString()) { condition += GroupBuyUtility.CombineMutlQueryCondtion(condition, String.Concat(" create_date LIKE '%", createDate.Trim(), "'")); } if (startDate.IsSafeString()) { condition += GroupBuyUtility.CombineMutlQueryCondtion(condition, String.Concat(" start_date = '", startDate.Trim(), "'")); } if (endDate.IsSafeString()) { condition += GroupBuyUtility.CombineMutlQueryCondtion(condition, String.Concat(" end_date = '", endDate.Trim(), "'")); } string finalSqlCmd = String.Format(GetGroupBuyTicketsSqlCmdTemplate, String.Empty, sortOrder, orderDire); if (condition != "WHERE") { finalSqlCmd = String.Format(GetGroupBuyTicketsSqlCmdTemplate, condition, sortOrder, orderDire); } SqlDBHelper dbHelper = new SqlDBHelper(); DataTable data = dbHelper.ExecuteQuery(finalSqlCmd); return(data); }
public static DataTable GetKPI2L1(string Frequency, string From = null, string To = null) { DataTable dt = new DataTable(); var sqlParam = new SqlParameter[3]; sqlParam[0] = new SqlParameter("@Frequency", Frequency) { SqlDbType = SqlDbType.NVarChar }; sqlParam[1] = new SqlParameter("@From", From) { SqlDbType = SqlDbType.DateTime }; sqlParam[2] = new SqlParameter("@To", null) { SqlDbType = SqlDbType.DateTime }; dt = SqlDBHelper.ExecuteQuery("sp_SA_L1", sqlParam); return(dt); }
private static bool UpdateTicketInitialAllocatedQtyAndStatus(string gid, int status, int productSysNo) { bool result = false; string getInitialAllocatedQtySqlTxt = String.Format(GetInitialAllocatedQtySqlCmd, productSysNo); SqlDBHelper dbHelper = new SqlDBHelper(); try { DataTable data1 = dbHelper.ExecuteQuery(getInitialAllocatedQtySqlTxt); if (data1.Rows.Count > 0) { int initialAllocatedQty = int.Parse(data1.Rows[0]["saledBefore"].ToString()); string updateInitialAllocatedQtySqlTxt = String.Format(UpdateInitialAllocatedQtyAndStatusSqlCmd, status, initialAllocatedQty, gid); if (dbHelper.ExecuteNonQuery(updateInitialAllocatedQtySqlTxt) > 0) { result = true; } } } catch { } return(result); }
private static bool UpdateTicketInitialAllocatedQtyAndStatus(string gid, int status, int productSysNo) { bool result = false; string getInitialAllocatedQtySqlTxt = String.Format(GetInitialAllocatedQtySqlCmd, productSysNo); SqlDBHelper dbHelper = new SqlDBHelper(); try { DataTable data1 = dbHelper.ExecuteQuery(getInitialAllocatedQtySqlTxt); if (data1.Rows.Count > 0) { int initialAllocatedQty = int.Parse(data1.Rows[0]["saledBefore"].ToString()); string updateInitialAllocatedQtySqlTxt = String.Format(UpdateInitialAllocatedQtyAndStatusSqlCmd, status, initialAllocatedQty, gid); if (dbHelper.ExecuteNonQuery(updateInitialAllocatedQtySqlTxt) > 0) { result = true; } } } catch { } return result; }
/// <summary> /// 获取团购单 /// </summary> /// <param name="id"></param> /// <param name="title"></param> /// <param name="city"></param> /// <param name="productSysNo"></param> /// <param name="status"></param> /// <param name="createDate"></param> /// <param name="startDate"></param> /// <param name="endDate"></param> /// <returns></returns> public static DataTable GetTickets(string id, string title, string city, string productSysNo, int status, string createDate, string startDate, string endDate, string sortOrder = "groupid", string orderDire = "ASC") { string condition = "WHERE"; if (id.IsSafeString()) { condition += String.Concat(" groupid=", int.Parse(id)); } if (title.IsSafeString()) { condition += GroupBuyUtility.CombineMutlQueryCondtion(condition, String.Concat(" title LIKE '%", title, "%'")); } if (city.IsSafeString()) { condition += GroupBuyUtility.CombineMutlQueryCondtion(condition, String.Concat(" location='", city, "'")); } if (productSysNo.IsSafeString()) { condition += GroupBuyUtility.CombineMutlQueryCondtion(condition, String.Concat(" product_sysNo=", int.Parse(productSysNo))); } if (status != 0) { condition += GroupBuyUtility.CombineMutlQueryCondtion(condition, String.Concat(" status=", status)); } if (createDate.IsSafeString()) { condition += GroupBuyUtility.CombineMutlQueryCondtion(condition, String.Concat(" create_date LIKE '%", createDate.Trim(), "'")); } if (startDate.IsSafeString()) { condition += GroupBuyUtility.CombineMutlQueryCondtion(condition, String.Concat(" start_date = '", startDate.Trim(), "'")); } if (endDate.IsSafeString()) { condition += GroupBuyUtility.CombineMutlQueryCondtion(condition, String.Concat(" end_date = '", endDate.Trim(), "'")); } string finalSqlCmd = String.Format(GetGroupBuyTicketsSqlCmdTemplate, String.Empty, sortOrder, orderDire); if (condition != "WHERE") { finalSqlCmd = String.Format(GetGroupBuyTicketsSqlCmdTemplate, condition, sortOrder, orderDire); } SqlDBHelper dbHelper = new SqlDBHelper(); DataTable data = dbHelper.ExecuteQuery(finalSqlCmd); return data; }
/// <summary> /// 通过ID 获得团购单的详细信息 /// </summary> /// <param name="id"></param> /// <returns></returns> public static DataTable GetTicketById(string id) { int groupId = int.Parse(id); string sqlCmd = String.Format(GetTicketDetailSqlCmdTemplate, groupId); SqlDBHelper dbHelper = new SqlDBHelper(); DataTable data = dbHelper.ExecuteQuery(sqlCmd); return data; }
/// <summary> /// 获取用户的订单和订单商品 /// </summary> /// <param name="customerSysno"></param> /// <param name="pageIndex"></param> /// <param name="pageNum"></param> /// <returns></returns> public static List <OrderModuel> GetMyOrder(int customerSysno, int pageIndex, int pageCount) { try { SqlDBHelper dbHelper = new SqlDBHelper(); string sql1 = String.Format(getPagedOrderListSqlCmdTemplate, pageCount, customerSysno, pageIndex, customerSysno); DataTable data1 = dbHelper.ExecuteQuery(sql1); if (data1.Rows.Count > 0) { int rowCount1 = data1.Rows.Count; List <OrderModuel> orderList = new List <OrderModuel>(); for (int i = 0; i < rowCount1; i++) { orderList.Add(new OrderModuel() { SysNo = int.Parse(data1.Rows[i]["sysno"].ToString().Trim()), SoId = int.Parse(data1.Rows[i]["soid"].ToString().Trim()), OrderDate = DateTime.Parse(data1.Rows[i]["orderdate"].ToString().Trim()), TotalCash = decimal.Parse(data1.Rows[i]["totalcash"].ToString().Trim()), Status = int.Parse(data1.Rows[i]["status"].ToString().Trim()), Memo = data1.Rows[i]["memo"].ToString().Trim(), PointPay = int.Parse(data1.Rows[i]["pointpay"].ToString().Trim()), Pointamt = int.Parse(data1.Rows[i]["pointamt"].ToString().Trim()), IsNet = int.Parse(data1.Rows[i]["isNet"].ToString().Trim()), PagementPage = data1.Rows[i]["paymentpage"].ToString().Trim(), IsPayWhenEcv = int.Parse(data1.Rows[i]["ispaywhenrecv"].ToString().Trim()), DoNo = data1.Rows[i]["doNo"].Equals(DBNull.Value)?0:int.Parse(data1.Rows[i]["paymentpage"].ToString().Trim()), }); } foreach (var orderListItem in orderList) { string sql2 = String.Format(getOrderProductListSqlCmdTemplate, orderListItem.SysNo); DataTable data2 = dbHelper.ExecuteQuery(sql2); orderListItem.ProductList = new List <OrderProductModuel>(); if (data2.Rows.Count > 0) { int rowCount2 = data2.Rows.Count; for (int j = 0; j < rowCount2; j++) { orderListItem.ProductList.Add(new OrderProductModuel() { ProductSysNo = data2.Rows[j]["ProductSysNo"].ToString().Trim(), Cost = data2.Rows[j]["cost"].ToString().Trim(), C1SysNo = int.Parse(data2.Rows[j]["C1SysNo"].ToString().Trim()), C2SysNo = int.Parse(data2.Rows[j]["C2SysNo"].ToString().Trim()), C3SysNo = int.Parse(data2.Rows[j]["C3SysNo"].ToString().Trim()), ImgPath = data2.Rows[j]["product_simg"].ToString().Trim(), Quantity = int.Parse(data2.Rows[j]["Quantity"].ToString().Trim()), ProductBriefName = data2.Rows[j]["BriefName"].ToString().Trim(), }); } } } return(orderList); } else { return(null); } } catch { return(null); } }
/// <summary> /// 获取用户的订单和订单商品 /// </summary> /// <param name="customerSysno"></param> /// <param name="pageIndex"></param> /// <param name="pageNum"></param> /// <returns></returns> public static List<OrderModuel> GetMyOrder(int customerSysno, int pageIndex, int pageCount) { try { SqlDBHelper dbHelper = new SqlDBHelper(); string sql1 = String.Format(getPagedOrderListSqlCmdTemplate, pageCount, customerSysno, pageIndex, customerSysno); DataTable data1 = dbHelper.ExecuteQuery(sql1); if (data1.Rows.Count > 0) { int rowCount1 = data1.Rows.Count; List<OrderModuel> orderList=new List<OrderModuel>(); for (int i = 0; i < rowCount1; i++) { orderList.Add(new OrderModuel() { SysNo=int.Parse(data1.Rows[i]["sysno"].ToString().Trim()), SoId = int.Parse(data1.Rows[i]["soid"].ToString().Trim()), OrderDate = DateTime.Parse(data1.Rows[i]["orderdate"].ToString().Trim()), TotalCash =decimal.Parse(data1.Rows[i]["totalcash"].ToString().Trim()), Status = int.Parse(data1.Rows[i]["status"].ToString().Trim()), Memo = data1.Rows[i]["memo"].ToString().Trim(), PointPay = int.Parse(data1.Rows[i]["pointpay"].ToString().Trim()), Pointamt = int.Parse(data1.Rows[i]["pointamt"].ToString().Trim()), IsNet = int.Parse(data1.Rows[i]["isNet"].ToString().Trim()), PagementPage = data1.Rows[i]["paymentpage"].ToString().Trim(), IsPayWhenEcv = int.Parse(data1.Rows[i]["ispaywhenrecv"].ToString().Trim()), DoNo = data1.Rows[i]["doNo"].Equals(DBNull.Value)?0:int.Parse(data1.Rows[i]["paymentpage"].ToString().Trim()), }); } foreach (var orderListItem in orderList) { string sql2 = String.Format(getOrderProductListSqlCmdTemplate, orderListItem.SysNo); DataTable data2 = dbHelper.ExecuteQuery(sql2); orderListItem.ProductList = new List<OrderProductModuel>(); if (data2.Rows.Count > 0) { int rowCount2 = data2.Rows.Count; for(int j=0;j<rowCount2;j++) { orderListItem.ProductList.Add(new OrderProductModuel() { ProductSysNo = data2.Rows[j]["ProductSysNo"].ToString().Trim(), Cost = data2.Rows[j]["cost"].ToString().Trim(), C1SysNo = int.Parse(data2.Rows[j]["C1SysNo"].ToString().Trim()), C2SysNo = int.Parse(data2.Rows[j]["C2SysNo"].ToString().Trim()), C3SysNo = int.Parse(data2.Rows[j]["C3SysNo"].ToString().Trim()), ImgPath = data2.Rows[j]["product_simg"].ToString().Trim(), Quantity = int.Parse(data2.Rows[j]["Quantity"].ToString().Trim()), ProductBriefName = data2.Rows[j]["BriefName"].ToString().Trim(), }); } } } return orderList; } else { return null; } } catch { return null; } }