public List<MarketCategoryQuery> GetMarketCategoryList(MarketCategory model)
{
model.Replace4MySQL();
StringBuilder sql = new StringBuilder();
StringBuilder sqlCondi = new StringBuilder();
sql.Append("select m.market_category_id,m.market_category_name,m.market_category_father_id,m.market_category_code");
sql.Append(",m.market_category_sort,m.market_category_status,m.muser,m.modified,f.market_category_father_name");
sqlCondi.Append(" from market_category m ");
sqlCondi.Append(" left join (select market_category_id, market_category_name as market_category_father_name from market_category) f on f.market_category_id=m.market_category_father_id");
sqlCondi.Append(" where 1=1");
if (!string.IsNullOrEmpty(model.market_category_code))
{
sqlCondi.AppendFormat(" and m.market_category_code='{0}'", model.market_category_code);
}
if (model.market_category_id != 0)
{
sqlCondi.AppendFormat(" and m.market_category_id='{0}'", model.market_category_id);
}
if (model.market_category_father_id != 0)
{
sqlCondi.AppendFormat(" and m.market_category_father_id='{0}'", model.market_category_father_id);
}
try
{
sql.Append(sqlCondi.ToString());
return _access.getDataTableForObj<MarketCategoryQuery>(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("MarketCategoryDao.GetMarketCategoryList-->" + ex.Message + sql.ToString(), ex);
}
}
public List<MarketCategoryQuery> GetMarketCategoryList(MarketCategory model, out int totalCount)
{
model.Replace4MySQL();
StringBuilder sql = new StringBuilder();
StringBuilder sqlCondi = new StringBuilder();
sql.Append("select m.market_category_id,m.market_category_name,m.market_category_father_id,m.market_category_code");
sql.Append(",m.market_category_sort,m.market_category_status,m.modified");
sql.Append(",f.market_category_father_name,mu.user_username as muser_name ");//m.muser,
sqlCondi.Append(" from market_category m");
//獲取父級類別名稱
sqlCondi.Append(" left join (select market_category_id, market_category_name as market_category_father_name from market_category) f on f.market_category_id=m.market_category_father_id");
sqlCondi.Append(" left join manage_user mu on mu.user_id=m.muser ");
//獲取特定父級下的類別
sqlCondi.AppendFormat(" where m.market_category_father_id='{0}'", model.market_category_father_id);
if (!string.IsNullOrEmpty(model.market_category_code))
{
sqlCondi.AppendFormat(" and m.market_category_code='{0}'", model.market_category_code);
}
if (!string.IsNullOrEmpty(model.market_category_name))
{
sqlCondi.AppendFormat(" and m.market_category_name like '%{0}%'", model.market_category_name);
}
sqlCondi.Append(" order by m.market_category_id desc");
try
{
totalCount = 0;
if (model.IsPage)
{
System.Data.DataTable _dt = _access.getDataTable("select count(m.market_category_id) as totalCount" + sqlCondi.ToString());
if (_dt != null && _dt.Rows.Count > 0)
{
totalCount = Convert.ToInt32(_dt.Rows[0]["totalCount"].ToString());
}
sqlCondi.AppendFormat(" limit {0},{1}", model.Start, model.Limit);
}
sql.Append(sqlCondi.ToString());
return _access.getDataTableForObj<MarketCategoryQuery>(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("MarketCategoryDao.GetMarketCategoryList-->" + ex.Message + sql.ToString(), ex);
}
}
public string InsertMarketCategory(MarketCategory model)
{
model.Replace4MySQL();
StringBuilder sql = new StringBuilder();
try
{
sql.Append("insert into market_category(market_category_name,market_category_father_id");
sql.Append(",market_category_code,market_category_sort,market_category_status,kuser,muser,created,modified,attribute)");
sql.AppendFormat(" values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}');",
model.market_category_name, model.market_category_father_id, model.market_category_code, model.market_category_sort
, model.market_category_status, model.kuser, model.muser, BLL.gigade.Common.CommonFunction.DateTimeToString(model.created), BLL.gigade.Common.CommonFunction.DateTimeToString(model.modified), model.attribute);
return sql.ToString();
}
catch (Exception ex)
{
throw new Exception("MarketCategoryDao.InsertMarketCategory-->" + ex.Message + sql.ToString(), ex);
}
}
public string UpdateMarketCategory(MarketCategory model)
{
model.Replace4MySQL();
StringBuilder sql = new StringBuilder();
try
{
sql.AppendFormat("update market_category set market_category_name='{0}',market_category_father_id='{1}'", model.market_category_name, model.market_category_father_id);
sql.AppendFormat(",market_category_code='{0}',market_category_sort='{1}',market_category_status='{2}'", model.market_category_code, model.market_category_sort, model.market_category_status);
sql.AppendFormat(",muser='******',modified='{1}',attribute='{2}' where market_category_id='{3}';", model.muser, BLL.gigade.Common.CommonFunction.DateTimeToString(model.modified), model.attribute, model.market_category_id);
return sql.ToString();
}
catch (Exception ex)
{
throw new Exception("MarketCategoryDao.UpdateMarketCategory-->" + ex.Message + sql.ToString(), ex);
}
}
public int MarketCategoryImport(DataRow[] dr)
{
try
{
int res = 0;
StringBuilder sql = new StringBuilder();
List<string> cateIds = new List<string>();
for (int j = 0; j < dr.Length; j++)
{
uint fatherID = 0;//保存上層market_category_id
string[] arryCate = dr[j][1].ToString().Split('/');
for (int i = 0; i < arryCate.Length; i++)//循環每一層類別
{
int firstLeft = arryCate[i].IndexOf('(');//首個左括號的位置
int firstRight = arryCate[i].IndexOf(')', firstLeft);//首個右括號的位置
string cateId = arryCate[i].Substring(firstLeft + 1, firstRight - firstLeft - 1);
string cateName = arryCate[i].Substring(firstRight + 1).Replace('+', '&');
if (!cateIds.Contains(cateId))//判斷是否與本次匯入重複
{
cateIds.Add(cateId);
List<MarketCategoryQuery> store = _marketCategoryDao.GetMarketCategoryList(new MarketCategory { market_category_code = cateId });
if (store.Count == 0)//判斷是否與已有的類別重複
{
MarketCategory model = new MarketCategory();
model.attribute = dr[j][1].ToString();
model.created = DateTime.Now;
model.kuser = (System.Web.HttpContext.Current.Session["caller"] as Caller).user_id;
model.market_category_code = cateId;
if (i == 0)//匯入時每行第一個類別父層為0
{
model.market_category_father_id = 0;
}
else//非首層類別父層為首層的market_category_id
{
model.market_category_father_id = (int)fatherID;
}
model.market_category_name = cateName;
model.market_category_sort = 0;
model.market_category_status = 1;
model.modified = model.created;
model.muser = model.kuser;
sql.Append(_marketCategoryDao.InsertMarketCategory(model));
}
else
{//已經存在則進行修改
MarketCategory model = store.FirstOrDefault();
model.attribute = dr[j][1].ToString();
model.modified = DateTime.Now;
model.muser = (System.Web.HttpContext.Current.Session["caller"] as Caller).user_id;
model.market_category_code = cateId;
if (i == 0)//匯入時每行第一個類別父層為0
{
model.market_category_father_id = 0;
}
else//非首層類別父層為首層的market_category_id
{
model.market_category_father_id = (int)fatherID;
}
model.market_category_name = cateName;
model.market_category_sort = 0;
model.market_category_status = 1;
sql.Append(_marketCategoryDao.UpdateMarketCategory(model));
}
if (i == 0)//執行首條數據
{
res = res + _access.execCommand(sql.ToString());
sql.Clear();
fatherID = _marketCategoryDao.GetMarketCategoryList(new MarketCategory { market_category_code = cateId }).FirstOrDefault().market_category_id;
}
}
else//
{
if (i == 0)//執行首條數據
{
if (!string.IsNullOrEmpty(sql.ToString()))
{
res = res + _access.execCommand(sql.ToString());
sql.Clear();
}
fatherID = _marketCategoryDao.GetMarketCategoryList(new MarketCategory { market_category_code = cateId }).FirstOrDefault().market_category_id;
}
}
}
if (!string.IsNullOrEmpty(sql.ToString()))
{
res = res + _access.execCommand(sql.ToString());
sql.Clear();
}
}
return res;
}
catch (Exception ex)
{
throw new Exception("MarketCategoryMgr-->MarketCategoryImport-->" + ex.Message, ex);
}
}
public int UpdateMarketCategory(MarketCategory model)
{
try
{
string sql = _marketCategoryDao.UpdateMarketCategory(model);
return _access.execCommand(sql);
}
catch (Exception ex)
{
throw new Exception("MarketCategoryMgr-->UpdateMarketCategory-->" + ex.Message, ex);
}
}
public List<MarketCategoryQuery> GetMarketCategoryList(MarketCategory model)
{
try
{
return _marketCategoryDao.GetMarketCategoryList(model);
}
catch (Exception ex)
{
throw new Exception("MarketCategoryMgr-->GetMarketCategoryList-->" + ex.Message, ex);
}
}
public HttpResponseBase SaveMarketCategory()
{
string json = string.Empty;
try
{
_marketCategoryMgr = new MarketCategoryMgr(mySqlConnectionString);
MarketCategory query = new MarketCategory();
// MarketCategoryQuery model = new MarketCategoryQuery();
if (!string.IsNullOrEmpty(Request.Params["market_category_id"]))
{
query.market_category_id = Convert.ToUInt32(Request.Params["market_category_id"]);
//model = _marketCategoryMgr.GetMarketCategoryList(query).FirstOrDefault();
}
if (!string.IsNullOrEmpty(Request.Params["market_category_father_id"]))
{
query.market_category_father_id = Convert.ToInt32(Request.Params["market_category_father_id"]);
}
if (!string.IsNullOrEmpty(Request.Params["market_category_code"]))
{
query.market_category_code = Request.Params["market_category_code"].ToString();
}
if (!string.IsNullOrEmpty(Request.Params["market_category_name"]))
{
query.market_category_name = Request.Params["market_category_name"].ToString();
}
if (!string.IsNullOrEmpty(Request.Params["market_category_sort"]))
{
query.market_category_sort = Convert.ToInt32(Request.Params["market_category_sort"]);
}
if (!string.IsNullOrEmpty(Request.Params["market_category_status"]))
{
query.market_category_status = Convert.ToInt32(Request.Params["market_category_status"]);
}
int result = 0;
if (query.market_category_id == 0)//新增
{
query.kuser = (Session["caller"] as Caller).user_id;
query.muser = query.kuser;
query.created = DateTime.Now;
query.modified = query.created;
result = _marketCategoryMgr.InsertMarketCategory(query);
}
else//修改
{
query.muser = (Session["caller"] as Caller).user_id;
query.modified = DateTime.Now;
result = _marketCategoryMgr.UpdateMarketCategory(query);
}
if (result > 0)
{
json = "{success:true}";
}
else
{
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:false}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
public HttpResponseBase GetFrontCatagory()
{
//Response.Cache.SetOmitVaryStar(true);
List<MarketCategoryQuery> categoryList = new List<MarketCategoryQuery>();
List<ProductCategoryCustom> cateList = new List<ProductCategoryCustom>();
string resultStr = "";
try
{
_marketCategoryMgr = new MarketCategoryMgr(mySqlConnectionString);
MarketCategory category = new MarketCategory();
category.IsPage = false;
categoryList = _marketCategoryMgr.GetMarketCategoryList(category);//查詢美安的所有節點
cateList = getCate(categoryList, 0);
GetFrontCateList(categoryList, ref cateList);
resultStr = JsonConvert.SerializeObject(cateList);
}
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);
}
this.Response.Clear();
this.Response.Write(resultStr);
this.Response.End();
return this.Response;
}