public List <ClassMasterBLL.ClassMasterEntity> GetTeacherClasses(DBSite site, string subuser_id)
{
List <ClassMasterBLL.ClassMasterEntity> class_list = new List <ClassMasterBLL.ClassMasterEntity>();
ClassMasterBLL.ClassMasterEntity clas = null;
string qry = " SELECT "
+ " tc.classId"
+ " FROM tblTeacherClasses tc "
+ " LEFT OUTER JOIN tblClassMaster c ON c.ClassMasterID = tc.ClassID "
+ " WHERE tc.UserId=" + Util_BLL.User.UserId
+ " AND tc.SubuserId=" + subuser_id;
DataTable class_table = site.ExecuteSelect(qry);
foreach (DataRow row in class_table.Rows)
{
clas = new ClassMasterBLL.ClassMasterEntity();
clas.ClassMasterId = util.CheckNullInt(row["ClassID"]);
class_list.Add(clas);
}
return(class_list);
}
public List <SubjectMasterBLL.SubjectMasterEntity> GetTeacherSubjects(DBSite site, string subuser_id)
{
List <SubjectMasterBLL.SubjectMasterEntity> sub_list = new List <SubjectMasterBLL.SubjectMasterEntity>();
SubjectMasterBLL.SubjectMasterEntity sub = null;
string qry = " SELECT "
+ " ts.SubjectID"
+ " FROM tblTeacherSubjects ts "
+ " LEFT OUTER JOIN tblSubjectMaster s ON s.SubjectMasterID = ts.SubjectID "
+ " WHERE ts.UserId=" + Util_BLL.User.UserId
+ " AND ts.SubuserId=" + subuser_id;
DataTable dt = site.ExecuteSelect(qry);
foreach (DataRow row in dt.Rows)
{
sub = new SubjectMasterBLL.SubjectMasterEntity();
sub.SubjectMasterId = util.CheckNullInt(row["subjectId"]);
sub_list.Add(sub);
}
return(sub_list);
}
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);
}
//----- get permissions ----------------------------------------------
public List <Permission> GetPermissions(DBSite site, string subuser_id)
{
List <Permission> permission_list = new List <Permission>();
Permission permission = null;
string qry = " SELECT "
+ " p.PermissionId"
+ ", p.Module"
+ ", p.ASPXPageName"
+ " FROM tblUserPermissions as up"
+ " LEFT OUTER JOIN tblPermission as p"
+ " ON up.PermissionId = p.PermissionId"
+ " WHERE up.UserId=" + Util_BLL.User.UserId
+ " AND SubuserId=" + subuser_id;
DataTable permission_table = site.ExecuteSelect(qry);
foreach (DataRow row in permission_table.Rows)
{
permission = new Permission();
permission.PermissionId = util.CheckNullInt(row["PermissionId"]);
permission.ModuleName = util.CheckNull(row["Module"]);
permission.ASPXPageName = util.CheckNull(row["ASPXPageName"]);
permission_list.Add(permission);
}
return(permission_list);
}
public SubuserBLL.SubuserEntity AuthenticateSubuser2(DBSite site, string email_id, string password)
{
SubuserBLL.SubuserEntity sub_user = null;
string qry = "SELECT "
+ " SubuserID "
+ ", SubUserName "
+ ", EmailId "
+ ", Active "
+ ", UserId "
+ " FROM tblSubuser ";
qry += " WHERE EmailId='" + email_id + "'"
+ " AND SubuserPassword='******'";
DataTable dt = site.ExecuteSelect(qry);
if (dt.Rows.Count > 0)
{
DataRow row = dt.Rows[0];
sub_user = new SubuserBLL.SubuserEntity();
sub_user.SubuserId = util.CheckNullInt(row["SubuserID"]);
sub_user.SubuserName = util.CheckNull(row["SubUserName"]);
sub_user.EmailId = util.CheckNull(row["EmailID"]);
sub_user.UserState = util.CheckNull(row["Active"]);
sub_user.UserId = util.CheckNullInt(row["UserId"]);
}
return(sub_user);
}
public bool IsDuplicate(DBSite site, string value)
{
string qry = " SELECT UserInfoId FROM tblUserInfo"
+ " WHERE UserName='******'";
return(site.ExecuteSelect(qry).Rows.Count > 0);
}
public List <SchoolClass> GetClasses()
{
List <SchoolClass> class_list = new List <SchoolClass>();
DBSite site = new DBSite();
string qry = " SELECT "
+ " ClassMasterID"
+ ", ClassName"
+ " FROM tblClassMaster "
+ " WHERE UserId=" + Util_BLL.User.UserId
+ " ORDER By ClassOrder ";
DataTable dt = site.ExecuteSelect(qry);
SchoolClass the_class;
foreach (DataRow row in dt.Rows)
{
the_class = new SchoolClass();
the_class.SchoolClassId = util.CheckNullInt(row["ClassMasterID"]);
the_class.SchoolClassName = util.CheckNull(row["ClassName"]);
class_list.Add(the_class);
}
return(class_list);
}
public string GetMobileNos(DBSite site, List <AttendanceEntity> attdList)
{
string mobileNos = "";
string attdIds = "";
foreach (AttendanceEntity atd in attdList)
{
attdIds += atd.AttendanceId + ", ";
}
attdIds += "-1111";
string qry = "SELECT MobileF "
+ " from tblAttendance a "
+ " INNER JOIN tblStudentMaster s ON s.StudentMasterID = a.StudentMasterID "
+ " WHERE attendanceID IN (" + attdIds + ") ";
DataTable dt = site.ExecuteSelect(qry);
foreach (DataRow dr in dt.Rows)
{
mobileNos += util.CheckNull(dr["MobileF"]) + ", ";
}
return(mobileNos);
}
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 bool IsMasterBeingUsed(DBSite site, string selectedAccountType, AccountType act, int masterId)
{
string tableName = "";
string colName = AccountType.GetTypeIdName(act, selectedAccountType);
if (colName.ToUpper() == "GROUPID")
{
tableName = "tblAccountMaster";
}
else if (colName.ToUpper() == "CATEGORYID")
{
tableName = "tblProductMaster";
}
else if (colName.ToUpper() == "UOMID")
{
tableName = "tblProductMaster";
colName = "UOM";
}
else if (colName.ToUpper() == "LOCATIONID")
{
tableName = "tblProductLedger";
}
string qry = " SELECT " + colName + " FROM " + tableName + " ";
qry += Util_BLL.GetUserWhereCondition(Util_BLL.User);
qry += " AND " + colName + " = '" + masterId + "'";
DataTable dt = null;
dt = site.ExecuteSelect(qry);
return(dt.Rows.Count > 0);
}
public List <Location> GetLocations()
{
List <Location> locations = new List <Location>();
Location location = null;
DBSite site = new DBSite();
string qry = " SELECT locationId, LocationName "
+ " FROM tblLocation "
+ " WHERE locationName is not NULL AND UserId = " + Util_BLL.User.UserId
+ " ORDER BY locationName";
DataTable dt = site.ExecuteSelect(qry);
foreach (DataRow row in dt.Rows)
{
location = new Location();
location.LocationId = util.CheckNullInt(row["LocationId"]);
location.LocationName = util.CheckNull(row["LocationName"]);
locations.Add(location);
}
return(locations);
}
//---------------- Get Query For Sale Detail -------------------------------
//protected string GetQueryForSimpleFields()
//{
// string qry = "SELECT DISTINCT";
// qry += " LedgerId, LDate, AccountName + '|' + CONVERT( VARCHAR, acc.AccountMasterId) as AccountName , ";
// qry+=" Particulars, Debit, BillNumber, ldg.SourceId, ldg.Discount ";
// qry += " FROM ( tblLedger as ldg ";
// qry+="LEFT OUTER JOIN tblAccountMaster as acc ON ldg.AccountMasterId = acc.AccountMasterId )";
// qry += " LEFT OUTER JOIN tblProductLedger as prd ON ldg.LedgerNumber = prd.LedgerNumber ";
// qry += " WHERE ldg.UserId = " + User.userID;
// qry += " AND Credit IS NOT NULL";
// return qry;
//}
//---------------- Get Query For Tax Information [ Whole Taxes ] -------------------------------
//protected string GetQueryForTaxesOnTheWhole()
//{
// string qry = " SELECT ";
// qry += " tm.TaxName + '|' + CONVERT( VARCHAR , tm.TaxId ) as TaxName , tax.TaxAmount ";
// qry += " FROM ( tblLedger as ldg ";
// qry += " LEFT OUTER JOIN tblTax as tax ON ldg.LedgerNumber = tax.LedgerNumber ) ";
// qry += " LEFT OUTER JOIN tblTaxMaster as tm ON tax.TaxMasterId = tm.TaxId ";
// qry+="WHERE tax.ProductLedgerNumber = NULL AND ldg.UserId = " +User.userID;
// return qry;
//}
//---------------- Get Query For [ Associated Product ] -----------------------------------
//protected string GetQueryForAssociatedProducts()
//{
// string qry = " SELECT ";
// qry += " ";
// return qry;
//}
public List <string> GetAccountListWithCashOrBankAsGroup(DBSite site, bool accounts_with_group_bank_only)
{
List <string> account_list = new List <string>();
string qry = " SELECT ";
qry += " AccountName " + " + ' | ' + CONVERT( VARCHAR, AccountMasterId ) as AccountName ";
qry += " FROM tblAccountMaster as acc ";
qry += " LEFT OUTER JOIN ";
qry += " tblGroup as grp ";
qry += " ON acc.GroupId = grp.GroupId ";
qry += " WHERE grp.GroupName = 'bank' ";
if (!accounts_with_group_bank_only)
{
qry += " OR grp.GroupName = 'cash' ";
}
qry += " AND acc.UserId = " + Util_BLL.User.UserId;
DataTable dt = site.ExecuteSelect(qry);
foreach (DataRow row in dt.Rows)
{
account_list.Add(util.CheckNull(row["AccountName"]));
}
return(account_list);
}
public List <ReportCardCBSEEntity> GetReportCardCBSEOakWod_Remarks(int studentId)
{
DBSite site = new DBSite();
List <ReportCardCBSEEntity> ReportCardList = new List <ReportCardCBSEEntity>();
//DataTable dt = site.ExecuteSelect(GetCBSEQry(studentId, "Discipline"));
DataTable dt = site.ExecuteSelect(GetCBSEQry_OakWood(studentId
, "Remarks"
, "Unit Test 1"
, "Unit Test 2"
, "Note Book 1"
, "Note Book 2"
, "SEA 1"
, "SEA 2"
, "Half Yearly"
, "Annual"
, "Term I"
, "Term II"
));
ReportCardCBSEEntity reportCard;
foreach (DataRow dr in dt.Rows)
{
reportCard = new ReportCardCBSEEntity();
reportCard.StudentMasterId = studentId;
reportCard.StudentName = util.CheckNull(dr["StudentName"]);
reportCard.Subject = util.CheckNull(dr["SubjectName"]);
reportCard.HalfYearly = util.CheckNull(dr["HalfYearly"]);
reportCard.Yearly = util.CheckNull(dr["Yearly"]);
ReportCardList.Add(reportCard);
}
return(ReportCardList);
}
public bool IsDulicateExam(DBSite site, ExamMasterEntity exam, bool isUpdate)
{
bool isDuplicate = true;
string qry = "";
qry += "SELECT ExamName FROM tblExamMaster ";
qry += Util_BLL.GetUserWhereCondition(Util_BLL.User);
qry += " AND ExamName ='" + exam.ExamName + "'";
//qry += " AND TermId =" + exam.TermId;
//qry += " AND MaxMarks =" + exam.MaxMarks;
DataTable dt = site.ExecuteSelect(qry);
if (isUpdate)
{
isDuplicate = dt.Rows.Count > 1;
}
else
{
isDuplicate = dt.Rows.Count > 0;
}
return(false);
//return isDuplicate;
}
public DataTable GetTaxInformation(DBSite site)
{
string qry = " SELECT TaxId as Id , "
+ " TaxName + ' | ' + CONVERT( VARCHAR , TaxAmount ) as TaxName FROM tblTaxMaster "
+ Util_BLL.GetUserWhereCondition(Util_BLL.User);
return(site.ExecuteSelect(qry));
}
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 <ReportCardCBSEEntity_Hashmi> GetReportCardCBSE_Hashmi(int studentId)
{
DBSite site = new DBSite();
List <ReportCardCBSEEntity_Hashmi> ReportCardList = new List <ReportCardCBSEEntity_Hashmi>();
string qry = GetCBSEQry_Hashmi(studentId
, "Scholastic"
, "Unit Test 1"
, "Unit Test 2"
, "SA 1"
, "SA 2"
, "SEA 1"
, "SEA 2"
, "Half Yearly"
, "Annual"
, "Term 1"
, "Term 2"
);
DataTable dt = site.ExecuteSelect(qry);
ReportCardCBSEEntity_Hashmi reportCard;
foreach (DataRow dr in dt.Rows)
{
reportCard = new ReportCardCBSEEntity_Hashmi();
reportCard.StudentMasterId = studentId;
reportCard.StudentName = util.CheckNull(dr["StudentName"]);
reportCard.Subject = util.CheckNull(dr["SubjectName"]);
reportCard.ClassName = util.CheckNull(dr["ClassName"]);
reportCard.SectionName = util.CheckNull(dr["SectionName"]);
reportCard.MotherName = util.CheckNull(dr["MotherName"]);
reportCard.FatherName = util.CheckNull(dr["FatherName"]);
reportCard.DOB = util.CheckNull(dr["dob"]);
reportCard.UnitTest1 = util.CheckNull(dr["PerTest1"]);
reportCard.UnitTest1MaxMarks = util.CheckNull(dr["UnitTest1MaxMarks"]);
reportCard.SA1 = util.CheckNull(dr["NoteBook1"]);
reportCard.SA1MaxMarks = util.CheckNull(dr["SA1MaxMarks"]);
reportCard.UnitTest2 = util.CheckNull(dr["PerTest2"]);
reportCard.UnitTest2MaxMarks = util.CheckNull(dr["UnitTest2MaxMarks"]);
reportCard.SA2 = util.CheckNull(dr["NoteBook2"]);
reportCard.SA2MaxMarks = util.CheckNull(dr["SA2MaxMarks"]);
ReportCardList.Add(reportCard);
}
return(ReportCardList);
}
//------------------ Get Sale-Purchage Information -----------------------------------------------
public List <SalePurchaseEntity> GetSalePurchase(DBSite site, string sp_ids = "")
{
List <SalePurchaseEntity> sale_purchase_entity_list = new List <SalePurchaseEntity>();
SalePurchaseEntity sale_purchase_entity = null;
//444
string qry = " SELECT ledgerId "
+ ", Billdate "
+ ", AccountName "
+ ", credit " // if sales else debit
+ ", particulars "
+ ", discount "
+ " FROM tblLedger l"
+ Util_BLL.GetUserWhereCondition(Util_BLL.User);
//string qry = GetQueryForSimpleFields();
DataTable dt = dt = site.ExecuteSelect(qry);
foreach (DataRow row in dt.Rows)
{
sale_purchase_entity = new SalePurchaseEntity(); // -------- sale purchase entity ----------
//--------------- Normal Fields ----------------------------
sale_purchase_entity.SalePurchaseId = util.CheckNullInt(row["LedgerId"]);
sale_purchase_entity.BillDate = util.CheckNull(row["LDate"]);
sale_purchase_entity.party = util.CheckNull(row["AccountName"]);
sale_purchase_entity.BillNumber = util.CheckNull(row["BillNumber"]);
sale_purchase_entity.SalePurchaseAmount = util.CheckNullDouble(row["Debit"]);
sale_purchase_entity.discount = util.CheckNull(row["Particulars"]);
sale_purchase_entity.discount = util.CheckNull(row["Discount"]);
double discount = util.GetDiscountAmount(sale_purchase_entity.discount, sale_purchase_entity.SalePurchaseAmount);
sale_purchase_entity.TotalAmount = sale_purchase_entity.SalePurchaseAmount - discount;
//---------------- sale detail entity [ product information ]------------------
// -------------- tax information [ taxes on the whole ] ------------------------
sale_purchase_entity_list.Add(sale_purchase_entity);
}
return(sale_purchase_entity_list);
}
public DataTable GetExamMarksDT(DBSite site, int classId, int sectionId, int examId, int subjectId, int IsNew)
{
string qry;
qry = " SELECT "
+ " StudentMasterId "
+ " , StudentName "
//+ " , ExamMarksId "
//+ " , ExamID "
+ " , ExamName "
//+ " , m.ClassID "
+ " , ClassName "
//+ " , m.SectionID "
+ " , SectionName "
//+ " , SubjectID "
+ " , SubjectName "
//+ " , IsNull(IsPresent, 1) IsPresent "
+ ", IsNull(MarksObtained, '0') MarksObtained "
//+ " , m.SubUserId, m.UserId, m.FYear "
+ " FROM tblExamMarks m "
+ " LEFT OUTER JOIN tblStudentMaster st ON m.studentID = st.StudentMasterID "
+ " LEFT OUTER JOIN tblClassMaster c ON c.ClassMasterId = m.ClassID "
+ " LEFT OUTER JOIN tblSectionMaster sm ON sm.sectionMasterID = m.SectionID "
+ " LEFT OUTER JOIN tblExamMaster em ON em.ExamMasterID = m.ExamID "
+ " LEFT OUTER JOIN tblSubjectMaster sub ON sub.SubjectMasterID = m.SubjectId ";
qry += Util_BLL.GetUserWhereCondition(Util_BLL.User, "m");
if (IsNew == 0 && subjectId > 0) // update
{
qry += " AND m.subjectID = " + subjectId;
}
if (IsNew == 0 && examId > 0) //update
{
qry += " AND m.ExamID = " + examId;
}
if (classId > 0)
{
qry += " AND st.ClassID = " + classId;
}
if (sectionId > 0)
{
qry += " AND st.sectionID = " + sectionId;
}
qry += " ORDER BY StudentName, ClassOrder, SectionOrder ";
return(site.ExecuteSelect(qry));
}
public bool isDuplicateAccountName(DBSite site, string accountName)
{
string qry = " SELECT AccountName FROM tblAccountMaster ";
qry += Util_BLL.GetUserWhereCondition(Util_BLL.User); // ------- get user where condition ----------
qry += " AND AccountName = '" + accountName + "'";
DataTable dt = null;
dt = site.ExecuteSelect(qry);
return(dt.Rows.Count > 0);
}
public bool IsStudentPresentInAttendance(DBSite site, int stId)
{
string qry = " SELECT StudentMasterID FROM tblAttendance ";
qry += Util_BLL.GetUserWhereCondition(Util_BLL.User);
qry += " AND StudentMasterID = " + stId;
DataTable dt = null;
dt = site.ExecuteSelect(qry);
return(dt.Rows.Count > 0);
}
public bool IsProductPresentInProductLedger(DBSite site, int productMasterId)
{
string qry = " SELECT ProductId FROM tblProductLedger ";
qry += Util_BLL.GetUserWhereCondition(Util_BLL.User);
qry += " AND ProductId = '" + productMasterId + "'";
DataTable dt = null;
dt = site.ExecuteSelect(qry);
return(dt.Rows.Count > 0);
}
public bool IsTransactionInProductLedger(DBSite site, int transId)
{
string qry = " SELECT SourceId FROM tblProductLedger ";
qry += Util_BLL.GetUserWhereCondition(Util_BLL.User);
qry += " AND SourceId = '" + transId + "'";
DataTable dt = null;
dt = site.ExecuteSelect(qry);
return(dt.Rows.Count > 0);
}
public DataTable GetMatchedRecords(DBSite site, string selectedAccountType, string value_to_search, AccountType act)
{
string qry = "SELECT " + AccountType.GetTypeIdName(act, selectedAccountType) + " ID, " + AccountType.GetColumnName(act, selectedAccountType) + " Name ";
qry += " FROM " + AccountType.GetTableName(act, selectedAccountType);
qry += " WHERE " + AccountType.GetColumnName(act, selectedAccountType) + " LIKE '%" + value_to_search + "%'";
qry += " AND UserId= " + Util_BLL.User.UserId;
return(site.ExecuteSelect(qry));
}
public bool isDuplicateFieldName(DBSite site, string typeName, string name, int userId)
{
AccountType act = new AccountType();
string qry = "SELECT " + AccountType.GetTypeIdName(act, typeName) + " FROM " + AccountType.GetTableName(act, typeName);
qry += " WHERE " + AccountType.GetColumnName(act, typeName) + " = '" + name + "' ";
qry += " AND userID = " + Util_BLL.User.UserId;
DataTable dt = site.ExecuteSelect(qry);
return(dt.Rows.Count > 0);
}
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);
}
//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 bool IsDuplicateCard(DBSite site, string cardNo, int studentId)
{
string qry = " SELECT IdCardNo FROM tblStudentMaster ";
qry += Util_BLL.GetUserWhereCondition(Util_BLL.User);
qry += " AND IdCardNo = '" + cardNo + "'";
qry += "AND studentMasterId <> " + studentId;
DataTable dt = null;
dt = site.ExecuteSelect(qry);
return(dt.Rows.Count > 0);
}
public bool ValidDateEntry(DBSite site, string acc_id, string payment_date)
{
string qry = " SELECT CreationDate FROM tblAccountMaster "
+ " WHERE AccountMasterId=" + acc_id;
DataTable dt = site.ExecuteSelect(qry);
DataRow row = dt.Rows[0];
DateTime date = Convert.ToDateTime(row["CreationDate"]);
string acc_creation_date = date.ToShortDateString();
return(util.isDateRangeValid(acc_creation_date, payment_date));
}
public static string[] GetProductIdAndUOM(DBSite site, string productId)
{
string qry = "SELECT SellingPrice , UnitName + ' | ' +CONVERT( VARCHAR, UOMId ) as UnitName"
+ " FROM tblProductMaster as productTable "
+ " LEFT OUTER JOIN tblUOM as unitTable ON productTable.UOM=unitTable.UOMId "
+ /*util.GetUserWhereCondition()*/ " WHERE 1=1 AND productTable.UserID = " + Util_BLL.User.UserId + " AND productTable.FYear = " + Util_BLL.User.fYear
+ " AND ProductID='" + productId + "' ";
DataTable dt = site.ExecuteSelect(qry);
DataRow dr = dt.Rows[0];
return(new string[] { util.CheckNull(dr["UnitName"]), util.CheckNull(dr["SellingPrice"]) });
}
