/// <summary>
/// 所有本年度项目资金使用情况
/// </summary>
/// <param name="xmxx">项目信息实体类</param>
/// <param name="recordCount">总记录数</param>
/// <returns>数据集</returns>
public DataTable GetXmZjQk(Xm_Xmxx xmxx, ref int recordCount)
{
StringBuilder sbSql = new StringBuilder();
//劉宏哲修改。
sbSql.AppendLine(@"select *
from xm_xmxx x
left join (select z.itemcode zitemcode,z.zjze
from xm_xmzj z
where z.rowid in
(select max(z.rowid) from xm_xmzj z group by z.itemcode)) z
on x.itemcode = z.zitemcode
left join (select b.itemcode bitemcode, sum(b.bfje) bfje
from xm_ss_zjbf b
group by b.itemcode) b
on x.itemcode = b.bitemcode
left join company c
on x.ccode = c.ccode
where x.itemstate != 0
and x.wfstate != 2
and x.itemcode like '{0}%'");
if (!string.IsNullOrEmpty(xmxx.Xmnd))
{
sbSql.AppendLine("and x.xmnd = '{1}'");
}
if (!string.IsNullOrEmpty(xmxx.Zjnd))
{
sbSql.AppendLine("and x.zjnd = '{2}'");
}
sbSql.AppendLine("order by x.itemcode");
// sbSql.AppendLine(@"with a as
// (select j.itemcode, max(j.stage) stage
// from XM_XMZJ j, xm_xmxx x
// where j.itemcode = x.itemcode
// and x.zjpfsj is not null
// and substr(j.itemcode, 0, 6) like '{0}%'
// and x.itemstate!=0 and x.wfstate!=2 and x.wfstate!=3");
// if (!string.IsNullOrEmpty(xmxx.Xmnd))
// {
// sbSql.AppendLine("and x.xmnd = '{2}'");
// }
// if (!string.IsNullOrEmpty(xmxx.Zjnd))
// {
// sbSql.AppendLine("and x.zjnd = '{3}'");
// }
// sbSql.AppendLine(@"group by j.itemcode),
// b as
// (select j.itemcode, max(j.stage) stage, max(j.xh) xh
// from xm_xmzj j, A
// where j.itemcode = A.itemcode
// and j.stage = A.stage
// group by j.itemcode)
//
// select x.itemcode,x.itemname,x.ccode,x.lxsj,x.itemstage,j.zjze,sum(bf.bfje) bfje,c.cname
// from xm_xmzj j, b, a, xm_xmxx x,xm_ss_zjbf bf,company c
// where j.stage = a.stage
// and j.xh = b.xh
// and A.itemcode = j.itemcode
// and j.itemcode = b.itemcode
// and j.itemcode = x.itemcode
// and j.itemcode=bf.itemcode(+)
// and substr(x.ccode,0,{1})=c.shortccode
// group by x.itemcode,x.itemname,x.ccode,x.lxsj,j.zjze,c.cname,x.itemstage");
string sql = string.Format(sbSql.ToString(), xmxx.Ccode, xmxx.Xmnd, xmxx.Zjnd);
#region 设置参数
OracleParameter pageSize = new OracleParameter("p_PageSize", OracleType.Number, 10);
pageSize.Value = xmxx.PageSize;
OracleParameter pageIndex = new OracleParameter("p_PageIndex", OracleType.Number, 10);
pageIndex.Value = xmxx.PageIndex;
OracleParameter sqlSelect = new OracleParameter("p_SqlSelect", OracleType.VarChar, 4000);
sqlSelect.Value = sql;
OracleParameter recount = new OracleParameter("p_OutRecordCount", OracleType.Number, 5);
recount.Direction = ParameterDirection.Output;
OracleParameter returnCursor = new OracleParameter("p_OutCursor", OracleType.Cursor);
returnCursor.Direction = ParameterDirection.Output;
OracleParameter[] oracleParameters = { pageSize, pageIndex, sqlSelect, recount, returnCursor };
#endregion
DataTable dt = OracleHelper.ExecuteDataTable("CommonPackages.sp_Page", oracleParameters);
recordCount = int.Parse(recount.Value.ToString());
return dt;
}
/// <summary>
/// 根据项目编号获得项目信息
/// </summary>
/// <param name="itemcode">项目编号</param>
/// <returns>项目信息实体类</returns>
public Xm_Xmxx GetXmxxByItemCode(string itemcode)
{
string sql = string.Format("select * from xm_xmxx x where x.itemcode='{0}'", itemcode);
DataTable dt = OracleHelper.ExecuteDataTable(sql);
Xm_Xmxx xmxx = new Xm_Xmxx();
if (dt != null && dt.Rows.Count > 0)
{
DataRow dr = dt.Rows[0];
xmxx.WfState = (WfState)Convert.ToInt32(dr["wfstate"]);
}
return xmxx;
}
/// <summary>
///获得所有项目信息
/// </summary>
/// <param name="xmxx">项目信息实体类</param>
/// <param name="recordCount">记录总数</param>
/// <returns></returns>
public DataTable GetXmxxByXmxx(Xm_Xmxx xmxx, ref int recordCount)
{
string sql = "select t.* from XM_XMXX t {0} order by t.ItemCode ";
//初始化查询条件
string whereStr = "where t.ccode like '{0}%'";
string shotCode = xmxx.Ccode;
whereStr = string.Format(whereStr, shotCode);
//添加项目年度条件
if (!string.IsNullOrEmpty(xmxx.Xmnd)) whereStr += string.Format(" and t.xmnd='{0}'", xmxx.Xmnd);
//添加资金年度条件
if (!string.IsNullOrEmpty(xmxx.Zjnd)) whereStr += string.Format(" and t.zjnd='{0}'", xmxx.Zjnd);
//添加项目阶段条件
if (xmxx.ItemStage != 0) whereStr += string.Format(" and t.itemstage='{0}'", Convert.ToInt32(xmxx.ItemStage));
sql = string.Format(sql, whereStr);
#region 设置参数
OracleParameter pageSize = new OracleParameter("p_PageSize", OracleType.Number, 10);
pageSize.Value = xmxx.PageSize;
OracleParameter pageIndex = new OracleParameter("p_PageIndex", OracleType.Number, 10);
pageIndex.Value = xmxx.PageIndex;
OracleParameter sqlSelect = new OracleParameter("p_SqlSelect", OracleType.VarChar, 4000);
sqlSelect.Value = sql;
OracleParameter recount = new OracleParameter("p_OutRecordCount", OracleType.Number, 5);
recount.Direction = ParameterDirection.Output;
OracleParameter returnCursor = new OracleParameter("p_OutCursor", OracleType.Cursor);
returnCursor.Direction = ParameterDirection.Output;
OracleParameter[] oracleParameters = { pageSize, pageIndex, sqlSelect, recount, returnCursor };
#endregion
DataTable dt = OracleHelper.ExecuteDataTable("CommonPackages.sp_Page", oracleParameters);
recordCount = int.Parse(recount.Value.ToString());
return dt;
}
public bool SaveItem(Xm_Xmxx itemInfo, Xm_Sb_Jbxx itemSb,
List<Item_File> itemFile, Xm_Xmzj itemZj, Xm_Gcxx itemGcxx)
{
ArrayList strSql = new ArrayList();
string tmpSql = string.Empty;
//// 项目主表
strSql.Add(SqlBuilder.BuildDeleteSql<Xm_Xmxx>(CommonManage.delWhere(itemInfo.ItemCode)));
strSql.Add(SqlBuilder.BuildInsertSql(itemInfo));
////项目申报信息
strSql.Add(SqlBuilder.BuildDeleteSql<Xm_Sb_Jbxx>(CommonManage.delWhere(itemSb.ItemCode)));
strSql.Add(SqlBuilder.BuildInsertSql(itemSb));
////文件
new BusiItemManage().SaveItemFile(itemInfo.ItemCode, WorkFlowNode.TB, itemFile, ref strSql);
////资金
new BusiItemManage().SaveItemMoney(itemInfo.ItemCode, WorkFlowNode.TB, itemZj, ref strSql);
////工程
new BusiItemManage().SaveItemGcxx(itemInfo.ItemCode, itemGcxx, ref strSql);
////GIS信息
return OracleHelper.ExecuteCommand(strSql);
}
