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