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;
}
