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