/// <summary>
/// 區域包列表頁查詢
/// </summary>
/// <param name="query"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public List<AreaPacket> QueryAll(AreaPacket query, out int totalCount)
{
query.Replace4MySQL();
StringBuilder sb = new StringBuilder();
try
{
StringBuilder sql = new StringBuilder();
StringBuilder sqlcount = new StringBuilder();
StringBuilder sqlfrom = new StringBuilder();
StringBuilder sqlwhere = new StringBuilder();
sql.Append(@"select packet_id,packet_name,show_number,packet_sort,element_type,packet_status,packet_desc,packet_createdate,packet_updatedate,create_userid,update_userid ");
sqlcount.Append("select count(packet_id) as totalcounts ");
sqlfrom.Append(" FROM area_packet ");
if (query.packet_id != 0)
{
sqlwhere.AppendFormat(" and packet_id={0}", query.packet_id);
}
if (!string.IsNullOrEmpty(query.packet_name) || !string.IsNullOrEmpty(query.packet_desc))
{
sqlwhere.AppendFormat(" and (packet_name like N'%{0}%' or packet_desc like '%{1}%')", query.packet_name, query.packet_desc);
}
if (query.element_type != 0)
{
sqlwhere.AppendFormat(" and element_type='{0}'", query.element_type);
}
if (sqlwhere.Length != 0)
{
sqlfrom.Append(" WHERE ");
sqlfrom.Append(sqlwhere.ToString().TrimStart().Remove(0, 3));
}
totalCount = 0;
if (query.IsPage)
{
sb.Append(sqlcount.ToString() + sqlfrom.ToString() + ";");
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(" ORDER BY packet_id DESC ");
sqlfrom.AppendFormat(" limit {0},{1}", query.Start, query.Limit);
}
sb.Append(sql.ToString() + sqlfrom.ToString());
return _access.getDataTableForObj<AreaPacket>(sql.ToString() + sqlfrom.ToString());
}
catch (Exception ex)
{
throw new Exception("AreaPacketDao-->QueryAll-->" + ex.Message + sb.ToString(), ex);
}
}
public JsonResult UpdateAreaPacketActive()
{
string jsonStr = string.Empty;
try
{
_areaPacketMgr = new AreaPacketMgr(mySqlConnectionString);
int id = Convert.ToInt32(Request.Params["id"]);
AreaPacket model = new AreaPacket();
model.packet_id = id;
model = _areaPacketMgr.GetModelById(model);
int statusValue = Convert.ToInt32(Request.Params["active"]);
model.packet_id = id;
model.packet_status = statusValue;
model.update_userid = int.Parse((System.Web.HttpContext.Current.Session["caller"] as Caller).user_id.ToString());
model.packet_updatedate = DateTime.Now;
if (_areaPacketMgr.UpAreaPacketStatus(model) > 0)
{
return Json(new { success = "true" });
}
else
{
return Json(new { success = "false" });
}
}
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);
return Json(new { success = "false" });
}
}
/// <summary>
/// 新增編輯區域包信息
/// </summary>
/// <returns></returns>
public HttpResponseBase AreaPactetSave()
{
string json = string.Empty;
AreaPacket ap = new AreaPacket();
IAreaPactetImplMgr _iareaPacketMgr = new AreaPacketMgr(mySqlConnectionString);
try
{
if (string.IsNullOrEmpty(Request.Params["id"]))
{
if (!string.IsNullOrEmpty(Request.Params["name"]))
{
ap.packet_name = Request.Params["name"].ToString();
}
if (!string.IsNullOrEmpty(Request.Params["element_type"]))
{
ap.element_type = Convert.ToInt32(Request.Params["element_type"]);
}
if (!string.IsNullOrEmpty(Request.Params["packet_desc"]))
{
ap.packet_desc = Request.Params["packet_desc"].ToString();
}
if (!string.IsNullOrEmpty(Request.Params["show_number"]))
{
ap.show_number = Convert.ToInt32(Request.Params["show_number"]);
}
if (!string.IsNullOrEmpty(Request.Params["packet_sort"]))
{
ap.packet_sort = Convert.ToInt32(Request.Params["packet_sort"].ToString());
}
ap.create_userid = (Session["caller"] as Caller).user_id;
ap.update_userid = (Session["caller"] as Caller).user_id;
ap.packet_createdate = DateTime.Now;
ap.packet_updatedate = DateTime.Now;
int i = _iareaPacketMgr.AreaPacketSave(ap);
if (i > 0)
{
json = "{success:true}";
}
}
else
{
AreaPacket areaPacketTemp = new AreaPacket();
areaPacketTemp.packet_id = Convert.ToInt32(Request.Params["id"]);
AreaPacket oldap = _iareaPacketMgr.GetModelById(areaPacketTemp);
ap.packet_id = Convert.ToInt32(Request.Params["id"]);
ap.packet_name = Request.Params["name"].ToString();
if (!string.IsNullOrEmpty(Request.Params["element_type"]))
{
ap.element_type = Convert.ToInt32(Request.Params["element_type"]);
}
else
{
ap.element_type = oldap.element_type;
}
ap.packet_desc = Request.Params["packet_desc"].ToString();
ap.show_number = Convert.ToInt32(Request.Params["show_number"]);
ap.packet_sort = Convert.ToInt32(Request.Params["packet_sort"].ToString());
ap.update_userid = (Session["caller"] as Caller).user_id;
ap.packet_updatedate = DateTime.Now;
ap.packet_status = oldap.packet_status;
int j = _iareaPacketMgr.AreaPacketSave(ap);
if (j > 0)
{
json = "{success:true}";
}
}
}
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:true,data:[]}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
/// <summary>
/// 區域包列表
/// </summary>
/// <returns></returns>
public HttpResponseBase AreaPacketList()
{
List<AreaPacket> stores = new List<AreaPacket>();
string json = string.Empty;
try
{
AreaPacket query = new AreaPacket();
query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");//用於分頁的變量
query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "20");//用於分頁的變量
if (!string.IsNullOrEmpty(Request.Params["serchcontent"]))
{
query.packet_name = Request.Params["serchcontent"].ToString();
query.packet_desc = Request.Params["serchcontent"].ToString();
}
if (!string.IsNullOrEmpty(Request.Params["serchtype"]))
{
query.element_type = Convert.ToInt32(Request.Params["serchtype"].ToString());
}
_areaPacketMgr = new AreaPacketMgr(mySqlConnectionString);
int totalCount = 0;
stores = _areaPacketMgr.QueryAll(query, out totalCount);
IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
//这里使用自定义日期格式，如果不使用的话，默认是ISO8601格式
timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
//listUser是准备转换的对象
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:true,totalCount:0,data:[]}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
public int UpAreaPacketStatus(AreaPacket model)
{
return _areaPacketDao.UpAreaPacketStatus(model);
}
public AreaPacket GetModelById(AreaPacket model)
{
return _areaPacketDao.GetModelById(model);
}
public int AreaPacketSave(AreaPacket ap)
{
return _areaPacketDao.AreaPacketSave(ap);
}
/// <summary>
/// 保存新增區域數據
/// </summary>
/// <param name="ba"></param>
/// <returns></returns>
public int AreaPacketSave(AreaPacket ap)
{
ap.Replace4MySQL();
StringBuilder strSql = new StringBuilder();
try
{
if (ap.packet_id == 0)
{
strSql.AppendFormat("insert into area_packet (packet_name,show_number,packet_sort,element_type,packet_status,packet_desc,packet_createdate,packet_updatedate,create_userid,update_userid)value('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}')", ap.packet_name, ap.show_number, ap.packet_sort, ap.element_type, ap.packet_status, ap.packet_desc, ap.packet_createdate.ToString("yyyy-MM-dd HH:mm:ss"), ap.packet_updatedate.ToString("yyyy-MM-dd HH:mm:ss"), ap.create_userid, ap.update_userid);
return _access.execCommand(strSql.ToString());
}
else
{
strSql.AppendFormat(@"update area_packet set
packet_name='{0}',
packet_desc='{1}',packet_sort='{2}',
packet_updatedate='{3}',
update_userid='{4}',show_number='{5}',element_type='{6}' where packet_id={7}",
ap.packet_name,
ap.packet_desc, ap.packet_sort,
ap.packet_updatedate.ToString("yyyy-MM-dd HH:mm:ss"),
ap.update_userid, ap.show_number, ap.element_type, ap.packet_id);
return _access.execCommand(strSql.ToString());
}
}
catch (Exception ex)
{
throw new Exception("AreaPacketDao-->AreaPacketSave-->" + ex.Message + strSql.ToString(), ex);
}
}
/// <summary>
/// 更新區域包的狀態
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int UpAreaPacketStatus(AreaPacket model)
{
model.Replace4MySQL();
StringBuilder sb = new StringBuilder();
try
{
sb.AppendFormat("update area_packet set packet_status={0},packet_updatedate='{1}',update_userid={2} where packet_id={3} ", model.packet_status, CommonFunction.DateTimeToString(model.packet_updatedate), model.update_userid, model.packet_id);
return _access.execCommand(sb.ToString());
}
catch (Exception ex)
{
throw new Exception("AreaPacketDao-->UpAreaPacketStatus-->" + ex.Message + sb.ToString(), ex);
}
}
public AreaPacket GetModelById(AreaPacket model)
{
model.Replace4MySQL();
StringBuilder sb = new StringBuilder();
try
{
sb.AppendFormat(@"select packet_id,packet_name,show_number,packet_sort,element_type,packet_status,packet_desc,packet_createdate,packet_updatedate,create_userid,update_userid from area_packet where packet_id ={0}", model.packet_id);
return _access.getSinggleObj<AreaPacket>(sb.ToString());
}
catch (Exception ex)
{
throw new Exception("AreaPacketDao-->GetModelById-->" + ex.Message + sb.ToString(), ex);
}
}