public int Update(LogisticsCompany c)
{
using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
{
conn.Open();
int result = conn.Execute(@"
update invoice.Company
set CompanyName = @CompanyName
,ContactPerson = @ContactPerson
,AddressLine1 = @AddressLine1
,AddressLine2 = @AddressLine2
,City = @City
,State = @State
,Zip = @Zip
,Email = @Email
,MobileNumber = @MobileNumber
,OfficeNumber = @OfficeNumber
,FaxNumber = @FaxNumber
,ComplimentaryWeight = @ComplimentaryWeight
,BasePickupCharge = @BasePickupCharge
,WeightRate = @WeightRate
,ModifiedBy = @ModifiedBy
,ModifiedAt = getdate()
where CompanyId = @CompanyId
", c);
return result;
}
}
// GET: MyCompany
public ActionResult Index()
{
logger.Info("Simplex Invoice MyCompanyController.Index()");
if (!User.Identity.IsAuthenticated)
{
return RedirectToAction("Login", "Account");
}
LogisticsCompany c = (LogisticsCompany)ch.GetCompanyRegisteredByUser(User.Identity.Name);
if (c == null)
{
c = new LogisticsCompany();
c.CompanyName = string.Empty;
c.ContactPerson = string.Empty;
c.AddressLine1 = string.Empty;
c.AddressLine2 = string.Empty;
c.City = string.Empty;
c.State = "Choose one";
c.Zip = string.Empty;
c.Email = string.Empty;
c.MobileNumber = string.Empty;
c.OfficeNumber = string.Empty;
c.FaxNumber = string.Empty;
c.ComplimentaryWeight = 100;
c.WeightRate = 2;
c.BasePickupCharge = 25;
}
return View(c);
}
public int Add(LogisticsCompany c, string currentUser)
{
using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
{
conn.Open();
var result = conn.Query<int>(@"
insert into invoice.Company(CompanyName,ContactPerson,
AddressLine1,AddressLine2,City,State,Zip,Email,
MobileNumber,OfficeNumber,FaxNumber,ComplimentaryWeight,WeightRate,
BasePickupCharge,CreatedBy,CreatedAt)
values(@CompanyName,@ContactPerson,
@AddressLine1,@AddressLine2,@City,@State,@Zip,@Email,
@MobileNumber,@OfficeNumber,@FaxNumber,@ComplimentaryWeight,@WeightRate,
@BasePickupCharge,@CreatedBy,getdate());
SELECT SCOPE_IDENTITY()
", c);
var result1 = conn.Query<int>(@"
insert into invoice.UserCompany(UserId, CompanyId, CreatedBy, CreatedAt)
values(@CurrentUser, @CompanyId, @EnvUser, getdate());
", new { CurrentUser = currentUser , CompanyId = result.FirstOrDefault(), EnvUser = Environment.UserName });
return result.FirstOrDefault();
}
}
// GET: MyClients
public ActionResult Index()
{
logger.Info("Simplex Invoice MyClients. Index()");
if (!User.Identity.IsAuthenticated)
{
return RedirectToAction("Login", "Account");
}
ClientCompany c = null;
if (c == null)
{
c = new ClientCompany();
c.CompanyName = string.Empty;
c.ContactPerson = string.Empty;
c.AddressLine1 = string.Empty;
c.AddressLine2 = string.Empty;
c.City = string.Empty;
c.State = "NY";
c.Zip = string.Empty;
c.Email = string.Empty;
c.MobileNumber = string.Empty;
c.OfficeNumber = string.Empty;
c.FaxNumber = string.Empty;
}
lc = lch.GetCompanyRegisteredByUser(User.Identity.Name);
if(lc != null)
{
c.WeightRate = lc.WeightRate;
c.ComplimentaryWeight = lc.ComplimentaryWeight;
c.BasePickupCharge = lc.BasePickupCharge;
}
ViewData["PU_FORM"] = c;
return View();
}
public ActionResult GetInvoice([DataSourceRequest]DataSourceRequest request, int jobTicketId)
{
Invoice invoice = new Invoice();
lc = ch.GetCompanyRegisteredByUser(User.Identity.Name);
var jobTicket = jth.GetJobTicket(jobTicketId);
invoice.MyCompanyAddress = string.Format("{0}<br />{1}<br />{2}, {3} {4} <br />", lc.CompanyName,lc.AddressLine1,lc.City,lc.State,lc.Zip);
invoice.MyCompanyContactInfo = string.Format("TEL: {0} <br /> FAX: {1} <br />", lc.MobileNumber, lc.FaxNumber);
invoice.JobDate = string.Format("DATE: {0}",jobTicket.JobDate.ToString("MM/dd/yyyy"));
invoice.JobNumber = string.Format("JOB NUMBER: {0}", jobTicket.JobTicketId);
var clientCompany = cch.GetClientCompanyById(jobTicket.ClientCompanyId, lc);
invoice.BillTo = string.Format(@"BILL TO : {0}
<br />
{1}
<br />
{2} {3} {4}<br />", clientCompany.BillToName, clientCompany.BillToAddressLine1, clientCompany.BillToCity,clientCompany.State,clientCompany.BillToZip);
invoice.PUFromAddress = string.Format("P/U FROM : </br> {0} <br />", jobTicket.PickupFrom.Replace("\n", "<br />"));
invoice.PUFromContactInfo = string.Format("CONTACT : {0} <br /> PHONE: {1} <br />", jobTicket.PickupFromContact,jobTicket.PickupFromPhone);
invoice.DeliverToAddress = string.Format("DELIVER TO : </br> {0} <br />", jobTicket.DeliverTo.Replace("\n", "<br />"));
invoice.DeliverToContactInfo = string.Format("CONTACT : {0} <br /> PHONE: {1} <br />", jobTicket.DeliverToContact, jobTicket.DeliverToPhone);
invoice.Instructions = jobTicket.Instruction;
invoice.TypeOfService = jobTicket.ServiceType;
invoice.DeliveryDate = jobTicket.DeliveryDate.ToString("MM/dd/yyyy");
invoice.Qty = jobTicket.Quantity.ToString();
invoice.POD = jobTicket.POD;
invoice.Comment = jobTicket.Comments;
invoice.DeliveryAgent = jobTicket.DeliveryAgent;
invoice.Weight = jobTicket.Weight.ToString();
invoice.MilageCharge = jobTicket.Milage.ToString();
invoice.TollCharge = jobTicket.Toll.ToString();
invoice.FuelCharge = jobTicket.FuelSurcharge.ToString();
invoice.MiscServiceCharge = jobTicket.MiscFee.ToString();
invoice.TotalCharge = jobTicket.TotalCharge.ToString();
return Json(new { success = true, message = invoice }, JsonRequestBehavior.AllowGet);
}
public ActionResult Search_Read([DataSourceRequest] DataSourceRequest request, string jobDate, string clientName)
{
if (lc == null)
lc = lch.GetCompanyRegisteredByUser(User.Identity.Name);
IEnumerable<JobTicket> result = jth.GetAllTickets(lc);
if (result == null)
result = new List<JobTicket>();
if(!jobDate.Equals("UNSELECTED"))
{
result = result.Where(x => x.JobDate.ToString("d").Equals(jobDate));
}
if (!clientName.Equals("UNSELECTED"))
{
result = result.Where(x => x.ClientName.Equals(clientName));
}
return Json(result.ToDataSourceResult(request));
}
public ActionResult GetClientDefaults([DataSourceRequest]DataSourceRequest request, string inputClient)
{
if (!User.Identity.IsAuthenticated)
{
return RedirectToAction("Login", "Account");
}
if (string.IsNullOrEmpty(inputClient))
return Json(new { success = true, message = 0 }, JsonRequestBehavior.AllowGet);
if (lc == null)
lc = lch.GetCompanyRegisteredByUser(User.Identity.Name);
ClientCompany c = cch.GetClientCompanyByName(inputClient, lc);
return Json(new { success = true, message = c }, JsonRequestBehavior.AllowGet);
}
public ActionResult SaveJobTicket([DataSourceRequest]DataSourceRequest request, string inputJobTicket)
{
if (!User.Identity.IsAuthenticated)
{
return RedirectToAction("Login", "Account");
}
var json_serializer = new JavaScriptSerializer();
JobTicket ticket = json_serializer.Deserialize<JobTicket>(inputJobTicket);
if (lc == null)
lc = lch.GetCompanyRegisteredByUser(User.Identity.Name);
var clientCompany = cch.GetClientCompanyByName(ticket.ClientName, lc);
ticket.ClientCompanyId = clientCompany.ClientCompanyId;
PickupDeliveryAddress addrPickupFrom = new PickupDeliveryAddress(ticket.PickupFrom);
PickupDeliveryAddress addrDeliverTo = new PickupDeliveryAddress(ticket.DeliverTo);
PickupDeliveryAddress.Add(addrPickupFrom);
PickupDeliveryAddress.Add(addrDeliverTo);
if (ticket.JobTicketId == 0)
{
ticket.CreatedBy = User.Identity.Name;
ticket.CompanyId = lc.CompanyId;
ticket.JobTicketId = jth.Add(ticket, User.Identity.Name);
}
else
{
ticket.ModifiedBy = User.Identity.Name;
jth.Update(ticket);
}
// Save to db here
// Assign job ticket number to the object
// return jobTicket
return Json(new { success = true, message = ticket }, JsonRequestBehavior.AllowGet);
}
public IEnumerable<ClientCompany> GetClients(LogisticsCompany lc)
{
if (lc == null)
return null;
using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
{
conn.Open();
var result = conn.Query<ClientCompany>(@"
select*
from invoice.MyClients
where CompanyId = @companyId
", new { companyId = lc.CompanyId });
return result;
}
}
public ClientCompany GetClientCompanyById(int clientCompanyId, LogisticsCompany lc)
{
using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
{
conn.Open();
var result = conn.Query<ClientCompany>(@"
select *
from [SimplexInvoice].[invoice].[MyClients]
where ClientCompanyId = @clientCompanyId
and CompanyId = @companyId
", new { clientCompanyId, companyId = lc.CompanyId });
return result.FirstOrDefault();
}
}
public ActionResult SaveClientInfo([DataSourceRequest]DataSourceRequest request, string clientCompanyString)
{
if (!User.Identity.IsAuthenticated)
{
return RedirectToAction("Login", "Account");
}
string message = "Company saved.";
var json_serializer = new JavaScriptSerializer();
ClientCompany c = json_serializer.Deserialize<ClientCompany>(clientCompanyString);
c.CreatedBy = User.Identity.Name;
logger.InfoFormat("Saving company...");
if (lc == null)
lc = lch.GetCompanyRegisteredByUser(User.Identity.Name);
try
{
var existingCompany = cch.GetClientCompanyByName(c.CompanyName, lc);
if (existingCompany == null)
{
c.CompanyId = lc.CompanyId;
c.ClientCompanyId = cch.Add(c);
}
else
{
c.CompanyId = existingCompany.CompanyId;
c.ClientCompanyId = existingCompany.ClientCompanyId;
logger.InfoFormat("Company exists. Updating client company.");
c.ModifiedBy = User.Identity.Name;
logger.InfoFormat("{0} rows updated.", cch.Update(c));
}
logger.InfoFormat("Company saved.");
}
catch (Exception ex)
{
message = string.Format("Failed to save company. Error: {0}", ex.Message);
logger.Fatal(ex);
}
return Json(new { success = true, message = message }, JsonRequestBehavior.AllowGet);
}
public ActionResult Editing_Read([DataSourceRequest] DataSourceRequest request)
{
if (lc == null)
lc = lch.GetCompanyRegisteredByUser(User.Identity.Name);
IEnumerable<ClientCompany> result = cch.GetClientCompanies(lc.CompanyId);
return Json(result.ToDataSourceResult(request));
}
public ActionResult GetClientInfo([DataSourceRequest]DataSourceRequest request, string clientCompanyString)
{
string message = "Getting company";
if (lc == null)
lc = lch.GetCompanyRegisteredByUser(User.Identity.Name);
ClientCompany c = cch.GetClientCompanyByName(clientCompanyString,lc);
if (c == null)
{
c = new ClientCompany();
c.CompanyName = clientCompanyString;
c.ContactPerson = string.Empty;
c.AddressLine1 = string.Empty;
c.AddressLine2 = string.Empty;
c.City = string.Empty;
c.State = "NY";
c.Zip = string.Empty;
c.Email = string.Empty;
c.MobileNumber = string.Empty;
c.OfficeNumber = string.Empty;
c.FaxNumber = string.Empty;
c.WeightRate = 3;
c.ComplimentaryWeight = 1000;
c.BasePickupCharge = 56;
}
return Json(new { success = true, message = c }, JsonRequestBehavior.AllowGet);
}
public IEnumerable<JobTicket> GetTodaysTickes(LogisticsCompany lc)
{
try
{
using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
{
conn.Open();
return conn.Query<JobTicket>(@"
select mc.CompanyName as ClientName, jt.*
from invoice.JobTickets jt
inner join invoice.MyClients mc on jt.ClientCompanyId = mc.ClientCompanyId
where (jt.CreatedAt >= (cast(GETDATE()-6 as date)) or jt.ModifiedAt >= (cast(GETDATE()-6 as date)))
and jt.CompanyId = @CompanyId
", new { CompanyId = lc.CompanyId });
}
}
catch (Exception ex)
{
logger.Fatal(ex);
return null;
}
}
public IEnumerable<JobTicket> GetAllTickets(LogisticsCompany lc)
{
try
{
using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
{
conn.Open();
return conn.Query<JobTicket>(@"
select mc.CompanyName as ClientName, jt.*
from invoice.JobTickets jt
inner join invoice.MyClients mc on jt.ClientCompanyId = mc.ClientCompanyId
where jt.CompanyId = @CompanyId
", new { CompanyId = lc.CompanyId });
}
}
catch (Exception ex)
{
logger.Fatal(ex);
return null;
}
}
public ActionResult Editing_Read([DataSourceRequest] DataSourceRequest request)
{
if (lc == null)
lc = lch.GetCompanyRegisteredByUser(User.Identity.Name);
IEnumerable<JobTicket> result = jth.GetTodaysTickes(lc);
if (result == null)
result = new List<JobTicket>();
return Json(result.ToDataSourceResult(request));
}
public ActionResult CalcTotalCharge([DataSourceRequest]DataSourceRequest request, string inputChargeParameters)
{
if (!User.Identity.IsAuthenticated)
{
return RedirectToAction("Login", "Account");
}
var json_serializer = new JavaScriptSerializer();
TotalCharge chargeInput = json_serializer.Deserialize<TotalCharge>(inputChargeParameters);
if (string.IsNullOrEmpty(chargeInput.InputClient))
return Json(new { success = true, message = 0 }, JsonRequestBehavior.AllowGet);
if (lc == null)
lc = lch.GetCompanyRegisteredByUser(User.Identity.Name);
ClientCompany c = cch.GetClientCompanyByName(chargeInput.InputClient, lc);
chargeInput.Calc(c.WeightRate, c.ComplimentaryWeight);
//decimal totalCharges = inputQuantity * inputWeight *
return Json(new { success = true, message = chargeInput }, JsonRequestBehavior.AllowGet);
}
public ActionResult GetClientNames()
{
if (!User.Identity.IsAuthenticated)
{
return RedirectToAction("Login", "Account");
}
if (lc == null)
lc = lch.GetCompanyRegisteredByUser(User.Identity.Name);
var clients = lch.GetClients(lc);
List<ClientDDL> result = new List<ClientDDL>();
foreach(var cl in clients)
{
ClientDDL c = new ClientDDL() { ClientName = cl.CompanyName , ClientID = cl.ClientCompanyId };
result.Add(c);
}
return Json(result, JsonRequestBehavior.AllowGet);
}
