private void AdvancePaymentDataRetrive()
{
string query = "select * from AdvancePayment where PaymentNo='" + mReceiptNoForEdit + "'";
DataTable dt = SQLHelper.GetInstance().ExcuteNonQuery(query, out msg);
if (dt.IsValidDataTable())
{
lblreceiptNo.Text = mReceiptNoForEdit;
string ledgerid = dt.Rows[0]["LedgerId"].ToString();
mLedgerId = ledgerid;
string suppliername = "";
Supplier._DicSuppliers.TryGetValue(ledgerid, out suppliername);
lblSupplierName.Text = suppliername;
dtpAdvPaymentDate.Text = dt.Rows[0]["PaymentDate"].ToString();
string orderno = dt.Rows[0]["OrderNo"].ToString();
lblOrderNo.Text = orderno;
mOrderNofromAdvReceipt = orderno;
string orderDate = dt.Rows[0]["OrderDate"].ToString();
lblOrderate.Text = orderDate.ISNullOrWhiteSpace() ? "" : DateTime.Parse(orderDate).ToString("dd-MMM-yyyy");
string itemId = dt.Rows[0]["ItemId"].ToString();
string itemName = dt.Rows[0]["ItemName"].ToString();
string comodityCode = dt.Rows[0]["ComodityCode"].ToString();
string qty = dt.Rows[0]["Qty"].ToString();
string unit = dt.Rows[0]["Unit"].ToString();
string rate = dt.Rows[0]["Rate"].ToString();
string taxValue = dt.Rows[0]["TaxValue"].ToString();
mcgstrate = dt.Rows[0]["CGSTRate"].ToString();
string cGSTAmount = dt.Rows[0]["CGSTAmount"].ToString();
msgstrate = dt.Rows[0]["SGSTRate"].ToString();
string sGSTAmount = dt.Rows[0]["SGSTAmount"].ToString();
migstrate = dt.Rows[0]["IGSTRate"].ToString();
string iGSTAmount = dt.Rows[0]["IGSTAmount"].ToString();
mcessrate = dt.Rows[0]["CessRate"].ToString();
string cessAmount = dt.Rows[0]["CessAmount"].ToString();
string totalGst = dt.Rows[0]["TotalGst"].ToString();
txtDescription.Text = dt.Rows[0]["Description"].ToString();
string total = dt.Rows[0]["Total"].ToString();
if (orderno.ISNullOrWhiteSpace())
{
pnlorder.Hide();
pnlGst.Show();
double cgstrate = 0, sgstrate = 0;
double.TryParse(mcgstrate, out cgstrate);
double.TryParse(msgstrate, out sgstrate);
cmbGstRate.Text = !migstrate.ISNullOrWhiteSpace() ? migstrate : (cgstrate + sgstrate).ToString();
}
lblTotaltaxbleValue.Text = taxValue;
lblTotalGstValue.Text = totalGst;
lblTotalReceiptVoucherValue.Text = total;
txtAdvanceAmount.Text = total;
GetCustomerAddressDetails(ledgerid);
GetCustomerShippedDetails(ledgerid);
mTransectionID = dt.Rows[0]["LastTransecetionID"].ToString();
mTotalPreviousPayment = double.Parse(dt.Rows[0]["Total"].ToString());
TransectionTools.GetPaymentDetailsId(mTransectionID);
cmbPaymentMethod.Text = TransectionTools._PaymentMethod;
cmbPaymentAccount.Text = TransectionTools._CRAccountTemplateName;
txtChequeNo.Text = TransectionTools._ChequeNo;
dtpDateCheque.Text = TransectionTools._ChequeDate;
cmbPaymentAccount_SelectedIndexChanged(null, null);
dgvItemList.Rows.Clear();
dgvItemList.Rows.Add(1, itemId, itemName, comodityCode, qty, unit, rate, "", "", "", taxValue, mcgstrate, cGSTAmount, msgstrate, sGSTAmount, migstrate, iGSTAmount
, mcessrate, cessAmount, total);
}
}
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 ShowStatement(string query)
{
dgvStatement.Rows.Clear();
DataTable dt = SQLHelper.GetInstance().ExcuteNonQuery(query, out msg);
///Set Opening
#region Set Opening
DateTime openingDate;
DateTime.TryParse(mFromDate, out openingDate);
openingDate = openingDate.AddDays(-1);
mOpeningBalance = GetLedgersOpeningBalanceByDate(openingDate.ToString("dd-MMM-yyyy"));
double closing = mOpeningBalance;
int slno = 1;
double totDebit = 0d, totCredit = 0d;
if (mOpeningBalance > 0)
{
totDebit += mOpeningBalance;
dgvStatement.Rows.Add("", slno++, mFromDate, "To Opening Balance", "", "",
"", "", mOpeningBalance.toString(), "", mOpeningBalance.toString());
}
else if (mOpeningBalance < 0)
{
totCredit += mOpeningBalance;
dgvStatement.Rows.Add("", slno++, mFromDate, "To Opening Balance", "", "",
"", "", "", Math.Abs(mOpeningBalance).toString(), mOpeningBalance.toString());
}
#endregion
if (dt.IsValidDataTable())
{
foreach (DataRow row in dt.Rows)
{
double debit = 0d, credit = 0d;
string date = row["Date"].ToString();
string voucherType = row["TransectionType"].ToString();
string voucherNo = row["No"].ToString();
string ledgerId = row["LedgerIdFrom"].ToString();
string ledgerName = row["TemplateName"].ToString();
object amountDr = row["Amount_Dr"];
bool flag = amountDr.ISValidObject() ? double.TryParse(amountDr.ToString(), out debit) : false;
object amountCr = row["Amount_Cr"];
flag = amountCr.ISValidObject() ? double.TryParse(amountCr.ToString(), out credit) : false;
object transectionMode = row["Mode"];
object chkNo = row["ChequeNo"];
closing = (closing + debit) - credit;
totDebit += debit;
totCredit += credit;
dgvStatement.Rows.Add(ledgerId, slno++, date, ledgerName, voucherType, voucherNo,
transectionMode, chkNo, amountDr.toRound(), amountCr.toRound(), closing.toString());
}
}
#region Closing
lblDebit.Text = totDebit.ToString("0.00");
lblCredit.Text = totCredit.ToString("0.00");
lblBalanceDate.Text = mToDate;
if (closing >= 0)
{
lblDebitBalance.Text = closing.ToString("0.00");
}
else
{
lblCreditBalance.Text = Math.Abs(closing).ToString("0.00");
}
#endregion
}
private void PurchaseDetails(string startDate, string endDate)
{
#region VARIABLE
double mAllTotal = 0d, mAllGstAll = 0d, mAllTaxibleValue = 0d,
//**********CGST,SGST**************
mAllCGST_2_5 = 0d, mAllSGST_2_5 = 0d,
mAllCGST_6 = 0d, mAllSGST_6 = 0d,
mAllCGST_9 = 0d, mAllSGST_9 = 0d,
mAllCGST_14 = 0d, mAllSGST_14 = 0d;
//***************IGST**************
double mAllIGST_5 = 0d, mAllIGST_12 = 0d,
mAllIGST_18 = 0d, mAllIGST_28 = 0d;
//***************CESS**************
double mAllCESS = 0d;
#endregion
DataTable dt = new DataTable();
_Dt.Rows.Clear();
int i = 0;
#region QUERY
string Query = "select PurchaseBill.LedgerId,LadgerMain.LadgerName,LadgerMain.GSTIN,Convert(varchar(11),InvoiceDate,106) as InvoiceDate,InvoiceNo,Sum(PurchaseBillDetails.TaxAmount) as TaxableAmount " +
//% WISE TOTAL CGST,SGST
", (a.CGSTAmount_2_5) as CGSTAmount_2_5, (a.SGSTAmount_2_5) as SGSTAmount_2_5 " +
", (b.CGSTAmount_6) as CGSTAmount_6, (b.SGSTAmount_6) as SGSTAmount_6 " +
", (c.CGSTAmount_9) as CGSTAmount_9, (c.SGSTAmount_9) as SGSTAmount_9 " +
", (d.CGSTAmount_14) as CGSTAmount_14, (d.SGSTAmount_14) as SGSTAmount_14, " +
//% WISE TOTAL IGST
"(I_5.IGSTAmount_5) as IGSTAmount_5, (I_12.IGSTAmount_12) as IGSTAmount_12, (I_18.IGSTAmount_18) as IGSTAmount_18, " +
"(I_28.IGSTAmount_28) as IGSTAmount_28, " +
//% WISE TOTAL CESS
"(Cess.CessAmount) as CessAmount, " +
//TOTAL GST(CGST+SGST+IGST )
"(ISNULL((CGSTAmount_2_5), 0) + ISNULL((SGSTAmount_2_5), 0) " +
"+ ISNULL((CGSTAmount_6), 0) + ISNULL((SGSTAmount_6), 0) + ISNULL((CGSTAmount_9), 0) " +
"+ ISNULL((SGSTAmount_9), 0) + ISNULL((CGSTAmount_14), 0) + ISNULL((SGSTAmount_14), 0)) as TotalGST, " +
//
"ISNULL((IGSTAmount_5), 0),ISNULL((IGSTAmount_12), 0),ISNULL((IGSTAmount_18), 0),ISNULL((IGSTAmount_28), 0), " +
//TOTAL
" ISNULL(SUM(PurchaseBillDetails.Total), 0) as Total " +
"from PurchaseBill " +
"inner join PurchaseBillDetails on PurchaseBill.BillID = PurchaseBillDetails.Billid " +
"inner join LadgerMain on LadgerMain.LadgerID = PurchaseBill.LedgerId " +
//jOINING RESULT FOR CGST,SGST
"left join (select Billid, Sum(CGSTAmount) as CGSTAmount_2_5, Sum(SGSTAmount) as SGSTAmount_2_5 from PurchaseBillDetails where CGSTRate = 2.5 and SGSTRate = 2.5 group by Billid) as a on a.Billid = PurchaseBill.BillID " +
"left join (select Billid, Sum(CGSTAmount) as CGSTAmount_6, Sum(SGSTAmount) as SGSTAmount_6 from PurchaseBillDetails where CGSTRate = 6 and SGSTRate = 6 group by Billid) as b on b.Billid = PurchaseBill.BillID " +
"left join (select Billid, Sum(CGSTAmount) as CGSTAmount_9, Sum(SGSTAmount) as SGSTAmount_9 from PurchaseBillDetails where CGSTRate = 9 and SGSTRate = 9 group by Billid) as c on c.Billid = PurchaseBill.BillID " +
"left join (select Billid, Sum(CGSTAmount) as CGSTAmount_14, Sum(SGSTAmount) as SGSTAmount_14 from PurchaseBillDetails where CGSTRate = 14 and SGSTRate = 14 group by Billid) as d on d.Billid = PurchaseBill.BillID " +
//JOINING RESULT FOR IGST
"left join(select Billid, Sum(IGSTAmount) as IGSTAmount_5 from PurchaseBillDetails where IGSTRate = 5 group by Billid) as I_5 on I_5.Billid = PurchaseBill.BillID " +
"left join (select Billid, Sum(IGSTAmount) as IGSTAmount_12 from PurchaseBillDetails where IGSTRate = 12 group by Billid) as I_12 on I_12.Billid = PurchaseBill.BillID " +
"left join (select Billid, Sum(IGSTAmount) as IGSTAmount_18 from PurchaseBillDetails where IGSTRate = 18 group by Billid) as I_18 on I_18.Billid = PurchaseBill.BillID " +
"left join (select Billid, Sum(IGSTAmount) as IGSTAmount_28 from PurchaseBillDetails where IGSTRate = 28 group by Billid) as I_28 on I_28.Billid = PurchaseBill.BillID " +
//JOINING RESULT FOR CESS
"left join(select Billid, Sum(CeassAmount) as CessAmount from PurchaseBillDetails group by Billid) as Cess on Cess.Billid = PurchaseBill.BillID " +
//GROUP BY CLOUSE
"Where InvoiceDate BETWEEN '" + startDate + "' AND '" + endDate + "' and Status<>'Cancel' " +
"Group by PurchaseBill.LedgerId,LadgerMain.LadgerName,LadgerMain.GSTIN,InvoiceDate,InvoiceNo, " +
"CGSTAmount_2_5, SGSTAmount_2_5, " +
"CGSTAmount_6, SGSTAmount_6, CGSTAmount_9, " +
"SGSTAmount_9, CGSTAmount_14, SGSTAmount_14, " +
"IGSTAmount_5, IGSTAmount_12, " +
"IGSTAmount_18, IGSTAmount_28, " +
"CessAmount " +
" order by InvoiceDate,InvoiceNo";
#endregion
dt = SQLHelper.GetInstance().ExcuteNonQuery(Query, out msg);
if (dt.IsValidDataTable())
{
//***************maximum length of Progress Ber*******************
progressBar1.Maximum = dt.Rows.Count;
foreach (DataRow item in dt.Rows)
{
//**************Increase ProgressBer Value********************
progressBar1.Value = ++MethodExecutioncount;
#region LOCAL vARIABLE
//***************CGST,SGST**************
double Total = 0d, GstAll = 0d, TaxibleValue = 0d,
CGST_2_5 = 0d, SGST_2_5 = 0d,
CGST_6 = 0d, SGST_6 = 0d,
CGST_9 = 0d, SGST_9 = 0d,
CGST_14 = 0d, SGST_14 = 0d;
//***************IGST**************
double IGST_5 = 0d, IGST_12 = 0d,
IGST_18 = 0d, IGST_28 = 0d;
//***************CESS**************
double CESS = 0d;
#endregion
#region VALUE INITIALIZE FROM DATATABLE
//***************TOTAL**************
double.TryParse(item["Total"].ToString(), out Total);
double.TryParse(item["TotalGst"].ToString(), out GstAll);
double.TryParse(item["TaxableAmount"].ToString(), out TaxibleValue);
//***************CGST,SGST**************
double.TryParse(item["CGSTAmount_2_5"].ToString(), out CGST_2_5);
double.TryParse(item["SGSTAmount_2_5"].ToString(), out SGST_2_5);
double.TryParse(item["CGSTAmount_6"].ToString(), out CGST_6);
double.TryParse(item["SGSTAmount_6"].ToString(), out SGST_6);
double.TryParse(item["CGSTAmount_9"].ToString(), out CGST_9);
double.TryParse(item["SGSTAmount_9"].ToString(), out SGST_9);
double.TryParse(item["CGSTAmount_14"].ToString(), out CGST_14);
double.TryParse(item["SGSTAmount_14"].ToString(), out SGST_14);
//***************IGST**************
double.TryParse(item["IGSTAmount_5"].ToString(), out IGST_5);
double.TryParse(item["IGSTAmount_12"].ToString(), out IGST_12);
double.TryParse(item["IGSTAmount_18"].ToString(), out IGST_18);
double.TryParse(item["IGSTAmount_28"].ToString(), out IGST_28);
//***************CESS**************
double.TryParse(item["CESSAMOUNT"].ToString(), out CESS);
#endregion
#region DATATABLE ROW ADD
//*********************************DATATABLE ROW ADD************************
_Dt.Rows.Add(++i, item["InvoiceDate"], item["LadgerName"], item["GSTIN"], item["InvoiceNo"],
TaxibleValue, CGST_2_5, SGST_2_5, IGST_5, CGST_6, SGST_6, IGST_12, CGST_9, SGST_9, IGST_18, CGST_14, SGST_14, IGST_28, CESS, GstAll, Total);
#endregion
#region tOTAL CALCULATE
//**********Total Calculate*********************
mAllTotal += Total;
mAllGstAll += GstAll;
mAllTaxibleValue += TaxibleValue;
//**CGST,SGST**
mAllCGST_2_5 += CGST_2_5;
mAllSGST_2_5 += SGST_2_5;
mAllCGST_6 += CGST_6;
mAllSGST_6 += SGST_6;
mAllCGST_9 += CGST_9;
mAllSGST_9 += SGST_9;
mAllCGST_14 += CGST_14;
mAllSGST_14 += SGST_14;
//****IGST****
mAllIGST_5 += IGST_5;
mAllIGST_12 += IGST_12;
mAllIGST_18 += IGST_18;
mAllIGST_28 += IGST_28;
//****CESS****
mAllCESS += CESS;
#endregion
}
}
#region TOTAL VALUE ADD IN LAST POSITION ROW
//*******************"ALL TOTAL" ROW ADD IN LAST POSITION ****************
_Dt.Rows.Add();
_Dt.Rows[_Dt.Rows.Count - 1][0] = "TOTAL";
_Dt.Rows[_Dt.Rows.Count - 1][5] = mAllTaxibleValue.ToString("0.00");
_Dt.Rows[_Dt.Rows.Count - 1][6] = mAllCGST_2_5.ToString("0.00");
_Dt.Rows[_Dt.Rows.Count - 1][7] = mAllSGST_2_5.ToString("0.00");
_Dt.Rows[_Dt.Rows.Count - 1][8] = mAllIGST_5.ToString("0.00");
_Dt.Rows[_Dt.Rows.Count - 1][9] = mAllCGST_6.ToString("0.00");
_Dt.Rows[_Dt.Rows.Count - 1][10] = mAllSGST_6.ToString("0.00");
_Dt.Rows[_Dt.Rows.Count - 1][11] = mAllIGST_12.ToString("0.00");
_Dt.Rows[_Dt.Rows.Count - 1][12] = mAllCGST_9.ToString("0.00");
_Dt.Rows[_Dt.Rows.Count - 1][13] = mAllSGST_9.ToString("0.00");
_Dt.Rows[_Dt.Rows.Count - 1][14] = mAllIGST_18.ToString("0.00");
_Dt.Rows[_Dt.Rows.Count - 1][15] = mAllCGST_14.ToString("0.00");
_Dt.Rows[_Dt.Rows.Count - 1][16] = mAllSGST_14.ToString("0.00");
_Dt.Rows[_Dt.Rows.Count - 1][17] = mAllIGST_28.ToString("0.00");
_Dt.Rows[_Dt.Rows.Count - 1][18] = mAllCESS.ToString("0.00");
_Dt.Rows[_Dt.Rows.Count - 1][19] = mAllGstAll.ToString("0.00");
_Dt.Rows[_Dt.Rows.Count - 1][20] = mAllTotal.ToString("0.00");
#endregion
//*******************PROGRESSBER ****************
progressBar1.Value = 0;
MethodExecutioncount = 0;
}
public void ShowData()
{
string query = "Select OrganizationDetails.*,OrganizationAddress.*,State.StateName from OrganizationDetails" +
" inner join state on OrganizationDetails.StateID = State.ID" +
" inner join OrganizationAddress on OrganizationDetails.OrganizationName = OrganizationAddress.OrganizationName";
DataTable dt = SQLHelper.GetInstance().ExcuteNonQuery(query, out msg);
if (dt.IsValidDataTable())
{
mIsUpdate = true;
txtOrgName.Text = dt.Rows[0]["OrganizationName"].ToString();
txtAddress.Text = dt.Rows[0]["Address"].ToString();
txtCity.Text = dt.Rows[0]["CityTown"].ToString();
string StateId = dt.Rows[0]["StateId"].ToString();
cmbState.Text = GetSateName(StateId);
cmbDist.Text = dt.Rows[0]["Dist"].ToString();
txtPIN.Text = dt.Rows[0]["PIN"].ToString();
txtRegionalAdDress.Text = dt.Rows[0]["AddressRegional"].ToString();
txtRegionalCity.Text = dt.Rows[0]["CityTownRegional"].ToString();
string stateRegionalId = dt.Rows[0]["SateRegionalID"].ToString();
cmbRegionalState.Text = GetSateName(stateRegionalId);
cmbRegionalDist.Text = dt.Rows[0]["DistRegional"].ToString();
txtRegionalPIN.Text = dt.Rows[0]["PinRegional"].ToString();
txtCIN.Text = dt.Rows[0]["CorporateNo"].ToString();
txtPAN.Text = dt.Rows[0]["PAN"].ToString();
txtTAN.Text = dt.Rows[0]["TIN"].ToString();
txtGST.Text = dt.Rows[0]["GSTin"].ToString();
string gstCategory = dt.Rows[0]["GSTtype"].ToString();
if (gstCategory.ISNullOrWhiteSpace())
{
pnlGstInformation.Enabled = true;
}
else
{
pnlGstInformation.Enabled = false;
}
cmbGSTCatagory.Text = dt.Rows[0]["GSTtype"].ToString();
cmbCompositionType.Text = dt.Rows[0]["CompositionType"].ToString();
lblPercentage.Text = dt.Rows[0]["CompositionPercentage"].ToString();
txtContctNo.Text = dt.Rows[0]["ContactNo1"].ToString();
txtAlternativNo.Text = dt.Rows[0]["AlternativeNo"].ToString();
txtEmail.Text = dt.Rows[0]["Email"].ToString();
txtWebSite.Text = dt.Rows[0]["website"].ToString();
cmbBusinessCategory.Text = dt.Rows[0]["BusinessCatagory"].ToString();
txtShippingNAme.Text = dt.Rows[0]["ShippingName"].ToString();
txtContactShiping.Text = dt.Rows[0]["ShippingContactNo"].ToString();
txtAddressShipping.Text = dt.Rows[0]["ShippingAddress"].ToString();
txtCityTownShipping.Text = dt.Rows[0]["ShippingTown"].ToString();
cmbStateShipping.Text = dt.Rows[0]["ShippingState"].ToString();
cmbDistShipping.Text = dt.Rows[0]["ShippingDist"].ToString();
txtPinShipping.Text = dt.Rows[0]["ShippingPIN"].ToString();
txtNameBilling.Text = dt.Rows[0]["BillingName"].ToString();
txtAddressBilling.Text = dt.Rows[0]["BillingAddress"].ToString();
txtCityTownBilling.Text = dt.Rows[0]["BillingTown"].ToString();
cmbStateBilling.Text = dt.Rows[0]["BillingState"].ToString();
cmbDistBilling.Text = dt.Rows[0]["BillingDist"].ToString();
txtPinBilling.Text = dt.Rows[0]["BillingPIN"].ToString();
txtcontactOfBilling.Text = dt.Rows[0]["ContactNo1"].ToString();
ShowImage();
}
}
private void DataSave()
{
string orgName = txtOrgName.Text.GetDBFormatString();
string address = txtAddress.Text.GetDBFormatString();
string city = txtCity.Text.GetDBFormatString();
string district = ((KeyValuePair <string, string>)cmbDist.SelectedItem).Value.ToString();
string stateID = ((KeyValuePair <string, string>)cmbState.SelectedItem).Key.ToString();
string pin = txtPIN.Text.GetDBFormatString();
string tin = txtTAN.Text.GetDBFormatString();
string addressREGional = txtRegionalAdDress.Text.GetDBFormatString();
string cityREGional = txtRegionalCity.Text.GetDBFormatString();
string districtREGional = (!cmbRegionalDist.Text.ISNullOrWhiteSpace() ? ((KeyValuePair <string, string>)cmbRegionalDist.SelectedItem).Value.ToString() : "");
string stateIDREGional = (!cmbRegionalState.Text.ISNullOrWhiteSpace() ? ((KeyValuePair <string, string>)cmbRegionalState.SelectedItem).Key.ToString() : "");
string pinREGional = txtRegionalPIN.Text.GetDBFormatString();
string PAN = txtPAN.Text.GetDBFormatString();
string gstCategory = cmbGSTCatagory.Text.GetDBFormatString();
string gstNo = txtGST.Text.GetDBFormatString();
string contctNo = txtContctNo.Text.GetDBFormatString();
string alterContctNo = txtAlternativNo.Text.GetDBFormatString();
string email = txtEmail.Text.GetDBFormatString();
string website = txtWebSite.Text.GetDBFormatString();
string cin = txtCIN.Text.GetDBFormatString();
string businessCategory = cmbBusinessCategory.Text;
string nameShipping = txtShippingNAme.Text.GetDBFormatString();
string contctShipping = txtContactShiping.Text.GetDBFormatString();
string addressShipping = txtAddressShipping.Text.GetDBFormatString();
string cityShipping = txtCityTownShipping.Text.GetDBFormatString();
string districtShipping = (!cmbDistShipping.Text.ISNullOrWhiteSpace() ? ((KeyValuePair <string, string>)cmbDistShipping.SelectedItem).Value.ToString() : "");
string stateIDShipping = (!cmbStateShipping.Text.ISNullOrWhiteSpace() ? ((KeyValuePair <string, string>)cmbStateShipping.SelectedItem).Value.ToString() : "");
string pinShipping = txtPinShipping.Text.GetDBFormatString();
string nameBilling = txtNameBilling.Text.GetDBFormatString();
string addressBilling = txtAddressBilling.Text.GetDBFormatString();
string cityBilling = txtCityTownBilling.Text.GetDBFormatString();
string districtBilling = (!cmbDistBilling.Text.ISNullOrWhiteSpace() ? ((KeyValuePair <string, string>)cmbDistBilling.SelectedItem).Value.ToString() : "");
string stateIDBilling = (!cmbStateBilling.Text.ISNullOrWhiteSpace() ? ((KeyValuePair <string, string>)cmbStateBilling.SelectedItem).Value.ToString() : "");
string pinBilling = txtPinBilling.Text.GetDBFormatString();
string compositiontype = "NULL";
string percentage = "NULL";
string delecration = "NULL";
if (gstCategory == "Composition")
{
compositiontype = "'" + cmbCompositionType.Text.GetDBFormatString() + "'";
percentage = "" + lblPercentage.Text.GetDBFormatString() + "";
delecration = "'" + txtCompositeDeclaration.Text.GetDBFormatString() + "'";
}
string query = "";
if (mIsUpdate)
{
query = "Update OrganizationDetails set OrganizationName='" + orgName + "',Address='" + address
+ "',CityTown='" + city + "',Dist='" + district + "',stateID=" + stateID + ",PIN='" + pin
+ "',PAN='" + PAN + "',CorporateNo='" + cin + "',GSTtype='" + gstCategory + "',GSTin='" + gstNo
+ "',ContactNo1='" + contctNo + "',AlternativeNo='" + alterContctNo + "',Email='" + email
+ "',website='" + website + "',TIN='" + tin + "',AddressRegional='" + addressREGional +
"',CityTownRegional='" + cityREGional + "',DistRegional='" + districtREGional
+ "',SateRegionalID='" + stateIDREGional + "',PinRegional='" + pinREGional + "',BusinessCatagory='" + businessCategory + "',CompositionType=" + compositiontype + ",CompositionPercentage=" + percentage + ",CompositionDeclaration=" + delecration + "";
mlstQry.Add(query);
query = "update OrganizationAddress set OrganizationName='" + orgName + "',BillingName='" + nameBilling +
"',BillingAddress='" + addressBilling + "',BillingTown='" + cityBilling + "',BillingDist='" + districtBilling + "',BillingState='" + stateIDBilling +
"',BillingPIN='" + pinBilling + "',ShippingName ='" + nameShipping + "',ShippingAddress='" + addressShipping + "',ShippingTown='" + cityShipping + "',ShippingDist='" + districtShipping + "',ShippingState='" + stateIDShipping + "',ShippingPIN='" + pinShipping + "',ShippingContactNo= '" + contctShipping + "'";
mlstQry.Add(query);
}
else
{
query = "Insert into OrganizationDetails(OrganizationName, Address, CityTown, Dist, StateID, " +
"PIN, PAN, CorporateNo, GSTtype,GSTin, ContactNo1, AlternativeNo, Email, website,TIN,AddressRegional, " +
"CityTownRegional,DistRegional,SateRegionalID,PinRegional,BusinessCatagory,CompositionType,CompositionPercentage,CompositionDeclaration) " +
"values('" + orgName + "','" + address + "','" + city + "','" + district + "'," + stateID
+ ",'" + pin + "','" + PAN + "','" + cin + "','" + gstCategory + "','" + gstNo + "','" + contctNo
+ "','" + alterContctNo + "','" + email + "','" + website + "','" + tin + "','" + addressREGional +
"','" + cityREGional + "','" + districtREGional + "'," + stateIDREGional + ",'" + pinREGional + "','" + businessCategory + "'," + compositiontype + "," + percentage + "," + delecration + ")";
mlstQry.Add(query);
query = "insert into OrganizationAddress(OrganizationName,BillingName,BillingAddress,BillingTown,BillingDist,BillingState,BillingPIN,ShippingName,ShippingAddress,ShippingTown,ShippingDist,ShippingState,ShippingPIN,ShippingContactNo) " +
"values('" + orgName + "','" + nameBilling + "','" + addressBilling + "','" + cityBilling + "','" + districtBilling + "','" + stateIDBilling + "','" + pinBilling
+ "','" + nameShipping + "','" + addressShipping + "','" + cityShipping + "','" + districtShipping + "','" + stateIDShipping + "','" + pinShipping + "','" + contctShipping + "')";
mlstQry.Add(query);
}
if (SQLHelper.GetInstance().ExecuteTransection(mlstQry, out msg))
{
UpdateLogo();
ORG_Tools.InitDetails();
if (mIsFistTime)
{
this.Close();
}
else
{
MessageBox.Show("Save data.", "Organization Details", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
