public List<ProductOrderTemp> QueryParent(ProductOrderTemp pot)
{
try
{
return _potDao.QueryParent(pot);
}
catch (Exception ex)
{
throw new Exception("ProductOrderTempMgr-->QueryParent" + ex, ex);
}
}
public ActionResult OutToExcel()
{
string xmlPath = "../XML/SaleSum.xml";
IProductOrderTempImplMgr _potMgr = new ProductOrderTempMgr(connectionString);
ProductOrderTemp pot =new ProductOrderTemp();
pot.brand_id="29, 98,41, 6, 53, 90";
pot.create_time = Convert.ToDateTime("2014/10/1 00:00:01");
MemoryStream ms = _potMgr.OutToExcel(Server.MapPath(xmlPath), pot);//根據查詢條件和xml路徑得到excel文件流
if(ms==null)
{
return new EmptyResult();
}
return File(ms.ToArray(), "application/-excel", DateTime.Now.ToString("yyyyMMddhhmmss") + ".xls");
}
public List<ProductOrderTemp> QueryParent(ProductOrderTemp pot)
{
StringBuilder sb = new StringBuilder();
try
{
//LEFT JOIN (SELECT parameterCode,parameterName FROM t_parametersrc WHERE parameterType = 'Combo_Type') AS c ON c.parameterCode = p.combination
sb.AppendFormat(@"SELECT vb.brand_name,product_name,p.product_id,pi.item_id,p.combination,product_name AS combination_name,o.buy_num AS c_num,
FROM_UNIXTIME(p.product_start) AS product_start,FROM_UNIXTIME(p.product_end) AS product_end,
CASE WHEN ignore_stock = 0 then '否' ELSE '是' END AS ignore_stock,
CASE WHEN shortage = 0 then '否' ELSE '是' END AS shortage FROM product p
INNER JOIN vendor_brand vb ON vb.brand_id = p.brand_id
LEFT JOIN product_item pi ON pi.product_id = p.product_id
LEFT JOIN (
SELECT pi.item_id,CAST(SUM(od.buy_num) AS SIGNED) AS buy_num FROM product_item pi
INNER JOIN order_detail od ON pi.item_id = od.item_id
INNER JOIN order_slave os ON os.slave_id = od.slave_id
INNER JOIN order_master om ON om.order_id = os.order_id
WHERE od.detail_status= 4 AND order_createdate > UNIX_TIMESTAMP('2014-10-01 00:10:01') AND od.item_mode = 1
GROUP BY pi.item_id
) o ON o.item_id = pi.item_id
WHERE p.product_status = 5 AND p.product_id >10000 AND p.brand_id IN({1}) AND p.combination <> 1", pot.create_time.ToString("yyyy-MM-dd HH:MM:ss"), pot.brand_id);
//edit by zhuoqin0830w 2015/05/18
IParametersrcImplDao _parameterDao = new ParametersrcDao(connStr);
List<Parametersrc> parameterList = _parameterDao.QueryParametersrcByTypes("Combo_Type");
List<ProductOrderTemp> list = _dbAccess.getDataTableForObj<ProductOrderTemp>(sb.ToString());
foreach (ProductOrderTemp q in list)
{
var alist = parameterList.Find(m => m.ParameterType == "Combo_Type" && m.ParameterCode == q.combination.ToString());
if (alist != null)
{
q.combination_name = alist.parameterName;
}
else { q.combination_name = ""; }
}
return list;
//return _dbAccess.getDataTableForObj<ProductOrderTemp>(sb.ToString());
}
catch (Exception ex)
{
throw new Exception("ProductOrderTempDao-->QueryParent" + ex.Message, ex);
}
}
public MemoryStream OutToExcel(string fileName,ProductOrderTemp pot)
{
//XDocument xml = XDocument.Load(fileName);//加载xml
//Dictionary<string, string> columns = xml.Elements().Elements().ToDictionary(p => p.Attribute("key").Value, p => p.Value);//將xml轉換成Dictionary
List<ProductOrderTemp> productOrderTempItems = new List<ProductOrderTemp>();//組合商品
List<ProductOrderTemp> productSingleTempItems =new List<ProductOrderTemp>();//單一商品
List<ProductOrderTemp> childOrderTempItems = new List<ProductOrderTemp>();//組合商品中子商品
List<ProductOrderTemp> mainOrderTempItems = new List<ProductOrderTemp>();//組合商品與子商品的集合
DateTime dt = Convert.ToDateTime("2014/10/1 00:00:01");
string parentIds = "";
productSingleTempItems = QuerySingle(pot);
productOrderTempItems = QueryParent(pot); ///查詢父商品
if (productOrderTempItems != null && productOrderTempItems.Count > 0)
{
foreach (ProductOrderTemp p in productOrderTempItems)
{
parentIds += p.product_id + ",";
}
parentIds = parentIds.Remove(parentIds.ToString().Length - 1, 1);//拼接父商品product_id
childOrderTempItems = QueryChild(dt, parentIds); //查詢所有的子商品
foreach (ProductOrderTemp p in productOrderTempItems)
{
mainOrderTempItems.Add(p);
List<ProductOrderTemp> list = childOrderTempItems.FindAll(m => m.parent_id == p.product_id);
mainOrderTempItems.AddRange(list);
}//查找父商品的子商品
var dic = new Dictionary<string,List<ProductOrderTemp>>();
dic.Add("單一商品",productSingleTempItems);
dic.Add("組合商品",mainOrderTempItems);
return ExcelHelperXhf.ExportExcel<ProductOrderTemp>(dic, fileName, null);
//return ExportExcelByOrder(productSingleTempItems,mainOrderTempItems, columns);
}
else
{
return null;
}
throw new Exception("unaccepted exportFlag!!!");
}