//-------------- Edit Issuse Information -------------------------------- public void EditIssueInformation(DBSite site, SalePurchaseDetail from_product, SalePurchaseDetail to_product, string product_ledger_number) { string qry = " "; qry = " UPDATE tblProductLedger SET " + " BillDate = '" + from_product.MovementDate + " '" + ", ProductId = " + from_product.productAutoId + ", SoldQty = " + from_product.qty + ", LocationId = " + from_product.locationId + Util_BLL.GetUserWhereCondition(Util_BLL.User); qry += " AND ProductLedgerNumber = " + product_ledger_number + " AND SourceId = " + TransactionType.StockMovement + " AND drcr = 'C'"; site.Execute(qry); qry = " UPDATE tblProductLedger SET " + " BillDate = '" + to_product.MovementDate + " '" + ", ProductId = " + to_product.productAutoId + ", BoughtQty = " + to_product.qty + ", LocationId = " + to_product.locationId + Util_BLL.GetUserWhereCondition(Util_BLL.User); qry += " AND ProductLedgerNumber = " + product_ledger_number + " AND SourceId = " + TransactionType.StockMovement + " AND drcr = 'D'"; site.Execute(qry); }
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 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 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 void DeleteExam(DBSite site, string examMarksIds) { string qry = "DELETE FROM tblExamMarks "; qry += Util_BLL.GetUserWhereCondition(Util_BLL.User); qry += " AND ExamMarksId IN (" + examMarksIds + ")"; site.Execute(qry); }
public void DeleteRecords(DBSite site, string ids) { string qry = "DELETE FROM tblAccountMaster"; qry += Util_BLL.GetUserWhereCondition(Util_BLL.User); // --------get user where condition ----------------- qry += " AND AccountMasterId IN (" + ids + ")"; site.Execute(qry); }
public void DeleteTerm(DBSite site, string ids) { String qry = "DELETE FROM tblTerm"; qry += Util_BLL.GetUserWhereCondition(Util_BLL.User); // --------get user where condition ----------------- qry += " AND TermID IN (" + ids + ")"; site.Execute(qry); }
public void DeleteSection(DBSite site, string ids) { string qry = "DELETE FROM tblSectionMaster"; qry += Util_BLL.GetUserWhereCondition(Util_BLL.User); qry += " AND SectionMasterID IN (" + ids + ")"; site.Execute(qry); }
//-------------------- Update Payment Information ---------------------------------------------- public void UpdatePaymentInformation(DBSite site, SalePurchaseEntity salePurchase, string ledgerIdNumber) { string ledger_id = util.GetLedgerId(ledgerIdNumber); string ledger_number = util.GetLedgerNumber(ledgerIdNumber); // 444 int party = Int32.Parse(util.GetLOVId(salePurchase.party)); int mode = Int32.Parse(util.GetLOVId(salePurchase.mode)); if (salePurchase.transactionType == TransactionType.PaymentMade) { int tmp; tmp = Int32.Parse(util.GetLOVId(salePurchase.mode)); mode = Int32.Parse(util.GetLOVId(salePurchase.party)); party = tmp; } // sales - party string party_qry = " UPDATE tblLedger SET " + " LDate = '" + salePurchase.BillDate + "' " + ", AccountMasterId= " + mode + ", modeid= " + party + ", BillNumber= '" + util.GetLOVName(salePurchase.BillNumber) + "' " + ", Particulars= '" + salePurchase.particulars + "' " + ", debit = " + salePurchase.SalePurchaseAmount + ", CheckNumber= '" + salePurchase.CheckNumber + "' " + ", subUserId = " + Util_BLL.User.Subusers[0].SubuserId + Util_BLL.GetUserWhereCondition(Util_BLL.User) // + " AND LedgerId=" + ledger_id + " AND LedgerNumber=" + ledger_number + " AND drcr = 'D'"; // sales - mode string mode_qry = " UPDATE tblLedger SET " + " LDate = '" + salePurchase.BillDate + "' " + ", AccountMasterId= " + party + ", modeId = " + mode + ", BillNumber= '" + util.GetLOVName(salePurchase.BillNumber) + "' " + ", Particulars= '" + salePurchase.particulars + "' " + ", credit = " + salePurchase.SalePurchaseAmount + ", CheckNumber= '" + salePurchase.CheckNumber + "' " + ", subUserId = " + Util_BLL.User.Subusers[0].SubuserId + Util_BLL.GetUserWhereCondition(Util_BLL.User) + " AND LedgerNumber=" + ledger_number + " AND drcr = 'C'"; site.Execute(party_qry); site.Execute(mode_qry); }
public void UpdateSectionFrm(DBSite site, SectionMasterEntity sec) { string qry = "UPDATE tblSectionMaster SET "; qry += " SectionName ='" + sec.SectionName + "'"; qry += ", SectionOrder=" + sec.SectionOrder + ""; qry += Util_BLL.GetUserWhereCondition(Util_BLL.User); qry += " AND SectionMasterID=" + sec.SectionMasterId; site.Execute(qry); }
//------------------ 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 void UpdateClassFrm(DBSite site, ClassMasterEntity cls) { string qry = "UPDATE tblClassMaster SET "; qry += " ClassName ='" + cls.ClassName + "'"; qry += ", ClassOrder=" + cls.ClassOrder + ""; qry += Util_BLL.GetUserWhereCondition(Util_BLL.User); //------------- gwt user where condition -------------- qry += " AND ClassMasterID=" + cls.ClassMasterId; site.Execute(qry); }
public void UpdateTerm(DBSite site, TermMasterEntity trm) { string qry = "UPDATE tblTerm SET "; qry += " TermName ='" + trm.TermName + "'"; qry += ", TermStartDate=" + trm.TermStartDate + ""; qry += ", TermEndDate=" + trm.TermEndDate + ""; qry += Util_BLL.GetUserWhereCondition(Util_BLL.User); //------------- gwt user where condition -------------- qry += " AND TermID=" + trm.TermMasterId; site.Execute(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 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 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 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 void DeleteClasses(DBSite site, string ids) { string qry = "DELETE FROM tblStudentMaster"; qry += Util_BLL.GetUserWhereCondition(Util_BLL.User); // --------get user where condition ----------------- qry += " AND ClassID IN (" + ids + ")"; site.Execute(qry); qry = "DELETE FROM tblClassMaster"; qry += Util_BLL.GetUserWhereCondition(Util_BLL.User); // --------get user where condition ----------------- qry += " AND ClassMasterID IN (" + ids + ")"; site.Execute(qry); }
public void UpdateSubject(DBSite site, SubjectMasterEntity subject) { string qry = "UPDATE tblSubjectMaster SET "; qry += " SubjectName ='" + subject.SubjectName + "'"; qry += ", SubjectCode ='" + subject.SubjectCode + "'"; qry += ", SubjectGroupMasterId=" + subject.SubjectGroupID; qry += ", SubjectClassID =" + subject.SubjectClassID; qry += ", SubjectSectionID =" + subject.SubjectSectionID; qry += ", SubjectOrder=" + subject.SubjectOrder; qry += Util_BLL.GetUserWhereCondition(Util_BLL.User); qry += " AND SubjectMasterID=" + subject.SubjectMasterId; site.Execute(qry); }
public void ProcessManualAttendance(DBSite site, int yr, int mnth, int dey, int isHoliday) { // Insert records in tblAttendance when page loads string qry = "INSERT INTO tblAttendance(YearNo, MonthNo, DayNo, HolidayId " + " , StudentMasterID, UserID, SubUserID, FYear) " + " SELECT " + yr + ", " + mnth + ", " + dey + ", " + isHoliday + ", StudentMasterID, " + util.GetUserInsertQry(Util_BLL.User) + " FROM tblStudentMaster s " + Util_BLL.GetUserWhereCondition(Util_BLL.User) + " AND NOT EXISTS(SELECT MonthNo, DayNo, StudentMasterID " + " FROM tblAttendance a " + " WHERE a.StudentMasterID = s.StudentMasterID " + " AND a.YearNo=" + yr + " AND a.MonthNo = " + mnth + " AND a.DayNo = " + dey + ") "; site.Execute(qry); }
public bool IsDulicateSubject(DBSite site, SubjectMasterEntity subject) { bool isDuplicate = true; string qry = ""; qry += "SELECT subject FROM tblExamMaster "; qry += Util_BLL.GetUserWhereCondition(Util_BLL.User); qry += " AND ExamName ='" + subject.SubjectName + "'"; //qry += " AND TermId =" + exam.TermId; //qry += " AND MaxMarks =" + exam.MaxMarks; DataTable dt = site.ExecuteSelect(qry); isDuplicate = dt.Rows.Count > 0; return(isDuplicate); }
public void ProcessMachineAttendanceOld(DBSite site, int yr, int mnth, int dey, int isHoliday) { // Insert records in tblAttendance when page loads from attendance machine string qry = " INSERT INTO tblAttendance(YearNo, MonthNo, DayNo, HolidayId " + ", status, InTime, OutTime, StudentMasterID, UserID, SubUserID, FYear) " + " SELECT " + yr + ", " + mnth + ", " + dey + ", " + isHoliday + ", (CASE WHEN Min(OfficePunch) is Null THEN 0 ELSE 1 END) status " + " , Min(OfficePunch), MAX(OfficePunch), s.StudentMasterID, " + util.GetUserInsertQry(Util_BLL.User) + " FROM tblStudentMaster s " + " LEFT OUTER JOIN stardc_rawdata r ON s.IdCardNo = r.CardNo " + " AND Day(officePunch) = " + dey + " and month(officepunch) = " + mnth + Util_BLL.GetUserWhereCondition(Util_BLL.User) + " AND NOT EXISTS(SELECT YearNo, MonthNo, DayNo, StudentMasterID, outtime " + " FROM tblAttendance a " + " WHERE a.StudentMasterID = s.StudentMasterID " + " AND outTime is Null AND YearNo = " + yr + " AND a.MonthNo =" + mnth + " AND a.DayNo = " + dey + " ) " + " GROUP BY s.StudentMasterID " + " UNION " + " SELECT " + yr + ", " + mnth + ", " + dey + ", " + isHoliday + ", (CASE WHEN Min(OfficePunch) is Null THEN 0 ELSE 1 END) status " + " , Min(OfficePunch), MAX(OfficePunch), s.StudentMasterID, " + util.GetUserInsertQry(Util_BLL.User) + " FROM tblStudentMaster s " + " INNER JOIN stardc_rawdata r ON s.IdCardNo = r.CardNo " + " AND Day(officePunch) = " + dey + " and month(officepunch) = " + mnth + " AND year(officepunch) = " + yr + Util_BLL.GetUserWhereCondition(Util_BLL.User) + " AND NOT EXISTS(SELECT YearNo, MonthNo, DayNo, StudentMasterID, outtime " + " FROM tblAttendance a " + " WHERE a.StudentMasterID = s.StudentMasterID " + " AND outtime = officePunch AND year(officepunch) = " + yr + " AND a.MonthNo =" + mnth + " AND a.DayNo = " + dey + " ) " + " GROUP BY s.StudentMasterID "; site.Execute(qry); }
public void ProcessMachineAttendance(DBSite site, int yr, int mnth, int dey, int isHoliday) { // Insert records in tblAttendance when page loads from attendance machine string qry = " INSERT INTO tblAttendance(YearNo, MonthNo, DayNo, HolidayId " + ", status, InTime, OutTime, StudentMasterID, UserID, SubUserID, FYear) " + " SELECT " + yr + ", " + mnth + ", " + dey + ", " + isHoliday + ", (CASE WHEN Min(logDate) is Null THEN 0 ELSE 1 END) status " + " , Min(logDate), MAX(logDate), s.StudentMasterID, " + util.GetUserInsertQry(Util_BLL.User) + " FROM tblStudentMaster s " + " LEFT OUTER JOIN ebioServer.dbo.DeviceLogs_3_2019 e ON e.UserID=s.IDCardNO " + " AND Day(logDate) = " + dey + " and month(logDate) = " + mnth + Util_BLL.GetUserWhereCondition(Util_BLL.User, "s") + " AND NOT EXISTS(SELECT YearNo, MonthNo, DayNo, StudentMasterID, outtime " + " FROM tblAttendance a " + " WHERE a.StudentMasterID = s.StudentMasterID " + " AND outTime is Null AND YearNo = " + yr + " AND a.MonthNo =" + mnth + " AND a.DayNo = " + dey + " ) " + " GROUP BY s.StudentMasterID " + " UNION " + " SELECT " + yr + ", " + mnth + ", " + dey + ", " + isHoliday + ", (CASE WHEN Min(logDate) is Null THEN 0 ELSE 1 END) status " + " , Min(logDate), MAX(logDate), s.StudentMasterID, " + util.GetUserInsertQry(Util_BLL.User) + " FROM tblStudentMaster s " + " INNER JOIN ebioServer.dbo.DeviceLogs_3_2019 e ON e.UserID=s.IDCardNO" + " AND Day(logDate) = " + dey + " and month(logDate) = " + mnth + " AND year(logDate) = " + yr + Util_BLL.GetUserWhereCondition(Util_BLL.User, "s") + " AND NOT EXISTS(SELECT YearNo, MonthNo, DayNo, StudentMasterID, outtime " + " FROM tblAttendance a " + " WHERE a.StudentMasterID = s.StudentMasterID " + " AND outtime = logDate AND year(logDate) = " + yr + " AND a.MonthNo =" + mnth + " AND a.DayNo = " + dey + " ) " + " GROUP BY s.StudentMasterID "; site.Execute(qry); }
public void EditAccountMasterData(DBSite site, AccountMasterEntity ame) { string qry = "UPDATE tblAccountMaster SET "; qry += " AccountName='" + ame.AccountName + "'"; qry += ", CreationDate='" + ame.CreationDate + "'"; qry += ", OpeningBalance=" + ame.OpeningBalance + ""; qry += ", DRCR=" + ame.DRCR + ""; qry += ", Address='" + ame.Address + "'"; qry += ", City='" + ame.City + "'"; qry += ", Phone='" + ame.Phone + "'"; qry += ", Mobile='" + ame.Mobile + "'"; qry += ", Email='" + ame.Email + "'"; qry += ", Remarks='" + ame.Remarks + "'"; qry += ", GroupId=" + ame.GroupId; qry += Util_BLL.GetUserWhereCondition(Util_BLL.User); //------------- gwt user where condition -------------- qry += " AND AccountMasterId=" + ame.AccountMasterId; site.Execute(qry); }
public void UpdateExam(DBSite site, ExamMasterEntity exam) { string qry = "UPDATE tblExamMaster SET "; qry += " ExamName ='" + exam.ExamName + "'"; qry += " , ExamCode ='" + exam.ExamCode + "'"; qry += ", ExamDate='" + exam.ExamDate + "'"; qry += ", ClassMasterID=" + exam.ClassMasterID; qry += ", SectionMasterID =" + exam.SectionMasterID; qry += ", MaxMarks =" + exam.MaxMarks; qry += ", PassMarks =" + exam.PassMarks; qry += ", ISFormula =" + exam.IsFormula; qry += ", ExamOrder =" + exam.ExamOrder; qry += ", TermId =" + exam.TermId; qry += Util_BLL.GetUserWhereCondition(Util_BLL.User); qry += " AND ExamMasterId = " + exam.ExamMasterId; site.Execute(qry); }
public string GetCBSEQry_Hashmi(int studentId, string subjectGroup , string unitTest1, string unitTest2 , string noteBook1, string noteBook2 , string sea1, string sea2 , string halfYearly, string yearly , string term1, string term2 ) { string qry = ""; qry = "SELECT " + " MAX(StudentName) StudentName, SubjectName " + ", Max(FatherName)FatherName, MAX(MotherName)MotherName, MAX(DOB)DOB, MAX(AdmissionNo)AdmNo" + " , MAX(ClassName) className, MAX(SectionName) SectionName" + " , MAX(SubjectGroupName)SubjectGroupName " + " , MAX(PerTest1)PerTest1 " + ", MAX(UnitTest1MaxMarks) UnitTest1MaxMarks " + " , MAX(NoteBook1)NoteBook1 " + ", MAX(SA1MaxMarks) SA1MaxMarks " + " , MAX(SEA1)SEA1 " + " , MAX(HalfYearly)HalfYearly " + " , MAX(PerTest2)PerTest2 " + ", MAX(UnitTest2MaxMarks) UnitTest2MaxMarks " + " , MAX(NoteBook2)NoteBook2 " + ", MAX(SA2MaxMarks) SA2MaxMarks " + " , MAX(SEA2)SEA2 " + " , MAX(Yearly) Yearly " + " FROM " + " ( " + " SELECT " + " StudentName, FatherName, MotherName, DOB, AdmissionNo , SubjectGroupName" + " , ClassName, SectionName, SubjectName, SubjectOrder , " + " (CASE WHEN (ExamName = '" + unitTest1 + "' AND TermName ='" + term1 + "') " + " THEN IsNull(MAX(MarksObtained), '0') END) PerTest1 " + ", (CASE WHEN (ExamName = '" + unitTest1 + "' AND TermName ='" + term1 + "') " + " THEN IsNull(MAX(MaxMarks), '0') END) UnitTest1MaxMarks " + " ,(CASE WHEN (ExamName = '" + noteBook1 + "' AND TermName ='" + term1 + "') " + " THEN IsNull(MAX(MarksObtained), '0') END) NoteBook1 " + " ,(CASE WHEN (ExamName = '" + noteBook1 + "' AND TermName ='" + term1 + "') " + " THEN IsNull(MAX(MaxMarks), '0') END) SA1MaxMarks " + " ,(CASE WHEN (ExamName = '" + sea1 + "' AND TermName ='" + term1 + "') " + " THEN IsNull(MAX(MarksObtained), '0') END) SEA1 " + " ,(CASE WHEN (ExamName = '" + halfYearly + "' AND TermName ='" + term1 + "') " + " THEN IsNull(MAX(MarksObtained), '0') END) HalfYearly " + " ,(CASE WHEN (ExamName = '" + unitTest2 + "' AND TermName ='" + term2 + "') " + " THEN IsNull(MAX(MarksObtained), '0') END) PerTest2 " + " ,(CASE WHEN (ExamName = '" + unitTest2 + "' AND TermName ='" + term2 + "') " + " THEN IsNull(MAX(MaxMarks), '0') END) UnitTest2MaxMarks " + " ,(CASE WHEN (ExamName = '" + noteBook2 + "' AND TermName ='" + term2 + "') " + " THEN IsNull(MAX(MarksObtained), '0') END) NoteBook2 " + " ,(CASE WHEN (ExamName = '" + noteBook2 + "' AND TermName ='" + term2 + "') " + " THEN IsNull(MAX(MaxMarks), '0') END) SA2MaxMarks " + " ,(CASE WHEN (ExamName = '" + sea2 + "' AND TermName ='" + term2 + "') " + " THEN IsNull(MAX(MarksObtained), '0') END) SEA2 " + " ,(CASE WHEN (ExamName = '" + yearly + "' AND TermName='" + term2 + "') " + " THEN IsNull(MAX(MarksObtained), '0') END) Yearly " + " FROM( " + " SELECT " + " StudentMasterId " + " , StudentName , FatherName, MotherName, DOB, AdmissionNo, SubjectGroupName" + " , MobileF " + " , ClassName " + " , SectionName " + " , TermName " + " , SubjectName " + " , SubjectOrder " + " , ExamName " + " , IsNull(MAX(MarksObtained), '0') marksObtained " + " , IsNull(MAX(MaxMarks), '0') MaxMarks " //+ " --, 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 " + " LEFT OUTER JOIN tblSubjectGroupMaster sgm " + " ON sgm.SubjectGroupMasterID = sub.SubjectGroupMasterID " + " LEFT OUTER JOIN tblTerm tm ON tm.TermId = em.termID "; qry += Util_BLL.GetUserWhereCondition(Util_BLL.User, "st"); qry += " AND st.studentMasterId = " + studentId + " AND SubjectGroupName='" + subjectGroup + "'" + " GROUP By " + " StudentMasterId " + " , StudentName " + " , FatherName, MotherName, DOB, AdmissionNo, SubjectGroupName" + " , MobileF " + " , ClassName " + " , SectionName " + " , SubjectName" + " , SubjectOrder " + " , ExamName " + " , TermName " + " ) as A " + " GROUP BY StudentName, ExamName " + " , FatherName, MotherName, DOB, AdmissionNo, SubjectGroupName" + " , ClassName, SectionName " + " , TermName " + " , SubjectName " + " , subjectOrder " + " ) as b " + " GROUP BY StudentName, SubjectName " + " , SubjectOrder " + " ORDER BY SubjectOrder "; return(qry); }
public List <ExamMarksEntity> GetExamMarks(DBSite site, int classId, int sectionId, int examId, string subjectIds, int studentId, int IsNew) { // if isNew=0 then get reocrds from tblStudentMaster // else get the reocrds from tblExamMarks List <ExamMarksEntity> marks = new List <ExamMarksEntity>(); ExamMarksEntity mark = null; string qry = ""; ExamMarksEntity exam = new ExamMarksEntity(); //exam.StudentMasterId = stu if (IsNew == 1) { qry = " SELECT " + " StudentMasterId " + " , StudentName " + " , MobileF " + " , FatherName " + " , 0 ExamMarksId " + " , 0 ExamId " + " , '' ExamName " + " , '' ExamCode " + ", '' MaxMarks " + " , 0 SubjectID" + " , '' SubjectName" + ", '' SubjectGroupType " + " , ClassID " + " , ClassName " + " , SectionID " + " , SectionName " + " , 1 IsPresent " + " , '0' MarksObtained " + " , st.SubUserId, st.UserId, st.FYear " + " FROM tblStudentMaster st " + " LEFT OUTER JOIN tblClassMaster c ON c.ClassMasterId = st.ClassID " + " LEFT OUTER JOIN tblSectionMaster s On s.SectionMasterID = st.SectionId "; qry += Util_BLL.GetUserWhereCondition(Util_BLL.User, "st"); } else { // update qry = " SELECT " + " StudentMasterId " + " , StudentName " + " , FatherName " + " , MobileF " + " , ExamMarksId " + " , ExamID " + " , ExamName " + " , ExamCode " + ", MaxMarks " + " , m.ClassID " + " , ClassName " + " , m.SectionID " + " , SectionName " + " , SubjectID " + " , SubjectName " + " , SubjectGroupType " + " , 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 " + " LEFT OUTER JOIN tblSubjectGroupMaster sgm ON sgm.SubjectGroupMasterID = sub.SubjectGroupMasterID "; qry += Util_BLL.GetUserWhereCondition(Util_BLL.User, "m"); } if (IsNew == 0 && subjectIds.Length > 1) // update { qry += " AND m.subjectID IN (" + subjectIds + ")"; } 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; } if (studentId > 0) { qry += " AND st.studentMasterId = " + studentId; } if (IsNew == 1) { qry += " ORDER BY StudentName, ClassOrder, SectionOrder "; } else { if (studentId > 0) { qry += " ORDER BY ExamOrder, subjectOrder "; } else { qry += " ORDER BY StudentName, ClassOrder, SectionOrder, subjectOrder "; } } int SerialCount = 0; DataTable dt = site.ExecuteSelect(qry); foreach (DataRow dr in dt.Rows) { mark = new ExamMarksEntity(); SerialCount += 1; mark.SlNo = SerialCount; mark.ExamMarksId = util.CheckNullInt(dr["ExamMarksId"]); mark.StudentMasterId = util.CheckNullInt(dr["StudentMasterId"]); mark.StudentName = util.CheckNull(dr["StudentName"]); mark.FatherName = util.CheckNull(dr["FatherName"]); mark.MobileNo = util.CheckNull(dr["MobileF"]); mark.ExamId = util.CheckNullInt(dr["ExamId"]); mark.ExamName = util.CheckNull(dr["ExamName"]); mark.ExamCode = util.CheckNull(dr["ExamCode"]); mark.MaxMarks = util.CheckNull(dr["MaxMarks"]); mark.SubjectId = util.CheckNullInt(dr["SubjectID"]); mark.SubjectName = util.CheckNull(dr["SubjectName"]); mark.SubjectGroupType = util.CheckNull(dr["SubjectGroupType"]); mark.classId = util.CheckNullInt(dr["ClassID"]); mark.ClassName = util.CheckNull(dr["ClassName"]); mark.SectionId = util.CheckNullInt(dr["SectionID"]); mark.SectionName = util.CheckNull(dr["SectionName"]); mark.MarksObtained = util.CheckNull(dr["MarksObtained"]); mark.IsPresent = util.CheckNullInt(dr["IsPresent"]); mark.SubUserID = util.CheckNullInt(dr["SubUserId"]); mark.UserID = util.CheckNullInt(dr["userID"]); mark.FYear = util.CheckNullInt(dr["FYear"]); marks.Add(mark); } return(marks); }
public List <User> GetMatchedUsers(DBSite site, string value_to_search) { List <User> users = new List <User>(); User user = null; string qry = " SELECT " + " UserInfoId" + ", UserName" + ", Email" + ", SenderID" + ", SMSUser" + ", SMSPassword" + ", SMSDelivery" + ", UserPassword" + ", CreationDate" + ", AmountPaid" + ", StartDate" + ", EndDate" + ", Address" + ", City" + ", Country" + ", Phone" + ", Mobile" + ", TinNumber" + ", SalesTaxNumber" + ", CSTNumber" + ", UserType" + ", Remarks"; qry += " FROM tblUserInfo "; qry += Util_BLL.GetUserWhereCondition(Util_BLL.User); // qry += " WHERE "; //qry += " WHERE UserID = " + User.userID + " AND FYear= " + User.fYear + " AND "; qry += "AND (( UserName LIKE '%" + value_to_search + "%' ) OR"; qry += " ( Email LIKE '%" + value_to_search + "%' ) OR"; qry += " ( Address LIKE '%" + value_to_search + "%' ) OR"; qry += " ( City LIKE '%" + value_to_search + "%' ) OR"; qry += " ( Country LIKE '%" + value_to_search + "%' ) OR"; qry += " ( Phone LIKE '%" + value_to_search + "%' ) OR"; qry += " ( Mobile LIKE '%" + value_to_search + "%' ) OR"; qry += " ( TinNumber LIKE '%" + value_to_search + "%' ) OR"; qry += " ( SalesTaxNumber LIKE '%" + value_to_search + "%' ) OR"; if (util.IsDate(value_to_search)) { qry += " ( CreationDate = '" + value_to_search + "' ) OR"; qry += " ( EndDate = '" + value_to_search + "' ) OR"; qry += " ( StartDate = '" + value_to_search + "' ) OR"; } if (util.IsNumber(value_to_search)) { qry += " ( AmountPaid = '" + value_to_search + "' ) OR "; } qry += " ( CSTNumber LIKE '%" + value_to_search + "%' ) "; qry += ")"; DataTable dt = site.ExecuteSelect(qry); foreach (DataRow row in dt.Rows) { user = new 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.AmountPaid = util.CheckNullDecimal(row["AmountPaid"]); user.CreationDate = ((DateTime)row["CreationDate"]).ToShortDateString(); user.StartDate = ((DateTime)row["StartDate"]).ToShortDateString(); user.EndDate = ((DateTime)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["PinNumber"]); user.SalesTaxNumber = util.CheckNull(row["SalesTaxNumber"]); user.CSTNumber = util.CheckNull(row["CSTNumber"]); user.UserType = util.CheckNullInt(row["UserType"]); user.Remarks = util.CheckNull(row["Remarks"]); users.Add(user); } return(users); }