public string GetResponse(string message, string mobileNo)
{
if (!CanBuildResponse(message))
{
throw new Exception("Message not in correct format.");
}
//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
//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
//TODO use the Truck or Order Repository??? maybe not so much
string responseMessage = string.Empty;
string[] ttMessageParts = message.Remove(0, 2).Trim().Split(',');
if (ttMessageParts.Length == 2)
{
SqlService sql = new SqlService(_sqlConnectionString);
sql.AddParameter("@pTTId", System.Data.SqlDbType.VarChar, ttMessageParts[1]);
using (System.Data.SqlClient.SqlDataReader reader = sql.ExecuteSPReader("ICMS_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.";
}
return responseMessage;
}
else
{
return "Your Tank Truck is not reported to terminal.";
}
}
}
return "Message not in correct format.";
}
/// <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)
{
System.Diagnostics.Trace.TraceError(string.Format("DOING passwordless login auth token [{0}] email [{1}] connection [{2}]", authToken, email, _sqlConnectionString));
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;
}
public static string GetAuthToken(string email)
{
// TEST_spGetAuthToken @pEmailAddress = '*****@*****.**'
string token = string.Empty;
string sqlConnection = @"Server=PUKETI\SQL2012EXPRESS;Database=ICMSMaster_v2;Integrated Security=True";
SqlService sql = new SqlService(sqlConnection);
sql.AddParameter("@pEmailAddress", System.Data.SqlDbType.VarChar, email);
using (System.Data.SqlClient.SqlDataReader reader = sql.ExecuteSPReader("TEST_spGetAuthToken"))
{
if (reader.HasRows)
{
reader.Read();
token = reader.GetString(0);
}
}
return token;
}
public void ProcessFile(string pathToFile)
{
string excelQuery = "SELECT * FROM [" + WorksheetName.TTIn[0] + "$]";
BulkLoad load = new BulkLoad(pathToFile,
WorksheetName.TTIn[0],
WorksheetName.TTIn[1],
_sqlConnectionString, excelQuery);
load.LoadData();
SqlService sql = new SqlService(_sqlConnectionString);
try
{
sql.ExecuteSP("MMD_spProcessTTIn");
}
catch (Exception ex)
{
throw new Exception("UploadTTIn: " + ex.Message + Environment.NewLine + sql.SqlQuery);
}
}
public void ProcessFile(string pathToFile)
{
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] ";
sqlNotAllocated += string.Format(@"FROM [{0}$]", WorksheetName.NotAllocated[0]);
BulkLoad load = new BulkLoad(pathToFile,
WorksheetName.NotAllocated[0],
WorksheetName.NotAllocated[1],
_sqlConnectionString, sqlNotAllocated);
load.LoadData();
SqlService sql = new SqlService(_sqlConnectionString);
try
{
sql.ExecuteSP("MMD_spProcessTmpNotAllocatedOrders");
}
catch (Exception ex)
{
throw new Exception("UploadNotAllocated: " + ex.Message + Environment.NewLine + sql.SqlQuery);
}
}
public string GetResponse(string message, string mobileNo)
{
if (!CanBuildResponse(message))
{
throw new Exception("Message not in correct format.");
}
//TODO can I do this with the messages repository?
SqlService sql = new SqlService(_sqlConnectionString);
sql.AddParameter("@pMessage", System.Data.SqlDbType.VarChar, message);
sql.AddParameter("@pCustomerMobile", System.Data.SqlDbType.BigInt, mobileNo);
sql.AddParameter("@pDateSent", System.Data.SqlDbType.DateTime, DateTime.Now);
try
{
sql.ExecuteSP("MMD_spLogPlanningOfficerMessage");
}
catch (Exception ex)
{
throw new Exception("PlanningOfficerMessage: " + ex.Message + Environment.NewLine + sql.SqlQuery);
}
return "Your message received. Planning officer will take action and inform you accordingly.";
}
public void ProcessFile(string pathToFile)
{
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] ";
sqlAllocated += string.Format(@"FROM [{0}$]", WorksheetName.Allocated[0]);
BulkLoad load = new BulkLoad(pathToFile,
WorksheetName.Allocated[0],
WorksheetName.Allocated[1], _sqlConnectionString, sqlAllocated);
load.LoadData();
//run sproc to process temp table into new orders
//MMD_spProcessTmpAllocatedOrders
SqlService sql = new SqlService(_sqlConnectionString);
try
{
sql.ExecuteSP("MMD_spProcessTmpAllocatedOrders");
}
catch (Exception ex)
{
throw new Exception("UploadAllocated: " + ex.Message + Environment.NewLine + sql.SqlQuery);
}
}
public string GetResponse(string message, string mobileNo)
{
// HPICMS
//R:14000674,13:45
if (!CanBuildResponse(message))
{
throw new Exception("Message not in correct format.");
}
SqlService sql = new SqlService(_sqlConnectionString);
//int timeLen = TTReportTime.Length;
//int insertLen = timeLen - 2;
//TODO convert message time to time usable by MS SQL
string[] reportMessage = message.Remove(0, 2).Split(',');
if (reportMessage.Length == 2)
{
string ttId = reportMessage[0].Trim();
string ttReportTime = reportMessage[1].Trim();
//TTReportTime = TTReportTime.Insert(TTReportTime.Length - 2, ":");
sql.AddParameter("@pOrdIdent", System.Data.SqlDbType.VarChar, ttId);
sql.AddParameter("@pOutletReported", System.Data.SqlDbType.Time, ttReportTime);
try
{
sql.ExecuteSP("MMD_spUpdateTTReportTime");
return "Your TT Reported time logged. Thank you, HPCL Wadala(Mumbai) Terminal";
}
catch (Exception ex)
{
//Logging.AutoUploadLog log = new Logging.AutoUploadLog(
throw new Exception("Failed update TT Report time: " + ex.Message);
}
}
return "Message not in correct format";
}
private IEnumerable<string> GetUnits(Guid packageId)
{
SqlService sql = new SqlService(_sqlConnectionString);
sql.AddParameter("@pPackageId", System.Data.SqlDbType.UniqueIdentifier, packageId);
IList<string> list = new List<string>();
using (SqlDataReader reader = sql.ExecuteSPReader("spGetPackageUnits"))
{
if (reader.HasRows)
{
while (reader.Read())
{
string unit = string.Empty;
unit = reader.SafeGetString(reader.GetOrdinal("unit"));
list.Add(unit);
}
}
}
return list;
}
private IEnumerable<Package> GetPackageList(Guid itemId)
{
SqlService sql = new SqlService(_sqlConnectionString);
sql.AddParameter("@pItemId", System.Data.SqlDbType.UniqueIdentifier, itemId);
IList<Package> list = new List<Package>();
list.Add(new Package() { Id = Guid.Empty, Name = string.Empty });
using (SqlDataReader reader = sql.ExecuteSPReader("spGetPackagesForItem"))
{
if (reader.HasRows)
{
while (reader.Read())
{
Package package = new Package();
package.Id = reader.SafeGetGuid(reader.GetOrdinal("Id")).Value;
package.Name = reader.SafeGetString(reader.GetOrdinal("Name"));
list.Add(package);
}
}
}
return list;
}
private IEnumerable<Item> GetItemsList(Guid id)
{
SqlService sql = new SqlService(_sqlConnectionString);
sql.AddParameter("@pCategoryId", System.Data.SqlDbType.UniqueIdentifier, id);
IList<Item> list = new List<Item>();
list.Add(new Item()
{ Id=Guid.Empty, WebName=string.Empty });
using (SqlDataReader reader = sql.ExecuteSPReader("spGetItems"))
{
if (reader.HasRows)
{
while(reader.Read())
{
Item item = new Item();
item.Id = reader.SafeGetGuid(reader.GetOrdinal("Id")).Value;
item.WebName = reader.SafeGetString(reader.GetOrdinal("WebName"));
list.Add(item);
}
}
}
return list;
}
public IEnumerable<Message> GetListByUserId(Guid id)
{
//ICMS_spGetMessagesById
IList<Message> list = new List<Message>();
SqlService sql = new SqlService(_sqlConnection);
sql.AddParameter("@pId", System.Data.SqlDbType.UniqueIdentifier, id);
using (SqlDataReader reader = sql.ExecuteSPReader("ICMS_spGetMessagesById"))
{
if (reader.HasRows)
{
while (reader.Read())
{
Message message = new Message();
message.Id = reader.SafeGetGuid(reader.GetOrdinal("Id"));
message.MessageText = reader.SafeGetString(reader.GetOrdinal("Message"));
message.SendDate = reader.GetDateTime(reader.GetOrdinal("SendDate"));
message.Sent = reader.SafeGetBool(reader.GetOrdinal("Sent")).Value;
list.Add(message);
}
}
}
return list;
}