public object Get_Trans_User_WH_Rights(TransUser_Ety tu_ety)
{
object obj = new object();
string strQuey = string.Empty;
if (tu_ety.ParentFormatId == 38)
{
strQuey = " SELECT count(Docid) FROM ETRS_TRANSDTL1_AUTHOR_Tbl t INNER JOIN Etrs_UsrRights_Tbl u ON " +
" t.FROMWAREHOUSE = u.Security_Id WHERE t.FROMWAREHOUSE<>0 " +
" AND t.DOCID =" + tu_ety.DocId + " AND u.EnableP='Y' AND u.Usr_Id = " + tu_ety.UserId + " AND u.FinYear_Id= " + tu_ety.FinId + "";
}
else
{
strQuey = " SELECT count(Docid) FROM ETRS_TRANSDTL1_AUTHOR_Tbl t INNER JOIN Etrs_UsrRights_Tbl u ON " +
" t.WARE_HOUSE_ID = u.Security_Id WHERE t.WARE_HOUSE_ID<>0 " +
" AND t.DOCID =" + tu_ety.DocId + " AND u.EnableP='Y' AND u.Usr_Id = " + tu_ety.UserId + " AND u.FinYear_Id= " + tu_ety.FinId + "";
}
if (strQuey != string.Empty)
{
obj = Get_GWTME_StrQuery_Object(strQuey);
}
return(obj);
}
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 Get_TransUserRights(TransUser_Ety tu_ety)
{
//CRM_BDF_Get_TransUser_Rights_Proc (@UserId BIGINT, @FormatId SMALLINT, @FinId SMALLINT, @Flag VARCHAR (10)
DataTable dt = new DataTable();
cn2.Open();
string strProc = string.Empty;
try
{
SqlParameter _UserId = new SqlParameter("@UserId", SqlDbType.BigInt);
_UserId.Value = tu_ety.UserId;
SqlParameter _FormatId = new SqlParameter("@FormatId", SqlDbType.SmallInt);
_FormatId.Value = tu_ety.FormatId;
SqlParameter _FinId = new SqlParameter("@FinId", SqlDbType.BigInt);
_FinId.Value = tu_ety.FinId;
SqlParameter _Flag = new SqlParameter("@Flag", SqlDbType.VarChar, 50);
_Flag.Value = tu_ety.TransMode;
SqlParameter[] paramFields = { _UserId, _FormatId, _FinId, _Flag };
strProc = "etrs_Get_TransUser_Rights_Proc";
if (strProc != string.Empty)
{
SqlDataReader sdr = SqlHelper.ExecuteReader(cn2, CommandType.StoredProcedure, strProc, paramFields);
dt.Load(sdr);
if (!sdr.IsClosed)
{
sdr.Close();
}
}
}
catch (Exception msg)
{
cn2.Close();
if (res == null)
{
res = msg.Message;
}
}
finally
{
if (cn2.State == ConnectionState.Open)
{
cn2.Close();
}
if (cn2 != null)
{
cn2.Dispose();
}
}
return(dt);
}
public DataTable Get_User_Childs(TransUser_Ety tu_ety)
{
DataTable dt = new DataTable();
string strQuey = "SELECT * FROM [dbo].[GetUserChild] (" + Convert.ToInt16(tu_ety.UserId) + ")";
if (strQuey != string.Empty)
{
dt = Get_GWTME_StrQuery_Data(strQuey);
}
return(dt);
}
public object Get_AccLedgerType(TransUser_Ety tu_ety)
{
object obj = new object();
string strQuey = string.Empty;
strQuey = "SELECT ACC_LEDGERTYPE FROM Etrs_AccMast_Tbl WHERE ACC_ID = " + tu_ety.AccId;
if (strQuey != string.Empty)
{
obj = Get_GWTME_StrQuery_Object(strQuey);
}
return(obj);
}
public object Check_PartyWiseNlp(TransUser_Ety tu_ety)
{
object obj = new object();
string strQuey = "select 1 from Etrs_NLPSchemeAccountLink_Tbl a "
+ " inner join etrs_NlpSchemeMaster_Tbl s on s.NlpScheme_Id =a.SchemeId "
+ " where s.Discontinue='N' AND convert(date,getdate()) BETWEEN convert(date,s.SchemeStart_Date) AND convert(date,s.SchemeEnd_Date) "
+ " and a.AccountId = " + tu_ety.AccId + " ";
obj = Get_GWTME_StrQuery_Object(strQuey);
return(obj);
}
public DataTable Get_Trans_Contacts(TransUser_Ety tu_ety)
{
DataTable dt = new DataTable();
cn1.Open();
string strProc = string.Empty;
try
{
SqlParameter _docid = new SqlParameter("@docid", SqlDbType.BigInt);
_docid.Value = tu_ety.DocId;
SqlParameter[] paramFields = { _docid };
strProc = "CRM_GetContactEmailbsdOnAcc_proc";
if (strProc != string.Empty)
{
SqlDataReader sdr = SqlHelper.ExecuteReader(cn1, CommandType.StoredProcedure, strProc, paramFields);
dt.Load(sdr);
if (!sdr.IsClosed)
{
sdr.Close();
}
}
}
catch (Exception msg)
{
cn1.Close();
if (res == null)
{
res = msg.Message;
}
}
finally
{
if (cn1.State == ConnectionState.Open)
{
cn1.Close();
}
if (cn1 != null)
{
cn1.Dispose();
}
}
//SqlParameter _docid = new SqlParameter("@docid", SqlDbType.BigInt);
//_docid.Value = docid;
//SqlParameter[] paras = { _docid };
//SqlDataReader dr = SqlHelper.ExecuteReader(setCon(), CommandType.StoredProcedure, "crm_getContactEmailbsdOnAcc_proc", paras);
//if (dr.HasRows)
// dt.Load(dr);
return(dt);
}
public DataTable Get_TransFormats(TransUser_Ety tu_ety)
{
DataTable dt = new DataTable();
cn1.Open();
string strProc = string.Empty;
try
{
SqlParameter usr_Id, fin_Id, comp_Id;
usr_Id = new SqlParameter("@UserId", SqlDbType.SmallInt);
usr_Id.Value = tu_ety.UserId;
fin_Id = new SqlParameter("@FinId", SqlDbType.SmallInt);
fin_Id.Value = tu_ety.FinId;
comp_Id = new SqlParameter("@CompId", SqlDbType.SmallInt);
comp_Id.Value = tu_ety.CompId;
SqlParameter[] paramFields = { usr_Id, fin_Id, comp_Id };
strProc = "CRM_Trans_Get_Formats_Proc";
if (strProc != string.Empty)
{
SqlDataReader sdr = SqlHelper.ExecuteReader(cn1, CommandType.StoredProcedure, strProc, paramFields);
dt.Load(sdr);
if (!sdr.IsClosed)
{
sdr.Close();
}
}
}
catch (Exception msg)
{
cn1.Close();
if (res == null)
{
res = msg.Message;
}
}
finally
{
if (cn1.State == ConnectionState.Open)
{
cn1.Close();
}
if (cn1 != null)
{
cn1.Dispose();
}
}
return(dt);
}
public object Check_NlpFormat(TransUser_Ety tu_ety)
{
object obj = new object();
string strQuey = string.Empty;
strQuey = "SELECT count(f.Format_Id) FROM Etrs_Formats_Tbl f INNER JOIN Etrs_FdFormatDetails_Tbl fd "
+ " ON f.Format_Id =fd.Format_Id WHERE f.Parent_Id =27 AND fd.Field_Id =432 AND f.Format_Id =" + tu_ety.FormatId;
if (strQuey != string.Empty)
{
obj = Get_GWTME_StrQuery_Object(strQuey);
}
return(obj);
}
public DataTable CRM_Get_KW_DSR_Summary(TransUser_Ety tu_ety)
{
DataTable dt = new DataTable();
cn1.Open();
string strProc = string.Empty;
try
{
SqlParameter[] ParamDSRSummary = new SqlParameter[2];
ParamDSRSummary[0] = new SqlParameter("@fromDate", SqlDbType.SmallDateTime);
ParamDSRSummary[1] = new SqlParameter("@toDate", SqlDbType.SmallDateTime);
ParamDSRSummary[0].Value = tu_ety.FromDt;
ParamDSRSummary[1].Value = tu_ety.ToDt;
strProc = "Dsr_DsrSummary_Proc";
if (strProc != string.Empty)
{
SqlDataReader sdr = SqlHelper.ExecuteReader(cn1, CommandType.StoredProcedure, strProc, ParamDSRSummary);
dt.Load(sdr);
if (!sdr.IsClosed)
{
sdr.Close();
}
}
}
catch (Exception msg)
{
cn1.Close();
if (res == null)
{
res = msg.Message;
}
}
finally
{
if (cn1.State == ConnectionState.Open)
{
cn1.Close();
}
if (cn1 != null)
{
cn1.Dispose();
}
}
return(dt);
}
public object Get_Trans_DocAmount(TransUser_Ety tu_ety)
{
object obj = new object();
string strQuey = string.Empty;
strQuey = "Select isnull((SELECT isnull(docamount,0) FROM ETRS_TRANSHDR_MAST_TBL where docid =" + tu_ety.DocId + "),0)";
if (strQuey != string.Empty)
{
obj = Get_GWTME_StrQuery_Object(strQuey);
}
return(obj);
}
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 Check_Trans_BeyondCreditLimit(TransUser_Ety tu_ety)
{
DataTable dt = new DataTable();
string strQuey = string.Empty;
strQuey = "SELECT isnull(a.Crdt_Lmt,0) Crdt_Lmt,isnull(a.Crdt_Period,0)Crdt_Period,isnull(ad.CreditLmt,0) CreditLmt,isnull(ad.CreditPeriod,0) CreditPeriod,isnull(a.Lower_CrdtLmt,0) Lower_CrdtLmt,a.allowcombi " +
" FROM ETRS_AccDtls_Tbl a,Etrs_AccDept_tbl ad " +
" WHERE a.Acc_Id=ad.Acc_Id and a.Acc_Id=" + tu_ety.AccId + " " +
" AND ad.dept_Id=" + tu_ety.DeptId + "";
if (strQuey != string.Empty)
{
dt = Get_StrQuery_Data(strQuey);
}
return(dt);
}
public object Check_NlpFailure(TransUser_Ety tu_ety)
{
object obj = new object();
//etrs_CheckNlpFailure_Proc(@DocId BigInt,@AllowMgmtNlpRate varchar(1))
cn2.Open();
string strProc = string.Empty;
try
{
SqlParameter pDocId = new SqlParameter("@DocId", SqlDbType.BigInt);
pDocId.Value = tu_ety.DocId;
SqlParameter pAllowMgmtNlpRate = new SqlParameter("@AllowMgmtNlpRate", SqlDbType.VarChar, 1);
pAllowMgmtNlpRate.Value = tu_ety.TransMode;
SqlParameter[] paramFields = { pDocId, pAllowMgmtNlpRate };
strProc = "etrs_CheckNlpFailure_Proc";
if (strProc != string.Empty)
{
obj = SqlHelper.ExecuteScalar(cn2, CommandType.StoredProcedure, strProc, paramFields);
}
}
catch (Exception msg)
{
cn2.Close();
if (res == null)
{
res = msg.Message;
}
}
finally
{
if (cn2.State == ConnectionState.Open)
{
cn2.Close();
}
if (cn2 != null)
{
cn2.Dispose();
}
}
return(obj);
}
public DataTable Check_Trans_Stock(TransUser_Ety tu_ety)
{
DataTable dt = new DataTable();
string strQuey = string.Empty;
if (tu_ety.TransMode == "Mast")
{
strQuey = "SELECT sum(CASE td.TYPESGN WHEN 'C' THEN Qty ELSE -Qty End)transstock, " +
" itemid,WARE_HOUSE_ID , td.color_id ,docid,p.Item_Desc ,w.Wh_Name,c.Color_Desc, " +
" (SELECT isnull((opng_Balqty+rec_qty-Iss_qty),0) FROM Etrs_ProdStock_Tbl " +
" WHERE Item_Id =td.ITEMID AND Wh_Id =td.WARE_HOUSE_ID AND color_id=td.COLOR_ID AND " +
" YearId =(SELECT year_id FROM ETRS_TRANSHDR_Mast_TBL WHERE DOCID =td.docid)) stock " +
" FROM Etrs_TRANSDTL1_Tbl td " +
" INNER JOIN Etrs_ProdMast_Tbl p ON td.ITEMID=p.Item_Id " +
" INNER JOIN ETRS_WareHouseMast_Tbl w ON w.Wh_Id=td.WARE_HOUSE_ID" +
" INNER JOIN ETRS_ColorMast_Tbl c ON c.Color_Id =td.COLOR_ID " +
" WHERE docid=" + tu_ety.DocId + " GROUP BY itemid, WARE_HOUSE_ID,p.Item_Desc ," +
" w.Wh_Name,c.Color_Desc,td.color_id,docid";
}
else if (tu_ety.TransMode == "Auth")
{
strQuey = "SELECT sum(CASE td.TYPESGN WHEN 'C' THEN Qty ELSE -Qty End)transstock, " +
" itemid,WARE_HOUSE_ID , td.color_id ,docid,p.Item_Desc ,w.Wh_Name,c.Color_Desc, " +
" (SELECT isnull((opng_Balqty+rec_qty-Iss_qty),0) FROM Etrs_ProdStock_Tbl " +
" WHERE Item_Id =td.ITEMID AND Wh_Id =td.WARE_HOUSE_ID AND color_id=td.COLOR_ID AND " +
" YearId =(SELECT year_id FROM ETRS_TRANSHDR_AUTHOR_TBL WHERE DOCID =td.docid)) stock " +
" FROM Etrs_TRANSDTL1_author_Tbl td " +
" INNER JOIN Etrs_ProdMast_Tbl p ON td.ITEMID=p.Item_Id " +
" INNER JOIN ETRS_WareHouseMast_Tbl w ON w.Wh_Id=td.WARE_HOUSE_ID" +
" INNER JOIN ETRS_ColorMast_Tbl c ON c.Color_Id =td.COLOR_ID " +
" WHERE docid=" + tu_ety.DocId + " GROUP BY itemid, WARE_HOUSE_ID,p.Item_Desc ," +
" w.Wh_Name,c.Color_Desc,td.color_id,docid";
}
if (strQuey != string.Empty)
{
dt = Get_GWTME_StrQuery_Data(strQuey);
}
return(dt);
}
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 Check_Trans_CreditDays(TransUser_Ety tu_ety)
{
DataTable dt = new DataTable();
string strQuey = string.Empty;
if (tu_ety.DeptId == 0)
{
strQuey = "SELECT th.docid FROM ETRS_TRANSHDR_MAST_TBL th ,etrs_TRANSDTL1_tbl td WHERE th.docid=td.docid AND td.pending_qty>0 and th.format_id=" + tu_ety.FormatId + " AND th.accid=" + tu_ety.AccId + " AND datediff(day,docdt,getdate()) >(SELECT crdt_period FROM ETRS_Accdtls_Tbl am WHERE acc_id =" + tu_ety.AccId + ")";
}
else
{
strQuey = "SELECT th.docid FROM ETRS_TRANSHDR_MAST_TBL th ,etrs_TRANSDTL1_tbl td WHERE th.docid=td.docid AND td.pending_qty>0 and th.format_id=" + tu_ety.FormatId + " AND th.accid=" + tu_ety.AccId + " AND th.deptid=" + tu_ety.DeptId + " and datediff(day,docdt,getdate()) >(SELECT CreditPeriod FROM ETRS_Accdept_Tbl am WHERE acc_id =" + tu_ety.AccId + " and dept_id=" + tu_ety.DeptId + " )";
}
if (strQuey != string.Empty)
{
dt = Get_GWTME_StrQuery_Data(strQuey);
}
return(dt);
}
public object Check_Trans_DocNoExist(TransUser_Ety tu_ety)
{
object obj = new object();
string strQuey = string.Empty;
if (tu_ety.TransMode == "Mast")
{
strQuey = "SELECT count(docid) FROM Crm_TransHdr_Mast_Tbl WHERE DOCID =" + tu_ety.DocId;
}
else if (tu_ety.TransMode == "Auth")
{
strQuey = "SELECT count(docid) FROM Crm_TransHdr_Author_Tbl WHERE DOCID =" + tu_ety.DocId;
}
if (strQuey != string.Empty)
{
obj = Get_StrQuery_Object(strQuey);
}
return(obj);
}
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));
}
}
public object Get_Trans_SumOutStandDays(TransUser_Ety tu_ety, decimal lowercrdlmt)
{
object obj = new object();
string strQuey = string.Empty;
if (tu_ety.TransMode == "acc")
{
strQuey = "SELECT isnull(max(datediff(dd, docdt, getdate())),0) OutStandDays FROM Etrs_OutStnd_tbl " +
"WHERE OTsign='D' and OTSTNDAMT > " + lowercrdlmt + " and ACCID=" + tu_ety.AccId + "";
}
else if (tu_ety.TransMode == "cost")
{
strQuey = "SELECT isnull(max(datediff(dd, docdt, getdate())),0) OutStandDays FROM Etrs_OutStnd_tbl " +
"WHERE OTsign='D' and OTSTNDAMT > " + lowercrdlmt + " and ACCID=" + tu_ety.AccId + "" +
" and deptid=" + tu_ety.DocId + " ";
}
if (strQuey != string.Empty)
{
obj = Get_GWTME_StrQuery_Object(strQuey);
}
return(obj);
}
public object Get_Trans_SumOutStandAmount(TransUser_Ety tu_ety)
{
object obj = new object();
string strQuey = string.Empty;
if (tu_ety.TransMode == "acc")
{
strQuey = "select isnull(sum( case when (OTSIGN ='C')then -1* OTSTNDAMT else OTSTNDAMT end),0) OutStandAmt FROM Etrs_OutStnd_tbl " +
"WHERE OTSTNDAMT > 0 and ACCID=" + tu_ety.AccId + "";
}
if (tu_ety.TransMode == "cost")
{
strQuey = "select isnull(sum( case when (OTSIGN ='C')then -1* OTSTNDAMT else OTSTNDAMT end),0) OutStandAmt FROM Etrs_OutStnd_tbl " +
"WHERE OTSTNDAMT > 0 and ACCID=" + tu_ety.AccId + "" +
" and deptid=" + tu_ety.DocId + " ";
}
if (strQuey != string.Empty)
{
obj = Get_GWTME_StrQuery_Object(strQuey);
}
return(obj);
}
public DataTable CRM_Get_Sales_AnalsisData(TransUser_Ety tu_ety)
{
//@UserId SMALLINT,@UserType VARCHAR(20),@CompanyType varchar(10)
DataTable dt = new DataTable();
SqlParameter _FromDt, _ToDt, _UserId, _UserType, _CompanyType;
_FromDt = new SqlParameter("@FromDt", SqlDbType.SmallDateTime);
_FromDt.Value = tu_ety.FromDt;
_ToDt = new SqlParameter("@ToDt", SqlDbType.SmallDateTime);
_ToDt.Value = tu_ety.ToDt;
_UserId = new SqlParameter("@UserId", SqlDbType.BigInt);
_UserId.Value = tu_ety.UserId;
_UserType = new SqlParameter("@UserType", SqlDbType.VarChar);
_UserType.Value = tu_ety.UserType;
_CompanyType = new SqlParameter("@CompanyType", SqlDbType.VarChar);
_CompanyType.Value = tu_ety.TransMode;
SqlParameter[] paramFields = { _FromDt, _ToDt, _UserId, _UserType, _CompanyType };
//CRM_Trans_Get_Transactions_proc(@FromDt Date,@ToDt Date,@Mode varchar(50), @FormatId smallint)
cn1.Open();
string strProc = string.Empty;
try
{
strProc = "Crm_Get_SalesAnalysisData_Proc";
if (strProc != string.Empty)
{
SqlDataReader sdr = SqlHelper.ExecuteReader(cn1, CommandType.StoredProcedure, strProc, paramFields);
dt.Load(sdr);
if (!sdr.IsClosed)
{
sdr.Close();
}
}
}
catch (Exception msg)
{
cn1.Close();
if (res == null)
{
res = msg.Message;
}
}
finally
{
if (cn1.State == ConnectionState.Open)
{
cn1.Close();
}
if (cn1 != null)
{
cn1.Dispose();
}
}
return(dt);
}
public DataTable Get_Transactions(TransUser_Ety tu_ety)
{
DataTable dt = new DataTable();
SqlParameter _FromDt, _ToDt, _Mode, _FormatId;
_FromDt = new SqlParameter("@FromDt", SqlDbType.Date);
_FromDt.Value = tu_ety.FromDt;
_ToDt = new SqlParameter("@ToDt", SqlDbType.Date);
_ToDt.Value = tu_ety.ToDt;
_Mode = new SqlParameter("@Mode", SqlDbType.VarChar, 50);
_Mode.Value = tu_ety.TransMode;
_FormatId = new SqlParameter("@FormatId", SqlDbType.SmallInt);
_FormatId.Value = tu_ety.FormatId;
SqlParameter[] paramFields = { _FromDt, _ToDt, _Mode, _FormatId };
if (tu_ety.ParentFormatId == 27)
{
//Etrs_Get_Transactions_proc(@FromDt Date,@ToDt Date,@Mode varchar(50), @FormatId smallint)
cn2.Open();
string strProc = string.Empty;
try
{
strProc = "Etrs_Get_Transactions_proc";
if (strProc != string.Empty)
{
SqlDataReader sdr = SqlHelper.ExecuteReader(cn2, CommandType.StoredProcedure, strProc, paramFields);
dt.Load(sdr);
if (!sdr.IsClosed)
{
sdr.Close();
}
}
}
catch (Exception msg)
{
cn2.Close();
if (res == null)
{
res = msg.Message;
}
}
finally
{
if (cn2.State == ConnectionState.Open)
{
cn2.Close();
}
if (cn2 != null)
{
cn2.Dispose();
}
}
}
else
{
//CRM_Trans_Get_Transactions_proc(@FromDt Date,@ToDt Date,@Mode varchar(50), @FormatId smallint)
cn1.Open();
string strProc = string.Empty;
try
{
strProc = "CRM_Trans_Get_Transactions_proc";
if (strProc != string.Empty)
{
SqlDataReader sdr = SqlHelper.ExecuteReader(cn1, CommandType.StoredProcedure, strProc, paramFields);
dt.Load(sdr);
if (!sdr.IsClosed)
{
sdr.Close();
}
}
}
catch (Exception msg)
{
cn1.Close();
if (res == null)
{
res = msg.Message;
}
}
finally
{
if (cn1.State == ConnectionState.Open)
{
cn1.Close();
}
if (cn1 != null)
{
cn1.Dispose();
}
}
}
return(dt);
}
public string Detlete_Trans_Data(TransUser_Ety tu_ety)
{
SqlConnection cn; //= new SqlConnection();
if (tu_ety.ParentFormatId == 27)
{
cn = new SqlConnection(ConfigurationManager.ConnectionStrings["GWTME_ConnStr"].ConnectionString);
}
else
{
cn = new SqlConnection(ConfigurationManager.ConnectionStrings["Crm_ConnStr"].ConnectionString);
}
cn.Open();
SqlTransaction st = cn.BeginTransaction();
string restransauth = string.Empty;
try
{
SqlParameter[] ProductParams = new SqlParameter[9];
ProductParams[0] = new SqlParameter("@InDocId1", SqlDbType.BigInt);
ProductParams[1] = new SqlParameter("@InFormat_Id1", SqlDbType.SmallInt);
ProductParams[2] = new SqlParameter("@InYearId", SqlDbType.SmallInt);
ProductParams[3] = new SqlParameter("@InCompId", SqlDbType.SmallInt);
ProductParams[4] = new SqlParameter("@InUsrId", SqlDbType.SmallInt);
ProductParams[5] = new SqlParameter("@InMode", SqlDbType.VarChar);
ProductParams[6] = new SqlParameter("@Result", SqlDbType.VarChar, 200);
ProductParams[7] = new SqlParameter("@ExecStatus", SqlDbType.Int);
ProductParams[8] = new SqlParameter("@InChngUsrId", SqlDbType.SmallInt);//added to check with change over user 29 dec 2009
ProductParams[7].Direction = ParameterDirection.Output;
ProductParams[6].Direction = ParameterDirection.Output;
ProductParams[0].Value = tu_ety.DocId;
ProductParams[1].Value = tu_ety.FormatId;
ProductParams[2].Value = tu_ety.FinId;
ProductParams[3].Value = tu_ety.CompId;
ProductParams[4].Value = tu_ety.UserId;
ProductParams[5].Value = tu_ety.TransMode;
ProductParams[6].Value = "";
ProductParams[7].Value = 0;
ProductParams[8].Value = tu_ety.UserId;
if (tu_ety.ParentFormatId == 27)
{
SqlHelper.ExecuteNonQuery(st, CommandType.StoredProcedure, "Etrs_TransDelete_Proc", ProductParams);
}
else
{
SqlHelper.ExecuteNonQuery(st, CommandType.StoredProcedure, "CRM_TransDelete_Proc", ProductParams);
}
if (Convert.ToInt16(ProductParams[7].Value) == 0)
{
st.Rollback();
cn.Close();
return(ProductParams[6].Value.ToString());
}
restransauth = ProductParams[6].Value.ToString();
st.Commit();
return(restransauth);
}
catch (Exception ex)
{
// objLog.writeLog(HttpContext.Current.Session["UserName"].ToString(), "PSI transaction Delete", "Detete Error", HttpContext.Current.Session.SessionID, ex.Message);
st.Rollback();
cn.Close();
return(ex.Message.Replace("'", " "));
}
finally
{
cn.Close();
}
}
public string Save_Trans_Data(string hdocid, string strslno, string hdrstatus, string dtlstatus, string flag, string finflag, string stkflag,
string vLimitFlag, string vLimitMsg, Hashtable htGrHdr, Hashtable htGrDtl, string NLPAuth, TransUser_Ety tu_ety)
{
SqlConnection cn; //= new SqlConnection();
if (tu_ety.ParentFormatId == 27)
{
cn = new SqlConnection(ConfigurationManager.ConnectionStrings["GWTME_ConnStr"].ConnectionString);
}
else
{
cn = new SqlConnection(ConfigurationManager.ConnectionStrings["Crm_ConnStr"].ConnectionString);
}
cn.Open();
SqlTransaction st = cn.BeginTransaction();
string restransauth = string.Empty;
try
{
SqlParameter[] transauth = new SqlParameter[11];
transauth[0] = new SqlParameter("@DocId1", SqlDbType.VarChar, 500);
transauth[1] = new SqlParameter("@HdrStatus1", SqlDbType.VarChar, 500);
transauth[2] = new SqlParameter("@DtlTblFlg1 ", SqlDbType.VarChar, 500);
transauth[3] = new SqlParameter("@Format_Id ", SqlDbType.VarChar, 50);
transauth[4] = new SqlParameter("@CmpId", SqlDbType.SmallInt);
transauth[5] = new SqlParameter("@Yearid", SqlDbType.SmallInt);
transauth[6] = new SqlParameter("@SecurityId ", SqlDbType.SmallInt);
transauth[7] = new SqlParameter("@UserId", SqlDbType.SmallInt);
transauth[8] = new SqlParameter("@Result", SqlDbType.VarChar, 200);
transauth[9] = new SqlParameter("@ExecStatus", SqlDbType.TinyInt);
transauth[10] = new SqlParameter("@ChngUserId", SqlDbType.SmallInt);//31 dec 2009
transauth[0].Value = hdocid;
transauth[1].Value = hdrstatus;
transauth[2].Value = flag;
transauth[3].Value = tu_ety.FormatId;
transauth[4].Value = tu_ety.CompId;
transauth[5].Value = tu_ety.FinId;
transauth[6].Value = 503;
transauth[7].Value = tu_ety.UserId;
transauth[8].Value = "";
transauth[8].Direction = ParameterDirection.Output;
transauth[9].Value = 0;
transauth[9].Direction = ParameterDirection.Output;
transauth[10].Value = tu_ety.UserId;
if (tu_ety.ParentFormatId == 27)
{
SqlHelper.ExecuteNonQuery(st, CommandType.StoredProcedure, "etrsCrm_TransAuth_Proc", transauth);
}
else
{
SqlHelper.ExecuteNonQuery(st, CommandType.StoredProcedure, "Crm_TransAuth_Proc", transauth);
}
restransauth = transauth[8].Value.ToString() + "; ";
if (Convert.ToInt16(transauth[9].Value) == 0)
{
st.Rollback();
cn.Close();
return(restransauth);
}
if (vLimitFlag == "Y" && (tu_ety.CompId == 8 || tu_ety.CompId == 7 || tu_ety.CompId == 11))
{
SqlParameter[] transSendMail = new SqlParameter[6];
transSendMail[0] = new SqlParameter("@Accid", SqlDbType.SmallInt);
transSendMail[1] = new SqlParameter("@DeptId", SqlDbType.SmallInt);
transSendMail[2] = new SqlParameter("@Docid", SqlDbType.BigInt);
transSendMail[3] = new SqlParameter("@Reason", SqlDbType.VarChar, 1000);
transSendMail[4] = new SqlParameter("@OutErrMsg", SqlDbType.VarChar, 200);
transSendMail[5] = new SqlParameter("@ExecStatus", SqlDbType.TinyInt);
transSendMail[4].Direction = ParameterDirection.Output;
transSendMail[5].Direction = ParameterDirection.Output;
transSendMail[0].Value = Convert.ToInt16(tu_ety.AccId);
transSendMail[1].Value = tu_ety.DeptId;
transSendMail[2].Value = Convert.ToInt64(hdocid.Replace("~", ""));
transSendMail[3].Value = vLimitMsg;
transSendMail[4].Value = "result";
transSendMail[5].Value = 0;
int rsno = SqlHelper.ExecuteNonQuery(st, CommandType.StoredProcedure, "Etrs_SendCreditLimitMastMail_Proc", transSendMail);
if (Convert.ToInt16(transSendMail[5].Value) == 0)
{
restransauth = transSendMail[4].Value.ToString();
st.Rollback();
cn.Close();
return(restransauth);
}
}
if (tu_ety.ParentFormatId == 27)
{
if ((tu_ety.CompId == 8 && tu_ety.AccId == 18046) || (tu_ety.CompId == 7 && tu_ety.AccId == 8229))
{
SqlParameter pSchChkEmail = new SqlParameter("@Indocid", SqlDbType.BigInt);
pSchChkEmail.Value = Convert.ToInt64(hdocid.Replace("~", ""));
SqlHelper.ExecuteNonQuery(st, CommandType.StoredProcedure, "Etrs_EmailScehemeCheckSalesOrderMast_Proc", pSchChkEmail);
}
}
if (NLPAuth == "Y")
{
SqlParameter[] transWhSendMail = new SqlParameter[1];
transWhSendMail[0] = new SqlParameter("@Indocid", SqlDbType.BigInt);
transWhSendMail[0].Value = Convert.ToInt64(hdocid.Replace("~", ""));
int rsno = SqlHelper.ExecuteNonQuery(st, CommandType.StoredProcedure, "Etrs_EmailNlpSalesOrderMast_Proc", transWhSendMail);
}
st.Commit();
}
catch (Exception ex)
{
st.Rollback();
return(ex.Message);
}
if (tu_ety.ParentFormatId == 27)
{
try
{ //for checking credit and debit amounts if docid.IF not matched both then inserting that docid into temp table*/
SqlParameter[] chkoutparam = new SqlParameter[2];
chkoutparam[0] = new SqlParameter("@InDocId", SqlDbType.BigInt);
chkoutparam[1] = new SqlParameter("@InYearId", SqlDbType.SmallInt);
chkoutparam[0].Value = Convert.ToInt64(hdocid.Replace("~", ""));
chkoutparam[1].Value = tu_ety.FinId;
int j = SqlHelper.ExecuteNonQuery(cn, CommandType.StoredProcedure, "Etrs_CheckOutStndAmt_Proc", chkoutparam);
}
catch (Exception ex)
{
return(ex.Message);
}
finally
{
cn.Close();
}
}
return(restransauth);
}
public DataTable Get_Trans_Hdr_Data(TransUser_Ety tu_ety)
{
DataTable dt = new DataTable();
string strProc = string.Empty;
SqlConnection cn;
if (tu_ety.ParentFormatId == 27)
{
cn = new SqlConnection(ConfigurationManager.ConnectionStrings["GWTME_ConnStr"].ConnectionString);
}
else
{
cn = new SqlConnection(ConfigurationManager.ConnectionStrings["Crm_ConnStr"].ConnectionString);
}
cn.Open();
try
{
SqlParameter pDocId = new SqlParameter("@DocId", SqlDbType.BigInt);
pDocId.Value = tu_ety.DocId;
SqlParameter _Mode = new SqlParameter("@Mode", SqlDbType.VarChar);
_Mode.Value = tu_ety.TransMode;
SqlParameter[] paramFields = { pDocId, _Mode };
if (tu_ety.ParentFormatId == 27)
{
strProc = "Etrs_Trans_Get_TransHdr_Proc";
}
else
{
strProc = "CRM_Trans_Get_TransHdr_Proc";
}
if (strProc != string.Empty)
{
SqlDataReader sdr = SqlHelper.ExecuteReader(cn, CommandType.StoredProcedure, strProc, paramFields);
dt.Load(sdr);
if (!sdr.IsClosed)
{
sdr.Close();
}
}
}
catch (Exception msg)
{
cn.Close();
if (res == null)
{
res = msg.Message;
}
}
finally
{
if (cn.State == ConnectionState.Open)
{
cn.Close();
}
if (cn != null)
{
cn.Dispose();
}
}
return(dt);
}
