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);
}
