Example #1
0
 //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);
 }
Example #2
0
 //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);
 }
Example #3
0
        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);
        }
Example #4
0
        /// <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;
        }
Example #5
0
        /// <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 "";
        }
Example #6
0
        /// <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;
        }
Example #7
0
        /// <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;
        }
Example #8
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;
        }
Example #9
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 "";
        }
Example #10
0
        /// <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;
        }
Example #11
0
        //單[多]選 筆數
        /// <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;
        }
Example #12
0
        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;
        }
Example #13
0
 /// <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;
 }
Example #14
0
        /// <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;
        }
Example #15
0
        /// <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;
        }
Example #16
0
        /// <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;
        }