public DataTable GetAllMatterView_English()
{
//string sql = "select M.Matter_Id,M.Matter_number,emp.UserName AS AssignedLawyer, EMP1.UserName ResponsibileLawyer,mt.Matter_Type_Desc, ";
//sql = sql + "st.Staus_Desc ,sg.stage_type_desc,pa.Company_Name ";
//sql = sql + "from Matters M ";
//sql = sql + " left join Matter_Parties MP on(mp.Matter_ID =m.Matter_ID and mp.Role_ID=1) ";
//sql = sql + " left join Parties PA on(PA.Party_ID = mp.Matter_ID ) ";
//sql = sql + " inner join Employess EMP on(M.Assigned_lawyer_ID=emp.Employee_Id) ";
//sql = sql + " inner join Employess EMP1 on(m.Responsibale_Lawyer_ID = EMP1.Employee_Id) ";
//sql = sql + " inner join Matter_Types MT on (mt.Matter_Type_Id = m.Matter_Type_ID) ";
//sql = sql + " inner join Statuses ST on (st.Satus_id = m.Matter_status_ID) ";
//sql = sql + " left join Stages S on(s.Matter_Id = m.Matter_ID) ";
//sql = sql + " left join Stage_Types SG on (sg.stage_type_id = S.Stage_Type_ID) ";
string sql = "select distinct M.Matter_Id,M.Matter_number,emp.UserName AS AssignedLawyer, EMP1.UserName ";
sql = sql + "ResponsibileLawyer,mt.Matter_Type_Desc, st.Staus_Desc , ";
sql = sql + "Company_Name =(select PA.Company_Name from Parties PA where pa.Party_ID = mp.Party_ID and mp.Matter_ID =m.Matter_ID) , ";
sql = sql + "stage_type_desc = substring((SELECT ( '| ' + ms2.stage_type_desc ) FROM Stage_Types ms2 ";
sql = sql + "right join Stages Ho on ms2.stage_type_id = ho.Stage_Type_ID ";
sql = sql + "and ho.Matter_Id = m.Matter_ID ";
sql = sql + "FOR XML PATH( '' )), 3, 1000) ";
sql = sql + "from Matters M ";
sql = sql + "left join Matter_Parties MP on(mp.Matter_ID =m.Matter_ID and mp.Role_ID=1) ";
sql = sql + "left join Parties PA on(PA.Party_ID = mp.Matter_ID ) ";
sql = sql + "inner join Employess EMP on(M.Assigned_lawyer_ID=emp.Employee_Id) ";
sql = sql + "inner join Employess EMP1 on(m.Responsibale_Lawyer_ID = EMP1.Employee_Id) ";
sql = sql + "inner join Matter_Types MT on (mt.Matter_Type_Id = m.Matter_Type_ID) ";
sql = sql + "inner join Statuses ST on (st.Satus_id = m.Matter_status_ID) ";
return(SqlDataAccess.ExecuteDataset(SqlDataAccess.ConnectionString, CommandType.Text, sql).Tables[0]);
}
public DataSet Chart_Data(string Matter_ID)
{
string sql = "SELECT st.stage_type_desc , s.actual_effort,s.weighting,s.exp_start_date,s.exp_end_date,s.Open_Date,s.Close_Date from Stages S ";
sql = sql + "inner join Stage_Types ST on ( st.stage_type_id = S.Stage_Type_ID) and s.Matter_Id='" + Matter_ID + "'";
return(SqlDataAccess.ExecuteDataset(SqlDataAccess.ConnectionString, CommandType.Text, sql));
}
public DataTable GetAlertMailToFallower(int MatterID)
{
string sql = "select distinct emp.Mail_ID from Fallowers F inner join Employess emp on (emp.Employee_Id = f.Employee_Id) ";
sql = sql + "where f.Matter_Id= '" + MatterID + "' ";
return(SqlDataAccess.ExecuteDataset(SqlDataAccess.ConnectionString, CommandType.Text, sql).Tables[0]);
}
public DataTable GetAllMatterView_Arabic()
{
string sql = "select distinct M.Matter_Id,M.Matter_number,emp.UserName AS AssignedLawyer, EMP1.UserName ";
sql = sql + "ResponsibileLawyer,mt.Matter_Type_Desc, st.Staus_Desc , ";
sql = sql + "Company_Name =(select PA.Company_Name from Parties PA where pa.Party_ID = mp.Party_ID and mp.Matter_ID =m.Matter_ID) , ";
sql = sql + "stage_type_desc = substring((SELECT ( '| ' + ms2.stage_type_desc ) FROM Stage_Types ms2 ";
sql = sql + "right join Stages Ho on ms2.stage_type_id = ho.Stage_Type_ID ";
sql = sql + "and ho.Matter_Id = m.Matter_ID ";
sql = sql + "FOR XML PATH( '' )), 3, 1000) ";
sql = sql + "from Matters M ";
sql = sql + "left join Matter_Parties MP on(mp.Matter_ID =m.Matter_ID and mp.Role_ID=1) ";
sql = sql + "left join Parties PA on(PA.Party_ID = mp.Matter_ID ) ";
sql = sql + "inner join Employess EMP on(M.Assigned_lawyer_ID=emp.Employee_Id) ";
sql = sql + "inner join Employess EMP1 on(m.Responsibale_Lawyer_ID = EMP1.Employee_Id) ";
sql = sql + "inner join Matter_Types MT on (mt.Matter_Type_Id = m.Matter_Type_ID) ";
sql = sql + "inner join Statuses ST on (st.Satus_id = m.Matter_status_ID) ";
//string sql = " select M.Matter_Id,m.Matter_Number,(select pty.Company_Name_ar where p.Role_Id='1' ) as Company_Name,Mt.Matter_Type_Desc_ar as Matter_Type_Desc ,EMP.UserName_ar AS AssignedLawyer, ";
//sql = sql + "(SELECT EMP.UserName_ar FROM Employess EMP WHERE M.Responsibale_Lawyer_ID = EMP.Employee_Id)";
//sql = sql + " AS ResponsibileLawyer, st.Staus_Desc_ar as Staus_Desc, sg.stage_type_desc_ar as stage_type_desc,he.Hearing_date from Matters M ";
//sql = sql + " INNER JOIN Matter_Parties P ON (P.Matter_Id = M.Matter_ID) ";
//sql = sql + " INNER JOIN Parties PTY ON (PTY.Party_ID = P.Party_Id) ";
//sql = sql + " inner join Matter_Types MT on (m.Matter_Type_ID = mt.Matter_Type_Id) ";
//sql = sql + "inner join Employess EMP on(m.Assigned_Lawyer_ID= emp.Employee_Id) ";
//sql = sql + "inner join Statuses ST on(m.Matter_Status_id= st.Satus_id) ";
//sql = sql + "left outer join Stages S on (s.Matter_Id = M.Matter_Id) ";
//sql = sql + "left outer join Cases CS on (cs.stage_id = s.Stage_Id) ";
//sql = sql + " left outer join Stage_Types SG on (s.Stage_Type_ID = sg.stage_type_id) ";
//sql = sql + " left outer join Hearings HE on (he.Case_ID = cs.Case_ID) ";
return(SqlDataAccess.ExecuteDataset(SqlDataAccess.ConnectionString, CommandType.Text, sql).Tables[0]);
}
public DataSet Chart_Data(string Matter_ID)
{
string sql = "select COUNT(*) as count ,stagetype='First Instance' from Cases C ";
sql = sql + "inner join Stages S on (c.Stage_ID = S.Stage_ID) ";
sql = sql + "inner join Matters M on (m.Matter_ID = s.Matter_Id) ";
sql = sql + "where S.Stage_Type_ID =1 and M.Matter_ID ='" + Matter_ID + "' ";
sql = sql + "union ";
sql = sql + "select COUNT(*) as count ,stagetype='Appeal' from Cases C ";
sql = sql + "inner join Stages S on (c.Stage_ID = S.Stage_ID) ";
sql = sql + "inner join Matters M on (m.Matter_ID = s.Matter_Id) ";
sql = sql + "where S.Stage_Type_ID =2 and M.Matter_ID ='" + Matter_ID + "' ";
sql = sql + "union ";
sql = sql + "select COUNT(*) as count ,stagetype='Cassation' from Cases C ";
sql = sql + "inner join Stages S on (c.Stage_ID = S.Stage_ID) ";
sql = sql + "inner join Matters M on (m.Matter_ID = s.Matter_Id) ";
sql = sql + "where S.Stage_Type_ID =3 and M.Matter_ID = '" + Matter_ID + "' ";
sql = sql + "union ";
sql = sql + "select COUNT(*) as count ,stagetype='Execution' from Cases C ";
sql = sql + "inner join Stages S on (c.Stage_ID = S.Stage_ID) ";
sql = sql + "inner join Matters M on (m.Matter_ID = s.Matter_Id) ";
sql = sql + "where S.Stage_Type_ID =4 and M.Matter_ID ='" + Matter_ID + "' ";
sql = sql + "union ";
sql = sql + "select COUNT(*) as count ,stagetype='Preliminary' from Cases C ";
sql = sql + "inner join Stages S on (c.Stage_ID = S.Stage_ID) ";
sql = sql + "inner join Matters M on (m.Matter_ID = s.Matter_Id) ";
sql = sql + "where S.Stage_Type_ID =5 and M.Matter_ID ='" + Matter_ID + "' ";
//string sql = "SELECT st.stage_type_desc , s.actual_effort,s.weighting,s.exp_start_date,s.exp_end_date,s.Open_Date,s.Close_Date from Stages S ";
//sql = sql + "inner join Stage_Types ST on ( st.stage_type_id = S.Stage_Id)";
return(SqlDataAccess.ExecuteDataset(SqlDataAccess.ConnectionString, CommandType.Text, sql));
}
public DataTable OpponentDetails(string Matter_id)
{
string sql = "SELECT P.Company_Name FROM Parties P INNER JOIN Matter_Parties MP ON (MP.Party_Id = P.Party_ID) ";
sql = sql + " WHERE MP.Role_Id = 2 and mp.Matter_Id ='" + Matter_id + "'";
return(SqlDataAccess.ExecuteDataset(SqlDataAccess.ConnectionString, CommandType.Text, sql).Tables[0]);
}
public DataTable FallowerGrid(int Matter_id)
{
string sql = "select F.FallowerID, M.Matter_number as Matter_ID ,E.UserName as Employee_Id from Fallowers F ";
sql = sql + "inner join Matters M on (m.Matter_ID= F.Matter_Id) ";
sql = sql + "inner join Employess E on(E.Employee_Id= f.Employee_Id) where F.Matter_Id ='" + Matter_id + "' ";
return(SqlDataAccess.ExecuteDataset(SqlDataAccess.ConnectionString, CommandType.Text, sql).Tables[0]);
}
public DataTable GetSpecficSearch(string Matter_ID)
{
string sql = "select Matter_Id,Matter_Number from Matters ";
sql = sql + "where Matter_Id = '" + Matter_ID + "'";
return(SqlDataAccess.ExecuteDataset(SqlDataAccess.ConnectionString, CommandType.Text, sql).Tables[0]);
}
public DataTable GetAllAlertsArabic()
{
string sql = "select M.Matter_number,A.Due_date,At.Alert_Type_Ar as Alert_Type_En from alerts A ";
sql = sql + "inner join Alert_Types AT on (a.Alert_type_ID = AT.Alert_Type_ID) ";
sql = sql + "inner join Matters M on (m.Matter_ID = A.Matter_ID) ";
sql = sql + "order by Due_date asc ";
return(SqlDataAccess.ExecuteDataset(SqlDataAccess.ConnectionString, CommandType.Text, sql).Tables[0]);
}
public DataTable GetAlertMailtoAssigned_Responsiable(int MatterID)
{
string sql = "select emp.Mail_ID ,emp1.Mail_ID from Matters M ";
sql = sql + "inner join Employess emp on (emp.Employee_Id = m.Assigned_lawyer_ID) ";
sql = sql + "inner join Employess emp1 on (emp1.Employee_Id = m.Responsibale_Lawyer_ID) ";
sql = sql + "where M.Matter_Id='" + MatterID + "' ";
return(SqlDataAccess.ExecuteDataset(SqlDataAccess.ConnectionString, CommandType.Text, sql).Tables[0]);
}
public DataTable ClickCaseGrid(int Case_Id)
{
string sql = "select case_id, Case_number,Case_Type_ID,C.Status_ID,S.Stage_Type_ID,Registration_Date, ";
sql = sql + "End_date, Court_Id,Court_Cleark_ID,Client_Capcity_ID,Opponents_Capcity_ID,Judge_Id, ";
sql = sql + "Department_Id,Hall_number from Cases C ";
sql = sql + "inner join Stages S on (s.Stage_ID =c.Stage_ID) ";
sql = sql + "where Case_ID ='" + Case_Id + "' AND c.isdelete = 0 ";
return(SqlDataAccess.ExecuteDataset(SqlDataAccess.ConnectionString, CommandType.Text, sql).Tables[0]);
}
public DataTable AlertGridArabic()
{
string sql = "select a.Alert_ID, m.Matter_ID,m.Matter_number,AT.Alert_Type_En,A.Due_date,EMP.UserName as ConcernEmp, EMP1.UserName createdby,a.Date_Created,A.Discription from Alerts A ";
sql = sql + "right join Matters M on (m.Matter_ID = A.Matter_ID) ";
sql = sql + "left join alert_types AT on (AT.Alert_type_ID = A.Alert_Type_ID) ";
sql = sql + "left join Employess EMP on (emp.Employee_Id =a.Concern_emp_ID) ";
sql = sql + "left join Employess EMP1 on (EMP1.Employee_Id = A.Created_Emp_ID) ";
return(SqlDataAccess.ExecuteDataset(SqlDataAccess.ConnectionString, CommandType.Text, sql).Tables[0]);
}
public DataTable GetMatterDetails(string Matter_Number)
{
string sql = "select m.Matter_ID,m.Matter_number,p.Company_Name,emp.UserName as REP,EMP1.UserName as Assigned,mt.Matter_Type_Desc,m.Date_Opend ,M.assigned_ID, m.superviser_ID,m.MatteType_ID,m.Client_Name from Matter_Details M ";
sql = sql + "inner join Parties P on (m.Matter_ID = p.Party_ID) ";
sql = sql + "inner join Matter_Types MT on(mt.Matter_Type_Id = m.MatteType_ID) ";
sql = sql + "inner join Employess EMP on (emp.Employee_Id = m.superviser_ID) ";
sql = sql + "inner join Employess EMP1 on (EMP1.Employee_Id = m.assigned_ID) ";
sql = sql + "where M.Matter_number= '" + Matter_Number + "' ";
return(SqlDataAccess.ExecuteDataset(SqlDataAccess.ConnectionString, CommandType.Text, sql).Tables[0]);
}
public DataTable PartyDetails_Arabic(int Matter_ID)
{
string sql = "select MP.P_ID ,MP.Ranking,m.Matter_number,p.Company_Name_ar as Company_Name,pt.Party_Type_Desc_Ar as Party_Type_Desc_En,rt.Role_Type_ar as Role_Type_En from Matters M ";
sql = sql + "inner join Matter_Parties MP on(m.Matter_ID= mp.Matter_Id) ";
sql = sql + "inner join Party_Types PT on(pt.Party_Type_Id= mp.party_Type_Id) ";
sql = sql + "inner join Roles_Party RT on(rt.Role_Id = mp.Role_Id)";
sql = sql + "inner join Parties P on(p.Party_ID = mp.Party_Id)";
sql = sql + " WHERE M.Matter_id= '" + Matter_ID + "' order by Ranking ";
return(SqlDataAccess.ExecuteDataset(SqlDataAccess.ConnectionString, CommandType.Text, sql).Tables[0]);
}
public DataTable POAGRID_Arebic(int Matter_ID)
{
string sql = "select p.POA_id, pt.Poa_Types_desc_ar as Poa_Types_desc,p.Issue_date,pil.Location_desc_ar as Location_desc,p.Notes from POAs P";
sql = sql + "inner join POA_Types PT on (p.POA_Type_ID = pt.Poa_Type_Id) ";
sql = sql + "inner join POA_Issue_Location PIL on (pil.Location_id = p.POA_Issue_Location) ";
sql = sql + "inner join Parties PA on(pa.Party_ID = p.Party_ID) ";
sql = sql + "inner join Matter_Parties MP on(mp.Party_ID = Pa.Party_ID) ";
sql = sql + "and mp.Matter_ID ='" + Matter_ID + "' and mp.Role_ID=1 ";
return(SqlDataAccess.ExecuteDataset(SqlDataAccess.ConnectionString, CommandType.Text, sql).Tables[0]);
}
public DataTable ViewStage(string MatterID)
{
string sql = "select s.Stage_ID,s.Matter_Id, st.stage_type_desc,sta.Staus_Desc,s.Description,s.Open_date,s.Close_date,s.Exp_Start_Date,s.Exp_End_date,s.weighting,s.Actual_Effort ";
sql = sql + "from Stages S ";
sql = sql + " inner join Stage_Types ST on (s.Stage_Type_ID = st.stage_type_id) ";
sql = sql + " inner join Statuses STA on (s.Status_ID = sta.Satus_id) ";
sql = sql + " where ";
sql = sql + "s.Matter_Id = '" + MatterID + "' and isdelete = 0 order by s.stage_type_id ";
return(SqlDataAccess.ExecuteDataset(SqlDataAccess.ConnectionString, CommandType.Text, sql).Tables[0]);
}
public DataTable HearingGridArabic()
{
string sql = "select H.Hearing_ID,c.Case_number,h.Hearing_date,ho.Hearing_Outcome_Desc_AR as Hearing_Outcome_Desc_EN , ";
sql = sql + " ps.Pleading_status_desc_ar as Pleading_status_desc_en,hs.staus_desc_ar as staus_desc from Hearings H ";
sql = sql + "inner join Hearing_status HS on (HS.status_id = H.Hearing_Staus_Id) ";
sql = sql + "inner join Cases C on (C.Case_ID = h.Case_ID)";
sql = sql + "inner join Hearings_outcome HO on (Ho.Hearing_Outcome_ID = H.Hearing_outcome_ID) ";
sql = sql + "inner join Pleadings P on (h.Hearing_ID = p.Hearing_ID) ";
sql = sql + "inner join Pleading_Status PS on (p.Pleading_status_id =PS.Pleadings_Status_ID) ";
return(SqlDataAccess.ExecuteDataset(SqlDataAccess.ConnectionString, CommandType.Text, sql).Tables[0]);
}
public DataTable CloseMatterDetails(string Matter_Number)
{
string sql = "select m.Matter_ID,m.Matter_number,p.Company_Name,emp.UserName as REP,EMP1.UserName as Assigned,mt.Matter_Type_Desc,F.Office_Desc_En ,m.Open_date from Matters M ";
sql = sql + "inner join Parties P on (m.Matter_ID = p.Party_ID) ";
sql = sql + "inner join Matter_Types MT on(mt.Matter_Type_Id = m.Matter_Type_ID) ";
sql = sql + "inner join Employess EMP on (emp.Employee_Id = m.Responsibale_Lawyer_ID) ";
sql = sql + "inner join Employess EMP1 on (EMP1.Employee_Id = m.Assigned_lawyer_ID) ";
sql = sql + "inner join Offices F on(F.OFFICE_ID = m.Office_ID)";
sql = sql + "where M.Matter_number= '" + Matter_Number + "' ";
return(SqlDataAccess.ExecuteDataset(SqlDataAccess.ConnectionString, CommandType.Text, sql).Tables[0]);
}
public DataTable HearingGridEnglish()
{
string sql = "select H.Hearing_ID,c.Case_number,h.Hearing_date,ho.Hearing_Outcome_Desc_EN as Hearing_Outcome_Desc_EN , ";
sql = sql + " ps.Pleading_status_desc_en,hs.staus_desc as staus_desc from Cases C ";
sql = sql + "left join Hearings H on (C.Case_ID = h.Case_ID) ";
sql = sql + "left join Hearings_outcome HO on(ho.Hearing_Outcome_ID=h.Hearing_outcome_ID) ";
sql = sql + "left join Hearing_status HS on (hs.status_id = h.Hearing_Staus_Id) ";
sql = sql + "left join Pleadings P on (p.Hearing_ID = h.Hearing_ID) ";
sql = sql + "left join Pleading_Status PS on (ps.Pleadings_Status_ID = p.Pleading_status_id) ";
return(SqlDataAccess.ExecuteDataset(SqlDataAccess.ConnectionString, CommandType.Text, sql).Tables[0]);
}
public DataTable GetAllMatterView(string Matter_ID)
{
string sql = "SELECT O.Matter_Id,O.Matter_number,PS.Company_Name,PS.Fisrt_Name ,ES.UserName As RLAWYER,stage_type_desc,HE.Hearing_Date,HE.Hearing_Notes,(SELECT ES.UserName FROM Employess ES WHERE O.Responsiable_Lawyer_Id = ES.Employee_Id) AS EMP2 ";
sql = sql + "FROM Matters as O ";
sql = sql + " INNER JOIN Persons PS ON (O.Client_Id = PS.Person_Id)";
sql = sql + "INNER JOIN Employess ES ON(O.Assigned_Lawyer_Id = ES.Employee_Id)";
sql = sql + "INNER JOIN Stages S ON (s.Matter_Id = o.Matter_Id)";
sql = sql + "INNER JOIN Stage_Types ST ON (ST.stage_type_id = s.Stage_ID)";
sql = sql + "INNER JOIN Cases CS ON(CS.Stage_ID = S.Stage_Id)";
sql = sql + "INNER JOIN Hearings HE ON (CS.Case_Id = HE.Case_Id)";
sql = sql + "WHERE o.Matter_Id= '" + Matter_ID + "'";
return(SqlDataAccess.ExecuteDataset(SqlDataAccess.ConnectionString, CommandType.Text, sql).Tables[0]);
}
public DataTable Case_MatterView(string Matter_ID)
{
string sql = " SELECT o.Matter_Id,o.Matter_number, stage_type_desc as CURRENT_STAGE, CS.case_number as NUMBER,STA.Staus_Desc AS STATUS,CONVERT(VARCHAR(10),HE.Hearing_Date,110)AS NEXT_HEARING,HE.Hearings_Notes AS HERAING_DESP ";
sql = sql + " , cs.Case_ID,He.Hearing_ID,HS.staus_desc FROM Matters as O ";
sql = sql + "INNER JOIN Stages S ON (s.Matter_Id = o.Matter_Id) ";
sql = sql + "INNER JOIN Stage_Types ST ON (ST.stage_type_id = s.Stage_Type_ID) ";
sql = sql + "INNER JOIN Cases CS ON(CS.Stage_ID = S.Stage_Id) ";
sql = sql + "INNER JOIN Hearings HE ON (CS.Case_Id = HE.Case_Id) ";
sql = sql + "INNER join Hearing_status HS on(hs.status_id =he.Hearing_Staus_Id)";
sql = sql + "INNER JOIN Statuses STA ON (S.Status_Id = STA.Satus_id) ";
sql = sql + "WHERE o.Matter_Id= '" + Matter_ID + "'";
return(SqlDataAccess.ExecuteDataset(SqlDataAccess.ConnectionString, CommandType.Text, sql).Tables[0]);
}
public DataTable MatterView()
{
string sql = " select M.Matter_Id,M.Matter_number,emp.UserName AS AssignedLawyer,m.Open_date, ";
sql = sql + "EMP1.UserName ResponsibileLawyer,mt.Matter_Type_Desc, st.Staus_Desc ,pa.Company_Name as Clientname ";
sql = sql + "from Matters M ";
sql = sql + "left join Matter_Parties MP on(mp.Matter_ID =m.Matter_ID and mp.Role_ID=1) ";
sql = sql + "left join Parties PA on(PA.Party_ID = mp.Matter_ID ) ";
sql = sql + "inner join Employess EMP on(M.Assigned_lawyer_ID=emp.Employee_Id) ";
sql = sql + "inner join Employess EMP1 on(m.Responsibale_Lawyer_ID = EMP1.Employee_Id)";
sql = sql + "inner join Matter_Types MT on (mt.Matter_Type_Id = m.Matter_Type_ID) ";
sql = sql + "inner join Statuses ST on (st.Satus_id = m.Matter_status_ID) ";
return(SqlDataAccess.ExecuteDataset(SqlDataAccess.ConnectionString, CommandType.Text, sql).Tables[0]);
}
public DataTable HearingDetails(int HearingID)
{
string sql = "select H.Hearing_ID,h.Case_ID,c.Case_number, h.Hearing_date,h.Hearing_outcome_ID,h.Hearing_Staus_Id, ";
sql = sql + "h.Attend_Empl_Id,h.Hearings_Notes,p.Pleading_status_id,p.Reviewed_Date,p.Reviewed_by,p.Created_date, ";
sql = sql + "h.Hearing_outcome_ID,h.Decision_Details,j.Judgement_OutCome_ID,j.Date_Judgement, ";
sql = sql + "h.Client_Docket,h.oppenet_Docket,j.Judgement_doc,p.Pleading_document ";
sql = sql + "from Hearings H ";
sql = sql + "left join Cases C on (C.Case_ID = h.Case_ID) ";
sql = sql + "left outer join Pleadings P on (h.Hearing_ID = p.Hearing_ID) ";
sql = sql + "left outer join Judgement J on(j.Heraings_ID = h.Hearing_ID) ";
sql = sql + "where h.Hearing_ID='" + HearingID + "' ";
return(SqlDataAccess.ExecuteDataset(SqlDataAccess.ConnectionString, CommandType.Text, sql).Tables[0]);
}
public DataTable PrintMaterListArabic()
{
string sql = "select M.Matter_Id,M.Matter_number,emp.UserName_ar AS Assigned, EMP1.UserName_ar Supervier,mt.Matter_Type_Desc_ar as Matter_Type_Desc, ";
sql = sql + "st.Staus_Desc_ar as Staus_Desc ,sg.stage_type_desc_ar as stage_type_desc ";
sql = sql + "from Matters M ";
sql = sql + "inner join Employess EMP on(M.Assigned_lawyer_ID=emp.Employee_Id) ";
sql = sql + "inner join Employess EMP1 on(m.Responsibale_Lawyer_ID = EMP1.Employee_Id) ";
sql = sql + "inner join Matter_Types MT on (mt.Matter_Type_Id = m.Matter_Type_ID) ";
sql = sql + "inner join Statuses ST on (st.Satus_id = m.Matter_status_ID) ";
sql = sql + "left join Stages S on(s.Matter_Id = m.Matter_ID) ";
sql = sql + "left join Stage_Types SG on (sg.stage_type_id = S.Stage_Type_ID) ";
sql = sql + "order by s.stage_type_id ";
return(SqlDataAccess.ExecuteDataset(SqlDataAccess.ConnectionString, CommandType.Text, sql).Tables[0]);
}
public DataTable CaseGridArabic()
{
string sql = "select c.Case_ID ,c.Case_number, ct.Case_Type_desc,sta.Staus_Desc, ";
sql = sql + "st.stage_type_desc,c.Registration_Date,c.End_date, ";
sql = sql + "Hearing_Outcome_Desc_EN = substring((SELECT ( ' | ' + ho.Hearing_Outcome_Desc_EN ) FROM Hearings ms2 ";
sql = sql + "left outer join Hearings_outcome Ho on ms2.Hearing_Outcome_ID = ho.Hearing_outcome_ID ";
sql = sql + "and c.Case_ID= ms2.Case_ID ";
sql = sql + "FOR XML PATH( '' )), 3, 1000) ";
sql = sql + "from Cases C ";
sql = sql + "left join Case_Types CT on ct.Case_Type_ID = c.Case_Type_ID ";
sql = sql + "left join Stage_Types ST on st.stage_type_id = c.Stage_ID ";
sql = sql + "left join Statuses STA on sta.Satus_id = c.Status_ID ";
return(SqlDataAccess.ExecuteDataset(SqlDataAccess.ConnectionString, CommandType.Text, sql).Tables[0]);
}
public static List <CodeTable> GetCodeTable(string tableName, string iId, string nvName)
{
try
{
string query = "select " + (iId == null ? "iId" : iId) + " as iId, " + (nvName == null ? "nvName" : nvName) + " as nvName from tbl_code_" + tableName;
query += " where iSysRowStatus=1";
DataSet ds = SqlDataAccess.ExecuteDataset(query);
DataTable dt = ds.Tables[0];
return(ObjectGenerator <CodeTable> .GeneratListFromDataRowCollection(ds.Tables[0].Rows));
}
catch (Exception ex)
{
Log.ExceptionLog(ex.Message, "GetCodeTable");
return(null);
}
}
public DataTable GetSpecficSearch(string Matter_ID)
{
string sql = "select M.Matter_Id,m.Matter_Number,(select pty.Company_Name where p.Role_Id='1' ) as Company_Name,Mt.Matter_Type_Desc ,EMP.UserName AS AssignedLawyer,M.open_date, ";
sql = sql + "m.Description,(select pty.Company_Name where p.Role_Id='2' ) as Oppnents, OFFC.Office_Desc_En as offc_desc , ";
sql = sql + "(SELECT EMP.UserName FROM Employess EMP WHERE M.Responsibale_Lawyer_ID = EMP.Employee_Id) AS ResponsibileLawyer,st.Staus_Desc from Matters M ";
sql = sql + "inner join Matter_Types MT on (m.Matter_Type_ID = mt.Matter_Type_Id) ";
sql = sql + "inner join Employess EMP on(m.Assigned_Lawyer_ID= emp.Employee_Id) ";
sql = sql + "inner join Statuses ST on(m.Matter_Status_id= st.Satus_id) ";
sql = sql + "INNER JOIN Offices OFFC ON(M.office_id = OFFC.Office_ID) ";
sql = sql + "INNER JOIN Matter_Parties P ON (P.Matter_Id = M.Matter_ID) ";
sql = sql + " INNER JOIN Parties PTY ON (PTY.Party_ID = P.Party_Id) ";
sql = sql + "where M.Matter_Id = '" + Matter_ID + "' order by company_name desc";
return(SqlDataAccess.ExecuteDataset(SqlDataAccess.ConnectionString, CommandType.Text, sql).Tables[0]);
}
public DataTable DepartmentArabic()
{
string sql = "select Department_ID, Department_name_AR as Department_Name_EN from Departments";
return(SqlDataAccess.ExecuteDataset(SqlDataAccess.ConnectionString, CommandType.Text, sql).Tables[0]);
}
public DataTable CasetypeEnglish()
{
string sql = " select Case_Type_ID, Case_Type_desc from Case_Types";
return(SqlDataAccess.ExecuteDataset(SqlDataAccess.ConnectionString, CommandType.Text, sql).Tables[0]);
}
public DataTable GetAllStatusArabic()
{
string sql = "select Satus_id,Staus_Desc_ar as Staus_Desc from Statuses";
return(SqlDataAccess.ExecuteDataset(SqlDataAccess.ConnectionString, CommandType.Text, sql).Tables[0]);
}