Beispiel #1
0
        public void save(SeatType SeatType)
        {
            try
            {
                using (var context = new SmsMisDB())
                {
                    SeatType.AddDateTime = DateTime.Now;
                    var entry = context.Entry(SeatType);

                    if (entry != null)
                    {
                        if (SeatType.SeatTypeCode == 0)
                        {
                            SeatType.SeatTypeCode = Functions.getNextPk("SeatType", SeatType.SeatTypeCode, SeatType.CompanyCode);
                            entry.State           = EntityState.Added;
                        }
                        else
                        {
                            entry.State = EntityState.Modified;
                        }
                        context.SaveChanges();
                    }
                }
            }
            catch (System.Data.Entity.Validation.DbEntityValidationException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public DataTable SelectAll(int CompanyCode)
        {
            SmsMis.Models.Console.Handlers.Admin.SmsMisDB db = new SmsMis.Models.Console.Handlers.Admin.SmsMisDB();
            SqlConnection connection = null;

            try
            {
                connection = new hdlCommonMethods().GetConnection(SmsMis.Models.Console.Common.GlobalConstants.connectionString);
                DataTable dtCompany = SqlHelper.ExecuteDataTable(connection, CommandType.Text, string.Concat("SELECT p.BiilingCycle,d.CompanyCode,d.BranchCode,d.SessionCode,d.StudentNo,d.FullName,StudentRollNo = case when d.StudentRollNo IS null then cast(d.StudentNo as varchar) else d.StudentRollNo end,c.ClassName,IsNew = case when p.companycode IS NULL then cast(1 as bit) else cast(0  as bit) end from Student d inner join Class c on d.CompanyCode = c.CompanyCode and d.ClassCode = c.ClassCode left join StudentBillingCycle p on p.CompanyCode = d.CompanyCode and p.BranchCode = d.BranchCode and p.SessionCode = d.SessionCode and p.StudentNo = d.StudentNo where d.Status = 1 AND d.CompanyCode = ", CompanyCode));
                if (dtCompany.Rows.Count > 0)
                {
                    return(dtCompany);
                }
                return(null);
            }
            catch (Exception ex)
            {
                throw new ExceptionTranslater(ex);
            }
            finally
            {
                if (connection != null && connection.State == ConnectionState.Open)
                {
                    connection.Close();
                }
            }
        }
        public DataTable SelectAll(int CompanyCode)
        {
            //SmsMis.Models.Console.Handlers.Admin.SmsMisDB db = new SmsMis.Models.Console.Handlers.Admin.SmsMisDB();
            //return db.StudentFeeTermScholarship.Where(s => s.CompanyCode == CompanyCode).ToList();
            SmsMis.Models.Console.Handlers.Admin.SmsMisDB db = new SmsMis.Models.Console.Handlers.Admin.SmsMisDB();
            //from p in db.StudentFixedDiscount.AsEnumerable()
            SqlConnection connection = null;

            try
            {
                connection = new hdlCommonMethods().GetConnection(SmsMis.Models.Console.Common.GlobalConstants.connectionString);
                DataTable dtCompany = SqlHelper.ExecuteDataTable(connection, CommandType.Text, string.Concat("SELECT f.FeeTermName, p.FeeTermCode,p.ScholarshipRate,d.CompanyCode,d.BranchCode,d.SessionCode,d.StudentNo,d.FullName,StudentRollNo = case when d.StudentRollNo IS null then cast(d.StudentNo as varchar) else d.StudentRollNo end,c.ClassName,IsNew = case when p.companycode IS NULL then cast(1 as bit) else cast(0  as bit) end from Student d inner join Class c on d.CompanyCode = c.CompanyCode and d.ClassCode = c.ClassCode left join StudentFeeTermScholarship p on p.CompanyCode = d.CompanyCode and p.BranchCode = d.BranchCode and p.SessionCode = d.SessionCode and p.StudentNo = d.StudentNo left join FeeTerm f on f.CompanyCode = p.CompanyCode and f.FeeTermCode = p.FeeTermCode where d.Status = 1 AND d.CompanyCode = ", CompanyCode));
                if (dtCompany.Rows.Count > 0)
                {
                    return(dtCompany);
                }
                return(null);
            }
            catch (Exception ex)
            {
                throw new ExceptionTranslater(ex);
            }
            finally
            {
                if (connection != null && connection.State == ConnectionState.Open)
                {
                    connection.Close();
                }
            }
        }
Beispiel #4
0
        public IList <VoucherMaster> SelectAll(int CompanyCode)
        {
            SmsMis.Models.Console.Handlers.Admin.SmsMisDB db = new SmsMis.Models.Console.Handlers.Admin.SmsMisDB();
            var voucherMasters = db.VoucherMaster.Where(a => a.CompanyCode == CompanyCode).OrderBy(v => v.VoucherNo).ToList();

            return(voucherMasters);
        }
        public IList <object> SelectAllCOA(int CompanyCode)
        {
            SmsMis.Models.Console.Handlers.Admin.SmsMisDB db = new SmsMis.Models.Console.Handlers.Admin.SmsMisDB();
            //return db.ChartOfAccounts.ToList().Where(s => s.CompanyCode == CompanyCode).ToList();


            var otherItems = (from p in db.ChartOfAccounts
                              join d in db.COALevels on p.CompanyCode equals d.CompanyCode
                              //from e in db.COABranch.Where(e => e.CompanyCode == p.CompanyCode ).DefaultIfEmpty()
                              where d.LevelId == p.LevelId
                              select new
            {
                CompanyCode = p.CompanyCode,
                AccountCode = p.AccountCode,
                AccountTitle = p.AccountTitle,
                ShortName = p.ShortName,
                Remarks = p.Remarks,
                LevelId = p.LevelId,
                AccountType = p.AccountType,
                ParentAccountCode = p.ParentAccountCode,
                Status = p.Status,
                AddByUserId = p.AddByUserId,
                AddDateTime = p.AddDateTime,
                LevelColor = d.LevelColor
            }).Where(a => a.CompanyCode == CompanyCode).ToArray();

            return(otherItems);
        }
Beispiel #6
0
 public void delete(Branch branch)
 {
     try
     {
         var context = new SmsMisDB();
         context.Branch.Attach(branch);
         var entry = context.Entry(branch);
         if (entry != null)
         {
             entry.State = EntityState.Deleted;
             context.BranchContactPerson.ToList <BranchContactPerson>().Where(i => i.CompanyCode == branch.CompanyCode && i.BranchCode == branch.BranchCode && i.SrNo > 0).ToList <BranchContactPerson>().ForEach(s => context.Entry(s).State = EntityState.Deleted);
             context.SaveChanges();
         }
     }
     catch (System.Data.Entity.Validation.DbEntityValidationException ex)
     {
         //throw SmsMis.Models.Console.Common.ExceptionTranslater.translate(ex);
         throw ex;
     }
     catch (Exception ex)
     {
         //throw SmsMis.Models.Console.Common.ExceptionTranslater.translate(ex);
         throw ex;
     }
 }
Beispiel #7
0
 public void delete(Department Department)
 {
     try
     {
         var context = new SmsMisDB();
         context.Department.Attach(Department);
         var entry = context.Entry(Department);
         if (entry != null)
         {
             entry.State = EntityState.Deleted;
             context.DepartmentBranch.ToList().Where(i => i.DepartmentCode == Department.DepartmentCode && i.CompanyCode == Department.CompanyCode).ToList <DepartmentBranch>().ForEach(s => context.Entry(s).State = EntityState.Deleted);
             context.SaveChanges();
         }
     }
     catch (System.Data.Entity.Validation.DbEntityValidationException ex)
     {
         //throw SmsMis.Models.Console.Common.ExceptionTranslater.translate(ex);
         throw ex;
     }
     catch (Exception ex)
     {
         //throw SmsMis.Models.Console.Common.ExceptionTranslater.translate(ex);
         throw ex;
     }
 }
Beispiel #8
0
        public void save(MarketingReference MarketingReference)
        {
            try
            {
                using (var context = new SmsMisDB())
                {
                    MarketingReference.AddDateTime = DateTime.Now;
                    var entry = context.Entry(MarketingReference);

                    if (entry != null)
                    {
                        if (MarketingReference.MarketingReferenceCode == 0)
                        {
                            MarketingReference.MarketingReferenceCode = Functions.getNextPk("MarketingReference", MarketingReference.MarketingReferenceCode, 0);
                            entry.State = EntityState.Added;
                        }
                        else
                        {
                            entry.State = EntityState.Modified;
                        }
                        context.SaveChanges();
                    }
                }
            }
            catch (System.Data.Entity.Validation.DbEntityValidationException ex)
            {
                //throw ex;
            }
            catch (Exception ex)
            {
                // throw ex;
            }
        }
Beispiel #9
0
        public void delete(List <BranchSession> branchSession, int iCompanyCode, int iBranchCode)
        {
            try
            {
                using (var context = new SmsMisDB())
                {
                    if (branchSession != null)
                    {
                        branchSession.ToList <BranchSession>().ForEach(i => i.AddDateTime = DateTime.Now);
                        context.BranchSession.ToList <BranchSession>().Where(s => s.CompanyCode == branchSession[0].CompanyCode && s.BranchCode == branchSession[0].BranchCode).ToList().ForEach(entry2 => context.Entry(entry2).State = EntityState.Deleted);
                    }
                    //else
                    //    context.BranchSession.ToList<BranchSession>().Where(s => s.CompanyCode == iCompanyCode && s.BranchCode == iBranchCode).ToList().ForEach(entry2 => context.Entry(entry2).State = EntityState.Deleted);

                    context.SaveChanges();
                }
            }
            catch (System.Data.Entity.Validation.DbEntityValidationException ex)
            {
                //throw ex;
            }
            catch (Exception ex)
            {
                // throw ex;
            }
        }
        public void save(List <ClassCourseSubject> ClassCourseSubject, int iCompanyCode, int iBranchCode, int iClassCode, int iCourseCode)
        {
            try
            {
                using (var context = new SmsMisDB())
                {
                    if (ClassCourseSubject != null)
                    {
                        ClassCourseSubject.ToList <ClassCourseSubject>().ForEach(i => i.AddDateTime = DateTime.Now);

                        foreach (var row in ClassCourseSubject)
                        {
                            context.ClassCourseSubject.Add(row);
                        }
                        context.ClassCourseSubject.ToList <ClassCourseSubject>().Where(s => s.CompanyCode == iCompanyCode && s.BranchCode == iBranchCode && s.ClassCode == iClassCode && s.CourseCode == iCourseCode).ToList().ForEach(entry2 => context.Entry(entry2).State = System.Data.Entity.EntityState.Deleted);
                    }
                    else
                    {
                        context.ClassCourseSubject.ToList <ClassCourseSubject>().Where(s => s.CompanyCode == iCompanyCode && s.BranchCode == iBranchCode && s.ClassCode == iClassCode && s.CourseCode == iCourseCode).ToList().ForEach(entry2 => context.Entry(entry2).State = System.Data.Entity.EntityState.Deleted);
                    }

                    context.SaveChanges();
                }
            }
            catch (System.Data.Entity.Validation.DbEntityValidationException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
Beispiel #11
0
        public void save(Religion Religion)
        {
            try
            {
                using (var context = new SmsMisDB())
                {
                    Religion.AddDateTime = DateTime.Now;
                    var entry = context.Entry(Religion);

                    if (entry != null)
                    {
                        if (Religion.ReligionCode == 0)
                        {
                            Religion.ReligionCode = Functions.getNextPk("Religion", Religion.ReligionCode, 0);
                            entry.State           = EntityState.Added;
                        }
                        else
                        {
                            entry.State = EntityState.Modified;
                        }
                        context.SaveChanges();
                    }
                }
            }
            catch (System.Data.Entity.Validation.DbEntityValidationException ex)
            {
                //throw ex;
            }
            catch (Exception ex)
            {
                // throw ex;
            }
        }
Beispiel #12
0
        public void save(BranchBuilding BranchBuilding)
        {
            try
            {
                using (var context = new SmsMisDB())
                {
                    BranchBuilding.AddDateTime = DateTime.Now;
                    var entry = context.Entry(BranchBuilding);

                    if (entry != null)
                    {
                        if (BranchBuilding.BuildingCode == 0)
                        {
                            BranchBuilding.BuildingCode = Functions.getNextPk("BranchBuilding", BranchBuilding.BuildingCode, BranchBuilding.CompanyCode, BranchBuilding.BranchCode);
                            entry.State = EntityState.Added;
                        }
                        else
                        {
                            entry.State = EntityState.Modified;
                        }
                        context.SaveChanges();
                    }
                }
            }
            catch (System.Data.Entity.Validation.DbEntityValidationException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
Beispiel #13
0
        public void save(FeeParticular FeeParticular)
        {
            try
            {
                using (var context = new SmsMisDB())
                {
                    FeeParticular.AddDateTime = DateTime.Now;
                    var entry = context.Entry(FeeParticular);

                    if (entry != null)
                    {
                        if (FeeParticular.ParticularCode == 0)
                        {
                            FeeParticular.ParticularCode = Functions.getNextPk("FeeParticular", FeeParticular.ParticularCode, FeeParticular.CompanyCode);
                            entry.State = EntityState.Added;
                        }
                        else
                        {
                            entry.State = EntityState.Modified;
                        }
                        context.SaveChanges();
                    }
                }
            }
            catch (System.Data.Entity.Validation.DbEntityValidationException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
Beispiel #14
0
      public IList <InstrumentTypeVM> SelectAllApprove(int companyCode, int branchCode)
      {
          SmsMis.Models.Console.Handlers.Admin.SmsMisDB db = new SmsMis.Models.Console.Handlers.Admin.SmsMisDB();
          var InstrumentTypes = db.Database.SqlQuery <InstrumentTypeVM>("select CAST(RANK() over (order by AccountCode) AS int) as InstrumentTypeRowNumber, AccountCode,InstrumentTypeCode,InstrumentName,CompanyCode,BranchCode from lov_instrument")
                                .Where(x => x.CompanyCode == companyCode && x.BranchCode == branchCode).ToList();

          return(InstrumentTypes);
      }
Beispiel #15
0
        public IList <LinksInfo> getClientLinks(string userID)
        {
            SmsMis.Models.Console.Handlers.Admin.SmsMisDB db = new SmsMis.Models.Console.Handlers.Admin.SmsMisDB();
            DbRawSqlQuery <LinksInfo> data = db.Database.SqlQuery <LinksInfo>
                                                 ("Select isnull(parent,0) as parentId,LinkID  as linkID,label as linkLabel,url as url from vwClientAccess where accesstypeid=1 and UserID=@p0 order by linkid,sortorder", userID);

            return(data.ToArray());
        }
Beispiel #16
0
        public IList <VoucherTypeVM> SelectAllApproved(int companyCode, int branchCode)
        {
            SmsMis.Models.Console.Handlers.Admin.SmsMisDB db = new SmsMis.Models.Console.Handlers.Admin.SmsMisDB();

            IList <VoucherTypeVM> voucherTypes = db.Database.SqlQuery <VoucherTypeVM>("select CAST( RANK() over (order by AccountCode,VoucherCode ) AS int) as VoucherTypeRowNumber, VoucherCode,VoucherName,AccountCode,AccountTitle,TransactionType, Category,Frequency,CompanyCode,BranchCode,CurrencyName, CurrencyCode, LocalCurrencyCode, CurrencySymbol, LocalCurrencySymbol from LOV_VoucherType")
                                                 .Where(x => x.CompanyCode == companyCode && x.BranchCode == branchCode).ToList();

            return(voucherTypes);
        }
Beispiel #17
0
 public IList <InstrumentType> SelectAll()
 {
     try
     {
         SmsMis.Models.Console.Handlers.Admin.SmsMisDB db = new SmsMis.Models.Console.Handlers.Admin.SmsMisDB();
         return(db.InstrumentType.ToList());
     }
     catch (Exception ex)
     {
         return(null);
     }
 }
Beispiel #18
0
        //public VoucherMaster SelectByIDs(int companyID)
        //{
        //    SmsMis.Models.Console.Handlers.Admin.SmsMisDB db = new SmsMis.Models.Console.Handlers.Admin.SmsMisDB();
        //    return db.Branch.ToList().Where(s => s.CompanyCode == companyID).ToList();
        //}
        //Start Farhat Ullah
        public bool ValidateInstrumntNumber(int companyCode, int branchCode, string accountCode, int instrumentCode, int instrumentNo)
        {
            SmsMis.Models.Console.Handlers.Admin.SmsMisDB db = new SmsMis.Models.Console.Handlers.Admin.SmsMisDB();
            var val = db.InstrumentSerialDetail.Where(x => x.CompanyCode == companyCode && x.BranchCode == branchCode &&
                                                      x.AccountCode == accountCode && x.InstrumentTypeCode == instrumentCode && x.InstrumentNo == instrumentNo).FirstOrDefault();

            if (val != null)
            {
                return(true);
            }
            return(false);
        }
Beispiel #19
0
        public void save(SmsMis.Models.Console.Admin.Type Type, bool isNew)
        {
            try
            {
                using (var context = new SmsMisDB())
                {
                    Type.AddDateTime = DateTime.Now;
                    var entry = context.Entry(Type);

                    if (entry != null)
                    {
                        if (isNew)
                        {
                            Type.TypeCode = Functions.getNextPk("Type", Type.TypeCode, Type.CompanyCode);
                            if (Type.TypeBranch != null && Type.TypeBranch.Count > 0)
                            {
                                Type.TypeBranch.ToList().ForEach(i => { i.TypeCode = Type.TypeCode; });
                            }
                            entry.State = EntityState.Added;
                        }
                        else
                        {
                            if (Type.TypeBranch != null && Type.TypeBranch.Count > 0)
                            {
                                Type.TypeBranch.ToList().ForEach(i => { i.TypeCode = Type.TypeCode; });
                            }

                            entry.State = EntityState.Modified;
                        }



                        if (Type.TypeBranch != null && Type.TypeBranch.Count > 0)
                        {
                            Type.TypeBranch.ToList <TypeBranch>().ForEach(x => context.Entry(x).State = EntityState.Added);
                        }

                        context.TypeBranch.ToList().Where(i => i.TypeCode == Type.TypeCode && i.CompanyCode == Type.CompanyCode).ToList <TypeBranch>().ForEach(s => context.Entry(s).State = EntityState.Deleted);

                        context.SaveChanges();
                    }
                }
            }
            catch (System.Data.Entity.Validation.DbEntityValidationException ex)
            {
                //throw ex;
            }
            catch (Exception ex)
            {
                // throw ex;
            }
        }
Beispiel #20
0
        public void save(Department Department, bool isNew)
        {
            try
            {
                using (var context = new SmsMisDB())
                {
                    Department.AddDateTime = DateTime.Now;
                    var entry = context.Entry(Department);

                    if (entry != null)
                    {
                        if (isNew)
                        {
                            Department.DepartmentCode = Functions.getNextPk("Department", Department.DepartmentCode, Department.CompanyCode);
                            if (Department.DepartmentBranch != null && Department.DepartmentBranch.Count > 0)
                            {
                                Department.DepartmentBranch.ToList().ForEach(i => { i.DepartmentCode = Department.DepartmentCode; });
                            }
                            entry.State = EntityState.Added;
                        }
                        else
                        {
                            if (Department.DepartmentBranch != null && Department.DepartmentBranch.Count > 0)
                            {
                                Department.DepartmentBranch.ToList().ForEach(i => { i.DepartmentCode = Department.DepartmentCode; });
                            }

                            entry.State = EntityState.Modified;
                        }



                        if (Department.DepartmentBranch != null && Department.DepartmentBranch.Count > 0)
                        {
                            Department.DepartmentBranch.ToList <DepartmentBranch>().ForEach(x => context.Entry(x).State = EntityState.Added);
                        }

                        context.DepartmentBranch.ToList().Where(i => i.DepartmentCode == Department.DepartmentCode && i.CompanyCode == Department.CompanyCode).ToList <DepartmentBranch>().ForEach(s => context.Entry(s).State = EntityState.Deleted);

                        context.SaveChanges();
                    }
                }
            }
            catch (System.Data.Entity.Validation.DbEntityValidationException ex)
            {
                //throw ex;
            }
            catch (Exception ex)
            {
                // throw ex;
            }
        }
Beispiel #21
0
        public DataTable SelectAll(int companycode)
        {
            SmsMis.Models.Console.Handlers.Admin.SmsMisDB db = new SmsMis.Models.Console.Handlers.Admin.SmsMisDB();
            //from p in db.StudentFixedDiscount.AsEnumerable()
            SqlConnection connection = null;

            try
            {
                connection = new hdlCommonMethods().GetConnection(SmsMis.Models.Console.Common.GlobalConstants.connectionString);
                DataTable dtCompany = SqlHelper.ExecuteDataTable(connection, CommandType.Text, string.Concat("SELECT p.DiscountTypeCode,d.CompanyCode,d.BranchCode,d.SessionCode,d.StudentNo,Discounts = case when p.DiscountPercentage = 0 then cast(cast(p.DiscountAmount as float) AS varchar) else cast(p.DiscountPercentage as varchar) + '%' end,p.DiscountAmount,p.DiscountPercentage,d.FullName,StudentRollNo = case when d.StudentRollNo IS null then cast(d.StudentNo as varchar) else d.StudentRollNo end,c.ClassName,p.AddByUserId,IsNew = case when p.companycode IS NULL then cast(1 as bit) else cast(0  as bit) end from Student d inner join Class c on d.CompanyCode = c.CompanyCode and d.ClassCode = c.ClassCode left join StudentFixedDiscount p on p.CompanyCode = d.CompanyCode and p.BranchCode = d.BranchCode and p.SessionCode = d.SessionCode and p.StudentNo = d.StudentNo where d.Status = 1 AND d.CompanyCode = ", companycode));
                if (dtCompany.Rows.Count > 0)
                {
                    return(dtCompany);
                }
                return(null);
            }
            catch (Exception ex)
            {
                throw new ExceptionTranslater(ex);
            }
            finally
            {
                if (connection != null && connection.State == ConnectionState.Open)
                {
                    connection.Close();
                }
            }
            //var otherItems = (from d in db.Student
            //                  join c in db.Classes on d.CompanyCode equals c.CompanyCode where d.ClassCode == c.ClassCode
            //                  from p in db.StudentFixedDiscount.Where(b => b.CompanyCode == d.CompanyCode && b.BranchCode == d.BranchCode && b.SessionCode == d.SessionCode && b.StudentNo == d.StudentNo).DefaultIfEmpty()
            //                  select new
            //                  {
            //                      CompanyCode = d.CompanyCode,
            //                      BranchCode = d.BranchCode,
            //                      SessionCode = d.SessionCode,
            //                      StudentNo = p.StudentNo,
            //                      //Discounts = p.DiscountPercentage == 0 ? Convert.ToString(p.DiscountAmount) : string.Concat(p.DiscountPercentage,"%"),
            //                      DiscountAmount = p.DiscountAmount,
            //                      DiscountPercentage = p.DiscountPercentage,
            //                      FullName = d.FullName,
            //                      //StudentRollNo = string.IsNullOrEmpty(d.StudentRollNo) ? Convert.ToString(d.StudentNo) : d.StudentRollNo,
            //                      ClassName = c.ClassName,
            //                      AddByUserId = p.AddByUserId ,
            //                      //AddDateTime = p.AddDateTime
            //                  }).Where(a => a.CompanyCode == companycode).ToArray();
            //return otherItems;

            //return db.StudentFixedDiscount.Where(s=>s.CompanyCode == companycode).ToList();
        }
Beispiel #22
0
        public IList <Object> SelectAll(int CompanyCode)
        {
            SmsMis.Models.Console.Handlers.Admin.SmsMisDB db = new SmsMis.Models.Console.Handlers.Admin.SmsMisDB();
            var otherItems = (from p in db.Employee
                              //from e in db.COABranch.Where(e => e.CompanyCode == p.CompanyCode ).DefaultIfEmpty()
                              select new
            {
                CompanyCode = p.CompanyCode,
                EmployeeCode = p.EmployeeCode,
                EmployeeName = p.EmployeeName
            }).Where(a => a.CompanyCode == CompanyCode).ToArray();

            return(otherItems);
            //return db.Employee.ToList<Employee>().Where(s => s.CompanyCode == CompanyCode).ToList();
        }
Beispiel #23
0
        public IList <object> SelectAllActiveCOA(int CompanyCode)
        {
            SmsMis.Models.Console.Handlers.Admin.SmsMisDB db = new SmsMis.Models.Console.Handlers.Admin.SmsMisDB();
            //return db.ChartOfAccounts.ToList().Where(s => s.CompanyCode == CompanyCode).ToList();


            var otherItems = (from p in db.ChartOfAccounts
                              //from e in db.COABranch.Where(e => e.CompanyCode == p.CompanyCode ).DefaultIfEmpty()
                              select new
            {
                CompanyCode = p.CompanyCode,
                AccountCode = p.AccountCode,
                AccountTitle = p.AccountTitle
            }).Where(a => a.CompanyCode == CompanyCode).ToArray();

            return(otherItems);
        }
        public DataTable SelectAllClass()
        {
            //SmsMis.Models.Console.Handlers.Admin.SmsMisDB db = new SmsMis.Models.Console.Handlers.Admin.SmsMisDB();
            //DataTable dt = new DbContext("name=ValencySGIEntities").Database.SqlQuery("Select distinct css.CompanyCode,css.BranchCode,css.ClassCode,css.CourseCode,css.SubjectCode,co.CourseName,su.SubjectName,cl.ClassName from ClassCourseSubject css with(nolock) inner join Course co with(nolock) on css.CourseCode = co.CourseCode and css.CompanyCode = co.CompanyCode inner join [Subject] su with(nolock) on css.SubjectCode = su.SubjectCode inner join Class cl with(nolock) on cl.ClassCode = css.ClassCode and cl.CompanyCode = css.CompanyCode");

            DataTable dt              = new DataTable();
            var       context         = new SmsMisDB();
            var       conn            = context.Database.Connection;
            var       connectionState = conn.State;

            try
            {
                using (context)
                {
                    if (connectionState != ConnectionState.Open)
                    {
                        conn.Open();
                    }
                    using (var cmd = conn.CreateCommand())
                    {
                        //cmd.CommandText = "Select distinct css.CompanyCode,css.BranchCode,css.ClassCode,cl.ClassName,css.Mandatory from ClassCourseSubject css with(nolock) inner join Course co with(nolock) on css.CourseCode = co.CourseCode and css.CompanyCode = co.CompanyCode inner join [Subject] su with(nolock) on css.SubjectCode = su.SubjectCode inner join Class cl with(nolock) on cl.ClassCode = css.ClassCode and cl.CompanyCode = css.CompanyCode where co.Status = 1 and su.Status =1 and cl.Status = 1";
                        cmd.CommandText = "Select distinct css.CompanyCode,css.BranchCode,css.ClassCode, cl.ClassName from ClassCourseSubject css with(nolock) inner join Class cl with(nolock) on cl.ClassCode = css.ClassCode and cl.CompanyCode = css.CompanyCode";
                        //cmd.CommandText = "Select * from ClassCourseSubject";
                        cmd.CommandType = CommandType.Text;
                        //cmd.Parameters.Add(new SqlParameter("jobCardId", 100525));
                        using (var reader = cmd.ExecuteReader())
                        {
                            dt.Load(reader);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (connectionState != ConnectionState.Open)
                {
                    conn.Close();
                }
            }
            return(dt);
        }
Beispiel #25
0
        //End By Farhat Ullah
        public int getVoucherNumber(int companyCode, int branchCode, int voucherTypeCode, DateTime voucherDate)
        {
            SmsMis.Models.Console.Handlers.Admin.SmsMisDB db = new SmsMis.Models.Console.Handlers.Admin.SmsMisDB();
            var voucherNumberList = db.VoucherMaster.Where(x => x.CompanyCode == companyCode && x.BranchCode == branchCode).ToList();
            int voucherNumber     = voucherNumberList.Count.Equals(0) ? 1 : voucherNumberList.Max(x => x.VoucherNo);
            //int voucherNumber = db.VoucherMaster.Where(x => x.CompanyCode == companyCode && x.BranchCode == branchCode).Max(x => x.VoucherNo);
            var voucherFrequency = db.VoucherType.FirstOrDefault(x => x.VoucherCode == voucherTypeCode).Frequency;

            voucherNumber = 0;
            if (voucherFrequency == "Y") //Yearly
            {
                DateTime fromDate;
                DateTime toDate;
                var      fiscalYear =
                    db.FiscalYear.FirstOrDefault(e => e.CompanyCode == companyCode && e.BranchCode == branchCode);
                fromDate = fiscalYear.StartDate;
                toDate   = fiscalYear.EndDate;
                DateTime dt        = System.DateTime.Now;
                var      voucherNo =
                    db.VoucherMaster.Where(
                        x =>
                        x.CompanyCode == companyCode && x.BranchCode == branchCode && x.VoucherDate >= fromDate &&
                        voucherDate <= toDate).ToList();
                if (voucherNo != null && voucherNo.Count > 0)
                {
                    voucherNumber = voucherNo.Max(e => e.VoucherNo);
                }
                voucherNumber = voucherNumber > 0 ? voucherNumber + 1 : 1;
            }
            else if (voucherFrequency.Equals("M")) //Monthly
            {
                //Farhat Ullah
                voucherNumber = db.VoucherMaster.Where(
                    x =>
                    x.CompanyCode == companyCode && x.BranchCode == branchCode &&
                    x.VoucherDate.Month == voucherDate.Month && x.VoucherDate.Year == voucherDate.Year)
                                .Max(x => x.VoucherNo);
                voucherNumber = voucherNumber > 0 ? voucherNumber + 1 : 1;
            }
            else //Continuously
            {
                voucherNumber++;
            }
            return(voucherNumber);
        }
Beispiel #26
0
        public IList <Object> SelectCodes(int CompanyCode, int BranchCode, string AccountType)
        {
            SmsMis.Models.Console.Handlers.Admin.SmsMisDB db = new SmsMis.Models.Console.Handlers.Admin.SmsMisDB();
            var otherItems = (from st in db.ChartOfAccounts
                              from rate in db.COABranch.Where(b => b.CompanyCode == st.CompanyCode && b.AccountCode == st.AccountCode).DefaultIfEmpty()
                              select new
            {
                BranchCode = (int?)rate.BranchCode,
                AccountCode = st.AccountCode,
                AccountTitle = st.AccountTitle,
                AccountType = st.AccountType,
                LevelId = st.LevelId,
                CompanyCode = st.CompanyCode,
                Status = st.Status
            }).Where(s => s.CompanyCode == CompanyCode && s.AccountType == AccountType).ToArray();

            return(otherItems.Where(z => z.BranchCode == null || z.BranchCode == BranchCode).ToArray());
        }
Beispiel #27
0
        public IList <object> SelectAll(int CompanyCode)
        {
            SmsMis.Models.Console.Handlers.Admin.SmsMisDB db = new SmsMis.Models.Console.Handlers.Admin.SmsMisDB();
            var otherItems = (from p in db.COAGroup
                              from e in db.ChartOfAccounts.Where(e => e.CompanyCode == p.CompanyCode && e.AccountCode == p.AccountCode).DefaultIfEmpty()
                              select new
            {
                CompanyCode = p.CompanyCode,
                AccountCode = p.AccountCode,
                AccountTitle = e.AccountTitle,
                Code = p.Code,
                Description = p.Description,
                ShortName = p.ShortName
            }).Where(a => a.CompanyCode == CompanyCode).ToArray();

            return(otherItems);
            //return db.COAGroup.Where(a => a.CompanyCode == CompanyCode).ToList();
        }
Beispiel #28
0
        public IList <object> SelectAll(int CompanyCode)
        {
            SmsMis.Models.Console.Handlers.Admin.SmsMisDB db = new SmsMis.Models.Console.Handlers.Admin.SmsMisDB();
            var otherItems = (from p in db.VoucherTypeBranch
                              from e in db.ChartOfAccounts.Where(e => e.CompanyCode == p.CompanyCode && e.AccountCode == p.AccountCode).DefaultIfEmpty()
                              //from rate in db.COABranch.Where(b => b.CompanyCode == p.CompanyCode && b.AccountCode == p.AccountCode).DefaultIfEmpty()
                              select new
            {
                CompanyCode = p.CompanyCode,
                BranchCode = p.BranchCode,
                VoucherCode = p.VoucherCode,
                Status = p.Status,
                AccountCode = p.AccountCode,
                AccountTitle = e.AccountTitle
            }).Where(a => a.CompanyCode == CompanyCode).ToArray();

            return(otherItems);//.Where(z => z.BranchCode == null || z.BranchCode == BranchCode).ToArray();
            //return otherItems;
            //return db.VoucherTypeBranch.Where(a => a.CompanyCode == CompanyCode).ToList();
        }
Beispiel #29
0
        public IList <object> SelectAll()
        {
            SmsMis.Models.Console.Handlers.Admin.SmsMisDB db = new SmsMis.Models.Console.Handlers.Admin.SmsMisDB();
            //return db.Student.ToList();

            var otherItems = (from br in db.Student
                              select new
            {
                CompanyCode = br.CompanyCode,
                BranchCode = br.BranchCode,
                SessionCode = br.SessionCode,
                FirstName = br.FirstName,
                FullName = br.FullName,
                StudentNo = br.StudentNo
            })
                             .OrderBy(opr => opr.StudentNo)
                             .ToArray();

            return(otherItems);
        }
        public DataTable getAllStudentClassCourseSubjects()
        {
            DataTable dt              = new DataTable();
            var       context         = new SmsMisDB();
            var       conn            = context.Database.Connection;
            var       connectionState = conn.State;

            try
            {
                using (context)
                {
                    if (connectionState != ConnectionState.Open)
                    {
                        conn.Open();
                    }
                    using (var cmd = conn.CreateCommand())
                    {
                        //cmd.CommandText = "Select distinct css.CompanyCode,css.BranchCode,css.ClassCode,css.CourseCode,css.SubjectCode,co.CourseName,su.SubjectName,cl.ClassName,css.Mandatory,ss.StudentNo from ClassCourseSubject css with(nolock) inner join Course co with(nolock) on css.CourseCode = co.CourseCode and css.CompanyCode = co.CompanyCode inner join [Subject] su with(nolock) on css.SubjectCode = su.SubjectCode inner join Class cl with(nolock) on cl.ClassCode = css.ClassCode and cl.CompanyCode = css.CompanyCode left outer join StudentSubject ss with(nolock) on ss.CompanyCode =css.CompanyCode and ss.ClassCode =cl.ClassCode and ss.SubjectCode=su.SubjectCode";
                        cmd.CommandText = "Select distinct css.CompanyCode,css.BranchCode,css.ClassCode,css.CourseCode,css.SubjectCode,co.CourseName,su.SubjectName,cl.ClassName,css.Mandatory from ClassCourseSubject css with(nolock) inner join Course co with(nolock) on css.CourseCode = co.CourseCode and css.CompanyCode = co.CompanyCode inner join [Subject] su with(nolock) on css.SubjectCode = su.SubjectCode inner join Class cl with(nolock) on cl.ClassCode = css.ClassCode and cl.CompanyCode = css.CompanyCode";
                        cmd.CommandType = CommandType.Text;
                        //cmd.Parameters.Add(new SqlParameter("jobCardId", 100525));
                        using (var reader = cmd.ExecuteReader())
                        {
                            dt.Load(reader);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (connectionState != ConnectionState.Open)
                {
                    conn.Close();
                }
            }
            return(dt);
        }