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 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 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 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
}
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
}
/// <summary>
/// LedgerMaib Table Update & Data Update
/// </summary>
public static void LedgerMainTableUpdate()
{
int counttable = 0;
#region Remove then Add COLUMNS
string query = "Select COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, " +
"NUMERIC_PRECISION, DATETIME_PRECISION, " +
"IS_NULLABLE from INFORMATION_SCHEMA.COLUMNS " +
"where TABLE_NAME='LadgerMain' and COLUMN_NAME = 'SubGroup'";
if (SQLHelper.GetInstance().ExcuteScalar(query, out msg).ISValidObject())
{
counttable = 1;
mquery = "ALTER TABLE LadgerMain DROP COLUMN SubGroup,Under,GroupName";
mListQuery.Add(mquery);
mquery = "ALTER TABLE LadgerMain ADD SubAccount varchar(50) NULL, " +
"ParentAccount varchar(50) NULL, " +
"Type varchar(50) NULL";
mListQuery.Add(mquery);
#region Update Data
mquery = "Update LadgerMain set SubAccount='Sundry Debtors',ParentAccount='Current Assets' " +
"where Category='Customer'";
mListQuery.Add(mquery);
mquery = "Update LadgerMain set SubAccount='Sundry Creditors',ParentAccount='Current Liability' " +
"where Category='Supplier'";
mListQuery.Add(mquery);
mquery = "Update LadgerMain set SubAccount='Sales A/C',ParentAccount='Parent',Type='Income' " +
"where Category='Sales'";
mListQuery.Add(mquery);
mquery = "Update LadgerMain set SubAccount='Bank A/C',ParentAccount='Current Assets' " +
"where Category='Bank'";
mListQuery.Add(mquery);
mquery = "Update LadgerMain set SubAccount='Sales Return',ParentAccount='Sales A/C' " +
"where Category='Sales_Return'";
mListQuery.Add(mquery);
mquery = "Update LadgerMain set SubAccount='Purchase A/C',ParentAccount='Parent',Type='Expense' " +
"where Category='Purchase'";
mListQuery.Add(mquery);
mquery = "Update LadgerMain set SubAccount='Purchase Retun',ParentAccount='Purchase A/C' " +
"where Category='Purchase_Return'";
mListQuery.Add(mquery);
mquery = "Update LadgerMain set SubAccount='Cash A/C',ParentAccount='Current Assets' " +
"where Category='Cash'";
mListQuery.Add(mquery);
#endregion
}
#endregion
///Update Cash & Bank Ledgers SubAccount
mquery = "Update LadgerMain set SubAccount='Cash A/C' where Category='Cash'";
mListQuery.Add(mquery);
mquery = "Update LadgerMain set SubAccount='Bank A/C' where Category='Bank'";
mListQuery.Add(mquery);
#region Change Columns MAXLENGTH
query = "select COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH," +
"NUMERIC_PRECISION, DATETIME_PRECISION," +
" IS_NULLABLE from INFORMATION_SCHEMA.COLUMNS" +
" where TABLE_NAME='LadgerMain' and COLUMN_NAME = 'TemplateName'" +
" and CHARACTER_MAXIMUM_LENGTH='300'";
if (!SQLHelper.GetInstance().ExcuteScalar(query, out msg).ISValidObject())
{
counttable = 1;
mquery = "ALTER TABLE LadgerMain ALTER COLUMN TemplateName VARCHAR (300)";
mListQuery.Add(mquery);
}
#endregion
count = counttable == 0 ? count : count + 1;
}
private void GenerateOrderList()
{
mDtTable.Clear();
int i = 0;
string query = "SELECT PurchaseOrder.DeliveryDate,PurchaseOrder.OrderID,StatusForchallan,Estimateno,PurchaseOrderNo, convert(varchar(11),PurchaseOrder.OrderDate,106) as Date, " +
"PurchaseOrder.Description, PurchaseOrder.SlNo,Ledgers. LedgerName,PurchaseOrder.Status " +
"FROM PurchaseOrder inner join Ledgers on PurchaseOrder.LedgerID=Ledgers.LedgerID where PurchaseOrder.OrderDate between '" +
mFromDate + "' and '" + mToDate + "' order by PurchaseOrder.OrderDate,PurchaseOrder.PurchaseOrderNo desc ";
DataTable dt = SQLHelper.GetInstance().ExcuteNonQuery(query, out msg);
if (dt.IsValidDataTable())
{
foreach (DataRow item in dt.Rows)
{
string deliverydatestr = !item["DeliveryDate"].ToString().ISNullOrWhiteSpace() ? DateTime.Parse(item["DeliveryDate"].ToString()).ToString("dd-MMM-yyyy") : "";
string orderID = item["OrderID"].ToString();
string orderNo = item["PurchaseOrderNo"].ToString();
string estimateno = item["Estimateno"].ToString();
string orderDate = item["Date"].ToString();
string partyName = item["LedgerName"].ToString();
string status = item["Status"].ToString();
string statusforChallan = item["StatusForchallan"].ToString();
mDtTable.Rows.Add(orderID, orderDate, orderNo, estimateno, partyName, deliverydatestr, status, statusforChallan);
#region MyRegion
Color clr = new Color();
clr = Color.Black;
if (status == "Open")
{
DataGridViewComboBoxCell cmbCell = new DataGridViewComboBoxCell();
cmbCell.DisplayStyle = DataGridViewComboBoxDisplayStyle.DropDownButton;
cmbCell.FlatStyle = FlatStyle.Flat;
if (statusforChallan == "Close")
{
dgvBills.Rows[i].Cells["Action"].ReadOnly = false;
cmbCell.ToolTipText = "Create Bill";
cmbCell.MaxDropDownItems = 3;
cmbCell.Items.Add("Advance Payment");
//cmbCell.Items.Add("Challan");
cmbCell.Items.Add("Bill");
dgvBills.Rows[i].Cells["Action"] = cmbCell;
}
else
{
dgvBills.Rows[i].Cells["Action"].ReadOnly = false;
//cmbCell.ToolTipText = "Create Challan";
cmbCell.MaxDropDownItems = 2;
cmbCell.Items.Add("Advance Payment");
//cmbCell.Items.Add("Challan");
dgvBills.Rows[i].Cells["Action"] = cmbCell;
}
}
else
{
dgvBills.Rows[i].Cells["Action"].ReadOnly = true;
}
#endregion
i++;
}
}
}
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;
}
/// <summary>
/// Inser Any New Table
/// </summary>
public static void InsertNewTable()
{
int counttable = 0;
string query = "";
#region INSERT TABLE AdjustHistory
query = "select COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH," +
"NUMERIC_PRECISION, DATETIME_PRECISION," +
" IS_NULLABLE from INFORMATION_SCHEMA.COLUMNS" +
" where TABLE_NAME='AdjustHistory'";
if (!SQLHelper.GetInstance().ExcuteScalar(query, out msg).ISValidObject())
{
counttable = 1;
mquery = "create table [dbo].[AdjustHistory](Id bigint NOT NULL IDENTITY(1,1)," +
"InVoiceNo varchar(50) NOT NULL,Type varchar(50) NOT NULL ,Cr_Adv_Id" +
" varchar(50) NOT NULL,AdjustAmount float NOT NULL, PRIMARY KEY (ID))";
mListQuery.Add(mquery);
}
#endregion
#region INSERT TABLE DamageProduct
query = "select COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH," +
"NUMERIC_PRECISION, DATETIME_PRECISION," +
" IS_NULLABLE from INFORMATION_SCHEMA.COLUMNS" +
" where TABLE_NAME='DamageProduct'";
if (!SQLHelper.GetInstance().ExcuteScalar(query, out msg).ISValidObject())
{
counttable = 1;
mquery = "create table [dbo].[DamageProduct](ID bigint NOT NULL IDENTITY(1,1)," +
"StockSummaryId bigint NOT NULL ,BatchNo varchar(50) NOT NULL ,ItemID" +
" bigint NOT NULL,DamageType varchar(50) NOT NULL,Reason varchar(50) NULL,MfgDate date NULL," +
" ExpDate date NULL,HighestUnit varchar(50) NULL,HighestDamageQty float NULL,HighestRate float NULL," +
" HighestMRP float NULL,MiddleUnit varchar(50) NULL,MiddleDamageQty float NULL,MiddleRate float NULL," +
" MiddleMRP float NULL,LowestUnit varchar(50) NULL,LowestDamageQty float NULL,LowestRate float NULL," +
" LowestMRP float NULL,HighestMeasureQty float NULL,LowestMeasureQty float NULL,PurchaseQty float NULL," +
" PurchaseRate float NULL,PurchaseUnit varchar(50) NULL,PRIMARY KEY (ID),UNIQUE(StockSummaryId),FOREIGN KEY (StockSummaryId) REFERENCES StockSummary(ID))";
mListQuery.Add(mquery);
}
#endregion
#region INSERT TABLE ToolsTable
query = "select COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH," +
"NUMERIC_PRECISION, DATETIME_PRECISION," +
" IS_NULLABLE from INFORMATION_SCHEMA.COLUMNS" +
" where TABLE_NAME='ToolsTable'";
if (!SQLHelper.GetInstance().ExcuteScalar(query, out msg).ISValidObject())
{
counttable = 1;
mquery = "create table [dbo].[ToolsTable](ID bigint NOT NULL IDENTITY(1,1)," +
"DecimalPlace int NOT NULL ,IsPercentageInMRP bit NOT NULL," +
"CONSTRAINT Pk_ID PRIMARY KEY (ID))";
mListQuery.Add(mquery);
mquery = "insert into ToolsTable(DecimalPlace,IsPercentageInMRP) values(2,'False') ";
mListQuery.Add(mquery);
}
#endregion
#region Create & Data Insert TABLE SubAccount
query = "Select COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH," +
"NUMERIC_PRECISION, DATETIME_PRECISION, " +
"IS_NULLABLE from INFORMATION_SCHEMA.COLUMNS " +
"where TABLE_NAME='SubAccount'";
if (!SQLHelper.GetInstance().ExcuteScalar(query, out msg).ISValidObject())
{
counttable = 1;
query = "CREATE TABLE [dbo].[SubAccount]( " +
"[ID][int] IDENTITY(1, 1) NOT NULL, " +
"[AccountName] [varchar](50) NOT NULL, " +
"[ParentAccount] [varchar](50) NOT NULL, " +
"[Nature] [varchar](50) NULL, " +
"[IsFixed][bit] NULL, " +
"CONSTRAINT[PK_SubAccount] PRIMARY KEY CLUSTERED( [ID] ASC) " +
"WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, " +
"ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON[PRIMARY], " +
"CONSTRAINT[IX_SubAccount] UNIQUE NONCLUSTERED( [AccountName] ASC) " +
"WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, " +
"ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON[PRIMARY] " +
") ON[PRIMARY]";
mListQuery.Add(query);
#region Insert Data
query = "Insert into SubAccount(AccountName,ParentAccount,Nature,IsFixed) " +
"Values('Current Assets','Parent','Assets','True'), " +
"('Current Liability','Parent','Assets','True'), " +
"('Capital A/C','Parent','Liabilities','True'), " +
"('Expense[Direct]','Parent','Expense','True'), " +
"('Expense[Indirect]','Parent','Expense','True'), " +
"('Income[Direct]','Parent','Income','True'), " +
"('Income[Indirect]','Parent','Income','True'), " +
"('Investment','Parent','Assets','True'), " +
"('Loans[Liability]','Parent','Liabilities','True'), " +
"('Purchase A/C','Parent','Expense','True'), " +
"('Sales A/C','Parent','Income','True'), " +
"('Fixed Assets','Parent','Assets','True'), " +
"('Duties & Tax','Current Liability',NULL,'True'), " +
"('Bank A/C','Current Assets',NULL,'True') , " +
"('Bank OD','Loans[Liability]', NULL,'True') , " +
"('Cash A/C','Current Assets',NULL,'True') , " +
"('Load & Advances','Current Assets', NULL,'True') , " +
"('Stock in Hand','Current Assets',NULL,'True') , " +
"('Sundry Creditors','Current Liability', NULL,'True') , " +
"('Sundry Debtors','Current Assets',NULL,'True'), " +
"('Sales Return','Sales A/C', NULL,'True') , " +
"('Purchase Retun','Purchase A/C',NULL,'True') ";
mListQuery.Add(query);
#endregion
}
#endregion
#region INSERT TABLE Expense
query = "Select COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH," +
"NUMERIC_PRECISION, DATETIME_PRECISION, " +
"IS_NULLABLE from INFORMATION_SCHEMA.COLUMNS " +
"where TABLE_NAME='Expense'";
if (!SQLHelper.GetInstance().ExcuteScalar(query, out msg).ISValidObject())
{
counttable = 1;
#region Expense
mquery = "CREATE TABLE [dbo].[Expense]( " +
"[ID][bigint] IDENTITY(1, 1) NOT NULL, " +
"[SlNo] [bigint] NOT NULL, " +
"[BillNo] [varchar](50) NULL, " +
"[BillingDate] [date] NOT NULL, " +
"[LedgerId] [uniqueidentifier] NULL, " +
"[DueDate] [date] NULL, " +
"[MemoNo] [varchar](50) NULL, " +
"[Description] [varchar](max) NULL, " +
"[TotalAmount] [float] NOT NULL, " +
"[DueAmount] [float] NULL, " +
"[Status] [varchar](50) NULL, " +
"[RCM] [varchar](50) NULL, " +
"[LastTransectionID] [uniqueidentifier] NULL, " +
"CONSTRAINT[PK_Expense_1] PRIMARY KEY CLUSTERED( [SlNo] ASC) " +
"WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, " +
"ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON[PRIMARY]) ON[PRIMARY] " +
"ALTER TABLE[dbo].[Expense] " +
"WITH CHECK ADD CONSTRAINT[FK_Expense_LadgerMain] FOREIGN KEY([LedgerId]) " +
"REFERENCES[dbo].[LadgerMain] ([LadgerID]) " +
"ON UPDATE CASCADE " +
"ON DELETE CASCADE " +
"ALTER TABLE[dbo].[Expense] " +
"CHECK CONSTRAINT[FK_Expense_LadgerMain]";
mListQuery.Add(mquery);
#endregion
#region ExpenseDetails
mquery = "CREATE TABLE [dbo].[ExpenseDetails]( " +
"[ID][bigint] IDENTITY(1, 1) NOT NULL, " +
"[SlNo] [bigint] NOT NULL, " +
"[LedgerID] [uniqueidentifier] NOT NULL, " +
"[Description] [varchar](max) NULL, " +
"[Amount] [float] NULL, " +
"[TransectionID] [uniqueidentifier] NULL, " +
"CONSTRAINT[PK_ExpenseDetails] PRIMARY KEY CLUSTERED([ID] ASC) " +
"WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, " +
"ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON[PRIMARY] " +
") ON[PRIMARY] " +
"ALTER TABLE[dbo].[ExpenseDetails] " +
"WITH CHECK ADD CONSTRAINT[FK_ExpenseDetails_ExpenseDetails] FOREIGN KEY([SlNo]) " +
"REFERENCES[dbo].[Expense] ([SlNo]) " +
"ON UPDATE CASCADE " +
"ON DELETE CASCADE " +
"ALTER TABLE[dbo].[ExpenseDetails] " +
"CHECK CONSTRAINT[FK_ExpenseDetails_ExpenseDetails] " +
"ALTER TABLE[dbo].[ExpenseDetails] " +
"WITH CHECK ADD CONSTRAINT[FK_ExpenseDetails_LadgerMain] FOREIGN KEY([LedgerID]) " +
"REFERENCES[dbo].[LadgerMain] ([LadgerID]) " +
"ALTER TABLE[dbo].[ExpenseDetails] " +
"CHECK CONSTRAINT[FK_ExpenseDetails_LadgerMain]";
mListQuery.Add(mquery);
#endregion
}
#endregion
count = counttable == 0 ? count : count + 1;
}
private void GenerateBillingList()
{
mDtTable.Rows.Clear();
int i = 0;
string query = "Select PurchaseBill.id,BillID,InvoiceNo, InvoiceDate, LadgerMain.TemplateName,DueDate, " +
"TotalAmount,DueAmount, Status from PurchaseBill inner join LadgerMain on " +
"PurchaseBill.LedgerId=LadgerMain.LadgerID where " + mQueryClouse
+ " order by PurchaseBill.id desc ";
DataTable dt = SQLHelper.GetInstance().ExcuteNonQuery(query, out msg);
if (dt.IsValidDataTable())
{
foreach (DataRow item in dt.Rows)
{
string billid = item["BillID"].ToString();
string invoicedatedate = !item["InvoiceDate"].ToString().ISNullOrWhiteSpace() ? DateTime.Parse(item["InvoiceDate"].ToString()).ToString("dd-MMM-yyyy") : "";
string invoiceno = item["InvoiceNo"].ToString();
string suppliername = item["TemplateName"].ToString();
string amountStr = item["TotalAmount"].ToString();
double totAmount = amountStr.ISNullOrWhiteSpace() ? 0f : double.Parse(amountStr);
string dueDateStr = !item["DueDate"].ToString().ISNullOrWhiteSpace() ? DateTime.Parse(item["DueDate"].ToString()).ToString("dd-MMM-yyyy") : "";
DateTime?dueDate = !dueDateStr.ISNullOrWhiteSpace() ? DateTime.Parse(dueDateStr) : (DateTime?)null;
string status = item["Status"].ToString();
string dueamountStr = item["DueAmount"].ToString();
double dueamount = !dueamountStr.ISNullOrWhiteSpace() ? double.Parse(dueamountStr) : 0f;
#region MyRegion
Color clr = new Color();
clr = Color.Black;
DataGridViewComboBoxCell cmbCell = new DataGridViewComboBoxCell();
cmbCell.Style.Font = new Font("Arial", 8f);
cmbCell.Style.ForeColor = Color.Blue;
//cmbCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
cmbCell.FlatStyle = FlatStyle.Flat;
cmbCell.DisplayStyle = DataGridViewComboBoxDisplayStyle.ComboBox;
cmbCell.Items.Add(" <Select> ");
if (status != "Cancel")
{
cmbCell.Items.Add("Bill Payment");
cmbCell.Items.Add("Cancel");
if (!IsDebitNoteIssue(invoiceno))
{
cmbCell.Items.Add("Debit Note");
}
if (dueamount > 0)
{
if (dueDate != null && dueDate >= DateTime.Now.Date)
{
clr = Color.Orange;
status = "Due";
}
else
{
clr = Color.Red;
status = "Overdue";
}
cmbCell.Value = " <Select> ";
}
else
{
cmbCell.Items.RemoveAt(1);
cmbCell.Items.RemoveAt(1);
clr = Color.ForestGreen;
status = "Paid";
}
}
else
{
clr = Color.DeepPink;
status = "Cancel";
}
#endregion
mDtTable.Rows.Add(i + 1, invoicedatedate, billid, invoiceno, suppliername,
totAmount.toString(), dueamount.toString(),
dueDate?.ToString("dd-MMM-yyyy"));
dgvBills.Rows[i].Cells["Action"] = cmbCell;
dgvBills.Rows[i].Cells["Status"].Value = status;
dgvBills.Rows[i].Cells["Status"].Style.ForeColor = clr;
i++;
}
}
}
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);
}
}
}
private void DataSave()
{
mlistquery.Clear();
string query = "";
#region data
#region No
string estmateno = txtestimateno.Text.ISNullOrWhiteSpace() ? "NULL" : "'" + txtestimateno.Text.GetDBFormatString() + "'";
string challanno = txtChallanno.Text.ISNullOrWhiteSpace() ? "NULL" : "'" + txtChallanno.Text.GetDBFormatString() + "'";
string purchaseorderno = txtpurchaseOrderno.Text.ISNullOrWhiteSpace() ? "NULL" : "'" + txtpurchaseOrderno.Text.GetDBFormatString() + "'";
string creditNoteNo = txtCreditNoteNo.Text.ISNullOrWhiteSpace() ? "NULL" : "'" + txtCreditNoteNo.Text.GetDBFormatString() + "'";
string debitNoteNo = txtDebitNoteNo.Text.ISNullOrWhiteSpace() ? "NULL" : "'" + txtDebitNoteNo.Text.GetDBFormatString() + "'";
string advanceReceiptNo = txtAdvanceReceiptNo.Text.ISNullOrWhiteSpace() ? "NULL" : "'" + txtAdvanceReceiptNo.Text.GetDBFormatString() + "'";
string advancePaymentNo = txtAdvancePaymentNo.Text.ISNullOrWhiteSpace() ? "NULL" : "'" + txtAdvancePaymentNo.Text.GetDBFormatString() + "'";
string refuntVoucherNo = txtRefuntVoucherNo.Text.ISNullOrWhiteSpace() ? "NULL" : "'" + txtRefuntVoucherNo.Text.GetDBFormatString() + "'";
string receiptVoucherNo = txtreceiptVoucherNo.Text.ISNullOrWhiteSpace() ? "NULL" : "'" + txtreceiptVoucherNo.Text.GetDBFormatString() + "'";
string paymentVoucharNo = txtPaymentVoucharNo.Text.ISNullOrWhiteSpace() ? "NULL" : "'" + txtPaymentVoucharNo.Text.GetDBFormatString() + "'";
#endregion
#region startfrom
string estmatestartfrom = txtEstimateStartfrom.Text.ISNullOrWhiteSpace() ? "NULL" : "" + txtEstimateStartfrom.Text.GetDBFormatString() + "";
string challanstartfrom = txtChallanStartfrom.Text.ISNullOrWhiteSpace() ? "NULL" : "" + txtChallanStartfrom.Text.GetDBFormatString() + "";
string purchaseOrderStartfrom = txtPurchaseOrderStartfrom.Text.ISNullOrWhiteSpace() ? "NULL" : "" + txtPurchaseOrderStartfrom.Text.GetDBFormatString() + "";
string creditNoteStartFrom = txtCreditNoteStartFrom.Text.ISNullOrWhiteSpace() ? "NULL" : "" + txtCreditNoteStartFrom.Text.GetDBFormatString() + "";
string debitNoteStartFrom = txtDebitNoteStartFrom.Text.ISNullOrWhiteSpace() ? "NULL" : "" + txtDebitNoteStartFrom.Text.GetDBFormatString() + "";
string advanceReceiptStartFrom = txtAdvanceReceiptStartFrom.Text.ISNullOrWhiteSpace() ? "NULL" : "" + txtAdvanceReceiptStartFrom.Text.GetDBFormatString() + "";
string advancePaymentStartFrom = txtAdvancePaymentStartFrom.Text.ISNullOrWhiteSpace() ? "NULL" : "" + txtAdvancePaymentStartFrom.Text.GetDBFormatString() + "";
string refuntVoucherStartFrom = txtRefuntVoucherStartFrom.Text.ISNullOrWhiteSpace() ? "NULL" : "" + txtRefuntVoucherStartFrom.Text.GetDBFormatString() + "";
string receiptVoucherStartFrom = txtreceiptVoucherStartFrom.Text.ISNullOrWhiteSpace() ? "NULL" : "" + txtreceiptVoucherStartFrom.Text.GetDBFormatString() + "";
string paymentVoucharStartFrom = txtPaymentVoucharStartFrom.Text.ISNullOrWhiteSpace() ? "NULL" : "" + txtPaymentVoucharStartFrom.Text.GetDBFormatString() + "";
string IsPercentageInMrp = rbtnMrp.Checked ? "True" : "False";
string IsThousandSeparate = chkThousandSeperator.Checked ? "True" : "False";
#endregion
#endregion
#region Query
query = "Update VoucherSettings set VoucherNoStart=" + estmateno + ",VoucherStartFrom=" + estmatestartfrom + " where VoucherType='Estimate'";
mlistquery.Add(query);
query = "Update VoucherSettings set VoucherNoStart=" + challanno + ",VoucherStartFrom=" + challanstartfrom + " where VoucherType='Challan'";
mlistquery.Add(query);
query = "Update VoucherSettings set VoucherNoStart=" + creditNoteNo + ",VoucherStartFrom=" + creditNoteStartFrom + " where VoucherType='CreditNote'";
mlistquery.Add(query);
query = "Update VoucherSettings set VoucherNoStart=" + purchaseorderno + ",VoucherStartFrom=" + purchaseOrderStartfrom + " where VoucherType='PurchaseOrder'";
mlistquery.Add(query);
query = "Update VoucherSettings set VoucherNoStart=" + advanceReceiptNo + ",VoucherStartFrom=" + advanceReceiptStartFrom + " where VoucherType='AdvanceReceiptVoucher'";
mlistquery.Add(query);
query = "Update VoucherSettings set VoucherNoStart=" + receiptVoucherNo + ",VoucherStartFrom=" + receiptVoucherStartFrom + " where VoucherType='ReceiptVoucher'";
mlistquery.Add(query);
query = "Update VoucherSettings set VoucherNoStart=" + refuntVoucherNo + ",VoucherStartFrom=" + refuntVoucherStartFrom + " where VoucherType='RefundVoucher'";
mlistquery.Add(query);
query = "Update VoucherSettings set VoucherNoStart=" + advancePaymentNo + ",VoucherStartFrom=" + advancePaymentStartFrom + " where VoucherType='AdvancePaymentVoucher'";
mlistquery.Add(query);
query = "Update VoucherSettings set VoucherNoStart=" + paymentVoucharNo + ",VoucherStartFrom=" + paymentVoucharStartFrom + " where VoucherType='PaymentVoucher'";
mlistquery.Add(query);
query = "Update VoucherSettings set VoucherNoStart=" + debitNoteNo + ",VoucherStartFrom=" + debitNoteStartFrom + " where VoucherType='DebitNote'";
mlistquery.Add(query);
//Update ToolsTable
query = "Update ToolsTable set IsPercentageInMRP='" + IsPercentageInMrp + "',IsThousandSeparate='" + IsThousandSeparate + "'";
mlistquery.Add(query);
SaveDecimalPlace();
#endregion
#region Execute
if (SQLHelper.GetInstance().ExecuteTransection(mlistquery, out msg))
{
MessageBox.Show("Data save.", "Save", MessageBoxButtons.OK, MessageBoxIcon.Information);
OtherSettingTools.CallAllFunctionOfOtherSettingTools();
}
else
{
MessageBox.Show(msg);
}
#endregion
}
private void DataSave()
{
#region Data
string itemtype = cmbType.Text.GetDBFormatString();
string itemName = txtItemName.Text.GetDBFormatString();
string categoryid = cmbCategory.Text.ISNullOrWhiteSpace() ? "NULL" : ((KeyValuePair <string, string>)cmbCategory.SelectedItem).Key.ToString();
string hsnorsaccode = cmbComodityNo.Text.GetDBFormatString();
string gstrate = "NULL";
string cessrate = "NULL";
string unitcode = "NULL";
string naration = "NULL";
string cgst = "NULL";
string sgst = "NULL";
string igst = "NULL";
string CompanyNAme = txtCompanyNAme.Text.GetDBFormatString();
string templateName = txtTemplatNAme.Text.GetDBFormatString();
string subcategory = cmbSubCategory.Text.GetDBFormatString();
if (cmbType.Text == "Goods")
{
unitcode = ((KeyValuePair <string, string>)cmbUnit.SelectedItem).Key.ToString();
}
if (!txtNaration.Text.ISNullOrWhiteSpace())
{
naration = "'" + txtNaration.Text.GetDBFormatString() + "'";
}
if (!txtCESSRate.Text.ISNullOrWhiteSpace())
{
cessrate = "'" + txtCESSRate.Text.GetDBFormatString() + "'";
}
if (!cmbGstRate.Text.ISNullOrWhiteSpace())
{
gstrate = "'" + cmbGstRate.Text.GetDBFormatString() + "'";
}
if (!txtCgst.Text.ISNullOrWhiteSpace())
{
cgst = "'" + txtCgst.Text.GetDBFormatString() + "'";
}
if (!txtSgst.Text.ISNullOrWhiteSpace())
{
sgst = "'" + txtSgst.Text.GetDBFormatString() + "'";
}
if (!txtIGst.Text.ISNullOrWhiteSpace())
{
igst = "'" + txtIGst.Text.GetDBFormatString() + "'";
}
bool isRCM = chkRCM.Checked;
bool isITC = chkITC.Checked;
#endregion
string query = "";
if (!mItemIDForEdit.ISNullOrWhiteSpace())
{
query = "Update item set itemName='" + templateName + "',itemType='" + itemtype +
"',Categoryid=" + categoryid + ",ComodityCode='" + hsnorsaccode +
"',GSTRate=" + gstrate + ",CessRate=" + cessrate + ",CGSTRate=" + cgst + ",sGSTRate="
+ sgst + ",UnitId=" + unitcode + ",Narration=" + naration + ",IGSTRATE=" + igst
+ ",Company='" + CompanyNAme + "',TemplateName='" + itemName + "',SubCategory='" +
subcategory + "',IsRCM='" + isRCM + "',IsITC='" + isITC + "' " +
"where id='" + mItemIDForEdit + "' ";
}
else
{
query = "Insert into item(itemName,itemType,Categoryid,ComodityCode,GSTRate, " +
"CessRate,CGSTRate,sGSTRate,UnitId,Narration,IGSTRATE,Company, " +
"TemplateName,SubCategory,IsRCM,IsITC) values('" + templateName + "','" +
itemtype + "'," + categoryid + "," + "'" + hsnorsaccode + "'," +
gstrate + "," + cessrate + "," + cgst + "," + sgst + "," + unitcode
+ "," + naration + "," + igst + ",'" + CompanyNAme + "','" + itemName
+ "','" + subcategory + "','" + isRCM + "','" + isITC + "')";
}
if (SQLHelper.GetInstance().ExcuteQuery(query, out msg))
{
ItemTools.GetItem();
ItemTools.GetItemCategory();
ItemTools.GetSubCategory();
ItemTools.GetItemCompany();
this.Close();
}
else
{
MessageBox.Show("Internal error. \n" + msg, "Save", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
