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