//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;
}