protected void btnClearGrid_OnClick(object sender, EventArgs e)
{
try
{
NewGeneratedBill.DataSource = null;
NewGeneratedBill.DataBind();
tableGeneratedBill.Clear();
lblmessage.Text = "";
ClientScript.RegisterStartupScript(this.GetType(), "alert('')", "HideGenerateBillPreview()", true);
}
catch (Exception ex)
{
lblmessage.Text = "Error Clearing Data";
}
}
protected void btnUpdateDB_OnClick(object sender, EventArgs e)
{
try
{
String SqlSocietyString = Utility.SocietyConnectionString;
SqlConnection sqlCon = new SqlConnection(SqlSocietyString);
sqlCon.Open();
using (SqlBulkCopy Billdata = new SqlBulkCopy(sqlCon))
{
Billdata.DestinationTableName = "dbo.GeneratedBill";
Billdata.ColumnMappings.Add("FlatID", "FlatID");
// Billdata.ColumnMappings.Add("FlatNumber", "FlatNumber");
Billdata.ColumnMappings.Add("SocietyBillID", "SocietyBillID");
Billdata.ColumnMappings.Add("CurrentBillAmount", "CurrentBillAmount");
Billdata.ColumnMappings.Add("CycleType", "CycleType");
Billdata.ColumnMappings.Add("PaymentDueDate", "PaymentDueDate");
//Amount to be paid is calculated
Billdata.ColumnMappings.Add("PreviousMonthBalance", "PreviousMonthBalance");
//Billdata.ColumnMappings.Add("ModifiedAt", "ModifiedAt");
Billdata.ColumnMappings.Add("BillDescription", "BillDescription");
Billdata.ColumnMappings.Add("BillStartDate", "BillStartDate");
Billdata.ColumnMappings.Add("BillEndDate", "BillEndDate");
Billdata.ColumnMappings.Add("BillMonth", "BillMonth");
Billdata.ColumnMappings.Add("SocietyID", "SocietyID");
Billdata.ColumnMappings.Add("ActionType", "ActionType");
Billdata.ColumnMappings.Add("Activated", "Activated");
Billdata.ColumnMappings.Add("ResID", "ResID");
Billdata.ColumnMappings.Add("AmountPaidDate", "AmountPaidDate");
Billdata.WriteToServer(tableGeneratedBill);
}
sqlCon.Close();
NewGeneratedBill.DataSource = null;
NewGeneratedBill.DataBind();
tableGeneratedBill.Clear();
lblmessage.Text = "The Data has been uploaded successfully.";
ClientScript.RegisterStartupScript(this.GetType(), "alert('')", "HideGenerateBillPreview()", true);
}
catch (Exception ex)
{
lblmessage.Text = "Error updating data base";
int a = 1;
}
}
protected void btnGenerateLatestBill_Click(object sender, EventArgs e)
{
try
{
int countCalulatedBill = 0;
int countInActiveBill = 0;
String BillType = drpGenBillForOnLatest.SelectedItem.Text;
String stringEndDate = newBillDate.Text;
DateTime billEndDate;
int BillTypeID = Convert.ToInt32(drpGenBillForOnLatest.SelectedItem.Value);
if (stringEndDate == "")
{
lblmessage.Text = "Select the Bill End Date";
return;
}
else
{
billEndDate = DateTime.ParseExact(stringEndDate, "dd-MM-yyyy", null);
}
GeneratedBillController billController = new GeneratedBillController();
List <GenerateBill> LatestBill = billController.GetLatestBills(BillTypeID, muser.currentResident.SocietyID);
List <GenerateBill> listCalculatedBill = new List <GenerateBill>();
for (int i = 0; i < LatestBill.Count; i++)
{
GenerateBill previousBill = LatestBill[i];
if (previousBill.op_Applyto == 1 || (previousBill.op_Applyto == 0 && previousBill.Activated == 2))
{
if (previousBill.BillEndDate > billEndDate)
{
countInActiveBill = countInActiveBill + 1;
}
else
{
GenerateBill calculatedBill = Bill.CalculateNewBill(previousBill, "Manual", billEndDate, 0);
listCalculatedBill.Add(calculatedBill);
tableGeneratedBill.Rows.Add(calculatedBill.FlatID, calculatedBill.op_FlatNumber, BillTypeID, BillType, calculatedBill.BillStartDate, calculatedBill.BillEndDate,
calculatedBill.PaymentDueDate, calculatedBill.PreviousMonthBalance, calculatedBill.CurrentBillAmount,
calculatedBill.SocietyBillID, calculatedBill.CycleType, "Bulk Generate", DateTime.Today, SocietyID, "Generate", 2, calculatedBill.ResID,
baseDate);
countCalulatedBill = countCalulatedBill + 1;
}
}
else
{
}
}
if (countCalulatedBill == 0)
{
lblmessage.Text = countCalulatedBill + " Bill calculated. " + countInActiveBill + " Bill not in date range";
}
else
{
lblmessage.Text = countCalulatedBill + " Bill calculated. " + countInActiveBill + " Bill not in date range";
NewGeneratedBill.DataSource = tableGeneratedBill;
NewGeneratedBill.DataBind();
ClientScript.RegisterStartupScript(this.GetType(), "alert('')", "ShowGenerateBillPreview()", true);
}
}
catch (Exception ex)
{
lblmessage.Text = "Error displaying calculated data";
}
}
protected void BillsUploadSubmit_Click(object sender, EventArgs e)
{
OleDbConnection Connection = null;
try
{
int countCalulatedBill = 0;
int countInActiveBill = 0;
if (BillsUpload.PostedFile.FileName == "")
{
lblmessage.Text = "Please Select a file.";
return;
}
String strTime = Utility.GetCurrentDateTimeinUTC().ToLongTimeString();
strTime = strTime.ToString().Replace(':', '-');
string path = strTime + "-" + BillsUpload.PostedFile.FileName;
String Extension = Path.GetExtension(BillsUpload.PostedFile.FileName);
path = Server.MapPath(Path.Combine("~/Data/", path));
BillsUpload.SaveAs(path);
String Connectionstring = String.Empty;
switch (Extension)
{
case ".xls":
Connectionstring = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
// Connectionstring = ConfigurationManager.ConnectionStrings["Excel07conString"].ConnectionString;
break;
case ".xlsx":
Connectionstring = ConfigurationManager.ConnectionStrings["Excel07conString"].ConnectionString;
break;
}
Connectionstring = String.Format(Connectionstring, path);
//Session["Path"] = path;
//Added by Aarshi on 18 auh 2017 for session storage
SessionVariables.Path = path;
Connection = new OleDbConnection(Connectionstring);
Connection.Open();
String insertQuery = "select * from [Sheet1$]";
OleDbDataAdapter adp = new OleDbDataAdapter(insertQuery, Connection);
DataTable DataExcel = new DataTable();
adp.Fill(DataExcel);
if (DataExcel.Rows.Count > 0)
{
foreach (DataRow ExcelRow in DataExcel.Rows)
{
try
{
int previousBillTypeID = 0;
SocietyBillPlan socBillPlan = new SocietyBillPlan();
int FlatID = Convert.ToInt32(ExcelRow["FlatID"]);
String FlatNumber = ExcelRow["FlatNumber"].ToString();
int BillTypeID = Convert.ToInt32(ExcelRow["BillTypeID"]);
String BillType = ExcelRow["BillType"].ToString();
DateTime BillStartDate = Convert.ToDateTime(ExcelRow["BillStartDate"]);
DateTime BillEndDate = Convert.ToDateTime(ExcelRow["BillEndDate"]);
DateTime PaymentDueDate = Convert.ToDateTime(ExcelRow["PaymentDueDate"]);
int PreviousMonthBalance = Convert.ToInt32(ExcelRow["PreviousMonthBalance"]);
int CurrentBillAmount = Convert.ToInt32(ExcelRow["CurrentBillAmount"]);
DateTime BillMonth = Convert.ToDateTime(ExcelRow["BillMonth"]);
int billResID = Convert.ToInt32(ExcelRow["ResID"]);
if (BillTypeID != previousBillTypeID)
{
BillPlan billPlan = new BillPlan();
socBillPlan = billPlan.GetPlan(BillTypeID, SocietyID);
}
previousBillTypeID = BillTypeID;
tableGeneratedBill.Rows.Add(FlatID, FlatNumber, BillTypeID, BillType, BillStartDate, BillEndDate, PaymentDueDate, PreviousMonthBalance, CurrentBillAmount,
socBillPlan.SocietyBillID, socBillPlan.CycleType, "Bulk Import", DateTime.Today.Date, SocietyID, "Import", 2, billResID, baseDate);
countCalulatedBill = countCalulatedBill + 1;
}
catch (Exception ex)
{
countInActiveBill = countInActiveBill + 1;
}
}
if (countCalulatedBill == 0)
{
lblmessage.Text = countCalulatedBill + " Bill calculated. " + countInActiveBill + " Bill not in date range";
}
else
{
lblmessage.Text = countCalulatedBill + " Bill calculated. " + countInActiveBill + " Bill not in date range";
NewGeneratedBill.DataSource = tableGeneratedBill;
NewGeneratedBill.DataBind();
ClientScript.RegisterStartupScript(this.GetType(), "alert('')", "ShowGenerateBillPreview()", true);
}
}
else
{
//lblNewvalues.Visible = false;
//NewValueScroll_Div.Visible = false;
}
//Ends here
}
catch (Exception ex)
{
lblmessage.Text = "Please check the file and data format. Error Detail: ex.Message";
//lblNewvalues.Visible = false;
//NewValueScroll_Div.Visible = false;
String message = ex.Message;
}
finally
{
if (Connection != null)
{
if (Connection.State == ConnectionState.Open)
{
Connection.Close();
}
Connection = null;
}
}
}