dateTimePicker (วัน เดือน ปี)กับ listView(แสดงผลข้อมูล) (C++/CLI)

#pragma once
#include "FormEdit.h" //ถ้าไม่ใส่จะ Error c2065

namespace Pay2015 {

using namespace System;
using namespace System::ComponentModel;
using namespace System::Collections;
using namespace System::Windows::Forms;
using namespace System:ata;
using namespace System:rawing;
using namespace System:ata::OleDb;//Connect MS Access 2003 database
namespace Excel = Microsoft::Office::Interop::Excel;// Export To Excel

/// <summary>
/// Summary for FormReport
/// </summary>
public ref class FormReport : public System::Windows::Forms::Form
{

public:
static String ^conStr = "provider=Microsoft.Jet.Oledb.4.0;data source=" + Application::StartupPath + "\\dbPay.mdb;";

private:
OleDbConnection ^cnn;
OleDbDataAdapter ^da;
DataSet ^ds;
OleDbCommand ^cmd;
private: System::Windows::Forms::Label^ label2;


private: System::Windows::Forms::Label^ label3;


private: System::Windows::Forms:ateTimePicker^ dateTimePicker1;
private: System::Windows::Forms:ateTimePicker^ dateTimePicker2;
private: System::Windows::Forms::Label^ lblCountCoffee;
private: System::Windows::Forms::Label^ lblMoneyCoffee;
private: System::Windows::Forms::Label^ lblMoneyBookID;
private: System::Windows::Forms::Label^ lblCountBookID;


private: System::Windows::Forms::Label^ lblMoneyCaraboa;

private: System::Windows::Forms::Label^ lblCountCaraboa;
private: System::Windows::Forms::Label^ lblMoneyEat;
private: System::Windows::Forms::Label^ lblCountEat;
private: System::Windows::Forms::Label^ lblMoneyTelephone;

private: System::Windows::Forms::Label^ lblCountTelephone;
private: System::Windows::Forms::Label^ lblGrandTotal;
private: System::Windows::Forms::Label^ lblMoneyMerit;

private: System::Windows::Forms::Label^ lblCountMerit;

OleDbDataReader ^dr;

public:
FormReport(void)
{
InitializeComponent();
this->WindowState = FormWindowState::Maximized;//ขยายเต็มจอ
}

#pragma endregion
private: System::Void FormReport_Shown(System::Object^ sender, System::EventArgs^ e) {
this->WindowState = FormWindowState::Maximized;//ขยายเต็มจอ
}
private: System::Void FormReport_Load(System::Object^ sender, System::EventArgs^ e) {
LoadData();
}

void FormReport::LoadData(){
//cnn = gcnew OleDbConnection("provider=Microsoft.Jet.Oledb.4.0;data source=" + Application::StartupPath + "\\dbPay.mdb;");
cnn = gcnew OleDbConnection(conStr);
cnn->Open();

String ^dt1 = nullptr;
dt1 = dateTimePicker1->Value.Year + "/";
dt1 += dateTimePicker1->Value.Month + "/";
dt1 += dateTimePicker1->Value.Day;

String ^dt2 = nullptr;
dt2 = dateTimePicker2->Value.Year + "/";
dt2 += dateTimePicker2->Value.Month + "/";
dt2 += dateTimePicker2->Value.Day;

String ^sql = "SELECT CodePK,CodeID, " +
"Format([PickerUp]) As Pay, " +
"Coffee,BookID,Book,Caraboa, " +
"OilName,OilPrice,Eat,Telephone, "+
"Merit,Other FROM tblPay " +
"where ([PickerUp] >= #" + dt1 + "# and [PickerUp] <= #" + dt2 + "#) ORDER BY CodeID ASC ";
//DateDiff ('yyyy', #30/09/2010#, PickerUp) As DT
//DateDiff ('yyyy', Now(), PickerUp) As DT
//DateDiff('m', #30/09/2013#, PickerUp, True) As DT
cmd = gcnew OleDbCommand(sql, cnn);

try{
//====================================================================================
dr = cmd->ExecuteReader();

listView1->Items->Clear();
while (dr->Read()){
ListViewItem ^item = gcnew ListViewItem(dr["CodePK"]->ToString());
item->SubItems->Add(dr["CodeID"]->ToString());
item->SubItems->Add(dr["Pay"]->ToString());
item->SubItems->Add(String::Format("{0:#,##0.00}", Convert::ToDecimal(dr["Coffee"])));
item->SubItems->Add(String::Format("{0:#,##0.00}", Convert::ToDecimal(dr["BookID"])));//BookID
item->SubItems->Add(String::Format("{0:#,##0.00}", Convert::ToDecimal(dr["Book"])));;//Book
item->SubItems->Add(String::Format("{0:#,##0.00}", Convert::ToDecimal(dr["Caraboa"])));//Caraboa
item->SubItems->Add(dr["OilName"]->ToString());
item->SubItems->Add(String::Format("{0:#,##0.00}", Convert::ToDecimal(dr["OilPrice"])));//OilPrice
item->SubItems->Add(String::Format("{0:#,##0.00}", Convert::ToDecimal(dr["Eat"])));//Eat
item->SubItems->Add(String::Format("{0:#,##0.00}", Convert::ToDecimal(dr["Telephone"])));//Telephone
item->SubItems->Add(String::Format("{0:#,##0.00}", Convert::ToDecimal(dr["Merit"])));//Merit
item->SubItems->Add(String::Format("{0:#,##0.00}", Convert::ToDecimal(dr["Other"])));//Other
listView1->Items->Add(item);
}
//=======================================================================================
OleDbCommand ^com1 = gcnew OleDbCommand("Select Count(CodePK) " +
"From tblPay " +
"where ([PickerUp] >= #" + dt1 + "# and [PickerUp] <= #" + dt2 + "#)" +
"", cnn);

Int32 count1 = safe_cast<Int32>(com1->ExecuteScalar());
lblCount->Text = "[ จำนวน : " + count1.ToString() + " แถว ]";
//====================lblCountCoffee=====================================================
OleDbCommand ^com2 = gcnew OleDbCommand("Select Count(Coffee) " +
"From tblPay " +
"where ([PickerUp] >= #" + dt1 + "# and [PickerUp] <= #" + dt2 + "#)" +
"And Coffee > 0 ", cnn);

Int32 count2 = safe_cast<Int32>(com2->ExecuteScalar());
lblCountCoffee->Text = "[ จำนวน : " + count2.ToString() + " กระป๋อง ]";
//=====================lblCountEat========================================================
OleDbCommand ^com3 = gcnew OleDbCommand("Select Count(Eat) " +
"From tblPay " +
"where ([PickerUp] >= #" + dt1 + "# and [PickerUp] <= #" + dt2 + "#)" +
"And Eat > 0 ", cnn);

Int32 count3 = safe_cast<Int32>(com3->ExecuteScalar());
lblCountEat->Text = "[ จำนวน : " + count3.ToString() + " ครั้ง ]";
//=====================lblCountTelephone==================================================
OleDbCommand ^com4 = gcnew OleDbCommand("Select Count(Telephone) " +
"From tblPay " +
"where ([PickerUp] >= #" + dt1 + "# and [PickerUp] <= #" + dt2 + "#)" +
"And Telephone > 0 ", cnn);

Int32 count4 = safe_cast<Int32>(com4->ExecuteScalar());
lblCountTelephone->Text = "[ จำนวน : " + count4.ToString() + " ครั้ง ]";
//=====================lblCountMerit======================================================
OleDbCommand ^com5 = gcnew OleDbCommand("Select Count(Merit) " +
"From tblPay " +
"where ([PickerUp] >= #" + dt1 + "# and [PickerUp] <= #" + dt2 + "#)" +
"And Merit > 0 ", cnn);

Int32 count5 = safe_cast<Int32>(com5->ExecuteScalar());
lblCountMerit->Text = "[ จำนวน : " + count5.ToString() + " ครั้ง ]";
//=====================lblCountOther======================================================
OleDbCommand ^com6 = gcnew OleDbCommand("Select Count(Other) " +
"From tblPay " +
"where ([PickerUp] >= #" + dt1 + "# and [PickerUp] <= #" + dt2 + "#)" +
"And Other > 0 ", cnn);

Int32 count6 = safe_cast<Int32>(com6->ExecuteScalar());
//========================================================================================
int i = 0;
double TotalOilPrice = 0;
double TotalCoffee = 0;
double TotalBookID = 0;
double TotalBook = 0;
double TotalCaraboa = 0;
double TotalEat = 0;
double TotalTelephone = 0;
double TotalMerit = 0;
double TotalOther = 0;
double SeparateCaraboa = 0;
double GrdTotal = 0;

for (i = 0; i <= listView1->Items->Count - 1; i++)
{
TotalOilPrice += double:arse(listView1->Items->SubItems[8]->Text);
TotalCoffee += double:arse(listView1->Items->SubItems[3]->Text);
TotalBookID += double:arse(listView1->Items->SubItems[4]->Text);
TotalBook += double:arse(listView1->Items->SubItems[5]->Text);
TotalCaraboa += double:arse(listView1->Items->SubItems[6]->Text);
TotalEat += double:arse(listView1->Items->SubItems[9]->Text);
TotalTelephone += double:arse(listView1->Items->SubItems[10]->Text);
TotalMerit += double:arse(listView1->Items->SubItems[11]->Text);
TotalOther += double:arse(listView1->Items->SubItems[12]->Text);
}

SeparateCaraboa = TotalCaraboa / 10;
GrdTotal = TotalCoffee + TotalBook + TotalCaraboa + TotalOilPrice + TotalEat + TotalTelephone +
TotalMerit + TotalOther;

lblTotalOil->Text = "[ น้ำมัน : " + TotalOilPrice.ToString("#,##0.00") + " บาท ]";
lblMoneyCoffee->Text = "[ กาแฟ : " + TotalCoffee.ToString("#,##0.00") + " บาท ]";
lblCountBookID->Text = "[ จำนวน : " + TotalBookID.ToString() + " เล่ม ]";
lblMoneyBookID->Text = "[ หนังสือ : " + TotalBook.ToString("#,##0.00") + " บาท ]";
lblCountCaraboa->Text = "[ จำนวน : " + SeparateCaraboa.ToString() + " ขวด ]";
lblMoneyCaraboa->Text = "[ คาราบาว : " + TotalCaraboa.ToString("#,##0.00") + " บาท ]";
lblMoneyEat->Text = "[ กินข้าว : " + TotalEat.ToString("#,##0.00") + " บาท ]";
lblMoneyTelephone->Text = "[ บัตรโทร : " + TotalTelephone.ToString("#,##0.00") + " บาท ]";
lblMoneyMerit->Text = "[ ทำบุญ : " + TotalMerit.ToString("#,##0.00") + " บาท ]";

lblGrandTotal->Text = "[ รวม : " + GrdTotal.ToString("#,##0.00") + " บาท ]";
//=======================================================================================
dr->Close();
//=======================================================================================
}
catch (Exception ^ex){
MessageBox::Show(ex->Message, Application:roductName, MessageBoxButtons::OK, MessageBoxIcon::Error);
Application::ExitThread();
}
cnn->Close();
}
private: System::Void btnSearch_Click(System::Object^ sender, System::EventArgs^ e) {
//============Search===========
listView1->Items->Clear();
LoadData();
//=============================
}
private: System::Void btnRefresh_Click(System::Object^ sender, System::EventArgs^ e) {
//===========Refresh Data================================================
dateTimePicker2->Value = DateTime::Today;//โชว์ค่าในวันที่ครบเดือน
//dateTimePicker2->MinDate = DateTime::Today;//โชว์ค่าในวันที่จากวันที่ปัจจุบันเป็นต้นไป
listView1->Items->Clear();

lblCount->Text = "[ จำนวน : 0 แถว ]";
lblTotalOil->Text = "[ น้ำมัน : 0.00 บาท ]";
lblCountCoffee->Text = "[ จำนวน : 0 กระป๋อง ]";
lblMoneyCoffee->Text = "[ กาแฟ : 0.00 บาท ]";
lblCountBookID->Text = "[ จำนวน : 0 เล่ม ]";
lblMoneyBookID->Text = "[ หนังสือ : 0.00 บาท ]";
lblCountCaraboa->Text = "[ จำนวน : 0 ขวด ]";
lblMoneyCaraboa->Text = "[ คาราบาว : 0.00 บาท ]";
lblCountEat->Text = "[ จำนวน : 0 ครั้ง ]";
lblMoneyEat->Text = "[ กินข้าว : 0.00 บาท ]";
lblCountTelephone->Text = "[ จำนวน : 0 ครั้ง ]";
lblMoneyTelephone->Text = "[ บัตรโทร : 0.00 บาท ]";
lblCountMerit->Text = "[ จำนวน : 0 ครั้ง ]";
lblMoneyMerit->Text = "[ ทำบุญ : 0.00 บาท ]";
lblGrandTotal->Text = "[ รวม : 0.00 บาท ]";
//=======================================================================
}
private: System::Void btnExcel_Click(System::Object^ sender, System::EventArgs^ e) {
//====================Export 2 Excel=====================
Microsoft::Office::Interop::Excel::Application ^app = gcnew Microsoft::Office::Interop::Excel::Application();
app->Visible = true;
Microsoft::Office::Interop::Excel::Workbook ^wb = app->Workbooks->Add(1);
Microsoft::Office::Interop::Excel::Worksheet ^ws = safe_cast<Microsoft::Office::Interop::Excel::Worksheet^>(wb->Worksheets[1]);
int i = 1;
int i2 = 1;
for each (ListViewItem ^lvi in listView1->Items)
{
i = 1;
for each (ListViewItem::ListViewSubItem ^lvs in lvi->SubItems)
{
ws->Cells[i2, i] = lvs->Text;
i++;
}
i2++;
}
//=====Kill Process Excel=================================
ObjectRelease(ws);
ObjectRelease(wb);
ObjectRelease(app);
//========================================================
}

void FormReport::ObjectRelease(Object ^objRealease){
try{
System::Runtime::InteropServices::Marshal::ReleaseComObject(objRealease);
objRealease = nullptr;
}
catch (Exception ^ex){
objRealease = nullptr;
MessageBox::Show("Error_" + ex->ToString());
}
finally{
GC::Collect();
}
}
private: System::Void listView1_MouseDoubleClick(System::Object^ sender, System::Windows::Forms::MouseEventArgs^ e) {
FormEdit ^edit = gcnew FormEdit();
edit->strID = listView1->SelectedItems[0]->SubItems[0]->Text;
edit->Show();
}
};
}