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())));
}
public void fn_ppmdaily(string orderdate5, string orderdate6, Form1 frm1)
{
//frm1.sendSms("PPM " + orderdate5 + " 정가판매 데이터 생성 시작");
fn_get_sale_ppm_daily(orderdate5, orderdate6, frm1); //기초 데이터 생성
frm1.sendSms("PPM " + orderdate5 + " 정가판매 데이터 생성 완료");
}