Beispiel #1
0
        private void deleteKendaraan()
        {
            cQuery    qr = new cQuery();
            cDatabase db = new cDatabase();

            db.insertDataSqlite(qr.qDeleteKendaraan());
        }
Beispiel #2
0
        private void copyTmpToKendaraan()
        {
            cKoneksi koneksi = new cKoneksi();

            using (var conn = new SQLiteConnection(koneksi.LokasiSqlite()))//koneksi ke sqlite
            {
                try
                {
                    conn.Open();//buka koneksi
                    using (var cmd = new SQLiteCommand(conn))
                    {
                        using (var transaction = conn.BeginTransaction())
                        {
                            cQuery qr = new cQuery();
                            cmd.CommandText = qr.qCopyTmpToKendaraan();//"INSERT INTO kendaraan SELECT * FROM kendaraanTmp";//query copy table
                            cmd.ExecuteNonQuery();

                            transaction.Commit();
                        }
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
                finally
                {
                    conn.Close(); //tutup koneksi
                    conn.Dispose();
                }
            }
        }
Beispiel #3
0
        private void deleteOperator()
        {
            cQuery    qr = new cQuery();
            cDatabase db = new cDatabase();

            db.insertDataSqlite(qr.qDeleteOperator());
        }
Beispiel #4
0
        private void isiListBox()
        {
            LbxUsername.Items.Clear();

            cDatabase db = new cDatabase();
            cQuery    qr = new cQuery();

            string queryUse = string.Empty;

            if (rbBiroUmum.Checked == true)
            {
                queryUse = qr.qSelectUserOpBu();
            }
            else if (rbAkademik.Checked == true)
            {
                queryUse = qr.qSelectUserOpAkademik();
            }

            DataTable dtUser = db.selectData(queryUse, '1');


            if (dtUser.Rows.Count != 0)
            {
                foreach (DataRow row in dtUser.Rows)
                {
                    LbxUsername.Items.Add(row["username"]);
                }
            }
            dtUser.Clear();
            dtUser.Dispose();
        }
Beispiel #5
0
        private void LbxUsername_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (LbxUsername.SelectedItem.ToString() != null)
            {
                string username = LbxUsername.SelectedItem.ToString();

                cDatabase db = new cDatabase();
                cQuery    qr = new cQuery();

                DataTable dtUser = db.selectData(qr.qSelectUserWhereUsername(username), '1');

                if (dtUser.Rows.Count != 0)
                {
                    foreach (DataRow row in dtUser.Rows)
                    {
                        txt_username.Text       = row["username"].ToString();
                        txt_fullname.Text       = row["fullname"].ToString();
                        txt_password.Text       = row["password"].ToString();
                        combo_role.SelectedItem = row["role"].ToString();

                        if (row["aplication"].ToString() == "Biro Umum")
                        {
                            rbKantor.Checked = true;
                        }
                        else if (row["aplication"].ToString() == "Gate")
                        {
                            rbGate.Checked = true;
                        }
                    }
                }
            }
        }
Beispiel #6
0
        private void deleteMahasiswaTmp()
        {
            cQuery    qr = new cQuery();
            cDatabase db = new cDatabase();

            //string query = "delete from penggunaTmp where kategori = 1"; //kategori 1=mahasiwa
            db.insertDataSqlite(qr.qDeleteMhsTmp());
        }
        private DataTable selectFrHistoryRfidMhsTerakirbyProdi(string prodi)
        {
            DataTable dt = null;
            cDatabase db = new cDatabase();
            cQuery    qr = new cQuery();

            dt = db.selectData(qr.qSelectLastHistoryRfidByProdi(prodi), '3');//konek ke tabel history_rfid
            return(dt);
        }
        private DataTable selectRfidPegTerakir()
        {
            DataTable dt = null;
            cDatabase db = new cDatabase();
            cQuery    qr = new cQuery();

            dt = db.selectData(qr.qSelectLastRfidPegawai(), '4'); //4 = konek ke payroll_web
            return(dt);
        }
        private DataTable selectHistoryRfidTerakirPegawai()
        {
            DataTable dt = null;
            cDatabase db = new cDatabase();
            cQuery    qr = new cQuery();

            dt = db.selectData(qr.qSelectLastHistoryRfidPegawai(), '3');//konek ke tabel history_rfid
            return(dt);
        }
        private DataTable selectRfidMhsTerakirByProdi(string prodi)
        {
            DataTable dt = null;
            cDatabase db = new cDatabase();
            cQuery    qr = new cQuery();

            dt = db.selectData(qr.qSelectLastRfidMhsByProdi(prodi), '2');
            return(dt);
        }
Beispiel #11
0
        private string SyncMahasiswa(bool firstSync)
        {
            string err = string.Empty;    //tampungan error message

            deleteMahasiswaTmp();         //hapus tmp

            err = downloadMahasiswaTmp(); //insert tmp

            //cek fisrtSync
            if (firstSync == true)
            {
                if (err != string.Empty)
                { //jika terjadi error
                    return(err);
                }
            }

            cQuery qr = new cQuery();

            //string qrTmp = "SELECT nik FROM penggunaTmp WHERE kategori = 1 LIMIT 1";//cek data mhs di tmp
            if (cekDataSqlLIte(qr.qSelectMhsTmpLimit1(), false) == true) //cek tabel tmp
            {                                                            //jika ada datanya
               //pindah tabel tmp ke kendaraan

                deleteMahasiswa();
                copyTmpToMahasiswa();

                if (ProgressBarSinkron.Value == ProgressBarSinkron.Maximum)//jika sudah selesai tampilkan download complete
                {
                    if (LbDownload.InvokeRequired)
                    {
                        LbDownload.BeginInvoke(
                            new Action(() =>
                        {
                            LbDownload.Text = "Download Complete";
                        }
                                       ));
                    }
                }

                //Cek firstSync
                if (firstSync == false)
                {
                    MessageBox.Show("" + ProgressBarSinkron.Value + " Data Berhasil di Download", "BSI UMY", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
            }
            else
            {
                MessageBox.Show("Synchronize Failed", "BSI UMY", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                //ProgressBarSinkron.Visible = false;// hiden panel progressbar
            }

            return(err);
        }
Beispiel #12
0
        private DataTable cekTransaksiMhs(string nim)
        {
            cDatabase db = new cDatabase();
            cQuery    qr = new cQuery();

            DataTable dtStatusMhs = null;

            //Cek dulu data yang mau diUpload apakah sudah ada atau belum
            dtStatusMhs = db.selectData(qr.qSelectTop1StatusMhsWhereStudId(nim), '2'); //2 = user ktm

            return(dtStatusMhs);
        }
Beispiel #13
0
        //true = ada data
        //false = tidak ada data
        private bool cekTransaksiUploadMhs()
        {
            DataTable dt = null;
            cQuery    qr = new cQuery();
            cDatabase db = new cDatabase();

            dt = db.selectDataSqlite(qr.qSelectTransaksiMhsStatus0Limit1()); // ambil data dari sqlite
            if (dt.Rows.Count != 0)                                          //jika ada data
            {
                return(true);
            }
            else
            {
                return(false);
            }
        }
        private void SearchAmbilKtm() //select dari simak
        {
            SqlConnection conn = null;

            try
            {
                string konekKe  = null;
                string queryUse = null;

                cQuery   qr      = new cQuery();
                cKoneksi koneksi = new cKoneksi();

                if (rbMahasiswa.Checked == true)
                {
                    konekKe  = "2";//2 = ke simak
                    queryUse = qr.qSearchMhsAmbilKtm(nik, nama);
                }
                else
                {
                    konekKe  = "4";//4 = ke payroll
                    queryUse = qr.qSearchAmbilKartuPeg(nik, nama);
                }

                conn = new SqlConnection(koneksi.konekMsSql(Convert.ToChar(konekKe)));
                SqlCommand cmd = null;
                //---------------------------------
                cmd = new SqlCommand(queryUse, conn);
                //---------------------------------
                conn.Open();
                cmd.ExecuteNonQuery();
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet        ds = new DataSet();
                da.Fill(ds, "Authors_table");
                //---------------------
                dataGridView1.DataSource = ds;
                dataGridView1.DataMember = "Authors_table";
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
        }
        private void UpdateRfidPegawai(string idPegawai, string lastHeksa)
        {
            Int32 sequence = Int32.Parse(lastHeksa, System.Globalization.NumberStyles.HexNumber);//dari heksa terakir convert ke int

            sequence++;
            string heksa = String.Format("{0:X7}", sequence); //heksa selanjutnya => utk pegawai heksa ada 7 digit

            DataTable     dt      = null;
            cKoneksi      koneksi = new cKoneksi();
            SqlConnection conn    = new SqlConnection(koneksi.konekMsSql('4'));

            conn.Open();

            try
            {
                string RFID = "2" + heksa + "";

                SqlCommand cmd = null;
                cQuery     qr  = new cQuery();

                cmd = new SqlCommand(qr.qSelectIdPegawaiByRfid(RFID), conn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                dt = new DataTable();
                da.Fill(dt);

                if (dt.Rows.Count > 0)
                {
                    MessageBox.Show("Proses Generate Gagal");
                    return;
                }

                cmd = new SqlCommand(qr.qUpdateRfidPegByID(), conn);
                cmd.Parameters.AddWithValue("@rfid", RFID);
                cmd.Parameters.AddWithValue("@id_pegawai", idPegawai);
                cmd.ExecuteNonQuery();
            }
            finally
            {
                conn.Close();
                conn.Dispose();
                dt.Clear();
                dt.Dispose();
            }
        }
        private void UpdateRfidMhs(string NIM, string prodi, string lastHeksa)
        {
            Int32 sequence = Int32.Parse(lastHeksa, System.Globalization.NumberStyles.HexNumber);//dari heksa terakir convert ke int

            sequence++;
            string heksa = String.Format("{0:X4}", sequence); //heksa selanjutnya

            DataTable     dt      = null;
            cKoneksi      koneksi = new cKoneksi();
            SqlConnection conn    = new SqlConnection(koneksi.konekMsSql('2'));

            conn.Open();

            try
            {
                string RFID = "1" + prodi + "" + heksa + "";

                SqlCommand cmd = null;
                cQuery     qr  = new cQuery();

                cmd = new SqlCommand(qr.qSelectNimByRfid(RFID), conn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                dt = new DataTable();
                da.Fill(dt);

                if (dt.Rows.Count > 0)
                {
                    MessageBox.Show("Proses Generate Gagal");
                    return;
                }

                cmd = new SqlCommand(qr.qUpdateRfidMhsByNim(), conn);
                cmd.Parameters.AddWithValue("@RFID", RFID);
                cmd.Parameters.AddWithValue("@STUDENTID", NIM);
                cmd.ExecuteNonQuery();
            }
            finally
            {
                conn.Close();
                conn.Dispose();
                dt.Clear();
                dt.Dispose();
            }
        }
        private void tampilMhs(string NIM)
        {
            cDatabase db = new cDatabase();
            cQuery    qr = new cQuery();

            DataTable dt = db.selectData(qr.qSelectNamaRfidMhsByNim(NIM), '2');

            if (dt.Rows.Count != 0)
            {
                foreach (DataRow rowMhs in dt.Rows)
                {
                    txt_nik.Text  = rowMhs["STUDENTID"].ToString();
                    txt_nama.Text = rowMhs["FULLNAME"].ToString();
                    txtRfid.Text  = rowMhs["RFID"].ToString();
                }
            }
            dt.Clear();
            dt.Dispose();
        }
        private void tampilPeg(string ID_PEG)
        {
            cDatabase db = new cDatabase();
            cQuery    qr = new cQuery();

            DataTable dt = db.selectData(qr.qSelectNamaRfidPegByIdPeg(ID_PEG), '4');//4 = konek ke 10.0.1.64 db payroll_web

            if (dt.Rows.Count != 0)
            {
                foreach (DataRow rowMhs in dt.Rows)
                {
                    txt_nik.Text     = rowMhs["nik"].ToString();
                    txt_nama.Text    = rowMhs["nama"].ToString();
                    txtRfid.Text     = rowMhs["rfid"].ToString();
                    lbIdPegawai.Text = rowMhs["id_pegawai"].ToString();
                }
            }
            dt.Clear();
            dt.Dispose();
        }
        private void SearchAmbilKtm()
        {
            cQuery    qr = new cQuery();
            cDatabase db = new cDatabase();

            DataTable dtSearch = null;

            if (rbMahasiswa.Checked == true)
            {
                dtSearch = db.selectData(qr.qSearchMhsByNImNama(txt_cari_nik.Text, txt_cari_nama.Text), '2');
            }
            else
            {
                dtSearch = db.selectData(qr.qSearchPegByNikNama(txt_cari_nik.Text, txt_cari_nama.Text), '4');
            }

            cControl cnt = new cControl();

            foreach (DataRow rowSearch in dtSearch.Rows)
            {
                string[] row = null;

                if (rbMahasiswa.Checked == true)
                {
                    row = new string[] { rowSearch["STUDENTID"].ToString(), rowSearch["STUDENTID"].ToString(), rowSearch["FULLNAME"].ToString(), rowSearch["RFID"].ToString() };
                }
                else
                {
                    row = new string[] { rowSearch["id_pegawai"].ToString(), rowSearch["nik"].ToString(), rowSearch["nama"].ToString(), rowSearch["rfid"].ToString() };
                }

                DGList.Rows.Add(row);
            }

            dtSearch.Clear();
            dtSearch.Dispose();
        }
Beispiel #20
0
        private string loginMsSql()
        {
            cKoneksi koneksi = new cKoneksi();
            string   role    = string.Empty;

            var        con = new SqlConnection(koneksi.konekMsSql('1'));//// konek ke 10.0.1.64 db parkir
            cQuery     qr  = new cQuery();
            SqlCommand cmd = new SqlCommand(qr.qSelectUserLoginMsSql(), con);

            cmd.Parameters.AddWithValue("@username", this.textBox_username.Text);
            cmd.Parameters.AddWithValue("@password", this.textBox_pass.Text);

            try
            {
                con.Open();
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    if (dr.HasRows == true)
                    {
                        role = dr.GetString(1);
                        cVarGlobal.isAplication = dr.GetString(2);//set hak akses aplikasi
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                role = string.Empty;
            }
            finally
            {
                con.Close();
                con.Dispose();
            }
            return(role);
        }
        private void cariMhs()
        {
            cQuery    qr       = new cQuery();
            cDatabase db       = new cDatabase();
            DataTable dtSearch = db.selectData(qr.qSearchMhsByNImNama(txt_cari_nim.Text, txt_cari_nama.Text), '2');
            cControl  cnt      = new cControl();

            foreach (DataRow rowSearch in dtSearch.Rows)
            {
                string status    = string.Empty;
                String TglBlokir = string.Empty;
                //jika kode rfid kososng, cek dari table history blokir
                if (rowSearch["RFID"].ToString().Trim() == string.Empty)
                {
                    DataTable dtHistory = db.selectData(qr.qSearchHistoryByIdPenggunaTop1(rowSearch["STUDENTID"].ToString()), '3');
                    if (dtHistory.Rows.Count != 0)//jika ada data di dlm history blokir = terblokir
                    {
                        status = "Terblokir";
                    }
                    else
                    {//jika tidak ada data di dlm history blokir = Tidak Aktif
                        status = "Tidak Aktif";
                    }
                }
                else
                {//jika ada data rfidnya = akif
                    status = "Aktif";
                }

                string[] row = new string[] { rowSearch["STUDENTID"].ToString(), rowSearch["STUDENTID"].ToString(), rowSearch["FULLNAME"].ToString(), rowSearch["RFID"].ToString(), status };
                DGList.Rows.Add(row);
            }

            dtSearch.Clear();
            dtSearch.Dispose();
        }
Beispiel #22
0
        private void createUser()
        {
            string        errInsert = string.Empty;
            cKoneksi      koneksi   = new cKoneksi();
            SqlConnection conn      = new SqlConnection(koneksi.konekMsSql('1'));//koneksi ke 64 db parkir

            cQuery     qr  = new cQuery();
            SqlCommand cmd = null;

            string aplication = string.Empty;

            if (rbAkademik.Checked == true)
            {
                aplication = "Akademik";
            }
            else if (rbBiroUmum.Checked == true)
            {
                if (rbGate.Checked == true)
                {
                    aplication = "Gate";
                }
                else if (rbKantor.Checked == true)
                {
                    aplication = "Biro Umum";
                }
            }

            try
            {
                conn.Open();

                cmd = new SqlCommand(qr.qInsertUser(), conn);

                cmd.Parameters.AddWithValue("@username", txt_username.Text);
                cmd.Parameters.AddWithValue("@fullname", txt_fullname.Text);
                cmd.Parameters.AddWithValue("@password", txt_password.Text);
                cmd.Parameters.AddWithValue("@role", combo_role.Text);
                cmd.Parameters.AddWithValue("@aplication", aplication);

                cmd.ExecuteNonQuery();//insert ke sql
            }
            catch (Exception ex)
            {
                errInsert = ex.Message;
                MessageBox.Show(errInsert, "BSI UMY", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }

            if (errInsert == string.Empty)
            {
                label_message.Text = "Registrasi User Berhasil Disimpan";
            }

            //cKoneksi koneksi = new cKoneksi();
            //cQuery qr = new cQuery();
            //using (var conn = new SqlConnection(koneksi.konekMsSql('1')))//koneksi ke 64 db parkir
            //{
            //    try
            //    {

            //        conn.Open();//buka koneksi
            //        using (var cmd = new SqlCommand(conn))
            //        {
            //            using (var transaction = conn.BeginTransaction())
            //            {
            //                cmd.CommandText = qr.qInsertUser(); // " INSERT INTO user (username ,fullname ,password ,role) VALUES (@username,@fullname,@password,@role)";
            //                    cmd.Parameters.Clear();
            //                    cmd.Prepare();
            //                    cmd.Parameters.AddWithValue("@username", txt_username.Text);
            //                    cmd.Parameters.AddWithValue("@fullname", txt_fullname.Text);
            //                    cmd.Parameters.AddWithValue("@password", txt_password.Text);
            //                    cmd.Parameters.AddWithValue("@role", combo_role.Text);
            //                    cmd.ExecuteNonQuery();

            //                transaction.Commit();
            //            }
            //        }
            //    }
            //    catch
            //    {
            //        MessageBox.Show("Username sudah dipakai, coba yang lain");
            //    }
            //    finally
            //    {
            //        conn.Close(); //tutup koneksi
            //        conn.Dispose();
            //    }

            //}
        }
Beispiel #23
0
        private string downloadOperatorTmp()
        {
            string err = string.Empty;

            try
            {
                cQuery    qr = new cQuery();
                cDatabase db = new cDatabase();
                DataTable dt = null;
                dt = db.selectData(qr.qSelectUserOpAkdk(), '1'); // ambil data dari mssql
                if (dt.Rows.Count != 0)                          //jika ada data
                {
                    //===inisialisasi progres bar======
                    if (ProgressBarSinkron.InvokeRequired)
                    {
                        ProgressBarSinkron.BeginInvoke(
                            new Action(() =>
                        {
                            ProgressBarSinkron.Style   = ProgressBarStyle.Blocks;
                            ProgressBarSinkron.Value   = 0;
                            ProgressBarSinkron.Minimum = 0;
                            ProgressBarSinkron.Maximum = dt.Rows.Count;
                        }
                                       ));
                    }

                    //=================================

                    cKoneksi koneksi = new cKoneksi();

                    using (var conn = new SQLiteConnection(koneksi.LokasiSqlite()))//koneksi ke sqlite
                    {
                        try
                        {
                            conn.Open();//buka koneksi
                            using (var cmd = new SQLiteCommand(conn))
                            {
                                using (var transaction = conn.BeginTransaction())
                                {
                                    //looping insert
                                    foreach (DataRow row in dt.Rows)
                                    {
                                        cmd.CommandText = qr.qInsertUserTmp();//" INSERT INTO userTmp (username, fullname, password, role) values(@username, @fullname, @password, @role)";
                                        cmd.Parameters.Clear();
                                        cmd.Prepare();
                                        cmd.Parameters.AddWithValue("@username", row["username"]);
                                        cmd.Parameters.AddWithValue("@fullname", row["fullname"]);
                                        cmd.Parameters.AddWithValue("@password", row["password"]);
                                        cmd.Parameters.AddWithValue("@aplication", row["aplication"]);
                                        cmd.Parameters.AddWithValue("@role", row["role"]);
                                        cmd.ExecuteNonQuery();

                                        //===proses progres bar======
                                        if (ProgressBarSinkron.InvokeRequired)
                                        {
                                            ProgressBarSinkron.BeginInvoke(
                                                new Action(() =>
                                            {
                                                ProgressBarSinkron.Increment(1);
                                            }
                                                           ));
                                        }

                                        //ProgressBarSinkron.Increment(1);
                                        //===========================
                                    }

                                    transaction.Commit();
                                }
                            }
                        }
                        catch (Exception ex)
                        {
                            MessageBox.Show(ex.Message);
                        }
                        finally
                        {
                            conn.Close(); //tutup koneksi
                            conn.Dispose();
                        }
                    }

                    ////===tutup progres bar======
                    //if (ProgressBarSinkron.Value == ProgressBarSinkron.Maximum)//jika sudah selesai tampilkan download complete
                    //{ LblProgressBar.Text = "Download Complete"; }
                    //MessageBox.Show("" + ProgressBarSinkron.Value + " Data Berhasil di Download");
                    //panelProgressBar.Visible = false;// hiden panel progressbar
                    ////==========================
                }

                //clear datatable
                dt.Clear();
                dt.Dispose();
                //================
            }
            catch (Exception ex)
            {
                err = ex.Message;
                //ProgressBarSinkron.Visible = false;
                MessageBox.Show(ex.Message);
            }
            return(err);
        }
Beispiel #24
0
        private string SyncKendaraan(bool firstSync)
        {
            string err = string.Empty;    //tampungan error message

            deleteKendaraanTmp();         //hapus tmp

            err = downloadKendaraanTmp(); //insert tmp

            //cek fisrtSync
            if (firstSync == true)
            {
                if (err != string.Empty)
                { //jika terjadi error
                    return(err);
                }
            }

            cQuery qr = new cQuery();

            //string qrTmp = "SELECT last_update FROM kendaraanTmp order by last_update desc LIMIT 1";//cek data di tmp
            if (cekDataSqlLIte(qr.qSelectKendaraanTmpLimit1(), false) == true) //cek tabel tmp
            {                                                                  //jika ada datanya
               //pindah tabel tmp ke kendaraan

                deleteKendaraan();
                copyTmpToKendaraan();

                if (ProgressBarSinkron.Value == ProgressBarSinkron.Maximum)//jika sudah selesai tampilkan download complete
                {
                    if (LbDownload.InvokeRequired)
                    {
                        LbDownload.BeginInvoke(
                            new Action(() =>
                        {
                            LbDownload.Text = "Download Complete";
                        }
                                       ));
                    }
                }

                //cek firstSync
                if (firstSync == false)
                {
                    MessageBox.Show("" + ProgressBarSinkron.Value + " Data Berhasil di Download", "BSI UMY", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }

                if (LbDownload.InvokeRequired)
                {
                    LbDownload.BeginInvoke(
                        new Action(() =>
                    {
                        LbDownload.Text = string.Empty;
                    }
                                   ));
                }
            }
            else
            {
                MessageBox.Show("Synchronize Failed", "BSI UMY", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }

            return(err);
        }
Beispiel #25
0
        private string downloadKendaraanTmp()
        {
            string err = string.Empty;

            try
            {
                cQuery    qr = new cQuery();
                cDatabase db = new cDatabase();
                DataTable dt = null;
                dt = db.selectData(qr.qSelectKendaraan(), '1'); // ambil data dari mssql
                if (dt.Rows.Count != 0)                         //jika ada data
                {
                    //===inisialisasi progres bar======
                    if (ProgressBarSinkron.InvokeRequired)
                    {
                        ProgressBarSinkron.BeginInvoke(
                            new Action(() =>
                        {
                            ProgressBarSinkron.Style   = ProgressBarStyle.Blocks;
                            ProgressBarSinkron.Value   = 0;
                            ProgressBarSinkron.Minimum = 0;
                            ProgressBarSinkron.Maximum = dt.Rows.Count;
                        }
                                       ));
                    }
                    //=================================

                    cKoneksi koneksi = new cKoneksi();

                    using (var conn = new SQLiteConnection(koneksi.LokasiSqlite()))//koneksi ke sqlite
                    {
                        try
                        {
                            conn.Open();//buka koneksi
                            using (var cmd = new SQLiteCommand(conn))
                            {
                                using (var transaction = conn.BeginTransaction())
                                {
                                    //looping insert
                                    foreach (DataRow row in dt.Rows)
                                    {
                                        cmd.CommandText = qr.qInsertKendaraanTmp();
                                        cmd.Parameters.Clear();
                                        cmd.Prepare();
                                        cmd.Parameters.AddWithValue("@id", row["id_kendaraan"]);
                                        cmd.Parameters.AddWithValue("@id_pengguna", row["pengguna"]);
                                        cmd.Parameters.AddWithValue("@no_kendaraan", row["tnkb"]);
                                        cmd.Parameters.AddWithValue("@pemilik", row["pemilik"]);
                                        cmd.Parameters.AddWithValue("@merek", row["merek"]);
                                        cmd.Parameters.AddWithValue("@jenis", row["id_jenis_kendaraan"]); //jenis dilangsung aja
                                        cmd.Parameters.AddWithValue("@last_update", row["last_update"]);  //last Update
                                        cmd.ExecuteNonQuery();

                                        //===proses progres bar======
                                        if (ProgressBarSinkron.InvokeRequired)
                                        {
                                            ProgressBarSinkron.BeginInvoke(
                                                new Action(() =>
                                            {
                                                ProgressBarSinkron.Increment(1);
                                            }
                                                           ));
                                        }
                                        //===========================
                                    }

                                    transaction.Commit();
                                }
                            }
                        }
                        catch (Exception ex)
                        {
                            MessageBox.Show(ex.Message);
                        }
                        finally
                        {
                            conn.Close(); //tutup koneksi
                            conn.Dispose();
                        }
                    }

                    ////===tutup progres bar======
                    //if (ProgressBarSinkron.Value == ProgressBarSinkron.Maximum)//jika sudah selesai tampilkan download complete
                    //{ LblProgressBar.Text = "Download Complete"; }
                    //MessageBox.Show("" + ProgressBarSinkron.Value + " Data Berhasil di Download");
                    //panelProgressBar.Visible = false;// hiden panel progressbar
                    ////==========================
                }

                //clear datatable
                dt.Clear();
                dt.Dispose();
                //================
            }
            catch (Exception ex)
            {
                err = ex.Message;
                //ProgressBarSinkron.Visible = false;
                MessageBox.Show(ex.Message);
            }
            return(err);
        }
Beispiel #26
0
        private string downloadMahasiswaTmp()
        {
            string err = string.Empty;

            try
            {
                cQuery    qr = new cQuery();
                cDatabase db = new cDatabase();
                DataTable dt = null;
                dt = db.selectData(qr.qSelectMhs(), '2'); // '2' = ambil data dari 10.0.1.61 db s1makumyny4
                if (dt.Rows.Count != 0)                   //jika ada data
                {
                    //===inisialisasi progres bar======
                    if (ProgressBarSinkron.InvokeRequired)
                    {
                        ProgressBarSinkron.BeginInvoke(
                            new Action(() =>
                        {
                            ProgressBarSinkron.Value   = 0;
                            ProgressBarSinkron.Minimum = 0;
                            ProgressBarSinkron.Maximum = dt.Rows.Count;
                        }
                                       ));
                    }
                    //=================================

                    cKoneksi koneksi = new cKoneksi();
                    using (var conn = new SQLiteConnection(koneksi.LokasiSqlite()))//koneksi ke sqlite
                    {
                        try
                        {
                            conn.Open();//buka koneksi
                            using (var cmd = new SQLiteCommand(conn))
                            {
                                using (var transaction = conn.BeginTransaction())
                                {
                                    //looping insert
                                    foreach (DataRow row in dt.Rows)
                                    {
                                        cmd.CommandText = qr.qInsertPenggunaTmp();//"insert into penggunaTmp (id,nik,nama,rfid,kategori) values (@id,@nik,@nama,@rfid,@kategori)";
                                        cmd.Parameters.Clear();
                                        cmd.Prepare();
                                        cmd.Parameters.AddWithValue("@id", row["StudentID"]);  //kenapa ID diisi sama kayak nik??
                                        cmd.Parameters.AddWithValue("@nik", row["StudentID"]); //kenapa ID diisi sama kayak nik??
                                        cmd.Parameters.AddWithValue("@nama", row["fullname"]);
                                        cmd.Parameters.AddWithValue("@rfid", row["rfid"]);
                                        cmd.Parameters.AddWithValue("@kategori", 1); // 1 = kategori mahasiswa
                                        cmd.ExecuteNonQuery();

                                        //===proses progres bar======
                                        if (ProgressBarSinkron.InvokeRequired)
                                        {
                                            ProgressBarSinkron.BeginInvoke(
                                                new Action(() =>
                                            {
                                                ProgressBarSinkron.Increment(1);
                                            }
                                                           ));
                                        }
                                        //===========================
                                    }

                                    transaction.Commit();
                                }
                            }
                        }
                        catch (Exception ex)
                        {
                            MessageBox.Show(ex.Message);
                        }
                        finally
                        {
                            conn.Close(); //tutup koneksi
                            conn.Dispose();
                        }
                    }

                    ////===tutup progres bar======
                    //if (ProgressBarSinkron.Value == ProgressBarSinkron.Maximum)//jika sudah selesai tampilkan download complete
                    //{ LblProgressBar.Text = "Download Complete"; }
                    //MessageBox.Show("" + ProgressBarSinkron.Value + " Data Berhasil di Download");
                    //panelProgressBar.Visible = false;// hiden panel progressbar
                    ////==========================
                }

                //clear datatable
                dt.Clear();
                dt.Dispose();
                //================
            }
            catch (Exception ex)
            {
                err = ex.Message;
                //ProgressBarSinkron.Visible = false;
                MessageBox.Show(ex.Message);
            }
            return(err);
        }
        private void generateRfidPegawai() //harusnya bukan nik tapi id_pegawai CEK LAGI DARI PROSES SEARCH!!!!!!!!!!!!!!!!!!!!
        {
            string idPegawai = lbIdPegawai.Text.Replace("'", "''");

            DataTable dtRfid = null;
            cDatabase db     = new cDatabase();
            cQuery    qr     = new cQuery();

            dtRfid = db.selectData(qr.qSelectRfidByIdPeg(idPegawai), '4');
            if (dtRfid.Rows.Count != 0)
            {
                string cekRfid = string.Empty;
                foreach (DataRow rowProdi in dtRfid.Rows)
                {
                    cekRfid = rowProdi["rfid"].ToString();
                }

                //jika ternyata sudah ada rfidnya, proses stop
                if (cekRfid != string.Empty)
                {
                    clearTampilan();
                    tampilPeg(idPegawai);

                    //txt_cari_nama.Text = string.Empty;
                    //txt_cari_nik.Text = string.Empty;

                    return;
                }

                //cek dari tabel pegawai
                DataTable lastHeksa = selectRfidPegTerakir(); //ambil heksa terakir dari pegawai
                string    heksa     = string.Empty;
                if (lastHeksa.Rows.Count != 0)
                {
                    foreach (DataRow rowHeksa in lastHeksa.Rows)
                    {
                        heksa = rowHeksa["heksa"].ToString();
                    }
                }

                //cek dari tabel history_rfid
                DataTable lastHeksaHis = selectHistoryRfidTerakirPegawai(); //ambil heksa terakir pegawai dari history
                string    heksaHis     = string.Empty;
                if (lastHeksaHis.Rows.Count != 0)
                {
                    foreach (DataRow rowHeksaHis in lastHeksaHis.Rows)
                    {
                        heksaHis = rowHeksaHis["heksa"].ToString();
                    }
                }

                string heksaUse = string.Empty;                        //heksa yang akan dipakai

                if (heksa != string.Empty && heksaHis != string.Empty) //jika di kedua tabel ditemukan heksa sebelumnya
                {
                    //bandingkan heksa dari tabel mhs dan history, nilainya paling tinggi yang dipakai
                    Int32 iheksa    = Int32.Parse(heksa, System.Globalization.NumberStyles.HexNumber);    //dari heksa terakir convert ke int
                    Int32 iheksaHis = Int32.Parse(heksaHis, System.Globalization.NumberStyles.HexNumber); //dari heksa terakir convert ke int

                    if (iheksa > iheksaHis)
                    {
                        heksaUse = heksa;
                    }
                    else if (iheksaHis > iheksa)
                    {
                        heksaUse = heksaHis;
                    }
                }
                else if (heksa != string.Empty && heksaHis == string.Empty) //jika hanya ditemukan dari tabel pegawai
                {
                    heksaUse = heksa;
                }
                else if (heksa == string.Empty && heksaHis != string.Empty) //jika hanya ditemukan dari tabel history_rfid
                {
                    heksaUse = heksaHis;
                }

                //=======================================================================
                if (heksaUse != string.Empty)//jika sudah ditemukan kode rfid terakir, lanjutkan sequence-nya
                {
                    UpdateRfidPegawai(idPegawai, heksaUse);
                }
                else
                { //jika belum ditemukan kode rfid sebelumnya, generate dari awal
                    UpdateRfidPegawai(idPegawai, "0000");
                }

                lastHeksa.Clear();
                lastHeksa.Dispose();
                lastHeksaHis.Clear();
                lastHeksaHis.Dispose();
            }

            clearTampilan();

            //txt_cari_nama.Text = string.Empty;
            //txt_cari_nik.Text = string.Empty;
            //DGList.Rows.Clear();
            //jmlRow.Text = string.Empty;

            tampilPeg(idPegawai);
        }
        private void blokir(string IdMhsOrPegawai, string rfid, string NamaMhs)
        {
            string        errInsert = string.Empty;
            cKoneksi      koneksi   = new cKoneksi();
            SqlConnection conn      = new SqlConnection(koneksi.konekMsSql('3'));//konek ke sql server historyBlokir

            cQuery     qr  = new cQuery();
            SqlCommand cmd = null;

            try
            {
                conn.Open();

                cmd = new SqlCommand(qr.qInsertHistory(), conn);

                cmd.Parameters.AddWithValue("@id_pengguna", IdMhsOrPegawai);
                cmd.Parameters.AddWithValue("@rfid", rfid);
                cmd.Parameters.AddWithValue("@nama", NamaMhs);

                cmd.ExecuteNonQuery();//insert ke sql
            }
            catch (Exception ex)
            {
                errInsert = ex.Message;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }

            //jika insert ke table history sukses, lanjut hapus rfid dari tblMhs
            if (errInsert == string.Empty)
            {
                string konekKe  = null;
                string queryUse = null;
                string paramUse = null;
                if (rbMahasiswa.Checked == true)//konek ke simak
                {
                    konekKe  = "2";
                    queryUse = qr.qUpdateRfidNullByNim();
                    paramUse = "@STUDENTID";
                }
                else if (rbPegawai.Checked == true)//konek ke payroll
                {
                    konekKe  = "4";
                    queryUse = qr.qUpdateRfidNullByIdPeg();
                    paramUse = "@id_pegawai";
                }

                SqlConnection connMhsOrPeg = new SqlConnection(koneksi.konekMsSql(Convert.ToChar(konekKe)));
                SqlCommand    cmdDelRfid   = null;
                try
                {
                    connMhsOrPeg.Open();

                    cmdDelRfid = new SqlCommand(queryUse, connMhsOrPeg);

                    cmdDelRfid.Parameters.AddWithValue(paramUse, IdMhsOrPegawai);
                    cmdDelRfid.ExecuteNonQuery();//insert ke sql
                }
                finally
                {
                    connMhsOrPeg.Close();
                    connMhsOrPeg.Dispose();
                }
            }
        }
Beispiel #29
0
        private void deleteUser()
        {
            string        errInsert = string.Empty;
            cKoneksi      koneksi   = new cKoneksi();
            SqlConnection conn      = new SqlConnection(koneksi.konekMsSql('1'));//koneksi ke 64 db parkir

            cQuery     qr  = new cQuery();
            SqlCommand cmd = null;

            try
            {
                conn.Open();

                cmd = new SqlCommand(qr.qDeleteUserWhereUsernameAndPass(), conn);
                cmd.Parameters.AddWithValue("@username", txt_username.Text);
                cmd.Parameters.AddWithValue("@password", txt_password.Text);

                cmd.ExecuteNonQuery();//insert ke sql
            }
            catch (Exception ex)
            {
                errInsert = ex.Message;
                MessageBox.Show(errInsert, "BSI UMY", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }

            if (errInsert == string.Empty)
            {
                label_message.Text = "User Berahasil dihapus";
            }

            //string err = string.Empty;
            //cQuery qr = new cQuery();
            //cKoneksi koneksi = new cKoneksi();
            //using (var conn = new SQLiteConnection(koneksi.LokasiSqlite()))//koneksi ke sqlite
            //{
            //    try
            //    {
            //        conn.Open();//buka koneksi
            //        using (var cmd = new SQLiteCommand(conn))
            //        {
            //            using (var transaction = conn.BeginTransaction())
            //            {
            //                cmd.CommandText = qr.qDeleteUserWhereUsernameAndPass();//" Delete from user where username = @username and password = @password ";
            //                cmd.Parameters.Clear();
            //                cmd.Prepare();
            //                cmd.Parameters.AddWithValue("@username", txt_username.Text);
            //                cmd.Parameters.AddWithValue("@password", txt_password.Text);
            //                cmd.ExecuteNonQuery();

            //                transaction.Commit();
            //            }
            //        }
            //    }
            //    catch(Exception ex)
            //    {
            //        MessageBox.Show(ex.Message);
            //    }
            //    finally
            //    {
            //        conn.Close(); //tutup koneksi
            //        conn.Dispose();
            //    }
            //}
            //if (err == string.Empty)
            //{
            //    MessageBox.Show("Username berhasil dihapus");
            //}
        }
        private void generateRfidMhs()
        {
            string NIM = txt_nik.Text.Replace("'", "''");

            string prodi = string.Empty;

            DataTable dtProdi = null;
            cDatabase db      = new cDatabase();
            cQuery    qr      = new cQuery();

            dtProdi = db.selectData(qr.qSelectKodeProdiByNim(NIM), '2');
            if (dtProdi.Rows.Count != 0)
            {
                string cekRfid = string.Empty;
                foreach (DataRow rowProdi in dtProdi.Rows)//ambil kode prodi sesuai nim
                {
                    cekRfid = rowProdi["RFID"].ToString();
                    prodi   = rowProdi["prodi"].ToString();
                }

                //jika ternyata sudah ada rfidnya, proses stop
                if (cekRfid != string.Empty)
                {
                    clearTampilan();

                    tampilMhs(NIM);

                    //txt_cari_nama.Text = string.Empty;
                    //txt_cari_nik.Text = string.Empty;

                    return;
                }

                //cek dari tabel mahasiswa
                DataTable lastHeksa = selectRfidMhsTerakirByProdi(prodi); //ambil heksa terakir dari mahasiswa, sesuai prodinya
                string    heksa     = string.Empty;
                if (lastHeksa.Rows.Count != 0)
                {
                    foreach (DataRow rowHeksa in lastHeksa.Rows)
                    {
                        heksa = rowHeksa["heksa"].ToString();
                    }
                }

                //cek dari tabel history_rfid
                DataTable lastHeksaHis = selectFrHistoryRfidMhsTerakirbyProdi(prodi); //ambil heksa terakir dari history, sesuai prodinya
                string    heksaHis     = string.Empty;
                if (lastHeksaHis.Rows.Count != 0)
                {
                    foreach (DataRow rowHeksaHis in lastHeksaHis.Rows)
                    {
                        heksaHis = rowHeksaHis["heksa"].ToString();
                    }
                }

                string heksaUse = string.Empty;                        //heksa yang akan dipakai

                if (heksa != string.Empty && heksaHis != string.Empty) //jika di kedua tabel ditemukan heksa sebelumnya
                {
                    //bandingkan heksa dari tabel mhs dan history, nilainya paling tinggi yang dipakai
                    Int32 iheksa    = Int32.Parse(heksa, System.Globalization.NumberStyles.HexNumber);    //dari heksa terakir convert ke int
                    Int32 iheksaHis = Int32.Parse(heksaHis, System.Globalization.NumberStyles.HexNumber); //dari heksa terakir convert ke int

                    if (iheksa > iheksaHis)
                    {
                        heksaUse = heksa;
                    }
                    else if (iheksaHis > iheksa)
                    {
                        heksaUse = heksaHis;
                    }
                }
                else if (heksa != string.Empty && heksaHis == string.Empty) //jika hanya ditemukan dari tabel mahasiswa
                {
                    heksaUse = heksa;
                }
                else if (heksa == string.Empty && heksaHis != string.Empty) //jika hanya ditemukan dari tabel history_rfid
                {
                    heksaUse = heksaHis;
                }

                //=======================================================================
                if (heksaUse != string.Empty)//jika sudah ditemukan kode rfid terakir, lanjutkan sequence-nya
                {
                    UpdateRfidMhs(NIM, prodi, heksaUse);
                }
                else
                { //jika belum ditemukan kode rfid sebelumnya, generate dari awal
                    UpdateRfidMhs(NIM, prodi, "0000");
                }

                lastHeksa.Clear();
                lastHeksa.Dispose();
                lastHeksaHis.Clear();
                lastHeksaHis.Dispose();
            }

            clearTampilan();

            //txt_cari_nama.Text = string.Empty;
            //txt_cari_nik.Text = string.Empty;
            //DGList.Rows.Clear();
            //jmlRow.Text = string.Empty;

            tampilMhs(NIM);
        }