public ActionResult Index()
{
DataManageSearchCriteria search = new DataManageSearchCriteria();
var models = exportSqlManageProvider.Query(search);
return(View(models));
}
/// <summary>
/// 查询对象
/// </summary>
public PageInfo <ExportSqlManage> Query(DataManageSearchCriteria search)
{
string where = " 1=1 ";
if (!string.IsNullOrWhiteSpace(search.Name))
{
where = where + string.Format(" and Name like '%{0}%'", search.Name);
}
return(new PageHelper().GetPages <ExportSqlManage>(SuperMan_Read, search.PageIndex, where, "Id desc", " Id,Name,SqlText,Executetime,ReceiveEmail,IsEnable", " ExportSqlManage (nolock)", SystemConst.PageSize, true));
}
public ActionResult PostIndex(int pageindex = 1)
{
DataManageSearchCriteria search = new DataManageSearchCriteria();
TryUpdateModel(search);
var models = exportSqlManageProvider.Query(search);
return(View(models));
}
/// <summary>
/// 查询今日尚未执行的服务
/// </summary>
public IList <ExportSqlManage> QueryForWindows(DataManageSearchCriteria search)
{
IList <ExportSqlManage> models = new List <ExportSqlManage>();
const string querysql = @"
select Id,Name,SqlText,Executetime,ReceiveEmail,IsEnable
from ExportSqlManage (nolock) where IsEnable=0
and ABS(datediff(minute,getdate(),CONVERT(DATETIME, CONVERT(varchar(100), GETDATE(), 23)+' '+Executetime) ) ) <8
and ABS( datediff(minute,getdate(),CONVERT(DATETIME, CONVERT(varchar(100), GETDATE(), 23)+' '+Executetime) )) >0";
IDbParameters dbParameters = DbHelper.CreateDbParameters();
DataTable dt = DataTableHelper.GetTable(DbHelper.ExecuteDataset(SuperMan_Read, querysql, dbParameters));
if (DataTableHelper.CheckDt(dt))
{
models = DataTableHelper.ConvertDataTableList <ExportSqlManage>(dt);
}
return(models);
}
/// <summary>
/// 查询今日尚未执行的服务
/// </summary>
public IList <ExportSqlManage> QueryForWindows(DataManageSearchCriteria search)
{
IList <ExportSqlManage> results = exportSqlManageDao.QueryForWindows(search);
//string urlPath = "E:\\Export\\" + DateTime.Now.ToString("yyyyMMdd") + "\\";
string urlPath = ETS.Config.ConfigKey("ExportPath").TrimEnd('\\') + "\\" + DateTime.Now.ToString("yyyyMMdd") + "\\";
if (!System.IO.Directory.Exists(urlPath))
{
System.IO.Directory.CreateDirectory(urlPath);
}
foreach (var temp in results)
{
//判断今天是否已经导出过该文件
if (System.IO.File.Exists(urlPath + temp.Name + DateTime.Now.ToString("yyyyMMdd") + ".xls"))
{
continue;
}
else
{
try
{
DataTable dt = exportSqlManageDao.ExecuteForExport(temp.SqlText);
if (dt != null)
{
if (Excel.OutputXLSFromDataTable(null, dt,
urlPath + temp.Name + DateTime.Now.ToString("yyyyMMdd") + ".xls"))
{
EmailHelper.SendEmailTo("", temp.ReceiveEmail, "数据", "*****@*****.**", false,
attachName: urlPath + temp.Name + DateTime.Now.ToString("yyyyMMdd") + ".xls", displayName: "导出数据");
}
}
}
catch (Exception ex)
{
throw;
}
}
}
return(results);
}
/// <summary>
/// 查询对象
/// </summary>
public PageInfo <ExportSqlManage> Query(DataManageSearchCriteria search)
{
return(exportSqlManageDao.Query(search));
}