private void button1_Click(object sender, EventArgs e)
{
try
{
Cursor.Current = Cursors.WaitCursor;
DataTable dt = new DataTable();
List <MemberBuyList> MemberBuyListsGet = new List <MemberBuyList>();
using (SSLsEntities db = new SSLsEntities())
{
string queryString = "select t.MemberId, BuyTotal,m.Name,m.TaxId,isnull(s.Name,'ชาย') Sex,isnull(m.IsRemoveShared,1) IsRemoveShared from ( select MEMBER_ID MemberId, sum(PRICE) BuyTotal from WH_TRAT.dbo.PS_ORDER o inner join WH_TRAT.dbo.PS_HD h on o.invoice_no = h.invoice_no inner join WH_TRAT.dbo.PS_DTL d on o.invoice_no = d.invoice_no where CANCEL_DATE is null and convert(varchar, h.create_date,112) >= '" + DtBuyS.Value.ToString("yyyyMMdd") + "' and convert(varchar, h.create_date,112) <= '" + DtBuyE.Value.ToString("yyyyMMdd") + "' " + (TbBuyMS.Text.Trim() == "" ? "" : "and convert(varchar, MEMBER_ID) >= '" + TbBuyMS.Text.Trim() + "' and convert(varchar, MEMBER_ID) <= '" + TbBuyME.Text.Trim() + "' ") + (TbBuyNM.Text.Trim() == "" ? "" : "and MEMBER_ID <> '" + TbBuyNM.Text.Trim() + "' ") + " group by MEMBER_ID )t left join mem.Member m on t.MemberId = m.Code and m.Enable = 1 inner join mem.Sex s on m.FKSex = s.Id ";
var getdata = db.Database.ExecuteEntities <MemberBuy>(queryString);
if (getdata.Count() > 0)
{
foreach (var item in getdata.ToList())
{
MemberBuyListsGet.Add(new MemberBuyList()
{
MemberId = item.MemberId,
MemberName = item.MemberName,
TaxId = item.TaxId,
Sex = item.Sex,
BuyTotal = item.BuyTotal
});
}
}
}
if (MemberBuyListsGet.Count() > 0)
{
List <MemberBuyList> MemberBuyLists = new List <MemberBuyList>();
var DsGetMember = MemberBuyListsGet.Where(w => w.MemberId != null & (CombBuyS.SelectedIndex == 1 ? w.IsRemoveShared == false : (CombBuyS.SelectedIndex == 2 ? w.IsRemoveShared == true : w.MemberId != null)) &
(CombBuySex.SelectedIndex == 1 ? w.Sex == "ชาย" : (CombBuySex.SelectedIndex == 2 ? w.Sex == "หญิง" : w.MemberId != null))).ToList();
foreach (var item in DsGetMember.ToList())
{
if (item.MemberId == "99")
{
using (SSLsEntities db = new SSLsEntities())
{
string queryString = "select COUNT(h.INVOICE_NO) Qty from WH_TRAT.dbo.PS_ORDER o inner join WH_TRAT.dbo.PS_HD h on o.invoice_no = h.invoice_no where CANCEL_DATE is null and convert(varchar, h.create_date,112) >= '" + DtBuyS.Value.ToString("yyyyMMdd") + "' and convert(varchar, h.create_date,112) <= '" + DtBuyE.Value.ToString("yyyyMMdd") + "' and MEMBER_ID = '99'";
var getdata99 = db.Database.ExecuteEntities <Get99>(queryString);
MemberBuyLists.Add(new MemberBuyList()
{
MemberName = "สมาชิก",
BuyTotal = item.BuyTotal,
Qty = (getdata99 == null ? 0 : getdata99.Select(s => s.Qty).FirstOrDefault())
});
}
}
else
{
MemberBuyLists.Add(new MemberBuyList()
{
MemberName = "ลูกค้าทั่วไป",
BuyTotal = item.BuyTotal,
Qty = 1
});
}
}
var abc = MemberBuyLists.GroupBy(b => b.MemberName).Select(s => new
{
MemberName = s.Key,
BuyTotal = s.Sum(ss => ss.BuyTotal),
Qty = s.Sum(ss => ss.Qty)
}).ToList();
FormReportViewer formReportViewer = new FormReportViewer(this);
formReportViewer.reportViewer1.Reset();
formReportViewer.reportViewer1.ProcessingMode = ProcessingMode.Local;
formReportViewer.reportViewer1.LocalReport.DataSources.Clear();
formReportViewer.reportViewer1.LocalReport.DataSources.Add(new Microsoft.Reporting.WinForms.ReportDataSource("DataSet1", abc));
var Report = new ReportParameter("Report", "สมาชิกใช้บริการเเบบสรุป");
var Where = new ReportParameter("Where", "วันที่ซื้อสินค้า " + DtBuyS.Value.ToString("dd/MM/yyyy") + " ถึง " + DtBuyE.Value.ToString("dd/MM/yyyy") + " " + (TbBuyMS.Text == "" ? "" : "ตามสมาชิก " + TbBuyMS.Text + " - " + TbBuyME.Text + " ") + (TbBuyNM.Text == "" ? "" : "ไม่ตามสมาชิก " + TbBuyNM.Text + " ") + (CobRegistor.SelectedIndex == 1 ? "สถานะ : ร่วมหุ้น " : (CobRegistor.SelectedIndex == 2 ? "สถานะ : ถอนหุ้น " : "สถานะ : ทั้งหมด ")) + (CobSex.SelectedIndex == 1 ? "เพศ : ชาย" : CobSex.SelectedIndex == 2 ? "เพศ : หญิง" : "เพศ : ทั้งหมด"));
ReportParameter[] HeaderParams = { Report, Where };
formReportViewer.reportViewer1.LocalReport.ReportEmbeddedResource = "Pos_Management_System.ReportMemberBuy2.rdlc";
foreach (ReportParameter param in HeaderParams)
{
formReportViewer.reportViewer1.LocalReport.SetParameters(param);
}
formReportViewer.reportViewer1.RefreshReport();
formReportViewer.Show();
Cursor.Current = Cursors.Default;
}
else
{
Cursor.Current = Cursors.Default;
MessageBox.Show("ไม่พบข้อมูล", "เเจ้ง", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
catch (Exception cx)
{
Cursor.Current = Cursors.Default;
MessageBox.Show(cx.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void BtReport1_Click(object sender, EventArgs e)
{
try
{
if (CbAdd.Checked == true)
{
Cursor.Current = Cursors.WaitCursor;
DataTable dt = new DataTable();
using (SSLsEntities db = new SSLsEntities())
{
var ds = db.MemberShare.Where(w => w.Enable == true & w.Member.Enable == true & w.CreateDate >= DtAddS.Value & w.CreateDate <= DtAddE.Value & (CobRegistor.SelectedIndex == 1 ? w.Member.IsRemoveShared == false : (CobRegistor.SelectedIndex == 2 ? w.Member.IsRemoveShared == true : w.Enable == true)) & (CobSex.SelectedIndex == 1 ? w.Member.Sex.Id == 2 : (CobSex.SelectedIndex == 2 ? w.Member.Sex.Id == 3 : w.Enable == true)) &
(TbMemberE.Text == "" ? w.Enable == true : w.Member.Code.Contains(TbMemberS.Text.Trim()) & w.Member.Code.Contains(TbMemberE.Text.Trim())) &
(TbNoMemberS.Text == "" ? w.Enable == true : w.Member.Code != TbNoMemberS.Text.Trim())).Select(s => new
{
Id = s.Member.Id,
MemberId = s.Member.Code,
MemberName = s.Member.Name,
RegistorDate = s.Member.CreateDate,
s.Member.Address,
s.Member.TaxId,
Sex = s.Member.Sex.Name,
QtyOld = (s.Member.MemberShare.Where(w => w.Enable == true & w.CreateDate < s.CreateDate).Sum(ss => ss.Qty) == null ? 0 : s.Member.MemberShare.Where(w => w.Enable == true & w.CreateDate < s.CreateDate).Sum(ss => ss.Qty)),
Qty = s.Qty,
QtyTotal = s.Member.MemberShare.Where(w => w.Enable == true).Sum(ss => ss.Qty),
AddShareDate = s.CreateDate,
}).OrderBy(o => o.MemberId).ToList();
if (ds.Count > 0)
{
dt = Library.ConvertToDataTable(ds);
}
}
if (dt.Rows.Count > 0)
{
//dataGridView1.DataSource = dt;
FormReportViewer formReportViewer = new FormReportViewer(this);
formReportViewer.reportViewer1.Reset();
formReportViewer.reportViewer1.ProcessingMode = ProcessingMode.Local;
formReportViewer.reportViewer1.LocalReport.DataSources.Clear();
formReportViewer.reportViewer1.LocalReport.DataSources.Add(new Microsoft.Reporting.WinForms.ReportDataSource("DataSet1", dt));
var Report = new ReportParameter("Report", "สมาชิกเพิ่มหุ้น");
var Where = new ReportParameter("Where", "สมาชิกเพิ่มหุ้นวันที่ " + DtAddS.Value.ToString("dd/MM/yyyy") + " ถึง " + DtAddE.Value.ToString("dd/MM/yyyy") + " " + (TbMemberS.Text == "" ? "" : "ตามสมาชิก " + TbMemberS.Text + " - " + TbMemberE.Text + " ") + (TbNoMemberS.Text == "" ? "" : "ไม่ตามสมาชิก " + TbNoMemberS.Text + " ") + (CobRegistor.SelectedIndex == 1 ? "สถานะ : ร่วมหุ้น " : (CobRegistor.SelectedIndex == 2 ? "สถานะ : ถอนหุ้น " : "สถานะ : ทั้งหมด ")) + (CobSex.SelectedIndex == 1 ? "เพศ : ชาย" : CobSex.SelectedIndex == 2 ? "เพศ : หญิง" : "เพศ : ทั้งหมด"));
var Sort = new ReportParameter("Sort", (CbDelete.Checked == true ? "D" : "R"));
ReportParameter[] HeaderParams = { Report, Where, Sort };
formReportViewer.reportViewer1.LocalReport.ReportEmbeddedResource = "Pos_Management_System.ReportMember2.rdlc";
foreach (ReportParameter param in HeaderParams)
{
formReportViewer.reportViewer1.LocalReport.SetParameters(param);
}
formReportViewer.reportViewer1.RefreshReport();
formReportViewer.Show();
Cursor.Current = Cursors.Default;
}
else
{
Cursor.Current = Cursors.Default;
MessageBox.Show("ไม่พบข้อมูล", "เเจ้ง", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
else
{
Cursor.Current = Cursors.WaitCursor;
var DsGetMember = Singleton.SingletonMember.Instance().Members.Where(w => w.Enable == true & (CbRegistor.Checked == true ? w.CreateDate >= DtRegistorS.Value & w.CreateDate <= DtRegistorE.Value : w.Enable == true)
& (CobRegistor.SelectedIndex == 1 ? w.IsRemoveShared == false : (CobRegistor.SelectedIndex == 2 ? w.IsRemoveShared == true : w.Enable == true)) &
(CbDelete.Checked == true ? w.ResignDate >= DtDeleteS.Value & w.ResignDate <= DtDeleteE.Value : w.Enable == true) &
(CobSex.SelectedIndex == 1 ? w.Sex.Id == 2 : (CobSex.SelectedIndex == 2 ? w.Sex.Id == 3 : w.Enable == true)) &
(TbMemberE.Text == "" ? w.Enable == true : Convert.ToDouble(w.Code) >= Convert.ToDouble(TbMemberS.Text.Trim()) & Convert.ToDouble(w.Code) <= Convert.ToDouble(TbMemberE.Text.Trim())) &
(TbNoMemberS.Text == "" ? w.Enable == true : w.Code != TbNoMemberS.Text.Trim())).Select(s => new
{
Id = s.Id,
MemberId = s.Code,
MemberName = s.Name,
RegistorDate = s.CreateDate,
s.Address,
s.TaxId,
Sex = s.Sex.Name,
Qty = s.MemberShare.Where(w => w.Enable == true).Sum(ss => ss.Qty),
Bath = s.MemberShare.Where(w => w.Enable == true).Sum(ss => ss.Qty * ss.Share.Value),
s.IsRemoveShared,
s.ResignDate,
s.ResignRemark
}).OrderBy(o => o.MemberId).ToList();
if (DsGetMember.Count() > 0)
{
DataTable dt = Library.ConvertToDataTable(DsGetMember);
FormReportViewer formReportViewer = new FormReportViewer(this);
formReportViewer.reportViewer1.Reset();
formReportViewer.reportViewer1.ProcessingMode = ProcessingMode.Local;
formReportViewer.reportViewer1.LocalReport.DataSources.Clear();
formReportViewer.reportViewer1.LocalReport.DataSources.Add(new Microsoft.Reporting.WinForms.ReportDataSource("DataSet1", dt));
var Report = new ReportParameter("Report", (CbRegistor.Checked == true ? "สมัครสมาชิก" : (CbDelete.Checked == true ? "สมาชิกลาออก" : "ทั้งหมด")));
var Where = new ReportParameter("Where", (CbRegistor.Checked == true ? "สมัครสมาชิกวันที่ " + DtRegistorS.Value.ToString("dd/MM/yyyy") + " ถึง " + DtRegistorE.Value.ToString("dd/MM/yyyy") + " " : (CbDelete.Checked == true ? "สมาชิกลาออกวันที่ " + DtDeleteS.Value.ToString("dd/MM/yyyy") + " ถึง " + DtDeleteE.Value.ToString("dd/MM/yyyy") + " " : "")) + (TbMemberS.Text == "" ? "" : "ตามสมาชิก " + TbMemberS.Text + " - " + TbMemberE.Text + " ") + (TbNoMemberS.Text == "" ? "" : "ไม่ตามสมาชิก " + TbNoMemberS.Text + " ") + (CobRegistor.SelectedIndex == 1 ? "สถานะ : ร่วมหุ้น " : (CobRegistor.SelectedIndex == 2 ? "สถานะ : ถอนหุ้น " : "สถานะ : ทั้งหมด ")) + (CobSex.SelectedIndex == 1 ? "เพศ : ชาย" : CobSex.SelectedIndex == 2 ? "เพศ : หญิง" : "เพศ : ทั้งหมด"));
var Sort = new ReportParameter("Sort", (CbDelete.Checked == true ? "D" : "R"));
ReportParameter[] HeaderParams = { Report, Where, Sort };
formReportViewer.reportViewer1.LocalReport.ReportEmbeddedResource = "Pos_Management_System.ReportMember1.rdlc";
foreach (ReportParameter param in HeaderParams)
{
formReportViewer.reportViewer1.LocalReport.SetParameters(param);
}
formReportViewer.reportViewer1.RefreshReport();
formReportViewer.Show();
Cursor.Current = Cursors.Default;
}
else
{
Cursor.Current = Cursors.Default;
MessageBox.Show("ไม่พบข้อมูล", "เเจ้ง", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
}
catch (Exception cx)
{
Cursor.Current = Cursors.Default;
MessageBox.Show(cx.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
