public void OutExcel() { List<Parametersrc> store = new List<Parametersrc>(); model = new PayEasyQuery(); psmodel = new Parametersrc(); _payeasemgr = new PayEasyMgr(mySqlConnectionString); try { try { model.rid = Convert.ToInt32(Request.Params["product_id"] ?? "0"); } catch (Exception) { model.rid = 0; } psmodel.ParameterType = "payeasy"; psmodel.ParameterCode = "1"; store = _parameter.QueryUsed(psmodel); foreach (var item in store) { model.chnanel_id = Int32.Parse(item.ParameterProperty); } psmodel.ParameterCode = "2"; store = _parameter.QueryUsed(psmodel); foreach (var item in store) { model.category_id = Int32.Parse(item.ParameterProperty); } //MemoryStream ms = ExcelHelperXhf.ExportDT(_payeasemgr.QueryExcel(model), "匯出"); //Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.csv", DateTime.Now.ToString("yyyyMMddhhmmss"))); //Response.BinaryWrite(ms.ToArray()); //ms.Close(); //ms.Dispose(); string fileName = DateTime.Now.ToString("6727_yyyyMMddHHmm") + ".xls"; MemoryStream ms = ExcelHelperXhf.ExportDT(_payeasemgr.QueryExcel(model), ""); Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(fileName)); Response.BinaryWrite(ms.ToArray()); //StringWriter sw = ExcelHelperXhf.SetCsvFromData(_payeasemgr.QueryExcel(model), fileName); //Response.Clear(); //Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(fileName)); //Response.ContentType = "application/ms-excel"; //Response.ContentEncoding = Encoding.Default; //Response.Write(sw); //Response.End(); } 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); } }
public DataTable QueryExcel(PayEasyQuery query) { StringBuilder sbSql = new StringBuilder(); string sql = @"select 6727 AS '廠商流水號', 6037 AS '網站別流水號', pim.channel_detail_id as '廠商原始商品碼', pim.channel_detail_id AS 廠商康迅商品代碼, pim.product_name as '商品名稱', '0' as '成本價', 1 as '重量(公克)', pim.product_price '現金價', concat('http://www.gigade100.com/payeasy/product.php?mid=',pim.channel_detail_id) as '商品說明', '1' as '體積 - 長(公分)','1' as '體積 - 寬(公分)','1' as '體積 - 高(公分)','0' as '付款期數','9999' as '庫存量', date_format(date(now()),'%Y-%m-%d') as '上架日期', date_format(date(date_add(now(), interval 10 year)),'%Y-%m-%d') as '下架日期', concat('http://img.gigade100.com/product/280x280/',substr(product_image,1,2),'/',substr(product_image,3,2),'/',product_image) as '商品圖檔', '' as '商品分類' from product_category pc1,product_category pc2 inner join product_category_set pcs on pcs.category_id = pc2.category_id left join product_item_map pim on pim.product_id=pcs.product_id left join product p on p.product_id = pcs.product_id where pc1.category_id=pc2.category_father_id and (pc1.category_id={0}) and pim.channel_id={1} and pim.rid > {2}"; // string sql = @"select //6727 AS '廠商流水號', //6037 AS '網站別流水號', //pim.channel_detail_id as '廠商原始商品碼', //pim.channel_detail_id AS 廠商康迅商品代碼, //pim.product_name as '商品名稱', //'0' as '成本價', //1 as '重量(公克)', //pim.product_price '現金價', //concat('http://www.gigade100.com/payeasy/product.php?mid=',pim.channel_detail_id) as '商品說明', //'1' as '體積 - 長(公分)','1' as '體積 - 寬(公分)','1' as '體積 - 高(公分)','0' as '付款期數','9999' as '庫存量',"; // sql += @"concat(" + "\"'\"" + ",date_format(date(now()),'%Y/%m/%d')) as '上架日期'," + "concat(" + "\"'\"" + ",date_format(date(date_add(now(), interval 10 year)),'%Y-%m-%d')) as '下架日期',"; ////date_format(date(now()),'%Y/%m/%d') as '上架日期', ////date_format(date(date_add(now(), interval 10 year)),'%Y-%m-%d') as '下架日期', // sql += @"concat('http://img.gigade100.com/product/',substr(product_image,1,2),'/',substr(product_image,3,4),product_image) as '商品圖檔', //'' as '商品分類' //from product_category pc1,product_category pc2 //inner join product_category_set pcs on pcs.category_id = pc2.category_id //left join product_item_map pim on pim.product_id=pcs.product_id //left join product p on p.product_id = pcs.product_id //where pc1.category_id=pc2.category_father_id and (pc1.category_id={0}) and pim.channel_id={1} and pim.rid > {2} //"; sbSql.AppendFormat(sql, query.category_id, query.chnanel_id, query.rid); return _accessMySql.getDataTable(sbSql.ToString()); }
public System.Data.DataTable QueryExcel(PayEasyQuery query) { return _payEasyDao.QueryExcel(query); }
public List<PayEasyQuery> Query(PayEasyQuery query) { //todo: List<PayEasyQuery> list = new List<PayEasyQuery>(); return list; }