private void frmLapPhieu_Load(object sender, EventArgs e)
{
ReportParameterCollection reportParameters = new ReportParameterCollection();
reportParameters.Add(new ReportParameter("tbHoTen", QuanLyMuonSach.utcQuanLyMuonSach.tbTenDocGia.Text));
reportParameters.Add(new ReportParameter("tbSoThe", LapPhieuMuon.utcLapPhieuMuon.tbSoThe.Text));
this.rpLapPhieuMuon.LocalReport.SetParameters(reportParameters);
CommonDataSet commonDataSet = new CommonDataSet();
string query = "Select ds.MaSach as Ma_Sach, ds.TenSach as Ten_Sach, " +
"pm.NgayMuon as Ngay_Muon from DauSach ds join Sach s on ds.MaSach = s.MaSach " +
"join ChungThucTra ctt on ctt.SoCaBiet = s.SoCaBiet " +
"join PhieuMuon pm on pm.IdPhieuMuon = ctt.IdPhieuMuon " +
"where pm.IdPhieuMuon = N'" + LapPhieuMuon.utcLapPhieuMuon.tbIdPhieuMuon.Text + "'";
string strConnString = @"Data Source=DESKTOP-2NJ6H39\MSSQLSERVER1;Initial Catalog=QuanLyThuVien;Integrated Security=True";
SqlConnection cn = new SqlConnection(strConnString);
SqlDataAdapter da = new SqlDataAdapter(query, cn);
da.Fill(commonDataSet, commonDataSet.Tables[0].TableName);
ReportDataSource rds = new ReportDataSource("XuatBaoCao", commonDataSet.Tables[0]);
this.rpLapPhieuMuon.LocalReport.DataSources.Clear();
this.rpLapPhieuMuon.LocalReport.DataSources.Add(rds);
this.rpLapPhieuMuon.LocalReport.Refresh();
this.rpLapPhieuMuon.RefreshReport();
}
private void btnTDD_Click(object sender, EventArgs e)
{
variablePublic.user_group_id = "TDD";
#region Binding Permission
CommonDataSet dsPermission = commonBiz.npd_select_product_department_role_by_user_group_id(variablePublic.user_group_id);
bool canAddProduct = false;
canAddProduct = dsPermission.NPD_SELECT_PRODUCT_DEPARTMENT_ROLE_BY_USER_GROUP_ID[0].ADD_NEW_PRODUCT;
if (canAddProduct == true)
{
CloseAllChildForm();
Form f = new Home_Trade();
f.MdiParent = this;
f.FormBorderStyle = System.Windows.Forms.FormBorderStyle.None; //set form without maximize,minimize and close button
f.Dock = DockStyle.Fill; //set form's dock property to fill
f.Show();
}
else
{
CloseAllChildForm();
Form f = new Home();
f.MdiParent = this;
f.FormBorderStyle = System.Windows.Forms.FormBorderStyle.None; //set form without maximize,minimize and close button
f.Dock = DockStyle.Fill; //set form's dock property to fill
f.Show();
}
#endregion
}
public override global::System.Data.DataSet Clone()
{
CommonDataSet cln = ((CommonDataSet)(base.Clone()));
cln.InitVars();
cln.SchemaSerializationMode = this.SchemaSerializationMode;
return(cln);
}
private void cmbChannel_DropDown(object sender, EventArgs e)
{
#region Binding Channel
CommonDataSet dsProductChannel = commonBiz.npd_select_product_sell_all_active(variablePublic.editPassing);
cmbChannel.DisplayMember = "SELL_NAME";
cmbChannel.ValueMember = "SELL_ID";
cmbChannel.DataSource = dsProductChannel.NPD_SELECT_PRODUCT_SELL_ALL_ACTIVE;
#endregion
}
private void cmbProductItemNo_DropDown(object sender, EventArgs e)
{
#region Binding Product Item_No
CommonDataSet dsProductItemNo = commonBiz.npd_select_product_item_no_desc_item_name_code_by_type_id(variablePublic.type_id);
cmbProductItemNo.DisplayMember = "ITEM_NAME".Substring("ITEM_NAME".IndexOf(":") + 1);;
cmbProductItemNo.ValueMember = "ITEM_NO";
cmbProductItemNo.DataSource = dsProductItemNo.NPD_SELECT_PRODUCT_ITEM_NO_DESC_ITEM_NAME_CODE_BY_TYPE_ID;
#endregion
}
private void cmbProductType_DropDown(object sender, EventArgs e)
{
cmbProductItemNo.Text = "";
#region Binding Product Type
CommonDataSet dsProductType = commonBiz.npd_select_product_type();
cmbProductType.DisplayMember = "TYPE_DESC_ENG";
cmbProductType.ValueMember = "TYPE_ID";
cmbProductType.DataSource = dsProductType.NPD_SELECT_PRODUCT_TYPE;
#endregion
}
public static global::System.Xml.Schema.XmlSchemaComplexType GetTypedDataSetSchema(global::System.Xml.Schema.XmlSchemaSet xs)
{
CommonDataSet ds = new CommonDataSet();
global::System.Xml.Schema.XmlSchemaComplexType type = new global::System.Xml.Schema.XmlSchemaComplexType();
global::System.Xml.Schema.XmlSchemaSequence sequence = new global::System.Xml.Schema.XmlSchemaSequence();
global::System.Xml.Schema.XmlSchemaAny any = new global::System.Xml.Schema.XmlSchemaAny();
any.Namespace = ds.Namespace;
sequence.Items.Add(any);
type.Particle = sequence;
global::System.Xml.Schema.XmlSchema dsSchema = ds.GetSchemaSerializable();
if (xs.Contains(dsSchema.TargetNamespace))
{
global::System.IO.MemoryStream s1 = new global::System.IO.MemoryStream();
global::System.IO.MemoryStream s2 = new global::System.IO.MemoryStream();
try {
global::System.Xml.Schema.XmlSchema schema = null;
dsSchema.Write(s1);
for (global::System.Collections.IEnumerator schemas = xs.Schemas(dsSchema.TargetNamespace).GetEnumerator(); schemas.MoveNext();)
{
schema = ((global::System.Xml.Schema.XmlSchema)(schemas.Current));
s2.SetLength(0);
schema.Write(s2);
if ((s1.Length == s2.Length))
{
s1.Position = 0;
s2.Position = 0;
for (; ((s1.Position != s1.Length) &&
(s1.ReadByte() == s2.ReadByte()));)
{
;
}
if ((s1.Position == s1.Length))
{
return(type);
}
}
}
}
finally {
if ((s1 != null))
{
s1.Close();
}
if ((s2 != null))
{
s2.Close();
}
}
}
xs.Add(dsSchema);
return(type);
}
public void CreateReport(String connectionstring, string storeProcedureName, SqlParameter[] parameter, string reportDataSource, string tableName)
{
using (SqlConnection con = new SqlConnection(connectionstring))
{
SqlCommand com = new SqlCommand();
com.Connection = con;
com.CommandType = CommandType.StoredProcedure;
com.CommandText = storeProcedureName;
if (parameter != null)
{
com.Parameters.AddRange(parameter);
}
CommonDataSet ds = new CommonDataSet();
SqlDataAdapter da = new SqlDataAdapter(com);
da.Fill(ds, tableName);
grdreport.DataSource = (DataTable)ds.Tables[tableName];
grdreport.DataBind();
decimal totalAmount = 0;
decimal totalTax = 0;
decimal totalDiscount = 0;
decimal grandTotal = 0;
decimal givenAmt = 0;
decimal balanceAmt = 0;
for (int i = 0; i < ds.Tables[tableName].Rows.Count; i++)
{
lblCompanyName.Text = ds.Tables[tableName].Rows[i]["company_name"].ToString();
lblAddress.Text = ds.Tables[tableName].Rows[i]["company_address"].ToString();
// lblemail.Text = ds.Tables[tableName].Rows[i]["company_address"].ToString();
//lblzipcode.Text = ds.Tables[tableName].Rows[i]["company_address"].ToString();
lbldate.Text = ds.Tables[tableName].Rows[i]["created_date"].ToString();
lblpartyname.Text = ds.Tables[tableName].Rows[i]["party_name"].ToString();
lblpartyaddress.Text = ds.Tables[tableName].Rows[i]["party_address"].ToString();
lblinvoiceno.Text = ds.Tables[tableName].Rows[i]["InvoiceNumber"].ToString();
totalAmount = totalAmount + Convert.ToDecimal(ds.Tables[tableName].Rows[i]["amount"]);
totalTax = totalTax + Convert.ToDecimal(ds.Tables[tableName].Rows[i]["tax_amt"]);
totalDiscount = totalDiscount + Convert.ToDecimal(ds.Tables[tableName].Rows[i]["discount_amnt"]);
grandTotal = grandTotal + totalAmount + totalTax - totalDiscount;
}
lblsubtotal.Text = totalAmount.ToString(); //ds.Tables[tableName].Rows[i]["total_amount"].ToString();given_amnt
lblTaxAmount.Text = totalTax.ToString(); //ds.Tables[tableName].Rows[i]["total_tax"].ToString();
lblDiscountAmt.Text = totalDiscount.ToString(); //ds.Tables[tableName].Rows[i]["total_discount"].ToString();
lblGrandTotal.Text = grandTotal.ToString(); //ds.Tables[tableName].Rows[i]["grand_total"].ToString();
}
}
private void ThongKeDocGiaDangMuon_Load(object sender, EventArgs e)
{
CommonDataSet commonDataSet = new CommonDataSet();
string querySachTrongThuVien = "SELECT dg.SoThe as So_The, dg.TenDocGia as Doc_Gia, ctt.SoCaBiet as So_Ca_Biet ," +
"ds.TenSach as Ten_Sach ,pm.NgayMuon as Ngay_Muon , ctt.NgayTra as Ngay_Tra " +
"FROM DocGia dg join TheThuVien ttv on ttv.ID_The = dg.SoThe " +
"join PhieuMuon pm on pm.SoThe = ttv.ID_The " +
"join ChungThucTra ctt on ctt.IdPhieuMuon = pm.IdPhieuMuon " +
"join Sach s on s.SoCaBiet = ctt.SoCaBiet join DauSach ds on ds.MaSach = s.MaSach";
string strConnString = @"Data Source=DESKTOP-2NJ6H39\MSSQLSERVER1;Initial Catalog=QuanLyThuVien;Integrated Security=True";
SqlConnection cn = new SqlConnection(strConnString);
SqlDataAdapter da = new SqlDataAdapter(querySachTrongThuVien, cn);
da.Fill(commonDataSet, commonDataSet.Tables[0].TableName);
ReportDataSource rds = new ReportDataSource("ThongKeDocGiaDangMuon", commonDataSet.Tables[0]);
this.rpLapPhieuMuon.LocalReport.DataSources.Clear();
this.rpLapPhieuMuon.LocalReport.DataSources.Add(rds);
this.rpLapPhieuMuon.LocalReport.Refresh();
this.rpLapPhieuMuon.RefreshReport();
}
private void ThongKeSachTrongThuVien_Load(object sender, EventArgs e)
{
CommonDataSet commonDataSet = new CommonDataSet();
string querySachTrongThuVien = "SELECT MaSach as Ma_Sach, TenSach as Ten_Sach, tg.TenTacGia as Tac_Gia, " +
"tl.TenTheLoai as The_Loai, nxb.TenNXB as Nha_San_Xuat, NamXuatBan as Nam_Xuat_Ban " +
"From DauSach JOIN TacGia tg on DauSach.MaTacGia = tg.MaTacGia " +
"Join TheLoai tl on DauSach.MaTheLoai = tl.MaTheLoai " +
"join NXB nxb on DauSach.MaNXB = nxb.MaNXB";
string strConnString = @"Data Source=DESKTOP-2NJ6H39\MSSQLSERVER1;Initial Catalog=QuanLyThuVien;Integrated Security=True";
SqlConnection cn = new SqlConnection(strConnString);
SqlDataAdapter da = new SqlDataAdapter(querySachTrongThuVien, cn);
da.Fill(commonDataSet, commonDataSet.Tables[0].TableName);
ReportDataSource rds = new ReportDataSource("ThongKeSachTrongThuVien", commonDataSet.Tables[0]);
this.rpLapPhieuMuon.LocalReport.DataSources.Clear();
this.rpLapPhieuMuon.LocalReport.DataSources.Add(rds);
this.rpLapPhieuMuon.LocalReport.Refresh();
this.rpLapPhieuMuon.RefreshReport();
}
//protected void ddlReportType_SelectedIndexChanged(object sender, EventArgs e)
//{
// if (ddlFilerBy.SelectedItem.Text == "Stock Report")
// {
// ddlFilerBy.Enabled = false;
// txtenddate.Enabled = false;
// txtStartDate.Enabled = false;
// }
// else {
// ddlFilerBy.Enabled = true;
// txtenddate.Enabled = true;
// txtStartDate.Enabled = true;
// }
//}
public void CreateReport(String connectionstring, string storeProcedureName, SqlParameter[] parameter, ref Microsoft.Reporting.WebForms.ReportViewer reportViewer, string reportDataSource, string tableName)
{
using (SqlConnection con = new SqlConnection(connectionstring))
{
SqlCommand com = new SqlCommand();
com.Connection = con;
com.CommandType = CommandType.StoredProcedure;
com.CommandText = storeProcedureName;
if (parameter != null)
{
com.Parameters.AddRange(parameter);
}
CommonDataSet ds = new CommonDataSet();
SqlDataAdapter da = new SqlDataAdapter(com);
da.Fill(ds, tableName);
ReportDataSource datasource = new ReportDataSource(reportDataSource, ds.Tables[tableName]);
reportViewer.LocalReport.DataSources.Clear();
reportViewer.LocalReport.DataSources.Add(datasource);
}
}
protected void btnSearch_Click(object sender, EventArgs e)
{
try
{
if (ValidDate())
{
displayReportSection.Visible = true;
var filterIds = string.Empty;
foreach (ListItem item in lstProduct.Items)
{
if (item.Selected)
{
filterIds += item.Value + ",";
}
}
foreach (ListItem item in lstVendor.Items)
{
if (item.Selected)
{
filterIds += "," + item.Value;
}
}
foreach (ListItem item in lstCustomers.Items)
{
if (item.Selected)
{
filterIds += "," + item.Value;
}
}
foreach (ListItem item in lstGodowns.Items)
{
if (item.Selected)
{
filterIds += "," + item.Value;
}
}
//if (ddlReportType.SelectedItem.Text != "Stock Report")
//{
// btnSearch.ValidationGroup = "search";
//}
SqlParameter[] sqlParams;
int companyId = Convert.ToInt32(Session["company_id"]);
string reportDataSet = string.Empty;
string dataTable = string.Empty;
CommonDataSet ds = new CommonDataSet();
string reportType = string.Empty;
switch (ddlReportType.SelectedItem.Text)
{
case "Stock Report":
isReportTypeStockReport = true;
if (ddlFilerBy.SelectedItem.Text == "Product Wise")
{
reportType = "PRODUCTSTOCKREPORT";
isReportTypeproductInventory = true;
}
else if (ddlFilerBy.SelectedItem.Text == "Godown Wise")
{
reportType = "GODOWNSTOCKREPORT";
}
sqlParams = new SqlParameter[] {
new SqlParameter("@ReportType", reportType),
new SqlParameter("@CompanyId", companyId),
new SqlParameter("@start_date", txtStartDate.Text),
new SqlParameter("@end_date", txtenddate.Text),
new SqlParameter("@FilterIds", filterIds)
};
CreateReport(connectionstring, "CommonReport", sqlParams);
break;
case "Low Stock Report":
reportType = "LOWSTOCKREPORT";
isReportTypeproductInventory = true;
sqlParams = new SqlParameter[] {
new SqlParameter("@ReportType", reportType),
new SqlParameter("@CompanyId", companyId),
new SqlParameter("@start_date", txtStartDate.Text),
new SqlParameter("@end_date", txtenddate.Text),
new SqlParameter("@FilterIds", filterIds)
};
CreateReport(connectionstring, "CommonReport", sqlParams);
break;
case "Inventory Report":
//string reportType = string.Empty;
if (ddlFilerBy.SelectedItem.Text == "Product Wise")
{
reportType = "PRODUCTINVENTORYREPORT";
isReportTypeproductInventory = true;
}
else if (ddlFilerBy.SelectedItem.Text == "Vendor Wise")
{
reportType = "VENDORINVENTORYREPORT";
}
else if (ddlFilerBy.SelectedItem.Text == "Cutomer Wise")
{
reportType = "CUSTOMERINVENTORYREPORT";
}
sqlParams = new SqlParameter[] {
new SqlParameter("@ReportType", reportType),
new SqlParameter("@CompanyId", companyId),
new SqlParameter("@start_date", txtStartDate.Text),
new SqlParameter("@end_date", txtenddate.Text),
new SqlParameter("@FilterIds", filterIds)
};
CreateReport(connectionstring, "CommonReport", sqlParams);
break;
case "Balance Report":
isReportTypeBalance = true;
if (ddlFilerBy.SelectedItem.Text == "Cutomer Wise")
{
reportType = "CUSTOMERBALANCEREPORT";
}
else if (ddlFilerBy.SelectedItem.Text == "Vendor Wise")
{
reportType = "VENDORBALANCEREPORT";
}
sqlParams = new SqlParameter[] {
new SqlParameter("@ReportType", reportType),
new SqlParameter("@CompanyId", companyId),
new SqlParameter("@start_date", txtStartDate.Text),
new SqlParameter("@end_date", txtenddate.Text),
new SqlParameter("@FilterIds", filterIds)
};
CreateReport(connectionstring, "CommonReport", sqlParams);
break;
default:
break;
}
}
else
{
ClientScript.RegisterStartupScript(this.GetType(), "Pop", "openalert('Please Enter The Dates Or Check The Financial Year CheckeBox ');", true);
}
}
catch (Exception ex)
{
ErrorLog.saveerror(ex);
}
}
public void CreateReport(String connectionstring, string storeProcedureName, SqlParameter[] parameter, string reportDataSource, string tableName)
{
using (SqlConnection con = new SqlConnection(connectionstring))
{
SqlCommand com = new SqlCommand();
com.Connection = con;
com.CommandType = CommandType.StoredProcedure;
com.CommandText = storeProcedureName;
if (parameter != null)
{
com.Parameters.AddRange(parameter);
}
CommonDataSet ds = new CommonDataSet();
SqlDataAdapter da = new SqlDataAdapter(com);
da.Fill(ds, tableName);
grdreport.DataSource = (DataTable)ds.Tables[tableName];
grdreport.DataBind();
decimal totalAmount = 0;
decimal totalTax = 0;
decimal totalDiscount = 0;
decimal grandTotal = 0;
for (int i = 0; i < ds.Tables[tableName].Rows.Count; i++)
{
lblCompanyName.Text = ds.Tables[tableName].Rows[i]["Company"].ToString();
lblAddress.Text = ds.Tables[tableName].Rows[i]["CompanyAddress"].ToString();
// lblemail.Text = ds.Tables[tableName].Rows[i]["company_address"].ToString();
//lblzipcode.Text = ds.Tables[tableName].Rows[i]["company_address"].ToString();
lbldate.Text = ds.Tables[tableName].Rows[i]["Date"].ToString();
lblpartyname.Text = ds.Tables[tableName].Rows[i]["Party"].ToString();
lblpartyaddress.Text = ds.Tables[tableName].Rows[i]["PartyAddress"].ToString();
lblinvoiceno.Text = ds.Tables[tableName].Rows[i]["InvoiceNumber"].ToString();
//lblGivenAmnt.Text = ds.Tables[tableName].Rows[i]["GivenAmnt"].ToString();
//lblBalanceAmnt.Text = ds.Tables[tableName].Rows[i]["BalanceAmnt"].ToString();
//for resolve calculation Error Occur
if (isInvoiceType)
{
var purchase = context.tbl_PurchasePaymentDetials.Where(p => p.PurchaseId == id).FirstOrDefault();
totalAmount = Convert.ToDecimal(purchase.SubTotal);
totalTax = Convert.ToDecimal(purchase.TaxAmount);
totalDiscount = Convert.ToDecimal(purchase.DiscountAmount);
grandTotal = Convert.ToDecimal(purchase.GrandTotal);
lblGivenAmnt.Text = purchase.GivenAmnt.ToString();
lblBalanceAmnt.Text = purchase.BalanceAmnt.ToString();
}
else
{
var sale = context.tbl_SalePaymentDetails.Where(s => s.SaleId == id).FirstOrDefault();
totalAmount = Convert.ToDecimal(sale.SubTotal);
totalTax = Convert.ToDecimal(sale.TaxAmount);
totalDiscount = Convert.ToDecimal(sale.DiscountAmount);
grandTotal = Convert.ToDecimal(sale.GrandTotal);
lblGivenAmnt.Text = sale.GivenAmnt.ToString();
lblBalanceAmnt.Text = sale.BalanceAmnt.ToString();
}
//if (ds.Tables[tableName].Rows[i]["Type"].ToString() == "Purchase")
//{
// totalAmount = totalAmount + Convert.ToDecimal(ds.Tables[tableName].Rows[i]["TotalAmount"]);
// totalTax = totalTax + Convert.ToDecimal(ds.Tables[tableName].Rows[i]["TotalTax"]);
// totalDiscount = totalDiscount + Convert.ToDecimal(ds.Tables[tableName].Rows[i]["TotalDiscount"]);
// grandTotal = grandTotal + Convert.ToDecimal(ds.Tables[tableName].Rows[i]["GrandTotal"]);
//}
//else if (ds.Tables[tableName].Rows[i]["Type"].ToString() == "Sale")
//{
// totalAmount = totalAmount + Convert.ToDecimal(ds.Tables[tableName].Rows[i]["TotalAmount"]);
// totalTax = totalTax + Convert.ToDecimal(ds.Tables[tableName].Rows[i]["TotalTax"]);
// totalDiscount = totalDiscount + Convert.ToDecimal(ds.Tables[tableName].Rows[i]["TotalDiscount"]);
// grandTotal = grandTotal + Convert.ToDecimal(ds.Tables[tableName].Rows[i]["GrandTotal"]);
//}
//else if (ds.Tables[tableName].Rows[i]["Type"].ToString() == "Return")
//{
// totalAmount = totalAmount - Convert.ToDecimal(ds.Tables[tableName].Rows[i]["TotalAmount"]);
// totalTax = totalTax - Convert.ToDecimal(ds.Tables[tableName].Rows[i]["TotalTax"]);
// totalDiscount = totalDiscount - Convert.ToDecimal(ds.Tables[tableName].Rows[i]["TotalDiscount"]);
// grandTotal = grandTotal - Convert.ToDecimal(ds.Tables[tableName].Rows[i]["GrandTotal"]);
//}
//if (isReportType)
//{
//totalAmount = Convert.ToDecimal(ds.Tables[tableName].Rows[i]["TotalAmount"]);
//totalTax = Convert.ToDecimal(ds.Tables[tableName].Rows[i]["TotalTax"]);
//totalDiscount = Convert.ToDecimal(ds.Tables[tableName].Rows[i]["TotalDiscount"]);
//grandTotal = Convert.ToDecimal(ds.Tables[tableName].Rows[i]["GrandTotal"]);
// }
//totalAmount = Convert.ToDecimal(ds.Tables[tableName].Rows[i]["TotalAmount"]);
//totalTax = Convert.ToDecimal(ds.Tables[tableName].Rows[i]["TotalTax"]);
//totalDiscount = Convert.ToDecimal(ds.Tables[tableName].Rows[i]["TotalDiscount"]);
//grandTotal = Convert.ToDecimal(ds.Tables[tableName].Rows[i]["TotalAmount"]);
lblsubtotal.Text = totalAmount.ToString();
lblTaxAmount.Text = totalTax.ToString();
lblDiscountAmt.Text = totalDiscount.ToString();
lblGrandTotal.Text = grandTotal.ToString();
}
}
}
