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