テックメモ – ページ 46 – 自分用のメモです。内容が間違っていたり、作りかけで動作しないコードなどあるのでご注意ください。

2017-03-06

PHP Javascript 在庫管理

以前作っていた在庫管理。お蔵入り版。
1つのファイルにHTML,CSS,JS,PHPを全て書き込んでいた。

<!DOCTYPE html>
<html lang="ja">
<head>
	<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.2/jquery.min.js"></script>
	<meta charset="UTF-8">
	<title>受注参照</title>
	<style>
	body{
		font-family: Meiryo, sans-serif;
		font-size: 12px;
	}
	#view_tb{
		border-collapse: collapse;
		font-size: 12px;
	}
	#view_tb td, #view_tb th{
		border: 1px dotted #ccc;
		white-space: nowrap;
		padding: 1px;
	}
	#view_tb tr:hover{
		background-color: #eef;	
	}
	#frm{
		margin-bottom: 10px;
		background-color: #eef;
	}
	#bk{
		display: none;	
	}
	#sav_tb td{
		text-align: right;
	}
	.yen{
		text-align: right;
	}
	.lnk{
		cursor: pointer;
		color: blue;
	}
	</style>
</head>
<body>
<a href="?view=受注一覧&offset=0" id="bk">戻る</a>
<div id="header">
<?php
require_once('config.php');
require_once('functions.php');
// 登録 //
if( $_GET['save'] == "登録"){
	$n = date("Y-m-d");
echo <<< HTML
<form method="POST">
	<table id="sav_tb">
	<tr><td>区分：</td><td><input type="text" value="新規" disabled></td></tr>
	<tr><td>型式：</td><td><input type="text"></td></tr>
	<tr><td>工番(数値)：</td><td><input type="text" value=0></td></tr>
	<tr><td>数量(数値)：</td><td><input type="text" value=0></td></tr>
	<tr><td>納期：</td><td><input type="text" value="$n"></td></tr>
	<tr><td>得意先：</td><td><input type="text"></td></tr>
	<tr><td>単価(数値)：</td><td><input type="text" value="0"></td></tr>
	<tr><td>金額(数値)：</td><td><input type="text" value="0" disabled></td></tr>
	<tr><td>本社備考1：</td><td><input type="text"></td></tr>
	<tr><td>本社備考2：</td><td><input type="text"></td></tr>
	<tr><td>納期確定：</td><td><input type="text"></td></tr>
	<tr><td>更新日：</td><td><input type="text" value="$n" disabled></td></tr>
	<tr><td>作成日：</td><td><input type="text" value="$n" disabled></td></tr>
	<tr>
		<td></td>
		<td><span class="lnk" id="sv">[登録]</span>&nbsp;&nbsp;&nbsp;<span class="lnk" id="abt">[中止]</span></td>
	</tr>
	</table>
</div><!-- header -->
</form>
<script>
$(function(){
	// 登録 //
	$(document).on('click', '#sv', function(){
		var kubun = $("#sav_tb tr:nth-child(1) input").val();
		var katashiki = $("#sav_tb tr:nth-child(2) input").val();
		var kouban = parseInt($("#sav_tb tr:nth-child(3) input").val());
		var suryo = parseInt($("#sav_tb tr:nth-child(4) input").val());
		var nouki = $("#sav_tb tr:nth-child(5) input").val();	
		var tokuisaki = $("#sav_tb tr:nth-child(6) input").val();
		var tanka = parseInt($("#sav_tb tr:nth-child(7) input").val());
		var kingaku = parseInt($("#sav_tb tr:nth-child(8) input").val());
		var honsyabikou1 = $("#sav_tb tr:nth-child(9) input").val();
		var honsyabikou2 = $("#sav_tb tr:nth-child(10) input").val();
		var noukikakutei = $("#sav_tb tr:nth-child(11) input").val();
		var koushinbi = $("#sav_tb tr:nth-child(12) input").val();
		var sakuseibi = $("#sav_tb tr:nth-child(13) input").val();
		// nouki //
		if (nouki != ""){
			nouki = nouki.replace(/\//g,"-");
			var d = new Date(nouki);
			var mth = d.getMonth() + 1;
			if( 1 <= mth && 12 >= mth && 10 > mth ){ 
				mth = '0' + mth;
			}else if( 1 <= mth && 12 >= mth ){
				mth = mth;
			}else{
				alert("納期を確認してください(YYYY-MM-DD)");
				return;
			}
			var day = d.getDate();
			if( 1 <= day && 31 >= day && 10 > day ){ 
				day = '0' + day;
			}else if( 1 <= day && 31 >= day ){
				day = day;
			}else{
				alert("納期を確認してください(YYYY-MM-DD)");
				return;
			}
			nouki = d.getFullYear() + "-" + mth + "-" + day;
			$("#sav_tb tr:nth-child(5) input").val(nouki);
		}
		// suryo tanka //
		if((!isNaN(suryo)) && (!isNaN(tanka))){
			kingaku = suryo * tanka;
			$("#sav_tb tr:nth-child(8) input").val(kingaku);
		}else{
			alert("数量と単価を確認してください");
			return;
		}
		// kouban //
		if(isNaN(kouban)){
			alert("工番を確認してください");
			return;
		}
		if(confirm('登録しますか？')){			
			$.post('_save.php',{
				kubun: kubun,
				katashiki: katashiki,
				kouban: kouban,
				suryo: suryo,
				nouki: nouki,
				tokuisaki: tokuisaki,
				tanka: tanka,
				kingaku: kingaku,
				honsyabikou1: honsyabikou1,
				honsyabikou2: honsyabikou2,
				noukikakutei: noukikakutei,
				koushinbi: koushinbi,
				sakuseibi: sakuseibi
			},function(){
				location.href="?view=受注一覧&offset=0";
			});			
		}
	});
	// 中止 //
	$(document).on('click', '#abt', function(){
		location.href="?view=受注一覧&offset=0";
	});
});
</script>
</body>	
</html>
HTML;
exit;	
}
// 受注一覧　在庫一覧 //
if( $_GET['view'] == "在庫一覧" ){
	echo $_GET['view'];
	$sql = "select * from order_tb where (入庫 is not null) and (出庫 is null)";
	$cntsql = "select count(*) from order_tb where (入庫 is not null) and (出庫 is null)";	

	if (array_key_exists('型',$_GET)){
		$sql = $sql . " and (型式 like '%" . $_GET['型'] . "%')";
		$cntsql = $cntsql . " and (型式 like '%" . $_GET['型'] . "%')";
	}
	if (array_key_exists('工',$_GET)){
		$sql = $sql . " and (工番 like '%" . $_GET['工'] . "%')";
		$cntsql = $cntsql . " and (工番 like '%" . $_GET['工'] . "%')";
	}
	if (array_key_exists('納',$_GET) && $_GET['納'] != ""){
		$sql = $sql . " and (納期 like '%" . date("Y-m-d", strtotime($_GET['納'])) . "%')";
		$cntsql = $cntsql . " and (納期 like '%" . date("Y-m-d", strtotime($_GET['納'])) . "%')";
	}
	if (array_key_exists('得',$_GET)){
		$sql = $sql . " and (得意先 like '%" . $_GET['得'] . "%')";	
		$cntsql = $cntsql . " and (得意先 like '%" . $_GET['得'] . "%')";	
	}
	if (array_key_exists('本',$_GET)){
		$sql = $sql . " and ((本社備考1 like '%" . $_GET['本'] . "%')";
		$sql = $sql . " or (本社備考2 like '%" . $_GET['本'] . "%'))";
		$cntsql = $cntsql . " and ((本社備考1 like '%" . $_GET['本'] . "%')";	
		$cntsql = $cntsql . " or (本社備考2 like '%" . $_GET['本'] . "%'))";
	}
	if (array_key_exists('更',$_GET) && $_GET['更'] != ""){
		$sql = $sql . " and (更新日 like '%" . date("Y-m-d", strtotime($_GET['更'])) . "%')";	
		$cntsql = $cntsql . " and (更新日 like '%" . date("Y-m-d", strtotime($_GET['更'])) . "%')";	
	}
	if (array_key_exists('作',$_GET) && $_GET['作'] != ""){
		$sql = $sql . " and (作成日 like '%" . date("Y-m-d", strtotime($_GET['作'])) . "%')";	
		$cntsql = $cntsql . " and (作成日 like '%" . date("Y-m-d", strtotime($_GET['作'])) . "%')";	
	}
	$sql = $sql . " limit " . $_GET['offset'] . ",100;";
	$cntsql = $cntsql . ";";
}elseif( $_GET['view'] == "受注一覧" ){
	echo $_GET['view'];
	$sql = "select * from order_tb where (id is not null)";
	$cntsql = "select count(*) from order_tb where (id is not null)";
	if (array_key_exists('型',$_GET)){
		$sql = $sql . " and (型式 like '%" . $_GET['型'] . "%')";
		$cntsql = $cntsql . " and (型式 like '%" . $_GET['型'] . "%')";
	}
	if (array_key_exists('工',$_GET)){
		$sql = $sql . " and (工番 like '%" . $_GET['工'] . "%')";
		$cntsql = $cntsql . " and (工番 like '%" . $_GET['工'] . "%')";
	}
	if (array_key_exists('納',$_GET) && $_GET['納'] != ""){
		$sql = $sql . " and (納期 like '%" . date("Y-m-d", strtotime($_GET['納'])) . "%')";
		$cntsql = $cntsql . " and (納期 like '%" . date("Y-m-d", strtotime($_GET['納'])) . "%')";
	}
	if (array_key_exists('得',$_GET)){
		$sql = $sql . " and (得意先 like '%" . $_GET['得'] . "%')";	
		$cntsql = $cntsql . " and (得意先 like '%" . $_GET['得'] . "%')";	
	}
	if (array_key_exists('本',$_GET)){
		$sql = $sql . " and ((本社備考1 like '%" . $_GET['本'] . "%')";
		$sql = $sql . " or (本社備考2 like '%" . $_GET['本'] . "%'))";
		$cntsql = $cntsql . " and ((本社備考1 like '%" . $_GET['本'] . "%')";	
		$cntsql = $cntsql . " or (本社備考2 like '%" . $_GET['本'] . "%'))";
	}
	if (array_key_exists('更',$_GET) && $_GET['更'] != ""){
		$sql = $sql . " and (更新日 like '%" . date("Y-m-d", strtotime($_GET['更'])) . "%')";	
		$cntsql = $cntsql . " and (更新日 like '%" . date("Y-m-d", strtotime($_GET['更'])) . "%')";	
	}
	if (array_key_exists('作',$_GET) && $_GET['作'] != ""){
		$sql = $sql . " and (作成日 like '%" . date("Y-m-d", strtotime($_GET['作'])) . "%')";	
		$cntsql = $cntsql . " and (作成日 like '%" . date("Y-m-d", strtotime($_GET['作'])) . "%')";	
	}
	$sql = $sql . " limit " . $_GET['offset'] . ",100;";
	$cntsql = $cntsql . ";";
}else{
echo <<< HTML
<a href="?view=受注一覧&offset=0">受注一覧(入出庫管理)</a>
</div><!--header-->
</body>
</html>
HTML;
exit;
}
$db = con();
$rows = array();
$s = $db->query($cntsql);
if (array_key_exists('型',$_GET)){ echo "(検索結果)"; }
$cnt = $s->fetchColumn();
echo " " .$cnt . "件取得しました";
foreach($db->query($sql) as $row){
	array_push($rows, $row);
}
?>
<form method="GET" id="frm">
	<input type="submit" value="受注一覧" name="view">
	<input type="submit" value="在庫一覧" name="view">
	<input type="hidden" value="0" name="offset">
	<?php if($_GET['offset'] > 0 && array_key_exists('型', $_GET)): ?>
		<a href="?view=<?php echo $_GET['view']; ?>&offset=<?php echo $_GET['offset'] - 100; ?>
		&型=<?php echo $_GET['型']; ?>
		&工=<?php echo $_GET['工']; ?>
		&納=<?php echo $_GET['納']; ?>
		&得=<?php echo $_GET['得']; ?>
		&本=<?php echo $_GET['本']; ?>
		&更=<?php echo $_GET['更']; ?>
		&作=<?php echo $_GET['作']; ?>		
		">前へ</a>
	<?php elseif($_GET['offset'] > 0): ?>
		<a href="?view=<?php echo $_GET['view']; ?>&offset=<?php echo $_GET['offset'] - 100; ?>">前へ</a>		
	<?php else: ?>
		前へ
	<?php endif; ?>
	<?php if($_GET['offset'] + 100 < $cnt  && array_key_exists('型', $_GET)): ?>
		<a href="?view=<?php echo $_GET['view']; ?>&offset=<?php echo $_GET['offset'] + 100; ?>
		&型=<?php echo $_GET['型']; ?>
		&工=<?php echo $_GET['工']; ?>
		&納=<?php echo $_GET['納']; ?>
		&得=<?php echo $_GET['得']; ?>
		&本=<?php echo $_GET['本']; ?>
		&更=<?php echo $_GET['更']; ?>
		&作=<?php echo $_GET['作']; ?>	
		">次へ</a>
	<?php elseif($_GET['offset'] + 100 < $cnt): ?>
		<a href="?view=<?php echo $_GET['view']; ?>&offset=<?php echo $_GET['offset'] + 100; ?>">次へ</a>
	<?php else: ?>
		次へ
	<?php endif; ?>
	<?php if($_GET['offset'] < (($cnt-1) - (($cnt-1) % 100)) && array_key_exists('型', $_GET)): ?>
		<a href="?view=<?php echo $_GET['view']; ?>&offset=<?php echo ($cnt-1) - (($cnt-1) % 100); ?>
		&型=<?php echo $_GET['型']; ?>
		&工=<?php echo $_GET['工']; ?>
		&納=<?php echo $_GET['納']; ?>
		&得=<?php echo $_GET['得']; ?>
		&本=<?php echo $_GET['本']; ?>
		&更=<?php echo $_GET['更']; ?>
		&作=<?php echo $_GET['作']; ?>	
		">最後へ</a>
	<?php elseif($_GET['offset'] < (($cnt-1) - (($cnt-1) % 100))): ?>
		<a href="?view=<?php echo $_GET['view']; ?>&offset=<?php echo ($cnt-1) - (($cnt-1) % 100); ?>">最後へ</a>
	<?php else: ?>
		最後へ
	<?php endif; ?>
	(<?php echo $_GET['offset'] + 1; ?>から<?php echo $_GET['offset'] + 100; ?>まで)
</form>	
<form method="GET" id="frm">
	<input type="submit" value="登録" name="save">
	<input type="button" id="csv" value="書出">
</form>
<form method="GET" id="frm">
	型:<input type="text" value="<?php echo $_GET['型']; ?>" size="8" name="型">
	　工:<input type="text" value="<?php echo $_GET['工']; ?>" size="8" name="工">
	　納:<input type="text" value="<?php echo $_GET['納']; ?>" size="8" name="納" id="n">
	　得:<input type="text" value="<?php echo $_GET['得']; ?>" size="8" name="得">
	　本:<input type="text" value="<?php echo $_GET['本']; ?>" size="8" name="本">
	　更:<input type="text" value="<?php echo $_GET['更']; ?>" size="8" name="更">
	　作:<input type="text" value="<?php echo $_GET['作']; ?>" size="8" name="作">
	<input type="hidden" value="<?php echo $_GET['view']; ?>" name="view">
	<input type="hidden" value="0" name="offset">
	<input type="submit" value="取得">：
</form>	
</div><!-- header -->
<div id="view">
<table id="view_tb">
	<tr>
		<th>区分</th>
		<th>型式</th>
		<th>工番</th>
		<th>数量</th>
		<th>納期</th>
		<th>得意先</th>
		<th>単価</th>
		<th>金額</th>	
		<th>本社備考1</th>
		<th>本社備考2</th>
		<th>納期確定</th>
		<th>更新日</th>	
		<th>作成日</th>
		<th>入庫</th>
		<th>出庫</th>
		<th>入庫</th>
		<th>出庫</th>
		<th>入消</th>
		<th>出消</th>
		<th></th>
		<th>分割</th>
		<th>回数</th>	
		<th>削除</th>
	</tr>
	<?php for($i = 0, $total = 0; $i < count($rows); $i++ ): ?>
	<tr id="r_<?php echo $rows[$i][0]; ?>" data-id="<?php echo $rows[$i][0]; ?>">
		<td><?php echo $rows[$i][2]; ?></td>
		<td><?php echo $rows[$i][4]; ?></td>
		<td><?php echo $rows[$i][5]; ?></td>
		<td><?php echo $rows[$i][9]; ?></td>
		<td><?php echo $rows[$i][10]; ?></td>
		<td><?php echo $rows[$i][15]; ?></td>
		<td class="lnk yen" id="plice"><?php echo $rows[$i][16]; ?></td>
		<td class="yen"><?php echo $rows[$i][17]; ?></td>
		<td><?php echo $rows[$i][18]; ?></td>
		<td><?php echo $rows[$i][19]; ?></td>
		<td><?php echo $rows[$i][20]; ?></td>
		<td><?php echo $rows[$i][22]; ?></td>
		<td><?php echo $rows[$i][23]; ?></td>
		<td><?php echo $rows[$i][24]; ?></td>
		<td><?php echo $rows[$i][25]; ?></td>
		<td class="lnk" id="rcv">[入庫]</td>
		<td class="lnk" id="shp">[出庫]</td>
		<td class="lnk" id="d_rcv">[入消]</td>
		<td class="lnk" id="d_shp">[出消]</td>	
		<td><input id="inp" type="text" size="1"></td>
		<td class="lnk" id="sp">[分割]</td>
		<td><?php echo $rows[$i][26]; ?></td>		
		<td class="lnk" id="del">[削除]</td>
	</tr>
	<?php $total = $total + $rows[$i][17]; ?>
	<?php endfor; ?>
	<tr>
		<td></td>
		<td></td>
		<td></td>
		<td></td>
		<td></td>
		<td></td>
		<td></td>
		<td class="yen"><?php echo number_format($total); ?></td>	
		<td></td>
		<td></td>
		<td></td>
		<td></td>	
		<td></td>
		<td></td>
		<td></td>
		<td></td>
		<td></td>
		<td></td>
		<td></td>
		<td></td>
		<td></td>
		<td></td>	
		<td></td>
	</tr>
</table>
</div><!--view-->	
<script>
$(function(){
	// 削除 //
	$(document).on('click', '#del', function(){
		if(confirm('削除しますか？')){
			var id = $(this).parent().data('id');
			$.post('_delete.php',{id: id},function(){
				$('#r_' + id).fadeOut(300);
				location.reload();
			});
		}
	});
	// 入庫 //
	$(document).on('click', '#rcv', function(){
		if(confirm('入庫しますか？')){
			var id = $(this).parent().data('id');
			$.post('_rcv.php',{id: id},function(rs){
				$('#r_' + id + " > td:nth-child(14)").text(rs);
			});
		}
	});
	// 出庫 //
	$(document).on('click', '#shp', function(){
		if(confirm('出庫しますか？')){
			var id = $(this).parent().data('id');
			$.post('_shp.php',{id: id},function(rs){
				$('#r_' + id + " > td:nth-child(15)").text(rs);
			});
		}
	});
	// 入消 //
	$(document).on('click', '#d_rcv', function(){
		if(confirm('入庫を取消しますか？')){
			var id = $(this).parent().data('id');
			$.post('_d_rcv.php',{id: id},function(){
				$('#r_' + id + " > td:nth-child(14)").text("");
			});
		}
	});
	// 出消 //
	$(document).on('click', '#d_shp', function(){
		if(confirm('出庫を取消しますか？')){
			var id = $(this).parent().data('id');
			$.post('_d_shp.php',{id: id},function(){
				$('#r_' + id + " > td:nth-child(15)").text("");
			});
		}
	});
	// 分割 //
	$(document).on('click', '#sp', function(){
		var id = $(this).parent().data('id');
		var num = parseInt($('#r_' + id + " > td:nth-child(4)").text());
		var plice = parseInt($('#r_' + id + " > td:nth-child(7)").text());
		var p_num = parseInt($('#r_' + id + ' input').val());
		if(!isNaN(p_num) && num > p_num && p_num > 0){
			if(confirm('受注数 ' + num + ' から ' + p_num + ' を別の行に複製します')){
				num = num - p_num;
				$.post('_pull_num.php',{id: id, num: num, p_num: p_num, plice: plice},function(){
					$('#r_' + id).fadeOut(300,function(){
						location.reload();
					});
				});
			}
		}
	});
	// 単価 //
	$(document).on('click', '#plice',function(){
		var id = $(this).parent().data('id');
		var num = parseInt($('#r_' + id + " > td:nth-child(4)").text());
		var plice = parseInt(prompt("単価を入力してください"));
		if(!isNaN(plice) && !isNaN(plice * num)){
			$.post('_plice.php',{id: id, num: num, plice: plice},function(){
				$('#r_' + id + " > td:nth-child(7)").text(plice);
				$('#r_' + id + " > td:nth-child(8)").text(plice * num);
				location.reload();
			});
		}
	});
	// 書出 //
	var flg = true;
	$(document).on('click', '#csv',function(){
		if(flg){
			flg = false;
			$("#view_tb th:nth-child(n+16):nth-child(-n+21), td:nth-child(n+16):nth-child(-n+21)").remove();
			$("#view_tb th:nth-last-child(1), td:nth-last-child(1)").remove();
			$("#header").css("display","none");
			$("#bk").css("display","inline");
		}
	});
});
</script>
</body>
</html>

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

493

494

495

496

<!DOCTYPE html>

<head>

<style>

body{

font-family: Meiryo, sans-serif;

font-size: 12px;

}

#view_tb{

border-collapse: collapse;

font-size: 12px;

}

#view_tb td, #view_tb th{

border: 1px dotted #ccc;

white-space: nowrap;

padding: 1px;

}

#view_tb tr:hover{

background-color: #eef;

}

#frm{

margin-bottom: 10px;

background-color: #eef;

}

#bk{

display: none;

}

#sav_tb td{

text-align: right;

}

.yen{

text-align: right;

}

.lnk{

cursor: pointer;

color: blue;

}

</style>

</head>

<body>

<?php

require_once('config.php');

require_once('functions.php');

// 登録 //

if( $_GET['save'] == "登録"){

$n = date("Y-m-d");

echo <<< HTML

<tr>

</tr>

</table>

</div>

</form>

$(function(){

// 登録 //

$(document).on('click', '#sv', function(){

var kubun = $("#sav_tb tr:nth-child(1) input").val();

var katashiki = $("#sav_tb tr:nth-child(2) input").val();

var kouban = parseInt($("#sav_tb tr:nth-child(3) input").val());

var suryo = parseInt($("#sav_tb tr:nth-child(4) input").val());

var nouki = $("#sav_tb tr:nth-child(5) input").val();

var tokuisaki = $("#sav_tb tr:nth-child(6) input").val();

var tanka = parseInt($("#sav_tb tr:nth-child(7) input").val());

var kingaku = parseInt($("#sav_tb tr:nth-child(8) input").val());

var honsyabikou1 = $("#sav_tb tr:nth-child(9) input").val();

var honsyabikou2 = $("#sav_tb tr:nth-child(10) input").val();

var noukikakutei = $("#sav_tb tr:nth-child(11) input").val();

var koushinbi = $("#sav_tb tr:nth-child(12) input").val();

var sakuseibi = $("#sav_tb tr:nth-child(13) input").val();

// nouki //

if (nouki != ""){

nouki = nouki.replace(/\//g,"-");

var d = new Date(nouki);

var mth = d.getMonth() + 1;

if( 1 <= mth && 12 >= mth && 10 > mth ){

mth = '0' + mth;

}else if( 1 <= mth && 12 >= mth ){

mth = mth;

}else{

alert("納期を確認してください(YYYY-MM-DD)");

return;

}

var day = d.getDate();

if( 1 <= day && 31 >= day && 10 > day ){

day = '0' + day;

}else if( 1 <= day && 31 >= day ){

day = day;

}else{

alert("納期を確認してください(YYYY-MM-DD)");

return;

}

nouki = d.getFullYear() + "-" + mth + "-" + day;

$("#sav_tb tr:nth-child(5) input").val(nouki);

}

// suryo tanka //

if((!isNaN(suryo)) && (!isNaN(tanka))){

kingaku = suryo * tanka;

$("#sav_tb tr:nth-child(8) input").val(kingaku);

}else{

alert("数量と単価を確認してください");

return;

}

// kouban //

if(isNaN(kouban)){

alert("工番を確認してください");

return;

}

if(confirm('登録しますか？')){

$.post('_save.php',{

kubun: kubun,

katashiki: katashiki,

kouban: kouban,

suryo: suryo,

nouki: nouki,

tokuisaki: tokuisaki,

tanka: tanka,

kingaku: kingaku,

honsyabikou1: honsyabikou1,

honsyabikou2: honsyabikou2,

noukikakutei: noukikakutei,

koushinbi: koushinbi,

sakuseibi: sakuseibi

},function(){

location.href="?view=受注一覧&offset=0";

});

}

});

// 中止 //

$(document).on('click', '#abt', function(){

location.href="?view=受注一覧&offset=0";

});

</script>

</body>

</html>

HTML;

exit;

}

// 受注一覧　在庫一覧 //

if( $_GET['view'] == "在庫一覧" ){

echo $_GET['view'];

$sql = "select * from order_tb where (入庫 is not null) and (出庫 is null)";

$cntsql = "select count(*) from order_tb where (入庫 is not null) and (出庫 is null)";

if (array_key_exists('型',$_GET)){

$sql = $sql . " and (型式 like '%" . $_GET['型'] . "%')";

$cntsql = $cntsql . " and (型式 like '%" . $_GET['型'] . "%')";

}

if (array_key_exists('工',$_GET)){

$sql = $sql . " and (工番 like '%" . $_GET['工'] . "%')";

$cntsql = $cntsql . " and (工番 like '%" . $_GET['工'] . "%')";

}

if (array_key_exists('納',$_GET) && $_GET['納'] != ""){

$sql = $sql . " and (納期 like '%" . date("Y-m-d", strtotime($_GET['納'])) . "%')";

$cntsql = $cntsql . " and (納期 like '%" . date("Y-m-d", strtotime($_GET['納'])) . "%')";

}

if (array_key_exists('得',$_GET)){

$sql = $sql . " and (得意先 like '%" . $_GET['得'] . "%')";

$cntsql = $cntsql . " and (得意先 like '%" . $_GET['得'] . "%')";

}

if (array_key_exists('本',$_GET)){

$sql = $sql . " and ((本社備考1 like '%" . $_GET['本'] . "%')";

$sql = $sql . " or (本社備考2 like '%" . $_GET['本'] . "%'))";

$cntsql = $cntsql . " and ((本社備考1 like '%" . $_GET['本'] . "%')";

$cntsql = $cntsql . " or (本社備考2 like '%" . $_GET['本'] . "%'))";

}

if (array_key_exists('更',$_GET) && $_GET['更'] != ""){

$sql = $sql . " and (更新日 like '%" . date("Y-m-d", strtotime($_GET['更'])) . "%')";

$cntsql = $cntsql . " and (更新日 like '%" . date("Y-m-d", strtotime($_GET['更'])) . "%')";

}

if (array_key_exists('作',$_GET) && $_GET['作'] != ""){

$sql = $sql . " and (作成日 like '%" . date("Y-m-d", strtotime($_GET['作'])) . "%')";

$cntsql = $cntsql . " and (作成日 like '%" . date("Y-m-d", strtotime($_GET['作'])) . "%')";

}

$sql = $sql . " limit " . $_GET['offset'] . ",100;";

$cntsql = $cntsql . ";";

}elseif( $_GET['view'] == "受注一覧" ){

echo $_GET['view'];

$sql = "select * from order_tb where (id is not null)";

$cntsql = "select count(*) from order_tb where (id is not null)";

if (array_key_exists('型',$_GET)){

$sql = $sql . " and (型式 like '%" . $_GET['型'] . "%')";

$cntsql = $cntsql . " and (型式 like '%" . $_GET['型'] . "%')";

}

if (array_key_exists('工',$_GET)){

$sql = $sql . " and (工番 like '%" . $_GET['工'] . "%')";

$cntsql = $cntsql . " and (工番 like '%" . $_GET['工'] . "%')";

}

if (array_key_exists('納',$_GET) && $_GET['納'] != ""){

$sql = $sql . " and (納期 like '%" . date("Y-m-d", strtotime($_GET['納'])) . "%')";

$cntsql = $cntsql . " and (納期 like '%" . date("Y-m-d", strtotime($_GET['納'])) . "%')";

}

if (array_key_exists('得',$_GET)){

$sql = $sql . " and (得意先 like '%" . $_GET['得'] . "%')";

$cntsql = $cntsql . " and (得意先 like '%" . $_GET['得'] . "%')";

}

if (array_key_exists('本',$_GET)){

$sql = $sql . " and ((本社備考1 like '%" . $_GET['本'] . "%')";

$sql = $sql . " or (本社備考2 like '%" . $_GET['本'] . "%'))";

$cntsql = $cntsql . " and ((本社備考1 like '%" . $_GET['本'] . "%')";

$cntsql = $cntsql . " or (本社備考2 like '%" . $_GET['本'] . "%'))";

}

if (array_key_exists('更',$_GET) && $_GET['更'] != ""){

$sql = $sql . " and (更新日 like '%" . date("Y-m-d", strtotime($_GET['更'])) . "%')";

$cntsql = $cntsql . " and (更新日 like '%" . date("Y-m-d", strtotime($_GET['更'])) . "%')";

}

if (array_key_exists('作',$_GET) && $_GET['作'] != ""){

$sql = $sql . " and (作成日 like '%" . date("Y-m-d", strtotime($_GET['作'])) . "%')";

$cntsql = $cntsql . " and (作成日 like '%" . date("Y-m-d", strtotime($_GET['作'])) . "%')";

}

$sql = $sql . " limit " . $_GET['offset'] . ",100;";

$cntsql = $cntsql . ";";

}else{

echo <<< HTML

<a href="?view=受注一覧&offset=0">受注一覧(入出庫管理)</a>

</div>

</body>

</html>

HTML;

exit;

}

$db = con();

$rows = array();

$s = $db->query($cntsql);

if (array_key_exists('型',$_GET)){ echo "(検索結果)"; }

$cnt = $s->fetchColumn();

echo " " .$cnt . "件取得しました";

foreach($db->query($sql) as $row){

array_push($rows, $row);

}

<?php if($_GET['offset'] > 0 && array_key_exists('型', $_GET)): ?>

<a href="?view=<?php echo $_GET['view']; ?>&offset=<?php echo $_GET['offset'] - 100; ?>

&型=<?php echo $_GET['型']; ?>

&工=<?php echo $_GET['工']; ?>

&納=<?php echo $_GET['納']; ?>

&得=<?php echo $_GET['得']; ?>

&本=<?php echo $_GET['本']; ?>

&更=<?php echo $_GET['更']; ?>

&作=<?php echo $_GET['作']; ?>

">前へ</a>

<?php elseif($_GET['offset'] > 0): ?>

<a href="?view=<?php echo $_GET['view']; ?>&offset=<?php echo $_GET['offset'] - 100; ?>">前へ</a>

<?php else: ?>

<?php endif; ?>

<?php if($_GET['offset'] + 100 < $cnt && array_key_exists('型', $_GET)): ?>

<a href="?view=<?php echo $_GET['view']; ?>&offset=<?php echo $_GET['offset'] + 100; ?>

&型=<?php echo $_GET['型']; ?>

&工=<?php echo $_GET['工']; ?>

&納=<?php echo $_GET['納']; ?>

&得=<?php echo $_GET['得']; ?>

&本=<?php echo $_GET['本']; ?>

&更=<?php echo $_GET['更']; ?>

&作=<?php echo $_GET['作']; ?>

">次へ</a>

<?php elseif($_GET['offset'] + 100 < $cnt): ?>

<a href="?view=<?php echo $_GET['view']; ?>&offset=<?php echo $_GET['offset'] + 100; ?>">次へ</a>

<?php else: ?>

<?php endif; ?>

<?php if($_GET['offset'] < (($cnt-1) - (($cnt-1) % 100)) && array_key_exists('型', $_GET)): ?>

<a href="?view=<?php echo $_GET['view']; ?>&offset=<?php echo ($cnt-1) - (($cnt-1) % 100); ?>

&型=<?php echo $_GET['型']; ?>

&工=<?php echo $_GET['工']; ?>

&納=<?php echo $_GET['納']; ?>

&得=<?php echo $_GET['得']; ?>

&本=<?php echo $_GET['本']; ?>

&更=<?php echo $_GET['更']; ?>

&作=<?php echo $_GET['作']; ?>

">最後へ</a>

<?php elseif($_GET['offset'] < (($cnt-1) - (($cnt-1) % 100))): ?>

<a href="?view=<?php echo $_GET['view']; ?>&offset=<?php echo ($cnt-1) - (($cnt-1) % 100); ?>">最後へ</a>

<?php else: ?>

最後へ

<?php endif; ?>

(<?php echo $_GET['offset'] + 1; ?>から<?php echo $_GET['offset'] + 100; ?>まで)

</form>

</form>

型:<input type="text" value="<?php echo $_GET['型']; ?>" size="8" name="型">

　工:<input type="text" value="<?php echo $_GET['工']; ?>" size="8" name="工">

　納:<input type="text" value="<?php echo $_GET['納']; ?>" size="8" name="納" id="n">

　得:<input type="text" value="<?php echo $_GET['得']; ?>" size="8" name="得">

　本:<input type="text" value="<?php echo $_GET['本']; ?>" size="8" name="本">

　更:<input type="text" value="<?php echo $_GET['更']; ?>" size="8" name="更">

　作:<input type="text" value="<?php echo $_GET['作']; ?>" size="8" name="作">

<input type="submit" value="取得">：

</form>

</div>

<tr>

</tr>

<?php for($i = 0, $total = 0; $i < count($rows); $i++ ): ?>

</tr>

<?php $total = $total + $rows[$i][17]; ?>

<?php endfor; ?>

<tr>

</tr>

</table>

</div>

$(function(){

// 削除 //

$(document).on('click', '#del', function(){

if(confirm('削除しますか？')){

var id = $(this).parent().data('id');

$.post('_delete.php',{id: id},function(){

$('#r_' + id).fadeOut(300);

location.reload();

});

}

});

// 入庫 //

$(document).on('click', '#rcv', function(){

if(confirm('入庫しますか？')){

var id = $(this).parent().data('id');

$.post('_rcv.php',{id: id},function(rs){

$('#r_' + id + " > td:nth-child(14)").text(rs);

});

}

});

// 出庫 //

$(document).on('click', '#shp', function(){

if(confirm('出庫しますか？')){

var id = $(this).parent().data('id');

$.post('_shp.php',{id: id},function(rs){

$('#r_' + id + " > td:nth-child(15)").text(rs);

});

}

});

// 入消 //

$(document).on('click', '#d_rcv', function(){

if(confirm('入庫を取消しますか？')){

var id = $(this).parent().data('id');

$.post('_d_rcv.php',{id: id},function(){

$('#r_' + id + " > td:nth-child(14)").text("");

});

}

});

// 出消 //

$(document).on('click', '#d_shp', function(){

if(confirm('出庫を取消しますか？')){

var id = $(this).parent().data('id');

$.post('_d_shp.php',{id: id},function(){

$('#r_' + id + " > td:nth-child(15)").text("");

});

}

});

// 分割 //

$(document).on('click', '#sp', function(){

var id = $(this).parent().data('id');

var num = parseInt($('#r_' + id + " > td:nth-child(4)").text());

var plice = parseInt($('#r_' + id + " > td:nth-child(7)").text());

var p_num = parseInt($('#r_' + id + ' input').val());

if(!isNaN(p_num) && num > p_num && p_num > 0){

if(confirm('受注数 ' + num + ' から ' + p_num + ' を別の行に複製します')){

num = num - p_num;

$.post('_pull_num.php',{id: id, num: num, p_num: p_num, plice: plice},function(){

$('#r_' + id).fadeOut(300,function(){

location.reload();

});

}

});

// 単価 //

$(document).on('click', '#plice',function(){

var id = $(this).parent().data('id');

var num = parseInt($('#r_' + id + " > td:nth-child(4)").text());

var plice = parseInt(prompt("単価を入力してください"));

if(!isNaN(plice) && !isNaN(plice * num)){

$.post('_plice.php',{id: id, num: num, plice: plice},function(){

$('#r_' + id + " > td:nth-child(7)").text(plice);

$('#r_' + id + " > td:nth-child(8)").text(plice * num);

location.reload();

});

}

});

// 書出 //

var flg = true;

$(document).on('click', '#csv',function(){

if(flg){

flg = false;

$("#view_tb th:nth-child(n+16):nth-child(-n+21), td:nth-child(n+16):nth-child(-n+21)").remove();

$("#view_tb th:nth-last-child(1), td:nth-last-child(1)").remove();

$("#header").css("display","none");

$("#bk").css("display","inline");

}

});

</script>

</body>

</html>

2017-03-06

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.Data.OleDb;

namespace 全在庫管理
{
    public partial class Form1 : Form
    {
        DataTable dt;

        public Form1()
        {
            InitializeComponent();

            dataGridView1.MultiSelect = false;
            dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
            dataGridView1.AllowUserToAddRows = false;
            dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;

            comboBox2.Enabled = false;

            textBox1.TextChanged += new EventHandler(init_view);
            textBox2.TextChanged += new EventHandler(init_view);
            textBox3.TextChanged += new EventHandler(init_view);
            textBox5.TextChanged += new EventHandler(init_view);

            contextMenuStrip1.Items.Add("更新");
            contextMenuStrip1.Items.Add("出庫");
            contextMenuStrip1.Items.Add("複写");
            contextMenuStrip1.Items.Add("削除");

            init();
        }

         ~Form1()
        {
            dt.Dispose();
        }

        private void init()
        {
            string q = @"select * from tbl where 出庫日 is null order by ID";
            if (checkBox3.Checked) q = @"select * from tbl where 出庫日 is not null order by ID";
            if (checkBox4.Checked) q = @"select * from tbl where 入庫日 = #" + DateTime.Now.ToString("yyyy/MM/dd") + "# order by ID";
            if (checkBox5.Checked) q = @"select * from tbl where 出庫日 = #" + DateTime.Now.ToString("yyyy/MM/dd") + "# order by ID";

            dt = Util.getDt(q);
            list_add();
        }

        public void list_add()
        {
            comboBox1.Items.Clear();
            comboBox2.Items.Clear();
            using (DataView dv = new DataView(dt))
            {
                using (DataTable tmp = dv.ToTable(true, new string[] { "仕入月" }))
                {
                    DataRow[] dr = tmp.Select("仕入月 is not null");
                    for (int i = 0; i < dr.Length; i++)
                    {
                        comboBox1.Items.Add(DateTime.Parse(dr[i]["仕入月"].ToString()).ToString("yyyy年MM月"));
                        comboBox2.Items.Add(DateTime.Parse(dr[i]["仕入月"].ToString()).ToString("yyyy年MM月"));
                    }
                }
            }
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            init_view(sender, e);
        }

        public void init_view(object sender, EventArgs e)
        {
            dataGridView1.Rows.Clear();
            string tmp = "";
            bool chk = false;

            if (textBox2.Text != "")
            {
                tmp = "工番 like '%" + textBox2.Text + "%'";
                chk = true;
            }

            if (textBox1.Text != "")
            {
                if (chk) tmp = tmp + " and ";
                tmp = tmp + "製品名称 like '%" + textBox1.Text + "%'";
                chk = true;
            }

            if (textBox3.Text != "")
            {
                if (chk) tmp = tmp + " and ";
                tmp = tmp + " 仕入先 like '%" + textBox3.Text + "%'";
                chk = true;
            }

            if (textBox5.Text != "")
            {
                if (chk) tmp = tmp + " and ";
                tmp = tmp + " 区分 like '%" + textBox5.Text + "%'";
                chk = true;
            }

            if (comboBox1.Text != "" && comboBox2.Enabled == false)
            {
                if (chk) tmp = tmp + " and ";
                tmp = tmp + " 仕入月 = #" + DateTime.Parse(comboBox1.Text).ToString("yyyy/MM/01") + "#";
            }
            else if (comboBox1.Text != "" && comboBox2.Enabled == true && comboBox2.Text != "")
            {
                if (chk) tmp = tmp + " and ";
                tmp = tmp + " 仕入月 >= #" + DateTime.Parse(comboBox1.Text).ToString("yyyy/MM/01")
                    + "# and 仕入月 <= #" + DateTime.Parse(comboBox2.Text).ToString("yyyy/MM/01") + "#";
            }
            else if (comboBox1.Text != "" && comboBox2.Enabled == true && comboBox2.Text == "")
            {
                if (chk) tmp = tmp + " and ";
                tmp = tmp + " 仕入月 >= #" + DateTime.Parse(comboBox1.Text).ToString("yyyy/MM/01") + "#";
            }
            else if (comboBox1.Text == "" && comboBox2.Enabled == true && comboBox2.Text != "")
            {
                if (chk) tmp = tmp + " and ";
                tmp = tmp + " 仕入月 <= #" + DateTime.Parse(comboBox2.Text).ToString("yyyy/MM/01") + "#";
            }

            System.Diagnostics.Debug.Print(tmp.ToString());

            DataRow[] dr = dt.Select(tmp, "ID");
            decimal[,] rc = new decimal[6, 4];

            for (int i = 0; i < dr.Length; i++)
            {
                decimal res00;
                decimal.TryParse(dr[i]["数量"].ToString(), out res00);
                decimal res01;
                decimal.TryParse(dr[i]["計上単価"].ToString(), out res01);
                DateTime res02;
                if (!DateTime.TryParse(dr[i]["仕入月"].ToString(), out res02)) res02 = new DateTime(1970, 1, 1);

                dataGridView1.Rows.Add(
                    dr[i]["ID"].ToString(),
                    dr[i]["工番"].ToString(),
                    dr[i]["製品名称"].ToString(),
                    dr[i]["仕入先"].ToString(),
                    res00.ToString(),
                    string.Format("{0:#,0}", res01),
                    string.Format("{0:#,0}", (res00 * res01)),
                    dr[i]["区分"].ToString(),
                    res02.ToString("yyyy年MM月"),
                    dr[i]["備考"].ToString()
                    );

                switch (dr[i]["区分"].ToString())
                {
                    case "内作材料①":

                        if ((res00 * res01) >= 50000)
                        {
                            rc[1, 2] = rc[1, 2] + (res00 * res01);
                        }
                        else
                        {
                            rc[1, 3] = rc[1, 3] + (res00 * res01);
                        }
                        rc[1, 1] = rc[1, 1] + (res00 * res01);
                        break;

                    case "外作材料①":

                        if ((res00 * res01) >= 50000)
                        {
                            rc[2, 2] = rc[2, 2] + (res00 * res01);
                        }
                        else
                        {
                            rc[2, 3] = rc[2, 3] + (res00 * res01);
                        }
                        rc[2, 1] = rc[2, 1] + (res00 * res01);
                        break;

                    case "内作製品":

                        if ((res00 * res01) >= 50000)
                        {
                            rc[3, 2] = rc[3, 2] + (res00 * res01);
                        }
                        else
                        {
                            rc[3, 3] = rc[3, 3] + (res00 * res01);
                        }
                        rc[3, 1] = rc[3, 1] + (res00 * res01);
                        break;

                    case "外作製品":

                        if ((res00 * res01) >= 50000)
                        {
                            rc[4, 2] = rc[4, 2] + (res00 * res01);
                        }
                        else
                        {
                            rc[4, 3] = rc[4, 3] + (res00 * res01);
                        }
                        rc[4, 1] = rc[4, 1] + (res00 * res01);
                        break;

                    default:

                        if ((res00 * res01) >= 50000)
                        {
                            rc[5, 2] = rc[5, 2] + (res00 * res01);
                        }
                        else
                        {
                            rc[5, 3] = rc[5, 3] + (res00 * res01);
                        }
                        rc[5, 1] = rc[5, 1] + (res00 * res01);
                        break;
                }
            }
            textBox4.Text = string.Format("{0:#,0}", rc[1, 1]);
            textBox8.Text = string.Format("{0:#,0}", rc[2, 1]);
            textBox11.Text = string.Format("{0:#,0}", rc[3, 1]);
            textBox14.Text = string.Format("{0:#,0}", rc[4, 1]);
            textBox17.Text = string.Format("{0:#,0}", rc[5, 1]);
            textBox20.Text = string.Format("{0:#,0}", rc[1, 1] + rc[2, 1] + rc[3, 1] + rc[4, 1] + rc[5, 1]);
            textBox6.Text = string.Format("{0:#,0}", rc[1, 2]);
            textBox9.Text = string.Format("{0:#,0}", rc[2, 2]);
            textBox12.Text = string.Format("{0:#,0}", rc[3, 2]);
            textBox15.Text = string.Format("{0:#,0}", rc[4, 2]);
            textBox18.Text = string.Format("{0:#,0}", rc[5, 2]);
            textBox21.Text = string.Format("{0:#,0}", rc[1, 2] + rc[2, 2] + rc[3, 2] + rc[4, 2] + rc[5, 2]);
            textBox7.Text = string.Format("{0:#,0}", rc[1, 3]);
            textBox10.Text = string.Format("{0:#,0}", rc[2, 3]);
            textBox13.Text = string.Format("{0:#,0}", rc[3, 3]);
            textBox16.Text = string.Format("{0:#,0}", rc[4, 3]);
            textBox19.Text = string.Format("{0:#,0}", rc[5, 3]);
            textBox22.Text = string.Format("{0:#,0}", rc[1, 3] + rc[2, 3] + rc[3, 3] + rc[4, 3] + rc[5, 3]);
            label16.Text = dr.Length.ToString() + "件取得しました";
        }

        private void button1_Click(object sender, EventArgs e)
        {
            textBox1.Text = "";
            textBox2.Text = "";
            textBox3.Text = "";
            textBox5.Text = "";
            comboBox1.Text = "";
            comboBox2.Text = "";
            checkBox1.Checked = false;
            comboBox2.Enabled = false;
            textBox23.Text = "";
            textBox24.Text = "";
            textBox25.Text = "";
            textBox26.Text = "";
            textBox27.Text = "";
            textBox28.Text = "";
            textBox29.Text = "";
            textBox30.Text = "";
            init_view(sender, e);
        }

        private void dataGridView1_CellMouseDown(object sender, DataGridViewCellMouseEventArgs e)
        {
            if (e.Button == MouseButtons.Right)
            {
                dataGridView1.ClearSelection();

                if (e.RowIndex >= 0 && e.ColumnIndex >= 0) dataGridView1.Rows[e.RowIndex].Selected = true;

                foreach (ToolStripMenuItem tsmi in contextMenuStrip1.Items)
                {
                    if (tsmi.Text == "出庫取消")
                    {
                        contextMenuStrip1.Items.Remove(tsmi);
                        break;
                    }
                }

                if (checkBox3.Checked || checkBox5.Checked) contextMenuStrip1.Items.Add("出庫取消");
            }
        }

        private string[] parse_num(string[] tmp)
        {
            string[] res = new string[3];

            decimal out001;
            if (tmp[0] == "" || !decimal.TryParse(tmp[0], out out001))
            {
                res[0] = "0";
            }
            else
            {
                res[0] = out001.ToString();
            }

            decimal out002;
            if (tmp[1] == "" || !decimal.TryParse(tmp[1], out out002))
            {
                res[1] = "0";
            }
            else
            {
                res[1] = out002.ToString();
            }

            DateTime out003;

            if (tmp[2].Length == 6 && tmp[2].Substring(4, 1) != "/")
            {
                tmp[2] = tmp[2].Substring(0, 4) + "/" + tmp[2].Substring(4, 2) + "/1";
            }
            else if (tmp[2].Length == 6 && tmp[2].Substring(4, 1) == "/")
            {
                tmp[2] = tmp[2].Substring(0, 4) + "/" + tmp[2].Substring(5, 1) + "/1";
            }
            else if (tmp[2].Length == 7 && tmp[2].Substring(4, 1) == "/")
            {
                tmp[2] = tmp[2].Substring(0, 4) + "/" + tmp[2].Substring(5, 2) + "/1";
            }

            if (tmp[2] == "" || !DateTime.TryParse(tmp[2], out out003))
            {
                res[2] = "1970/01/01";
            }
            else
            {
                res[2] = out003.ToString("yyyy/MM/01");
            }

            return res;
        }

        private void contextMenuStrip1_ItemClicked(object sender, ToolStripItemClickedEventArgs e)
        {
            if (dataGridView1.Rows.Count == 0) return;
            set_selection(dataGridView1.SelectedRows[0].Index);

            string q = "";

            switch (e.ClickedItem.Text.ToString())
            {
                case "更新":

                    if (dgvr.Cells[4].Value == null || dgvr.Cells[5].Value == null || dgvr.Cells[8].Value == null)
                    {
                        MessageBox.Show("数量、計上単価、仕入月が空白では処理できません。");
                        return;
                    }

                    string[] res = parse_num(new string[] {
                        dgvr.Cells[4].Value.ToString(),
                        dgvr.Cells[5].Value.ToString(),
                        dgvr.Cells[8].Value.ToString()
                    } );

                    q = @"update tbl set " +
                        "工番='" + dgvr.Cells[1].Value.ToString() + "'," +
                        "製品名称='" + dgvr.Cells[2].Value.ToString() + "'," +
                        "仕入先='" + dgvr.Cells[3].Value.ToString() + "'," +
                        "数量=" + res[0] + "," +
                        "計上単価=" + res[1] + "," +
                        "区分='" + dgvr.Cells[7].Value.ToString() + "'," +
                        "仕入月=#" + res[2] + "#," +
                        "備考='" + dgvr.Cells[9].Value.ToString() + "' " +
                        "where ID = " + dgvr.Cells[0].Value.ToString() + "";

                    break;

                case "出庫":

                    q = @"update tbl set " +
                        "出庫日=#" + DateTime.Now.ToString("yyyy/MM/dd") + "# " +
                        "where ID = " + dgvr.Cells[0].Value.ToString() + "";

                    break;

                case "出庫取消":

                    q = @"update tbl set " +
                    "出庫日=NULL " +
                    "where ID = " + dgvr.Cells[0].Value.ToString() + "";

                    break;

                case "複写":

                    q = @"insert into tbl select 工番,製品名称,仕入先,数量,計上単価,区分,仕入月,備考,出庫日,入庫日 from tbl " + 
                        "where ID = " + dgvr.Cells[0].Value.ToString() + "";

                    break;

                case "削除":

                    q = @"delete from tbl " +
                        "where ID = " + dgvr.Cells[0].Value.ToString() + "";
                    break;
            }

            Util.exeQry(q);

            init();
            init_view(sender, e);
            init_selection(e.ClickedItem.Text.ToString());
            MessageBox.Show(e.ClickedItem.Text.ToString() + "しました。");
        }

        private void init_selection(string tmp)
        {
            if (dataGridView1.Rows.Count == 0) return;

            switch (tmp)
            {
                case "更新":
                case "出庫":
                case "削除":

                    if (dgv_rows_count == dataGridView1.Rows.Count)
                    {
                        dataGridView1.Rows[dgv_selected].Selected = true;
                        dataGridView1.FirstDisplayedScrollingRowIndex = dgv_view_index;
                    }
                    else
                    {
                        if (dgv_selected >= 1) dataGridView1.Rows[dgv_selected - 1].Selected = true;
                        dataGridView1.FirstDisplayedScrollingRowIndex = dgv_view_index;
                    }
                    break;

                case "複写":
                case "新規":

                    dataGridView1.Rows[dataGridView1.Rows.Count - 1].Selected = true;
                    dataGridView1.FirstDisplayedScrollingRowIndex = dataGridView1.Rows.Count - 1;
                    break;
            }
        }

        private DataGridViewRow dgvr;
        private int dgv_view_index;
        private int dgv_selected;
        private int dgv_rows_count;

        private void set_selection(int rows_index)
        {
            dgvr = dataGridView1.SelectedRows[0];
            dgv_view_index = dataGridView1.FirstDisplayedScrollingRowIndex;
            dgv_selected = rows_index;
            dgv_rows_count = dataGridView1.Rows.Count;
        }

        private void comboBox1_TextChanged(object sender, EventArgs e)
        {
            DateTime out001;
            if (!DateTime.TryParse(comboBox1.Text, out out001)) comboBox1.Text = "";
            init_view(sender, e);
        }

        private void comboBox2_TextChanged(object sender, EventArgs e)
        {
            DateTime out001;
            if (!DateTime.TryParse(comboBox2.Text, out out001)) comboBox2.Text = "";
            init_view(sender, e);
        }

        private void checkBox1_CheckedChanged(object sender, EventArgs e)
        {
            if(checkBox1.Checked)
            {
                comboBox2.Enabled = true;
            }
            else
            {
                comboBox2.Text = "";
                comboBox2.Enabled = false;
            }
            init_view(sender, e);
        }

        private void button2_Click(object sender, EventArgs e)
        {
            checkBox3.Checked = false;

            string[] res = parse_num(new string[] {
                        textBox26.Text,
                        textBox27.Text,
                        textBox29.Text
                    });

            string q = @"insert into tbl (工番,製品名称,仕入先,数量,計上単価,区分,仕入月,備考,入庫日) values " +
                "('" + textBox23.Text + "','" + textBox24.Text + "','" + textBox25.Text + "'" +
                "," + res[0] + "," + res[1] + ",'" + textBox28.Text + "',#" + res[2] + "#,'" + textBox30.Text + "',#" + DateTime.Now.ToString("yyyy/MM/dd") + "#)";

            Util.exeQry(q);

            init();
            init_view(sender, e);
            init_selection("新規");
            button1.PerformClick();
            MessageBox.Show("新規登録しました。");
        }

        private void btn5678_click(object sender, EventArgs e)
        {
            switch (((Button)sender).Text)
            {
                case "更新":
                    contextMenuStrip1.Items[0].PerformClick();
                    break;
                case "出庫":
                    contextMenuStrip1.Items[1].PerformClick();
                    break;
                case "複写":
                    contextMenuStrip1.Items[2].PerformClick();
                    break;
                case "削除":
                    contextMenuStrip1.Items[3].PerformClick();
                    break;
            }
        }

        private void btn34_click(object sender, EventArgs e)
        {
            DialogResult res = MessageBox.Show("表示されている行が" + ((Button)sender).Text + "されます。実行しますか？","",MessageBoxButtons.OKCancel);
            if (res == DialogResult.Cancel) return;

            for (int r = 0; r < dataGridView1.Rows.Count; r++)
            {
                string q = "";

                if (((Button)sender).Text == "全て出庫")
                {
                    q = @"update tbl set " +
                        "出庫日=#" + DateTime.Now.ToString("yyyy/MM/dd") + "# " +
                        "where ID = " + dataGridView1.Rows[r].Cells[0].Value.ToString() + "";
                }
                else
                {
                    q = @"delete from tbl " +
                        "where ID = " + dataGridView1.Rows[r].Cells[0].Value.ToString() + "";
                }

                Text = r + "件目処理中...";
                Util.exeQry(q);

            }
            
            Text = "全在庫管理 v1.0";

            init();
            init_view(sender, e);
            button1.PerformClick();
            MessageBox.Show(((Button)sender).Text + "しました。");
        }

        private void checkBox3_CheckedChanged(object sender, EventArgs e)
        {
            if (checkBox3.Checked)
            {
                checkBox4.Checked = false;
                checkBox5.Checked = false;
            }
            init();
            init_view(sender, e);
            button1.PerformClick();
        }

        private void checkBox4_CheckedChanged(object sender, EventArgs e)
        {
            if (checkBox4.Checked)
            {
                checkBox3.Checked = false;
                checkBox5.Checked = false;
            }
            init();
            init_view(sender, e);
            button1.PerformClick();
        }

        private void checkBox5_CheckedChanged(object sender, EventArgs e)
        {
            if (checkBox5.Checked)
            {
                checkBox3.Checked = false;
                checkBox4.Checked = false;
            }

            init();
            init_view(sender, e);
            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

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

493

494

495

496

497

498

499

500

501

502

503

504

505

506

507

508

509

510

511

512

513

514

515

516

517

518

519

520

521

522

523

524

525

526

527

528

529

530

531

532

533

534

535

536

537

538

539

540

541

542

543

544

545

546

547

548

549

550

551

552

553

554

555

556

557

558

559

560

561

562

563

564

565

566

567

568

569

570

571

572

573

574

575

576

577

578

579

580

581

582

583

584

585

586

587

588

589

590

591

592

593

594

595

596

597

598

599

600

601

602

603

604

605

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.Data.OleDb;

namespace 全在庫管理

{

public partial class Form1 : Form

{

DataTable dt;

public Form1()

{

InitializeComponent();

dataGridView1.MultiSelect = false;

dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;

dataGridView1.AllowUserToAddRows = false;

dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;

comboBox2.Enabled = false;

textBox1.TextChanged += new EventHandler(init_view);

textBox2.TextChanged += new EventHandler(init_view);

textBox3.TextChanged += new EventHandler(init_view);

textBox5.TextChanged += new EventHandler(init_view);

contextMenuStrip1.Items.Add("更新");

contextMenuStrip1.Items.Add("出庫");

contextMenuStrip1.Items.Add("複写");

contextMenuStrip1.Items.Add("削除");

init();

}

~Form1()

{

dt.Dispose();

}

private void init()

{

string q = @"select * from tbl where 出庫日 is null order by ID";

if (checkBox3.Checked) q = @"select * from tbl where 出庫日 is not null order by ID";

if (checkBox4.Checked) q = @"select * from tbl where 入庫日 = #" + DateTime.Now.ToString("yyyy/MM/dd") + "# order by ID";

if (checkBox5.Checked) q = @"select * from tbl where 出庫日 = #" + DateTime.Now.ToString("yyyy/MM/dd") + "# order by ID";

dt = Util.getDt(q);

list_add();

}

public void list_add()

{

comboBox1.Items.Clear();

comboBox2.Items.Clear();

using (DataView dv = new DataView(dt))

{

using (DataTable tmp = dv.ToTable(true, new string[] { "仕入月" }))

{

DataRow[] dr = tmp.Select("仕入月 is not null");

for (int i = 0; i < dr.Length; i++)

{

comboBox1.Items.Add(DateTime.Parse(dr[i]["仕入月"].ToString()).ToString("yyyy年MM月"));

comboBox2.Items.Add(DateTime.Parse(dr[i]["仕入月"].ToString()).ToString("yyyy年MM月"));

}

private void Form1_Load(object sender, EventArgs e)

{

init_view(sender, e);

}

public void init_view(object sender, EventArgs e)

{

dataGridView1.Rows.Clear();

string tmp = "";

bool chk = false;

if (textBox2.Text != "")

{

tmp = "工番 like '%" + textBox2.Text + "%'";

chk = true;

}

if (textBox1.Text != "")

{

if (chk) tmp = tmp + " and ";

tmp = tmp + "製品名称 like '%" + textBox1.Text + "%'";

chk = true;

}

if (textBox3.Text != "")

{

if (chk) tmp = tmp + " and ";

tmp = tmp + " 仕入先 like '%" + textBox3.Text + "%'";

chk = true;

}

if (textBox5.Text != "")

{

if (chk) tmp = tmp + " and ";

tmp = tmp + " 区分 like '%" + textBox5.Text + "%'";

chk = true;

}

if (comboBox1.Text != "" && comboBox2.Enabled == false)

{

if (chk) tmp = tmp + " and ";

tmp = tmp + " 仕入月 = #" + DateTime.Parse(comboBox1.Text).ToString("yyyy/MM/01") + "#";

}

else if (comboBox1.Text != "" && comboBox2.Enabled == true && comboBox2.Text != "")

{

if (chk) tmp = tmp + " and ";

tmp = tmp + " 仕入月 >= #" + DateTime.Parse(comboBox1.Text).ToString("yyyy/MM/01")

+ "# and 仕入月 <= #" + DateTime.Parse(comboBox2.Text).ToString("yyyy/MM/01") + "#";

}

else if (comboBox1.Text != "" && comboBox2.Enabled == true && comboBox2.Text == "")

{

if (chk) tmp = tmp + " and ";

tmp = tmp + " 仕入月 >= #" + DateTime.Parse(comboBox1.Text).ToString("yyyy/MM/01") + "#";

}

else if (comboBox1.Text == "" && comboBox2.Enabled == true && comboBox2.Text != "")

{

if (chk) tmp = tmp + " and ";

tmp = tmp + " 仕入月 <= #" + DateTime.Parse(comboBox2.Text).ToString("yyyy/MM/01") + "#";

}

System.Diagnostics.Debug.Print(tmp.ToString());

DataRow[] dr = dt.Select(tmp, "ID");

decimal[,] rc = new decimal[6, 4];

for (int i = 0; i < dr.Length; i++)

{

decimal res00;

decimal.TryParse(dr[i]["数量"].ToString(), out res00);

decimal res01;

decimal.TryParse(dr[i]["計上単価"].ToString(), out res01);

DateTime res02;

if (!DateTime.TryParse(dr[i]["仕入月"].ToString(), out res02)) res02 = new DateTime(1970, 1, 1);

dataGridView1.Rows.Add(

dr[i]["ID"].ToString(),

dr[i]["工番"].ToString(),

dr[i]["製品名称"].ToString(),

dr[i]["仕入先"].ToString(),

res00.ToString(),

string.Format("{0:#,0}", res01),

string.Format("{0:#,0}", (res00 * res01)),

dr[i]["区分"].ToString(),

res02.ToString("yyyy年MM月"),

dr[i]["備考"].ToString()

);

switch (dr[i]["区分"].ToString())

{

case "内作材料①":

if ((res00 * res01) >= 50000)

{

rc[1, 2] = rc[1, 2] + (res00 * res01);

}

else

{

rc[1, 3] = rc[1, 3] + (res00 * res01);

}

rc[1, 1] = rc[1, 1] + (res00 * res01);

break;

case "外作材料①":

if ((res00 * res01) >= 50000)

{

rc[2, 2] = rc[2, 2] + (res00 * res01);

}

else

{

rc[2, 3] = rc[2, 3] + (res00 * res01);

}

rc[2, 1] = rc[2, 1] + (res00 * res01);

break;

case "内作製品":

if ((res00 * res01) >= 50000)

{

rc[3, 2] = rc[3, 2] + (res00 * res01);

}

else

{

rc[3, 3] = rc[3, 3] + (res00 * res01);

}

rc[3, 1] = rc[3, 1] + (res00 * res01);

break;

case "外作製品":

if ((res00 * res01) >= 50000)

{

rc[4, 2] = rc[4, 2] + (res00 * res01);

}

else

{

rc[4, 3] = rc[4, 3] + (res00 * res01);

}

rc[4, 1] = rc[4, 1] + (res00 * res01);

break;

default:

if ((res00 * res01) >= 50000)

{

rc[5, 2] = rc[5, 2] + (res00 * res01);

}

else

{

rc[5, 3] = rc[5, 3] + (res00 * res01);

}

rc[5, 1] = rc[5, 1] + (res00 * res01);

break;

}

textBox4.Text = string.Format("{0:#,0}", rc[1, 1]);

textBox8.Text = string.Format("{0:#,0}", rc[2, 1]);

textBox11.Text = string.Format("{0:#,0}", rc[3, 1]);

textBox14.Text = string.Format("{0:#,0}", rc[4, 1]);

textBox17.Text = string.Format("{0:#,0}", rc[5, 1]);

textBox20.Text = string.Format("{0:#,0}", rc[1, 1] + rc[2, 1] + rc[3, 1] + rc[4, 1] + rc[5, 1]);

textBox6.Text = string.Format("{0:#,0}", rc[1, 2]);

textBox9.Text = string.Format("{0:#,0}", rc[2, 2]);

textBox12.Text = string.Format("{0:#,0}", rc[3, 2]);

textBox15.Text = string.Format("{0:#,0}", rc[4, 2]);

textBox18.Text = string.Format("{0:#,0}", rc[5, 2]);

textBox21.Text = string.Format("{0:#,0}", rc[1, 2] + rc[2, 2] + rc[3, 2] + rc[4, 2] + rc[5, 2]);

textBox7.Text = string.Format("{0:#,0}", rc[1, 3]);

textBox10.Text = string.Format("{0:#,0}", rc[2, 3]);

textBox13.Text = string.Format("{0:#,0}", rc[3, 3]);

textBox16.Text = string.Format("{0:#,0}", rc[4, 3]);

textBox19.Text = string.Format("{0:#,0}", rc[5, 3]);

textBox22.Text = string.Format("{0:#,0}", rc[1, 3] + rc[2, 3] + rc[3, 3] + rc[4, 3] + rc[5, 3]);

label16.Text = dr.Length.ToString() + "件取得しました";

}

private void button1_Click(object sender, EventArgs e)

{

textBox1.Text = "";

textBox2.Text = "";

textBox3.Text = "";

textBox5.Text = "";

comboBox1.Text = "";

comboBox2.Text = "";

checkBox1.Checked = false;

comboBox2.Enabled = false;

textBox23.Text = "";

textBox24.Text = "";

textBox25.Text = "";

textBox26.Text = "";

textBox27.Text = "";

textBox28.Text = "";

textBox29.Text = "";

textBox30.Text = "";

init_view(sender, e);

}

private void dataGridView1_CellMouseDown(object sender, DataGridViewCellMouseEventArgs e)

{

if (e.Button == MouseButtons.Right)

{

dataGridView1.ClearSelection();

if (e.RowIndex >= 0 && e.ColumnIndex >= 0) dataGridView1.Rows[e.RowIndex].Selected = true;

foreach (ToolStripMenuItem tsmi in contextMenuStrip1.Items)

{

if (tsmi.Text == "出庫取消")

{

contextMenuStrip1.Items.Remove(tsmi);

break;

}

if (checkBox3.Checked || checkBox5.Checked) contextMenuStrip1.Items.Add("出庫取消");

}

private string[] parse_num(string[] tmp)

{

string[] res = new string[3];

decimal out001;

if (tmp[0] == "" || !decimal.TryParse(tmp[0], out out001))

{

res[0] = "0";

}

else

{

res[0] = out001.ToString();

}

decimal out002;

if (tmp[1] == "" || !decimal.TryParse(tmp[1], out out002))

{

res[1] = "0";

}

else

{

res[1] = out002.ToString();

}

DateTime out003;

if (tmp[2].Length == 6 && tmp[2].Substring(4, 1) != "/")

{

tmp[2] = tmp[2].Substring(0, 4) + "/" + tmp[2].Substring(4, 2) + "/1";

}

else if (tmp[2].Length == 6 && tmp[2].Substring(4, 1) == "/")

{

tmp[2] = tmp[2].Substring(0, 4) + "/" + tmp[2].Substring(5, 1) + "/1";

}

else if (tmp[2].Length == 7 && tmp[2].Substring(4, 1) == "/")

{

tmp[2] = tmp[2].Substring(0, 4) + "/" + tmp[2].Substring(5, 2) + "/1";

}

if (tmp[2] == "" || !DateTime.TryParse(tmp[2], out out003))

{

res[2] = "1970/01/01";

}

else

{

res[2] = out003.ToString("yyyy/MM/01");

}

return res;

}

private void contextMenuStrip1_ItemClicked(object sender, ToolStripItemClickedEventArgs e)

{

if (dataGridView1.Rows.Count == 0) return;

set_selection(dataGridView1.SelectedRows[0].Index);

string q = "";

switch (e.ClickedItem.Text.ToString())

{

case "更新":

if (dgvr.Cells[4].Value == null || dgvr.Cells[5].Value == null || dgvr.Cells[8].Value == null)

{

MessageBox.Show("数量、計上単価、仕入月が空白では処理できません。");

return;

}

string[] res = parse_num(new string[] {

dgvr.Cells[4].Value.ToString(),

dgvr.Cells[5].Value.ToString(),

dgvr.Cells[8].Value.ToString()

} );

q = @"update tbl set " +

"工番='" + dgvr.Cells[1].Value.ToString() + "'," +

"製品名称='" + dgvr.Cells[2].Value.ToString() + "'," +

"仕入先='" + dgvr.Cells[3].Value.ToString() + "'," +

"数量=" + res[0] + "," +

"計上単価=" + res[1] + "," +

"区分='" + dgvr.Cells[7].Value.ToString() + "'," +

"仕入月=#" + res[2] + "#," +

"備考='" + dgvr.Cells[9].Value.ToString() + "' " +

"where ID = " + dgvr.Cells[0].Value.ToString() + "";

break;

case "出庫":

q = @"update tbl set " +

"出庫日=#" + DateTime.Now.ToString("yyyy/MM/dd") + "# " +

"where ID = " + dgvr.Cells[0].Value.ToString() + "";

break;

case "出庫取消":

q = @"update tbl set " +

"出庫日=NULL " +

"where ID = " + dgvr.Cells[0].Value.ToString() + "";

break;

case "複写":

q = @"insert into tbl select 工番,製品名称,仕入先,数量,計上単価,区分,仕入月,備考,出庫日,入庫日 from tbl " +

"where ID = " + dgvr.Cells[0].Value.ToString() + "";

break;

case "削除":

q = @"delete from tbl " +

"where ID = " + dgvr.Cells[0].Value.ToString() + "";

break;

}

Util.exeQry(q);

init();

init_view(sender, e);

init_selection(e.ClickedItem.Text.ToString());

MessageBox.Show(e.ClickedItem.Text.ToString() + "しました。");

}

private void init_selection(string tmp)

{

if (dataGridView1.Rows.Count == 0) return;

switch (tmp)

{

case "更新":

case "出庫":

case "削除":

if (dgv_rows_count == dataGridView1.Rows.Count)

{

dataGridView1.Rows[dgv_selected].Selected = true;

dataGridView1.FirstDisplayedScrollingRowIndex = dgv_view_index;

}

else

{

if (dgv_selected >= 1) dataGridView1.Rows[dgv_selected - 1].Selected = true;

dataGridView1.FirstDisplayedScrollingRowIndex = dgv_view_index;

}

break;

case "複写":

case "新規":

dataGridView1.Rows[dataGridView1.Rows.Count - 1].Selected = true;

dataGridView1.FirstDisplayedScrollingRowIndex = dataGridView1.Rows.Count - 1;

break;

}

private DataGridViewRow dgvr;

private int dgv_view_index;

private int dgv_selected;

private int dgv_rows_count;

private void set_selection(int rows_index)

{

dgvr = dataGridView1.SelectedRows[0];

dgv_view_index = dataGridView1.FirstDisplayedScrollingRowIndex;

dgv_selected = rows_index;

dgv_rows_count = dataGridView1.Rows.Count;

}

private void comboBox1_TextChanged(object sender, EventArgs e)

{

DateTime out001;

if (!DateTime.TryParse(comboBox1.Text, out out001)) comboBox1.Text = "";

init_view(sender, e);

}

private void comboBox2_TextChanged(object sender, EventArgs e)

{

DateTime out001;

if (!DateTime.TryParse(comboBox2.Text, out out001)) comboBox2.Text = "";

init_view(sender, e);

}

private void checkBox1_CheckedChanged(object sender, EventArgs e)

{

if(checkBox1.Checked)

{

comboBox2.Enabled = true;

}

else

{

comboBox2.Text = "";

comboBox2.Enabled = false;

}

init_view(sender, e);

}

private void button2_Click(object sender, EventArgs e)

{

checkBox3.Checked = false;

string[] res = parse_num(new string[] {

textBox26.Text,

textBox27.Text,

textBox29.Text

});

string q = @"insert into tbl (工番,製品名称,仕入先,数量,計上単価,区分,仕入月,備考,入庫日) values " +

"('" + textBox23.Text + "','" + textBox24.Text + "','" + textBox25.Text + "'" +

"," + res[0] + "," + res[1] + ",'" + textBox28.Text + "',#" + res[2] + "#,'" + textBox30.Text + "',#" + DateTime.Now.ToString("yyyy/MM/dd") + "#)";

Util.exeQry(q);

init();

init_view(sender, e);

init_selection("新規");

button1.PerformClick();

MessageBox.Show("新規登録しました。");

}

private void btn5678_click(object sender, EventArgs e)

{

switch (((Button)sender).Text)

{

case "更新":

contextMenuStrip1.Items[0].PerformClick();

break;

case "出庫":

contextMenuStrip1.Items[1].PerformClick();

break;

case "複写":

contextMenuStrip1.Items[2].PerformClick();

break;

case "削除":

contextMenuStrip1.Items[3].PerformClick();

break;

}

private void btn34_click(object sender, EventArgs e)

{

DialogResult res = MessageBox.Show("表示されている行が" + ((Button)sender).Text + "されます。実行しますか？","",MessageBoxButtons.OKCancel);

if (res == DialogResult.Cancel) return;

for (int r = 0; r < dataGridView1.Rows.Count; r++)

{

string q = "";

if (((Button)sender).Text == "全て出庫")

{

q = @"update tbl set " +

"出庫日=#" + DateTime.Now.ToString("yyyy/MM/dd") + "# " +

"where ID = " + dataGridView1.Rows[r].Cells[0].Value.ToString() + "";

}

else

{

q = @"delete from tbl " +

"where ID = " + dataGridView1.Rows[r].Cells[0].Value.ToString() + "";

}

Text = r + "件目処理中...";

Util.exeQry(q);

}

Text = "全在庫管理 v1.0";

init();

init_view(sender, e);

button1.PerformClick();

MessageBox.Show(((Button)sender).Text + "しました。");

}

private void checkBox3_CheckedChanged(object sender, EventArgs e)

{

if (checkBox3.Checked)

{

checkBox4.Checked = false;

checkBox5.Checked = false;

}

init();

init_view(sender, e);

button1.PerformClick();

}

private void checkBox4_CheckedChanged(object sender, EventArgs e)

{

if (checkBox4.Checked)

{

checkBox3.Checked = false;

checkBox5.Checked = false;

}

init();

init_view(sender, e);

button1.PerformClick();

}

private void checkBox5_CheckedChanged(object sender, EventArgs e)

{

if (checkBox5.Checked)

{

checkBox3.Checked = false;

checkBox4.Checked = false;

}

init();

init_view(sender, e);

button1.PerformClick();

}

Util.cs

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

namespace 全在庫管理
{
    class Util
    {
        public static void exeQry(string q)
        {
            using (OleDbConnection con = new OleDbConnection(@"provider=microsoft.jet.oledb.4.0;data source=" + Application.StartupPath + @"\zen_zaiko.mdb"))
            {
                try
                {
                    con.Open();
                    OleDbCommand cmd = new OleDbCommand(q, con);
                    cmd.ExecuteNonQuery();
                }
                finally
                {
                    con.Close();
                }
            }
        }

        public static DataTable getDt(string q)
        {
            DataTable dt;

            using (OleDbConnection con = new OleDbConnection(@"provider=microsoft.jet.oledb.4.0;data source=" + Application.StartupPath + @"\zen_zaiko.mdb"))
            {
                try
                {
                    con.Open();
                    OleDbCommand cmd = new OleDbCommand(q, con);
                    using (OleDbDataReader dr = cmd.ExecuteReader())
                    {
                        dt = new DataTable();
                        dt.Load(dr);
                        dr.Close();
                    }
                }
                finally
                {
                    con.Close();
                }

            }
            return dt;
        }
    }
}

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Data;

using System.Data.OleDb;

using System.Windows.Forms;

namespace 全在庫管理

{

class Util

{

public static void exeQry(string q)

{

using (OleDbConnection con = new OleDbConnection(@"provider=microsoft.jet.oledb.4.0;data source=" + Application.StartupPath + @"\zen_zaiko.mdb"))

{

try

{

con.Open();

OleDbCommand cmd = new OleDbCommand(q, con);

cmd.ExecuteNonQuery();

}

finally

{

con.Close();

}

public static DataTable getDt(string q)

{

DataTable dt;

using (OleDbConnection con = new OleDbConnection(@"provider=microsoft.jet.oledb.4.0;data source=" + Application.StartupPath + @"\zen_zaiko.mdb"))

{

try

{

con.Open();

OleDbCommand cmd = new OleDbCommand(q, con);

using (OleDbDataReader dr = cmd.ExecuteReader())

{

dt = new DataTable();

dt.Load(dr);

dr.Close();

}

finally

{

con.Close();

}

return dt;

}

アップロードVBA

Public frm_type As String
Public frm_date As String
Public frm_book As String
Public frm_chk As Boolean

Sub upload_zaiko()

On Error GoTo ex

frm_chk = False

UserForm1.Show

If frm_chk = False Then Exit Sub

If frm_type = "" Then

  MsgBox "区分を選択してください。終了します。"
  Exit Sub

End If

If frm_date = "" Then

  MsgBox "仕入月を選択してください。終了します。"
  Exit Sub

End If

If frm_book = "" Then

  MsgBox "対象ブックを選択してください。終了します。"
  Exit Sub

End If

Application.ScreenUpdating = False
Application.DisplayAlerts = False

p = ThisWorkbook.Path & "\zen_zaiko.mdb"

t = Selection(1).Row
b = Selection(Selection.Count).Row

Set con = CreateObject("adodb.connection")
Set res = CreateObject("adodb.recordset")

con.Open "provider=microsoft.jet.oledb.4.0;data source=" & p

Dim buf(9) As String

For r = t To b

  If frm_book = "仕入台帳" Then
  
    buf(1) = h(Cells(r, 10).Text, "str") '工番
    buf(2) = h(Cells(r, 5).Text & " " & Cells(r, 6).Text & " " & Cells(r, 7).Text, "str") & "(" & h(Cells(r, 12).Text, "str") & ")" '製品名称/型式
    buf(3) = h(Cells(r, 3).Text, "str") '仕入先
    buf(4) = h(Cells(r, 15).Text, "int") '数量
    buf(5) = h(Cells(r, 18).Text, "cur") '計上単価
    buf(6) = frm_type '区分
    buf(7) = Format(CDate(frm_date), "yyyy/MM/01") '仕入月
    buf(8) = "" '備考
    buf(9) = Format(Year(Date) & "/" & Month(Date) & "/" & Day(Date), "yyyy/MM/dd") '入庫日
    
  ElseIf frm_book = "工程表" Then
  
    buf(1) = h(Cells(r, 3).Text, "str") '工番
    buf(2) = h(Cells(r, 2).Text, "str") & "(" & h(Cells(r, 102).Text, "str") & ")" '製品名称/客先
    buf(3) = h(ActiveSheet.Name, "str") '班
    buf(4) = h(Cells(r, 5).Text, "int") '数量
    buf(5) = h(Cells(r, 104).Text, "cur") '計上単価
    buf(6) = frm_type '区分
    buf(7) = Format(CDate(frm_date), "yyyy/MM/01") '仕入月
    buf(8) = "" '備考
    buf(9) = Format(Year(Date) & "/" & Month(Date) & "/" & Day(Date), "yyyy/MM/dd") '入庫日
    
  End If
  
  q = "insert into tbl (工番,製品名称,仕入先,数量,計上単価,区分,仕入月,備考,入庫日) values (" & _
  "'" & buf(1) & "'," & _
  "'" & buf(2) & "'," & _
  "'" & buf(3) & "'," & _
  buf(4) & "," & _
  buf(5) & "," & _
  "'" & buf(6) & "'," & _
  "'" & buf(7) & "'," & _
  "'" & buf(8) & "'," & _
  "'" & buf(9) & "')"
  
  con.Execute (q)

Next r

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

Application.ScreenUpdating = True
Application.DisplayAlerts = True

MsgBox "終了しました。"

Exit Sub

ex:

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

  Application.ScreenUpdating = True
  Application.DisplayAlerts = True

  MsgBox "エラーが発生しました。"

End Sub

Function h(str, chk)

On Error GoTo ex

If (chk = "int" Or chk = "cur") And str = "" Then

  h = 0
  Exit Function

End If

If chk = "int" Then

  If IsNumeric(str) Then
  
    h = CInt(str)
    Exit Function
  
  Else
  
    h = 0
    Exit Function
  
  End If

End If

If chk = "cur" Then

  If IsNumeric(str) Then
  
    h = CCur(str)
    Exit Function
  
  Else
  
    h = 0
    Exit Function
  
  End If

End If

If chk = "str" And str <> "" Then

  str = Replace(str, ",", "，")
  str = Replace(str, "'", "’")
  '全角空白
  str = Replace(str, "　", " ")
  '半角空白
  str = Replace(str, "  ", " ")
  str = Replace(str, "  ", " ")
  str = Replace(str, "  ", " ")
  
  h = str
  
  Exit Function

ElseIf chk = "str" And str = "" Then

  h = ""
  
  Exit Function

End If

Exit Function

ex:

  MsgBox "文字列の変換エラーが発生しました。"
  h = 0

End Function

Sub auto_open()

On Error Resume Next

Application.CommandBars("cell").Controls("全在庫へアップロード").Delete

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

  .Caption = "全在庫へアップロード"
  .OnAction = "upload_zaiko"

End With

End Sub

Sub auto_close()

On Error Resume Next

Application.CommandBars("cell").Controls("全在庫へアップロード").Delete
Application.CommandBars("cell").Controls("全在庫へアップロード").Delete

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

Public frm_type As String

Public frm_date As String

Public frm_book As String

Public frm_chk As Boolean

Sub upload_zaiko()

On Error GoTo ex

frm_chk = False

UserForm1.Show

If frm_chk = False Then Exit Sub

If frm_type = "" Then

MsgBox "区分を選択してください。終了します。"

Exit Sub

End If

If frm_date = "" Then

MsgBox "仕入月を選択してください。終了します。"

Exit Sub

End If

If frm_book = "" Then

MsgBox "対象ブックを選択してください。終了します。"

Exit Sub

End If

Application.ScreenUpdating = False

Application.DisplayAlerts = False

p = ThisWorkbook.Path & "\zen_zaiko.mdb"

t = Selection(1).Row

b = Selection(Selection.Count).Row

Set con = CreateObject("adodb.connection")

Set res = CreateObject("adodb.recordset")

con.Open "provider=microsoft.jet.oledb.4.0;data source=" & p

Dim buf(9) As String

For r = t To b

If frm_book = "仕入台帳" Then

buf(1) = h(Cells(r, 10).Text, "str") '工番

buf(2) = h(Cells(r, 5).Text & " " & Cells(r, 6).Text & " " & Cells(r, 7).Text, "str") & "(" & h(Cells(r, 12).Text, "str") & ")" '製品名称/型式

buf(3) = h(Cells(r, 3).Text, "str") '仕入先

buf(4) = h(Cells(r, 15).Text, "int") '数量

buf(5) = h(Cells(r, 18).Text, "cur") '計上単価

buf(6) = frm_type '区分

buf(7) = Format(CDate(frm_date), "yyyy/MM/01") '仕入月

buf(8) = "" '備考

buf(9) = Format(Year(Date) & "/" & Month(Date) & "/" & Day(Date), "yyyy/MM/dd") '入庫日

ElseIf frm_book = "工程表" Then

buf(1) = h(Cells(r, 3).Text, "str") '工番

buf(2) = h(Cells(r, 2).Text, "str") & "(" & h(Cells(r, 102).Text, "str") & ")" '製品名称/客先

buf(3) = h(ActiveSheet.Name, "str") '班

buf(4) = h(Cells(r, 5).Text, "int") '数量

buf(5) = h(Cells(r, 104).Text, "cur") '計上単価

buf(6) = frm_type '区分

buf(7) = Format(CDate(frm_date), "yyyy/MM/01") '仕入月

buf(8) = "" '備考

buf(9) = Format(Year(Date) & "/" & Month(Date) & "/" & Day(Date), "yyyy/MM/dd") '入庫日

End If

q = "insert into tbl (工番,製品名称,仕入先,数量,計上単価,区分,仕入月,備考,入庫日) values (" & _

"'" & buf(1) & "'," & _

"'" & buf(2) & "'," & _

"'" & buf(3) & "'," & _

buf(4) & "," & _

buf(5) & "," & _

"'" & buf(6) & "'," & _

"'" & buf(7) & "'," & _

"'" & buf(8) & "'," & _

"'" & buf(9) & "')"

con.Execute (q)

Next r

If con.State = 1 Then con.Close

Set con = Nothing

Application.ScreenUpdating = True

Application.DisplayAlerts = True

MsgBox "終了しました。"

Exit Sub

ex:

If con.State = 1 Then con.Close

Set con = Nothing

Application.ScreenUpdating = True

Application.DisplayAlerts = True

MsgBox "エラーが発生しました。"

End Sub

Function h(str, chk)

On Error GoTo ex

If (chk = "int" Or chk = "cur") And str = "" Then

h = 0

Exit Function

End If

If chk = "int" Then

If IsNumeric(str) Then

h = CInt(str)

Exit Function

Else

h = 0

Exit Function

End If

If chk = "cur" Then

If IsNumeric(str) Then

h = CCur(str)

Exit Function

Else

h = 0

Exit Function

End If

If chk = "str" And str <> "" Then

str = Replace(str, ",", "，")

str = Replace(str, "'", "’")

'全角空白

str = Replace(str, "　", " ")

'半角空白

str = Replace(str, " ", " ")

h = str

Exit Function

ElseIf chk = "str" And str = "" Then

h = ""

Exit Function

End If

Exit Function

ex:

MsgBox "文字列の変換エラーが発生しました。"

h = 0

End Function

Sub auto_open()

On Error Resume Next

Application.CommandBars("cell").Controls("全在庫へアップロード").Delete

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

.Caption = "全在庫へアップロード"

.OnAction = "upload_zaiko"

End With

End Sub

Sub auto_close()

On Error Resume Next

Application.CommandBars("cell").Controls("全在庫へアップロード").Delete

End Sub

2017-03-062017-03-06

AccessVBA テーブル作成、他

Sub test()

'テーブル作成 --------------------------------------------------------

Set t = CurrentDb.CreateTableDef("ttest")
Set f = t.CreateField("id", dbDate)

t.Fields.Append f
CurrentDb.TableDefs.Append t

'パススルークエリ作成 --------------------------------------------------------
'SQLServerへ接続

queryName = "test"

For i = 0 To CurrentDb.QueryDefs.Count - 1
  If CurrentDb.QueryDefs(i).Name = queryName Then CurrentDb.QueryDefs.Delete queryName
Next i

Set t = CurrentDb.CreateQueryDef(queryName)

With t
.Connect = "ODBC; Driver=SQL Server; Server=ESPRIMO\SQLEXPRESS; Database=my_database"
.ReturnsRecords = True
.SQL = "SELECT * FROM tbl;"
End With

Set t = Nothing

'更新系などのパススルークエリも作成できる --------------------------------------------------------

queryName = "test"

For i = 0 To CurrentDb.QueryDefs.Count - 1
  If CurrentDb.QueryDefs(i).Name = queryName Then CurrentDb.QueryDefs.Delete queryName
Next i

Set t = CurrentDb.CreateQueryDef(queryName)

With t
.Connect = "ODBC; Driver=SQL Server; Server=ESPRIMO\SQLEXPRESS;Database=my_database"
.ReturnsRecords = False
.SQL = "DELETE FROM tbl;"
End With

Set t = Nothing

'リンクテーブル(mdbへ接続) --------------------------------------------------------

Set t = CurrentDb.CreateTableDef("AccessLink")
t.Connect = ";DATABASE=" & Application.CurrentProject.Path & "/tmp.mdb;"
t.SourceTableName = "tbl"
CurrentDb.TableDefs.Append t

'テーブル削除 --------------------------------------------------------

tableName = "test_tbl"

For i = 0 To CurrentDb.TableDefs.Count - 1

  If CurrentDb.TableDefs(i).Name = tableName Then
    CurrentDb.TableDefs.Delete tableName
    Exit For
  End If

Next i

'クエリの実行 --------------------------------------------------------

'new_tableを作成しold_tableのデータをnew_tableへ入れる。
CurrentDb.Execute "select * into new_table from old_table"

'new_tableにold_tableのデータを入れる。
CurrentDb.Execute "insert into new_table select * from old_table"

End Sub

Sub test()

'テーブル作成 --------------------------------------------------------

Set t = CurrentDb.CreateTableDef("ttest")

Set f = t.CreateField("id", dbDate)

t.Fields.Append f

CurrentDb.TableDefs.Append t

'パススルークエリ作成 --------------------------------------------------------

'SQLServerへ接続

queryName = "test"

For i = 0 To CurrentDb.QueryDefs.Count - 1

If CurrentDb.QueryDefs(i).Name = queryName Then CurrentDb.QueryDefs.Delete queryName

Next i

Set t = CurrentDb.CreateQueryDef(queryName)

With t

.Connect = "ODBC; Driver=SQL Server; Server=ESPRIMO\SQLEXPRESS; Database=my_database"

.ReturnsRecords = True

.SQL = "SELECT * FROM tbl;"

End With

Set t = Nothing

'更新系などのパススルークエリも作成できる --------------------------------------------------------

queryName = "test"

For i = 0 To CurrentDb.QueryDefs.Count - 1

If CurrentDb.QueryDefs(i).Name = queryName Then CurrentDb.QueryDefs.Delete queryName

Next i

Set t = CurrentDb.CreateQueryDef(queryName)

With t

.Connect = "ODBC; Driver=SQL Server; Server=ESPRIMO\SQLEXPRESS;Database=my_database"

.ReturnsRecords = False

.SQL = "DELETE FROM tbl;"

End With

Set t = Nothing

'リンクテーブル(mdbへ接続) --------------------------------------------------------

Set t = CurrentDb.CreateTableDef("AccessLink")

t.Connect = ";DATABASE=" & Application.CurrentProject.Path & "/tmp.mdb;"

t.SourceTableName = "tbl"

CurrentDb.TableDefs.Append t

'テーブル削除 --------------------------------------------------------

tableName = "test_tbl"

For i = 0 To CurrentDb.TableDefs.Count - 1

If CurrentDb.TableDefs(i).Name = tableName Then

CurrentDb.TableDefs.Delete tableName

Exit For

End If

Next i

'クエリの実行 --------------------------------------------------------

'new_tableを作成しold_tableのデータをnew_tableへ入れる。

CurrentDb.Execute "select * into new_table from old_table"

'new_tableにold_tableのデータを入れる。

CurrentDb.Execute "insert into new_table select * from old_table"

End Sub

2017-03-06

VBA 農産物売上集計

mdbに登録する予定で作成していたが、途中でエクセルのみにしようと思い中断。

Sub auto_open()

On Error Resume Next

Application.CommandBars("cell").Controls("処理実行").Delete
Application.CommandBars("cell").Controls("シート初期化(貼付前処理)").Delete

With Application.CommandBars("cell").Controls.Add
.FaceId = 18
.Caption = "シート初期化(貼付前処理)"
.OnAction = "SheetsClear"
End With

With Application.CommandBars("cell").Controls.Add
.FaceId = 18
.Caption = "処理実行"
.OnAction = "MainProcedure"
End With

End Sub

Sub auto_close()

On Error Resume Next

Application.CommandBars("cell").Controls("処理実行").Delete
Application.CommandBars("cell").Controls("シート初期化(貼付前処理)").Delete

End Sub

Sub SheetsClear()

Sheets("<実績表集計表>貼付1").Rows("1:1000").Delete
Sheets("<実績表集計表>貼付2").Rows("1:1000").Delete
Sheets("<買取業者別 手数料集計一覧表>貼付").Rows("1:1000").Delete

End Sub

Sub MainProcedure()

On Error GoTo e

YearMonth = InputBox("登録月をYYYY/MMの形式で入力してください。")
YearMonth = StrConv(YearMonth, vbNarrow)
YearMonth = YearMonth & "/01"

If True = IsDate(YearMonth) Then

  yesno = MsgBox(Format(YearMonth, "yyyy年mm月") & "でよろしいですか？", vbYesNo)
  If yesno = vbNo Then Exit Sub

Else

  MsgBox "正しい登録月を入力してください。"
  Exit Sub

End If

If False = DataUpload("<実績表集計表>貼付1", 1, YearMonth) Then GoTo e
If False = DataUpload("<実績表集計表>貼付2", 1, YearMonth) Then GoTo e
If False = DataUpload("<買取業者別 手数料集計一覧表>貼付", 2, YearMonth) Then GoTo e

MsgBox "完了しました。"

Exit Sub

e:
  
  MsgBox "エラーが発生しています。"

End Sub

Function DataUpload(sheetName, sheetType, salesDate)

On Error GoTo e

Set con = CreateObject("adodb.connection")
con.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ThisWorkbook.Path & "/sales.mdb")

Dim w As Worksheet: Set w = Sheets(sheetName)


For r = 1 To w.UsedRange.Rows.Count

  If sheetType = 1 And _
  w.Cells(r, 1).Text <> "" And IsNumeric(w.Cells(r, 1).Text) And _
  w.Cells(r, 8).Text <> "" And IsNumeric(w.Cells(r, 8).Text) Then
  
    partnerCode = w.Cells(r, 1).Text
    partnerName = w.Cells(r, 2).Text
    salesVolume = w.Cells(r, 6).Text
    salesAmount = w.Cells(r, 7).Text
    marginCost = w.Cells(r, 10).Text
    pointCost = w.Cells(r, 18).Text
    overheadCost = w.Cells(r, 19).Text
    
    q = "insert into SalesTable (売上年月日,パートナーID,パートナー名称,売上数量,売上金額,手数料,ポイント負担額,諸経費,登録日時) " & _
    "values (#" & salesDate & "#,'" & partnerCode & "','" & partnerName & "','" & salesVolume & "','" & salesAmount & "','" & marginCost & "','" & pointCost & "','" & overheadCost & "',#" & Now() & "#)"
    
    con.Execute q
  
  End If
  
  If sheetType = 2 And _
  w.Cells(r, 1).Text <> "" And IsNumeric(w.Cells(r, 1).Text) And _
  w.Cells(r, 4).Text <> "" And IsNumeric(w.Cells(r, 4).Text) Then
  
    partnerCode = w.Cells(r, 1).Text
    partnerName = w.Cells(r, 2).Text
    salesVolume = w.Cells(r, 4).Text
    salesAmount = w.Cells(r, 5).Text
    marginCost = w.Cells(r, 7).Text
    pointCost = w.Cells(r, 9).Text
    overheadCost = 0

    q = "insert into SalesTable (売上年月日,パートナーID,パートナー名称,売上数量,売上金額,手数料,ポイント負担額,諸経費,登録日時) " & _
    "values (#" & salesDate & "#,'" & partnerCode & "','" & partnerName & "','" & salesVolume & "','" & salesAmount & "','" & marginCost & "','" & pointCost & "','" & overheadCost & "',#" & Now() & "#)"
    
    con.Execute q
    
  End If

Next r

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

DataUpload = True

Exit Function

e:

  If con.State = 1 Then con.Close
  Set con = Nothing
  
  DataUpload = False
  
End Function

Sub MasterUpdate()

On Error GoTo e

Set con = CreateObject("adodb.connection")
con.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ThisWorkbook.Path & "/sales.mdb")

q = "delete from MasterTable"
con.Execute q

Dim w As Worksheet: Set w = Sheets("マスタ")
        
For r = 1 To w.UsedRange.Rows.Count

  If w.Cells(r, 1).Text <> "" And w.Cells(r, 2).Text <> "" And w.Cells(r, 3).Text <> "" Then
  
    Select Case w.Cells(r, 3).Text
    
      Case "正契約農産物", "準契約農産物", "委託農産物", "買取農産物", "花卉", "契約加工品", "委託加工品", "買取加工品"
      
        partnerCode = w.Cells(r, 1).Text
        partnerName = w.Cells(r, 2).Text
        partnerType = w.Cells(r, 3).Text
    
        q = "insert into MasterTable (パートナーID,パートナー名称,パートナータイプ,登録日時) " & _
        "values ('" & partnerCode & "','" & partnerName & "','" & partnerType & "',#" & Now() & "#)"
        
        con.Execute q
        
        w.Rows(r).Columns("A:C").Interior.ColorIndex = 0 ' OKの場合
    
      Case Else
      
        w.Cells(r, 3).Interior.Color = RGB(255, 0, 0) 'A:Cは空白ではないが、C列が不正

    End Select
    
  Else
  
    w.Rows(r).Columns("A:C").Interior.Color = RGB(255, 0, 0) 'A:C列の何れか空白
  
  End If

Next r

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

MsgBox "完了しました。"

Exit Sub

e:

  If con.State = 1 Then con.Close
  Set con = Nothing
  
  MsgBox "エラーが発生しています。"
  
End Sub

Sub DataDelte()

On Error GoTo e

Dim w As Worksheet: Set w = Sheets("表紙")

yesno = MsgBox(w.Cells(6, 2).Text & "の日付で登録したデータは全て削除されます。よろしいですか？", vbYesNo)
If yesno = vbNo Then Exit Sub

Set con = CreateObject("adodb.connection")
con.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ThisWorkbook.Path & "/sales.mdb")

q = "delete from SalesTable where 売上年月日 = #" & w.Cells(6, 2).Text & "#"
con.Execute q

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

MsgBox "完了しました。"

Exit Sub

e:

  If con.State = 1 Then con.Close
  Set con = Nothing
  
  MsgBox "エラーが発生しています。"
  
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

Sub auto_open()

On Error Resume Next

Application.CommandBars("cell").Controls("処理実行").Delete

Application.CommandBars("cell").Controls("シート初期化(貼付前処理)").Delete

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

.FaceId = 18

.Caption = "シート初期化(貼付前処理)"

.OnAction = "SheetsClear"

End With

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

.FaceId = 18

.Caption = "処理実行"

.OnAction = "MainProcedure"

End With

End Sub

Sub auto_close()

On Error Resume Next

Application.CommandBars("cell").Controls("処理実行").Delete

Application.CommandBars("cell").Controls("シート初期化(貼付前処理)").Delete

End Sub

Sub SheetsClear()

Sheets("<実績表集計表>貼付1").Rows("1:1000").Delete

Sheets("<実績表集計表>貼付2").Rows("1:1000").Delete

Sheets("<買取業者別手数料集計一覧表>貼付").Rows("1:1000").Delete

End Sub

Sub MainProcedure()

On Error GoTo e

YearMonth = InputBox("登録月をYYYY/MMの形式で入力してください。")

YearMonth = StrConv(YearMonth, vbNarrow)

YearMonth = YearMonth & "/01"

If True = IsDate(YearMonth) Then

yesno = MsgBox(Format(YearMonth, "yyyy年mm月") & "でよろしいですか？", vbYesNo)

If yesno = vbNo Then Exit Sub

Else

MsgBox "正しい登録月を入力してください。"

Exit Sub

End If

If False = DataUpload("<実績表集計表>貼付1", 1, YearMonth) Then GoTo e

If False = DataUpload("<実績表集計表>貼付2", 1, YearMonth) Then GoTo e

If False = DataUpload("<買取業者別手数料集計一覧表>貼付", 2, YearMonth) Then GoTo e

MsgBox "完了しました。"

Exit Sub

MsgBox "エラーが発生しています。"

End Sub

Function DataUpload(sheetName, sheetType, salesDate)

On Error GoTo e

Set con = CreateObject("adodb.connection")

con.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ThisWorkbook.Path & "/sales.mdb")

Dim w As Worksheet: Set w = Sheets(sheetName)

For r = 1 To w.UsedRange.Rows.Count

If sheetType = 1 And _

w.Cells(r, 1).Text <> "" And IsNumeric(w.Cells(r, 1).Text) And _

w.Cells(r, 8).Text <> "" And IsNumeric(w.Cells(r, 8).Text) Then

partnerCode = w.Cells(r, 1).Text

partnerName = w.Cells(r, 2).Text

salesVolume = w.Cells(r, 6).Text

salesAmount = w.Cells(r, 7).Text

marginCost = w.Cells(r, 10).Text

pointCost = w.Cells(r, 18).Text

overheadCost = w.Cells(r, 19).Text

q = "insert into SalesTable (売上年月日,パートナーID,パートナー名称,売上数量,売上金額,手数料,ポイント負担額,諸経費,登録日時) " & _

"values (#" & salesDate & "#,'" & partnerCode & "','" & partnerName & "','" & salesVolume & "','" & salesAmount & "','" & marginCost & "','" & pointCost & "','" & overheadCost & "',#" & Now() & "#)"

con.Execute q

End If

If sheetType = 2 And _

w.Cells(r, 1).Text <> "" And IsNumeric(w.Cells(r, 1).Text) And _

w.Cells(r, 4).Text <> "" And IsNumeric(w.Cells(r, 4).Text) Then

partnerCode = w.Cells(r, 1).Text

partnerName = w.Cells(r, 2).Text

salesVolume = w.Cells(r, 4).Text

salesAmount = w.Cells(r, 5).Text

marginCost = w.Cells(r, 7).Text

pointCost = w.Cells(r, 9).Text

overheadCost = 0

q = "insert into SalesTable (売上年月日,パートナーID,パートナー名称,売上数量,売上金額,手数料,ポイント負担額,諸経費,登録日時) " & _

"values (#" & salesDate & "#,'" & partnerCode & "','" & partnerName & "','" & salesVolume & "','" & salesAmount & "','" & marginCost & "','" & pointCost & "','" & overheadCost & "',#" & Now() & "#)"

con.Execute q

End If

Next r

If con.State = 1 Then con.Close

Set con = Nothing

DataUpload = True

Exit Function

If con.State = 1 Then con.Close

Set con = Nothing

DataUpload = False

End Function

Sub MasterUpdate()

On Error GoTo e

Set con = CreateObject("adodb.connection")

con.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ThisWorkbook.Path & "/sales.mdb")

q = "delete from MasterTable"

con.Execute q

Dim w As Worksheet: Set w = Sheets("マスタ")

For r = 1 To w.UsedRange.Rows.Count

If w.Cells(r, 1).Text <> "" And w.Cells(r, 2).Text <> "" And w.Cells(r, 3).Text <> "" Then

Select Case w.Cells(r, 3).Text

Case "正契約農産物", "準契約農産物", "委託農産物", "買取農産物", "花卉", "契約加工品", "委託加工品", "買取加工品"

partnerCode = w.Cells(r, 1).Text

partnerName = w.Cells(r, 2).Text

partnerType = w.Cells(r, 3).Text

q = "insert into MasterTable (パートナーID,パートナー名称,パートナータイプ,登録日時) " & _

"values ('" & partnerCode & "','" & partnerName & "','" & partnerType & "',#" & Now() & "#)"

con.Execute q

w.Rows(r).Columns("A:C").Interior.ColorIndex = 0 ' OKの場合

Case Else

w.Cells(r, 3).Interior.Color = RGB(255, 0, 0) 'A:Cは空白ではないが、C列が不正

End Select

Else

w.Rows(r).Columns("A:C").Interior.Color = RGB(255, 0, 0) 'A:C列の何れか空白

End If

Next r

If con.State = 1 Then con.Close

Set con = Nothing

MsgBox "完了しました。"

Exit Sub

If con.State = 1 Then con.Close

Set con = Nothing

MsgBox "エラーが発生しています。"

End Sub

Sub DataDelte()

On Error GoTo e

Dim w As Worksheet: Set w = Sheets("表紙")

yesno = MsgBox(w.Cells(6, 2).Text & "の日付で登録したデータは全て削除されます。よろしいですか？", vbYesNo)

If yesno = vbNo Then Exit Sub

Set con = CreateObject("adodb.connection")

con.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ThisWorkbook.Path & "/sales.mdb")

q = "delete from SalesTable where 売上年月日 = #" & w.Cells(6, 2).Text & "#"

con.Execute q

If con.State = 1 Then con.Close

Set con = Nothing

MsgBox "完了しました。"

Exit Sub

If con.State = 1 Then con.Close

Set con = Nothing

MsgBox "エラーが発生しています。"

End Sub

2017-02-272024-05-04

Excel 標準偏差、相関係数、線形回帰

標準偏差、相関係数、線形回帰の求め方。
手動計算とエクセル関数を使った場合。

分析基礎

・基本

結論は金額換算して分かりやすく。継続観察していくならKPIを設定する。

・サンプルの中で変数に属性を追加。

例)
来店率が下がっている顧客、上がっている顧客、特定の時期に上がる顧客等。
購入金額が高い顧客(Aランク)、低い顧客(Bランク)など。

これにより、顧客のニーズの仮説や、分析から排除の必要性等を検討する。

・次に他のサンプルと比較。

サンプル１(会員等)での特徴(標準偏差や中央値等)がサンプル２(非会員等)と比較してどう違うか。そうすることで、サンプル１はサンプル２と比べてはどのような特徴があるのか分かる。

・複数のサンプルでの比較

会員と商品の関係での特徴は、非会員と商品の関係と比較してどう違うか。
会員は特定商品のリピート率が高い等。

SQLで相関係数(傾き)

標準偏差や分散などは標準の関数であるが、相関係数はない。
集計用に傾きだけ欲しかったので、とりあえずSQLで求めた

テーブル名:t
結果:17.2482570475901

select t4.cor * ( t4.y_dev / t4.x_dev ) from (

	select avg(t3.zz) / ( avg(t3.x_dev) * avg(t3.y_dev) ) as cor , avg(t3.x_dev) as x_dev , avg(t3.y_dev) as y_dev
	from (

		select ( t2.xx * t2.yy ) as zz , t2.x_dev , t2.y_dev
		from (

			select
				t.x_m , 
				t.y_m , 
				t.x_m - ( select avg(x_m) from t ) as xx , 
				t.y_m - ( select avg(y_m) from t ) as yy , 
				( select stdevp(x_m) from t ) as x_dev , 
				( select stdevp(y_m) from t ) as y_dev
			from t 

		) as t2

	) as t3

) as t4

select t4.cor * ( t4.y_dev / t4.x_dev ) from (

select avg(t3.zz) / ( avg(t3.x_dev) * avg(t3.y_dev) ) as cor , avg(t3.x_dev) as x_dev , avg(t3.y_dev) as y_dev

from (

select ( t2.xx * t2.yy ) as zz , t2.x_dev , t2.y_dev

from (

select

t.x_m ,

t.y_m ,

t.x_m - ( select avg(x_m) from t ) as xx ,

t.y_m - ( select avg(y_m) from t ) as yy ,

( select stdevp(x_m) from t ) as x_dev ,

( select stdevp(y_m) from t ) as y_dev

from t

) as t2

) as t3

) as t4

傾きで利用したストアド

declare cur cursor for
select distinct カード番号_s from tbl

open cur

declare @id bigint
fetch next from cur into @id

while @@fetch_status = 0
begin

if 7 <> (select count(*) from tbl where カード番号_s = @id)
goto brk

select
@id,
t4.cor * ( t4.y_dev / t4.x_dev )
from
(
	select
	avg(t3.zz) / ( avg(t3.x_dev) * avg(t3.y_dev) ) as cor , 
	avg(t3.x_dev) as x_dev , 
	avg(t3.y_dev) as y_dev
	from
	(
		select
		(t2.xx * t2.yy) as zz , 
		t2.x_dev , 
		t2.y_dev
		from
		(
			select
			集計期間月_m - ( select avg(集計期間月_m) from tbl where カード番号_s = @id ) as xx , 
			買上金額_m - ( select avg(買上金額_m) from tbl where カード番号_s = @id ) as yy , 
			( select stdevp(集計期間月_m) from tbl where カード番号_s = @id ) as x_dev , 
			( select stdevp(買上金額_m) from tbl where カード番号_s = @id ) as y_dev
			from tbl
			where カード番号_s = @id 
		) 
		as t2
	) 
	as t3
) 
as t4

brk:
fetch next from cur into @id

end

close cur
deallocate cur

declare cur cursor for

select distinct カード番号_s from tbl

open cur

declare @id bigint

fetch next from cur into @id

while @@fetch_status = 0

begin

if 7 <> (select count(*) from tbl where カード番号_s = @id)

goto brk

select

@id,

t4.cor * ( t4.y_dev / t4.x_dev )

from

(

select

avg(t3.zz) / ( avg(t3.x_dev) * avg(t3.y_dev) ) as cor ,

avg(t3.x_dev) as x_dev ,

avg(t3.y_dev) as y_dev

from

(

select

(t2.xx * t2.yy) as zz ,

t2.x_dev ,

t2.y_dev

from

(

select

集計期間月_m - ( select avg(集計期間月_m) from tbl where カード番号_s = @id ) as xx ,

買上金額_m - ( select avg(買上金額_m) from tbl where カード番号_s = @id ) as yy ,

( select stdevp(集計期間月_m) from tbl where カード番号_s = @id ) as x_dev ,

( select stdevp(買上金額_m) from tbl where カード番号_s = @id ) as y_dev

from tbl

where カード番号_s = @id

)

as t2

)

as t3

)

as t4

brk:

fetch next from cur into @id

end

close cur

deallocate cur

SQl Server Management Studioでの結果をそのままエクセルに貼り付けたかったので、
以下のような書き方で解決した。

declare @mydate nchar(10)
set @mydate = '2017/1/1'

select 
(select sum(買上金額_m) from tb4 where 集計期間月_d = @mydate),
(select sum(買上金額_m) from tb4 where 集計期間月_d = @mydate and 買上回数_i <= 1),
(select sum(買上金額_m) from tb4 where 集計期間月_d = @mydate and 買上回数_i >= 2 and 買上回数_i <= 4),
(select sum(買上金額_m) from tb4 where 集計期間月_d = @mydate and 買上回数_i >= 5 and 買上回数_i <= 7),
(select sum(買上金額_m) from tb4 where 集計期間月_d = @mydate and 買上回数_i >= 8 and 買上回数_i <= 10),
(select sum(買上金額_m) from tb4 where 集計期間月_d = @mydate and 買上回数_i >= 11 and 買上回数_i <= 15),
(select sum(買上金額_m) from tb4 where 集計期間月_d = @mydate and 買上回数_i >= 16 and 買上回数_i <= 20),
(select sum(買上金額_m) from tb4 where 集計期間月_d = @mydate and 買上回数_i >= 21)

declare @mydate nchar(10)

set @mydate = '2017/1/1'

select

(select sum(買上金額_m) from tb4 where 集計期間月_d = @mydate),

(select sum(買上金額_m) from tb4 where 集計期間月_d = @mydate and 買上回数_i <= 1),

(select sum(買上金額_m) from tb4 where 集計期間月_d = @mydate and 買上回数_i >= 2 and 買上回数_i <= 4),

(select sum(買上金額_m) from tb4 where 集計期間月_d = @mydate and 買上回数_i >= 5 and 買上回数_i <= 7),

(select sum(買上金額_m) from tb4 where 集計期間月_d = @mydate and 買上回数_i >= 8 and 買上回数_i <= 10),

(select sum(買上金額_m) from tb4 where 集計期間月_d = @mydate and 買上回数_i >= 11 and 買上回数_i <= 15),

(select sum(買上金額_m) from tb4 where 集計期間月_d = @mydate and 買上回数_i >= 16 and 買上回数_i <= 20),

(select sum(買上金額_m) from tb4 where 集計期間月_d = @mydate and 買上回数_i >= 21)

2017-02-222020-05-10

VBA SQL実験用②(SQL Server)

以前、Accessにテーブルを手軽作成するエクセルVBAがあったので、今回はSQL Server版。クエリの複数行対応と、シート名でテーブル作成へ少し修正している。

Sub auto_open()

Application.CommandBars("cell").Reset

With Application.CommandBars("cell").Controls.Add
.OnAction = "t"
.Caption = "テーブル作成・登録"
End With

With Application.CommandBars("cell").Controls.Add
.OnAction = "tt"
.Caption = "SQL実行"
End With

End Sub

Sub auto_close()

On Error Resume Next

For i = 1 To 2
  Application.CommandBars("cell").Controls("テーブル作成・登録").Delete
  Application.CommandBars("cell").Controls("SQL実行").Delete
Next i

End Sub

Sub t()

Set cn = CreateObject("adodb.connection")
cn.Open "Provider=SQLOLEDB;Data Source=ESPRIMO\SQLEXPRESS;Initial Catalog=my_database;Integrated Security=SSPI;"

tbl = ActiveSheet.Name

'テーブル削除

On Error Resume Next
cn.Execute "drop table " & tbl

'テーブル作成

On Error GoTo 0

fld = ""

For c = Selection(1).Column To Selection(Selection.Count).Column

  Select Case Right(ActiveSheet.Cells(Selection(1).Row, c).Value, 1)
    Case "s"
    tmp = "varchar(255)"
    Case "i"
    tmp = "bigint"
    Case "m"
    tmp = "money"
    Case "d"
    tmp = "date"
  End Select
  
  fld = fld & ActiveSheet.Cells(Selection(1).Row, c).Value & " " & tmp & ","

Next c

fld = Left(fld, Len(fld) - 1)

q = "create table " & tbl & " (id integer identity(1,1) primary key," & fld & ")": Debug.Print q
cn.Execute q

'データ登録

fld = ""

For c = Selection(1).Column To Selection(Selection.Count).Column
  fld = fld & ActiveSheet.Cells(Selection(1).Row, c).Value & ","
Next c

fld = Left(fld, Len(fld) - 1)

For r = (Selection(1).Row) + 1 To Selection(Selection.Count).Row
  For cc = Selection(1).Column To Selection(Selection.Count).Column
    rec = rec & "'" & ActiveSheet.Cells(r, cc).Value & "'" & ","
  Next cc

  rec = Left(rec, Len(rec) - 1)
  
  q = "insert into " & tbl & " (" & fld & ") values (" & rec & ")": Debug.Print q
  cn.Execute q

  rec = ""
Next r

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

MsgBox "done"

End Sub

Sub tt()

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

cn.Open "Provider=SQLOLEDB;Data Source=ESPRIMO\SQLEXPRESS;Initial Catalog=my_database;Integrated Security=SSPI;"

If Selection.Rows.Count = 1 Then
  rn.Open ActiveCell.Value, cn
Else
  For r = 1 To Selection.Rows.Count
    q = q & " " & Selection(r)
  Next r
  rn.Open q, cn: Debug.Print q
End If

'フィールド作成

i = 0

For c = Selection(1).Column To (Selection(1).Column + rn.Fields.Count) - 1
  Cells(Selection(Selection.Count).Row + 1, c).Value = rn.Fields(i).Name
  i = i + 1
Next c

'データ読込

Cells(Selection(Selection.Count).Row + 2, Selection(1).Column).CopyFromRecordset rn

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

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

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

Sub auto_open()

Application.CommandBars("cell").Reset

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

.OnAction = "t"

.Caption = "テーブル作成・登録"

End With

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

.OnAction = "tt"

.Caption = "SQL実行"

End With

End Sub

Sub auto_close()

On Error Resume Next

For i = 1 To 2

Application.CommandBars("cell").Controls("テーブル作成・登録").Delete

Application.CommandBars("cell").Controls("SQL実行").Delete

Next i

End Sub

Sub t()

Set cn = CreateObject("adodb.connection")

cn.Open "Provider=SQLOLEDB;Data Source=ESPRIMO\SQLEXPRESS;Initial Catalog=my_database;Integrated Security=SSPI;"

tbl = ActiveSheet.Name

'テーブル削除

On Error Resume Next

cn.Execute "drop table " & tbl

'テーブル作成

On Error GoTo 0

fld = ""

For c = Selection(1).Column To Selection(Selection.Count).Column

Select Case Right(ActiveSheet.Cells(Selection(1).Row, c).Value, 1)

Case "s"

tmp = "varchar(255)"

Case "i"

tmp = "bigint"

Case "m"

tmp = "money"

Case "d"

tmp = "date"

End Select

fld = fld & ActiveSheet.Cells(Selection(1).Row, c).Value & " " & tmp & ","

Next c

fld = Left(fld, Len(fld) - 1)

q = "create table " & tbl & " (id integer identity(1,1) primary key," & fld & ")": Debug.Print q

cn.Execute q

'データ登録

fld = ""

For c = Selection(1).Column To Selection(Selection.Count).Column

fld = fld & ActiveSheet.Cells(Selection(1).Row, c).Value & ","

Next c

fld = Left(fld, Len(fld) - 1)

For r = (Selection(1).Row) + 1 To Selection(Selection.Count).Row

For cc = Selection(1).Column To Selection(Selection.Count).Column

rec = rec & "'" & ActiveSheet.Cells(r, cc).Value & "'" & ","

Next cc

rec = Left(rec, Len(rec) - 1)

q = "insert into " & tbl & " (" & fld & ") values (" & rec & ")": Debug.Print q

cn.Execute q

rec = ""

Next r

If cn.State = 1 Then cn.Close

Set cn = Nothing

MsgBox "done"

End Sub

Sub tt()

Set cn = CreateObject("adodb.connection")

Set rn = CreateObject("adodb.recordset")

cn.Open "Provider=SQLOLEDB;Data Source=ESPRIMO\SQLEXPRESS;Initial Catalog=my_database;Integrated Security=SSPI;"

If Selection.Rows.Count = 1 Then

rn.Open ActiveCell.Value, cn

Else

For r = 1 To Selection.Rows.Count

q = q & " " & Selection(r)

Next r

rn.Open q, cn: Debug.Print q

End If

'フィールド作成

i = 0

For c = Selection(1).Column To (Selection(1).Column + rn.Fields.Count) - 1

Cells(Selection(Selection.Count).Row + 1, c).Value = rn.Fields(i).Name

i = i + 1

Next c

'データ読込

Cells(Selection(Selection.Count).Row + 2, Selection(1).Column).CopyFromRecordset rn

If rn.State = 1 Then rn.Close

Set rn = Nothing

If cn.State = 1 Then cn.Close

Set cn = Nothing

End Sub

2017-02-222018-03-19

AccessVBA クエリを呼び出す

Sub test()

'Accessにあるクエリを作成するプロシージャを呼ぶ
'クエリを作ってあるmdbなら不要

Set a = CreateObject("Access.Application")

With a
.OpenCurrentDatabase "xxx.mdb"
.Visible = False
.Application.Run "xxx"
.Quit
End With

Set a = Nothing

'ここからはADO

Set c = CreateObject("ADODB.Connection")
Set r = CreateObject("ADODB.Recordset")

c.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=xxx.mdb;"
r.Open "test", c

'クエリで動的なテーブルを開く。
'ちゃんとSQL Serverまで読みにいっている｡Accessの中にキャッシュなどしていないよう

ActiveSheet.Cells(1, 1).CopyFromRecordset r

End Sub

Sub test()

'Accessにあるクエリを作成するプロシージャを呼ぶ

'クエリを作ってあるmdbなら不要

Set a = CreateObject("Access.Application")

With a

.OpenCurrentDatabase "xxx.mdb"

.Visible = False

.Application.Run "xxx"

.Quit

End With

Set a = Nothing

'ここからはADO

Set c = CreateObject("ADODB.Connection")

Set r = CreateObject("ADODB.Recordset")

c.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=xxx.mdb;"

r.Open "test", c

'クエリで動的なテーブルを開く。

'ちゃんとSQL Serverまで読みにいっている｡Accessの中にキャッシュなどしていないよう

ActiveSheet.Cells(1, 1).CopyFromRecordset r

End Sub

2017-02-212017-02-22

C# Access(accdb) 比較

業者製作のAccessファイル+クライアントで、クライアントプログラム側で操作した結果が
Access内部でどう変更されるか調べようと思ったがAccessファイルが大きすぎた。

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;

/* Microsoft Access データベース エンジン 2010 再頒布可能コンポーネント (AccessDatabaseEngine.exe)
ファイルがaccdb、ACE.OLEDB.12.0を利用する場合、
PCが32bitならインストールしないとプロバイダが見つからない。*/

namespace データベース差分チェック
{
    public partial class Form1 : Form
    {
        UserDataSet uds1 = null;
        UserDataSet uds2 = null;

        public Form1()
        {
            InitializeComponent();

            comboBox1.Enabled = false;
            comboBox2.Enabled = false;

            comboBox1.SelectedIndexChanged += new EventHandler(ComboxChange1);
            comboBox2.SelectedIndexChanged += new EventHandler(ComboxChange2);
        }
        private void ComboxChange1(object sender, EventArgs e)
        {
            if (uds1 == null) return;
            dataGridView1.DataSource = uds1.AccessDataSet.Tables[comboBox1.Text];
        }
        private void ComboxChange2(object sender, EventArgs e)
        {
            if (uds2 == null) return;
            dataGridView2.DataSource = uds2.AccessDataSet.Tables[comboBox2.Text];
        }

        private void button1_Click(object sender, EventArgs e)
        {
            OpenFileDialog ofd = new OpenFileDialog();
            if (ofd.ShowDialog() == DialogResult.OK)
            {
                if (listBox1.Items.Count <= 1)
                {
                    listBox1.Items.Add(ofd.FileName);
                }
                else
                {
                    listBox1.Items[0] = listBox1.Items[1];
                    listBox1.Items.RemoveAt(1);
                    listBox1.Items.Add(ofd.FileName);
                }
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            comboBox1.Items.Clear();
            comboBox2.Items.Clear();

            if (listBox1.Items.Count == 1)
            {
                this.uds1 = new UserDataSet(listBox1.Items[0].ToString());

                for (int i = 0; i < uds1.AccessDataSet.Tables.Count; i++)
                {
                    comboBox1.Items.Add(uds1.AccessDataSet.Tables[i].TableName);
                }
                comboBox1.Enabled = true;
            }


            if (listBox1.Items.Count == 2)
            {
                this.uds2 = new UserDataSet(listBox1.Items[1].ToString());

                for (int i = 0; i < uds2.AccessDataSet.Tables.Count; i++)
                {
                    comboBox2.Items.Add(uds2.AccessDataSet.Tables[i].TableName);
                }
                comboBox2.Enabled = true;
            }
        }
    }

    public class UserDataSet
    {
        public DataSet AccessDataSet = new DataSet();

        public UserDataSet(string filePath)
        {
            using (System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection(
                @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";JET OLEDB:Database Password=xxx"))
            {
                try
                {
                    con.Open(); //fillの場合不要だがGetSchemaで必要。

                    DataTable dt = con.GetSchema("Tables");

                    foreach (DataRow r in dt.Rows)
                    {
                        string tableName = r["TABLE_NAME"].ToString(); //System.Diagnostics.Debug.Print(r["TABLE_NAME"].ToString());

                        if (tableName.Substring(0, 4) != "MSys")
                        {
                            using (System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter())
                            {
                                System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand("select * from " + tableName, con);
                                da.SelectCommand = cmd;

                                DataTable newTable = new DataTable(tableName);
                                AccessDataSet.Tables.Add(newTable);
                                da.Fill(AccessDataSet.Tables[tableName]);
                            }
                        }
                    }
                }
                catch(Exception e)
                {
                    MessageBox.Show(e.Message);
                    return;
                }
                finally
                {
                    con.Close();
                }
            }
        }
    }
}

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.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;

/* Microsoft Access データベースエンジン 2010 再頒布可能コンポーネント (AccessDatabaseEngine.exe)

ファイルがaccdb、ACE.OLEDB.12.0を利用する場合、

PCが32bitならインストールしないとプロバイダが見つからない。*/

namespace データベース差分チェック

{

public partial class Form1 : Form

{

UserDataSet uds1 = null;

UserDataSet uds2 = null;

public Form1()

{

InitializeComponent();

comboBox1.Enabled = false;

comboBox2.Enabled = false;

comboBox1.SelectedIndexChanged += new EventHandler(ComboxChange1);

comboBox2.SelectedIndexChanged += new EventHandler(ComboxChange2);

}

private void ComboxChange1(object sender, EventArgs e)

{

if (uds1 == null) return;

dataGridView1.DataSource = uds1.AccessDataSet.Tables[comboBox1.Text];

}

private void ComboxChange2(object sender, EventArgs e)

{

if (uds2 == null) return;

dataGridView2.DataSource = uds2.AccessDataSet.Tables[comboBox2.Text];

}

private void button1_Click(object sender, EventArgs e)

{

OpenFileDialog ofd = new OpenFileDialog();

if (ofd.ShowDialog() == DialogResult.OK)

{

if (listBox1.Items.Count <= 1)

{

listBox1.Items.Add(ofd.FileName);

}

else

{

listBox1.Items[0] = listBox1.Items[1];

listBox1.Items.RemoveAt(1);

listBox1.Items.Add(ofd.FileName);

}

private void button2_Click(object sender, EventArgs e)

{

comboBox1.Items.Clear();

comboBox2.Items.Clear();

if (listBox1.Items.Count == 1)

{

this.uds1 = new UserDataSet(listBox1.Items[0].ToString());

for (int i = 0; i < uds1.AccessDataSet.Tables.Count; i++)

{

comboBox1.Items.Add(uds1.AccessDataSet.Tables[i].TableName);

}

comboBox1.Enabled = true;

}

if (listBox1.Items.Count == 2)

{

this.uds2 = new UserDataSet(listBox1.Items[1].ToString());

for (int i = 0; i < uds2.AccessDataSet.Tables.Count; i++)

{

comboBox2.Items.Add(uds2.AccessDataSet.Tables[i].TableName);

}

comboBox2.Enabled = true;

}

public class UserDataSet

{

public DataSet AccessDataSet = new DataSet();

public UserDataSet(string filePath)

{

using (System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection(

@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";JET OLEDB:Database Password=xxx"))

{

try

{

con.Open(); //fillの場合不要だがGetSchemaで必要。

DataTable dt = con.GetSchema("Tables");

foreach (DataRow r in dt.Rows)

{

string tableName = r["TABLE_NAME"].ToString(); //System.Diagnostics.Debug.Print(r["TABLE_NAME"].ToString());

if (tableName.Substring(0, 4) != "MSys")

{

using (System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter())

{

System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand("select * from " + tableName, con);

da.SelectCommand = cmd;

DataTable newTable = new DataTable(tableName);

AccessDataSet.Tables.Add(newTable);

da.Fill(AccessDataSet.Tables[tableName]);

}

catch(Exception e)

{

MessageBox.Show(e.Message);

return;

}

finally

{

con.Close();

}

2017-02-212020-10-21

C# 設計メモ

順不同。思いつたら更新。

・クラスの単位

GUIのフォーム単位のクラスが基本(GUIがなければユースケース管理)で、その中で必要に応じてクラスに抜き出す。

１．変更の発生しそうな部分をクラスに抜き出す。
拡張ポイント(DI)

２．共通で使う部分をクラスに抜き出す。
アダプター(合成＋委譲)で共通化
ストラテジー

３．他クラスから参照される部分をクラスに抜き出す。

４．テストしたい部分をクラスに抜き出す。

５．インスタンス生成をクラスに抜き出す。
生成処理を1箇所にまとめることで変更に対応しやすく。
DIが連続して上位レイヤーに引数が溢れてきたら。
(作ると使うを分ける)

６．プリミティブな値に型定義する場合。
値オブジェクト

・クラス、インターフェース、メソッド

参照と変更を分ける(CQS)。意味のある名前にする。スコープが大きいのに一文字変数名や、ハードコーディングしている数値など避ける。とりあえずメソッドは小さく。

・レイヤー

適切にクラス分けしていけば最終的に自然とレイヤーに当てはまる形になっているのでそれほど意識しない。

・依存関係のコントロール

クラス間の依存をインターフェースへの依存にすること。派生クラスの追加で対応する。拡張ポイントと呼ぶ場合もある。

    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();

            // 下位レイヤーに依存すると下位レイヤーの変更に影響を受ける

            new App("");
            // AppでSayHelloのインスタンス生成をしている 

            new App();
            // SayHelloのインスタンスをBuilderで生成し、App内で利用している
            // (生成と利用の分離)

            new App(new Builder());
            // Builderの生成をAppの生成と同じレイヤー化
            // AppはBuilderに依存(利用)していたが、
            // App、BuilderともにIFactoryに依存することになる
            // (DI)
        }
    }
    
    class App
    {
        public App(string dummy)
        {
            new SayHello().Do();
        }

        public App()
        {
            new Builder().CreateObj().Do();
        }

        public App(IFactory f)
        {
            f.CreateObj().Do();
        }

    }

    class Builder : IFactory
    {
        public SayHello CreateObj()
        {
            return new SayHello();
        }
    }

    interface IFactory
    {
        SayHello CreateObj();
    }

    class SayHello
    {
        public void Do()
        {
            MessageBox.Show("hello");
        }

    }

public partial class Form1 : Form

{

public Form1()

{

InitializeComponent();

// 下位レイヤーに依存すると下位レイヤーの変更に影響を受ける

new App("");

// AppでSayHelloのインスタンス生成をしている

new App();

// SayHelloのインスタンスをBuilderで生成し、App内で利用している

// (生成と利用の分離)

new App(new Builder());

// Builderの生成をAppの生成と同じレイヤー化

// AppはBuilderに依存(利用)していたが、

// App、BuilderともにIFactoryに依存することになる

// (DI)

}

class App

{

public App(string dummy)

{

new SayHello().Do();

}

public App()

{

new Builder().CreateObj().Do();

}

public App(IFactory f)

{

f.CreateObj().Do();

}

class Builder : IFactory

{

public SayHello CreateObj()

{

return new SayHello();

}

interface IFactory

{

SayHello CreateObj();

}

class SayHello

{

public void Do()

{

MessageBox.Show("hello");

}

・DB系

DBの依存関係。FKがある方が依存している。依存している方が子。PKは重複できないけど、FKは重複できるので、FKある方が多。

複合主キーとなっている関係従属を取り除く正規化は当たり前に発生するので、正しいモデリングをすると必然的に複合主キーが現れる。サロゲートキーを入れる場合、制約はアプリ側で対応する。例えば仕入価格を、仕入先・商品・開始年月で識別するような場合。

参照整合性。実務では完全な制約が必要ではなく、緩い制約の方が都合がいいときもある。ただし、構造が壊れやすくはなるので注意。

2017-02-212017-09-26

SQL DB正規化

直ぐ忘れてしまう。

先ず、一方の値が決まると他方も決まるものを、関数従属性という。

第一正規化

一つのセルに複数のデータを持たない。

第二正規化

部分関数従属を取り除く。
部分関数従属とは、主キーの一部だけで一意に決まる列のこと。

主キーが複数あり、その内の一つだけで値が決まるものを別のテーブルにする。

第三正規化

推移的関数従属を取り除く。

推移的関数従属とは、テーブル内の段階的な関数従属のこと。
つまり、主キー以外と関数従属があること。

主キー以外の項目で、他の列の値を一意に決めるものを別テーブルにする。

例えば、(主)社員ID　→　部署コード　→　部署名

部署コードが決まれば部署名が決まる。当然主である社員IDと部署コードは関数従属の関係なので、
段階的な関数従属があるといえる。

部分関数従属、推移的関数従属を取り除くことの必要性。

あるテーブルの中で推移的関数従属が残っていると、間違った登録が可能。(会社コードと会社名)
また、主キーが不明の段階で登録できない。

キー｜　メンバー名　｜　会社コード　｜　会社名
１　｜　太郎　　　｜　００００　　　｜　AAA
２　｜　次郎　　　｜　０００１　　　｜　AAA

テーブルの関連

１対１、１対多、多対多があるが、通常１対多のみ使う。

多対多となる場合、関連実体というテーブルを作る。ただ、通常の業務要件では、
マスタとマスタを直接関連付けることはないので、普通に考えると多対多とはならない。