public override void DataBind()
{
string strSql = "";
DataView dv = null;
//â¤Ã§¡Ò÷Ñé§ËÁ´
strSql = @" Select D.DeptCode, REPLACE(D.DeptName,N'Êӹѡ','ʹ.') As Name, Count(P.ProjectsCode) data, 0 As data1, 0 As data2, SD.Sort, D.Sort
From Department D Left Join Projects P On P.DeptCode = D.DeptCode And P.DelFlag = 0 And P.StudyYear = '{1}' ";
if (ddlMonth.SelectedIndex != 0)
{
strSql += btc.getMonthOfMonth(ddlMonth.SelectedValue, ddlSearchYear.SelectedValue);
}
if (ddlQuarter.SelectedIndex != 0)
{
strSql += btc.getMonthOfQuarter(ddlQuarter.SelectedValue, ddlSearchYear.SelectedValue);
}
strSql += @" Left Join MainSubDepartment SD On SD.MainSubDeptCode = D.MainSubDeptCode
Where D.DelFlag = 0 And SD.MainDeptCode = '{0}'
Group By D.DeptCode, D.DeptName, SD.Sort, D.Sort Order By SD.Sort, D.Sort ";
dv = Conn.Select(string.Format(strSql, CurrentUser.MainDeptID, ddlSearchYear.SelectedValue));
//â¤Ã§¡Ò÷Õè͹ØÁѵÔ
strSql = @" Select D.DeptCode, D.DeptName As Name, Count(P.ProjectsCode) data1, SD.Sort, D.Sort
From Department D Left Join Projects P On P.DeptCode = D.DeptCode And P.DelFlag = 0 And P.StudyYear = '{1}' And P.IsApprove = 1 ";
if (ddlMonth.SelectedIndex != 0)
{
strSql += btc.getMonthOfMonth(ddlMonth.SelectedValue, ddlSearchYear.SelectedValue);
}
if (ddlQuarter.SelectedIndex != 0)
{
strSql += btc.getMonthOfQuarter(ddlQuarter.SelectedValue, ddlSearchYear.SelectedValue);
}
strSql += @" Left Join MainSubDepartment SD On SD.MainSubDeptCode = D.MainSubDeptCode
Where D.DelFlag = 0 And SD.MainDeptCode = '{0}'
Group By D.DeptCode, D.DeptName, SD.Sort, D.Sort Order By SD.Sort, D.Sort ";
DataView dv1 = Conn.Select(string.Format(strSql, CurrentUser.MainDeptID, ddlSearchYear.SelectedValue));
//¤Ø³ÀÒ¾â¤Ã§¡ÒÃ
strSql = @" Select D.DeptCode, D.DeptName As Name, IsNull(Sum(P.Quality), 0) / Case Count(P.ProjectsCode) When 0 Then 1 Else Count(P.ProjectsCode) End data2, SD.Sort, D.Sort
From Department D Left Join Projects P On P.DeptCode = D.DeptCode And P.DelFlag = 0
And P.StudyYear = '{1}' And P.IsApprove = 1 " ;
if (ddlMonth.SelectedIndex != 0)
{
strSql += btc.getMonthOfMonth(ddlMonth.SelectedValue, ddlSearchYear.SelectedValue);
}
if (ddlQuarter.SelectedIndex != 0)
{
strSql += btc.getMonthOfQuarter(ddlQuarter.SelectedValue, ddlSearchYear.SelectedValue);
}
strSql += @" Left Join MainSubDepartment SD On SD.MainSubDeptCode = D.MainSubDeptCode
Where D.DelFlag = 0 And SD.MainDeptCode = '{0}'
Group By D.DeptCode, D.DeptName, SD.Sort, D.Sort Order By SD.Sort, D.Sort ";
DataView dv2 = Conn.Select(string.Format(strSql, CurrentUser.MainDeptID, ddlSearchYear.SelectedValue));
if (dv.Count > 0)
{
for (int j = 0; j < dv.Count; j++)
{
DataRow[] drData1 = dv1.Table.Select("DeptCode = '" + dv[j]["DeptCode"].ToString() + "'");
if (drData1.Length > 0)
{
dv[j]["data1"] = Convert.ToInt32(drData1[0]["data1"]);
}
DataRow[] drData2 = dv2.Table.Select("DeptCode = '" + dv[j]["DeptCode"].ToString() + "'");
if (drData2.Length > 0)
{
dv[j]["data2"] = Convert.ToInt32(drData2[0]["data2"]);
}
}
}
if (dv.Count > 0)
{
ReportGraph("MSColumn2D", dv, 1);
}
else
{
dv = Conn.Select("Select '' As AcCode, '-' As FullName, '-' As DeptName, 0 As TotalBalance, '' As Name, 0 As data, 0 As data1, 0 As data2, 0 As data3 From Config");
ReportGraph("MSColumn2D", dv, 1);
}
GridView1.DataSource = dv;
GridView1.DataBind();
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 P.ProjectsCode, P.StudyYear, P.ProjectsName, P.Df, Ep.EmpID, Ep.EmpName, P.Sdate, P.Edate,
P.Sort, e.DeptName, P.IsApprove, IsNull(P.Quality, 0) Quality
From Projects P Left Join dtStrategies S On P.ProjectsCode = S.ProjectsCode
Left Join ProjectsApproveDetail PD On PD.ProjectsCode = P.ProjectsCode
Left Join Employee d On PD.EmpID = d.EmpID
Left Join Employee Ep On P.CreateUser = Ep.EmpID
Left Join Department e On P.DeptCode = e.DeptCode
Left Join MainSubDepartment MSD On e.MainSubDeptCode = MSD.MainSubDeptCode
Left Join MainDepartment MD On MSD.MainDeptCode = MD.MainDeptCode
Where P.DelFlag = 0 And d.DelFlag = 0 And d.hideFlag = 0 And P.StudyYear = '{0}' And P.SchoolID = '{1}' ";
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 (ddlMonth.SelectedIndex != 0)
{
strSql += btc.getMonthOfMonth(ddlMonth.SelectedValue, ddlSearchYear.SelectedValue);
}
if (ddlQuarter.SelectedIndex != 0)
{
strSql += btc.getMonthOfQuarter(ddlQuarter.SelectedValue, ddlSearchYear.SelectedValue);
}
DataView dv9 = Conn.Select(string.Format(strSql + " Group By P.ProjectsCode, P.StudyYear, P.ProjectsName, P.Df, Ep.EmpID, Ep.EmpName, P.Sdate, P.Edate, P.Sort, e.DeptName, P.IsApprove, P.Quality Order By P.Sort Desc ", ddlSearchYear.SelectedValue, CurrentUser.SchoolID));
//TotalAmount = 0;
////àªç¤¼ÅÃÇÁ
//try
//{
// DataTable dt = dvTotalAmout.ToTable();
// tTotalAmount = Convert.ToDecimal(dt.Compute("Sum(TotalMoney)", dvTotalAmout.RowFilter));
//}
//catch (Exception ex)
//{
//}
DataGridView1.DataSource = dv9;
lblSearchTotal.InnerText = dv9.Count.ToString();
DataGridView1.DataBind();
}
public override void DataBind()
{
string strSql = "";
DataView dv = null;
string strNotPATWit = "";
string strNotPATWit2 = "";
if (CurrentUser.MainDeptID.ToUpper() == "BA422CC9-6E77-4D43-BAFB-7539B9286E59")
{
strNotPATWit = " And SD.MainSubDeptCode Not In ('80F1E0B3-E2A8-458E-A6E0-EAFD3593D56E', '8D892520-6C31-4BEB-A746-4BD69A8E3037') ";
strNotPATWit2 = " And MSD.MainSubDeptCode Not In ('80F1E0B3-E2A8-458E-A6E0-EAFD3593D56E', '8D892520-6C31-4BEB-A746-4BD69A8E3037') ";
}
//â¤Ã§¡ÒÃ͹ØÁѵԷÑé§ËÁ´
strSql = @" Select D.DeptCode, REPLACE(D.DeptName,N'Êӹѡ','ʹ.') As Name, Count(P.ProjectsCode) data, 0 As data1, 0 As data2, SD.Sort, D.Sort
From Department D Left Join Projects P On P.DeptCode = D.DeptCode And P.DelFlag = 0 And P.StudyYear = '{1}' And P.IsApprove = 1 ";
if (ddlMonth.SelectedIndex != 0)
{
strSql += btc.getMonthOfMonth(ddlMonth.SelectedValue, ddlSearchYear.SelectedValue);
}
if (ddlQuarter.SelectedIndex != 0)
{
strSql += btc.getMonthOfQuarter(ddlQuarter.SelectedValue, ddlSearchYear.SelectedValue);
}
strSql += @" Left Join MainSubDepartment SD On SD.MainSubDeptCode = D.MainSubDeptCode
Where D.DelFlag = 0 And SD.MainDeptCode = '{0}' {2}
Group By D.DeptCode, D.DeptName, SD.Sort, D.Sort Order By SD.Sort, D.Sort ";
dv = Conn.Select(string.Format(strSql, CurrentUser.MainDeptID, ddlSearchYear.SelectedValue, strNotPATWit));
//§ºÍ¹ØÁѵԵÑ駢Öé¹
strSql = @" Select D.DeptCode, D.DeptName As Name, IsNull(Sum(A.TotalAmount), 0) data1, SD.Sort, D.Sort
From Department D Left Join Projects P On P.DeptCode = D.DeptCode And P.DelFlag = 0 And P.StudyYear = '{1}' And P.IsApprove = 1 ";
if (ddlMonth.SelectedIndex != 0)
{
strSql += btc.getMonthOfMonth(ddlMonth.SelectedValue, ddlSearchYear.SelectedValue);
}
if (ddlQuarter.SelectedIndex != 0)
{
strSql += btc.getMonthOfQuarter(ddlQuarter.SelectedValue, ddlSearchYear.SelectedValue);
}
strSql += @" Left Join MainSubDepartment SD On SD.MainSubDeptCode = D.MainSubDeptCode
Left Join Activity A On A.ProjectsCode = P.ProjectsCode And A.DelFlag = 0
Where D.DelFlag = 0 And SD.MainDeptCode = '{0}' {2}
Group By D.DeptCode, D.DeptName, SD.Sort, D.Sort Order By SD.Sort, D.Sort ";
DataView dv1 = Conn.Select(string.Format(strSql, CurrentUser.MainDeptID, ddlSearchYear.SelectedValue, strNotPATWit));
//§º¤èÒãªé¨èÒ¨ÃÔ§
strSql = @" Select D.DeptCode, D.DeptName As Name, IsNull(Sum(A.TotalAmount2), 0) data2, SD.Sort, D.Sort
From Department D Left Join Projects P On P.DeptCode = D.DeptCode And P.DelFlag = 0
And P.StudyYear = '{1}' And P.IsApprove = 1 " ;
if (ddlMonth.SelectedIndex != 0)
{
strSql += btc.getMonthOfMonth(ddlMonth.SelectedValue, ddlSearchYear.SelectedValue);
}
if (ddlQuarter.SelectedIndex != 0)
{
strSql += btc.getMonthOfQuarter(ddlQuarter.SelectedValue, ddlSearchYear.SelectedValue);
}
strSql += @" Left Join MainSubDepartment SD On SD.MainSubDeptCode = D.MainSubDeptCode
Left Join Activity A On A.ProjectsCode = P.ProjectsCode And A.DelFlag = 0
Where D.DelFlag = 0 And SD.MainDeptCode = '{0}' {2}
Group By D.DeptCode, D.DeptName, SD.Sort, D.Sort Order By SD.Sort, D.Sort ";
DataView dv2 = Conn.Select(string.Format(strSql, CurrentUser.MainDeptID, ddlSearchYear.SelectedValue, strNotPATWit));
if (dv.Count > 0)
{
for (int j = 0; j < dv.Count; j++)
{
DataRow[] drData1 = dv1.Table.Select("DeptCode = '" + dv[j]["DeptCode"].ToString() + "'");
if (drData1.Length > 0)
{
dv[j]["data1"] = Convert.ToInt32(drData1[0]["data1"]);
}
DataRow[] drData2 = dv2.Table.Select("DeptCode = '" + dv[j]["DeptCode"].ToString() + "'");
if (drData2.Length > 0)
{
dv[j]["data2"] = Convert.ToInt32(drData2[0]["data2"]);
}
}
}
if (dv.Count > 0)
{
ReportGraph("MSColumn2D", dv, 1);
}
else
{
dv = Conn.Select("Select '' As AcCode, '-' As FullName, '-' As DeptName, 0 As TotalBalance, '' As Name, 0 As data, 0 As data1, 0 As data2, 0 As data3 From Config");
ReportGraph("MSColumn2D", dv, 1);
}
GridView1.DataSource = dv;
GridView1.DataBind();
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 ";
dvTotalAmount = Conn.Select(string.Format(strSql, ddlSearchYear.SelectedValue));
strSql = @"Select P.ProjectsCode, IsNull(Sum(A.TotalAmount2), 0) TotalMoney From Projects P
Left Join Activity A On P.ProjectsCode = A.ProjectsCode
Where P.DelFlag = 0 And P.StudyYear = '{0}' Group By P.ProjectsCode ";
dvTotalRealAmount = Conn.Select(string.Format(strSql, ddlSearchYear.SelectedValue));
strSql = @" Select P.ProjectsCode, P.StudyYear, P.ProjectsName, P.Df, Ep.EmpID, Ep.EmpName, P.Sdate, P.Edate,
P.Sort, e.DeptName, P.IsApprove, IsNull(Sum(Av.TotalAmount), 0) As TotalAmount, IsNull(Sum(Av.TotalAmount2), 0) As TotalAmount2
From Projects P Left Join dtStrategies S On P.ProjectsCode = S.ProjectsCode
Left Join Activity Av On Av.ProjectsCode = P.ProjectsCode And Av.DelFlag = 0
Left Join ProjectsApproveDetail PD On PD.ProjectsCode = P.ProjectsCode
Left Join Employee d On PD.EmpID = d.EmpID
Left Join Employee Ep On P.CreateUser = Ep.EmpID
Left Join Department e On P.DeptCode = e.DeptCode
Left Join MainSubDepartment MSD On e.MainSubDeptCode = MSD.MainSubDeptCode
Left Join MainDepartment MD On MSD.MainDeptCode = MD.MainDeptCode
Where P.DelFlag = 0 And d.DelFlag = 0 And d.hideFlag = 0 And P.IsApprove = 1 And P.StudyYear = '{0}' And P.SchoolID = '{1}' {2} ";
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 (ddlMonth.SelectedIndex != 0)
{
strSql += btc.getMonthOfMonth(ddlMonth.SelectedValue, ddlSearchYear.SelectedValue);
}
if (ddlQuarter.SelectedIndex != 0)
{
strSql += btc.getMonthOfQuarter(ddlQuarter.SelectedValue, ddlSearchYear.SelectedValue);
}
DataView dv9 = Conn.Select(string.Format(strSql + " Group By P.ProjectsCode, P.StudyYear, P.ProjectsName, P.Df, Ep.EmpID, Ep.EmpName, P.Sdate, P.Edate, P.Sort, e.DeptName, P.IsApprove, P.Quality Order By P.Sort Desc ", ddlSearchYear.SelectedValue, CurrentUser.SchoolID, strNotPATWit2));
//TotalAmount = 0;
////àªç¤¼ÅÃÇÁ
//try
//{
// DataTable dt = dvTotalAmount.ToTable();
// tTotalAmount = Convert.ToDecimal(dt.Compute("Sum(TotalMoney)", dvTotalAmount.RowFilter));
//}
//catch (Exception ex)
//{
//}
DataGridView1.DataSource = dv9;
lblSearchTotal.InnerText = dv9.Count.ToString();
DataGridView1.DataBind();
}
