public async Task <IActionResult> Post([FromQuery] Int32 hid, Int32 loanAccountID, Int32?tmpdocid, [FromBody] FinanceDocumentUIViewModel repaydoc)
{
if (!ModelState.IsValid)
{
return(BadRequest(ModelState));
}
// The post here is:
// 1. Post a repayment document with the content from this template doc
// 2. Update the template doc with REFDOCID
// 3. If the account balance is zero, close the account;
// Basic check
if (hid <= 0 || (tmpdocid.HasValue && tmpdocid.Value <= 0) ||
loanAccountID <= 0 ||
repaydoc == null || repaydoc.HID != hid ||
repaydoc.DocType != FinanceDocTypeViewModel.DocType_Repay)
{
return(BadRequest("No data inputted!"));
}
SqlConnection conn = null;
SqlCommand cmd = null;
SqlDataReader reader = null;
SqlTransaction tran = null;
String queryString = String.Empty;
String strErrMsg = String.Empty;
HttpStatusCode errorCode = HttpStatusCode.OK;
Decimal acntBalance = 0M;
String usrName = String.Empty;
if (Startup.UnitTestMode)
{
usrName = UnitTestUtility.UnitTestUser;
}
else
{
var usrObj = HIHAPIUtility.GetUserClaim(this);
usrName = usrObj.Value;
}
if (String.IsNullOrEmpty(usrName))
{
return(BadRequest("User cannot recognize"));
}
// Update the database
FinanceTmpDocLoanViewModel vmTmpDoc = new FinanceTmpDocLoanViewModel();
HomeDefViewModel vmHome = new HomeDefViewModel();
FinanceAccountUIViewModel vmAccount = new FinanceAccountUIViewModel();
try
{
using (conn = new SqlConnection(Startup.DBConnectionString))
{
await conn.OpenAsync();
// Check: HID, it requires more info than just check, so it implemented it
try
{
HIHAPIUtility.CheckHIDAssignment(conn, hid, usrName);
}
catch (Exception)
{
errorCode = HttpStatusCode.BadRequest;
throw;
}
// Check: DocID
String checkString = "";
if (tmpdocid.HasValue)
{
checkString = HIHDBUtility.GetFinanceDocLoanListQueryString() + " WHERE [DOCID] = " + tmpdocid.Value.ToString() + " AND [HID] = " + hid.ToString();
cmd = new SqlCommand(checkString, conn);
reader = cmd.ExecuteReader();
if (!reader.HasRows)
{
errorCode = HttpStatusCode.BadRequest;
throw new Exception("Invalid Doc ID inputted: " + tmpdocid.Value.ToString());
}
else
{
while (reader.Read())
{
HIHDBUtility.FinTmpDocLoan_DB2VM(reader, vmTmpDoc);
// It shall be only one entry if found!
break;
}
}
reader.Dispose();
reader = null;
cmd.Dispose();
cmd = null;
}
// Check: Tmp doc has posted or not?
if (vmTmpDoc == null || (vmTmpDoc.RefDocID.HasValue && vmTmpDoc.RefDocID.Value > 0) ||
vmTmpDoc.AccountID != loanAccountID)
{
errorCode = HttpStatusCode.BadRequest;
throw new Exception("Tmp Doc not existed yet or has been posted");
}
// Check: Loan account
checkString = HIHDBUtility.GetFinanceLoanAccountQueryString(hid, loanAccountID);
cmd = new SqlCommand(checkString, conn);
reader = cmd.ExecuteReader();
if (!reader.HasRows)
{
errorCode = HttpStatusCode.BadRequest;
throw new Exception("Loan account read failed based on Doc ID inputted: " + tmpdocid.ToString());
}
else
{
if (reader.HasRows)
{
while (reader.Read())
{
HIHDBUtility.FinAccountHeader_DB2VM(reader, vmAccount, 0);
break;
}
}
reader.NextResult();
vmAccount.ExtraInfo_Loan = new FinanceAccountExtLoanViewModel();
if (reader.HasRows)
{
while (reader.Read())
{
HIHDBUtility.FinAccountLoan_DB2VM(reader, vmAccount.ExtraInfo_Loan, 0);
break;
}
}
reader.NextResult();
if (reader.HasRows)
{
while (reader.Read())
{
if (!reader.IsDBNull(0))
{
acntBalance = reader.GetDecimal(0);
}
break;
}
}
}
reader.Dispose();
reader = null;
cmd.Dispose();
cmd = null;
// Data validation - basic
try
{
await FinanceDocumentController.FinanceDocumentBasicValidationAsync(repaydoc, conn);
}
catch (Exception)
{
errorCode = HttpStatusCode.BadRequest;
throw;
}
// Data validation - loan specific
try
{
int ninvaliditems = 0;
// Only four tran. types are allowed
if (vmAccount.CtgyID == FinanceAccountCtgyViewModel.AccountCategory_BorrowFrom)
{
ninvaliditems = repaydoc.Items.Where(item => item.TranType != FinanceTranTypeViewModel.TranType_InterestOut &&
item.TranType != FinanceTranTypeViewModel.TranType_RepaymentOut &&
item.TranType != FinanceTranTypeViewModel.TranType_RepaymentIn)
.Count();
}
else if (vmAccount.CtgyID == FinanceAccountCtgyViewModel.AccountCategory_LendTo)
{
ninvaliditems = repaydoc.Items.Where(item => item.TranType != FinanceTranTypeViewModel.TranType_InterestIn &&
item.TranType != FinanceTranTypeViewModel.TranType_RepaymentOut &&
item.TranType != FinanceTranTypeViewModel.TranType_RepaymentIn)
.Count();
}
if (ninvaliditems > 0)
{
throw new Exception("Items with invalid tran type");
}
// Check the amount
decimal totalOut = repaydoc.Items.Where(item => item.TranType == FinanceTranTypeViewModel.TranType_RepaymentOut).Sum(item2 => item2.TranAmount);
decimal totalIn = repaydoc.Items.Where(item => item.TranType == FinanceTranTypeViewModel.TranType_RepaymentIn).Sum(item2 => item2.TranAmount);
//decimal totalintOut = repaydoc.Items.Where(item => (item.TranType == FinanceTranTypeViewModel.TranType_InterestOut)).Sum(item2 => item2.TranAmount);
// New account balance
if (vmAccount.CtgyID == FinanceAccountCtgyViewModel.AccountCategory_BorrowFrom)
{
acntBalance += totalOut;
}
else if (vmAccount.CtgyID == FinanceAccountCtgyViewModel.AccountCategory_LendTo)
{
acntBalance -= totalIn;
}
if (totalOut != totalIn)
{
throw new Exception("Amount is not equal!");
}
}
catch (Exception)
{
errorCode = HttpStatusCode.BadRequest;
throw;
}
// Now go ahead for the creating
tran = conn.BeginTransaction();
Int32 nNewDocID = 0;
// Now go ahead for creating
queryString = HIHDBUtility.GetFinDocHeaderInsertString();
// Header
cmd = new SqlCommand(queryString, conn)
{
Transaction = tran
};
HIHDBUtility.BindFinDocHeaderInsertParameter(cmd, repaydoc, usrName);
SqlParameter idparam = cmd.Parameters.AddWithValue("@Identity", SqlDbType.Int);
idparam.Direction = ParameterDirection.Output;
Int32 nRst = await cmd.ExecuteNonQueryAsync();
nNewDocID = (Int32)idparam.Value;
repaydoc.ID = nNewDocID;
cmd.Dispose();
cmd = null;
// Then, creating the items
foreach (FinanceDocumentItemUIViewModel ivm in repaydoc.Items)
{
queryString = HIHDBUtility.GetFinDocItemInsertString();
SqlCommand cmd2 = new SqlCommand(queryString, conn)
{
Transaction = tran
};
HIHDBUtility.BindFinDocItemInsertParameter(cmd2, ivm, nNewDocID);
await cmd2.ExecuteNonQueryAsync();
cmd2.Dispose();
cmd2 = null;
}
// Then, update the template doc
queryString = @"UPDATE [dbo].[t_fin_tmpdoc_loan]
SET [REFDOCID] = @REFDOCID
,[UPDATEDBY] = @UPDATEDBY
,[UPDATEDAT] = @UPDATEDAT
WHERE [HID] = @HID AND [DOCID] = @DOCID";
SqlCommand cmdTmpDoc = new SqlCommand(queryString, conn)
{
Transaction = tran
};
cmdTmpDoc.Parameters.AddWithValue("@REFDOCID", nNewDocID);
cmdTmpDoc.Parameters.AddWithValue("@UPDATEDBY", usrName);
cmdTmpDoc.Parameters.AddWithValue("@UPDATEDAT", DateTime.Now);
cmdTmpDoc.Parameters.AddWithValue("@HID", hid);
cmdTmpDoc.Parameters.AddWithValue("@DOCID", tmpdocid);
await cmdTmpDoc.ExecuteNonQueryAsync();
cmdTmpDoc.Dispose();
cmdTmpDoc = null;
// Incase balance is zero, update the account status
if (Decimal.Compare(acntBalance, 0) == 0)
{
queryString = HIHDBUtility.GetFinanceAccountStatusUpdateString();
SqlCommand cmdAccount = new SqlCommand(queryString, conn, tran);
HIHDBUtility.BindFinAccountStatusUpdateParameter(cmdAccount, FinanceAccountStatus.Closed, loanAccountID, hid, usrName);
await cmdAccount.ExecuteNonQueryAsync();
cmdAccount.Dispose();
cmdAccount = null;
}
tran.Commit();
// Update the buffer of the relevant Account!
// Account List
try
{
var cacheKey = String.Format(CacheKeys.FinAccountList, hid, null);
this._cache.Remove(cacheKey);
cacheKey = String.Format(CacheKeys.FinAccount, hid, loanAccountID);
this._cache.Remove(cacheKey);
}
catch (Exception)
{
// Do nothing here.
}
}
}
catch (Exception exp)
{
#if DEBUG
System.Diagnostics.Debug.WriteLine(exp.Message);
#endif
if (tran != null)
{
tran.Rollback();
}
strErrMsg = exp.Message;
if (errorCode == HttpStatusCode.OK)
{
errorCode = HttpStatusCode.InternalServerError;
}
}
finally
{
if (tran != null)
{
tran.Dispose();
tran = null;
}
if (reader != null)
{
reader.Dispose();
reader = null;
}
if (cmd != null)
{
cmd.Dispose();
cmd = null;
}
if (conn != null)
{
conn.Dispose();
conn = null;
}
}
if (errorCode != HttpStatusCode.OK)
{
switch (errorCode)
{
case HttpStatusCode.Unauthorized:
return(Unauthorized());
case HttpStatusCode.NotFound:
return(NotFound());
case HttpStatusCode.BadRequest:
return(BadRequest(strErrMsg));
default:
return(StatusCode(500, strErrMsg));
}
}
var setting = new Newtonsoft.Json.JsonSerializerSettings
{
DateFormatString = HIHAPIConstants.DateFormatPattern,
ContractResolver = new Newtonsoft.Json.Serialization.CamelCasePropertyNamesContractResolver()
};
return(new JsonResult(repaydoc, setting));
}
public async Task <IActionResult> Post([FromQuery] Int32 hid, Int32 docid)
{
// The post here is:
// 1. Post a normal document with the content from this template doc
// 2. Update the template doc with REFDOCID
// Basic check
if (hid <= 0 || docid <= 0)
{
return(BadRequest("No data inputted!"));
}
// Update the database
SqlConnection conn = null;
SqlCommand cmd = null;
SqlDataReader reader = null;
SqlTransaction tran = null;
String queryString = String.Empty;
String strErrMsg = String.Empty;
FinanceTmpDocDPViewModel vmTmpDoc = new FinanceTmpDocDPViewModel();
HomeDefViewModel vmHome = new HomeDefViewModel();
FinanceDocumentUIViewModel vmFIDOC = new FinanceDocumentUIViewModel();
HttpStatusCode errorCode = HttpStatusCode.OK;
String usrName = String.Empty;
if (Startup.UnitTestMode)
{
usrName = UnitTestUtility.UnitTestUser;
}
else
{
var usrObj = HIHAPIUtility.GetUserClaim(this);
usrName = usrObj.Value;
}
if (String.IsNullOrEmpty(usrName))
{
return(BadRequest("User cannot recognize"));
}
try
{
using (conn = new SqlConnection(Startup.DBConnectionString))
{
await conn.OpenAsync();
// Check: HID, it requires more info than just check, so it implemented it
if (hid != 0)
{
String strHIDCheck = HIHDBUtility.getHomeDefQueryString() + " WHERE [ID]= @hid AND [USER] = @user";
cmd = new SqlCommand(strHIDCheck, conn);
cmd.Parameters.AddWithValue("@hid", hid);
cmd.Parameters.AddWithValue("@user", usrName);
reader = await cmd.ExecuteReaderAsync();
if (!reader.HasRows)
{
errorCode = HttpStatusCode.BadRequest;
throw new Exception("Not home found!");
}
else
{
while (reader.Read())
{
HIHDBUtility.HomeDef_DB2VM(reader, vmHome);
// It shall be only one entry if found!
break;
}
}
reader.Dispose();
reader = null;
cmd.Dispose();
cmd = null;
}
if (vmHome == null || String.IsNullOrEmpty(vmHome.BaseCurrency) || vmHome.ID != hid)
{
errorCode = HttpStatusCode.BadRequest;
throw new Exception("Home Definition is invalid");
}
// Check: DocID
String checkString = HIHDBUtility.getFinanceDocADPListQueryString() + " WHERE [DOCID] = " + docid.ToString() + " AND [HID] = " + hid.ToString();
cmd = new SqlCommand(checkString, conn);
reader = cmd.ExecuteReader();
if (!reader.HasRows)
{
errorCode = HttpStatusCode.BadRequest;
throw new Exception("Invalid Doc ID inputted: " + docid.ToString());
}
else
{
while (reader.Read())
{
HIHDBUtility.FinTmpDocADP_DB2VM(reader, vmTmpDoc);
// It shall be only one entry if found!
break;
}
}
reader.Dispose();
reader = null;
cmd.Dispose();
cmd = null;
// Check: Tmp doc has posted or not?
if (vmTmpDoc == null || (vmTmpDoc.RefDocID.HasValue && vmTmpDoc.RefDocID.Value > 0))
{
errorCode = HttpStatusCode.BadRequest;
throw new Exception("Tmp Doc not existed yet or has been posted");
}
if (!vmTmpDoc.ControlCenterID.HasValue && !vmTmpDoc.OrderID.HasValue)
{
errorCode = HttpStatusCode.BadRequest;
throw new Exception("Tmp doc lack of control center or order");
}
if (vmTmpDoc.TranAmount == 0)
{
errorCode = HttpStatusCode.BadRequest;
throw new Exception("Tmp doc lack of amount");
}
// Now go ahead for the creating
tran = conn.BeginTransaction();
cmd = null;
Int32 nNewDocID = 0;
vmFIDOC.Desp = vmTmpDoc.Desp;
vmFIDOC.DocType = FinanceDocTypeViewModel.DocType_Normal;
vmFIDOC.HID = hid;
//vmFIDOC.TranAmount = vmTmpDoc.TranAmount;
vmFIDOC.TranCurr = vmHome.BaseCurrency;
vmFIDOC.TranDate = vmTmpDoc.TranDate;
vmFIDOC.CreatedAt = DateTime.Now;
FinanceDocumentItemUIViewModel vmItem = new FinanceDocumentItemUIViewModel
{
AccountID = vmTmpDoc.AccountID
};
if (vmTmpDoc.ControlCenterID.HasValue)
{
vmItem.ControlCenterID = vmTmpDoc.ControlCenterID.Value;
}
if (vmTmpDoc.OrderID.HasValue)
{
vmItem.OrderID = vmTmpDoc.OrderID.Value;
}
vmItem.Desp = vmTmpDoc.Desp;
vmItem.ItemID = 1;
vmItem.TranAmount = vmTmpDoc.TranAmount;
vmItem.TranType = vmTmpDoc.TranType;
vmFIDOC.Items.Add(vmItem);
// Now go ahead for the creating
queryString = HIHDBUtility.GetFinDocHeaderInsertString();
// Header
cmd = new SqlCommand(queryString, conn)
{
Transaction = tran
};
HIHDBUtility.BindFinDocHeaderInsertParameter(cmd, vmFIDOC, usrName);
SqlParameter idparam = cmd.Parameters.AddWithValue("@Identity", SqlDbType.Int);
idparam.Direction = ParameterDirection.Output;
Int32 nRst = await cmd.ExecuteNonQueryAsync();
nNewDocID = (Int32)idparam.Value;
vmFIDOC.ID = nNewDocID;
cmd.Dispose();
cmd = null;
// Then, creating the items
foreach (FinanceDocumentItemUIViewModel ivm in vmFIDOC.Items)
{
queryString = HIHDBUtility.GetFinDocItemInsertString();
cmd = new SqlCommand(queryString, conn)
{
Transaction = tran
};
HIHDBUtility.BindFinDocItemInsertParameter(cmd, ivm, nNewDocID);
await cmd.ExecuteNonQueryAsync();
cmd.Dispose();
cmd = null;
}
// Then, update the template doc
queryString = @"UPDATE [dbo].[t_fin_tmpdoc_dp]
SET [REFDOCID] = @REFDOCID
,[UPDATEDBY] = @UPDATEDBY
,[UPDATEDAT] = @UPDATEDAT
WHERE [HID] = @HID AND [DOCID] = @DOCID";
cmd = new SqlCommand(queryString, conn)
{
Transaction = tran
};
cmd.Parameters.AddWithValue("@REFDOCID", nNewDocID);
cmd.Parameters.AddWithValue("@UPDATEDBY", usrName);
cmd.Parameters.AddWithValue("@UPDATEDAT", DateTime.Now);
cmd.Parameters.AddWithValue("@HID", hid);
cmd.Parameters.AddWithValue("@DOCID", docid);
await cmd.ExecuteNonQueryAsync();
tran.Commit();
// Update the buffer of the relevant Account!
var cacheAccountKey = String.Format(CacheKeys.FinAccount, hid, vmTmpDoc.AccountID);
this._cache.Remove(cacheAccountKey);
}
}
catch (Exception exp)
{
#if DEBUG
System.Diagnostics.Debug.WriteLine(exp.Message);
#endif
if (tran != null)
{
tran.Rollback();
}
strErrMsg = exp.Message;
if (errorCode == HttpStatusCode.OK)
{
errorCode = HttpStatusCode.InternalServerError;
}
}
finally
{
if (tran != null)
{
tran.Dispose();
tran = null;
}
if (reader != null)
{
reader.Dispose();
reader = null;
}
if (cmd != null)
{
cmd.Dispose();
cmd = null;
}
if (conn != null)
{
conn.Dispose();
conn = null;
}
}
if (errorCode != HttpStatusCode.OK)
{
switch (errorCode)
{
case HttpStatusCode.Unauthorized:
return(Unauthorized());
case HttpStatusCode.NotFound:
return(NotFound());
case HttpStatusCode.BadRequest:
return(BadRequest(strErrMsg));
default:
return(StatusCode(500, strErrMsg));
}
}
var setting = new Newtonsoft.Json.JsonSerializerSettings
{
DateFormatString = HIHAPIConstants.DateFormatPattern,
ContractResolver = new Newtonsoft.Json.Serialization.CamelCasePropertyNamesContractResolver()
};
return(new JsonResult(vmFIDOC, setting));
}
public async Task <IActionResult> Post([FromBody] FinanceAssetSoldoutDocViewModel vm)
{
if (!ModelState.IsValid)
{
return(BadRequest(ModelState));
}
// Perform checks
if (vm.HID <= 0)
{
return(BadRequest("Not HID inputted"));
}
if (vm.AssetAccountID <= 0)
{
return(BadRequest("Asset Account is invalid"));
}
if (vm.TranAmount <= 0)
{
return(BadRequest("Amount is less than zero"));
}
if (vm.Items.Count <= 0)
{
return(BadRequest("No items inputted"));
}
String usrName = String.Empty;
if (Startup.UnitTestMode)
{
usrName = UnitTestUtility.UnitTestUser;
}
else
{
var usrObj = HIHAPIUtility.GetUserClaim(this);
usrName = usrObj.Value;
}
if (String.IsNullOrEmpty(usrName))
{
return(BadRequest("User cannot recognize"));
}
// Construct the Doc.
var vmFIDoc = new FinanceDocumentUIViewModel();
vmFIDoc.DocType = FinanceDocTypeViewModel.DocType_AssetSoldOut;
vmFIDoc.Desp = vm.Desp;
vmFIDoc.TranDate = vm.TranDate;
vmFIDoc.HID = vm.HID;
vmFIDoc.TranCurr = vm.TranCurr;
Decimal totalAmt = 0;
var maxItemID = 0;
foreach (var di in vm.Items)
{
if (di.ItemID <= 0 || di.TranAmount == 0 || di.AccountID <= 0 ||
di.TranType != FinanceTranTypeViewModel.TranType_AssetSoldoutIncome ||
(di.ControlCenterID <= 0 && di.OrderID <= 0))
{
return(BadRequest("Invalid input data in items!"));
}
totalAmt += di.TranAmount;
vmFIDoc.Items.Add(di);
if (maxItemID < di.ItemID)
{
maxItemID = di.ItemID;
}
}
if (Decimal.Compare(totalAmt, vm.TranAmount) != 0)
{
return(BadRequest("Amount is not even"));
}
var nitem = new FinanceDocumentItemUIViewModel();
nitem.ItemID = ++maxItemID;
nitem.AccountID = vm.AssetAccountID;
nitem.TranAmount = vm.TranAmount;
nitem.Desp = vmFIDoc.Desp;
nitem.TranType = FinanceTranTypeViewModel.TranType_AssetSoldout;
if (vm.ControlCenterID.HasValue)
{
nitem.ControlCenterID = vm.ControlCenterID.Value;
}
if (vm.OrderID.HasValue)
{
nitem.OrderID = vm.OrderID.Value;
}
vmFIDoc.Items.Add(nitem);
// Update the database
SqlConnection conn = null;
SqlCommand cmd = null;
SqlDataReader reader = null;
SqlTransaction tran = null;
String queryString = "";
Int32 nNewDocID = -1;
String strErrMsg = "";
Decimal dCurrBalance = 0;
HttpStatusCode errorCode = HttpStatusCode.OK;
try
{
// Basic check again
FinanceDocumentController.FinanceDocumentBasicCheck(vmFIDoc);
using (conn = new SqlConnection(Startup.DBConnectionString))
{
await conn.OpenAsync();
// Check Home assignment with current user
try
{
HIHAPIUtility.CheckHIDAssignment(conn, vm.HID, usrName);
}
catch (Exception)
{
errorCode = HttpStatusCode.BadRequest;
throw;
}
// Perfrom the doc. validation
await FinanceDocumentController.FinanceDocumentBasicValidationAsync(vmFIDoc, conn);
// Additional checks
SqlCommand cmdAddCheck = null;
SqlDataReader readerAddCheck = null;
try
{
// Check 1: check account is a valid asset?
String strsqls = @"SELECT
[t_fin_account].[STATUS]
,[t_fin_account_ext_as].[REFDOC_SOLD] AS [ASREFDOC_SOLD]
FROM [dbo].[t_fin_account]
INNER JOIN [dbo].[t_fin_account_ext_as]
ON [t_fin_account].[ID] = [t_fin_account_ext_as].[ACCOUNTID]
WHERE [t_fin_account].[ID] = " + vm.AssetAccountID.ToString()
+ " AND [t_fin_account].[HID] = " + vm.HID.ToString();
cmdAddCheck = new SqlCommand(strsqls, conn);
readerAddCheck = await cmdAddCheck.ExecuteReaderAsync();
if (readerAddCheck.HasRows)
{
while (readerAddCheck.Read())
{
if (!readerAddCheck.IsDBNull(0))
{
var acntStatus = (FinanceAccountStatus)readerAddCheck.GetByte(0);
if (acntStatus != FinanceAccountStatus.Normal)
{
throw new Exception("Account status is not normal");
}
}
else
{
// Status is NULL stands for Active Status
// throw new Exception("Account status is not normal");
}
if (!readerAddCheck.IsDBNull(1))
{
throw new Exception("Account has soldout doc already");
}
break;
}
}
readerAddCheck.Close();
readerAddCheck = null;
cmdAddCheck.Dispose();
cmdAddCheck = null;
// Check 2: check the inputted date is valid > must be the later than all existing transactions;
strsqls = @"SELECT MAX(t_fin_document.TRANDATE)
FROM [dbo].[t_fin_document_item]
INNER JOIN [dbo].[t_fin_document]
ON [dbo].[t_fin_document_item].[DOCID] = [dbo].[t_fin_document].[ID]
WHERE [dbo].[t_fin_document_item].[ACCOUNTID] = " + vm.AssetAccountID.ToString();
cmdAddCheck = new SqlCommand(strsqls, conn);
readerAddCheck = await cmdAddCheck.ExecuteReaderAsync();
if (readerAddCheck.HasRows)
{
while (readerAddCheck.Read())
{
var latestdate = readerAddCheck.GetDateTime(0);
if (vm.TranDate.Date < latestdate.Date)
{
throw new Exception("Invalid date");
}
break;
}
}
else
{
throw new Exception("Invalid account - no doc items");
}
readerAddCheck.Close();
readerAddCheck = null;
cmdAddCheck.Dispose();
cmdAddCheck = null;
// Check 3. Fetch current balance
strsqls = @"SELECT [balance]
FROM [dbo].[v_fin_report_bs] WHERE [accountid] = " + vm.AssetAccountID.ToString();
cmdAddCheck = new SqlCommand(strsqls, conn);
readerAddCheck = await cmdAddCheck.ExecuteReaderAsync();
if (readerAddCheck.HasRows)
{
while (readerAddCheck.Read())
{
dCurrBalance = readerAddCheck.GetDecimal(0);
if (dCurrBalance <= 0)
{
throw new Exception("Balance is zero");
}
break;
}
}
else
{
throw new Exception("Invalid account - no doc items");
}
readerAddCheck.Close();
readerAddCheck = null;
cmdAddCheck.Dispose();
cmdAddCheck = null;
var ncmprst = Decimal.Compare(dCurrBalance, vm.TranAmount);
if (ncmprst > 0)
{
var nitem2 = new FinanceDocumentItemUIViewModel();
nitem2.ItemID = ++maxItemID;
nitem2.AccountID = vm.AssetAccountID;
nitem2.TranAmount = Decimal.Subtract(dCurrBalance, vm.TranAmount);
nitem2.Desp = vmFIDoc.Desp;
nitem2.TranType = FinanceTranTypeViewModel.TranType_AssetValueDecrease;
if (vm.ControlCenterID.HasValue)
{
nitem2.ControlCenterID = vm.ControlCenterID.Value;
}
if (vm.OrderID.HasValue)
{
nitem2.OrderID = vm.OrderID.Value;
}
vmFIDoc.Items.Add(nitem2);
}
else if (ncmprst < 0)
{
var nitem2 = new FinanceDocumentItemUIViewModel();
nitem2.ItemID = ++maxItemID;
nitem2.AccountID = vm.AssetAccountID;
nitem2.TranAmount = Decimal.Subtract(vm.TranAmount, dCurrBalance);
nitem2.Desp = vmFIDoc.Desp;
nitem2.TranType = FinanceTranTypeViewModel.TranType_AssetValueIncrease;
if (vm.ControlCenterID.HasValue)
{
nitem2.ControlCenterID = vm.ControlCenterID.Value;
}
if (vm.OrderID.HasValue)
{
nitem2.OrderID = vm.OrderID.Value;
}
vmFIDoc.Items.Add(nitem2);
}
}
catch (Exception)
{
errorCode = HttpStatusCode.BadRequest;
throw;
}
finally
{
if (readerAddCheck != null)
{
readerAddCheck.Close();
readerAddCheck = null;
}
if (cmdAddCheck != null)
{
cmdAddCheck.Dispose();
cmdAddCheck = null;
}
}
// Begin the modification
tran = conn.BeginTransaction();
// First, craete the doc header => nNewDocID
queryString = HIHDBUtility.GetFinDocHeaderInsertString();
cmd = new SqlCommand(queryString, conn)
{
Transaction = tran
};
HIHDBUtility.BindFinDocHeaderInsertParameter(cmd, vmFIDoc, usrName);
SqlParameter idparam = cmd.Parameters.AddWithValue("@Identity", SqlDbType.Int);
idparam.Direction = ParameterDirection.Output;
Int32 nRst = await cmd.ExecuteNonQueryAsync();
nNewDocID = (Int32)idparam.Value;
cmd.Dispose();
cmd = null;
// Then, creating the items
foreach (FinanceDocumentItemUIViewModel ivm in vmFIDoc.Items)
{
queryString = HIHDBUtility.GetFinDocItemInsertString();
cmd = new SqlCommand(queryString, conn)
{
Transaction = tran
};
HIHDBUtility.BindFinDocItemInsertParameter(cmd, ivm, nNewDocID);
await cmd.ExecuteNonQueryAsync();
cmd.Dispose();
cmd = null;
// Tags
if (ivm.TagTerms.Count > 0)
{
// Create tags
foreach (var term in ivm.TagTerms)
{
queryString = HIHDBUtility.GetTagInsertString();
cmd = new SqlCommand(queryString, conn, tran);
HIHDBUtility.BindTagInsertParameter(cmd, vm.HID, HIHTagTypeEnum.FinanceDocumentItem, nNewDocID, term, ivm.ItemID);
await cmd.ExecuteNonQueryAsync();
cmd.Dispose();
cmd = null;
}
}
}
// Third, update the Account's status
queryString = HIHDBUtility.GetFinanceAccountStatusUpdateString();
cmd = new SqlCommand(queryString, conn)
{
Transaction = tran
};
// Close this account
HIHDBUtility.BindFinAccountStatusUpdateParameter(cmd, FinanceAccountStatus.Closed, vm.AssetAccountID, vm.HID, usrName);
nRst = await cmd.ExecuteNonQueryAsync();
cmd.Dispose();
cmd = null;
// Fourth, Update the Asset account part for sold doc
queryString = HIHDBUtility.GetFinanceAccountAssetUpdateSoldDocString();
cmd = new SqlCommand(queryString, conn)
{
Transaction = tran
};
HIHDBUtility.BindFinAccountAssetUpdateSoldDocParameter(cmd, nNewDocID, vm.AssetAccountID);
nRst = await cmd.ExecuteNonQueryAsync();
cmd.Dispose();
cmd = null;
// Do the commit
tran.Commit();
// Update the buffer
// Account List
try
{
var cacheKey = String.Format(CacheKeys.FinAccountList, vm.HID, null);
this._cache.Remove(cacheKey);
}
catch (Exception)
{
// Do nothing here.
}
// Account itself
try
{
var cacheKey = String.Format(CacheKeys.FinAccount, vm.HID, vm.AssetAccountID);
this._cache.Remove(cacheKey);
}
catch (Exception)
{
// Do nothing here.
}
}
}
catch (Exception exp)
{
#if DEBUG
System.Diagnostics.Debug.WriteLine(exp.Message);
#endif
strErrMsg = exp.Message;
if (errorCode == HttpStatusCode.OK)
{
errorCode = HttpStatusCode.InternalServerError;
}
if (tran != null)
{
tran.Rollback();
}
}
finally
{
if (tran != null)
{
tran.Dispose();
tran = null;
}
if (reader != null)
{
reader.Dispose();
reader = null;
}
if (cmd != null)
{
cmd.Dispose();
cmd = null;
}
if (conn != null)
{
conn.Dispose();
conn = null;
}
}
if (errorCode != HttpStatusCode.OK)
{
switch (errorCode)
{
case HttpStatusCode.Unauthorized:
return(Unauthorized());
case HttpStatusCode.NotFound:
return(NotFound());
case HttpStatusCode.BadRequest:
return(BadRequest(strErrMsg));
default:
return(StatusCode(500, strErrMsg));
}
}
// Return nothing
return(Ok(nNewDocID));
}
public async Task <IActionResult> Post([FromBody] FinanceLoanDocumentUIViewModel vm)
{
if (!ModelState.IsValid)
{
return(BadRequest(ModelState));
}
if (vm == null || (vm.DocType != FinanceDocTypeViewModel.DocType_BorrowFrom &&
vm.DocType != FinanceDocTypeViewModel.DocType_LendTo))
{
return(BadRequest("No data is inputted"));
}
if (vm.HID <= 0)
{
return(BadRequest("Not HID inputted"));
}
String usrName = String.Empty;
if (Startup.UnitTestMode)
{
usrName = UnitTestUtility.UnitTestUser;
}
else
{
var usrObj = HIHAPIUtility.GetUserClaim(this);
usrName = usrObj.Value;
}
if (String.IsNullOrEmpty(usrName))
{
return(BadRequest("User cannot recognize"));
}
// Check the items
if (vm.Items.Count != 1)
{
return(BadRequest("Only one item doc is supported by far"));
}
if (vm.AccountVM == null || vm.AccountVM.ExtraInfo_Loan == null)
{
return(BadRequest("No account info!"));
}
if (vm.AccountVM.ExtraInfo_Loan.LoanTmpDocs.Count <= 0)
{
return(BadRequest("No template docs defined!"));
}
else
{
foreach (var tdoc in vm.AccountVM.ExtraInfo_Loan.LoanTmpDocs)
{
if (!tdoc.ControlCenterID.HasValue && !tdoc.OrderID.HasValue)
{
return(BadRequest("Either control center or order shall be specified in Loan Template doc"));
}
if (tdoc.TranAmount <= 0)
{
return(BadRequest("Amount is zero!"));
}
}
}
// Update the database
SqlConnection conn = null;
SqlCommand cmd = null;
SqlDataReader reader = null;
SqlTransaction tran = null;
String queryString = "";
Int32 nNewDocID = -1;
String strErrMsg = "";
HttpStatusCode errorCode = HttpStatusCode.OK;
try
{
using (conn = new SqlConnection(Startup.DBConnectionString))
{
await conn.OpenAsync();
// Check Home assignment with current user
try
{
HIHAPIUtility.CheckHIDAssignment(conn, vm.HID, usrName);
}
catch (Exception)
{
errorCode = HttpStatusCode.BadRequest;
throw;
}
tran = conn.BeginTransaction();
// First, create the doc header => nNewDocID
queryString = HIHDBUtility.GetFinDocHeaderInsertString();
cmd = new SqlCommand(queryString, conn)
{
Transaction = tran
};
HIHDBUtility.BindFinDocHeaderInsertParameter(cmd, vm, usrName);
SqlParameter idparam = cmd.Parameters.AddWithValue("@Identity", SqlDbType.Int);
idparam.Direction = ParameterDirection.Output;
Int32 nRst = await cmd.ExecuteNonQueryAsync();
nNewDocID = (Int32)idparam.Value;
cmd.Dispose();
cmd = null;
// Then, creating the items
foreach (FinanceDocumentItemUIViewModel ivm in vm.Items)
{
if (vm.DocType == FinanceDocTypeViewModel.DocType_BorrowFrom)
{
ivm.TranType = FinanceTranTypeViewModel.TranType_BorrowFrom;
}
else if (vm.DocType == FinanceDocTypeViewModel.DocType_LendTo)
{
ivm.TranType = FinanceTranTypeViewModel.TranType_LendTo;
}
queryString = HIHDBUtility.GetFinDocItemInsertString();
cmd = new SqlCommand(queryString, conn)
{
Transaction = tran
};
HIHDBUtility.BindFinDocItemInsertParameter(cmd, ivm, nNewDocID);
await cmd.ExecuteNonQueryAsync();
cmd.Dispose();
cmd = null;
// Tags
if (ivm.TagTerms.Count > 0)
{
// Create tags
foreach (var term in ivm.TagTerms)
{
queryString = HIHDBUtility.GetTagInsertString();
cmd = new SqlCommand(queryString, conn, tran);
HIHDBUtility.BindTagInsertParameter(cmd, vm.HID, HIHTagTypeEnum.FinanceDocumentItem, nNewDocID, term, ivm.ItemID);
await cmd.ExecuteNonQueryAsync();
cmd.Dispose();
cmd = null;
}
}
}
// Third, go to the account creation => nNewAccountID
queryString = HIHDBUtility.GetFinanceAccountHeaderInsertString();
cmd = new SqlCommand(queryString, conn)
{
Transaction = tran
};
HIHDBUtility.BindFinAccountInsertParameter(cmd, vm.AccountVM, usrName);
SqlParameter idparam2 = cmd.Parameters.AddWithValue("@Identity", SqlDbType.Int);
idparam2.Direction = ParameterDirection.Output;
nRst = await cmd.ExecuteNonQueryAsync();
Int32 nNewAccountID = (Int32)idparam2.Value;
cmd.Dispose();
cmd = null;
// 3a. Create another item to loan document
var nMaxItemID = vm.Items.Max(item => item.ItemID);
foreach (FinanceDocumentItemUIViewModel ivm in vm.Items)
{
ivm.ItemID = ++nMaxItemID;
ivm.AccountID = nNewAccountID;
if (vm.DocType == FinanceDocTypeViewModel.DocType_BorrowFrom)
{
ivm.TranType = FinanceTranTypeViewModel.TranType_OpeningLiability;
}
else if (vm.DocType == FinanceDocTypeViewModel.DocType_LendTo)
{
ivm.TranType = FinanceTranTypeViewModel.TranType_OpeningAsset;
}
queryString = HIHDBUtility.GetFinDocItemInsertString();
SqlCommand cmd2 = new SqlCommand(queryString, conn)
{
Transaction = tran
};
HIHDBUtility.BindFinDocItemInsertParameter(cmd2, ivm, nNewDocID);
await cmd2.ExecuteNonQueryAsync();
cmd2.Dispose();
cmd2 = null;
}
// Fourth, creat the Loan part
queryString = HIHDBUtility.GetFinanceAccountLoanInsertString();
cmd = new SqlCommand(queryString, conn)
{
Transaction = tran
};
HIHDBUtility.BindFinAccountLoanInsertParameter(cmd, vm.AccountVM.ExtraInfo_Loan, nNewDocID, nNewAccountID, usrName);
nRst = await cmd.ExecuteNonQueryAsync();
cmd.Dispose();
cmd = null;
// Fifth, create template docs
foreach (FinanceTmpDocLoanViewModel avm in vm.AccountVM.ExtraInfo_Loan.LoanTmpDocs)
{
queryString = HIHDBUtility.GetFinanceTmpDocLoanInsertString();
cmd = new SqlCommand(queryString, conn)
{
Transaction = tran
};
HIHDBUtility.BindFinTmpDocLoanParameter(cmd, avm, nNewAccountID, usrName);
await cmd.ExecuteNonQueryAsync();
cmd.Dispose();
cmd = null;
}
tran.Commit();
// Update the buffer
// Account list
var cacheKey = String.Format(CacheKeys.FinAccountList, vm.HID, null);
this._cache.Remove(cacheKey);
}
}
catch (Exception exp)
{
#if DEBUG
System.Diagnostics.Debug.WriteLine(exp.Message);
#endif
if (tran != null)
{
tran.Rollback();
}
strErrMsg = exp.Message;
if (errorCode == HttpStatusCode.OK)
{
errorCode = HttpStatusCode.InternalServerError;
}
}
finally
{
if (tran != null)
{
tran.Dispose();
tran = null;
}
if (reader != null)
{
reader.Dispose();
reader = null;
}
if (cmd != null)
{
cmd.Dispose();
cmd = null;
}
if (conn != null)
{
conn.Dispose();
conn = null;
}
}
if (errorCode != HttpStatusCode.OK)
{
switch (errorCode)
{
case HttpStatusCode.Unauthorized:
return(Unauthorized());
case HttpStatusCode.NotFound:
return(NotFound());
case HttpStatusCode.BadRequest:
return(BadRequest(strErrMsg));
default:
return(StatusCode(500, strErrMsg));
}
}
vm.ID = nNewDocID;
var setting = new Newtonsoft.Json.JsonSerializerSettings
{
DateFormatString = HIHAPIConstants.DateFormatPattern,
ContractResolver = new Newtonsoft.Json.Serialization.CamelCasePropertyNamesContractResolver()
};
return(new JsonResult(vm, setting));
}
public async Task <IActionResult> Post([FromBody] FinanceAssetBuyinDocViewModel vm)
{
if (!ModelState.IsValid)
{
return(BadRequest(ModelState));
}
if (vm == null || vm.accountAsset == null)
{
return(BadRequest("No data is inputted"));
}
if (vm.HID <= 0)
{
return(BadRequest("Not HID inputted"));
}
// Do basic checks
if (String.IsNullOrEmpty(vm.TranCurr) || String.IsNullOrEmpty(vm.accountAsset.Name) ||
(vm.IsLegacy.HasValue && vm.IsLegacy.Value && vm.Items.Count > 0) ||
((!vm.IsLegacy.HasValue || (vm.IsLegacy.HasValue && !vm.IsLegacy.Value)) && vm.Items.Count <= 0)
)
{
return(BadRequest("Invalid input data"));
}
foreach (var di in vm.Items)
{
if (di.TranAmount == 0 || di.AccountID <= 0 || di.TranType <= 0 || (di.ControlCenterID <= 0 && di.OrderID <= 0))
{
return(BadRequest("Invalid input data in items!"));
}
}
String usrName = String.Empty;
if (Startup.UnitTestMode)
{
usrName = UnitTestUtility.UnitTestUser;
}
else
{
var usrObj = HIHAPIUtility.GetUserClaim(this);
usrName = usrObj.Value;
}
if (String.IsNullOrEmpty(usrName))
{
return(BadRequest("User cannot recognize"));
}
// Construct the Account
var vmAccount = new FinanceAccountViewModel();
vmAccount.HID = vm.HID;
vmAccount.Name = vm.accountAsset.Name;
vmAccount.Status = FinanceAccountStatus.Normal;
vmAccount.CtgyID = FinanceAccountCtgyViewModel.AccountCategory_Asset;
vmAccount.ExtraInfo_AS = new FinanceAccountExtASViewModel();
vmAccount.Owner = vm.AccountOwner;
vmAccount.Comment = vm.accountAsset.Name;
vmAccount.ExtraInfo_AS.Name = vm.accountAsset.Name;
vmAccount.ExtraInfo_AS.Comment = vm.accountAsset.Comment;
vmAccount.ExtraInfo_AS.CategoryID = vm.accountAsset.CategoryID;
// Construct the Doc.
var vmFIDoc = new FinanceDocumentUIViewModel();
vmFIDoc.DocType = FinanceDocTypeViewModel.DocType_AssetBuyIn;
vmFIDoc.Desp = vm.Desp;
vmFIDoc.TranDate = vm.TranDate;
vmFIDoc.HID = vm.HID;
vmFIDoc.TranCurr = vm.TranCurr;
var maxItemID = 0;
if (vm.IsLegacy.HasValue && vm.IsLegacy.Value)
{
// Legacy account...
}
else
{
Decimal totalAmt = 0;
foreach (var di in vm.Items)
{
if (di.ItemID <= 0 || di.TranAmount == 0 || di.AccountID <= 0 ||
(di.ControlCenterID <= 0 && di.OrderID <= 0))
{
return(BadRequest("Invalid input data in items!"));
}
// Todo: new check the tran. type is an expense!
totalAmt += di.TranAmount;
vmFIDoc.Items.Add(di);
if (maxItemID < di.ItemID)
{
maxItemID = di.ItemID;
}
}
if (totalAmt != vm.TranAmount)
{
return(BadRequest("Amount is not even"));
}
}
var nitem = new FinanceDocumentItemUIViewModel();
nitem.ItemID = ++maxItemID;
nitem.AccountID = -1;
nitem.TranAmount = vm.TranAmount;
nitem.Desp = vmFIDoc.Desp;
nitem.TranType = FinanceTranTypeViewModel.TranType_OpeningAsset;
if (vm.ControlCenterID.HasValue)
{
nitem.ControlCenterID = vm.ControlCenterID.Value;
}
if (vm.OrderID.HasValue)
{
nitem.OrderID = vm.OrderID.Value;
}
vmFIDoc.Items.Add(nitem);
// Update the database
SqlConnection conn = null;
SqlCommand cmd = null;
SqlDataReader reader = null;
SqlTransaction tran = null;
String queryString = "";
Int32 nNewDocID = -1;
String strErrMsg = "";
HttpStatusCode errorCode = HttpStatusCode.OK;
try
{
// Basic check again - document level
FinanceDocumentController.FinanceDocumentBasicCheck(vmFIDoc);
using (conn = new SqlConnection(Startup.DBConnectionString))
{
await conn.OpenAsync();
// Check Home assignment with current user
try
{
HIHAPIUtility.CheckHIDAssignment(conn, vm.HID, usrName);
}
catch (Exception)
{
errorCode = HttpStatusCode.BadRequest;
throw;
}
// Perfrom the doc. validation
await FinanceDocumentController.FinanceDocumentBasicValidationAsync(vmFIDoc, conn, -1);
// 0) Start the trasnaction for modifications
tran = conn.BeginTransaction();
// 1) craete the doc header => nNewDocID
queryString = HIHDBUtility.GetFinDocHeaderInsertString();
cmd = new SqlCommand(queryString, conn)
{
Transaction = tran
};
HIHDBUtility.BindFinDocHeaderInsertParameter(cmd, vmFIDoc, usrName);
SqlParameter idparam = cmd.Parameters.AddWithValue("@Identity", SqlDbType.Int);
idparam.Direction = ParameterDirection.Output;
Int32 nRst = await cmd.ExecuteNonQueryAsync();
nNewDocID = (Int32)idparam.Value;
vmFIDoc.ID = nNewDocID;
cmd.Dispose();
cmd = null;
// 2), create the new account => nNewAccountID
queryString = HIHDBUtility.GetFinanceAccountHeaderInsertString();
cmd = new SqlCommand(queryString, conn)
{
Transaction = tran
};
HIHDBUtility.BindFinAccountInsertParameter(cmd, vmAccount, usrName);
SqlParameter idparam2 = cmd.Parameters.AddWithValue("@Identity", SqlDbType.Int);
idparam2.Direction = ParameterDirection.Output;
nRst = await cmd.ExecuteNonQueryAsync();
vmAccount.ID = (Int32)idparam2.Value;
cmd.Dispose();
cmd = null;
// 3) create the Asset part of account
vmAccount.ExtraInfo_AS.AccountID = vmAccount.ID;
vmAccount.ExtraInfo_AS.RefDocForBuy = nNewDocID;
queryString = HIHDBUtility.GetFinanceAccountAssetInsertString();
cmd = new SqlCommand(queryString, conn)
{
Transaction = tran
};
HIHDBUtility.BindFinAccountAssetInsertParameter(cmd, vmAccount.ExtraInfo_AS);
nRst = await cmd.ExecuteNonQueryAsync();
cmd.Dispose();
cmd = null;
// 4) create the doc items
foreach (FinanceDocumentItemUIViewModel ivm in vmFIDoc.Items)
{
if (ivm.AccountID == -1)
{
ivm.AccountID = vmAccount.ID;
}
queryString = HIHDBUtility.GetFinDocItemInsertString();
cmd = new SqlCommand(queryString, conn)
{
Transaction = tran
};
HIHDBUtility.BindFinDocItemInsertParameter(cmd, ivm, nNewDocID);
await cmd.ExecuteNonQueryAsync();
cmd.Dispose();
cmd = null;
// Tags
if (ivm.TagTerms.Count > 0)
{
// Create tags
foreach (var term in ivm.TagTerms)
{
queryString = HIHDBUtility.GetTagInsertString();
cmd = new SqlCommand(queryString, conn, tran);
HIHDBUtility.BindTagInsertParameter(cmd, vm.HID, HIHTagTypeEnum.FinanceDocumentItem, nNewDocID, term, ivm.ItemID);
await cmd.ExecuteNonQueryAsync();
cmd.Dispose();
cmd = null;
}
}
}
// 5) Do the commit
tran.Commit();
// Update the buffer
// Account List
try
{
var cacheKey = String.Format(CacheKeys.FinAccountList, vm.HID, null);
this._cache.Remove(cacheKey);
}
catch (Exception)
{
// Do nothing here.
}
// B.S.
try
{
var cacheKey = String.Format(CacheKeys.FinReportBS, vm.HID);
this._cache.Remove(cacheKey);
}
catch (Exception)
{
// Do nothing here.
}
}
}
catch (Exception exp)
{
#if DEBUG
System.Diagnostics.Debug.WriteLine(exp.Message);
#endif
strErrMsg = exp.Message;
if (errorCode == HttpStatusCode.OK)
{
errorCode = HttpStatusCode.InternalServerError;
}
if (tran != null)
{
tran.Rollback();
}
}
finally
{
if (tran != null)
{
tran.Dispose();
tran = null;
}
if (reader != null)
{
reader.Dispose();
reader = null;
}
if (cmd != null)
{
cmd.Dispose();
cmd = null;
}
if (conn != null)
{
conn.Dispose();
conn = null;
}
}
if (errorCode != HttpStatusCode.OK)
{
switch (errorCode)
{
case HttpStatusCode.Unauthorized:
return(Unauthorized());
case HttpStatusCode.NotFound:
return(NotFound());
case HttpStatusCode.BadRequest:
return(BadRequest(strErrMsg));
default:
return(StatusCode(500, strErrMsg));
}
}
return(Ok(nNewDocID));
}