public DataTable GetMarketProductMapList(MarketProductMapQuery query, out int totalCount)
{
try
{
return _IMarketProductMapDao.GetMarketProductMapList(query,out totalCount);
}
catch (Exception ex)
{
throw new Exception("MarketProductMapMgr-->GetMarketProductMapList" + ex.Message, ex);
}
}
public DataTable GetMarketProductMapList(MarketProductMapQuery query, out int totalCount)
{
StringBuilder sql = new StringBuilder();
StringBuilder sqlCondi = new StringBuilder();
try
{// FROM_UNIXTIME(UNIX_TIMESTAMP(modified)) as modified_time, FROM_UNIXTIME(UNIX_TIMESTAMP(estimated_send_time)) as estimated_time,created_time
//sql.Append(@" select row_id,event_name,event_desc,q.event_id,q.group_id,link_url,promo_image,device,count_by,count,case when active_now=TRUE then 1 when active_now=false then 0 end as active_now,case when new_user=TRUE then 1 when new_user=false then 0 end as new_user,new_user_date,start,end,active,kuser,muser,created,modified ");
sql.Append(@" select mpm.map_id,mpm.product_category_id,mpm.market_category_id, ");
sql.Append(" mu.user_username as muser,mpm.modified,mc.market_category_code,mc.market_category_name,pc.category_name ");
sqlCondi.Append(" from market_product_map mpm ");
sqlCondi.Append(" left join product_category pc on mpm.product_category_id=pc.category_id left join market_category mc on mpm.market_category_id=mc.market_category_id ");
sqlCondi.Append(" left join manage_user mu on mu.user_id=mpm.muser ");
sqlCondi.Append(" where 1=1 ");
if (!string.IsNullOrEmpty(query.product_number))
{
sqlCondi.AppendFormat(" and mpm.product_category_id='{0}' or mc.market_category_code = '{1}' ", query.product_number, query.product_number);
}
if (!string.IsNullOrEmpty(query.product_name))
{
sqlCondi.AppendFormat(" and mc.market_category_name like '%{0}%' or pc.category_name like '%{1}%' ", query.product_name, query.product_name);
}
//
sqlCondi.Append(" order by mpm.map_id desc ");
totalCount = 0;
if (query.IsPage)
{
DataTable _dt = _access.getDataTable("select count(mpm.map_id) as totalCount " + sqlCondi.ToString());
if (_dt.Rows.Count > 0)
{
totalCount = int.Parse(_dt.Rows[0]["totalCount"].ToString());
}
sqlCondi.AppendFormat(" limit {0},{1} ", query.Start, query.Limit);
}
sql.Append(sqlCondi.ToString());
return _access.getDataTable(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("MarketProductMapDao.GetMarketProductMapList-->" + ex.Message + sql.ToString(), ex);
}
}
public int SavetMarketProductMap(MarketProductMapQuery query)
{
StringBuilder sb = new StringBuilder();
try
{
sb.Append(_IMarketProductMapDao.SelectProductMapCount(query));
DataTable _dt = _access.getDataTable(sb.ToString());
if(Convert.ToInt32(_dt.Rows[0]["num"].ToString()) > 0)
return -1;
sb.Clear();
sb.Append(_IMarketProductMapDao.SavetMarketProductMap(query));
return _access.execCommand(sb.ToString());
}
catch (Exception ex)
{
throw new Exception("MarketProductMapMgr-->SavetMarketProductMap" + ex.Message, ex);
}
}
public string SavetMarketProductMap(MarketProductMapQuery query)
{
StringBuilder sql = new StringBuilder();
try
{
if (query.map_id == 0)
{
sql.Append(@"insert into market_product_map(product_category_id,market_category_id,kuser,muser,created,modified)values( ");
sql.AppendFormat(" '{0}','{1}','{2}','{3}' ", query.product_category_id, query.market_category_id, query.kuser, query.muser);
sql.AppendFormat(" ,'{0}','{1}')", CommonFunction.DateTimeToString(query.created), CommonFunction.DateTimeToString(query.modified));
}
else
{
sql.AppendFormat(@" update market_product_map set product_category_id='{0}', market_category_id='{1}', ", query.product_category_id, query.market_category_id);
sql.AppendFormat(" muser='******',modified='{1}' where map_id='{2}' ", query.muser, CommonFunction.DateTimeToString(query.modified), query.map_id);
}
return sql.ToString();
}
catch (Exception ex)
{
throw new Exception("MarketProductMapDao.SavetMarketProductMap-->" + ex.Message + sql.ToString(), ex);
}
}
public string SelectProductMapCount(MarketProductMapQuery query)
{
StringBuilder sql = new StringBuilder();
try
{
sql.AppendFormat(@" select count(*) as num from market_product_map where product_category_id='{0}' and market_category_id='{1}' and map_id<>'{2}' ", query.product_category_id, query.market_category_id, query.map_id);
return sql.ToString();
}
catch (Exception ex)
{
throw new Exception("MarketProductMapDao.SelectProductMapCount-->" + ex.Message + sql.ToString(), ex);
}
}
public string DeleteMarketProductMap(MarketProductMapQuery model)
{
model.Replace4MySQL();
StringBuilder sql = new StringBuilder();
try
{
sql.Append(@"delete from market_product_map where 1=1");
if (model.map_id != 0)
{
sql.AppendFormat(" and map_id ='{0}'", model.map_id);
}
if (model.market_category_id != 0)
{
sql.AppendFormat(" and market_category_id ='{0}'", model.market_category_id);
}
sql.Append(";");
return sql.ToString();
}
catch (Exception ex)
{
throw new Exception("MarketProductMapDao.DeleteNewPromoQuestion-->" + ex.Message + sql.ToString(), ex);
}
}
/// <summary>
/// 保存美安類別關係
/// </summary>
/// <returns></returns>
public HttpResponseBase SavetMarketProductMap()
{
string json = string.Empty;
try
{
MarketProductMapQuery query = new MarketProductMapQuery();
if (!string.IsNullOrEmpty(Request.Params["map_id"]))
{
query.map_id = Convert.ToInt32(Request.Params["map_id"]);
}
if (!string.IsNullOrEmpty(Request.Params["comboFrontCage_hide"]))
{
query.product_category_id = Convert.ToInt32(Request.Params["comboFrontCage_hide"]);
}
if (!string.IsNullOrEmpty(Request.Params["comboMarket_hide"]))
{
query.market_category_id = Convert.ToInt32(Request.Params["comboMarket_hide"]);
}
query.kuser = (Session["caller"] as Caller).user_id;
query.muser = (Session["caller"] as Caller).user_id;
query.created = DateTime.Now;
query.modified = query.created;
_IMarketProductMap = new MarketProductMapMgr(mySqlConnectionString);
int result = 0;
//新增修改都在Mgr裡面判斷
//通過map_id 是否為零 判斷新增還是編輯
result = _IMarketProductMap.SavetMarketProductMap(query);
if (result > 0)
{
json = "{success:true,msg:\"" + result + "\"}";
}
else
{
json = "{success:true,msg:\"" + result + "\"}";
}
}
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,msg:'0'}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
/// <summary>
/// 美安類別關係設定列表頁
/// </summary>
/// <returns></returns>
public HttpResponseBase GetMarketProductMapList()
{
string jsonStr = string.Empty;
int tranInt = 0;
try
{
MarketProductMapQuery query = new MarketProductMapQuery();
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["search"]))
{
if (int.TryParse(Request.Params["search"].ToString(), out tranInt))
{
query.product_number = Request.Params["search"].ToString();
}
else
{
query.product_name = Request.Params["search"].ToString();
}
}
int totalCount = 0;
_IMarketProductMap = new MarketProductMapMgr(mySqlConnectionString);
DataTable _dt = _IMarketProductMap.GetMarketProductMapList(query, out totalCount);
IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
//这里使用自定义日期格式，如果不使用的话，默认是ISO8601格式
timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
jsonStr = "{success:true,totalCount:" + totalCount + ",data:" + JsonConvert.SerializeObject(_dt, 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,msg:0}";
}
this.Response.Clear();
this.Response.Write(jsonStr.ToString());
this.Response.End();
return this.Response;
}