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);
}
