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(); } } }
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); }
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; } }
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; } }
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; } }
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; } }
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; } }
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; } }
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; } }
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); }
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()); }
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); }
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); } }
//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); }
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; } }
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; } }
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(); }
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(); }
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); }
//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); }
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()); }
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(); }
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(); }
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); }