private void fn_get_safe_standard_deviation_log(Form1 frm1)
{
DateTime today = DateTime.Now.Date; //현재 날짜 확인
var ex_today = today.AddMonths(0); // 월 구함
var ex_sdate = ex_today.AddDays(1 - today.Day); //현재 날짜 기준의 월의 날짜 1일 구함
string safe_sale_sdate = string.Format(ex_sdate.AddDays(0).ToString("yyyy-MM-dd")); //전월 날짜 1일을 YYYY-MM-DD형식으로 변경
myConnection = DbConnection.GetRemoteDataInstance().getRemoteDbConnection("213");
string sql = null;
string sql_1 = null;
SqlCommand command = new SqlCommand(sql, myConnection);
SqlDataAdapter adapter = new SqlDataAdapter();
try
{
sql = " insert into preset_scm_stock_share_log "
+ " select '" + safe_sale_sdate + "', storeno, ppmidx, total_jungga, share, sum_share, grade "
+ " from preset_scm_stock_share ";
command.CommandText = sql;
command.CommandTimeout = 120;
command.ExecuteNonQuery();
try
{
sql_1 = " delete from preset_scm_stock_share ";
command.CommandText = sql_1;
command.CommandTimeout = 120;
command.ExecuteNonQuery();
}
catch (Exception ex)
{
frm1.sendSms("[전월 ABC누적 삭제]오류:" + ex.Message);
frm1.Log("[전월 ABC누적 삭제]오류:" + ex.Message);
}
}
catch (Exception ex)
{
frm1.sendSms("[전월 ABC누적 백업]오류:" + ex.Message);
frm1.Log("[전월 ABC누적 백업]오류:" + ex.Message);
}
myConnection.Dispose();
myConnection.Close();
}
private void fn_get_stock_preset_stock(string orderdate1, string orderdate2, Form1 frm1)
{
myConnection = DbConnection.GetRemoteDataInstance().getRemoteDbConnection("213");
string queryString1 = "";
string ckYYYYMM = "";
SqlDataAdapter adapter = new SqlDataAdapter();
ckYYYYMM = String.Format("{0:yyyyMM}", Convert.ToDateTime(orderdate1));
// frm1.sendSms("NEWPOS_SCM 입고량 생성 시작");
frm1.Log("NEWPOS_SCM 입고량 생성 시작");
string queryString = "select a.orderdate, right(a.storeno,4) as storeno, a.ppmidx, SUM(ISNULL(a.amt_result,0)) as amt_pre , SUM(ISNULL(a.amt_input,0)) as amt_input ,(case when (sum(a.amt_result) <= 0) THEN 1 ELSE 0 END) as amt_result "
+ " from pos_store_stock_" + ckYYYYMM + " as a with( nolock ) "
+ " right join webdb_se.dbo.stock_preset4_2019 as b with( nolock ) on a.orderdate = b.sdate and right(a.storeno,4) = b.storeno and a.ppmidx = b.ppmidx "
+ "where (b.sdate >= '" + orderdate1 + "' and b.sdate <= '" + orderdate2 + "') and a.storeno <> '' "
// + " from pos_store_stock with( nolock )where orderdate >= '" + orderdate1 + "' and orderdate <= '" + orderdate2 + "' and a.storeno <> '' "
+ " group by a.storeno, a.ppmidx , orderdate "
// + " HAVING sum(amt_pre + amt_input + amt_result) <> 0 "
+ " order by orderdate, a.storeno, a.ppmidx ";
adapter.SelectCommand = new SqlCommand(queryString, myConnection);
adapter.SelectCommand.CommandTimeout = 0;
SqlCommand command = new SqlCommand(queryString, myConnection);
try
{
DataSet ds = new DataSet();
adapter.Fill(ds);
DataTable table = ds.Tables[0];
DataRowCollection rows = table.Rows;
string storeno = "";
string ppmidx = "";
string ckorderdate = "";
int amt_input = 0;
int amt_pre = 0;
int amt_result = 0;
myConnection.Dispose();
myConnection.Close();
myConnection_201 = DbConnection.GetRemoteDataInstance().getRemoteDbConnection("azure");
foreach (DataRow dr in rows)
{
storeno = dr["storeno"].ToString();
ckorderdate = String.Format("{0:yyyy-MM-dd}", Convert.ToDateTime(dr["orderdate"].ToString()));
ppmidx = dr["ppmidx"].ToString();
amt_input = Convert.ToInt32(dr["amt_input"].ToString());
amt_pre = Convert.ToInt32(dr["amt_pre"].ToString());
amt_result = Convert.ToInt32(dr["amt_result"].ToString());
queryString1 = " UPDATE scm_stock_preset SET "
+ " amt_input = '" + amt_input + "'"
+ " ,amt_pre = '" + amt_pre + "'"
+ " ,amt_soldout = '" + amt_result + "'"
+ " WHERE orderdate = '" + ckorderdate + "' and storeno = '" + storeno + "' AND ppmidx = '" + ppmidx + "'";
//Log((String.Format("[in_stock] 쿼리[{0}] : ", queryString1)));
SqlCommand command1 = new SqlCommand(queryString1, myConnection_201);
command1.CommandText = queryString1;
command1.ExecuteNonQuery();
}
frm1.sendSms("NEWPOS_SCM 입고량 생성 완료(4/4)");
frm1.Log("NEWPOS_SCM 입고량 생성 완료");
myConnection_201.Dispose();
myConnection_201.Close();
}
catch (Exception ex)
{
frm1.Log("[입고량]:" + ex.Message);
frm1.Log("입고량 쿼리:" + queryString1);
}
}
private void fn_get_stock_preset_panmae(string orderdate1, string orderdate2, Form1 frm1)
{
myConnection = DbConnection.GetRemoteDataInstance().getRemoteDbConnection("213");
string queryString1 = "";
SqlDataAdapter adapter = new SqlDataAdapter();
// frm1.sendSms("NEWPOS_SCM 판매예측 생성 시작");
frm1.Log("NEWPOS_SCM 판매예측 생성 시작");
string queryString = "select storeno, ppmidx, sdate, ( SUM(ISNULL(amt_sale,0)) ) as panmae "
+ " from webdb_se.dbo.preset_sale_data_2019 with (nolock)where sdate >= '" + orderdate1 + "' and sdate <= '" + orderdate2 + "' "
// + " from preset_sale_data with (nolock) where sdate >= '" + orderdate1 + "' and sdate <= '" + orderdate2 + "' "
+ " group by storeno, ppmidx , sdate "
+ " having SUM(ISNULL(amt_sale,0)) <> 0 "
+ " order by sdate ";
//AND CONVERT(Varchar(10),a.regdate,120) = CONVERT(Varchar(10),getdate(),120)";
adapter.SelectCommand = new SqlCommand(queryString, myConnection);
adapter.SelectCommand.CommandTimeout = 0;
SqlCommand command = new SqlCommand(queryString, myConnection);
try
{
DataSet ds = new DataSet();
adapter.Fill(ds);
DataTable table = ds.Tables[0];
DataRowCollection rows = table.Rows;
string storeno = "";
string ppmidx = "";
string cksdate = "";
int panmae = 0;
myConnection.Dispose();
myConnection.Close();
myConnection_201 = DbConnection.GetRemoteDataInstance().getRemoteDbConnection("azure");
foreach (DataRow dr in rows)
{
storeno = dr["storeno"].ToString();
cksdate = String.Format("{0:yyyy-MM-dd}", Convert.ToDateTime(dr["sdate"].ToString()));
ppmidx = dr["ppmidx"].ToString();
panmae = Convert.ToInt32(dr["panmae"].ToString());
//D-6, D-3 실행하므로 기존에 있는 물품인 경우, 설정값 Update 없는 물품인 경우 Insert
queryString1 = " UPDATE scm_stock_preset SET "
+ " amt_panmae = '" + panmae + "'"
+ " WHERE orderdate = '" + cksdate + "' and storeno = '" + storeno + "' AND ppmidx = '" + ppmidx + "'";
//Log((String.Format("[in_stock] 쿼리[{0}] : ", queryString1)));
SqlCommand command1 = new SqlCommand(queryString1, myConnection_201);
command1.CommandText = queryString1;
command1.ExecuteNonQuery();
}
frm1.sendSms("NEWPOS_SCM 판매예측 생성 완료(3/4)");
frm1.Log("NEWPOS_SCM 판매예측 생성 완료");
myConnection_201.Dispose();
myConnection_201.Close();
}
catch (Exception ex)
{
frm1.Log("[판매예측]:" + ex.Message);
frm1.Log("판매예측쿼리:" + queryString1);
}
}
private void fn_get_stock_preset_jungga(string orderdate1, string orderdate2, Form1 frm1)
{
myConnection = DbConnection.GetRemoteDataInstance().getRemoteDbConnection("213");
string queryString1 = "";
SqlDataAdapter adapter = new SqlDataAdapter();
//frm1.sendSms("NEWPOS_SCM 정가 판매 생성 시작");
frm1.Log("NEWPOS_SCM 정가 판매 생성 시작");
string queryString = "select right(a.storeno,4) as storeno, a.ppmidx, orderdate as ckorderdate,( SUM(ISNULL(qty_total,0)) - SUM(ISNULL(qty_discount,0)) ) as jungga "
+ " from pos_sale_A01_ppm_daily as a with (nolock) "
+ " inner join webdb_se.dbo.stock_preset4_2019 as b with (nolock) on a.orderdate = b.sdate and right(a.storeno,4) = b.storeno and a.ppmidx = b.ppmidx "
+ " where b.sdate >= '" + orderdate1 + "' and b.sdate <= '" + orderdate2 + "' "
+ " AND b.ppmidx IN (SELECT idx FROM pos_product_master as a with (nolock) INNER JOIN preset_base_product as b with (nolock) ON a.icoop_code = b.product_code WHERE b.vc = '1' )"
+ " group by a.storeno, a.ppmidx,orderdate "
+ " HAVING SUM(ISNULL(qty_total,0)) - SUM(ISNULL(qty_discount,0)) > 0 "
+ " order by orderdate ";
adapter.SelectCommand = new SqlCommand(queryString, myConnection);
adapter.SelectCommand.CommandTimeout = 0;
SqlCommand command = new SqlCommand(queryString, myConnection);
try
{
DataSet ds = new DataSet();
adapter.Fill(ds);
DataTable table = ds.Tables[0];
DataRowCollection rows = table.Rows;
string storeno = "";
string ppmidx = "";
string ckorderdate = "";
int jungga = 0;
myConnection.Dispose();
myConnection.Close();
myConnection_201 = DbConnection.GetRemoteDataInstance().getRemoteDbConnection("azure");
foreach (DataRow dr in rows)
{
storeno = dr["storeno"].ToString();
ckorderdate = String.Format("{0:yyyy-MM-dd}", Convert.ToDateTime(dr["ckorderdate"].ToString()));
ppmidx = dr["ppmidx"].ToString();
jungga = Convert.ToInt32(dr["jungga"].ToString());
queryString1 = " UPDATE scm_stock_preset SET "
+ " amt_jungga = '" + jungga + "'"
+ " WHERE orderdate = '" + ckorderdate + "' and storeno = '" + storeno + "' AND ppmidx = '" + ppmidx + "'";
SqlCommand command1 = new SqlCommand(queryString1, myConnection_201);
command1.CommandText = queryString1;
command1.ExecuteNonQuery();
}
frm1.sendSms("NEWPOS_SCM 정가 판매 생성 완료(2/4)");
frm1.Log("NEWPOS_SCM 정가 판매 생성 완료");
myConnection_201.Dispose();
myConnection_201.Close();
}
catch (Exception ex)
{
frm1.Log("[정가 판매]:" + ex.Message);
frm1.Log("정가 판매쿼리:" + queryString1);
}
}
private void fn_get_stock_preset(string orderdate1, string orderdate2, Form1 frm1)
{
myConnection = DbConnection.GetRemoteDataInstance().getRemoteDbConnection("213");
int totalcount = 0;
int pagecount = 0;
int i;
string YYYYMM = "";
YYYYMM = String.Format("{0:yyyyMM}", Convert.ToDateTime(orderdate1));
frm1.Log("NEWPOS_SCM 기준데이터 생성 시작");
string queryString4 = " select count(g.storeno) from ( SELECT t1.storeno as storeno, t1.sdate, t1.m_code as m_code, t1.product_code, t4.storeSubject as storeSubject, t1.ppmidx,t3.subject, t1.reg_gb,"
+ " (case when (t1.product_code <> '') THEN '물품' ELSE '그룹' END) as group_yn, isnull(gg.group_cd,'') as group_cd,isnull( gg.group_nm,'') as group_nm, t1.scm_gb, "
+ " (case when ( t1.stock_only_ab = 'A') THEN 'Y' when ( t1.stock_only_ab = 'B') THEN 'N' ELSE '' END) as stock_only_ab, "
+ " (case when ( t1.scm_susik = 'D') THEN '기본' when ( t1.scm_susik = 'B') THEN '묶음' ELSE '' END) as scm_susik"
+ " , t1.amt_bundle, t5.subject3 as pdbsubject ,t5.idx as pdbidx , t7.subject as pcgsubject ,t7.idx as pcgidx,t6.subject as pcmsubject,t6.idx as pcmidx"
+ " ,group_susik,amt_balju_pre,amt_balju,amt_sale_d3,amt_sale_d2,amt_sale_d1,amt_sale_d"
+ " ,amt_pda_d3,amt_pda_d2,amt_pda_d1,amt_safe_d,amt_safe_max,amt_magam,amt_input_d3,amt_input_d2,amt_input_d1"
+ " ,amt_adjust1,amt_adjust2,amt_adjust3,amt_adjust4,amt_adjust5,amt_adjust6,amt_adjust7,t1.vc, t3.erp_p_ab,t3.erp_netshow_ab"
+ " , (SUM(ISNULL(t8.amt_sale, 0)) ) as panmae "
+ " , SUM(ISNULL(t9.amt_result,0)) as amt_pre , SUM(ISNULL(t9.amt_input,0)) as amt_input ,(case when (sum(t9.amt_result) <= 0) THEN 1 ELSE 0 END) as amt_soldout "
+ " ,SUM(ISNULL(t10.qty_jungga, 0)) as qty_jungga "
+ " FROM webdb_se.dbo.stock_preset4_2019 as t1 with (nolock) "
+ " LEFT OUTER JOIN pos_product_master as t3 with (nolock) ON t1.ppmidx = t3.idx"
+ " LEFT OUTER JOIN pos_erp_goods_group as gg with (nolock) On t1.ppmidx = gg.product_ppmidx "
+ " LEFT OUTER JOIN pos_db_BrandClass as t5 with (nolock) On t5.idx = t3.pdbidx "
+ " LEFT OUTER JOIN pos_category_master as t6 with (nolock) On t6.idx = t3.pcmidx "
+ " LEFT OUTER JOIN pos_category_group as t7 with (nolock) on t7.idx = t6.pcgidx"
// + " LEFT OUTER JOIN webdb_se.dbo.preset_sale_data_2019 as t8 with(nolock) on convert(varchar(10),t1.ppmidx) = t8.ppmidx and t1.sdate = t8.sdate and t1.storeno = t8.storeno "
+ " LEFT OUTER JOIN preset_sale_data as t8 with(nolock) on convert(varchar(10),t1.ppmidx) = t8.ppmidx and t1.sdate = t8.sdate and t1.storeno = t8.storeno "
+ " LEFT OUTER JOIN pos_store_stock_" + YYYYMM + " as t9 with(nolock) on t9.orderdate = t1.sdate and right(t9.storeno,4) = t1.storeno and t9.ppmidx = t1.ppmidx "
+ " LEFT OUTER JOIN preset_sale_ppm_daily as t10 with(nolock) on t10.ppmidx = t1.ppmidx and t10.orderdate = t1.sdate and t10.storeno = t1.storeno "
+ " INNER JOIN pos_store as t4 ON t1.storeno=right(t4.storeno,4) and t4.vc_businessStatus='1' "
+ " WHERE (t1.sdate >= '"+ orderdate1 + "' and t1.sdate <= '" + orderdate2 + "' ) "
+ " group by t1.sdate,t1.storeno , t1.m_code , t4.storeSubject , t1.product_code, t3.subject, t1.reg_gb,t1.product_code,"
+ " t1.scm_gb, t1.stock_only_ab, t1.scm_susik, t1.amt_bundle, t5.subject3 ,t5.idx , t7.subject ,t7.idx,t6.subject,t6.idx,"
+ " gg.group_cd, gg.group_nm,t1.ppmidx,group_susik,amt_balju_pre,amt_balju,amt_sale_d3,amt_sale_d2,amt_sale_d1,amt_sale_d"
+ " ,amt_pda_d3,amt_pda_d2,amt_pda_d1,amt_safe_d,amt_safe_max,amt_magam,amt_input_d3,amt_input_d2,amt_input_d1"
+ " ,amt_adjust1,amt_adjust2,amt_adjust3,amt_adjust4,amt_adjust5,amt_adjust6,amt_adjust7,t1.vc, t3.erp_p_ab,t3.erp_netshow_ab"
+ " UNION ALL "
+ " SELECT distinct t1.storeno as storeno, t1.sdate, t1.m_code as m_code, '' as product_code, t4.storeSubject as storeSubject, null as ppmidx, '' as subject, t1.reg_gb, "
+ " (case when (''='') THEN '그룹' ELSE '물품' END) as group_yn, gg.group_cd, gg.group_nm, "
+ " (case when (group_ab = 'A') then 'Y' else 'N' END) as scm_gb,"
+ " (case when ( t1.stock_only_ab = 'A') THEN 'Y' when ( t1.stock_only_ab = 'B') THEN 'N' ELSE '' END) as stock_only_ab, "
+ " '' as scm_susik, '' as amt_bundle,t5.subject3 as pdbsubject ,t5.idx as pdbidx , t7.subject as pcgsubject ,t7.idx as pcgidx,t6.subject as pcmsubject,t6.idx as pcmidx, "
+ " '' as group_susik,amt_balju_pre,amt_balju,amt_sale_d3,amt_sale_d2,amt_sale_d1,amt_sale_d "
+ " ,amt_pda_d3,amt_pda_d2,amt_pda_d1,amt_safe_d, 0 as amt_safe_max,amt_magam,amt_input_d3,amt_input_d2,amt_input_d1 "
+ " ,amt_adjust1,amt_adjust2,0 as amt_adjust3,amt_adjust4,0 as amt_adjust5,amt_adjust6,amt_adjust7,t1.vc, t3.erp_p_ab,t3.erp_netshow_ab"
+ " , (SUM(ISNULL(t8.amt_sale, 0)) ) as panmae "
+ ", SUM(ISNULL(t9.amt_result, 0)) as amt_pre , SUM(ISNULL(t9.amt_input, 0)) as amt_input ,(case when(sum(t9.amt_result) <= 0) THEN 1 ELSE 0 END) as amt_soldout "
+ " , SUM(ISNULL(t10.qty_jungga, 0)) as qty_jungga "
+ " FROM stock_preset4_group as t1 "
+ " LEFT OUTER JOIN pos_erp_goods_group as gg On t1.group_cd = gg.group_cd "
+ " LEFT OUTER JOIN pos_product_master as t3 with (nolock) ON t3.icoop_code = gg.product_cd "
+ " LEFT OUTER JOIN pos_db_BrandClass as t5 with (nolock) On t5.idx = t3.pdbidx "
+ " LEFT OUTER JOIN pos_category_master as t6 with (nolock) On t6.idx = t3.pcmidx "
+ " LEFT OUTER JOIN pos_category_group as t7 with (nolock) on t7.idx = t6.pcgidx "
//+ " LEFT OUTER JOIN webdb_se.dbo.preset_sale_data_2019 as t8 with(nolock) on convert(varchar(10),gg.product_ppmidx) = t8.ppmidx and t1.sdate = t8.sdate and t1.storeno = t8.storeno "
+ " LEFT OUTER JOIN preset_sale_data as t8 with(nolock) on convert(varchar(10),gg.product_ppmidx) = t8.ppmidx and t1.sdate = t8.sdate and t1.storeno = t8.storeno "
+ " LEFT OUTER JOIN pos_store_stock_" + YYYYMM + " as t9 with(nolock) on t9.orderdate = t1.sdate and right(t9.storeno,4) = t1.storeno and t9.ppmidx = gg.product_ppmidx "
+ " LEFT OUTER JOIN preset_sale_ppm_daily as t10 with(nolock) on t10.ppmidx = gg.product_ppmidx and t10.orderdate = t1.sdate and t10.storeno = t1.storeno "
+ " INNER JOIN pos_store as t4 ON t1.storeno=right(t4.storeno,4) and t4.vc_businessStatus='1' "
+ " WHERE (t1.sdate >= '" + orderdate1 + "' and t1.sdate <= '" + orderdate2 + "' ) "
+ " group by t1.storeno ,t1.sdate, t1.m_code , t4.storeSubject , t3.subject, t1.reg_gb, "
+ " t1.stock_only_ab, t5.subject3 ,t5.idx , t7.subject ,t7.idx,t6.subject,t6.idx,group_ab,gg.group_cd, gg.group_nm "
+ " ,amt_balju_pre,amt_balju,amt_sale_d3,amt_sale_d2,amt_sale_d1,amt_sale_d "
+ " ,amt_pda_d3,amt_pda_d2,amt_pda_d1,amt_safe_d,amt_magam,amt_input_d3,amt_input_d2,amt_input_d1 "
+ " ,amt_adjust1,amt_adjust2,amt_adjust4,amt_adjust6,amt_adjust7,t1.vc, t3.erp_p_ab,t3.erp_netshow_ab "
+ " ) as g";
SqlCommand command4 = new SqlCommand(queryString4, myConnection);
SqlDataReader reader4 = command4.ExecuteReader();
while (reader4.Read())
{
totalcount = Convert.ToInt32(reader4[0].ToString()); //ppmidx
}
reader4.Close();
myConnection.Dispose();
myConnection.Close();
frm1.Log("NEWPOS_SCM 자료 생성 개수(" + totalcount + ")");
//frm1.Log("NEWPOS_SCM 쿼리(" + queryString4 + ")");
pagecount = totalcount / 50000;
pagecount = pagecount + 1;
for (i = 1; i <= pagecount; i++)
{
myConnection_201 = DbConnection.GetRemoteDataInstance().getRemoteDbConnection("azure");
string queryString1 = "";
string queryString3 = "";
SqlDataAdapter adapter = new SqlDataAdapter();
SqlDataAdapter adapter1 = new SqlDataAdapter();
//frm1.sendSms("NEWPOS_SCM 자료 생성 시작(" + String.Format("{0:yyyy-MM-dd HH:mm:ss}", DateTime.Now.AddDays(0).ToString()) + ")("+ i + "/"+pagecount+")");
var table_ex = new DataTable();
queryString3 = "SELECT TOP 0 * FROM scm_stock_preset ";
adapter1.SelectCommand = new SqlCommand(queryString3, myConnection_201);
SqlCommand command2 = new SqlCommand(queryString3, myConnection_201);
adapter1.Fill(table_ex);
myConnection_201.Dispose();
myConnection_201.Close();
myConnection = DbConnection.GetRemoteDataInstance().getRemoteDbConnection("213");
//step1. 바코등 등록 및 수정 대상물품 조회
string queryString = "SELECT t1.storeno as storeno, t1.sdate, t1.m_code as m_code, t1.product_code, t4.storeSubject as storeSubject, convert(varchar(10),t1.ppmidx) as ppmidx,t3.subject, t1.reg_gb,"
+ " (case when (t1.product_code <> '') THEN '물품' ELSE '그룹' END) as group_yn, isnull(gg.group_cd,'') as group_cd,isnull( gg.group_nm,'') as group_nm, (case when (t1.scm_gb = '0') THEN '' ELSE t1.scm_gb END) as scm_gb , "
+ " (case when ( t1.stock_only_ab = 'A') THEN 'Y' when ( t1.stock_only_ab = 'B') THEN 'N' ELSE '' END) as stock_only_ab, "
+ " (case when ( t1.scm_susik = 'D') THEN '기본' when ( t1.scm_susik = 'B') THEN '묶음' ELSE '' END) as scm_susik"
+ " ,isnull(t1.amt_bundle,0) as amt_bundle, t5.subject3 as pdbsubject ,t5.idx as pdbidx , t7.subject as pcgsubject ,t7.idx as pcgidx,t6.subject as pcmsubject,t6.idx as pcmidx"
+ " ,group_susik,amt_balju_pre,amt_balju,amt_sale_d3,amt_sale_d2,amt_sale_d1,amt_sale_d"
+ " ,amt_pda_d3,amt_pda_d2,amt_pda_d1,amt_safe_d,amt_safe_max,amt_magam,amt_input_d3,amt_input_d2,amt_input_d1"
+ " ,amt_adjust1,amt_adjust2,amt_adjust3,amt_adjust4,amt_adjust5,amt_adjust6,amt_adjust7,t1.vc, isnull(t3.erp_p_ab,'') as erp_p_ab, isnull(t3.erp_netshow_ab,'') as erp_netshow_ab "
+ " , (SUM(ISNULL(t8.amt_sale, 0)) ) as panmae "
+ " , SUM(ISNULL(t9.amt_result,0)) as amt_pre , SUM(ISNULL(t9.amt_input,0)) as amt_input ,(case when (sum(t9.amt_result) <= 0) THEN 1 ELSE 0 END) as amt_soldout "
+ " ,SUM(ISNULL(t10.qty_jungga, 0)) as qty_jungga "
+ " FROM webdb_se.dbo.stock_preset4_2019 as t1 with (nolock) "
+ " LEFT OUTER JOIN pos_product_master as t3 with (nolock) ON t1.ppmidx = t3.idx "
+ " LEFT OUTER JOIN pos_erp_goods_group as gg with (nolock) On t1.ppmidx = gg.product_ppmidx "
+ " LEFT OUTER JOIN pos_db_BrandClass as t5 with (nolock) On t5.idx = t3.pdbidx "
+ " LEFT OUTER JOIN pos_category_master as t6 with (nolock) On t6.idx = t3.pcmidx "
+ " LEFT OUTER JOIN pos_category_group as t7 with (nolock) on t7.idx = t6.pcgidx"
+ " LEFT OUTER JOIN preset_sale_data as t8 with(nolock) on convert(varchar(10),t1.ppmidx) = t8.ppmidx and t1.sdate = t8.sdate and t1.storeno = t8.storeno "
// + " LEFT OUTER JOIN webdb_se.dbo.preset_sale_data_2019 as t8 with(nolock) on convert(varchar(10),t1.ppmidx) = t8.ppmidx and t1.sdate = t8.sdate and t1.storeno = t8.storeno "
+ " LEFT OUTER JOIN pos_store_stock_" + YYYYMM + " as t9 with(nolock) on t9.orderdate = t1.sdate and right(t9.storeno,4) = t1.storeno and t9.ppmidx = t1.ppmidx "
+ " LEFT OUTER JOIN preset_sale_ppm_daily as t10 with(nolock) on t10.ppmidx = t1.ppmidx and t10.orderdate = t1.sdate and t10.storeno = t1.storeno "
+ " INNER JOIN pos_store as t4 ON t1.storeno=right(t4.storeno,4) and t4.vc_businessStatus='1' "
+ " WHERE (t1.sdate >= '"+ orderdate1 + "' and t1.sdate <= '" + orderdate2 + "' ) "
+ " group by t1.sdate,t1.storeno , t1.m_code , t4.storeSubject , t1.product_code, t3.subject, t1.reg_gb,t1.product_code,"
+ " t1.scm_gb, t1.stock_only_ab, t1.scm_susik, t1.amt_bundle, t5.subject3 ,t5.idx , t7.subject ,t7.idx,t6.subject,t6.idx,"
+ " gg.group_cd, gg.group_nm,t1.ppmidx,group_susik,amt_balju_pre,amt_balju,amt_sale_d3,amt_sale_d2,amt_sale_d1,amt_sale_d"
+ ", amt_pda_d3,amt_pda_d2,amt_pda_d1,amt_safe_d,amt_safe_max,amt_magam,amt_input_d3,amt_input_d2,amt_input_d1"
+ " ,amt_adjust1,amt_adjust2,amt_adjust3,amt_adjust4,amt_adjust5,amt_adjust6,amt_adjust7,t1.vc,t3.erp_p_ab,t3.erp_netshow_ab"
+ " UNION ALL "
+ " SELECT distinct t1.storeno as storeno, t1.sdate, t1.m_code as m_code, '' as product_code, t4.storeSubject as storeSubject, '' as ppmidx, '' as subject, t1.reg_gb, "
+ " (case when (''='') THEN '그룹' ELSE '물품' END) as group_yn, gg.group_cd, gg.group_nm, "
+ " (case when (group_ab = 'A') then 'Y' else 'N' END) as scm_gb, "
+ " (case when ( t1.stock_only_ab = 'A') THEN 'Y' when ( t1.stock_only_ab = 'B') THEN 'N' ELSE '' END) as stock_only_ab, "
+ " '' as scm_susik, '' as amt_bundle,t5.subject3 as pdbsubject ,t5.idx as pdbidx , t7.subject as pcgsubject ,t7.idx as pcgidx,t6.subject as pcmsubject,t6.idx as pcmidx, "
+ " '' as group_susik,amt_balju_pre,amt_balju,amt_sale_d3,amt_sale_d2,amt_sale_d1,amt_sale_d "
+ " ,amt_pda_d3,amt_pda_d2,amt_pda_d1,amt_safe_d, 0 as amt_safe_max,amt_magam,amt_input_d3,amt_input_d2,amt_input_d1 "
+ " ,amt_adjust1,amt_adjust2,0 as amt_adjust3,amt_adjust4,0 as amt_adjust5,amt_adjust6,amt_adjust7,t1.vc, isnull(t3.erp_p_ab,'') as erp_p_ab, isnull(t3.erp_netshow_ab,'') as erp_netshow_ab "
+ " , (SUM(ISNULL(t8.amt_sale, 0)) ) as panmae "
+ ", SUM(ISNULL(t9.amt_result, 0)) as amt_pre , SUM(ISNULL(t9.amt_input, 0)) as amt_input ,(case when(sum(t9.amt_result) <= 0) THEN 1 ELSE 0 END) as amt_soldout "
+ " , SUM(ISNULL(t10.qty_jungga, 0)) as qty_jungga "
+ " FROM stock_preset4_group as t1 "
+ "LEFT OUTER JOIN pos_erp_goods_group as gg On t1.group_cd = gg.group_cd "
+ " LEFT OUTER JOIN pos_product_master as t3 with (nolock) ON t3.icoop_code = gg.product_cd "
+ " LEFT OUTER JOIN pos_db_BrandClass as t5 with (nolock) On t5.idx = t3.pdbidx "
+ " LEFT OUTER JOIN pos_category_master as t6 with (nolock) On t6.idx = t3.pcmidx "
+ " LEFT OUTER JOIN pos_category_group as t7 with (nolock) on t7.idx = t6.pcgidx "
//+ " LEFT OUTER JOIN webdb_se.dbo.preset_sale_data_2019 as t8 with(nolock) on convert(varchar(10),gg.product_ppmidx) = t8.ppmidx and t1.sdate = t8.sdate and t1.storeno = t8.storeno "
+ " LEFT OUTER JOIN preset_sale_data as t8 with(nolock) on convert(varchar(10),gg.product_ppmidx) = t8.ppmidx and t1.sdate = t8.sdate and t1.storeno = t8.storeno "
+ " LEFT OUTER JOIN pos_store_stock_" + YYYYMM + " as t9 with(nolock) on t9.orderdate = t1.sdate and right(t9.storeno,4) = t1.storeno and t9.ppmidx = gg.product_ppmidx "
+ " LEFT OUTER JOIN preset_sale_ppm_daily as t10 with(nolock) on t10.ppmidx = gg.product_ppmidx and t10.orderdate = t1.sdate and t10.storeno = t1.storeno "
+ " INNER JOIN pos_store as t4 ON t1.storeno=right(t4.storeno,4) and t4.vc_businessStatus='1' "
+ "WHERE (t1.sdate >= '" + orderdate1 + "' and t1.sdate <= '" + orderdate2 + "' ) "
+ " group by t1.storeno ,t1.sdate, t1.m_code , t4.storeSubject , t3.subject, t1.reg_gb, "
+ " t1.stock_only_ab, t5.subject3 ,t5.idx , t7.subject ,t7.idx,t6.subject,t6.idx,group_ab,gg.group_cd, gg.group_nm "
+ " ,amt_balju_pre,amt_balju,amt_sale_d3,amt_sale_d2,amt_sale_d1,amt_sale_d "
+ " ,amt_pda_d3,amt_pda_d2,amt_pda_d1,amt_safe_d,amt_magam,amt_input_d3,amt_input_d2,amt_input_d1 "
+ ",amt_adjust1,amt_adjust2,amt_adjust4,amt_adjust6,amt_adjust7,t1.vc,t3.erp_p_ab,t3.erp_netshow_ab "
+ " ORDER BY t1.sdate,t1.storeno, group_cd desc, product_code"
+ " offset(" + i + " - 1) * 50000 rows"
+ " Fetch next 50000 rows only";
adapter.SelectCommand = new SqlCommand(queryString, myConnection);
adapter.SelectCommand.CommandTimeout = 0;
SqlCommand command = new SqlCommand(queryString, myConnection);
try
{
DataSet ds = new DataSet();
adapter.Fill(ds);
DataTable table = ds.Tables[0];
DataRowCollection rows = table.Rows;
myConnection.Dispose();
myConnection.Close();
myConnection_201 = DbConnection.GetRemoteDataInstance().getRemoteDbConnection("azure");
// int i = 0;
foreach (DataRow dr in rows)
{
var row_ex = table_ex.NewRow();
row_ex["orderdate"] = String.Format("{0:yyyy-MM-dd}", Convert.ToDateTime(dr["sdate"].ToString()));
row_ex["storeno"] = dr["storeno"].ToString();
row_ex["m_code"] = dr["m_code"].ToString();
row_ex["ppmidx"] = dr["ppmidx"].ToString();
row_ex["product_code"] = dr["product_code"].ToString();
row_ex["storeSubject"] = dr["storeSubject"].ToString();
row_ex["subject"] = dr["subject"].ToString();
row_ex["reg_gb"] = dr["reg_gb"].ToString();
row_ex["group_yn"] = dr["group_yn"].ToString();
row_ex["group_cd"] = dr["group_cd"].ToString();
row_ex["group_nm"] = dr["group_nm"].ToString();
row_ex["scm_gb"] = dr["scm_gb"].ToString();
row_ex["stock_only_ab"] = dr["stock_only_ab"].ToString();
row_ex["scm_susik"] = dr["scm_susik"].ToString();
row_ex["amt_bundle"] = Convert.ToInt32(dr["amt_bundle"].ToString());
row_ex["pdbsubject"] = dr["pdbsubject"].ToString();
row_ex["pdbidx"] = Convert.ToInt32(dr["pdbidx"].ToString());
row_ex["pcgsubject"] = dr["pcgsubject"].ToString();
row_ex["pcgidx"] = Convert.ToInt32(dr["pcgidx"].ToString());
row_ex["pcmsubject"] = dr["pcmsubject"].ToString();
row_ex["pcmidx"] = Convert.ToInt32(dr["pcmidx"].ToString());
row_ex["group_susik"] = dr["group_susik"].ToString();
row_ex["amt_balju_pre"] = Convert.ToInt32(dr["amt_balju_pre"].ToString());
row_ex["amt_balju"] = Convert.ToInt32(dr["amt_balju"].ToString());
row_ex["amt_sale_d3"] = Convert.ToInt32(dr["amt_sale_d3"].ToString());
row_ex["amt_sale_d2"] = Convert.ToInt32(dr["amt_sale_d2"].ToString());
row_ex["amt_sale_d1"] = Convert.ToInt32(dr["amt_sale_d1"].ToString());
row_ex["amt_sale_d"] = Convert.ToInt32(dr["amt_sale_d"].ToString());
row_ex["amt_pda_d3"] = Convert.ToInt32(dr["amt_pda_d3"].ToString());
row_ex["amt_pda_d2"] = Convert.ToInt32(dr["amt_pda_d2"].ToString());
row_ex["amt_pda_d1"] = Convert.ToInt32(dr["amt_pda_d1"].ToString());
row_ex["amt_safe_d"] = Convert.ToInt32(dr["amt_safe_d"].ToString());
row_ex["amt_safe_max"] = Convert.ToInt32(dr["amt_safe_max"].ToString());
row_ex["amt_magam"] = Convert.ToInt32(dr["amt_magam"].ToString());
row_ex["amt_input_d3"] = Convert.ToInt32(dr["amt_input_d3"].ToString());
row_ex["amt_input_d2"] = Convert.ToInt32(dr["amt_input_d2"].ToString());
row_ex["amt_input_d1"] = Convert.ToInt32(dr["amt_input_d1"].ToString());
row_ex["amt_adjust1"] = Convert.ToInt32(dr["amt_adjust1"].ToString());
row_ex["amt_adjust2"] = Convert.ToInt32(dr["amt_adjust2"].ToString());
row_ex["amt_adjust3"] = Convert.ToInt32(dr["amt_adjust3"].ToString());
row_ex["amt_adjust4"] = Convert.ToInt32(dr["amt_adjust4"].ToString());
row_ex["amt_adjust5"] = Convert.ToInt32(dr["amt_adjust5"].ToString());
row_ex["amt_adjust6"] = Convert.ToInt32(dr["amt_adjust6"].ToString());
row_ex["amt_adjust7"] = Convert.ToInt32(dr["amt_adjust7"].ToString());
row_ex["vc"] = Convert.ToInt32(dr["vc"].ToString());
row_ex["erp_p_ab"] = dr["erp_p_ab"].ToString();
row_ex["erp_netshow_ab"] = dr["erp_netshow_ab"].ToString();
row_ex["amt_panmae"] = Convert.ToInt32(dr["panmae"].ToString());
row_ex["amt_pre"] = Convert.ToInt32(dr["amt_pre"].ToString());
row_ex["amt_input"] = Convert.ToInt32(dr["amt_input"].ToString());
row_ex["amt_soldout"] = Convert.ToInt32(dr["amt_soldout"].ToString());
row_ex["amt_jungga"] = Convert.ToInt32(dr["qty_jungga"].ToString());
table_ex.Rows.Add(row_ex);
}
using (var bulk = new SqlBulkCopy(myConnection_201))
{
bulk.DestinationTableName = "scm_stock_preset";
bulk.BulkCopyTimeout = 0;
bulk.WriteToServer(table_ex);
}
// frm1.sendSms("NEWPOS_SCM 자료 생성 완료(" + String.Format("{0:yyyy-MM-dd HH:mm:ss}", DateTime.Now.AddDays(0).ToString()) + ")(" + i + "/" + pagecount + ")");
frm1.Log("NEWPOS_SCM 자료 생성 완료(" + i + "/" + pagecount + ")");
//frm1.Log("NEWPOS_SCM 자료 생성 완료(" + queryString + "");
myConnection_201.Dispose();
myConnection_201.Close();
}
catch (Exception ex)
{
frm1.Log("[자료 생성)]:" + ex.Message);
frm1.Log("자료 생성 쿼리:" + queryString1);
}
}
if (pagecount + 1 != i)
{
frm1.sendSms("NEWPOS_SCM 기준데이터 오류(" + String.Format("{0:yyyy-MM-dd HH:mm:ss}", DateTime.Now.AddDays(0).ToString()) + ") 오류 (1/4)");
frm1.Log("날짜" + orderdate1 + "~" + orderdate2 + "");
}
else
{
frm1.sendSms("NEWPOS_SCM 기준데이터 완료(" + String.Format("{0:yyyy-MM-dd HH:mm:ss}", DateTime.Now.AddDays(0).ToString()) + ") 완료");
frm1.Log("NEWPOS_SCM 자료 생성 완료(" + i + "/" + pagecount + ")");
frm1.Log("날짜" + orderdate1 + "~" + orderdate2 + "");
}
}
private void fn_stock_auto_input(string orderdate, string type, string ip, Form1 frm1)
{
frm1.Log((String.Format("NEWPOS 물품 자동입고 시작")));
string storeNo = "";
string storeNo_chack = "8000006999";
String queryString1 = "";
String queryString4 = "";
String queryString5 = "";
myConnection = DbConnection.GetRemoteDataInstance().getRemoteDbConnection("213");
SqlDataAdapter adapter = new SqlDataAdapter();
string queryString = "select a.idx, a.ppmidx, a.storeNo, a.order_date, a.order_icoopCode, a.order_price, a.order_qty, a.order_qty_reconfirm, a.order_sort1, a.order_sort2, a.storeCheck_qty, a.storeCheck_vc,b.vc_packingBox "
+ " from pos_store_icoopOrder as a with (nolock) left outer join pos_product_master as b with (nolock) on a.ppmidx = b.idx "
+ " left outer join pos_store as c with (nolock) on a.storeno = c.storeno "
+ " where a.order_date = '" + orderdate + "' and a.storeCheck_vc ='0' and a.ppmidx <> '' and c.vc_autoinput = '1' order by a.storeNo";
adapter.SelectCommand = new SqlCommand(queryString, myConnection);
SqlCommand command = new SqlCommand(queryString, myConnection);
try
{
DataSet ds = new DataSet();
adapter.Fill(ds);
DataTable table = ds.Tables[0];
DataRowCollection rows = table.Rows;
//string ppmidx = "";
int ppmidx_box_ea = 0;
int amtDiff = 0;
int countsum = 0;
int vc_countsum = 0;
int count_storeNo = 0;
foreach (DataRow dr in rows)
{
int vc_display = 0;
String subject = "";
String pcmidx = "";
string tmp_ppmidx = dr[1].ToString();
storeNo = dr[2].ToString();
String tmp_icoopcode = dr[4].ToString();
String vc_packingBox = dr[12].ToString();
amtDiff = Convert.ToInt32(dr[6].ToString());
if (tmp_ppmidx != "")
{
if (vc_packingBox == "1")
{
queryString4 = " select ppmidx_single, ppmidx_box_ea "
+ " from pos_product_master_box "
+ " where ppmidx_box = '" + tmp_ppmidx + "' and vc ='1' ";
SqlCommand command1 = new SqlCommand(queryString4, myConnection);
SqlDataReader reader1 = command1.ExecuteReader();
while (reader1.Read())
{
tmp_ppmidx = reader1["ppmidx_single"].ToString();
ppmidx_box_ea = Convert.ToInt32(reader1["ppmidx_box_ea"].ToString());
amtDiff = amtDiff * ppmidx_box_ea;
}
reader1.Close();
}
queryString5 = " select vc_display,subject,pcmidx "
+ " from pos_product_master with (nolock) "
+ " where idx = '" + tmp_ppmidx + "' ";
SqlCommand command2 = new SqlCommand(queryString5, myConnection);
SqlDataReader reader2 = command2.ExecuteReader();
while (reader2.Read())
{
subject = reader2["subject"].ToString();
vc_display = Convert.ToInt32(reader2["vc_display"].ToString());
pcmidx = reader2["pcmidx"].ToString();
}
reader2.Close();
if (amtDiff != 0)
{
if (type == "A") //자동 입고를 진행하였을 경우
{
queryString1 = " IF NOT EXISTS (SELECT ppmidx from pos_store_stock where storeno = '" + storeNo + "' and ppmidx = '" + tmp_ppmidx + "') "
+ " BEGIN "
+ " insert into pos_store_stock (storeNo, orderDate, ppmidx, amt_input, rdate_input, sdate_input, finalUpdate, adminMemidx) values "
+ " ( '" + storeNo + "' , '" + orderdate + "' , '" + tmp_ppmidx + "', '" + amtDiff + "', convert(varchar(10),getdate(),120) , convert(varchar(10),getdate(),120), GETDATE(), 'DEMON1') ;"
+ " END "
+ " ELSE IF NOT EXISTS (SELECT ppmidx from pos_store_stock where storeno = '" + storeNo + "' and ppmidx = '" + tmp_ppmidx + "' and sdate_input is null) "
+ " BEGIN "
+ " UPDATE pos_store_stock SET "
+ " amt_input = amt_input +'" + amtDiff + "'"
+ " , rdate_input ='" + string.Format(DateTime.Now.ToString("yyyy-MM-dd")) + "'"
+ " , vc_stock = '1'"
+ " , finalUpdate = GETDATE()"
+ " ,adminMemidx = 'DEMON1'"
+ " WHERE storeno = '" + storeNo + "' AND ppmidx = '" + tmp_ppmidx + "'"
+ " END "
+ " ELSE "
+ " BEGIN "
+ " UPDATE pos_store_stock SET "
+ " amt_input = amt_input +'" + amtDiff + "'"
+ " , rdate_input ='" + string.Format(DateTime.Now.ToString("yyyy-MM-dd")) + "'"
+ " , sdate_input = '" + string.Format(DateTime.Now.ToString("yyyy-MM-dd")) + "'" //최초입고일
+ " , vc_stock = '1'"
+ " , finalUpdate = GETDATE()"
+ " ,adminMemidx = 'DEMON1'"
+ " WHERE storeno = '" + storeNo + "' AND ppmidx = '" + tmp_ppmidx + "'"
+ " END ";
//Log((String.Format("[in_stock] 쿼리[{0}] : ", queryString1)));
command.CommandText = queryString1;
command.ExecuteNonQuery();
//최초입고일 메인 테이블 추가(2019.09.09)
string queryString2 = " IF NOT EXISTS (SELECT ppmidx from pos_product_date where storeno = '" + storeNo.Substring(6, 4) + "' and ppmidx = '" + tmp_ppmidx + "') "
+ " BEGIN "
+ " insert into pos_product_date (storeNo, ppmidx,sdate_input) values "
+ " ( '" + storeNo.Substring(6, 4) + "' ,'" + tmp_ppmidx + "',getdate()) "
+ " END ";
command.CommandText = queryString2;
command.ExecuteNonQuery();
if (vc_display == 0 && (pcmidx != "77" && pcmidx != "83"))
{
queryString1 = "exec usp_pos_product_update_display '" + tmp_ppmidx + "', '" + tmp_icoopcode + "', '" + subject + "' , '1', 'DEMON1', 'System', 'D';";
command.CommandText = queryString1;
command.ExecuteNonQuery();
vc_countsum = vc_countsum + 1;
frm1.Log((String.Format("[vc_input] 쿼리[{0}] : ", queryString1)));
}
}
else
{
if (System.DateTime.Now.ToString("yyyy-MM-dd").CompareTo(orderdate) >= 0 && amtDiff != 0) //현재날짜가 입고일보다 작거나 같을 경우(선입고 할 경우는 안됨)
{
queryString1 = " IF NOT EXISTS (SELECT ppmidx from pos_store_stock where storeno = '" + storeNo + "' and ppmidx = '" + tmp_ppmidx + "') "
+ " BEGIN "
+ " insert into pos_store_stock (storeNo, orderDate, ppmidx, amt_input, rdate_input, sdate_input, finalUpdate, adminMemidx) values "
+ " ( '" + storeNo + "' , '" + orderdate + "' , '" + tmp_ppmidx + "', '" + amtDiff + "', convert(varchar(10),getdate(),120) , convert(varchar(10),getdate(),120), GETDATE(), 'DEMON1') ;"
+ " END "
+ " ELSE IF NOT EXISTS (SELECT ppmidx from pos_store_stock where storeno = '" + storeNo + "' and ppmidx = '" + tmp_ppmidx + "' and sdate_input is null) "
+ " BEGIN "
+ " UPDATE pos_store_stock SET "
+ " amt_input = amt_input +'" + amtDiff + "'"
+ " , rdate_input ='" + string.Format(DateTime.Now.ToString("yyyy-MM-dd")) + "'"
+ " , vc_stock = '1'"
+ " , finalUpdate = GETDATE()"
+ " ,adminMemidx = 'DEMON1'"
+ " WHERE storeno = '" + storeNo + "' AND ppmidx = '" + tmp_ppmidx + "'"
+ " END "
+ " ELSE "
+ " BEGIN "
+ " UPDATE pos_store_stock SET "
+ " amt_input = amt_input +'" + amtDiff + "'"
+ " , rdate_input ='" + string.Format(DateTime.Now.ToString("yyyy-MM-dd")) + "'"
+ " , sdate_input = '" + string.Format(DateTime.Now.ToString("yyyy-MM-dd")) + "'" //최초입고일
+ " , vc_stock = '1'"
+ " , finalUpdate = GETDATE()"
+ " ,adminMemidx = 'DEMON1'"
+ " WHERE storeno = '" + storeNo + "' AND ppmidx = '" + tmp_ppmidx + "'"
+ " END ";
command.CommandText = queryString1;
command.ExecuteNonQuery();
string queryString2 = " IF NOT EXISTS (SELECT ppmidx from pos_product_date where storeno = '" + storeNo.Substring(6, 4) + "' and ppmidx = '" + tmp_ppmidx + "') "
+ " BEGIN "
+ " insert into pos_product_date (storeNo, ppmidx,sdate_input) values "
+ " ( '" + storeNo.Substring(6, 4) + "' ,'" + tmp_ppmidx + "',getdate()) "
+ " END ";
command.CommandText = queryString2;
command.ExecuteNonQuery();
if (vc_display == 0 && (pcmidx != "77" && pcmidx != "83"))
{
queryString1 = "exec usp_pos_product_update_display '" + tmp_ppmidx + "', '" + tmp_icoopcode + "', '" + subject + "' , '1', 'DEMON1', 'System', 'D';";
command.CommandText = queryString1;
command.ExecuteNonQuery();
frm1.Log((String.Format("[vc_input] 쿼리[{0}] : ", queryString1)));
vc_countsum = vc_countsum + 1;
}
}
if ((string.Format(DateTime.Now.ToString("yyyy-MM-dd")) != orderdate) && amtDiff != 0)
{
queryString1 = "insert into pos_store_icoopOrder_other (storeNo, order_date, order_date_real, order_icoopCode, order_price,vc_insertType,ppmidx,storeCheck_qty,storeCheck_update) values "
+ "('" + storeNo + "'"
+ ",'" + orderdate + "'"
+ ",'" + string.Format(DateTime.Now.ToString("yyyy-MM-dd")) + "'"
+ ",'" + tmp_icoopcode + "'"
+ ",'0'"
+ ",'0'"
+ ",'" + tmp_ppmidx + "'"
+ ",'" + amtDiff + "'"
+ ",GETDATE()"
+ ");";
// Log((String.Format("[in_other] 쿼리[{0}] : ", queryString1)));
command.CommandText = queryString1;
command.ExecuteNonQuery();
}
}
queryString1 = "insert into pos_store_icoopOrder_log (storeno,orderdate,ppmidx,qty,regdate,adminName,userip) values "
+ "('" + storeNo + "'"
+ ",'" + orderdate + "'"
+ ",'" + tmp_ppmidx + "'"
+ ",'" + amtDiff + "'"
+ ",GETDATE()"
+ ",'DEMON1'"
+ ",'" + ip + "'"
+ ");";
//Log((String.Format("[in_other_log] 쿼리[{0}] : ", queryString1)));
command.CommandText = queryString1;
command.ExecuteNonQuery();
queryString1 = "update pos_store_icoopOrder set storeCheck_qty = '" + dr[6].ToString() + "', storeCheck_vc = '1' , storeCheck_update = getdate() where storeno = '" + storeNo + "' and order_date = '" + orderdate + "' and idx = '" + dr[0].ToString() + "';";
//Log((String.Format("[up_other] 쿼리[{0}] : ", queryString1)));
command.CommandText = queryString1;
command.ExecuteNonQuery();
countsum = countsum + 1;
if (storeNo != storeNo_chack) //
{
queryString1 = "update pos_store_icoopOrder_status set total_count_storeCheck = (select count(storecheck_qty) as total_count_storeCheck from pos_store_icoopOrder where storeno = '" + storeNo_chack + "' and order_date ='" + orderdate + "' and storeCheck_vc = '1') "
+ " where storeno ='" + storeNo_chack + "' and order_date ='" + orderdate + "' ";
//Log((String.Format("[up_other_status] 쿼리[{0}] : ", queryString1)));
command.CommandText = queryString1;
command.ExecuteNonQuery();
if (storeNo_chack != "8000006999")
{
count_storeNo = count_storeNo + 1;
}
}
storeNo_chack = storeNo;
}
//frm1.Log((String.Format("[AUTO stock] - ppmidx=[{0}] / storeno=[{1}] / orderdate =[{2}]", tmp_ppmidx, storeNo, orderdate)));
}
}
queryString1 = "update pos_store_icoopOrder_status set total_count_storeCheck = (select count(storecheck_qty) as total_count_storeCheck from pos_store_icoopOrder where storeno = '" + storeNo + "' and order_date ='" + orderdate + "' and storeCheck_vc = '1') "
+ " where storeno ='" + storeNo + "' and order_date ='" + orderdate + "' ";
command.CommandText = queryString1;
command.ExecuteNonQuery();
count_storeNo = count_storeNo + 1;
frm1.sendSms("자동입고 완료 \n[" + orderdate + "] [" + count_storeNo + "]매장,\n [" + countsum + "]건, [" + vc_countsum + "] 보임 ");
frm1.Log((String.Format("NEWPOS 물품 자동입고 종료")));
}
catch (SqlException ex)
{
StringBuilder errorMessages = new StringBuilder();
for (int i = 0; i < ex.Errors.Count; i++)
{
errorMessages.Append("Index #" + i + " || " +
"Message: " + ex.Errors[i].Message + " || " +
"LineNumber: " + ex.Errors[i].LineNumber + " || " +
"Source: " + ex.Errors[i].Source + " || " +
"Procedure: " + ex.Errors[i].Procedure + ";");
//쿼리정보도
}
frm1.Log(errorMessages.ToString());
frm1.Log((String.Format("[에러] 쿼리[{0}] : ", queryString1)));
frm1.sendSms("[" + orderdate + "] 자동입고 에러");
}
myConnection.Dispose();
myConnection.Close();
}
private void fn_unpopular(string orderMonth, Form1 frm1)
{
myConnection = DbConnection.GetRemoteDataInstance().getRemoteDbConnection("213");
try
{
if (orderMonth == "")
{
frm1.Log(Convert.ToDateTime(DateTime.Today.ToString()) + "[비인기품목생성에러]생성년/월 확인");
}
else
{
//orderMonth = "201710";
//전월 1일
string sdate = orderMonth.Substring(0, 4) + "-" + orderMonth.Substring(4, 2) + "-01";
sdate = String.Format("{0:yyyy-MM-dd}", Convert.ToDateTime((sdate)).AddMonths(-1));
//전월 마지막 일자
string edate = orderMonth.Substring(0, 4) + "-" + orderMonth.Substring(4, 2) + "-01";
edate = String.Format("{0:yyyy-MM-dd}", Convert.ToDateTime((edate)).AddDays(-1));
//전월 마지막 일자 - 2개월
string edate_2m = String.Format("{0:yyyy-MM-dd}", Convert.ToDateTime((edate)).AddMonths(-2));
//전월 마지막 일자 - 3개월
string edate_3m = String.Format("{0:yyyy-MM-dd}", Convert.ToDateTime((edate)).AddMonths(-3));
//비인기품목 리스트
string queryString = "INSERT INTO store_product_unpopular (ordermonth, storeno, ppmidx, amt_sale, bad_stock_gb, rdate_sale, regdate) "
+ " SELECT '" + orderMonth + "' as orderMonth, right(a.storeNo,4) as storeno, a.ppmidx, "
+ " ISNULL(b.qty_total,0),"
+ " CASE WHEN c.rdate_sale <= '" + edate_3m + "' OR c.rdate_sale is null THEN 'Y' ELSE 'N' END as bad_stock_gb, "
+ " ISNULL(c.rdate_sale, ''), getdate()"
+ " FROM (SELECT ppmidx, storeno FROM pos_store_stock_reality with (nolock)"
+ " WHERE orderdate >= '" + sdate + "' AND orderDate <= '" + edate + "'"
+ " AND ppmidx in (SELECT idx FROM pos_product_master WHERE regdate < '" + edate_2m + "' and pcmidx not in (111, 24, 110, 26, 113, 112, 25)) "
+ " GROUP BY storeno, ppmidx"
+ " ) as a"
+ " LEFT OUTER JOIN (SELECT storeno, ppmidx, SUM(qty_total-qty_g08-qty_g13) as qty_total "
+ " FROM pos_sale_A01_ppm_daily with (nolock)"
+ " WHERE orderdate >= '" + sdate + "' AND orderdate <= '" + edate + "'"
+ " AND ppmidx in (SELECT idx FROM pos_product_master WHERE regdate < '" + edate_2m + "' and pcmidx not in (111, 24, 110, 26, 113, 112, 25)) "
+ " GROUP BY storeno, ppmidx"
+ " ) as b on a.storeno = b.storeno and a.ppmidx = b.ppmidx"
+ " LEFT OUTER JOIN pos_store_stock as c with (nolock) ON a.storeno = c.storeno and a.ppmidx = c.ppmidx"
+ " WHERE ISNULL(b.qty_total,0) <= 30"
+ " ORDER BY a.storeno, a.ppmidx";
SqlCommand command = new SqlCommand(queryString, myConnection);
command.CommandTimeout = 120;
command.CommandText = queryString;
command.ExecuteNonQuery();
frm1.Log("[비인기품목생성에러]\n" + queryString);
}
myConnection.Dispose();
myConnection.Close();
frm1.sendSms("매월 24일 비인기품목생성 완료");
}
catch (Exception e)
{
frm1.sendSms("[비인기품목생성에러]\n" + e.Message);
frm1.Log(Convert.ToDateTime(DateTime.Today.ToString()) + "[비인기품목생성에러]\n" + e.Message);
}
}
private void fn_get_safe_sale_total(string safe_sale_sdate, string safe_sale_edate, string safe_junnga, string stock_YYYYMM, string type, int totlaDay, Form1 frm1)
{
myConnection = DbConnection.GetRemoteDataInstance().getRemoteDbConnection("213");
SqlCommand command = new SqlCommand();
String sql = null;
String safe_sale_table = null;
try
{
//이전 생성 자료 로그 저장
sql = "IF EXISTS (SELECT top 1 * FROM preset_scm_safe_sale_date ) "
+ " BEGIN "
+ " INSERT INTO preset_scm_safe_sale_date_back ( storeno, ppmidx, qty_jungga, qty_safe, regdate ) SELECT storeno, ppmidx, qty_jungga, qty_safe ,getdate() FROM preset_scm_safe_sale_date "
+ " DELETE preset_scm_safe_sale_date "
+ " END";
command.Connection = myConnection;
command.CommandText = sql;
command.CommandTimeout = 120;
command.ExecuteNonQuery();
}
catch (Exception ex)
{
frm1.Log("[매월 안전재고 정가판매 백업]:" + ex.Message);
frm1.Log("매월 안전재고 정가판매 백업쿼리:" + sql);
frm1.sendSms("매월 1일 안전재고, 정가판매 백업 오류 ");
}
safe_sale_table = "pos_store_stock_" + stock_YYYYMM + " ";
try
{
/*
* 한달간 재고량합/월별일수로 기준데이터 생성
* 일주일간 안전재고 수량이 0<=3 적으며
* 한달간 정가판매수량이 0<=7작은 내역 중
* 두개의 조건이 포함(AND)되는 것들만 저장
*/
if (type == "A")
{
sql = " INSERT INTO preset_scm_safe_sale_date (storeno, ppmidx, qty_jungga, qty_safe) "
+ " select right(a.storeno,4) as storeno, a.ppmidx,isnull(c.jungga,0) as jungga, isnull(b.safe,0) as safe "
+ " from "
+ "( "
+ " select storeno, ppmidx "
+ " from pos_store_stock with(nolock) where storeno <> '' and ppmidx <> 0 "
+ " group by storeno, ppmidx "
+ " having round(sum(amt_result)/convert(float," + totlaDay + "),1) > 0 "
+ " UNION ALL "
+ " select storeno, ppmidx "
+ " from " + safe_sale_table + " with(nolock) where storeno <> '' and ppmidx <> 0 "
+ " group by storeno, ppmidx "
+ " having round(sum(amt_result)/convert(float," + totlaDay + "),1) > 0 "
+ ") as a "
+ "left join "
+ "( "
+ " select storeno, ppmidx, (sum(amt_safe_fri+amt_safe_mon+amt_safe_sat+amt_safe_sun+amt_safe_thu+amt_safe_tue+amt_safe_wed+amt_safe_wed) /7 ) as safe "
+ " from preset_safe_stock with(nolock) "
+ " group by storeno, ppmidx "
// + " having sum(amt_safe_fri+amt_safe_mon+amt_safe_sat+amt_safe_sun+amt_safe_thu+amt_safe_tue+amt_safe_wed+amt_safe_wed) /7 <= 3 "
+ " ) as b on right(a.storeno,4) = b.storeno and convert(varchar,a.ppmidx) = b.ppmidx "
+ " left join ( "
+ " select storeno, ppmidx, sum(qty_jungga) as jungga "
+ " from preset_sale_ppm_daily with(nolock) "
+ " where orderdate >='" + safe_sale_sdate + "' and orderdate <='" + safe_sale_edate + "' "
+ " group by storeno, ppmidx "
// + " having sum(qty_jungga) <= " + safe_junnga + " "
+ " ) as c on right(a.storeno,4) = c.storeno and convert(varchar,a.ppmidx) = c.ppmidx "
+ " where a.storeno is not null and b.safe <> 0 "
+ " order by storeno, ppmidx ";
}
else
{
sql = " INSERT INTO preset_scm_safe_sale_date (storeno, ppmidx, qty_jungga, qty_safe) "
+ " select right(a.storeno,4) as storeno, a.ppmidx,isnull(c.jungga,0) as jungga, isnull(b.safe,0) as safe "
+ " from "
+ "( "
+ " select storeno, ppmidx "
+ " from " + safe_sale_table + " with(nolock) where storeno <> '' and ppmidx <> 0 "
+ " group by storeno, ppmidx "
+ " having round(sum(amt_result)/convert(float,30),1) > 0 "
+ ") as a "
+ "left join "
+ "( "
+ " select storeno, ppmidx, (sum(amt_safe_fri+amt_safe_mon+amt_safe_sat+amt_safe_sun+amt_safe_thu+amt_safe_tue+amt_safe_wed+amt_safe_wed) /7 ) as safe "
+ " from preset_safe_stock with(nolock) "
+ " group by storeno, ppmidx "
// + " having sum(amt_safe_fri+amt_safe_mon+amt_safe_sat+amt_safe_sun+amt_safe_thu+amt_safe_tue+amt_safe_wed+amt_safe_wed) /7 <= 3 "
+ " ) as b on right(a.storeno,4) = b.storeno and convert(varchar,a.ppmidx) = b.ppmidx "
+ " left join ( "
+ " select storeno, ppmidx, sum(qty_jungga) as jungga "
+ " from preset_sale_ppm_daily with(nolock) "
+ " where orderdate >='" + safe_sale_sdate + "' and orderdate <='" + safe_sale_edate + "' "
+ " group by storeno, ppmidx "
// + " having sum(qty_jungga) <= " + safe_junnga + " "
+ " ) as c on right(a.storeno,4) = c.storeno and convert(varchar,a.ppmidx) = c.ppmidx "
+ " where a.storeno is not null and b.safe <> 0 "
+ " order by storeno, ppmidx ";
}
command.Connection = myConnection;
command.CommandText = sql;
command.CommandTimeout = 120;
command.ExecuteNonQuery();
frm1.sendSms("생성기준 매월 1일 안전재고,정가판매 제외 " + safe_sale_sdate + "~" + safe_sale_edate + " 생성 완료");
}
catch (Exception ex)
{
frm1.Log("[매월 안전재고 정가판매 생성]:" + ex.Message);
frm1.Log("매월 안전재고 정가판매 생성쿼리:" + sql);
frm1.sendSms("매월 1일 안전재고, 정가판매 오류 ");
}
}
private void fn_get_sale_ppm_daily(string orderdate5, string orderdate6, Form1 frm1)
{
string sql = null;
string queryString1 = "";
myConnection = DbConnection.GetRemoteDataInstance().getRemoteDbConnection("213");
SqlCommand command = new SqlCommand(sql, myConnection);
SqlDataAdapter adapter = new SqlDataAdapter();
// SqlCommand command = new SqlCommand();
frm1.Log("PPM 정가판매 데이터 생성 시작");
string YYYYMM = String.Format("{0:yyyyMM}", Convert.ToDateTime(orderdate5));
try
{
//1. 낱개 박스설정 제외한 물품 적용 !!!!!!!!!!!수정
// 제외 카테고리 적용
sql = " INSERT INTO preset_sale_ppm_daily"
+ " (orderdate, storeno, ppmidx, qty_jungga, qty_total, qty_discount) "
+ " SELECT a.orderdate, right(a.storeno,4) as storeno, a.ppmidx "
+ " , SUM(CASE WHEN a.vc_mainvoid='0' THEN a.qty ELSE 0 END) - SUM(CASE WHEN a.vc_mainvoid='0' and (a.price_discount<>0 OR b.membergrade='13')THEN a.qty ELSE 0 END) as qty_jungga "
+ " , SUM(CASE WHEN a.vc_mainvoid='0' THEN a.qty ELSE 0 END) as qty_total "
+ " , SUM(CASE WHEN a.vc_mainvoid='0' and (a.price_discount<>0 OR b.membergrade='13') THEN a.qty ELSE 0 END) as qty_discount "
+ " FROM pos_sale_orderNo_detail_" + YYYYMM + " a WITH (NOLOCK) "
+ " LEFT JOIN pos_member_sy b WITH (NOLOCK) ON a.memidx=b.memidx "
+ " LEFT JOIN pos_db_membergrade_sub c WITH (NOLOCK) ON c.gradeNo2=b.memberGrade2 and c.memo='폐기자가' "
+ " LEFT JOIN pos_product_master_box as d WITH (NOLOCK) on a.ppmidx = d.ppmidx_box and d.vc ='1' "
+ " WHERE a.orderDate >= '" + orderdate5 + "' and a.orderDate <= '" + orderdate6 + "' "
+ " AND a.ppmidx in ( "
+ " select ppmidx from pos_sale_A01_ppm_daily as a with(nolock) "
+ " left join pos_product_master as b with(nolock) on a.ppmidx = b.idx "
+ " where (a.orderdate >= '" + orderdate5 + "' and a.orderdate <= '" + orderdate6 + "' ) and b.pdbidx not in ('3','4','5','6','7','8','9','10','11','12','13','14','15','16','17') and "
+ " b.pcmidx in (select idx from pos_category_master where idx not in ('1','4','15','18','23','62','77','83','84','85','86','102','107','106','108','109','155','103','129','130','144','161','164') and pcgidx not in('32','33') ) "
// + " and b.erp_p_ab = 'A' and b.erp_recall_ab <> 'A' and b.erp_shop_release_date > 1 "
+ "group by ppmidx "
+ " ) "
// + " AND a.ppmidx in (SELECT ppmidx FROM pos_sale_A01_ppm_daily WHERE orderdate >= '" + orderdate5 + "' and orderdate <= '" + orderdate6 + "' )"
+ " and ppmidx_box is null "
+ " GROUP BY a.orderdate,a.storeno,a.ppmidx "
+ " having SUM(CASE WHEN a.vc_mainvoid='0' THEN a.qty ELSE 0 END) - SUM(CASE WHEN a.vc_mainvoid='0' and (a.price_discount<>0 OR b.membergrade='13')THEN a.qty ELSE 0 END) > 0 ";
command = new SqlCommand(sql, myConnection);
command.CommandTimeout = 0;
command.CommandText = sql;
command.ExecuteNonQuery();
}
catch (Exception ex)
{
frm1.sendSms("ppm_daily 박스 제외 쿼리 1:" + ex.Message);
frm1.Log("ppm_daily 박스 제외 쿼리 1(fn_get_sale_ppm_daily)]:" + ex.Message);
frm1.Log("박스제외쿼리:" + sql);
//throw;
}
//myConnection.Dispose();
//myConnection.Close();
//2. 낱개 박스설정 포함한 물품에 대한 낱개코드로 환산
string queryString = " SELECT a.orderdate, right(a.storeno,4) as storeno, a.ppmidx, d.ppmidx_single "
+ " , SUM(CASE WHEN a.vc_mainvoid='0' THEN a.qty * ppmidx_box_ea ELSE 0 END) - SUM(CASE WHEN a.vc_mainvoid='0' and (a.price_discount<>0 OR b.membergrade='13') THEN a.qty * ppmidx_box_ea ELSE 0 END) as qty_jungga "
+ " , SUM(CASE WHEN a.vc_mainvoid='0' THEN a.qty * ppmidx_box_ea ELSE 0 END) as qty_total "
+ ", SUM(CASE WHEN a.vc_mainvoid='0' and (a.price_discount<>0 OR b.membergrade='13') THEN a.qty * ppmidx_box_ea ELSE 0 END) as qty_discount "
+ " FROM pos_sale_orderNo_detail_" + YYYYMM + " a WITH (NOLOCK) "
+ " LEFT JOIN pos_member_sy b WITH (NOLOCK) ON a.memidx=b.memidx "
+ " LEFT JOIN pos_db_membergrade_sub c WITH (NOLOCK) ON c.gradeNo2=b.memberGrade2 and c.memo='폐기자가' "
+ " LEFT JOIN pos_product_master_box as d WITH (NOLOCK) on a.ppmidx = d.ppmidx_box and d.vc ='1' "
+ " WHERE a.orderDate >= '" + orderdate5 + "' and a.orderDate <= '" + orderdate6 + "' " // AND d.ppmidx_single in ('26433','15494','21219') " //cug 2276010 덧글 28> 등록된 물품만
+ " AND a.ppmidx in ( "
+ " select ppmidx from pos_sale_A01_ppm_daily as a with(nolock) "
+ " left join pos_product_master as b with(nolock) on a.ppmidx = b.idx "
+ " where (a.orderdate >= '" + orderdate5 + "' and a.orderdate <= '" + orderdate6 + "' ) and b.pdbidx not in ('3','4','5','6','7','8','9','10','11','12','13','14','15','16','17') and "
+ " b.pcmidx in (select idx from pos_category_master where idx not in ('1','4','15','18','23','62','77','83','84','85','86','102','107','106','108','109','155','103','129','130','144','161','164') and pcgidx not in('32','33') ) "
// + " and b.erp_p_ab = 'A' and b.erp_recall_ab <> 'A' and b.erp_shop_release_date > 1 "
+ "group by ppmidx "
+ " ) "
//+ " AND a.ppmidx in (SELECT ppmidx FROM pos_sale_A01_ppm_daily WHERE orderdate >= '" + orderdate5 + "' and orderdate <= '" + orderdate6 + "' )"
+ " and ppmidx_box is not null "
+ " GROUP BY a.orderdate, a.storeno,a.ppmidx, d.ppmidx_single "
+ " having SUM(CASE WHEN a.vc_mainvoid='0' THEN a.qty * ppmidx_box_ea ELSE 0 END) - SUM(CASE WHEN a.vc_mainvoid='0' and (a.price_discount<>0 OR b.membergrade='13') THEN a.qty * ppmidx_box_ea ELSE 0 END) > 0 ";
adapter.SelectCommand = new SqlCommand(queryString, myConnection);
adapter.SelectCommand.CommandTimeout = 0;
try
{
DataSet ds = new DataSet();
adapter.Fill(ds);
DataTable table = ds.Tables[0];
DataRowCollection rows = table.Rows;
string orderdate = "";
string storeno = "";
string ppmidx_single = "";
int qty_jungga = 0;
int qty_total = 0;
int qty_discount = 0;
foreach (DataRow dr in rows)
{
orderdate = dr["orderdate"].ToString();
storeno = dr["storeno"].ToString();
ppmidx_single = dr["ppmidx_single"].ToString().Trim();
qty_jungga = Convert.ToInt32(dr["qty_jungga"].ToString());
qty_total = Convert.ToInt32(dr["qty_total"].ToString());
qty_discount = Convert.ToInt32(dr["qty_discount"].ToString());
//기존에 있는 물품인 경우, 설정값 Update 없는 물품인 경우 Insert
queryString1 = "IF EXISTS (SELECT * FROM preset_sale_ppm_daily WITH (NOLOCK) WHERE orderdate = '" + orderdate + "' AND storeno ='" + storeno + "' AND ppmidx ='" + ppmidx_single + "')"
+ " BEGIN "
+ " UPDATE preset_sale_ppm_daily "
+ " SET qty_jungga = qty_jungga + '" + qty_jungga + "', qty_total = qty_total + '" + qty_total + "', qty_discount = qty_discount +'" + qty_discount + "'"
+ " WHERE orderdate = '" + orderdate + "' AND storeno = '" + storeno + "' AND ppmidx ='" + ppmidx_single + "' "
+ " END"
+ " ELSE"
+ " BEGIN"
+ " INSERT INTO preset_sale_ppm_daily "
+ " (orderdate, storeno, ppmidx, qty_jungga, qty_total, qty_discount) "
+ " VALUES ('" + orderdate + "', '" + storeno + "', '" + ppmidx_single + "', '" + qty_jungga + "', '" + qty_total + "', '" + qty_discount + "') "
+ " END";
SqlCommand command1 = new SqlCommand(queryString1, myConnection);
command1.CommandText = queryString1;
command1.ExecuteNonQuery();
}
// frm1.sendSms("NEWPOS_PPM 기초 자료 생성 완료");
frm1.Log("PPM 정가판매 데이터 생성 완료");
}
catch (Exception ex)
{
frm1.sendSms("박스기초자료오류:" + ex.Message);
frm1.Log("[박스기초자료오류]:" + ex.Message);
frm1.Log("박스기초자료쿼리:" + queryString1);
//throw;
}
myConnection.Dispose();
myConnection.Close();
frm1.Log((String.Format("[MsSql 정가판매량 ]:{0}", myConnection.State.ToString())));
}
private void fn_get_safe_standard_deviation_total(string sdate, string edate, Form1 frm1)
{
myConnection = DbConnection.GetRemoteDataInstance().getRemoteDbConnection("213");
SqlDataAdapter adapter = new SqlDataAdapter();
string queryString1 = "";
string stock_date = string.Format(DateTime.Today.AddDays(0).ToString("yyyy-MM-dd"));
string queryString = "SELECT a.storeno "
+ " FROM pos_store_detail as a "
+ " LEFT JOIN pos_store b ON b.storeno = a.storeno "
+ " WHERE b.vc_businessStatus = '1' AND a.store_mcode <> '' AND b.storeType <> 'A' "
+ " AND a.sdate <= '" + stock_date + "' AND (a.edate = '' OR a.edate >= '" + stock_date + "') "
+ " and a.storeno not in ('8000006996','8000009011','8000009017','8000006158') ";
adapter.SelectCommand = new SqlCommand(queryString, myConnection);
SqlCommand command = new SqlCommand(queryString, myConnection);
try
{
DataSet ds = new DataSet();
adapter.Fill(ds);
DataTable table = ds.Tables[0];
DataRowCollection rows = table.Rows;
string storeno = "";
foreach (DataRow dr in rows)
{
storeno = dr["storeno"].ToString();
//queryString1 = " insert into preset_scm_stock_share (storeno, ppmidx, total_jungga, share, cum_share,grade)"
// + " select b.*, "
// + " (case when running < 80 then 'A' when running > 80 and running < 90 then 'B' when running >= 90 then 'C' else '' end) as grade "
// + " from "
// + " ( "
// + " select a.storeno, a.ppmidx, "
// + " sum(a.qty_jungga)as qty_jungga,"
// + " round(sum(a.qty_jungga)/convert(float,c.jungga_total)*100,2) as avg_jungga, "
// + " round(sum(sum(a.qty_jungga)/convert(float,c.jungga_total)*100) over(order by sum(a.qty_jungga) desc),1) as running from "
// + " ( "
// + " select right(storeno,4) as storeno , sum(qty_jungga) as jungga_total from preset_sale_ppm_daily "
// + " where storeno ='" + storeno + "' and (orderdate >='" + sdate + "' and orderdate <='" + edate + "')"
// + " group by storeno"
// + " ) as c"
// + " left join"
// + " ( "
// + " select right(storeno,4) as storeno, ppmidx,sum(qty_jungga)as qty_jungga from "
// + " preset_sale_ppm_daily "
// + " where storeno ='" + storeno + "' and (orderdate >='" + sdate + "' and orderdate <='" + edate + "')"
// + " group by storeno, ppmidx "
// + " ) as a on a.storeno = c.storeno"
// + " group by a.storeno, a.ppmidx,c.jungga_total"
// + " ) as b "
// + " group by b.storeno, b.ppmidx,b.qty_jungga,b.avg_jungga,b.running "
// + "order by storeno, sum(qty_jungga) desc ";
queryString1 = " insert into preset_scm_stock_share (storeno, ppmidx, total_jungga, share, cum_share,grade)"
+ " select a.storeno, a.ppmidx, "
+ " sum(a.qty_jungga)as qty_jungga, "
+ " round(sum(a.qty_jungga)/convert(float,c.jungga_total)*100,2) as avg_jungga, "
+ " round(sum(sum(a.qty_jungga)/convert(float,c.jungga_total)*100) over(order by sum(a.qty_jungga) desc),1) as running "
+ " , case "
+ " when round(sum(sum(a.qty_jungga)/convert(float,c.jungga_total)*100) over(order by sum(a.qty_jungga) desc),1) < 80 then 'A' "
+ " when round(sum(sum(a.qty_jungga)/convert(float,c.jungga_total)*100) over(order by sum(a.qty_jungga) desc),1) > 80 and round(sum(sum(a.qty_jungga)/convert(float,c.jungga_total)*100) over(order by sum(a.qty_jungga) desc),1) < 90 then 'B' "
+ " when round(sum(sum(a.qty_jungga)/convert(float,c.jungga_total)*100) over(order by sum(a.qty_jungga) desc),1) >= 90 then 'C' "
+ " else '' "
+ " end "
+ " from "
+ " ( "
+ " select storeno , sum(qty_jungga) as jungga_total "
+ " from preset_sale_ppm_daily "
+ " where storeno ='"+ storeno + "' and (orderdate >='" + sdate + "' and orderdate <='" + edate + "') "
+ " group by storeno "
+ " ) as c "
+ " left join "
+ " ( "
+ " select storeno, ppmidx, sum(qty_jungga)as qty_jungga "
+ " from preset_sale_ppm_daily "
+ " where storeno ='"+ storeno + "' and (orderdate >='" + sdate + "' and orderdate <='" + edate + "') "
+ " group by storeno, ppmidx "
+ " ) as a on a.storeno = c.storeno "
+ " group by a.storeno, a.ppmidx,c.jungga_total ";
command.CommandText = queryString1;
command.ExecuteNonQuery();
}
frm1.sendSms("매월1일 ABC 누적 데이터 생성 완료");
frm1.Log("매월1일 ABC 누적 데이터 생성 완료");
}
catch (Exception ex)
{
frm1.Log("[매월1일 ABC 누적 데이터 생성 에러 ]:" + ex.Message);
frm1.Log("매월1일 ABC 누적 데이터 생성 쿼리:" + queryString1);
frm1.sendSms("매월1일 ABC 누적 데이터 생성 오류");
}
myConnection.Dispose();
myConnection.Close();
}