예제 #1
0
        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);
        }
예제 #2
0
        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));
            }
        }
예제 #3
0
        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);
        }
예제 #4
0
        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);
        }
예제 #5
0
        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);
        }
예제 #6
0
        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);
        }
예제 #7
0
        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);
        }
예제 #8
0
        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);
        }
예제 #9
0
        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);
        }
예제 #10
0
        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);
        }
예제 #11
0
        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);
        }
예제 #12
0
        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));
            }
        }
예제 #13
0
        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);
        }
예제 #14
0
        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);
        }
예제 #15
0
        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);
        }
예제 #16
0
        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));
            }
        }
예제 #17
0
        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);
        }
예제 #18
0
        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);
        }
예제 #19
0
        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));
            }
        }
예제 #20
0
        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);
        }
예제 #21
0
        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);
        }
예제 #22
0
        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);
        }
예제 #23
0
        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);
        }
예제 #24
0
        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();
            }
        }
예제 #25
0
        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);
        }
예제 #26
0
        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);
        }