public static void DeleteOfferMst(clsSubOfferMst mst, DataTable dtStd)
{
SqlConnection connection = new SqlConnection(DataManager.OraConnString());
SqlTransaction transection;
try
{
connection.Open();
transection = connection.BeginTransaction();
SqlCommand command = new SqlCommand();
command.Connection = connection;
command.Transaction = transection;
command.CommandText = "update sub_offer_mst set [DeleteBy]='" + mst.LoginBy +
"',[DeleteDate]=GETDATE() where offer_id= convert(nvarchar,'" + mst.OfferId +
"') ";
command.ExecuteNonQuery();
command.CommandText = @"update SUB_OFFER_DTL set [DeleteBy]='" + mst.LoginBy +
"',[DeleteDate]=GETDATE() where OFFER_ID= '" + mst.OfferId + "' ";
command.ExecuteNonQuery();
command.CommandText = @"DELETE FROM [SUB_Offer_Schedule] WHERE [OfferDtl_ID]='" + mst.OfferId + "' ";
command.ExecuteNonQuery();
foreach (DataRow dr in dtStd.Rows)
{
string DeptVal = "", DeptTypeVal = "", BatchVal = "";
if (!string.IsNullOrEmpty(mst.DeptId))
{
DeptVal = " AND E.DEPT_ID='" + mst.DeptId + "' ";
}
if (!string.IsNullOrEmpty(mst.DeptTypeID))
{
DeptTypeVal = " AND E.DeptTypeID='" + mst.DeptTypeID + "' ";
}
if (!string.IsNullOrEmpty(mst.BatchNo))
{
BatchVal = " AND E.BATCH_NO='" + mst.BatchNo + "' ";
}
command.CommandText = @"UPDATE w
SET w.DeleteBy = '" + mst.LoginBy + "',w.DeleteDate=GETDATE() FROM STD_SUBJECT AS w INNER JOIN dbo.STUDENT_INFO AS E ON w.STUDENT_ID = E.ID WHERE w.STUDENT_ID='" + dr["ID"].ToString() + "' AND w.[SESSIONS]='" + mst.Sessions + "' AND w.STUDY_YEAR='" + mst.StudyYear + "' " + DeptVal + " " + DeptTypeVal + " " + BatchVal + " ";
command.ExecuteNonQuery();
}
transection.Commit();
connection.Close();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public static void CreateOfferMst(clsSubOfferMst mst, DataTable ddtDtl, DataTable dtStd, DataTable dtShedule,
string OfferDepartmentID)
{
SqlConnection connection = new SqlConnection(DataManager.OraConnString());
SqlTransaction transection;
try
{
connection.Open();
transection = connection.BeginTransaction();
SqlCommand command = new SqlCommand();
command.Connection = connection;
command.Transaction = transection;
SqlCommand command1 = new SqlCommand();
command1.Connection = connection;
command1.Transaction = transection;
string FieldDeptTypeID = "",
ValueDeptTypeID = "",
FieldDeptID = "",
ValueDeptID = "",
FieldOfferDept = "",
ValueOfferDept = "";
if (!string.IsNullOrEmpty(mst.DeptTypeID))
{
FieldDeptTypeID = " , DeptTypeID";
ValueDeptTypeID = " , " + mst.DeptTypeID;
}
if (!string.IsNullOrEmpty(mst.DeptId))
{
FieldDeptID = " , dept_id";
ValueDeptID = " , " + mst.DeptId;
}
if (!string.IsNullOrEmpty(OfferDepartmentID))
{
FieldOfferDept = " , OfferDept";
ValueOfferDept = " , '" + OfferDepartmentID + "' ";
}
command.CommandText =
@"insert into sub_offer_mst (offer_id,offer_date,sessions,study_year,batch_no,sections ,[AddBy] ,[AddDate] " +
FieldDeptTypeID + " " + FieldDeptID + " " + FieldOfferDept + ") values ('" + mst.OfferId + "', " +
" convert(datetime,nullif('" + mst.OfferDate + "',''),103), " + " '" + mst.Sessions + "','" +
mst.StudyYear + "','" + mst.BatchNo + "','" + mst.Sections + "','" +
mst.LoginBy + "',GETDATE() " + ValueDeptTypeID + " " + ValueDeptID + " " + ValueOfferDept + ")";
command.ExecuteNonQuery();
foreach (DataRow dr in ddtDtl.Rows)
{
if (dr["check"].ToString() == "1")
{
command.CommandText =
@"insert into sub_offer_dtl (offer_id,subject_id,FacultyID,Section) values ('" + mst.OfferId +
"', '" + dr["subject_id"].ToString() + "' ,'" + dr["FacultyID"].ToString() + "','" +
dr["Section"].ToString() + "') ";
command.ExecuteNonQuery();
command1.CommandText = @"SELECT top(1)[ID] FROM [sub_offer_dtl] ORDER BY ID DESC";
int DtlID = Convert.ToInt32(command1.ExecuteScalar());
if (dtStd != null)
{
foreach (DataRow drr in dtStd.Rows)
{
command.CommandText = @"insert into std_subject(student_id,sessions,Batch,study_year,subject_id,UpdateBy,UpdateDate,FacultyID,Dtl_ID,CheckTime,Improve)
values ('" + drr["ID"].ToString() + "', '" + mst.Sessions + "','" + mst.BatchNo.Replace("''", "'0'") +
"','" + mst.StudyYear + "','" + dr["subject_id"].ToString() +
"','" + mst.LoginBy + "',GETDATE(),'" + dr["FacultyID"].ToString() +
"','" + DtlID + "','0','N')";
command.ExecuteNonQuery();
}
}
}
}
transection.Commit();
connection.Close();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public static void UpdateOfferMst(clsSubOfferMst mst, DataTable dtSubjectOfferDtl, DataTable dtStd, DataTable dtShedule,
DataTable dtOldSub, string OfferDepartmentID)
{
SqlConnection connection = new SqlConnection(DataManager.OraConnString());
SqlTransaction transection;
try
{
//****************** Old Subject Offer Details **********//
DataTable dtOfferDept = IdManager.GetShowDataTable("select * from SUB_OFFER_DTL WHERE OFFER_ID='" + mst.OfferId + "' ");
//**
connection.Open();
transection = connection.BeginTransaction();
SqlCommand command = new SqlCommand();
command.Connection = connection;
command.Transaction = transection;
SqlCommand command1 = new SqlCommand();
command1.Connection = connection;
command1.Transaction = transection;
SqlCommand command2 = new SqlCommand();
command2.Connection = connection;
command2.Transaction = transection;
//********** Suject Offer MSt ******//
string DeptValue = "", OfferDepartmentValue = "";
if (!string.IsNullOrEmpty(mst.DeptId))
{
DeptValue = " , dept_id='" + mst.DeptId + "' ";
}
if (!string.IsNullOrEmpty(OfferDepartmentID))
{
OfferDepartmentValue = " , OfferDept ='" + OfferDepartmentID + "' ";
}
else
{
OfferDepartmentValue = " , OfferDept = NULL ";
}
command.CommandText = @"update sub_offer_mst set offer_date= convert(datetime,nullif('" + mst.OfferDate +
"',''),103), sessions= '" + mst.Sessions + "', study_year='" + mst.StudyYear +
"',batch_no='" + mst.BatchNo + "',sections= '" + mst.Sections + "' ,[UpdateBy]='" +
mst.LoginBy + "',[UpdateDate]=GETDATE() " + DeptValue + " " + OfferDepartmentValue +
" where offer_id= '" + mst.OfferId + "' ";
command.ExecuteNonQuery();
//***** Insert & Update sub_offer_dtl & std_subject **********//
foreach (DataRow dr in dtSubjectOfferDtl.Rows)
{
int DtlID = 0;
//string OfferDept ="", OfferDeptTypeID= "";
//************* Subnject Offer Dtl *********//
string FieldOfferDept = "", FieldOfferDeptTypeID = "", ValOfferDept = "", ValOfferDeptTypeID = "", UpdateDept = "", UpdateDeptType = "";
if (dr["check"].ToString().Equals("1"))
{
DataRow[] rows = dtOfferDept.Select("ID ='" + dr["ID"].ToString().Trim() + "'");
FieldOfferDept = ",OfferDept";
FieldOfferDeptTypeID = ",OfferDeptTypeID";
if (rows.Length > 0)
{
if (!string.IsNullOrEmpty(rows[0]["OfferDept"].ToString()))
{
ValOfferDept = ",'" + rows[0]["OfferDept"].ToString() + "' ";
UpdateDept = ",OfferDept='" + rows[0]["OfferDept"].ToString() + "' ";
}
else
{
ValOfferDept = ", NULL";
}
if (!string.IsNullOrEmpty(rows[0]["OfferDeptTypeID"].ToString()))
{
ValOfferDeptTypeID = ", '" + rows[0]["OfferDeptTypeID"].ToString() + "' ";
UpdateDeptType = ",OfferDeptTypeID='" + rows[0]["OfferDeptTypeID"].ToString() + "'";
}
else
{
ValOfferDeptTypeID = ", NULL";
}
}
else
{
ValOfferDept = ", NULL";
ValOfferDeptTypeID = ", NULL";
}
command1.CommandText = @"select COUNT(*) from SUB_OFFER_DTL t1 where t1.ID=" +
dr["ID"].ToString();
int CheckSubDtl = Convert.ToInt32(command1.ExecuteScalar());
if (CheckSubDtl > 0)
{
command2.CommandText = @"update SUB_OFFER_DTL set FacultyID='" +
dr["FacultyID"].ToString() + "',Section='" +
dr["Section"].ToString() + "' " + UpdateDept + " " + UpdateDeptType +
" where ID='" +
dr["ID"].ToString() + "'";
command2.ExecuteNonQuery();
command1.CommandText = @"SELECT [ID] FROM [sub_offer_dtl] where ID=" + dr["ID"].ToString();
DtlID = Convert.ToInt32(command1.ExecuteScalar());
}
else
{
command.CommandText =
@"insert into sub_offer_dtl (offer_id,subject_id,FacultyID,Section " + FieldOfferDept +
" " + FieldOfferDeptTypeID + ") values ('" + mst.OfferId + "', '" +
dr["subject_id"].ToString() + "' ,'" + dr["FacultyID"].ToString() + "','" +
dr["Section"].ToString() + "' " + ValOfferDept + " " + ValOfferDeptTypeID + ") ";
command.ExecuteNonQuery();
command1.CommandText = @"SELECT top(1)[ID] FROM [sub_offer_dtl] ORDER BY ID DESC";
DtlID = Convert.ToInt32(command1.ExecuteScalar());
}
}
else
{
command1.CommandText = @"SELECT [ID] FROM [sub_offer_dtl] where ID=" + dr["ID"].ToString();
DtlID = Convert.ToInt32(command1.ExecuteScalar());
command.CommandText = @"delete from sub_offer_dtl where ID=" + dr["ID"].ToString();
command.ExecuteNonQuery();
}
// ****** Update Student Subject Choice Tbable ******//
//if (dtStd == null)
//{
command1.CommandText = @"SELECT COUNT(*) FROM [STD_SUBJECT] t1 where t1.[Dtl_ID]='" +
dr["ID"].ToString() + "' AND t1.DeleteBy IS NULL";
int CountStudentSubject = Convert.ToInt32(command1.ExecuteScalar());
if (CountStudentSubject > 0)
{
if (dr["check"].ToString() == "1")
{
// ****************** Update Offer Dtl_ID STD_SUBJECT ****************//
command.CommandText = @"UPDATE [STD_SUBJECT] SET FacultyID='" +
dr["FacultyID"].ToString() + "',[Dtl_ID]='" + DtlID +
"' where [Dtl_ID]='" +
dr["ID"].ToString() + "' AND DeleteBy IS NULL";
command.ExecuteNonQuery();
}
else
{
command.CommandText = @"UPDATE [STD_SUBJECT] SET DeleteBy='" + mst.LoginBy +
"',DeleteDate=GETDATE() where [Dtl_ID]='" +
dr["ID"].ToString() +
"' AND DeleteBy IS NULL";
command.ExecuteNonQuery();
}
}
//}
//********************* Update Shedule Table ******//
if (dtShedule != null)
{
command.CommandText = @"select COUNT(*) from [SUB_Offer_Schedule] where OfferMstID='" +
mst.OfferId + "' and OfferDtl_ID='" + dr["ID"].ToString() + "'";
int Count = Convert.ToInt32(command.ExecuteScalar());
if (Count > 0)
{
if (dr["check"].ToString() == "1")
{
// ****************** STD_SUBJECT && SUB_Offer_Schedule ****************//
command.CommandText = @"UPDATE [SUB_Offer_Schedule] SET OfferDtl_ID='" +
DtlID + "' where OfferMstID='" + mst.OfferId +
"' and OfferDtl_ID='" + dr["ID"].ToString() + "' ";
command.ExecuteNonQuery();
}
else
{
command.CommandText = @"delete from [SUB_Offer_Schedule] where OfferMstID='" +
mst.OfferId + "' and OfferDtl_ID='" + dr["ID"].ToString() + "'";
command.ExecuteNonQuery();
}
}
}
//********* Batch Wise Fix student **************//
// if (dtStd != null)
// {
// //command.CommandText = @"delete from sub_offer_dtl where offer_id='" + mst.OfferId + "'";
// //command.ExecuteNonQuery();
// foreach (DataRow drr in dtStd.Rows)
// {
// string DeptVal = "", DeptTypeVal = "", BatchVal = "";
// if (!string.IsNullOrEmpty(mst.DeptId))
// {
// DeptVal = " AND E.DEPT_ID='" + mst.DeptId + "' ";
// }
// if (!string.IsNullOrEmpty(mst.DeptTypeID))
// {
// DeptTypeVal = " AND E.DeptTypeID='" + mst.DeptTypeID + "' ";
// }
// if (!string.IsNullOrEmpty(mst.BatchNo))
// {
// BatchVal = " AND E.BATCH_NO='" + mst.BatchNo + "' ";
// }
// command2.CommandText =
// @"DELETE w FROM STD_SUBJECT w INNER JOIN STUDENT_INFO E ON E.ID=w.STUDENT_ID AND E.DeleteBy IS NULL WHERE w.STUDENT_ID='" +
// drr["ID"].ToString() + "' AND w.[SESSIONS]='" + mst.Sessions + "' AND w.Dtl_ID='" +
// dr["ID"].ToString() + "' AND w.STUDY_YEAR='" + mst.StudyYear +
// "' AND w.DeleteBy IS NULL " + DeptVal + " " + DeptTypeVal + " " + BatchVal + " ";
// command2.ExecuteNonQuery();
// command.CommandText = @"insert into std_subject (student_id,sessions,Batch,study_year,subject_id,UpdateBy,UpdateDate,FacultyID,Dtl_ID,CheckTime,Improve)
// values
// ('" + drr["ID"].ToString() + "', '" + mst.Sessions + "','" +
// mst.BatchNo.Replace("''", "'0'") +
// "','" + mst.StudyYear + "','" + dr["subject_id"].ToString() + "','" +
// mst.LoginBy + "',GETDATE(),'" + dr["FacultyID"].ToString() + "','" +
// DtlID + "','0','N')";
// command.ExecuteNonQuery();
// }
// }
}
transection.Commit();
connection.Close();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
