//string strTbName /// <summary> /// ��l�� /// </summary> public PermissionDao(string FormID, string cnKey) { this.cnKey = cnKey; ia = DBAccessLayer.DBFactory.GetDBAccess(DBAccessLayer.DBAccessType.SQL, cnKey); string strSql = string.Format("select * from Sys_TreeList Where FormID = '{0}'", FormID); Sys.TreeListDao dao = new Sys.TreeListDao(); SysModel = dao.GetModel(FormID); }
//string strTbName /// <summary> /// ��l�� /// </summary> public RoleDao(string FormID, string cnKey) { this.cnKey = cnKey; ia = DBAccessLayer.DBFactory.GetDBAccess(DBAccessLayer.DBAccessType.SQL, cnKey); string strSql = string.Format("select * from Sys_TreeList Where FormID = '{0}'", FormID); Sys.TreeListDao dao = new Sys.TreeListDao(); SysModel = dao.GetModel(FormID); tf = new TableFieldInfo(SysModel.TableName, this.cnKey); }
public CheckDao(string FormID,string cnKey) { ia = DBAccessLayer.DBFactory.GetDBAccess(DBAccessLayer.DBAccessType.SQL, ""); ea = DBAccessLayer.DBFactory.GetDBAccess(DBAccessLayer.DBAccessType.SQL, cnKey); this.cnKey = cnKey; Sys.TreeListDao dao = new Sys.TreeListDao(); SysModel = dao.GetModel(FormID); }
/// <summary> /// 根據sysid,permissionid,获得欄位,组合成语句,并返回所查询的DataSet /// </summary> /// <param name="SysID"></param> /// <param name="PermissionID"></param> /// <returns></returns> public DataSet GetSearchSelectSQL(string FormID, int PageIndex, string strWhere, int PageSize, out int PageCount, out int RecrodCount, out string SelectField) { Sys.TreeListDao dao = new Sys.TreeListDao(); Js.Model.Sys.TreeListInfo SysModel = dao.GetModel(FormID); SelectField = ""; PageCount = 0; RecrodCount = 0; DataTable dt = SearchTable(FormID, true).Tables[0]; if (dt.Rows.Count > 0) { string strSql = ""; for (int i = 0; i < dt.Rows.Count; i++) { string strTmp = ""; strTmp = dt.Rows[i]["FieldName"].ToString().Trim(); strTmp += " as " + dt.Rows[i]["FieldCName"].ToString(); if (i == 0) { strSql = dt.Rows[i]["FieldName"].ToString() + " as ID," + strTmp; SelectField = dt.Rows[i]["FieldName"].ToString(); } else { strSql += "," + strTmp; SelectField += "@|$" + dt.Rows[i]["FieldName"].ToString(); } } string PageWhere = SysModel.strWhere == "" ? "1=1" : SysModel.strWhere; if (!string.IsNullOrEmpty(strWhere)) PageWhere += " and " + strWhere; SqlParameter[] parameter ={ new SqlParameter("@tbname",SqlDbType.NVarChar,100), new SqlParameter("@FieldKey",SqlDbType.NVarChar,100), new SqlParameter("@PageCurrent",SqlDbType.Int,13), new SqlParameter("@PageSize",SqlDbType.Int,13), new SqlParameter("@FieldShow",SqlDbType.NVarChar,4000), new SqlParameter("@FieldOrder",SqlDbType.NVarChar,1000), new SqlParameter("@Where",SqlDbType.NVarChar,4000), new SqlParameter("@PageCount",SqlDbType.Int,13), new SqlParameter("@RecordCount",SqlDbType.Int,13) }; parameter[0].Value = SysModel.TableName; parameter[1].Value = SysModel.KeyField; parameter[2].Value = PageIndex; parameter[3].Value = PageSize; parameter[4].Value = strSql; parameter[5].Value = SysModel.OrderField; parameter[6].Value = PageWhere; parameter[7].Direction = ParameterDirection.Output; parameter[8].Direction = ParameterDirection.Output; DataSet ds = ia.ExecuteDataSetProc("sp_PageView", parameter); if (parameter[7].Value.ToString() != "") PageCount = (int)parameter[7].Value; if (parameter[8].Value.ToString() != "") RecrodCount = (int)parameter[8].Value; DataTable dtNew = new DataTable(); foreach (DataColumn dr in ds.Tables[0].Columns) { if (dr.DataType.ToString() != "System.DateTime") { dtNew.Columns.Add(dr.ColumnName, dr.DataType); } else { dtNew.Columns.Add(dr.ColumnName, typeof(string)); } } foreach (DataRow dr in ds.Tables[0].Rows) { DataRow drNew = dtNew.NewRow(); for (int i = 0; i < dr.ItemArray.Length; i++) { if (dr[i].GetType().ToString() != "System.DateTime") { drNew[i] = dr[i]; } else { drNew[i] = Common.GetDateTimeString((DateTime)dr[i], "yyyy/MM/dd", false); } } dtNew.Rows.Add(drNew); } ds.Tables.RemoveAt(0); ds.Tables.Add(dtNew); return ds; } return null; }
/// <summary> /// 返回沒有沒有資料的表單名稱 /// </summary> /// <returns></returns> public string GetHasNotRecrodName(int SysID, int PermissionID) { Sys.TreeListDao dao = new Sys.TreeListDao(); Js.Model.Sys.TreeListInfo SysModel = dao.GetModel(SysID, PermissionID); string PageWhere = ""; if (SysModel.strWhere.Trim().Length > 0) PageWhere = SysModel.strWhere; else PageWhere = "1=1"; StringBuilder strSql = new StringBuilder(); strSql.AppendFormat("select top 1 {0} from {1} where {2}",SysModel.KeyField,SysModel.TableName,PageWhere); int row = (int)ia.ExecuteScalarSql(strSql.ToString()); if (row == 0) { if (SysModel.BaseName.IndexOf("(") >= 0) { return SysModel.BaseName.Substring(0, SysModel.BaseName.IndexOf("(")); } else { return SysModel.BaseName; } } else return ""; }
/// <summary> /// 獲得欄位長度 /// </summary> /// <param name="SysID"></param> /// <param name="PermissionID"></param> /// <returns></returns> public DataSet GetFieldLength(int SysID, int PermissionID) { Sys.TreeListDao dao = new Sys.TreeListDao(); Js.Model.Sys.TreeListInfo SysModel = dao.GetModel(SysID, PermissionID); StringBuilder strSql = new StringBuilder(); strSql.AppendFormat("select name, (case xtype when 239 then length/2 when 231 then length/2 else length end) as Length, " + "(CASE xtype WHEN 239 THEN 'string' WHEN 231 THEN 'string' WHEN 61 THEN 'datetime' WHEN '108' THEN 'decimal' WHEN 48 THEN 'byte' WHEN 104 THEN 'bool' END) as DataType " + "from sys.syscolumns WHERE id = OBJECT_ID('{0}')",SysModel.TableName); DataSet ds = ia.ExecuteDataSetProc(strSql.ToString()); return ds; }
/// <summary> /// 單據可否被編輯 /// 返回 /// 0:可做任何操作 /// 1:單據時間不在交易時間內 /// 2:單據時間<=關帳日期 /// 3:已複核 /// 4:被用 /// </summary> public int GetBillCanBeEdit(string FormID, string IDValue, object date, object beginDate, object enddate, object closedate) { try { if (date != null) { DateTime _date = (DateTime)date, _begindate, _enddate, _closedate; if (beginDate == null) _begindate = DateTime.Parse("0001/01/01"); else _begindate = (DateTime)beginDate; if (enddate == null) _enddate = DateTime.Parse("0001/01/01"); else _enddate = (DateTime)enddate; if (closedate == null) _closedate = DateTime.Parse("0001/01/01"); else _closedate = (DateTime)closedate; //交易區間 if (!(_date >= _begindate && _date <= _enddate)) return (int)10; //closedate if (_closedate >= _date) return (int)20; } Sys.TreeListDao dao = new Sys.TreeListDao(); Js.Model.Sys.TreeListInfo SysModel = dao.GetModel(FormID); //check string strSql = string.Format("select top 1 * from {0} where {1} = '{2}' and {3}", SysModel.TableName, SysModel.KeyField, IDValue, SysModel.strWhere); DataTable dt = ia.ExecuteDataSetSql(strSql).Tables[0]; if (dt.Rows.Count > 0 && dt.Columns.IndexOf("CheckUserName") != -1 && dt.Rows[0]["CheckUserName"].ToString().Length != 0) return (int)30; //isused if (SysModel.IsUsedView.Trim().Length > 0) { strSql = string.Format("select top 1 ID from {0} where ID='{1}'", SysModel.IsUsedView, IDValue); dt = ia.ExecuteDataSetSql(strSql).Tables[0]; if (dt.Rows.Count > 0) return (int)40; } } catch { } return (int)0; }
/// <summary> /// 單據可否被編輯 /// 返回 /// 0:可做任何操作 /// 1:已複核 /// 2:被用 /// </summary> public int GetBillCanBeEdit(string FormID, string IDValue,string filter) { Sys.TreeListDao dao = new Sys.TreeListDao(); Js.Model.Sys.TreeListInfo SysModel = dao.GetModel(FormID); //check string strSql = string.Format("select top 1 * from {0} where {1} = '{2}' and {3} and {4}", SysModel.TableName, SysModel.KeyField, IDValue, SysModel.strWhere,filter); DataTable dt = ia.ExecuteDataSetSql(strSql).Tables[0]; if (dt.Rows.Count > 0 && dt.Columns.IndexOf("CheckUserName") != -1 && dt.Rows[0]["CheckUserName"].ToString().Length != 0) return 1; //isused if (SysModel.IsUsedView.Trim().Length > 0) { strSql = string.Format("select top 1 ID from {0} where ID='{1}' and {2}", SysModel.IsUsedView, IDValue,filter); dt = ia.ExecuteDataSetSql(strSql).Tables[0]; if (dt.Rows.Count > 0) return 2; } return 0; }
/// <summary> /// 首笔,上笔,下笔,末笔资料 /// </summary> /// <param name="SysID"></param> /// <param name="PermissionID"></param> /// <param name="ListType"></param> /// <param name="strKeyValue"></param> /// <param name="strWhere"></param> /// <returns></returns> public string GetBaseModel(int SysID, int PermissionID, int ListType, ArrayList strKeyValue, string strWhere) { Sys.TreeListDao dao = new Sys.TreeListDao(); Js.Model.Sys.TreeListInfo SysModel = dao.GetModel(SysID, PermissionID); string PageWhere = ""; if (SysModel.strWhere.Trim().Length > 0) PageWhere = SysModel.strWhere; else PageWhere = "1=1"; //排列 string strOrder = ""; //条件 string Where = ""; string FieldOrder = ""; if (string.IsNullOrEmpty(SysModel.OrderField.Trim())) FieldOrder = SysModel.KeyField.Trim().ToLower(); else FieldOrder = SysModel.OrderField.Trim().ToLower(); char[] spliter = new char[1]; spliter[0] = char.Parse(","); string strFieldOrder = FieldOrder.ToLower().Replace("convert(nvarchar(10),", "").Replace(",111)", ""); string[] fieldName = strFieldOrder.ToLower().Split(spliter); string[] orderField = new string[2]; //处理日期转换 if (fieldName.Length == 2) { if (fieldName[0].IndexOf("asc") < 0 && fieldName[0].IndexOf("desc") < 0) { orderField[0] = "convert(nvarchar(10)," + fieldName[0] + ",111) asc"; } else { if (fieldName[0].IndexOf("asc") >= 0) orderField[0] = "convert(nvarchar(10)," + fieldName[0].Replace("asc", "").Trim() + ",111) asc"; else orderField[0] = "convert(nvarchar(10)," + fieldName[0].Replace("desc", "").Trim() + ",111) desc"; } if (fieldName[1].IndexOf("asc") < 0 && fieldName[1].IndexOf("desc") < 0) { orderField[1] = fieldName[1] + " asc "; } } else { if (fieldName[0].IndexOf("asc") < 0 && fieldName[0].IndexOf("desc") < 0) { orderField[0] = fieldName[0] + " asc "; } } for (int i = 0; i < fieldName.Length; i++) { fieldName[i] = fieldName[i].Replace("desc", "").Replace("asc", ""); } string[] fieldNameWhere = fieldName; string strSql = string.Format("select top 1 {0} from {1}", SysModel.KeyField, SysModel.TableName); if (ListType == 0) { strKeyValue[0] = strKeyValue[0].ToString().Replace("'", "''"); if (fieldName.Length == 1) { Where = fieldName[0].Trim() + "=N'" + strKeyValue[0].ToString() + "'"; } else { strKeyValue[1] = strKeyValue[1].ToString().Replace("'", "''"); Where = "DATEDIFF(d," + fieldName[0] + ",'" + strKeyValue[0].ToString().Trim() + "')=0 and " + fieldName[1] + "=N'" + strKeyValue[1].ToString() + "'"; } strOrder = FieldOrder; } if (ListType == 1) { strOrder = FieldOrder; Where = "1=1"; } if (ListType == 2) { //一个排序栏位 if (fieldName.Length == 1) { strKeyValue[0] = strKeyValue[0].ToString().Replace("'", "''"); if (orderField[0].IndexOf("asc") >= 0) { strOrder = orderField[0].Replace("asc", "desc"); Where = fieldName[0] + "<N'" + strKeyValue[0].ToString() + "'"; } else { strOrder = orderField[0].Replace("desc", "asc"); Where = fieldName[0] + ">N'" + strKeyValue[0].ToString() + "'"; } } //两个排序栏位 else { strKeyValue[0] = strKeyValue[0].ToString().Replace("'", "''"); strKeyValue[1] = strKeyValue[1].ToString().Replace("'", "''"); //日期,单号排列 if (orderField[0].IndexOf("asc") >= 0 && orderField[1].IndexOf("asc") >= 0) { strOrder = orderField[0].Replace("asc", "desc,") + orderField[1].Replace("asc", "desc"); Where = "DATEDIFF(d," + fieldName[0] + ",'" + strKeyValue[0].ToString().Trim() + "')=0 and " + fieldName[1] + "<N'" + strKeyValue[1].ToString() + "' or DATEDIFF(d," + fieldName[0] + ",'" + strKeyValue[0].ToString() + "')>0"; } else if (orderField[0].IndexOf("asc") >= 0 && orderField[1].IndexOf("desc") >= 0) { strOrder = orderField[0].Replace("asc", "desc,") + orderField[1].Replace("desc", "asc"); Where = "DATEDIFF(d," + fieldName[0] + ",'" + strKeyValue[0].ToString().Trim() + "')=0 and " + fieldName[1] + ">N'" + strKeyValue[1].ToString() + "' or DATEDIFF(d," + fieldName[0] + ",'" + strKeyValue[0].ToString() + "')>0"; } else if (orderField[0].IndexOf("desc") >= 0 && orderField[1].IndexOf("asc") >= 0) { strOrder = orderField[0].Replace("desc", "asc,") + orderField[1].Replace("asc", "desc"); Where = "DATEDIFF(d," + fieldName[0] + ",'" + strKeyValue[0].ToString().Trim() + "')=0 and " + fieldName[1] + "<N'" + strKeyValue[1].ToString() + "' or DATEDIFF(d," + fieldName[0] + ",'" + strKeyValue[0].ToString() + "')<0"; } else { strOrder = orderField[0].Replace("desc", "asc,") + orderField[1].Replace("desc", "asc"); Where = "DATEDIFF(d," + fieldName[0] + ",'" + strKeyValue[0].ToString().Trim() + "')=0 and " + fieldName[1] + ">N'" + strKeyValue[1].ToString() + "' or DATEDIFF(d," + fieldName[0] + ",'" + strKeyValue[0].ToString() + "')<0"; } } } //下一笔 if (ListType == 3) { //一个排序栏位 strKeyValue[0] = strKeyValue[0].ToString().Replace("'", "''"); if (fieldName.Length == 1) { if (orderField[0].IndexOf("asc") >= 0) { strOrder = orderField[0]; Where = fieldName[0] + ">N'" + strKeyValue[0].ToString() + "'"; } else { strOrder = orderField[0]; Where = fieldName[0] + "<N'" + strKeyValue[0].ToString() + "'"; } } //两个排序栏位 else { strKeyValue[0] = strKeyValue[0].ToString().Replace("'", "''"); strKeyValue[1] = strKeyValue[1].ToString().Replace("'", "''"); //日期,单号排列 if (orderField[0].IndexOf("asc") >= 0 && orderField[1].IndexOf("asc") >= 0) { strOrder = orderField[0].Replace("asc", "asc,") + orderField[1]; Where = "DATEDIFF(d," + fieldName[0] + ",'" + strKeyValue[0].ToString().Trim() + "')=0 and " + fieldName[1] + ">N'" + strKeyValue[1].ToString() + "' or DATEDIFF(d,'" + strKeyValue[0].ToString() + "'," + fieldName[0] + ")>0"; } else if (orderField[0].IndexOf("asc") >= 0 && orderField[1].IndexOf("desc") >= 0) { strOrder = orderField[0].Replace("asc", "asc,") + orderField[1]; Where = "DATEDIFF(d," + fieldName[0] + ",'" + strKeyValue[0].ToString().Trim() + "')=0 and " + fieldName[1] + "<N'" + strKeyValue[1].ToString() + "' or DATEDIFF(d,'" + strKeyValue[0].ToString() + "'," + fieldName[0] + ")>0"; } else if (orderField[0].IndexOf("desc") >= 0 && orderField[1].IndexOf("asc") >= 0) { strOrder = orderField[0].Replace("desc", "desc,") + orderField[1]; Where = "DATEDIFF(d," + fieldName[0] + ",'" + strKeyValue[0].ToString().Trim() + "')=0 and " + fieldName[1] + ">N'" + strKeyValue[1].ToString() + "' or DATEDIFF(d,'" + strKeyValue[0].ToString() + "'," + fieldName[0] + ")<0"; } else { strOrder = orderField[0].Replace("desc", "desc,") + orderField[1]; Where = "DATEDIFF(d," + fieldName[0] + ",'" + strKeyValue[0].ToString().Trim() + "')=0 and " + fieldName[1] + "<N'" + strKeyValue[1].ToString() + "' or DATEDIFF(d,'" + strKeyValue[0].ToString() + "'," + fieldName[0] + ")<0"; } } } if (ListType == 4) { //一个排序栏位 if (fieldName.Length == 1) { if (orderField[0].IndexOf("asc") >= 0) strOrder = orderField[0].Replace("asc", "desc"); else strOrder = orderField[0].Replace("desc", "asc"); } //两个排序栏位 else { //日期,单号排列 if (orderField[0].IndexOf("asc") >= 0 && orderField[1].IndexOf("asc") >= 0) strOrder = orderField[0].Replace("asc", "desc,") + orderField[1].Replace("asc", "desc"); else if (orderField[0].IndexOf("asc") >= 0 && orderField[1].IndexOf("desc") >= 0) strOrder = orderField[0].Replace("asc", "desc,") + orderField[1].Replace("desc", "asc"); else if (orderField[0].IndexOf("desc") >= 0 && orderField[1].IndexOf("asc") >= 0) strOrder = orderField[0].Replace("desc", "asc,") + orderField[1].Replace("asc", "desc"); else strOrder = orderField[0].Replace("desc", "asc,") + orderField[1].Replace("desc", "asc"); } Where = "1=1"; } Where = PageWhere + " and (" + Where + ")"; strSql += " where " + Where + " order by " + strOrder; DataTable dt = ia.ExecuteDataSetSql(strSql).Tables[0]; if (dt.Rows.Count > 0) return dt.Rows[0][0].ToString(); else return ""; }
/// <summary> /// 资料表中,改栏位的所有值(重复只出现一次)。 /// </summary> /// <param name="SysID"></param> /// <param name="PermissionID"></param> /// <param name="FieldName"></param> /// <returns></returns> public DataSet FieldTable(int SysID, int PermissionID, string FieldName) { Sys.TreeListDao dao = new Sys.TreeListDao(); Js.Model.Sys.TreeListInfo SysModel = dao.GetModel(SysID, PermissionID); StringBuilder strSql = new StringBuilder(); if (SysModel.strWhere.Trim().Length > 0) strSql.AppendFormat("select Distinct({0}) from {1} where {2} order by {0}", FieldName, SysModel.TableName, SysModel.strWhere); else strSql.AppendFormat("select Distinct({0}) from {1} Order by {0}", FieldName, SysModel.TableName); DataSet ds = ia.ExecuteDataSetProc(strSql.ToString()); return ds; }
//單[多]選 筆數 /// <summary> /// 當前編號的筆數 /// </summary> /// <param name="SysID"></param> /// <param name="PermissionID"></param> /// <param name="strWhere"></param> /// <param name="CurrentValue"></param> /// <returns></returns> public int CurrentRecord(int SysID, int PermissionID, string strWhere,string CurrentValue,out ArrayList FieldOrderValue) { Sys.TreeListDao dao = new Sys.TreeListDao(); Js.Model.Sys.TreeListInfo SysModel = dao.GetModel(SysID, PermissionID); string PageWhere = ""; if (SysModel.strWhere.Trim().Length > 0) PageWhere = SysModel.strWhere; else PageWhere = "1=1"; if (strWhere != "") PageWhere += " and " + strWhere; FieldOrderValue = new ArrayList(); string FieldOrder = ""; if (string.IsNullOrEmpty(SysModel.OrderField.Trim())) FieldOrder = SysModel.KeyField.Trim(); else FieldOrder = SysModel.OrderField.Trim(); char[] spliter = new char[1]; spliter[0] = char.Parse(","); string strFieldOrder= FieldOrder.ToLower().Replace("convert(nvarchar(10),", "").Replace(",111)",""); string strFieldOrderNosc = strFieldOrder.ToLower().Replace("desc", "").Replace("asc", ""); string[] fieldName = strFieldOrderNosc.Split(spliter); if (fieldName.Length > 0) { for (int i = 0; i < fieldName.Length; i++) { FieldOrderValue.Add(fieldName[i]); } } StringBuilder strSql = new StringBuilder(); strSql.AppendFormat("select RowNumber,{0} from (select {1} as IDTwo,{0},ROW_NUMBER () OVER ( Order By {2}) as RowNumber from {3} where {4}) temp Where Temp.IDTwo=N'{5}'", strFieldOrderNosc,SysModel.KeyField,SysModel.TableName,PageWhere,CurrentValue); DataTable dtValue = ia.ExecuteDataSetSql(strSql.ToString()).Tables[0]; int intReturn = 0; if (dtValue.Rows.Count > 0) { for (int i = 0; i < FieldOrderValue.Count; i++) { if (dtValue.Columns[FieldOrderValue[i].ToString().Trim()].DataType.Name == "DateTime") FieldOrderValue[i] = Common.GetDateTimeString((DateTime)dtValue.Rows[0][FieldOrderValue[i].ToString().Trim()], ""); else FieldOrderValue[i] = dtValue.Rows[0][FieldOrderValue[i].ToString().Trim()].ToString(); } intReturn = int.Parse(dtValue.Rows[0][0].ToString()); } else { for (int i = 0; i < FieldOrderValue.Count; i++) { FieldOrderValue[i] = ""; } intReturn = 0; } return intReturn; }
private string GetIsUsedString(bool blnReturn, string FormID, string CurrentValue) { Sys.TreeListDao dao = new Sys.TreeListDao(); Js.Model.Sys.TreeListInfo SysModel = dao.GetModel(FormID); string[] sViewTableName = new string[2]; sViewTableName = SysModel.IsUsedView.Split(','); string strSql = ""; if (sViewTableName[0] != null && sViewTableName[0].Length > 0) { if (blnReturn) strSql = string.Format("select * from {0} where ID = '{1}'",sViewTableName[0],CurrentValue); else strSql = string.Format("select top 1 ID from {0} where ID = '{1}'", sViewTableName[0], CurrentValue); } return strSql; }
/// <summary> /// 設定當前 SysID PermissionID /// </summary> /// <param name="strFormclass">Form.tostring()</param> /// <param name="SysID">回寫</param> /// <param name="PermissionID">回寫</param> public bool SetCurrentSysIDPermissionID(string strFormclass, ref int SysID, ref int PermissionID) { //初始 SysID = 0; PermissionID = -1; Sys.TreeListDao dao = new Sys.TreeListDao(); Js.Model.Sys.TreeListInfo SysModel = dao.GetModel(strFormclass); if (SysModel.FormID.Trim().Length > 0) { SysID = SysModel.SysID; PermissionID = SysModel.PermissionID; return true; } return false; }
/// <summary> /// 传回PageIndex页的资料,并对资料中的日期欄位转化为字符串形式。 /// </summary> /// <param name="SysID"></param> /// <param name="PermissionID"></param> /// <param name="PageIndex"></param> /// <param name="strWhere"></param> /// <param name="SelectField"></param> /// <returns></returns> public DataSet SelectTable(int SysID, int PermissionID, int PageIndex, string strWhere, int PageSize, out int PageCount, out int RecrodCount,string DateFormat) { Sys.TreeListDao dao = new Sys.TreeListDao(); Js.Model.Sys.TreeListInfo SysModel = dao.GetModel(SysID, PermissionID); PageCount = 0; RecrodCount = 0; string PageWhere = ""; if (SysModel.strWhere.Trim().Length > 0) PageWhere = SysModel.strWhere; else PageWhere = "1=1"; if (strWhere != "") PageWhere += " and " + strWhere; SqlParameter[] parameter ={ new SqlParameter("@tbname",SqlDbType.NVarChar,100), new SqlParameter("@FieldKey",SqlDbType.NVarChar,100), new SqlParameter("@PageCurrent",SqlDbType.Int,13), new SqlParameter("@PageSize",SqlDbType.Int,13), new SqlParameter("@FieldShow",SqlDbType.NVarChar,1000), new SqlParameter("@FieldOrder",SqlDbType.NVarChar,1000), new SqlParameter("@Where",SqlDbType.NVarChar,4000), new SqlParameter("@PageCount",SqlDbType.Int,13), new SqlParameter("@RecordCount",SqlDbType.Int,13) }; parameter[0].Value = SysModel.ViewName; parameter[1].Value = SysModel.KeyField; parameter[2].Value = PageIndex; parameter[3].Value = PageSize; parameter[4].Value = "*"; parameter[5].Value = SysModel.OrderField; parameter[6].Value = PageWhere; parameter[7].Direction = ParameterDirection.Output; parameter[8].Direction = ParameterDirection.Output; DataSet ds = ia.ExecuteDataSetProc("sp_PageView", parameter); if (parameter[7].Value.ToString() != "") PageCount = (int)parameter[7].Value; if (parameter[8].Value.ToString() != "") RecrodCount = (int)parameter[8].Value; DataTable dtNew = new DataTable(); foreach (DataColumn dr in ds.Tables[0].Columns) { if (dr.DataType.ToString() != "System.DateTime") { dtNew.Columns.Add(dr.ColumnName, dr.DataType); } else { dtNew.Columns.Add(dr.ColumnName, typeof(string)); } } foreach (DataRow dr in ds.Tables[0].Rows) { DataRow drNew = dtNew.NewRow(); for (int i = 0; i < dr.ItemArray.Length; i++) { if (dr[i].GetType().ToString() != "System.DateTime") { drNew[i] = dr[i]; } else { drNew[i] = Common.GetDateTimeString((DateTime)dr[i], DateFormat, false); } } dtNew.Rows.Add(drNew); } ds.Tables.RemoveAt(0); ds.Tables.Add(dtNew); return ds; }
/// <summary> /// 传回PageIndex页的资料,自行傳入排序字段。 /// </summary> /// <param name="SysID"></param> /// <param name="PermissionID"></param> /// <param name="PageIndex"></param> /// <param name="strWhere"></param> /// <param name="SelectField"></param> /// <returns></returns> public DataSet SelectTable(int SysID, int PermissionID, int PageIndex, string strWhere, int PageSize, string OrderField,out int PageCount, out int RecrodCount) { Sys.TreeListDao dao = new Sys.TreeListDao(); Js.Model.Sys.TreeListInfo SysModel = dao.GetModel(SysID, PermissionID); PageCount = 0; RecrodCount = 0; string PageWhere = ""; if (SysModel.strWhere.Trim().Length > 0) PageWhere = SysModel.strWhere; else PageWhere = "1=1"; if (strWhere != "") PageWhere += " and " + strWhere; SqlParameter[] parameter ={ new SqlParameter("@tbname",SqlDbType.NVarChar,100), new SqlParameter("@FieldKey",SqlDbType.NVarChar,100), new SqlParameter("@PageCurrent",SqlDbType.Int,13), new SqlParameter("@PageSize",SqlDbType.Int,13), new SqlParameter("@FieldShow",SqlDbType.NVarChar,1000), new SqlParameter("@FieldOrder",SqlDbType.NVarChar,1000), new SqlParameter("@Where",SqlDbType.NVarChar,4000), new SqlParameter("@PageCount",SqlDbType.Int,13), new SqlParameter("@RecordCount",SqlDbType.Int,13) }; parameter[0].Value = SysModel.ViewName; parameter[1].Value = SysModel.KeyField; parameter[2].Value = PageIndex; parameter[3].Value = PageSize; parameter[4].Value = "*"; parameter[5].Value = OrderField; parameter[6].Value = PageWhere; parameter[7].Direction = ParameterDirection.Output; parameter[8].Direction = ParameterDirection.Output; DataSet ds = ia.ExecuteDataSetProc("sp_PageView", parameter); if (parameter[7].Value.ToString() != "") PageCount = (int)parameter[7].Value; if (parameter[8].Value.ToString() != "") RecrodCount = (int)parameter[8].Value; return ds; }
/// <summary> /// 單[多]選 筆數 /// </summary> /// <param name="SysID"></param> /// <param name="PermissionID"></param> /// <param name="strWhere"></param> /// <returns></returns> public int RecordCount(int SysID, int PermissionID, string strWhere) { Sys.TreeListDao dao = new Sys.TreeListDao(); Js.Model.Sys.TreeListInfo SysModel = dao.GetModel(SysID, PermissionID); string PageWhere = ""; if (SysModel.strWhere.Trim().Length > 0) PageWhere = SysModel.strWhere; else PageWhere = "1=1"; if (strWhere != "") PageWhere += " and " + strWhere; StringBuilder strSql = new StringBuilder(); strSql.AppendFormat("select Count(1) from {0} where {1}",SysModel.TableName,PageWhere); int row = (int)ia.ExecuteScalarSql(strSql.ToString()); return row; }