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