public ActionResult Index() => View();

        public JsonResult GetAvailableSubsPackages()
        {
            List<SubsPackage> packages = new List<SubsPackage>();
            int firmaID = (int)Session["FirmaID"]; string version = (Session["PaketTipi"].ToString());
            string query = "select * from packages p where p.version = N'"+version+"'";

            using (SqlConnection connection = new SqlConnection(AyarMetot.CrmConnection))
            {
                connection.Open();
                using (SqlCommand servisgetir = new SqlCommand(query, connection))
                {
                    using (SqlDataReader dr = servisgetir.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            SubsPackage package = new SubsPackage()
                            {
                                Content = Convert.ToString(dr["PackageContent"]),
                                Day = Convert.ToInt32(dr["Day"]),
                                ID = Convert.ToInt32(dr["ID"]),
                                Kdv = Convert.ToInt32(dr["Kdv"]),
                                Name = Convert.ToString(dr["PackageName"]),
                                Price = Convert.ToDecimal(dr["Price"]),
                                TotalPrice = Convert.ToDecimal(dr["TotalPrice"]),
                            };
                            packages.Add(package);
                        }
                    }
                }
            }
            packages = packages.OrderBy(x => x.Day).ToList();
            return Json(new { data = packages.ToList()});
        }
        public JsonResult GetPackageInfo(int id)
        {
            SubsPackage package = new SubsPackage();
            using (SqlConnection connection = new SqlConnection(AyarMetot.CrmConnection))
            {
                connection.Open();
                using (SqlCommand servisgetir = new SqlCommand("select * from packages where ID = "+id+"", connection))
                {
                    using (SqlDataReader dr = servisgetir.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            package = new SubsPackage()
                            {
                                Content = Convert.ToString(dr["PackageContent"]),
                                Day = Convert.ToInt32(dr["Day"]),
                                ID = Convert.ToInt32(dr["ID"]),
                                Kdv = Convert.ToInt32(dr["Kdv"]),
                                Name = Convert.ToString(dr["PackageName"]),
                                Price = Convert.ToDecimal(dr["Price"]),
                                TotalPrice = Convert.ToDecimal(dr["TotalPrice"]),
                            };
                        }
                    }
                }
            }
            return Json(package);
        }
        public JsonResult BuySubsPackage(AddPackageTransaction buy)
        {
            bool result = false; string message = "";

            int FirmaID = (int)Session["FirmaID"];

            SubsPackage package = new SubsPackage();
            using (SqlConnection connection = new SqlConnection(AyarMetot.CrmConnection))
            {
                connection.Open();
                using (SqlCommand servisgetir = new SqlCommand("select * from packages where ID = " + buy.PackageID + "", connection))
                {
                    using (SqlDataReader dr = servisgetir.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            package = new SubsPackage()
                            {
                                Content = Convert.ToString(dr["PackageContent"]),
                                Day = Convert.ToInt32(dr["Day"]),
                                ID = Convert.ToInt32(dr["ID"]),
                                Kdv = Convert.ToInt32(dr["Kdv"]),
                                Name = Convert.ToString(dr["PackageName"]),
                                Price = Convert.ToDecimal(dr["Price"]),
                                TotalPrice = Convert.ToDecimal(dr["TotalPrice"]),
                            };
                        }
                    }
                }

                buy.PackagePrice = package.TotalPrice;
                buy.Date = DateTime.Now;
                buy.Day = package.Day;
                buy.FirmaID = FirmaID;

                using (SqlDataAdapter da = new SqlDataAdapter("select Top 1 * from PackageTransactions", connection))
                {
                    using (SqlCommandBuilder cb = new SqlCommandBuilder(da))
                    {
                        DataSet ds = new DataSet();
                        da.Fill(ds, "PackageTransactions");
                        DataRow df = ds.Tables["PackageTransactions"].NewRow();

                        df["FirmaID"] = buy.FirmaID;
                        df["PackageID"] = buy.PackageID;
                        df["Date"] = buy.Date;
                        df["Day"] = buy.Day;
                        df["PackagePrice"] = buy.PackagePrice;
                        df["Payment"] = buy.Payment;
                        df["PaymentCode"] = buy.PaymentCode;

                        ds.Tables["PackageTransactions"].Rows.Add(df);
                        da.Update(ds, "PackageTransactions");
                    }
                }

                // Son Kullanım Tarihi Güncellenecek 
                DateTime ExpiryDate = DateTime.Now;
                using (SqlCommand servisgetir = new SqlCommand("select * from PAKETLER_WEB where ID = " + FirmaID + "", connection))
                {
                    using (SqlDataReader dr = servisgetir.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            try { ExpiryDate = Convert.ToDateTime(dr["ExpiryDate"]); } catch { }
                        }
                    }
                }
            
                ExpiryDate = ExpiryDate.AddDays(buy.Day);

                using (SqlDataAdapter dac = new SqlDataAdapter("select * from PAKETLER_WEB WHERE ID=" + FirmaID + " ", connection))
                {
                    using (SqlCommandBuilder cb = new SqlCommandBuilder(dac))
                    {
                        DataSet dsc = new DataSet();
                        dac.Fill(dsc, "PAKETLER_WEB");

                        DataRow[] adf = dsc.Tables["PAKETLER_WEB"].Select(" ID=" + FirmaID + "");
                        if (adf.Length != 0)
                        {
                            DataRow dr = adf[0];

                            dr["ExpiryDate"] = ExpiryDate;
                            dr["Demo"] = false;

                            dac.Update(dsc, "PAKETLER_WEB");
                        }
                    }
                }
            }

            result = true;
            message = """ + package.Name + "" isimli paketi satın aldınız. İyi Kullanımlar dileriz.";

            return Json(new { success = result, message = message });
        }
        public JsonResult GetCompanyStatus()
        {
            int FirmaID = (int)Session["FirmaID"];

            DateTime RegisterDate = DateTime.Now, ExpiryDate = DateTime.Now; bool demo = true;
            using (SqlConnection connection = new SqlConnection(AyarMetot.CrmConnection))
            {
                connection.Open();
                using (SqlCommand servisgetir = new SqlCommand("select * from PAKETLER_WEB where ID = " + FirmaID + "", connection))
                {
                    using (SqlDataReader dr = servisgetir.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            try { RegisterDate = Convert.ToDateTime(dr["KayitTarih"]); } catch { }
                            try { ExpiryDate = Convert.ToDateTime(dr["ExpiryDate"]); } catch { }
                            try { demo = Convert.ToBoolean(dr["Demo"]); } catch { }
                        }
                    }
                }
            }
            int kalanSure = 0;
            if (!demo) {
                TimeSpan timeSpan = ExpiryDate - DateTime.Now;
                kalanSure = timeSpan.TotalDays >= 1 ? (int)timeSpan.TotalDays : DateTime.Now > ExpiryDate ? 1 : 0;
                Session["DemoDurum"] = "Lisans";
                Session["Demo"] = false;
            }
            else {
                RegisterDate = RegisterDate.AddDays(7);
                TimeSpan timeSpan = RegisterDate - DateTime.Now;
                kalanSure = timeSpan.TotalDays >= 1 ? (int)timeSpan.TotalDays : DateTime.Now < RegisterDate ? 1 : 0;
                Session["DemoDurum"] = "Demo";
                Session["Demo"] = true;
            }
            Session["kalanGun"] = kalanSure;
            return Json(new { remainingTime = kalanSure, demo = demo });
        }
        public JsonResult GetMySubscriptionTransaction()
        {
             List<SubsTransaction> transacti List<SubsTransaction>();

            int FirmaID = (int)Session["FirmaID"];
            string query = @"select PT.ID, P.PackageName, PT.Date, PT.Day, PT.PackagePrice, PT.Payment
                            from PackageTransactions PT inner join Packages P on PT.PackageID = P.ID where PT.FirmaID = "+FirmaID+" order by PT.Date desc";

            using (SqlConnection connection = new SqlConnection(AyarMetot.CrmConnection))
            {
                connection.Open();
                using (SqlCommand servisgetir = new SqlCommand(query, connection))
                {
                    using (SqlDataReader dr = servisgetir.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            SubsTransaction transaction = new SubsTransaction()
                            {
                                Date = Convert.ToDateTime(dr["Date"]).ToString("dd-MM-yyyy"),
                                Day = Convert.ToString(dr["Day"]),
                                ID = Convert.ToInt32(dr["ID"]),
                                Package = Convert.ToString(dr["PackageName"]),
                                PackagePrice = Convert.ToDecimal(dr["PackagePrice"]).ToString("N2"),
                                Payment = Convert.ToDecimal(dr["Payment"]).ToString("N2"),
                            };
                            transactions.Add(transaction);
                        }
                    }
                }
            }


            var jsonResult = Json(new { data = transactions.ToList() }, JsonRequestBehavior.AllowGet);
            jsonResult.MaxJsonLength = int.MaxValue;
            return jsonResult;
        }



        public class SubsPackage
        {
            public int ID { get; set; }
            public string Name { get; set; }
            public string Content { get; set; }
            public int Day { get; set; }
            public decimal Price { get; set; }
            public decimal TotalPrice { get; set; }
            public int Kdv { get; set; }
        }
        public class SubsTransaction
        {
            public int ID { get; set; }
            public string Package { get; set; }
            public string Date { get; set; }
            public string Day { get; set; }
            public string PackagePrice { get; set; }
            public string Payment { get; set; }
        }
        public class AddPackageTransaction
        {
            public int ID { get; set; }
            public int FirmaID { get; set; }
            public int PackageID { get; set; }
            public DateTime Date { get; set; }
            public int Day { get; set; }
            public decimal PackagePrice  { get; set; }
            public decimal Payment  { get; set; }
            public string PaymentCode  { get; set; }
        }