public List <AccountParty> GetAccounts()
{
List <AccountParty> accounts = new List <AccountParty>();
AccountParty account = null;
DBSite site = new DBSite();
string qry = " SELECT AccountMasterId, AccountName"
+ " FROM tblAccountMaster "
+ " WHERE AccountName is not NULL AND UserId = " + Util_BLL.User.UserId
+ " ORDER BY AccountName";
DataTable dt = site.ExecuteSelect(qry);
foreach (DataRow row in dt.Rows)
{
account = new AccountParty();
account.AccountId = util.CheckNullInt(row["AccountMasterId"]);
account.AccountName = util.CheckNull(row["AccountName"]);
accounts.Add(account);
}
return(accounts);
}
public List <SubuserEntity> GetSubusers(DBSite site, int userId, string subuser_ids = "")
{
List <SubuserEntity> subuser_list = new List <SubuserEntity>();
SubuserEntity subuser = null;
string qry = "SELECT "
+ "SubuserId"
+ ", SubuserName"
+ ", SubuserCreationDate"
+ ", SubuserPassword"
+ ", EmailId"
+ ", Designation"
+ ", Address"
+ ", City"
+ ", Mobile"
+ ", Active"
+ ", UserId"
+ " FROM tblSubuser "
+ " WHERE userId=" + userId;
if (subuser_ids != "")
{
qry += " AND SubuserId IN ( " + subuser_ids + " )";
}
DataTable dt = site.ExecuteSelect(qry);
foreach (DataRow row in dt.Rows)
{
subuser = new SubuserEntity();
subuser.SubuserId = util.CheckNullInt(row["SubuserId"]);
subuser.SubuserName = util.CheckNull(row["SubuserName"]);
DateTime date = Convert.ToDateTime(row["SubuserCreationDate"]);
subuser.CreationDate = date.ToShortDateString();
subuser.Password = util.CheckNull(row["SubuserPassword"]);
subuser.EmailId = util.CheckNull(row["EmailId"]);
subuser.Designation = util.CheckNull(row["Designation"]);
subuser.Address = util.CheckNull(row["Address"]);
subuser.City = util.CheckNull(row["City"]);
subuser.Mobile = util.CheckNull(row["Mobile"]);
subuser.UserState = util.CheckNullInt(row["Active"]) == 1 ? "Yes" : "No";
subuser.Permissions = GetPermissions(site, subuser.SubuserId + "");
subuser.TeacherClasses = GetTeacherClasses(site, subuser.SubuserId + "");
subuser.TeacherSubjects = GetTeacherSubjects(site, subuser.SubuserId + "");
subuser_list.Add(subuser);
}
return(subuser_list);
}
public List <ExamMasterEntity> GetExamList(DBSite site, int userId, string id = "")
{
List <ExamMasterEntity> examList = new List <ExamMasterEntity>();
string qry = "";
qry += "SELECT "
+ " ExamMasterId, ExamDate, ExamName, ExamCode, MaxMarks, PassMarks "
+ ", TermId, ClassMasterID, SectionMasterID, ExamOrder, IsFormula, UserID, FYear "
+ " FROM tblExamMaster em "
+ " WHERE em.UserId = " + Util_BLL.User.UserId
+ " AND isFormula = 0 ";
if (id != string.Empty)
{
qry += "AND ExamMasterId = " + id;
}
qry += " ORDER BY ExamOrder ";
DataTable dt = site.ExecuteSelect(qry);
ExamMasterEntity exam;
foreach (DataRow dr in dt.Rows)
{
exam = new ExamMasterEntity();
exam.ExamMasterId = util.CheckNullInt(dr["ExamMasterId"]);
exam.ExamDate = util.CheckNullDate(dr["ExamDate"]);
exam.ExamName = util.CheckNull(dr["ExamName"]);
exam.ExamCode = util.CheckNull(dr["ExamCode"]);
exam.TermId = util.CheckNullInt(dr["TermId"]);
exam.ClassMasterID = util.CheckNullInt(dr["ClassMasterID"]);
exam.SectionMasterID = util.CheckNullInt(dr["SectionMasterID"]);
exam.MaxMarks = util.CheckNullInt(dr["MaxMarks"]);
exam.PassMarks = util.CheckNullInt(dr["PassMarks"]);
exam.ExamOrder = util.CheckNullInt(dr["ExamOrder"]);
exam.IsFormula = util.CheckNullInt(dr["IsFormula"]);
exam.UserID = util.CheckNullInt(dr["UserID"]);
exam.FYear = util.CheckNullInt(dr["FYear"]);
examList.Add(exam);
}
return(examList);
}
public List <ClassMasterEntity> GetClassListBySubUser(DBSite site, int userId, string id, bool BySubUser)
{
List <ClassMasterEntity> classList = new List <ClassMasterEntity>();
string qry = "";
if (BySubUser)
{
qry = " SELECT ClassMasterID, ClassName, ClassOrder, cl.UserID, cl.FYear "
+ " FROM tblClassMaster cl "
+ " INNER JOIN tblTeacherClasses tc ON tc.classId = cl.classMasterID "
+ " AND tc.userID = cl.userID AND tc.subuserID =" + Util_BLL.SubUser.SubuserId;
}
else
{
qry = " SELECT ClassMasterID, ClassName, ClassOrder, UserID, FYear "
+ " FROM tblClassMaster cl ";
}
qry += " WHERE cl.UserId = " + Util_BLL.User.UserId;
if (id != string.Empty)
{
qry += "AND ClassMasterID = " + id;
}
qry += " ORDER BY ClassOrder ";
DataTable dt = site.ExecuteSelect(qry);
ClassMasterEntity cls;
foreach (DataRow dr in dt.Rows)
{
cls = new ClassMasterEntity();
cls.ClassMasterId = util.CheckNullInt(dr["ClassMasterID"].ToString());
cls.ClassName = util.CheckNull(dr["ClassName"]);
cls.ClassOrder = util.CheckNullInt(dr["ClassOrder"]);
cls.UserID = util.CheckNullInt(dr["UserID"]);
cls.FYear = util.CheckNullInt(dr["FYear"]);
classList.Add(cls);
}
return(classList);
}
protected void btnPost_Click(object sender, EventArgs e)
{
AttendanceBll aBll = new AttendanceBll();
site = new DBSite();
List <AttendanceBll.AttendanceEntity> attdList = new List <AttendanceBll.AttendanceEntity>();
AttendanceBll.AttendanceEntity atd;
foreach (GridViewRow gr in gridView1.Rows)
{
atd = new AttendanceBll.AttendanceEntity();
CheckBox cb = (CheckBox)gr.Cells[0].FindControl("ckbPresent");
if (cb.Checked)
{
atd.Status = 1;
}
else
{
atd.Status = 0;
}
atd.AttendanceId = util.CheckNullInt(gr.Cells[2].Text);
attdList.Add(atd);
}
aBll.PostAttendance(site, attdList);
}
public int GetLocationId(DBSite site, string locationName)
{
int locationId = -1;
string qry = "SELECT LocationId FROM tblLocation "
+ " WHERE locationName = '" + locationName + "'"
+ " AND userId= " + Util_BLL.User.UserId;
DataTable dt = site.ExecuteSelect(qry);
foreach (DataRow r in dt.Rows)
{
locationId = util.CheckNullInt(r["LocationId"]);
}
return(locationId);
}
public List <SMSMasterEntity> GetSMSsentList(string fromDt, string toDt)
{
List <SMSMasterEntity> smsList = new List <SMSMasterEntity>();
if (fromDt == null)
{
fromDt = "1-Jan-17";
}
if (toDt == null)
{
toDt = DateTime.Today.ToString();
}
string qry = "";
qry += "SELECT "
+ " smsText, MobileNo, CreatedAt, MsgCount, UserID "
+ " FROM tblSMSsent ss "
+ " WHERE ss.UserId = " + Util_BLL.User.UserId
+ " AND createdAt BETWEEN '" + fromDt + " ' AND '" + toDt + "'";
qry += " ORDER BY createdAt ";
DBSite site = new DBSite();
DataTable dt = site.ExecuteSelect(qry);
SMSMasterEntity sms;
foreach (DataRow dr in dt.Rows)
{
sms = new SMSMasterEntity();
sms.SMSText = util.CheckNull(dr["smsText"]);
sms.MobileNo = util.CheckNull(dr["MobileNo"]);
sms.SentDate = util.CheckNull(dr["CreatedAt"]);
sms.SMSCount = util.CheckNullInt(dr["MsgCount"]);
sms.UserId = util.CheckNullInt(dr["UserID"]);
smsList.Add(sms);
}
return(smsList);
}
public List <SectionMasterEntity> GetSectionList(DBSite site, int userId, string id = "")
{
List <SectionMasterEntity> sectionList = new List <SectionMasterEntity>();
string qry = "";
qry += "SELECT ";
qry += " SectionMasterID, SectionName, SectionOrder, UserID, FYear ";
qry += " FROM tblSectionMaster sec ";
qry += " WHERE sec.UserId = " + Util_BLL.User.UserId;
if (id != string.Empty)
{
qry += "AND SectionMasterID = " + id;
}
qry += " ORDER BY SectionOrder ";
DataTable dt = site.ExecuteSelect(qry);
SectionMasterEntity sec;
foreach (DataRow dr in dt.Rows)
{
sec = new SectionMasterEntity();
sec.SectionMasterId = util.CheckNullInt(dr["SectionMasterID"].ToString());
sec.SectionName = util.CheckNull(dr["SectionName"]);
sec.SectionOrder = util.CheckNullInt(dr["SectionOrder"]);
sec.UserID = util.CheckNullInt(dr["UserID"]);
sec.FYear = util.CheckNullInt(dr["FYear"]);
sectionList.Add(sec);
}
return(sectionList);
}
public List <TermMasterEntity> GetTermList(DBSite site, int userId, string id = "")
{
List <TermMasterEntity> termList = new List <TermMasterEntity>();
string qry = "";
qry += "SELECT "
+ " TermId, TermName, TermStartDate, TermEndDate, UserID, FYear "
+ " FROM tblTerm tm "
+ " WHERE tm.UserId = " + Util_BLL.User.UserId
+ " ";
if (id != string.Empty)
{
qry += "AND TermId = " + id;
}
//qry += " ORDER BY TermStartDate ";
DataTable dt = site.ExecuteSelect(qry);
TermMasterEntity term;
foreach (DataRow dr in dt.Rows)
{
term = new TermMasterEntity();
term.TermMasterId = util.CheckNullInt(dr["TermId"]);
term.TermName = util.CheckNull(dr["TermName"]);
term.TermStartDate = util.CheckNullDate(dr["TermStartDate"]);
term.TermEndDate = util.CheckNullDate(dr["TermEndDate"]);
term.UserID = util.CheckNullInt(dr["UserID"]);
term.FYear = util.CheckNullInt(dr["FYear"]);
termList.Add(term);
}
return(termList);
}
public List <StudenEntity> SearchStudents(DBSite site, string student__class_names = "")
{
string ids = "";
string qry = " SELECT studentMasterID, StudentName FROM tblStudentMaster ";
qry += " WHERE UserID = " + Util_BLL.User.UserId;
qry += " AND ( ";
string[] student_arr = student__class_names.Split(',');
string where_condition = "";
foreach (string str in student_arr)
{
where_condition += " OR StudentName LIKE '" + str.Trim() + "%' ";
}
if (where_condition.Length > 3)
{
where_condition = where_condition.Substring(3, where_condition.Length - 4);
}
qry += where_condition + " )";
DataTable dt = site.ExecuteSelect(qry);
foreach (DataRow dr in dt.Rows)
{
ids += ", " + util.CheckNullInt(dr["StudentMasterId"]);
}
if (ids.Length > 2)
{
ids = ids.Substring(2, ids.Length - 2);
}
return(GetStudentsByClass(site, ids, "ALL"));
}
public List <SubjectGroupMasterEntity> GetSubjectGroup(DBSite site, int userId, string id = "")
{
List <SubjectGroupMasterEntity> groups = new List <SubjectGroupMasterEntity>();
string qry = "";
qry += "SELECT ";
qry += " SubjectGroupMasterId, SubjectGroupName, SubjectGroupType, UserID, FYear ";
qry += " FROM tblSubjectGroupMaster sgm ";
qry += " WHERE sgm.UserId = " + Util_BLL.User.UserId;
if (id != string.Empty)
{
qry += "AND SubjectGroupMasterId = " + id;
}
qry += " ORDER BY SubjectGroupName ";
DataTable dt = site.ExecuteSelect(qry);
SubjectGroupMasterEntity sgrp;
foreach (DataRow dr in dt.Rows)
{
sgrp = new SubjectGroupMasterEntity();
sgrp.SubjectGroupMasterId = util.CheckNullInt(dr["SubjectGroupMasterId"].ToString());
sgrp.SubjectGroupName = util.CheckNull(dr["SubjectGroupName"]);
sgrp.SubjectGroupType = util.CheckNull(dr["SubjectGroupType"]);
sgrp.UserID = util.CheckNullInt(dr["UserID"]);
sgrp.FYear = util.CheckNullInt(dr["FYear"]);
groups.Add(sgrp);
}
return(groups);
}
public List <StockTransactionType> GetInfo(DBSite site, string trn_id = "")
{
List <StockTransactionType> trn_list = new List <StockTransactionType>();
StockTransactionType trn = null;
string qry = " SELECT "
+ "TransactionId "
+ ", TransactionName "
+ ", Flow"
+ " FROM tblStockTransactionMaster"
+ " WHERE "
+ " UserId =" + Util_BLL.User.UserId
+ " AND FYear=" + Util_BLL.User.fYear;
if (trn_id != "")
{
qry += " AND TransactionId=" + trn_id;
}
DataTable dt = site.ExecuteSelect(qry);
StoreBll store = new StoreBll();
foreach (DataRow row in dt.Rows)
{
trn = new StockTransactionType();
trn.StockTransactionId = util.CheckNullInt(row["TransactionId"]);
trn.StockTransactionName = util.CheckNull(row["TransactionName"]);
trn.StockTransactionFlow = util.CheckNullInt(row["Flow"]);
trn.StockTransactionFlowName = store.stock_transaction_type[trn.StockTransactionFlow];
trn_list.Add(trn);
}
return(trn_list);
}
// ------------ Get Tax Info -------------------------------------
public List <TaxEntity> GetInfo(DBSite site, string tax_id = "")
{
List <TaxEntity> tax_list = new List <TaxEntity>();
TaxEntity tax = null;
string qry = " SELECT "
+ "TaxId"
+ ", TaxName"
+ ", TaxPercent"
+ ", TaxAmount"
+ " FROM tblTaxMaster"
+ " WHERE "
+ " UserId =" + Util_BLL.User.UserId
+ " AND FYear=" + Util_BLL.User.fYear;
if (tax_id != "")
{
qry += " AND TaxId=" + tax_id;
}
DataTable dt = site.ExecuteSelect(qry);
foreach (DataRow row in dt.Rows)
{
tax = new TaxEntity();
tax.TaxId = util.CheckNullInt(row["TaxId"]);
tax.TaxName = util.CheckNull(row["TaxName"]);
tax.TaxPercent = util.CheckNullDecimal(row["TaxPercent"]);
tax.TaxAmount = util.CheckNullDecimal(row["TaxAmount"]);
tax_list.Add(tax);
}
return(tax_list);
}
public List <SalaryEntity> GetSalaries(DBSite site, int salaryID = -1)
{
List <SalaryEntity> salList = new List <SalaryEntity>();
string qry = "";
qry += " SELECT SalaryID, sa.DesignationID, Designation, YearNo, MonthNo, BasicSalary ";
qry += " , conveyance, EPF, ESI, EMI, StaffID, st.StudentName ";
qry += " , sa.userID, sa.FYear ";
qry += " FROM tblSalary as sa "
+ " LEFT OUTER JOIN tblStudentMaster st on st.StudentMasterID = sa.staffID "
+ " LEFT OUTER JOIN tblDesignation d ON d.designationId = sa.designationId "
+ " WHERE sa.UserID = " + Util_BLL.User.UserId;
if (salaryID != -1)
{
qry += " AND SalaryID = " + salaryID;
}
else
{
qry += " AND SalaryId IN (SELECT MAX(SalaryID) FROM tblSalary GROUP BY StaffID) ";
}
DataTable dt = site.ExecuteSelect(qry);
SalaryEntity sal;
foreach (DataRow dr in dt.Rows)
{
sal = new SalaryEntity();
sal.SalaryId = util.CheckNullInt(dr["SalaryID"]);
sal.StaffId = util.CheckNullInt(dr["StaffId"]);
sal.StaffName = util.CheckNull(dr["StudentName"]);
sal.DesignationId = util.CheckNullInt(dr["DesignationId"]);
sal.Designation = util.CheckNull(dr["Designation"]);
sal.YearNo = util.CheckNullInt(dr["YearNo"]);
sal.MonthNo = util.CheckNullInt(dr["MonthNo"]);
sal.BasicSalary = util.CheckNullDouble(dr["BasicSalary"]);
sal.Conveyance = util.CheckNullDouble(dr["Conveyance"]);
sal.epf = util.CheckNullDouble(dr["epf"]);
sal.esi = util.CheckNullDouble(dr["esi"]);
sal.emi = util.CheckNullDouble(dr["emi"]);
sal.UserID = util.CheckNullInt(dr["UserID"]);
sal.FYear = util.CheckNullInt(dr["FYear"]);
salList.Add(sal);
}
return(salList);
}
void SaveExam()
{
exmBll = new ExamMasterBll();
ExamMasterBll.ExamMasterEntity exam = new ExamMasterBll.ExamMasterEntity();
try
{
string message = "";
exam.ExamName = this.txtExamName.Text;
exam.ExamCode = this.txtExamCode.Text;
exam.ExamDate = util.CheckNullDate(DateTime.Today);
exam.ClassMasterID = util.CheckNullInt(ddlClass.SelectedValue);
exam.SectionMasterID = util.CheckNullInt(ddlSection.SelectedValue);
exam.TermId = util.CheckNullInt(ddlTerm.SelectedValue);
exam.MaxMarks = util.CheckNullInt(txtMaxMarks.Text);
exam.PassMarks = util.CheckNullInt(txtPassMarks.Text);
exam.ExamOrder = util.CheckNullInt(txtExamOrder.Text);
site = new DBSite();
// save or updare
site.BeginTrans();
if (lblExamMasterId.Text == "New")
{
exmBll.SaveExam(site, exam);
message = "\t '" + exam.ExamName + "' Successfully Added !";
}
else
{
exam.ExamMasterId = util.CheckNullInt(lblExamMasterId.Text);
exmBll.UpdateExam(site, exam);
message = "\t '" + exam.ExamName + "' Successfully Updated !";
}
site.Commit();
lblMessage.Text = message;
lblMessage.Visible = true;
ClearErrorMessage();
}
catch (Exception ex)
{
site.RollBack();
if (ex.Message.Contains("UNQ_ExamName"))
{
lblMessage.Text = "\t '" + exam.ExamName + "' is already present !";
lblMessage.Visible = true;
}
else
{
util.HandleExpception(ex, Response);
}
}
finally
{
site.Close();
}
}
protected void btnSendSMSToAbsentees_Click(object sender, EventArgs e)
{
Util_BLL util = new Util_BLL();
AttendanceBll abll = new AttendanceBll();
site = new DBSite();
List <AttendanceBll.AttendanceEntity> attdList = new List <AttendanceBll.AttendanceEntity>();
AttendanceBll.AttendanceEntity atd;
foreach (GridViewRow gr in gridView1.Rows)
{
atd = new AttendanceBll.AttendanceEntity();
CheckBox cb = (CheckBox)gr.Cells[0].FindControl("ckbPresent");
if (!cb.Checked)
{
atd.AttendanceId = util.CheckNullInt(gr.Cells[2].Text);
string msg = "Dear Parent, Your Child Name <40> is absent today.";
msg = msg.Replace("<40>", util.CheckNull(gr.Cells[3].Text));
// 5cs School, Lalpur
if (Util_BLL.User.SenderId == "FifthC")
{
msg = "Dear Parents, Your ward '<40>' is absent today from School.";
msg = msg.Replace("<40>", util.CheckNull(gr.Cells[3].Text));
}
// if not class is Staff then do not send sms
// and not sms has been sent already
string mobileNo = abll.GetMobileNo(site, atd.AttendanceId);
string ss = util.SendSms(site, mobileNo, msg, Util_BLL.User, this.Response, false);
int sms_count = 0;
try
{
site.BeginTrans();
sms_count = util.SaveSentSMSToDB(site, mobileNo, msg, false, Util_BLL.User);
site.Commit();
}
catch (Exception ex)
{
site.RollBack();
}
if (abll.GetClassName(site, atd.AttendanceId).ToUpper() != "STAFF" && !abll.IsSMSSent(site, atd.AttendanceId))
{
//string ss = util.SendSms(abll.GetMobileNo(site, atd.AttendanceId), msg, Util_BLL.User, this.Response);
}
attdList.Add(atd);
}
}
abll.PostSMS(site, attdList);
}
protected void SaveStudent()
{
StudentBll st_bll = null;
st_bll = new StudentBll();
StudentBll.StudenEntity student = new StudentBll.StudenEntity();
try
{
site = new DBSite();
site.BeginTrans();
student.StudentName = txtStudentName.Text;
student.AdmNo = txtAdmNo.Text;
student.PresentClass = util.CheckNullInt(ddlPresentClass.SelectedValue);
student.SectionId = util.CheckNullInt(ddlSection.SelectedValue);
student.IdCardNo = txtIdCardNo.Text;
student.FatherName = txtFatherName.Text;
student.MotherName = txtMotherName.Text;
student.MobileNoF = txtMobileNoF.Text;
student.MobileNoM = txtMobileNoM.Text;
student.DOB = txtDOB.Text;
if (txtMobileNoF.Text.Length != 10)
{
lblError.Text = "Mobile Number should be of 10 digits";
lblError.Visible = true;
txtMobileNoF.Focus();
ClearSuccessMessage();
return;
}
student.Email = txtDOB.Text;
string message = "";
if (this.lblStudentMasterId.Text == "New")
{
//if (IsDuplicateCardNo(site, student.IdCardNo, -1))
//{
// txtIdCardNo.Focus();
// isErr = true;
// return;
//}
st_bll.SaveStudent(site, student);
message = " Successfully Added !";
}
else
{
student.StudentMasterId = util.CheckNullInt(lblStudentMasterId.Text);
//if (IsDuplicateCardNo(site, student.IdCardNo, student.StudentMasterId))
//{
// txtIdCardNo.Focus();
// isErr = true;
// return;
//}
st_bll.UpdateStudent(site, student);
message = " Successfully Updated !";
}
site.Commit();
lblMessage.Text = message;
lblMessage.Visible = true;
isErr = false;
ClearErrorMessage();
}
catch (Exception ex)
{
site.RollBack();
util.HandleExpception(ex, Response);
}
finally
{
site.Close();
}
}
public void SaveUserInfo(DBSite site, User user)
{
string qry = " INSERT INTO tblUserInfo("
+ " UserName "
+ ", Email"
+ ", UserPassword"
+ ", CreationDate"
+ ", AmountPaid"
+ ", StartDate"
+ ", EndDate"
+ ", Address"
+ ", City"
+ ", Country"
+ ", Phone"
+ ", Mobile"
+ ", TinNumber"
+ ", SalesTaxNumber"
+ ", CSTNumber"
+ ", UserType"
+ ", NumberOfSubusers"
+ ", Remarks"
+ ", FYear"
+ " )";
qry += " VALUES( '" + user.UserName + "'"
+ ", '" + user.EmailId + "'"
+ ", '" + user.UserPassword + "'"
+ ", '" + user.CreationDate + "'"
+ ", " + user.AmountPaid
+ ", '" + user.StartDate + "'"
+ ", '" + user.EndDate + "'"
+ ", '" + user.Address + "'"
+ ", '" + user.City + "'"
+ ", '" + user.Country + "'"
+ ", '" + user.Phone + "'"
+ ", '" + user.Mobile + "'"
+ ", '" + user.TinNumber + "'"
+ ", '" + user.SalesTaxNumber + "'"
+ ", '" + user.CSTNumber + "'"
+ ", " + user.UserType
+ ", " + user.NumberOFSubusers
+ ", '" + user.Remarks + "'"
+ ", " + user.fYear
+ ")";
site.Execute(qry);
//------ Get User Id Just Inbserted ------------------------
qry = " SELECT UserInfoId FROM tblUserInfo WHERE Email='" + user.EmailId + "'";
user.UserId = util.CheckNullInt(site.ExecuteSelect(qry).Rows[0]["UserInfoId"]); //----- get user id query ----------
foreach (Permission permission in user.Permissions)
{
qry = " INSERT INTO tblUserPermissions"
+ "("
+ "UserId"
+ ", PermissionId"
+ ")"
+ " VALUES("
+ user.UserId
+ ", "
+ permission.PermissionId
+ ")";
site.Execute(qry);
}
}
//select p1.BillDate, p1.productLedgerNumber, pm1.ProductName, p1.soldQty, p1.BoughtQty, p2.productID ProductToId , pm2.ProductName
// , p2.BoughtQty, p2.SoldQty, p2.LocationId, p2.AccountID, AccountName
// , LocationName
// from tblProductLedger p1
// INNER JOIN tblProductLedger p2 ON p1.productLedgerNumber = p2.productLedgerNumber
// LEFT OUTER JOIN tblProductMaster PM1 ON pm1.productMasterId = p1.productID
// LEFT OUTER JOIN tblProductMaster PM2 ON pm2.productMasterId = p2.productID
// LEFT OUTER JOIN tblAccountMaster AM ON am.accountMasterID = p2.accountID
// LEFT OUTER JOIN tblLocation L on l.locationId = p2.LocationID
// where
// --p1.productLedgerNumber = 1057
// --and
// p1.productId = 3
// --and p2.productID <> 3
// ORDER BY BillDate, p1.productLedgerNumber
public int GetFlow(DBSite site, int trnTypeId)
{
int flow = 0;
string qry = " SELECT Flow FROM tblStockTransactionMaster WHERE TransactionId = " + trnTypeId;
DataTable dt = site.ExecuteSelect(qry);
foreach (DataRow row in dt.Rows)
{
flow = util.CheckNullInt(row["Flow"]);
}
return(flow);
}
public List <AttendanceByMonthEntity> GetAttendanceByMonth(int yearNo, int monthNo, int SchoolClassId)
{
List <AttendanceByMonthEntity> attendance_list = new List <AttendanceByMonthEntity>();
AttendanceByMonthEntity abm;
string qry = "SELECT * FROM vwAttendanceByMonth "
+ Util_BLL.GetUserWhereCondition(Util_BLL.User)
+ " AND YearNo = " + yearNo
+ " AND MonthNo = " + monthNo;
if (SchoolClassId != -1)
{
qry += " AND ClassMasterId = " + SchoolClassId;
}
DBSite site2 = new DBSite();
DataTable dt = site2.ExecuteSelect(qry);
foreach (DataRow dr in dt.Rows)
{
abm = new AttendanceByMonthEntity();
abm.StudentId = util.CheckNullInt(dr["StudentMasterID"]);
abm.StudentName = util.CheckNull(dr["StudentName"]);
abm.ClassId = util.CheckNullInt(dr["ClassMasterId"]);
abm.ClassName = util.CheckNull(dr["ClassName"]);
abm.YearNo = util.CheckNullInt(dr["YearNo"]);
abm.MonthNo = util.CheckNullInt(dr["MonthNo"]);
abm.Day1 = util.CheckNull(dr["Day1"]);
abm.Day2 = util.CheckNull(dr["Day2"]);
abm.Day3 = util.CheckNull(dr["Day3"]);
abm.Day4 = util.CheckNull(dr["Day4"]);
abm.Day5 = util.CheckNull(dr["Day5"]);
abm.Day6 = util.CheckNull(dr["Day6"]);
abm.Day7 = util.CheckNull(dr["Day7"]);
abm.Day8 = util.CheckNull(dr["Day8"]);
abm.Day9 = util.CheckNull(dr["Day9"]);
abm.Day10 = util.CheckNull(dr["Day10"]);
abm.Day11 = util.CheckNull(dr["Day11"]);
abm.Day12 = util.CheckNull(dr["Day12"]);
abm.Day13 = util.CheckNull(dr["Day13"]);
abm.Day14 = util.CheckNull(dr["Day14"]);
abm.Day15 = util.CheckNull(dr["Day15"]);
abm.Day16 = util.CheckNull(dr["Day16"]);
abm.Day17 = util.CheckNull(dr["Day17"]);
abm.Day18 = util.CheckNull(dr["Day18"]);
abm.Day19 = util.CheckNull(dr["Day19"]);
abm.Day20 = util.CheckNull(dr["Day20"]);
abm.Day21 = util.CheckNull(dr["Day21"]);
abm.Day22 = util.CheckNull(dr["Day22"]);
abm.Day23 = util.CheckNull(dr["Day23"]);
abm.Day2 = util.CheckNull(dr["Day24"]);
abm.Day25 = util.CheckNull(dr["Day25"]);
abm.Day26 = util.CheckNull(dr["Day26"]);
abm.Day27 = util.CheckNull(dr["Day27"]);
abm.Day28 = util.CheckNull(dr["Day28"]);
abm.Day29 = util.CheckNull(dr["Day29"]);
abm.Day30 = util.CheckNull(dr["Day30"]);
abm.Day31 = util.CheckNull(dr["Day31"]);
attendance_list.Add(abm);
}
return(attendance_list);
}
public List <ProductMasterEntity> GetProductMasterData(DBSite site, string id = "")
{
List <ProductMasterEntity> productMasterList = new List <ProductMasterEntity>();
string qry = "";
qry += " SELECT ";
qry += " ProductMasterId, ProductID, ProductName, OpeningBalance, ProductDate, CostPrice, SellingPrice ";
qry += " , ReOrderQty , ProductDescription, product.UserID, FYear, product.CreatedAt ";
qry += ", category.CategoryId CategoryId ";
qry += ", CategoryName ";
qry += ", uom.UOMId UOMId ";
qry += ", product.LocationId LocationId ";
qry += ", product.AccountId AccountId ";
qry += ", UnitName ";
qry += " FROM ( tblProductMaster as product ";
qry += " LEFT OUTER JOIN tblProductCategory as category ON ";
qry += " product.CategoryId = category.CategoryId )";
qry += " LEFT OUTER JOIN tblUOM as uom ON ";
qry += " product.UOM= uom.UOMId ";
qry += " WHERE product.UserID = " + Util_BLL.User.UserId;
if (id != string.Empty)
{
qry += " AND ProductMasterId = " + id;
}
DataTable dt = site.ExecuteSelect(qry);
ProductMasterEntity pme;
foreach (DataRow dr in dt.Rows)
{
pme = new ProductMasterEntity();
pme.ProductMasterId = util.CheckNullInt(dr["ProductMasterId"]);
pme.ProductId = util.CheckNull(dr["ProductID"]);
pme.ProductName = util.CheckNull(dr["ProductName"]);
pme.OpeningBalance = util.ToDecimal(util.CheckNullDecimal(dr["OpeningBalance"]));
DateTime date = Convert.ToDateTime(dr["ProductDate"]);
pme.productDate = date;
pme.CostPrice = util.ToDecimal(util.CheckNullDecimal(dr["CostPrice"]));
pme.SellingPrice = util.ToDecimal(util.CheckNullDecimal(dr["SellingPrice"]));
pme.UOM = util.CheckNullInt(dr["UOMId"]);
pme.UOMName = util.CheckNull(dr["UnitName"]);
pme.CategoryID = util.CheckNullInt(dr["CategoryID"]);
pme.CategoryName = util.CheckNull(dr["CategoryName"]);
pme.AccountId = util.CheckNullInt(dr["AccountId"]);
pme.LocationId = util.CheckNullInt(dr["LocationId"]);
pme.ReOrderQty = util.CheckNullDecimal(dr["ReOrderQty"]);
pme.ProductDescription = util.CheckNull(dr["ProductDescription"]);
pme.UserID = util.CheckNullInt(dr["UserID"]);
pme.FYear = util.CheckNullInt(dr["FYear"]);
productMasterList.Add(pme);
}
return(productMasterList);
}
protected bool isDuplicate(DBSite site, ExamMarksEntity exam)
{
bool isDuplicateRecord = true;
string qry = "SELECT COUNT(*) records FROM tblExamMarks"
+ " WHERE examID = " + exam.ExamId
+ " AND SubjectID = " + exam.SubjectId
+ " AND studentID = " + exam.StudentMasterId
+ " AND ClassID = " + exam.classId
+ " AND SectionId = " + exam.SectionId
+ " AND UserID = " + exam.UserID
+ " AND FYear = " + exam.FYear;
DataTable dt = site.ExecuteSelect(qry);
foreach (DataRow dr in dt.Rows)
{
isDuplicateRecord = (util.CheckNullInt(dr["records"])) > 0;
}
return(isDuplicateRecord);
}
// ---------------- Authenticate User ----------------------------------------------
public UserBLL.User AuthenticateUser(DBSite site, string email_id, string password, string user_id = "", int subuserId = -1)
{
UserBLL.User user = null;
UserBLL user_bll = new UserBLL();
string qry = "SELECT "
+ " UserInfoId"
+ ", UserName"
+ ", Email"
+ ", SenderID"
+ ", SMSUser"
+ ", SMSPassword"
+ ", SMSDelivery"
+ ", UserPassword"
+ ", CreationDate"
+ ", AmountPaid"
+ ", StartDate"
+ ", EndDate"
+ ", Address"
+ ", City"
+ ", Country"
+ ", Phone"
+ ", Mobile"
+ ", TinNumber"
+ ", SalesTaxNumber"
+ ", CSTNumber"
+ ", IsActive"
+ ", UserType"
+ ", IsUnicode"
+ ", Remarks"
+ ", NumberOfSubusers"
+ " FROM tblUserInfo";
if (user_id == "")
{
qry += " WHERE Email='" + email_id + "'"
+ " AND UserPassword='******'";
}
else
{
qry += " WHERE UserInfoId=" + user_id;
}
DataTable dt = site.ExecuteSelect(qry);
if (dt.Rows.Count > 0)
{
DataRow row = dt.Rows[0];
user = new UserBLL.User();
user.UserId = util.CheckNullInt(row["UserInfoID"]);
user.UserName = util.CheckNull(row["UserName"]);
user.EmailId = util.CheckNull(row["Email"]);
user.SenderId = util.CheckNull(row["SenderID"]);
user.SMSUser = util.CheckNull(row["SMSUser"]);
user.SMSPassword = util.CheckNull(row["SMSPassword"]);
user.SMSDelivery = util.CheckNullInt(row["SMSDelivery"]);
user.UserPassword = util.CheckNull(row["UserPassword"]);
user.CreationDate = Convert.ToDateTime(row["CreationDate"]).ToShortDateString();
user.AmountPaid = util.CheckNullDecimal(row["AmountPaid"]);
user.StartDate = Convert.ToDateTime(row["StartDate"]).ToShortDateString();
user.EndDate = Convert.ToDateTime(row["EndDate"]).ToShortDateString();
user.Address = util.CheckNull(row["Address"]);
user.City = util.CheckNull(row["City"]);
user.Country = util.CheckNull(row["Country"]);
user.Phone = util.CheckNull(row["Phone"]);
user.Mobile = util.CheckNull(row["Mobile"]);
user.TinNumber = util.CheckNull(row["TinNumber"]);
user.SalesTaxNumber = util.CheckNull(row["SalesTaxNumber"]);
user.CSTNumber = util.CheckNull(row["CSTNumber"]);
user.IsActive = util.CheckNull(row["IsActive"]);
user.UserType = util.CheckNullInt(row["UserType"]);
user.IsUnicode = util.CheckNullInt(row["IsUnicode"]) == 1 ? true : false;
user.Remarks = util.CheckNull(row["Remarks"]);
user.NumberOFSubusers = util.CheckNullInt(row["NumberOfSubusers"]);
user.fYear = util.CheckNullInt(util.GetHomeSetting(site, "CurrentFYear"));
user.Subusers = user_bll.GetSubusers(site, user.UserId + ""); //------- Subusers --------
user.Current_subuserId = subuserId;
user.Permissions = user_bll.GetPermissions(site, user.UserId + ""); //--------- Permissions ------------------
}
return(user);
}
public List <AccountMasterEntity> GetAccountMasterData(DBSite site, int userId, string id = "")
{
List <AccountMasterEntity> accountMasterList = new List <AccountMasterEntity>();
string qry = "";
qry += "SELECT ";
qry += "AccountMasterId, AccountName, CreationDate, OpeningBalance,";
qry += "DRCR, Address, City, Phone, Mobile, Email,";
qry += " Remarks, acc.UserID, FYear ";
qry += ", acc.GroupID GroupID";
qry += ", GroupName ";
qry += " FROM tblAccountMaster as acc ";
qry += " LEFT OUTER JOIN tblGroup as gp ON ";
qry += " acc.GroupId = gp.GroupId ";
//qry += Util_BLL.GetUserWhereCondition();
qry += " Where acc.UserId = " + Util_BLL.User.UserId;
if (id != string.Empty)
{
qry += "AND AccountMasterId = " + id;
}
DataTable dt = site.ExecuteSelect(qry);
AccountMasterEntity ac;
foreach (DataRow dr in dt.Rows)
{
ac = new AccountMasterEntity();
ac.AccountMasterId = util.CheckNullInt(dr["AccountMasterId"].ToString());
ac.AccountName = dr["AccountName"].ToString();
DateTime date = Convert.ToDateTime(dr["CreationDate"]);
ac.CreationDate = date.ToShortDateString();
ac.OpeningBalance = util.ToDecimal(util.CheckNullDecimal(dr["OpeningBalance"]));
ac.DRCR = util.CheckNullInt(dr["DRCR"]);
ac.DRCR_Name = (util.CheckNullInt(dr["DRCR"]) == 0) ? "Credit" : "Debit";
ac.Address = util.CheckNull(dr["Address"].ToString());
ac.City = util.CheckNull(dr["City"].ToString());
ac.Phone = util.CheckNull(dr["Phone"].ToString());
ac.Mobile = util.CheckNull(dr["Mobile"].ToString());
ac.Email = util.CheckNull(dr["Email"].ToString());
ac.Remarks = util.CheckNull(dr["Remarks"].ToString());
ac.GroupId = util.CheckNullInt(dr["GroupId"]);
ac.GroupName = util.CheckNull(dr["GroupName"]);
ac.UserID = util.CheckNullInt(dr["UserID"]);
ac.FYear = util.CheckNullInt(dr["FYear"]);
accountMasterList.Add(ac);
}
return(accountMasterList);
}
protected void Page_Load(object sender, EventArgs e)
{
int trnID = util.CheckNullInt(Request.QueryString["new"]);
isNewExamMarksEntry = trnID;
isUpdate = isNewExamMarksEntry != 1;
// change back gound color
if (isUpdate)
{
form1.Attributes.Add("style", "background-color:lightBlue;");
}
else
{
form1.Attributes.Add("style", "background-color:lightGrey;");
}
btnDelete.Visible = isUpdate;
if (!IsPostBack)
{
Clear();
site = new DBSite();
ClassMasterBLL cBll = new ClassMasterBLL();
List <ClassMasterBLL.ClassMasterEntity> clasList = cBll.GetClassListBySubUser(site, Util_BLL.User.UserId, "", true);
//ddlClass.Items.Add(new ListItem("All", "0"));
foreach (ClassMasterBLL.ClassMasterEntity cls in clasList)
{
ddlClass.Items.Add(new ListItem(cls.ClassName, cls.ClassMasterId.ToString()));
}
SectionMasterBll secBll = new SectionMasterBll();
List <SectionMasterBll.SectionMasterEntity> secList = secBll.GetSectionList(site, Util_BLL.User.UserId);
ddlSection.Items.Add(new ListItem("All", "0"));
foreach (SectionMasterBll.SectionMasterEntity sec in secList)
{
ddlSection.Items.Add(new ListItem(sec.SectionName, sec.SectionMasterId.ToString()));
}
ExamMasterBll exBll = new ExamMasterBll();
List <ExamMasterBll.ExamMasterEntity> examList = exBll.GetExamList(site, Util_BLL.User.UserId);
ddlExam.Items.Add(new ListItem("All", "0"));
foreach (ExamMasterBll.ExamMasterEntity exam in examList)
{
ddlExam.Items.Add(new ListItem(exam.ExamCode + " (MM." + exam.MaxMarks + ")", exam.ExamMasterId.ToString()));
}
//gridView3.DataSource = examList;
//gridView3.DataBind();
SubjectMasterBLL subBll = new SubjectMasterBLL();
List <SubjectMasterBLL.SubjectMasterEntity> subList = subBll.GetSubjectsBySubUser(site, Util_BLL.User.UserId, "", true);
ddlSubject.Items.Add(new ListItem("All", "0"));
foreach (SubjectMasterBLL.SubjectMasterEntity subject in subList)
{
ddlSubject.Items.Add(new ListItem(subject.SubjectName + " (" + subject.SubjectGroupType + ")", subject.SubjectMasterId.ToString()));
}
// 7777
//gridView2.DataSource = subList;
//gridView2.DataBind();
if (!isUpdate)
{
legendExamMarks.InnerText = "Enter New Exam Marks";
}
else
{
legendExamMarks.InnerText = "Edit Exam Marks";
}
}
}
public List <SubjectMasterEntity> GetSubjectsBySubUser(DBSite site, int userId, string id, bool BySubUser)
{
List <SubjectMasterEntity> subjects = new List <SubjectMasterEntity>();
string qry = "";
qry += "SELECT ";
qry += " SubjectMasterId, SubjectName ";
qry += " , SubjectOrder, SubjectCode";
qry += ", sub.SubjectGroupMasterId ";
qry += ", SubjectGroupName ";
qry += ", SubjectGroupType ";
qry += ", sub.SubjectClassId ";
qry += ", ISNull(ClassName, 'All') ClassName ";
qry += ", sub.SubjectSectionId ";
qry += ", IsNull(SectionName, 'All') SectionName ";
qry += " , sub.UserID, sub.FYear ";
qry += " FROM tblSubjectMaster sub ";
qry += " LEFT OUTER JOIN tblSubjectGroupMaster sgp ON ";
qry += " sub.SubjectGroupMasterId = sgp.SubjectGroupMasterId ";
qry += " LEFT OUTER JOIN tblClassMaster cm ON ";
qry += " cm.ClassMasterID = sub.SubjectClassId ";
qry += " LEFT OUTER JOIN tblSectionMaster sm ON ";
qry += " sm.SectionMasterId = sub.SubjectSectionID ";
if (BySubUser)
{
qry += " INNER JOIN tblTeacherSubjects ts ON ts.SubjectId = sub.SubjectMasterID "
+ "AND ts.UserId=sub.userID AND ts.SubUserId=" + Util_BLL.SubUser.SubuserId;
}
qry += " Where sub.UserId = " + Util_BLL.User.UserId;
if (id != string.Empty)
{
qry += "AND SubjectMasterId = " + id;
}
DataTable dt = site.ExecuteSelect(qry);
SubjectMasterEntity sub;
foreach (DataRow dr in dt.Rows)
{
sub = new SubjectMasterEntity();
sub.SubjectMasterId = util.CheckNullInt(dr["SubjectMasterId"]);
sub.SubjectName = util.CheckNull(dr["SubjectName"]);
sub.SubjectGroupID = util.CheckNullInt(dr["SubjectGroupMasterId"]);
sub.SubjectGroupName = util.CheckNull(dr["SubjectGroupName"]);
sub.SubjectGroupType = util.CheckNull(dr["SubjectGroupType"]);
sub.SubjectClassID = util.CheckNullInt(dr["SubjectClassId"]);
sub.SubjectClassName = util.CheckNull(dr["ClassName"]);
sub.SubjectSectionID = util.CheckNullInt(dr["SubjectSectionID"]);
sub.SubjectSectionName = util.CheckNull(dr["SectionName"]);
sub.SubjectCode = util.CheckNull(dr["SubjectCode"]);
sub.SubjectOrder = util.CheckNullInt(dr["SubjectOrder"]);
sub.UserID = util.CheckNullInt(dr["UserID"]);
sub.FYear = util.CheckNullInt(dr["FYear"]);
subjects.Add(sub);
}
return(subjects);
}
