示例#1
0
        public List<MarketOrderQuery> GetMarketOrderExcel(MarketOrderQuery q)
        {
            StringBuilder sb = new StringBuilder();
            try
            {
                sb.Append(@"SELECT om.order_createdate,om.order_id,delivery_name,oms.rid,od.product_name,od.buy_num, od.parent_num,od.single_money AS 'price','' as 'Sale Amount',od.item_id,od.deduct_happygo_money,od.deduct_welfare,od.deduct_bonus,od.item_mode
from order_master_shop_com oms LEFT JOIN order_master om ON oms.order_id=om.order_id
LEFT JOIN order_slave os ON om.order_id = os.order_id
LEFT JOIN order_detail od ON os.slave_id = od.slave_id
WHERE  item_mode in (0,1) ");
                DateTime smaill = DateTime.Parse("1990-01-01");
                if (!string.IsNullOrEmpty(q.starttime.ToString()) && q.starttime > smaill)
                {
                    sb.AppendFormat(" and om.order_createdate > '{0}' ",CommonFunction.GetPHPTime(q.starttime.ToString()));
                }
                if (!string.IsNullOrEmpty(q.endtime.ToString()) && q.endtime > smaill && q.starttime < q.endtime)
                {
                    sb.AppendFormat(" and om.order_createdate < '{0}' ", CommonFunction.GetPHPTime(q.endtime.ToString()));                
                }
                return _access.getDataTableForObj<MarketOrderQuery>(sb.ToString());
            }
            catch (Exception ex)
            {
                throw new Exception(" OrderMasterShopCom-->GetMarketOrderExcel-->" + ex.Message + "sql:" + sb.ToString(), ex);

            }
        }
        public List<MarketOrderQuery> GetMarketOrderExcel(MarketOrderQuery q)
        {
            try
            {
                return _MarketOrderDao.GetMarketOrderExcel(q);
            }
            catch (Exception ex)
            {
                throw new Exception("OrderMasterShopComMgr-->GetMarketOrderExcel-->" + ex.Message, ex);
            }

        }
 public HttpResponseBase ExcelMarketOrder()
 {
     StringBuilder sb = new StringBuilder();
     DataTable dt = new DataTable();
     MarketOrderQuery q = new MarketOrderQuery();
     List<MarketOrderQuery> Mp = new List<MarketOrderQuery>();
     om = new OrderMasterShopComMgr(mySqlConnectionString);
     uint sum = 0;
     double sum2 = 0;
     double sum3 = 0;
     int sum4 = 0;
     string fileName = "";
     uint bonus = 0;
     ulong price = 0;
     try
     {
         if (!string.IsNullOrEmpty(Request.Params["starttime"]))
         {//料位開始
             q.starttime = DateTime.Parse(Request.Params["starttime"]);
         }
         if (!string.IsNullOrEmpty(Request.Params["endtime"]))
         {
             q.endtime = DateTime.Parse(Request.Params["endtime"]);
         }
         Mp = om.GetMarketOrderExcel(q);
         if (Mp.Count > 0)
         {
             #region 表頭
             dt.Columns.Add(new DataColumn("訂購日期"));
             dt.Columns.Add(new DataColumn("訂單編號"));
             dt.Columns.Add(new DataColumn("購買人"));
             dt.Columns.Add(new DataColumn("Market Taiwan RID number"));
             dt.Columns.Add(new DataColumn("商品編號"));
             dt.Columns.Add(new DataColumn("購買商品"));
             dt.Columns.Add(new DataColumn("數量"));
             dt.Columns.Add(new DataColumn("網路銷售價"));
             dt.Columns.Add(new DataColumn("折扣金額"));
             dt.Columns.Add(new DataColumn("總計"));
             #endregion
             #region 給字段賦值
             foreach (var item in Mp)
             {
                 uint money = 0;
                 DataRow dr = dt.NewRow();
                 dr["訂購日期"] = CommonFunction.GetNetTime(item.Order_Createdate);
                 dr["訂單編號"] = item.Order_Id;
                 dr["購買人"] = item.Delivery_Name;
                 dr["Market Taiwan RID number"] = item.rid;
                 dr["商品編號"] = item.item_id;
                 dr["購買商品"] = item.product_name;
                 if (item.item_mode == 2)
                 {
                     dr["數量"] = item.buy_num * item.parent_num;
                     item.buy_num = item.buy_num * item.parent_num;
                 }
                 else
                 {
                     dr["數量"] = item.buy_num;
                 }
                 dr["網路銷售價"] = "NT$" + item.price.ToString("###,###");
                 price += item.price * item.buy_num ;
                 money= item.deduct_bonus + item.deduct_welfare + uint.Parse(item.deduct_happygo_money.ToString());
                 dr["折扣金額"] = money;
                 bonus += money;
                 if (item.buy_num > 1)
                 {
                     ulong a = (item.buy_num * item.price) - money;
                     dr["總計"] = "NT$" + a.ToString("###,###"); ;
                     sum += uint.Parse(a.ToString());
                 }
                 else
                 {
                     long a = item.price - money;
                     dr["總計"] = "NT$" + a.ToString("###,###"); ;
                     sum += uint.Parse(a.ToString());
                 }
                 dt.Rows.Add(dr);
             }
             DataRow dr1 = dt.NewRow();
             dr1["網路銷售價"] = price.ToString("###,###");
             dr1["折扣金額"] = bonus;
             dr1["總計"] = sum.ToString("###,###");
             dt.Rows.Add(dr1);
             DataRow dr2 = dt.NewRow();
             dr2["網路銷售價"] = "右欄填入佣金%";
             dr2["折扣金額"] = "8.0%";
             sum2 = sum * 0.08;
             sum4 += int.Parse(Math.Round(sum2, MidpointRounding.AwayFromZero).ToString());
             dr2["總計"] = Math.Round(sum2, MidpointRounding.AwayFromZero).ToString("###,###");
             dt.Rows.Add(dr2);
             DataRow dr3 = dt.NewRow();
             dr3["網路銷售價"] = "營業稅";
             dr3["折扣金額"] = "5%";
             sum3 = sum4 * 0.05;
             sum4 += int.Parse(Math.Round(sum3, MidpointRounding.AwayFromZero).ToString());
             dr3["總計"] = Math.Round(sum3, MidpointRounding.AwayFromZero).ToString("###,###");
             dt.Rows.Add(dr3);
             DataRow dr4 = dt.NewRow();
             dr4["網路銷售價"] = "應付佣金總數";
             dr4["總計"] = sum4.ToString("###,###");
             dt.Rows.Add(dr4);
             #endregion
         }
         fileName = "美安訂單" + DateTime.Now.ToString("yyyyMMdd") + ".xls";
         String str = "美安訂單";
         MemoryStream ms = ExcelHelperXhf.ExportDT(dt, str);
         Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName);
         Response.BinaryWrite(ms.ToArray());
     }
     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 this.Response;
 }