public override void DataBind()
{
if (string.IsNullOrEmpty(Request.QueryString["mode"]))
{
string StrSql = @" Select M.MasterID, M.Term, M.StudyYear, D.DeptName, D.DeptCode, SD.MainSubDeptCode, SD.MainSubDeptName, MD.MainDeptCode, MD.MainDeptName
From ftYearMaster M Inner Join Department D On M.DeptCode = D.DeptCode
Inner Join MainSubDepartment SD On D.MainSubDeptCode = SD.MainSubDeptCode
Inner Join MainDepartment MD ON SD.MainDeptCode = MD.MainDeptCode
Where M.DelFlag = 0 ";
if (ddlSearchYear.SelectedIndex != 0)
{
StrSql += " And M.StudyYear = '" + ddlSearchYear.SelectedValue + "' ";
}
if (ddlSearchMainDept.SelectedIndex != 0)
{
StrSql += " And MD.MainDeptCode = '" + ddlSearchMainDept.SelectedValue + "'";
}
if (ddlSearchMainSubDept.SelectedIndex != 0)
{
StrSql += " And SD.MainSubDeptCode = '" + ddlSearchMainSubDept.SelectedValue + "'";
}
if (ddlSearchDept.SelectedIndex != 0)
{
StrSql += " And D.DeptCode = '" + ddlSearchDept.SelectedValue + "'";
}
if (txtSearch.Text != "")
{
StrSql += " And M.Term Like '%" + txtSearch.Text + "%' Or M.StudyYear Like '%" + txtSearch.Text + "%' ";
}
DataView dv = Conn.Select(string.Format(StrSql + " Order By M.StudyYear Asc, M.Term "));
GridView1.DataSource = dv;
GridView1.DataBind();
lblSearchTotal.InnerText = dv.Count.ToString();
}
else
{
if (Request.QueryString["mode"] == "5")
{
string StrSql = @" Select M.MasterID, M.Term, M.StudyYear, MD.ItemID, MD.Price, MD.Amount, IsNull(MD.SetMoney, 0) + (IsNull(MD.Price, 0) * IsNull(MD.Amount, 0)) As TotalAmount,
C.ClassName, BD.BudgetDetailTypeName, BT.BudgetTypeName, IsNull(MD.SetMoney, 0) SetMoney, IsNull(MD.Amount2, 0) Amount2,
IsNull(MD.Price2, 0) Price2, IsNull(MD.Price2, 0) * IsNull(MD.Amount2, 0) As TotalAmount2, MD.WithdrawUser, MD.WithdrawDate, MD.BudgetDetailTypeID
From ftYearMaster M Inner Join ftYearMasterDetail MD On M.MasterID = MD.MasterID
Inner Join ftYearClass C On MD.ClassID = C.ClassID
Inner Join ftYearBudgetTypeDetail BD On MD.BudgetDetailTypeID = BD.BudgetDetailTypeID
Inner JOin ftYearBudgetType BT On BD.BudgetTypeID = BT.BudgetTypeID
Inner Join Department D On M.DeptCode = D.DeptCode
Inner Join MainSubDepartment SD On D.MainSubDeptCode = SD.MainSubDeptCode
Inner Join MainDepartment MDt ON SD.MainDeptCode = MDt.MainDeptCode
Where M.DelFlag = 0 And M.MasterID = '" + Request.QueryString["mid"] + "' ";
if (ddlSearchClass.SelectedIndex != 0)
{
StrSql += " And C.ClassID = '" + ddlSearchClass.SelectedValue + "' ";
}
if (ddlSearchBudgetType.SelectedIndex != 0)
{
StrSql += " And BT.BudgetTypeID = '" + ddlSearchBudgetType.SelectedValue + "' ";
}
if (ddlSearchMainDept2.SelectedIndex != 0)
{
StrSql += " And MDt.MainDeptCode = '" + ddlSearchMainDept2.SelectedValue + "' ";
}
if (ddlSearchMainSubDept2.SelectedIndex != 0)
{
StrSql += " And SD.MainSubDeptCode = '" + ddlSearchMainSubDept2.SelectedValue + "' ";
}
if (ddlSearchDept2.SelectedIndex != 0)
{
StrSql += " And D.DeptCode = '" + ddlSearchDept2.SelectedValue + "'";
}
if (txtSearch2.Text != "")
{
StrSql += " And (C.ClassName Like '%" + txtSearch2.Text + "%' Or BD.BudgetDetailTypeName Like '%" + txtSearch2.Text + "%' Or BT.BudgetTypeName Like '%" + txtSearch2.Text + "%') ";
}
DataView dv = Conn.Select(string.Format(StrSql + " And BD.BudgetDetailTypeID <> 5 Order By M.StudyYear Asc, M.Term, C.ClassID, BT.BudgetTypeID, BD.BudgetDetailTypeID"));
GridView2.DataSource = dv;
GridView2.DataBind();
lblSearchTotal2.InnerText = dv.Count.ToString();
DataView dvAc = Conn.Select(string.Format(StrSql + " And BD.BudgetDetailTypeID = 5 Order By M.StudyYear Asc, M.Term, C.ClassID, BT.BudgetTypeID, BD.BudgetDetailTypeID"));
GridView3.DataSource = dvAc;
GridView3.DataBind();
StrSql = @" Select IsNull(Sum(IsNull(MD.Amount, 0)), 0) TotalStudent, IsNull(Sum(IsNull(MD.Price, 0)), 0) TotalPrice,
IsNull(Sum((IsNull(MD.Amount, 0) * IsNull(MD.Price, 0))), 0) SumTotal
From ftYearMaster M Inner Join ftYearMasterDetail MD On M.MasterID = MD.MasterID
Where M.DelFlag = 0 And M.MasterID = '{0}' And MD.BudgetDetailTypeID = 5 " ;
DataView dvMoneyActivity = Conn.Select(string.Format(StrSql, Request.QueryString["mid"]));
if (dvMoneyActivity.Count > 0)
{
lblTotalStudent.Text = Convert.ToInt32(dvMoneyActivity[0]["TotalStudent"]).ToString("#,##0");
lblTotalPrice.Text = Convert.ToDecimal(dvMoneyActivity[0]["TotalPrice"]).ToString("#,##0.00");
lblSumTotal.Text = Convert.ToDecimal(dvMoneyActivity[0]["SumTotal"]).ToString("#,##0.00");
decimal SumToal = 0;
decimal SetMoneyAcType = 0;
if ((!string.IsNullOrEmpty(lblSumTotal.Text)) && (!string.IsNullOrEmpty(lblSetMoneyAcType.Text)))
{
SumToal = Convert.ToDecimal(lblSumTotal.Text);
SetMoneyAcType = Convert.ToDecimal(lblSetMoneyAcType.Text);
}
lblSumTotalAll.Text = (SumToal + SetMoneyAcType).ToString("#,##0.00");
}
StrSql = @" Select IsNull(Sum(CD.TotalMoney2), 0) As MoneyAcType From Activity A
Inner Join dtAcDept dt On A.ActivityCode = dt.ActivityCode
Inner Join CostsDetail CD On A.ActivityCode = CD.ActivityCode
Where A.DelFlag = 0 And A.ApproveFlag = 1 And A.Status = 3
And A.StudyYear = '{0}' And A.Term = '{1}' And dt.DeptCode = '{2}'
And CD.BudgetTypeCode = 'b27b079f-5da5-4278-a172-b974be1ed317' " ;
DataView dvMoneyAcType = Conn.Select(string.Format(StrSql, lblStudyYear.Text, lblTerm.Text, ddlSearchDept2.SelectedValue));
if (dvMoneyAcType.Count > 0)
{
lblMoneyAcType.Text = Convert.ToDecimal(dvMoneyAcType[0]["MoneyAcType"]).ToString("#,##0.00");
}
lblTotalBalance.Text = (Convert.ToDecimal(lblSumTotalAll.Text) - Convert.ToDecimal(lblMoneyAcType.Text)).ToString("#,##0.00");
btc.SetLableColor(lblTotalBalance);
}
else
{
if (Request.QueryString["mode"] == "7")
{
string strSql = @"Select s.SubsidyID, s.SubsidyName, Sd.ItemID, IsNull(Sd.Money, 0) Money, Sd.BudgetTypeID
From ftYearSubsidy s Left Join ftYearSubsidyDetail Sd On s.SubsidyID = Sd.SubsidyID And SD.BudgetTypeID = 1 And Sd.SubsidyItemID = '{0}'
Where s.Delflag = 0
Order By s.SubsidyID ";
DataView dv = Conn.Select(string.Format(strSql, Request.QueryString["id"]));
GridSubsidy.DataSource = dv;
GridSubsidy.DataBind();
}
}
}
}
