From 9129c14135a79a2a99df228a9993a775867c8930 Mon Sep 17 00:00:00 2001 From: newton-cn Date: Sat, 24 May 2025 11:56:07 +0900 Subject: [PATCH] mm --- .vs/newton-cn/v16/.suo | Bin 111104 -> 115200 bytes .../other_shop_find_ps.sql" | 52 +++ ...5\353\263\264\354\241\260\355\232\214.sql" | 65 ++++ ... \355\212\270\353\246\254\352\261\260.sql" | 35 ++ ...4\354\226\264\352\260\200\352\262\214.sql" | 205 ++++++++++++ ...4\353\241\234\354\213\234\354\240\200.sql" | 59 ++++ ...4\353\241\234\354\213\234\354\240\200.sql" | 311 ++++++++++++++++++ newton-cn/App.config | 2 +- newton-cn/Properties/Resources.Designer.cs | 2 +- newton-cn/Properties/Settings.Designer.cs | 2 +- newton-cn/newton-cn.csproj | 2 +- .../DesignTimeResolveAssemblyReferences.cache | Bin 137 -> 11 bytes ...gnTimeResolveAssemblyReferencesInput.cache | Bin 2588 -> 7723 bytes .../Properties.Resources.Designer.cs.dll | Bin 4096 -> 3584 bytes .../newton-cn.csproj.AssemblyReference.cache | Bin 2006 -> 11 bytes 15 files changed, 731 insertions(+), 4 deletions(-) create mode 100644 "DB_\355\224\204\353\241\234\354\213\234\354\240\200,\355\212\270\353\246\254\352\261\260/\354\277\274\353\246\254\353\254\270/other_shop_find_ps.sql" create mode 100644 "DB_\355\224\204\353\241\234\354\213\234\354\240\200,\355\212\270\353\246\254\352\261\260/\354\277\274\353\246\254\353\254\270/\353\213\244\353\245\270\353\247\244\354\236\245\355\232\214\354\233\220\354\240\225\353\263\264\354\241\260\355\232\214.sql" create mode 100644 "DB_\355\224\204\353\241\234\354\213\234\354\240\200,\355\212\270\353\246\254\352\261\260/\354\277\274\353\246\254\353\254\270/\354\203\201\355\222\210 visible \354\227\205\353\215\260\354\235\264\355\212\270\354\213\234 \355\212\270\353\246\254\352\261\260.sql" create mode 100644 "DB_\355\224\204\353\241\234\354\213\234\354\240\200,\355\212\270\353\246\254\352\261\260/\354\277\274\353\246\254\353\254\270/\354\203\201\355\222\210\353\223\261\353\241\235 \354\236\220\353\217\231\354\234\274\353\241\234 order \353\223\244\354\226\264\352\260\200\352\262\214.sql" create mode 100644 "DB_\355\224\204\353\241\234\354\213\234\354\240\200,\355\212\270\353\246\254\352\261\260/\354\277\274\353\246\254\353\254\270/\354\235\274\355\232\214\354\232\251\354\240\201\353\246\275\355\224\204\353\241\234\354\213\234\354\240\200.sql" create mode 100644 "DB_\355\224\204\353\241\234\354\213\234\354\240\200,\355\212\270\353\246\254\352\261\260/\354\277\274\353\246\254\353\254\270/\354\235\274\355\232\214\354\232\251\355\224\204\353\241\234\354\213\234\354\240\200.sql" diff --git a/.vs/newton-cn/v16/.suo b/.vs/newton-cn/v16/.suo index cd8dec73d8460315d12e29e39c62307ea1a2e408..1c4ac091e1499aad098022e354886d864ccd959b 100644 GIT binary patch delta 3860 zcmd6qdr(y86~OPgdw1DIM1qST5LtWyk;ft+i(-pHEFlMKb7qtnUQrq!TSrROYn3EEC;&9u{R z=Er$_5AOZGbIy0yec9yR6tpcjB3+Us195wBaFB!qMCRMh7}?@_Du3tWr_MnMHp zC#DcQIhWw^$+~=;WIkadczh(uI3k>gAWTF&F_xG|%p#(QUA$ZjDZE&)E?F-mJ*(9@ zw%JI^WFnimMIAFpwh;~@hZs#vAjT7d;K_kDb%Z@;gr2ACmJvRlGmY$G-M4^bvn0)x z7Lu}vSfY0%l6*qfdD3#ySLn8DgrED$$zIv&OmME!J64mdyK7KA*$qS^@htHjqKQ~X z@FL$O`8>h<>ehYh@s_dH{NdbtJ@V$jk@JGwLTDl0&QN_p_HiKaSp#0+M%}F$IOu9|W|o z&1UN5N9T5*C;U|o*z{jLVauL~AJhBsaWDsU{ipbXV=NvE-eLKKJijB(qH|P?<{lLb z|Jj7mg|fm2=r0Vt1}k>HrfAp4bi+S*Ap~!XJ&9d%x^~qb0_wQQ+T`#!umDFm7Y4ny zw0XwKxXEEftK)<%kfJlGe-^<-gJ0xpy2P7~#IPbV9wj*M_7j+yb4o2LRK~H+1*_K- zg-u1p<_E~Z?SzAc+4bSvd+zWmwkZj2V0$!ptIGPJ^wwjjuKtOG2&Om z`vlKCLGstcN#XEyz88_vTY5hlh-Kk&))p7nS{%zP z-!p4w_x1ZZ%zHYI-(%Ob+3G@yBbzNyTim0DDhchABx!@qps0)UE&h+Fc=815jn=TZ z1yrkED~7KMm%Yl&r6o{su-95?0p9T8!p65rLqv%Y@_9ME1Q|J*615viL9+Vt-4!-| z08B$QSQ{8`lD*;i|Kxh{s)eAG-SLFjA_od!pqTj!pt^+03cdK!0yAF(ks~Ile^sz) z=|P-a6=(5(+qp(`Y7JE_;GNe6*kw&OR#MLa?Z);+1}xtGj(1nAjIoDbw;a8dv0~_J z8+sb|+1~~^JP|{8_n@NP#O^)qX!C?XHpcIbfo$Pi1>>=8ui5W(_??a~ozJ!%_q?S* z=9eCtt3AE92Rw%rL$y>t+@ZPlT{LJtdu{{T57c7VITMy%NY*ayFNJ;Gdri2ZuL3(i zwQ7CuZ#H<2hvSBeSKmI6I`{jp+44z&S)*iKL?lW$a zcJRVt8T0zy)UNk7DA;$|rrECc0#5JqY7?&Z0oqPiXrWgYgE$fl^`cvW8F-_=T9l1} zEPVBE_3b9Qzxq0sqx~0A+Jq|wpmhuu$yhsE@hN67%2Ug$?e@&+85Jot_VVnM%o#P6 zDHZmdtdtq$IT;x_m6=)goa$gnthGV4NE-#gzTLr40lchOt3a8r%nEyRw}R+#Fa+(=&^|SWJndRtq|_Z+d}_e ztaZXmVpJ8_McPI%`i{7uz=*b+LA-SlzSt+ht%y2=XkW)JC<62@FVG5Va{t}OQ%5Zv pQGDAFRx4sd6KodmJO?lOHtm4p(Eb*fg{Kal@vk^Wv~7l>zXKe%QOf`T delta 4945 zcmd5<4RBP|72fmmcC))lb~l?PSwc*fBp^Te*@XNP>n7R35Pm}V(P#o}{tCnp0wn%m z$Hc-oC=kqE~3CQYIWL~ zdS||O&pG$rbI(2J-1FY+*b>@tPV-pdganmJ6@vI`WMl+86(DByR!kCfiwZ(}O~>Dd zxvWiShtuo}VY48JY*R>?wiqX=5o~CIg$-#e5k6$@i^T`FWCmhP`wXN|qX2b*pnJ^hqgvdZx5pjq;Dpj*8 z3kh1F$KHYnM?@jA5z&ZS5j0M1rl0;2=>nv45Qh}wG-o#Q5r}w&d?M01#O|(&1X$Wt zF|)lLxoQ3xmDpGLY0|LVPp`m!C8Aa_R)=&wq5<)J#45yU#2Q2+q6tAd*7b&K|wnr|Ayc4Y`jJGErgOg=G;?Jz=t4Fp3O)LZ=WuM27@ z&Y@&b2317I823{&l0xt^><=Pd@bfQX&-(OxyXuprj<7iR z=942z1u=nEvQGp1y85oi4)z6af;mrS_(Q{$Up&Mt?eC}EhuO+OV_qt1I-;s zADS**OVWs5H|3qA9IzkLuak|pa>h}-&sjr>fCsizV^wTn`nGVN9b z8@5hkTdY=AIPnd4;uUz7eUUh#qayOQykpUDlw>OW%eU6$DHgpy8UQvRWQ0?3cpgE` zJ;SMu#GdM7z!#9m+GZ?ar86s;ZRT@gT42bPXmiJwic1I9-XL(s`_HQn{9DEuA?aR+k|oNLS~D3cSAr7W2iqkQND8gPXAs*;FN}MXF`0 z*}Nhb(#g*8ViuW}9$I;luCIz+PRj>iMQe_J+cQ0gS+w{A@MH>}If8o+a+QA!k)WUi zM5r9KBUwAd^h&{5eE>M zDR-%ELW0T!l?W;iR3@lA^dNc>5`t8|g#F71GJ^8vFya-&5yY#AKE!K?*AYh%$GR#e z!Mj}*xe>oZ?lj^I;;XKjeR#K7@}2}8EJ1+_K^Y^t_Ur}by~`;%rxk+f$$`r;rx)yO zd#gWT%G{ZM@8cEsKoU33f@FRs6{i1xeM#4ZT=E_8rDv(&Y0`T(X!wVfP|W*RgUH&e zQ`qcfI_ZHGyMdjWn=UzA{ov^kzp;1STb=T4j`hceKHOOA)DdXhpYwLa1Tnog8F zVP)46uZ2_HldBo_URx_b3~Srb9=-tCT6&dZbC>;E3>I{%JM&7Yw6fND+r4%tXaVO? zQU}V&xPt#i zqsQVWz1wJ$<(CWF($6*~$}}3I+92ghZ~de~04ICs)DnKN1)`b#S(|RlnF~vtZ_>B1 zLYxWn#@P3st%QXv!~2dxC~exG0<7TqW31_wU99P#mA`&3XeC?UkeUsiF-V4EiGt^V z$UFmXw*51`ROi-0|N zBu?tNlqWFvWeYp;D~r_ksZ-4s{(cwp{^Fpt`?E$78Lt$7-dYC+A{o1uZHt3l7779j-(8~9Z%Wc!YbAOi16!g*;rSi{#PAcevP-)rG;AHIRc zH2MY&kPMMA6neSE^;3lnXOX|HVy=@``GnG;kum{P{FDLK%UkhgBZTo6P4GZu$LKPA zn)<1JNAN#S!7a%S9=;q5zVneVQQ&)%K;sz}c!vO@PZU5c_;h+02L7-M3izF|V4+8y zx}H?{ex3|{_>P^kZo}G~w7QM9DrdvSHLIGJ!=&bhdt28vr`9!BUq8ruLF_z*%7Lwt%MlRuH;PAcQ@EpZWB=rdLU%_kPw=9md1z z!48MV@!9Z7kQdgj>o$YHTTGCpoJQ_fOyf_LVUncY2`M1!zP&pcw7&jKaDdN|3jN`{ zrVNtg4Xf_P4HX8^!IYZ*u^DodJK=b?lE?Mg{Ky?(P%5EQh)iTd}3*ioa zxDamPHRbpjNlr%ZF^sdtz(1{mlIRZHVYK;E9vP=1M#0>N(%{j!Z#hI< z$2VN>R~{Y(M$_C)1=Sz-?D}N$$ULoC0%04B?BDOib79P(D i.PR_ORDER; + + -- 1 → 0 (보임 처리) : pr_order 재설정만 (자리 만드는 작업 없음) + UPDATE p + SET p.PR_ORDER = ( + SELECT COUNT(*) + FROM pr + WHERE kind_code = i.kind_code AND pr_visible = 0 + ) + FROM pr p + JOIN inserted i ON p.PR_CODE = i.PR_CODE + JOIN deleted d ON i.PR_CODE = d.PR_CODE + WHERE + i.PR_VISIBLE = 0 AND d.PR_VISIBLE = 1; + END +END; diff --git "a/DB_\355\224\204\353\241\234\354\213\234\354\240\200,\355\212\270\353\246\254\352\261\260/\354\277\274\353\246\254\353\254\270/\354\203\201\355\222\210\353\223\261\353\241\235 \354\236\220\353\217\231\354\234\274\353\241\234 order \353\223\244\354\226\264\352\260\200\352\262\214.sql" "b/DB_\355\224\204\353\241\234\354\213\234\354\240\200,\355\212\270\353\246\254\352\261\260/\354\277\274\353\246\254\353\254\270/\354\203\201\355\222\210\353\223\261\353\241\235 \354\236\220\353\217\231\354\234\274\353\241\234 order \353\223\244\354\226\264\352\260\200\352\262\214.sql" new file mode 100644 index 0000000..82ee1f6 --- /dev/null +++ "b/DB_\355\224\204\353\241\234\354\213\234\354\240\200,\355\212\270\353\246\254\352\261\260/\354\277\274\353\246\254\353\254\270/\354\203\201\355\222\210\353\223\261\353\241\235 \354\236\220\353\217\231\354\234\274\353\241\234 order \353\223\244\354\226\264\352\260\200\352\262\214.sql" @@ -0,0 +1,205 @@ +-- 청라 +drop trigger trg_pr_insert; + +CREATE TRIGGER trg_pr_insert +ON pr +INSTEAD OF INSERT +AS +BEGIN + SET NOCOUNT ON; + + INSERT INTO pr ( + PR_CODE, + PR_NAME, + KIND_CODE, + PR_KIND, + PR_DATE, + PR_PRICE1, + PR_PRICE2, + PR_PRICE3, + PR_COST, + PR_PRT, + PR_CK, + PR_TAX, + PR_IVCK, + PR_DCGU, + PR_VISIBLE, + PR_SETGU, + PR_COSGU, + PR_OPTGU, + PR_CPRGU, + PR_ORDER, -- ← 덮어씌움 + PR_BARCODE, + PR_QTYS, + PR_QTY, + PR_UNT, + PR_INQTY, + PR_OPEN, + PR_POINT, + PR_PRINTAMT, + PR_REG, + PR_STYPE, + PR_ORDUSE, -- ← 덮어씌움 + PR_FONTSIZE, + PR_AFTERPRICE, + PR_EVT, + PR_ORDER_P, + PR_ORDER_Y, + PR_ORDER_X, + PR_VISIBLE2, + PR_GU, + PR_NICK, + rn + ) + SELECT + i.PR_CODE, + i.PR_NAME, + i.KIND_CODE, + i.PR_KIND, + i.PR_DATE, + i.PR_PRICE1, + i.PR_PRICE2, + i.PR_PRICE3, + i.PR_COST, + i.PR_PRT, + i.PR_CK, + i.PR_TAX, + i.PR_IVCK, + i.PR_DCGU, + i.PR_VISIBLE, + i.PR_SETGU, + i.PR_COSGU, + i.PR_OPTGU, + i.PR_CPRGU, + ( + SELECT COUNT(*) + 1 + FROM pr + WHERE KIND_CODE = i.KIND_CODE and pr_visible= 0 + ) AS PR_ORDER, -- 자동 계산 + i.PR_BARCODE, + i.PR_QTYS, + i.PR_QTY, + i.PR_UNT, + i.PR_INQTY, + i.PR_OPEN, + i.PR_POINT, + i.PR_PRINTAMT, + i.PR_REG, + i.PR_STYPE, + 1 AS PR_ORDUSE, -- 강제 설정 + i.PR_FONTSIZE, + i.PR_AFTERPRICE, + i.PR_EVT, + i.PR_ORDER_P, + i.PR_ORDER_Y, + i.PR_ORDER_X, + i.PR_VISIBLE2, + i.PR_GU, + i.PR_NICK, + i.rn + FROM inserted i; +END; + + + +-- 루원 +drop trigger trg_pr_insert; + +CREATE TRIGGER trg_pr_insert +ON pr +INSTEAD OF INSERT +AS +BEGIN + SET NOCOUNT ON; + + INSERT INTO pr ( + PR_CODE, + PR_NAME, + KIND_CODE, + PR_KIND, + PR_DATE, + PR_PRICE1, + PR_PRICE2, + PR_PRICE3, + PR_COST, + PR_PRT, + PR_CK, + PR_TAX, + PR_IVCK, + PR_DCGU, + PR_VISIBLE, + PR_SETGU, + PR_COSGU, + PR_OPTGU, + PR_CPRGU, + PR_ORDER, -- ← 덮어씌움 + PR_BARCODE, + PR_QTYS, + PR_QTY, + PR_UNT, + PR_INQTY, + PR_OPEN, + PR_POINT, + PR_PRINTAMT, + PR_REG, + PR_STYPE, + PR_ORDUSE, -- ← 덮어씌움 + PR_FONTSIZE, + PR_AFTERPRICE, + PR_EVT, + PR_ORDER_P, + PR_ORDER_Y, + PR_ORDER_X, + PR_VISIBLE2, + PR_GU, + PR_NICK, + rn + ) + SELECT + i.PR_CODE, + i.PR_NAME, + i.KIND_CODE, + i.PR_KIND, + i.PR_DATE, + i.PR_PRICE1, + i.PR_PRICE2, + i.PR_PRICE3, + i.PR_COST, + i.PR_PRT, + i.PR_CK, + i.PR_TAX, + i.PR_IVCK, + i.PR_DCGU, + i.PR_VISIBLE, + i.PR_SETGU, + i.PR_COSGU, + i.PR_OPTGU, + i.PR_CPRGU, + ( + SELECT COUNT(*) + 1 + FROM pr + WHERE KIND_CODE = i.KIND_CODE and pr_visible= 0 + ) AS PR_ORDER, -- 자동 계산 + i.PR_BARCODE, + i.PR_QTYS, + i.PR_QTY, + i.PR_UNT, + i.PR_INQTY, + i.PR_OPEN, + i.PR_POINT, + i.PR_PRINTAMT, + i.PR_REG, + i.PR_STYPE, + 1 AS PR_ORDUSE, -- 강제 설정 + i.PR_FONTSIZE, + i.PR_AFTERPRICE, + i.PR_EVT, + i.PR_ORDER_P, + i.PR_ORDER_Y, + i.PR_ORDER_X, + i.PR_VISIBLE2, + i.PR_GU, + i.PR_NICK, + i.rn + FROM inserted i; +END; diff --git "a/DB_\355\224\204\353\241\234\354\213\234\354\240\200,\355\212\270\353\246\254\352\261\260/\354\277\274\353\246\254\353\254\270/\354\235\274\355\232\214\354\232\251\354\240\201\353\246\275\355\224\204\353\241\234\354\213\234\354\240\200.sql" "b/DB_\355\224\204\353\241\234\354\213\234\354\240\200,\355\212\270\353\246\254\352\261\260/\354\277\274\353\246\254\353\254\270/\354\235\274\355\232\214\354\232\251\354\240\201\353\246\275\355\224\204\353\241\234\354\213\234\354\240\200.sql" new file mode 100644 index 0000000..9eec39e --- /dev/null +++ "b/DB_\355\224\204\353\241\234\354\213\234\354\240\200,\355\212\270\353\246\254\352\261\260/\354\277\274\353\246\254\353\254\270/\354\235\274\355\232\214\354\232\251\354\240\201\353\246\275\355\224\204\353\241\234\354\213\234\354\240\200.sql" @@ -0,0 +1,59 @@ + +drop procedure disposable_analysis_earn; + +EXEC disposable_analysis_earn '2025-05-03'; + +CREATE PROCEDURE disposable_analysis_earn + @SelectedDate DATETIME +AS +BEGIN + SET NOCOUNT ON; + SELECT + CONVERT(CHAR(19), a1.ps_date, 20) AS ps_date, + a2.ct_code, + a2.ct_name + '(' + a5.ct_phone + ')' AS ct_name, + a1.ps_prname, + CAST(a1.ps_qty AS INT) AS ps_qty, + CAST( + ( + ISNULL(( + SELECT SUM(ps_up * ps_qty) + FROM ps + WHERE pe_id = a1.pe_id and ps_no= a1.ps_no + AND ps_prcode IN ( + SELECT pr_code FROM pr + where kind_code in (select kind_code from disposable_analysis_info where gubun in ('wevape', 'etc')) + ) + ), 0) + ) + + + ( + ISNULL(( + SELECT SUM(ps_up * ps_qty) + FROM ps + WHERE pe_id = a1.pe_id + AND ps_prcode IN ( + SELECT pr_code FROM pr + WHERE kind_code = '1100' + ) + ), 0) + ) + AS INT) AS pe_bamt, + a1.pe_id, + a1.ps_no, + ISNULL(a3.is_earn, 0) AS is_earn, + (case when a1.ps_dc != 0 then 'O' else 'X' end) as is_dc + FROM ps a1 + JOIN pe a2 ON a1.pe_id = a2.pe_id AND a2.ct_code != '' + LEFT JOIN disposable_earn_point_status a3 ON a3.pe_id = a1.pe_id AND a3.ps_no = a1.ps_no + JOIN ac a4 ON a4.pe_id = a1.pe_id AND a4.ac_fncd2 NOT IN ('01', '00') + JOIN ct a5 ON a5.ct_code = a2.ct_code + WHERE + a1.ps_prcode IN ( + SELECT pr_code FROM pr WHERE kind_code IN (select kind_code from disposable_analysis_info where gubun in ('wevape', 'etc')) + ) + AND a1.ps_date >= @SelectedDate + AND a1.ps_date < DATEADD(DAY, 1, @SelectedDate) + ORDER BY + a1.pe_id DESC; +END; \ No newline at end of file diff --git "a/DB_\355\224\204\353\241\234\354\213\234\354\240\200,\355\212\270\353\246\254\352\261\260/\354\277\274\353\246\254\353\254\270/\354\235\274\355\232\214\354\232\251\355\224\204\353\241\234\354\213\234\354\240\200.sql" "b/DB_\355\224\204\353\241\234\354\213\234\354\240\200,\355\212\270\353\246\254\352\261\260/\354\277\274\353\246\254\353\254\270/\354\235\274\355\232\214\354\232\251\355\224\204\353\241\234\354\213\234\354\240\200.sql" new file mode 100644 index 0000000..5d3cc47 --- /dev/null +++ "b/DB_\355\224\204\353\241\234\354\213\234\354\240\200,\355\212\270\353\246\254\352\261\260/\354\277\274\353\246\254\353\254\270/\354\235\274\355\232\214\354\232\251\355\224\204\353\241\234\354\213\234\354\240\200.sql" @@ -0,0 +1,311 @@ +create procedure disposable_analysis_old + @year_month nvarchar(7) -- "YYYY-MM" 형식의 월별 입력값 +as +begin + -- 시작일과 종료일 계산 + declare @start_date datetime; + declare @end_date datetime; + + set @start_date = convert(datetime, @year_month + '-01'); + set @end_date = dateadd(month, 1, @start_date); + + -- 임시 테이블 생성 (판매개수 컬럼으로 변경) + create table #product_sales ( + product_name varchar(100), + product_sales varchar(50), -- 문자열로 저장 (반점 포함) + product_ratio varchar(10), -- 문자열로 저장 + product_qty int -- 판매개수 컬럼 + ); + + -- 매출 계산 + declare @totalamount numeric(18, 0); + declare @disposableamount numeric(18, 0); + + select + @totalamount = sum(ps_amt + ps_vat), + @disposableamount = sum(case when kind.kind_code in (1099, 1105, 1106, 1107, 1108) then ps_amt + ps_vat else 0 end) + from ps + join pr on ps.ps_prcode = pr.pr_code + join kind on pr.kind_code = kind.kind_code + where ps.ps_date >= @start_date and ps.ps_date < @end_date; + + declare @totalamount_str varchar(50); + declare @disposableamount_str varchar(50); + + set @totalamount_str = replace(convert(varchar(50), cast(@totalamount as money), 1), '.00', ''); + set @disposableamount_str = replace(convert(varchar(50), cast(@disposableamount as money), 1), '.00', ''); + + insert into #product_sales + values (@year_month + ' ' + '매출', @totalamount_str, null, null); + + insert into #product_sales + values (@year_month + ' ' + '일회용매출', @disposableamount_str, null, null); + + declare @disposable_ratio varchar(10); + if @totalamount > 0 + begin + set @disposable_ratio = cast(cast(@disposableamount * 100.0 / @totalamount as numeric(5, 2)) as varchar(10)) + '%'; + end + else + begin + set @disposable_ratio = '0%'; + end + + update #product_sales + set product_ratio = @disposable_ratio + where product_name = @year_month + ' ' + '일회용매출'; + + insert into #product_sales + select + pr.pr_name as product_name, + replace(convert(varchar(50), cast(sum(ps.ps_amt + ps.ps_vat) as money), 1), '.00', '') as product_sales, + cast(cast(sum(ps.ps_amt + ps.ps_vat) * 100.0 / nullif(@disposableamount, 0) as numeric(5, 2)) as varchar(10)) + '%' as product_ratio, + sum(ps.ps_qty) as product_qty + from ps + join pr on ps.ps_prcode = pr.pr_code + join kind on pr.kind_code = kind.kind_code + where ps.ps_date >= @start_date and ps.ps_date < @end_date + and kind.kind_code in (1099, 1105, 1106, 1107, 1108) + group by pr.pr_name + having sum(ps.ps_amt + ps.ps_vat) > 0; + + declare @total_qty int; + select @total_qty = sum(ps.ps_qty) + from ps + join pr on ps.ps_prcode = pr.pr_code + join kind on pr.kind_code = kind.kind_code + where ps.ps_date >= @start_date and ps.ps_date < @end_date + and kind.kind_code in (1099, 1105, 1106, 1107, 1108); + + update #product_sales + set product_qty = @total_qty + where product_name = @year_month + ' ' + '일회용매출'; + + -- 최종 결과 출력: 매출 → 일회용매출 → 특정 상품들 → 나머지 + select + product_name as 상품명, + product_sales as 매출, + product_ratio as 비율, + product_qty as 개수 + from #product_sales + order by + case product_name + when @year_month + ' ' + '매출' then 1 + when @year_month + ' ' + '일회용매출' then 2 + when '와카' then 3 + when '쥬피터' then 4 + when '에이스' then 5 + when '크리에이터' then 6 + else 7 + end, + product_name; + + drop table #product_sales; +end; + +-- 실행 예제 +EXEC disposable_analysis @year_month = '2025-01'; + + + + +create procedure disposable_analysis_new + @year_month nvarchar(7) +as +begin + declare @start_date datetime; + declare @end_date datetime; + declare @color1 varchar(50); + declare @color2 varchar(50); + declare @color3 varchar(50); + + set @start_date = convert(datetime, @year_month + '-01'); + set @end_date = dateadd(month, 1, @start_date); + set @color1= 'LightYellow'; + set @color2= 'LightPink'; + set @color3= 'PeachPuff'; + + create table #product_sales ( + product_name varchar(100), + product_sales varchar(50), + product_ratio varchar(10), + product_qty int, + row_color varchar(20) + ); + + declare @totalamount numeric(18, 0); + declare @disposableamount numeric(18, 0); + + select + @totalamount = sum(ps_amt + ps_vat) + from ps + join pr on ps.ps_prcode = pr.pr_code + join kind on pr.kind_code = kind.kind_code + where ps.ps_date >= @start_date and ps.ps_date < @end_date; + + select + @disposableamount = sum(ps_amt + ps_vat) + from ps + join pr on ps.ps_prcode = pr.pr_code + join kind on pr.kind_code = kind.kind_code + join disposable_analysis_info on disposable_analysis_info.gubun in ('wevape', 'etc') and kind.kind_code= disposable_analysis_info.kind_code + where ps.ps_date >= @start_date and ps.ps_date < @end_date; + + declare @totalamount_str varchar(50); + declare @disposableamount_str varchar(50); + + set @totalamount_str = replace(convert(varchar(50), cast(@totalamount as money), 1), '.00', ''); + set @disposableamount_str = replace(convert(varchar(50), cast(@disposableamount as money), 1), '.00', ''); + + insert into #product_sales values (@year_month + ' 매출', @totalamount_str, null, null, @color1); + insert into #product_sales values (@year_month + ' 일회용매출', @disposableamount_str, null, null, @color1); + insert into #product_sales values ('----- 일회용매출 -----', null, null, null, null); + + declare @disposable_ratio varchar(10); + if @totalamount > 0 + set @disposable_ratio = cast(cast(@disposableamount * 100.0 / @totalamount as numeric(5, 2)) as varchar(10)) + '%'; + else + set @disposable_ratio = '0%'; + + update #product_sales + set product_ratio = @disposable_ratio + where product_name = @year_month + ' 일회용매출'; + + -- kind별 통합 매출 (매출 없어도 항상 표시되도록 수정) + insert into #product_sales + select + k.kind_name + ' 총 매출', + replace(convert(varchar(50), cast(isnull(sum(ps.ps_amt + ps.ps_vat), 0) as money), 1), '.00', ''), + case when @disposableamount > 0 then + cast(cast(isnull(sum(ps.ps_amt + ps.ps_vat), 0) * 100.0 / nullif(@disposableamount, 0) as numeric(5, 2)) as varchar(10)) + '%' + else '0%' end, + isnull(sum(ps.ps_qty), 0), + @color2 + from ( + select kind_code, kind_name + from kind + where kind_code in (select kind_code from disposable_analysis_info where gubun in ('wevape')) + ) k + left join pr on k.kind_code = pr.kind_code + left join ps on ps.ps_prcode = pr.pr_code and ps.ps_date >= @start_date and ps.ps_date < @end_date + group by k.kind_name; + + -- '그 외 매출 요약' 추가 + declare @sum_known_kind numeric(18, 0); + declare @etc_qty int; + + select @etc_qty= sum(ps.ps_qty) from ps join pr on ps.ps_prcode= pr.pr_code join kind on kind.kind_code= 1099 and kind.kind_code= pr.kind_code where ps.ps_date >= @start_date and ps.ps_date < @end_date + + select @sum_known_kind = sum(amount) + from ( + select sum(ps.ps_amt + ps.ps_vat) as amount + from ps + join pr on ps.ps_prcode = pr.pr_code + join kind k on pr.kind_code = k.kind_code + where ps.ps_date >= @start_date and ps.ps_date < @end_date + and k.kind_code in (select kind_code from disposable_analysis_info where gubun in ('wevape')) + group by k.kind_code + ) as subquery; + + declare @other_amount numeric(18, 0); + set @other_amount = @disposableamount - isnull(@sum_known_kind, 0); + + insert into #product_sales + values ( + '그 외 매출', + replace(convert(varchar(50), cast(isnull(@other_amount, 0) as money), 1), '.00', ''), + case when @disposableamount > 0 then cast(cast(isnull(@other_amount, 0) * 100.0 / nullif(@disposableamount, 0) as numeric(5, 2)) as varchar(10)) + '%' else '0%' end, + @etc_qty, + @color3 + ); + + -- 상세 매출내용 시작 + insert into #product_sales + values ('----- 상세내역 -----', null, null, null, null); + + -- 상품별 상세 매출 삽입 + insert into #product_sales + select + pr.pr_name, + replace(convert(varchar(50), cast(sum(ps.ps_amt + ps.ps_vat) as money), 1), '.00', ''), + cast(cast(sum(ps.ps_amt + ps.ps_vat) * 100.0 / nullif(@disposableamount, 0) as numeric(5, 2)) as varchar(10)) + '%', + sum(ps.ps_qty), + null + from ps + join pr on ps.ps_prcode = pr.pr_code + join kind on pr.kind_code = kind.kind_code + where ps.ps_date >= @start_date and ps.ps_date < @end_date + and kind.kind_code in (select kind_code from disposable_analysis_info where gubun in ('wevape', 'etc')) + group by pr.pr_name + having sum(ps.ps_amt + ps.ps_vat) > 0; + + -- 총 수량 계산해서 일회용매출에 반영 + declare @total_qty int; + select @total_qty = sum(ps.ps_qty) + from ps + join pr on ps.ps_prcode = pr.pr_code + join kind on pr.kind_code = kind.kind_code + where ps.ps_date >= @start_date and ps.ps_date < @end_date + and kind.kind_code in (select kind_code from disposable_analysis_info where gubun in ('wevape', 'etc')); + + update #product_sales + set product_qty = @total_qty + where product_name = @year_month + ' 일회용매출'; + + -- 최종 결과 + select + product_name as 상품명, + product_sales as 매출, + product_ratio as 비율, + product_qty as 개수, + row_color + from #product_sales + order by + case + when product_name = @year_month + ' 매출' then 1 + when product_name = @year_month + ' 일회용매출' then 2 + when product_name = '----- 일회용매출 -----' then 3 + when (product_name like '% 총 매출') then 4 + when product_name = '그 외 매출' then 5 + when product_name = '----- 상세내역 -----' then 6 + else 7 + end, + product_name; + + drop table #product_sales; +end; + + + + + + + +create procedure disposable_analysis + @year_month nvarchar(7) -- "YYYY-MM" 형식 +as +begin + -- 변수 선언 + declare @standard_month nvarchar(7); + set @standard_month = '2025-05'; + + if @year_month < @standard_month + begin + -- 기준월 이전이면 old 프로시저 호출 + exec disposable_analysis_old @year_month; + end + else + begin + -- 기준월 이후거나 같으면 new 프로시저 호출 + exec disposable_analysis_new @year_month; + end +end; + + + + + +EXEC disposable_analysis_old @year_month = '2025-05'; +EXEC disposable_analysis_new @year_month = '2025-05'; +EXEC disposable_analysis @year_month = '2025-05'; +drop procedure disposable_analysis_new; \ No newline at end of file diff --git a/newton-cn/App.config b/newton-cn/App.config index 92c094c..e8e81a2 100644 --- a/newton-cn/App.config +++ b/newton-cn/App.config @@ -2,5 +2,5 @@ - + diff --git a/newton-cn/Properties/Resources.Designer.cs b/newton-cn/Properties/Resources.Designer.cs index c17df5e..e2147a8 100644 --- a/newton-cn/Properties/Resources.Designer.cs +++ b/newton-cn/Properties/Resources.Designer.cs @@ -19,7 +19,7 @@ namespace newton_cn.Properties { // 클래스에서 자동으로 생성되었습니다. // 멤버를 추가하거나 제거하려면 .ResX 파일을 편집한 다음 /str 옵션을 사용하여 ResGen을 // 다시 실행하거나 VS 프로젝트를 다시 빌드하십시오. - [global::System.CodeDom.Compiler.GeneratedCodeAttribute("System.Resources.Tools.StronglyTypedResourceBuilder", "17.0.0.0")] + [global::System.CodeDom.Compiler.GeneratedCodeAttribute("System.Resources.Tools.StronglyTypedResourceBuilder", "16.0.0.0")] [global::System.Diagnostics.DebuggerNonUserCodeAttribute()] [global::System.Runtime.CompilerServices.CompilerGeneratedAttribute()] internal class Resources { diff --git a/newton-cn/Properties/Settings.Designer.cs b/newton-cn/Properties/Settings.Designer.cs index 17dbd47..21eeacd 100644 --- a/newton-cn/Properties/Settings.Designer.cs +++ b/newton-cn/Properties/Settings.Designer.cs @@ -12,7 +12,7 @@ namespace newton_cn.Properties { [global::System.Runtime.CompilerServices.CompilerGeneratedAttribute()] - [global::System.CodeDom.Compiler.GeneratedCodeAttribute("Microsoft.VisualStudio.Editors.SettingsDesigner.SettingsSingleFileGenerator", "17.14.0.0")] + [global::System.CodeDom.Compiler.GeneratedCodeAttribute("Microsoft.VisualStudio.Editors.SettingsDesigner.SettingsSingleFileGenerator", "16.10.0.0")] internal sealed partial class Settings : global::System.Configuration.ApplicationSettingsBase { private static Settings defaultInstance = ((Settings)(global::System.Configuration.ApplicationSettingsBase.Synchronized(new Settings()))); diff --git a/newton-cn/newton-cn.csproj b/newton-cn/newton-cn.csproj index fae4e76..321c85a 100644 --- a/newton-cn/newton-cn.csproj +++ b/newton-cn/newton-cn.csproj @@ -8,7 +8,7 @@ WinExe newton_cn newton-cn - v4.8 + v4.6.1 512 true true diff --git a/newton-cn/obj/Debug/DesignTimeResolveAssemblyReferences.cache b/newton-cn/obj/Debug/DesignTimeResolveAssemblyReferences.cache index 8b0abf355fa8c48924d70e6190f5cfa2bcd95065..f5e894aea93a73e58fa9d4feab241bc197ee1a40 100644 GIT binary patch literal 11 QcmeZu3JP{+WMlvW01Ze1dH?_b literal 137 zcmZQ$WMp7qU`%ngiU}ynPcKT$RdCD9Ni9~;sIV~8j0s9jOD#&xOHNgAEG|yXP0Gnk zEspWcOfJeV&QB|eaRaJPEzd8?j?wdT4MFhAO!O>b9Fvn%i;FXpGIKIZD)mxwa==zG ZsLnLMzUdkV$W|_(Is-jp17kx=1_0H-qJR^ZXDoFgN(BA;(tnGE0riBNu)CX_m=1c=~OE94?grw z&w(Kn-U4@()Usxr`&@_2FPqTg(wUV(AjN!fE@$5drMVP03o}lh{&Mzg=$lZ%O#z`% z%+J}2Vb$mE7tmUkPe9zP<|k{@wX3y4!JE#rNj6H5*It8kJLJAcc=N)5l8pz7H5#Ct zC8p*@BQ*LX4nE0zUpB$Jr8NXqzcsgEK+vr8LF_#%MUB@>+=pW>6CU@Nfo@N#)u(Hr z@0Sz{pefapZqqWWV%yP|+Dg**(B5-Yxy)1@OgyX{RyCM=s6=1jbs-fLp~lb^^FkY( zUvGA8#h2gQsjaTv^*G;K#o1TtcaE}sv{Rd1(=2iYu91p^KHTsOQ)OVvU0Y;kO&_lB zs@UI9vW6ZatBxqBb8S?sHz0$C86X#)LPa~Bu>efbXv4*T+%Sxq&Q4XckX$wCnQ$s(EmRs6DfZ>Vj>lz9;=W`ICxa-_SXFV!BeE5bg zxFhZ}KZHfb)uStM*RoMu)N^*AU8#It?d`Ft|GmYoUS-qn^i1LUs>651e*KSQ7nyPZQhy8PZq9c z-oyV><5_YyeXL_7oF*56XC24#h~xRbbs{Rk24rbPBF&TreA6fYAPvM6!yrYjVEW($ zOz;{qY3meXxc$c{gE2_>w1fFWRIpAX(3gDzx`h_FQ3hn4LA8ALMg{GwW42>Q$&Y<; zyHsFND}P<7oKctrzBONBZq#1 zkj+FP@dvbqM@V$g)Q|#0b3uCGN8lF`+)Wbv>!1yEDd}Y-pu1dr8<}+((UN&>cGmAU z)6r{lgb34s<_~CoUk|kUoh3(Xa}^H~13kK``BNBMU2mUkW6N(NF%@|Apl@)BM`!x4 zJ#b7S?)$y?eLmB8xH2}D`4r!J-6u=wBHeOWITT+d@?(;vY&61{z`{rgZz$4~nMUdA z&dH=HRehy4OSvmOS;}8&zSd_bPHfKZd1bO?<+1h1<{d5!9g@@O`8;l(Evta_r#6@O zz7J?^Sy!;VKPmAfYC5>N#F~`H(2c>CHHB6EN}Q@(bh6|uCBJkGU`?Y)X8EicRK1c& nWR#f8Jj~{@QF4ti`=W55SfOY5*TLKEziuoCZ@2g+X=VSv8v;DW literal 2588 zcmcImO>5gg5OrIVL!lR6T5{5{1$#+IDQTdG#I{JGHpX^8pekV{jpS|iL)~3lHpZ9y zp4>vA|0(?uofWQ3t|n`cS*^r-^JYf#_T{riqj5k=CPmsLDd$ZBOK7xkcho(Lm^4)8 zQK|H`DQE&w2@6_4htH3%3T1*$aDVsv@6GM~-JeK8X_f3GG8$=BfVK=wM2mt5Dp>}` zetrzV30TWyZiv|~x_hIZV{D0%8Ow9Tz@T$YMG!I$CV0I(efK6BLk1dT0>Qa4AY$$$ z4OpU;QJIZGd>>qHA~NhxcXx}opiF7j2OjT(}sI;hhdG3)5M^VOAi65&dl*EP`s1&%X81={c zO;ijXOXV0oG5O2aDA?BMf|_c?q4zbO;5G4(Gl$y@=bSr|-R}UGuhv~EQYG~F9!lTi=ic^*|tA0aZ1CE4#xqT-+1SbAVsE~iYu z7>weJI@`6|8Vc>#qCWc~;R0)v9DAAemMgn(!4#Ok|?=rKB zQ&bRNT4m#ySG6j@cl4W7?-Gg~>+qBMMK#f; zSL!f9wuswI@3yQ$MuqTf2p1LpPmYr$;yZtp30M*FXRM4kr@v6A>gy zBb!Y|N@{AX-R5}-6}E&eysfY!yTXYOcDg1Wb~>Jfc>{dT60Qr^`8SVk?r&H)btd74 zE$n&r40jv07|8Q%W&&m+%&-jN_$ETg8_4MEK}px9J2H#gi{{5e33N}ockbf$Qq|)N oD^qP3tN##&{ub+r^lWuCzVR%*Nm3EO?-X~J-pJPcCu2qLEy_Ej9smFU delta 1475 zcmY*ZYiJx*6#nj=+1c62Ycp9&LQ~vqB#%v-?9#^cLDjV>b(Pd6*+irDk;f!mx@P0< z#u9_wm54N2i(xK{SSf9;Qoe~K1bH26bG@w>CzfU|qQIo~F`$|XSytN7l}n?(%}(G6H!hmp7V*5*Xweqt zsCM5JU82_A5RWx&(M3Iva!B|PY5_ho0pb2;^%WzsBLG;4K3EZ?_NoB%Xq)U{H2$M# zh)EpaZ0hTAmihJ2U11r!Bgj!T_p{Ex9aVJBvOX(cL#XnXlwaAqx(jBM^BZCUbWERf>7 z3`gs5A(x(=$uku;4d7wETStZgKM+*%v5{my-$C*Z+-J4+rVtqevS5JiP%8zqAn0Dy zb``G(#hDoy@vSs)-otjWChM@p+y*wG^dE{4alfXpj-$&}77hu8uc3$f6x7_9hc9Sj^f`~7B2$!0Qpi$75TBz(YuaWh7NnSR4B`)|X?rNM z6ZCAqz zn#Ri&Hipw=zf_omIkIaWd)GXnz06Yn_ES|Bzf?#W{wF5?ky(`B60;uxpq0k%^G`Unj;hIZIECpvKiE25hl z@B+H2e~-8m8`QSqYobL=dOnk$X42D4q6@|Eu!Oz%lX!?uOP+bPf_t$?L~xh|=v=i= zH64qREyDs{C#JMzu2H96ojo6Y45H-c@Ix=r;eXF9eqFT(?>)!W0d&9c)#=B%C)^)> z8+AutoN%V5oT)^Cn|xy4T{MPjDssUY$&b&t-x&LAhn(r*@oA^9Ff^ZW^6qWpdt=ne z<}by`-7P%ZrT06uHC)) z_*XZhZ{M+=+6_G{gsFugd=%>IJ>2CBOHo_v$p`#?P1L+XK2^ z*u4ru$~b5bnqR(havAAmzL-M?*XyfqiItfDKWUoj AN&o-= diff --git a/newton-cn/obj/Debug/newton-cn.csproj.AssemblyReference.cache b/newton-cn/obj/Debug/newton-cn.csproj.AssemblyReference.cache index efeb79baf7bdc3adf0061d91882b7cd92b8d4ea7..f5e894aea93a73e58fa9d4feab241bc197ee1a40 100644 GIT binary patch literal 11 QcmeZu3JP{+WMlvW01Ze1dH?_b literal 2006 zcmc(f!AiqG5QdYBh!7eOyerJ~iFrVSo!kyfaOh-{Kcx^#CZ?yjbN6ko%K(bqAN zpvTQ+@UpP5@PGU>-|mHnK@fz`(N%J9uryo^Vj;l}j<@6UlVkw}7|0lMLK6|oXJ$J(+Ojp`u?rz2@U4)11CsL@q$!!7&|K<8$J#t-lf D{c4Vu -- 2.43.0