public List<SubItemLookUp> GetAllSubItemsForLookUp()
{
DBEgine objDbAccess = new DBEgine();
var list = new List<SubItemLookUp>();
try
{
objDbAccess.DBOpen();
SqlDataReader dr = objDbAccess.ExecuteReader("Inventory.GetAllSubItems", null);
while (dr.Read())
{
var subItem = new SubItemLookUp();
subItem.SubItemID = int.Parse(dr["SubItemID"].ToString());
subItem.SubItemSerial = dr["SubItemSerial"].ToString();
subItem.SubItemDescription = dr["SubItemDescription"].ToString();
subItem.SubItemUnitPrice = Convert.ToDecimal(dr["SubItemUnitPrice"].ToString());
list.Add(subItem);
}
return list;
}
catch (Exception ex)
{
throw ex;
}
finally
{
objDbAccess.DBClose();
}
}
public bool DeleteSubItemByID(int id)
{
DBEgine objDbAccess = new DBEgine();
bool result = false;
try
{
SqlParameter[] parms = new SqlParameter[1];
SqlParameter parmItemID = new SqlParameter();
parmItemID.Value = id;
parmItemID.SqlDbType = SqlDbType.Int;
parmItemID.Direction = ParameterDirection.Input;
parmItemID.ParameterName = "@SubItemID";
parms[0] = parmItemID;
objDbAccess.DBOpen();
int count = objDbAccess.ExecNonQueryStoredProc("Inventory.DeleteSubItem", parms);
if (count > 0)
{
result = true;
}
return result;
}
catch (Exception ex)
{
throw ex;
}
finally
{
objDbAccess.DBClose();
}
}
public void DeactivateUsersessionByInterval(int interval)
{
DBEgine dbEngine = new DBEgine();
try
{
dbEngine.DBOpen();
SqlParameter[] parms = new SqlParameter[1];
SqlParameter p1 = new SqlParameter();
p1.ParameterName = "intervalSecond";
p1.SqlDbType = SqlDbType.Int;
p1.Value = interval;
parms[0] = p1;
dbEngine.ExecNonQueryStoredProc("UM_DeactivateUsersessionByInterval", parms);
}
catch
{
throw;
}
finally
{
dbEngine.DBClose();
}
}
public DataSet Report_GetUserRoles(int branchId, string userIdList)
{
DBEgine dbEngin = new DBEgine ();
try
{
string BranchId = branchId.ToString();
SqlParameter branchIdParam = new SqlParameter("@BranchId", SqlDbType.VarChar,10);
branchIdParam.Value = BranchId;
SqlParameter userListParam = new SqlParameter("@userId", SqlDbType.VarChar, 800);
userListParam.Value = userIdList;
SqlParameter[] paramList = { branchIdParam, userListParam };
dbEngin.DBOpen();
return dbEngin.ExecuteDataSet("UM_Report_GerUserRoles", paramList);
}
catch
{
throw;
}
finally
{
dbEngin.DBClose();
}
}
public List<InvoiceHeaderBO> GetAllInvoicesForCurrentDate()
{
DBEgine objDbAccess = new DBEgine();
var list = new List<InvoiceHeaderBO>();
try
{
objDbAccess.DBOpen();
SqlDataReader dr = objDbAccess.ExecuteReader("Sales.GetCurrentDayInvoices", null);
while (dr.Read())
{
var invoice = new InvoiceHeaderBO();
invoice.InvoiceID = int.Parse(dr["InvoiceID"].ToString());
invoice.InvoiceNumber = dr["InvoiceNumber"].ToString();
list.Add(invoice);
}
return list;
}
catch (Exception ex)
{
throw ex;
}
finally
{
objDbAccess.DBClose();
}
}
public DateTime GetCurrentDayProcessDate()
{
DBEgine objDbAccess = new DBEgine();
DateTime currentDate = DateTime.Now;
try
{
objDbAccess.DBOpen();
SqlDataReader dr = objDbAccess.ExecuteReader("[admin].GetCurrentDayProcess ", null);
while (dr.Read())
{
currentDate = DateTime.Parse(dr["DayProcessDate"].ToString());
}
return currentDate;
}
catch (Exception ex)
{
throw ex;
}
finally
{
objDbAccess.DBClose();
}
}
public bool CompleteDayProcess(int user)
{
DBEgine objDbAccess = new DBEgine();
bool result = false;
try
{
objDbAccess.DBOpen();
SqlParameter[] parms = new SqlParameter[1];
SqlParameter parmUserID = new SqlParameter();
parmUserID.Value = user;
parmUserID.SqlDbType = SqlDbType.Int;
parmUserID.Direction = ParameterDirection.Input;
parmUserID.ParameterName = "@User";
parms[0] = parmUserID;
objDbAccess.ExecNonQueryStoredProc("[admin].CompleteDayProcess ", parms);
result = true;
return result;
}
catch (Exception ex)
{
throw ex;
}
finally
{
objDbAccess.DBClose();
}
}
public List<UserLevelsBO> LoadActiveUserLevels()
{
DBEgine dbEgine = new DBEgine();
List<UserLevelsBO> userLevelsList = new List<UserLevelsBO>();
try
{
dbEgine.DBOpen();
SqlDataReader drUserLevels = dbEgine.ExecuteReader("UserManagement.UM_LoadActiveUserLevels", null);
if (drUserLevels != null && drUserLevels.HasRows)
{
while (drUserLevels.Read())
{
UserLevelsBO userLevelObj = new UserLevelsBO();
userLevelObj = FillUserLevel(drUserLevels);
userLevelsList.Add(userLevelObj);
}
}
return userLevelsList;
}
catch
{
throw;
}
finally
{
dbEgine.DBClose();
}
}
public UserLevelsBO GetNextUserLevelByCurrentUserLevelID(int currentUserLevelID)
{
DBEgine dbEgine = new DBEgine();
try
{
UserLevelsBO userLevelObj = new UserLevelsBO();
SqlParameter[] parms = new SqlParameter[1];
dbEgine.DBOpen();
SqlParameter userLevelIdParm = new SqlParameter();
userLevelIdParm.ParameterName = "@UserLevelId";
userLevelIdParm.SqlDbType = SqlDbType.Int;
userLevelIdParm.Direction = ParameterDirection.Input;
userLevelIdParm.Value = currentUserLevelID;
parms[0] = userLevelIdParm;
SqlDataReader drUserLevels = dbEgine.ExecuteReader("UserManagement.UM_GetNextUserLevelByCurrentUserLevel", parms);
if (drUserLevels != null && drUserLevels.HasRows && drUserLevels.Read())
{
userLevelObj = FillUserLevel(drUserLevels);
}
return userLevelObj;
}
catch
{
throw;
}
finally
{
dbEgine.DBClose();
}
}
public List<MeasuringUnitLookUp> GetAllMeasuringUnitsForLookUp()
{
DBEgine objDbAccess = new DBEgine();
var list = new List<MeasuringUnitLookUp>();
try
{
objDbAccess.DBOpen();
SqlDataReader dr = objDbAccess.ExecuteReader("Inventory.GetMeasuringUnits", null);
while (dr.Read())
{
var item = new MeasuringUnitLookUp();
item.UnitID = Convert.ToInt32(dr["UnitID"].ToString());
item.UnitDescription = dr["UnitDescription"].ToString();
list.Add(item);
}
return list;
}
catch (Exception ex)
{
throw ex;
}
finally
{
objDbAccess.DBClose();
}
}
public List<ScreenBO> LoadGrantedScreensByRoleID(int roleId)
{
DBEgine dbEgine = new DBEgine();
List<ScreenBO> screenList = new List<ScreenBO>();
try
{
dbEgine.DBOpen();
SqlParameter[] parms = new SqlParameter[1];
SqlParameter p1 = new SqlParameter();
p1.ParameterName = "@RoleId";
p1.SqlDbType = SqlDbType.Int;
p1.Direction = ParameterDirection.Input;
p1.Value = roleId;
parms[0] = p1;
SqlDataReader drScreenList = dbEgine.ExecuteReader("UserManagement.UM_LoadGrantedScreensByRoleID", parms);
while (drScreenList.Read())
{
ScreenBO objscreen = new ScreenBO();
objscreen.ScreenId = drScreenList.GetInt32(drScreenList.GetOrdinal("ScreenId"));
objscreen.ScreenName = drScreenList.GetValue(drScreenList.GetOrdinal("Screen")).ToString();
objscreen.Url = drScreenList.GetValue(drScreenList.GetOrdinal("URL")).ToString();
objscreen.ScreenParentId = drScreenList.GetInt32(drScreenList.GetOrdinal("ScreenParentId"));
objscreen.IsMenu = drScreenList.GetBoolean(drScreenList.GetOrdinal("IsMenu"));
objscreen.IsEnable = drScreenList.GetBoolean(drScreenList.GetOrdinal("IsEnable"));
objscreen.ScreenOrderId = drScreenList.GetInt32(drScreenList.GetOrdinal("ScreenOrderId"));
List<ScreenFunctionsBO> listScreenFuntions = new ScreenFunctionsDAL().LoadScreenFunctions(objscreen.ScreenId, roleId);
objscreen.ScreenFunctions = new List<ScreenFunctionsBO>();
if (listScreenFuntions != null && listScreenFuntions.Count > 0)
{
objscreen.ScreenFunctions = listScreenFuntions;
}
screenList.Add(objscreen);
}
dbEgine.DBClose();
return screenList;
}
catch
{
throw;
}
finally
{
dbEgine.DBClose();
}
}
public List<ScreenFunctionsBO> LoadGrantedScreenFunctionsbyScreenIDandUserName(int screenId, string userName)
{
DBEgine dbEgine = new DBEgine();
List<ScreenBO> screenList = new List<ScreenBO>();
try
{
dbEgine.DBOpen();
SqlParameter[] parms = new SqlParameter[2];
SqlParameter p1 = new SqlParameter();
p1.ParameterName = "UserName";
p1.SqlDbType = SqlDbType.NVarChar;
p1.Size = 256;
p1.Direction = ParameterDirection.Input;
p1.Value = userName;
parms[0] = p1;
SqlParameter p2 = new SqlParameter();
p2.ParameterName = "ScreenId";
p2.SqlDbType = SqlDbType.Int;
p2.Direction = ParameterDirection.Input;
p2.Value = screenId;
parms[1] = p2;
SqlDataReader drScreenFunctionList = dbEgine.ExecuteReader("UM_LoadGrantedScreenFunctionsbyScreenIDandUserName", parms);
List<ScreenFunctionsBO> screenFunctionsList = new List<ScreenFunctionsBO>();
while (drScreenFunctionList.Read())
{
ScreenFunctionsBO screenFunction = new ScreenFunctionsBO();
screenFunction.ScreenFunctionCode = drScreenFunctionList.GetValue(drScreenFunctionList.GetOrdinal("ScreenFunctionCode")).ToString();
screenFunction.FunctionName = drScreenFunctionList.GetValue(drScreenFunctionList.GetOrdinal("FunctionName")).ToString();
screenFunction.IsActive = drScreenFunctionList.GetBoolean(drScreenFunctionList.GetOrdinal("IsActive"));
screenFunction.IsGranted = drScreenFunctionList.GetBoolean(drScreenFunctionList.GetOrdinal("IsGranted"));
screenFunctionsList.Add(screenFunction);
}
dbEgine.DBClose();
return screenFunctionsList;
}
catch
{
throw;
}
finally
{
dbEgine.DBClose();
}
}
public bool AddMeasurementUnit(MeasurementUnitBO measurementUnit)
{
DBEgine objDbAccess = new DBEgine();
bool result = false;
try
{
SqlParameter[] parms = new SqlParameter[4];
SqlParameter parmUnitID = new SqlParameter();
parmUnitID.Value = measurementUnit.UnitID;
parmUnitID.SqlDbType = SqlDbType.Int;
parmUnitID.Direction = ParameterDirection.Input;
parmUnitID.ParameterName = "@UnitID";
parms[0] = parmUnitID;
SqlParameter parmUnitDescription = new SqlParameter();
parmUnitDescription.Value = measurementUnit.UnitDescription;
parmUnitDescription.SqlDbType = SqlDbType.VarChar;
parmUnitDescription.Direction = ParameterDirection.Input;
parmUnitDescription.ParameterName = "@UnitDescription";
parms[1] = parmUnitDescription;
SqlParameter parmIsActive = new SqlParameter();
parmIsActive.Value = measurementUnit.IsActive;
parmIsActive.SqlDbType = SqlDbType.Bit;
parmIsActive.Direction = ParameterDirection.Input;
parmIsActive.ParameterName = "@IsActive";
parms[2] = parmIsActive;
SqlParameter parmCreatedUser = new SqlParameter();
parmCreatedUser.Value = measurementUnit.CreatedUser;
parmCreatedUser.SqlDbType = SqlDbType.Int;
parmCreatedUser.Direction = ParameterDirection.Input;
parmCreatedUser.ParameterName = "@CreatedUser";
parms[3] = parmCreatedUser;
objDbAccess.DBOpen();
if (objDbAccess.ExecNonQueryStoredProc("Inventory.SaveMeasurementUnit", parms) > 0)
{
result = true;
}
return result;
}
catch (Exception ex)
{
throw ex;
}
finally
{
objDbAccess.DBClose();
}
}
public static List<RelationshipBO> GetAllRelationship()
{
List<RelationshipBO> listRel = new List<RelationshipBO>();
DBEgine dbEgine = new DBEgine();
try
{
dbEgine.DBOpen();
DataTable dt = dbEgine.ExecuteDataTable("U_GetAllRelationship", null);
if (dt.Rows.Count > 0)
{
foreach (DataRow r in dt.Rows)
{
var relationshipBO = new RelationshipBO();
if (r["RelationShipID"] != DBNull.Value)
relationshipBO.RelationShipID = (int)r["RelationShipID"];
if (r["RelationShipName"] != DBNull.Value)
relationshipBO.RelationShipName = r["RelationShipName"].ToString();
if (r["IsActive"] != DBNull.Value)
relationshipBO.IsActive = (bool)r["IsActive"];
if (r["CreatedBy"] != DBNull.Value)
relationshipBO.CreatedBy = (int)r["CreatedBy"];
if (r["CreatedDate"] != DBNull.Value)
relationshipBO.CreatedDate = (DateTime)r["CreatedDate"];
if (r["ModifiedBy"] != DBNull.Value)
relationshipBO.ModifiedBy = (int)r["ModifiedBy"];
if (r["ModifiedDate"] != DBNull.Value)
relationshipBO.ModifiedDate = (DateTime)r["ModifiedDate"];
listRel.Add(relationshipBO);
}
}
}
catch
{
throw;
}
finally
{
dbEgine.DBClose();
}
return listRel;
}
public List<UserRolesBO> LoadUserRolesByUserName(string UserName, string Password)
{
DBEgine dbEgine = new DBEgine();
List<UserRolesBO> userRoleList = new List<UserRolesBO>();
try
{
dbEgine.DBOpen();
SqlParameter[] parms = new SqlParameter[2];
SqlParameter userNameParm = new SqlParameter();
userNameParm.ParameterName = "@UserName";
userNameParm.SqlDbType = SqlDbType.NVarChar;
userNameParm.Direction = ParameterDirection.Input;
userNameParm.Value = UserName;
parms[0] = userNameParm;
SqlParameter passwordParm = new SqlParameter();
passwordParm.ParameterName = "@password";
passwordParm.SqlDbType = SqlDbType.VarChar;
passwordParm.Direction = ParameterDirection.Input;
passwordParm.Value = Password;
parms[1] = passwordParm;
SqlDataReader drUserRoles = dbEgine.ExecuteReader("UM_LoadUserRolesByUserName", parms);
if (drUserRoles != null && drUserRoles.HasRows)
{
while (drUserRoles.Read())
{
UserRolesBO userRoleObj = new UserRolesBO();
userRoleObj.UserRoleName = drUserRoles.GetValue(drUserRoles.GetOrdinal("UserRoleName")).ToString();
userRoleList.Add(userRoleObj);
}
}
return userRoleList;
}
catch
{
throw;
}
finally
{
dbEgine.DBClose();
}
}
public List<SubItemDetailBO> GetSubItemDetailBySubItemID(int id)
{
DBEgine objDbAccess = new DBEgine();
var list = new List<SubItemDetailBO>();
try
{
SqlParameter[] parms = new SqlParameter[1];
SqlParameter parmItemID = new SqlParameter();
parmItemID.Value = id;
parmItemID.SqlDbType = SqlDbType.Int;
parmItemID.Direction = ParameterDirection.Input;
parmItemID.ParameterName = "@SubItemID";
parms[0] = parmItemID;
objDbAccess.DBOpen();
SqlDataReader dr = objDbAccess.ExecuteReader("Inventory.GetSubItemDetailBySubItemID", parms);
while (dr.Read())
{
var subItemDetail = new SubItemDetailBO();
subItemDetail.SubItemID = int.Parse(dr["SubItemID"].ToString());
subItemDetail.Serial = dr["ItemSerial"].ToString();
subItemDetail.UnitOfMeasureID = int.Parse(dr["UnitOfMeasureID"].ToString());
subItemDetail.SubItemDetailID = int.Parse(dr["SubItemDetailID"].ToString());
subItemDetail.ItemID = int.Parse(dr["ItemID"].ToString());
subItemDetail.QtyForUnit = decimal.Parse(dr["QtyForUnit"].ToString());
subItemDetail.Description = dr["ItemDescription"].ToString();
subItemDetail.UnitOfMeasureName = dr["UnitDescription"].ToString();
list.Add(subItemDetail);
}
return list;
}
catch (Exception ex)
{
throw ex;
}
finally
{
objDbAccess.DBClose();
}
}
public static List<DesignationBO> GetAllDesignations(bool IsActive)
{
List<DesignationBO> DesignationList = new List<DesignationBO>();
DBEgine dbEgine = new DBEgine();
try
{
dbEgine.DBOpen();
SqlParameter[] parms = new SqlParameter[1];
SqlParameter p1 = new SqlParameter();
p1.ParameterName = "@IsActive";
p1.SqlDbType = SqlDbType.Bit;
p1.Direction = ParameterDirection.Input;
p1.Value = IsActive;
parms[0] = p1;
SqlDataReader drDesig = dbEgine.ExecuteReader("UM_GetAllDesignations", parms);
if (drDesig != null && drDesig.HasRows)
{
while (drDesig.Read())
{
DesignationDAL des = new DesignationDAL();
DesignationList.Add(des.FillDesignation(drDesig));
}
}
return DesignationList;
}
catch
{
throw;
}
finally
{
dbEgine.DBClose();
}
}
public DesignationBO GetDesignation(int Id)
{
DBEgine dbEgine = new DBEgine();
DesignationBO des = new DesignationBO();
try
{
dbEgine.DBOpen();
SqlParameter[] parms = new SqlParameter[1];
SqlParameter p1 = new SqlParameter();
p1.ParameterName = "in_DesignationId";
p1.SqlDbType = SqlDbType.Int;
p1.Direction = ParameterDirection.Input;
p1.Value = Id;
parms[0] = p1;
SqlDataReader drDesig = dbEgine.ExecuteReader("UserManagement.UM_LoadDesignationById", parms);
if (drDesig != null && drDesig.HasRows)
{
while (drDesig.Read())
{
des= FillDesignation(drDesig);
}
// this.ScreenList = LoadUserPermissionByUserId();
}
return des;
}
catch
{
throw;
}
finally
{
dbEgine.DBClose();
}
}
public List<UserUnlockBO> LoadUserLockHistory(int UserId)
{
DBEgine dbEgine = new DBEgine();
List<UserUnlockBO> LockList = new List<UserUnlockBO>();
try
{
dbEgine.DBOpen();
SqlParameter[] parms = new SqlParameter[1];
SqlParameter p1 = new SqlParameter();
p1.ParameterName = "@UserId";
p1.SqlDbType = SqlDbType.Int;
p1.Direction = ParameterDirection.Input;
p1.Value = UserId;
parms[0] = p1;
SqlDataReader drUser = dbEgine.ExecuteReader("UM_LoadUserLockHistory", parms);
LockList = FillUserUnlockInformation(drUser);
if (LockList != null)
{
return LockList;
}
return null;
}
catch
{
throw;
}
finally
{
dbEgine.DBClose();
}
}
public Guid CreateSession(UserSessionBO obj)
{
DBEgine dbEngine = new DBEgine();
try
{
dbEngine.DBOpen();
SqlParameter[] parms = new SqlParameter[3];
SqlParameter p1 = new SqlParameter();
p1.ParameterName = "UserId";
p1.SqlDbType = SqlDbType.Int;
p1.Value = obj.UserID;
parms[0] = p1;
SqlParameter p2 = new SqlParameter();
p2.ParameterName = "LanguageID";
p2.SqlDbType = SqlDbType.Int;
p2.Value = obj.LanguageID;
parms[1] = p2;
SqlParameter p3 = new SqlParameter();
p3.ParameterName = "UserSessionID";
p3.SqlDbType = SqlDbType.UniqueIdentifier;
p3.Direction = ParameterDirection.Output;
parms[2] = p3;
dbEngine.ExecNonQueryStoredProc("UM_CreateUserSession", parms);
return (Guid)p3.Value;
}
catch
{
throw;
}
finally
{
dbEngine.DBClose();
}
}
public bool EditGroup(UserGroupBO obj)
{
bool isUpdate = false;
DBEgine dbEgine = new DBEgine();
dbEgine.DBOpen();
SqlParameter[] parms = new SqlParameter[3];
SqlParameter p1 = new SqlParameter();
p1.ParameterName = "in_groupId";
p1.SqlDbType = SqlDbType.Int;
p1.Direction = ParameterDirection.Input;
p1.Value = obj.GroupId;
parms[0] = p1;
SqlParameter p2 = new SqlParameter();
p2.ParameterName = "in_groupName";
p2.SqlDbType = SqlDbType.NVarChar;
p2.Size = 50;
p2.Direction = ParameterDirection.Input;
p2.Value = obj.GroupName;
parms[1] = p2;
SqlParameter p3 = new SqlParameter();
p3.ParameterName = "in_modifiedUser";
p3.SqlDbType = SqlDbType.Int;
p3.Direction = ParameterDirection.Input;
p3.Value = obj.ModifiedUser;
parms[2] = p3;
dbEgine.ExecNonQueryStoredProc("UM_EditGroup", parms);
isUpdate = true;
dbEgine.DBClose();
return isUpdate;
}
public bool DeleteInvoice(InvoiceHeaderBO invoiceHeader)
{
DBEgine objDbAccess = new DBEgine();
bool result = false;
SqlTransaction tran = null;
try
{
SqlParameter[] parms = new SqlParameter[1];
SqlParameter parmInvoiceID = new SqlParameter();
parmInvoiceID.Value = invoiceHeader.InvoiceID;
parmInvoiceID.SqlDbType = SqlDbType.Int;
parmInvoiceID.Direction = ParameterDirection.Input;
parmInvoiceID.ParameterName = "@InvoiceID";
parms[0] = parmInvoiceID;
objDbAccess.DBOpen(tran);
if (objDbAccess.ExecNonQueryStoredProc("Sales.DeleteInvoices", parms) > 0)
{
result = true;
}
objDbAccess.DBTransAction.Commit();
return result;
}
catch
{
objDbAccess.DBTransAction.Rollback();
throw;
}
finally
{
objDbAccess.DBClose();
}
}
public int SaveDesignation(out int Id, DesignationBO obj)
{
DBEgine dbEgine = new DBEgine();
try
{
dbEgine.DBOpen();
SqlParameter[] parms = new SqlParameter[4];
SqlParameter designationIdParm = new SqlParameter();
designationIdParm.ParameterName = "DesignationId";
designationIdParm.SqlDbType = SqlDbType.Int;
designationIdParm.Direction = ParameterDirection.InputOutput;
designationIdParm.Value = obj.DesignationId;
parms[0] = designationIdParm;
SqlParameter designationParm = new SqlParameter();
designationParm.ParameterName = "Designation";
designationParm.SqlDbType = SqlDbType.NVarChar;
designationParm.Direction = ParameterDirection.Input;
designationParm.Value = obj.DesignationName;
parms[1] = designationParm;
SqlParameter descriptionParm = new SqlParameter();
descriptionParm.ParameterName = "Description";
descriptionParm.SqlDbType = SqlDbType.NVarChar;
descriptionParm.Direction = ParameterDirection.Input;
descriptionParm.Value = obj.Desciption;
parms[2] = descriptionParm;
SqlParameter IsActiveParm = new SqlParameter();
IsActiveParm.ParameterName = "IsActive";
IsActiveParm.SqlDbType = SqlDbType.Bit;
IsActiveParm.Direction = ParameterDirection.Input;
IsActiveParm.Value = obj.IsActive;
parms[3] = IsActiveParm;
dbEgine.ExecNonQueryStoredProc("UM_AddDesignations", parms);
Id = Int32.Parse(designationIdParm.Value.ToString());
return Id;
}
catch
{
throw;
}
finally
{
dbEgine.DBClose();
}
}
public DataSet GetDailySalesReport(DateTime fromDate,DateTime toDate)
{
DBEgine objDbAccess = new DBEgine();
try
{
objDbAccess.DBOpen();
DataSet ds = objDbAccess.ExecuteDataSet("SELECT * FROM Sales.DailySales('"+fromDate.ToShortDateString()+"','"+toDate.ToShortDateString()+"')");
return ds;
}
catch (Exception ex)
{
throw ex;
}
finally
{
objDbAccess.DBClose();
}
}
public bool SaveInvoice(InvoiceHeaderBO invoiceHeader, List<InvoiceDetailBO> invoiceDetail,ref string invoiceNumber,ref int invoiceID)
{
DBEgine objDbAccess = new DBEgine();
bool result = false;
SqlTransaction tran = null;
try
{
SqlParameter[] parms = new SqlParameter[8];
SqlParameter parmInvoiceID = new SqlParameter();
parmInvoiceID.Value = invoiceHeader.InvoiceID;
parmInvoiceID.SqlDbType = SqlDbType.Int;
parmInvoiceID.Direction = ParameterDirection.InputOutput;
parmInvoiceID.ParameterName = "@InvoiceID";
parms[0] = parmInvoiceID;
SqlParameter parmInvoiceNumber = new SqlParameter();
parmInvoiceNumber.Value = invoiceHeader.InvoiceNumber;
parmInvoiceNumber.SqlDbType = SqlDbType.VarChar;
parmInvoiceNumber.Direction = ParameterDirection.InputOutput;
parmInvoiceNumber.ParameterName = "@InvoiceNumber";
parms[1] = parmInvoiceNumber;
SqlParameter parmIsInvalied = new SqlParameter();
parmIsInvalied.Value = invoiceHeader.StatusID;
parmIsInvalied.SqlDbType = SqlDbType.Int;
parmIsInvalied.Direction = ParameterDirection.Input;
parmIsInvalied.ParameterName = "@StatusID";
parms[2] = parmIsInvalied;
SqlParameter parmTotal = new SqlParameter();
parmTotal.Value = invoiceHeader.Total;
parmTotal.SqlDbType = SqlDbType.Decimal;
parmTotal.Direction = ParameterDirection.Input;
parmTotal.ParameterName = "@Total";
parms[3] = parmTotal;
SqlParameter parmCreatedUser = new SqlParameter();
parmCreatedUser.Value = invoiceHeader.CreatedUser;
parmCreatedUser.SqlDbType = SqlDbType.Int;
parmCreatedUser.Direction = ParameterDirection.Input;
parmCreatedUser.ParameterName = "@CreatedUser";
parms[4] = parmCreatedUser;
SqlParameter parmCustomerID = new SqlParameter();
parmCustomerID.Value = invoiceHeader.CustomerID;
parmCustomerID.SqlDbType = SqlDbType.Int;
parmCustomerID.Direction = ParameterDirection.Input;
parmCustomerID.ParameterName = "@CustomerID";
parms[5] = parmCustomerID;
SqlParameter parmReceivedAmount = new SqlParameter();
parmReceivedAmount.Value = invoiceHeader.ReceivedAmount;
parmReceivedAmount.SqlDbType = SqlDbType.Decimal;
parmReceivedAmount.Direction = ParameterDirection.Input;
parmReceivedAmount.ParameterName = "@ReceivedAmount";
parms[6] = parmReceivedAmount;
SqlParameter parmBalance = new SqlParameter();
parmBalance.Value = invoiceHeader.Balance;
parmBalance.SqlDbType = SqlDbType.Int;
parmBalance.Direction = ParameterDirection.Input;
parmBalance.ParameterName = "@Balance";
parms[7] = parmBalance;
objDbAccess.DBOpen(tran);
if (objDbAccess.ExecNonQueryStoredProc("Sales.SaveInvoice", parms) > 0)
{
foreach (InvoiceDetailBO obj in invoiceDetail)
{
obj.InvoiceId = int.Parse(parms[0].Value.ToString());
bool success = SaveInvoiceDetail(objDbAccess, obj);
if (!success)
{
objDbAccess.DBTransAction.Rollback();
return false;
}
}
invoiceNumber = parms[1].Value.ToString();
invoiceID = int.Parse(parms[0].Value.ToString());
result = true;
}
objDbAccess.DBTransAction.Commit();
return result;
}
catch (SqlException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
finally
{
objDbAccess.DBClose();
}
}
public List<InvoiceLookUp> LoadAllInvoicesForLookUp()
{
DBEgine objDbAccess = new DBEgine();
var list = new List<InvoiceLookUp>();
try
{
objDbAccess.DBOpen();
SqlDataReader dr = objDbAccess.ExecuteReader("Sales.GetInvoiceDetailsLookUp", null);
while (dr.Read())
{
var invoice = new InvoiceLookUp();
invoice.InvoiceID = int.Parse(dr["InvoiceID"].ToString());
invoice.InvoiceNumber = dr["InvoiceNumber"].ToString();
invoice.Status = dr["Status"].ToString();
invoice.Total = Convert.ToDecimal(dr["Total"].ToString());
list.Add(invoice);
}
return list;
}
catch (Exception ex)
{
throw ex;
}
finally
{
objDbAccess.DBClose();
}
}
public DataSet GetSalesRefundReport(DateTime fromDate, DateTime toDate, int subItemID,decimal amountFrom,decimal amountTo)
{
DBEgine objDbAccess = new DBEgine();
try
{
SqlParameter[] parms = new SqlParameter[5];
SqlParameter parmFromDate = new SqlParameter();
parmFromDate.Value = fromDate;
parmFromDate.SqlDbType = SqlDbType.Date;
parmFromDate.Direction = ParameterDirection.Input;
parmFromDate.ParameterName = "@SalesDateFrom";
parms[0] = parmFromDate;
SqlParameter parmSalesDateTo = new SqlParameter();
parmSalesDateTo.Value = toDate;
parmSalesDateTo.SqlDbType = SqlDbType.Date;
parmSalesDateTo.Direction = ParameterDirection.Input;
parmSalesDateTo.ParameterName = "@SalesDateTo";
parms[1] = parmSalesDateTo;
SqlParameter parmPLUID = new SqlParameter();
parmPLUID.Value = subItemID;
parmPLUID.SqlDbType = SqlDbType.Int;
parmPLUID.Direction = ParameterDirection.Input;
parmPLUID.ParameterName = "@PLUID";
parms[2] = parmPLUID;
SqlParameter parmAmountFrom = new SqlParameter();
parmAmountFrom.Value = amountFrom;
parmAmountFrom.SqlDbType = SqlDbType.Decimal;
parmAmountFrom.Direction = ParameterDirection.Input;
parmAmountFrom.ParameterName = "@AmountFrom";
parms[3] = parmAmountFrom;
SqlParameter parmAmountTo = new SqlParameter();
parmAmountTo.Value = amountTo;
parmAmountTo.SqlDbType = SqlDbType.Decimal;
parmAmountTo.Direction = ParameterDirection.Input;
parmAmountTo.ParameterName = "@AmountTo";
parms[4] = parmAmountTo;
objDbAccess.DBOpen();
DataSet ds = objDbAccess.ExecuteDataSet("Sales.RefundedSalesReport",parms);
return ds;
}
catch (Exception ex)
{
throw ex;
}
finally
{
objDbAccess.DBClose();
}
}
public List<InvoiceDetailBO> GetInvoiceDetailByInvoiceID(int invoiceID)
{
DBEgine objDbAccess = new DBEgine();
var list = new List<InvoiceDetailBO>();
try
{
SqlParameter[] parms = new SqlParameter[1];
SqlParameter parmItemID = new SqlParameter();
parmItemID.Value = invoiceID;
parmItemID.SqlDbType = SqlDbType.Int;
parmItemID.Direction = ParameterDirection.Input;
parmItemID.ParameterName = "@InvoiceID";
parms[0] = parmItemID;
objDbAccess.DBOpen();
SqlDataReader dr = objDbAccess.ExecuteReader("Sales.GetInvoiceDetailByInvoiceID", parms);
while (dr.Read())
{
var invoiceDetailBO = new InvoiceDetailBO();
invoiceDetailBO.InvoiceDetailId = int.Parse(dr["InvoiceDetailID"].ToString());
invoiceDetailBO.InvoiceId = int.Parse(dr["InvoiceID"].ToString());
invoiceDetailBO.ItemId = int.Parse(dr["ItemID"].ToString());
invoiceDetailBO.Quantity = decimal.Parse(dr["Qty"].ToString());
invoiceDetailBO.SubTotal = decimal.Parse(dr["SubTotal"].ToString());
invoiceDetailBO.ItemSerial = dr["SubItemSerial"].ToString();
invoiceDetailBO.ItemDescription = dr["SubItemDescription"].ToString();
list.Add(invoiceDetailBO);
}
return list;
}
catch (Exception ex)
{
throw ex;
}
finally
{
objDbAccess.DBClose();
}
}
public List<UserBranchesDetailsBO> LoadAllUserBranchesByUserID(int userId)
{
List<UserBranchesDetailsBO> userBranchesList=new List<UserBranchesDetailsBO>();
UserBranchesDetailsBO userBranch = new UserBranchesDetailsBO();
DBEgine dbEgine = new DBEgine();
try{
SqlParameter[] parms = new SqlParameter[1];
SqlParameter p1 = new SqlParameter();
p1.ParameterName = "UserID";
p1.SqlDbType = SqlDbType.Int;
p1.Direction = ParameterDirection.Input;
p1.Value = userId;
parms[0] = p1;
dbEgine.DBOpen();
SqlDataReader drUserBranches = dbEgine.ExecuteReader("UserManagement.UM_LoadUserBranchesByUserID", parms);
if (drUserBranches != null && drUserBranches.HasRows )
{
while(drUserBranches.Read()){
userBranch=FillUserBranches(drUserBranches);
userBranchesList.Add(userBranch);
}
}
return userBranchesList;
}
catch{
throw;
}
finally{
dbEgine.DBClose();
}
}
public UserBranchesDetailsBO LoadUserBrancheByUserIDANDBranchID(int userId,int branchId)
{
List<UserBranchesDetailsBO> userBranchesList = new List<UserBranchesDetailsBO>();
UserBranchesDetailsBO userBranch = new UserBranchesDetailsBO();
DBEgine dbEgine = new DBEgine();
try
{
SqlParameter[] parms = new SqlParameter[2];
SqlParameter p1 = new SqlParameter();
p1.ParameterName = "UserID";
p1.SqlDbType = SqlDbType.Int;
p1.Direction = ParameterDirection.Input;
p1.Value = userId;
parms[0] = p1;
SqlParameter p2 = new SqlParameter();
p2.ParameterName = "BranchID";
p2.SqlDbType = SqlDbType.Int;
p2.Direction = ParameterDirection.Input;
p2.Value = branchId;
parms[1] = p2;
dbEgine.DBOpen();
SqlDataReader drUserBranches = dbEgine.ExecuteReader("UserManagement.UM_LoadUserBranchByUserIDANDBranchID", parms);
if (drUserBranches != null && drUserBranches.HasRows && drUserBranches.Read())
{
userBranch = FillUserBranches(drUserBranches);
}
return userBranch;
}
catch
{
throw;
}
finally
{
dbEgine.DBClose();
}
}