public DataSet SectionWiseSaleReport(DateTime fromdate, DateTime todate, string section)
{
DataSet ds = new System.Data.DataSet();
string strqry = string.Empty;
try
{
if (section == "0")
{
//string strqry = "select vw.itemid,y.itemcategoryname,billno, vw.vchbillno,vw. tableid, vchkotid, vw.itemid,vw.itemname, amount,totalbillamount, totaldiscount,itemdicount, numservicetaxvale,numservicechargevale, vchdiscounttype, vchdiscount ,y.categoryid,s.sectionid,s.sectionname from vwitemdiscountdetails vw left join (select itemid,z.itemcategoryname,z.categoryid from tabpositemmst x left join tabpositemcategorymst z on x.categoryid=z.categoryid and x.categoryid=z.categoryid)y on y.itemid=vw.itemid left join tabpostablesandcoversmst s on s.tableid=vw.tableid;";
strqry = "select vw.itemid,y.itemcategoryname,billno, vw.vchbillno,vw. tableid, vchkotid, vw.itemid,vw.itemname, amount,totalbillamount, totaldiscount,itemdicount, numservicetaxvale,numservicechargevale, vchdiscounttype, vchdiscount ,y.categoryid,s.sectionid,s.sectionname from vwitemdiscountdetails vw left join (select itemid,z.itemcategoryname,z.categoryid from tabpositemmst x left join tabpositemcategorymst z on x.categoryid=z.categoryid and x.categoryid=z.categoryid)y on y.itemid=vw.itemid left join tabpostablesandcoversmst s on s.tablesname=vw.tableid where billdate between '" + FormatDate(fromdate.ToString("dd/MM/yyyy")) + "' and '" + FormatDate(todate.ToString("dd/MM/yyyy")) + "';";
}
else
{
strqry = "select vw.itemid,y.itemcategoryname,billno, vw.vchbillno,vw. tableid, vchkotid, vw.itemid,vw.itemname, amount,totalbillamount, totaldiscount,itemdicount, numservicetaxvale,numservicechargevale, vchdiscounttype, vchdiscount ,y.categoryid,s.sectionid,s.sectionname from vwitemdiscountdetails vw left join (select itemid,z.itemcategoryname,z.categoryid from tabpositemmst x left join tabpositemcategorymst z on x.categoryid=z.categoryid and x.categoryid=z.categoryid)y on y.itemid=vw.itemid left join tabpostablesandcoversmst s on s.tablesname=vw.tableid where billdate between '" + FormatDate(fromdate.ToString("dd/MM/yyyy")) + "' and '" + FormatDate(todate.ToString("dd/MM/yyyy")) + "' and sectionid=" + section + ";";
}
ds = NPGSqlHelper.ExecuteDataset(NPGSqlHelper.SQLConnString, CommandType.Text, strqry);
}
catch (Exception ex)
{
EventLogger.WriteToErrorLog(ex, "SectionWiseSale Report");
throw ex;
}
return(ds);
}
public DataTable ShowEmployeeName()
{
DataSet ds = new DataSet();
//List<UserInfo> lstemployeename = new List<UserInfo>();
try
{
string str = "select recordid as employeeid,vchemployeename as employeename from tabinvemployeedetails where statusid =1 order by 2; ";
ds = NPGSqlHelper.ExecuteDataset(NPGSqlHelper.SQLConnString, CommandType.Text, str);
//while (npgdr.Read())
//{
// UserInfo objUserInfoDTO = new UserInfo();
// objUserInfoDTO.UserID = npgdr["recordid"].ToString();
// objUserInfoDTO.UserName = npgdr["vchemployeename"].ToString();
// lstemployeename.Add(objUserInfoDTO);
//}
}
catch (Exception ex)
{
EventLogger.WriteToErrorLog(ex, "ShowEmployeeName");
}
finally
{
//npgdr.Dispose();
}
return(ds.Tables[0]);
}
public List <ModuleDTO> ModueTypeChange(string Type, string Form)
{
List <ModuleDTO> lstmodule = new List <ModuleDTO>();
DataSet ds = new DataSet();
try
{
string strGetCountry = "select distinct parentmoduleid,parentmodulename from tabmodules where parentmodulename is not null and moduletype in ('" + Type + "') and parentmoduleid is not null order by parentmodulename;";
ds = NPGSqlHelper.ExecuteDataset(NPGSqlHelper.SQLConnString, CommandType.Text, strGetCountry);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
ModuleDTO objC = new ModuleDTO();
objC.ModuleId = Convert.ToInt32(ds.Tables[0].Rows[i]["parentmoduleid"]);
objC.ParentModulename = ds.Tables[0].Rows[i]["parentmodulename"].ToString();
lstmodule.Add(objC);
}
}
}
catch (Exception ex)
{
EventLogger.WriteToErrorLog(ex, "UserRights");
}
finally
{
ds.Dispose();
}
return(lstmodule);
}
public DataSet BillwiseReport(DateTime fromdate, DateTime todate, string billno)
{
DataSet ds = new System.Data.DataSet();
string strqry = string.Empty;
try
{
if (billno == "ALL")
{
//strqry = "select billno,billdate,vchbillno,tablesname,totalbillamount,replace(vchbillno ,'B','')::int as ordernumber,numservicetaxvale,numservicechargevale,itemdicount as totaldiscount,vchhostname,round((totalbillamount+numservicetaxvale+numservicechargevale-itemdicount),2) as withtaxsale,round((totalbillamount+numservicetaxvale+numservicechargevale-itemdicount),0) as Roundedsale from(select billno,billdate,vchbillno,tablesname,sum(amount) as totalbillamount,sum(coalesce(itemdicount,0)) as itemdicount,sum(coalesce(numservicetaxvale,0)) as numservicetaxvale,sum(coalesce(numservicechargevale,0)) as numservicechargevale,vchhostname from vwitemdiscountdetails vw join tabpostablesandcoversmst tab on vw.tableid=tab.tableid where billdate between '" + FormatDate(fromdate.ToString("dd/MM/yyyy")) + "' and '" + FormatDate(todate.ToString("dd/MM/yyyy")) + "' group by billno,billdate,vchbillno,tablesname,totalbillamount,vchhostname ) g order by ordernumber;";
strqry = "select billno,billdate,vchbillno,tablesname,totalbillamount,numservicetaxvale,numservicechargevale,itemdicount as totaldiscount,vchhostname,round((totalbillamount+numservicetaxvale+numservicechargevale-itemdicount),2) as withtaxsale,round((totalbillamount+numservicetaxvale+numservicechargevale-itemdicount),0) as Roundedsale from(select billno,billdate,vchbillno,tablesname,sum(amount) as totalbillamount,sum(coalesce(itemdicount,0)) as itemdicount,sum(coalesce(numservicetaxvale,0)) as numservicetaxvale,sum(coalesce(numservicechargevale,0)) as numservicechargevale,vchhostname from vwitemdiscountdetails vw join tabpostablesandcoversmst tab on vw.tableid=tab.tablesname where billdate between '" + FormatDate(fromdate.ToString("dd/MM/yyyy")) + "' and '" + FormatDate(todate.ToString("dd/MM/yyyy")) + "' group by billno,billdate,vchbillno,tablesname,totalbillamount,vchhostname ) g order by billno;";
}
else
{
strqry = "select * from(select billno,billdate,vchbillno,tablesname,totalbillamount,numservicetaxvale,numservicechargevale,itemdicount as totaldiscount,vchhostname,round((totalbillamount+numservicetaxvale+numservicechargevale-itemdicount),2) as withtaxsale,round((totalbillamount+numservicetaxvale+numservicechargevale-itemdicount),0) as Roundedsale from(select billno,billdate,vchbillno,tablesname,sum(amount) as totalbillamount,sum(coalesce(itemdicount,0)) as itemdicount,sum(coalesce(numservicetaxvale,0)) as numservicetaxvale,sum(coalesce(numservicechargevale,0)) as numservicechargevale,vchhostname from vwitemdiscountdetails vw join tabpostablesandcoversmst tab on vw.tableid=tab.tablesname where billdate between '" + FormatDate(fromdate.ToString("dd/MM/yyyy")) + "' and '" + FormatDate(todate.ToString("dd/MM/yyyy")) + "' group by billno,billdate,vchbillno,tablesname,totalbillamount,vchhostname ) g order by billno)x where vchbillno='" + billno + "';";
}
//string strqry = "select DISTINCT billno,billdate,vchbillno,tablesname,totalbillamount,replace(vchbillno ,'B','')::int as ordernumber,numservicetaxvale,totaldiscount,vchhostname,round((totalbillamount+numservicetaxvale),2) as withtaxsale,round((totalbillamount+numservicetaxvale),0) as Roundedsale from vwitemdiscountdetails vw join tabpostablesandcoversmst tab on vw.tableid=tab.tableid where billdate between '" + FormatDate(fromdate.ToString("dd/MM/yyyy")) + "' and '" + FormatDate(todate.ToString("dd/MM/yyyy")) + "' order by ordernumber;";
ds = NPGSqlHelper.ExecuteDataset(NPGSqlHelper.SQLConnString, CommandType.Text, strqry);
}
catch (Exception ex)
{
EventLogger.WriteToErrorLog(ex, "BillWise Report");
throw ex;
}
return(ds);
}
public DataSet TableGenerateReports(DateTime fromdate, DateTime todate, string tablename)
{
DataSet ds = new System.Data.DataSet();
string strqry = string.Empty;
try
{
if (tablename == "0")
{
strqry = "select tableid,tablesname,sectionname,round(amt,2) as amt,round(dis,2)as dis,round(tax,2) as tax,((amt-dis)+tax) as saleofamt from(select vw.tableid as tableid,tablesname,tab.sectionname as sectionname,sum(vw.amount) as amt,sum(vw.itemdicount) as dis,sum(vw.numservicetaxvale +vw.numservicechargevale) as tax from vwitemdiscountdetails vw join tabpostablesandcoversmst tab on vw.tableid=tab.tablesname where vw.billdate between '" + FormatDate(fromdate.ToString("dd/MM/yyyy")) + "' and '" + FormatDate(todate.ToString("dd/MM/yyyy")) + "' group by vw.tableid,tablesname,tab.sectionname) x ";
}
else
{
strqry = "select tableid,tablesname,sectionname,round(amt,2) as amt,round(dis,2)as dis,round(tax,2) as tax,((amt-dis)+tax) as saleofamt from(select vw.tableid as tableid,tablesname,tab.sectionname as sectionname,sum(vw.amount) as amt,sum(vw.itemdicount) as dis,sum(vw.numservicetaxvale +vw.numservicechargevale) as tax from vwitemdiscountdetails vw join tabpostablesandcoversmst tab on vw.tableid=tab.tablesname where vw.billdate between '" + FormatDate(fromdate.ToString("dd/MM/yyyy")) + "' and '" + FormatDate(todate.ToString("dd/MM/yyyy")) + "' and tab.tableid='" + tablename + "' group by vw.tableid,tablesname,tab.sectionname) x ";
}
ds = NPGSqlHelper.ExecuteDataset(NPGSqlHelper.SQLConnString, CommandType.Text, strqry);
}
catch (Exception ex)
{
EventLogger.WriteToErrorLog(ex, "BillEdit");
throw ex;
}
return(ds);
}
public DataSet ShiftwiseItemSaleReport(DateTime fromdate, DateTime todate, string Shift)
{
DataSet ds = new System.Data.DataSet();
string strqry = string.Empty;
try
{
if (Shift == "0")
{
strqry = " select itemid,itemname,sessionname,case when amount>0 then(amount+numservicetaxvale+numservicechargevale-itemdicount) else 0 end as itemqty,'Amount' as subname from vwitemdiscountdetails vw join tabpossessionmst s on s.sessionid=vw.sessionid where billdate between '" + FormatDate(fromdate.ToString("dd/MM/yyyy")) + "' and '" + FormatDate(todate.ToString("dd/MM/yyyy")) + "' union all select itemid,itemname,sessionname,itemqty,'Quantity' from vwitemdiscountdetails vw join tabpossessionmst s on s.sessionid=vw.sessionid where billdate between '" + FormatDate(fromdate.ToString("dd/MM/yyyy")) + "' and '" + FormatDate(todate.ToString("dd/MM/yyyy")) + "' order by itemname;";
}
else
{
strqry = " select itemid,itemname,sessionname,case when amount>0 then(amount+numservicetaxvale+numservicechargevale-itemdicount) else 0 end as itemqty,'Amount' as subname from vwitemdiscountdetails vw join tabpossessionmst s on s.sessionid=vw.sessionid where vw.sessionid='" + Shift + "' and billdate between '" + FormatDate(fromdate.ToString("dd/MM/yyyy")) + "' and '" + FormatDate(todate.ToString("dd/MM/yyyy")) + "' union all select itemid,itemname,sessionname,itemqty,'Quantity' from vwitemdiscountdetails vw join tabpossessionmst s on s.sessionid=vw.sessionid where vw.sessionid='" + Shift + "' and billdate between '" + FormatDate(fromdate.ToString("dd/MM/yyyy")) + "' and '" + FormatDate(todate.ToString("dd/MM/yyyy")) + "' order by itemname;";
}
ds = NPGSqlHelper.ExecuteDataset(NPGSqlHelper.SQLConnString, CommandType.Text, strqry);
}
catch (Exception ex)
{
EventLogger.WriteToErrorLog(ex, "BillWise Report");
throw ex;
}
return(ds);
}
public List <Login> GetParentModuleNames(int userid)
{
List <Login> objModuleValues = new List <Login>();
DataSet ds = new DataSet();
try
{
ds = NPGSqlHelper.ExecuteDataset(NPGSqlHelper.SQLConnString, CommandType.Text, "select distinct modulename,tm.moduleid,moduledescription,modulesortorder,parentmodulename from tabmodules tm join tabrolefunctions trf on tm.moduleid=trf.moduleid where moduletype in('RMS','HRMS') and modulename not in ('RMSDASHBOARD') and userid=" + userid + " order by modulesortorder");
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
Login objmodule = new Login();
objmodule.ModuleId = Convert.ToInt32(ds.Tables[0].Rows[i]["moduleid"]);
objmodule.ModuleName = Convert.ToString(ds.Tables[0].Rows[i]["modulename"]);
objmodule.ParentModuleName = Convert.ToString(ds.Tables[0].Rows[i]["parentmodulename"]);
if (objmodule.ModuleName.ToLower().Contains("master"))
{
objmodule.moduleclass = "menu-icon fa fa-cog";
}
if (objmodule.ModuleName.ToLower().Contains("tran"))
{
objmodule.moduleclass = "menu-icon fa fa-pie-chart";
}
if (objmodule.ModuleName.ToLower().Contains("report"))
{
objmodule.moduleclass = "menu-icon fa fa-random";
}
if (objmodule.ModuleName.ToLower().Contains("home"))
{
objmodule.moduleclass = "menu-icon fa fa-home";
}
objmodule.moduledescription = Convert.ToString(ds.Tables[0].Rows[i]["moduledescription"]);
objModuleValues.Add(objmodule);
}
}
}
catch (Exception)
{
throw;
}
finally
{
ds.Dispose();
}
return(objModuleValues);
}
public DataSet GetUserid(string username)
{
try
{
string StrUsers = "select userid from tabuserinfo where username='******'";
return(NPGSqlHelper.ExecuteDataset(NPGSqlHelper.SQLConnString, CommandType.Text, StrUsers));
}
catch (System.Exception ex)
{
// EventLogger.WriteToErrorLog(ex, "Login");
throw;
}
}
public DataSet ItemCancelGenerateReports(DateTime fromdate, DateTime todate)
{
DataSet ds = new System.Data.DataSet();
try
{
string strqry = "select vw.vchkotid,vw.tableid,tab.tablesname,vw.itemname,vw.itemqty,vw.amount,vw.reason,vw.canceldate,vw.vchhostname,vw.ordertime as kotordertime,vw.canceltime as kotcanceltime from vwitemcancel vw join tabpostablesandcoversmst tab on vw.tableid=tab.tableid where vw.canceldate between '" + fromdate + "' and '" + todate + "' order by tableid,itemname;";
ds = NPGSqlHelper.ExecuteDataset(NPGSqlHelper.SQLConnString, CommandType.Text, strqry);
}
catch (Exception ex)
{
EventLogger.WriteToErrorLog(ex, "ItemCancel");
throw ex;
}
return(ds);
}
public DataTable GetCashierComboData()
{
DataTable dt = new DataTable();
string strData = string.Empty;
try
{
strData = "select userid,username from tabuserinfo where statusid=1;";
dt = NPGSqlHelper.ExecuteDataset(NPGSqlHelper.SQLConnString, CommandType.Text, strData).Tables[0];
}
catch (Exception ex)
{
//EventLogger.WriteToErrorLog(ex, "Designation");
}
return(dt);
}
public List <ModuleDTO> ShowMainModules(string ID, string Form, string ModuleType)
{
List <ModuleDTO> lstModule = new List <ModuleDTO>();
DataSet ds = new DataSet();
try
{
if (Form == "MODULE")
{
string strGetCountry = "select distinct moduleid,modulename from tabmodules where parentmodulename is not null and moduletype in ('" + ModuleType + "') and parentmoduleid=" + ID + " and moduleid not in (" + ID + ") order by modulename;";
ds = NPGSqlHelper.ExecuteDataset(NPGSqlHelper.SQLConnString, CommandType.Text, strGetCountry);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
ModuleDTO objC = new ModuleDTO();
objC.ModuleId = Convert.ToInt32(ds.Tables[0].Rows[i]["moduleid"]);
objC.ParentModulename = ds.Tables[0].Rows[i]["modulename"].ToString();
lstModule.Add(objC);
}
}
}
else
{
string str = "select distinct modulename,moduleid from tabmodules where statusid=1 and moduletype in ('" + ModuleType + "') and parentmoduleid=" + ID + " and moduleid not in (" + ID + ");";
npgdr = NPGSqlHelper.ExecuteReader(NPGSqlHelper.SQLConnString, CommandType.Text, str);
while (npgdr.Read())
{
ModuleDTO objModuleDTO = new ModuleDTO();
objModuleDTO.Moduleid = Convert.ToInt32(npgdr["moduleid"]);
objModuleDTO.Modulename = npgdr["modulename"].ToString();
lstModule.Add(objModuleDTO);
}
}
}
catch (Exception ex)
{
EventLogger.WriteToErrorLog(ex, "ShowMainModules");
}
finally
{
ds.Dispose();
}
return(lstModule);
}
public List <Login> GetChildFunctionNames(int userid, string ModuleId, string type)
{
List <Login> objChildModuleValues = new List <Login>();
DataSet ds = new DataSet();
string str = "";
try
{
if (type == "RMSDASHBOARD")
{
str = "select distinct functionname,functionurl,tf.moduleid,functionsortorder,vchfunctionclass from tabfunctions tf join tabrolefunctions trf on tf.functionid=trf.functionid join tabposfunctionclasses fc on tf.functionid=fc.functionid and fc.functionid=trf.functionid where type='" + type + "' and userid=" + userid + " order by functionsortorder;";
}
else
{
str = "select distinct functionname,functionurl,tf.moduleid,functionsortorder,vchfunctionclass from tabfunctions tf join tabrolefunctions trf on tf.functionid=trf.functionid left join tabposfunctionclasses fc on tf.functionid=fc.functionid and fc.functionid=trf.functionid where type='" + type + "' and userid=" + userid + " and tf.moduleid=" + ModuleId + " order by functionsortorder;";
}
ds = NPGSqlHelper.ExecuteDataset(NPGSqlHelper.SQLConnString, CommandType.Text, str);
// npgdr = NPGSqlHelper.ExecuteReader(NPGSqlHelper.SQLConnString, CommandType.Text, "select functionname,functionurl,moduleid from tabfunctions where type='HRMS' order by moduleid;");
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
Login objChildmodule = new Login();
objChildmodule.ModuleId = Convert.ToInt32(ds.Tables[0].Rows[i]["moduleid"]);
objChildmodule.FunctionName = Convert.ToString(ds.Tables[0].Rows[i]["functionname"]);
objChildmodule.Functionclass = Convert.ToString(ds.Tables[0].Rows[i]["vchfunctionclass"]);
objChildmodule.FunctionUrl = Convert.ToString(ds.Tables[0].Rows[i]["functionurl"]);
objChildModuleValues.Add(objChildmodule);
}
}
}
catch (Exception)
{
throw;
}
finally
{
ds.Dispose();
}
return(objChildModuleValues);
}
public DataSet KotChangeslip(string kotno)
{
DataSet ds = new DataSet();
try
{
string strquery = "select vk.sessionid,vk.vchkotid,vk.itemname,vk.vchhostname,vk.itemqty ,tp.itemqty as prasentqty,ts.sessionname,tt.tablesname from vwkotdetails vk left join tabpossessionmst ts on vk.sessionid=ts.sessionid left join tabpostablesandcoversmst tt on vk.tableid=tt.tableid left join tabposkotchangedetails tp on vk.vchkotid=tp.vchkotid where vk.vchkotid='" + kotno + "'";
//string strquery = "select vk.sessionid,vk.vchkotid,vk.itemname,vk.vchhostname,vk.itemqty,ts.sessionname,tt.tablesname from vwkotdetails vk left join tabpossessionmst ts on vk.sessionid=ts.sessionid left join tabpostablesandcoversmst tt on vk.tableid=tt.tableid where vk.vchkotid='KOT7'";
ds = NPGSqlHelper.ExecuteDataset(NPGSqlHelper.SQLConnString, CommandType.Text, strquery);
}
catch (Exception ex)
{
EventLogger.WriteToErrorLog(ex, "KotChangeslip Report");
}
finally
{
}
return(ds);
}
public DataSet SalesGenerateReports(DateTime fromdate, DateTime todate, int userid)
{
DataSet ds = new System.Data.DataSet();
try
{
//string strqry = "select vchbillno,(numpaidamount+numbalanceamount) as numpaidamount from tabposbillsettlement where datdate between '" + FormatDate(fromdate.ToString("dd/MM/yyyy")) + "' and '" + FormatDate(todate.ToString("dd/MM/yyyy")) + "' and createdby=" + userid + ";";
string strqry = "select distinct (totalbillamount-totaldiscount) as numpaidamount,billno,vchbillno from vwitemdiscountdetails where billdate between '" + FormatDate(fromdate.ToString("dd/MM/yyyy")) + "' and '" + FormatDate(todate.ToString("dd/MM/yyyy")) + "' and createdby=" + userid + ";";
ds = NPGSqlHelper.ExecuteDataset(NPGSqlHelper.SQLConnString, CommandType.Text, strqry);
}
catch (Exception ex)
{
EventLogger.WriteToErrorLog(ex, "BillEdit");
throw ex;
}
return(ds);
}
public DataSet WaiterwiseGenerateReports(DateTime fromdate, DateTime todate)
{
DataSet ds = new System.Data.DataSet();
try
{
//string strqry = "select vchhostname,numservicetaxvale,numservicechargevale,itemdicount,amount from vwitemdiscountdetails where billdate between '" + FormatDate(fromdate.ToString("dd/MM/yyyy")) + "' and '" + FormatDate(todate.ToString("dd/MM/yyyy")) + "';";
string strqry = "select vchhostname,round(tax,2) as tax,round(dis,2) as dis,round(amt,2) as amt,round(((amt-dis)+tax),2) as saleofamt from(select vchhostname,sum(numservicetaxvale+numservicechargevale) as tax,sum(amount) as amt,sum(itemdicount) as dis from vwitemdiscountdetails where billdate between '" + FormatDate(fromdate.ToString("dd/MM/yyyy")) + "' and '" + FormatDate(todate.ToString("dd/MM/yyyy")) + "' group by vchhostname)g group by vchhostname,tax,dis,amt";
ds = NPGSqlHelper.ExecuteDataset(NPGSqlHelper.SQLConnString, CommandType.Text, strqry);
}
catch (Exception ex)
{
EventLogger.WriteToErrorLog(ex, "BillEdit");
throw ex;
}
return(ds);
}
public DataSet companyname()
{
DataSet ds1 = new DataSet();
try
{
string strquery = "select vchcompanyname,(vchdoorno||','||vchstreet||','||vcharea) as address from tabcompany tc join tabbranch tb on tc.branchid=tb.recordid;";
ds1 = NPGSqlHelper.ExecuteDataset(NPGSqlHelper.SQLConnString, CommandType.Text, strquery);
}
catch (Exception ex)
{
EventLogger.WriteToErrorLog(ex, "Company Name");
}
finally
{
}
return(ds1);
}
public DataSet usernmaere(int userid)
{
DataSet ds1 = new DataSet();
try
{
string strData = "select username from tabuserinfo where userid=" + userid + ";";
ds1 = NPGSqlHelper.ExecuteDataset(NPGSqlHelper.SQLConnString, CommandType.Text, strData);
}
catch (Exception ex)
{
EventLogger.WriteToErrorLog(ex, "Company Name");
}
finally
{
}
return(ds1);
}
public DataTable GetCategorynames()
{
DataTable dt = new DataTable();
string strdata = string.Empty;
try
{
strdata = "select itemcategoryname,categoryid from tabpositemcategorymst WHERE statusid=1 order by itemcategoryname;";
dt = NPGSqlHelper.ExecuteDataset(NPGSqlHelper.SQLConnString, CommandType.Text, strdata).Tables[0];
DataRow dr = dt.NewRow();
dr["categoryid"] = 0;
dr["itemcategoryname"] = "ALL";
dt.Rows.InsertAt(dr, 0);
}
catch (Exception)
{
throw;
}
return(dt);
}
public DataSet BillGenerationReport(string BillNumber)
{
DataSet ds = new System.Data.DataSet();
try
{
// string strqry = "select *,'' as categoryname from vwbillgeneration where vchbillno ='" + BillNumber + "';";
string strqry = "select * from vwbillgeneration vw join tabpositemmst ti on vw.itemid=ti.itemid where vchbillno ='" + BillNumber + "';";
ds = NPGSqlHelper.ExecuteDataset(NPGSqlHelper.SQLConnString, CommandType.Text, strqry);
}
catch (Exception ex)
{
EventLogger.WriteToErrorLog(ex, "BillGeneration");
throw ex;
}
return(ds);
}
public DataTable GetSectionnames()
{
DataTable dt = new DataTable();
string strData = string.Empty;
try
{
strData = "select sectionid,sectionname from tabpossectionmst WHERE statusid=1 order by sectionid;";
dt = NPGSqlHelper.ExecuteDataset(NPGSqlHelper.SQLConnString, CommandType.Text, strData).Tables[0];
DataRow dr = dt.NewRow();
dr["sectionid"] = 0;
dr["sectionname"] = "ALL";
dt.Rows.InsertAt(dr, 0);
}
catch (Exception ex)
{
//EventLogger.WriteToErrorLog(ex, "Designation");
}
return(dt);
}