using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Text;
using System.Data;
using MySql.Data.MySqlClient;
using CLS_DL;
using CLS_PL;
using CLS_BL;
using SUNWEB;
using System.IO;
using System.Security.Cryptography;
namespace CLS_BL
{


    public class BL_SELECT
    {
        BL_ErrorLog ObjLog = new BL_ErrorLog();
        DataTable DT = new DataTable();
        MYSQLDB MysqlObj = new MYSQLDB();
        MYSQLDB objMain = new MYSQLDB();
        MYSQLCLOUD CloudSqlObj = new MYSQLCLOUD();
        DataTable Dt = new DataTable();
        
        string TotSales = string.Empty;
        /// <summary>
        ///  Get the Cm_Name through Cm_id in Companymaster 
        /// </summary>
        /// <param name="Cm_id"></param>
        /// <returns></returns>
        public string GetCmp_Namee(string Cm_id)
        {
            string ComName = string.Empty;
            try
            {
                ComName = MysqlObj.ExecuteScalar("select cm_name    from portaldb.companymaster where cm_id='" + Cm_id + "' ");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);

            }
            return ComName;
        }

        /// <summary>
        ///  This Functiion uses to Load All the CompanyList 
        /// </summary>
        /// <returns></returns>
        public DataTable GetAllCmp_Namee()
        {

            try
            {
                Dt = MysqlObj.GetData_DT("select *  from    portaldb.companymaster  ");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return Dt;
        }

        public DataTable GetNotificationList()
        {

            try
            {
                Dt = MysqlObj.GetData_DT("select * from portaldb.NotificationMaster  where Status='A' ");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return Dt;
        }

        /// <summary>
        ///  This Function uses to Load All CompanyList
        /// </summary>
        /// <returns></returns>
        /// 
        public DataTable GetAllCmp_List()
        {

            try
            {
                Dt = MysqlObj.GetData_DT("select cm_no,cm_name  from    portaldb.companymaster");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return Dt;
        }


        /// <summary>
        ///  This Funcation  uses to Get the CompanyName based on cm_id
        /// </summary>
        /// <param name="cm_idd"></param>
        /// <returns></returns>
        public string GetComp_Name(string cm_idd)
        {
            string AliasValues = string.Empty;
            try
            {
                AliasValues = MysqlObj.ExecuteScalar("select cm_name    from portaldb.companymaster where cm_id='" + cm_idd + "' ");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return AliasValues;
        }

        /// <summary>
        ///  This function uses to take the count for the Cd_Branch Count : Purpose - To Create Brabch_Alias Name Increment
        /// </summary>
        /// <param name="AliasName"></param>
        /// <returns></returns>
        /// 
        public string Get_Cd_BranchCnt(string Cm_Id)
        {
            string ChkAlias = string.Empty;
            try
            {
                ChkAlias = MysqlObj.ExecuteScalar("Select count(*) from  portaldb.companydetails   where cm_id =" + Cm_Id + "  ");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return ChkAlias;
        }

        /// <summary>
        ///  Get the Alias Name using the cm_alias Name 
        /// </summary>
        /// <param name="cm_alias"></param>
        /// <returns></returns>
        /// 
        public string ChkAliasName(string cm_alias)
        {
            string AliasValues = string.Empty;
            try
            {
                AliasValues = MysqlObj.ExecuteScalar("select cm_alias    from portaldb.companymaster where cm_alias='" + cm_alias + "' ");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return AliasValues;
        }

        public string Get_HQNAME(string cm_ID)
        {
            string AliasValues = string.Empty;
            try
            {
                AliasValues = MysqlObj.ExecuteScalar("Select cd_branchAlias  from portaldb.companydetails where  cd_hq=1 and  cm_id ='" + cm_ID + "' ");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return AliasValues;
        }

        /// <summary>
        ///  Get the EmailAddress using the cm_id 
        /// </summary>
        /// <param name="cm_ID"></param>
        /// <returns></returns>
        public string Get_EmaildAddr(string cm_ID)
        {
            string AliasValues = string.Empty;
            try
            {
                AliasValues = MysqlObj.ExecuteScalar("select cm_email from portaldb.companymaster where cm_id ='" + cm_ID + "' ");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return AliasValues;
        }

        /// <summary>
        ///  Get the CompanyName through cm_id 
        /// </summary>
        /// <param name="cm_ID"></param>
        /// <returns></returns>
        public string Get_CompanyName(string cm_ID)
        {
            string AliasValues = string.Empty;
            try
            {
                AliasValues = MysqlObj.ExecuteScalar("Select cm_name from portaldb.companymaster where cm_id ='" + cm_ID + "' ");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return AliasValues;
        }

        /// <summary>
        ///  Get the Max Branchid through the HQ_DBName.Branch
        /// </summary>
        /// <param name="HQ_DbName"></param>
        /// <returns></returns>
        public string Get_BranchIdOnHQ(string HQ_DbName)
        {
            string Branch_MaxId = string.Empty;
            try
            {
                Branch_MaxId = MysqlObj.ExecuteScalar("Select max((branchid)+1) as Maxid from " + HQ_DbName + ".branches");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return Branch_MaxId;
        }

        public string Get_IISAppName(string cm_id, string Branchid)
        {
            string IISAppName = string.Empty;
            try
            {
                IISAppName = MysqlObj.ExecuteScalar("select IISAppName from Portaldb.companydetails  where cm_id=" + cm_id + " and cd_branchid=" + Branchid + "");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return IISAppName;
        }

        /**************************************** Report Section  ********************************************/


        /// <summary>
        ///  This function Return "Payment Type" & "Count" & " Amount " 
        ///  It returns data between two dates and Particular Branch 
        /// </summary>
        /// <param name="BranchAliasNam"></param>
        /// <param name="D1"></param>
        /// <param name="D2"></param>
        /// <returns></returns>
        public DataTable Retail_PaymentDetails(string BranchAliasNam, string D1, string D2)
        {

            try
            {
                Dt = MysqlObj.GetData_DT("Select PaymentType,count(Paymenttype) as count,ifnull(sum(paymentamount),0) as amount From " + BranchAliasNam + ".trans t    join " + BranchAliasNam + ".transpayment tp on t.transid = tp.transid where tp.businessdate between '" + D1 + "' and '" + D2 + "' group by PaymentType");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return Dt;
        }

        public string Retail_TotalSales(string BranchAliasNam, string D1, string D2)
        {

            try
            {
                TotSales = MysqlObj.ExecuteScalar("Select sum(TP.PaymentAmount) as salesamount From " + BranchAliasNam + ".trans t join rasa1.transpayment tp on t.transid = tp.transid where tp.businessdate between '" + D1 + "' and '" + D2 + "';");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return TotSales;
        }

        public DataTable DtList()
        {

            try
            {
                Dt = MysqlObj.GetData_DT("Select  * from portaldb.companymaster ");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return Dt;
        }

        public string GETBrachAlias(string cmid)
        {
            string Branch_MaxId = string.Empty;
            try
            {
                Branch_MaxId = MysqlObj.ExecuteScalar("select cd_branchalias from portaldb.companydetails  where  cm_id=" + cmid + "  and cd_branchid=2;");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return Branch_MaxId;
        }

        /// <summary>
        ///  Get the Fist Branch Based on the condition with Subcompany Rights 
        /// </summary>
        /// <param name="cmid"></param>
        /// <returns></returns>
        public string GETBrachAlias8(string cmid, string HQ, string Userid)
        {
            string Branch_MaxId = string.Empty;
            try
            {
                Branch_MaxId = MysqlObj.ExecuteScalar("SELECT  BranchName FROM " + HQ + ".branches  as b inner join  portaldb.Companydetails as C on C.cd_branchid=b.Branchid   inner join portaldb.subcompmaster as S  on b.Branchid=S.cd_Branchid  where S.userid=" + Userid + " and Multi_Access<>'N' and c.cm_id=" + cmid + " AND C.Status='A' order by b.Branchid  limit 1  ;");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return Branch_MaxId;
        }

        public string GETcmid(string cdid)
        {
            string Branch_MaxId = string.Empty;
            try
            {
                Branch_MaxId = MysqlObj.ExecuteScalar("select cm_id from portaldb.companydetails where cd_id=" + cdid + ";");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return Branch_MaxId;
        }

        /**************************************** Inventory Read Methods  Section  ********************************************/
        /// <summary>
        ///  This Function Uses to Load the Inventory CategoryList
        /// </summary>
        /// <param name="BranchAlias"></param>
        /// <returns></returns>
        public DataTable Inv_CategoryList(string BranchAlias)
        {

            try
            {
                Dt = CloudSqlObj.GetData_DT("select CategoryID,Description from " + BranchAlias + ".inventorycategories order by CategoryID ,Description ");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error (ex.Message);
            }
            return Dt;
        }

        public DataTable paymenttypes(string db)
        {
            try
            {
                Dt = CloudSqlObj.GetData_DT("select paymenttype from " + db + ".transpayment group by paymenttype");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return Dt;
        }
        /// <summary>
        ///   Load the Units From db
        /// </summary>
        /// <param name="BranchAlias"></param>
        /// <returns></returns>
        public DataTable Inv_Units(string BranchAlias)
        {

            try
            {
                Dt = CloudSqlObj.GetData_DT("select UnitID,Unit from " + BranchAlias + ".inventoryunits order by Unitid;");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error (ex.Message);
            }
            return Dt;
        }
        /// <summary>
        ///  Read the CategoryId using the CategoryName
        /// </summary>
        /// <param name="CategoryName"></param>
        /// <returns></returns>
        public string Get_Inv_CategoryID(string BranchAlias, string CategoryName)
        {
            string Inv_CategoryId = string.Empty;
            try
            {
                Inv_CategoryId = CloudSqlObj.ExecuteScalar("select CategoryID from " + BranchAlias + ".inventorycategories where CategoryName='" + CategoryName + "';");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);  
            }
            return Inv_CategoryId;
        }
        /// <summary>
        ///  Get InventoryID  Through InventoryName In Inventory
        /// </summary>
        /// <param name="BranchAlias"></param>
        /// <param name="Inventorynam"></param>
        /// <returns></returns>
        public string GetInvenID(string BranchAlias, string Inventorynam)
        {
            string Inv_CategoryId = string.Empty;
            try
            {
                Inv_CategoryId = CloudSqlObj.ExecuteScalar("select InventoryID from " + BranchAlias + ".inventory where inventoryname='" + Inventorynam + "';");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return Inv_CategoryId;
        }
        /// <summary>
        ///  Read the RunningNo 
        /// </summary>
        /// <param name="BranchAlias"></param>
        /// <returns></returns>
        public string GetMax_RunningNo(string BranchAlias)
        {
            string Inv_RunningNo = string.Empty;
            try
            {
                Inv_RunningNo = CloudSqlObj.ExecuteScalar("SELECT (max(RunningNo)+1) as RunningNo FROM " + BranchAlias + ".runningnumber  where ModuleName='Inventory'");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return Inv_RunningNo;
        }
        //Nid added for getting Running number for Package module on 15-Mar-2023
        public string GetMax_RunningNoInvPackage(string BranchAlias)
        {
            string InvP_RunningNo = string.Empty ;
            try
            {
                InvP_RunningNo = CloudSqlObj.ExecuteScalar("SELECT (max(RunningNo)+1) as RunningNo FROM " + BranchAlias + ".runningnumber  where ModuleName='InventoryPackage'");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return InvP_RunningNo;
        }

        public string GetMax_CatID(string BranchAlias)
        {
            string Cat_Val = string.Empty;
            try
            {
                Cat_Val = CloudSqlObj.ExecuteScalar("SELECT (max(CategoryID)+1) as CatID FROM " + BranchAlias + ".inventorycategories");

                if (Cat_Val == "")
                {
                    Cat_Val = "1";
                }
                else
                {

                }

            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return Cat_Val;
        }

        public string GetMax_GrpID1(string BranchAlias)
        {
            string Grp1_Val = string.Empty;
            try
            {
                Grp1_Val = CloudSqlObj.ExecuteScalar("SELECT (max(GroupID)+1) as GroupID FROM " + BranchAlias + ".group1");

                if (Grp1_Val == "")
                {
                    Grp1_Val = "1";
                }
                else
                {

                }

            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return Grp1_Val;
        }

        public string GetMax_CommonGroupid(string BranchAlias, string GroupName)
        {
            string Grp1_Val = string.Empty;
            try
            {
                Grp1_Val = CloudSqlObj.ExecuteScalar("SELECT (max(GroupID)+1) as GroupID FROM " + BranchAlias + "." + GroupName + "");

                if (Grp1_Val == "")
                {
                    Grp1_Val = "1";
                }
                else
                {

                }

            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return Grp1_Val;
        }

        public string GetMax_GrpID2(string BranchAlias)
        {
            string Grp2_Val = string.Empty;
            try
            {
                Grp2_Val = CloudSqlObj.ExecuteScalar("SELECT (max(GroupID)+1) as GroupID FROM " + BranchAlias + ".group2 ");

                if (Grp2_Val == "")
                {
                    Grp2_Val = "1";
                }
                else
                {

                }

            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return Grp2_Val;
        }

        public string GetMax_GrpID3(string BranchAlias)
        {
            string Grp3_Val = string.Empty;
            try
            {
                Grp3_Val = CloudSqlObj.ExecuteScalar("SELECT (max(GroupID)+1) as GroupID FROM " + BranchAlias + ".group3");

                if (Grp3_Val == "")
                {
                    Grp3_Val = "1";
                }
                else
                {

                }

            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return Grp3_Val;
        }

        public string GetMax_GrpID4(string BranchAlias)
        {
            string Grp4_Val = string.Empty;
            try
            {
                Grp4_Val = CloudSqlObj.ExecuteScalar("SELECT (max(GroupID)+1) as GroupID FROM " + BranchAlias + ".group4");

                if (Grp4_Val == "")
                {
                    Grp4_Val = "1";
                }
                else
                {

                }

            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return Grp4_Val;
        }

        public string GetMax_GrpID5(string BranchAlias)
        {
            string Grp5_Val = string.Empty;
            try
            {
                Grp5_Val = CloudSqlObj.ExecuteScalar("SELECT (max(GroupID)+1) as GroupID FROM " + BranchAlias + ".group5");

                if (Grp5_Val == "")
                {
                    Grp5_Val = "1";
                }
                else
                {

                }

            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return Grp5_Val;
        }

        /// <summary>
        ///   This Function  for InvenAdjId 
        /// </summary>
        /// <param name="BranchAlias"></param>
        /// <returns></returns>
        public string GetMax_InvAdjID(string BranchAlias)
        {
            string InvenAdjID = string.Empty;
            try
            {
                InvenAdjID = CloudSqlObj.ExecuteScalar("Select Max((InvAdjID )+ 1) as MaxInvAdjID from " + BranchAlias + ".InvAdjMaster;");

                if (InvenAdjID == "")
                {
                    InvenAdjID = "1";
                }
                else
                {

                }

            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return InvenAdjID;
        }

        public string Get_HQ(string cmid)
        {
            string HQ_Value = string.Empty;
            try
            {
                HQ_Value = CloudSqlObj.ExecuteScalar("SELECT cd_branchalias FROM portaldb.companydetails  where cm_id=" + cmid + " and cd_hq=1 ;");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);

            }
            return HQ_Value;
        }

        public string GetBranchFullName(string BanchHQ, string BranchName)
        {
            string BranchFullnam = string.Empty;
            try
            {
                BranchFullnam = MysqlObj.ExecuteScalar("select Branchfullname from " + BanchHQ + ".branches where branchname='" + BranchName + "';");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return BranchFullnam;
        }

        public DataTable Inv_Grp1List(string BranchAlias)
        {

            try
            {
                Dt = CloudSqlObj.GetData_DT("SELECT Groupid, GroupName as GroupName1 FROM " + BranchAlias + ".group1;");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return Dt;
        }

        public DataTable Inv_Grp2List(string BranchAlias)
        {

            try
            {
                Dt = CloudSqlObj.GetData_DT("SELECT Groupid,GroupName as GroupName2 FROM " + BranchAlias + ".group2;");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error (ex.Message);
            }
            return Dt;
        }

        public DataTable Inv_Grp3List(string BranchAlias)
        {

            try
            {
                Dt = CloudSqlObj.GetData_DT("SELECT Groupid, GroupName as GroupName3 FROM " + BranchAlias + ".group3;");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
               Log.Error(ex.Message);
            }
            return Dt;
        }

        public DataTable Inv_Grp4List(string BranchAlias)
        {

            try
            {
                Dt = CloudSqlObj.GetData_DT("SELECT Groupid,GroupName as GroupName4 FROM " + BranchAlias + ".group4;");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return Dt;
        }

        public DataTable Inv_Grp5List(string BranchAlias)
        {

            try
            {
                Dt = CloudSqlObj.GetData_DT("SELECT Groupid, GroupName as GroupName5 FROM " + BranchAlias + ".group5;");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return Dt;
        }

        /// <summary>
        ///  This Function User to Select the Dashboard Branch Details Based on the CompanyStatus 
        /// </summary>
        /// <param name="BranchHQ"></param>
        /// <param name="Userid"></param>
        /// <param name="cm_id"></param>
        /// <returns></returns>
        /// 
        public DataTable GetDashboard(string BranchHQ, string Userid)
        {

            try
            {
                //Live
                Dt = CloudSqlObj.GetData_DT("select Branchid,BranchNames,DailySales,MonthlySale,DATE_format(UPDATEDTIME,'%d-%m-%Y %H:%i:%s')  AS UPDATEDTIME    from " + BranchHQ + ".dashboardcount  as b inner join portaldb.subcompmaster as S  on b.Branchid=S.cd_Branchid   where S.userid=" + Userid + " and Multi_Access<>'N' and b.branchid<>1 ;");
                // Test (Modified on 05-09-20) 

                //  Dt = CloudSqlObj.GetData_DT("select Branchid,BranchNames,DailySales,MonthlySale,DATE_format(UPDATEDTIME,'%d-%m-%Y %H:%i:%s')  AS UPDATEDTIME    from " + BranchHQ + ".dashboardcount  as b   inner join portaldb.companydetails AS C on b.Branchid=C.cd_Branchid    inner join portaldb.subcompmaster as S  on b.Branchid=S.cd_Branchid     where S.userid=" + Userid + " and Multi_Access<>'N' And C.status='A'   and c.cm_id=" + cm_id + "  and b.branchid<>1");



            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return Dt;
        }

        public DataTable GetDashboard8(string BranchHQ, string Userid, string Cm_id)
        {

            try
            {
                // Old Live
                // Dt = CloudSqlObj.GetData_DT("select Branchid,BranchNames,DailySales,MonthlySale,DATE_format(UPDATEDTIME,'%d-%m-%Y %H:%i:%s')  AS UPDATEDTIME    from " + BranchHQ + ".dashboardcount  as b inner join portaldb.subcompmaster as S  on b.Branchid=S.cd_Branchid   where S.userid=" + Userid + " and Multi_Access<>'N' and b.branchid<>1 ;");
                // Current Live (Modified on 012-09-20) 

                Dt = CloudSqlObj.GetData_DT("select Branchid,BranchNames,DailySales,MonthlySale,DATE_format(UPDATEDTIME,'%d-%m-%Y %H:%i:%s')  AS UPDATEDTIME    from " + BranchHQ + ".dashboardcount  as b   inner join portaldb.companydetails AS C on b.Branchid=C.cd_Branchid    inner join portaldb.subcompmaster as S  on b.Branchid=S.cd_Branchid     where S.userid=" + Userid + " and Multi_Access<>'N' And C.status='A'   and c.cm_id= " + Cm_id + "  and b.branchid<>1");



            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return Dt;
        }

        //++++++++++++++++++++++++++++++TRANSFER,RECEIVE AND RETURN+++++++++++++++++++++++++++++++++++++++++
        //Get Branch ID by Name 
        public string GetBranchIDbyName(string BranchAlias, string BranchName)
        {
            string BranchID = string.Empty;
            try
            {
                BranchID = CloudSqlObj.ExecuteScalar("Select branchid from " + BranchAlias + ".Branches where Branchfullname = '" + BranchName + "'");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return BranchID;
        }
        //Get TransferID by TransNo
        public string GetTransferIDbyTransferNo(string BranchAlias, string TransferNo)
        {
            string sTransferNo = string.Empty;
            try
            {
                sTransferNo = CloudSqlObj.ExecuteScalar("Select TransferID from " + BranchAlias + ".transferstock where transferNo = '" + TransferNo + "'");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return sTransferNo;
        }
        //Generate TransferID
        public string GenerateTransferID(string BranchAlias)
        {
            string sTransferID = string.Empty;
            int iNewTransID = 0;
            try
            {
                sTransferID = CloudSqlObj.ExecuteScalar("Select transferid from " + BranchAlias + ".transferstock order by transferid desc limit 1");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            if (sTransferID != "")
            {
                iNewTransID = Convert.ToInt16(sTransferID) + 1;
                return iNewTransID.ToString();
            }
            else
            {
                return "1";
            }


        }
        //Generate Confirm Number
        public string GenerateConfirmNo(string BranchAlias)
        {

            string sConfirmID = string.Empty;
            int iNewConfirmID = 0;
            try
            {
                sConfirmID = CloudSqlObj.ExecuteScalar("Select ConfirmNo from " + BranchAlias + ".transferstock where confirmflag = 1 order by transferid desc limit 1");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            if (sConfirmID != "")
            {
                string[] words = sConfirmID.Split('-');
                iNewConfirmID = Convert.ToInt16(words[1]) + 1;

                return String.Format("{0:D7}", iNewConfirmID);
                //return iNewConfirmID.ToString();

            }
            else
            {
                return "0000001";
            }
        }
        //Get From Branch Name
        public string GetFromBranch(string sBranchAlias, string iTransferID)
        {
            string sBranchName = string.Empty;
            try
            {
                sBranchName = CloudSqlObj.ExecuteScalar("Select B.BranchName From " + sBranchAlias + ".transferstock A " +
                                " join " + sBranchAlias + ".Branches B on A.FromBranchID=B.BranchID " +
                                " where TransferID = '" + iTransferID + "';");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return sBranchName;

        }
        //Get To Branch Name
        public string GetToBranch(string sBranchAlias, string iTransferID)
        {
            string sBranchName = string.Empty;
            try
            {
                sBranchName = CloudSqlObj.ExecuteScalar("Select B.BranchName From " + sBranchAlias + ".transferstock A " +
                                " join " + sBranchAlias + ".Branches B on A.ToBranchID=B.BranchID " +
                                " where TransferID = '" + iTransferID + "';");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return sBranchName;

        }
        //Check if Item is already inside the tmpTransferDetails
        public string CheckItem(string sBranchAlias, string sBarcode)
        {
            string CheckItem = string.Empty;
            try
            {
                CheckItem = CloudSqlObj.ExecuteScalar("Select *from " + sBranchAlias + ".tmptransferstockdetail where barcode = '" + sBarcode + "';");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            if (CheckItem != "")
            {
                return "True";

            }
            else
            {
                return "False";
            }

        }
        //Get Receive ID
        public string GetReceiveIDbyTransferNo(string BranchAlias, string sTransferNo)
        {
            string sReceivedID = string.Empty;
            try
            {
                sReceivedID = CloudSqlObj.ExecuteScalar("Select ReceiveID from " + BranchAlias + ".receivestock where TransferNo = '" + sTransferNo + "'");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);  
            }
            return sReceivedID;
        }

        public DataTable ReadCategoryByNo(string BranchAlias, string CategoryNum)
        {

            try
            {
                Dt = CloudSqlObj.GetData_DT("SELECT CategoryID, CategoryNumber ,CategoryName , Description, Active,Updated,BranchUpdated,Flag1,Flag2,FireIndex  FROM  " + BranchAlias + ". inventorycategories  where  CategoryID=" + CategoryNum + "  ");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return Dt;
        }

        public DataTable GetCategoryList(string BranchAlias)
        {

            try
            {
                Dt = CloudSqlObj.GetData_DT("SELECT CategoryID, CategoryNumber,CategoryName , Description, Active,Updated,BranchUpdated,Flag1,Flag2,FireIndex  FROM  " + BranchAlias + ". inventorycategories");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return Dt;
        }

        public string GetLastTransferNo(string BranchAlias)
        {
            string sGetLastTransferNo = string.Empty;
            try
            {
                sGetLastTransferNo = CloudSqlObj.ExecuteScalar("Select max(transferno) as transferno from " + BranchAlias + ".transferstock limit 1");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return sGetLastTransferNo;
        }

        public string GenerateReturnID(string BranchAlias)
        {
            string sReturnID = string.Empty;
            int iNewReturnID = 0;
            try
            {
                sReturnID = CloudSqlObj.ExecuteScalar("Select ReturnID from " + BranchAlias + ".returnstock order by ReturnID desc limit 1");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            if (sReturnID != "")
            {
                iNewReturnID = Convert.ToInt16(sReturnID) + 1;
                return iNewReturnID.ToString();
            }
            else
            {
                return "1";
            }


        }

        //nid added for dispaying inventory information on 14-May-2019
        public DataTable GetInvInfo(string BranchAlias, string Barcode)
        {
            DataTable Dt= new DataTable();
            try
            {
                // Modified on 19-07-21 : For promo 
                //  Dt = CloudSqlObj.GetData_DT("SELECT InventoryID,BarCode,InventoryName,Description,CategoryID,Class,Unit,Round(Price,2) as Price,Round(Price2,2) as Price2,Round(Price3,2) as Price3,Round(Price4,2) as Price4,DateCreated,Group1,Group2,Group3,Group4,Group5,NoPriceShift,Active,Updated,Notes,BranchUpdated,StockCode,Flag1,Flag2,ReorderLevel,MaxiumLevel,SafetyLevel,TakeAwayFlag,OutofStock,showitem,webpricecaptionid  FROM  " + BranchAlias + ". inventory  where  Barcode='" + Barcode + "'  ");
                Dt = CloudSqlObj.GetData_DT("SELECT InventoryID,BarCode,InventoryName,Description,CategoryID,Class,Unit,Round(Price,2) as Price,Round(Price2,2) as Price2,Round(Price3,2) as Price3,Round(Price4,2) as Price4,DateCreated,Group1,Group2,Group3,Group4,Group5,NoPriceShift,Active,Updated,Notes,BranchUpdated,StockCode,Flag1,Flag2,ReorderLevel,MaxiumLevel,SafetyLevel,TakeAwayFlag,OutofStock,showitem,Promo,webpricecaptionid,TAId,productrelationmain,productrelationnotinclude ,WebstockCount FROM  " + BranchAlias + ". inventory  where  Barcode='" + Barcode + "'  ");
            }
            catch (Exception ex)
            {
               

                Log.Error(ex.Message);
            }
            return Dt;
        }

        public DataTable GetCustMasterDetails(string BranchAlias, string CustID)
        {
            DataTable Dt = new DataTable();
            try
            {
                Dt = CloudSqlObj.GetData_DT("SELECT CustomerID,CustomerNumber,ID, CustomerName,DATE_FORMAT(OpenDate, '%Y/%m/%d')  as OpenDate, DATE_FORMAT(LastDate, '%Y/%m/%d')  as LastDate ,DATE_FORMAT(Birthday, '%Y/%m/%d')  as Birthday,DATE_FORMAT(ExpiryDate, '%Y/%m/%d') as ExpiryDate ,Mobile,EmailAddress    FROM  " + BranchAlias + ". customers  where  CustomerID='" + CustID + "'  ");
            }
            catch (Exception ex)
            {
               
                Log.Error(ex.Message);  
            }
            return Dt;
        }

        //nid added for checking existing barcodes  on 15-May-2019 
        public DataTable CheckBarcode(string BranchAlias, string Barcode)
        {
            DataTable Dt = new DataTable();
            try
            {
                Dt = CloudSqlObj.GetData_DT("SELECT Barcode  FROM  " + BranchAlias + ". inventory  where  Barcode='" + Barcode + "'  ");
            }
            catch (Exception ex)
            {
                Log.Error(ex.Message);
            }
            return Dt;
        }

        //nid added for checking unit costs  on 21-Jun-2019 
        public DataTable CheckUnitCost(string BranchAlias, string InvID)
        {
            DataTable Dt = new DataTable();
            try
            {
                Dt = CloudSqlObj.GetData_DT("SELECT UnitCost  FROM  " + BranchAlias + ". inventorycostaverage  where  InventoryID='" + InvID + "'  ");
            }
            catch (Exception ex)
            {
                Log.Error(ex.Message);
            }
            return Dt;
        }

        public string GetBranchDBNamebyFullName(string sHQ, string sBranchName)
        {
            string BranchDBName = string.Empty;
            try
            {
                BranchDBName = MysqlObj.ExecuteScalar("SELECT  BranchName FROM " + sHQ + ".branches where BranchFullName = '" + sBranchName + "'");
            }
            catch (Exception ex)
            {
                Log.Error(ex.Message);
            }
            return BranchDBName;
        }

        public string GetBranchFullNameByDB(string sBranchName)
        {
            string BranchFullName = string.Empty;
            try
            {
                BranchFullName = MysqlObj.ExecuteScalar("SELECT  BranchFullName FROM " + sBranchName + ".branches where BranchName = '" + sBranchName + "'");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return BranchFullName;
        }

        public string CheckedTransferNoifExist(string sBranchName, string sTransferNumber)
        {
            string sTransferNo = string.Empty;
            try
            {
                sTransferNo = MysqlObj.ExecuteScalar("Select *From " + sBranchName + ".transferstock where transferNo = '" + sTransferNumber + "'");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return sTransferNo;
        }

        /// <summary>
        ///  This Fuction Gets the Tax Setting Value by Branchwise
        /// </summary>
        /// <param name="BranchAlias"></param>
        /// <returns></returns>
        public DataTable ReadTaxMasterValue(string BranchAlias)
        {
            DataTable Dt = new DataTable();
            try
            {
                string query = "SELECT   TaxID,TaxName, Tax, AddOn,TaxOnTax,Updated,BranchUpdated   FROM {0}.Tax   ";
                string command = string.Format(query, BranchAlias);

                Dt = CloudSqlObj.GetData_DT(command);
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return Dt;
        }

        public DataTable TaxData_Load(string BranchAlias, string Taxid)
        {
            DataTable Dt = new DataTable();
            try
            {
                string query = "SELECT  TaxName  FROM  {0}.Tax  where Taxid={1} ;";
                string command = string.Format(query, BranchAlias, Taxid);
                Dt = CloudSqlObj.GetData_DT(command);
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return Dt;
        }

        // Group List Functions 
        public DataTable GetCommonList(string BranchAlias, string GroupName)
        {
            DataTable Dt = new DataTable();
            try
            {
                string query = "SELECT  GroupID,GroupName,Description   FROM  {0}.{1} ; ";
                string command = string.Format(query, BranchAlias, GroupName);

                Dt = CloudSqlObj.GetData_DT(command);
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return Dt;
        }


        public DataTable GetCommonListNew(string BranchAlias, string GroupName, string GroupNameSearchText)
        {
            DataTable Dt = new DataTable();
            try
            {
                string query = "SELECT  GroupID,GroupName,Description   FROM  {0}.{1} where GroupName like CONCAT('%',@GroupSearch,'%') ";
                string command = string.Format(query, BranchAlias, GroupName);

                MySqlParameter[] parameters =
                  {
                     new MySqlParameter("@GroupSearch",GroupNameSearchText.ToString().Trim()),

                    };

                Dt = objMain.NewGetData_DT(command, parameters);

            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return Dt;
        }

        public DataTable GetCommonListAll(string BranchAlias, string GroupName)
        {
            DataTable Dt = new DataTable();
            try
            {
                string query = "SELECT  GroupID,GroupName,Description   FROM  {0}.{1} ; ";
                string command = string.Format(query, BranchAlias, GroupName);

               

                Dt = objMain.GetData_DT(command);

            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return Dt;
        }



        public DataTable GetGroup1List(string BranchAlias)
        {
            DataTable Dt = new DataTable();
            try
            {
                Dt = CloudSqlObj.GetData_DT("SELECT  GroupID,GroupName,Description   FROM  " + BranchAlias + ".group1");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return Dt;
        }

        public DataTable GetGroup2List(string BranchAlias)
        {

            DataTable Dt = new DataTable();
            try
            {
                Dt = CloudSqlObj.GetData_DT("SELECT  GroupID,GroupName,Description   FROM  " + BranchAlias + ".group2");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return Dt;
        }

        public DataTable GetGroup3List(string BranchAlias)
        {
            DataTable Dt = new DataTable();
            try
            {
                Dt = CloudSqlObj.GetData_DT("SELECT  GroupID,GroupName,Description   FROM  " + BranchAlias + ".group3");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return Dt;
        }

        public DataTable ReadGrouPCommonByID(string BranchAlias, string GroupName, string Groupid)
        {

            try
            {
                Dt = CloudSqlObj.GetData_DT("SELECT GroupID,GroupName, Description FROM  " + BranchAlias + ". " + GroupName + "  where  GroupID=" + Groupid + "  ");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return Dt;
        }

        public DataTable ReadPromoByID(string BranchAlias, string MixMatchID)
        {

            try
            {
                Dt = CloudSqlObj.GetData_DT(" SELECT Name,TimeType, FromTime ,ToTime, PriceType ,PriceType, Mixed ,OddDisc ,QuantityShiftType, Quantity1, DiscValue1 , "
                                          + "  Quantity2, DiscValue2, Quantity3, DiscValue3 ,Quantity4 ,DiscValue4 ,Quantity5 ,DiscValue5 ,Description ,Active ,NoDiscQty "
                                          + "  FROM  " + BranchAlias + ".inventorypromix  where  MixMatchID=" + MixMatchID + "  ");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return Dt;
        }

        public DataTable ReadGroup1ByID(string BranchAlias, string Groupid)
        {
            try
            {
                Dt = CloudSqlObj.GetData_DT("SELECT GroupID,GroupName, Description FROM  " + BranchAlias + ". group1  where  GroupID=" + Groupid + "  ");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return Dt;
        }

        public DataTable ReadGroup2ByID(string BranchAlias, string Groupid)
        {

            try
            {
                Dt = CloudSqlObj.GetData_DT("SELECT GroupID,GroupName, Description FROM  " + BranchAlias + ". group2  where  GroupID=" + Groupid + "  ");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);

                Log.Error(ex.Message);

            }
            return Dt;
        }

        public DataTable ReadGroup3ByID(string BranchAlias, string Groupid)
        {

            try
            {
                Dt = CloudSqlObj.GetData_DT("SELECT GroupID,GroupName, Description FROM  " + BranchAlias + ". group3  where  GroupID=" + Groupid + "  ");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return Dt;
        }

        public DataTable GetGroup4List(string BranchAlias)
        {
            DataTable Dt=new DataTable();
            try
            {
                Dt = CloudSqlObj.GetData_DT("SELECT  GroupID,GroupName,Description   FROM  " + BranchAlias + ".group4");
            }
            catch (Exception ex)
            {
                Log.Error(ex.Message);

            }
            return Dt;
        }

        public DataTable GetGroup5List(string BranchAlias)
        {
            DataTable Dt = new DataTable();
            try
            {
                Dt = CloudSqlObj.GetData_DT("SELECT  GroupID,GroupName,Description   FROM  " + BranchAlias + ".group5");
            }
            catch (Exception ex)
            {
                Log.Error(ex.Message);
            }
            return Dt;
        }

        public DataTable ReadGroup4ByID(string BranchAlias, string Groupid)
        {
            DataTable Dt = new DataTable();
            try
            {
                Dt = CloudSqlObj.GetData_DT("SELECT GroupID,GroupName, Description FROM  " + BranchAlias + ". group4  where  GroupID=" + Groupid + "  ");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return Dt;
        }

        public DataTable ReadGroup5ByID(string BranchAlias, string Groupid)
        {
            DataTable Dt = new DataTable();
            try
            {
                Dt = CloudSqlObj.GetData_DT("SELECT GroupID,GroupName, Description FROM  " + BranchAlias + ". group5  where  GroupID=" + Groupid + "  ");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return Dt;
        }

        public string GetFirstBranch7(string HQDB, string Userid)
        {
            string sGetFirstBranch = string.Empty;
            try
            {
                //sGetFirstBranch = CloudSqlObj.ExecuteScalar("Select Branchname from "+ HQDB  +".branches where branchid = 2;"); Modified on 07-02-20- Karthik
                sGetFirstBranch = CloudSqlObj.ExecuteScalar("SELECT b.BranchName FROM " + HQDB + ".branches  as b inner join portaldb.subcompmaster as S  on b.Branchid=S.cd_Branchid     where S.userid=" + Userid + " and Multi_Access<>'N' ORDER BY b.BranchName asc;");


            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return sGetFirstBranch;
        }



        public string GetDefault_Brachid(string cm_id)
        {
            string sGetFirstBranch = string.Empty;
            try
            {
                //sGetFirstBranch = CloudSqlObj.ExecuteScalar("Select Branchname from "+ HQDB  +".branches where branchid = 2;"); Modified on 07-02-20- Karthik
                sGetFirstBranch = CloudSqlObj.ExecuteScalar("select DefaultBranch from portaldb.companymaster  where cm_id="+ cm_id + " ;");


            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return sGetFirstBranch;
        }





        /// <summary>
        /// This Function returns first Branch only 'A' - Active  status in Companydetails ..
        /// </summary>
        /// <param name="HQDB"></param>
        /// <param name="Userid"></param>
        /// <param name="cm_id"></param>
        /// <returns></returns>
        public string GetFirstBranch8(string HQDB, string Userid, string cm_id)
        {
            string sGetFirstBranch = string.Empty;
            try
            {
                //sGetFirstBranch = CloudSqlObj.ExecuteScalar("Select Branchname from "+ HQDB  +".branches where branchid = 2;"); Modified on 07-02-20- Karthik
                sGetFirstBranch = CloudSqlObj.ExecuteScalar("SELECT b.BranchName,b.branchid FROM " + HQDB + ".branches  as b inner join  portaldb.Companydetails as C on C.cd_branchid=b.Branchid  inner join portaldb.subcompmaster as S  on b.Branchid=S.cd_Branchid     where S.userid=" + Userid + " and C.cm_id=" + cm_id + " and Multi_Access<>'N' AND c.status='A'   ORDER BY b.Branchid asc;");


            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return sGetFirstBranch;
        }

        public string GetFirstBranch(string HQDB)
        {
            string sGetFirstBranch = string.Empty;
            try
            {
                sGetFirstBranch = CloudSqlObj.ExecuteScalar("Select Branchname from " + HQDB + ".branches where branchid = 2;");



            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return sGetFirstBranch;
        }

        public string GetFirstBranch_Light(string HQDB)
        {
            string sGetFirstBranch = string.Empty;
            try
            {
                sGetFirstBranch = CloudSqlObj.ExecuteScalar("Select Branchname from " + HQDB + ".branches AS B INNER join  portaldb.companydetails as C  ON B.branchid=C.cd_branchid where   C.IslightReport='Y'  order by  B.branchid  Limit 1 ;");



            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return sGetFirstBranch;
        }


        public string GetBranch_Branchname_Light(string HQDB,string branchname)
        {
            string sGetBranch = string.Empty;
            try
            {
                sGetBranch = CloudSqlObj.ExecuteScalar("Select Branchname from "+HQDB +".branches AS B INNER join  portaldb.companydetails as C  ON B.branchid=C.cd_branchid where   C.IslightReport='Y'  and C.cd_branchname='"+branchname+"';");



            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return sGetBranch;
        }




        public string GetHQ(string HQDB)
        {
            string sGetHQ = string.Empty;
            try
            {
                sGetHQ = CloudSqlObj.ExecuteScalar("Select Branchname from " + HQDB + ".branches where branchid = 1;");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return sGetHQ;
        }

        public string GetBranchPrefix(string HQDB, string sBranchFullName)
        {
            string sGetBranchPrefix = string.Empty;
            try
            {
                sGetBranchPrefix = CloudSqlObj.ExecuteScalar("Select BranchPrefix from " + HQDB + ".branches where branchfullname = '" + sBranchFullName + "';");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return sGetBranchPrefix;
        }

        //***********************************************  STOCK COLLECTOR RELATED FUNCTIONS  **************************************************************************************************

        /// <summary>
        ///  Stc max Id Value : Gnerated Function 
        /// </summary>
        /// <param name="sBranchName"></param>
        /// <returns></returns>
        public string GetMaxSTCID(string sBranchName)
        {
            string STCID = string.Empty;
            try
            {
                STCID = MysqlObj.ExecuteScalar("select max((stockcheckid)+1) as MAXID  FROM " + sBranchName + ".stockcheckmaster ;");

                if (STCID == "")
                {
                    STCID = "1";
                }

            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return STCID;
        }

        public string GetBranchPrefix(string sBranchName)
        {
            string BranchPrefix = string.Empty;
            try
            {
                BranchPrefix = MysqlObj.ExecuteScalar("select BranchPrefix from " + sBranchName + ".branches WHERE branchname='" + sBranchName + "' ;");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return BranchPrefix;
        }

        //John
        public string STCCheckID(string sDB, string sSTCID)
        {
            string sSTCCheckID = string.Empty;
            try
            {
                sSTCCheckID = CloudSqlObj.ExecuteScalar("Select *from " + sDB + ".StockCheckmaster where Stockcheckid= '" + sSTCID + "' ");
                if (sSTCCheckID != "")
                {
                    sSTCCheckID = "True";
                }
                else
                {
                    sSTCCheckID = "False";
                }
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return sSTCCheckID;

        }
        //John
        public string STCCheckInventoryIDExistBySTCID(string sDB, int iSTCID, int iInventoryID)
        {
            string sSTCCheckInventoryIDExistBySTCID = string.Empty;
            try
            {
                sSTCCheckInventoryIDExistBySTCID = CloudSqlObj.ExecuteScalar("Select *from " + sDB + ".StockCheckDetail where stockcheckid = '" + iSTCID + "' and inventoryid = " + iInventoryID + "");
                if (sSTCCheckInventoryIDExistBySTCID != "")
                {
                    sSTCCheckInventoryIDExistBySTCID = "True";
                }
                else
                {
                    sSTCCheckInventoryIDExistBySTCID = "False";
                }
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return sSTCCheckInventoryIDExistBySTCID;
        }

        public string GetTotalRowCountbySTCID(string sDB, int iSTCID)  //John added on 24-Dec-2019 PM
        {
            string sGetTotalRowCountbySTCID = string.Empty;
            try
            {
                sGetTotalRowCountbySTCID = MysqlObj.ExecuteScalar("Select count(*) from " + sDB + ".stockcheckdetail where stockcheckid = " + iSTCID + "");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return sGetTotalRowCountbySTCID;
        }

        public string GetStockCheckStatus(string sDB, int iSTCID)
        {
            string sGetStockCheckStatus = string.Empty;
            try
            {
                sGetStockCheckStatus = MysqlObj.ExecuteScalar("Select confirmed from " + sDB + ".stockcheckmaster where stockcheckid = " + iSTCID + "");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return sGetStockCheckStatus;
        }

        public string GetReturnIDbyReturnNo(string sDB, string ReturnNo)
        {
            string sGetReturnIDbyReturnNo = string.Empty;
            try
            {
                sGetReturnIDbyReturnNo = CloudSqlObj.ExecuteScalar("Select returnid from " + sDB + ".returnstock where returnno = '" + ReturnNo + "'");
            }
            catch(Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return sGetReturnIDbyReturnNo;
        }

        public string GetReturnConfirmIDbyRetrunConfirmNo(string sDBName, string sConfirmNo)
        {
            string GetReturnConfirmIDbyRetrunConfirmNo = string.Empty;
            try
            {
                GetReturnConfirmIDbyRetrunConfirmNo = CloudSqlObj.ExecuteScalar("Select ReceiveID from " + sDBName + ".receivestock where ConfirmNo = '" + sConfirmNo + "';");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return GetReturnConfirmIDbyRetrunConfirmNo;
        }

        public string GenerateReturnNo(string sDB)
        {
            string sGenerateReturnNo = string.Empty;
            string[] RetrunNo;
            int NewReturn;
            try
            {
                sGenerateReturnNo = CloudSqlObj.ExecuteScalar("Select ReturnNo from " + sDB + ".ReturnStock order by returnid desc limit 1;");

                if (sGenerateReturnNo != "")
                {
                    RetrunNo = sGenerateReturnNo.Split('-');
                    NewReturn = int.Parse(RetrunNo[1]) + 1;
                    //NewReturn = int.Parse(sGenerateReturnNo) + 1;
                    sGenerateReturnNo = String.Format("{0:D7}", NewReturn);
                }
                else
                {
                    sGenerateReturnNo = "0000001";

                }
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }

            return sGenerateReturnNo;

        }


        // **************************************************  CRM- CUSTOMER MASTER *************************************************************************************************


        public string GetMaxCUSTID(string sBranchName)
        {
            string CUSTID = string.Empty;
            try
            {
                CUSTID = MysqlObj.ExecuteScalar("select max((CustomerID  )+1) as CUSTID  FROM " + sBranchName + ".customers ;");

                if (CUSTID == "")
                {
                    CUSTID = "1";
                }

            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return CUSTID;
        }

        public string GenerateCondimentID(string sDB)
        {
            string sGenerateCondimentID = string.Empty;
            try
            {
                sGenerateCondimentID = MysqlObj.ExecuteScalar("Select max(condimentid) as CondimentID from " + sDB + ".condiment");

                if (sGenerateCondimentID == "")
                {
                    sGenerateCondimentID = "1";
                }
                else
                {

                    sGenerateCondimentID = (int.Parse(sGenerateCondimentID) + 1).ToString();
                }
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return sGenerateCondimentID;
        }

        public string GenerateCondimentGroupID(string sDB)
        {
            string sGenerateCondimentGroupID = string.Empty;
            try
            {
                sGenerateCondimentGroupID = MysqlObj.ExecuteScalar("Select max(condimentgroupid) as CondimentgroupID from " + sDB + ".condimentgroup");

                if (sGenerateCondimentGroupID == "")
                {
                    sGenerateCondimentGroupID = "1";
                }
                else
                {

                    sGenerateCondimentGroupID = (int.Parse(sGenerateCondimentGroupID) + 1).ToString();
                }
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return sGenerateCondimentGroupID;
        }

        public string GenerateModifierID(string sDB)
        {
            string sGenerateModifierID = string.Empty;
            try
            {
                sGenerateModifierID = MysqlObj.ExecuteScalar("Select max(ControlID) as ControlID from " + sDB + ".ModifierControl");
                if (sGenerateModifierID == "")
                {
                    sGenerateModifierID = "1";
                }
                else
                {
                    sGenerateModifierID = (int.Parse(sGenerateModifierID) + 1).ToString();
                }
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return sGenerateModifierID;
        }

        public string GetPriceCaption(string sDB, int iPriceType)
        {
            string sGetPriceCaption = string.Empty;
            try
            {
                sGetPriceCaption = MysqlObj.ExecuteScalar("Select PriceCaption from " + sDB + ".PriceCaption where ID = '" + iPriceType + "'");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return sGetPriceCaption;
        }


        //FOR PRICE CAPTION HEADER AND DETAIL//

        public string GetPriceCaptionHeaderID(string sDB) //GET PRICE CAPTION HEADER ID JOHN ADDED ON 2021-JAN-13
        {
            string sGetPriceCaptionHeaderID = string.Empty;
            try
            {
                sGetPriceCaptionHeaderID = MysqlObj.ExecuteScalar("Select ID from " + sDB + ".webpricecaption order by id desc limit 1;");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return sGetPriceCaptionHeaderID;
        }

        public string CheckPriceCaptionHeader(string sDB, string sPriceCaptionName) //CHECK IPRICE CAPTION HEADER NAME ALREADY EXIST JOHN ADDED ON 13-JAN-2021
        {
            string sCheckPriceCaptionHeader = string.Empty;
            try
            {
                sCheckPriceCaptionHeader = MysqlObj.ExecuteScalar("Select PriceCaptionName from " + sDB + ".WebPriceCaption where PriceCaptionName = '" + sPriceCaptionName + "'");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return sCheckPriceCaptionHeader;
        }

        public DataTable GetPriceCaptionDetail(string sDB, string ID) //GET PRICE CAPTION DETAIL BY PRICECAPTION HEADER ID JOHN ADDED ON 13-JAN-2021
        {
            try
            {
                Dt = MysqlObj.GetData_DT("Select PriceCaption from " + sDB + ".webpricecaptiondetail where pricecaptionid = '" + ID + "'; ");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return Dt;
        }

        public DataTable LoadPriceTypeHeader(string sDB) //LOAD PRICE CAPTION HEADER JOHN ADDED ON 13-JAN-2021
        {
            try
            {
                Dt = MysqlObj.GetData_DT("Select pricecaptionname from " + sDB + ".WebPriceCaption");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return Dt;
        }

        public string GetPriceCaptionHeaderIDByName(string sDB, string sPriceCaptionName) //GET WEBPRICECAPTIONID JOHN ADDED ON 13-JAN-2021
        {
            string sGetPriceCaptionHeaderID = string.Empty;
            try
            {
                sGetPriceCaptionHeaderID = MysqlObj.ExecuteScalar("Select ID from " + sDB + ".WebPriceCaption where PriceCaptionName = '" + sPriceCaptionName + "';");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return sGetPriceCaptionHeaderID;
        }

        public string GetPriceCaptionName(string sDB, string sWebPriceCaptionHeaderID, string sPriceID)
        {
            string sGetPriceCaption = string.Empty;
            try
            {
                sGetPriceCaption = MysqlObj.ExecuteScalar("Select PriceCaption from " + sDB + ".webpricecaptiondetail where priceCaptionID = '" + sWebPriceCaptionHeaderID + "' and PriceID = '" + sPriceID + "'");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return sGetPriceCaption;
        }

        public DataTable GetCondimentGroup(string DB)
        {
            try
            {
                //DT = MysqlObj.GetData_DT("SELECT  Branchid ,BranchName,b.datapath as Datapath FROM " + BranchHQ + ".branches  as b inner join portaldb.subcompmaster as S  on b.Branchid=S.cd_Branchid  where S.userid=" + UserId + " and Multi_Access<>'N'");
                DT = MysqlObj.GetData_DT("SELECT CondimentGroupID,CondimentGroupname from " + DB + ".CondimentGroup order by CondimentGroupName");

            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return DT;
        }

        public string GetCondimetGroupIDbyName(string DB, string CondimentGroupName)
        {
            string sGetCondimetGroupIDbyName = string.Empty;
            try
            {
                sGetCondimetGroupIDbyName = MysqlObj.ExecuteScalar("Select CondimentGroupID from " + DB + ".CondimentGroup where CondimentGroupName = '" + CondimentGroupName + "'");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return sGetCondimetGroupIDbyName;
        }

        public string GetIniSettingValue(string DB, string IniID)
        {
            string sGetIniSettingValue = string.Empty;
            try
            {
                sGetIniSettingValue = MysqlObj.ExecuteScalar("Select IniValue from " + DB + ".weborderinginisetting where ID = '" + IniID + "'");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return sGetIniSettingValue;
        }

        public string GetLastWebOrderMainPosition(string DB)
        {
            string sGetlastWebOrderMainPosition = string.Empty;
            try
            {
                sGetlastWebOrderMainPosition = MysqlObj.ExecuteScalar("Select max(MainPosition) as LastPostion from " + DB + ".WebOrderPromo");
                if (sGetlastWebOrderMainPosition == "")
                {
                    sGetlastWebOrderMainPosition = "0";
                }
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return sGetlastWebOrderMainPosition;
        }

        public string GetUserNamebyUserID(string DB, int UserID)
        {
            string sGetUserNamebyUserID = string.Empty;
            try
            {
                sGetUserNamebyUserID = MysqlObj.ExecuteScalar("Select Username from portaldb.usermaster where userid = " + UserID + ";");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return sGetUserNamebyUserID;
        }

        /**************************************** iCRM Module ********************************************/
        /// <summary>
        ///  This Function Uses to Load the Membership Types
        /// </summary>
        /// <param name="BranchAlias"></param>
        /// <returns></returns>
        public DataTable iCRM_MembershipTypes(string BranchAlias)
        {
            try
            {
                Dt = CloudSqlObj.GetData_DT("select MembershipID, MembershipName From " + BranchAlias + ".imembership");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return Dt;
        }
        
        /// <summary>
        ///  This Function Uses to Load the Members List
        /// </summary>
        /// <param name="BranchAlias"></param>
        /// <returns></returns>
        public DataTable GetMemberList(string BranchAlias)
        {

            try
            {
                Dt = CloudSqlObj.GetData_DT("SELECT CustomerID,UserName,CustomerName, Mobile   FROM  " + BranchAlias + ".icustomers");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return Dt;
        }


        public DataTable GetMemberInformation(string BranchAlias)
        {
            try
            {
                //Dt = CloudSqlObj.GetData_DT("SELECT CID,UserName as 'Mobile No.'   FROM  " + BranchAlias + ".icustomers where allowadvertisement = 1;");
                Dt = CloudSqlObj.GetData_DT("SELECT CID,UserName as 'Mobile No.'   FROM  " + BranchAlias + ".icustomers where allowadvertisement = 1 group by username;");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return Dt;
        }
        /// <summary>
        ///  This Function Uses to Load the Member Information for editing
        /// </summary>
        /// <param name="BranchAlias"></param>
        /// <returns></returns>
        public DataTable ReadMemberInformations(string BranchAlias, string CustomerID)
        {

            try
            {
                Dt = CloudSqlObj.GetData_DT("SELECT CustomerID, CID, CustomerName, Mobile, Email, Address, PostalCode, Date(DOB) as DOB, Date(RegistrationDate) as RegistrationDate, PDPA, AllowAdvertisement, Active, MembershipType, Notes, UserName, Password  FROM  " + BranchAlias + ". icustomers  Where  CustomerID=" + CustomerID + "  ");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return Dt;
        }

        public DataTable ReadiCrmCustomerPIN(string branchAlias,string customerId) 
        {
            try
            {
                Dt = CloudSqlObj.GetData_DT("SELECT CID,PIN from " + branchAlias + ".icustomerspin where cid = '" + customerId + "';");
            }catch(Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }

            return Dt;
        }

        public DataTable ReadCondiment(string BranchAlias, string CondimentID)
        {

            try
            {
                Dt = CloudSqlObj.GetData_DT("SELECT * FROM  " + BranchAlias + ".Condiment  where  CondimentID =" + CondimentID + "  ");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return Dt;
        }


        public DataTable LoadTakeAwayCharge(string sDB) 
        {
            try
            {
                Dt = MysqlObj.GetData_DT("Select  ID,TakeAwayCaption from " + sDB + ".webtakeawaychargeOnly; ");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return Dt;
        }


        public string GetMax_SeqNo(string sBranchDB)
        {
            string SeqNo = string.Empty;
            try
            {
                SeqNo = MysqlObj.ExecuteScalar("SELECT max((SeqNo)+1 )as SEQNO FROM " + sBranchDB + ".Menu");
                if (SeqNo == "")
                {
                    SeqNo = Convert.ToString(1);
                }

            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }


            return SeqNo;
        }

        public string GetMax_SeqNo_MenuGroup(string sBranchDB)
        {
            string SeqNo = string.Empty;
            try
            {
                SeqNo = MysqlObj.ExecuteScalar("SELECT max((SeqNo)+1 )as SEQNO FROM " + sBranchDB + ".Menugroup");
                if (SeqNo == "")
                {
                    SeqNo = Convert.ToString(1);
                }

            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }


            return SeqNo;
        }




        public string GetHQ_Status(string  cm_id)
        {
            string sGetUserNamebyUserID = string.Empty;
            try
            {
                sGetUserNamebyUserID = MysqlObj.ExecuteScalar("select isHQ from  portaldb.companymaster where cm_id="+ cm_id + ";");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return sGetUserNamebyUserID;
        }


        public DataTable ReadLoyaltypoints(string HQ)
        {

            try
            {
                Dt = CloudSqlObj.GetData_DT("SELECT lpid,LPName  FROM "+ HQ +".iloyaltypoints;");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return Dt;
        }

        public DataTable ReadPointExpiryMonths(string HQ)
        {

            try
            {
                Dt = CloudSqlObj.GetData_DT("SELECT PeID, PeMonths  FROM " + HQ + ".iPointExpiryMonths;");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return Dt;
        }



        public string Get_BranchId_By_BranchName(string dbName,string HQ)
        {
            string sBranchid = string.Empty;
            try
            {
                sBranchid = MysqlObj.ExecuteScalar("select Branchid from "+HQ+".branches where BranchName = '"+ dbName + "' ;");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);

                Log.Error(ex.Message);
            }
            return sBranchid;
        }
        public DataTable LoadIMembership(string HQ)
        {

            try
            {
                Dt = CloudSqlObj.GetData_DT("select Membershipid,MembershipName from "+HQ+".imembership order by Membershipname ;");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return Dt;
        }
        //*************************************************  ICRM Funcations *********************************************************

        public string GeneratePointsAdjustNo(string HQ)
        {
            string sGenerateReturnNo = string.Empty;
            string RetrunNo;
            int NewReturn;
            try
            {
                sGenerateReturnNo = CloudSqlObj.ExecuteScalar("Select ReferenceNo from  "+HQ +".ICompaignAdjustment order by id desc limit 1;");

                if (sGenerateReturnNo != "")
                {
                    RetrunNo = sGenerateReturnNo.Substring(4);
                    NewReturn = int.Parse(RetrunNo) + 1;
                    sGenerateReturnNo = String.Format("{0:D7}", NewReturn);
                }
                else
                {
                    sGenerateReturnNo = "0000001";

                }
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }

            return sGenerateReturnNo;

        }

        public DataTable Get_iTransHistory_WebTransDetailscondiment(string db, string orderid, string Itemid)
        {

            try
            {
                Dt = CloudSqlObj.GetData_DT("SELECT * FROM " + db + ".webtransdetailcondiment where orderid='" + orderid + "' and Itemid=" + Itemid + ";");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return Dt;
        }


        public DataTable Get_iTransHistory_WebTransDetails(string db, string orderid)
        {

            try
            {
                // Dt = CloudSqlObj.GetData_DT("SELECT * FROM " + db + ".webtransdetail where orderid='" + orderid + "';");
                Dt = CloudSqlObj.GetData_DT("select* from  " + db + ".webtransdetail as wtd  inner join  " + db + ".Inventory as i on wtd.Inventoryid = i.Inventoryid where wtd.orderid = '" + orderid + "';");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return Dt;
        }



        public DataTable Get_iTransHistory_WebTrans(string db, string orderid)
        {
            // change it on requirement 
            try
            {
                Dt = CloudSqlObj.GetData_DT("SELECT* FROM " + db + ".webtrans where orderid='" + orderid + "';");

            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return Dt;
        }


        public DataTable Get_CashBack_Adjustment_Details(string HQ, string ReferenceNo)
        {
            // change it on requirement 
            try
            {
                Dt = CloudSqlObj.GetData_DT("  SELECT * FROM "+ HQ +".ICompaignAdjustment where ReferenceNo='"+ ReferenceNo+"';");

            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return Dt;
        }


         //# CashBack Header 
        public DataTable Get_iTransHistory(string HQ)
        {

            try
            {
                Dt = CloudSqlObj.GetData_DT(" SELECT C.CustomerName as CustomerName,C.UserName as UserName, CB.Businessdate as TransDate,CB.Transid as Orderid,CB.Transid as Transid ," +
                     " GROUP_CONCAT('' ,truncate(CB.Amount,2) SEPARATOR '    |    ') AS `R_CashBack_CashBackEarn` , B.BranchFullName   `Branch Name` " +
                     " FROM "+HQ+".icashbacktrans as CB INNER JOIN "+HQ+".ICustomers as C ON C.CID = CB.CID  inner join   "+ HQ +".Branches as B  on B.Branchid = CB.Branchid " +
                     " WHERE TransType  IN(0, 1)" +
                     " GROUP BY CB.Transid order  by CB.Businessdate desc ; ");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return Dt;
        }

        // # Points Header 
        public DataTable Get_Point_Redemption(string HQ)
        {

            try
            {
                Dt = CloudSqlObj.GetData_DT("SELECT C.CustomerName as CustomerName,C.UserName as UserName , LD.transdate as Transdate,LD.Transid as Orderid ,LD.Points as Points,"
                         + " LD.RDPoints as RDPoints ,LD.BranchName as BranchName  FROM " + HQ+".iloyaltypointsdetails as LD "  
                         + " inner join "+HQ+".ICustomers as C On LD.CID = C.CID  ; ");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return Dt;
        }
        // # Point Report By MobileNo
        public DataTable Get_Point_Redemption_By_MobileNo(string HQ, string username)
        {

            try
            {
                Dt = CloudSqlObj.GetData_DT("SELECT C.CustomerName as CustomerName,C.UserName as UserName , LD.transdate as Transdate,LD.Transid as Orderid ,LD.Points as Points,"
                         + " LD.RDPoints as RDPoints ,LD.BranchName as BranchName  FROM " + HQ + ".iloyaltypointsdetails as LD "
                         + " inner join " + HQ + ".ICustomers as C On LD.CID = C.CID where  C.UserName ='" + username + "' ; ");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return Dt;
        }
        // # Point Report By MobileNo and Between Two Date 
        public DataTable Get_Point_Redemption_By_MobileNo_Date(string HQ, string username, string D1, string D2)
        {

            try
            {
                Dt = CloudSqlObj.GetData_DT("SELECT C.CustomerName as CustomerName,C.UserName as UserName , LD.transdate as Transdate,LD.Transid as Orderid ,LD.Points as Points, "
                         + " LD.RDPoints as RDPoints ,LD.BranchName as BranchName  FROM " + HQ + ".iloyaltypointsdetails as LD "
                         + " inner join " + HQ + ".ICustomers as C On LD.CID = C.CID where  C.UserName ='" + username + "'  and " 
                         + " date(LD.Transdate) between '" + D1+"' and '"+D2+"' ; ");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return Dt;
        }

        // # Point Report By Date Alone 
        public DataTable Get_Point_Redemption_By_Date_Alone(string HQ, string D1, string D2)
        {

            try
            {
                Dt = CloudSqlObj.GetData_DT("SELECT C.CustomerName as CustomerName,C.UserName as UserName , LD.transdate as Transdate,LD.Transid as Orderid ,LD.Points as Points,"
                         + " LD.RDPoints as RDPoints ,LD.BranchName as BranchName  FROM " + HQ + ".iloyaltypointsdetails as LD "
                         + " inner join " + HQ + ".ICustomers as C On LD.CID = C.CID where "
                         + " Date(LD.Transdate) between '" + D1 + "' and '" + D2 + "' ; ");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return Dt;
        }





        public DataTable Get_iTrans_CashBack_By_MobileNo (string HQ,string Username)
        {

            try
            {
                Dt = CloudSqlObj.GetData_DT(" SELECT C.CustomerName as CustomerName,C.UserName as UserName , CB.Businessdate as TransDate,CB.Transid as Orderid,CB.Transid as Transid ," +
                     " GROUP_CONCAT('' ,truncate(CB.Amount,2) SEPARATOR '    |    ') AS `R_CashBack_CashBackEarn` , B.BranchFullName   `Branch Name` " +
                     " FROM " + HQ + ".icashbacktrans as CB INNER JOIN " + HQ + ".ICustomers as C ON C.CID = CB.CID  " +
                     " inner join   " + HQ + ".Branches as B  on B.Branchid = CB.Branchid " +
                     " WHERE TransType  IN(0, 1) And C.UserName ="+ Username + " " +
                     " GROUP BY CB.Transid order  by CB.Businessdate desc ; ");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return Dt;
        }


        public DataTable Get_iTrans_CashBack_By_MobileNo_and_Date(string HQ, string username,string D1,string D2)
        {

            try
            {
                Dt = CloudSqlObj.GetData_DT(" SELECT C.CustomerName as CustomerName,C.UserName as UserName , CB.Businessdate as TransDate,CB.Transid as Orderid,CB.Transid as Transid ," +
                     " GROUP_CONCAT('' ,truncate(CB.Amount,2) SEPARATOR '    |    ') AS `R_CashBack_CashBackEarn` , B.BranchFullName   `Branch Name` " +
                     " FROM " + HQ + ".icashbacktrans as CB INNER JOIN " + HQ + ".ICustomers as C ON C.CID = CB.CID  inner join   " + HQ + ".Branches as B  on B.Branchid = CB.Branchid " +
                     " WHERE TransType  IN(0, 1) And C.UserName =" + username + " and  date(CB.Businessdate) between '"+D1+"' And '"+D2+"' " +
                     " GROUP BY CB.Transid order  by CB.Businessdate desc;");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return Dt;
        }




        public DataTable Get_iTrans_CashBack_By_Date_Alone(string HQ,  string D1, string D2)
        {

            try
            {
                Dt = CloudSqlObj.GetData_DT(" SELECT C.CustomerName as CustomerName,C.UserName as UserName , CB.Businessdate as TransDate,CB.Transid as Orderid,CB.Transid as Transid ," +
                     " GROUP_CONCAT('' ,truncate(CB.Amount,2) SEPARATOR '    |    ') AS `R_CashBack_CashBackEarn` , B.BranchFullName   `Branch Name` " +
                     " FROM " + HQ + ".icashbacktrans as CB INNER JOIN " + HQ + ".ICustomers as C ON C.CID = CB.CID  inner join   " + HQ + ".Branches as B  on B.Branchid = CB.Branchid " +
                     " WHERE TransType  IN(0, 1) And date(CB.Businessdate) between '" + D1 + "' And '" + D2 + "' " +
                     " GROUP BY CB.Transid order  by CB.Businessdate desc ; ");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return Dt;
        }

        public DataTable getChangePaymentLog(string BranchAlias,string from,string to) //john added on 12-Dec-2024 for change paymenttypes
        {
            try
            {
                Dt = CloudSqlObj.GetData_DT("SELECT transid,transno,transactiondate,frompaymenttype,changepaymenttypedate,topaymenttype,userid,username   FROM  " + BranchAlias + ".changepaymentlog where date(transactiondate) between '" + from + "' and '" + to+ "'");
            }
            catch (Exception ex)
            {
                ObjLog.Write(ex);
                Log.Error(ex.Message);
            }
            return Dt;
        }

        public DataTable TransDetailCosting(string db)
        {
            Dt = CloudSqlObj.GetData_DT("select a.transid,itemid,inventoryid,batchid,sqty,scost,expirydate,expiryalarm,businessdate from " + db + ".transdetailcosting a join " + db + ".trans b on a.transid = b.transid");
            return Dt;
        }



        #region AESEncryption 
        public readonly byte[] IV = new byte[16];
        /// <summary>
        /// Encrypts a string using AES encryption.
        /// </summary>
        /// <param name="plainText">The string to encrypt.</param>
        /// <param name="keyString">The encryption key as a string.</param>
        /// <returns>Encrypted string in Base64 format.</returns>
        public string EncryptString(string plainText, string keyString)
        {
            // Convert the key string to a byte array
            byte[] key = Encoding.UTF8.GetBytes(keyString);

            // Create an instance of the AES algorithm
            using (Aes aes = Aes.Create())
            {
                aes.Key = key;
                aes.IV = IV;

                // Create a memory stream to hold the encrypted data
                using (MemoryStream memoryStream = new MemoryStream())
                {
                    // Create a crypto stream using the memory stream and the AES encryptor
                    using (CryptoStream cryptoStream = new CryptoStream(memoryStream, aes.CreateEncryptor(aes.Key, aes.IV), CryptoStreamMode.Write))
                    {
                        // Create a stream writer to write the encrypted data to the crypto stream
                        using (StreamWriter streamWriter = new StreamWriter(cryptoStream))
                        {
                            // Write the plain text to the crypto stream
                            streamWriter.Write(plainText);
                        }
                        // Return the encrypted data as a Base64 string
                        return Convert.ToBase64String(memoryStream.ToArray());
                    }
                }
            }
        }

        /// <summary>
        /// Decrypts a string encrypted using AES encryption.
        /// </summary>
        /// <param name="cipherText">The encrypted string in Base64 format.</param>
        /// <param name="keyString">The encryption key as a string.</param>
        /// <returns>Decrypted string.</returns>
        public string DecryptString(string cipherText, string keyString)
        {
            // Convert the key string to a byte array
            byte[] key = Encoding.UTF8.GetBytes(keyString);

            // Convert the Base64 encoded encrypted data to a byte array
            byte[] buffer = Convert.FromBase64String(cipherText);

            // Create an instance of the AES algorithm
            using (Aes aes = Aes.Create())
            {
                aes.Key = key;
                aes.IV = IV;

                // Create a memory stream to hold the decrypted data
                using (MemoryStream memoryStream = new MemoryStream(buffer))
                {
                    // Create a crypto stream using the memory stream and the AES decryptor
                    using (CryptoStream cryptoStream = new CryptoStream(memoryStream, aes.CreateDecryptor(aes.Key, aes.IV), CryptoStreamMode.Read))
                    {
                        // Create a stream reader to read the decrypted data from the crypto stream
                        using (StreamReader streamReader = new StreamReader(cryptoStream))
                        {
                            // Return the decrypted data as a string
                            return streamReader.ReadToEnd();
                        }
                    }
                }
            }
        }


        public string Generic_AES_Encrypt(string giventxt, string keyval)
        {



            string retunencrypt = null;
            try
            {

                retunencrypt = EncryptString(giventxt, keyval);
            }
            catch (Exception ex)
            {
                Log.Error("Check the The Exception:", ex);
            }


            return retunencrypt;


        }

        public string Generic_AES_Decrypt(string giventxt, string keyval)
        {
            string retundecrypt = null;

            try
            {
                retundecrypt = DecryptString(giventxt, keyval);
            }
            catch (Exception ex)
            {
                Log.Error("Check the The Exception:", ex);
            }


            return retundecrypt;


        }



        public  string ConvertToAES(string password, string key )
        {

            string enc_password = string.Empty;
            try
            {
                            
                //Encrypt the original string
                enc_password = EncryptString(password, key);

                
            }
            catch (Exception ex)
            {
                Log.Error("Error :", ex);
            }
            return enc_password.ToString();

        }

        #endregion







    }
}





