Beispiel #1
0
        public List <sledger> getsledger(DateTime FYStartDate, DateTime fromDate, DateTime toDate, string SLCode, string SLType)
        {
            decimal        ob = 0;
            sledger        ldg;
            List <sledger> ledger = new List <sledger>();

            try
            {
                string query = "select sum(DrAmtINR-CrAmtINR) Balance from ViewLedgerCustomer" +
                               " where SLType='" + SLType + "' and SLCode='" + SLCode + "' and  VoucherDate >= '" + FYStartDate.ToString("yyyy-MM-dd") + "'" +
                               " and VoucherDate <'" + fromDate.ToString("yyyy-MM-dd") + "'";
                SqlConnection conn = new SqlConnection(Login.connString);
                SqlCommand    cmd  = new SqlCommand(query, conn);
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    ob                    = reader.IsDBNull(0) ? 0 : reader.GetDecimal(0);
                    ldg                   = new sledger();
                    ldg.DocumentID        = "OB";
                    ldg.VoucherNo         = 0;
                    ldg.VoucherDate       = fromDate;
                    ldg.TransactionACName = "Opening Balance";
                    ldg.Narration         = "";
                    if (ob >= 0)
                    {
                        ldg.DebitAmnt  = ob;
                        ldg.CreditAmnt = 0;
                    }
                    else
                    {
                        ldg.CreditAmnt = ob * -1;
                        ldg.DebitAmnt  = 0;
                    }

                    ledger.Add(ldg);
                }
                conn.Close();
            }
            catch (Exception ex)
            {
                ////MessageBox.Show("Error querying Ledger Details");
            }

            try
            {
                string query = "select DocumentID,VoucherNo,VoucherDate,ACName,Narration,DrAmtINR,CrAmtINR from ViewLedgerCustomer" +
                               " where SLType='" + SLType + "' and SLCode='" + SLCode + "' and VoucherDate >= '" + fromDate.ToString("yyyy-MM-dd") + "' and" +
                               " VoucherDate <='" + toDate.ToString("yyyy-MM-dd") + "'" +
                               " order by VoucherDate,DocumentID, VoucherNo ";
                SqlConnection conn = new SqlConnection(Login.connString);
                SqlCommand    cmd  = new SqlCommand(query, conn);
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    try
                    {
                        ldg                   = new sledger();
                        ldg.DocumentID        = reader.GetString(0);
                        ldg.VoucherNo         = reader.GetInt32(1);
                        ldg.VoucherDate       = reader.GetDateTime(2);
                        ldg.TransactionACName = reader.GetString(3);
                        ldg.Narration         = reader.GetString(4);
                        ldg.DebitAmnt         = reader.GetDecimal(5);
                        ldg.CreditAmnt        = reader.GetDecimal(6);
                        ledger.Add(ldg);
                    }
                    catch (Exception ex)
                    {
                    }
                }
                conn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error querying Ledger Details");
            }
            return(ledger);
        }