protected void Btngenerate_Click(object sender, EventArgs e) { cateselect = Label1.Text; string supcode = ""; foreach (ListItem i in selecteditem) { supcode += "'" + i.Text + "',"; } string resultsupplier = supcode.Substring(0, supcode.Length - 1); string que = " select c.category,d.suppliercode,Month(d.deliverydate) as reordermonth,YEAR(d.deliverydate) as reorderyear, sum(b.cost) as reorderammount from OrderItem b,Item c ,sorder d " + "where b.itemcode = c.itemcode and d.purchaseordernumber = b.purchaseordernumber and c.category='" + cateselect + "' and d.suppliercode in (" + resultsupplier + ") and d.deliverydate like ('" + time1 + "-%' )" + "group by d.suppliercode, c.category, Month(d.deliverydate), YEAR(d.deliverydate)" + "union" + " select c.category,d.suppliercode,Month(d.deliverydate) as reordermonth,YEAR(d.deliverydate) as reorderyear, sum(b.cost) as reorderammount from OrderItem b,Item c ,sorder d " + "where b.itemcode = c.itemcode and d.purchaseordernumber = b.purchaseordernumber and c.category='" + cateselect + "' and d.suppliercode in (" + resultsupplier + ") and d.deliverydate like ('" + time2 + "-%' )" + "group by d.suppliercode, c.category, Month(d.deliverydate), YEAR(d.deliverydate)" + "union" + " select c.category,d.suppliercode,Month(d.deliverydate) as reordermonth,YEAR(d.deliverydate) as reorderyear, sum(b.cost) as reorderammount from OrderItem b,Item c ,sorder d " + "where b.itemcode = c.itemcode and d.purchaseordernumber = b.purchaseordernumber and c.category='" + cateselect + "' and d.suppliercode in (" + resultsupplier + ") and d.deliverydate like ('" + time3 + "-%' )" + "group by d.suppliercode, c.category, Month(d.deliverydate), YEAR(d.deliverydate)" ; SS.CryDataSet ds = ssmanager.setReorderDataSet(que); SS.SSreorderTrend cryview2 = new SS.SSreorderTrend(); cryview2.SetDataSource(ds); CrystalReportViewer1.ReportSource = cryview2; }
public static SS.CryDataSet setRequisitionDataSet(string que) { string sr = que; SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=team6adprojectdb;Integrated Security=True;MultipleActiveResultSets=True;Application Name=EntityFramework"); SS.CryDataSet ds = new SS.CryDataSet(); SqlDataAdapter ad = new SqlDataAdapter(que, con); ad.Fill(ds.requisitiontrend); return(ds); }
protected void Page_Load(object sender, EventArgs e) { cate = ListBox5.SelectedValue.ToString(); time1 = Lbmonth1.Text; time2 = Lbmonth2.Text; time3 = Lbmonth3.Text; cateselect = Label1.Text; selecttime = Calendar1.SelectedDate; selecteditem = new List <ListItem>(); foreach (ListItem item in CheckBoxList1.Items) { if (item.Selected) { selecteditem.Add(item); } } if (cate == null) { Button1.Enabled = false; } if (selecttime == null) { Btnmonth1.Enabled = false; Btnmonth2.Enabled = false; Btnmonth3.Enabled = false; } if (cateselect == null || selecteditem == null || time1 == null) { Btngenerate.Enabled = false; } string que = " select c.category,d.suppliercode,Month(d.deliverydate) as reordermonth,YEAR(d.deliverydate) as reorderyear, sum(b.cost) as reorderammount from OrderItem b,Item c ,sorder d where b.itemcode = c.itemcode and d.purchaseordernumber = b.purchaseordernumber group by d.suppliercode, c.category, Month(d.deliverydate), YEAR(d.deliverydate)"; SS.CryDataSet ds = ssmanager.setReorderDataSet(que); SS.SSreorderTrend cryview2 = new SS.SSreorderTrend(); cryview2.SetDataSource(ds); CrystalReportViewer1.ReportSource = cryview2; }
protected void Page_Load(object sender, EventArgs e) { cate = ListBox5.SelectedValue.ToString(); time1 = Lbmonth1.Text; time2 = Lbmonth2.Text; time3 = Lbmonth3.Text; cateselect = Label1.Text; selecttime = Calendar1.SelectedDate; selecteditem = new List <ListItem>(); foreach (ListItem item in CheckBoxList1.Items) { if (item.Selected) { selecteditem.Add(item); } } if (cate == null) { Button1.Enabled = false; } if (selecttime == null) { Btnmonth1.Enabled = false; Btnmonth2.Enabled = false; Btnmonth3.Enabled = false; } if (cateselect == null || selecteditem == null || time1 == null) { Btngenerate.Enabled = false; } string que = "select c.category,d.deptcode,Year(d.collectiondate) as requistionyear ,Month(d.collectiondate) as requsitionmonth, sum(b.actualquantity) as requisitionquantity from DisbursementItem b,Item c, Disbursement d where b.itemcode = c.itemcode and d.disbursementid = b.disbursementid group by c.category,d.deptcode,Month(d.collectiondate),YEAR(d.collectiondate)"; SS.CryDataSet ds = ssmanager.setRequisitionDataSet(que); SS.SSrequisitionTrend cryview2 = new SS.SSrequisitionTrend(); cryview2.SetDataSource(ds); CrystalReportViewer1.ReportSource = cryview2; }
protected void Btngenerate_Click(object sender, EventArgs e) { cateselect = Label1.Text; string depcode = ""; foreach (ListItem i in selecteditem) { depcode += "'" + i.Text + "',"; } string resultdep = depcode.Substring(0, depcode.Length - 1); string que = "select c.category,d.deptcode,Year(d.collectiondate) as requistionyear ,Month(d.collectiondate) as requsitionmonth, sum(b.actualquantity) as requisitionquantity from DisbursementItem b,Item c, Disbursement d " + "where b.itemcode=c.itemcode and d.disbursementid=b.disbursementid and c.category='" + cateselect + "' and d.deptcode in (" + resultdep + ") and d.collectiondate like ('" + time1 + "-%' )" + " group by c.category,d.deptcode,Month(d.collectiondate),YEAR(d.collectiondate)" + " union " + " select c.category,d.deptcode,Year(d.collectiondate) as requistionyear ,Month(d.collectiondate) as requsitionmonth, sum(b.actualquantity) as requisitionquantity from DisbursementItem b,Item c, Disbursement d " + "where b.itemcode=c.itemcode and d.disbursementid=b.disbursementid and c.category='" + cateselect + "' and d.deptcode in (" + resultdep + ") and d.collectiondate like ('" + time2 + "-%' )" + " group by c.category,d.deptcode,Month(d.collectiondate),YEAR(d.collectiondate)" + " union " + " select c.category,d.deptcode,Year(d.collectiondate) as requistionyear ,Month(d.collectiondate) as requsitionmonth, sum(b.actualquantity) as requisitionquantity from DisbursementItem b,Item c, Disbursement d " + "where b.itemcode=c.itemcode and d.disbursementid=b.disbursementid and c.category='" + cateselect + "' and d.deptcode in (" + resultdep + ") and d.collectiondate like ('" + time3 + "-%' )" + " group by c.category,d.deptcode,Month(d.collectiondate),YEAR(d.collectiondate)" ; SS.CryDataSet ds = ssmanager.setRequisitionDataSet(que); SS.SSrequisitionTrend cryview2 = new SS.SSrequisitionTrend(); cryview2.SetDataSource(ds); CrystalReportViewer1.ReportSource = cryview2; //List<Supplier> selectedsupplier = new List<Supplier>(); //DateTime date1 = DateTime.Parse(time1); //DateTime date2 = DateTime.Parse(time2); //DateTime date3 = DateTime.Parse(time3); //suppliers = ClassList.findSupplierByCategory(cate); //for (int i = 0; i < selecteditem.Count; i++) //{ // if (CheckBoxList1.Items[i].Selected) // { // selectedsupplier.Add(suppliers[i]); // } //} //CryDataSet dset = new CryDataSet(); //CryDataSetTableAdapters.reordertrend21TableAdapter reordertrend = new CryDataSetTableAdapters.reordertrend21TableAdapter(); //reordertrend.Fill(dset.reordertrend21); //var total = dset.reordertrend21.Select(y => y).ToList(); //reordertrend.FillBy(dset.reordertrend2,cate,time1,time2,time3); //for ( int j=0; j<selectedsupplier.Count;j++) //{ // var com = dset.reordertrend2. // Where(x =>x.suppliercode == selectedsupplier[j].suppliercode). // Select(y=> new {y.category,y.amount }).ToList(); // total.Add(com); //} //SSreorderTrend cryview = new SSreorderTrend(); //cryview.SetDataSource(total); //CrystalReportViewer1.ReportSource = cryview; }