/// <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); } }