public static string EditOrderInfo(string trnId, string itemId, string cartons, string pcs) { string msg = ""; OracleDBConnectionClass objOracleDB = new OracleDBConnectionClass(); try { OracleConnection conn = new OracleConnection(objOracleDB.OracleConnectionString()); conn.Open(); string query = @"UPDATE T_ORDER_DETAIL SET ITEM_CTN='" + cartons + "', ITEM_QTY='" + pcs + "' WHERE TRAN_ID='" + trnId + "' AND ITEM_ID='" + itemId + "'"; OracleCommand cmd = new OracleCommand(query, conn); int i = cmd.ExecuteNonQuery(); if (i > 0) { msg = "Successful!"; } else { msg = "Not Successful!"; } conn.Close(); } catch (Exception ex) { } return(msg); }
public static void ShowOutletPicture(string outletID) { try { string[] outletInfo = outletID.Split('-'); OracleDBConnectionClass objOracleDB = new OracleDBConnectionClass(); OracleConnection con = new OracleConnection(objOracleDB.OracleConnectionString()); con.Open(); string queryOutletInfo = "SELECT OUTLET_IMAGE FROM OUTLET_INFO" + " WHERE OUTLET_ID= '" + outletInfo[0] + "'" + " AND MARCHANDISE='" + outletInfo[3] + "'" + " AND ROUT_SRID='" + outletInfo[2] + "' ORDER BY IMAGE_CAPTURE_DT ASC"; OracleCommand cmdOutletInfo = new OracleCommand(queryOutletInfo, con); OracleDataAdapter daOutletInfo = new OracleDataAdapter(cmdOutletInfo); DataSet dsOutletInfo = new DataSet(); daOutletInfo.Fill(dsOutletInfo); int c = dsOutletInfo.Tables[0].Rows.Count; if (c > 0) { byte[] binData = (byte[])dsOutletInfo.Tables[0].Rows[0]["OUTLET_IMAGE"]; //ViewImage(binData); //image.src = "data:image/png;base64," + Convert.ToBase64String(binData); } con.Close(); con.Dispose(); } catch (Exception ex) { } }
public static string DelDistFreeItemInfo(string itemId, string ocN, string free) { string msg = ""; OracleDBConnectionClass objOracleDB = new OracleDBConnectionClass(); try { OracleConnection conn = new OracleConnection(objOracleDB.OracleConnectionString()); conn.Open(); string query = @"DELETE FROM T_DIST_FREE_ITEM WHERE OC_NUMBER='" + ocN + "' AND ITEM_CODE='" + itemId + "' AND FREE_ITEM ='" + free + "'"; OracleCommand cmd = new OracleCommand(query, conn); int c = cmd.ExecuteNonQuery(); if (c > 0) { msg = "Successfuly Deleted"; } else { msg = "Not Successful!"; } conn.Close(); } catch (Exception ex) { } return(msg); }
public static string GetWarehouseInfo(string distId) { string msg = ""; OracleDBConnectionClass objOracleDB = new OracleDBConnectionClass(); try { OracleConnection conn = new OracleConnection(objOracleDB.OracleConnectionString()); conn.Open(); string query = @"SELECT WH_CODE,WH_NAME FROM T_WARE_HOUSE WHERE DIST_CODE='" + distId + "'"; OracleCommand cmd = new OracleCommand(query, conn); OracleDataAdapter da = new OracleDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds); int c = ds.Tables[0].Rows.Count; if (c > 0) { string whId = ds.Tables[0].Rows[0]["WH_CODE"].ToString(); string whName = ds.Tables[0].Rows[0]["WH_NAME"].ToString(); msg = msg + ";" + whId + ";" + whName; } else { msg = "No WH"; } conn.Close(); } catch (Exception ex) { } return(msg); }
private Customers GetData() { OracleDBConnectionClass objOracleDB = new OracleDBConnectionClass(); OracleConnection conn = new OracleConnection(objOracleDB.OracleConnectionString()); conn.Open(); string sql = "SELECT * FROM T_REPORT"; OracleCommand cmd = new OracleCommand(sql, conn); //OracleDataAdapter da = new OracleDataAdapter(cmd); //da.Fill(dsReport, "DataTable1"); //conn.Close(); using (OracleDataAdapter da = new OracleDataAdapter()) { cmd.Connection = conn; da.SelectCommand = cmd; using (Customers dsCustomers = new Customers()) { da.Fill(dsCustomers, "DataTable1"); return(dsCustomers); } } }
public static string AddCustomerInfo(string customerName, string phone, string address, string email, string outlet, string zone, string remarks) { string msg = ""; OracleDBConnectionClass objOracleDB = new OracleDBConnectionClass(); try { OracleConnection conn = new OracleConnection(objOracleDB.OracleConnectionString()); conn.Open(); string querys = @"INSERT INTO T_MODERN_TRADE(CUSTOMER_NAME,PHONE,ADDRESS,EMAIL,OUTLET,ZONE,REMARKS,ENTRY_DATE) VALUES ('" + customerName + "','" + phone + "','" + address + "','" + email + "','" + outlet + "','" + zone + "','" + remarks + "',TO_DATE(SYSDATE))"; OracleCommand cmds = new OracleCommand(querys, conn); int cs = cmds.ExecuteNonQuery(); if (cs > 0) { msg = "Successful!"; } else { msg = "Not Successful"; } conn.Close(); } catch (Exception ex) { } return(msg); }
public static string GetDistName(string distId) { string msg = ""; OracleDBConnectionClass objOracleDB = new OracleDBConnectionClass(); try { OracleConnection conn = new OracleConnection(objOracleDB.OracleConnectionString()); conn.Open(); string query = @"SELECT DIST_ID,DIST_NAME FROM T_DISTRIBUTOR WHERE DIST_ID='" + distId + "'"; OracleCommand cmd = new OracleCommand(query, conn); OracleDataAdapter da = new OracleDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds); int c = ds.Tables[0].Rows.Count; if (c > 0) { string dtId = ds.Tables[0].Rows[0]["DIST_ID"].ToString(); string distName = ds.Tables[0].Rows[0]["DIST_NAME"].ToString(); msg = distName; } else { msg = "No Distributor"; } conn.Close(); } catch (Exception ex) { } return(msg); }
public static string GetSR(string dist) { string msg = ""; OracleDBConnectionClass objOracleDB = new OracleDBConnectionClass(); try { OracleConnection conn = new OracleConnection(objOracleDB.OracleConnectionString()); conn.Open(); string qr = @"SELECT SR_ID,SR_NAME FROM T_SR_INFO WHERE DIST_ID='" + dist + "' AND STATUS='Y'"; OracleCommand cmdSR = new OracleCommand(qr, conn); OracleDataAdapter daSR = new OracleDataAdapter(cmdSR); DataSet ds = new DataSet(); daSR.Fill(ds); int i = ds.Tables[0].Rows.Count; if (i > 0 && ds.Tables[0].Rows[0]["SR_ID"].ToString() != "") { for (int j = 0; j < i; j++) { string SR_ID = ds.Tables[0].Rows[j]["SR_ID"].ToString(); string SR_NAME = ds.Tables[0].Rows[j]["SR_NAME"].ToString(); msg = msg + ";" + SR_ID + ";" + SR_NAME; } } conn.Close(); } catch (Exception ex) { } return(msg); }
public static string GetDistributor(string company) { string msg = ""; OracleDBConnectionClass objOracleDB = new OracleDBConnectionClass(); try { OracleConnection conn = new OracleConnection(objOracleDB.OracleConnectionString()); conn.Open(); string qr = @"SELECT DIST_ID,DIST_NAME FROM T_DISTRIBUTOR WHERE STATUS='Y' AND ITEM_GROUP IN( SELECT ITEM_GROUP_ID FROM T_ITEM_GROUP WHERE COMPANY_ID='" + company + "' AND STATUS='Y')"; OracleCommand cmdSR = new OracleCommand(qr, conn); OracleDataAdapter daSR = new OracleDataAdapter(cmdSR); DataSet ds = new DataSet(); daSR.Fill(ds); int i = ds.Tables[0].Rows.Count; if (i > 0 && ds.Tables[0].Rows[0]["DIST_ID"].ToString() != "") { for (int j = 0; j < i; j++) { string distId = ds.Tables[0].Rows[j]["DIST_ID"].ToString(); string distName = ds.Tables[0].Rows[j]["DIST_NAME"].ToString(); msg = msg + ";" + distId + ";" + distName; } } conn.Close(); } catch (Exception ex) { } return(msg); }
public static List <Group> getData(string querySR, string id, string name) { List <Group> objSRList = new List <Group>(); Group objSR = null; try { OracleDBConnectionClass objOracleDB = new OracleDBConnectionClass(); OracleConnection con = new OracleConnection(objOracleDB.OracleConnectionString()); con.Open(); OracleCommand cmdSR = new OracleCommand(querySR, con); OracleDataAdapter daSR = new OracleDataAdapter(cmdSR); DataSet dsSR = new DataSet(); daSR.Fill(dsSR); int c = dsSR.Tables[0].Rows.Count; if (c > 0) { for (int i = 0; i < c; i++) { objSR = new Group(); objSR.outletID = dsSR.Tables[0].Rows[i][id].ToString(); objSR.outletName = objSR.outletID + " - " + dsSR.Tables[0].Rows[i][name].ToString(); objSRList.Add(objSR); } } con.Close(); con.Dispose(); } catch (Exception ex) { } return(objSRList); }
public static string AddDistStockFreeItem(string freeItem, string freeQty, string distId, string whId, string itemId, string carton, string piece, string receivingDate, string ocNos) { string msg = ""; OracleDBConnectionClass objOracleDB = new OracleDBConnectionClass(); try { OracleConnection conn = new OracleConnection(objOracleDB.OracleConnectionString()); conn.Open(); string query = @"INSERT INTO T_DIST_FREE_ITEM(DIST_CODE,WH_CODE,ITEM_CODE,ITEM_CTN,ITEM_PCS,FREE_ITEM,FREE_QTY,RECEIVING_DATE,ENTRY_DATE,ENTRY_BY,OC_NUMBER) VALUES('" + distId + "','" + whId + "','" + itemId + "','" + carton + "','" + piece + "','" + freeItem + "','" + freeQty + "',TO_DATE('" + receivingDate + "','DD/MM/YYYY'),SYSDATE,'" + HttpContext.Current.Session["userid"].ToString() + "','" + ocNos + "')"; OracleCommand cmd = new OracleCommand(query, conn); int c = cmd.ExecuteNonQuery(); if (c > 0) { msg = "Successful!"; } else { msg = "Not Successful!"; } conn.Close(); } catch (Exception ex) { } return(msg); }
public DataTable getAllOrders() { DataSet ds = new DataSet(); OracleDBConnectionClass objOracleDB = new OracleDBConnectionClass(); OracleConnection conn = new OracleConnection(objOracleDB.OracleConnectionString()); try { conn.Open(); string querys = @"SELECT SR_NAME NAME,EMAIL_ADDRESS ADDRESS FROM T_SR_INFO"; OracleCommand cmds = new OracleCommand(querys, conn); OracleDataAdapter da = new OracleDataAdapter(cmds); da.Fill(ds, "DataTable1"); } catch (Exception ex) { throw new Exception(ex.Message); } finally { if (conn.State != ConnectionState.Closed) { conn.Close(); } } return(ds.Tables[0]); }
public static string GetItemInformation(string itemId) { string msg = ""; OracleDBConnectionClass objOracleDB = new OracleDBConnectionClass(); try { OracleConnection conn = new OracleConnection(objOracleDB.OracleConnectionString()); conn.Open(); string query = @"SELECT FACTOR,FACTOR_CATEGORY,DP FROM T_ITEM WHERE ITEM_ID='" + itemId + "' AND ACTIVENESS='Y'"; OracleCommand cmd = new OracleCommand(query, conn); OracleDataAdapter da = new OracleDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds); int c = ds.Tables[0].Rows.Count; if (c > 0) { string FACTOR = ds.Tables[0].Rows[0]["FACTOR"].ToString(); string FACTOR_CATEGORY = ds.Tables[0].Rows[0]["FACTOR_CATEGORY"].ToString(); string DP = ds.Tables[0].Rows[0]["DP"].ToString(); msg = msg + ";" + FACTOR + ";" + FACTOR_CATEGORY + ";" + DP; } else { msg = "No WH"; } conn.Close(); } catch (Exception ex) { } return(msg); }
public static string GetProductGroupInfo(string distId) { string msg = ""; OracleDBConnectionClass objOracleDB = new OracleDBConnectionClass(); try { OracleConnection conn = new OracleConnection(objOracleDB.OracleConnectionString()); conn.Open(); string query = @"SELECT ITEM_GROUP_ID,ITEM_GROUP_NAME FROM T_ITEM_GROUP WHERE ITEM_GROUP_ID IN(SELECT ITEM_GROUP FROM T_DISTRIBUTOR WHERE DIST_ID='" + distId + "' AND STATUS='Y')"; OracleCommand cmd = new OracleCommand(query, conn); OracleDataAdapter da = new OracleDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds); int c = ds.Tables[0].Rows.Count; if (c > 0) { string whId = ds.Tables[0].Rows[0]["ITEM_GROUP_ID"].ToString(); string whName = ds.Tables[0].Rows[0]["ITEM_GROUP_NAME"].ToString(); msg = msg + ";" + whId + ";" + whName; } else { msg = "No WH"; } conn.Close(); } catch (Exception ex) { } return(msg); }
public static string GetDistSingleItemInfo(string itemId, string ocN) { string msg = ""; OracleDBConnectionClass objOracleDB = new OracleDBConnectionClass(); try { OracleConnection conn = new OracleConnection(objOracleDB.OracleConnectionString()); conn.Open(); string query = @"SELECT T1.*,T2.WH_NAME FROM (SELECT * FROM T_DIST_STOCK WHERE ITEM_ID ='" + itemId + "' AND OC_NUMBER='" + ocN + "') T1,(SELECT WH_CODE,WH_NAME FROM T_WARE_HOUSE) T2 WHERE T1.WH_CODE=T2.WH_CODE"; OracleCommand cmd = new OracleCommand(query, conn); OracleDataAdapter da = new OracleDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds); int c = ds.Tables[0].Rows.Count; if (c > 0) { for (int i = 0; i < c; i++) { string distId = ds.Tables[0].Rows[i]["DIST_CODE"].ToString(); string distName = ds.Tables[0].Rows[i]["DIST_NAME"].ToString(); string whId = ds.Tables[0].Rows[i]["WH_CODE"].ToString(); string itemGroupId = ds.Tables[0].Rows[i]["ITEM_GROUP"].ToString(); string itemCode = ds.Tables[0].Rows[i]["ITEM_ID"].ToString(); string carton = ds.Tables[0].Rows[i]["ITEM_CTN"].ToString(); string piece = ds.Tables[0].Rows[i]["ITEM_PCS"].ToString(); string factor = ds.Tables[0].Rows[i]["FACTOR"].ToString(); string factorType = ds.Tables[0].Rows[i]["FACTOR_TYPE"].ToString(); string dpPrice = ds.Tables[0].Rows[i]["DP_PRICE"].ToString(); string remarks = ds.Tables[0].Rows[i]["REMARKS"].ToString(); string d = Convert.ToDateTime(ds.Tables[0].Rows[i]["RECEIVING_DATE"].ToString()).Day.ToString(); d = d.Length == 1 ? "0" + d : d; string m = Convert.ToDateTime(ds.Tables[0].Rows[i]["RECEIVING_DATE"].ToString()).Month.ToString(); m = m.Length == 1 ? "0" + m : m; string y = Convert.ToDateTime(ds.Tables[0].Rows[i]["RECEIVING_DATE"].ToString()).Year.ToString(); string recdate = d + "/" + m + "/" + y; msg = msg + ";" + ocN + ";" + distId + ";" + distName + ";" + whId + ";" + itemGroupId + ";" + itemCode + ";" + carton + ";" + piece + ";" + dpPrice + ";" + factor + ";" + factorType + ";" + recdate + ";" + remarks; } } else { msg = "No Info"; } conn.Close(); } catch (Exception ex) { } return(msg); }
public static string GetBaseInfo(string baseId) { string msg = ""; OracleDBConnectionClass objOracleDB = new OracleDBConnectionClass(); try { OracleConnection conn = new OracleConnection(objOracleDB.OracleConnectionString()); conn.Open(); string query = ""; if (baseId == "baseId") { query = @"SELECT * FROM T_BASE_INFO"; } else { query = @"SELECT * FROM T_BASE_INFO WHERE BASE_ID='" + baseId + "'"; } OracleCommand cmd = new OracleCommand(query, conn); OracleDataAdapter da = new OracleDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds); int c = ds.Tables[0].Rows.Count; if (c > 0) { for (int i = 0; i < c; i++) { string BASE_ID = ds.Tables[0].Rows[i]["BASE_ID"].ToString(); string BSSE_NAME = ds.Tables[0].Rows[i]["BASE_NAME"].ToString(); string COUNTRY_NAME = ds.Tables[0].Rows[i]["COUNTRY_NAME"].ToString(); string DIVISION_ID = ds.Tables[0].Rows[i]["REGION_ID"].ToString(); string DIVISION_NAME = ds.Tables[0].Rows[i]["REGION_NAME"].ToString(); string ZONE_ID = ds.Tables[0].Rows[i]["ZONE_ID"].ToString(); string ZONE_NAME = ds.Tables[0].Rows[i]["ZONE_NAME"].ToString(); string STATUS = ds.Tables[0].Rows[i]["STATUS"].ToString(); msg = msg + ";" + BASE_ID + ";" + BSSE_NAME + ";" + COUNTRY_NAME + ";" + DIVISION_ID + ";" + DIVISION_NAME + ";" + STATUS + ";" + ZONE_ID + ";" + ZONE_NAME; } } else { msg = "Not Exist"; } conn.Close(); } catch (Exception ex) { } return(msg); }
public DataTable getAllOrders() { DataSet ds = new DataSet(); OracleDBConnectionClass objOracleDB = new OracleDBConnectionClass(); OracleConnection conn = new OracleConnection(objOracleDB.OracleConnectionString()); try { conn.Open(); string querys = @"SELECT T2.TRAN_ID,T1.* FROM (SELECT OUTLET_ID,OUTLET_NAME,OUTLET_ADDRESS,PROPRITOR_NAME,MOBILE_NUMBER FROM T_OUTLET WHERE OUTLET_ID IN( SELECT DISTINCT OUTLET_ID FROM T_TRANSACTION WHERE ENTRY_DATE=TO_DATE('" + orderdate + "','DD/MM/YYYY') AND SR_ID='" + srid + "')) T1, "; querys = querys + @"(SELECT * FROM T_ORDER_HEADER WHERE ENTRY_DATE=TO_DATE('" + orderdate + "','DD/MM/YYYY')) T2 WHERE T1.OUTLET_ID=T2.OUTLET_ID"; OracleCommand cmds = new OracleCommand(querys, conn); OracleDataAdapter da = new OracleDataAdapter(cmds); DataSet dSet = new DataSet(); da.Fill(dSet); int c = dSet.Tables[0].Rows.Count; if (c > 0 && dSet.Tables[0].Rows[0]["TRAN_ID"].ToString() != "") { for (int i = 0; i < c; i++) { string OC = dSet.Tables[0].Rows[i]["TRAN_ID"].ToString(); string OUTLET_ID = dSet.Tables[0].Rows[i]["OUTLET_ID"].ToString(); string OUTLET_NAME = dSet.Tables[0].Rows[i]["OUTLET_NAME"].ToString(); string OUTLET_ADDRESS = dSet.Tables[0].Rows[i]["OUTLET_ADDRESS"].ToString(); string PROPRITOR_NAME = dSet.Tables[0].Rows[i]["PROPRITOR_NAME"].ToString(); string MOBILE_NUMBER = dSet.Tables[0].Rows[i]["MOBILE_NUMBER"].ToString(); } } da.Fill(ds, "DataTable1"); } catch (Exception ex) { throw new Exception(ex.Message); } finally { if (conn.State != ConnectionState.Closed) { conn.Close(); } } return(ds.Tables[0]); }
public static string GetCOOInfo(string coo) { string msg = ""; OracleDBConnectionClass objOracleDB = new OracleDBConnectionClass(); try { OracleConnection conn = new OracleConnection(objOracleDB.OracleConnectionString()); conn.Open(); string query = ""; if (coo == "coo") { query = @"SELECT COO_ID,COO_NAME,DESIGNATION,MOTHER_COMPANY,OWN_COMPANY,PASS_WORD,STATUS FROM T_COO WHERE COMPANY_ID='" + HttpContext.Current.Session["company"].ToString().Trim() + "'"; } else { query = @"SELECT COO_ID,COO_NAME,DESIGNATION,MOTHER_COMPANY,OWN_COMPANY,PASS_WORD,STATUS FROM T_COO WHERE COO_ID='" + coo + "'"; } OracleCommand cmd = new OracleCommand(query, conn); OracleDataAdapter da = new OracleDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds); int c = ds.Tables[0].Rows.Count; if (c > 0) { for (int i = 0; i < c; i++) { string COO_ID = ds.Tables[0].Rows[i]["COO_ID"].ToString(); string COO_NAME = ds.Tables[0].Rows[i]["COO_NAME"].ToString(); string DESIGNATION = ds.Tables[0].Rows[i]["DESIGNATION"].ToString(); string MOTHER_COMPANY = ds.Tables[0].Rows[i]["MOTHER_COMPANY"].ToString(); string OWN_COMPANY = ds.Tables[0].Rows[i]["OWN_COMPANY"].ToString(); string PASS_WORD = ds.Tables[0].Rows[i]["PASS_WORD"].ToString(); string STATUS = ds.Tables[0].Rows[i]["STATUS"].ToString(); msg = msg + ";" + COO_ID + ";" + COO_NAME + ";" + DESIGNATION + ";" + MOTHER_COMPANY + ";" + OWN_COMPANY + ";" + PASS_WORD + ";" + STATUS; } } else { msg = "NotExist"; } conn.Close(); } catch (Exception ex) { } return(msg); }
public static string GetOutletWiseOrder(string srId, string orderDate, string outletId) { string msg = ""; OracleDBConnectionClass objOracleDB = new OracleDBConnectionClass(); try { OracleConnection conn = new OracleConnection(objOracleDB.OracleConnectionString()); conn.Open(); string query = @"SELECT T4.*,T3.TRAN_ID,T3.ITEM_CTN,T3.ITEM_QTY,T3.ENTRY_DATE FROM (SELECT DISTINCT T2.TRAN_ID,T2.ITEM_ID,T2.ITEM_CTN,T2.ITEM_QTY,T2.ENTRY_DATE FROM (SELECT TRAN_ID,SR_ID,ROUTE_ID FROM T_ORDER_HEADER WHERE ENTRY_DATE=TO_DATE('" + orderDate.Trim() + "','DD/MM/YYYY') AND SR_ID='" + srId + "' AND OUTLET_ID='" + outletId + "') T1, "; query = query + @"(SELECT * FROM T_ORDER_DETAIL) T2 WHERE T1.TRAN_ID=T2.TRAN_ID) T3, (SELECT ITEM_ID,ITEM_NAME FROM T_ITEM) T4 WHERE T3.ITEM_ID=T4.ITEM_ID"; OracleCommand cmd = new OracleCommand(query, conn); OracleDataAdapter da = new OracleDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds); int c = ds.Tables[0].Rows.Count; if (c > 0) { for (int i = 0; i < c; i++) { string trnId = ds.Tables[0].Rows[i]["TRAN_ID"].ToString(); string itemId = ds.Tables[0].Rows[i]["ITEM_ID"].ToString(); string itemName = ds.Tables[0].Rows[i]["ITEM_NAME"].ToString(); string carton = ds.Tables[0].Rows[i]["ITEM_CTN"].ToString(); string pcs = ds.Tables[0].Rows[i]["ITEM_QTY"].ToString(); string entryDate = Convert.ToDateTime(ds.Tables[0].Rows[i]["ENTRY_DATE"].ToString()).ToShortDateString(); msg = msg + ";" + itemId + ";" + itemName + ";" + carton + ";" + pcs + ";" + entryDate + ";" + trnId; } } else { msg = "Not Order"; } conn.Close(); } catch (Exception ex) { } return(msg); }
public string GetInfoFirstTime(string Date_Time) { //SR_ID = "152015"; //Date_Time = "5/2/2017"; string msg = ""; OracleDBConnectionClass objOracleDB = new OracleDBConnectionClass(); try { OracleConnection con = new OracleConnection(objOracleDB.OracleConnectionString()); con.Open(); // string srC = @"SELECT title,lat,lng, (OUTLET_NAME||','||UploadTime) description FROM // (SELECT DISTINCT T1.TRAN_ID title,T1.LATITUDE lat, // T1.LONGITUDE lng, T2.OUTLET_NAME,to_char( T1.ENTRY_DATETIME , 'HH24:MI:SS' ) UploadTime FROM T_ORDER_DETAIL T1 // INNER JOIN T_OUTLET T2 ON T1.OUTLET_ID=T2.OUTLET_ID // WHERE T1.LATITUDE !=0 AND T1.ENTRY_DATE=TO_DATE('" + Date_Time + "','DD/MM/YYYY') ORDER BY UploadTime )"; string qrC = @"SELECT title,lat,lng, ('Name: ' || SR_NAME || '(' || SR_ID || ')<br/>Mobile No: ' || MOBILE_NO || '<br/>Group: ' || ITEM_GROUP || '<br/>Outlet: ' || OUTLET_NAME||'<br/>Order Time: '||UploadTime || '<br/>Route: ' || ROUTE_NAME) description FROM (SELECT DISTINCT T1.TRAN_ID title,T1.LATITUDE lat, T1.LONGITUDE lng, T2.OUTLET_NAME,to_char( T1.ENTRY_DATETIME , 'HH24:MI:SS' ) UploadTime, T3.SR_NAME,T3.MOBILE_NO,T1.SR_ID,T4.ROUTE_NAME,T3.ITEM_GROUP FROM T_ORDER_DETAIL T1 INNER JOIN T_OUTLET T2 ON T1.OUTLET_ID=T2.OUTLET_ID INNER JOIN T_SR_INFO T3 ON T3.SR_ID=T1.SR_ID INNER JOIN T_ROUTE T4 ON T1.ROUTE_ID=T4.ROUTE_ID WHERE T1.LATITUDE !=0 AND T1.ENTRY_DATE=TO_DATE('" + Date_Time + "','DD/MM/YYYY') ORDER BY UploadTime)"; OracleCommand cC = new OracleCommand(qrC, con); OracleDataAdapter daC = new OracleDataAdapter(cC); DataSet dsC = new DataSet(); daC.Fill(dsC); int cS = dsC.Tables[0].Rows.Count; con.Close(); int c = dsC.Tables[0].Rows.Count; return(JsonConvert.SerializeObject(dsC.Tables[0])); } catch (Exception ex) { msg = ex.ToString(); } return(msg); }
public static string GetDivisionInfo(string division) { string msg = ""; OracleDBConnectionClass objOracleDB = new OracleDBConnectionClass(); try { OracleConnection conn = new OracleConnection(objOracleDB.OracleConnectionString()); conn.Open(); string query = ""; if (division == "division") { query = @"SELECT * FROM T_DIVISION WHERE COUNTRY_NAME LIKE '%" + HttpContext.Current.Session["country"].ToString().Trim() + "%' ORDER BY DIVISION_NAME"; } else { query = @"SELECT * FROM T_DIVISION WHERE DIVISION_ID='" + division + "' ORDER BY DIVISION_NAME"; } OracleCommand cmd = new OracleCommand(query, conn); OracleDataAdapter da = new OracleDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds); int c = ds.Tables[0].Rows.Count; if (c > 0) { for (int i = 0; i < c; i++) { string DIVISION_ID = ds.Tables[0].Rows[i]["DIVISION_ID"].ToString(); string DIVISION_NAME = ds.Tables[0].Rows[i]["DIVISION_NAME"].ToString(); string COUNTRY_NAME = ds.Tables[0].Rows[i]["COUNTRY_NAME"].ToString(); string STATUS = ds.Tables[0].Rows[i]["STATUS"].ToString(); msg = msg + ";" + DIVISION_ID + ";" + DIVISION_NAME + ";" + COUNTRY_NAME + ";" + STATUS; } } else { msg = "Not Exist"; } conn.Close(); } catch (Exception ex) { } return(msg); }
public static string GetCustomerinfo(string dtes) { string msg = ""; OracleDBConnectionClass objOracleDB = new OracleDBConnectionClass(); try { OracleConnection conn = new OracleConnection(objOracleDB.OracleConnectionString()); conn.Open(); string query = @"SELECT * FROM T_MODERN_TRADE WHERE ENTRY_DATE=TO_DATE('" + dtes + "','DD/MM/YYYY')"; OracleCommand cmd = new OracleCommand(query, conn); OracleDataAdapter da = new OracleDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds); int c = ds.Tables[0].Rows.Count; if (c > 0) { for (int i = 0; i < c; i++) { string CUSTOMER_NAME = ds.Tables[0].Rows[i]["CUSTOMER_NAME"].ToString(); string PHONE = ds.Tables[0].Rows[i]["PHONE"].ToString(); string ADDRESS = ds.Tables[0].Rows[i]["ADDRESS"].ToString(); string EMAIL = ds.Tables[0].Rows[i]["EMAIL"].ToString(); string OUTLET = ds.Tables[0].Rows[i]["OUTLET"].ToString(); string ZONE = ds.Tables[0].Rows[i]["ZONE"].ToString(); string REMARKS = ds.Tables[0].Rows[i]["REMARKS"].ToString(); string ENTRY_DATE = Convert.ToDateTime(ds.Tables[0].Rows[i]["ENTRY_DATE"].ToString()).ToShortDateString(); msg = msg + ";" + CUSTOMER_NAME + ";" + PHONE + ";" + ADDRESS + ";" + EMAIL + ";" + OUTLET + ";" + ZONE + ";" + REMARKS + ";" + ENTRY_DATE; } } else { msg = "Not Exist"; } conn.Close(); } catch (Exception ex) { } return(msg); }
public static List <Group> LoadAllZone(string groupID) { List <Group> objZoneList = new List <Group>(); Group objZone = null; try { OracleDBConnectionClass objOracleDB = new OracleDBConnectionClass(); OracleConnection con = new OracleConnection(objOracleDB.OracleConnectionString()); con.Open(); string group = ""; if (groupID == "PRAN") { group = "PRAN-CS-Others"; } string queryZone = @"SELECT ZONE_ID,ZONE_NAME FROM T_MKTG_ZONE WHERE ZONE_ID IN(SELECT ZONE_ID FROM T_MKTG_SR_INFO WHERE GROUP_NAME LIKE '%" + group + "%')"; OracleCommand cmdZone = new OracleCommand(queryZone, con); OracleDataAdapter daZone = new OracleDataAdapter(cmdZone); DataSet dsZone = new DataSet(); daZone.Fill(dsZone); int c = dsZone.Tables[0].Rows.Count; if (c > 0) { for (int i = 0; i < c; i++) { objZone = new Group(); objZone.zoneID = dsZone.Tables[0].Rows[i]["ZONE_ID"].ToString(); objZone.zoneName = dsZone.Tables[0].Rows[i]["ZONE_NAME"].ToString(); objZoneList.Add(objZone); } } con.Close(); con.Dispose(); } catch (Exception ex) { } return(objZoneList.ToList()); }
public static string GetDistStockInfo(string ocNum) { string msg = ""; OracleDBConnectionClass objOracleDB = new OracleDBConnectionClass(); try { OracleConnection conn = new OracleConnection(objOracleDB.OracleConnectionString()); conn.Open(); string query = @"SELECT T1.*,T2.WH_NAME FROM (SELECT * FROM T_DIST_STOCK WHERE OC_NUMBER='" + ocNum + "') T1,(SELECT WH_CODE,WH_NAME FROM T_WARE_HOUSE) T2 WHERE T1.WH_CODE=T2.WH_CODE"; OracleCommand cmd = new OracleCommand(query, conn); OracleDataAdapter da = new OracleDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds); int c = ds.Tables[0].Rows.Count; if (c > 0) { for (int i = 0; i < c; i++) { string distId = ds.Tables[0].Rows[i]["DIST_CODE"].ToString(); string distName = ds.Tables[0].Rows[i]["DIST_NAME"].ToString(); string whId = ds.Tables[0].Rows[i]["WH_CODE"].ToString(); string whName = ds.Tables[0].Rows[i]["WH_NAME"].ToString(); string itemCode = ds.Tables[0].Rows[i]["ITEM_ID"].ToString(); string itemName = ds.Tables[0].Rows[i]["ITEM_NAME"].ToString(); string carton = ds.Tables[0].Rows[i]["ITEM_CTN"].ToString(); string piece = ds.Tables[0].Rows[i]["ITEM_PCS"].ToString(); string recdate = Convert.ToDateTime(ds.Tables[0].Rows[i]["RECEIVING_DATE"].ToString()).ToShortDateString(); msg = msg + ";" + ocNum + ";" + distId + ";" + distName + ";" + whId + ";" + whName + ";" + itemCode + ";" + itemName + ";" + carton + ";" + piece + ";" + recdate; } } else { msg = "No Info"; } conn.Close(); } catch (Exception ex) { } return(msg); }
public static string AddBaseInfo(string baseId, string baseName, string zoneId, string zoneName, string regionId, string regionName, string country, string status) { string msg = ""; OracleDBConnectionClass objOracleDB = new OracleDBConnectionClass(); try { OracleConnection conn = new OracleConnection(objOracleDB.OracleConnectionString()); conn.Open(); string qr = @"SELECT BASE_ID FROM T_BASE_INFO WHERE BASE_ID='" + baseId + "'"; OracleCommand cmdSR = new OracleCommand(qr, conn); OracleDataAdapter daSR = new OracleDataAdapter(cmdSR); DataSet ds = new DataSet(); daSR.Fill(ds); int i = ds.Tables[0].Rows.Count; if (i > 0 && ds.Tables[0].Rows[0]["BASE_ID"].ToString() != "") { string qrS = @"DELETE FROM T_BASE_INFO WHERE BASE_ID='" + baseId + "'"; OracleCommand cmdS = new OracleCommand(qrS, conn); int cS = cmdS.ExecuteNonQuery(); } string query = @"INSERT INTO T_BASE_INFO(BASE_ID,BASE_NAME,COUNTRY_NAME,ZONE_ID,ZONE_NAME,REGION_ID,REGION_NAME,STATUS) VALUES ('" + baseId + "','" + baseName + "','" + country + "','" + zoneId + "','" + zoneName + "','" + regionId + "','" + regionName + "','" + status + "')"; OracleCommand cmd = new OracleCommand(query, conn); int c = cmd.ExecuteNonQuery(); if (c > 0) { msg = "Successful!"; } else { msg = "Not Successful"; } conn.Close(); } catch (Exception ex) { } return(msg); }
public static string AddZoneInfo(string zoneId, string zoneName, string division, string country, string status) { string msg = ""; OracleDBConnectionClass objOracleDB = new OracleDBConnectionClass(); try { OracleConnection conn = new OracleConnection(objOracleDB.OracleConnectionString()); conn.Open(); string qr = @"SELECT ZONE_ID FROM T_ZONE WHERE ZONE_ID='" + zoneId + "'"; OracleCommand cmdSR = new OracleCommand(qr, conn); OracleDataAdapter daSR = new OracleDataAdapter(cmdSR); DataSet ds = new DataSet(); daSR.Fill(ds); int i = ds.Tables[0].Rows.Count; if (i > 0 && ds.Tables[0].Rows[0]["ZONE_ID"].ToString() != "") { string qrS = @"DELETE FROM T_ZONE WHERE ZONE_ID='" + zoneId + "'"; OracleCommand cmdS = new OracleCommand(qrS, conn); int cS = cmdS.ExecuteNonQuery(); } string query = @"INSERT INTO T_ZONE(ZONE_ID,ZONE_NAME,DIVISION_ID,COUNTRY_NAME,STATUS,ENTRY_DATE,ENTRY_BY) VALUES ('" + zoneId + "','" + zoneName + "','" + division + "','" + country + "','" + status + "',TO_DATE(SYSDATE),'" + HttpContext.Current.Session["userid"].ToString() + "')"; OracleCommand cmd = new OracleCommand(query, conn); int c = cmd.ExecuteNonQuery(); if (c > 0) { msg = "Successful!"; } else { msg = "Not Successful"; } conn.Close(); } catch (Exception ex) { } return(msg); }
public static string GetDistFreeStockInfo(string ocNumber) { string msg = ""; OracleDBConnectionClass objOracleDB = new OracleDBConnectionClass(); try { OracleConnection conn = new OracleConnection(objOracleDB.OracleConnectionString()); conn.Open(); string query = @"SELECT OC_NUMBER,ITEM_CODE,FREE_ITEM,FREE_QTY FROM T_DIST_FREE_ITEM WHERE OC_NUMBER='" + ocNumber + "'"; OracleCommand cmd = new OracleCommand(query, conn); OracleDataAdapter da = new OracleDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds); int c = ds.Tables[0].Rows.Count; if (c > 0) { for (int i = 0; i < c; i++) { string ocN = ds.Tables[0].Rows[i]["OC_NUMBER"].ToString(); string itemCode = ds.Tables[0].Rows[i]["ITEM_CODE"].ToString(); string freeItem = ds.Tables[0].Rows[i]["FREE_ITEM"].ToString(); string qty = ds.Tables[0].Rows[i]["FREE_QTY"].ToString(); msg = msg + ";" + ocN + ";" + itemCode + ";" + freeItem + ";" + qty; } } else { msg = "No Info"; } conn.Close(); } catch (Exception ex) { } return(msg); }
public static List <Group> LoadAllOutlet(string zoneID) { List <Group> objOutletList = new List <Group>(); Group objOutlet = null; try { OracleDBConnectionClass objOracleDB = new OracleDBConnectionClass(); OracleConnection con = new OracleConnection(objOracleDB.OracleConnectionString()); con.Open(); string queryOutlet = @"SELECT DISTINCT OUTLET_INFO.OUTLET_ID OUTLET_ID,T_OULT.OULT_NAME OUTLET_NAME FROM OUTLET_INFO LEFT JOIN T_OULT ON OUTLET_INFO.OUTLET_ID=T_OULT.OULT_ID WHERE T_OULT.OULT_ZONE='" + zoneID + "'"; OracleCommand cmdOutlet = new OracleCommand(queryOutlet, con); OracleDataAdapter daOutlet = new OracleDataAdapter(cmdOutlet); DataSet dsOutlet = new DataSet(); daOutlet.Fill(dsOutlet); int c = dsOutlet.Tables[0].Rows.Count; if (c > 0) { for (int i = 0; i < c; i++) { objOutlet = new Group(); objOutlet.outletID = dsOutlet.Tables[0].Rows[i]["OUTLET_ID"].ToString(); objOutlet.outletName = dsOutlet.Tables[0].Rows[i]["OUTLET_NAME"].ToString(); objOutletList.Add(objOutlet); } } con.Close(); con.Dispose(); } catch (Exception ex) { } return(objOutletList.ToList()); }
protected void Button1_Click(object sender, EventArgs e) { try { //var connectionString = ConfigurationManager.ConnectionStrings["OracleDBMain"].ConnectionString; // string constr = @"Data Source=(DESCRIPTION= // (ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost )(PORT=1522))) // (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=PAL))); // User Id=OUTLET;Password=outlet"; OracleDBConnectionClass objOracleDB = new OracleDBConnectionClass(); OracleConnection conn = new OracleConnection(objOracleDB.OracleConnectionString()); conn.Open(); string sql = "SELECT * FROM TBL_EMP"; OracleCommand command = new OracleCommand(sql, conn); OracleDataAdapter da = new OracleDataAdapter(command); DataSet ds = new DataSet(); da.Fill(ds); int c = ds.Tables[0].Rows.Count; if (c > 0) { } else { string q = "INSERT INTO TBL_EMP(ID,NAME) VALUES('139399','Rathindra Nath')"; OracleCommand cmd = new OracleCommand(q, conn); int i = cmd.ExecuteNonQuery(); string msg = "Inserted!"; } conn.Close(); } catch (Exception ex) { } }
public string GetDivision(string countryName) { string msg = ""; OracleDBConnectionClass objOracleDB = new OracleDBConnectionClass(); try { OracleConnection conn = new OracleConnection(objOracleDB.OracleConnectionString()); conn.Open(); // string query = @"SELECT DIVISION_ID,DIVISION_NAME FROM T_DIVISION // WHERE COUNTRY_NAME LIKE '%" + HttpContext.Current.Session["country"].ToString() + "%'"; string query = @"SELECT DIVISION_ID,DIVISION_NAME FROM T_DIVISION WHERE COUNTRY_NAME LIKE '%" + countryName + "%' ORDER BY DIVISION_NAME ASC"; OracleCommand cmd = new OracleCommand(query, conn); OracleDataAdapter da = new OracleDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds); int c = ds.Tables[0].Rows.Count; if (c > 0) { for (int i = 0; i < c; i++) { string comId = ds.Tables[0].Rows[i]["DIVISION_ID"].ToString(); string comName = ds.Tables[0].Rows[i]["DIVISION_NAME"].ToString(); msg = msg + ";" + comId + ";" + comName; } } else { msg = "NotExist"; } conn.Close(); } catch (Exception ex) { } return(msg); }