public IList <Bill_Ysmxb> GetYsmxByCode(string billCode)
{
string sql = @"select * from bill_ysmxb
where billCode=@billCode";
SqlParameter[] sps =
{
new SqlParameter("@billCode", billCode)
};
DataTable dt = DataHelper.GetDataTable(sql, sps, false);
IList <Bill_Ysmxb> list = new List <Bill_Ysmxb>();
foreach (DataRow dr in dt.Rows)
{
Bill_Ysmxb ysmx = new Bill_Ysmxb();
ysmx.Gcbh = Convert.ToString(dr["Gcbh"]);
ysmx.YsDept = Convert.ToString(dr["YsDept"]);
ysmx.Ysje = Convert.ToDecimal(dr["Ysje"]);
ysmx.Yskm = Convert.ToString(dr["Yskm"]);
ysmx.BillCode = Convert.ToString(dr["BillCode"]);
ysmx.YsType = Convert.ToString(dr["YsType"]);
list.Add(ysmx);
}
return(list);
}
/// <summary>
/// 增加一条数据
/// </summary>
public void Add(Bill_Ysmxb model, SqlTransaction tran)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into bill_ysmxb(");
strSql.Append("gcbh,billCode,yskm,ysje,ysDept,ysType");
strSql.Append(") values (");
strSql.Append("@gcbh,@billCode,@yskm,@ysje,@ysDept,@ysType");
strSql.Append(") ");
SqlParameter[] parameters =
{
new SqlParameter("@gcbh", SqlDbType.VarChar, 50),
new SqlParameter("@billCode", SqlDbType.VarChar, 50),
new SqlParameter("@yskm", SqlDbType.VarChar, 50),
new SqlParameter("@ysje", SqlDbType.Float, 8),
new SqlParameter("@ysDept", SqlDbType.VarChar, 50),
new SqlParameter("@ysType", SqlDbType.VarChar, 1),
};
parameters[0].Value = SqlNull(model.Gcbh);
parameters[1].Value = SqlNull(model.BillCode);
parameters[2].Value = SqlNull(model.Yskm);
parameters[3].Value = SqlNull(model.Ysje);
parameters[4].Value = SqlNull(model.YsDept);
parameters[5].Value = SqlNull(model.YsType);
DataHelper.ExcuteNonQuery(strSql.ToString(), tran, parameters, false);
}
/// <summary>
/// 根据科目编号获得各月预算
/// </summary>
/// <param name="kmCode"></param>
/// <returns></returns>
public IList <Bill_Ysmxb> GetYsmxByKm(string kmCode, string dept, string gcbh)
{
string sql = @" select gcbh,yskm,ysdept,round(sum(isnull(ysje,0)),2) as ysje
from bill_ysmxb where ysdept=@dept and yskm=@kmCode and yskm in(" + gcbh + @")
group by gcbh,yskm,ysdept order by gcbh ";
SqlParameter[] sps =
{
new SqlParameter("@dept", dept),
new SqlParameter("@kmCode", kmCode)
};
DataTable dt = DataHelper.GetDataTable(sql, sps, false);
IList <Bill_Ysmxb> list = new List <Bill_Ysmxb>();
foreach (DataRow dr in dt.Rows)
{
Bill_Ysmxb ysmx = new Bill_Ysmxb();
ysmx.Gcbh = Convert.ToString(dr["Gcbh"]);
ysmx.YsDept = Convert.ToString(dr["YsDept"]);
ysmx.Ysje = Convert.ToDecimal(dr["Ysje"]);
ysmx.Yskm = Convert.ToString(dr["Yskm"]);
list.Add(ysmx);
}
return(list);
}
/// <summary>
/// 根据过程编号,部门编号获得各科目预算
/// </summary>
/// <param name="gcbh"></param>
/// <param name="depCode"></param>
/// <returns></returns>
public IList <Bill_Ysmxb> GetYsmxByDeptYue(string gcbh, string depCode)
{
string sql = @"select gcbh,yskm,round(sum(isnull(ysje,0)),2) as ysje,ysDept from bill_ysmxb a,bill_main b
where gcbh=@gcbh and ysDept=@depCode and a.billcode=b.billcode and b.stepid='end'
group by gcbh,yskm,ysDept";
SqlParameter[] sps =
{
new SqlParameter("@gcbh", gcbh),
new SqlParameter("@depCode", depCode)
};
DataTable dt = DataHelper.GetDataTable(sql, sps, false);
IList <Bill_Ysmxb> list = new List <Bill_Ysmxb>();
foreach (DataRow dr in dt.Rows)
{
Bill_Ysmxb ysmx = new Bill_Ysmxb();
ysmx.Gcbh = Convert.ToString(dr["Gcbh"]);
ysmx.YsDept = Convert.ToString(dr["YsDept"]);
ysmx.Ysje = Convert.ToDecimal(dr["Ysje"]);
ysmx.Yskm = Convert.ToString(dr["Yskm"]);
list.Add(ysmx);
}
return(list);
}
public void Add(Bill_Ysmxb model)
{
using (SqlConnection conn = new SqlConnection(DataHelper.constr))
{
conn.Open();
SqlTransaction tran = conn.BeginTransaction();
try
{
Delete(model.BillCode, tran);
Add(model, tran);
tran.Commit();
}
catch
{
tran.Rollback();
throw;
}
}
}
/// <summary>
/// 按条件获取一个实例
/// </summary>
/// <param name="tempsql"></param>
/// <param name="sps"></param>
/// <returns></returns>
public IList <Bill_Ysmxb> ListMaker(string tempsql, SqlParameter[] sps)
{
DataTable dt = DataHelper.GetDataTable(tempsql, sps, false);
IList <Bill_Ysmxb> list = new List <Bill_Ysmxb>();
foreach (DataRow dr in dt.Rows)
{
Bill_Ysmxb model = new Bill_Ysmxb();
model.Gcbh = dr["gcbh"].ToString();
model.BillCode = dr["billCode"].ToString();
model.Yskm = dr["yskm"].ToString();
if (!DBNull.Value.Equals(dr["ysje"]))
{
model.Ysje = decimal.Parse(dr["ysje"].ToString());
}
model.YsDept = dr["ysDept"].ToString();
model.YsType = dr["ysType"].ToString();
list.Add(model);
}
return(list);
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public Bill_Ysmxb GetModel(string billcode)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(sql);
strSql.Append(" where billCode=@billCode");
SqlParameter[] parameters =
{
new SqlParameter("@billCode", SqlDbType.Char, 50)
};
parameters[0].Value = billcode;
Bill_Ysmxb model = new Bill_Ysmxb();
using (SqlDataReader dr = DataHelper.GetDataReader(strSql.ToString(), parameters))
{
if (dr.Read())
{
model.Gcbh = dr["gcbh"].ToString();
model.BillCode = dr["billCode"].ToString();
model.Yskm = dr["yskm"].ToString();
if (!DBNull.Value.Equals(dr["ysje"]))
{
model.Ysje = decimal.Parse(dr["ysje"].ToString());
}
model.YsDept = dr["ysDept"].ToString();
model.YsType = dr["ysType"].ToString();
return(model);
}
else
{
return(null);
}
}
}
