internal DataTable GetShowPatientTest(string ID)
{
string Query = @" with tot AS (SELECT t1.[PatientID],t1.[PatientName],convert(nvarchar,t1.[DOB])DOB,t1.Mobile,ISNULL(sum(t3.Fee),0) AS Fee,t2.PayAmount AS PayAmount FROM [TestRequestMst] t1 inner join TestRequestDtls t3 on t3.MstID=t1.ID left join (select tt.PatientID,SUM(tt.PayAmount)PayAmount from Payment tt group by tt.PatientID) t2 on t2.PatientID=t1.ID where t1.ID='" + ID + "' group by t1.[PatientID],t1.[PatientName],t1.[DOB] ,t1.Mobile,t2.PayAmount) select tot.PatientID,tot.PatientName,tot.DOB,tot.Mobile,tot.Fee,tot.PayAmount,(tot.Fee-tot.PayAmount) AS Due from tot";
DataTable dt = DataTransfection.GetShowDataTable(Query, "TestRequestMst");
return(dt);
}
internal string SaveStudentResult(StudentResults studentResults)
{
try
{
string Query = @"INSERT INTO [StudentResult]
([RegID]
,[CourseID]
,[GradeID]
,[Addby]
,[Addate])
VALUES
('" + studentResults.RegNo + "','" + studentResults.Course + "','" + studentResults.Grade + "','Sojol',GETDATE())";
int rowEffect = DataTransfection.ExecuteNonQuery(Query);
if (rowEffect > 0)
{
return("Record is/are Saved Sucessfully....!!");
}
else
{
throw new Exception("Record Save Failed....!!! ");
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
internal DataTable GetShowTestRequestMst(string ID)
{
string Query = @"SELECT [PatientID],[PatientName],CONVERT(NVARCHAR,[DOB],103) AS DOB ,[Mobile] FROM [TestRequestMst] WHERE ID='" + ID + "'";
DataTable dt = DataTransfection.GetShowDataTable(Query, "TestRequestMst");
return(dt);
}
internal DataTable GetShowItemsDetails()
{
string Query = @"SELECT [ID],row_number() over (order by ID) as Serial ,[Name] FROM [Department]";
DataTable dt = DataTransfection.GetShowDataTable(Query, "Department");
return(dt);
}
public ActionResult RegisterStudent(RegisterStudents registerStudent)
{
string AutoID = "";
string DepartMent = DataTransfection.GetShowSingleValueString("Name", "ID", "Department",
registerStudent.DeptID);
int Count = DataTransfection.GetShowSingleValueInt("Count(*)", "Student_Registation");
if (DepartMent.Length > 3)
{
AutoID = DepartMent.Substring(0, 2);
}
else
{
AutoID = DepartMent.Substring(0, 1);
}
AutoID = AutoID.ToUpper() + "-" + DateTime.Now.Year + "-00" + (Count + 1);
string Alart = _registerStudentManager.SaveStudent(registerStudent, AutoID);
ViewBag.Message = Alart;
GetDepartment();
return(View());
}
internal DataTable GetShowSemester()
{
string Query = @"SELECT [ID],row_number() over (order by ID) as Serial ,[Name] FROM [Semester]";
DataTable dt = DataTransfection.GetShowDataTable(Query, "Semester");
return(dt);
}
internal int GetValidationCode(string Code)
{
//string Query = @"SELECT * from [Department] where Code='"+Code+"'";
int dt = DataTransfection.GetShowSingleValueInt("COUNT(*)", "Code", "Department", Code);
return(dt);
}
internal DataTable GetShowRequestPatientTestDetailsReport(string ID)
{
string Query = @"SELECT ROW_NUMBER() OVER(ORDER BY (SELECT t1.ID)) AS ID ,t2.TestName ,t1.[Fee] FROM [TestRequestDtls] t1 inner join TestSetup t2 on t2.ID=t1.TestID WHERE MstID='" + ID + "'";
DataTable dt = DataTransfection.GetShowDataTable(Query, "TestRequestDtls");
return(dt);
}
internal string SaveTeacher(Teacher aTeacher)
{
string Query = @"INSERT INTO [Teacher]
([Name]
,[Address]
,[Email]
,[ContractNo]
,[DesignatioID]
,[DepartmentID]
,[CreditTaken]
,[Addby]
,[Addate])
VALUES
('" + aTeacher.Name + "','" + aTeacher.Address + "','" + aTeacher.Email + "','" + aTeacher.ContractNo + "','" + aTeacher.Designation + "','" + aTeacher.Department + "','" + aTeacher.CreditTaken + "','Sojol',GETDATE())";
int rowEffect = DataTransfection.ExecuteNonQuery(Query);
if (rowEffect > 0)
{
return("Record is/are Saved Sucessfully....!!");
}
else
{
throw new Exception("Record Save Failed....!!! ");
}
}
internal string SaveRoomAllocate(Models.RoomAllocate roomAllocate)
{
try
{
string Query = @"INSERT INTO [ClassRoomAllocate]
([DeptID]
,[CourseID]
,[RoomNo]
,[Day]
,[Fromday]
,[Today]
,[Adby]
,[Addate])
VALUES
('" + roomAllocate.Department + "','" + roomAllocate.CourseID + "','" + roomAllocate.RoomNo + "','" + roomAllocate.Day + "','" + roomAllocate.From + "','" + roomAllocate.To + "','Sojol',GETDATE())";
int rowEffect = DataTransfection.ExecuteNonQuery(Query);
if (rowEffect > 0)
{
return("Record is/are Saved Sucessfully....!!");
}
else
{
throw new Exception("Record Save Failed....!!! ");
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
internal string SaveStudent(Models.RegisterStudents registerStudent, string AutoID)
{
string Query = @"INSERT INTO [Student_Registation]
([RegNo]
,[St_Name]
,[Email]
,[ContractNo]
,[Date]
,[Address]
,[DeptID]
,[Adby]
,[Addate])
VALUES
('" + AutoID + "','" + registerStudent.Name + "','" + registerStudent.Email + "','" + registerStudent.ContractNo + "',GETDATE(),'" + registerStudent.Address + "','" + registerStudent.DeptID + "','Sojol',GETDATE())";
int rowEffect = DataTransfection.ExecuteNonQuery(Query);
if (rowEffect > 0)
{
return("Record is/are Saved Sucessfully....!!");
}
else
{
throw new Exception("Record Save Failed....!!! ");
}
}
internal DataTable GetShowTestTypeSetupDetails()
{
string Query = @"SELECT [ID],row_number() over (order by ID) as Serial ,[TypeName],[AddBy] FROM [TestTypeSetup]";
DataTable dt = DataTransfection.GetShowDataTable(Query, "TestTypeSetup");
return(dt);
}
public DataTable GetShowTestSetupDetais()
{
string Query = @"SELECT t1.[ID],t1.[TestName],t1.[Fee],t2.TypeName FROM [TestSetup] t1 inner join TestTypeSetup t2 on t2.ID=t1.TestTypeID";
DataTable dt = DataTransfection.GetShowDataTable(Query, "TestType");
return(dt);
}
protected void btnSearch_Click(object sender, EventArgs e)
{
try
{
int ID = DataTransfection.GetShowSingleValueInt("ID", "PatientID", "TestRequestMst", txtSearch.Text);
if (ID != 0)
{
hfID.Value = ID.ToString();
DataTable dtMst = aTestRequestManager.GetShowPatientTest(ID.ToString());
if (dtMst.Rows.Count > 0)
{
lblTolatFee.Text = dtMst.Rows[0]["Fee"].ToString();
lblPaidAmount.Text = dtMst.Rows[0]["PayAmount"].ToString();
lblDue.Text = dtMst.Rows[0]["Due"].ToString();
DataTable dt = aTestRequestManager.GetShowRequestPatientTestDetailsReport(ID.ToString());
dgTestHistory.DataSource = dt;
dgTestHistory.DataBind();
lblDue.Visible = lblPaidAmount.Visible = lblTolatFee.Visible = lblDate.Visible = txtPayAmt.Visible = btnPay.Visible = btnClear.Visible = btnPrint.Visible = true;
label12.Visible = label3.Visible = label5.Visible = label22.Visible = label2.Visible = true;
}
}
}
catch (Exception ex)
{
ClientScript.RegisterStartupScript(this.GetType(), "Warning", "alert('" + ex.Message + "');", true);
}
}
internal DataTable GetShowTestWiseReportDetails(string FromDate, string ToDate)
{
string ShowQuery = @"SELECT ROW_NUMBER() OVER(ORDER BY (SELECT t1.[TestID])) AS SL, t2.TestName AS [Test Name],COUNT(t1.[TestID])AS [Total Test]
,sum(t1.[Fee]) AS [Total Amount]
FROM [TestRequestDtls] t1 inner join TestSetup t2 on t2.ID=t1.TestID inner join TestRequestMst t3 on t3.ID=t1.MstID where convert(date,t3.AddDate,103) between convert(date,'" + FromDate + "',103) and convert(date,'" + ToDate + "',103) group by t2.TestName, TestID ";
DataTable dt = DataTransfection.GetShowDataTable(ShowQuery, "TestRequestDtls");
return(dt);
}
internal TestSetup GetTestSetup(string ID)
{
string Query = @"SELECT [ID],[TestName],[Fee],[TestTypeID] FROM [TestSetup] WHERE [ID]='" + ID + "'";
DataTable dt = DataTransfection.GetShowDataTable(Query, "TestType");
if (dt.Rows.Count == 0)
{
return(null);
}
return(new TestSetup(dt.Rows[0]));
}
internal string SaveTestTypeInformation(TestTypeSetup aTestTypeSetup)
{
if (aTestTypeSetup.Name == "")
{
throw new Exception("Enter Test Type Name......!!!!");
}
int Count = DataTransfection.GetShowSingleValueInt("COUNT(*)", "ID", "TestTypeSetup", aTestTypeSetup.ID);
if (Count > 0)
{
return(aTestTypeSetupGetaway.GetUpdateTestSetupInfo(aTestTypeSetup));
}
return(aTestTypeSetupGetaway.GetSaveTestSetupInfo(aTestTypeSetup));
}
public string UpdateTestSetupInformation(TestSetup aTestSetup)
{
string Query = @"UPDATE [TestSetup] SET [TestName] ='" + aTestSetup.TestName + "' ,[Fee] = " + aTestSetup.Fee + " ,[TestTypeID] = '" + aTestSetup.TestTypeID + "' WHERE ID='" + aTestSetup.ID + "'";
int rowEffect = DataTransfection.ExecuteNonQuery(Query);
if (rowEffect > 0)
{
return("Record is/are Update Sucessfully....!!");
}
else
{
throw new Exception("Record Update Failed....!!! ");
}
}
internal string GetUpdateTestSetupInfo(TestTypeSetup aTestTypeSetup)
{
string Query = @"UPDATE [TestTypeSetup] SET [TypeName] ='" + aTestTypeSetup.Name + "' WHERE ID='" + aTestTypeSetup.ID + "'";
int rowEffect = DataTransfection.ExecuteNonQuery(Query);
if (rowEffect > 0)
{
return("Record is/are Update Sucessfully....!!");
}
else
{
throw new Exception("Record Update Failed....!!! ");
}
}
internal string UpdateDepartment(CourseSetup acourse)
{
string Query = @"UPDATE [Course]
SET [Code] ='" + acourse.Code + "' ,[CourseName] ='" + acourse.Name + "',[Credit] ='" + acourse.Credit + "' ,[Description] ='" + acourse.Description + "',[DepartmentID] ='" + acourse.DeptID + "' ,[SemesterID] ='" + acourse.Semester + "' ,[Updateby] ='Sojol',[Update] = GETDATE() WHERE ID='" + acourse.ID + "'";
int rowEffect = DataTransfection.ExecuteNonQuery(Query);
if (rowEffect > 0)
{
return("Record is/are Update Sucessfully....!!");
}
else
{
throw new Exception("Record Update Failed....!!! ");
}
}
internal string GetSaveTestSetupInfo(TestTypeSetup aTestTypeSetup)
{
string Query = @"INSERT INTO [TestTypeSetup]
([TypeName])
VALUES ('" + aTestTypeSetup.Name + "')";
int rowEffect = DataTransfection.ExecuteNonQuery(Query);
if (rowEffect > 0)
{
return("Record is/are Saved Sucessfully....!!");
}
else
{
throw new Exception("Record Save Failed....!!! ");
}
}
internal string SavePaymentInformation(Models.PaymentInfo aPaymentInfo)
{
string Query = @"INSERT INTO [Payment]
([PatientID],[PayAmount])
VALUES
('" + aPaymentInfo.ID + "','" + aPaymentInfo.PaymentAmount.Replace("'", "") + "')";
int rowEffect = DataTransfection.ExecuteNonQuery(Query);
if (rowEffect > 0)
{
return("Record is/are Saved Sucessfully....!!");
}
else
{
throw new Exception("Record Save Failed....!!! ");
}
}
protected void btnSave_Click(object sender, EventArgs e)
{
try
{
TestRequest aTestRequest = new TestRequest();
aTestRequest.PatientName = txtPatientName.Text;
aTestRequest.DateOfBirth = txtDBO.Text;
aTestRequest.MobileNumber = txtMobileNo.Text;
DataTable dtList = (DataTable)ViewState["TableList"];
string Alert = aTestRequestManager.SaveTestRequest(aTestRequest, dtList);
ClientScript.RegisterStartupScript(this.GetType(), "Warning", "alert('" + Alert + "');", true);
hfMstID.Value = DataTransfection.GetShowSingleValueInt("TOP(1)ID", "TestRequestMst order by ID desc").ToString();
TestReport();
btnSave.Enabled = false;
}
catch (Exception ex)
{
ClientScript.RegisterStartupScript(this.GetType(), "Warning", "alert('" + ex.Message + "');", true);
}
}
internal string SaveDepartment(Department aDepartment)
{
string Query = @"INSERT INTO [Department]
([Code]
,[Name]
,[AddBy]
,[AddDate])
VALUES
('" + aDepartment.Code + "','" + aDepartment.Name + "','Sojol',GETDATE())";
int rowEffect = DataTransfection.ExecuteNonQuery(Query);
if (rowEffect > 0)
{
return("Record is/are Saved Sucessfully....!!");
}
else
{
throw new Exception("Record Save Failed....!!! ");
}
}
internal string SaveCourseEnroll(Models.EnrollCourses enrollCourses)
{
string Query = @"INSERT INTO [EnrollCourse]
([RegID]
,[CourseID]
,[Date]
,[Adby]
,[Addate])
VALUES
('" + enrollCourses.RegNo + "','" + enrollCourses.Course + "','" + enrollCourses.Date + "','Sojol',GETDATE())";
int rowEffect = DataTransfection.ExecuteNonQuery(Query);
if (rowEffect > 0)
{
return("Record is/are Saved Sucessfully....!!");
}
else
{
throw new Exception("Record Save Failed....!!! ");
}
}
internal string SaveCourseAssain(Models.CourseAssign courseAssign)
{
string Query = @"INSERT INTO [CourseAssaign]
([DeptID]
,[TechID]
,[CourseID]
,[Credit]
,[Addby]
,[Addate])
VALUES
('" + courseAssign.Department + "','" + courseAssign.Teacher + "','" + courseAssign.CourseID + "','" + courseAssign.Credit + "','Sojol',GETDATE())";
int rowEffect = DataTransfection.ExecuteNonQuery(Query);
if (rowEffect > 0)
{
return("Record is/are Saved Sucessfully....!!");
}
else
{
throw new Exception("Record Save Failed....!!! ");
}
}
internal string SaveDepartment(CourseSetup acourse)
{
string Query = @"INSERT INTO [Course]
([Code]
,[CourseName]
,[Credit]
,[Description]
,[DepartmentID]
,[SemesterID]
,[Addby]
,[Adddate])
VALUES
('" + acourse.Code + "','" + acourse.Name + "','" + acourse.Credit + "','" + acourse.Description + "','" + acourse.DeptID + "','" + acourse.Semester + "','Sojol',GETDATE())";
int rowEffect = DataTransfection.ExecuteNonQuery(Query);
if (rowEffect > 0)
{
return("Record is/are Saved Sucessfully....!!");
}
else
{
throw new Exception("Record Save Failed....!!! ");
}
}
internal int GetValidation(string ID)
{
int dt = DataTransfection.GetShowSingleValueInt("COUNT(*)", "St_Name", "Student_Registation", ID);
return(dt);
}
internal int GetValidation(string ID)
{
int dt = DataTransfection.GetShowSingleValueInt("COUNT(*)", "Code", "Course", ID);
return(dt);
}
internal int GetValidation(string TeacherName)
{
int dt = DataTransfection.GetShowSingleValueInt("COUNT(*)", "Name", "Teacher", TeacherName);
return(dt);
}
