/// <summary>
/// 下载PO
/// </summary>
/// <param name="year"></param>
/// <param name="month"></param>
/// <param name="brand"></param>
/// <param name="usedType"></param>
/// <param name="key"></param>
/// <param name="rule"></param>
/// <param name="sidx"></param>
/// <param name="sord"></param>
/// <returns></returns>
public JsonResult DownPO(string calculationType, int year, int month, string brand, string usedType, string key, string rule, string sidx, string sord)
{
try
{
//PO存的位置
string savePath = "/Areas/DSF20/Content/POFile";
#region 获取数据
key = Server.UrlDecode(key);
rule = Server.UrlDecode(rule);
R_ContractInfo_Impl impl = new R_ContractInfo_Impl();
int totalCount = 0;
int totalPage = 0;
List<V_BIDealerContract> list = impl.GetPageList(calculationType, year, month, brand, usedType, key, sidx, sord, 1, int.MaxValue, out totalCount, out totalPage);
if (list.Count == 0)
{
throw new Exception("无BI合同数据");
}
var dsfCalculation = new DSFCalculation_Impl();
int saveCount = dsfCalculation.GetContractSaveCount(calculationType, year, month);
if (list.Count != saveCount)
{
throw new Exception("当前计算月份还有未存档的合同数据,请先存档再下载PO");
}
#endregion
//数据转存T_DealerServiceFeeInfoByMonly
var result = dsfCalculation.SaveDBToSFInfoByMonly(calculationType, year, month, this.CurrentUserEntry.GetDKCode());
if (result == false)
{
throw new Exception("数据拷贝异常");
}
//生成POList
result = dsfCalculation.InsertPOList(year, month, this.CurrentUserEntry.GetDKCode());
if (result == false)
{
throw new Exception("生成POList异常");
}
//获取PO结果-Dealer
DataTable dt = dsfCalculation.GetPOList(year, month);
//获取PO详情-Contract
var contractList = dsfCalculation.GetPODetailList(year, month);
//声明工作表
HSSFWorkbook wk = new HSSFWorkbook();
#region 导出Dealer数据
{
ISheet sheet = wk.CreateSheet("DealerInfo");
//设置表头
IRow cells = sheet.CreateRow(0);
#region 列名
cells.CreateCell(0).SetCellValue("Month");
cells.CreateCell(1).SetCellValue("DealerCode");
cells.CreateCell(2).SetCellValue("DealerNameCN");
cells.CreateCell(3).SetCellValue("Brand");
cells.CreateCell(4).SetCellValue("Region");
cells.CreateCell(5).SetCellValue("ProvinceEN");
cells.CreateCell(6).SetCellValue("CityEN");
cells.CreateCell(7).SetCellValue("AreaManager");
cells.CreateCell(8).SetCellValue("ContractVolume");
cells.CreateCell(9).SetCellValue("NewCarNum");
cells.CreateCell(10).SetCellValue("UsedCarNum");
cells.CreateCell(11).SetCellValue("UnitPrice");
cells.CreateCell(12).SetCellValue("Category");
cells.CreateCell(13).SetCellValue("NewCarServiceFee");
cells.CreateCell(14).SetCellValue("UsedCarServiceFee");
cells.CreateCell(15).SetCellValue("TaxPayerType");
cells.CreateCell(16).SetCellValue("ServiceFeeAmount");
cells.CreateCell(17).SetCellValue("BasicServiceFeeAmount");
cells.CreateCell(18).SetCellValue("OtherServiceFeeAmount");
cells.CreateCell(19).SetCellValue("SpecialFee");
cells.CreateCell(20).SetCellValue("ServiceFeeStatus");
cells.CreateCell(21).SetCellValue("ServiceFeeDetail");
cells.CreateCell(22).SetCellValue("GeneralTaxRate");
cells.CreateCell(23).SetCellValue("ActualTaxRate");
cells.CreateCell(24).SetCellValue("ActualUnityPrice");
#endregion
int i = 0;
foreach (DataRow row in dt.Rows)
{
#region 数据
i++;
IRow rowtemp = sheet.CreateRow(i); //循环行
rowtemp.CreateCell(0).SetCellValue(row["Month"].ToString().Replace("-", ""));
rowtemp.CreateCell(1).SetCellValue(row["DealerCode"].ToString());
rowtemp.CreateCell(2).SetCellValue(row["DealerNameCN"].ToString());
rowtemp.CreateCell(3).SetCellValue(row["Brand"].ToString());
rowtemp.CreateCell(4).SetCellValue(row["Region"].ToString());
rowtemp.CreateCell(5).SetCellValue(row["ProvinceEN"].ToString());
rowtemp.CreateCell(6).SetCellValue(row["CityEN"].ToString());
rowtemp.CreateCell(7).SetCellValue(row["AreaManager"].ToString());
rowtemp.CreateCell(8).SetCellValue(row["ContractVolume"].ToString());
rowtemp.CreateCell(9).SetCellValue(row["NewCarNum"].ToString());
rowtemp.CreateCell(10).SetCellValue(row["UsedCarNum"].ToString());
rowtemp.CreateCell(11).SetCellValue(row["UnitPrice"].ToString());
rowtemp.CreateCell(12).SetCellValue(row["Category"].ToString());
rowtemp.CreateCell(13).SetCellValue(row["NewCarServiceFee"].ToString());
rowtemp.CreateCell(14).SetCellValue(row["UsedCarServiceFee"].ToString());
rowtemp.CreateCell(15).SetCellValue(row["TaxPayerType"].ToString());
rowtemp.CreateCell(16).SetCellValue(row["ServiceFeeAmount"].ToString());
rowtemp.CreateCell(17).SetCellValue(row["BasicServiceFeeAmount"].ToString());
rowtemp.CreateCell(18).SetCellValue(row["OtherServiceFeeAmount"].ToString());
rowtemp.CreateCell(19).SetCellValue(row["SpecialFee"].ToString());
rowtemp.CreateCell(20).SetCellValue(row["ServiceFeeStatus"].ToString());
rowtemp.CreateCell(21).SetCellValue(row["ServiceFeeDetail"].ToString());
rowtemp.CreateCell(22).SetCellValue(row["GeneralTaxRate"].ToString());
rowtemp.CreateCell(23).SetCellValue(row["ActualTaxRate"].ToString());
rowtemp.CreateCell(24).SetCellValue(row["ActualUnityPrice"].ToString());
#endregion
}
}
#endregion
#region 导出Contract数据
//处理65536问题
int rowSize = 65000;//页大小
//总页数
int pageSize = (int)Math.Ceiling((contractList.Count * 0.1) / (rowSize * 0.1));
for (int currentPage = 1; currentPage <= pageSize; currentPage++)
{
var dbList = contractList.Skip((currentPage - 1) * rowSize).Take(rowSize).ToList();
ISheet sheet = wk.CreateSheet("ContractInfo_" + currentPage.ToString().PadLeft(2, '0'));
//设置表头
IRow cells = sheet.CreateRow(0);
#region 列名
cells.CreateCell(0).SetCellValue("月份");
cells.CreateCell(1).SetCellValue("经销商编码");
cells.CreateCell(2).SetCellValue("合同号");
cells.CreateCell(3).SetCellValue("申请日期");
cells.CreateCell(4).SetCellValue("激活日期");
cells.CreateCell(5).SetCellValue("经销商主品牌");
cells.CreateCell(6).SetCellValue("厂商");
cells.CreateCell(7).SetCellValue("新车/二手车");
cells.CreateCell(8).SetCellValue("普通利率");
cells.CreateCell(9).SetCellValue("INT利率");
cells.CreateCell(10).SetCellValue("项目号");
cells.CreateCell(11).SetCellValue("是否大众集团品牌");
cells.CreateCell(12).SetCellValue("是否增益贷");
cells.CreateCell(13).SetCellValue("是否附加贷");
cells.CreateCell(14).SetCellValue("是否自主贴息");
cells.CreateCell(15).SetCellValue("是否奢牌");
cells.CreateCell(16).SetCellValue("总价");
cells.CreateCell(17).SetCellValue("首付");
cells.CreateCell(18).SetCellValue("贷款");
cells.CreateCell(19).SetCellValue("贷期");
cells.CreateCell(20).SetCellValue("附加贷");
cells.CreateCell(21).SetCellValue("基础服务费");
cells.CreateCell(22).SetCellValue("增益贷服务费");
cells.CreateCell(23).SetCellValue("附加贷服务费");
cells.CreateCell(24).SetCellValue("大额融资服务费");
cells.CreateCell(25).SetCellValue("经销商自主贴息");
cells.CreateCell(26).SetCellValue("单价");
cells.CreateCell(27).SetCellValue("原服务费");
cells.CreateCell(28).SetCellValue("总服务费");
cells.CreateCell(29).SetCellValue("CarveOut基础服务");
cells.CreateCell(30).SetCellValue("CarveOut其他服务费");
cells.CreateCell(31).SetCellValue("备注");
#endregion
int i = 0;
foreach (var contract in dbList)
{
#region 数据
i++;
IRow rowtemp = sheet.CreateRow(i); //循环行
rowtemp.CreateCell(0).SetCellValue(contract.Year + "" + contract.Month.ToString().PadLeft(2, '0'));
rowtemp.CreateCell(1).SetCellValue(contract.DealerCode);
rowtemp.CreateCell(2).SetCellValue(contract.LoanNo);
rowtemp.CreateCell(3).SetCellValue(contract.ApplyDate.HasValue ? contract.ApplyDate.Value.ToString("yyyy-MM-dd") : "");
rowtemp.CreateCell(4).SetCellValue(contract.ActiveDate.HasValue ? contract.ActiveDate.Value.ToString("yyyy-MM-dd") : "");
rowtemp.CreateCell(5).SetCellValue(contract.Brand);
rowtemp.CreateCell(6).SetCellValue(contract.Manuftr);
rowtemp.CreateCell(7).SetCellValue(contract.UsedType);
rowtemp.CreateCell(8).SetCellValue(contract.NormalRate.HasValue ? contract.NormalRate.Value : 0);
rowtemp.CreateCell(9).SetCellValue(contract.INTRate.HasValue ? contract.INTRate.Value : 0);
rowtemp.CreateCell(10).SetCellValue(StrUtil.SubString(contract.ProjectCode, 4));
rowtemp.CreateCell(11).SetCellValue((contract.IsVWBrand.HasValue && contract.IsVWBrand.Value) ? "是" : "否");
rowtemp.CreateCell(12).SetCellValue((contract.IsGainLoan.HasValue && contract.IsGainLoan.Value) ? "是" : "否");
rowtemp.CreateCell(13).SetCellValue((contract.IsAddLoan.HasValue && contract.IsAddLoan.Value) ? "是" : "否");
rowtemp.CreateCell(14).SetCellValue((contract.IsDealerInDis.HasValue && contract.IsDealerInDis.Value) ? "是" : "否");
rowtemp.CreateCell(15).SetCellValue((contract.IsLux.HasValue && contract.IsLux.Value) ? "是" : "否");
rowtemp.CreateCell(16).SetCellValue(StrUtil.DoubleFormat(contract.CashPriceTotal));
rowtemp.CreateCell(17).SetCellValue(StrUtil.DoubleFormat(contract.DownPayTotal));
rowtemp.CreateCell(18).SetCellValue(StrUtil.DoubleFormat(contract.ContractPriceTotal));
rowtemp.CreateCell(19).SetCellValue(StrUtil.DoubleFormat(contract.ContractPriceTNR));
rowtemp.CreateCell(20).SetCellValue(StrUtil.DoubleFormat(contract.VAPPriceTotal)); ;
rowtemp.CreateCell(21).SetCellValue(StrUtil.DoubleFormat(contract.BasicServiceFee));
rowtemp.CreateCell(22).SetCellValue(StrUtil.DoubleFormat(contract.GainLoanServiceFee));
rowtemp.CreateCell(23).SetCellValue(StrUtil.DoubleFormat(contract.AddLoanServiceFee));
rowtemp.CreateCell(24).SetCellValue(StrUtil.DoubleFormat(contract.LargeAmountServiceFee));
rowtemp.CreateCell(25).SetCellValue(StrUtil.DoubleFormat(contract.DealerInDisServiceFee));
rowtemp.CreateCell(26).SetCellValue(contract.UnitPrice.HasValue ? StrUtil.DoubleFormat(contract.UnitPrice) : "");
double sf = contract.BasicServiceFee ?? 0;
sf += contract.GainLoanServiceFee ?? 0;
sf += contract.AddLoanServiceFee ?? 0;
sf += contract.LargeAmountServiceFee ?? 0;
sf += contract.DealerInDisServiceFee ?? 0;
rowtemp.CreateCell(27).SetCellValue(StrUtil.DoubleFormat(sf));
rowtemp.CreateCell(28).SetCellValue(StrUtil.DoubleFormat(contract.TotalSF));
rowtemp.CreateCell(29).SetCellValue(StrUtil.DoubleFormat(contract.CoveroutBaseSF));
rowtemp.CreateCell(30).SetCellValue(StrUtil.DoubleFormat(contract.CoveroutOtherSF));
rowtemp.CreateCell(31).SetCellValue(contract.Remark);
#endregion
}
}
#endregion
string dir = Server.MapPath("~" + savePath);
if (Directory.Exists(dir) == false)
{
Directory.CreateDirectory(dir);
}
string fileName = year + "" + month.ToString().PadLeft(2, '0') + "POList.xls";
string filePath = dir + "\\" + fileName;
FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
wk.Write(fs);
fs.Dispose();
return Json(new { msg = "生成成功,PO号:" + year + "" + month.ToString().PadLeft(2, '0') + ",详情数据已导出", result = true, data = WebCommon.WebPrefix + savePath + "/" + fileName }, JsonRequestBehavior.AllowGet);
}
catch (Exception ex)
{
return Json(new { msg = "生成失败:" + ex.Message, result = false, data = "" }, JsonRequestBehavior.AllowGet);
}
}
11-29
03-12
166