/// <summary>
/// 查找列表數據
/// </summary>
/// <param name="appgory"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public List<Appcategory> GetAppcategoryList(Appcategory appgory, out int totalCount)
{
try
{
return _iappcategoryImplDao.GetAppcategoryList(appgory, out totalCount);
}
catch (Exception ex)
{
throw new Exception("AppcategoryMgr-->GetAppcategoryList-->" + ex.Message, ex);
}
}
/// <summary>
/// 查找列表
/// </summary>
/// <param name="appgory"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public List<Appcategory> GetAppcategoryList(Appcategory appgory, out int totalCount)
{
StringBuilder sql = new StringBuilder();
StringBuilder sqlcount = new StringBuilder();
StringBuilder sqlwhere = new StringBuilder();
sql.AppendFormat("SELECT category_id,category,brand_id,brand_name,category1,category2,category3,product_id,Property FROM appcategory where 1=1 ");
sqlcount.AppendFormat("SELECT category_id FROM appcategory where 1=1 ");
if (!string.IsNullOrEmpty(appgory.category))
{
sqlwhere.AppendFormat(" and appcategory.category='{0}'", appgory.category);
}
if (!string.IsNullOrEmpty(appgory.category1))
{
sqlwhere.AppendFormat(" and appcategory.category1='{0}'", appgory.category1);
}
if (!string.IsNullOrEmpty(appgory.category2))
{
sqlwhere.AppendFormat(" and appcategory.category2='{0}'", appgory.category2);
}
if (!string.IsNullOrEmpty(appgory.category3))
{
sqlwhere.AppendFormat(" and appcategory.category3='{0}'", appgory.category3);
}
if (appgory.product_id != 0)
{
sqlwhere.AppendFormat(" and appcategory.product_id='{0}'", appgory.product_id);
}
totalCount = 0;
try
{
sqlcount.AppendFormat(sqlwhere.ToString());
sql.AppendFormat(sqlwhere.ToString());
if (appgory.IsPage)
{
System.Data.DataTable _dt = _access.getDataTable(sqlcount.ToString());
if (_dt != null && _dt.Rows.Count > 0)
{
totalCount = _dt.Rows.Count;
}
sql.AppendFormat(" limit {0},{1}", appgory.Start, appgory.Limit);
}
return _access.getDataTableForObj<Appcategory>(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("AppcategoryDao.GetAppcategoryList-->" + ex.Message + sql.ToString(), ex);
}
}
/// <summary>
/// 刪除數據
/// </summary>
/// <param name="appgory"></param>
/// <returns></returns>
public int AppcategoryDelete(Appcategory appgory)
{
StringBuilder sql = new StringBuilder();
try
{
sql.AppendFormat("delete from appcategory where category_id ='{0}'", appgory.category_id);
return _access.execCommand(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("AppcategoryDao-->AppcategoryDelete-->" + ex.Message + sql.ToString(), ex);
}
}
/// <summary>
/// 保存數據
/// </summary>
/// <param name="appgory"></param>
/// <returns></returns>
public int AppcategorySave(Appcategory appgory)
{
StringBuilder sql = new StringBuilder();
try
{
sql.AppendFormat("insert into appcategory (category,brand_id,brand_name,category1,category2,category3,product_id,property)values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}')",appgory.category,appgory.brand_id,appgory.brand_name,appgory.category1,appgory.category2,appgory.category3,appgory.product_id,appgory.property);
return _access.execCommand(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("AppcategoryDao-->AppcategorySave-->" + ex.Message + sql.ToString(), ex);
}
}
/// <summary>
/// 導入Excel數據
/// </summary>
/// <returns></returns>
public HttpResponseBase AppCategoryUpExcel()
{
string json = string.Empty;//json字符串
int successcount = 0;
int failcount = 0;
int typeerror = 0;//要匯入的數據類型錯誤
try
{
if (Request.Files["ImportFileMsg"] != null && Request.Files["ImportFileMsg"].ContentLength > 0)//判斷文件是否為空
{
HttpPostedFileBase excelFile = Request.Files["ImportFileMsg"];//獲取文件流
FileManagement fileManagement = new FileManagement();//實例化 FileManagement
StringBuilder str = new StringBuilder();
string fileLastName = excelFile.FileName;
string newExcelName = Server.MapPath(excelPath) + "App功能管理" + fileManagement.NewFileName(excelFile.FileName);//處理文件名，獲取新的文件名
excelFile.SaveAs(newExcelName);//上傳文件
DataTable dt = new DataTable();
NPOI4ExcelHelper helper = new NPOI4ExcelHelper(newExcelName);
dt = helper.SheetData();//根據這個表,插入到數據庫中
DataRow[] dr = dt.Select(); //定义一个DataRow数组,读取ds里面所有行
_iappcategoryMgr = new AppcategoryMgr(mySqlConnectionString);
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
Appcategory appCategory = new Appcategory();
try
{
appCategory.brand_id = Convert.ToInt32(dt.Rows[i]["brand_id"]);
appCategory.brand_name = dt.Rows[i]["brand_name"].ToString();
appCategory.category = dt.Rows[i]["category"].ToString();
appCategory.category1 = dt.Rows[i]["category1"].ToString();
appCategory.category2 = dt.Rows[i]["category2"].ToString();
appCategory.category3 = dt.Rows[i]["category3"].ToString();
appCategory.product_id = Convert.ToInt32(dt.Rows[i]["product_id"]);
appCategory.property = dt.Rows[i]["property"].ToString();
}
catch (Exception ex)
{
str.Append(i + 2 + " ");
failcount++;
continue;
}
int results= _iappcategoryMgr.AppcategorySave(appCategory);
if (results > 0)
{
successcount++;
}
else
{
str.Append(i + 2 + " ");
failcount++;
}
}
if (str.Length > 1)
{
str.Length -= 1;
json = "{success:true,total:" + successcount + ",fail:" + failcount + ",errorRow:\"" + str.ToString() + "\"}";
}
else
{
json = "{success:true,total:" + successcount + ",fail:" + failcount + "}";
}
}
else
{
json = "{success:false,msg:\"" + "此表內沒有數據或數據有誤,請檢查后再次匯入!" + "\"}";
}
}
else
{
json = "{success:false,msg:\"" + "請選擇要匯入的Excel表" + "\"}";
}
}
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:\"" + ex.ToString() + "\"}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
/// <summary>
/// 刪除數據
/// </summary>
/// <returns></returns>
public HttpResponseBase AppCategoryDelete()
{
string jsonStr = String.Empty;
_iappcategoryMgr = new AppcategoryMgr(mySqlConnectionString);
Appcategory query = new Appcategory();
if (!String.IsNullOrEmpty(Request.Params["rowid"]))
{
try
{
foreach (string rid in Request.Params["rowid"].ToString().Split('|'))
{
if (!string.IsNullOrEmpty(rid))
{
query.category_id = Convert.ToInt32(rid);
_iappcategoryMgr.AppcategoryDelete(query);
}
}
jsonStr = "{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);
jsonStr = "{success:false}";
}
}
this.Response.Clear();
this.Response.Write(jsonStr);
this.Response.End();
return this.Response;
}
/// <summary>
/// 獲取表單的數據
/// </summary>
/// <returns></returns>
public HttpResponseBase GetAppCategoryList()
{
List<Appcategory> stores = new List<Appcategory>();
string json = string.Empty;
try
{
Appcategory query = new Appcategory();
query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");//用於分頁的變量
query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25");//用於分頁的變量
if (!string.IsNullOrEmpty(Request.Form["category"]))//判斷館別是否為空
{
query.category = Request.Form["category"];
}
if (!string.IsNullOrEmpty(Request.Form["category1"]))//判斷分類一是否為空
{
query.category1 = Request.Form["category1"];
}
if (!string.IsNullOrEmpty(Request.Form["category2"]))
{
query.category2 = Request.Form["category2"];
}
if (!string.IsNullOrEmpty(Request.Form["category3"]))
{
query.category3 = Request.Form["category3"];
}
if (!string.IsNullOrEmpty(Request.Form["product_id"]))//判斷商品ID是否為空
{
query.product_id = int.Parse(Request.Form["product_id"]);
}
_iappcategoryMgr = new AppcategoryMgr(mySqlConnectionString);
int totalCount = 0;
stores = _iappcategoryMgr.GetAppcategoryList(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;
}
/// <summary>
/// 查詢下拉框的參數數據
/// </summary>
/// <returns></returns>
public string QueryPara()
{
Appcategory appQuery = new Appcategory();
string json = string.Empty;
try
{
if (!string.IsNullOrEmpty(Request.QueryString["paraType"]))
{
_iappcategoryMgr = new AppcategoryMgr(mySqlConnectionString);
string para = Request.Params["paraType"].ToString();
appQuery.category = Request.Params["selectCondition"];
appQuery.category1 = Request.Params["select1Condition"];
appQuery.category2 = Request.Params["select2Condition"];
json = _iappcategoryMgr.GetParaList(para, appQuery);
}
}
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:[]}";
}
return json;
}
/// <summary>
/// 獲取參數
/// </summary>
/// <param name="para">表中對應的其欄位</param>
/// <param name="appgory">查詢的條件</param>
/// <returns></returns>
public string GetParaList(string para, Appcategory appgory)
{
try
{
List<Appcategory> appgoryPara = new List<Appcategory>();
StringBuilder stb = new StringBuilder();//用於存儲json來返回前臺的數據
StringBuilder sqlwhere = new StringBuilder();//用於sql語句的查詢條件，根據查詢條件是否有值，如果有則添加到查詢條件中
stb.Append("{");
stb.Append(string.Format("success:true,items:["));
//當前的邏輯
//初次加載的時候進行四次查詢，分別獲取對應的四個欄位的參數
//在用戶進行選擇一個下拉框欄位的時候，前臺會傳過來此欄位及父級所有的下拉框的值
//附：分類一二三帶有一定的關係，但是并不是上下級關係。
switch (para)
{
case "category"://當需要查詢的欄位是category的時候
appgoryPara = _iappcategoryImplDao.GetParaList("SELECT category FROM appcategory GROUP BY category");
foreach (Appcategory act in appgoryPara)
{
stb.Append("{");
stb.Append(string.Format("\"parameterName\":\"{0}\"", act.category));
stb.Append("}");
}
break;
case "category1":
if (string.IsNullOrEmpty(appgory.category))
{
appgoryPara = _iappcategoryImplDao.GetParaList("SELECT category1 FROM appcategory GROUP BY category1");
}
else
{
appgoryPara = _iappcategoryImplDao.GetParaList(string.Format("SELECT category1 FROM appcategory WHERE category = '{0}' GROUP BY category1", appgory.category));
}
foreach (Appcategory act in appgoryPara)
{
stb.Append("{");
stb.Append(string.Format("\"parameterName\":\"{0}\"", act.category1));
stb.Append("}");
}
break;
case "category2":
if (!string.IsNullOrEmpty(appgory.category))
{
sqlwhere.AppendFormat(" AND category = '" + appgory.category + "'");
}
if (!string.IsNullOrEmpty(appgory.category1))
{
sqlwhere.AppendFormat(" AND category1 = '" + appgory.category1 + "'");
}
appgoryPara = _iappcategoryImplDao.GetParaList("SELECT category2 FROM appcategory WHERE 1=1" + sqlwhere.ToString() + " GROUP BY category2");
foreach (Appcategory act in appgoryPara)
{
stb.Append("{");
stb.Append(string.Format("\"parameterName\":\"{0}\"", act.category2));
stb.Append("}");
}
break;
case "category3":
if (!string.IsNullOrEmpty(appgory.category))
{
sqlwhere.AppendFormat(" AND category = '" + appgory.category + "'");
}
if (!string.IsNullOrEmpty(appgory.category1))
{
sqlwhere.AppendFormat(" AND category1 = '" + appgory.category1 + "'");
}
if (!string.IsNullOrEmpty(appgory.category2))
{
sqlwhere.AppendFormat(" AND category2 = '" + appgory.category2 + "'");
}
appgoryPara = _iappcategoryImplDao.GetParaList("SELECT category3 FROM appcategory WHERE 1=1" + sqlwhere.ToString() + " GROUP BY category3");
foreach (Appcategory act in appgoryPara)
{
stb.Append("{");
stb.Append(string.Format("\"parameterName\":\"{0}\"", act.category3));
stb.Append("}");
}
break;
}
stb.Append("]}");
return stb.ToString().Replace("}{", "},{");
}
catch (Exception ex)
{
throw new Exception("AppcategoryMgr-->GetParaList-->" + ex.Message, ex);
}
}
/// <summary>
/// 保存數據
/// </summary>
/// <param name="appgory"></param>
/// <returns></returns>
public int AppcategorySave(Appcategory appgory)
{
try
{
return _iappcategoryImplDao.AppcategorySave(appgory);
}
catch (Exception ex)
{
throw new Exception("AppcategoryMgr-->AppcategorySave-->" + ex.Message, ex);
}
}