/// <summary>
/// This is used to binding the datagrid .
/// </summary>
public void Bindthedata()
{
SqlConnection sqlcon = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["Servosms"]);
//************
string prod_name = "";
string pack1 = "";
string pack2 = "";
string trans_type = "";
string Cat = "";
prod_name = drpProductName.Value.ToString().Trim();
// check if product contains the package then split it into pack1 and pack2 . Incase of fuel category and Loose Oil pack the pack1 and pack2 will be 0.
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";
}
trans_type = drpTransType.SelectedItem.Value.ToString().Trim();
// Response.Write(prod_name+"#"+pack1+"#"+pack2+"#"+trans_type);
//exec sp_StockLedger 'Servo 4t',2,4,'Sales','06/12/2006','06/13/2006'
object obj = null;
// Calls the procedure sp_StockLedger and creates the temporary table Stock_Ledger.
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);
//if(SqlDtr.HasRows)
//{
// Stock_Ledger.Visible = true;
// Stock_Ledger.DataSource = SqlDtr;
// Stock_Ledger.DataBind();
//}
//else
//{
// Stock_Ledger.Visible = false;
// MessageBox.Show("Data not available" );
// }
// SqlDtr.Close();
//*********
string sql = "Select * from Stock_Ledger order by trans_date ";
SqlDataAdapter da = new SqlDataAdapter(sql, sqlcon);
DataSet ds = new DataSet();
da.Fill(ds, "Stock_Ledger");
DataTable dtcustomer = ds.Tables["Stock_Ledger"];
//da.Fill(ds,"beat_master","machanic_entry","customermechanicentry ","customer");
//DataTable dtcustomer=ds.Tables["beat_master","machanic_entry","customermechanicentry ","customer"];
DataView dv = new DataView(dtcustomer);
dv.Sort = strorderby;
Cache["strorderby"] = strorderby;
Stock_Ledger.DataSource = dv;
if (dv.Count != 0)
{
Stock_Ledger.DataBind();
Stock_Ledger.Visible = true;
}
else
{
Stock_Ledger.Visible = false;
MessageBox.Show("Data Not Available");
}
sqlcon.Dispose();
}
/// <summary>
/// This is method is used to binding the datagrid with help of query.
/// </summary>
public void Bindthedata()
{
/**********************************************
* SqlConnection sqlcon=new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["Servosms"]);
* //************
* string prod_name ="";
* string pack1 = "";
* string pack2 = "";
* string trans_type ="";
* string Cat = "";
* prod_name = drpProductName.SelectedItem.Value.ToString().Trim();
*
* // check if product contains the package then split it into pack1 and pack2 . Incase of fuel category and Loose Oil pack the pack1 and pack2 will be 0.
* 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";
* }
* trans_type = drpTransType.SelectedItem.Value.ToString().Trim();
*
* // Response.Write(prod_name+"#"+pack1+"#"+pack2+"#"+trans_type);
* //exec sp_StockLedger 'Servo 4t',2,4,'Sales','06/12/2006','06/13/2006'
* object obj = null;
* // Calls the procedure sp_StockLedger and creates the temporary table Stock_Ledger.
* dbobj.ExecProc(OprType.Insert,"sp_BatchStockLedger",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);
*****************************/
//***********************************************************
InventoryClass obj = new InventoryClass();
InventoryClass obj1 = new InventoryClass();
SqlDataReader SqlDtr = null, rdr1 = null;
string[] strName = new string[2];
//if(drpProductName.SelectedItem.Text.IndexOf(":")>0)
if (drpProductName.Value.IndexOf(":") > 0)
{
strName = drpProductName.Value.Split(new char[] { ':' }, drpProductName.Value.Length);
}
else
{
strName[0] = drpProductName.Value;
}
string Op_st_No = "", Op_st_Ltr = "", Cl_st_No = "", Cl_st_Ltr = "", Bat_ID = "";
int x = 0;
double Bal_No = 0, Bal_Ltr = 0;
dbobj.Insert_or_Update("truncate table StockLedger_Batch", ref x);
SqlDataReader rdr = obj.GetRecordSet("select * from stockmaster_batch where productid=(select prod_id from products where prod_name='" + strName[0].ToString() + "' and pack_type='" + strName[1].ToString() + "') order by batch_id");
if (rdr.HasRows)
{
while (rdr.Read())
{
SqlDtr = obj1.GetRecordSet("select top 1 opening_stock,batch_id from stockmaster_batch where cast(floor(cast(stock_date as float)) as datetime) >= '" + GenUtil.str2MMDDYYYY(txtDateFrom.Text) + "' and batch_id='" + rdr["batch_id"].ToString() + "' and productid='" + rdr["productid"].ToString() + "' order by Stock_Date");
if (SqlDtr.Read())
{
Op_st_No = SqlDtr.GetValue(0).ToString();
Bal_No = double.Parse(SqlDtr.GetValue(0).ToString());
Bat_ID = SqlDtr.GetValue(1).ToString();
}
else
{
dbobj.SelectQuery("select top 1 closing_stock,batch_id from stockmaster_batch where productID = '" + rdr["ProductID"].ToString() + "' and cast(floor(cast(stock_date as float)) as datetime) <= '" + GenUtil.str2MMDDYYYY(txtDateFrom.Text) + "' and batch_id='" + rdr["batch_id"].ToString() + "' and productid='" + rdr["productid"].ToString() + "' order by stock_date desc", ref rdr1);
if (rdr1.Read())
{
Op_st_No = rdr1.GetValue(0).ToString();
Bal_No = double.Parse(rdr1.GetValue(0).ToString());
Bat_ID = rdr1.GetValue(1).ToString();
}
rdr1.Close();
}
SqlDtr.Close();
Op_st_Ltr = GenUtil.changeqtyltr(strName[1].ToString(), int.Parse(Op_st_No));
Bal_Ltr = double.Parse(Op_st_Ltr.ToString());
dbobj.Insert_or_Update("insert into StockLedger_Batch values('Opening Balance','','" + GenUtil.str2MMDDYYYY(txtDateFrom.Text) + "','" + Bat_ID + "',0,0,0,0,'" + Op_st_No + "','" + Op_st_Ltr + "')", ref x);
//*******************************
SqlDtr = obj1.GetRecordSet("select qty,trans_ID,batch_id,trans_date from batch_transaction where prod_ID = '" + rdr["ProductID"].ToString() + "' and cast(floor(cast(trans_date as float)) as datetime) >= '" + GenUtil.str2MMDDYYYY(txtDateFrom.Text) + "' and cast(floor(cast(trans_date as float)) as datetime) <= '" + GenUtil.str2MMDDYYYY(txtDateTo.Text) + "' and batch_id='" + rdr["batch_id"].ToString() + "' and trans_type='Purchase Invoice'");
if (SqlDtr.HasRows)
{
while (SqlDtr.Read())
{
Bal_No += double.Parse(SqlDtr.GetValue(0).ToString());
string Qty_Ltr = GenUtil.changeqtyltr(strName[1].ToString(), int.Parse(SqlDtr["Qty"].ToString()));
Bal_Ltr += double.Parse(Qty_Ltr);
dbobj.Insert_or_Update("insert into StockLedger_Batch values('Purchase Invoice','" + SqlDtr["Trans_ID"].ToString() + "','" + SqlDtr["Trans_Date"].ToString() + "','" + SqlDtr["Batch_ID"].ToString() + "','" + SqlDtr["Qty"].ToString() + "','" + Qty_Ltr + "',0,0,'" + Bal_No.ToString() + "','" + Bal_Ltr.ToString() + "')", ref x);
}
}
SqlDtr.Close();
//*******************************
SqlDtr = obj1.GetRecordSet("select qty,trans_ID,batch_id,trans_date from batch_transaction where prod_ID = '" + rdr["ProductID"].ToString() + "' and cast(floor(cast(trans_date as float)) as datetime) >= '" + GenUtil.str2MMDDYYYY(txtDateFrom.Text) + "' and cast(floor(cast(trans_date as float)) as datetime) <= '" + GenUtil.str2MMDDYYYY(txtDateTo.Text) + "' and batch_id='" + rdr["batch_id"].ToString() + "' and trans_type='Sales Invoice'");
if (SqlDtr.HasRows)
{
while (SqlDtr.Read())
{
Bal_No -= double.Parse(SqlDtr.GetValue(0).ToString());
string Qty_Ltr = GenUtil.changeqtyltr(strName[1].ToString(), int.Parse(SqlDtr["Qty"].ToString()));
Bal_Ltr -= double.Parse(Qty_Ltr);
dbobj.Insert_or_Update("insert into StockLedger_Batch values('Sales Invoice','" + SqlDtr["Trans_ID"].ToString() + "','" + SqlDtr["Trans_Date"].ToString() + "','" + SqlDtr["Batch_ID"].ToString() + "',0,0,'" + SqlDtr["Qty"].ToString() + "','" + Qty_Ltr + "','" + Bal_No.ToString() + "','" + Bal_Ltr.ToString() + "')", ref x);
}
}
SqlDtr.Close();
//************************************
SqlDtr = obj1.GetRecordSet("select top 1 closing_stock,batch_id from stockmaster_batch where productID = '" + rdr["ProductID"].ToString() + "' and cast(floor(cast(stock_date as float)) as datetime) <= '" + GenUtil.str2MMDDYYYY(txtDateTo.Text) + "' and batch_id='" + rdr["batch_id"].ToString() + "' order by stock_date desc");
if (SqlDtr.Read())
{
Cl_st_No = SqlDtr.GetValue(0).ToString();
Bat_ID = SqlDtr.GetValue(1).ToString();
}
SqlDtr.Close();
Cl_st_Ltr = GenUtil.changeqtyltr(strName[1].ToString(), int.Parse(Cl_st_No));
//Bal_Ltr+=double.Parse(Op_st_Ltr.ToString());
dbobj.Insert_or_Update("insert into StockLedger_Batch values('Closing Balance','','" + GenUtil.str2MMDDYYYY(txtDateTo.Text) + "','" + Bat_ID + "',0,0,0,0,'" + Cl_st_No + "','" + Cl_st_Ltr + "')", ref x);
}
/*
* set @last_min = (Select top 1 datepart(Minute,Trans_date) from stock_ledger Order by trans_Date desc)
* set @last_hour = (Select top 1 datepart(Hour,Trans_date) from stock_ledger Order by trans_Date desc)
* set @last_date = (Select top 1 Trans_date from stock_ledger where Trans_Type='Closing Balance')
* update stock_ledger set trans_date = dateAdd(Minute,(@last_min+1),@last_date) where Trans_type = 'Closing Balance'
* update stock_ledger set trans_date = dateAdd(Hour,(@last_hour+1),@last_date) where Trans_type = 'Closing Balance'
*/
int last_min = 0, last_hour = 0;
DateTime last_date = System.Convert.ToDateTime("1/1/1900");
dbobj.SelectQuery("Select top 1 datepart(Minute,Trans_date) from stockledger_batch Order by trans_Date desc", ref rdr1);
if (rdr1.Read())
{
last_min = int.Parse(rdr1.GetValue(0).ToString());
}
rdr1.Close();
dbobj.SelectQuery("Select top 1 datepart(Hour,Trans_date) from stockledger_batch Order by trans_Date desc", ref rdr1);
if (rdr1.Read())
{
last_hour = int.Parse(rdr1.GetValue(0).ToString());
}
rdr1.Close();
dbobj.SelectQuery("Select top 1 Trans_date from stockledger_batch where Trans_Type='Closing Balance'", ref rdr1);
if (rdr1.Read())
{
last_date = System.Convert.ToDateTime(rdr1.GetValue(0).ToString());
}
rdr1.Close();
dbobj.Insert_or_Update("update stockledger_batch set trans_date = dateAdd(Minute,(" + last_min + "+1),'" + last_date + "') where Trans_type = 'Closing Balance'", ref x);
dbobj.Insert_or_Update("update stockledger_batch set trans_date = dateAdd(Hour,(" + last_hour + "+1),'" + last_date + "') where Trans_type = 'Closing Balance'", ref x);
}
//***********************************************************
SqlConnection sqlcon = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["Servosms"]);
//coment by vikas 30.06.09 string sql="Select * from StockLedger_Batch order by trans_date";
string sql = "Select * from StockLedger_Batch order by Batch_ID";
SqlDataAdapter da = new SqlDataAdapter(sql, sqlcon);
DataSet ds = new DataSet();
da.Fill(ds, "StockLedger_Batch");
DataTable dtcustomer = ds.Tables["StockLedger_Batch"];
DataView dv = new DataView(dtcustomer);
dv.Sort = strorderby;
Cache["strorderby"] = strorderby;
Stock_Ledger.DataSource = dv;
if (dv.Count != 0)
{
Stock_Ledger.DataBind();
Stock_Ledger.Visible = true;
}
else
{
Stock_Ledger.Visible = false;
MessageBox.Show("Data Not Available");
}
sqlcon.Dispose();
}
