public int SaveUser(UserDetails userDetails)
{
int iValue = 0;
DynamicParameters param = new DynamicParameters();
param.Add("@Name", userDetails.Name);
param.Add("@RoleId", userDetails.RoleId);
param.Add("@Active", userDetails.Active?1:0);
//var id = dbConnection.Query<int>(newUser, param, commandType: CommandType.StoredProcedure).SingleOrDefault();
const string sql = @"INSERT INTO dbo.UserMaster (Name,RoleId,Active)
Values (@Name,@RoleId,@Active);
SELECT CAST(SCOPE_IDENTITY() as int)";
var id = dbConnection.Query <int>(sql, new { Name = userDetails.Name, RoleId = userDetails.RoleId, Active = userDetails.Active ? 1 : 0 }).Single();
iValue = Convert.ToInt32(id);
if (iValue > 0)
{
DynamicParameters paramNew = new DynamicParameters();
paramNew.Add("@UserID", iValue);
paramNew.Add("@UserName", userDetails.UserName);
paramNew.Add("@Password", userDetails.Password);
var newid = dbConnection.Query <int>(newUserAuthentication, paramNew, commandType: CommandType.StoredProcedure).SingleOrDefault();
}
return(iValue);
}
public List <PROutstandingListMainModel> GetOutstandingPR()
{
var obj = db.Query <PROutstandingListMainModel>("Select a.ID,a.PRNo,a.PRDate from TH_PurchaseRequisition a Where Status=1").ToList();
db.Close();
return(obj);
}
//User authentication. returns true or false
public override bool ValidateUser(string username, string password)
{
bool isValidUser = false;
string _Password = string.Empty;
try
{
_Password = EncodePassword(password);
List <userListModel> trxs = _db.Query <userListModel>(";Exec AuthenticateUser @UserName, @Password",
new
{
UserName = username,
Password = _Password
}).ToList();
//List<User> trxs = con.Fetch<User>(";Exec getAuthenticateUser @UserName, @Password", new { UserName= username, Password= _Password });
isValidUser = trxs[0].UserName.ToString().ToUpper().Trim().Equals(username.ToUpper());
FormsAuthentication.SetAuthCookie(trxs[0].UserName.ToString().ToUpper(), false);
}
catch (Exception ee)
{
}
return(isValidUser);
}
public static bool ValidateUser(string username, string password)
{
bool isValidUser = false;
string _Password = string.Empty;
try
{
_Password = EncodePassword(password);
System.Data.IDbConnection _db = DapperConnection();
List <userListModel> trxs = _db.Query <userListModel>(";Exec AuthenticateUser @UserName, @Password",
new
{
UserName = username,
Password = _Password
}).ToList();
//List<User> trxs = con.Fetch<User>(";Exec getAuthenticateUser @UserName, @Password", new { UserName= username, Password= _Password });
isValidUser = trxs[0].UserName.ToString().ToUpper().Trim().Equals(username.ToUpper());
}
catch (Exception ee)
{
}
return(isValidUser);
}
public Core.Domain.Building Find(int id)
{
db = new SqlConnection(ConfigurationManager.ConnectionStrings["cnConsumption"].ConnectionString);
var obj = db.Query <Core.Domain.Building>("Select * from tblBuilding Where id=" + id).FirstOrDefault();
db.Close();
return(obj);
}
public List <Core.Domain.Building> GetList()
{
db = new SqlConnection(ConfigurationManager.ConnectionStrings["cnConsumption"].ConnectionString);
var obj = db.Query <Core.Domain.Building>("Select * from tblBuilding").ToList();
db.Close();
return(obj);
}
public int Add(TypeDTO type)
{
using (System.Data.IDbConnection connection = Connection.GetConnection())
{
string sqlExpression = "Type_Add @name";
return(connection.Query <int>(sqlExpression, type).FirstOrDefault());
}
}
protected int LastInsertId(System.Data.IDbConnection connection, IDbTransaction transaction)
{
if (provider == DbProvider.SQLite)
{
return((int)((SQLiteConnection)connection).LastInsertRowId);
}
return(connection.Query <int>("SELECT lastval();", transaction: transaction).First());
}
public void Delete(Todo item1)
{
using (System.Data.IDbConnection dbConnection = GetConnection())
{
string sQuery = "delete from Todo WHERE Todoid = @Todoid";
dbConnection.Open();
dbConnection.Query(sQuery, item1);
}
}
public void Done(int id)
{
using (System.Data.IDbConnection dbConnection = GetConnection())
{
string sQuery = "UPDATE Todo SET Done = 1 WHERE Todoid = @Todoid";
dbConnection.Open();
dbConnection.Query(sQuery, new { Todoid = id });
}
}
public UserAuthentication GetUserAuthentication(UserAuthentication userCred)
{
UserAuthentication userAuth = new UserAuthentication();
try
{
DynamicParameters param = new DynamicParameters();
param.Add("@UserName", userCred.UserName);
param.Add("@Password", userCred.Password);
return(userAuth = dbConnection.Query <UserAuthentication>(valUser, param, commandType: CommandType.StoredProcedure).SingleOrDefault());
}
catch (Exception ex)
{
throw;
}
finally
{
}
}
public void Update(Accident item1)
{
using (System.Data.IDbConnection dbConnection = GetConnection())
{
string sQuery = @"UPDATE AccidentTable SET Verified = @Verified
WHERE Id = @Id";
dbConnection.Open();
dbConnection.Query(sQuery, item1);
}
}
// public void Done(Accident item1)
// {
// using (System.Data.IDbConnection dbConnection = GetConnection())
// {
// string sQuery = "UPDATE AccidentTable SET "
// + " Done = @Done "
// + " WHERE Todoid = @Todoid";
// dbConnection.Open();
// dbConnection.Query(sQuery, item1);
// }
// }
public Accident Delete(int item)
{
using (System.Data.IDbConnection dbConnection = GetConnection())
{
string sQuery = "delete from AccidentTable WHERE Id = @id";
dbConnection.Open();
List <Accident> accdnt = dbConnection.Query <Accident>(sQuery, new { id = item }).ToList();
return(accdnt.FirstOrDefault());
}
}
public void Done(Todo item1)
{
using (System.Data.IDbConnection dbConnection = GetConnection())
{
string sQuery = "UPDATE Todo SET "
+ " Done = @Done "
+ " WHERE Todoid = @Todoid";
dbConnection.Open();
dbConnection.Query(sQuery, item1);
}
}
public EditPurchaseReqHeader GetPurchaseReqHeader(int id)
{
db = new SqlConnection(ConfigurationManager.ConnectionStrings["cnConsumption"].ConnectionString);
var obj = db.Query <EditPurchaseReqHeader>(" Select Th_PurchaseRequisition.PRNO, Th_PurchaseRequisition.PRDate,Remarks " +
" From Th_PurchaseRequisition " +
" Where Th_PurchaseRequisition.ID = " + id + "").FirstOrDefault();
db.Close();
return(obj);
}
public void Update(Todo item1)
{
using (System.Data.IDbConnection dbConnection = GetConnection())
{
string sQuery = "UPDATE Todo SET Task = @Task,"
+ " Done = @Done ,"
+ " Description = @Description"
+ " WHERE Todoid = @Todoid";
dbConnection.Open();
dbConnection.Query(sQuery, item1);
}
}
public List <ReqitemCategory> GetPurchaseReqCategory(int id)
{
db = new SqlConnection(ConfigurationManager.ConnectionStrings["cnConsumption"].ConnectionString);
var obj = db.Query <ReqitemCategory>(" Select distinct tblcategory.Name from tblCategory, Td_PurchaseRequisition, tblitem " +
" where Td_PurchaseRequisition.ItemId = tblitem.id" +
" and tblCategory.ID = tblitem.CategoryID" +
" and Td_PurchaseRequisition.prid =" + id + " order by tblcategory.name").ToList();
db.Close();
return(obj);
}
public IEnumerable <ModuleAccess> GetModuleAccessRights(int _UserID)
{
System.Data.IDbConnection _db = DapperConnection();
List <ModuleAccess> modulerights =
_db.Query <ModuleAccess>(";Exec getUserMainMenu @UserName", new
{
UserName = Membership.GetUser().UserName.ToString()
}).ToList();
return(modulerights);
}
public List <BuildingViewModel> GetListView()
{
string sql = " Select a.id,a.BuildingNo,b.Name as TankName from tblBuilding a ,tbltank b " +
" Where a.TankID = b.id";
db = new SqlConnection(ConfigurationManager.ConnectionStrings["cnConsumption"].ConnectionString);
var obj = db.Query <Services.Building.BuildingViewModel>(sql).ToList();
db.Close();
return(obj);
}
public List <EditPurchaseReqDetail> GetPurchaseReqDetail(int id)
{
db = new SqlConnection(ConfigurationManager.ConnectionStrings["cnConsumption"].ConnectionString);
var obj = db.Query <EditPurchaseReqDetail>(" Select Th_PurchaseRequisition.ID as PRID,b.id ,tblCategory.Name as Category,c.Name as Brand, " +
" a.ID as itemID, a.Name as ItemName, b.Unit,b.Specs, b.Qty" +
" From tblitem a inner join TD_PurchaseRequisition b on a.id = b.ItemId" +
" Left outer join tblBrand c on c.Id = a.BrandID" +
" inner join Th_PurchaseRequisition on Th_PurchaseRequisition.id = b.PRID" +
" inner join tblCategory on tblCategory.id = a.CategoryID" +
" Where Th_PurchaseRequisition.id= " + id + " order by tblCategory.Name").ToList();
db.Close();
return(obj);
}
public string NewPurchaseReq(List <Core.Domain.Purchase_Requisition.PurchaseRequisitionDetail> PurchaseItem, Core.Domain.Purchase_Requisition.PurchaseRequisition PurchaseHeader)
{
db = new SqlConnection(ConfigurationManager.ConnectionStrings["cnConsumption"].ConnectionString);
string sql;
//First Add Header
int ID = 0;
string NewPrNO = "PR-" + DateTime.Now.ToLongTimeString();
sql = " INSERT INTO TH_PurchaseRequisition(PRNo,PRDate,KitchenID,Remarks)VALUES ('" + NewPrNO + "',@PRDate,1,@Remarks) " +
"Select Cast(SCOPE_IDENTITY() AS int)";
try
{
ID = db.Query <int>(sql, new
{
PurchaseHeader.PRDate,
PurchaseHeader.Remarks
}).SingleOrDefault();
}
catch (Exception e)
{
if (e.Message.IndexOf("") > 0)
{
db.Close();
return("-1");
}
}
foreach (PurchaseRequisitionDetail obj in PurchaseItem)
{
sql = " Insert into TD_PurchaseRequisition (PRID,ItemID,Qty,Unit,Specs) " +
" Values (" + ID + ",@ItemID,@Qty,@Unit,@Specs)";
db.Execute(sql, new
{
obj.ItemId,
obj.Qty,
obj.Unit,
obj.Specs
});
}
return(NewPrNO);
}
public DLCustomerDetails GetCustomerByID(int custID)
{
DLCustomerDetails customerDetails = new DLCustomerDetails();
con = connManager.GetConnection();
con.Open();
try
{
DynamicParameters param = new DynamicParameters();
param.Add("@CustomerID", custID);
return(customerDetails = dbConnection.Query <DLCustomerDetails>(getCustomerByID, param, commandType: CommandType.StoredProcedure).SingleOrDefault());
}
catch (Exception ex)
{
throw;
}
finally
{
con.Close();
}
}
public int InsertBuilding(Core.Domain.Building model, ref string ErrorMsg)
{
int ID = 0;
db = new SqlConnection(ConfigurationManager.ConnectionStrings["cnConsumption"].ConnectionString);
string sql = " INSERT INTO tblBuilding(BuildingNo,BuildingName,TankID) " +
" VALUES (@BuildingNo,@BuildingName,@TankID)" +
" Select Cast(SCOPE_IDENTITY() AS int)";
try
{
ID = db.Query <int>(sql, new
{
model.BuildingNo,
model.BuildingName,
model.TankID
}).SingleOrDefault();
}
catch (Exception e)
{
if (e.Message.IndexOf("IX_tblItem-Name") > 0)
{
ErrorMsg = "Item Already Exist....";
}
else
{
ErrorMsg = e.Message;
}
db.Close();
return(0);
}
model.ID = ID;
db.Close();
return(ID);
}
public IEnumerable <PriorityCode> FindPriorityCodeList()
{
return(_db.Query <PriorityCode>(listSQL));
}
public ICollection <UserId> FindUserIdList()
{
string runSQL = listSQL + " Order by AD_FULL_NAME";
return(_db.Query <UserId>(listSQL).ToList());
}
public ICollection <CommentType> FindTypeList()
{
return(_db.Query <CommentType>(listSQL).ToList());
}
public ICollection <LogComment> FindCommentList(string Log)
{
string getSQL = listSQL + " Where LOG_NUM = " + Log;
return(_db.Query <LogComment>(getSQL).ToList());
}
public ICollection <StatusCode> FindStatusCodeList()
{
string runSQL = listSQL + " Order by Status";
return(_db.Query <StatusCode>(listSQL).ToList());
}
