public void Remove(PL_CUSTOMER_CLUSTER id) { using (var conn = new SqlConnection(connString)) { try { Message = ""; conn.Open(); SqlCommand command = new SqlCommand("DELETE PL_CUSTOMER_CLUSTER WHERE ID = @ID", conn); command.CommandType = System.Data.CommandType.Text; if (id.ID != null) { command.Parameters.AddWithValue("@ID", id.ID); } else { command.Parameters.AddWithValue("@ID", DBNull.Value); } command.ExecuteNonQuery(); } catch (Exception ex) { Message = ex.Message; } } }
public List <PL_CUSTOMER_CLUSTER> GetDateByID(DateTime date) { List <PL_CUSTOMER_CLUSTER> items = new List <PL_CUSTOMER_CLUSTER>(); using (var conn = new SqlConnection(connString)) { Message = ""; //DATETIME = "'" + DATETIME + "'"; try { conn.Open(); SqlCommand command = new SqlCommand("SELECT [ID], [DATE_STAMP], COALESCE(NAMA, [IDREFPELANGGAN]) As NAMA, [IDREFPELANGGAN], [HOUR_NUM], [CLUSTER_NUM], [FDVC_NORMALIZED] FROM[SIPG].[dbo].[VW_AA_DATAPELANGGAN_V1] WHERE CLUSTER_NUM in (0,1) AND DATE_STAMP = '" + date.ToString("yyyy-MM-dd") + "' AND IDREFPELANGGAN IN('CENTROID_ID0', 'CENTROID_ID1', '0140019647', '014525646', '0140019671', '014517181') ORDER BY DATE_STAMP,IDREFPELANGGAN, HOUR_NUM", conn); //command.Parameters.AddWithValue("@ID", ID); command.Parameters.AddWithValue("@DATE", date); SqlDataReader reader = command.ExecuteReader(); PL_CUSTOMER_CLUSTER item = new PL_CUSTOMER_CLUSTER(); while (reader.Read()) { item = new PL_CUSTOMER_CLUSTER(); if (reader[0] != DBNull.Value) { item.ID = Convert.ToInt32(reader[0]); } if (reader[1] != DBNull.Value) { item.DATE_STAMP = Convert.ToDateTime(reader[1]); } if (reader[2] != DBNull.Value) { item.NAMA = Convert.ToString(reader[2]); } if (reader[3] != DBNull.Value) { item.IDREFPELANGGAN = Convert.ToString(reader[3]); } if (reader[4] != DBNull.Value) { item.HOUR_NUM = Convert.ToInt32(reader[4]); } if (reader[5] != DBNull.Value) { item.CLUSTER_NUM = Convert.ToInt32(reader[5]); } if (reader[6] != DBNull.Value) { item.FDVC_NORMALIZED = Convert.ToDouble(reader[6]); } items.Add(item); } } catch (Exception ex) { Message = ex.Message; } } return(items); }
public List <PL_CUSTOMER_CLUSTER> GetCentroid2() { List <PL_CUSTOMER_CLUSTER> items = new List <PL_CUSTOMER_CLUSTER>(); using (var conn = new SqlConnection(connString)) { Message = ""; try { conn.Open(); SqlCommand command = new SqlCommand("SELECT[ID],[DATE_STAMP],COALESCE(NAMA, [IDREFPELANGGAN]) As NAMA,[IDREFPELANGGAN],[HOUR_NUM],[CLUSTER_NUM],[FDVC_NORMALIZED] FROM[SIPG].[dbo].[VW_AA_DATAPELANGGAN_V1] WHERE CLUSTER_NUM LIKE '%0%' AND DATE_STAMP = '2020-11-01' AND IDREFPELANGGAN IN('CENTROID_ID0', '014577739', '0140019647', '014525646', '0140019671', '014517181') ORDER BY IDREFPELANGGAN, HOUR_NUM", conn); SqlDataReader reader = command.ExecuteReader(); PL_CUSTOMER_CLUSTER item = new PL_CUSTOMER_CLUSTER(); while (reader.Read()) { item = new PL_CUSTOMER_CLUSTER(); if (reader[0] != DBNull.Value) { item.ID = Convert.ToInt32(reader[0]); } if (reader[1] != DBNull.Value) { item.DATE_STAMP = Convert.ToDateTime(reader[1]); } if (reader[2] != DBNull.Value) { item.NAMA = Convert.ToString(reader[2]); } if (reader[2] != DBNull.Value) { item.IDREFPELANGGAN = Convert.ToString(reader[3]); } if (reader[3] != DBNull.Value) { item.HOUR_NUM = Convert.ToInt32(reader[4]); } if (reader[4] != DBNull.Value) { item.CLUSTER_NUM = Convert.ToInt32(reader[5]); } if (reader[5] != DBNull.Value) { item.FDVC_NORMALIZED = Convert.ToDouble(reader[6]); } items.Add(item); } } catch (Exception ex) { Message = ex.Message; } } return(items); }
public List <PL_CUSTOMER_CLUSTER> GetCustomer(int ID, string MONTH, int YEAR) { List <PL_CUSTOMER_CLUSTER> items = new List <PL_CUSTOMER_CLUSTER>(); using (var conn = new SqlConnection(connString)) { Message = ""; try { conn.Open(); SqlCommand command = new SqlCommand("SELECT DISTINCT [DATE_STAMP],[MONTH],[YEAR],[IDREFPELANGGAN],[NAMA],[CLUSTER_NUM] FROM [SIPG].[dbo].[VW_AA_DATAPELANGGAN_V1] where CLUSTER_NUM = @ID and MONTH = @MONTH and YEAR = @YEAR AND NAMA NOT LIKE '%CENTROID%' ORDER BY NAMA DESC", conn); command.Parameters.AddWithValue("@ID", ID); command.Parameters.AddWithValue("@MONTH", MONTH); command.Parameters.AddWithValue("@YEAR", YEAR); SqlDataReader reader = command.ExecuteReader(); PL_CUSTOMER_CLUSTER item = new PL_CUSTOMER_CLUSTER(); while (reader.Read()) { item = new PL_CUSTOMER_CLUSTER(); if (reader[0] != DBNull.Value) { item.DATE_STAMP = Convert.ToDateTime(reader[0]); } if (reader[1] != DBNull.Value) { item.MONTH = Convert.ToString(reader[1]); } if (reader[2] != DBNull.Value) { item.YEAR = Convert.ToInt32(reader[2]); } if (reader[3] != DBNull.Value) { item.IDREFPELANGGAN = Convert.ToString(reader[3]); } if (reader[4] != DBNull.Value) { item.NAMA = Convert.ToString(reader[4]); } if (reader[5] != DBNull.Value) { item.CLUSTER_NUM = Convert.ToInt32(reader[5]); } items.Add(item); } } catch (Exception ex) { Message = ex.Message; } } return(items); }
public List <PL_CUSTOMER_CLUSTER> GetDataByID(int ID) { List <PL_CUSTOMER_CLUSTER> items = new List <PL_CUSTOMER_CLUSTER>(); using (var conn = new SqlConnection(connString)) { Message = ""; try { conn.Open(); SqlCommand command = new SqlCommand("SELECT [ID], [DATE_STAMP], [IDREFPELANGGAN], [HOUR_NUM], [CLUSTER_NUM], [FDVC_NORMALIZED] FROM PL_CUSTOMER_CLUSTER WHERE ID = @ID", conn); command.Parameters.AddWithValue("@ID", ID); SqlDataReader reader = command.ExecuteReader(); PL_CUSTOMER_CLUSTER item = new PL_CUSTOMER_CLUSTER(); while (reader.Read()) { item = new PL_CUSTOMER_CLUSTER(); if (reader[0] != DBNull.Value) { item.ID = Convert.ToInt32(reader[0]); } if (reader[1] != DBNull.Value) { item.DATE_STAMP = Convert.ToDateTime(reader[1]); } if (reader[2] != DBNull.Value) { item.IDREFPELANGGAN = Convert.ToString(reader[2]); } if (reader[3] != DBNull.Value) { item.HOUR_NUM = Convert.ToInt32(reader[3]); } if (reader[4] != DBNull.Value) { item.CLUSTER_NUM = Convert.ToInt32(reader[4]); } if (reader[5] != DBNull.Value) { item.FDVC_NORMALIZED = Convert.ToDouble(reader[5]); } items.Add(item); } } catch (Exception ex) { Message = ex.Message; } } return(items); }
public List <PL_CUSTOMER_CLUSTER> GetDataPelanggan() { List <PL_CUSTOMER_CLUSTER> items = new List <PL_CUSTOMER_CLUSTER>(); using (var conn = new SqlConnection(connString)) { Message = ""; try { conn.Open(); SqlCommand command = new SqlCommand("SELECT [DATE_STAMP],[IDREFPELANGGAN],[NAMA],[HOUR_NUM],[CLUSTER_NUM],[FDVC_NORMALIZED]FROM [SIPG].[dbo].[VW_AA_DATAPELANGGAN_V1] WHERE CLUSTER_NUM = 0 ORDER BY DATE_STAMP, IDREFPELANGGAN, CLUSTER_NUM, HOUR_NUM", conn); SqlDataReader reader = command.ExecuteReader(); PL_CUSTOMER_CLUSTER item = new PL_CUSTOMER_CLUSTER(); while (reader.Read()) { item = new PL_CUSTOMER_CLUSTER(); if (reader[0] != DBNull.Value) { item.DATE_STAMP = Convert.ToDateTime(reader[0]); } if (reader[1] != DBNull.Value) { item.IDREFPELANGGAN = Convert.ToString(reader[1]); } if (reader[2] != DBNull.Value) { item.NAMA = Convert.ToString(reader[2]); } if (reader[3] != DBNull.Value) { item.HOUR_NUM = Convert.ToInt32(reader[3]); } if (reader[4] != DBNull.Value) { item.CLUSTER_NUM = Convert.ToInt32(reader[4]); } if (reader[5] != DBNull.Value) { item.FDVC_NORMALIZED = Convert.ToInt32(reader[5]); } items.Add(item); } } catch (Exception ex) { Message = ex.Message; } } return(items); }
public List <PL_CUSTOMER_CLUSTER> GetCustomer_byCluster(DateTime date, int cluster) { List <PL_CUSTOMER_CLUSTER> items = new List <PL_CUSTOMER_CLUSTER>(); using (var conn = new SqlConnection(connString)) { Message = ""; try { conn.Open(); SqlCommand command = new SqlCommand(" SELECT * FROM [SIPG].[dbo].[PL_CUSTOMER_CLUSTER] WHERE CLUSTER_NUM = " + Convert.ToString(cluster) + " AND DATE_STAMP = '" + date.ToString("yyyy-MM-dd") + "' ORDER BY IDREFPELANGGAN ASC", conn); SqlDataReader reader = command.ExecuteReader(); PL_CUSTOMER_CLUSTER item = new PL_CUSTOMER_CLUSTER(); while (reader.Read()) { item = new PL_CUSTOMER_CLUSTER(); if (reader[0] != DBNull.Value) { item.ID = Convert.ToInt32(reader[0]); } if (reader[1] != DBNull.Value) { item.DATE_STAMP = Convert.ToDateTime(reader[1]); } if (reader[2] != DBNull.Value) { item.IDREFPELANGGAN = Convert.ToString(reader[2]); } if (reader[3] != DBNull.Value) { item.HOUR_NUM = Convert.ToInt32(reader[3]); } if (reader[4] != DBNull.Value) { item.CLUSTER_NUM = Convert.ToInt32(reader[4]); } if (reader[5] != DBNull.Value) { item.FDVC_NORMALIZED = Convert.ToDouble(reader[5]); } items.Add(item); } } catch (Exception ex) { Message = ex.Message; } } return(items); }
public void Add(PL_CUSTOMER_CLUSTER pl_customer_cluster) { using (var conn = new SqlConnection(connString)) { try { Message = ""; conn.Open(); SqlCommand command = new SqlCommand("INSERT INTO PL_CUSTOMER_CLUSTER ([ID], [DATE_STAMP], [IDREFPELANGGAN], [HOUR_NUM], [CLUSTER_NUM], [FDVC_NORMALIZED]) VALUES(@ID, @DATE_STAMP, @IDREFPELANGGAN, @HOUR_NUM, @CLUSTER_NUM, @FDVC_NORMALIZED)", conn); command.CommandType = System.Data.CommandType.Text; if (pl_customer_cluster.ID != null) { command.Parameters.AddWithValue("@ID", pl_customer_cluster.ID); } else { command.Parameters.AddWithValue("@ID", DBNull.Value); } if (pl_customer_cluster.DATE_STAMP != null) { command.Parameters.AddWithValue("@DATE_STAMP", pl_customer_cluster.DATE_STAMP); } else { command.Parameters.AddWithValue("@DATE_STAMP", DBNull.Value); } if (pl_customer_cluster.IDREFPELANGGAN != null) { command.Parameters.AddWithValue("@IDREFPELANGGAN", pl_customer_cluster.IDREFPELANGGAN); } else { command.Parameters.AddWithValue("@IDREFPELANGGAN", DBNull.Value); } if (pl_customer_cluster.HOUR_NUM != null) { command.Parameters.AddWithValue("@HOUR_NUM", pl_customer_cluster.HOUR_NUM); } else { command.Parameters.AddWithValue("@HOUR_NUM", DBNull.Value); } if (pl_customer_cluster.CLUSTER_NUM != null) { command.Parameters.AddWithValue("@CLUSTER_NUM", pl_customer_cluster.CLUSTER_NUM); } else { command.Parameters.AddWithValue("@CLUSTER_NUM", DBNull.Value); } if (pl_customer_cluster.FDVC_NORMALIZED != null) { command.Parameters.AddWithValue("@FDVC_NORMALIZED", pl_customer_cluster.FDVC_NORMALIZED); } else { command.Parameters.AddWithValue("@FDVC_NORMALIZED", DBNull.Value); } command.ExecuteNonQuery(); } catch (Exception ex) { Message = ex.Message; } } }
public void Update(PL_CUSTOMER_CLUSTER pl_customer_cluster) { using (var conn = new SqlConnection(connString)) { try { Message = ""; conn.Open(); SqlCommand command = new SqlCommand("UPDATE PL_CUSTOMER_CLUSTER SET DATE_STAMP = @DATE_STAMP, IDREFPELANGGAN = @IDREFPELANGGAN, HOUR_NUM = @HOUR_NUM, CLUSTER_NUM = @CLUSTER_NUM, FDVC_NORMALIZED = @FDVC_NORMALIZED WHERE ID = @ID", conn); command.CommandType = System.Data.CommandType.Text; if (pl_customer_cluster.ID != null) { command.Parameters.AddWithValue("@ID", pl_customer_cluster.ID); } else { command.Parameters.AddWithValue("@ID", DBNull.Value); } if (pl_customer_cluster.DATE_STAMP != null) { command.Parameters.AddWithValue("@DATE_STAMP", pl_customer_cluster.DATE_STAMP); } else { command.Parameters.AddWithValue("@DATE_STAMP", DBNull.Value); } if (pl_customer_cluster.IDREFPELANGGAN != null) { command.Parameters.AddWithValue("@IDREFPELANGGAN", pl_customer_cluster.IDREFPELANGGAN); } else { command.Parameters.AddWithValue("@IDREFPELANGGAN", DBNull.Value); } if (pl_customer_cluster.HOUR_NUM != null) { command.Parameters.AddWithValue("@HOUR_NUM", pl_customer_cluster.HOUR_NUM); } else { command.Parameters.AddWithValue("@HOUR_NUM", DBNull.Value); } if (pl_customer_cluster.CLUSTER_NUM != null) { command.Parameters.AddWithValue("@CLUSTER_NUM", pl_customer_cluster.CLUSTER_NUM); } else { command.Parameters.AddWithValue("@CLUSTER_NUM", DBNull.Value); } if (pl_customer_cluster.FDVC_NORMALIZED != null) { command.Parameters.AddWithValue("@FDVC_NORMALIZED", pl_customer_cluster.FDVC_NORMALIZED); } else { command.Parameters.AddWithValue("@FDVC_NORMALIZED", DBNull.Value); } command.ExecuteNonQuery(); } catch (Exception ex) { Message = ex.Message; } } }
public void Delete(PL_CUSTOMER_CLUSTER pl_customer_cluster) { PL_CUSTOMER_CLUSTERRepository rep = new PL_CUSTOMER_CLUSTERRepository(connectionString); rep.Remove(pl_customer_cluster); }
public void Put(PL_CUSTOMER_CLUSTER pl_customer_cluster) { PL_CUSTOMER_CLUSTERRepository rep = new PL_CUSTOMER_CLUSTERRepository(connectionString); rep.Update(pl_customer_cluster); }