//導出類別 public DataTable GetCategoryInfo(RecommendedOutPra rop, string sheetname) { try { //獲得類別 DataTable dtCategory = _iRecommendedExcleImplDao.GetCategoryInfo(rop); return dtCategory; } catch (Exception ex) { throw new Exception("RecommendedExcleMgr-->GetCategoryInfo" + ex.Message, ex); } }
//導出訂單內容 public DataTable GetOrderDetailInfo(RecommendedOutPra rop, string sheetname) { try { //獲得訂單內容 DataTable dtOrderDetail = _iRecommendedExcleImplDao.GetOrderDetailInfo(rop); return dtOrderDetail; } catch (Exception ex) { throw new Exception("RecommendedExcleMgr-->GetOrderDetailInfo" + ex.Message, ex); } }
//獲得會員信息 public DataTable GetVipUserInfo(RecommendedOutPra rop) { StringBuilder sql = new StringBuilder(); StringBuilder sqlwhere = new StringBuilder(); try { sql.AppendFormat(@"SELECT distinct(us.user_id) as '會員編號',CASE us.user_gender WHEN 0 THEN '女' ELSE '男' END as '性別', case vu.v_id WHEN null then '否' ELSE '是' end as 'VIP', (Year(CURDATE())-us.user_birthday_year)as '年齡',FROM_UNIXTIME(us.user_reg_date)as '註冊時間',uos.order_product_subtotals as '購買金額', uos.buy_counts as '購買次數',uos.order_product_subtotals/uos.buy_counts as '客單價' ,om.deduct_bonuss as '購物金使用',uos.normal_product_subtotals as '常溫商品總額',uos.low_product_subtotals as '低溫商品總額' FROM users us LEFT JOIN (SELECT user_id,sum(buy_count)as buy_counts,SUM(order_product_subtotal) as order_product_subtotals ,sum(normal_product_subtotal)as normal_product_subtotals,sum(low_product_subtotal)as low_product_subtotals FROM user_orders_subtotal GROUP BY user_id) as uos on us.user_id=uos.user_id left join vip_user vu on us.user_id=vu.user_id LEFT JOIN (SELECT user_id,sum(deduct_bonus) as deduct_bonuss FROM order_master GROUP BY user_id) as om on us.user_id = om.user_id "); sqlwhere.Append(" where 1=1 "); //如果沒給類型就是導出全部時間的 if (!string.IsNullOrEmpty(rop.outType)) { switch (rop.outType) { case "年": sqlwhere.Append(" and year(FROM_UNIXTIME(us.user_reg_date))=" + rop.outTime); break; case "月": sqlwhere.Append(" and year(FROM_UNIXTIME(us.user_reg_date))=" + rop.nowYear); sqlwhere.Append(" and month(FROM_UNIXTIME(us.user_reg_date))=" + rop.outTime); break; case "日": sqlwhere.Append(" and year(FROM_UNIXTIME(us.user_reg_date))=" + rop.nowYear); sqlwhere.Append(" and month(FROM_UNIXTIME(us.user_reg_date))=" + rop.nowMonth); sqlwhere.Append(" and day(FROM_UNIXTIME(us.user_reg_date))=" + rop.outTime); break; } } sql.Append(sqlwhere); DataTable dt = _access.getDataTable(sql.ToString()); return dt; } catch (Exception ex) { throw new Exception("RecommendedExcleDao-->GetVipUserInfo" + ex.Message + sql.ToString(), ex); } }
//獲得商品信息 public DataTable GetProductInfo(RecommendedOutPra rop) { StringBuilder sql = new StringBuilder(); StringBuilder sqlwhere = new StringBuilder(); try { sql.AppendFormat(@"select pro.product_id as '商品編號',pro.brand_id as '品牌編號' , pro.product_type as '類別編號',pro.product_name as '名稱',pm.price as '售價',FROM_UNIXTIME(pro.product_start) as '上架時間', FROM_UNIXTIME(pro.product_end) as '下架時間', pro.product_image as '商品圖片',pro.page_content_1 as '簡介', pro.page_content_2 as '商品規格',case ISNULL(rpa.months) WHEN TRUE then '否' else '是' end as '是否推薦商品',rpa.expend_day as '預計消耗時間',rpa.months AS '推薦月份設定',tp.parameterName as '商品狀態' from product as pro left join recommended_product_attribute as rpa on pro.product_id=rpa.product_id LEFT JOIN (SELECT parameterCode,parameterName FROM t_parametersrc WHERE parameterType='product_status') as tp on tp.parameterCode=pro.product_status LEFT JOIN price_master pm on pm.product_id=pro.product_id "); sqlwhere.Append(" where 1=1 "); sqlwhere.Append(" and pro.product_id > 10000 "); //如果沒給類型就是導出全部時間的 if (!string.IsNullOrEmpty(rop.outType)) { switch (rop.outType) { case "年": sqlwhere.Append(" and year(FROM_UNIXTIME(pro.product_createdate))=" + rop.outTime); break; case "月": sqlwhere.Append(" and year(FROM_UNIXTIME(pro.product_createdate))=" + rop.nowYear); sqlwhere.Append(" and month(FROM_UNIXTIME(pro.product_createdate))=" + rop.outTime); break; case "日": sqlwhere.Append(" and year(FROM_UNIXTIME(pro.product_createdate))=" + rop.nowYear); sqlwhere.Append(" and month(FROM_UNIXTIME(pro.product_createdate))=" + rop.nowMonth); sqlwhere.Append(" and day(FROM_UNIXTIME(pro.product_createdate))=" + rop.outTime); break; } } sql.Append(sqlwhere); DataTable dt = _access.getDataTable(sql.ToString()); return dt; } catch (Exception ex) { throw new Exception("RecommendedExcleDao-->GetProductInfo" + ex.Message + sql.ToString(), ex); } }
//獲得品牌信息 public DataTable GetBrandInfo(RecommendedOutPra rop) { StringBuilder sql = new StringBuilder(); StringBuilder sqlwhere = new StringBuilder(); try { sql.AppendFormat(@"select p.brand_id as '品牌編號',vb.brand_name as '名稱',pcb.category_id as '品牌類別編號',p.prod_classify as '館別編號' from product as p left join product_category_brand as pcb on p.brand_id=pcb.brand_id left join vendor_brand as vb on p.brand_id=vb.brand_id "); sqlwhere.Append(" where 1=1 "); sqlwhere.Append(" and p.product_id>10000 group by p.brand_id "); //如果沒給類型就是導出全部時間的 if (!string.IsNullOrEmpty(rop.outType)) { switch (rop.outType) { case "年": sqlwhere.Append(" and year(FROM_UNIXTIME(p.product_createdate))=" + rop.outTime); break; case "月": sqlwhere.Append(" and year(FROM_UNIXTIME(p.product_createdate))=" + rop.nowYear); sqlwhere.Append(" and month(FROM_UNIXTIME(p.product_createdate))=" + rop.outTime); break; case "日": sqlwhere.Append(" and year(FROM_UNIXTIME(p.product_createdate))=" + rop.nowYear); sqlwhere.Append(" and month(FROM_UNIXTIME(p.product_createdate))=" + rop.nowMonth); sqlwhere.Append(" and day(FROM_UNIXTIME(p.product_createdate))=" + rop.outTime); break; } } sql.Append(sqlwhere); DataTable dt = _access.getDataTable(sql.ToString()); return dt; } catch (Exception ex) { throw new Exception("RecommendedExcleDao-->GetBrandInfo" + ex.Message + sql.ToString(), ex); } }
//獲得類別信息 public DataTable GetCategoryInfo(RecommendedOutPra rop) { StringBuilder sql = new StringBuilder(); StringBuilder sqlwhere = new StringBuilder(); try { sql.AppendFormat(@"select category_id as '類別編號',category_name as '類別名稱',category_father_id as '父類別編號' from product_category "); sqlwhere.Append(" where 1=1 "); //如果沒給類型就是導出全部時間的 if (!string.IsNullOrEmpty(rop.outType)) { switch (rop.outType) { case "年": sqlwhere.Append(" and year(FROM_UNIXTIME(category_createdate))=" + rop.outTime); break; case "月": sqlwhere.Append(" and year(FROM_UNIXTIME(category_createdate))=" + rop.nowYear); sqlwhere.Append(" and month(FROM_UNIXTIME(category_createdate))=" + rop.outTime); break; case "日": sqlwhere.Append(" and year(FROM_UNIXTIME(category_createdate))=" + rop.nowYear); sqlwhere.Append(" and month(FROM_UNIXTIME(category_createdate))=" + rop.nowMonth); sqlwhere.Append(" and day(FROM_UNIXTIME(category_createdate))=" + rop.outTime); break; } } sql.Append(sqlwhere); DataTable dt = _access.getDataTable(sql.ToString()); return dt; } catch (Exception ex) { throw new Exception("RecommendedExcleDao-->GetCategoryInfo" + ex.Message + sql.ToString(), ex); } }
//獲得訂單內容信息 public DataTable GetOrderDetailInfo(RecommendedOutPra rop) { StringBuilder sql = new StringBuilder(); StringBuilder sqlwhere = new StringBuilder(); try { sql.AppendFormat(@"SELECT om.order_id as '訂單編號',pi.product_id as '商品編號',od.buy_num as '數量', tp.parameterName as '溫層' FROM order_master om INNER JOIN order_slave os on om.order_id =os.order_id INNER JOIN order_detail od on os.slave_id=od.slave_id INNER JOIN product_item pi on od.item_id=pi.item_id INNER JOIN product pt on pi.product_id =pt.product_id INNER JOIN (SELECT parameterCode,parameterName FROM t_parametersrc WHERE parameterType='product_freight') AS tp on pt.product_freight_set =tp.parameterCode "); sqlwhere.Append(" where 1=1 "); //如果沒給類型就是導出全部時間的 if (!string.IsNullOrEmpty(rop.outType)) { switch (rop.outType) { case "年": sqlwhere.Append(" and year(FROM_UNIXTIME(om.order_createdate))=" + rop.outTime); break; case "月": sqlwhere.Append(" and year(FROM_UNIXTIME(om.order_createdate))=" + rop.nowYear); sqlwhere.Append(" and month(FROM_UNIXTIME(om.order_createdate))=" + rop.outTime); break; case "日": sqlwhere.Append(" and year(FROM_UNIXTIME(om.order_createdate))=" + rop.nowYear); sqlwhere.Append(" and month(FROM_UNIXTIME(om.order_createdate))=" + rop.nowMonth); sqlwhere.Append(" and day(FROM_UNIXTIME(om.order_createdate))=" + rop.outTime); break; } } sql.Append(sqlwhere); DataTable dt = _access.getDataTable(sql.ToString()); return dt; } catch (Exception ex) { throw new Exception("RecommendedExcleDao-->GetOrderDetailInfo" + ex.Message + sql.ToString(), ex); } }
//獲得訂單信息 public DataTable GetOrderInfo(RecommendedOutPra rop) { StringBuilder sql = new StringBuilder(); StringBuilder sqlwhere = new StringBuilder(); try { sql.AppendFormat(@"SELECT om.order_id as '訂單編號',us.user_id as '會員編號',order_amount as '訂單金額', FROM_UNIXTIME(order_createdate) as '訂單日期' FROM order_master om LEFT JOIN users us on om.user_id=us.user_id "); sqlwhere.Append(" where 1=1 "); //如果沒給類型就是導出全部時間的 if (!string.IsNullOrEmpty(rop.outType)) { switch (rop.outType) { case "年": sqlwhere.Append(" and year(FROM_UNIXTIME(om.order_createdate))=" + rop.outTime); break; case "月": sqlwhere.Append(" and year(FROM_UNIXTIME(om.order_createdate))=" + rop.nowYear); sqlwhere.Append(" and month(FROM_UNIXTIME(om.order_createdate))=" + rop.outTime); break; case "日": sqlwhere.Append(" and year(FROM_UNIXTIME(om.order_createdate))=" + rop.nowYear); sqlwhere.Append(" and month(FROM_UNIXTIME(om.order_createdate))=" + rop.nowMonth); sqlwhere.Append(" and day(FROM_UNIXTIME(om.order_createdate))=" + rop.outTime); break; } } sql.Append(sqlwhere); DataTable dt = _access.getDataTable(sql.ToString()); return dt; } catch (Exception ex) { throw new Exception("RecommendedExcleDao-->GetOrderInfo" + ex.Message + sql.ToString(), ex); } }
//導出品牌 public DataTable GetBrandInfo(RecommendedOutPra rop, string sheetname) { //獲得品牌 try { DataTable dtBrand = _iRecommendedExcleImplDao.GetBrandInfo(rop); return dtBrand; } catch (Exception ex) { throw new Exception("RecommendedExcleMgr-->GetBrandInfo" + ex.Message, ex); } }
// 吉甲地推薦系統匯出 guodong1130w 2015/10/9 public string OutExcleForRecommended() { try { //獲取傳參并封裝到對象 RecommendedOutPra rop = new RecommendedOutPra(); string outType = string.Empty; if (!string.IsNullOrEmpty(Request.Params["outType"])) { outType = Request.Params["outType"].ToString(); } string outTime = string.Empty; if (!string.IsNullOrEmpty(Request.Params["outTime"])) { outTime = Request.Params["outTime"].ToString(); } if (!string.IsNullOrEmpty(outType)) { rop.outType = outType; DateTime dtnow = DateTime.Now; rop.nowMonth = dtnow.Month.ToString(); rop.nowYear = dtnow.Year.ToString(); } if (!string.IsNullOrEmpty(outTime)) { rop.outTime = outTime; } //導出方法 _recommendedExcleMgr = new RecommendedExcleMgr(connectionString); //獲取XML裏面的Sheetname string strXml = "../XML/ParameterSrc.xml"; _iParametersrcImplMgr = new ParameterMgr(Server.MapPath(strXml), ParaSourceType.XML); List<Parametersrc> liparsrc = _iParametersrcImplMgr.QueryUsed(new Parametersrc { ParameterType = "RecommendedExcleSheetName" }).ToList(); //導出文件名稱 DateTime nowtime = DateTime.Now; //導出EXCLE 分別導出6種 DataTable msVipUser = _recommendedExcleMgr.GetVipUserInfo(rop, liparsrc[0].parameterName); OutExcleForRecommendedByMs(msVipUser, liparsrc[0].parameterName, nowtime); DataTable msProduct = _recommendedExcleMgr.GetProductInfo(rop, liparsrc[1].parameterName); OutExcleForRecommendedByMs(msProduct, liparsrc[1].parameterName, nowtime); DataTable msOrder = _recommendedExcleMgr.GetOrderInfo(rop, liparsrc[2].parameterName); OutExcleForRecommendedByMs(msOrder, liparsrc[2].parameterName, nowtime); DataTable msOrderDetail = _recommendedExcleMgr.GetOrderDetailInfo(rop, liparsrc[3].parameterName); OutExcleForRecommendedByMs(msOrderDetail, liparsrc[3].parameterName, nowtime); DataTable msCategory = _recommendedExcleMgr.GetCategoryInfo(rop, liparsrc[4].parameterName); OutExcleForRecommendedByMs(msCategory, liparsrc[4].parameterName, nowtime); DataTable msBrand = _recommendedExcleMgr.GetBrandInfo(rop, liparsrc[5].parameterName); OutExcleForRecommendedByMs(msBrand, liparsrc[5].parameterName, nowtime); return "{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); return "{success:false,data:'',msg:" + ex.Message + "}"; } }