ひろしブック: 5月 2015

2015年5月31日日曜日

ADO.NET

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

public partial class IdMaster : System.Web.UI.Page
{
private string strConn;

private Boolean isRegisterd() {
try
{
string Sql = " SELECT * FROM [Jisseki_Report_Ibaraki].dbo.ID WHERE COCODE=@COCODE ";
using (SqlConnection Conn = new SqlConnection(strConn))
{
Conn.Open();
using (SqlCommand cmd = new SqlCommand(Sql, Conn))
{

cmd.Parameters.Add(new SqlParameter("@COCODE", this.txtCOCODE.Text));

using (SqlDataReader Reader = cmd.ExecuteReader()) {
if (Reader.HasRows)
{
return true;
}
else
{
return false;
}

}
}

}
}
catch
{
throw;

}

}

private void insertUnitPrice()
{
string Sql = "SELECT TOP 1 * FROM [dbo].[UnitPrice]";
using (SqlConnection Conn = new SqlConnection(strConn))
{
Conn.Open();
using (SqlCommand cmd = new SqlCommand(Sql, Conn))
{

SqlDataReader Reader = cmd.ExecuteReader();
if (Reader.HasRows)
{
Reader.Read();
//登録
string InsertSQL = "INSERT INTO [Jisseki_Report_Ibaraki].[dbo].[UnitPrice] "
+ " ([Code] ,[BigSize] ,[MediumSmall] ,[Average] ,[Kamotu7t] ,[Kamotu6DP9_5t] "
+ " ,[Kamotu4DP9_3t] ,[Kamotu2DP9_2DP5t] ,[Over2001cc] ,[To2000From1000cc] "
+ " ,[Over30] ,[LessThan29] ,[MemberFee] ,[COCODE]) "
+ "VALUES"
+ "("
+" @Code"
+" ,@BigSize"
+" ,@MediumSmall"
+" ,@Average"
+" ,@Kamotu7t"
+" ,@Kamotu6DP9_5t"
+" ,@Kamotu4DP9_3t"
+" ,@Kamotu2DP9_2DP5t"
+" ,@Over2001cc"
+" ,@To2000From1000cc"
+" ,@Over30"
+" ,@LessThan29"
+" ,@MemberFee"
+" ,@COCODE"

+ ")";
using (SqlConnection ConnIns = new SqlConnection(strConn))
{
ConnIns.Open();
//using (SqlTransaction Tran = ConnIns.BeginTransaction())
//{

SqlCommand InsertCmd = new SqlCommand();
InsertCmd.Connection = ConnIns;
InsertCmd.CommandText = InsertSQL;
//InsertCmd.Transaction = Tran;
InsertCmd.Parameters.Add(new SqlParameter("@Code", Reader["Code"].ToString()));
InsertCmd.Parameters.Add(new SqlParameter("@BigSize", Reader["BigSize"].ToString()));
InsertCmd.Parameters.Add(new SqlParameter("@MediumSmall", Reader["MediumSmall"].ToString()));
InsertCmd.Parameters.Add(new SqlParameter("@Average", Reader["Average"].ToString()));
InsertCmd.Parameters.Add(new SqlParameter("@Kamotu7t", Reader["Kamotu7t"].ToString()));
InsertCmd.Parameters.Add(new SqlParameter("@Kamotu6DP9_5t", Reader["Kamotu6DP9_5t"].ToString()));
InsertCmd.Parameters.Add(new SqlParameter("@Kamotu4DP9_3t", Reader["Kamotu4DP9_3t"].ToString()));
InsertCmd.Parameters.Add(new SqlParameter("@Kamotu2DP9_2DP5t", Reader["Kamotu2DP9_2DP5t"].ToString()));
InsertCmd.Parameters.Add(new SqlParameter("@Over2001cc", Reader["Over2001cc"].ToString()));
InsertCmd.Parameters.Add(new SqlParameter("@To2000From1000cc", Reader["To2000From1000cc"].ToString()));
InsertCmd.Parameters.Add(new SqlParameter("@Over30", Reader["Over30"].ToString()));
InsertCmd.Parameters.Add(new SqlParameter("@LessThan29", Reader["LessThan29"].ToString()));
InsertCmd.Parameters.Add(new SqlParameter("@MemberFee", Reader["MemberFee"].ToString()));
InsertCmd.Parameters.Add(new SqlParameter("@COCODE", this.txtCOCODE.Text));
InsertCmd.ExecuteNonQuery();

//}
}
}
}
}

}

private void setGridView() {
try
{
string Sql = " SELECT * FROM [Jisseki_Report_Ibaraki].dbo.ID ";
using (SqlConnection Conn = new SqlConnection(strConn))
{
Conn.Open();
using (SqlCommand cmd = new SqlCommand(Sql, Conn))
{
using (SqlDataAdapter Adapter = new SqlDataAdapter(Sql, Conn))
{
Adapter.SelectCommand = cmd;
DataTable ID = new DataTable("IDマスタ");
Adapter.Fill(ID);
if (ID.Rows.Count == 0)
{
GridView1.EmptyDataText = "IDマスタに登録がありません。";
}
else
{

GridView1.DataSource = ID;
GridView1.DataBind();
}
}
}
Conn.Close();
}
}
catch
{

}

}

protected void Page_Load(object sender, EventArgs e)
{
try{

//ログインしていなければ表示しない
if (Session["COCODE"] == null)
{
Response.Redirect(URL.LOGIN_DEALER);
}

//接続文字列
strConn = ConfigurationManager.ConnectionStrings["JissekiConnectionString"].ConnectionString;
setGridView();
}catch{
}

}

protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
{
try
{
int i = GridView1.SelectedIndex;
String Sql = " SELECT * FROM Jisseki_Report_Ibaraki.dbo.ID "
+ " WHERE "
+ " COCODE = @COCODE ";

using (SqlConnection Conn = new SqlConnection(strConn))
{
Conn.Open();
using (SqlCommand cmd = new SqlCommand(Sql, Conn))
{

cmd.Parameters.Add(new SqlParameter("@COCODE", GridView1.Rows[i].Cells[1].Text));

using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
reader.Read();
txtCOCODE.Text = reader["COCODE"].ToString();
txtUID.Text = reader["UID"].ToString();
txtCONAME.Text = reader["CONAME"].ToString();
txtRepName.Text = reader["RepName"].ToString();
txtPostalCode.Text = reader["PostalCode"].ToString();
txtAddress.Text = reader["Address"].ToString();
txtTel.Text = reader["Tel"].ToString();
txtPassword.Text = reader["Password"].ToString();
txtMember.Text = reader["Member"].ToString();
txtMemberType.Text = reader["MemberType"].ToString();
txtshort_CONAME.Text = reader["short_CONAME"].ToString();
txtPosition.Text = reader["Position"].ToString();

}
else
{
return;
}

}

}
Conn.Close();
}
}
catch {

}

}
#region"ボタン"
protected void btnUpdate_Click(object sender, EventArgs e)
{
//Update
string Sql= " UPDATE [Jisseki_Report_Ibaraki].[dbo].[ID] "
+ " SET "
+ " [COCODE] = @COCODE "
+ " ,[UID] = @UID "
+ " ,[CONAME] = @CONAME "
+ " ,[RepName] = @RepName "
+ " ,[PostalCode] = @PostalCode "
+ " ,[Address] = @Address "
+ " ,[Tel] = @Tel "
+ " ,[Password] = @Password "
+ " ,[Member] = @Memeber "
+ " ,[MemberType] = @MemeberType "
+ " ,[short_CONAME] = @short_CONAME "
+ " ,[Position] = @Position "
+ " ,[isCanceled] = @isCanceled "
+ " WHERE "
//Key
+ " COCODE = @Key"
;

using (SqlConnection Conn = new SqlConnection(strConn))
{
Conn.Open();
using (SqlTransaction Tran = Conn.BeginTransaction())
{
try
{

using (SqlCommand cmd = new SqlCommand(Sql, Conn, Tran))
{
cmd.Parameters.Add(new SqlParameter("@COCODE", this.txtCOCODE.Text));
cmd.Parameters.Add(new SqlParameter("@UID", this.txtUID.Text));
cmd.Parameters.Add(new SqlParameter("@CONAME", this.txtCONAME.Text));
cmd.Parameters.Add(new SqlParameter("@RepName", this.txtRepName.Text));
cmd.Parameters.Add(new SqlParameter("@PostalCode", this.txtPostalCode.Text));
cmd.Parameters.Add(new SqlParameter("@Address", this.txtAddress.Text));
cmd.Parameters.Add(new SqlParameter("@Tel", this.txtTel.Text));
cmd.Parameters.Add(new SqlParameter("@Password", this.txtPassword.Text));
cmd.Parameters.Add(new SqlParameter("@Memeber", this.txtMember.Text));
cmd.Parameters.Add(new SqlParameter("@MemeberType", this.txtMemberType.Text));
cmd.Parameters.Add(new SqlParameter("@short_CONAME", this.txtshort_CONAME.Text));
cmd.Parameters.Add(new SqlParameter("@Position", this.txtPosition.Text));
cmd.Parameters.Add(new SqlParameter("@isCanceled", this.txtisCanceled.Text));
cmd.Parameters.Add(new SqlParameter("@Key", this.txtCOCODE.Text));
cmd.ExecuteNonQuery();

}

Tran.Commit();
setGridView();
this.lblMsg.Text = "更新しました";
this.lblMsg.BackColor = System.Drawing.Color.Pink;

}
catch (Exception ex)
{
this.lblMsg.Text = ex.Message;
Tran.Rollback();

}
}

}

//GridViewでバインド

}

protected void btnDelete_Click(object sender, EventArgs e)
{

//削除確認
//OnClient_Clickでやる
//削除
try
{
string Sql = " DELETE FROM [Jisseki_Report_Ibaraki].dbo.ID "
+ " WHERE COCODE = @Key; "
+ " DELETE FROM [Jisseki_Report_Ibaraki].[dbo].[UnitPrice] "
+ " WHERE COCODE = @Key; ";

using (SqlConnection Conn = new SqlConnection(strConn))
{
Conn.Open();
using (SqlCommand cmd = new SqlCommand(Sql, Conn))
{
cmd.Parameters.Add(new SqlParameter("@Key", this.txtCOCODE.Text));
cmd.ExecuteNonQuery();

setGridView();
this.lblMsg.Text = "削除しました";
this.lblMsg.BackColor = System.Drawing.Color.Pink;

}
}
}
catch (Exception ex)
{
this.lblMsg.Text = ex.Message;

}
}

protected void btnInsert_Click(object sender, EventArgs e)
{
string Sql = " INSERT INTO [Jisseki_Report_Ibaraki].[dbo].[ID] "
+ "("
+ " [COCODE] "
+ ",[UID] "
+ ",[CONAME] "
+ ",[RepName] "
+ ",[PostalCode] "
+ ",[Address] "
+ ",[Tel] "
+ ",[Password] "
+ ",[Member] "
+ ",[MemberType] "
+ ",[short_CONAME] "
+ ",[Position] "
//+ ",[isCanceled] "
+ ")"
+ " VALUES "
+ "("
+ " @COCODE "
+ ",@UID "
+ ",@CONAME "
+ ",@RepName "
+ ",@PostalCode "
+ ",@Address "
+ ",@Tel "
+ ",@Password "
+ ",@Member "
+ ",@MemberType "
+ ",@short_CONAME "
+ ",@Position "
//+ ",@isCanceled"
+ ")";

//入力チェック
try
{
using (SqlConnection Conn = new SqlConnection(strConn))
{
Conn.Open();
using (SqlTransaction Tran = Conn.BeginTransaction())
{
try
{

SqlCommand cmd = new SqlCommand();
cmd.Connection = Conn;
cmd.CommandText = Sql;
cmd.Transaction = Tran;
cmd.Parameters.Add(new SqlParameter("@COCODE", this.txtCOCODE.Text));
cmd.Parameters.Add(new SqlParameter("@UID", this.txtUID.Text));
cmd.Parameters.Add(new SqlParameter("@CONAME", this.txtCONAME.Text));
cmd.Parameters.Add(new SqlParameter("@RepName", this.txtRepName.Text));
cmd.Parameters.Add(new SqlParameter("@PostalCode", this.txtPostalCode.Text));
cmd.Parameters.Add(new SqlParameter("@Address", this.txtAddress.Text));
cmd.Parameters.Add(new SqlParameter("@Tel", this.txtTel.Text));
cmd.Parameters.Add(new SqlParameter("@Password", this.txtPassword.Text));
cmd.Parameters.Add(new SqlParameter("@Member", this.txtMember.Text));
cmd.Parameters.Add(new SqlParameter("@MemberType", this.txtMemberType.Text));
cmd.Parameters.Add(new SqlParameter("@short_CONAME", this.txtshort_CONAME.Text));
cmd.Parameters.Add(new SqlParameter("@Position", this.txtPosition.Text));
//cmd.Parameters.Add(new SqlParameter("@isCanceled", this.txtPosition.Text));
cmd.ExecuteNonQuery();

//単価マスタも登録する
this.insertUnitPrice();

//Commit Transaction
Tran.Commit();
this.setGridView();
this.lblMsg.Text="登録しました";
this.lblMsg.BackColor = System.Drawing.Color.Pink;

}
catch
{
//Rollback Transaction
Tran.Rollback();
throw;

}

}
}

}
catch (SqlException SqlEx)
{
if (SqlEx.Number == 2627)
{
this.lblMsg.Text="既に登録済です";
this.lblMsg.BackColor = System.Drawing.Color.Pink;
//Response.Write("既に登録済です");
}
else
{
//Response.Write("" + SqlEx.Message + "");
//Response.Write("" + SqlEx.StackTrace + "");
this.lblMsg.Text = "SQLエラー";
this.lblMsg.BackColor = System.Drawing.Color.Pink;

}

}
catch
{

//Response.Write("" + ex.Message + "");
//Response.Write("" + ex.StackTrace + "");
this.lblMsg.Text = "エラー";
this.lblMsg.BackColor = System.Drawing.Color.Pink;

}

}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Globalization;
using System.Data.SqlClient;
using System.Configuration;
using System.IO;
using System.Text;
using Jisseki_Report_Ibaraki.Tools;

namespace Jisseki_Report_Ibaraki.jada.download
{
public partial class Jisseki_Report : System.Web.UI.Page
{

private string strConn;
//TODO　パスとファイルをデータベースか設定ファイルに
string DownloadWritePath;
string DownloadFileName ;

public string getSql()
{

string Sql = " SELECT "
//Header and ID Master
+ " H.COCODE as COCODE, I.CONAME as CONAME , H.YearRep as YearRep , H.MonthRep as MonthRep "
//Mito
+ " , M.Kamotu1 as M_Kamotu1, M.Kamotu2 as M_Kamotu2, M.Kamotu3 as M_Kamotu3 , M.Kamotu4 as M_Kamotu4 "
+ " , M.Bus1 as M_Bus1, M.Bus2 as M_Bus2 "
+ " , M.JK_J1 as M_JK_J1 , M.JK_K1 as M_JK_K1 , M.JK_J2 as M_JK_J2 , M.JK_K2 as M_JK_K2 , M.JK_J3 as M_JK_J3 , M.JK_K3 as M_JK_K3 "
+ " , M.SubTotal1 as M_SubTotal1 , M.Total1 as M_Total1 "

//Tuchiura
+ " , TC.Kamotu1 as TC_Kamotu1, TC.Kamotu2 as TC_Kamotu2, TC.Kamotu3 as TC_Kamotu3 , TC.Kamotu4 as TC_Kamotu4 "
+ " , TC.Bus1 as TC_Bus1, TC.Bus2 as TC_Bus2 "
+ " , TC.JK_J1 as TC_JK_J1 , TC.JK_K1 as TC_JK_K1 , TC.JK_J2 as TC_JK_J2 , TC.JK_K2 as TC_JK_K2 , TC.JK_J3 as TC_JK_J3 , TC.JK_K3 as TC_JK_K3 "
+ " , TC.SubTotal1 as TC_SubTotal1 , TC.Total1 as TC_Total1 "

//Tukuba
+ " , TK.Kamotu1 as TK_Kamotu1, TK.Kamotu2 as TK_Kamotu2, TK.Kamotu3 as TK_Kamotu3 , TK.Kamotu4 as TK_Kamotu4 "
+ " , TK.Bus1 as TK_Bus1, TK.Bus2 as TK_Bus2 "
+ " , TK.JK_J1 as TK_JK_J1 , TK.JK_K1 as TK_JK_K1 , TK.JK_J2 as TK_JK_J2 , TK.JK_K2 as TK_JK_K2 , TK.JK_J3 as TK_JK_J3 , TK.JK_K3 as TK_JK_K3 "
+ " , TK.SubTotal1 as TK_SubTotal1 , TK.Total1 as TK_Total1 "

//Sonota
+ " , S.Kamotu1 as S_Kamotu1, S.Kamotu2 as S_Kamotu2, S.Kamotu3 as S_Kamotu3 , S.Kamotu4 as S_Kamotu4 "
+ " , S.Bus1 as S_Bus1, S.Bus2 as S_Bus2 "
+ " , S.JK_J1 as S_JK_J1 , S.JK_K1 as S_JK_K1 , S.JK_J2 as S_JK_J2 , S.JK_K2 as S_JK_K2 , S.JK_J3 as S_JK_J3 , S.JK_K3 as S_JK_K3 "
+ " , S.SubTotal1 as S_SubTotal1 , S.Total1 as S_Total1 "

//Goukei
+ " , G.Kamotu1 as G_Kamotu1, G.Kamotu2 as G_Kamotu2, G.Kamotu3 as G_Kamotu3 , G.Kamotu4 as G_Kamotu4 "
+ " , G.Bus1 as G_Bus1, G.Bus2 as G_Bus2 "
+ " , G.JK_J1 as G_JK_J1 , G.JK_K1 as G_JK_K1 , G.JK_J2 as G_JK_J2 , G.JK_K2 as G_JK_K2 , G.JK_J3 as G_JK_J3 , G.JK_K3 as G_JK_K3 "
+ " , G.SubTotal1 as G_SubTotal1 , G.Total1 as G_Total1 "

//ポジション追加
+ " , I.Position as Position "

+ " FROM "
+ " [Jisseki_Report_Ibaraki].[dbo].[Jisseki_Header] H "
+ " INNER JOIN "
+ " [Jisseki_Report_Ibaraki].[dbo].[Jisseki_Mito] M "
+ " ON H.COCODE = M.COCODE AND H.YearRep = M.YearRep AND H.MonthRep = M.MonthRep "
+ " INNER JOIN "
+ " [Jisseki_Report_Ibaraki].[dbo].[Jisseki_Tuchiura] TC "
+ " ON H.COCODE = TC.COCODE AND H.YearRep = TC.YearRep AND H.MonthRep = TC.MonthRep "
+ " INNER JOIN "
+ " [Jisseki_Report_Ibaraki].[dbo].[Jisseki_Tukuba] TK "
+ " ON H.COCODE = TK.COCODE AND H.YearRep = TK.YearRep AND H.MonthRep = TK.MonthRep "
+ " INNER JOIN "
+ " [Jisseki_Report_Ibaraki].[dbo].[Jisseki_Sonota] S "
+ " ON H.COCODE = S.COCODE AND H.YearRep = S.YearRep AND H.MonthRep = S.MonthRep "
+ " INNER JOIN "
+ " [Jisseki_Report_Ibaraki].[dbo].[Jisseki_Goukei] G "
+ " ON H.COCODE = G.COCODE AND H.YearRep = G.YearRep AND H.MonthRep = G.MonthRep "
+ " INNER JOIN "
+ " [Jisseki_Report_Ibaraki].[dbo].[ID] I "
+ " ON H.COCODE = I.COCODE "
+ " WHERE "
+ " I.Member = 1 "//会員のみ(念のため)
+ " AND "
+ " H.YearRep = @YearRep "
+ " AND "
+ " H.MonthRep = @MonthRep ";

return Sql;

}

private bool writeData(string Sql , string qYearRep, string qMonthRep)
{
//using (SqlConnection Conn = new SqlConnection(strConn))
//{
SqlConnection Conn = new SqlConnection(strConn);
Conn.Open();
SqlCommand cmd = new SqlCommand(Sql, Conn);

cmd.Parameters.Add(new SqlParameter("@YearRep", qYearRep));
cmd.Parameters.Add(new SqlParameter("@MonthRep", qMonthRep));
SqlDataReader Reader = cmd.ExecuteReader();
if (Reader.HasRows)
{
string CsvLine = string.Empty;
using (StreamWriter Writer = new StreamWriter(DownloadWritePath + DownloadFileName, false, Encoding.Default))
{

//ヘッダーを書き出す
Writer.Write("COCODE, CONAME, YearRep, MonthRep,");
//Mito
Writer.Write("M_Kamotu1,M_Kamotu2,M_Kamotu3,M_Kamotu4,M_Bus1,M_Bus2,");
Writer.Write("M_JK_J1,M_JK_K1,M_JK_J2,M_JK_K2,M_JK_J3,M_JK_K3,");
Writer.Write("M_SubTotal1,M_Total1,");
//Tuchiura
Writer.Write("TC_Kamotu1,TC_Kamotu2,TC_Kamotu3,TC_Kamotu4,TC_Bus1,TC_Bus2,");
Writer.Write("TC_JK_J1,TC_JK_K1,TC_JK_J2,TC_JK_K2,TC_JK_J3,TC_JK_K3,");
Writer.Write("TC_SubTotal1,TC_Total1,");
//Tukuba
Writer.Write("TK_Kamotu1,TK_Kamotu2,TK_Kamotu3,TK_Kamotu4,TK_Bus1,TK_Bus2,");
Writer.Write("TK_JK_J1,TK_JK_K1,TK_JK_J2,TK_JK_K2,TK_JK_J3,TK_JK_K3,");
Writer.Write("TK_SubTotal1,TK_Total1,");
//Sonota
Writer.Write("S_Kamotu1,S_Kamotu2,S_Kamotu3,S_Kamotu4,S_Bus1,S_Bus2,");
Writer.Write("S_JK_J1,S_JK_K1,S_JK_J2,S_JK_K2,S_JK_J3,S_JK_K3,");
Writer.Write("S_SubTotal1,S_Total1,");
//Goukei
Writer.Write("G_Kamotu1,G_Kamotu2,G_Kamotu3,G_Kamotu4,G_Bus1,G_Bus2,");
Writer.Write("G_JK_J1,G_JK_K1,G_JK_J2,G_JK_K2,G_JK_J3,G_JK_K3,");
Writer.WriteLine("G_SubTotal1,G_Total1,Position");

while (Reader.Read())
{
//TODO NULLのとき

//Header
CsvLine = Reader["COCODE"].ToString();
CsvLine += ",";
CsvLine += Reader["CONAME"].ToString();
CsvLine += ",";
CsvLine += Reader["YearRep"].ToString();
CsvLine += ",";
CsvLine += Reader["MonthRep"].ToString();
CsvLine += ",";

//Mito
CsvLine += Reader["M_Kamotu1"].ToString();
CsvLine += ",";
CsvLine += Reader["M_Kamotu2"].ToString();
CsvLine += ",";
CsvLine += Reader["M_Kamotu3"].ToString();
CsvLine += ",";
CsvLine += Reader["M_Kamotu4"].ToString();
CsvLine += ",";
CsvLine += Reader["M_Bus1"].ToString();
CsvLine += ",";
CsvLine += Reader["M_Bus2"].ToString();
CsvLine += ",";
CsvLine += Reader["M_JK_J1"].ToString();
CsvLine += ",";
CsvLine += Reader["M_JK_K1"].ToString();
CsvLine += ",";
CsvLine += Reader["M_JK_J2"].ToString();
CsvLine += ",";
CsvLine += Reader["M_JK_K2"].ToString();
CsvLine += ",";
CsvLine += Reader["M_JK_J3"].ToString();
CsvLine += ",";
CsvLine += Reader["M_JK_K3"].ToString();
CsvLine += ",";
CsvLine += Reader["M_SubTotal1"].ToString();
CsvLine += ",";
CsvLine += Reader["M_Total1"].ToString();
CsvLine += ",";

//Tuchiura
CsvLine += Reader["TC_Kamotu1"].ToString();
CsvLine += ",";
CsvLine += Reader["TC_Kamotu2"].ToString();
CsvLine += ",";
CsvLine += Reader["TC_Kamotu3"].ToString();
CsvLine += ",";
CsvLine += Reader["TC_Kamotu4"].ToString();
CsvLine += ",";
CsvLine += Reader["TC_Bus1"].ToString();
CsvLine += ",";
CsvLine += Reader["TC_Bus2"].ToString();
CsvLine += ",";
CsvLine += Reader["TC_JK_J1"].ToString();
CsvLine += ",";
CsvLine += Reader["TC_JK_K1"].ToString();
CsvLine += ",";
CsvLine += Reader["TC_JK_J2"].ToString();
CsvLine += ",";
CsvLine += Reader["TC_JK_K2"].ToString();
CsvLine += ",";
CsvLine += Reader["TC_JK_J3"].ToString();
CsvLine += ",";
CsvLine += Reader["TC_JK_K3"].ToString();
CsvLine += ",";
CsvLine += Reader["TC_SubTotal1"].ToString();
CsvLine += ",";
CsvLine += Reader["TC_Total1"].ToString();
CsvLine += ",";

//Tukuba
CsvLine += Reader["TK_Kamotu1"].ToString();
CsvLine += ",";
CsvLine += Reader["TK_Kamotu2"].ToString();
CsvLine += ",";
CsvLine += Reader["TK_Kamotu3"].ToString();
CsvLine += ",";
CsvLine += Reader["TK_Kamotu4"].ToString();
CsvLine += ",";
CsvLine += Reader["TK_Bus1"].ToString();
CsvLine += ",";
CsvLine += Reader["TK_Bus2"].ToString();
CsvLine += ",";
CsvLine += Reader["TK_JK_J1"].ToString();
CsvLine += ",";
CsvLine += Reader["TK_JK_K1"].ToString();
CsvLine += ",";
CsvLine += Reader["TK_JK_J2"].ToString();
CsvLine += ",";
CsvLine += Reader["TK_JK_K2"].ToString();
CsvLine += ",";
CsvLine += Reader["TK_JK_J3"].ToString();
CsvLine += ",";
CsvLine += Reader["TK_JK_K3"].ToString();
CsvLine += ",";
CsvLine += Reader["TK_SubTotal1"].ToString();
CsvLine += ",";
CsvLine += Reader["TK_Total1"].ToString();
CsvLine += ",";

//Sonota
CsvLine += Reader["S_Kamotu1"].ToString();
CsvLine += ",";
CsvLine += Reader["S_Kamotu2"].ToString();
CsvLine += ",";
CsvLine += Reader["S_Kamotu3"].ToString();
CsvLine += ",";
CsvLine += Reader["S_Kamotu4"].ToString();
CsvLine += ",";
CsvLine += Reader["S_Bus1"].ToString();
CsvLine += ",";
CsvLine += Reader["S_Bus2"].ToString();
CsvLine += ",";
CsvLine += Reader["S_JK_J1"].ToString();
CsvLine += ",";
CsvLine += Reader["S_JK_K1"].ToString();
CsvLine += ",";
CsvLine += Reader["S_JK_J2"].ToString();
CsvLine += ",";
CsvLine += Reader["S_JK_K2"].ToString();
CsvLine += ",";
CsvLine += Reader["S_JK_J3"].ToString();
CsvLine += ",";
CsvLine += Reader["S_JK_K3"].ToString();
CsvLine += ",";
CsvLine += Reader["S_SubTotal1"].ToString();
CsvLine += ",";
CsvLine += Reader["S_Total1"].ToString();
CsvLine += ",";

//Goukei
CsvLine += Reader["G_Kamotu1"].ToString();
CsvLine += ",";
CsvLine += Reader["G_Kamotu2"].ToString();
CsvLine += ",";
CsvLine += Reader["G_Kamotu3"].ToString();
CsvLine += ",";
CsvLine += Reader["G_Kamotu4"].ToString();
CsvLine += ",";
CsvLine += Reader["G_Bus1"].ToString();
CsvLine += ",";
CsvLine += Reader["G_Bus2"].ToString();
CsvLine += ",";
CsvLine += Reader["G_JK_J1"].ToString();
CsvLine += ",";
CsvLine += Reader["G_JK_K1"].ToString();
CsvLine += ",";
CsvLine += Reader["G_JK_J2"].ToString();
CsvLine += ",";
CsvLine += Reader["G_JK_K2"].ToString();
CsvLine += ",";
CsvLine += Reader["G_JK_J3"].ToString();
CsvLine += ",";
CsvLine += Reader["G_JK_K3"].ToString();
CsvLine += ",";
CsvLine += Reader["G_SubTotal1"].ToString();
CsvLine += ",";
CsvLine += Reader["G_Total1"].ToString();
CsvLine += ",";
CsvLine += Reader["Position"].ToString();
Writer.WriteLine(CsvLine);

}

Writer.Close();
return true;

}
}
else
{
return false;

}

}

private void outData() {
//Response.CleaarContent();
Response.ContentType = "text/csv";//Defined in RFC4180
Response.AddHeader("Content-Disposition", "Attachment;filename=" + HttpUtility.UrlEncode(DownloadFileName));
Response.WriteFile(DownloadWritePath + DownloadFileName);
Response.End();

}
#region "イベント"
protected void Page_Load(object sender, EventArgs e)
{
//ログインしていなければ表示しない
if (Session["COCODE"] == null)
{
Response.Redirect(URL.LOGIN_DEALER);
}

//接続文字列
strConn = ConfigurationManager.ConnectionStrings["JissekiConnectionString"].ConnectionString;
DownloadWritePath = ConfigurationManager.AppSettings["DownloadWritePath"].ToString();

JapaneseCalendar jCalender = new JapaneseCalendar();
if (Page.IsPostBack)
{
//No28

}
else
{

//初期表示のみ
int iEra = jCalender.GetEra(DateTime.Now);
switch (iEra)
{
case 4://平成
lblEra.Text = "平成";
break;

case 3://昭和
lblEra.Text = "昭和";
break;

case 2://大正
lblEra.Text = "大正";

break;

case 1://明治
lblEra.Text = "明治";

break;
}
this.txtYearRep.Text = jCalender.GetYear(DateTime.Today).ToString();
this.txtMonthRep.Text = Utility.covertDigit2(DateTime.Today.AddMonths(-1).Month);

//string strYearMonthDay = (jCalender.GetYear(DateTime.Today).ToString() + DateTime.Today.Month.ToString() + DateTime.Today.Day.ToString());
string strYearMonthDay = (DateTime.Today.Year.ToString() + Utility.covertDigit2(DateTime.Today.Month) + Utility.covertDigit2(DateTime.Today.Day));
string strHourMinSec = (Utility.covertDigit2(DateTime.Now.Hour) + Utility.covertDigit2(DateTime.Now.Minute) + Utility.covertDigit2(DateTime.Now.Second));

this.txtFileName.Text = DateTime.Today.Year.ToString() + this.txtMonthRep.Text + "Jisseki" + strYearMonthDay + strHourMinSec + ".csv";

}

}

protected void btnDownload_Click(object sender, EventArgs e)
{
try
{
//入力チェック
//TODO::JavaScriptでも
if (this.txtYearRep.Text.Trim() == string.Empty)
{
lblMsg.Text = "ダウンロードする実績報告書の年月を入力してください。";
this.lblMsg.BackColor = System.Drawing.Color.Pink;
return;
}
else
{
lblMsg.Text = "";
}

if (this.txtMonthRep.Text.Trim() == string.Empty)
{
lblMsg.Text = "ダウンロードする実績報告書の年月を入力してください。";
this.lblMsg.BackColor = System.Drawing.Color.Pink;
return;
}
else
{
lblMsg.Text = "";
}

if (this.txtFileName.Text.Trim() == string.Empty)
{
lblMsg.Text = "ファイル名を入力してください。";
this.lblMsg.BackColor = System.Drawing.Color.Pink;
return;
}
else
{
lblMsg.Text = "";
}

//
DownloadFileName = this.txtFileName.Text.Trim();

//TODO::CSVファイルのディレクトリ、ファイルの存在チェック
if (!Directory.Exists(this.DownloadWritePath))
{
lblMsg.Text = "ディレクトリが存在しません。";
this.lblMsg.BackColor = System.Drawing.Color.Pink;
return;

}
else
{
lblMsg.Text = "";
}

if (!File.Exists(this.DownloadWritePath + this.DownloadFileName))
{
// lblMsg.Text = "ファイル存在しません。";
using (File.Create(this.DownloadWritePath + this.DownloadFileName)) { };

// return;

}

//SELECT
//Key項目で結合
string qYearRep = Utility.HeiseiToChristianEra(this.txtYearRep.Text);
string qMonthRep = this.txtMonthRep.Text;

//ファイルを作る
string Sql = getSql();
if (!this.writeData(Sql, qYearRep, qMonthRep))
{
this.lblMsg.Text = "ダウンロードデータがありませんでした。";
this.lblMsg.BackColor = System.Drawing.Color.Pink;
};

//クライアントへ返す
this.outData();

}
catch {
}
}

protected void btnlinkMenu_Click(object sender, EventArgs e)
{
try
{
//自販連
Response.Redirect(URL.MENU_JADA);
}
catch
{

}
}

protected void btnLogOut_Click(object sender, EventArgs e)
{
try
{
this.Session.Abandon();
Response.Redirect(URL.LOGIN_DEALER);

}
catch
{

}
}
#endregion
}
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Microsoft.VisualBasic;
using System.Text.RegularExpressions;

namespace Jisseki_Report_Ibaraki.Tools
{
public class Utility
{

/// <summary>
/// Covert JapaneseYear to ChristianYear
/// </summary>
/// <param name="strYY">JapaneseEra(Wareki)</param>
/// <returns>ChristianEra</returns>
public static string HeiseiToChristianEra(String strYY) {
int iYY;
if (strYY == string.Empty)
{
iYY = 0;
}
else
{
iYY = int.Parse(strYY);
}
return (iYY + 1988).ToString();

}
/// <summary>
/// Get JapaneseEraLetter
/// </summary>
/// <param name="iEra">0to4</param>
/// <returns>JapaneseEraLetter</returns>
public static string getJapaneseEra(int iEra){
string strEra=string.Empty;
switch (iEra)
{
case 4://平成
strEra = "平成";
break;

case 3://昭和
strEra = "昭和";
break;

case 2://大正
strEra = "大正";
break;

case 1://明治
strEra = "明治";
break;

}

return strEra;
}

/// <summary>
/// convert 0 to space
/// </summary>
/// <param name="param"></param>
/// <returns></returns>
public static string zeroToSpace(string param){
if (param == "0")
{
return string.Empty;
}
else
{
return param;
}
}

/// <summary>
/// 全角→半角
/// </summary>
/// <param name="_Zenkaku"></param>
public static string ToHankaku(string _Zenkaku) {
return Strings.StrConv(_Zenkaku, VbStrConv.Narrow);
}

public static bool Number3IsValid(string chkValue) {
Regex reg = new Regex("[0-9]{1,3}");
if (reg.IsMatch(chkValue))
{
return true;
}
else
{
return false;
}
}

public static bool IsNotNumber(string chkValue)
{
Regex reg = new Regex(@"\D");//数字以外はだめ
if (reg.IsMatch(chkValue))
{
return true;
}
else
{
return false;
}
}

public static string covertDigit2(int val)
{

if (val == 1 || val == 2 || val == 3 ||
val == 4 || val == 5 || val == 6 ||
val == 7 || val == 9 || val == 9
){

return ("0" + val.ToString()).ToString();
}
return val.ToString();
}

}
}

登録: 投稿 (Atom)