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()); } }
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() { 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 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(); }