private void Search()
{
string itxbPetID = txb_PetID.Text.ToString();
DataTable idtCompany;
string isqlCompany = "SELECT * FROM petshop.tb_company";
idtCompany = iConnect.SelectByCommand(isqlCompany);
string inUD = nUDID.Value.ToString();
DataTable idtPetProfile;
string isqlPetProfile = "SELECT * FROM petshop.tb_petprofile where pet_ID between '" + itxbPetID + "' AND '" + itxbPetID + "'+'" + inUD + "'";
idtPetProfile = iConnect.SelectByCommand(isqlPetProfile);
System.Globalization.CultureInfo cultureInfo = new System.Globalization.CultureInfo("th-TH");
System.Threading.Thread.CurrentThread.CurrentCulture = cultureInfo;
System.Threading.Thread.CurrentThread.CurrentUICulture = cultureInfo;
ReportDocument rpt = new ReportDocument();
//rpt.Load("D:\\PetShop\\CrmemberCard.rpt");
rpt.Load("CrmemberCard.rpt");
rpt.SetDataSource(idtPetProfile);
rpt.Subreports["Company_Sub_Report"].Database.Tables[0].SetDataSource(idtCompany);
this.crViewerBloodtest.ReportSource = rpt;
this.crViewerBloodtest.Refresh();
}
private void SearchBloodTest()
{
DataTable idtCompany;
string isqlCompany = "SELECT * FROM petshop.tb_company";
idtCompany = iConnect.SelectByCommand(isqlCompany);
string ilbBloodTestID = lb_BloodTestID.Text.Trim();
DataTable idtBloodTest;
string isqlBloodTest = "SELECT tb_bloodtest.*,tb_employee.Em_Name,tb_petprofile.Pet_Name,tb_petprofile.Owner_Name,tb_petprofile.Owner_Tel FROM petshop.tb_bloodtest,tb_employee,tb_petprofile "
+ "where tb_bloodtest.BloodTest_ID = '" + ilbBloodTestID + "' AND tb_petprofile.Pet_ID = tb_bloodtest.Pet_ID AND tb_bloodtest.Em_ID = tb_employee.Em_ID";
idtBloodTest = iConnect.SelectByCommand(isqlBloodTest);
System.Globalization.CultureInfo cultureInfo = new System.Globalization.CultureInfo("th-TH");
System.Threading.Thread.CurrentThread.CurrentCulture = cultureInfo;
System.Threading.Thread.CurrentThread.CurrentUICulture = cultureInfo;
ReportDocument rpt = new ReportDocument();
//rpt.Load("D:\\Petshop\\CrBloodTest.rpt");
rpt.Load("CrBloodTest.rpt");
rpt.SetDataSource(idtBloodTest);
rpt.Subreports["Company_Sub_Report"].Database.Tables[0].SetDataSource(idtCompany);
this.crViewerBloodtest.ReportSource = rpt;
this.crViewerBloodtest.Refresh();
}
private void loadData()
{
DataTable idtUnit;
string isqlcommand = "SELECT * FROM `tb_unit`";
idtUnit = iConnect.SelectByCommand(isqlcommand);
dGV_Uni.DataSource = idtUnit;
dGV_Uni.Refresh();
}
internal void LoadEmployee()
{
DataTable idtEmployee;
string isqlEmployee = "SELECT * FROM petshop.tb_employee where Em_ID = '" + _strUser + "'";
idtEmployee = iConnect.SelectByCommand(isqlEmployee);
StripStatusEm_ID.Text = idtEmployee.Rows[0].Field <string>(0);
StripStatusEm_Name.Text = idtEmployee.Rows[0].Field <string>(1);
}
private void loadData()
{
DataTable idtoutlay;
string isqlcommand = "SELECT * FROM petshop.tb_outlay";
idtoutlay = iConnect.SelectByCommand(isqlcommand);
dGV_outlay.DataSource = idtoutlay;
dGV_outlay.Refresh();
lb_Searchoutlay.Text = idtoutlay.Rows.Count.ToString();
}
///
/// ส่วนโหลดข้อมูลขึ้นตารางต่างๆของโปรแกรม
///
private void LoadService() //ส่วนของดึงข้อมูลจาก mysql ตาราง Service มาแสดง แท๊บ บริการ
{
DataTable idtService;
string isqlCommand = "SELECT * FROM `tb_service`";
idtService = iConnect.SelectByCommand(isqlCommand);
dGV_Service.DataSource = idtService;
dGV_Service.Refresh();
lb_SearchService.Text = idtService.Rows.Count.ToString();
}
private void LoadBreed()
{
DataTable idtBreed;
string isqlCommand = "SELECT tb_petbreed.*,tb_pettype.PetType_Des FROM tb_petbreed,tb_pettype WHERE (tb_petbreed.PetType_ID = tb_pettype.PetType_ID)";
idtBreed = iConnect.SelectByCommand(isqlCommand);
dGV_Breed.DataSource = idtBreed;
dGV_Breed.Refresh();
lb_CountBreed.Text = idtBreed.Rows.Count.ToString();
}
private void loadBill() //ส่วนของดึงข้อมูลจาก mysql ตาราง Service มาแสดง แท๊บ บริการ
{
DataTable idtBillAll;
string isqlCommand = "SELECT tb_bill.*,tb_employee.Em_Name FROM tb_bill,tb_employee where tb_bill.Em_ID = tb_employee.Em_ID";
idtBillAll = iConnect.SelectByCommand(isqlCommand);
LoadThai();
dGV_Bill.DataSource = idtBillAll;
dGV_Bill.Refresh();
}
private void LoadEmPloyee()
{
DataTable idtEmployee;
string isqlCommand = "SELECT tb_employee.*,tb_emposition.Em_Position FROM `tb_employee`,tb_emposition where tb_employee.EmPosition_ID = tb_emposition.EmPosition_ID";
idtEmployee = iConnect.SelectByCommand(isqlCommand);
LoadThai();
dGV_Ep.DataSource = idtEmployee;
dGV_Ep.Refresh();
}
private void LoadUnit()
{
DataTable idtUnit;
string isqlcommand = "SELECT * FROM `tb_unit` ";
idtUnit = iConnect.SelectByCommand(isqlcommand);
cb_ProductUnit.DisplayMember = idtUnit.Columns["Unit_Name"].ColumnName;
cb_ProductUnit.ValueMember = idtUnit.Columns["Unit_ID"].ColumnName;
cb_ProductUnit.DataSource = idtUnit;
}
private void LoadBill() //ข้อมูลใบเสร็จ //Master
{
string ilbBillid = lb_BillID.Text.Trim();
DataTable idtBillDetail;
string isqlBillDetail = "SELECT tb_servicebill.Service_ID as ServiceMedi_ID,tb_service.Service_Des as ServiceMedi_Des ," +
"tb_servicebill.Service_Bill_Unit as ServiceMedi_Unit ," +
"tb_servicebill.Service_Bill_Price as ServiceMedi_Price ," +
"tb_servicebill.Service_Bill_Amt as ServiceMedi_Amt " +
"From tb_servicebill,tb_service where tb_servicebill.Bill_ID = '" + ilbBillid + "' AND tb_servicebill.Service_ID = tb_service.Service_ID " +
"union " +
"SELECT tb_medibill.Medi_ID as ServiceMedi_ID,tb_medicine.Medi_Des as ServiceMedi_Des ," +
"tb_medibill.Medi_Bill_Unit as ServiceMedi_Unit ," +
"tb_medibill.Medi_Bill_Price as ServiceMedi_Price ," +
"tb_medibill.Medi_Bill_Amt as ServiceMedi_Amt " +
"From tb_medibill,tb_medicine where tb_medibill.Bill_ID = '" + ilbBillid + "' AND tb_medibill.Medi_ID = tb_medicine.Medi_ID " +
"union " +
"SELECT tb_productsalebill.Product_ID as ServiceMedi_ID,tb_product.Product_Des as ServiceMedi_Des, " +
"tb_productsalebill.ProductSale_Bill_Unit as ServiceMedi_Unit, " +
"tb_productsalebill.ProductSale_Bill_Price as ServiceMedi_Price, " +
"tb_productsalebill.ProductSale_Bill_Amt as ServiceMedi_Amt " +
"FROM tb_productsalebill,tb_product where Bill_ID = '" + ilbBillid + "' AND tb_productsalebill.Product_ID = tb_product.Product_ID";
idtBillDetail = iConnect.SelectByCommand(isqlBillDetail);
//////////////////////////////////////////////Load Company//////////////////////////////////////////////////////////
DataTable idtCompany;
string isqlCompany = "SELECT * FROM petshop.tb_company";
idtCompany = iConnect.SelectByCommand(isqlCompany);
//////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////Load Bill Master//////////////////////////////////////////////////////////////
DataTable idtbill;
string isqlBill = "SELECT tb_bill.*,tb_employee.Em_Name FROM tb_bill,tb_employee where Bill_ID ='" + ilbBillid + "' AND tb_bill.Em_ID = tb_employee.Em_ID";
idtbill = iConnect.SelectByCommand(isqlBill);
System.Globalization.CultureInfo cultureInfo = new System.Globalization.CultureInfo("th-TH");
System.Threading.Thread.CurrentThread.CurrentCulture = cultureInfo;
System.Threading.Thread.CurrentThread.CurrentUICulture = cultureInfo;
ReportDocument rpt = new ReportDocument();
rpt.Load("CrBillSerMe.rpt");
/////////////////////////////Main Detail/////////////////////////////////
rpt.SetDataSource(idtBillDetail);
/////////////////////////////Sub Company////////////////////////////////
rpt.Subreports["Company_Sub_Report"].Database.Tables[0].SetDataSource(idtCompany);
////////////////////////////Sub Bill Master////////////////////////////////
rpt.Subreports["Bill_Sub_Report"].Database.Tables[0].SetDataSource(idtbill);
rpt.Subreports["Bill_Sub_ReportM"].Database.Tables[0].SetDataSource(idtbill);
this.crystalReportViewer1.ReportSource = rpt;
this.crystalReportViewer1.Refresh();
}
private void loadoutlayDetail()
{
DataTable idtoutlay;
string isqloutlay = "SELECT tb_outlaydetail.*,tb_outlay.outlay_Detail,tb_employee.em_Name FROM petshop.tb_outlaydetail,tb_outlay,tb_employee where tb_outlaydetail.outlay_ID = tb_outlay.outlay_ID AND tb_outlaydetail.Em_ID = tb_employee.Em_ID";
idtoutlay = iConnect.SelectByCommand(isqloutlay);
if (idtoutlay.Rows.Count > 0)
{
LoadThai();
dGV_outlay.DataSource = idtoutlay;
dGV_outlay.Refresh();
}
}
private void loadHealDate()
{
LoadThai();
DataTable idtHealDate;
if (rBt_All.Checked == true)
{
string isqlHealDate = "SELECT tb_healdate.*,tb_petprofile.pet_name,tb_service.Service_Des,tb_petprofile.Owner_Name,tb_petprofile.Owner_Tel, if (tb_healdate.HealDate_Status = '1','ติดตามแล้ว','ยังไม่ได้') as Status " +
"FROM tb_healdate,tb_petprofile,tb_service where tb_healdate.Service_ID = tb_Service.Service_ID AND tb_healdate.Pet_ID = tb_petprofile.Pet_ID order by HealDate_Status,HealDate_Day ";
idtHealDate = iConnect.SelectByCommand(isqlHealDate);
LoadThai();
dGV_HealDate.DataSource = idtHealDate;
dGV_HealDate.Refresh();
}
else if (rBt_contract.Checked == true)
{
string isqlHealDate = "SELECT tb_healdate.*,tb_petprofile.pet_name,tb_service.Service_Des,tb_petprofile.Owner_Name,tb_petprofile.Owner_Tel, if (tb_healdate.HealDate_Status = '1','ติดตามแล้ว','ยังไม่ได้') as Status " +
"FROM tb_healdate,tb_petprofile,tb_service where tb_healdate.Service_ID = tb_Service.Service_ID AND tb_healdate.Pet_ID = tb_petprofile.Pet_ID AND Healdate_Status = 0 order by HealDate_Day";
idtHealDate = iConnect.SelectByCommand(isqlHealDate);
LoadThai();
dGV_HealDate.DataSource = idtHealDate;
dGV_HealDate.Refresh();
}
else if (rBt_contracted.Checked == true)
{
string isqlHealDate = "SELECT tb_healdate.*,tb_petprofile.pet_name,tb_service.Service_Des,tb_petprofile.Owner_Name,tb_petprofile.Owner_Tel , if (tb_healdate.HealDate_Status = '1','ติดตามแล้ว','ยังไม่ได้') as Status " +
"FROM tb_healdate,tb_petprofile,tb_service where tb_healdate.Service_ID = tb_Service.Service_ID AND tb_healdate.Pet_ID = tb_petprofile.Pet_ID AND Healdate_Status = 1 order by HealDate_Day";
idtHealDate = iConnect.SelectByCommand(isqlHealDate);
LoadThai();
dGV_HealDate.DataSource = idtHealDate;
dGV_HealDate.Refresh();
}
else if (rBt_Today.Checked == true)
{
System.Globalization.CultureInfo cultureInfo = new System.Globalization.CultureInfo("en-US");
System.Threading.Thread.CurrentThread.CurrentCulture = cultureInfo;
System.Threading.Thread.CurrentThread.CurrentUICulture = cultureInfo;
iDateToday = DateTime.Now.ToString("yyyy-MM-dd");
string isqlHealDate = "SELECT tb_healdate.*,tb_petprofile.pet_name,tb_service.Service_Des,tb_petprofile.Owner_Name,tb_petprofile.Owner_Tel , if (tb_healdate.HealDate_Status = '1','ติดตามแล้ว','ยังไม่ได้') as Status "
+ " FROM tb_healdate,tb_petprofile,tb_service where tb_healdate.Service_ID = tb_Service.Service_ID AND tb_healdate.Pet_ID = tb_petprofile.Pet_ID AND Healdate_Status = 0 "
+ " AND HealDate_Remind = '" + iDateToday + "'"
+ " Union "
+ " SELECT tb_healdate.*,tb_petprofile.pet_name,tb_service.Service_Des,tb_petprofile.Owner_Name,tb_petprofile.Owner_Tel , if (tb_healdate.HealDate_Status = '1','ติดตามแล้ว','ยังไม่ได้') as Status "
+ " FROM tb_healdate,tb_petprofile,tb_service where tb_healdate.Service_ID = tb_Service.Service_ID AND tb_healdate.Pet_ID = tb_petprofile.Pet_ID AND Healdate_Status = 0 "
+ " AND HealDate_Day = '" + iDateToday + "' Order By HealDate_ID";
idtHealDate = iConnect.SelectByCommand(isqlHealDate);
LoadThai();
dGV_HealDate.DataSource = idtHealDate;
dGV_HealDate.Refresh();
}
}
private void LoadData()
{
string isqlCommand = "SELECT * FROM `tb_company`";
idtCompany = iConnect.SelectByCommand(isqlCommand);
if (idtCompany.Rows.Count > 0)
{
txb_CoID.Text = idtCompany.Rows[0]["Company_ID"].ToString();
txb_Company_Name.Text = idtCompany.Rows[0]["Company_Name"].ToString();
txb_CompanyAddr.Text = idtCompany.Rows[0]["Company_Addr"].ToString();
txb_CompanyTel.Text = idtCompany.Rows[0]["Company_Tel"].ToString();
txb_CompanyRemark.Text = idtCompany.Rows[0]["Company_Remark"].ToString();
txb_CoService.Text = idtCompany.Rows[0]["CoService"].ToString();
txb_CoSale.Text = idtCompany.Rows[0]["CoSale"].ToString();
txb_CoBill.Text = idtCompany.Rows[0]["CoBill"].ToString();
lb_CoIDH.Text = idtCompany.Rows[0]["Company_ID"].ToString();
string FP = idtCompany.Rows[0]["CoLogo"].ToString();
if (File.Exists(FP))
{
picBoxLogo.Image = Image.FromFile(FP);
Image i = Image.FromFile(FP);
float docHeight = i.Height / i.VerticalResolution;
float docWidth = i.Width / i.HorizontalResolution;
string iHeight = docWidth.ToString();
string iWidth = docWidth.ToString();
lb_ExLogo.Text = "ความกว้าง " + iWidth + " x ความยาว " + iHeight;
}
}
}
private void loadBill()
{
Regex Regint = new Regex(@"^\d{1,3}$");
if (!Regint.IsMatch(txb_UnitList.Text.Trim()))
{
txb_UnitList.Text = "5";
}
string itxbUnitList = txb_UnitList.Text.Trim();
string ilbPetID = lb_PetID.Text.Trim();
DataTable idtDateDetail;
string isqlDateDetail = "SELECT tb_healdate.*,tb_service.Service_Des FROM tb_healdate,tb_service where tb_HealDate.Pet_ID ='" + ilbPetID + "' AND tb_healdate.Service_ID = tb_service.Service_ID AND HealDate_Status = 0 Order By HealDate_Day asc limit " + itxbUnitList + "";
idtDateDetail = iConnect.SelectByCommand(isqlDateDetail);
//////////////////////////////////////////////Load Company//////////////////////////////////////////////////////////
DataTable idtCompany;
string isqlCompany = "SELECT * FROM petshop.tb_company";
idtCompany = iConnect.SelectByCommand(isqlCompany);
////////////////////////////////////////////Load Bill Master//////////////////////////////////////////////////////////////
DataTable idtProfile;
string isqlProfile = "SELECT * FROM tb_petprofile where Pet_ID = '" + ilbPetID + "'";
idtProfile = iConnect.SelectByCommand(isqlProfile);
System.Globalization.CultureInfo cultureInfo = new System.Globalization.CultureInfo("th-TH");
System.Threading.Thread.CurrentThread.CurrentCulture = cultureInfo;
System.Threading.Thread.CurrentThread.CurrentUICulture = cultureInfo;
ReportDocument rpt = new ReportDocument();
//string iFolder = Application.ExecutablePath;
//rpt.Load("D:\\Petshop\\Resources\\CrBillDate.rpt");
rpt.Load("CrBillDate.rpt");
/////////////////////////////Main Detail/////////////////////////////////
rpt.SetDataSource(idtDateDetail);
/////////////////////////////Sub Company////////////////////////////////
rpt.Subreports["Company_Sub_Report"].Database.Tables[0].SetDataSource(idtCompany);
////////////////////////////Sub Bill Master////////////////////////////////
// rpt.Subreports["Bill_Sub_Report"].Database.Tables[0].SetDataSource(idtBillDetail);
rpt.Subreports["Profile_Sub_Report"].Database.Tables[0].SetDataSource(idtProfile);
this.crystalReportViewer1.ReportSource = rpt;
this.crystalReportViewer1.Refresh();
}
private void loadBill()
{
string iReferID = txb_ReferID.Text;
DataTable idtBill;
string isqlBill = "SELECT * FROM petshop.tb_bill where Refer_ID ='" + iReferID + "'";
idtBill = iConnect.SelectByCommand(isqlBill);
if (idtBill.Rows.Count > 0)
{
Lb_BillID.Text = idtBill.Rows[0].Field <string>(0);
}
}
private void bt_Submit_Click(object sender, EventArgs e)
{
string itxbOldPwd = txb_OldPwd.Text.Trim();
string itxbNewPwd = txb_NewPwd.Text.Trim();
string itxbPwdconf = txb_Pwdconf.Text.Trim();
byte[] hashold;
byte[] hashnew;
byte[] hashconf;
using (MD5 md5 = MD5.Create())
{
hashold = md5.ComputeHash(Encoding.UTF8.GetBytes(itxbOldPwd));
hashnew = md5.ComputeHash(Encoding.UTF8.GetBytes(itxbNewPwd));
hashconf = md5.ComputeHash(Encoding.UTF8.GetBytes(itxbPwdconf));
}
string iPwdOld = Convert.ToBase64String(hashold);
string iPwdnew = Convert.ToBase64String(hashnew);
string iPwdconf = Convert.ToBase64String(hashconf);
if (iPwdnew == iPwdconf)
{
string ilbPetID = lb_PetID.Text.Trim();
DataTable idtCheckPassword;
string isqlCheckPass = "******" + ilbPetID + "' AND `Em_Pwd`='" + iPwdOld + "'";
idtCheckPassword = iConnect.SelectByCommand(isqlCheckPass);
if (idtCheckPassword.Rows.Count == 1)
{
string isqlChangePwd = "UPDATE `petshop`.`tb_employee` SET `Em_Pwd`='" + iPwdnew + "' WHERE `Em_ID`='" + ilbPetID + "'";
iConnect.Insert(isqlChangePwd);
this.Close();
MessageBox.Show("เปลี่ยนรหัสผ่านแล้ว");
}
else
{
epCheck.SetError(txb_OldPwd, "รหัสผ่านเดิมไม่ถูกต้อง");
}
}
else
{
epCheck.SetError(txb_Pwdconf, "รหัสผ่านไม่ตรงกัน");
}
}
private void bt_remove_Click(object sender, EventArgs e)
{
string itxbPetProfileID = txb_PetProfileID.Text.ToString();
DataTable idtCheckProfileID;
string isqlCheckProfileID = "SELECT * FROM petshop.tb_petprofile where Pet_ID = '" + itxbPetProfileID + "' ";
idtCheckProfileID = iConnect.SelectByCommand(isqlCheckProfileID);
if (idtCheckProfileID.Rows.Count != 0)
{
DataTable idtCheckStatus;
string isqlCheckStatus = "SELECT * FROM petshop.tb_petprofile where Pet_ID = '" + itxbPetProfileID + "' AND Status = 0";
idtCheckStatus = iConnect.SelectByCommand(isqlCheckStatus);
if (idtCheckStatus.Rows.Count == 1)
{
DialogResult iConfirmResult = MessageBox.Show("ต้องการลบข้อมูลสัตว์ใช่หรือไม่?", "ลบข้อมูลข้อมูล..", MessageBoxButtons.YesNo);
if (iConfirmResult == DialogResult.Yes)
{
string isqlreStatus = "UPDATE `petshop`.`tb_petprofile` SET `Status`=1 WHERE `Pet_ID`='" + itxbPetProfileID + "'";
iConnect.Insert(isqlreStatus);
clearTxb();
}
}
else
{
DialogResult iConfirmResult = MessageBox.Show("ต้องการคืนข้อมูลสัตว์ใช่หรือไม่?", "คืนค่าข้อมูลข้อมูล..", MessageBoxButtons.YesNo);
if (iConfirmResult == DialogResult.Yes)
{
string isqlreStatus = "UPDATE `petshop`.`tb_petprofile` SET `Status`=0 WHERE `Pet_ID`='" + itxbPetProfileID + "'";
iConnect.Insert(isqlreStatus);
clearTxb();
}
}
}
LoadPetProfile();
}
private void loadBillAmt()
{
string ilbBillID = Lb_BillID.Text.Trim();
DataTable idtSumService;
string isqlSumService = "SELECT sum(service_Bill_Amt) as ServiceAmt FROM tb_servicebill where Bill_ID = '" + ilbBillID + "'";
idtSumService = iConnect.SelectByCommand(isqlSumService);
foreach (DataRow row in idtSumService.Rows)
{
object value = row["ServiceAmt"];
if (value == DBNull.Value)
{
iSerPrice = 0;
}
else
{
iSerPrice = idtSumService.Rows[0].Field <decimal>(0);
}
}
DataTable idtSumMedi;
string isqlSumMedi = "SELECT sum(Medi_Bill_Amt) as MediAmt FROM tb_medibill where Bill_ID = '" + ilbBillID + "'";
idtSumMedi = iConnect.SelectByCommand(isqlSumMedi);
foreach (DataRow row in idtSumMedi.Rows)
{
object value = row["MediAmt"];
if (value == DBNull.Value)
{
iMePrice = 0;
}
else
{
iMePrice = idtSumMedi.Rows[0].Field <decimal>(0);
}
}
}
private void CheckBill()
{
string ilbHealRecordID = lb_HealRecordID.Text.Trim();
if ((ilbHealRecordID != null) && (ilbHealRecordID != ""))
{
tabControlServiceMedi.Enabled = true;
bt_PrintBill.Enabled = true;
bt_PrintDate.Enabled = true;
txb_HealRecordDC.Enabled = true;
}
else
{
tabControlServiceMedi.Enabled = false;
bt_PrintBill.Enabled = false;
bt_PrintDate.Enabled = false;
txb_HealRecordDC.Enabled = false;
}
DataTable idtBillCheck; //CheckBill
string isqlBillCheck = "SELECT * FROM petshop.tb_bill where Refer_ID = '" + ilbHealRecordID + "'";
idtBillCheck = iConnect.SelectByCommand(isqlBillCheck);
if (idtBillCheck.Rows.Count == 0)
{
bt_RecordHeal.Enabled = true;
bt_BuyService.Enabled = true;
bt_BuyMedi.Enabled = true;
bt_refService.Enabled = true;
bt_refMedi.Enabled = true;
}
else
{
bt_RecordHeal.Enabled = false;
bt_BuyService.Enabled = false;
bt_BuyMedi.Enabled = false;
bt_refService.Enabled = false;
bt_refMedi.Enabled = false;
}
}
private void SearchHealRecord()
{
string itxbPetID = "%%";
if (CheckBox_PetID.Checked == true)
{
itxbPetID = txb_PetID.Text.Trim();
}
//////////////////////ข้อมูลองค์กร//////////////////////////
DataTable idtCompany;
string isqlCompany = "SELECT * FROM petshop.tb_company";
idtCompany = iConnect.SelectByCommand(isqlCompany);
/////////////////////////////////////////////////////////
System.Globalization.CultureInfo cultureInfo = new System.Globalization.CultureInfo("th-TH");
System.Threading.Thread.CurrentThread.CurrentCulture = cultureInfo;
System.Threading.Thread.CurrentThread.CurrentUICulture = cultureInfo;
ReportDate = dTP_Date.Value.ToString("dd MMMM yyyy");
ReportToDate = dTP_Date.Value.ToString("dd MMMM yyyy");
///////////////////////////////////////////////////////////
DataTable idtHead = new DataTable("date");
DataRow dr = null;
idtHead.Columns.Add(new DataColumn("ReportDate", typeof(string)));
idtHead.Columns.Add(new DataColumn("ReportToDate", typeof(string)));
dr = idtHead.NewRow();
dr["ReportDate"] = ReportDate;
dr["ReportToDate"] = ReportToDate;
idtHead.Rows.Add(dr);
///////////////////////[[ส่วนบันทึกส่วนหลัก]]//////////////////////////
DataTable idtHealRecord;
string isqlHealRecord = "SELECT tb_healRecord.*,tb_petprofile.Pet_Name,tb_petprofile.Owner_Name,tb_petprofile.Owner_Tel,tb_employee.Em_Name " +
"FROM tb_healrecord,tb_petprofile,tb_employee " +
"where tb_petprofile.Pet_ID = tb_healRecord.Pet_ID AND tb_employee.Em_ID = tb_healRecord.Em_ID AND tb_healRecord.Pet_ID like '" + itxbPetID + "'" +
"AND (tb_healrecord.HealRecord_Date between '" + idtpDate + "' AND '" + idtpToDate + "') ";
idtHealRecord = iConnect.SelectByCommand(isqlHealRecord);
/////////////////////////ส่วนบริการ////////////////////////////////
DataTable idtServiceRecord;
string isqlServiceRecord = "SELECT tb_servicerecord.*,tb_service.Service_Des " +
"FROM petshop.tb_servicerecord,tb_service " +
"where tb_service.service_ID = tb_servicerecord.service_ID";
idtServiceRecord = iConnect.SelectByCommand(isqlServiceRecord);
////////////////////////ส่วนยา///////////////////////////////////
DataTable idtMediRecord;
string isqlMediRecord = "SELECT tb_medirecord.*,tb_medicine.Medi_Des " +
"FROM petshop.tb_medirecord,tb_medicine " +
"where tb_medicine.Medi_ID = tb_medirecord.Medi_ID";
idtMediRecord = iConnect.SelectByCommand(isqlMediRecord);
ReportDocument rpt = new ReportDocument();
//rpt.Load("D:\\Petshop\\CrHealRecord.rpt");
rpt.Load("CrHealRecord.rpt");
rpt.SetDataSource(idtHealRecord);
rpt.Subreports["Head_Sub_Report"].Database.Tables[0].SetDataSource(idtHead);
rpt.Subreports["Company_Sub_Report"].Database.Tables[0].SetDataSource(idtCompany);
rpt.Subreports["ServiceRecord_Sub_Report"].Database.Tables[0].SetDataSource(idtServiceRecord);
rpt.Subreports["MediRecord_Sub_Report"].Database.Tables[0].SetDataSource(idtMediRecord);
rpt.Subreports["HealRecord_Sub_Report"].Database.Tables[0].SetDataSource(idtHealRecord);
this.crViewerHealRecord.ReportSource = rpt;
this.crViewerHealRecord.Refresh();
}
private void AddEditBloodTest()
{
string ilbBloodTestID = lb_BloodTestID.Text.Trim();
System.Globalization.CultureInfo cultureInfo = new System.Globalization.CultureInfo("en-US");
System.Threading.Thread.CurrentThread.CurrentCulture = cultureInfo;
System.Threading.Thread.CurrentThread.CurrentUICulture = cultureInfo;
string idtpBlootTestDate = dTP_BloodTestDate.Value.ToString("yyyy-MM-dd");
string icbEmID = cb_Em.SelectedValue.ToString();
string ilbPetID = lb_PetID.Text.Trim();
/////////////////////////////////////////////
Regex Regdecimal = new Regex(@"^((\d{1,8})|(\d{1,6}\.\d{1,2}))$");
if (!Regdecimal.IsMatch(txb_HGB.Text))
{
txb_HGB.Text = "0.00";
}
string itxbHGB = txb_HGB.Text.Trim();
///////////////////
if (!Regdecimal.IsMatch(txb_PCV.Text))
{
txb_PCV.Text = "0.00";
}
string itxbPCV = txb_PCV.Text.Trim();
///////////////////
if (!Regdecimal.IsMatch(txb_RBC.Text))
{
txb_RBC.Text = "0.00";
}
string itxbRBC = txb_RBC.Text.Trim();
/////////////////////
if (!Regdecimal.IsMatch(txb_MCV.Text))
{
txb_MCV.Text = "0.00";
}
string itxbMCV = txb_MCV.Text.Trim();
///////////////////
if (!Regdecimal.IsMatch(txb_MCHC.Text))
{
txb_MCHC.Text = "0.00";
}
string itxbMCHC = txb_MCHC.Text.Trim();
//////////////////
if (!Regdecimal.IsMatch(txb_WBC.Text))
{
txb_WBC.Text = "0.00";
}
string itxbWBC = txb_WBC.Text.Trim();
/////////////////////
if (!Regdecimal.IsMatch(txb_MYCYTE.Text))
{
txb_MYCYTE.Text = "0.00";
}
string itxbMYCYTE = txb_MYCYTE.Text.Trim();
/////////////////////////
if (!Regdecimal.IsMatch(txb_META.Text))
{
txb_META.Text = "0.00";
}
string itxbMETA = txb_META.Text.Trim();
/////////////////
if (!Regdecimal.IsMatch(txb_BAND.Text))
{
txb_BAND.Text = "0.00";
}
string itxbBand = txb_BAND.Text.Trim();
////////////////
if (!Regdecimal.IsMatch(txb_SEGS.Text))
{
txb_SEGS.Text = "0.00";
}
string itxbSEGS = txb_SEGS.Text.Trim();
///////////////////////////////////////////
if (!Regdecimal.IsMatch(txb_LYMPH.Text))
{
txb_LYMPH.Text = "0.00";
}
string itxbLYMPH = txb_LYMPH.Text.Trim();
if (!Regdecimal.IsMatch(txb_Mono.Text))
{
txb_Mono.Text = "0.00";
}
string itxbMONO = txb_Mono.Text.Trim();
if (!Regdecimal.IsMatch(txb_EOS.Text))
{
txb_EOS.Text = "0.00";
}
string itxbEOS = txb_EOS.Text.Trim();
if (!Regdecimal.IsMatch(txb_BASO.Text))
{
txb_BASO.Text = "0.00";
}
string itxbBASO = txb_BASO.Text.Trim();
if (!Regdecimal.IsMatch(txb_UNCLASS.Text))
{
txb_UNCLASS.Text = "0.00";
}
string itxbUNCLASS = txb_UNCLASS.Text.Trim();
///////////////////////////////////////////
if (!Regdecimal.IsMatch(txb_NUCLRBC.Text))
{
txb_NUCLRBC.Text = "0.00";
}
string itxbNUCLRBC = txb_NUCLRBC.Text.Trim();
if (!Regdecimal.IsMatch(txb_RETICS.Text))
{
txb_RETICS.Text = "0.00";
}
string itxbRETICS = txb_RETICS.Text.Trim();
if (!Regdecimal.IsMatch(txb_PLATELETS.Text))
{
txb_PLATELETS.Text = "0.00";
}
string itxbPLATELETS = txb_PLATELETS.Text.Trim();
if (!Regdecimal.IsMatch(txb_REFRACT.Text))
{
txb_REFRACT.Text = "0.00";
}
string itxbREFRACT = txb_REFRACT.Text.Trim();
if (!Regdecimal.IsMatch(txb_Blmor.Text))
{
txb_Blmor.Text = "0.00";
}
string itxbBlmor = txb_Blmor.Text.Trim();
////////////////////////////////////////
if (!Regdecimal.IsMatch(txb_BUN.Text))
{
txb_BUN.Text = "0.00";
}
string itxbBUN = txb_BUN.Text.Trim();
if (!Regdecimal.IsMatch(txb_CREATINNIE.Text))
{
txb_CREATINNIE.Text = "0.00";
}
string itxbCREATINNIE = txb_CREATINNIE.Text.Trim();
if (!Regdecimal.IsMatch(txb_GLUCOSE.Text))
{
txb_GLUCOSE.Text = "0.00";
}
string itxbGLUCOSE = txb_GLUCOSE.Text.Trim();
if (!Regdecimal.IsMatch(txb_AMYLASE.Text))
{
txb_AMYLASE.Text = "0.00";
}
string itxbAMYLASE = txb_AMYLASE.Text.Trim();
if (!Regdecimal.IsMatch(txb_ALT.Text))
{
txb_ALT.Text = "0.00";
}
string itxbALT = txb_ALT.Text.Trim();
if (!Regdecimal.IsMatch(txb_AST.Text))
{
txb_AST.Text = "0.00";
}
string itxbAST = txb_AST.Text.Trim();
if (!Regdecimal.IsMatch(txb_ALK.Text))
{
txb_ALK.Text = "0.00";
}
string itxbALK = txb_ALK.Text.Trim();
if (!Regdecimal.IsMatch(txb_CPK.Text))
{
txb_CPK.Text = "0.00";
}
string itxbCPK = txb_CPK.Text.Trim();
if (!Regdecimal.IsMatch(txb_BILIRUBIN.Text))
{
txb_BILIRUBIN.Text = "0.00";
}
string itxbBILIRUBIN = txb_BILIRUBIN.Text.Trim();
if (!Regdecimal.IsMatch(txb_DIRECT.Text))
{
txb_DIRECT.Text = "0.00";
}
string itxbDIRECT = txb_DIRECT.Text.Trim();
if (!Regdecimal.IsMatch(txb_INDIRECT.Text))
{
txb_INDIRECT.Text = "0.00";
}
string itxbINDIRECT = txb_INDIRECT.Text.Trim();
if (!Regdecimal.IsMatch(txb_CHOLESTEROL.Text))
{
txb_CHOLESTEROL.Text = "0.00";
}
string itxbCHOLESTEROL = txb_CHOLESTEROL.Text.Trim();
if (!Regdecimal.IsMatch(txb_TRIGLYCERIDE.Text))
{
txb_TRIGLYCERIDE.Text = "0.00";
}
string itxbTRIGLYCERIDE = txb_TRIGLYCERIDE.Text.Trim();
if (!Regdecimal.IsMatch(txb_BIURET.Text))
{
txb_BIURET.Text = "0.00";
}
string itxbBIURET = txb_BIURET.Text.Trim();
if (!Regdecimal.IsMatch(txb_ALBUMIN.Text))
{
txb_ALBUMIN.Text = "0.00";
}
string itxbALBUMIN = txb_ALBUMIN.Text.Trim();
if (!Regdecimal.IsMatch(txb_GLOBULIN.Text))
{
txb_GLOBULIN.Text = "0.00";
}
string itxbGLOBULIN = txb_GLOBULIN.Text.Trim();
////////////////////////////////////////////
if (!Regdecimal.IsMatch(txb_CALCIUM.Text))
{
txb_CALCIUM.Text = "0.00";
}
string itxbCALCIUM = txb_CALCIUM.Text.Trim();
if (!Regdecimal.IsMatch(txb_PHOSPHORUS.Text))
{
txb_PHOSPHORUS.Text = "0.00";
}
string itxbPHOSPHORUS = txb_PHOSPHORUS.Text.Trim();
if (!Regdecimal.IsMatch(txb_SODIUM.Text))
{
txb_SODIUM.Text = "0.00";
}
string itxbSODIUM = txb_SODIUM.Text.Trim();
if (!Regdecimal.IsMatch(txb_POTASSIUM.Text))
{
txb_POTASSIUM.Text = "0.00";
}
string itxbPOTASSIUM = txb_POTASSIUM.Text.Trim();
if (!Regdecimal.IsMatch(txb_CHLORIDE.Text))
{
txb_CHLORIDE.Text = "0.00";
}
string itxbCHLORIDE = txb_CHLORIDE.Text.Trim();
if (!Regdecimal.IsMatch(txb_AMMONIA.Text))
{
txb_AMMONIA.Text = "0.00";
}
string itxbAMMONIA = txb_AMMONIA.Text.Trim();
if (!Regdecimal.IsMatch(txb_OTHERS.Text))
{
txb_OTHERS.Text = "0.00";
}
string itxbOTHERS = txb_OTHERS.Text.Trim();
///////////////////////////////////////////
string itxbRemark = txb_bloodtestRemark.Text.Trim();
if ((ilbBloodTestID != string.Empty) && (ilbBloodTestID != null))
{
DialogResult iConfirmResult = MessageBox.Show("ต้องการแก้ไขข้อมูลผลเลือดหรือไม่?", "บันทึกข้อมูล..", MessageBoxButtons.YesNo);
if (iConfirmResult == DialogResult.Yes)
{
string isqlBloodTestEdit = "UPDATE `petshop`.`tb_bloodtest` SET `Bloodtest_Date`='" + idtpBlootTestDate + "', `Em_ID`='" + icbEmID + "', `HGB`='" + itxbHGB + "', `PCV`='" + itxbPCV + "', `RBC`='" + itxbRBC + "', `MCV`='" + itxbMCV + "', `MCHC`='" + itxbMCHC + "', `WBC`='" + itxbWBC + "', `MYCYTE`='" + itxbMYCYTE + "', `META`='" + itxbMETA + "', `BAND`='" + itxbBand + "', `SEGS`='" + itxbSEGS + "', `LYMPH`='" + itxbLYMPH + "', `MONO`='" + itxbMONO + "', `EOS`='" + itxbEOS + "', `BASO`='" + itxbBASO + "', `UNCLASS`='" + itxbUNCLASS + "', `NUCLRBC`='" + itxbNUCLRBC + "', `RETICS`='" + itxbRETICS + "', `PLATELETS`='" + itxbPLATELETS + "', `REFRACT`='" + itxbREFRACT + "', `blmor`='" + itxbBlmor + "', `BUN`='" + itxbBUN + "',"
+ " `CREATINNIE`='" + itxbCREATINNIE + "', `GLUCOSE`='" + itxbGLUCOSE + "', `AMYLASE`='" + itxbAMYLASE + "', `ALT`='" + itxbALT + "', `AST`='" + itxbAST + "', `ALK`='" + itxbALK + "', `CPK`='" + itxbCPK + "', `BILIRUBIN`='" + itxbBILIRUBIN + "', `DIRECT`='" + itxbDIRECT + "', `INDIRECT`='" + itxbINDIRECT + "', `CHOLESTEROL`='" + itxbCHOLESTEROL + "', `TRIGLYCERIDE`='" + itxbTRIGLYCERIDE + "', `BIURET`='" + itxbBIURET + "', `ALBUMIN`='" + itxbALBUMIN + "', `GLOBULIN`='" + itxbGLOBULIN + "', `CALCIUM`='" + itxbCALCIUM + "', `PHOSPHORUS`='" + itxbPHOSPHORUS + "', `SODIUM`='" + itxbSODIUM + "', `POTASSIUM`='" + itxbPOTASSIUM + "', `CHLORIDE`='" + itxbCHLORIDE + "', `AMMONIA`='" + itxbAMMONIA + "', `bloodtest_OTHERS`='" + itxbOTHERS + "', `bloodtest_remark`='" + itxbRemark + "' WHERE `Bloodtest_ID`='" + ilbBloodTestID + "'";
iConnect.Insert(isqlBloodTestEdit);
MessageBox.Show("ทำการข้อมูลแก้ไขแล้ว");
}
}
else
{
DialogResult iConfirmResult = MessageBox.Show("ต้องการเพิ่มข้อมูลผลเลือดหรือไม่?", "บันทึกข้อมูล..", MessageBoxButtons.YesNo);
if (iConfirmResult == DialogResult.Yes)
{
string isqlBloodTestAdd = "INSERT INTO `petshop`.`tb_bloodtest` (`Bloodtest_Date`, `Em_ID`, `Pet_ID`, `HGB`, `PCV`, `RBC`, `MCV`, `MCHC`, `WBC`, `MYCYTE`, `META`, `BAND`, `SEGS`, `LYMPH`, `MONO`, `EOS`, `BASO`, `UNCLASS`, `NUCLRBC`, `RETICS`, `PLATELETS`, `REFRACT`, `blmor`, `BUN`, `CREATINNIE`, `GLUCOSE`, `AMYLASE`, `ALT`, `AST`, `ALK`, `CPK`, `BILIRUBIN`, `DIRECT`, `INDIRECT`, `CHOLESTEROL`, `TRIGLYCERIDE`, `BIURET`, `ALBUMIN`, `GLOBULIN`, `CALCIUM`, `PHOSPHORUS`, `SODIUM`, `POTASSIUM`, `CHLORIDE`, `AMMONIA`, `bloodtest_OTHERS`, `bloodtest_remark`) " +
"VALUES ('" + idtpBlootTestDate + "', '" + icbEmID + "', '" + ilbPetID + "', '" + itxbHGB + "', '" + itxbPCV + "', '" + itxbRBC + "', '" + itxbMCV + "', '" + itxbMCHC + "', '" + itxbWBC + "', '" + itxbMYCYTE + "', '" + itxbMETA + "', '" + itxbBand + "', '" + itxbSEGS + "', '" + itxbLYMPH + "', '" + itxbMONO + "', '" + itxbEOS + "', '" + itxbBASO + "', '" + itxbUNCLASS + "', '" + itxbNUCLRBC + "', '" + itxbRETICS + "', '" + itxbPLATELETS + "', '" + itxbREFRACT + "', '" + itxbBlmor + "', '" + itxbBUN + "', '" + itxbCREATINNIE + "', '" + itxbGLUCOSE + "', '" + itxbAMYLASE + "', '" + itxbAST + "', '" + itxbAST + "', '" + itxbALK + "', '" + itxbCPK + "', '" + itxbBILIRUBIN + "', '" + itxbDIRECT + "', '" + itxbINDIRECT + "', '" + itxbCHOLESTEROL + "', '" + itxbTRIGLYCERIDE + "', '" + itxbBIURET + "', '" + itxbALBUMIN + "', '" + itxbGLOBULIN + "', '" + itxbCALCIUM + "', '" + itxbPHOSPHORUS + "', '" + itxbSODIUM + "', '" + itxbPOTASSIUM + "', '" + itxbCHLORIDE + "', '" + itxbAMMONIA + "', '" + itxbOTHERS + "', '" + itxbRemark + "')";
iConnect.Insert(isqlBloodTestAdd);
MessageBox.Show("บันทึกข้อมูลแล้ว");
DataTable idtBloodTest;
string isqlBloodTest = "SELECT * FROM petshop.tb_bloodtest ORDER BY Bloodtest_ID DESC LIMIT 1";
idtBloodTest = iConnect.SelectByCommand(isqlBloodTest);
lb_BloodTestID.Text = idtBloodTest.Rows[0].Field <Int32>(0).ToString();
}
}
}
private void SearchReport()
{
//////////////////////ข้อมูลองค์กร//////////////////////////
DataTable idtCompany;
string isqlCompany = "SELECT * FROM petshop.tb_company";
idtCompany = iConnect.SelectByCommand(isqlCompany);
////////////////////ช่วงเวลาที่โหลดข้อมูล//////////////////////////
System.Globalization.CultureInfo cultureInfo = new System.Globalization.CultureInfo("th-TH");
System.Threading.Thread.CurrentThread.CurrentCulture = cultureInfo;
System.Threading.Thread.CurrentThread.CurrentUICulture = cultureInfo;
ReportDate = dTP_Date.Value.ToString("dd MMMM yyyy");
ReportToDate = dTP_ToDate.Value.ToString("dd MMMM yyyy");
///////////////////ประกาศช่วงบนหัวเอกสาร///////////////////////
DataTable idtHead = new DataTable("date");
DataRow dr = null;
idtHead.Columns.Add(new DataColumn("ReportDate", typeof(string)));
idtHead.Columns.Add(new DataColumn("ReportToDate", typeof(string)));
dr = idtHead.NewRow();
dr["ReportDate"] = ReportDate;
dr["ReportToDate"] = ReportToDate;
idtHead.Rows.Add(dr);
///////////////////////////[[รายงานส่วนรายจ่าย]]//////////////////////////////////
DataTable idtOutlay;
string isqlOutlay = "SELECT tb_outlaydetail.*,tb_outlay.outlay_Detail,tb_employee.Em_Name FROM tb_outlaydetail,tb_outlay,tb_employee where tb_outlay.outlay_ID = tb_outlaydetail.outlay_ID AND tb_outlaydetail.Em_ID = tb_employee.Em_ID AND (STR_TO_DATE(`outlayDetail_Date`,'%Y-%m-%d') between '" + idtpDate + "' AND '" + idtpToDate + "' )";
idtOutlay = iConnect.SelectByCommand(isqlOutlay);
////////////////////แบบที่สอง/////////////////////////
//DataTable idtOutlayAmt;
//string isqlOutlayAmt = "SELECT tb_outlaydetail.outlay_ID,tb_outlay.outlay_Detail,tb_outlaydetail.outlayDetail_Amt FROM tb_outlaydetail,tb_outlay where tb_outlay.outlay_ID = tb_outlaydetail.outlay_ID AND (STR_TO_DATE(`outlayDetail_Date`,'%Y-%m-%d') between '" + idtpDate + "' AND '" + idtpToDate + "' ) group by Outlay_ID";
//idtOutlayAmt = iConnect.SelectByCommand(isqlOutlayAmt);
////////////////////////////////////////////////////
DataTable idtOutlayTotal;
string isqlOutlayTotal = "SELECT sum(outlayDetail_Amt) as outlayDetail_Total FROM petshop.tb_outlaydetail where (STR_TO_DATE(`outlayDetail_Date`,'%Y-%m-%d') between '" + idtpDate + "' AND '" + idtpToDate + "' )";
idtOutlayTotal = iConnect.SelectByCommand(isqlOutlayTotal);
ReportDocument rpt = new ReportDocument();
rpt.Load("CrOutlay.rpt");
rpt.SetDataSource(idtOutlay);
rpt.Subreports["Head_Sub_Report"].Database.Tables[0].SetDataSource(idtHead);
rpt.Subreports["Company_Sub_Report"].Database.Tables[0].SetDataSource(idtCompany);
rpt.Subreports["Outlay_Sub_Report"].Database.Tables[0].SetDataSource(idtOutlayTotal);
//rpt.Subreports["Outlay_Sub_ReportA"].Database.Tables[0].SetDataSource(idtOutlayAmt);
this.crViewerOutlay.ReportSource = rpt;
this.crViewerOutlay.Refresh();
/////////////////////////////////////[[รายงานส่วนยา]]//////////////////////////////////////////
DataTable idtMedi;
string isqlMedi = "SELECT tb_medirecord.Medi_ID as ProMedi_ID,tb_medicine.Medi_Des as ProMedi_Des, "
+ "sum(tb_medirecord.MediSale_Unit) as ProMedi_Unit,tb_medirecord.Medi_Sale as ProMedi_Sale, "
+ "sum(tb_medirecord.MediRecord_Total) as ProMedi_Total "
+ "FROM tb_medirecord,tb_medicine,tb_healrecord "
+ "where (tb_healrecord.HealRecord_Date between '" + idtpDate + "' AND '" + idtpToDate + "') "
+ "AND tb_healrecord.HealRecord_ID = tb_medirecord.HealRecord_ID AND (tb_medirecord.Medi_ID = tb_medicine.Medi_ID) group by ProMedi_ID";
idtMedi = iConnect.SelectByCommand(isqlMedi);
///////////////////////////////////////////////////
DataTable idtMediTotal;
string isqlMediTotal = "SELECT sum(tb_medirecord.MediRecord_Total) as ProMedi_Total, "
+ "(sum(tb_medirecord.MediRecord_Total)-sum(tb_medirecord.MediSale_Unit)*tb_medicine.Medi_Price) "
+ "as ProMedi_Price,(sum(tb_medirecord.MediRecord_Total))-(sum(tb_medirecord.MediRecord_Total) "
+ "- sum(tb_medirecord.MediSale_Unit)*tb_medicine.Medi_Price) as ProMedi_PriceTotal "
+ "FROM tb_medirecord,tb_medicine,tb_healrecord "
+ "where tb_medirecord.Medi_ID = tb_medicine.Medi_ID AND tb_medirecord.HealRecord_ID = tb_healrecord.HealRecord_ID "
+ "AND (tb_healrecord.HealRecord_Date between '" + idtpDate + "' AND '" + idtpToDate + "') ";
idtMediTotal = iConnect.SelectByCommand(isqlMediTotal);
/////////////////////////////////////////////////////////////
ReportDocument rptMedi = new ReportDocument();
rptMedi.Load("CrProMe.rpt");
rptMedi.SetDataSource(idtMedi);
rptMedi.Subreports["Head_Sub_Report"].Database.Tables[0].SetDataSource(idtHead);
rptMedi.Subreports["Company_Sub_Report"].Database.Tables[0].SetDataSource(idtCompany);
rptMedi.Subreports["ProMedi_Sub_Report"].Database.Tables[0].SetDataSource(idtMediTotal);
this.crViewerMedi.ReportSource = rptMedi;
this.crViewerMedi.Refresh();
////////////////////////////////[[รายงานส่วนสินค้า]]///////////////////////////////////////////////
DataTable idtProductSale;
string isqlProductSale = "SELECT tb_productsaledetail.Product_ID as ProMedi_ID,tb_Product.Product_Des as ProMedi_Des " +
",sum(productsale_Unit) as ProMedi_Unit,tb_productsaledetail.Product_Sale as ProMedi_Sale,sum(tb_productsaledetail.ProductSale_Total) as ProMedi_Total " +
"FROM tb_Product,tb_productsale,tb_productsaledetail " +
"WHERE (tb_productsale.ProductSale_Date between '" + idtpDate + "' AND '" + idtpToDate + "') " +
"AND tb_productsale.Productsale_ID = tb_productsaleDetail.ProductSale_ID AND (tb_Product.Product_ID = tb_productsaledetail.Product_ID) group by ProMedi_ID";
idtProductSale = iConnect.SelectByCommand(isqlProductSale);
/////////////////////////////////////////////////////////////////////////////////////
DataTable idtProductTotal;
string isqlProductTotal = "Select sum(tb_productsaledetail.productsale_Total)as ProMedi_Total, "
+ "(sum(tb_productsaledetail.productsale_total)-sum(tb_productsaledetail.productsale_Unit)*tb_product.Product_Price) as ProMedi_Price, "
+ "(sum(tb_productsaledetail.productsale_Total)) - (sum(tb_productsaledetail.productsale_total)-sum(tb_productsaledetail.productsale_Unit)*tb_product.Product_Price) as ProMedi_PriceTotal "
+ "from tb_productsaleDetail,tb_product,tb_productsale "
+ "where tb_productsaledetail.Product_ID = tb_product.Product_ID AND tb_productsale.ProductSale_ID = tb_productsaledetail.ProductSale_ID "
+ "AND (tb_productsale.ProductSale_Date between '" + idtpDate + "' AND '" + idtpToDate + "')";
idtProductTotal = iConnect.SelectByCommand(isqlProductTotal);
ReportDocument rptPro = new ReportDocument();
////////////////////////////////////////////////////////////////////////////////////
rptPro.Load("CrProMe.rpt");
rptPro.SetDataSource(idtProductSale);
rptPro.Subreports["Head_Sub_Report"].Database.Tables[0].SetDataSource(idtHead);
rptPro.Subreports["Company_Sub_Report"].Database.Tables[0].SetDataSource(idtCompany);
rptPro.Subreports["ProMedi_Sub_Report"].Database.Tables[0].SetDataSource(idtProductTotal);
this.crViewerProduct.ReportSource = rptPro;
this.crViewerProduct.Refresh();
//////////////////////////////[[ส่วนบริการ]]/////////////////////////////////////////
DataTable idtService;
string isqlService = "SELECT tb_servicerecord.Service_ID,tb_service.Service_Des, "
+ "count(tb_servicerecord.Service_ID) as Service_Unit,tb_servicerecord.Service_Amt as Service_Price, "
+ "sum(tb_servicerecord.Service_Amt) as Service_Total "
+ "FROM tb_service,tb_healrecord,tb_servicerecord "
+ "where tb_service.Service_ID = tb_servicerecord.Service_ID AND tb_healrecord.HealRecord_ID = tb_servicerecord.HealRecord_ID "
+ "AND (tb_healrecord.HealRecord_Date between '" + idtpDate + "' AND '" + idtpToDate + "') "
+ "group by Service_ID";
idtService = iConnect.SelectByCommand(isqlService);
DataTable idtServiceTotal;
string isqlServiceTotal = "SELECT sum(tb_servicerecord.Service_Amt) as Service_Total "
+ "FROM tb_healrecord,tb_servicerecord "
+ "where tb_healrecord.HealRecord_ID = tb_servicerecord.HealRecord_ID "
+ "AND (tb_healrecord.HealRecord_Date between '" + idtpDate + "' AND '" + idtpToDate + "') ";
idtServiceTotal = iConnect.SelectByCommand(isqlServiceTotal);
ReportDocument rptService = new ReportDocument();
////////////////////////////////////////////////////////////////////////////////////
rptService.Load("CrService.rpt");
rptService.SetDataSource(idtService);
rptService.Subreports["Head_Sub_Report"].Database.Tables[0].SetDataSource(idtHead);
rptService.Subreports["Company_Sub_Report"].Database.Tables[0].SetDataSource(idtCompany);
rptService.Subreports["Service_Sub_Report"].Database.Tables[0].SetDataSource(idtServiceTotal);
this.crViewerService.ReportSource = rptService;
this.crViewerService.Refresh();
}
private void loadProductSale()
{
DataTable idtProductSale;
string isqlProductSale = "SELECT tb_productSale.*,tb_employee.Em_Name FROM tb_productsale,tb_employee where tb_productsale.Em_ID = tb_employee.Em_ID";
idtProductSale = iConnect.SelectByCommand(isqlProductSale);
LoadThai();
dGV_ProductSale.DataSource = idtProductSale;
dGV_ProductSale.Refresh();
}