okamura – ページ 48 – テックメモ

2017-01-17

出荷前工程表

ある帳票から工程管理用の帳票を作ろうとしていた模様。
昔のでハッキリ思い出せない。

Dim ymd As Variant
Dim dst As Integer
Dim comma_count As Integer

Sub auto_open()

On Error Resume Next

Application.CommandBars("cell").Controls("出荷前工程表作成(工程表で実行)").Delete
Application.CommandBars("cell").Controls("出荷前工程表DATA_SAVE").Delete
Application.CommandBars("cell").Controls("出荷前工程表DATA_LOAD").Delete

With Application.CommandBars("cell").Controls.Add
.OnAction = "出荷前工程表"
.Caption = "出荷前工程表作成(工程表で実行)"
End With

With Application.CommandBars("cell").Controls.Add
.OnAction = "save_data"
.Caption = "出荷前工程表DATA_SAVE"
End With

With Application.CommandBars("cell").Controls.Add
.OnAction = "load_data"
.Caption = "出荷前工程表DATA_LOAD"
End With

End Sub

Sub auto_close()

On Error Resume Next

Application.CommandBars("cell").Controls("出荷前工程表作成(工程表で実行)").Delete
Application.CommandBars("cell").Controls("出荷前工程表DATA_SAVE").Delete
Application.CommandBars("cell").Controls("出荷前工程表DATA_LOAD").Delete

End Sub

Sub 出荷前工程表()

ymd = ""
dst = 2

For Each ws In Sheets

    If ws.Name = "出荷前工程表" Then
    
        If vbYes = MsgBox("出荷前工程表シートが存在します。削除しますか？", vbYesNo) Then
        
            Worksheets("出荷前工程表").Delete
        
        Else
        
            Exit Sub
        
        End If
    
    End If

Next ws

On Error Resume Next

ymd = Mid(ActiveWorkbook.Name, 1, 4) & "/" & Replace(Sheets(1).Cells(2, 8).Value, "月", "") & "/" & "21"

On Error GoTo 0

If False = IsDate(ymd) Then

  MsgBox "日付が正しく取得できません。" & Chr(13) & "工程表を前面にしていないとエラーになります。"
  Exit Sub
  
End If
    
For Each ws In Sheets

  tmp = Mid(ActiveWorkbook.Name, 1, 4) & "/" & Replace(ws.Cells(2, 8).Value, "月", "") & "/" & "21"
  
  If ymd <> tmp Then
  
    MsgBox "日付が正しく取得できません。" & Chr(13) & "全てのシートが同じ日付でないとエラーになります。"
    Exit Sub
  
  End If

Next ws

sc = ActiveWorkbook.Sheets.Count
ReDim buff(sc, 2) As String

For i = 1 To sc
    
    buff(i, 1) = Sheets(i).Name
    buff(i, 2) = form_count(buff(i, 1))
    
    If buff(i, 2) = -1 Then
    
      MsgBox buff(i, 1) & "シートは工程表のフォーマットが正しくありません。"
      Exit Sub
    
    End If
    
Next i

Sheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = "出荷前工程表"

Sheets("出荷前工程表").Cells(1, 1).Value = "班名"
Sheets("出荷前工程表").Cells(1, 2).Value = "型式"
Sheets("出荷前工程表").Cells(1, 3).Value = "工番"
Sheets("出荷前工程表").Cells(1, 4).Value = "数量"
Sheets("出荷前工程表").Cells(1, 5).Value = "納期"
Sheets("出荷前工程表").Cells(1, 6).Value = "組完"

Sheets("出荷前工程表").Rows(1).HorizontalAlignment = xlHAlignCenter

For i = 1 To sc

    Call main_proc(buff(i, 1), buff(i, 2))

Next i

tmp = ymd
i = 7

Do While tmp <> DateAdd("m", 1, ymd)

  Sheets("出荷前工程表").Cells(1, i).Value = Year(tmp) & "/" & Month(tmp) & "/" & Day(tmp)
  Sheets("出荷前工程表").Cells(1, i).NumberFormatLocal = "m/d"
  
  tmp = DateAdd("d", 1, tmp)
  i = i + 1
  
Loop

Sheets("出荷前工程表").Columns.AutoFit

yn = MsgBox("データを復元しますか？", vbYesNo)
If yn = vbYes Then Call load_data

End Sub

Function form_count(ByVal sn As String) As Integer

For i = Sheets(sn).UsedRange.Rows.Count To 1 Step -1

    If Sheets(sn).Range("A" & i & "").Value <> "" Then Exit For

Next i

Select Case i

    Case 24
        tmp = 1
    
    Case 49
        tmp = 2
    
    Case 74
        tmp = 3
    
    Case 99
        tmp = 4
    
    Case 124
        tmp = 5

    Case Else
        tmp = -1
        
End Select

form_count = tmp
    
End Function

Sub main_proc(ByVal sn As String, ByVal fc As Integer)

For r = 5 To fc * 25 - 1

  r_chk = False

  For c = 100 To 8 Step -1
  
    If Sheets(sn).Cells(r, c).Interior.Color = RGB(0, 0, 0) Then
    
      r_chk = True
      Exit For
    
    End If
  
  Next c

  If r_chk Then
    
    Sheets("出荷前工程表").Cells(dst, 1).Value = sn
    Sheets("出荷前工程表").Cells(dst, 2).Value = rep(Sheets(sn).Cells(r, 2).Value)
    Sheets("出荷前工程表").Cells(dst, 3).Value = rep(Sheets(sn).Cells(r, 3).Value)
    Sheets("出荷前工程表").Cells(dst, 4).Value = Sheets(sn).Cells(r, 5).Value
    Sheets("出荷前工程表").Cells(dst, 5).Value = Sheets(sn).Cells(r, 6).Value
    If Sheets(sn).Cells(r, 7).Value <> "" Then Sheets("出荷前工程表").Cells(dst, 5).Value = Sheets(sn).Cells(r, 7).Value
    
    If c >= 41 Then
      tmp = DateAdd("m", 1, ymd)
    Else
      tmp = ymd
    End If
    
    Sheets("出荷前工程表").Cells(dst, 6).Value = Year(tmp) & "/" & Month(tmp) & "/" & Sheets(sn).Cells(3, c).MergeArea(1, 1).Value
    
    dst = dst + 1
  
  End If

  Select Case r
  
    Case 99, 74, 49, 24
    r = r + 5
    
  End Select

Next r

End Sub

Function rep(tmp As String)
tmp = Replace(tmp, vbLf, " ")
tmp = Replace(tmp, "  ", " ")
tmp = Replace(tmp, "  ", " ")
tmp = Replace(tmp, ",", "，")
rep = tmp
End Function

Sub save_data()

If ActiveSheet.Name <> "出荷前工程表" Then

  MsgBox "出荷前工程表シートで実行してください。"
  Exit Sub
  
End If

yn = MsgBox("サーバー上のデータが上書きされます。実行しますか？", vbYesNo)
If yn = vbNo Then Exit Sub

If chk_col = -1 Then

  MsgBox "最終列が正しく取得できませんでした。"
  Exit Sub
  
End If

If chk_btm() = -1 Then

  MsgBox "最終行が正しく取得できませんでした。"
  Exit Sub

End If

Call u_key_chk

Set cn = CreateObject("adodb.connection")
cn.Open "provider=microsoft.jet.oledb.4.0; data source=" & ThisWorkbook.Path & "\syukka_kotei.mdb;"

On Error GoTo ex

For r = 2 To chk_btm()

  u_key = Sheets("出荷前工程表").Cells(r, 3).Text & " " & Sheets("出荷前工程表").Cells(r, 4).Text & " " & Format(Sheets("出荷前工程表").Cells(1, 7).Text, "yyyymmdd")
  u_day = Format(Sheets("出荷前工程表").Cells(1, 7).Text, "yyyy/mm/dd")

  For c = 7 To chk_col
    
    If c = 7 Then
      u_data = ""
      u_data = rep(Sheets("出荷前工程表").Cells(r, c).Text)
    Else
      u_data = u_data & "," & rep(Sheets("出荷前工程表").Cells(r, c).Text)
    End If
    
  Next c
  
  cn.Execute "delete * from tbl where u_key = '" & u_key & "' and u_day = #" & u_day & "#"
  cn.Execute "insert into tbl(u_key,u_day,u_data) values('" & u_key & "',#" & u_day & "#,'" & u_data & "')"

Next r

If cn.State = 1 Then cn.Close
Set cn = Nothing

MsgBox "done"

Exit Sub

ex:

  MsgBox "正しく保存できませんでした。"
  
  If cn.State = 1 Then cn.Close
  Set cn = Nothing

End Sub

Function chk_col()

c = Sheets("出荷前工程表").Cells(1, 1).CurrentRegion.Columns.Count
u = Sheets("出荷前工程表").UsedRange.Columns.Count

If c <> u Then

  chk_col = -1
  Exit Function

End If

chk_col = c

End Function

Function chk_btm()

c = Sheets("出荷前工程表").Cells(1, 1).CurrentRegion.Rows.Count
u = Sheets("出荷前工程表").UsedRange.Rows.Count

If c <> u Then

  chk_btm = -1
  Exit Function

End If

chk_btm = c

End Function

Sub load_data()

If ActiveSheet.Name <> "出荷前工程表" Then

  MsgBox "出荷前工程表シートで実行してください。"
  Exit Sub
  
End If

yn = MsgBox("シート上のデータが上書きされます。実行しますか？", vbYesNo)
If yn = vbNo Then Exit Sub

If chk_col = -1 Then

  MsgBox "最終列が正しく取得できませんでした。"
  Exit Sub
  
End If

If chk_btm() = -1 Then

  MsgBox "最終行が正しく取得できませんでした。"
  Exit Sub

End If

Call u_key_chk

Set cn = CreateObject("adodb.connection")
Set rs = CreateObject("adodb.recordset")

On Error GoTo ex

For r = 2 To chk_btm()

  u_key = Sheets("出荷前工程表").Cells(r, 3).Text & " " & Sheets("出荷前工程表").Cells(r, 4).Text & " " & Format(Sheets("出荷前工程表").Cells(1, 7).Text, "yyyymmdd")

  cn.Open "provider=microsoft.jet.oledb.4.0; data source=" & ThisWorkbook.Path & "\syukka_kotei.mdb;"
  rs.Open "select u_data from tbl where u_key = '" & u_key & "'", cn
 
  buff = ""
  
  Do Until rs.EOF
    
    If buff <> "" Then
    
      MsgBox "データが重複している可能性があります。注意してください。"
      buff = rs!u_data
    
    Else
    
      buff = rs!u_data
    
    End If
    
    rs.MoveNext
    
  Loop
  
  If buff <> "" Then
  
    comma_count = 0
  
    For c = 7 To chk_col

      Sheets("出荷前工程表").Cells(r, c).Value = split_comma(buff)
    
    Next c
  
  End If
  
  If rs.State = 1 Then rs.Close
  If cn.State = 1 Then cn.Close

Next r

If rs.State = 1 Then rs.Close
Set rs = Nothing

If cn.State = 1 Then cn.Close
Set cn = Nothing
  
MsgBox "done"

Exit Sub

ex:

  MsgBox "正しく取得できませんでした。"
  
  If rs.State = 1 Then rs.Close
  Set rs = Nothing

  If cn.State = 1 Then cn.Close
  Set cn = Nothing

End Sub

Function split_comma(tmp)

buff = ""
comma_count_local = 0

For i = 1 To Len(tmp)

  If Mid(tmp, i, 1) <> "," Then
  
    buff = buff + Mid(tmp, i, 1)
  
  Else

    comma_count_local = comma_count_local + 1
    
    If comma_count >= comma_count_local Then
    
      buff = ""
    
    Else
    
      comma_count = comma_count_local
      Exit For
    
    End If
  
  End If

Next i

split_comma = buff

End Function

Sub u_key_chk()

Sheets("出荷前工程表").UsedRange.Interior.Color = xlNone

For r = 2 To chk_btm()
  
  from_u_key = Sheets("出荷前工程表").Cells(r, 3).Text & " " & Sheets("出荷前工程表").Cells(r, 4).Text & " " & Format(Sheets("出荷前工程表").Cells(1, 7).Text, "yyyymmdd")
  
  For rr = (r + 1) To chk_btm()
  
    to_u_key = Sheets("出荷前工程表").Cells(rr, 3).Text & " " & Sheets("出荷前工程表").Cells(rr, 4).Text & " " & Format(Sheets("出荷前工程表").Cells(1, 7).Text, "yyyymmdd")
    
    If from_u_key = to_u_key Then

      Sheets("出荷前工程表").Cells(r, 1).Interior.Color = RGB(255, 0, 0)
      Sheets("出荷前工程表").Cells(rr, 1).Interior.Color = RGB(255, 0, 0)
    
    End If

  Next rr

Next r

End Sub

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

361

362

363

364

365

366

367

368

369

370

371

372

373

374

375

376

377

378

379

380

381

382

383

384

385

386

387

388

389

390

391

392

393

394

395

396

397

398

399

400

401

402

403

404

405

406

407

408

409

410

411

412

413

414

415

416

417

418

419

420

421

422

423

424

425

426

427

428

429

430

431

432

433

434

435

436

437

438

439

440

441

442

443

444

445

446

447

448

449

450

451

452

453

454

455

456

457

458

459

460

461

462

463

464

465

466

467

468

469

470

471

472

473

474

475

476

477

478

479

480

481

482

483

484

485

486

487

488

489

490

491

492

Dim ymd As Variant

Dim dst As Integer

Dim comma_count As Integer

Sub auto_open()

On Error Resume Next

Application.CommandBars("cell").Controls("出荷前工程表作成(工程表で実行)").Delete

Application.CommandBars("cell").Controls("出荷前工程表DATA_SAVE").Delete

Application.CommandBars("cell").Controls("出荷前工程表DATA_LOAD").Delete

With Application.CommandBars("cell").Controls.Add

.OnAction = "出荷前工程表"

.Caption = "出荷前工程表作成(工程表で実行)"

End With

With Application.CommandBars("cell").Controls.Add

.OnAction = "save_data"

.Caption = "出荷前工程表DATA_SAVE"

End With

With Application.CommandBars("cell").Controls.Add

.OnAction = "load_data"

.Caption = "出荷前工程表DATA_LOAD"

End With

End Sub

Sub auto_close()

On Error Resume Next

Application.CommandBars("cell").Controls("出荷前工程表作成(工程表で実行)").Delete

Application.CommandBars("cell").Controls("出荷前工程表DATA_SAVE").Delete

Application.CommandBars("cell").Controls("出荷前工程表DATA_LOAD").Delete

End Sub

Sub 出荷前工程表()

ymd = ""

dst = 2

For Each ws In Sheets

If ws.Name = "出荷前工程表" Then

If vbYes = MsgBox("出荷前工程表シートが存在します。削除しますか？", vbYesNo) Then

Worksheets("出荷前工程表").Delete

Else

Exit Sub

End If

Next ws

On Error Resume Next

ymd = Mid(ActiveWorkbook.Name, 1, 4) & "/" & Replace(Sheets(1).Cells(2, 8).Value, "月", "") & "/" & "21"

On Error GoTo 0

If False = IsDate(ymd) Then

MsgBox "日付が正しく取得できません。" & Chr(13) & "工程表を前面にしていないとエラーになります。"

Exit Sub

End If

For Each ws In Sheets

tmp = Mid(ActiveWorkbook.Name, 1, 4) & "/" & Replace(ws.Cells(2, 8).Value, "月", "") & "/" & "21"

If ymd <> tmp Then

MsgBox "日付が正しく取得できません。" & Chr(13) & "全てのシートが同じ日付でないとエラーになります。"

Exit Sub

End If

Next ws

sc = ActiveWorkbook.Sheets.Count

ReDim buff(sc, 2) As String

For i = 1 To sc

buff(i, 1) = Sheets(i).Name

buff(i, 2) = form_count(buff(i, 1))

If buff(i, 2) = -1 Then

MsgBox buff(i, 1) & "シートは工程表のフォーマットが正しくありません。"

Exit Sub

End If

Next i

Sheets.Add after:=Sheets(Sheets.Count)

ActiveSheet.Name = "出荷前工程表"

Sheets("出荷前工程表").Cells(1, 1).Value = "班名"

Sheets("出荷前工程表").Cells(1, 2).Value = "型式"

Sheets("出荷前工程表").Cells(1, 3).Value = "工番"

Sheets("出荷前工程表").Cells(1, 4).Value = "数量"

Sheets("出荷前工程表").Cells(1, 5).Value = "納期"

Sheets("出荷前工程表").Cells(1, 6).Value = "組完"

Sheets("出荷前工程表").Rows(1).HorizontalAlignment = xlHAlignCenter

For i = 1 To sc

Call main_proc(buff(i, 1), buff(i, 2))

Next i

tmp = ymd

i = 7

Do While tmp <> DateAdd("m", 1, ymd)

Sheets("出荷前工程表").Cells(1, i).Value = Year(tmp) & "/" & Month(tmp) & "/" & Day(tmp)

Sheets("出荷前工程表").Cells(1, i).NumberFormatLocal = "m/d"

tmp = DateAdd("d", 1, tmp)

i = i + 1

Loop

Sheets("出荷前工程表").Columns.AutoFit

yn = MsgBox("データを復元しますか？", vbYesNo)

If yn = vbYes Then Call load_data

End Sub

Function form_count(ByVal sn As String) As Integer

For i = Sheets(sn).UsedRange.Rows.Count To 1 Step -1

If Sheets(sn).Range("A" & i & "").Value <> "" Then Exit For

Next i

Select Case i

Case 24

tmp = 1

Case 49

tmp = 2

Case 74

tmp = 3

Case 99

tmp = 4

Case 124

tmp = 5

Case Else

tmp = -1

End Select

form_count = tmp

End Function

Sub main_proc(ByVal sn As String, ByVal fc As Integer)

For r = 5 To fc * 25 - 1

r_chk = False

For c = 100 To 8 Step -1

If Sheets(sn).Cells(r, c).Interior.Color = RGB(0, 0, 0) Then

r_chk = True

Exit For

End If

Next c

If r_chk Then

Sheets("出荷前工程表").Cells(dst, 1).Value = sn

Sheets("出荷前工程表").Cells(dst, 2).Value = rep(Sheets(sn).Cells(r, 2).Value)

Sheets("出荷前工程表").Cells(dst, 3).Value = rep(Sheets(sn).Cells(r, 3).Value)

Sheets("出荷前工程表").Cells(dst, 4).Value = Sheets(sn).Cells(r, 5).Value

Sheets("出荷前工程表").Cells(dst, 5).Value = Sheets(sn).Cells(r, 6).Value

If Sheets(sn).Cells(r, 7).Value <> "" Then Sheets("出荷前工程表").Cells(dst, 5).Value = Sheets(sn).Cells(r, 7).Value

If c >= 41 Then

tmp = DateAdd("m", 1, ymd)

Else

tmp = ymd

End If

Sheets("出荷前工程表").Cells(dst, 6).Value = Year(tmp) & "/" & Month(tmp) & "/" & Sheets(sn).Cells(3, c).MergeArea(1, 1).Value

dst = dst + 1

End If

Select Case r

Case 99, 74, 49, 24

r = r + 5

End Select

Next r

End Sub

Function rep(tmp As String)

tmp = Replace(tmp, vbLf, " ")

tmp = Replace(tmp, " ", " ")

tmp = Replace(tmp, ",", "，")

rep = tmp

End Function

Sub save_data()

If ActiveSheet.Name <> "出荷前工程表" Then

MsgBox "出荷前工程表シートで実行してください。"

Exit Sub

End If

yn = MsgBox("サーバー上のデータが上書きされます。実行しますか？", vbYesNo)

If yn = vbNo Then Exit Sub

If chk_col = -1 Then

MsgBox "最終列が正しく取得できませんでした。"

Exit Sub

End If

If chk_btm() = -1 Then

MsgBox "最終行が正しく取得できませんでした。"

Exit Sub

End If

Call u_key_chk

Set cn = CreateObject("adodb.connection")

cn.Open "provider=microsoft.jet.oledb.4.0; data source=" & ThisWorkbook.Path & "\syukka_kotei.mdb;"

On Error GoTo ex

For r = 2 To chk_btm()

u_key = Sheets("出荷前工程表").Cells(r, 3).Text & " " & Sheets("出荷前工程表").Cells(r, 4).Text & " " & Format(Sheets("出荷前工程表").Cells(1, 7).Text, "yyyymmdd")

u_day = Format(Sheets("出荷前工程表").Cells(1, 7).Text, "yyyy/mm/dd")

For c = 7 To chk_col

If c = 7 Then

u_data = ""

u_data = rep(Sheets("出荷前工程表").Cells(r, c).Text)

Else

u_data = u_data & "," & rep(Sheets("出荷前工程表").Cells(r, c).Text)

End If

Next c

cn.Execute "delete * from tbl where u_key = '" & u_key & "' and u_day = #" & u_day & "#"

cn.Execute "insert into tbl(u_key,u_day,u_data) values('" & u_key & "',#" & u_day & "#,'" & u_data & "')"

Next r

If cn.State = 1 Then cn.Close

Set cn = Nothing

MsgBox "done"

Exit Sub

ex:

MsgBox "正しく保存できませんでした。"

If cn.State = 1 Then cn.Close

Set cn = Nothing

End Sub

Function chk_col()

c = Sheets("出荷前工程表").Cells(1, 1).CurrentRegion.Columns.Count

u = Sheets("出荷前工程表").UsedRange.Columns.Count

If c <> u Then

chk_col = -1

Exit Function

End If

chk_col = c

End Function

Function chk_btm()

c = Sheets("出荷前工程表").Cells(1, 1).CurrentRegion.Rows.Count

u = Sheets("出荷前工程表").UsedRange.Rows.Count

If c <> u Then

chk_btm = -1

Exit Function

End If

chk_btm = c

End Function

Sub load_data()

If ActiveSheet.Name <> "出荷前工程表" Then

MsgBox "出荷前工程表シートで実行してください。"

Exit Sub

End If

yn = MsgBox("シート上のデータが上書きされます。実行しますか？", vbYesNo)

If yn = vbNo Then Exit Sub

If chk_col = -1 Then

MsgBox "最終列が正しく取得できませんでした。"

Exit Sub

End If

If chk_btm() = -1 Then

MsgBox "最終行が正しく取得できませんでした。"

Exit Sub

End If

Call u_key_chk

Set cn = CreateObject("adodb.connection")

Set rs = CreateObject("adodb.recordset")

On Error GoTo ex

For r = 2 To chk_btm()

u_key = Sheets("出荷前工程表").Cells(r, 3).Text & " " & Sheets("出荷前工程表").Cells(r, 4).Text & " " & Format(Sheets("出荷前工程表").Cells(1, 7).Text, "yyyymmdd")

cn.Open "provider=microsoft.jet.oledb.4.0; data source=" & ThisWorkbook.Path & "\syukka_kotei.mdb;"

rs.Open "select u_data from tbl where u_key = '" & u_key & "'", cn

buff = ""

Do Until rs.EOF

If buff <> "" Then

MsgBox "データが重複している可能性があります。注意してください。"

buff = rs!u_data

Else

buff = rs!u_data

End If

rs.MoveNext

Loop

If buff <> "" Then

comma_count = 0

For c = 7 To chk_col

Sheets("出荷前工程表").Cells(r, c).Value = split_comma(buff)

Next c

End If

If rs.State = 1 Then rs.Close

If cn.State = 1 Then cn.Close

Next r

If rs.State = 1 Then rs.Close

Set rs = Nothing

If cn.State = 1 Then cn.Close

Set cn = Nothing

MsgBox "done"

Exit Sub

ex:

MsgBox "正しく取得できませんでした。"

If rs.State = 1 Then rs.Close

Set rs = Nothing

If cn.State = 1 Then cn.Close

Set cn = Nothing

End Sub

Function split_comma(tmp)

buff = ""

comma_count_local = 0

For i = 1 To Len(tmp)

If Mid(tmp, i, 1) <> "," Then

buff = buff + Mid(tmp, i, 1)

Else

comma_count_local = comma_count_local + 1

If comma_count >= comma_count_local Then

buff = ""

Else

comma_count = comma_count_local

Exit For

End If

Next i

split_comma = buff

End Function

Sub u_key_chk()

Sheets("出荷前工程表").UsedRange.Interior.Color = xlNone

For r = 2 To chk_btm()

from_u_key = Sheets("出荷前工程表").Cells(r, 3).Text & " " & Sheets("出荷前工程表").Cells(r, 4).Text & " " & Format(Sheets("出荷前工程表").Cells(1, 7).Text, "yyyymmdd")

For rr = (r + 1) To chk_btm()

to_u_key = Sheets("出荷前工程表").Cells(rr, 3).Text & " " & Sheets("出荷前工程表").Cells(rr, 4).Text & " " & Format(Sheets("出荷前工程表").Cells(1, 7).Text, "yyyymmdd")

If from_u_key = to_u_key Then

Sheets("出荷前工程表").Cells(r, 1).Interior.Color = RGB(255, 0, 0)

Sheets("出荷前工程表").Cells(rr, 1).Interior.Color = RGB(255, 0, 0)

End If

Next rr

Next r

End Sub

2017-01-172017-01-17

C# VBA タイムカードアプリ

以前作ったタイムカードアプリ。氏名などはフォームに直接。

Form1.cs

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Collections;
using System.Data.OleDb;
using System.Drawing.Text;

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
            this.WindowState = FormWindowState.Maximized;
            this.label2.Font = new System.Drawing.Font("7barP",110);
            this.label1.Text = DateTime.Now.ToString("yyyy/MM/dd dddd");
            this.label2.Text = DateTime.Now.ToString("HH:mm:ss");
            attach_function();
            this.timer1.Start();
        }
        private void timer1_Tick(object sender, EventArgs e)
        {
            this.label1.Text = DateTime.Now.ToString("yyyy/MM/dd dddd");
            this.label2.Text = DateTime.Now.ToString("HH:mm:ss");
        }
        private Control[] get_controls(Control control_top)
        {
            ArrayList tmp = new ArrayList();
            foreach(Control ctrl in control_top.Controls)
            {
                tmp.Add(ctrl);
                if (ctrl is TabControl)
                {
                    foreach (Control tb in ctrl.Controls)
                    {
                        foreach (Control btn in tb.Controls) tmp.Add(btn);
                    } 
                }
            }
            return (Control[])tmp.ToArray(typeof(Control));
        }
        public void attach_function()
        {
            Control[] controls = get_controls(this);
            foreach (Control ctrl in controls)
            {
                if (ctrl is Button) ((Button)ctrl).Click += new EventHandler(click_button);
                if (ctrl is RadioButton) ((RadioButton)ctrl).CheckedChanged += new EventHandler(clear_labels);
            }
        }
        public void click_button(object sender, EventArgs e)
        {
            clear_labels();
            if (((Button)sender).Text == "リセット") return;
            if (get_check_state() != null)
            {
                connction_database(get_check_state().Text, ((Button)sender).Text);
            }
            else
            {
                connction_database("", ((Button)sender).Text);
            }
        }
        public void clear_labels(object sender = null, EventArgs e = null)
        {
            this.label3.Text = "00/00 00:00:00";
            this.label4.Text = "00/00 00:00:00";
            this.label5.Text = "00/00 00:00:00";
            this.label6.Text = "00/00 00:00:00";
        }
        public void clear_check_state()
        {
            this.radioButton1.Checked = false;
            this.radioButton2.Checked = false;
            this.radioButton3.Checked = false;
            this.radioButton4.Checked = false;
            this.checkBox1.Checked = false;
        }
        public void save_log(string check_text, string user)
        {
            using (System.IO.StreamWriter s = new System.IO.StreamWriter(System.IO.Path.GetDirectoryName(Application.ExecutablePath) + @"\log.txt", true, System.Text.Encoding.GetEncoding("shift_jis")))
            {
                s.Write(user + "," + check_text + "," + DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss") + "\r\n");
            }
        }
        public RadioButton get_check_state()
        {
            Control[] controls = get_controls(this);
            foreach (Control ctrl in controls)
            {
                if (ctrl is RadioButton)
                {
                    if (((RadioButton)ctrl).Checked) return (RadioButton)ctrl;
                }
            }
            return null;
        }
        public void connction_database(String checked_text, String button_text)
        {
            String start_day = DateTime.Now.ToString("yyyy/MM/dd 00:00:01");
            if (this.checkBox1.Checked == true) start_day = DateTime.Now.AddDays(-1).ToString("yyyy/MM/dd 00:00:01");
            using (OleDbConnection cn = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + System.IO.Path.GetDirectoryName(Application.ExecutablePath) + @"\Tc_Db.mdb;"))
            {
                OleDbTransaction transaction = null;
                try
                {
                    cn.Open();
                    if (checked_text != "")
                    {
                        OleDbCommand cmd_main = new OleDbCommand("SELECT 出社,退社,外出,帰社 FROM Tc_Tbl WHERE 日付 = #" + start_day + "# AND 氏名 = '" + button_text + "'", cn);
                        using (OleDbDataReader dr_main = cmd_main.ExecuteReader())
                        {
                            if (dr_main.Read())
                            {
                                if (dr_main[checked_text].ToString() == "")
                                {
                                    transaction = cn.BeginTransaction(IsolationLevel.ReadCommitted);
                                    OleDbCommand cmd = new OleDbCommand("UPDATE Tc_Tbl SET " + checked_text + " = '" + DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss") + "' WHERE 日付 = #" + start_day + "# AND 氏名 = '" + button_text + "'", cn);
                                    cmd.Transaction = transaction;
                                    cmd.ExecuteNonQuery();
                                    transaction.Commit();
                                    save_log(checked_text, button_text);
                                    //MessageBox.Show(checked_text + "を登録しました。");
                                }
                                else if (dr_main[checked_text].ToString() != "")
                                {
                                    MessageBox.Show("既に" + checked_text + "は登録されています。");
                                }
                                if (dr_main.Read()) MessageBox.Show(DateTime.Now.ToString("yyyy/MM/dd") + "が複数行あります。編集より削除してください。");
                            }
                            else
                            {
                                transaction = cn.BeginTransaction(IsolationLevel.ReadCommitted);
                                OleDbCommand cmd = new OleDbCommand("INSERT INTO Tc_Tbl (日付," + checked_text + ",氏名) VALUES ('" + start_day + "', '" + DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss") + "', '" + button_text + "')", cn);
                                cmd.Transaction = transaction;
                                cmd.ExecuteNonQuery();
                                transaction.Commit();
                                save_log(checked_text, button_text);
                                //MessageBox.Show(checked_text + "を登録しました。");
                            }
                        }
                        clear_check_state();
                    }
                    OleDbCommand cmd_to_labels = new OleDbCommand("SELECT 出社,退社,外出,帰社 FROM Tc_Tbl WHERE 日付 = #" + start_day + "# AND 氏名 = '" + button_text + "'", cn);
                    using(OleDbDataReader dr_to_labels = cmd_to_labels.ExecuteReader())
                    {
                        if (dr_to_labels.Read())
                        {
                            if (dr_to_labels["出社"].ToString() != "") label3.Text = DateTime.Parse(dr_to_labels["出社"].ToString()).ToString("MM/dd HH:mm:ss");
                            if (dr_to_labels["退社"].ToString() != "") label4.Text = DateTime.Parse(dr_to_labels["退社"].ToString()).ToString("MM/dd HH:mm:ss");
                            if (dr_to_labels["外出"].ToString() != "") label5.Text = DateTime.Parse(dr_to_labels["外出"].ToString()).ToString("MM/dd HH:mm:ss");
                            if (dr_to_labels["帰社"].ToString() != "") label6.Text = DateTime.Parse(dr_to_labels["帰社"].ToString()).ToString("MM/dd HH:mm:ss");
                        }
                        if (dr_to_labels.Read()) MessageBox.Show(DateTime.Now.ToString("yyyy/MM/dd") + "が複数行あります。編集より削除してください。");
                    }
                }
                catch
                {
                    MessageBox.Show("データベースに接続できませんでした。管理者に連絡してください。");
                    try
                    {
                        transaction.Rollback();
                        return;
                    }
                    catch
                    {
                        return;
                    }
                }
                finally
                {
                    cn.Close();
                }
            }
        }
        private void button3_Click(object sender, EventArgs e)
        {
            clear_labels();
            clear_check_state();
        }
        private void 編集ToolStripMenuItem1_Click(object sender, EventArgs e)
        {
            Form2 f = new Form2(get_controls(this));
            f.ShowDialog();
        }
        private void tabControl1_SelectedIndexChanged(object sender, EventArgs e)
        {
            //button3.PerformClick();
        }
    }
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Windows.Forms;

using System.Collections;

using System.Data.OleDb;

using System.Drawing.Text;

namespace WindowsFormsApplication1

{

public partial class Form1 : Form

{

public Form1()

{

InitializeComponent();

this.WindowState = FormWindowState.Maximized;

this.label2.Font = new System.Drawing.Font("7barP",110);

this.label1.Text = DateTime.Now.ToString("yyyy/MM/dd dddd");

this.label2.Text = DateTime.Now.ToString("HH:mm:ss");

attach_function();

this.timer1.Start();

}

private void timer1_Tick(object sender, EventArgs e)

{

this.label1.Text = DateTime.Now.ToString("yyyy/MM/dd dddd");

this.label2.Text = DateTime.Now.ToString("HH:mm:ss");

}

private Control[] get_controls(Control control_top)

{

ArrayList tmp = new ArrayList();

foreach(Control ctrl in control_top.Controls)

{

tmp.Add(ctrl);

if (ctrl is TabControl)

{

foreach (Control tb in ctrl.Controls)

{

foreach (Control btn in tb.Controls) tmp.Add(btn);

}

return (Control[])tmp.ToArray(typeof(Control));

}

public void attach_function()

{

Control[] controls = get_controls(this);

foreach (Control ctrl in controls)

{

if (ctrl is Button) ((Button)ctrl).Click += new EventHandler(click_button);

if (ctrl is RadioButton) ((RadioButton)ctrl).CheckedChanged += new EventHandler(clear_labels);

}

public void click_button(object sender, EventArgs e)

{

clear_labels();

if (((Button)sender).Text == "リセット") return;

if (get_check_state() != null)

{

connction_database(get_check_state().Text, ((Button)sender).Text);

}

else

{

connction_database("", ((Button)sender).Text);

}

public void clear_labels(object sender = null, EventArgs e = null)

{

this.label3.Text = "00/00 00:00:00";

this.label4.Text = "00/00 00:00:00";

this.label5.Text = "00/00 00:00:00";

this.label6.Text = "00/00 00:00:00";

}

public void clear_check_state()

{

this.radioButton1.Checked = false;

this.radioButton2.Checked = false;

this.radioButton3.Checked = false;

this.radioButton4.Checked = false;

this.checkBox1.Checked = false;

}

public void save_log(string check_text, string user)

{

using (System.IO.StreamWriter s = new System.IO.StreamWriter(System.IO.Path.GetDirectoryName(Application.ExecutablePath) + @"\log.txt", true, System.Text.Encoding.GetEncoding("shift_jis")))

{

s.Write(user + "," + check_text + "," + DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss") + "\r\n");

}

public RadioButton get_check_state()

{

Control[] controls = get_controls(this);

foreach (Control ctrl in controls)

{

if (ctrl is RadioButton)

{

if (((RadioButton)ctrl).Checked) return (RadioButton)ctrl;

}

return null;

}

public void connction_database(String checked_text, String button_text)

{

String start_day = DateTime.Now.ToString("yyyy/MM/dd 00:00:01");

if (this.checkBox1.Checked == true) start_day = DateTime.Now.AddDays(-1).ToString("yyyy/MM/dd 00:00:01");

using (OleDbConnection cn = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + System.IO.Path.GetDirectoryName(Application.ExecutablePath) + @"\Tc_Db.mdb;"))

{

OleDbTransaction transaction = null;

try

{

cn.Open();

if (checked_text != "")

{

OleDbCommand cmd_main = new OleDbCommand("SELECT 出社,退社,外出,帰社 FROM Tc_Tbl WHERE 日付 = #" + start_day + "# AND 氏名 = '" + button_text + "'", cn);

using (OleDbDataReader dr_main = cmd_main.ExecuteReader())

{

if (dr_main.Read())

{

if (dr_main[checked_text].ToString() == "")

{

transaction = cn.BeginTransaction(IsolationLevel.ReadCommitted);

OleDbCommand cmd = new OleDbCommand("UPDATE Tc_Tbl SET " + checked_text + " = '" + DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss") + "' WHERE 日付 = #" + start_day + "# AND 氏名 = '" + button_text + "'", cn);

cmd.Transaction = transaction;

cmd.ExecuteNonQuery();

transaction.Commit();

save_log(checked_text, button_text);

//MessageBox.Show(checked_text + "を登録しました。");

}

else if (dr_main[checked_text].ToString() != "")

{

MessageBox.Show("既に" + checked_text + "は登録されています。");

}

if (dr_main.Read()) MessageBox.Show(DateTime.Now.ToString("yyyy/MM/dd") + "が複数行あります。編集より削除してください。");

}

else

{

transaction = cn.BeginTransaction(IsolationLevel.ReadCommitted);

OleDbCommand cmd = new OleDbCommand("INSERT INTO Tc_Tbl (日付," + checked_text + ",氏名) VALUES ('" + start_day + "', '" + DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss") + "', '" + button_text + "')", cn);

cmd.Transaction = transaction;

cmd.ExecuteNonQuery();

transaction.Commit();

save_log(checked_text, button_text);

//MessageBox.Show(checked_text + "を登録しました。");

}

clear_check_state();

}

OleDbCommand cmd_to_labels = new OleDbCommand("SELECT 出社,退社,外出,帰社 FROM Tc_Tbl WHERE 日付 = #" + start_day + "# AND 氏名 = '" + button_text + "'", cn);

using(OleDbDataReader dr_to_labels = cmd_to_labels.ExecuteReader())

{

if (dr_to_labels.Read())

{

if (dr_to_labels["出社"].ToString() != "") label3.Text = DateTime.Parse(dr_to_labels["出社"].ToString()).ToString("MM/dd HH:mm:ss");

if (dr_to_labels["退社"].ToString() != "") label4.Text = DateTime.Parse(dr_to_labels["退社"].ToString()).ToString("MM/dd HH:mm:ss");

if (dr_to_labels["外出"].ToString() != "") label5.Text = DateTime.Parse(dr_to_labels["外出"].ToString()).ToString("MM/dd HH:mm:ss");

if (dr_to_labels["帰社"].ToString() != "") label6.Text = DateTime.Parse(dr_to_labels["帰社"].ToString()).ToString("MM/dd HH:mm:ss");

}

if (dr_to_labels.Read()) MessageBox.Show(DateTime.Now.ToString("yyyy/MM/dd") + "が複数行あります。編集より削除してください。");

}

catch

{

MessageBox.Show("データベースに接続できませんでした。管理者に連絡してください。");

try

{

transaction.Rollback();

return;

}

catch

{

return;

}

finally

{

cn.Close();

}

private void button3_Click(object sender, EventArgs e)

{

clear_labels();

clear_check_state();

}

private void 編集ToolStripMenuItem1_Click(object sender, EventArgs e)

{

Form2 f = new Form2(get_controls(this));

f.ShowDialog();

}

private void tabControl1_SelectedIndexChanged(object sender, EventArgs e)

{

//button3.PerformClick();

}

Form2.cs

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Collections;
using System.Data.OleDb;

namespace WindowsFormsApplication1
{
    public partial class Form2 : Form
    {
        private String database_path = System.IO.Path.GetDirectoryName(Application.ExecutablePath);

        public Form2(Control[] parent_contorls)
        {
            InitializeComponent();
            foreach (Control ctrl in parent_contorls) if (ctrl is Button && ctrl.Text != "リセット") this.comboBox1.Items.Add(ctrl.Text);
            for (int i = -2; i <= 2; i++) this.comboBox2.Items.Add(DateTime.Now.Year + i);
            for (int i = 1; i <= 12; i++) this.comboBox3.Items.Add(i.ToString().PadLeft(2, '0'));
            for (int i = 1; i <= 31; i++) this.comboBox4.Items.Add(i.ToString().PadLeft(2, '0'));
            this.comboBox2.Text = DateTime.Now.ToString("yyyy");
            this.comboBox3.Text = DateTime.Now.ToString("MM");
            this.comboBox4.Text = DateTime.Now.ToString("dd");
            this.WindowState = FormWindowState.Maximized;
            //this.dataGridView1.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
            //this.dataGridView1.ColumnHeadersDefaultCellStyle.WrapMode = DataGridViewTriState.False;
        }
        private void button2_Click(object sender, EventArgs e)
        {
            if (this.textBox1.Text != "1234") return;
            if (this.comboBox1.Text == "")
            {
                MessageBox.Show("名前を選択してください。");
                return;
            }
            DateTime dt;
            string[] check_date = new string[2];
            if (DateTime.TryParse(this.comboBox2.Text + "/" + this.comboBox3.Text + "/" + this.comboBox4.Text, out dt))
            {
                check_date[0] = dt.ToString("yyyy/MM/dd 00:00:01");
                check_date[1] = dt.ToString("yyyy/MM/dd 08:25:00");
            }
            else
            {
                MessageBox.Show("日付が適切ではありません。");
                return;
            }
            using (OleDbConnection cn = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + database_path + @"\Tc_Db.mdb;"))
            {
                OleDbTransaction transaction = null;
                try
                {
                    cn.Open();
                    transaction = cn.BeginTransaction(IsolationLevel.ReadCommitted);
                    OleDbCommand cmd = new OleDbCommand("INSERT INTO Tc_Tbl (氏名,日付) VALUES ('" + this.comboBox1.Text + "','" + check_date[0] + "')", cn);
                    cmd.Transaction = transaction;
                    cmd.ExecuteNonQuery();
                    transaction.Commit();
                }
                catch
                {
                    MessageBox.Show("データベースに接続できませんでした。管理者に連絡してください。");
                    try
                    {
                        transaction.Rollback();
                        return;
                    }
                    catch
                    {
                        return;
                    }
                }
                finally
                {
                    cn.Close();
                }
            }
            button1.PerformClick();
        }
        private void button1_Click(object sender, EventArgs e)
        {
            if(this.comboBox1.Text == "")
            {
                MessageBox.Show("名前を選択してください。");
                return;
            }
            this.dataGridView1.Rows.Clear();
            DateTime tmp;
            DateTime[] chk_date = new DateTime[2];
            if (DateTime.TryParse(this.comboBox2.Text + "/" + this.comboBox3.Text + "/1", out tmp))
            {
                chk_date[0] = new DateTime(int.Parse(this.comboBox2.Text), int.Parse(this.comboBox3.Text), 1, 00, 00, 01);
                chk_date[1] = new DateTime(int.Parse(this.comboBox2.Text), int.Parse(this.comboBox3.Text),DateTime.DaysInMonth(int.Parse(this.comboBox2.Text),int.Parse(this.comboBox3.Text)), 00, 00, 01);
            }
            else
            {
                MessageBox.Show("日付が適切ではありません。");
                return;
            }
            using (OleDbConnection cn = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + database_path + @"\Tc_Db.mdb;"))
            {
                try
                {
                    cn.Open();
                    for (DateTime i = chk_date[0]; i <= chk_date[1]; i = i.AddDays(1))
                    {
                        OleDbCommand cmd = new OleDbCommand("SELECT ID,氏名,日付,出社,退社,外出,帰社,早出申請,残業申請,休出申請,有給申請,無給（遅・早・欠）申請,代休申請,備考 FROM Tc_Tbl WHERE" + " 日付 = #" + i.ToString("yyyy/MM/dd 00:00:01") + "# AND 氏名 = '" + this.comboBox1.Text + "'", cn);
                        using(OleDbDataReader dr = cmd.ExecuteReader())
                        {
                            if (!dr.Read())
                            {
                                this.dataGridView1.Rows.Add(
                                    "", //Button
                                    "", //ID
                                    "", //氏名
                                    i.ToString("yyyy/MM/dd 00:00:01"),
                                    "", //出社
                                    "", //退社
                                    "", //外出
                                    "", //帰社
                                    "", //早出申請
                                    "", //残業申請
                                    "", //休出申請
                                    "", //有給申請
                                    "", //無給（遅・早・欠）申請
                                    "", //代休申請
                                    "", //備考
                                    "", //内容不備(NoDb)
                                    "" //Button
                                    );

                                // 2016/04/21 追加
                                if (6 == (int)i.DayOfWeek)
                                {
                                    this.dataGridView1.Rows[dataGridView1.Rows.Count - 1].DefaultCellStyle.BackColor = Color.FromArgb(220,220,255);
                                }
                                else if (0 == (int)i.DayOfWeek)
                                {
                                    this.dataGridView1.Rows[dataGridView1.Rows.Count - 1].DefaultCellStyle.BackColor = Color.FromArgb(255, 220, 220);
                                }
                                
                            }
                            else
                            {
                                int j = 0;
                                do
                                {
                                    if (j != 0) MessageBox.Show(i.ToString("yyyy/MM/dd") + "が重複しています。");
                                    this.dataGridView1.Rows.Add(
                                        "更新", //Button
                                        dr[0], //ID
                                        dr[1], //氏名
                                        DateTime.Parse(dr[2].ToString()).ToString("yyyy/MM/dd 00:00:01"),
                                        dr[3], //出社
                                        dr[4], //退社
                                        dr[5], //外出
                                        dr[6], //帰社
                                        dr[7], //早出申請
                                        dr[8], //残業申請
                                        dr[9], //休出申請
                                        dr[10], //有給申請
                                        dr[11], //無給（遅・早・欠）申請
                                        dr[12], //代休申請
                                        dr[13], //備考
                                        DateCheck.Do(new string[] { dr[2].ToString(), dr[3].ToString(), dr[4].ToString(), dr[5].ToString(), dr[6].ToString() }),
                                        "削除" //Button
                                        );

                                    // 2016/04/21 追加
                                    if (6 == (int)DateTime.Parse(dr[2].ToString()).DayOfWeek)
                                    {
                                        this.dataGridView1.Rows[dataGridView1.Rows.Count - 1].DefaultCellStyle.BackColor = Color.FromArgb(220, 220, 255);
                                    }
                                    else if (0 == (int)DateTime.Parse(dr[2].ToString()).DayOfWeek)
                                    {
                                        this.dataGridView1.Rows[dataGridView1.Rows.Count - 1].DefaultCellStyle.BackColor = Color.FromArgb(255, 220, 220);
                                    }

                                    j++;
                                }
                                while (dr.Read());
                            }
                        }
                    }
                }
                catch
                {
                    MessageBox.Show("データベースに接続できませんでした。管理者に連絡してください。");
                    return;
                }
                finally
                {
                    cn.Close();
                }
            }
            MessageBox.Show("取得しました。");
        }
        private void DataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            if (e.ColumnIndex == 0 && dataGridView1.Rows[e.RowIndex].Cells[0].Value.ToString() == "更新")
            {
                if (this.textBox1.Text != "1234") return;
                DialogResult YesNo = MessageBox.Show("更新しますか？", "確認", MessageBoxButtons.YesNo);
                if (YesNo == DialogResult.Yes) data_update(e);
            }
            else if (e.ColumnIndex == 16 && dataGridView1.Rows[e.RowIndex].Cells[16].Value.ToString() == "削除")
            {
                if (this.textBox1.Text != "1234") return;
                DialogResult YesNo = MessageBox.Show("削除しますか？","確認", MessageBoxButtons.YesNo);
                if(YesNo == DialogResult.Yes) data_delete(e);
            }
        }
        private void data_delete(DataGridViewCellEventArgs e)
        {
            using (OleDbConnection cn = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + database_path + @"\Tc_Db.mdb;"))
            {
                OleDbTransaction transaction = null;
                try
                {
                    cn.Open();
                    transaction = cn.BeginTransaction(IsolationLevel.ReadCommitted);
                    OleDbCommand cmd = new OleDbCommand("DELETE FROM Tc_Tbl WHERE ID = " + dataGridView1.Rows[e.RowIndex].Cells[1].Value.ToString() + "", cn);
                    cmd.Transaction = transaction;
                    cmd.ExecuteNonQuery();
                    transaction.Commit();
                }
                catch
                {
                    MessageBox.Show("データベースに接続できませんでした。管理者に連絡してください。");
                    try
                    {
                        transaction.Rollback();
                        return;
                    }
                    catch
                    {
                        return;
                    }
                }
                finally
                {
                    cn.Close();
                }
                button1.PerformClick();
            }
        }
        private void data_update(DataGridViewCellEventArgs e)
        {
            string[] row_val = new string[12];
            for (int i = 0; i < 12; i++)
            {
                row_val[i] = "null";
                if (i < 4)
                {
                    DateTime dt;
                    if (dataGridView1.Rows[e.RowIndex].Cells[i + 4].Value != null && dataGridView1.Rows[e.RowIndex].Cells[i + 4].Value.ToString() != "" && DateTime.TryParse(dataGridView1.Rows[e.RowIndex].Cells[i + 4].Value.ToString(), out dt))
                    {
                        row_val[i] = "'" + dt.ToString("yyyy/MM/dd HH:mm:ss") + "'";
                    }
                    else if (dataGridView1.Rows[e.RowIndex].Cells[i + 4].Value != null && dataGridView1.Rows[e.RowIndex].Cells[i + 4].Value.ToString() != "")
                    {
                        MessageBox.Show(i + 4 + "列目が不正な文字列です。");
                        return;
                    }
                }
                else if (dataGridView1.Rows[e.RowIndex].Cells[i + 4].Value != null && dataGridView1.Rows[e.RowIndex].Cells[i + 4].Value.ToString() != "")
                {
                    row_val[i] = "'" + dataGridView1.Rows[e.RowIndex].Cells[i + 4].Value.ToString() + "'";
                }
            }
            using (OleDbConnection cn = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + database_path + @"\Tc_Db.mdb;"))
            {
                OleDbTransaction transaction = null;
                try
                {
                    cn.Open();
                    transaction = cn.BeginTransaction(IsolationLevel.ReadCommitted);
                    OleDbCommand cmd = new OleDbCommand(
                        "UPDATE Tc_Tbl SET" + 
                        " 出社 = " + row_val[0] +"," + 
                        " 退社 = " + row_val[1] + "," + 
                        " 外出 = " + row_val[2] + "," + 
                        " 帰社 = " + row_val[3] + "," + 
                        " 早出申請 = " + row_val[4] + "," + 
                        " 残業申請 = " + row_val[5] + "," + 
                        " 休出申請 = " + row_val[6] + "," + 
                        " 有給申請 = " + row_val[7] + "," +
                        " 無給（遅・早・欠）申請 = " + row_val[8] + "," +
                        " 代休申請 = " + row_val[9] + "," + 
                        " 備考 = " + row_val[10] + 
                        " WHERE ID = " + dataGridView1.Rows[e.RowIndex].Cells[1].Value.ToString() + "", cn
                        );
                    cmd.Transaction = transaction;
                    cmd.ExecuteNonQuery();
                    transaction.Commit();
                }
                catch
                {
                    MessageBox.Show("データベースに接続できませんでした。管理者に連絡してください。");
                    try
                    {
                        transaction.Rollback();
                        return;
                    }
                    catch
                    {
                        return;
                    }
                }
                finally
                {
                    cn.Close();
                }
                button1.PerformClick();
            }
        }
    }
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Windows.Forms;

using System.Collections;

using System.Data.OleDb;

namespace WindowsFormsApplication1

{

public partial class Form2 : Form

{

private String database_path = System.IO.Path.GetDirectoryName(Application.ExecutablePath);

public Form2(Control[] parent_contorls)

{

InitializeComponent();

foreach (Control ctrl in parent_contorls) if (ctrl is Button && ctrl.Text != "リセット") this.comboBox1.Items.Add(ctrl.Text);

for (int i = -2; i <= 2; i++) this.comboBox2.Items.Add(DateTime.Now.Year + i);

for (int i = 1; i <= 12; i++) this.comboBox3.Items.Add(i.ToString().PadLeft(2, '0'));

for (int i = 1; i <= 31; i++) this.comboBox4.Items.Add(i.ToString().PadLeft(2, '0'));

this.comboBox2.Text = DateTime.Now.ToString("yyyy");

this.comboBox3.Text = DateTime.Now.ToString("MM");

this.comboBox4.Text = DateTime.Now.ToString("dd");

this.WindowState = FormWindowState.Maximized;

//this.dataGridView1.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;

//this.dataGridView1.ColumnHeadersDefaultCellStyle.WrapMode = DataGridViewTriState.False;

}

private void button2_Click(object sender, EventArgs e)

{

if (this.textBox1.Text != "1234") return;

if (this.comboBox1.Text == "")

{

MessageBox.Show("名前を選択してください。");

return;

}

DateTime dt;

string[] check_date = new string[2];

if (DateTime.TryParse(this.comboBox2.Text + "/" + this.comboBox3.Text + "/" + this.comboBox4.Text, out dt))

{

check_date[0] = dt.ToString("yyyy/MM/dd 00:00:01");

check_date[1] = dt.ToString("yyyy/MM/dd 08:25:00");

}

else

{

MessageBox.Show("日付が適切ではありません。");

return;

}

using (OleDbConnection cn = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + database_path + @"\Tc_Db.mdb;"))

{

OleDbTransaction transaction = null;

try

{

cn.Open();

transaction = cn.BeginTransaction(IsolationLevel.ReadCommitted);

OleDbCommand cmd = new OleDbCommand("INSERT INTO Tc_Tbl (氏名,日付) VALUES ('" + this.comboBox1.Text + "','" + check_date[0] + "')", cn);

cmd.Transaction = transaction;

cmd.ExecuteNonQuery();

transaction.Commit();

}

catch

{

MessageBox.Show("データベースに接続できませんでした。管理者に連絡してください。");

try

{

transaction.Rollback();

return;

}

catch

{

return;

}

finally

{

cn.Close();

}

button1.PerformClick();

}

private void button1_Click(object sender, EventArgs e)

{

if(this.comboBox1.Text == "")

{

MessageBox.Show("名前を選択してください。");

return;

}

this.dataGridView1.Rows.Clear();

DateTime tmp;

DateTime[] chk_date = new DateTime[2];

if (DateTime.TryParse(this.comboBox2.Text + "/" + this.comboBox3.Text + "/1", out tmp))

{

chk_date[0] = new DateTime(int.Parse(this.comboBox2.Text), int.Parse(this.comboBox3.Text), 1, 00, 00, 01);

chk_date[1] = new DateTime(int.Parse(this.comboBox2.Text), int.Parse(this.comboBox3.Text),DateTime.DaysInMonth(int.Parse(this.comboBox2.Text),int.Parse(this.comboBox3.Text)), 00, 00, 01);

}

else

{

MessageBox.Show("日付が適切ではありません。");

return;

}

using (OleDbConnection cn = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + database_path + @"\Tc_Db.mdb;"))

{

try

{

cn.Open();

for (DateTime i = chk_date[0]; i <= chk_date[1]; i = i.AddDays(1))

{

OleDbCommand cmd = new OleDbCommand("SELECT ID,氏名,日付,出社,退社,外出,帰社,早出申請,残業申請,休出申請,有給申請,無給（遅・早・欠）申請,代休申請,備考 FROM Tc_Tbl WHERE" + " 日付 = #" + i.ToString("yyyy/MM/dd 00:00:01") + "# AND 氏名 = '" + this.comboBox1.Text + "'", cn);

using(OleDbDataReader dr = cmd.ExecuteReader())

{

if (!dr.Read())

{

this.dataGridView1.Rows.Add(

"", //Button

"", //ID

"", //氏名

i.ToString("yyyy/MM/dd 00:00:01"),

"", //出社

"", //退社

"", //外出

"", //帰社

"", //早出申請

"", //残業申請

"", //休出申請

"", //有給申請

"", //無給（遅・早・欠）申請

"", //代休申請

"", //備考

"", //内容不備(NoDb)

"" //Button

);

// 2016/04/21 追加

if (6 == (int)i.DayOfWeek)

{

this.dataGridView1.Rows[dataGridView1.Rows.Count - 1].DefaultCellStyle.BackColor = Color.FromArgb(220,220,255);

}

else if (0 == (int)i.DayOfWeek)

{

this.dataGridView1.Rows[dataGridView1.Rows.Count - 1].DefaultCellStyle.BackColor = Color.FromArgb(255, 220, 220);

}

else

{

int j = 0;

{

if (j != 0) MessageBox.Show(i.ToString("yyyy/MM/dd") + "が重複しています。");

this.dataGridView1.Rows.Add(

"更新", //Button

dr[0], //ID

dr[1], //氏名

DateTime.Parse(dr[2].ToString()).ToString("yyyy/MM/dd 00:00:01"),

dr[3], //出社

dr[4], //退社

dr[5], //外出

dr[6], //帰社

dr[7], //早出申請

dr[8], //残業申請

dr[9], //休出申請

dr[10], //有給申請

dr[11], //無給（遅・早・欠）申請

dr[12], //代休申請

dr[13], //備考

DateCheck.Do(new string[] { dr[2].ToString(), dr[3].ToString(), dr[4].ToString(), dr[5].ToString(), dr[6].ToString() }),

"削除" //Button

);

// 2016/04/21 追加

if (6 == (int)DateTime.Parse(dr[2].ToString()).DayOfWeek)

{

this.dataGridView1.Rows[dataGridView1.Rows.Count - 1].DefaultCellStyle.BackColor = Color.FromArgb(220, 220, 255);

}

else if (0 == (int)DateTime.Parse(dr[2].ToString()).DayOfWeek)

{

this.dataGridView1.Rows[dataGridView1.Rows.Count - 1].DefaultCellStyle.BackColor = Color.FromArgb(255, 220, 220);

}

j++;

}

while (dr.Read());

}

catch

{

MessageBox.Show("データベースに接続できませんでした。管理者に連絡してください。");

return;

}

finally

{

cn.Close();

}

MessageBox.Show("取得しました。");

}

private void DataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)

{

if (e.ColumnIndex == 0 && dataGridView1.Rows[e.RowIndex].Cells[0].Value.ToString() == "更新")

{

if (this.textBox1.Text != "1234") return;

DialogResult YesNo = MessageBox.Show("更新しますか？", "確認", MessageBoxButtons.YesNo);

if (YesNo == DialogResult.Yes) data_update(e);

}

else if (e.ColumnIndex == 16 && dataGridView1.Rows[e.RowIndex].Cells[16].Value.ToString() == "削除")

{

if (this.textBox1.Text != "1234") return;

DialogResult YesNo = MessageBox.Show("削除しますか？","確認", MessageBoxButtons.YesNo);

if(YesNo == DialogResult.Yes) data_delete(e);

}

private void data_delete(DataGridViewCellEventArgs e)

{

using (OleDbConnection cn = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + database_path + @"\Tc_Db.mdb;"))

{

OleDbTransaction transaction = null;

try

{

cn.Open();

transaction = cn.BeginTransaction(IsolationLevel.ReadCommitted);

OleDbCommand cmd = new OleDbCommand("DELETE FROM Tc_Tbl WHERE ID = " + dataGridView1.Rows[e.RowIndex].Cells[1].Value.ToString() + "", cn);

cmd.Transaction = transaction;

cmd.ExecuteNonQuery();

transaction.Commit();

}

catch

{

MessageBox.Show("データベースに接続できませんでした。管理者に連絡してください。");

try

{

transaction.Rollback();

return;

}

catch

{

return;

}

finally

{

cn.Close();

}

button1.PerformClick();

}

private void data_update(DataGridViewCellEventArgs e)

{

string[] row_val = new string[12];

for (int i = 0; i < 12; i++)

{

row_val[i] = "null";

if (i < 4)

{

DateTime dt;

if (dataGridView1.Rows[e.RowIndex].Cells[i + 4].Value != null && dataGridView1.Rows[e.RowIndex].Cells[i + 4].Value.ToString() != "" && DateTime.TryParse(dataGridView1.Rows[e.RowIndex].Cells[i + 4].Value.ToString(), out dt))

{

row_val[i] = "'" + dt.ToString("yyyy/MM/dd HH:mm:ss") + "'";

}

else if (dataGridView1.Rows[e.RowIndex].Cells[i + 4].Value != null && dataGridView1.Rows[e.RowIndex].Cells[i + 4].Value.ToString() != "")

{

MessageBox.Show(i + 4 + "列目が不正な文字列です。");

return;

}

else if (dataGridView1.Rows[e.RowIndex].Cells[i + 4].Value != null && dataGridView1.Rows[e.RowIndex].Cells[i + 4].Value.ToString() != "")

{

row_val[i] = "'" + dataGridView1.Rows[e.RowIndex].Cells[i + 4].Value.ToString() + "'";

}

using (OleDbConnection cn = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + database_path + @"\Tc_Db.mdb;"))

{

OleDbTransaction transaction = null;

try

{

cn.Open();

transaction = cn.BeginTransaction(IsolationLevel.ReadCommitted);

OleDbCommand cmd = new OleDbCommand(

"UPDATE Tc_Tbl SET" +

" 出社 = " + row_val[0] +"," +

" 退社 = " + row_val[1] + "," +

" 外出 = " + row_val[2] + "," +

" 帰社 = " + row_val[3] + "," +

" 早出申請 = " + row_val[4] + "," +

" 残業申請 = " + row_val[5] + "," +

" 休出申請 = " + row_val[6] + "," +

" 有給申請 = " + row_val[7] + "," +

" 無給（遅・早・欠）申請 = " + row_val[8] + "," +

" 代休申請 = " + row_val[9] + "," +

" 備考 = " + row_val[10] +

" WHERE ID = " + dataGridView1.Rows[e.RowIndex].Cells[1].Value.ToString() + "", cn

);

cmd.Transaction = transaction;

cmd.ExecuteNonQuery();

transaction.Commit();

}

catch

{

MessageBox.Show("データベースに接続できませんでした。管理者に連絡してください。");

try

{

transaction.Rollback();

return;

}

catch

{

return;

}

finally

{

cn.Close();

}

button1.PerformClick();

}

DataCheck.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace WindowsFormsApplication1
{
    class DateCheck
    {
        static public string Do(string[] tmp)
        {
            //出社がない
            if (tmp[1] == "") return "NG";

            //出社が日付と同日か
            if(tmp[0] != "" && tmp[1] != "")
            {
                if (DateTime.Parse(tmp[0]).Date != DateTime.Parse(tmp[1]).Date) return "NG";
            }

            //出社したら退社しているか
            if (tmp[1] != "" && tmp[2] == "") return "NG";

            //退社してて出社がない
            if (tmp[1] == "" && tmp[2] != "") return "NG";

            //退社が出社と同日あるいは翌日か、退社が出社より前ではないか
            if (tmp[1] != "" && tmp[2] != "")
            {
                if (DateTime.Parse(tmp[1]) > DateTime.Parse(tmp[2]) || DateTime.Parse(tmp[1]).Date != DateTime.Parse(tmp[2]).Date && DateTime.Parse(tmp[1]).AddDays(1).Date != DateTime.Parse(tmp[2]).Date) return "NG";
            }

            //外出が出社より後で退出より前か
            if (tmp[1] != "" && tmp[2] != "" && tmp[3] != "")
            {
                if (DateTime.Parse(tmp[1]) > DateTime.Parse(tmp[3]) || DateTime.Parse(tmp[2]) < DateTime.Parse(tmp[3])) return "NG";
            }

            //外出したら帰社しているか
            if (tmp[3] != "" && tmp[4] == "") return "NG";

            //帰社してて外出がない
            if (tmp[3] == "" && tmp[4] != "") return "NG";

            //帰社が外出より後で退出より前か
            if (tmp[2] != "" && tmp[3] != "" && tmp[4] != "")
            {
                if (DateTime.Parse(tmp[2]) < DateTime.Parse(tmp[4]) || DateTime.Parse(tmp[3]) > DateTime.Parse(tmp[4])) return "NG";
            }
            return "OK";
        }
    }
}

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

namespace WindowsFormsApplication1

{

class DateCheck

{

static public string Do(string[] tmp)

{

//出社がない

if (tmp[1] == "") return "NG";

//出社が日付と同日か

if(tmp[0] != "" && tmp[1] != "")

{

if (DateTime.Parse(tmp[0]).Date != DateTime.Parse(tmp[1]).Date) return "NG";

}

//出社したら退社しているか

if (tmp[1] != "" && tmp[2] == "") return "NG";

//退社してて出社がない

if (tmp[1] == "" && tmp[2] != "") return "NG";

//退社が出社と同日あるいは翌日か、退社が出社より前ではないか

if (tmp[1] != "" && tmp[2] != "")

{

if (DateTime.Parse(tmp[1]) > DateTime.Parse(tmp[2]) || DateTime.Parse(tmp[1]).Date != DateTime.Parse(tmp[2]).Date && DateTime.Parse(tmp[1]).AddDays(1).Date != DateTime.Parse(tmp[2]).Date) return "NG";

}

//外出が出社より後で退出より前か

if (tmp[1] != "" && tmp[2] != "" && tmp[3] != "")

{

if (DateTime.Parse(tmp[1]) > DateTime.Parse(tmp[3]) || DateTime.Parse(tmp[2]) < DateTime.Parse(tmp[3])) return "NG";

}

//外出したら帰社しているか

if (tmp[3] != "" && tmp[4] == "") return "NG";

//帰社してて外出がない

if (tmp[3] == "" && tmp[4] != "") return "NG";

//帰社が外出より後で退出より前か

if (tmp[2] != "" && tmp[3] != "" && tmp[4] != "")

{

if (DateTime.Parse(tmp[2]) < DateTime.Parse(tmp[4]) || DateTime.Parse(tmp[3]) > DateTime.Parse(tmp[4])) return "NG";

}

return "OK";

}

C#でmdbに登録してエクセルで抜き出して集計

Sub test()

Application.CommandBars("cell").Reset

End Sub
Sub auto_open()

On Error Resume Next

Application.CommandBars("cell").Controls("TC集計開始").Delete
Application.CommandBars("cell").Controls("TC集計開始").Delete

With Application.CommandBars("cell").Controls.Add
  .OnAction = "main"
  .Caption = "TC集計開始"
End With

Application.CommandBars("cell").Controls("確認表示").Delete
Application.CommandBars("cell").Controls("確認表示").Delete

With Application.CommandBars("cell").Controls.Add
  .OnAction = "check_layout"
  .Caption = "確認表示"
End With


End Sub
Sub auto_close()

On Error Resume Next

Application.CommandBars("cell").Controls("TC集計開始").Delete
Application.CommandBars("cell").Controls("TC集計開始").Delete

Application.CommandBars("cell").Controls("確認表示").Delete
Application.CommandBars("cell").Controls("確認表示").Delete

End Sub

Sub check_layout()

tmp = MsgBox("実行後に保存しないよう注意してください。実行しますか？", vbYesNo)
If tmp = vbNo Then Exit Sub

For Each w In Worksheets

  If w.Name <> "MAIN MENU" Then
  
    w.Columns("A:X").ColumnWidth = 0
    w.Rows("1:3").RowHeight = 0
    
  End If
  
Next

MsgBox "終了しました。"

End Sub

Sub init()

For Each w In Worksheets

  If w.Name <> "MAIN MENU" Then

    w.Activate
    w.Unprotect
    w.Range("W4").Select
    
    ActiveWindow.Zoom = 85
    ActiveWindow.ScrollColumn = 1
    ActiveWindow.ScrollRow = 1

    w.Range("B5:B35").ClearContents
    w.Range("C5:C35").ClearContents
    w.Range("E5:E35").ClearContents
    w.Range("F5:F35").ClearContents
    w.Range("S5:S35").ClearContents
    w.Range("W4:AK35").ClearContents

    w.Range("B5:C35").NumberFormatLocal = "0"
    w.Range("E5:F35").NumberFormatLocal = "0"
    w.Range("N5:Q35").NumberFormatLocal = "0.00"
    w.Range("W5:W35").NumberFormatLocal = "yyyy/mm/dd hh:mm:ss"
    w.Range("X5:X35").NumberFormatLocal = "0"
    w.Range("Z5:AD35").NumberFormatLocal = "yyyy/mm/dd hh:mm:ss"
    w.Range("AE5:AK35").NumberFormatLocal = "@"
  
    For r = 5 To 35
    
      w.Cells(r, 2).Value = "=HOUR(AA" & r & ")"
      w.Cells(r, 3).Value = "=MINUTE(AA" & r & ")"
      w.Cells(r, 5).Value = "=HOUR(AB" & r & ") + (INT(AB" & r & ")-INT(AA" & r & ")) * 24"
      w.Cells(r, 6).Value = "=MINUTE(AB" & r & ")"
      
      w.Cells(r, 14).Value = "=IF(D" & r & "="""","""",(IF(AND(VALUE(D" & r & ")<VALUE(""7:31""),VALUE(D" & r & ")>=VALUE(""6:31"")),1,IF(AND(VALUE(D" & r & ")<VALUE(""6:31""),VALUE(D" & r & ")>=VALUE(""5:31"")),2,IF(AND(VALUE(D" & r & ")<VALUE(""5:31""),VALUE(D" & r & ")>=VALUE(""4:30"")),3,0)))))"
      
      'w.Cells(r, 15).Value = "=IF(H" & r & "="""","""",IF(AND(VALUE(D" & r & ")<=VALUE(""8:30""),VALUE(G" & r & ")>=VALUE(""17:21"")),8,IF(AND(NOT(VALUE(D" & r & ")<=VALUE(""8:30"")),NOT(VALUE(G" & r & ")>VALUE(""17:21""))),FLOOR(H" & r & "*24-I" & r & ",0.5),IF(AND(VALUE(D" & r & ")>=VALUE(""8:30""),VALUE(G" & r & ")>=VALUE(""17:21"")),IF(FLOOR(G" & r & "*24-D" & r & "*24-I" & r & ",0.5)>4,4,FLOOR(G" & r & "*24-D" & r & "*24-I" & r & ",0.5)),IF(VALUE(G" & r & ")<=VALUE(""17:21""),FLOOR(VALUE(G" & r & ")*24-(""8:30"")*24-I" & r & ",0.5))))))"
      'w.Cells(r, 16).Value = "=IF(H" & r & "="""","""",IF(M" & r & "="""",IF(VALUE(G" & r & ")>VALUE(""18:30""),IF(AND(-Q" & r & "+FLOOR(G" & r & "*24-""18:01""*24,0.5)>2,S" & r & "=""申請不要""),2,-Q" & r & "+FLOOR(G" & r & "*24-""18:01""*24,0.5)),0),IF(VALUE(G" & r & ")>VALUE(""18:30""),IF(AND(-Q" & r & "+FLOOR(G" & r & "*24-""17:31""*24,0.5)>2,S" & r & "=""申請不要""),2,-Q" & r & "+FLOOR(G" & r & "*24-""18:01""*24,0.5)),0)))"
      'w.Cells(r, 17).Value = "=IF(H" & r & "="""","""",(IF(VALUE(G" & r & ")*24>TIMEVALUE(""22:01"")*24,FLOOR(G" & r & "-""22:01"",""0:30"")*24,0)))"
      
      ' rev2.3 2016/4/26追加
      w.Cells(r, 15).Value = "=IF(H" & r & "="""","""",IF(AND(VALUE(D" & r & ")<=VALUE(""8:30""),VALUE(G" & r & ")>=VALUE(""17:20"")),8,IF(AND(VALUE(D" & r & ")>VALUE(""8:30""),VALUE(G" & r & ")<VALUE(""17:20"")),FLOOR(VALUE(H" & r & ")*24-I" & r & ",0.25),IF(AND(VALUE(D" & r & ")>VALUE(""8:30""),VALUE(G" & r & ")>=VALUE(""17:20"")),FLOOR((VALUE(G" & r & ")*24-VALUE(D" & r & ")*24)-I" & r & ",0.25),FLOOR((VALUE(G" & r & ")*24-VALUE(""8:30"")*24)-I" & r & ",0.25)))))"
      'w.Cells(r, 16).Value = "=IF(H" & r & "="""","""",IF(AND(VALUE(G" & r & ")>VALUE(""18:30""),S" & r & "<>""申請不要""),IF(M" & r & "="""",-Q" & r & "+FLOOR(VALUE(G" & r & ")*24-VALUE(""18:00"")*24,0.25),-Q" & r & "+FLOOR(VALUE(G" & r & ")*24-VALUE(""17:30"")*24,0.25)),0))"
      w.Cells(r, 17).Value = "=IF(H" & r & "="""","""",IF(VALUE(G" & r & ")*24>VALUE(""22:00"")*24,FLOOR(VALUE(G" & r & ")*24-VALUE(""22:00"")*24,0.25),0))"
      
      ' rev2.5.1
      w.Cells(r, 16).Value = "=IF(H" & r & "="""","""",IF(AND(VALUE(G" & r & ")>VALUE(""18:14:59""),S" & r & "<>""申請不要""),IF(M" & r & "="""",-Q" & r & "+FLOOR(VALUE(G" & r & ")*24-VALUE(""17:59:59"")*24,0.25),-Q" & r & "+FLOOR(VALUE(G" & r & ")*24-VALUE(""17:29:59"")*24,0.25)),0))"
      
    Next r

    DoEvents

  End If
  
Next w

End Sub

Sub ed()

For Each w In Worksheets

  If w.Name <> "MAIN MENU" Then
    
    w.Activate
    
    For r = 5 To 35
    
      If w.Cells(r, 27).Value = "" Then w.Cells(r, 2).Value = ""
      If w.Cells(r, 27).Value = "" Then w.Cells(r, 3).Value = ""
      If w.Cells(r, 28).Value = "" Then w.Cells(r, 5).Value = ""
      If w.Cells(r, 28).Value = "" Then w.Cells(r, 6).Value = ""

      If w.Cells(r, 31).Value = "" And w.Cells(r, 13).Text = "" Then
      
          w.Cells(r, 14).Value = ""
          
      End If
      
      If w.Cells(r, 32).Value = "" And w.Cells(r, 13).Text = "" Then
      
          w.Cells(r, 16).Value = ""
          w.Cells(r, 17).Value = ""
          
      End If

      If w.Cells(r, 33).Value = "" And w.Cells(r, 13).Text <> "" Then
      
          w.Cells(r, 14).Value = ""
          w.Cells(r, 15).Value = ""
          w.Cells(r, 16).Value = ""
          w.Cells(r, 17).Value = ""
          
      End If
      
      Select Case w.Name
      
          Case "xxx"
    
          Case Else
          
              If w.Cells(r, 13).Text = "" Then w.Cells(r, 15).NumberFormatLocal = """-"""
          
      End Select

    Next r

    DoEvents

  End If

  w.Columns("W:AK").AutoFit

Next w

End Sub

Sub ck()

For Each w In Worksheets

  If w.Name <> "MAIN MENU" Then
    
    For r = 5 To 35
    
    Select Case w.Name
    
      Case "xxx"
      Case Else
      
        If IsError(w.Cells(r, 15)) Then
        
          w.Activate: w.Cells(r, 15).Select
          MsgBox w.Name & "の" & r & "行目の定時時間が正しく取得できません。"
        
        Else
        
          If w.Cells(r, 13).Value = "" And w.Cells(r, 15).Value <> "" And w.Cells(r, 15).Value < 8 Then
    
            If w.Cells(r, 19).Value = "" Then
            
              w.Activate: w.Cells(r, 15).Select
              MsgBox w.Name & "の" & r & "行目の定時時間が不足しています。" & Chr(13) & "申請がありません。"
              
            ElseIf w.Cells(r, 19).Value <> "" And w.Cells(r, 19).Value <> "有給" And w.Cells(r, 19).Value <> "半休" Then
            
              w.Activate: w.Cells(r, 15).Select
              MsgBox w.Name & "の" & r & "行目の定時時間が不足しています。" & Chr(13) & "有給・半休が申請されていません。"
              
            End If
          
          End If
        
        End If
      
    End Select
      
    Next r
      
  End If
Next w

End Sub

Private Sub main()

Call init

f = "#" & Sheets("MAIN MENU").Cells(5, 4) & "/" & Sheets("MAIN MENU").Cells(5, 5) & "/1 00:00:01#"
tmp = DateAdd("d", -1, DateAdd("m", 1, Sheets("MAIN MENU").Cells(5, 4) & "/" & Sheets("MAIN MENU").Cells(5, 5) & "/1"))
t = "#" & tmp & " 00:00:01#"

For Each w In Worksheets

    If w.Name <> "MAIN MENU" Then
    
      w.Activate

      If False = fnc(w.Name, f, t) Then Exit Sub
      
      DoEvents
      
    End If

Next w

Call ed
Call ck

MsgBox "done"

End Sub

Function n(fld As Variant)

If False = IsNull(fld) Then

    n = CStr(fld)
    Exit Function

End If

n = ""

End Function

Function fnc(u, f, t)

On Error GoTo try

p = ActiveWorkbook.Path & "\Tc_Db.mdb"
s = "SELECT * FROM Tc_Tbl WHERE 氏名 = '" & u & "' AND 日付 BETWEEN " & f & " AND " & t & ""

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & p & ";"
rs.Open s, cn

k = 0
tmp = Sheets("MAIN MENU").Cells(5, 4) & "/" & Sheets("MAIN MENU").Cells(5, 5) & "/1 00:00:01"

For i = 5 To 35
      If Month(tmp) = Month(DateAdd("d", k, tmp)) Then Sheets(u).Cells(i, 23) = DateAdd("d", k, tmp)
      k = k + 1
Next

For i = 24 To 37
    Sheets(u).Cells(4, i) = rs(i - 24).Name
Next i

Do Until rs.EOF
    
    For r = 5 To 35
    
      If Sheets(u).Cells(r, 23) <> "" And Day(Sheets(u).Cells(r, 23)) = Day(n(rs(2))) Then

        For c = 24 To 37
        
            Sheets(u).Cells(r, c).Value = n(rs(c - 24))
    
        Next c

        If Sheets(u).Cells(r, 31).Value <> "" Then Sheets(u).Cells(r, 19).Value = "早出申請有"
        If Sheets(u).Cells(r, 32).Value <> "" Then Sheets(u).Cells(r, 19).Value = "残業申請有"
        If Sheets(u).Cells(r, 31).Value <> "" And Sheets(u).Cells(r, 32).Value <> "" Then Sheets(u).Cells(r, 19).Value = "残・早申請有"
        If Sheets(u).Cells(r, 33).Value <> "" Then Sheets(u).Cells(r, 19).Value = "休日出勤申請有"
        If Sheets(u).Cells(r, 34).Value <> "" Then Sheets(u).Cells(r, 19).Value = "有休"
        If InStr(Sheets(u).Cells(r, 34).Value, "(") > 0 Then Sheets(u).Cells(r, 19).Value = "半休"
        If Sheets(u).Cells(r, 35).Value <> "" Then Sheets(u).Cells(r, 19).Value = "欠勤"
        If Sheets(u).Cells(r, 36).Value <> "" Then Sheets(u).Cells(r, 19).Value = "代休"
        If InStr(Sheets(u).Cells(r, 37).Value, "申請不要") > 0 Then Sheets(u).Cells(r, 19).Value = "申請不要"
        
      End If
    
    Next r

    rs.MoveNext
    
Loop

rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

fnc = True

Exit Function

try:

    MsgBox "error"
    fnc = False
    
End Function

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

Sub test()

Application.CommandBars("cell").Reset

End Sub

Sub auto_open()

On Error Resume Next

Application.CommandBars("cell").Controls("TC集計開始").Delete

With Application.CommandBars("cell").Controls.Add

.OnAction = "main"

.Caption = "TC集計開始"

End With

Application.CommandBars("cell").Controls("確認表示").Delete

With Application.CommandBars("cell").Controls.Add

.OnAction = "check_layout"

.Caption = "確認表示"

End With

End Sub

Sub auto_close()

On Error Resume Next

Application.CommandBars("cell").Controls("TC集計開始").Delete

Application.CommandBars("cell").Controls("確認表示").Delete

End Sub

Sub check_layout()

tmp = MsgBox("実行後に保存しないよう注意してください。実行しますか？", vbYesNo)

If tmp = vbNo Then Exit Sub

For Each w In Worksheets

If w.Name <> "MAIN MENU" Then

w.Columns("A:X").ColumnWidth = 0

w.Rows("1:3").RowHeight = 0

End If

MsgBox "終了しました。"

End Sub

Sub init()

For Each w In Worksheets

If w.Name <> "MAIN MENU" Then

w.Activate

w.Unprotect

w.Range("W4").Select

ActiveWindow.Zoom = 85

ActiveWindow.ScrollColumn = 1

ActiveWindow.ScrollRow = 1

w.Range("B5:B35").ClearContents

w.Range("C5:C35").ClearContents

w.Range("E5:E35").ClearContents

w.Range("F5:F35").ClearContents

w.Range("S5:S35").ClearContents

w.Range("W4:AK35").ClearContents

w.Range("B5:C35").NumberFormatLocal = "0"

w.Range("E5:F35").NumberFormatLocal = "0"

w.Range("N5:Q35").NumberFormatLocal = "0.00"

w.Range("W5:W35").NumberFormatLocal = "yyyy/mm/dd hh:mm:ss"

w.Range("X5:X35").NumberFormatLocal = "0"

w.Range("Z5:AD35").NumberFormatLocal = "yyyy/mm/dd hh:mm:ss"

w.Range("AE5:AK35").NumberFormatLocal = "@"

For r = 5 To 35

w.Cells(r, 2).Value = "=HOUR(AA" & r & ")"

w.Cells(r, 3).Value = "=MINUTE(AA" & r & ")"

w.Cells(r, 5).Value = "=HOUR(AB" & r & ") + (INT(AB" & r & ")-INT(AA" & r & ")) * 24"

w.Cells(r, 6).Value = "=MINUTE(AB" & r & ")"

w.Cells(r, 14).Value = "=IF(D" & r & "="""","""",(IF(AND(VALUE(D" & r & ")<VALUE(""7:31""),VALUE(D" & r & ")>=VALUE(""6:31"")),1,IF(AND(VALUE(D" & r & ")<VALUE(""6:31""),VALUE(D" & r & ")>=VALUE(""5:31"")),2,IF(AND(VALUE(D" & r & ")<VALUE(""5:31""),VALUE(D" & r & ")>=VALUE(""4:30"")),3,0)))))"

'w.Cells(r, 15).Value = "=IF(H" & r & "="""","""",IF(AND(VALUE(D" & r & ")<=VALUE(""8:30""),VALUE(G" & r & ")>=VALUE(""17:21"")),8,IF(AND(NOT(VALUE(D" & r & ")<=VALUE(""8:30"")),NOT(VALUE(G" & r & ")>VALUE(""17:21""))),FLOOR(H" & r & "*24-I" & r & ",0.5),IF(AND(VALUE(D" & r & ")>=VALUE(""8:30""),VALUE(G" & r & ")>=VALUE(""17:21"")),IF(FLOOR(G" & r & "*24-D" & r & "*24-I" & r & ",0.5)>4,4,FLOOR(G" & r & "*24-D" & r & "*24-I" & r & ",0.5)),IF(VALUE(G" & r & ")<=VALUE(""17:21""),FLOOR(VALUE(G" & r & ")*24-(""8:30"")*24-I" & r & ",0.5))))))"

'w.Cells(r, 16).Value = "=IF(H" & r & "="""","""",IF(M" & r & "="""",IF(VALUE(G" & r & ")>VALUE(""18:30""),IF(AND(-Q" & r & "+FLOOR(G" & r & "*24-""18:01""*24,0.5)>2,S" & r & "=""申請不要""),2,-Q" & r & "+FLOOR(G" & r & "*24-""18:01""*24,0.5)),0),IF(VALUE(G" & r & ")>VALUE(""18:30""),IF(AND(-Q" & r & "+FLOOR(G" & r & "*24-""17:31""*24,0.5)>2,S" & r & "=""申請不要""),2,-Q" & r & "+FLOOR(G" & r & "*24-""18:01""*24,0.5)),0)))"

'w.Cells(r, 17).Value = "=IF(H" & r & "="""","""",(IF(VALUE(G" & r & ")*24>TIMEVALUE(""22:01"")*24,FLOOR(G" & r & "-""22:01"",""0:30"")*24,0)))"

' rev2.3 2016/4/26追加

w.Cells(r, 15).Value = "=IF(H" & r & "="""","""",IF(AND(VALUE(D" & r & ")<=VALUE(""8:30""),VALUE(G" & r & ")>=VALUE(""17:20"")),8,IF(AND(VALUE(D" & r & ")>VALUE(""8:30""),VALUE(G" & r & ")<VALUE(""17:20"")),FLOOR(VALUE(H" & r & ")*24-I" & r & ",0.25),IF(AND(VALUE(D" & r & ")>VALUE(""8:30""),VALUE(G" & r & ")>=VALUE(""17:20"")),FLOOR((VALUE(G" & r & ")*24-VALUE(D" & r & ")*24)-I" & r & ",0.25),FLOOR((VALUE(G" & r & ")*24-VALUE(""8:30"")*24)-I" & r & ",0.25)))))"

'w.Cells(r, 16).Value = "=IF(H" & r & "="""","""",IF(AND(VALUE(G" & r & ")>VALUE(""18:30""),S" & r & "<>""申請不要""),IF(M" & r & "="""",-Q" & r & "+FLOOR(VALUE(G" & r & ")*24-VALUE(""18:00"")*24,0.25),-Q" & r & "+FLOOR(VALUE(G" & r & ")*24-VALUE(""17:30"")*24,0.25)),0))"

w.Cells(r, 17).Value = "=IF(H" & r & "="""","""",IF(VALUE(G" & r & ")*24>VALUE(""22:00"")*24,FLOOR(VALUE(G" & r & ")*24-VALUE(""22:00"")*24,0.25),0))"

' rev2.5.1

w.Cells(r, 16).Value = "=IF(H" & r & "="""","""",IF(AND(VALUE(G" & r & ")>VALUE(""18:14:59""),S" & r & "<>""申請不要""),IF(M" & r & "="""",-Q" & r & "+FLOOR(VALUE(G" & r & ")*24-VALUE(""17:59:59"")*24,0.25),-Q" & r & "+FLOOR(VALUE(G" & r & ")*24-VALUE(""17:29:59"")*24,0.25)),0))"

Next r

DoEvents

End If

Next w

End Sub

Sub ed()

For Each w In Worksheets

If w.Name <> "MAIN MENU" Then

w.Activate

For r = 5 To 35

If w.Cells(r, 27).Value = "" Then w.Cells(r, 2).Value = ""

If w.Cells(r, 27).Value = "" Then w.Cells(r, 3).Value = ""

If w.Cells(r, 28).Value = "" Then w.Cells(r, 5).Value = ""

If w.Cells(r, 28).Value = "" Then w.Cells(r, 6).Value = ""

If w.Cells(r, 31).Value = "" And w.Cells(r, 13).Text = "" Then

w.Cells(r, 14).Value = ""

End If

If w.Cells(r, 32).Value = "" And w.Cells(r, 13).Text = "" Then

w.Cells(r, 16).Value = ""

w.Cells(r, 17).Value = ""

End If

If w.Cells(r, 33).Value = "" And w.Cells(r, 13).Text <> "" Then

w.Cells(r, 14).Value = ""

w.Cells(r, 15).Value = ""

w.Cells(r, 16).Value = ""

w.Cells(r, 17).Value = ""

End If

Select Case w.Name

Case "xxx"

Case Else

If w.Cells(r, 13).Text = "" Then w.Cells(r, 15).NumberFormatLocal = """-"""

End Select

Next r

DoEvents

End If

w.Columns("W:AK").AutoFit

Next w

End Sub

Sub ck()

For Each w In Worksheets

If w.Name <> "MAIN MENU" Then

For r = 5 To 35

Select Case w.Name

Case "xxx"

Case Else

If IsError(w.Cells(r, 15)) Then

w.Activate: w.Cells(r, 15).Select

MsgBox w.Name & "の" & r & "行目の定時時間が正しく取得できません。"

Else

If w.Cells(r, 13).Value = "" And w.Cells(r, 15).Value <> "" And w.Cells(r, 15).Value < 8 Then

If w.Cells(r, 19).Value = "" Then

w.Activate: w.Cells(r, 15).Select

MsgBox w.Name & "の" & r & "行目の定時時間が不足しています。" & Chr(13) & "申請がありません。"

ElseIf w.Cells(r, 19).Value <> "" And w.Cells(r, 19).Value <> "有給" And w.Cells(r, 19).Value <> "半休" Then

w.Activate: w.Cells(r, 15).Select

MsgBox w.Name & "の" & r & "行目の定時時間が不足しています。" & Chr(13) & "有給・半休が申請されていません。"

End If

End Select

Next r

End If

Next w

End Sub

Private Sub main()

Call init

f = "#" & Sheets("MAIN MENU").Cells(5, 4) & "/" & Sheets("MAIN MENU").Cells(5, 5) & "/1 00:00:01#"

tmp = DateAdd("d", -1, DateAdd("m", 1, Sheets("MAIN MENU").Cells(5, 4) & "/" & Sheets("MAIN MENU").Cells(5, 5) & "/1"))

t = "#" & tmp & " 00:00:01#"

For Each w In Worksheets

If w.Name <> "MAIN MENU" Then

w.Activate

If False = fnc(w.Name, f, t) Then Exit Sub

DoEvents

End If

Next w

Call ed

Call ck

MsgBox "done"

End Sub

Function n(fld As Variant)

If False = IsNull(fld) Then

n = CStr(fld)

Exit Function

End If

n = ""

End Function

Function fnc(u, f, t)

On Error GoTo try

p = ActiveWorkbook.Path & "\Tc_Db.mdb"

s = "SELECT * FROM Tc_Tbl WHERE 氏名 = '" & u & "' AND 日付 BETWEEN " & f & " AND " & t & ""

Set cn = CreateObject("ADODB.Connection")

Set rs = CreateObject("ADODB.Recordset")

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & p & ";"

rs.Open s, cn

k = 0

tmp = Sheets("MAIN MENU").Cells(5, 4) & "/" & Sheets("MAIN MENU").Cells(5, 5) & "/1 00:00:01"

For i = 5 To 35

If Month(tmp) = Month(DateAdd("d", k, tmp)) Then Sheets(u).Cells(i, 23) = DateAdd("d", k, tmp)

k = k + 1

For i = 24 To 37

Sheets(u).Cells(4, i) = rs(i - 24).Name

Next i

Do Until rs.EOF

For r = 5 To 35

If Sheets(u).Cells(r, 23) <> "" And Day(Sheets(u).Cells(r, 23)) = Day(n(rs(2))) Then

For c = 24 To 37

Sheets(u).Cells(r, c).Value = n(rs(c - 24))

Next c

If Sheets(u).Cells(r, 31).Value <> "" Then Sheets(u).Cells(r, 19).Value = "早出申請有"

If Sheets(u).Cells(r, 32).Value <> "" Then Sheets(u).Cells(r, 19).Value = "残業申請有"

If Sheets(u).Cells(r, 31).Value <> "" And Sheets(u).Cells(r, 32).Value <> "" Then Sheets(u).Cells(r, 19).Value = "残・早申請有"

If Sheets(u).Cells(r, 33).Value <> "" Then Sheets(u).Cells(r, 19).Value = "休日出勤申請有"

If Sheets(u).Cells(r, 34).Value <> "" Then Sheets(u).Cells(r, 19).Value = "有休"

If InStr(Sheets(u).Cells(r, 34).Value, "(") > 0 Then Sheets(u).Cells(r, 19).Value = "半休"

If Sheets(u).Cells(r, 35).Value <> "" Then Sheets(u).Cells(r, 19).Value = "欠勤"

If Sheets(u).Cells(r, 36).Value <> "" Then Sheets(u).Cells(r, 19).Value = "代休"

If InStr(Sheets(u).Cells(r, 37).Value, "申請不要") > 0 Then Sheets(u).Cells(r, 19).Value = "申請不要"

End If

Next r

rs.MoveNext

Loop

rs.Close

Set rs = Nothing

cn.Close

Set cn = Nothing

fnc = True

Exit Function

try:

MsgBox "error"

fnc = False

End Function

2017-01-152018-02-11

動画サイズの計算

ピクセルから計算。
1280 px * 720 px * 24 bit = 22118400 bit
22118400 bit / 1000 = 22118.4 Kbit　　【 / 1000 = K】
22118.4 Kbit / 1000 = 22.1184 Mbit　　【K / 1000 = M】
22.1184 Mbit * 30fps = 663.552 Mbps　　【bit * フレームレート = bps】
128 Kbps / 1000 = 0.128 Mbps　　【音声】
663.552 Mbps + 0.128 Mbps = 663.68 Mbps　　【映像 + 音声】
663.68 Mbps / 8 = 82.96MB/s　【bps / 8 = byte】

最初から映像や音声がKbpsの場合。
( 映像 Kbps + 音声 Kbps ) × 再生時間 sec / 8 / 1000

ちなみに音声の場合。
44100 Hz * 16 bit * 2 ch * 3 sec = 4233600 bit
4233600 / 8 = 529200 byte
529200 / 1000 = 529.2 KB

Youtubeでの表示

144p → 256×144
240p → 427×240
360p → 640×360
480p → 720×480
720p → 1280×720 （HD）
1080p → 1920×1080（FHD）
1440p → 2560×1440（2K）
2160p → 3840×2160（4K）

2017-01-132024-03-02

ネットワークメモ

･ダムハブ(リピータハブ)
全てのポートに転送する｡

･VLAN
スイッチやルータで作れる､ネットワーク(セグメント)を分ける機能｡
ポートVLAN
タグVLAN
など

･マルチプルVLAN
ポートベースVLANで一つのポートが複数のVLANに参加できるようにする｡

･マルチプルVLANのポート
マルチプルポート=アップリンクポート=オーバーラップポート

･IPアドレス
ABCとクラスに分ける､クラスフルは現在利用されず､現在はクラスレス(CIDR)だが､プライベートアドレスは
A：10.0.0.0 – 10.255.255.255（10.0.0.0/8）
B：172.16.0.0 – 172.31.255.255（172.16.0.0/12）
C：192.168.0.0 – 192.168.255.255（192.168.0.0/16）
と予約(クラス分け)されている｡

https://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q13118580668

x.x.x.0=ネットワーク識別用アドレス
x.x.x.255=ブロードキャストアドレス

･サブネット化(サブネッティング)
トラフィックが多い場合､ネットワークを小さく分けることで､それぞれの通信も抑制できる｡ネットワーク部.ホスト部が､あり､デフォルトで指定されている部分を拡張した部分をサブネット部と呼ぶ｡

クラスのデフォルトのネットワーク部を拡張した部分をサブネット部をと呼び､
“ネットワーク部.サブネット部.ホスト部”とすることになっているが､
ローカルIPアドレスのクラス分けが予約のためなので､
“ネットワーク部.サブネット部.ホスト部”は､サブネット部も含めてネットワーク部と考えれば問題ない｡サブネット化はネットワークを小さくすること｡

https://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q1497905699

https://e-words.jp/w/%E3%82%B5%E3%83%96%E3%83%8D%E3%83%83%E3%83%88%E5%8C%96.html

･ARP
IPアドレスからMACアドレスを調べるブロードキャスト｡

･L2スイッチ
MACアドレスを記憶している｡送られてきたデータを､対象が接続されたポートにのみ転送する｡
※適切な相手にのみ流す=スッチング｡

･L3スイッチ
IPアドレスで参照する｡異なるネットワークを接続できる｡
同じネットワークならMACアドレス､ネットワークが違うとIPアドレスを利用｡

･PPPoE
IPv4、IPv6
ID/パスワードで接続する従来のネット接続のこと。

･PPPoEパススルー(PPPoEブリッジ)
ルーターを介さず接続｡

･IPoE
IPv6
LANと同じプロトコル(PPPを利用しない)でネットまで接続する。
回線認証で接続する。
NAT不要。

･NAT
一つのローカルアドレスと一つのグローバルアドレスを変換する。

･NAPT(IPマスカレード)
複数のローカルアドレスを一つのグローバルアドレスに変換する。
ポート番号を利用してパケットの対応づけを行う。

･NATトラバーサル
NAT/NAPTを利用したネットワークでIPSecを利用する技術。
IPSecパケットをNAT/NAPTを通過させるために必要な技術。

･Wi-FI暗号化

暗号化方式として以下がある。(プロトコル / アルゴリズム)
WEP / RC4
TKIP / RC4
CCMP / AES ※AESと呼ばれることが多い

認証方式としてパーソナルとエンタープライズがある。
パーソナル：PSK認証
エンタープライズ：専用サーバーでの認証

規格として以下がある。(方式/プロトコル/アルゴリズム)
WEP/WEP/RC4
WPA-PSK/CCMP/AES
WPA-PSK/TKIP/RC4
WPA2-PSK/CCMP/AES
WPA2-PSK/TKIP/RC4
WPA3-SAE/CCMP/AES

PSK(Pre-Shared Key)：共通パスワード
SAE(Simultaneous Authentication of Equals)：
パスワードそのものはやり取りせず、正しいパスワードを使って計算すれば正解が出る問題やり取りして認証する方式らしい。。

･電波帯

2.4GHz(g)：障害物に強い。他の電波(レンジ等)と干渉しやすい。
5GHz(a)：障害物に弱い。電波干渉が少ない。
WiFi5以降5GHz(a)を選択で問題ない｡

･IEEE802.11規格

ax：2.4GHz|5GHz / WiFi6 / 9.6Gbps
ac：5GHz / WiFi5 / 6.9Gbps(867Mbps*8)
n：2.4GHz|5GHz / WiFi4 /600Mbps(150Mbps*4)
g：2.4GHz / 第3世代 / 54Mbps
a：5GHz / 第2世代 / 54Mbps
b：2.4GHz / 第2世代 / 11Mbps

･b/g/n/axチャンネル
1-14チャンネル
1CHあたり22MHz

･a/n/ac/axチャンネル
J52：34,38,42,46
W52：36,40,44,48
W53：52,56,60,64
W56：100,104,108,112,116,120,124,128,132,136,140,144
1CHあたり20MHz

･ボンディング
隣り合うチャンネルを束ねる｡
NEC Aterm：デュアルチャネル､クワッドチャネル､オクタチャネル
バッファロー：倍速モード

･ストリーム数/アンテナ数
✕で表示されるているのは受信と送信｡
MIMOと呼ばれる｡本数分同時に通信できるので多い方が速度がでる｡
1ストリームごとの速度は規格によって違う｡
acの例)
2.4GHz(g)：150,300,450,600Mbps
5GHz(a)：433,867,1300,1733Mbps

･LANケーブル

cat5 / 100M bps
cat5e / 1G bps
cat6 / 1G bps
cat6A / 10G bps
cat7 / 10G bps
cat7A / 10G bps
cat8 / 40G bps

2017-01-12

C# 型情報取得

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Windows.Forms;
using System.Reflection;

namespace インスタンスとクラスでのメンバ
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();

            this.Width = 800;
            this.Height = 600;

            TextBox tb = new TextBox();
            tb.Location = new Point(2, 57);
            tb.Multiline = true;
            tb.Size = new System.Drawing.Size(700, 445);
            tb.Anchor = AnchorStyles.Top | AnchorStyles.Right | AnchorStyles.Bottom | AnchorStyles.Left;
            tb.ScrollBars = ScrollBars.Both;
            Controls.Add(tb);

            Button btn = new Button();
            btn.Text = "click!";
            btn.Location = new Point(2, 2);
            btn.Size = new System.Drawing.Size(100, 50);
            Controls.Add(btn);
            btn.Click += new EventHandler((object sender, EventArgs e)=> {

                //typeofだと型を直接渡す。静的
                //MemberInfo[] mi = typeof(String).GetMembers(
                //GetTypeだとインスタンスから情報取得可能。動的
                System.String s = "Hello World";
                MemberInfo[] mi = s.GetType().GetMembers(
                    BindingFlags.Public | 
                    BindingFlags.NonPublic | 
                    BindingFlags.Instance | 
                    BindingFlags.Static | 
                    BindingFlags.FlattenHierarchy);

                string buff = "";
                foreach (MemberInfo m in mi)
                {
                    buff = m.Module + ": " + m.ReflectedType + ": " + m.MemberType + ": " + m.Name + "\r\n" + buff;
                }
                tb.Text = buff;
            });
        }
    }
}

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Drawing;

using System.Windows.Forms;

using System.Reflection;

namespace インスタンスとクラスでのメンバ

{

public partial class Form1 : Form

{

public Form1()

{

InitializeComponent();

this.Width = 800;

this.Height = 600;

TextBox tb = new TextBox();

tb.Location = new Point(2, 57);

tb.Multiline = true;

tb.Size = new System.Drawing.Size(700, 445);

tb.Anchor = AnchorStyles.Top | AnchorStyles.Right | AnchorStyles.Bottom | AnchorStyles.Left;

tb.ScrollBars = ScrollBars.Both;

Controls.Add(tb);

Button btn = new Button();

btn.Text = "click!";

btn.Location = new Point(2, 2);

btn.Size = new System.Drawing.Size(100, 50);

Controls.Add(btn);

btn.Click += new EventHandler((object sender, EventArgs e)=> {

//typeofだと型を直接渡す。静的

//MemberInfo[] mi = typeof(String).GetMembers(

//GetTypeだとインスタンスから情報取得可能。動的

System.String s = "Hello World";

MemberInfo[] mi = s.GetType().GetMembers(

BindingFlags.Public |

BindingFlags.NonPublic |

BindingFlags.Instance |

BindingFlags.Static |

BindingFlags.FlattenHierarchy);

string buff = "";

foreach (MemberInfo m in mi)

{

buff = m.Module + ": " + m.ReflectedType + ": " + m.MemberType + ": " + m.Name + "\r\n" + buff;

}

tb.Text = buff;

});

}

2017-01-112017-01-17

ds.min.js

<!DOCTYPE html>
<meta charset="utf-8">
<style>

body {
  margin: 0;
  background: #222;
  min-width: 960px;
}

rect {
  fill: none;
  pointer-events: all;
}

circle {
  fill: none;
  stroke-width: 2.5px;
}

</style>
<body>
<script src="http://d3js.org/d3.v3.min.js"></script>
<script>window.d3 || document.write('<script src="d3.min.js"><\/script>')</script>
<script>

var width = Math.max(960, innerWidth),
    height = Math.max(500, innerHeight);

var i = 0;

var svg = d3.select("body").append("svg")
    .attr("width", width)
    .attr("height", height);

svg.append("rect")
    .attr("width", width)
    .attr("height", height)
    .on("ontouchstart" in document ? "touchmove" : "mousemove", particle);

function particle() {
  var m = d3.mouse(this);

  svg.insert("circle", "rect")
      .attr("cx", m[0])
      .attr("cy", m[1])
      .attr("r", 1e-6)
      .style("stroke", d3.hsl((i = (i + 1) % 360), 1, .5))
      .style("stroke-opacity", 1)
    .transition()
      .duration(2000)
      .ease(Math.sqrt)
      .attr("r", 100)
      .style("stroke-opacity", 1e-6)
      .remove();

  d3.event.preventDefault();
}

</script>

<!DOCTYPE html>

<style>

body {

margin: 0;

background: #222;

min-width: 960px;

}

rect {

fill: none;

pointer-events: all;

}

circle {

fill: none;

stroke-width: 2.5px;

}

</style>

<body>

var width = Math.max(960, innerWidth),

height = Math.max(500, innerHeight);

var i = 0;

var svg = d3.select("body").append("svg")

.attr("width", width)

.attr("height", height);

svg.append("rect")

.attr("width", width)

.attr("height", height)

.on("ontouchstart" in document ? "touchmove" : "mousemove", particle);

function particle() {

var m = d3.mouse(this);

svg.insert("circle", "rect")

.attr("cx", m[0])

.attr("cy", m[1])

.attr("r", 1e-6)

.style("stroke", d3.hsl((i = (i + 1) % 360), 1, .5))

.style("stroke-opacity", 1)

.transition()

.duration(2000)

.ease(Math.sqrt)

.attr("r", 100)

.style("stroke-opacity", 1e-6)

.remove();

d3.event.preventDefault();

}

</script>

2017-01-112017-01-13

C# Webサーバー

サーバ１

using System;
using System.Windows.Forms;

namespace tcp_listen_client
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
            Control.CheckForIllegalCrossThreadCalls = false;
            //スレッドからテキストボックスにアクセスすることを許可

            //スレッドセーフとはアプリをマルチスレッドで走らせても問題ないこと。
            //Formはスレッドセーフではない。
            //通常、Control はメンバの参照時にUIスレッド(Application.Run したスレッド)で操作することを強制。
            //CheckForIllegalCrossThreadCallsをfalseにすると、これを回避できる。
        }

        private System.Net.Sockets.TcpListener server;//リスナー接続待ちや受信等を行うオブジェクト
        private System.Threading.Thread ListeningCallbackThred;//接続待ちスレッド
        private volatile bool SLTAlive;//接続待ちスレッド終了指示フラグ
        
        //volatile
        //複数のスレッドによって変更される可能性がある場合指定。
        //シングルスレット前提の最適化をしない

        private void Form1_Load(object sender, EventArgs e)
        {
            this.Text = "サーバー";
            button1.Text = "サーバー開始";
            button2.Text = "サーバー終了";
            label1.Text = "";

            SLTAlive = false;
            //スレッド終了指示フラグを終了に初期化
        }

        private void Form1_FormClosed(object sender, FormClosedEventArgs e)
        {
            //サーバーを終了するのに、接続待ちスレッドが終了していない場合
            if (SLTAlive)
            {
                SLTAlive = false;
                server.Stop();
                ListeningCallbackThred = null;
                //スレッドをnull設定
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            //接続待ちスレッドが生成されていない場合
            if (!SLTAlive)
            {
                ListeningCallbackThred = new System.Threading.Thread(ListeningCallback);
                //ListeningCallbackはメソッド
                ListeningCallbackThred.Start();
                SLTAlive = true;
                //未終了に設定
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            if (SLTAlive)
            {
                if (server != null)
                {
                    server.Stop();
                }
            }
            SLTAlive = false;//終了に設定
            label1.Text = "サーバー終了";
        }

        private void ListeningCallback()
        {
            //リスナーを生成
            server = new System.Net.Sockets.TcpListener(System.Net.IPAddress.Parse("192.168.102.9"),9000);
            server.Start();
            label1.Text = "サーバー開始";

            try
            {
                //受信は無限ループ。終了指示があればループ終了
                while (SLTAlive)
                {
                    //受信接続キューに接続待ちがあるかどうか
                    if(server.Pending() == true)
                    {
                        //クライアントからの接続を受け付ける。
                        //通信ストリームの取得
                        System.Net.Sockets.TcpClient ClientSocket = server.AcceptTcpClient();
                        System.Net.Sockets.NetworkStream stream = ClientSocket.GetStream();
                        byte[] ReceiveData = new byte[2000];
                        int DataLength = stream.Read(ReceiveData,0,ReceiveData.Length);

                        // textBox1.Text = ReceiveData.ToString();　こうすると
                        // System.Byte[]と表示される。つまりバイト型の配列になっているということか。

                        //string str = System.Text.Encoding.ASCII.GetString(ReceiveData, 0, DataLength);
                        string str = System.Text.Encoding.GetEncoding("shift_jis").GetString(ReceiveData,0,DataLength);
                        
                        //GETのパラメータに日本語を入れてリクエストした場合、
                        //ASCIIだと??となる。
                        //UTF-8だと文字化けする。
                        //shift_jisでちゃんと表示された。

                        //str = Uri.UnescapeDataString(str);
                        // String <- byte[] GetString
                        // String -> byte[] GetByte

                        textBox1.Text = str;

                        //返信
                        string html = "<a href=\"http://yahoo.co.jp\">hello</a>";
                        byte[] SendBuffer = System.Text.Encoding.ASCII.GetBytes(html);
                        //ASCIの部分をUnicodeにするとブラウザでダウロードしてしまう。

                        stream.Write(SendBuffer, 0, SendBuffer.Length);
                        //stream.Flush();

                        ClientSocket.Close();
                    }
                    //短時間だけ待機
                    System.Threading.Thread.Sleep(100);
                }
            }
            catch(Exception e)
            {
                label1.Text = "サーバー終了";
            }
        }
    }
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

using System;

using System.Windows.Forms;

namespace tcp_listen_client

{

public partial class Form1 : Form

{

public Form1()

{

InitializeComponent();

Control.CheckForIllegalCrossThreadCalls = false;

//スレッドからテキストボックスにアクセスすることを許可

//スレッドセーフとはアプリをマルチスレッドで走らせても問題ないこと。

//Formはスレッドセーフではない。

//通常、Control はメンバの参照時にUIスレッド(Application.Run したスレッド)で操作することを強制。

//CheckForIllegalCrossThreadCallsをfalseにすると、これを回避できる。

}

private System.Net.Sockets.TcpListener server;//リスナー接続待ちや受信等を行うオブジェクト

private System.Threading.Thread ListeningCallbackThred;//接続待ちスレッド

private volatile bool SLTAlive;//接続待ちスレッド終了指示フラグ

//volatile

//複数のスレッドによって変更される可能性がある場合指定。

//シングルスレット前提の最適化をしない

private void Form1_Load(object sender, EventArgs e)

{

this.Text = "サーバー";

button1.Text = "サーバー開始";

button2.Text = "サーバー終了";

label1.Text = "";

SLTAlive = false;

//スレッド終了指示フラグを終了に初期化

}

private void Form1_FormClosed(object sender, FormClosedEventArgs e)

{

//サーバーを終了するのに、接続待ちスレッドが終了していない場合

if (SLTAlive)

{

SLTAlive = false;

server.Stop();

ListeningCallbackThred = null;

//スレッドをnull設定

}

private void button1_Click(object sender, EventArgs e)

{

//接続待ちスレッドが生成されていない場合

if (!SLTAlive)

{

ListeningCallbackThred = new System.Threading.Thread(ListeningCallback);

//ListeningCallbackはメソッド

ListeningCallbackThred.Start();

SLTAlive = true;

//未終了に設定

}

private void button2_Click(object sender, EventArgs e)

{

if (SLTAlive)

{

if (server != null)

{

server.Stop();

}

SLTAlive = false;//終了に設定

label1.Text = "サーバー終了";

}

private void ListeningCallback()

{

//リスナーを生成

server = new System.Net.Sockets.TcpListener(System.Net.IPAddress.Parse("192.168.102.9"),9000);

server.Start();

label1.Text = "サーバー開始";

try

{

//受信は無限ループ。終了指示があればループ終了

while (SLTAlive)

{

//受信接続キューに接続待ちがあるかどうか

if(server.Pending() == true)

{

//クライアントからの接続を受け付ける。

//通信ストリームの取得

System.Net.Sockets.TcpClient ClientSocket = server.AcceptTcpClient();

System.Net.Sockets.NetworkStream stream = ClientSocket.GetStream();

byte[] ReceiveData = new byte[2000];

int DataLength = stream.Read(ReceiveData,0,ReceiveData.Length);

// textBox1.Text = ReceiveData.ToString();　こうすると

// System.Byte[]と表示される。つまりバイト型の配列になっているということか。

//string str = System.Text.Encoding.ASCII.GetString(ReceiveData, 0, DataLength);

string str = System.Text.Encoding.GetEncoding("shift_jis").GetString(ReceiveData,0,DataLength);

//GETのパラメータに日本語を入れてリクエストした場合、

//ASCIIだと??となる。

//UTF-8だと文字化けする。

//shift_jisでちゃんと表示された。

//str = Uri.UnescapeDataString(str);

// String <- byte[] GetString

// String -> byte[] GetByte

textBox1.Text = str;

//返信

string html = "<a href=\"http://yahoo.co.jp\">hello</a>";

byte[] SendBuffer = System.Text.Encoding.ASCII.GetBytes(html);

//ASCIの部分をUnicodeにするとブラウザでダウロードしてしまう。

stream.Write(SendBuffer, 0, SendBuffer.Length);

//stream.Flush();

ClientSocket.Close();

}

//短時間だけ待機

System.Threading.Thread.Sleep(100);

}

catch(Exception e)

{

label1.Text = "サーバー終了";

}

サーバ２

using System;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Net;
using System.Net.Sockets;
using System.IO;
using System.Text.RegularExpressions;

namespace SimpSrv
{
    public partial class Form1 : Form
    {
        bool chk;
        TcpListener listener = null;

        public Form1()
        {
            InitializeComponent();
        }

        private async void button1_Click(object sender, EventArgs e)
        {
            if (listener != null) return;

            MessageBox.Show("起動しました。");
            this.Text = "起動中";

            IPAddress ip = IPAddress.Parse("192.168.102.9");
            listener = new TcpListener(ip, 80);

            listener.Start();
            TcpClient client = null;

            chk = true;

            while(true)
            {
                await Task.Run(() =>
                {
                    try
                    {
                        client = listener.AcceptTcpClient();
                    }
                    catch (SocketException ex)
                    {
                        System.Diagnostics.Debug.Print(ex.Message.ToString());
                    }

                });

                if (!chk) break;

                Encoding enc = Encoding.GetEncoding("shift_jis");
                string resMsg = "";
                string sendMsg = "";

                using (NetworkStream ns = client.GetStream())
                {
                    ns.WriteTimeout = 10000;
                    ns.ReadTimeout = 10000;

                    using (MemoryStream ms = new MemoryStream())
                    {
                        byte[] resByte = new byte[1024];
                        int resSize = 0;
                        
                        do
                        {
                            try
                            {
                                resSize = ns.Read(resByte, 0, resByte.Length);
                            }
                            catch (IOException ex)
                            {
                                System.Diagnostics.Debug.Print(ex.Message.ToString());
                                break;
                            }

                            if (resSize == 0) break;
                            ms.Write(resByte, 0, resSize);

                        } while (ns.DataAvailable);

                        resMsg = enc.GetString(ms.GetBuffer(), 0, (int)ms.Length);
                        ms.Close();
                    }

                    Regex reg = new Regex(@"\/\?name=([a-z]+)&id=([0-9]{6})&p=([0-9]{2})\s");
                    Match match = reg.Match(resMsg);

                    if (match.Success)
                    {
                        sendMsg = "\n\n" + Html.MakeHtml(match.Groups[1].ToString(),match.Groups[2].ToString(),match.Groups[3].ToString());
                    }
                    else
                    {
                        sendMsg = "\n\n" + "false";
                    }

                    byte[] sendBytes = enc.GetBytes(sendMsg);

                    try
                    {
                        ns.Write(sendBytes, 0, sendBytes.Length);
                    }
                    catch (IOException ex)
                    {
                        System.Diagnostics.Debug.Print(ex.Message.ToString());
                    }
                    ns.Close();
                }
                client.Close();
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            if (listener == null) return;

            MessageBox.Show("終了しました。");
            this.Text = "停止中";

            listener.Stop();
            listener = null;

            chk = false;
        }
    }
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

using System;

using System.Text;

using System.Threading.Tasks;

using System.Windows.Forms;

using System.Net;

using System.Net.Sockets;

using System.IO;

using System.Text.RegularExpressions;

namespace SimpSrv

{

public partial class Form1 : Form

{

bool chk;

TcpListener listener = null;

public Form1()

{

InitializeComponent();

}

private async void button1_Click(object sender, EventArgs e)

{

if (listener != null) return;

MessageBox.Show("起動しました。");

this.Text = "起動中";

IPAddress ip = IPAddress.Parse("192.168.102.9");

listener = new TcpListener(ip, 80);

listener.Start();

TcpClient client = null;

chk = true;

while(true)

{

await Task.Run(() =>

{

try

{

client = listener.AcceptTcpClient();

}

catch (SocketException ex)

{

System.Diagnostics.Debug.Print(ex.Message.ToString());

}

});

if (!chk) break;

Encoding enc = Encoding.GetEncoding("shift_jis");

string resMsg = "";

string sendMsg = "";

using (NetworkStream ns = client.GetStream())

{

ns.WriteTimeout = 10000;

ns.ReadTimeout = 10000;

using (MemoryStream ms = new MemoryStream())

{

byte[] resByte = new byte[1024];

int resSize = 0;

{

try

{

resSize = ns.Read(resByte, 0, resByte.Length);

}

catch (IOException ex)

{

System.Diagnostics.Debug.Print(ex.Message.ToString());

break;

}

if (resSize == 0) break;

ms.Write(resByte, 0, resSize);

} while (ns.DataAvailable);

resMsg = enc.GetString(ms.GetBuffer(), 0, (int)ms.Length);

ms.Close();

}

Regex reg = new Regex(@"\/\?name=([a-z]+)&id=([0-9]{6})&p=([0-9]{2})\s");

Match match = reg.Match(resMsg);

if (match.Success)

{

sendMsg = "\n\n" + Html.MakeHtml(match.Groups[1].ToString(),match.Groups[2].ToString(),match.Groups[3].ToString());

}

else

{

sendMsg = "\n\n" + "false";

}

byte[] sendBytes = enc.GetBytes(sendMsg);

try

{

ns.Write(sendBytes, 0, sendBytes.Length);

}

catch (IOException ex)

{

System.Diagnostics.Debug.Print(ex.Message.ToString());

}

ns.Close();

}

client.Close();

}

private void button2_Click(object sender, EventArgs e)

{

if (listener == null) return;

MessageBox.Show("終了しました。");

this.Text = "停止中";

listener.Stop();

listener = null;

chk = false;

}

2017-01-102017-01-17

C# 付箋紙プログラム

以下は未使用。

Program.cs

using System;
using System.Windows.Forms;

namespace sticker_notes
{
    static class Program
    {
        /// <summary>
        /// アプリケーションのメイン エントリ ポイントです。
        /// </summary>
        [STAThread]
        static void Main()
        {
            //Application.EnableVisualStyles();
            //Application.SetCompatibleTextRenderingDefault(false);

            NotifyIcon ni = new System.Windows.Forms.NotifyIcon();
            ni.Visible = true;
            ni.Icon = new System.Drawing.Icon(@"C:\Program Files\Windows Live\Writer\writer.ico");//適当なico

            ni.Click += new EventHandler((object sender, EventArgs e) => {
                Form1 f = new Form1();
                f.Show();
            });

            ni.DoubleClick += new EventHandler((object sender, EventArgs e)=> {
                Application.Exit();
            });

            Application.Run();

        }
    }
}

using System;

using System.Windows.Forms;

namespace sticker_notes

{

static class Program

{

/// <summary>

/// アプリケーションのメインエントリポイントです。

/// </summary>

[STAThread]

static void Main()

{

//Application.EnableVisualStyles();

//Application.SetCompatibleTextRenderingDefault(false);

NotifyIcon ni = new System.Windows.Forms.NotifyIcon();

ni.Visible = true;

ni.Icon = new System.Drawing.Icon(@"C:\Program Files\Windows Live\Writer\writer.ico");//適当なico

ni.Click += new EventHandler((object sender, EventArgs e) => {

Form1 f = new Form1();

f.Show();

});

ni.DoubleClick += new EventHandler((object sender, EventArgs e)=> {

Application.Exit();

});

Application.Run();

}

Form1.cs

using System;
using System.Drawing;
using System.Windows.Forms;

namespace sticker_notes
{
    public partial class Form1 : Form
    {
        int mouseX;
        int mouseY;

        int mouseWith;
        int mouseHeight;

        Boolean FgWith = false;
        Boolean FgHeight = false;

        public Form1()
        {
            InitializeComponent();
            this.FormBorderStyle = FormBorderStyle.None;
            //バーは消えるがサイズ変更できず。

            //this.ControlBox = false;
            //this.Text = "";
            //サイズ変更できるが境界線が消せない。

            this.Opacity = 0.9;
            this.TopMost = true;

            TextBox tb = new System.Windows.Forms.TextBox();
            tb.Dock = DockStyle.Fill;
            tb.Multiline = true;
            tb.Font = new Font("", 12);
            tb.BackColor = Color.Beige;
            this.Controls.Add(tb);

            tb.KeyDown += new KeyEventHandler(this.text_keydown);
            tb.MouseDown += new MouseEventHandler(this.mouse_down);
            tb.MouseMove += new MouseEventHandler(this.mouse_move);
            tb.MouseUp += new MouseEventHandler(this.mouse_up);

        }
        public void mouse_up(object sender, MouseEventArgs e)
        {
            FgWith = false;
            FgHeight = false;
        }

        public void mouse_down(object sender, MouseEventArgs e)
        {
            if (e.Button == MouseButtons.Left)
            {
                this.mouseX = e.X;
                this.mouseY = e.Y;
                this.mouseWith = this.Width;
                this.mouseHeight = this.Height;
            }
        }

        public void mouse_move(object sender, MouseEventArgs e)
        {
            if (e.X > this.Width - 20 && e.X < this.Width &&
                e.Y > this.Height - 20 && e.Y < this.Height && !FgWith && !FgHeight)
            {
                this.Cursor = Cursors.SizeNWSE;
                if (e.Button == MouseButtons.Left)
                {
                    FgWith = true;
                    FgHeight = true;
                }
            }
            else if (e.X > this.Width - 20 && e.X < this.Width && !FgHeight)
            {
                this.Cursor = Cursors.SizeWE;
                if (e.Button == MouseButtons.Left) FgWith = true;
            }
            else if (e.Y > this.Height - 20 && e.Y < this.Height && !FgWith)
            {
                this.Cursor = Cursors.SizeNS;
                if (e.Button == MouseButtons.Left) FgHeight = true;
            }

            if (FgWith && FgHeight && e.Button == MouseButtons.Left)
            {
                this.Width = mouseWith + e.X - this.mouseX;
                this.Height = mouseHeight + e.Y - this.mouseY;
            }
            else if (FgWith && e.Button == MouseButtons.Left)
            {
                this.Width = mouseWith + e.X - this.mouseX;
            }
            else if (FgHeight && e.Button == MouseButtons.Left)
            {
                this.Height = mouseHeight + e.Y - this.mouseY;
            }
            else if (!FgWith && !FgHeight && e.Button == MouseButtons.Left)
            {
                this.Left += e.X - this.mouseX;
                this.Top += e.Y - this.mouseY;
            }

        }
        public void text_keydown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Escape) this.Close();
            if (e.Control && e.KeyCode == Keys.A) (sender as TextBox).SelectAll();
        }
    }
}

100

101

102

103

104

105

106

107

108

109

110

using System;

using System.Drawing;

using System.Windows.Forms;

namespace sticker_notes

{

public partial class Form1 : Form

{

int mouseX;

int mouseY;

int mouseWith;

int mouseHeight;

Boolean FgWith = false;

Boolean FgHeight = false;

public Form1()

{

InitializeComponent();

this.FormBorderStyle = FormBorderStyle.None;

//バーは消えるがサイズ変更できず。

//this.ControlBox = false;

//this.Text = "";

//サイズ変更できるが境界線が消せない。

this.Opacity = 0.9;

this.TopMost = true;

TextBox tb = new System.Windows.Forms.TextBox();

tb.Dock = DockStyle.Fill;

tb.Multiline = true;

tb.Font = new Font("", 12);

tb.BackColor = Color.Beige;

this.Controls.Add(tb);

tb.KeyDown += new KeyEventHandler(this.text_keydown);

tb.MouseDown += new MouseEventHandler(this.mouse_down);

tb.MouseMove += new MouseEventHandler(this.mouse_move);

tb.MouseUp += new MouseEventHandler(this.mouse_up);

}

public void mouse_up(object sender, MouseEventArgs e)

{

FgWith = false;

FgHeight = false;

}

public void mouse_down(object sender, MouseEventArgs e)

{

if (e.Button == MouseButtons.Left)

{

this.mouseX = e.X;

this.mouseY = e.Y;

this.mouseWith = this.Width;

this.mouseHeight = this.Height;

}

public void mouse_move(object sender, MouseEventArgs e)

{

if (e.X > this.Width - 20 && e.X < this.Width &&

e.Y > this.Height - 20 && e.Y < this.Height && !FgWith && !FgHeight)

{

this.Cursor = Cursors.SizeNWSE;

if (e.Button == MouseButtons.Left)

{

FgWith = true;

FgHeight = true;

}

else if (e.X > this.Width - 20 && e.X < this.Width && !FgHeight)

{

this.Cursor = Cursors.SizeWE;

if (e.Button == MouseButtons.Left) FgWith = true;

}

else if (e.Y > this.Height - 20 && e.Y < this.Height && !FgWith)

{

this.Cursor = Cursors.SizeNS;

if (e.Button == MouseButtons.Left) FgHeight = true;

}

if (FgWith && FgHeight && e.Button == MouseButtons.Left)

{

this.Width = mouseWith + e.X - this.mouseX;

this.Height = mouseHeight + e.Y - this.mouseY;

}

else if (FgWith && e.Button == MouseButtons.Left)

{

this.Width = mouseWith + e.X - this.mouseX;

}

else if (FgHeight && e.Button == MouseButtons.Left)

{

this.Height = mouseHeight + e.Y - this.mouseY;

}

else if (!FgWith && !FgHeight && e.Button == MouseButtons.Left)

{

this.Left += e.X - this.mouseX;

this.Top += e.Y - this.mouseY;

}

public void text_keydown(object sender, KeyEventArgs e)

{

if (e.KeyCode == Keys.Escape) this.Close();

if (e.Control && e.KeyCode == Keys.A) (sender as TextBox).SelectAll();

}

画像の場合

using System.Windows.Forms;

namespace sticker_notes
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
            this.FormBorderStyle = FormBorderStyle.None;

            PictureBox tb = new PictureBox();
            tb.ImageLocation = Application.StartupPath + @"\1.jpg";
            tb.Dock = DockStyle.Fill;
            tb.SizeMode = PictureBoxSizeMode.Zoom;
            this.Controls.Add(tb);
        }
    }
}

using System.Windows.Forms;

namespace sticker_notes

{

public partial class Form1 : Form

{

public Form1()

{

InitializeComponent();

this.FormBorderStyle = FormBorderStyle.None;

PictureBox tb = new PictureBox();

tb.ImageLocation = Application.StartupPath + @"\1.jpg";

tb.Dock = DockStyle.Fill;

tb.SizeMode = PictureBoxSizeMode.Zoom;

this.Controls.Add(tb);

}

ここまで未使用。

サイズ変更の作りがおかしかったので、以下、修正版。画像を表示しておくため位置をxmlにシリアライズ。

Progam.cs

using System;
using System.Windows.Forms;

namespace sticker_notes
{
    static class Program
    {
        /// <summary>
        /// アプリケーションのメイン エントリ ポイントです。
        /// </summary>
        [STAThread]
        static void Main()
        {
            Application.EnableVisualStyles();
            Application.SetCompatibleTextRenderingDefault(false);

            NotifyIcon ni = new System.Windows.Forms.NotifyIcon();
            ni.Visible = true;
            ni.Icon = new System.Drawing.Icon(Application.StartupPath + @"\i.ico");
            ni.DoubleClick += new EventHandler((object sender, EventArgs e) => {
                Application.Exit();
            });

            Form1 f = new Form1();
            f.Show();

            Application.Run();
        }
    }
}

using System;

using System.Windows.Forms;

namespace sticker_notes

{

static class Program

{

/// <summary>

/// アプリケーションのメインエントリポイントです。

/// </summary>

[STAThread]

static void Main()

{

Application.EnableVisualStyles();

Application.SetCompatibleTextRenderingDefault(false);

NotifyIcon ni = new System.Windows.Forms.NotifyIcon();

ni.Visible = true;

ni.Icon = new System.Drawing.Icon(Application.StartupPath + @"\i.ico");

ni.DoubleClick += new EventHandler((object sender, EventArgs e) => {

Application.Exit();

});

Form1 f = new Form1();

f.Show();

Application.Run();

}

Form1.cs

using System;
using System.Drawing;
using System.Windows.Forms;

namespace sticker_notes
{
    public partial class Form1 : Form
    {
        int MouseX;
        int MouseY;
        int ThisW;
        int ThisH;
        Boolean FW = false;
        Boolean FH = false;

        public Form1()
        {
            InitializeComponent();
            this.FormBorderStyle = FormBorderStyle.None;
            this.Opacity = 0.9;
            this.TopMost = false;
            this.ShowInTaskbar = false;

            PictureBox pb = new PictureBox();
            pb.Dock = DockStyle.Fill;
            pb.ImageLocation = Application.StartupPath + @"\1.jpg";
            pb.SizeMode = PictureBoxSizeMode.Zoom;
            this.Controls.Add(pb);

            pb.MouseUp += new MouseEventHandler(this.mouse_up);
            pb.MouseDown += new MouseEventHandler(this.mouse_down);
            pb.MouseMove += new MouseEventHandler(this.mouse_move);

            StartPosition = FormStartPosition.Manual;

            try
            {
                System.Xml.Serialization.XmlSerializer xs = new System.Xml.Serialization.XmlSerializer(typeof(int[]));
                using (System.IO.StreamReader sr = new System.IO.StreamReader(Application.StartupPath + @"\config", new System.Text.UTF8Encoding(false)))
                {
                    int[] i = (int[])xs.Deserialize(sr);
                    this.Width = i[0];
                    this.Height = i[1];
                    this.Top = i[2];
                    this.Left = i[3];
                    sr.Close();
                }
            }
            catch (System.IO.FileNotFoundException e)
            {

            }
        }
        public void mouse_up(object sender, MouseEventArgs e)
        {
            FW = false;
            FH = false;

            System.Xml.Serialization.XmlSerializer xs = new System.Xml.Serialization.XmlSerializer(typeof(int[]));
            using (System.IO.StreamWriter sw = new System.IO.StreamWriter(Application.StartupPath + @"\config",false,new System.Text.UTF8Encoding(false)))
            {
                xs.Serialize(sw, new int[] { this.Width, this.Height, this.Top, this.Left });
                sw.Close();
            }
        }
        public void mouse_down(object sender, MouseEventArgs e)
        {
            if (e.Button != MouseButtons.Left) return;

            this.MouseX = e.X;
            this.MouseY = e.Y;
            this.ThisW = this.Width;
            this.ThisH = this.Height;

            if (e.X > this.Width - 20 && e.X < this.Width && e.Y > this.Height - 20 && e.Y < this.Height)
            {
                FW = true;
                FH = true;
            }
            else if (e.X > this.Width - 20 && e.X < this.Width)
            {
                FW = true;
            }
            else if (e.Y > this.Height - 20 && e.Y < this.Height)
            {
                FH = true;
            }
        }

        public void mouse_move(object sender, MouseEventArgs e)
        {
            if (e.X > this.Width - 20 && e.X < this.Width && e.Y > this.Height - 20 && e.Y < this.Height)
            {
                this.Cursor = Cursors.SizeNWSE;
            }
            else if (e.X > this.Width - 20 && e.X < this.Width)
            {
                this.Cursor = Cursors.SizeWE;
            }
            else if (e.Y > this.Height - 20 && e.Y < this.Height)
            {
                this.Cursor = Cursors.SizeNS;
            }
            else
            {
                this.Cursor = Cursors.Default;
            }
             
            if (FW && FH)
            {
                this.Width = this.ThisW + e.X - this.MouseX;
                this.Height = this.ThisH + e.Y - this.MouseY;
            }
            else if (FW)
            {
                this.Width = ThisW + e.X - this.MouseX;
            }
            else if (FH)
            {
                this.Height = ThisH + e.Y - this.MouseY;
            }

            if (!FW && !FH && e.Button == MouseButtons.Left)
            {
                this.Left += e.X - this.MouseX;
                this.Top += e.Y - this.MouseY;
            }
        }
    }
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

using System;

using System.Drawing;

using System.Windows.Forms;

namespace sticker_notes

{

public partial class Form1 : Form

{

int MouseX;

int MouseY;

int ThisW;

int ThisH;

Boolean FW = false;

Boolean FH = false;

public Form1()

{

InitializeComponent();

this.FormBorderStyle = FormBorderStyle.None;

this.Opacity = 0.9;

this.TopMost = false;

this.ShowInTaskbar = false;

PictureBox pb = new PictureBox();

pb.Dock = DockStyle.Fill;

pb.ImageLocation = Application.StartupPath + @"\1.jpg";

pb.SizeMode = PictureBoxSizeMode.Zoom;

this.Controls.Add(pb);

pb.MouseUp += new MouseEventHandler(this.mouse_up);

pb.MouseDown += new MouseEventHandler(this.mouse_down);

pb.MouseMove += new MouseEventHandler(this.mouse_move);

StartPosition = FormStartPosition.Manual;

try

{

System.Xml.Serialization.XmlSerializer xs = new System.Xml.Serialization.XmlSerializer(typeof(int[]));

using (System.IO.StreamReader sr = new System.IO.StreamReader(Application.StartupPath + @"\config", new System.Text.UTF8Encoding(false)))

{

int[] i = (int[])xs.Deserialize(sr);

this.Width = i[0];

this.Height = i[1];

this.Top = i[2];

this.Left = i[3];

sr.Close();

}

catch (System.IO.FileNotFoundException e)

{

}

public void mouse_up(object sender, MouseEventArgs e)

{

FW = false;

FH = false;

System.Xml.Serialization.XmlSerializer xs = new System.Xml.Serialization.XmlSerializer(typeof(int[]));

using (System.IO.StreamWriter sw = new System.IO.StreamWriter(Application.StartupPath + @"\config",false,new System.Text.UTF8Encoding(false)))

{

xs.Serialize(sw, new int[] { this.Width, this.Height, this.Top, this.Left });

sw.Close();

}

public void mouse_down(object sender, MouseEventArgs e)

{

if (e.Button != MouseButtons.Left) return;

this.MouseX = e.X;

this.MouseY = e.Y;

this.ThisW = this.Width;

this.ThisH = this.Height;

if (e.X > this.Width - 20 && e.X < this.Width && e.Y > this.Height - 20 && e.Y < this.Height)

{

FW = true;

FH = true;

}

else if (e.X > this.Width - 20 && e.X < this.Width)

{

FW = true;

}

else if (e.Y > this.Height - 20 && e.Y < this.Height)

{

FH = true;

}

public void mouse_move(object sender, MouseEventArgs e)

{

if (e.X > this.Width - 20 && e.X < this.Width && e.Y > this.Height - 20 && e.Y < this.Height)

{

this.Cursor = Cursors.SizeNWSE;

}

else if (e.X > this.Width - 20 && e.X < this.Width)

{

this.Cursor = Cursors.SizeWE;

}

else if (e.Y > this.Height - 20 && e.Y < this.Height)

{

this.Cursor = Cursors.SizeNS;

}

else

{

this.Cursor = Cursors.Default;

}

if (FW && FH)

{

this.Width = this.ThisW + e.X - this.MouseX;

this.Height = this.ThisH + e.Y - this.MouseY;

}

else if (FW)

{

this.Width = ThisW + e.X - this.MouseX;

}

else if (FH)

{

this.Height = ThisH + e.Y - this.MouseY;

}

if (!FW && !FH && e.Button == MouseButtons.Left)

{

this.Left += e.X - this.MouseX;

this.Top += e.Y - this.MouseY;

}

これでデスクトップに画像を表示しておくことができる。

以下はテキスト用にForm1だけさらに修正。デスクトップに表示させておいてメモとして利用
Form1.cs

using System;
using System.Drawing;
using System.Windows.Forms;

namespace sticker_notes
{
    public partial class Form1 : Form
    {
        int MouseX;
        int MouseY;
        int ThisW;
        int ThisH;
        Boolean FW = false;
        Boolean FH = false;
        Boolean Ctr = false;

        public Form1()
        {
            InitializeComponent();
            this.FormBorderStyle = FormBorderStyle.None;
            this.Opacity = 0.9;
            this.TopMost = false;
            this.ShowInTaskbar = false;
            StartPosition = FormStartPosition.Manual;

            TextBox tb = new TextBox();
            tb.Dock = DockStyle.Fill;
            tb.Multiline = true;
            tb.Font = new Font("メイリオ", 12);

            this.Controls.Add(tb);

            //tb.BackColor = Color.Transparent;
            //this.TransparencyKey = Color.White;           

            try
            {
                System.Xml.Serialization.XmlSerializer xs = new System.Xml.Serialization.XmlSerializer(typeof(int[]));
                using (System.IO.StreamReader sr = new System.IO.StreamReader(Application.StartupPath + @"\config", new System.Text.UTF8Encoding(false)))
                {
                    int[] i = (int[])xs.Deserialize(sr);
                    this.Width = i[0];
                    this.Height = i[1];
                    this.Top = i[2];
                    this.Left = i[3];
                    sr.Close();
                }

                System.Xml.XmlDocument xd = new System.Xml.XmlDocument();
                xd.PreserveWhitespace = true;
                xd.Load(Application.StartupPath + @"\body");
                xs = new System.Xml.Serialization.XmlSerializer(typeof(string));
                using (System.Xml.XmlNodeReader xnr = new System.Xml.XmlNodeReader(xd.DocumentElement))
                {
                    tb.Text = (string)xs.Deserialize(xnr);
                    xnr.Close();
                }
            }
            catch (System.IO.FileNotFoundException e) { }

            tb.MouseUp += new MouseEventHandler(this.mouse_up);
            tb.MouseDown += new MouseEventHandler(this.mouse_down);
            tb.MouseMove += new MouseEventHandler(this.mouse_move);
            tb.TextChanged += new EventHandler(this.body_save);
            tb.KeyDown += new KeyEventHandler((object sender, KeyEventArgs e) => {

                if (e.Control && e.KeyCode == Keys.A) tb.SelectAll();
                if (e.KeyCode == Keys.Escape) Application.Exit();
                if (e.Control) Ctr = true;

            });
            tb.KeyUp += new KeyEventHandler((object sender, KeyEventArgs e) => { Ctr = false; });
            tb.GotFocus += new EventHandler((object sender, EventArgs e) => { this.Opacity = 0.9; });
            tb.LostFocus += new EventHandler((object sender, EventArgs e) => { this.Opacity = 0.5; });
        }
        public void body_save(object sender, EventArgs e)
        {
            System.Xml.Serialization.XmlSerializer xs = new System.Xml.Serialization.XmlSerializer(typeof(String));
            using (System.IO.StreamWriter sw = new System.IO.StreamWriter(Application.StartupPath + @"\body", false, new System.Text.UTF8Encoding(false)))
            {
                xs.Serialize(sw, ((TextBox)sender).Text);
                sw.Close();
            }
        }
        public void mouse_up(object sender, MouseEventArgs e)
        {
            FW = false;
            FH = false;

            System.Xml.Serialization.XmlSerializer xs = new System.Xml.Serialization.XmlSerializer(typeof(int[]));
            using (System.IO.StreamWriter sw = new System.IO.StreamWriter(Application.StartupPath + @"\config",false,new System.Text.UTF8Encoding(false)))
            {
                xs.Serialize(sw, new int[] { this.Width, this.Height, this.Top, this.Left });
                sw.Close();
            }
        }
        public void mouse_down(object sender, MouseEventArgs e)
        {
            if (e.Button != MouseButtons.Left) return;

            this.MouseX = e.X;
            this.MouseY = e.Y;
            this.ThisW = this.Width;
            this.ThisH = this.Height;

            if (e.X > this.Width - 20 && e.X < this.Width && e.Y > this.Height - 20 && e.Y < this.Height)
            {
                FW = true;
                FH = true;
            }
            else if (e.X > this.Width - 20 && e.X < this.Width)
            {
                FW = true;
            }
            else if (e.Y > this.Height - 20 && e.Y < this.Height)
            {
                FH = true;
            }
        }

        public void mouse_move(object sender, MouseEventArgs e)
        {

            if (e.X > this.Width - 20 && e.X < this.Width && e.Y > this.Height - 20 && e.Y < this.Height)
            {
                this.Cursor = Cursors.SizeNWSE;
            }
            else if (e.X > this.Width - 20 && e.X < this.Width)
            {
                this.Cursor = Cursors.SizeWE;
            }
            else if (e.Y > this.Height - 20 && e.Y < this.Height)
            {
                this.Cursor = Cursors.SizeNS;
            }
             
            if (FW && FH)
            {
                this.Width = this.ThisW + e.X - this.MouseX;
                this.Height = this.ThisH + e.Y - this.MouseY;
            }
            else if (FW)
            {
                this.Width = ThisW + e.X - this.MouseX;
            }
            else if (FH)
            {
                this.Height = ThisH + e.Y - this.MouseY;
            }

            if (!FW && !FH && e.Button == MouseButtons.Left && Ctr)
            {
                this.Left += e.X - this.MouseX;
                this.Top += e.Y - this.MouseY;
            }
        }
    }
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

using System;

using System.Drawing;

using System.Windows.Forms;

namespace sticker_notes

{

public partial class Form1 : Form

{

int MouseX;

int MouseY;

int ThisW;

int ThisH;

Boolean FW = false;

Boolean FH = false;

Boolean Ctr = false;

public Form1()

{

InitializeComponent();

this.FormBorderStyle = FormBorderStyle.None;

this.Opacity = 0.9;

this.TopMost = false;

this.ShowInTaskbar = false;

StartPosition = FormStartPosition.Manual;

TextBox tb = new TextBox();

tb.Dock = DockStyle.Fill;

tb.Multiline = true;

tb.Font = new Font("メイリオ", 12);

this.Controls.Add(tb);

//tb.BackColor = Color.Transparent;

//this.TransparencyKey = Color.White;

try

{

System.Xml.Serialization.XmlSerializer xs = new System.Xml.Serialization.XmlSerializer(typeof(int[]));

using (System.IO.StreamReader sr = new System.IO.StreamReader(Application.StartupPath + @"\config", new System.Text.UTF8Encoding(false)))

{

int[] i = (int[])xs.Deserialize(sr);

this.Width = i[0];

this.Height = i[1];

this.Top = i[2];

this.Left = i[3];

sr.Close();

}

System.Xml.XmlDocument xd = new System.Xml.XmlDocument();

xd.PreserveWhitespace = true;

xd.Load(Application.StartupPath + @"\body");

xs = new System.Xml.Serialization.XmlSerializer(typeof(string));

using (System.Xml.XmlNodeReader xnr = new System.Xml.XmlNodeReader(xd.DocumentElement))

{

tb.Text = (string)xs.Deserialize(xnr);

xnr.Close();

}

catch (System.IO.FileNotFoundException e) { }

tb.MouseUp += new MouseEventHandler(this.mouse_up);

tb.MouseDown += new MouseEventHandler(this.mouse_down);

tb.MouseMove += new MouseEventHandler(this.mouse_move);

tb.TextChanged += new EventHandler(this.body_save);

tb.KeyDown += new KeyEventHandler((object sender, KeyEventArgs e) => {

if (e.Control && e.KeyCode == Keys.A) tb.SelectAll();

if (e.KeyCode == Keys.Escape) Application.Exit();

if (e.Control) Ctr = true;

});

tb.KeyUp += new KeyEventHandler((object sender, KeyEventArgs e) => { Ctr = false; });

tb.GotFocus += new EventHandler((object sender, EventArgs e) => { this.Opacity = 0.9; });

tb.LostFocus += new EventHandler((object sender, EventArgs e) => { this.Opacity = 0.5; });

}

public void body_save(object sender, EventArgs e)

{

System.Xml.Serialization.XmlSerializer xs = new System.Xml.Serialization.XmlSerializer(typeof(String));

using (System.IO.StreamWriter sw = new System.IO.StreamWriter(Application.StartupPath + @"\body", false, new System.Text.UTF8Encoding(false)))

{

xs.Serialize(sw, ((TextBox)sender).Text);

sw.Close();

}

public void mouse_up(object sender, MouseEventArgs e)

{

FW = false;

FH = false;

System.Xml.Serialization.XmlSerializer xs = new System.Xml.Serialization.XmlSerializer(typeof(int[]));

using (System.IO.StreamWriter sw = new System.IO.StreamWriter(Application.StartupPath + @"\config",false,new System.Text.UTF8Encoding(false)))

{

xs.Serialize(sw, new int[] { this.Width, this.Height, this.Top, this.Left });

sw.Close();

}

public void mouse_down(object sender, MouseEventArgs e)

{

if (e.Button != MouseButtons.Left) return;

this.MouseX = e.X;

this.MouseY = e.Y;

this.ThisW = this.Width;

this.ThisH = this.Height;

if (e.X > this.Width - 20 && e.X < this.Width && e.Y > this.Height - 20 && e.Y < this.Height)

{

FW = true;

FH = true;

}

else if (e.X > this.Width - 20 && e.X < this.Width)

{

FW = true;

}

else if (e.Y > this.Height - 20 && e.Y < this.Height)

{

FH = true;

}

public void mouse_move(object sender, MouseEventArgs e)

{

if (e.X > this.Width - 20 && e.X < this.Width && e.Y > this.Height - 20 && e.Y < this.Height)

{

this.Cursor = Cursors.SizeNWSE;

}

else if (e.X > this.Width - 20 && e.X < this.Width)

{

this.Cursor = Cursors.SizeWE;

}

else if (e.Y > this.Height - 20 && e.Y < this.Height)

{

this.Cursor = Cursors.SizeNS;

}

if (FW && FH)

{

this.Width = this.ThisW + e.X - this.MouseX;

this.Height = this.ThisH + e.Y - this.MouseY;

}

else if (FW)

{

this.Width = ThisW + e.X - this.MouseX;

}

else if (FH)

{

this.Height = ThisH + e.Y - this.MouseY;

}

if (!FW && !FH && e.Button == MouseButtons.Left && Ctr)

{

this.Left += e.X - this.MouseX;

this.Top += e.Y - this.MouseY;

}

2017-01-082020-05-05

Androidで取得した値をメールで確認

Androidで自動で値を収集して、それをレンサバにPOSTして、レンサバから自動でメール送信。

レンタルサーバーの設定

エックスサーバーのcronにスクリプトをフルパスで追加。
/home/xxx/xxx/script
のような感じにして、scriptのパーミッションは701に設定。

スクリプトの実体
tmp=｀cat /xxx/xxx.txt｀
echo “$tmp” | mail -s “title” mail@xxx.com
echo “” > /xxx/xxx.txt

これで、xxx.txtの値を定期送信する。

xxx.txtを作成するためのスクリプトは以下

<?php
class App{
  private function main(){
    if(array_key_exists("key",$_POST) && array_key_exists("lux", $_POST) &&
    $_POST["key"] == "xxx")
    {
      $tmp = file_get_contents("xxx.txt");
      file_put_contents("xxx.txt",$_POST["lux"]."\n".$tmp);
    }
  }
  public function __construct(){
    $this->main();
  }
}
new App;

<?php

class App{

private function main(){

if(array_key_exists("key",$_POST) && array_key_exists("lux", $_POST) &&

$_POST["key"] == "xxx")

{

$tmp = file_get_contents("xxx.txt");

file_put_contents("xxx.txt",$_POST["lux"]."\n".$tmp);

}

public function __construct(){

$this->main();

}

new App;

ここでは単純POSTを受け取ったら作成する。

送信するのはAndroid

package com.example.sokamura.myapplication;

import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import android.hardware.Sensor;
import android.hardware.SensorEvent;
import android.hardware.SensorEventListener;
import android.hardware.SensorManager;
import android.widget.TextView;

import java.util.Timer;
import java.util.TimerTask;

public class MainActivity extends AppCompatActivity implements SensorEventListener {

    SensorManager sm;
    TextView tv;
    MainActivity ma;
    String body = "";

    @Override
    protected void onCreate(Bundle savedInstanceState) {

        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        tv = (TextView)findViewById(R.id.t);
        sm = (SensorManager)getSystemService(SENSOR_SERVICE);
        ma = this;

        Timer timer = new Timer();
        timer.scheduleAtFixedRate( new TimerTask(){
            @Override
            public void run(){
                SubClass s = new SubClass(ma);
                s.execute(body);
                body = "";
            }
        },0,1000*60*60*1);//1時間

    }

    @Override
    protected void onStop() {
        super.onStop();
        //sm.unregisterListener(this);
    }

    @Override
    protected void onResume() {
        super.onResume();
        List<Sensor> sensors = sm.getSensorList(Sensor.TYPE_LIGHT);
        if(sensors.size() > 0) {
            Sensor s = sensors.get(0);
            sm.registerListener(this, s, SensorManager.SENSOR_DELAY_UI);
        }
    }

    @Override
    public void onAccuracyChanged(Sensor sensor, int accuracy) {}

    @Override
    public void onSensorChanged(SensorEvent event) {
        if(event.sensor.getType() == Sensor.TYPE_LIGHT) {
            String s = new SimpleDateFormat("MM/dd HH:mm:ss").format(new Date()) + " " +
                    String.format("%.2f", event.values[0]);
            tv.setText(s);
            body = s + "\n" + body;
        }
    }
}

package com.example.sokamura.myapplication;

import android.support.v7.app.AppCompatActivity;

import android.os.Bundle;

import java.text.SimpleDateFormat;

import java.util.Date;

import java.util.List;

import android.hardware.Sensor;

import android.hardware.SensorEvent;

import android.hardware.SensorEventListener;

import android.hardware.SensorManager;

import android.widget.TextView;

import java.util.Timer;

import java.util.TimerTask;

public class MainActivity extends AppCompatActivity implements SensorEventListener {

SensorManager sm;

TextView tv;

MainActivity ma;

String body = "";

@Override

protected void onCreate(Bundle savedInstanceState) {

super.onCreate(savedInstanceState);

setContentView(R.layout.activity_main);

tv = (TextView)findViewById(R.id.t);

sm = (SensorManager)getSystemService(SENSOR_SERVICE);

ma = this;

Timer timer = new Timer();

timer.scheduleAtFixedRate( new TimerTask(){

@Override

public void run(){

SubClass s = new SubClass(ma);

s.execute(body);

body = "";

}

},0,1000*60*60*1);//1時間

}

@Override

protected void onStop() {

super.onStop();

//sm.unregisterListener(this);

}

@Override

protected void onResume() {

super.onResume();

List<Sensor> sensors = sm.getSensorList(Sensor.TYPE_LIGHT);

if(sensors.size() > 0) {

Sensor s = sensors.get(0);

sm.registerListener(this, s, SensorManager.SENSOR_DELAY_UI);

}

@Override

public void onAccuracyChanged(Sensor sensor, int accuracy) {}

@Override

public void onSensorChanged(SensorEvent event) {

if(event.sensor.getType() == Sensor.TYPE_LIGHT) {

String s = new SimpleDateFormat("MM/dd HH:mm:ss").format(new Date()) + " " +

String.format("%.2f", event.values[0]);

tv.setText(s);

body = s + "\n" + body;

}

package com.example.sokamura.myapplication;

import android.os.AsyncTask;
import java.io.StringWriter;
import java.io.PrintWriter;
import java.io.OutputStreamWriter;
import java.net.HttpURLConnection;
import java.net.URL;
import java.net.URLEncoder;

class SubClass extends AsyncTask<String,Integer,String> {

    //マニフェストにパーミッション追加

    public SubClass(MainActivity param){}

    @Override
    protected void onPostExecute(String param){}

    @Override
    protected String doInBackground (String ... params) {

        HttpURLConnection con = null;

        try {

            URL url = new URL("http://xxx.com/xxx.php");

            con = (HttpURLConnection) url.openConnection();
            con.setRequestMethod("POST");
            con.setDoOutput(true);
            con.setRequestProperty("Content-Type", "application/x-www-form-urlencoded; charset=UTF-8");

            OutputStreamWriter out = new OutputStreamWriter(con.getOutputStream());
            out.write("key=xxx" + "&lux=" + URLEncoder.encode(params[0],"UTF-8"));
            out.close();
            con.getInputStream();

            return "";

        } catch (Exception e) {

            StringWriter sw = new StringWriter();
            PrintWriter pw = new PrintWriter(sw);
            e.printStackTrace(pw);
            pw.flush();

            return  "";

        } finally { if (con != null) con.disconnect(); }
    }
}

package com.example.sokamura.myapplication;

import android.os.AsyncTask;

import java.io.StringWriter;

import java.io.PrintWriter;

import java.io.OutputStreamWriter;

import java.net.HttpURLConnection;

import java.net.URL;

import java.net.URLEncoder;

class SubClass extends AsyncTask<String,Integer,String> {

//マニフェストにパーミッション追加

public SubClass(MainActivity param){}

@Override

protected void onPostExecute(String param){}

@Override

protected String doInBackground (String ... params) {

HttpURLConnection con = null;

try {

URL url = new URL("http://xxx.com/xxx.php");

con = (HttpURLConnection) url.openConnection();

con.setRequestMethod("POST");

con.setDoOutput(true);

con.setRequestProperty("Content-Type", "application/x-www-form-urlencoded; charset=UTF-8");

OutputStreamWriter out = new OutputStreamWriter(con.getOutputStream());

out.write("key=xxx" + "&lux=" + URLEncoder.encode(params[0],"UTF-8"));

out.close();

con.getInputStream();

return "";

} catch (Exception e) {

StringWriter sw = new StringWriter();

PrintWriter pw = new PrintWriter(sw);

e.printStackTrace(pw);

pw.flush();

return "";

} finally { if (con != null) con.disconnect(); }

}

2017-01-052023-04-05

WordPress カスタマイズ(VerUp含む)

不要ファイル削除

・バージョンアップ時、再実行。

public_html
にて実行。
rm -i readme.html
rm -i license.txt
rm -i wp-config-sample.php

記事一覧

・バージョンアップ時、再実行。

WordPress 記事一覧インクリメンタル検索

タイトル表示(投稿)を消す

・バージョンアップ時、再実行。

wp-content/themes/xxx/index.php
の中で以下のようにコメントアウトする。

<?php if ( is_home() && ! is_front_page() ) : ?>
	<header class="page-header">
		<h1 class="page-title"><?php single_post_title(); ?></h1>
	</header>
<?php else : ?>
<!--
<header class="page-header">
	<h2 class="page-title"><?php _e( 'Posts', 'twentyseventeen' ); ?></h2>
</header>
-->
<?php endif; ?>

<?php if ( is_home() && ! is_front_page() ) : ?>

</header>

<?php else : ?>

<!--

</header>

-->

<?php endif; ?>

Analytics/AdSense連携

・バージョンアップ時、再実行。

Google Analytics/AdSense WordPress 連携

テーマの幅変更

・バージョンアップ時、再実行。

wp-content/themes/xxx/style.css
の3408行(/* Layout */)以下、変更。

.wrap
max-width
を1000pxから1200px

.has-sidebar:not(.error404) #primary
width
を58%から62%

.has-sidebar #secondary
width
を36%から32%

Aタグアンダーバー非表示

・バージョンアップ時、再実行。

wp-content/themes/xxx/style.css
の中で
.entry-content a(1191行)
が含まれる
-webkit-box-shadow: inset 0 -1px 0 rgba(15, 15, 15, 1);
box-shadow: inset 0 -1px 0 rgba(15, 15, 15, 1);
をコメントアウト。

.entry-content a:focus(1237行)
.entry-content a:hover(1238行)
が含まれる
-webkit-box-shadow: inset 0 0 0 rgba(0, 0, 0, 0), 0 3px 0 rgba(0, 0, 0, 1);
box-shadow: inset 0 0 0 rgba(0, 0, 0, 0), 0 3px 0 rgba(0, 0, 0, 1);
をコメントアウト。

タグ一覧で本文非表示

・バージョンアップ時、再実行。

wp-content/themes/xxx
の中に、
tag.php
を作成し以下のように記入。

<?php

get_header(); ?>

<div class="wrap">
	<div id="primary" class="content-area">
		<main id="main" class="site-main" role="main">

		<?php

		if (have_posts()){

			while (have_posts()){

				the_post();

				echo '<header class="entry-header">';
				if ('post' === get_post_type() ) {
					echo '<div class="entry-meta">';
					echo twentyseventeen_time_link();
					twentyseventeen_edit_link();
					echo '</div><!-- .entry-meta -->';
				};
				the_title('<span class="entry-title"><a href="' . esc_url( get_permalink() ) . '" rel="bookmark">', '</a></span>' );
				echo "</header>";
				
			}

			the_posts_pagination(
				array(
					'prev_text'          => twentyseventeen_get_svg( array( 'icon' => 'arrow-left' ) ) . '<span class="screen-reader-text">' . __( 'Previous page', 'twentyseventeen' ) . '</span>',
					'next_text'          => '<span class="screen-reader-text">' . __( 'Next page', 'twentyseventeen' ) . '</span>' . twentyseventeen_get_svg( array( 'icon' => 'arrow-right' ) ),
					'before_page_number' => '<span class="meta-nav screen-reader-text">' . __( 'Page', 'twentyseventeen' ) . ' </span>',
				)
			);
		}

		?>

		</main><!-- #main -->
	</div><!-- #primary -->
	<?php get_sidebar(); ?>
</div><!-- .wrap -->

<?php

get_footer();

<?php

get_header(); ?>

<?php

if (have_posts()){

while (have_posts()){

the_post();

echo '<header class="entry-header">';

if ('post' === get_post_type() ) {

echo '<div class="entry-meta">';

echo twentyseventeen_time_link();

twentyseventeen_edit_link();

echo '</div>';

};

the_title('<span class="entry-title"><a href="' . esc_url( get_permalink() ) . '" rel="bookmark">', '</a></span>' );

echo "</header>";

}

the_posts_pagination(

array(

'prev_text' => twentyseventeen_get_svg( array( 'icon' => 'arrow-left' ) ) . '<span class="screen-reader-text">' . __( 'Previous page', 'twentyseventeen' ) . '</span>',

'next_text' => '<span class="screen-reader-text">' . __( 'Next page', 'twentyseventeen' ) . '</span>' . twentyseventeen_get_svg( array( 'icon' => 'arrow-right' ) ),

'before_page_number' => '<span class="meta-nav screen-reader-text">' . __( 'Page', 'twentyseventeen' ) . ' </span>',

)

);

}

</main>

</div>

<?php get_sidebar(); ?>

</div>

<?php

get_footer();

更新順ウィジェット

・プラグイン化済み

WordPress 更新順ウィジェット

特定のページにJavaScriptを挿入

・プラグイン化済み

ここでは下記に挿入しているコード。

FFmpeg メモ

add_action('wp_footer', 'get_time_range', 1);

function get_time_range(){

if (!is_single('151')) return;

echo <<<HTML

  <style>
  #sh,#sm,#ss,#eh,#em,#es {
    display: inline;
  }
  </style>

  <script>

  jQuery(window).on('load', function() {

    jQuery('#sh').on('change', ToSec);
    jQuery('#sm').on('change', ToSec);
    jQuery('#ss').on('change', ToSec);
    jQuery('#eh').on('change', ToSec);
    jQuery('#em').on('change', ToSec);
    jQuery('#es').on('change', ToSec);

    function ToSec(){
      let startSec = (Number(jQuery('#sh').val()) * 60 * 60) + (Number(jQuery('#sm').val()) * 60) + Number(jQuery('#ss').val());
      let endSec = (Number(jQuery('#eh').val()) * 60 * 60) + (Number(jQuery('#em').val()) * 60) + Number(jQuery('#es').val());
      let rangeSec = endSec - startSec;
      jQuery('#cmd').text('ffmpeg -ss ' + startSec + ' -i i.mp4 -t ' + rangeSec + ' o.mp4');
    }

  });

  </script>

HTML;

}

add_action('wp_footer', 'get_time_range', 1);

function get_time_range(){

if (!is_single('151')) return;

echo <<<HTML

<style>

#sh,#sm,#ss,#eh,#em,#es {

display: inline;

}

</style>

jQuery(window).on('load', function() {

jQuery('#sh').on('change', ToSec);

jQuery('#sm').on('change', ToSec);

jQuery('#ss').on('change', ToSec);

jQuery('#eh').on('change', ToSec);

jQuery('#em').on('change', ToSec);

jQuery('#es').on('change', ToSec);

function ToSec(){

let startSec = (Number(jQuery('#sh').val()) * 60 * 60) + (Number(jQuery('#sm').val()) * 60) + Number(jQuery('#ss').val());

let endSec = (Number(jQuery('#eh').val()) * 60 * 60) + (Number(jQuery('#em').val()) * 60) + Number(jQuery('#es').val());

let rangeSec = endSec - startSec;

jQuery('#cmd').text('ffmpeg -ss ' + startSec + ' -i i.mp4 -t ' + rangeSec + ' o.mp4');

}

});

</script>

HTML;

}

タグクラウドを投稿数順に変更

・プラグイン化済み

function args_customize($args) {

  $add_args = array(
      'orderby' => 'count',
      'order' => 'DESC',
  );
  $args = wp_parse_args($args, $add_args);
  return $args;
}

add_action('widget_tag_cloud_args', 'args_customize');

function args_customize($args) {

$add_args = array(

'orderby' => 'count',

'order' => 'DESC',

);

$args = wp_parse_args($args, $add_args);

return $args;

}

add_action('widget_tag_cloud_args', 'args_customize');

タグクラウドにCSSを適用

・プラグイン化済み

function tag_cloud_css(){

echo <<<HTML

  <style>
  .tagcloud li {
    font-size: 0.9em;
  }
  </style>

HTML;
}

add_action('wp_footer', 'tag_cloud_css', 1);

function tag_cloud_css(){

echo <<<HTML

<style>

.tagcloud li {

font-size: 0.9em;

}

</style>

HTML;

}

add_action('wp_footer', 'tag_cloud_css', 1);

REST APIを無効化

・プラグイン化済み

パーマリンクが標準だと動かないが、塞がれているわけではないので注意。

http://okamurax.com/wp-json/wp/v2/posts
http://okamurax.com/?rest_route=/wp/v2/posts

function disable_rest() {
  return new WP_Error();
}
add_action('rest_authentication_errors', 'disable_rest');

function disable_rest() {

return new WP_Error();

}

add_action('rest_authentication_errors', 'disable_rest');

更新日を変更しない

更新日を更新しないチェックボックスを記事編集画面に追加。

・プラグイン化済み

add_action('admin_menu', function(){
	add_meta_box('high_low', '更新日', function(){
		echo '<div style="padding-top: 3px; overflow: hidden;">';
		echo '<div style="width: 100px; float: left;"><input name="high_low" type="radio" value="high" checked="checked">更新する</div>';
		echo '<div><input name="high_low" type="radio" value="low">更新しない</div>';
		echo '</div>';
	}, 'post', 'side', 'high');
});

add_action('save_post', function($post_id){
	$high_low = isset($_POST['high_low']) ? $_POST['high_low'] : null;
  if("" == get_post_meta($post_id, 'high_low')) {
		add_post_meta($post_id, 'high_low', $high_low, true) ;
  } elseif($high_low != get_post_meta($post_id, 'high_low')) {
		update_post_meta($post_id, 'high_low', $high_low) ;
  } elseif("" == $high_low) {
		delete_post_meta($post_id, 'high_low') ;
	}
});

add_filter('wp_insert_post_data', function($data, $postarr){
	$high_low = isset($_POST['high_low']) ? $_POST['high_low'] : null;
	if($high_low == "low"){
			unset($data["post_modified"]);
			unset($data["post_modified_gmt"]);
	}
	return $data;
}, 10, 2);

add_action('admin_menu', function(){

add_meta_box('high_low', '更新日', function(){

echo '<div style="padding-top: 3px; overflow: hidden;">';

echo '<div style="width: 100px; float: left;"><input name="high_low" type="radio" value="high" checked="checked">更新する</div>';

echo '<div><input name="high_low" type="radio" value="low">更新しない</div>';

echo '</div>';

}, 'post', 'side', 'high');

});

add_action('save_post', function($post_id){

$high_low = isset($_POST['high_low']) ? $_POST['high_low'] : null;

if("" == get_post_meta($post_id, 'high_low')) {

add_post_meta($post_id, 'high_low', $high_low, true) ;

} elseif($high_low != get_post_meta($post_id, 'high_low')) {

update_post_meta($post_id, 'high_low', $high_low) ;

} elseif("" == $high_low) {

delete_post_meta($post_id, 'high_low') ;

}

});

add_filter('wp_insert_post_data', function($data, $postarr){

$high_low = isset($_POST['high_low']) ? $_POST['high_low'] : null;

if($high_low == "low"){

unset($data["post_modified"]);

unset($data["post_modified_gmt"]);

}

return $data;

}, 10, 2);

favicon設定

・初回のみ

header.phpとwp-login.php
のheadタグの中に以下を追加

<link rel="SHORTCUT ICON" href=<?php echo get_template_directory_uri(); ?>/favicon.ico />

1	<link rel="SHORTCUT ICON" href=<?php echo get_template_directory_uri(); ?>/favicon.ico />

ダッシュボード＞外観＞カスタマイズ＞サイト基本情報＞サイトアイコン
からも変更することができる。

パーミション変更

・初回のみ

find . -type d | xargs chmod 705
find . -type f | xargs chmod 604
find . -name “wp-config.php” | xargs chmod 600

IPアドレスでログインを弾く

・初回のみ+IPアドレス変更時

wp-login.phpのある階層の.htaccessに以下を追記

<files wp-login.php>
order deny,allow
deny from all
allow from xxx.xxx.xxx.xxx
</files>

order deny,allow

deny from all

allow from xxx.xxx.xxx.xxx

</files>

wp-adminの直下にある.htaccessにも以下を追記。

order deny,allow
deny from all
allow from xxx.xxx.xxx.xxx

order deny,allow

deny from all

allow from xxx.xxx.xxx.xxx

※orderにてallowとdeny各ディレクティブのどちらを先に評価するか順番を指定する。
order allow,deny：許可→拒否
order deny,allow：拒否→許可

・2.4以降

wp-login.phpの階層

<files wp-login.php>
require all denied
require ip xxx.xxx.xxx.xxx
</files>

<files xmlrpc.php>
require all denied
</files>

require all denied

require ip xxx.xxx.xxx.xxx

</files>

require all denied

</files>

wp-adminの直下

require all denied
require ip xxx.xxx.xxx.xxx

1 2	require all denied require ip xxx.xxx.xxx.xxx

(未使用)ウィジットの最新の投稿を更新順に変更

wp-includes/widgets/class-wp-widget-recent-posts.php
の中にある
$r = new WP_Query()
の中に条件の連想配列があるので、以下を追加する。
‘orderby’=>’modified’

これで更新順になる。
Wordpressのバージョンアップ時に消えるときがある。

(未使用)更新日を表示

wp-includes/widgets/class-wp-widget-recent-posts.php
の中で以下のように変更

<span class="post-date"><br>
投稿：<?php echo get_the_date('Y-m-d',$recent_post->ID); ?> /
更新：<?php echo get_the_modified_date('Y-m-d',$recent_post->ID); ?>
</span>

投稿：<?php echo get_the_date('Y-m-d',$recent_post->ID); ?> /

更新：<?php echo get_the_modified_date('Y-m-d',$recent_post->ID); ?>

</span>

(未使用)ナビメニュー非表示

wp-content/themes/xxx/header.php
の中にある
wp_nav_menu()
をコメントアウト

(未使用)トップページから特定カテゴリ非表示

functions.phpに以下を追加。

function ExcCat( $query ) {
if ( $query->is_home()) {
$query->set( 'cat', '-99' );
}
}
add_action( 'pre_get_posts', 'ExcCat' );

function ExcCat( $query ) {

if ( $query->is_home()) {

$query->set( 'cat', '-99' );

}

add_action( 'pre_get_posts', 'ExcCat' );

(未使用)Google検索ボックス追加

ウィジェットでテキストを追加する。

<form action="http://www.google.com/search">
<input type="hidden" name="hl" value="ja" />
<input type="hidden" value="http://okamurax.com/" name="as_sitesearch" />
<input type="text" name="q" size="25" />
<input type="submit" name="btnG" value="検索" />
</form>

</form>

(未使用)ログイン状態でリダイレクト

<?php

add_action('wp_head', 'login_state', 1);

function login_state() {
	if (!is_user_logged_in()) wp_redirect('/wp-login.php');
}

<?php

add_action('wp_head', 'login_state', 1);

function login_state() {

if (!is_user_logged_in()) wp_redirect('/wp-login.php');

}

(未使用)自作タグクラウド

ウィジェットにショートコードを入れれば表示される。

function count_tag(){
  echo '<ul>';
  $tags = get_tags();
  foreach($tags as $tag){
    echo '<li>';
    echo '<a href="' . get_tag_link($tag->term_id) . '">' . $tag->name . '[' . $tag->count . ']</a>';
    echo '</li>';
  }
  echo '</ul>';
}

add_shortcode('count_tag', 'count_tag');

function count_tag(){

echo '<ul>';

$tags = get_tags();

foreach($tags as $tag){

echo '<li>';

echo '<a href="' . get_tag_link($tag->term_id) . '">' . $tag->name . '[' . $tag->count . ']</a>';

echo '</li>';

}

echo '</ul>';

}

add_shortcode('count_tag', 'count_tag');