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);
}
protected void Get()
{
Util_BLL util = new Util_BLL();
if (!util.IsDate(txtFromDate.Text))
{
lblError.Text = "Please Enter Valid Date !";
lblError.Visible = true;
return;
}
lblError.Text = string.Empty;
lblError.Visible = false;
if (txtFromDate.Text == "")
{
txtFromDate.Text = Util_BLL.MIN_DATE;
}
rvAttendance.LocalReport.Refresh();
}
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);
}
private void BindReport()
{
ColecaoRelatorioPonto Colecao = new ColecaoRelatorioPonto();
ColecaoRelatorioLembrete Lembrete = new ColecaoRelatorioLembrete();
TimeSpan TotalHorasTrabalhadas = (new Ponto_BLL()).HorasTrabalhadas(Util_BLL.ListPonto);
string DifInicial = Util_BLL.FormatHoras(TotalDiferencaInicial(Util_BLL.ListPonto));
foreach (Ponto Item in Util_BLL.ListPonto)
{
#region "Histórico de Ponto"
Colecao.Add(new RelatorioPonto()
{
Data = Item.Data,
DiaSemana = Util_BLL.strRetornaDiaSemana(Item.DiaSemana),
DiferencaInicial = (Item.HoraEntrada != null && Item.HoraEntrada != string.Empty && Item.HoraEntrada != " ::00") ? (Item.HoraEntrada != "00:00:00" ? (TimeSpan.Parse("08:00:00") - TimeSpan.Parse(Item.HoraEntrada)) : TimeSpan.Parse("00:00:00")) : (TimeSpan?)null,
HoraEntrada = (Item.HoraEntrada != null && Item.HoraEntrada != string.Empty && Item.HoraEntrada != " ::00") ? TimeSpan.Parse(Item.HoraEntrada) : (TimeSpan?)null,
HoraSaidaAlmoco = (Item.HoraSaidaAlmoco != null && Item.HoraSaidaAlmoco != string.Empty && Item.HoraSaidaAlmoco != " ::00") ? TimeSpan.Parse(Item.HoraSaidaAlmoco) : (TimeSpan?)null,
HoraRetornoAlmoco = (Item.HoraRetornoAlmoco != null && Item.HoraRetornoAlmoco != string.Empty && Item.HoraRetornoAlmoco != " ::00") ? TimeSpan.Parse(Item.HoraRetornoAlmoco) : (TimeSpan?)null,
HoraSaida = (Item.HoraSaida != null && Item.HoraSaida != string.Empty && Item.HoraSaida != " ::00") ? TimeSpan.Parse(Item.HoraSaida) : (TimeSpan?)null,
TotalHora = Item.TotalHora,
DiferencaTotalHora = Item.DiferencaTotalHora,
TotalHorasDia = Util_BLL.FormatHoras(Util_BLL._TotalHorasDia),
TotalHorasSabado = Util_BLL.FormatHoras(Util_BLL._TotalHorasSabado),
TotalHorasSemanais = Util_BLL.FormatHoras(Util_BLL._TotalHorasSemana),
TotalHorasMes = Util_BLL.FormatHoras(Util_BLL._TotalHorasMes),
TotalHorasTrabalhadas = Util_BLL.FormatHoras(TotalHorasTrabalhadas),
TotalDiferencaHoras = Util_BLL.FormatHoras(new TimeSpan(TotalHorasTrabalhadas.Ticks - Util_BLL._TotalHorasMes.Ticks)),
TotalDiferencaInicial = DifInicial
});
#endregion
#region "Lembretes"
if (!string.IsNullOrEmpty(Item.LembreteHoraEntrada))
{
Lembrete.Add(new RelatorioLembrete()
{
DataLembrete = Item.Data,
DiaSemanaLembrete = Util_BLL.strRetornaDiaSemana(Item.DiaSemana),
Hora = (Item.HoraEntrada != null && Item.HoraEntrada != string.Empty && Item.HoraEntrada != " ::00") ? TimeSpan.Parse(Item.HoraEntrada) : (TimeSpan?)null,
Lembrete = Item.LembreteHoraEntrada,
});
}
if (!string.IsNullOrEmpty(Item.LembreteHoraSaidaAlmoco))
{
Lembrete.Add(new RelatorioLembrete()
{
DataLembrete = Item.Data,
DiaSemanaLembrete = Util_BLL.strRetornaDiaSemana(Item.DiaSemana),
Hora = (Item.HoraSaidaAlmoco != null && Item.HoraSaidaAlmoco != string.Empty && Item.HoraSaidaAlmoco != " ::00") ? TimeSpan.Parse(Item.HoraSaidaAlmoco) : (TimeSpan?)null,
Lembrete = Item.LembreteHoraSaidaAlmoco,
});
}
if (!string.IsNullOrEmpty(Item.LembreteHoraRetornoAlmoco))
{
Lembrete.Add(new RelatorioLembrete()
{
DataLembrete = Item.Data,
DiaSemanaLembrete = Util_BLL.strRetornaDiaSemana(Item.DiaSemana),
Hora = (Item.HoraRetornoAlmoco != null && Item.HoraRetornoAlmoco != string.Empty && Item.HoraRetornoAlmoco != " ::00") ? TimeSpan.Parse(Item.HoraRetornoAlmoco) : (TimeSpan?)null,
Lembrete = Item.LembreteHoraRetornoAlmoco,
});
}
if (!string.IsNullOrEmpty(Item.LembreteHoraSaida))
{
Lembrete.Add(new RelatorioLembrete()
{
DataLembrete = Item.Data,
DiaSemanaLembrete = Util_BLL.strRetornaDiaSemana(Item.DiaSemana),
Hora = (Item.HoraSaida != null && Item.HoraSaida != string.Empty && Item.HoraSaida != " ::00") ? TimeSpan.Parse(Item.HoraSaida) : (TimeSpan?)null,
Lembrete = Item.LembreteHoraSaida,
});
}
#endregion
}
rtvRelatorio.LocalReport.DataSources.Clear();
rtvRelatorio.LocalReport.DataSources.Add(new ReportDataSource("MarcacaoPonto", Colecao));
rtvRelatorio.LocalReport.DataSources.Add(new ReportDataSource("LembretePonto", Lembrete));
rtvRelatorio.SetDisplayMode(DisplayMode.PrintLayout);
rtvRelatorio.ZoomMode = ZoomMode.PageWidth;
rtvRelatorio.RefreshReport();
}
public TimeSpan TotalDiferencaInicial(List <Ponto> ListPonto)
{
long ValorTotal = 0;
foreach (Ponto Item in ListPonto.Where(p => (((Ponto)p).DiaSemana != DiaSemanda.DOMINGO && ((Ponto)p).Feriado == 'N' && ((Ponto)p).Ferias == 'N' && ((Ponto)p).Folga == 'N')))
{
if (Item.TotalHora != null)
{
ValorTotal += TimeSpan.Parse((Item.HoraEntrada != null && Item.HoraEntrada != string.Empty && Item.HoraEntrada != " ::00") ? (Item.HoraEntrada != "00:00:00" ? Util_BLL.FormatHoras(TimeSpan.Parse("08:00:00") - TimeSpan.Parse(Item.HoraEntrada)) : "00:00:00") : "00:00:00").Ticks;
}
}
return(new TimeSpan(ValorTotal));
}
public void SaveSalePurchase(DBSite site, SalePurchaseEntity salePurchase, bool isOpeningBalance)
{
Util_BLL util = new Util_BLL();
/**
*
* // payment made If GM.isFees = 2 Then
* Dim tmp As String
* tmp = mode
* mode = party
* party = tmp
* End If
*
**/
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;
}
if (salePurchase.transactionType == TransactionType.Sales)
{
mode = Modes.SaleAccount;
}
int ledgerNumber = util.GetAutoNumber(site, "LedgerNumber");
// opening balance
string opening_qry = "";
if (isOpeningBalance)
{
string credit_debit = "Debit";
if (salePurchase.drcr == DrCr.Debit)
{
credit_debit = "Debit";
}
else if (salePurchase.drcr == DrCr.Credit)
{
credit_debit = "Credit";
}
opening_qry = " INSERT INTO tblLedger ("
+ " LedgerNumber, Ldate, AccountMasterId, modeId, Particulars, " + credit_debit + ", sourceId, UserId, SubUserId, FYear )"
+ " VALUES ( "
+ ledgerNumber
+ ", '" + salePurchase.BillDate + "' "
+ ", " + party
+ ", " + mode
+ ", '" + salePurchase.particulars + "' "
+ ", " + salePurchase.SalePurchaseAmount
+ ", " + salePurchase.transactionType
+ ", " + util.GetUserInsertQry(Util_BLL.User)
+ " ) ";
site.Execute(opening_qry);
}
// sales - party
string party_qry = " INSERT INTO tblLedger ("
+ " LedgerNumber, drcr, Ldate, AccountMasterId, modeId, BillNumber, Particulars, credit, Discount, sourceId, UserId, SubuserId, FYear )"
+ " VALUES ( "
+ ledgerNumber
+ ", 'C' "
+ ", '" + salePurchase.BillDate + "' "
+ ", " + party
+ ", " + mode
+ ", '" + util.GetLOVName(salePurchase.BillNumber) + "' "
+ ", '" + salePurchase.particulars + "' "
+ ", " + salePurchase.SalePurchaseAmount
+ ", '" + salePurchase.discount + "'"
+ ", " + salePurchase.transactionType
+ ", " + util.GetUserInsertQry(Util_BLL.User)
+ " ) ";
// sales - party
string mode_qry = " INSERT INTO tblLedger ("
+ " LedgerNumber, drcr, Ldate, AccountMasterId, modeId, BillNumber, Particulars, debit, Discount, sourceId, UserId, SubuserId, FYear )"
+ " VALUES ( "
+ ledgerNumber
+ ", 'D' "
+ ", '" + salePurchase.BillDate + "' "
+ ", " + mode
+ ", " + party
+ ", '" + util.GetLOVName(salePurchase.BillNumber) + "' "
+ ", '" + salePurchase.particulars + "' "
+ ", " + salePurchase.SalePurchaseAmount
+ ", '" + salePurchase.discount + "'"
+ ", " + salePurchase.transactionType
+ ", " + util.GetUserInsertQry(Util_BLL.User)
+ " ) ";
site.Execute(party_qry);
site.Execute(mode_qry);
if (salePurchase.transactionType != TransactionType.PaymentMade && salePurchase.transactionType != TransactionType.PaymentRcd)
{
string tax_qry = "";
for (int i = 0; i < salePurchase.SalePurchaseTaxes.Count; i++)
{
tax_qry = " INSERT INTO tblTax(LedgerNumber, TaxMasterId, TaxAmount, UserID, SubuserId, FYear) "
+ " VALUES ( "
+ ledgerNumber
+ ", " + salePurchase.SalePurchaseTaxes[i].TaxId
+ ", " + salePurchase.SalePurchaseTaxes[i].TaxAmount
+ ", " + util.GetUserInsertQry(Util_BLL.User)
+ " ) ";
site.Execute(tax_qry);
}
SaveProductLedger(site, salePurchase, ledgerNumber);
}
}
private void Confirmar()
{
string HrEntrada = txtHrEntrada.Text + ":00";
string HrSaidaAlmoco = txtHrSaidaAlmoco.Text + ":00";
string HrRetornoAlmoco = txtHrRetornoAlmoco.Text + ":00";
string HrSaida = txtHrSaida.Text + ":00";
if (ValidaHora(HrEntrada))
{
if (ValidaHora(HrSaidaAlmoco))
{
if (_ItemPonto.DiaSemana == DiaSemanda.SABADO || ValidaHora(HrRetornoAlmoco))
{
if (_ItemPonto.DiaSemana == DiaSemanda.SABADO || ValidaHora(HrSaida))
{
_ItemPonto.HoraEntrada = HrEntrada;
_ItemPonto.HoraSaidaAlmoco = HrSaidaAlmoco;
_ItemPonto.HoraRetornoAlmoco = HrRetornoAlmoco;
_ItemPonto.HoraSaida = HrSaida;
if (_ItemPonto.DiaSemana != DiaSemanda.SABADO)
{
#region "Verifica se o retorno do almoço é menor que 01:01:00"
TimeSpan HoraSaidaAlmoco = TimeSpan.Parse(_ItemPonto.HoraSaidaAlmoco);
TimeSpan HoraRetornoAlmoco = TimeSpan.Parse(_ItemPonto.HoraRetornoAlmoco);
TimeSpan Diferenca = new TimeSpan(HoraRetornoAlmoco.Ticks - HoraSaidaAlmoco.Ticks);
#endregion
if (Diferenca.Ticks < TimeSpan.Parse("01:01:00").Ticks&& HoraRetornoAlmoco.Ticks != TimeSpan.Parse("00:00:00").Ticks)
{
if (MessageBox.Show(this, "Intervalo de retorno de almoço é menor que 01:01 hrs." + Environment.NewLine + "Deseja Continuar?", "Marcação de Ponto", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
FinalizaAlteracao();
}
}
else
{
FinalizaAlteracao();
}
}
else
{
FinalizaAlteracao();
}
}
else
{
Util_BLL.MensagemAlerta("Horário de saída inválido!");
txtHrSaida.Focus();
}
}
else
{
Util_BLL.MensagemAlerta("Horário de retorno almoço inválido!");
txtHrRetornoAlmoco.Focus();
}
}
else
{
Util_BLL.MensagemAlerta("Horário de saída almoço inválido!");
txtHrSaidaAlmoco.Focus();
}
}
else
{
Util_BLL.MensagemAlerta("Horário de entrada inválido!");
txtHrEntrada.Focus();
}
}
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);
}
private void CarregarArquivosDiretorio(string FindAno)
{
#region "Buscar Arquivos"
DirectoryInfo Dir = new DirectoryInfo(Caminho.PastaXML);
List <ListItem> ListMes = new List <ListItem>();
List <ListItem> ListAno = new List <ListItem>();
FileInfo[] Files = Dir.GetFiles("*.xml", SearchOption.AllDirectories);
foreach (FileInfo File in Files)
{
string[] strMes = File.Name.Replace(".xml", string.Empty).Split('_');
#region "Valida Mês"
ListItem ItemMes = new ListItem();
ItemMes.Value = strMes[1];
ItemMes.ValueAux = strMes[0];
ItemMes.Text = Util_BLL.RetornaMes(Convert.ToInt32(ItemMes.Value));
ListMes.Add(ItemMes);
//if (ListMes.Count(p => ((ListItem)p).Value == ItemMes.Value) <= 0)
//{
// ListMes.Add(ItemMes);
//}
#endregion
if (string.IsNullOrEmpty(FindAno))
{
#region "Valida Ano"
ListItem ItemAno = new ListItem();
ItemAno.Value = strMes[0];
ItemAno.Text = strMes[0];
if (ListAno.Count(p => ((ListItem)p).Value == ItemAno.Value) <= 0)
{
ListAno.Add(ItemAno);
}
#endregion
}
}
#endregion
#region "Ano"
if (string.IsNullOrEmpty(FindAno))
{
cboAno.DisplayMember = "Text";
cboAno.ValueMember = "Value";
cboAno.DataSource = ListAno;
cboAno.SelectedIndexChanged += cboAno_SelectedIndexChanged;
cboAno.SelectedIndex = cboAno.Items.Count - 1;
FindAno = cboAno.SelectedValue.ToString();
}
#endregion
#region "Mês"
cboMes.DisplayMember = "Text";
cboMes.ValueMember = "Value";
cboMes.DataSource = ListMes.Where(p => ((ListItem)p).ValueAux == FindAno).ToList();
cboMes.SelectedIndex = cboMes.Items.Count - 1;
#endregion
}
private void grvPonto_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
{
#region "Busca registro existente na linha"
object objItem = grvPonto.Rows[e.RowIndex].DataBoundItem;
#endregion
if (objItem != null)
{
#region "Cast para objeto Ponto"
Ponto Item = (Ponto)objItem;
#endregion
#region "Verifica se é feriado"
if (Item.Feriado == 'S')
{
e.CellStyle.BackColor = ColorTranslator.FromHtml("#FCDC6F");
}
else if (Item.Ferias == 'S')
{
e.CellStyle.BackColor = ColorTranslator.FromHtml("#B8E3EF");
}
else if (Item.Folga == 'S')
{
e.CellStyle.BackColor = ColorTranslator.FromHtml("#CFED70");
}
#endregion
#region "Verifica se esta sendo preenchido a coluna colDiaSemana"
if (grvPonto.Columns[e.ColumnIndex].Name == "colDiaSemana")
{
e.Value = Util_BLL.strRetornaDiaSemana(Item.DiaSemana);
}
#endregion
#region "Verifica Lembrete"
if (!string.IsNullOrEmpty(Item.LembreteHoraEntrada) && grvPonto.Columns[e.ColumnIndex].Name == "colHrEntrada")
{
e.CellStyle.BackColor = ColorTranslator.FromHtml("#F59A92");
}
else if (!string.IsNullOrEmpty(Item.LembreteHoraSaidaAlmoco) && grvPonto.Columns[e.ColumnIndex].Name == "colHrSaidaAlmoco")
{
e.CellStyle.BackColor = ColorTranslator.FromHtml("#F59A92");
}
else if (!string.IsNullOrEmpty(Item.LembreteHoraRetornoAlmoco) && grvPonto.Columns[e.ColumnIndex].Name == "colHrRetornoAlmoco")
{
e.CellStyle.BackColor = ColorTranslator.FromHtml("#F59A92");
}
else if (!string.IsNullOrEmpty(Item.LembreteHoraSaida) && grvPonto.Columns[e.ColumnIndex].Name == "colHrSaida")
{
e.CellStyle.BackColor = ColorTranslator.FromHtml("#F59A92");
}
#endregion
#region "Valida colunas para formatar grid"
if (Item.DiaSemana == DiaSemanda.SABADO)
{
if (grvPonto.Columns[e.ColumnIndex].Name == "colHrRetornoAlmoco" || grvPonto.Columns[e.ColumnIndex].Name == "colHrSaida")
{
e.CellStyle.BackColor = Color.Silver;
e.Value = string.Empty;
}
else if (grvPonto.Columns[e.ColumnIndex].Name == "colTotalHora" && e.Value != null)
{
e.CellStyle.ForeColor = ValidaTotalHoras(e.Value.ToString(), Util_BLL._TotalHorasSabado);
}
}
else if (Item.DiaSemana == DiaSemanda.DOMINGO)
{
e.CellStyle.BackColor = Color.Silver;
if (grvPonto.Columns[e.ColumnIndex].Name != "colDia" && grvPonto.Columns[e.ColumnIndex].Name != "colDiaSemana" &&
grvPonto.Columns[e.ColumnIndex].Name != "colTotalHora" && grvPonto.Columns[e.ColumnIndex].Name != "colDiferenca")
{
e.Value = string.Empty;
}
else if (grvPonto.Columns[e.ColumnIndex].Name == "colTotalHora" || grvPonto.Columns[e.ColumnIndex].Name == "colDiferenca")
{
e.CellStyle.Font = new Font(grvPonto.DefaultCellStyle.Font, FontStyle.Bold);
}
}
else
{
if (grvPonto.Columns[e.ColumnIndex].Name == "colTotalHora" && e.Value != null)
{
e.CellStyle.ForeColor = ValidaTotalHoras(e.Value.ToString(), Util_BLL._TotalHorasDia);
}
else if ((grvPonto.Columns[e.ColumnIndex].Name == "colHrSaidaAlmoco" || grvPonto.Columns[e.ColumnIndex].Name == "colHrRetornoAlmoco") && e.Value != null)
{
#region "Verifica se o retorno do almoço é menor que 01:01:00"
TimeSpan HoraSaidaAlmoco = TimeSpan.Parse(Item.HoraSaidaAlmoco);
TimeSpan HoraRetornoAlmoco = TimeSpan.Parse(Item.HoraRetornoAlmoco);
TimeSpan Diferenca = new TimeSpan(HoraRetornoAlmoco.Ticks - HoraSaidaAlmoco.Ticks);
if (Diferenca.Ticks < TimeSpan.Parse("01:01:00").Ticks&& HoraRetornoAlmoco.Ticks != TimeSpan.Parse("00:00:00").Ticks)
{
e.CellStyle.ForeColor = Color.Red;
}
#endregion
}
}
#endregion
}
}
public List <AttendanceEntity> GetAttendance(DBSite site, int yearNo, int theMonth, int theDay, int clasId, bool onlyAbsentees)
{
List <AttendanceEntity> adList = new List <AttendanceEntity>();
AttendanceEntity da = null;
string qry = "";
qry = "SELECT AttendanceId, YearNo, MonthNo, DayNo, HolidayID "
+ ", a.StudentMasterID, StudentName, s.classID, ClassName, s.SectionId, sc.SectionName, s.IdCardNo "
+ " , (SELECT MIN(InTime) FROM tblAttendance "
+ Util_BLL.GetUserWhereCondition(Util_BLL.User, "a")
+ " AND YearNo = " + yearNo
+ " AND MonthNo = " + theMonth
+ " AND DayNo = " + theDay
+ " AND studentmasterid=s.studentMasterID "
+ " GROUP BY StudentMasterID, YearNo, monthno, dayno, userId, FYear )InTime "
+ " , OutTime, Status "
+ ", IsPosted, IsSmsSent "
+ " FROM tblAttendance a "
+ " LEFT OUTER JOIN tblStudentMaster s ON s.StudentMasterID = a.StudentMasterID "
+ " LEFT OUTER JOIN tblClassMaster C ON c.classMasterID = s.classID "
+ " LEFT OUTER JOIN tblSectionMaster sc ON sc.SectionMasterID = s.SectionId "
+ Util_BLL.GetUserWhereCondition(Util_BLL.User, "a")
+ " AND YearNo = " + yearNo
+ " AND MonthNo = " + theMonth
+ " AND DayNo = " + theDay;
if (onlyAbsentees)
{
qry += " AND status = 0 ";
}
if (clasId != -1)
{
qry += " AND c.classMasterID = " + clasId;
}
qry += " AND AttendanceID In (select MAx(AttendanceID) "
+ " FROM tblAttendance b"
+ Util_BLL.GetUserWhereCondition(Util_BLL.User, "b")
+ " GROUP BY StudentMasterID, YearNo, monthno, dayno, userId, FYear ) "
+ " ORDER BY ClassOrder,sectionName, StudentName ";
DataTable dt = site.ExecuteSelect(qry);
foreach (DataRow dr in dt.Rows)
{
da = new AttendanceEntity();
da.AttendanceId = util.CheckNullInt(dr["AttendanceId"]);
da.YearNo = util.CheckNullInt(dr["YearNo"]);
da.MonthNo = util.CheckNullInt(dr["MonthNo"]);
da.DayNo = util.CheckNullInt(dr["DayNo"]);
da.HolidayId = util.CheckNullInt(dr["HolidayID"]);
da.StudentId = util.CheckNullInt(dr["StudentMasterID"]);
da.StudentName = util.CheckNull(dr["StudentName"]);
da.ClassId = util.CheckNullInt(dr["ClassID"]);
da.ClassName = util.CheckNull(dr["ClassName"]);
da.SectionId = util.CheckNullInt(dr["SectionID"]);
da.SectionName = util.CheckNull(dr["SectionName"]);
da.IdcardNo = util.CheckNull(dr["IdCardNo"]);
da.InTime = util.CheckNull(dr["InTime"]);
da.OutTime = util.CheckNull(dr["OutTime"]);
da.IsPosted = util.CheckNullInt(dr["IsPosted"]);
da.IsSMSSent = util.CheckNullInt(dr["IsSMSSent"]);
da.Status = util.CheckNullInt(dr["status"]);
if (da.IsPosted == 0)
{
da.Status = 1;
}
adList.Add(da);
}
return(adList);
}
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);
}
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);
}
