[소스 : Mis.asp]
<%
Option Explicit
Response.CacheControl = "no-cache"
Response.AddHeader "Pragma", "no-cache"
Response.Expires = -1
'Dim selYear, selMonth, selDay
'selYear = Request("selYear")
'selMonth = Request("selMonth")
%>
<!DOCTYPE HTML>
<html>
<head>
<title>경영정보시스템</title>
<meta http-equiv="Content-Type" content="text/html; charset=euc-kr">
<meta http-equiv="X-UA-Compatible" content="IE=edge" />
<link HREF="/css/css.css" REL="stylesheet" TYPE="text/css">
<script type="text/javascript" src="/script/CheckChr.js"></script>
<!-- Jquery CDN 로드 : 항상 최신 버전 사용 -->
<!--<script type="text/javascript" src="/script/Jquery/jquery-1.9.1.js"></script>-->
<script src="https://code.jquery.com/jquery-latest.min.js"></script>
<!-- chart.js 라이브러리 CDN 로드 -->
<!--<script type="text/javascript" src="https://cdn.jsdelivr.net/npm/chart.js"></script>-->
<script src="https://cdn.jsdelivr.net/npm/chart.js@3.0.0/dist/chart.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/chartjs-plugin-datalabels@2.0.0"></script>
</head>
<body>
<div style="height: 10px;"></div>
<div style="float: left;">
<select id="selYear" name="selYear" style="height: 23px; float: left;">
<option value="2017">2017 년</option>
<option value="2018">2018 년</option>
<option value="2019">2019 년</option>
<option value="2020">2020 년</option>
<option value="2021">2021 년</option>
<option value="2022" selected="selected">2022 년</option>
<option value="2023">2023 년</option>
</select>
<select id="selMonth" name="selMonth" style="height: 23px; float: left; margin-left: 5px;">
<option value="01" selected="selected">01 월</option>
<option value="02">02 월</option>
<option value="03">03 월</option>
<option value="04">04 월</option>
<option value="05">05 월</option>
<option value="06">06 월</option>
<option value="07">07 월</option>
<option value="08">08 월</option>
<option value="09">09 월</option>
<option value="10">10 월</option>
<option value="11">11 월</option>
<option value="12">12 월</option>
</select>
<select id="selDay" name="selDay" style="height: 23px; float: left; margin-left: 5px;">
<option value="01" selected="selected">01 일</option>
<option value="02">02 일</option>
<option value="03">03 일</option>
<option value="04">04 일</option>
<option value="05">05 일</option>
<option value="06">06 일</option>
<option value="07">07 일</option>
<option value="08">08 일</option>
<option value="09">09 일</option>
<option value="10">10 일</option>
<option value="11">11 일</option>
<option value="12">12 일</option>
<option value="13">13 일</option>
<option value="14">14 일</option>
<option value="15">15 일</option>
<option value="16">16 일</option>
<option value="17">17 일</option>
<option value="18">18 일</option>
<option value="19">19 일</option>
<option value="20">20 일</option>
<option value="21">21 일</option>
<option value="22">22 일</option>
<option value="23">23 일</option>
<option value="24">24 일</option>
<option value="25">25 일</option>
<option value="26">26 일</option>
<option value="27">27 일</option>
<option value="28">28 일</option>
<option value="29">29 일</option>
<option value="30">30 일</option>
<option value="31">31 일</option>
</select>
<button type="button" onclick="GetData_Mis1()" style=" float: left; margin-left: 10px; cursor: pointer;">데이터 가지고 오기</button>
</div>
<div style="width: 1080px; height: 24px; float: left; padding: 6px 0px 0px 10px;">
<progress id="animationProgress" max="1" value="0" style="width: 100%"></progress>
</div>
<div style="height: 5px; clear: both;"></div>
<div style="width: 500px; height: 250px; float: left;">
<canvas id="y_Chart"></canvas>
</div>
<div style="height: 5px; width: 20px; float: left;"></div>
<div style="width: 900px; height: 250px; float: left;">
<canvas id="m_Chart"></canvas>
</div>
<div style="height: 25px; clear: both;"></div>
<div style="width: 1420px; height: 300px; clear: both;">
<canvas id="d_Chart"></canvas>
</div>
<div style="height: 25px; clear: both;"></div>
<div style="width: 1420px; height: 300px; clear: both;">
<canvas id="hh_Chart"></canvas>
</div>
<script type="text/javascript">
var ctx_y = null;
var ctx_m = null;
var ctx_d = null;
var ctx_hh = null;
var chart_y = null;
var chart_m = null;
var chart_d = null;
var chart_hh = null;
var progress = document.getElementById("animationProgress"); // and missed this
$(document).ready(function () {
function create_Y_Chart() {
ctx_y = document.getElementById("y_Chart");
chart_y = new Chart(ctx_y, {
type: "bar",
//plugins: [ChartDataLabels],
data: {
labels: null,
datasets: [
{
type: "bar",
label: "[년] 총 매출 금액 ",
// 수치 데이터 표시
//datalabels: { color:'#f00', font:{ size:15 } },
borderColor: "rgb(54, 162, 235)",
backgroundColor: "rgba(54, 162, 235, 0.2)",
borderWidth: 2,
data: null
},
{
type: "line",
label: "[년] 목표 금액 ",
borderColor: "rgb(255, 99, 132)",
backgroundColor: "rgba(255, 99, 132, 0.2)",
pointBackgroundColor: 'rgb(255, 99, 132)',
borderWidth: 2,
lineTension: 0,
fill: false,
data: null
},
{
type: "line",
label: "[년] 이전 매출 차이 금액 ",
borderColor: "rgb(255, 159, 64)",
backgroundColor: "rgba(255, 159, 64, 0.2)",
pointBackgroundColor: "rgb(255, 159, 64)",
borderWidth: 2,
lineTension: 0.3, // line 볼록정도
fill: true,
//borderDash: [0 ,6],
//borderCapStyle: 'round', // 점선 그래프, 점의 모양
//pointRadius: 5, // 포인트 크기
//pointStyle: 'rect', // 직사각형, triangle : 삼각형, default : circle (원형)
data: null
}
]
}
, options: {
responsive: true,
maintainAspectRatio: false, // 그래프의 비율 유지
animation: {
duration: 1000,
onProgress: function (animation) {
//add progress
progress.value = animation.currentStep / animation.numSteps;
},
onComplete: function (animation) {
window.setTimeout(function () {
progress.value = 0;
$("#animationProgress").hide();
}, 1000);
}
}
, scales: {
x: {
stacked: true // 누적 막대 그래프 표시
}
, y: {
//beginAtZero: true
}
//, xAxes: [{ }]
//, yAxes: [{ }]
}
}
});
}
function create_M_Chart() {
ctx_m = document.getElementById("m_Chart");
chart_m = new Chart(ctx_m, {
type: "bar",
//plugins: [ChartDataLabels],
data: {
labels: null,
datasets: [
{
type: "bar",
label: "[월] 총 매출 금액 ",
// 수치 데이터 표시
//datalabels: { color:'#f00', font:{ size:15 } },
borderColor: "rgb(54, 162, 235)",
backgroundColor: "rgba(54, 162, 235, 0.2)",
borderWidth: 2,
data: null
},
{
type: "line",
label: "[월] 목표 금액 ",
borderColor: "rgb(255, 99, 132)",
backgroundColor: "rgba(255, 99, 132, 0.2)",
pointBackgroundColor: 'rgb(255, 99, 132)',
borderWidth: 2,
lineTension: 0,
fill: false,
data: null
},
{
type: "line",
label: "[월] 이전 매출 차이 금액 ",
borderColor: "rgb(255, 159, 64)",
backgroundColor: "rgba(255, 159, 64, 0.2)",
pointBackgroundColor: "rgb(255, 159, 64)",
borderWidth: 2,
lineTension: 0.3, // line 볼록정도
fill: true,
//borderDash: [0 ,6],
//borderCapStyle: 'round', // 점선 그래프, 점의 모양
//pointRadius: 5, // 포인트 크기
//pointStyle: 'rect', // 직사각형, triangle : 삼각형, default : circle (원형)
data: null
}
]
}
, options: {
responsive: true,
maintainAspectRatio: false, // 그래프의 비율 유지
animation: {
duration: 1000,
onProgress: function (animation) {
//add progress
progress.value = animation.currentStep / animation.numSteps;
},
onComplete: function (animation) {
window.setTimeout(function () {
progress.value = 0;
$("#animationProgress").hide();
}, 1000);
}
}
, scales: {
x: {
stacked: true // 누적 막대 그래프 표시
}
, y: {
//beginAtZero: true
}
//, xAxes: [{ }]
//, yAxes: [{ }]
}
}
});
}
function create_D_Chart() {
ctx_d = document.getElementById("d_Chart");
chart_d = new Chart(ctx_d, {
type: "bar",
//plugins: [ChartDataLabels],
data: {
labels: null,
datasets: [
{
type: "bar",
label: "[일] 총 매출 금액 ",
// 수치 데이터 표시
//datalabels: { color:'#f00', font:{ size:15 } },
borderColor: "rgb(54, 162, 235)",
backgroundColor: "rgba(54, 162, 235, 0.2)",
borderWidth: 2,
data: null
},
{
type: "line",
label: "[일] 목표 금액 ",
borderColor: "rgb(255, 99, 132)",
backgroundColor: "rgba(255, 99, 132, 0.2)",
pointBackgroundColor: 'rgb(255, 99, 132)',
borderWidth: 2,
lineTension: 0,
fill: false,
data: null
},
{
type: "line",
label: "[일] 이전 매출 차이 금액 ",
borderColor: "rgb(255, 159, 64)",
backgroundColor: "rgba(255, 159, 64, 0.2)",
pointBackgroundColor: "rgb(255, 159, 64)",
borderWidth: 2,
lineTension: 0.3, // line 볼록정도
fill: true,
//borderDash: [0 ,6],
//borderCapStyle: 'round', // 점선 그래프, 점의 모양
//pointRadius: 5, // 포인트 크기
//pointStyle: 'rect', // 직사각형, triangle : 삼각형, default : circle (원형)
data: null
}
]
}
, options: {
responsive: true,
maintainAspectRatio: false, // 그래프의 비율 유지
animation: {
duration: 1000,
onProgress: function (animation) {
//add progress
progress.value = animation.currentStep / animation.numSteps;
},
onComplete: function (animation) {
window.setTimeout(function () {
progress.value = 0;
$("#animationProgress").hide();
}, 1000);
}
}
, scales: {
x: {
stacked: true // 누적 막대 그래프 표시
}
, y: {
//beginAtZero: true
}
//, xAxes: [{ }]
//, yAxes: [{ }]
}
}
});
}
function create_HH_Chart() {
ctx_hh = document.getElementById("hh_Chart");
chart_hh = new Chart(ctx_hh, {
type: "bar",
//plugins: [ChartDataLabels],
data: {
labels: null,
datasets: [
{
type: "bar",
label: "[시간] 총 매출 금액 ",
// 수치 데이터 표시
//datalabels: { color:'#f00', font:{ size:15 } },
borderColor: "rgb(54, 162, 235)",
backgroundColor: "rgba(54, 162, 235, 0.2)",
borderWidth: 2,
data: null
},
{
type: "line",
label: "[시간] 목표 금액 ",
borderColor: "rgb(255, 99, 132)",
backgroundColor: "rgba(255, 99, 132, 0.2)",
pointBackgroundColor: 'rgb(255, 99, 132)',
borderWidth: 2,
lineTension: 0,
fill: false,
data: null
},
{
type: "line",
label: "[시간] 이전 매출 차이 금액 ",
borderColor: "rgb(255, 159, 64)",
backgroundColor: "rgba(255, 159, 64, 0.2)",
pointBackgroundColor: "rgb(255, 159, 64)",
borderWidth: 2,
lineTension: 0.3, // line 볼록정도
fill: true,
//borderDash: [0 ,6],
//borderCapStyle: 'round', // 점선 그래프, 점의 모양
//pointRadius: 5, // 포인트 크기
//pointStyle: 'rect', // 직사각형, triangle : 삼각형, default : circle (원형)
data: null
}
]
}
, options: {
responsive: true,
maintainAspectRatio: false, // 그래프의 비율 유지
animation: {
duration: 1000,
onProgress: function (animation) {
//add progress
progress.value = animation.currentStep / animation.numSteps;
},
onComplete: function (animation) {
window.setTimeout(function () {
progress.value = 0;
$("#animationProgress").hide();
}, 1000);
}
}
, scales: {
x: {
stacked: true // 누적 막대 그래프 표시
}
, y: {
//beginAtZero: true
}
//, xAxes: [{ }]
//, yAxes: [{ }]
}
}
});
}
create_Y_Chart();
create_M_Chart();
create_D_Chart();
create_HH_Chart();
GetData_Mis1();
});
function updateData(chart, xLabels, datas1, datas2, datas3, datas1_sum) {
//event.target.disabled = true;
//chart2.clear();
$("#animationProgress").show();
chart.data.labels = xLabels; //["2021년 01월", "2021년 02월", "2021년 03월", "2021년 04월", "2021년 05월", "2021년 06월", "2021년 07월", "2021년 08월", "2021년 09월", "2021년 10월", "2021년 11월", "2021년 12월"];
chart.data.datasets[0].data = datas1; //[1888265412, 1786793107, 2339582602, 2124799830, 2127685953, 2351000317, 2206149756, 3799796825, 2590782818, 2558583990, 2803074210, 2633081919];
chart.data.datasets[1].data = datas2; //[2000000000, 2100000000, 2200000000, 2300000000, 2400000000, 2500000000, 2600000000, 2700000000, 2800000000, 2900000000, 3000000000, 3100000000];
chart.data.datasets[2].data = datas3; //[1888265412, -101472305, 552789495, -214782772, 2886123, 223314364, -144850561, 1593647069, -1209014007, -32198828, 244490220, -169992291];
chart.update();
}
function GetData_Mis1() {
var MisType = "1";
var selYear = $("#selYear").val();
var selMonth = $("#selMonth").val();
var selDay = $("#selDay").val();
$.ajax({
url: "Mis_Controllers.asp",
type: "GET",
datatype: "json",
data: "MisType=" + MisType + "&selYear=" + selYear + "&selMonth=" + selMonth + "&selDay=" + selDay,
//contentType: "application/json",
contentType: "application/x-www-form-urlencoded; charset=euc-kr",
async: false,
//cache : false,
success: function (data) {
var items = $.parseJSON(data);
if (items == undefined || items == null) {
alert("데이터가 없습니다.");
return false;
}
updateData(chart_y, items.y_xLabels, items.y_datas1, items.y_datas2, items.y_datas3, items.y_datas1_sum);
updateData(chart_m, items.m_xLabels, items.m_datas1, items.m_datas2, items.m_datas3, items.m_datas1_sum);
updateData(chart_d, items.d_xLabels, items.d_datas1, items.d_datas2, items.d_datas3, items.d_datas1_sum);
updateData(chart_hh, items.hh_xLabels, items.hh_datas1, items.hh_datas2, items.hh_datas3, items.hh_datas1_sum);
},
error: function (request, status, error) {
alert("code:" + request.status + "\n" + "message:" + request.responseText + "\n" + "error:" + error);
}
});
}
var sec = 60000; // 60초
var repeatFunc = function(){
setTimeout(function(){
GetData_Mis1(); // 차트 호출
setTimeout(function(){
repeatFunc();
}, sec);
}, sec);
}
repeatFunc();
</script>
</body>
</html>
[소스 : Mis_Controllers.asp]
<%
Option Explicit
Response.CacheControl = "no-cache"
Response.AddHeader "Pragma", "no-cache"
Response.Expires = -1
Dim MisType
Dim selYear, selMonth, selDay
Dim ReValue
'Response.Write "0-----------------------------------<br />"
MisType = Request("MisType")
selYear = Request("selYear")
selMonth = Request("selMonth")
selDay = Request("selDay")
'Response.Write "1-----------------------------------<br />"
'Response.Write "MisType=" & MisType & "<br />"
'Response.Write "selYear=" & selYear & "<br />"
'Response.Write "selMonth=" & selMonth & "<br />"
'Response.Write "selDay=" & selDay & "<br />"
if (MisType = 1) then
'Response.Write "2-----------------------------------<br />"
Set adoCmd = Server.CreateObject("ADODB.Command")
WITH adoCmd
.ActiveConnection = adoCn
.CommandType = adCmdStoredProc
.CommandText = "KHD_MIS_1"
.Parameters("@setYear").Value = selYear
.Parameters("@setMonth").Value = selMonth
.Parameters("@setDay").Value = selDay
End WITH
Set adoRs = adoCmd.Execute
'JSON 형식 데이터
'{
' "y_xLabels": ["2017년","2018년","2019년","2020년","2021년","2022년","2023년"]
' , "y_datas1": [3233134449,21197614804,24864717274,24912461082,29209596739,29551800464,0]
' , "y_datas2": [10000000000,15000000000,22000000000,28000000000,35000000000,40000000000,45000000000]
' ,"y_datas3": [3233134449,17964480355,3667102470,47743808,4297135657,342203725,0]
' ,"y_datas1_sum": [132969324812]
'}
ReValue = ReValue & "{ "
if not adoRs.EOF then
ReValue = ReValue & chr(34) & "y_xLabels" & chr(34) & ":" & adoRs("y_xLabels")
ReValue = ReValue & ", " & chr(34) & "y_datas1" & chr(34) & ":" & adoRs("y_datas1")
ReValue = ReValue & ", " & chr(34) & "y_datas2" & chr(34) & ":" & adoRs("y_datas2")
ReValue = ReValue & ", " & chr(34) & "y_datas3" & chr(34) & ":" & adoRs("y_datas3")
ReValue = ReValue & ", " & chr(34) & "y_datas1_sum" & chr(34) & ":" & adoRs("y_datas1_sum")
end if
Set adoRs = adoRs.NextRecordSet
if not adoRs.EOF then
ReValue = ReValue & ", " & chr(34) & "m_xLabels" & chr(34) & ":" & adoRs("m_xLabels")
ReValue = ReValue & ", " & chr(34) & "m_datas1" & chr(34) & ":" & adoRs("m_datas1")
ReValue = ReValue & ", " & chr(34) & "m_datas2" & chr(34) & ":" & adoRs("m_datas2")
ReValue = ReValue & ", " & chr(34) & "m_datas3" & chr(34) & ":" & adoRs("m_datas3")
ReValue = ReValue & ", " & chr(34) & "m_datas1_sum" & chr(34) & ":" & adoRs("m_datas1_sum")
end if
Set adoRs = adoRs.NextRecordSet
if not adoRs.EOF then
ReValue = ReValue & ", " & chr(34) & "d_xLabels" & chr(34) & ":" & adoRs("d_xLabels")
ReValue = ReValue & ", " & chr(34) & "d_datas1" & chr(34) & ":" & adoRs("d_datas1")
ReValue = ReValue & ", " & chr(34) & "d_datas2" & chr(34) & ":" & adoRs("d_datas2")
ReValue = ReValue & ", " & chr(34) & "d_datas3" & chr(34) & ":" & adoRs("d_datas3")
ReValue = ReValue & ", " & chr(34) & "d_datas1_sum" & chr(34) & ":" & adoRs("d_datas1_sum")
end if
Set adoRs = adoRs.NextRecordSet
if not adoRs.EOF then
ReValue = ReValue & ", " & chr(34) & "hh_xLabels" & chr(34) & ":" & adoRs("hh_xLabels")
ReValue = ReValue & ", " & chr(34) & "hh_datas1" & chr(34) & ":" & adoRs("hh_datas1")
ReValue = ReValue & ", " & chr(34) & "hh_datas2" & chr(34) & ":" & adoRs("hh_datas2")
ReValue = ReValue & ", " & chr(34) & "hh_datas3" & chr(34) & ":" & adoRs("hh_datas3")
ReValue = ReValue & ", " & chr(34) & "hh_datas1_sum" & chr(34) & ":" & adoRs("hh_datas1_sum")
end if
ReValue = ReValue & " }"
Set adoRs = Nothing
adoCn.Close
Set adoCn = Nothing
Response.Write ReValue
end if
%>
[SP]
-- exec MIS_1 '2021', '01', '01'
CREATE PROCEDURE [dbo].[MIS_1]
@setYear varchar(4) = ''
, @setMonth varchar(2) = ''
, @setDay varchar(2) = ''
AS
BEGIN
SET NOCOUNT ON;
if (@setYear = '' or @setYear is null)
set @setYear = '2022'
if (@setMonth = '' or @setMonth is null)
set @setMonth = '01'
else
set @setMonth = RIGHT('00' + CAST(@setMonth AS VARCHAR), 2)
if (@setDay = '' or @setDay is null)
set @setDay = '01'
else
set @setDay = RIGHT('00' + CAST(@setDay AS VARCHAR), 2)
declare @i int
, @lastvalue int
-----------------------
-- 목표금액 세팅
-----------------------
--declare @YearGoalTable TABLE ([sYear] varchar(4), [목표금액] bigint)
create table #YearGoalTable ([sYear] varchar(4), [목표금액] bigint)
insert into #YearGoalTable
select '2017', 10000000000 union
select '2018', 15000000000 union
select '2019', 22000000000 union
select '2020', 28000000000 union
select '2021', 35000000000 union
select '2022', 40000000000 union
select '2023', 45000000000
--select * from @YearGoalTable
--declare @MonthGoalTable TABLE ([sYear] varchar(4), [sMonth] varchar(2), [목표금액] bigint)
create table #MonthGoalTable ([sYear] varchar(4), [sMonth] varchar(2), [목표금액] bigint)
declare @m_SLIMIT bigint -- 범위 시작
, @m_ELIMIT bigint -- 범위 마지막
set @m_SLIMIT = 2000000000
set @m_ELIMIT = 3100000000
-- 해당 년의 월수 값
set @lastvalue = 12
set @i = 1
while (@i <= @lastvalue)
begin
insert into #MonthGoalTable
select @setYear
, RIGHT('00' + CAST(@i AS VARCHAR), 2)
, ROUND(((@m_ELIMIT + 1) - @m_SLIMIT) * RAND() + @m_SLIMIT, 0, 1)
set @i = @i + 1
end
--declare @DayGoalTable TABLE ([sYear] varchar(4), [sMonth] varchar(2), [sDay] varchar(2), [목표금액] bigint)
create table #DayGoalTable ([sYear] varchar(4), [sMonth] varchar(2), [sDay] varchar(2), [목표금액] bigint)
declare @d_SLIMIT bigint -- 범위 시작
, @d_ELIMIT bigint -- 범위 마지막
set @d_SLIMIT = 100000000
set @d_ELIMIT = 150000000
-- 해당 월의 마지막 일자 값
set @lastvalue = convert(int,substring(convert(varchar,EOMONTH(@setYear + '-' + @setMonth + '-01')),9,2))
--SELECT substring(convert(varchar,EOMONTH('2020-09-09')),9,2) AS result
--select substring(convert(varchar,EOMONTH(@setYear + '-' + @setMonth + '-01')),9,2) AS result
-- 문자 자리수 0 SELECT RIGHT('00000' + CAST(12 AS NVARCHAR), 5);
set @i = 1
while (@i <= @lastvalue)
begin
insert into #DayGoalTable
select @setYear
, @setMonth
, RIGHT('00' + CAST(@i AS VARCHAR), 2)
, ROUND(((@d_ELIMIT + 1) - @d_SLIMIT) * RAND() + @d_SLIMIT, 0, 1)
set @i = @i + 1
end
create table #HhGoalTable ([sYear] varchar(4), [sMonth] varchar(2), [sDay] varchar(2), [sHh] varchar(2), [목표금액] bigint)
insert into #HhGoalTable
select @setYear, @setMonth, @setDay, '01', 0 union
select @setYear, @setMonth, @setDay, '02', 0 union
select @setYear, @setMonth, @setDay, '03', 0 union
select @setYear, @setMonth, @setDay, '04', 0 union
select @setYear, @setMonth, @setDay, '05', 0 union
select @setYear, @setMonth, @setDay, '06', 0 union
select @setYear, @setMonth, @setDay, '07', 0 union
select @setYear, @setMonth, @setDay, '08', 0 union
select @setYear, @setMonth, @setDay, '09', 0 union
select @setYear, @setMonth, @setDay, '10', 0 union
select @setYear, @setMonth, @setDay, '11', 0 union
select @setYear, @setMonth, @setDay, '12', 0 union
select @setYear, @setMonth, @setDay, '13', 0 union
select @setYear, @setMonth, @setDay, '14', 0 union
select @setYear, @setMonth, @setDay, '15', 0 union
select @setYear, @setMonth, @setDay, '16', 0 union
select @setYear, @setMonth, @setDay, '17', 0 union
select @setYear, @setMonth, @setDay, '18', 0 union
select @setYear, @setMonth, @setDay, '19', 0 union
select @setYear, @setMonth, @setDay, '20', 0 union
select @setYear, @setMonth, @setDay, '21', 0 union
select @setYear, @setMonth, @setDay, '22', 0 union
select @setYear, @setMonth, @setDay, '23', 0 union
select @setYear, @setMonth, @setDay, '24', 0
-----------------------
-- 년별
-----------------------
select q2.sYear as [년도]
, '' as [월]
, '' as [일]
, '' as [시간]
, isnull(ord_price, 0) as [총매출액]
, isnull(ord_price - isnull(LAG(ord_price) OVER(ORDER BY q2.sYear), 0), 0) as [차이금액]
, q2.sYear + '년' as [xLabel]
, q2.sYear as [xLabel2]
, isnull(q2.[목표금액], 0) as [목표금액]
into #YearDatas
from #YearGoalTable q2
left join (
select sYear, sum(ord_price) as ord_price
from (
select
left(oi.settle_date,4) as sYear
, substring(convert(varchar,oi.settle_date),5,2) as sMonth
, right(oi.settle_date,2) as sDay
, substring(convert(varchar,oi.settle_time),1,2) as sHh
, convert(bigint, case when (orp.ord_pnum is not null) then (op.ord_price * op.ord_cnt) - orp.price else (op.ord_price * op.ord_cnt) end) as ord_price
from TABLE_A oi nolock
join TABLE_B op on oi.ord_num = op.ord_num
left join TABLE_C orp on op.ord_pnum = orp.ord_pnum and orp.[State] = 3 and orp.[State1] = 1
where oi.settle_flag = 1
and op.ord_state in (2,4,5,6,7)
and op.prod_type = 0
) q1
group by sYear
) q3 on q2.sYear = q3.sYear
order by q2.sYear
-----------------------
-- 월별
-----------------------
select q2.sYear as [년도]
, q2.sMonth as [월]
, '' as [일]
, '' as [시간]
, isnull(ord_price, 0) as [총매출액]
, isnull(ord_price - isnull(LAG(ord_price) OVER(ORDER BY q2.sYear, q2.sMonth), 0), 0) as [차이금액]
, q2.sYear + '년 ' + q2.sMonth + '월' as [xLabel]
, q2.sYear + '.' + q2.sMonth as [xLabel2]
, isnull(q2.[목표금액], 0) as [목표금액]
into #MonthDatas
from #MonthGoalTable q2
left join (
select sYear, sMonth, sum(ord_price) as ord_price
from (
select
left(oi.settle_date,4) as sYear
, substring(convert(varchar,oi.settle_date),5,2) as sMonth
, right(oi.settle_date,2) as sDay
, substring(convert(varchar,oi.settle_time),1,2) as sHh
, convert(bigint, case when (orp.ord_pnum is not null) then (op.ord_price * op.ord_cnt) - orp.price else (op.ord_price * op.ord_cnt) end) as ord_price
from TABLE_A oi
join TABLE_B op on oi.ord_num = op.ord_num
left join TABLE_C orp on op.ord_pnum = orp.ord_pnum and orp.[State] = 3 and orp.[State1] = 1
where oi.settle_flag = 1
and op.ord_state in (2,4,5,6,7)
and op.prod_type = 0
and left(oi.settle_date,4) = @setYear
) q1
group by sYear, sMonth
) q3 on q2.sYear = q3.sYear and q2.sMonth = q3.sMonth
order by q2.sYear, q2.sMonth
-----------------------
-- 일별
-----------------------
select q2.sYear as [년도]
, q2.sMonth as [월]
, q2.sDay as [일]
, '' as [시간]
, isnull(ord_price, 0) as [총매출액]
, isnull(ord_price - isnull(LAG(ord_price) OVER(ORDER BY q2.sYear, q2.sMonth, q2.sDay), 0), 0) as [차이금액]
, q2.sYear + '년 ' + q2.sMonth + '월 ' + q2.sDay + '일' as [xLabel]
--, q2.sYear + '.' + q2.sMonth + '.' + q2.sDay as [xLabel2]
, q2.sMonth + '.' + q2.sDay as [xLabel2]
, isnull(q2.[목표금액], 0) as [목표금액]
into #DayDatas
from #DayGoalTable q2
left join (
select sYear, sMonth, sDay, sum(ord_price) as ord_price
from (
select
left(oi.settle_date,4) as sYear
, substring(convert(varchar,oi.settle_date),5,2) as sMonth
, right(oi.settle_date,2) as sDay
, substring(convert(varchar,oi.settle_time),1,2) as sHh
, convert(bigint, case when (orp.ord_pnum is not null) then (op.ord_price * op.ord_cnt) - orp.price else (op.ord_price * op.ord_cnt) end) as ord_price
from TABLE_A oi
join TABLE_B op on oi.ord_num = op.ord_num
left join TABLE_C orp on op.ord_pnum = orp.ord_pnum and orp.[State] = 3 and orp.[State1] = 1
where oi.settle_flag = 1
and op.ord_state in (2,4,5,6,7)
and op.prod_type = 0
and left(oi.settle_date,4) = @setYear
and substring(convert(varchar,oi.settle_date),5,2) = @setMonth
) q1
group by sYear, sMonth, sDay
) q3 on q2.sYear = q3.sYear and q2.sMonth = q3.sMonth and q2.sDay = q3.sDay
order by q2.sYear, q2.sMonth, q2.sDay
-----------------------
-- 시간별
-----------------------
select q2.sYear as [년도]
, q2.sMonth as [월]
, q2.sDay as [일]
, q2.sHh as [시간]
, isnull(ord_price, 0) as [총매출액]
, isnull(ord_price - isnull(LAG(ord_price) OVER(ORDER BY q2.sYear, q2.sMonth, q2.sDay, q2.sHh), 0), 0) as [차이금액]
, q2.sYear + '년 ' + q2.sMonth + '월 ' + q2.sDay + '일' + q2.sHh + '시간' as [xLabel]
--, q2.sYear + '.' + q2.sMonth + '.' + q2.sDay as [xLabel2]
, q2.sHh + '시' as [xLabel2]
, isnull(q2.[목표금액], 0) as [목표금액]
into #HhDatas
from #HhGoalTable q2
left join (
select sYear, sMonth, sDay, sHh, sum(ord_price) as ord_price
from (
select
left(oi.settle_date,4) as sYear
, substring(convert(varchar,oi.settle_date),5,2) as sMonth
, right(oi.settle_date,2) as sDay
, substring(convert(varchar,oi.settle_time),1,2) as sHh
, convert(bigint, case when (orp.ord_pnum is not null) then (op.ord_price * op.ord_cnt) - orp.price else (op.ord_price * op.ord_cnt) end) as ord_price
from TABLE_A oi
join TABLE_B op on oi.ord_num = op.ord_num
left join TABLE_C orp on op.ord_pnum = orp.ord_pnum and orp.[State] = 3 and orp.[State1] = 1
where oi.settle_flag = 1
and op.ord_state in (2,4,5,6,7)
and op.prod_type = 0
--and left(oi.settle_date,4) = @setYear
--and substring(convert(varchar,oi.settle_date),5,2) = @setMonth
--and substring(convert(varchar,oi.settle_date),7,2) = @setDay
and oi.settle_date = @setYear + @setMonth + @setDay
) q1
group by sYear, sMonth, sDay, sHh
) q3 on q2.sYear = q3.sYear and q2.sMonth = q3.sMonth and q2.sDay = q3.sDay and q2.sHh = q3.sHh
order by q2.sYear, q2.sMonth, q2.sDay, q2.sHh
--select * from #YearDatas
--select * from #MonthDatas
--select * from #DayDatas
--select * from #HhDatas order by 시간
-----------------------
-- 년 (JSON 형식)
-----------------------
select '[' + stuff((select ',"' + xLabel + '"' from #YearDatas order by [년도] for xml path('')), 1, 1, '') + ']' as y_xLabels
, '[' + stuff((select ',' + CONVERT(varchar, 총매출액) from #YearDatas order by [년도] for xml path('')), 1, 1, '') + ']' as y_datas1
, '[' + stuff((select ',' + CONVERT(varchar, 목표금액) from #YearDatas order by [년도] for xml path('')), 1, 1, '') + ']' as y_datas2
, '[' + stuff((select ',' + CONVERT(varchar, 차이금액) from #YearDatas order by [년도] for xml path('')), 1, 1, '') + ']' as y_datas3
, '[' + (select CONVERT(varchar, isnull(sum(convert(bigint, 총매출액)),0)) from #YearDatas) + ']' as y_datas1_sum
-----------------------
-- 월 (JSON 형식)
-----------------------
select '[' + stuff((select ',"' + xLabel + '"' from #MonthDatas order by [월] for xml path('')), 1, 1, '') + ']' as m_xLabels
, '[' + stuff((select ',' + CONVERT(varchar, 총매출액) from #MonthDatas order by [월] for xml path('')), 1, 1, '') + ']' as m_datas1
, '[' + stuff((select ',' + CONVERT(varchar, 목표금액) from #MonthDatas order by [월] for xml path('')), 1, 1, '') + ']' as m_datas2
, '[' + stuff((select ',' + CONVERT(varchar, 차이금액) from #MonthDatas order by [월] for xml path('')), 1, 1, '') + ']' as m_datas3
, '[' + (select CONVERT(varchar, isnull(sum(convert(bigint, 총매출액)),0)) from #MonthDatas) + ']' as m_datas1_sum
-----------------------
-- 일 (JSON 형식)
-----------------------
--select '[' + stuff((select ',"' + xLabel + '"' from #DayDatas for xml path('')), 1, 1, '') + ']' as d_xLabels
select '[' + stuff((select ',"' + xLabel2 + '"' from #DayDatas order by [일] for xml path('')), 1, 1, '') + ']' as d_xLabels
, '[' + stuff((select ',' + CONVERT(varchar, 총매출액) from #DayDatas order by [일] for xml path('')), 1, 1, '') + ']' as d_datas1
, '[' + stuff((select ',' + CONVERT(varchar, 목표금액) from #DayDatas order by [일] for xml path('')), 1, 1, '') + ']' as d_datas2
, '[' + stuff((select ',' + CONVERT(varchar, 차이금액) from #DayDatas order by [일] for xml path('')), 1, 1, '') + ']' as d_datas3
, '[' + (select CONVERT(varchar, isnull(sum(convert(bigint, 총매출액)),0)) from #DayDatas) + ']' as d_datas1_sum
-----------------------
-- 시간 (JSON 형식)
-----------------------
--select '[' + stuff((select ',"' + xLabel + '"' from #DayDatas for xml path('')), 1, 1, '') + ']' as hh_xLabels
select '[' + stuff((select ',"' + xLabel2 + '"' from #HhDatas order by [시간] for xml path('')), 1, 1, '') + ']' as hh_xLabels
, '[' + stuff((select ',' + CONVERT(varchar, 총매출액) from #HhDatas order by [시간] for xml path('')), 1, 1, '') + ']' as hh_datas1
, '[' + stuff((select ',' + CONVERT(varchar, 목표금액) from #HhDatas order by [시간] for xml path('')), 1, 1, '') + ']' as hh_datas2
, '[' + stuff((select ',' + CONVERT(varchar, 차이금액) from #HhDatas order by [시간] for xml path('')), 1, 1, '') + ']' as hh_datas3
, '[' + (select CONVERT(varchar, isnull(sum(convert(bigint, 총매출액)),0)) from #HhDatas) + ']' as hh_datas1_sum
-----------------------
-- #임시 테이블 삭제
-----------------------
if OBJECT_ID('tempdb..#MonthGoalTable') is not null drop table #MonthGoalTable
if OBJECT_ID('tempdb..#MonthGoalTable') is not null drop table #MonthGoalTable
if OBJECT_ID('tempdb..#DayGoalTable') is not null drop table #DayGoalTable
if OBJECT_ID('tempdb..#HhGoalTable') is not null drop table #HhGoalTable
if OBJECT_ID('tempdb..#YearDatas') is not null drop table #YearDatas
if OBJECT_ID('tempdb..#MonthDatas') is not null drop table #MonthDatas
if OBJECT_ID('tempdb..#DayDatas') is not null drop table #DayDatas
if OBJECT_ID('tempdb..#HhDatas') is not null drop table #HhDatas
END
