Exemplo n.º 1
0
        /// <summary>
        /// Method to write into the excel report file to print.
        /// </summary>
        public void ConvertToExcel()
        {
            //InventoryClass obj=new InventoryClass();
            SqlDataReader SqlDtr     = null;
            string        home_drive = Environment.SystemDirectory;

            home_drive = home_drive.Substring(0, 2);
            string strExcelPath = home_drive + "\\Servosms_ExcelFile\\Export\\";

            Directory.CreateDirectory(strExcelPath);
            string       path       = home_drive + @"\Servosms_ExcelFile\Export\StockLedgerReport.xls";
            StreamWriter sw         = new StreamWriter(path);
            string       prod_name  = "";
            string       pack1      = "";
            string       pack2      = "";
            string       trans_type = "";
            string       Cat        = "";

            //prod_name = drpProductName.SelectedItem.Value.ToString().Trim();
            prod_name = drpProductName.Value.ToString().Trim();
            if (prod_name.LastIndexOf(":") > -1)
            {
                string[] strArr = prod_name.Split(new char[] { ':' }, prod_name.Length);
                prod_name = strArr[0].Trim();
                if (strArr[1].Trim().IndexOf("Loose") > -1)
                {
                    pack1 = "0";
                    pack2 = "0";
                    Cat   = "Loose";
                }
                else
                {
                    string[] strPack = strArr[1].Trim().Split(new char[] { 'X' }, strArr[1].Length);
                    pack1 = strPack[0].Trim();
                    pack2 = strPack[1].Trim();
                    Cat   = "Others";
                }
            }
            else
            {
                pack1 = "0";
                pack2 = "0";
                Cat   = "Fuel";
            }
            //int f = 0;
            //trans_type = drpTransType.SelectedItem.Value.ToString().Trim();
            object obj = null;

            dbobj.ExecProc(OprType.Insert, "sp_stockLedger", ref obj, "@Prod_Name", prod_name, "@Pack11", pack1, "@Pack22", pack2, "@Trans_Type", trans_type, "@fromdate", GenUtil.str2MMDDYYYY(txtDateFrom.Text.Trim()), "@Todate", GenUtil.str2MMDDYYYY(txtDateTo.Text.Trim()), "@Cat", Cat);
            dbobj.SelectQuery("Select * from Stock_Ledger order by " + Cache["strorderby"] + "", ref SqlDtr);
            sw.WriteLine("From Date\t" + txtDateFrom.Text);
            sw.WriteLine("To Date\t" + txtDateTo.Text);
            //sw.WriteLine("Product Name\t"+drpProductName.SelectedItem.Text);
            sw.WriteLine("Product Name\t" + drpProductName.Value);
            sw.WriteLine("Transaction Type\t" + drpTransType.SelectedItem.Text);
            sw.WriteLine();
            sw.WriteLine("Shipping Party\tTransaction Type\tTransaction ID\tDate\tQty Nos(IN)\tQty Ltr(IN)\tQty Nos(OUT)\tQty Ltr(OUT)\tClosing Balance(Nos)\tClosing Balance(Ltr)");
            SqlDataReader rdr = null;

            if (SqlDtr.HasRows)
            {
                while (SqlDtr.Read())
                {
                    string trans_no = "";
                    string Party    = "";
                    if (SqlDtr.GetValue(0).ToString().StartsWith("Sales Invoice"))
                    {
                        trans_no = SqlDtr.GetValue(1).ToString();
                        //*********************
                        dbobj.SelectQuery("select Cust_Name from Customer c,Sales_Master sm where c.Cust_ID=sm.Cust_ID and Invoice_No='" + trans_no + "'", ref rdr);
                        if (rdr.Read())
                        {
                            Party = rdr["Cust_Name"].ToString();
                        }
                        rdr.Close();
                        //*********************
                        if (System.Convert.ToString(int.Parse(FromDate).ToString() + ToDate).Length > 3)
                        {
                            trans_no = trans_no.Substring(4);
                        }
                        else
                        {
                            trans_no = trans_no.Substring(3);
                        }
                    }
                    //*********************
                    else if (SqlDtr.GetValue(0).ToString().StartsWith("Purchase Invoice"))
                    {
                        trans_no = SqlDtr.GetValue(1).ToString();
                        dbobj.SelectQuery("select supp_name from purchase_master pm,supplier s where supp_id=vendor_id and Invoice_No='" + trans_no + "'", ref rdr);
                        if (rdr.Read())
                        {
                            Party = rdr["Supp_Name"].ToString();
                        }
                        rdr.Close();
                    }
                    //*********************
                    else
                    {
                        trans_no = SqlDtr.GetValue(1).ToString();
                    }
                    sw.WriteLine(Party + "\t" + SqlDtr.GetValue(0).ToString() + "\t" +
                                 //SqlDtr.GetValue(1).ToString()+"\t"+
                                 trans_no + "\t" +
                                 GenUtil.str2MMDDYYYY(trimDate(SqlDtr.GetValue(2).ToString())) + "\t" +
                                 checkValue1(SqlDtr.GetValue(3).ToString()) + "\t" +
                                 checkValue1(SqlDtr.GetValue(4).ToString()) + "\t" +
                                 checkValue1(SqlDtr.GetValue(5).ToString()) + "\t" +
                                 checkValue1(SqlDtr.GetValue(6).ToString()) + "\t" +
                                 checkValue1(SqlDtr.GetValue(7).ToString()) + "\t" +
                                 checkValue1(SqlDtr.GetValue(8).ToString()));
                }
                sw.WriteLine("Total\t\t\t\t" + InQtyNos.ToString() + "\t" + InQtyLtr.ToString() + "\t" + OutQtyNos.ToString() + "\t" + OutQtyLtr.ToString() + "\t" + closing_bal_nos.ToString() + "\t" + closing_bal_ltr.ToString());
            }
            else
            {
                Stock_Ledger.Visible = false;
                sw.Close();
                MessageBox.Show("Data not available");
                return;
            }
            SqlDtr.Close();
            sw.Close();
        }
Exemplo n.º 2
0
        /// <summary>
        /// Its fires the sp_StockLedger procedure and fetch the values from Stock_Ledger Report and writes the return values into StockLedgerReport.txt file.
        /// </summary>
        protected void btnPrint_Click(object sender, System.EventArgs e)
        {
            if (!checkValidity())
            {
                return;
            }

            /*
             *                                         =================================================
             *                                         Stock Ledger Report From mm/dd/yyyy To mm/dd/yyyy
             *                                         =================================================
             *
             * Product Name     : Petrol(MS)
             * Transaction Type : Sales
             +----------------------+-----+----------+------------+------------+------------+
             |                      |     |          |     IN     |    OUT     |CLOSING BAL.|
             |    Transaction       |Trans|   Date   |----+-------|----+-------|----+-------|
             |       Type           |  ID |          |Qty.|Qty. in|Qty.|Qty. in|Bal.|Bal. in|
             |                      |     |          |Nos |  Ltr. |Nos |  Ltr. |Nos |  Ltr. |
             +----------------------+-----+----------+----+-------+----+-------+----+-------+
             | Opening Balance        1001  mm/dd/yyyy 1234 1234567 1234 1234567 1234 1234567
             | Closing Balance
             | Purchase Invoice
             | Stock Adjustment(OUT)
             | 123456789012345678|
             +----------------------+-----+----------+----+-------+----+-------+----+-------+
             */

            try
            {
                string home_drive = Environment.SystemDirectory;
                home_drive = home_drive.Substring(0, 2);
                string       path = home_drive + @"\Inetpub\wwwroot\Servosms\Sysitem\ServosmsPrintServices\ReportView\StockLedgerReport.txt";
                StreamWriter sw   = new StreamWriter(path);
                string       info = "";
                // Condensed
                sw.Write((char)27);               //added by vishnu
                sw.Write((char)67);               //added by vishnu
                sw.Write((char)0);                //added by vishnu
                sw.Write((char)12);               //added by vishnu

                sw.Write((char)27);               //added by vishnu
                sw.Write((char)78);               //added by vishnu
                sw.Write((char)5);                //added by vishnu

                sw.Write((char)27);               //added by vishnu
                sw.Write((char)15);
                //**********
                string   des     = "--------------------------------------------------------------------------------";
                string   Address = GenUtil.GetAddress();
                string[] addr    = Address.Split(new char[] { ':' }, Address.Length);
                sw.WriteLine(GenUtil.GetCenterAddr(addr[0], des.Length).ToUpper());
                sw.WriteLine(GenUtil.GetCenterAddr(addr[1] + addr[2], des.Length));
                sw.WriteLine(GenUtil.GetCenterAddr("Tin No : " + addr[3], des.Length));
                sw.WriteLine(des);
                //**********
                sw.WriteLine(GenUtil.GetCenterAddr("=================================================", des.Length));
                sw.WriteLine(GenUtil.GetCenterAddr("Stock Ledger Report From " + txtDateFrom.Text.Trim() + " To " + txtDateTo.Text.Trim(), des.Length));
                sw.WriteLine(GenUtil.GetCenterAddr("=================================================", des.Length));
                //sw.WriteLine("");
                sw.WriteLine("Product Name     : " + drpProductName.Value);
                //sw.WriteLine("Transaction Type : "+drpTransType.SelectedItem.Value);
                sw.WriteLine("+------------------------------+----------------------+-----+----------+------------+------------+------------+  ");
                sw.WriteLine("|                              |                      |     |          |     IN     |    OUT     |CLOSING BAL.|");
                sw.WriteLine("|    Shipping Party            |    Transaction       |Trans|   Date   |----+-------|----+-------|----+-------|");
                sw.WriteLine("|                              |                      |  ID |          |Qty.|Qty. in|Qty.|Qty. in|Bal.|Bal. in|");
                sw.WriteLine("|                              |                      |     |          |Nos |  Ltr. |Nos |  Ltr. |Nos |  Ltr. |");
                sw.WriteLine("+------------------------------+----------------------+-----+----------+----+-------+----+-------+----+-------+");
                //             123456789012345678901234567890 1234567890123456789012 1001  mm/dd/yyyy 1234 1234567 1234 1234567 1234 1234567
                info = " {0,-30:S} {1,-22:S} {2,-5:S} {3,-10:F} {4,4:F} {5,7:F} {6,4:F} {7,7:F} {8,4:F} {9,7:F}";

                string prod_name  = "";
                string pack1      = "";
                string pack2      = "";
                string trans_type = "";
                string Cat        = "";
                prod_name = drpProductName.Value.ToString().Trim();
                if (prod_name.LastIndexOf(":") > -1)
                {
                    string[] strArr = prod_name.Split(new char[] { ':' }, prod_name.Length);
                    prod_name = strArr[0].Trim();
                    if (strArr[1].Trim().IndexOf("Loose") > -1)
                    {
                        pack1 = "0";
                        pack2 = "0";
                        Cat   = "Loose";
                    }
                    else
                    {
                        string[] strPack = strArr[1].Trim().Split(new char[] { 'X' }, strArr[1].Length);
                        pack1 = strPack[0].Trim();
                        pack2 = strPack[1].Trim();
                        Cat   = "Others";
                    }
                }
                else
                {
                    pack1 = "0";
                    pack2 = "0";
                    Cat   = "Fuel";
                }
                int f = 0;
                trans_type = drpTransType.SelectedItem.Value.ToString().Trim();
                object obj = null;
                dbobj.ExecProc(OprType.Insert, "sp_stockLedger", ref obj, "@Prod_Name", prod_name, "@Pack11", pack1, "@Pack22", pack2, "@Trans_Type", trans_type, "@fromdate", GenUtil.str2MMDDYYYY(txtDateFrom.Text.Trim()), "@Todate", GenUtil.str2MMDDYYYY(txtDateTo.Text.Trim()), "@Cat", Cat);
                SqlDataReader SqlDtr = null;
                //dbobj.SelectQuery("Select * from Stock_Ledger order by trans_date ",ref SqlDtr);
                dbobj.SelectQuery("Select * from Stock_Ledger order by " + Cache["strorderby"] + "", ref SqlDtr);
                //sql=sql+" order by "+""+Cache["strorderby"]+"";
                SqlDataReader rdr = null;
                if (SqlDtr.HasRows)
                {
                    while (SqlDtr.Read())
                    {
                        string trans_no = "";
                        string Party    = "";
                        if (SqlDtr.GetValue(0).ToString().StartsWith("Sales Invoice"))
                        {
                            trans_no = SqlDtr.GetValue(1).ToString();
                            //*********************
                            //Coment by vikas 23.10.09 dbobj.SelectQuery("select Cust_Name from Customer c,Sales_Master sm where c.Cust_ID=sm.Cust_ID and Invoice_No='"+trans_no+"'",ref rdr);
                            dbobj.SelectQuery("select Cust_Name+':'+cast(c.City as varchar) Cust_Name from Customer c,Sales_Master sm where c.Cust_ID=sm.Cust_ID and Invoice_No='" + trans_no + "'", ref rdr);
                            if (rdr.Read())
                            {
                                Party = rdr["Cust_Name"].ToString();
                            }
                            rdr.Close();
                            //*********************
                            if (System.Convert.ToString(int.Parse(FromDate).ToString() + ToDate).Length > 3)
                            {
                                trans_no = trans_no.Substring(4);
                            }
                            else
                            {
                                trans_no = trans_no.Substring(3);
                            }
                        }
                        //*********************
                        else if (SqlDtr.GetValue(0).ToString().StartsWith("Purchase Invoice"))
                        {
                            trans_no = SqlDtr.GetValue(1).ToString();
                            dbobj.SelectQuery("select supp_name from purchase_master pm,supplier s where supp_id=vendor_id and Invoice_No='" + trans_no + "'", ref rdr);
                            if (rdr.Read())
                            {
                                Party = rdr["Supp_Name"].ToString();
                            }
                            rdr.Close();
                        }
                        //*********************
                        else
                        {
                            trans_no = SqlDtr.GetValue(1).ToString();
                        }

                        sw.WriteLine(info, GenUtil.TrimLength(Party, 30), SqlDtr.GetValue(0).ToString(),
                                     //SqlDtr.GetValue(1).ToString(),
                                     trans_no,
                                     GenUtil.str2MMDDYYYY(trimDate(SqlDtr.GetValue(2).ToString())),
                                     checkValue1(SqlDtr.GetValue(3).ToString()),
                                     checkValue1(SqlDtr.GetValue(4).ToString()),
                                     checkValue1(SqlDtr.GetValue(5).ToString()),
                                     checkValue1(SqlDtr.GetValue(6).ToString()),
                                     checkValue1(SqlDtr.GetValue(7).ToString()),
                                     checkValue1(SqlDtr.GetValue(8).ToString()));
                    }
                }
                else
                {
                    Stock_Ledger.Visible = false;
                    f = 1;
                    sw.Close();
                    MessageBox.Show("Data not available");
                    return;
                }
                SqlDtr.Close();
                sw.WriteLine("+------------------------------+----------------------+-----+----------+----+-------+----+-------+----+-------+");
                sw.WriteLine(info, "  Total", "", "", "", InQtyNos.ToString(), InQtyLtr.ToString(), OutQtyNos.ToString(), OutQtyLtr.ToString(), closing_bal_nos.ToString(), closing_bal_ltr.ToString());
                sw.WriteLine("+------------------------------+----------------------+-----+----------+----+-------+----+-------+----+-------+");
                SqlDtr.Close();
                sw.Close();
                if (f == 0)
                {
                    Print();
                }
                else
                {
                    return;
                }
            }
            catch (Exception ex)
            {
                CreateLogFiles.ErrorLog("Form:StockLedgerReport.aspx,Method:btnPrint_Click" + " EXCEPTION " + ex.Message + "  userid  " + uid);
            }
        }