//-------------------- Update Payment Information ----------------------------------------------
public void UpdatePaymentInformation(DBSite site, SalePurchaseEntity salePurchase, string ledgerIdNumber)
{
string ledger_id = util.GetLedgerId(ledgerIdNumber);
string ledger_number = util.GetLedgerNumber(ledgerIdNumber);
// 444
int party = Int32.Parse(util.GetLOVId(salePurchase.party));
int mode = Int32.Parse(util.GetLOVId(salePurchase.mode));
if (salePurchase.transactionType == TransactionType.PaymentMade)
{
int tmp;
tmp = Int32.Parse(util.GetLOVId(salePurchase.mode));
mode = Int32.Parse(util.GetLOVId(salePurchase.party));
party = tmp;
}
// sales - party
string party_qry = " UPDATE tblLedger SET "
+ " LDate = '" + salePurchase.BillDate + "' "
+ ", AccountMasterId= " + mode
+ ", modeid= " + party
+ ", BillNumber= '" + util.GetLOVName(salePurchase.BillNumber) + "' "
+ ", Particulars= '" + salePurchase.particulars + "' "
+ ", debit = " + salePurchase.SalePurchaseAmount
+ ", CheckNumber= '" + salePurchase.CheckNumber + "' "
+ ", subUserId = " + Util_BLL.User.Subusers[0].SubuserId
+ Util_BLL.GetUserWhereCondition(Util_BLL.User)
// + " AND LedgerId=" + ledger_id
+ " AND LedgerNumber=" + ledger_number
+ " AND drcr = 'D'";
// sales - mode
string mode_qry = " UPDATE tblLedger SET "
+ " LDate = '" + salePurchase.BillDate + "' "
+ ", AccountMasterId= " + party
+ ", modeId = " + mode
+ ", BillNumber= '" + util.GetLOVName(salePurchase.BillNumber) + "' "
+ ", Particulars= '" + salePurchase.particulars + "' "
+ ", credit = " + salePurchase.SalePurchaseAmount
+ ", CheckNumber= '" + salePurchase.CheckNumber + "' "
+ ", subUserId = " + Util_BLL.User.Subusers[0].SubuserId
+ Util_BLL.GetUserWhereCondition(Util_BLL.User)
+ " AND LedgerNumber=" + ledger_number
+ " AND drcr = 'C'";
site.Execute(party_qry);
site.Execute(mode_qry);
}
//------------------ Get Sale-Purchage Information -----------------------------------------------
public List <SalePurchaseEntity> GetSalePurchase(DBSite site, string sp_ids = "")
{
List <SalePurchaseEntity> sale_purchase_entity_list = new List <SalePurchaseEntity>();
SalePurchaseEntity sale_purchase_entity = null;
//444
string qry = " SELECT ledgerId "
+ ", Billdate "
+ ", AccountName "
+ ", credit " // if sales else debit
+ ", particulars "
+ ", discount "
+ " FROM tblLedger l"
+ Util_BLL.GetUserWhereCondition(Util_BLL.User);
//string qry = GetQueryForSimpleFields();
DataTable dt = dt = site.ExecuteSelect(qry);
foreach (DataRow row in dt.Rows)
{
sale_purchase_entity = new SalePurchaseEntity(); // -------- sale purchase entity ----------
//--------------- Normal Fields ----------------------------
sale_purchase_entity.SalePurchaseId = util.CheckNullInt(row["LedgerId"]);
sale_purchase_entity.BillDate = util.CheckNull(row["LDate"]);
sale_purchase_entity.party = util.CheckNull(row["AccountName"]);
sale_purchase_entity.BillNumber = util.CheckNull(row["BillNumber"]);
sale_purchase_entity.SalePurchaseAmount = util.CheckNullDouble(row["Debit"]);
sale_purchase_entity.discount = util.CheckNull(row["Particulars"]);
sale_purchase_entity.discount = util.CheckNull(row["Discount"]);
double discount = util.GetDiscountAmount(sale_purchase_entity.discount, sale_purchase_entity.SalePurchaseAmount);
sale_purchase_entity.TotalAmount = sale_purchase_entity.SalePurchaseAmount - discount;
//---------------- sale detail entity [ product information ]------------------
// -------------- tax information [ taxes on the whole ] ------------------------
sale_purchase_entity_list.Add(sale_purchase_entity);
}
return(sale_purchase_entity_list);
}
//----------------- Search Method [ For Payment ] ----------------------------
public List <SalePurchaseEntity> GetMatchedRecords(DBSite site, string payment_made_or_received, string value_to_search)
{
List <SalePurchaseEntity> sale_purchase_entity_list = new List <SalePurchaseEntity>();
SalePurchaseEntity sale_purchase_entity = null;
string qry = " SELECT DISTINCT LedgerNumber FROM tblLedger as ldg "
+ " LEFT OUTER JOIN tblAccountMaster as acc"
+ " ON ldg.AccountMasterId = acc.AccountMasterId "
+ " WHERE ( "
+ " ( BillNumber LIKE '%" + value_to_search + "%')"
+ " OR ( CheckNumber LIKE '%" + value_to_search + "%')"
+ " OR ( AccountName LIKE '%" + value_to_search + "%')";
if (IsDate(value_to_search))
{
qry += " OR ( LDate = '" + value_to_search + "' ) ";
}
if (util.IsNumber(value_to_search))
{
qry += " OR ( Credit = '" + value_to_search + "' ) ";
qry += " OR( Debit = '" + value_to_search + "' ) ";
}
qry += " )";
qry += " AND ldg.UserId = " + Util_BLL.User.UserId;
qry += " AND ldg.FYear = " + Util_BLL.User.fYear;
qry += " AND ldg.SourceId = " + payment_made_or_received;
string ledger_number = null;
DataTable dt = site.ExecuteSelect(qry);
foreach (DataRow row in dt.Rows)
{
sale_purchase_entity = new SalePurchaseEntity();
ledger_number = util.CheckNull(row["LedgerNumber"]);
sale_purchase_entity = GetPaymentInformation(site, payment_made_or_received, ledger_number)[0];
sale_purchase_entity_list.Add(sale_purchase_entity);
}
return(sale_purchase_entity_list);
}
//------------- Sale purchse Entity Objects ---------------------------------
public List <SalePurchaseEntity> Get(DBSite site, string sp_ids = "")
{
List <SalePurchaseEntity> sale_purchase_entity_list = new List <SalePurchaseEntity>();
SalePurchaseEntity sale_purchase_entity_1 = new SalePurchaseEntity
{
SalePurchaseId = 1001,
party = "gurgaon branch1|4",
BillNumber = "BN108",
BillDate = "02/02/2012",
SalePurchaseAmount = 3000.00,
discount = "50%",
TotalAmount = 1500.00,
particulars = "Sale 1",
SalePurchaseTaxes = new List <Tax>(
new Tax[] {
new Tax {
TaxId = 101,
TaxName = "Cst|3",
TaxPercent = 0,
TaxAmount = 40
}
}
),
SalePurchaseDetails = new List <SalePurchaseDetail>(
new SalePurchaseDetail[] {
new SalePurchaseDetail {
SalePurchaseDetailId = 100,
productId = "new Product 10|4",
qty = 20,
UOM = "Litre|6",
SalePurchaseDetailAmount = 2000,
discount = "30",
SalePurchaseDetailsTaxes = new List <Tax>(
new Tax[] {
new Tax {
TaxId = 301,
TaxName = "Vat|2",
TaxPercent = 10,
TaxAmount = 100
}
}
)
}
}
)
};
SalePurchaseEntity sale_purchase_entity_2 = new SalePurchaseEntity
{
SalePurchaseId = 1002,
party = "newaccount 1|21",
BillNumber = "BN109",
BillDate = "02/05/2012",
SalePurchaseAmount = 2000.00,
discount = "50%",
TotalAmount = 1000.00,
particulars = "Sale 2",
SalePurchaseTaxes = new List <Tax>(
new Tax[] {
new Tax {
TaxId = 102,
TaxName = "Cst|3",
TaxPercent = 0,
TaxAmount = 50
}
}
),
SalePurchaseDetails = new List <SalePurchaseDetail>(
new SalePurchaseDetail[] {
new SalePurchaseDetail {
SalePurchaseDetailId = 101,
productId = "new Product 10|4",
qty = 30,
UOM = "Litre|6",
SalePurchaseDetailAmount = 2000,
discount = "20",
SalePurchaseDetailsTaxes = new List <Tax>(
new Tax[] {
new Tax {
TaxId = 302,
TaxName = "Vat|2",
TaxPercent = 0,
TaxAmount = 50
}
}
)
}
}
)
};
sale_purchase_entity_list.Add(sale_purchase_entity_1);
sale_purchase_entity_list.Add(sale_purchase_entity_2);
return(sale_purchase_entity_list);
}
//------------------- Get Payment Information --------------------------------------------------
public List <SalePurchaseEntity> GetPaymentInformation(DBSite site, string payment_made_or_received, string ledger_number = "")
{
List <SalePurchaseEntity> sale_purchase_entity_list = new List <SalePurchaseEntity>();
SalePurchaseEntity sale_purchase_entity = null;
string qry = " SELECT ";
qry += " CONVERT( VARCHAR, LedgerId ) + '-' + CONVERT( VARCHAR, LedgerNumber ) as LedgerNumber ";
qry += ", LDate ";
qry += ", AccountName + ' | ' +CONVERT( VARCHAR, AccountMasterId ) as AccountName ";
qry += ", ModeName + ' | ' +CONVERT( VARCHAR, ModeId ) as ModeName ";
if (Int32.Parse(payment_made_or_received) == TransactionType.PaymentMade)
{
qry += ", debit amount";
}
else
{
qry += ", credit amount ";
}
qry += ", Particulars ";
qry += ", BillNumber ";
qry += ", CheckNumber ";
qry += " FROM vwLedger as ldg ";
qry += " WHERE SourceId= " + payment_made_or_received;
if (Int32.Parse(payment_made_or_received) == TransactionType.PaymentMade)
{
qry += " AND drcr ='D'";
}
else
{
qry += " AND drcr ='C'";
}
qry += " AND ldg.UserId=" + Util_BLL.User.UserId;
qry += " AND ldg.FYear=" + Util_BLL.User.fYear;
if (ledger_number != "")
{
qry += " AND LedgerNumber = " + ledger_number;
}
DataTable dt = site.ExecuteSelect(qry);
DataRow row = null;
for (int i = 0; i < dt.Rows.Count; i++)
{
sale_purchase_entity = new SalePurchaseEntity();
row = dt.Rows[i];
sale_purchase_entity.Id = util.CheckNull(row["LedgerNumber"]);
DateTime date = Convert.ToDateTime(row["LDate"]);
sale_purchase_entity.BillDate = date.ToShortDateString();
sale_purchase_entity.CheckNumber = util.CheckNull(row["CheckNumber"]);
sale_purchase_entity.particulars = util.CheckNull(row["Particulars"]);
sale_purchase_entity.BillNumber = util.CheckNull(row["BillNumber"]);
sale_purchase_entity.SalePurchaseAmount = util.CheckNullDouble(row["amount"]);
sale_purchase_entity.party = util.CheckNull(row["AccountName"]);
sale_purchase_entity.mode = util.CheckNull(row["modeName"]);
sale_purchase_entity_list.Add(sale_purchase_entity);
}
return(sale_purchase_entity_list);
}
public void UpdateSalePurchase(DBSite site, SalePurchaseEntity salePurchase)
{
}
public void SaveProductLedger(DBSite site, SalePurchaseEntity salePurchase, int ledgerNumber)
{
string qry = "";
for (int i = 0; i < salePurchase.SalePurchaseDetails.Count; i++)
{
int productLedgerNumber = util.GetAutoNumber(site, "ProductLedgerNumber");
int theLocation = -1;
if (salePurchase.transactionType == TransactionType.Sales)
{
theLocation = GetLocationId(site, "OUT");
}
else
{
theLocation = GetLocationId(site, "IN");
}
qry = " INSERT INTO tblProductLedger(LedgerNumber, ProductLedgerNumber, drcr, BillDate, BillNumber, AccountId, ProductID, BoughtQty, Discount, rate, SourceId, LocationID, UserID, subuserId, FYear) "
+ " VALUES ( "
+ ledgerNumber
+ ", " + productLedgerNumber
+ ", 'C'"
+ ", '" + salePurchase.BillDate + "'"
+ ", '" + salePurchase.BillNumber + "'"
+ ", '" + util.GetLOVId(salePurchase.party) + "'"
+ ", " + salePurchase.SalePurchaseDetails[i].productAutoId
+ ", " + salePurchase.SalePurchaseDetails[i].qty
+ ", '" + salePurchase.SalePurchaseDetails[i].discount + " '"
+ ", " + salePurchase.SalePurchaseDetails[i].rate
+ ", " + salePurchase.SaleType
+ ", " + theLocation
//+ ", " + salePurchase.SalePurchaseDetails[i].locationId
+ ", " + util.GetUserInsertQry(Util_BLL.User)
+ " ) ";
site.Execute(qry);
qry = " INSERT INTO tblProductLedger(LedgerNumber, ProductLedgerNumber, drcr, BillDate, BillNumber, AccountId, ProductID, SoldQty, Discount, rate, SourceId, LocationID, UserID, subuserId, FYear) "
+ " VALUES ( "
+ ledgerNumber
+ ", " + productLedgerNumber
+ ", 'D'"
+ ", '" + salePurchase.BillDate + "'"
+ ", '" + salePurchase.BillNumber + "'"
+ ", '" + Modes.SaleAccount + "'"
+ ", " + salePurchase.SalePurchaseDetails[i].productAutoId
+ ", " + salePurchase.SalePurchaseDetails[i].qty
+ ", '" + salePurchase.SalePurchaseDetails[i].discount + " '"
+ ", " + salePurchase.SalePurchaseDetails[i].rate
+ ", " + salePurchase.SaleType
+ ", " + theLocation
//+ ", " + salePurchase.SalePurchaseDetails[i].locationId
+ ", " + util.GetUserInsertQry(Util_BLL.User)
+ " ) ";
site.Execute(qry);
string tax_qry = "";
for (int j = 0; j < salePurchase.SalePurchaseDetails[i].SalePurchaseDetailsTaxes.Count; j++)
{
tax_qry = " INSERT INTO tblTax(LedgerNumber, ProductLedgerNumber, TaxMasterId, TaxAmount, UserID, subUserId, FYear) "
+ " VALUES ( "
+ ledgerNumber
+ ", " + productLedgerNumber
+ ", " + salePurchase.SalePurchaseDetails[i].SalePurchaseDetailsTaxes[j].TaxId
+ ", " + salePurchase.SalePurchaseDetails[i].SalePurchaseDetailsTaxes[j].TaxAmount
+ ", " + util.GetUserInsertQry(Util_BLL.User)
+ " ) ";
site.Execute(tax_qry);
}
}
}
public void SaveSalePurchase(DBSite site, SalePurchaseEntity salePurchase, bool isOpeningBalance)
{
Util_BLL util = new Util_BLL();
/**
*
* // payment made If GM.isFees = 2 Then
* Dim tmp As String
* tmp = mode
* mode = party
* party = tmp
* End If
*
**/
int party = Int32.Parse(util.GetLOVId(salePurchase.party));
int mode = Int32.Parse(util.GetLOVId(salePurchase.mode));
if (salePurchase.transactionType == TransactionType.PaymentMade)
{
int tmp;
tmp = Int32.Parse(util.GetLOVId(salePurchase.mode));
mode = Int32.Parse(util.GetLOVId(salePurchase.party));
party = tmp;
}
if (salePurchase.transactionType == TransactionType.Sales)
{
mode = Modes.SaleAccount;
}
int ledgerNumber = util.GetAutoNumber(site, "LedgerNumber");
// opening balance
string opening_qry = "";
if (isOpeningBalance)
{
string credit_debit = "Debit";
if (salePurchase.drcr == DrCr.Debit)
{
credit_debit = "Debit";
}
else if (salePurchase.drcr == DrCr.Credit)
{
credit_debit = "Credit";
}
opening_qry = " INSERT INTO tblLedger ("
+ " LedgerNumber, Ldate, AccountMasterId, modeId, Particulars, " + credit_debit + ", sourceId, UserId, SubUserId, FYear )"
+ " VALUES ( "
+ ledgerNumber
+ ", '" + salePurchase.BillDate + "' "
+ ", " + party
+ ", " + mode
+ ", '" + salePurchase.particulars + "' "
+ ", " + salePurchase.SalePurchaseAmount
+ ", " + salePurchase.transactionType
+ ", " + util.GetUserInsertQry(Util_BLL.User)
+ " ) ";
site.Execute(opening_qry);
}
// sales - party
string party_qry = " INSERT INTO tblLedger ("
+ " LedgerNumber, drcr, Ldate, AccountMasterId, modeId, BillNumber, Particulars, credit, Discount, sourceId, UserId, SubuserId, FYear )"
+ " VALUES ( "
+ ledgerNumber
+ ", 'C' "
+ ", '" + salePurchase.BillDate + "' "
+ ", " + party
+ ", " + mode
+ ", '" + util.GetLOVName(salePurchase.BillNumber) + "' "
+ ", '" + salePurchase.particulars + "' "
+ ", " + salePurchase.SalePurchaseAmount
+ ", '" + salePurchase.discount + "'"
+ ", " + salePurchase.transactionType
+ ", " + util.GetUserInsertQry(Util_BLL.User)
+ " ) ";
// sales - party
string mode_qry = " INSERT INTO tblLedger ("
+ " LedgerNumber, drcr, Ldate, AccountMasterId, modeId, BillNumber, Particulars, debit, Discount, sourceId, UserId, SubuserId, FYear )"
+ " VALUES ( "
+ ledgerNumber
+ ", 'D' "
+ ", '" + salePurchase.BillDate + "' "
+ ", " + mode
+ ", " + party
+ ", '" + util.GetLOVName(salePurchase.BillNumber) + "' "
+ ", '" + salePurchase.particulars + "' "
+ ", " + salePurchase.SalePurchaseAmount
+ ", '" + salePurchase.discount + "'"
+ ", " + salePurchase.transactionType
+ ", " + util.GetUserInsertQry(Util_BLL.User)
+ " ) ";
site.Execute(party_qry);
site.Execute(mode_qry);
if (salePurchase.transactionType != TransactionType.PaymentMade && salePurchase.transactionType != TransactionType.PaymentRcd)
{
string tax_qry = "";
for (int i = 0; i < salePurchase.SalePurchaseTaxes.Count; i++)
{
tax_qry = " INSERT INTO tblTax(LedgerNumber, TaxMasterId, TaxAmount, UserID, SubuserId, FYear) "
+ " VALUES ( "
+ ledgerNumber
+ ", " + salePurchase.SalePurchaseTaxes[i].TaxId
+ ", " + salePurchase.SalePurchaseTaxes[i].TaxAmount
+ ", " + util.GetUserInsertQry(Util_BLL.User)
+ " ) ";
site.Execute(tax_qry);
}
SaveProductLedger(site, salePurchase, ledgerNumber);
}
}