/// <summary>
/// 真分页情况下，返回分页数据
/// </summary>
/// <param name="template"></param>
/// <param name="pPageNo"></param>
/// <param name="pPageSize"></param>
/// <param name="pWhere"></param>
/// <param name="paras"></param>
/// <param name="pOrderby"></param>
/// <returns></returns>
public DataTable GetDataIsTruePaged(TemplateModel template, int pPageNo, int pPageSize, string pWhere, string pOrderby, bool paged = true)
{
DbHelperSQL dbhelper = new DbHelperSQL(template.SQLBuilder.ConnectionStrings);
DataTable dt;
string sql = string.Empty;
if (template.IsTruePaged == true) //真分页，表示直接从数据库取数据
{
StringBuilder sb = new StringBuilder();
//分页语句 例：select * from (select row_number() over(order by @@servername) as bs_row_num,* from BS_TEMPLATE_MAIN where TemplateName like N'%%')t where bs_row_num>4 and bs_row_num <8
sb.Append(@"select * from (select row_number() over(order by ");
if (!string.IsNullOrWhiteSpace(pOrderby))
{
sb.Append(pOrderby);
}
else if (!string.IsNullOrWhiteSpace(template.SQLBuilder.Orderby))
{
sb.Append(template.SQLBuilder.Orderby);
}
else
{
sb.Append("@@servername");
}
sb.Append(") as bs_row_num, ");
sb.Append(template.SQLBuilder.SelectSQL);
sb.Append(" from ");
sb.Append(template.SQLBuilder.TableName);
sb.Append(template.SQLBuilder.DefauleQuery.Replace("[本人]", BaseComponent.GetCurrentUserLoginName()));
if (!string.IsNullOrEmpty(pWhere))
{
sb.Append(" and ");
sb.Append(pWhere);
}
sb.Append(")t");
if (paged)
{
string startPage = ((pPageNo - 1) * pPageSize).ToString();
string endPage = (pPageNo * pPageSize + 1).ToString();
sb.Append(" where bs_row_num>");
sb.Append(startPage);
sb.Append(" and bs_row_num<");
sb.Append(endPage);
}
BaseComponent.Info("GetDataIsTruePaged SQL:" + sb.ToString());
dt = dbhelper.ExecuteTable(CommandType.Text, sb.ToString());
return(dt);
}
return(null);
}
public int GetTotalCount(TemplateModel template, string pWhere)
{
DbHelperSQL dbhelper = new DbHelperSQL(template.SQLBuilder.ConnectionStrings);
StringBuilder sb = new StringBuilder();
sb.Append("select count(*) from ");
sb.Append(template.SQLBuilder.TableName);
sb.Append(template.SQLBuilder.DefauleQuery.Replace("[本人]", BaseComponent.GetCurrentUserLoginName()));
if (!string.IsNullOrWhiteSpace(pWhere))
{
sb.Append(" and ");
sb.Append(pWhere);
}
BaseComponent.Info("GetTotalCount SQL:" + sb.ToString());
return((int)dbhelper.ExecuteScalar(CommandType.Text, sb.ToString()));
}
/// <summary>
/// 返回总页数
/// </summary>
/// <param name="templateID"></param>
/// <param name="pageSize"></param>
/// <param name="where"></param>
/// <returns></returns>
public string PageCount(TemplateModel template, string pPageSize, string pWhere)
{
DbHelperSQL dbhelper = new DbHelperSQL(template.SQLBuilder.ConnectionStrings);
string pageSize = pPageSize;
string where = template.SQLBuilder.DefauleQuery.Replace("[本人]", BaseComponent.GetCurrentUserLoginName()) + pWhere;
string result = "0";
string sql = "select count(*) from " + template.SQLBuilder.TableName + " where " + where;
object rows = dbhelper.ExecuteScalar(CommandType.Text, sql);
BaseComponent.Info("SQL:" + sql);
if (rows != null && rows.ToString() != "")
{
result = Convert.ToString(Convert.ToInt32(rows));
}
return(result);
}
/// <summary>
/// 假分页情况下，返回所有数据
/// </summary>
/// <param name="template"></param>
/// <param name="paras"></param>
/// <returns></returns>
public DataTable GetDataIsNotTruePaged(TemplateModel template, string pWhere, SqlParameter[] paras, string orderBy = "")
{
DbHelperSQL dbhelper = new DbHelperSQL(template.SQLBuilder.ConnectionStrings);
DataTable dt;
if (template.SQLBuilder.IsProcudure == true)
{
//SqlParameter[] newParas = new SqlParameter[template.SQLBuilder.Parameters.Length + paras.Length];
//template.SQLBuilder.Parameters.CopyTo(newParas, 0);
//paras.CopyTo(newParas, template.SQLBuilder.Parameters.Length);
List <SqlParameter> listParas = new List <SqlParameter>();
if (paras != null && paras.Count() > 0)
{
listParas.AddRange(paras.ToList());
}
if (template.SQLBuilder.Parameters != null && template.SQLBuilder.Parameters.Count() > 0)
{
foreach (var dic in template.SQLBuilder.Parameters)
{
listParas.Add(new SqlParameter(dic.Key, dic.Value.Replace("[本人]", BaseComponent.GetCurrentUserLoginName())));
}
}
DataTable returnDt = dbhelper.ExecuteTable(CommandType.StoredProcedure, template.SQLBuilder.TableName, listParas.ToArray());
string filter = string.Empty;
if (template.SQLBuilder.DefauleQuery != "where 1=1")
{
filter += template.SQLBuilder.DefauleQuery.Substring(7).Replace("[本人]", BaseComponent.GetCurrentUserLoginName());
}
if (!string.IsNullOrWhiteSpace(pWhere))
{
filter += " and " + pWhere;
}
BaseComponent.Info("存储过程本人查询条件:" + filter);
var rows = returnDt.Select(filter);
dt = returnDt.Clone();
for (int i = 0; i < rows.Length; i++)
{
dt.ImportRow(rows[i]);
}
//列排序，计算列
int columnIndex = 0;
foreach (string proColumnName in template.SQLBuilder.ColumnNames.Split(','))
{
if (dt.Columns.Contains(proColumnName))
{
dt.Columns[proColumnName].SetOrdinal(columnIndex);
}
else
{
dt.Columns.Add(proColumnName);
dt.Columns[proColumnName].Expression = template.SQLBuilder.ProcCalColumns[proColumnName];
dt.Columns[proColumnName].SetOrdinal(columnIndex);
}
columnIndex++;
}
//while (dt.Columns.Count > columnIndex) //不用的列全干掉，性能上说不定有奇效:)
//{
// dt.Columns.RemoveAt(columnIndex);
//}
//排序
//string sortview = string.Empty;
string sortview = template.SQLBuilder.Orderby;
if (!string.IsNullOrEmpty(orderBy))
{
sortview = orderBy;
//string sortcolname = orderBy.Split(' ')[0].ToLower().Trim();
//if (dorderby.Contains(sortcolname + " "))
//{
// string[] dorderlist = dorderby.Split(',');
// foreach (string ds in dorderlist)
// {
// if (ds.Contains(sortcolname + " "))
// {
// sortview += orderBy + ",";
// }
// else
// {
// sortview += ds + ",";
// }
// }
//}
//else
//{
// sortview = dorderby + "," + orderBy;
//}
}
//else
//{
// sortview = dorderby;
//}
if (!string.IsNullOrEmpty(sortview))
{
dt.DefaultView.Sort = sortview.TrimEnd(','); //TODO:行汇总排序
}
dt = dt.DefaultView.ToTable();
}
else
{
StringBuilder sb = new StringBuilder();
sb.Append("select ");
sb.Append(template.SQLBuilder.SelectSQL);
sb.Append(" from ");
sb.Append(template.SQLBuilder.TableName);
sb.Append(template.SQLBuilder.DefauleQuery.Replace("[本人]", BaseComponent.GetCurrentUserLoginName()));
if (!string.IsNullOrWhiteSpace(pWhere))
{
sb.Append(" and ");
sb.Append(pWhere);
}
if (!string.IsNullOrWhiteSpace(template.SQLBuilder.Orderby))//TODO:行汇总排序
{
sb.Append(" order by ");
//排序
//string sortview = string.Empty;
string sortview = template.SQLBuilder.Orderby;
if (!string.IsNullOrEmpty(orderBy))
{
sortview = orderBy;
//string sortcolname = orderBy.Split(' ')[0].ToLower().Trim();
//if (dorderby.Contains(sortcolname + " "))
//{
// string[] dorderlist = dorderby.Split(',');
// foreach (string ds in dorderlist)
// {
// if (ds.Contains(sortcolname + " "))
// {
// sortview += orderBy + ",";
// }
// else
// {
// sortview += ds + ",";
// }
// }
//}
//else
//{
// sortview = dorderby + "," + orderBy;
//}
}
//else
//{
// sortview = dorderby;
//}
sb.Append(sortview.TrimEnd(','));
}
dt = dbhelper.ExecuteTable(CommandType.Text, sb.ToString());
BaseComponent.Info("SQL:" + sb.ToString());
}
DataTable dtResult = dt.Copy();
dtResult.Columns.Add("bs_row_num");
dtResult.Columns.Add("bs_is_groupby");
//分组统计
if (template.GroupBy != null && template.GroupBy.Count > 0)
{
//表中汇总
var middleGroup = template.GroupBy.Where(p => p.IsAtLast == false);
foreach (GroupBy gb in middleGroup) //每次循环后 得到分组后的数据
{
DataTable distinct = dtResult.DefaultView.ToTable(true, gb.Columns.Split(','));
DataTable tempResult = dtResult.Clone();
foreach (DataRow dr in distinct.Rows)
{
string select = string.Empty;
foreach (string colName in gb.Columns.Split(','))
{
select += colName + "='" + dr[colName] + "' and ";
}
if (!string.IsNullOrWhiteSpace(select))
{
select = select.Substring(0, select.Length - 5);
}
DataRow[] rows = dtResult.Select(select); //取出一组数据
//temp用来存储筛选出来的数据
DataTable temp = dtResult.Clone();
foreach (DataRow row in rows)
{
temp.Rows.Add(row.ItemArray);
tempResult.Rows.Add(row.ItemArray);
}
DataRow groupDr = tempResult.NewRow();
foreach (string colName in gb.Columns.Split(','))
{
groupDr[colName] = dr[colName];
}
foreach (var groupName in gb.GroupByColumns)
{
string[] gn = groupName.Value.Split(',');
string res = Convert.ToString(temp.Compute(gn[0], "bs_is_groupby is null"));
if (gn[1] == "-1")//自动小数
{
groupDr[groupName.Key] = temp.Compute(gn[0], "bs_is_groupby is null");
}
else
{
double comresult = 0.00;
if (!string.IsNullOrEmpty(res))
{
double.TryParse(res, out comresult);
}
if (comresult != 0.0)
{
groupDr[groupName.Key] = comresult.ToString("f" + gn[1]);
}
else
{
groupDr[groupName.Key] = DBNull.Value;
}
}
}
groupDr["bs_is_groupby"] = "1";
groupDr["bs_row_num"] = "小计（合计）";
tempResult.Rows.Add(groupDr);
}
dtResult = tempResult.Copy();
}
//表尾汇总
var lastGroup = template.GroupBy.Where(p => p.IsAtLast == true);
foreach (GroupBy gb in lastGroup)
{
//DataTable newdt = new DataTable();
//newdt = dtResult.Clone();
//DataRow[] realRows = dtResult.Select("bs_is_groupby is null");
//foreach (DataRow row in realRows)
//{
// newdt.Rows.Add(row.ItemArray);
//}
if (string.IsNullOrEmpty(gb.Columns))
{
DataRow groupDr = dtResult.NewRow();
foreach (var groupName in gb.GroupByColumns)
{
string[] gn = groupName.Value.Split(',');
string res = Convert.ToString(dtResult.Compute(gn[0], "bs_is_groupby is null"));
if (gn[1] == "-1")//自动小数
{
groupDr[groupName.Key] = dtResult.Compute(gn[0], "bs_is_groupby is null");
}
else
{
double comresult = 0.00;
if (!string.IsNullOrEmpty(res))
{
double.TryParse(res, out comresult);
}
if (comresult != 0.0)
{
groupDr[groupName.Key] = comresult.ToString("f" + gn[1]);
}
else
{
groupDr[groupName.Key] = DBNull.Value;
}
}
}
groupDr["bs_is_groupby"] = "1";
groupDr["bs_row_num"] = "总计";
dtResult.Rows.Add(groupDr);
}
else
{
DataTable distinct = dt.DefaultView.ToTable(true, gb.Columns.Split(','));
foreach (DataRow dr in distinct.Rows)
{
string select = string.Empty;
foreach (string colName in gb.Columns.Split(','))
{
select += colName + "='" + dr[colName] + "' and ";
}
if (!string.IsNullOrWhiteSpace(select))
{
select = select.Substring(0, select.Length - 5);
}
DataRow[] rows = dtResult.Select(select); //取出一组数据
//temp用来存储筛选出来的数据
DataTable temp = dtResult.Clone();
foreach (DataRow row in rows)
{
temp.Rows.Add(row.ItemArray);
}
DataRow groupDr = dtResult.NewRow();
foreach (string colName in gb.Columns.Split(','))
{
groupDr[colName] = dr[colName];
}
foreach (var groupName in gb.GroupByColumns)
{
string[] gn = groupName.Value.Split(',');
string res = Convert.ToString(temp.Compute(gn[0], "bs_is_groupby is null"));
if (gn[1] == "-1")//自动小数
{
groupDr[groupName.Key] = temp.Compute(gn[0], "bs_is_groupby is null");
}
else
{
double comresult = 0.00;
if (!string.IsNullOrEmpty(res))
{
double.TryParse(res, out comresult);
}
if (comresult != 0.0)
{
groupDr[groupName.Key] = comresult.ToString("f" + gn[1]);
}
else
{
groupDr[groupName.Key] = DBNull.Value;
}
}
}
groupDr["bs_is_groupby"] = "1";
groupDr["bs_row_num"] = "小计（合计）";
dtResult.Rows.Add(groupDr);
}
}
}
}
int index = 1;
foreach (DataRow dr in dtResult.Rows)
{
if (!string.IsNullOrEmpty(Convert.ToString(dr["bs_is_groupby"])))
{
continue;
}
dr["bs_row_num"] = index.ToString();
index++;
}
return(dtResult);
}