public HttpResponseBase GetUserOrdersSubtotalList()
{
int totalCount = 0;
List<UserOrdersSubtotalQuery> stores = new List<UserOrdersSubtotalQuery>();
string json = string.Empty;
try
{
csMgr = new ConsumeStatisticMgr(SqlConnectionString);
UserOrdersSubtotalQuery query = new UserOrdersSubtotalQuery();
query.Start = Convert.ToInt32(Request.Params["Start"] ?? "0");
if (!string.IsNullOrEmpty(Request.Params["Limit"]))
{
query.Limit = Convert.ToInt32(Request.Params["Limit"]);
}
if (!string.IsNullOrEmpty(Request.Params["startMoney"]))
{
query.startMoney = Convert.ToDouble(Request.Params["startMoney"]);
}
if (!string.IsNullOrEmpty(Request.Params["endMoney"]))
{
query.endMoney = Convert.ToDouble(Request.Params["endMoney"]);
}
if (!string.IsNullOrEmpty(Request.Params["startTime"]))
{
query.startTime = Convert.ToDateTime(Request.Params["startTime"]);
query.startTime = Convert.ToDateTime(query.startTime.ToString("yyyy-MM-dd HH:mm:ss"));
}
if (!string.IsNullOrEmpty(Request.Params["endTime"]))
{
query.endTime = Convert.ToDateTime(Request.Params["endTime"]);
query.endTime = Convert.ToDateTime(query.endTime.ToString("yyyy-MM-dd HH:mm:ss"));
}
if (!string.IsNullOrEmpty(Request.Params["searchType"]))
{
query.searchType = Convert.ToInt32(Request.Params["searchType"]);
}
if (!string.IsNullOrEmpty(Request.Params["searchKey"]))
{
query.searchKey = Request.Params["searchKey"];
}
stores = csMgr.GetUserOrdersSubtotal(query, out totalCount);
IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
//这里使用自定义日期格式，如果不使用的话，默认是ISO8601格式
timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
json = "{success:true,totalCount:" + totalCount + ",data:" + JsonConvert.SerializeObject(stores, Formatting.Indented, timeConverter) + "}";//返回json數據
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
json = "{success:false}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
public List<UserOrdersSubtotalQuery> GetUserOrdersSubtotal(UserOrdersSubtotalQuery query, out int totalCount)
{
try
{
return dao.GetUserOrdersSubtotal(query, out totalCount);
}
catch (Exception ex)
{
throw new Exception("ConsumeStatisticMgr->GetUserOrdersSubtotal:" + ex.Message);
}
}
public List<UserOrdersSubtotalQuery> GetUserOrdersSubtotal(UserOrdersSubtotalQuery query, out int totalCount)
{
totalCount = 0;
StringBuilder sql = new StringBuilder();
StringBuilder sqlCondi = new StringBuilder();
StringBuilder sqlCount = new StringBuilder();
sql.Append("SELECT uos.row_id, u.user_id,u.user_name,uos.year,uos.month,uos.order_product_subtotal,uos.normal_product_subtotal,uos.low_product_subtotal,uos.buy_count,uos.last_buy_time,uos.buy_avg,uos.note,uos.create_datetime ");
sqlCount.Append("select count(u.user_id) as totalCount ");
sqlCondi.Append(" from users as u LEFT JOIN user_orders_subtotal as uos on u.user_id=uos.user_id WHERE 1=1 ");
try
{
if (query.startTime!=DateTime.MinValue)
{
sqlCondi.AppendFormat(" and uos.create_datetime BETWEEN '{0}' and '{1}'", CommonFunction.DateTimeToString(query.startTime), CommonFunction.DateTimeToString(query.endTime));
}
if (query.startMoney != 0)
{
sqlCondi.AppendFormat(" and uos.order_product_subtotal >= {0}",query.startMoney);//BETWEEN {0} and {1}", query.startMoney, query.endMoney);
}
if (query.endMoney != 0)
{
sqlCondi.AppendFormat(" and uos.order_product_subtotal <= {0}",query.endMoney);//BETWEEN {0} and {1}", query.startMoney, query.endMoney);
}
if (!string.IsNullOrEmpty(query.searchKey))
{
if (query.searchType == 1)
{
sqlCondi.AppendFormat(" and u.user_id = {0} ", query.searchKey);
}
else if (query.searchType == 2)
{
sqlCondi.AppendFormat(" and u.user_name like N'%{0}%' ", query.searchKey);
}
}
if (query.IsPage)
{
try
{
sqlCount.Append(sqlCondi.ToString());
DataTable dt = _accessMySql.getDataTable(sqlCount.ToString());
if (dt.Rows.Count != 0)
{
totalCount = Convert.ToInt32(dt.Rows[0]["totalCount"].ToString());
}
}
catch (Exception ex)
{
throw new Exception("ConsumeStatisticDao-->GetUserOrdersSubtotal: " + ex.Message + "sqlCount:" + sqlCount.ToString());
}
sqlCondi.AppendFormat(" limit {0},{1};", query.Start, query.Limit);
}
sql.Append(sqlCondi.ToString());
return _accessMySql.getDataTableForObj<UserOrdersSubtotalQuery>(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("ConsumeStatisticDao-->GetUserOrdersSubtotal: " + ex.Message + "sql:" + sql.ToString());
}
}
