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---------- }