//導出類別
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 + "}";
}
}