private void loadsprcolumns()
{
try
{
FpSpread.Sheets[0].RowCount = 0;
FpSpread.Sheets[0].ColumnCount = 5;
FpSpread.CommandBar.Visible = false;
FpSpread.RowHeader.Visible = false;
FpSpread.Sheets[0].AutoPostBack = false;
FpSpread.Sheets[0].ColumnHeader.RowCount = 1;
FarPoint.Web.Spread.TextCellType textcell = new FarPoint.Web.Spread.TextCellType();
FarPoint.Web.Spread.StyleInfo darkstyle = new FarPoint.Web.Spread.StyleInfo();
darkstyle.BackColor = ColorTranslator.FromHtml("#0CA6CA");
darkstyle.ForeColor = Color.Black;
FpSpread.ActiveSheetView.ColumnHeader.DefaultStyle = darkstyle;
FpSpread.Sheets[0].ColumnHeader.Cells[0, 0].Text = "S.No";
FpSpread.Sheets[0].ColumnHeader.Cells[0, 0].Font.Bold = true;
FpSpread.Sheets[0].ColumnHeader.Cells[0, 0].HorizontalAlign = HorizontalAlign.Center;
FpSpread.Sheets[0].ColumnHeader.Cells[0, 0].Font.Name = "Book Antiqua";
FpSpread.Sheets[0].ColumnHeader.Cells[0, 0].Font.Size = FontUnit.Medium;
FpSpread.Columns[0].Locked = true;
FpSpread.Columns[0].Width = 50;
FpSpread.Sheets[0].ColumnHeader.Cells[0, 1].Text = "Staff Code";
FpSpread.Sheets[0].ColumnHeader.Cells[0, 1].Font.Bold = true;
FpSpread.Sheets[0].ColumnHeader.Cells[0, 1].HorizontalAlign = HorizontalAlign.Center;
FpSpread.Sheets[0].ColumnHeader.Cells[0, 1].Font.Name = "Book Antiqua";
FpSpread.Sheets[0].ColumnHeader.Cells[0, 1].Font.Size = FontUnit.Medium;
FpSpread.Columns[1].Locked = true;
FpSpread.Columns[1].Width = 150;
FpSpread.Sheets[0].ColumnHeader.Cells[0, 2].Text = "Staff Name";
FpSpread.Sheets[0].ColumnHeader.Cells[0, 2].Font.Bold = true;
FpSpread.Sheets[0].ColumnHeader.Cells[0, 2].HorizontalAlign = HorizontalAlign.Center;
FpSpread.Sheets[0].ColumnHeader.Cells[0, 2].Font.Name = "Book Antiqua";
FpSpread.Sheets[0].ColumnHeader.Cells[0, 2].Font.Size = FontUnit.Medium;
FpSpread.Columns[2].Locked = true;
FpSpread.Columns[2].Width = 300;
FpSpread.Sheets[0].ColumnHeader.Cells[0, 3].Text = "Device ID";
FpSpread.Sheets[0].ColumnHeader.Cells[0, 3].Font.Bold = true;
FpSpread.Sheets[0].ColumnHeader.Cells[0, 3].HorizontalAlign = HorizontalAlign.Center;
FpSpread.Sheets[0].ColumnHeader.Cells[0, 3].Font.Name = "Book Antiqua";
FpSpread.Sheets[0].ColumnHeader.Cells[0, 3].Font.Size = FontUnit.Medium;
FpSpread.Columns[3].Locked = true;
FpSpread.Columns[3].Width = 150;
FpSpread.Sheets[0].ColumnHeader.Cells[0, 4].Text = "Finger ID";
FpSpread.Sheets[0].ColumnHeader.Cells[0, 4].CellType = textcell;
FpSpread.Sheets[0].ColumnHeader.Cells[0, 4].Font.Bold = true;
FpSpread.Sheets[0].ColumnHeader.Cells[0, 4].HorizontalAlign = HorizontalAlign.Center;
FpSpread.Sheets[0].ColumnHeader.Cells[0, 4].Font.Name = "Book Antiqua";
FpSpread.Sheets[0].ColumnHeader.Cells[0, 4].Font.Size = FontUnit.Medium;
FpSpread.Columns[4].Locked = true;
FpSpread.Columns[4].Width = 150;
}
catch { }
}
public void loaduser()
{
try
{
Fpuser.Sheets[0].ColumnCount = 4;
Fpuser.Sheets[0].RowCount = 0;
Fpuser.SheetCorner.ColumnCount = 0;
Fpuser.Sheets[0].ColumnHeader.Cells[0, 0].Text = "S.No";
Fpuser.Sheets[0].Columns[0].Width = 50;
Fpuser.Sheets[0].ColumnHeader.Cells[0, 1].Text = "User Name";
Fpuser.Sheets[0].Columns[1].Width = 200;
Fpuser.Sheets[0].ColumnHeader.Cells[0, 2].Text = "Description";
Fpuser.Sheets[0].Columns[2].Width = 200;
Fpuser.Sheets[0].ColumnHeader.Cells[0, 3].Text = "Select";
Fpuser.Sheets[0].Columns[3].Width = 50;
Fpuser.CommandBar.Visible = false;
FarPoint.Web.Spread.CheckBoxCellType chk = new FarPoint.Web.Spread.CheckBoxCellType();
FarPoint.Web.Spread.TextCellType txt = new FarPoint.Web.Spread.TextCellType();
string chkgroup = " and (isnull(group_code,'')='' or group_code='0' or group_code='-1')";
if (chkincludegroup.Checked == true)
{
chkgroup = "";
}
string strusename = "";
if (txtusersearch.Text.ToString().Trim() != "")
{
strusename = " and user_id like '" + txtusersearch.Text.ToString() + "%'";
}
string strquery = "select User_code,user_id,Description from usermaster where college_code='" + ddlcollege.SelectedValue.ToString() + "' " + chkgroup + " " + strusename + " order by user_id";
DataSet ds = d2.select_method_wo_parameter(strquery, "Text");
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
Fpuser.Sheets[0].RowCount++;
Fpuser.Sheets[0].Cells[Fpuser.Sheets[0].RowCount - 1, 0].Text = Fpuser.Sheets[0].RowCount.ToString();
Fpuser.Sheets[0].Cells[Fpuser.Sheets[0].RowCount - 1, 0].HorizontalAlign = HorizontalAlign.Center;
Fpuser.Sheets[0].Cells[Fpuser.Sheets[0].RowCount - 1, 1].CellType = txt;
Fpuser.Sheets[0].Cells[Fpuser.Sheets[0].RowCount - 1, 1].Text = ds.Tables[0].Rows[i]["user_id"].ToString();
Fpuser.Sheets[0].Cells[Fpuser.Sheets[0].RowCount - 1, 2].CellType = txt;
Fpuser.Sheets[0].Cells[Fpuser.Sheets[0].RowCount - 1, 2].Text = ds.Tables[0].Rows[i]["Description"].ToString();
Fpuser.Sheets[0].Cells[Fpuser.Sheets[0].RowCount - 1, 1].Tag = ds.Tables[0].Rows[i]["User_code"].ToString();
Fpuser.Sheets[0].Cells[Fpuser.Sheets[0].RowCount - 1, 3].CellType = chk;
Fpuser.Sheets[0].Cells[Fpuser.Sheets[0].RowCount - 1, 3].HorizontalAlign = HorizontalAlign.Center;
}
Fpuser.Width = 518;
Fpuser.Sheets[0].PageSize = Fpuser.Sheets[0].RowCount;
}
catch (Exception ex)
{
lblperrmsg.Visible = true;
lblperrmsg.Text = ex.ToString();
}
}
public void loadstage()
{
FarPoint.Web.Spread.TextCellType tb = new FarPoint.Web.Spread.TextCellType();
//FpSpreadstage.Sheets[0].AutoPostBack = true;
//FpSpreadstage.ActiveSheetView.SheetCorner.Cells[0, 0].Text = "S.No";
//FpSpreadstage.ActiveSheetView.SheetCorner.DefaultStyle.Font.Bold = true;
FpSpreadstage.ActiveSheetView.DefaultRowHeight = 25;
FpSpreadstage.ActiveSheetView.Rows.Default.Font.Name = "MS Sans Serif";
FpSpreadstage.ActiveSheetView.Rows.Default.Font.Size = FontUnit.Small;
FpSpreadstage.ActiveSheetView.Rows.Default.Font.Bold = false;
FpSpreadstage.ActiveSheetView.Columns.Default.Font.Bold = false;
FpSpreadstage.Sheets[0].RowHeader.Visible = false;
FpSpreadstage.ActiveSheetView.Columns.Default.Font.Name = "MS Sans Serif";
FpSpreadstage.ActiveSheetView.Columns.Default.Font.Size = FontUnit.Small;
FpSpreadstage.ActiveSheetView.ColumnHeader.DefaultStyle.Font.Bold = true;
FpSpreadstage.ActiveSheetView.ColumnHeader.DefaultStyle.Font.Name = "MS Sans Serif";
FpSpreadstage.ActiveSheetView.ColumnHeader.DefaultStyle.Font.Size = FontUnit.Small;
FpSpreadstage.VerticalScrollBarPolicy = FarPoint.Web.Spread.ScrollBarPolicy.AsNeeded;
FpSpreadstage.HorizontalScrollBarPolicy = FarPoint.Web.Spread.ScrollBarPolicy.AsNeeded;
FarPoint.Web.Spread.ButtonCellType btnmodify = new FarPoint.Web.Spread.ButtonCellType();
FarPoint.Web.Spread.CheckBoxCellType cbxd = new FarPoint.Web.Spread.CheckBoxCellType();
FarPoint.Web.Spread.ComboBoxCellType cf = new FarPoint.Web.Spread.ComboBoxCellType();
cf.AutoPostBack = true;
cbxd.AutoPostBack = true;
btnmodify.Text = "Remove";
FpSpreadstage.Sheets[0].RowCount = 0;
FpSpreadstage.Sheets[0].ColumnCount = 5;
FpSpreadstage.Sheets[0].ColumnHeader.Cells[0, 0].Text = "S.No";
FpSpreadstage.Sheets[0].ColumnHeader.Cells[0, 1].Text = "District";
FpSpreadstage.Sheets[0].Columns[1].CellType = cf;
FpSpreadstage.Sheets[0].ColumnHeader.Cells[0, 2].Text = "Stage Name";
FpSpreadstage.Sheets[0].ColumnHeader.Cells[0, 3].Text = "Address";
FpSpreadstage.Sheets[0].ColumnHeader.Cells[0, 4].Text = "Select";
FpSpreadstage.Sheets[0].Columns[4].CellType = cbxd;
//FpSpreadstage.Sheets[0].Columns[2].CellType = btnmodify;
FpSpreadstage.Sheets[0].Columns[3].Visible = true;
FpSpreadstage.Sheets[0].Columns[0].HorizontalAlign = HorizontalAlign.Center;
FpSpreadstage.Sheets[0].Columns[4].HorizontalAlign = HorizontalAlign.Center;
//FpSpreadstage.Sheets[0].Columns[2].Font.Bold = true;
FpSpreadstage.Sheets[0].Columns[0].Width = 40;
FpSpreadstage.Sheets[0].Columns[1].Width = 200;
FpSpreadstage.Sheets[0].Columns[2].Width = 350;
FpSpreadstage.Sheets[0].Columns[3].Width = 340;
FpSpreadstage.Sheets[0].Columns[4].Width = 50;
FpSpreadstage.Sheets[0].PageSize = 100;
FpSpreadstage.Width = 1000;
FpSpreadstage.Height = 391;
FpSpreadstage.SaveChanges();
FpSpreadstage.CommandBar.Visible = false;
}
public void btn_go_Click(object sender, EventArgs e)
{
try
{
string sql = "";
string colno = "";
string menuvalue1 = "";
string item = "";
//for (int i = 0; i < cbl_hostel.Items.Count; i++)
//{
// if (cbl_hostel.Items[i].Selected == true)
// {
// if (menuvalue1 == "")
// {
// menuvalue1 = "" + cbl_hostel.Items[i].Value.ToString() + "";
// }
// else
// {
// menuvalue1 = menuvalue1 + "'" + "," + "'" + cbl_hostel.Items[i].Value.ToString() + "";
// }
// }
//}
for (int i = 0; i < cbl_supplier.Items.Count; i++)
{
if (cbl_supplier.Items[i].Selected == true)
{
if (item == "")
{
item = "" + cbl_supplier.Items[i].Value.ToString() + "";
}
else
{
item = item + "'" + "," + "'" + cbl_supplier.Items[i].Value.ToString() + "";
}
}
}
//if (ItemList.Count == 0)
//{
// ItemList.Add("Hostel_Name");
// ItemList.Add("Session_Name");
// ItemList.Add("MenuName");
// ItemList.Add("Item_Code");
// ItemList.Add("item_name");
// ItemList.Add("RPU");
// ItemList.Add("Consumption_Date");
// ItemList.Add("qut");
// ItemList.Add("value");
// ItemList.Add("Total_Present");
//}
Hashtable columnhash = new Hashtable();
columnhash.Clear();
int colinc = 0;
columnhash.Add("vendor_name", "Supplier Name");
columnhash.Add("GI_Date", "Supplied Date");
columnhash.Add("Order_Date", "Purchase Order Date");
columnhash.Add("Order_Code", "Purchase Order Code");
columnhash.Add("itemheader_name", "Item Header Name");
columnhash.Add("Item_Code", "Item Code");
columnhash.Add("Item_Name", "Item Name");
columnhash.Add("Item_unit", "Measure");
columnhash.Add("order_qty", "Ordered QTY");
columnhash.Add("OrderedAmt", "Ordered QTY Amount");
columnhash.Add("Request_qty", "Requested QTY");
columnhash.Add("Request_Amt", "Requested QTY Amount");
columnhash.Add("RejQty", "Rejected QTY");
columnhash.Add("MasterValue", "Reject Reason");
if (ItemList.Count == 0)
{
ItemList.Add("Order_Date");
ItemList.Add("Order_Code");
ItemList.Add("GI_Date");
ItemList.Add("itemheader_name");
ItemList.Add("Item_Code");
ItemList.Add("Item_Name");
//ItemList.Add("Consumption_Date");
//ItemList.Add("qut");
//ItemList.Add("value");
//ItemList.Add("Total_Present");
}
string getday = "";
string gettoday = "";
string from = "";
string to = "";
from = Convert.ToString(txt_fromdate.Text);
string[] splitdate = from.Split('-');
splitdate = splitdate[0].Split('/');
DateTime dt = new DateTime();
if (splitdate.Length > 0)
{
dt = Convert.ToDateTime(splitdate[1] + "/" + splitdate[0] + "/" + splitdate[2]);
}
getday = dt.ToString("MM/dd/yyyy");
to = Convert.ToString(txt_todate.Text);
string[] splitdate1 = to.Split('-');
splitdate1 = splitdate1[0].Split('/');
DateTime dt1 = new DateTime();
if (splitdate1.Length > 0)
{
dt1 = Convert.ToDateTime(splitdate1[1] + "/" + splitdate1[0] + "/" + splitdate1[2]);
}
gettoday = dt1.ToString("MM/dd/yyyy");
if (item != "")
{
string selectquery = "";
selectquery = "SELECT distinct P.OrderCode as Order_Code,CONVERT(varchar(10), OrderDate,103) as Order_Date,G.GoodsInwardCode as GI_Code,CONVERT(varchar(10), GoodsInwardDate,103) as GI_Date,ItemHeaderName as itemheader_name,m.itemcode as Item_Code,M.ItemName as Item_Name,M.ItemUnit as Item_unit,(AppQty-ISNULL(RejQty,0)) as order_qty,((AppQty-ISNULL(RejQty,0))*RPU) as OrderedAmt,AppQty as Request_qty,(AppQty * rpu) as Request_Amt,InwardQty,(InwardQty*RPU) InwardAmt,RejQty,v.VendorCompName as vendor_name,(select MasterValue from CO_MasterValues c where c.MasterCode=i.Reject_reason)as MasterValue FROM IT_PurchaseOrder P,IT_PurchaseOrderDetail I,IT_GoodsInward G,IM_ItemMaster M,CO_VendorMaster V WHERE P.PurchaseOrderPK = I.PurchaseOrderFK and I.ItemFK=M.ItemPK and I.ItemFK=G.ItemFK and p.VendorFK =v.VendorPK AND p.VendorFK in ('" + item + "') AND OrderDate between '" + dt.ToString("MM/dd/yyyy") + "' and '" + dt1.ToString("MM/dd/yyyy") + "' and p.PurchaseOrderPK=g.PurchaseOrderFK ";
ds.Clear();
ds = d2.select_method_wo_parameter(selectquery, "Text");
if (ds.Tables[0].Rows.Count > 0)
{
pcolumnorder.Visible = true;
Fpspread1.Sheets[0].RowHeader.Visible = false;
//Fpspread1.Sheets[0].ColumnCount = 11;
Fpspread1.CommandBar.Visible = false;
Fpspread1.Sheets[0].RowCount = 0;
Fpspread1.SheetCorner.ColumnCount = 0;
FarPoint.Web.Spread.StyleInfo darkstyle = new FarPoint.Web.Spread.StyleInfo();
darkstyle.BackColor = ColorTranslator.FromHtml("#0CA6CA");
darkstyle.ForeColor = Color.White;
Fpspread1.ActiveSheetView.ColumnHeader.DefaultStyle = darkstyle;
Fpspread1.Sheets[0].ColumnHeader.RowCount = 1;
Fpspread1.Sheets[0].RowHeader.Visible = false;
Fpspread1.Sheets[0].ColumnCount = ItemList.Count + 1;
Fpspread1.Sheets[0].RowCount = ds.Tables[0].Rows.Count;
Fpspread1.Sheets[0].AutoPostBack = true;
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 0].Text = "S.No";
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 0].Font.Bold = true;
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 0].Font.Name = "Book Antiqua";
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 0].Font.Size = FontUnit.Medium;
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 0].HorizontalAlign = HorizontalAlign.Center;
Fpspread1.Sheets[0].Columns[0].HorizontalAlign = HorizontalAlign.Center;
FarPoint.Web.Spread.TextCellType txt = new FarPoint.Web.Spread.TextCellType();
for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
{
colno = Convert.ToString(ds.Tables[0].Columns[j]);
if (ItemList.Contains(Convert.ToString(colno)))
{
int index = ItemList.IndexOf(Convert.ToString(colno));
Fpspread1.Sheets[0].ColumnHeader.Cells[0, index + 1].Text = Convert.ToString(columnhash[colno]);
Fpspread1.Sheets[0].ColumnHeader.Cells[0, index + 1].Font.Bold = true;
Fpspread1.Sheets[0].ColumnHeader.Cells[0, index + 1].Font.Name = "Book Antiqua";
Fpspread1.Sheets[0].ColumnHeader.Cells[0, index + 1].Font.Size = FontUnit.Medium;
Fpspread1.Sheets[0].ColumnHeader.Cells[0, index + 1].HorizontalAlign = HorizontalAlign.Center;
}
}
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
Fpspread1.Sheets[0].Cells[i, 0].Text = Convert.ToString(i + 1);
Fpspread1.Sheets[0].Cells[i, 0].HorizontalAlign = HorizontalAlign.Center;
for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
{
if (ItemList.Contains(Convert.ToString(ds.Tables[0].Columns[j].ToString())))
{
int index = ItemList.IndexOf(Convert.ToString(ds.Tables[0].Columns[j].ToString()));
Fpspread1.Sheets[0].Columns[index + 1].Width = 150;
Fpspread1.Sheets[0].Columns[index + 1].Locked = true;
Fpspread1.Sheets[0].Cells[i, index + 1].CellType = txt;
Fpspread1.Sheets[0].Cells[i, index + 1].Text = ds.Tables[0].Rows[i][j].ToString();
Fpspread1.Sheets[0].Cells[i, index + 1].Font.Name = "Book Antiqua";
Fpspread1.Sheets[0].Cells[i, index + 1].Font.Size = FontUnit.Medium;
if (ds.Tables[0].Columns[j].ToString() == "order_qty")
{
Fpspread1.Sheets[0].Cells[i, index + 1].HorizontalAlign = HorizontalAlign.Right;
}
if (ds.Tables[0].Columns[j].ToString() == "OrderedAmt")
{
Fpspread1.Sheets[0].Cells[i, index + 1].HorizontalAlign = HorizontalAlign.Right;
}
if (ds.Tables[0].Columns[j].ToString() == "Request_qty")
{
Fpspread1.Sheets[0].Cells[i, index + 1].HorizontalAlign = HorizontalAlign.Right;
}
if (ds.Tables[0].Columns[j].ToString() == "Request_Amt")
{
Fpspread1.Sheets[0].Cells[i, index + 1].HorizontalAlign = HorizontalAlign.Right;
}
if (ds.Tables[0].Columns[j].ToString() == "Rej_qty")
{
Fpspread1.Sheets[0].Cells[i, index + 1].HorizontalAlign = HorizontalAlign.Right;
}
Fpspread1.Sheets[0].SetColumnMerge(index + 1, FarPoint.Web.Spread.Model.MergePolicy.Always);
Fpspread1.Sheets[0].Cells[i, index + 1].VerticalAlign = VerticalAlign.Middle;
}
}
}
//Fpspread1.Sheets[0].SetColumnMerge(1, FarPoint.Web.Spread.Model.MergePolicy.Always);
////Fpspread1.Sheets[0].SetColumnMerge(2, FarPoint.Web.Spread.Model.MergePolicy.Always);
////Fpspread1.Sheets[0].SetColumnMerge(3, FarPoint.Web.Spread.Model.MergePolicy.Always);
Fpspread1.Sheets[0].PageSize = Fpspread1.Sheets[0].RowCount;
rptprint.Visible = true;
Fpspread1.Visible = true;
div1.Visible = true;
lbl_error.Visible = false;
pheaderfilter.Visible = true;
pcolumnorder.Visible = true;
}
else
{
rptprint.Visible = false;
//imgdiv2.Visible = true;
// lbl_alert.Text = "No records found";
lbl_error.Visible = true;
lbl_error.Text = "No records found";
pheaderfilter.Visible = false;
pcolumnorder.Visible = false;
div1.Visible = false;
Fpspread1.Visible = false;
}
}
else
{
rptprint.Visible = false;
// imgdiv2.Visible = true;
//lbl_alert.Text = "No records found";
lbl_error.Visible = true;
lbl_error.Text = "Please Select Any One Supplier Name";
pheaderfilter.Visible = false;
pcolumnorder.Visible = false;
div1.Visible = false;
Fpspread1.Visible = false;
}
}
catch (Exception ex)
{
}
}
protected void btngo_click(object sender, EventArgs e)
{
try
{
string selq = "";
string collcode = Convert.ToString(ddlpopclg.SelectedItem.Value);
if (ddldept.SelectedItem.Text != "Select" && ddlstaffcat.SelectedItem.Text != "Select")
{
if (ddldept.SelectedItem.Text == "All" && ddlstaffcat.SelectedItem.Text == "All")
{
selq = " select sm.staff_code,staff_name,Fingerprint1,dept_name,desig_name,Fingerprint1 as MachineNo from staffmaster sm,hrdept_master h,desig_master d,stafftrans st,staffcategorizer sc where sm.staff_code=st.staff_code and sc.category_code=st.category_code and sc.college_code=sm.college_code and sm.college_code=h.college_code and sm.college_code=d.collegeCode and st.dept_code=h.dept_code and st.desig_code=d.desig_code and st.latestrec='1' and sm.college_code='" + collcode + "'";
}
else if (ddldept.SelectedItem.Text == "All" && ddlstaffcat.SelectedItem.Text != "All")
{
selq = " select sm.staff_code,staff_name,Fingerprint1,dept_name,desig_name,Fingerprint1 as MachineNo from staffmaster sm,hrdept_master h,desig_master d,stafftrans st,staffcategorizer sc where sm.staff_code=st.staff_code and sc.category_code=st.category_code and sc.college_code=sm.college_code and sm.college_code=h.college_code and sm.college_code=d.collegeCode and st.dept_code=h.dept_code and st.desig_code=d.desig_code and st.latestrec='1' and sm.college_code='" + collcode + "' and st.category_code='" + ddlstaffcat.SelectedItem.Value + "'";
}
else if (ddldept.SelectedItem.Text != "All" && ddlstaffcat.SelectedItem.Text == "All")
{
selq = " select sm.staff_code,staff_name,Fingerprint1,dept_name,desig_name,Fingerprint1 as MachineNo from staffmaster sm,hrdept_master h,desig_master d,stafftrans st,staffcategorizer sc where sm.staff_code=st.staff_code and sc.category_code=st.category_code and sc.college_code=sm.college_code and sm.college_code=h.college_code and sm.college_code=d.collegeCode and st.dept_code=h.dept_code and st.desig_code=d.desig_code and st.latestrec='1' and sm.college_code='" + collcode + "' and st.dept_code='" + ddldept.SelectedItem.Value + "'";
}
else if (ddldept.SelectedItem.Text != "All" && ddlstaffcat.SelectedItem.Text != "All")
{
selq = " select sm.staff_code,staff_name,Fingerprint1,dept_name,desig_name,Fingerprint1 as MachineNo from staffmaster sm,hrdept_master h,desig_master d,stafftrans st,staffcategorizer sc where sm.staff_code=st.staff_code and sc.category_code=st.category_code and sc.college_code=sm.college_code and sm.college_code=h.college_code and sm.college_code=d.collegeCode and st.dept_code=h.dept_code and st.desig_code=d.desig_code and st.latestrec='1' and sm.college_code='" + collcode + "' and st.dept_code='" + ddldept.SelectedItem.Value + "' and st.category_code='" + ddlstaffcat.SelectedItem.Value + "'";
}
if (chkincrel.Checked == false)
{
selq = selq + " and ((sm.resign='0' and sm.settled='0') or sm.Discontinue='0')";
}
if (rbfingerid.Checked == true)
{
selq = selq + " and ((sm.Fingerprint1 is not null) and (cast(sm.FingerPrint1 as varchar)<>''))";
}
else
{
selq = selq + " and ((sm.Fingerprint1 is null) or (cast(sm.FingerPrint1 as varchar)=''))";
}
}
ds.Clear();
ds = d2.select_method_wo_parameter(selq, "Text");
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
loadrepsprcolumns();
FarPoint.Web.Spread.CheckBoxCellType cb = new FarPoint.Web.Spread.CheckBoxCellType();
cb.AutoPostBack = false;
FarPoint.Web.Spread.CheckBoxCellType cball = new FarPoint.Web.Spread.CheckBoxCellType();
cball.AutoPostBack = true;
FarPoint.Web.Spread.TextCellType txtcell = new FarPoint.Web.Spread.TextCellType();
Fpspreadpop.Sheets[0].RowCount++;
Fpspreadpop.Sheets[0].Cells[0, 1].CellType = cball;
Fpspreadpop.Sheets[0].Cells[0, 1].Value = 0;
Fpspreadpop.Sheets[0].Cells[0, 1].HorizontalAlign = HorizontalAlign.Center;
Fpspreadpop.Sheets[0].Cells[0, 1].Font.Name = "Book Antiqua";
for (ik = 0; ik < ds.Tables[0].Rows.Count; ik++)
{
Fpspreadpop.Sheets[0].RowCount++;
Fpspreadpop.Sheets[0].Cells[Fpspreadpop.Sheets[0].RowCount - 1, 0].Text = Convert.ToString(ik + 1);
Fpspreadpop.Sheets[0].Cells[Fpspreadpop.Sheets[0].RowCount - 1, 0].HorizontalAlign = HorizontalAlign.Center;
Fpspreadpop.Sheets[0].Cells[Fpspreadpop.Sheets[0].RowCount - 1, 0].Font.Name = "Book Antiqua";
Fpspreadpop.Sheets[0].Cells[Fpspreadpop.Sheets[0].RowCount - 1, 0].Font.Size = FontUnit.Medium;
Fpspreadpop.Sheets[0].Cells[Fpspreadpop.Sheets[0].RowCount - 1, 1].CellType = cb;
Fpspreadpop.Sheets[0].Cells[Fpspreadpop.Sheets[0].RowCount - 1, 1].HorizontalAlign = HorizontalAlign.Center;
Fpspreadpop.Sheets[0].Cells[Fpspreadpop.Sheets[0].RowCount - 1, 1].Font.Name = "Book Antiqua";
Fpspreadpop.Sheets[0].Cells[Fpspreadpop.Sheets[0].RowCount - 1, 1].Font.Size = FontUnit.Medium;
Fpspreadpop.Sheets[0].Cells[Fpspreadpop.Sheets[0].RowCount - 1, 2].Text = Convert.ToString(ds.Tables[0].Rows[ik]["staff_Code"]);
Fpspreadpop.Sheets[0].Cells[Fpspreadpop.Sheets[0].RowCount - 1, 2].CellType = txtcell;
Fpspreadpop.Sheets[0].Cells[Fpspreadpop.Sheets[0].RowCount - 1, 2].HorizontalAlign = HorizontalAlign.Left;
Fpspreadpop.Sheets[0].Cells[Fpspreadpop.Sheets[0].RowCount - 1, 2].Font.Name = "Book Antiqua";
Fpspreadpop.Sheets[0].Cells[Fpspreadpop.Sheets[0].RowCount - 1, 2].Font.Size = FontUnit.Medium;
Fpspreadpop.Sheets[0].Cells[Fpspreadpop.Sheets[0].RowCount - 1, 3].Text = Convert.ToString(ds.Tables[0].Rows[ik]["staff_name"]);
Fpspreadpop.Sheets[0].Cells[Fpspreadpop.Sheets[0].RowCount - 1, 3].HorizontalAlign = HorizontalAlign.Left;
Fpspreadpop.Sheets[0].Cells[Fpspreadpop.Sheets[0].RowCount - 1, 3].Font.Name = "Book Antiqua";
Fpspreadpop.Sheets[0].Cells[Fpspreadpop.Sheets[0].RowCount - 1, 3].Font.Size = FontUnit.Medium;
Fpspreadpop.Sheets[0].Cells[Fpspreadpop.Sheets[0].RowCount - 1, 4].Text = Convert.ToString(ds.Tables[0].Rows[ik]["desig_name"]);
Fpspreadpop.Sheets[0].Cells[Fpspreadpop.Sheets[0].RowCount - 1, 4].HorizontalAlign = HorizontalAlign.Left;
Fpspreadpop.Sheets[0].Cells[Fpspreadpop.Sheets[0].RowCount - 1, 4].Font.Name = "Book Antiqua";
Fpspreadpop.Sheets[0].Cells[Fpspreadpop.Sheets[0].RowCount - 1, 4].Font.Size = FontUnit.Medium;
Fpspreadpop.Sheets[0].Cells[Fpspreadpop.Sheets[0].RowCount - 1, 5].Text = Convert.ToString(ds.Tables[0].Rows[ik]["dept_name"]);
Fpspreadpop.Sheets[0].Cells[Fpspreadpop.Sheets[0].RowCount - 1, 5].HorizontalAlign = HorizontalAlign.Left;
Fpspreadpop.Sheets[0].Cells[Fpspreadpop.Sheets[0].RowCount - 1, 5].Font.Name = "Book Antiqua";
Fpspreadpop.Sheets[0].Cells[Fpspreadpop.Sheets[0].RowCount - 1, 5].Font.Size = FontUnit.Medium;
Fpspreadpop.Sheets[0].Cells[Fpspreadpop.Sheets[0].RowCount - 1, 6].CellType = txtcell;
Fpspreadpop.Sheets[0].Cells[Fpspreadpop.Sheets[0].RowCount - 1, 6].Text = Convert.ToString(ds.Tables[0].Rows[ik]["Fingerprint1"]);
Fpspreadpop.Sheets[0].Cells[Fpspreadpop.Sheets[0].RowCount - 1, 6].HorizontalAlign = HorizontalAlign.Center;
Fpspreadpop.Sheets[0].Cells[Fpspreadpop.Sheets[0].RowCount - 1, 6].Font.Name = "Book Antiqua";
Fpspreadpop.Sheets[0].Cells[Fpspreadpop.Sheets[0].RowCount - 1, 6].Font.Size = FontUnit.Medium;
//Fpspreadpop.Sheets[0].Cells[Fpspreadpop.Sheets[0].RowCount - 1, 7].Text = Convert.ToString(ds.Tables[0].Rows[ik]["MachineNo"]);
//Fpspreadpop.Sheets[0].Cells[Fpspreadpop.Sheets[0].RowCount - 1, 7].HorizontalAlign = HorizontalAlign.Center;
//Fpspreadpop.Sheets[0].Cells[Fpspreadpop.Sheets[0].RowCount - 1, 7].Font.Name = "Book Antiqua";
//Fpspreadpop.Sheets[0].Cells[Fpspreadpop.Sheets[0].RowCount - 1, 7].Font.Size = FontUnit.Medium;
}
Fpspreadpop.Sheets[0].PageSize = Fpspreadpop.Sheets[0].RowCount;
Fpspreadpop.Visible = true;
lblpoperr.Visible = false;
rprint.Visible = true;
divpopspr.Visible = true;
}
else
{
Fpspreadpop.Visible = false;
lblpoperr.Visible = true;
lblpoperr.Text = "No Records Found!";
rprint.Visible = false;
divpopspr.Visible = false;
}
}
catch { }
}
public void bindspread()
{
try
{
FpSpread1.Sheets[0].RowCount = 0;
FpSpread1.Sheets[0].ColumnCount = 0;
strquery = "SELECT DISTINCT Stage_ID,S.Stage_Name,R.Veh_ID,len(r.Veh_ID),(select priority from tbl_vechile_priority p where p.veh_id=r.veh_id and p.stage_id=s.Stage_ID) as periorty FROM RouteMaster R,Vehicle_Master V,Stage_Master S WHERE R.Veh_ID = V.Veh_ID AND R.Stage_Name = S.Stage_ID";
if (ddlstage.SelectedItem.ToString() != "All")
{
strquery = strquery + " and R.Stage_Name = '" + ddlstage.SelectedValue.ToString() + "' ";
}
strquery = strquery + " order by S.Stage_Name,len(r.Veh_ID),R.Veh_ID";
ds = d2.select_method_wo_parameter(strquery, "Text");
if (ds.Tables[0].Rows.Count > 0)
{
lblrptname.Visible = true;
txtexcelname.Visible = true;
btnxl.Visible = true;
btnprintmaster.Visible = true;
Printcontrol.Visible = false;
FpSpread1.Visible = true;
btnsave.Enabled = true;
btnreset.Enabled = true;
FpSpread1.Sheets[0].ColumnHeader.RowCount = 1;
FpSpread1.Sheets[0].ColumnCount = 5;
FpSpread1.Sheets[0].RowCount = 0;
FpSpread1.Sheets[0].Columns[0].HorizontalAlign = HorizontalAlign.Center;
FpSpread1.Sheets[0].ColumnHeader.Rows[0].BackColor = Color.AliceBlue;
FpSpread1.Sheets[0].ColumnHeader.Rows[0].Height = 25;
FpSpread1.Sheets[0].ColumnHeader.Cells[0, 0].Text = "S.No";
FpSpread1.Sheets[0].ColumnHeader.Cells[0, 1].Text = "Stage";
FpSpread1.Sheets[0].ColumnHeader.Cells[0, 2].Text = "Vehicle";
FpSpread1.Sheets[0].ColumnHeader.Cells[0, 3].Text = "Select";
FpSpread1.Sheets[0].ColumnHeader.Cells[0, 4].Text = "Priority";
FarPoint.Web.Spread.TextCellType txt = new FarPoint.Web.Spread.TextCellType();
FpSpread1.Sheets[0].Columns[0].CellType = txt;
FpSpread1.Sheets[0].Columns[1].CellType = txt;
FpSpread1.Sheets[0].Columns[2].CellType = txt;
FpSpread1.Sheets[0].Columns[4].CellType = txt;
FarPoint.Web.Spread.CheckBoxCellType chk = new FarPoint.Web.Spread.CheckBoxCellType();
chk.AutoPostBack = true;
FpSpread1.Sheets[0].SetColumnMerge(1, FarPoint.Web.Spread.Model.MergePolicy.Always);
FpSpread1.Sheets[0].Columns[0].Width = 75;
FpSpread1.Sheets[0].Columns[1].Width = 250;
FpSpread1.Sheets[0].Columns[2].Width = 150;
FpSpread1.Sheets[0].Columns[3].Width = 50;
FpSpread1.Sheets[0].Columns[4].Width = 100;
int srno = 0;
string stageid = "";
string priority = "";
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
FpSpread1.Sheets[0].RowCount++;
srno++;
stageid = ds.Tables[0].Rows[i]["Stage_ID"].ToString();
priority = ds.Tables[0].Rows[i]["periorty"].ToString();
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 0].Text = srno.ToString();
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 1].Text = ds.Tables[0].Rows[i]["Stage_Name"].ToString();
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 1].Tag = stageid;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 2].Text = ds.Tables[0].Rows[i]["Veh_ID"].ToString();
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 3].CellType = chk;
if (priority.Trim() != "0" && priority.Trim() != "" && priority.Trim().ToLower() != null)
{
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 4].Text = priority;
}
else
{
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 4].Text = "";
}
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 0].HorizontalAlign = HorizontalAlign.Center;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 1].HorizontalAlign = HorizontalAlign.Left;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 2].HorizontalAlign = HorizontalAlign.Left;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 3].HorizontalAlign = HorizontalAlign.Center;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 4].HorizontalAlign = HorizontalAlign.Center;
}
}
else
{
lblrptname.Visible = false;
txtexcelname.Visible = false;
btnxl.Visible = false;
btnprintmaster.Visible = false;
Printcontrol.Visible = false;
FpSpread1.Visible = false;
errmsg.Visible = true;
errmsg.Text = "no Records Found";
btnsave.Enabled = false;
btnreset.Enabled = false;
}
FpSpread1.Sheets[0].PageSize = FpSpread1.Sheets[0].RowCount;
FpSpread1.SaveChanges();
int h1 = 100;
for (int i = 0; i < FpSpread1.Sheets[0].RowCount; i++)
{
h1 = h1 + FpSpread1.Sheets[0].Rows[i].Height;
}
if (h1 < 500)
{
FpSpread1.Height = h1;
}
else
{
FpSpread1.Height = 500;
}
h1 = 20;
for (int i = 0; i < FpSpread1.Sheets[0].ColumnCount; i++)
{
h1 = h1 + FpSpread1.Sheets[0].Columns[i].Width;
}
FpSpread1.Width = h1;
}
catch
{
}
}
public void grdRackStatus_SelectedIndexChanged(object sender, EventArgs e)
{
try
{
DataTable rack = new DataTable();
DataRow drrow;
string libname = "";
DataSet dsgetupdatebook = new DataSet();
var grid = (GridView)sender;
GridViewRow selectedRow = grid.SelectedRow;
int rowIndex = grid.SelectedIndex;
int selectedCellIndex = int.Parse(this.SelectedGridCellIndex.Value);
if (ddllibrary_sts.Items.Count > 0)
{
libname = Convert.ToString(ddllibrary_sts.SelectedItem.Text);
libcode = Convert.ToString(ddllibrary_sts.SelectedValue);
}
if (Convert.ToString(rowIndex) != "" && Convert.ToString(selectedCellIndex) != "-1")
{
string getra = Convert.ToString(grdRackStatus.Rows[rowIndex].Cells[selectedCellIndex].Text);
string getrackNo = Convert.ToString(grdRackStatus.HeaderRow.Cells[selectedCellIndex].Text);
if (getra != "")
{
string[] rash = getra.Split('-');
string RowValue = rash[1].Trim();
string getsql = " select bookdetails.acc_no,title ,author,call_no from bookdetails left join rack_allocation on (bookdetails.acc_no=rack_allocation.acc_no and rack_allocation.lib_code=bookdetails.lib_code) where bookdetails.lib_code='" + libcode + "' and rack_no='" + getrackNo + "' and row_no='" + RowValue + "' and (rack_allocation.book_type='BOK' or rack_allocation.book_type='REF') order by acc_no";
dsgetupdatebook.Clear();
dsgetupdatebook = d2.select_method_wo_parameter(getsql, "Text");
rack.Columns.Add("Access No");
rack.Columns.Add("Title");
rack.Columns.Add("Author");
rack.Columns.Add("Call No");
if (dsgetupdatebook.Tables.Count > 0 && dsgetupdatebook.Tables[0].Rows.Count > 0)
{
FarPoint.Web.Spread.TextCellType txtCell = new FarPoint.Web.Spread.TextCellType();
int sno = 0;
for (int row = 0; row < dsgetupdatebook.Tables[0].Rows.Count; row++)
{
sno++;
drrow = rack.NewRow();
drrow["Access No"] = Convert.ToString(dsgetupdatebook.Tables[0].Rows[row]["acc_no"]).Trim();
drrow["Title"] = Convert.ToString(dsgetupdatebook.Tables[0].Rows[row]["title"]).Trim();
drrow["Author"] = Convert.ToString(dsgetupdatebook.Tables[0].Rows[row]["author"]).Trim();
drrow["Call No"] = Convert.ToString(dsgetupdatebook.Tables[0].Rows[row]["call_no"]).Trim();
rack.Rows.Add(drrow);
}
}
div1.Visible = true;
grdCellClick.DataSource = rack;
grdCellClick.DataBind();
grdCellClick.Visible = true;
Divfspreadstatus.Visible = true;
Buttonexit.Visible = true;
}
}
else
{
Divfspreadstatus.Visible = false;
}
}
catch
{
}
}
protected void btn_go(object sender, EventArgs e)
{
try
{
clear();
FpSpread1.Sheets[0].SheetCorner.ColumnCount = 0;
FpSpread1.Sheets[0].ColumnHeader.RowCount = 2;
FpSpread1.Sheets[0].ColumnCount = 0;
FpSpread1.Sheets[0].RowCount = 0;
FpSpread1.CommandBar.Visible = false;
FpSpread1.ActiveSheetView.ColumnHeader.DefaultStyle.Font.Name = "Book Antiqua";
FpSpread1.ActiveSheetView.ColumnHeader.DefaultStyle.Font.Size = FontUnit.Medium;
FpSpread1.ActiveSheetView.ColumnHeader.DefaultStyle.Font.Bold = true;
FpSpread1.Sheets[0].DefaultStyle.Font.Name = "Book Antiqua";
FpSpread1.Sheets[0].DefaultStyle.Font.Size = FontUnit.Medium;
FarPoint.Web.Spread.StyleInfo style2 = new FarPoint.Web.Spread.StyleInfo();
style2.Font.Size = 13;
style2.Font.Name = "Book Antiqua";
style2.Font.Bold = true;
style2.HorizontalAlign = HorizontalAlign.Center;
style2.ForeColor = Color.White;
style2.BackColor = Color.Teal;
FpSpread1.Sheets[0].SheetCornerStyle = new FarPoint.Web.Spread.StyleInfo(style2);
FpSpread1.Sheets[0].ColumnHeader.DefaultStyle = new FarPoint.Web.Spread.StyleInfo(style2);
FarPoint.Web.Spread.TextCellType txt = new FarPoint.Web.Spread.TextCellType();
DataSet dsrank = new DataSet();
Hashtable hatstutotal = new Hashtable();
DataSet dsexam = new DataSet();
DataView dvcount = new DataView();
string batchcount = "";
string sqlbatch = "";
string branchcount = "";
string sqlbranch = "";
string exam_year = "";
string exam_month = "";
collegecode = ddlclg.SelectedItem.Value;
for (int itemcount = 0; itemcount < chklsbatch.Items.Count; itemcount++)
{
if (chklsbatch.Items[itemcount].Selected == true)
{
if (batchcount == "")
{
batchcount = "'" + chklsbatch.Items[itemcount].Value.ToString() + "'";
}
else
{
batchcount = batchcount + ",'" + chklsbatch.Items[itemcount].Value.ToString() + "'";
}
}
}
if (batchcount != "")
{
sqlbatch = " and r.batch_year in(" + batchcount + ")";
}
else
{
lbl_errmsg.Visible = true;
lbl_errmsg.Text = "Please Select The Batch And Then Proceed";
return;
}
for (int itemcount = 0; itemcount < chklstbranch.Items.Count; itemcount++)
{
if (chklstbranch.Items[itemcount].Selected == true)
{
if (branchcount == "")
{
branchcount = "'" + chklstbranch.Items[itemcount].Value.ToString() + "'";
}
else
{
branchcount = branchcount + ",'" + chklstbranch.Items[itemcount].Value.ToString() + "'";
}
}
}
if (branchcount != "")
{
sqlbranch = " and r.degree_code in(" + branchcount + ")";
}
else
{
lbl_errmsg.Visible = true;
lbl_errmsg.Text = "Please Select The Degree and Branch And Then Proceed";
return;
}
if (ddlyear.Items.Count > 0)
{
if (ddlyear.SelectedItem.ToString().Trim() != "")
{
exam_year = ddlyear.SelectedValue.ToString();
}
else
{
lbl_errmsg.Visible = true;
lbl_errmsg.Text = "Please Select The Exam Year And Then Proceed";
return;
}
}
else
{
lbl_errmsg.Visible = true;
lbl_errmsg.Text = "Please Select The Exam Year And Then Proceed";
return;
}
if (ddlmonth.Items.Count > 0)
{
if (ddlmonth.SelectedItem.ToString().Trim() != "")
{
exam_month = ddlmonth.SelectedValue.ToString();
}
else
{
lbl_errmsg.Visible = true;
lbl_errmsg.Text = "Please Select The Exam Month And Then Proceed";
return;
}
}
else
{
lbl_errmsg.Visible = true;
lbl_errmsg.Text = "Please Select The Exam Month And Then Proceed";
return;
}
Double cgpafrom = 0;
Double cgpato = 0;
string strfrange = txt_rangefrom.Text.ToString();
if (strfrange.Trim() != "")
{
cgpafrom = Convert.ToDouble(strfrange);
}
else
{
lbl_errmsg.Visible = true;
lbl_errmsg.Text = "Please Select The CGPA From Range And Then Proceed";
return;
}
string strtrange = txt_to.Text.ToString();
if (strtrange.Trim() != "")
{
cgpato = Convert.ToDouble(strtrange);
}
else
{
lbl_errmsg.Visible = true;
lbl_errmsg.Text = "Please Select The CGPA To Range And Then Proceed";
return;
}
if (cgpato < cgpafrom)
{
lbl_errmsg.Visible = true;
lbl_errmsg.Text = "Please Enter The CGPA To Range Must Be Greater Then From Range";
return;
}
string query = "select distinct r.stud_name,r.Reg_No,r.batch_year,dg.Acronym,course.Course_Name,ed.current_semester,r.sections,r.degree_code,d.dept_name,course.course_name,dg.course_id,r.mode ,r.roll_no,r.degree_code,r.mode from registration r,mark_entry me ,department d,course,degree dg,Exam_Details ed where me.roll_no=r.roll_no and r.degree_code=dg.degree_code and dg.course_id = course.course_id and dg.dept_code = d.dept_code and ed.degree_code=r.degree_code and ed.batch_year=r.Batch_Year and me.result='pass' and ed.Exam_Month='" + exam_month + "' and ed.Exam_year='" + exam_year + "' " + sqlbatch + " " + sqlbranch + " order by r.degree_code,r.Batch_Year";
string getbranchvalue = "select distinct d.dept_acronym,c.Course_Name,dg.Degree_Code from registration r,mark_entry me ,department d,course c,degree dg,Exam_Details ed where me.roll_no=r.roll_no and r.degree_code=dg.degree_code and dg.course_id = c.course_id and dg.dept_code = d.dept_code and ed.degree_code=r.degree_code and ed.batch_year=r.Batch_Year and me.result='pass' and ed.Exam_Month='" + exam_month + "' and ed.Exam_year='" + exam_year + "' " + sqlbatch + " " + sqlbranch + " order by c.Course_Name,d.dept_acronym";
ds.Dispose();
ds.Reset();
ds = d2.select_method(query, hat, "Text");
if (ds.Tables[0].Rows.Count > 0)
{
DataSet dstable = d2.select_method("select * from sysobjects where name='tbl_Topperrank' and Type='U'", hat, "text ");
if (dstable.Tables[0].Rows.Count == 0)
{
int p = d2.insert_method("create table tbl_Topperrank (roll_no nvarchar(50),cgpa float (8),stud_name nvarchar(200),degree nvarchar(500),user_code nvarchar(25))", hat, "text");
}
else
{
int p = d2.insert_method("IF not EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tbl_Topperrank' AND COLUMN_NAME = 'user_code') alter table tbl_Topperrank add user_code nvarchar(15)", hat, "text");
}
ds.Dispose();
dsfind = d2.select_method("select name from sysobjects where xtype='p' and name='sp_ins_upd_topperrank' ", hat, "text");
if (dsfind.Tables[0].Rows.Count == 0)
{
string spcreation = " CREATE procedure sp_ins_upd_topperrank (@RollNumber varchar(50), @cgpa varchar(20), @stud_name varchar(20), @degree varchar(200) ,@user_code nvarchar(25)) as declare @cou_nt int set @cou_nt=(select count(Roll_no)from tbl_Topperrank where Roll_no=@RollNumber) if @cou_nt=0 BEGIN insert into tbl_Topperrank(Roll_no,cgpa,stud_name,degree,user_code) values (@RollNumber,@cgpa,@stud_name,@degree,@user_code) End Else BEGIN update tbl_Topperrank set cgpa=@cgpa where Roll_no=@RollNumber and user_code=@user_code end";
int s = d2.insert_method(spcreation, hat, "Text");
}
else
{
string spalter = " alter procedure sp_ins_upd_topperrank (@RollNumber varchar(50), @cgpa varchar(20), @stud_name varchar(20), @degree varchar(200) ,@user_code nvarchar(25)) as declare @cou_nt int set @cou_nt=(select count(Roll_no)from tbl_Topperrank where Roll_no=@RollNumber) if @cou_nt=0 BEGIN insert into tbl_Topperrank(Roll_no, cgpa,stud_name,degree,user_code)values(@RollNumber,@cgpa,@stud_name,@degree, @user_code) End Else BEGIN update tbl_Topperrank set cgpa=@cgpa where Roll_no=@RollNumber and user_code=@user_code End";
int gf = d2.insert_method(spalter, hat, "Text");
}
Boolean rowflag = false;
FpSpread1.Sheets[0].ColumnCount++;
FpSpread1.Sheets[0].ColumnHeader.Cells[0, 0].Text = "CGPA";
FpSpread1.Sheets[0].ColumnHeaderSpanModel.Add(0, 0, 2, 1);
string tempdegree = "";
DataSet dsdegree = d2.select_method_wo_parameter(getbranchvalue, "text");
int spancolumn = 0;
Boolean valkflag = false;
for (int d = 0; d < dsdegree.Tables[0].Rows.Count; d++)
{
string strdegree = dsdegree.Tables[0].Rows[d]["dept_acronym"].ToString();
string Course = dsdegree.Tables[0].Rows[d]["Course_Name"].ToString();
if (tempdegree != Course || d == dsdegree.Tables[0].Rows.Count - 1)
{
if (tempdegree != "")
{
if (tempdegree == Course && d == dsdegree.Tables[0].Rows.Count - 1)
{
spancolumn++;
valkflag = true;
FpSpread1.Sheets[0].ColumnCount++;
FpSpread1.Sheets[0].ColumnHeader.Cells[0, FpSpread1.Sheets[0].ColumnCount - 1].Text = Course;
FpSpread1.Sheets[0].ColumnHeader.Cells[1, FpSpread1.Sheets[0].ColumnCount - 1].Text = strdegree;
FpSpread1.Sheets[0].ColumnHeader.Cells[1, FpSpread1.Sheets[0].ColumnCount - 1].Note = dsdegree.Tables[0].Rows[d]["Degree_Code"].ToString();
}
FpSpread1.Sheets[0].ColumnHeaderSpanModel.Add(0, FpSpread1.Sheets[0].ColumnCount - spancolumn, 1, spancolumn);
}
spancolumn = 0;
tempdegree = Course;
}
if (valkflag == false)
{
spancolumn++;
FpSpread1.Sheets[0].ColumnCount++;
FpSpread1.Sheets[0].ColumnHeader.Cells[0, FpSpread1.Sheets[0].ColumnCount - 1].Text = Course;
FpSpread1.Sheets[0].ColumnHeader.Cells[1, FpSpread1.Sheets[0].ColumnCount - 1].Text = strdegree;
FpSpread1.Sheets[0].ColumnHeader.Cells[1, FpSpread1.Sheets[0].ColumnCount - 1].Note = dsdegree.Tables[0].Rows[d]["Degree_Code"].ToString();
}
}
FpSpread1.Sheets[0].ColumnCount++;
FpSpread1.Sheets[0].ColumnHeader.Cells[0, FpSpread1.Sheets[0].ColumnCount - 1].Text = "Total";
FpSpread1.Sheets[0].ColumnHeaderSpanModel.Add(0, FpSpread1.Sheets[0].ColumnCount - 1, 2, 1);
for (int y = 0; y < chklsbatch.Items.Count; y++)
{
if (chklsbatch.Items[y].Selected == true)
{
string batch = chklsbatch.Items[y].Text.ToString();
FpSpread1.Sheets[0].RowCount++;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 0].CellType = txt;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 0].Text = "Batch " + batch + "";
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 0].BackColor = Color.AliceBlue;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 0].HorizontalAlign = HorizontalAlign.Center;
FpSpread1.Sheets[0].SpanModel.Add(FpSpread1.Sheets[0].RowCount - 1, 0, 1, FpSpread1.Sheets[0].ColumnCount);
int startow = FpSpread1.Sheets[0].RowCount;
for (Double dc = cgpato; dc >= cgpafrom; dc = dc - 0.5)
{
FpSpread1.Sheets[0].RowCount++;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 0].CellType = txt;
if (dc == cgpato)
{
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 0].Text = ">" + dc.ToString();
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 0].Note = " and cgpa >" + dc + "";
}
else
{
Double doc1 = dc + 0.5;
Double doc2 = dc + 0.001;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 0].Text = dc + " - " + doc1;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 0].Note = " and cgpa between " + doc2 + " and " + doc1 + "";
}
}
for (int c = 1; c < FpSpread1.Sheets[0].ColumnCount - 1; c++)
{
string degreecode = FpSpread1.Sheets[0].ColumnHeader.Cells[1, c].Note.ToString();
ds.Tables[0].DefaultView.RowFilter = "Batch_year='" + batch + "' and degree_code='" + degreecode + "'";
DataView dvstud = ds.Tables[0].DefaultView;
for (int s = 0; s < dvstud.Count; s++)
{
string rollno = dvstud[s]["roll_no"].ToString();
string sem = dvstud[s]["current_semester"].ToString();
string mode = dvstud[s]["mode"].ToString();
string name = dvstud[s]["stud_name"].ToString();
string degreevalue = dvstud[s]["Acronym"].ToString();
int failcount = Convert.ToInt32(d2.GetFunction(" Select COUNT(*) from Mark_Entry,Subject where Mark_Entry.Subject_No = Subject.Subject_No and roll_no='" + rollno + "' and result='fail' and result='Fail' "));
if (failcount == 0)
{
string cgpav = d2.Calculete_CGPA(rollno, sem, degreecode, batch, mode, collegecode);
if (cgpav != "0" && cgpav != "" && cgpav != "-" && cgpav != "NaN")
{
Double num = 0;
if (Double.TryParse(cgpav, out num))
{
hat.Clear();
hat.Add("RollNumber", rollno);
hat.Add("cgpa", cgpav.ToString());
hat.Add("stud_name", name.ToString());
hat.Add("degree", degreevalue.ToString());
hat.Add("user_code", usercode.ToString());
int o = d2.insert_method("sp_ins_upd_topperrank", hat, "sp");
}
}
}
}
for (int r = startow; r < FpSpread1.Sheets[0].RowCount; r++)
{
string valran = FpSpread1.Sheets[0].Cells[r, 0].Note;
string getstucount = d2.GetFunction("select count(roll_no) from tbl_Topperrank where user_code='" + usercode + "' " + valran + "");
if (getstucount.Trim() != "0" && getstucount.Trim() != "")
{
rowflag = true;
}
FpSpread1.Sheets[0].Cells[r, c].CellType = txt;
FpSpread1.Sheets[0].Cells[r, c].Text = getstucount;
FpSpread1.Sheets[0].Cells[r, c].HorizontalAlign = HorizontalAlign.Center;
}
int p = d2.insert_method("Delete from tbl_Topperrank where user_code='" + usercode + "' ", hat, "text");
}
}
}
for (int r = 1; r < FpSpread1.Sheets[0].RowCount; r++)
{
int totalstudent = 0;
for (int c = 1; c < FpSpread1.Sheets[0].ColumnCount - 1; c++)
{
string rowsvalue = FpSpread1.Sheets[0].Cells[r, c].Text.ToString();
if (rowsvalue.Trim() != "")
{
totalstudent = totalstudent + Convert.ToInt32(rowsvalue);
}
FpSpread1.Sheets[0].Cells[r, FpSpread1.Sheets[0].ColumnCount - 1].Text = totalstudent.ToString();
FpSpread1.Sheets[0].Cells[r, FpSpread1.Sheets[0].ColumnCount - 1].HorizontalAlign = HorizontalAlign.Center;
}
}
if (rowflag == true)
{
FpSpread1.Visible = true;
lbl_rptname.Visible = true;
btn_excel.Visible = true;
btn_print.Visible = true;
txt_rpt.Visible = true;
}
else
{
lbl_errmsg.Text = "No Records Found";
lbl_errmsg.Visible = true;
}
}
else
{
lbl_errmsg.Text = "No Records Found";
lbl_errmsg.Visible = true;
}
FpSpread1.Sheets[0].PageSize = FpSpread1.Sheets[0].RowCount;
}
catch (Exception ex)
{
lbl_errmsg.Text = ex.ToString();
lbl_errmsg.Visible = true;
}
}
protected void btn_go(object sender, EventArgs e)
{
try
{
Printcontrol.Visible = false;
FarPoint.Web.Spread.TextCellType timecell = new FarPoint.Web.Spread.TextCellType();
btncal();
Printcontrol.Visible = false;
FpSpread2.Visible = false;
string vehicalid = "";
string routeid = "";
string currentloc = "";
int serialno = 1;
string startstage = "";
string endstage = "";
string starttime = "";
string status = "";
string endtime = "";
string val = Convert.ToString(DateTime.Now);
string[] split = val.Split();
string time = split[2].ToString();
FarPoint.Web.Spread.StyleInfo style2 = new FarPoint.Web.Spread.StyleInfo();
style2.Font.Size = 13;
style2.Font.Name = "Book Antiqua";
style2.Font.Bold = true;
style2.HorizontalAlign = HorizontalAlign.Center;
style2.ForeColor = Color.White;
style2.BackColor = Color.Teal;
FpSpread1.Sheets[0].Rows.Count = 0;
FpSpread1.Sheets[0].AutoPostBack = true;
FpSpread1.Sheets[0].SheetCornerStyle = new FarPoint.Web.Spread.StyleInfo(style2);
FpSpread1.Sheets[0].ColumnHeader.DefaultStyle = new FarPoint.Web.Spread.StyleInfo(style2);
FpSpread1.Sheets[0].AllowTableCorner = true;
FpSpread1.Sheets[0].SheetCornerStyle = new FarPoint.Web.Spread.StyleInfo(style2);
FpSpread1.Sheets[0].ColumnHeader.Cells[0, 0].Text = "S.No";
FpSpread1.Sheets[0].ColumnHeader.Cells[0, 0].Border.BorderColor = Color.Black;
FpSpread1.Sheets[0].Columns[0].Width = 10;
FpSpread1.Sheets[0].ColumnHeader.Cells[0, 1].Text = "Vehicle ID ";
FpSpread1.Sheets[0].ColumnHeader.Cells[0, 1].Border.BorderColor = Color.Black;
FpSpread1.Sheets[0].Columns[1].Width = 120;
FpSpread1.Sheets[0].ColumnHeader.Cells[0, 2].Text = "Route ID";
FpSpread1.Sheets[0].ColumnHeader.Cells[0, 2].Border.BorderColor = Color.Black;
FpSpread1.Sheets[0].Columns[2].Width = 90;
FpSpread1.Sheets[0].ColumnHeader.Cells[0, 3].Text = "Boarding Point";
FpSpread1.Sheets[0].ColumnHeader.Cells[0, 3].Border.BorderColor = Color.Black;
FpSpread1.Sheets[0].Columns[3].Width = 200;
FpSpread1.Sheets[0].ColumnHeader.Cells[0, 4].Text = "Ending Point";
FpSpread1.Sheets[0].ColumnHeader.Cells[0, 4].Border.BorderColor = Color.Black;
FpSpread1.Sheets[0].Columns[4].Width = 200;
FpSpread1.Sheets[0].Columns[0].Locked = true;
FpSpread1.Sheets[0].Columns[1].Locked = true;
FpSpread1.Sheets[0].Columns[2].Locked = true;
FpSpread1.Sheets[0].Columns[3].Locked = true;
FpSpread1.Sheets[0].Columns[4].Locked = true;
string mainquery = "select distinct veh_id,route_id from RouteMaster r,Stage_Master s where r.stage_name = s.Stage_id and route_id in('" + route_all + "')";
mainquery = mainquery + "select * from VTSGPRSData where VehicleID in( '" + vech_all + "')";
main = da.select_method_wo_parameter(mainquery, "text");
if (main.Tables[0].Rows.Count > 0)
{
for (int loop = 0; loop < main.Tables[0].Rows.Count; loop++)
{
vehicalid = main.Tables[0].Rows[loop]["veh_id"].ToString();
routeid = main.Tables[0].Rows[loop]["route_id"].ToString();
DataView dlocation = new DataView();
main.Tables[1].DefaultView.RowFilter = "VehicleID in('" + vehicalid + "') ";
dlocation = main.Tables[1].DefaultView;
if (dlocation.Count > 0)
{
currentloc = Convert.ToString(dlocation[0]["GoogleLocation"]);
}
else
{
currentloc = "Location Not Found";
}
if (currentloc == "")
{
currentloc = "Location Not Found";
}
if (time == "PM")
{
datecalc = " select r.Route_ID,r.Mdate,r.Stage_Name as stage,Arr_Time,Dep_Time,sess,Veh_ID,s.Stage_id,s.Stage_Name, s.Address from RouteMaster r,Stage_Master s where str(r.Stage_Name)=str(s.Stage_id) and sess='M' and Route_ID in ('" + route_all + "') order by convert(nvarchar(5),Dep_Time,103) asc ";
datecalc = datecalc + " select r.Route_ID,r.Mdate,r.Stage_Name as stage,Arr_Time,Dep_Time,sess,Veh_ID,s.Stage_id,s.Stage_Name, s.Address from RouteMaster r,Stage_Master s where str(r.Stage_Name)=str(s.Stage_id) and sess='M' and Route_ID in ('" + route_all + "') order by convert(nvarchar(5),Dep_Time,103) desc";
}
else
{
datecalc = " select r.Route_ID,r.Mdate,r.Stage_Name as stage,Arr_Time,Dep_Time,sess,Veh_ID,s.Stage_id,s.Stage_Name, s.Address from RouteMaster r,Stage_Master s where str(r.Stage_Name)=str(s.Stage_id) and sess='M' and Route_ID in ('" + route_all + "') order by convert(nvarchar(5),Dep_Time,103) asc ";
datecalc = datecalc + " select r.Route_ID,r.Mdate,r.Stage_Name as stage,Arr_Time,Dep_Time,sess,Veh_ID,s.Stage_id,s.Stage_Name, s.Address from RouteMaster r,Stage_Master s where str(r.Stage_Name)=str(s.Stage_id) and sess='M' and Route_ID in ('" + route_all + "') order by convert(nvarchar(5),Dep_Time,103) desc";
}
datecalculate = da.select_method_wo_parameter(datecalc, "text");
if (datecalculate.Tables[0].Rows.Count > 0)
{
DataView dft = new DataView();
datecalculate.Tables[0].DefaultView.RowFilter = "Route_ID in('" + routeid + "') ";
dft = datecalculate.Tables[0].DefaultView;
startstage = dft[0]["Stage_Name"].ToString();
starttime = dft[0]["Dep_Time"].ToString();
DataView endview = new DataView();
datecalculate.Tables[1].DefaultView.RowFilter = "Route_ID in('" + routeid + "') ";
endview = datecalculate.Tables[1].DefaultView;
endstage = endview[0]["Stage_Name"].ToString();
endtime = endview[0]["Arr_Time"].ToString();
if (endtime == "Halt")
{
endtime = endview[1]["Arr_Time"].ToString();
}
FpSpread1.Sheets[0].RowCount++;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 0].Text = serialno.ToString();
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 0].HorizontalAlign = HorizontalAlign.Center;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 1].Tag = vehicalid.ToString();
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 1].Text = Convert.ToString(FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 1].Tag);
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 1].HorizontalAlign = HorizontalAlign.Left;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 2].Text = routeid.ToString();
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 2].HorizontalAlign = HorizontalAlign.Left;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 3].Text = startstage.ToString();
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 3].HorizontalAlign = HorizontalAlign.Left;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 4].Text = endstage.ToString();
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 4].HorizontalAlign = HorizontalAlign.Left;
status = "Halt";
//string statusquery = " select Digitalinput1status from VTSGPRSData where VehicleID='" + vehicalid + "'";
//DataSet statusds = new DataSet();
//statusds = da.select_method_wo_parameter(statusquery, "text");
////if (statusds.Tables[0].Rows.Count > 0)
////{
//// if (status.Trim() != "Running")
//// {
//// status = statusds.Tables[0].Rows[0]["Digitalinput1status"].ToString();
//// FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 8].Text = status.ToString();
//// FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 8].HorizontalAlign = HorizontalAlign.Center;
//// }
//// else
//// {
//// status = "Halt";
//// FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 8].Text = status.ToString();
//// FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 8].HorizontalAlign = HorizontalAlign.Center;
//// }
////}
serialno++;
FpSpread1.Sheets[0].PageSize = FpSpread1.Sheets[0].RowCount;
FpSpread1.Visible = true;
spreadexcel1.Visible = true;
spreadpdf1.Visible = true;
lbl_rptname.Visible = true;
txt_name.Visible = true;
}
else
{
IblError.Text = "No Records Found";
IblError.Visible = true;
FpSpread1.Visible = false;
}
}
}
else
{
IblError.Text = "No Records Found";
IblError.Visible = true;
FpSpread1.Visible = false;
}
}
catch (Exception ex)
{
IblError.Visible = true;
IblError.Text = ex.ToString();
}
}
protected void loadfsstaff()
{
string sql = "";
if (ddldepratstaff.SelectedIndex != 0)
{
if (txt_search.Text != "")
{
if (ddlstaff.SelectedIndex == 0)
{
sql = "SELECT distinct staffmaster.staff_code, staffmaster.staff_name FROM staffmaster INNER JOIN stafftrans ON staffmaster.staff_code = stafftrans.staff_code INNER JOIN hrdept_master ON stafftrans.dept_code = hrdept_master.dept_code WHERE (stafftrans.latestrec <> 0) AND (staffmaster.resign = 0)and (staffmaster.settled = 0) and (staff_name like '" + txt_search.Text + "%') and (hrdept_master.dept_code = '" + ddldepratstaff.SelectedValue + "') and (staffmaster.college_code =hrdept_master.college_code) and staffmaster.college_code='" + ddlcollege.SelectedValue + "'";//Modifed By Srinath 9/5/2013
}
else if (ddlstaff.SelectedIndex == 1)
{
sql = "SELECT distinct staffmaster.staff_code, staffmaster.staff_name FROM staffmaster INNER JOIN stafftrans ON staffmaster.staff_code = stafftrans.staff_code INNER JOIN hrdept_master ON stafftrans.dept_code = hrdept_master.dept_code WHERE (stafftrans.latestrec <> 0) AND (staffmaster.resign = 0) and (staffmaster.settled = 0) and (staffmaster.staff_code like '" + txt_search.Text + "%') and (hrdept_master.dept_code = '" + ddldepratstaff.SelectedValue + "') and (staffmaster.college_code =hrdept_master.college_code) and staffmaster.college_code='" + ddlcollege.SelectedValue + "'";//Modifed By Srinath 9/5/2013
}
}
else
{
//sql = "SELECT staffmaster.staff_code, staffmaster.staff_name FROM staffmaster INNER JOIN stafftrans ON staffmaster.staff_code = stafftrans.staff_code INNER JOIN hrdept_master ON stafftrans.dept_code = hrdept_master.dept_code WHERE (stafftrans.latestrec <> 0) AND (staffmaster.resign = 0) and (staffmaster.settled = 0) and (hrdept_master.dept_name = '" + ddldepratstaff.Text + "') AND (staffmaster.college_code = '" + ddlcollege.SelectedValue + "' and (staffmaster.college_code =hrdept_master.college_code)";
sql = "SELECT staffmaster.staff_code, staffmaster.staff_name FROM staffmaster INNER JOIN stafftrans ON staffmaster.staff_code = stafftrans.staff_code INNER JOIN hrdept_master ON stafftrans.dept_code = hrdept_master.dept_code WHERE (stafftrans.latestrec <> 0) AND (staffmaster.resign = 0) and (staffmaster.settled = 0) and (hrdept_master.dept_code = '" + ddldepratstaff.SelectedValue + "') AND (staffmaster.college_code = '" + ddlcollege.SelectedValue + "') and (staffmaster.college_code =hrdept_master.college_code)";
}
}
else if (txt_search.Text != "")
{
if (ddlstaff.SelectedIndex == 0)
{
sql = "SELECT distinct staffmaster.staff_code, staffmaster.staff_name FROM staffmaster INNER JOIN stafftrans ON staffmaster.staff_code = stafftrans.staff_code INNER JOIN hrdept_master ON stafftrans.dept_code = hrdept_master.dept_code WHERE (stafftrans.latestrec <> 0) AND (staffmaster.resign = 0) and (staffmaster.settled = 0) and (staffmaster.staff_name like '" + txt_search.Text + "%') and (staffmaster.college_code =hrdept_master.college_code) and staffmaster.college_code='" + ddlcollege.SelectedValue + "'";//Modifed By Srinath 9/5/2013
}
else if (ddlstaff.SelectedIndex == 1)
{
sql = "SELECT distinct staffmaster.staff_code, staffmaster.staff_name FROM staffmaster INNER JOIN stafftrans ON staffmaster.staff_code = stafftrans.staff_code INNER JOIN hrdept_master ON stafftrans.dept_code = hrdept_master.dept_code WHERE (stafftrans.latestrec <> 0) AND (staffmaster.resign = 0) and (staffmaster.settled = 0) and (staffmaster.staff_code like '" + txt_search.Text + "%') and (staffmaster.college_code =hrdept_master.college_code) and staffmaster.college_code='" + ddlcollege.SelectedValue + "'";//Modifed By Srinath 9/5/2013
}
else if (ddlcollege.SelectedIndex != -1)
{
sql = "select distinct staffmaster.staff_code, staff_name from stafftrans,staffmaster where stafftrans.staff_code=staffmaster.staff_code and latestrec<>0 and resign=0 and settled=0 and staffmaster.college_code='" + ddlcollege.SelectedValue + "'";//Modifed By Srinath 9/5/2013
}
else
{
sql = "select distinct staffmaster.staff_code, staff_name from stafftrans,staffmaster,hrdept_master.dept_name where stafftrans.staff_code=staffmaster.staff_code and latestrec<>0 and resign=0 and staffmaster.college_code='" + ddlcollege.SelectedValue + "'";//Modifed By Srinath 9/5/2013
}
}
else
if (ddldepratstaff.SelectedValue.ToString() == "All")
{
sql = "select distinct staffmaster.staff_code, staff_name from stafftrans,staffmaster where stafftrans.staff_code=staffmaster.staff_code and latestrec<>0 and resign=0 and settled=0 and staffmaster.college_code='" + ddlcollege.SelectedValue + "'"; //Modifed By Srinath 9/5/2013
}
fsstaff.Sheets[0].RowCount = 0;
fsstaff.SaveChanges();
FarPoint.Web.Spread.CheckBoxCellType chkcell1 = new FarPoint.Web.Spread.CheckBoxCellType();
FarPoint.Web.Spread.CheckBoxCellType chkcell = new FarPoint.Web.Spread.CheckBoxCellType();
//fsstaff.Sheets[0].RowCount = fsstaff.Sheets[0].RowCount + 1;
//fsstaff.Sheets[0].SpanModel.Add(fsstaff.Sheets[0].RowCount - 1, 0, 1, 3);
fsstaff.Sheets[0].AutoPostBack = false;
// string bindspread = sql;
DataSet dsbindspread = new DataSet();
dsbindspread = da.select_method_wo_parameter(sql, "Text");
//con.Close();
// con.Open();
FarPoint.Web.Spread.TextCellType txt = new FarPoint.Web.Spread.TextCellType();
fsstaff.Sheets[0].RowCount = 0;
if (dsbindspread.Tables[0].Rows.Count > 0)
{
int sno = 0;
for (int rolcount = 0; rolcount < dsbindspread.Tables[0].Rows.Count; rolcount++)
{
sno++;
string name = dsbindspread.Tables[0].Rows[rolcount]["staff_name"].ToString();
string code = dsbindspread.Tables[0].Rows[rolcount]["staff_code"].ToString();
// fsstaff.Sheets[0].ColumnHeader.Columns[0].Label = "S.No";
fsstaff.Sheets[0].ColumnHeader.Columns[0].Label = "Select";
fsstaff.Sheets[0].ColumnHeader.Columns[2].Label = "Staff Name";
fsstaff.Sheets[0].ColumnHeader.Columns[1].Label = "Staff Code";
fsstaff.Sheets[0].Columns[1].CellType = txt;
fsstaff.SheetCorner.Cells[0, 0].Text = "S.No";
fsstaff.SheetCorner.Cells[0, 0].Font.Bold = true;
fsstaff.Sheets[0].Columns[1].Locked = true;//Added By Srinath 16/8/2013
fsstaff.Sheets[0].Columns[2].Locked = true;
fsstaff.Sheets[0].Columns[2].Width = 200;
fsstaff.Sheets[0].Columns[0].HorizontalAlign = HorizontalAlign.Center;
fsstaff.Sheets[0].ColumnCount = 3;
fsstaff.Sheets[0].Columns[2].Width = 250;
fsstaff.Sheets[0].Columns[1].Width = 120;
fsstaff.Width = 515;
//fsstaff.Width = 398;
fsstaff.Sheets[0].RowCount = fsstaff.Sheets[0].RowCount + 1;
fsstaff.Sheets[0].Rows[fsstaff.Sheets[0].RowCount - 1].Font.Bold = false;
//fsstaff.Sheets[0].Cells[fsstaff.Sheets[0].RowCount - 1, 0].Text = Convert.ToString(sno);
//fsstaff.Sheets[0].Cells[fsstaff.Sheets[0].RowCount - 1, 0].HorizontalAlign = HorizontalAlign.Center;
fsstaff.Sheets[0].Cells[fsstaff.Sheets[0].RowCount - 1, 2].Text = name;
fsstaff.Sheets[0].Cells[fsstaff.Sheets[0].RowCount - 1, 2].HorizontalAlign = HorizontalAlign.Left;
fsstaff.Sheets[0].Cells[fsstaff.Sheets[0].RowCount - 1, 1].CellType = txt;
fsstaff.Sheets[0].Cells[fsstaff.Sheets[0].RowCount - 1, 1].Text = dsbindspread.Tables[0].Rows[rolcount]["staff_code"].ToString();
fsstaff.Sheets[0].Cells[fsstaff.Sheets[0].RowCount - 1, 1].HorizontalAlign = HorizontalAlign.Left;
fsstaff.Sheets[0].Cells[fsstaff.Sheets[0].RowCount - 1, 0].CellType = chkcell1;
fsstaff.Sheets[0].Cells[fsstaff.Sheets[0].RowCount - 1, 0].HorizontalAlign = HorizontalAlign.Center;
fsstaff.Sheets[0].Columns[0].HorizontalAlign = HorizontalAlign.Left;
chkcell1.AutoPostBack = true;
}
int rowcount = fsstaff.Sheets[0].RowCount;
fsstaff.Height = 278;
fsstaff.Sheets[0].PageSize = 25 + (rowcount * 20);
fsstaff.SaveChanges();
// con.Close();
}
}
protected void btnviewre_Click(object sender, EventArgs e)
{
try
{
clear();
if (ddlYear1.SelectedIndex == 0)
{
lblerr1.Visible = true;
lblerr1.Text = "Please Select Year";
return;
}
if (ddlMonth1.SelectedIndex == 0)
{
lblerr1.Visible = true;
lblerr1.Text = "Please Select Month";
return;
}
if (ddltype.SelectedIndex == 0)
{
lblerr1.Visible = true;
lblerr1.Text = "Please Select Type";
return;
}
if (ddldegree1.SelectedIndex == 0 && chksubwise.Checked == false)
{
lblerr1.Visible = true;
lblerr1.Text = "Please Select Degree";
return;
}
if (ddlbranch1.SelectedIndex == 0 && chksubwise.Checked == false)
{
lblerr1.Visible = true;
lblerr1.Text = "Please Select branch";
return;
}
if (ddlsem1.SelectedIndex == 0)
{
lblerr1.Visible = true;
lblerr1.Text = "Please Select Semester";
return;
}
if (ddlSubject.SelectedIndex == 0)
{
lblerr1.Visible = true;
lblerr1.Text = "Please Select Subject";
return;
}
fpspread.Width = 906;
fpspread.Visible = false;
fpspread.Sheets[0].RowCount = 0;
fpspread.Sheets[0].ColumnCount = 0;
fpspread.Sheets[0].ColumnCount = 5;
fpspread.Sheets[0].RowHeader.Visible = false;
fpspread.Sheets[0].DefaultStyle.Font.Size = FontUnit.Medium;
fpspread.Sheets[0].ColumnHeader.DefaultStyle.Font.Size = FontUnit.Medium;
fpspread.Sheets[0].ColumnHeader.DefaultStyle.Font.Name = "Book Antiqua";
fpspread.Sheets[0].ColumnHeader.DefaultStyle.Font.Bold = true;
fpspread.Sheets[0].ColumnHeader.DefaultStyle.Font.Size = FontUnit.Medium;
fpspread.Sheets[0].DefaultStyle.Font.Size = FontUnit.Medium;
fpspread.Sheets[0].DefaultStyle.Font.Name = "Book Antiqua";
fpspread.Sheets[0].DefaultStyle.Font.Bold = false;
fpspread.Sheets[0].ColumnHeader.RowCount = 1;
fpspread.Sheets[0].AutoPostBack = false;
fpspread.CommandBar.Visible = false;
FarPoint.Web.Spread.TextCellType txt = new FarPoint.Web.Spread.TextCellType();
fpspread.Sheets[0].ColumnHeader.Columns[0].Width = 50;
fpspread.Sheets[0].ColumnHeader.Columns[1].Width = 100;
fpspread.Sheets[0].ColumnHeader.Columns[2].Width = 100;
fpspread.Sheets[0].ColumnHeader.Columns[3].Width = 250;
fpspread.Sheets[0].ColumnHeader.Columns[4].Width = 80;
fpspread.Sheets[0].ColumnHeader.Columns[0].HorizontalAlign = HorizontalAlign.Center;
fpspread.Sheets[0].ColumnHeader.Columns[1].HorizontalAlign = HorizontalAlign.Left;
fpspread.Sheets[0].ColumnHeader.Columns[2].HorizontalAlign = HorizontalAlign.Left;
fpspread.Sheets[0].ColumnHeader.Columns[3].HorizontalAlign = HorizontalAlign.Left;
fpspread.Sheets[0].ColumnHeader.Columns[4].HorizontalAlign = HorizontalAlign.Center;
fpspread.Sheets[0].Columns[0].VerticalAlign = VerticalAlign.Middle;
fpspread.Sheets[0].Columns[1].VerticalAlign = VerticalAlign.Middle;
fpspread.Sheets[0].Columns[2].VerticalAlign = VerticalAlign.Middle;
fpspread.Sheets[0].Columns[3].VerticalAlign = VerticalAlign.Middle;
fpspread.Sheets[0].Columns[4].VerticalAlign = VerticalAlign.Middle;
fpspread.Sheets[0].Columns[0].Locked = true;
fpspread.Sheets[0].Columns[1].Locked = true;
fpspread.Sheets[0].Columns[2].Locked = true;
fpspread.Sheets[0].Columns[3].Locked = true;
fpspread.Sheets[0].ColumnHeader.Cells[0, 0].Text = "S.No";
fpspread.Sheets[0].ColumnHeader.Cells[0, 0].HorizontalAlign = HorizontalAlign.Center;
fpspread.Sheets[0].ColumnHeader.Cells[0, 1].Text = "Roll No";
fpspread.Sheets[0].ColumnHeader.Cells[0, 1].HorizontalAlign = HorizontalAlign.Center;
fpspread.Sheets[0].ColumnHeader.Cells[0, 2].Text = "Reg No";
fpspread.Sheets[0].ColumnHeader.Cells[0, 2].HorizontalAlign = HorizontalAlign.Center;
fpspread.Sheets[0].ColumnHeader.Cells[0, 3].Text = "Student Name";
fpspread.Sheets[0].ColumnHeader.Cells[0, 3].HorizontalAlign = HorizontalAlign.Center;
fpspread.Sheets[0].ColumnHeader.Cells[0, 4].Text = "Select";
fpspread.Sheets[0].ColumnHeader.Cells[0, 4].HorizontalAlign = HorizontalAlign.Center;
FarPoint.Web.Spread.StyleInfo style2 = new FarPoint.Web.Spread.StyleInfo();
style2.Font.Size = 13;
style2.Font.Name = "Book Antiqua";
style2.Font.Bold = true;
style2.HorizontalAlign = HorizontalAlign.Center;
style2.ForeColor = System.Drawing.Color.White;
style2.BackColor = System.Drawing.Color.Teal;
fpspread.Sheets[0].ColumnHeader.DefaultStyle = new FarPoint.Web.Spread.StyleInfo(style2);
fpspread.Sheets[0].SheetName = " ";
fpspread.Sheets[0].Columns.Default.VerticalAlign = VerticalAlign.Middle;
fpspread.Sheets[0].Rows.Default.VerticalAlign = VerticalAlign.Middle;
fpspread.Sheets[0].DefaultStyle.Font.Name = "Book Antiqua";
fpspread.Sheets[0].DefaultStyle.Font.Size = FontUnit.Medium;
fpspread.Sheets[0].DefaultStyle.Font.Bold = false;
fpspread.Sheets[0].Columns[1].Visible = false;
FarPoint.Web.Spread.CheckBoxCellType chkall = new FarPoint.Web.Spread.CheckBoxCellType();
chkall.AutoPostBack = true;
FarPoint.Web.Spread.CheckBoxCellType chk = new FarPoint.Web.Spread.CheckBoxCellType();
string repav = " and r.cc=1";
if (ddlicatype.SelectedValue.ToString() == "1")
{
repav = " and r.cc=0";
}
//string strquery1 = "select m.roll_no,m.internal_mark,m.external_mark,m.result from Exam_Details ed,mark_entry m,subject s where m.exam_code=ed.exam_code and m.subject_no=s.subject_no and s.subject_code='"+ddlSubject.SelectedValue.ToString()+"' and ed.Exam_Month=11 and ed.Exam_year=2015";
//DataSet dsmarkentry = da.select_method_wo_parameter(strquery1, "Text");
string strbaks = "select ea.roll_no,ea.appl_no,ed.current_semester from Exam_Details ed,exam_application ea,exam_appl_details ead,subject s where ea.exam_code=ed.exam_code and ea.appl_no=ead.appl_no and ead.subject_no=s.subject_no and ead.type='1' and s.subject_code='" + ddlSubject.SelectedValue.ToString() + "' and ed.Exam_Month='" + ddlMonth1.SelectedValue.ToString() + "' and ed.Exam_year='" + ddlYear1.SelectedValue.ToString() + "'";
DataSet dsexamapp = da.select_method_wo_parameter(strbaks, "Text");
string degreeval = string.Empty;
if (chksubwise.Checked == false)
{
degreeval = " and r.degree_code='" + ddlbranch1.SelectedValue.ToString() + "'";
}
string strquery = "select s.subject_no,s.subject_name,subject_code,r.Roll_No,r.Reg_No,r.Stud_Name,r.Batch_Year,r.degree_code,r.current_semester,r.cc,r.delflag,max(m.internal_mark) ICA,max(m.external_mark) EXE from mark_entry m,subject s,Registration r where m.subject_no=s.subject_no and r.Roll_No=m.roll_no and s.subject_code='" + ddlSubject.SelectedValue.ToString() + "' " + degreeval + " " + repav + " and m.roll_no not in (select m1.roll_no from mark_entry m1 where m1.roll_no=m.roll_no and m.subject_no=m1.subject_no and m1.result='Pass') group by s.subject_no,s.subject_name,subject_code,r.Roll_No,r.Reg_No,r.Stud_Name,r.Batch_Year,r.degree_code,r.current_semester,r.cc,r.delflag";
strquery = strquery + " union select s.subject_no,s.subject_name,subject_code,r.Roll_No,r.Reg_No,r.Stud_Name,r.Batch_Year,r.degree_code,r.current_semester,r.cc,r.delflag,max(m.internal_mark) ICA,max(m.external_mark) EXE from mark_entry m,subject s,Registration r,Exam_Details ed,exam_application ea,exam_appl_details ead where m.subject_no=s.subject_no and r.Roll_No=m.roll_no and s.subject_code='" + ddlSubject.SelectedValue.ToString() + "' " + degreeval + " " + repav + " and r.cc=1 and r.Roll_No=ea.roll_no and ed.exam_code=m.exam_code and ed.exam_code=ea.exam_code and ea.appl_no=ead.appl_no and ead.type='1' and m.result='Pass' group by s.subject_no,s.subject_name,subject_code,r.Roll_No,r.Reg_No,r.Stud_Name,r.Batch_Year,r.degree_code,r.current_semester,r.cc,r.delflag order by r.batch_year desc,r.degree_code,r.reg_no desc";
ds2.Dispose();
ds2.Reset();
ds2 = da.select_method_wo_parameter(strquery, "Text");
if (ds2.Tables.Count > 0 && ds2.Tables[0].Rows.Count > 0)
{
btnsave1.Visible = true;
fpspread.Visible = true;
int srno = 0;
fpspread.Sheets[0].RowCount++;
fpspread.Sheets[0].SpanModel.Add(fpspread.Sheets[0].RowCount - 1, 0, 1, 4);
fpspread.Sheets[0].Cells[fpspread.Sheets[0].RowCount - 1, 4].CellType = chkall;
fpspread.Sheets[0].Cells[fpspread.Sheets[0].RowCount - 1, 4].HorizontalAlign = HorizontalAlign.Center;
for (int i = 0; i < ds2.Tables[0].Rows.Count; i++)
{
string rollno = ds2.Tables[0].Rows[i]["Roll_No"].ToString();
string regno = ds2.Tables[0].Rows[i]["Reg_No"].ToString();
string name = ds2.Tables[0].Rows[i]["Stud_Name"].ToString();
string subno = ds2.Tables[0].Rows[i]["subject_no"].ToString();
srno++;
fpspread.Sheets[0].RowCount++;
fpspread.Sheets[0].Cells[fpspread.Sheets[0].RowCount - 1, 0].Text = srno.ToString();
fpspread.Sheets[0].Cells[fpspread.Sheets[0].RowCount - 1, 0].HorizontalAlign = HorizontalAlign.Center;
fpspread.Sheets[0].Cells[fpspread.Sheets[0].RowCount - 1, 4].HorizontalAlign = HorizontalAlign.Center;
fpspread.Sheets[0].Cells[fpspread.Sheets[0].RowCount - 1, 1].CellType = txt;
fpspread.Sheets[0].Cells[fpspread.Sheets[0].RowCount - 1, 2].CellType = txt;
fpspread.Sheets[0].Cells[fpspread.Sheets[0].RowCount - 1, 3].CellType = txt;
fpspread.Sheets[0].Cells[fpspread.Sheets[0].RowCount - 1, 1].Text = rollno;
fpspread.Sheets[0].Cells[fpspread.Sheets[0].RowCount - 1, 2].Text = regno;
fpspread.Sheets[0].Cells[fpspread.Sheets[0].RowCount - 1, 3].Text = name;
fpspread.Sheets[0].Cells[fpspread.Sheets[0].RowCount - 1, 1].Tag = ds2.Tables[0].Rows[i]["batch_year"].ToString();
fpspread.Sheets[0].Cells[fpspread.Sheets[0].RowCount - 1, 2].Tag = ds2.Tables[0].Rows[i]["degree_code"].ToString();
fpspread.Sheets[0].Cells[fpspread.Sheets[0].RowCount - 1, 3].Tag = ds2.Tables[0].Rows[i]["current_semester"].ToString();
fpspread.Sheets[0].Cells[fpspread.Sheets[0].RowCount - 1, 4].Tag = ds2.Tables[0].Rows[i]["subject_no"].ToString();
fpspread.Sheets[0].Cells[fpspread.Sheets[0].RowCount - 1, 4].CellType = chk;
dsexamapp.Tables[0].DefaultView.RowFilter = "roll_no='" + rollno + "'";
DataView dvexmapp = dsexamapp.Tables[0].DefaultView;
if (dvexmapp.Count > 0)
{
fpspread.Sheets[0].Cells[fpspread.Sheets[0].RowCount - 1, 4].Value = 1;
}
else
{
fpspread.Sheets[0].Cells[fpspread.Sheets[0].RowCount - 1, 4].Value = 0;
}
}
}
else
{
lblerr1.Visible = true;
lblerr1.Text = "No Records Found";
}
fpspread.SaveChanges();
fpspread.Width = 600;
fpspread.Sheets[0].PageSize = fpspread.Sheets[0].RowCount;
int hei = 300;
for (int col = 0; col < fpspread.Sheets[0].RowCount; col++)
{
hei = hei + fpspread.Sheets[0].Rows[col].Height;
}
fpspread.Height = hei;
fpspread.SaveChanges();
}
catch (Exception ex)
{
lblerr1.Visible = true;
lblerr1.Text = ex.ToString();
}
}
protected void btnGo_Click(object sender, EventArgs e)
{
try
{
string month = Convert.ToString(ddlMonth.SelectedItem.Value);
string year = Convert.ToString(ddlYear.SelectedItem.Text);
string section = "";
if (ddlsec.Enabled == true)
{
section = "and r.Sections ='" + ddlsec.SelectedItem.Text + "'";
}
DataView dv = new DataView();
DataView dv1 = new DataView();
if (year.Trim() != "" && month.Trim() != "0")
{
//string selectquery = "select count(m.roll_no)as total, s.subject_code,s.subject_name,s.subject_no from Registration r,mark_entry m,Exam_Details e,subject s ,subjectchooser sc where e.exam_code=m.exam_code and m.roll_no=r.Roll_No and m.subject_no=s.subject_no and e.batch_year=r.Batch_Year and s.subType_no =sc.subtype_no and s.subject_no =sc.subject_no and sc.roll_no =r.Roll_No and e.degree_code=r.degree_code and e.batch_year=" + ddlbatch.SelectedItem.Text + " and r.degree_code=" + ddlbranch.SelectedItem.Value + " and e.Exam_Month=" + ddlMonth.SelectedItem.Value + " and e.Exam_year=" + ddlYear.SelectedItem.Text + " and m.attempts =1 and m.result not like 'A%' and semester =" + ddlsem.SelectedItem.Text + " " + section + " group by s.subject_code,s.subject_name ,s.subject_no";
//selectquery = selectquery + " select count(m.roll_no)as total, s.subject_code,s.subject_name,s.subject_no from Registration r,mark_entry m,Exam_Details e,subject s ,subjectchooser sc where e.exam_code=m.exam_code and m.roll_no=r.Roll_No and m.subject_no=s.subject_no and e.batch_year=r.Batch_Year and s.subType_no =sc.subtype_no and s.subject_no =sc.subject_no and sc.roll_no =r.Roll_No and e.degree_code=r.degree_code and e.batch_year=" + ddlbatch.SelectedItem.Text + " and r.degree_code=" + ddlbranch.SelectedItem.Value + " and e.Exam_Month=" + ddlMonth.SelectedItem.Value + " and e.Exam_year=" + ddlYear.SelectedItem.Text + " and m.attempts =1 and m.result not like 'A%' and semester =" + ddlsem.SelectedItem.Text + " " + section + " and result ='Pass' and passorfail ='1' group by s.subject_code,s.subject_name ,s.subject_no";
//selectquery = selectquery + " select count(m.roll_no)as total, s.subject_code,s.subject_name,s.subject_no from Registration r,mark_entry m,Exam_Details e,subject s ,subjectchooser sc where e.exam_code=m.exam_code and m.roll_no=r.Roll_No and m.subject_no=s.subject_no and e.batch_year=r.Batch_Year and s.subType_no =sc.subtype_no and s.subject_no =sc.subject_no and sc.roll_no =r.Roll_No and e.degree_code=r.degree_code and e.batch_year=" + ddlbatch.SelectedItem.Text + " and r.degree_code=" + ddlbranch.SelectedItem.Value + " and e.Exam_Month=" + ddlMonth.SelectedItem.Value + " and e.Exam_year=" + ddlYear.SelectedItem.Text + " and m.attempts =1 and m.result not like 'A%' and semester =" + ddlsem.SelectedItem.Text + " " + section + " and result ='Fail' and passorfail ='0' group by s.subject_code,s.subject_name ,s.subject_no";
//selectquery = selectquery + " select distinct m.roll_no from Registration r,mark_entry m,Exam_Details e,subject s ,subjectchooser sc where e.exam_code=m.exam_code and m.roll_no=r.Roll_No and m.subject_no=s.subject_no and e.batch_year=r.Batch_Year and s.subType_no =sc.subtype_no and s.subject_no =sc.subject_no and sc.roll_no =r.Roll_No and e.degree_code=r.degree_code and e.batch_year=" + ddlbatch.SelectedItem.Text + " and r.degree_code=" + ddlbranch.SelectedItem.Value + " and e.Exam_Month=" + ddlMonth.SelectedItem.Value + " and e.Exam_year=" + ddlYear.SelectedItem.Text + " and m.attempts =1 and m.result not like 'A%' and semester =" + ddlsem.SelectedItem.Text + " " + section + "";
//selectquery = selectquery + " select distinct m.roll_no from Registration r,mark_entry m,Exam_Details e,subject s ,subjectchooser sc where e.exam_code=m.exam_code and m.roll_no=r.Roll_No and m.subject_no=s.subject_no and e.batch_year=r.Batch_Year and s.subType_no =sc.subtype_no and s.subject_no =sc.subject_no and sc.roll_no =r.Roll_No and e.degree_code=r.degree_code and e.batch_year=" + ddlbatch.SelectedItem.Text + " and r.degree_code=" + ddlbranch.SelectedItem.Value + " and e.Exam_Month=" + ddlMonth.SelectedItem.Value + " and e.Exam_year=" + ddlYear.SelectedItem.Text + " and m.attempts =1 and m.result not like 'A%' and semester =" + ddlsem.SelectedItem.Text + " " + section + " and result ='Pass' and passorfail ='1'";
string getexamcode = d2.GetFunction("select exam_code from Exam_Details e where e.Exam_Month=" + ddlMonth.SelectedItem.Value + " and e.Exam_year=" + ddlYear.SelectedItem.Value + " and e.batch_year=" + ddlbatch.SelectedItem.Text + " and e.degree_code=" + ddlbranch.SelectedItem.Value + " and current_semester =" + ddlsem.SelectedItem.Text + "");
if (getexamcode.Trim() != "")
{
string selectquery = "select distinct s.subject_no,subject_code from Registration r,mark_entry m,subject s where r.Roll_No =m.roll_no and s.subject_no =m.subject_no and m.exam_code ='" + getexamcode + "' " + section + " and m.attempts <=1";
selectquery = selectquery + " select Reg_No,r.Roll_No,Stud_Name,result,external_mark,internal_mark,total ,subject_code,m.subject_no from Registration r,mark_entry m,subject s where r.Roll_No =m.roll_no and s.subject_no =m.subject_no and m.exam_code ='" + getexamcode + "' " + section + " and m.attempts <=1";
selectquery = selectquery + " select distinct r.Roll_No from Registration r,mark_entry m,subject s where r.Roll_No =m.roll_no and s.subject_no =m.subject_no and m.exam_code ='" + getexamcode + "' " + section + " and m.attempts <=1 ";
selectquery = selectquery + " select roll_no,bf_moderation_extmrk,subject_no from moderation where exam_code ='" + getexamcode + "' and Exam_Month=" + ddlMonth.SelectedItem.Value + " and Exam_year=" + ddlYear.SelectedItem.Value + " and batch_year=" + ddlbatch.SelectedItem.Text + " and degree_code=" + ddlbranch.SelectedItem.Value + " and semester =" + ddlsem.SelectedItem.Text + "";
ds.Clear();
ds = d2.select_method_wo_parameter(selectquery, "Text");
if (ds.Tables[0].Rows.Count > 0)
{
FpSpread1.Sheets[0].RowCount = 0;
FpSpread1.Sheets[0].ColumnCount = 0;
FpSpread1.Sheets[0].ColumnHeader.RowCount = 2;
FpSpread1.CommandBar.Visible = false;
FpSpread1.Sheets[0].ColumnCount = 4;
FpSpread1.Sheets[0].ColumnHeader.Cells[0, 0].Text = "S.No";
FpSpread1.Sheets[0].ColumnHeaderSpanModel.Add(0, 0, 2, 1);
FpSpread1.Sheets[0].ColumnHeader.Cells[0, 1].Text = "Roll No";
FpSpread1.Sheets[0].ColumnHeaderSpanModel.Add(0, 1, 2, 1);
FpSpread1.Sheets[0].ColumnHeader.Cells[0, 2].Text = "Reg No";
FpSpread1.Sheets[0].ColumnHeaderSpanModel.Add(0, 2, 2, 1);
FpSpread1.Sheets[0].ColumnHeader.Cells[0, 3].Text = "Student Name";
FpSpread1.Sheets[0].ColumnHeaderSpanModel.Add(0, 3, 2, 1);
FpSpread1.Columns[0].Width = 50;
FpSpread1.Columns[1].Width = 100;
FpSpread1.Columns[2].Width = 100;
FpSpread1.Columns[3].Width = 200;
for (int row = 0; row < ds.Tables[0].Rows.Count; row++)
{
FpSpread1.Sheets[0].ColumnCount += 4;
FpSpread1.Sheets[0].ColumnHeaderSpanModel.Add(0, FpSpread1.Sheets[0].ColumnCount - 4, 1, 4);
FpSpread1.Sheets[0].ColumnHeader.Cells[0, FpSpread1.Sheets[0].ColumnCount - 4].Text = Convert.ToString(ds.Tables[0].Rows[row]["subject_code"]);
FpSpread1.Sheets[0].ColumnHeader.Cells[1, FpSpread1.Sheets[0].ColumnCount - 4].Text = "IN";
FpSpread1.Sheets[0].ColumnHeader.Cells[1, FpSpread1.Sheets[0].ColumnCount - 3].Text = "EX";
FpSpread1.Sheets[0].ColumnHeader.Cells[1, FpSpread1.Sheets[0].ColumnCount - 2].Text = "TOT";
FpSpread1.Sheets[0].ColumnHeader.Cells[1, FpSpread1.Sheets[0].ColumnCount - 1].Text = "R";
}
if (ds.Tables[2].Rows.Count > 0)
{
int sno = 0;
int column = 3;
FarPoint.Web.Spread.TextCellType txt = new FarPoint.Web.Spread.TextCellType();
for (int r = 0; r < ds.Tables[2].Rows.Count; r++)
{
string rollno = Convert.ToString(ds.Tables[2].Rows[r]["Roll_No"]);
ds.Tables[1].DefaultView.RowFilter = "Roll_No='" + rollno + "'";
dv = ds.Tables[1].DefaultView;
if (dv.Count > 0)
{
sno++;
FpSpread1.Sheets[0].RowCount++;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 0].Text = Convert.ToString(sno);
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 0].HorizontalAlign = HorizontalAlign.Center;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 1].CellType = txt;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 1].Text = Convert.ToString(dv[0]["Roll_No"]);
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 2].CellType = txt;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 2].Text = Convert.ToString(dv[0]["Reg_No"]);
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 3].CellType = txt;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 3].Text = Convert.ToString(dv[0]["Stud_Name"]);
column = 4;
for (int r1 = 0; r1 < ds.Tables[0].Rows.Count; r1++)
{
column += 4;
dv.RowFilter = "subject_no='" + ds.Tables[0].Rows[r1]["subject_no"] + "' and Roll_No='" + rollno + "'";
if (dv.Count > 0)
{
string examint = dv[0]["internal_mark"].ToString();
if (examint.Trim() == "" || examint == null)
{
examint = "0";
}
if (Convert.ToInt32(examint) >= 0)
{
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, column - 4].Text = Convert.ToString(examint);
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, column - 4].HorizontalAlign = HorizontalAlign.Center;
}
else
{
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, column - 4].Text = Convert.ToString("-");
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, column - 4].HorizontalAlign = HorizontalAlign.Center;
}
if (ds.Tables[3].Rows.Count > 0)
{
ds.Tables[3].DefaultView.RowFilter = "subject_no='" + ds.Tables[0].Rows[r1]["subject_no"] + "' and Roll_No='" + rollno + "'";
dv1 = ds.Tables[3].DefaultView;
}
if (dv1.Count == 0)
{
string extyer = dv[0]["external_mark"].ToString();
if (extyer.Trim() == "" || extyer == null)
{
extyer = "0";
}
if (Convert.ToInt32(extyer) >= 0)
{
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, column - 3].Text = Convert.ToString(extyer);
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, column - 3].HorizontalAlign = HorizontalAlign.Center;
}
else
{
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, column - 3].Text = Convert.ToString("AB");
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, column - 3].HorizontalAlign = HorizontalAlign.Center;
}
string exttotal = dv[0]["total"].ToString();
if (exttotal.Trim() == "" || exttotal == null)
{
exttotal = "0";
}
if (Convert.ToInt32(exttotal) >= 0)
{
if (Convert.ToString(dv[0]["result"]).ToUpper() == "PASS")
{
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, column - 2].Text = Convert.ToString(exttotal);
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, column - 2].HorizontalAlign = HorizontalAlign.Center;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, column - 1].Text = Convert.ToString("P");
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, column - 1].HorizontalAlign = HorizontalAlign.Center;
}
else
{
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, column - 2].Text = Convert.ToString("-");
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, column - 2].HorizontalAlign = HorizontalAlign.Center;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, column - 1].Text = Convert.ToString("RA");
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, column - 1].HorizontalAlign = HorizontalAlign.Center;
}
}
else
{
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, column - 2].Text = Convert.ToString("-");
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, column - 2].HorizontalAlign = HorizontalAlign.Center;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, column - 1].Text = Convert.ToString("RA");
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, column - 1].HorizontalAlign = HorizontalAlign.Center;
}
}
else
{
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, column - 3].Text = Convert.ToString(dv1[0]["bf_moderation_extmrk"]);
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, column - 3].HorizontalAlign = HorizontalAlign.Center;
// FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, column - 3].BackColor = Color.Green;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, column - 2].Text = Convert.ToString("-");
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, column - 2].HorizontalAlign = HorizontalAlign.Center;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, column - 1].Text = Convert.ToString("RA");
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, column - 1].HorizontalAlign = HorizontalAlign.Center;
}
}
}
}
}
}
FpSpread1.Sheets[0].PageSize = FpSpread1.Sheets[0].RowCount;
if (Session["Rollflag"] == "0")
{
FpSpread1.Sheets[0].Columns[1].Visible = false;
}
else
{
FpSpread1.Sheets[0].Columns[1].Visible = true;
}
if (Session["Regflag"] == "0")
{
FpSpread1.Sheets[0].Columns[2].Visible = false;
}
else
{
FpSpread1.Sheets[0].Columns[2].Visible = true;
}
FpSpread1.Visible = true;
rptprint.Visible = true;
}
else
{
FpSpread1.Visible = false;
rptprint.Visible = false;
errorlabl.Visible = true;
errorlabl.Text = "No Records Found";
}
}
else
{
FpSpread1.Visible = false;
rptprint.Visible = false;
errorlabl.Visible = true;
errorlabl.Text = "No Records Found";
}
}
}
catch
{
}
}
protected void btn_go_Click(object sender, EventArgs e)
{
try
{
string itemheadercode = "";
for (int i = 0; i < cbl_itemname.Items.Count; i++)
{
if (cbl_itemname.Items[i].Selected == true)
{
if (itemheadercode == "")
{
itemheadercode = "" + cbl_itemname.Items[i].Value.ToString() + "";
}
else
{
itemheadercode = itemheadercode + "'" + "," + "'" + cbl_itemname.Items[i].Value.ToString() + "";
}
}
}
if (itemheadercode.Trim() != "")
{
string selectquery = "";
if (txt_searchby.Text.Trim() != "")
{
selectquery = "select StudItemCode,t.TextVal,case when PayMode=1 then 'Monthly' when PayMode =2 then 'Yearly' when PayMode =3 then 'Semester' end as paymode,StudItemCost,(Select Fee_Type from Fee_Info F,Acctheader H,Acctinfo I WHERE F.Header_ID = H.Header_ID AND H.Acct_ID = I.Acct_ID AND fee = 1 AND Fee_Code =StudItemFeeLed and fee_type not in ('Cash','Misc','Income & Expenditure') and fee_type not in (select bankname from bank_master1))as StudItemFeeLed,(select TextVal from TextValTable where TextCriteria ='SIdec' and TextCode= ISNULL( StudItemDesc,0))as StudItemDesc from StudItemMaster s,TextValTable t where s.StudItemCode =t.TextCode and StudItemCode =CONVERT(numeric ,isnull( (select Textcode from TextValTable where TextVal ='" + txt_searchby.Text + "'),0))";
}
else
{
selectquery = "select StudItemCode,t.TextVal,case when PayMode=1 then 'Monthly' when PayMode =2 then 'Yearly' when PayMode =3 then 'Semester' end as paymode,StudItemCost,(Select Fee_Type from Fee_Info F,Acctheader H,Acctinfo I WHERE F.Header_ID = H.Header_ID AND H.Acct_ID = I.Acct_ID AND fee = 1 AND Fee_Code =StudItemFeeLed and fee_type not in ('Cash','Misc','Income & Expenditure') and fee_type not in (select bankname from bank_master1))as StudItemFeeLed,(select TextVal from TextValTable where TextCriteria ='SIdec' and TextCode= ISNULL( StudItemDesc,0))as StudItemDesc from StudItemMaster s,TextValTable t where s.StudItemCode =t.TextCode and StudItemCode in ('" + itemheadercode + "')";
}
ds.Clear();
ds = d2.select_method_wo_parameter(selectquery, "Text");
if (ds.Tables[0].Rows.Count > 0)
{
Fpspread1.Sheets[0].RowCount = 0;
Fpspread1.Sheets[0].ColumnCount = 0;
Fpspread1.CommandBar.Visible = false;
Fpspread1.Sheets[0].AutoPostBack = true;
Fpspread1.Sheets[0].ColumnHeader.RowCount = 1;
Fpspread1.Sheets[0].RowHeader.Visible = false;
Fpspread1.Sheets[0].ColumnCount = 6;
FarPoint.Web.Spread.StyleInfo darkstyle = new FarPoint.Web.Spread.StyleInfo();
darkstyle.BackColor = ColorTranslator.FromHtml("#0CA6CA");
darkstyle.ForeColor = Color.White;
Fpspread1.ActiveSheetView.ColumnHeader.DefaultStyle = darkstyle;
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 0].Text = "S.No";
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 0].Font.Bold = true;
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 0].Font.Name = "Book Antiqua";
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 0].Font.Size = FontUnit.Medium;
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 0].HorizontalAlign = HorizontalAlign.Center;
Fpspread1.Columns[0].Width = 50;
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 1].Text = "Item Name";
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 1].Font.Bold = true;
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 1].Font.Name = "Book Antiqua";
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 1].Font.Size = FontUnit.Medium;
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 1].HorizontalAlign = HorizontalAlign.Center;
Fpspread1.Columns[1].Width = 100;
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 2].Text = "Mode Of Payment";
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 2].Font.Bold = true;
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 2].Font.Name = "Book Antiqua";
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 2].Font.Size = FontUnit.Medium;
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 2].HorizontalAlign = HorizontalAlign.Center;
Fpspread1.Columns[2].Width = 100;
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 3].Text = "Cost";
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 3].Font.Bold = true;
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 3].Font.Name = "Book Antiqua";
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 3].Font.Size = FontUnit.Medium;
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 3].HorizontalAlign = HorizontalAlign.Center;
Fpspread1.Columns[3].Width = 50;
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 4].Text = "Ledger";
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 4].Font.Bold = true;
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 4].Font.Name = "Book Antiqua";
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 4].Font.Size = FontUnit.Medium;
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 4].HorizontalAlign = HorizontalAlign.Center;
Fpspread1.Columns[4].Width = 150;
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 5].Text = "Description";
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 5].Font.Bold = true;
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 5].Font.Name = "Book Antiqua";
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 5].Font.Size = FontUnit.Medium;
Fpspread1.Sheets[0].ColumnHeader.Cells[0, 5].HorizontalAlign = HorizontalAlign.Center;
FarPoint.Web.Spread.TextCellType txtcell = new FarPoint.Web.Spread.TextCellType();
for (int row = 0; row < ds.Tables[0].Rows.Count; row++)
{
Fpspread1.Sheets[0].RowCount++;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 0].Text = Convert.ToString(row + 1);
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 0].HorizontalAlign = HorizontalAlign.Center;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 0].Font.Size = FontUnit.Medium;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 0].Font.Name = "Book Antiqua";
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 1].Text = Convert.ToString(ds.Tables[0].Rows[row]["TextVal"]);
// Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 1].Tag = Convert.ToString(ds.Tables[0].Rows[row]["StudItemCode"]);
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 1].HorizontalAlign = HorizontalAlign.Left;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 1].Font.Size = FontUnit.Medium;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 1].Font.Name = "Book Antiqua";
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 2].Text = Convert.ToString(ds.Tables[0].Rows[row]["PayMode"]);
//Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 2].Tag = Convert.ToString(ds.Tables[0].Rows[row]["rpu"]);
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 2].HorizontalAlign = HorizontalAlign.Left;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 2].Font.Size = FontUnit.Medium;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 2].Font.Name = "Book Antiqua";
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 3].Text = Convert.ToString(ds.Tables[0].Rows[row]["StudItemCost"]);
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 3].CellType = txtcell;
// Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 3].Tag = Convert.ToString(ds.Tables[0].Rows[row]["Stock_Value"]);
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 3].HorizontalAlign = HorizontalAlign.Right;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 3].Font.Size = FontUnit.Medium;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 3].Font.Name = "Book Antiqua";
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 4].Text = Convert.ToString(ds.Tables[0].Rows[row]["StudItemFeeLed"]);
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 4].HorizontalAlign = HorizontalAlign.Left;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 4].Font.Size = FontUnit.Medium;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 4].Font.Name = "Book Antiqua";
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 5].Text = Convert.ToString(ds.Tables[0].Rows[row]["StudItemDesc"]);
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 5].HorizontalAlign = HorizontalAlign.Left;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 5].Font.Size = FontUnit.Medium;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 5].Font.Name = "Book Antiqua";
}
Fpspread1.Visible = true;
rptprint.Visible = true;
div1.Visible = true;
lbl_error.Visible = false;
Fpspread1.Sheets[0].PageSize = Fpspread1.Sheets[0].RowCount;
}
else
{
div1.Visible = false;
Fpspread1.Visible = false;
rptprint.Visible = false;
lbl_error.Visible = true;
lbl_error.Text = "No Records Found";
}
}
else
{
div1.Visible = false;
Fpspread1.Visible = false;
rptprint.Visible = false;
lbl_error.Visible = true;
lbl_error.Text = "Please Select Any one Item Name";
}
}
catch
{
}
}
protected void FSpread1_SelectedIndexChanged(Object sender, EventArgs e)
{
try
{
if (Cellclick == true)
{
modelpopsetting.Show();
FpSpread1.Visible = true;
FarPoint.Web.Spread.TextCellType txtcell = new FarPoint.Web.Spread.TextCellType();
FarPoint.Web.Spread.DoubleCellType speed1 = new FarPoint.Web.Spread.DoubleCellType();
Panelshow.Visible = true;
spreadexcel1.Visible = false;
spreadpdf1.Visible = false;
FpSpread2.Visible = true;
string query = "";
string activerow = "";
string activecol = "";
double speed = 0;
string vehid = "";
int sno = 1;
DataSet dcal = new DataSet();
FarPoint.Web.Spread.StyleInfo style2 = new FarPoint.Web.Spread.StyleInfo();
style2.Font.Size = 13;
style2.Font.Name = "Book Antiqua";
style2.Font.Bold = true;
style2.HorizontalAlign = HorizontalAlign.Center;
style2.ForeColor = Color.White;
style2.BackColor = Color.Teal;
FpSpread2.Sheets[0].ColumnCount = 10;
FpSpread2.Sheets[0].SheetCornerStyle = new FarPoint.Web.Spread.StyleInfo(style2);
FpSpread2.Sheets[0].ColumnHeader.DefaultStyle = new FarPoint.Web.Spread.StyleInfo(style2);
FpSpread2.Sheets[0].AllowTableCorner = true;
FpSpread2.Sheets[0].SheetCornerStyle = new FarPoint.Web.Spread.StyleInfo(style2);
FpSpread2.Sheets[0].AutoPostBack = true;
FpSpread2.Sheets[0].RowCount = 0;
activerow = FpSpread1.ActiveSheetView.ActiveRow.ToString();
activecol = FpSpread1.ActiveSheetView.ActiveColumn.ToString();
string vechicle = FpSpread1.Sheets[0].Cells[Convert.ToInt32(activerow), 1].Text;
string route = FpSpread1.Sheets[0].Cells[Convert.ToInt32(activerow), 2].Text;
string course = FpSpread1.Sheets[0].Cells[Convert.ToInt32(activerow), 3].Text;
FpSpread2.Sheets[0].ColumnHeader.RowCount = 2;
FpSpread2.Sheets[0].ColumnHeaderSpanModel.Add(0, 0, 2, 1);
FpSpread2.Sheets[0].ColumnHeader.Cells[0, 0].Text = "S.No";
FpSpread2.Sheets[0].ColumnHeader.Cells[0, 0].Border.BorderColor = Color.Black;
FpSpread2.Sheets[0].Columns[0].Width = 10;
FpSpread2.Sheets[0].ColumnHeader.Cells[0, 1].Text = "Vehicle ID ";
FpSpread2.Sheets[0].ColumnHeader.Cells[0, 1].Border.BorderColor = Color.Black;
FpSpread2.Sheets[0].Columns[1].Visible = false;
FpSpread2.Sheets[0].ColumnHeader.Cells[0, 2].Text = "Route ID";
FpSpread2.Sheets[0].ColumnHeader.Cells[0, 2].Border.BorderColor = Color.Black;
FpSpread2.Sheets[0].Columns[2].Visible = false;
FpSpread2.Sheets[0].ColumnHeaderSpanModel.Add(0, 5, 2, 1);
FpSpread2.Sheets[0].ColumnHeader.Cells[0, 3].Text = "Start Time";
FpSpread2.Sheets[0].ColumnHeader.Cells[0, 3].Border.BorderColor = Color.Black;
FpSpread2.Sheets[0].Columns[3].Width = 90;
FpSpread2.Sheets[0].ColumnHeader.Cells[0, 4].Text = "End Time";
FpSpread2.Sheets[0].ColumnHeader.Cells[0, 4].Border.BorderColor = Color.Black;
FpSpread2.Sheets[0].Columns[4].Width = 80;
FpSpread2.Sheets[0].ColumnHeaderSpanModel.Add(0, 4, 2, 1);
FpSpread2.Sheets[0].ColumnHeaderSpanModel.Add(0, 3, 2, 1);
FpSpread2.Sheets[0].ColumnHeaderSpanModel.Add(0, 6, 2, 1);
FpSpread2.Sheets[0].ColumnHeader.Cells[0, 5].Text = "Stage";
FpSpread2.Sheets[0].ColumnHeader.Cells[0, 5].Border.BorderColor = Color.Black;
FpSpread2.Sheets[0].Columns[5].Width = 150;
FpSpread2.Sheets[0].ColumnHeaderSpanModel.Add(1, 0, 1, 8);
FpSpread2.Sheets[0].ColumnHeader.Cells[0, 6].Text = "Speed";
FpSpread2.Sheets[0].Columns[6].Width = 90;
FpSpread2.Sheets[0].ColumnHeader.Cells[0, 7].Text = "Total No of Student";
FpSpread2.Sheets[0].Columns[7].Width = 150;
FpSpread2.Sheets[0].ColumnHeader.Cells[0, 8].Text = "";
FpSpread2.Sheets[0].ColumnHeader.Cells[0, 9].Text = "";
FpSpread2.Sheets[0].ColumnHeaderSpanModel.Add(0, 7, 1, 3);
FpSpread2.Sheets[0].ColumnHeader.Cells[1, 7].Text = "Male";
FpSpread2.Sheets[0].Columns[8].Width = 150;
FpSpread2.Sheets[0].ColumnHeader.Cells[1, 8].Text = "Female";
FpSpread2.Sheets[0].Columns[9].Width = 150;
FpSpread2.Sheets[0].ColumnHeader.Cells[1, 9].Text = "Staff";
FpSpread2.Sheets[0].Columns[0].Locked = true;
FpSpread2.Sheets[0].Columns[1].Locked = true;
FpSpread2.Sheets[0].Columns[2].Locked = true;
FpSpread2.Sheets[0].Columns[3].Locked = true;
FpSpread2.Sheets[0].Columns[4].Locked = true;
string currenttime = Convert.ToString(DateTime.Now);
string[] split = currenttime.Split(); // date time am/pm
string timenow = split[2].ToString();
string curtime = split[1].ToString();
if (timenow.Trim() == "AM")
{
query = "select r.Route_ID,r.Mdate,r.Stage_Name as stage,Arr_Time,Dep_Time,sess,Veh_ID,s.Stage_id,s.Stage_Name,s.Address from RouteMaster r,Stage_Master s where str(r.Stage_Name)=str(s.Stage_id) and sess='M' and Route_ID in('" + route + "') order by convert(nvarchar(5),Dep_Time,103) asc ";
}
else
{
query = "select r.Route_ID,r.Mdate,r.Stage_Name as stage,Arr_Time,Dep_Time,sess,Veh_ID,s.Stage_id,s.Stage_Name,s.Address from RouteMaster r,Stage_Master s where str(r.Stage_Name)=str(s.Stage_id) and sess='A' and Route_ID in('" + route + "') order by convert(nvarchar(5),Dep_Time,103) asc ";
}
dcal = da.select_method_wo_parameter(query, "text");
for (int temp = 0; temp < dcal.Tables[0].Rows.Count; temp++)
{
string routid = dcal.Tables[0].Rows[temp]["Route_ID"].ToString();
vehid = dcal.Tables[0].Rows[temp]["Veh_ID"].ToString();
string stastage = dcal.Tables[0].Rows[temp]["Stage_Name"].ToString();
string enddingtime = dcal.Tables[0].Rows[temp]["Dep_Time"].ToString();
string starttime = dcal.Tables[0].Rows[temp]["Arr_Time"].ToString();
string stageid = dcal.Tables[0].Rows[temp]["Stage_id"].ToString();
FpSpread2.Sheets[0].RowCount++;
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 0].Text = sno.ToString();
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 0].HorizontalAlign = HorizontalAlign.Center;
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 1].Tag = vehid.ToString();
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 1].Text = Convert.ToString(FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 1].Tag);
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 1].HorizontalAlign = HorizontalAlign.Center;
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 2].Text = routid.ToString();
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 2].HorizontalAlign = HorizontalAlign.Center;
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 3].CellType = txtcell;
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 3].Text = starttime.ToString();
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 3].HorizontalAlign = HorizontalAlign.Center;
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 4].CellType = txtcell;
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 4].Text = enddingtime.ToString();
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 4].HorizontalAlign = HorizontalAlign.Center;
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 5].Text = stastage.ToString();
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 5].HorizontalAlign = HorizontalAlign.Left;
//string tottab = " select COUNT(*) as tot from Registration r,applyn a where Boarding = '" + stageid + "' and VehID ='" + vehid + "'";
//DataSet spreadtot = new DataSet();
//spreadtot = da.select_method_wo_parameter(tottab, "text");
//if (spreadtot.Tables[0].Rows.Count > 0)
//{
// string totcount = spreadtot.Tables[0].Rows[0]["tot"].ToString();
// FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 7].Text = totcount.ToString();
// FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 7].HorizontalAlign = HorizontalAlign.Center;
//}
//else
//{
// FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 7].Text = "";
// FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 7].HorizontalAlign = HorizontalAlign.Center;
// FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 7].v
//}
string maletab = " select COUNT(*) as male from Registration r,applyn a where r.App_No = a.app_no and Boarding = '" + stageid + "' and VehID ='" + vehid + "' and sex = 0";
DataSet spreadmale = new DataSet();
spreadmale = da.select_method_wo_parameter(maletab, "text");
if (spreadmale.Tables[0].Rows.Count > 0)
{
string malecount = spreadmale.Tables[0].Rows[0]["male"].ToString();
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 7].Text = malecount.ToString();
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 7].HorizontalAlign = HorizontalAlign.Center;
}
else
{
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 7].Text = "";
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 7].HorizontalAlign = HorizontalAlign.Center;
}
string femaletab = " select COUNT(*) as female from Registration r,applyn a where r.App_No = a.app_no and Boarding = '" + stageid + "' and VehID ='" + vehid + "' and sex = 1";
DataSet spreadfemale = new DataSet();
spreadfemale = da.select_method_wo_parameter(femaletab, "text");
if (spreadfemale.Tables[0].Rows.Count > 0)
{
string femalecount = spreadfemale.Tables[0].Rows[0]["female"].ToString();
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 8].Text = femalecount.ToString();
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 8].HorizontalAlign = HorizontalAlign.Center;
}
else
{
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 8].Text = "";
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 8].HorizontalAlign = HorizontalAlign.Center;
}
string stafftab = "select COUNT(*) as staffcount from staffmaster m where Boarding = '" + stageid + "' and VehID = '" + vehid + "' ";
DataSet spreadstaff = new DataSet();
spreadstaff = da.select_method_wo_parameter(stafftab, "text");
if (spreadstaff.Tables[0].Rows.Count > 0)
{
string staffcount = spreadstaff.Tables[0].Rows[0]["staffcount"].ToString();
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 9].Text = staffcount.ToString();
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 9].HorizontalAlign = HorizontalAlign.Center;
}
else
{
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 9].Text = "";
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 9].HorizontalAlign = HorizontalAlign.Center;
}
FpSpread2.Sheets[0].Columns[6].Visible = false;
sno++;
string stage = "select GPSFixstatus,Speed from VTSGPRSData where VehicleID ='" + vehid + "'";
DataSet dstage = new DataSet();
string gpstage = "";
dstage = da.select_method_wo_parameter(stage, "Text");
if (dstage.Tables[0].Rows.Count > 0)
{
gpstage = dstage.Tables[0].Rows[0]["GPSFixstatus"].ToString();
speed = Convert.ToDouble(dstage.Tables[0].Rows[0]["Speed"]);
}
else
{
gpstage = "";
//speed = "Speed Not Found";
}
string status = "";
ArrayList add = new ArrayList();
if (dstage.Tables[0].Rows.Count > 0)
{
stage = stage + " select distinct address from RouteMaster where Route_ID='" + routid + "' ";
dstage = da.select_method_wo_parameter(stage, "Text");
for (int b = 0; b < dstage.Tables[1].Rows.Count; b++)
{
status = dstage.Tables[1].Rows[b]["address"].ToString();
add.Add(status);
}
}
else
{
}
if (add.Contains(gpstage))
{
for (int search = 0; search < FpSpread2.Sheets[0].RowCount; search++)
{
if (stastage == gpstage)
{
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 5].BackColor = Color.Khaki;
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 1].BackColor = Color.Khaki;
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 2].BackColor = Color.Khaki;
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 3].BackColor = Color.Khaki;
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 4].BackColor = Color.Khaki;
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 0].BackColor = Color.Khaki;
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 6].BackColor = Color.Khaki;
Error.Visible = false;
}
}
}
else
{
//Error.Text = "Stage Not Found";
//Error.Visible = true;
}
if (add.Contains(gpstage))
{
for (int search = 0; search < FpSpread2.Sheets[0].RowCount; search++)
{
if (stastage == gpstage)
{
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 6].CellType = speed1;
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 6].Text = Convert.ToString(speed);
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 6].HorizontalAlign = HorizontalAlign.Left;
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 6].CellType = speed1;
}
}
}
else
{
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 6].Text = "";
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 6].HorizontalAlign = HorizontalAlign.Center;
}
}
string count = "select COUNT(*) as count from Registration r,applyn a where r.App_No = a.app_no and VehID = '" + vehid + "' ";
DataSet dstot = new DataSet();
dstot = da.select_method_wo_parameter(count, "text");
Label1.Text = dstot.Tables[0].Rows[0]["count"].ToString();
string female = "select COUNT(*) as count from Registration r,applyn a where r.App_No = a.app_no and VehID = '" + vehid + "'and sex = 1";
DataSet dsfemale = new DataSet();
dstot = da.select_method_wo_parameter(female, "text");
Label3.Text = dstot.Tables[0].Rows[0]["count"].ToString();
lblmale.Text = "Male";
Label5.Text = " " + ":";
lblstaff.Text = "Staff";
Label6.Text = " " + ":";
lblfemale.Text = "Female";
Label7.Text = " " + ":";
Totalstudent.Text = "Total No of Student ";
Label8.Text = ":";
string male = "select COUNT(*) as count from Registration r,applyn a where r.App_No = a.app_no and VehID = '" + vehid + "'and sex = 0";
DataSet dsmale = new DataSet();
dstot = da.select_method_wo_parameter(male, "text");
Label2.Text = dstot.Tables[0].Rows[0]["count"].ToString();
string staff = "select COUNT(*) as count from staffmaster m where VehID = '" + vehid + "'";
DataSet dsstaff = new DataSet();
dstot = da.select_method_wo_parameter(staff, "text");
Label4.Text = dstot.Tables[0].Rows[0]["count"].ToString();
FpSpread2.Sheets[0].SetColumnMerge(1, FarPoint.Web.Spread.Model.MergePolicy.Always);
FpSpread2.Sheets[0].SetColumnMerge(2, FarPoint.Web.Spread.Model.MergePolicy.Always);
FpSpread2.Sheets[0].Columns[0].VerticalAlign = VerticalAlign.Middle;
FpSpread2.Sheets[0].Columns[1].VerticalAlign = VerticalAlign.Middle;
FpSpread2.Sheets[0].Columns[2].VerticalAlign = VerticalAlign.Middle;
sno++;
modelpopsetting.Show();
Panelshow.Visible = true;
FpSpread2.Visible = true;
FpSpread1.Visible = true;
Label1.Visible = true;
Label2.Visible = true;
Label3.Visible = true;
Label4.Visible = true;
Totalstudent.Visible = true;
lblmale.Visible = true;
lblfemale.Visible = true;
lblstaff.Visible = true;
Label5.Visible = true;
Label6.Visible = true;
Label7.Visible = true;
Label8.Visible = true;
FpSpread2.Sheets[0].PageSize = FpSpread2.Sheets[0].RowCount;
}
else
{
FpSpread2.Visible = false;
}
}
catch (Exception ex)
{ }
}
protected void Buttongo_Click(object sender, EventArgs e)
{
try
{
ss2 = "";
p = "";
FpSpread2.Visible = false; btnreset.Visible = false;
FpSpread2.CommandBar.Visible = false;
FpSpread2.Sheets[0].RowCount = 0;
FpSpread2.Sheets[0].ColumnCount = 5;
FpSpread2.Sheets[0].Columns[0].Width = 40;
FpSpread2.Sheets[0].Columns[1].Width = 113;
FpSpread2.Sheets[0].Columns[2].Width = 342;
FpSpread2.Sheets[0].Columns[3].Width = 83;
FpSpread2.Sheets[0].Columns[4].Width = 90;
FpSpread2.Sheets[0].Columns[0].Locked = true;
FpSpread2.Sheets[0].Columns[1].Locked = true;
FpSpread2.Sheets[0].Columns[2].Locked = true;
FpSpread2.Sheets[0].Columns[4].Locked = true;
FpSpread2.Sheets[0].Columns[0].HorizontalAlign = HorizontalAlign.Center;
FpSpread2.Sheets[0].Columns[3].HorizontalAlign = HorizontalAlign.Center;
FpSpread2.Sheets[0].Columns[4].HorizontalAlign = HorizontalAlign.Center;
FpSpread2.Sheets[0].RowHeader.Visible = false;
FpSpread2.Sheets[0].DefaultStyle.Font.Size = FontUnit.Medium;
FpSpread2.Sheets[0].ColumnHeader.DefaultStyle.Font.Size = FontUnit.Medium;
FpSpread2.Sheets[0].ColumnHeader.DefaultStyle.Font.Name = "Book Antiqua";
FpSpread2.Sheets[0].ColumnHeader.DefaultStyle.Font.Bold = true;
FpSpread2.Sheets[0].DefaultStyle.Font.Size = FontUnit.Medium;
FpSpread2.Sheets[0].DefaultStyle.Font.Name = "Book Antiqua";
FpSpread2.Sheets[0].SheetCorner.RowCount = 2;
FpSpread2.Sheets[0].ColumnHeaderSpanModel.Add(0, 0, 2, 1);
FpSpread2.Sheets[0].ColumnHeaderSpanModel.Add(0, 1, 2, 1);
FpSpread2.Sheets[0].ColumnHeaderSpanModel.Add(0, 2, 2, 1);
FpSpread2.Sheets[0].ColumnHeaderSpanModel.Add(0, 3, 2, 1);
FpSpread2.Sheets[0].ColumnHeaderSpanModel.Add(0, 4, 2, 1);
FpSpread2.Sheets[0].ColumnHeader.Cells[0, 0].Text = "S.No";
FpSpread2.Sheets[0].ColumnHeader.Cells[0, 1].Text = "Subject Code";
FpSpread2.Sheets[0].ColumnHeader.Cells[0, 2].Text = "Subject Name";
FpSpread2.Sheets[0].ColumnHeader.Cells[0, 3].Text = "Select";
FpSpread2.Sheets[0].ColumnHeader.Cells[0, 4].Text = "Priority";
FarPoint.Web.Spread.TextCellType txt = new FarPoint.Web.Spread.TextCellType();
FarPoint.Web.Spread.CheckBoxCellType chkcell = new FarPoint.Web.Spread.CheckBoxCellType();
FpSpread2.Sheets[0].RowCount = 0;
FarPoint.Web.Spread.CheckBoxCellType chkcell1 = new FarPoint.Web.Spread.CheckBoxCellType();
FpSpread2.Sheets[0].RowCount = FpSpread2.Sheets[0].RowCount + 1;
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 3].CellType = chkcell1;
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 3].Locked = true;
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 4].HorizontalAlign = HorizontalAlign.Center;
FpSpread2.Sheets[0].FrozenRowCount = 1;
chkcell1.AutoPostBack = true;
chkcell.AutoPostBack = true;
FpSpread2.Sheets[0].AutoPostBack = false;
string batchyear = ddlbatch.SelectedValue.ToString();
string degreecode = ddlbranch.SelectedValue.ToString();
string year = ddlbatch.SelectedValue.ToString();
string degree = ddldegree.SelectedItem.ToString();
string course = ddldegree.SelectedItem.ToString();
string depart_code = ddlbranch.SelectedValue.ToString();
string batchyearatt = ddlbatch.SelectedValue.ToString();
string studinfo = "";
//studinfo = "select distinct s.subject_no,subject_code,subject_name,subject_type,ss.subtype_no,subjectpriority from subject s,syllabus_master sm ,sub_sem ss where ss.syll_code=sm.syll_code and sm.syll_code=s.syll_code and ss.syll_code=s.syll_code and ss.subtype_no=s.subtype_no and batch_year='" + ddlbatch.SelectedItem.Text.ToString() + "' and semester='" + ddlsem.SelectedItem.Text.ToString() + "' and degree_code='" + depart_code + "' and ss.subtype_no='" + ddlsubtype.SelectedItem.Value.ToString() + "'";
studinfo = "select distinct s.subject_no,subject_code,subject_name,subject_type,ss.subtype_no,subjectpriority from subject s,syllabus_master sm ,sub_sem ss where ss.syll_code=sm.syll_code and sm.syll_code=s.syll_code and ss.syll_code=s.syll_code and ss.subtype_no=s.subtype_no and batch_year='" + ddlbatch.SelectedItem.Text.ToString() + "' and semester='" + ddlsem.SelectedItem.Text.ToString() + "' and degree_code='" + depart_code + "' order by subject_code ";
DataSet dsstudinfo = da.select_method_wo_parameter(studinfo, "Text");
if (dsstudinfo.Tables[0].Rows.Count > 0)
{
btnsave.Visible = true;
int sno = 0;
for (int studcount = 0; studcount < dsstudinfo.Tables[0].Rows.Count; studcount++)
{
string regno = "";
string studname = "";
string rollno = "";
FpSpread2.Visible = true;
sno++;
FpSpread2.Sheets[0].RowCount = FpSpread2.Sheets[0].RowCount + 1;
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 0].Text = sno + "";
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 1].CellType = txt;
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 1].Text = dsstudinfo.Tables[0].Rows[studcount]["subject_code"].ToString();
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 1].Note = dsstudinfo.Tables[0].Rows[studcount]["subject_no"].ToString();
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 2].CellType = txt;
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 2].Text = dsstudinfo.Tables[0].Rows[studcount]["subject_name"].ToString();
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 2].Text = dsstudinfo.Tables[0].Rows[studcount]["subject_name"].ToString();
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 4].Text = dsstudinfo.Tables[0].Rows[studcount]["subjectpriority"].ToString();;
if (FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 4].Text.Trim() != "")
{
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 3].Locked = true;
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 3].Value = 1;
btnreset.Visible = true;
}
else
{
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 3].Locked = false;
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 3].Value = 0;
btnreset.Visible = false;
}
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 3].CellType = chkcell;
}
}
else
{
clear();
lblerror.Text = "No Records Found";
lblerror.Visible = true;
}
string totalrows = FpSpread2.Sheets[0].RowCount.ToString();
FpSpread2.Sheets[0].PageSize = (Convert.ToInt32(totalrows) * 20) + 40;
FpSpread2.Height = (Convert.ToInt32(totalrows) * 50) + 40;
FpSpread2.SaveChanges();
}
catch (Exception ex)
{
lblerror.Text = ex.ToString();
lblerror.Visible = true;
}
}
public void newsloadspread(DataSet dsnews)
{
try
{
FpSpread1.SaveChanges();
if (dsnews.Tables.Count > 0 && dsnews.Tables[0].Rows.Count > 0)
{
FpSpread1.Sheets[0].RowCount = 1;
FpSpread1.CommandBar.Visible = false;
FpSpread1.Sheets[0].AutoPostBack = false;
FpSpread1.Sheets[0].ColumnHeader.RowCount = 1;
FpSpread1.Sheets[0].ColumnHeader.Columns.Count = 3;
FpSpread1.Sheets[0].RowHeader.Visible = false;
FarPoint.Web.Spread.StyleInfo darkstyle = new FarPoint.Web.Spread.StyleInfo();
darkstyle.BackColor = ColorTranslator.FromHtml("#0CA6CA");
darkstyle.ForeColor = Color.Black;
FpSpread1.ActiveSheetView.ColumnHeader.DefaultStyle = darkstyle;
FpSpread1.Sheets[0].ColumnHeader.Cells[0, 0].Text = "S.No";
FpSpread1.Sheets[0].ColumnHeader.Cells[0, 0].Font.Bold = true;
FpSpread1.Sheets[0].ColumnHeader.Cells[0, 0].Font.Name = "Book Antiqua";
FpSpread1.Sheets[0].ColumnHeader.Cells[0, 0].HorizontalAlign = HorizontalAlign.Center;
FpSpread1.Sheets[0].ColumnHeader.Cells[0, 0].Font.Size = FontUnit.Medium;
FpSpread1.Sheets[0].Columns[0].Locked = true;
FpSpread1.Columns[0].Width = 50;
FpSpread1.Sheets[0].ColumnHeader.Cells[0, 1].Text = "Journal Name";
FpSpread1.Sheets[0].ColumnHeader.Cells[0, 1].Font.Bold = true;
FpSpread1.Sheets[0].ColumnHeader.Cells[0, 1].Font.Name = "Book Antiqua";
FpSpread1.Sheets[0].ColumnHeader.Cells[0, 1].Font.Size = FontUnit.Medium;
FpSpread1.Sheets[0].ColumnHeader.Cells[0, 1].HorizontalAlign = HorizontalAlign.Center;
FpSpread1.Columns[1].Width = 150;
FpSpread1.Columns[1].Visible = true;
FpSpread1.Sheets[0].ColumnHeader.Cells[0, 2].Text = "Journal Code";
FpSpread1.Sheets[0].ColumnHeader.Cells[0, 2].Font.Bold = true;
FpSpread1.Sheets[0].ColumnHeader.Cells[0, 2].Font.Name = "Book Antiqua";
FpSpread1.Sheets[0].ColumnHeader.Cells[0, 2].Font.Size = FontUnit.Medium;
FpSpread1.Sheets[0].ColumnHeader.Cells[0, 2].HorizontalAlign = HorizontalAlign.Center;
FpSpread1.Columns[2].Width = 150;
FpSpread1.Columns[2].Visible = false;
FarPoint.Web.Spread.TextCellType txtCell = new FarPoint.Web.Spread.TextCellType();
FarPoint.Web.Spread.CheckBoxCellType chkcell1 = new FarPoint.Web.Spread.CheckBoxCellType();
chkcell1.AutoPostBack = true;
FarPoint.Web.Spread.CheckBoxCellType chkcell2 = new FarPoint.Web.Spread.CheckBoxCellType();
chkcell2.AutoPostBack = false;
int sno = 0;
DateTime dateFromDate = new DateTime();
DateTime dateToDate = new DateTime();
dateFromDate = getdate(txt_fromdate.Text.ToString());
dateToDate = getdate(txt_todate.Text.ToString());
string coldate = "";
int col = 0;
while (dateFromDate <= dateToDate)
{
coldate = dateFromDate.ToString("dd/MM/yyyy");
col = FpSpread1.Sheets[0].ColumnHeader.Columns.Count++;
FpSpread1.Sheets[0].ColumnHeader.Cells[0, col].Text = coldate;
FpSpread1.Sheets[0].ColumnHeader.Cells[0, col].HorizontalAlign = HorizontalAlign.Center;
FpSpread1.Columns[col].Width = 90;
FpSpread1.Columns[col].Visible = true;
FpSpread1.Sheets[0].Cells[0, col].CellType = chkcell1;
FpSpread1.Sheets[0].Cells[0, col].HorizontalAlign = HorizontalAlign.Center;
dateFromDate = dateFromDate.AddDays(1);
col++;
}
int nojoural = dsnews.Tables[0].Rows.Count;
for (int row = 0; row < dsnews.Tables[0].Rows.Count; row++)
{
FpSpread1.Sheets[0].RowCount++;
sno++;
dateFromDate = getdate(txt_fromdate.Text.ToString());
dateToDate = getdate(txt_todate.Text.ToString());
string lan = Convert.ToString(dsnews.Tables[0].Rows[row]["TitleLanguage"]).Trim();
string jname = Convert.ToString(dsnews.Tables[0].Rows[row]["Journal_Name"]).Trim();
string jcode = Convert.ToString(dsnews.Tables[0].Rows[row]["Journal_Code"]).Trim();
string yr = Convert.ToString(dsnews.Tables[0].Rows[row]["Subs_Year"]);
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 0].CellType = txtCell;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 1].CellType = txtCell;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 0].Text = Convert.ToString(sno);
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 0].Tag = jcode;
if (lan == "1")
{
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 1].Text = jname;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 1].Font.Name = "Amudham";
}
else
{
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 1].Text = jname;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 1].Font.Name = "Arial";
}
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 2].Tag = jname;
col = 3;
while (dateFromDate <= dateToDate)
{
coldate = dateFromDate.ToString("yyyy/MM/dd");
string Sqlqry = d2.GetFunction("SELECT Issue_Status FROM Journal_Issues WHERE Journal_Code ='" + jcode + "' AND Subs_Year='" + yr + "' AND IssueDate ='" + coldate + "'");
if (Sqlqry == "1")
{
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, col].Tag = Sqlqry;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 1].Tag = coldate;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, col].CellType = chkcell2;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, col].Value = 1;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, col].BackColor = Color.Green;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, col].HorizontalAlign = HorizontalAlign.Center;
}
else
{
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, col].Tag = Sqlqry;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 1].Tag = coldate;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, col].CellType = chkcell2;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, col].Value = 0;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, col].BackColor = Color.Red;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, col].HorizontalAlign = HorizontalAlign.Center;
}
dateFromDate = dateFromDate.AddDays(1);
col++;
}
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 0].HorizontalAlign = HorizontalAlign.Center;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 1].HorizontalAlign = HorizontalAlign.Left;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 0].Locked = true;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 1].Locked = true;
}
FpSpread1.Sheets[0].PageSize = 100;
FpSpread1.Sheets[0].CurrentPageIndex = 0;
FpSpread1.SaveChanges();
//FpSpread1.Sheets[0].PageSize = FpSpread1.Sheets[0].RowCount;
FpSpread1.Width = 1000;
FpSpread1.Height = 400;
FpSpread1.Visible = true;
btn_Save.Visible = true;
lbl_total_journal.Visible = true;
lbl_total_journal.Text = "No. Of Journals:" + nojoural;
}
}
catch (Exception ex) { d2.sendErrorMail(ex, userCollegeCode, "News_Paper_Entry"); }
}
private void loadspreaddetails(DataSet ds)
{
try
{
string gym_Name = string.Empty;
string Year = string.Empty;
string GymPK = string.Empty;
string Semester = string.Empty;
string Term = string.Empty;
string Month = string.Empty;
DataView dvgym = new DataView();
DataSet dsgymcost = new DataSet();
loadspreadHeader(ds);
FarPoint.Web.Spread.TextCellType txtCell = new FarPoint.Web.Spread.TextCellType();
int sno = 0;
for (int row = 0; row < ds.Tables[0].Rows.Count; row++)
{
spreadDet2.Sheets[0].RowCount++;
sno++;
GymPK = Convert.ToString(ds.Tables[0].Rows[row]["GymPK"]).Trim();
gym_Name = Convert.ToString(ds.Tables[0].Rows[row]["GymName"]).Trim();
spreadDet2.Sheets[0].Cells[spreadDet2.Sheets[0].RowCount - 1, 0].CellType = txtCell;
spreadDet2.Sheets[0].Cells[spreadDet2.Sheets[0].RowCount - 1, 1].CellType = txtCell;
spreadDet2.Sheets[0].Cells[spreadDet2.Sheets[0].RowCount - 1, 2].CellType = txtCell;
spreadDet2.Sheets[0].Cells[spreadDet2.Sheets[0].RowCount - 1, 3].CellType = txtCell;
spreadDet2.Sheets[0].Cells[spreadDet2.Sheets[0].RowCount - 1, 4].CellType = txtCell;
spreadDet2.Sheets[0].Cells[spreadDet2.Sheets[0].RowCount - 1, 5].CellType = txtCell;
spreadDet2.Sheets[0].Cells[spreadDet2.Sheets[0].RowCount - 1, 6].CellType = txtCell;
spreadDet2.Sheets[0].Cells[spreadDet2.Sheets[0].RowCount - 1, 0].Text = Convert.ToString(sno);
spreadDet2.Sheets[0].Cells[spreadDet2.Sheets[0].RowCount - 1, 1].Text = Convert.ToString(GymPK);
spreadDet2.Sheets[0].Cells[spreadDet2.Sheets[0].RowCount - 1, 2].Text = gym_Name;
if (ddl_pattern.SelectedIndex == 1)
{
if (GymPK != "")
{
string selQ1 = "select Year from HM_GymCostMaster gc where GymFK='" + GymPK + "'";
dsgymcost.Clear();
dsgymcost = d2.select_method_wo_parameter(selQ1, "Text");
if (dsgymcost.Tables.Count > 0 && dsgymcost.Tables[0].Rows.Count > 0)
{
year = Convert.ToString(dsgymcost.Tables[0].Rows[0]["Year"]).Trim();
spreadDet2.Sheets[0].Cells[spreadDet2.Sheets[0].RowCount - 1, 3].Text = year;
}
}
}
if (ddl_pattern.SelectedIndex == 0)
{
if (GymPK != "")
{
string selQ1 = "select Month from HM_GymCostMaster gc where GymFK='" + GymPK + "'";
dsgymcost.Clear();
dsgymcost = d2.select_method_wo_parameter(selQ1, "Text");
if (dsgymcost.Tables.Count > 0 && dsgymcost.Tables[0].Rows.Count > 0)
{
Month = Convert.ToString(dsgymcost.Tables[0].Rows[0]["Month"]).Trim();
spreadDet2.Sheets[0].Cells[spreadDet2.Sheets[0].RowCount - 1, 4].Text = Month;
}
}
}
if (ddl_pattern.SelectedIndex == 2)
{
if (GymPK != "")
{
string selQ1 = "select Term from HM_GymCostMaster gc where GymFK='" + GymPK + "'";
dsgymcost.Clear();
dsgymcost = d2.select_method_wo_parameter(selQ1, "Text");
if (dsgymcost.Tables.Count > 0 && dsgymcost.Tables[0].Rows.Count > 0)
{
term = Convert.ToString(dsgymcost.Tables[0].Rows[0]["Term"]).Trim();
spreadDet2.Sheets[0].Cells[spreadDet2.Sheets[0].RowCount - 1, 5].Text = term;
}
}
}
if (ddl_pattern.SelectedIndex == 3)
{
if (GymPK != "")
{
string selQ1 = "select Semester from HM_GymCostMaster gc where GymFK='" + GymPK + "'";
dsgymcost.Clear();
dsgymcost = d2.select_method_wo_parameter(selQ1, "Text");
if (dsgymcost.Tables.Count > 0 && dsgymcost.Tables[0].Rows.Count > 0)
{
semester = Convert.ToString(dsgymcost.Tables[0].Rows[0]["Semester"]).Trim();
spreadDet2.Sheets[0].Cells[spreadDet2.Sheets[0].RowCount - 1, 6].Text = semester;
}
}
}
//spreadDet2.Sheets[0].Cells[spreadDet2.Sheets[0].RowCount - 1, 2].Text = year;
//spreadDet2.Sheets[0].Cells[spreadDet2.Sheets[0].RowCount - 1, 3].Text = semester;
//spreadDet2.Sheets[0].Cells[spreadDet2.Sheets[0].RowCount - 1, 4].Text = term;
//spreadDet2.Sheets[0].Cells[spreadDet2.Sheets[0].RowCount - 1, 5].Text = Month;
spreadDet2.Sheets[0].Cells[spreadDet2.Sheets[0].RowCount - 1, 0].HorizontalAlign = HorizontalAlign.Center;
spreadDet2.Sheets[0].Cells[spreadDet2.Sheets[0].RowCount - 1, 1].HorizontalAlign = HorizontalAlign.Center;
spreadDet2.Sheets[0].Cells[spreadDet2.Sheets[0].RowCount - 1, 2].HorizontalAlign = HorizontalAlign.Left;
spreadDet2.Sheets[0].Cells[spreadDet2.Sheets[0].RowCount - 1, 3].HorizontalAlign = HorizontalAlign.Left;
spreadDet2.Sheets[0].Cells[spreadDet2.Sheets[0].RowCount - 1, 4].HorizontalAlign = HorizontalAlign.Left;
spreadDet2.Sheets[0].Cells[spreadDet2.Sheets[0].RowCount - 1, 5].HorizontalAlign = HorizontalAlign.Left;
spreadDet2.Sheets[0].Cells[spreadDet2.Sheets[0].RowCount - 1, 6].HorizontalAlign = HorizontalAlign.Left;
spreadDet2.Sheets[0].Cells[spreadDet2.Sheets[0].RowCount - 1, 0].VerticalAlign = VerticalAlign.Middle;
spreadDet2.Sheets[0].Cells[spreadDet2.Sheets[0].RowCount - 1, 1].VerticalAlign = VerticalAlign.Middle;
spreadDet2.Sheets[0].Cells[spreadDet2.Sheets[0].RowCount - 1, 2].VerticalAlign = VerticalAlign.Middle;
spreadDet2.Sheets[0].Cells[spreadDet2.Sheets[0].RowCount - 1, 3].VerticalAlign = VerticalAlign.Middle;
spreadDet2.Sheets[0].Cells[spreadDet2.Sheets[0].RowCount - 1, 4].VerticalAlign = VerticalAlign.Middle;
spreadDet2.Sheets[0].Cells[spreadDet2.Sheets[0].RowCount - 1, 5].VerticalAlign = VerticalAlign.Middle;
spreadDet2.Sheets[0].Cells[spreadDet2.Sheets[0].RowCount - 1, 6].VerticalAlign = VerticalAlign.Middle;
spreadDet2.Sheets[0].Cells[spreadDet2.Sheets[0].RowCount - 1, 0].Locked = true;
spreadDet2.Sheets[0].Cells[spreadDet2.Sheets[0].RowCount - 1, 1].Locked = true;
spreadDet2.Sheets[0].Cells[spreadDet2.Sheets[0].RowCount - 1, 2].Locked = true;
spreadDet2.Sheets[0].Cells[spreadDet2.Sheets[0].RowCount - 1, 3].Locked = false;
spreadDet2.Sheets[0].Cells[spreadDet2.Sheets[0].RowCount - 1, 4].Locked = false;
spreadDet2.Sheets[0].Cells[spreadDet2.Sheets[0].RowCount - 1, 5].Locked = false;
spreadDet2.Sheets[0].Cells[spreadDet2.Sheets[0].RowCount - 1, 6].Locked = false;
}
spreadDet2.Sheets[0].Columns[0].Width = 128;
spreadDet2.Sheets[0].Columns[1].Width = 385;
spreadDet2.Sheets[0].Columns[2].Width = 265;
spreadDet2.Sheets[0].Columns[3].Width = 385;
spreadDet2.Sheets[0].Columns[4].Width = 382;
spreadDet2.Sheets[0].Columns[5].Width = 382;
spreadDet2.Sheets[0].Columns[6].Width = 382;
spreadDet2.Sheets[0].Columns[1].Visible = false;
spreadDet2.Height = 390;
spreadDet2.Width = 800;
spreadDet2.Sheets[0].PageSize = spreadDet2.Sheets[0].RowCount;
spreadDet2.SaveChanges();
showreport2.Visible = true;
btn_save.Visible = true;
print2.Visible = true;
}
catch
{
}
}
protected void FSpread1_SelectedIndexChanged(Object sender, EventArgs e)
{
try
{
if (Cellclick == true)
{
lbl_errmsg.Visible = false;
lblerr.Visible = false;
Labelheader.Visible = true;
lbl_rpt.Visible = true;
txt_rpt.Visible = true;
spreadpdf2.Visible = true;
spreadexcel2.Visible = true;
Panelshow.Height = 500;
modelpopsetting.Show();
FpSpread1.Visible = true;
Panelshow.Visible = true;
spreadexcel1.Visible = true;
spreadpdf1.Visible = true;
FpSpread2.Visible = true;
lbl_routename.Visible = true;
lbl_routeid.Visible = true;
string query = "";
string activerow = "";
string activecol = "";
DataSet dcal = new DataSet();
FarPoint.Web.Spread.TextCellType txtcell = new FarPoint.Web.Spread.TextCellType();
FarPoint.Web.Spread.DoubleCellType speed1 = new FarPoint.Web.Spread.DoubleCellType();
FarPoint.Web.Spread.StyleInfo style2 = new FarPoint.Web.Spread.StyleInfo();
style2.Font.Size = 13;
style2.Font.Name = "Book Antiqua";
style2.Font.Bold = true;
style2.HorizontalAlign = HorizontalAlign.Center;
style2.ForeColor = Color.Purple;
style2.BackColor = ColorTranslator.FromHtml("#58D3F7");
FpSpread2.Sheets[0].ColumnCount = 2;
FpSpread2.Sheets[0].SheetCornerStyle = new FarPoint.Web.Spread.StyleInfo(style2);
FpSpread2.Sheets[0].ColumnHeader.DefaultStyle = new FarPoint.Web.Spread.StyleInfo(style2);
FpSpread2.Sheets[0].AllowTableCorner = true;
FpSpread2.Sheets[0].SheetCornerStyle = new FarPoint.Web.Spread.StyleInfo(style2);
FpSpread2.Sheets[0].AutoPostBack = true;
FpSpread2.Sheets[0].RowCount = 0;
activerow = FpSpread1.ActiveSheetView.ActiveRow.ToString();
activecol = FpSpread1.ActiveSheetView.ActiveColumn.ToString();
string vechicle = FpSpread1.Sheets[0].Cells[Convert.ToInt32(activerow), 1].Text;
string route = FpSpread1.Sheets[0].Cells[Convert.ToInt32(activerow), 2].Text;
string stage = FpSpread1.Sheets[0].Cells[Convert.ToInt32(activerow), 4].Text;
string course = FpSpread1.Sheets[0].Cells[Convert.ToInt32(activerow), 3].Text;
FpSpread2.Sheets[0].ColumnHeader.Cells[0, 0].Text = "BOARDING POINT";
FpSpread2.Sheets[0].ColumnHeader.Cells[0, 0].Border.BorderColor = Color.Black;
FpSpread2.Sheets[0].Columns[0].Width = 200;
FpSpread2.Sheets[0].ColumnHeader.Cells[0, 1].Text = "TIME ";
FpSpread2.Sheets[0].ColumnHeader.Cells[0, 1].Border.BorderColor = Color.Black;
FpSpread2.Sheets[0].Columns[1].Width = 100;
FpSpread2.Sheets[0].Columns[0].Locked = true;
FpSpread2.Sheets[0].Columns[1].Locked = true;
string currenttime = Convert.ToString(DateTime.Now);
string[] split = currenttime.Split(); // date time am/pm
string timenow = split[2].ToString();
string curtime = split[1].ToString();
lbl_routeid.Text = " " + route.ToString() + " : ";
lbl_routename.Text = course.ToString();
FpSpread2.Sheets[0].RowCount++;
FpSpread2.Sheets[0].Cells[0, 0].Text = "MORNING TIME";
FpSpread2.Sheets[0].SpanModel.Add(0, 0, 1, 2);
FpSpread2.Sheets[0].Cells[0, 0].ForeColor = Color.Brown;
FpSpread2.Sheets[0].Cells[0, 0].Font.Bold = true;
FpSpread2.Sheets[0].Cells[0, 0].Font.Size = FontUnit.Medium;
FpSpread2.Sheets[0].Cells[0, 0].Font.Name = "Book Antiqua";
FpSpread2.Sheets[0].Cells[0, 0].HorizontalAlign = HorizontalAlign.Center;
FpSpread2.Sheets[0].Cells[0, 0].BackColor = ColorTranslator.FromHtml("#CEECF5");
query = "select r.Route_ID,r.Mdate,r.Stage_Name as stage,Arr_Time,Dep_Time,sess,Veh_ID,s.Stage_id,s.Stage_Name,s.Address from RouteMaster r,Stage_Master s where str(r.Stage_Name)=str(s.Stage_id) and sess='M' and Route_ID in('" + route + "') order by convert(nvarchar(5),Dep_Time,103) asc ";
dcal = da.select_method_wo_parameter(query, "text");
for (int temp = 0; temp < dcal.Tables[0].Rows.Count; temp++)
{
string stastage = dcal.Tables[0].Rows[temp]["Stage_Name"].ToString();
string starttime = dcal.Tables[0].Rows[temp]["Arr_Time"].ToString();
if (starttime == "Halt" || starttime == "-")
{
starttime = dcal.Tables[0].Rows[temp]["Dep_Time"].ToString();
}
FpSpread2.Sheets[0].RowCount++;
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 0].Text = stastage.ToString();
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 0].HorizontalAlign = HorizontalAlign.Left;
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 0].BackColor = ColorTranslator.FromHtml("#CEECF5");
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 1].CellType = txtcell;
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 1].Text = starttime.ToString();
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 1].HorizontalAlign = HorizontalAlign.Center;
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 1].BackColor = ColorTranslator.FromHtml("#CEECF5");
}
FpSpread2.Sheets[0].RowCount++;
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 0].Text = "EVENING TIME";
FpSpread2.Sheets[0].SpanModel.Add(FpSpread2.Sheets[0].RowCount - 1, 0, 1, 2);
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 0].Font.Bold = true;
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 0].Font.Size = FontUnit.Medium;
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 0].Font.Name = "Book Antiqua";
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 0].BackColor = ColorTranslator.FromHtml("#CEECF5");
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 0].ForeColor = Color.Brown;
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 0].HorizontalAlign = HorizontalAlign.Center;
query = "select r.Route_ID,r.Mdate,r.Stage_Name as stage,Arr_Time,Dep_Time,sess,Veh_ID,s.Stage_id,s.Stage_Name,s.Address from RouteMaster r,Stage_Master s where str(r.Stage_Name)=str(s.Stage_id) and sess='A' and Route_ID in('" + route + "') order by convert(nvarchar(5),Dep_Time,103) asc ";
dcal = da.select_method_wo_parameter(query, "text");
for (int temp = 0; temp < dcal.Tables[0].Rows.Count; temp++)
{
string stastage = dcal.Tables[0].Rows[temp]["Stage_Name"].ToString();
string starttime1 = dcal.Tables[0].Rows[temp]["Arr_Time"].ToString();
if (starttime1 == "-" || starttime1 == "Halt")
{
starttime1 = dcal.Tables[0].Rows[temp]["Dep_Time"].ToString();
}
FpSpread2.Sheets[0].RowCount++;
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 0].Text = stastage.ToString();
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 0].HorizontalAlign = HorizontalAlign.Left;
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 0].BackColor = ColorTranslator.FromHtml("#CEECF5");
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 1].CellType = txtcell;
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 1].Text = starttime1.ToString();
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 1].HorizontalAlign = HorizontalAlign.Center;
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 1].BackColor = ColorTranslator.FromHtml("#CEECF5");
}
FpSpread2.Sheets[0].SetColumnMerge(1, FarPoint.Web.Spread.Model.MergePolicy.Always);
FpSpread2.Sheets[0].Columns[0].VerticalAlign = VerticalAlign.Middle;
FpSpread2.Sheets[0].Columns[1].VerticalAlign = VerticalAlign.Middle;
modelpopsetting.Show();
Panelshow.Visible = true;
FpSpread2.Visible = true;
FpSpread1.Visible = true;
FpSpread2.Sheets[0].PageSize = FpSpread2.Sheets[0].RowCount;
FpSpread2.Width = 500;
FpSpread2.Height = 400;
}
else
{
}
}
catch (Exception ex)
{
IblError.Visible = true;
IblError.Text = ex.ToString();
}
}
protected void Buttongo_Click(object sender, EventArgs e)
{
try
{
lblvalidation1.Text = "";
ss2 = "";
p = "";
FpSpread2.Visible = false; btnreset.Visible = false;
FpSpread2.CommandBar.Visible = false;
FpSpread2.Sheets[0].RowCount = 0;
FpSpread2.Sheets[0].ColumnCount = 5;
FpSpread2.Sheets[0].Columns[0].Width = 40;
FpSpread2.Sheets[0].Columns[1].Width = 113;
FpSpread2.Sheets[0].Columns[2].Width = 342;
FpSpread2.Sheets[0].Columns[3].Width = 83;
FpSpread2.Sheets[0].Columns[4].Width = 90;
FpSpread2.Sheets[0].RowHeader.Visible = false;
FpSpread2.Sheets[0].DefaultStyle.Font.Size = FontUnit.Medium;
FpSpread2.Sheets[0].ColumnHeader.DefaultStyle.Font.Size = FontUnit.Medium;
FpSpread2.Sheets[0].ColumnHeader.DefaultStyle.Font.Name = "Book Antiqua";
FpSpread2.Sheets[0].ColumnHeader.DefaultStyle.Font.Bold = true;
FpSpread2.Sheets[0].DefaultStyle.Font.Size = FontUnit.Medium;
FpSpread2.Sheets[0].DefaultStyle.Font.Name = "Book Antiqua";
FpSpread2.Sheets[0].ColumnHeader.Cells[0, 0].Text = "S.No";
FpSpread2.Sheets[0].ColumnHeader.Cells[0, 1].Text = "Reg.No.";
FpSpread2.Sheets[0].ColumnHeader.Cells[0, 2].Text = "Student Name";
FpSpread2.Sheets[0].ColumnHeader.Cells[0, 3].Text = "Total Papers Selected";
FpSpread2.Sheets[0].ColumnHeader.Cells[0, 4].Text = "Total Papers Appeared";
FpSpread2.Sheets[0].Columns[0].Locked = true;
FpSpread2.Sheets[0].Columns[1].Locked = true;
FpSpread2.Sheets[0].Columns[2].Locked = true;
FpSpread2.Sheets[0].Columns[4].Locked = true;
FpSpread2.Sheets[0].Columns[0].VerticalAlign = VerticalAlign.Middle;
FpSpread2.Sheets[0].Columns[1].VerticalAlign = VerticalAlign.Middle;
FpSpread2.Sheets[0].Columns[2].VerticalAlign = VerticalAlign.Middle;
FpSpread2.Sheets[0].Columns[3].VerticalAlign = VerticalAlign.Middle;
FpSpread2.Sheets[0].Columns[4].VerticalAlign = VerticalAlign.Middle;
FpSpread2.Sheets[0].Columns[0].HorizontalAlign = HorizontalAlign.Center;
FpSpread2.Sheets[0].Columns[1].HorizontalAlign = HorizontalAlign.Left;
FpSpread2.Sheets[0].Columns[2].HorizontalAlign = HorizontalAlign.Left;
FpSpread2.Sheets[0].Columns[3].HorizontalAlign = HorizontalAlign.Center;
FpSpread2.Sheets[0].Columns[4].HorizontalAlign = HorizontalAlign.Center;
loadheader();
FarPoint.Web.Spread.TextCellType txt = new FarPoint.Web.Spread.TextCellType();
FarPoint.Web.Spread.CheckBoxCellType chkcell = new FarPoint.Web.Spread.CheckBoxCellType();
FpSpread2.Sheets[0].RowCount = 0;
FarPoint.Web.Spread.CheckBoxCellType chkcell1 = new FarPoint.Web.Spread.CheckBoxCellType();
chkcell1.AutoPostBack = true;
chkcell.AutoPostBack = true;
FpSpread2.Sheets[0].AutoPostBack = true;
DataSet printds = new DataSet();
string batchyear = ddlbatch.SelectedValue.ToString();
string degreecode = ddlbranch.SelectedValue.ToString();
string year = ddlbatch.SelectedValue.ToString();
string degree = ddldegree.SelectedItem.ToString();
string course = ddldegree.SelectedItem.ToString();
string depart_code = ddlbranch.SelectedValue.ToString();
string batchyearatt = ddlbatch.SelectedValue.ToString();
string studinfo = "";
studinfo = "select Stud_Name,Roll_No,Reg_No,APP_No from Registration where Batch_Year='" + ddlbatch.SelectedItem.Text.ToString() + "' and degree_code='" + depart_code + "' and DelFlag=0 and Exam_Flag<>'DEBAR' order by Reg_No ; select m.roll_no,ed.Exam_year,ed.Exam_Month,m.subject_no,m.internal_mark,m.external_mark,m.total,m.result from mark_entry m,Exam_Details ed where ed.exam_code=m.exam_code and ed.batch_year='" + ddlbatch.SelectedItem.Text.ToString() + "' and ed.degree_code='" + depart_code + "' order by m.roll_no,m.subject_no,ed.Exam_year,ed.Exam_Month";
studinfo = studinfo + " select r.Roll_No,Count(distinct sc.subject_no) subcount from Registration r,subjectChooser sc where r.Roll_No=sc.roll_no and r.Batch_Year='" + ddlbatch.SelectedItem.Text.ToString() + "' and r.degree_code='" + depart_code + "' and DelFlag=0 and Exam_Flag<>'DEBAR' group by r.Roll_No";
studinfo = studinfo + " select r.Roll_No,Count(distinct m.subject_no) subcount from Registration r,mark_entry m,subjectChooser sc where r.Roll_No=m.roll_no and r.Roll_No=sc.roll_no and sc.subject_no=m.subject_no and sc.roll_no=m.roll_no and r.Batch_Year='" + ddlbatch.SelectedItem.Text.ToString() + "' and r.degree_code='" + depart_code + "' and DelFlag=0 and Exam_Flag<>'DEBAR' group by r.Roll_No";
dsstudinfo = da.select_method_wo_parameter(studinfo, "Text");
dv = new DataView();
if (dsstudinfo.Tables[0].Rows.Count > 0)
{
btnsave.Visible = false;
FpSpread2.Visible = true;
btnPrint.Visible = true;
int sno = 0;
for (int studcount = 0; studcount < dsstudinfo.Tables[0].Rows.Count; studcount++)
{
sno++;
string roll_no = dsstudinfo.Tables[0].Rows[studcount]["Roll_No"].ToString();
FpSpread2.Sheets[0].RowCount++;
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 0].Text = sno + "";
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 1].CellType = txt;
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 1].Text = dsstudinfo.Tables[0].Rows[studcount]["Reg_No"].ToString();
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 1].Note = dsstudinfo.Tables[0].Rows[studcount]["Roll_No"].ToString();
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 2].CellType = txt;
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 2].Text = dsstudinfo.Tables[0].Rows[studcount]["Stud_Name"].ToString();
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 2].Tag = dsstudinfo.Tables[0].Rows[studcount]["APP_No"].ToString();
int noofsubject = 0;
dsstudinfo.Tables[2].DefaultView.RowFilter = "roll_no='" + roll_no + "'";
dv = dsstudinfo.Tables[2].DefaultView;
if (dv.Count > 0)
{
noofsubject = Convert.ToInt32(dv[0]["subcount"].ToString());
}
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 3].Text = noofsubject.ToString();
noofsubject = 0;
dsstudinfo.Tables[3].DefaultView.RowFilter = "roll_no='" + roll_no + "'";
dv = dsstudinfo.Tables[3].DefaultView;
if (dv.Count > 0)
{
noofsubject = Convert.ToInt32(dv[0]["subcount"].ToString());
}
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 4].Text = noofsubject.ToString();
int startrow = FpSpread2.Sheets[0].RowCount;
for (int i = 5; i < FpSpread2.Sheets[0].Columns.Count; i++)
{
string strm_y = "";
int noofrow = startrow;
string subject_no = FpSpread2.Sheets[0].ColumnHeader.Cells[0, i].Tag.ToString();
dsstudinfo.Tables[1].DefaultView.RowFilter = "roll_no='" + roll_no + "' and subject_no='" + subject_no + "'";
dv = dsstudinfo.Tables[1].DefaultView;
for (int k = 0; k < dv.Count; k++)
{
int e_month = Convert.ToInt32(dv[k]["Exam_Month"].ToString());
string e_year = dv[k]["Exam_year"].ToString();
string strMonthName = CultureInfo.CurrentCulture.DateTimeFormat.AbbreviatedMonthGenitiveNames[e_month - 1];
strMonthName = e_year + "-" + strMonthName;
string strintmark = dv[k]["internal_mark"].ToString();
if (strintmark.Trim() == "-1")
{
strintmark = "AB";
}
else if (strintmark.Trim() == "-2")
{
strintmark = "NE";
}
else if (strintmark.Trim() == "-3")
{
strintmark = "NR";
}
string strextmark = dv[k]["external_mark"].ToString();
if (strextmark.Trim() == "-1")
{
strextmark = "AB";
}
else if (strextmark.Trim() == "-2")
{
strextmark = "NE";
}
else if (strextmark.Trim() == "-3")
{
strextmark = "NR";
}
if (strm_y.Trim() == "")
{
strm_y = strMonthName + "-" + strintmark + "," + strextmark;
}
else
{
strm_y = strm_y + " " + strMonthName + "-" + strintmark + "," + strextmark;
noofrow++;
if (FpSpread2.Sheets[0].RowCount < noofrow)
{
FpSpread2.Sheets[0].RowCount++;
FpSpread2.Sheets[0].Rows[FpSpread2.Sheets[0].RowCount - 2].Border.BorderColorBottom = Color.White;
}
}
FpSpread2.Sheets[0].Cells[noofrow - 1, i].Text = strMonthName + "-" + strintmark + "," + strextmark;
}
}
}
dsstudinfo.Clear();
dsstudinfo.Dispose();
rptprint.Visible = true;
}
else
{
clear();
lblerror.Text = "No Records Found";
lblerror.Visible = true;
}
string totalrows = FpSpread2.Sheets[0].RowCount.ToString();
FpSpread2.Sheets[0].PageSize = (Convert.ToInt32(totalrows) * 20) + 40;
FpSpread2.Height = (Convert.ToInt32(totalrows) * 50) + 40;
FpSpread2.Sheets[0].Columns[0].Width = 40;
FpSpread2.Sheets[0].Columns[1].Width = 113;
FpSpread2.Sheets[0].Columns[2].Width = 342;
FpSpread2.Sheets[0].Columns[3].Width = 83;
FpSpread2.Sheets[0].Columns[4].Width = 90;
FpSpread2.Sheets[0].PageSize = FpSpread2.Sheets[0].RowCount;
FpSpread2.SaveChanges();
FpSpread2.Visible = true;
}
catch (Exception ex)
{
lblerror.Text = ex.ToString();
lblerror.Visible = true;
}
}
public void loadspread(DataSet ds)
{
try
{
DataView dv = new DataView();
spreadDet1.Sheets[0].RowCount = 0;
spreadDet1.Sheets[0].ColumnCount = 6;
spreadDet1.CommandBar.Visible = false;
spreadDet1.Sheets[0].AutoPostBack = false;
spreadDet1.Sheets[0].ColumnHeader.RowCount = 1;
spreadDet1.Sheets[0].RowHeader.Visible = false;
FarPoint.Web.Spread.StyleInfo darkstyle = new FarPoint.Web.Spread.StyleInfo();
darkstyle.BackColor = ColorTranslator.FromHtml("#0CA6CA");
darkstyle.ForeColor = Color.White;
spreadDet1.Sheets[0].ColumnHeader.Cells[0, 0].Text = "S.No";
spreadDet1.Sheets[0].ColumnHeader.Cells[0, 0].Font.Bold = true;
spreadDet1.Sheets[0].ColumnHeader.Cells[0, 0].Font.Name = "Book Antiqua";
spreadDet1.Sheets[0].ColumnHeader.Cells[0, 0].HorizontalAlign = HorizontalAlign.Center;
spreadDet1.Sheets[0].ColumnHeader.Cells[0, 0].Font.Size = FontUnit.Medium;
spreadDet1.Sheets[0].Columns[0].Locked = true;
spreadDet1.Columns[0].Width = 80;
spreadDet1.Sheets[0].ColumnHeader.Cells[0, 1].Text = "Store Name";
spreadDet1.Sheets[0].ColumnHeader.Cells[0, 1].Font.Bold = true;
spreadDet1.Sheets[0].ColumnHeader.Cells[0, 1].Font.Name = "Book Antiqua";
spreadDet1.Sheets[0].ColumnHeader.Cells[0, 1].Font.Size = FontUnit.Medium;
spreadDet1.Sheets[0].ColumnHeader.Cells[0, 1].HorizontalAlign = HorizontalAlign.Center;
spreadDet1.Sheets[0].Columns[1].Locked = true;
spreadDet1.Columns[1].Width = 150;
spreadDet1.Sheets[0].ColumnHeader.Cells[0, 2].Text = "Item Header Name";
spreadDet1.Sheets[0].ColumnHeader.Cells[0, 2].Font.Bold = true;
spreadDet1.Sheets[0].ColumnHeader.Cells[0, 2].Font.Name = "Book Antiqua";
spreadDet1.Sheets[0].ColumnHeader.Cells[0, 2].Font.Size = FontUnit.Medium;
spreadDet1.Sheets[0].ColumnHeader.Cells[0, 2].HorizontalAlign = HorizontalAlign.Center;
spreadDet1.Sheets[0].Columns[2].Locked = true;
spreadDet1.Columns[2].Width = 200;
spreadDet1.Sheets[0].ColumnHeader.Cells[0, 3].Text = "Item SubHeader Name";
spreadDet1.Sheets[0].ColumnHeader.Cells[0, 3].Font.Bold = true;
spreadDet1.Sheets[0].ColumnHeader.Cells[0, 3].Font.Name = "Book Antiqua";
spreadDet1.Sheets[0].ColumnHeader.Cells[0, 3].Font.Size = FontUnit.Medium;
spreadDet1.Sheets[0].ColumnHeader.Cells[0, 3].HorizontalAlign = HorizontalAlign.Center;
spreadDet1.Sheets[0].Columns[3].Locked = true;
spreadDet1.Columns[3].Width = 200;
spreadDet1.Sheets[0].ColumnHeader.Cells[0, 4].Text = "Item Name";
spreadDet1.Sheets[0].ColumnHeader.Cells[0, 4].Font.Bold = true;
spreadDet1.Sheets[0].ColumnHeader.Cells[0, 4].Font.Name = "Book Antiqua";
spreadDet1.Sheets[0].ColumnHeader.Cells[0, 4].Font.Size = FontUnit.Medium;
spreadDet1.Sheets[0].ColumnHeader.Cells[0, 4].HorizontalAlign = HorizontalAlign.Center;
spreadDet1.Columns[4].Width = 200;
spreadDet1.Sheets[0].ColumnHeader.Cells[0, 5].Text = "Select";
spreadDet1.Sheets[0].ColumnHeader.Cells[0, 5].Font.Bold = true;
spreadDet1.Sheets[0].ColumnHeader.Cells[0, 5].HorizontalAlign = HorizontalAlign.Center;
spreadDet1.Sheets[0].ColumnHeader.Cells[0, 5].Font.Name = "Book Antiqua";
spreadDet1.Sheets[0].ColumnHeader.Cells[0, 5].Font.Size = FontUnit.Medium;
spreadDet1.Columns[5].Width = 50;
FarPoint.Web.Spread.TextCellType txtCell = new FarPoint.Web.Spread.TextCellType();
FarPoint.Web.Spread.CheckBoxCellType chkall = new FarPoint.Web.Spread.CheckBoxCellType();
chkall.AutoPostBack = false;
int sno = 0;
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
for (int row = 0; row < ds.Tables[0].Rows.Count; row++)
{
spreadDet1.Sheets[0].RowCount++;
sno++;
string strname = Convert.ToString(ds.Tables[0].Rows[row]["StoreName"]).Trim();
string headname = Convert.ToString(ds.Tables[0].Rows[row]["ItemHeaderName"]).Trim();
string subheadname = Convert.ToString(ds.Tables[0].Rows[row]["ItemSubHeadername"]).Trim();
string itemname = Convert.ToString(ds.Tables[0].Rows[row]["ItemName"]).Trim();
string itemcode = Convert.ToString(ds.Tables[0].Rows[row]["ItemCode"]).Trim();
spreadDet1.Sheets[0].Cells[spreadDet1.Sheets[0].RowCount - 1, 0].CellType = txtCell;
spreadDet1.Sheets[0].Cells[spreadDet1.Sheets[0].RowCount - 1, 1].CellType = txtCell;
spreadDet1.Sheets[0].Cells[spreadDet1.Sheets[0].RowCount - 1, 2].CellType = txtCell;
spreadDet1.Sheets[0].Cells[spreadDet1.Sheets[0].RowCount - 1, 3].CellType = txtCell;
spreadDet1.Sheets[0].Cells[spreadDet1.Sheets[0].RowCount - 1, 4].CellType = txtCell;
spreadDet1.Sheets[0].Cells[spreadDet1.Sheets[0].RowCount - 1, 5].CellType = chkall;
ds.Tables[1].DefaultView.RowFilter = "ItemCode ='" + Convert.ToString(ds.Tables[0].Rows[row]["ItemCode"]) + "'";
dv = ds.Tables[1].DefaultView;
if (dv.Count > 0)
{
spreadDet1.Sheets[0].Cells[row, 5].Value = 1;
}
else
{
spreadDet1.Sheets[0].Cells[row, 5].Value = 0;
}
spreadDet1.Sheets[0].Cells[spreadDet1.Sheets[0].RowCount - 1, 0].Text = Convert.ToString(sno);
spreadDet1.Sheets[0].Cells[spreadDet1.Sheets[0].RowCount - 1, 1].Text = strname;
spreadDet1.Sheets[0].Cells[spreadDet1.Sheets[0].RowCount - 1, 2].Text = headname;
spreadDet1.Sheets[0].Cells[spreadDet1.Sheets[0].RowCount - 1, 3].Text = subheadname;
spreadDet1.Sheets[0].Cells[spreadDet1.Sheets[0].RowCount - 1, 3].Tag = itemcode;
spreadDet1.Sheets[0].Cells[spreadDet1.Sheets[0].RowCount - 1, 4].Text = itemname;
spreadDet1.Sheets[0].Cells[spreadDet1.Sheets[0].RowCount - 1, 4].Tag = itemname;
spreadDet1.Sheets[0].Cells[spreadDet1.Sheets[0].RowCount - 1, 0].HorizontalAlign = HorizontalAlign.Center;
spreadDet1.Sheets[0].Cells[spreadDet1.Sheets[0].RowCount - 1, 1].HorizontalAlign = HorizontalAlign.Left;
spreadDet1.Sheets[0].Cells[spreadDet1.Sheets[0].RowCount - 1, 2].HorizontalAlign = HorizontalAlign.Left;
spreadDet1.Sheets[0].Cells[spreadDet1.Sheets[0].RowCount - 1, 3].HorizontalAlign = HorizontalAlign.Left;
spreadDet1.Sheets[0].Cells[spreadDet1.Sheets[0].RowCount - 1, 4].HorizontalAlign = HorizontalAlign.Left;
spreadDet1.Sheets[0].Cells[spreadDet1.Sheets[0].RowCount - 1, 5].HorizontalAlign = HorizontalAlign.Center;
spreadDet1.Sheets[0].Cells[spreadDet1.Sheets[0].RowCount - 1, 0].VerticalAlign = VerticalAlign.Middle;
spreadDet1.Sheets[0].Cells[spreadDet1.Sheets[0].RowCount - 1, 1].VerticalAlign = VerticalAlign.Middle;
spreadDet1.Sheets[0].Cells[spreadDet1.Sheets[0].RowCount - 1, 2].VerticalAlign = VerticalAlign.Middle;
spreadDet1.Sheets[0].Cells[spreadDet1.Sheets[0].RowCount - 1, 3].VerticalAlign = VerticalAlign.Middle;
spreadDet1.Sheets[0].Cells[spreadDet1.Sheets[0].RowCount - 1, 4].VerticalAlign = VerticalAlign.Middle;
spreadDet1.Sheets[0].Cells[spreadDet1.Sheets[0].RowCount - 1, 5].VerticalAlign = VerticalAlign.Middle;
spreadDet1.Sheets[0].Cells[spreadDet1.Sheets[0].RowCount - 1, 0].Locked = true;
spreadDet1.Sheets[0].Cells[spreadDet1.Sheets[0].RowCount - 1, 1].Locked = true;
spreadDet1.Sheets[0].Cells[spreadDet1.Sheets[0].RowCount - 1, 2].Locked = true;
spreadDet1.Sheets[0].Cells[spreadDet1.Sheets[0].RowCount - 1, 3].Locked = true;
spreadDet1.Sheets[0].Cells[spreadDet1.Sheets[0].RowCount - 1, 4].Locked = true;
//spreadDet1.Sheets[0].Cells[spreadDet1.Sheets[0].RowCount - 1, 3].ForeColor = Color.Blue;
//spreadDet1.Sheets[0].Cells[spreadDet1.Sheets[0].RowCount - 1, 3].Font.Underline = true;
//spreadDet1.Sheets[0].Cells[spreadDet1.Sheets[0].RowCount - 1, 4].ForeColor = Color.Blue;
//spreadDet1.Sheets[0].Cells[spreadDet1.Sheets[0].RowCount - 1, 4].Font.Underline = true;
spreadDet1.Sheets[0].SetColumnMerge(1, FarPoint.Web.Spread.Model.MergePolicy.Always);
spreadDet1.Sheets[0].SetColumnMerge(2, FarPoint.Web.Spread.Model.MergePolicy.Always);
spreadDet1.Sheets[0].SetColumnMerge(3, FarPoint.Web.Spread.Model.MergePolicy.Always);
}
//spreadDet1.Sheets[0].Columns[0].Width = 50;
//spreadDet1.Sheets[0].Columns[1].Width = 160;
//spreadDet1.Sheets[0].Columns[2].Width = 100;
//spreadDet1.Sheets[0].Columns[3].Width = 100;
//spreadDet1.Sheets[0].Columns[4].Width = 200;
spreadDet1.Sheets[0].PageSize = spreadDet1.Sheets[0].RowCount;
spreadDet1.SaveChanges();
ShowReport.Visible = true;
spreadDet1.Visible = true;
}
}
catch
{
}
}
protected void btngo_Click(object sender, EventArgs e)
{
try
{
clear();
fpsalary.CommandBar.Visible = false;
fpsalary.Sheets[0].RowCount = 0;
fpsalary.Sheets[0].ColumnCount = 0;
fpsalary.Sheets[0].SheetCorner.ColumnCount = 0;
fpsalary.Sheets[0].ColumnCount = 15;
fpsalary.Sheets[0].ColumnHeader.RowCount = 1;
fpsalary.Sheets[0].ColumnHeader.Rows[0].BackColor = Color.AliceBlue;
fpsalary.Sheets[0].SheetName = " ";
fpsalary.Sheets[0].Columns.Default.VerticalAlign = VerticalAlign.Middle;
fpsalary.Sheets[0].Rows.Default.HorizontalAlign = HorizontalAlign.Center;
fpsalary.Sheets[0].Rows.Default.VerticalAlign = VerticalAlign.Middle;
fpsalary.Sheets[0].DefaultStyle.Font.Name = "Book Antiqua";
fpsalary.Sheets[0].DefaultStyle.Font.Size = FontUnit.Medium;
fpsalary.Sheets[0].DefaultStyle.Font.Bold = false;
FarPoint.Web.Spread.StyleInfo style1 = new FarPoint.Web.Spread.StyleInfo();
style1.Font.Size = 12;
style1.Font.Bold = true;
style1.HorizontalAlign = HorizontalAlign.Center;
style1.ForeColor = Color.Black;
style1.BackColor = ColorTranslator.FromHtml("#0CA6CA");
fpsalary.Sheets[0].SheetCornerStyle = new FarPoint.Web.Spread.StyleInfo(style1);
fpsalary.Sheets[0].ColumnHeader.DefaultStyle = new FarPoint.Web.Spread.StyleInfo(style1);
fpsalary.Sheets[0].ColumnHeader.DefaultStyle.HorizontalAlign = HorizontalAlign.Center;
fpsalary.Sheets[0].AllowTableCorner = true;
fpsalary.Pager.Position = FarPoint.Web.Spread.PagerPosition.Bottom;
fpsalary.Pager.Mode = FarPoint.Web.Spread.PagerMode.Both;
fpsalary.Pager.Align = HorizontalAlign.Right;
fpsalary.Pager.Font.Bold = true;
fpsalary.Pager.Font.Name = "Book Antiqua";
fpsalary.Pager.ForeColor = Color.DarkGreen;
fpsalary.Pager.BackColor = Color.Beige;
fpsalary.Pager.BackColor = Color.AliceBlue;
fpsalary.Pager.PageCount = 5;
fpsalary.CommandBar.Visible = false;
fpsalary.Sheets[0].ColumnHeader.Cells[0, 0].Text = "S.No";
fpsalary.Sheets[0].ColumnHeader.Cells[0, 1].Text = "Staff Code";
fpsalary.Sheets[0].ColumnHeader.Cells[0, 2].Text = "Staff Name";
fpsalary.Sheets[0].ColumnHeader.Cells[0, 3].Text = "Department";
fpsalary.Sheets[0].ColumnHeader.Cells[0, 4].Text = "Designation";
fpsalary.Sheets[0].ColumnHeader.Cells[0, 5].Text = "Category";
fpsalary.Sheets[0].ColumnHeader.Cells[0, 6].Text = "Type";
fpsalary.Sheets[0].ColumnHeader.Cells[0, 7].Text = "Date Of Joining";
fpsalary.Sheets[0].ColumnHeader.Cells[0, 8].Text = "PF Join Date";
fpsalary.Sheets[0].ColumnHeader.Cells[0, 9].Text = "Date Of Relive";
fpsalary.Sheets[0].ColumnHeader.Cells[0, 10].Text = "Father/Husband Name";
fpsalary.Sheets[0].ColumnHeader.Cells[0, 11].Text = "Date Of Birth";
fpsalary.Sheets[0].ColumnHeader.Cells[0, 12].Text = "Age";
fpsalary.Sheets[0].ColumnHeader.Cells[0, 13].Text = "PF A/C Number";
fpsalary.Sheets[0].ColumnHeader.Cells[0, 14].Text = "Remarks";
fpsalary.Sheets[0].Columns[0].HorizontalAlign = HorizontalAlign.Center;
fpsalary.Sheets[0].Columns[1].HorizontalAlign = HorizontalAlign.Left;
fpsalary.Sheets[0].Columns[2].HorizontalAlign = HorizontalAlign.Left;
fpsalary.Sheets[0].Columns[3].HorizontalAlign = HorizontalAlign.Left;
fpsalary.Sheets[0].Columns[4].HorizontalAlign = HorizontalAlign.Left;
fpsalary.Sheets[0].Columns[5].HorizontalAlign = HorizontalAlign.Left;
fpsalary.Sheets[0].Columns[6].HorizontalAlign = HorizontalAlign.Left;
fpsalary.Sheets[0].Columns[7].HorizontalAlign = HorizontalAlign.Left;
fpsalary.Sheets[0].Columns[8].HorizontalAlign = HorizontalAlign.Left;
fpsalary.Sheets[0].Columns[9].HorizontalAlign = HorizontalAlign.Left;
fpsalary.Sheets[0].Columns[10].HorizontalAlign = HorizontalAlign.Left;
fpsalary.Sheets[0].Columns[11].HorizontalAlign = HorizontalAlign.Left;
fpsalary.Sheets[0].Columns[12].HorizontalAlign = HorizontalAlign.Left;
fpsalary.Sheets[0].Columns[13].HorizontalAlign = HorizontalAlign.Left;
fpsalary.Sheets[0].Columns[0].Width = 50;
fpsalary.Sheets[0].Columns[1].Width = 200;
fpsalary.Sheets[0].Columns[2].Width = 200;
fpsalary.Sheets[0].Columns[3].Width = 200;
fpsalary.Sheets[0].Columns[4].Width = 200;
fpsalary.Sheets[0].Columns[5].Width = 200;
fpsalary.Sheets[0].Columns[6].Width = 200;
fpsalary.Sheets[0].Columns[7].Width = 80;
fpsalary.Sheets[0].Columns[8].Width = 80;
fpsalary.Sheets[0].Columns[9].Width = 80;
fpsalary.Sheets[0].Columns[10].Width = 200;
fpsalary.Sheets[0].Columns[11].Width = 80;
fpsalary.Sheets[0].Columns[12].Width = 200;
fpsalary.Sheets[0].Columns[13].Width = 80;
FarPoint.Web.Spread.TextCellType txt = new FarPoint.Web.Spread.TextCellType();
fpsalary.Sheets[0].Columns[0].CellType = txt;
fpsalary.Sheets[0].Columns[1].CellType = txt;
fpsalary.Sheets[0].Columns[2].CellType = txt;
fpsalary.Sheets[0].Columns[3].CellType = txt;
fpsalary.Sheets[0].Columns[4].CellType = txt;
fpsalary.Sheets[0].Columns[5].CellType = txt;
fpsalary.Sheets[0].Columns[6].CellType = txt;
fpsalary.Sheets[0].Columns[7].CellType = txt;
fpsalary.Sheets[0].Columns[8].CellType = txt;
fpsalary.Sheets[0].Columns[9].CellType = txt;
fpsalary.Sheets[0].Columns[10].CellType = txt;
fpsalary.Sheets[0].Columns[11].CellType = txt;
fpsalary.Sheets[0].Columns[12].CellType = txt;
fpsalary.Sheets[0].Columns[13].CellType = txt;
fpsalary.Width = 1200;
string strdeptcode = "";
for (int i = 0; i < chklsdept.Items.Count; i++)
{
if (chklsdept.Items[i].Selected == true)
{
if (strdeptcode == "")
{
strdeptcode = "'" + chklsdept.Items[i].Value + "'";
}
else
{
strdeptcode = strdeptcode + ",'" + chklsdept.Items[i].Value + "'";
}
}
}
if (strdeptcode.Trim() != "")
{
strdeptcode = " and st.dept_code in(" + strdeptcode + ")";
}
string strdesignquery = "";
for (int i = 0; i < chklsdesgination.Items.Count; i++)
{
if (chklsdesgination.Items[i].Selected == true)
{
if (strdesignquery == "")
{
strdesignquery = "'" + chklsdesgination.Items[i].Value + "'";
}
else
{
strdesignquery = strdesignquery + ",'" + chklsdesgination.Items[i].Value + "'";
}
}
}
if (strdesignquery.Trim() != "")
{
strdesignquery = " and st.desig_code in(" + strdesignquery + ")";
}
string strcatequery = "";
for (int i = 0; i < chklscategory.Items.Count; i++)
{
if (chklscategory.Items[i].Selected == true)
{
if (strcatequery == "")
{
strcatequery = "'" + chklscategory.Items[i].Value + "'";
}
else
{
strcatequery = strcatequery + ",'" + chklscategory.Items[i].Value + "'";
}
}
}
if (strcatequery.Trim() != "")
{
strcatequery = " and st.category_code in(" + strcatequery + ")";
}
string strtypequery = "";
for (int i = 0; i < chklsstafftype.Items.Count; i++)
{
if (chklsstafftype.Items[i].Selected == true)
{
if (strtypequery == "")
{
strtypequery = "'" + chklsstafftype.Items[i].Value + "'";
}
else
{
strtypequery = strtypequery + ",'" + chklsstafftype.Items[i].Value + "'";
}
}
}
if (strtypequery.Trim() != "")
{
strtypequery = " and st.stftype in(" + strtypequery + ")";
}
string strquery = "select s.staff_code,LEN(s.staff_code),s.staff_name,sa.father_name,sa.husband_name,sa.sex,convert(nvarchar(15),sa.date_of_birth,103) dob,convert(nvarchar(15),s.relieve_date,103) relivedate,convert(nvarchar(15),s.join_date,103) joindate,h.dept_name,d.desig_name,sc.category_name,st.stftype,s.Is_PF,s.pfnumber from staffmaster s,stafftrans st,hrdept_master h,desig_master d,staffcategorizer sc,staff_appl_master sa where sa.appl_no=s.appl_no and s.staff_code=st.staff_code and st.dept_code=h.dept_code and st.desig_code=d.desig_code and st.category_code=sc.category_code and st.latestrec=1 ";
if (ddltype.Text.ToString() == "New Staff List")
{
fpsalary.Sheets[0].Columns[9].Visible = false;
fpsalary.Sheets[0].Columns[7].Visible = true;
fpsalary.Sheets[0].Columns[8].Visible = true;
fpsalary.Sheets[0].Columns[11].Visible = true;
fpsalary.Sheets[0].Columns[12].Visible = true;
strquery = strquery + " and MONTH(s.join_date) = '" + ddlmonth.SelectedValue.ToString() + "' AND YEAR(s.join_date) = '" + ddlyear.SelectedItem.ToString() + "'";
}
else
{
fpsalary.Sheets[0].Columns[9].Visible = true;
fpsalary.Sheets[0].Columns[7].Visible = false;
fpsalary.Sheets[0].Columns[8].Visible = false;
fpsalary.Sheets[0].Columns[11].Visible = false;
fpsalary.Sheets[0].Columns[12].Visible = false;
strquery = strquery + " and MONTH(s.relieve_date) = '" + ddlmonth.SelectedValue.ToString() + "' AND YEAR(s.relieve_date) = '" + ddlyear.SelectedItem.ToString() + "' and ((s.resign=1 and s.settled=1) or (s.Discontinue=1))";
}
strquery = strquery + strdeptcode + strdesignquery + strcatequery + strtypequery + " order by s.join_date,LEN(s.staff_code),s.staff_code,s.staff_name";
ds.Dispose();
ds.Reset();
ds.Clear();
ds = d2.select_method_wo_parameter(strquery, "Text");
if (ds.Tables[0].Rows.Count > 0)
{
fpsalary.Visible = true;
lblexcel.Visible = true;
txtexcel.Visible = true;
btnprintmaster.Visible = true;
btnexcel.Visible = true;
int srno = 0;
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
string staffcode = ds.Tables[0].Rows[i]["staff_code"].ToString();
string name = ds.Tables[0].Rows[i]["staff_name"].ToString();
string dob = ds.Tables[0].Rows[i]["dob"].ToString();
string jdate = ds.Tables[0].Rows[i]["joindate"].ToString();
string deptname = ds.Tables[0].Rows[i]["dept_name"].ToString();
string designname = ds.Tables[0].Rows[i]["desig_name"].ToString();
string catname = ds.Tables[0].Rows[i]["category_name"].ToString();
string stftypenme = ds.Tables[0].Rows[i]["stftype"].ToString();
string fname = ds.Tables[0].Rows[i]["father_name"].ToString();
string hname = ds.Tables[0].Rows[i]["husband_name"].ToString();
if (hname.Trim() != "" && hname == null)
{
fname = hname;
}
string pfnumber = ds.Tables[0].Rows[i]["pfnumber"].ToString();
string relivedate = ds.Tables[0].Rows[i]["relivedate"].ToString();
string age = "";
string[] spdate = dob.Split('/');
if (spdate.GetUpperBound(0) == 2)
{
int curemonth = 0, cureyear;
DateTime dtdob = Convert.ToDateTime(spdate[1] + '/' + spdate[0] + '/' + spdate[2]);
DateTime dtnow = DateTime.Now;
int cury = Convert.ToInt32(dtnow.ToString("yyyy"));
int jyear = Convert.ToInt32(dtdob.ToString("yyyy"));
cureyear = cury - jyear;
int curmon = Convert.ToInt32(dtnow.ToString("MM"));
int jmon = Convert.ToInt32(dtdob.ToString("MM"));
if (curmon < jmon)
{
curemonth = (curmon + 12) - jmon;
cureyear--;
}
else
{
curemonth = curmon - jmon;
}
age = cureyear.ToString() + " Years ";
if (curemonth > 0)
{
age = age + curemonth + " Months";
}
}
fpsalary.Sheets[0].RowCount++;
srno++;
fpsalary.Sheets[0].Cells[fpsalary.Sheets[0].RowCount - 1, 0].Text = srno.ToString();
fpsalary.Sheets[0].Cells[fpsalary.Sheets[0].RowCount - 1, 1].Text = staffcode.ToString();
fpsalary.Sheets[0].Cells[fpsalary.Sheets[0].RowCount - 1, 2].Text = name.ToString();
fpsalary.Sheets[0].Cells[fpsalary.Sheets[0].RowCount - 1, 3].Text = deptname.ToString();
fpsalary.Sheets[0].Cells[fpsalary.Sheets[0].RowCount - 1, 4].Text = designname.ToString();
fpsalary.Sheets[0].Cells[fpsalary.Sheets[0].RowCount - 1, 5].Text = catname.ToString();
fpsalary.Sheets[0].Cells[fpsalary.Sheets[0].RowCount - 1, 6].Text = stftypenme.ToString();
fpsalary.Sheets[0].Cells[fpsalary.Sheets[0].RowCount - 1, 7].Text = jdate.ToString();
fpsalary.Sheets[0].Cells[fpsalary.Sheets[0].RowCount - 1, 8].Text = jdate.ToString();
fpsalary.Sheets[0].Cells[fpsalary.Sheets[0].RowCount - 1, 9].Text = relivedate.ToString();
fpsalary.Sheets[0].Cells[fpsalary.Sheets[0].RowCount - 1, 10].Text = fname.ToString();
fpsalary.Sheets[0].Cells[fpsalary.Sheets[0].RowCount - 1, 11].Text = dob.ToString();
fpsalary.Sheets[0].Cells[fpsalary.Sheets[0].RowCount - 1, 12].Text = age.ToString();
fpsalary.Sheets[0].Cells[fpsalary.Sheets[0].RowCount - 1, 13].Text = pfnumber.ToString();
}
}
else
{
errmsg.Text = "No Records Found";
errmsg.Visible = true;
}
fpsalary.Sheets[0].PageSize = fpsalary.Sheets[0].RowCount;
}
catch (Exception ex)
{
errmsg.Visible = true;
errmsg.Text = ex.ToString();
d2.sendErrorMail(ex, "13", "StaffSalarydetails.aspx");
}
}
public void Gofuncation()
{
try
{
string Branch = Rs.GetSelectedItemsValueAsString(cbl_branch);
string batch = Convert.ToString(ddl_batch.SelectedItem.Text);
string college = Convert.ToString(ddlcollege.SelectedValue);
string[] fromDate = txtFromDate.Text.Split('/');
string[] toDate = txtToDate.Text.Split('/');
string fromDt = fromDate[1] + "/" + fromDate[0] + "/" + fromDate[2];
string toDt = toDate[1] + "/" + toDate[0] + "/" + toDate[2];
string Stream = string.Empty;
string Category = string.Empty;
if (ddlStream.SelectedItem.Text != "All")
{
Stream = ddlStream.SelectedValue;
}
if (ddlSession.SelectedItem.Text != "All")
{
Category = ddlSession.SelectedValue;
}
DataTable data = new DataTable();
DataView Dsview = new DataView();
DataView DvRank = new DataView();
string Query = "select r.app_no,r.Roll_Admit,r.Stud_Name,CONVERT(varchar(10),r.Adm_Date,103) as AdmitDate,(select Mastervalue from CO_MasterValues where MasterCode=a.quota) as Categroy,a.quota,a.StreamAdmission,(select TextVal from TextValTable where TextCode= a.StreamAdmission) as Stream,r.degree_code,st.HSCMarkSec,st.CombinedScore,st.CombinedScoreSII,enrollment_card_date,(c.Course_Name +''+dt.Dept_Name) as Departmet from applyn a,Registration r,ST_Student_Mark_Detail st ,Degree d,Department dt,Course c where d.Degree_Code =r.degree_code and d.Dept_Code =dt.Dept_Code and c.Course_Id =d.Course_Id and a.app_no =r.App_No and a.app_no =st.ST_AppNo and r.App_No =st.ST_AppNo and CC=0 and DelFlag =0 and Exam_Flag <>'Debar' and r.college_code ='" + college + "' and r.Batch_Year ='" + batch + "' and r.degree_code in ('" + Branch + "') and Adm_Date between '" + fromDt + "' and '" + toDt + "' ";
if (Stream.Trim() != "")
{
Query += " and a.StreamAdmission ='" + Stream + "'";
}
if (Category.Trim() != "")
{
Query += " and a.quota ='" + Category + "'";
}
Query += " select ST_Rank,ST_RankCriteria,ST_Stream,ST_AppNo from ST_RankTable St,Registration r where st.ST_AppNo =r.App_No and r.degree_code in ('" + Branch + "') and r.college_code ='" + college + "' and r.Batch_Year ='" + batch + "'";
if (Stream.Trim() != "")
{
Query += " and ST_Stream ='" + Stream + "'";
}
if (Category.Trim() != "")
{
Query += " and ST_RankCriteria ='" + Category + "'";
}
ds.Clear();
ds = d2.select_method_wo_parameter(Query, "Text");
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
int SNo = 0;
FpSpread1.Sheets[0].ColumnCount = 0;
FpSpread1.Sheets[0].RowCount = 0;
FpSpread1.Sheets[0].RowHeader.Visible = false;
FpSpread1.CommandBar.Visible = false;
FpSpread1.Sheets[0].ColumnHeader.RowCount = 1;
FpSpread1.Sheets[0].ColumnCount++;
FpSpread1.Sheets[0].ColumnHeader.Cells[0, FpSpread1.Sheets[0].ColumnCount - 1].Text = "S.No";
FpSpread1.Sheets[0].ColumnHeader.Cells[0, FpSpread1.Sheets[0].ColumnCount - 1].Font.Bold = true;
FpSpread1.Columns[FpSpread1.Sheets[0].ColumnCount - 1].Width = 25;
FpSpread1.Sheets[0].ColumnCount++;
FpSpread1.Sheets[0].ColumnHeader.Cells[0, FpSpread1.Sheets[0].ColumnCount - 1].Text = "Admission / Application No";
FpSpread1.Sheets[0].ColumnHeader.Cells[0, FpSpread1.Sheets[0].ColumnCount - 1].Font.Bold = true;
FpSpread1.Sheets[0].ColumnCount++;
FpSpread1.Sheets[0].ColumnHeader.Cells[0, FpSpread1.Sheets[0].ColumnCount - 1].Text = "Student Name";
FpSpread1.Sheets[0].ColumnHeader.Cells[0, FpSpread1.Sheets[0].ColumnCount - 1].Font.Bold = true;
FpSpread1.Sheets[0].ColumnCount++;
FpSpread1.Sheets[0].ColumnHeader.Cells[0, FpSpread1.Sheets[0].ColumnCount - 1].Text = "Date of Admit";
FpSpread1.Sheets[0].ColumnHeader.Cells[0, FpSpread1.Sheets[0].ColumnCount - 1].Font.Bold = true;
FpSpread1.Sheets[0].ColumnCount++;
FpSpread1.Sheets[0].ColumnHeader.Cells[0, FpSpread1.Sheets[0].ColumnCount - 1].Text = "Combined Score";
FpSpread1.Sheets[0].ColumnHeader.Cells[0, FpSpread1.Sheets[0].ColumnCount - 1].Font.Bold = true;
FpSpread1.Sheets[0].ColumnCount++;
FpSpread1.Sheets[0].ColumnHeader.Cells[0, FpSpread1.Sheets[0].ColumnCount - 1].Text = "Percentile";
FpSpread1.Sheets[0].ColumnHeader.Cells[0, FpSpread1.Sheets[0].ColumnCount - 1].Font.Bold = true;
FpSpread1.Sheets[0].ColumnCount++;
FpSpread1.Sheets[0].ColumnHeader.Cells[0, FpSpread1.Sheets[0].ColumnCount - 1].Text = "Rank";
FpSpread1.Sheets[0].ColumnHeader.Cells[0, FpSpread1.Sheets[0].ColumnCount - 1].Font.Bold = true;
FarPoint.Web.Spread.TextCellType db = new FarPoint.Web.Spread.TextCellType();
data = ds.Tables[0].DefaultView.ToTable(true, "degree_code", "StreamAdmission", "quota");
DataView Dsort = data.DefaultView;
Dsort.Sort = "degree_code,StreamAdmission,quota";
for (int intds = 0; intds < Dsort.Count; intds++)
{
ds.Tables[0].DefaultView.RowFilter = "degree_code='" + Convert.ToString(Dsort[intds]["degree_code"]) + "' and StreamAdmission='" + Convert.ToString(Dsort[intds]["StreamAdmission"]) + "' and quota='" + Convert.ToString(Dsort[intds]["quota"]) + "'";
Dsview = ds.Tables[0].DefaultView;
string Strm = Convert.ToString(Dsview[0]["Stream"]);
if (Strm.Trim() != "Stream II")
{
Dsview.Sort = " CombinedScore desc";
}
else
{
Dsview.Sort = " CombinedScoreSII desc,stud_name asc";
}
if (Dsview.Count > 0)
{
FpSpread1.Sheets[0].RowCount++;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 0].Text = (Convert.ToString(Dsview[0]["Departmet"]) + " " + Convert.ToString(Dsview[0]["Stream"]) + " " + Convert.ToString(Dsview[0]["Categroy"]));
FpSpread1.Sheets[0].SpanModel.Add(FpSpread1.Sheets[0].RowCount - 1, 0, 1, FpSpread1.Sheets[0].ColumnCount);
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 0].HorizontalAlign = HorizontalAlign.Center;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 0].Font.Bold = true;
for (int intdv = 0; intdv < Dsview.Count; intdv++)
{
SNo++;
string AppNo = Convert.ToString(Dsview[intdv]["app_no"]);
FpSpread1.Sheets[0].RowCount++;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 0].Text = Convert.ToString(SNo);
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 0].HorizontalAlign = HorizontalAlign.Center;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 1].Text = Convert.ToString(Dsview[intdv]["Roll_Admit"]);
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 1].HorizontalAlign = HorizontalAlign.Center;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 2].Text = Convert.ToString(Dsview[intdv]["stud_name"]);
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 2].HorizontalAlign = HorizontalAlign.Left;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 3].Text = Convert.ToString(Dsview[intdv]["AdmitDate"]);
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 3].HorizontalAlign = HorizontalAlign.Center;
double comI = 0;
string ComIIval = Convert.ToString(Dsview[intdv]["CombinedScore"]);
double.TryParse(ComIIval, out comI);
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 4].CellType = db;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 4].Text = Convert.ToString(Math.Round(comI, 4));
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 4].HorizontalAlign = HorizontalAlign.Center;
double comII = 0;
string ComIIvalue = Convert.ToString(Dsview[intdv]["CombinedScoreSII"]);
double.TryParse(ComIIvalue, out comII);
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 5].CellType = db;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 5].Text = Convert.ToString(Math.Round(comII, 4));
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 5].HorizontalAlign = HorizontalAlign.Center;
ds.Tables[1].DefaultView.RowFilter = "ST_RankCriteria='" + Convert.ToString(Dsort[intds]["quota"]) + "' and ST_Stream ='" + Convert.ToString(Dsort[intds]["StreamAdmission"]) + "' and ST_AppNo ='" + AppNo + "'";
DvRank = ds.Tables[1].DefaultView;
if (DvRank.Count > 0)
{
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 6].Text = Convert.ToString(DvRank[0]["ST_Rank"]);
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 6].HorizontalAlign = HorizontalAlign.Center;
}
}
}
}
if (FpSpread1.Sheets[0].RowCount > 0)
{
FpSpread1.Visible = true;
FpSpread1.Sheets[0].PageSize = FpSpread1.Sheets[0].RowCount;
div_report.Visible = true;
}
}
else
{
lblAlertMsg.Text = "No Record(s) Found";
lblAlertMsg.Visible = true;
divPopupAlert.Visible = true;
return;
}
}
catch
{
}
}
public void gomethod()
{
string sqlcmdgraderstotal = "";
string GPA_Val = "";
string CGPA_Val = "";
string RollNo1 = "";
string semval = "";
string strtempseme = "";
string latmode1 = "";
FpSpread1.Sheets[0].RowCount = 0;
FpSpread1.Sheets[0].ColumnCount = 0;
FpSpread1.Sheets[0].ColumnHeader.Visible = true;
FpSpread1.Sheets[0].ColumnCount++;
FpSpread1.Sheets[0].AutoPostBack = true;
FpSpread1.Sheets[0].ColumnHeader.RowCount = 1;
FpSpread1.Sheets[0].ColumnCount = 8;
FpSpread1.Sheets[0].RowCount = 0;
string sqlcmdall = "select distinct ROW_NUMBER() OVER (ORDER BY Roll_no) As SrNo,Convert(Varchar,batch_year) + '-' + acronym as Degree,roll_no,reg_no,stud_name,stud_type,Convert(Varchar,r.batch_year) + '-' + Convert(Varchar,r.degree_code) as DegreeCode,mode as Mode from registration r,degree d where r.degree_code=d.degree_code";
if (txtbatch.Text != "---Select---" || chklstbatch.Items.Count != null)
{
int itemcount = 0;
for (itemcount = 0; itemcount < chklstbatch.Items.Count; itemcount++)
{
if (chklstbatch.Items[itemcount].Selected == true)
{
if (strbatch == "")
{
strbatch = chklstbatch.Items[itemcount].Value.ToString();
}
else
{
strbatch = strbatch + "," + chklstbatch.Items[itemcount].Value.ToString();
}
}
}
if (strbatch != "")
{
strbatch = " in(" + strbatch + ")";
}
sqlcmdall = sqlcmdall + " and r.batch_year " + strbatch + "";
sqlcmdbatch = " batch_year " + strbatch + "";
}
else
{
// errmsg.Visible = true;
errmsg.Text = "Plaese Choose Batch";
}
if (txtbranch.Text != "---Select---" || chklstbranch.Items.Count != null)
{
int itemcount1 = 0;
for (itemcount1 = 0; itemcount1 < chklstbranch.Items.Count; itemcount1++)
{
if (chklstbranch.Items[itemcount1].Selected == true)
{
if (strbranch == "")
{
strbranch = chklstbranch.Items[itemcount1].Value.ToString();
}
else
{
strbranch = strbranch + "," + chklstbranch.Items[itemcount1].Value.ToString();
}
}
}
if (strbranch != "")
{
strbranch = " in (" + strbranch + ")";
}
sqlcmdall = sqlcmdall + " and r.degree_code" + strbranch + "";
sqlcmdbranch = " degree_code" + strbranch + " and";
}
else
{
// errmsg.Visible = true;
errmsg.Text = "Plaese Choose Degree";
}
if (chklstsection.Items.Count > 0)
{
if (txtsection.Text != "---Select---" || chklstsection.Items.Count != null)
{
int itemcount = 0;
if (chklstsection.Items[chklstsection.Items.Count - 1].Selected == true)
{
sqlcmdall1 = "or sections is null or sections=''";
}
for (itemcount = 0; itemcount < chklstsection.Items.Count - 1; itemcount++)
{
if (chklstsection.Items[itemcount].Selected == true)
{
if (strsecti == "")
{
strsecti = "'" + chklstsection.Items[itemcount].Value.ToString() + "'";
}
else
{
strsecti = strsecti + "," + "'" + chklstsection.Items[itemcount].Value.ToString() + "'";
}
}
}
if (strsecti != "")
{
strsecti = " in(" + strsecti + ")";
sqlcmdall = sqlcmdall + " and (sections " + strsecti + sqlcmdall1 + ")";
}
}
}
if (txtseme.Text != "---Select---" || chklstseme.Items.Count != null)
{
int itemcount3 = 0;
for (itemcount3 = 0; itemcount3 < chklstseme.Items.Count; itemcount3++)
{
if (chklstseme.Items[itemcount3].Selected == true)
{
if (strseme == "")
{
strseme = chklstseme.Items[itemcount3].Value.ToString();
}
else
{
strseme = strseme + "," + chklstseme.Items[itemcount3].Value.ToString();
}
if (strseme == "")
{
strtempseme = chklstseme.Items[itemcount3].Value.ToString();
}
else
{
strtempseme = strtempseme + "," + chklstseme.Items[itemcount3].Value.ToString();
string[] semecount = strtempseme.Split(new Char[] { ',' });
if (semecount.GetUpperBound(0) >= 0)
{
int semcount = semecount.GetUpperBound(0);
semval = Convert.ToString(semecount[semcount]);
}
}
}
}
if (strseme != "")
{
strseme = " in(" + strseme + ")";
sqlcmdseme = " and current_semester " + strseme + "";
}
}
else
{
// errmsg.Visible = true;
errmsg.Text = "Plaese Choose Semester";
}
sqlcmdall = sqlcmdall + "and cc=0 and delflag=0 and exam_flag<>'debar' order by r.reg_no";
dgo = d2.select_method(sqlcmdall, hat, "Text");
FarPoint.Web.Spread.TextCellType textcell = new FarPoint.Web.Spread.TextCellType();
if (dgo != null && dgo.Tables[0] != null && dgo.Tables[0].Rows.Count > 0)
{
FpSpread1.Visible = true;
FpSpread1.Sheets[0].ColumnHeaderSpanModel.Add(0, 0, 1, 1);
FpSpread1.Sheets[0].ColumnHeader.Cells[0, 0].HorizontalAlign = HorizontalAlign.Center;
FpSpread1.Sheets[0].ColumnHeader.Cells[0, 0].Text = "S.No";
FpSpread1.Sheets[0].ColumnHeaderSpanModel.Add(0, 1, 1, 1);
FpSpread1.Sheets[0].ColumnHeader.Cells[0, 1].Text = "Degree";
FpSpread1.Sheets[0].SetColumnMerge(1, FarPoint.Web.Spread.Model.MergePolicy.Always);
FpSpread1.Sheets[0].ColumnHeaderSpanModel.Add(0, 2, 1, 1);
FpSpread1.Sheets[0].ColumnHeader.Cells[0, 2].Text = "Roll No";
FpSpread1.Sheets[0].ColumnHeaderSpanModel.Add(0, 3, 1, 1);
FpSpread1.Sheets[0].ColumnHeader.Cells[0, 3].Text = "Reg No";
FpSpread1.Sheets[0].Columns[3].CellType = textcell;
FpSpread1.Sheets[0].ColumnHeaderSpanModel.Add(0, 4, 1, 1);
FpSpread1.Sheets[0].ColumnHeader.Cells[0, 4].Text = "Student Name";
FpSpread1.Sheets[0].ColumnHeaderSpanModel.Add(0, 5, 1, 1);
FpSpread1.Sheets[0].ColumnHeader.Cells[0, 5].Text = "Student Type";
FpSpread1.Sheets[0].Columns[0].HorizontalAlign = HorizontalAlign.Center;
FpSpread1.Sheets[0].Columns[1].HorizontalAlign = HorizontalAlign.Center;
FpSpread1.Sheets[0].Columns[6].Visible = false;
FpSpread1.Sheets[0].Columns[7].Visible = false;
//FpSpread1.DataSource = dgo;
//FpSpread1.DataBind();
int slno = 0;
for (int cnt = 0; cnt < dgo.Tables[0].Rows.Count; cnt++)
{
slno++;
FpSpread1.Sheets[0].RowCount = FpSpread1.Sheets[0].RowCount + 1;
int rc = FpSpread1.Sheets[0].RowCount - 1;
//magesh 27/2/18
FarPoint.Web.Spread.TextCellType txt = new FarPoint.Web.Spread.TextCellType();
FpSpread1.Sheets[0].Cells[rc, 2].CellType = txt;//magesh 27/1/18
FpSpread1.Sheets[0].Cells[rc, 0].Text = slno.ToString();
FpSpread1.Sheets[0].Cells[rc, 1].Text = dgo.Tables[0].Rows[cnt]["Degree"].ToString();
FpSpread1.Sheets[0].Cells[rc, 2].Text = dgo.Tables[0].Rows[cnt]["roll_no"].ToString();
FpSpread1.Sheets[0].Cells[rc, 3].Text = dgo.Tables[0].Rows[cnt]["reg_no"].ToString();
FpSpread1.Sheets[0].Cells[rc, 4].Text = dgo.Tables[0].Rows[cnt]["stud_name"].ToString();
FpSpread1.Sheets[0].Cells[rc, 5].Text = dgo.Tables[0].Rows[cnt]["stud_type"].ToString();
FpSpread1.Sheets[0].Cells[rc, 6].Text = dgo.Tables[0].Rows[cnt]["DegreeCode"].ToString();
FpSpread1.Sheets[0].Cells[rc, 7].Text = dgo.Tables[0].Rows[cnt]["Mode"].ToString();
}
sqlcmdretriveunialldet = "select distinct exam_month,exam_year,DATENAME(MONTH,'1990/' + CAST(exam_month AS VARCHAR(3)) + '/23')as exam_month1 from exam_details where " + sqlcmdbranch + " " + sqlcmdbatch + " " + sqlcmdseme + " order by exam_year,exam_month";
dgo2 = d2.select_method(sqlcmdretriveunialldet, hat, "Text");
if (dgo2 != null && dgo2.Tables[0] != null && dgo2.Tables[0].Rows.Count > 0)
{
for (int o = 0; o < dgo2.Tables[0].Rows.Count; o++)
{
strexammonth = Convert.ToString(dgo2.Tables[0].Rows[o]["exam_month1"]);
strexamyear = dgo2.Tables[0].Rows[o]["exam_year"].ToString();
FpSpread1.Sheets[0].ColumnCount = FpSpread1.Sheets[0].ColumnCount + 1;
FpSpread1.Sheets[0].ColumnHeader.Cells[0, FpSpread1.Sheets[0].ColumnCount - 1].Text = Convert.ToString(strexammonth + "-" + strexamyear);
FpSpread1.Sheets[0].ColumnHeader.Cells[0, FpSpread1.Sheets[0].ColumnCount - 1].Note = Convert.ToString(dgo2.Tables[0].Rows[o]["exam_month"]);
FpSpread1.Sheets[0].ColumnHeader.Cells[0, FpSpread1.Sheets[0].ColumnCount - 1].Tag = Convert.ToString(strexamyear);
}
FpSpread1.Sheets[0].ColumnCount = FpSpread1.Sheets[0].ColumnCount + 1;
FpSpread1.Sheets[0].ColumnHeader.Cells[0, FpSpread1.Sheets[0].ColumnCount - 1].Text = "CGPA";
}
FpSpread1.Sheets[0].PageSize = FpSpread1.Sheets[0].RowCount;
for (int q = 0; q < FpSpread1.Sheets[0].RowCount; q++)
{
int col_count = FpSpread1.Sheets[0].ColumnCount;
string tempt1 = "", tempt2 = "";
Boolean checkfailstatus = false;//added By srinath
for (int r = 8; r < col_count; r++)
{
RollNo1 = Convert.ToString(FpSpread1.Sheets[0].Cells[q, 2].Text);
string[] degcodebatchyr = Convert.ToString(FpSpread1.Sheets[0].Cells[q, 6].Text).Split(new Char[] { '-' });
if (degcodebatchyr.GetUpperBound(0) >= 1)
{
batch_year1 = Convert.ToString(degcodebatchyr[0]);
degree_code1 = Convert.ToString(degcodebatchyr[1]);
}
exam_month1 = Convert.ToString(FpSpread1.Sheets[0].ColumnHeader.Cells[0, r].Note);
exam_year1 = Convert.ToString(FpSpread1.Sheets[0].ColumnHeader.Cells[0, r].Tag);
latmode1 = Convert.ToString(dgo.Tables[0].Rows[q]["mode"]);
if (RollNo1 != null && RollNo1 != "" && degree_code1 != null && degree_code1 != "" && batch_year1 != null && batch_year1 != "" && exam_month1 != null && exam_month1 != "" && exam_year1 != null && exam_year1 != "" && semval != null && semval != "" && latmode1 != null && latmode1 != "")
{
sqlcmdgraderstotal = " select distinct frange,trange,credit_points,mark_grade from grade_master where degree_code=" + degree_code1 + " and batch_year=" + batch_year1 + " and college_code=" + Session["collegecode"] + "";
dggradetot = d2.select_method(sqlcmdgraderstotal, hat, "Text");
//Added By Srinath 12/2/2013 ==Start
string syll_code = "";
syll_code = GetFunction("select distinct syll_code from exam_details e,syllabus_master s where e.degree_code=s.degree_code and e.batch_year=s.batch_year and e.current_semester=s.semester and e.degree_code='" + degree_code1 + "' and e.batch_year=" + batch_year1 + " and exam_month=" + exam_month1 + " and exam_year=" + exam_year1 + "");
//string checkresult = "Select Mark_Entry.result,Subject.credit_points,Mark_Entry.total,Mark_Entry.grade from Mark_Entry,Subject,exam_details e where Mark_Entry.Subject_No = Subject.Subject_No and e.exam_code=mark_entry.exam_code and e.degree_code=" + degree_code1 + " and e.batch_year=" + batch_year1 + " and subject.syll_code="+ syll_code +" and roll_no='" + RollNo1 + "' and result<>'Pass'";
string checkresult = "Select isnull(Subject_Code,'') as scode , isnull(subjecT_name,'') as sname , semester from subject,syllabus_master as smas where smas.syll_code = subject.syll_code and subject_no in (select distinct subject_no from mark_entry where subject_no not in (select distinct subject_no from mark_entry where passorfail=1 and result='Pass' and ltrim(rtrim(roll_no))='" + RollNo1 + "') and roll_no ='" + RollNo1 + "' and subject.syll_code=" + syll_code.ToString() + " )";
DataSet dscheckresult = d2.select_method(checkresult, hat, "Text");
//Rajkumar for fail CGPA on 29-5-2018 ===========
bool ArrerCheckFlag = false;
string val1 = d2.GetFunctionv("select value from Master_Settings where settings = 'include gpa for fail student'");//Rajkumar on 28-5-2018
if (val1.Trim() == "true" || val1.Trim() == "1")
{
ArrerCheckFlag = true;
}
//=====================
if (dscheckresult.Tables[0].Rows.Count > 0 && !ArrerCheckFlag)
{
GPA_Val = "-";
checkfailstatus = true;
}
else
{
// GPA_Val = Calulat_GPA(RollNo1,degree_code1,batch_year1,exam_month1 ,exam_year1 );//modified By Srinath 12/2/2013
GPA_Val = d2.Calulat_GPA_Semwise(RollNo1, degree_code1, batch_year1, exam_month1, exam_year1, collegecode);
}
//------------------rajkumar
FpSpread1.Sheets[0].Cells[q, r].HorizontalAlign = HorizontalAlign.Center;
if (Convert.ToString(GPA_Val) == "0")
{
FpSpread1.Sheets[0].Cells[q, r].Text = "-";
}
else
{
FpSpread1.Sheets[0].Cells[q, r].Text = Convert.ToString(GPA_Val);
}
//===End
if (r >= col_count - 2)
{ //Added By Srinath 12/2/2013 ==Start
if (checkfailstatus == false)
{
CGPA_Val = d2.Calculete_CGPA(RollNo1, semval, degree_code1, batch_year1, latmode1, collegecode);
}
else
{
CGPA_Val = "-";
}
// CGPA_Val = Calculete_CGPA(RollNo1, semval, degree_code1, batch_year1, exam_month1, exam_year1, latmode1);//Hiden By Srinath 12/2/2013
//===End
FpSpread1.Sheets[0].Cells[q, FpSpread1.Sheets[0].ColumnCount - 1].HorizontalAlign = HorizontalAlign.Center;
if (Convert.ToString(CGPA_Val) == "0")
{
FpSpread1.Sheets[0].Cells[q, FpSpread1.Sheets[0].ColumnCount - 1].Text = "-";
}
else
{
FpSpread1.Sheets[0].Cells[q, FpSpread1.Sheets[0].ColumnCount - 1].Text = Convert.ToString(CGPA_Val);
}
}
}
}
}
btnxl.Visible = true;
Button1.Visible = true;
}
}
private void loadspreaddetails(DataSet ds)
{
try
{
loadspreadHeader(ds);
FarPoint.Web.Spread.TextCellType txtCell = new FarPoint.Web.Spread.TextCellType();
int sno = 0;
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
for (int row = 0; row < ds.Tables[0].Rows.Count; row++)
{
Fpload1.Sheets[0].RowCount++;
sno++;
Gym_Name = Convert.ToString(ds.Tables[0].Rows[row]["GymName"]).Trim();
Gym_Acroynm = Convert.ToString(ds.Tables[0].Rows[row]["GymAcr"]).Trim();
Gym_PK = Convert.ToString(ds.Tables[0].Rows[row]["GymPK"]).Trim();
Fpload1.Sheets[0].Cells[Fpload1.Sheets[0].RowCount - 1, 0].CellType = txtCell;
Fpload1.Sheets[0].Cells[Fpload1.Sheets[0].RowCount - 1, 1].CellType = txtCell;
Fpload1.Sheets[0].Cells[Fpload1.Sheets[0].RowCount - 1, 2].CellType = txtCell;
Fpload1.Sheets[0].Cells[Fpload1.Sheets[0].RowCount - 1, 3].CellType = txtCell;
Fpload1.Sheets[0].Cells[Fpload1.Sheets[0].RowCount - 1, 0].Text = Convert.ToString(sno);
Fpload1.Sheets[0].Cells[Fpload1.Sheets[0].RowCount - 1, 1].Text = Gym_Name;
Fpload1.Sheets[0].Cells[Fpload1.Sheets[0].RowCount - 1, 2].Text = Gym_Acroynm;
Fpload1.Sheets[0].Cells[Fpload1.Sheets[0].RowCount - 1, 3].Text = Gym_PK;
Fpload1.Sheets[0].Cells[Fpload1.Sheets[0].RowCount - 1, 0].HorizontalAlign = HorizontalAlign.Center;
Fpload1.Sheets[0].Cells[Fpload1.Sheets[0].RowCount - 1, 1].HorizontalAlign = HorizontalAlign.Left;
Fpload1.Sheets[0].Cells[Fpload1.Sheets[0].RowCount - 1, 2].HorizontalAlign = HorizontalAlign.Left;
Fpload1.Sheets[0].Cells[Fpload1.Sheets[0].RowCount - 1, 3].HorizontalAlign = HorizontalAlign.Left;
Fpload1.Sheets[0].Cells[Fpload1.Sheets[0].RowCount - 1, 0].VerticalAlign = VerticalAlign.Middle;
Fpload1.Sheets[0].Cells[Fpload1.Sheets[0].RowCount - 1, 1].VerticalAlign = VerticalAlign.Middle;
Fpload1.Sheets[0].Cells[Fpload1.Sheets[0].RowCount - 1, 2].VerticalAlign = VerticalAlign.Middle;
Fpload1.Sheets[0].Cells[Fpload1.Sheets[0].RowCount - 1, 3].VerticalAlign = VerticalAlign.Middle;
Fpload1.Sheets[0].Cells[Fpload1.Sheets[0].RowCount - 1, 0].Locked = true;
Fpload1.Sheets[0].Cells[Fpload1.Sheets[0].RowCount - 1, 1].Locked = true;
Fpload1.Sheets[0].Cells[Fpload1.Sheets[0].RowCount - 1, 2].Locked = true;
}
Fpload1.Sheets[0].Columns[0].Width = 50;
Fpload1.Sheets[0].Columns[1].Width = 150;
Fpload1.Sheets[0].Columns[2].Width = 100;
Fpload1.Sheets[0].Columns[3].Visible = false;
Fpload1.Sheets[0].PageSize = Fpload1.Sheets[0].RowCount;
Fpload1.SaveChanges();
Fpload1.Visible = true;
lbprint.Visible = true;
lblrptname.Visible = true;
txtexcelname.Visible = true;
btn_excel.Visible = true;
btnprintmaster.Visible = true;
}
}
catch
{
}
}
protected void loadfsstaff()
{
try
{
string sql = "select sm.staff_name,st.staff_code from staffmaster sm,stafftrans st,hrdept_master h where sm.staff_code=st.staff_code and st.dept_code=h.dept_code and sm.college_code=h.college_code and sm.college_code='" + ddlcollege.SelectedValue + "'";
if (ddldepratstaff.SelectedItem.ToString() != "All")
{
sql = sql + " and st.dept_code='" + ddldepratstaff.SelectedValue.ToString() + "'";
}
if (txt_search.Text != "")
{
if (ddlstaff.SelectedIndex == 0)
{
sql = sql + " and sm.staff_name like '" + txt_search.Text.ToString() + "%'";
}
else
{
sql = sql + " and sm.staff_code like '" + txt_search.Text.ToString() + "%'";
}
}
DataSet dsbindspread = d2.select_method_wo_parameter(sql, "Text");
FarPoint.Web.Spread.CheckBoxCellType chkcell1 = new FarPoint.Web.Spread.CheckBoxCellType();
fsstaff.Sheets[0].RowCount = 0;
fsstaff.CommandBar.Visible = false;
fsstaff.SheetCorner.ColumnCount = 0;
if (dsbindspread.Tables[0].Rows.Count > 0)
{
int sno = 0;
fsstaff.Sheets[0].ColumnHeader.Columns[0].Label = "S.No";
fsstaff.Sheets[0].ColumnHeader.Columns[2].Label = "Staff Name";
fsstaff.Sheets[0].ColumnHeader.Columns[1].Label = "Staff Code";
fsstaff.Sheets[0].ColumnHeader.Columns[3].Label = "Select";
fsstaff.Sheets[0].Columns[0].Width = 50;
fsstaff.Sheets[0].Columns[1].Width = 210;
fsstaff.Sheets[0].Columns[2].Width = 350;
fsstaff.Sheets[0].Columns[3].Width = 64;
fsstaff.Sheets[0].ColumnCount = 4;
fsstaff.Width = 690;
fsstaff.Visible = true;
FarPoint.Web.Spread.TextCellType btva = new FarPoint.Web.Spread.TextCellType();
for (int rolcount = 0; rolcount < dsbindspread.Tables[0].Rows.Count; rolcount++)
{
sno++;
string name = dsbindspread.Tables[0].Rows[rolcount]["staff_name"].ToString();
string code = dsbindspread.Tables[0].Rows[rolcount]["staff_code"].ToString();
fsstaff.Sheets[0].RowCount = fsstaff.Sheets[0].RowCount + 1;
fsstaff.Sheets[0].Rows[fsstaff.Sheets[0].RowCount - 1].Font.Bold = false;
fsstaff.Sheets[0].Cells[fsstaff.Sheets[0].RowCount - 1, 0].Text = Convert.ToString(sno);
fsstaff.Sheets[0].Cells[fsstaff.Sheets[0].RowCount - 1, 0].HorizontalAlign = HorizontalAlign.Center;
fsstaff.Sheets[0].Cells[fsstaff.Sheets[0].RowCount - 1, 2].Text = name;
fsstaff.Sheets[0].Cells[fsstaff.Sheets[0].RowCount - 1, 2].Locked = true;
fsstaff.Sheets[0].Cells[fsstaff.Sheets[0].RowCount - 1, 2].CellType = btva;
fsstaff.Sheets[0].Cells[fsstaff.Sheets[0].RowCount - 1, 2].HorizontalAlign = HorizontalAlign.Left;
fsstaff.Sheets[0].Cells[fsstaff.Sheets[0].RowCount - 1, 1].CellType = btva;
fsstaff.Sheets[0].Cells[fsstaff.Sheets[0].RowCount - 1, 1].Text = code;
fsstaff.Sheets[0].Cells[fsstaff.Sheets[0].RowCount - 1, 1].Locked = true;
fsstaff.Sheets[0].Cells[fsstaff.Sheets[0].RowCount - 1, 1].HorizontalAlign = HorizontalAlign.Left;
fsstaff.Sheets[0].Cells[fsstaff.Sheets[0].RowCount - 1, 3].CellType = chkcell1;
fsstaff.Sheets[0].Cells[fsstaff.Sheets[0].RowCount - 1, 3].HorizontalAlign = HorizontalAlign.Center;
fsstaff.Sheets[0].Columns[3].HorizontalAlign = HorizontalAlign.Left;
chkcell1.AutoPostBack = true;
}
}
else
{
fsstaff.Visible = false;
errstaff.Visible = true;
errstaff.Text = "No Records Found";
}
fsstaff.Sheets[0].AutoPostBack = false;
fsstaff.Sheets[0].PageSize = fsstaff.Sheets[0].RowCount;
fsstaff.SaveChanges();
}
catch (Exception ex)
{
errstaff.Visible = true;
errstaff.Text = ex.ToString();
}
}
protected void Buttongo_Click(object sender, EventArgs e)
{
try
{
ss2 = "";
p = "";
FpSpread2.Visible = false;//btnreset.Visible = false;
FpSpread2.CommandBar.Visible = false;
FpSpread2.Sheets[0].RowCount = 0;
FpSpread2.Sheets[0].ColumnCount = 4;
FpSpread2.Sheets[0].Columns[0].Width = 100;
FpSpread2.Sheets[0].Columns[1].Width = 100;
FpSpread2.Sheets[0].Columns[2].Width = 100;
FpSpread2.Sheets[0].Columns[3].Width = 100;
// FpSpread2.Sheets[0].Columns[4].Width = 90;
//FpSpread2.Sheets[0].Columns[0].Locked = true;
//FpSpread2.Sheets[0].Columns[1].Locked = true;
//FpSpread2.Sheets[0].Columns[2].Locked = true;
//FpSpread2.Sheets[0].Columns[4].Locked = true;
FpSpread2.Sheets[0].RowHeader.Visible = false;
FpSpread2.Sheets[0].DefaultStyle.Font.Size = FontUnit.Medium;
FpSpread2.Sheets[0].ColumnHeader.DefaultStyle.Font.Size = FontUnit.Medium;
FpSpread2.Sheets[0].ColumnHeader.DefaultStyle.Font.Name = "Book Antiqua";
FpSpread2.Sheets[0].ColumnHeader.DefaultStyle.Font.Bold = true;
FpSpread2.Sheets[0].DefaultStyle.Font.Size = FontUnit.Medium;
FpSpread2.Sheets[0].DefaultStyle.Font.Name = "Book Antiqua";
FpSpread2.Sheets[0].SheetCorner.RowCount = 1;
FpSpread2.Sheets[0].ColumnHeader.Cells[0, 0].Text = "From Point";
FpSpread2.Sheets[0].ColumnHeader.Cells[0, 1].Text = "To Point";
FpSpread2.Sheets[0].ColumnHeader.Cells[0, 2].Text = "Credit Points";
FpSpread2.Sheets[0].ColumnHeader.Cells[0, 3].Text = "Grade";
// FpSpread2.Sheets[0].ColumnHeader.Cells[0, 4].Text = "Priority";
FarPoint.Web.Spread.DoubleCellType intgrcell = new FarPoint.Web.Spread.DoubleCellType();
intgrcell.FormatString = System.Windows.Forms.MaskFormat.ExcludePromptAndLiterals.ToString();
intgrcell.MaximumValue = Convert.ToInt32(100);
intgrcell.MinimumValue = 0;
intgrcell.ErrorMessage = "Enter valid Mark";
FarPoint.Web.Spread.DoubleCellType intgrcell_cp = new FarPoint.Web.Spread.DoubleCellType();
intgrcell_cp.FormatString = System.Windows.Forms.MaskFormat.ExcludePromptAndLiterals.ToString();
intgrcell_cp.MaximumValue = Convert.ToInt32(10);
intgrcell_cp.MinimumValue = 0;
intgrcell_cp.ErrorMessage = "Enter valid Credit Points";
FarPoint.Web.Spread.TextCellType txt = new FarPoint.Web.Spread.TextCellType();
FpSpread2.Sheets[0].Columns[0].CellType = intgrcell;
FpSpread2.Sheets[0].Columns[1].CellType = intgrcell;
FpSpread2.Sheets[0].Columns[2].CellType = intgrcell_cp;
FpSpread2.Sheets[0].Columns[3].CellType = txt;
FpSpread2.Sheets[0].AutoPostBack = false;
string batchyear = ddlbatch.SelectedValue.ToString();
string degree = ddldegree.SelectedItem.Value.ToString();
string depart_code = ddlbranch.SelectedValue.ToString();
string studinfo = "";
studinfo = "select * from Grade_Master where College_Code='" + Session["collegecode"].ToString() + "' and batch_year='" + ddlbatch.SelectedItem.Text.ToString() + "' and degree_code='" + depart_code + "'";
DataSet dsstudinfo = da.select_method_wo_parameter(studinfo, "Text");
if (dsstudinfo.Tables[0].Rows.Count > 0)
{
btnsave.Visible = true;
int sno = 0;
for (int studcount = 0; studcount < dsstudinfo.Tables[0].Rows.Count; studcount++)
{
FpSpread2.Visible = true;
sno++;
FpSpread2.Sheets[0].RowCount = FpSpread2.Sheets[0].RowCount + 1;
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 0].Text = dsstudinfo.Tables[0].Rows[studcount]["Frange"].ToString();
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 1].Text = dsstudinfo.Tables[0].Rows[studcount]["Trange"].ToString();
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 2].Text = dsstudinfo.Tables[0].Rows[studcount]["Credit_Points"].ToString();
FpSpread2.Sheets[0].Cells[FpSpread2.Sheets[0].RowCount - 1, 3].Text = dsstudinfo.Tables[0].Rows[studcount]["Mark_Grade"].ToString();;
}
}
else
{
//clear();
//lblerror.Text = "No Records Found";
//lblerror.Visible = true;
FpSpread2.Visible = true;
btnsave.Visible = true;
Button1.Visible = true;
showdata.Visible = true;
}
string totalrows = FpSpread2.Sheets[0].RowCount.ToString();
FpSpread2.Sheets[0].PageSize = (Convert.ToInt32(totalrows) * 20) + 40;
FpSpread2.Height = 350;
FpSpread2.SaveChanges();
FpSpread2.Visible = true;
btnsave.Visible = true;
Button1.Visible = true;
showdata.Visible = true;
}
catch (Exception ex)
{
lblerror.Text = ex.ToString();
lblerror.Visible = true;
}
}
protected void btnmatch_click(object sender, EventArgs e)
{
try
{
string getnamecode = Convert.ToString(ddlstafflst.SelectedItem.Text);
string staffname = "";
if (getnamecode.Trim() != "Select")
{
staffname = getnamecode.Split('-')[0];
}
if ((FpSpread.Sheets[0].RowCount == 3 && checkvalue() == false) || FpSpread.Sheets[0].RowCount == 0)
{
loadsprcolumns();
}
if (checkstaffcode() == false)
{
lblerr.Visible = true;
lblerr.Text = "Staff Already Exists!";
return;
}
else if (ddlstafflst.SelectedIndex == 0)
{
lblerr.Visible = true;
lblerr.Text = "Please Select Staff!";
return;
}
else if (txt_macid.Text.Trim() == "")
{
lblerr.Visible = true;
lblerr.Text = "Please Enter MachineID!";
return;
}
else if (ddlfingerid.SelectedIndex == 0)
{
lblerr.Visible = true;
lblerr.Text = "Please Select FingerID!";
return;
}
else
{
FarPoint.Web.Spread.TextCellType textcell = new FarPoint.Web.Spread.TextCellType();
FpSpread.Sheets[0].RowCount++;
FpSpread.Sheets[0].Cells[FpSpread.Sheets[0].RowCount - 1, 0].Text = Convert.ToString(FpSpread.Sheets[0].RowCount);
FpSpread.Sheets[0].Cells[FpSpread.Sheets[0].RowCount - 1, 0].HorizontalAlign = HorizontalAlign.Center;
FpSpread.Sheets[0].Cells[FpSpread.Sheets[0].RowCount - 1, 0].Font.Name = "Book Antiqua";
FpSpread.Sheets[0].Cells[FpSpread.Sheets[0].RowCount - 1, 0].Font.Size = FontUnit.Medium;
FpSpread.Sheets[0].Cells[FpSpread.Sheets[0].RowCount - 1, 1].Text = Convert.ToString(ddlstafflst.SelectedValue);
FpSpread.Sheets[0].Cells[FpSpread.Sheets[0].RowCount - 1, 1].HorizontalAlign = HorizontalAlign.Center;
FpSpread.Sheets[0].Cells[FpSpread.Sheets[0].RowCount - 1, 1].Font.Name = "Book Antiqua";
FpSpread.Sheets[0].Cells[FpSpread.Sheets[0].RowCount - 1, 1].Font.Size = FontUnit.Medium;
FpSpread.Sheets[0].Cells[FpSpread.Sheets[0].RowCount - 1, 2].Text = Convert.ToString(staffname);
FpSpread.Sheets[0].Cells[FpSpread.Sheets[0].RowCount - 1, 2].HorizontalAlign = HorizontalAlign.Left;
FpSpread.Sheets[0].Cells[FpSpread.Sheets[0].RowCount - 1, 2].Font.Name = "Book Antiqua";
FpSpread.Sheets[0].Cells[FpSpread.Sheets[0].RowCount - 1, 2].Font.Size = FontUnit.Medium;
FpSpread.Sheets[0].Cells[FpSpread.Sheets[0].RowCount - 1, 3].Text = Convert.ToString(txt_macid.Text);
FpSpread.Sheets[0].Cells[FpSpread.Sheets[0].RowCount - 1, 3].HorizontalAlign = HorizontalAlign.Center;
FpSpread.Sheets[0].Cells[FpSpread.Sheets[0].RowCount - 1, 3].Font.Name = "Book Antiqua";
FpSpread.Sheets[0].Cells[FpSpread.Sheets[0].RowCount - 1, 3].Font.Size = FontUnit.Medium;
FpSpread.Sheets[0].Cells[FpSpread.Sheets[0].RowCount - 1, 4].CellType = textcell;
FpSpread.Sheets[0].Cells[FpSpread.Sheets[0].RowCount - 1, 4].Text = Convert.ToString(ddlfingerid.SelectedValue);
FpSpread.Sheets[0].Cells[FpSpread.Sheets[0].RowCount - 1, 4].HorizontalAlign = HorizontalAlign.Center;
FpSpread.Sheets[0].Cells[FpSpread.Sheets[0].RowCount - 1, 4].Font.Name = "Book Antiqua";
FpSpread.Sheets[0].Cells[FpSpread.Sheets[0].RowCount - 1, 4].Font.Size = FontUnit.Medium;
FpSpread.Sheets[0].PageSize = FpSpread.Sheets[0].RowCount;
FpSpread.Visible = true;
lblerr.Visible = false;
btnsave.Visible = true;
}
}
catch { }
}
protected void btn_go_Click(object sender, EventArgs e)
{
try
{
Printcontrol.Visible = false;
Hashtable DeductionHash = new Hashtable();
int sno = 0;
string query = "";
string deptcode = rs.GetSelectedItemsValueAsString(cbl_dept);
string designation = rs.GetSelectedItemsValueAsString(cbl_desig);
string stafftype = rs.GetSelectedItemsValueAsString(cbl_staffc);
Fpspread1.Sheets[0].Visible = true;
Fpspread1.Sheets[0].RowHeader.Visible = false;
Fpspread1.CommandBar.Visible = false;
Fpspread1.Sheets[0].AutoPostBack = false;
Fpspread1.Sheets[0].RowCount = 0;
Fpspread1.Sheets[0].ColumnHeader.RowCount = 1;
Fpspread1.Sheets[0].ColumnCount = 13;
FarPoint.Web.Spread.StyleInfo darkstyle2 = new FarPoint.Web.Spread.StyleInfo();
darkstyle2.BackColor = ColorTranslator.FromHtml("#0CA6CA");
darkstyle2.ForeColor = Color.Black;
darkstyle2.HorizontalAlign = HorizontalAlign.Center;
Fpspread1.ActiveSheetView.ColumnHeader.DefaultStyle = darkstyle2;
FarPoint.Web.Spread.TextCellType txt = new FarPoint.Web.Spread.TextCellType();
// string deductiondates = Convert.ToString(txt_deduction.Text);
//string[] deductiondates = txt_deduction.Text.Split('/');
//string month = Convert.ToString(deductiondates[1].ToString());
string frommonth = "";
string tomonth = "";
string fromyear = "";
string toyear = "";
frommonth = Convert.ToString(ddl_frommonth.SelectedItem.Value);
tomonth = Convert.ToString(ddl_tomonth.SelectedItem.Value);
fromyear = Convert.ToString(ddl_fromyear.SelectedItem.Value);
toyear = Convert.ToString(ddl_toyear.SelectedItem.Value);
query = "select s.staff_code,s.staff_name,appl_id,h.dept_name,d.desig_name,pangirnumber,m.netadd,convert(varchar(max),m.deductions)deductions,m.paymonth,m.payyear from staffmaster s,hrdept_master h,desig_master d,stafftrans st,staff_appl_master sm,monthlypay m where m.staff_code=s.staff_code and m.staff_code=st.staff_code and s.staff_code=st.staff_code and st.Dept_Code = h.Dept_Code and d.desig_code=st.desig_code and s.college_code = h.college_code and s.college_code = d.collegecode and s.appl_no=sm.appl_no and h.dept_code in('" + deptcode + "') and s.college_code='" + ddlcollege.SelectedItem.Value + "' and resign = 0 and settled = 0 and ISNULL(Discontinue,'0')='0' and st.latestrec=1 and CAST(CONVERT(varchar(20),m.PayMonth)+'/01/'+CONVERT(varchar(20),m.PayYear) as Datetime) between CAST(CONVERT(varchar(20),'" + frommonth + "')+'/01/'+CONVERT(varchar(20),'" + fromyear + "') as Datetime) and CAST(CONVERT(varchar(20),'" + tomonth + "')+'/01/'+CONVERT(varchar(20),'" + toyear + "') as Datetime) group by m.payyear,m.paymonth, LEN(s.staff_code),s.staff_code,s.staff_name,appl_id,h.dept_name,d.desig_name,pangirnumber,m.netadd,convert(varchar(max),m.deductions)order by year(m.payyear),month(m.paymonth)";
ds.Clear();
ds = d2.select_method_wo_parameter(query, "text");
DataSet data = new DataSet();
string collegecode = ddlcollege.SelectedItem.Value;
string query1 = "select MasterCriteria1,MasterValue from CO_MasterValues where MasterCriteria='Quarterly Report Date' and collegecode='" + collegecode + "'";
query1 += " select MasterCriteria1,MasterCriteriaValue1,MasterCriteriaValue2,MasterValue from CO_MasterValues where MasterCriteria='Quarterly Report DepositDate' and collegecode='" + collegecode + "'";
data = d2.select_method_wo_parameter(query1, "text");
if (ds.Tables[0].Rows.Count > 0)
{
ermsg.Visible = false;
Fpspread1.Visible = true;
Fpspread1.ActiveSheetView.ColumnHeader.DefaultStyle.Font.Size = FontUnit.Medium;
Fpspread1.ActiveSheetView.ColumnHeader.DefaultStyle.Font.Bold = true;
Fpspread1.ActiveSheetView.ColumnHeader.DefaultStyle.Font.Name = "Book Antiqua";
//Fpspread1.Sheets[0].ColumnHeader.Columns[0].Label = "Month";
Fpspread1.Sheets[0].ColumnHeader.Columns[0].Label = "S.No";
Fpspread1.Sheets[0].ColumnHeader.Columns[1].Label = "Employee No";
Fpspread1.Sheets[0].ColumnHeader.Columns[2].Label = "PAN No";
Fpspread1.Sheets[0].ColumnHeader.Columns[3].Label = "Name of the Employee";
Fpspread1.Sheets[0].ColumnHeader.Columns[4].Label = "Date of the payment/ credited";
Fpspread1.Sheets[0].ColumnHeader.Columns[5].Label = "Taxable amount on which tax Deducted";
Fpspread1.Sheets[0].ColumnHeader.Columns[6].Label = "TDS";
Fpspread1.Sheets[0].ColumnHeader.Columns[7].Label = "Education CESS";
Fpspread1.Sheets[0].ColumnHeader.Columns[8].Label = "Total Tax Deducted";
Fpspread1.Sheets[0].ColumnHeader.Columns[9].Label = "Total Tax Deposited";
Fpspread1.Sheets[0].ColumnHeader.Columns[10].Label = "Date of Deduction";
Fpspread1.Sheets[0].ColumnHeader.Columns[11].Label = "Date of Deposit";
Fpspread1.Sheets[0].ColumnHeader.Columns[12].Label = "CHALLAN No";
// Fpspread1.Sheets[0].Columns[0].Width = 100;
Fpspread1.Sheets[0].Columns[0].Width = 80;
Fpspread1.Sheets[0].Columns[1].Width = 100;
Fpspread1.Sheets[0].Columns[2].Width = 150;
Fpspread1.Sheets[0].Columns[3].Width = 250;
Fpspread1.Sheets[0].Columns[0].Locked = true;
Fpspread1.Sheets[0].Columns[1].Locked = true;
Fpspread1.Sheets[0].Columns[2].Locked = true;
Fpspread1.Sheets[0].Columns[3].Locked = true;
Fpspread1.Sheets[0].Columns[4].Locked = true;
Fpspread1.Sheets[0].Columns[5].Locked = true;
Fpspread1.Sheets[0].Columns[6].Locked = true;
Fpspread1.Sheets[0].Columns[7].Locked = true;
Fpspread1.Sheets[0].Columns[8].Locked = true;
Fpspread1.Sheets[0].Columns[9].Locked = true;
Fpspread1.Sheets[0].Columns[10].Locked = true;
Fpspread1.Sheets[0].Columns[11].Locked = true;
Fpspread1.Sheets[0].Columns[12].Locked = true;
ArrayList arrPayMonthYear = new ArrayList();
string Amt = string.Empty;
string preMon = string.Empty;
for (int rolcount = 0; rolcount < ds.Tables[0].Rows.Count; rolcount++)
{
string paymonth = ds.Tables[0].Rows[rolcount]["paymonth"].ToString();
string payYear = ds.Tables[0].Rows[rolcount]["payyear"].ToString();
int months = Convert.ToInt32(paymonth);
string monthyear = Convert.ToString(months) + "/" + payYear;
if (monthyear != preMon)//barath 17.01.18
{
sno = 0;
}
preMon = monthyear;
paymonth = getmonth(months);
string datemon = string.Empty;
string deductdate = string.Empty;
//int year = Convert.ToInt32(payYear);
DataView dv = new DataView();
data.Tables[0].DefaultView.RowFilter = " MasterValue='" + monthyear + "'";
dv = data.Tables[0].DefaultView;
if (dv.Count > 0)
{
datemon = Convert.ToString(dv[0]["MasterCriteria1"]);
deductdate = datemon + "/" + monthyear;
}
else
{
deductdate = string.Empty;
}
if (!arrPayMonthYear.Contains(Convert.ToString(paymonth + "@" + payYear).Trim().ToLower()))
{
if (arrPayMonthYear.Count > 0)
{
Fpspread1.Sheets[0].RowCount++;
Fpspread1.Sheets[0].Rows[Fpspread1.Sheets[0].RowCount - 1].BackColor = Color.Bisque;
Fpspread1.Sheets[0].Rows[Fpspread1.Sheets[0].RowCount - 1].ForeColor = Color.DarkRed;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 4].Text = "Total";
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 4].HorizontalAlign = HorizontalAlign.Center;
for (int Totalcol = 5; Totalcol < 10; Totalcol++)
{
Amt = string.Empty;
if (DeductionHash.ContainsKey(Totalcol))
{
Amt = Convert.ToString(DeductionHash[Totalcol]);
}
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, Totalcol].Text = Amt;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, Totalcol].HorizontalAlign = HorizontalAlign.Right;
}
DeductionHash.Clear();
}
Fpspread1.Sheets[0].RowCount++;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 0].Text = Convert.ToString(paymonth) + "-" + payYear;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 0].HorizontalAlign = HorizontalAlign.Left;
Fpspread1.Sheets[0].SpanModel.Add(Fpspread1.Sheets[0].RowCount - 1, 0, 1, 12);
arrPayMonthYear.Add(Convert.ToString(paymonth + "@" + payYear).Trim().ToLower());
}
sno++;
string name = ds.Tables[0].Rows[rolcount]["staff_name"].ToString();
string code = ds.Tables[0].Rows[rolcount]["staff_code"].ToString();
string panno = ds.Tables[0].Rows[rolcount]["pangirnumber"].ToString();
double gross = 0;
double.TryParse(Convert.ToString(ds.Tables[0].Rows[rolcount]["netadd"]), out gross);
string tds = ds.Tables[0].Rows[rolcount]["deductions"].ToString();
string incTax = "";
string deductions = Convert.ToString(ds.Tables[0].Rows[rolcount]["deductions"]);
string[] deductionlist = deductions.Split('\\');
for (int k = 0; k < deductionlist.GetUpperBound(0); k++)
{
string getactal = deductionlist[k];
if (getactal.Trim() != "" && getactal != null)
{
string[] actallspv = getactal.Split(';');
if (actallspv.GetUpperBound(0) >= 3)
{
if (actallspv[0].ToString().Trim().ToLower() == "inc tax" || actallspv[0].ToString().Trim().ToLower() == "income tax")
{
string de = actallspv[0];
string de1 = actallspv[1];
string de2 = actallspv[2];
string[] dedspl = de2.Split('-');
if (dedspl.Length == 2)
{
if (de1.Trim().ToUpper() == "PERCENT")
{
incTax = Convert.ToString(dedspl[1]);
}
else if (de1.Trim().ToUpper() == "SLAB")
{
incTax = Convert.ToString(dedspl[1]);
}
else
{
incTax = Convert.ToString(dedspl[0]);
}
}
else
{
incTax = Convert.ToString(actallspv[3]);
}
//double InctaxAmt = 0;
//double.TryParse(incTax, out InctaxAmt);
// TotalTaxAmt = InctaxAmt;
}
}
}
}
string date = "";
double educationcess = 0;
double tax = 0;
double.TryParse(incTax, out tax);
educationcess = Math.Round(tax / 100 * 3);
double totaltaxdeduct = Math.Round(tax + educationcess);
//string challan = "";
Fpspread1.Sheets[0].RowCount = Fpspread1.Sheets[0].RowCount + 1;
Fpspread1.Sheets[0].Rows[Fpspread1.Sheets[0].RowCount - 1].Font.Bold = false;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 0].Text = Convert.ToString(sno);
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 0].HorizontalAlign = HorizontalAlign.Center;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 1].Text = code;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 1].HorizontalAlign = HorizontalAlign.Left;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 2].Text = panno;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 2].HorizontalAlign = HorizontalAlign.Left;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 3].Text = name;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 3].HorizontalAlign = HorizontalAlign.Left;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 4].Text = date;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 4].HorizontalAlign = HorizontalAlign.Left;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 5].Text = Convert.ToString(gross);
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 5].HorizontalAlign = HorizontalAlign.Right;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 6].Text = incTax;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 6].HorizontalAlign = HorizontalAlign.Right;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 7].Text = Convert.ToString(educationcess);
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 7].HorizontalAlign = HorizontalAlign.Right;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 8].Text = Convert.ToString(totaltaxdeduct);
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 8].HorizontalAlign = HorizontalAlign.Right;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 9].Text = Convert.ToString(totaltaxdeduct);
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 9].HorizontalAlign = HorizontalAlign.Right;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 10].CellType = txt;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 10].Text = deductdate;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 10].HorizontalAlign = HorizontalAlign.Left;
//barath 18.12.17
for (int Totalcol = 5; Totalcol < 10; Totalcol++)
{
double PrevAmt = 0;
double Amount = 0;
double.TryParse(Convert.ToString(Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, Totalcol].Text), out Amount);
if (!DeductionHash.ContainsKey(Totalcol))
{
DeductionHash.Add(Totalcol, Amount);
}
else
{
PrevAmt = 0;
double.TryParse(Convert.ToString(DeductionHash[Totalcol]), out PrevAmt);
Amount += PrevAmt;
DeductionHash[Totalcol] = Amount;
}
}
string depositDate = string.Empty;
string challanNo = string.Empty;
string monyears = string.Empty;
DataView dv1 = new DataView();
data.Tables[1].DefaultView.RowFilter = " MasterValue='" + monthyear + "'";
dv1 = data.Tables[1].DefaultView;
if (dv1.Count > 0)
{
monyears = Convert.ToString(dv1[0]["MasterCriteriavalue2"]);
challanNo = Convert.ToString(dv1[0]["MasterCriteriavalue1"]);
datemon = Convert.ToString(dv1[0]["MasterCriteria1"]);
// depositDate = datemon + "/" + monthyear;
depositDate = datemon + "/" + monyears;
}
else
{
depositDate = string.Empty;
challanNo = string.Empty;
}
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 11].CellType = txt;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 11].Text = depositDate;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 11].HorizontalAlign = HorizontalAlign.Left;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 12].CellType = txt;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 12].Text = challanNo;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 12].HorizontalAlign = HorizontalAlign.Left;
}
//barath 18.12.17
Amt = string.Empty;
if (DeductionHash.Count > 0)
{
Fpspread1.Sheets[0].RowCount++;
Fpspread1.Sheets[0].Rows[Fpspread1.Sheets[0].RowCount - 1].BackColor = Color.Bisque;
Fpspread1.Sheets[0].Rows[Fpspread1.Sheets[0].RowCount - 1].ForeColor = Color.DarkRed;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 4].Text = "Total";
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, 4].HorizontalAlign = HorizontalAlign.Center;
for (int Totalcol = 5; Totalcol < 10; Totalcol++)
{
Amt = string.Empty;
if (DeductionHash.ContainsKey(Totalcol))
{
Amt = Convert.ToString(DeductionHash[Totalcol]);
}
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, Totalcol].Text = Amt;
Fpspread1.Sheets[0].Cells[Fpspread1.Sheets[0].RowCount - 1, Totalcol].HorizontalAlign = HorizontalAlign.Right;
}
}
Fpspread1.Sheets[0].PageSize = Fpspread1.Sheets[0].RowCount;
Fpspread1.Width = 1000;
Fpspread1.Height = 500;
rptprint.Visible = true;
}
else
{
if ((txt_dept.Text == "--Select--") || (txt_desig.Text == "--Select--") || (txt_staffc.Text == "--Select--"))
{
ermsg.Visible = true;
ermsg.Text = "Please Select Atleast One Item";
}
else
{
Fpspread1.Visible = false;
ermsg.Visible = true;
ermsg.Text = "No Records Found";
rptprint.Visible = false;
//btnprintmaster.Visible = false;
}
}
}
catch (Exception ex)
{
alertmessage.Visible = true;
lbl_alerterror.Visible = true;
lbl_alerterror.Text = ex.ToString();
}
}
protected void btn_go_OnClick(object sender, EventArgs e)
{
try
{
#region Order by
string orderby_Setting = d2.GetFunction("select value from master_Settings where settings='order_by'");
string strorder = ",len(r.roll_no)";
if (orderby_Setting == "0")
{
strorder = ",len(r.roll_no)";
}
else if (orderby_Setting == "1")
{
strorder = ",len(r.Reg_No)";
}
else if (orderby_Setting == "2")
{
strorder = ",r.Stud_Name";
}
else if (orderby_Setting == "0,1,2")
{
strorder = ",len(r.roll_no),len(r.Reg_No),r.stud_name";
}
else if (orderby_Setting == "0,1")
{
strorder = ",len(r.roll_no),len(r.Reg_No)";
}
else if (orderby_Setting == "1,2")
{
strorder = ",len(r.Reg_No),r.Stud_Name";
}
else if (orderby_Setting == "0,2")
{
strorder = ",len(r.roll_no),r.Stud_Name";
}
#endregion
//FarPoint.Web.Spread.TextCellType txt;
FarPoint.Web.Spread.TextCellType txt = new FarPoint.Web.Spread.TextCellType();
string degreecode = rs.GetSelectedItemsValueAsString(cbl_degree);
string deptcode = rs.GetSelectedItemsValueAsString(cbl_branch);
string batchyear = ddl_batch.SelectedItem.Text;
//string sem = rs.GetSelectedItemsValueAsString(cbl_sem);
DateTime FromDate = new DateTime();
DateTime ToDate = new DateTime();
string[] fromdate = txtfromdate.Text.Split('/');
string[] todate = txttodate.Text.Split('/');
FromDate = Convert.ToDateTime(fromdate[1] + "/" + fromdate[0] + "/" + fromdate[2]);
ToDate = Convert.ToDateTime(todate[1] + "/" + todate[0] + "/" + todate[2]);
if (ddlCatogery.SelectedValue.ToString() == "1")
{
q1 = "select distinct R.Stud_Name ,r.Roll_no,R.Reg_No, Convert(varchar(10),R.batch_year)+' - '+ Convert(varchar(10),c.Course_name)+' - '+de.Acronym+' - '+Convert(varchar(10),R.current_semester) as Degreedet,Convert(varchar(20),re.readm_date,103) as ReadmittedDate ,Convert(varchar(10), re.newbatch_year) +' - ' + Convert(varchar(10),re.Readm_Semester) +' SEM' as ReadmittedSemester from Readmission re,REgistration R,Degree de,Department dep,Course c where R.App_No=re.App_No and c.Course_id=de.Course_id and de.college_code=c.college_code and dep.Dept_code=de.Dept_code and dep.college_code=de.college_code and R.college_code=dep.college_code and R.degree_code=de.Degree_code and c.Course_id IN ('" + degreecode + "') and R.batch_year in ('" + batchyear + "') and dep.dept_code in('" + deptcode + "') and re.Dis_Date between '" + FromDate.ToString("MM/dd/yyyy") + "' and '" + ToDate.ToString("MM/dd/yyyy") + "' and REadmitreason='2' "; //REadmitreason 1 for Prolong Absent Students
}
else if (ddlCatogery.SelectedValue.ToString() == "2")
{
q1 = "select distinct R.Stud_Name ,r.Roll_no,R.Reg_No, Convert(varchar(10),R.batch_year)+' - '+ Convert(varchar(10),c.Course_name)+' - '+de.Acronym+' - '+Convert(varchar(10),R.current_semester) as Degreedet,Convert(varchar(20),re.readm_date,103) as ReadmittedDate ,Convert(varchar(10), re.newbatch_year) +' - ' + Convert(varchar(10),re.Readm_Semester) +' SEM' as ReadmittedSemester from Readmission re,REgistration R,Degree de,Department dep,Course c where R.App_No=re.App_No and c.Course_id=de.Course_id and de.college_code=c.college_code and dep.Dept_code=de.Dept_code and dep.college_code=de.college_code and R.college_code=dep.college_code and R.degree_code=de.Degree_code and c.Course_id IN ('" + degreecode + "') and R.batch_year in ('" + batchyear + "') and dep.dept_code in('" + deptcode + "') and re.Dis_Date between '" + FromDate.ToString("MM/dd/yyyy") + "' and '" + ToDate.ToString("MM/dd/yyyy") + "' and REadmitreason='1' "; //REadmitreason 1 for Discontinued Students
}
//q1 += " order by " + strorder.TrimStart(',') + " ";
if (deptcode.Trim() != "" && degreecode.Trim() != "") //&& sem.Trim() != ""
{
ds.Clear();
ds = d2.select_method_wo_parameter(q1, "Text");
if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
{
//rs.Fpreadheaderbindmethod("S.No-50/Student Name-220/Roll No-130/Reg No-150/Degree Details-180/Readmitted Date-120/Semester-140/", FpSpread1, "FALSE");
rs.Fpreadheaderbindmethod("S.No/Student Name/Roll No/Reg No/Degree Details/Readmitted Date/Semester/", FpSpread1, "TRUE");
FpSpread1.Sheets[0].Rows.Count++;
foreach (DataRow dr in ds.Tables[0].Rows)
{
FpSpread1.Sheets[0].Rows.Count++;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 0].Text = Convert.ToString(FpSpread1.Sheets[0].Rows.Count - 1);
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 1].Text = Convert.ToString(FpSpread1.Sheets[0].Rows.Count - 1);
// FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 2].Tag = Convert.ToString(dr["degree_code"]);
if (Convert.ToString(Session["Rollflag"]) == "1")
{
FpSpread1.Sheets[0].Columns[3].Visible = true;
}
if (Convert.ToString(Session["Regflag"]) == "1")
{
FpSpread1.Sheets[0].Columns[4].Visible = true;
}
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 3].CellType = txt;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 4].CellType = txt;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 2].CellType = txt;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 1].Text = Convert.ToString(dr["Stud_Name"]);
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 2].Text = Convert.ToString(dr["Roll_no"]);
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 3].Text = Convert.ToString(dr["Reg_No"]);
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 4].Text = Convert.ToString(dr["Degreedet"]);
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 5].Text = Convert.ToString(dr["ReadmittedDate"]);
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 6].Text = Convert.ToString(dr["ReadmittedSemester"]);
FarPoint.Web.Spread.TextCellType txtclType = new FarPoint.Web.Spread.TextCellType();
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 5].CellType = txtclType;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 0].Locked = true;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 1].Locked = true;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 2].Locked = true;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 3].Locked = true;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 4].Locked = true;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 5].Locked = true;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 6].Locked = true;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 0].HorizontalAlign = HorizontalAlign.Center;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 0].Font.Size = FontUnit.Medium;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 0].Font.Name = "Book Antiqua";
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 1].HorizontalAlign = HorizontalAlign.Left;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 1].Font.Size = FontUnit.Medium;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 1].Font.Name = "Book Antiqua";
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 2].HorizontalAlign = HorizontalAlign.Left;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 2].Font.Size = FontUnit.Medium;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 2].Font.Name = "Book Antiqua";
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 3].HorizontalAlign = HorizontalAlign.Left;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 3].Font.Size = FontUnit.Medium;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 3].Font.Name = "Book Antiqua";
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 4].HorizontalAlign = HorizontalAlign.Left;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 4].Font.Size = FontUnit.Medium;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 4].Font.Name = "Book Antiqua";
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 5].HorizontalAlign = HorizontalAlign.Left;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 5].Font.Size = FontUnit.Medium;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 5].Font.Name = "Book Antiqua";
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 6].HorizontalAlign = HorizontalAlign.Left;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 6].Font.Size = FontUnit.Medium;
FpSpread1.Sheets[0].Cells[FpSpread1.Sheets[0].RowCount - 1, 6].Font.Name = "Book Antiqua";
FpSpread1.Sheets[0].Columns[7].Visible = false;
}
FpSpread1.Sheets[0].PageSize = FpSpread1.Sheets[0].RowCount;
FpSpread1.Visible = true;
FpSpread1.SaveChanges();
lbl_error.Visible = false;
print.Visible = true;
}
else
{
FpSpread1.Visible = false;
lbl_error.Visible = true;
lbl_error.Text = "No Record Founds";
}
}
else
{
FpSpread1.Visible = false;
lbl_error.Visible = true;
lbl_error.Text = "Please Select All Fields";
}
}
catch { }
}
private void loadCommonSpread(DataTable dtSpreadData)
{
spreadReport.Sheets[0].RowCount = dtSpreadData.Rows.Count;
spreadReport.Sheets[0].ColumnCount = 0;
spreadReport.Sheets[0].ColumnHeader.RowCount = 1;
spreadReport.CommandBar.Visible = false;
spreadReport.Sheets[0].ColumnCount = 6;
spreadReport.Sheets[0].RowHeader.Visible = false;
spreadReport.Sheets[0].AutoPostBack = false;
FarPoint.Web.Spread.StyleInfo darkstyle = new FarPoint.Web.Spread.StyleInfo();
darkstyle.BackColor = ColorTranslator.FromHtml("#0CA6CA");
darkstyle.ForeColor = Color.Black;
spreadReport.ActiveSheetView.ColumnHeader.DefaultStyle = darkstyle;
FarPoint.Web.Spread.TextCellType txt = new FarPoint.Web.Spread.TextCellType();
spreadReport.Sheets[0].ColumnHeader.Cells[0, 0].Text = "S.No";
spreadReport.Sheets[0].ColumnHeader.Cells[0, 0].Font.Bold = true;
spreadReport.Sheets[0].ColumnHeader.Cells[0, 0].Font.Name = "Book Antiqua";
spreadReport.Sheets[0].ColumnHeader.Cells[0, 0].Font.Size = FontUnit.Medium;
spreadReport.Sheets[0].ColumnHeader.Cells[0, 0].HorizontalAlign = HorizontalAlign.Center;
spreadReport.Sheets[0].Columns[0].Locked = true;
spreadReport.Columns[0].Width = 50;
spreadReport.Sheets[0].ColumnHeader.Cells[0, 1].Text = "Reg No";
spreadReport.Sheets[0].ColumnHeader.Cells[0, 1].Font.Bold = true;
spreadReport.Sheets[0].ColumnHeader.Cells[0, 1].Font.Name = "Book Antiqua";
spreadReport.Sheets[0].ColumnHeader.Cells[0, 1].Font.Size = FontUnit.Medium;
spreadReport.Sheets[0].ColumnHeader.Cells[0, 1].HorizontalAlign = HorizontalAlign.Center;
spreadReport.Sheets[0].Columns[1].Locked = true;
spreadReport.Columns[1].Width = 150;
spreadReport.Columns[1].CellType = txt;
spreadReport.Sheets[0].ColumnHeader.Cells[0, 2].Text = "Roll No";
spreadReport.Sheets[0].ColumnHeader.Cells[0, 2].Font.Bold = true;
spreadReport.Sheets[0].ColumnHeader.Cells[0, 2].Font.Name = "Book Antiqua";
spreadReport.Sheets[0].ColumnHeader.Cells[0, 2].Font.Size = FontUnit.Medium;
spreadReport.Sheets[0].ColumnHeader.Cells[0, 2].HorizontalAlign = HorizontalAlign.Center;
spreadReport.Sheets[0].Columns[2].Locked = true;
spreadReport.Columns[2].Width = 100;
spreadReport.Columns[2].Visible = false;
spreadReport.Sheets[0].ColumnHeader.Cells[0, 3].Text = "Dummy No";
spreadReport.Sheets[0].ColumnHeader.Cells[0, 3].Font.Bold = true;
spreadReport.Sheets[0].ColumnHeader.Cells[0, 3].Font.Name = "Book Antiqua";
spreadReport.Sheets[0].ColumnHeader.Cells[0, 3].Font.Size = FontUnit.Medium;
spreadReport.Sheets[0].ColumnHeader.Cells[0, 3].HorizontalAlign = HorizontalAlign.Center;
spreadReport.Sheets[0].Columns[3].Locked = true;
spreadReport.Columns[3].Width = 100;
spreadReport.Sheets[0].ColumnHeader.Cells[0, 4].Text = "Name";
spreadReport.Sheets[0].ColumnHeader.Cells[0, 4].Font.Bold = true;
spreadReport.Sheets[0].ColumnHeader.Cells[0, 4].Font.Name = "Book Antiqua";
spreadReport.Sheets[0].ColumnHeader.Cells[0, 4].Font.Size = FontUnit.Medium;
spreadReport.Sheets[0].ColumnHeader.Cells[0, 4].HorizontalAlign = HorizontalAlign.Center;
spreadReport.Sheets[0].Columns[4].Locked = true;
spreadReport.Columns[4].Width = 250;
spreadReport.Sheets[0].ColumnHeader.Cells[0, 5].Text = "Branch";
spreadReport.Sheets[0].ColumnHeader.Cells[0, 5].Font.Bold = true;
spreadReport.Sheets[0].ColumnHeader.Cells[0, 5].Font.Name = "Book Antiqua";
spreadReport.Sheets[0].ColumnHeader.Cells[0, 5].Font.Size = FontUnit.Medium;
spreadReport.Sheets[0].ColumnHeader.Cells[0, 5].HorizontalAlign = HorizontalAlign.Center;
spreadReport.Sheets[0].Columns[5].Locked = true;
spreadReport.Columns[5].Width = 250;
spreadReport.Sheets[0].Columns[0].HorizontalAlign = HorizontalAlign.Center;
spreadReport.Sheets[0].Columns[0].Font.Size = FontUnit.Medium;
spreadReport.Sheets[0].Columns[0].Font.Name = "Book Antiqua";
spreadReport.Sheets[0].Columns[1].HorizontalAlign = HorizontalAlign.Center;
spreadReport.Sheets[0].Columns[1].Font.Size = FontUnit.Medium;
spreadReport.Sheets[0].Columns[1].Font.Name = "Book Antiqua";
spreadReport.Sheets[0].Columns[2].HorizontalAlign = HorizontalAlign.Center;
spreadReport.Sheets[0].Columns[2].Font.Size = FontUnit.Medium;
spreadReport.Sheets[0].Columns[2].Font.Name = "Book Antiqua";
spreadReport.Sheets[0].Columns[3].HorizontalAlign = HorizontalAlign.Center;
spreadReport.Sheets[0].Columns[3].Font.Size = FontUnit.Medium;
spreadReport.Sheets[0].Columns[3].Font.Name = "Book Antiqua";
spreadReport.Sheets[0].Columns[4].HorizontalAlign = HorizontalAlign.Left;
spreadReport.Sheets[0].Columns[4].Font.Size = FontUnit.Medium;
spreadReport.Sheets[0].Columns[4].Font.Name = "Book Antiqua";
spreadReport.Sheets[0].Columns[5].HorizontalAlign = HorizontalAlign.Left;
spreadReport.Sheets[0].Columns[5].VerticalAlign = VerticalAlign.Middle;
spreadReport.Sheets[0].Columns[5].Font.Size = FontUnit.Medium;
spreadReport.Sheets[0].Columns[5].Font.Name = "Book Antiqua";
spreadReport.Sheets[0].SetColumnMerge(5, FarPoint.Web.Spread.Model.MergePolicy.Always);
for (int rowI = 0; rowI < dtSpreadData.Rows.Count; rowI++)
{
spreadReport.Sheets[0].Cells[rowI, 0].Text = (rowI + 1).ToString();
spreadReport.Sheets[0].Cells[rowI, 1].Text = Convert.ToString(dtSpreadData.Rows[rowI]["reg_no"]);
spreadReport.Sheets[0].Cells[rowI, 1].Tag = Convert.ToString(dtSpreadData.Rows[rowI]["college_code"]);
spreadReport.Sheets[0].Cells[rowI, 2].Text = Convert.ToString(dtSpreadData.Rows[rowI]["roll_no"]);
spreadReport.Sheets[0].Cells[rowI, 3].Text = Convert.ToString(dtSpreadData.Rows[rowI]["dummy_no"]);
spreadReport.Sheets[0].Cells[rowI, 4].Text = Convert.ToString(dtSpreadData.Rows[rowI]["stud_name"]);
spreadReport.Sheets[0].Cells[rowI, 5].Text = Convert.ToString(dtSpreadData.Rows[rowI]["branch"]);
spreadReport.Sheets[0].Cells[rowI, 5].Tag = Convert.ToString(dtSpreadData.Rows[rowI]["degree_code"]);
}
spreadReport.Sheets[0].PageSize = spreadReport.Sheets[0].RowCount;
spreadReport.SaveChanges();
spreadReport.Visible = true;
rptprint.Visible = true;
}
