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