private void Show(DataTable dt)
{
if (dt.Rows.Count == 0)
{
lbldidnt.Visible = true;
}
else
{
lbldidnt.Visible = false;
GrdProducts.DataSource = dt;
GrdProducts.DataBind();
for (int i = 0; i < GrdProducts.Rows.Count; i++)
{
GrdProducts.Rows[i].Cells[5].Text = s.GetProviderFromId(int.Parse(GrdProducts.Rows[i].Cells[5].Text));
if (GrdProducts.Rows[i].Cells[7].Text.Equals("1"))
{
GrdProducts.Rows[i].Cells[7].Text = "YES";
}
else
{
GrdProducts.Rows[i].Cells[7].Text = "No";
}
}
}
}
public void Bind()
{
DataSet ds = new DataSet();
DataTable dt = new DataTable();
DataView dv = new DataView();
ds = objProducts.GetAllProducts(Request.QueryString["SearchFor"].ToString(), Request.QueryString["SearchText"].ToString());
ddlSearch.SelectedValue = Request.QueryString["SearchFor"].ToString();
txtSearch.Text = Request.QueryString["SearchText"].ToString();
dtOrder = ds.Tables[0];
ViewState["MaxPosition"] = ds.Tables[1].Rows[0]["Position"].ToString();
if (ds.Tables[0].Rows.Count > 0)
{
GrdProducts.Visible = true;
dt = ds.Tables[0];
dv = dt.DefaultView;
if ((SortExpression != string.Empty) && (SortDirection != string.Empty))
{
dv.Sort = SortExpression + " " + SortDirection;
}
GrdProducts.DataSource = dv;
GrdProducts.DataBind();
CheckAll();
check();
Utility.Setserial(GrdProducts, "srno");
btnDelete.Visible = true;
btnStatus.Visible = true;
divsearch.Visible = true;
btnFlea.Visible = true;
btnHome.Visible = true;
btnNew.Visible = true;
btnPreview.Visible = true;
}
else
{
if ((Convert.ToInt32(ddlSearch.SelectedIndex) > 0) && (txtSearch.Text != ""))
{
txtSearch.Text = "";
ddlSearch.SelectedIndex = 0;
lnkNorec.Visible = true;
btnFlea.Visible = false;
btnHome.Visible = false;
btnNew.Visible = false;
btnPreview.Visible = false;
// btnImport.Visible = false;
//lblMsg.Visible = true;
ErrMessage("Sorry, No records found.");
}
divsearch.Visible = false;
GrdProducts.Visible = false;
btnDelete.Visible = false;
btnStatus.Visible = false;
}
}
protected void grdReports_RowCommand(object sender, GridViewCommandEventArgs e)
{
try
{
int rowIndex = Convert.ToInt32(e.CommandArgument);
GridViewRow row = grdReports.Rows[rowIndex];
string ReceiptNo = row.Cells[1].Text;
Report.Columns.Add("sno");
Report.Columns.Add("Month");
Report.Columns.Add("GrossPay");
Report.Columns.Add("NetPay");
lblmsg.Text = "";
int i = 1;
string year = slct_year.SelectedItem.Value;
DBManager vdm = new DBManager();
cmd = new SqlCommand("SELECT branchid, branchname, address, phone, emailid, statename, fromdate, todate, night_allowance, branchtype, company_code, branchcode, sapcode, estdyear, incharge, lat, long FROM branchmaster WHERE (branchname = @branchname)");
cmd.Parameters.Add("@branchname", ReceiptNo);
DataTable dtbranchid = vdm.SelectQuery(cmd).Tables[0];
if (dtbranchid.Rows.Count > 0)
{
cmd = new SqlCommand("SELECT SUM(monthlysalarystatement.gross) AS gross, SUM(monthlysalarystatement.netpay) AS Netpay, monthlysalarystatement.month FROM monthlysalarystatement INNER JOIN branchmaster ON monthlysalarystatement.branchid = branchmaster.branchid WHERE (monthlysalarystatement.year = @year) AND (monthlysalarystatement.branchid = @branch) GROUP BY branchmaster.branchname, monthlysalarystatement.month");
cmd.Parameters.Add("@branch", dtbranchid.Rows[0]["branchid"].ToString());
cmd.Parameters.Add("@year", year);
DataTable dtemployee = vdm.SelectQuery(cmd).Tables[0];
foreach (DataRow dr in dtemployee.Rows)
{
DataRow newrow = Report.NewRow();
newrow["sno"] = i++.ToString();
//newrow["Month"] = dr["month"].ToString();
int m = Convert.ToInt32(dr["month"].ToString());
string month = System.Globalization.CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(m);
newrow["Month"] = month;
newrow["GrossPay"] = dr["gross"].ToString();
newrow["NetPay"] = dr["Netpay"].ToString();
Report.Rows.Add(newrow);
}
GrdProducts.DataSource = Report;
GrdProducts.DataBind();
hidepanel.Visible = true;
}
ScriptManager.RegisterStartupScript(Page, GetType(), "JsStatus", "PopupOpen();", true);
}
catch (Exception ex)
{
lblmsg.Text = ex.Message;
hidepanel.Visible = false;
}
}
protected void grdReports_RowCommand(object sender, GridViewCommandEventArgs e)
{
try
{
int rowIndex = Convert.ToInt32(e.CommandArgument);
GridViewRow row = grdReports.Rows[rowIndex];
string ReceiptNo = row.Cells[2].Text;
Report.Columns.Add("sno");
Report.Columns.Add("Product Id");
Report.Columns.Add("Product Name");
//Report.Columns.Add("Outward Date");
Report.Columns.Add("Quantity");
Report.Columns.Add("Price");
Report.Columns.Add("Value");
lblmsg.Text = "";
SalesDBManager SalesDB = new SalesDBManager();
DateTime fromdate = DateTime.Now;
DateTime todate = DateTime.Now;
string[] datestrig = dtp_FromDate.Text.Split(' ');
if (datestrig.Length > 1)
{
if (datestrig[0].Split('-').Length > 0)
{
string[] dates = datestrig[0].Split('-');
string[] times = datestrig[1].Split(':');
fromdate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
datestrig = dtp_Todate.Text.Split(' ');
if (datestrig.Length > 1)
{
if (datestrig[0].Split('-').Length > 0)
{
string[] dates = datestrig[0].Split('-');
string[] times = datestrig[1].Split(':');
todate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
lblfrom_date.Text = fromdate.ToString("dd/MM/yyyy");
lblto_date.Text = todate.ToString("dd/MM/yyyy");
string branchid = Session["Po_BranchID"].ToString();
cmd = new SqlCommand("SELECT productid, productname,price,sectionid FROM productmaster");
DataTable dtproducts = SalesDB.SelectQuery(cmd).Tables[0];
if (dtproducts.Rows.Count > 0)
{
double Totalopeningqty = 0;
double Totalreceptqty = 0;
double Totalissueqty = 0;
double Totalbqty = 0;
double Totalclosingqty = 0;
var i = 1;
cmd = new SqlCommand("SELECT productmaster.productname, productmaster.productid, (suboutwarddetails.quantity*suboutwarddetails.perunit) AS Value,suboutwarddetails.quantity,suboutwarddetails.perunit, outwarddetails.inwarddate FROM outwarddetails INNER JOIN suboutwarddetails ON outwarddetails.sno = suboutwarddetails.in_refno INNER JOIN productmaster ON productmaster.productid = suboutwarddetails.productid WHERE (outwarddetails.inwarddate BETWEEN @fromdate AND @todate) AND (outwarddetails.section_id = @sectionid) AND (outwarddetails.branchid = @branchid) AND suboutwarddetails.quantity != '0'");
cmd.Parameters.Add("@fromdate", GetLowDate(fromdate));
cmd.Parameters.Add("@todate", GetHighDate(todate));
cmd.Parameters.Add("@sectionid", ReceiptNo);
cmd.Parameters.Add("@branchid", branchid);
DataTable dtIsspcode = SalesDB.SelectQuery(cmd).Tables[0];
foreach (DataRow dr in dtproducts.Rows)
{
double openingqty = 0;
double receptqty = 0;
double qty = 0;
double bqty = 0;
foreach (DataRow drissue in dtIsspcode.Select("productid='" + dr["productid"].ToString() + "'"))
{
DataRow newrow = Report.NewRow();
newrow["sno"] = i++.ToString();
newrow["Product Id"] = dr["productid"].ToString();
newrow["Product Name"] = dr["productname"].ToString();
// newrow["OutwardDate"] = drissue["inwarddate"].ToString();
// double price = 0;
// double.TryParse(drissue["perunit"].ToString(), out price);
newrow["Price"] = drissue["perunit"].ToString();
newrow["Quantity"] = drissue["quantity"].ToString();
double value = 0;
double.TryParse(drissue["Value"].ToString(), out value);
value = Math.Round(value, 2);
newrow["Value"] = value;
Totalissueqty += value;
Report.Rows.Add(newrow);
}
}
DataRow stockreport = Report.NewRow();
stockreport["Product Name"] = "Total";
stockreport["Value"] = Totalissueqty;
//stockreport["ClosingBalance"] = Math.Round(Totalclosingqty, 2); //Totalclosingqty;
Report.Rows.Add(stockreport);
GrdProducts.DataSource = Report;
GrdProducts.DataBind();
hidepanel.Visible = true;
}
else
{
lblmsg.Text = "No data were found";
hidepanel.Visible = false;
}
ScriptManager.RegisterStartupScript(Page, GetType(), "JsStatus", "PopupOpen();", true);
}
catch (Exception ex)
{
lblmsg.Text = ex.Message;
hidepanel.Visible = false;
}
}
protected void OnSelectedIndexChanged(object sender, EventArgs e)
{
int index = grdDue.SelectedRow.RowIndex;
string headsno = grdDue.SelectedRow.Cells[0].Text;
string name = grdDue.SelectedRow.Cells[1].Text;
string country = grdDue.SelectedRow.Cells[2].Text;
DateTime fromdate = new DateTime();
string[] datestrig = txtFromdate.Text.Split(' ');
if (datestrig.Length > 1)
{
if (datestrig[0].Split('-').Length > 0)
{
string[] dates = datestrig[0].Split('-');
string[] times = datestrig[1].Split(':');
fromdate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
//string message = "Row Index: " + index + "\\nName: " + name + "\\nCountry: " + country;
try
{
vdm = new SalesDBManager();
DataTable Report = new DataTable();
cmd = new SqlCommand("SELECT BranchID FROM Collections WHERE (BranchId = @BranchId) AND (PaidDate BETWEEN @d1 AND @d2)");
cmd.Parameters.Add("@BranchID", ddlSalesOffice.SelectedValue);
cmd.Parameters.Add("@d1", GetLowDate(fromdate));
cmd.Parameters.Add("@d2", GetHighDate(fromdate));
DataTable dtCol = vdm.SelectQuery(cmd).Tables[0];
if (dtCol.Rows.Count > 0)
{
lblmsg.Text = "Please Select Details On Current Day Cash Book";
}
else
{
//cmd = new SqlCommand("SELECT DATE_FORMAT(cashpayables.DOE, '%d %b %y') AS EntryDate, cashpayables.VocherID, accountheads.HeadName,cashpayables.VoucherType, cashpayables.Amount, cashpayables.ApprovedAmount as ApprovedAmount FROM accountheads INNER JOIN subpayable ON accountheads.Sno = subpayable.HeadSno INNER JOIN cashpayables ON subpayable.RefNo = cashpayables.Sno WHERE (cashpayables.BranchID = @BranchID) AND (subpayable.HeadSno = @HeadSno) and (cashpayables.Status=@Status) ORDER BY cashpayables.DOE");
cmd = new SqlCommand("SELECT DATE_FORMAT(cashpayables.DOE, '%d %b %y') AS EntryDate, cashpayables.VocherID, accountheads.HeadName, cashpayables.VoucherType,cashpayables.Amount, cashpayables.ApprovedAmount FROM accountheads INNER JOIN subpayable ON accountheads.Sno = subpayable.HeadSno INNER JOIN cashpayables ON subpayable.RefNo = cashpayables.Sno WHERE (subpayable.HeadSno = @HeadSno) AND (cashpayables.Status = @Status) AND (cashpayables.VoucherType<>'Debit') ORDER BY cashpayables.DOE");
cmd.Parameters.Add("@Status", 'P');
//cmd.Parameters.Add("@BranchID", ddlSalesOffice.SelectedValue);
cmd.Parameters.Add("@HeadSno", headsno);
DataTable dtCredit = vdm.SelectQuery(cmd).Tables[0];
Report.Columns.Add("Date");
Report.Columns.Add("VoucherID");
Report.Columns.Add("AccountName");
Report.Columns.Add("IOUAmount").DataType = typeof(Double);
Report.Columns.Add("CreditAmount").DataType = typeof(Double);
double DueAmount = 0;
double CreditAmount = 0;
if (dtCredit.Rows.Count > 0)
{
foreach (DataRow dr in dtCredit.Rows)
{
DataRow newrow = Report.NewRow();
newrow["Date"] = dr["EntryDate"].ToString();
newrow["VoucherID"] = dr["VocherID"].ToString();
newrow["AccountName"] = dr["HeadName"].ToString();
string VoucherType = dr["VoucherType"].ToString();
if (VoucherType == "Due")
{
double ReceivedAmount = 0;
double.TryParse(dr["Amount"].ToString(), out ReceivedAmount);
newrow["IOUAmount"] = ReceivedAmount;
DueAmount += ReceivedAmount;
}
if (VoucherType == "Credit")
{
double ApprovedAmount = 0;
double.TryParse(dr["ApprovedAmount"].ToString(), out ApprovedAmount);
newrow["CreditAmount"] = ApprovedAmount;
CreditAmount += ApprovedAmount;
}
Report.Rows.Add(newrow);
}
}
double Amount = 0;
Amount = DueAmount - CreditAmount;
//lblmsg.Text = "Due Amount: " + Amount.ToString();
DataRow newrowbalance = Report.NewRow();
newrowbalance["AccountName"] = "Due Amount: ";
newrowbalance["IOUAmount"] = Amount;
Report.Rows.Add(newrowbalance);
GrdProducts.DataSource = Report;
GrdProducts.DataBind();
ScriptManager.RegisterStartupScript(Page, GetType(), "JsStatus", "PopupOpen();", true);
}
}
catch (Exception ex)
{
lblmsg.Text = ex.Message;
}
}
protected void grdrecipts_RowCommand(object sender, GridViewCommandEventArgs e)
{
try
{
DataTable Report = new DataTable();
int rowIndex = Convert.ToInt32(e.CommandArgument);
GridViewRow row = grdReports_mnth.Rows[rowIndex];
string pid = row.Cells[2].Text;
if (pid == "" || pid == null || pid == " ")
{
ScriptManager.RegisterStartupScript(Page, GetType(), "JsStatus", "PopupOpen();", false);
}
else
{
SalesDBManager SalesDB = new SalesDBManager();
DateTime fromdate = DateTime.Now;
DateTime todate = DateTime.Now;
string[] datestrig = dtp_FromDate.Text.Split(' ');
if (datestrig.Length > 1)
{
if (datestrig[0].Split('-').Length > 0)
{
string[] dates = datestrig[0].Split('-');
string[] times = datestrig[1].Split(':');
fromdate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
datestrig = dtp_Todate.Text.Split(' ');
if (datestrig.Length > 1)
{
if (datestrig[0].Split('-').Length > 0)
{
string[] dates = datestrig[0].Split('-');
string[] times = datestrig[1].Split(':');
todate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
lblFromDate.Text = fromdate.ToString("dd/MMM/yyyy");
lbltodate.Text = todate.ToString("dd/MMM/yyyy");
string Type = Session["BranchType"].ToString();
Report.Columns.Add("Product Name");
Report.Columns.Add("Received Film");
Report.Columns.Add("Consumption Film");
Report.Columns.Add("Wastage Film");
Report.Columns.Add("Production Film");
Report.Columns.Add("Film Wastage(%)");
cmd = new SqlCommand("SELECT cpm.sno, cpm.productid, cpm.ob, cpm.received_film, cpm.cb, cpm.total, cpm.consumption_film, cpm.remarks, cpm.wastage_film, cpm.production, cpm.approveproduction, cpm.entry_by, cpm.branchid, cpm.doe, pm.productname, bi.branchname, cpm.qty_ltr FROM packing_entry AS cpm INNER JOIN productmaster AS pm ON pm.sno = cpm.productid INNER JOIN branch_info AS bi ON bi.sno = cpm.branchid WHERE (cpm.branchid = @branchid) AND (cpm.doe BETWEEN @d1 AND @d2) AND (cpm.section = 'curd') AND (cpm.productid = @pid) ORDER BY cpm.sno");
cmd.Parameters.Add("@d1", GetLowDate(fromdate));
cmd.Parameters.Add("@d2", GetHighDate(todate));
cmd.Parameters.Add("@branchid", BranchID);
cmd.Parameters.Add("@pid", pid);
DataTable dtPacking = SalesDB.SelectQuery(cmd).Tables[0];
if (dtPacking.Rows.Count > 0)
{
int i = 1;
double wastagetotal = 0;
double cuttingtotal = 0;
double consumptiontotal = 0;
double receivedtotal = 0;
double returnfilmtotal = 0;
double totalwastage = 0;
double totalcuttingwastage = 0;
double overallwastage = 0;
double totalqtykgs = 0;
foreach (DataRow dr in dtPacking.Rows)
{
DataRow newrow = Report.NewRow();
newrow["Product Name"] = dr["productname"].ToString();
double received_film = 0;
double.TryParse(dr["received_film"].ToString(), out received_film);
newrow["Received Film"] = Math.Round(received_film, 2);
double consumption_film = 0;
double.TryParse(dr["consumption_film"].ToString(), out consumption_film);
newrow["Consumption Film"] = Math.Round(consumption_film, 2);
double wastage_film = 0;
double.TryParse(dr["wastage_film"].ToString(), out wastage_film);
newrow["Wastage Film"] = Math.Round(wastage_film, 2);
double production = 0;
double.TryParse(dr["production"].ToString(), out production);
newrow["Production Film"] = Math.Round(production, 2);
double wastage = 0;
double.TryParse(dr["wastage_film"].ToString(), out wastage);
wastagetotal += wastage;
double consumption = 0;
double.TryParse(dr["consumption_film"].ToString(), out consumption);
consumptiontotal += consumption;
double RecivedFilm = 0;
double.TryParse(dr["received_film"].ToString(), out RecivedFilm);
receivedtotal += RecivedFilm;
Report.Rows.Add(newrow);
}
DataRow newvartical21 = Report.NewRow();
newvartical21["Product Name"] = "Total";
newvartical21["Received Film"] = receivedtotal;
newvartical21["Consumption Film"] = consumptiontotal;
newvartical21["Wastage Film"] = wastagetotal;
Report.Rows.Add(newvartical21);
DataRow newvartical31 = Report.NewRow();
totalwastage = (wastagetotal / consumptiontotal) * 100;
totalwastage = Math.Round(totalwastage, 2);
newvartical31["Production Film"] = "Total Wastage Film(%)";
newvartical31["Film Wastage(%)"] = totalwastage;
Report.Rows.Add(newvartical31);
DataRow newvartical51 = Report.NewRow();
overallwastage = ((wastagetotal + cuttingtotal) / consumptiontotal) * 100;
overallwastage = Math.Round(overallwastage, 2);
newvartical51["Production Film"] = "Over All Film Watsge(%)";
newvartical51["Film Wastage(%)"] = overallwastage;
Report.Rows.Add(newvartical51);
GrdProducts.DataSource = Report;
GrdProducts.DataBind();
}
ScriptManager.RegisterStartupScript(Page, GetType(), "JsStatus", "PopupOpen();", true);
}
}
catch (Exception ex)
{
lblmsg.Text = ex.Message;
hidepanel.Visible = false;
}
}
