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!!!"); }