/// <summary>
///根据部门和填报类型获取预算科目
/// </summary>
/// <param name="yskmcode"></param>
/// <returns></returns>
public IList <Bill_Yskm> GetYskmByDeptTblx(string depcode, string tblx, string nd, string yskmType)
{
if (new Dal.Bills.SysconfigDal().GetsysConfigBynd(nd)["ystbfs"] == "0") //0是部门汇总
{
string sql = " select * from Bill_Yskm where yskmcode in (select yskmcode from bill_yskm_dept where deptcode=@deptcode) and tblx=@tblx and yskmMc not like '%退费%' "; // and isnull(iszyys,'1')='1' and yskmBm not like '0234%'
if (!string.IsNullOrEmpty(yskmType))
{
sql += " and dydj='" + yskmType + "'";
}
SqlParameter[] sp = { new SqlParameter("@deptcode", depcode),
new SqlParameter("@tblx", tblx) };
return(ListMaker(sql, sp));
}
else//1是预算分解
{
if (tblx == "02")
{
return(null);
}
else
{
bool boIsgkfj = new Dal.ConfigDal().GetValueByKey("UseGKFJ").Equals("1") ? true : false;
if (boIsgkfj)
{
string cxsql = @"select * from bill_yskm where yskmcode in (select distinct kmcode from bill_ys_xmfjbm where deptcode = @deptcode and by3 = '2' and left(procode,4) = @nd)
and yskmcode not in (select distinct yskmcode from bill_yskm_gkdept gkdept inner join bill_ys_xmfjbm xmfjbm on gkdept.yskmcode=xmfjbm.kmcode where xmfjbm.procode=@nd
and xmfjbm.deptcode=@deptcode and gkdept.deptcode=@deptcode)";//and isnull(iszyys,'1')='1'
if (!string.IsNullOrEmpty(yskmType))
{
cxsql += " and dydj='" + yskmType + "'";
}
SqlParameter[] paramter = { new SqlParameter("@deptcode", depcode),
new SqlParameter("@nd", nd) };
return(ListMaker(cxsql, paramter));
}
else
{
string cxsql = @"select * from bill_yskm where yskmcode in (select yskmcode from bill_yskm_dept where deptcode = @deptcode ) ";// and isnull(iszyys,'1')='1' and yskmMc not like '%退费% 大智专用
if (!string.IsNullOrEmpty(yskmType))
{
cxsql += " and dydj='" + yskmType + "'";
}
SqlParameter[] paramter = { new SqlParameter("@deptcode", depcode) };
return(ListMaker(cxsql, paramter));
}
}
}
}
/// <summary>
/// 根据部门编号获得该部门可以填报/报销的预算科目
/// </summary>
/// <param name="depcode">部门编号</param>
/// <param name="strdydjcode">对应单据编号 01 收入类单据 02 报销单 03固定资产购置 04往来费用</param>
/// <returns></returns>
public IList <Bill_Yskm> GetGkYskmByDep(string depcode, string strdydjcode)
{
string sql = "";
bool boIsGkfj = new Dal.ConfigDal().GetValueByKey("UseGKFJ").Equals("1") ? true : false;
if (boIsGkfj)
{
sql = " select * from Bill_Yskm where yskmcode in (select yskmcode from bill_yskm_gkdept where deptcode=@deptcode) and gkfy='1' and kmStatus='1' and (dydj=@dydj or dydj='04') ";
}
else
{
sql = " select * from Bill_Yskm where yskmcode in (select yskmcode from bill_yskm_dept where deptcode=@deptcode) and gkfy='1' and kmStatus='1' and (dydj=@dydj or dydj='04') ";
}
SqlParameter[] sp = { new SqlParameter("@deptcode", depcode), new SqlParameter("@dydj", strdydjcode) };
return(ListMaker(sql, sp));
}
public IList <Bill_Ysmxb> InsetNdysmx(string nd, string deptcode, string gcbh, string billcode)
{
bool boIsGkfj = new Dal.ConfigDal().GetValueByKey("UseGKFJ").Equals("1") ? true : false;
string cxsql = "";
if (boIsGkfj)//and kmcode not in (select distinct yskmcode from bill_yskm_gkdept)
{
cxsql = @"insert into bill_ysmxb ( gcbh , billcode , yskm , ysje , ysdept , ystype )
select '" + gcbh + "' , '" + billcode + "', kmcode as yskmcode , je , '" + deptcode + @"' , '1'
from (select (je-isnull(ysje,0)) as ncys,* from (select * from bill_ys_xmfjbm where procode=@nd and by3 = '2'
and deptcode=@deptcode
) xmfjbm left join (select * from bill_ysmxb where ystype='5' and gcbh=@gcbh ) mxb on mxb.yskm=xmfjbm.kmcode and left(mxb.gcbh,4)=xmfjbm.procode and mxb.ysdept=xmfjbm.deptcode) a ";
}
else
{
cxsql = @"insert into bill_ysmxb ( gcbh , billcode , yskm , ysje , ysdept , ystype )
select '" + gcbh + "' , '" + billcode + "', kmcode as yskmcode ,a.ncys as je , '" + deptcode + @"' , '1'
from
(select (je-isnull(ysje,0)) as ncys,* from (select * from bill_ys_xmfjbm where procode=@nd and by3 = '2'
and deptcode=@deptcode
) xmfjbm left join (select SUM(ysje) as ysje,gcbh,ysDept,yskm from bill_ysmxb where ystype='5' and gcbh=@gcbh group by gcbh,ysDept,yskm) mxb on mxb.yskm=xmfjbm.kmcode and left(mxb.gcbh,4)=xmfjbm.procode and mxb.ysdept=xmfjbm.deptcode) a
" ;
}
SqlParameter[] paramter = { new SqlParameter("@deptcode", deptcode), new SqlParameter("@gcbh", nd + "0001"), new SqlParameter("@nd", nd) };
string cxjesql = "select * from bill_ysmxb where gcbh = @gcbh and billcode=@billcode ";
SqlParameter[] cxparamter = { new SqlParameter("@gcbh", gcbh),
new SqlParameter("@billcode", billcode) };
using (SqlConnection conn = new SqlConnection(DataHelper.constr))
{
conn.Open();
SqlTransaction tran = conn.BeginTransaction();
try
{
DataHelper.ExcuteNonQuery(cxsql, tran, paramter, false);
tran.Commit();
return(ListMaker(cxjesql, cxparamter));
}
catch
{
tran.Rollback();
throw;
}
}
}
