public HttpResponseMessage GetOrdertableSystemExcel(parm reparm)
{
string result = string.Empty;
try
{
RuleExcel ruleExcel = new RuleExcel();
string path = @"~\Excel\MerchantSysem\";
string excname = DateTime.Now.ToString("yyyyMMddHHmmss") + "订单列表.xlsx";
string filename = path + excname;
var sPath = @filename;
path = HostingEnvironment.MapPath(path);
sPath = HostingEnvironment.MapPath(sPath); //转为绝对路径
string where = parwhere(reparm);
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
string file = "fldCreatetime,fldtransactionnum,fldOrdernum,fldOrderAmount,fldMerchID,fldMerchName,fldStaute,fldChannelType,fldRateName,fldchangstautetime,fldSettlement,fldServiceCharge";
DataTable dt = rule.GetQueryDate("0", "vwOrdertableSystem", "1=1" + where, file);
List <string> colname = new List <string>();
colname.Add("订单创建时间");
colname.Add("交易流水");
colname.Add("订单号");
colname.Add("订单金额");
colname.Add("商户ID");
colname.Add("商户名称");
colname.Add("代付状态");
colname.Add("支付类型");
colname.Add("支付方式");
colname.Add("状态变化时间");
colname.Add("结算金额");
colname.Add("手续费");
ruleExcel.TableToExcel(dt, sPath, colname);
//取得当前网站的绝对路径
var sRootePath = HostingEnvironment.MapPath(HostingEnvironment.ApplicationVirtualPath);
//取得文件相对于网站的路径（如：TempData/abc.mdb）
var sRelativeUri = new Uri(sRootePath, UriKind.Absolute).MakeRelativeUri(new Uri(sPath, UriKind.Absolute)).ToString();
// 先判断是否与原路径相同，如相同则表示二者可能不在同一个磁盘上
var sResult = (0 == string.Compare(Path.GetFullPath(sRelativeUri), Path.GetFullPath(sPath), StringComparison.OrdinalIgnoreCase))
? null : $"http://{HttpContext.Current.Request.Url.Authority}/{sRelativeUri}"; //构造返回路径
result = rule.JsonStr("ok", excname, sResult);
}
catch (Exception e)
{
throw new InsertException(e.Message, "Transaction", "GetOrdertableExcelDate" +
"", "");
}
return(new HttpResponseMessage {
Content = new StringContent(result, System.Text.Encoding.UTF8, "application/json")
});
}
public HttpResponseMessage ExcelDate(amountpram amountpram)
{
try
{
string result = string.Empty;
RuleExcel ruleExcel = new RuleExcel();
string path = @"~\Excel\";
string excname = DateTime.Now.ToString("yyyyMMddHHmmss") + "金额明细.xlsx";
string filename = path + excname;
var sPath = @filename;
path = HostingEnvironment.MapPath(path);
sPath = HostingEnvironment.MapPath(sPath); //转为绝对路径
string where = parwhere(amountpram);
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
if (string.IsNullOrEmpty(amountpram.startime))
{
amountpram.startime = DateTime.Now.Date.ToString();
}
if (string.IsNullOrEmpty(amountpram.endtime))
{
amountpram.endtime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
}
RuleSuccessStatecs statecs = new RuleSuccessStatecs();
//计算多个时间
DateTime stime = DateTime.Parse(amountpram.startime);
DateTime etime = DateTime.Parse(amountpram.endtime);
TimeSpan ts = etime - stime;
int tsday = ts.Days;
DataTable dataTable = new DataTable();
//时间不是当天时
if (tsday > 0)
{
for (int i = 0; i < tsday; i++)
{
DateTime pramendtime = stime.AddDays(i);
DataTable table = statecs.AllAmount(stime.AddDays(i).ToString("yyyy-MM-dd HH:mm:ss"), pramendtime.AddHours(24).ToString("yyyy-MM-dd HH:mm:ss"), where);
dataTable.Merge(table);
}
}
else
{
DataTable table = statecs.AllAmount(amountpram.startime, amountpram.endtime, where);
dataTable.Merge(table);
}
string allamount = dataTable.Compute("sum(allamount)", "true").ToString();
string seccs = dataTable.Compute("sum(seccs)", "true").ToString();
string fldServiceCharge = dataTable.Compute("sum(fldServiceCharge)", "true").ToString();
string AllCount = dataTable.Compute("sum(AllCount)", "true").ToString();
string SuccessCount = dataTable.Compute("sum(SuccessCount)", "true").ToString();
string fldProfit = dataTable.Compute("sum(Profit)", "true").ToString();
DataRow row = dataTable.NewRow();
row["allamount"] = allamount == "" ? "0" : allamount;
row["seccs"] = seccs == "" ? "0" : seccs;
row["fldServiceCharge"] = fldServiceCharge == "" ? "0" : fldServiceCharge;
row["AllCount"] = AllCount == "" ? "0" : AllCount;
row["SuccessCount"] = SuccessCount == "" ? "0" : SuccessCount;
row["Profit"] = fldProfit == "" ? "0" : fldProfit;
row["time"] = "合计";
dataTable.Rows.Add(row);
List <string> colname = new List <string>();
colname.Add("日期");
colname.Add("商户名称");
colname.Add("商户号");
colname.Add("渠道名称");
colname.Add("渠道号");
colname.Add("支付方式");
colname.Add("总交易额");
colname.Add("交易成功金额");
colname.Add("手续费");
colname.Add("收入");
colname.Add("总笔数");
colname.Add("成功笔数");
ruleExcel.TableToExcel(dataTable, sPath, colname);
//取得当前网站的绝对路径
var sRootePath = HostingEnvironment.MapPath(HostingEnvironment.ApplicationVirtualPath);
//取得文件相对于网站的路径（如：TempData/abc.mdb）
var sRelativeUri = new Uri(sRootePath, UriKind.Absolute).MakeRelativeUri(new Uri(sPath, UriKind.Absolute)).ToString();
// 先判断是否与原路径相同，如相同则表示二者可能不在同一个磁盘上
var sResult = (0 == string.Compare(Path.GetFullPath(sRelativeUri), Path.GetFullPath(sPath), StringComparison.OrdinalIgnoreCase))
? null : $"http://{HttpContext.Current.Request.Url.Authority}/{sRelativeUri}"; //构造返回路径
result = rule.JsonStr("ok", excname, sResult);
return(new HttpResponseMessage {
Content = new StringContent(result, System.Text.Encoding.UTF8, "application/json")
});
}
catch (Exception e)
{
throw new InsertException(e.Message, "Transaction", "GetOrdertableExcelDate" +
"", "");
}
}
public HttpResponseMessage GetExcelDate(parm reparm)
{
string result = string.Empty;
try
{
RuleExcel ruleExcel = new RuleExcel();
string path = @"~\Excel\";
string excname = DateTime.Now.ToString("yyyyMMddHHmmss") + "代付列表.xlsx";
string filename = path + excname;
var sPath = @filename;
path = HostingEnvironment.MapPath(path);
sPath = HostingEnvironment.MapPath(sPath); //转为绝对路径
string where = parwhere(reparm);
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
string file = "fldCreateTime,fldChannelnum,fldOrdernum,fldMerchID,fldMerchName,fldPayState,fldServiceCharge,fldBankName,fldBankbranch,fldAccountname,fldBankCardId,fldBankprovince,fldBankcity,fldPayAmount,fldRtefundAmount,fldChannelID," +
"fldLaunchIP,fldNotice,fldchangstautetime,fldtransactiontime,fldBankType,fldBankTelephoneNo,fldIdCard,fldCardType";
DataTable dt = rule.GetQueryDate("0", "vwtblAgentPay", "1=1" + where, file);
List <string> colname = new List <string>();
colname.Add("创建时间");
colname.Add("渠道流水号");
colname.Add("订单号");
colname.Add("商户号");
colname.Add("商户名");
colname.Add("订单状态");
colname.Add("手续费");
colname.Add("银行名称");
colname.Add("银行支行");
colname.Add("账户名");
colname.Add("银行卡号");
colname.Add("银行省份");
colname.Add("开户行所在市");
colname.Add("代付金额");
colname.Add("上送金额");
colname.Add("渠道号");
colname.Add("发起方IP");
colname.Add("异步通知地址");
colname.Add("状态变化时间");
colname.Add("交易时间");
colname.Add("银行类型");
colname.Add("电话号码");
colname.Add("身份证号");
colname.Add("银行卡类型");
ruleExcel.TableToExcel(dt, sPath, colname);
//取得当前网站的绝对路径
var sRootePath = HostingEnvironment.MapPath(HostingEnvironment.ApplicationVirtualPath);
//取得文件相对于网站的路径（如：TempData/abc.mdb）
var sRelativeUri = new Uri(sRootePath, UriKind.Absolute).MakeRelativeUri(new Uri(sPath, UriKind.Absolute)).ToString();
// 先判断是否与原路径相同，如相同则表示二者可能不在同一个磁盘上
var sResult = (0 == string.Compare(Path.GetFullPath(sRelativeUri), Path.GetFullPath(sPath), StringComparison.OrdinalIgnoreCase))
? null : $"http://{HttpContext.Current.Request.Url.Authority}/{sRelativeUri}"; //构造返回路径
result = rule.JsonStr("ok", excname, sResult);
}
catch (Exception e)
{
throw new InsertException(e.Message, "Transaction", "AgentPay", "");
}
return(new HttpResponseMessage {
Content = new StringContent(result, System.Text.Encoding.UTF8, "application/json")
});
}
