void LoadUsage() { if (dtCust != null && dtTermNo != null) { dtCurrentUsage = dataAccess.GetAccessDataSetByQuery("select * from tbl_usage where Cust_No = " + cmbCust.Text + " and term_no = " + cmbTermNo.Text).Tables[0]; if (dtCurrentUsage.Rows.Count > 0) { usageExistsInDB = true; txtCounter.Text = Convert.ToInt32(dtCurrentUsage.Rows[0]["Cntr_Liter"]).ToString(); txtWaterPrice.Text = Convert.ToInt32(dtCurrentUsage.Rows[0]["Wtr_Price"]).ToString(); txtGarbage.Text = Convert.ToInt32(dtCurrentUsage.Rows[0]["Garbage"]).ToString(); txtPartnership.Text = Convert.ToInt32(dtCurrentUsage.Rows[0]["Partnership"]).ToString(); txtRenovation.Text = Convert.ToInt32(dtCurrentUsage.Rows[0]["Renovation"]).ToString(); txtCommunion.Text = Convert.ToInt32(dtCurrentUsage.Rows[0]["Communion"]).ToString(); txtOther.Text = Convert.ToInt32(dtCurrentUsage.Rows[0]["Others"]).ToString(); txtDiscount.Text = Convert.ToInt32(dtCurrentUsage.Rows[0]["Discount"]).ToString(); txtSubscription.Text = Convert.ToInt32(dtCurrentUsage.Rows[0]["Subscription"]).ToString(); SetWaterPriceText(null, null); SetPriceSumText(null, null); } else { txtCounter.Text = mainForm.GetCntr_Liter((Convert.ToDouble(cmbTermNo.Text) - 1).ToString(), cmbCust.Text).ToString(); if (chkClearForm.Checked) { txtWaterPrice.Text = txtRenovation.Text = txtPartnership.Text = txtGarbage.Text = txtCommunion.Text = txtOther.Text = txtDiscount.Text = txtSubscription.Text = "0"; } usageExistsInDB = false; } } }
/* * private void EnableSorting() * { * label1.Visible = cmbSort.Visible = radioAns.Visible = radioDesc.Visible = true; * sortTexts = new List<string>(); * sortColumns = new List<string>(); * if (rptType == ReportType.ServiceTicket) * { * sortTexts.Add("نام"); * sortColumns.Add("FName"); * sortTexts.Add("نام خانوادگی"); * sortColumns.Add("LName"); * sortTexts.Add("شماره بلیط"); * sortColumns.Add("TicketCodeFull"); * sortTexts.Add("FirstName"); * sortColumns.Add("EngFName"); * sortTexts.Add("SurName"); * sortColumns.Add("EngLName"); * sortTexts.Add("رده سنی"); * sortColumns.Add("AgeGroup"); * sortTexts.Add("شماره قرارداد"); * sortColumns.Add("ContractCode"); * } * else if (rptType == ReportType.ServicePassports) * { * sortTexts.Add("نام(فارسی)"); * sortColumns.Add("PassportOwnerFullName"); * sortTexts.Add("نام(لاتین)"); * sortColumns.Add("PassportOwneEngFullName"); * sortTexts.Add("شماره پاسپورت"); * sortColumns.Add("PassportNo"); * sortTexts.Add("نوع عضویت"); * sortColumns.Add("MembershipType"); * sortTexts.Add("تاریخ اعتبار"); * sortColumns.Add("ValidityDate"); * sortTexts.Add("توضیحات"); * sortColumns.Add("PassengerDescription"); * } * foreach (string str in sortTexts) * cmbSort.Items.Add(str); * } */ /* * private void DisableSorting() * { * label1.Visible = cmbSort.Visible = radioAns.Visible = radioDesc.Visible = false; * previewControl.Dock = DockStyle.Fill; * } */ /* * private string GetSortStr() * { * if (!cmbSort.Visible) return ""; * string sortStr = sortColumns[cmbSort.SelectedIndex].ToString(); * if (radioDesc.Checked) sortStr += " DESC"; * return sortStr; * } */ /* * private void SortDtReport() * { * DataTable dtSorted = new DataTable(); * foreach (DataColumn dc in dtReport.Columns) * dtSorted.Columns.Add(dc.ColumnName, dc.DataType); * foreach (DataRow dr in dtReport.Select("", GetSortStr())) * { * if (rptType == ReportType.ServiceTicket) * dtSorted.Rows.Add(dr["TripServiceID"], dr["FName"], dr["LName"], dr["TicketCodeL"], dr["TicketCodeR"], dr["TicketCodeFull"], dr["EngFName"], * dr["EngLName"], dr["FromDate"], dr["ToDate"], dr["AgeGroup"], dr["ContractCode"]); * else if (rptType == ReportType.ServicePassports) * dtSorted.Rows.Add(dr["PassportNo"], dr["ExportPlace"], dr["ExportDate"], dr["ValidityDate"], dr["MembershipType"], dr["PassportOwnerFullName"], * dr["PassportOwneEngFullName"], dr["TripName"], dr["TripCode"], dr["FromDate"], dr["ToDate"], dr["AgeGroup"], dr["PassengerDescription"], dr["TripServiceID"]); * } * if (rptType == ReportType.ServiceTicket) report.RegisterData(dtSorted, "ViewServiceTickets"); * else if (rptType == ReportType.ServicePassports) report.RegisterData(dtSorted, "ViewServicePassports"); * * report.Show(); * dtReport = dtSorted; * } */ public void FastReportBill(int Term_No, string Cntr_No, string BillDesc) { //DisableSorting(); report.Load(Application.StartupPath + @"/FastReport/Bill.frx"); DataTable dt = mainForm.dataAccess.GetAccessDataSetByQuery("select * from Tbl_Usage inner join Tbl_Cust on Tbl_Usage.Cntr_No = Tbl_Cust.Cntr_No where Term_No=" + Term_No.ToString() + " AND Tbl_Usage.Cntr_No = " + Cntr_No).Tables[0]; dt.Columns.Add("Debt", typeof(double)); dt.Columns.Add("Credit", typeof(double)); dt.Columns.Add("FullName", typeof(string)); //dt.Columns.Add("Addr", typeof(string)); dt.Columns.Add("Cntr_Liter_Pre", typeof(int)); dt.Columns.Add("TermSum", typeof(double)); dt.Columns.Add("Sub500", typeof(double)); //dt.Columns.Add("BillSum", typeof(double)); dt.Columns.Add("TermStart", typeof(string)); dt.Columns.Add("TermStart_Pre", typeof(string)); dt.Columns.Add("TermPayable", typeof(double)); DataRow dr = dt.Rows[0]; double debt = 0, credit = 0; mainForm.CalcTermDebtCredit(out credit, out debt, Term_No, Cntr_No); dr["Debt"] = debt; dr["Credit"] = credit; dr["FullName"] = dr["FName"].ToString() + " " + dr["LName"].ToString(); DataTable dtPreUsage = da.GetAccessDataSetByQuery("select cntr_liter from tbl_usage where cntr_no =" + Cntr_No + " and term_no = " + (Term_No - 1).ToString()).Tables[0]; if (dtPreUsage.Rows.Count > 0) { dr["Cntr_Liter_Pre"] = Convert.ToInt16(dtPreUsage.Rows[0]["cntr_liter"]); } else { dr["Cntr_Liter_Pre"] = 0; } dr["TermSum"] = CalcTermSum(dr); dr["TermPayable"] = mainForm.CalcTermInstalment(Term_No, Cntr_No) + Convert.ToDouble(dr["Debt"]) - Convert.ToDouble(dr["Credit"]); dr["Sub500"] = CalcSub500(Convert.ToDouble(dr["TermPayable"])); dr["TermPayable"] = Convert.ToDouble(dr["TermPayable"]) - Convert.ToDouble(dr["Sub500"]); dr["TermStart"] = frmMain.GetTermStart(Term_No + 1); dr["TermStart_Pre"] = frmMain.GetTermStart(Term_No); report.Parameters[0].Value = dr["TermStart"].ToString().Substring(0, 2); report.Parameters[1].Value = frmMain.GetToDay("/"); report.Parameters[2].Value = frmMain.GetPayLimit("/"); //report.Parameters[2].Value = frmMain.GetTermStart(Term_No + 1); report.Parameters[3].Value = BillDesc; report.RegisterData(dt, "TblBill"); Text = "پیش نمایش قبوض"; mainForm.ProgressValue = 0; }
public string GetAccessDBField(string TableName, string RequestedCol, string CriteriaCol, int CriteriaVal) { CLSDataAccess da = new CLSDataAccess(); DataTable dt = da.GetAccessDataSetByQuery("select " + RequestedCol + " from " + TableName + " where " + CriteriaCol + "=" + CriteriaVal.ToString()).Tables[0]; if (dt.Rows.Count > 0) { return(dt.Rows[0][0].ToString()); } else { return(null); } }
private void LoadCust() { try { DataTable dt = dataAccess.GetAccessDataSetByQuery("select * from tbl_cust where cntr_no = " + cntr_No).Tables[0]; txtCntr_No.Text = cntr_No; txtCust_No.Text = dt.Rows[0]["Cust_No"].ToString(); txtFName.Text = dt.Rows[0]["FName"].ToString(); txtLName.Text = dt.Rows[0]["LName"].ToString(); txtAddr.Text = dt.Rows[0]["Addr"].ToString(); } catch { } }
//frmMain mainForm; public frmInstalmentInsertUpdate(string CntrNo, string TermNo, string Cust_No, string Cust_Name, string Pay_Balance) // Update Mode { InitializeComponent(); Text = "مدیریت اطلاعات تقسیط"; numTermNo.Minimum = int.Parse(TermNo); cntrNo = CntrNo; termNo = TermNo; modeIsUpdate = (dtInstalment = dataAccess.GetAccessDataSetByQuery("select *,'حذف' as cmdDelete from tbl_instalment where Cntr_No = " + cntrNo + " and Bill_Term_No = " + termNo + " order by Pay_Term_No").Tables[0]).Rows.Count > 0; grdInstalment.DataSource = dtInstalment.DefaultView; lblCustName.Text = Cust_Name; lblBillBalance.Text = Pay_Balance; lblTermNo.Text = TermNo; lblCntrNo.Text = CntrNo; billBalance = double.Parse(Pay_Balance); CalcFormBalances(); }
public static bool ExistsInTable(string TableName, string ColumnName, string Value, bool ValueIsNumeric) { try { string absoluteValue = Value; if (!ValueIsNumeric) { absoluteValue = "'" + Value + "'"; } CLSDataAccess da = new CLSDataAccess(); if ((da.GetAccessDataSetByQuery("select * from " + TableName + " where " + ColumnName + " = " + absoluteValue)).Tables[0].Rows.Count > 0) { return(true); } else { return(false); } } catch { return(false); } }
private bool InsertUpdateAction() { if (CLSValidityCheck.IsInt(txtPayBalance) && CLSValidityCheck.IsDate(mskPayDate)) { string query = ""; if (modeIsUpdate) { query += "update tbl_payment set "; query += "Pay_Balance =" + txtPayBalance.Text.Trim() + ","; query += "Pay_Date = '" + (mskPayDate.Text.Trim()) + "'"; query += " where Cust_No = " + cmbCust.Text + " and Pay_Date = '" + original_Pay_Date + "' and Pay_Balance = " + original_Pay_Balance; } else { string Cntr_No = ""; try { Cntr_No = dataAccess.GetAccessDataSetByQuery("select Cntr_No from tbl_Cust where cust_No = " + cmbCust.Text).Tables[0].Rows[0]["Cntr_No"].ToString(); } catch { MessageBox.Show("مشتری با این شماره وجود ندارد", "", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1, MessageBoxOptions.RightAlign); return(false); } query += "insert into tbl_payment (cust_No,cntr_No,Pay_Date,Pay_Balance)"; query += " values (" + cmbCust.Text.Trim() + "," + Cntr_No + ",'" + mskPayDate.Text.Trim() + "'," + txtPayBalance.Text + ")"; } dataAccess.ExecuteAccess(query); if (!modeIsUpdate) { if (cmbCust.SelectedIndex < dtCust.Rows.Count - 1) { cmbCust.SelectedIndex++; } if (mskPayDate.Enabled) { mskPayDate.Select(); mskPayDate.SelectAll(); } else { txtPayBalance.Select(); txtPayBalance.SelectAll(); } } else { DialogResult = DialogResult.OK; //Close(); } } else { MessageBox.Show("لطفاً مقادیر وارد شده را تصحیح نمایید"); return(false); } return(true); }
public double CalcTermDebt(int TermNo, string Cntr_No) { double debtSum = 0; string query1 = "select sum(Subscription+Garbage+Wtr_Price+Partnership-Discount+Renovation+Communion+Others) as BillSum from tbl_usage where " + "cntr_no = " + Cntr_No + " and term_no < " + TermNo.ToString(); string query2 = "select sum(Balance) as Instalment_Total from tbl_instalment where cntr_no = " + Cntr_No + " and Bill_Term_No < " + TermNo.ToString(); string query3 = "select sum(Balance) as Instalment_Payable from tbl_instalment where cntr_no = " + Cntr_No + " and Pay_Term_No < " + TermNo.ToString(); DataTable dt1 = dataAccess.GetAccessDataSetByQuery(query1).Tables[0]; //DataTable dt2 = dataAccess.GetAccessDataSetByQuery(query2).Tables.Count > 0 ? dataAccess.GetAccessDataSetByQuery(query2).Tables[0] : null; //DataTable dt3 = dataAccess.GetAccessDataSetByQuery(query3).Tables.Count > 0 ? dataAccess.GetAccessDataSetByQuery(query3).Tables[0] : null; DataTable dt2 = dataAccess.GetAccessDataSetByQuery(query2).Tables[0]; DataTable dt3 = dataAccess.GetAccessDataSetByQuery(query3).Tables[0]; DataRow dr1 = dt1.Rows[0]; DataRow dr2 = dt2.Rows[0]; DataRow dr3 = dt3.Rows[0]; double instalment_total = dr2["Instalment_Total"].ToString() == "" ? 0 : Convert.ToDouble(dr2["Instalment_Total"]); double instalment_payable = dr3["Instalment_Payable"].ToString() == "" ? 0 : Convert.ToDouble(dr3["Instalment_Payable"]); try { debtSum = Convert.ToDouble(dr1["BillSum"]) - instalment_total + instalment_payable; //MessageBox.Show(debtSum.ToString() + " = " + dr1["BillSum"].ToString () +"-"+instalment_total.ToString ()+"+"+instalment_payable.ToString ()); return(debtSum); } catch { return(0); } }