public void ConvertToExcel()
{
InventoryClass obj = new InventoryClass();
System.Data.SqlClient.SqlDataReader rdr = 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\Prod_Promo_Dis_Claim_Report.xls";
StreamWriter sw = new StreamWriter(path);
string sql = "";
string info = "";
//05.06.09 sql="select sp.supp_name,pm.vndr_invoice_no,pm.vndr_invoice_date,p.prod_name+':'+p.pack_type,pd.qty from supplier sp,purchase_master pm,purchase_details pd,products p where pm.invoice_no=pd.invoice_no and sp.supp_id=pm.vendor_id and p.prod_id=pd.prod_id and pm.vndr_invoice_date>='"+GenUtil.str2MMDDYYYY(txtDateFrom.Text)+"' and pm.vndr_invoice_date<='"+GenUtil.str2MMDDYYYY(txtDateTo.Text)+"' and pd.prod_id in (select prodid from Prod_Promo_Grade_Entry where datefrom>='"+GenUtil.str2MMDDYYYY(txtDateFrom.Text)+"' and dateto<='"+GenUtil.str2MMDDYYYY(txtDateTo.Text)+"') order by p.prod_name,p.pack_type";
if (DropSchemName.SelectedIndex == 0)
{
sql = "select sp.supp_name,pm.vndr_invoice_no,pm.vndr_invoice_date,p.prod_name+':'+p.pack_type,pd.qty from supplier sp,purchase_master pm,purchase_details pd,products p where pm.invoice_no=pd.invoice_no and sp.supp_id=pm.vendor_id and p.prod_id=pd.prod_id and pm.vndr_invoice_date>='" + GenUtil.str2MMDDYYYY(Request.Form["txtDateFrom"]) + "' and pm.vndr_invoice_date<='" + GenUtil.str2MMDDYYYY(Request.Form["txtDateTo"]) + "' and pd.prod_id in (select prodid from Prod_Promo_Grade_Entry where datefrom>='" + GenUtil.str2MMDDYYYY(Request.Form["txtDateFrom"]) + "' and dateto<='" + GenUtil.str2MMDDYYYY(Request.Form["txtDateTo"]) + "') order by p.prod_name,p.pack_type";
}
else
{
sql = "select sp.supp_name,pm.vndr_invoice_no,pm.vndr_invoice_date,p.prod_name+':'+p.pack_type,pd.qty from supplier sp,purchase_master pm,purchase_details pd,products p where pm.invoice_no=pd.invoice_no and sp.supp_id=pm.vendor_id and p.prod_id=pd.prod_id and pm.vndr_invoice_date>='" + GenUtil.str2MMDDYYYY(Request.Form["txtDateFrom"]) + "' and pm.vndr_invoice_date<='" + GenUtil.str2MMDDYYYY(Request.Form["txtDateTo"]) + "' and pd.prod_id in (select prodid from Prod_Promo_Grade_Entry where schname='" + DropSchemName.SelectedItem.Value.ToString() + "' and datefrom>='" + GenUtil.str2MMDDYYYY(Request.Form["txtDateFrom"]) + "' and dateto<='" + GenUtil.str2MMDDYYYY(Request.Form["txtDateTo"]) + "') order by p.prod_name,p.pack_type";
}
rdr = obj.GetRecordSet(sql);
string des = "--------------------------------------------------------------------------------------------------------------";
sw.WriteLine(GenUtil.GetCenterAddr("=============================================", des.Length));
sw.WriteLine(GenUtil.GetCenterAddr("Product Promotion Scheme Discount Claim Report From " + txtDateFrom.Text + " To " + txtDateTo.Text, des.Length));
sw.WriteLine(GenUtil.GetCenterAddr("=============================================", des.Length));
//sw.WriteLine("From Date : "+txtDateFrom.Text+", To Date : "+txtDateTo.Text);
sw.WriteLine("Vendor Name\tInvoice No.\tInvoice Date\tProduct Name\tQty in Nos.\tQty in ltr.");
sw.WriteLine();
while (rdr.Read())
{
sw.WriteLine(rdr.GetValue(0).ToString() + "\t" +
rdr.GetValue(1).ToString() + "\t" +
GenUtil.str2DDMMYYYY(GenUtil.trimDate(rdr.GetValue(2).ToString())) + "\t" +
rdr.GetValue(3).ToString() + "\t" + rdr.GetValue(4).ToString() + "\t" +
Qtyinltr(rdr.GetValue(3).ToString(), rdr.GetValue(4).ToString()));
Qty_Tot += Convert.ToDouble(rdr.GetValue(4).ToString());
}
rdr.Close();
sw.WriteLine();
sw.WriteLine("Total" + "\t" + "" + "\t" + "" + "\t" + "" + "\t" + Qty_Tot.ToString() + "\t" + Qty_Tot_ltr.ToString());
sw.WriteLine();
sw.Close();
}
//This is used to make the report.
public void makingReport()
{
show();
string home_drive = Environment.SystemDirectory;
home_drive = home_drive.Substring(0, 2);
string path = home_drive + @"\Inetpub\wwwroot\Servosms\Sysitem\ServosmsPrintServices\ReportView\ProfitAnalysisReport.txt";
StreamWriter sw = new StreamWriter(path);
// 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("PROFIT ANALYSIS REPORT From " + txtDateFrom.Text.ToString() + " To " + txtDateTo.Text.ToString(), des.Length));
sw.WriteLine(GenUtil.GetCenterAddr("====================================================", des.Length));
sw.WriteLine(" ");
sw.WriteLine(" Opening Stock : " + os.ToString() + " ");
sw.WriteLine(" Purchase : " + rect.ToString() + " ");
sw.WriteLine(" Sales : " + sales.ToString() + " ");
sw.WriteLine(" Closing Stock : " + cs.ToString() + " ");
sw.WriteLine(" --------------------+-----------------");
sw.WriteLine(" Total Sales : " + sales.ToString() + " ");
sw.WriteLine(" Sales Ibp : " + ibp1.ToString() + " ");
sw.WriteLine(" Purchase Eicher : " + eicher1.ToString() + " ");
sw.WriteLine(" Purchase Force : " + force1.ToString() + " ");
sw.WriteLine(" --------------------+-----------------");
sw.WriteLine(" Grand Total : " + grandtotal.ToString() + " ");
sw.WriteLine(" --------------------+-----------------");
sw.Close();
}
/// <summary>
/// Method to write into the report file to print.
/// </summary>
public void makingReport()
{
InventoryClass obj = new InventoryClass();
System.Data.SqlClient.SqlDataReader rdr = null;
string home_drive = Environment.SystemDirectory;
home_drive = home_drive.Substring(0, 2);
string path = home_drive + @"\Inetpub\wwwroot\Servosms\Sysitem\ServosmsPrintServices\ReportView\SSRIncentiveSheet.txt";
StreamWriter sw = new StreamWriter(path);
string sql = "";
string info = "";
sql = "select Emp_ID,Emp_Name from Employee where Designation='Servo Sales Representative'";
rdr = obj.GetRecordSet(sql);
// 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);
//******S***
sw.WriteLine(GenUtil.GetCenterAddr("========================================================", des.Length));
sw.WriteLine(GenUtil.GetCenterAddr("SSR Incentive Sheet Report From " + txtDateFrom.Text + " To " + txtDateTo.Text, des.Length));
sw.WriteLine(GenUtil.GetCenterAddr("========================================================", des.Length));
//sw.WriteLine("From Date : "+txtDateFrom.Text+", To Date : "+txtDateTo.Text);
// coment by vikas 26.10.2012 sw.WriteLine("+-------------------------+-------------+-------------+-------------+-------------+---------------+---------------+-------------+");
// coment by vikas 26.10.2012 sw.WriteLine("| SSR Name | Receipt | Cash Disc. | Spa. Disc | Credit Note | Cheque Bounce | Total Receipt | Incentive |");
// coment by vikas 26.10.2012 sw.WriteLine("+-------------------------+-------------+-------------+-------------+-------------+---------------+---------------+-------------+");
sw.WriteLine("+-------------------------+-------------+-------------+-------------+-------------+---------------+---------------+-------------+--------+----------+");
sw.WriteLine("| SSR Name | Receipt | Cash Disc. | Spa. Disc | Credit Note | Cheque Bounce | Total Receipt | Incentive | Salary | Sal+Inc |");
sw.WriteLine("+-------------------------+-------------+-------------+-------------+-------------+---------------+---------------+-------------+--------+----------+");
// 1234567890123456789012345 1234567890123 1234567890123 1234567890123 1234567890123 123456789012345 123456789012345 1234567890123 12345678 1234567890
if (rdr.HasRows)
{
//coment by vikas 26.10.2012 info = " {0,-25:S} {1,13:F} {2,13:S} {3,13:S} {4,13:S} {5,15:S} {6,15:S} {7,13:S}";
info = " {0,-25:S} {1,13:F} {2,13:S} {3,13:S} {4,13:S} {5,15:S} {6,15:S} {7,13:S} {8,8:S} {9,10:S}";
while (rdr.Read())
{
sw.WriteLine(info, GenUtil.TrimLength(rdr["Emp_Name"].ToString(), 25),
GetReceipt(rdr["Emp_ID"].ToString()),
GetCashDiscount(rdr["Emp_ID"].ToString()),
GetSpacialDiscount(rdr["Emp_ID"].ToString()),
GetCreditNote(rdr["Emp_ID"].ToString()),
GetBounce(rdr["Emp_ID"].ToString()),
GenUtil.strNumericFormat(TotalReceipt.ToString()),
GetIncentive(TotalReceipt.ToString()),
GetBasicSalary(rdr["Emp_ID"].ToString()), // add by vikas 26.10.2012
GetSalaryIncentive(TotalReceipt.ToString(), rdr["Emp_ID"].ToString()) // add by vikas 26.10.2012
);
TotalAmount[5] += TotalReceipt;
}
}
//coment by vikas 26.10.2012 sw.WriteLine("+-------------------------+-------------+-------------+-------------+-------------+---------------+---------------+-------------+");
sw.WriteLine("+-------------------------+-------------+-------------+-------------+-------------+---------------+---------------+-------------+--------+----------+");
sw.WriteLine(info, " total",
GenUtil.strNumericFormat(TotalAmount[0].ToString()),
GenUtil.strNumericFormat(TotalAmount[1].ToString()),
GenUtil.strNumericFormat(TotalAmount[2].ToString()),
GenUtil.strNumericFormat(TotalAmount[3].ToString()),
GenUtil.strNumericFormat(TotalAmount[4].ToString()),
GenUtil.strNumericFormat(TotalAmount[5].ToString()),
GenUtil.strNumericFormat(TotalAmount[6].ToString()),
GenUtil.strNumericFormat(TotalAmount[7].ToString()), // add by vikas 26.10.2012
GenUtil.strNumericFormat(TotalAmount[8].ToString()) // add by vikas 26.10.2012
);
//coment by vikas 26.10.2012 sw.WriteLine("+-------------------------+-------------+-------------+-------------+-------------+---------------+---------------+-------------+");
sw.WriteLine("+-------------------------+-------------+-------------+-------------+-------------+---------------+---------------+-------------+--------+----------+");
sw.Close();
}
/// <summary>
/// This method is used to prepares the report file TaxReport.txt for printing.
/// </summary>
protected void BtnPrint_Click(object sender, System.EventArgs e)
{
try
{
/*
* ==========
* Tax Report
* ==========
*
+------------+---------+-------+-------+---------+---------+---------+--------+-------+---------+--------+--------+
| Product |Reduction| Entry | RPG | RPG | Local |Transport| Other | Local | LST |License |DO/FO/BC|
| Name | | Tax |Charges|Surcharge|Transport| Charge | Levies | Sales |Surcharge| Free |Charges |
| | | | | | Charge | | Value | Tax | |Recovery| |
+------------+---------+-------+-------+---------+---------+---------+--------+-------+---------+--------+--------+
| 123456789012 123456789 1234567 1234567 123456789 123456789 123456789 12345678 1234567 123456789 12345678 12345678
*/
SqlDataReader SqlDtr = null;
string home_drive = Environment.SystemDirectory;
home_drive = home_drive.Substring(0, 2);
string path = home_drive + @"\Inetpub\wwwroot\Servosms\Sysitem\ServosmsPrintServices\ReportView\TaxReport.txt";
StreamWriter sw = new StreamWriter(path);
// 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("Tax Report", des.Length));
sw.WriteLine(GenUtil.GetCenterAddr("==========", des.Length));
sw.WriteLine("");
sw.WriteLine("+------------+---------+-------+---------+-----------+-----------+-----------+----------+---------+-----------+----------+----------+");
sw.WriteLine("| Product |Reduction| Entry | RPG | RPG | Local | Transport | Other | Local | LST | License | DO/FO/BC |");
sw.WriteLine("| Name | | Tax | Charges | Surcharge | Transport | Charge | Levies | Sales | Surcharge | Free | Charges |");
sw.WriteLine("| | | | | | Charge | | Value | Tax | | Recovery | |");
sw.WriteLine("+------------+---------+-------+---------+-----------+-----------+-----------+----------+---------+-----------+----------+----------+");
// 123456789012 123456789 1234567 1234567 123456789 123456789 123456789 12345678 1234567 123456789 12345678 12345678
string info = " {0,-12:S} {1,9:S} {2,7:S} {3,9:S} {4,11:S} {5,11:S} {6,11:S} {7,10:S} {8,9:S} {9,11:S} {10,10:S} {11,10:S}";
dbobj.SelectQuery("select p.prod_name, cast(Reduction as varchar)+' '+Unit_rdc as Reduction,cast(entry_tax as varchar)+' '+unit_etax as Entry_Tax, cast(rpg_charge as varchar)+' '+Unit_rpgchg as rpg_charge,cast(rpg_surcharge as varchar)+' '+Unit_rpgschg as rpg_surcharge,cast(LT_charge as varchar)+' '+Unit_ltchg as LT_Charge,cast(tran_charge as varchar)+' '+Unit_tchg as trans_charge,cast(Other_Lvy as varchar)+' '+Unit_olvy as Other_Lvy,cast(LST as varchar)+' '+Unit_LST as LST, cast(LST_Surcharge as varchar)+' '+Unit_lstschg as LST_Surcharge,cast(LF_Recov as varchar)+' '+Unit_lfrecov as LF_Recov, cast(dofobc_Charge as varchar)+' '+Unit_dochg as dofobc_Charge from tax_entry t, Products p where p.Prod_ID = t.ProductID ", ref SqlDtr);
if (SqlDtr.HasRows)
{
while (SqlDtr.Read())
{
sw.WriteLine(info, SqlDtr.GetValue(0).ToString(),
SqlDtr.GetValue(1).ToString(),
SqlDtr.GetValue(2).ToString(),
SqlDtr.GetValue(3).ToString(),
SqlDtr.GetValue(4).ToString(),
SqlDtr.GetValue(5).ToString(),
SqlDtr.GetValue(6).ToString(),
SqlDtr.GetValue(7).ToString(),
SqlDtr.GetValue(8).ToString(),
SqlDtr.GetValue(9).ToString(),
SqlDtr.GetValue(10).ToString(),
SqlDtr.GetValue(11).ToString());
}
}
else
{
MessageBox.Show("Data not available");
sw.Close();
return;
}
SqlDtr.Close();
sw.WriteLine("+------------+---------+-------+---------+-----------+-----------+-----------+----------+---------+-----------+----------+----------+");
// deselect Condensed
//sw.Write((char)18);
//sw.Write((char)12);
sw.Close();
Print();
}
catch (Exception ex)
{
CreateLogFiles.ErrorLog("Form:TaxReport.aspx,Method:BtnPrint_Click() EXCEPTION: " + ex.Message + ". userid " + uid);
}
}
/// <summary>
/// Prepares the report file BalanceSheet.txt for printing.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void BtnPrint_Click(object sender, System.EventArgs e)
{
try
{
if (DateTime.Compare(ToMMddYYYY(txtDateFrom.Text), ToMMddYYYY(txtDateTo.Text)) > 0)
{
MessageBox.Show("Date From Should be less than Date To");
return;
}
// Table1.Visible = true;
/*
* ===============
* Balance Sheet
* ===============
*
* From Date : mm/dd/yyyy
* To Date : mm/dd/yyyy
+--------------------------------------+--------------------------------------+
| LIABILITIES | ASSETS |
+--------------------------------------+--------------------------------------+
|Capital 123456789012.00 |Fixed Assets 123456789012.00 |
|Reserve & Surplus 12 |Investments 12 |
|Secured Loans 12 |Current Assets 12 |
|Unsecured Loans 12 |Loan & Advances 12 |
|Current Liabilities 12 |Profit & Loss A/C 12 |
|Provisions 12 |Misc. Expenditure 12 |
|Difference in Balance 123456789012.00 |Difference in Balance 12 |
| | |
|--------------------- |--------------------- |
|Total 123456789012.00 |Total 123456789012.00 |
|--------------------- |--------------------- |
| | |
+--------------------------------------+--------------------------------------+
|
*/
string home_drive = Environment.SystemDirectory;
home_drive = home_drive.Substring(0, 2);
string path = home_drive + @"\Inetpub\wwwroot\Servosms\Sysitem\ServosmsPrintServices\ReportView\BalanceSheet.txt";
StreamWriter sw = new StreamWriter(path);
// 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("Balance Sheet", des.Length));
sw.WriteLine(GenUtil.GetCenterAddr("===============", des.Length));
sw.WriteLine("");
sw.WriteLine("From Date : " + txtDateFrom.Text);
sw.WriteLine("To Date : " + txtDateTo.Text);
sw.WriteLine("+-------------------------------------+-------------------------------------+");
sw.WriteLine("| LIABILITIES | ASSETS |");
sw.WriteLine("+-------------------------------------+-------------------------------------+");
SqlConnection con = null;
SqlCommand cmd = null;
SqlDataReader SqlDtr = null;
string Op_Stock = "";
double Opening_Stock = 0;
string Cl_Stock = "";
double Closing_Stock = 0;
string Net_Profit = "";
double Net_Pro = 0;
double Net_Loss = 0;
con = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["Servosms"]);
con.Open();
cmd = new SqlCommand("exec getProfitLoss '" + getFromDate() + "','" + GenUtil.str2MMDDYYYY(txtDateTo.Text) + "'", con);
SqlDtr = cmd.ExecuteReader();
if (SqlDtr.Read())
{
Net_Profit = GenUtil.strNumericFormat(SqlDtr["Net_Profit"].ToString());
if (!Net_Profit.Trim().Equals(""))
{
Net_Pro = System.Convert.ToDouble(Net_Profit);
if (Net_Pro < 0)
{
Net_Pro = (Net_Pro * -1);
Net_Loss = Net_Pro;
Net_Pro = 0;
}
else
{
Net_Loss = 0;
}
}
Op_Stock = GenUtil.strNumericFormat(SqlDtr["Opening_Stock"].ToString());
Cl_Stock = GenUtil.strNumericFormat(SqlDtr["Closing_Stock"].ToString());
}
SqlDtr.Close();
string op_bal_dr = "";
string op_bal_cr = "";
double op_bal_d = 0;
double op_bal_c = 0;
cmd = new SqlCommand("exec getBalanceSheet '" + getFromDate() + "','" + GenUtil.str2MMDDYYYY(txtDateTo.Text) + "'", con);
SqlDtr = cmd.ExecuteReader();
if (SqlDtr.Read())
{
sw.WriteLine("|Capital {0,15:F} |Fixed Assets {1,15:F} |", GenUtil.strNumericFormat(SqlDtr.GetValue(2).ToString()), GenUtil.strNumericFormat(SqlDtr.GetValue(7).ToString()));
sw.WriteLine("|Reserve & Surplus {0,15:F} |Investments {1,15:F} |", GenUtil.strNumericFormat(Net_Pro.ToString()), GenUtil.strNumericFormat(SqlDtr.GetValue(8).ToString()));
if (!Cl_Stock.Trim().Equals(""))
{
Closing_Stock = System.Convert.ToDouble(Cl_Stock);
}
string current_assets = GenUtil.strNumericFormat(SqlDtr.GetValue(9).ToString());
double C_A = 0;
if (!current_assets.Trim().Equals(""))
{
C_A = System.Convert.ToDouble(current_assets);
C_A = C_A + Closing_Stock;
}
sw.WriteLine("|Secured Loans {0,15:F} |Current Assets {1,15:F} |", GenUtil.strNumericFormat(SqlDtr.GetValue(3).ToString()), GenUtil.strNumericFormat(C_A.ToString()));
sw.WriteLine("|Unsecured Loans {0,15:F} |Loan & Advances {1,15:F} |", GenUtil.strNumericFormat(SqlDtr.GetValue(4).ToString()), GenUtil.strNumericFormat(SqlDtr.GetValue(10).ToString()));
sw.WriteLine("|Current Liabilities {0,15:F} |Profit & Loss A/C {1,15:F} |", GenUtil.strNumericFormat(SqlDtr.GetValue(5).ToString()), GenUtil.strNumericFormat(Net_Loss.ToString()));
sw.WriteLine("|Provisions {0,15:F} |Misc. Expenditure {1,15:F} |", GenUtil.strNumericFormat(SqlDtr.GetValue(6).ToString()), GenUtil.strNumericFormat(SqlDtr.GetValue(11).ToString()));
op_bal_dr = GenUtil.strNumericFormat(SqlDtr.GetValue(0).ToString());
op_bal_cr = GenUtil.strNumericFormat(SqlDtr.GetValue(1).ToString());
if (!Op_Stock.Trim().Equals(""))
{
Opening_Stock = System.Convert.ToDouble(Op_Stock);
}
if (!op_bal_dr.Trim().Equals(""))
{
op_bal_d = System.Convert.ToDouble(op_bal_dr);
op_bal_d = op_bal_d + Opening_Stock;
}
if (!op_bal_cr.Trim().Equals(""))
{
op_bal_c = System.Convert.ToDouble(op_bal_cr);
}
bool d1 = false;
bool d2 = false;
if (op_bal_d != op_bal_c)
{
op_bal_d = op_bal_d - op_bal_c;
if (op_bal_d < 0)
{
op_bal_d = (op_bal_d * -1);
sw.WriteLine("| |Diff. in Op. Balance {0,15:F} |", GenUtil.strNumericFormat(op_bal_d.ToString()));
d2 = true;
}
else
{
sw.WriteLine("|Diff. in Op. Balance {0,15:F} | |", GenUtil.strNumericFormat(op_bal_d.ToString()));
d1 = true;
}
}
else
{
sw.WriteLine("| | |");
}
sw.WriteLine("| | |");
double total11 = 0;
double capital1 = 0;
double Res_Sur = 0;
double Sec_Loan = 0;
double Un_sec_ln = 0;
double Curr_liab = 0;
double Provision = 0;
capital1 = System.Convert.ToDouble(GenUtil.strNumericFormat(SqlDtr.GetValue(2).ToString()));
Res_Sur = System.Convert.ToDouble(GenUtil.strNumericFormat(Net_Pro.ToString()));
Sec_Loan = System.Convert.ToDouble(GenUtil.strNumericFormat(SqlDtr.GetValue(3).ToString()));
Un_sec_ln = System.Convert.ToDouble(GenUtil.strNumericFormat(SqlDtr.GetValue(4).ToString()));
Curr_liab = System.Convert.ToDouble(GenUtil.strNumericFormat(SqlDtr.GetValue(5).ToString()));
Provision = System.Convert.ToDouble(GenUtil.strNumericFormat(SqlDtr.GetValue(6).ToString()));
total11 = capital1 + Res_Sur + Sec_Loan + Un_sec_ln + Curr_liab + Provision;
if (d1 == true)
{
total11 = total11 + op_bal_d;
}
double total22 = 0;
double Fix_Assets = 0;
double investment = 0;
double Curr_assets = 0;
double Pro_loss = 0;
double Misc = 0;
double Loan_adv = 0;
Fix_Assets = System.Convert.ToDouble(GenUtil.strNumericFormat(SqlDtr.GetValue(7).ToString()));
investment = System.Convert.ToDouble(GenUtil.strNumericFormat(SqlDtr.GetValue(8).ToString()));
Curr_assets = System.Convert.ToDouble(GenUtil.strNumericFormat(C_A.ToString()));
Loan_adv = System.Convert.ToDouble(GenUtil.strNumericFormat(SqlDtr.GetValue(10).ToString()));
Pro_loss = System.Convert.ToDouble(GenUtil.strNumericFormat(Net_Loss.ToString()));
Misc = System.Convert.ToDouble(GenUtil.strNumericFormat(SqlDtr.GetValue(11).ToString()));
total22 = Fix_Assets + investment + Curr_assets + Pro_loss + Misc + Loan_adv;
if (d2 == true)
{
total22 = total22 + op_bal_d;
}
sw.WriteLine("|-------------------- |-------------------- |");
sw.WriteLine("|Total {0,15:F} |Total {1,15:F} |", GenUtil.strNumericFormat(total11.ToString()), GenUtil.strNumericFormat(total22.ToString()));
sw.WriteLine("|-------------------- |-------------------- |");
sw.WriteLine("| | |");
sw.WriteLine("+-------------------------------------+-------------------------------------+");
}
sw.Close();
SqlDtr.Close();
Print();
}
catch (Exception ex)
{
CreateLogFiles.ErrorLog("Form:BalanceSheet.aspx,Method:BtnPrint_Click. EXCEPTION: " + ex.Message + " User: " + uid);
}
}
/// <summary>
/// This method is used to make the report for printing.
/// </summary>
public void makingReport()
{
System.Data.SqlClient.SqlDataReader rdr=null;
string home_drive = Environment.SystemDirectory;
home_drive = home_drive.Substring(0,2);
string path = home_drive+@"\Inetpub\wwwroot\Servosms\Sysitem\ServosmsPrintServices\ReportView\MarketPotentialReport.txt";
StreamWriter sw = new StreamWriter(path);
string sql="";
string info = "";
//string strDate = "";
sql="select firmname m1,place m2,contactper m3,teleno m4,type m5,regcustomer m6,potential m7,servo m8,castrol m9,shell m10,bpcl m11,veedol m12,elf m13,hpcl m14,pennzoil m15,spurious m16 from marketcustomerentry1";
sql=sql+" order by "+""+Cache["strorderby"]+"";
dbobj.SelectQuery(sql,ref rdr);
// 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("Market Potential REPORT",des.Length));
sw.WriteLine(GenUtil.GetCenterAddr("=========================",des.Length));
sw.WriteLine("Note --> Reg :- Reguler Customer, Potl :- Potential, pnzoil :- Pennzoil, Spurs :- Spurious");
//sw.WriteLine("+---------------+---------------+---------------+-----------+-------------+-------+---------+-----+-------+-----+-----+------+-----+-----+--------+--------+");
// sw.WriteLine("| Firm Name | Place |Contact Person | Tele No | Type |Regular|Potential|Servo|Castrol|Shell|BPCL |Veedol| ELF |HPCL |Pennzoil|Spurious");
// sw.WriteLine("+---------------+---------------+---------------+-----------+-------------+-------+---------+-----+-------+-----+-----+------+-----+-----+--------+--------+");
sw.WriteLine("+---------------+---------------+---------------+----------+-------------+---+----+-----+-------+-----+----+------+---+----+------+-----+");
sw.WriteLine("| Firm Name | Place |Contact Person | Tele No | Type |Reg|Potl|Servo|Castrol|Shell|BPCL|Veedol|ELF|HPCL|Pnzoil|Spurs");
sw.WriteLine("+---------------+---------------+---------------+----------+-------------+---+----+-----+-------+-----+----+------+---+----+------+-----+");
if(rdr.HasRows)
{
// info : to set the format the displaying string.
info = " {0,-15:S} {1,-15:S} {2,-15:S} {3,10:S} {4,-13:S} {5,-3:S} {6,4:S} {7,5:S} {8,7:S} {9,5:S} {10,4:S} {11,6:S} {12,3:S} {13,4:S} {14,6:S} {15,5:S}";
while(rdr.Read())
{
/*sw.WriteLine(info,rdr["Prod_ID"].ToString().Trim(),
rdr["Prod_Name"].ToString().Trim(),
rdr["Pack_Type"].ToString(),
GenUtil.strNumericFormat(rdr["Pur_Rate"].ToString().Trim()),
GenUtil.strNumericFormat(rdr["sal_Rate"].ToString().Trim()),
GenUtil.str2DDMMYYYY(strDate));*/
sw.WriteLine(info,GenUtil.TrimLength(rdr["m1"].ToString(),15),
GenUtil.TrimLength(rdr["m2"].ToString(),15),
GenUtil.TrimLength(rdr["m3"].ToString(),15),
rdr["m4"].ToString(),
GenUtil.TrimLength(rdr["m5"].ToString(),13),
rdr["m6"].ToString(),
rdr["m7"].ToString(),
rdr["m8"].ToString(),
rdr["m9"].ToString(),
rdr["m10"].ToString(),
rdr["m11"].ToString(),
rdr["m12"].ToString(),
rdr["m13"].ToString(),
rdr["m14"].ToString(),
rdr["m15"].ToString(),
rdr["m16"].ToString());
}
}
//sw.WriteLine("+---------------+---------------+---------------+-----------+-------------+-------+---------+-----+-------+-----+-----+------+-----+-----+--------+--------+");
sw.WriteLine("+---------------+---------------+---------------+----------+-------------+---+----+-----+-------+-----+----+------+---+----+------+-----+");
dbobj.Dispose();
sw.Close();
}
/// <summary>
/// This method is used to write into the report file to print.
/// </summary>
public void makingReport()
{
System.Data.SqlClient.SqlDataReader rdr = null;
string home_drive = Environment.SystemDirectory;
home_drive = home_drive.Substring(0, 2);
string path = home_drive + @"\Inetpub\wwwroot\Servosms\Sysitem\ServosmsPrintServices\ReportView\LY_PS_SalesReport.txt";
StreamWriter sw = new StreamWriter(path);
string sql = "";
string info = "";
//string strDate = "";
//sql="select lr.Emp_ID r1,e.Emp_Name r2,lr.Date_From r3,lr.Date_To r4,lr.Reason r5,lr.isSanction r6 from Employee e,Leave_Register lr where e.Emp_ID=lr.Emp_ID and cast(floor(cast(lr.Date_From as float)) as datetime)>='"+ ToMMddYYYY(txtDateFrom.Text) +"' and cast(floor(cast(lr.Date_To as float)) as datetime)<='"+ ToMMddYYYY(Textbox1.Text) +"'";
sql = "select * from LY_PS_SALES";
//sql=sql+" order by "+Cache["strOrderBy"];
dbobj.SelectQuery(sql, ref rdr);
// 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("LY_PS_Sales REPORT",des.Length));
* sw.WriteLine(GenUtil.GetCenterAddr("====================",des.Length));
*
* sw.WriteLine("+------+-------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------+");
* sw.WriteLine("|Month | Primary Sales | Secondary Sales |");
* sw.WriteLine("+------+-------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------+");
* sw.WriteLine("| | Total | Pur | Gen |Greases| RO1 | RO2 | RO3 | RO4 | RO5 | IBP |Bazzar | OE | Fleet |Maruti |Eicher |Hyundai| Total |");
* sw.WriteLine("| | Pur | FOC | Oils | | Lube | 2T/4T | Lube | 2T/4T | Lube | 2T/4T | Lube | 2T/4T | Lube | 2T/4T | | | | | | | | Sales |");
* sw.WriteLine("+------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+");
* // 123456 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567
*/
if (rdr.HasRows)
{
sw.WriteLine(GenUtil.GetCenterAddr("====================", des.Length));
sw.WriteLine(GenUtil.GetCenterAddr("LY_PS_Sales REPORT", des.Length));
sw.WriteLine(GenUtil.GetCenterAddr("====================", des.Length));
sw.WriteLine("+------+-------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------+");
sw.WriteLine("|Month | Primary Sales | Secondary Sales |");
sw.WriteLine("+------+-------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------+");
sw.WriteLine("| | Total | Pur | Gen |Greases| RO1 | RO2 | RO3 | RO4 | RO5 | IBP |Bazzar | OE | Fleet |Maruti |Eicher |Hyundai| Total |");
sw.WriteLine("| | Pur | FOC | Oils | | Lube | 2T/4T | Lube | 2T/4T | Lube | 2T/4T | Lube | 2T/4T | Lube | 2T/4T | | | | | | | | Sales |");
sw.WriteLine("+------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+");
// 123456 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567
int i = 0;
info = " {0,-6:S} {1,7:F} {2,7:S} {3,7:S} {4,7:S} {5,7:S} {6,7:S} {7,7:S} {8,7:S} {9,7:S} {10,7:S} {11,7:S} {12,7:S} {13,7:S} {14,7:S} {15,7:S} {16,7:S} {17,7:S} {18,7:S} {19,7:S} {20,7:S} {21,7:S} {22,7:S}";
while (rdr.Read())
{
if (i < 12)
{
sw.WriteLine(info, rdr.GetValue(1).ToString(),
rdr.GetValue(2).ToString(),
rdr.GetValue(3).ToString(),
rdr.GetValue(4).ToString(),
rdr.GetValue(5).ToString(),
rdr.GetValue(6).ToString(),
rdr.GetValue(7).ToString(),
rdr.GetValue(8).ToString(),
rdr.GetValue(9).ToString(),
rdr.GetValue(10).ToString(),
rdr.GetValue(11).ToString(),
rdr.GetValue(12).ToString(),
rdr.GetValue(13).ToString(),
rdr.GetValue(14).ToString(),
rdr.GetValue(15).ToString(),
rdr.GetValue(16).ToString(),
rdr.GetValue(17).ToString(),
rdr.GetValue(18).ToString(),
rdr.GetValue(19).ToString(),
rdr.GetValue(20).ToString(),
rdr.GetValue(21).ToString(),
rdr.GetValue(22).ToString(),
rdr.GetValue(23).ToString()
);
}
else
{
sw.WriteLine("+------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+");
sw.WriteLine(info, rdr.GetValue(1).ToString(),
rdr.GetValue(2).ToString(),
rdr.GetValue(3).ToString(),
rdr.GetValue(4).ToString(),
rdr.GetValue(5).ToString(),
rdr.GetValue(6).ToString(),
rdr.GetValue(7).ToString(),
rdr.GetValue(8).ToString(),
rdr.GetValue(9).ToString(),
rdr.GetValue(10).ToString(),
rdr.GetValue(11).ToString(),
rdr.GetValue(12).ToString(),
rdr.GetValue(13).ToString(),
rdr.GetValue(14).ToString(),
rdr.GetValue(15).ToString(),
rdr.GetValue(16).ToString(),
rdr.GetValue(17).ToString(),
rdr.GetValue(18).ToString(),
rdr.GetValue(19).ToString(),
rdr.GetValue(20).ToString(),
rdr.GetValue(21).ToString(),
rdr.GetValue(22).ToString(),
rdr.GetValue(23).ToString()
);
sw.WriteLine("+------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+");
}
i++;
}
}
else
{
MessageBox.Show("Data Not Available");
return;
}
//sw.WriteLine("+------+-------+-------+-------+-------+----------+----------+----------+----------+----------+-----+-----+-----+-----+-----+-----+-----+");
dbobj.Dispose();
sw.Close();
}
/// <summary>
/// Method to prepare the report file .txt to print
/// </summary>
public void makingReport()
{
string home_drive = Environment.SystemDirectory;
home_drive = home_drive.Substring(0, 2);
string path = home_drive + @"\Inetpub\wwwroot\Servosms\Sysitem\ServosmsPrintServices\ReportView\AttendenceReport.txt";
StreamWriter sw = new StreamWriter(path);
//***added by vishnu ***//
sw.Write((char)27);
sw.Write((char)67);
sw.Write((char)0);
sw.Write((char)12);
sw.Write((char)27);
sw.Write((char)78);
sw.Write((char)5);
sw.Write((char)27); //added by vishnu for condensed
sw.Write((char)15); //
sw.WriteLine("");
//**********
/**********Add by vikas 20.12.2012***********************/
string[] DayName = new string[Day]; //add by vikas 20.12.2012
for (int i = 0, j = 1; i < Day; i++, j++)
{
DateTime D_Name = new DateTime(int.Parse(DropYear.SelectedItem.Text), DropMonth.SelectedIndex, j);
string Days = D_Name.DayOfWeek.ToString();
//Days=Days.Substring(0,1);
DayName[i] = Days;
}
/**********End***********************/
string Head = "| |";
string msg = "| Employee Name / Day |";
string des = "+-------------------------+";
int k = 0;
for (int i = 1; i <= Day; i++)
{
if (i.ToString().Length != 1)
{
msg += i.ToString() + "|";
}
else
{
msg += i.ToString() + " |";
}
des += "--+";
/**********add by vikas 20.12.2012**********************/
string Days = DayName[k].Substring(0, 1);
if (i.ToString().Length != 1)
{
Head += Days.ToString() + " |";
}
else
{
Head += Days.ToString() + " |";
}
k++;
/**********End**********************/
}
msg += "Total P|Total A|";
des += "-------+-------+";
Head += " ";
//**********
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));
for (int i = 0; i < des.Length; i++)
{
sw.Write("-");
}
sw.WriteLine();
//**********
sw.WriteLine(GenUtil.GetCenterAddr("-------------------", des.Length));
sw.WriteLine(GenUtil.GetCenterAddr("ATTENDENCE REPORT", des.Length));
sw.WriteLine(GenUtil.GetCenterAddr("-------------------", des.Length));
sw.WriteLine(" Month : " + DropMonth.SelectedItem.Text + ", Year : " + DropYear.SelectedItem.Text);
sw.WriteLine(des);
sw.WriteLine(msg);
sw.WriteLine(Head); // add by vikas 20.12.2012
sw.WriteLine(des);
//***********************************
string[] arr1 = new string[Day];
string[] arr2 = new string[Day];
string FromDate = DropMonth.SelectedIndex + "/1/" + DropYear.SelectedItem.Text;
string ToDate = DropMonth.SelectedIndex + "/" + Day.ToString() + "/" + DropYear.SelectedItem.Text;
InventoryClass obj = new InventoryClass();
SqlDataReader rdr, rdr1 = null;
for (int i = 0, j = 1; i < Day; i++, j++)
{
arr1[i] = DropMonth.SelectedIndex + "/" + j + "/" + DropYear.SelectedItem.Text;
//arr2[i]="A";
}
string emp = "";
//coment by vikas 20.12.2012 dbobj.SelectQuery("select emp_id,emp_name from employee",ref rdr1);
dbobj.SelectQuery("select emp_id,emp_name from employee where status=1", ref rdr1);
while (rdr1.Read())
{
for (int i = 0, j = 1; i < Day; i++, j++)
{
//arr1[i]=DropMonth.SelectedIndex+"/"+j+"/"+DropYear.SelectedItem.Text;
//add by vikas 20.12.2012 arr2[i]="A";
if (DayName[i] == "Sunday")
{
arr2[i] = "S";
}
else
{
arr2[i] = "A";
}
}
int countP = 0, countA = 0;
emp = rdr1.GetValue(1).ToString();
rdr = obj.GetRecordSet("select * from attandance_register where att_date>='" + FromDate + "' and att_date<='" + ToDate + "' and emp_id='" + rdr1.GetValue(0).ToString() + "' and status=1 order by att_date");
while (rdr.Read())
{
for (int i = 0; i < arr1.Length; i++)
{
//if(rdr.GetValue(0).ToString().Equals(arr1[i].ToString()))
if (GenUtil.trimDate(rdr.GetValue(0).ToString()).Equals(arr1[i].ToString()))
{
arr2[i] = "P";
countP++;
break;
}
}
}
rdr.Close();
countA = Day - countP;
sw.Write(" " + emp.ToString());
//for(int i=0;i<=30-emp.Length;i++)
for (int i = 0; i <= 25 - emp.Length; i++)
{
sw.Write(" ");
}
for (int i = 0; i < arr1.Length; i++)
{
sw.Write(arr2[i].ToString() + " ");
}
sw.Write(" " + countP.ToString());
for (int i = 0; i <= 6 - countP.ToString().Length; i++)
{
sw.Write(" ");
}
sw.Write(" " + countA.ToString());
for (int i = 0; i <= 6 - countP.ToString().Length; i++)
{
sw.Write(" ");
}
sw.WriteLine();
}
sw.WriteLine(des);
//***********************************
//deselect condensed
sw.Write((char)27);
sw.Write((char)18); //ad
dbobj.Dispose();
sw.Close();
}
/// <summary>
/// Method to write into the report file to print.
/// </summary>
public void makingReport()
{
System.Data.SqlClient.SqlDataReader rdr = null;
string home_drive = Environment.SystemDirectory;
home_drive = home_drive.Substring(0, 2);
string path = home_drive + @"\Inetpub\wwwroot\Servosms\Sysitem\ServosmsPrintServices\ReportView\DocumentCancelReport.txt";
StreamWriter sw = new StreamWriter(path);
string sql = "";
string info = "";
//string strDate = "";
//sql="select lr.Emp_ID r1,e.Emp_Name r2,lr.Date_From r3,lr.Date_To r4,lr.Reason r5,lr.isSanction r6 from Employee e,Leave_Register lr where e.Emp_ID=lr.Emp_ID and cast(floor(cast(lr.Date_From as float)) as datetime)>='"+ ToMMddYYYY(txtDateFrom.Text) +"' and cast(floor(cast(lr.Date_To as float)) as datetime)<='"+ ToMMddYYYY(Textbox1.Text) +"'";
/*10.07.09 sql="select lr.Emp_ID,e.Emp_Name,lr.Date_From,lr.Date_To,lr.Reason,lr.isSanction from Employee e,Leave_Register lr where e.Emp_ID=lr.Emp_ID and cast(floor(cast(lr.Date_From as float)) as datetime)>='"+ ToMMddYYYY(txtDateFrom.Text) +"' and cast(floor(cast(lr.Date_To as float)) as datetime)<='"+ ToMMddYYYY(Textbox1.Text) +"'";
* sql=sql+" order by "+Cache["strOrderBy"];
* dbobj.SelectQuery(sql,ref rdr);*/
// 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);
//**********
/*10.07.09 Purchase = new ArrayList();
* Sales = new ArrayList();
* Payment = new ArrayList();
* Receipt = new ArrayList();*/
ArrayList counter = new ArrayList();
counter.Add(Purchase.Count.ToString());
counter.Add(Sales.Count.ToString());
counter.Add(Payment.Count.ToString());
counter.Add(Receipt.Count.ToString());
counter.Sort();
int max = Convert.ToInt32(counter[3].ToString());
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);
//******S***
string Purchase_ = "", Sales_ = "", Payment_ = "", Receipt_ = "";
sw.WriteLine(GenUtil.GetCenterAddr("------------------------------", des.Length));
sw.WriteLine(GenUtil.GetCenterAddr("DOCUMENT CANCELLATION REPORT", des.Length));
sw.WriteLine(GenUtil.GetCenterAddr("------------------------------", des.Length));
if (Flag != 0)
{
sw.WriteLine("+----------+------------+----------+----------+");
sw.WriteLine("| Purchase | Sales | Receipt | Payment |");
sw.WriteLine("+----------+------------+----------+----------+");
// 1234567890 123456789012 1234567890 1234567890
info = " {0,-10:S} {1,-12:S} {2,-10:S} {3,-10:S} ";
int Count = 0;
for (int i = 0; i < max; i++)
{
if (Purchase.Count != 0)
{
if (Purchase.Count <= i)
{
Purchase_ = "";
}
else
{
if (Convert.ToString(Purchase[i]) != null && Convert.ToString(Purchase[i]) != "")
{
Purchase_ = Convert.ToString(Purchase[i]);
}
else
{
Purchase_ = "";
}
}
}
else
{
Purchase_ = "";
}
if (Sales.Count != 0)
{
if (Sales.Count <= i)
{
Sales_ = "";
}
else
{
if (Convert.ToString(Sales[i]) != null && Convert.ToString(Sales[i]) != "")
{
Sales_ = Convert.ToString(Sales[i]);
}
else
{
Sales_ = "";
}
}
}
else
{
Sales_ = "";
}
if (Receipt.Count != 0)
{
if (Receipt.Count <= i)
{
Receipt_ = "";
}
else
{
if (Convert.ToString(Receipt[i]) != null && Convert.ToString(Receipt[i]) != "")
{
Receipt_ = Convert.ToString(Receipt[i]);
}
else
{
Receipt_ = "";
}
}
}
else
{
Receipt_ = "";
}
if (Payment.Count != 0)
{
if (Payment.Count <= i)
{
Payment_ = "";
}
else
{
if (Convert.ToString(Payment[i]) != null && Convert.ToString(Payment[i]) != "")
{
Payment_ = Convert.ToString(Payment[i]);
}
else
{
Payment_ = "";
}
}
}
else
{
Payment_ = "";
}
sw.WriteLine(info, Purchase_, Sales_, Receipt_, Payment_);
}
sw.WriteLine("+----------+------------+----------+----------+");
}
//10.07.09 vikas dbobj.Dispose();
sw.Close();
}
/// <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);
}
}
/// <summary>
/// Its fires the query according to selected order and writes the result into file CustomerDataMining.txt.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnPrint_Click(object sender, System.EventArgs e)
{
try
{
string home_drive = Environment.SystemDirectory;
home_drive = home_drive.Substring(0, 2);
string path = home_drive + @"\Inetpub\wwwroot\Servosms\Sysitem\ServosmsPrintServices\ReportView\CustomerDataMining.txt";
StreamWriter sw = new StreamWriter(path);
string info = "";
SqlDataReader SqlDtr = null;
sw.Write((char)15);
sw.WriteLine("");
//*********************
string des = "";
if (chkTesting.Checked)
{
des = "------------------------------------------------------------------------------------";
}
else
{
//des="-----------------------------------------------------------------------------------------------------------------------------------------";
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("Customer Data Mining", des.Length));
sw.WriteLine(GenUtil.GetCenterAddr("======================", des.Length));
if (chkTesting.Checked)
{
sw.WriteLine("+-----------------------------------+-------------------------+--------------------+");
sw.WriteLine("| Customer Name | City | Customer Type |");
sw.WriteLine("+-----------------------------------+-------------------------+--------------------+");
// 12345678901234567890123456789012345 1234567890123456789012345 12345678901234567890
info = "|{0,-35:S}|{1,-25:S}|{2,-20:S}|";
}
else
{
//coment by vikas 26.05.09
/*sw.WriteLine("+-----------------+---------+----------------+-----------+---------+-------+------------------------------+-----------------------------+");
* sw.WriteLine("| | | | | | | Contact Number | |");
* sw.WriteLine("| Customer Name | Type | Address | City | State |Country|--------+---------+-----------| EMail |");
* sw.WriteLine("| | | | | | | Office |Residence| Mobile | |");
* sw.WriteLine("+-----------------+---------+----------------+-----------+---------+-------+--------+---------+-----------+-----------------------------+");
* // 12345678901234567 123456789 1234567890123456 12345678901 123456789 1234567 12345678 123456789 12345678901 12345678901234567890123456789
* info = "|{0,-17:S}|{1,-9:S}|{2,-16:S}|{3,-11:S}|{4,-9:S}|{5,-7:S}|{6,8:S}|{7,9:S}|{8,11:S}|{9,-29:S}|";*/
/********Start add by vikas 26.05.09****************************/
sw.WriteLine("+-----------------+---------+----------------+-----------+-----------------------------------+-----------------------------+");
sw.WriteLine("| | | | | Contact Number | |");
sw.WriteLine("| Customer Name | Type | Address | City |-----------+-----------+-----------| Contact Person |");
sw.WriteLine("| | | | | Office | Residence | Mobile | |");
sw.WriteLine("+-----------------+---------+----------------+-----------+-----------+-----------+-----------+-----------------------------+");
// 12345678901234567 123456789 1234567890123456 12345678901 123456789 1234567 12345678 123456789 12345678901 12345678901234567890123456789
info = "|{0,-17:S}|{1,-9:S}|{2,-16:S}|{3,-11:S}|{4,11:S}|{5,11:S}|{6,11:S}|{7,-29:S}|";
/********end****************************/
}
string sql = "";
string Cust_Name = "";
if (DropSearchBy.SelectedIndex == 0)
{
sql = "Select * from Customer";
}
else
{
if (DropValue.Value == "All")
{
sql = "Select * from Customer";
}
else if (DropSearchBy.SelectedIndex == 1)
{
//coment by vikas 25.05.09 sql="Select * from Customer where cust_name='"+DropValue.Value+"'";
/*******Add by vikas sharma 25.05.09************************/
Cust_Name = DropValue.Value.Substring(0, DropValue.Value.IndexOf(":"));
sql = "Select * from Customer where cust_name='" + Cust_Name.ToString() + "'";
/*******Add by vikas sharma 25.05.09************************/
}
/*Coment by vikas 17.11.2012
* else if(DropSearchBy.SelectedIndex==2)
* sql="Select * from Customer where cust_type like '"+DropValue.Value+"%'";*/
/******Add by vikas 17.11.2012*********************/
else if (DropSearchBy.SelectedIndex == 2)
{
sql = "Select * from Customer where cust_id in(select cust_id from customer c,customertype ct where c.cust_type=ct.customertypename and group_Name ='" + DropValue.Value + "')";
}
else if (DropSearchBy.SelectedIndex == 3)
{
sql = "Select * from Customer where cust_id in(select cust_id from customer c,customertype ct where c.cust_type=ct.customertypename and sub_group_Name ='" + DropValue.Value + "')"; /****End******/
}
else if (DropSearchBy.SelectedIndex == 4)
{
sql = "Select * from Customer where state='" + DropValue.Value + "'";
}
else if (DropSearchBy.SelectedIndex == 5)
{
sql = "Select * from Customer where city='" + DropValue.Value + "'";
}
else if (DropSearchBy.SelectedIndex == 6)
{
sql = "Select * from Customer where ssr=(select emp_id from employee where emp_name='" + DropValue.Value + "')";
}
}
sql += " order by " + Cache["strorderby"];
//dbobj.SelectQuery("Select * from Customer order by "+Cache["strorderby"],ref SqlDtr);
dbobj.SelectQuery(sql, ref SqlDtr);
if (SqlDtr.HasRows)
{
if (chkTesting.Checked)
{
while (SqlDtr.Read())
{
sw.WriteLine(info, GenUtil.TrimLength(SqlDtr["Cust_Name"].ToString().Trim(), 35),
GenUtil.TrimLength(SqlDtr["City"].ToString().Trim(), 25),
GenUtil.TrimLength(SqlDtr["Cust_Type"].ToString().Trim(), 20)
);
}
}
else
{
while (SqlDtr.Read())
{
sw.WriteLine(info, GenUtil.TrimLength(SqlDtr["Cust_Name"].ToString().Trim(), 17),
GenUtil.TrimLength(SqlDtr["Cust_Type"].ToString().Trim(), 9),
GenUtil.TrimLength(trimString(SqlDtr["Address"].ToString().Trim()), 16),
GenUtil.TrimLength(SqlDtr["City"].ToString().Trim(), 11),
//coment by vikas 26.05.09 GenUtil.TrimLength(SqlDtr["State"].ToString().Trim(),9),
//coment by vikas 26.05.09 GenUtil.TrimLength(SqlDtr["Country"].ToString().Trim(),7),
GenUtil.TrimLength(SqlDtr["Tel_Off"].ToString().Trim(), 11),
GenUtil.TrimLength(SqlDtr["Tel_Res"].ToString().Trim(), 11),
GenUtil.TrimLength(SqlDtr["Mobile"].ToString().Trim(), 11),
GenUtil.TrimLength(SqlDtr["ContactPerson"].ToString().Trim(), 29));
}
}
}
else
{
MessageBox.Show("Data not available");
sw.Close();
return;
}
if (chkTesting.Checked)
{
sw.WriteLine("+-----------------------------------+-------------------------+--------------------+");
}
else
{
sw.WriteLine("+-----------------+---------+----------------+-----------+-----------+-----------+-----------+-----------------------------+");
}
sw.Close();
Print();
}
catch (Exception ex)
{
CreateLogFiles.ErrorLog("Form:CustomerDataMining.aspx,Method:btnPrint_Click EXCEPTION " + ex.Message + " User: " + uid);
}
}
/// <summary>
/// This method is used to prepares the report file PrimSecDiscountReport.txt for printing.
/// </summary>
public void makingReport()
{
try
{
//System.Data.SqlClient.SqlDataReader rdr=null;
string info = "";
string home_drive = Environment.SystemDirectory;
home_drive = home_drive.Substring(0, 2);
string path = home_drive + @"\Inetpub\wwwroot\Servosms\Sysitem\ServosmsPrintServices\ReportView\PrimSecDiscountReport.txt";
StreamWriter sw = new StreamWriter(path);
// 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);
info = " {0,-40:S} {1,-5:S} {2,14:S} {3,16:S} {4,-7:S} ";
//**********
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("Primary Secondry Discount Report From " + txtDateFrom.Text.ToString() + " To " + TextBox1.Text.ToString(), des.Length));
sw.WriteLine(GenUtil.GetCenterAddr("==================================================", des.Length));
sw.WriteLine("+----------------------------------------+-----+--------------+----------------+-------+");
sw.WriteLine(info, "Primary Sales Discount", "", "Purchase:", lbltotalpurltr.Text.ToString(), "Ltr./Kg");
sw.WriteLine(info, "1.Early Bird Discount", "", "", lblebird.Text.ToString(), "Rs.");
sw.WriteLine(info, "2.Trade Discount", "", "", lbltrade.Text.ToString(), "Rs.");
sw.WriteLine(info, "3.Cash Discount", "", "", lblcash.Text.ToString(), "Rs.");
sw.WriteLine(info, "4.Fixed (Transport Subcidy) Discount", "", "", lblfixed.Text.ToString(), "Rs.");
sw.WriteLine(info, "5.FOC Discount", "", "", lblfoc.Text.ToString(), "Rs.");
sw.WriteLine(info, "6.Old Rate Discount", "", "", lblold.Text.ToString(), "Rs."); //Add By Vikas Sharma 16.04.09
sw.WriteLine("+----------------------------------------+-----+--------------+----------------+-------+");
sw.WriteLine(info, "Total Primary Discount Recieved", "", "", lblpurtotal.Text.ToString(), "Rs.");
// 1234567890123456789012345678901234567890 12345 123456789012345 123456789012345 1234567 12345678 12345678 123456789
sw.WriteLine("+----------------------------------------+-----+--------------+----------------+-------+");
sw.WriteLine(" ");
sw.WriteLine(" ");
sw.WriteLine("+----------------------------------------+-----+--------------+----------------+-------+");
sw.WriteLine(info, "Secondry Sales Discount", "", "Sales:", GenUtil.strNumericFormat(lbltotalsalltr.Text.ToString()), "Ltr./Kg");
sw.WriteLine(info, "1.Secondry Sales Scheme Discount", "", "", lblsecsale.Text.ToString(), "Rs.");
sw.WriteLine(info, "2.Primary Sales Scheme Discount", "", "", lblprimsale.Text.ToString(), "Rs.");
sw.WriteLine(info, "3.Cash Discount", "", "", lblcashsale.Text.ToString(), "Rs.");
sw.WriteLine(info, "4.Fleet Discount", "", "", lblfleetsale.Text.ToString(), "Rs.");
sw.WriteLine(info, "5.OE Discount", "", "", lbloesale.Text.ToString(), "Rs.");
sw.WriteLine(info, "6.(%) Discount", "", "", lblperdis.Text.ToString(), "Rs."); //Add by vikas 14.08.09
sw.WriteLine("+----------------------------------------+-----+--------------+----------------+-------+");
sw.WriteLine(info, "Total Secondry Discount Passed", "", "", lblsaletotal.Text.ToString(), "Rs.");
// 1234567890123456789012345678901234567890 12345 123456789012345 123456789012345 1234567 12345678 12345678 123456789
sw.WriteLine("+----------------------------------------+-----+--------------+----------------+-------+");
sw.Close();
}
catch (Exception ex)
{
CreateLogFiles.ErrorLog("Form:PrimSecDiscountReport.aspx,Method:makingReport(). EXCEPTION " + ex.Message + " userid " + uid);
}
}
/// <summary>
/// Method to write into the report file to print.
/// </summary>
public void makingReport()
{
InventoryClass obj = new InventoryClass();
System.Data.SqlClient.SqlDataReader rdr = null;
string home_drive = Environment.SystemDirectory;
home_drive = home_drive.Substring(0, 2);
string path = home_drive + @"\Inetpub\wwwroot\Servosms\Sysitem\ServosmsPrintServices\ReportView\SSRPerformance.txt";
StreamWriter sw = new StreamWriter(path);
string sql = "";
string info = "";
//sql="select c.cust_name,sm.cust_id,c.city from sales_master sm,customer c where sm.cust_id=c.cust_id and cast(floor(cast(cast(invoice_date as datetime) as float)) as datetime)>='"+GenUtil.str2MMDDYYYY(txtDateFrom.Text)+"' and cast(floor(cast(cast(invoice_date as datetime) as float)) as datetime)<='"+GenUtil.str2MMDDYYYY(txtDateTo.Text)+"' group by sm.cust_id,cust_name,c.city order by cust_name";
sql = "select Emp_Name,Emp_ID from Employee where Designation='Servo Sales Representative' and status=1 order by Emp_Name";
rdr = obj.GetRecordSet(sql);
// 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);
//******S***
sw.WriteLine(GenUtil.GetCenterAddr("=============================================", des.Length));
sw.WriteLine(GenUtil.GetCenterAddr("SSR Performance From " + txtDateFrom.Text + " To " + txtDateTo.Text, des.Length));
sw.WriteLine(GenUtil.GetCenterAddr("=============================================", des.Length));
//sw.WriteLine("From Date : "+txtDateFrom.Text+", To Date : "+txtDateTo.Text);
sw.WriteLine("+----------------------------------------+--------------+--------------+-------------+-----------------+");
sw.WriteLine("| SSR Name | Sales in Ltr | Sales in Rs. | Receipt | OutStanding |");
sw.WriteLine("+----------------------------------------+--------------+--------------+-------------+-----------------+");
// 1234567890123456789012345678901234567890 12345678901234 12345678901234 1234567890123 12345678901234567
if (rdr.HasRows)
{
info = "|{0,-40:S}|{1,14:F}|{2,14:S}|{3,13:S}|{4,17:S}|";
while (rdr.Read())
{
sw.WriteLine(info, GenUtil.TrimLength(rdr["Emp_Name"].ToString(), 40),
GetSalesLtr(rdr["Emp_ID"].ToString()),
GetSalesRs(rdr["Emp_ID"].ToString()),
GetReceipt(rdr["Emp_ID"].ToString()),
GetOutstanding(rdr["Emp_ID"].ToString())
);
}
}
sw.WriteLine("+----------------------------------------+--------------+--------------+-------------+-----------------+");
sw.WriteLine(info, " Total",
GenUtil.strNumericFormat(TotalAmount[0].ToString()),
GenUtil.strNumericFormat(TotalAmount[1].ToString()),
GenUtil.strNumericFormat(TotalAmount[2].ToString()),
GenUtil.strNumericFormat(TotalAmount[3].ToString())
);
sw.WriteLine("+----------------------------------------+--------------+--------------+-------------+-----------------+");
sw.Close();
}
/// <summary>
/// This method is used to write into the report file to print.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnPrint_Click(object sender, System.EventArgs e)
{
try
{
InventoryClass obj = new InventoryClass();
System.Data.SqlClient.SqlDataReader rdr = null;
string home_drive = Environment.SystemDirectory;
home_drive = home_drive.Substring(0, 2);
string path = home_drive + @"\Inetpub\wwwroot\Servosms\Sysitem\ServosmsPrintServices\ReportView\LubeIndent.txt";
StreamWriter sw = new StreamWriter(path);
string sql = "select * from Indent_Lube il,products p where ssaid='" + DropYear.SelectedItem.Text + DropMonth.SelectedIndex + "' and il.prodcode=p.prod_code order by prodcode";
string info = "";
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);
//******S***
sw.WriteLine(GenUtil.GetCenterAddr("=================================", des.Length));
sw.WriteLine(GenUtil.GetCenterAddr("Lube Indent Report For " + DropMonth.SelectedItem.Text, des.Length));
sw.WriteLine(GenUtil.GetCenterAddr("=================================", des.Length));
sw.WriteLine("+-------+-------+-----------+----------+---------+--------+------------+--------------------+------+-------+--------+-------------------+");
sw.WriteLine("| RSE |SUP.EX.|RETAIL MPSO| SKY TYPE |PACK CODE|PACK QTY|PRODUCT CODE| SKU NAME WITH PACK |INDENT|RECEIPT|DIFFRENT| REMARK |");
sw.WriteLine("+-------+-------+-----------+----------+---------+--------+------------+--------------------+------+-------+--------+-------------------+");
// 1234567 1234567 12345678901 1234567890 123456789 12345678 123456789012 12345678901234567890 123456 1234567 12345678 1234567890123456789
int i = 0;
if (DropMonth.SelectedIndex != 0)
{
int k = DropMonth.SelectedIndex;
StartDate = k + "/1/" + DropYear.SelectedItem.Text;
int day = DateTime.DaysInMonth(int.Parse(DropYear.SelectedItem.Text), k);
EndDate = k + "/" + day + "/" + DropYear.SelectedItem.Text;
}
info = " {0,-7:S} {1,-7:S} {2,-11:S} {3,-10:S} {4,-9:S} {5,-8:S} {6,-12:S} {7,-20:S} {8,6:S} {9,7:S} {10,8:S} {11,-19:S}";
rdr = obj.GetRecordSet(sql);
if (rdr.HasRows)
{
while (rdr.Read())
{
sw.WriteLine(info, GenUtil.TrimLength(rdr["rse"].ToString(), 7),
GenUtil.TrimLength(rdr["supex"].ToString(), 7),
GenUtil.TrimLength(rdr["retailmpso"].ToString(), 11),
GenUtil.TrimLength(rdr["skutype"].ToString(), 10),
rdr["packcode"].ToString(),
rdr["packqty"].ToString(),
rdr["prodcode"].ToString(),
GenUtil.TrimLength(rdr["skunamewithpack"].ToString(), 20),
rdr["indent"].ToString(),
getReceipt(rdr["prodcode"].ToString(), rdr["packqty"].ToString()),
getDiff(getReceipt(rdr["prodcode"].ToString(), rdr["packqty"].ToString()), rdr["Indent"].ToString()),
GenUtil.TrimLength(Request.Params.Get("txtRemark" + i++), 19)
);
}
}
sw.WriteLine("+-------+-------+-----------+----------+---------+--------+------------+--------------------+------+-------+--------+-------------------+");
//dbobj.Dispose();
sw.Close();
print();
CreateLogFiles.ErrorLog("Form:LubeIndent.aspx,Method:Print() Indent Updated For " + DropMonth.SelectedItem.Text + " 2007");
}
catch (Exception ex)
{
CreateLogFiles.ErrorLog("Form:LubeIndent.aspx,Method:update(). EXCEPTION " + ex.Message);
}
}
/// <summary>
/// This method is used to Method to write into the report file to print.
/// </summary>
public void makingReport()
{
try
{
System.Data.SqlClient.SqlDataReader rdr = null;
string home_drive = Environment.SystemDirectory;
home_drive = home_drive.Substring(0, 2);
string path = home_drive + @"\Inetpub\wwwroot\Servosms\Sysitem\ServosmsPrintServices\ReportView\ProductReport.txt";
StreamWriter sw = new StreamWriter(path);
string sql = "";
string info = "";
//string strDate = "";
//sql="select lr.Emp_ID r1,e.Emp_Name r2,lr.Date_From r3,lr.Date_To r4,lr.Reason r5,lr.isSanction r6 from Employee e,Leave_Register lr where e.Emp_ID=lr.Emp_ID and cast(floor(cast(lr.Date_From as float)) as datetime)>='"+ ToMMddYYYY(txtDateFrom.Text) +"' and cast(floor(cast(lr.Date_To as float)) as datetime)<='"+ ToMMddYYYY(Textbox1.Text) +"'";
//Coment by vikas 23.10.09 sql="select Category,Prod_Name,Pack_Type,Prod_Code,Unit,Total_Qty,Store_In from Products";
sql = "select Category,Prod_Name,Pack_Type,Prod_Code,Unit,Total_Qty,Store_In,Prod_id from Products";
/*******Add by vikas 18.07.09***********************/
if (DropValue.Value != "All")
{
if (DropSearchBy.SelectedIndex == 1)
{
string[] pname = DropValue.Value.Split(new char[] { ':' }, DropValue.Value.Length);
sql += " where prod_name='" + pname[0].ToString() + "' and pack_type='" + pname[1].ToString() + "'";
}
else if (DropSearchBy.SelectedIndex == 2)
{
sql += " where Prod_Name='" + DropValue.Value + "'";
}
else if (DropSearchBy.SelectedIndex == 3)
{
sql += " where Pack_Type='" + DropValue.Value + "'";
}
else if (DropSearchBy.SelectedIndex == 4)
{
sql += " where Category='" + DropValue.Value + "'";
}
}
/********End**********************/
sql = sql + " order by " + Cache["strOrderBy"];
dbobj.SelectQuery(sql, ref rdr);
// 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("PRODUCT REPORT", des.Length));
sw.WriteLine(GenUtil.GetCenterAddr("================", des.Length));
/*Coment by vikas 23.10.09 sw.WriteLine("+------------+-------------------------+--------------------------------------------------+---------------+--------+--------+----------+");
* sw.WriteLine("|Product Code| Category | Product Name | Pack Type | unit |Unit Qty| Store In |");
* sw.WriteLine("+------------+-------------------------+--------------------------------------------------+---------------+--------+--------+----------+");
* // 123456789012 1234567890123456789012345 12345678901234567890123456789012345678901234567890 123456789012345 */
sw.WriteLine("+----------+------------+--------------------+--------------------------------------------+---------------+--------+--------+----------+");
sw.WriteLine("|Product ID|Product Code| Category | Product Name | Pack Type | unit |Unit Qty| Store In |");
sw.WriteLine("+----------+------------+--------------------+--------------------------------------------+---------------+--------+--------+----------+");
// 1234567890 123456789012 12345678901234567890 12345678901234567890123456789012345678901234 123456789012345 12345678 12345678 1234567890
//string str="";
if (rdr.HasRows)
{
//Coment by vikas 23.10.09 info = " {0,-12:S} {1,-25:F} {2,-50:S} {3,-15:S} {4,-8:S} {5,8:S} {6,-10:S}";
info = " {0,-10:S} {1,-12:F} {2,-20:S} {3,-44:S} {4,-15:S} {5,8:S} {6,-8:S} {7,-10:S}";
while (rdr.Read())
{
sw.WriteLine(info, rdr["Prod_ID"].ToString(),
rdr["Prod_Code"].ToString(),
rdr["Category"].ToString(),
rdr["Prod_Name"].ToString().Trim(),
rdr["Pack_Type"].ToString(),
rdr["Unit"].ToString(),
rdr["Total_Qty"].ToString(),
rdr["Store_In"].ToString()
);
}
}
sw.WriteLine("+----------+------------+--------------------+--------------------------------------------+---------------+--------+--------+----------+");
dbobj.Dispose();
sw.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
/// <summary>
/// This fucntion does the same and calls the getProfitLoss procedure and writes the values in to TradingAccount.txt file to print.
/// </summary>
protected void BtnPrint_Click(object sender, System.EventArgs e)
{
try
{
if (DateTime.Compare(ToMMddYYYY(txtDateFrom.Text), ToMMddYYYY(txtDateTo.Text)) > 0)
{
MessageBox.Show("Date From Should be less than Date To");
return;
}
string home_drive = Environment.SystemDirectory;
home_drive = home_drive.Substring(0, 2);
string path = home_drive + @"\Inetpub\wwwroot\Servosms\Sysitem\ServosmsPrintServices\ReportView\TradingAccount.txt";
StreamWriter sw = new StreamWriter(path);
/*
*
* ===============
* Trading Account
* ===============
*
* From Date : mm/dd/yyyy
* To Date : mm/dd/yyyy
*
+----------------------------------+----------------------------------+
| Debit Side | Credit Side |
+----------------------------------+----------------------------------+
|Opening Stock 12 |Sales 12 |
|Purchase 12 |Closing Stock 12 |
|Direct Expenses 12 |Direct Income 12 |
| | 12 |
|Gross Profit 12 |Gross Loss 12 |
|----------------- 123456789012.00 |----------------- 12 |
|Total 123456789012.00 |Total 123456789012.00 |
|----------------- |----------------- |
| | |
+----------------------------------+----------------------------------+
| Profit & Loss Account |
+----------------------------------+----------------------------------+
|Gross Loss 12 |Gross Profit 12 |
|Indirect Expenses 12 |Indirect Income 12 |
|Net Profit 12 | Net Loss 12 |
| | 12 |
|----------------- |----------------- |
|Total 12 |Total 12 |
|----------------- |----------------- |
| | |
+----------------------------------+----------------------------------+
*/
// 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("Trading Account", des.Length));
sw.WriteLine(GenUtil.GetCenterAddr("=================", des.Length));
sw.WriteLine("From Date : " + txtDateFrom.Text);
sw.WriteLine("To Date : " + txtDateTo.Text);
sw.WriteLine("");
sw.WriteLine("+----------------------------------+----------------------------------+");
sw.WriteLine("| Debit Side | Credit Side |");
sw.WriteLine("+----------------------------------+----------------------------------+");
SqlConnection con = null;
SqlCommand cmd = null;
SqlDataReader SqlDtr = null;
string gross_profit = "";
double gross_pro = 0;
string Net_Profit = "";
double Net_Pro = 0;
double total1 = 0;
double total2 = 0;
con = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["Servosms"]);
con.Open();
cmd = new SqlCommand("exec getProfitLoss '" + GenUtil.str2MMDDYYYY(txtDateFrom.Text) + "','" + GenUtil.str2MMDDYYYY(txtDateTo.Text) + "'", con);
SqlDtr = cmd.ExecuteReader();
if (SqlDtr.Read())
{
sw.WriteLine("|Opening Stock {0,15:F} |Sales {1,15:F} |", GenUtil.strNumericFormat(SqlDtr.GetValue(0).ToString()), GenUtil.strNumericFormat(SqlDtr.GetValue(2).ToString()));
sw.WriteLine("|Purchase {0,15:F} |Closing Stock {1,15:F} |", GenUtil.strNumericFormat(SqlDtr.GetValue(1).ToString()), GenUtil.strNumericFormat(SqlDtr.GetValue(3).ToString()));
sw.WriteLine("|Direct Expenses {0,15:F} |Direct Income {1,15:F} |", GenUtil.strNumericFormat(SqlDtr.GetValue(4).ToString()), GenUtil.strNumericFormat(SqlDtr.GetValue(5).ToString()));
sw.WriteLine("| | |");
gross_profit = GenUtil.strNumericFormat(SqlDtr.GetValue(6).ToString());
if (!gross_profit.Equals(""))
{
gross_pro = System.Convert.ToDouble(gross_profit);
if (gross_pro < 0)
{
gross_pro = (gross_pro * -1);
total1 = gross_pro;
sw.WriteLine("| |Gross Loss {0,15:F} |", GenUtil.strNumericFormat(gross_pro.ToString()));
}
else
{
total2 = gross_pro;
sw.WriteLine("|Gross Profit {0,15:F} | |", GenUtil.strNumericFormat(gross_pro.ToString()));
}
}
sw.WriteLine("|----------------- |----------------- |");
sw.WriteLine("|Total {0,15:F} |Total {1,15:F} |", GenUtil.strNumericFormat(SqlDtr.GetValue(7).ToString()), GenUtil.strNumericFormat(SqlDtr.GetValue(8).ToString()));
sw.WriteLine("|----------------- |----------------- |");
sw.WriteLine("| | |");
sw.WriteLine("+----------------------------------+----------------------------------+");
sw.WriteLine("| Profit & Loss Account |");
sw.WriteLine("+----------------------------------+----------------------------------+");
if (!gross_profit.Equals(""))
{
gross_pro = System.Convert.ToDouble(gross_profit);
if (gross_pro < 0)
{
gross_pro = (gross_pro * -1);
sw.WriteLine("|Gross Loss {0,15:F} | |", GenUtil.strNumericFormat(gross_pro.ToString()));
}
else
{
sw.WriteLine("| |Gross Profit {0,15:F} |", GenUtil.strNumericFormat(gross_pro.ToString()));
}
}
sw.WriteLine("|Indirect Expenses {0,15:F} |Indirect Income {1,15:F} |", GenUtil.strNumericFormat(SqlDtr.GetValue(9).ToString()), GenUtil.strNumericFormat(SqlDtr.GetValue(10).ToString()));
Net_Profit = GenUtil.strNumericFormat(SqlDtr.GetValue(11).ToString());
if (!Net_Profit.Equals(""))
{
Net_Pro = System.Convert.ToDouble(Net_Profit);
if (Net_Pro < 0)
{
Net_Pro = (Net_Pro * -1);
total2 = total2 + Net_Pro;
sw.WriteLine("| | Net Loss {0,15:F} |", GenUtil.strNumericFormat(Net_Pro.ToString()));
}
else
{
total1 = total1 + Net_Pro;
sw.WriteLine("|Net Profit {0,15:F} | |", GenUtil.strNumericFormat(Net_Pro.ToString()));
}
}
total1 = total1 + System.Convert.ToDouble(GenUtil.strNumericFormat(SqlDtr.GetValue(9).ToString()));
total2 = total2 + System.Convert.ToDouble(GenUtil.strNumericFormat(SqlDtr.GetValue(10).ToString()));
sw.WriteLine("| | |");
sw.WriteLine("|----------------- |----------------- |");
sw.WriteLine("|Total {0,15:F} |Total {1,15:F} |", GenUtil.strNumericFormat(total1.ToString()), GenUtil.strNumericFormat(total2.ToString()));
sw.WriteLine("|----------------- |----------------- |");
sw.WriteLine("| | |");
sw.WriteLine("+----------------------------------+----------------------------------+");
}
SqlDtr.Close();
con.Close();
sw.Close();
Print();
}
catch (Exception ex)
{
CreateLogFiles.ErrorLog("Form:TradingAccount.aspx,Method:BtnPrint_Click" + " EXCEPTION " + ex.Message + " userid " + uid);
}
}
/// <summary>
/// Method to write into the excel report file to print.
/// </summary>
public void ConvertToExcel()
{
string Purchase_ = "", Sales_ = "", Payment_ = "", Receipt_ = "";
InventoryClass obj = new InventoryClass();
SqlDataReader rdr;
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\DocumentCancle_Report.xls";
StreamWriter sw = new StreamWriter(path);
string sql = "";
/*11.07.09 vikas Purchase = new ArrayList();
* Sales = new ArrayList();
* Payment = new ArrayList();
* Receipt = new ArrayList();*/
//11.07.09 vikas sql="select lr.Emp_ID,e.Emp_Name,lr.Date_From,lr.Date_To,lr.Reason,lr.isSanction from Employee e,Leave_Register lr where e.Emp_ID=lr.Emp_ID and cast(floor(cast(lr.Date_From as float)) as datetime)>='"+ ToMMddYYYY(txtDateFrom.Text) +"' and cast(floor(cast(lr.Date_To as float)) as datetime)<='"+ ToMMddYYYY(Textbox1.Text) +"'";
//11.07.09 vikas sql=sql+" order by "+Cache["strOrderBy"];
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(GenUtil.GetCenterAddr("------------------------------",des.Length));
sw.WriteLine(GenUtil.GetCenterAddr("DOCUMENT CANCELLATION REPORT", des.Length));
//sw.WriteLine(GenUtil.GetCenterAddr("------------------------------",des.Length));
ArrayList counter = new ArrayList();
counter.Add(Purchase.Count.ToString());
counter.Add(Sales.Count.ToString());
counter.Add(Payment.Count.ToString());
counter.Add(Receipt.Count.ToString());
counter.Sort();
int max = Convert.ToInt32(counter[3].ToString());
sw.WriteLine("Purchase\tSales\tReceipt\tPayment");
for (int i = 0; i < max; i++)
{
if (Purchase.Count != 0)
{
if (Purchase.Count <= i)
{
Purchase_ = "";
}
else
{
if (Convert.ToString(Purchase[i]) != null && Convert.ToString(Purchase[i]) != "")
{
Purchase_ = Convert.ToString(Purchase[i]);
}
else
{
Purchase_ = "";
}
}
}
else
{
Purchase_ = "";
}
if (Sales.Count != 0)
{
if (Sales.Count <= i)
{
Sales_ = "";
}
else
{
if (Convert.ToString(Sales[i]) != null && Convert.ToString(Sales[i]) != "")
{
Sales_ = Convert.ToString(Sales[i]);
}
else
{
Sales_ = "";
}
}
}
else
{
Sales_ = "";
}
if (Receipt.Count != 0)
{
if (Receipt.Count <= i)
{
Receipt_ = "";
}
else
{
if (Convert.ToString(Receipt[i]) != null && Convert.ToString(Receipt[i]) != "")
{
Receipt_ = Convert.ToString(Receipt[i]);
}
else
{
Receipt_ = "";
}
}
}
else
{
Receipt_ = "";
}
if (Payment.Count != 0)
{
if (Payment.Count <= i)
{
Payment_ = "";
}
else
{
if (Convert.ToString(Payment[i]) != null && Convert.ToString(Payment[i]) != "")
{
Payment_ = Convert.ToString(Payment[i]);
}
else
{
Payment_ = "";
}
}
}
else
{
Payment_ = "";
}
sw.WriteLine(Purchase_ + "\t" + Sales_ + "\t" + Receipt_ + "\t" + Payment_);
}
sw.Close();
/*11.07.09 vikas rdr=obj.GetRecordSet(sql);
* sw.WriteLine("Employee ID\tEmployee Name\tFrom Date\tTo Date\tReason\tApproved");
* while(rdr.Read())
* {
* 11.07.09 vikas sw.WriteLine(rdr["Emp_ID"].ToString()+"\t"+
* rdr["Emp_Name"].ToString()+"\t"+
* GenUtil.str2DDMMYYYY(GenUtil.trimDate(rdr["Date_From"].ToString()))+"\t"+
* GenUtil.str2DDMMYYYY(GenUtil.trimDate(rdr["Date_To"].ToString()))+"\t"+
* rdr["Reason"].ToString()+"\t"+
* Approved(rdr["isSanction"].ToString())
* );
* }
* sw.Close();*/
}
/// <summary>
/// This method is used to write into the report file to print.
/// </summary>
public void makingReport()
{
System.Data.SqlClient.SqlDataReader rdr = null;
string home_drive = Environment.SystemDirectory;
home_drive = home_drive.Substring(0, 2);
string path = home_drive + @"\Inetpub\wwwroot\Servosms\Sysitem\ServosmsPrintServices\ReportView\LeaveReport.txt";
StreamWriter sw = new StreamWriter(path);
string sql = "";
string info = "";
//string strDate = "";
//sql="select lr.Emp_ID r1,e.Emp_Name r2,lr.Date_From r3,lr.Date_To r4,lr.Reason r5,lr.isSanction r6 from Employee e,Leave_Register lr where e.Emp_ID=lr.Emp_ID and cast(floor(cast(lr.Date_From as float)) as datetime)>='"+ ToMMddYYYY(txtDateFrom.Text) +"' and cast(floor(cast(lr.Date_To as float)) as datetime)<='"+ ToMMddYYYY(Textbox1.Text) +"'";
sql = "select lr.Emp_ID,e.Emp_Name,lr.Date_From,lr.Date_To,lr.Reason,lr.isSanction from Employee e,Leave_Register lr where e.Emp_ID=lr.Emp_ID and cast(floor(cast(lr.Date_From as float)) as datetime)>=Convert(datetime,'" + GenUtil.str2DDMMYYYY(Request.Form["txtDateFrom"].ToString()) + "',103) and cast(floor(cast(lr.Date_To as float)) as datetime)<=Convert(datetime,'" + GenUtil.str2DDMMYYYY(Request.Form["Textbox1"].ToString()) + "',103)";
sql = sql + " order by " + Cache["strOrderBy"];
dbobj.SelectQuery(sql, ref rdr);
// 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);
//******S***
sw.WriteLine(GenUtil.GetCenterAddr("================", des.Length));
sw.WriteLine(GenUtil.GetCenterAddr("LEAVE REPORT", des.Length));
sw.WriteLine(GenUtil.GetCenterAddr("================", des.Length));
sw.WriteLine("+-----------+--------------------+----------+----------+--------------------+--------+");
sw.WriteLine("|Employee ID| Employee Name |From Date | To Date | Reason |Approved|");
sw.WriteLine("+-----------+--------------------+----------+----------+--------------------+--------+");
// 12345678901 12345678901234567890 1234567890 1234567890 12345678901234567890 12345678
if (rdr.HasRows)
{
info = " {0,-11:S} {1,-20:F} {2,-10:S} {3,-10:S} {4,-20:S} {5,-6:S}";
while (rdr.Read())
{
sw.WriteLine(info, GenUtil.TrimLength(rdr["Emp_ID"].ToString(), 10),
GenUtil.TrimLength(rdr["Emp_Name"].ToString(), 20),
GenUtil.str2DDMMYYYY(GenUtil.trimDate(rdr["Date_From"].ToString())),
GenUtil.str2DDMMYYYY(GenUtil.trimDate(rdr["Date_To"].ToString())),
GenUtil.TrimLength(rdr["Reason"].ToString(), 20),
Approved(rdr["isSanction"].ToString())
);
}
}
sw.WriteLine("+-----------+--------------------+----------+----------+--------------------+--------+");
dbobj.Dispose();
sw.Close();
}
/// <summary>
/// Method to prepare the report file .txt to print
/// </summary>
public void makingReport()
{
/*
* ========================
* Bank Reconcillation
* ========================
+-------+----------------------+-----------+-----------+-----------+----------+
|Prod_ID| Product Name | Pack_Type | Pur_Rate | Sal_Rate |Eff_Date |
+-------+-------------------------+-----------+-----------+-----------+----------+
* 1001 1234567890123456789012345 1X20777 12345678.00 12345678.00 DD/MM/YYYY
*/
System.Data.SqlClient.SqlDataReader rdr = null;
string home_drive = Environment.SystemDirectory;
home_drive = home_drive.Substring(0, 2);
string path = home_drive + @"\Inetpub\wwwroot\Servosms\Sysitem\ServosmsPrintServices\ReportView\BankReconcillation1.txt";
StreamWriter sw = new StreamWriter(path);
string sql = "";
string info = "";
//string strDate = "";
//sql="select distinct lm.Ledger_Name Cust_Name,alt.Particulars Type,alt.Debit_Amount Debit,alt.Credit_Amount Credit,alt.Entry_Date Entry from Ledger_Master lm,AccountsLedgerTable alt where lm.Ledger_ID=alt.Ledger_ID and alt.Particulars not like('Opening%') and alt.Particulars not like('Credit%') and alt.Particulars not like('Sales%') and alt.Particulars not like('Debit%') and alt.Particulars not like('Purchase%')";// and lm.Ledger_Name not like('Cash%') and lm.Ledger_Name not like('%Bank%')";
sql = "(select lm.Ledger_Name Cust_Name,alt.Particulars Type,alt.Debit_Amount Debit,alt.Credit_Amount Credit,alt.Entry_Date Entry from accountsledgertable alt,ledger_master lm where lm.Ledger_Name='" + DropBank.SelectedItem.Text + "' and cast(floor(cast(entry_date as float)) as datetime)>='" + GenUtil.str2MMDDYYYY(txtDateFrom.Text) + "' and cast(floor(cast(entry_date as float)) as datetime)<='" + GenUtil.str2MMDDYYYY(txtDateTo.Text) + "' and (alt.particulars like 'payment%' or alt.particulars like 'receipt%') and alt.ledger_id=lm.ledger_id and (lm.sub_grp_id='117' or lm.sub_grp_id='126' or lm.sub_grp_id='127'))union(select lm.Ledger_Name Cust_Name,alt.Particulars Type,alt.Debit_Amount Debit,alt.Credit_Amount Credit,alt.Entry_Date Entry from accountsledgertable alt,ledger_master lm where lm.Ledger_Name='" + DropBank.SelectedItem.Text + "' and cast(floor(cast(entry_date as float)) as datetime)>='" + GenUtil.str2MMDDYYYY(txtDateFrom.Text) + "' and cast(floor(cast(entry_date as float)) as datetime)<='" + GenUtil.str2MMDDYYYY(txtDateTo.Text) + "' and alt.particulars like 'contra%' and Credit_amount>0 and alt.ledger_id=lm.ledger_id and (lm.sub_grp_id='117' or lm.sub_grp_id='126' or lm.sub_grp_id='127'))";
dbobj.SelectQuery(sql, ref rdr);
// 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)15);
sw.WriteLine("");
//**********
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("Bank Reconcillation", des.Length));
sw.WriteLine(GenUtil.GetCenterAddr("=======================", des.Length));
sw.WriteLine("+-------------------------+--------------------+-----------+-----------+----------+--------------+");
sw.WriteLine("| Account Name | Voucher Type | Debit | Credit |Posted On |Reconcilled On|");
sw.WriteLine("+-------------------------+--------------------+-----------+-----------+----------+--------------+");
// 1234567890123456789012345 12345678901234567890 12345678901 12345678901 1234567890 12345678901234
// --------DD/MM/YYYY-------
double ddr = 0, ccr = 0;
if (rdr.HasRows)
{
// info : to set the format the displaying string.
info = " {0,-25:S} {1,-20:S} {2,11:S} {3,11:F} {4,10:F} {5,-14:S}";
int i = 0;
while (rdr.Read())
{
ddr += double.Parse(rdr["Debit"].ToString());
ccr += double.Parse(rdr["Credit"].ToString());
if (Request.Params.Get("DropDay" + i) != "Select" && Request.Params.Get("DropMonth" + i) != "Select" && Request.Params.Get("DropYear" + i) != "Select")
{
sw.WriteLine(info, GenUtil.TrimLength(rdr["Cust_Name"].ToString().Trim(), 25),
GenUtil.TrimLength(CheckType(rdr["Type"].ToString().Trim()), 20),
GenUtil.strNumericFormat(rdr["Debit"].ToString()),
GenUtil.strNumericFormat(rdr["Credit"].ToString().Trim()),
GenUtil.trimDate(GenUtil.str2DDMMYYYY(rdr["Entry"].ToString().Trim())),
Request.Params.Get("DropDay" + i) + "/" + Request.Params.Get("DropMonth" + i) + "/" + Request.Params.Get("DropYear" + i)
//GenUtil.str2DDMMYYYY(strDate)
);
}
else
{
sw.WriteLine(info, GenUtil.TrimLength(rdr["Cust_Name"].ToString().Trim(), 25),
GenUtil.TrimLength(CheckType(rdr["Type"].ToString().Trim()), 20),
GenUtil.strNumericFormat(rdr["Debit"].ToString()),
GenUtil.strNumericFormat(rdr["Credit"].ToString().Trim()),
GenUtil.trimDate(GenUtil.str2DDMMYYYY(rdr["Entry"].ToString().Trim())),
""
//GenUtil.str2DDMMYYYY(strDate)
);
}
i++;
}
}
sw.WriteLine("+-------------------------+--------------------+-----------+-----------+----------+--------------+");
sw.WriteLine(info, " Total", "", GenUtil.strNumericFormat(ddr.ToString()), GenUtil.strNumericFormat(ccr.ToString()), "", "");
sw.WriteLine("+-------------------------+--------------------+-----------+-----------+----------+--------------+");
dbobj.Dispose();
sw.Close();
}
/// <summary>
/// Method to write into the report file to print.
/// </summary>
public void makingReport()
{
string home_drive = Environment.SystemDirectory;
home_drive = home_drive.Substring(0, 2);
string path = home_drive + @"\Inetpub\wwwroot\Servosms\Sysitem\ServosmsPrintServices\ReportView\DistrictWiseChannelSalesReport.txt";
StreamWriter sw = new StreamWriter(path);
DBUtil dbobj2 = new DBUtil(System.Configuration.ConfigurationSettings.AppSettings["Servosms"], true);
SqlDataReader rdr1 = null;
ArrayList arrType = new ArrayList();
ArrayList arrState = new ArrayList();
double[] arrTotal = null, arrTotal1 = null;
string Header = "", desdes = "", des = "";
string Header1 = "", desdes1 = "", des1 = "";
//dbobj2.SelectQuery("select distinct state from customer order by state",ref rdr1);
if (radDetails.Checked)
{
dbobj2.SelectQuery("(select distinct case when substring(cust_type,1,2)='Ro' then 'RO' when substring(cust_type,1,2)='Oe' then 'OE' else substring(cust_type,1,2) end from customer where (substring(cust_type,1,2)='Ro' or substring(cust_type,1,2)='oe') group by substring(cust_type,1,2)) union (select customertypename from customertype where (substring(customertypename,1,2)!='Ro' and substring(customertypename,1,2)!='oe') group by customertypename)", ref rdr1);
}
else
{
dbobj2.SelectQuery("(select distinct case when substring(cust_type,1,2)='Ro' then 'RO' when substring(cust_type,1,2)='Oe' then 'OE' when substring(cust_type,1,2)='Ks' then 'KSK' when substring(cust_type,1,2)='N-' then 'N-KSK' else substring(cust_type,1,2) end from customer where (substring(cust_type,1,2)='Ro' or substring(cust_type,1,2)='oe' or substring(cust_type,1,2)='Ks' or substring(cust_type,1,2)='n-') group by substring(cust_type,1,2)) union (select customertypename from customertype where (substring(customertypename,1,2)!='Ro' and substring(customertypename,1,2)!='oe' and substring(customertypename,1,2)!='ks' and substring(customertypename,1,2)!='N-') group by customertypename)", ref rdr1);
}
if (rdr1.HasRows)
{
int cc = 0;
Header = "| Distict/Type ";
desdes = "+--------------";
des = "---------------";
Header1 = "| Distict/Type ";
desdes1 = "+--------------";
des1 = "---------------";
while (rdr1.Read())
{
if (cc > 10)
{
Header1 += "| " + rdr1.GetValue(0).ToString();
for (int i = rdr1.GetValue(0).ToString().Length + 1; i < 10; i++)
{
Header1 += " ";
}
desdes1 += "+----------";
des1 += "-----------";
}
else
{
Header += "| " + rdr1.GetValue(0).ToString();
for (int i = rdr1.GetValue(0).ToString().Length + 1; i < 10; i++)
{
Header += " ";
}
desdes += "+----------";
des += "-----------";
}
arrType.Add(rdr1.GetValue(0).ToString());
cc++;
}
if (cc < 9)
{
Header += "| Total Sales | Monthly Avr. |";
desdes += "+-------------+--------------+";
des += "------------------------------";
}
else
{
Header1 += "| Total Sales | Monthly Avr. |";
desdes1 += "+-------------+--------------+";
des1 += "------------------------------";
}
}
rdr1.Close();
//*********************************
// 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 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);
//******S***
sw.WriteLine(GenUtil.GetCenterAddr("-------------------------------------------------------------------------------------------", des.Length));
sw.WriteLine(GenUtil.GetCenterAddr("District / Channel Wise Summerized Sales Report Form Date : " + txtDateFrom.Text + " To Date : " + txtDateTo.Text, des.Length));
sw.WriteLine(GenUtil.GetCenterAddr("-------------------------------------------------------------------------------------------", des.Length));
//sw.WriteLine(" From Date : "+txtDateFrom.Text+" , To Date : "+txtDateTo.Text);
//*********************************
sw.WriteLine(desdes);
sw.WriteLine(Header);
sw.WriteLine(desdes);
arrTotal = new double[arrType.Count];
double Total = 0, GTotal = 0, GAvrTotal = 0;
//dbobj2.SelectQuery("(select distinct case when substring(cust_type,1,2)='Ro' then 'RO' when substring(cust_type,1,2)='Oe' then 'OE' else substring(cust_type,1,2) end from customer where (substring(cust_type,1,2)='Ro' or substring(cust_type,1,2)='oe') group by substring(cust_type,1,2)) union (select cust_type from customer where (substring(cust_type,1,2)!='Ro' and substring(cust_type,1,2)!='oe') group by cust_type)",ref rdr1);
dbobj2.SelectQuery("select distinct state from customer order by state", ref rdr1);
while (rdr1.Read())
{
arrState.Add(rdr1.GetValue(0).ToString());
}
rdr1.Close();
arrTotal1 = new double[arrState.Count];
int Flag = 0;
for (int i = 0; i < arrState.Count; i++)
{
Total = 0;
sw.Write(" " + arrState[i].ToString());
//for(int k=arrState[i].ToString().Length;k<=16;k++)
for (int k = arrState[i].ToString().Length; k <= 14; k++)
{
sw.Write(" ");
}
for (int j = 0; j < arrType.Count; j++)
{
//dbobj2.SelectQuery("select case when sum(net_amount) is null then '0' else sum(net_amount) end from sales_master sm,customer c where sm.cust_id=c.cust_id and c.state='"+arrState[i].ToString()+"' and cust_type like'"+arrType[j].ToString()+"%' and cast(floor(cast(cast(invoice_date as datetime) as float)) as datetime)>=Convert(datetime,'" + GenUtil.str2DDMMYYYY(Request.Form["txtDateFrom"].ToString()) + "',103) and cast(floor(cast(cast(invoice_date as datetime) as float)) as datetime)<=Convert(datetime,'" + GenUtil.str2DDMMYYYY(Request.Form["txtDateTo"].ToString()) + "',103)",ref rdr1);
//dbobj2.SelectQuery("select case when sum(qty) is null then '0' else sum(qty) end from sales_master sm,customer c,sales_details sd where sm.cust_id=c.cust_id and sd.invoice_no=sm.invoice_no and c.state='"+arrState[i].ToString()+"' and cust_type like'"+arrType[j].ToString()+"%' and cast(floor(cast(cast(invoice_date as datetime) as float)) as datetime)>=Convert(datetime,'" + GenUtil.str2DDMMYYYY(Request.Form["txtDateFrom"].ToString()) + "',103) and cast(floor(cast(cast(invoice_date as datetime) as float)) as datetime)<=Convert(datetime,'" + GenUtil.str2DDMMYYYY(Request.Form["txtDateTo"].ToString()) + "',103)",ref rdr1);
if (j == 8)
{
Flag = 1;
}
if (j == 11)
{
break;
}
if (radDetails.Checked)
{
if (arrType[j].ToString().ToLower() == "ksk")
{
dbobj2.SelectQuery("select case when sum(Total_Qty*Qty) is null then '0' else sum(Total_Qty*Qty) end from sales_master sm,customer c,sales_details sd,Products p where p.Prod_ID=sd.Prod_ID and sm.cust_id=c.cust_id and sd.invoice_no=sm.invoice_no and c.state='" + arrState[i].ToString() + "' and cust_type ='" + arrType[j].ToString() + "' and cast(floor(cast(cast(invoice_date as datetime) as float)) as datetime)>=Convert(datetime,'" + GenUtil.str2DDMMYYYY(Request.Form["txtDateFrom"].ToString()) + "',103) and cast(floor(cast(cast(invoice_date as datetime) as float)) as datetime)<=Convert(datetime,'" + GenUtil.str2DDMMYYYY(Request.Form["txtDateTo"].ToString()) + "',103)", ref rdr1);
}
else
{
dbobj2.SelectQuery("select case when sum(Total_Qty*Qty) is null then '0' else sum(Total_Qty*Qty) end from sales_master sm,customer c,sales_details sd,Products p where p.Prod_ID=sd.Prod_ID and sm.cust_id=c.cust_id and sd.invoice_no=sm.invoice_no and c.state='" + arrState[i].ToString() + "' and cust_type like'" + arrType[j].ToString() + "%' and cast(floor(cast(cast(invoice_date as datetime) as float)) as datetime)>=Convert(datetime,'" + GenUtil.str2DDMMYYYY(Request.Form["txtDateFrom"].ToString()) + "',103) and cast(floor(cast(cast(invoice_date as datetime) as float)) as datetime)<=Convert(datetime,'" + GenUtil.str2DDMMYYYY(Request.Form["txtDateTo"].ToString()) + "',103)", ref rdr1);
}
}
else
{
if (arrType[j].ToString().ToLower() == "ksk")
{
dbobj2.SelectQuery("select case when sum(Total_Qty*Qty) is null then '0' else sum(Total_Qty*Qty) end from sales_master sm,customer c,sales_details sd,Products p where p.Prod_ID=sd.Prod_ID and sm.cust_id=c.cust_id and sd.invoice_no=sm.invoice_no and c.state='" + arrState[i].ToString() + "' and cust_type like'" + arrType[j].ToString() + "%' and cast(floor(cast(cast(invoice_date as datetime) as float)) as datetime)>=Convert(datetime,'" + GenUtil.str2DDMMYYYY(Request.Form["txtDateFrom"].ToString()) + "',103) and cast(floor(cast(cast(invoice_date as datetime) as float)) as datetime)<=Convert(datetime,'" + GenUtil.str2DDMMYYYY(Request.Form["txtDateTo"].ToString()) + "',103)", ref rdr1);
}
else if (arrType[j].ToString().ToLower() == "n-ksk")
{
dbobj2.SelectQuery("select case when sum(Total_Qty*Qty) is null then '0' else sum(Total_Qty*Qty) end from sales_master sm,customer c,sales_details sd,Products p where p.Prod_ID=sd.Prod_ID and sm.cust_id=c.cust_id and sd.invoice_no=sm.invoice_no and c.state='" + arrState[i].ToString() + "' and cust_type like'" + arrType[j].ToString() + "%' and cast(floor(cast(cast(invoice_date as datetime) as float)) as datetime)>=Convert(datetime,'" + GenUtil.str2DDMMYYYY(Request.Form["txtDateFrom"].ToString()) + "',103) and cast(floor(cast(cast(invoice_date as datetime) as float)) as datetime)<=Convert(datetime,'" + GenUtil.str2DDMMYYYY(Request.Form["txtDateTo"].ToString()) + "',103)", ref rdr1);
}
else
{
dbobj2.SelectQuery("select case when sum(Total_Qty*Qty) is null then '0' else sum(Total_Qty*Qty) end from sales_master sm,customer c,sales_details sd,Products p where p.Prod_ID=sd.Prod_ID and sm.cust_id=c.cust_id and sd.invoice_no=sm.invoice_no and c.state='" + arrState[i].ToString() + "' and cust_type like'" + arrType[j].ToString() + "%' and cast(floor(cast(cast(invoice_date as datetime) as float)) as datetime)>=Convert(datetime,'" + GenUtil.str2DDMMYYYY(Request.Form["txtDateFrom"].ToString()) + "',103) and cast(floor(cast(cast(invoice_date as datetime) as float)) as datetime)<=Convert(datetime,'" + GenUtil.str2DDMMYYYY(Request.Form["txtDateTo"].ToString()) + "',103)", ref rdr1);
}
}
if (rdr1.Read())
{
arrTotal[j] += double.Parse(rdr1.GetValue(0).ToString());
sw.Write(rdr1.GetValue(0).ToString());
for (int k = rdr1.GetValue(0).ToString().Length; k <= 10; k++)
{
sw.Write(" ");
}
Total += double.Parse(rdr1.GetValue(0).ToString());
}
}
arrTotal1[i] = Total;
if (Flag == 0)
{
sw.Write(Total.ToString());
for (int k = Total.ToString().Length; k <= 13; k++)
{
sw.Write(" ");
}
string GAvr = System.Convert.ToString(Total / double.Parse(Count.ToString()));
sw.Write(GenUtil.strNumericFormat(GAvr));
for (int k = GAvr.Length; k <= 14; k++)
{
sw.Write(" ");
}
GTotal += Total;
GAvrTotal += double.Parse(GAvr);
}
sw.WriteLine();
}
sw.WriteLine(desdes);
string str = " Total";
sw.Write(str);
for (int k = str.ToString().Length; k <= 15; k++)
{
sw.Write(" ");
}
for (int n = 0; n < arrTotal.Length; n++)
{
if (n > 10)
{
break;
}
sw.Write(arrTotal[n].ToString());
for (int k = arrTotal[n].ToString().Length; k <= 10; k++)
{
sw.Write(" ");
}
}
if (Flag == 0)
{
sw.Write(GTotal.ToString());
for (int k = GTotal.ToString().Length; k <= 13; k++)
{
sw.Write(" ");
}
sw.Write(GenUtil.strNumericFormat(GAvrTotal.ToString()));
for (int k = GAvrTotal.ToString().Length; k <= 14; k++)
{
sw.Write(" ");
}
}
sw.WriteLine();
sw.WriteLine(desdes);
if (Flag == 1)
{
sw.WriteLine();
sw.WriteLine();
sw.WriteLine(desdes1);
sw.WriteLine(Header1);
sw.WriteLine(desdes1);
for (int i = 0; i < arrState.Count; i++)
{
//Total=0;
Total = arrTotal1[i];
sw.Write(" " + arrState[i].ToString());
//for(int k=arrState[i].ToString().Length;k<=16;k++)
for (int k = arrState[i].ToString().Length; k <= 14; k++)
{
sw.Write(" ");
}
for (int j = 11; j < arrType.Count; j++)
{
//dbobj2.SelectQuery("select case when sum(net_amount) is null then '0' else sum(net_amount) end from sales_master sm,customer c where sm.cust_id=c.cust_id and c.state='"+arrState[i].ToString()+"' and cust_type like'"+arrType[j].ToString()+"%' and cast(floor(cast(cast(invoice_date as datetime) as float)) as datetime)>=Convert(datetime,'" + GenUtil.str2DDMMYYYY(Request.Form["txtDateFrom"].ToString()) + "',103) and cast(floor(cast(cast(invoice_date as datetime) as float)) as datetime)<=Convert(datetime,'" + GenUtil.str2DDMMYYYY(Request.Form["txtDateTo"].ToString()) + "',103)",ref rdr1);
//dbobj2.SelectQuery("select case when sum(qty) is null then '0' else sum(qty) end from sales_master sm,customer c,sales_details sd where sm.cust_id=c.cust_id and sd.invoice_no=sm.invoice_no and c.state='"+arrState[i].ToString()+"' and cust_type like'"+arrType[j].ToString()+"%' and cast(floor(cast(cast(invoice_date as datetime) as float)) as datetime)>=Convert(datetime,'" + GenUtil.str2DDMMYYYY(Request.Form["txtDateFrom"].ToString()) + "',103) and cast(floor(cast(cast(invoice_date as datetime) as float)) as datetime)<=Convert(datetime,'" + GenUtil.str2DDMMYYYY(Request.Form["txtDateTo"].ToString()) + "',103)",ref rdr1);
// if(arrType[j].ToString().ToLower()=="ksk")
// dbobj2.SelectQuery("select case when sum(Total_Qty*Qty) is null then '0' else sum(Total_Qty*Qty) end from sales_master sm,customer c,sales_details sd,Products p where p.Prod_ID=sd.Prod_ID and sm.cust_id=c.cust_id and sd.invoice_no=sm.invoice_no and c.state='"+arrState[i].ToString()+"' and cust_type ='"+arrType[j].ToString()+"' and cast(floor(cast(cast(invoice_date as datetime) as float)) as datetime)>=Convert(datetime,'" + GenUtil.str2DDMMYYYY(Request.Form["txtDateFrom"].ToString()) + "',103) and cast(floor(cast(cast(invoice_date as datetime) as float)) as datetime)<=Convert(datetime,'" + GenUtil.str2DDMMYYYY(Request.Form["txtDateTo"].ToString()) + "',103)",ref rdr1);
// else
dbobj2.SelectQuery("select case when sum(Total_Qty*Qty) is null then '0' else sum(Total_Qty*Qty) end from sales_master sm,customer c,sales_details sd,Products p where p.Prod_ID=sd.Prod_ID and sm.cust_id=c.cust_id and sd.invoice_no=sm.invoice_no and c.state='" + arrState[i].ToString() + "' and cust_type like'" + arrType[j].ToString() + "%' and cast(floor(cast(cast(invoice_date as datetime) as float)) as datetime)>=Convert(datetime,'" + GenUtil.str2DDMMYYYY(Request.Form["txtDateFrom"].ToString()) + "',103) and cast(floor(cast(cast(invoice_date as datetime) as float)) as datetime)<=Convert(datetime,'" + GenUtil.str2DDMMYYYY(Request.Form["txtDateTo"].ToString()) + "',103)", ref rdr1);
if (rdr1.Read())
{
arrTotal[j] += double.Parse(rdr1.GetValue(0).ToString());
sw.Write(rdr1.GetValue(0).ToString());
for (int k = rdr1.GetValue(0).ToString().Length; k <= 10; k++)
{
sw.Write(" ");
}
Total += double.Parse(rdr1.GetValue(0).ToString());
}
}
sw.Write(Total.ToString());
for (int k = Total.ToString().Length; k <= 13; k++)
{
sw.Write(" ");
}
string GAvr = System.Convert.ToString(Total / double.Parse(Count.ToString()));
sw.Write(GenUtil.strNumericFormat(GAvr));
for (int k = GAvr.Length; k <= 14; k++)
{
sw.Write(" ");
}
GTotal += Total;
GAvrTotal += double.Parse(GAvr);
sw.WriteLine();
}
// if(Flag!=2)
// {
// Flag=3;
// for(int i=0;i<arrState.Count;i++)
// {
// sw.Write(Total.ToString());
// for(int k=Total.ToString().Length;k<=13;k++)
// {
// sw.Write(" ");
// }
// string GAvr=System.Convert.ToString(Total/double.Parse(Count.ToString()));
// sw.Write(GenUtil.strNumericFormat(GAvr));
// for(int k=GAvr.Length;k<=14;k++)
// {
// sw.Write(" ");
// }
// GTotal+=Total;
// GAvrTotal+=double.Parse(GAvr);
// sw.WriteLine();
// }
// }
//***********
sw.WriteLine(desdes1);
str = " Total";
sw.Write(str);
for (int k = str.ToString().Length; k <= 15; k++)
{
sw.Write(" ");
}
for (int n = 11; n < arrTotal.Length; n++)
{
sw.Write(arrTotal[n].ToString());
for (int k = arrTotal[n].ToString().Length; k <= 10; k++)
{
sw.Write(" ");
}
}
sw.Write(GTotal.ToString());
for (int k = GTotal.ToString().Length; k <= 13; k++)
{
sw.Write(" ");
}
sw.Write(GenUtil.strNumericFormat(GAvrTotal.ToString()));
for (int k = GAvrTotal.ToString().Length; k <= 14; k++)
{
sw.Write(" ");
}
sw.WriteLine();
sw.WriteLine(desdes1);
}
sw.Close();
}
/// <summary>
/// Method to write into the report file to print.
/// </summary>
public void makingReport()
{
if (View == 1)
{
string home_drive = Environment.SystemDirectory;
home_drive = home_drive.Substring(0, 2);
string path = home_drive + @"\Inetpub\wwwroot\Servosms\Sysitem\ServosmsPrintServices\ReportView\ClaimAnalysisReport.txt";
StreamWriter sw = new StreamWriter(path);
InventoryClass obj = new InventoryClass();
SqlDataReader rdr = null;
// 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[] SameMon = { "January" + DropYearFrom.SelectedItem.Text, "February" + DropYearFrom.SelectedItem.Text, "March" + DropYearFrom.SelectedItem.Text, "April" + DropYearFrom.SelectedItem.Text, "May" + DropYearFrom.SelectedItem.Text, "June" + DropYearFrom.SelectedItem.Text, "July" + DropYearFrom.SelectedItem.Text, "August" + DropYearFrom.SelectedItem.Text, "September" + DropYearFrom.SelectedItem.Text, "October" + DropYearFrom.SelectedItem.Text, "November" + DropYearFrom.SelectedItem.Text, "December" + DropYearFrom.SelectedItem.Text };
string[] DiffMon = { "April" + DropYearFrom.SelectedItem.Text, "May" + DropYearFrom.SelectedItem.Text, "June" + DropYearFrom.SelectedItem.Text, "July" + DropYearFrom.SelectedItem.Text, "August" + DropYearFrom.SelectedItem.Text, "September" + DropYearFrom.SelectedItem.Text, "October" + DropYearFrom.SelectedItem.Text, "November" + DropYearFrom.SelectedItem.Text, "December" + DropYearFrom.SelectedItem.Text, "January" + DropYearTo.SelectedItem.Text, "February" + DropYearTo.SelectedItem.Text, "March" + DropYearTo.SelectedItem.Text };
int Flag = 0, Count = 0;
double Tot = 0;
double[] TotalAmt = null;
string des = "", Header = "", subHead = "";
ArrayList Header1 = new ArrayList();
for (int i = 0; i < SameMon.Length; i++)
{
if (Flag == 1)
{
if (DropYearFrom.SelectedIndex == DropYearTo.SelectedIndex)
{
rdr = obj.GetRecordSet("select count(*) from ClaimAnalysis where ClaimID='" + SameMon[i].ToString() + "'");
}
else
{
rdr = obj.GetRecordSet("select count(*) from ClaimAnalysis where ClaimID='" + DiffMon[i].ToString() + "'");
}
if (rdr.Read())
{
if (int.Parse(rdr.GetValue(0).ToString()) > Count)
{
Count = 0;
Flag = 0;
Header1 = new ArrayList();
}
}
rdr.Close();
}
if (Flag == 0)
{
if (DropYearFrom.SelectedIndex == DropYearTo.SelectedIndex)
{
rdr = obj.GetRecordSet("select * from ClaimAnalysis where ClaimID='" + SameMon[i].ToString() + "'");
}
else
{
rdr = obj.GetRecordSet("select * from ClaimAnalysis where ClaimID='" + DiffMon[i].ToString() + "'");
}
if (rdr.HasRows)
{
Flag = 1;
//sw.Write(" Month ");
Header1.Add(" Month ");
Header = "+--------------";
des = "---------------";
while (rdr.Read())
{
Header += "+------------";
des += "-------------";
//sw.Write(rdr["TypeofClaim"].ToString());
string amt = rdr["TypeofClaim"].ToString();
if (rdr["TypeofClaim"].ToString().Length > 13)
{
amt = amt.Substring(0, 12);
//sw.Write(amt);
Header1.Add(" " + amt);
}
else
{
//sw.Write(rdr["TypeofClaim"].ToString());
subHead = rdr["TypeofClaim"].ToString();
for (int m = rdr["TypeofClaim"].ToString().Length; m < 13; m++)
{
//sw.Write(" ");
subHead += " ";
}
Header1.Add(" " + subHead);
}
Count++;
}
//sw.Write("\tTotal");
subHead = " Total";
for (int m = 4; m < 13; m++)
{
//sw.Write(" ");
subHead += " ";
}
Header1.Add(subHead);
Header += "+------------+";
des += "--------------";
//sw.WriteLine();
}
rdr.Close();
}
//else
//{
// break;
//}
}
int NoofCol = Count;
TotalAmt = new double[Count + 1];
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("Claim Analysis Report From " + DropYearFrom.SelectedItem.Text + " to " + DropYearTo.SelectedItem.Text, des.Length));
sw.WriteLine(GenUtil.GetCenterAddr("=========================================", des.Length));
sw.WriteLine(Header);
for (int l = 0; l < Header1.Count; l++)
{
sw.Write(Header1[l].ToString());
}
sw.WriteLine();
sw.WriteLine(Header);
if (Flag == 1)
{
if (DropYearFrom.SelectedIndex == DropYearTo.SelectedIndex)
{
for (int i = 0; i < SameMon.Length; i++)
{
rdr = obj.GetRecordSet("select * from ClaimAnalysis where ClaimID='" + SameMon[i].ToString() + "'");
if (rdr.HasRows)
{
Tot = 0; Count = -1;
sw.Write(" " + SameMon[i].ToString());
for (int m = SameMon[i].ToString().Length; m < 15; m++)
{
sw.Write(" ");
}
while (rdr.Read())
{
sw.Write(rdr["Amount"].ToString());
for (int m = rdr["Amount"].ToString().Length; m < 13; m++)
{
sw.Write(" ");
}
Tot += double.Parse(rdr["Amount"].ToString());
TotalAmt[++Count] += double.Parse(rdr["Amount"].ToString());
//MessageBox.Show(i.ToString()+"::"+Count.ToString()+":"+TotalAmt[Count].ToString());
}
while (NoofCol != Count + 1)
{
sw.Write("0 ");
Count++;
}
sw.Write(Tot.ToString());
for (int m = Tot.ToString().Length; m < 14; m++)
{
sw.Write(" ");
}
sw.WriteLine();
TotalAmt[++Count] += Tot;
}
else
{
sw.Write(" " + SameMon[i].ToString());
for (int m = SameMon[i].ToString().Length; m < 15; m++)
{
sw.Write(" ");
}
for (int k = 0; k < TotalAmt.Length; k++)
{
sw.Write("0");
for (int m = 1; m < 14; m++)
{
sw.Write(" ");
}
}
sw.WriteLine();
}
rdr.Close();
}
}
else
{
for (int i = 0; i < DiffMon.Length; i++)
{
rdr = obj.GetRecordSet("select * from ClaimAnalysis where ClaimID='" + DiffMon[i].ToString() + "'");
if (rdr.HasRows)
{
Tot = 0; Count = -1;
sw.Write(" " + DiffMon[i].ToString());
for (int m = DiffMon[i].ToString().Length; m < 15; m++)
{
sw.Write(" ");
}
while (rdr.Read())
{
sw.Write(rdr["Amount"].ToString());
//for(int m=DiffMon[i].ToString().Length;m<14;m++)
for (int m = rdr["Amount"].ToString().Length; m < 13; m++)
{
sw.Write(" ");
}
Tot += double.Parse(rdr["Amount"].ToString());
TotalAmt[++Count] += double.Parse(rdr["Amount"].ToString());
//MessageBox.Show(i.ToString()+"::"+Count.ToString()+":"+TotalAmt[Count].ToString());
}
while (NoofCol != Count + 1)
{
sw.Write("0 ");
Count++;
}
sw.Write(Tot.ToString());
for (int m = Tot.ToString().Length; m < 14; m++)
{
sw.Write(" ");
}
sw.WriteLine();
TotalAmt[++Count] += Tot;
}
else
{
sw.Write(" " + DiffMon[i].ToString());
for (int m = DiffMon[i].ToString().Length; m < 15; m++)
{
sw.Write(" ");
}
for (int k = 0; k < TotalAmt.Length; k++)
{
sw.Write("0");
for (int m = 2; m < 14; m++)
{
sw.Write(" ");
}
}
sw.WriteLine();
}
rdr.Close();
}
}
sw.WriteLine(Header);
sw.Write(" Total");
for (int m = 4; m < 13; m++)
{
sw.Write(" ");
}
for (int j = 0; j < TotalAmt.Length; j++)
{
sw.Write(" " + TotalAmt[j].ToString());
for (int m = TotalAmt[j].ToString().Length; m < 12; m++)
{
sw.Write(" ");
}
}
sw.WriteLine();
sw.WriteLine(Header);
}
else
{
MessageBox.Show("Data Not Available");
}
sw.Close();
}
}
public void makingReport()
{
Qty_Tot_ltr = 0;
InventoryClass obj = new InventoryClass();
System.Data.SqlClient.SqlDataReader rdr = null;
string home_drive = Environment.SystemDirectory;
home_drive = home_drive.Substring(0, 2);
string path = home_drive + @"\Inetpub\wwwroot\Servosms\Sysitem\ServosmsPrintServices\ReportView\Prod_Promo_Dis_Claim_Report.txt";
StreamWriter sw = new StreamWriter(path);
string sql = "";
string info = "";
//05.06.09 sql="select sp.supp_name,pm.vndr_invoice_no,pm.vndr_invoice_date,p.prod_name+':'+p.pack_type,pd.qty from supplier sp,purchase_master pm,purchase_details pd,products p where pm.invoice_no=pd.invoice_no and sp.supp_id=pm.vendor_id and p.prod_id=pd.prod_id and pm.vndr_invoice_date>='"+GenUtil.str2MMDDYYYY(txtDateFrom.Text)+"' and pm.vndr_invoice_date<='"+GenUtil.str2MMDDYYYY(txtDateTo.Text)+"' and pd.prod_id in (select prodid from Prod_Promo_Grade_Entry where datefrom>='"+GenUtil.str2MMDDYYYY(txtDateFrom.Text)+"' and dateto<='"+GenUtil.str2MMDDYYYY(txtDateTo.Text)+"') order by p.prod_name,p.pack_type";
if (DropSchemName.SelectedIndex == 0)
{
sql = "select sp.supp_name,pm.vndr_invoice_no,pm.vndr_invoice_date,p.prod_name+':'+p.pack_type,pd.qty from supplier sp,purchase_master pm,purchase_details pd,products p where pm.invoice_no=pd.invoice_no and sp.supp_id=pm.vendor_id and p.prod_id=pd.prod_id and pm.vndr_invoice_date>='" + GenUtil.str2MMDDYYYY(Request.Form["txtDateFrom"]) + "' and pm.vndr_invoice_date<='" + GenUtil.str2MMDDYYYY(Request.Form["txtDateTo"]) + "' and pd.prod_id in (select prodid from Prod_Promo_Grade_Entry where datefrom>='" + GenUtil.str2MMDDYYYY(Request.Form["txtDateFrom"]) + "' and dateto<='" + GenUtil.str2MMDDYYYY(Request.Form["txtDateTo"]) + "') order by p.prod_name,p.pack_type";
}
else
{
sql = "select sp.supp_name,pm.vndr_invoice_no,pm.vndr_invoice_date,p.prod_name+':'+p.pack_type,pd.qty from supplier sp,purchase_master pm,purchase_details pd,products p where pm.invoice_no=pd.invoice_no and sp.supp_id=pm.vendor_id and p.prod_id=pd.prod_id and pm.vndr_invoice_date>='" + GenUtil.str2MMDDYYYY(Request.Form["txtDateFrom"]) + "' and pm.vndr_invoice_date<='" + GenUtil.str2MMDDYYYY(Request.Form["txtDateTo"]) + "' and pd.prod_id in (select prodid from Prod_Promo_Grade_Entry where schname='" + DropSchemName.SelectedItem.Value.ToString() + "' and datefrom>='" + GenUtil.str2MMDDYYYY(Request.Form["txtDateFrom"]) + "' and dateto<='" + GenUtil.str2MMDDYYYY(Request.Form["txtDateTo"]) + "') order by p.prod_name,p.pack_type";
}
rdr = obj.GetRecordSet(sql);
// 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 = "--------------------------------------------------------------------------------------------------------------";
sw.WriteLine(des);
//******S***
sw.WriteLine(GenUtil.GetCenterAddr("=============================================", des.Length));
sw.WriteLine(GenUtil.GetCenterAddr("Product Promotion Scheme Discount Claim Report From " + txtDateFrom.Text + " To " + txtDateTo.Text, des.Length));
sw.WriteLine(GenUtil.GetCenterAddr("=============================================", des.Length));
//sw.WriteLine("From Date : "+txtDateFrom.Text+", To Date : "+txtDateTo.Text);
sw.WriteLine("+--------------------+------------+--------------+------------------------------+------------+------------+");
sw.WriteLine("| Vendor Name |Invoice No. | Invoice Date | Product Name | Qty in Nos.| Qty in ltr.|");
sw.WriteLine("+--------------------+------------+--------------+------------------------------+------------+------------+");
// 12345678901234567890 123456789012 12345678901234 123456789012345678901234567890 123456789012 123456789012
info = " {0,-20:S} {1,12:F} {2,14:S} {3,-30:S} {4,12:S} {5,12:S} ";
string info1 = " {0,-20:S} {1,12:F} {2,14:S} {3,-30:S} {4,12:S} {5,12:S} ";
while (rdr.Read())
{
sw.WriteLine(info,
rdr.GetValue(0).ToString(),
rdr.GetValue(1).ToString(),
GenUtil.str2DDMMYYYY(GenUtil.trimDate(rdr.GetValue(2).ToString())),
rdr.GetValue(3).ToString(), rdr.GetValue(4).ToString(),
Qtyinltr(rdr.GetValue(3).ToString(), rdr.GetValue(4).ToString()));
Qty_Tot += Convert.ToDouble(rdr.GetValue(4).ToString());
}
rdr.Close();
sw.WriteLine("+--------------------+------------+--------------+------------------------------+------------+------------+");
sw.WriteLine(info1, "Total", "", "", "", Qty_Tot.ToString(), Qty_Tot_ltr.ToString());
sw.WriteLine("+--------------------+------------+--------------+------------------------------+------------+------------+");
sw.Close();
}
/// <summary>
/// Method to prepare the .txt report file.
/// </summary>
public void makingReport()
{
/*
* =====================================================
* Vehicle Log Book Report From mm/dd/yyyy To mm/dd/yyyy
* =====================================================
* Vehicle No. : MH 09 78787
*
+------+-----------+------+-----+------+-----+-------+------+-----------------------------+--------+--------+------+-------+
| Fuel | Vehicle |Engine|Gear |Grease|Brake|Coolent|Trans.| Expenses (In Rs.) |Opening |Closing | KM. | |
|Inward| Route |Oil |Oil | Used |Oil | Used |Oil |------+--------+------+------| Meter | Meter | Move |Mileage|
| | |Used |Used | |Used | |Used | Toll | Police | Food | Misc.|Reading |Reading | | |
+------+-----------+------+-----+------+-----+-------+------+------+--------+------+------+--------+--------+------+-------+
| 123456 ########### 123456 12345 123456 12345 1234567 123456 123456 12345678 123456 123456 12345678 12345678 123456 1234.00
*/
try
{
if (!checkValidity())
{
f = false;
return;
}
string home_drive = Environment.SystemDirectory;
home_drive = home_drive.Substring(0, 2);
string path = home_drive + @"\Inetpub\wwwroot\Servosms\Sysitem\ServosmsPrintServices\ReportView\VehicleLogBookReport.txt";
StreamWriter sw = new StreamWriter(path);
SqlDataReader SqlDtr = null;
SqlDataReader SqlDtr1 = null;
//Mahesh 05.05.007 Start
/*
* sw.Write((char)27);
* sw.Write('O');
* sw.Write('P');
* sw.Write((char)15);
*/
//Mahesh 05.05.007 End
// Condensed
sw.Write((char)27);
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("Vehicle Log Book Report From " + txtDateFrom.Text.Trim() + " To " + txtDateTo.Text.Trim(), des.Length));
sw.WriteLine(GenUtil.GetCenterAddr("=====================================================", des.Length));
sw.WriteLine("Vehicle No. : " + Dropvehicleno.SelectedItem.Text.Substring(0, Dropvehicleno.SelectedItem.Text.IndexOf("VID")).Trim());
//sw.WriteLine("");
sw.WriteLine("+------+-----------------------+------+-----+------+-----+-------+------+-----------------------------+--------+--------+------+-------+");
sw.WriteLine("| | |Engine|Gear |Grease|Brake|Coolent|Trans.| Expenses (In Rs.) |Opening |Closing | | |");
sw.WriteLine("| Fuel | Vehicle Route |Oil |Oil | Used |Oil | Used |Oil |------+--------+------+------| Meter | Meter | KM. |Mileage|");
sw.WriteLine("|Inward| |Used |Used | |Used | |Used | Toll | Police | Food | Misc.|Reading |Reading | Move | |");
sw.WriteLine("+------+-----------------------+------+-----+------+-----+-------+------+------+--------+------+------+--------+--------+------+-------+");
// 123456 ########### 123456 12345 123456 12345 1234567 123456 123456 12345678 123456 123456 12345678 12345678 123456 1234.00
// 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
//info : to set string format.
string info = " {0,6:f} {1,-23:S} {2,6:f} {3,5:f} {4,6:f} {5,5:f} {6,7:f} {7,6:f} {8,6:f} {9,8:f} {10,6:f} {11,6:f} {12,8:f} {13,8:f} {14,6:f} {15,7:f}";
string route = "";
dbobj.SelectQuery("Select *,(Meter_reading_Cur - Meter_Reading_Pre) as KM,((Meter_reading_Cur - Meter_Reading_Pre)/Fuel_Used_Qty) as Mileage from VDLB where vehicle_no = right('" + Dropvehicleno.SelectedItem.Text.Trim() + "',4) and cast(floor(cast(DOE as float)) as datetime) >= Convert(datetime,'" + GenUtil.str2DDMMYYYY(Request.Form["txtDateFrom"].ToString()) + "',103) and cast(floor(cast(DOE as float)) as datetime) <= Convert(datetime,'" + GenUtil.str2DDMMYYYY(Request.Form["txtDateTo"].ToString()) + "',103)", ref SqlDtr);
if (SqlDtr.HasRows)
{
while (SqlDtr.Read())
{
route = "";
dbobj.SelectQuery("Select route_name from route where route_id =" + SqlDtr["Vehicle_Route"].ToString().Trim(), ref SqlDtr1);
if (SqlDtr1.Read())
{
route = SqlDtr1.GetValue(0).ToString();
}
SqlDtr1.Close();
sw.WriteLine(info, SqlDtr["Fuel_Used_Qty"].ToString().Trim(),
route,
SqlDtr["Engine_Oil_Qty"].ToString().Trim(),
SqlDtr["Gear_Oil_Qty"].ToString().Trim(),
SqlDtr["Grease_Qty"].ToString().Trim(),
SqlDtr["Brake_Oil_Qty"].ToString().Trim(),
SqlDtr["Coolent_Qty"].ToString().Trim(),
SqlDtr["Trans_Oil_Qty"].ToString().Trim(),
SqlDtr["Toll"].ToString().Trim(),
SqlDtr["Police"].ToString().Trim(),
SqlDtr["Food"].ToString().Trim(),
SqlDtr["Misc"].ToString().Trim(),
SqlDtr["Meter_Reading_Pre"].ToString().Trim(),
SqlDtr["Meter_Reading_Cur"].ToString().Trim(),
SqlDtr["KM"].ToString().Trim(),
GenUtil.strNumericFormat(SqlDtr["Mileage"].ToString().Trim()));
}
}
else
{
MessageBox.Show("Data not available");
SqlDtr.Close();
sw.Close();
f = false;
return;
}
SqlDtr.Close();
sw.WriteLine("+------+-----------------------+------+-----+------+-----+-------+------+------+--------+------+------+--------+--------+------+-------+");
// deselect Condensed
//sw.Write((char)18);
//sw.Write((char)12);
sw.Close();
}
catch (Exception ex)
{
CreateLogFiles.ErrorLog("Form:vehicleLogReport.aspx,Method:makingReport() " + " EXCEPTION " + ex.Message + " " + uid);
}
}
/// <summary>
/// This method is used to make the format for print .
/// </summary>
public void makingReport()
{
/*
* ======================
* PRICE LIST REPORT
* ======================
+-------+----------------------+-----------+-----------+-----------+----------+
|Prod_ID| Product Name | Pack_Type | Pur_Rate | Sal_Rate |Eff_Date |
+-------+-------------------------+-----------+-----------+-----------+----------+
* 1001 1234567890123456789012345 1X20777 12345678.00 12345678.00 DD/MM/YYYY
*/
System.Data.SqlClient.SqlDataReader rdr = null;
string home_drive = Environment.SystemDirectory;
home_drive = home_drive.Substring(0, 2);
string path = home_drive + @"\Inetpub\wwwroot\Servosms\Sysitem\ServosmsPrintServices\ReportView\MechanicReport.txt";
StreamWriter sw = new StreamWriter(path);
string sql = "";
string info = "";
//string strDate = "";
sql = "select distinct b.state r1,me.mccd r2,me.mcname r3,me.mctype r4,me.place r5,cme.customername r6,c.cust_type r7 from beat_master b,machanic_entry me, customermechanicentry cme,customer c where b.state in(select state from beat_master where city =me.place) and cme.customername in(select customername from customermechanicentry where customermechid=me.custid) and c.cust_type in(select cust_type from customer where cust_name=cme.customername)";
sql = sql + " order by " + Cache["strorderby"];
dbobj.SelectQuery(sql, ref rdr);
// 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("MECHANIC REPORT", des.Length));
sw.WriteLine(GenUtil.GetCenterAddr("=================", des.Length));
sw.WriteLine("+----------+-------------+---------------+----------+-------------------------+---------------+---------------+");
sw.WriteLine("| District |Mechanic Code| Name | Type | Under Firm | Firm Type | Place |");
sw.WriteLine("+----------+-------------+---------------+----------+-------------------------+---------------+---------------+");
// 1001567 12345678901234567890123 12345678901 12345678.00 12345678.00 DD/MM/YYYY
if (rdr.HasRows)
{
// info : to set the format the displaying string.
info = " {0,-10:S} {1,-13:F} {2,-15:S} {3,-10:S} {4,-25:S} {5,-15:S} {6,-15:S}";
while (rdr.Read())
{
/*sw.WriteLine(info,rdr["Prod_ID"].ToString().Trim(),
* rdr["Prod_Name"].ToString().Trim(),
* rdr["Pack_Type"].ToString(),
* GenUtil.strNumericFormat(rdr["Pur_Rate"].ToString().Trim()),
* GenUtil.strNumericFormat(rdr["sal_Rate"].ToString().Trim()),
* GenUtil.str2DDMMYYYY(strDate));*/
sw.WriteLine(info, GenUtil.TrimLength(rdr["r1"].ToString(), 10),
rdr["r2"].ToString(),
GenUtil.TrimLength(rdr["r3"].ToString(), 15),
GenUtil.TrimLength(rdr["r4"].ToString(), 10),
GenUtil.TrimLength(rdr["r6"].ToString(), 25),
GenUtil.TrimLength(rdr["r7"].ToString(), 15),
GenUtil.TrimLength(rdr["r5"].ToString(), 15));
}
}
sw.WriteLine("+----------+-------------+---------------+----------+-------------------------+---------------+---------------+");
dbobj.Dispose();
sw.Close();
}
/// <summary>
/// This method is used to prepares the report file PrimarySecSalesAnalysys.txt for printing.
/// </summary>
public void makingReport()
{
try
{
string home_drive = Environment.SystemDirectory;
home_drive = home_drive.Substring(0, 2);
string path = home_drive + @"\Inetpub\wwwroot\Servosms\Sysitem\ServosmsPrintServices\ReportView\PrimarySecAnalysis.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("Primary Secondary Sales Analysis Report From Year " + DropYearFrom.SelectedItem.Text + " To Year " + DropYearTo.SelectedItem.Text, des.Length));
sw.WriteLine(GenUtil.GetCenterAddr("===================================================================", des.Length));
//sw.WriteLine(" From Year : "+DropYearFrom.SelectedItem.Text+", To Year : "+DropYearTo.SelectedItem.Text);
sw.WriteLine("+----------+-------------------------------------------------------------+-------------------------------------------------------------+");
sw.WriteLine("| | Primary Sales | Secondary Sales |");
sw.WriteLine("+----------+----------+---------+---------+---------+---------+----------+----------+---------+---------+---------+---------+----------+");
sw.WriteLine("| Month | Lube | 2T | 4T | Grease |gen. Oils| Total | Lube | 2T | 4T | Grease |gen. Oils| Total |");
sw.WriteLine("+----------+----------+---------+---------+---------+---------+----------+----------+---------+---------+---------+---------+----------+");
// 1234567890 123456789 123456789 123456789 123456789 123456789 1234567890 123456789 123456789 123456789 123456789 123456789 1234567890
info = " {0,-10:S} {1,10:F} {2,9:S} {3,9:S} {4,9:S} {5,9:S} {6,10:S} {7,10:F} {8,9:S} {9,9:S} {10,9:S} {11,9:S} {12,10:S}";
string[] DiffMon = { "Apr." + DropYearFrom.SelectedItem.Text, "May." + DropYearFrom.SelectedItem.Text, "Jun." + DropYearFrom.SelectedItem.Text, "Jul." + DropYearFrom.SelectedItem.Text, "Aug." + DropYearFrom.SelectedItem.Text, "Sep." + DropYearFrom.SelectedItem.Text, "Oct." + DropYearFrom.SelectedItem.Text, "Nov." + DropYearFrom.SelectedItem.Text, "Dec." + DropYearFrom.SelectedItem.Text, "Jan." + DropYearTo.SelectedItem.Text, "Feb." + DropYearTo.SelectedItem.Text, "Mar." + DropYearTo.SelectedItem.Text };
string[] SameMon = { "Jan." + DropYearFrom.SelectedItem.Text, "Feb." + DropYearFrom.SelectedItem.Text, "Mar." + DropYearFrom.SelectedItem.Text, "Apr." + DropYearFrom.SelectedItem.Text, "May." + DropYearFrom.SelectedItem.Text, "Jun." + DropYearFrom.SelectedItem.Text, "Jul." + DropYearFrom.SelectedItem.Text, "Aug." + DropYearFrom.SelectedItem.Text, "Sep." + DropYearFrom.SelectedItem.Text, "Oct." + DropYearFrom.SelectedItem.Text, "Nov." + DropYearFrom.SelectedItem.Text, "Dec." + DropYearFrom.SelectedItem.Text };
double[] TotalAmt = new double[8];
int k = 0;
SecSales = 0;
PrimarySales = 0;
if (DropYearFrom.SelectedIndex == DropYearTo.SelectedIndex)
{
for (int i = 0; i < SameMon.Length; i++)
{
TotalPrimarySales = 0;
TotalSecSales = 0;
k = 0;
sw.WriteLine(info, SameMon[i].ToString(), GetPrimaryLubeSales(SameMon[i].ToString()), GetPrimarySales("2T", SameMon[i].ToString()), GetPrimarySales("4T", SameMon[i].ToString()), GetPrimarySales("Grease", SameMon[i].ToString()), GetPrimarySalesGen(SameMon[i].ToString()), GenUtil.strNumericFormat(TotalPrimarySales.ToString()), GetSecLubeSales(SameMon[i].ToString()), GetSecSales("2T", SameMon[i].ToString()), GetSecSales("4T", SameMon[i].ToString()), GetSecSales("Grease", SameMon[i].ToString()), GetSecSalesGen(SameMon[i].ToString()), GenUtil.strNumericFormat(TotalSecSales.ToString()));
PrimarySales += TotalPrimarySales;
SecSales += TotalSecSales;
TotalAmt[k] += double.Parse(GetPrimarySales("2T", SameMon[i].ToString()));
TotalAmt[++k] += double.Parse(GetPrimarySales("4T", SameMon[i].ToString()));
TotalAmt[++k] += double.Parse(GetPrimarySales("Grease", SameMon[i].ToString()));
TotalAmt[++k] += double.Parse(GetPrimarySalesGen(SameMon[i].ToString()));
TotalAmt[++k] += double.Parse(GetSecSales("2T", SameMon[i].ToString()));
TotalAmt[++k] += double.Parse(GetSecSales("4T", SameMon[i].ToString()));
TotalAmt[++k] += double.Parse(GetSecSales("Grease", SameMon[i].ToString()));
TotalAmt[++k] += double.Parse(GetSecSalesGen(SameMon[i].ToString()));
}
}
else
{
for (int i = 0; i < SameMon.Length; i++)
{
TotalPrimarySales = 0;
TotalSecSales = 0;
k = 0;
sw.WriteLine(info, DiffMon[i].ToString(), GetPrimaryLubeSales(DiffMon[i].ToString()), GetPrimarySales("2T", DiffMon[i].ToString()), GetPrimarySales("4T", DiffMon[i].ToString()), GetPrimarySales("Grease", DiffMon[i].ToString()), GetPrimarySalesGen(DiffMon[i].ToString()), GenUtil.strNumericFormat(TotalPrimarySales.ToString()), GetSecLubeSales(DiffMon[i].ToString()), GetSecSales("2T", DiffMon[i].ToString()), GetSecSales("4T", DiffMon[i].ToString()), GetSecSales("Grease", DiffMon[i].ToString()), GetSecSalesGen(DiffMon[i].ToString()), GenUtil.strNumericFormat(TotalSecSales.ToString()));
PrimarySales += TotalPrimarySales;
SecSales += TotalSecSales;
TotalAmt[k] += double.Parse(GetPrimarySales("2T", DiffMon[i].ToString()));
TotalAmt[++k] += double.Parse(GetPrimarySales("4T", DiffMon[i].ToString()));
TotalAmt[++k] += double.Parse(GetPrimarySales("Grease", DiffMon[i].ToString()));
TotalAmt[++k] += double.Parse(GetPrimarySalesGen(DiffMon[i].ToString()));
TotalAmt[++k] += double.Parse(GetSecSales("2T", DiffMon[i].ToString()));
TotalAmt[++k] += double.Parse(GetSecSales("4T", DiffMon[i].ToString()));
TotalAmt[++k] += double.Parse(GetSecSales("Grease", DiffMon[i].ToString()));
TotalAmt[++k] += double.Parse(GetSecSalesGen(DiffMon[i].ToString()));
}
}
k = 0;
sw.WriteLine("+----------+----------+---------+---------+---------+---------+----------+----------+---------+---------+---------+---------+----------+");
sw.WriteLine(info, " Total", GenUtil.strNumericFormat(PrimarySales.ToString()), GenUtil.strNumericFormat(TotalAmt[k].ToString()), GenUtil.strNumericFormat(TotalAmt[++k].ToString()), GenUtil.strNumericFormat(TotalAmt[++k].ToString()), GenUtil.strNumericFormat(TotalAmt[++k].ToString()), GenUtil.strNumericFormat(PrimarySales.ToString()), GenUtil.strNumericFormat(SecSales.ToString()), GenUtil.strNumericFormat(TotalAmt[++k].ToString()), GenUtil.strNumericFormat(TotalAmt[++k].ToString()), GenUtil.strNumericFormat(TotalAmt[++k].ToString()), GenUtil.strNumericFormat(TotalAmt[++k].ToString()), GenUtil.strNumericFormat(SecSales.ToString()));
sw.WriteLine("+----------+----------+---------+---------+---------+---------+----------+----------+---------+---------+---------+---------+----------+");
sw.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
/// <summary>
/// Method to write into the report file to print.
/// </summary>
public void makingReport()
{
try
{
System.Data.SqlClient.SqlDataReader rdr = null;
string home_drive = Environment.SystemDirectory;
home_drive = home_drive.Substring(0, 2);
string path = home_drive + @"\Inetpub\wwwroot\Servosms\Sysitem\ServosmsPrintServices\ReportView\StockReOrderingReport.txt";
StreamWriter sw = new StreamWriter(path);
string sql = "";
string info = "";
//string strDate = "";
//sql="select lr.Emp_ID r1,e.Emp_Name r2,lr.Date_From r3,lr.Date_To r4,lr.Reason r5,lr.isSanction r6 from Employee e,Leave_Register lr where e.Emp_ID=lr.Emp_ID and cast(floor(cast(lr.Date_From as float)) as datetime)>='"+ ToMMddYYYY(txtDateFrom.Text) +"' and cast(floor(cast(lr.Date_To as float)) as datetime)<='"+ ToMMddYYYY(Textbox1.Text) +"'";
sql = "select Prod_ID,Prod_Name+':'+Pack_Type Prod_Name,Prod_Code,minlabel,maxlabel,reorderlable from Products";
sql = sql + " order by " + Cache["strOrderBy"];
dbobj.SelectQuery(sql, ref rdr);
// 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 ReOrdering Report as ", des.Length));
sw.WriteLine(GenUtil.GetCenterAddr("=====================================", des.Length));
sw.WriteLine("+------------+--------------------------------------------------+---------------+-----------+-------------+-----------+");
sw.WriteLine("|Product Code| Product Name | Stock as On | Min Level |ReOrder Level| Max Level |");
sw.WriteLine("+------------+--------------------------------------------------+---------------+-----------+-------------+-----------+");
// 123456789012 12345678901234567890123456789012345678901234567890 123456789012345 12345678901 1234567890123 12345678901
//string str="";
if (rdr.HasRows)
{
info = " {0,-12:S} {1,-50:S} {2,15:S} {3,11:S} {4,13:S} {5,11:S}";
while (rdr.Read())
{
sw.WriteLine(info, rdr["Prod_Code"].ToString(),
GenUtil.TrimLength(rdr["Prod_Name"].ToString().Trim(), 50),
GetStock(rdr["Prod_ID"].ToString()),
rdr["minlabel"].ToString(),
rdr["reorderlable"].ToString(),
rdr["maxlabel"].ToString()
);
}
}
sw.WriteLine("+------------+--------------------------------------------------+---------------+-----------+-------------+-----------+");
dbobj.Dispose();
sw.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
/// <summary>
/// Prepares the report file BatchWiseStockLedger.txt for printing.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnPrint_Click(object sender, System.EventArgs e)
{
if (!checkValidity())
{
return;
}
try
{
string home_drive = Environment.SystemDirectory;
home_drive = home_drive.Substring(0, 2);
string path = home_drive + @"\Inetpub\wwwroot\Servosms\Sysitem\ServosmsPrintServices\ReportView\BatchWiseStockLedger.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("Batch Wise 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("| Transaction |Trans| Date | Batch No |-----+-------|-----+-------|-----+-------|");
sw.WriteLine("| Type | ID | | | Qty |Qty in | Qty |Qty in | Bal |Bal. in|");
sw.WriteLine("| | | | |(Nos)| (Ltr) |(Nos)| (Ltr) |(Nos)| (Ltr) |");
sw.WriteLine("+----------------------+-----+----------+------------+-----+-------+-----+-------+-----+-------+");
// 1234567890123456789012 12345 mm/dd/yyyy 123456789012 12345 1234567 12345 1234567 12345 1234567
info = " {0,-22:S} {1,-5:S} {2,-10:S} {3,-12:F} {4,5:F} {5,7:F} {6,5:F} {7,7:F} {8,5:F} {9,7:F}";
// string prod_name ="";
// string pack1 = "";
// string pack2 = "";
// string trans_type ="";
// string Cat = "";
//prod_name = drpProductName.SelectedItem.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 StockLedger_Batch order by " + Cache["strorderby"] + "", ref SqlDtr);
//sql=sql+" order by "+""+Cache["strorderby"]+"";
if (SqlDtr.HasRows)
{
while (SqlDtr.Read())
{
// string trans_no = "";
// if(SqlDtr.GetValue(0).ToString().StartsWith("Sales Invoice"))
// {
// trans_no = SqlDtr.GetValue(1).ToString();
// 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
// trans_no=SqlDtr.GetValue(1).ToString();
sw.WriteLine(info, SqlDtr.GetValue(0).ToString(),
SqlDtr.GetValue(1).ToString(),
//trans_no,
GenUtil.str2MMDDYYYY(trimDate(SqlDtr.GetValue(2).ToString())),
GenUtil.TrimLength(GetBatchNo(SqlDtr.GetValue(3).ToString()), 12),
SqlDtr.GetValue(4).ToString(),
SqlDtr.GetValue(5).ToString(),
SqlDtr.GetValue(6).ToString(),
SqlDtr.GetValue(7).ToString(),
SqlDtr.GetValue(8).ToString(),
SqlDtr.GetValue(9).ToString());
}
}
else
{
Stock_Ledger.Visible = false;
f = 1;
sw.Close();
MessageBox.Show("Data not available");
return;
}
SqlDtr.Close();
sw.WriteLine("+----------------------+-----+----------+------------+-----+-------+-----+-------+-----+-------+");
SqlDtr.Close();
sw.Close();
if (f == 0)
{
Print();
}
else
{
return;
}
}
catch (Exception ex)
{
CreateLogFiles.ErrorLog("Form:BatchWiseStockLedgerReport.aspx,Method:btnPrint_Click" + " EXCEPTION " + ex.Message + " userid " + uid);
}
}