<%@ Page Title="" Language="C#" MasterPageFile="~/Site1.Master" AutoEventWireup="true" CodeBehind="Daily_Updation.aspx.cs" Inherits="Loan_Charge.WebForm2" %>
<%--<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="asp" %>--%>
<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" runat="server">
<td align="left" valign="middle" style="background-color: #FF0000; height: 50px">
Height="20px" ontextchanged="TextBox1_TextChanged"></asp:TextBox>
<asp:Button ID="Button1" runat="server" Text="Search" onclick="Button1_Click"
Text="Show Total Collection" onclick="Button20_Click" />
<asp:Label ID="Label6" runat="server" ForeColor="White" Font-Size="Large" Font-Bold="true" Text=""></asp:Label>
AllowSorting="False" DataKeyNames="File_No" RowHeaderColumn="Operation"
onselectedindexchanged="GridView1_SelectedIndexChanged" Font-Size="Small" >
<asp:TemplateField HeaderText="S. No." ><ItemStyle Width="5%" HorizontalAlign="Center" /><HeaderStyle HorizontalAlign="Center" />
<asp:TemplateField HeaderStyle-Width="100PX"><ItemStyle Width="10%" HorizontalAlign="Center" /><HeaderStyle HorizontalAlign="Center" />
<asp:Label ID="Label3" runat="server" Text="File No."></asp:Label>
<asp:Label ID="Label2" runat="server" Text='<%#Eval("File_No")%>'></asp:Label>
<asp:TemplateField HeaderStyle-Width="100PX"><ItemStyle Width="20%" />
<asp:Label ID="Label1" runat="server" Text="Customer Name"></asp:Label>
<asp:Label ID="Label4" runat="server" Text='<%#Eval("Customer_Name")%>'></asp:Label>
<asp:TemplateField HeaderStyle-Width="100PX"><ItemStyle Width="15%" />
<asp:Label ID="C_Amount" runat="server" Text="Collection"></asp:Label>
<asp:TextBox ID="C_Amount1" runat="server" Height="22px" Width="80px" Text="" ForeColor="Black" Font-Size="Medium"></asp:TextBox>
<asp:TemplateField HeaderStyle-Width="100PX"><ItemStyle Width="15%" />
<asp:Label ID="Remarks" runat="server" Text="Remarks"></asp:Label>
<asp:TextBox ID="Remarks1" runat="server" Height="22px" ForeColor="Black" Font-Size="Medium" Width="100px"></asp:TextBox>
<asp:TemplateField HeaderStyle-Height="18PX"><ItemStyle Width="25%" />
<asp:Label ID="Label5" runat="server" Text="Command"></asp:Label>
<asp:LinkButton ID="LinkButton3" CommandName="Insert" CommandArgument="<%# ((GridViewRow) Container).RowIndex %>" Font-Bold="True" CssClass="cccc" runat="server" Font-Size="Small" Font-Underline="False" Width="50px" BorderColor="Black" BorderWidth="1px"> Insert</asp:LinkButton>
<asp:LinkButton ID="LinkButton1" CommandName="up" CommandArgument="<%# ((GridViewRow) Container).RowIndex %>" Font-Bold="True" CssClass="cccc" runat="server" Font-Size="Small" Font-Underline="False" BorderWidth="1px" Width="50px"> Update</asp:LinkButton>
<asp:LinkButton ID="LinkButton2" CommandName="de" CommandArgument="<%# ((GridViewRow) Container).RowIndex %>" Font-Bold="True" runat="server" Font-Size="Small" Font-Underline="False" CssClass="cccc" Width="50px" BorderWidth="1px"> Delete</asp:LinkButton>
BorderWidth="1px" Font-Bold="True" Font-Size="Large" Height="25px" ForeColor="White" BorderColor="Black" />
<RowStyle BackColor="#CCCCCC" BorderWidth="2px" ForeColor="Black"
Height="50px" Font-Size="Large" BorderColor="Black" BorderStyle="Solid" />
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace Loan_Charge
{
public partial class WebForm2 : System.Web.UI.Page
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ToString());
SqlDataAdapter da;
DataTable dt;
SqlCommand cmd;
protected void Page_Load(object sender, EventArgs e)
{
string username = "";
string role = "";
try
{
username = Session["username"].ToString();
role = Session["role"].ToString();
}
catch (Exception)
{
}
try
{
if (username != "")
{
if (role == "admin")
{
Response.Write("<Script Language=JavaScript>alert('You do not have authority !')</Script>");
Response.Redirect("Home.aspx", false);
Context.ApplicationInstance.CompleteRequest();
}
else
{
if (!IsPostBack)
{
showdata1();
}
}
}
}
catch (Exception ex)
{
Response.Redirect("Default.aspx");
}
}
protected void showdata(string rnumber)
{
try
{
da = new SqlDataAdapter("select File_No,Customer_Name from CustomerDetails where Root_No='" + rnumber + "' and FStatus='Active' order by File_No asc", con);
dt = new DataTable();
da.Fill(dt);
GridView1.DataSource = dt;
GridView1.DataBind();
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
}
protected void showdata1()
{
try
{
da = new SqlDataAdapter("select File_No,Customer_Name from CustomerDetails where FStatus='Active' order by File_No asc", con);
dt = new DataTable();
da.Fill(dt);
GridView1.DataSource = dt;
GridView1.DataBind();
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
}
protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
{
try
{
if (e.CommandName == "Insert")
{
int id = int.Parse(e.CommandArgument.ToString());
Label fileno = (Label)GridView1.Rows[id].FindControl("Label2");
Label cname = (Label)GridView1.Rows[id].FindControl("Label4");
TextBox colamount = (TextBox)GridView1.Rows[id].FindControl("C_Amount1");
TextBox remarks = (TextBox)GridView1.Rows[id].FindControl("Remarks1");
if (colamount.Text != "")
{
cmd = new SqlCommand("select * from Page4 where File_No=@File_No and Dte=@Dte", con);
cmd.Parameters.AddWithValue("@File_No", fileno.Text);
DateTime ddd = Convert.ToDateTime(DateTime.UtcNow.AddHours(5.5).ToString("yyyy-MM-dd"));
cmd.Parameters.AddWithValue("@Dte", ddd);
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
con.Close();
Response.Write("<script LANGUAGE='JavaScript' >alert('Data already inserted!')</script>");
}
else
{
con.Close();
cmd = new SqlCommand("insert into Page4(File_No,Customer_Name,C_Amount,Remarks,Dte) values(@File_No,@Customer_Name,@C_Amount,@Remarks,@Dte)", con);
cmd.Parameters.AddWithValue("@File_No", fileno.Text);
cmd.Parameters.AddWithValue("@Customer_Name", cname.Text);
cmd.Parameters.AddWithValue("@C_Amount", colamount.Text);
cmd.Parameters.AddWithValue("@Remarks", remarks.Text);
cmd.Parameters.AddWithValue("@Dte", DateTime.UtcNow.AddHours(5.5).ToShortDateString());
con.Open();
int i= cmd.ExecuteNonQuery();
con.Close();
if (i > 0)
{
updatedata(fileno.Text);
Response.Write("<script LANGUAGE='JavaScript' >alert('Insert successfully!')</script>");
}
}
}
else
{
Response.Write("<script LANGUAGE='JavaScript' >alert('Please fill amount first!')</script>");
}
}
if (e.CommandName == "up")
{
int id = int.Parse(e.CommandArgument.ToString());
Label fileno = (Label)GridView1.Rows[id].FindControl("Label2");
//Label cname = (Label)GridView1.Rows[id].FindControl("Label4");
TextBox colamount = (TextBox)GridView1.Rows[id].FindControl("C_Amount1");
TextBox remarks = (TextBox)GridView1.Rows[id].FindControl("Remarks1");
if (colamount.Text != "")
{
cmd = new SqlCommand("update Page4 set C_Amount=@C_Amount,Remarks=@Remarks where File_No=@File_No and Dte=@Dte", con);
cmd.Parameters.AddWithValue("@File_No", fileno.Text);
//cmd.Parameters.AddWithValue("@Customer_Name", cname.Text);
cmd.Parameters.AddWithValue("@C_Amount", colamount.Text);
cmd.Parameters.AddWithValue("@Remarks", remarks.Text);
cmd.Parameters.AddWithValue("@Dte", DateTime.UtcNow.AddHours(5.5).ToString("yyyy/MM/dd"));
con.Open();
int i = cmd.ExecuteNonQuery();
con.Close();
if (i>0)
{
updatedata(fileno.Text);
Response.Write("<script LANGUAGE='JavaScript' >alert('Update successfully!')</script>");
}
else
{
Response.Write("<script LANGUAGE='JavaScript' >alert('Data is not available!')</script>");
}
}
else
{
Response.Write("<script LANGUAGE='JavaScript' >alert('Please fill amount first!')</script>");
}
}
if (e.CommandName == "de")
{
int id = int.Parse(e.CommandArgument.ToString());
Label fileno = (Label)GridView1.Rows[id].FindControl("Label2");
cmd = new SqlCommand("delete from Page4 where File_No=@File_No and Dte=@Dte", con);
cmd.Parameters.AddWithValue("@File_No", fileno.Text);
cmd.Parameters.AddWithValue("@Dte", DateTime.UtcNow.AddHours(5.5).ToString());
con.Open();
int i= cmd.ExecuteNonQuery();
con.Close();
if (i>0)
{
updatedata(fileno.Text);
Response.Write("<script LANGUAGE='JavaScript' >alert('Delete successfully!')</script>");
}
else
{
Response.Write("<script LANGUAGE='JavaScript' >alert('Data is not available!')</script>");
}
}
}
catch (Exception ex)
{
con.Close();
}
}
protected void Button1_Click(object sender, EventArgs e)
{
try
{
if (TextBox1.Text != "")
{
showdata(TextBox1.Text);
}
else
{
showdata1();
}
}
catch (Exception) { }
}
protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
{
}
protected void TextBox1_TextChanged(object sender, EventArgs e)
{
try
{
//if (TextBox1.Text != "")
// {
showdata(TextBox1.Text);
//}
//else
//{
// showdata1();
//}
}
catch (Exception) { }
}
protected void updatedata( string filenoo)
{
try
{
da = new SqlDataAdapter("select * from CustomerDetails where File_No='"+filenoo+"'", con);
dt = new DataTable();
da.Fill(dt);
for (int i = 0; i < dt.Rows.Count; i++)
{
try
{
string fileno = dt.Rows[i][1].ToString();
double tot_rec_amount = Math.Round(Convert.ToDouble(dt.Rows[i]["FReceivable_Amount"]), 2);
//int loan_duration = Convert.ToInt32(dt.Rows[i]["FReceivable_Amount"]);
DateTime ope_date = Convert.ToDateTime(dt.Rows[i]["FDate_Of_Opening"]);
DateTime clo_date = Convert.ToDateTime(dt.Rows[i]["FDate_Of_Closing"]);
string mod_of_pay = dt.Rows[i]["FLoan_Mode"].ToString();
double eddi = Math.Round(Convert.ToDouble(dt.Rows[i]["FEDI"]), 2);
int update = updateddate(ope_date, mod_of_pay, clo_date);
double payable_amount = payableamount(ope_date, clo_date, tot_rec_amount, mod_of_pay, eddi);
double paidamount = totalpaid(fileno);
double shortexcess = (paidamount - payable_amount);
DateTime last_pay_dat = lastpaymentdate(fileno, ope_date);
double totaldue = (tot_rec_amount - paidamount);
cmd = new SqlCommand("update balance set short_excess=@short_excess,last_pay_date=@last_pay_date,total_due=@total_due where fileno=@fileno", con);
cmd.Parameters.AddWithValue("@short_excess", shortexcess);
cmd.Parameters.AddWithValue("@last_pay_date", last_pay_dat);
cmd.Parameters.AddWithValue("@total_due", totaldue);
cmd.Parameters.AddWithValue("@fileno", fileno);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
if (totaldue <= 0)
{
updatestatus(fileno);
}
if (update == 0)
{
updatestateon(fileno.ToString());
}
else
{
updatestateoff(fileno.ToString());
}
}
catch (Exception)
{
con.Close();
}
}
}
catch (Exception)
{
con.Close();
}
}
protected double totalpaid(string fno)
{
double totp = 0;
try
{
cmd = new SqlCommand("select Sum(C_Amount) from Page4 where File_No=@File_No", con);
cmd.Parameters.AddWithValue("@File_No", fno.ToString());
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
totp = Math.Round(Convert.ToDouble(dr[0]), 2);
}
con.Close();
}
catch (Exception)
{
con.Close();
totp = 0;
}
return totp;
}
protected int updateddate(DateTime odate, string modpay, DateTime cdate)
{
int baldate = 0;
try
{
TimeSpan ts = (DateTime.UtcNow.AddHours(5.5) - odate);
int bal_date = Convert.ToInt32(ts.Days.ToString());
TimeSpan tsc = (cdate - DateTime.UtcNow.AddHours(5.5));
int c_date = Convert.ToInt32(tsc.Days.ToString());
if (c_date > 0)
{
if (modpay == "Daily")
{
baldate = (bal_date % 1);
}
if (modpay == "Weekly")
{
baldate = (bal_date % 7);
}
if (modpay == "15 Days")
{
baldate = (bal_date % 15);
}
if (modpay == "Monthly")
{
baldate = (bal_date % 30);
}
}
else
{
baldate = 1;
}
}
catch (Exception)
{
con.Close();
}
return baldate;
}
protected double payableamount(DateTime odate, DateTime cdate, double totrec_amount, string modpay, double eddi)
{
double paya = 0;
try
{
TimeSpan ts = (DateTime.UtcNow.AddHours(5.5) - odate);
TimeSpan cts = (cdate - DateTime.UtcNow.AddHours(5.5));
int bal_date;
int cbal_date = Convert.ToInt32(cts.Days.ToString());
if (cbal_date > 0)
{
bal_date = Convert.ToInt32(ts.Days.ToString());
if (modpay == "Daily")
{
paya = ((bal_date + 1) * eddi);
}
if (modpay == "Weekly")
{
int bald = (bal_date / 7) + 1;
paya = (bald * eddi);
}
if (modpay == "15 Days")
{
int bald = (bal_date / 15) + 1;
paya = (bald * eddi);
}
if (modpay == "Monthly")
{
int bald = (bal_date / 30) + 1;
paya = (bald * eddi);
}
}
else
{
paya = totrec_amount;
}
}
catch (Exception)
{
con.Close();
}
return paya;
}
protected DateTime lastpaymentdate(string fileno, DateTime dt1)
{
DateTime last_pay_date = dt1;
try
{
cmd = new SqlCommand("select MAX(Dte) from Page4 where File_No=@File_No", con);
cmd.Parameters.AddWithValue("@File_No", fileno);
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
last_pay_date = Convert.ToDateTime(dr[0]);
}
con.Close();
}
catch (Exception)
{
con.Close();
last_pay_date = dt1;
}
return last_pay_date;
}
protected void updatestatus(string fileno)
{
try
{
string fs = "Deactive";
cmd = new SqlCommand("update CustomerDetails set FStatus=@FStatus where File_No=@File_No", con);
cmd.Parameters.AddWithValue("@FStatus", fs);
cmd.Parameters.AddWithValue("@File_No", fileno);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
catch (Exception)
{
con.Close();
}
}
protected void updatestateon(string fileno)
{
try
{
int fs = 1;
cmd = new SqlCommand("update CustomerDetails set State=@State where File_No=@File_No", con);
cmd.Parameters.AddWithValue("@State", fs);
cmd.Parameters.AddWithValue("@File_No", fileno);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
catch (Exception)
{
con.Close();
}
}
protected void updatestateoff(string fileno)
{
try
{
int fs = 0;
cmd = new SqlCommand("update CustomerDetails set State=@State where File_No=@File_No", con);
cmd.Parameters.AddWithValue("@State", fs);
cmd.Parameters.AddWithValue("@File_No", fileno);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
catch (Exception)
{
con.Close();
}
}
protected void Button20_Click(object sender, EventArgs e)
{
try
{
da = new SqlDataAdapter("select SUM(C_Amount) as total from Page4 where Dte='" + DateTime.UtcNow.AddHours(5.5).ToString("MM/dd/yyyy") + "'", con);
dt = new DataTable();
da.Fill(dt);
if (dt.Rows.Count > 0)
{
Label6.Text = Math.Round(Convert.ToDouble(dt.Rows[0]["total"]), 2).ToString();
}
else
{
Label6.Text = "0";
}
}
catch (Exception)
{
Label6.Text = "0";
}
}
}
}
