/// <summary> /// CSV出力ボタンを押すことで、 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void CSVOutput_Click(object sender, EventArgs e) { string NameValue = NameBox.Text; try { DataSet ds = MakeDataTables(Sqlcode, Dbcode); DataTable DT = ds.Tables[0]; DataGridView1.DataSource = DT; DataGridView1.DataBind(); ConvertDAtaToCSV0(DT, @"E:\testCSV\" + NameValue + ".csv", true); Csv_Message.Text = "変換処理に成功しました、確認してみてください"; }catch (FormatException) { Csv_Message.Text = "処理に失敗しました、保存名を指定して下さい"; } }
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(); }
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(); }
/// <summary> /// 导入房屋平面图纸 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void btnImportHouseImage_OnClick(object sender, EventArgs e) { this.lblImportImage.Text = ""; string importImagePath = txtImportImagePath.Text.Trim(); if (importImagePath != "") { if (!Directory.Exists(importImagePath)) { this.lblImportImage.Text = "您输入的文件导入路径不存在!"; return; } int rel = 0; DataTable dtResult = new DataTable(); dtResult.Columns.Add("Index", Type.GetType("System.String")); dtResult.Columns.Add("DataId", Type.GetType("System.String")); dtResult.Columns.Add("HouseNo", Type.GetType("System.String")); dtResult.Columns.Add("ImageName", Type.GetType("System.String")); foreach (string item in Directory.GetDirectories(importImagePath)) { DirectoryInfo directoryinfo = new DirectoryInfo(item); string houseNo = directoryinfo.Name; DateTime creationTime = directoryinfo.CreationTime; string strSql = "select * from HM_HouseBan where HouseNo =:HouseNo "; OracleParameter[] parameters = { new OracleParameter("HouseNo", OracleDbType.Varchar2, 50) }; parameters[0].Value = houseNo; //string strSql = "select * from HM_HouseBan where HouseNo ='"+ houseNo + "' "; string connectionString = ConfigurationManager.ConnectionStrings["jmgf_new_oracle"].ConnectionString; DataTable dt = OracleHelper.ExecuteDataTable(connectionString, CommandType.Text, strSql, parameters); if (dt != null && dt.Rows.Count > 0) { try { Guid fid = Guid.Empty; FileInfo[] arrFile = directoryinfo.GetFiles(); foreach (FileInfo fileInfo in arrFile) { string fileName = Path.GetFileName(fileInfo.FullName); string fileExtensionName = Path.GetExtension(fileInfo.FullName); string fileid = fileName.Replace(fileExtensionName, ""); Guid fileId = fid; if (fid == Guid.Empty) { fileId = Guid.NewGuid(); } using (Stream inStream = StaticFunction.StreamToMemoryStream(fileInfo.OpenRead())) { using (Stream fmpStream = FileManager.CreateFile(fileId, fileName)) { StaticFunction.StreamSourceStreamToTargetStream(inStream, fmpStream); StringBuilder strSql0 = new StringBuilder(); strSql0.Append(" insert into Sys_FileRelation(Id,ModuleId,KeyId,FileId,FileName,CreatorUserId,CreationTime,FileType) "); strSql0.Append(" values(SYS_FILERELATION_SEQ.Nextval,:ModuleId,:KeyId,:FileId,:FileName,:CreatorUserId,:CreationTime,:FileType)"); OracleParameter[] parameters0 = { new OracleParameter("ModuleId", OracleDbType.Int32, 10), new OracleParameter("KeyId", OracleDbType.Int32, 10), new OracleParameter("FileId", OracleDbType.Raw), new OracleParameter("FileName", OracleDbType.Varchar2, 200), new OracleParameter("CreatorUserId", OracleDbType.Int32, 10), new OracleParameter("CreationTime", OracleDbType.Date), new OracleParameter("FileType", OracleDbType.Int32, 1) }; parameters0[0].Value = Topevery.ModuleType.HouseBan; parameters0[1].Value = int.Parse(dt.Rows[0]["Id"].ToString()); parameters0[2].Value = fileId.ToByteArray(); parameters0[3].Value = fileName; parameters0[4].Value = 0; parameters0[5].Value = System.DateTime.Now; parameters0[6].Value = FileHelper.GetAttachType(fileExtensionName); rel += OracleHelper.ExecuteNonQuery(connectionString, CommandType.Text, strSql0.ToString(), parameters0); DataRow newRow = dtResult.NewRow(); newRow["Index"] = rel.ToString(); newRow["DataId"] = dt.Rows[0]["Id"].ToString(); newRow["HouseNo"] = houseNo; newRow["ImageName"] = fileName; dtResult.Rows.Add(newRow); } } } } catch (Exception ex) { throw ex; } finally { } } } DataGridView1.DataSource = dtResult; DataGridView1.DataBind(); this.lblImportImage.Text = "成功导入了" + rel + "张图片"; } else { this.lblImportImage.Text = "请输入文件导入路径!"; } }