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