private void frmStockMaster_Load(object sender, EventArgs e)
{
conn = new OleDbConnection();
ClosingStock cs;
cs = new ClosingStock();
conn = cs.GetConnection();
conn.Open();
OleDbDataAdapter da;
da = new OleDbDataAdapter("select * from scriptmaster", conn);
DataSet ds;
ds = new DataSet();
da.Fill(ds, "Tab1");
dataGridView1.DataSource = ds.Tables[0];
OleDbDataAdapter Da1;
Da1 = new OleDbDataAdapter("select distinct sector from scriptmaster", conn);
Da1.Fill(ds, "Script");
DataGridViewComboBoxColumn cbc = new DataGridViewComboBoxColumn();
cboSector.DataSource = ds.Tables["script"];
cboSector.ValueMember = "sector";
cboSector.DisplayMember = "sector";
}
private void frmCapitalGainLoss_Load(object sender, EventArgs e)
{
try
{
ClosingStock cs;
cs = new ClosingStock();
conn = cs.GetConnection();
conn.Open();
OleDbCommand cmd1;
cmd1 = new OleDbCommand();
cmd1.CommandText = "select distinct holder from stockpurchase order by holder";
cmd1.Connection = conn;
OleDbDataReader dr;
dr = cmd1.ExecuteReader();
cboHolder.Items.Add("All");
while (dr.Read())
{
cboHolder.Items.Add(dr["holder"].ToString());
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
}
private void button1_Click(object sender, EventArgs e)
{
ClosingStock Cs;
Cs = new ClosingStock();
conn = Cs.GetConnection();
try
{
conn.Open();
OleDbDataAdapter da;
string strSQL;
if (cboHolder.Text == "All")
{
strSQL = "select stocksold.ScriptCode,stocksold.Holder,scriptmaster.ScriptName,DateOfSale,Qty,Price,Qty*price as Amount,(select top 1 price from stockpurchase where trnid=stocksold.purchasetrnid) as PurchaseRate,(select top 1 dateofpurchase from stockpurchase where trnid=stocksold.purchasetrnid) as PurDate ,(select InflationIndex from InflationIndex where enddate>=(select dateofpurchase from stockpurchase where trnid=stocksold.purchasetrnid) and startdate <=(select top 1 dateofpurchase from stockpurchase where trnid=stocksold.purchasetrnid)) as PurIndex,(select InflationIndex from InflationIndex where enddate>=dateofsale and startdate <=dateofsale) as SaleIndex,(select financialyear from InflationIndex where enddate>=dateofsale and startdate <=dateofsale )as FinancialYear, Purchaserate*qty as PurAmount from stocksold,scriptmaster where stocksold.scriptcode=scriptmaster.scriptcode and dateofsale>=#" + dtStart.Value.ToShortDateString() + "# and dateofsale<=#" + DtEnd.Value.ToShortDateString() + "#";
}
else
{
strSQL = "select stocksold.ScriptCode,StockSold.Holder,scriptmaster.ScriptName,DateOfSale,Qty,Price,Qty*price as Amount,(select top 1 price from stockpurchase where trnid=stocksold.purchasetrnid) as PurchaseRate,(select top 1 dateofpurchase from stockpurchase where trnid=stocksold.purchasetrnid) as PurDate ,(select InflationIndex from InflationIndex where enddate>=(select dateofpurchase from stockpurchase where trnid=stocksold.purchasetrnid) and startdate <=(select top 1 dateofpurchase from stockpurchase where trnid=stocksold.purchasetrnid)) as PurIndex,(select InflationIndex from InflationIndex where enddate>=dateofsale and startdate <=dateofsale) as SaleIndex,(select financialyear from InflationIndex where enddate>=dateofsale and startdate <=dateofsale) as FinancialYear, Purchaserate*qty as PurAmount from stocksold,scriptmaster where stocksold.scriptcode=scriptmaster.scriptcode and dateofsale>=#" + dtStart.Value.ToShortDateString() + "# and dateofsale<=#" + DtEnd.Value.ToShortDateString() + "# and holder='" + cboHolder.Text + "'";
}
da = new OleDbDataAdapter(strSQL, conn);
DataSet ds;
ds = new DataSet();
da.Fill(ds, "Temp");
dataGridView1.DataSource = ds.Tables[0];
CapitalGain cr = new CapitalGain();
cr.SetDataSource(ds.Tables[0]);
cr.Refresh();
crystalReportViewer1.ReportSource = cr;
crystalReportViewer1.ShowRefreshButton = true;
crystalReportViewer1.DisplayToolbar = true;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
finally
{
}
}
private void cboScript_Leave(object sender, EventArgs e)
{
try
{
Conn.Open();
ClosingStock cst;
cst = new ClosingStock();
label8.Text = "Current Balance : " + cst.CalculateClosingStock(cboScript.SelectedValue.ToString(), Conn, cboHolder.Text);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally { Conn.Close(); }
}
public void InsertSoldEntry()
{
try{
Conn.Open();
cmd = new OleDbCommand();
cmd.Connection = Conn;
string InsertQuery;
ClosingStock ClsStock;
ClsStock = new ClosingStock();
cmd.CommandText = "delete from stocksold where trnid=" + long.Parse(lblTransactionId.Text);
cmd.ExecuteNonQuery();
float ClosingQty = ClsStock.CalculateClosingStock(cboScript.SelectedValue.ToString(), Conn, cboHolder.Text.ToString());
if (float.Parse("0" + txtQty.Text) > ClosingQty)
{
MessageBox.Show("Sold Qty should not be greater than " + ClosingQty + " (Qty in hand", "Error Saving", MessageBoxButtons.OK, MessageBoxIcon.Stop);
return;
}
float[,] st;
st = ClsStock.ReturnSellingNumbers(cboScript.SelectedValue.ToString(), Conn, float.Parse(txtQty.Text));
int i;
for (i = 0; i < 10; i++)
{
if (st[i, 1] > 0)
{
InsertQuery = "insert into stockSold (scriptcode,dateofsale,qty,price,amount,holder,typeoftransaction,purchasetrnid) values('" + cboScript.SelectedValue.ToString() + "','" + dtDated.Value.ToShortDateString() + "'," + float.Parse(st[i, 1].ToString()) + "," + float.Parse(txtPrice.Text) + "," + (float.Parse(txtPrice.Text) * float.Parse(st[i, 1].ToString())) + ",'" + cboHolder.Text + "','" + cboTransactiontype.Text + "'," + st[i, 0] + ")";
cmd.CommandText = InsertQuery;
cmd.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
MessageBox.Show("" + ex.Message);
}
finally
{
ClearControls();
Conn.Close();
cboScript.Focus();
MainDataset();
}
}
private void frmMyPortfolio_Load(object sender, EventArgs e)
{
ClosingStock cm;
cm = new ClosingStock();
conn1 = cm.GetConnection();
//conn1 = new OleDbConnection();
string txtconn = "provider=microsoft.jet.oledb.4.0;data source=c:\\temp\\sharereport.mdb";
conn1.ConnectionString = txtconn;
try
{
conn1.Open();
OleDbCommand cmd;
cmd = new OleDbCommand();
cmd.CommandText = "select distinct holder from stockpurchase order by holder";
cmd.Connection = conn1;
OleDbDataReader dr;
dr = cmd.ExecuteReader();
cboHolder1.Items.Add("All");
cboHolder1.Text = "All";
while (dr.Read())
{
cboHolder1.Items.Add(dr["holder"].ToString());
}
cboHolder1.Text = "All";
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
finally
{
conn1.Close();
}
}
