/// <summary>
/// Insert Used Computer Accessories
/// </summary>
/// <param name="purchaseId">Purchase ID</param>
/// <param name="assetId">Asset ID</param>
/// <param name="operatingComputerId">Operating Computer ID</param>
/// <param name="description">Description</param>
/// <param name="accessoryType">Accessory Type</param>
/// <param name="restoredDate">Restored Date</param>
public void InsertUsedComputerAccessories(long purchaseId, long assetId, long operatingComputerId, string description, string accessoryType, DateTime restoredDate)
{
string insertUsedcomputerAcc = String.Format(
@" INSERT INTO {0} (AssetPurchaseId, AssetId, OperatingComputerId, Description, AccessoryType, RestoredDate, Status)
VALUES ({1}, {2}, {3}, '{4}', '{5}', '{6}', {7})",
TableConstant.TBL_USED_COMPUTER_ACCESSORY,
purchaseId,
assetId,
operatingComputerId,
description,
accessoryType,
restoredDate,
1
);
ado.ExecNonQuery(insertUsedcomputerAcc);
}
/// <summary>
/// Adds Asset Category
/// </summary>
/// <param name="acv">AssetCategory</param>
public void AddAssetCategory(AssetCategory aavm)
{
var assetCategoryName = aavm.Name;
var categoryKey = aavm.CategoryKey;
var status = aavm.Status;
var obj = ado.ExecNonQuery(
String.Format(
@" INSERT INTO {0} (Name, CategoryKey, Status)
OUTPUT Inserted.ID, Inserted.Name
VALUES ('{1}', '{2}', {3})",
TableConstant.TBL_ASSET_CATEGORY,
assetCategoryName,
categoryKey,
status
));
}
/// <summary>
/// Creates Annual Budget
/// </summary>
/// <param name="bvm">BudgetViewModel</param>
public void CreateAnnualBudget(BudgetAdjustmentViewModel bavm)
{
string QueryString = String.Format(
@" INSERT INTO {0}
([Year], BudgetProvision, StartDate, EndDate, CreatedDate, [Status])
VALUES
('{1}', {2}, '{3}', '{4}', '{5}', {6})",
TableConstant.TBL_ANNUAL_BUDGET,
bavm.Year,
bavm.BudgetProvision,
bavm.StartDate,
bavm.EndDate,
bavm.CreatedDate,
bavm.Status
);
ado.ExecNonQuery(QueryString);
}
/// <summary>
/// Updates User
/// </summary>
/// <param name="uuvm">UpdateUserViewModel object</param>
/// <param name="userid">User ID</param>
public void UpdateUser(UpdateUserViewModel uuvm, long userid)
{
var QueryString = String.Format(
@" UPDATE {0} SET Email = '{1}',
Image = '{2}',
Password = '******',
ModifiedDate = '{4}'
WHERE Id = {5}",
TableConstant.TBL_USER,
uuvm.Email,
uuvm.Image,
uuvm.Password,
uuvm.ModifiedDate,
userid
);
ado.ExecNonQuery(QueryString);
}
/// <summary>
/// Update Asset by AssetId
/// </summary>
/// <param name="aavm">AddAssetViewModel</param>
/// <param name="assetId">AssetId</param>
public void UpdateAsset(AddAssetViewModel aavm, long assetId)
{
//Update Asset
string UpdateQuery = String.Format(
@" UPDATE {0} SET AssetCategoryId = {1}, Name = '{2}',
AssetKey = '{3}',
Model = '{4}', Manufacturer = '{5}',
ConfigurationInfo = '{6}', Status = {7}
WHERE Id = {8}",
TableConstant.TBL_ASSET,
aavm.AssetCategoryId,
aavm.AssetName,
aavm.AssetKey,
aavm.ModelNo,
aavm.Manufacturer,
aavm.ConfigurationInfo,
1,
assetId
);
ado.ExecNonQuery(UpdateQuery);
}
/// <summary>
/// Changes Quotation Approval Status to 1 or 0
/// </summary>
/// <param name="id">Purchase Quotation ID</param>
/// <param name="status">Pervious Status (1 or 0)</param>
/// <returns>Status 1 or 0</returns>
public int ChangeQuotationApprovalStatus(int id, int status)
{
int updateQuoteStatus;
if (status == 1)
{
updateQuoteStatus = ado.ExecNonQuery("UPDATE " + TableConstant.TBL_PURCHASE_QUOTATION + " SET IsApproved = 0 WHERE Id = " + id);
return(0);
}
else
{
updateQuoteStatus = ado.ExecNonQuery("UPDATE " + TableConstant.TBL_PURCHASE_QUOTATION + " SET IsApproved = 1 WHERE Id = " + id);
return(1);
}
}
private int SaveinDB(int iCount, EmailQueue email, bool isRemove)
{
try
{
using (AdoHelper db = new AdoHelper())
{
string Query = isRemove ? string.Format("DELETE FROM dbo.EmailQueues WHERE ID={0} AND SiteID={1}", email.ID, email.SiteID) : string.Format("UPDATE EmailQueues SET NumberOfTries={0} , LastAttempt=GETDATE(),LastErrorMessage='{1}' WHERE ID={2} AND SiteID={3}", email.NumberOfTries, email.LastErrorMessage, email.ID, email.SiteID);
SqlCommand cmd = db.CreateCommand(Query, false);
iCount = db.ExecNonQuery(cmd, false);
}
// iCount += objDbContext.SaveChanges();
// logger.Log("No of EmailQueue rows affected are : " + iCount, Logger.LogType.EMAILQUEUE);
}
catch (SqlException dbEx)
{
logger.LogException(dbEx);
}
return(iCount);
}
/// <summary>
/// Submits Purchase Asset Info From Quotation
/// </summary>
/// <param name="objPurchaseAsset">MakePurchaseFromQuotationViewModel object</param>
/// <param name="purchaseQuotationId">Purchase Quotation ID</param>
/// <returns>Rollback Flag 1 or 0</returns>
public int SubmitPurchaseAssetInfoFromQuotation(MakePurchaseFromQuotationViewModel objPurchaseAsset, long purchaseQuotationId)
{
var rollbackFlag = 0;
/*Start of Stored Procedure for Purchase Asset*/
using (SqlConnection con = new SqlConnection(TableConstant.CONNECTION_STRING))
{
using (SqlCommand cmd = new SqlCommand("dbo.usp_PurchaseAssetIndividual"))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = con;
cmd.Parameters.AddWithValue("@PurchaseNo", objPurchaseAsset.PurchaseNoId);
cmd.Parameters.AddWithValue("@AssetId", (int)objPurchaseAsset.AssetListId);
cmd.Parameters.AddWithValue("@PricePerUnit", objPurchaseAsset.Price);
cmd.Parameters.AddWithValue("@VAT", objPurchaseAsset.VAT);
cmd.Parameters.AddWithValue("@PurchasedDate", objPurchaseAsset.PurchasedDate);
cmd.Parameters.AddWithValue("@AssetQuantity", objPurchaseAsset.Quantity);
cmd.Parameters.AddWithValue("@PurchaseType", objPurchaseAsset.PurchaseType);
//cmd.Parameters.AddWithValue("@CurrentDepreciation", objPurchaseAsset.CurrentDepreciationValue);
cmd.Parameters.AddWithValue("@VendorId", objPurchaseAsset.VendorListId);
cmd.Parameters.AddWithValue("@HasAmc", int.Parse(objPurchaseAsset.HasAmc));
cmd.Parameters.AddWithValue("@AmcStartDate", objPurchaseAsset.AMCStartDate);
cmd.Parameters.AddWithValue("@AmcCost", (objPurchaseAsset.AMCCost != "") ? Decimal.Parse(objPurchaseAsset.AMCCost) : 0);
cmd.Parameters.AddWithValue("@HasWarranty", int.Parse(objPurchaseAsset.HasWarranty));
cmd.Parameters.AddWithValue("@WarrantyValidityDate", objPurchaseAsset.WarrantyValidityDate);
cmd.Parameters.AddWithValue("@AnnualBudgetId", objPurchaseAsset.AnnualBudgetId);
cmd.Parameters.AddWithValue("@PurchaseQuotation", purchaseQuotationId);
cmd.Parameters.AddWithValue("@UserId", objPurchaseAsset.UserId);
cmd.Parameters.AddWithValue("@Username", objPurchaseAsset.Username);
var returnParameter = cmd.Parameters.Add("@RollBackFlag", SqlDbType.Int);
returnParameter.Direction = ParameterDirection.ReturnValue;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
rollbackFlag = (int)returnParameter.Value;
}
}
/*End of Stored Procedure for Purchase Asset*/
/**Query to maintain closing of Purchase Quotation Entry Log**/
var QueryString = String.Format(
@" INSERT INTO {0} (AssetId, AnnualBudgetId, UserId, AssetPrice, VAT, Quantity, QuotedDate, IsApproved, PurchaseType, ActionType, Username, CreatedDate)
VALUES ({1}, {2}, {3}, {4}, {5}, {6}, '{7}', {8}, '{9}', '{10}', '{11}', '{12}')",
TableConstant.TBL_PURCHASE_QUOTATION_ENTRY_LOG,
(int)objPurchaseAsset.AssetListId,
objPurchaseAsset.AnnualBudgetId,
objPurchaseAsset.UserId,
objPurchaseAsset.Price,
objPurchaseAsset.VAT,
objPurchaseAsset.Quantity,
objPurchaseAsset.quotationDetails.QuotedDate,
1,
objPurchaseAsset.PurchaseType,
"Close",
objPurchaseAsset.Username,
DateTime.Now);
ado.ExecNonQuery(QueryString);
/**Query to maintain closing of Purchase Quotation Entry Log**/
return(rollbackFlag);
}
/// <summary>
/// Deletes Asset By ID
/// </summary>
/// <param name="id">Asset Log ID</param>
public void DeleteAssetById(long id)
{
ado.ExecNonQuery(
String.Format("DELETE FROM {0} WHERE Id = {1}", TableConstant.TBL_ASSET_ENTRY_LOG, id)
);
}