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