public JsonResult GetAssetByVendor(string id)
{
int idC;
int.TryParse(id, out idC);
//var VendorList = (from asstReg in db.tr_asset_registration
// join dept in db.tr_depreciation
// on asstReg.asset_id equals dept.asset_id
// where (asstReg.vendor_id == idC)
// select new
// {
// asstReg.asset_number
// , asstReg.asset_name
// , AssetValue = 0
// , AssetLifeTime = ""
// });
string constring = WebConfigurationManager.ConnectionStrings["ModelAsmRemote"].ConnectionString;
var conn = new SqlConnection(constring);
var cmd = new SqlCommand("getvendorasset", conn);
cmd.CommandText = "select * from dbo.FN_GET_ASSET_VALUE_BY_DEPTYPE(@vendor_id,@asset_id,@depreciation_type_id)";
cmd.Parameters.AddWithValue("@vendor_id", idC);
cmd.Parameters.AddWithValue("@asset_id", 0);
cmd.Parameters.AddWithValue("@depreciation_type_id", 1);
List <VendorAssetViewModel> VendorAssetList = new List <VendorAssetViewModel>();
conn.Open();
//var vendlist = cmd.ExecuteNonQuery();
using (SqlDataReader dr = cmd.ExecuteReader())
{
var tb = new DataTable();
tb.Load(dr);
foreach (DataRow row in tb.Rows)
{
VendorAssetViewModel vn = new VendorAssetViewModel();
vn.asset_number = row["asset_number"].ToString();
vn.asset_name = row["asset_name"].ToString();
vn.asset_price_fiskal = decimal.Parse(row["asset_price_fiskal"].ToString());
vn.asset_price_market = decimal.Parse(row["asset_price_market"].ToString());
vn.usage_life_time_fiskal = row["usage_life_time_fiskal"].ToString();
vn.usage_life_time_market = row["usage_life_time_market"].ToString();
VendorAssetList.Add(vn);
}
}
conn.Close();
return(Json(new { data = VendorAssetList.ToList() }, JsonRequestBehavior.AllowGet));
}
public JsonResult CostDepreByPeriod(string id)
{
int AssetID;
//int.TryParse(Request.Form["asset_id"], out AssetID);
int.TryParse(id, out AssetID);
List <VendorAssetViewModel> VendorAssetList = new List <VendorAssetViewModel>();
string constring = WebConfigurationManager.ConnectionStrings["ModelAsmRemote"].ConnectionString;
var conn = new SqlConnection(constring);
var cmd2 = new SqlCommand("GetData", conn);
cmd2.CommandText = "EXEC SP_COST_DEPRECIATION @asset_id ";
cmd2.Parameters.AddWithValue("@asset_id", AssetID);
conn.Open();
using (SqlDataReader dr = cmd2.ExecuteReader())
{
var tb = new DataTable();
tb.Load(dr);
foreach (DataRow row in tb.Rows)
{
VendorAssetViewModel vn = new VendorAssetViewModel();
vn.fis_change_period = int.Parse(row["fis_depre_correction_month"].ToString());
vn.mkt_change_period = int.Parse(row["mkt_depre_correction_month"].ToString());
vn.variant_fiskal = decimal.Parse(row["variant_fis_cost"].ToString());
vn.variant_market = decimal.Parse(row["variant_mkt_cost"].ToString());
VendorAssetList.Add(vn);
}
}
conn.Close();
return(Json(VendorAssetList.ToList(), JsonRequestBehavior.AllowGet));
//return Json(new { data = VendorAssetList.ToList() }, JsonRequestBehavior.AllowGet);
//return Json(AssetList, JsonRequestBehavior.AllowGet);
}
public JsonResult GetAssetList(string id)
{
int AssetID = int.Parse(id);
if (AssetID == 0)
{
List <VendorAssetViewModel> VendorAssetList = new List <VendorAssetViewModel>();
string constring = WebConfigurationManager.ConnectionStrings["ModelAsmRemote"].ConnectionString;
var conn = new SqlConnection(constring);
var cmd2 = new SqlCommand("GetData", conn);
cmd2.CommandText = "EXEC SP_GET_ALL_ASSET_TO_PROCESS @company_id";
cmd2.Parameters.AddWithValue("@company_id", UserProfile.company_id);
conn.Open();
using (SqlDataReader dr = cmd2.ExecuteReader())
{
var tb = new DataTable();
tb.Load(dr);
foreach (DataRow row in tb.Rows)
{
VendorAssetViewModel vn = new VendorAssetViewModel();
vn.asset_id = int.Parse(row["asset_id"].ToString());
vn.asset_number = row["asset_number"].ToString();
vn.asset_name = row["asset_name"].ToString();
vn.asset_receipt_date = DateTime.Parse(row["asset_receipt_date"].ToString());
VendorAssetList.Add(vn);
}
}
conn.Close();
return(Json(new { data = VendorAssetList.ToList() }, JsonRequestBehavior.AllowGet));
//var AssetList = db.tr_asset_registration.Where(t => t.deleted_date == null && t.fl_active == true).Select(
//t => new
//{
// t.asset_id,
// t.asset_number,
// t.asset_name,
// t.asset_receipt_date
//}).ToList();
//return Json(new { data = AssetList.ToList() }, JsonRequestBehavior.AllowGet);
}
else
{
var AssetList = db.tr_asset_registration.Where(t => t.deleted_date == null &&
t.asset_id == AssetID &&
t.fl_active == true
//&& t.current_location_id == UserProfile.location_id
).Select(
t => new
{
t.asset_id,
t.asset_number,
t.asset_name,
t.asset_receipt_date,
t.fl_active
}).ToList();
//return Json(new { data = AssetList.ToList() }, JsonRequestBehavior.AllowGet);
return(Json(AssetList.ToList(), JsonRequestBehavior.AllowGet));
}
//return Json(AssetList, JsonRequestBehavior.AllowGet);
}
public JsonResult SaveChange()
{
#region Variable Declaration
int CurrUser, OrgID, ChangeReason, depreciationID;
string mode;
int AssetID, DepreciationTypeID, OriCurrencyID;
DateTime elipsDate;
Decimal OriValue, usdKurs, idrKurs, BookValue, BookValueIDR;
int FisUsefulLife, MktUsefulLife;
Decimal FisResiduValue, MktResiduValue;
#endregion
#region Variable Value Assign
CurrUser = UserProfile.UserId;
OrgID = UserProfile.OrgId;
mode = Request.Form["mode"].ToString();
int.TryParse(Request.Form["asset_id"], out AssetID);
int.TryParse(Request.Form["depre_method"], out DepreciationTypeID);
int.TryParse(Request.Form["currency"], out OriCurrencyID);
int.TryParse(Request.Form["change_reason"], out ChangeReason);
int.TryParse(Request.Form["depreciation_id"], out depreciationID);
Decimal.TryParse(Request.Form["price"], out OriValue);
var a_date = Request.Form["elips_date"];
string[] arr_part = a_date.Split('-');
string d_date = null;
if (arr_part.Length >= 2)
{
d_date = arr_part[2] + "/" + arr_part[1] + "/" + arr_part[0];
}
else
{
d_date = string.Format("{0:yyyy/MM/dd}", DateTime.Now);
}
DateTime.TryParse(d_date, out elipsDate);
//DateTime.TryParse(Request.Form["elips_date"], out elipsDate);
Decimal.TryParse(Request.Form["usd_kurs"], out usdKurs);
Decimal.TryParse(Request.Form["idr_kurs"], out idrKurs);
Decimal.TryParse(Request.Form["asset_value_usd"], out BookValue);
Decimal.TryParse(Request.Form["asset_value_idr"], out BookValueIDR);
int.TryParse(Request.Form["fiskal_life_value"], out FisUsefulLife);
int.TryParse(Request.Form["komersil_life_value"], out MktUsefulLife);
Decimal.TryParse(Request.Form["fiskal_residu_value"], out FisResiduValue);
Decimal.TryParse(Request.Form["komersil_residu_value"], out MktResiduValue);
string constring = WebConfigurationManager.ConnectionStrings["ModelAsmRemote"].ConnectionString;
#endregion
var conn = new SqlConnection(constring);
var cmd = new SqlCommand("InsertDepreciation", conn);
cmd.CommandText = "Exec SP_INSERT_CHANGE_TR_DEPRECIATION @asset_id,@depreciation_type_id, @asset_original_value,@asset_original_currency_id ,@elips_date, @usd_kurs, @idr_kurs ,@asset_book_value,@asset_book_value_idr ,@fis_asset_residu_value ,@fis_asset_usefull_life ,@mkt_asset_residu_value ,@mkt_asset_usefull_life ,@created_by ,@orgID,@fl_change_reason,@depreciation_id ";
cmd.Parameters.AddWithValue("@asset_id", AssetID);
cmd.Parameters.AddWithValue("@depreciation_type_id", DepreciationTypeID);
cmd.Parameters.AddWithValue("@asset_original_value", OriValue);
cmd.Parameters.AddWithValue("@asset_original_currency_id", OriCurrencyID);
cmd.Parameters.AddWithValue("@elips_date", elipsDate);
cmd.Parameters.AddWithValue("@usd_kurs", usdKurs);
cmd.Parameters.AddWithValue("@idr_kurs", idrKurs);
cmd.Parameters.AddWithValue("@asset_book_value", BookValue);
cmd.Parameters.AddWithValue("@asset_book_value_idr", BookValueIDR);
cmd.Parameters.AddWithValue("@created_by", CurrUser);
cmd.Parameters.AddWithValue("@orgID", OrgID);
cmd.Parameters.AddWithValue("@fis_asset_residu_value", FisResiduValue);
cmd.Parameters.AddWithValue("@fis_asset_usefull_life", FisUsefulLife);
cmd.Parameters.AddWithValue("@mkt_asset_residu_value", MktResiduValue);
cmd.Parameters.AddWithValue("@mkt_asset_usefull_life", MktUsefulLife);
cmd.Parameters.AddWithValue("@fl_change_reason", ChangeReason);
cmd.Parameters.AddWithValue("@depreciation_id", depreciationID);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
var cmd2 = new SqlCommand("ChangeDepreciation", conn);
cmd2.CommandText = "Exec SP_INSERT_CHANGE_TR_DEPRECIATION_PROCESS @asset_id,@depreciation_type_id,@created_by ,@orgID ";
cmd2.Parameters.AddWithValue("@asset_id", AssetID);
cmd2.Parameters.AddWithValue("@depreciation_type_id", DepreciationTypeID);
cmd2.Parameters.AddWithValue("@created_by", CurrUser);
cmd2.Parameters.AddWithValue("@orgID", OrgID);
conn.Open();
cmd2.ExecuteNonQuery();
conn.Close();
List <VendorAssetViewModel> VendorAssetList = new List <VendorAssetViewModel>();
var cmd3 = new SqlCommand("ShowSimulationData", conn);
cmd3.CommandText = "EXEC SP_GET_DEPRECIATION_SIMULATION @asset_id,@depreciation_type_id";
cmd3.Parameters.AddWithValue("@asset_id", AssetID);
cmd3.Parameters.AddWithValue("@depreciation_type_id", DepreciationTypeID);
conn.Open();
using (SqlDataReader dr = cmd3.ExecuteReader())
{
var tb = new DataTable();
tb.Load(dr);
foreach (DataRow row in tb.Rows)
{
VendorAssetViewModel vn = new VendorAssetViewModel();
vn.periode = row["period"].ToString();
vn.asset_price_fiskal_before = decimal.Parse(row["asset_price_fiskal_before"].ToString());
vn.asset_price_fiskal_after = decimal.Parse(row["asset_price_fiskal_after"].ToString());
vn.variant_fiskal = vn.asset_price_fiskal_after - vn.asset_price_fiskal_before;
vn.asset_price_market_before = decimal.Parse(row["asset_price_market_before"].ToString());
vn.asset_price_market_after = decimal.Parse(row["asset_price_market_after"].ToString());
vn.variant_market = vn.asset_price_market_after - vn.asset_price_market_before;
VendorAssetList.Add(vn);
}
}
conn.Close();
return(Json(VendorAssetList.ToList(), JsonRequestBehavior.AllowGet));
//return Json("Change Depreciation Success", JsonRequestBehavior.AllowGet);
}
public JsonResult ProcessGenerate()
{
int CurrUser = UserProfile.UserId;
int OrgID = UserProfile.OrgId;
List <VendorAssetViewModel> VendorAssetList = new List <VendorAssetViewModel>();
#region remark
int AssetID, DepreciationTypeID, OriCurrencyID;
DateTime elipsDate;
Decimal OriValue, usdKurs, idrKurs, BookValue;
int FisUsefulLife, MktUsefulLife;
Decimal FisResiduValue, FisDDBPercentage, MktResiduValue, MktDDBPercentage;
string mode;
int.TryParse(Request.Form["asset_id"], out AssetID);
int.TryParse(Request.Form["depre_method"], out DepreciationTypeID);
int.TryParse(Request.Form["currency"], out OriCurrencyID);
mode = Request.Form["mode"].ToString();
Decimal.TryParse(Request.Form["price"], out OriValue);
DateTime.TryParse(Request.Form["elips_date"], out elipsDate);
Decimal.TryParse(Request.Form["usd_kurs"], out usdKurs);
Decimal.TryParse(Request.Form["idr_kurs"], out idrKurs);
Decimal.TryParse(Request.Form["asset_value_usd"], out BookValue);
int.TryParse(Request.Form["fiskal_life_value"], out FisUsefulLife);
int.TryParse(Request.Form["komersil_life_value"], out MktUsefulLife);
Decimal.TryParse(Request.Form["fiskal_residu_value"], out FisResiduValue);
Decimal.TryParse(Request.Form["komersil_residu_value"], out MktResiduValue);
Decimal.TryParse(Request.Form["fiskal_depreciation"], out FisDDBPercentage);
Decimal.TryParse(Request.Form["komersial_depreciation"], out MktDDBPercentage);
#endregion
string constring = WebConfigurationManager.ConnectionStrings["ModelAsmRemote"].ConnectionString;
var conn = new SqlConnection(constring);
var cmd = new SqlCommand("InsertDepreciation", conn);
cmd.CommandText = "Exec SP_INSERT_TR_DEPRECIATION @asset_id,@depreciation_type_id, @asset_original_value,@asset_original_currency_id ,@elips_date ,@usd_kurs ,@idr_kurs, @asset_book_value ,@fis_asset_residu_value ,@fis_asset_usefull_life ,@fis_ddb_percentage ,@mkt_asset_residu_value ,@mkt_asset_usefull_life ,@mkt_ddb_percentage ,@created_by ,@orgID ";
cmd.Parameters.AddWithValue("@asset_id", AssetID);
cmd.Parameters.AddWithValue("@depreciation_type_id", DepreciationTypeID);
cmd.Parameters.AddWithValue("@asset_original_value", OriValue);
cmd.Parameters.AddWithValue("@asset_original_currency_id", OriCurrencyID);
cmd.Parameters.AddWithValue("@elips_date", elipsDate);
cmd.Parameters.AddWithValue("@usd_kurs", usdKurs);
cmd.Parameters.AddWithValue("@idr_kurs", idrKurs);
cmd.Parameters.AddWithValue("@asset_book_value", BookValue);
cmd.Parameters.AddWithValue("@created_by", CurrUser);
cmd.Parameters.AddWithValue("@orgID", OrgID);
cmd.Parameters.AddWithValue("@fis_asset_residu_value", FisResiduValue);
cmd.Parameters.AddWithValue("@fis_asset_usefull_life", FisUsefulLife);
cmd.Parameters.AddWithValue("@fis_ddb_percentage", FisDDBPercentage);
cmd.Parameters.AddWithValue("@mkt_asset_residu_value", MktResiduValue);
cmd.Parameters.AddWithValue("@mkt_asset_usefull_life", MktUsefulLife);
cmd.Parameters.AddWithValue("@mkt_ddb_percentage", MktDDBPercentage);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
var cmd2 = new SqlCommand("InsertDepreciation", conn);
cmd2.CommandText = "select * from dbo.FN_GET_ASSET_HISTORY_BY_DEPRETYPE(@asset_id,@depreciation_type_id)";
cmd2.Parameters.AddWithValue("@asset_id", AssetID);
cmd2.Parameters.AddWithValue("@depreciation_type_id", DepreciationTypeID);
conn.Open();
using (SqlDataReader dr = cmd2.ExecuteReader())
{
var tb = new DataTable();
tb.Load(dr);
foreach (DataRow row in tb.Rows)
{
VendorAssetViewModel vn = new VendorAssetViewModel();
//vn.asset_id = int.Parse(row["asset_id"].ToString());
//vn.depreciation_type_id = int.Parse(row["depreciation_type_id"].ToString());
//vn.fis_asset_usefull_life = decimal.Parse(row["fis_asset_usefull_life"].ToString());
//vn.mkt_asset_usefull_life = decimal.Parse(row["mkt_asset_usefull_life"].ToString());
//vn.periode = int.Parse(row["period"].ToString());
vn.periode = row["period"].ToString();
//vn.asset_price_fiskal = decimal.Parse(row["asset_price_fiskal"].ToString());
//vn.asset_price_market = decimal.Parse(row["asset_price_market"].ToString());
//vn.fis_depreciation_value = decimal.Parse(row["fis_depreciation_value"].ToString());
//vn.mkt_depreciation_value = decimal.Parse(row["mkt_depreciation_value"].ToString());
vn.asset_price_fiskal_before = decimal.Parse(row["asset_price_fiskal_before"].ToString());
vn.asset_price_fiskal_after = decimal.Parse(row["asset_price_fiskal_after"].ToString());
vn.variant_fiskal = vn.asset_price_fiskal_after - vn.asset_price_fiskal_before;
vn.asset_price_market_before = decimal.Parse(row["asset_price_market_before"].ToString());
vn.asset_price_market_after = decimal.Parse(row["asset_price_market_after"].ToString());
vn.variant_market = vn.asset_price_market_after - vn.asset_price_market_before;
//vn.asset_number = row["asset_number"].ToString();
//vn.asset_name = row["asset_name"].ToString();
//vn.asset_price_fiskal = decimal.Parse(row["asset_price_fiskal"].ToString());
//vn.asset_price_market = decimal.Parse(row["asset_price_market"].ToString());
//vn.usage_life_time_fiskal = row["usage_life_time_fiskal"].ToString();
//vn.usage_life_time_market = row["usage_life_time_market"].ToString();
//vn.fis_book_value = decimal.Parse(row["fis_book_value"].ToString());
//vn.mkt_book_value = decimal.Parse(row["mkt_book_value"].ToString());
VendorAssetList.Add(vn);
}
}
conn.Close();
return(Json(VendorAssetList.ToList(), JsonRequestBehavior.AllowGet));
//return Json(VendorAssetList, JsonRequestBehavior.AllowGet);
}
