using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Text;

public partial class Default3 : System.Web.UI.Page
{
    SqlConnection con = new SqlConnection(ConfigurationManager.AppSettings["NewCon"]);
    DataSet ds = new DataSet();
    DataSet ds1 = new DataSet();
    DataSet ds2 = new DataSet();
    DataSet ds3 = new DataSet();
    DataSet ds4 = new DataSet();
    DataSet ds5 = new DataSet();
    DataSet ds6 = new DataSet();
    DataSet ds7 = new DataSet();
    string jvid;
    
   


    protected void Page_Load(object sender, EventArgs e)
    {

        if (!Page.IsPostBack)
        {



            txtTransactionDate.Text = DateTime.Today.Day.ToString() + "-" + DateTime.Today.Month.ToString() + "-" + DateTime.Today.Year.ToString();

            Session["compid"] = 35;
            Session["currency"] = "FRANC CFA(BEN)";


            generateAutoNumber();
            ////txtTransactionDate.Text = DateTime.Today.ToString();



            SqlDataAdapter da = new SqlDataAdapter("select Periodid,period from mstPeriods", con);
            da.Fill(ds);
            intPeriod.DataSource = ds;
            intPeriod.DataTextField = "period";
            intPeriod.DataValueField = "periodid";
            intPeriod.DataBind();
            intPeriod.Items.Insert(0, "Select a Period");


            SqlDataAdapter da1 = new SqlDataAdapter("select invoiceid,invnumber from [sl-invoices]where compid=" + Session["compid"] + "", con);
            da1.Fill(ds1);
            refinvoiceno.DataSource = ds1;
            refinvoiceno.DataTextField = "invnumber";
            refinvoiceno.DataValueField = "invoiceid";
            refinvoiceno.DataBind();
            refinvoiceno.Items.Insert(0, "Select a Invoice No.");


            SqlDataAdapter da2 = new SqlDataAdapter("select bldraftid,blnumber from [sh-bldraft] where  compid=" + Session["compid"] + " and blnumber<>'' AND blnumber <>'NO BL' union select bldraftid,blnumber from [sh-bldraft] where bldraftid in (select blid from [sl-invoices] where buyerid in(select buyerid from [sl-buyers] where groupcompid=" + Session["compid"] + ")) and blnumber in (select blno from [sl-purchasebooked] where compid=" + Session["compid"] + " and blno<>'')", con);
            da2.Fill(ds2);
            RefBlNo.DataSource = ds2;
            RefBlNo.DataTextField = "blnumber";
            RefBlNo.DataValueField = "bldraftid";
            RefBlNo.DataBind();
            RefBlNo.Items.Insert(0, "Select a Bl");


            SqlDataAdapter da3 = new SqlDataAdapter("select matoutid,containerno from [in-matout] where compid=" + Session["compid"] + "", con);
            da3.Fill(ds3);
            refcontno.DataSource = ds3;
            refcontno.DataTextField = "Containerno";
            refcontno.DataValueField = "matoutid";
            refcontno.DataBind();
            refcontno.Items.Insert(0, "Select a Container");


            SqlDataAdapter da4 = new SqlDataAdapter("select matoutid,trailerno from [in-matout] where compid=" + Session["compid"] + "", con);
            da4.Fill(ds4);
            cmbtrailer.DataSource = ds4;
            cmbtrailer.DataTextField = "Trailerno";
            cmbtrailer.DataValueField = "matoutid";
            cmbtrailer.DataBind();
            cmbtrailer.Items.Insert(0, "Select a Container");

            //fill debit account leger
            SqlDataAdapter da5 = new SqlDataAdapter("select  [AC-SubAccounts].subaccountid, [AC-SubAccounts].subaccountname from [AC-AccountHeads] INNER JOIN [AC-SubAccounts] ON [AC-AccountHeads].AccountHeadID = [AC-SubAccounts].AccountHeadID AND [AC-AccountHeads].CompID = [AC-SubAccounts].CompID INNER JOIN [AC-AccountGroups] ON [AC-AccountGroups].AccountGroupID = [AC-AccountHeads].AccountGroupID AND [AC-AccountGroups].CompID = [AC-AccountHeads].CompID AND [AC-AccountGroups].AccountGroupName not in ('STOCK','PURCHASE ACCOUNT','SALES ACCOUNT','BANK ACCOUNTS') and [AC-SubAccounts].compid=" + Session["compid"] + " order by [ac-subaccounts].subaccountname", con);
            da5.Fill(ds5);
            cmbDebit.DataSource = ds5;
            cmbDebit.DataTextField = "subaccountname";
            cmbDebit.DataValueField = "subaccountid";
            cmbDebit.DataBind();
            cmbDebit.Items.Insert(0, "Select a Ledger");

           // Fill credit account ledger
            cmbCredit.DataSource = ds5;
            cmbCredit.DataTextField = "subaccountname";
            cmbCredit.DataValueField = "subaccountid";
            cmbCredit.DataBind();
            cmbCredit.Items.Insert(0, "Select a ledger");

            // Fill debit journal currency
            SqlDataAdapter da6 = new SqlDataAdapter("select currid,currency from mstcurrencies", con);
            da6.Fill(ds6);
            cmbCurrencydr.DataSource = ds6;
            cmbCurrencydr.DataTextField = "currency";
            cmbCurrencydr.DataValueField = "currid";
            cmbCurrencydr.DataBind();
            cmbCurrencydr.Items.Insert(0, "Select a currency");

         // fill credit journal currency
            cmbCurrencycr.DataSource = ds6;
            cmbCurrencycr.DataTextField = "currency";
            cmbCurrencycr.DataValueField = "currid";
            cmbCurrencycr.DataBind();
           
        }

    }

    protected void generateAutoNumber()
    {
        DataSet ds8 = new DataSet();
        int i;

        SqlDataAdapter daat = new SqlDataAdapter("select top 1 jvnumber from [ac-journalvoucher] where compid=" + Session["compid"] + " order by jvnumber desc", con);
        daat.Fill(ds8);
        string p;
        p = ds8.Tables[0].Rows[0][0].ToString();
        StringBuilder sb = new StringBuilder("0");
        StringBuilder sb1 = new StringBuilder("0");

        foreach (char c in p)
        {
            if (Char.IsNumber(c))
            {
                sb.Append(c);
            }
        }

        foreach (char d in p)
        {
            if (char.IsLetter(d))
            {
                sb1.Append(d);
            }
        }

        string dd;
        dd = Convert.ToString(sb1.ToString());
        i = Int32.Parse(sb.ToString());


        if (i == 0)
        {
            txtJVNumber.Text = "JV" + "1";
            // txtJVNumber.Enabled = false;
        }
        else
        {
            int j = i + 1;
            txtJVNumber.Text = j.ToString();
            txtJVNumber.Text = dd + txtJVNumber.Text;
            //txtJVNumber.Enabled = false;
        }
        daat.Dispose();
        ds8.Reset();
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        lstDebit.Items.Add(new ListItem(cmbDebit.SelectedItem.Text, cmbDebit.SelectedValue));
        lstclbaltilldate.Items.Add(new ListItem(txtclbal.Text, txtclbal.Text));
        lstcurrdr.Items.Add(new ListItem(cmbCurrencydr.SelectedItem.Text, cmbCurrencydr.SelectedItem.Value));
        lstexchdr.Items.Add(new ListItem(txtExchangedr.Text,txtExchangedr.Text));
        lstamt.Items.Add(new ListItem(txtAmtdr.Text, txtAmtdr.Text));
        lstDebitAmount.Items.Add(new ListItem(txtDebitAmount.Text, txtDebitAmount.Text));

       decimal TotalQty;
          TotalQty =0;
        for (int i = 0; i < lstDebitAmount.Items.Count; i++)
        {
     
            TotalQty = Convert.ToDecimal(TotalQty) + Convert .ToDecimal(lstDebitAmount.Items[i].Value);
       
        }
        txtTotDebitAmount.Text = (TotalQty.ToString());


    }
    protected void bttnSaveCredit_Click(object sender, EventArgs e)
    {
        lstCredit.Items.Add(new ListItem(cmbCredit.SelectedItem.Text, cmbCredit.SelectedValue));
        lstclbalcr.Items.Add(new ListItem(txtclbalcr.Text, cmbCredit.Text));
        lstcurrcr.Items.Add(new ListItem(cmbCurrencycr.SelectedItem.Text, cmbCurrencycr.SelectedItem.Value));
        lstexchcr.Items.Add(new ListItem(txtExchangecr.Text, txtExchangecr.Text));
        lstamtcr.Items.Add(new ListItem(txtAmtcr.Text, txtAmtcr.Text));
        lstCreditAmount.Items.Add(new ListItem(txtCreditAmount.Text, txtCreditAmount.Text));

        decimal TotalQtyCr;
        TotalQtyCr = 0;
        for (int i = 0; i < lstCreditAmount.Items.Count; i++)
        {
            TotalQtyCr =Convert.ToDecimal(TotalQtyCr) +Convert.ToDecimal(lstCreditAmount.Items[i].Value);
        
        }

        txtTotCreditAmount.Text = TotalQtyCr.ToString();

    }
    protected void bttnUpdate_Click(object sender, EventArgs e)
    {
             
            con.Open();
            SqlCommand cmd = new SqlCommand("insert into [ac-journalvoucher](DocTypeID,JVTypeID,JVNumber,CompID,Amount,AmountUE,Remarks,posted,ChequeNo,RefInvoiceNo,RefBlNo,RefContNo,TrailerNo,RefNo,TransactionDate,Chequedate) values(@DocTypeID,@JVTypeID,@JVNumber,@CompID,@Amount,@AmountUE,@Remarks,@posted,@ChequeNo,@RefInvoiceNo,@RefBlNo,@RefContNo,@TrailerNo,@RefNo,@TransactionDate,@Chequedate)", con);

            cmd.Parameters.Add(new SqlParameter("@DocTypeID", SqlDbType.Int));
            cmd.Parameters["@DocTypeID"].Value = 3;

            cmd.Parameters.Add(new SqlParameter("@jvtypeid", SqlDbType.Int));
            cmd.Parameters["@jvtypeid"].Value = 9;

            cmd.Parameters.Add(new SqlParameter("@JVNumber", SqlDbType.NVarChar));
            cmd.Parameters["@JVNumber"].Value = txtJVNumber.Text;

            cmd.Parameters.Add(new SqlParameter("@compid", SqlDbType.Int));
            cmd.Parameters["@compid"].Value = 35;
            cmd.Parameters.Add(new SqlParameter("@Amount", SqlDbType.Int));
            cmd.Parameters["@Amount"].Value = Convert.ToDecimal(txtTotDebitAmount.Text);

            cmd.Parameters.Add(new SqlParameter("@AmountUE", SqlDbType.Int));
            cmd.Parameters["@AmountUE"].Value = Convert.ToDecimal(txtTotDebitAmount.Text);

            //  cmd.Parameters.Add(new SqlParameter("@PeriodID", SqlDbType.Int));
            //  cmd.Parameters["@PeriodID"].Value = Convert.ToInt32(intPeriod.SelectedValue.ToString());

            cmd.Parameters.AddWithValue("@Remarks", txtRemarks.Text);

            cmd.Parameters.Add(new SqlParameter("@posted", SqlDbType.Int));
            cmd.Parameters["@posted"].Value = 0;

            cmd.Parameters.AddWithValue("@ChequeNo", intchqNo.Text);
            cmd.Parameters.AddWithValue("@RefInvoiceNo", refinvoiceno.SelectedValue.ToString());
            cmd.Parameters.AddWithValue("@RefBlNo", RefBlNo.SelectedValue.ToString());
            cmd.Parameters.AddWithValue("@RefContNo", refcontno.SelectedValue.ToString());
            cmd.Parameters.AddWithValue("@TrailerNo", cmbtrailer.SelectedValue.ToString());
            cmd.Parameters.AddWithValue("@RefNo", txtRefno.Text);
            cmd.Parameters.AddWithValue("@TransactionDate", Convert.ToDateTime(txtTransactionDate.Text));
            cmd.Parameters.AddWithValue("@Chequedate", Convert.ToDateTime(intChkDate.Text));

            cmd.ExecuteNonQuery();

            // start entry of [ac-journalvoucherdet]
            // get journalvoucherid 
            SqlCommand cmdJvid = new SqlCommand("select journalvoucherid from [ac-journalvoucher] where compid=" + Session["compid"] + " and jvnumber='" + txtJVNumber.Text + "'", con);
            SqlDataReader drJvid;

            drJvid = cmdJvid.ExecuteReader();
            //    con.Close();

            if (drJvid.Read())
            {

                jvid = drJvid[0].ToString();
                Response.Write(jvid);
            }
            cmdJvid.Dispose();
            drJvid.Dispose();
            con.Close();

            // debit part in [ac-journalvoucherdet]
            con.Open();
            for (int i = 0; i < lstDebit.Items.Count; i++)
            {
                SqlCommand cmdDebit = new SqlCommand("insert into [ac-journalvoucherdet](JournalVoucherID,SubAccountID,Closing,JournalCurr,ExchRate,Amount,DebitAmount,DebitAmountUE,compid,AddedOn) values(" + jvid + "," + lstDebit.Items[i].Value + "," + lstclbaltilldate.Items[i].Value + "," + lstcurrdr.Items[i].Value + "," + lstexchdr.Items[i].Text + "," + lstamt.Items[i].Text + "," + lstDebitAmount.Items[i].Value + "," + lstDebitAmount.Items[i].Value + "," + Session["compid"] + ",'" + txtTransactionDate.Text + "')", con);
                cmdDebit.ExecuteNonQuery();

            }

            con.Close();
            con.Open();
            // credit part in [ac-journalvoucherdet]

            for (int j = 0; j < lstCredit.Items.Count; j++)
            {
                //JournalVoucherID,SubAccountID,Closing,JournalCurr,ExchRate,Amount,CreditAmount,CreditAmountUE,compid,AddedOn
                SqlCommand cmdCredit = new SqlCommand("insert into [ac-journalvoucherdet](JournalVoucherID,SubAccountID,Closing,JournalCurr,ExchRate,Amount,CreditAmount,CreditAmountUE,compid,AddedOn) values(" + jvid + "," + lstCredit.Items[j].Value + "," + lstclbalcr.Items[j].Text + "," + lstcurrcr.Items[j].Value + "," + lstexchcr.Items[j].Value + "," + lstamtcr.Items[j].Text + "," + lstCreditAmount.Items[j].Text + "," + lstCreditAmount.Items[j].Text + "," + Session["compid"] + ",'" + txtTransactionDate.Text + "')", con);
                cmdCredit.ExecuteNonQuery();
            }
            con.Close();

            //debit part in [ac-transactions]
            con.Open();

            for (int i = 0; i < lstDebit.Items.Count; i++)
            {
                SqlCommand cmdDebitTrans = new SqlCommand("insert into [ac-transactions](DocTypeID,JVTypeID,JVNo,JVNumber,SubAccountID,TransactionDate,CurrID,DebitAmount,DebitAmountUE,PeriodID,Narration,CompID,AddedOn)values(3,9," + jvid + ",'" + txtJVNumber.Text + "'," + lstDebit.Items[i].Value + ",'" + txtTransactionDate.Text + "'," + lstcurrdr.Items[i].Value + "," + lstDebitAmount.Items[i].Text + "," + lstDebitAmount.Items[i].Text + "," + intPeriod.SelectedItem.Value + ",'" + txtRemarks.Text + "'," + Session["compid"] + ",'" + txtTransactionDate.Text + "')", con);
                cmdDebitTrans.ExecuteNonQuery();
            }

            con.Close();
            con.Open();
            //credit part in [ac-transactions]

            for (int i = 0; i < lstCredit.Items.Count; i++)
            {

                SqlCommand cmdCreditTrans = new SqlCommand("insert into [ac-transactions](DocTypeID,JVTypeID,JVNo,JVNumber,SubAccountID,TransactionDate,CurrID,CreditAmount,CreditAmountUE,PeriodID,Narration,CompID,AddedOn)values(3,9," + jvid + ",'" + txtJVNumber.Text + "'," + lstCredit.Items[i].Value + ",'" + txtTransactionDate.Text + "'," + lstcurrcr.Items[i].Value + "," + lstCreditAmount.Items[i].Value + "," + lstCreditAmount.Items[i].Value + "," + intPeriod.SelectedItem.Value + ",'" + txtRemarks.Text + "'," + Session["compid"] + ",'" + txtTransactionDate.Text + "')", con);
                cmdCreditTrans.ExecuteNonQuery();
            }
   
    //this.bttnUpdate.Attributes.Add("onclick","return Test()");

    }
 



       
    }