/// <summary>
/// This function returns list of Departments for a specific state, city or village and its locality.
/// </summary>
/// <param name="stateId"></param>
/// <param name="cityId"></param>
/// <param name="villageId"></param>
/// <param name="localityId"></param>
/// <param name="departmentTypeId"></param>
/// <returns></returns>
public static List <Department> GetAllByLocationAndType(int stateId, int cityId, int villageId, int localityId, int departmentTypeId)
{
String SQL = null;
List <Department> list = null;
if (villageId != 0)
{
SQL = String.Format("select * from Departments where StateId='{0}' and CityId='{1}' and VillageId='{2}' and LocalityId='{3}' and DepartmentTypeId='{4}' ", stateId, cityId, villageId, localityId, departmentTypeId);
}
else
{
SQL = String.Format("select * from Departments where StateId='{0}' and CityId='{1}' and LocalityId='{2}' and DepartmentTypeId='{3}' ", stateId, cityId, localityId, departmentTypeId);
}
list = BaseDataAccess.GetRecordsList <Department>(SQL);
foreach (Department info in list)
{
info.State = StateDA.GetDetails(info.StateId);
info.City = CityDA.GetDetails(info.CityId);
if (info.VillageId != 0)
{
info.Village = VillageDA.GetDetails(info.VillageId);
}
info.Locality = LocalityDA.GetDetails(info.LocalityId);
info.DepartmentType = DepartmentTypeDA.GetDetails(info.DepartmentTypeId);
}
return(list);
}
public static ComplaintXStatus GetDetails(int id)
{
ComplaintXStatus info = null;
info = BaseDataAccess.GetRecords <ComplaintXStatus>(String.Format("select * from ComplaintXStatus where Id='{0}' ", id));
return(info);
}
public static AdhaarXContactNumber GetDetailsByAdhaar(string Adhaar)
{
string sql = String.Format("select * from AdhaarXContactNumber where AdhaarNumber='{0}' ", Adhaar);
AdhaarXContactNumber obj = null;
obj = BaseDataAccess.GetRecords <AdhaarXContactNumber>(sql);
return(obj);
}
public static GovernmentOfficial GetDetailsByContactNumber(string ContactNumber)
{
GovernmentOfficial info = null;
info = BaseDataAccess.GetRecords <GovernmentOfficial>(string.Format("select * from GovernmentOfficials where ContactNumber='{0}'", ContactNumber));
return(info);
}
public static OfficialXComplaintsAssigned GetDetails(int id)
{
OfficialXComplaintsAssigned info = null;
info = BaseDataAccess.GetRecords <OfficialXComplaintsAssigned>(String.Format("select * from OfficialXComplaintsAssigned where Id='{0}'", id));
info.GovernmentOfficial = GovernmentOfficialDA.GetDetails(info.OfficialId);
info.Complaint = ComplaintDA.GetDetails(info.ComplaintId);
return(info);
}
public static List <Village> GetAll()
{
List <Village> list = BaseDataAccess.GetRecordsList <Village>(String.Format("select * from villages"));
foreach (Village info in list)
{
info.City = CityDA.GetDetails(info.CityId);
}
return(list);
}
public static List <ComplaintType> GetAllForDepartment(int departmentTypeId)
{
List <ComplaintType> list = BaseDataAccess.GetRecordsList <ComplaintType>(String.Format("select * from ComplaintTypes where DepartmentTypeId='{0}' ", departmentTypeId));
foreach (ComplaintType info in list)
{
info.DepartmentType = DepartmentTypeDA.GetDetails(info.DepartmentTypeId);
}
return(list);
}
public static ComplaintXEscalationLevel GetDetails(int id)
{
ComplaintXEscalationLevel info = null;
info = BaseDataAccess.GetRecords <ComplaintXEscalationLevel>(String.Format("select * from ComplaintXEscalationLevel where Id='{0}'", id));
if (info != null)
{
info.Complaint = ComplaintDA.GetDetails(info.ComplaintId);
}
return(info);
}
public static bool Update(Locality info)
{
if (info.VillageId != 0)
{
return(BaseDataAccess.ExecuteSQL(String.Format("update Localities set CityId='{0}', VillageId='{1}', Name='{2}', PinCode='{3}', Latitude='{4}', Longitude='{5}' where Id='{6}' ", info.CityId, info.VillageId, info.Name, info.Pincode, info.Latitude, info.Longitude, info.Id)));
}
else
{
return(BaseDataAccess.ExecuteSQL(String.Format("update Localities set CityId='{0}', Name='{1}', PinCode='{2}', Latitude='{3}', Longitude='{4}' where Id='{5}' ", info.CityId, info.Name, info.Pincode, info.Latitude, info.Longitude, info.Id)));
}
}
public static bool Add(Locality info)
{
if (info.VillageId != 0)
{
return(BaseDataAccess.ExecuteSQL(String.Format("insert into Localities(CityId, VillageId, Name, PinCode, Latitude, Longitude) values('{0}', '{1}', '{2}', '{3}', '{4}', '{5}' ) ", info.CityId, info.VillageId, info.Name, info.Pincode, info.Latitude, info.Longitude)));
}
else
{
return(BaseDataAccess.ExecuteSQL(String.Format("insert into Localities(CityId, Name, PinCode, Latitude, Longitude) values('{0}', '{1}', '{2}', '{3}', '{4}' ) ", info.CityId, info.Name, info.Pincode, info.Latitude, info.Longitude)));
}
}
public static bool Update(Department info)
{
if (info.VillageId != 0)
{
return(BaseDataAccess.ExecuteSQL(String.Format("update Departments set DepartmentTypeId='{0}', Name='{1}', StateId='{2}',CityId='{3}',VillageId='{4}',LocalityId='{5}',OfficeAddress='{6}',Website='{7}',ContactEmails='{8}',ContactNumbers='{9}', ContactTimings='{10}' where Id='{11}'", info.DepartmentTypeId, info.Name, info.StateId, info.CityId, info.VillageId, info.LocalityId, info.OfficeAddress, info.Website, info.ContactEmails, info.ContactNumbers, info.ContactTimings, info.Id)));
}
else
{
return(BaseDataAccess.ExecuteSQL(String.Format("update Departments set DepartmentTypeId='{0}', Name='{1}', StateId='{2}',CityId='{3}',LocalityId='{4}',OfficeAddress='{5}',Website='{6}',ContactEmails='{7}',ContactNumbers='{8}', ContactTimings='{9}' where Id='{10}'", info.DepartmentTypeId, info.Name, info.StateId, info.CityId, info.LocalityId, info.OfficeAddress, info.Website, info.ContactEmails, info.ContactNumbers, info.ContactTimings, info.Id)));
}
}
public static bool Add(Department info)
{
if (info.VillageId != 0)
{
return(BaseDataAccess.ExecuteSQL(String.Format("insert into Departments(DepartmentTypeId, Name, StateId, CityId, VillageId, LocalityId, OfficeAddress, Website, ContactEmails, ContactNumbers, ContactTimings) values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}')", info.DepartmentTypeId, info.Name, info.StateId, info.CityId, info.VillageId, info.LocalityId, info.OfficeAddress, info.Website, info.ContactEmails, info.ContactNumbers, info.ContactTimings)));
}
else
{
return(BaseDataAccess.ExecuteSQL(String.Format("insert into Departments(DepartmentTypeId, Name, StateId, CityId, LocalityId, OfficeAddress, Website, ContactEmails, ContactNumbers, ContactTimings) values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}')", info.DepartmentTypeId, info.Name, info.StateId, info.CityId, info.LocalityId, info.OfficeAddress, info.Website, info.ContactEmails, info.ContactNumbers, info.ContactTimings)));
}
}
public static bool Update(Complaint info)
{
if (info.VillageId == 0)
{
return(BaseDataAccess.ExecuteSQL(String.Format("Update Complaints set StateId='{0}',CityId='{1}',LocalityId='{2}',DepartmentTypeId='{3}',ComplaintTypeId='{4}', ReportingPersonFirstName='{5}',ReportingPersonLastName='{6}',ReportingPersonAdhaarId='{7}',ReportingPersonContactNumber='{8}',ReportingTime='{9}', CurrentStatusId='{10}', CurrentEscalationNumber='{11}', OTP='{12}', ComplaintVerifiedViaOTP='{13}', IsEscalated='{14}', DepartmentId='{15}' where Id='{16}'", info.StateId, info.CityId, info.LocalityId, info.DepartmentTypeId, info.ComplaintTypeId, info.ReportingPersonFirstName, info.ReportingPersonLastName, info.ReportingPersonAdhaarId, info.ReportingPersonContactNumber, info.ReportingTime, info.CurrentStatusId, info.CurrentEscalationNumber, info.OTP, info.ComplaintVerifiedViaOTP, info.IsEscalated, info.DepartmentId, info.Id)));
}
else
{
return(BaseDataAccess.ExecuteSQL(String.Format("Update Complaints set StateId='{0}',CityId='{1}',VillageId='{2}',LocalityId='{3}',DepartmentTypeId='{4}',ComplaintTypeId='{5}', ReportingPersonFirstName='{6}',ReportingPersonLastName='{7}',ReportingPersonAdhaarId='{8}',ReportingPersonContactNumber='{9}',ReportingTime='{10}', CurrentStatusId='{11}', CurrentEscalationNumber='{12}', OTP='{13}', ComplaintVerifiedViaOTP='{14}', IsEscalated='{15}', DepartmentId='{16}' where Id='{17}'", info.StateId, info.CityId, info.VillageId, info.LocalityId, info.DepartmentTypeId, info.ComplaintTypeId, info.ReportingPersonFirstName, info.ReportingPersonLastName, info.ReportingPersonAdhaarId, info.ReportingPersonContactNumber, info.ReportingTime, info.CurrentStatusId, info.CurrentEscalationNumber, info.OTP, info.ComplaintVerifiedViaOTP, info.IsEscalated, info.DepartmentId, info.Id)));
}
}
public static GovernmentOfficial GetDetails(string officialCode)
{
GovernmentOfficial info = null;
info = BaseDataAccess.GetRecords <GovernmentOfficial>(string.Format("select * from GovernmentOfficials where OfficialCode='{0}'", officialCode));
if (info != null)
{
info.DepartmentDesignation = DepartmentDesignationDA.GetDetails(info.DesignationId);
}
return(info);
}
public static bool Add(DepartmentXAreaIncharge info)
{
if (info.VillageId != 0)
{
return(BaseDataAccess.ExecuteSQL(String.Format("insert into DepartmentXAreaIncharges(DepartmentId, StateId, CityId,VillageId,LocalityId, InchargeId)values('{0}','{1}','{2}','{3}','{4}','{5}')", info.DepartmentId, info.StateId, info.CityId, info.VillageId, info.LocalityId, info.InchargeId)));
}
else
{
return(BaseDataAccess.ExecuteSQL(String.Format("insert into DepartmentXAreaIncharges(DepartmentId, StateId, CityId,LocalityId, InchargeId)values('{0}','{1}','{2}','{3}','{4}')", info.DepartmentId, info.StateId, info.CityId, info.LocalityId, info.InchargeId)));
}
}
public static bool Update(DepartmentXAreaOnDuty info)
{
if (info.VillageId == 0)
{
return(BaseDataAccess.ExecuteSQL(String.Format("update DepartmentXAreaOnDuty set DepartmentId='{0}', StateId='{1}', CityId='{2}', VillageId='{3}',LocalityId='{4}', OnDutyPersonId='{5}',DutyTimeFrom='{6}',DutyTimeTo='{7}' where Id='{8}'", info.DepartmentId, info.StateId, info.CityId, info.VillageId, info.LocalityId, info.OnDutyPersonId, info.DutyTimeFrom.ToShortTimeString(), info.DutyTimeTo.ToShortTimeString(), info.Id)));
}
else
{
return(BaseDataAccess.ExecuteSQL(String.Format("update DepartmentXAreaOnDuty set DepartmentId='{0}', StateId='{1}', CityId='{2}', VillageId='{3}',LocalityId='{4}', OnDutyPersonId='{5}',DutyTimeFrom='{6}',DutyTimeTo='{7}' where Id='{8}'", info.DepartmentId, info.StateId, info.CityId, info.VillageId, info.LocalityId, info.OnDutyPersonId, info.DutyTimeFrom.ToShortTimeString(), info.DutyTimeTo.ToShortTimeString(), info.Id)));
}
}
public static bool Add(DepartmentXAreaOnDuty info)
{
if (info.VillageId == 0)
{
return(BaseDataAccess.ExecuteSQL(String.Format("insert into DepartmentXAreaOnDuty(DepartmentId, StateId, CityId,LocalityId, OnDutyPersonId, DutyTimeFrom, DutyTimeTo)values('{0}','{1}','{2}','{3}','{4}','{5}','{6}')", info.DepartmentId, info.StateId, info.CityId, info.LocalityId, info.OnDutyPersonId, info.DutyTimeFrom.ToShortTimeString(), info.DutyTimeTo.ToShortTimeString())));
}
else
{
return(BaseDataAccess.ExecuteSQL(String.Format("insert into DepartmentXAreaOnDuty(DepartmentId, StateId, CityId,VillageId,LocalityId, OnDutyPersonId, DutyTimeFrom, DutyTimeTo)values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}')", info.DepartmentId, info.StateId, info.CityId, info.VillageId, info.LocalityId, info.OnDutyPersonId, info.DutyTimeFrom.ToShortTimeString(), info.DutyTimeTo.ToShortTimeString())));
}
}
public static List <GovernmentOfficial> GetAllByDesignation(int designationId)
{
List <GovernmentOfficial> list = null;
list = BaseDataAccess.GetRecordsList <GovernmentOfficial>(String.Format("select * from GovernmentOfficials where DesignationId='{0}' ", designationId));
foreach (GovernmentOfficial info in list)
{
info.DepartmentDesignation = DepartmentDesignationDA.GetDetails(info.DesignationId);
}
return(list);
}
public static DepartmentEscalationLevel GetDetailsByDepartmentId(int id, int LevelNumber)
{
DepartmentEscalationLevel info = null;
info = BaseDataAccess.GetRecords <DepartmentEscalationLevel>(String.Format("select * from DepartmentEscalationLevels where DepartmentId='{0}' and LevelNumber = '{1}'", id, LevelNumber));
if (info != null)
{
info.Department = DepartmentDA.GetDetails(info.DepartmentId);
info.GovernmentOfficial = GovernmentOfficialDA.GetDetails(info.DesignatedOfficialId);
}
return(info);
}
public static List <City> GetAllByState(int stateId)
{
List <City> list = null;
list = BaseDataAccess.GetRecordsList <City>(String.Format("select * from Cities where StateId='{0}' ", stateId));
foreach (City info in list)
{
info.State = StateDA.GetDetails(info.StateId);
}
return(list);
}
public static List <Village> GetAllForCity(int cityId)
{
List <Village> list = null;
list = BaseDataAccess.GetRecordsList <Village>(String.Format("select * from villages where CityId='{0}' ", cityId));
foreach (Village info in list)
{
info.City = CityDA.GetDetails(info.CityId);
}
return(list);
}
public static List <ComplaintXEscalationLevel> GetAllByComplaintId(int Id)
{
List <ComplaintXEscalationLevel> list = null;
list = BaseDataAccess.GetRecordsList <ComplaintXEscalationLevel>(String.Format("Select * from ComplaintXEscalationLevel where ComplaintId='{0}'", Id));
foreach (ComplaintXEscalationLevel info in list)
{
info.Complaint = ComplaintDA.GetDetails(info.ComplaintId);
}
return(list);
}
public static DepartmentXOfficial GetDetails(int id)
{
DepartmentXOfficial info = null;
info = BaseDataAccess.GetRecords <DepartmentXOfficial>(String.Format("select * from DepartmentXOfficials where Id='{0}'", id));
if (info != null)
{
info.Department = DepartmentDA.GetDetails(info.DepartmentId);
info.GovernmentOfficial = GovernmentOfficialDA.GetDetails(info.GovernmentOfficialId);
info.DepartmentDesignation = DepartmentDesignationDA.GetDetails(info.DesignationId);
}
return(info);
}
public static List <OfficialXComplaintsAssigned> GetAllByOfficialId(int officialId)
{
List <OfficialXComplaintsAssigned> list = null;
list = BaseDataAccess.GetRecordsList <OfficialXComplaintsAssigned>(string.Format("Select * from OfficialXComplaintsAssigned where OfficialId = '{0}'", officialId));
foreach (OfficialXComplaintsAssigned info in list)
{
info.GovernmentOfficial = GovernmentOfficialDA.GetDetails(info.OfficialId);
info.Complaint = ComplaintDA.GetDetails(info.ComplaintId);
}
return(list);
}
public static List <DepartmentEscalationLevel> GetAllForDepartment(int departmentId)
{
List <DepartmentEscalationLevel> list = new List <DepartmentEscalationLevel>();
list = BaseDataAccess.GetRecordsList <DepartmentEscalationLevel>(String.Format("select * from DepartmentEscalationLevels where DepartmentId='{0}'", departmentId));
foreach (DepartmentEscalationLevel info in list)
{
info.Department = DepartmentDA.GetDetails(info.DepartmentId);
info.GovernmentOfficial = GovernmentOfficialDA.GetDetails(info.DesignatedOfficialId);
}
return(list);
}
public static List <ComplaintXStatus> GetAllByStatus(int StatusId)
{
List <ComplaintXStatus> list = null;
list = BaseDataAccess.GetRecordsList <ComplaintXStatus>(String.Format("select * from ComplaintXStatus where Status='{0}' ", StatusId));
foreach (ComplaintXStatus info in list)
{
info.Complaint = ComplaintDA.GetDetails(info.ComplaintId);
info.ComplaintStatus = ComplaintStatusDA.GetDetails(info.StatusId);
}
return(list);
}
public static List <DepartmentXOfficial> GetAllByUsername(string userName)
{
List <DepartmentXOfficial> list = null;
list = BaseDataAccess.GetRecordsList <DepartmentXOfficial>(String.Format("select * from DepartmentXOfficials where ContactNumber='{0}' ", userName));
foreach (DepartmentXOfficial info in list)
{
info.Department = DepartmentDA.GetDetails(info.DepartmentId);
info.GovernmentOfficial = GovernmentOfficialDA.GetDetails(info.GovernmentOfficialId);
info.DepartmentDesignation = DepartmentDesignationDA.GetDetails(info.DesignationId);
}
return(list);
}
public static Village GetDetails(int id)
{
Village info = BaseDataAccess.GetRecords <Village>(String.Format("select * from Villages where id='{0}' ", id));
if (info != null)
{
info.City = CityDA.GetDetails(info.CityId);
return(info);
}
else
{
return(info);
}
}
public static List <DepartmentXOfficial> GetAllForOfficial(int officialId)
{
List <DepartmentXOfficial> list = null;
list = BaseDataAccess.GetRecordsList <DepartmentXOfficial>(String.Format("select * from DepartmentXOfficials where GovernmentOfficialId='{0}' ", officialId));
foreach (DepartmentXOfficial info in list)
{
info.Department = DepartmentDA.GetDetails(info.DepartmentId);
info.GovernmentOfficial = GovernmentOfficialDA.GetDetails(info.GovernmentOfficialId);
info.DepartmentDesignation = DepartmentDesignationDA.GetDetails(info.DesignationId);
}
return(list);
}
public static Department GetDetails(int id)
{
Department info = null;
info = BaseDataAccess.GetRecords <Department>(String.Format("select * from Departments where Id='{0}' ", id));
if (info != null)
{
return(info);
}
else
{
return(null);
}
}