public IEnumerable <JifenOut> GetStoreJifen(StoreCondition input)
{
var sqlParamters = new List <SqlParameter>();
sqlParamters.Add(new SqlParameter("@pageindex", input.Page));
sqlParamters.Add(new SqlParameter("@pagesize", input.Limit));
sqlParamters.Add(new SqlParameter("@name", input.Name ?? ""));
var r = Repository.ExecuteQuery <JifenOut>("EXEC sp_GetPageJifen @pageindex,@pagesize,@name", sqlParamters.ToArray());
return(r);
}
public IEnumerable <TipsFlowOut> GetTipsFlowList(StoreCondition input)
{
var sqlParamters = new List <SqlParameter>();
sqlParamters.Add(new SqlParameter("@pageindex", input.Page));
sqlParamters.Add(new SqlParameter("@pagesize", input.Limit));
sqlParamters.Add(new SqlParameter("@name", input.Name ?? ""));
sqlParamters.Add(new SqlParameter("@Start", input.Start ?? ""));
sqlParamters.Add(new SqlParameter("@End", input.End ?? ""));
var r = Repository.ExecuteQuery <TipsFlowOut>("EXEC sp_GetPageTipsFlow @pageindex,@pagesize,@name,@Start,@End", sqlParamters.ToArray());
return(r);
}
public IEnumerable <PointDetailOut> GetPointDetail(StoreCondition input)
{
var sqlParamters = new List <SqlParameter>();
sqlParamters.Add(new SqlParameter("@id", input.Id));
sqlParamters.Add(new SqlParameter("@pageindex", input.Page));
sqlParamters.Add(new SqlParameter("@pagesize", input.Limit));
sqlParamters.Add(new SqlParameter("@type", input.StoreType));
sqlParamters.Add(new SqlParameter("@start", input.Start ?? ""));
sqlParamters.Add(new SqlParameter("@end", input.End ?? ""));
var r = Repository.ExecuteQuery <PointDetailOut>("EXEC sp_GetPagePointDetail @id,@pageindex,@pagesize,@type,@start,@end", sqlParamters.ToArray());
return(r);
}
public IEnumerable <OrderOut> GetOrderList(StoreCondition input)
{
var sqlParamters = new List <SqlParameter>();
sqlParamters.Add(new SqlParameter("@pageindex", input.Page));
sqlParamters.Add(new SqlParameter("@pagesize", input.Limit));
sqlParamters.Add(new SqlParameter("@name", input.Name ?? ""));
sqlParamters.Add(new SqlParameter("@trade_no", input.Area ?? ""));
sqlParamters.Add(new SqlParameter("@Start", input.Start ?? ""));
sqlParamters.Add(new SqlParameter("@End", input.End ?? ""));
sqlParamters.Add(new SqlParameter("@Type", input.StoreType));
var r = Repository.ExecuteQuery <OrderOut>("EXEC sp_GetPageOrder @pageindex,@pagesize,@type,@name,@trade_no,@Start,@End", sqlParamters.ToArray());
return(r);
}
public IEnumerable <StoreOut> GetStoreList(StoreCondition input, out int totalCount)
{
var sqlParamters = new List <SqlParameter>();
sqlParamters.Add(new SqlParameter("@pageindex", input.Page));
sqlParamters.Add(new SqlParameter("@pagesize", input.Limit));
sqlParamters.Add(new SqlParameter("@name", input.Name ?? ""));
sqlParamters.Add(new SqlParameter("@storetype", input.StoreType));
var outParameter = new SqlParameter("@count", SqlDbType.Int, 4);
outParameter.Direction = ParameterDirection.Output;
sqlParamters.Add(outParameter);
var r = Repository.ExecuteQuery <StoreOut>("EXEC sp_GetPageStore @pageindex,@pagesize,@name,@storetype,@count out", sqlParamters.ToArray());
totalCount = Convert.ToInt32(outParameter.Value == DBNull.Value ? 0 : outParameter.Value);
return(r);
}
public string ExportExcel(StoreCondition input, string path)
{
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
var sqlParamters = new List <SqlParameter>();
sqlParamters.Add(new SqlParameter("@name", input.Name ?? ""));
sqlParamters.Add(new SqlParameter("@Start", input.Start ?? ""));
sqlParamters.Add(new SqlParameter("@End", input.End ?? ""));
var dt = Repository.ExecuteQuery <TakeCheckOut>("EXEC sp_GetExportTakeCheck @name,@Start,@End", sqlParamters.ToArray());
//创建EXCEL工作薄
HSSFWorkbook workBook = null;
using (FileStream fs = File.Open(path + "/takelist.xls", FileMode.Open, System.IO.FileAccess.Read, FileShare.Read))
{
workBook = new HSSFWorkbook(fs);
fs.Close();
}
//创建sheet文件表
Sheet sheet = workBook.GetSheetAt(0);//workBook..CreateSheet("提现列表");
int r = 0;
Dictionary <string, object> dict = new Dictionary <string, object>();
//创建表头
if (dt.Count() > 0)
{
Expression <Func <TakeCheck> > properties = (() => new TakeCheck()
{
Id = 0,
StoreName = "",
TakeOut = 0,
Account = "",
Bank = "",
BankNo = "",
CreateTime = DateTime.Now,
ExportCount = 0,
ConfirmTime = DateTime.Now
});
dict = properties.GetPropertyWithValue();
//Cell cell = header.CreateCell(r);
// cell.SetCellValue("头部");
//foreach (var item in dict.Keys)
//{
// string colunname = GetColumnName(item);
// if (colunname != "")
// {
// Cell cell = header.CreateCell(r);
// cell.SetCellValue(colunname);
// r++;
// }
//}
}
else
{
Row header = sheet.CreateRow(2);
Cell cell = header.CreateCell(0);
cell.SetCellValue("数据为空");
}
#region 设置字体
Font font = workBook.CreateFont(); //创建字体样式
font.Color = HSSFColor.RED.index; //设置字体颜色
CellStyle style = workBook.CreateCellStyle(); //创建单元格样式
style.SetFont(font); //设置单元格样式中的字体样式
#endregion
Row row;
Dictionary <string, string> di;
//数据
for (var i = 0; i < dt.Count(); i++)
{
row = sheet.CreateRow(i + 1);
r = 0;
TakeCheckOut model = dt.ElementAt(i);
di = ExpressionHelper.GetProperties <TakeCheckOut>(model);
foreach (var item in dict.Keys)
{
var cell = row.CreateCell(r);
object obj = di[item];
if (obj != null)
{
cell.SetCellValue(obj.ToString());
}
else
{
cell.SetCellValue(string.Empty);
}
if (item == "ExportCount")
{
var v = 0;
if (int.TryParse(obj.ToString(), out v) && v > 0)
{
cell.CellStyle = style;//为单元格设置显示样式
}
}
if (item == "ConfirmTime")
{
cell.CellStyle = style;
}
r++;
}
}
//转为字节数组
var stream = new MemoryStream();
workBook.Write(stream);
path += System.Guid.NewGuid().GetHashCode().ToString("x") + ".xls";
using (FileStream fs = new FileStream(path, FileMode.Create, FileAccess.Write))
{
byte[] data = stream.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
data = null;
}
stream.Close();
stream.Dispose();
if (File.Exists(path))
{
Update(dt);
return(path);
}
else
{
return(string.Empty);
}
}