//======================================================================================================
public void get_cust_id()
{
CRUD sql = new CRUD();
try
{
ckon.sqlCon().Open();
String cmd = "SELECT * FROM store";
ckon.sqlDataRd = sql.ExecuteDataReader(cmd, ckon.sqlCon());
if (ckon.sqlDataRd.HasRows)
{
while (ckon.sqlDataRd.Read())
{
cust_id_store = ckon.sqlDataRd["CUST_ID_STORE"].ToString();
}
}
}
catch (Exception e)
{
MessageBox.Show(e.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (ckon.sqlDataRd != null)
{
ckon.sqlDataRd.Close();
}
if (ckon.sqlCon().State == ConnectionState.Open)
{
ckon.sqlCon().Close();
}
}
}
//===========================METHOD ISI COMBO=============================================
public void isi_combo_spg()
{
CRUD sql = new CRUD();
combo_spg.Items.Clear();
//String sql = "SELECT employee.EMPLOYEE_ID, employee.NAME FROM employee INNER JOIN position ON employee.POSITION_ID = position._id WHERE position._id = '4' OR position._id = '3' OR position._id = '2'";
//String sql = "SELECT * FROM employee WHERE POSITION_ID = '2' OR POSITION_ID = '3' OR POSITION_ID = '4'";
try
{
ckon.sqlCon().Open();
String cmd = "SELECT * FROM employee where STORE_CODE = '" + store + "'";
ckon.sqlDataRd = sql.ExecuteDataReader(cmd, ckon.sqlCon());
if (ckon.sqlDataRd.HasRows)
{
while (ckon.sqlDataRd.Read())
{
id_spg = ckon.sqlDataRd["EMPLOYEE_ID"].ToString();
nama_spg = ckon.sqlDataRd["NAME"].ToString();
combo_spg.Items.Add(id_spg + "--" + nama_spg);
}
}
}
catch (Exception e)
{
MessageBox.Show("No connection to database", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (ckon.sqlDataRd != null)
{
ckon.sqlDataRd.Close();
}
if (ckon.sqlCon().State == ConnectionState.Open)
{
ckon.sqlCon().Close();
}
}
//ckon.cmd = new MySqlCommand(sql, ckon.con);
//try
//{
// ckon.con.Open();
// ckon.myReader = ckon.cmd.ExecuteReader();
// while (ckon.myReader.Read())
// {
// //String name = ckon.myReader.GetString("ID_SPG");
// //combo_spg.Items.Add(name);
// id_spg = ckon.myReader.GetString("EMPLOYEE_ID");
// nama_spg = ckon.myReader.GetString("NAME");
// combo_spg.Items.Add(id_spg + "--" + nama_spg);
// }
// ckon.con.Close();
//}
//catch
//{ MessageBox.Show("Data gagal ditambilkan untuk combobox"); }
}
//=================CEK APAKAH USERNAME TERSEBUT DAN PASSWORD SAMA======
public void cek_username()
{
CRUD sql = new CRUD();
//ckon.con.Close();
try
{
ckon.sqlCon().Open();
String cmd = "SELECT * FROM employee WHERE EMPLOYEE_ID='" + t_user.Text + "' and Pass = '******'";
ckon.sqlDataRd = sql.ExecuteDataReader(cmd, ckon.sqlCon());
if (ckon.sqlDataRd.HasRows)
{
cek_pass_baru();
}
else
{
MessageBox.Show("Username or Password Not Valid");
}
}
catch (Exception e)
{
MessageBox.Show("No connection to database", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (ckon.sqlDataRd != null)
{
ckon.sqlDataRd.Close();
}
if (ckon.sqlCon().State == ConnectionState.Open)
{
ckon.sqlCon().Close();
}
}
//ckon.cmd = new MySqlCommand(sql, ckon.con);
//ckon.con.Open();
//ckon.myReader = ckon.cmd.ExecuteReader();
//int count0 = 0;
//if (ckon.myReader.HasRows)
//{
// while (ckon.myReader.Read())
// {
// count0 = count0 + 1;
// }
// ckon.con.Close();
// if (count0 != 0)
// {
// cek_pass_baru();
// }
//}
//else
//{
// MessageBox.Show("Username or Password Not Valid");
//}
}
public void downloadFile()
{
CRUD sql = new CRUD();
String downloadPath = "";
String fileName = "";
String id = "";
String jobId = "";
String storeId = "";
String rowFatch = "";
int syncType = 0;
try
{
ckon.sqlConMsg().Open();
String cmd = "SELECT JobID, StoreID, DownloadPath, SynchDetail, RowFatch, Synctype FROM JobTabletoSynchDetailDownload " +
"WHERE SynchDetail NOT IN(SELECT SynchDetail FROM JobSynchDetailDownloadStatus) OR " +
"SynchDetail IN(SELECT SynchDetail FROM JobSynchDetailDownloadStatus a WHERE a.RowApplied = 0)";
ckon.sqlDataRd = sql.ExecuteDataReader(cmd, ckon.sqlConMsg());
if (ckon.sqlDataRd.HasRows)
{
Guid downloadSession = Guid.NewGuid();
while (ckon.sqlDataRd.Read())
{
downloadPath = Convert.ToString(ckon.sqlDataRd["DownloadPath"]);
id = Convert.ToString(ckon.sqlDataRd["SynchDetail"]);
jobId = Convert.ToString(ckon.sqlDataRd["JobID"]);
storeId = Convert.ToString(ckon.sqlDataRd["StoreID"]);
rowFatch = Convert.ToString(ckon.sqlDataRd["RowFatch"]);
syncType = Convert.ToInt32(ckon.sqlDataRd["Synctype"]);
fileName = getFilename(downloadPath, syncType);
downloadFromFTP(downloadPath, fileName, id, jobId.Trim(), storeId.Trim(), rowFatch, downloadSession, syncType);
createFilePath(downloadPath);
}
}
}
catch (Exception e)
{
MessageBox.Show(e.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (ckon.sqlDataRd != null)
{
ckon.sqlDataRd.Close();
}
if (ckon.sqlCon().State == ConnectionState.Open)
{
ckon.sqlCon().Close();
}
}
}
//=====================select do line======================
public void get_do_line(String id_do)
{
string command;
koneksi ckon = new koneksi();
do_id = id_do;
//String sql = "SELECT * FROM deliveryorder_line WHERE DELIVERY_ORDER_ID = '" + do_id + "'";
//ckon3.cmd3 = new MySqlCommand(sql, ckon3.con3);
//ckon3.con3.Open();
//ckon3.myReader3 = ckon3.cmd3.ExecuteReader();
//while (ckon3.myReader3.Read())
//{
// art_id_do = ckon3.myReader3.GetString("ARTICLE_ID");
// qty_receive = ckon3.myReader3.GetInt32("QTY_RECEIVE");
// cek_qty_inv(art_id_do);
// cek_inv_line(do_id, qty_receive);
//}
//ckon3.con3.Close();
try
{
ckon.sqlCon().Open();
command = "SELECT a.QTY_RECEIVE, b._id FROM deliveryorder_line a JOIN article b "
+ "ON a.ARTICLE_ID = b.ARTICLE_ID WHERE DELIVERY_ORDER_ID = '" + do_id + "' AND QTY_RECEIVE > 0";
CRUD sql = new CRUD();
ckon.sqlDataRd = sql.ExecuteDataReader(command, ckon.sqlCon());
if (ckon.sqlDataRd.HasRows)
{
while (ckon.sqlDataRd.Read())
{
art_id_do = ckon.sqlDataRd["_id"].ToString();
qty_receive = Convert.ToInt32(ckon.sqlDataRd["QTY_RECEIVE"].ToString());
cek_qty_inv(art_id_do);
cek_inv_line(do_id, qty_receive);
}
}
}
catch (Exception e)
{
MessageBox.Show(e.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (ckon.sqlDataRd != null)
{
ckon.sqlDataRd.Close();
}
if (ckon.sqlCon().State == ConnectionState.Open)
{
ckon.sqlCon().Close();
}
}
}
//melihat max qty dari article yang dipilih
public void cek_qty()
{
CRUD sql = new CRUD();
try
{
ckon.sqlCon().Open();
String cmd = "SELECT inventory.GOOD_QTY FROM inventory INNER JOIN article "
+ "ON article._id = inventory.ARTICLE_ID "
+ "WHERE article.ARTICLE_ID = '" + id2 + "'";
ckon.sqlDataRd = sql.ExecuteDataReader(cmd, ckon.sqlCon());
if (ckon.sqlDataRd.HasRows)
{
while (ckon.sqlDataRd.Read())
{
inv_good_qty = Convert.ToInt32(ckon.sqlDataRd["GOOD_QTY"].ToString());
}
}
}
catch (Exception e)
{
MessageBox.Show("No connection to database", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (ckon.sqlDataRd != null)
{
ckon.sqlDataRd.Close();
}
if (ckon.sqlCon().State == ConnectionState.Open)
{
ckon.sqlCon().Close();
}
}
//ckon.cmd = new MySqlCommand(sql, ckon.con);
//ckon.con.Open();
//ckon.myReader = ckon.cmd.ExecuteReader();
//while(ckon.myReader.Read())
//{
// String _id = ckon.myReader.GetString("_id");
// //ambil total qty dari tabel inventory
// String sql2 = "SELECT * FROM inventory WHERE ARTICLE_ID = '" + _id + "'";
// ckon2.cmd2 = new MySqlCommand(sql2, ckon2.con2);
// ckon2.con2.Open();
// ckon2.myReader2 = ckon2.cmd2.ExecuteReader();
// while(ckon2.myReader2.Read())
// {
// inv_good_qty = ckon2.myReader2.GetInt32("GOOD_QTY");
// }
// ckon2.con2.Close();
//}
//ckon.con.Close();
}
public void UploaddFile()
{
CRUD sql = new CRUD();
String uploadPath = "";
String fileName = "";
String id = "";
String jobId = "";
String storeId = "";
String rowFatch = "";
try
{
ckon.sqlConMsg().Open();
String cmd = "SELECT JobID, StoreID, UploadPath, SynchDetail, RowFatch FROM JobTabletoSynchDetailUpload " +
"WHERE SynchDetail NOT IN(SELECT SynchDetail FROM JobSynchDetailUploadStatus)";
ckon.sqlDataRd = sql.ExecuteDataReader(cmd, ckon.sqlConMsg());
if (ckon.sqlDataRd.HasRows)
{
while (ckon.sqlDataRd.Read())
{
uploadPath = Convert.ToString(ckon.sqlDataRd["UploadPath"]);
id = Convert.ToString(ckon.sqlDataRd["SynchDetail"]);
jobId = Convert.ToString(ckon.sqlDataRd["JobID"]);
storeId = Convert.ToString(ckon.sqlDataRd["StoreID"]);
rowFatch = Convert.ToString(ckon.sqlDataRd["RowFatch"]);
fileName = getFilename(uploadPath);
uploadToFTP(fileName, storeId, id, rowFatch);
}
}
}
catch (Exception e)
{
using (EventLog eventLog = new EventLog("Application"))
{
eventLog.Source = "Application";
eventLog.WriteEntry("Upload background " + e.Message.ToString(), EventLogEntryType.Information, 101, 1);
}
MessageBox.Show(e.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (ckon.sqlDataRd != null)
{
ckon.sqlDataRd.Close();
}
if (ckon.sqlCon().State == ConnectionState.Open)
{
ckon.sqlCon().Close();
}
}
}
//====GET VOUCHER FROM API, COONECT TO INTERNET======
//public async Task GetVoucher()
//{
// String response = "";
// var credentials = new NetworkCredential("username", "password");
// var handler = new HttpClientHandler { Credentials = credentials };
// using (var client = new HttpClient(handler))
// {
// // Make your request...
// client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));
// try
// {
// //HttpResponseMessage message = client.GetAsync("http://retailbiensi.azurewebsites.net/api/Voucher?VoucherCode="+ voucher_code+"&storeCode="+store_code2).Result;
// HttpResponseMessage message = client.GetAsync("http://mpos.biensicore.co.id/api/Voucher?VoucherCode=" + voucher_code + "&storeCode=" + store_code2).Result;
// if (message.IsSuccessStatusCode)
// {
// var serializer = new DataContractJsonSerializer(typeof(Voucher));
// var result = message.Content.ReadAsStringAsync().Result;
// byte[] byteArray = Encoding.UTF8.GetBytes(result);
// MemoryStream stream = new MemoryStream(byteArray);
// Voucher resultData = serializer.ReadObject(stream) as Voucher;
// //==masukan daat ke dalam variable
// String code = resultData.VoucherCode;
// String desc = resultData.Description;
// int value = resultData.Value;
// int id = resultData.Id;
// //==VALIDASI JIKA VOUCHER TIDAK ADA
// if(value==0)
// {
// MessageBox.Show("No Voucher Available");
// this.Close();
// }
// else
// {
// //===buka form untuk menggunakan voucher
// W_Vou_Confirm confirm = new W_Vou_Confirm();
// confirm.id_transaksi3 = id_transaksi2; //digunakan untuk membawa id transaksi
// confirm.get_voucher_valid(code, desc, value, id);//memberikan data ke form penggunaan voucher
// confirm.ShowDialog();//buka form nya
// //====tutup form ini
// this.Close();
// }
// }
// else
// {
// response = "Fail";
// }
// }
// catch (Exception ex)
// {
// response = ex.ToString();
// }
// }
//}
//=====================================================================
private void voucherCheck()
{
CRUD sql = new CRUD();
string desc = "";
int value = 0, id = 0, valuePct = 0;
try
{
ckon.sqlCon().Open();
String cmd = "SELECT * FROM DiscountSetup WHERE DiscountCode = '" + t_voucher.Text + "' and StartDate <= '" + System.DateTime.Today.ToString("yyyy-MM-dd") + "' and EndDate >= '" + System.DateTime.Today.ToString("yyyy-MM-dd") + "'";
ckon.sqlDataRd = sql.ExecuteDataReader(cmd, ckon.sqlCon());
if (ckon.sqlDataRd.HasRows)
{
while (ckon.sqlDataRd.Read())
{
desc = ckon.sqlDataRd["DiscountName"].ToString();
value = Convert.ToInt32(ckon.sqlDataRd["DiscountCash"]);
valuePct = Convert.ToInt32(ckon.sqlDataRd["DiscountPercent"]);
id = Convert.ToInt32(ckon.sqlDataRd["Id"].ToString());
}
//=== buka form untuk menggunakan voucher
W_Vou_Confirm confirm = new W_Vou_Confirm();
confirm.id_transaksi3 = id_transaksi2; //digunakan untuk membawa id transaksi
confirm.get_voucher_valid(t_voucher.Text, desc, value, valuePct, id); //memberikan data ke form penggunaan voucher
confirm.ShowDialog(); //buka form nya
//====tutup form ini
this.Close();
}
else
{
MessageBox.Show("No Voucher Available");
this.Close();
}
}
catch (Exception e)
{
MessageBox.Show(e.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (ckon.sqlDataRd != null)
{
ckon.sqlDataRd.Close();
}
if (ckon.sqlCon().State == ConnectionState.Open)
{
ckon.sqlCon().Close();
}
}
}
//======================================================================
public void get_id(String id)
{
string command;
koneksi ckon = new koneksi();
//ckon.con.Close();
//string sql = "SELECT * FROM article WHERE ARTICLE_ID = '" + id +"'";
//ckon.cmd = new MySqlCommand(sql, ckon.con);
//ckon.con.Open();
//ckon.myReader = ckon.cmd.ExecuteReader();
//while(ckon.myReader.Read())
//{
// id_from_article = ckon.myReader.GetString("_id");
//}
//cek_qty_inv(id_from_article);
//ckon.con.Close();
try
{
ckon.sqlCon().Open();
command = "SELECT * FROM article WHERE ARTICLE_ID = '" + id + "'";
CRUD sql = new CRUD();
ckon.sqlDataRd = sql.ExecuteDataReader(command, ckon.sqlCon());
if (ckon.sqlDataRd.HasRows)
{
while (ckon.sqlDataRd.Read())
{
id_from_article = ckon.sqlDataRd["_id"].ToString();
}
cek_qty_inv(id_from_article);
}
}
catch (Exception e)
{
MessageBox.Show(e.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (ckon.sqlDataRd != null)
{
ckon.sqlDataRd.Close();
}
if (ckon.sqlCon().State == ConnectionState.Open)
{
ckon.sqlCon().Close();
}
}
}
public string cek_ver()
{
string command;
try
{
ckon.sqlCon().Open();
command = "SELECT * FROM version_apk WHERE Type='POS'";
CRUD sql = new CRUD();
ckon.sqlDataRd = sql.ExecuteDataReader(command, ckon.sqlCon());
if (ckon.sqlDataRd.HasRows)
{
while (ckon.sqlDataRd.Read())
{
ver_db = ckon.sqlDataRd["Version"].ToString();
}
if (ver_apk == ver_db)
{
message = "The Application Version Is up to date";
}
else
{
message = "Application Version Needs To Be Updated";
}
}
else
{
message = "GetFirst";
}
}
catch (Exception e)
{
MessageBox.Show(e.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (ckon.sqlDataRd != null)
{
ckon.sqlDataRd.Close();
}
if (ckon.sqlCon().State == ConnectionState.Open)
{
ckon.sqlCon().Close();
}
}
return(message);
}
//==============METHOD INPUT, DELETE, Dan Edit============================================
public void ExecuteNonQuery(String query)
{
string command;
SqlCommand sqlCmd = null;
SqlDataReader sqlDataRd = null;
SqlConnection sqlCon;
sqlCon = ckon.sqlCon();
try
{
if (sqlCon.State == ConnectionState.Closed)
{
sqlCon.Open();
}
sqlCmd = new SqlCommand(query);
sqlCmd.Connection = sqlCon;
sqlCmd.ExecuteNonQuery();
}
catch (Exception e)
{
MessageBox.Show(e.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (sqlCon.State == ConnectionState.Open)
{
sqlCon.Close();
}
}
}
//======================AMBIL TOTAL ARTICLE ID DARI INVENTORY, LALU DIKURANGI DAN DIUPDATE====================
public void cek_qty_inv(String art_id)
{
string command;
koneksi ckon = new koneksi();
article_id = art_id;
try
{
ckon.sqlCon().Open();
command = "SELECT * FROM inventory WHERE ARTICLE_ID = '" + art_id + "' AND GOOD_QTY > 0";
CRUD sql = new CRUD();
ckon.sqlDataRd = sql.ExecuteDataReader(command, ckon.sqlCon());
if (ckon.sqlDataRd.HasRows)
{
while (ckon.sqlDataRd.Read())
{
status_inv = "YES";
qty_inv = Convert.ToInt32(ckon.sqlDataRd["GOOD_QTY"].ToString());
}
}
else
{
status_inv = "NO";
}
}
catch (Exception e)
{
MessageBox.Show(e.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (ckon.sqlDataRd != null)
{
ckon.sqlDataRd.Close();
}
if (ckon.sqlCon().State == ConnectionState.Open)
{
ckon.sqlCon().Close();
}
}
}
//MENDAPATKAN id transaksi terakhir berdasarkan id_shift dan status 1, ubah running number ke int, lalu update ke table auto_number
public void update_runningNumber()
{
string command;
try
{
get_year_month();
ckon.sqlCon().Open();
command = "SELECT TOP 1 SUBSTRING(TRANSACTION_ID, 13, LEN(TRANSACTION_ID)) AS inv FROM [transaction] ORDER BY TRANSACTION_ID DESC";
CRUD sql = new CRUD();
ckon.sqlDataRd = sql.ExecuteDataReader(command, ckon.sqlCon());
if (ckon.sqlDataRd.HasRows)
{
while (ckon.sqlDataRd.Read())
{
id_substring = ckon.sqlDataRd["inv"].ToString();
id_substring2 = Convert.ToInt32(id_substring);
}
command = "UPDATE auto_number SET Number='" + id_substring2 + "' WHERE Type_Trans = '1' AND Year = '" + tahun_now + "' AND Month = '" + bulan_now + "'";
CRUD update = new CRUD();
update.ExecuteNonQuery(command);
}
}
catch (Exception e)
{
MessageBox.Show(e.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (ckon.sqlDataRd != null)
{
ckon.sqlDataRd.Close();
}
if (ckon.sqlCon().State == ConnectionState.Open)
{
ckon.sqlCon().Close();
}
}
}
public void discCalcSP(String transId, String articleId, int qty, String spgId, String discountCode, String omniStorCode, int is_Service = 0, int is_OmniTrans = 1)
{
CRUD sql = new CRUD();
try
{
string command = "SELECT ARTICLE_NAME FROM ARTICLE WHERE ARTICLE_ID='" + articleId + "'";
ckon.sqlDataRd = sql.ExecuteDataReader(command, ckon.sqlCon());
if (ckon.sqlDataRd.HasRows)
{
while (ckon.sqlDataRd.Read())
{
articleName = ckon.sqlDataRd["ARTICLE_NAME"].ToString();
}
}
using (SqlConnection con = new SqlConnection(ckon.sqlCon().ConnectionString))
{
using (SqlCommand cmd = new SqlCommand("InsertTransaction", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@TRANSACTION_ID", SqlDbType.VarChar).Value = transId;
cmd.Parameters.Add("@ARTICLE_ID", SqlDbType.VarChar).Value = articleId;
cmd.Parameters.Add("@QUANTITY", SqlDbType.Int).Value = qty;
cmd.Parameters.Add("@SPG_ID", SqlDbType.VarChar).Value = spgId;
cmd.Parameters.Add("@DISCOUNT_CODE", SqlDbType.VarChar).Value = discountCode;
cmd.Parameters.Add("@IS_SERVICE", SqlDbType.VarChar).Value = is_Service;
cmd.Parameters.Add("@IS_OMNITRANS", SqlDbType.VarChar).Value = is_OmniTrans;
cmd.Parameters.Add("@OMNISTORECODE", SqlDbType.VarChar).Value = omniStorCode;
con.Open();
cmd.ExecuteNonQuery();
}
}
}
catch (Exception e)
{
MessageBox.Show(e.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
//==========================LOGIN================================================
public void loginSQL()
{
string command;
int count0 = 0;
try
{
ckon.sqlCon().Open();
command = "SELECT TOP 1 * FROM employee WHERE EMPLOYEE_ID = '" + t_username.Text + "' and CONVERT(nvarchar, Pass) = '" + t_pass.Text + "'";
CRUD sql = new CRUD();
ckon.sqlDataRd = sql.ExecuteDataReader(command, ckon.sqlCon());
if (ckon.sqlDataRd.HasRows)
{
while (ckon.sqlDataRd.Read())
{
count0 = count0 + 1;
nm_employe = ckon.sqlDataRd["NAME"].ToString();
id_employee = ckon.sqlDataRd["EMPLOYEE_ID"].ToString();
store_code = ckon.sqlDataRd["STORE_CODE"].ToString();
}
if (count0 != 0)
{
this.Hide();
Form1 fm1 = new Form1();
fm1.nama_employee = nm_employe;
fm1.id_employee = id_employee;
fm1.store_code = store_code;
fm1.setHo();
fm1.ShowDialog();
this.Close();
}
}
else
{
MessageBox.Show("Username or Password Not Valid");
}
}
catch (Exception e)
{
MessageBox.Show(e.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (ckon.sqlDataRd != null)
{
ckon.sqlDataRd.Close();
}
if (ckon.sqlCon().State == ConnectionState.Open)
{
ckon.sqlCon().Close();
}
}
}
public void getStore()
{
try
{
ckon.sqlCon().Open();
String cmd = "SELECT * FROM store";
ckon.sqlDataRd = sql.ExecuteDataReader(cmd, ckon.sqlCon());
if (ckon.sqlDataRd.HasRows)
{
while (ckon.sqlDataRd.Read())
{
storeCode = Convert.ToString(ckon.sqlDataRd["CODE"]);
}
}
else
{
storeCode = link.storeId;
}
}
catch (Exception e)
{
MessageBox.Show(e.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (ckon.sqlDataRd != null)
{
ckon.sqlDataRd.Close();
}
if (ckon.sqlCon().State == ConnectionState.Open)
{
ckon.sqlCon().Close();
}
}
}
public void tes_koneksi()
{
//string connectionString = "";
//LinkApi xmlCon = new LinkApi();
//connectionString = "Data Source='" + xmlCon.host_db + "';Initial Catalog='" + xmlCon.name_db + "';User ID='" + xmlCon.user_db + "';Password='******'";
try
{
ckon.sqlCon().Open();
checkConnectionString();
}
catch (Exception ex)
{
MessageBox.Show("Connection refused! Please configure a password on the connection.");
var appLog = new System.Diagnostics.EventLog("Pos Biensi");
appLog.Source = "Connection";
}
finally
{
if (ckon.sqlCon().State == ConnectionState.Open)
{
ckon.sqlCon().Close();
}
}
}
//======================LIST HOLD TRANSACTION============================================
public void holding(String cmd)
{
CRUD sql = new CRUD();
dgv_hold.Rows.Clear();
//koneksi2 ckon2 = new koneksi2();
//ckon.con.Close();
try
{
ckon.sqlCon().Open();
ckon.sqlDataRd = sql.ExecuteDataReader(cmd, ckon.sqlCon());
if (ckon.sqlDataRd.HasRows)
{
while (ckon.sqlDataRd.Read())
{
id_trans = ckon.sqlDataRd["RETURN_ORDER_ID"].ToString();
jam = ckon.sqlDataRd["TIME"].ToString();
int st_api = Convert.ToInt32(ckon.sqlDataRd["STATUS_API"].ToString());
String tr_date = ckon.sqlDataRd["DATE"].ToString();
int dgRows = dgv_hold.Rows.Add();
dgv_hold.Rows[dgRows].Cells[0].Value = id_trans;
dgv_hold.Rows[dgRows].Cells[1].Value = tr_date + " " + jam;
if (st_api == 1)
{
dgv_hold.Rows[dgRows].Cells[2].Value = "Confirmed";
}
else
{
dgv_hold.Rows[dgRows].Cells[2].Value = "Unconfirmed";
}
}
}
}
catch (Exception e)
{
MessageBox.Show(e.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (ckon.sqlDataRd != null)
{
ckon.sqlDataRd.Close();
}
if (ckon.sqlCon().State == ConnectionState.Open)
{
ckon.sqlCon().Close();
}
}
//date = mydate.ToString("yyyy-MM-dd");
//ckon.cmd = new MySqlCommand(sql, ckon.con);
//ckon.con.Open();
//ckon.myReader = ckon.cmd.ExecuteReader();
//List<string> numbersList = new List<string>();
//if (ckon.myReader.HasRows)
//{
// while (ckon.myReader.Read())
// {
// id_trans = ckon.myReader.GetString("RETURN_ORDER_ID");
// jam = ckon.myReader.GetString("TIME");
// //String sql2 = "SELECT article.ARTICLE_NAME FROM returnorder_line, article WHERE article.ARTICLE_ID = returnorder_line.ARTICLE_ID AND returnorder_line.RETURN_ORDER_ID='" + id_trans + "'";
// //ckon2.cmd2 = new MySqlCommand(sql2, ckon2.con2);
// //ckon2.con2.Open();
// //ckon2.myReader2 = ckon2.cmd2.ExecuteReader();
// //while (ckon2.myReader2.Read())
// //{
// // article_id = ckon2.myReader2.GetString("ARTICLE_NAME");
// // numbersList.Add(Convert.ToString(ckon2.myReader2["ARTICLE_NAME"]));
// //}
// //string[] numbersArray = numbersList.ToArray();
// //numbersList.Clear();
// //string result = String.Join(", ", numbersArray);
// int st_api = ckon.myReader.GetInt32("STATUS_API");
// String tr_date = ckon.myReader.GetString("DATE");
// int n = dgv_hold.Rows.Add();
// dgv_hold.Rows[n].Cells[0].Value = id_trans;
// dgv_hold.Rows[n].Cells[1].Value = tr_date +" "+jam;
// dgv_hold.Rows[n].Cells[2].Value = st_api.ToString();
// ckon2.con2.Close();
// }
//}
//ckon.con.Close();
}
public async Task Post_Discount()
{
CRUD sql = new CRUD();
Transaction transaction = new Transaction();
transaction.storeCode = code_store;
transaction.customerId = customer;
List <TransactionLine> transLine = new List <TransactionLine>();
Article articleFromDb = new Article();
//==================================================================================
try
{
ckon.sqlCon().Open();
string cmd = "SELECT * FROM ARTICLE WHERE ARTICLE_ID='" + S_ID + "'";
ckon.sqlDataRd = sql.ExecuteDataReader(cmd, ckon.sqlCon());
if (ckon.sqlDataRd.HasRows)
{
while (ckon.sqlDataRd.Read())
{
articleFromDb.articleId = ckon.sqlDataRd["ARTICLE_ID"].ToString();
articleFromDb.articleName = ckon.sqlDataRd["ARTICLE_NAME"].ToString();
articleFromDb.brand = ckon.sqlDataRd["BRAND"].ToString();
articleFromDb.color = ckon.sqlDataRd["COLOR"].ToString();
articleFromDb.department = ckon.sqlDataRd["DEPARTMENT"].ToString();
articleFromDb.departmentType = ckon.sqlDataRd["DEPARTMENT_TYPE"].ToString();
articleFromDb.gender = ckon.sqlDataRd["GENDER"].ToString();
articleFromDb.id = Convert.ToInt32(ckon.sqlDataRd["_id"].ToString());
articleFromDb.price = Convert.ToInt32(ckon.sqlDataRd["PRICE"].ToString());
articleFromDb.size = ckon.sqlDataRd["SIZE"].ToString();
articleFromDb.unit = ckon.sqlDataRd["UNIT"].ToString();
subtotal = Convert.ToInt32(ckon.sqlDataRd["PRICE"].ToString());
articleFromDb.articleIdAlias = ckon.sqlDataRd["ARTICLE_ID_ALIAS"].ToString();
isService = Convert.ToInt32(ckon.sqlDataRd["IS_SERVICE"].ToString());
}
}
}
catch (Exception e)
{
MessageBox.Show(e.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (ckon.sqlDataRd != null)
{
ckon.sqlDataRd.Close();
}
if (ckon.sqlCon().State == ConnectionState.Open)
{
ckon.sqlCon().Close();
}
}
//====================================================================
TransactionLine t = new TransactionLine();
t.subtotal = subtotal;
t.quantity = qty;
t.discount = 0;
t.price = subtotal;
//wahyu
//t.isService = isService;
t.article = articleFromDb;
transLine.Add(t);
transaction.transactionLines = transLine;
BiensiPOSContext.BiensiPOSDataContext contex = new BiensiPOSContext.BiensiPOSDataContext();
DiscountCalculateNew dc = new DiscountCalculateNew(contex);
try
{
DiscountMaster resultData = dc.Post(transaction);
//=================================================
try
{
foreach (var c in resultData.discountItems)
{
disc = (Int32)c.amountDiscount;
disc_code = c.discountCode;
disc_type = c.discountType;
disc_desc = c.discountDesc;
}
}
catch (Exception ex)
{
}
}
catch (Exception ex)
{
}
}
public void total_bank(String tanggal)
{
CRUD sql = new CRUD();
dgv_bank.Rows.Clear();
String date = tanggal;
try
{
ckon.sqlCon().Open();
String cmd_bank = "SELECT * FROM bank";
ckon.sqlDataRdHeader = sql.ExecuteDataReader(cmd_bank, ckon.sqlCon());
if (ckon.sqlDataRdHeader.HasRows)
{
while (ckon.sqlDataRdHeader.Read())
{
id_bank = ckon.sqlDataRdHeader["BANK_ID"].ToString();
nm_bank = ckon.sqlDataRdHeader["BANK_NAME"].ToString();
String cmd_EDC1 = "SELECT SUM([transaction].EDC) as total FROM [transaction] WHERE BANK_NAME='" + id_bank + "'AND ID_C_STORE='" + id_Cstore2 + "' AND (STATUS='1' or STATUS='2')";
ckon.sqlDataRdLine = sql.ExecuteDataReader(cmd_EDC1, ckon.sqlCon());
if (ckon.sqlDataRdLine.HasRows)
{
while (ckon.sqlDataRdLine.Read())
{
total_amount = Convert.ToInt32(ckon.sqlDataRdLine["total"].ToString());
}
}
else
{
total_amount = 0;
}
String cmd_EDC2 = "SELECT SUM([transaction].EDC2) as total FROM [transaction] WHERE BANK_NAME2='" + id_bank + "' AND ID_C_STORE='" + id_Cstore2 + "' AND (STATUS='1' or STATUS='2')";
ckon.sqlDataRdLine = sql.ExecuteDataReader(cmd_EDC1, ckon.sqlCon());
if (ckon.sqlDataRdLine.HasRows)
{
while (ckon.sqlDataRdLine.Read())
{
total_edc2 = Convert.ToInt32(ckon.sqlDataRdLine["total"].ToString());
}
}
else
{
total_edc2 = 0;
}
fix_total = total_amount + total_edc2;
int dgRows = dgv_bank.Rows.Add();
dgv_bank.Rows[dgRows].Cells[0].Value = nm_bank;
dgv_bank.Rows[dgRows].Cells[1].Value = fix_total;
dgv_bank.Columns[1].DefaultCellStyle.Format = "#,###";
}
}
}
catch (Exception e)
{
MessageBox.Show("No connection to database", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (ckon.sqlDataRdHeader != null)
{
ckon.sqlDataRdHeader.Close();
}
if (ckon.sqlDataRdLine != null)
{
ckon.sqlDataRdLine.Close();
}
if (ckon.sqlCon().State == ConnectionState.Open)
{
ckon.sqlCon().Close();
}
}
//koneksi2 ckon2 = new koneksi2();
//ckon.con.Close();
//String sql = "SELECT * FROM bank";
//ckon.cmd = new MySqlCommand(sql, ckon.con);
//ckon.con.Open();
//ckon.myReader = ckon.cmd.ExecuteReader();
//if(ckon.myReader.HasRows)
//{
// while(ckon.myReader.Read())
// {
// id_bank = ckon.myReader.GetString("BANK_ID");
// nm_bank = ckon.myReader.GetString("BANK_NAME");
// String sql2 = "SELECT SUM(transaction.EDC) as total FROM transaction WHERE BANK_NAME='" + id_bank + "' AND ID_C_STORE='" + id_Cstore2 + "' AND (STATUS='1' or STATUS='2')";
// ckon2.cmd2 = new MySqlCommand(sql2, ckon2.con2);
// ckon2.con2.Open();
// ckon2.myReader2 = ckon2.cmd2.ExecuteReader();
// while(ckon2.myReader2.Read())
// {
// try
// {
// total_amount = ckon2.myReader2.GetInt32("total");
// }
// catch
// {
// total_amount = 0;
// }
// }
// ckon2.con2.Close();
// //=====================================================================================================
// String sql3 = "SELECT SUM(transaction.EDC2) as total FROM transaction WHERE BANK_NAME2='" + id_bank + "' AND ID_C_STORE='" + id_Cstore2 + "' AND (STATUS='1' or STATUS='2')";
// ckon2.cmd2 = new MySqlCommand(sql3, ckon2.con2);
// ckon2.con2.Open();
// ckon2.myReader2 = ckon2.cmd2.ExecuteReader();
// while (ckon2.myReader2.Read())
// {
// try
// {
// total_edc2 = ckon2.myReader2.GetInt32("total");
// }
// catch
// {
// total_edc2 = 0;
// }
// }
// fix_total = total_amount + total_edc2;
// int n = dgv_bank.Rows.Add();
// dgv_bank.Rows[n].Cells[0].Value = nm_bank;
// dgv_bank.Rows[n].Cells[1].Value = fix_total;
// dgv_bank.Columns[1].DefaultCellStyle.Format = "#,###";
// ckon2.con2.Close();
// }
//}
//ckon.con.Close();
}
//=====================METHOD FOR ASYNC TASK API===================
public async Task <Boolean> Post_Closing_Shift()
{
Boolean isSuccess = false;
try
{
link_api = link.aLink;
String cmd = "SELECT * FROM closing_shift WHERE ID_SHIFT = '" + id_shift + "'";
ckon.sqlCon().Open();
ckon.sqlDataRd = sql.ExecuteDataReader(cmd, ckon.sqlCon());
if (ckon.sqlDataRd.HasRows)
{
while (ckon.sqlDataRd.Read())
{
_id = Convert.ToString(ckon.sqlDataRd["_id"]);
id_shift = Convert.ToString(ckon.sqlDataRd["ID_SHIFT"]);
seq_number_substring = id_shift.Substring(12);
store_id = Convert.ToString(ckon.sqlDataRd["STORE_ID"]);
shift = Convert.ToString(ckon.sqlDataRd["SHIFT"]);
opening_time = Convert.ToString(ckon.sqlDataRd["OPENING_TIME"]);
closing_time = Convert.ToString(ckon.sqlDataRd["CLOSING_TIME"]);
open_trans_balance = Convert.ToInt32(ckon.sqlDataRd["OPENING_TRANS_BALANCE"]);
closing_trans_balance = Convert.ToInt32(ckon.sqlDataRd["CLOSING_TRANS_BALANCE"]);
real_trans_balance = Convert.ToInt32(ckon.sqlDataRd["REAL_TRANS_BALANCE"]);
dispute_trans_balance = Convert.ToInt32(ckon.sqlDataRd["DISPUTE_TRANS_BALANCE"]);
open_pety = Convert.ToInt32(ckon.sqlDataRd["OPENING_PETTY_CASH"]);
close_pety = Convert.ToInt32(ckon.sqlDataRd["CLOSING_PETTY_CASH"]);
real_pety = Convert.ToInt32(ckon.sqlDataRd["REAL_PETTY_CASH"]);
dispute_pety = Convert.ToInt32(ckon.sqlDataRd["DISPUTE_PETTY_CASH"]);
open_deposit = Convert.ToInt32(ckon.sqlDataRd["OPENING_DEPOSIT"]);
close_deposit = Convert.ToInt32(ckon.sqlDataRd["CLOSING_DEPOSIT"]);
real_deposit = Convert.ToInt32(ckon.sqlDataRd["REAL_DEPOSIT"]);
dispute_deposit = Convert.ToInt32(ckon.sqlDataRd["DISPUTE_DEPOSIT"]);
dev_name = Convert.ToString(ckon.sqlDataRd["DEVICE_NAME"]);
status = Convert.ToString(ckon.sqlDataRd["STATUS_CLOSE"]);
epy_id = Convert.ToString(ckon.sqlDataRd["EMPLOYEE_ID"]);
epy_name = Convert.ToString(ckon.sqlDataRd["EMPLOYEE_NAME"]);
}
ClosingShift close = new ClosingShift()
{
closingShiftId = id_shift,
sequenceNumber = seq_number_substring,
storeCode = store_id,
shiftCode = shift,
openingTimestamp = opening_time,
closingTimestamp = closing_time,
openingTransBal = open_trans_balance,
closingTransBal = closing_trans_balance,
realTransBal = real_trans_balance,
disputeTransBal = dispute_trans_balance,
openingPettyCash = open_pety,
closingPettyCash = close_pety,
realPettyCash = real_pety,
disputePettyCash = dispute_pety,
openingDeposit = open_deposit,
closingDeposit = close_deposit,
realDeposit = real_deposit,
disputeDeposit = dispute_deposit,
deviceName = dev_name,
statusClose = status,
employeeId = epy_id,
employeeName = epy_name
};
var stringPayload = JsonConvert.SerializeObject(close);
String response = "";
var credentials = new NetworkCredential("username", "password");
var handler = new HttpClientHandler {
Credentials = credentials
};
var httpContent = new StringContent(stringPayload, Encoding.UTF8, "application/json");
using (var client = new HttpClient(handler))
{
HttpResponseMessage message = client.PostAsync(link_api + "/api/ClosingShift", httpContent).Result;
if (message.IsSuccessStatusCode)
{
isSuccess = true;
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "Error");
}
finally
{
if (ckon.sqlDataRd != null)
{
ckon.sqlDataRd.Close();
}
if (ckon.sqlCon().State == ConnectionState.Open)
{
ckon.sqlCon().Close();
}
}
return(isSuccess);
}
//MENDAPATKAN CURRENCY DARI TABEL CURRENCY
public void get_currency()
{
string command;
//ckon.con.Close();
//String sql = "SELECT * FROM currency";
//ckon.con.Open();
//ckon.cmd = new MySqlCommand(sql, ckon.con);
//ckon.myReader = ckon.cmd.ExecuteReader();
//while (ckon.myReader.Read())
//{
// sign = ckon.myReader.GetString("NAME");
//}
//ckon.con.Close();
try
{
ckon.sqlCon().Open();
command = "SELECT * FROM currency";
CRUD sql = new CRUD();
ckon.sqlDataRd = sql.ExecuteDataReader(command, ckon.sqlCon());
if (ckon.sqlDataRd.HasRows)
{
while (ckon.sqlDataRd.Read())
{
sign = ckon.sqlDataRd["SIGN"].ToString();
}
}
}
catch (Exception e)
{
MessageBox.Show(e.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (ckon.sqlDataRd != null)
{
ckon.sqlDataRd.Close();
}
if (ckon.sqlCon().State == ConnectionState.Open)
{
ckon.sqlCon().Close();
}
}
if (sign == "IDR")
{
mata_uang = "Rp";
}
if (sign == "SGD")
{
mata_uang = "$";
}
if (sign == "MYR")
{
mata_uang = "RM";
}
}
//====================FROM VOID TRANSACTION==========================
public void void_trans(String id)
{
string command;
koneksi ckon = new koneksi();
void_id = id;
//String sql = "SELECT * FROM transaction_line WHERE TRANSACTION_ID = '" + void_id + "'";
//ckon3.cmd3 = new MySqlCommand(sql, ckon3.con3);
//ckon3.con3.Open();
//ckon3.myReader3 = ckon3.cmd3.ExecuteReader();
//while(ckon3.myReader3.Read())
//{
// art_id = ckon3.myReader3.GetString("ARTICLE_ID");
// qty_trans_line = ckon3.myReader3.GetInt32("QUANTITY");
// String sql2 = "SELECT * FROM article WHERE ARTICLE_ID = '" + art_id + "'";
// ckon4.cmd4 = new MySqlCommand(sql2, ckon4.con4);
// ckon4.con4.Open();
// ckon4.myReader4 = ckon4.cmd4.ExecuteReader();
// while(ckon4.myReader4.Read())
// {
// inv_id = ckon4.myReader4.GetString("_id");
// cek_qty_inv(inv_id);//mengambil good qty dari inventory sesuai id
// cek_inv_line(void_id, qty_trans_line);
// }
// ckon4.con4.Close();
//}
//ckon3.con3.Close();
try
{
ckon.sqlCon().Open();
command = "SELECT article._id as artId, * FROM transaction_line INNER JOIN article "
+ "ON article.ARTICLE_ID = transaction_line.ARTICLE_ID "
+ "WHERE transaction_line.TRANSACTION_ID = '" + void_id + "'";
CRUD sql = new CRUD();
ckon.sqlDataRd = sql.ExecuteDataReader(command, ckon.sqlCon());
if (ckon.sqlDataRd.HasRows)
{
while (ckon.sqlDataRd.Read())
{
qty_trans_line = Convert.ToInt32(ckon.sqlDataRd["QUANTITY"].ToString());
inv_id = ckon.sqlDataRd["artId"].ToString();
cek_qty_inv(inv_id);//mengambil good qty dari inventory sesuai id
cek_inv_line(void_id, qty_trans_line);
}
}
}
catch (Exception e)
{
MessageBox.Show(e.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (ckon.sqlDataRd != null)
{
ckon.sqlDataRd.Close();
}
if (ckon.sqlCon().State == ConnectionState.Open)
{
ckon.sqlCon().Close();
}
}
}
//======Cek Di inventory Line, kalo kosong input, kalo ada update==============
public void cek_inv_line(String trans_id, int qty)
{
string command;
koneksi ckon = new koneksi();
int count = 0;
transaksi_id = trans_id;
qty_min_plus2 = qty;
try
{
ckon.sqlCon().Open();
command = "SELECT * FROM inventory_line WHERE TRANS_REF_ID = '" + transaksi_id + "' AND ARTICLE_ID = '" + article_id + "'";
CRUD sql = new CRUD();
ckon.sqlDataRd = sql.ExecuteDataReader(command, ckon.sqlCon());
if (ckon.sqlDataRd.HasRows)
{
while (ckon.sqlDataRd.Read())
{
count = count + 1;
qty_line = Convert.ToInt32(ckon.sqlDataRd["QTY"].ToString());
}
}
if (count == 0)
{
if (type_id == "1")
{
int I = 1;
String sql2 = "INSERT INTO inventory_line (TRANS_TYPE, TRANS_TYPE_NAME, TRANS_REF_ID, QTY,ARTICLE_ID) VALUES ('" + type_id + "','" + type_name + "', '" + transaksi_id + "', '-" + I + "', '" + article_id + "')";
CRUD input = new CRUD();
input.ExecuteNonQuery(sql2);
qty_total_inv_fix = qty_inv - 1;
String sql3 = "UPDATE inventory SET GOOD_QTY='" + qty_total_inv_fix + "' WHERE ARTICLE_ID='" + article_id + "'";
CRUD UPDATE = new CRUD();
UPDATE.ExecuteNonQuery(sql3);
}
else if (type_id == "2")
{
string sql2 = "INSERT INTO inventory_line (TRANS_TYPE, TRANS_TYPE_NAME, TRANS_REF_ID, QTY,ARTICLE_ID) VALUES ('" + type_id + "','" + type_name + "', '" + transaksi_id + "', '" + qty_min_plus2 + "', '" + article_id + "')";
CRUD input = new CRUD();
input.ExecuteNonQuery(sql2);
//==========JIKA INVENTORY TELAH ADA
if (status_inv == "YES")
{
qty_total_inv_fix = qty_inv + qty_min_plus2;
String sql3 = "UPDATE inventory SET GOOD_QTY='" + qty_total_inv_fix + "' WHERE ARTICLE_ID='" + article_id + "'";
CRUD UPDATE = new CRUD();
UPDATE.ExecuteNonQuery(sql3);
}
//=====JIKA INVENTORY BELOM ADA====
else
{
String input_inv = "INSERT INTO inventory (ARTICLE_ID,GOOD_QTY) VALUES ('" + article_id + "','" + qty_min_plus2 + "')";
CRUD input_baru = new CRUD();
input_baru.ExecuteNonQuery(input_inv);
}
}
else if (type_id == "4")
{
string sql2 = "INSERT INTO inventory_line (TRANS_TYPE, TRANS_TYPE_NAME, TRANS_REF_ID, QTY,ARTICLE_ID) VALUES ('" + type_id + "','" + type_name + "', '" + transaksi_id + "', '" + qty_min_plus2 + "', '" + article_id + "')";
CRUD input = new CRUD();
input.ExecuteNonQuery(sql2);
qty_total_inv_fix = qty_inv + qty_min_plus2;
String sql3 = "UPDATE inventory SET GOOD_QTY='" + qty_total_inv_fix + "' WHERE ARTICLE_ID='" + article_id + "'";
CRUD UPDATE = new CRUD();
UPDATE.ExecuteNonQuery(sql3);
}
else if (type_id == "3" || type_id == "10")
{
string sql2 = "INSERT INTO inventory_line (TRANS_TYPE, TRANS_TYPE_NAME, TRANS_REF_ID, QTY,ARTICLE_ID) VALUES ('" + type_id + "','" + type_name + "', '" + transaksi_id + "', '" + qty_min_plus2 + "', '" + article_id + "')";
CRUD input = new CRUD();
input.ExecuteNonQuery(sql2);
qty_total_inv_fix = qty_inv - qty_min_plus2;
String sql3 = "UPDATE inventory SET GOOD_QTY='" + qty_total_inv_fix + "' WHERE ARTICLE_ID='" + article_id + "'";
CRUD UPDATE = new CRUD();
UPDATE.ExecuteNonQuery(sql3);
}
else
{
string sql2 = "INSERT INTO inventory_line (TRANS_TYPE, TRANS_TYPE_NAME, TRANS_REF_ID, QTY,ARTICLE_ID) VALUES ('" + type_id + "','" + type_name + "', '" + transaksi_id + "', '" + qty_min_plus2 + "', '" + article_id + "')";
CRUD input = new CRUD();
input.ExecuteNonQuery(sql2);
qty_total_inv_fix = qty_inv - qty_min_plus2;
String sql3 = "UPDATE inventory SET GOOD_QTY='" + qty_total_inv_fix + "' WHERE ARTICLE_ID='" + article_id + "'";
CRUD UPDATE = new CRUD();
UPDATE.ExecuteNonQuery(sql3);
}
}
else
{
if (type_id == "1")
{
qty_line = qty_line + (qty_min_plus2);
String sql3 = "UPDATE inventory_line SET QTY='" + qty_line + "' WHERE TRANS_REF_ID='" + transaksi_id + "' AND ARTICLE_ID = '" + article_id + "'";
CRUD update = new CRUD();
update.ExecuteNonQuery(sql3);
qty_total_inv_fix = qty_inv + (qty_min_plus2);
String sql4 = "UPDATE inventory SET GOOD_QTY='" + qty_total_inv_fix + "' WHERE ARTICLE_ID='" + article_id + "'";
CRUD UPDATE = new CRUD();
UPDATE.ExecuteNonQuery(sql4);
}
else if (type_id == "2")
{
//qty_line = qty_line + 1;
String sql3 = "UPDATE inventory_line SET QTY='" + qty_line + "' WHERE TRANS_REF_ID='" + transaksi_id + "' AND ARTICLE_ID = '" + article_id + "'";
CRUD update = new CRUD();
update.ExecuteNonQuery(sql3);
//==========JIKA INVENTORY TELAH ADA
if (status_inv == "YES")
{
qty_total_inv_fix = qty_inv + qty_min_plus2;
String sql3A = "UPDATE inventory SET GOOD_QTY='" + qty_total_inv_fix + "' WHERE ARTICLE_ID='" + article_id + "'";
CRUD UPDATE = new CRUD();
UPDATE.ExecuteNonQuery(sql3A);
}
//=====JIKA INVENTORY BELOM ADA====
else
{
String input_inv = "INSERT INTO inventory (_id,ARTICLE_ID,GOOD_QTY) VALUES ('" + article_id + "','" + article_id + "','" + qty_min_plus2 + "')";
CRUD input_baru = new CRUD();
input_baru.ExecuteNonQuery(input_inv);
}
}
else if (type_id == "4")
{
string sql2 = "INSERT INTO inventory_line (TRANS_TYPE, TRANS_TYPE_NAME, TRANS_REF_ID, QTY,ARTICLE_ID) VALUES ('" + type_id + "','" + type_name + "', '" + transaksi_id + "', '" + qty_min_plus2 + "', '" + article_id + "')";
CRUD input = new CRUD();
input.ExecuteNonQuery(sql2);
qty_total_inv_fix = qty_inv + qty_min_plus2;
String sql3 = "UPDATE inventory SET GOOD_QTY='" + qty_total_inv_fix + "' WHERE ARTICLE_ID='" + article_id + "'";
CRUD UPDATE = new CRUD();
UPDATE.ExecuteNonQuery(sql3);
}
else if (type_id == "3" || type_id == "10")
{
string sql2 = "INSERT INTO inventory_line (TRANS_TYPE, TRANS_TYPE_NAME, TRANS_REF_ID, QTY,ARTICLE_ID) VALUES ('" + type_id + "','" + type_name + "', '" + transaksi_id + "', '" + qty_min_plus2 + "', '" + article_id + "')";
CRUD input = new CRUD();
input.ExecuteNonQuery(sql2);
qty_total_inv_fix = qty_inv - qty_min_plus2;
String sql3 = "UPDATE inventory SET GOOD_QTY='" + qty_total_inv_fix + "' WHERE ARTICLE_ID='" + article_id + "'";
CRUD UPDATE = new CRUD();
UPDATE.ExecuteNonQuery(sql3);
}
else
{
string sql2 = "INSERT INTO inventory_line (TRANS_TYPE, TRANS_TYPE_NAME, TRANS_REF_ID, QTY,ARTICLE_ID) VALUES ('" + type_id + "','" + type_name + "', '" + transaksi_id + "', '" + qty_min_plus2 + "', '" + article_id + "')";
CRUD input = new CRUD();
input.ExecuteNonQuery(sql2);
qty_total_inv_fix = qty_inv - qty_min_plus2;
String sql3 = "UPDATE inventory SET GOOD_QTY='" + qty_total_inv_fix + "' WHERE ARTICLE_ID='" + article_id + "'";
CRUD UPDATE = new CRUD();
UPDATE.ExecuteNonQuery(sql3);
}
}
}
catch (Exception e)
{
MessageBox.Show(e.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (ckon.sqlDataRd != null)
{
ckon.sqlDataRd.Close();
}
if (ckon.sqlCon().State == ConnectionState.Open)
{
ckon.sqlCon().Close();
}
}
}
//======================================================================
//======================RETREIVE DATA ===============================================
public void get_load_data(String query)
{
dgv_2.Rows.Clear();
try
{
ckon.sqlCon().Open();
CRUD sql = new CRUD();
ckon.dt = sql.ExecuteDataTable(query, ckon.sqlCon());
foreach (DataRow row in ckon.dt.Rows)
{
int dgRows = dgv_2.Rows.Add();
dgv_2.Rows[dgRows].Cells[0].Value = row["_id"];
dgv_2.Rows[dgRows].Cells[1].Value = row["ARTICLE_ID"].ToString();
dgv_2.Rows[dgRows].Cells[2].Value = row["ARTICLE_NAME"].ToString();
dgv_2.Rows[dgRows].Cells[3].Value = row["SIZE_ID"].ToString();
dgv_2.Rows[dgRows].Cells[4].Value = row["COLOR_ID"].ToString();
dgv_2.Rows[dgRows].Cells[5].Value = row["PRICE"];
dgv_2.Rows[dgRows].Cells[6].Value = row["GOOD_QTY"];
}
dgv_2.Columns[5].DefaultCellStyle.Format = "#,###";
}
catch (Exception e)
{
MessageBox.Show(e.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
ckon.dt.Rows.Clear();
if (ckon.sqlCon().State == ConnectionState.Open)
{
ckon.sqlCon().Close();
}
}
}
private void t_find_article_OnTextChange(object sender, EventArgs e)
{
CRUD sql = new CRUD();
dgv_hold.Rows.Clear();
//ckon.con.Close();
try
{
ckon.sqlCon().Open();
String cmd = "SELECT discount_item.ARTICLE_ID, discount_item.ARTICLE_NAME,inventory._id FROM discount_item INNER JOIN inventory ON discount_item._id = inventory.ARTICLE_ID WHERE inventory.GOOD_QTY >= 1 AND discount_item.ARTICLE_ID LIKE '%" + t_find_article.text + "%'";
ckon.dt = sql.ExecuteDataTable(cmd, ckon.sqlCon());
foreach (DataRow row in ckon.dt.Rows)
{
int dgRows = dgv_hold.Rows.Add();
dgv_hold.Rows[dgRows].Cells[0].Value = row["ARTICLE_ID"].ToString();
dgv_hold.Rows[dgRows].Cells[1].Value = row["ARTICLE_NAME"].ToString();
dgv_hold.Rows[dgRows].Cells[2].Value = row["_id"].ToString();
}
}
catch (Exception er)
{
MessageBox.Show("No connection to database", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
ckon.dt.Rows.Clear();
if (ckon.sqlCon().State == ConnectionState.Open)
{
ckon.sqlCon().Close();
}
}
//ckon.cmd = new MySqlCommand(sql, ckon.con);
//ckon.con.Open();
//ckon.adapter = new MySqlDataAdapter(ckon.cmd);
//ckon.adapter.Fill(ckon.dt);
//foreach (DataRow row in ckon.dt.Rows)
//{
// int n = dgv_hold.Rows.Add();
// dgv_hold.Rows[n].Cells[0].Value = row["ARTICLE_ID"].ToString();
// dgv_hold.Rows[n].Cells[1].Value = row["ARTICLE_NAME"].ToString();
// dgv_hold.Rows[n].Cells[2].Value = row["_id"].ToString();
//}
//ckon.dt.Rows.Clear();
//ckon.con.Close();
}
//======================LIST HOLD TRANSACTION============================================
public void holding(String cmd)
{
CRUD sql = new CRUD();
List <string> numbersList = new List <string>();
dgv_hold.Rows.Clear();
dgv_inv.Rows.Clear();
try
{
ckon.sqlCon().Open();
ckon.sqlDataRd = sql.ExecuteDataReader(cmd, ckon.sqlCon());
if (ckon.sqlDataRd.HasRows)
{
while (ckon.sqlDataRd.Read())
{
id_do = ckon.sqlDataRd["DELIVERY_ORDER_ID"].ToString();
total_qty = ckon.sqlDataRd["TOTAL_QTY"].ToString();
sj_fisik = ckon.sqlDataRd["SJ_FISIK"].ToString();
int dgRows = dgv_hold.Rows.Add();
dgv_hold.Rows[dgRows].Cells[0].Value = id_do;
dgv_hold.Rows[dgRows].Cells[1].Value = sj_fisik;
dgv_hold.Rows[dgRows].Cells[2].Value = total_qty;
}
}
}
catch (Exception e)
{
MessageBox.Show(e.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (ckon.sqlDataRd != null)
{
ckon.sqlDataRd.Close();
}
if (ckon.sqlCon().State == ConnectionState.Open)
{
ckon.sqlCon().Close();
}
}
}
//=======RETURN ORDER OUT========
public void return_order(String id)
{
string command;
koneksi ckon = new koneksi();
return_id = id;
//String sql = "SELECT * FROM returnorder_line WHERE RETURN_ORDER_ID = '" + return_id + "'";
//ckon3.cmd3 = new MySqlCommand(sql, ckon3.con3);
//ckon3.con3.Open();
//ckon3.myReader3 = ckon3.cmd3.ExecuteReader();
//while (ckon3.myReader3.Read())
//{
// art_id = ckon3.myReader3.GetString("ARTICLE_ID");
// qty_trans_line = ckon3.myReader3.GetInt32("QUANTITY");
// String sql2 = "SELECT * FROM article WHERE ARTICLE_ID = '" + art_id + "'";
// ckon4.cmd4 = new MySqlCommand(sql2, ckon4.con4);
// ckon4.con4.Open();
// ckon4.myReader4 = ckon4.cmd4.ExecuteReader();
// while (ckon4.myReader4.Read())
// {
// inv_id = ckon4.myReader4.GetString("_id");
// cek_qty_inv(inv_id);
// cek_inv_line(return_id, qty_trans_line);
// }
// ckon4.con4.Close();
//}
//ckon3.con3.Close();
try
{
ckon.sqlCon().Open();
command = "SELECT article._Id, returnorder_line.QUANTITY FROM returnorder_line INNER JOIN article "
+ "ON article.ARTICLE_ID = returnorder_line.ARTICLE_ID "
+ "WHERE RETURN_ORDER_ID = '" + return_id + "'";
CRUD sql = new CRUD();
ckon.sqlDataRd = sql.ExecuteDataReader(command, ckon.sqlCon());
if (ckon.sqlDataRd.HasRows)
{
while (ckon.sqlDataRd.Read())
{
qty_trans_line = Convert.ToInt32(ckon.sqlDataRd["QUANTITY"].ToString());
inv_id = ckon.sqlDataRd["_id"].ToString();
cek_qty_inv(inv_id);
cek_inv_line(return_id, qty_trans_line);
}
}
}
catch (Exception e)
{
MessageBox.Show(e.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (ckon.sqlDataRd != null)
{
ckon.sqlDataRd.Close();
}
if (ckon.sqlCon().State == ConnectionState.Open)
{
ckon.sqlCon().Close();
}
}
}
//=======SET ANGKA UNTUK INFO PERHITUNGAN DI POP UP DEPAN DARI TABEL CLOSING SHIFT========
public void set_angka()
{
CRUD sql = new CRUD();
cek_status_Cstore();
try
{
ckon.sqlCon().Open();
String cmd = "SELECT TOP 1 * FROM closing_shift WHERE STATUS_CLOSE='1' ORDER BY _id DESC";
ckon.sqlDataRd = sql.ExecuteDataReader(cmd, ckon.sqlCon());
if (ckon.sqlDataRd.HasRows)
{
while (ckon.sqlDataRd.Read())
{
SHIFT = ckon.sqlDataRd["SHIFT"].ToString();
set_open_trans = Convert.ToInt32(ckon.sqlDataRd["REAL_TRANS_BALANCE"].ToString());
set_open_petty = Convert.ToInt32(ckon.sqlDataRd["REAL_PETTY_CASH"].ToString());
set_open_deposit = Convert.ToInt32(ckon.sqlDataRd["REAL_DEPOSIT"].ToString());
}
if (SHIFT == "1" && status_store == "0")
{
if (set_open_trans == 0)
{
l_cash_amount.Text = "0,00";
}
else
{
l_cash_amount.Text = string.Format("{0:#,###}" + ",00", set_open_trans);
}
if (set_open_petty == 0)
{
l_petty_amount.Text = "0,00";
}
else
{
l_petty_amount.Text = string.Format("{0:#,###}" + ",00", set_open_petty);
}
if (set_open_deposit == 0)
{
l_dep_amount.Text = "0,00";
}
else
{
l_dep_amount.Text = string.Format("{0:#,###}" + ",00", set_open_deposit);
}
}
if (SHIFT == "2" && status_store == "0")
{
if (set_open_trans == 0)
{
l_cash_amount.Text = "0,00";
}
else
{
l_cash_amount.Text = string.Format("{0:#,###}" + ",00", set_open_trans);
}
if (set_open_petty == 0)
{
l_petty_amount.Text = "0,00";
}
else
{
l_petty_amount.Text = string.Format("{0:#,###}" + ",00", set_open_petty);
}
if (set_open_deposit == 0)
{
l_dep_amount.Text = "0,00";
}
else
{
l_dep_amount.Text = string.Format("{0:#,###}" + ",00", set_open_deposit);
}
}
if ((SHIFT == "2" && status_store == "1") || (SHIFT == "1" && status_store == "1"))
{
l_cash_amount.Text = "0,00";
if (set_open_petty == 0)
{
l_petty_amount.Text = "0,00";
}
else
{
l_petty_amount.Text = string.Format("{0:#,###}" + ",00", set_open_petty);
}
if (set_open_deposit == 0)
{
l_dep_amount.Text = "0,00";
}
else
{
l_dep_amount.Text = string.Format("{0:#,###}" + ",00", set_open_deposit);
}
}
}
else
{
l_cash_amount.Text = "0,00";
l_petty_amount.Text = "0,00";
l_dep_amount.Text = "0,00";
}
}
catch (Exception e)
{
MessageBox.Show(e.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (ckon.sqlDataRd != null)
{
ckon.sqlDataRd.Close();
}
if (ckon.sqlCon().State == ConnectionState.Open)
{
ckon.sqlCon().Close();
}
}
}
//=================================GENERATOR NUMBER=================================
public void new_invoice()
{
CRUD sql = new CRUD();
this.ActiveControl = t_barcode;
t_barcode.Focus();
dgv_request.Rows.Clear();
l_amount.Text = "0,00";
l_qty.Text = "0";
no_sj.Text = "";
//ckon.con.Close();
try
{
ckon.sqlCon().Open();
String cmd_store = "SELECT * FROM store";
ckon.sqlDataRd = sql.ExecuteDataReader(cmd_store, ckon.sqlCon());
if (ckon.sqlDataRd.HasRows)
{
while (ckon.sqlDataRd.Read())
{
store_code = ckon.sqlDataRd["CODE"].ToString();
cust_id_store = ckon.sqlDataRd["CUST_ID_STORE"].ToString();
}
}
}
catch (Exception e)
{
MessageBox.Show(e.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (ckon.sqlDataRd != null)
{
ckon.sqlDataRd.Close();
}
if (ckon.sqlCon().State == ConnectionState.Open)
{
ckon.sqlCon().Close();
}
}
}
