public DataTable GetDormSuggestList(TB_DormSuggest mItem)
{
var sb = new StringBuilder("select * from TB_DormSuggest ");
var db = DBO.GetInstance();
DataTable dt = null;
DbCommand dbCommandWrapper = null;
dbCommandWrapper = db.DbProviderFactory.CreateCommand();
dbCommandWrapper.CommandType = CommandType.Text;
sb.Append("where 1=1 ");
if (mItem.ID > 0)
{
sb.AppendFormat("and id={0} ", mItem.ID);
}
if (mItem.Status > -1)
{
sb.AppendFormat("and Status='{0}' ", mItem.Status);
}
if (mItem.SubmitDayBegin != default(DateTime))
{
sb.AppendFormat(" AND CAST(CreateDate as Date) between '{0:yyyy/MM/dd}' and '{1:yyyy/MM/dd}'",
mItem.SubmitDayBegin, mItem.SubmitDayEnd);
}
sb.Append(" order by CreateDate desc, status");
dbCommandWrapper.CommandText = sb.ToString();
dt = db.ExecuteDataSet(dbCommandWrapper).Tables[0];
return(dt);
}
public string ExportDormSugget(TB_DormSuggest mItem, string sfn)
{
DataTable dt = _mDAL.GetDormSuggestList(mItem);
string strFilePath = Path.Combine(System.Web.HttpContext.Current.Server.MapPath("..\\..\\"), "DormSuggest");
if (!Directory.Exists(strFilePath))
{
Directory.CreateDirectory(strFilePath);
}
string strFileName = Path.Combine(strFilePath, sfn);
new ExcelHelper().RenderToExcel(dt, strFileName);
return(strFileName);
}
private TB_DormSuggest GetParam()
{
var mItem = new TB_DormSuggest();
mItem.Status = Convert.ToInt32(ddlStatus.SelectedValue);
DateTime dtVal = DateTime.Now;
if (DateTime.TryParse(txtSubmitDayBegin.Text.Trim(), out dtVal))
{
mItem.SubmitDayBegin = dtVal;
}
if (DateTime.TryParse(txtSubmitDayEnd.Text.Trim(), out dtVal))
{
mItem.SubmitDayEnd = dtVal;
}
return(mItem);
}
public DataTable GetDormSuggestList(TB_DormSuggest mItem, Pager pager)
{
var sb = new StringBuilder("select * from TB_DormSuggest ");
var db = DBO.GetInstance();
DataTable dt = null;
DbCommand dbCommandWrapper = null;
dbCommandWrapper = db.DbProviderFactory.CreateCommand();
dbCommandWrapper.CommandType = CommandType.Text;
sb.Append("where 1=1 ");
if (mItem.ID > 0)
{
sb.AppendFormat("and id={0} ", mItem.ID);
}
if (mItem.Status > -1)
{
sb.AppendFormat("and Status='{0}' ", mItem.Status);
}
if (mItem.SubmitDayBegin != default(DateTime))
{
sb.AppendFormat(" AND CAST(CreateDate as Date) between '{0:yyyy/MM/dd}' and '{1:yyyy/MM/dd}'",
mItem.SubmitDayBegin, mItem.SubmitDayEnd);
}
if (pager != null && !pager.IsNull)
{
dbCommandWrapper.CommandText = pager.GetPagerSql4Count(sb.ToString());
dt = db.ExecuteDataSet(dbCommandWrapper).Tables[0];
pager.TotalRecord = Convert.ToInt32(dt.Rows[0][0]);
dbCommandWrapper.CommandText = pager.GetPagerSql4Data(sb.ToString(), DataBaseTypeEnum.sqlserver);
}
else
{
dbCommandWrapper.CommandText = sb.ToString();
}
dt = db.ExecuteDataSet(dbCommandWrapper).Tables[0];
return(dt);
}
public DataTable GetDormSuggestList(TB_DormSuggest mItem, ref Pager pager)
{
return(_mDAL.GetDormSuggestList(mItem, pager));
}