void LoadData()
{
string clientName = txtName.Value.Trim();
string clientID = txtMerchantID.Text.Trim();
string startDate = txtStartTime.Value.Trim();
string oprID = txtOprID.Value.Trim();
string endDate = txtEndTime.Value.Trim();
WebTradeCondition condition = new WebTradeCondition();
condition.ClientID = clientID;
if (!string.IsNullOrEmpty(startDate))
{
condition.StartDate = DateTime.Parse(startDate);
}
if (!string.IsNullOrEmpty(endDate))
{
condition.EndDate = DateTime.Parse(endDate).AddDays(1);
}
HYCashierDAL.CahierMgrDAL dal = new HYCashierDAL.CahierMgrDAL();
condition.OprID = oprID;
int?count = 0;
count = dal.GetTradeInfoCount(condition);
if (count == null)
{
AspNetPager1.RecordCount = 0;
}
else
{
AspNetPager1.RecordCount = (int)count;
}
DataTable dt = dal.GetTradeInfoPager(condition, AspNetPager1.PageSize, AspNetPager1.CurrentPageIndex);
dataGrid.DataSource = dt;
dataGrid.DataBind();
}
public DataTable GetTradeInfoPager(WebTradeCondition queryCondition, int pageSize, int pageIndex)
{
DataTable dt = null;
string sql = @"select tradetime, t1.localorderno,t1.clientID,t1.amount,t1.oprid,t1.tradestatus,
t1.tradetype, t2.clientName from tradeinfo t1 left join clientinfo t2 on t1.clientid=t2.clientid
where tradestatus='Ok' ";
List <MySqlParameter> paras = new List <MySqlParameter>();
if (!string.IsNullOrEmpty(queryCondition.ClientID))
{
sql += " and t1.ClientID=?ClientID";
MySqlParameter para = new MySqlParameter();
para.ParameterName = "?ClientID";
para.Value = queryCondition.ClientID;
para.Direction = ParameterDirection.Input;
paras.Add(para);
}
if (!string.IsNullOrEmpty(queryCondition.ClientName))
{
sql += " and t2.ClientName like ?ClientName";
MySqlParameter para = new MySqlParameter();
para.ParameterName = "?ClientName";
para.Value = "%" + queryCondition.ClientName + "%";
para.Direction = ParameterDirection.Input;
paras.Add(para);
}
if (!string.IsNullOrEmpty(queryCondition.OprID))
{
sql += " and OprID=?OprID";
MySqlParameter para = new MySqlParameter();
para.ParameterName = "?OprID";
para.Value = queryCondition.OprID;
para.Direction = ParameterDirection.Input;
paras.Add(para);
}
if (!string.IsNullOrEmpty(queryCondition.LocalFlowNo))
{
sql += " and LocalOrderNo=?LocalOrderNo";
MySqlParameter para = new MySqlParameter();
para.ParameterName = "?LocalOrderNo";
para.Value = queryCondition.LocalFlowNo;
para.Direction = ParameterDirection.Input;
paras.Add(para);
}
if (!string.IsNullOrEmpty(queryCondition.ServerFlowNo))
{
sql += " and ServerFlowNo=?ServerFlowNo";
MySqlParameter para = new MySqlParameter();
para.ParameterName = "?ServerFlowNo";
para.Value = queryCondition.ServerFlowNo;
para.Direction = ParameterDirection.Input;
paras.Add(para);
}
if (queryCondition.StartDate != null)
{
sql += " and TradeTime>=?StartDate";
MySqlParameter para = new MySqlParameter();
para.ParameterName = "?StartDate";
para.Value = queryCondition.StartDate;
para.Direction = ParameterDirection.Input;
para.DbType = DbType.DateTime;
paras.Add(para);
}
if (queryCondition.EndDate != null)
{
sql += " and TradeTime<?EndDate";
MySqlParameter para = new MySqlParameter();
para.ParameterName = "?EndDate";
para.Value = queryCondition.EndDate;
para.Direction = ParameterDirection.Input;
para.DbType = DbType.DateTime;
paras.Add(para);
}
int startIndex = pageSize * (pageIndex - 1);
sql += " limit " + startIndex.ToString() + " ," + pageSize.ToString();;
dt = DbHelper.ExecuteDataTable(sql, paras.ToArray());
return(dt);
}
public int GetTradeInfoCount(WebTradeCondition queryCondition)
{
int count = 0;
string sql = @"select count(t1.localorderno) from tradeinfo t1 left join
clientinfo t2 on t1.clientID=t2.clientID where tradestatus='Ok' ";
List <MySqlParameter> paras = new List <MySqlParameter>();
if (!string.IsNullOrEmpty(queryCondition.ClientID))
{
sql += " and t1.ClientID=?ClientID";
MySqlParameter para = new MySqlParameter();
para.ParameterName = "?ClientID";
para.Value = queryCondition.ClientID;
para.Direction = ParameterDirection.Input;
paras.Add(para);
}
if (!string.IsNullOrEmpty(queryCondition.OprID))
{
sql += " and OprID=?OprID";
MySqlParameter para = new MySqlParameter();
para.ParameterName = "?OprID";
para.Value = queryCondition.OprID;
para.Direction = ParameterDirection.Input;
paras.Add(para);
}
if (!string.IsNullOrEmpty(queryCondition.LocalFlowNo))
{
sql += " and LocalOrderNo=?LocalOrderNo";
MySqlParameter para = new MySqlParameter();
para.ParameterName = "?LocalOrderNo";
para.Value = queryCondition.LocalFlowNo;
para.Direction = ParameterDirection.Input;
paras.Add(para);
}
if (!string.IsNullOrEmpty(queryCondition.ServerFlowNo))
{
sql += " and ServerFlowNo=?ServerFlowNo";
MySqlParameter para = new MySqlParameter();
para.ParameterName = "?ServerFlowNo";
para.Value = queryCondition.ServerFlowNo;
para.Direction = ParameterDirection.Input;
paras.Add(para);
}
if (!string.IsNullOrEmpty(queryCondition.ClientName))
{
sql += " and t2.ClientName like ?ClientName";
MySqlParameter para = new MySqlParameter();
para.ParameterName = "?ClientName";
para.Value = "%" + queryCondition.ClientName + "%";
para.Direction = ParameterDirection.Input;
paras.Add(para);
}
if (queryCondition.StartDate != null)
{
sql += " and TradeTime>=?StartDate";
MySqlParameter para = new MySqlParameter();
para.ParameterName = "?StartDate";
para.Value = queryCondition.StartDate;
para.Direction = ParameterDirection.Input;
para.DbType = DbType.DateTime;
paras.Add(para);
}
if (queryCondition.EndDate != null)
{
sql += " and TradeTime<?EndDate";
MySqlParameter para = new MySqlParameter();
para.ParameterName = "?EndDate";
para.Value = queryCondition.EndDate;
para.Direction = ParameterDirection.Input;
para.DbType = DbType.DateTime;
paras.Add(para);
}
object obj = DbHelper.ExecuteScalar(sql, paras.ToArray());
if (obj != null)
{
count = int.Parse(obj.ToString());
}
return(count);
}
public int GetTradeInfoCount(WebTradeCondition queryCondition)
{
int count = 0;
string sql = "select count(*) from tradeinfo where 1=1 ";
List <MySqlParameter> paras = new List <MySqlParameter>();
if (!string.IsNullOrEmpty(queryCondition.ClientID))
{
sql += " and ClientID=?ClientID";
MySqlParameter para = new MySqlParameter();
para.ParameterName = "?ClientID";
para.Value = queryCondition.ClientID;
para.Direction = ParameterDirection.Input;
paras.Add(para);
}
if (!string.IsNullOrEmpty(queryCondition.OprID))
{
sql += " and OprID=?OprID";
MySqlParameter para = new MySqlParameter();
para.ParameterName = "?OprID";
para.Value = queryCondition.OprID;
para.Direction = ParameterDirection.Input;
paras.Add(para);
}
if (!string.IsNullOrEmpty(queryCondition.LocalFlowNo))
{
sql += " and LocalOrderNo=?LocalOrderNo";
MySqlParameter para = new MySqlParameter();
para.ParameterName = "?LocalOrderNo";
para.Value = queryCondition.LocalFlowNo;
para.Direction = ParameterDirection.Input;
paras.Add(para);
}
if (!string.IsNullOrEmpty(queryCondition.ServerFlowNo))
{
sql += " and ServerFlowNo=?ServerFlowNo";
MySqlParameter para = new MySqlParameter();
para.ParameterName = "?ServerFlowNo";
para.Value = queryCondition.ServerFlowNo;
para.Direction = ParameterDirection.Input;
paras.Add(para);
}
if (queryCondition.StartDate != null)
{
sql += " and TradeTime>=?StartDate";
MySqlParameter para = new MySqlParameter();
para.ParameterName = "?StartDate";
para.Value = queryCondition.StartDate;
para.Direction = ParameterDirection.Input;
para.DbType = DbType.DateTime;
paras.Add(para);
}
if (queryCondition.EndDate != null)
{
sql += " and TradeTime<?EndDate";
MySqlParameter para = new MySqlParameter();
para.ParameterName = "?EndDate";
para.Value = queryCondition.EndDate;
para.Direction = ParameterDirection.Input;
para.DbType = DbType.DateTime;
paras.Add(para);
}
count = (int)DbHelper.ExecuteScalar(sql, paras.ToArray());
return(count);
}
public DataTable GetTradeInfoPager(WebTradeCondition queryCondition, int pageSize, int pageIndex)
{
DataTable dt = null;
string sql = "select * from tradeinfo where 1=1 ";
List <MySqlParameter> paras = new List <MySqlParameter>();
if (!string.IsNullOrEmpty(queryCondition.ClientID))
{
sql += " and ClientID=?ClientID";
MySqlParameter para = new MySqlParameter();
para.ParameterName = "?ClientID";
para.Value = queryCondition.ClientID;
para.Direction = ParameterDirection.Input;
paras.Add(para);
}
if (!string.IsNullOrEmpty(queryCondition.OprID))
{
sql += " and OprID=?OprID";
MySqlParameter para = new MySqlParameter();
para.ParameterName = "?OprID";
para.Value = queryCondition.OprID;
para.Direction = ParameterDirection.Input;
paras.Add(para);
}
if (!string.IsNullOrEmpty(queryCondition.LocalFlowNo))
{
sql += " and LocalOrderNo=?LocalOrderNo";
MySqlParameter para = new MySqlParameter();
para.ParameterName = "?LocalOrderNo";
para.Value = queryCondition.LocalFlowNo;
para.Direction = ParameterDirection.Input;
paras.Add(para);
}
if (!string.IsNullOrEmpty(queryCondition.ServerFlowNo))
{
sql += " and ServerFlowNo=?ServerFlowNo";
MySqlParameter para = new MySqlParameter();
para.ParameterName = "?ServerFlowNo";
para.Value = queryCondition.ServerFlowNo;
para.Direction = ParameterDirection.Input;
paras.Add(para);
}
if (queryCondition.StartDate != null)
{
sql += " and TradeTime>=?StartDate";
MySqlParameter para = new MySqlParameter();
para.ParameterName = "?StartDate";
para.Value = queryCondition.StartDate;
para.Direction = ParameterDirection.Input;
para.DbType = DbType.DateTime;
paras.Add(para);
}
if (queryCondition.EndDate != null)
{
sql += " and TradeTime<?EndDate";
MySqlParameter para = new MySqlParameter();
para.ParameterName = "?EndDate";
para.Value = queryCondition.EndDate;
para.Direction = ParameterDirection.Input;
para.DbType = DbType.DateTime;
paras.Add(para);
}
int startIndex = pageSize * pageIndex;
sql += " limit " + startIndex.ToString() + " ," + pageSize.ToString();;
dt = DbHelper.ExecuteDataTable(sql, paras.ToArray());
return(dt);
}