[网络工具] 分享一个数据库连接工具[SQLCMD]

@echo off
setlocal ENABLEDELAYEDEXPANSION
::设置连接数据库的字符串sqlc
set sqlc=sqlcmd /log dbs.log /APPEND /user sa /db "BGS-YCM" /command
::获取数据库查询结果 records
%sqlc% "select * from TimeRecords where card_id='0016459843' and sign_time > '2013.1.1'" |findstr /v "[a-zA-Z]" |conset records=

::整理数据
for /f "tokens=4,5,6,7,8,9,10 delims=:'/ " %%a in ('conset /v records') do (
	set dt=1%%d%%e%%f
	if %%g equ 1 (
		if !dt! gtr 1060000 if !dt! lss 1083100 set "#%%c.%%a.%%b#A=%%g$%%d:%%e:%%f"
		if !dt! gtr 1083100 if !dt! lss 1173000 set "#%%c.%%a.%%b#B=%%g$%%d:%%e:%%f"
		if !dt! gtr 1173000 set "#%%c.%%a.%%b#D=%%g$%%d:%%e:%%f"
	)
	if %%g geq 2 (
		if !dt! gtr 1083100 if !dt! lss 1173000 set "#%%c.%%a.%%b#B=%%g$%%d:%%e:%%f"
		if !dt! gtr 1173000 set "#%%c.%%a.%%b#C=%%g$%%d:%%e:%%f"
	)
	set /a ￥%%c￥%%a￥%%b￥+=1
)

::分析缺少打卡的日期
for /f "tokens=1-4 delims=￥=" %%a in ('set ￥') do (
echo,
	IF NOT DEFINED #%%a.%%b.%%c#A call :补上班 %%a %%b %%c
	IF NOT DEFINED #%%a.%%b.%%c#C call :补下班 %%a %%b %%c
)
pause
goto :eof


:补上班
echo,%1.%2.%3日，补上班...
IF DEFINED #%1.%2.%3#B for /f "tokens=1-2 delims=$" %%a in ("!#%1.%2.%3#B!") do (
	if %%a equ 1 (
		call :改时间 %1 %2 %3 1
	)
) else (
	call :补时间 %1 %2 %3 1
)
goto :eof

:补下班
echo,%1.%2.%3日，补下班...
IF DEFINED #%1.%2.%3#D (
	call :改次 %1 %2 %3
) else (
	set /a flag=!￥%1￥%2￥%3￥!+1
	call :补时间 %1 %2 %3 !flag!
)
goto :eof
	
:改次
echo,%1.%2.%3 改次!#%1.%2.%3#D!
for /f "tokens=1,2 delims=$" %%a in ("!#%1.%2.%3#D!") do (
	echo,%sqlc% "update TimeRecords set mark=2 where card_id='0016459843' and sign_time = '%1.%2.%3 %%b'"
)	
goto :eof

:补时间
echo,%1.%2.%3:%4补时间...
if %4 gtr 1 (
	set /a hh=17+!RANDOM!%%2,ss=!RANDOM!%%60
	if !hh! equ 17 (set /a mm=31+!RANDOM!%%29) else set /a mm=!RANDOM!%%60
	set "newtime=%1.%2.%3 !hh!:!mm!:!ss!"
) else (
	set /a hh=8,mm=15+!RANDOM!%%15,ss=!RANDOM!%%60
	set "newtime=%1.%2.%3 0!hh!:!mm!:!ss!"
)
echo,%sqlc% "insert into TimeRecords values (1,'0016459843','160921','!newtime!',%4,0)"
goto :eof

:改时间
echo,%1.%2.%3迟到改时间，原!#%1.%2.%3#B!
set /a hh=8,mm=15+!RANDOM!%%15,ss=!RANDOM!%%60
set "newtime=%1.%2.%3 0!hh!:!mm!:!ss!"
for /f "tokens=1,2 delims=$" %%a in ("!#%1.%2.%3#B!") do (
	echo,%sqlc% "update TimeRecords set sign_time='!newtime!' where card_id='0016459843' and sign_time = '%1.%2.%3 %%b'"
)	
goto :eof
复制代码