public static List <BalanceStatement> GetByOrgId(int OrgId)
{
DBCon con = new DBCon();
SqlCommand cmd = null;
SqlDataReader SDR = null;
List <BalanceStatement> BalanceStatementList = new List <BalanceStatement>();
try
{
string Quary = "Select * from ACBalanceStatement where OrgId=" + OrgId + " ORDER BY Date ASC";
cmd = new SqlCommand(Quary, con.Con);
SDR = cmd.ExecuteReader();
while (SDR.Read())
{
BalanceStatement OBJBS = new BalanceStatement();
OBJBS.BID = SDR.GetInt32(0);
OBJBS.Date = SDR.GetDateTime(1);
OBJBS.Amount = SDR.GetDouble(2);
OBJBS.Narration = SDR.GetString(3);
OBJBS.OrderId = SDR.GetInt32(4);
OBJBS.OrgId = SDR.GetInt32(5);
OBJBS.CRAmount = SDR.GetDouble(6);
OBJBS.EntryNo = SDR.GetInt32(7);
OBJBS.Balance = SDR.GetDouble(8);
BalanceStatementList.Add(OBJBS);
}
}
catch (Exception e) { e.ToString(); }
finally { cmd.Dispose(); con.Con.Close(); }
return(BalanceStatementList);
}
public static List <BalanceStatement> GetDataBYDate(int OrgId, DateTime Fdate, DateTime ToDate)
{
DBCon con = new DBCon();
SqlCommand cmd = null;
SqlDataReader SDR = null;
BalanceStatement OBJBS = new BalanceStatement();
List <BalanceStatement> BalanceStatementList = new List <BalanceStatement>();
try
{
BalanceStatement BAS = BalanceStatement.GetByOrgId(OrgId)
.Where(w => w.Date.Date < Fdate.Date).OrderByDescending(d => d.BID).FirstOrDefault();
if (BAS != null)
{
OBJBS.Date = BAS.Date;
OBJBS.Narration = "Opening Balance";
OBJBS.Balance = BAS.Balance;
BalanceStatementList.Add(OBJBS);
}
else
{
BalanceStatement BAS1 = BalanceStatement.GetByOrgId(OrgId).FirstOrDefault();
OBJBS.Date = BAS1.Date;
OBJBS.Narration = BAS1.Narration;
OBJBS.Balance = BAS1.Balance;
BalanceStatementList.Add(OBJBS);
}
var tDate = new DateTime(ToDate.Year, ToDate.Month, ToDate.Day, 23, 59, 00);
string Quary = "Select * from ACBalanceStatement where OrgId=" + OrgId + " and Date between '" + Fdate.ToString("MM/dd/yyyy") + "' and '" + tDate.ToString("MM/dd/yyyy HH:mm:ss") + "' ORDER BY Date ASC";
cmd = new SqlCommand(Quary, con.Con);
SDR = cmd.ExecuteReader();
while (SDR.Read())
{
OBJBS = new BalanceStatement();
OBJBS.BID = SDR.GetInt32(0);
OBJBS.Date = SDR.GetDateTime(1);
OBJBS.Amount = SDR.GetDouble(2);
OBJBS.Narration = SDR.GetString(3);
OBJBS.OrderId = SDR.GetInt32(4);
OBJBS.OrgId = SDR.GetInt32(5);
OBJBS.CRAmount = SDR.GetDouble(6);
OBJBS.EntryNo = SDR.GetInt32(7);
OBJBS.Balance = SDR.GetDouble(8);
BalanceStatementList.Add(OBJBS);
}
}
catch (Exception e) { e.ToString(); }
finally { cmd.Dispose(); con.Con.Close(); }
return(BalanceStatementList);
}
public int Save()
{
int Row = 0;
DBCon con = new DBCon();
SqlCommand cmd = null;
string Quary = "";
try
{
if (this.ID == 0)
{
Quary = "Insert Into ACLedger Values (@Name,@ShortName,@MobileNo1,@MobileNo2,@DebtorType,@OrgId,@State,@MarginOnCash,@TaxOnAboveMargin,@MarginOnline,@TaxOnAboveMarginOnline,@PaymentFrequency,@PaymentDay,@CollectionFrequency,@CollectionDay,@CalculationStartFrom,@TDSApplicable,@Email,@Remarks,@LisenceRenewalDate,@ParentGroup,@AccountNumber,@IFSCCode,@BankName,@Branch,@ManualPaymentDays,@ManualCollectionDays,@YearlyRenewalCharges,@Tax,@ReceivePaymentFromPOS);SELECT SCOPE_IDENTITY();";
}
else
{
Quary = "Update ACLedger Set Name=@Name,ShortName=@ShortName,MobileNo1=@MobileNo1,MobileNo2=@MobileNo2,DebtorType=@DebtorType,OrgId=@OrgId,State=@State,MarginOnCash=@MarginOnCash,TaxOnAboveMargin=@TaxOnAboveMargin,MarginOnline=@MarginOnline,TaxOnAboveMarginOnline=@TaxOnAboveMarginOnline,PaymentFrequency=@PaymentFrequency,PaymentDay=@PaymentDay,CollectionFrequency=@CollectionFrequency,CollectionDay=@CollectionDay,CalculationStartFrom=@CalculationStartFrom,TDSApplicable=@TDSApplicable,Email=@Email,Remarks=@Remarks,LisenceRenewalDate=@LisenceRenewalDate,ParentGroup=@ParentGroup,AccountNumber=@AccountNumber,IFSCCode=@IFSCCode,BankName=@BankName,Branch=@Branch,ManualPaymentDays=@ManualPaymentDays," +
"ManualCollectionDays=@ManualCollectionDays,YearlyRenewalCharges=@YearlyRenewalCharges,Tax=@Tax,ReceivePaymentFromPOS=@ReceivePaymentFromPOS where ID=@ID";
}
cmd = new SqlCommand(Quary, con.Con);
cmd.Parameters.AddWithValue("@ID", this.ID);
cmd.Parameters.AddWithValue("@Name", this.Name);
cmd.Parameters.AddWithValue("@ShortName", this.ShortName);
cmd.Parameters.AddWithValue("@MobileNo1", this.MobileNo1);
cmd.Parameters.AddWithValue("@MobileNo2", this.MobileNo2);
cmd.Parameters.AddWithValue("@DebtorType", this.DebtorType);
cmd.Parameters.AddWithValue("@OrgId", this.OrgId);
cmd.Parameters.AddWithValue("@State", this.State);
cmd.Parameters.AddWithValue("@MarginOnCash", this.MarginOnCash);
cmd.Parameters.AddWithValue("@TaxOnAboveMargin", this.TaxOnAboveMargin);
cmd.Parameters.AddWithValue("@MarginOnline", this.MarginOnline);
cmd.Parameters.AddWithValue("@TaxOnAboveMarginOnline", this.TaxOnAboveMarginOnline);
cmd.Parameters.AddWithValue("@PaymentFrequency", this.PaymentFrequency);
cmd.Parameters.AddWithValue("@PaymentDay", this.PaymentDay);
cmd.Parameters.AddWithValue("@CollectionFrequency", this.CollectionFrequency);
cmd.Parameters.AddWithValue("@CollectionDay", this.CollectionDay);
cmd.Parameters.AddWithValue("@CalculationStartFrom", this.CalculationStartFrom);
cmd.Parameters.AddWithValue("@TDSApplicable", this.TDSApplicable);
cmd.Parameters.AddWithValue("@Email", this.Email);
cmd.Parameters.AddWithValue("@Remarks", this.Remarks);
cmd.Parameters.AddWithValue("@LisenceRenewalDate", this.LisenceRenewalDate);
cmd.Parameters.AddWithValue("@ParentGroup", this.ParentGroup);
cmd.Parameters.AddWithValue("@AccountNumber", this.AccountNumber);
cmd.Parameters.AddWithValue("@IFSCCode", this.IFSCCode);
cmd.Parameters.AddWithValue("@BankName", this.BankName);
cmd.Parameters.AddWithValue("@Branch", this.BankName);
cmd.Parameters.AddWithValue("@ManualPaymentDays", this.ManualPaymentDays);
cmd.Parameters.AddWithValue("@ManualCollectionDays", this.ManualCollectionDays);
cmd.Parameters.AddWithValue("@YearlyRenewalCharges", this.YearlyRenewalCharges);
cmd.Parameters.AddWithValue("@Tax", this.Tax);
cmd.Parameters.AddWithValue("@ReceivePaymentFromPOS", this.ReceivePaymentFromPOS);
if (this.ID == 0)
{
Row = Convert.ToInt32(cmd.ExecuteScalar());
this.ID = Row;
if (DebtorType == 1 && ParentGroup == 2)
{
HG_OrganizationDetails org = new HG_OrganizationDetails();
org = org.GetOne(OrgId);
if (org.OrgTypes == "2")
{
BalanceStatementController balanceStatement = new BalanceStatementController();
BalanceStatement BSObj = new BalanceStatement();
try
{
BSObj.Narration = "Opening Balance";
BSObj.Amount = 0.00;
BSObj.Date = CalculationStartFrom;
BSObj.OrgId = OrgId;
BSObj.Balance = 0.00;
BSObj.SaveOpeningValue();
balanceStatement.InsertIntoBalanceStementAfterRegistration(CalculationStartFrom, OrgId);
}
catch (Exception ex)
{
ex.ToString();
}
}
}
}
else
{
Row = cmd.ExecuteNonQuery();
//this.ID = Row;
}
}
catch (Exception e) { e.ToString(); }
finally { cmd.Dispose(); con.Con.Close(); }
return(Row);
}