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