//public DataSet GetSystemAdminInfo(string admin, string pwd)
//{
// string query = string.Format("SELECT * FROM SYSTEM_ADMIN WHERE SYS_ADMIN = @SYS_ADMIN AND SYS_PWD = @SYS_PWD");
// SqlParameter[] sqlParameters = new SqlParameter[2];
// sqlParameters[0] = new SqlParameter("@SYS_ADMIN", SqlDbType.NVarChar);
// sqlParameters[0].Value = Convert.ToString(admin);
// sqlParameters[1] = new SqlParameter("@SYS_PWD", SqlDbType.NVarChar);
// sqlParameters[1].Value = Convert.ToString(pwd);
// DataTable tblResult = m_dbConnection.ExecuteSelectQuery(query, sqlParameters);
// if (tblResult.Rows.Count > 0)
// {
// DataSet ds = new DataSet();
// ds.Tables.Add(tblResult.Copy());
// return ds;
// }
// return null;
//}
public SystemAdminDTO GetSystemAdminInfo(string admin, string pwd)
{
SystemAdminDTO dtoSysAdmin = null;
string query = string.Format("SELECT * FROM SYSTEM_ADMIN WHERE SYS_ADMIN = @SYS_ADMIN AND SYS_PWD = @SYS_PWD");
SqlParameter[] sqlParameters = new SqlParameter[2];
sqlParameters[0] = new SqlParameter("@SYS_ADMIN", SqlDbType.NVarChar);
sqlParameters[0].Value = Convert.ToString(admin);
sqlParameters[1] = new SqlParameter("@SYS_PWD", SqlDbType.NVarChar);
sqlParameters[1].Value = Convert.ToString(pwd);
DataTable tblResult = m_dbConnection.ExecuteSelectQuery(query, sqlParameters);
if (tblResult.Rows.Count > 0)
{
dtoSysAdmin = new SystemAdminDTO();
foreach (DataRow dr in tblResult.Rows)
{
dtoSysAdmin.SysAdminAccount = dr["SYS_ADMIN"].ToString();
dtoSysAdmin.SysAdminPwd = dr["SYS_PWD"].ToString();
dtoSysAdmin.SysGas92CurrentPrice = Int32.Parse(dr["SYS_GAS92_CURRENT_PRICE"].ToString());
dtoSysAdmin.SysGas95CurrentPrice = Int32.Parse(dr["SYS_GAS95_CURRENT_PRICE"].ToString());
dtoSysAdmin.SysGasDOCurrentPrice = Int32.Parse(dr["SYS_GASDO_CURRENT_PRICE"].ToString());
dtoSysAdmin.SysGas92NewPrice = Int32.Parse(dr["SYS_GAS92_NEW_PRICE"].ToString());
dtoSysAdmin.SysGas95NewPrice = Int32.Parse(dr["SYS_GAS95_NEW_PRICE"].ToString());
dtoSysAdmin.SysGasDONewPrice = Int32.Parse(dr["SYS_GASDO_NEW_PRICE"].ToString());
dtoSysAdmin.SysApplyDate = DateTime.Parse(dr["SYS_APPLY_DATE"].ToString());
}
}
return(dtoSysAdmin);
}
public CardDTO GetCard(string stCardID)
{
CardDTO dtoCard = null;
string query = string.Format("SELECT * FROM CARD WHERE CARD_ID = @CARD_ID");
SqlParameter[] sqlParameters = new SqlParameter[1];
sqlParameters[0] = new SqlParameter("@CARD_ID", SqlDbType.NVarChar);
sqlParameters[0].Value = Convert.ToString(stCardID);
DataTable tblResult = m_dbConnection.ExecuteSelectQuery(query, sqlParameters);
if (tblResult.Rows.Count > 0)
{
dtoCard = new CardDTO();
foreach (DataRow dr in tblResult.Rows)
{
dtoCard.CardID = dr["CARD_ID"].ToString();;
dtoCard.CardUnlockState = Boolean.Parse(dr["CARD_STATE"].ToString());
dtoCard.CardRemainingMoney = Int32.Parse(dr["CARD_MONEY"].ToString());
dtoCard.CardMoneyEx = Int32.Parse(dr["CARD_MONEY_EX"].ToString());
dtoCard.CardBuyDate = DateTime.Parse(dr["CARD_BUY_DATE"].ToString());
dtoCard.RechargeID = Int32.Parse(dr["RECHARGE_ID"].ToString());
dtoCard.CustomerID = dr["CUS_ID"].ToString();
}
}
return(dtoCard);
}
public SaleGasDTO GetSaleGas(int iSaleGasID)
{
SaleGasDTO dtoSaleGas = null;
string query = string.Format("SELECT * FROM SALE_GAS WHERE SALEGAS_ID = @SALEGAS_ID");
SqlParameter[] sqlParameters = new SqlParameter[1];
sqlParameters[0] = new SqlParameter("@SALEGAS_ID", SqlDbType.Int);
sqlParameters[0].Value = iSaleGasID;
DataTable tblResult = m_dbConnection.ExecuteSelectQuery(query, sqlParameters);
if (tblResult.Rows.Count > 0)
{
dtoSaleGas = new SaleGasDTO();
foreach (DataRow dr in tblResult.Rows)
{
dtoSaleGas.SaleGasID = Int32.Parse(dr["SALEGAS_ID"].ToString());
dtoSaleGas.SaleGasType = dr["SALEGAS_TYPE"].ToString();
dtoSaleGas.SaleGasCurrentPrice = Int32.Parse(dr["SALEGAS_CURRENT_PRICE"].ToString());
dtoSaleGas.SaleGasPriceOnCard = Int32.Parse(dr["SALEGAS_CARD_PRICE"].ToString());
dtoSaleGas.SaleGasCardMoneyBefore = Int32.Parse(dr["SALEGAS_CARD_MONEY_BEFORE"].ToString());
dtoSaleGas.SaleGasCardMoneyAfter = Int32.Parse(dr["SALEGAS_CARD_MONEY_AFTER"].ToString());
dtoSaleGas.GasStationID = dr["GASSTATION_ID"].ToString();
dtoSaleGas.SaleGasDate = DateTime.Parse(dr["SALEGAS_DATE"].ToString());
dtoSaleGas.CardID = dr["CARD_ID"].ToString();
dtoSaleGas.NumberBuyLit = float.Parse(dr["SALEGAS_LIT"].ToString());
}
}
return(dtoSaleGas);
}
public DataTransfer GetCustomers(string value)
{
DataTransfer dataResult = new DataTransfer();
try
{
string query;
if (value == null)
{
query = string.Format("SELECT CUS_ID AS STT, * FROM CUSTOMER");
}
else
{
query = string.Format("SELECT CUS_ID AS STT, * FROM CUSTOMER WHERE CUS_ID LIKE '%" + value + "%' OR CUS_NAME LIKE '%" + value + "%'");
}
DataTable tblResult = m_dbConnection.ExecuteSelectQuery(query, new SqlParameter[0]);
if (tblResult.Rows.Count > 0)
{
DataSet ds = new DataSet();
ds.Tables.Add(tblResult.Copy());
dataResult.ResponseDataSet = ds;
}
dataResult.ResponseCode = DataTransfer.RESPONSE_CODE_SUCCESS;
}
catch (Exception ex)
{
dataResult.ResponseCode = DataTransfer.RESPONSE_CODE_FAIL;
dataResult.ResponseErrorMsg = SGMText.CUSTOMER_GET_CUS_ERR;
dataResult.ResponseErrorMsgDetail = ex.Message + " : " + ex.StackTrace;
}
return(dataResult);
}
public RechargeDTO GetRecharge(int iRechargeID)
{
RechargeDTO dtoRecharge = null;
string query = string.Format("SELECT * FROM RECHARGE WHERE RECHARGE_ID = @RECHARGE_ID");
SqlParameter[] sqlParameters = new SqlParameter[1];
sqlParameters[0] = new SqlParameter("@RECHARGE_ID", SqlDbType.Int);
sqlParameters[0].Value = Convert.ToInt32(iRechargeID);
DataTable tblResult = m_dbConnection.ExecuteSelectQuery(query, sqlParameters);
if (tblResult.Rows.Count > 0)
{
dtoRecharge = new RechargeDTO();
foreach (DataRow dr in tblResult.Rows)
{
dtoRecharge.RechargeID = Int32.Parse(dr["RECHARGE_ID"].ToString());
dtoRecharge.RechargeDate = DateTime.Parse(dr["RECHARGE_DATE"].ToString());
dtoRecharge.RechargeGas92Price = Int32.Parse(dr["RECHARGE_GAS92_PRICE"].ToString());
dtoRecharge.RechargeGas95Price = Int32.Parse(dr["RECHARGE_GAS95_PRICE"].ToString());
dtoRecharge.RechargeGasDOPrice = Int32.Parse(dr["RECHARGE_GASDO_PRICE"].ToString());
dtoRecharge.RechargeMoney = Int32.Parse(dr["RECHARGE_MONEY"].ToString());
dtoRecharge.RechargeMoneyEx = Int32.Parse(dr["RECHARGE_MONEY_EX"].ToString());
dtoRecharge.RechargeNote = dr["RECHARGE_NOTE"].ToString();
dtoRecharge.CardID = dr["CARD_ID"].ToString();
}
}
return(dtoRecharge);
}
public DataSet GetGasStationList(string stGasStoreID)
{
DataSet ds = null;
string query = string.Format("SELECT GASSTATION_ID, GASSTATION_NAME FROM GAS_STATION ");
DataTable tblResult = null;
if (stGasStoreID == null || stGasStoreID.Equals(""))
{
tblResult = m_dbConnection.ExecuteSelectQuery(query, null);
}
else
{
query += "WHERE GASSTORE_ID = @GASSTORE_ID";
SqlParameter[] sqlParameters = new SqlParameter[1];
sqlParameters[0] = new SqlParameter("@GASSTORE_ID", SqlDbType.NVarChar);
sqlParameters[0].Value = Convert.ToString(stGasStoreID);
tblResult = m_dbConnection.ExecuteSelectQuery(query, sqlParameters);
}
if (tblResult.Rows.Count > 0)
{
ds = new DataSet();
ds.Tables.Add(tblResult.Copy());
}
return(ds);
}
public DataTransfer ValidateGasStoreLogin(string stGasStoreID, string stGasStoreMacAddress)
{
DataTransfer dataResult = new DataTransfer();
try
{
string query = string.Format("SELECT ISNULL(GASSTORE_MACADDRESS,'NULL') AS RESULT, * FROM GAS_STORE WHERE (GASSTORE_MACADDRESS IS NULL OR GASSTORE_MACADDRESS = @GASSTORE_MACADDRESS) AND GASSTORE_ID = @GASSTORE_ID");
SqlParameter[] sqlParameters = new SqlParameter[2];
sqlParameters[0] = new SqlParameter("@GASSTORE_MACADDRESS", SqlDbType.NVarChar);
sqlParameters[0].Value = Convert.ToString(stGasStoreMacAddress);
sqlParameters[1] = new SqlParameter("@GASSTORE_ID", SqlDbType.NVarChar);
sqlParameters[1].Value = Convert.ToString(stGasStoreID);
DataTable tblResult = m_dbConnection.ExecuteSelectQuery(query, sqlParameters);
if (tblResult.Rows.Count > 0)
{
dataResult.ResponseCode = DataTransfer.RESPONSE_CODE_SUCCESS;
DataRow dr = tblResult.Rows[0];
GasStoreDTO dtoGasStore = new GasStoreDTO();
dtoGasStore.GasStoreID = dr["GASSTORE_ID"].ToString();
dtoGasStore.GasStoreName = dr["GASSTORE_NAME"].ToString();
dtoGasStore.GasStoreAddress = dr["GASSTORE_ADDRESS"].ToString();
dtoGasStore.GasStoreDescription = dr["GASSTORE_DESCRIPTION"].ToString();
dtoGasStore.GasStoreMacAddress = dr["GASSTORE_MACADDRESS"].ToString();
dtoGasStore.GasStoreGas92Total = float.Parse(dr["GASSTORE_GAS92_TOTAL"].ToString());
dtoGasStore.GasStoreGas95Total = float.Parse(dr["GASSTORE_GAS95_TOTAL"].ToString());
dtoGasStore.GasStoreGasDOTotal = float.Parse(dr["GASSTORE_GASDO_TOTAL"].ToString());
dataResult.ResponseDataGasStoreDTO = dtoGasStore;
string result = dr["RESULT"].ToString();
if (result.Equals("NULL")) //insert mac address
{
SqlParameter[] sqlParametersUpdate = new SqlParameter[2];
sqlParametersUpdate[0] = new SqlParameter("@GASSTORE_MACADDRESS", SqlDbType.NVarChar);
sqlParametersUpdate[0].Value = Convert.ToString(stGasStoreMacAddress);
sqlParametersUpdate[1] = new SqlParameter("@GASSTORE_ID", SqlDbType.NVarChar);
sqlParametersUpdate[1].Value = Convert.ToString(stGasStoreID);
query = string.Format("UPDATE GAS_STORE SET GASSTORE_MACADDRESS = @GASSTORE_MACADDRESS WHERE GASSTORE_ID = @GASSTORE_ID");
if (!m_dbConnection.ExecuteUpdateQuery(query, sqlParametersUpdate))
{
dataResult.ResponseCode = DataTransfer.RESPONSE_CODE_FAIL;
dataResult.ResponseErrorMsg = SGMText.GAS_STATION_LOGON_UPDATE_MACADR_ERR;
}
}
}
else
{
dataResult.ResponseCode = DataTransfer.RESPONSE_CODE_FAIL;
dataResult.ResponseErrorMsg = SGMText.GAS_STATION_LOGON_ID_INVALID;
}
}
catch (Exception ex)
{
dataResult.ResponseCode = DataTransfer.RESPONSE_CODE_FAIL;
dataResult.ResponseErrorMsg = SGMText.GAS_STATION_LOGON_ERR;
dataResult.ResponseErrorMsgDetail = ex.Message + " : " + ex.StackTrace;
}
return(dataResult);
}