public static DatabaseStatusModel GetStatus(DataSet ds)
{
List <DatabaseStatusModel> dataList = BaseReflection.DatasetBDToObjectList <DatabaseStatusModel>(ds);
DatabaseStatusModel model = (dataList != null && dataList.Count > 0) ? dataList[0] : new DatabaseStatusModel();
return(model);
}
public static List <DashboardModel> LisstOfBranches()
{
string query = "SELECT branchID, name FROM [dbo].[Branch]";
DataSet ds = Connection.connectSQLServerQuery(query);
return(BaseReflection.DatasetBDToObjectList <DashboardModel>(ds));
}
public ServantModel GetServantDetails(int id)
{
List <ServantModel> partLst = new List <ServantModel>();
ServantModel part = new ServantModel();
//string query = "Select * from [dbo].[CLPParticipant] WHERE Memberid = " + id;
string query = "Select prt.id, prt.memberId, prt.CLPid as CLPId, prt.ServiceId as ServiceId, mem.LastName + ', ' + mem.FirstName as name from [dbo].[CLPServants] prt " +
"Inner Join[dbo].[Member] mem ON prt.memberId = mem.id WHERE prt.memberId = " + id;
DataSet ds = Connection.connectSQLServerQuery(query);
partLst = BaseReflection.DatasetBDToObjectList <ServantModel>(ds);
if (partLst.Count > 0)
{
part = new ServantModel
{
id = partLst[0].id,
memberId = partLst[0].memberId,
name = partLst[0].name,
CLPId = partLst[0].CLPId,
ServiceId = partLst[0].ServiceId
};
}
//part.MemberList = ParticipantData.GetMemberParticipantListModify();
//GenericList model = (GroupList != null && GroupList.Count > 0) ? GroupList[0] : null;
return(part);
}
public static List <StockResponse> LisstOfStockOut(int itemNum)
{
string query = "SELECT TOP 5 b.name AS itemname,SUM(a.TranOutwards) as qty FROM [dbo].[Transactions] a " +
" JOIN [dbo].[Products] b ON b.id = a.TranItemNumber " +
" WHERE b.branch = " + itemNum + " AND a.TranMode = 2 GROUP BY b.name,b.sysModified ORDER BY qty DESC";
DataSet ds = Connection.connectSQLServerQuery(query);
return(BaseReflection.DatasetBDToObjectList <StockResponse>(ds));
}
public static List <GenericList> GetCLPList()
{
List <GenericList> CLPList = new List <GenericList>();
string query = "Select CLPId as id, clpName as name " +
"from [dbo].[CLP] ORDER BY clpName ";
DataSet ds = Connection.connectSQLServerQuery(query);
CLPList = BaseReflection.DatasetBDToObjectList <GenericList>(ds);
return(CLPList);
}
public static List <GenericList> GetServiceList()
{
List <GenericList> ServiceList = new List <GenericList>();
string query = "Select id, ServantTask as name " +
"from [dbo].[CLPService] ORDER BY ServantTask ";
DataSet ds = Connection.connectSQLServerQuery(query);
ServiceList = BaseReflection.DatasetBDToObjectList <GenericList>(ds);
return(ServiceList);
}
public static List <GenericList> GetMemberList()
{
List <GenericList> MemberList = new List <GenericList>();
string query = "Select mem.id, mem.LastName + ', ' + mem.FirstName as name " +
"from[dbo].[Member] mem ";
DataSet ds = Connection.connectSQLServerQuery(query);
MemberList = BaseReflection.DatasetBDToObjectList <GenericList>(ds);
return(MemberList);
}
//public static List<GenericList> GetMemberParticipantListModify()
//{
// List<GenericList> MemberParticipantList = new List<GenericList>();
// string query = "Select mem.id, mem.LastName + ', ' + mem.FirstName as name " +
// "from[dbo].[Member] mem " +
// "INNER JOIN (SELECT * FROM[dbo].[CLP] clp INNER JOIN[dbo].[CLPStatus] st ON clp.clpStatus = st.id WHERE st.Name LIKE '%On Going%' OR st.Name LIKE '%to be%') clp ON mem.CLPid = clp.CLPId " +
// "WHERE mem.Position = 5 AND mem.id NOT IN (SELECT memberId FROM [dbo].[CLPParticipant])";
// query = "";
// DataSet ds = Connection.connectSQLServerQuery(query);
// MemberParticipantList = BaseReflection.DatasetBDToObjectList<GenericList>(ds);
// return MemberParticipantList;
//}
public static List <GenericList> GetGroupList()
{
List <GenericList> GroupList = new List <GenericList>();
string query = "Select id, name from [dbo].[CLPGroupings] order by id";
DataSet ds = Connection.connectSQLServerQuery(query);
GroupList = BaseReflection.DatasetBDToObjectList <GenericList>(ds);
//GenericList model = (GroupList != null && GroupList.Count > 0) ? GroupList[0] : null;
return(GroupList);
}
public List <GenericList> GetSponsorList()
{
List <GenericList> sponsors = new List <GenericList>();
string query = "Select id, LastName + ', ' + FirstName as name FROM [dbo].[Member] ORDER BY LastName";
DataSet ds = Connection.connectSQLServerQuery(query);
sponsors = BaseReflection.DatasetBDToObjectList <GenericList>(ds);
GenericList model = (sponsors != null && sponsors.Count > 0) ? sponsors[0] : null;
return(sponsors);
}
public List <GenericList> GetAcccessTypeList()
{
List <GenericList> AcccessTypeList = new List <GenericList>();
string sql = "Select id, name from [AccessType] order by id desc";
DataSet ds = Connection.connectSQLServerQuery(sql);
AcccessTypeList = BaseReflection.DatasetBDToObjectList <GenericList>(ds);
GenericList model = (AcccessTypeList != null && AcccessTypeList.Count > 0) ? AcccessTypeList[0] : null;
return(AcccessTypeList);
}
public List <GenericList> GetStatusList()
{
List <GenericList> StatusList = new List <GenericList>();
string sql = "Select id, name from [statusGeneric] order by id";
DataSet ds = Connection.connectSQLServerQuery(sql);
StatusList = BaseReflection.DatasetBDToObjectList <GenericList>(ds);
GenericList model = (StatusList != null && StatusList.Count > 0) ? StatusList[0] : null;
return(StatusList);
}
public List <GenericList> GetPositionList()
{
List <GenericList> PositionList = new List <GenericList>();
string query = "Select id, name from [Position] order by id";
DataSet ds = Connection.connectSQLServerQuery(query);
PositionList = BaseReflection.DatasetBDToObjectList <GenericList>(ds);
GenericList model = (PositionList != null && PositionList.Count > 0) ? PositionList[0] : null;
return(PositionList);
}
public List <GenericList> GetChapterList()
{
List <GenericList> memlist = new List <GenericList>();
string sql = "Select id, name from [ChapterList] order by name";
DataSet ds = Connection.connectSQLServerQuery(sql);
memlist = BaseReflection.DatasetBDToObjectList <GenericList>(ds);
GenericList model = (memlist != null && memlist.Count > 0) ? memlist[0] : null;
return(memlist);
}
private bool CheckSponsorIfExist(int id)
{
string query = string.Empty;
List <MemberModel> memlist = new List <MemberModel>();
if (id != 0)
{
query = "SELECT * FROM [SponsorList] WHERE id = " + id + "";
}
DataSet ds = Connection.connectSQLServerQuery(query);
memlist = BaseReflection.DatasetBDToObjectList <MemberModel>(ds);
return(memlist.Count > 0);
}
public ResponseModel SaveSponsor(MemberModel modl)
{
ResponseModel ans = new ResponseModel();
string sqlQuery = string.Empty;
try
{
if (!CheckSponsorIfExist(modl.SponsorId))
{
if (modl.SponsorId != 0)
{
string query = string.Empty;
List <MemberModel> member = new List <MemberModel>();
query = "SELECT * FROM [Member] WHERE id = " + modl.SponsorId + "";
DataSet ds = Connection.connectSQLServerQuery(query);
member = BaseReflection.DatasetBDToObjectList <MemberModel>(ds);
using (SqlConnection db = new SqlConnection(Connection.connString))
{
sqlQuery = "Insert Into [SponsorList] ([id], [FirstName], [LastName]) " +
"Values(@id, @FirstName, @LastName)";
var x = db.Execute(sqlQuery, new
{
id = member[0].id,
FirstName = member[0].FirstName,
LastName = member[0].LastName
});
}
ans.status = 1;
}
ans.status = 0;
}
else
{
ans.status = 0;
}
}
catch (Exception ex)
{
ans.status = 0;
}
return(ans);
}
public static List <GenericList> GetMemberParticipantList()
{
string CLPongoing = System.Configuration.ConfigurationManager.AppSettings["CLPongoing"];
string CLPupcoming = System.Configuration.ConfigurationManager.AppSettings["CLPupcoming"];
int CLPparticipantID = Convert.ToInt32(System.Configuration.ConfigurationManager.AppSettings["CLPparticipantID"]);
List <GenericList> MemberParticipantList = new List <GenericList>();
string query = "Select mem.id, mem.LastName + ', ' + mem.FirstName as name " +
"from[dbo].[Member] mem " +
"INNER JOIN (SELECT * FROM[dbo].[CLP] clp INNER JOIN[dbo].[CLPStatus] st ON clp.clpStatus = st.id WHERE st.Name LIKE '%" + CLPongoing + "%' OR st.Name LIKE '%" + CLPupcoming + "%') clp ON mem.CLPid = clp.CLPId " +
"WHERE mem.Position = " + CLPparticipantID + " AND mem.id NOT IN (SELECT memberId FROM [dbo].[CLPParticipant])";
DataSet ds = Connection.connectSQLServerQuery(query);
MemberParticipantList = BaseReflection.DatasetBDToObjectList <GenericList>(ds);
return(MemberParticipantList);
}
public static List <MemberModel> MemberList(int?id)
{
string query = string.Empty;
List <MemberModel> members = new List <MemberModel>();
query = " Select mem.id, mem.LastName + ', ' + mem.FirstName as memName, mem.address, mem.status, pos.name as PositionName, st.Name as statusName, sp.LastName + ', ' + sp.FirstName as SponsorName " +
"from[dbo].[Member] mem " +
"inner join[dbo].[Position] pos on mem.Position = pos.id " +
"inner join[dbo].[StatusGeneric] st on mem.status = st.id " +
"left join[dbo].[SponsorList] sp on mem.SponsorId = sp.id";
DataSet ds = Connection.connectSQLServerQuery(query);
members = BaseReflection.DatasetBDToObjectList <MemberModel>(ds);
MemberModel model = (members != null && members.Count > 0) ? members[0] : null;
return(members);
}
public static List <ServantModel> GetServantsList(int?id)
{
string query = string.Empty;
List <ServantModel> servant = new List <ServantModel>();
query = " SELECT ser.id, ser.memberId, mem.LastName + ', ' + mem.FirstName as name, vc.ServantTask as ServiceName " +
"FROM[dbo].[CLPServants] ser " +
"INNER JOIN[dbo].[Member] mem ON ser.memberId = mem.id " +
"INNER JOIN[dbo].[CLPService] vc ON ser.ServiceId = vc.id";
DataSet ds = Connection.connectSQLServerQuery(query);
servant = BaseReflection.DatasetBDToObjectList <ServantModel>(ds);
ServantModel model = (servant != null && servant.Count > 0) ? servant[0] : null;
return(servant);
}
public static UserResponse AccountAuth(string username, string password)
{
List <UserIfo> UserList = new List <UserIfo>();
UserResponse model = new UserResponse();
try
{
DataSet ds = new DataSet();
string sql = "SELECT * FROM [dbo].[User] WHERE UserName = @uname AND UserPaswr = @passwrd";
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ERPconn"].ConnectionString))
{
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.Parameters.AddWithValue("@uname", username);
command.Parameters.AddWithValue("@passwrd", password);
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = command;
command.Connection.Open();
da.Fill(ds);
command.Connection.Close();
}
}
UserList = BaseReflection.DatasetBDToObjectList <UserIfo>(ds);
model.Userdata = (UserList != null && UserList.Count > 0) ? UserList[0] : null;
if (UserList.Count != 0)
{
model.status = 1;
model.msg = "Success";
}
else
{
model.status = 2;
model.msg = "Invalid username/password";
}
}
catch (Exception ex)
{
model.status = 0;
model.msg = "Failed : " + ex.Message;
}
return(model);
}
public static List <UserIfo> UserList(int id)
{
string query = string.Empty;
List <UserIfo> users = new List <UserIfo>();
if (id != 0)
{
query = "SELECT * FROM [User] WHERE UserNr = " + id + "";
}
else
{
query = "SELECT * FROM [User]";
}
DataSet ds = Connection.connectSQLServerQuery(query);
users = BaseReflection.DatasetBDToObjectList <UserIfo>(ds);
return(users);
}
public static List <ParticipantModel> ParticipantList(int?id)
{
string query = string.Empty;
List <ParticipantModel> members = new List <ParticipantModel>();
query = "SELECT prt.id, prt.memberId, prt.GroupId, prt.CLPid, mem.LastName + ', ' + mem.FirstName as name, grp.Name as GroupName, sp.LastName + ', ' + sp.FirstName as SponsorName, st.Name as status " +
"FROM [dbo].[CLPParticipant] prt " +
"INNER JOIN [dbo].[Member] mem ON prt.memberId = mem.id " +
"INNER JOIN [dbo].[CLPGroupings] grp ON prt.GroupId = grp.id " +
"INNER JOIN[dbo].[SponsorList] sp ON mem.SponsorId = sp.id " +
"INNER JOIN[dbo].[StatusGeneric] st ON prt.statusID = st.id";
DataSet ds = Connection.connectSQLServerQuery(query);
members = BaseReflection.DatasetBDToObjectList <ParticipantModel>(ds);
ParticipantModel model = (members != null && members.Count > 0) ? members[0] : null;
return(members);
}
public static MemberModel MemberDetails(int id)
{
string query = string.Empty;
List <MemberModel> memlist = new List <MemberModel>();
if (id != 0)
{
query = "SELECT * FROM [Member] WHERE id = " + id + "";
}
else
{
query = "SELECT * FROM [Member] Order by [LastName]";
}
DataSet ds = Connection.connectSQLServerQuery(query);
memlist = BaseReflection.DatasetBDToObjectList <MemberModel>(ds);
MemberModel model = (memlist != null && memlist.Count > 0) ? memlist[0] : null;
return(model);
}
public static UserIfo UserData(int id)
{
string query = string.Empty;
List <UserIfo> user = new List <UserIfo>();
if (id != 0)
{
query = "SELECT * FROM [User] WHERE UserNr = " + id + "";
}
else
{
query = "SELECT * FROM [User]";
}
DataSet ds = Connection.connectSQLServerQuery(query);
user = BaseReflection.DatasetBDToObjectList <UserIfo>(ds);
UserIfo model = (user != null && user.Count > 0) ? user[0] : null;
return(model);
}
