/// <summary>
/// 列表頁
/// </summary>
/// <param name="query"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public List<BrandLogoSort> GetBLSList(BrandLogoSort query, out int totalCount)
{
StringBuilder sql = new StringBuilder();
StringBuilder sqlFrom = new StringBuilder();
StringBuilder sqlWhere = new StringBuilder();
StringBuilder sqlCount = new StringBuilder();
totalCount = 0;
try
{
sqlCount.Append("select count(blo.blo_id) as 'totalCount' ");
sql.Append(" select blo.blo_id,blo.brand_id,vb.brand_name, vb.brand_logo, blo.blo_sort, pc.category_id, pc.category_name,mu.user_username,blo.blo_mdate ");
sqlFrom.Append(" from brand_logo_sort blo LEFT JOIN vendor_brand vb on blo.brand_id=vb.brand_id LEFT JOIN product_category pc on blo.category_id=pc.category_id LEFT JOIN manage_user mu on blo.blo_muser=mu.user_id ");
sqlWhere.AppendFormat(" where 1=1 ");
if (query.category_id != 0 && query.brand_id != 0)
{
sqlWhere.AppendFormat("and pc.category_id='{0}' and blo.brand_id='{1}'", query.category_id, query.brand_id);
}
else if (query.category_id != 0 && query.brand_id == 0)
{
sqlWhere.AppendFormat("and pc.category_id='{0}' ", query.category_id);
}
DataTable _dt = _access.getDataTable(sqlCount.ToString() + sqlFrom.ToString()+sqlWhere.ToString());
if (_dt != null && _dt.Rows.Count > 0)
{
totalCount = Convert.ToInt32(_dt.Rows[0][0]);
}
sqlWhere.AppendFormat(" order by blo.blo_id desc limit {0},{1}; ", query.Start, query.Limit);
return _access.getDataTableForObj<BrandLogoSort>(sql.ToString() + sqlFrom.ToString() + sqlWhere.ToString());
}
catch (Exception ex)
{
throw new Exception("BrandLogoSortDao-->GetBLSList-->" + sql.ToString() + sqlFrom.ToString() + sqlWhere.ToString() + ex.Message, ex);
}
}
/// <summary>
/// 列表頁
/// </summary>
/// <param name="query"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public List<BrandLogoSort> GetBLSList(BrandLogoSort query, out int totalCount)
{
try
{
return _brandLogoSort.GetBLSList(query, out totalCount);
}
catch (Exception ex)
{
throw new Exception("BrandLogoSortDao-->GetBLSList-->" + ex.Message, ex);
}
}
/// <summary>
/// 編輯
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public int UpdateBLS(BrandLogoSort query)
{
StringBuilder sql = new StringBuilder();
try
{
sql.AppendFormat(" update brand_logo_sort set category_id='{0}',brand_id='{1}',blo_sort='{2}', blo_muser='******',blo_mdate='{4}' where blo_id='{5}'; ", query.category_id, query.brand_id, query.blo_sort, query.blo_muser, CommonFunction.DateTimeToString(DateTime.Now), query.blo_id);
return _access.execCommand(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("BrandLogoSortDao-->UpdateBLS-->" + sql.ToString() + ex.Message, ex);
}
}
/// <summary>
/// 新增
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public int InsertBLS(BrandLogoSort query)
{
StringBuilder sql = new StringBuilder();
try
{
sql.AppendFormat("insert into brand_logo_sort(category_id,brand_id,blo_sort,blo_kuser,blo_kdate,blo_muser,blo_mdate) values('{0}','{1}','{2}','{3}','{4}','{5}','{6}');", query.category_id, query.brand_id, query.blo_sort, query.blo_kuser, CommonFunction.DateTimeToString(DateTime.Now), query.blo_muser, CommonFunction.DateTimeToString(DateTime.Now));
return _access.execCommand(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("BrandLogoSortDao-->InsertBLS-->" + sql.ToString() + ex.Message, ex);
}
}
/// <summary>
/// 保存
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public string SaveBLS(BrandLogoSort query)
{
string json = string.Empty;
try
{
if (query.blo_id == 0)//add
{
//當前品牌館下有多少筆數據了
DataTable _dataCount = _brandLogoSort.GetCountByCat(query.category_id);
if (_dataCount.Rows.Count < _brandLogoSort.MaxLimit())
{
//數據不能重複
DataTable _existData = _brandLogoSort.NoExistData(query);
if (_existData == null || _existData.Rows.Count == 0)
{
//排序不能重複
DataTable _existSort = _brandLogoSort.NoExistSort(query);
if (_existSort == null || _existSort.Rows.Count == 0)
{
if (_brandLogoSort.InsertBLS(query) > 0)
{
json = "{success:'true',re:'true'}";
}
else
{
json = "{success:'false'}";
}
}
else
{
json = "{success:'true',repeatSort:'true'}";
}
}
else
{
json = "{success:'true',repeatData:'true'}";
}
}
else
{
json = "{success:'true',maxCount:'true'}";
}
}
else//edit
{
//编辑时不檢查當前品牌館有多少條數據了
//數據是否重複
DataTable _existData = _brandLogoSort.NoExistData(query);
if (_existData == null || _existData.Rows.Count == 0)
{
//排序是否重複
DataTable _existSort = _brandLogoSort.NoExistSort(query);
if (_existSort == null || _existSort.Rows.Count == 0)
{
if (_brandLogoSort.UpdateBLS(query) > 0)
{
json = "{success:'true',re:'true'}";
}
else
{
json = "{success:'false'}";
}
}
else
{
json = "{success:'true',repeatSort:'true'}";
}
}
else
{
json = "{success:'true',repeatData:'true'}";
}
}
return json;
}
catch (Exception ex)
{
throw new Exception("BrandLogoSortDao-->SaveBLS-->" + ex.Message, ex);
}
}
public int MaxSort(BrandLogoSort query)
{
int sort = 0;
try
{
DataTable _dt = _brandLogoSort.MaxSort(query.category_id);
int n = 0;
if (int.TryParse(_dt.Rows[0][0].ToString(), out n))
{
sort = Convert.ToInt32(_dt.Rows[0][0]) + 1;
}
else
{
sort = 1;
}
return sort;
}
catch (Exception ex)
{
throw new Exception("BrandLogoSortDao-->MaxSort-->" + ex.Message, ex);
}
}
public DataTable BrandStore(BrandLogoSort query)
{
try
{
return _brandLogoSort.BrandStore(query);
}
catch (Exception ex)
{
throw new Exception("BrandLogoSortDao-->BrandStore-->" + ex.Message, ex);
}
}
/// <summary>
/// 數據不能重複
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public DataTable NoExistData(BrandLogoSort query)
{
StringBuilder sql = new StringBuilder();
try
{
if (query.old_brand_id != query.brand_id)
{
sql.AppendFormat(" select blo_id from brand_logo_sort where category_id='{0}' and brand_id='{1}';", query.category_id, query.brand_id);
return _access.getDataTable(sql.ToString());
}
else
{
return new DataTable();
}
}
catch (Exception ex)
{
throw new Exception("BrandLogoSortDao-->GetCountByCat-->" + sql.ToString(), ex);
}
}
/// <summary>
/// 品牌store
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public DataTable BrandStore(BrandLogoSort query)
{
StringBuilder sql = new StringBuilder();
try
{
sql.AppendFormat("SELECT DISTINCT p.brand_id, v.brand_name FROM product_category_brand AS p LEFT JOIN vendor_brand AS v ON p.brand_id = v.brand_id WHERE p.category_id IN (SELECT t_parametersrc.parameterCode FROM t_parametersrc WHERE t_parametersrc.parameterType = 'BrandCategoryRelation' AND t_parametersrc.topValue = '{0}');", query.category_id);
return _access.getDataTable(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("BrandLogoSortDao-->BrandStore-->" + sql.ToString(), ex);
}
}
/// <summary>
/// 刪除
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public string DeleteBLS(BrandLogoSort query)
{
StringBuilder sql = new StringBuilder();
try
{
sql.AppendFormat("delete from brand_logo_sort where blo_id='{0}';", query.blo_id);
return sql.ToString();
}
catch (Exception ex)
{
throw new Exception("BrandLogoSortDao-->DeleteBLS-->" + sql.ToString() + ex.Message, ex);
}
}
public HttpResponseBase SaveBLS()
{
string json = string.Empty;
BrandLogoSort query = new BrandLogoSort();
try
{
_BrandLSMgr = new BrandLogoSortMgr(mySqlConnectionString);
if (!string.IsNullOrEmpty(Request.Params["blo_id"]))
{
query.blo_id = Convert.ToInt32(Request.Params["blo_id"]);
}
if (!string.IsNullOrEmpty(Request.Params["category_id"]))
{
query.category_id = Convert.ToUInt32(Request.Params["category_id"]);
}
if (!string.IsNullOrEmpty(Request.Params["brand_id"]))
{
query.brand_id = Convert.ToUInt32(Request.Params["brand_id"]);
}
if (!string.IsNullOrEmpty(Request.Params["old_brand_id"]))
{
query.old_brand_id = Convert.ToInt32(Request.Params["old_brand_id"]);
}
if (!string.IsNullOrEmpty(Request.Params["blo_sort"]))
{
query.blo_sort = Convert.ToInt32(Request.Params["blo_sort"]);
}
query.blo_kuser = Convert.ToUInt32((Session["caller"] as Caller).user_id);
query.blo_muser = query.blo_kuser;
json = _BrandLSMgr.SaveBLS(query);
}
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 GetBLSList()
{
string json = string.Empty;
try
{
BrandLogoSort query = new BrandLogoSort();
List<BrandLogoSort> store = new List<BrandLogoSort>();
if (!string.IsNullOrEmpty(Request.Params["category_id"]))
{
uint n = 99999999;
if (uint.TryParse(Request.Params["category_id"].ToString().Trim(), out n))
{
query.category_id = Convert.ToUInt32(Request.Params["category_id"].ToString().Trim());
}
else
{
query.category_id = 99999999;
}
}
if (!string.IsNullOrEmpty(Request.Params["brand_id"]))
{
uint n = 99999999;
if (uint.TryParse(Request.Params["brand_id"].ToString().Trim(), out n))
{
query.brand_id = Convert.ToUInt32(Request.Params["brand_id"].ToString().Trim());
}
else
{
query.brand_id = 99999999;
}
}
query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");
query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25");
int totalCount = 0;
_BrandLSMgr = new BrandLogoSortMgr(mySqlConnectionString);
store = _BrandLSMgr.GetBLSList(query, out totalCount);
foreach (var item in store)
{
if (!string.IsNullOrEmpty(item.brand_logo))
{
string folder1 = item.brand_logo.Substring(0, 2) + "/"; //圖片名前兩碼
string folder2 = item.brand_logo.Substring(2, 2) + "/"; //圖片名第三四碼
item.brand_logo = imgServerPath + brandPath + folder1 + folder2 + item.brand_logo;
}
else
{
item.brand_logo = defaultImg;
}
}
IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
json = "{success:true,totalCount:" + totalCount + ",data:" + JsonConvert.SerializeObject(store, Formatting.Indented, timeConverter) + "}";
}
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,totalCount:0,data:[]}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
public HttpResponseBase MaxSort()
{
string json = string.Empty;
try
{
BrandLogoSort query = new BrandLogoSort();
if (!string.IsNullOrEmpty(Request.Params["category_id"]))
{
query.category_id = Convert.ToUInt32(Request.Params["category_id"]);
_BrandLSMgr = new BrandLogoSortMgr(mySqlConnectionString);
int sort = _BrandLSMgr.MaxSort(query);
json = "{success:true,data:" + sort + "}";
}
}
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 DeleteBLS()
{
string json = string.Empty;
BrandLogoSort query = new BrandLogoSort();
List<BrandLogoSort> list = new List<BrandLogoSort>();
try
{
_BrandLSMgr = new BrandLogoSortMgr(mySqlConnectionString);
if (!string.IsNullOrEmpty(Request.Form["rowID"]))
{
string rowIDs = Request.Form["rowID"];
if (rowIDs.IndexOf("∑") != -1)
{
foreach (string id in rowIDs.Split('∑'))
{
if (!string.IsNullOrEmpty(id))
{
query = new BrandLogoSort();
query.blo_id = Convert.ToInt32(id);
list.Add(query);
}
}
}
}
json = _BrandLSMgr.DeleteBLS(list);
}
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;
}