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();
}
}
}
//cek data sql lite
//lastUpdate, true = pakai lastUpdate, false tidak pakai lastUpdate
// return true jika ada data
// return false jika tidak ada
private bool cekDataSqlLIte(string query, bool lastUpdate)
{
if (lastUpdate == true)
{
this.LastUpdate = string.Empty;
} //kosongkan dulu lastUpdate
bool ada = false;
cKoneksi koneksi = new cKoneksi();
SQLiteConnection sqlite = null;
sqlite = new SQLiteConnection(koneksi.LokasiSqlite());
SQLiteDataReader rdrCek = null; //fikri 20150202
SQLiteCommand cek_dataSqlLite = null; //fikri 20150202
cek_dataSqlLite = new SQLiteCommand(query); // fikri 20150202
try
{
sqlite.Open();
cek_dataSqlLite.Connection = sqlite;
rdrCek = cek_dataSqlLite.ExecuteReader();
if (rdrCek.HasRows)
{
if (lastUpdate == true)
{
while (rdrCek.Read())
{
this.LastUpdate = rdrCek.GetDateTime(0).ToString();
} //isi lastUpdate
}
ada = true;
}
else
{
ada = false;
}
return(ada);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return(ada);
}
finally
{
rdrCek.Close();
sqlite.Close();
sqlite.Dispose();
}
}
private void InsertIP()
{
bool err = false;
string query = "INSERT INTO IpHistory (IpAddress, LastModified, port) values (@IpAddress, @LastModified, @port)";
cKoneksi koneksi = new cKoneksi();
cControl cnt = new cControl();
using (SQLiteConnection sqlCon = new SQLiteConnection(koneksi.LokasiSqlite()))//insert ke local Sqlite
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
DataTable dt = new DataTable();
try
{
//---------------------------------
cmd.CommandText = query;
cmd.Parameters.AddWithValue("@IpAddress", getIpAdr);
cmd.Parameters.AddWithValue("@LastModified", cnt.getDateTimeNow());
cmd.Parameters.AddWithValue("@port", txtNewPort.Text.ToString().Trim());
//---------------------------------
cmd.Connection = sqlCon;
sqlCon.Open();
da.Fill(dt);
//---------------------
}
catch (Exception ex)
{
err = true;
MessageBox.Show(ex.Message);
}
finally
{
sqlCon.Close();
sqlCon.Dispose();
dt.Clear();
dt.Dispose();
}
}
}
if (err == false)
{
refresh(); //jika tidak ada error refresh
}
}
cKoneksi koneksi = new cKoneksi();//buat koneksi
public DataTable selectDataSqlite(string query)
{
using (SQLiteConnection sqliteCon = new SQLiteConnection(koneksi.LokasiSqlite()))// select data dari sqlite
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
//---------------------------------
cmd.CommandText = query;
//---------------------------------
cmd.Connection = sqliteCon;
sqliteCon.Open();
SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
//---------------------
sqliteCon.Close();
sqliteCon.Dispose();
return(dt);
}
}
}
private string loginLocal()
{
cKoneksi koneksi = new cKoneksi();
string role = string.Empty;
var con = new SQLiteConnection(koneksi.LokasiSqlite());
cQuery qr = new cQuery();
SQLiteCommand cmd = new SQLiteCommand(qr.qSelectUserLoginSqlite(), con);
cmd.Parameters.AddWithValue("@username", this.textBox_username.Text);
cmd.Parameters.AddWithValue("@password", this.textBox_pass.Text);
try
{
con.Open();
SQLiteDataReader 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 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 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);
}
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);
}
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);
}