/// <summary>
/// Get all the ledger data from the
/// </summary>
/// <param name="dtMonth"></param>
/// <returns></returns>
public DataTable GetFertilizerDataTable(DateTime dtMonth, int intOrder)
{
sql = "SELECT a.IssueID, a.IssueDate, a.AccID, a.Amount, a.RegNo, a.RegName, a.RegRoute, a.DIscountAmt, " +
"a.DeductionAmt, a.IssueType, a.GatePass, a.GatePassName, a.VehicleNo, a.CatFK, b.Cr, b.BLregNo, " + intOrder + " AS ind," +
" b.ProductID, c.CatID, c.ProductName FROM " + DBAccess.InvDBname + ".dbo.GenaralIssues AS a INNER JOIN " +
DBAccess.InvDBname + ".dbo.Stock_Data AS b ON a.IssueID = b.StockID INNER JOIN " + DBAccess.InvDBname +
".dbo.Products AS c ON b.ProductID = c.PK" +
" WHERE(MONTH(a.IssueDate) = " + dtMonth.Month + ") AND(YEAR(a.IssueDate) =" + dtMonth.Year + ")";
//sql = " SELECT " +intOrder + "As ind, a.trDate, a.IssuedMonth, a.trMonth, a.regNo, a.Route, a.regName, a.Amount, a.trType, a.LogDate, a.IssueID, a.crRoute, a.ItemID, a.Price, a.Qty, b.Cr, b.BLregNo, " +
// "c.RegRoute, c.GatePassName, c.VehicleNo, b.ProductID, p.ProductName, p.CatID " +
// "FROM " + DBAccess.blDBname +".dbo.Advance_tbl" +
// " AS a INNER JOIN " + DBAccess.InvDBname + ".dbo.Stock_Data AS b ON a.IssueID = b.StockID INNER JOIN " +
// " " + DBAccess.InvDBname + ".dbo.GenaralIssues AS c ON a.IssueID = c.IssueID INNER JOIN " +
// " " + DBAccess.InvDBname + ".dbo.Products AS p ON b.ProductID = p.PK WHERE Month(a.TRDate )=" + dtMonth.Month +
// " AND Year(a.TRDate )=" + dtMonth.Year ;
//sql = sql + " Where SavingReg=1";
SavingData = new DataTable();
try
{
SavingData = DBAccess.GetDataTable(sql, DBAccess.BoughtLeaf);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
return(SavingData);
}
/// <summary>
/// Get Accounts from the Account Master Table
/// </summary>
/// <param name="acType"></param>
/// <returns>Collection of Accounts</returns>
public ICollection <Account> GetAccounts(string acType)
{
strsql = "SELECT * From dbo.Account_List WHERE (AcName = N'" + acType + "')";
table = new DataTable();
db = new DBAccess();
table = db.GetDataTable(strsql, DBAccess.GL);
accountsList = new HashSet <Account>();
foreach (DataRow dr in table.Rows)
{
Account account = new Account();
account.AccountCode = dr["AccountCode"].ToString();
account.AccountName = dr["AccountName"].ToString();
account.CurrentBalance = decimal.Parse(dr["CurrentBalance"].ToString());
account.CreditBalance = decimal.Parse(dr["CreditBalance"].ToString());
account.DebitBalance = decimal.Parse(dr["DebitBalance"].ToString());
account.id = int.Parse(dr["id"].ToString());
accountsList.Add(account);
}
return(accountsList);
}
/// <summary>
/// Get all the ledger data from the
/// </summary>
/// <param name="dtMonth"></param>
/// <returns></returns>
public DataTable GetSavingDataTable(DateTime dtMonth)
{
sql = " Select routeName,RegNo, RegName, SavingAMT,TotKg from Main_Reg_" + String.Format("{0:MMM_yyyy}", dtMonth);
sql = sql + " Where SavingReg=1";
SavingData = new DataTable();
try
{
SavingData = DBAccess.GetDataTable(sql, DBAccess.BLHistory);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
return(SavingData);
}
public AdvanceMaster GetLastAdvanceEntry()
{
table = new DataTable();
strsql = "Select top 1 * From Advance_File Order By ListId Desc";
table = db.GetDataTable(strsql, DBAccess.BoughtLeaf);
advHead = new AdvanceMaster();
if (table.Rows.Count == 1)
{
advHead.crAcc = table.Rows[0]["crAcc"].ToString();
advHead.drAcc = table.Rows[0]["drAcc"].ToString();
advHead.ListId = int.Parse(table.Rows[0]["ListId"].ToString());
advHead.PayMode = table.Rows[0]["PayMode"].ToString();
advHead.trDate = DateTime.Parse(table.Rows[0]["trDate"].ToString());
advHead.trMonth = table.Rows[0]["trMonth"].ToString();
advHead.trRoute = table.Rows[0]["trRoute"].ToString();
}
return(advHead);
}
public DataTable Users()
{
strsql = "SELECT * FROM SystemUser";
table = db.GetDataTable(strsql, DBAccess.AUDIT);
return(table);
}
