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