public JsonResult Export(ApplyCompensate model)
{
using (var stream = new MemoryStream(System.IO.File.ReadAllBytes(Server.MapPath(@"~/Content/Export/申请赔付.xlsx"))))
{
var list = ApplyCompensateManager.GetApplyCompensate(model);
var xssfWorkbook = new XSSFWorkbook(stream); //创建Workbook对象 2007+
// var hssfWorkbook = new HSSFWorkbook(stream)://2003
if (list.Count > 0)
{
var i = 0;
var sheet = xssfWorkbook.GetSheetAt(0);
foreach (var item in list)
{
var status = "";
if (item.Status == 0)
{
status = "待审核";
}
else if (item.Status == 1)
{
status = "已驳回";
}
else
{
status = "已通过";
}
var row = sheet.CreateRow((i++) + 1);
row.CreateCell(0).SetCellValue(item.Id);
row.CreateCell(1).SetCellValue(item.UserName);
row.CreateCell(2).SetCellValue(item.PhoneNumber);
row.CreateCell(3).SetCellValue(item.OrderId);
row.CreateCell(4).SetCellValue(item.ProductName);
row.CreateCell(5).SetCellValue(item.DifferencePrice.ToString());
row.CreateCell(6).SetCellValue(item.ApplyTime?.ToString());
row.CreateCell(7).SetCellValue(item.AuditTime?.ToString());
row.CreateCell(8).SetCellValue(status);
row.CreateCell(9).SetCellValue(item.Link);
row.CreateCell(10).SetCellValue(item.OrderChannel);
if (!string.IsNullOrWhiteSpace(item.Images))
{
int imgId = 11;
foreach (var img in item.Images.Split(';'))
{
row.CreateCell(imgId).SetCellValue(img);
imgId++;
}
}
}
}
Response.AppendHeader("Content-Disposition", "attachment;fileName=申请赔付" + ".xlsx");
xssfWorkbook.Write(Response.OutputStream);
Response.End();
}
return(Json(true));
}
public ActionResult Edit(ApplyCompensate model)
{
string js = "<script>alert(\"保存失败 \");location='/ApplyCompensate/Index';</script>";
if (ApplyCompensateManager.UpdateApplyCompensate(model))
{
return(RedirectToAction("Index"));
}
else
{
return(Content(js));
}
}
public ActionResult List(ApplyCompensate model, int pageIndex = 1, int pageSize = 30)
{
int count = 0;
var lists = ApplyCompensateManager.GetApplyCompensateList(model, pageSize, pageIndex, out count);
var list = new OutData <List <ApplyCompensate>, int>(lists, count);
var pager = new PagerModel(pageIndex, pageSize)
{
TotalItem = count
};
return(View(new ListModel <ApplyCompensate>(list.ReturnValue, pager)));
}
public static bool UpdateApplyCompensate(ApplyCompensate model)
{
const string sql = @"UPDATE [Configuration].[dbo].[SE_ApplyCompensateConfig] SET
[AuditTime]=GETDATE()
,[Status]= @Status
WHERE Id=@Id";
var sqlParameters = new SqlParameter[]
{
new SqlParameter("@Id", model.Id),
new SqlParameter("@Status", model.Status)
};
return(SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sql, sqlParameters) > 0);
}
public bool UpdateApplyCompensate(ApplyCompensate model)
{
try
{
return(DALApplyCompensate.UpdateApplyCompensate(model));
}
catch (TuhuBizException)
{
throw;
}
catch (Exception ex)
{
var exception = new ApplyCompensateException(1, "UpdateApplyCompensate", ex);
Logger.Log(Level.Error, exception, "UpdateApplyCompensate");
throw ex;
}
}
public List <ApplyCompensate> GetApplyCompensateList(ApplyCompensate model, int pageSize, int pageIndex, out int recordCount)
{
try
{
return(DALApplyCompensate.GetApplyCompensateList(model, pageSize, pageIndex, out recordCount));
}
catch (TuhuBizException)
{
throw;
}
catch (Exception ex)
{
var exception = new ApplyCompensateException(1, "GetApplyCompensateList", ex);
Logger.Log(Level.Error, exception, "GetApplyCompensateList");
throw ex;
}
}
public static List <ApplyCompensate> GetApplyCompensateList(ApplyCompensate model, int pageSize, int pageIndex, out int recordCount)
{
string sql = @"SELECT *
FROM ( SELECT [Id]
,[UserName]
,[PhoneNumber]
,[OrderId]
,[ProductName]
,[Link]
,[DifferencePrice]
,[Images]
,[ApplyTime]
,[AuditTime]
,[Status]
,OrderChannel
FROM [Configuration].[dbo].[SE_ApplyCompensateConfig] WITH ( NOLOCK )
WHERE
( @UserName = ''
OR ( @UserName <> ''
AND UserName = @UserName
)
)
AND
( @PhoneNumber = ''
OR ( @PhoneNumber <> ''
AND PhoneNumber = @PhoneNumber
)
)
AND
( @ProductName = ''
OR ( @ProductName <> ''
AND ProductName = @ProductName
)
)
AND
( @DifferencePrice = 0
OR ( @DifferencePrice <> 0
AND DifferencePrice = @DifferencePrice
)
)
AND
( @OrderId = ''
OR ( @OrderId <> ''
AND OrderId = @OrderId
)
)
AND
( @Status = -1
OR ( @Status <> -1
AND Status = @Status
)
)
AND
( @OrderChannel = ''
OR ( @OrderChannel <> ''
AND OrderChannel = @OrderChannel
)
)
AND
( @StartApplyTime = ''
OR ( @StartApplyTime <> ''
AND ApplyTime >= @StartApplyTime
)
)
AND
( @EndApplyTime = ''
OR ( @EndApplyTime <> ''
AND ApplyTime <= @EndApplyTime
)
)
AND
( @StartAuditTime = ''
OR ( @StartAuditTime <> ''
AND AuditTime >= @StartAuditTime
)
)
AND
( @EndAuditTime = ''
OR ( @EndAuditTime <> ''
AND AuditTime <= @EndAuditTime
)
)
) AS PG
ORDER BY PG.ApplyTime DESC
OFFSET ( @PageIndex - 1 ) * @PageSize ROWS FETCH NEXT @PageSize ROWS ONLY ";
string sqlCount = @"SELECT
COUNT(1)
FROM [Configuration].[dbo].[SE_ApplyCompensateConfig] WITH (NOLOCK)
WHERE ( @UserName = ''
OR ( @UserName <> ''
AND UserName = @UserName
)
)
AND
( @PhoneNumber = ''
OR ( @PhoneNumber <> ''
AND PhoneNumber = @PhoneNumber
)
)
AND
( @ProductName = ''
OR ( @ProductName <> ''
AND ProductName = @ProductName
)
)
AND
( @DifferencePrice = 0
OR ( @DifferencePrice <> 0
AND DifferencePrice = @DifferencePrice
)
)
AND
( @OrderId = ''
OR ( @OrderId <> ''
AND OrderId = @OrderId
)
)
AND
( @Status = -1
OR ( @Status <> -1
AND Status = @Status
)
)
AND
( @OrderChannel = ''
OR ( @OrderChannel <> ''
AND OrderChannel = @OrderChannel
)
)
AND
( @StartApplyTime = ''
OR ( @StartApplyTime <> ''
AND ApplyTime >= @StartApplyTime
)
)
AND
( @EndApplyTime = ''
OR ( @EndApplyTime <> ''
AND ApplyTime <= @EndApplyTime
)
)
AND
( @StartAuditTime = ''
OR ( @StartAuditTime <> ''
AND AuditTime >= @StartAuditTime
)
)
AND
( @EndAuditTime = ''
OR ( @EndAuditTime <> ''
AND AuditTime <= @EndAuditTime
)
)";
var sqlParameters = new SqlParameter[]
{
new SqlParameter("@PageSize", pageSize),
new SqlParameter("@PageIndex", pageIndex),
new SqlParameter("@OrderId", model.OrderId ?? string.Empty),
new SqlParameter("@DifferencePrice", model.DifferencePrice),
new SqlParameter("@ProductName", model.ProductName ?? string.Empty),
new SqlParameter("@PhoneNumber", model.PhoneNumber ?? string.Empty),
new SqlParameter("@UserName", model.UserName ?? string.Empty),
new SqlParameter("@Status", model.Status),
new SqlParameter("@OrderChannel", model.OrderChannel ?? string.Empty),
new SqlParameter("@StartApplyTime", model.StartApplyTime.HasValue?model.StartApplyTime.Value.ToString():""),
new SqlParameter("@StartAuditTime", model.StartAuditTime.HasValue?model.StartAuditTime.Value.ToString():""),
new SqlParameter("@EndApplyTime", model.EndApplyTime.HasValue?model.EndApplyTime.Value.ToString():""),
new SqlParameter("@EndAuditTime", model.EndAuditTime.HasValue?model.EndAuditTime.Value.ToString():"")
};
recordCount = (int)SqlHelper.ExecuteScalar(connOnRead, CommandType.Text, sqlCount, sqlParameters);
return(SqlHelper.ExecuteDataTable(connOnRead, CommandType.Text, sql, sqlParameters).ConvertTo <ApplyCompensate>().ToList());
}
