private void upDateSIVoucher()
{
string strSQL = "", strString = "", strCompRefNo, strKey, strDate, strPartyname = "", strSymbol = "";
double dblNetAmount = 0, dblRoundOff = 0;
int lngloop = 0;
string connstring = Utility.SQLConnstringComSwitch(strComID);
using (SqlConnection gcnMain = new SqlConnection(connstring))
{
if (gcnMain.State == ConnectionState.Open)
{
gcnMain.Close();
}
try
{
gcnMain.Open();
SqlDataReader dr;
SqlCommand cmdInsert = new SqlCommand();
SqlTransaction myTrans;
Prog.Value = 0;
myTrans = gcnMain.BeginTransaction();
cmdInsert.Connection = gcnMain;
cmdInsert.Transaction = myTrans;
//string strItemname = "";
//List<StockItem> oogrp = invms.gFillStockItemAllWithoutGodown(strComID, Utility.gblnAccessControl, Utility.gstrUserName, "").ToList();
//if (oogrp.Count > 0)
//{
// foreach (StockItem ostk in oogrp)
// {
// strItemname = ostk.strLocation;
// }
//}
strSQL = "SELECT STOCKITEM_NAME FROM INV_STOCKITEM ";
strSQL = strSQL + " WHERE STOCKITEM_PRIMARY_GROUP ='Finished Goods' ";
cmdInsert.CommandText = strSQL;
dr = cmdInsert.ExecuteReader();
while (dr.Read())
{
strString = strString + dr["COMP_REF_NO"].ToString() + "~" + dr["VOUCHER_REF_KEY"].ToString() + "~" + dr["COMP_VOUCHER_DATE"].ToString() + "~" + dr["LEDGER_NAME"].ToString() + "|";
lngloop += 1;
}
dr.Close();
if (strString != "")
{
Prog.Value = 0;
string[] words = strString.Split('|');
Prog.Maximum = lngloop;
foreach (string strBill in words)
{
string[] ooCost = strBill.Split('~');
if (ooCost[0] != "")
{
strCompRefNo = ooCost[0].ToString();
strKey = ooCost[1].ToString();
strDate = Convert.ToDateTime(ooCost[2]).ToString("dd-MM-yyyy");
//strLedgerName = ooCost[3].ToString();
//dblNetAmount = Convert.ToDouble(words[4].ToString());
strSQL = "SELECT LEDGER_NAME ,VOUCHER_CREDIT_AMOUNT from ACC_VOUCHER WHERE COMP_VOUCHER_TYPE =16 and COMP_REF_NO ='" + strCompRefNo + "' and VOUCHER_TOBY ='Cr' ";
cmdInsert.CommandText = strSQL;
dr = cmdInsert.ExecuteReader();
if (dr.Read())
{
strPartyname = dr["LEDGER_NAME"].ToString();
dblNetAmount = Convert.ToDouble(dr["VOUCHER_CREDIT_AMOUNT"].ToString());
}
dr.Close();
strSQL = "SELECT VOUCHER_DEBIT_AMOUNT,VOUCHER_TOBY from ACC_VOUCHER WHERE COMP_VOUCHER_TYPE =16 and COMP_REF_NO ='" + strCompRefNo + "' and LEDGER_NAME ='Round Off'";
cmdInsert.CommandText = strSQL;
dr = cmdInsert.ExecuteReader();
if (dr.Read())
{
dblRoundOff = Convert.ToDouble(dr["VOUCHER_DEBIT_AMOUNT"].ToString());
strSymbol = dr["VOUCHER_TOBY"].ToString();
if (strSymbol == "Dr")
{
dblRoundOff = dblRoundOff * -1;
}
}
dr.Close();
strSQL = "DELETE FROM ACC_VOUCHER WHERE COMP_REF_NO='" + strCompRefNo + "' ";
cmdInsert.CommandText = strSQL;
cmdInsert.ExecuteNonQuery();
if (dblRoundOff < 0)
{
//dblCredit = dblCredit - ((Math.Abs(dblRoundOff) + Math.Abs(dblAddAmount)) - Math.Abs(dblLessAmount));
strSQL = VoucherSW.gInsertSalesVoucher(strCompRefNo, strDate, 1, strPartyname, "Dr", dblNetAmount, 16, "As per Details", "0001", 0, "", "", "", "");
cmdInsert.CommandText = strSQL;
cmdInsert.ExecuteNonQuery();
strSQL = VoucherSW.gInsertSalesVoucher(strCompRefNo, strDate, 2, "Sales Accounts", "Cr", dblNetAmount + Math.Abs(dblRoundOff), 16, "As per Details", "0001", 2, "", "", "", "", "", "");
cmdInsert.CommandText = strSQL;
cmdInsert.ExecuteNonQuery();
strSQL = VoucherSW.gInsertSalesVoucher(strCompRefNo, strDate, 3, "Round Off", "Dr", Math.Abs(dblRoundOff), 16, "As per Details", "0001", 2, "-", "", "", "", "", "");
cmdInsert.CommandText = strSQL;
cmdInsert.ExecuteNonQuery();
}
else if (dblRoundOff > 0)
{
//dblCredit = dblCredit - ((Math.Abs(dblRoundOff) + Math.Abs(dblAddAmount)) - Math.Abs(dblLessAmount));
strSQL = VoucherSW.gInsertSalesVoucher(strCompRefNo, strDate, 2, "Sales Accounts", "Cr", dblNetAmount - Math.Abs(dblRoundOff), 16, "As per Details", "0001", 2, "", "", "", "", "", "");
cmdInsert.CommandText = strSQL;
cmdInsert.ExecuteNonQuery();
strSQL = VoucherSW.gInsertSalesVoucher(strCompRefNo, strDate, 1, strPartyname, "Dr", dblNetAmount, 16, "As per Details", "0001", 0, "", "", "", "");
cmdInsert.CommandText = strSQL;
cmdInsert.ExecuteNonQuery();
strSQL = VoucherSW.gInsertSalesVoucher(strCompRefNo, strDate, 3, "Round Off", "Cr", Math.Abs(dblRoundOff), 16, "As per Details", "0001", 2, "+", "", "", "", "", "");
cmdInsert.CommandText = strSQL;
cmdInsert.ExecuteNonQuery();
}
Prog.Value += 1;
}
}
}
cmdInsert.Transaction.Commit();
gcnMain.Close();
cmdInsert.Dispose();
MessageBox.Show("Ok");
this.Dispose();
this.Hide();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
}
private void btnSave_Click(object sender, EventArgs e)
{
string i = "";
if (ValidateFields() == false)
{
return;
}
try
{
string strSQL = "", strBillKey = "", strRefNo = "", strBranchId = "", strNarrations = "",
strDate = "", strItemName = "", strGodownName = "", strUOm = "";
SqlCommand cmdQuery = new SqlCommand();
SqlDataReader dr;
long lngSlNo, lngloop = 1;
double dblqty = 0, dblRate = 0, dblTotalAmount = 0;
SqlCommand cmdInsert = new SqlCommand();
string connstring = Utility.SQLConnstringComSwitch(strComID);
using (SqlConnection gcnMain = new SqlConnection(connstring))
{
if (gcnMain.State == ConnectionState.Open)
{
gcnMain.Close();
}
gcnMain.Open();
cmdQuery.Connection = gcnMain;
strDate = dteDate.Text;
strBranchId = Utility.gstrGetBranchID(strComID, uctxtFromLocation.Text);
strGodownName = uctxtFromLocation.Text;
strRefNo = "TS" + strBranchId + Utility.gstrLastNumber(strComID, 60);
strNarrations = uctxtNarration.Text;
for (int introw = 0; introw < DGright.Rows.Count; introw++)
{
lngSlNo = Convert.ToInt64(DGright[0, introw].Value);
strItemName = DGright[1, introw].Value.ToString();
dblqty = Convert.ToDouble(DGright[2, introw].Value.ToString());
dblRate = Utility.gdblGetCostPriceNew(strComID, strItemName, strDate);
dblTotalAmount = Math.Round(dblqty * dblRate, 2);
SqlTransaction myTrans;
myTrans = gcnMain.BeginTransaction();
cmdInsert.Connection = gcnMain;
cmdInsert.Transaction = myTrans;
if (introw == 0)
{
strSQL = VoucherSW.gInsertmasterNew(strRefNo, strBranchId, 60, strDate, 0,
strNarrations, "", 0, "", "0", 6, "");
cmdInsert.CommandText = strSQL;
cmdInsert.ExecuteNonQuery();
strSQL = VoucherSW.gIncreaseVoucher(60);
cmdInsert.CommandText = strSQL;
cmdInsert.ExecuteNonQuery();
}
strBillKey = strRefNo + lngloop.ToString().PadLeft(4, '0');
strSQL = VoucherSW.mInsertTranInward(strBillKey, lngloop, strRefNo, strItemName, 60,
strDate, dblqty, dblRate, uctxtToLocation.Text, dblTotalAmount, "I",
strBranchId, "", "", strUOm, strUOm, "", 0, 0, "");
cmdInsert.CommandText = strSQL;
cmdInsert.ExecuteNonQuery();
strSQL = "UPDATE INV_TRAN SET ";
strSQL = strSQL + "INV_TRAN_QUANTITY=INV_TRAN_QUANTITY+" + dblqty;
strSQL = strSQL + ",OUTWARD_QUANTITY=OUTWARD_QUANTITY+" + dblqty;
strSQL = strSQL + ",INV_TRAN_AMOUNT= INV_TRAN_AMOUNT -" + dblTotalAmount;
strSQL = strSQL + ",OUTWARD_SALES_AMOUNT=OUTWARD_SALES_AMOUNT+" + dblTotalAmount;
strSQL = strSQL + ",OUTWARD_COST_AMOUNT=OUTWARD_COST_AMOUNT +" + dblTotalAmount;
strSQL = strSQL + "WHERE INV_TRAN_SERIAL=" + lngSlNo + " ";
strSQL = strSQL + "AND STOCKITEM_NAME='" + strItemName.Replace("'", "''") + "' ";
cmdInsert.CommandText = strSQL;
cmdInsert.ExecuteNonQuery();
cmdInsert.Transaction.Commit();
lngloop += 1;
}
MessageBox.Show("Success");
DG.Rows.Clear();
DGright.Rows.Clear();
gcnMain.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
private void updateAccVoucher()
{
string strSQL = "", strString = "", strCompRefNo, strKey, strDate = "", strLedgerName = "", strPartyname = "", strSymbol = "";
double dblNetAmount = 0, dblDebitAmount = 0, dblCreditAmount = 0, dblLoose = 0;
int lngloop = 0;
string connstring = Utility.SQLConnstringComSwitch(strComID);
using (SqlConnection gcnMain = new SqlConnection(connstring))
{
if (gcnMain.State == ConnectionState.Open)
{
gcnMain.Close();
}
try
{
gcnMain.Open();
SqlDataReader dr;
SqlCommand cmdInsert = new SqlCommand();
SqlTransaction myTrans;
Prog.Value = 0;
myTrans = gcnMain.BeginTransaction();
cmdInsert.Connection = gcnMain;
cmdInsert.Transaction = myTrans;
strSQL = "select COMP_REF_NO,sum(VOUCHER_DEBIT_AMOUNT),sum(VOUCHER_CREDIT_AMOUNT) from ACC_VOUCHER ";
strSQL = strSQL + "WHERE COMP_VOUCHER_TYPE =16 ";
strSQL = strSQL + "group by COMP_REF_NO having sum(VOUCHER_DEBIT_AMOUNT) <> sum(VOUCHER_CREDIT_AMOUNT) ";
//strSQL = strSQL + "AND COMP_REF_NO ='SI0001D-65569'";
cmdInsert.CommandText = strSQL;
dr = cmdInsert.ExecuteReader();
while (dr.Read())
{
strString = strString + dr["COMP_REF_NO"].ToString() + "|";
lngloop += 1;
}
dr.Close();
if (strString != "")
{
Prog.Value = 0;
string[] words = strString.Split('|');
Prog.Maximum = lngloop;
foreach (string strBill in words)
{
string[] ooCost = strBill.Split('~');
if (ooCost[0] != "")
{
strCompRefNo = ooCost[0].ToString();
//strKey = ooCost[1].ToString();
//strDate = Convert.ToDateTime(ooCost[2]).ToString("dd-MM-yyyy");
//strLedgerName = ooCost[3].ToString();
//dblNetAmount = Convert.ToDouble(words[4].ToString());
strSQL = "SELECT LEDGER_NAME ,COMP_VOUCHER_DATE,VOUCHER_DEBIT_AMOUNT from ACC_VOUCHER WHERE COMP_VOUCHER_TYPE =16 and COMP_REF_NO ='" + strCompRefNo + "' and LEDGER_NAME <> 'Sales Accounts' AND LEDGER_NAME <> 'Loose Medicine Sale' ";
cmdInsert.CommandText = strSQL;
dr = cmdInsert.ExecuteReader();
if (dr.Read())
{
strPartyname = dr["LEDGER_NAME"].ToString();
strDate = Convert.ToDateTime(dr["COMP_VOUCHER_DATE"]).ToString("dd-MM-yyyy");
dblDebitAmount = Convert.ToDouble(dr["VOUCHER_DEBIT_AMOUNT"].ToString());
}
dr.Close();
strSQL = "SELECT LEDGER_NAME,VOUCHER_CREDIT_AMOUNT from ACC_VOUCHER WHERE COMP_VOUCHER_TYPE =16 and COMP_REF_NO ='" + strCompRefNo + "' and LEDGER_NAME ='Sales Accounts' ";
cmdInsert.CommandText = strSQL;
dr = cmdInsert.ExecuteReader();
if (dr.Read())
{
strLedgerName = dr["LEDGER_NAME"].ToString();
dblCreditAmount = Convert.ToDouble(dr["VOUCHER_CREDIT_AMOUNT"].ToString());
}
dr.Close();
strSQL = "SELECT VOUCHER_CREDIT_AMOUNT from ACC_VOUCHER WHERE COMP_VOUCHER_TYPE =16 and COMP_REF_NO ='" + strCompRefNo + "' and LEDGER_NAME ='Loose Medicine Sale'";
cmdInsert.CommandText = strSQL;
dr = cmdInsert.ExecuteReader();
if (dr.Read())
{
dblLoose = Convert.ToDouble(dr["VOUCHER_CREDIT_AMOUNT"].ToString());
}
dr.Close();
strSQL = "DELETE FROM ACC_VOUCHER WHERE COMP_REF_NO='" + strCompRefNo + "' ";
cmdInsert.CommandText = strSQL;
cmdInsert.ExecuteNonQuery();
//dblCredit = dblCredit - ((Math.Abs(dblRoundOff) + Math.Abs(dblAddAmount)) - Math.Abs(dblLessAmount));
strSQL = VoucherSW.gInsertSalesVoucher(strCompRefNo, strDate, 1, strPartyname, "Dr", dblCreditAmount + dblLoose, 16, "As per Details", "0001", 0, "", "", "", "");
cmdInsert.CommandText = strSQL;
cmdInsert.ExecuteNonQuery();
strSQL = VoucherSW.gInsertSalesVoucher(strCompRefNo, strDate, 2, "Sales Accounts", "Cr", dblCreditAmount, 16, "As per Details", "0001", 2, "", "", "", "", "", "");
cmdInsert.CommandText = strSQL;
cmdInsert.ExecuteNonQuery();
strSQL = VoucherSW.gInsertSalesVoucher(strCompRefNo, strDate, 3, "Loose Medicine Sale", "Cr", dblLoose, 16, "As per Details", "0001", 2, "-", "", "", "", "", "");
cmdInsert.CommandText = strSQL;
cmdInsert.ExecuteNonQuery();
double dblTotalAmnt = (dblCreditAmount + dblLoose);
strSQL = "UPDATE ACC_COMPANY_VOUCHER SET COMP_VOUCHER_AMOUNT =" + dblCreditAmount + " ";
strSQL = strSQL + ",COMP_VOUCHER_ADD_AMOUNT=" + dblLoose + " ";
strSQL = strSQL + ",COMP_VOUCHER_NET_AMOUNT=" + dblTotalAmnt + " ";
strSQL = strSQL + " WHERE COMP_REF_NO='" + strCompRefNo + "' ";
cmdInsert.CommandText = strSQL;
cmdInsert.ExecuteNonQuery();
Prog.Value += 1;
}
}
}
cmdInsert.Transaction.Commit();
gcnMain.Close();
cmdInsert.Dispose();
MessageBox.Show("Ok");
this.Dispose();
this.Hide();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
}
