using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using System.Text;
using DataLibrary;
using Sybase.DataWindow;

namespace GcoopServiceCs
{
    public class AdminService
    {
        private String connectionString;

        public AdminService(String connectionString)
        {
            this.connectionString = connectionString;
        }

        public Boolean RemoveGroupAppWins(String Application, String Group_Name, String Coop_id)
        {
            Sta ta = new Sta(connectionString);
            ta.Transection();
            Boolean result = false;
            try
            {
                string sql2 = "";
                try
                {
                    string sql = "delete from amsecuseapps where application='" + Application + "' and user_name='" + Group_Name + "' and coop_id='" + Coop_id + "'";
                    ta.Exe(sql);
                }
                catch { }
                try
                {
                    sql2 = "update amsecpermiss set check_flag=0,save_status=0 where application='" + Application + "' and user_name='" + Group_Name + "' and coop_id='" + Coop_id + "'";
                    ta.Exe(sql2);
                }
                catch { }
                ta.Commit();
                ta.Close();
            }
            catch (Exception ex)
            {
                ta.RollBack();
                ta.Close();
                throw ex;
            }
            return result;
        }
        public Boolean RemoveAppWins(String Application, String User_Name, String Coop_id)
        {
            Sta ta = new Sta(connectionString);
            ta.Transection();
            Boolean result = false;
            try
            {
                string sql2 = "";
                try
                {
                    string sql = "delete from amsecuseapps where application='" + Application + "' and user_name='" + User_Name + "' and coop_id='" + Coop_id + "'";
                    ta.Exe(sql);
                }
                catch { }
                try
                {
                    sql2 = "update amsecpermiss set check_flag=0,save_status=0 where application='" + Application + "' and user_name='" + User_Name + "' and coop_id='" + Coop_id + "'";
                    ta.Exe(sql2);
                }
                catch { }
                ta.Commit();
                ta.Close();
            }
            catch (Exception ex)
            {
                ta.RollBack();
                ta.Close();
                throw ex;
            }
            return result;

        }
        public Boolean SaveGroupAppWins(String Application, String Group_Name, String Coop_id)
        {
            Sta ta = new Sta(connectionString);
            ta.Transection();
            Boolean result = false;
            try
            {
                string sql3 = "";
                string window_id = "";
                string sql2 = "";
                try
                {
                    sql3 = "insert into amsecuseapps (coop_id,application,user_name,coop_control) " +
                    " values ('" + Coop_id + "','" + Application + "','" + Group_Name + "','"+Coop_id+"')";
                    ta.Exe(sql3);
                }
                catch { }
                try
                {
                    string sql = "select window_id from amsecwins where application='" + Application + "'";
                    Sdt dt = ta.Query(sql);
                    while (dt.Next())
                    {
                        try
                        {
                            window_id = dt.GetString("window_id");
                            sql2 = "insert into amsecpermiss (coop_id,user_name,application,window_id,save_status,check_flag,coop_control) "
                                + "values ('" + Coop_id + "','" + Group_Name + "','" + Application + "','" + window_id + "',0,0,'"+Coop_id+"')";
                            ta.Exe(sql2);
                        }
                        catch { }
                    }
                }
                catch { }

                ta.Commit();
                ta.Close();
            }
            catch (Exception ex)
            {
                ta.RollBack();
                ta.Close();
                throw ex;
            }
            return result;
        }
        public Boolean SaveAppWins(String Application, String User_Name, String Coop_id)
        {
            Sta ta = new Sta(connectionString);
            ta.Transection();
            Boolean result = false;
            try
            {
                string sql3 = "";
                string window_id = "";
                string sql2 = "";
                try
                {
                    sql3 = "insert into amsecuseapps (coop_id,application,user_name,coop_control) " +
                    " values ('" + Coop_id + "','" + Application + "','" + User_Name + "','" + Coop_id + "')";
                    ta.Exe(sql3);
                }
                catch { }
                try
                {
                    string sql = "select window_id from amsecwins where application='" + Application + "'";
                    Sdt dt = ta.Query(sql);
                    while (dt.Next())
                    {
                        try
                        {
                            window_id = dt.GetString("window_id");
                            sql2 = "insert into amsecpermiss (coop_id,user_name,application,window_id,save_status,check_flag,coop_control) "
                                + "values ('" + Coop_id + "','" + User_Name + "','" + Application + "','" + window_id + "',0,0,'"+Coop_id+"')";
                            ta.Exe(sql2);
                        }
                        catch { }
                    }
                }
                catch { }
                ta.Commit();
                ta.Close();
            }
            catch (Exception ex)
            {
                ta.RollBack();
                ta.Close();
                throw ex;
            }
            return result;
        }
        public Boolean SaveUserPermissChange(String User_Name, String Coop_id)
        {
            Sta ta = new Sta(connectionString);
            ta.Transection();
            Boolean result = false;
            string application = "", sqlapp = "", sqlinsertapp = "";
            string sqlpermissall = "", sqlinsertwins = "", sqlupdatewins = "", window_id = "";
            string save_status = "0", check_flag = "0", sqlupdatewins0 = "", sqlupdate0 = "";
            try
            {
                sqlapp = "select ua.application as application from amsecgroupings gp left join amsecuseapps ua on(gp.group_name = ua.user_name) " +
                    " where gp.user_name = '" + User_Name + "'  and ua.coop_id='" + Coop_id + "' group by ua.application";
                Sdt dt = ta.Query(sqlapp);
                while (dt.Next())
                {
                    try
                    {
                        application = dt.GetString("application");
                        sqlinsertapp = "insert into amsecuseapps (coop_id,user_name,application,coop_control) values " +
                            " ('" + Coop_id + "','" + User_Name + "','" + application + "','"+Coop_id+"')";
                        ta.Exe(sqlinsertapp);
                    }
                    catch { }
                    //Set ให้เป็น 0 ก่อน
                    sqlupdatewins0 = "update amsecpermiss set check_flag=" + check_flag + ",save_status=0" +
                    " where coop_id='" + Coop_id + "' and user_name='" + User_Name + "' and application='" + application + "'";
                    ta.Exe(sqlupdatewins0);
                    //
                    try
                    {
                        sqlpermissall = "select distinct pm.window_id as win,pm.save_status as save_sta,pm.check_flag as flag " +
                                    "from amsecgroupings gp left join amsecuseapps ua on(gp.group_name = ua.user_name) " +
                                    "left join amsecpermiss pm on (ua.application = pm.application and gp.group_name = pm.user_name) " +
                                    "where gp.user_name = '" + User_Name + "' " +
                                   " and ua.coop_id='" + Coop_id + "' and pm.application='" + application + "'";
                        Sdt dt2 = ta.Query(sqlpermissall);
                        while (dt2.Next())
                        {
                            window_id = dt2.GetString("win");
                            save_status = dt2.GetString("save_sta");
                            check_flag = dt2.GetString("flag");
                            try
                            {
                                sqlinsertwins = "insert into amsecpermiss (coop_id,user_name,application,window_id,save_status,check_flag,coop_control) " +
                                    " values ('" + Coop_id + "','" + User_Name + "','" + application + "','" + window_id + "','" + save_status + "','" + check_flag + "','"+Coop_id
                                    +"')";
                                ta.Exe(sqlinsertwins);
                            }
                            catch
                            {
                                if (save_status == "0" && check_flag != "0")
                                {
                                    sqlupdatewins = "update amsecpermiss set check_flag=" + check_flag +
                                    " where coop_id='" + Coop_id + "' and user_name='" + User_Name + "' and window_id='" + window_id + "'";
                                    ta.Exe(sqlupdatewins);
                                }
                                else if (save_status != "0" && check_flag == "0")
                                {
                                    sqlupdatewins = "update amsecpermiss set save_status=" + save_status +
                                    " where coop_id='" + Coop_id + "' and user_name='" + User_Name + "' and window_id='" + window_id + "'";
                                    ta.Exe(sqlupdatewins);
                                }
                                else if (save_status == "1" && check_flag == "1")
                                {
                                    sqlupdatewins = "update amsecpermiss set save_status=" + save_status + ",check_flag=" + check_flag +
                                    " where coop_id='" + Coop_id + "' and user_name='" + User_Name + "' and window_id='" + window_id + "'";
                                    ta.Exe(sqlupdatewins);
                                }
                            }
                        }
                    }
                    catch { }
                    //amsecreportpermiss
                    //Set ให้เป็น 0 ทั้งหมดก่อน
                    sqlupdate0 = "update amsecreportpermiss set check_flag= 0 " +
                    " where coop_id='" + Coop_id + "' and user_name='" + User_Name + "' and group_id in (select distinct group_id from webreportgroup where application ='" + application + "')";
                    ta.Exe(sqlupdate0);
                    //
                    try
                    {
                        string report_id = "", group_id = "";
                        string sqlreport = @"select rp.report_id as r , rp.group_id as g  , rp.check_flag as flag 
                        from amsecgroupings gp left join amsecreportpermiss rp on (gp.group_name = rp.user_name) where gp.user_name ='" + User_Name + @"' and rp.coop_id='" +
                        Coop_id + @"'  and   rp.group_id in (select distinct group_id from webreportgroup where application ='" + application + "')";
                        Sdt re = ta.Query(sqlreport);
                        while (re.Next())
                        {
                            report_id = re.GetString("r");
                            group_id = re.GetString("g");
                            check_flag = re.GetString("flag");
                            try
                            {
                                sqlinsertwins = "insert into amsecreportpermiss (coop_id,user_name,group_id,report_id,check_flag) " +
                                    " values ('" + Coop_id + "','" + User_Name + "','" + group_id + "','" + report_id + "','" + check_flag + "')";
                                ta.Exe(sqlinsertwins);
                            }
                            catch
                            {
                                if (check_flag != "0")
                                {
                                    sqlupdatewins = "update amsecreportpermiss set check_flag=" + check_flag +
                                    " where coop_id='" + Coop_id + "' and user_name='" + User_Name + "' and report_id='" + report_id + "'";
                                    ta.Exe(sqlupdatewins);
                                }
                            }
                        }
                    }
                    catch
                    {

                    }
                }
                ta.Commit();
                ta.Close();
            }
            catch (Exception ex)
            {
                ta.RollBack();
                ta.Close();
                throw ex;
            }
            return result;
        }
        //
        public Boolean RemoveGroupAppReport(String Application, String Group_Name, String Coop_id)
        {
            Sta ta = new Sta(connectionString);
            ta.Transection();
            Boolean result = false;
            try
            {
                string sql2 = "";
                try
                {
                    sql2 = "update amsecreportpermiss set check_flag=0 where group_id in (select distinct group_id from webreportgroup where  application='" + Application + "' and user_name='" + Group_Name + "' and coop_id='" + Coop_id +
                        "')  and ( user_name in (select user_name from amsecgroupings where group_name ='" + Group_Name + "') or user_name ='" + Group_Name + "')";
                    ta.Exe(sql2);
                }
                catch { }
                ta.Commit();
                ta.Close();
            }
            catch (Exception ex)
            {
                ta.RollBack();
                ta.Close();
                throw ex;
            }
            return result;
        }
        public Boolean RemoveAppReport(String Application, String User_Name, String Coop_id)
        {
            Sta ta = new Sta(connectionString);
            ta.Transection();
            Boolean result = false;
            try
            {
                string sql2 = "";
                try
                {
                    sql2 = "update amsecreportpermiss set check_flag=0 where group_id in (select distinct group_id from webreportgroup where  application='" + Application + "' and user_name='" + User_Name + "' and coop_id='" + Coop_id + "')";
                    ta.Exe(sql2);
                }
                catch { }
                ta.Commit();
                ta.Close();
            }
            catch (Exception ex)
            {
                ta.RollBack();
                ta.Close();
                throw ex;
            }
            return result;

        }
        public Boolean SaveGroupAppReport(String Application, String Group_Name, String Coop_id)
        {
            Sta ta = new Sta(connectionString);
            ta.Transection();
            Boolean result = false;
            try
            {
                string sql2 = "";
                string report_id = "", group_id = "";
                try
                {
                    string sql = "select report_id,group_id from webreportdetail where group_id in (select distinct group_id from webreportgroup where application='" + Application + "')";
                    Sdt dt = ta.Query(sql);
                    while (dt.Next())
                    {
                        try
                        {
                            report_id = dt.GetString("report_id");
                            group_id = dt.GetString("group_id");
                            sql2 = "insert into amsecreportpermiss (coop_id,user_name,group_id,report_id,check_flag) "
                                + "values ('" + Coop_id + "','" + Group_Name + "','" + group_id + "','" + report_id + "',0)";
                            ta.Exe(sql2);
                        }
                        catch { }
                    }
                }
                catch { }
                ta.Commit();
                ta.Close();
                ta.Close();
            }
            catch (Exception ex)
            {
                ta.RollBack();
                ta.Close();
                throw ex;
            }
            return result;
        }
        public Boolean SaveAppReport(String Application, String User_Name, String Coop_id)
        {
            Sta ta = new Sta(connectionString);
            ta.Transection();
            Boolean result = false;
            try
            {
                string sql2 = "";
                string report_id = "", group_id = "";
                try
                {
                    string sql = "select report_id,group_id from webreportdetail where group_id in (select distinct group_id from webreportgroup where application='" + Application + "')";
                    Sdt dt = ta.Query(sql);
                    while (dt.Next())
                    {
                        try
                        {
                            report_id = dt.GetString("report_id");
                            group_id = dt.GetString("group_id");
                            sql2 = "insert into amsecreportpermiss (coop_id,user_name,group_id,report_id,check_flag) "
                                + "values ('" + Coop_id + "','" + User_Name + "','" + group_id + "','" + report_id + "',0)";
                            ta.Exe(sql2);
                        }
                        catch { }
                    }
                }
                catch { }
                ta.Commit();
                ta.Close();
            }
            catch (Exception ex)
            {
                ta.RollBack();
                ta.Close();
                throw ex;
            }
            return result;
        }
        public Boolean SaveUserPermissReportChange(String User_Name, String Coop_id)
        {
            Sta ta = new Sta(connectionString);
            ta.Transection();
            Boolean result = false;
            string application = "", sqlapp = "", sqlinsertapp = "";
            string sqlinsertwins = "", sqlupdatewins = "", check_flag = "0", sqlupdate0 = "";
            try
            {
                sqlapp = "select ua.application as application from amsecgroupings gp left join amsecuseapps ua on(gp.group_name = ua.user_name) " +
                    " where gp.user_name = '" + User_Name + "'  and ua.coop_id='" + Coop_id + "' group by ua.application";
                Sdt dt = ta.Query(sqlapp);
                while (dt.Next())
                {
                    try
                    {
                        application = dt.GetString("application");
                        sqlinsertapp = "insert into amsecuseapps (coop_id,user_name,application,coop_control) values " +
                            " ('" + Coop_id + "','" + User_Name + "','" + application + "','"+Coop_id+"')";
                        ta.Exe(sqlinsertapp);
                    }
                    catch { }
                    //amsecreportpermiss
                    //Set ให้เป็น 0 ทั้งหมดก่อน
                    sqlupdate0 = "update amsecreportpermiss set check_flag= 0 " +
                    " where coop_id='" + Coop_id + "' and user_name='" + User_Name + "' and group_id in (select distinct group_id from webreportgroup where application ='" + application + "')";
                    ta.Exe(sqlupdate0);
                    //
                    try
                    {
                        string report_id = "", group_id = "";
                        string sqlreport = @"select distinct rp.report_id as report_id , rp.group_id as group_id  , rp.check_flag as flag 
                        from amsecgroupings gp left join amsecreportpermiss rp on (gp.group_name = rp.user_name) where gp.user_name ='" + User_Name + @"' and rp.coop_id='" +
                        Coop_id + @"'  and   rp.group_id in (select distinct group_id from webreportgroup where application ='" + application + "')";
                        Sdt dt8 = ta.Query(sqlreport);
                        while (dt8.Next())
                        {
                            report_id = dt8.GetString("report_id");
                            group_id = dt8.GetString("group_id");
                            check_flag = dt8.GetString("flag");
                            try
                            {
                                sqlinsertwins = "insert into amsecreportpermiss (coop_id,user_name,group_id,report_id,check_flag) " +
                                    " values ('" + Coop_id + "','" + User_Name + "','" + group_id + "','" + report_id + "','" + check_flag + "')";
                                ta.Exe(sqlinsertwins);
                            }
                            catch
                            {
                                if (check_flag != "0")
                                {
                                    sqlupdatewins = "update amsecreportpermiss set check_flag=" + check_flag +
                                    " where coop_id='" + Coop_id + "' and user_name='" + User_Name + "' and report_id='" + report_id + "'";
                                    ta.Exe(sqlupdatewins);
                                }
                            }
                        }
                    }
                    catch
                    {
                    }
                }
                ta.Commit();
                ta.Close();
            }
            catch (Exception ex)
            {
                ta.RollBack();
                ta.Close();
                throw ex;
            }
            return result;
        }
    }
}