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 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);
}
//select data ms sql
public DataTable selectData(string query, char server)
{
cKoneksi koneksi = new cKoneksi();
using (SqlConnection sqlCon = new SqlConnection(koneksi.konekMsSql(server)))// select data dari server
{
using (SqlCommand cmd = new SqlCommand())
{
//---------------------------------
cmd.CommandText = query;
//---------------------------------
cmd.Connection = sqlCon;
sqlCon.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
//---------------------
sqlCon.Close();
sqlCon.Dispose();
return(dt);
}
}
}
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 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();
// }
//}
}
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();
}
}
}
private string uploadTransaksiKartuMhs()
{
cSyncRun.isSync = true; //tandai proses sync
string err = string.Empty; //tampungan error message
try
{
DataTable dt = null;
cQuery qr = new cQuery();
cDatabase db = new cDatabase();
dt = db.selectDataSqlite(qr.qSelectTransaksiMhsStatus0()); // ambil data dari sqlite
if (dt.Rows.Count != 0) //jika ada data
{
cKoneksi koneksi = new cKoneksi();
SqlConnection conn = new SqlConnection(koneksi.konekMsSql('2')); //konek ke sql server user ktm = 2
SQLiteConnection connLite = new SQLiteConnection(koneksi.LokasiSqlite()); //koneksi ke sqlite
try
{
conn.Open(); //open sql server
connLite.Open(); //open sqlite
//===inisialisasi progres bar======
if (PbUpload.InvokeRequired)
{
PbUpload.BeginInvoke(
new Action(() =>
{
PbUpload.Style = ProgressBarStyle.Blocks;
PbUpload.Value = 0;
PbUpload.Minimum = 0;
PbUpload.Maximum = dt.Rows.Count;
}
));
}
//=================================
SQLiteTransaction transactionSqlite = connLite.BeginTransaction();
DataTable dtTrMhs = null;
foreach (DataRow row in dt.Rows)
{
SqlCommand myCommand = null;
dtTrMhs = cekTransaksiMhs(row["nim"].ToString()); //cek data sebelumnya
if (dtTrMhs.Rows.Count == 0) //jika tidak ada data sebelumnya langung di insert
{
myCommand = new SqlCommand(qr.qInsertStatusMhs(), conn);
myCommand.Parameters.AddWithValue("@STUDENTID", row["nim"]);
myCommand.Parameters.AddWithValue("@TGL_AMBIL_KTM", row["lastModified"]);
myCommand.Parameters.AddWithValue("@PTGS_KTM_AMBIL", row["operator"]);
}
else //hanya di update
{
//==cek dulu status ktm diambil==
Int32 AMBIL_KTM_KE;
string tmpAMBIL_KTM_KE = "";
foreach (DataRow rowTrMhs in dtTrMhs.Rows)
{
tmpAMBIL_KTM_KE = rowTrMhs["AMBIL_KTM_KE"].ToString().Trim();
}
//===============================
myCommand = new SqlCommand(qr.qUpdateStatusMhs(), conn);
if (Int32.TryParse(tmpAMBIL_KTM_KE, out AMBIL_KTM_KE))
{//jika sudah pernah diambil, urutan pengambilan ditambah 1
myCommand.Parameters.AddWithValue("@AMBIL_KTM_KE", AMBIL_KTM_KE + 1);
}
else
{//jika urutan bukan int, set jadi 0
myCommand.Parameters.AddWithValue("@AMBIL_KTM_KE", 1);
}
myCommand.Parameters.AddWithValue("@TGL_AMBIL_KTM", row["lastModified"]);
myCommand.Parameters.AddWithValue("@STUDENTID", row["nim"]);
myCommand.Parameters.AddWithValue("@PTGS_KTM_AMBIL", row["operator"]);
}
SQLiteCommand cmdSqlite = new SQLiteCommand(qr.qUpdateStatusUpload1(), connLite);
cmdSqlite.Parameters.AddWithValue("@idTransaksi", row["idTransaksi"]);
////cek perintah thread close
if (cSyncRun.closingThread == true)
{
transactionSqlite.Commit(); //jika ada perintah thread close, maka di sqlite langsung di commit
conn.Close(); //tutup koneksi
conn.Dispose();
connLite.Close();
connLite.Dispose();
return("Thread Closed By User");
}
try
{
myCommand.ExecuteNonQuery();//insert ke sql
}
catch
{
transactionSqlite.Commit();//jika proses insert di sql gagal, di sqlite langsung di commit
}
cmdSqlite.ExecuteNonQuery();//update status ke sqlite
//===proses progres bar======
if (PbUpload.InvokeRequired)
{
PbUpload.BeginInvoke(
new Action(() =>
{
PbUpload.Increment(1);
}
));
}
//===========================
dtTrMhs.Clear();
}
transactionSqlite.Commit();
}
catch (Exception ex)
{
err = ex.Message;
//MessageBox.Show(ex.Message);
}
finally
{
conn.Close(); //tutup koneksi
conn.Dispose();
connLite.Close();
connLite.Dispose();
}
}
//clear datatable
dt.Clear();
dt.Dispose();
//================
}
catch (Exception ex)
{
err = ex.Message;
}
cSyncRun.isSync = false;
return(err);
}