private void getDataActivity(string id)
{
string strSql = " Select a.ActivityCode, a.ActivityName, a.Place, b.ProjectsName, a.TotalAmount, "
+ " CostsType = Case a.CostsType When 0 Then '§Ò¹¡Ô¨¡ÃÃÁ' When 1 Then '§Ò¹»ÃШÓ' End From Activity a, Projects b "
+ " Where a.ProjectsCode = b.ProjectsCode And a.ActivityCode = '" + id + "' ";
DataView dv = Conn.Select(strSql);
lblProjects.Text = dv[0]["ProjectsName"].ToString();
txtActivity.Text = dv[0]["ActivityName"].ToString();
lblType.Text = dv[0]["CostsType"].ToString();
lblPlace.Text = dv[0]["Place"].ToString();
lblDept.Text = btc.getAcDeptName(id);
lblEmp.Text = btc.getAcEmpName(id);
getddlResponsibleName();
if (Request.QueryString["mode"] == "1")
{
getBudget(dv[0]["ActivityCode"].ToString());
}
}
public override void DataBind()
{
string StrSql = "Select a.ProjectsCode, a.ProjectsName, b.ActivityCode, b.ActivityName, b.SDate , b.EDate, '' As DeptName, b.Status, b.Df, IsNull(b.ApproveFlag, 0) ApproveFlag, "
+ " b.CostsType, IsNull(b.TotalAmount, 0) TotalAmount, IsNull((Cast(b.Term As nVarChar) + '/' + Cast(b.YearB As nVarChar)), '') Term, 0 As ConnectInd, 0 As ConnectEva "
+ " From Projects a, Activity b "
+ " Where a.ProjectsCode = b.ProjectsCode And b.DelFlag = 0 "
+ " And a.StudyYear = '" + ddlYearB.SelectedValue + "' And b.SchoolID = '" + CurrentUser.SchoolID + "' ";
DataView dv = Conn.Select(string.Format(StrSql + " Order By b.CreateDate Desc "));
for (int j = 0; j < dv.Count; j++)
{
dv[j]["DeptName"] = btc.getAcDeptName(dv[j]["ActivityCode"].ToString());
dv[j]["ConnectInd"] = btc.getAcIndicators2(dv[j]["ActivityCode"].ToString());
dv[j]["ConnectEva"] = btc.getAcEvaluation(dv[j]["ActivityCode"].ToString());
}
GridView1.DataSource = dv;
lblSearchTotal.InnerText = dv.Count.ToString();
GridView1.DataBind();
}
public override void DataBind()
{
string StrSql = " Select Distinct(a.ActivityCode) ActivityCode, b.ProjectsCode, b.ActivityName, b.DeptCode, "
+ " CostsType = Case b.CostsType When 0 Then 'เงินงบประมาณ' When 1 Then 'เงินระดม' End, "
+ " b.TotalAmount, b.SDate, b.EDate, b.Status, c.ProjectsName, '' DeptName, c.Sort, b.Sort "
+ " From Evaluation a, Activity b, Projects c "
+ " Where a.DelFlag = 0 And b.DelFlag = 0 And a.ActivityCode = b.ActivityCode "
+ " And b.ProjectsCode = c.ProjectsCode "
+ " And a.IndicatorsCode = '" + Request.QueryString["id"] + "' And a.StudyYear = '" + Request.QueryString["syear"] + "' "
+ " And a.SchoolID = '" + CurrentUser.SchoolID + "' ";
DataView dv = Conn.Select(string.Format(StrSql + " Order By c.Sort, b.Sort "));
for (int j = 0; j < dv.Count; j++)
{
dv[j]["DeptName"] = btc.getAcDeptName(dv[j]["ActivityCode"].ToString());
}
GridView1.DataSource = dv;
GridView1.DataBind();
}
private void getData()
{
DataView dv;
BTC btc = new BTC();
Connection Conn = new Connection();
string strSql = " Select ActivityCode, ActivityName, StudyYear, RealSDate As SDate, RealEDate As EDate, '' EmpName, '' DeptName "
+ " From Activity Where DelFlag = 0 And StudyYear = '" + ddlYearB.SelectedValue + "' "
+ " And SchoolID = '" + CurrentUser.SchoolID + "'";
if (ddlMonth.SelectedValue != "00")
{
strSql += " And '" + Convert.ToInt32(ddlMonth.SelectedValue) + "' Between Month(RealSDate) And Month(RealEDate) ";
}
if (!string.IsNullOrEmpty(ddlSearchDept.SelectedValue))
{
strSql = " Select a.ActivityCode, a.ActivityName, a.StudyYear, a.RealSDate As SDate, a.RealEDate As EDate, '' EmpName, '' DeptName "
+ " From Activity a, dtAcDept c "
+ " Where a.DelFlag = 0 And a.ActivityCode = c.ActivityCode "
+ " And a.StudyYear = '" + ddlYearB.SelectedValue + "' And a.SchoolID = '" + CurrentUser.SchoolID + "' "
+ " And c.DeptCode = '" + ddlSearchDept.SelectedValue + "' ";
if (ddlMonth.SelectedValue != "00")
{
strSql += " And '" + Convert.ToInt32(ddlMonth.SelectedValue) + "' Between Month(a.RealSDate) And Month(a.RealEDate) ";
}
}
if (!string.IsNullOrEmpty(ddlSearchEmp.SelectedValue))
{
if (string.IsNullOrEmpty(ddlSearchDept.SelectedValue))
{
strSql = " Select a.ActivityCode, a.ActivityName, a.StudyYear, a.RealSDate As SDate, a.RealEDate As EDate, '' EmpName, '' DeptName "
+ " From Activity a, dtAcEmp c "
+ " Where a.DelFlag = 0 And a.ActivityCode = c.ActivityCode "
+ " And a.StudyYear = '" + ddlYearB.SelectedValue + "' And a.SchoolID = '" + CurrentUser.SchoolID + "' "
+ " And c.EmpCode = '" + ddlSearchEmp.SelectedValue + "' ";
if (ddlMonth.SelectedValue != "00")
{
strSql += " And '" + Convert.ToInt32(ddlMonth.SelectedValue) + "' Between Month(a.RealSDate) And Month(a.RealEDate) ";
}
}
else
{
strSql = " Select a.ActivityCode, a.ActivityName, a.StudyYear, a.RealSDate As SDate, a.RealEDate As EDate, '' EmpName, '' DeptName "
+ " From Activity a, dtAcEmp c, dtAcDept d "
+ " Where a.DelFlag = 0 And a.ActivityCode = c.ActivityCode And a.ActivityCode = d.ActivityCode "
+ " And a.StudyYear = '" + ddlYearB.SelectedValue + "' And a.SchoolID = '" + CurrentUser.SchoolID + "' "
+ " And c.EmpCode = '" + ddlSearchEmp.SelectedValue + "' And d.DeptCode = '" + ddlSearchDept.SelectedValue + "' ";
if (ddlMonth.SelectedValue != "00")
{
strSql += " And '" + Convert.ToInt32(ddlMonth.SelectedValue) + "' Between Month(a.RealSDate) And Month(a.RealEDate) ";
}
}
}
dv = Conn.Select(string.Format(strSql + " Order By RealSDate"));
if (dv.Count > 0)
{
for (int i = 0; i < dv.Count; i++)
{
dv[i]["EmpName"] = btc.getAcEmpName(dv[i]["ActivityCode"].ToString());
dv[i]["DeptName"] = btc.getAcDeptName(dv[i]["ActivityCode"].ToString());
}
}
DataView dvSchool = btc.getMasterSchool();
string schoolName = "";
if (dvSchool.Count > 0)
{
schoolName = dvSchool[0]["SchoolName"].ToString();
}
else
{
FormsAuthentication.RedirectToLoginPage();
}
GridView1.DataSource = dv;
GridView1.DataBind();
}
public override void DataBind()
{
string strSideName = "'Áҵðҹ·Õè '";
string strStandardName = "'µÑǺ觪Õé·Õè '";
string StrSql = "Select a.EvaluationCode, b.StandardCode, {1} + Cast(s.Sort As nVarChar) + '.' + Cast(b.Sort As nVarChar) StandardName, {0} + Cast(s.Sort As nVarChar) SideName, "
+ " c.ProjectsCode, c.ProjectsName, d.ActivityCode, d.ActivityName, d.SDate, d.EDate, d.Status, "
+ " a.OffAll, a.OffThat, a.APercent, a.CkCriterion, e.Indicators2Code, e.IndicatorsName2, '' DeptName, "
+ " c.Sort SortPj, d.Sort SortAc, e.Sort SortInt2, b.Sort SortStd, s.Sort SortSide "
+ " From Evaluation a Inner Join Standard b On a.StandardCode = b.StandardCode "
+ " Inner Join Projects c On a.ProjectsCode = c.ProjectsCode "
+ " Left Join dtStrategies SS On c.ProjectsCode = SS.ProjectsCode "
+ " Inner Join Activity d On a.ActivityCode = d.ActivityCode "
+ " Inner Join Indicators2 e On a.Indicators2Code = e.Indicators2Code "
+ " Inner Join Side s On b.SideCode = s.SideCode "
+ " Where a.DelFlag = 0 And a.StudyYear = '" + ddlSearchYear.SelectedValue + "' And a.SchoolID = '" + CurrentUser.SchoolID + "' ";
if (ddlSearchDept.SelectedIndex != 0)
{
StrSql = "Select a.EvaluationCode, b.StandardCode, {1} + Cast(s.Sort As nVarChar) + '.' + Cast(b.Sort As nVarChar) StandardName, {0} + Cast(s.Sort As nVarChar) SideName, "
+ " c.ProjectsCode, c.ProjectsName, d.ActivityCode, d.ActivityName, d.SDate, d.EDate, d.Status, "
+ " a.OffAll, a.OffThat, a.APercent, a.CkCriterion, e.Indicators2Code, e.IndicatorsName2, '' DeptName, "
+ " c.Sort SortPj, d.Sort SortAc, e.Sort SortInt2, b.Sort SortStd, s.Sort SortSide "
+ " From Evaluation a Inner Join Standard b On a.StandardCode = b.StandardCode "
+ " Inner Join Projects c On a.ProjectsCode = c.ProjectsCode "
+ " Left Join dtStrategies SS On c.ProjectsCode = SS.ProjectsCode "
+ " Inner Join Activity d On a.ActivityCode = d.ActivityCode "
+ " Inner Join Indicators2 e On a.Indicators2Code = e.Indicators2Code "
+ " Inner Join dtAcDept g On d.ActivityCode = g.ActivityCode "
+ " Inner Join Side s On b.SideCode = s.SideCode "
+ " Where a.DelFlag = 0 And a.StudyYear = '" + ddlSearchYear.SelectedValue + "' And a.SchoolID = '" + CurrentUser.SchoolID + "' "
+ " And g.DeptCode = '" + ddlSearchDept.SelectedValue + "' ";
}
if (ddlSearchEmp.SelectedIndex != 0)
{
if (ddlSearchDept.SelectedIndex == 0)
{
StrSql = "Select a.EvaluationCode, b.StandardCode, {1} + Cast(s.Sort As nVarChar) + '.' + Cast(b.Sort As nVarChar) StandardName, {0} + Cast(s.Sort As nVarChar) SideName, "
+ " c.ProjectsCode, c.ProjectsName, d.ActivityCode, d.ActivityName, d.SDate, d.EDate, d.Status, "
+ " a.OffAll, a.OffThat, a.APercent, a.CkCriterion, e.Indicators2Code, e.IndicatorsName2, '' DeptName, "
+ " c.Sort SortPj, d.Sort SortAc, e.Sort SortInt2, b.Sort SortStd, s.Sort SortSide "
+ " From Evaluation a Inner Join Standard b On a.StandardCode = b.StandardCode "
+ " Inner Join Projects c On a.ProjectsCode = c.ProjectsCode "
+ " Left Join dtStrategies SS On c.ProjectsCode = SS.ProjectsCode "
+ " Inner Join Activity d On a.ActivityCode = d.ActivityCode "
+ " Inner Join Indicators2 e On a.Indicators2Code = e.Indicators2Code "
+ " Innner Join dtAcEmp g On d.ActivityCode = g.ActivityCode "
+ " Inner Join Side s On b.SideCode = s.SideCode "
+ " Where a.DelFlag = 0 And a.StudyYear = '" + ddlSearchYear.SelectedValue + "' And a.SchoolID = '" + CurrentUser.SchoolID + "' "
+ " And g.EmpCode = '" + ddlSearchEmp.SelectedValue + "' ";
}
else
{
StrSql = "Select a.EvaluationCode, b.StandardCode, {1} + Cast(s.Sort As nVarChar) + '.' + Cast(b.Sort As nVarChar) StandardName, {0} + Cast(s.Sort As nVarChar) SideName, "
+ " c.ProjectsCode, c.ProjectsName, d.ActivityCode, d.ActivityName, d.SDate, d.EDate, d.Status, "
+ " a.OffAll, a.OffThat, a.APercent, a.CkCriterion, e.Indicators2Code, e.IndicatorsName2, '' DeptName, "
+ " c.Sort SortPj, d.Sort SortAc, e.Sort SortInt2, b.Sort SortStd, s.Sort SortSide "
+ " From Evaluation a Inner Join Standard b On a.StandardCode = b.StandardCode "
+ " Inner Join Projects c On a.ProjectsCode = c.ProjectsCode "
+ " Left Join dtStrategies SS On c.ProjectsCode = SS.ProjectsCode "
+ " Inner Join Activity d On a.ActivityCode = d.ActivityCode "
+ " Inner Join Indicators2 e On a.Indicators2Code = e.Indicators2Code "
+ " Inner Join dtAcEmp g On d.ActivityCode = g.ActivityCode "
+ " Inner Join dtAcDept i On d.ActivityCode = i.ActivityCode "
+ " Inner Join Side s On b.SideCode = s.SideCode "
+ " Where a.DelFlag = 0 And a.StudyYear = '" + ddlSearchYear.SelectedValue + "' And a.SchoolID = '" + CurrentUser.SchoolID + "' "
+ " And g.EmpCode = '" + ddlSearchEmp.SelectedValue + "' And i.DeptCode = '" + ddlSearchDept.SelectedValue + "' ";
}
}
if (ddlSearchStandard.SelectedIndex != 0)
{
StrSql = StrSql + " And a.StandardCode = '" + ddlSearchStandard.SelectedValue + "' ";
}
//if (ddlSearch2.SelectedIndex != 0)
//{
// StrSql = StrSql + " And SS.StrategiesCode = '" + ddlSearch2.SelectedValue + "' ";
//}
if (ddlSearch.SelectedIndex != 0)
{
StrSql = StrSql + " And c.ProjectsCode = '" + ddlSearch.SelectedValue + "' ";
}
if (ddlSearch1.SelectedIndex != 0)
{
StrSql = StrSql + " And d.ActivityCode = '" + ddlSearch1.SelectedValue + "' ";
}
if (ddlSearch3.SelectedIndex != 0)
{
StrSql = StrSql + " And e.Indicators2Code = '" + ddlSearch3.SelectedValue + "' ";
}
if (txtSearch.Text != "")
{
StrSql = StrSql + " And (d.ActivityName Like '%" + txtSearch.Text + "%' Or e.IndicatorsName2 Like '%" + txtSearch.Text + "%') ";
}
if (ddlSearchStandard.SelectedIndex == 0)
{
if ((ddlSearchDept.SelectedIndex == 0) && (ddlSearchEmp.SelectedIndex == 0))
{
StrSql = StrSql + " Union "
+ " Select '' As EvaluationCode, '' As StandardCode, '' As StandardName, '' As SideName, "
+ " c.ProjectsCode, c.ProjectsName, d.ActivityCode, d.ActivityName, d.SDate, d.EDate, d.Status, "
+ " 0 As OffAll, 0 As OffThat, 0 As APercent, 0 As CkCriterion, e.Indicators2Code, e.IndicatorsName2, '' DeptName, "
+ " c.Sort SortPj, d.Sort SortAc, e.Sort SortInt2, 0 As SortStd, 0 As SortSide "
+ " From Projects c Inner Join Activity d On c.ProjectsCode = d.ProjectsCode "
+ " Left Join dtStrategies SS On c.ProjectsCode = SS.ProjectsCode "
+ " Inner Join Indicators2 e On d.ActivityCode = e.ActivityCode "
+ " Where e.DelFlag = 0 "
+ " And d.StudyYear = '" + ddlSearchYear.SelectedValue + "' And d.SchoolID = '" + CurrentUser.SchoolID + "' "
+ " And e.Indicators2Code Not In(Select Indicators2Code From Evaluation) ";
}
else
{
if ((ddlSearchDept.SelectedIndex != 0) && (ddlSearchEmp.SelectedIndex == 0))
{
StrSql = StrSql + " Union "
+ " Select '' As EvaluationCode, '' As StandardCode, '' As StandardName, '' As SideName, "
+ " c.ProjectsCode, c.ProjectsName, d.ActivityCode, d.ActivityName, d.SDate, d.EDate, d.Status, "
+ " 0 As OffAll, 0 As OffThat, 0 As APercent, 0 As CkCriterion, e.Indicators2Code, e.IndicatorsName2, '' DeptName, "
+ " c.Sort SortPj, d.Sort SortAc, e.Sort SortInt2, 0 As SortStd, 0 As SortSide "
+ " From Projects c Inner Join Activity d On c.ProjectsCode = d.ProjectsCode "
+ " Left Join dtStrategies SS On c.ProjectsCode = SS.ProjectsCode "
+ " Inner Join Indicators2 e On d.ActivityCode = e.ActivityCode "
+ " Inner Join dtAcDept g On d.ActivityCode = g.ActivityCode "
+ " Where e.DelFlag = 0 "
+ " And d.StudyYear = '" + ddlSearchYear.SelectedValue + "' And d.SchoolID = '" + CurrentUser.SchoolID + "' "
+ " And e.Indicators2Code Not In(Select Indicators2Code From Evaluation) "
+ " And g.DeptCode = '" + ddlSearchDept.SelectedValue + "' ";
}
if (ddlSearchEmp.SelectedIndex != 0)
{
StrSql = StrSql + " Union "
+ " Select '' As EvaluationCode, '' As StandardCode, '' As StandardName, '' As SideName, "
+ " c.ProjectsCode, c.ProjectsName, d.ActivityCode, d.ActivityName, d.SDate, d.EDate, d.Status, "
+ " 0 As OffAll, 0 As OffThat, 0 As APercent, 0 As CkCriterion, e.Indicators2Code, e.IndicatorsName2, '' DeptName, "
+ " c.Sort SortPj, d.Sort SortAc, e.Sort SortInt2, 0 As SortStd, 0 As SortSide "
+ " From Projects c Inner Join Activity d On c.ProjectsCode = d.ProjectsCode "
+ " Left Join dtStrategies SS On c.ProjectsCode = SS.ProjectsCode "
+ " Inner Join Indicators2 e On d.ActivityCode = e.ActivityCode "
+ " Inner Join dtAcEmp g On d.ActivityCode = g.ActivityCode "
+ " Where e.DelFlag = 0 "
+ " And d.StudyYear = '" + ddlSearchYear.SelectedValue + "' And d.SchoolID = '" + CurrentUser.SchoolID + "' "
+ " And e.Indicators2Code Not In(Select Indicators2Code From Evaluation) "
+ " And g.EmpCode = '" + ddlSearchEmp.SelectedValue + "'";
}
}
//if (ddlSearch2.SelectedIndex != 0)
//{
// StrSql = StrSql + " And SS.StrategiesCode = '" + ddlSearch2.SelectedValue + "' ";
//}
if (ddlSearch.SelectedIndex != 0)
{
StrSql = StrSql + " And c.ProjectsCode = '" + ddlSearch.SelectedValue + "' ";
}
if (ddlSearch1.SelectedIndex != 0)
{
StrSql = StrSql + " And d.ActivityCode = '" + ddlSearch1.SelectedValue + "' ";
}
if (ddlSearch3.SelectedIndex != 0)
{
StrSql = StrSql + " And e.Indicators2Code = '" + ddlSearch3.SelectedValue + "' ";
}
if (txtSearch.Text != "")
{
StrSql = StrSql + " And (d.ActivityName Like '%" + txtSearch.Text + "%' Or e.IndicatorsName2 Like '%" + txtSearch.Text + "%') ";
}
}
DataView dv = Conn.Select(string.Format(StrSql + " Order By SortPj Desc, SortAc Desc, SortInt2 Desc, SortStd, SortSide", strSideName, strStandardName));
for (int j = 0; j < dv.Count; j++)
{
dv[j]["DeptName"] = btc.getAcDeptName(dv[j]["ActivityCode"].ToString());
}
if (dv.Count != 0)
{
btnDelete.Visible = true;
}
else
{
btnDelete.Visible = false;
}
GridView1.DataSource = dv;
GridView1.CheckListDataField = "EvaluationCode";
lblSearchTotal.InnerText = dv.Count.ToString();
GridView1.DataBind();
StrSql = " Select a.EvaluationCode, a.StandardCode, a.ProjectsCode, a.ActivityCode, a.Indicators2Code, {1} + Cast(d.Sort As nVarChar) + '.' + Cast(b.Sort As nVarChar) + ' - ' + b.StandardName StandardName, "
+ " d.SideCode, {0} + Cast(d.Sort As nVarChar) + ' - ' + d.SideName SideName "
+ " From Evaluation a, Standard b, Projects P, Activity Ac, Side d "
+ " Where a.StandardCode = b.StandardCode And b.SideCode = d.SideCode And a.ProjectsCode = P.ProjectsCode And a.ActivityCode = Ac.ActivityCode "
+ " And a.Indicators2Code = '" + ddlIndicators2.SelectedValue + "' ";
DataView dv1 = Conn.Select(string.Format(StrSql + " Order By d.Sort, b.Sort", strSideName, strStandardName));
GridView2.DataSource = dv1;
GridView2.DataBind();
}
public override void DataBind()
{
DataView dv1 = Conn.Select("Select StudyYear From StudyYear Where DelFlag = 0 And Df = 1");
string studyYear = (DateTime.Now.Year + 543).ToString();
if (dv1.Count != 0)
{
studyYear = dv1[0]["StudyYear"].ToString();
}
string StrSql = "";
StrSql = " Select a.ProjectsCode, a.ProjectsName, b.ActivityCode, b.ActivityName, b.Status, "
+ " IsNull(b.TotalAmount, 0) TotalAmount, IsNull(b.TotalAmount2, 0) TotalAmount2, "
+ " b.SDate, b.EDate, 0.0 As TotalBalance, '' DeptName, '' EmpName, IsNull(b.ActivityStatus, 0) As ActivityStatus "
+ " From Projects a, Activity b "
+ " Where a.ProjectsCode = b.ProjectsCode And b.DelFlag = 0 "
+ " And a.StudyYear = '" + studyYear + "' And b.SchoolID = '" + CurrentUser.SchoolID + "' ";
if (ddlSearchDept.SelectedIndex != 0)
{
StrSql = " Select a.ProjectsCode, a.ProjectsName, b.ActivityCode, b.ActivityName, b.Status, "
+ " IsNull(b.TotalAmount, 0) TotalAmount, IsNull(b.TotalAmount2, 0) TotalAmount2, "
+ " b.SDate, b.EDate, 0.0 As TotalBalance, '' DeptName, '' EmpName, IsNull(b.ActivityStatus, 0) As ActivityStatus "
+ " From Projects a, Activity b, dtAcDept c "
+ " Where a.ProjectsCode = b.ProjectsCode And b.DelFlag = 0 And b.ActivityCode = c.ActivityCode "
+ " And a.StudyYear = '" + studyYear + "' And b.SchoolID = '" + CurrentUser.SchoolID + "' "
+ " And c.DeptCode = '" + ddlSearchDept.SelectedValue + "'";
}
if (ddlSearchEmp.SelectedIndex != 0)
{
if (ddlSearchDept.SelectedIndex == 0)
{
StrSql = " Select a.ProjectsCode, a.ProjectsName, b.ActivityCode, b.ActivityName, b.Status, "
+ " IsNull(b.TotalAmount, 0) TotalAmount, IsNull(b.TotalAmount2, 0) TotalAmount2, "
+ " b.SDate, b.EDate, 0.0 As TotalBalance, '' DeptName, '' EmpName, IsNull(b.ActivityStatus, 0) As ActivityStatus "
+ " From Projects a, Activity b, dtAcEmp c "
+ " Where a.ProjectsCode = b.ProjectsCode And b.DelFlag = 0 And b.ActivityCode = c.ActivityCode "
+ " And a.StudyYear = '" + studyYear + "' And b.SchoolID = '" + CurrentUser.SchoolID + "' "
+ " And c.EmpCode = '" + ddlSearchEmp.SelectedValue + "'";
}
else
{
StrSql = " Select a.ProjectsCode, a.ProjectsName, b.ActivityCode, b.ActivityName, b.Status, "
+ " IsNull(b.TotalAmount, 0) TotalAmount, IsNull(b.TotalAmount2, 0) TotalAmount2, "
+ " b.SDate, b.EDate, 0.0 As TotalBalance, '' DeptName, '' EmpName, IsNull(b.ActivityStatus, 0) As ActivityStatus "
+ " From Projects a, Activity b, dtAcEmp c, dtAcDept d "
+ " Where a.ProjectsCode = b.ProjectsCode And b.DelFlag = 0 "
+ " And b.ActivityCode = c.ActivityCode And b.ActivityCode = d.ActivityCode "
+ " And a.StudyYear = '" + studyYear + "' And b.SchoolID = '" + CurrentUser.SchoolID + "' "
+ " And c.EmpCode = '" + ddlSearchEmp.SelectedValue + "' And d.DeptCode = '" + ddlSearchDept.SelectedValue + "' ";
}
}
if (rbtlStatus.SelectedIndex != 5)
{
StrSql = StrSql + " And b.Status = '" + rbtlStatus.SelectedValue + "'";
}
DataView dv = Conn.Select(string.Format(StrSql + " Order By b.Sort "));
for (int j = 0; j < dv.Count; j++)
{
dv[j]["DeptName"] = btc.getAcDeptName(dv[j]["ActivityCode"].ToString());
dv[j]["EmpName"] = btc.getAcEmpName(dv[j]["ActivityCode"].ToString());
if (btc.getNTotalAmount(dv[j]["ActivityCode"].ToString()) != 0)
{
dv[j]["TotalAmount2"] = btc.getNTotalAmount(dv[j]["ActivityCode"].ToString());
}
dv[j]["TotalBalance"] = (Convert.ToDecimal(dv[j]["TotalAmount"]) - Convert.ToDecimal(dv[j]["TotalAmount2"])).ToString();
}
GridView1.DataSource = dv;
lblSearchTotal.InnerHtml = dv.Count.ToString();
GridView1.DataBind();
StrSql = @"Select P.ProjectsCode, P.ProjectsName, P.IsApprove, P.UserApprove, P.DateApprove, P.Comment,
PD.EmpID, PD.IsApprove As IsApprove2, PD.CreateDate As DateApprove2, PD.Comment As Comment2
From Projects P Left Join ProjectsApproveDetail PD On PD.ProjectsCode = P.ProjectsCode
Where P.DelFlag = 0 And P.StudyYear = '{0}' And P.SchoolID = '{1}' ";
dvApproveFlow = Conn.Select(string.Format(StrSql, ddlSearchYear.SelectedValue, CurrentUser.SchoolID));
StrSql = @"Select P.ProjectsCode, IsNull(Sum(CD.TotalMoney), 0) TotalMoney From Projects P
Left Join Activity A On P.ProjectsCode = A.ProjectsCode
Left Join CostsDetail CD On A.ActivityCode = CD.ActivityCode
Where P.DelFlag = 0 And P.StudyYear = '{0}' Group By P.ProjectsCode ";
dvTotalAmout = Conn.Select(string.Format(StrSql, ddlSearchYear.SelectedValue));
StrSql = @" Select a.ProjectsCode, a.StudyYear, a.ProjectsName, a.Df, Ep.EmpID, Ep.EmpName,
a.Sort, e.DeptName, a.IsApprove, a.ProjectRegistration, a.IsWait, a.CreateDate
From Projects a Left Join dtStrategies S On a.ProjectsCode = S.ProjectsCode
Left Join ProjectsApproveDetail PD On PD.ProjectsCode = a.ProjectsCode
Left Join Employee d On PD.EmpID = d.EmpID
Left Join Employee Ep On a.CreateUser = Ep.EmpID
Left Join Department e On a.DeptCode = e.DeptCode
Left Join MainSubDepartment MSD On e.MainSubDeptCode = MSD.MainSubDeptCode
Left Join MainDepartment MD On MSD.MainDeptCode = MD.MainDeptCode
Where a.DelFlag = 0 And d.DelFlag = 0 And PD.IsApprove Is Null And d.hideFlag = 0 And a.StudyYear = '{0}' And a.SchoolID = '{1}' ";
if (ddlSearchMainDept2.SelectedIndex != 0)
{
StrSql += " And MD.MainDeptCode = '" + ddlSearchMainDept2.SelectedValue + "'";
}
if (ddlSearchMainSubDept2.SelectedIndex != 0)
{
StrSql += " And MSD.MainSubDeptCode = '" + ddlSearchMainSubDept2.SelectedValue + "'";
}
if (ddlSearchDept2.SelectedIndex != 0)
{
StrSql += " And e.DeptCode = '" + ddlSearchDept2.SelectedValue + "'";
}
if (ddlSearchEmp2.SelectedIndex != 0)
{
StrSql += " And a.CreateUser = '******'";
}
if (CurrentUser.RoleLevel > 1) //&& (CurrentUser.RoleLevel != 98))
{
StrSql += " And PD.EmpID = '" + CurrentUser.ID + "'";
}
DataView dv0 = Conn.Select(string.Format(StrSql + " Group By a.ProjectsCode, a.StudyYear, a.ProjectsName, a.Df, Ep.EmpID, Ep.EmpName, a.Sort, e.DeptName, a.IsApprove, a.ProjectRegistration, a.IsWait, a.CreateDate Order By a.CreateDate Desc, a.ProjectRegistration Desc ", ddlSearchYear.SelectedValue, CurrentUser.SchoolID));
DataGridView1.DataSource = dv0;
lblSearchTotal2.InnerText = dv0.Count.ToString("#,##0");
DataGridView1.DataBind();
}
public override void DataBind() //¡Ô¨¡ÃÃÁ
{
DataView dv, dv1;
Connection Conn = new Connection();
BTC btc = new BTC();
string strSql = " Select a.ActivityCode, '¡Ô¨¡ÃÃÁ - ' + a.ActivityName ActivityName, a.Sort, Sum(b.APercent) CkAPercent, '' DeptName, b.ProjectsCode, b.IndicatorsCode, '' As FullName "
+ " From Activity a, Evaluation b "
+ " Where a.DelFlag = 0 And b.DelFlag = 0 And a.ActivityCode = b.ActivityCode "
+ " And b.IndicatorsCode = '" + Request.QueryString["idtid"] + "' And a.ProjectsCode = '" + Request.QueryString["pjid"] + "' "
+ " And b.StudyYear = '" + ddlYearB.SelectedValue + "' And b.SchoolID = '" + CurrentUser.SchoolID + "' ";
if (ddlSearchDept.SelectedIndex != 0)
{
strSql = " Select a.ActivityCode, '¡Ô¨¡ÃÃÁ - ' + a.ActivityName ActivityName, a.Sort, Sum(b.APercent) CkAPercent, '' DeptName, b.ProjectsCode, b.IndicatorsCode, '' As FullName "
+ " From Activity a, Evaluation b, dtAcDept c "
+ " Where a.DelFlag = 0 And b.DelFlag = 0 And a.ActivityCode = b.ActivityCode And a.ActivityCode = c.ActivityCode "
+ " And b.IndicatorsCode = '" + Request.QueryString["idtid"] + "' And a.ProjectsCode = '" + Request.QueryString["pjid"] + "' "
+ " And b.StudyYear = '" + ddlYearB.SelectedValue + "' And b.SchoolID = '" + CurrentUser.SchoolID + "' "
+ " And c.DeptCode = '" + ddlSearchDept.SelectedValue + "' ";
}
if (ddlSearchEmp.SelectedIndex != 0)
{
strSql = " Select a.ActivityCode, '¡Ô¨¡ÃÃÁ - ' + a.ActivityName ActivityName, a.Sort, Sum(b.APercent) CkAPercent, '' DeptName, b.ProjectsCode, b.IndicatorsCode, '' As FullName "
+ " From Activity a, Evaluation b, dtAcEmp c "
+ " Where a.DelFlag = 0 And b.DelFlag = 0 And a.ActivityCode = b.ActivityCode And a.ActivityCode = c.ActivityCode "
+ " And b.IndicatorsCode = '" + Request.QueryString["idtid"] + "' And a.ProjectsCode = '" + Request.QueryString["pjid"] + "' "
+ " And b.StudyYear = '" + ddlYearB.SelectedValue + "' And b.SchoolID = '" + CurrentUser.SchoolID + "' "
+ " And c.EmpCode = '" + ddlSearchEmp.SelectedValue + "' ";
}
dv = Conn.Select(strSql + " Group By a.ActivityCode, a.ActivityName, a.Sort, b.ProjectsCode, b.IndicatorsCode Order By a.Sort");
for (int j = 0; j < dv.Count; j++)
{
dv[j]["DeptName"] = btc.getAcDeptName(dv[j]["ActivityCode"].ToString());
}
double CkAPercent;
string strColor;
string strEva = "";
for (int i = 0; i < dv.Count; i++)
{
strSql = " Select a.ActivityCode "
+ " From Activity a, Evaluation b "
+ " Where a.DelFlag = 0 And b.DelFlag = 0 And a.ActivityCode = b.ActivityCode "
+ " And b.IndicatorsCode = '" + Request.QueryString["idtid"] + "' And a.ProjectsCode = '" + Request.QueryString["pjid"] + "' "
+ " And b.ActivityCode = '" + dv[i]["ActivityCode"].ToString() + "' "
+ " And b.StudyYear = '" + ddlYearB.SelectedValue + "' And b.SchoolID = '" + CurrentUser.SchoolID + "' ";
if (ddlSearchDept.SelectedIndex != 0)
{
strSql = " Select a.ActivityCode "
+ " From Activity a, Evaluation b, dtAcDept c "
+ " Where a.DelFlag = 0 And b.DelFlag = 0 And a.ActivityCode = b.ActivityCode And a.ActivityCode = c.ActivityCode "
+ " And b.IndicatorsCode = '" + Request.QueryString["idtid"] + "' And a.ProjectsCode = '" + Request.QueryString["pjid"] + "' "
+ " And b.ActivityCode = '" + dv[i]["ActivityCode"].ToString() + "' "
+ " And b.StudyYear = '" + ddlYearB.SelectedValue + "' And b.SchoolID = '" + CurrentUser.SchoolID + "' "
+ " And c.DeptCode = '" + ddlSearchDept.SelectedValue + "' ";
}
if (ddlSearchEmp.SelectedIndex != 0)
{
strSql = " Select a.ActivityCode "
+ " From Activity a, Evaluation b, dtAcEmp c "
+ " Where a.DelFlag = 0 And b.DelFlag = 0 And a.ActivityCode = b.ActivityCode And a.ActivityCode = c.ActivityCode "
+ " And b.IndicatorsCode = '" + Request.QueryString["idtid"] + "' And a.ProjectsCode = '" + Request.QueryString["pjid"] + "' "
+ " And b.ActivityCode = '" + dv[i]["ActivityCode"].ToString() + "' "
+ " And b.StudyYear = '" + ddlYearB.SelectedValue + "' And b.SchoolID = '" + CurrentUser.SchoolID + "' "
+ " And c.EmpCode = '" + ddlSearchEmp.SelectedValue + "' ";
}
dv1 = Conn.Select(strSql);
CkAPercent = (Convert.ToDouble(dv[i]["CkAPercent"]) * 100) / (Convert.ToDouble(dv1.Count) * 100);
if (CkAPercent < 50)
{
strColor = "Red";//"#f87373";
strEva = "(äÁè¼èÒ¹)";
}
else
{
strColor = "#0B610B";
strEva = "(¼èÒ¹)";
}
dv[i]["FullName"] = string.Format("<img onclick=\"getPopUpActivity('{5}')\" src=\"../Image/icon/" + ((CkAPercent >= 50) ? "ballg" : "ballr") + ".png\" style=\"border:none;width:32px;height:35px;\"/> <span onclick=\"GoToIndicators2('{5}', '{6}', '{7}');\" style=\"color:" + ((rbtlView1.Checked) ? "#6E6E6E" : strColor) + ";font-size:" + btc.fontView2 + "pt;font-weight:bold;width:630px;overflow:hidden;\" title=\"{4}\">{0}</span><span style=\"width:100px; text-align:right ;float:right; margin:20px 20px 0 0;color:" + strColor + ";font-size:" + btc.fontView2 + "pt; font-weight:bold;\">{2}</span><span style=\"width:50px; text-align:right; float:Right; padding:20px 55px 0 0; color:" + strColor + "; font-size:" + btc.fontView2 + "pt; font-weight:bold;\">{1}</span><span style=\"float:right; width:120px; padding:20px 45px 0 0; color:" + strColor + "; font-size:" + btc.fontView2 + "pt; text-align:right; font-weight:bold;\">{3}%</span><p style=\"clear:both; float:none;\"></p><hr />", (dv[i]["ActivityName"].ToString().Length > btc.strCut4 ? dv[i]["ActivityName"].ToString().Substring(0, btc.strCut4) + "..." : dv[i]["ActivityName"]), "", strEva, " " + CkAPercent.ToString("#,##0.00") + " ", dv[i]["DeptName"], dv[i]["ActivityCode"].ToString(), Request.QueryString["pjid"], Request.QueryString["idtid"]);
//tNodeActivity.Text = string.Format("<img src=\"../Image/icon/" + ((CkAPercent >= 50) ? "icon5g" : "icon5r") + ".png\" style=\"float:left;border:none;padding:0 0 0 35px;\"/> <span title=\"˹èǧҹ : {3}\" style=\"font-size:11pt;color:" + ((rbtlView1.Checked) ? "#6E6E6E" : strColor) + ";\">{0}</span> <span style=\"font-size:12pt;color:" + strColor + ";\">{2}%</span> <span style=\"font-size:12pt;color:" + strColor + ";\">{1}</span>", dv[i]["ActivityName"].ToString(), strEva, CkAPercent.ToString("#,##0.00"), dv[i]["DeptName"].ToString());
Repeater1.DataSource = dv;
Repeater1.DataBind();
}
}
public override void DataBind()
{
string StrSql = "";
if (ddlSearch.SelectedIndex == 0)
{
if (ddlSearchDept.SelectedIndex == 0)
{
StrSql = " Select a.ProjectsCode, a.StrategiesCode, a.StudyYear,'â¤Ã§¡ÒÃ' + a.ProjectsName As FullName, a.Df, "
+ " a.Sort, b.Sort Sort1, b.StrategiesName, IsNull(Sum(c.TotalAmount), 0) TotalAmount1, "
+ " IsNull(Sum(c.TotalAmount2), 0) TotalAmount2, '' As AcCode, "
+ " (IsNull(Sum(c.TotalAmount), 0) - IsNull(Sum(c.TotalAmount2), 0)) TotalBalance, '-' As DeptName "
+ " From Projects a Left Join Strategies b On a.StrategiesCode = b.StrategiesCode "
+ " Left Join Activity c On a.ProjectsCode = c.ProjectsCode And c.DelFlag = 0 ";
if (btc.ckIdentityName("ckBudgetYear"))
{
StrSql += " And c.StudyYear = '" + ddlSearchYear.SelectedValue + "' ";
}
else
{
StrSql += " And c.BudgetYear = '" + ddlSearchYear.SelectedValue + "' ";
}
StrSql += " Where a.DelFlag = 0 And a.StudyYear = '" + ddlSearchYear.SelectedValue + "' And a.SchoolID = '" + CurrentUser.SchoolID + "' ";
}
else
{
StrSql = " Select a.ProjectsCode, a.StrategiesCode, a.StudyYear,'â¤Ã§¡ÒÃ' + a.ProjectsName As FullName, a.Df, "
+ " a.Sort, b.Sort Sort1, b.StrategiesName, IsNull(Sum(c.TotalAmount), 0) TotalAmount1, "
+ " IsNull(Sum(c.TotalAmount2), 0) TotalAmount2, '' As AcCode, "
+ " (IsNull(Sum(c.TotalAmount), 0) - IsNull(Sum(c.TotalAmount2), 0)) TotalBalance, '-' As DeptName "
+ " From Projects a Left Join Strategies b On a.StrategiesCode = b.StrategiesCode "
+ " Left Join Activity c On a.ProjectsCode = c.ProjectsCode And c.DelFlag = 0 ";
if (btc.ckIdentityName("ckBudgetYear"))
{
StrSql += " And c.StudyYear = '" + ddlSearchYear.SelectedValue + "' ";
}
else
{
StrSql += " And c.BudgetYear = '" + ddlSearchYear.SelectedValue + "' ";
}
//+ " Left Join dtAcDept d On c.ActivityCode = d.ActivityCode "
StrSql += " Where a.DelFlag = 0 And a.StudyYear = '" + ddlSearchYear.SelectedValue + "' "
+ " And c.DeptCode = '" + ddlSearchDept.SelectedValue + "' "
+ " And a.SchoolID = '" + CurrentUser.SchoolID + "' ";
}
}
else
{
if (ddlSearchDept.SelectedIndex == 0)
{
StrSql = " Select a.ProjectsCode, a.StrategiesCode, a.StudyYear, a.ProjectsName, a.Df, "
+ " a.Sort, b.Sort Sort1, b.StrategiesName, c.ActivityCode AcCode,'¡Ô¨¡ÃÃÁ' + c.ActivityName As FullName, "
+ " IsNull(c.TotalAmount, 0) TotalAmount1, "
+ " IsNull(c.TotalAmount2, 0) TotalAmount2, "
+ " (IsNull(c.TotalAmount, 0) - IsNull(c.TotalAmount2, 0)) TotalBalance, '' As DeptName "
+ " From Projects a Left Join Strategies b On a.StrategiesCode = b.StrategiesCode "
+ " Left Join Activity c On a.ProjectsCode = c.ProjectsCode And c.DelFlag = 0 ";
if (btc.ckIdentityName("ckBudgetYear"))
{
StrSql += " And c.StudyYear = '" + ddlSearchYear.SelectedValue + "' ";
}
else
{
StrSql += " And c.BudgetYear = '" + ddlSearchYear.SelectedValue + "' ";
}
StrSql += " Left Join dtAcDept d On c.ActivityCode = d.ActivityCode "
+ " Where a.DelFlag = 0 And a.StudyYear = '" + ddlSearchYear.SelectedValue + "' "
+ " And a.SchoolID = '" + CurrentUser.SchoolID + "' "
+ " And a.ProjectsCode = '" + ddlSearch.SelectedValue + "'";
}
else
{
StrSql = " Select a.ProjectsCode, a.StrategiesCode, a.StudyYear, a.ProjectsName, a.Df, "
+ " a.Sort, b.Sort Sort1, b.StrategiesName, c.ActivityCode AcCode,'¡Ô¨¡ÃÃÁ' + c.ActivityName As FullName, "
+ " IsNull(c.TotalAmount, 0) TotalAmount1, "
+ " IsNull(c.TotalAmount2, 0) TotalAmount2, "
+ " (IsNull(c.TotalAmount, 0) - IsNull(c.TotalAmount2, 0)) TotalBalance, '' As DeptName "
+ " From Projects a Left Join Strategies b On a.StrategiesCode = b.StrategiesCode "
+ " Left Join Activity c On a.ProjectsCode = c.ProjectsCode And c.DelFlag = 0 ";
if (btc.ckIdentityName("ckBudgetYear"))
{
StrSql += " And c.StudyYear = '" + ddlSearchYear.SelectedValue + "' ";
}
else
{
StrSql += " And c.BudgetYear = '" + ddlSearchYear.SelectedValue + "' ";
}
//+ " Left Join dtAcDept d On c.ActivityCode = d.ActivityCode "
StrSql += " Where a.DelFlag = 0 And a.StudyYear = '" + ddlSearchYear.SelectedValue + "' "
+ " And a.SchoolID = '" + CurrentUser.SchoolID + "' "
+ " And c.DeptCode = '" + ddlSearchDept.SelectedValue + "' "
+ " And a.ProjectsCode = '" + ddlSearch.SelectedValue + "'";
}
}
if (txtSearch.Text != "")
{
StrSql += " And ( a.ProjectsName Like '%" + txtSearch.Text + "%' Or c.ActivityName Like '%" + txtSearch.Text + "%' ) ";
}
DataView dv;
if (ddlSearch.SelectedIndex == 0)
{
StrSql += " Group By a.ProjectsCode, a.StrategiesCode, a.StudyYear, a.ProjectsName, a.Df, a.Sort, b.Sort, b.StrategiesName ";
}
else
{
StrSql += " Group By a.ProjectsCode, a.StrategiesCode, a.StudyYear, a.ProjectsName, a.Df, a.Sort, b.Sort, b.StrategiesName, c.ActivityCode, c.ActivityName, c.TotalAmount, c.TotalAmount2 ";
}
dv = Conn.Select(string.Format(StrSql + " Order By b.Sort, a.Sort "));
for (int j = 0; j < dv.Count; j++)
{
if (dv[j]["DeptName"].ToString() != "-")
{
dv[j]["DeptName"] = btc.getAcDeptName(dv[j]["AcCode"].ToString());
}
}
GridView1.DataSource = dv;
lblSearchTotal.InnerText = dv.Count.ToString();
GridView1.DataBind();
//----GrandTotal-----------
if (ddlSearchDept.SelectedIndex == 0)
{
StrSql = " Select IsNull(Sum(c.TotalAmount), 0) TotalAmount1, "
+ " IsNull(Sum(c.TotalAmount2), 0) TotalAmount2, "
+ " (IsNull(Sum(c.TotalAmount), 0) - IsNull(Sum(c.TotalAmount2), 0)) TotalBalance "
+ " From Projects a Left Join Strategies b On a.StrategiesCode = b.StrategiesCode "
+ " Left Join Activity c On a.ProjectsCode = c.ProjectsCode "
+ " Where a.DelFlag = 0 And c.DelFlag = 0 "
+ " And a.SchoolID = '" + CurrentUser.SchoolID + "' ";
if (btc.ckIdentityName("ckBudgetYear"))
{
StrSql += " And c.StudyYear = '" + ddlSearchYear.SelectedValue + "' ";
}
else
{
StrSql += " And c.BudgetYear = '" + ddlSearchYear.SelectedValue + "' ";
}
}
else
{
StrSql = " Select IsNull(Sum(c.TotalAmount), 0) TotalAmount1, "
+ " IsNull(Sum(c.TotalAmount2), 0) TotalAmount2, "
+ " (IsNull(Sum(c.TotalAmount), 0) - IsNull(Sum(c.TotalAmount2), 0)) TotalBalance "
+ " From Projects a Left Join Strategies b On a.StrategiesCode = b.StrategiesCode "
+ " Left Join Activity c On a.ProjectsCode = c.ProjectsCode "
+ " Left Join dtAcDept d On c.ActivityCode = d.ActivityCode "
+ " Where a.DelFlag = 0 And c.DelFlag = 0 "
+ " And d.DeptCode = '" + ddlSearchDept.SelectedValue + "' "
+ " And a.SchoolID = '" + CurrentUser.SchoolID + "' ";
if (btc.ckIdentityName("ckBudgetYear"))
{
StrSql += " And c.StudyYear = '" + ddlSearchYear.SelectedValue + "' ";
}
else
{
StrSql += " And c.BudgetYear = '" + ddlSearchYear.SelectedValue + "' ";
}
}
DataView dvTotal = Conn.Select(StrSql);
ToltalBudget.InnerHtml = (dvTotal.Count != 0) ? Convert.ToDecimal(dvTotal[0]["TotalAmount1"]).ToString("#,##0.00") : "0.00";
ToltalBudget2.InnerHtml = (dvTotal.Count != 0) ? Convert.ToDecimal(dvTotal[0]["TotalAmount2"]).ToString("#,##0.00") : "0.00";
TotalBalance.InnerHtml = (dvTotal.Count != 0) ? Convert.ToDecimal(dvTotal[0]["TotalBalance"]).ToString("#,##0.00") : "0.00";
//----EndGrandTotal-----------
}
public override void DataBind()
{
DataView dv1 = Conn.Select("Select StudyYear From StudyYear Where DelFlag = 0 And Df = 1");
string studyYear = (DateTime.Now.Year + 543).ToString();
if (dv1.Count != 0)
{
studyYear = dv1[0]["StudyYear"].ToString();
}
string StrSql = "";
StrSql = " Select a.ProjectsCode, a.ProjectsName, b.ActivityCode, b.ActivityName, b.Status, "
+ " IsNull(b.TotalAmount, 0) TotalAmount, IsNull(b.TotalAmount2, 0) TotalAmount2, "
+ " b.SDate, b.EDate, 0.0 As TotalBalance, '' DeptName, '' EmpName, IsNull(b.ActivityStatus, 0) As ActivityStatus "
+ " From Projects a, Activity b "
+ " Where a.ProjectsCode = b.ProjectsCode And b.DelFlag = 0 "
+ " And a.StudyYear = '" + studyYear + "' And b.SchoolID = '" + CurrentUser.SchoolID + "' ";
if (ddlSearchDept.SelectedIndex != 0)
{
StrSql = " Select a.ProjectsCode, a.ProjectsName, b.ActivityCode, b.ActivityName, b.Status, "
+ " IsNull(b.TotalAmount, 0) TotalAmount, IsNull(b.TotalAmount2, 0) TotalAmount2, "
+ " b.SDate, b.EDate, 0.0 As TotalBalance, '' DeptName, '' EmpName, IsNull(b.ActivityStatus, 0) As ActivityStatus "
+ " From Projects a, Activity b, dtAcDept c "
+ " Where a.ProjectsCode = b.ProjectsCode And b.DelFlag = 0 And b.ActivityCode = c.ActivityCode "
+ " And a.StudyYear = '" + studyYear + "' And b.SchoolID = '" + CurrentUser.SchoolID + "' "
+ " And c.DeptCode = '" + ddlSearchDept.SelectedValue + "'";
}
if (ddlSearchEmp.SelectedIndex != 0)
{
if (ddlSearchDept.SelectedIndex == 0)
{
StrSql = " Select a.ProjectsCode, a.ProjectsName, b.ActivityCode, b.ActivityName, b.Status, "
+ " IsNull(b.TotalAmount, 0) TotalAmount, IsNull(b.TotalAmount2, 0) TotalAmount2, "
+ " b.SDate, b.EDate, 0.0 As TotalBalance, '' DeptName, '' EmpName, IsNull(b.ActivityStatus, 0) As ActivityStatus "
+ " From Projects a, Activity b, dtAcEmp c "
+ " Where a.ProjectsCode = b.ProjectsCode And b.DelFlag = 0 And b.ActivityCode = c.ActivityCode "
+ " And a.StudyYear = '" + studyYear + "' And b.SchoolID = '" + CurrentUser.SchoolID + "' "
+ " And c.EmpCode = '" + ddlSearchEmp.SelectedValue + "'";
}
else
{
StrSql = " Select a.ProjectsCode, a.ProjectsName, b.ActivityCode, b.ActivityName, b.Status, "
+ " IsNull(b.TotalAmount, 0) TotalAmount, IsNull(b.TotalAmount2, 0) TotalAmount2, "
+ " b.SDate, b.EDate, 0.0 As TotalBalance, '' DeptName, '' EmpName, IsNull(b.ActivityStatus, 0) As ActivityStatus "
+ " From Projects a, Activity b, dtAcEmp c, dtAcDept d "
+ " Where a.ProjectsCode = b.ProjectsCode And b.DelFlag = 0 "
+ " And b.ActivityCode = c.ActivityCode And b.ActivityCode = d.ActivityCode "
+ " And a.StudyYear = '" + studyYear + "' And b.SchoolID = '" + CurrentUser.SchoolID + "' "
+ " And c.EmpCode = '" + ddlSearchEmp.SelectedValue + "' And d.DeptCode = '" + ddlSearchDept.SelectedValue + "' ";
}
}
if (rbtlStatus.SelectedIndex != 5)
{
StrSql = StrSql + " And b.Status = '" + rbtlStatus.SelectedValue + "'";
}
DataView dv = Conn.Select(string.Format(StrSql + " Order By b.Sort "));
for (int j = 0; j < dv.Count; j++)
{
dv[j]["DeptName"] = btc.getAcDeptName(dv[j]["ActivityCode"].ToString());
dv[j]["EmpName"] = btc.getAcEmpName(dv[j]["ActivityCode"].ToString());
if (btc.getNTotalAmount(dv[j]["ActivityCode"].ToString()) != 0)
{
dv[j]["TotalAmount2"] = btc.getNTotalAmount(dv[j]["ActivityCode"].ToString());
}
dv[j]["TotalBalance"] = (Convert.ToDecimal(dv[j]["TotalAmount"]) - Convert.ToDecimal(dv[j]["TotalAmount2"])).ToString();
}
GridView1.DataSource = dv;
lblSearchTotal.InnerHtml = dv.Count.ToString();
GridView1.DataBind();
}
public override void DataBind()
{
string StrSql = @" Select b.ActivityCode AcCode, '¡Ô¨¡ÃÃÁ' + b.ActivityName As FullName, b.Status,
IsNull(b.TotalAmount, 0) TotalAmount1, IsNull(b.TotalAmount2, 0) TotalAmount2, (IsNull(b.TotalAmount, 0) - IsNull(b.TotalAmount2, 0)) TotalBalance,
'' As DeptName, IsNull(b.ApproveFlag, 0) ApproveFlag, b.UpdateDate
From Projects a Left Join dtStrategies S On a.ProjectsCode = S.ProjectsCode
Left Join Activity b On a.ProjectsCode = b.ProjectsCode
Left Join ProjectsApproveDetail PD On PD.ProjectsCode = a.ProjectsCode
Left Join Department e On a.DeptCode = e.DeptCode
Left Join MainSubDepartment MSD On e.MainSubDeptCode = MSD.MainSubDeptCode
Left Join MainDepartment MD On MSD.MainDeptCode = MD.MainDeptCode
Where b.DelFlag = 0 And b.SchoolID = '{0}' And b.StudyYear = '" + ddlSearchYear.SelectedValue + "' ";
if (ddlSearchMainDept.SelectedIndex != 0)
{
StrSql += " And MD.MainDeptCode = '" + ddlSearchMainDept.SelectedValue + "'";
}
if (ddlSearchMainSubDept.SelectedIndex != 0)
{
StrSql += " And MSD.MainSubDeptCode = '" + ddlSearchMainSubDept.SelectedValue + "'";
}
if (ddlSearchDept.SelectedIndex != 0)
{
StrSql += " And e.DeptCode = '" + ddlSearchDept.SelectedValue + "'";
}
if (txtSearch.Text != "")
{
StrSql += " And b.ActivityName Like '%" + txtSearch.Text + "%' ";
}
if (ddlSearchProjects.SelectedIndex != 0)
{
StrSql += " And b.ProjectsCode = '" + ddlSearchProjects.SelectedValue + "' ";
}
StrSql += " Group By b.ActivityCode, b.ActivityName, b.TotalAmount, b.TotalAmount2, b.Status, b.ApproveFlag, b.UpdateDate ";
//if (ddlSearchDept.SelectedIndex == 0)
//{
// StrSql = " Select c.ActivityCode AcCode,'¡Ô¨¡ÃÃÁ' + c.ActivityName As FullName, c.Status, "
// + " IsNull(c.TotalAmount, 0) TotalAmount1, "
// + " IsNull(c.TotalAmount2, 0) TotalAmount2, "
// + " (IsNull(c.TotalAmount, 0) - IsNull(c.TotalAmount2, 0)) TotalBalance, '' As DeptName, IsNull(c.ApproveFlag, 0) ApproveFlag, c.UpdateDate "
// + " From Activity c "
// + " Where c.DelFlag = 0 "
// + " And c.SchoolID = '" + CurrentUser.SchoolID + "' ";
// if (rbtStudyYear.Checked)
// {
// StrSql += " And c.StudyYear = '" + ddlSearchYear.SelectedValue + "' ";
// }
// else
// {
// StrSql += " And c.BudgetYear = '" + ddlSearchYear.SelectedValue + "' ";
// }
// if (txtSearch.Text != "")
// {
// StrSql = StrSql + " And c.ActivityName Like '%" + txtSearch.Text + "%' ";
// }
// if (ddlSearchProjects.SelectedIndex != 0)
// {
// StrSql = StrSql + " And c.ProjectsCode = '" + ddlSearchProjects.SelectedValue + "' ";
// }
// StrSql = StrSql + " Group By c.ActivityCode, c.ActivityName, c.TotalAmount, c.TotalAmount2, c.Status, c.ApproveFlag, c.UpdateDate ";
//}
//else
//{
// StrSql = " Select c.ActivityCode AcCode,'¡Ô¨¡ÃÃÁ' + c.ActivityName As FullName, c.Status, "
// + " IsNull(c.TotalAmount, 0) TotalAmount1, "
// + " IsNull(c.TotalAmount2, 0) TotalAmount2, "
// + " (IsNull(c.TotalAmount, 0) - IsNull(c.TotalAmount2, 0)) TotalBalance, '' As DeptName, IsNull(c.ApproveFlag, 0) ApproveFlag, c.UpdateDate "
// + " From Activity c, dtAcDept d "
// + " Where c.DelFlag = 0 And c.ActivityCode = d.ActivityCode "
// + " And c.SchoolID = '" + CurrentUser.SchoolID + "' "
// + " And d.DeptCode = '" + ddlSearchDept.SelectedValue + "' ";
// if (rbtStudyYear.Checked)
// {
// StrSql += " And c.StudyYear = '" + ddlSearchYear.SelectedValue + "' ";
// }
// else
// {
// StrSql += " And c.BudgetYear = '" + ddlSearchYear.SelectedValue + "' ";
// }
// if (txtSearch.Text != "")
// {
// StrSql = StrSql + " And c.ActivityName Like '%" + txtSearch.Text + "%' ";
// }
// if (ddlSearchProjects.SelectedIndex != 0)
// {
// StrSql = StrSql + " And c.ProjectsCode = '" + ddlSearchProjects.SelectedValue + "' ";
// }
//}
DataView dv = Conn.Select(string.Format(StrSql, CurrentUser.SchoolID));
if (dv.Count != 0)
{
for (int j = 0; j < dv.Count; j++)
{
dv[j]["DeptName"] = btc.getAcDeptName(dv[j]["AcCode"].ToString());
}
}
//àªç¤¼ÅÃÇÁ
try
{
DataTable dt = dv.ToTable();
TotalAmount1 = Convert.ToDecimal(dt.Compute("Sum(TotalAmount1)", dv.RowFilter));
TotalAmount2 = Convert.ToDecimal(dt.Compute("Sum(TotalAmount2)", dv.RowFilter));
TotalAmount3 = Convert.ToDecimal(dt.Compute("Sum(TotalBalance)", dv.RowFilter));
}
catch (Exception ex)
{
}
GridView1.DataSource = dv;
lblSearchTotal.InnerText = dv.Count.ToString();
GridView1.DataBind();
getBudget();
//----GrandTotal-----------
if (ddlSearchDept.SelectedIndex == 0)
{
StrSql = " Select "
+ " IsNull(Sum(c.TotalAmount), 0) TotalAmount1, "
+ " IsNull(Sum(c.TotalAmount2), 0) TotalAmount2, "
+ " (IsNull(Sum(c.TotalAmount), 0) - IsNull(Sum(c.TotalAmount2), 0)) TotalBalance "
+ " From Activity c "
+ " Where c.DelFlag = 0 "
+ " And c.SchoolID = '" + CurrentUser.SchoolID + "' And c.StudyYear = '" + ddlSearchYear.SelectedValue + "' ";
}
else
{
StrSql = " Select "
+ " IsNull(Sum(c.TotalAmount), 0) TotalAmount1, "
+ " IsNull(Sum(c.TotalAmount2), 0) TotalAmount2, "
+ " (IsNull(Sum(c.TotalAmount), 0) - IsNull(Sum(c.TotalAmount2), 0)) TotalBalance "
+ " From Activity c, dtAcDept d "
+ " Where c.DelFlag = 0 And c.ActivityCode = d.ActivityCode "
+ " And c.SchoolID = '" + CurrentUser.SchoolID + "' "
+ " And d.DeptCode = '" + ddlSearchDept.SelectedValue + "' And c.StudyYear = '" + ddlSearchYear.SelectedValue + "' ";
}
DataView dvTotal = Conn.Select(StrSql);
ToltalBudget.InnerHtml = (dvTotal.Count != 0) ? Convert.ToDecimal(dvTotal[0]["TotalAmount1"]).ToString("#,##0.00") : "0.00";
ToltalBudget2.InnerHtml = (dvTotal.Count != 0) ? Convert.ToDecimal(dvTotal[0]["TotalAmount2"]).ToString("#,##0.00") : "0.00";
TotalBalance.InnerHtml = (dvTotal.Count != 0) ? Convert.ToDecimal(dvTotal[0]["TotalBalance"]).ToString("#,##0.00") : "0.00";
StrSql = @" Select IsNull(Sum(I.Subsidies), 0) Subsidies, IsNull(Sum(I.Revenue), 0) Revenue, IsNull(Sum(I.Reserve), 0) Reserve,
IsNull(Sum(I.Free), 0) Free, IsNull(Sum(I.Other), 0) Other, IsNull((Sum(I.Subsidies) + Sum(I.Revenue) + Sum(I.Reserve) + Sum(I.Free)), 0) As TotalAmount
From Income I Inner Join Department D On I.DeptCode = D.DeptCode
Inner Join MainSubDepartment MD On D.MainSubDeptCode = MD.MainSubDeptCode
Where I.DelFlag = 0 And I.StudyYear = '" + ddlSearchYear.SelectedValue + "' ";
if (ddlSearchDept.SelectedIndex != 0)
{
StrSql += " And I.DeptCode = '" + ddlSearchDept.SelectedValue + "' ";
}
if (ddlSearchMainSubDept.SelectedIndex != 0)
{
StrSql += " And MD.MainSubDeptCode = '" + ddlSearchMainSubDept.SelectedValue + "' ";
}
if (ddlSearchMainDept.SelectedIndex != 0)
{
StrSql += " And MD.MainDeptCode = '" + ddlSearchMainDept.SelectedValue + "' ";
}
DataView dvIncome = Conn.Select(string.Format(StrSql));
if (dvIncome.Count > 0)
{
lblInComeSubsidies.Text = Convert.ToDecimal(dvIncome[0]["Subsidies"]).ToString("#,##0.00");
lblInComeRevenue.Text = Convert.ToDecimal(dvIncome[0]["Revenue"]).ToString("#,##0.00");
lblInComeFree.Text = Convert.ToDecimal(dvIncome[0]["Free"]).ToString("#,##0.00");
lblInComeReserve.Text = Convert.ToDecimal(dvIncome[0]["Reserve"]).ToString("#,##0.00");
lblInComeTotal.Text = Convert.ToDecimal(dvIncome[0]["TotalAmount"]).ToString("#,##0.00");
}
else
{
lblInComeSubsidies.Text = "0.00";
lblInComeRevenue.Text = "0.00";
lblInComeFree.Text = "0.00";
lblInComeReserve.Text = "0.00";
lblInComeTotal.Text = "0.00";
}
//----EndGrandTotal----------
}
public override void DataBind()
{
string strSql = @" Select a.ProjectsCode, a.ProjectsName, b.ActivityCode, b.ActivityName, b.SDate , b.EDate, '' As DeptName, b.Status, b.Df, b.ApproveFlag,
b.CostsType, IsNull(b.TotalAmount, 0) TotalAmount, IsNull(b.TotalAmount2, 0) TotalAmount2, 0.0 As TotalBalance,
IsNull((Cast(b.Term As nVarChar) + '/' + Cast(b.YearB As nVarChar)), '') Term, IsNull(b.ActivityStatus, 0) As ActivityStatus,
a.Sort As SortPrj, b.Sort As SortAc
From Projects a Left Join dtStrategies S On a.ProjectsCode = S.ProjectsCode
Left Join Activity b On a.ProjectsCode = b.ProjectsCode
Left Join ProjectsApproveDetail PD On PD.ProjectsCode = a.ProjectsCode
Left Join Employee d On PD.EmpID = d.EmpID
Left Join Employee Ep On a.CreateUser = Ep.EmpID
Left Join Department e On a.DeptCode = e.DeptCode
Left Join MainSubDepartment MSD On e.MainSubDeptCode = MSD.MainSubDeptCode
Left Join MainDepartment MD On MSD.MainDeptCode = MD.MainDeptCode
Where b.DelFlag = 0 And ApproveFlag = 1 And a.StudyYear = '{0}' And b.SchoolID = '{1}' ";
//string StrSql = "Select a.ProjectsCode, a.ProjectsName, b.ActivityCode, b.ActivityName, b.SDate , b.EDate, '' As DeptName, b.Status, b.Df, "
// + " b.CostsType, IsNull(b.TotalAmount, 0) TotalAmount, IsNull(b.TotalAmount2, 0) TotalAmount2, 0.0 As TotalBalance, "
// + " IsNull((Cast(b.Term As nVarChar) + '/' + Cast(b.YearB As nVarChar)), '') Term, IsNull(b.ActivityStatus, 0) As ActivityStatus "
// + " From Projects a, Activity b "
// + " Where a.ProjectsCode = b.ProjectsCode And b.DelFlag = 0 "
// + " And a.StudyYear = '" + ddlSearchYear.SelectedValue + "' And b.SchoolID = '" + CurrentUser.SchoolID + "' ";
//if (ddlSearchDept.SelectedIndex != 0)
//{
// StrSql = "Select a.ProjectsCode, a.ProjectsName, b.ActivityCode, b.ActivityName, b.SDate , b.EDate, '' As DeptName, b.Status, b.Df, "
// + " b.CostsType, IsNull(b.TotalAmount, 0) TotalAmount, IsNull(b.TotalAmount2, 0) TotalAmount2, 0.0 As TotalBalance, "
// + " IsNull((Cast(b.Term As nVarChar) + '/' + Cast(b.YearB As nVarChar)), '') Term, IsNull(b.ActivityStatus, 0) As ActivityStatus "
// + " From Projects a, Activity b, dtAcDept c "
// + " Where a.ProjectsCode = b.ProjectsCode And b.DelFlag = 0 And b.ActivityCode = c.ActivityCode "
// + " And a.StudyYear = '" + ddlSearchYear.SelectedValue + "' And b.SchoolID = '" + CurrentUser.SchoolID + "' "
// + " And c.DeptCode = '" + ddlSearchDept.SelectedValue + "'";
//}
//if (ddlSearchEmp.SelectedIndex != 0)
//{
// if (ddlSearchDept.SelectedIndex == 0)
// {
// StrSql = "Select a.ProjectsCode, a.ProjectsName, b.ActivityCode, b.ActivityName, b.SDate , b.EDate, '' As DeptName, b.Status, b.Df, "
// + " b.CostsType, IsNull(b.TotalAmount, 0) TotalAmount, IsNull(b.TotalAmount2, 0) TotalAmount2, 0.0 As TotalBalance, "
// + " IsNull((Cast(b.Term As nVarChar) + '/' + Cast(b.YearB As nVarChar)), '') Term, IsNull(b.ActivityStatus, 0) As ActivityStatus "
// + " From Projects a, Activity b, dtAcEmp c "
// + " Where a.ProjectsCode = b.ProjectsCode And b.DelFlag = 0 And b.ActivityCode = c.ActivityCode "
// + " And a.StudyYear = '" + ddlSearchYear.SelectedValue + "' And b.SchoolID = '" + CurrentUser.SchoolID + "' "
// + " And c.EmpCode = '" + ddlSearchEmp.SelectedValue + "'";
// }
// else
// {
// StrSql = "Select a.ProjectsCode, a.ProjectsName, b.ActivityCode, b.ActivityName, b.SDate , b.EDate, '' As DeptName, b.Status, b.Df, "
// + " b.CostsType, IsNull(b.TotalAmount, 0) TotalAmount, IsNull(b.TotalAmount2, 0) TotalAmount2, 0.0 As TotalBalance, "
// + " IsNull((Cast(b.Term As nVarChar) + '/' + Cast(b.YearB As nVarChar)), '') Term, IsNull(b.ActivityStatus, 0) As ActivityStatus "
// + " From Projects a, Activity b, dtAcEmp c, dtAcDept d "
// + " Where a.ProjectsCode = b.ProjectsCode And b.DelFlag = 0 And b.ActivityCode = c.ActivityCode And b.ActivityCode = d.ActivityCode "
// + " And a.StudyYear = '" + ddlSearchYear.SelectedValue + "' And b.SchoolID = '" + CurrentUser.SchoolID + "' "
// + " And c.EmpCode = '" + ddlSearchEmp.SelectedValue + "' And d.DeptCode = '" + ddlSearchDept.SelectedValue + "'";
// }
//}
//if (ddlSearch2.SelectedIndex != 0)
//{
// strSql += " And S.StrategiesCode = '" + ddlSearch2.SelectedValue + "'";
//}
if (ddlSearch.SelectedIndex != 0)
{
strSql += " And a.ProjectsCode = '" + ddlSearch.SelectedValue + "'";
}
if (ddlSearchMainDept.SelectedIndex != 0)
{
strSql += " And MD.MainDeptCode = '" + ddlSearchMainDept.SelectedValue + "'";
}
if (ddlSearchMainSubDept.SelectedIndex != 0)
{
strSql += " And MSD.MainSubDeptCode = '" + ddlSearchMainSubDept.SelectedValue + "'";
}
if (ddlSearchDept.SelectedIndex != 0)
{
strSql += " And e.DeptCode = '" + ddlSearchDept.SelectedValue + "'";
}
if (ddlSearchEmp.SelectedIndex != 0)
{
strSql += " And a.CreateUser = '******'";
}
if (txtSearch.Text != "")
{
strSql += " And b.ActivityName Like '%" + txtSearch.Text + "%' ";
}
strSql += @" Group By a.ProjectsCode, a.ProjectsName, b.ActivityCode, b.ActivityName, b.SDate , b.EDate, b.Status, b.Df, b.ApproveFlag,
b.CostsType, b.TotalAmount, b.TotalAmount2, b.Term, b.YearB, b.ActivityStatus, a.Sort, b.Sort
Order By a.Sort Desc, b.Sort Desc ";
DataView dv = Conn.Select(string.Format(strSql, ddlSearchYear.SelectedValue, CurrentUser.SchoolID));
for (int j = 0; j < dv.Count; j++)
{
decimal TtAmount2 = Convert.ToDecimal(btc.getNTotalAmount(dv[j]["ActivityCode"].ToString()));
dv[j]["DeptName"] = btc.getAcDeptName(dv[j]["ActivityCode"].ToString());
if (TtAmount2 != 0)
{
dv[j]["TotalAmount2"] = TtAmount2.ToString();
}
dv[j]["TotalBalance"] = (Convert.ToDecimal(dv[j]["TotalAmount"]) - Convert.ToDecimal(dv[j]["TotalAmount2"])).ToString();
}
//àªç¤¼ÅÃÇÁ
try
{
DataTable dt = dv.ToTable();
TotalAmount1 = Convert.ToDecimal(dt.Compute("Sum(TotalAmount)", dv.RowFilter));
TotalAmount2 = Convert.ToDecimal(dt.Compute("Sum(TotalAmount2)", dv.RowFilter));
TotalAmount3 = Convert.ToDecimal(dt.Compute("Sum(TotalBalance)", dv.RowFilter));
}
catch (Exception ex)
{
}
GridView1.DataSource = dv;
lblSearchTotal.InnerText = dv.Count.ToString();
GridView1.DataBind();
//----GrandTotal-----------
//strSql = "Select a.ActivityCode, IsNull(Sum(a.TotalAmount), 0) TotalAmount, IsNull(Sum(a.TotalAmount2), 0) TotalAmount2 "
// + " From Activity a, Projects b "
// + " Where a.DelFlag = 0 And a.ProjectsCode = b.ProjectsCode And a.ApproveFlag = 1 "
// + " And a.StudyYear = '" + ddlSearchYear.SelectedValue + "' And a.SchoolID = '" + CurrentUser.SchoolID + "' ";
//if (ddlSearchDept.SelectedIndex != 0)
//{
// strSql = "Select a.ActivityCode, IsNull(Sum(a.TotalAmount), 0) TotalAmount, IsNull(Sum(a.TotalAmount2), 0) TotalAmount2 "
// + " From Activity a, Projects b, dtAcDept c "
// + " Where a.DelFlag = 0 And a.ProjectsCode = b.ProjectsCode And a.ActivityCode = c.ActivityCode And a.ApproveFlag = 1 "
// + " And a.StudyYear = '" + ddlSearchYear.SelectedValue + "' And a.SchoolID = '" + CurrentUser.SchoolID + "' "
// + " And c.DeptCode = '" + ddlSearchDept.SelectedValue + "'";
//}
//if (ddlSearchEmp.SelectedIndex != 0)
//{
// if (ddlSearchDept.SelectedIndex == 0)
// {
// strSql = "Select a.ActivityCode, IsNull(Sum(a.TotalAmount), 0) TotalAmount, IsNull(Sum(a.TotalAmount2), 0) TotalAmount2 "
// + " From Activity a, Projects b, dtAcEmp c "
// + " Where a.DelFlag = 0 And a.ProjectsCode = b.ProjectsCode And a.ActivityCode = c.ActivityCode And a.ApproveFlag = 1 "
// + " And a.StudyYear = '" + ddlSearchYear.SelectedValue + "' And a.SchoolID = '" + CurrentUser.SchoolID + "' "
// + " And c.EmpCode = '" + ddlSearchEmp.SelectedValue + "'";
// }
// else
// {
// strSql = "Select a.ActivityCode, IsNull(Sum(a.TotalAmount), 0) TotalAmount, IsNull(Sum(a.TotalAmount2), 0) TotalAmount2 "
// + " From Activity a, Projects b, dtAcEmp c, dtAcDept d "
// + " Where a.DelFlag = 0 And a.ProjectsCode = b.ProjectsCode And a.ActivityCode = c.ActivityCode And a.ActivityCode = d.ActivityCode And a.ApproveFlag = 1 "
// + " And a.StudyYear = '" + ddlSearchYear.SelectedValue + "' And a.SchoolID = '" + CurrentUser.SchoolID + "' "
// + " And c.EmpCode = '" + ddlSearchEmp.SelectedValue + "' And d.DeptCode = '" + ddlSearchDept.SelectedValue + "' ";
// }
//}
strSql = @" Select b.ActivityCode, IsNull(Sum(b.TotalAmount), 0) TotalAmount, IsNull(Sum(b.TotalAmount2), 0) TotalAmount2
From Projects a Left Join dtStrategies S On a.ProjectsCode = S.ProjectsCode
Left Join Activity b On a.ProjectsCode = b.ProjectsCode
Left Join ProjectsApproveDetail PD On PD.ProjectsCode = a.ProjectsCode
Left Join Employee d On PD.EmpID = d.EmpID
Left Join Employee Ep On a.CreateUser = Ep.EmpID
Left Join Department e On a.DeptCode = e.DeptCode
Left Join MainSubDepartment MSD On e.MainSubDeptCode = MSD.MainSubDeptCode
Left Join MainDepartment MD On MSD.MainDeptCode = MD.MainDeptCode
Where b.DelFlag = 0 And ApproveFlag = 1 And a.StudyYear = '{0}' And b.SchoolID = '{1}' ";
//if (ddlSearch2.SelectedIndex != 0)
//{
// strSql += " And S.StrategiesCode = '" + ddlSearch2.SelectedValue + "'";
//}
if (ddlSearch.SelectedIndex != 0)
{
strSql += " And a.ProjectsCode = '" + ddlSearch.SelectedValue + "'";
}
if (ddlSearchMainDept.SelectedIndex != 0)
{
strSql += " And MD.MainDeptCode = '" + ddlSearchMainDept.SelectedValue + "'";
}
if (ddlSearchMainSubDept.SelectedIndex != 0)
{
strSql += " And MSD.MainSubDeptCode = '" + ddlSearchMainSubDept.SelectedValue + "'";
}
if (ddlSearchDept.SelectedIndex != 0)
{
strSql += " And e.DeptCode = '" + ddlSearchDept.SelectedValue + "'";
}
if (ddlSearchEmp.SelectedIndex != 0)
{
strSql += " And a.CreateUser = '******'";
}
DataView dvTotal = Conn.Select(string.Format(strSql + " Group By b.ActivityCode ", ddlSearchYear.SelectedValue, CurrentUser.SchoolID));
for (int j = 0; j < dvTotal.Count; j++)
{
decimal TtAmount2 = Convert.ToDecimal(btc.getNTotalAmount(dvTotal[j]["ActivityCode"].ToString()));
if (TtAmount2 != 0)
{
dvTotal[j]["TotalAmount2"] = TtAmount2.ToString();
}
}
try
{
DataTable dt = dvTotal.ToTable();
ToltalBudget.InnerHtml = Convert.ToDecimal(dt.Compute("Sum(TotalAmount)", dvTotal.RowFilter)).ToString("#,##0.00");
ToltalBudget2.InnerHtml = Convert.ToDecimal(dt.Compute("Sum(TotalAmount2)", dvTotal.RowFilter)).ToString("#,##0.00");
TotalBalance.InnerHtml = (Convert.ToDecimal(ToltalBudget.InnerHtml) - Convert.ToDecimal(ToltalBudget2.InnerHtml)).ToString("#,##0.00");
}
catch (Exception ex)
{
}
//----EndGrandTotal-----------
}
public override void DataBind()
{
string StrSql = " Select a.ProjectsCode, a.ProjectsName, b.ActivityCode, b.ActivityName, b.SDate , b.EDate, b.Status, '' DeptName, b.Sort, "
+ " IsNull(b.TotalAmount, 0) TotalAmount, IsNull(b.TotalAmount2, 0) TotalAmount2, 0.0 As TotalBalance, IsNull(b.ActivityStatus, 0) As ActivityStatus "
+ " From Projects a Left Join dtStrategies S On a.ProjectsCode = S.ProjectsCode "
+ " Inner Join Activity b On a.ProjectsCode = b.ProjectsCode "
+ " Where b.DelFlag = 0 "
+ " And a.StudyYear = '" + ddlSearchYear.SelectedValue + "' And b.SchoolID = '" + CurrentUser.SchoolID + "' ";
if (ddlSearchDept.SelectedIndex != 0)
{
StrSql = " Select a.ProjectsCode, a.ProjectsName, b.ActivityCode, b.ActivityName, b.SDate , b.EDate, b.Status, '' DeptName, b.Sort, "
+ " IsNull(b.TotalAmount, 0) TotalAmount, IsNull(b.TotalAmount2, 0) TotalAmount2, 0.0 As TotalBalance, IsNull(b.ActivityStatus, 0) As ActivityStatus "
+ " From Projects a Left Join dtStrategies S On a.ProjectsCode = S.ProjectsCode "
+ " Inner Join Activity b On a.ProjectsCode = b.ProjectsCode "
+ " Inner Join dtAcDept c On b.ActivityCode = c.ActivityCode "
+ " Where b.DelFlag = 0 "
+ " And a.StudyYear = '" + ddlSearchYear.SelectedValue + "' And b.SchoolID = '" + CurrentUser.SchoolID + "' "
+ " And c.DeptCode = '" + ddlSearchDept.SelectedValue + "' ";
}
if (ddlSearchEmp.SelectedIndex != 0)
{
if (ddlSearchDept.SelectedIndex == 0)
{
StrSql = " Select a.ProjectsCode, a.ProjectsName, b.ActivityCode, b.ActivityName, b.SDate , b.EDate, b.Status, '' DeptName, b.Sort, "
+ " IsNull(b.TotalAmount, 0) TotalAmount, IsNull(b.TotalAmount2, 0) TotalAmount2, 0.0 As TotalBalance, IsNull(b.ActivityStatus, 0) As ActivityStatus "
+ " From Projects a Left Join dtStrategies S On a.ProjectsCode = S.ProjectsCode "
+ " Inner Join Activity b On a.ProjectsCode = b.ProjectsCode "
+ " Inner Join dtAcEmp c On b.ActivityCode = c.ActivityCode "
+ " Where b.DelFlag = 0 "
+ " And a.StudyYear = '" + ddlSearchYear.SelectedValue + "' And b.SchoolID = '" + CurrentUser.SchoolID + "' "
+ " And c.EmpCode = '" + ddlSearchEmp.SelectedValue + "'";
}
else
{
StrSql = " Select a.ProjectsCode, a.ProjectsName, b.ActivityCode, b.ActivityName, b.SDate , b.EDate, b.Status, '' DeptName, b.Sort, "
+ " IsNull(b.TotalAmount, 0) TotalAmount, IsNull(b.TotalAmount2, 0) TotalAmount2, 0.0 As TotalBalance, IsNull(b.ActivityStatus, 0) As ActivityStatus "
+ " From Projects a Left Join dtStrategies S On a.ProjectsCode = S.ProjectsCode "
+ " Inner Join Activity b On a.ProjectsCode = b.ProjectsCode "
+ " Inner Join dtAcEmp c On b.ActivityCode = c.ActivityCode "
+ " Inner Join dtAcDept d On b.ActivityCode = d.ActivityCode "
+ " Where b.DelFlag = 0 "
+ " And a.StudyYear = '" + ddlSearchYear.SelectedValue + "' And b.SchoolID = '" + CurrentUser.SchoolID + "' "
+ " And c.EmpCode = '" + ddlSearchEmp.SelectedValue + "' And d.DeptCode = '" + ddlSearchDept.SelectedValue + "' ";
}
}
if (rbtlStatus.SelectedIndex != 5)
{
StrSql += " And b.Status = '" + rbtlStatus.SelectedValue + "'";
}
if (ddlSearch2.SelectedIndex != 0)
{
StrSql += " And S.StrategiesCode = '" + ddlSearch2.SelectedValue + "'";
}
if (ddlSearch.SelectedIndex != 0)
{
StrSql += " And a.ProjectsCode = '" + ddlSearch.SelectedValue + "'";
}
if (ddlSearchMonth.SelectedIndex != 0)
{
int CkYear = Convert.ToInt32(ddlSearchYear.SelectedValue) - 543;
StrSql += " And Month(b.SDate) = " + ddlSearchMonth.SelectedValue + " And Year(b.SDate) = " + CkYear + " ";
}
if (txtSearch.Text != "")
{
StrSql += " And b.ActivityName Like '%" + txtSearch.Text + "%' ";
}
StrSql += " Group By a.ProjectsCode, a.ProjectsName, b.ActivityCode, b.ActivityName, b.SDate , b.EDate, b.Status, b.Sort, b.TotalAmount, b.TotalAmount2, b.ActivityStatus";
DataView dv = Conn.Select(string.Format(StrSql + " Order By b.Sort "));
for (int j = 0; j < dv.Count; j++)
{
dv[j]["DeptName"] = btc.getAcDeptName(dv[j]["ActivityCode"].ToString());
if (btc.getNTotalAmount(dv[j]["ActivityCode"].ToString()) != 0)
{
dv[j]["TotalAmount2"] = btc.getNTotalAmount(dv[j]["ActivityCode"].ToString());
}
dv[j]["TotalBalance"] = (Convert.ToDecimal(dv[j]["TotalAmount"]) - Convert.ToDecimal(dv[j]["TotalAmount2"])).ToString();
}
GridView1.DataSource = dv;
lblSearchTotal.InnerText = dv.Count.ToString();
GridView1.DataBind();
}
public override void DataBind()
{
string StrSql = "Select a.ProjectsCode, a.ProjectsName, b.ActivityCode, b.ActivityName, b.SDate , b.EDate, '' As DeptName, b.Status, b.Df, "
+ " b.CostsType, IsNull(b.TotalAmount, 0) TotalAmount, IsNull(b.TotalAmount2, 0) TotalAmount2, 0.0 As TotalBalance, "
+ " IsNull((Cast(b.Term As nVarChar) + '/' + Cast(b.YearB As nVarChar)), '') Term, IsNull(b.ActivityStatus, 0) As ActivityStatus "
+ " From Projects a, Activity b "
+ " Where a.ProjectsCode = b.ProjectsCode And b.DelFlag = 0 "
+ " And a.StudyYear = '" + ddlSearchYear.SelectedValue + "' And b.SchoolID = '" + CurrentUser.SchoolID + "' ";
if (ddlSearchDept.SelectedIndex != 0)
{
StrSql = "Select a.ProjectsCode, a.ProjectsName, b.ActivityCode, b.ActivityName, b.SDate , b.EDate, '' As DeptName, b.Status, b.Df, "
+ " b.CostsType, IsNull(b.TotalAmount, 0) TotalAmount, IsNull(b.TotalAmount2, 0) TotalAmount2, 0.0 As TotalBalance, "
+ " IsNull((Cast(b.Term As nVarChar) + '/' + Cast(b.YearB As nVarChar)), '') Term, IsNull(b.ActivityStatus, 0) As ActivityStatus "
+ " From Projects a, Activity b, dtAcDept c "
+ " Where a.ProjectsCode = b.ProjectsCode And b.DelFlag = 0 And b.ActivityCode = c.ActivityCode "
+ " And a.StudyYear = '" + ddlSearchYear.SelectedValue + "' And b.SchoolID = '" + CurrentUser.SchoolID + "' "
+ " And c.DeptCode = '" + ddlSearchDept.SelectedValue + "'";
}
if (ddlSearchEmp.SelectedIndex != 0)
{
if (ddlSearchDept.SelectedIndex == 0)
{
StrSql = "Select a.ProjectsCode, a.ProjectsName, b.ActivityCode, b.ActivityName, b.SDate , b.EDate, '' As DeptName, b.Status, b.Df, "
+ " b.CostsType, IsNull(b.TotalAmount, 0) TotalAmount, IsNull(b.TotalAmount2, 0) TotalAmount2, 0.0 As TotalBalance, "
+ " IsNull((Cast(b.Term As nVarChar) + '/' + Cast(b.YearB As nVarChar)), '') Term, IsNull(b.ActivityStatus, 0) As ActivityStatus "
+ " From Projects a, Activity b, dtAcEmp c "
+ " Where a.ProjectsCode = b.ProjectsCode And b.DelFlag = 0 And b.ActivityCode = c.ActivityCode "
+ " And a.StudyYear = '" + ddlSearchYear.SelectedValue + "' And b.SchoolID = '" + CurrentUser.SchoolID + "' "
+ " And c.EmpCode = '" + ddlSearchEmp.SelectedValue + "'";
}
else
{
StrSql = "Select a.ProjectsCode, a.ProjectsName, b.ActivityCode, b.ActivityName, b.SDate , b.EDate, '' As DeptName, b.Status, b.Df, "
+ " b.CostsType, IsNull(b.TotalAmount, 0) TotalAmount, IsNull(b.TotalAmount2, 0) TotalAmount2, 0.0 As TotalBalance, "
+ " IsNull((Cast(b.Term As nVarChar) + '/' + Cast(b.YearB As nVarChar)), '') Term, IsNull(b.ActivityStatus, 0) As ActivityStatus "
+ " From Projects a, Activity b, dtAcEmp c, dtAcDept d "
+ " Where a.ProjectsCode = b.ProjectsCode And b.DelFlag = 0 And b.ActivityCode = c.ActivityCode And b.ActivityCode = d.ActivityCode "
+ " And a.StudyYear = '" + ddlSearchYear.SelectedValue + "' And b.SchoolID = '" + CurrentUser.SchoolID + "' "
+ " And c.EmpCode = '" + ddlSearchEmp.SelectedValue + "' And d.DeptCode = '" + ddlSearchDept.SelectedValue + "'";
}
}
if (ddlSearch2.SelectedIndex != 0)
{
StrSql = StrSql + " And a.StrategiesCode = '" + ddlSearch2.SelectedValue + "'";
}
if (ddlSearch.SelectedIndex != 0)
{
StrSql = StrSql + " And a.ProjectsCode = '" + ddlSearch.SelectedValue + "'";
}
if (txtSearch.Text != "")
{
StrSql = StrSql + " And b.ActivityName Like '%" + txtSearch.Text + "%' ";
}
DataView dv = Conn.Select(string.Format(StrSql + " And ApproveFlag = 1 Order By a.Sort Desc, b.Sort Desc "));
for (int j = 0; j < dv.Count; j++)
{
decimal TtAmount2 = Convert.ToDecimal(btc.getNTotalAmount(dv[j]["ActivityCode"].ToString()));
dv[j]["DeptName"] = btc.getAcDeptName(dv[j]["ActivityCode"].ToString());
if (TtAmount2 != 0)
{
dv[j]["TotalAmount2"] = TtAmount2.ToString();
}
dv[j]["TotalBalance"] = (Convert.ToDecimal(dv[j]["TotalAmount"]) - Convert.ToDecimal(dv[j]["TotalAmount2"])).ToString();
}
//àªç¤¼ÅÃÇÁ
try
{
DataTable dt = dv.ToTable();
TotalAmount1 = Convert.ToDecimal(dt.Compute("Sum(TotalAmount)", dv.RowFilter));
TotalAmount2 = Convert.ToDecimal(dt.Compute("Sum(TotalAmount2)", dv.RowFilter));
TotalAmount3 = Convert.ToDecimal(dt.Compute("Sum(TotalBalance)", dv.RowFilter));
}
catch (Exception ex)
{
}
GridView1.DataSource = dv;
lblSearchTotal.InnerText = dv.Count.ToString();
GridView1.DataBind();
////----GrandTotal-----------
//StrSql = "Select IsNull(Sum(c.TotalAmount), 0) TotalAmount1, IsNull(Sum(c.TotalAmount2), 0) TotalAmount2, "
// + " (IsNull(Sum(c.TotalAmount), 0) - IsNull(Sum(c.TotalAmount2), 0)) TotalBalance "
// + " From Activity c "
// + " Where c.DelFlag = 0 "
// + " And c.StudyYear = '" + ddlSearchYear.SelectedValue + "' And c.SchoolID = '" + CurrentUser.SchoolID + "' "
// + " And c.ApproveFlag = 1 ";
//if (ddlSearchDept.SelectedIndex != 0)
//{
// StrSql = "Select IsNull(Sum(c.TotalAmount), 0) TotalAmount1, IsNull(Sum(c.TotalAmount2), 0) TotalAmount2, "
// + " (IsNull(Sum(c.TotalAmount), 0) - IsNull(Sum(c.TotalAmount2), 0)) TotalBalance "
// + " From Activity c, dtAcDept d "
// + " Where c.DelFlag = 0 And c.ActivityCode = d.ActivityCode "
// + " And c.StudyYear = '" + ddlSearchYear.SelectedValue + "' And c.SchoolID = '" + CurrentUser.SchoolID + "' "
// + " And d.DeptCode = '" + ddlSearchDept.SelectedValue + "'"
// + " And c.ApproveFlag = 1 ";
//}
//if (ddlSearchEmp.SelectedIndex != 0)
//{
// if (ddlSearchDept.SelectedIndex == 0)
// {
// StrSql = "Select IsNull(Sum(c.TotalAmount), 0) TotalAmount1, IsNull(Sum(c.TotalAmount2), 0) TotalAmount2, "
// + " (IsNull(Sum(c.TotalAmount), 0) - IsNull(Sum(c.TotalAmount2), 0)) TotalBalance "
// + " From Activity c, dtAcEmp d "
// + " Where c.DelFlag = 0 And c.ActivityCode = d.ActivityCode "
// + " And c.StudyYear = '" + ddlSearchYear.SelectedValue + "' And c.SchoolID = '" + CurrentUser.SchoolID + "' "
// + " And d.EmpCode = '" + ddlSearchEmp.SelectedValue + "'"
// + " And c.ApproveFlag = 1 ";
// }
// else
// {
// StrSql = "Select IsNull(Sum(c.TotalAmount), 0) TotalAmount1, IsNull(Sum(c.TotalAmount2), 0) TotalAmount2, "
// + " (IsNull(Sum(c.TotalAmount), 0) - IsNull(Sum(c.TotalAmount2), 0)) TotalBalance "
// + " From Activity c, dtAcEmp d, dtAcDept e "
// + " Where c.DelFlag = 0 And c.ActivityCode = d.ActivityCode And c.ActivityCode = e.ActivityCode "
// + " And c.StudyYear = '" + ddlSearchYear.SelectedValue + "' And c.SchoolID = '" + CurrentUser.SchoolID + "' "
// + " And d.EmpCode = '" + ddlSearchEmp.SelectedValue + "' And e.DeptCode = '" + ddlSearchDept.SelectedValue + "'"
// + " And c.ApproveFlag = 1 ";
// }
//}
//----GrandTotal-----------
StrSql = "Select a.ActivityCode, IsNull(Sum(a.TotalAmount), 0) TotalAmount, IsNull(Sum(a.TotalAmount2), 0) TotalAmount2 "
+ " From Activity a, Projects b "
+ " Where a.DelFlag = 0 And a.ProjectsCode = b.ProjectsCode And a.ApproveFlag = 1 "
+ " And a.StudyYear = '" + ddlSearchYear.SelectedValue + "' And a.SchoolID = '" + CurrentUser.SchoolID + "' ";
if (ddlSearchDept.SelectedIndex != 0)
{
StrSql = "Select a.ActivityCode, IsNull(Sum(a.TotalAmount), 0) TotalAmount, IsNull(Sum(a.TotalAmount2), 0) TotalAmount2 "
+ " From Activity a, Projects b, dtAcDept c "
+ " Where a.DelFlag = 0 And a.ProjectsCode = b.ProjectsCode And a.ActivityCode = c.ActivityCode And a.ApproveFlag = 1 "
+ " And a.StudyYear = '" + ddlSearchYear.SelectedValue + "' And a.SchoolID = '" + CurrentUser.SchoolID + "' "
+ " And c.DeptCode = '" + ddlSearchDept.SelectedValue + "'";
}
if (ddlSearchEmp.SelectedIndex != 0)
{
if (ddlSearchDept.SelectedIndex == 0)
{
StrSql = "Select a.ActivityCode, IsNull(Sum(a.TotalAmount), 0) TotalAmount, IsNull(Sum(a.TotalAmount2), 0) TotalAmount2 "
+ " From Activity a, Projects b, dtAcEmp c "
+ " Where a.DelFlag = 0 And a.ProjectsCode = b.ProjectsCode And a.ActivityCode = c.ActivityCode And a.ApproveFlag = 1 "
+ " And a.StudyYear = '" + ddlSearchYear.SelectedValue + "' And a.SchoolID = '" + CurrentUser.SchoolID + "' "
+ " And c.EmpCode = '" + ddlSearchEmp.SelectedValue + "'";
}
else
{
StrSql = "Select a.ActivityCode, IsNull(Sum(a.TotalAmount), 0) TotalAmount, IsNull(Sum(a.TotalAmount2), 0) TotalAmount2 "
+ " From Activity a, Projects b, dtAcEmp c, dtAcDept d "
+ " Where a.DelFlag = 0 And a.ProjectsCode = b.ProjectsCode And a.ActivityCode = c.ActivityCode And a.ActivityCode = d.ActivityCode And a.ApproveFlag = 1 "
+ " And a.StudyYear = '" + ddlSearchYear.SelectedValue + "' And a.SchoolID = '" + CurrentUser.SchoolID + "' "
+ " And c.EmpCode = '" + ddlSearchEmp.SelectedValue + "' And d.DeptCode = '" + ddlSearchDept.SelectedValue + "' ";
}
}
DataView dvTotal = Conn.Select(StrSql + " Group By a.ActivityCode ");
for (int j = 0; j < dvTotal.Count; j++)
{
decimal TtAmount2 = Convert.ToDecimal(btc.getNTotalAmount(dvTotal[j]["ActivityCode"].ToString()));
if (TtAmount2 != 0)
{
dvTotal[j]["TotalAmount2"] = TtAmount2.ToString();
}
}
try
{
DataTable dt = dvTotal.ToTable();
ToltalBudget.InnerHtml = Convert.ToDecimal(dt.Compute("Sum(TotalAmount)", dvTotal.RowFilter)).ToString("#,##0.00");
ToltalBudget2.InnerHtml = Convert.ToDecimal(dt.Compute("Sum(TotalAmount2)", dvTotal.RowFilter)).ToString("#,##0.00");
TotalBalance.InnerHtml = (Convert.ToDecimal(ToltalBudget.InnerHtml) - Convert.ToDecimal(ToltalBudget2.InnerHtml)).ToString("#,##0.00");
}
catch (Exception ex)
{
}
//ToltalBudget.InnerHtml = (dvTotal.Count != 0) ? Convert.ToDecimal(dvTotal[0]["TotalAmount"]).ToString("#,##0.00") : "0.00";
//ToltalBudget2.InnerHtml = (dvTotal.Count != 0) ? Convert.ToDecimal(dvTotal[0]["TotalAmount2"]).ToString("#,##0.00") : "0.00";
//TotalBalance.InnerHtml = (dvTotal.Count != 0) ? (Convert.ToDecimal(dvTotal[0]["TotalAmount"]) - Convert.ToDecimal(dvTotal[0]["TotalAmount2"])).ToString("#,##0.00") : "0.00";
//----EndGrandTotal-----------
}
private void getActivity() //¡Ô¨¡ÃÃÁ
{
DataView dv, dv1;
Connection Conn = new Connection();
BTC btc = new BTC();
string strSql = " Select a.ActivityCode, '¡Ô¨¡ÃÃÁ - ' + a.ActivityName ActivityName, a.Sort, Sum(b.APercent) CkAPercent, '' DeptName "
+ " From Activity a, Evaluation b "
+ " Where a.DelFlag = 0 And b.DelFlag = 0 And a.ActivityCode = b.ActivityCode "
+ " And b.IndicatorsCode = '" + Request.QueryString["idtid"] + "' And a.ProjectsCode = '" + Request.QueryString["pjid"] + "' "
+ " And b.StudyYear = '" + ddlYearB.SelectedValue + "' And b.SchoolID = '" + CurrentUser.SchoolID + "' ";
if (ddlSearchDept.SelectedIndex != 0)
{
strSql = " Select a.ActivityCode, '¡Ô¨¡ÃÃÁ - ' + a.ActivityName ActivityName, a.Sort, Sum(b.APercent) CkAPercent, '' DeptName "
+ " From Activity a, Evaluation b, dtAcDept c "
+ " Where a.DelFlag = 0 And b.DelFlag = 0 And a.ActivityCode = b.ActivityCode And a.ActivityCode = c.ActivityCode "
+ " And b.IndicatorsCode = '" + Request.QueryString["idtid"] + "' And a.ProjectsCode = '" + Request.QueryString["pjid"] + "' "
+ " And b.StudyYear = '" + ddlYearB.SelectedValue + "' And b.SchoolID = '" + CurrentUser.SchoolID + "' "
+ " And c.DeptCode = '" + ddlSearchDept.SelectedValue + "' ";
}
if (ddlSearchEmp.SelectedIndex != 0)
{
strSql = " Select a.ActivityCode, '¡Ô¨¡ÃÃÁ - ' + a.ActivityName ActivityName, a.Sort, Sum(b.APercent) CkAPercent, '' DeptName "
+ " From Activity a, Evaluation b, dtAcEmp c "
+ " Where a.DelFlag = 0 And b.DelFlag = 0 And a.ActivityCode = b.ActivityCode And a.ActivityCode = c.ActivityCode "
+ " And b.IndicatorsCode = '" + Request.QueryString["idtid"] + "' And a.ProjectsCode = '" + Request.QueryString["pjid"] + "' "
+ " And b.StudyYear = '" + ddlYearB.SelectedValue + "' And b.SchoolID = '" + CurrentUser.SchoolID + "' "
+ " And c.EmpCode = '" + ddlSearchEmp.SelectedValue + "' ";
}
dv = Conn.Select(strSql + " Group By a.ActivityCode, a.ActivityName, a.Sort Order By a.Sort ");
for (int j = 0; j < dv.Count; j++)
{
dv[j]["DeptName"] = btc.getAcDeptName(dv[j]["ActivityCode"].ToString());
}
double CkAPercent;
string strColor;
string strEva = "";
for (int i = 0; i < dv.Count; i++)
{
strSql = " Select a.ActivityCode "
+ " From Activity a, Evaluation b "
+ " Where a.DelFlag = 0 And b.DelFlag = 0 And a.ActivityCode = b.ActivityCode "
+ " And b.IndicatorsCode = '" + Request.QueryString["idtid"] + "' And a.ProjectsCode = '" + Request.QueryString["pjid"] + "' "
+ " And b.ActivityCode = '" + dv[i]["ActivityCode"].ToString() + "' "
+ " And b.StudyYear = '" + ddlYearB.SelectedValue + "' And b.SchoolID = '" + CurrentUser.SchoolID + "' ";
if (ddlSearchDept.SelectedIndex != 0)
{
strSql = " Select a.ActivityCode "
+ " From Activity a, Evaluation b, dtAcDept c "
+ " Where a.DelFlag = 0 And b.DelFlag = 0 And a.ActivityCode = b.ActivityCode And a.ActivityCode = c.ActivityCode "
+ " And b.IndicatorsCode = '" + Request.QueryString["idtid"] + "' And a.ProjectsCode = '" + Request.QueryString["pjid"] + "' "
+ " And b.ActivityCode = '" + dv[i]["ActivityCode"].ToString() + "' "
+ " And b.StudyYear = '" + ddlYearB.SelectedValue + "' And b.SchoolID = '" + CurrentUser.SchoolID + "' "
+ " And c.DeptCode = '" + ddlSearchDept.SelectedValue + "' ";
}
if (ddlSearchEmp.SelectedIndex != 0)
{
strSql = " Select a.ActivityCode "
+ " From Activity a, Evaluation b, dtAcEmp c "
+ " Where a.DelFlag = 0 And b.DelFlag = 0 And a.ActivityCode = b.ActivityCode And a.ActivityCode = c.ActivityCode "
+ " And b.IndicatorsCode = '" + Request.QueryString["idtid"] + "' And a.ProjectsCode = '" + Request.QueryString["pjid"] + "' "
+ " And b.ActivityCode = '" + dv[i]["ActivityCode"].ToString() + "' "
+ " And b.StudyYear = '" + ddlYearB.SelectedValue + "' And b.SchoolID = '" + CurrentUser.SchoolID + "' "
+ " And c.EmpCode = '" + ddlSearchEmp.SelectedValue + "' ";
}
dv1 = Conn.Select(strSql);
CkAPercent = (Convert.ToDouble(dv[i]["CkAPercent"]) * 100) / (Convert.ToDouble(dv1.Count) * 100);
if (CkAPercent < 50)
{
strColor = "Red";//"#f87373";
strEva = "(äÁè¼èÒ¹)";
}
else
{
strColor = "#0B610B";
strEva = "(¼èÒ¹)";
}
lblSummarize.Text = string.Format("<img src=\"../Image/icon/" + ((CkAPercent >= 50) ? "ballg" : "ballr") + ".png\" style=\"border:none;width:36px;height:39px;\"/> <span onclick=\"getPopUpActivity('{5}')\" style=\"cursor:pointer; color:" + ((rbtlView1.Checked) ? "#6E6E6E" : strColor) + ";font-size:" + btc.fontView1 + "pt;font-weight:bold;width:630px;overflow:hidden;\" title=\"{4}\">{0}</span><span style=\"width:120px; text-align:right ;float:right; margin:20px 20px 0 0;color:" + strColor + ";font-size:" + btc.fontView1 + "pt; font-weight:bold;\">{2}</span><span style=\"width:50px; text-align:right; float:Right; padding:20px 55px 0 0; color:" + strColor + "; font-size:" + btc.fontView1 + "pt; font-weight:bold;\">{1}</span><span style=\"float:right; width:140px; padding:20px 25px 0 0; color:" + strColor + "; font-size:" + btc.fontView1 + "pt; text-align:right; font-weight:bold;\">{3}%</span><p style=\"clear:both; float:none;\"></p><hr />", (dv[i]["ActivityName"].ToString().Length > btc.strCut4 ? dv[i]["ActivityName"].ToString().Substring(0, btc.strCut4) + "..." : dv[i]["ActivityName"]), "", strEva, " " + CkAPercent.ToString("#,##0.00") + " ", dv[i]["DeptName"], dv[i]["ActivityCode"].ToString());
DataView dvHead = Conn.Select("Select Top 1 a.SideCode, a.SideName, b.StandardCode, 'Áҵðҹ·Õè ' + Cast(b.Sort As nVarChar) As StandardName, c.IndicatorsCode, 'µÑǺ觪Õé·Õè ' + Cast(b.Sort As nVarChar) + '.' + Cast(c.Sort As nVarChar) As IndicatorsName, d.ProjectsCode, d.ProjectsName, f.ActivityCode, f.ActivityName From Side a, Standard b, Indicators c, Projects d, Activity f, Evaluation e Where a.SideCode = b.SideCode And b.StandardCode = c.StandardCode And d.ProjectsCode = e.ProjectsCode And e.IndicatorsCode = c.IndicatorsCode And d.ProjectsCode = f.ProjectsCode And c.IndicatorsCode = '" + Request.QueryString["idtid"] + "' And e.ProjectsCode = '" + Request.QueryString["pjid"] + "' And f.ActivityCode = '" + Request.QueryString["acid"] + "'");
Repeater2.DataSource = dvHead;
Repeater2.DataBind();
}
}
public override void DataBind()
{
string StrSql = "";
if (ddlSearchDept.SelectedIndex == 0)
{
StrSql = " Select c.ActivityCode AcCode,'¡Ô¨¡ÃÃÁ' + c.ActivityName As FullName, c.Status, "
+ " IsNull(c.TotalAmount, 0) TotalAmount1, "
+ " IsNull(c.TotalAmount2, 0) TotalAmount2, "
+ " (IsNull(c.TotalAmount, 0) - IsNull(c.TotalAmount2, 0)) TotalBalance, '' As DeptName, IsNull(c.ApproveFlag, 0) ApproveFlag, c.UpdateDate "
+ " From Activity c "
+ " Where c.DelFlag = 0 "
+ " And c.SchoolID = '" + CurrentUser.SchoolID + "' ";
if (rbtStudyYear.Checked)
{
StrSql += " And c.StudyYear = '" + ddlSearchYear.SelectedValue + "' ";
}
else
{
StrSql += " And c.BudgetYear = '" + ddlSearchYear.SelectedValue + "' ";
}
if (txtSearch.Text != "")
{
StrSql = StrSql + " And c.ActivityName Like '%" + txtSearch.Text + "%' ";
}
if (ddlSearchProjects.SelectedIndex != 0)
{
StrSql = StrSql + " And c.ProjectsCode = '" + ddlSearchProjects.SelectedValue + "' ";
}
StrSql = StrSql + " Group By c.ActivityCode, c.ActivityName, c.TotalAmount, c.TotalAmount2, c.Status, c.ApproveFlag, c.UpdateDate ";
}
else
{
StrSql = " Select c.ActivityCode AcCode,'¡Ô¨¡ÃÃÁ' + c.ActivityName As FullName, c.Status, "
+ " IsNull(c.TotalAmount, 0) TotalAmount1, "
+ " IsNull(c.TotalAmount2, 0) TotalAmount2, "
+ " (IsNull(c.TotalAmount, 0) - IsNull(c.TotalAmount2, 0)) TotalBalance, '' As DeptName, IsNull(c.ApproveFlag, 0) ApproveFlag, c.UpdateDate "
+ " From Activity c, dtAcDept d "
+ " Where c.DelFlag = 0 And c.ActivityCode = d.ActivityCode "
+ " And c.SchoolID = '" + CurrentUser.SchoolID + "' "
+ " And d.DeptCode = '" + ddlSearchDept.SelectedValue + "' ";
if (rbtStudyYear.Checked)
{
StrSql += " And c.StudyYear = '" + ddlSearchYear.SelectedValue + "' ";
}
else
{
StrSql += " And c.BudgetYear = '" + ddlSearchYear.SelectedValue + "' ";
}
if (txtSearch.Text != "")
{
StrSql = StrSql + " And c.ActivityName Like '%" + txtSearch.Text + "%' ";
}
if (ddlSearchProjects.SelectedIndex != 0)
{
StrSql = StrSql + " And c.ProjectsCode = '" + ddlSearchProjects.SelectedValue + "' ";
}
}
DataView dv = Conn.Select(string.Format(StrSql));
if (dv.Count != 0)
{
for (int j = 0; j < dv.Count; j++)
{
dv[j]["DeptName"] = btc.getAcDeptName(dv[j]["AcCode"].ToString());
}
}
//àªç¤¼ÅÃÇÁ
try
{
DataTable dt = dv.ToTable();
TotalAmount1 = Convert.ToDecimal(dt.Compute("Sum(TotalAmount1)", dv.RowFilter));
TotalAmount2 = Convert.ToDecimal(dt.Compute("Sum(TotalAmount2)", dv.RowFilter));
TotalAmount3 = Convert.ToDecimal(dt.Compute("Sum(TotalBalance)", dv.RowFilter));
}
catch (Exception ex)
{
}
GridView1.DataSource = dv;
lblSearchTotal.InnerText = dv.Count.ToString();
GridView1.DataBind();
getBudget();
//----GrandTotal-----------
if (ddlSearchDept.SelectedIndex == 0)
{
StrSql = " Select "
+ " IsNull(Sum(c.TotalAmount), 0) TotalAmount1, "
+ " IsNull(Sum(c.TotalAmount2), 0) TotalAmount2, "
+ " (IsNull(Sum(c.TotalAmount), 0) - IsNull(Sum(c.TotalAmount2), 0)) TotalBalance "
+ " From Activity c "
+ " Where c.DelFlag = 0 "
+ " And c.SchoolID = '" + CurrentUser.SchoolID + "' ";
if (rbtStudyYear.Checked)
{
StrSql += " And c.StudyYear = '" + ddlSearchYear.SelectedValue + "' ";
}
else
{
StrSql += " And c.BudgetYear = '" + ddlSearchYear.SelectedValue + "' ";
}
}
else
{
StrSql = " Select "
+ " IsNull(Sum(c.TotalAmount), 0) TotalAmount1, "
+ " IsNull(Sum(c.TotalAmount2), 0) TotalAmount2, "
+ " (IsNull(Sum(c.TotalAmount), 0) - IsNull(Sum(c.TotalAmount2), 0)) TotalBalance "
+ " From Activity c, dtAcDept d "
+ " Where c.DelFlag = 0 And c.ActivityCode = d.ActivityCode "
+ " And c.SchoolID = '" + CurrentUser.SchoolID + "' "
+ " And d.DeptCode = '" + ddlSearchDept.SelectedValue + "' ";
if (rbtStudyYear.Checked)
{
StrSql += " And c.StudyYear = '" + ddlSearchYear.SelectedValue + "' ";
}
else
{
StrSql += " And c.BudgetYear = '" + ddlSearchYear.SelectedValue + "' ";
}
}
DataView dvTotal = Conn.Select(StrSql);
ToltalBudget.InnerHtml = (dvTotal.Count != 0) ? Convert.ToDecimal(dvTotal[0]["TotalAmount1"]).ToString("#,##0.00") : "0.00";
ToltalBudget2.InnerHtml = (dvTotal.Count != 0) ? Convert.ToDecimal(dvTotal[0]["TotalAmount2"]).ToString("#,##0.00") : "0.00";
TotalBalance.InnerHtml = (dvTotal.Count != 0) ? Convert.ToDecimal(dvTotal[0]["TotalBalance"]).ToString("#,##0.00") : "0.00";
//----EndGrandTotal----------
}
