public void LogToDatabase()
{
//MMD_spLogAutoUpload
SqlService sql = new SqlService(_connection);
sql.AddParameter("@pMessage", System.Data.SqlDbType.VarChar, Message);
try
{
sql.ExecuteSP("MMD_spLogAutoUpload");
}
catch (Exception ex)
{
throw new Exception("AutoUploadLog.LogToDatabase: " + ex.Message);
}
}
/// <summary>
/// MMD_spLogPlanningOfficerMessage
/// </summary>
public void LogMessage()
{
SqlService sql = new SqlService(_connectionString);
sql.AddParameter("@pMessage", System.Data.SqlDbType.VarChar, Message);
sql.AddParameter("@pCustomerMobile", System.Data.SqlDbType.BigInt, CustomerMobile);
sql.AddParameter("@pDateSent", System.Data.SqlDbType.DateTime, DateSent);
try
{
sql.ExecuteSP("MMD_spLogPlanningOfficerMessage");
}
catch (Exception ex)
{
throw new Exception("PlanningOfficerMessage.LogMessage: " + ex.Message + Environment.NewLine + sql.SqlQuery);
}
}
/// <summary>
/// Set the auth token in the database according to user
/// </summary>
/// <param name="authToken"></param>
/// <param name="email"></param>
public bool SetAuthToken(string authToken, string email)
{
SqlService sql = new SqlService(_sqlConnectionString);
sql.AddParameter("@pEmail", System.Data.SqlDbType.VarChar, email);
sql.AddParameter("@pAuthToken", System.Data.SqlDbType.VarChar, authToken);
using (System.Data.SqlClient.SqlDataReader reader = sql.ExecuteSPReader("ICMS_spPasswordlessLogin"))
{
if (reader.HasRows)
{
return true;
}
}
return false;
}
/// <summary>
/// Authorize user
/// </summary>
/// <param name="authToken"></param>
/// <param name="email"></param>
/// <returns></returns>
public string Authorize(string authToken, string email)
{
SqlService sql = new SqlService(_sqlConnectionString);
sql.AddParameter("@pAuthToken", System.Data.SqlDbType.VarChar, authToken);
sql.AddParameter("@pEmail", System.Data.SqlDbType.VarChar, email);
using (SqlDataReader reader = sql.ExecuteSPReader("ICMS_spVerifyAuthToken"))
{
if (reader.HasRows)
{
reader.Read();
return reader.SafeGetString(reader.GetOrdinal("Id"));
}
}
return string.Empty;
}
/// <summary>
/// MMD_spUpdateTTReportTime
/// </summary>
/// <param name="connectionString"></param>
public void UpdateOutletReportTime()
{
SqlService sql = new SqlService(_connectionString);
//int timeLen = TTReportTime.Length;
//int insertLen = timeLen - 2;
TTReportTime = TTReportTime.Insert(TTReportTime.Length - 2, ":");
sql.AddParameter("@pOrdIdent", System.Data.SqlDbType.VarChar, TTId.Trim());
sql.AddParameter("@pOutletReported", System.Data.SqlDbType.Time, TTReportTime);
try
{
sql.ExecuteSP("MMD_spUpdateTTReportTime");
}
catch (Exception ex)
{
//Logging.AutoUploadLog log = new Logging.AutoUploadLog(
}
}
private void BuildTTStatusMessage()
{
SqlService sql = new SqlService(_connectionString);
sql.AddParameter("@pTTId", System.Data.SqlDbType.VarChar, _ttId);
try
{
using (System.Data.SqlClient.SqlDataReader reader = sql.ExecuteSPReader("MMD_spGetTTStatus"))
{
if (reader.HasRows)
{
reader.Read();
int status = reader.GetInt32(reader.GetOrdinal("OrdStatus"));
//bool paymentRcvd = reader.GetInt32(reader.GetOrdinal("OrdPaymentRcvd")) == 1 ? true : false;
ResponseMessage = "Your tank truck " + reader.GetString(reader.GetOrdinal("TTNo"));
if (status == 5)
{
ResponseMessage += " allocated with " + reader.GetString(reader.GetOrdinal("CustName"));
ResponseMessage += " order and left terminal at " + reader.GetString(reader.GetOrdinal("ttOut"));
}
else
{
ResponseMessage += " reported. Allocated with " + reader.GetString(reader.GetOrdinal("CustName")) + " and still inside terminal.";
}
//ResponseMessage = "TT reported at " + reader.GetDateTime(reader.GetOrdinal("lastDateIn")).ToString("dd-mm-yy HH:mm");
//Your tank truck MH01L6407 allocated with M/s Auto Fuels order and left from terminal at 13:25 PM
//Your tank truck MH01L6407 reported. Allocated with M/s Auto Fuels order and still inside the terminal
//“Your Tank Truck MH01L6407 is not reported to terminal
}
else
{
ResponseMessage = "Your Tank Truck is not reported to terminal.";
}
}
}
catch (Exception ex)
{
}
}
///// <summary>
///// Buil message for TT staus inquiry
///// </summary>
//private void BuildTTStatusMessage()
//{
// SqlService sql = new SqlService(_connectionString);
// sql.AddParameter("@pTTId", System.Data.SqlDbType.VarChar, _ttId);
// try
// {
// using (System.Data.SqlClient.SqlDataReader reader = sql.ExecuteSPReader("MMD_spGetTTStatus"))
// {
// if (reader.HasRows)
// {
// reader.Read();
// ResponseMessage = "TT reported at " + reader.GetDateTime(reader.GetOrdinal("lastDateIn")).ToString("YYYY-mm-dd HH:mm");
// }
// else
// ResponseMessage = "TT not found.";
// }
// }
// catch (Exception ex)
// {
// }
//}
/// <summary>
/// Check system for mobile number and get customer k
/// MMD_spGetCustomerFromMobile @pMobileNo = 454545
/// </summary>
public bool ValidCustomerMobile()
{
SqlService sql = new SqlService(_connectionString);
try
{
sql.AddParameter("@pMobileNo", System.Data.SqlDbType.BigInt, _mobileNo);
using (System.Data.SqlClient.SqlDataReader reader = sql.ExecuteSPReader("MMD_spGetCustomerFromMobile"))
{
if (reader.HasRows)
return true;
else
return false;
}
}
catch
{
return false;
}
}
public void UploadTTIn()
{
BulkLoad load = new BulkLoad(_datasheetPath, WorksheetName.TTIn[0], WorksheetName.TTIn[1], _connectionString, null);
SqlService sql = new SqlService(_connectionString);
try
{
sql.ExecuteSP("MMD_spProcessTTIn");
}
catch (Exception ex)
{
throw new Exception("UploadTTIn: " + ex.Message + Environment.NewLine + sql.SqlQuery);
}
}
public void UploadNotAllocated()
{
string sqlNotAllocated = "SELECT [Indent NO], [For Date], [Dealer], [Dlr#], [MSE], [MSEP], [HSDE], [HSDET], [MS], [MSP], [HSD],";
sqlNotAllocated += "[HSDT],[MSEF], [MSEFP], [HSDEF], [HSDEFT], [SKO], [SKOH], [Indent Date], [PAYMENT] ";
BulkLoad load = new BulkLoad(_datasheetPath, WorksheetName.NotAllocated[0], WorksheetName.NotAllocated[1], _connectionString, sqlNotAllocated);
SqlService sql = new SqlService(_connectionString);
try
{
sql.ExecuteSP("MMD_spProcessTmpNotAllocatedOrders");
}
catch (Exception ex)
{
throw new Exception("UploadNotAllocated: " + ex.Message + Environment.NewLine + sql.SqlQuery);
}
}
public void UploadAllocated()
{
string sqlAllocated = "SELECT [Indent No#], [For Date & Indent Date], [Truck No], [Dealer], [Invoice No], [MSE], [MSEP], [HSDE], [HSDET], [MS], [MSP], [HSD],";
sqlAllocated += "[HSDT],[MSEF], [MSEFP], [HSDEF], [HSDEFT], [SKO], [SKOH] ";
BulkLoad load = new BulkLoad(_datasheetPath, WorksheetName.Allocated[0], WorksheetName.Allocated[1], _connectionString, sqlAllocated);
//run sproc to process temp table into new orders
//MMD_spProcessTmpAllocatedOrders
SqlService sql = new SqlService(_connectionString);
try
{
sql.ExecuteSP("MMD_spProcessTmpAllocatedOrders");
}
catch (Exception ex)
{
throw new Exception("UploadAllocated: " + ex.Message + Environment.NewLine + sql.SqlQuery);
}
}
/// <summary>
/// Get the ordered products list. ICMS has up to 5 products non zero
/// MMD_spGetOrderedProducts
/// </summary>
private void GetOrderedProducts()
{
SqlService sql = new SqlService(_connectionString);
sql.AddParameter("@pIndentNo", System.Data.SqlDbType.VarChar, _ordIndent);
try
{
using (System.Data.SqlClient.SqlDataReader reader = sql.ExecuteSPReader("MMD_spGetOrderedProducts"))
{
if (reader.HasRows)
{
reader.Read();
Product1 = reader.GetString(0);
Amount1 = reader.GetInt32(1);
Product2 = reader.GetString(2);
Amount2 = reader.GetInt32(3);
Product3 = reader.GetString(4);
Amount3 = reader.GetInt32(5);
Product4 = reader.GetString(6);
Amount4 = reader.GetInt32(7);
Product5 = reader.GetString(8);
Amount5 = reader.GetInt32(9);
}
}
}
catch (Exception ex)
{
throw new Exception("GetOrderedProducts: " + ex.Message + Environment.NewLine + sql.SqlQuery);
}
}
/// <summary>
/// get oder details
/// MMD_spGetOrderDetails
/// </summary>
public void GetOrderDetails()
{
SqlService sql = new SqlService(_connectionString);
sql.AddParameter("@pIndentNo", System.Data.SqlDbType.VarChar, IndentNo);
try
{
using (System.Data.SqlClient.SqlDataReader reader = sql.ExecuteSPReader("MMD_spGetOrderDetails"))
{
if (reader.HasRows)
{
reader.Read();
TTID = reader.GetString(reader.GetOrdinal("TTID"));
TTNo = reader.GetString(reader.GetOrdinal("TTNo"));
DeliveryDate = reader.GetString(reader.GetOrdinal("OrdDelvDate"));
IndentNo = reader.GetString(reader.GetOrdinal("OrdIdentNo"));
Status = reader.GetString(reader.GetOrdinal("OrdStatus"));
StatusCode = reader.GetInt32(reader.GetOrdinal("OrdStatusCode"));
if (reader.GetValue(reader.GetOrdinal("closedDateTime")) != DBNull.Value)
ClosedDateTime = reader.GetString(reader.GetOrdinal("closedDateTime"));
PaymentPending = reader.GetBoolean(reader.GetOrdinal("OrdPaymentRcvd"));
}
}
}
catch (Exception ex)
{
throw new Exception("GetOrderDetails: " + ex.Message + Environment.NewLine + sql.SqlQuery);
}
}