//根据菜单的id修改菜单的状态
private void SetMenuState(Bill_SysMenu s, SqlTransaction trans)
{
string UpMenuStateSql = "update bill_sysMenu set menustate=@menustate where menuid=@menuid";
SqlParameter[] patamter = { new SqlParameter("@menustate", s.MenuState),
new SqlParameter("@menuid", s.MenuId) };
DataHelper.ExcuteNonQuery(UpMenuStateSql, trans, patamter, false);
}
/// <summary>
/// 根据配置项名称设置value值
/// </summary>
/// <param name="strKey"></param>
/// <param name="strVal"></param>
/// <returns></returns>
public int SetValueByKey(string strKey, string strVal)
{
string strexitsql = "select count(*) from t_Config where aKey=@key";
SqlParameter[] arr = { new SqlParameter("@key", strKey) };
int icount = DataHelper.GetDataTable(strexitsql, arr, false).Rows.Count;
if (icount > 0)
{
string strUpSql = "update t_Config set avalue=@value where aKey=@key";
SqlParameter[] arrSp = { new SqlParameter("@value", strVal), new SqlParameter("@key", strKey) };
return(DataHelper.ExcuteNonQuery(strUpSql, arrSp, false));
}
else
{
string strAddSql = "insert into t_Config(akey,meaning,avalue,classify) values (@key,@meaning,@avalue,@classify)";
SqlParameter[] arrSp = { new SqlParameter("@key", strKey), new SqlParameter("@meaning", ""), new SqlParameter("@avalue", strVal), new SqlParameter("@classify", "0") };
return(DataHelper.ExcuteNonQuery(strAddSql, arrSp, false));
}
}
public bool AddYscs(string isYear, string isMonth, string tbfs, string nd, string ndStatus)
{
string insert1 = @"insert into bill_SysConfig ([ConfigName]
,[ConfigValue]
,[Memo]
,[nd])values('YearBudget', '" + isYear + "', '1是启用 其他是不启用', '"+ nd + "')";
string insert2 = @"insert into bill_SysConfig ([ConfigName]
,[ConfigValue]
,[Memo]
,[nd])values('MonthOrQuarter', '" + isMonth + "', '1是季度2是月度', '"+ nd + "')";
string insert3 = @"insert into bill_SysConfig ([ConfigName]
,[ConfigValue]
,[Memo]
,[nd])values('ystbfs', '" + tbfs + "', '0是部门汇总是预算分解', '"+ nd + "')";
string insert4 = @"insert into bill_SysConfig ([ConfigName]
,[ConfigValue]
,[Memo]
,[nd])values('ndStatus', '" + ndStatus + "', '年度开关 1 进行中 0 关闭', '"+ nd + "')";
using (SqlConnection conn = new SqlConnection(DataHelper.constr))
{
conn.Open();
SqlTransaction tran = conn.BeginTransaction();
try
{
DataHelper.ExcuteNonQuery(insert1, tran, null, false);
DataHelper.ExcuteNonQuery(insert2, tran, null, false);
DataHelper.ExcuteNonQuery(insert3, tran, null, false);
DataHelper.ExcuteNonQuery(insert4, tran, null, false);
tran.Commit();
return(true);
}
catch
{
tran.Rollback();
throw;
}
}
}
//isYear 是否年度 isMonth 月度还是季度 tbfs填报方式 parList设置日期
public bool SetYscs(string isYear, string isMonth, string tbfs, IList <Models.bill_syspar> parList, IList <Bill_SysMenu> menulist, string nd, string ndStatus)
{
string upnd = "update bill_SysConfig set ConfigValue='" + isYear + "' where ConfigName='YearBudget' and nd='" + nd + "' ";
string upminth = "update bill_SysConfig set ConfigValue='" + isMonth + "' where ConfigName='MonthOrQuarter' and nd='" + nd + "' ";
string uptbfs = "update bill_SysConfig set ConfigValue='" + tbfs + "' where ConfigName='ystbfs' and nd='" + nd + "' ";
string upstatus = "update bill_SysConfig set ConfigValue='" + ndStatus + "' where ConfigName='ndStatus' and nd='" + nd + "' ";
string uppar = "update bill_syspar set parval=@parval where parname=@parname";
using (SqlConnection conn = new SqlConnection(DataHelper.constr))
{
conn.Open();
SqlTransaction tran = conn.BeginTransaction();
try
{
DataHelper.ExcuteNonQuery(upnd, tran, null, false);
DataHelper.ExcuteNonQuery(upminth, tran, null, false);
DataHelper.ExcuteNonQuery(uptbfs, tran, null, false);
DataHelper.ExcuteNonQuery(upstatus, tran, null, false);
foreach (var i in parList)
{
SqlParameter[] paramter = { new SqlParameter("@parval", i.parVal),
new SqlParameter("@parname", i.parname) };
DataHelper.ExcuteNonQuery(uppar, tran, paramter, false);
}
foreach (var s in menulist)
{
SetMenuState(s, tran);
}
tran.Commit();
return(true);
}
catch
{
tran.Rollback();
throw;
}
}
}
/// <summary>
/// 增加子表
/// </summary>
public void AddItem(Sys_Notes model, SqlTransaction tran)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into Sys_Notes(");
strSql.Append("sysCode,note6,note7,note8,note9,note10,note11,note12,note13,note14,note15,beforestr,note16,note17,note18,note19,note20,afterstr,edittype,note1,note2,note3,note4,note5");
strSql.Append(") values (");
strSql.Append("@sysCode,@note6,@note7,@note8,@note9,@note10,@note11,@note12,@note13,@note14,@note15,@beforestr,@note16,@note17,@note18,@note19,@note20,@afterstr,@edittype,@note1,@note2,@note3,@note4,@note5");
strSql.Append(") ");
SqlParameter[] parameters =
{
new SqlParameter("@sysCode", SqlDbType.VarChar, 50),
new SqlParameter("@note6", SqlDbType.VarChar, 100),
new SqlParameter("@note7", SqlDbType.VarChar, 100),
new SqlParameter("@note8", SqlDbType.VarChar, 100),
new SqlParameter("@note9", SqlDbType.VarChar, 100),
new SqlParameter("@note10", SqlDbType.VarChar, 100),
new SqlParameter("@note11", SqlDbType.VarChar, 100),
new SqlParameter("@note12", SqlDbType.VarChar, 100),
new SqlParameter("@note13", SqlDbType.VarChar, 100),
new SqlParameter("@note14", SqlDbType.VarChar, 100),
new SqlParameter("@note15", SqlDbType.VarChar, 100),
new SqlParameter("@beforestr", SqlDbType.Text),
new SqlParameter("@note16", SqlDbType.VarChar, 100),
new SqlParameter("@note17", SqlDbType.VarChar, 100),
new SqlParameter("@note18", SqlDbType.VarChar, 100),
new SqlParameter("@note19", SqlDbType.VarChar, 100),
new SqlParameter("@note20", SqlDbType.VarChar, 100),
new SqlParameter("@afterstr", SqlDbType.Text),
new SqlParameter("@edittype", SqlDbType.VarChar, 50),
new SqlParameter("@note1", SqlDbType.VarChar, 100),
new SqlParameter("@note2", SqlDbType.VarChar, 100),
new SqlParameter("@note3", SqlDbType.VarChar, 100),
new SqlParameter("@note4", SqlDbType.VarChar, 100),
new SqlParameter("@note5", SqlDbType.VarChar, 100)
};
parameters[0].Value = SqlNull(model.sysCode);
parameters[1].Value = SqlNull(model.note6);
parameters[2].Value = SqlNull(model.note7);
parameters[3].Value = SqlNull(model.note8);
parameters[4].Value = SqlNull(model.note9);
parameters[5].Value = SqlNull(model.note10);
parameters[6].Value = SqlNull(model.note11);
parameters[7].Value = SqlNull(model.note12);
parameters[8].Value = SqlNull(model.note13);
parameters[9].Value = SqlNull(model.note14);
parameters[10].Value = SqlNull(model.note15);
parameters[11].Value = SqlNull(model.beforestr);
parameters[12].Value = SqlNull(model.note16);
parameters[13].Value = SqlNull(model.note17);
parameters[14].Value = SqlNull(model.note18);
parameters[15].Value = SqlNull(model.note19);
parameters[16].Value = SqlNull(model.note20);
parameters[17].Value = SqlNull(model.afterstr);
parameters[18].Value = SqlNull(model.edittype);
parameters[19].Value = SqlNull(model.note1);
parameters[20].Value = SqlNull(model.note2);
parameters[21].Value = SqlNull(model.note3);
parameters[22].Value = SqlNull(model.note4);
parameters[23].Value = SqlNull(model.note5);
DataHelper.ExcuteNonQuery(strSql.ToString(), tran, parameters, false);
}
/// <summary>
/// 增加一条主表数据
/// </summary>
public void Add(Sys_Note model, SqlTransaction tran)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into Sys_Note(");
strSql.Append("sysCode,afterstr,opeDiscretion,note1,note2,note3,note4,note5,note6,note7,note8,billcode,note9,note10,note11,note12,note13,note14,note15,note16,note17,note18,billname,note19,note20,usercode,billtype,OperationType,userip,ndate,beforestr");
strSql.Append(") values (");
strSql.Append("@sysCode,@afterstr,@opeDiscretion,@note1,@note2,@note3,@note4,@note5,@note6,@note7,@note8,@billcode,@note9,@note10,@note11,@note12,@note13,@note14,@note15,@note16,@note17,@note18,@billname,@note19,@note20,@usercode,@billtype,@OperationType,@userip,@ndate,@beforestr");
strSql.Append(") ");
SqlParameter[] parameters =
{
new SqlParameter("@sysCode", SqlDbType.VarChar, 50),
new SqlParameter("@afterstr", SqlDbType.Text),
new SqlParameter("@opeDiscretion", SqlDbType.Text),
new SqlParameter("@note1", SqlDbType.VarChar, 100),
new SqlParameter("@note2", SqlDbType.VarChar, 100),
new SqlParameter("@note3", SqlDbType.VarChar, 100),
new SqlParameter("@note4", SqlDbType.VarChar, 100),
new SqlParameter("@note5", SqlDbType.VarChar, 100),
new SqlParameter("@note6", SqlDbType.VarChar, 100),
new SqlParameter("@note7", SqlDbType.VarChar, 100),
new SqlParameter("@note8", SqlDbType.VarChar, 100),
new SqlParameter("@billcode", SqlDbType.VarChar, 200),
new SqlParameter("@note9", SqlDbType.VarChar, 100),
new SqlParameter("@note10", SqlDbType.VarChar, 100),
new SqlParameter("@note11", SqlDbType.VarChar, 100),
new SqlParameter("@note12", SqlDbType.VarChar, 100),
new SqlParameter("@note13", SqlDbType.VarChar, 100),
new SqlParameter("@note14", SqlDbType.VarChar, 100),
new SqlParameter("@note15", SqlDbType.VarChar, 100),
new SqlParameter("@note16", SqlDbType.VarChar, 100),
new SqlParameter("@note17", SqlDbType.VarChar, 100),
new SqlParameter("@note18", SqlDbType.VarChar, 100),
new SqlParameter("@billname", SqlDbType.VarChar, 200),
new SqlParameter("@note19", SqlDbType.VarChar, 100),
new SqlParameter("@note20", SqlDbType.VarChar, 100),
new SqlParameter("@usercode", SqlDbType.VarChar, 100),
new SqlParameter("@billtype", SqlDbType.VarChar, 50),
new SqlParameter("@OperationType", SqlDbType.VarChar, 50),
new SqlParameter("@userip", SqlDbType.VarChar, 200),
new SqlParameter("@ndate", SqlDbType.DateTime),
new SqlParameter("@beforestr", SqlDbType.Text)
};
parameters[0].Value = SqlNull(model.sysCode);
parameters[1].Value = SqlNull(model.afterstr);
parameters[2].Value = SqlNull(model.opeDiscretion);
parameters[3].Value = SqlNull(model.note1);
parameters[4].Value = SqlNull(model.note2);
parameters[5].Value = SqlNull(model.note3);
parameters[6].Value = SqlNull(model.note4);
parameters[7].Value = SqlNull(model.note5);
parameters[8].Value = SqlNull(model.note6);
parameters[9].Value = SqlNull(model.note7);
parameters[10].Value = SqlNull(model.note8);
parameters[11].Value = SqlNull(model.billcode);
parameters[12].Value = SqlNull(model.note9);
parameters[13].Value = SqlNull(model.note10);
parameters[14].Value = SqlNull(model.note11);
parameters[15].Value = SqlNull(model.note12);
parameters[16].Value = SqlNull(model.note13);
parameters[17].Value = SqlNull(model.note14);
parameters[18].Value = SqlNull(model.note15);
parameters[19].Value = SqlNull(model.note16);
parameters[20].Value = SqlNull(model.note17);
parameters[21].Value = SqlNull(model.note18);
parameters[22].Value = SqlNull(model.billname);
parameters[23].Value = SqlNull(model.note19);
parameters[24].Value = SqlNull(model.note20);
parameters[25].Value = SqlNull(model.usercode);
parameters[26].Value = SqlNull(model.billtype);
parameters[27].Value = SqlNull(model.OperationType);
parameters[28].Value = SqlNull(model.userip);
parameters[29].Value = SqlNull(model.ndate);
parameters[30].Value = SqlNull(model.beforestr);
DataHelper.ExcuteNonQuery(strSql.ToString(), tran, parameters, false);
}
