customform.cs

最好用的站点内容管理系统 全部源代码都有

                string tabnm = string.Empty;
                string fldnm = string.Empty;
                int fm = 0;
                int sn = 0;
                string Sql = "select a.formtablename,b.fieldname,b.formid,b.seriesnumber from " + Pre + "customform a ";
                Sql += "inner join " + Pre + "customform_item b on a.id=b.formid where b.id=" + itemid;
                IDataReader rd = DbHelper.ExecuteReader(cn, CommandType.Text, Sql, null);
                if (rd.Read())
                {
                    tabnm = rd.GetString(0);
                    fldnm = rd.GetString(1);
                    fm = rd.GetInt32(2);
                    sn = rd.GetInt32(3);
                }
                rd.Close();
                if (tabnm == string.Empty || fldnm == string.Empty)
                    throw new Exception("没有找到相关的数据表或字段");
                SqlTransaction tran = cn.BeginTransaction();
                try
                {
                    Sql = "ALTER TABLE [" + tabnm + "] DROP COLUMN " + fldnm + "";
                    DbHelper.ExecuteNonQuery(tran, CommandType.Text, Sql, null);
                    Sql = "delete from " + Pre + "customform_item where id=" + itemid;
                    DbHelper.ExecuteNonQuery(tran, CommandType.Text, Sql, null);
                    Sql = "update " + Pre + "customform_item set seriesnumber=seriesnumber-1 where formid=" + fm + " and seriesnumber>" + sn;
                    DbHelper.ExecuteNonQuery(tran, CommandType.Text, Sql, null);
                    tran.Commit();
                }
                catch
                {
                    try
                    {
                        tran.Rollback();
                    }
                    catch
                    { }
                    throw;
                }
            }
            catch
            {
                throw;
            }
            finally
            {
                if (cn.State == ConnectionState.Open)
                    cn.Close();
            }
        }
        IList<CustomFormItemInfo> ICustomForm.GetAllInfo(int formid, out CustomFormInfo FormInfo)
        {
            SqlConnection cn = new SqlConnection(DBConfig.CmsConString);
            cn.Open();
            try
            {
                FormInfo = GetInfo(cn, formid);
                IList<CustomFormItemInfo> list = new List<CustomFormItemInfo>();
                string Sql = "select * from " + Pre + "customform_item where islock=0 and formid=" + formid + " order by seriesnumber asc";
                IDataReader rd = DbHelper.ExecuteReader(cn, CommandType.Text, Sql, null);
                while (rd.Read())
                {
                    CustomFormItemInfo info = new CustomFormItemInfo();
                    info.id = Convert.ToInt32(rd["id"]);
                    info.formid = Convert.ToInt32(rd["formid"]);
                    info.seriesnumber = Convert.ToInt32(rd["seriesnumber"]);
                    info.fieldname = rd["fieldname"].ToString();
                    info.itemname = rd["itemname"].ToString();
                    info.itemtype = (EnumCstmFrmItemType)Enum.Parse(typeof(EnumCstmFrmItemType), rd["itemtype"].ToString());
                    if (rd["defaultvalue"] != DBNull.Value) info.defaultvalue = rd["defaultvalue"].ToString();
                    info.isnotnull = Convert.ToBoolean(rd["isnotnull"].ToString());
                    if (rd["itemsize"] != DBNull.Value) info.itemsize = Convert.ToInt32(rd["itemsize"].ToString());
                    info.islock = Convert.ToBoolean(rd["islock"].ToString());
                    if (rd["prompt"] != DBNull.Value) info.prompt = rd["prompt"].ToString();
                    if (rd["selectitem"] != DBNull.Value) info.selectitem = rd["selectitem"].ToString();
                    list.Add(info);
                }
                rd.Close();
                return list;
            }
            catch
            {
                throw;
            }
            finally
            {
                if (cn.State == ConnectionState.Open)
                    cn.Close();
            }
        }
        void ICustomForm.AddRecord(int formid, SQLConditionInfo[] data)
        {
            SqlConnection cn = new SqlConnection(DBConfig.CmsConString);
            cn.Open();
            try
            {
                string SqlF = string.Empty;
                string SqlV = string.Empty;
                SqlParameter[] Param = new SqlParameter[data.Length + 4];
                #region 用户相关信息
                Param[0] = new SqlParameter("@usernum", SqlDbType.NVarChar, 50);
                Param[0].Value = DBNull.Value;
                Param[1] = new SqlParameter("@username", SqlDbType.NVarChar, 50);
                Param[1].Value = DBNull.Value;
                Param[2] = new SqlParameter("@submit_ip", SqlDbType.NVarChar, 15);
                Param[2].Value = NetCMS.Common.Public.getUserIP();
                Param[3] = new SqlParameter("@submit_time", SqlDbType.DateTime);
                Param[3].Value = DateTime.Now;
                #endregion
                CustomFormInfo FormInfo = GetInfo(cn, formid);
                if (FormInfo.isdelpoint != 0)
                {
                    if (Global.Current.IsTimeout())
                        throw new Exception("您还未登录,请先登录才能提交表单");
                    if (FormInfo.submitonce)
                    {
                        string SqlTimes = "select count(id) from " + FormInfo.formtablename + " where usernum='" + Global.Current.UserNum + "'";
                        int times = Convert.ToInt32(DbHelper.ExecuteScalar(cn, CommandType.Text, SqlTimes, null));
                        if (times > 1)
                            throw new Exception("该表单只允许每个会员提交一次,您已经提交过了。");
                    }
                    Param[0].Value = Global.Current.UserNum;
                    Param[1].Value = Global.Current.UserName;
                    if (FormInfo.ipoint > 0 || FormInfo.gpoint > 0)
                    {
                        bool bread = false;
                        int ipnt = 0;
                        int gpnt = 0;
                        string grp = string.Empty;
                        string SqlUser = "select iPoint,gPoint,UserGroupNumber from " + Pre + "sys_User where UserNum='" + Global.Current.UserNum + "'";
                        IDataReader rd = DbHelper.ExecuteReader(cn, CommandType.Text, SqlUser, null);
                        if (rd.Read())
                        {
                            ipnt = rd.GetInt32(0);
                            gpnt = rd.GetInt32(1);
                            grp = rd.GetString(2);
                            bread = true;
                        }
                        rd.Close();
                        if (!bread)
                        {
                            throw new Exception("该用户的相关信息未找到");
                        }
                        switch (FormInfo.isdelpoint)
                        {
                            case 1:
                                if (gpnt < FormInfo.gpoint)
                                    throw new Exception("提交需要扣除" + FormInfo.gpoint + "金币,您的金币不足");
                                break;
                            case 2:
                                if (ipnt < FormInfo.ipoint)
                                    throw new Exception("提交需要扣除" + FormInfo.ipoint + "点数,您的点数不足");
                                break;
                            case 3:
                                if (gpnt < FormInfo.gpoint || ipnt < FormInfo.ipoint)
                                    throw new Exception("提交需要扣除金币和点数,您的金币或点数不足");
                                break;
                            case 4:
                                if (gpnt < FormInfo.gpoint)
                                    throw new Exception("提交需要达到" + FormInfo.gpoint + "金币,您的金币不足");
                                break;
                            case 5:
                                if (ipnt < FormInfo.ipoint)
                                    throw new Exception("提交需要达到" + FormInfo.ipoint + "点数,您的点数不足");
                                break;
                            case 6:
                                if (gpnt < FormInfo.gpoint || ipnt < FormInfo.ipoint)
                                    throw new Exception("提交需要达到一定的金币和点数,您的金币或点数不足");
                                break;
                        }
                        if (FormInfo.groupnumber != string.Empty && FormInfo.groupnumber.IndexOf(grp + ",") < 0)
                        {
                            throw new Exception("只有指定的会员组才能提交,您不属于该会员组");
                        }
                    }
                }
                for (int i = 0; i < data.Length; i++)
                {
                    SQLConditionInfo info = data[i];
                    if (i > 0)
                    {
                        SqlF += ",";
                        SqlV += ",";
                    }
                    SqlF += info.name;
                    SqlV += "@" + info.name;
                    Param[i + 4] = new SqlParameter("@" + info.name, info.value);
                }
                SqlTransaction tran = cn.BeginTransaction();
                try
                {
                    string Sql = "insert into [" + FormInfo.formtablename + "] (usernum,username,submit_ip,submit_time," + SqlF + ")";
                    Sql += " values (@usernum,@username,@submit_ip,@submit_time," + SqlV + ")";
                    DbHelper.ExecuteNonQuery(tran, CommandType.Text, Sql, Param);
                    if ((FormInfo.isdelpoint == 1 || FormInfo.isdelpoint == 2 || FormInfo.isdelpoint == 3)
                        && (FormInfo.ipoint > 0 || FormInfo.gpoint > 0))
                    {
                        if (FormInfo.isdelpoint == 1)
                            Sql = "update " + Pre + "sys_User set gPoint=gPoint-" + FormInfo.gpoint + " where UserNum='" + Global.Current.UserNum + "'";
                        else if (FormInfo.isdelpoint == 2)
                            Sql = "update " + Pre + "sys_User set iPoint=iPoint-" + FormInfo.ipoint + " where UserNum='" + Global.Current.UserNum + "'";
                        else if (FormInfo.isdelpoint == 3)
                            Sql = "update " + Pre + "sys_User set gPoint=gPoint-" + FormInfo.gpoint + ",iPoint=iPoint-" + FormInfo.ipoint + " where UserNum='" + Global.Current.UserNum + "'";
                        DbHelper.ExecuteNonQuery(tran, CommandType.Text, Sql, null);
                    }
                    tran.Commit();
                }
                catch
                {
                    tran.Rollback();
                    throw;
                }
            }
            catch
            {
                throw;
            }
            finally
            {
                if (cn.State == ConnectionState.Open)
                    cn.Close();
            }
        }
        DataTable ICustomForm.GetSubmitData(int formid, out string formname, out string tablename)
        {
            SqlConnection cn = new SqlConnection(DBConfig.CmsConString);
            cn.Open();
            try
            {
                CustomFormInfo FormInfo = GetInfo(cn, formid);
                string GSql = "select itemname from " + Pre + "Customform_item where fieldname='" + FormInfo.formname + "'";
                string gCName = Convert.ToString(DbHelper.ExecuteScalar(CommandType.Text, GSql, null));
                formname = FormInfo.formname;
                tablename = FormInfo.formtablename;
                return DbHelper.ExecuteTable(cn, CommandType.Text, "select * from " + tablename, null);
            }
            finally
            {
                if (cn.State == ConnectionState.Open)
                    cn.Close();
            }
        }
        void ICustomForm.TruncateTable(int formid)
        {
            SqlConnection cn = new SqlConnection(DBConfig.CmsConString);
            cn.Open();
            try
            {
                CustomFormInfo FormInfo = GetInfo(cn, formid);
                string tablename = FormInfo.formtablename;
                DbHelper.ExecuteTable(cn, CommandType.Text, "TRUNCATE TABLE " + tablename, null);
            }
            finally
            {
                if (cn.State == ConnectionState.Open)
                    cn.Close();
            }
        }
    }
}