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 :&nbsp;&nbsp;{0}
                                            <br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                                            {1}
                                            <br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                                            {2}&nbsp;&nbsp;{3}&nbsp;{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);
        }