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()); } } }