public DataSet AllLedgerSummary(DateTime fromDate, DateTime toDate, int BranchId, int CompanyUnitId, string _LedgerId)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("With LedgerReport(LedgerId , OpeningDebit, OpeningCredit,PerodicDebit,PerodicCredit) \n");
strSql.Append("as ( \n");
strSql.Append("Select LedgerId,CASE WHEN SUM(LocalDrAmt -LocalCrAmt) > 0 THEN SUM(LocalDrAmt -LocalCrAmt) ELSE 0 END OpeningDebit, \n");
strSql.Append("CASE WHEN SUM(LocalDrAmt -LocalCrAmt) > 0 THEN 0 else abs(SUM(LocalDrAmt -LocalCrAmt)) END OpeningCredit, 0 PerodicDebit, 0 PerodicCredit \n");
strSql.Append("from ERP.FinanceTransaction \n");
strSql.Append("where VDate < '" + fromDate.ToString("yyyy-MM-dd") + "' \n");
if (!string.IsNullOrEmpty(_LedgerId))
{
strSql.Append("and LedgerId IN (SELECT Value FROM fn_Splitstring('" + _LedgerId + "', ','))\n");
}
strSql.Append("Group By LedgerId\n");
strSql.Append("Union All\n");
strSql.Append("Select LedgerId,0 OpeningDebit, 0 OpeningCredit,Sum(LocalDrAmt) PerodicDebit,Sum(LocalCrAmt) PerodicCredit\n");
strSql.Append("from ERP.FinanceTransaction \n");
strSql.Append("where VDate Between '" + fromDate.ToString("yyyy-MM-dd") + "' and '" + toDate.ToString("yyyy-MM-dd") + "' \n");
if (!string.IsNullOrEmpty(_LedgerId))
{
strSql.Append("and LedgerId IN (SELECT Value FROM fn_Splitstring('" + _LedgerId + "', ','))\n");
}
strSql.Append("Group By LedgerId\n");
strSql.Append(")\n");
strSql.Append("Select GlShortName, GlDesc, Sum(OpeningDebit) OpeningDebit, Sum(OpeningCredit) OpeningCredit,Sum(PerodicDebit) PerodicDebit,SUm(PerodicCredit) PerodicCredit,Sum(PerodicDebit-PerodicCredit) Balance, \n");
strSql.Append("case when Sum(((OpeningDebit-OpeningCredit) + (PerodicDebit-PerodicCredit))) >= 0 then Sum(((OpeningDebit-OpeningCredit) + ( PerodicDebit-PerodicCredit))) else 0 end ClosingDebit,\n");
strSql.Append("case when Sum((OpeningCredit-OpeningDebit) + (PerodicCredit-PerodicDebit)) <= 0 then 0 else abs(Sum((OpeningCredit-OpeningDebit) + (PerodicCredit-PerodicDebit))) end ClosingCredit\n");
strSql.Append("from LedgerReport as LR\n");
strSql.Append("inner Join ERP.GeneralLedger Gl on Lr.LedgerId=Gl.LedgerId\n");
strSql.Append("Group By GlDesc,GlShortName\n");
strSql.Append("Order By GlDesc,GlShortName");
return(DAL.ExecuteDataset(CommandType.Text, strSql.ToString()));
}
public string GetSchedule()
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select ISNULL((Select Top 1 max(cast(Schedule as int)) from ERP.AccountGroup),0)+1 As Schedule \n");
// DAL.ExecuteNonQuery(CommandType.Text, strSql.ToString());
DataTable dt = DAL.ExecuteDataset(CommandType.Text, strSql.ToString()).Tables[0];
return(dt.Rows[0]["Schedule"].ToString());
}
public DataTable TableList(int FloorId = 0, string TableStatus = "", string TableType = "")
{
if (FloorId == 0)
{
if (!string.IsNullOrEmpty(TableStatus))
{
return(DAL.ExecuteDataset(CommandType.Text, "select 0 as Tag, TableId,TableDesc,TableStatus,FloorId from ERP.TableMaster where TableDesc like 'SPlit%' and TableStatus='" + TableStatus + "' union all select 1 as Tag ,TableId,TableDesc,TableStatus,FloorId from ERP.TableMaster where TableDesc not like 'SPlit%' and TableStatus='" + TableStatus + "' order by Tag,FloorId,TableDesc ").Tables[0]);
}
else if (!string.IsNullOrEmpty(TableType))
{
return(DAL.ExecuteDataset(CommandType.Text, "select 0 as Tag, TableId,TableDesc,TableStatus,FloorId from ERP.TableMaster where TableDesc like 'SPlit%' and TableType='" + TableType + "' union all select 1 as Tag ,TableId,TableDesc,TableStatus,FloorId from ERP.TableMaster where TableDesc not like 'SPlit%' and TableType='" + TableType + "' order by Tag,FloorId,TableDesc ").Tables[0]);
}
else
{
return(DAL.ExecuteDataset(CommandType.Text, "select 0 as Tag, TableId,TableDesc,TableStatus,FloorId from ERP.TableMaster where TableDesc like 'SPlit%' union all select 1 as Tag ,TableId,TableDesc,TableStatus,FloorId from ERP.TableMaster where TableDesc not like 'SPlit%' order by Tag,FloorId,TableDesc ").Tables[0]);
}
}
else
{
if (!string.IsNullOrEmpty(TableStatus))
{
return(DAL.ExecuteDataset(CommandType.Text, "select 0 as Tag, TableId,TableDesc,TableStatus,FloorId from ERP.TableMaster where TableDesc like 'SPlit%' and TableStatus='" + TableStatus + "' union all select 1 as Tag ,TableId,TableDesc,TableStatus,FloorId from ERP.TableMaster where TableDesc not like 'SPlit%' and FloorId='" + FloorId + "' and TableStatus='" + TableStatus + "' order by Tag,TableDesc ").Tables[0]);
}
else if (!string.IsNullOrEmpty(TableType))
{
return(DAL.ExecuteDataset(CommandType.Text, "select 0 as Tag, TableId,TableDesc,TableStatus,FloorId from ERP.TableMaster where TableDesc like 'SPlit%' and TableType='" + TableType + "' union all select 1 as Tag ,TableId,TableDesc,TableStatus,FloorId from ERP.TableMaster where TableDesc not like 'SPlit%' and FloorId='" + FloorId + "' and TableType='" + TableType + "' order by Tag,TableDesc ").Tables[0]);
}
else
{
return(DAL.ExecuteDataset(CommandType.Text, "select 0 as Tag, TableId,TableDesc,TableStatus,FloorId from ERP.TableMaster where TableDesc like 'SPlit%' and TableStatus='" + TableStatus + "' union all select 1 as Tag ,TableId,TableDesc,TableStatus,FloorId from ERP.TableMaster where TableDesc not like 'SPlit%' and FloorId='" + FloorId + "' order by Tag,TableDesc ").Tables[0]);
}
}
}
public DataTable GetDataProductUnit(int ProductUnitId)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select * from ERP.ProductUnit ");
if (ProductUnitId != 0)
{
strSql.Append(" WHERE ProductUnitId='" + ProductUnitId + "' ");
}
return(DAL.ExecuteDataset(CommandType.Text, strSql.ToString()).Tables[0]);
}
public DataTable GetDataTable(int TableId)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select ERP.TableMaster.*,FloorDesc from ERP.TableMaster LEFT OUTER JOIN ERP.Floor ON ERP.Floor.FloorId=ERP.TableMaster.FloorId ");
if (TableId != 0)
{
strSql.Append("WHERE TableId='" + TableId + "'");
}
return(DAL.ExecuteDataset(CommandType.Text, strSql.ToString()).Tables[0]);
}
public DataTable GetDataDepartment(int DepartmentId)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("Select * from ERP.Department");
if (DepartmentId != 0)
{
strSql.Append(" WHERE DepartmentId='" + DepartmentId + "' ");
}
return(DAL.ExecuteDataset(CommandType.Text, strSql.ToString()).Tables[0]);
}
public DataTable GetDataSubledger(int SubledgerId)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select ERP.Subledger.*,GlDesc from ERP.Subledger left join ERP.GeneralLedger on ERP.GeneralLedger.LedgerId=ERP.Subledger.LedgerId ");
if (SubledgerId != 0)
{
strSql.Append(" WHERE SubledgerId='" + SubledgerId + "'");
}
return(DAL.ExecuteDataset(CommandType.Text, strSql.ToString()).Tables[0]);
}
public DataTable GetDataGodown(int GodownId)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select ERP.Godown.*,GLDesc from ERP.Godown left outer join ERP.Generalledger on ERP.Godown.ledgerId=ERP.Generalledger.LedgerId");
if (GodownId != 0)
{
strSql.Append(" WHERE GodownId='" + GodownId + "' ");
}
return(DAL.ExecuteDataset(CommandType.Text, strSql.ToString()).Tables[0]);
}
public DataTable GetDataCounter(int CounterId)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("Select ERP.Counter.*,GodownDesc from ERP.Counter left join ERP.Godown on ERP.Counter.GodownId=ERP.Godown.GodownId ");
if (CounterId != 0)
{
strSql.Append(" WHERE CounterId='" + CounterId + "'");
}
return(DAL.ExecuteDataset(CommandType.Text, strSql.ToString()).Tables[0]);
}
public DataTable GetDataPurchaseBillingTerm(int TermId)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("Select TermId, TermPosition,case when TermType='I' then 'Invoice' when TermType='B' then 'Both' else 'Return'end as TermType, TermDesc,Category,ERP.PurchaseBillingTerm.LedgerId, GlDesc as LedgerDesc,Case when Billwise='Y' then 'Bill Wise' else 'Product Wise' end as Billwise,Case when Basis='Q' then 'Quantity' else 'Value' end as Basis, PTSign, TermRate, StockValuation, SupressZero, Formula, ERP.PurchaseBillingTerm.Status, ERP.PurchaseBillingTerm.EnterBy, ERP.PurchaseBillingTerm.EnterDate from ERP.PurchaseBillingTerm left join ERP.GeneralLedger on ERP.PurchaseBillingTerm.LedgerId=ERP.GeneralLedger.LedgerId ");
if (TermId != 0)
{
strSql.Append("where TermId ='" + TermId + "'");
}
return(DAL.ExecuteDataset(CommandType.Text, strSql.ToString()).Tables[0]);
}
public DataTable GetDataCompanyUnit(int CmpUnitId)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select CU.*,B.BranchName from ERP.CompanyUnit AS CU INNER JOIN ERP.Branch AS B ON CU.BranchId =B.BranchId ");
if (CmpUnitId != 0)
{
strSql.Append("where CompanyUnitId='" + CmpUnitId + "' ");
}
strSql.Append(" ORDER BY CmpUnitName");
return(DAL.ExecuteDataset(CommandType.Text, strSql.ToString()).Tables[0]);
}
public string IsExistsVNumber(string VoucherNo)
{
DataTable dt = DAL.ExecuteDataset(CommandType.Text, "select BillOfMaterialId from erp.BillOfMaterialMaster WHERE BillOfMaterialId='" + VoucherNo.Trim() + "'").Tables[0];
if (dt.Rows.Count == 0)
{
return("");
}
else
{
return(dt.Rows[0]["BillOfMaterialId"].ToString());
}
}
public DataTable GetDataKOTAssign(DateTime KOTDate)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("Select * from ERP.KOTAssign where KOTDate='" + KOTDate.ToString("yyyy-MM-dd") + "' ");
return(DAL.ExecuteDataset(CommandType.Text, strSql.ToString()).Tables[0]);
}
public DataTable Get(DateTime?date = null, string miti = "")
{
StringBuilder strSql = new StringBuilder();
strSql.Append("Select * from ERP.DateMiti Where 1= 1 \n");
if (date != null)
{
strSql.Append("and MDate = '" + Convert.ToDateTime(date).ToString("MM/dd/yyyy") + "'");
}
if (!string.IsNullOrEmpty(miti))
{
strSql.Append("and MMiti = '" + miti + "'");
}
return(DAL.ExecuteDataset(CommandType.Text, strSql.ToString()).Tables[0]);
}
public DataTable GetProduct(string ProductGrpId, string ProductSubGrpId)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select ProductId,ProductDesc,p.ProductGrpId,pg.ProductGrpDesc,p.ProductSubGrpId,PSG.ProductSubGrpDesc,0 as [Percent],P.SalesRate as Rate from ERP.Product p Left Outer JOin ERP.ProductGroup PG on P.ProductGrpId=PG.ProductGrpId left Outer Join ERP.ProductSubGroup PSG on p.ProductSubGrpId=PSG.ProductSubGrpId where 1=1 ");
if (ProductGrpId != "")
{
strSql.Append(" and P.ProductGrpId in('" + ProductGrpId + "')");
}
if (ProductSubGrpId != "")
{
strSql.Append(" and P.ProductSubGrpId in('" + ProductSubGrpId + "')");
}
return(DAL.ExecuteDataset(CommandType.Text, strSql.ToString()).Tables[0]);
}
public DataTable GetUserRestriction(string UserCode, string IniTial)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT * FROM MyMaster.DBO.UserRestriction WHERE 1=1 ");
if (string.IsNullOrEmpty(UserCode))
{
strSql.Append(" AND UserCode = '" + UserCode + "'");
}
if (string.IsNullOrEmpty(IniTial))
{
strSql.Append(" AND IniTial = '" + IniTial + "'");
}
strSql.Append(" ORDER BY IniTial");
return(DAL.ExecuteDataset(CommandType.Text, strSql.ToString()).Tables[0]);
}
public DataTable GetDataMainArea(int MainAreaId)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select * from ERP.MainArea ");
if (MainAreaId != 0)
{
strSql.Append(" WHERE MainAreaId='" + MainAreaId + "' ");
}
return(DAL.ExecuteDataset(CommandType.Text, strSql.ToString()).Tables[0]);
}
public DataTable GetDataArea(int AreaId)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select ERP.Area.*,MainAreaDesc from ERP.Area LEFT OUTER JOIN ERP.MainArea ON ERP.MainArea.MainAreaId=ERP.Area.MainAreaId ");
if (AreaId != 0)
{
strSql.Append(" WHERE AreaId='" + AreaId + "' ");
}
return(DAL.ExecuteDataset(CommandType.Text, strSql.ToString()).Tables[0]);
}
public DataTable GetDataProductSubGroup(int ProductSubGrpId)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("Select erp.ProductSubGroup.*,ProductGrpDesc from erp.ProductSubGroup left join erp.ProductGroup on erp.ProductSubGroup.ProductGrpId= erp.ProductGroup.ProductGrpId");
if (ProductSubGrpId != 0)
{
strSql.Append(" WHERE ProductSubGrpId='" + ProductSubGrpId + "' ");
}
return(DAL.ExecuteDataset(CommandType.Text, strSql.ToString()).Tables[0]);
}
public DataTable GetDataCurrency(int CurrencyId)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("Select * from ERP.Currency");
if (CurrencyId != 0)
{
strSql.Append(" WHERE CurrencyId='" + CurrencyId + "' ");
}
return(DAL.ExecuteDataset(CommandType.Text, strSql.ToString()).Tables[0]);
}
public DataTable GetDataFloor(int FloorId)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select * from ERP.Floor ");
if (FloorId != 0)
{
strSql.Append(" WHERE FloorId='" + FloorId + "'");
}
return(DAL.ExecuteDataset(CommandType.Text, strSql.ToString()).Tables[0]);
}
public DataTable GetDataNarration(int NarrationId)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("Select * from ERP.NarrationMaster ");
if (NarrationId != 0)
{
strSql.Append(" WHERE NarrationId='" + NarrationId + "'");
}
return(DAL.ExecuteDataset(CommandType.Text, strSql.ToString()).Tables[0]);
}
public DataTable GetDataAccountSubGroup(int AccountSubGrpId)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select ASG.*,AG.AccountGrpDesc from ERP.AccountSubGroup AS ASG INNER JOIN ERP.AccountGroup AS AG ON ASG.AccountGrpId=AG.AccountGrpId ");
if (AccountSubGrpId != 0)
{
strSql.Append("WHERE AccountSubGrpId = '" + AccountSubGrpId + "' ");
}
strSql.Append("ORDER BY AccountSubGrpDesc ");
return(DAL.ExecuteDataset(CommandType.Text, strSql.ToString()).Tables[0]);
}
public string GenerateShortName(string Val, string Description, string ShortName, string TableName, string Category = "")
{
string _ShortName = "";
Val = Val.Replace(" ", string.Empty);
if (!string.IsNullOrEmpty(Val))
{
StringBuilder strSql = new StringBuilder();
strSql.Append("declare @c int \n");
strSql.Append("set @c=(SELECT TOP 1 (Right(" + ShortName + ",5)+1) AS D FROM ERP." + TableName + " where left(" + ShortName.Trim() + ",2) = Left('" + Val.Trim().Replace("'", "''") + "', 2) and len(" + ShortName + ") = 7 AND ISNUMERIC(Right(" + ShortName + ",5))=1 ORDER BY D DESC ) \n");
strSql.Append("Select top(1) Left(" + Description + ",2)+CASE WHEN ISNULL(LEN(@c),1)=1 THEN '0000' WHEN LEN(@c)=2 THEN '000' WHEN LEN(@c)=3 THEN '00' WHEN LEN(@c)=4 THEN '0' WHEN LEN(@c)=5 THEN '' END+ convert(varchar(5),(Right(" + ShortName + ",5))+1) as ShortName \n");
strSql.Append("from ERP." + TableName + " where left(" + ShortName.Trim() + ",2) = Left('" + Val.Trim().Replace("'", "''") + "', 2) and len(" + ShortName + ") = 7 AND ISNUMERIC(Right(" + ShortName + ",5))=1 \n");
strSql.Append("order by " + ShortName + " Desc");
DataTable dt = DAL.ExecuteDataset(CommandType.Text, strSql.ToString()).Tables[0];
if (dt.Rows.Count > 0)
{
_ShortName = dt.Rows[0]["ShortName"].ToString();
}
if (_ShortName != "")
{
return(_ShortName.ToUpper());
}
if (Val.Length == 1)
{
return(Val.Substring(0, 1).ToUpper() + "00001");
}
else
{
return(Val.Substring(0, 2).ToUpper() + "00001");
}
}
return(_ShortName.ToUpper());
}
public DataTable GetDataBranch(int BranchId)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select * from ERP.Branch");
if (BranchId != 0)
{
strSql.Append(" where BranchId='" + BranchId + "'");
}
return(DAL.ExecuteDataset(CommandType.Text, strSql.ToString()).Tables[0]);
}
public void GetEntryControl(string EntryModule)
{
EntryControlViewModel EntryControls = null;
StringBuilder strSql = new StringBuilder();
strSql.Append("Select * from Erp.EntryControl");
if (!string.IsNullOrEmpty(EntryModule))
{
strSql.Append(" WHERE EntryModule='" + EntryModule + "'");
}
DataTable dt = DAL.ExecuteDataset(CommandType.Text, strSql.ToString()).Tables[0];
if (dt.Rows.Count > 0)
{
foreach (DataRow ro in dt.Rows)
{
EntryControls = new EntryControlViewModel();
if (ro["EntryModule"] != DBNull.Value)
{
EntryControls.EntryModule = ro["EntryModule"].ToString();
}
if (ro["ControlName"] != DBNull.Value)
{
EntryControls.ControlName = ro["ControlName"].ToString();
}
if (ro["ControlValue"] != DBNull.Value)
{
EntryControls.ControlValue = ro["ControlValue"].ToString();
}
if (ro["MandatoryOpt"] != DBNull.Value)
{
EntryControls.MandatoryOpt = ro["MandatoryOpt"].ToString();
}
this.EntryControl.Add(EntryControls);
}
}
}
public string IsExistsVNumber(string VoucherNo)
{
DataTable dt = DAL.ExecuteDataset(CommandType.Text, "select VoucherNo from erp.SalesChallanMaster WHERE VoucherNo='" + VoucherNo.Trim() + "'").Tables[0];
return(dt.Rows.Count == 0 ? "" : dt.Rows[0]["VoucherNo"].ToString());
}
public DataTable GetDataCompany(string Initial)
{
return(DAL.ExecuteDataset(CommandType.Text, "select * from ERP.CompanyInfo where Initial='" + Initial + "'").Tables[0]);
}
public DataTable CheckUser(string username, string pass)
{
return(DAL.ExecuteDataset(CommandType.Text, "Select * from MyMaster.dbo.UserMaster where (UserCode='" + username + "' OR EmailId='" + username + "' OR MobileNo='" + username + "') AND (UserPassword = '' OR UserPassword is null)").Tables[0]);
}
public DataTable GetDataGeneralLedger(int LedgerId)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("Select Gl.*,AG.AccountGrpDesc,AG.AccountGrpShortName,ASG.AccountSubGrpDesc,ASG.AccountSubGrpShortName,AR.AreaDesc,AR.AreaShortName,AGN.SalesmanDesc,AGN.SalesmanShortName,\n");
strSql.Append("Cur.CurrencyDesc,Cur.CurrencyShortName,DP1.DepartmentDesc as DepartmentDesc1,DP1.DepartmentShortName as DepartmentShortName1,\n");
strSql.Append("DP2.DepartmentDesc as DepartmentDesc2,DP2.DepartmentShortName as DepartmentShortName2,DP3.DepartmentDesc as DepartmentDesc3,DP3.DepartmentShortName as DepartmentShortName3,\n");
strSql.Append("DP4.DepartmentDesc as DepartmentDesc4,DP4.DepartmentShortName as DepartmentShortName4,'' SchemeDesc \n");
strSql.Append("from ERP.GeneralLedger as Gl Inner join ERP.AccountGroup as AG on Gl.AccountGrpId = AG.AccountGrpId Left Outer join ERP.AccountSubGroup as ASG on Gl.AccountSubGrpId = ASG.AccountSubGrpId \n");
strSql.Append("Left Outer join ERP.Area as AR on Gl.AreaId = AR.AreaId Left Outer join ERP.Salesman as AGN on Gl.SalesmanId = AGN.SalesmanId \n");
strSql.Append("Left Outer join ERP.Currency as Cur on Gl.CurrencyId = Cur.CurrencyId \n");
strSql.Append("Left Outer join ERP.Department as DP1 on Gl.DepartmentId1 = DP1.DepartmentId Left Outer join ERP.Department as DP2 on Gl.DepartmentId2 = DP2.DepartmentId \n");
strSql.Append("Left Outer join ERP.Department as DP3 on Gl.DepartmentId3 = DP3.DepartmentId Left Outer join ERP.Department as DP4 on Gl.DepartmentId4 = DP4.DepartmentId \n");
if (LedgerId != 0)
{
strSql.Append("WHERE GL.LedgerId='" + LedgerId + "' \n");
}
return(DAL.ExecuteDataset(CommandType.Text, strSql.ToString()).Tables[0]);
}