public static Models.konsumen get_detail(string kode)
{
using (var db = new Models.sim_penjualanContext())
{
return(db.konsumen.Where(t => t.kd_konsumen == kode).SingleOrDefault());
}
}
public static List <Models.konsumen> get_data()
{
using (var db = new Models.sim_penjualanContext())
{
return(db.konsumen.ToList());
}
}
public static string get_kode()
{
using (var db = new Models.sim_penjualanContext())
{
return(db.Database.SqlQuery <string>("CALL penomoran('KSM','konsumen', 'kd_konsumen')").SingleOrDefault());
}
}
public static List <Models.barang> search(string kolom, string param)
{
kolom = kolom.ToLower();
using (var db = new Models.sim_penjualanContext())
{
if (kolom.Contains("kode"))
{
return(db.barangs.Where(t => t.kd_barang.Contains(param)).ToList());
}
else if (kolom.Contains("nama"))
{
return(db.barangs.Where(t => t.nama.Contains(param)).ToList());
}
else if (kolom.Contains("harga"))
{
return(db.barangs.Where(t => t.harga.ToString().Contains(param)).ToList());
}
else if (kolom.Contains("satuan"))
{
return(db.barangs.Where(t => t.satuan.Contains(param)).ToList());
}
else if (kolom.Contains("jenis"))
{
return(db.barangs.Where(t => t.jenis.Contains(param)).ToList());
}
else
{
return(db.barangs.Where(t => t.stok.ToString().Contains(param)).ToList());
}
}
}
public static Models.barang get_detail(string kode)
{
using (var db = new Models.sim_penjualanContext())
{
return(db.barangs.Where(t => t.kd_barang == kode).SingleOrDefault());
}
}
public static DataSet1 get_report_per_jenis(string param)
{
DataSet1 result = new DataSet1();
try
{
string sql = string.Format("select * from barang where jenis in ({0})", "'" + param.Replace(", ", "','") + "'");
using (var db = new Models.sim_penjualanContext())
{
db.Database.Connection.Open();
using (var cmd = db.Database.Connection.CreateCommand())
{
cmd.CommandText = sql;
cmd.CommandType = System.Data.CommandType.Text;
using (var reader = cmd.ExecuteReader())
{
var dt = new System.Data.DataTable();
dt.Load(reader);
result.Tables["barang"].Merge(dt);
}
}
}
}
catch (Exception ex)
{
Helpers.Generic.MsgError(ex.Message);
}
return(result);
}
public static List <Models.karyawan> search(string kolom, string param)
{
kolom = kolom.ToLower();
using (var db = new Models.sim_penjualanContext())
{
if (kolom.Contains("kode"))
{
return(db.karyawans.Where(t => t.kd_karyawan.Contains(param)).ToList());
}
else if (kolom.Contains("nama"))
{
return(db.karyawans.Where(t => t.nama.Contains(param)).ToList());
}
else if (kolom.Contains("alamat"))
{
return(db.karyawans.Where(t => t.alamat.Contains(param)).ToList());
}
else if (kolom.Contains("email"))
{
return(db.karyawans.Where(t => t.email.Contains(param)).ToList());
}
else if (kolom.Contains("username"))
{
return(db.karyawans.Where(t => t.username.Contains(param)).ToList());
}
else
{
return(db.karyawans.Where(t => t.hak_akses.Contains(param)).ToList());
}
}
}
public static DataSet1 get_faktur(string no_faktur)
{
DataSet1 result = new DataSet1();
try
{
string[] table1 = { "pembayaran", "penjualan", "detail_penjualan" };
foreach (var table in table1)
{
string sql = string.Format("select * from {0} where no_faktur = '{1}'", table, no_faktur);
using (var db = new Models.sim_penjualanContext())
{
db.Database.Connection.Open();
using (var cmd = db.Database.Connection.CreateCommand())
{
cmd.CommandText = sql;
cmd.CommandType = System.Data.CommandType.Text;
using (var reader = cmd.ExecuteReader())
{
var dt = new System.Data.DataTable();
dt.Load(reader);
result.Tables[table].Merge(dt);
}
}
}
}
string[] table2 = { "karyawan", "barang", "konsumen" };
foreach (var table in table2)
{
string sql = string.Format("select * from {0}", table);
using (var db = new Models.sim_penjualanContext())
{
db.Database.Connection.Open();
using (var cmd = db.Database.Connection.CreateCommand())
{
cmd.CommandText = sql;
cmd.CommandType = System.Data.CommandType.Text;
using (var reader = cmd.ExecuteReader())
{
var dt = new System.Data.DataTable();
dt.Load(reader);
result.Tables[table].Merge(dt);
}
}
}
}
}
catch (Exception ex)
{
Helpers.Generic.MsgError(ex.Message);
}
return(result);
}
public static DataSet1 get_report(string column, string param)
{
DataSet1 result = new DataSet1();
try
{
string sql = "select * from barang";
column = column.ToLower();
if (column.Contains("kode"))
{
sql += string.Format(" where kd_barang like '%{0}%'", param);
}
else if (column.Contains("nama"))
{
sql += string.Format(" where nama like '%{0}%'", param);
}
else if (column.Contains("harga"))
{
sql += string.Format(" where harga like '%{0}%'", param);
}
else if (column.Contains("satuan"))
{
sql += string.Format(" where satuan like '%{0}%'", param);
}
else if (column.Contains("jenis"))
{
sql += string.Format(" where jenis like '%{0}%'", param);
}
else if (column.Contains("stok"))
{
sql += string.Format(" where stok like '%{0}%'", param);
}
using (var db = new Models.sim_penjualanContext())
{
db.Database.Connection.Open();
using (var cmd = db.Database.Connection.CreateCommand())
{
cmd.CommandText = sql;
cmd.CommandType = System.Data.CommandType.Text;
using (var reader = cmd.ExecuteReader())
{
var dt = new System.Data.DataTable();
dt.Load(reader);
result.Tables["barang"].Merge(dt);
}
}
}
}
catch (Exception ex)
{
Helpers.Generic.MsgError(ex.Message);
}
return(result);
}
public static DataSet1 get_report(string kolom, string param)
{
DataSet1 result = new DataSet1();
kolom = kolom.ToLower();
string sql = "select * from karyawan where ";
if (kolom.Contains("kode"))
{
sql += string.Format("kd_karyawan like '%{0}%'", param);
}
else if (kolom.Contains("nama"))
{
sql += string.Format("nama like '%{0}%'", param);
}
else if (kolom.Contains("alamat"))
{
sql += string.Format("alamat like '%{0}%'", param);
}
else if (kolom.Contains("email"))
{
sql += string.Format("email like '%{0}%'", param);
}
else if (kolom.Contains("akses"))
{
sql += string.Format("hak_akses like '%{0}%'", param);
}
try
{
using (var db = new Models.sim_penjualanContext())
{
db.Database.Connection.Open();
using (var cmd = db.Database.Connection.CreateCommand())
{
cmd.CommandText = sql;
cmd.CommandType = System.Data.CommandType.Text;
using (var reader = cmd.ExecuteReader())
{
var dt = new System.Data.DataTable();
dt.Load(reader);
result.Tables["karyawan"].Merge(dt);
}
}
}
}
catch (Exception ex)
{
Helpers.Generic.MsgError(ex.Message);
}
return(result);
}
public static string get_faktur()
{
try
{
using (var db = new Models.sim_penjualanContext())
{
return(db.Database.SqlQuery <string>("call penomoran_transaksi('TRJ','penjualan','no_faktur')").SingleOrDefault());
}
}
catch (Exception ex)
{
Helpers.Generic.MsgError(ex.Message);
}
return("");
}
public static List <jenisbarang> get_jenis()
{
try
{
using (var db = new Models.sim_penjualanContext())
{
return(db.Database.SqlQuery <jenisbarang>("select distinct jenis from barang order by satuan desc").ToList());
}
}
catch (Exception ex)
{
Helpers.Generic.MsgError(ex.Message);
}
return(new List <jenisbarang>());
}
public static List <Models.barang> get_data()
{
try
{
using (var db = new Models.sim_penjualanContext())
{
return(db.barangs.ToList());
}
}
catch (Exception ex)
{
Helpers.Generic.MsgError(ex.Message);
}
return(new List <Models.barang>());
}
public static List <Models.penjualan> get_penjualan()
{
try
{
using (var db = new Models.sim_penjualanContext())
{
return(db.penjualans
.Include(t => t.konsumen)
.Where(t => t.status == 0).ToList());
}
}
catch (Exception ex)
{
Helpers.Generic.MsgError(ex.Message);
}
return(new List <Models.penjualan>());
}
public static DataSet1 get_report(string column, string param)
{
DataSet1 ds = new DataSet1();
try
{
string sql = "select * from konsumen where ";
if (column.Contains("kode"))
{
sql += string.Format("kd_konsumen like '%{0}%'", param);
}
else if (column.Contains("nama"))
{
sql += string.Format("nama like '%{0}%'", param);
}
else if (column.Contains("alamat"))
{
sql += string.Format("alamat like '%{0}%'", param);
}
else
{
sql += string.Format("email like '%{0}%'", param);
}
using (var db = new Models.sim_penjualanContext())
{
db.Database.Connection.Open();
using (var cmd = db.Database.Connection.CreateCommand())
{
cmd.CommandText = sql;
cmd.CommandType = System.Data.CommandType.Text;
using (var reader = cmd.ExecuteReader())
{
var dt = new System.Data.DataTable();
dt.Load(reader);
ds.Tables["konsumen"].Merge(dt);
}
}
}
}
catch (Exception ex)
{
Helpers.Generic.MsgError(ex.Message);
}
return(ds);
}
public static List <Models.detail_penjualan> get_detail_barang(string strNoFaktur)
{
try
{
using (var db = new Models.sim_penjualanContext())
{
return(db.detail_penjualan
.Include(t => t.barang)
.Where(t => t.no_faktur == strNoFaktur).ToList());
}
}
catch (Exception ex)
{
Helpers.Generic.MsgError(ex.Message);
}
return(new List <Models.detail_penjualan>());
}
public static Models.karyawan check_login(string username)
{
var result = new Models.karyawan();
try
{
using (var db = new Models.sim_penjualanContext())
{
result = db.karyawans.Where(t => t.username == username).SingleOrDefault();
}
}
catch (Exception ex)
{
Helpers.Generic.MsgError(ex.Message);
}
return(result);
}
public static Models.penjualan get_detail_penjualan(string strNoFaktur)
{
try
{
using (var db = new Models.sim_penjualanContext())
{
return(db.penjualans
.Include(t => t.konsumen)
.Include(t => t.karyawan)
.Where(t => t.no_faktur == strNoFaktur && t.status == 0).SingleOrDefault());
}
}
catch (Exception ex)
{
Helpers.Generic.MsgError(ex.Message);
}
return(new Models.penjualan());
}
public static bool delete(Models.konsumen data)
{
bool result = false;
try
{
using (var db = new Models.sim_penjualanContext())
{
db.Entry(data).State = EntityState.Deleted;
db.SaveChanges();
result = true;
}
}
catch (Exception ex)
{
Helpers.Generic.MsgError(ex.Message);
}
return(result);
}
public static bool insert(Models.penjualan data)
{
bool result = false;
try
{
using (var db = new Models.sim_penjualanContext())
{
db.Entry(data).State = System.Data.Entity.EntityState.Added;
db.SaveChanges();
result = true;
}
}
catch (Exception ex)
{
Helpers.Generic.MsgError(ex.Message);
}
return(result);
}
public static List <Models.pembayaran> search(string param)
{
var result = new List <Models.pembayaran>();
try
{
using (var db = new Models.sim_penjualanContext())
{
result = db.pembayarans
.Include(t => t.penjualan)
.Include(t => t.penjualan.konsumen)
.Where(t => t.no_faktur.Contains(param) || t.penjualan.konsumen.kd_konsumen.Contains(param) || t.penjualan.konsumen.nama.Contains(param))
.ToList();
}
}
catch (Exception ex)
{
Helpers.Generic.MsgError(ex.Message);
}
return(result);
}
public static DataSet1 get_report(DateTime tgl1, DateTime tgl2)
{
DataSet1 result = new DataSet1();
try
{
using (var db = new Models.sim_penjualanContext())
{
db.Database.Connection.Open();
using (var cmd = db.Database.Connection.CreateCommand())
{
cmd.CommandText = string.Format("select * from penjualan where date(tgl) between '{0}' and '{1}' order by kd_konsumen asc", tgl1.ToString("yyyy-MM-dd"), tgl2.ToString("yyyy-MM-dd"));
cmd.CommandType = System.Data.CommandType.Text;
using (var reader = cmd.ExecuteReader())
{
var dt = new System.Data.DataTable();
dt.Load(reader);
result.Tables["penjualan"].Merge(dt);
}
}
using (var cmd = db.Database.Connection.CreateCommand())
{
cmd.CommandText = "select * from konsumen";
cmd.CommandType = System.Data.CommandType.Text;
using (var reader = cmd.ExecuteReader())
{
var dt = new System.Data.DataTable();
dt.Load(reader);
result.Tables["konsumen"].Merge(dt);
}
}
}
}
catch (Exception ex)
{
Helpers.Generic.MsgError(ex.Message);
}
return(result);
}
public static bool insert(Models.pembayaran data)
{
bool result = false;
try
{
using (var db = new Models.sim_penjualanContext())
{
using (var transaction = db.Database.BeginTransaction())
{
try
{
List <Models.detail_penjualan> detail = db.detail_penjualan.Where(t => t.no_faktur == data.no_faktur).ToList();
foreach (var dt in detail)
{
var barang = db.barangs.Where(t => t.kd_barang == dt.kd_barang).Single();
barang.stok -= dt.jumlah;
db.Entry(barang).State = EntityState.Modified;
}
db.Entry(data).State = EntityState.Added;
db.SaveChanges();
result = true;
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
}
}
}
}
catch (Exception ex)
{
Helpers.Generic.MsgError(ex.Message);
}
return(result);
}
public static List <Models.konsumen> search(string kolom, string param)
{
kolom = kolom.ToLower();
using (var db = new Models.sim_penjualanContext())
{
if (kolom.Contains("kode"))
{
return(db.konsumen.Where(t => t.kd_konsumen.Contains(param)).ToList());
}
else if (kolom.Contains("nama"))
{
return(db.konsumen.Where(t => t.nama.Contains(param)).ToList());
}
else if (kolom.Contains("alamat"))
{
return(db.konsumen.Where(t => t.alamat.ToString().Contains(param)).ToList());
}
else
{
return(db.konsumen.Where(t => t.email.Contains(param)).ToList());
}
}
}
private void FrmDashboard_Load(object sender, EventArgs e)
{
DataTable dt_pendapatan1 = new DataTable();
dt_pendapatan1.Columns.Add("tgl", typeof(DateTime));
dt_pendapatan1.Columns.Add("total", typeof(int));
DataTable dt_pendapatan2 = new DataTable();
dt_pendapatan2.Columns.Add("tgl", typeof(DateTime));
dt_pendapatan2.Columns.Add("total", typeof(int));
DataTable dt_barang = new DataTable();
dt_barang.Columns.Add("nama", typeof(string));
dt_barang.Columns.Add("jumlah", typeof(int));
DataTable dt_karyawan = new DataTable();
dt_karyawan.Columns.Add("nama", typeof(string));
dt_karyawan.Columns.Add("total", typeof(int));
using (var db = new Models.sim_penjualanContext())
{
db.Database.Connection.Open();
using (var cmd = db.Database.Connection.CreateCommand())
{
cmd.CommandText = "SELECT SUM(total_bayar) AS total,DATE(tgl) AS tgl FROM pembayaran WHERE DATE(tgl) BETWEEN (DATE(NOW()) - INTERVAL 7 DAY) AND DATE(NOW()) GROUP BY DATE(tgl)";
cmd.CommandType = CommandType.Text;
using (var reader = cmd.ExecuteReader())
{
dt_pendapatan1.Load(reader);
}
}
using (var cmd = db.Database.Connection.CreateCommand())
{
cmd.CommandText = string.Format("SELECT SUM(total_bayar) AS total,DATE(tgl) AS tgl FROM pembayaran WHERE month(tgl)= '{0}' and year(tgl) = '{1}' GROUP BY DATE(tgl)", DateTime.Now.Month, DateTime.Now.Year);
cmd.CommandType = CommandType.Text;
using (var reader = cmd.ExecuteReader())
{
dt_pendapatan2.Load(reader);
}
}
using (var cmd = db.Database.Connection.CreateCommand())
{
cmd.CommandText = "select barang.`nama`, sum(detail_penjualan.`jumlah`) as jumlah FROM barang join detail_penjualan on barang.`kd_barang` = detail_penjualan.`kd_barang` group by barang.`kd_barang`";
cmd.CommandType = CommandType.Text;
using (var reader = cmd.ExecuteReader())
{
dt_barang.Load(reader);
}
}
using (var cmd = db.Database.Connection.CreateCommand())
{
cmd.CommandText = "SELECT karyawan.`nama`, SUM(pembayaran.total_bayar) AS total FROM karyawan JOIN pembayaran ON karyawan.`kd_karyawan` = pembayaran.`kd_karyawan` GROUP BY karyawan.`nama` limit 0,5";
cmd.CommandType = CommandType.Text;
using (var reader = cmd.ExecuteReader())
{
dt_karyawan.Load(reader);
}
}
}
chart_pendapatan_1.DataSource = dt_pendapatan1;
chart_pendapatan2.DataSource = dt_pendapatan2;
chart_persentase.DataSource = dt_barang;
chart_karyawan.DataSource = dt_karyawan;
}
