public DataTable CRM_Get_KW_Dsr_Main_Details(TransUser_Ety tu_ety)
{
string strQuey = string.Empty;
if (tu_ety.TransMode == "Mast")
{
strQuey = "select DSRId,replace(Convert(varchar,DSRDate,106),' ','-')DSRDate,CASE DSRoption WHEN 1 THEN 'Direct Market' WHEN 2"
+ " THEN 'Office and Market' ELSE 'Meeting' END DSRoption,City_Town AreasVisited,"//dbo.getDsrArea(DSRId)AreasVisited,"
+ " Convert(NUMERIC(18,2),POAmount) PoAmount,Convert(NUMERIC(18,2),ChequeAmount)ChequeAmount,"
+ " Convert(NUMERIC(18,2),Cashcollected)Cashcollected,Remarks,substring(Remarks,1,20)Remarks1,"
+ " isnull(a.AccCode +':'+a.AccName,'') Account,isnull(a.acc_id,0) acc_id From DSR_DailySalesRpt_Tbl d"
+ " LEFT JOIN GWTME_Sc_Accmast_View a ON d.AccId=a.acc_id "
+ " where DSRDate between '" + tu_ety.FromDt + "' and '" + tu_ety.ToDt + "' and d.empid=" + tu_ety.UserId + " order by d.DSRDate";
}
else if (tu_ety.TransMode == "Auth")
{
strQuey = "select DSRId,replace(Convert(varchar,DSRDate,106),' ','-')DSRDate,CASE DSRoption WHEN 1 THEN 'Direct Market' WHEN 2"
+ " THEN 'Office and Market' ELSE 'Meeting' END DSRoption,City_Town AreasVisited,"//dbo.getDsrArea(DSRId)AreasVisited,"
+ " Convert(NUMERIC(18,2),POAmount) PoAmount,Convert(NUMERIC(18,2),ChequeAmount)ChequeAmount,"
+ " Convert(NUMERIC(18,2),Cashcollected)Cashcollected,Remarks,substring(Remarks,1,20)Remarks1,"
+ " isnull(a.AccCode +':'+a.AccName,'') Account,isnull(a.acc_id,0) acc_id From DSR_DailySalesRptAuth_Tbl d"
+ " LEFT JOIN GWTME_Sc_Accmast_View a ON d.AccId=a.acc_id "
+ " where DSRDate between '" + tu_ety.FromDt + "' and '" + tu_ety.ToDt + "' and d.empid=" + tu_ety.UserId + " order by d.DSRDate";
}
using (Trans_Main_DAC dac = new Trans_Main_DAC())
{
return(dac.Get_StrQuery_Data(strQuey));
}
}
public DataTable CRM_Get_Employee_Reporting_Details(Int64 EmployeeId)
{
string strQuey = "SELECT Employeeid,dbo.PROPERCASE(EmpName)EmpName FROM dbo.getallReportings(" + EmployeeId + ") WHERE employeeid NOT IN (" + EmployeeId + ")";
using (Trans_Main_DAC dac = new Trans_Main_DAC())
{
return(dac.Get_StrQuery_Data(strQuey));
}
}
public DataTable CRM_Get_Employee_Designation_Details(Int64 EmployeeId)
{
string strQuey = "SELECT e.Employeeid ,dbo.PROPERCASE(e.Desig_Desc) Designation FROM hrms_EmployeeMast_View e"
+ " WHERE e.Employeeid =" + EmployeeId;
using (Trans_Main_DAC dac = new Trans_Main_DAC())
{
return(dac.Get_StrQuery_Data(strQuey));
}
}
public DataTable CRM_Get_Employee_Details(Int64 EmployeeId)
{
string strQuey = "select deptid departmentid, dbo.propercase(d.Dept_Desc) Dept_Desc,employeecode,EmployeeName Name " +
" from hrms_EmployeeMast_View e,dept_details d where employeeid = " + EmployeeId + " and d.Dept_Id=e.deptid order by employeecode";
using (Trans_Main_DAC dac = new Trans_Main_DAC())
{
return(dac.Get_StrQuery_Data(strQuey));
}
}
public DataTable CRM_Get_Area(string type)
{
string strQuey = "SELECT AreaId,dbo.PROPERCASE(AreaDescription)AreaDescription " +
" FROM DSR_AreaMast_Tbl where AreaType='" + type + "' order by AreaDescription";
using (Trans_Main_DAC dac = new Trans_Main_DAC())
{
return(dac.Get_StrQuery_Data(strQuey));
}
}
public DataTable CRM_Get_DsrUserRights(Int64 UserId, string DsrMenuName)
{
string strQuey = "SELECT AddBtn,ModifyBtn,PrintBtn,EraseBtn,ViewBtn,MenuPermission,"
+ " MenuName,updateCTC,Type FROM hrms_Security_tbl "
+ " WHERE userid=" + UserId + " AND MenuName= '" + DsrMenuName + "'";
using (Trans_Main_DAC dac = new Trans_Main_DAC())
{
return(dac.Get_StrQuery_Data(strQuey));
}
}
public DataTable CRM_Get_KW_TrExp_Dtl_Details(Int64 TravId)
{
string strQuey = "SELECT Replace(Convert(VARCHAR,trvdate,106),' ','-') TrvDate,Modeoftravell,Particulars TrFrom,ParticularsTo TrTo,Convert(NUMERIC(18,2),Fare)Fare,"
+ " Convert(NUMERIC(18,2),Lodging) LodgingBoard,Convert(NUMERIC(18,2),POrderValue) OrderVal,"
+ " dbo.getAreaName(particulars) particulars,dbo.getAreaName(particularsTo) particularsTo, "
+ " Convert(NUMERIC(18,2),CollectionAmt) ColAmt, Convert(NUMERIC(18,2),Miscexp) MicExpenses,Remarks, "
+ " Convert(NUMERIC(18,2),(Fare+Lodging+ Miscexp )) Total,Supportingflag,TSLNO FROM dbo.DSR_Travellingexpdetails_tbl WHERE travellid =" + TravId;
using (Trans_Main_DAC dac = new Trans_Main_DAC())
{
return(dac.Get_StrQuery_Data(strQuey));
}
}
public DataTable CRM_Get_KW_TrExp_Pending_dtls(Int64 TravId)
{
string strQuey = "SELECT Replace(Convert(VARCHAR,trvdate,106),' ','-') TrvDate,TSLNO,Modeoftravell,Particulars,Convert(NUMERIC(18,2),Fare)Fare,"
+ " Convert(NUMERIC(18,2),Lodging) LodgingBoard,Convert(NUMERIC(18,2),Locconvy) LocalConveyance,"
+ " Convert(NUMERIC(18,2),Extconvy)ExtraConveyance,Convert(NUMERIC(18,2),Luggagechrg) LuggageCharge,"
+ " Convert(NUMERIC(18,2),Miscexp) MicExpenses,Remarks, "
+ " Convert(NUMERIC(18,2),(Fare+Lodging+ Locconvy+ Extconvy+Luggagechrg+Miscexp )) Total,Supportingflag "
+ " FROM dbo.DSR_Travellingexpdetails_tbl WHERE travellid =" + TravId + " and authflag='N'";
using (Trans_Main_DAC dac = new Trans_Main_DAC())
{
return(dac.Get_StrQuery_Data(strQuey));
}
}
public DataTable CRM_Get_KW_TrExp_Header_Details(Int64 TravId)
{
string strQuey = "SELECT trvexpid,empid,dbo.PROPERCASE(Desig_Desc) Desig_Desc,Area,Replace(Convert(VARCHAR,tourfrmdate,106),' ','-')tourfrmdate,"
+ " Replace(Convert(VARCHAR,tourtodate,106),' ','-')tourtodate,tourauthorizedby, Convert(NUMERIC(18,2),Advancedrawn)Advancedrawn, "
+ "substring(Convert(VARCHAR,deptime,100),13,8) deptime,substring(Convert(VARCHAR,arivaltime,100),13,8)arivaltime,Convert(VARCHAR,repdatetime,106) repdatetime,"
+ " Convert(NUMERIC(18,2),Amtclaimed) Amtclaimed,Convert(NUMERIC(18,2),amtdisallowed) amtdisallowed,Convert(NUMERIC(18,2),amtpassed) amtpassed,approvedby,departmentid "
+ " FROM dbo.DSR_TravellingexpenseMast_tbl Tr INNER JOIN HRms_Employee_master m on tr.empid= m.employeeid "
+ " INNER JOIN Designations d on d.Desig_id=m.designationid WHERE trvexpid =" + TravId;
using (Trans_Main_DAC dac = new Trans_Main_DAC())
{
return(dac.Get_StrQuery_Data(strQuey));
}
}
public DataTable CRM_Get_Trexp_Data(TransUser_Ety tu_ety)
{
string strQuey = "SELECT tr.trvexpid,empid,Convert(varchar,tourfrmdate,106)tourfrmdate, " +
" Convert(varchar,tourtodate,106) tourtodate,dbo.PROPERCASE(firstname+' '+ middlename+' ' + lastname ) tourauthorizedby, " +
" Convert(NUMERIC(18,2),Advancedrawn)Advancedrawn,substring(Convert(VARCHAR,deptime,100),12,8) deptime, " +
" substring(Convert(VARCHAR,arivaltime,100),12,8)arivaltime,repdatetime,Convert(NUMERIC(18,2),Amtclaimed) Amtclaimed,amtdisallowed, " +
" Convert(NUMERIC(18,2),amtpassed) amtpassed,approvedby " +
" FROM dbo.DSR_TravellingexpenseMast_tbl tr " +
" INNER JOIN HRMS_Employee_Master h ON tr.tourauthorizedby =h.Employeeid " +
" WHERE tr.tourfrmdate BETWEEN '" + tu_ety.FromDt + "' and '" + tu_ety.ToDt + "' and tr.empid=" + tu_ety.UserId + " order by trvexpid";
using (Trans_Main_DAC dac = new Trans_Main_DAC())
{
return(dac.Get_StrQuery_Data(strQuey));
}
}
public DataTable CRM_Get_DeptWise_Employees(Int16 DeptId, string type)
{
string strQuey = string.Empty;
if (type == null)
{
strQuey = "select Employeeid,EmployeeCode,[EmployeeName] EmpName " +
" from hrms_EmployeeMast_View where deptid=" + DeptId + " and statusid<>1 order by EmployeeCode";
}
else
{
strQuey = "select Employeeid,EmployeeCode,[EmployeeName] EmpName " +
" from hrms_EmployeeMast_View m inner join hrms_DsrEntryEmployees_tbl h on m.employeeid=h.empid " +
" where deptid=" + DeptId + " and statusid<>1 order by EmployeeName";
}
using (Trans_Main_DAC dac = new Trans_Main_DAC())
{
return(dac.Get_StrQuery_Data(strQuey));
}
}
public DataTable CRM_Get_Depts(string type)
{
string strQuey = string.Empty;
if (type == null)
{
strQuey = "select Dept_id,dbo.propercase(Dept_desc) Dept_desc from dept_details Where Dflag='N' order by Dept_Desc";
}
else
{
strQuey = "SELECT d.Dept_id,dbo.propercase(Dept_desc) Dept_desc FROM dept_details d "
+ " INNER JOIN (select DISTINCT departmentid from hrms_employee_master m "
+ " INNER JOIN hrms_DsrEntryEmployees_tbl dsr on m.Employeeid =dsr.empid where statusid<>1 ) em"
+ " ON em.departmentid=d.Dept_Id Where d.Dflag='N' order by Dept_Desc";
}
using (Trans_Main_DAC dac = new Trans_Main_DAC())
{
return(dac.Get_StrQuery_Data(strQuey));
}
}
public DataTable CRM_Get_KW_TrExp_Main_Details(TransUser_Ety tu_ety)
{
string strQuey = "SELECT tr.trvexpid,empid,a.AreaDescription,Convert(varchar,tourfrmdate,106)tourfrmdate,"
+ "Convert(varchar,tourtodate,106) tourtodate,dbo.PROPERCASE(firstname+' '+ middlename+' ' + lastname ) tourauthorizedby,"
+ " Convert(NUMERIC(18,2),Advancedrawn)Advancedrawn,substring(Convert(VARCHAR,deptime,100),12,8) deptime,"
+ " substring(Convert(VARCHAR,arivaltime,100),12,8)arivaltime,repdatetime,Convert(NUMERIC(18,2),Amtclaimed) Amtclaimed,amtdisallowed,"
+ " Convert(NUMERIC(18,2),amtpassed) amtpassed,approvedby FROM dbo.DSR_TravellingexpenseMast_tbl tr "
+ " INNER JOIN HRMS_Employee_Master h ON tr.tourauthorizedby =h.Employeeid "
+ "INNER JOIN DSR_Travellingexpdetails_tbl d ON d.travellid =tr.trvexpid"
+ " INNER JOIN DSR_AreaMast_Tbl a ON a.AreaId =d.particulars "
+ " INNER JOIN ( SELECT DISTINCT trvexpid FROM DSR_Travellingexpdetails_tbl td INNER JOIN "
+ " DSR_TravellingexpenseMast_tbl tr1 ON td.travellid=tr1.trvexpid WHERE "
+ " tr1.empid=" + tu_ety.UserId + " AND tr1.tourfrmdate BETWEEN '" + tu_ety.FromDt + "' and '" + tu_ety.ToDt + "' /*AND td.authflag ='N'*/) b"
+ " ON b.trvexpid=tr.trvexpid WHERE tr.tourfrmdate BETWEEN '" + tu_ety.FromDt + "' and '" + tu_ety.ToDt + "' "
+ " and tr.empid=" + tu_ety.UserId + " order by trvexpid";
using (Trans_Main_DAC dac = new Trans_Main_DAC())
{
return(dac.Get_StrQuery_Data(strQuey));
}
}
public DataTable CRM_Get_KW_Dsr_Details(TransUser_Ety tu_ety)// Int64 vDsrId, string rbtnDsrAuth)
{
string strQuey = string.Empty;
if (tu_ety.TransMode == "Mast")
{
strQuey = "SELECT DSRId,DSRDate,d.Empid,DSRoption,City_Town AreasVisited,Convert(NUMERIC(18,2),POAmount)POAmount,"
+ " Convert(NUMERIC(18,2),ChequeAmount) ChequeAmount,Convert(NUMERIC(18,2),Cashcollected)Cashcollected, "
+ " Remarks ,isnull(a.AccCode,'') AccCode,isnull(a.AccName,'') AccName,isnull(a.acc_id,0) AccId " +
" FROM dbo.DSR_DailySalesRpt_Tbl d LEFT JOIN GWTME_Sc_Accmast_View a ON d.AccId=a.acc_id WHERE DSRId=" + tu_ety.DocId;
}
else if (tu_ety.TransMode == "Auth")
{
strQuey = "SELECT DSRId,DSRDate,d.Empid,DSRoption,City_Town AreasVisited,Convert(NUMERIC(18,2),POAmount)POAmount,"
+ " Convert(NUMERIC(18,2),ChequeAmount) ChequeAmount,Convert(NUMERIC(18,2),Cashcollected)Cashcollected, "
+ " Remarks ,isnull(a.AccCode,'') AccCode,isnull(a.AccName,'') AccName,isnull(a.acc_id,0) AccId " +
" FROM dbo.DSR_DailySalesRptAuth_Tbl d LEFT JOIN GWTME_Sc_Accmast_View a ON d.AccId=a.acc_id WHERE DSRId=" + +tu_ety.DocId;
}
using (Trans_Main_DAC dac = new Trans_Main_DAC())
{
return(dac.Get_StrQuery_Data(strQuey));
}
}