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