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