private void GenerateReport(string code) { DL_DBAccess = new DatabaseAccessLayer(); DataTable dtCreditNote = new DataTable(); DataTable dtTemp = new DataTable(); DataSet dsCreditNote = new DataSet(); ReportDocument rptname = null; rptname = new ReportDocument(); try { dtCreditNote = CommonClasses.Execute("SELECT DISTINCT(I_CODE),CNM_SERIAL_NO,ISNULL(CNM_GSTIN_NO,'') as CNM_GSTIN_NO,ISNULL(CNM_EWAY_BILL_NO,'') as CNM_EWAY_BILL_NO,CONVERT(VARCHAR,CNM_DATE,103) AS CNM_DATE ,CNM_CUST_ADDRESS,CNM_CUST_STATE_NAME,CNM_CUST_STATE_CODE, CAST(ISNULL(CNM_GRAND_TOTAL,0) AS NUMERIC(20,2)) AS CNM_GRAND_TOTAL,CAST(ISNULL(CNM_CENTRAL_TAX_AMT,0) AS NUMERIC(20,2)) AS CNM_CENTRAL_TAX_AMT, CAST(ISNULL(CNM_STATE_UNION_TAX_AMT,0) AS NUMERIC(20,2)) AS CNM_STATE_UNION_TAX_AMT,CAST(ISNULL(CNM_INTEGRATED_TAX_AMT,0) AS NUMERIC(20,2)) AS CNM_INTEGRATED_TAX_AMT, CAST(ISNULL(CNM_CESS_TAX_AMT,0) AS NUMERIC(20,2)) AS CNM_CESS_TAX_AMT,CAST(ISNULL(CNM_NET_AMOUNT,0) AS NUMERIC(20,2)) AS CNM_NET_AMOUNT, CAST(ISNULL(CND_QTY,0) AS NUMERIC (20,2)) AS CND_QTY, CAST(ISNULL(CND_RATE,0) AS NUMERIC(20,2)) AS CND_RATE,CAST(ISNULL(CND_AMOUNT,0) AS NUMERIC(20,2)) AS CND_AMOUNT, CAST(ISNULL(CND_CENTRAL_TAX,0) AS NUMERIC(20,2)) AS CND_CENTRAL_TAX, CAST(ISNULL(CND_STATE_UNION_TAX,0) AS NUMERIC(20,2)) AS CND_STATE_UNION_TAX, CAST(ISNULL(CND_INTEGRATED_TAX,0) AS NUMERIC(20,2)) AS CND_INTEGRATED_TAX,CAST(ISNULL(CND_CESS_TAX,0) AS NUMERIC(20,2)) AS CND_CESS_TAX,CAST(ISNULL(CND_CENTRAL_TAX_AMT,0) AS NUMERIC(20,2)) AS CND_CENTRAL_TAX_AMT, CAST(ISNULL(CND_STATE_UNION_TAX_AMT,0) AS NUMERIC(20,2)) AS CND_STATE_UNION_TAX_AMT, CAST(ISNULL(CND_INTEGRATED_TAX_AMT,0) AS NUMERIC(20,2)) AS CND_INTEGRATED_TAX_AMT, CAST(ISNULL(CND_CESS_TAX_AMT,0) AS NUMERIC(20,2)) AS CND_CESS_TAX_AMT, P_CODE,P_NAME,P_ADD1,P_VEND_CODE,P_CST,P_VAT,P_ECC_NO,ISNULL(P_LBT_NO,'') AS P_GST_NO ,P_PIN_CODE,P_PARTY_CODE,0 as E_TARIFF_NO ,SM_NAME,SM_STATE_CODE ,I_CODENO,I_NAME,ITEM_UNIT_MASTER.I_UOM_NAME FROM CREDIT_NOTE_MASTER,CREDIT_NOTE_DETAILS,PARTY_MASTER ,ITEM_MASTER,ITEM_UNIT_MASTER,STATE_MASTER WHERE CREDIT_NOTE_MASTER.CNM_CODE=CREDIT_NOTE_DETAILS.CND_CNM_CODE AND CREDIT_NOTE_MASTER.CNM_CUST_CODE=PARTY_MASTER.P_CODE AND ITEM_MASTER.I_CODE=CREDIT_NOTE_DETAILS.CND_ITEM_CODE AND ITEM_UNIT_MASTER.I_UOM_CODE=ITEM_MASTER.I_UOM_CODE AND STATE_MASTER.ES_DELETE=0 AND STATE_MASTER.SM_CODE=P_SM_CODE AND CND_CNM_CODE='"+ Credit_Code + "' "); DataTable dtComp = CommonClasses.Execute("SELECT * FROM COMPANY_MASTER WHERE CM_ID='" + Session["CompanyId"] + "' AND ISNULL(CM_DELETE_FLAG,0) ='0'"); if (dtComp.Rows.Count > 0) { if (dtCreditNote.Rows.Count > 0) { rptname.Load(Server.MapPath("~/Reports/rptCreditNote.rpt")); rptname.FileName = Server.MapPath("~/Reports/rptCreditNote.rpt"); rptname.Refresh(); rptname.SetDataSource(dtCreditNote); rptname.SetParameterValue("txtCompName", dtComp.Rows[0]["CM_NAME"].ToString()); rptname.SetParameterValue("txtCompAdd", dtComp.Rows[0]["CM_ADDRESS1"].ToString()); rptname.SetParameterValue("txtCompGSTIN", dtComp.Rows[0]["CM_GST_NO"].ToString()); CrystalReportViewer1.ReportSource = rptname; } } } catch (Exception Ex) { CommonClasses.SendError("CreditNote", "GenerateReport", Ex.Message); } }
public static Boolean EditSupplierList(string Id, string itemname, string vendor, string address, string contactperson, string contactnumber, string email, string terms, string tin) { Boolean _isUpdate = false; try { _isUpdate = DatabaseAccessLayer.UpdateInfomation("UPDATE PS.MasterList SET ItemName = @item, VendorName = @vendor, Address = @address, ContactPerson = @contactperson," + " ContactNumber = @contactnumber, Email = @email, Terms = @terms, TIN = @tin WHERE ID = @Id", new SqlParameter("@Id", Id), new SqlParameter("@item", itemname), new SqlParameter("@vendor", vendor), new SqlParameter("@address", address), new SqlParameter("@contactperson", contactperson), new SqlParameter("@contactnumber", contactnumber), new SqlParameter("@email", email), new SqlParameter("@terms", terms), new SqlParameter("@tin", tin)); return(_isUpdate); } catch (Exception ex) { throw new Exception("Error in updating master list" + Environment.NewLine + ex.Message.ToString(), ex); } }
private void GenerateReport(string Title, string TariffNo, string Comp1) { DL_DBAccess = new DatabaseAccessLayer(); string Query = ""; if (Comp1 == "All") { Query = "SELECT E_CODE,E_TARIFF_NO,E_COMMODITY,E_BASIC,E_SPECIAL,E_EDU_CESS,E_H_EDU,E_EX_TYPE FROM EXCISE_TARIFF_MASTER WHERE ES_DELETE=0"; } else { Query = "SELECT E_CODE,E_TARIFF_NO,E_COMMODITY,E_BASIC,E_SPECIAL,E_EDU_CESS,E_H_EDU,E_EX_TYPE FROM EXCISE_TARIFF_MASTER WHERE ES_DELETE=0 AND E_CODE='" + TariffNo + "'"; } DataSet ds = new DataSet(); DataTable dt = new DataTable(); dt = CommonClasses.Execute(Query); if (dt.Rows.Count > 0) { ReportDocument rptname = null; rptname = new ReportDocument(); rptname.Load(Server.MapPath("~/Reports/prtExiseTariffMaster.rpt")); rptname.FileName = Server.MapPath("~/Reports/prtExiseTariffMaster.rpt"); rptname.Refresh(); rptname.SetDataSource(dt); rptname.SetParameterValue("txtCompName", Session["CompanyName"].ToString()); CrystalReportViewer1.ReportSource = rptname; } }
public bool Update() { bool result = false; try { //if (ChkExstUpdateName()) //{ DL_DBAccess = new DatabaseAccessLayer(); if (CommonClasses.Execute("SELECT I_UOM_CODE,I_UOM_NAME,I_UOM_DESC FROM ITEM_UNIT_MASTER where I_UOM_NAME='" + I_UOM_NAME + "' and I_UOM_CODE!=" + I_UOM_CODE + "").Rows.Count == 0) { SqlParameter[] Params = { new SqlParameter("@I_UOM_CODE", I_UOM_CODE), new SqlParameter("@I_UOM_CM_COMP_ID", I_UOM_CM_COMP_ID), new SqlParameter("@I_UOM_NAME", I_UOM_NAME), new SqlParameter("@I_UOM_DESC", I_UOM_DESC), new SqlParameter("@ES_DELETE", false), new SqlParameter("@MODIFY", false) }; result = DL_DBAccess.Insertion_Updation_Delete("SP_ITEM_UNIT_MASTER_Update", Params); } else { Msg = "Unit Name Already Exist"; } } catch (Exception Ex) { CommonClasses.SendError("Unit Master Class", "Update", Ex.Message); } return(result); }
public static DataTable ApprovalDetailsEmail(string tab, string rprID) { DataTable Dt = new DataTable(); try { if (tab == "Request") { Dt = DatabaseAccessLayer.RetrieveDataTableInfo("SELECT * FROM PS.PurchaseRequest WHERE RPRID = @Id", new SqlParameter("@Id", rprID)); } else { Dt = DatabaseAccessLayer.RetrieveDataTableInfo("SELECT * FROM PS.PurchaseProposal WHERE RID = @Id", new SqlParameter("@Id", rprID)); } return(Dt); } catch (Exception ex) { throw new Exception("Error in getting Approval Details" + Environment.NewLine + ex.Message.ToString(), ex); } finally { Dt.Dispose(); } }
public static DataTable DashboardGraphRequest(string year) { try { return(DatabaseAccessLayer.RetrieveDataTableInfo("SELECT COUNT(DISTINCT RPRNum) [Jan]," + " (SELECT COUNT(DISTINCT RPRNum) FROM PS.PurchaseRequest WHERE MONTH(RDate) = 02" + " AND YEAR(RDate) = @year) [Feb]," + " (SELECT COUNT(DISTINCT RPRNum) FROM PS.PurchaseRequest WHERE MONTH(RDate) = 03" + " AND YEAR(RDate) = @year) [Mar]," + " (SELECT COUNT(DISTINCT RPRNum) FROM PS.PurchaseRequest WHERE MONTH(RDate) = 04" + " AND YEAR(RDate) = @year) [Apr]," + " (SELECT COUNT(DISTINCT RPRNum) FROM PS.PurchaseRequest WHERE MONTH(RDate) = 05" + " AND YEAR(RDate) = @year) [May]," + " (SELECT COUNT(DISTINCT RPRNum) FROM PS.PurchaseRequest WHERE MONTH(RDate) = 06" + " AND YEAR(RDate) = @year) [June]," + " (SELECT COUNT(DISTINCT RPRNum) FROM PS.PurchaseRequest WHERE MONTH(RDate) = 07" + " AND YEAR(RDate) = @year) [July]," + " (SELECT COUNT(DISTINCT RPRNum) FROM PS.PurchaseRequest WHERE MONTH(RDate) = 08" + " AND YEAR(RDate) = @year) [Aug]," + " (SELECT COUNT(DISTINCT RPRNum) FROM PS.PurchaseRequest WHERE MONTH(RDate) = 09" + " AND YEAR(RDate) = @year) [Sep]," + " (SELECT COUNT(DISTINCT RPRNum) FROM PS.PurchaseRequest WHERE MONTH(RDate) = 10" + " AND YEAR(RDate) = @year) [Oct]," + " (SELECT COUNT(DISTINCT RPRNum) FROM PS.PurchaseRequest WHERE MONTH(RDate) = 11" + " AND YEAR(RDate) = @year) [Nov]," + " (SELECT COUNT(DISTINCT RPRNum) FROM PS.PurchaseRequest WHERE MONTH(RDate) = 12" + " AND YEAR(RDate) = @year) [Dec]" + " FROM PS.PurchaseRequest WHERE MONTH(RDate) = 01 AND YEAR(RDate) = @year", new SqlParameter("@year", year))); } catch (Exception ex) { throw new Exception("Error in getting graph request" + Environment.NewLine + ex.Message.ToString(), ex); } }
public static DataTable FillCombo(string Tablename, string DispMember, string ValueMember, string Condition, object ddList) { DataTable dt = new DataTable(); DatabaseAccessLayer DL_DBAccess = new DatabaseAccessLayer(); try { SqlParameter[] par = new SqlParameter[4]; par[0] = new SqlParameter("@TbName", Tablename); par[1] = new SqlParameter("@fname", DispMember); par[2] = new SqlParameter("@code", ValueMember); par[3] = new SqlParameter("@cond", Condition); dt = DL_DBAccess.SelectData("SP_CM_FillCombo", par); ListControl ddl = null; ddl = ddList as DropDownList; ddl.DataSource = dt; ddl.DataTextField = DispMember; ddl.DataValueField = ValueMember; ddl.DataBind(); } catch (Exception Ex) { } return(dt); }
public static DataTable BindProposalStatus(string prnum) { DataTable Dt = new DataTable(); try { if (prnum == string.Empty) { Dt = DatabaseAccessLayer.RetrieveDataTableInfo("SELECT DISTINCT PR.RPRID, PP.ID [pID], PR.RPRNum, PP.PProp, PP.PVendorName, PP.PItem, PP.PQty, PP.PCost, PP.PStatus" + " FROM PS.PurchaseRequest PR INNER JOIN PS.PurchaseProposal PP ON RPRID = PP.RID ORDER BY PR.RPRID DESC"); } else { Dt = DatabaseAccessLayer.RetrieveDataTableInfo("SELECT DISTINCT PR.RPRID, PP.ID [pID], PR.RPRNum, PP.PProp, PP.PVendorName, PP.PItem, PP.PQty, PP.PCost, PP.PStatus" + " FROM PS.PurchaseRequest PR INNER JOIN PS.PurchaseProposal PP ON RPRID = PP.RID WHERE RPRNum = @prnum ORDER BY PR.RPRID DESC", new SqlParameter("@prnum", prnum)); } return(Dt); } catch (Exception ex) { throw new Exception("Error in binding Proposal Status" + Environment.NewLine + ex.Message.ToString(), ex); } finally { Dt.Dispose(); } }
public static bool SaveAccountLedger(string ACCL_CM_CODE, string ACCL_BM_CODE, string ACCL_DBM_CODE, string ACCL_LM_CODE, string ACCL_DOC_NO, string ACCL_DOC_NUMBER, string ACCL_DOC_TYPE, string ACCL_DOC_DATE, string ACCL_AMT, string ACCL_IS_ADJ, string ACCL_ADJ_AMT) { bool res = false; DatabaseAccessLayer DL_DBAccess = new DatabaseAccessLayer(); try { SqlParameter[] par = new SqlParameter[11]; par[0] = new SqlParameter("@ACCL_CM_CODE", ACCL_CM_CODE); par[1] = new SqlParameter("@ACCL_BM_CODE", ACCL_BM_CODE); par[2] = new SqlParameter("@ACCL_DBM_CODE", ACCL_DBM_CODE); par[3] = new SqlParameter("@ACCL_LM_CODE", ACCL_LM_CODE); par[4] = new SqlParameter("@ACCL_DOC_NO", ACCL_DOC_NO); par[5] = new SqlParameter("@ACCL_DOC_NUMBER", ACCL_DOC_NUMBER); par[6] = new SqlParameter("@ACCL_DOC_TYPE", ACCL_DOC_TYPE); par[7] = new SqlParameter("@ACCL_DOC_DATE", ACCL_DOC_DATE); par[8] = new SqlParameter("@ACCL_AMT", ACCL_AMT); par[9] = new SqlParameter("@ACCL_IS_ADJ", ACCL_IS_ADJ); par[10] = new SqlParameter("@ACCL_ADJ_AMT", ACCL_ADJ_AMT); res = DL_DBAccess.Insertion_Updation_Delete("SP_Insert_AccountLedger", par); } catch (Exception Ex) { } return(res); }
public static bool SaveAccountVoucherLedger(string ACC_VCL_NO, string ACC_CM_CODE, string ACC_UM_CODE, string ACC_VCL_DATE, string ACC_VCL_EX_NO, string ACC_VCL_LM_CODE, string ACC_VCL_P_TYPE, string ACC_VCL_DBM_CODE, string ACC_VCL_AMT, string ACC_VCL_ADJ_AMT, string ACC_VCL_AMT_TEMP, string ACC_VCL_DOC_ID, string ACC_VCL_DOC_NO, string ACC_VCL_DOC_DATE, string ACC_VCL_DOC_TYPE, string ACC_VCL_CON_FLAG) { bool res = false; DatabaseAccessLayer DL_DBAccess = new DatabaseAccessLayer(); try { SqlParameter[] par = new SqlParameter[16]; par[0] = new SqlParameter("@ACC_VCL_NO", ACC_VCL_NO); par[1] = new SqlParameter("@ACC_CM_CODE", ACC_CM_CODE); par[2] = new SqlParameter("@ACC_UM_CODE", ACC_UM_CODE); par[3] = new SqlParameter("@ACC_VCL_DATE", ACC_VCL_DATE); par[4] = new SqlParameter("@ACC_VCL_EX_NO", ACC_VCL_EX_NO); par[5] = new SqlParameter("@ACC_VCL_LM_CODE", ACC_VCL_LM_CODE); par[6] = new SqlParameter("@ACC_VCL_P_TYPE", ACC_VCL_P_TYPE); par[7] = new SqlParameter("@ACC_VCL_DBM_CODE", ACC_VCL_DBM_CODE); par[8] = new SqlParameter("@ACC_VCL_AMT", ACC_VCL_AMT); par[9] = new SqlParameter("@ACC_VCL_ADJ_AMT", ACC_VCL_ADJ_AMT); par[10] = new SqlParameter("@ACC_VCL_AMT_TEMP", ACC_VCL_AMT_TEMP); par[11] = new SqlParameter("@ACC_VCL_DOC_ID", ACC_VCL_DOC_ID); par[12] = new SqlParameter("@ACC_VCL_DOC_NO", ACC_VCL_DOC_NO); par[13] = new SqlParameter("@ACC_VCL_DOC_DATE", ACC_VCL_DOC_DATE); par[14] = new SqlParameter("@ACC_VCL_DOC_TYPE", ACC_VCL_DOC_TYPE); par[15] = new SqlParameter("@ACC_VCL_CON_FLAG", ACC_VCL_CON_FLAG); res = DL_DBAccess.Insertion_Updation_Delete("SP_Insert_AccountVoucherLedger", par); } catch (Exception Ex) { } return(res); }
private void GenerateReport(string code) { DL_DBAccess = new DatabaseAccessLayer(); try { DataTable dtfinal = CommonClasses.Execute("select SHM_CODE,SHM_FORMULA_CODE,SHM_FORMULA_NAME,SHM_REMARK,SHM_PROCESS_CODE ,PROCESS_NAME,SHM_PROCESS_STEPS,I_NAME,I_CODENO,SHM_QTY_KG,SHM_QTY_LTR ,SHM_RATE,isnull(SHM_VOL_SOLID,0) as SHM_VOL_SOLID,avg(isnull(I_DENSITY,0)) as I_DENSITY,avg(isnull(I_PIGMENT,0)) as I_PIGMENT,avg(isnull(I_SOLIDS,0)) as I_SOLIDS,avg(isnull(I_VOLATILE,0)) as I_VOLATILE From SHADE_DETAIL,SHADE_MASTER,ITEM_MASTER,ITEM_UNIT_MASTER,PROCESS_MASTER where SHM_ITEM_CODE=I_CODE AND SHM_CODE=SHD_SHM_CODE AND ITEM_MASTER.I_UOM_CODE =ITEM_UNIT_MASTER.I_UOM_CODE AND SHM_PROCESS_CODE=PROCESS_CODE AND SHADE_MASTER .ES_DELETE=0 and SHM_CODE='" + code + "' group by SHM_CODE,SHM_FORMULA_CODE,SHM_FORMULA_NAME,SHM_REMARK,SHM_PROCESS_CODE ,PROCESS_NAME,SHM_PROCESS_STEPS,I_NAME,I_CODENO,SHM_QTY_KG,SHM_QTY_LTR ,SHM_RATE,SHM_VOL_SOLID order by SHM_PROCESS_STEPS,I_NAME"); if (dtfinal.Rows.Count > 0) { ReportDocument rptname = null; rptname = new ReportDocument(); rptname.Load(Server.MapPath("~/Reports/rptShadePrint.rpt")); rptname.FileName = Server.MapPath("~/Reports/rptShadePrint.rpt"); rptname.Refresh(); rptname.SetDataSource(dtfinal); //rptname.SetParameterValue("txtCompIso", Session["CompanyIso"].ToString()); //string IsoNo = DL_DBAccess.GetColumn("select ISO_NO from ISONO_MASTER,ENQUIRY_MASTER where INQ_CODE='" + code + "' and ISO_SCREEN_NO=94 and ISO_WEF_DATE<=INQ_REQ_DATE order by ISO_WEF_DATE DESC"); //if (IsoNo == "") //{ // rptname.SetParameterValue("txtIsoNo", "1"); //} //else //{ // rptname.SetParameterValue("txtIsoNo", IsoNo.ToString()); //} CrystalReportViewer1.ReportSource = rptname; } } catch (Exception Ex) { CommonClasses.SendError("Shade Creation Print", "GenerateReport", Ex.Message); } }
/// <summary> /// Retrieve list of nodes from database /// </summary> /// <returns>List of nodes</returns> public List <Node> GetNodes() { var databaseAccessLayer = new DatabaseAccessLayer(); var nodes = databaseAccessLayer.GetNodes(); return(nodes); }
public bool Save() { bool result = false; DL_DBAccess = new DatabaseAccessLayer(); try { if (CheckExistSaveName()) { SqlParameter[] par = new SqlParameter[3]; par[0] = new SqlParameter("@DM_CM_CODE", DM_CM_CODE); par[1] = new SqlParameter("@DM_NAME", DM_NAME); par[2] = new SqlParameter("@DM_BM_CODE", DM_BM_CODE); result = DL_DBAccess.Insertion_Updation_Delete("SP_HR_InsertDept", par); } else { Msg = "Department Name Already Exist"; } } catch (Exception Ex) { CommonClasses.SendError("Department Master Class", "Save", Ex.Message); } return(result); }
public bool ChkExstUpdateName() { bool res = false; DL_DBAccess = new DatabaseAccessLayer(); DataTable dt = new DataTable(); try { SqlParameter[] par = new SqlParameter[4]; par[0] = new SqlParameter("@DM_CODE", DM_CODE); par[1] = new SqlParameter("@DM_NAME", DM_NAME); par[2] = new SqlParameter("@DM_BM_CODE", DM_BM_CODE); par[3] = new SqlParameter("@DM_CM_CODE", DM_CM_CODE); dt = DL_DBAccess.SelectData("SP_HR_CheckUpdateDept", par); if (dt.Rows.Count > 0) { res = false; } else { res = true; } } catch (Exception Ex) { CommonClasses.SendError("Department Master Class", "ChkExstUpdateName", Ex.Message); } return(res); }
/// <summary> /// Upload new data into database /// </summary> /// <param name="nodesList">List of nodes to upload</param> /// <returns> return count of nodes uploaded </returns> public int UploadNewData(List <Node> nodesList) { var databaseAccessLayer = new DatabaseAccessLayer(); int nodesListCount = databaseAccessLayer.Insert(nodesList); return(nodesListCount); }
public static DataTable GetRequestNumber(string stats, string prnum) { DataTable Dt = new DataTable(); try { if (stats == string.Empty) { Dt = DatabaseAccessLayer.RetrieveDataTableInfo("SELECT DISTINCT RPRID, RPRNum FROM PS.PurchaseRequest WHERE RStatus <> 'Approved'" + " AND RStatus <> 'Cancelled' ORDER BY RPRID DESC"); } else { Dt = DatabaseAccessLayer.RetrieveDataTableInfo("SELECT * FROM PS.PurchaseRequest WHERE (RStatus <> 'Approved' OR RStatus <> 'Cancelled') AND RPRNum = @prnum", new SqlParameter("@prnum", prnum)); } return(Dt); } catch (Exception ex) { throw new Exception("Error in getting Request information" + Environment.NewLine + ex.Message.ToString(), ex); } finally { Dt.Dispose(); } }
private void GenerateReport(string Title, string User_Name, string Comp1) { DL_DBAccess = new DatabaseAccessLayer(); string Query = ""; if (Comp1 == "All") { // Query = "SELECT ITEM_CATEGORY_MASTER.I_CAT_NAME,I_NAME,I_CODENO,ITEM_UNIT_MASTER.I_UOM_NAME,I_MATERIAL,I_SPECIFICATION,I_OP_BAL,I_STORE_LOC,I_INV_RATE,I_LENGTH,I_HEIGHT,I_WIDTH,I_ROWS,I_STORIED,I_MAX_LEVEL,I_MIN_LEVEL,I_REORDER_LEVEL FROM ITEM_UNIT_MASTER INNER JOIN ITEM_MASTER ON ITEM_UNIT_MASTER.I_UOM_CODE = ITEM_MASTER.I_UOM_CODE LEFT OUTER JOIN ITEM_CATEGORY_MASTER ON ITEM_MASTER.I_CAT_CODE = ITEM_CATEGORY_MASTER.I_CAT_CODE WHERE ITEM_MASTER.ES_DELETE=0 and ITEM_MASTER.I_CAT_CODE='-2147483646'"; Query = "SELECT UM_CODE,UM_NAME,UM_EMAIL,UM_LEVEL,UM_USERNAME FROM USER_MASTER WHERE ES_DELETE=0"; } else { // Query = "SELECT ITEM_CATEGORY_MASTER.I_CAT_NAME,I_NAME,I_CODENO,ITEM_UNIT_MASTER.I_UOM_NAME,I_MATERIAL,I_SPECIFICATION,I_OP_BAL,I_STORE_LOC,I_INV_RATE,I_LENGTH,I_HEIGHT,I_WIDTH,I_ROWS,I_STORIED,I_MAX_LEVEL,I_MIN_LEVEL,I_REORDER_LEVEL FROM ITEM_UNIT_MASTER INNER JOIN ITEM_MASTER ON ITEM_UNIT_MASTER.I_UOM_CODE = ITEM_MASTER.I_UOM_CODE LEFT OUTER JOIN ITEM_CATEGORY_MASTER ON ITEM_MASTER.I_CAT_CODE = ITEM_CATEGORY_MASTER.I_CAT_CODE WHERE ITEM_MASTER.ES_DELETE=0 and ITEM_MASTER.I_CAT_CODE='-2147483646' and ITEM_MASTER.I_CODE=" + User_Name + ""; Query = "SELECT UM_CODE,UM_NAME,UM_EMAIL,UM_LEVEL,UM_USERNAME FROM USER_MASTER WHERE ES_DELETE=0 AND UM_CODE='" + User_Name + "'"; } DataSet ds = new DataSet(); DataTable dt = new DataTable(); dt = CommonClasses.Execute(Query); if (dt.Rows.Count > 0) { ReportDocument rptname = null; rptname = new ReportDocument(); rptname.Load(Server.MapPath("~/Reports/rptUserMaster.rpt")); rptname.FileName = Server.MapPath("~/Reports/rptUserMaster.rpt"); rptname.Refresh(); rptname.SetDataSource(dt); rptname.SetParameterValue("txtCompName", Session["CompanyName"].ToString()); CrystalReportViewer1.ReportSource = rptname; } }
public static int PONumber(string month) { DataTable Dt = new DataTable(); int _PONumber = 0; try { Dt = DatabaseAccessLayer.RetrieveDataTableInfo("SELECT * FROM PS.AutoPO WHERE MONTH(PODate) = @month ORDER BY RefNum DESC", new SqlParameter("@month", month)); if (Dt.Rows.Count > 0) { _PONumber = Convert.ToInt32(Dt.Rows[0]["RefNum"]) + 1; } else { _PONumber = 1; } return(_PONumber); } catch (Exception ex) { throw new Exception("Error in getting PO number" + Environment.NewLine + ex.Message.ToString(), ex); } finally { Dt.Dispose(); } }
public bool Save() { bool result = false; DL_DBAccess = new DatabaseAccessLayer(); try { if (CheckExistSaveName()) { SqlParameter[] Params = { new SqlParameter("@I_CAT_CM_COMP_ID", I_CAT_CM_COMP_ID), new SqlParameter("@I_CAT_NAME", I_CAT_NAME), new SqlParameter("@ES_DELETE", ES_DELETE), new SqlParameter("@MODIFY", MODIFY), new SqlParameter("@I_CAT_SHORTCLOSE", I_CAT_SHORTCLOSE) }; result = DL_DBAccess.Insertion_Updation_Delete("SP_ITEM_CATEGORY_MASTER_Insert", Params); } else { Msg = "Item Category Already Exist"; } } catch (Exception Ex) { CommonClasses.SendError("Item Category Class", "Save", Ex.Message); } return(result); }
public static string GetAccountPOEdit(string Id) { DataTable Dt = new DataTable(); string accnt = string.Empty; try { Dt = DatabaseAccessLayer.RetrieveDataTableInfo("SELECT DISTINCT RAccount FROM PS.PurchaseRequest WHERE RPRID = @Id", new SqlParameter("@Id", Id)); if (Dt.Rows.Count > 0) { accnt = Dt.Rows[0]["RAccount"].ToString(); } return(accnt); } catch (Exception ex) { throw new Exception("Error in getting account PO" + Environment.NewLine + ex.Message.ToString(), ex); } finally { Dt.Dispose(); } }
public bool Save() { bool result = false; DL_DBAccess = new DatabaseAccessLayer(); try { if (CheckExistSaveName()) { //SqlParameter[] Params = //{ // new SqlParameter("@A_CM_COMP_ID",A_CM_COMP_ID), // new SqlParameter("@A_NO",A_NO), // new SqlParameter("@A_DESC",A_DESC) //}; //result = DL_DBAccess.Insertion_Updation_Delete("SP_ADMIN_MASTER_INSERT", Params); result = CommonClasses.Execute1("INSERT INTO AREA_MASTER (A_CM_COMP_ID,A_NO,A_DESC) values (" + A_CM_COMP_ID + ",'" + A_NO + "','" + A_DESC + "')"); } else { Msg = "Area Code Already Exist"; } } catch (Exception Ex) { CommonClasses.SendError("Admin Class", "Save", Ex.Message); } return(result); }
private void GenerateReport(string code) { DL_DBAccess = new DatabaseAccessLayer(); DataTable dtfinal = CommonClasses.Execute("select WO_AMC_CODE,WO_AMC_NO,CONVERT(varchar,WO_PO_DATE,103) as WO_PO_DATE,WO_P_CODE,P_NAME,P_ADD1,WO_TYPE,WO_REF,WO_CONTACT_PER,WO_PHONE_NO,WO_DELIVERY_SCHD,WO_PAY_TERM,WO_DELIVER_TO,WO_FRIEGHT_TERM,WO_GARUNTEE_TERM,WO_TRANSPORTOR,WO_NOTE,cast(isnull(WO_SER_TAX_PER,0) as numeric(20,2)) as WO_SER_TAX_PER,WO_TAX_APPLICABLE,cast(WO_GRAND_TOT as numeric(20,2)) as WO_GRAND_TOT,WOD_PROD_NAME,WOD_PROD_DESC,WOD_PREV_MAINTAIN_DEC,cast(WOD_QTY as numeric(20,3)) as WOD_QTY,cast(WOD_RATE as numeric(20,2)) as WOD_RATE,cast(WOD_TOT_AMT as numeric(20,2)) as WOD_TOT_AMT,CM_EXCISE_RANGE,CM_EXCISE_DIVISION,CM_COMMISONERATE,CM_PAN_NO,CM_CST_NO,CM_VAT_TIN_NO,CM_VAT_WEF,CM_CST_WEF,CM_BANK_NAME,CM_BANK_ACC_NO,CM_OWNER,UM_NAME,I_UOM_NAME from WORK_AMC_ORDER_MASTER,WORK_AMC_ORDER_DETAIL,PARTY_MASTER,USER_MASTER,COMPANY_MASTER,ITEM_UNIT_MASTER where WOD_WO_AMC_CODE=WO_AMC_CODE and I_UOM_CODE=WOD_UOM_CODE and WO_P_CODE=P_CODE and WO_CM_CODE=CM_CODE and WO_UM_CODE=UM_CODE and WORK_AMC_ORDER_MASTER.ES_DELETE=0 and WO_AMC_CODE='" + Code + "' and WO_TYPE='AMC' "); if (dtfinal.Rows.Count > 0) { } try { ReportDocument rptname = null; rptname = new ReportDocument(); rptname.Load(Server.MapPath("~/Reports/rptAMCPrint.rpt")); rptname.FileName = Server.MapPath("~/Reports/rptAMCPrint.rpt"); rptname.Refresh(); rptname.SetDataSource(dtfinal); string IsoNo = DL_DBAccess.GetColumn("select ISO_NO from ISONO_MASTER,WORK_AMC_ORDER_MASTER where WO_AMC_CODE='" + code + "' and ISO_SCREEN_NO=85 and ISO_WEF_DATE<=WO_PO_DATE order by ISO_WEF_DATE DESC"); rptname.SetParameterValue("txtIsoNo", IsoNo); if (IsoNo == "") { rptname.SetParameterValue("txtIsoNo", "1"); } else { rptname.SetParameterValue("txtIsoNo", IsoNo.ToString()); } CrystalReportViewer1.ReportSource = rptname; } catch (Exception Ex) { CommonClasses.SendError("AMC Print", "GenerateReport", Ex.Message); } }
private void filteringFormSubmittedAction() { DataBaseTable dbTable = new DataBaseTable(); string tableName = Request.Form["tableName"]; dbTable.setTableName(tableName); string[] names = dbTable.getColumnNames(); string[] dataTypes = new string[names.Length]; List <string[]> conditions = new List <string[]>(); //list of array of where conditions for filtering for (int i = 0; i < names.Length; i++) //get dataType for each field { dataTypes[i] = Request.Form[names[i] + "_dataType"]; } //check if the user had manually changed the datatypes of the menu bool flag = DatabaseAccessLayer.areDataTypesMatched(tableName, dataTypes); if (flag)//ie datatypes are same in database and form submitted { for (int i = 0; i < names.Length; i++) { conditions.Add(createCondition3(names[i], dataTypes[i])); } Literal1.Text = DatabaseAccessLayer.getFilteringForm(tableName); //display filtering form Literal2.Text = DatabaseAccessLayer.getFilteredTableData(conditions, tableName); //data } else { DatabaseAccessLayer.updateTableDataTypes(tableName, names, dataTypes); Literal1.Text = DatabaseAccessLayer.getFilteringForm(tableName); Literal2.Text = DatabaseAccessLayer.getTableDataWhole(tableName); } }
public bool Save() { bool result = false; DL_DBAccess = new DatabaseAccessLayer(); try { if (CheckExistSaveName()) { SqlParameter[] Params = { new SqlParameter("@I_UOM_CM_COMP_ID", I_UOM_CM_COMP_ID), new SqlParameter("@I_UOM_NAME", I_UOM_NAME), new SqlParameter("@I_UOM_DESC", I_UOM_DESC), new SqlParameter("@ES_DELETE", false), new SqlParameter("@MODIFY", false) }; result = DL_DBAccess.Insertion_Updation_Delete("SP_ITEM_UNIT_MASTER_Insert", Params); } else { Msg = "Unit Name Already Exist"; } } catch (Exception Ex) { CommonClasses.SendError("Unit Master Class", "Save", Ex.Message); } return(result); }
public static Boolean GetUserLogin(string username, string password, ref string rights) { DataTable DtLog = new DataTable(); try { DtLog = DatabaseAccessLayer.RetrieveDataTableInfo("SELECT * FROM PS.LoginCredentials WHERE Username = @username AND Password = @password AND Active = 'True'", new SqlParameter("@username", username), new SqlParameter("@password", password)); if (DtLog.Rows.Count > 0) { rights = DtLog.Rows[0]["Rights"].ToString(); return(true); } else { return(false); } } catch (Exception ex) { throw new Exception("Error in getting login credentials" + Environment.NewLine + ex.Message.ToString(), ex); } finally { DtLog.Dispose(); } }
public bool Delete() { bool result = false; try { DL_DBAccess = new DatabaseAccessLayer(); SqlParameter[] par = new SqlParameter[3]; par[0] = new SqlParameter("@ME_EEM_CODE", ME_EEM_CODE); par[1] = new SqlParameter("@ME_MONTH", ME_MONTH); par[2] = new SqlParameter("@ME_YEAR", ME_YEAR); DataTable dt = new DataTable(); dt = GetInfo(); for (int i = 0; i < dt.Rows.Count; i++) { CommonClasses.WriteLog("HR Monthly Earning", "Delete", "HR Monthly Earning", ME_MONTH + "/" + ME_YEAR, Convert.ToInt32(dt.Rows[i]["ME_CODE"].ToString()), ME_CM_CODE, userName, userCode); } result = DL_DBAccess.Insertion_Updation_Delete("SP_HR_DeleteMonthlyEarning", par); return(result); } catch (Exception ex) { return(false); } finally { } }
public void GetNodesBidirectional_ThirdNodeHaveOneDirection_FirstAndSecondHaveLinkToThird() { var databaseAccessLayer = new DatabaseAccessLayer(); var firstNode = new Node() { id = 1, adjacentNodes = new byte[1] { 2 } }; var secondNode = new Node() { id = 2, adjacentNodes = new byte[1] { 1 } }; var thirdNode = new Node() { id = 3, adjacentNodes = new byte[2] { 1, 2 } }; var nodesList = new List <Node> { firstNode, secondNode, thirdNode }; databaseAccessLayer.GetNodesBidirectional(nodesList); Assert.AreEqual(firstNode.adjacentNodes.Last(), thirdNode.id); Assert.AreEqual(secondNode.adjacentNodes.Last(), thirdNode.id); }
protected void ddlWorkOrderNo_SelectedIndexChanged(object sender, EventArgs e) { try { if (ddlWorkOrderNo.SelectedIndex != 0) { if (ddlBatchType.SelectedValue == "1") { DataTable dtItem = CommonClasses.FillCombo("ITEM_MASTER,WORK_ORDER_MASTER,WORK_ORDER_DETAIL", "I_CODENO", "I_CODE", "ITEM_MASTER.ES_DELETE=0 and WORK_ORDER_MASTER.ES_DELETE=0 and WO_CODE=WOD_WO_CODE and WOD_I_CODE=I_CODE and WO_CODE='" + ddlWorkOrderNo.SelectedValue + "' AND I_CM_COMP_ID='" + Convert.ToInt32(Session["CompanyId"]) + "'", ddlItemCode); ddlItemCode.Items.Insert(0, new ListItem("Please Select Item Code", "0")); if (dtItem.Rows.Count > 0) { ddlItemCode.SelectedIndex = 1; DatabaseAccessLayer dbaccess = new DatabaseAccessLayer(); txtWorkOrdQty.Text = dbaccess.GetColumn("select WOD_WORK_ORDER_QTY from WORK_ORDER_MASTER,WORK_ORDER_DETAIL where WO_CODE='" + ddlWorkOrderNo.SelectedValue + "' and WOD_WO_CODE=WO_CODE and WOD_I_CODE='" + ddlItemCode.SelectedValue + "' "); } } else { LoadICode(); } } else { LoadICode(); } } catch (Exception Ex) { CommonClasses.SendError(" Batch Ticket", "ddlWorkOrderNo_SelectedIndexChanged", Ex.Message); } }
public bool Save() { bool result = false; DL_DBAccess = new DatabaseAccessLayer(); try { if (CheckExistSaveName()) { SqlParameter[] Params = { new SqlParameter("@ISO_COMP_ID", ISO_COMP_ID), new SqlParameter("@ISO_SCREEN_NO", ISO_SCREEN_NO), new SqlParameter("@ISO_WEF_DATE", ISO_WEF_DATE) }; result = DL_DBAccess.Insertion_Updation_Delete("SP_STATE_MASTER_INSERT", Params); } else { Msg = "State Name Already Exist"; } } catch (Exception Ex) { CommonClasses.SendError("State Master Class", "Save", Ex.Message); } return(result); }
private void GenerateReport(string code) { DL_DBAccess = new DatabaseAccessLayer(); string Query = ""; try { string BatchType = DL_DBAccess.GetColumn("select isnull(BT_TYPE,0) as BT_TYPE from BATCH_MASTER,FILL_OFF_SHEET where FOS_BT_CODE=BT_CODE and BATCH_MASTER.ES_DELETE=0 and FOS_CODE='" + code + "'"); if (BatchType == "1") { Query = "select BT_NO,SHM_FORMULA_CODE as SHM_FORMULA_NAME,P_NAME,FOS_FINAL_YIELD,FOS_NOTES,FOS_FILTER_USED,FOSD_TYPE,FOSD_QTY,FOSD_WGT,I_UOM_NAME as UOM_NAME,FOS_DATE,FOS_FILL_DATE,sum(isnull(BTD_QTY,0)) as QTY_IN_LTR,sum(isnull(BTD_WGT,0)) as WEIGHT_IN_KG,(CASE FOSD_TYPE WHEN 1 THEN 'Package' WHEN 2 THEN 'Extra' WHEN 3 THEN 'Sample' END) AS Type_Name from BATCH_MASTER,BATCH_DETAIL,SHADE_MASTER,WORK_ORDER_MASTER,PARTY_MASTER,FILL_OFF_SHEET,FILL_OFF_SHEET_DETAIL,ITEM_UNIT_MASTER where BT_CODE=BTD_BT_CODE and BT_SHM_CODE=SHM_CODE and FOS_BT_CODE=BT_CODE and FOS_CODE=FOSD_FOS_CODE and SHADE_MASTER.ES_DELETE=0 and BT_WO_CODE=WO_CODE and WORK_ORDER_MASTER.ES_DELETE=0 and WO_P_CODE=P_CODE and PARTY_MASTER.ES_DELETE=0 and FOSD_UOM_CODE=I_UOM_CODE and ITEM_UNIT_MASTER.ES_DELETE=0 and FOS_CODE='" + code + "' group by BT_NO,SHM_FORMULA_CODE,P_NAME,FOS_APPROVED_BY,FOS_SPECIAL_INSTR,FOS_FILL_BY,FOS_FINAL_YIELD,FOS_NOTES,FOS_FILTER_USED,FOSD_TYPE,FOSD_QTY,FOSD_WGT,I_UOM_NAME,FOS_DATE,FOS_FILL_DATE order by FOSD_TYPE"; } else { Query = "select BT_NO,SHM_FORMULA_CODE as SHM_FORMULA_NAME,'' as P_NAME,FOS_FINAL_YIELD,FOS_NOTES,FOS_FILTER_USED,FOSD_TYPE,FOSD_QTY,FOSD_WGT,I_UOM_NAME as UOM_NAME,FOS_DATE,FOS_FILL_DATE,sum(isnull(BTD_QTY,0)) as QTY_IN_LTR,sum(isnull(BTD_WGT,0)) as WEIGHT_IN_KG,(CASE FOSD_TYPE WHEN 1 THEN 'Package' WHEN 2 THEN 'Extra' WHEN 3 THEN 'Sample' END) AS Type_Name from BATCH_MASTER,BATCH_DETAIL,SHADE_MASTER,FILL_OFF_SHEET,FILL_OFF_SHEET_DETAIL,ITEM_UNIT_MASTER where BT_CODE=BTD_BT_CODE and BT_SHM_CODE=SHM_CODE and FOS_BT_CODE=BT_CODE and FOS_CODE=FOSD_FOS_CODE and SHADE_MASTER.ES_DELETE=0 and FOSD_UOM_CODE=I_UOM_CODE and ITEM_UNIT_MASTER.ES_DELETE=0 and FOS_CODE='" + code + "' group by BT_NO,SHM_FORMULA_CODE,FOS_APPROVED_BY,FOS_SPECIAL_INSTR,FOS_FILL_BY,FOS_FINAL_YIELD,FOS_NOTES,FOS_FILTER_USED,FOSD_TYPE,FOSD_QTY,FOSD_WGT,I_UOM_NAME,FOS_DATE,FOS_FILL_DATE order by FOSD_TYPE"; } DataTable dtfinal = CommonClasses.Execute(Query); if (dtfinal.Rows.Count > 0) { ReportDocument rptname = null; rptname = new ReportDocument(); rptname.Load(Server.MapPath("~/Reports/rptFillOffSheetPrint.rpt")); rptname.FileName = Server.MapPath("~/Reports/rptFillOffSheetPrint.rpt"); rptname.Refresh(); rptname.SetDataSource(dtfinal); CrystalReportViewer1.ReportSource = rptname; } } catch (Exception Ex) { CommonClasses.SendError("Fill Off Sheet Print", "GenerateReport", Ex.Message); } }
public void Create_Database_Schema() { this.sessionFactoryBuilder = MockRepository.GenerateMock<ISessionFactoryBuilder>(); this.sessionFactoryBuilder.Expect(b => b.CreateSessionFactory(Arg<Schema>.Is.Equal(Schema.Rebuild))).Return(this.sessionFactory); var database = new DatabaseAccessLayer(this.sessionFactoryBuilder); database.Initalize(Schema.Rebuild); this.sessionFactoryBuilder.VerifyAllExpectations(); }