public void retreive(String transaksi, String code_store, String id_cust) { koneksi ckon = new koneksi(); koneksi2 ckon2 = new koneksi2(); String art_id, art_name, spg_id, size, color, qty, disc_desc, sub_total2, discount_code, discount_code_get; int price, sub_total, disc, disc_type_new, status_diskon_api; Transaction transaction = new Transaction(); transaction.storeCode = code_store; transaction.customerId = id_cust; List <TransactionLine> transLine = new List <TransactionLine>(); String sql = "SELECT transaction_line._id ,transaction_line.ARTICLE_ID ,transaction_line.QUANTITY, transaction_line.SUBTOTAL, transaction_line.SPG_ID, transaction_line.DISCOUNT, transaction_line.DISCOUNT_DESC,transaction_line.DISCOUNT_TYPE,transaction_line.DISCOUNT_CODE, article.ARTICLE_NAME, article.SIZE, article.COLOR, article.PRICE FROM transaction_line, article WHERE article.ARTICLE_ID = transaction_line.ARTICLE_ID AND transaction_line.TRANSACTION_ID='" + transaksi + "' ORDER BY transaction_line._id ASC"; ckon.cmd = new MySqlCommand(sql, ckon.con); try { ckon.con.Open(); //===================================================================================== ckon.myReader = ckon.cmd.ExecuteReader(); while (ckon.myReader.Read()) { art_id = ckon.myReader.GetString("ARTICLE_ID"); art_name = ckon.myReader.GetString("ARTICLE_NAME"); spg_id = ckon.myReader.GetString("SPG_ID"); size = ckon.myReader.GetString("SIZE"); color = ckon.myReader.GetString("COLOR"); price = ckon.myReader.GetInt32("PRICE"); qty = ckon.myReader.GetString("QUANTITY"); disc_desc = ckon.myReader.GetString("DISCOUNT_DESC"); sub_total = ckon.myReader.GetInt32("SUBTOTAL"); disc = ckon.myReader.GetInt32("DISCOUNT"); disc_type_new = ckon.myReader.GetInt32("DISCOUNT_TYPE"); discount_code = ckon.myReader.GetString("DISCOUNT_CODE"); //============================================================================== string query = "SELECT * FROM ARTICLE WHERE ARTICLE_ID='" + art_id + "'"; ckon2.cmd2 = new MySqlCommand(query, ckon2.con2); ckon2.con2.Open(); ckon2.myReader2 = ckon2.cmd2.ExecuteReader(); Article articleFromDb = new Article(); while (ckon2.myReader2.Read()) { articleFromDb.articleId = ckon2.myReader2.GetString("ARTICLE_ID"); articleFromDb.articleName = ckon2.myReader2.GetString("ARTICLE_NAME"); articleFromDb.brand = ckon2.myReader2.GetString("BRAND"); articleFromDb.color = ckon2.myReader2.GetString("COLOR"); articleFromDb.department = ckon2.myReader2.GetString("DEPARTMENT"); articleFromDb.departmentType = ckon2.myReader2.GetString("DEPARTMENT_TYPE"); articleFromDb.gender = ckon2.myReader2.GetString("GENDER"); articleFromDb.id = ckon2.myReader2.GetInt32("_id"); articleFromDb.price = ckon2.myReader2.GetInt32("PRICE"); articleFromDb.size = ckon2.myReader2.GetString("SIZE"); articleFromDb.unit = ckon2.myReader2.GetString("UNIT"); articleFromDb.articleIdAlias = ckon2.myReader2.GetString("ARTICLE_ID_ALIAS"); } ckon2.con2.Close(); //====================================================================== TransactionLine t = new TransactionLine(); t.discount = disc; t.subtotal = sub_total; t.quantity = Int32.Parse(qty); t.price = price; t.discountType = disc_type_new; t.discountCode = discount_code; t.article = articleFromDb; transLine.Add(t); } //===================================================================================== transaction.transactionLines = transLine; ckon.con.Close(); BiensiPosDbContext.BiensiPosDbDataContext contex = new BiensiPosDbContext.BiensiPosDbDataContext(); DiscountCalculate dc = new DiscountCalculate(contex); DiscountMaster resultData = dc.Post(transaction); Console.WriteLine(JsonConvert.SerializeObject(transaction)); //================================================= //for (int i = 0; i < resultData.discounts.Count; i++) //{ // discount_code_get = resultData.discounts[i].discountCode; // data_diskon(discount_code_get); //} foreach (var c in resultData.discounts) { var b = c.discountApiItems.ToList(); discount_code_get = c.discountCode; status_diskon_api = c.status; String art_diskon = c.articleId; int disc_type = c.discountType; //=================insert ke table disctype2 saat type diskon 2 dan status 1=========== if (c.status == 1 && c.discountType == 2) { String del = "delete from disctype2"; CRUD update = new CRUD(); update.ExecuteNonQuery(del); foreach (var a in b) { var hasil = a.price - a.amountDiscount; String input = "Insert into disctype2 (TransId, articleid, Price, Discount, TotHarga, DiscountRetailId, DiscPersent) values ('" + transaksi + "','" + a.articleId + "','" + a.price + "','" + a.amountDiscount + "','" + hasil + "','" + a.discountCode + "','" + a.discountDesc + "')"; CRUD insert = new CRUD(); insert.ExecuteNonQuery(input); } } if (c.status == 1 && c.discountType == 3) { if (resultData.discountItems != null) { foreach (var a in resultData.discountItems) { int price_real = 0, qty_real = 0, result_real = 0; String search = "Select * from transaction_line where TRANSACTION_ID = '" + transaksi + "' AND ARTICLE_ID = '" + a.articleId + "'"; ckon.cmd = new MySqlCommand(search, ckon.con); ckon.con.Open(); ckon.myReader = ckon.cmd.ExecuteReader(); while (ckon.myReader.Read()) { price_real = ckon.myReader.GetInt32("PRICE"); qty_real = ckon.myReader.GetInt32("QUANTITY"); } ckon.con.Close(); result_real = qty_real * price_real; String update_transline = "UPDATE transaction_line SET SUBTOTAL = '" + result_real + "', Discount = '0', DISCOUNT_TYPE = '" + a.discountType + "', DISCOUNT_CODE = '" + a.discountCode + "', DISCOUNT_DESC = '" + a.discountCode + "' where TRANSACTION_ID = '" + transaksi + "' AND ARTICLE_ID = '" + a.articleId + "'"; CRUD new_query = new CRUD(); new_query.ExecuteNonQuery(update_transline); } } if (c.discountApiItems != null) { foreach (var aa in c.discountApiItems) { int price_real = 0, qty_real = 0, result_real = 0; String coodee = ""; String article_id_update = ""; String search = "Select * from transaction_line where TRANSACTION_ID = '" + transaksi + "' "; ckon.cmd = new MySqlCommand(search, ckon.con); ckon.con.Open(); ckon.myReader = ckon.cmd.ExecuteReader(); while (ckon.myReader.Read()) { //price_real = ckon.myReader.GetInt32("PRICE"); //qty_real = ckon.myReader.GetInt32("QUANTITY"); coodee = ckon.myReader.GetString("DISCOUNT_CODE"); article_id_update = ckon.myReader.GetString("ARTICLE_ID"); result_real = price_real * qty_real; if (coodee == aa.discountCode) { result_real = aa.qty * Convert.ToInt32(aa.price); String update_transline = "UPDATE transaction_line SET SUBTOTAL = '" + result_real + "', Discount = '0', DISCOUNT_TYPE = '" + aa.discountType + "', DISCOUNT_CODE = '" + aa.discountCode + "', DISCOUNT_DESC = '" + aa.discountCode + "' where TRANSACTION_ID = '" + transaksi + "' AND ARTICLE_ID = '" + aa.articleId + "'"; CRUD new_query = new CRUD(); new_query.ExecuteNonQuery(update_transline); } } ckon.con.Close(); } } } //=================UPADTE STATUS DISKON KE DATABASE LOKAL(PROMOTION HEADER)============= } ckon.con.Close(); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } }
//======================INPUT TRANSAKSI LINE======================================== public void save_trans_line() { CRUD sql = new CRUD(); try { ckon.con.Close(); koneksi2 ckon2 = new koneksi2(); ckon.sqlCon().Open(); String cmd = "SELECT * FROM transaction_line WHERE TRANSACTION_ID = '" + id_trans + "'"; ckon.sqlDataRd = sql.ExecuteDataReader(cmd, ckon.sqlCon()); if (ckon.sqlDataRd.HasRows) { while (ckon.sqlDataRd.Read()) { String article_id = ckon.sqlDataRd["ARTICLE_ID"].ToString(); String qty = ckon.sqlDataRd["QUANTITY"].ToString(); String price = ckon.sqlDataRd["PRICE"].ToString(); String subtotal = ckon.sqlDataRd["SUBTOTAL"].ToString(); String discount = ckon.sqlDataRd["DISCOUNT"].ToString(); String spg_id_line = ckon.sqlDataRd["SPG_ID"].ToString(); String dis_code = ckon.sqlDataRd["DISCOUNT_CODE"].ToString(); String dis_type = ckon.sqlDataRd["DISCOUNT_TYPE"].ToString(); String dis_desc = ckon.sqlDataRd["DISCOUNT_DESC"].ToString(); String is_service = ckon.sqlDataRd["IS_SERVICE"].ToString(); String is_omniTrans = ckon.sqlDataRd["IS_OMNITRANS"].ToString(); String omniStoreCode = ckon.sqlDataRd["OMNISTORECODE"].ToString(); String deliv_cust = ckon.sqlDataRd["DELIVERYCUSTADDRESS"].ToString(); String omniShip = ckon.sqlDataRd["OMNISHIPPINGCOST"].ToString(); String omniCourier = ckon.sqlDataRd["OMNICOURIER"].ToString(); String deliv_type = ckon.sqlDataRd["DELIVERYTYPE"].ToString(); String maxDicsQty = ckon.sqlDataRd["MAXDISCOUNTQUANTITY"].ToString(); String maxDiscAmt = ckon.sqlDataRd["MAXDISCOUNTAMOUNT"].ToString(); String discAmt = ckon.sqlDataRd["DISCOUNTAMOUNT"].ToString(); String dis_setupLine = ckon.sqlDataRd["DiscountSetupLines"].ToString(); String cmd_insert = "INSERT INTO transaction_line(TRANSACTION_ID, ARTICLE_ID, QUANTITY, PRICE,DISCOUNT, SUBTOTAL,SPG_ID,DISCOUNT_CODE, DISCOUNT_TYPE, DISCOUNT_DESC, IS_SERVICE, IS_OMNITRANS, OMNISTORECODE, DELIVERYCUSTADDRESS, OMNISHIPPINGCOST, OMNICOURIER, DELIVERYTYPE, MAXDISCOUNTQUANTITY, MAXDISCOUNTAMOUNT, DISCOUNTAMOUNT, DiscountSetupLines) " + "VALUES ('" + new_idTrans + "', '" + article_id + "', '-" + qty + "', '" + price + "', '-" + discount + "','-" + subtotal + "','" + spg_id_line + "','" + dis_code + "', '" + dis_type + "', '" + dis_desc + "', '" + is_service + "', '" + is_omniTrans + "', '" + omniStoreCode + "', '" + deliv_cust + "', '" + omniShip + "', '" + omniCourier + "', '" + deliv_type + "', '" + maxDicsQty + "', '" + maxDiscAmt + "', '" + discAmt + "', '" + dis_setupLine + "')"; sql.ExecuteNonQuery(cmd_insert); } } //ckon.sqlCon().Open(); //String cmd = "SELECT * FROM [tmp].[" + store_code + "] WHERE TRANSACTION_ID = '" + id_trans + "'"; //ckon.sqlDataRd = sql.ExecuteDataReader(cmd, ckon.sqlCon()); //if (ckon.sqlDataRd.HasRows) //{ // while (ckon.sqlDataRd.Read()) // { // String article_id = ckon.sqlDataRd["ARTICLE_ID"].ToString(); // String article_name = ckon.sqlDataRd["ARTICLE_NAME"].ToString(); // String qty = ckon.sqlDataRd["QUANTITY"].ToString(); // String price = ckon.sqlDataRd["PRICE"].ToString(); // String discount = ckon.sqlDataRd["DISCOUNT"].ToString(); // String discAmt = ckon.sqlDataRd["DISCOUNTAMOUNT"].ToString(); // String maxDicsQty = ckon.sqlDataRd["MAXDISCOUNTQUANTITY"].ToString(); // String maxDiscAmt = ckon.sqlDataRd["MAXDISCOUNTAMOUNT"].ToString(); // String subtotal = ckon.sqlDataRd["SUBTOTAL"].ToString(); // String spg_id_line = ckon.sqlDataRd["SPG_ID"].ToString(); // String dis_code = ckon.sqlDataRd["DISCOUNT_CODE"].ToString(); // String dis_type = ckon.sqlDataRd["DISCOUNT_TYPE"].ToString(); // String dis_desc = ckon.sqlDataRd["DISCOUNT_DESC"].ToString(); // String dis_setupLine = ckon.sqlDataRd["DiscountSetupLines"].ToString(); // String is_service = ckon.sqlDataRd["IS_SERVICE"].ToString(); // String is_omniTrans = ckon.sqlDataRd["IS_OMNITRANS"].ToString(); // String omniStoreCode = ckon.sqlDataRd["OMNISTORECODE"].ToString(); // String deliv_cust = ckon.sqlDataRd["DELIVERYCUSTADDRESS"].ToString(); // String omniShip = ckon.sqlDataRd["OMNISHIPPINGCOST"].ToString(); // String omniCourier = ckon.sqlDataRd["OMNICOURIER"].ToString(); // String dis_code_promo = ckon.sqlDataRd["DISCOUNT_CODE_PROMOTION"].ToString(); // string cmd_insert = "INSERT INTO [tmp].[" + store_code + "](TRANSACTION_ID,ARTICLE_ID,ARTICLE_NAME,QUANTITY,PRICE,DISCOUNT,DISCOUNTAMOUNT,MAXDISCOUNTQUANTITY,MAXDISCOUNTAMOUNT,SUBTOTAL,SPG_ID,DISCOUNT_CODE,DISCOUNT_TYPE,DISCOUNT_DESC,DiscountSetupLines,IS_SERVICE,IS_OMNITRANS,OMNISTORECODE,DELIVERYCUSTADDRESS,OMNISHIPPINGCOST,OMNICOURIER,DISCOUNT_CODE_PROMOTION) " + // "values('"+ new_idTrans + "', '"+ article_id + "', '"+ article_name + "', '-"+ qty +"', '"+ price + "', '"+ discount +"', '-"+ discAmt +"', '"+ maxDicsQty +"', '"+ maxDiscAmt +"', '-"+ subtotal +"', '"+ spg_id_line + "', '"+ dis_code + "', '"+ dis_type + "', '"+ dis_desc + "', '"+ dis_setupLine + "', '"+ is_service + "', '"+ is_omniTrans + "', '"+ omniStoreCode + "', '"+ deliv_cust + "', '"+ omniShip + "', '"+ omniCourier + "', '"+ dis_code_promo + "')"; // sql.ExecuteNonQuery(cmd_insert); // } //} } 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(); } } }