/// <summary>
/// 根据部门编号和年度查找项目
/// </summary>
/// <param name="deptCode">部门编号</param>
/// <returns></returns>
public IList <bill_xm_dept_nd> GetXmByDepNd(string deptCode, string nd)
{
string sql = " select a.xmCode,a.xmDept,a.je,a.isCtrl,a.nd,a.status,(select sjXm from bill_xm where xmCode=a.xmCode and xmDept=a.xmDept) as sjXm ,(select xmName from bill_xm where xmCode=a.xmCode and xmDept=a.xmDept) as XmName from bill_xm_dept_nd a where a.status='1'and a.nd=@nd and (a.xmDept=@xmdept) union select a.xmCode,a.xmDept,a.je,a.isCtrl,a.nd,a.status,(select sjXm from bill_xm where xmCode=a.xmCode and xmDept=a.xmDept) as sjXm ,(select xmName from bill_xm where xmCode=a.xmCode and xmDept=a.xmDept) as XmName from bill_xm_dept_nd a where a.status='1' and a.nd=@nd and a.xmDept=(select top 1 deptCode from bill_departments where deptCode=@xmdept and isnull(deptStatus,'1')!='0') ";
SqlParameter[] sps = { new SqlParameter("@nd", nd),
new SqlParameter("@xmdept", deptCode) };
DataTable dt = DataHelper.GetDataTable(sql, sps, false);
IList <bill_xm_dept_nd> list = new List <bill_xm_dept_nd>();
foreach (DataRow dr in dt.Rows)
{
bill_xm_dept_nd dic = new bill_xm_dept_nd();
dic.xmCode = Convert.ToString(dr["xmCode"]);
dic.xmDept = Convert.ToString(dr["xmDept"]);
dic.nd = Convert.ToString(dr["nd"]);
dic.je = Convert.ToDecimal(dr["je"]);
dic.isCtrl = Convert.ToString(dr["isCtrl"]);
dic.SjXm = Convert.ToString(dr["SjXm"]);
dic.XmName = Convert.ToString(dr["XmName"]);
dic.status = Convert.ToString(dr["status"]);
list.Add(dic);
}
return(list);
}
/// <summary>
/// 通过查询语句和参数返回对象List 由于不允许直接传入语句查询 所以要求该方法一定为private
/// </summary>
private IList <bill_xm_dept_nd> ListMaker(string tempsql, SqlParameter[] sps)
{
DataTable dt = DataHelper.GetDataTable(tempsql, sps, false);
IList <bill_xm_dept_nd> list = new List <bill_xm_dept_nd>();
foreach (DataRow dr in dt.Rows)
{
bill_xm_dept_nd model = new bill_xm_dept_nd();
model.xmCode = dr["xmCode"].ToString();
model.note3 = dr["note3"].ToString();
model.note4 = dr["note4"].ToString();
model.note5 = dr["note5"].ToString();
model.note6 = dr["note6"].ToString();
model.note7 = dr["note7"].ToString();
model.xmDept = dr["xmDept"].ToString();
if (!DBNull.Value.Equals(dr["je"]))
{
model.je = decimal.Parse(dr["je"].ToString());
}
model.isCtrl = dr["isCtrl"].ToString();
model.nd = dr["nd"].ToString();
model.status = dr["status"].ToString();
model.note0 = dr["note0"].ToString();
model.note1 = dr["note1"].ToString();
model.note2 = dr["note2"].ToString();
list.Add(model);
}
return(list);
}
/// <summary>
/// 如果年度的数据不存在2 就先把数据插入到数据库
/// </summary>
/// <param name="nd"></param>
/// <param name="deptcode"></param>
/// <returns></returns>
public void Insert(string nd, string deptcode, string xm)
{
IList <bill_xm_dept_nd> list = new List <bill_xm_dept_nd>();
bill_xm_dept_nd temp = new bill_xm_dept_nd();
temp.nd = nd;
temp.xmDept = deptcode;
temp.xmCode = Convert.ToString(xm);
temp.je = Convert.ToDecimal(0.00);
temp.status = "0";
temp.isCtrl = "0";
list.Add(temp);
this.InsertList(list);
}
/// <summary>
/// 如果年度的数据不存在2 就先把数据插入到数据库
/// </summary>
/// <param name="nd"></param>
/// <param name="deptcode"></param>
/// <returns></returns>
public void NullListMaker(string nd, string deptcode)
{
string csql = "select * from bill_xm where xmDept = '" + deptcode + "' and xmStatus='1'";
IList <bill_xm_dept_nd> list = new List <bill_xm_dept_nd>();
//获取部门的项目
DataTable sdt = DataHelper.GetDataTable(csql, null, false);
//项目数据插入到表 在查询出结果返回
for (int i = 0; i < sdt.Rows.Count; i++)
{
bill_xm_dept_nd temp = new bill_xm_dept_nd();
temp.nd = nd;
temp.xmDept = deptcode;
temp.xmCode = Convert.ToString(sdt.Rows[i]["xmCode"]);
temp.je = Convert.ToDecimal(0.00);
temp.status = "0";
temp.isCtrl = "0";
list.Add(temp);
}
//return null;
this.InsertList(list);
}
