//
// GET: /DB/
/// <summary>
/// 表列表
/// </summary>
/// <returns></returns>
public ActionResult Index()
{
DataTable dt = DBManage.SqlDbHelper.ExecuteDataTable("select * from db_talbe_list order by tableName");
string Json = DataTableConvertJson.DataTable2Json(dt);
ViewBag.PageData = Json;
return(View());
}
/// <summary>
/// 获取查询人员
/// </summary>
private string GetSearchedEmps()
{
String curUserNo = BP.Web.WebUser.No;
String deptId = getUTF8ToString("DeptId");
bool searchChildDept = Convert.ToBoolean(getUTF8ToString("SearchChild"));
String name = getUTF8ToString("KeyWord");
string sql = "";
if (searchChildDept)
{
deptId = GetDeptAndChild(deptId);
}
else
{
deptId = "'" + deptId + "'";
}
if (BP.WF.Glo.OSModel == BP.Sys.OSModel.OneMore)
{
string filter_dept = deptId == "0" ? String.Empty : String.Format(" and Port_Emp.No in (Select FK_Emp from Port_DeptEmp where FK_Dept in ({0}))", deptId);
string filter_name = String.IsNullOrEmpty(name) ? String.Empty : String.Format(" and Port_Emp.Name+','+Port_Emp.NO like '%{0}%'", name);
if (BP.Sys.SystemConfig.AppCenterDBType == BP.DA.DBType.MySQL)
{
filter_name = String.IsNullOrEmpty(name) ? String.Empty : String.Format(" and CONCAT(Port_Emp.Name,',',Port_Emp.NO) like '%{0}%'", name);
}
sql = String.Format("select Port_Emp.*,Port_Dept.Name as DeptName from Port_Emp,Port_Dept where Port_Emp.FK_Dept = Port_Dept.No {0}{1}", filter_dept, filter_name);
}
else
{
string filter_dept = deptId == "0" ? String.Empty : String.Format(" and Port_Emp.No in (Select FK_Emp from Port_EmpDept where FK_Dept in ({0}))", deptId);
string filter_name = String.IsNullOrEmpty(name) ? String.Empty : String.Format(" and Port_Emp.Name+','+Port_Emp.NO like '%{0}%'", name);
if (BP.Sys.SystemConfig.AppCenterDBType == BP.DA.DBType.MySQL)
{
filter_name = String.IsNullOrEmpty(name) ? String.Empty : String.Format(" and CONCAT(Port_Emp.Name,',',Port_Emp.NO) like '%{0}%'", name);
}
sql = String.Format("select Port_Emp.*,Port_Dept.Name as DeptName from Port_Emp,Port_Dept where Port_Emp.FK_Dept = Port_Dept.No {0}{1}", filter_dept, filter_name);
}
DataTable dt = DBAccess.RunSQLReturnTable(sql);
return(DataTableConvertJson.DataTable2Json(ColumnName2UpperCase(dt)));
}
/// <summary>
/// 以下算法只包含 oracle mysql sqlserver 三种类型的数据库 qin
/// </summary>
/// <param name="dataSource">表名</param>
/// <param name="pageNumber">当前页</param>
/// <param name="pageSize">当前页数据条数</param>
/// <param name="key">计算总行数需要</param>
/// <param name="orderKey">排序字段</param>
/// <returns></returns>
public string DBPaging(string dataSource, int pageNumber, int pageSize, string key, string orderKey)
{
string sql = "";
string orderByStr = "";
if (!string.IsNullOrEmpty(orderKey))
{
orderByStr = " ORDER BY " + orderKey;
}
switch (DBAccess.AppCenterDBType)
{
case DBType.Oracle:
int beginIndex = (pageNumber - 1) * pageSize + 1;
int endIndex = pageNumber * pageSize;
sql = "SELECT * FROM ( SELECT A.*, ROWNUM RN " +
"FROM (SELECT * FROM " + dataSource + orderByStr + ") A WHERE ROWNUM <= " + endIndex + " ) WHERE RN >=" + beginIndex;
break;
case DBType.MSSQL:
sql = "SELECT TOP " + pageSize + " * FROM " + dataSource + " WHERE " + key + " NOT IN ("
+ "SELECT TOP (" + pageSize + "*(" + pageNumber + "-1)) " + key + " FROM " + dataSource + " )" + orderByStr;
break;
case DBType.MySQL:
pageNumber -= 1;
sql = "select * from " + dataSource + orderByStr + " limit " + pageNumber + "," + pageSize;
break;
default:
throw new Exception("暂不支持您的数据库类型.");
}
DataTable DTable = DBAccess.RunSQLReturnTable(sql);
int totalCount = DBAccess.RunSQLReturnCOUNT("select " + key + " from " + dataSource);
return(DataTableConvertJson.DataTable2Json(DTable, totalCount));
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public string loadData()
{
string FK_Flow = getUTF8ToString("FK_Flow");
string rpt = "ND" + int.Parse(FK_Flow) + "Rpt";
DataTable rptDt = DBAccess.GetTableSchema(rpt);
rptDt.Columns.Add("");
MapAttrs attrs = new MapAttrs(rpt);
DataTable dt = new DataTable();
dt.Columns.Add("ZD", typeof(string));
dt.Columns.Add("ZDMC", typeof(string));
dt.Columns.Add("LX", typeof(string));
foreach (MapAttr attr in attrs)
{
dt.Rows.Add(attr.Name, attr.KeyOfEn, attr.MyDataTypeStr);
}
return(DataTableConvertJson.DataTable2Json(dt, dt.Rows.Count));
}
