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(); }