public List<VendorLoginQuery> Query(VendorLoginQuery store, out int totalCount)
{
StringBuilder sql = new StringBuilder();
StringBuilder sqlcount = new StringBuilder();
StringBuilder sqlfrom = new StringBuilder();
sql.Append("SELECT FROM_UNIXTIME(vl.login_createdate) as slogin_createdate,vl.login_id,vl.login_ipfrom,v.vendor_code,v.vendor_name_simple as username,v.vendor_id ");
sqlcount.Append("select count(vl.vendor_id) as totalcounts ");
sqlfrom.Append("FROM vendor_login vl left join vendor v on vl.vendor_id=v.vendor_id ");
sqlfrom.Append(" WHERE 1=1");
if (store.serchstart != DateTime.MinValue)
{
sqlfrom.AppendFormat(" and vl.login_createdate >= '{0}'", CommonFunction.GetPHPTime(store.serchstart.ToString()));
}
if (store.serchend != DateTime.MinValue)
{
sqlfrom.AppendFormat(" and vl.login_createdate <= '{0}'", CommonFunction.GetPHPTime(store.serchend.ToString()));
}
if (!string.IsNullOrEmpty(store.vendor_code))
{
sqlfrom.AppendFormat(" and vendor_code LIKE '%{0}%'", store.vendor_code);
}
if (store.vendor_id == 0)
{
sqlfrom.AppendFormat("");
}
else
{
sqlfrom.AppendFormat(" and vl.vendor_id = '{0}'", store.vendor_id);
}
if (store.login_id != 0)
{
sqlfrom.AppendFormat(" and vl.login_id = '{0}'", store.login_id);
}
sqlfrom.AppendFormat(" ORDER BY login_createdate DESC, vl.vendor_id ASC");
totalCount = 0;
if (store.IsPage)
{
System.Data.DataTable _dt = _access.getDataTable(sqlcount.ToString() + sqlfrom.ToString());
if (_dt != null && _dt.Rows.Count > 0)
{
totalCount = Convert.ToInt32(_dt.Rows[0]["totalcounts"]);
}
sqlfrom.AppendFormat(" limit {0},{1}", store.Start, store.Limit);
}
try
{
return _access.getDataTableForObj<VendorLoginQuery>(sql.ToString() + sqlfrom.ToString());
}
catch (Exception ex)
{
throw new Exception("VendorLoginListDao.Query-->" + ex.Message + sql.ToString() + sqlfrom.ToString(), ex);
}
}
/// <summary>
/// 獲取供應商登錄記錄列表數據
/// </summary>
/// <returns></returns>
public HttpResponseBase GetVendorLoginList()
{
List<VendorLoginQuery> stores = new List<VendorLoginQuery>();
string jsonStr = string.Empty;
try
{
VendorLoginQuery query = new VendorLoginQuery();
query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");//用於分頁的變量
query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "20");//用於分頁的變量
if (!string.IsNullOrEmpty(Request.Params["vendor_id"]))
{
uint vendorid = 0;
if (uint.TryParse(Request.Params["vendor_id"].ToString(), out vendorid))
{
query.vendor_id = vendorid;
}
else
{
query.vendor_code = Request.Params["vendor_id"].ToString();
}
}
if (!string.IsNullOrEmpty(Request.Params["timestart"]))
{
query.serchstart = Convert.ToDateTime(Request.Params["timestart"]);
// query.serchstart = Convert.ToDateTime(query.serchstart.ToString("yyyy-MM-dd 00:00:00"));
}
if (!string.IsNullOrEmpty(Request.Params["timeend"]))
{
query.serchend = Convert.ToDateTime(Request.Params["timeend"]);
// query.serchend = Convert.ToDateTime(query.serchend.ToString("yyyy-MM-dd 23:59:59"));
}
_Ivendorloginlist = new VendorLoginListMgr(connectionString);
int totalCount = 0;
stores = _Ivendorloginlist.Query(query, out totalCount);
foreach (var item in stores)
{
if (!string.IsNullOrEmpty(item.username))
{
item.username = item.username.Substring(0, 1) + "**";
}
}
IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
//这里使用自定义日期格式，如果不使用的话，默认是ISO8601格式
timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
//listUser是准备转换的对象
jsonStr = "{success:true,totalCount:" + totalCount + ",data:" + JsonConvert.SerializeObject(stores, Newtonsoft.Json.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);
jsonStr = "{success：false}";
}
this.Response.Clear();
this.Response.Write(jsonStr);
this.Response.End();
return this.Response;
}