using CarGallery;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.IO;
using System.Linq;
using System.Web;


namespace CarGallery
{

    public class DatabaseRepository
    {
        CarGallery01Entities entities;

        // baraye ertebat ba database az EntityFramework (EF) estefade kardim ke amoozeshesh
        // too oon file word hast. EF miad ye model misaze va vase har Table to database ye Class
        // dar nazar migire. in model e ma ye Class e asli dare be esme CarGallery01Entities ke 
        // dar hangame sakhte model esmesho tayin kardim . az in class ye instance ( nemoone ) misazim
        // baraye sakhte ye instance az class intori amal mikonim :
        // EsmeClass esmeNemoone = new sazandeyeClass(); (sazandeye class esmesh ba khode class yekie)
        // CarGallery01Entities entities = new CarGallery01Entities();
        // in kar dorost mesle tarife moteqayere ! masalan int x;
        // ba in tafavot ke bayad ye meqdari hatman behesh bedim vagarna qeyre qabele estefadas
        // va oon meqdar chizi nist joz khodesh

        // hamoontor ke mibini too CarGallery01Entities entities; ye nemoone az class e CarGallery01Entities
        // sakhtim be esme entities 
        
        // **** entities RO HAME JA BAHASH KAR DARIM YADET BASHE CHI HAST ****


        
        // hala too method e sazandeye class , be in nemoone meqdar midim.

        // sazandeye class ye method e , ke faqat vaqti ejra mishe ke az class ye instance (nemoone) besazi
        // pas faqat ye bar ejra mishe oonam moqeye instance sakhtan az class ! badan dar moredesh tozih midam
        
        public DatabaseRepository()
        {
            entities = new CarGallery01Entities();
        }


        // ye tozihe kootah dar morde method ha bedam :

        // ma 2 no method (تابع) darim , 1. method bazgashti. 2. method qeyre bazgashti

        // method e bazgashti ye meqdari ro barmigardoonan va ye no e dadeyi daran
        // vali method haye qeyre bazgashti meqdari ro barnemigardoonan ! faqat ye kari anjam midan !

        // method ha mitoonan Private , Protected ya Public bashan ! 

        // age private bashe , faqat mitoonim to hamoon class azashoon estefade konim
        // age protected bashe ro begzarim faqat bedoon estefadash to verazat o inas ...
        // age method public bashe , mitoonim hame ja azash estefade konim to har class ya formi

        // vase tarife method ba meqdare bazgashti intori amal mikonim : private/protected/public noeBazgashti esmeTabe()
        // vase tarife method ba meqdare qeyre bazgashti intori amal mikonim : private/protected/public void esmeTabe()

        // nokte : age private/protected/public ro nanevisim , khod be khod private dar nazar migire

        // masalan ma ye class darim be esme ClassA
        // 2 ta method toosh darim :

        // public void methodA()
        // private void methodB()

        // tooye ye class e dige az class e ClassA ye instance (nemoone) misazim be in soorat : ClassA classA = new ClassA(); 
        //( inam begam ClassA ba classA farq dare , bozorg ya koochik boodane horoof moheme yani mitooni 2 ta moteqayer tarif koni yekish : int A; va yekish : int a; )

        // vaqti bezanim classA noqte (classA.)
        // ye list baramoon miare ke methodA toosh hast , vali methodB toosh nist !


        
        // ye method az no e DataTable ke qarare list e post ha ro baramoon bargardoone
        // baraye namayesh to qesmate modiriate post ha misazim

        // dataTable hamoon tor ke az esmesh peydas jadval e dade has ! ye chizi mesle table to database
        // ya DataGrid tooye C# , vali ba in farq ke zaher nadare ! vaqat data hastesh


        // ye meqdare voroodi az no e byte migire (no esh mohem nist mitoone int ham bashe ya harchize dige)
        // in meqdare voroodi mitoone 0,1,2,3 ya 4 bashe ke darvaghe vase qesmate filter e modiriat e post has

        public DataTable postsManage(byte filter)
        {

            // chon to qesmate modiriate post ha , ye listi az post ha neshoon dade mishe
            // pas ma bayad ye list az post ha dashte bashim
            // baraye sakhtane list migim List<noesh> esmesh = meqdaresh;

            List<tbl_posts> posts = null; // chon az filter estefade kardim meqdaresho felan Null (bedoone meqdar mizarim)
            // age null nazarim , payin error mide ! mitooni null ro hazf koni bebini chi mishe
            // bejaye khate bala bezani : List<tbl_posts> posts;


            // switch ye model dastoorate shartie ! 
            // be tore kheyli sade ! ye moteqayer behesh midi

            switch(filter)
            {
                case 0: // age to moteqayere filter 0 bood in kara ro anjam bede :
                    {
                        posts = entities.tbl_posts.AsEnumerable().OrderByDescending(tbl_posts => tbl_posts.datetime).ToList();
                        // tooye list e posts (list i az post ha ke oon bala sakhtim)
                        // in meqdar ro beriz :
                        // listi az hameye post ha ke bar asase tarikhZaman moratab shodan.

                        // ya be soorate tartibi bekham begam:

                        // listePostHa = nameInstanceEDatabase.jadvalePostHa.KoleRecordHash().MoratabKonBeSoorateNozooli(AzJadvalePostHa => barAsaseDateTime).TabdilBeList();


                        break; //break vase estefade tooye case haye switch lazeme. baes mishe age shart barqarar bood
                        // shart haye dige mese case 1: , case 2: va ... ro check nakone va mostaqim bere akharesh;

                    }
                case 1: // age to moteqayere filter 1 bood in kara ro anjam bede :
                    {
                        //tozihate asli ro bala dadam

                        // listi az hameye post hayi ke slideshoon fa al e , ke bar asase tarikhZaman moratab shodan.
                        posts = entities.tbl_posts.Where(tbl_posts => tbl_posts.slide == true).OrderByDescending(tbl_posts => tbl_posts.datetime).ToList();
                        // listePostHa = nameInstanceEDatabase.jadvalePostHa.RecordHayiKe(AzJadvalePostHa => FieldeSlideshoon mosavieBa true).MoratabKonBeSoorateNozooli(AzJadvalePostHa => barAsaseDateTime).TabdilBeList();

                        break;
                    }
                case 2:
                    {
                        // listi az hameye post hayi ke slideshoon fa al nist , ke bar asase tarikhZaman moratab shodan.

                        posts = entities.tbl_posts.Where(tbl_posts => tbl_posts.slide == false).OrderByDescending(tbl_posts => tbl_posts.datetime).ToList();
                        break;
                    }
                case 3:
                    {
                        // listi az hameye post hayi ke commenteshoon fa al e , ke bar asase tarikhZaman moratab shodan.

                        posts = entities.tbl_posts.Where(tbl_posts => tbl_posts.comment == true).OrderByDescending(tbl_posts => tbl_posts.datetime).ToList();
                        break;
                    }
                case 4:
                    {
                        // listi az hameye post hayi ke slideshoon fa al nist , ke bar asase tarikhZaman moratab shodan.

                        posts = entities.tbl_posts.Where(tbl_posts => tbl_posts.comment == false).ToList();
                        break;
                    }
            }

            // too karayi ke bala anjam dadim , oomadim post hayi ke mikhaym ro daryaft kardim va to ye list rikhtim.
            // chon noe method emoon DataTable hast , bayad DataTable bargardoonim.
            // mitoonestim no e method ro listi az post ha bezarim , vali chon niaz darim ye seri etela at taqir kone ya ezafe beshe
            // vase hamina DataTable estefade kardim.
            // DataTable azade ke harchi doos dari ezafe ya kam koni vali List sabete

            // pas ye DataTable misazim. (tozihat ro bala dadam , hamoon instance va ....)


            DataTable dt = new DataTable();

            // baraye DataTable ya jadvali ke sakhtim be  tedade lazem Sotoon misazim
            
            // esmeDataTableam.Sotoonhash.EzafeKon("Esme Sotoon",AzNoE(NoiKeMikhaym);

            dt.Columns.Add("no", typeof(string)); //shomare ke az 1 shoroo mishe ! maslan 5 tas mishe 1 2 3 4 5
            dt.Columns.Add("id", typeof(string)); //ID e post
            dt.Columns.Add("thumb", typeof(string)); // axe address e axe thumbnail (tasvire koochik band angoshti)
            dt.Columns.Add("title", typeof(string)); // .......
            dt.Columns.Add("visits", typeof(string)); // tedade moshhede
            dt.Columns.Add("datetime", typeof(string)); //tarikh va zaman
            dt.Columns.Add("slide", typeof(string)); // slide fa ale ya na ?
            dt.Columns.Add("comment", typeof(string)); // comment fa ale ya na ?
            dt.Columns.Add("comments", typeof(string)); // tedade comment ha

            string comments = ""; // gereftane tedade comment ha yekam pichidas ke tozih midam


            // ma ye listi az record ha (post ha) gereftim , niaz darim yeki yeki record ha (post ha) sho be
            // data table i ke sakhtim ezafe konim ba taqirati ke doos darim

            // pas ye halqe mizani az 0 ta tedade post ha menhaye 1 (x < b mosavie ba x <= b-1)
            // chera az 0 ?! chon shomare khoone ha hamishe az 0 shoroo mishe (bejoz bazi zaban haye khas)

            for (int x = 0; x < posts.Count; x++)
            {
                string tmp = posts[x].id; // ye list az post ha darim be esme posts , baraye inke betoonim be 
                // post e shomareye x dastrasi dashte bashim migim post[x]
                // alan oomadim ID e post to khooneye x ro rikhtim to moteqayere tmp;

                string slide,comment;

                if (posts[x].slide) // age post e to khooneye x , slide hash fa al bood 
                    slide = "img/btn_checked.png"; // address e ye ax ke tik khorde roosh ro mikham
                else// age slide hash fa al nabood
                    slide = "img/btn_checkednot.png"; //address e ye ax ke tik nakhorde roosh ro mikham 

                if (posts[x].comment) // mesle bala vali vase comment ha
                    comment = "img/btn_checked.png";
                else
                    comment = "img/btn_checkednot.png";


                // khob hala mikhaym tedade comment haye post e mojood to khooneye x ro dar biarim

                comments = "  " + entities.tbl_comments.Count(tbl_comments => tbl_comments.post_id == tmp).ToString() + "  ";
                // "  " + felanFelan + "  ";
                // in "  " ro hichi dar nazar begir ! :D aval o akharesh space mizare faqat chize khasi nist

                // entities.tbl_comments.Count(tbl_comments => tbl_comments.post_id == tmp).ToString()
                // azInstanceiKeSakhtim.JadvaleCommentHa.TedadeInaRoBehemBede(azJadvaleCommentHa => CommentHayiKe ID e posteshoon == tmp bood).TabdilKonBeString();
                // tmp ro bala tarif kardim string tmp = posts[x].id;




                // khob , hala hame meqdarayi ke mikhastim ro taqir dadim ya ezafe kardim.
                // hala bayad be DataTable i ke sakhtim ezafe konim

                // migim DataTable.SatrHash.EzafeKon(
                
                // (x+1).ToString() >>> chera X + 1 ?! goftim X az 0 shoroo beshe ! vali mikhaym to listi ke neshoon mide az 1 shoroo beshe ! pas migim X + 1
                
                // posts[x].id >>> ID e post too khooneye X
                
                // "/thumbs/" + posts[x].img >>> yadete goftim Thumbnail mikhaym ? axe Thumbnail ha to poosheye thumbs hastesh , vase dastrasi be har ax bayad begim /thumbs/esmeAx.pasvand
                        // to inja ma esmeAx.pasvand ro tooye database darim , pas vase sakhtanesh migim "/thumbs/" + esmeAx;

                // posts[x].visits >>> tedade visit hayi ke az post e mojood too khooneye X anjam shode

                // posts[x].datetime.ToShortDateString() + " - " + posts[x].datetime.ToShortTimeString()
                // postETooyeKhooneyeX.TarikhZaman.tabdilKonBeTarikheKoochik() + " - " + postETooyeKhooneyeX.TarikhZaman.tabdilKonBeZamaneKoochik();
                // masalan intori neshoon mide : 2016/02/16 - 22:16

                // slide >>> vase slide moteqayer bala tarif kardim goftim age fa at bood address e axe felan ro bede age qeyre fa al bood address e axe felan
                // comment >>> mesle slide
                // comments >>> tedade comment haye oon poste ro bede

                dt.Rows.Add((x+1).ToString(),
                    posts[x].id,
                    "/thumbs/" + posts[x].img,
                    posts[x].title,
                posts[x].visits,
                posts[x].datetime.ToShortDateString() + " - " + posts[x].datetime.ToShortTimeString(),
                slide,
                comment,
                comments);
            }

            // halqeye bala , be tedade post hayi ke to list e post hamoon hast tekrar mishe
            // har tekrar mishe x ye vahed ezafe mishe behesh va ye Post az khooneye X entekhab mishe
            // ye seri kara roosh anjam mishe va be DataTable ezafe mishe
            // va dar nahayat

            //DataTable i ke sakhtim va koli data toosh ezafe kardim ro barmigardoonim

            return dt;

            // age tooneste bashi ino befahmi chie , taqriban nesfe dastane kar ba database ro fahmidi ! baqiash asoon tare nesbatan
        }



        // ye method az noe qeyre bazgashti tarif kardim ke harvaqt sedash zadim , namayesh e slide e ye post ro fa al ya qeyre fa al kone
        // vase inke begim slide e ye post ro fa al kon ya qeyre fa al kon bayad behesh begim kodooooooom post ro mikhaym
        // pas ye meqdare voroodi midim behesh , ID e post

        public void postsSlideAllow(string post_id)
        {

            // posti ke mikhaym rosh taqir anjam bedim slide esh fa ale ya qeyre fa al ?!
            // too jadvale post ha ye field darim be esme slide ke no esh boolean (true ya false) hastesh

            bool allowed = entities.tbl_posts.Single(tbl_posts => tbl_posts.id == post_id).slide;
            // entities.jadvalePostha.FaqatYePost(ke Az Jadvale PostHa => IDe Postesh == postID ke behesh dadim).meqdareFieldeSlidesh;


            // hala migim age meqdaresh true bood ( yani age slide hash fa al bood ) bayad qeyre fa alesh konim !
            // vaqti ye moteqayere boolean ro too if qarar midim bedoone inke == true ya == false bezarim , khodesh == true dar nazar migire
            // yani : if(allowed) mosavie ba (if allowed == true) ya if(allowed != false) (agar allowed mosaviNist ba true)
            // hamchenin : if(!allowed) mosavie ba (if allowed == false) ya if(allowed != True)


            if (allowed) // agar slidesh fa al bood :

                entities.Database.ExecuteSqlCommand("UPDATE tbl_posts SET slide = 0 WHERE id = '" + post_id + "'");
                // update be soorate EF yekam pichidas vase hamin man Update az no e SQL estefade kardam ke sade tar bashe
                // vase ejraye ye dastoore SQL server to EF
                // migim entities.Database.ExecuteSQLCommand(code e sql );
                // faqat meqdari ro barnemigardoone ! faqat ye kari anjam mide
                // UPDATE tbl_posts SET slide = 0 WHERE id = '" + post_id + "'"
                // update kon tbl_post ro SET kon slide = false harja ke id = meqdare voroodie ma bood

            else // age slidesh fa al nabood
                entities.Database.ExecuteSqlCommand("UPDATE tbl_posts SET slide = 1 WHERE id = '" + post_id + "'");
                // update kon tbl_post ro SET kon slide = true harja ke id = meqdare voroodie ma bood


        }


        // daqiqan mesle hamoon balayie faqat vase comment has
        public void postsCommentAllow(string post_id)
        {
            bool allowed = entities.tbl_posts.Single(tbl_posts => tbl_posts.id == post_id).comment;

            if (allowed)
                entities.Database.ExecuteSqlCommand("UPDATE tbl_posts SET comment = 0 WHERE id = '" + post_id + "'");
            else
                entities.Database.ExecuteSqlCommand("UPDATE tbl_posts SET comment = 1 WHERE id = '" + post_id + "'");

        }


        // inam taqriban mese balayias vali dige dastie ! khodesh mostaqrim migim fa al kon ya qeyre fa al kon !
        public void postsSlideAllow(string post_id,bool isAllow)
        {
            if (isAllow)
                entities.Database.ExecuteSqlCommand("UPDATE tbl_posts SET slide = 1 WHERE id = '" + post_id + "'");
            else
                entities.Database.ExecuteSqlCommand("UPDATE tbl_posts SET slide = 0 WHERE id = '" + post_id + "'");
        }

        public void postsCommentAllow(string post_id,bool isAllow)
        {
            if (isAllow)
                entities.Database.ExecuteSqlCommand("UPDATE tbl_posts SET comment = 1 WHERE id = '" + post_id + "'");
            else
                entities.Database.ExecuteSqlCommand("UPDATE tbl_posts SET comment = 0 WHERE id = '" + post_id + "'");
        }



        // niaz darim post ro delete konim ! kodoom post ro ? oon post ke ID sh felane !
        public void postsDelete(string post_id)
        {
            string imgName = postGetSingle(post_id).Rows[0][3].ToString();
            // vase hazfe ye post niaz darim axasho hazf konim
            // mikham esme axe ye post ro dar biarim !
            // ye method to hamin class darim be esme postGetSingle
            // ye post e taki ro baramoon barmigardoone ke ye seri field ha dakhelesh hast az jomle field e esme ax ke img hastesh
            // migim postGetSingle(ID e post).img;

            bool slide = false, photo = false, thumb = false;
            // ma to 3 ta folder ax darim.
            // yani har post 3 ta ax dare
            // mamoolan vaqti ye file ro upload mikoni to server , file vase ye modate kootahi lock mishe va server nemizare file ro pak koni

            // momkene moqe pak kardane ye post yeki az file ha ya chand tashoon be khatere lock boodan pak nashe

            // barname bayad befahme ke file ha pak shodan ya na ! age hade aqal yeki az ax ha ham hazf nashode bashe
            // pas nabayad kole post ro pak kone chon file esh hast va badan moshkel ijad mishe

            // miaym 3 ta boolean dar nazar migirim vase inke control konimesh
            // meqdare hamashoonam pishfarz false migirim , age ax lock nabood va qabele pak kardan bood , oonvaqt meqdareshoono true mikonim

            // nemishe ke ye post 2 ta axash pak beshe yekish bemoone !
            // pas be tore movaqat , jaye inke ax ha ro delete konim , be ye pooshe dige move mikonim
            // age moshkel pish oomad axa ro barmigardoonim sare jashoon
            // age pish nayoomad axa ro kolan pak mikonim

            // migim :
            
            try // say kon 
            {
                File.Move("/photos/" + imgName, "/photos/archived/" + imgName);
                // filei ke esmesh imgName hast va tooye /photos/ hast ro enteqal bedi be /photos/archived
                // age error dad , mipare be qesmate catch{};

                // age error nadad , khate badi ro ejra mikone
                photo = true;

                // pas chi shod ? try va catch vase zamanie ke bekhayim error ro control konim
                // migim :
                
                //try 
                // {
                //      ye kari bokon ...
                // }
                // catch
                // {
                //      ye kar dige bokon
                // }

                // miad code i ke to try neveshtim ro ejra mikone , age moshkeli nabood ke hichi
                // age moshkeli bood az try mipare biroon va catch ro ejra mikone ! masalan migim age error dad ye messageBox bede fohsh bede

            }
            catch { };

            // mesle bala 
            try
            {
                File.Move("/slides/" + imgName, "/slides/archived/" + imgName);
                slide = true;

            }
            catch { };


            // mesle bala
            try
            {
                File.Move("/thumbs/" + imgName, "/thumbs/archived/" + imgName);
                thumb = true;

            }
            catch { };


            // hala bia control kon bebin moshkeli pish oomade ya na ! 
            // age moshkeli pish nayoomade bashe , 3 ta moteqayere photo , slide va thumb meqdareshoon true hastesh
            // pas age har 3 true boodan yani file lock nist va mishe deletesh kard !
            // pas delete kon

            if (photo && slide && thumb)
            {
                File.Delete("/photos/archived/" + imgName);
                File.Delete("/slides/archived/" + imgName);
                File.Delete("/thumbs/archived/" + imgName);

                postDelete(post_id); // akharesh ham bia kolan post ro delete kon , postDelete(post ID) ye method to hamin kelase
                // ke karesh ine ke post ro az to database pak kone. method e badie
            }
            else // hala age moshkeli pish oomade bood va hade aqal ye file lock bood !!!
            {
                try
                { // say kon axi ke to photos bordi to archive ro bargardooni ( momkene narafte bashe to archive be khatere lock boodan ! pas error mide)
                    File.Move("/photos/archived/" + imgName, "/photos/" + imgName);
                }
                catch { }; // error ro begir va hichkari nakon !

                try
                {
                    File.Move("/slides/archived/" + imgName, "/slides/" + imgName);
                }
                catch { };

                try
                {
                    File.Move("/thumbs/archived/" + imgName, "/thumbs/" + imgName);
                }
                catch { };
            }

        }

        public void postDelete(string post_id) // post i ke ID sh felane ro pak kon
        {
            // vase pak kardane ye post aval bayad harchi behesh rabt dare ro pak koni bad khodesho pak koni !
            // comment haye ye post be khode post rabt daran !
            // age aval post ro pak koni , comment ha bi sahab mishan bad error mide !
            // albate in amaliat dorostesh ine ke to database ejra beshe vali inja qabele fahm tar va rahat tare
            // pas aval miaym ye dastoore sql ro execute mikonim ke kole comment hayi ke post_id shoon barabar ba felane ro pak kon :

            entities.Database.ExecuteSqlCommand("DELETE FROM tbl_comments WHERE post_id = '" + post_id + "'");
            
            // bad bia khode posti ke post ID sh barabar ba felane ro pak kon , ok ?!
            entities.Database.ExecuteSqlCommand("DELETE FROM tbl_posts WHERE id = '" + post_id + "'");

        }


        // har bar harki ye post ro baz mikone be soorate taki , bayad yeki be tedade moshahedatesh ezafe beshe
        // be tedade moshahedate kodoom post ezafe beshe ?! hamoon ke ID sh barabar ba post_id hastesh !

        public void postVisitPlus(string post_id)
        {
            entities.Database.ExecuteSqlCommand("UPDATE tbl_posts SET visits = visits + 1 WHERE id='" + post_id + "'");
            // update kon jadvale post ha ro , posti ke idish mosave post_id hast ro be visits hash yeki ezafe kon
        }


        // ma to safhe asli gallery darim , hamoontor ke qablan goftim vaqti mikhaym ye daste data begirim
        // ya az list estefade mikonim ya az datatable
        // inja chon tedade column hayi ke lazem darim kamtar az tedade kole column haye jadvale post hast
        // va hamchenin niaz be yekam taqirate koochik darim , pas miaym az datatable estefade mikonim
        // 
        public DataTable getGallery()
        {

            // tozihate ina ro qablan dadam

            DataTable dt = new DataTable();

            dt.Columns.Add("title", typeof(string));
            dt.Columns.Add("thumbUrl", typeof(string));
            dt.Columns.Add("postUrl", typeof(string));

            List<tbl_posts> posts = entities.tbl_posts.AsEnumerable().ToList();
            // liste kole post ha ro bede biad.

            for (int x = 0; x < posts.Count;x++ )
            {
                dt.Rows.Add(
                    posts[x].title,
                    "/thumbs/" + posts[x].img,
                    "ViewSinglePost.aspx?post_id=" + posts[x].id);

                // be dataTable ezafe kon :

                // onvane posti ke to khooneye x hast ro

                // address e thumbnail e post i ke to khoone x hast ro


                // "ViewSinglePost.aspx?post_id=" + posts[x].id

                // bebin , yeki az safahate tooye in website , safheye ViewSinglePost.aspx hastesh
                // kare in safhe chie ? in ke ye post e taki ro neshoon bede !
                // kodoom post ro neshoon bede ?! hamoon post ke IDsh barabar ba post_id hast ro
                // in page tebqe tarife ma mitoone ye voroodi begire 
                // gereftane voroodish be in soorate ?esmikeTarifKardim = voroodi

            }

            return dt;
        }




        // khob
       
        // bebin ma to chand halat data ro az database migirim ! vase har halat bayad ye Type e khas vase method esh dar nazar begirim 
        // rah haye ziad va mokhtalefi vojood dare vali man faqat az inayi ke payin goftam estefade kardam :
        
        // gahi ye daste/list az etela at ro az ye jadval migirim bedoone hich taqiri
        // type e method ro list e oon jadval qarar midim masalan : public List<tbl_posts> getPosts()

        // gahi ye daste/list az etela at ro migirim ke mikhaym ye seri chizasho taqir bedim ya kamo ziad konim
        // ke to in halat az dataTable baraye type e jadval estefade kardim masalan : public DataTable getPosts()

        // gahi niaz darim ye post e single ro begirim bedoone hich taqir ya kamo ziad kardane sotoonesh
        // ke dar in soorat type ro khode jadval qarar midim , masalan : public tbl_posts getPost()

        // gahi ham ma niaz darim yek ya ye daste post ro ba ye seri taqirat begirim (masalan az join estefade konim ya faqat tedade khasi az column ha ro begirim) !
        // dar in soorat behtare khodemoon ye object tarif konim (tbl_posts masalan khodesh ye object e ... dar vaqe ye class e ke khode model sakhte)
        // hamintor ke to class i ke in payin sakhtim mibini ma ye object dar nazar gereftim va column hayi ke mikhaym daryaft konim ro toosh moshakhas kardim

        // mikham ye seri etela ate talfiqi ro begiram ! vase hamin bayad ye object (class) e khas vase
        // kari ke mikham bokonam tarif konam

        public class postGet
        {
            public string name { get; set; }
            public string title { get; set; }
            public string description { get; set; }
            public string img{get;set;}
            public Int64 visits { get; set; }
            public Int32 commentsCount { get; set; }
            public DateTime datetime { get; set; }
            public bool comment { get; set; }
            public bool slide { get; set; }

            // ina ro nemidonam chetori tozih bedam :| 
            // vali estelahan beheshoon migan getter va setter (estelahe dorosti nist albate)
        }


        // *******************************************************
        //########################################################

        // deqat kon inja yekam pichidas
        
        // kodoom post(ha) ro behem bede ?! hamoon ke ID e postesh hast post_id
        
        // to inja datatable e moon faqat ye post dare , kolan datatable mitoone ye daste etela at bargardoone , vali yeki ham mitoone :D!

        public DataTable postGetSingle(string post_id)
        {
            // vase daryafte record ha be soorate talfiqi va khas bayad az join estefade koni
            // az join vaqti estefade mikonim ke etela at to jadval haye mokhtalef parakande hastesh

            // ma vase namayeshe ye post niaz be ina darim :
            // name kasi ke post gozashte (to jadvale tbl_user hastesh)
            // onvan , tozihat , tasvir , tarikh va zaman , tedade moshahede va ... ye post ( to jadvale tbl_posts hastesh)
            // tedade comment ha ( to jadvale tbl_comments hastesh)

            // hamoontor ke didi etela at parakande shode to chand ta jadval
            // vase daryafte hamashoon bayad az JOIN estefade konim
            // kamel bekham tozih bedam kheeeyli toolanie , faqat ye tozihe mokhtasar az code SQL ke neveshtam midam.
            
            // vase daryafte etela at az SQL Query estefade kardam va barash ye moteqayer tarif kardam vase khanayie behtar 

            string command = "SELECT tbl_user.name,tbl_posts.title,tbl_posts.description,tbl_posts.img,tbl_posts.visits,COUNT(tbl_comments.post_id) as commentsCount,tbl_posts.datetime,tbl_posts.comment,tbl_posts.slide FROM tbl_posts JOIN tbl_user ON tbl_user.username = tbl_posts.username LEFT JOIN tbl_comments on tbl_comments.post_id = tbl_posts.id WHERE tbl_posts.id = '" + post_id + "' GROUP BY tbl_user.name,tbl_posts.title,tbl_posts.description,tbl_posts.img,tbl_posts.visits,tbl_posts.datetime,tbl_posts.comment,tbl_posts.slide ORDER BY tbl_posts.datetime,commentsCount";

            /*
             
             SELECT tbl_user.name,tbl_posts.title,tbl_posts.description,tbl_posts.img,tbl_posts.visits,COUNT(tbl_comments.post_id) as commentsCount,tbl_posts.datetime,tbl_posts.comment,tbl_posts.slide 
             * 
             * entekhab kon az table e user , esm ro . az table e post , title ro va .................. , tedade(record haye tooye jadvale comments) esmeshoBezar commentsCount. az table e post ha , tarikh va zaman ro va .......
             *
             * 
             * 
             FROM tbl_posts JOIN tbl_user ON tbl_user.username = tbl_posts.username
             * 
             * az jadvale post JOIN kon jadvale karbar ro , be soorati ke to jadvale post ha , username hamoon username i hastesh ke to jadvale user hastesh
             * 
             * 
             * 
             LEFT JOIN tbl_comments on tbl_comments.post_id = tbl_posts.id WHERE tbl_posts.id = '" + post_id + "' 
             * 
             * hamchenin az jadvale post , jadvale comment ha ro ham join kon ! be soorati ke to jadvale comment ha , post_id  hamoon post_id i hastesh ke
             * to jadvale post hastesh , harja ke post_id moon barabar bood ba felanfelan
             * 
             * 
             * 
             GROUP BY tbl_user.name,tbl_posts.title,tbl_posts.description,tbl_posts.img,tbl_posts.visits,tbl_posts.datetime,tbl_posts.comment,tbl_posts.slide 
             * bar asase ina gorooh bandi kon (age gorooh bandi nakonim error mide)
             * 
             * 
             * 
             ORDER BY tbl_posts.datetime,commentsCount
             * va bar asase ina moratab kon
            
             */



            List<postGet> post = entities.Database.SqlQuery<postGet>(command).ToList();

            // indafe jaye inke post ha ro mesle qabl az tariqe LINQ (hamoon tbl_posts => tbl_posts.id == id) begirim
            // miaym az tariqe SQL Query migirim

            // etela ati ke qarare daryaft beshe ro listi az class i ke bala sakhtim tayin kardim
            // pas miaym ye list az classemon misazim va meqdaresh ro barabar ba chizi qarar midim ke SQL Query barmigardoone

            

            // ina ro ham ke tozih dadam
            DataTable dt = new DataTable();

            dt.Columns.Add("name", typeof(string));
            dt.Columns.Add("title", typeof(string));
            dt.Columns.Add("description", typeof(string));
            dt.Columns.Add("img", typeof(string));
            dt.Columns.Add("visits", typeof(Int64));
            dt.Columns.Add("commentsCount", typeof(Int32));
            dt.Columns.Add("DateTime", typeof(string));
            dt.Columns.Add("comment", typeof(bool));
            dt.Columns.Add("slide", typeof(bool));

            dt.Rows.Add(post[0].name, post[0].title, post[0].description, post[0].img, post[0].visits, post[0].commentsCount, post[0].datetime.ToShortDateString() + " - " + post[0].datetime.ToShortTimeString(), post[0].comment, post[0].slide);

            return dt;

        }



        // vase daryafte Slide to safhe aslie site ke neshoon mide

        public DataTable postGetSlide()
        {
            DataTable dt = new DataTable();

            dt.Columns.Add("title",typeof(string));
            dt.Columns.Add("post_id",typeof(string));
            dt.Columns.Add("img",typeof(string));
            

            // ma faqat tasaviri ro to slideshow neshoon midim ke to posteshoon emkane Slide ro fa al kardim pas :
            // list e post hayi ro baram biar ke Slide eshoon TRUE hastesh !

            List<tbl_posts> posts = entities.tbl_posts.Where(tbl_posts => tbl_posts.slide == true).ToList();

            for (int x = 0; x < posts.Count; x++)
            {
                dt.Rows.Add(posts[x].title, posts[x].id, posts[x].img);
            }

            return dt;

        }



        // vaqti post ro edit mikonim niaz darim etela ate dakhele database Update beshan
        // chia mitoonan update beshan ? onvan , matn , slide e ya na ?, comment ha fa al hastan ya na ?
        // kodoom post ro ?! hamoon ke ID sh barabare ba originalID
        // pas maqadire voroodi ro tayin mikonim 

        public void postUpdate(string originalID, string title, string description, bool slide, bool comment)
        {
            string command = "UPDATE tbl_posts SET title='" + title + "',description = '" + description + "',slide = '" + slide + "', comment = '" + comment + "' WHERE id= '" + originalID + "'";
            // miaym ye SQL Code minevisim vase update
            // kheyli sadas !
            // UPDATE esmeJadval SET esmeField = MeqdareJadid WHERE felanesh = felan
            // update kon tbl_posts ro , set kon title = meqdareJadid , description = meqdareJadid  , ......... harja post ID = ooni ke man migam bood

            entities.Database.ExecuteSqlCommand(command);
            // hala bia SQL Commandi ke tarif kardam ro execute kon
        }


        // vase sakhtane post
        // post e ma ye seri etela at dare ke hame to class e tbl_posts moshakhas shode
        // goftim , to model i ke EF misaze , vase har jadval ye class dar nazar migire
        // fek kon to bayad yeja sabte nam koni vase sabte niam niaz be nam , name khanevadegi , code meli , tarikhe tavalod va name pedar daran
        // 2 halat dare ! ya inke be soorate shafahi khodet hame ro migi
        // ya inke cart e melito midi be taraf khodesh vared mikone bedoone inke harfi bezani
        // inja ma oomadim az cart e meli estefade kardim :D

        // ye post besaz ! meqdare voroodish chi bashe ?! harchi ke tbl_posts mikhad !

        public void postsCreate(tbl_posts post)
        {
            entities.tbl_posts.Add(post);
            // be tbl_posts az instance i ke sakhtim add kon ! chio ?! meqdare voroodi ro
            
            // hala tabe e saveChanges ro seda mizanim ke taqirat ro save kone !
            entities.SaveChanges();
        }




        // vaqti safheye namayeshe post e taki baz mishe ye seri etela at namayesh dade mishe dige
        // masalan post , ax , tedade comment ha , kasi ke post ro gozashte va ...
        // hamoon JOIN ke oon bala goftam !

        // alave bar oona , bayad comment haye oon post ham ham namayesh dade beshan !

        // chon niaz be ye taqire koochooloo darim az dataTable estefade mikonim

        public DataTable commentsGet(string post_id)
        {

            // kodoom comment ha ?!
            // hamoona ke avalan postID shoon barabare ba chizi ke man migam
            // sanian comment hayi ke TAYID shodan !


            List<tbl_comments> comments = entities.tbl_comments.Where(tbl_comments => tbl_comments.post_id == post_id && tbl_comments.confirmed != "").OrderByDescending(tbl_comments=> tbl_comments.datetime).ToList();
            // ye listi az comment ha bede ke
            // az jadvale comment ha , post ID sh barabar bashe ba chizi ke man dadam
            // VA HAMCHENIN , comment tayid shode bashe ( yani field e confirm esh khali nabashe)
            // bad bia bar asase tarikh va zamani ke comment ro gozashte be soorate nozooli moratab kon
            // va tabdilesh kon be list


            // ina ro qablan tozih dadam
            DataTable dt = new DataTable();

            dt.Columns.Add("id", typeof(string));
            dt.Columns.Add("name", typeof(string));
            dt.Columns.Add("description", typeof(string));
            dt.Columns.Add("datetime", typeof(string));

            // ye halqe az 0 ta tedade comment ha menhaye 1 ke tozih dadam chera

            for (int a = 0; a < comments.Count; a++)
            {
                // inam ke motmaenan yadete
                string datetime = comments[a].datetime.ToShortDateString() + " - " + comments[a].datetime.ToShortTimeString();
                
                dt.Rows.Add(comments[a].id, comments[a].name, comments[a].description.Replace("\r\n", "<br/>"), datetime);
                // bad bia ina ro too DataTable ezafe kon ...

                // comments[a].description.Replace("\r\n", "<br/>"), datetime

                // in yani chi ?! bebin , to windows masalan to textBox harja Enter mizani mire satre bad , ye \r\n mizare

                // masalan to c# ye textBox bezar va qabeliate e MultiLine sh ro fa al kon
                // bad bezan : textBox1.Text = "salam. \r\n man helia hastam. \r\n \r\n bye bye.
                // intori neshoon mide to text box :

                // salam.
                // man helia hastam.
                //
                // bye bye.

                // vali tooye HTML vase inke beri satre bad bayad <br/> bezari !
                // khode windows az \r\n estefade karde , to bayad vase namayeshesh to web , \r\n ro tabdil koni be <br/>
                // vase hamin mizani ReplaceKon( harja \r\n bood , jash <br/> ro bzar)

            }

            // dataTable amo bargardoon
            return dt;
        }


        // harki comment mizare, in method seda zade mishe va comment sakhte mihe
        // tozihatesh ham mesle tozihate sakhtane post e

        public void commentCreate(tbl_comments comment)
        {
            entities.tbl_comments.Add(comment);


            // hala tabe e saveChanges ro seda mizanim ke taqirat ro save kone !

            entities.SaveChanges();
        }


        // to qesmate modiriate comment ha , ye listi az comment ha baramoon namayesh mide
        // ke ma oomadim barash filter ham gozashtim ! goftim faqat comment hayi ro namayesh bede ke masalan
        // confirm shodan ! ya confirm nashodan ! ya comment hayi ke to post ba ID e felan gozashte shodan !

        public DataTable commentsManage(int filter,string post_id)
        {
            List<tbl_comments> comments = null;
            // ino qablan tozih dadam vase post ha

            if (post_id == "") // age post ID ro vared nakarde bood
            {
                switch (filter) // inam tozih dadam
                {
                    case 0:
                        {
                            comments = entities.tbl_comments.AsEnumerable().OrderByDescending(tbl_comments => tbl_comments.datetime).ToList();
                            // kole comment ha ro bede va be soorate nozooli bar asase tarikhZaman moratab kon
                            break;
                        }
                    case 1:
                        {
                            comments = entities.tbl_comments.Where(tbl_comments => tbl_comments.confirmed != "").OrderByDescending(tbl_comments => tbl_comments.datetime).ToList();
                            // oonayi ro bede ke Confirm shodan ! va moratab kon ...
                            break;
                        }
                    case 2:
                        {
                            comments = entities.tbl_comments.Where(tbl_comments => tbl_comments.confirmed == "").OrderByDescending(tbl_comments => tbl_comments.datetime).ToList();
                            // oonayi ro bede ke confirm nashodan , va moratab kon ...
                            break;
                        }
                }
            }
            else // age Post ID ro vared karde bood !
            {
                switch (filter)
                {
                    case 0:
                        {
                            comments = entities.tbl_comments.Where(tbl_comments => tbl_comments.post_id == post_id).OrderByDescending(tbl_comments => tbl_comments.datetime).ToList();
                            // oonayi ro bede ke post ID shoon barabare ba felan ! va moratab kon ...
                            break;
                        }
                    case 1:
                        {
                            comments = entities.tbl_comments.Where(tbl_comments => tbl_comments.confirmed != "" && tbl_comments.post_id == post_id).OrderByDescending(tbl_comments => tbl_comments.datetime).ToList();
                            // oonayi ro bede ke post ID shoon barabare ba felan va hamchenin tayid shodan ! va moratab kon ...
                            break;
                        }
                    case 2:
                        {
                            comments = entities.tbl_comments.Where(tbl_comments => tbl_comments.confirmed == "" && tbl_comments.post_id == post_id).OrderByDescending(tbl_comments => tbl_comments.datetime).ToList();
                            // oonayi ro bede ke post ID shoon barabare ba felan va hamchenin tayid nashodan ! va moratab kon ...
                            break;
                        }
                }
            }

            DataTable dt = new DataTable();

            dt.Columns.Add("no", typeof(string));
            dt.Columns.Add("id", typeof(string));
            dt.Columns.Add("post_id", typeof(string));
            dt.Columns.Add("name", typeof(string));
            dt.Columns.Add("email", typeof(string));
            dt.Columns.Add("description", typeof(string));
            dt.Columns.Add("datetime", typeof(string));
            dt.Columns.Add("ip", typeof(string));
            dt.Columns.Add("confirmed", typeof(string));


            // ina ro ham hame ro tozih dadam

            for (int x = 0; x < comments.Count; x++)
            {
                string confirmed,desc;

                if (comments[x].confirmed != "")
                    confirmed = "img/btn_checked.png";
                else
                    confirmed = "img/btn_checkednot.png";

                if(comments[x].description.Length > 30)
                    desc = comments[x].description.Substring(0,30) + " ...";
                else
                    desc = comments[x].description;

                dt.Rows.Add((x+1).ToString(),comments[x].id, comments[x].post_id, comments[x].name, comments[x].email, desc,
                    comments[x].datetime.ToShortDateString() + " - " + comments[x].datetime.ToShortTimeString()
                    , comments[x].ip, confirmed);
            }

            return dt;
        }


        // vase tayide comment !
        // operation chie ?! tayid beshe ya nashe ?! age 1 bood : age tayid shode bood az halate tayid bardar, age nashode bood tayidesh kon
        // age 2 bood : che tayid shode bood che nashode bood to bazam tayid kon :|
        // age 3 bood : che tayid shode bood che nashode bood to az tayid daresh biar

        // comment_id chie ?! kodoom comment ro mikhay tayid koni ?! ....
        
        // user_id chie ?! ki comment ro tayid karde ?!

        public void commentConfirm(byte operation, string comment_id,string user_id)
        {

            // inja mitoonestim az switch ham estefade konim vali man eshqam keshide az if estefade kardam :D
            // age operation 1 bood yani age tayid shode bood az tayid baresh dar , age tayid nashode bood tayidesh kon
            if (operation == 1)
            {
                // age (entities.jadvaleCommentha.RecordeTaki(az jadvale comment ha => oonja ke ID e comment barabare ba chizi ke man migam).fieldeConfirm == "") oonvaqt
                if (entities.tbl_comments.Single(tbl_comments => tbl_comments.id == comment_id).confirmed == "")
                {
                    // in SQL Command ro execute kon :
                    // update kon tbl_comment ro , user_id ro beriz to confirmed , harja ke id mosavi bood ba chizi ke man migam ... 
                    entities.Database.ExecuteSqlCommand("UPDATE tbl_comments SET confirmed = '" + user_id + "' WHERE id = '" + comment_id + "'");
                }
                else
                    entities.Database.ExecuteSqlCommand("UPDATE tbl_comments SET confirmed = '' WHERE id = '" + comment_id + "'");
                // age tayid shode bood ham confirmed ro khali kon harja ke man migam

            } // baqiasham maloome dige
            else if (operation == 2)
                entities.Database.ExecuteSqlCommand("UPDATE tbl_comments SET confirmed = '" + user_id + "' WHERE id = '" + comment_id + "'");
            else if (operation == 3)
                entities.Database.ExecuteSqlCommand("UPDATE tbl_comments SET confirmed = '' WHERE id = '" + comment_id + "'");

        }
        

        // niazi be tozih nist dige
        public void commentDelete(string comment_id)
        {
            entities.Database.ExecuteSqlCommand("DELETE FROM tbl_comments WHERE id = '" + comment_id + "'");
        }





        // in method vase ine ke check konim , useri ke mikhad login kone vojood dare ya na ?!
        // hamintor ke mibini ma method ro az no e bazgashti ba type e Boolean dar nazar gereftim
        // yani meqdari ke barmigardoone ya True hastesh ya False
        // be ebarate sade kare in method ine

        // aya karbari ba username e felan va password e felan vojood darad ?! javab ya True hastesh ya False
        public bool isUserExist(string username, string password)
        {
            // hamoontor ke to tozihat gofte boodam qablan , password Encrypt (ramz negari) mishe ba estefade az Algoritm e MD5 Hash!
            // vase encrytion (ramz negari) az ye class e amade estefade kardam be esme Crypto ke to Solution explorer mitooni bebinish
            // 2 ta method dare , yekish vase ramznegari (Encrypt) , yekish vase ramz goshayi(Decrypt)
            // har kodoom az method hayi ke goftam , 2 ta voroodi migiran , yekish ke matn hastesh , yekish Key hastesh
            // ramz negari bar asase ye kelid anjam mishe ke mitoonim dasti bezanim
            // masalan age begim Crypto.Encrypt("helia","key1") ye meqdare ramz shode behemoon mide
            // age begim Crypto.Decrypt(oon meqdare ramzi ke az encrypt gereftim , "key1") , helia ro behemoon mide

            // age to encrypt bejaye key1 masalan bezarim key2 , ramzi ke mide ba ramzi ke ba key1 dade farq dare !
            // age ba key1 encrypt konim va ba key2 decrypt konim ya error mide ya ye meqdare eshtebah mide

            // vase amniate behtar , behtare ke kelid e ramznegari sabet nabashe !
            // age masalan key ro sabet dar nazar begirim bezarim "helia" oonvaqt age ye hacker
            // site ro hack kone va be database dastrasi peyda kone va befahme kelid e ramz helia hastesh
            // har ramzi ke bezari va encrypt beshe , chon kelid ro dare rahat mitone decrypt kone

            // vali ma miaym kelid ro khode password dar nazar migirm !
            // masalan age to password ro avaz koni bezari 12345 , password ba kelide 12345 ramz mishe
            // age passwordeto avaz koni bezari abasAgha , password ba kelide abasAgha ramz mishe (kelide khode abbas agha na ha , esme kelidesh)
            // bad ke password encrypt shod miaym to database password e encrypt shode ro zakhire mikonim
            
            // vase inke befahmim karbar ramze dorost vared karde ya na , ramzi ke vared karde ro encrypt mikonim , ba ramzi ke to database hast moqayese
            // mikonim . age joftesh yeki bood pas password ro dorost zade
            // injasho yekam bad tozih dadam be nazaram shayad yekam bad motevajeh beshi , age moshkeli bood bego ta bishtar tozih bedam

            password = Crypto.Encrypt(password, password).ToString();
            

            // inja ye kare yekam kharaki kardam vali moshkeli nadare :D
            // miaym migim age tedade karbar hayi ke (usernameshoon hast chizi ke ma migim , va passwordeshoon hast chizi ke ma migim) > 0 bood pas karbar vojood dare
            // va etela at doroste ! pas True ro bargardoon

            if (entities.tbl_user.Count(tbl_user => tbl_user.username == username && tbl_user.password == password) > 0)
                return true;
            else// age na False ro bargardoon
                return false;
        }


        // to Settings e panel e modiriat ma etela at ro avaz mikonim , yeki az chizayi ke mishe taqir dad name hastesh
        // vazehe chikar kardam dige
        // name karbari ke usernamesh felan hast ro taqir bede be chizi ke ma migim

        public void updateName(string username, string newName)
        {
            entities.Database.ExecuteSqlCommand("UPDATE tbl_user SET name = '" + newName + "'  WHERE username='" + username + "'");
        }


        // inam talfiqi az 2 ta method e qablie
        public void updatePassword(string username, string newPassword)
        {

            string password = password = Crypto.Encrypt(newPassword, newPassword).ToString();

            entities.Database.ExecuteSqlCommand("UPDATE tbl_user SET password = '" + password + "'  WHERE username='" + username + "'");
        }


        // to settings to panel e modiriat vaqti miri , esmi ke alan hast ro bayad neshoon bede ta befahmi bayad avaz koni ya na
        // in method esm ro barmigardoone

        public string getName(string username)
        {
            return entities.tbl_user.Single(tbl_user => tbl_user.username == username).name;
        }
    }

}