public static List <ReportEntity> GetCashierWiseSale(ReportCriteria criteria)
{
List <ReportEntity> lstData = new List <ReportEntity>();
using (SqlDataHelper helper = new SqlDataHelper(ConnectionString))
{
helper.CommandText = "[dbo].[sp_rpt_CASHIERWISESALE]";
helper.CommandType = CommandType.StoredProcedure;
helper.Parameters.Add("@Mode", criteria.CashierId);
helper.Parameters.Add("@StartDate", criteria.FromDate);
helper.Parameters.Add("@EndDate", criteria.ToDate);
helper.Open();
helper.ExecuteReader(CommandBehavior.CloseConnection);
while (helper.DataReader.Read())
{
ReportEntity report = new ReportEntity();
report.LoadCashierWiseSale(helper.DataReader);
lstData.Add(report);
}
helper.Close();
}
return(lstData);
}
public static List <IRole> GetRole(bool isActiveOnly, SearchCriteria searchCriteria)
{
List <IRole> lstRole = new List <IRole>();
using (SqlDataHelper helper = new SqlDataHelper(ConnectionString))
{
helper.CommandText = "[webadmin].[uspGetRole]";
helper.CommandType = CommandType.StoredProcedure;
helper.Parameters.Add("@IsActiveOnly", isActiveOnly);
helper.Parameters.Add("@SchRole", searchCriteria.RoleName);
helper.Parameters.Add("@SortExpression", searchCriteria.SortExpression);
helper.Parameters.Add("@SortDirection", searchCriteria.SortDirection);
helper.Open();
helper.ExecuteReader(CommandBehavior.CloseConnection);
while (helper.DataReader.Read())
{
IRole role = new RoleEntity(helper.DataReader);
lstRole.Add(role);
}
helper.Close();
}
return(lstRole);
}
public static List <IUser> GetUserList(bool isActiveOnly, SearchCriteria searchCriteria)
{
List <IUser> lstUser = new List <IUser>();
using (SqlDataHelper helper = new SqlDataHelper(ConnectionString))
{
helper.CommandText = "[webadmin].[uspGetUser]";
helper.CommandType = CommandType.StoredProcedure;
helper.Parameters.Add("@IsActiveOnly", isActiveOnly);
helper.Parameters.Add("@SchUserName", searchCriteria.UserName);
helper.Parameters.Add("@SchFirstName", searchCriteria.FirstName);
helper.Parameters.Add("@SortExpression", searchCriteria.SortExpression);
helper.Parameters.Add("@SortDirection", searchCriteria.SortDirection);
helper.Open();
helper.ExecuteReader(CommandBehavior.CloseConnection);
while (helper.DataReader.Read())
{
IUser user = new UserEntity(helper.DataReader);
lstUser.Add(user);
}
helper.Close();
}
return(lstUser);
}
public static List <ReportEntity> GetStockLedger(ReportCriteria criteria)
{
List <ReportEntity> lstData = new List <ReportEntity>();
using (SqlDataHelper helper = new SqlDataHelper(ConnectionString))
{
helper.CommandText = "[rpt].[sp_rpt_STOCKREGISTER]";
helper.CommandType = CommandType.StoredProcedure;
helper.Parameters.Add("@StartDate", criteria.FromDate);
helper.Parameters.Add("@EndDate", criteria.ToDate);
//helper.Parameters.Add("@Mode", criteria.FromDate);
//helper.Parameters.Add("@OBDate", criteria.FromDate);
//helper.Parameters.Add("@ItemCode", criteria.FromDate);
helper.Open();
helper.ExecuteReader(CommandBehavior.CloseConnection);
while (helper.DataReader.Read())
{
ReportEntity report = new ReportEntity(helper.DataReader);
report.LoadStockLedger(helper.DataReader);
lstData.Add(report);
}
helper.Close();
}
return(lstData);
}
public static List <ReportEntity> GetBillWiseSaleRefund(ReportCriteria criteria)
{
List <ReportEntity> lstData = new List <ReportEntity>();
using (SqlDataHelper helper = new SqlDataHelper(ConnectionString))
{
helper.CommandText = "[dbo].[sp_rpt_SALEREFUNDREGISTER]";
helper.CommandType = CommandType.StoredProcedure;
helper.Parameters.Add("@TranType", criteria.TransactionType);
helper.Parameters.Add("@StartDate", criteria.FromDate);
helper.Parameters.Add("@EndDate", criteria.ToDate);
helper.Open();
helper.ExecuteReader(CommandBehavior.CloseConnection);
while (helper.DataReader.Read())
{
ReportEntity report = new ReportEntity();
report.LoadBillWiseSaleRefund(helper.DataReader);
lstData.Add(report);
}
helper.Close();
}
return(lstData);
}
public static List <IItemGroup> GetItemGroup(char mode, int lvl, Int64 userId)
{
List <IItemGroup> lstGroup = new List <IItemGroup>();
using (SqlDataHelper helper = new SqlDataHelper(ConnectionString))
{
helper.CommandText = "[dbo].[sp_Manage_ITEMGROUP]";
helper.CommandType = CommandType.StoredProcedure;
helper.Parameters.Add("@Mode", mode);
helper.Parameters.Add("@UserID", userId);
helper.Parameters.Add("@Lvl", lvl);
helper.Parameters.Add("@Result", SqlDbType.Int, ParameterDirection.Output);
helper.Open();
helper.ExecuteReader(CommandBehavior.CloseConnection);
while (helper.DataReader.Read())
{
IItemGroup group = new ItemGroupEntity(helper.DataReader);
lstGroup.Add(group);
}
helper.Close();
}
return(lstGroup);
}
public static IUser GetUser(int userId, bool isActiveOnly, SearchCriteria searchCriteria)
{
IUser user = null;
using (SqlDataHelper helper = new SqlDataHelper(ConnectionString))
{
helper.CommandText = "[webadmin].[uspGetUser]";
helper.CommandType = CommandType.StoredProcedure;
helper.Parameters.Add("@UserId", userId);
helper.Parameters.Add("@IsActiveOnly", isActiveOnly);
helper.Parameters.Add("@SortExpression", searchCriteria.SortExpression);
helper.Parameters.Add("@SortDirection", searchCriteria.SortDirection);
helper.Open();
helper.ExecuteReader(CommandBehavior.CloseConnection);
while (helper.DataReader.Read())
{
user = new UserEntity(helper.DataReader);
}
helper.Close();
}
return(user);
}
/// <summary>
/// Saves the error log.
/// </summary>
/// <param name="userId">The user id.</param>
/// <param name="message">The message.</param>
/// <param name="stackTrace">The stack trace.</param>
/// <createdby>Amit Kumar Chandra</createdby>
/// <createddate>11/06/2012</createddate>
public static void SaveErrorLog(int userId, string message, string stackTrace)
{
using (SqlDataHelper helper = new SqlDataHelper(ConnectionString))
{
helper.Open();
helper.CommandText = "[webadmin].[uspSaveError]";
helper.CommandType = CommandType.StoredProcedure;
helper.Parameters.Add("@UserId", userId);
helper.Parameters.Add("@ErrorMessage", message);
helper.Parameters.Add("@StackTrace", stackTrace);
helper.ExecuteNonQuery();
helper.Close();
}
}
public static DataTable GetUserSpecificMenuList(int UserID)
{
DataSet ds = new DataSet();
using (SqlDataHelper helper = new SqlDataHelper(ConnectionString))
{
helper.CommandText = "[webadmin].[uspGetUserSpecificMenuList]";
helper.CommandType = CommandType.StoredProcedure;
helper.Parameters.Add("@UserId", UserID);
helper.Open();
ds = helper.ExecuteDataSet();
helper.Close();
}
return(ds.Tables[0]);
}
public static DataSet GetUserById(int userId)
{
DataSet ds = new DataSet();
using (SqlDataHelper helper = new SqlDataHelper(ConnectionString))
{
helper.CommandText = "[webadmin].[uspGetUserById]";
helper.CommandType = CommandType.StoredProcedure;
helper.Parameters.Add("@UserId", userId);
helper.Open();
ds = helper.ExecuteDataSet();
helper.Close();
}
return(ds);
}
public static int DeleteRole(int roleId, int modifiedBy)
{
int result = 0;
using (SqlDataHelper helper = new SqlDataHelper(ConnectionString))
{
helper.CommandText = "[webadmin].[uspDeleteRole]";
helper.CommandType = CommandType.StoredProcedure;
helper.Parameters.Add("@RoleId", roleId);
helper.Parameters.Add("@ModifiedBy", modifiedBy);
helper.Open();
result = helper.ExecuteNonQuery();
helper.Close();
}
return(result);
}
public static int ChangePassword(IUser user)
{
int result = 0;
using (SqlDataHelper helper = new SqlDataHelper(ConnectionString))
{
helper.CommandText = "[webadmin].[uspChangePassword]";
helper.CommandType = CommandType.StoredProcedure;
helper.Parameters.Add("@UserId", user.Id);
helper.Parameters.Add("@OldPwd", user.Password);
helper.Parameters.Add("@NewPwd", user.NewPassword);
helper.Open();
result = helper.ExecuteNonQuery();
helper.Close();
}
return(result);
}
public static int ResetPassword(IUser user, int modifiedBy)
{
int result = 0;
using (SqlDataHelper helper = new SqlDataHelper(ConnectionString))
{
helper.CommandText = "[webadmin].[uspResetPassword]";
helper.CommandType = CommandType.StoredProcedure;
helper.Parameters.Add("@UserId", user.Id);
helper.Parameters.Add("@Pwd", user.Password);
helper.Parameters.Add("@ModifiedBy", modifiedBy);
helper.Open();
result = helper.ExecuteNonQuery();
helper.Close();
}
return(result);
}
public static int SaveRole(IRole role, string xmlDoc, int modifiedBy)
{
int result = 0;
using (SqlDataHelper helper = new SqlDataHelper(ConnectionString))
{
helper.CommandText = "[webadmin].[uspSaveRole]";
helper.CommandType = CommandType.StoredProcedure;
helper.Parameters.Add("@RoleID", role.Id);
helper.Parameters.Add("@RoleName", role.Name);
helper.Parameters.Add("@XMLDoc", xmlDoc);
helper.Parameters.Add("@ModifiedBy", modifiedBy);
helper.Open();
result = helper.ExecuteNonQuery();
helper.Close();
}
return(result);
}
public static IRoleMenu GetMenuAccessByUser(int userId, int menuId)
{
IRoleMenu roleMenu = null;
using (SqlDataHelper helper = new SqlDataHelper(ConnectionString))
{
helper.CommandText = "[webadmin].[uspGetMenuAccessByUser]";
helper.CommandType = CommandType.StoredProcedure;
helper.Parameters.Add("@UserId", userId);
helper.Parameters.Add("@MenuId", menuId);
helper.Open();
helper.ExecuteReader(CommandBehavior.CloseConnection);
while (helper.DataReader.Read())
{
roleMenu = new RoleMenuEntity(helper.DataReader);
}
helper.Close();
}
return(roleMenu);
}
public static List <ReportEntity> GetCashierLog(ReportCriteria criteria)
{
List <ReportEntity> lstData = new List <ReportEntity>();
using (SqlDataHelper helper = new SqlDataHelper(ConnectionString))
{
helper.CommandText = "[rpt].[]";
helper.CommandType = CommandType.StoredProcedure;
helper.Parameters.Add("@", 1);
helper.Open();
helper.ExecuteReader(CommandBehavior.CloseConnection);
while (helper.DataReader.Read())
{
ReportEntity report = new ReportEntity(helper.DataReader);
report.LoadCashierLog(helper.DataReader);
lstData.Add(report);
}
helper.Close();
}
return(lstData);
}
public static void ValidateUser(IUser user)
{
using (SqlDataHelper helper = new SqlDataHelper(ConnectionString))
{
helper.CommandText = "[webadmin].[uspValidateUser]";
helper.CommandType = CommandType.StoredProcedure;
helper.Parameters.Add("@UserName", user.Name);
helper.Parameters.Add("@Password", user.Password);
helper.Open();
helper.ExecuteReader(CommandBehavior.CloseConnection);
while (helper.DataReader.Read())
{
user.Id = Convert.ToInt32(helper.DataReader["UserId"]);
user.FirstName = Convert.ToString(helper.DataReader["FirstName"]);
user.LastName = Convert.ToString(helper.DataReader["LastName"]);
user.UserRole.Id = Convert.ToInt32(helper.DataReader["RoleId"]);
user.EmailId = Convert.ToString(helper.DataReader["emailID"]);
}
helper.Close();
}
}
public static List <IRoleMenu> GetMenuByRole(int roleId, int mainId)
{
List <IRoleMenu> lstMenu = new List <IRoleMenu>();
using (SqlDataHelper helper = new SqlDataHelper(ConnectionString))
{
helper.CommandText = "[webadmin].[uspGetMenuByRole]";
helper.CommandType = CommandType.StoredProcedure;
helper.Parameters.Add("@RoleId", roleId);
helper.Parameters.Add("@MainId", mainId);
helper.Open();
helper.ExecuteReader(CommandBehavior.CloseConnection);
while (helper.DataReader.Read())
{
IRoleMenu menu = new RoleMenuEntity(helper.DataReader);
lstMenu.Add(menu);
}
helper.Close();
}
return(lstMenu);
}
public static int SaveUser(IUser user, int modifiedBy)
{
int result = 0;
using (SqlDataHelper helper = new SqlDataHelper(ConnectionString))
{
helper.CommandText = "[webadmin].[uspSaveUser]";
helper.CommandType = CommandType.StoredProcedure;
helper.Parameters.Add("@UserId", user.Id);
helper.Parameters.Add("@UserName", user.Name);
helper.Parameters.Add("@Pwd", user.Password);
helper.Parameters.Add("@FirstName", user.FirstName);
helper.Parameters.Add("@LastName", user.LastName);
helper.Parameters.Add("@RoleId", user.UserRole.Id);
helper.Parameters.Add("@EmailId", user.EmailId);
helper.Parameters.Add("@IsActive", user.IsActive);
helper.Parameters.Add("@ModifiedBy", modifiedBy);
helper.Open();
result = helper.ExecuteNonQuery();
helper.Close();
}
return(result);
}
