protected DataTable GetDataTable(string Sqlstring, OracleDAO dao)
{
Sqlstring.Replace("\n", "");
Sqlstring.Replace("\r", "");
DataTable obj = dao.GetDataTable(Sqlstring, System.Data.CommandType.Text);
return obj;
}
/// <summary>
/// 查询视图smtfb.accountd得到数据
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnCheck_Click(object sender, EventArgs e)
{
OracleDAO dao = new OracleDAO(conn);
string strSql = string.Empty;
if (tbSub.Text.Trim() == "" || tbSub.Text.Trim() == null)
{
strSql = "select * from smtfb.accountd t where t.OWNERCOMPANYNAME = '" + tbCom.Text + "' and t.OWNERDEPARTMENTNAME = '" + tbDep.Text + "'";
}
else
{
strSql = "select * from smtfb.accountd t where t.OWNERCOMPANYNAME = '" + tbCom.Text + "' and t.OWNERDEPARTMENTNAME = '" + tbDep.Text + "' and t.subjectname = '" + tbSub.Text + "'";
}
ExecuteCustomerSql(strSql);
DataTable dsTotal = new DataTable();
dsTotal = this.GetDataTable(strSql, dao);
accountd.DataSource = dsTotal;
accountd.DataBind();
string ownercompanyid = dsTotal.Rows[0]["OWNERCOMPANYID"].ToString();
string ownerdepartmentid = dsTotal.Rows[0]["OWNERDEPARTMENTID"].ToString();
string subjectid = dsTotal.Rows[0]["SUBJECTID"].ToString();
tbComID.Text = dsTotal.Rows[0]["OWNERCOMPANYID"].ToString();
tbDepID.Text = dsTotal.Rows[0]["OWNERDEPARTMENTID"].ToString();
tbSubID.Text = dsTotal.Rows[0]["SUBJECTID"].ToString();
tbBG.Text = dsTotal.Rows[0]["BUDGETACCOUNTID"].ToString();
}
public object ExecuteCustomerSql(string Sqlstring, ParameterCollection prameters)
{
OracleDAO dao = new OracleDAO(_conn);
//prameters = new ParameterCollection();
//prameters.Add("parme1", null);
return(dao.ExecuteScalar(Sqlstring, System.Data.CommandType.Text, prameters));
//return object obj=dao.ExecuteScalar(Sqlstring, System.Data.CommandType.Text);
//prameters = new ParameterCollection();
//object obj1 = dao.GetDataTable(Sqlstring, System.Data.CommandType.Text, prameters);
}
public static object ExecuteCustomerSql(string Sqlstring, ParameterCollection prameters)
{
string conn = ConfigurationManager.AppSettings["ConnectionString"].ToString();
OracleDAO dao = new OracleDAO(conn);
//prameters = new ParameterCollection();
//prameters.Add("parme1", null);
dao.ExecuteScalar(Sqlstring, System.Data.CommandType.Text);
prameters = new ParameterCollection();
dao.GetDataTable(Sqlstring, System.Data.CommandType.Text, prameters);
return null;
}
public static object ExecuteCustomerSql(string Sqlstring, ParameterCollection prameters)
{
string conn = ConfigurationManager.AppSettings["ConnectionString"].ToString();
OracleDAO dao = new OracleDAO(conn);
//prameters = new ParameterCollection();
//prameters.Add("parme1", null);
dao.ExecuteScalar(Sqlstring, System.Data.CommandType.Text);
prameters = new ParameterCollection();
dao.GetDataTable(Sqlstring, System.Data.CommandType.Text, prameters);
return(null);
}
public DataTable GetDataTableByCustomerSql(string Sqlstring)
{
try
{
OracleDAO dao = new OracleDAO(conn);
DataTable dt = dao.GetDataTable(Sqlstring, System.Data.CommandType.Text);
return(dt);
}
catch (Exception ex)
{
Tracer.Debug(ex.ToString());
return(null);
}finally{
Tracer.Debug(Sqlstring);
}
}
public object ExecuteCustomerSql(string Sqlstring)
{
try
{
OracleDAO dao = new OracleDAO(conn);
object obj = dao.ExecuteScalar(Sqlstring, System.Data.CommandType.Text);
//ParameterCollection prameters = new ParameterCollection();
//object obj1 = dao.GetDataTable(Sqlstring, System.Data.CommandType.Text, prameters);
return(obj);
}
catch (Exception ex)
{
Tracer.Debug(ex.ToString());
return(null);
}
}
public int ExecuteNonQuery(string Sqlstring)
{
try
{
OracleDAO dao = new OracleDAO(conn);
//prameters = new ParameterCollection();
//prameters.Add("parme1", null);
return(dao.ExecuteNonQuery(Sqlstring));
//return object obj=dao.ExecuteScalar(Sqlstring, System.Data.CommandType.Text);
//prameters = new ParameterCollection();
//object obj1 = dao.GetDataTable(Sqlstring, System.Data.CommandType.Text, prameters);
}
catch (Exception ex)
{
Tracer.Debug(ex.ToString());
return(0);
}
}
/// <summary>
/// 查找部门流水以查看该部门是否做月度预算
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btCheckzb_dept_Click(object sender, EventArgs e)
{
OracleDAO dao = new OracleDAO(conn);
string strSql = "select * from smtfb.zb_dept t where t.OWNERCOMPANYNAME = '" + tbCom.Text + "' and t.OWNERDEPARTMENTNAME = '" + tbDep.Text + "' and t.subjectname = '" + tbSub.Text + "'" + "and extract(year from t.BUDGETARYMONTH) = 2013";
ExecuteCustomerSql(strSql);
DataTable dsTotal = new DataTable();
dsTotal = this.GetDataTable(strSql, dao);
zb_dept.DataSource = dsTotal;
//if (zb_dept.Rows.Count < 1)
//{
// Response.Write("<script language=javascript>alert('没有流水！');</script>");
// zb_dept.DataSource = null;
// zb_dept.DataBind();
// return;
//}
zb_dept.DataBind();
}
/// <summary>
/// 根据公司ownercompanyid部门ownerdepartmentid科目subjectid去找总账表t_Fb_Budgetaccount信息
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnBudget_Click(object sender, EventArgs e)
{
OracleDAO dao = new OracleDAO(conn);
string strSql = "select * from smtfb.t_Fb_Budgetaccount t where t.ownercompanyid = '" + tbComID.Text + "' and t.ownerdepartmentid = '" + tbDepID.Text + "' and t.subjectid = '" + tbSubID.Text + "'";
ExecuteCustomerSql(strSql);
DataTable dsTotal = new DataTable();
dsTotal = this.GetDataTable(strSql, dao);
t_Fb_Budgetaccount.DataSource = dsTotal;
t_Fb_Budgetaccount.DataBind();
///有用没用都取出来先
BUDGETACCOUNTID = Convert.ToString(dsTotal.Rows[0]["BUDGETACCOUNTID"]);
ACCOUNTOBJECTTYPE = Convert.ToInt32(dsTotal.Rows[0]["ACCOUNTOBJECTTYPE"]);
BUDGETYEAR = Convert.ToInt32(dsTotal.Rows[0]["BUDGETYEAR"]);
BUDGETMONTH = Convert.ToInt32(dsTotal.Rows[0]["BUDGETMONTH"]);
OWNERCOMPANYID = Convert.ToString(dsTotal.Rows[0]["OWNERCOMPANYID"]);
OWNERDEPARTMENTID = Convert.ToString(dsTotal.Rows[0]["OWNERDEPARTMENTID"]);
OWNERID = Convert.ToString(dsTotal.Rows[0]["OWNERID"]);
OWNERPOSTID = Convert.ToString(dsTotal.Rows[0]["OWNERPOSTID"]);
SUBJECTID = Convert.ToString(dsTotal.Rows[0]["SUBJECTID"]);
BUDGETMONEY = Convert.ToInt32(dsTotal.Rows[0]["BUDGETMONEY"]);
USABLEMONEY = Convert.ToInt32(dsTotal.Rows[0]["USABLEMONEY"]);
ACTUALMONEY = Convert.ToInt32(dsTotal.Rows[0]["ACTUALMONEY"]);
PAIEDMONEY = Convert.ToInt32(dsTotal.Rows[0]["PAIEDMONEY"]);
CREATEUSERID = Convert.ToString(dsTotal.Rows[0]["CREATEUSERID"]);
CREATEDATE = Convert.ToDateTime(dsTotal.Rows[0]["CREATEDATE"]);
UPDATEUSERID = Convert.ToString(dsTotal.Rows[0]["UPDATEUSERID"]);
UPDATEDATE = Convert.ToDateTime(dsTotal.Rows[0]["UPDATEDATE"]);
REMARK = Convert.ToString(dsTotal.Rows[0]["REMARK"]);
tbcreate.Text = CREATEDATE.ToString();
tbupdate.Text = UPDATEDATE.ToString();
}
public object ExecuteCustomerSql(string Sqlstring, ParameterCollection prameters)
{
OracleDAO dao = new OracleDAO(conn);
return(dao.ExecuteScalar(Sqlstring, System.Data.CommandType.Text, prameters));
}
/// <summary>
/// 修改可用结余
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Button3_Click(object sender, EventArgs e)
{
OracleDAO dao = new OracleDAO(conn);
Decimal um=0;
if (Decimal.TryParse(TxtUsableMoney.Text.Trim(), out um))
{
if (GridViewAcount.Rows.Count != 1)
{
if (TextBox1.Text == "活动经费")
{
}
else
{
Response.Write("<script language=javascript>alert('汇总记录不是一条,不能修改可用结余！');</script>");
return;
}
}
string strupdate = "";
if(DropDownList1.SelectedValue=="1")
strupdate = "update t_fb_budgetaccount t set t.USABLEMONEY=" + um + " where t.subjectid='" + order.Rows[0].Cells[12].Text + "' and t.accountobjecttype=" + DropDownList1.SelectedValue + " and t.ownercompanyid='" + order.Rows[0].Cells[11].Text + "'";
else if (DropDownList1.SelectedValue == "2")
strupdate = "update t_fb_budgetaccount t set t.USABLEMONEY=" + um + " where t.subjectid='" + order.Rows[0].Cells[12].Text + "' and t.accountobjecttype=" + DropDownList1.SelectedValue + " and t.ownercompanyid='" + order.Rows[0].Cells[11].Text + "' and t.ownerdepartmentid='" + order.Rows[0].Cells[10].Text + "'";
else if (DropDownList1.SelectedValue == "3")
strupdate = "update t_fb_budgetaccount t set t.USABLEMONEY=" + um + " where t.subjectid='" + order.Rows[0].Cells[12].Text + "' and t.accountobjecttype=" + DropDownList1.SelectedValue + " and t.ownercompanyid='" + order.Rows[0].Cells[11].Text + "' and t.ownerid='" + order.Rows[0].Cells[10].Text + "'";
int n =this.ExecuteNonQuery(strupdate,dao);
if (n > 0)
{
Label3.Text = "更新成功！";
}
else
{
Label3.Text = "可用结余更新失败！";
}
}
else
{
Label3.Text = "可用结余不是有效的数字！！！";
}
}
/// <summary>
/// 执行sql方法
/// </summary>
/// <param name="Sqlstring"></param>
/// <returns></returns>
public object ExecuteCustomerSql(string Sqlstring)
{
OracleDAO dao = new OracleDAO(conn);
object obj = dao.ExecuteScalar(Sqlstring, System.Data.CommandType.Text);
return obj;
}
/// <summary>
/// 查找部门流水以查看该部门是否做月度预算（增强版）(只根据公司部门没有预算科目)
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btCheckNew_Click(object sender, EventArgs e)
{
OracleDAO dao = new OracleDAO(conn);
string strSql = "select * from smtfb.zb_dept t where t.OWNERCOMPANYNAME = '" + tbCom.Text + "' and t.OWNERDEPARTMENTNAME = '" + tbDep.Text + "'" + "and extract(year from t.BUDGETARYMONTH) = 2013";
ExecuteCustomerSql(strSql);
DataTable dsTotal = new DataTable();
dsTotal = this.GetDataTable(strSql, dao);
zb_deptNew.DataSource = dsTotal;
//if (zb_deptNew.Rows.Count < 1)
//{
// Response.Write("<script language=javascript>alert('没有流水！');</script>");
// zb_deptNew.DataSource = null;
// zb_deptNew.DataBind();
// lbflag.Text = "没有流水";
// return;
//}
//else
//{
// lbflag.Text = "有流水，不能清零";
//}
zb_deptNew.DataBind();
}
protected void Button2_Click(object sender, EventArgs e)
{
OracleDAO dao = new OracleDAO(conn);
string strsql = TextBox5.Text .Trim ();
//OleDbDataAdapter ada = dbc.ExecuteAdapter(strsql);
DataTable ds = new DataTable();
ds = this.GetDataTable(strsql,dao);
order.DataSource = ds;
order.DataBind();
}
/// <summary>
/// 查询
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Button1_Click(object sender, EventArgs e)
{
OracleDAO dao = new OracleDAO(conn);
string strdate=string .Empty ;
if(DropDownList2.SelectedItem.Value !="0")
strdate=" and to_char((t.BUDGETARYMONTH),'YYYY') ='"+DropDownList2.SelectedItem.Text+"' ";
string strsql = "";
if(DropDownList1.SelectedValue=="1")
strsql = "select t.ORDERTYPENAME as 单据类型,t.budgetarymonth as 时间,t.checkstatesname as 审核状态,t.ordercode as 单号,t.subjectname as 科目名称,t.usablemoney as 可用金额,t.budgetmoney as 实际金额,t.createusername as 创建人,t.ownerdepartmentname as 部门,t.ownercompanyname as 公司,t.OWNERDEPARTMENTID as 部门ID, t.OWNERCOMPANYID as 公司ID,t.subjectid as 科目ID from zzz_comp t where t.OWNERCOMPANYNAME='" + TextBox2.Text + "' and t.OWNERDEPARTMENTNAME='" + TextBox3.Text + "' and t.SUBJECTNAME = '" + TextBox1.Text + "' " + strdate + " order by t.budgetarymonth";
else if(DropDownList1.SelectedValue=="2")
strsql = "select t.ORDERTYPENAME as 单据类型,t.budgetarymonth as 时间,t.checkstatesname as 审核状态,t.ordercode as 单号,t.subjectname as 科目名称,t.usablemoney as 可用金额,t.budgetmoney as 实际金额,t.createusername as 创建人,t.ownerdepartmentname as 部门,t.ownercompanyname as 公司,t.OWNERDEPARTMENTID as 部门ID, t.OWNERCOMPANYID as 公司ID,t.subjectid as 科目ID from zb_dept t where t.OWNERDEPARTMENTNAME='" + TextBox3.Text + "' and t.OWNERCOMPANYNAME='" + TextBox2.Text + "' and t.SUBJECTNAME = '" + TextBox1.Text + "' " + strdate + " order by t.budgetarymonth";
else if (DropDownList1.SelectedValue == "3")
strsql = "select t.ORDERTYPENAME as 单据类型,t.budgetarymonth as 时间,t.checkstatesname as 审核状态,t.ordercode as 单号,t.subjectname as 科目名称,t.usablemoney as 可用金额,t.budgetmoney as 实际金额,t.ownername as 个人姓名,t.ownerdepartmentname as 部门,t.ownercompanyname as 公司,t.ownerid as 个人ID, t.OWNERCOMPANYID as 公司ID,t.subjectid as 科目ID from zb_person t where t.ownername='" + TextBox4.Text + "' and t.OWNERCOMPANYNAME='" + TextBox2.Text + "' and t.SUBJECTNAME = '" + TextBox1.Text + "' " + strdate + " order by t.budgetarymonth";
//OleDbDataAdapter ada= dbc.ExecuteAdapter(strsql);
DataTable dtOrder = new DataTable();
dtOrder = this.GetDataTable(strsql, dao);
order.DataSource = dtOrder;
order.DataBind();
if (order.Rows.Count < 1)
{
Response.Write("<script language=javascript>alert('没有查到你要的数据！');</script>");
GridViewAcount.DataSource = null;
GridViewAcount.DataBind();
return;
}
string strGridViewAcount = "";
if (DropDownList1.SelectedValue == "1")
strGridViewAcount = "select t.subjectname as 科目名称,t.accountobjecttype as 预算类型,t.budgetyear as 年份,t.budgetmonth as 月份,t.budgetmoney as 预算额度,t.usablemoney as 可用结余,t.actualmoney as 实际结余,t.paiedmoney as 费用,t.updatedate as 更新时间 from budgetaccount t where t.ownerdepartmentid='" + dtOrder.Rows[0][10].ToString() + "' and t.ownercompanyid='" + dtOrder.Rows[0][11].ToString() + "' and t.SUBJECTNAME = '" + TextBox1.Text + "' and t.accountobjecttype=" + DropDownList1.SelectedValue;
else if (DropDownList1.SelectedValue == "2")
strGridViewAcount = "select t.subjectname as 科目名称,t.accountobjecttype as 预算类型,t.budgetyear as 年份,t.budgetmonth as 月份,t.budgetmoney as 预算额度,t.usablemoney as 可用结余,t.actualmoney as 实际结余,t.paiedmoney as 费用,t.updatedate as 更新时间 from budgetaccount t where t.ownerdepartmentid='" + dtOrder.Rows[0][10].ToString() + "' and t.ownercompanyid='" + dtOrder.Rows[0][11].ToString() + "' and t.SUBJECTNAME = '" + TextBox1.Text + "' and t.accountobjecttype=" + DropDownList1.SelectedValue;
else if (DropDownList1.SelectedValue == "3")
strGridViewAcount = "select t.subjectname as 科目名称,t.accountobjecttype as 预算类型,t.budgetyear as 年份,t.budgetmonth as 月份,t.budgetmoney as 预算额度,t.usablemoney as 可用结余,t.actualmoney as 实际结余,t.paiedmoney as 费用,t.updatedate as 更新时间 from budgetaccount t where t.ownerid='" + dtOrder.Rows[0][10].ToString()+"' ";
strGridViewAcount += "and t.ownercompanyid='" + dtOrder.Rows[0][11].ToString() + "' ";
strGridViewAcount +="and t.SUBJECTNAME = '" + TextBox1.Text + "' and t.accountobjecttype=" + DropDownList1.SelectedValue;
//ada = dbc.ExecuteAdapter(strGridViewAcount);
DataTable dsGridViewAcount = new DataTable();
dsGridViewAcount = this.GetDataTable(strGridViewAcount,dao);
GridViewAcount.DataSource = dsGridViewAcount;
GridViewAcount.DataBind();
Decimal um = 0;
Decimal am = 0;
for (int i = 0; i < order.Rows.Count; i++)
{
if (dtOrder.Rows[i][2].ToString() == "审核通过")
{
string oldstring=um.ToString();
um += Decimal.Parse(dtOrder.Rows[i][6].ToString());
txtResult.Text = txtResult.Text + System.Environment.NewLine + oldstring + " + " + dtOrder.Rows[i][6].ToString() + "=" + um.ToString();
am += Decimal.Parse(dtOrder.Rows[i][6].ToString());
}
if (dtOrder.Rows[i][2].ToString() == "审核中" || dtOrder.Rows[i][2].ToString() == "审核中或未汇总")
{
if (dtOrder.Rows[i][0].ToString() == "预算分配(调入)" ||dtOrder.Rows[i][0].ToString() == "月度部门调拨(调入)" || dtOrder.Rows[i][0].ToString() == "个人调拨(调入)" || dtOrder.Rows[i][0].ToString() == "个人月度部门预算" || dtOrder.Rows[i][0].ToString() == "月度部门预算" || dtOrder.Rows[i][0].ToString() == "月度部门增补")
{
//上面这些单可用的要审核通过后才算
}
else
{
um += Decimal.Parse(dtOrder.Rows[i][6].ToString());
}
}
}
TxtUsableMoney.Text = um.ToString ();
TxtActualMoney.Text = am.ToString();
Label1.Text = um.ToString();
Label2.Text = am.ToString();
}
protected void btnHuodong_Click(object sender, EventArgs e)
{
OracleDAO dao = new OracleDAO(conn);
//获取所有有多条记录的员工
string strAll = @"select count(t.usablemoney),t.ownerid from smtfb.t_fb_budgetaccount t
inner join smtfb.t_fb_subject s on t.subjectid=s.subjectid
where
s.subjectname='活动经费'
group by t.ownerid
having count(t.ownerid)>1";
DataTable dsGridViewAcount = new DataTable();
dsGridViewAcount = this.GetDataTable(strAll,dao);
//循环处理查出的员工记录
foreach (DataRow dr in dsGridViewAcount.Rows)
{
string ownid = dr["ownerid"].ToString();
if(ownid=="24ccad45-b49b-4499-9012-40f356e9811a")
{
}
//查出指定员工所有的活动经费记录
string allDealData = @"select t.budgetmoney,t.usablemoney,t.actualmoney,t.paiedmoney,t.ownerpostid,ep.isagency,ep.editstate,t.ownerid from smtfb.t_fb_budgetaccount t
inner join smtfb.t_fb_subject s on t.subjectid=s.subjectid
inner join smthrm.t_hr_employeepost ep on ep.employeeid=t.ownerid
and ep.postid=t.ownerpostid
where
t.ownerid='" + ownid + @"'
and
s.subjectname='活动经费'";
DataTable dtallDealData = GetDataTable(allDealData,dao);
//判断主岗位是否多个
if (dtallDealData.Select("isagency=0 and editstate=1").Count() > 1)
{
txtResult.Text += System.Environment.NewLine + "主岗位存在多个，请及时处理employeid：" + ownid;
}
else
{
//查出汇总金额
string strSumDealData = @"select sum(t.budgetmoney) sumbudgetmoney
,sum(t.usablemoney) sumusablemoney,
sum(t.actualmoney) sumactualmoney
,sum(t.paiedmoney) sumpaiedmoney,t.ownerid from smtfb.t_fb_budgetaccount t
inner join smtfb.t_fb_subject s on t.subjectid=s.subjectid
where t.ownerid='" + ownid + @"'
and s.subjectname='活动经费'
group by t.ownerid";
DataTable dtSumDealData = GetDataTable(strSumDealData,dao);
//开始处理
//foreach (DataRow dro in dtallDealData.Rows)
//{
DataRow[] drs = dtallDealData.Select("isagency=0 and editstate=1");
if (drs.Count() == 0)
{
txtResult.Text += System.Environment.NewLine + "通过岗位关联没查到活动经费 ownid：" + ownid;
continue;
}
DataRow drd = dtallDealData.Select("isagency=0 and editstate=1")[0];
string budgetmoney = dtSumDealData.Rows[0]["sumbudgetmoney"].ToString();
string usablemoney = dtSumDealData.Rows[0]["sumusablemoney"].ToString();
string actualmoney = dtSumDealData.Rows[0]["sumactualmoney"].ToString();
string paiedmoney = dtSumDealData.Rows[0]["sumpaiedmoney"].ToString();
//更新主岗位活动经费数据
string updateMainPost = @"update smtfb.t_fb_budgetaccount t
set t.budgetmoney=" + budgetmoney + @",
t.usablemoney=" + usablemoney + @",
t.actualmoney=" + actualmoney + @",
t.paiedmoney=" + paiedmoney + @"
where t.ownerid='" + ownid + @"'
and t.subjectid='d5134466-c207-44f2-8a36-cf7b96d5851f'
and t.ownerpostid='" + drd["ownerpostid"].ToString() + "'";
int i = ExecuteNonQuery(updateMainPost,dao);
txtResult.Text += System.Environment.NewLine + "更新额度成功 ownid：" + ownid;
if (i == 1)//更新成功
{
string strDelete = @"delete from smtfb.t_fb_budgetaccount t
where t.ownerid='" + ownid + @"'
and t.subjectid='d5134466-c207-44f2-8a36-cf7b96d5851f'
and t.ownerpostid <> '" + drd["ownerpostid"].ToString() + "'";
//删除其他所有无用数据
int j = ExecuteNonQuery(strDelete,dao);
txtResult.Text += System.Environment.NewLine + "删除其他数据成功 ownid：" + ownid;
}
}
}
return;
}
protected object ExecuteCustomerSql(string Sqlstring, SMT.Foundation.Core.ParameterCollection prameters)
{
Sqlstring.Replace("\n", "");
Sqlstring.Replace("\r", "");
OracleDAO dao = new OracleDAO(conn);
return dao.ExecuteScalar(Sqlstring, System.Data.CommandType.Text, prameters);
}
protected void btnCheckDep_Click(object sender, EventArgs e)
{
OracleDAO dao = new OracleDAO(conn);
string strSql = @"select
c.cname,
dd.departmentname
from smthrm.t_hr_company c
inner join smthrm.t_hr_department d on c.companyid =d.companyid
inner join smthrm.t_hr_departmentdictionary dd on d.departmentdictionaryid=dd.departmentdictionaryid
where c.cname='" + TextBox2.Text + "'" +
"and dd.departmentname='" + TextBox3.Text + "'";
DataTable dt = new DataTable();
dt = GetDataTable(strSql,dao);
if (dt.Rows.Count > 0)
{
string result = string.Empty;
foreach (DataRow dr in dt.Rows)
{
result = result + Environment.NewLine + dr[0].ToString()+" " + dr[1].ToString();
}
txtResult.Text = "部门检查正确：" + result;
}
else
{
txtResult.Text = "部门不存在";
}
}
protected void btnDepartment_Click(object sender, EventArgs e)
{
OracleDAO dao = new OracleDAO(conn);
string strSql = @"select t.departmentname from smthrm.t_hr_departmentdictionary t
where t.departmentname like '%" + TextBox3.Text + "%'";
DataTable dt = new DataTable();
dt = GetDataTable(strSql,dao);
if (dt.Rows.Count > 0)
{
string result = string.Empty;
foreach (DataRow dr in dt.Rows)
{
result = result + Environment.NewLine + dr[0].ToString();
}
txtResult.Text = "部门检查正确：" + result;
}
else
{
txtResult.Text = "部门不存在";
}
}
protected void btnsubject_Click(object sender, EventArgs e)
{
OracleDAO dao = new OracleDAO(conn);
string strSql = @"select t.subjectname from t_fb_subject t
where t.subjectname like '%" + TextBox1.Text + "%'";
DataTable dt = new DataTable();
dt = GetDataTable(strSql,dao);
if (dt.Rows.Count > 0)
{
string result = string.Empty;
foreach (DataRow dr in dt.Rows)
{
result =result+Environment.NewLine+ dr[0].ToString();
}
txtResult.Text = "科目检查正确：" + result;
}
else
{
txtResult.Text = "科目不存在";
}
}
/// <summary>
/// 修改实际结余
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Button4_Click(object sender, EventArgs e)
{
OracleDAO dao = new OracleDAO(conn);
Decimal am = 0;
if (Decimal.TryParse(TxtActualMoney.Text.Trim(), out am))
{
if (GridViewAcount.Rows.Count > 1)
{
Response.Write("<script language=javascript>alert('汇总记录大于一条,不能修改实际额度！');</script>");
return;
}
string strupdate = "";
if (DropDownList1.SelectedValue == "1")
strupdate = "update t_fb_budgetaccount t set t.actualmoney=" + am + " where t.subjectid='" + order.Rows[0].Cells[12].Text + "' and t.accountobjecttype=" + DropDownList1.SelectedValue + " and t.ownercompanyid='" + order.Rows[0].Cells[11].Text + "'";
else if (DropDownList1.SelectedValue == "2")
strupdate = "update t_fb_budgetaccount t set t.actualmoney=" + am + " where t.subjectid='" + order.Rows[0].Cells[12].Text + "' and t.accountobjecttype=" + DropDownList1.SelectedValue + " and t.ownercompanyid='" + order.Rows[0].Cells[11].Text + "' and t.ownerdepartmentid='" + order.Rows[0].Cells[10].Text + "'";
else if (DropDownList1.SelectedValue == "3")
strupdate = "update t_fb_budgetaccount t set t.actualmoney=" + am + " where t.subjectid='" + order.Rows[0].Cells[12].Text + "' and t.accountobjecttype=" + DropDownList1.SelectedValue + " and t.ownercompanyid='" + order.Rows[0].Cells[11].Text + "' and t.ownerid='" + order.Rows[0].Cells[10].Text + "'";
int n = (int)this.ExecuteNonQuery(strupdate,dao);
if (n > 0)
{
Label4.Text = "更新成功！";
}
else
{
Label4.Text = "实际额度更新失败！";
}
}
else
{
Label3.Text = "实际额度不是有效的数字！！！";
}
}
protected void btnCloseBudget_Click(object sender, EventArgs e)
{
//using (TM_SaaS_OA_EFModelContext dbcontext = new TM_SaaS_OA_EFModelContext())
//{
//
//}
OracleDAO dao = new OracleDAO(conn);
try
{
#region "结算活动经费"
// dao.BeginTransaction();
// string strAccounttd = @"select * from SMTFB.ZC_ACCOUNT_2012 t
// inner join smtfb.t_fb_subject s on t.subjectid=s.subjectid
// where
// s.subjectname='活动经费'";
// DataTable dtAccounttd = new DataTable();
// dtAccounttd = GetDataTable(strAccounttd,dao);
// //备份表
// string backAccountd = @"create table Huodongjingfei" + DateTime.Now.ToString("yyyy_MM_dd") + @" as select t.* from t_fb_budgetaccount t
// inner join smtfb.t_fb_subject s on t.subjectid=s.subjectid
// where s.subjectname='活动经费'";
// int i = this.ExecuteNonQuery(backAccountd, dao);
// //清空总账表
// string strDelT_fb_budgetAccount = @"delete smtfb.t_fb_budgetaccount t
// where t.subjectid='d5134466-c207-44f2-8a36-cf7b96d5851f'";
// int j = this.ExecuteNonQuery(strDelT_fb_budgetAccount, dao);
#endregion
#region "结算部门和个人科目总账"
dao.BeginTransaction();
string strAccounttd = @"select * from SMTFB.ZC_ACCOUNT_2012 t
inner join smtfb.t_fb_subject s on t.subjectid=s.subjectid
where
s.subjectname<>'活动经费'
and s.subjectname<>'部门经费'
and t.ACCOUNTOBJECTTYPE=2
or t.ACCOUNTOBJECTTYPE=3";
DataTable dtAccounttd = new DataTable();
dtAccounttd = GetDataTable(strAccounttd, dao);
//备份表
string backAccountd = @"create table " + DateTime.Now.ToString("yyyy_MM_dd") + @"BudgetAcount as select t.* from t_fb_budgetaccount t";
int i = this.ExecuteNonQuery(backAccountd, dao);
//清空总账表
string strDelT_fb_budgetAccount = @"delete smtfb.t_fb_budgetaccount t
where t.subjectid<>'08c1d9c6-2396-43c3-99f9-227e4a7eb417'
and t.subjectid<>'d5134466-c207-44f2-8a36-cf7b96d5851f'
and t.accountobjecttype=2
or t.accountobjecttype=3";
int j = this.ExecuteNonQuery(strDelT_fb_budgetAccount, dao);
#endregion
string strinsert = "";
//插入新记录
foreach (DataRow dr in dtAccounttd.Rows)
{
//生成新的guid
dr[0] = Guid.NewGuid().ToString();
strinsert += @"insert into t_fb_budgetaccount
(budgetaccountid, accountobjecttype, budgetyear, budgetmonth, ownercompanyid, ownerdepartmentid, ownerid, ownerpostid, subjectid, budgetmoney, usablemoney, actualmoney, paiedmoney, createuserid, createdate, updateuserid, updatedate, remark)
values
(" + "'" +
Guid.NewGuid().ToString() + "','" +
dr["accountobjecttype"].ToString().ToString() + "','" +
dr["budgetyear"].ToString() + "','" +
DateTime.Now.Month + "','" +
dr["ownercompanyid"].ToString() + "','" +
dr["ownerdepartmentid"].ToString() + "','" +
dr["ownerid"].ToString() + "','" +
dr["ownerpostid"].ToString() + "','" +
dr["subjectid"].ToString() + "','" +
dr["budgetmoney"].ToString() + "','" +
dr["usablemoney"].ToString() + "','" +
dr["actualmoney"].ToString() + "','" +
dr["paiedmoney"].ToString() + "','" +
dr["createuserid"].ToString() + "'," +
"TO_DATE('" +DateTime.Now.ToString("yyyy-MM-dd") + "','yyyy-mm-dd hh24:mi:ss')" + ",'" +
dr["updateuserid"].ToString() + "'," +
"TO_DATE('" + DateTime.Now.ToString("yyyy-MM-dd") + "','yyyy-mm-dd hh24:mi:ss')" + ",'" +
DateTime.Now.ToString("yyyy-MM-dd")+"总账手动结算" + "')" +
" ;" + System.Environment.NewLine;
}
int k = this.ExecuteNonQuery(strinsert, dao);
dao.Commit();
}
catch (Exception ex)
{
dao.Rollback();
}
}
protected void Button5_Click(object sender, EventArgs e)
{
OracleDAO dao = new OracleDAO(conn);
// Response.Write("<script language='javascript'>if(confirm('是否批量更新?')){alert('确定')}else{alert('取消')};</script>");
// string strsql = "select t.ORDERTYPENAME as 单据类型,t.budgetarymonth as 时间,t.checkstatesname as 审核状态,t.ordercode as 单号,t.subjectname as 科目名称,t.usablemoney as 可用金额,t.budgetmoney as 实际金额,t.createusername as 创建人,t.ownerdepartmentname as 部门,t.ownercompanyname as 公司,t.OWNERDEPARTMENTID as 部门ID, t.OWNERCOMPANYID as 公司ID,t.subjectid as 科目ID from zzz_dept t where t.usablemoney!=999999 order by t.OWNERCOMPANYNAME,t.ownerdepartmentname ,t.subjectname";
string strdate = string.Empty;
if (DropDownList2.SelectedItem.Value != "0")
strdate = " where to_char((t.BUDGETARYMONTH),'YYYY') ='" + DropDownList2.SelectedItem.Text + "' ";
string strsql = "";
if (DropDownList1.SelectedValue == "1")
strsql = "select t.ORDERTYPENAME as 单据类型,t.budgetarymonth as 时间,t.checkstatesname as 审核状态,t.ordercode as 单号,t.subjectname as 科目名称,t.usablemoney as 可用金额,t.budgetmoney as 实际金额,t.createusername as 创建人,t.ownerdepartmentname as 部门,t.ownercompanyname as 公司,t.OWNERDEPARTMENTID as 部门ID, t.OWNERCOMPANYID as 公司ID,t.subjectid as 科目ID from zzz_comp t " + strdate + " order by t.OWNERCOMPANYNAME,t.ownerdepartmentname ,t.subjectname";
else if (DropDownList1.SelectedValue == "2")
strsql = "select t.ORDERTYPENAME as 单据类型,t.budgetarymonth as 时间,t.checkstatesname as 审核状态,t.ordercode as 单号,t.subjectname as 科目名称,t.usablemoney as 可用金额,t.budgetmoney as 实际金额,t.createusername as 创建人,t.ownerdepartmentname as 部门,t.ownercompanyname as 公司,t.OWNERDEPARTMENTID as 部门ID, t.OWNERCOMPANYID as 公司ID,t.subjectid as 科目ID from zzz_dept t " + strdate + " and t.subjectid!='08c1d9c6-2396-43c3-99f9-227e4a7eb417' and t.subjectid!='d5134466-c207-44f2-8a36-cf7b96d5851f' order by t.OWNERCOMPANYID,t.ownerdepartmentname ,t.OWNERDEPARTMENTID ,t.subjectid";//and t.subjectid='08c1d9c6-2396-43c3-99f9-227e4a7eb417' 部门经费
// strsql = "select t.ORDERTYPENAME as 单据类型,t.budgetarymonth as 时间,t.checkstatesname as 审核状态,t.ordercode as 单号,t.subjectname as 科目名称,t.usablemoney as 可用金额,t.budgetmoney as 实际金额,t.createusername as 创建人,t.ownerdepartmentname as 部门,t.ownercompanyname as 公司,t.OWNERDEPARTMENTID as 部门ID, t.OWNERCOMPANYID as 公司ID,t.subjectid as 科目ID from zzz_dept t " + strdate + " where t.subjectid='08c1d9c6-2396-43c3-99f9-227e4a7eb417' order by t.OWNERCOMPANYID,t.ownerdepartmentname ,t.OWNERDEPARTMENTID ,t.subjectid";
else if (DropDownList1.SelectedValue == "3")
strsql = "select t.ORDERTYPENAME as 单据类型,t.budgetarymonth as 时间,t.checkstatesname as 审核状态,t.ordercode as 单号,t.subjectname as 科目名称,t.usablemoney as 可用金额,t.budgetmoney as 实际金额,t.ownername as 个人姓名,t.ownerdepartmentname as 部门,t.ownercompanyname as 公司,t.ownerid as 个人ID, t.OWNERCOMPANYID as 公司ID,t.subjectid as 科目ID from zzz_person t " + strdate + " where t.subjectid in (select distinct(subjectid) from t_fb_subjectcompany t where t.controltype=3) order by t.OWNERCOMPANYNAME,t.subjectname,t.ownername";
// strsql = "select t.ORDERTYPENAME as 单据类型,t.budgetarymonth as 时间,t.checkstatesname as 审核状态,t.ordercode as 单号,t.subjectname as 科目名称,t.usablemoney as 可用金额,t.budgetmoney as 实际金额,t.ownername as 个人姓名,t.ownerdepartmentname as 部门,t.ownercompanyname as 公司,t.ownerid as 个人ID, t.OWNERCOMPANYID as 公司ID,t.subjectid as 科目ID from zzz_person t " + strdate + " order by t.OWNERCOMPANYNAME,t.subjectname,t.ownername";//where t.subjectid='d5134466-c207-44f2-8a36-cf7b96d5851f' 活动经费
//OleDbDataAdapter ada1 = dbc.ExecuteAdapter(strsql);
DataTable ds1 = new DataTable();
DataTable ds2 = new DataTable();
ds1 = GetDataTable(strsql,dao);
order.DataSource = ds1;
order.DataBind();
Decimal um = 0;
string oldsubjectname = string.Empty;
string oldname = string.Empty;
string oldid= string.Empty;
StreamWriter sw = File.AppendText("D:\\SMTOA相关文档\\预算维护\\update.txt");
string w1 = "";
sw.WriteLine(w1);
string w2 = "修改表t_fb_budgetaccount中 USABLEMONEY "+ DropDownList1.SelectedItem.Text+" 可用结余字段值的时间是：" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + ",修改的记录如下（空白则没有修改）：";
sw.WriteLine(w2);
for (int i = 0; i < ds1.Rows.Count; i++)
{
string name = "";
string id = "";
if (DropDownList1.SelectedValue == "1")
name = ds1.Rows[i][9].ToString();
else if (DropDownList1.SelectedValue == "2")
{
name = ds1.Rows[i][8].ToString();
id = ds1.Rows[i][10].ToString();
}
else if (DropDownList1.SelectedValue == "3")
{
name = ds1.Rows[i][7].ToString();
id = ds1.Rows[i][10].ToString();
}
if ((ds1.Rows[i][4].ToString() == oldsubjectname && oldname == name) || i == 0)//&& oldid ==id
{
if (ds1.Rows[i][2].ToString() == "审核通过" || ds1.Rows[i][2].ToString() == "审核中" || ds1.Rows[i][2].ToString() == "审核中或未汇总")
{
if (ds1.Rows[i][2].ToString() != "审核通过" && (ds1.Rows[i][0].ToString() == "预算分配(调入)" || ds1.Rows[i][0].ToString() == "月度部门调拨(调入)" || ds1.Rows[i][0].ToString() == "个人调拨(调入)" || ds1.Rows[i][0].ToString() == "个人月度部门预算" || ds1.Rows[i][0].ToString() == "月度部门预算" || ds1.Rows[i][0].ToString() == "月度部门增补"))
{
//些上面这些单可用的要审核通过后才算
}
else
{
if (ds1.Rows[i][2].ToString() != "审核中或未汇总")
um += Decimal.Parse(ds1.Rows[i][6].ToString());
}
}
string sql = string.Empty;
if (i == ds1.Rows.Count - 1)
{
if (DropDownList1.SelectedValue == "1")
sql = "select * from t_fb_budgetaccount t where t.subjectid='" + ds1.Rows[i][12].ToString() + "' and t.accountobjecttype=" + DropDownList1.SelectedValue + " and t.ownercompanyid='" + ds1.Rows[i][11].ToString() + "' and t.ownerdepartmentid='" + ds1.Rows[i][10].ToString() + "'";
else if (DropDownList1.SelectedValue == "2")
sql = "select * from t_fb_budgetaccount t where t.subjectid='" + ds1.Rows[i][12].ToString() + "' and t.accountobjecttype=" + DropDownList1.SelectedValue + " and t.ownercompanyid='" + ds1.Rows[i][11].ToString() + "' and t.ownerdepartmentid='" + ds1.Rows[i][10].ToString() + "'";
else if (DropDownList1.SelectedValue == "3")
sql = "select * from t_fb_budgetaccount t where t.subjectid='" + ds1.Rows[i][12].ToString() + "' and t.accountobjecttype=" + DropDownList1.SelectedValue + " and t.ownercompanyid='" + ds1.Rows[i][11].ToString() + "' and t.ownerid='" + ds1.Rows[i][10].ToString() + "' order by t.UPDATEDATE desc";
//OleDbDataAdapter ada4 = dbc.ExecuteAdapter(sql);
DataTable ds4 = new DataTable();
ds4 = GetDataTable(sql,dao);
//ada4.Fill(ds4);
#region 最后只有一条科目（处理最后一条记录）
//if (ds4.Rows.Count ==1)
//{
// if (i == ds1.Rows.Count - 1)//处理最后一条记录
// {
// if (ds1.Rows[i][4].ToString() == oldsubjectname && oldname == ds1.Rows[i][7].ToString())
// um += Decimal.Parse(ds1.Rows[i][6].ToString());
// else
// um = Decimal.Parse(ds1.Rows[i][6].ToString());
// }
// if (Decimal.Parse(ds4.Rows[0][10].ToString()) != um)//(Decimal.Parse(ds4.Rows[0][10].ToString()) < um && Decimal.Parse(ds4.Rows[0][10].ToString()) < Decimal.Parse(ds4.Rows[0][11].ToString()))
// {
// string strupdate = string.Empty;
// if (i == ds1.Rows.Count - 1)
// {
// if (DropDownList1.SelectedValue == "1")
// strupdate = "update t_fb_budgetaccount t set t.USABLEMONEY=" + um + " where t.subjectid='" + ds1.Rows[i][12].ToString() + "' and t.accountobjecttype=" + DropDownList1.SelectedValue + " and t.ownercompanyid='" + ds1.Rows[i][11].ToString() + "' and t.ownerdepartmentid='" + ds1.Rows[i][10].ToString() + "'";
// else if (DropDownList1.SelectedValue == "2")
// strupdate = "update t_fb_budgetaccount t set t.USABLEMONEY=" + um + " where t.subjectid='" + ds1.Rows[i][12].ToString() + "' and t.accountobjecttype=" + DropDownList1.SelectedValue + " and t.ownercompanyid='" + ds1.Rows[i][11].ToString() + "' and t.ownerdepartmentid='" + ds1.Rows[i][10].ToString() + "'";
// else if (DropDownList1.SelectedValue == "3")
// strupdate = "update t_fb_budgetaccount t set t.USABLEMONEY=" + um + " where t.subjectid='" + ds1.Rows[i][12].ToString() + "' and t.accountobjecttype=" + DropDownList1.SelectedValue + " and t.ownercompanyid='" + ds1.Rows[i][11].ToString() + "' and t.ownerid='" + ds1.Rows[i][10].ToString() + "'";
// }
// else
// {
// if (DropDownList1.SelectedValue == "1")
// strupdate = "update t_fb_budgetaccount t set t.USABLEMONEY=" + um + " where t.subjectid='" + ds1.Rows[i - 1][12].ToString() + "' and t.accountobjecttype=" + DropDownList1.SelectedValue + " and t.ownercompanyid='" + ds1.Rows[i - 1][11].ToString() + "' and t.ownerdepartmentid='" + ds1.Rows[i - 1][10].ToString() + "'";
// else if (DropDownList1.SelectedValue == "2")
// strupdate = "update t_fb_budgetaccount t set t.USABLEMONEY=" + um + " where t.subjectid='" + ds1.Rows[i - 1][12].ToString() + "' and t.accountobjecttype=" + DropDownList1.SelectedValue + " and t.ownercompanyid='" + ds1.Rows[i - 1][11].ToString() + "' and t.ownerdepartmentid='" + ds1.Rows[i - 1][10].ToString() + "'";
// else if (DropDownList1.SelectedValue == "3")
// strupdate = "update t_fb_budgetaccount t set t.USABLEMONEY=" + um + " where t.subjectid='" + ds1.Rows[i - 1][12].ToString() + "' and t.accountobjecttype=" + DropDownList1.SelectedValue + " and t.ownercompanyid='" + ds1.Rows[i - 1][11].ToString() + "' and t.ownerid='" + ds1.Rows[i - 1][10].ToString() + "'";
// }
// //int n = (int)this.ExecuteCustomerSql(strupdate);
// //if (n > 0)
// //{
// string w = "修改表记录的ID是：" + ds4.Rows[0][0].ToString() + " 修改前可用结余是：" + ds4.Rows[0][10].ToString() + " 修改后的可用结余是：" + um.ToString();
// sw.WriteLine(w);
// //}
// }
// um = Decimal.Parse(ds1.Rows[i][6].ToString());//重新调整可用结余
//}
#endregion
}
}
else
{
string sql = string.Empty;
if (i == ds1.Rows.Count - 1)
{
if (DropDownList1.SelectedValue == "1")
sql = "select * from t_fb_budgetaccount t where t.subjectid='" + ds1.Rows[i][12].ToString() + "' and t.accountobjecttype=" + DropDownList1.SelectedValue + " and t.ownercompanyid='" + ds1.Rows[i][11].ToString() + "' and t.ownerdepartmentid='" + ds1.Rows[i][10].ToString() + "'";
else if (DropDownList1.SelectedValue == "2")
sql = "select * from t_fb_budgetaccount t where t.subjectid='" + ds1.Rows[i][12].ToString() + "' and t.accountobjecttype=" + DropDownList1.SelectedValue + " and t.ownercompanyid='" + ds1.Rows[i][11].ToString() + "' and t.ownerdepartmentid='" + ds1.Rows[i][10].ToString() + "'";
else if (DropDownList1.SelectedValue == "3")
sql = "select * from t_fb_budgetaccount t where t.subjectid='" + ds1.Rows[i][12].ToString() + "' and t.accountobjecttype=" + DropDownList1.SelectedValue + " and t.ownercompanyid='" + ds1.Rows[i][11].ToString() + "' and t.ownerid='" + ds1.Rows[i][10].ToString() + "'";
}
else
{
if (DropDownList1.SelectedValue == "1")
sql = "select * from t_fb_budgetaccount t where t.subjectid='" + ds1.Rows[i - 1][12].ToString() + "' and t.accountobjecttype=" + DropDownList1.SelectedValue + " and t.ownercompanyid='" + ds1.Rows[i - 1][11].ToString() + "' and t.ownerdepartmentid='" + ds1.Rows[i - 1][10].ToString() + "'";
else if (DropDownList1.SelectedValue == "2")
sql = "select * from t_fb_budgetaccount t where t.subjectid='" + ds1.Rows[i - 1][12].ToString() + "' and t.accountobjecttype=" + DropDownList1.SelectedValue + " and t.ownercompanyid='" + ds1.Rows[i - 1][11].ToString() + "' and t.ownerdepartmentid='" + ds1.Rows[i - 1][10].ToString() + "'";
else if (DropDownList1.SelectedValue == "3")
sql = "select * from t_fb_budgetaccount t where t.subjectid='" + ds1.Rows[i - 1][12].ToString() + "' and t.accountobjecttype=" + DropDownList1.SelectedValue + " and t.ownercompanyid='" + ds1.Rows[i - 1][11].ToString() + "' and t.ownerid='" + ds1.Rows[i - 1][10].ToString() + "'";
}
//OleDbDataAdapter ada3 = dbc.ExecuteAdapter(sql);
DataTable ds3 = new DataTable();
ds3 = GetDataTable(sql,dao);
//ada3.Fill(ds3);
if (ds3.Rows.Count == 1)//多条记录一般有岗位异动或兼职,暂不处理
{
if (i == ds1.Rows.Count - 1)//处理最后一条记录
{
if (ds1.Rows[i][4].ToString() == oldsubjectname && oldname == name)
{
if (ds1.Rows[i][2].ToString() != "审核通过" && (ds1.Rows[i][0].ToString() == "月度部门调拨(调入)" || ds1.Rows[i][0].ToString() == "个人调拨(调入)" || ds1.Rows[i][0].ToString() == "个人月度部门预算" || ds1.Rows[i][0].ToString() == "月度部门预算" || ds1.Rows[i][0].ToString() == "月度部门增补"))
{
//些上面这些单可用的要审核通过后才算
}
else
{
if (ds1.Rows[i][2].ToString() != "审核中或未汇总")
um += Decimal.Parse(ds1.Rows[i][6].ToString());
}
}
else
um = Decimal.Parse(ds1.Rows[i][6].ToString());
}
if (Decimal.Parse(ds3.Rows[0][10].ToString()) != um)// && Decimal.Parse(ds3.Rows[0][10].ToString()) != Decimal.Parse(ds3.Rows[0][11].ToString()))
{
string strupdate = string.Empty;
string Dname = "";
string Pname = "";
string Sname = "";
string Cname = "";
if (i == ds1.Rows.Count - 1)
{
if (DropDownList1.SelectedValue == "1")
{
strupdate = "update t_fb_budgetaccount t set t.USABLEMONEY=" + um + " where t.subjectid='" + ds1.Rows[i][12].ToString() + "' and t.accountobjecttype=" + DropDownList1.SelectedValue + " and t.ownercompanyid='" + ds1.Rows[i][11].ToString() + "' and t.ownerdepartmentid='" + ds1.Rows[i][10].ToString() + "'";
Pname = " 部门：";
Dname = ds1.Rows[i][8].ToString();
Sname = ds1.Rows[i][4].ToString();
Cname = ds1.Rows[i][9].ToString();
}
else if (DropDownList1.SelectedValue == "2")
{
strupdate = "update t_fb_budgetaccount t set t.USABLEMONEY=" + um + " where t.subjectid='" + ds1.Rows[i][12].ToString() + "' and t.accountobjecttype=" + DropDownList1.SelectedValue + " and t.ownercompanyid='" + ds1.Rows[i][11].ToString() + "' and t.ownerdepartmentid='" + ds1.Rows[i][10].ToString() + "'";
Pname = " 部门：";
Dname = ds1.Rows[i][8].ToString();
Sname = ds1.Rows[i][4].ToString();
Cname = ds1.Rows[i][9].ToString();
}
else if (DropDownList1.SelectedValue == "3")
{
strupdate = "update t_fb_budgetaccount t set t.USABLEMONEY=" + um + " where t.subjectid='" + ds1.Rows[i][12].ToString() + "' and t.accountobjecttype=" + DropDownList1.SelectedValue + " and t.ownercompanyid='" + ds1.Rows[i][11].ToString() + "' and t.ownerid='" + ds1.Rows[i][10].ToString() + "'";
Pname = " 个人：";
Dname = ds1.Rows[i][7].ToString();
Sname = ds1.Rows[i][4].ToString();
Cname = ds1.Rows[i][9].ToString();
}
}
else
{
if (DropDownList1.SelectedValue == "1")
{
strupdate = "update t_fb_budgetaccount t set t.USABLEMONEY=" + um + " where t.subjectid='" + ds1.Rows[i - 1][12].ToString() + "' and t.accountobjecttype=" + DropDownList1.SelectedValue + " and t.ownercompanyid='" + ds1.Rows[i - 1][11].ToString() + "' and t.ownerdepartmentid='" + ds1.Rows[i - 1][10].ToString() + "'";
Pname = " 部门：";
Dname = ds1.Rows[i - 1][8].ToString();
Sname = ds1.Rows[i - 1][4].ToString();
Cname = ds1.Rows[i - 1][9].ToString();
}
else if (DropDownList1.SelectedValue == "2")
{
strupdate = "update t_fb_budgetaccount t set t.USABLEMONEY=" + um + " where t.subjectid='" + ds1.Rows[i - 1][12].ToString() + "' and t.accountobjecttype=" + DropDownList1.SelectedValue + " and t.ownercompanyid='" + ds1.Rows[i - 1][11].ToString() + "' and t.ownerdepartmentid='" + ds1.Rows[i - 1][10].ToString() + "'";
Pname = " 部门：";
Dname = ds1.Rows[i - 1][8].ToString();
Sname = ds1.Rows[i - 1][4].ToString();
Cname = ds1.Rows[i - 1][9].ToString();
}
else if (DropDownList1.SelectedValue == "3")
{
strupdate = "update t_fb_budgetaccount t set t.USABLEMONEY=" + um + " where t.subjectid='" + ds1.Rows[i - 1][12].ToString() + "' and t.accountobjecttype=" + DropDownList1.SelectedValue + " and t.ownercompanyid='" + ds1.Rows[i - 1][11].ToString() + "' and t.ownerid='" + ds1.Rows[i - 1][10].ToString() + "'";
Pname = " 个人：";
Dname = ds1.Rows[i - 1][7].ToString();
Sname = ds1.Rows[i - 1][4].ToString();
Cname = ds1.Rows[i - 1][9].ToString();
}
}
//int n = (int)this.ExecuteCustomerSql(strupdate);
//if (n > 0)
//{
string w = "修改表记录的ID是：" + ds3.Rows[0][0].ToString() + " 修改前实际结余是：" + ds3.Rows[0][11].ToString() + " 修改前可用结余是：" + ds3.Rows[0][10].ToString() + " 修改后的可用结余是：" + um.ToString();
sw.WriteLine(w);
string c = " 科目：" + Sname + " 公司：" + Cname + Pname + Dname;
sw.WriteLine(c);
//}
}
if (ds1.Rows[i][2].ToString() != "审核中或未汇总")
um = Decimal.Parse(ds1.Rows[i][6].ToString());//重新调整可用结余
else
um = 0;
}
else
{
if (ds1.Rows[i][4].ToString() == oldsubjectname && oldname == name)
{
if (ds1.Rows[i][2].ToString() != "审核通过" && (ds1.Rows[i][0].ToString() == "月度部门调拨(调入)" || ds1.Rows[i][0].ToString() == "个人调拨(调入)" || ds1.Rows[i][0].ToString() == "个人月度部门预算" || ds1.Rows[i][0].ToString() == "月度部门预算" || ds1.Rows[i][0].ToString() == "月度部门增补"))
{
//些上面这些单可用的要审核通过后才算
}
else
{
if (ds1.Rows[i][2].ToString() != "审核中或未汇总")
um += Decimal.Parse(ds1.Rows[i][6].ToString());
}
}
else
{
if (ds1.Rows[i][2].ToString() != "审核中或未汇总")
um = Decimal.Parse(ds1.Rows[i][6].ToString());
else
um = 0;
}
}
}
oldsubjectname = ds1.Rows[i][4].ToString();
if (DropDownList1.SelectedValue == "1")
{
oldname = ds1.Rows[i][9].ToString();
}
else if (DropDownList1.SelectedValue == "2")
{
oldname = ds1.Rows[i][8].ToString();
oldid = ds1.Rows[i][10].ToString();
}
else if (DropDownList1.SelectedValue == "3")
{
oldname = ds1.Rows[i][7].ToString();
oldid = ds1.Rows[i][10].ToString();
}
}
sw.Close();
}
protected int ExecuteNonQuery(string Sqlstring,OracleDAO dao)
{
Sqlstring.Replace("\n", "");
Sqlstring.Replace("\r", "");
int i = dao.ExecuteNonQuery(Sqlstring, System.Data.CommandType.Text);
return i;
}
/// <summary>
/// 创建传统数据库操作DAO
/// </summary>
/// <param name="connection"></param>
/// <returns></returns>
public static IDAO CreateDao(string connection)
{
IDAO dao;
switch (DataBaseType)
{
case "Oracle":
dao = new OracleDAO(connection);
break;
case "SQLServer":
dao = new SqlServerDAO(connection);
break;
case "MySql":
dao = new MySqlServerDAO(connection);
break;
default:
dao = new MySqlServerDAO(connection);
break;
}
return dao;
}
