private void ReceiptSave()
{
mlstQuery.Clear();
string ladgerID = ((KeyValuePair <string, string>)cmbSuppliersName.SelectedItem).Key.ToString();
string paymentDate = dtpBillDate.Text;
string paidAmountStr = mTotalReceiptAmount.ToString();
string voucherNo = lblVoucherNo.Text;
string transectionid = Guid.NewGuid().ToString();
string transectiontype = "Receipt_Payment";
string drledgerid = ((KeyValuePair <string, string>)cmbPaymentAccount.SelectedItem).Key.ToString();
string crledgerid = ladgerID;
string status = "Due";
string mode = "'" + cmbPaymentMethod.Text + "'";
string bankname = cmbPaymentMethod.Text == "Cheque" ? "'" + cmbBank.Text.GetDBFormatString() + "'" : "NULL";
string checkno = cmbPaymentMethod.Text == "Cheque" ? "'" + txtChequeNo.Text.GetDBFormatString() + "'" : "NULL";
string checkdate = cmbPaymentMethod.Text == "Cheque" ? "'" + dtpDateCheque.Text.GetDBFormatString() + "'" : "NULL";
if (mTotalReceiptAmount == 0)
{
drledgerid = LedgerTools._DicCashLedgers.Keys.First();
}
string partpayment = string.Empty;
string fullpayment = string.Empty;
UpdateInvoiceLastTransectionId(transectionid, out partpayment, out fullpayment);
UpdateAdvanceReceiptLastTransectionId(transectionid);
partpayment = partpayment.ISNullOrWhiteSpace() ? string.Empty : "Part Payment of(" + partpayment + ")";
fullpayment = fullpayment.ISNullOrWhiteSpace() ? string.Empty : "Full Payment of(" + fullpayment + ")";
string naration = fullpayment.ISNullOrWhiteSpace() ? partpayment : partpayment.ISNullOrWhiteSpace() ? fullpayment : fullpayment + ", " + partpayment;
InsertOrUpdateTransection(transectionid, paymentDate, voucherNo, paidAmountStr, drledgerid, crledgerid, transectiontype, mode, bankname, checkno, checkdate, naration);
#region CurrentBalanceUpdate
mlstQuery.Add(LedgerStatus.UpdateLedgerStatus(drledgerid, crledgerid, paidAmountStr, out mQuery));//drQuery Add in the ListQuery and Out CrQuery in mQuery
mlstQuery.Add(mQuery);
#endregion
//UpdateCurrentBalance(drledgerid, paidAmountStr);
#region Execute
if (MessageBox.Show("Are you sure you want to receipt Rs. " + mTotalReceiptAmount + " ?", "Receipt Payment", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
if (SQLHelper.GetInstance().ExecuteTransection(mlstQuery, out msg))
{
if (MessageBox.Show("Do you want to print the receipt copy?", "Receipt Voucher", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
{
ReceiptVoucherReportView receiptVoucherReportView = new ReceiptVoucherReportView(transectionid);
receiptVoucherReportView.Show();
}
this.Close();
}
else
{
MessageBox.Show(msg, "Receipt Payment", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
#endregion
}
private void DataSave()
{
string slNo = lblJurnalNo.Text;
string date = dtpDate.Text;
string cashLedgerID = ((KeyValuePair <string, string>)cmbCashLedger.SelectedItem).Key.ToString();
double amount = 0d;
double.TryParse(txtAmount.Text, out amount);
string chequeNo = txtChequeNo.Text.GetDBFormatString();
string chequeDate = dtpDateCheque.Text;
string transectionType = mMode.ToString();
string transectionMode = "";
string narration = txtDescription.Text.GetDBFormatString();
string drLedgerid = mLedgerID;
string crLedgerid = cashLedgerID;
string mquery = "";
switch (mMode)
{
case _Mode.Deposit:
InsertOrUpdateTransection(date, slNo, amount, drLedgerid, crLedgerid, transectionType, chequeNo, chequeDate, transectionMode, narration);
#region UpdateLedgerStatus
if (!mSlNoEdit.ISNullOrWhiteSpace())
{
#region CurrentBalanceRestore
mLstQuery.Add(LedgerStatus.UpdateLedgerStatus(TransectionTools._CRAccountLedgerId, TransectionTools._DRAccountLedgerId, mTotalPreviousAmount.ToString("0.00"), out mquery)); //drQuery Add in the ListQuery and Out CrQuery in mQuery
mLstQuery.Add(mquery);
#endregion
}
#region CurrentBalanceUpdate
mLstQuery.Add(LedgerStatus.UpdateLedgerStatus(drLedgerid, crLedgerid, amount.ToString("0.00"), out mquery)); //drQuery Add in the ListQuery and Out CrQuery in mQuery
mLstQuery.Add(mquery);
#endregion
#endregion
break;
case _Mode.Withdrawal:
transectionMode = "Cheque";
drLedgerid = cashLedgerID;
crLedgerid = mLedgerID;
InsertOrUpdateTransection(date, slNo, amount, drLedgerid, crLedgerid, transectionType, chequeNo, chequeDate, transectionMode, narration);
#region UpdateLedgerStatus
if (!mSlNoEdit.ISNullOrWhiteSpace())
{
#region CurrentBalanceRestore
mLstQuery.Add(LedgerStatus.UpdateLedgerStatus(TransectionTools._CRAccountLedgerId, TransectionTools._DRAccountLedgerId, mTotalPreviousAmount.ToString("0.00"), out mquery)); //drQuery Add in the ListQuery and Out CrQuery in mQuery
mLstQuery.Add(mquery);
#endregion
}
#region CurrentBalanceUpdate
mLstQuery.Add(LedgerStatus.UpdateLedgerStatus(drLedgerid, crLedgerid, amount.ToString("0.00"), out mquery)); //drQuery Add in the ListQuery and Out CrQuery in mQuery
mLstQuery.Add(mquery);
#endregion
#endregion
break;
default:
break;
}
if (SQLHelper.GetInstance().ExecuteTransection(mLstQuery, out msg))
{
LedgerTools.GetBankLedgers();
mSuccess = true;
this.Close();
}
else
{
MessageBox.Show(msg, mMode.ToString(), MessageBoxButtons.OK, MessageBoxIcon.Stop);
}
}
private void DataSave()
{
#region Data
mlistquery.Clear();
string transectionid = Guid.NewGuid().ToString();
string receptno = lblreceiptNo.Text.GetDBFormatString();
string receiptddate = dtpAdvPaymentDate.Text.GetDBFormatString();
string orderno = "NULL";
string orderdate = "NULL";
string rcm = "NO";
#region Billing
string billingname = lblNameBilling.Text.GetDBFormatString();
string billingaddress = lblBillingAddress.Text.GetDBFormatString();
string billingstate = lblStateNameBilling.Text.GetDBFormatString();
string billingstatecode = lblStateCodeBilling.Text.GetDBFormatString();
#endregion
#region Shipping
string shippingname = lblShippedTo.Text.GetDBFormatString();
string shippingaddress = lblShippedAddress.Text.GetDBFormatString();
string shippingstate = lblStateNameShipping.Text.GetDBFormatString();
string shippingstatecode = lblStateCodeShipping.Text.GetDBFormatString();
#endregion
#region Item
object itemidobj = dgvItemList.Rows[0].Cells["itemid"].Value;
string itemid = itemidobj.ISValidObject() ? "" + itemidobj.ToString() + "" : "NULL";
object itemnameobj = dgvItemList.Rows[0].Cells["itemname"].Value;
string itemname = itemnameobj.ISValidObject() ? "'" + itemnameobj.ToString() + "'" : "NULL";
object comoditicodeobj = dgvItemList.Rows[0].Cells["ParticularsHsnCode"].Value;
string comoditicode = comoditicodeobj.ISValidObject() ? "'" + comoditicodeobj.ToString() + "'" : "NULL";
object qtyobj = dgvItemList.Rows[0].Cells["QTY"].Value;
string qty = qtyobj.ISValidObject() ? "" + qtyobj.ToString() + "" : "NULL";
object unitobj = dgvItemList.Rows[0].Cells["UNIT"].Value;
string unit = unitobj.ISValidObject() ? "'" + unitobj.ToString() + "'" : "NULL";
object rateobj = dgvItemList.Rows[0].Cells["RATE"].Value;
string rate = rateobj.ISValidObject() ? "" + rateobj.ToString() + "" : "NULL";
object taxvalueobj = dgvItemList.Rows[0].Cells["TAXABLEVALUE"].Value;
string taxvalue = taxvalueobj.ISValidObject() ? "" + taxvalueobj.ToString() + "" : "NULL";
object cgstrateobj = dgvItemList.Rows[0].Cells["CGSTRATE"].Value;
object cgstamountobj = dgvItemList.Rows[0].Cells["CGSTAMOUNT"].Value;
string cgstrate = cgstrateobj.ISValidObject() ? "" + cgstrateobj.ToString() + "" : "NULL";
string cgstamount = cgstamountobj.ISValidObject() ? "" + cgstamountobj.ToString() + "" : "NULL";
object sgstrateobj = dgvItemList.Rows[0].Cells["SGSTRATE"].Value;
object sgstamountobj = dgvItemList.Rows[0].Cells["SGSTAMOUNT"].Value;
string sgstrate = sgstrateobj.ISValidObject() ? "" + sgstrateobj.ToString() + "" : "NULL";
string sgstamount = sgstamountobj.ISValidObject() ? "" + sgstamountobj.ToString() + "" : "NULL";
object igstrateobj = dgvItemList.Rows[0].Cells["IGSTRATE"].Value;
object igstamountobj = dgvItemList.Rows[0].Cells["IGSTAMOUNT"].Value;
string igstrate = igstrateobj.ISValidObject() ? "" + igstrateobj.ToString() + "" : "NULL";
string igstamount = igstamountobj.ISValidObject() ? "" + igstamountobj.ToString() + "" : "NULL";
object cessrateobj = dgvItemList.Rows[0].Cells["CESSRATE"].Value;
object cessamountobj = dgvItemList.Rows[0].Cells["CESSAMOUNT"].Value;
string cessrate = cessrateobj.ISValidObject() ? "" + cessrateobj.ToString() + "" : "NULL";
string cessamount = cessamountobj.ISValidObject() ? "" + cessamountobj.ToString() + "" : "NULL";
#endregion
string totalgst = lblTotalGstValue.Text.GetDBFormatString();
string totaladvancevalue = lblTotalReceiptVoucherValue.Text.GetDBFormatString();
string description = txtDescription.Text.ISNullOrWhiteSpace() ? "NULL" : "'" + txtDescription.Text.GetDBFormatString() + "'";
string status = "Open";
if (!mOrderid.ISNullOrWhiteSpace())
{
orderno = "'" + lblOrderNo.Text.GetDBFormatString() + "'";
orderdate = "'" + lblOrderate.Text.GetDBFormatString() + "'";
}
string lasttransectionid = transectionid;
string transectiontype = "Advance_Payment";
string mode = "'" + cmbPaymentMethod.Text + "'";
string checkno = cmbPaymentMethod.Text == "Cheque" ? "'" + txtChequeNo.Text.GetDBFormatString() + "'" : "NULL";
string checkdate = cmbPaymentMethod.Text == "Cheque" ? "'" + dtpDateCheque.Text.GetDBFormatString() + "'" : "NULL";
string drledgerid = mLedgerId;
string crledgerid = ((KeyValuePair <string, string>)cmbPaymentAccount.SelectedItem).Key.ToString();
#endregion
#region Query
if (mReceiptNoForEdit.ISNullOrWhiteSpace())
{
mquery = "insert into AdvancePayment(SlNo,PaymentNo," +
"PaymentDate,OrderNo,OrderDate,ReverseCharge,LedgerId,BillingName," +
"BillingAddress,BillingState,BillingStateCode,ShippingName,ShippingAddress," +
"ShippingState,ShippingStateCode,ItemId,ItemName," +
"ComodityCode, Qty, Unit,RATE, TaxValue, CGSTRate, CGSTAmount," +
" SGSTRate, SGSTAmount, IGSTRate, IGSTAmount, CessRate,CessAmount,TotalGst," +
" Total,Description, Status,DueAmount,LastTransecetionID) values(" + mSerialno + ",'" + receptno + "','" + receiptddate + "'," + orderno + "," + orderdate
+ ",'" + rcm + "','" + mLedgerId + "','" + billingname + "','" + billingaddress + "','" + billingstate + "','" + billingstatecode
+ "','" + shippingname + "','" + shippingaddress + "','" + shippingstate + "','" + shippingstatecode
+ "'," + itemid + "," + itemname + "," + comoditicode + "," + qty + "," + unit + "," + rate + "," + taxvalue
+ "," + cgstrate + "," + cgstamount + "," + sgstrate + "," + sgstamount + "," + igstrate + "," + igstamount
+ "," + cessrate + "," + cessamount + "," + totalgst + "," + totaladvancevalue + "," + description + ",'" + status + "'," + totaladvancevalue + ",'" + lasttransectionid + "')";
mlistquery.Add(mquery);
InsertOrUpdateTransection(transectionid, receiptddate, receptno, totaladvancevalue, drledgerid, crledgerid, transectiontype, mode, "NULL", checkno, checkdate);
#region CurrentBalanceUpdate
mlistquery.Add(LedgerStatus.UpdateLedgerStatus(drledgerid, crledgerid, totaladvancevalue, out mquery));//drQuery Add in the ListQuery and Out CrQuery in mQuery
mlistquery.Add(mquery);
#endregion
}
else
{
mquery = "Update AdvancePayment set PaymentDate='" + receiptddate + "',BillingName='" + billingname + "',BillingAddress='" + billingaddress + "',BillingState='" + billingstate + "'," +
"BillingStateCode='" + billingstatecode + "',ShippingName='" + shippingname + "',ShippingAddress='" + shippingaddress + "',ShippingState='" + shippingstate + "',ShippingStateCode='" + shippingstatecode
+ "',ItemId=" + itemid + ",ItemName=" + itemname + ",ComodityCode=" + comoditicode + ", Qty=" + qty + ", Unit=" + unit + ",RATE=" + rate + ", TaxValue=" + taxvalue
+ ", CGSTRate=" + cgstrate + ", CGSTAmount=" + cgstamount + ",SGSTRate=" + sgstrate + ", SGSTAmount=" + sgstamount + ", IGSTRate=" + igstrate + ", IGSTAmount=" + igstamount + ", CessRate=" + cessrate
+ ",CessAmount=" + cessamount + ",TotalGst=" + totalgst + ", Total=" + totaladvancevalue + ",Description=" + description + " where PaymentNo='" + mReceiptNoForEdit + "'";
mlistquery.Add(mquery);
InsertOrUpdateTransection(transectionid, receiptddate, receptno, totaladvancevalue, drledgerid, crledgerid, transectiontype, mode, "NULL", checkno, checkdate);
#region UpdateLedgerStatus
if (!mReceiptNoForEdit.ISNullOrWhiteSpace())
{
#region CurrentBalanceRestore
mlistquery.Add(LedgerStatus.UpdateLedgerStatus(TransectionTools._CRAccountLedgerId, TransectionTools._DRAccountLedgerId, mTotalPreviousPayment.ToString("0.00"), out mquery));//drQuery Add in the ListQuery and Out CrQuery in mQuery
mlistquery.Add(mquery);
#endregion
}
#region CurrentBalanceUpdate
mlistquery.Add(LedgerStatus.UpdateLedgerStatus(drledgerid, crledgerid, totaladvancevalue, out mquery));//drQuery Add in the ListQuery and Out CrQuery in mQuery
mlistquery.Add(mquery);
#endregion
#endregion
}
#endregion
#region Execute
if (SQLHelper.GetInstance().ExecuteTransection(mlistquery, out msg))
{
MessageBox.Show("\"" + totaladvancevalue + "\" receipt successfully genarate..", "Advance Receipt", MessageBoxButtons.OK, MessageBoxIcon.Information);
OtherSettingTools._IsAdvanceReceiptBillgenarate = true;
this.Close();
}
else
{
MessageBox.Show(msg);
}
#endregion
}
private void BillPaid()
{
lstQuery.Clear();
string transectionid = Guid.NewGuid().ToString();
string payeeladgerID = ((KeyValuePair <string, string>)cmbSuppliersName.SelectedItem).Key.ToString();
string paymentDate = dtpBillDate.Text;
string transectiontype = "";
if (mFromWhere == _FromWhere.Purchase_Bill)
{
transectiontype = "Bill_Payment";
}
else if (mFromWhere == _FromWhere.Expense_Bill)
{
transectiontype = "Expense";
}
string paidAmountStr = mTotalPaidAmount.ToString();
string advSlNo = lblSlNo.Text;
string mode = "'" + cmbPaymentMethod.Text + "'";
string bankname = "NULL";
string checkno = cmbPaymentMethod.Text == "Cheque" ? "'" + txtChequeNo.Text.GetDBFormatString() + "'" : "NULL";
string checkdate = cmbPaymentMethod.Text == "Cheque" ? "'" + dtpDateCheque.Text.GetDBFormatString() + "'" : "NULL";
string crledgerid = ((KeyValuePair <string, string>)cmbPaymentAccount.SelectedItem).Key.ToString();
string drledgerid = payeeladgerID;
if (mTotalPaidAmount == 0)
{
crledgerid = LedgerTools._DicCashLedgers.Keys.First();
}
InsertOrUpdateTransection(transectionid, paymentDate, advSlNo, paidAmountStr, drledgerid, crledgerid, transectiontype, mode, bankname, checkno, checkdate);
#region CurrentBalanceUpdate
lstQuery.Add(LedgerStatus.UpdateLedgerStatus(drledgerid, crledgerid, paidAmountStr, out mQuery));//drQuery Add in the ListQuery and Out CrQuery in mQuery
lstQuery.Add(mQuery);
#endregion
//UpdateCurrentBalance(crledgerid, paidAmountStr);
if (mFromWhere == _FromWhere.Purchase_Bill)
{
UpdatePurchaseBillLastTransectionId(transectionid);
}
else if (mFromWhere == _FromWhere.Expense_Bill)
{
UpdateExpenseLastTransectionId(transectionid);
}
UpdateAdvancePaymentLastTransectionId(transectionid);
//query = "Insert into Transection(TransectionID, Date, No, TransectionType, LedgerIdFrom, " +
// "LedgerIdTo, Amount_Dr,Mode, BankName, ChequeNo, ChequeDate, Narration) " +
// "Values('" + transectionID + "','" + paymentDate + "','" + advSlNo + "','" +
// transectiontype + "','" + payeeladgerID + "'," + mTotalPaidAmount + ",'" + mode + "','" +
// bankName + "'," + chequeNo + ",'" + issueDate + "','" + narration + "')";
//lstQuery.Add(query);
#region Execute
if (MessageBox.Show("Are you sure you want to proceed to payment Rs. " + mTotalPaidAmount + " ?", "Expense", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
if (SQLHelper.GetInstance().ExecuteTransection(lstQuery, out msg))
{
MessageBox.Show("Bill payment complete Rs." + lblTotPaymentAmt.Text, "Bill Payment", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Close();
}
}
#endregion
}
private void BillSave()
{
#region Data
mLstQuery.Clear();
string transectionID = Guid.NewGuid().ToString();
string ladgerID = ((KeyValuePair <string, string>)cmbSupplierName.SelectedItem).Key.ToString();
string billNo = txtBillNo.Text.GetDBFormatString();
string billDate = dtpBillDate.Text;
string transectionType = "Expense Bill";
string totAmount = mtotalBillingAmount.ToString();
string billDesctiption = txtBillingDescription.Text.GetDBFormatString();
string slNo = lblSlNo.Text;
string dueDate = dtpDueDate.Text;
string memoNo = txtMamoNumber.Text.GetDBFormatString();
string voucherNo = txtBillNo.Text.GetDBFormatString();
string status = "Open";
bool isSplit = (dgvItemList.Rows.Count > 1) ? true : false;
#endregion
#region Query
if (mExpenceIDForEdit.ISNullOrWhiteSpace())
{
mquery = "Insert into Expense(SlNo, BillNo, BillingDate, LedgerId, DueDate, MemoNo, " +
"Description, TotalAmount, DueAmount, Status, RCM, LastTransectionID) " +
"Values(" + slNo + ",'" + billNo + "','" + billDate + "','" + ladgerID
+ "','" + dueDate + "','" + memoNo + "','" + billDesctiption + "'," + totAmount
+ "," + totAmount + ",'" + status + "','" + mIsRcm + "','" + transectionID + "')";
mLstQuery.Add(mquery);
}
else
{
mquery = "Update Transection LedgerID='" + ladgerID + "', Date='" + billDate + "', Amount=" + totAmount + ", " +
"Purpose='" + billDesctiption + "', DueDate='" + dueDate + "', MamoNo='" + memoNo + "', IsSplit='" +
isSplit + "',VoucherNo='" + voucherNo + "' where TransectionID='" + mExpenceIDForEdit + "'";
mLstQuery.Add(mquery);
}
#endregion
///Expense Details
foreach (DataGridViewRow row in dgvItemList.Rows)
{
transectionID = Guid.NewGuid().ToString();
string description = row.Cells["Description"].Value.ToString().GetDBFormatString();
string accountHeadID = row.Cells["AccountHeadId"].Value.ToString();
string amount = row.Cells["Amount"].Value.ToString();
mquery = "Insert into ExpenseDetails(SlNo, LedgerID, Description, Amount, TransectionID)" +
"Values(" + slNo + ",'" + ladgerID + "','" + description + "'," + amount
+ ",'" + transectionID + "')";
mLstQuery.Add(mquery);
string draccount = accountHeadID;
string craccount = ladgerID;
string transectiontype = "Expense_Bill";
InsertOrUpdateTransection(transectionID, billDate, billNo, amount, draccount, craccount, transectiontype, "NULL", "NULL", "NULL", "NULL");
#region CurrentBalanceUpdate
//mlistQuery.Add(LedgerStatus.UpdateLedgerStatus(drledgerid, crledgerid, mTotalAmount.ToString("0.00"), out mQuery));//drQuery Add in the ListQuery and Out CrQuery in mQuery
mLstQuery.Add(LedgerStatus.UpdateLedgerStatus(draccount, craccount, amount, out mquery));//drQuery Add in the ListQuery and Out CrQuery in mQuery
mLstQuery.Add(mquery);
#endregion
}
#region Execute
if (SQLHelper.GetInstance().ExecuteTransection(mLstQuery, out msg))
{
MessageBox.Show("Bill Rs. " + mtotalBillingAmount + " saved.", "Bill Entry", MessageBoxButtons.OK, MessageBoxIcon.Information);
if (mExpenceIDForEdit.ISNullOrWhiteSpace())
{
ResetData();
cmbSupplierName.SelectedIndex = -1;
cmbSupplierName.Select();
}
else
{
this.Close();
}
}
#endregion
}