/// <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 = "请输入文件导入路径！";
}
}
