public static long Add(DateTime valueDate, int storeId, bool isCredit, string partyCode, int agentId,
int priceTypeId, Collection <StockMasterDetailModel> details, int shipperId, string shippingAddressCode,
decimal shippingCharge, int cashRepositoryId, int costCenterId, string referenceNumber,
string statementReference, Collection <AttachmentModel> attachments)
{
StockMasterModel stockMaster = new StockMasterModel();
stockMaster.PartyCode = partyCode;
stockMaster.IsCredit = isCredit;
stockMaster.PriceTypeId = priceTypeId;
stockMaster.ShipperId = shipperId;
stockMaster.ShippingAddressCode = shippingAddressCode;
stockMaster.ShippingCharge = shippingCharge;
stockMaster.SalespersonId = agentId;
stockMaster.CashRepositoryId = cashRepositoryId;
stockMaster.StoreId = storeId;
long transactionMasterId = Add(valueDate, SessionHelper.GetOfficeId(), SessionHelper.GetUserId(),
SessionHelper.GetLogOnId(), costCenterId, referenceNumber, statementReference, stockMaster, details,
attachments);
MixERP.Net.TransactionGovernor.Autoverification.Autoverify.PassTransactionMasterId(transactionMasterId);
return(transactionMasterId);
}
public HttpResponseMessage StockSummary(StockMasterModel model)
{
ResponseStatus response = new ResponseStatus();
try
{
var data = _repository.StockSummary(model);
if (data.Count() > 0)
{
response.isSuccess = true;
response.serverResponseTime = System.DateTime.Now;
return(Request.CreateResponse(HttpStatusCode.OK, new { data, response }));
}
else
{
response.isSuccess = false;
response.serverResponseTime = System.DateTime.Now;
return(Request.CreateResponse(HttpStatusCode.BadRequest, new { response }));
}
}
catch (Exception ex)
{
return(Request.CreateErrorResponse(HttpStatusCode.BadRequest, "Something Worng !", ex));
}
}
public static long Add(string book, DateTime valueDate, string partyCode, int priceTypeId, Collection <StockMasterDetailModel> details, string referenceNumber, string statementReference, Collection <int> transactionIdCollection, Collection <AttachmentModel> attachments)
{
StockMasterModel stockMaster = new StockMasterModel();
stockMaster.PartyCode = partyCode;
stockMaster.PriceTypeId = priceTypeId;
long nonGlStockMasterId = NonGlStockTransaction.Add(book, valueDate, SessionHelper.GetOfficeId(), SessionHelper.GetUserId(), SessionHelper.GetLogOnId(), referenceNumber, statementReference, stockMaster, details, transactionIdCollection, attachments);
return(nonGlStockMasterId);
}
public IEnumerable <StockSummaryMasterModel> StockSummary(StockMasterModel model)
{
List <StockSummaryMasterModel> data = new List <StockSummaryMasterModel>();
MultipleDataBase dataAccess = new MultipleDataBase();
using (mom_androidEntities context = new mom_androidEntities())
{
var user = context.userdatabasemasters.Where(u => u.DataBaseId == model.DataBaseId).FirstOrDefault();
data = dataAccess.StockSummary(model, user.DataBase).ToList();
}
return(data);
}
public static long Add(DateTime valueDate, int storeId, string partyCode, int priceTypeId, Collection <StockMasterDetailModel> details, int shipperId, decimal shippingCharge, int costCenterId, string referenceNumber, int agentId, string statementReference, Collection <int> transactionIdCollection)
{
StockMasterModel stockMaster = new StockMasterModel();
stockMaster.PartyCode = partyCode;
stockMaster.StoreId = storeId;
stockMaster.PriceTypeId = priceTypeId;
stockMaster.ShipperId = shipperId;
stockMaster.ShippingCharge = shippingCharge;
stockMaster.AgentId = agentId;
long transactionMasterId = DatabaseLayer.Transactions.SalesDelivery.Add(valueDate, SessionHelper.GetOfficeId(), SessionHelper.GetUserId(), SessionHelper.GetLogOnId(), costCenterId, referenceNumber, statementReference, stockMaster, details, transactionIdCollection);
DatabaseLayer.Transactions.Verification.CallAutoVerification(transactionMasterId);
return(transactionMasterId);
}
public static long Add(DateTime valueDate, int storeId, string partyCode, Collection <StockMasterDetailModel> details, int costCenterId, string referenceNumber, string statementReference, Collection <int> transactionIdCollection, Collection <AttachmentModel> attachments)
{
StockMasterModel stockMaster = new StockMasterModel();
stockMaster.PartyCode = partyCode;
stockMaster.StoreId = storeId;
stockMaster.IsCredit = true;
if (!string.IsNullOrWhiteSpace(statementReference))
{
statementReference = statementReference.Replace(" ", " ").Trim();
}
long transactionMasterId = Add(valueDate, SessionHelper.GetOfficeId(), SessionHelper.GetUserId(), SessionHelper.GetLogOnId(), costCenterId, referenceNumber, statementReference, stockMaster, details, transactionIdCollection, attachments);
MixERP.Net.TransactionGovernor.Autoverification.Autoverify.PassTransactionMasterId(transactionMasterId);
return(transactionMasterId);
}
public static long Add(DateTime valueDate, int storeId, bool isCredit, string partyCode, int agentId, int priceTypeId, Collection <StockMasterDetailModel> details, int shipperId, string shippingAddressCode, decimal shippingCharge, int cashRepositoryId, int costCenterId, string referenceNumber, string statementReference)
{
StockMasterModel stockMaster = new StockMasterModel();
stockMaster.PartyCode = partyCode;
stockMaster.IsCredit = isCredit;
stockMaster.PriceTypeId = priceTypeId;
stockMaster.ShipperId = shipperId;
stockMaster.ShippingAddressCode = shippingAddressCode;
stockMaster.ShippingCharge = shippingCharge;
stockMaster.AgentId = agentId;
stockMaster.CashRepositoryId = cashRepositoryId;
stockMaster.StoreId = storeId;
long transactionMasterId = DatabaseLayer.Transactions.DirectSales.Add(valueDate, SessionHelper.GetOfficeId(), SessionHelper.GetUserId(), SessionHelper.GetLogOnId(), costCenterId, referenceNumber, statementReference, stockMaster, details);
DatabaseLayer.Transactions.Verification.CallAutoVerification(transactionMasterId);
return(transactionMasterId);
}
public static long Add(DateTime valueDate, int storeId, bool isCredit, string partyCode, Collection <StockMasterDetailModel> details, int cashRepositoryId, int costCenterId, string referenceNumber, string statementReference)
{
StockMasterModel stockMaster = new StockMasterModel();
stockMaster.PartyCode = partyCode;
stockMaster.StoreId = storeId;
stockMaster.CashRepositoryId = cashRepositoryId;
stockMaster.IsCredit = isCredit;
if (!string.IsNullOrWhiteSpace(statementReference))
{
statementReference = statementReference.Replace(" ", " ").Trim();
}
long transactionMasterId = DatabaseLayer.Transactions.DirectPurchase.Add(valueDate, SessionHelper.GetOfficeId(), SessionHelper.GetUserId(), SessionHelper.GetLogOnId(), costCenterId, referenceNumber, statementReference, stockMaster, details);
DatabaseLayer.Transactions.Verification.CallAutoVerification(transactionMasterId);
return(transactionMasterId);
}
public StockViewModel()
{
var tempDate = DateTime.Now;
stockMasterModel = new StockMasterModel();
itemMasterManger = new ItemMasterManager();
searchItemList = new ObservableCollection <ItemMasterModel>();
itemGroupList = new ObservableCollection <ItemGroupModel>();
selectedItemList = new ObservableCollection <ItemMasterModel>();
this.StockDate = tempDate;
this.StockOrderNo = "-";
this.StockOrderDate = tempDate;
this.StockBillNo = "-";
this.StockBillDate = tempDate;
this.StockSupplier = "-";
this.StockSuppliedOn = tempDate;
searchItemCmd = new RelayCommand(SearchItem, CanSearchItem);
addItemsCmd = new RelayCommand(AddSelectedItems, CanAddSelectedItems);
openAddItemsCmd = new RelayCommand(OpenAddItems, CanOpenAddItems);
this.ItemGroupList = itemMasterManger.GetIemGroupList();
}
private static long Add(DateTime valueDate, int officeId, int userId, long logOnId, int costCenterId, string referenceNumber, string statementReference, StockMasterModel stockMaster, Collection <StockMasterDetailModel> details, Collection <AttachmentModel> attachments)
{
if (stockMaster == null)
{
return(0);
}
if (details == null)
{
return(0);
}
if (details.Count.Equals(0))
{
return(0);
}
decimal total = details.Sum(d => (d.Price * d.Quantity));
decimal discountTotal = details.Sum(d => d.Discount);
decimal taxTotal = details.Sum(d => d.Tax);
const string creditInvariantParameter = "Purchase.Payables";
const string purchaseInvariantParameter = "Purchase";
const string purchaseTaxInvariantParamter = "Purchase.Tax";
const string purchaseDiscountInvariantParameter = "Purchase.Discount";
using (NpgsqlConnection connection = new NpgsqlConnection(DbConnection.ConnectionString()))
{
connection.Open();
using (NpgsqlTransaction transaction = connection.BeginTransaction())
{
try
{
#region TransactionMaster
string sql = "INSERT INTO transactions.transaction_master(transaction_master_id, transaction_counter, transaction_code, book, value_date, user_id, login_id, office_id, cost_center_id, reference_number, statement_reference) SELECT nextval(pg_get_serial_sequence('transactions.transaction_master', 'transaction_master_id')), transactions.get_new_transaction_counter(@ValueDate), transactions.get_transaction_code(@ValueDate, @OfficeId, @UserId, @LogOnId), @Book, @ValueDate, @UserId, @LogOnId, @OfficeId, @CostCenterId, @ReferenceNumber, @StatementReference;SELECT currval(pg_get_serial_sequence('transactions.transaction_master', 'transaction_master_id'));";
long transactionMasterId;
using (NpgsqlCommand tm = new NpgsqlCommand(sql, connection))
{
tm.Parameters.AddWithValue("@ValueDate", valueDate);
tm.Parameters.AddWithValue("@OfficeId", officeId);
tm.Parameters.AddWithValue("@UserId", userId);
tm.Parameters.AddWithValue("@LogOnId", logOnId);
tm.Parameters.AddWithValue("@Book", "Purchase.Direct");
tm.Parameters.AddWithValue("@CostCenterId", costCenterId);
tm.Parameters.AddWithValue("@ReferenceNumber", referenceNumber);
tm.Parameters.AddWithValue("@StatementReference", statementReference);
transactionMasterId = Conversion.TryCastLong(tm.ExecuteScalar());
}
#region TransactionDetails
sql = "INSERT INTO transactions.transaction_details(transaction_master_id, tran_type, account_id, statement_reference, cash_repository_id, currency_code, amount_in_currency, local_currency_code, er, amount_in_local_currency) " +
"SELECT @TransactionMasterId, @TranType, core.get_account_id_by_parameter(@ParameterName), @StatementReference, @CashRepositoryId, transactions.get_default_currency_code_by_office_id(@OfficeId), @Amount, transactions.get_default_currency_code_by_office_id(@OfficeId), 1, @Amount;";
using (NpgsqlCommand purchaseRow = new NpgsqlCommand(sql, connection))
{
purchaseRow.Parameters.AddWithValue("@TransactionMasterId", transactionMasterId);
purchaseRow.Parameters.AddWithValue("@TranType", "Dr");
purchaseRow.Parameters.AddWithValue("@ParameterName", purchaseInvariantParameter);
purchaseRow.Parameters.AddWithValue("@StatementReference", statementReference);
purchaseRow.Parameters.AddWithValue("@CashRepositoryId", DBNull.Value);
purchaseRow.Parameters.AddWithValue("@OfficeId", officeId);
purchaseRow.Parameters.AddWithValue("@Amount", total);
purchaseRow.ExecuteNonQuery();
}
if (taxTotal > 0)
{
using (NpgsqlCommand taxRow = new NpgsqlCommand(sql, connection))
{
taxRow.Parameters.AddWithValue("@TransactionMasterId", transactionMasterId);
taxRow.Parameters.AddWithValue("@TranType", "Dr");
taxRow.Parameters.AddWithValue("@ParameterName", purchaseTaxInvariantParamter);
taxRow.Parameters.AddWithValue("@StatementReference", statementReference);
taxRow.Parameters.AddWithValue("@CashRepositoryId", DBNull.Value);
taxRow.Parameters.AddWithValue("@OfficeId", officeId);
taxRow.Parameters.AddWithValue("@Amount", taxTotal);
taxRow.ExecuteNonQuery();
}
}
if (discountTotal > 0)
{
using (NpgsqlCommand discountRow = new NpgsqlCommand(sql, connection))
{
discountRow.Parameters.AddWithValue("@TransactionMasterId", transactionMasterId);
discountRow.Parameters.AddWithValue("@TranType", "Cr");
discountRow.Parameters.AddWithValue("@ParameterName", purchaseDiscountInvariantParameter);
discountRow.Parameters.AddWithValue("@StatementReference", statementReference);
discountRow.Parameters.AddWithValue("@CashRepositoryId", DBNull.Value);
discountRow.Parameters.AddWithValue("@OfficeId", officeId);
discountRow.Parameters.AddWithValue("@Amount", discountTotal);
discountRow.ExecuteNonQuery();
}
}
if (stockMaster.IsCredit)
{
using (NpgsqlCommand creditRow = new NpgsqlCommand(sql, connection))
{
creditRow.Parameters.AddWithValue("@TransactionMasterId", transactionMasterId);
creditRow.Parameters.AddWithValue("@TranType", "Cr");
creditRow.Parameters.AddWithValue("@ParameterName", creditInvariantParameter);
creditRow.Parameters.AddWithValue("@StatementReference", statementReference);
creditRow.Parameters.AddWithValue("@CashRepositoryId", DBNull.Value);
creditRow.Parameters.AddWithValue("@OfficeId", officeId);
creditRow.Parameters.AddWithValue("@Amount", total - discountTotal + taxTotal);
creditRow.ExecuteNonQuery();
}
}
else
{
sql = "INSERT INTO transactions.transaction_details(transaction_master_id, tran_type, account_id, statement_reference, cash_repository_id, currency_code, amount_in_currency, local_currency_code, er, amount_in_local_currency) " +
"SELECT @TransactionMasterId, @TranType, core.get_cash_account_id(), @StatementReference, @CashRepositoryId, transactions.get_default_currency_code_by_office_id(@OfficeId), @Amount, transactions.get_default_currency_code_by_office_id(@OfficeId), 1, @Amount;";
using (NpgsqlCommand cashRow = new NpgsqlCommand(sql, connection))
{
cashRow.Parameters.AddWithValue("@TransactionMasterId", transactionMasterId);
cashRow.Parameters.AddWithValue("@TranType", "Cr");
cashRow.Parameters.AddWithValue("@StatementReference", statementReference);
cashRow.Parameters.AddWithValue("@CashRepositoryId", stockMaster.CashRepositoryId);
cashRow.Parameters.AddWithValue("@OfficeId", officeId);
cashRow.Parameters.AddWithValue("@Amount", total - discountTotal + taxTotal);
cashRow.ExecuteNonQuery();
}
}
#endregion TransactionDetails
#endregion TransactionMaster
#region StockMaster
sql = "INSERT INTO transactions.stock_master(stock_master_id, transaction_master_id, party_id, is_credit, shipper_id, shipping_charge, store_id, cash_repository_id) SELECT nextval(pg_get_serial_sequence('transactions.stock_master', 'stock_master_id')), @TransactionMasterId, core.get_party_id_by_party_code(@PartyCode), @IsCredit, @ShipperId, @ShippingCharge, @StoreId, @CashRepositoryId; SELECT currval(pg_get_serial_sequence('transactions.stock_master', 'stock_master_id'));";
long stockMasterId;
using (NpgsqlCommand stockMasterRow = new NpgsqlCommand(sql, connection))
{
stockMasterRow.Parameters.AddWithValue("@TransactionMasterId", transactionMasterId);
stockMasterRow.Parameters.AddWithValue("@PartyCode", stockMaster.PartyCode);
stockMasterRow.Parameters.AddWithValue("@IsCredit", stockMaster.IsCredit);
if (stockMaster.ShipperId.Equals(0))
{
stockMasterRow.Parameters.AddWithValue("@ShipperId", DBNull.Value);
}
else
{
stockMasterRow.Parameters.AddWithValue("@ShipperId", stockMaster.ShipperId);
}
stockMasterRow.Parameters.AddWithValue("@ShippingCharge", stockMaster.ShippingCharge);
stockMasterRow.Parameters.AddWithValue("@StoreId", stockMaster.StoreId);
stockMasterRow.Parameters.AddWithValue("@CashRepositoryId", stockMaster.CashRepositoryId);
stockMasterId = Conversion.TryCastLong(stockMasterRow.ExecuteScalar());
}
#region StockDetails
sql = @"INSERT INTO
transactions.stock_details(stock_master_id, tran_type, store_id, item_id, quantity, unit_id, base_quantity, base_unit_id, price, discount, tax_rate, tax)
SELECT @StockMasterId, @TranType, @StoreId, core.get_item_id_by_item_code(@ItemCode), @Quantity, core.get_unit_id_by_unit_name(@UnitName), core.get_base_quantity_by_unit_name(@UnitName, @Quantity), core.get_base_unit_id_by_unit_name(@UnitName), @Price, @Discount, @TaxRate, @Tax;";
foreach (StockMasterDetailModel model in details)
{
using (NpgsqlCommand stockMasterDetailRow = new NpgsqlCommand(sql, connection))
{
stockMasterDetailRow.Parameters.AddWithValue("@StockMasterId", stockMasterId);
stockMasterDetailRow.Parameters.AddWithValue("@TranType", "Dr");
stockMasterDetailRow.Parameters.AddWithValue("@StoreId", model.StoreId);
stockMasterDetailRow.Parameters.AddWithValue("@ItemCode", model.ItemCode);
stockMasterDetailRow.Parameters.AddWithValue("@Quantity", model.Quantity);
stockMasterDetailRow.Parameters.AddWithValue("@UnitName", model.UnitName);
stockMasterDetailRow.Parameters.AddWithValue("@Price", model.Price);
stockMasterDetailRow.Parameters.AddWithValue("@Discount", model.Discount);
stockMasterDetailRow.Parameters.AddWithValue("@TaxRate", model.TaxRate);
stockMasterDetailRow.Parameters.AddWithValue("@Tax", model.Tax);
stockMasterDetailRow.ExecuteNonQuery();
}
}
#endregion StockDetails
#endregion StockMaster
#region Attachment
if (attachments != null && attachments.Count > 0)
{
foreach (AttachmentModel attachment in attachments)
{
sql = "INSERT INTO core.attachments(user_id, resource, resource_key, resource_id, original_file_name, file_extension, file_path, comment) SELECT @UserId, @Resource, @ResourceKey, @ResourceId, @OriginalFileName, @FileExtension, @FilePath, @Comment;";
using (NpgsqlCommand attachmentCommand = new NpgsqlCommand(sql, connection))
{
attachmentCommand.Parameters.AddWithValue("@UserId", userId);
attachmentCommand.Parameters.AddWithValue("@Resource", "transactions.transaction_master");
attachmentCommand.Parameters.AddWithValue("@ResourceKey", "transaction_master_id");
attachmentCommand.Parameters.AddWithValue("@ResourceId", transactionMasterId);
attachmentCommand.Parameters.AddWithValue("@OriginalFileName", attachment.OriginalFileName);
attachmentCommand.Parameters.AddWithValue("@FileExtension", Path.GetExtension(attachment.OriginalFileName));
attachmentCommand.Parameters.AddWithValue("@FilePath", attachment.FilePath);
attachmentCommand.Parameters.AddWithValue("@Comment", attachment.Comment);
attachmentCommand.ExecuteNonQuery();
}
}
}
#endregion Attachment
transaction.Commit();
return(transactionMasterId);
}
catch (NpgsqlException)
{
transaction.Rollback();
throw;
}
}
}
}
public static long Add(string book, DateTime valueDate, int officeId, int userId, long logOnId, string referenceNumber, string statementReference, StockMasterModel stockMaster, Collection <StockMasterDetailModel> details, Collection <int> transactionIdCollection, Collection <AttachmentModel> attachments)
{
if (stockMaster == null)
{
return(0);
}
if (details == null)
{
return(0);
}
if (details.Count.Equals(0))
{
return(0);
}
//decimal total = details.Sum(d => (d.Price * d.Quantity));
//decimal discountTotal = details.Sum(d => d.Discount);
//decimal taxTotal = details.Sum(d => d.Tax);
using (NpgsqlConnection connection = new NpgsqlConnection(DbConnection.ConnectionString()))
{
connection.Open();
using (NpgsqlTransaction transaction = connection.BeginTransaction())
{
try
{
#region NonGLStockMaster
string sql = "INSERT INTO transactions.non_gl_stock_master(non_gl_stock_master_id, value_date, book, party_id, price_type_id, login_id, user_id, office_id, reference_number, statement_reference) SELECT nextval(pg_get_serial_sequence('transactions.non_gl_stock_master', 'non_gl_stock_master_id')), @ValueDate, @Book, core.get_party_id_by_party_code(@PartyCode), @PriceTypeId, @LoginId, @UserId, @OfficeId, @ReferenceNumber, @StatementReference; SELECT currval(pg_get_serial_sequence('transactions.non_gl_stock_master', 'non_gl_stock_master_id'));";
long nonGlStockMasterId;
using (NpgsqlCommand stockMasterRow = new NpgsqlCommand(sql, connection))
{
stockMasterRow.Parameters.AddWithValue("@ValueDate", valueDate);
stockMasterRow.Parameters.AddWithValue("@Book", book);
stockMasterRow.Parameters.AddWithValue("@PartyCode", stockMaster.PartyCode);
if (stockMaster.PriceTypeId.Equals(0))
{
stockMasterRow.Parameters.AddWithValue("@PriceTypeId", DBNull.Value);
}
else
{
stockMasterRow.Parameters.AddWithValue("@PriceTypeId", stockMaster.PriceTypeId);
}
stockMasterRow.Parameters.AddWithValue("@LoginId", logOnId);
stockMasterRow.Parameters.AddWithValue("@UserId", userId);
stockMasterRow.Parameters.AddWithValue("@OfficeId", officeId);
stockMasterRow.Parameters.AddWithValue("@ReferenceNumber", referenceNumber);
stockMasterRow.Parameters.AddWithValue("@StatementReference", statementReference);
nonGlStockMasterId = Conversion.TryCastLong(stockMasterRow.ExecuteScalar());
}
#region NonGLStockDetails
sql = @"INSERT INTO
transactions.non_gl_stock_details(non_gl_stock_master_id, item_id, quantity, unit_id, base_quantity, base_unit_id, price, discount, tax_rate, tax)
SELECT @NonGlStockMasterId, core.get_item_id_by_item_code(@ItemCode), @Quantity, core.get_unit_id_by_unit_name(@UnitName), core.get_base_quantity_by_unit_name(@UnitName, @Quantity), core.get_base_unit_id_by_unit_name(@UnitName), @Price, @Discount, @TaxRate, @Tax;";
foreach (StockMasterDetailModel model in details)
{
using (NpgsqlCommand stockMasterDetailRow = new NpgsqlCommand(sql, connection))
{
stockMasterDetailRow.Parameters.AddWithValue("@NonGlStockMasterId", nonGlStockMasterId);
stockMasterDetailRow.Parameters.AddWithValue("@ItemCode", model.ItemCode);
stockMasterDetailRow.Parameters.AddWithValue("@Quantity", model.Quantity);
stockMasterDetailRow.Parameters.AddWithValue("@UnitName", model.UnitName);
stockMasterDetailRow.Parameters.AddWithValue("@Price", model.Price);
stockMasterDetailRow.Parameters.AddWithValue("@Discount", model.Discount);
stockMasterDetailRow.Parameters.AddWithValue("@TaxRate", model.TaxRate);
stockMasterDetailRow.Parameters.AddWithValue("@Tax", model.Tax);
stockMasterDetailRow.ExecuteNonQuery();
}
}
#endregion NonGLStockDetails
if (transactionIdCollection != null)
{
if (transactionIdCollection.Count > 0)
{
foreach (int tranId in transactionIdCollection)
{
sql = "INSERT INTO transactions.non_gl_stock_master_relations(order_non_gl_stock_master_id, quotation_non_gl_stock_master_id) SELECT @Id, @RelationId;";
using (NpgsqlCommand relation = new NpgsqlCommand(sql, connection))
{
relation.Parameters.AddWithValue("@Id", nonGlStockMasterId);
relation.Parameters.AddWithValue("@RelationId", tranId);
relation.ExecuteNonQuery();
}
}
}
}
#region Attachment
if (attachments != null && attachments.Count > 0)
{
foreach (AttachmentModel attachment in attachments)
{
sql = "INSERT INTO core.attachments(user_id, resource, resource_key, resource_id, original_file_name, file_extension, file_path, comment) SELECT @UserId, @Resource, @ResourceKey, @ResourceId, @OriginalFileName, @FileExtension, @FilePath, @Comment;";
using (NpgsqlCommand attachmentCommand = new NpgsqlCommand(sql, connection))
{
attachmentCommand.Parameters.AddWithValue("@UserId", userId);
attachmentCommand.Parameters.AddWithValue("@Resource", "transactions.non_gl_stock_master");
attachmentCommand.Parameters.AddWithValue("@ResourceKey", "non_gl_stock_master_id");
attachmentCommand.Parameters.AddWithValue("@ResourceId", nonGlStockMasterId);
attachmentCommand.Parameters.AddWithValue("@OriginalFileName", attachment.OriginalFileName);
attachmentCommand.Parameters.AddWithValue("@FileExtension", Path.GetExtension(attachment.OriginalFileName));
attachmentCommand.Parameters.AddWithValue("@FilePath", attachment.FilePath);
attachmentCommand.Parameters.AddWithValue("@Comment", attachment.Comment);
attachmentCommand.ExecuteNonQuery();
}
}
}
#endregion Attachment
#endregion NonGLStockMaster
transaction.Commit();
return(nonGlStockMasterId);
}
catch (NpgsqlException)
{
transaction.Rollback();
throw;
}
}
}
}
public static long Add(DateTime valueDate, int officeId, int userId, long logOnId, int costCenterId, string referenceNumber, string statementReference, StockMasterModel stockMaster, Collection <StockMasterDetailModel> details)
{
if (stockMaster == null)
{
return(0);
}
if (details == null)
{
return(0);
}
if (details.Count.Equals(0))
{
return(0);
}
decimal total = details.Sum(d => (d.Price * d.Quantity));
decimal discountTotal = details.Sum(d => d.Discount);
decimal taxTotal = details.Sum(d => d.Tax);
const string creditInvariantParameter = "Sales.Receivables";
const string salesInvariantParameter = "Sales";
const string salesTaxInvariantParamter = "Sales.Tax";
const string salesDiscountInvariantParameter = "Sales.Discount";
using (NpgsqlConnection connection = new NpgsqlConnection(DbConnection.ConnectionString()))
{
connection.Open();
using (NpgsqlTransaction transaction = connection.BeginTransaction())
{
try
{
#region TransactionMaster
string sql = "INSERT INTO transactions.transaction_master(transaction_master_id, transaction_counter, transaction_code, book, value_date, user_id, login_id, office_id, cost_center_id, reference_number, statement_reference) SELECT nextval(pg_get_serial_sequence('transactions.transaction_master', 'transaction_master_id')), transactions.get_new_transaction_counter(@ValueDate), transactions.get_transaction_code(@ValueDate, @OfficeId, @UserId, @LogOnId), @Book, @ValueDate, @UserId, @LogOnId, @OfficeId, @CostCenterId, @ReferenceNumber, @StatementReference;SELECT currval(pg_get_serial_sequence('transactions.transaction_master', 'transaction_master_id'));";
long transactionMasterId;
using (NpgsqlCommand tm = new NpgsqlCommand(sql, connection))
{
tm.Parameters.AddWithValue("@ValueDate", valueDate);
tm.Parameters.AddWithValue("@OfficeId", officeId);
tm.Parameters.AddWithValue("@UserId", userId);
tm.Parameters.AddWithValue("@LogOnId", logOnId);
tm.Parameters.AddWithValue("@Book", "Sales.Direct");
tm.Parameters.AddWithValue("@CostCenterId", costCenterId);
tm.Parameters.AddWithValue("@ReferenceNumber", referenceNumber);
tm.Parameters.AddWithValue("@StatementReference", statementReference);
//tm.UnpreparedExecute = true;
transactionMasterId = Conversion.TryCastLong(tm.ExecuteScalar());
}
#region TransactionDetails
sql = "INSERT INTO transactions.transaction_details(transaction_master_id, tran_type, account_id, statement_reference, cash_repository_id, amount) SELECT @TransactionMasterId, @TranType, core.get_account_id_by_parameter(@ParameterName), @StatementReference, @CashRepositoryId, @Amount;";
using (NpgsqlCommand salesRow = new NpgsqlCommand(sql, connection))
{
salesRow.Parameters.AddWithValue("@TransactionMasterId", transactionMasterId);
salesRow.Parameters.AddWithValue("@TranType", "Cr");
salesRow.Parameters.AddWithValue("@ParameterName", salesInvariantParameter);
salesRow.Parameters.AddWithValue("@StatementReference", statementReference);
salesRow.Parameters.AddWithValue("@CashRepositoryId", DBNull.Value);
salesRow.Parameters.AddWithValue("@Amount", total);
salesRow.ExecuteNonQuery();
}
if (taxTotal > 0)
{
using (NpgsqlCommand taxRow = new NpgsqlCommand(sql, connection))
{
taxRow.Parameters.AddWithValue("@TransactionMasterId", transactionMasterId);
taxRow.Parameters.AddWithValue("@TranType", "Cr");
taxRow.Parameters.AddWithValue("@ParameterName", salesTaxInvariantParamter);
taxRow.Parameters.AddWithValue("@StatementReference", statementReference);
taxRow.Parameters.AddWithValue("@CashRepositoryId", DBNull.Value);
taxRow.Parameters.AddWithValue("@Amount", taxTotal);
taxRow.ExecuteNonQuery();
}
}
if (discountTotal > 0)
{
using (NpgsqlCommand discountRow = new NpgsqlCommand(sql, connection))
{
discountRow.Parameters.AddWithValue("@TransactionMasterId", transactionMasterId);
discountRow.Parameters.AddWithValue("@TranType", "Dr");
discountRow.Parameters.AddWithValue("@ParameterName", salesDiscountInvariantParameter);
discountRow.Parameters.AddWithValue("@StatementReference", statementReference);
discountRow.Parameters.AddWithValue("@CashRepositoryId", DBNull.Value);
discountRow.Parameters.AddWithValue("@Amount", discountTotal);
discountRow.ExecuteNonQuery();
}
}
if (stockMaster.IsCredit)
{
using (NpgsqlCommand creditRow = new NpgsqlCommand(sql, connection))
{
creditRow.Parameters.AddWithValue("@TransactionMasterId", transactionMasterId);
creditRow.Parameters.AddWithValue("@TranType", "Dr");
creditRow.Parameters.AddWithValue("@ParameterName", creditInvariantParameter);
creditRow.Parameters.AddWithValue("@StatementReference", statementReference);
creditRow.Parameters.AddWithValue("@CashRepositoryId", DBNull.Value);
creditRow.Parameters.AddWithValue("@Amount", total - discountTotal + taxTotal + stockMaster.ShippingCharge);
creditRow.ExecuteNonQuery();
}
}
else
{
sql = "INSERT INTO transactions.transaction_details(transaction_master_id, tran_type, account_id, statement_reference, cash_repository_id, amount) SELECT @TransactionMasterId, @TranType, core.get_cash_account_id(), @StatementReference, @CashRepositoryId, @Amount;";
using (NpgsqlCommand cashRow = new NpgsqlCommand(sql, connection))
{
cashRow.Parameters.AddWithValue("@TransactionMasterId", transactionMasterId);
cashRow.Parameters.AddWithValue("@TranType", "Dr");
cashRow.Parameters.AddWithValue("@StatementReference", statementReference);
cashRow.Parameters.AddWithValue("@CashRepositoryId", stockMaster.CashRepositoryId);
cashRow.Parameters.AddWithValue("@Amount", total - discountTotal + taxTotal + stockMaster.ShippingCharge);
cashRow.ExecuteNonQuery();
}
}
if (stockMaster.ShippingCharge > 0)
{
sql = "INSERT INTO transactions.transaction_details(transaction_master_id, tran_type, account_id, statement_reference, cash_repository_id, amount) SELECT @TransactionMasterId, @TranType, core.get_account_id_by_shipper_id(@ShipperId), @StatementReference, @CashRepositoryId, @Amount;";
using (NpgsqlCommand shippingChargeRow = new NpgsqlCommand(sql, connection))
{
shippingChargeRow.Parameters.AddWithValue("@TransactionMasterId", transactionMasterId);
shippingChargeRow.Parameters.AddWithValue("@TranType", "Cr");
shippingChargeRow.Parameters.AddWithValue("@ShipperId", stockMaster.ShipperId);
shippingChargeRow.Parameters.AddWithValue("@StatementReference", statementReference);
shippingChargeRow.Parameters.AddWithValue("@CashRepositoryId", DBNull.Value);
shippingChargeRow.Parameters.AddWithValue("@Amount", stockMaster.ShippingCharge);
shippingChargeRow.ExecuteNonQuery();
}
}
#endregion
#endregion
#region StockMaster
sql = "INSERT INTO transactions.stock_master(stock_master_id, transaction_master_id, party_id, agent_id, price_type_id, is_credit, shipper_id, shipping_address_id, shipping_charge, store_id, cash_repository_id) SELECT nextval(pg_get_serial_sequence('transactions.stock_master', 'stock_master_id')), @TransactionMasterId, core.get_party_id_by_party_code(@PartyCode), @AgentId, @PriceTypeId, @IsCredit, @ShipperId, core.get_shipping_address_id_by_shipping_address_code(@ShippingAddressCode), @ShippingCharge, @StoreId, @CashRepositoryId; SELECT currval(pg_get_serial_sequence('transactions.stock_master', 'stock_master_id'));";
long stockMasterId;
using (NpgsqlCommand stockMasterRow = new NpgsqlCommand(sql, connection))
{
stockMasterRow.Parameters.AddWithValue("@TransactionMasterId", transactionMasterId);
stockMasterRow.Parameters.AddWithValue("@PartyCode", stockMaster.PartyCode);
stockMasterRow.Parameters.AddWithValue("@AgentId", stockMaster.AgentId);
stockMasterRow.Parameters.AddWithValue("@PriceTypeId", stockMaster.PriceTypeId);
stockMasterRow.Parameters.AddWithValue("@IsCredit", stockMaster.IsCredit);
if (stockMaster.ShipperId.Equals(0))
{
stockMasterRow.Parameters.AddWithValue("@ShipperId", DBNull.Value);
}
else
{
stockMasterRow.Parameters.AddWithValue("@ShipperId", stockMaster.ShipperId);
}
stockMasterRow.Parameters.AddWithValue("@ShippingAddressCode", stockMaster.ShippingAddressCode);
stockMasterRow.Parameters.AddWithValue("@ShippingCharge", stockMaster.ShippingCharge);
stockMasterRow.Parameters.AddWithValue("@StoreId", stockMaster.StoreId);
stockMasterRow.Parameters.AddWithValue("@CashRepositoryId", stockMaster.CashRepositoryId);
//stockMasterRow.UnpreparedExecute = true;
stockMasterId = Conversion.TryCastLong(stockMasterRow.ExecuteScalar());
}
#region StockDetails
sql = @"INSERT INTO
transactions.stock_details(stock_master_id, tran_type, store_id, item_id, quantity, unit_id, base_quantity, base_unit_id, price, discount, tax_rate, tax)
SELECT @StockMasterId, @TranType, @StoreId, core.get_item_id_by_item_code(@ItemCode), @Quantity, core.get_unit_id_by_unit_name(@UnitName), core.get_base_quantity_by_unit_name(@UnitName, @Quantity), core.get_base_unit_id_by_unit_name(@UnitName), @Price, @Discount, @TaxRate, @Tax;";
foreach (StockMasterDetailModel model in details)
{
using (NpgsqlCommand stockMasterDetailRow = new NpgsqlCommand(sql, connection))
{
stockMasterDetailRow.Parameters.AddWithValue("@StockMasterId", stockMasterId);
stockMasterDetailRow.Parameters.AddWithValue("@TranType", "Cr");
stockMasterDetailRow.Parameters.AddWithValue("@StoreId", model.StoreId);
stockMasterDetailRow.Parameters.AddWithValue("@ItemCode", model.ItemCode);
stockMasterDetailRow.Parameters.AddWithValue("@Quantity", model.Quantity);
stockMasterDetailRow.Parameters.AddWithValue("@UnitName", model.UnitName);
stockMasterDetailRow.Parameters.AddWithValue("@Price", model.Price);
stockMasterDetailRow.Parameters.AddWithValue("@Discount", model.Discount);
stockMasterDetailRow.Parameters.AddWithValue("@TaxRate", model.TaxRate);
stockMasterDetailRow.Parameters.AddWithValue("@Tax", model.Tax);
stockMasterDetailRow.ExecuteNonQuery();
}
}
#endregion
#endregion
transaction.Commit();
return(transactionMasterId);
}
catch (NpgsqlException)
{
transaction.Rollback();
throw;
}
}
}
}
public IEnumerable <StockSummaryMasterModel> StockSummary(StockMasterModel model, string databasename)
{
string strConnection = "";
strConnection = "Server=localhost;user id=root;password=pari@123;Database=" + databasename + "";
List <StockSummaryMasterModel> data = new List <StockSummaryMasterModel>();
MySqlConnection myConn = new MySqlConnection();
MySqlCommand myCmd = new MySqlCommand();
MySqlDataReader myRdr = null;
try
{
myConn.ConnectionString = strConnection;
myConn.Open();
myCmd.Connection = myConn;
myCmd.CommandType = CommandType.StoredProcedure;
myCmd.CommandText = "STOCKSUMM_Android";
MySqlParameter ParamdToDate = new MySqlParameter();
ParamdToDate.DbType = System.Data.DbType.DateTime;
ParamdToDate.Direction = ParameterDirection.Input;
ParamdToDate.ParameterName = "@VRDATE";
ParamdToDate.Value = model.ToDate;
MySqlParameter ParamdFromDate = new MySqlParameter();
ParamdFromDate.DbType = System.Data.DbType.DateTime;
ParamdFromDate.Direction = ParameterDirection.Input;
ParamdFromDate.ParameterName = "@VRDATE_TILL";
ParamdFromDate.Value = model.FromDate;
MySqlParameter ParamdDepartmentId = new MySqlParameter();
ParamdDepartmentId.DbType = System.Data.DbType.Int32;
ParamdDepartmentId.Direction = ParameterDirection.Input;
ParamdDepartmentId.ParameterName = "@COSTCODE";
ParamdDepartmentId.Value = model.DepartmentId;
MySqlParameter ParamdLocationName = new MySqlParameter();
ParamdLocationName.DbType = System.Data.DbType.String;
ParamdLocationName.Direction = ParameterDirection.Input;
ParamdLocationName.ParameterName = "@Locationname";
ParamdLocationName.Value = model.Location;
myCmd.Parameters.Add(ParamdToDate);
myCmd.Parameters.Add(ParamdFromDate);
myCmd.Parameters.Add(ParamdDepartmentId);
myCmd.Parameters.Add(ParamdLocationName);
myRdr = myCmd.ExecuteReader();
if (myRdr.HasRows)
{
while (myRdr.Read())
{
StockSummaryMasterModel singledata = new StockSummaryMasterModel();
singledata.DepartmentId = Convert.ToInt32(myRdr["COSTCENTER_CODE"].ToString());
singledata.ItemName = myRdr["item_name"].ToString();
singledata.ItemCode = myRdr["Item_code"].ToString();
singledata.TotalRecieved = Convert.ToDouble(myRdr["tot_recd"].ToString());
singledata.TotalIssue = Convert.ToDouble(myRdr["tot_ISSUE"].ToString());
singledata.OpeningBalance = Convert.ToDouble(myRdr["opening_balance"].ToString());
singledata.TotalReceivedTill = Convert.ToDouble(myRdr["Total_received_Till"].ToString());
singledata.TotalIssuedTill = Convert.ToDouble(myRdr["Total_Issued_Till"].ToString());
singledata.ClosingBalance = Convert.ToDouble(myRdr["Closing_Balance"].ToString());
singledata.Price = Convert.ToDouble(myRdr["PRICE"].ToString());
singledata.UOM = myRdr["uom"].ToString();
singledata.GroupName = myRdr["GRNAME"].ToString();
data.Add(singledata);
}
}
}
catch (Exception ex)
{
}
finally
{
if (myRdr != null)
{
myRdr.Close();
myRdr.Dispose();
myRdr = null;
}
if (myCmd != null)
{
myCmd.Dispose();
myCmd = null;
}
if (myConn != null)
{
myConn.Close();
myConn.Dispose();
myConn = null;
}
}
return(data);
}
