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