protected void BindGrid(bool isForceRefreshData = false) { try { if (!Page.IsPostBack || isForceRefreshData) { using (SqlConnection conn = new SqlConnection(SPlanetUtil.GetConnectionString())) { //Create array of Parameters List <SqlParameter> arrParm = new List <SqlParameter> { new SqlParameter("@id", SqlDbType.Int) { Value = 0 }, new SqlParameter("@customer_id", SqlDbType.Int) { Value = 0 }, new SqlParameter("@user_id", SqlDbType.Int) { Value = ConstantClass.SESSION_USER_ID } }; conn.Open(); dsResult = SqlHelper.ExecuteDataset(conn, "sp_notification_list", arrParm.ToArray()); if (dsResult != null) { var row = (from t in dsResult.Tables[0].AsEnumerable() select t).ToList(); foreach (var data in row) { notificationDetail.Add(new NotificationDetail() { id = Convert.ToInt32(data["id"]), notice_type = Convert.IsDBNull(data["notice_type"]) ? null : Convert.ToString(data["notice_type"]), notice_date = Convert.ToDateTime(data["notice_date"]), customer_id = Convert.IsDBNull(data["customer_id"]) ? 0 : Convert.ToInt32(data["customer_id"]), subject = Convert.IsDBNull(data["subject"]) ? null : Convert.ToString(data["subject"]), enable = Convert.IsDBNull(data["enable"]) ? null : Convert.ToString(data["enable"]), description = Convert.IsDBNull(data["description"]) ? null : Convert.ToString(data["description"]), company_name_tha = Convert.IsDBNull(data["company_name_tha"]) ? null : Convert.ToString(data["company_name_tha"]), customer_code = Convert.IsDBNull(data["customer_code"]) ? null : Convert.ToString(data["customer_code"]), }); } } HttpContext.Current.Session["SESSION_NOTIFICATION_LIST"] = notificationDetail; } } gridView.DataSource = notificationDetail; gridView.DataBind(); } catch (Exception ex) { string strErrorMsg = SPlanetUtil.LogErrorCollect(ex); ScriptManager.RegisterStartupScript(this, GetType(), "myalert", "alert('" + strErrorMsg + "');", true); } }
protected void BindGrid(bool isForceRefreshData = false) { try { if (!Page.IsPostBack || isForceRefreshData) { using (SqlConnection conn = new SqlConnection(SPlanetUtil.GetConnectionString())) { //Create array of Parameters List <SqlParameter> arrParm = new List <SqlParameter> { new SqlParameter("@id", SqlDbType.Int) { Value = 0 } }; conn.Open(); dsResult = SqlHelper.ExecuteDataset(conn, "sp_security_permission_screen_list", arrParm.ToArray()); //ViewState["dsResult"] = dsResult; } } //Bind data into GridView gridView.DataSource = UserGroupDetailList; gridView.FilterExpression = FilterBag.GetExpression(false); gridView.DataBind(); } catch (Exception ex) { string strErrorMsg = SPlanetUtil.LogErrorCollect(ex); ScriptManager.RegisterStartupScript(this, GetType(), "myalert", "alert('" + strErrorMsg + "');", true); } }
public static int DeleteProductCategory(string id) { var parent_id = 0; var dataProductCate = (List <ProductCategoryDetail>)HttpContext.Current.Session["SESSION_PRODUCT_CATEGORY_LIST"]; if (dataProductCate != null) { parent_id = (from t in dataProductCate where t.id == Convert.ToInt32(id) select t.parent_id).FirstOrDefault(); } try { using (SqlConnection conn = new SqlConnection(SPlanetUtil.GetConnectionString())) { using (SqlCommand cmd = new SqlCommand("sp_product_category_delete", conn)) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@id", SqlDbType.Int).Value = Convert.ToInt32(id); cmd.Parameters.Add("@updated_by", SqlDbType.Int).Value = Convert.ToInt32(ConstantClass.SESSION_USER_ID); conn.Open(); cmd.ExecuteNonQuery(); conn.Close(); } } } catch (Exception ex) { string strErrorMsg = SPlanetUtil.LogErrorCollect(ex); } return(parent_id); }
public override void DeleteEntry(string id) { try {//Execute Delete Data Store Procedure using (SqlConnection conn = new SqlConnection(SPlanetUtil.GetConnectionString())) { //Create array of Parameters List <SqlParameter> arrParm = new List <SqlParameter> { new SqlParameter("@id", SqlDbType.Int) { Value = int.Parse(id) }, new SqlParameter("@update_by", SqlDbType.Int) { Value = Session[ConstantClass.SESSION_USER_ID] } }; conn.Open(); SqlHelper.ExecuteNonQuery(conn, "sp_notification_delete", arrParm.ToArray()); } BindGrid(true); } catch (Exception ex) { string strErrorMsg = SPlanetUtil.LogErrorCollect(ex); ScriptManager.RegisterStartupScript(this, GetType(), "myalert", "alert('" + strErrorMsg + "');", true); } }
public static string DeleteDeliveryNote(string id) { string data = "success"; try { using (SqlConnection conn = new SqlConnection(SPlanetUtil.GetConnectionString())) { using (SqlCommand cmd = new SqlCommand("sp_delivery_note_header_delete", conn)) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@id", SqlDbType.Int).Value = Convert.ToInt32(id); cmd.Parameters.Add("@updated_by", SqlDbType.Int).Value = Convert.ToInt32(ConstantClass.SESSION_USER_ID); conn.Open(); cmd.ExecuteNonQuery(); conn.Close(); } } } catch (Exception ex) { string strErrorMsg = SPlanetUtil.LogErrorCollect(ex); } return(data); }
protected void gridView_CustomCallback(object sender, ASPxGridViewCustomCallbackEventArgs e) { try { using (SqlConnection conn = new SqlConnection(SPlanetUtil.GetConnectionString())) { //Create array of Parameters List <SqlParameter> arrParm = new List <SqlParameter> { new SqlParameter("@search_name", SqlDbType.VarChar, 200) { Value = "" }, new SqlParameter("@id", SqlDbType.Int) { Value = 0 } }; conn.Open(); dsResult = SqlHelper.ExecuteDataset(conn, "sp_supplier_branch_list", arrParm.ToArray()); ViewState["dsResult"] = dsResult; } //Bind data into GridView gridView.DataSource = dsResult; gridView.FilterExpression = FilterBag.GetExpression(false); gridView.DataBind(); } catch (Exception ex) { string strErrorMsg = SPlanetUtil.LogErrorCollect(ex); ScriptManager.RegisterStartupScript(this, GetType(), "myalert", "alert('" + strErrorMsg + "');", true); } }
protected void BindGrid(bool isForceRefreshData = false) { try { if (!Page.IsPostBack || isForceRefreshData) { using (SqlConnection conn = new SqlConnection(SPlanetUtil.GetConnectionString())) { //Create array of Parameters List <SqlParameter> arrParm = new List <SqlParameter> { new SqlParameter("@remark_type", SqlDbType.Int) { Value = "" }, new SqlParameter("@remark_type_document", SqlDbType.Int) { Value = "" } }; conn.Open(); dsResult = SqlHelper.ExecuteDataset(conn, "sp_remark_list", arrParm.ToArray()); if (dsResult != null) { var row = (from t in dsResult.Tables[0].AsEnumerable() select t).ToList(); foreach (var data in row) { remarkDetail.Add(new RemarkDetail() { remark_type_document = Convert.IsDBNull(data["remark_type_document"]) ? null : Convert.ToString(data["remark_typedocument"]), remark_type = Convert.IsDBNull(data["remark_type"]) ? null : Convert.ToString(data["remarktype"]), }); } } HttpContext.Current.Session["SESSION_NOTIFICATION_LIST"] = remarkDetail; } } gridView.DataSource = dsResult; gridView.DataBind(); // Check page from session if (Session["ROW"] != null) { int row = Convert.ToInt32(Session["ROW"]); gridView.FocusedRowIndex = row; } } catch (Exception ex) { string strErrorMsg = SPlanetUtil.LogErrorCollect(ex); ScriptManager.RegisterStartupScript(this, GetType(), "myalert", "alert('" + strErrorMsg + "');", true); } }
protected void BindGrid(bool isForceRefreshData = false) { try { if (!Page.IsPostBack || isForceRefreshData) { using (SqlConnection conn = new SqlConnection(SPlanetUtil.GetConnectionString())) { //Create array of Parameters List <SqlParameter> arrParm = new List <SqlParameter> { new SqlParameter("@id", SqlDbType.Int) { Value = 0 } }; conn.Open(); dsResult = SqlHelper.ExecuteDataset(conn, "sp_approve_list", arrParm.ToArray()); if (dsResult != null) { var row = (from t in dsResult.Tables[0].AsEnumerable() select t).ToList(); foreach (var data in row) { approveDetail.Add(new ApproveDetail() { id = Convert.ToInt32(data["id"]), approve_doc = Convert.IsDBNull(data["approve_doc"]) ? null : Convert.ToString(data["approve_doc"]), approve_type = Convert.IsDBNull(data["approve_type"]) ? null : Convert.ToString(data["approve_type"]), name_app1 = Convert.IsDBNull(data["name_app1"]) ? "-" : Convert.ToString(data["name_app1"]), name_app2 = Convert.IsDBNull(data["name_app2"]) ? "-" : Convert.ToString(data["name_app2"]), name_app3 = Convert.IsDBNull(data["name_app3"]) ? "-" : Convert.ToString(data["name_app3"]), }); } } HttpContext.Current.Session["SESSION_APPROVE_LIST"] = approveDetail; } } gridView.DataSource = approveDetail; gridView.DataBind(); gridView.SettingsBehavior.AllowFocusedRow = true; } catch (Exception ex) { string strErrorMsg = SPlanetUtil.LogErrorCollect(ex); ScriptManager.RegisterStartupScript(this, GetType(), "myalert", "alert('" + strErrorMsg + "');", true); } }
protected void FillDistrictCombo(string amphurId) { try { dtDistrict = new DataTable(); dtDistrict.Columns.Add("data_text", typeof(string)); dtDistrict.Columns.Add("data_value", typeof(int)); dtDistrict.Columns.Add("amphur_id", typeof(int)); if (!string.IsNullOrEmpty(amphurId) && amphurId != "0") { var dtSource = new DataTable(); dtSource.Columns.Add("data_value", typeof(int)); dtSource.Columns.Add("data_text", typeof(string)); using (SqlConnection conn = new SqlConnection(SPlanetUtil.GetConnectionString())) { conn.Open(); using (DataSet dsResult = SqlHelper.ExecuteDataset(SPlanetUtil.GetConnectionString(), CommandType.StoredProcedure, DataListUtil.DropdownStoreProcedureName.Thailand_District)) { if (dsResult != null) { foreach (var row in dsResult.Tables[0].AsEnumerable()) { if (Convert.ToString(row["amphur_id"]) == amphurId) { dtDistrict.Rows.Add(row[1], Convert.ToInt32(row[0]), Convert.ToInt32(row[2])); } } } } conn.Close(); } var districtFilter = (from t in dtDistrict.AsEnumerable() where t.Field <int>("amphur_id") == Convert.ToInt32(amphurId) select t).ToList(); foreach (var row in districtFilter) { dtSource.Rows.Add(row[1], row[0]); } cboDistrict.DataSource = dtSource; cboDistrict.DataBind(); } } catch (Exception ex) { string strErrorMsg = SPlanetUtil.LogErrorCollect(ex); ScriptManager.RegisterStartupScript(this, GetType(), "myalert", "alert('" + strErrorMsg + "');", true); } }
protected void BindGrid(bool isForceRefreshData = false) { try { if (!Page.IsPostBack || isForceRefreshData) { using (SqlConnection conn = new SqlConnection(SPlanetUtil.GetConnectionString())) { //Create array of Parameters List <SqlParameter> arrParm = new List <SqlParameter> { new SqlParameter("@id", SqlDbType.Int) { Value = 0 }, new SqlParameter("@search_name", SqlDbType.VarChar, 200) { Value = "" }, new SqlParameter("@return_type", SqlDbType.VarChar, 200) { Value = strReturnType == "" ? "" :strReturnType } }; conn.Open(); dsResult = SqlHelper.ExecuteDataset(conn, "sp_return_header_list", arrParm.ToArray()); conn.Close(); Session["SESSION_RETURN_LIST"] = dsResult; } } //Bind data into GridView gridView.DataSource = dsResult;//(from t in dsResult.Tables[0].AsEnumerable() where t.Field<string>("return_type") == strReturnType select t).ToList(); gridView.FilterExpression = FilterBag.GetExpression(false); gridView.DataBind(); if (strReturnType == "IS") { gridView.Columns[7].Visible = false; } else { gridView.Columns[6].Visible = false; } } catch (Exception ex) { string strErrorMsg = SPlanetUtil.LogErrorCollect(ex); ScriptManager.RegisterStartupScript(this, GetType(), "myalert", "alert('" + strErrorMsg + "');", true); } }
protected void BindGrid(bool isForceRefreshData = false) { try { if (!Page.IsPostBack || isForceRefreshData) { using (SqlConnection conn = new SqlConnection(SPlanetUtil.GetConnectionString())) { //Create array of Parameters List <SqlParameter> arrParm = new List <SqlParameter> { new SqlParameter("@id", SqlDbType.Int) { Value = 0 }, new SqlParameter("@search_name", SqlDbType.VarChar, 200) { Value = "" } }; conn.Open(); dsResult = SqlHelper.ExecuteDataset(conn, "sp_spare_part_set_list", arrParm.ToArray()); ViewState["dsResult"] = dsResult; } } var dataGrid = (from t in dsResult.Tables[0].AsEnumerable() select new { spare_part_set_id = Convert.IsDBNull(t["product_set_id"]) ? 0 : Convert.ToInt32(t["product_set_id"]), spare_part_set_name = Convert.IsDBNull(t["part_name_tha"]) ? string.Empty : Convert.ToString(t["part_name_tha"]), spare_part_set_no = Convert.IsDBNull(t["part_no"]) ? string.Empty : Convert.ToString(t["part_no"]), count_product_id = Convert.IsDBNull(t["count_product_id"]) ? 0 : Convert.ToInt32(t["count_product_id"]), qty = Convert.IsDBNull(t["qty"]) ? 0 : Convert.ToInt32(t["qty"]), is_enable = t.Field <bool>("is_enable"), }).Distinct().ToList(); //Bind data into GridView gridViewSparePartSet.DataSource = dataGrid; gridViewSparePartSet.FilterExpression = FilterBag.GetExpression(false); gridViewSparePartSet.DataBind(); } catch (Exception ex) { string strErrorMsg = SPlanetUtil.LogErrorCollect(ex); ScriptManager.RegisterStartupScript(this, GetType(), "myalert", "alert('" + strErrorMsg + "');", true); } }
protected void BindGrid(bool isForceRefreshData = false) { try { if (!Page.IsPostBack || isForceRefreshData) { using (SqlConnection conn = new SqlConnection(SPlanetUtil.GetConnectionString())) { //Create array of Parameters List <SqlParameter> arrParm = new List <SqlParameter> { new SqlParameter("@user_id", SqlDbType.Int) { Value = ConstantClass.SESSION_USER_ID }, new SqlParameter("@id", SqlDbType.Int) { Value = 0 }, new SqlParameter("@search_name", SqlDbType.VarChar, 200) { Value = "" }, new SqlParameter("@service_type", SqlDbType.VarChar, 100) { Value = ConstantClass.SESSION_DEPARTMENT_SERVICE_TYPE }, // new SqlParameter("@customer_id", SqlDbType.Int) { Value = 0 }, }; conn.Open(); dsResult = SqlHelper.ExecuteDataset(conn, "sp_quotation_header_list", arrParm.ToArray()); conn.Close(); Session["SESSION_QUOTATION_LIST"] = dsResult; } } //Bind data into GridView gridView.DataSource = dsResult; gridView.FilterExpression = FilterBag.GetExpression(false); gridView.DataBind(); } catch (Exception ex) { string strErrorMsg = SPlanetUtil.LogErrorCollect(ex); ScriptManager.RegisterStartupScript(this, GetType(), "myalert", "alert('" + strErrorMsg + "');", true); } }
protected void BindGrid(bool isForceRefreshData = false) { try { if (!Page.IsPostBack || isForceRefreshData) { using (SqlConnection conn = new SqlConnection(SPlanetUtil.GetConnectionString())) { //Create array of Parameters List <SqlParameter> arrParm = new List <SqlParameter> { new SqlParameter("@id", SqlDbType.Int) { Value = 0 }, new SqlParameter("@search_name", SqlDbType.VarChar, 200) { Value = "" }, new SqlParameter("@service_type", SqlDbType.VarChar, 100) { Value = ConstantClass.SESSION_DEPARTMENT_SERVICE_TYPE }, }; conn.Open(); dsResult = SqlHelper.ExecuteDataset(conn, "sp_delivery_order_header_list", arrParm.ToArray()); conn.Close(); Session["SESSION_DELIVERY_ORDER_LIST"] = dsResult; } } //Bind data into GridView gridViewDeliveryOrder.DataSource = dsResult; gridViewDeliveryOrder.FilterExpression = FilterBag.GetExpression(false); gridViewDeliveryOrder.DataBind(); } catch (Exception ex) { string strErrorMsg = SPlanetUtil.LogErrorCollect(ex); Page.ClientScript.RegisterStartupScript(this.GetType(), "AlertMessage", "alertMessage('" + ex.Message + "','E')", true); } }
protected void gridView_CustomCallback(object sender, ASPxGridViewCustomCallbackEventArgs e) { var dsResult = new DataSet(); try { using (SqlConnection conn = new SqlConnection(SPlanetUtil.GetConnectionString())) { //Create array of Parameters List <SqlParameter> arrParm = new List <SqlParameter> { new SqlParameter("@user_id", SqlDbType.Int) { Value = ConstantClass.SESSION_USER_ID }, new SqlParameter("@id", SqlDbType.Int) { Value = 0 }, new SqlParameter("@search_name", SqlDbType.VarChar, 200) { Value = e.Parameters.ToString() } }; conn.Open(); dsResult = SqlHelper.ExecuteDataset(conn, "sp_sale_order_header_list", arrParm.ToArray()); conn.Close(); Session["SESSION_SALE_ORDER_LIST"] = dsResult; } //Bind data into GridView gridView.DataSource = dsResult; gridView.FilterExpression = FilterBag.GetExpression(false); gridView.DataBind(); } catch (Exception ex) { string strErrorMsg = SPlanetUtil.LogErrorCollect(ex); ScriptManager.RegisterStartupScript(this, GetType(), "myalert", "alert('" + strErrorMsg + "');", true); } }
protected void gridView_CustomCallback(object sender, ASPxGridViewCustomCallbackEventArgs e) { var dsResult = new DataSet(); try { using (SqlConnection conn = new SqlConnection(SPlanetUtil.GetConnectionString())) { //Create array of Parameters List <SqlParameter> arrParm = new List <SqlParameter> { new SqlParameter("@id", SqlDbType.Int) { Value = 0 }, new SqlParameter("@search_name", SqlDbType.VarChar, 200) { Value = e.Parameters.ToString() }, new SqlParameter("@return_type", SqlDbType.VarChar, 200) { Value = strReturnType == "" ? "" :strReturnType } }; conn.Open(); dsResult = SqlHelper.ExecuteDataset(conn, "sp_return_header_list", arrParm.ToArray()); conn.Close(); Session["SESSION_RETURN_LIST"] = dsResult; } //Bind data into GridView gridView.DataSource = dsResult;//(from t in dsResult.Tables[0].AsEnumerable() where t.Field<string>("return_type") == strReturnType select t).ToList(); ; gridView.FilterExpression = FilterBag.GetExpression(false); gridView.DataBind(); } catch (Exception ex) { string strErrorMsg = SPlanetUtil.LogErrorCollect(ex); ScriptManager.RegisterStartupScript(this, GetType(), "myalert", "alert('" + strErrorMsg + "');", true); } }
protected void BindGrid(bool isForceRefreshData = false) { try { if (!Page.IsPostBack || isForceRefreshData) { using (SqlConnection conn = new SqlConnection(SPlanetUtil.GetConnectionString())) { //Create array of Parameters List <SqlParameter> arrParm = new List <SqlParameter> { new SqlParameter("@id", SqlDbType.Int) { Value = 0 }, new SqlParameter("@search_name", SqlDbType.VarChar, 200) { Value = "" } }; conn.Open(); dsResult = SqlHelper.ExecuteDataset(conn, "sp_purchase_request_header_list", arrParm.ToArray()); conn.Close(); Session["SESSION_PURCHASE_REQUEST"] = dsResult; } } //Bind data into GridView gridView.DataSource = dsResult; gridView.FilterExpression = FilterBag.GetExpression(false); gridView.DataBind(); } catch (Exception ex) { string strErrorMsg = SPlanetUtil.LogErrorCollect(ex); ScriptManager.RegisterStartupScript(this, GetType(), "myalert", "alert('" + strErrorMsg + "');", true); } }
protected void gridView_CustomCallback(object sender, ASPxGridViewCustomCallbackEventArgs e) { var dsResult = new DataSet(); try { using (SqlConnection conn = new SqlConnection(SPlanetUtil.GetConnectionString())) { //Create array of Parameters List <SqlParameter> arrParm = new List <SqlParameter> { new SqlParameter("@search_name", SqlDbType.VarChar, 200) { Value = e.Parameters.ToString() }, new SqlParameter("@id", SqlDbType.Int) { Value = 0 } }; conn.Open(); dsResult = SqlHelper.ExecuteDataset(conn, "sp_supplier_group_list", arrParm.ToArray()); conn.Close(); Session["SESSION_SUPPLIER_GROUP_MASTER"] = dsResult; Session["SEARCH"] = e.Parameters.ToString(); } //Bind data into GridView gridView.DataSource = dsResult; gridView.DataBind(); } catch (Exception ex) { string strErrorMsg = SPlanetUtil.LogErrorCollect(ex); ScriptManager.RegisterStartupScript(this, GetType(), "myalert", "alert('" + strErrorMsg + "');", true); } }
private void Test_Pivot() { try { if (!Page.IsPostBack) { using (SqlConnection conn = new SqlConnection(SPlanetUtil.GetConnectionString())) { conn.Open(); dsPivot = SqlHelper.ExecuteDataset(conn, "sp_test_pivot"); ViewState["dsPivot"] = dsPivot; conn.Close(); pivotGridControl.RetrieveFields(PivotArea.FilterArea, false); // attention_name field. PivotGridField fieldAttentionName = new PivotGridField("attention_name", PivotArea.RowArea); fieldAttentionName.Caption = "ผู้ติดต่อ"; // project_name field. PivotGridField fieldProjectName = new PivotGridField("project_name", PivotArea.RowArea); fieldProjectName.Caption = "โครงการ"; // quotation_date field. PivotGridField fieldQuotationDate = new PivotGridField("quotation_date", PivotArea.ColumnArea); fieldQuotationDate.Caption = "Quotation Date"; fieldQuotationDate.GroupInterval = PivotGroupInterval.DateMonth; // total_amount field. // https://docs.microsoft.com/en-us/dotnet/standard/base-types/standard-numeric-format-strings PivotGridField fieldTotalAmount = new PivotGridField("total_amount", PivotArea.DataArea); fieldTotalAmount.Caption = "Total Amount"; fieldTotalAmount.CellFormat.FormatType = DevExpress.Utils.FormatType.Numeric; fieldTotalAmount.CellFormat.FormatString = "n2"; // vat_total field. PivotGridField fieldTotalVAT = new PivotGridField("vat_total", PivotArea.FilterArea); fieldTotalVAT.Caption = "VAT"; fieldTotalVAT.Visible = false; fieldTotalVAT.CellFormat.FormatType = DevExpress.Utils.FormatType.Numeric; fieldTotalVAT.CellFormat.FormatString = "n2"; // Add the fields to the control's field collection. pivotGridControl.Fields.Add(fieldAttentionName); pivotGridControl.Fields.Add(fieldProjectName); pivotGridControl.Fields.Add(fieldQuotationDate); pivotGridControl.Fields.Add(fieldTotalAmount); pivotGridControl.Fields.Add(fieldTotalVAT); // Arrange the row fields within the Row Header Area. fieldAttentionName.AreaIndex = 0; // Arrange the column fields within the Column Header Area. fieldQuotationDate.AreaIndex = 0; } } else { dsPivot = (DataSet)ViewState["dsPivot"]; } pivotGridControl.DataSource = dsPivot; pivotGridControl.DataBind(); } catch (Exception ex) { string strErrorMsg = SPlanetUtil.LogErrorCollect(ex); ScriptManager.RegisterStartupScript(this, GetType(), "myalert", "alert('" + strErrorMsg + "');", true); } }
private void Pivot_Quotation_Summary_Product() { try { if (!Page.IsPostBack) { using (SqlConnection conn = new SqlConnection(SPlanetUtil.GetConnectionString())) { //Create array of Parameters List <SqlParameter> arrParm = new List <SqlParameter> { new SqlParameter("@quotation_date_from", SqlDbType.VarChar, 20) { Value = "" }, new SqlParameter("@quotation_date_to", SqlDbType.VarChar, 20) { Value = "" } }; conn.Open(); dsPivot = SqlHelper.ExecuteDataset(conn, "sp_report_quotation_summary_product", arrParm.ToArray()); ViewState["dsPivot"] = dsPivot; conn.Close(); } pivotGridControl.RetrieveFields(PivotArea.FilterArea, false); #region "Create PivotGrid Field" // quotation_no field. PivotGridField fieldQuotationNo = new PivotGridField("quotation_no", PivotArea.RowArea); fieldQuotationNo.Caption = "Quotation No"; fieldQuotationNo.Visible = false; // quotation_date field. *** TRUE *** PivotGridField fieldQuotationDate_Month = new PivotGridField("quotation_date", PivotArea.ColumnArea); fieldQuotationDate_Month.Caption = "Quotation Date (Month)"; fieldQuotationDate_Month.Visible = true; fieldQuotationDate_Month.GroupInterval = PivotGroupInterval.DateMonth; // quotation_date field. PivotGridField fieldQuotationDate_Quarter = new PivotGridField("quotation_date", PivotArea.ColumnArea); fieldQuotationDate_Quarter.Caption = "Quotation Date (Quarter)"; fieldQuotationDate_Quarter.Visible = false; fieldQuotationDate_Quarter.GroupInterval = PivotGroupInterval.DateQuarterYear; // customer_name field. *** TRUE *** PivotGridField fieldCustomerName = new PivotGridField("customer_name", PivotArea.RowArea); fieldCustomerName.Caption = "Customer Name"; fieldCustomerName.Visible = true; // project_name field. PivotGridField fieldProjectName = new PivotGridField("project_name", PivotArea.RowArea); fieldProjectName.Caption = "Project Name"; fieldProjectName.Visible = false; // product_name field. PivotGridField fieldProductName = new PivotGridField("product_name", PivotArea.RowArea); fieldProductName.Caption = "Product Name"; fieldProductName.Visible = false; // province_name field. *** TRUE *** PivotGridField fieldProvinceName = new PivotGridField("province_name", PivotArea.RowArea); fieldProvinceName.Caption = "Province Name"; fieldProvinceName.Visible = true; // customer_address field. PivotGridField fieldCustomerAddress = new PivotGridField("customer_address", PivotArea.RowArea); fieldCustomerAddress.Caption = "Customer Address"; fieldCustomerAddress.Visible = false; // category_alias_list field. *** TRUE *** PivotGridField fieldCategoryAliasList = new PivotGridField("category_alias_list", PivotArea.FilterArea); fieldCategoryAliasList.Caption = "Category Alias List"; fieldCategoryAliasList.Visible = true; // model_list_air_compressor field. *** TRUE *** PivotGridField fieldModelListAirCompressor = new PivotGridField("model_list_air_compressor", PivotArea.RowArea); fieldModelListAirCompressor.Caption = "Air Compressor-Model"; fieldModelListAirCompressor.Visible = false; // model_list_air_compressor_pressure field. PivotGridField fieldModelListAirCompressorPressure = new PivotGridField("model_list_air_compressor_pressure", PivotArea.RowArea); fieldModelListAirCompressorPressure.Caption = "Air Compressor-Pressure"; fieldModelListAirCompressorPressure.Visible = false; // model_list_air_compressor_qty field. *** TRUE *** PivotGridField fieldModelListAirCompressorQty = new PivotGridField("model_list_air_compressor_qty", PivotArea.DataArea); fieldModelListAirCompressorQty.Caption = "Air Compressor-Qty"; fieldModelListAirCompressorQty.Visible = true; // model_list_air_dryer field. PivotGridField fieldModelListAirDryer = new PivotGridField("model_list_air_dryer", PivotArea.RowArea); fieldModelListAirDryer.Caption = "Air Dryer-Model"; fieldModelListAirDryer.Visible = false; // model_list_air_dryer_qty field. *** TRUE *** PivotGridField fieldModelListAirDryerQty = new PivotGridField("model_list_air_dryer_qty", PivotArea.DataArea); fieldModelListAirDryerQty.Caption = "Air Dryer-Qty"; fieldModelListAirDryerQty.Visible = true; // model_list_line_filter field. PivotGridField fieldModelListLineFilter = new PivotGridField("model_list_line_filter", PivotArea.RowArea); fieldModelListLineFilter.Caption = "Line Filter-Model"; fieldModelListLineFilter.Visible = false; // model_list_line_filter_qty field. *** TRUE *** PivotGridField fieldModelListLineFilterQty = new PivotGridField("model_list_line_filter_qty", PivotArea.DataArea); fieldModelListLineFilterQty.Caption = "Line Filter-Qty"; fieldModelListLineFilterQty.Visible = true; // model_list_mist_filter field. PivotGridField fieldModelListMistFilter = new PivotGridField("model_list_mist_filter", PivotArea.RowArea); fieldModelListMistFilter.Caption = "Mist Filter-Model"; fieldModelListMistFilter.Visible = false; // model_list_mist_filter_qty field. *** TRUE *** PivotGridField fieldModelListMistFilterQty = new PivotGridField("model_list_mist_filter_qty", PivotArea.DataArea); fieldModelListMistFilterQty.Caption = "Mist Filter-Qty"; fieldModelListMistFilterQty.Visible = true; // model_list_air_tank field. PivotGridField fieldModelListAirTank = new PivotGridField("model_list_air_tank", PivotArea.RowArea); fieldModelListAirTank.Caption = "Air Tank-Model"; fieldModelListAirTank.Visible = false; // model_list_air_tank_qty field. *** TRUE *** PivotGridField fieldModelListAirTankQty = new PivotGridField("model_list_air_tank_qty", PivotArea.DataArea); fieldModelListAirTankQty.Caption = "Air Tank-Qty"; fieldModelListAirTankQty.Visible = true; // model_list_other field. PivotGridField fieldModelListOther = new PivotGridField("model_list_other", PivotArea.RowArea); fieldModelListOther.Caption = "Other-Model"; fieldModelListOther.Visible = false; // model_list_other_qty field. *** TRUE *** PivotGridField fieldModelListOtherQty = new PivotGridField("model_list_other_qty", PivotArea.DataArea); fieldModelListOtherQty.Caption = "Other-Qty"; fieldModelListOtherQty.Visible = true; // sales_name field. PivotGridField fieldSalesName = new PivotGridField("sales_name", PivotArea.RowArea); fieldSalesName.Caption = "Sales Name"; fieldSalesName.Visible = false; // total_amount field. PivotGridField fieldTotalAmount = new PivotGridField("total_amount", PivotArea.DataArea); fieldTotalAmount.Caption = "Total Amount"; fieldTotalAmount.Visible = false; fieldTotalAmount.CellFormat.FormatType = DevExpress.Utils.FormatType.Numeric; fieldTotalAmount.CellFormat.FormatString = "n2"; // total_discount field. PivotGridField fieldTotalDiscount = new PivotGridField("total_discount", PivotArea.DataArea); fieldTotalDiscount.Caption = "Total Discount"; fieldTotalDiscount.Visible = false; fieldTotalDiscount.CellFormat.FormatType = DevExpress.Utils.FormatType.Numeric; fieldTotalDiscount.CellFormat.FormatString = "n2"; // grand_total field. *** TRUE *** PivotGridField fieldGrandTotal = new PivotGridField("grand_total", PivotArea.DataArea); fieldGrandTotal.Caption = "Net Amount"; fieldGrandTotal.Visible = true; fieldGrandTotal.CellFormat.FormatType = DevExpress.Utils.FormatType.Numeric; fieldGrandTotal.CellFormat.FormatString = "n2"; // contact_name field. PivotGridField fieldContactName = new PivotGridField("contact_name", PivotArea.RowArea); fieldContactName.Caption = "Contact Name"; fieldContactName.Visible = false; // status_name field. *** TRUE *** PivotGridField fieldStatusName = new PivotGridField("status_name", PivotArea.FilterArea); fieldStatusName.Caption = "Status Name"; fieldStatusName.Visible = true; // status_date_dd field. PivotGridField fieldStatusDate = new PivotGridField("status_date", PivotArea.RowArea); fieldStatusDate.Caption = "Status Date"; fieldStatusDate.Visible = false; fieldStatusDate.GroupInterval = PivotGroupInterval.Date; // customer_tel field. PivotGridField fieldCustomerTel = new PivotGridField("customer_tel", PivotArea.RowArea); fieldCustomerTel.Caption = "Customer Tel"; fieldCustomerTel.Visible = false; // customer_fax field. PivotGridField fieldCustomerFax = new PivotGridField("customer_fax", PivotArea.RowArea); fieldCustomerFax.Caption = "Customer Fax"; fieldCustomerFax.Visible = false; // customer_email field. PivotGridField fieldCustomerEmail = new PivotGridField("customer_email", PivotArea.RowArea); fieldCustomerEmail.Caption = "Customer Email"; fieldCustomerEmail.Visible = false; #endregion #region " Add fields to Pivot Control" // Add the fields to the control's field collection. pivotGridControl.Fields.Add(fieldQuotationNo); pivotGridControl.Fields.Add(fieldQuotationDate_Month); pivotGridControl.Fields.Add(fieldQuotationDate_Quarter); pivotGridControl.Fields.Add(fieldCustomerName); pivotGridControl.Fields.Add(fieldProjectName); pivotGridControl.Fields.Add(fieldProductName); pivotGridControl.Fields.Add(fieldProvinceName); pivotGridControl.Fields.Add(fieldCustomerAddress); pivotGridControl.Fields.Add(fieldCategoryAliasList); pivotGridControl.Fields.Add(fieldTotalAmount); pivotGridControl.Fields.Add(fieldTotalDiscount); pivotGridControl.Fields.Add(fieldGrandTotal); pivotGridControl.Fields.Add(fieldModelListAirCompressor); pivotGridControl.Fields.Add(fieldModelListAirCompressorPressure); pivotGridControl.Fields.Add(fieldModelListAirCompressorQty); pivotGridControl.Fields.Add(fieldModelListAirDryer); pivotGridControl.Fields.Add(fieldModelListAirDryerQty); pivotGridControl.Fields.Add(fieldModelListLineFilter); pivotGridControl.Fields.Add(fieldModelListLineFilterQty); pivotGridControl.Fields.Add(fieldModelListMistFilter); pivotGridControl.Fields.Add(fieldModelListMistFilterQty); pivotGridControl.Fields.Add(fieldModelListAirTank); pivotGridControl.Fields.Add(fieldModelListAirTankQty); pivotGridControl.Fields.Add(fieldModelListOther); pivotGridControl.Fields.Add(fieldModelListOtherQty); pivotGridControl.Fields.Add(fieldSalesName); pivotGridControl.Fields.Add(fieldContactName); pivotGridControl.Fields.Add(fieldStatusName); pivotGridControl.Fields.Add(fieldStatusDate); pivotGridControl.Fields.Add(fieldCustomerTel); pivotGridControl.Fields.Add(fieldCustomerFax); pivotGridControl.Fields.Add(fieldCustomerEmail); //pivotGridControl.Fields.Add(fieldRemark); #endregion // Arrange the row fields within the Row Header Area. fieldProvinceName.AreaIndex = 0; fieldCustomerName.AreaIndex = 1; // Arrange the row fields within the Column Header Area. fieldGrandTotal.AreaIndex = 0; fieldModelListAirCompressorQty.AreaIndex = 1; fieldModelListAirDryerQty.AreaIndex = 2; fieldModelListLineFilterQty.AreaIndex = 3; fieldModelListMistFilterQty.AreaIndex = 4; fieldModelListAirTankQty.AreaIndex = 5; fieldModelListOtherQty.AreaIndex = 6; } else { dsPivot = (DataSet)ViewState["dsPivot"]; } pivotGridControl.DataSource = dsPivot; pivotGridControl.DataBind(); } catch (Exception ex) { string strErrorMsg = SPlanetUtil.LogErrorCollect(ex); ScriptManager.RegisterStartupScript(this, GetType(), "myalert", "alert('" + strErrorMsg + "');", true); } }
private void Pivot_Quotation_Summary_Contract() { try { if (!Page.IsPostBack) { using (SqlConnection conn = new SqlConnection(SPlanetUtil.GetConnectionString())) { //Create array of Parameters List <SqlParameter> arrParm = new List <SqlParameter> { new SqlParameter("@quotation_date_from", SqlDbType.VarChar, 20) { Value = "" }, new SqlParameter("@quotation_date_to", SqlDbType.VarChar, 20) { Value = "" } }; conn.Open(); dsPivot = SqlHelper.ExecuteDataset(conn, "sp_report_quotation_summary_service_contract", arrParm.ToArray()); ViewState["dsPivot"] = dsPivot; conn.Close(); } pivotGridControl.RetrieveFields(PivotArea.FilterArea, false); #region "Create PivotGrid Field" PivotGridField fieldContractNo = new PivotGridField("contract_no", PivotArea.RowArea); fieldContractNo.Caption = "Contract No"; fieldContractNo.Visible = false; PivotGridField fieldQuotationNo = new PivotGridField("quotation_no", PivotArea.RowArea); fieldQuotationNo.Caption = "Quotation No"; fieldQuotationNo.Visible = false; PivotGridField fieldIssueDate_Month = new PivotGridField("issue_date", PivotArea.ColumnArea); fieldIssueDate_Month.Caption = "Issue Date (Month)"; fieldIssueDate_Month.Visible = true; fieldIssueDate_Month.GroupInterval = PivotGroupInterval.DateMonth; PivotGridField fieldIssueDate_Year = new PivotGridField("issue_date", PivotArea.ColumnArea); fieldIssueDate_Year.Caption = "Issue Date (Quarter)"; fieldIssueDate_Year.Visible = false; fieldIssueDate_Year.GroupInterval = PivotGroupInterval.DateQuarterYear; PivotGridField fieldCode = new PivotGridField("customer_code", PivotArea.ColumnArea); fieldCode.Caption = "Code"; fieldCode.Visible = false; PivotGridField fieldCustomerName = new PivotGridField("customer_name", PivotArea.RowArea); fieldCustomerName.Caption = "Customer Name"; fieldCustomerName.Visible = true; PivotGridField fieldProject = new PivotGridField("project_name", PivotArea.RowArea); fieldProject.Caption = "Project"; fieldProject.Visible = true; PivotGridField fieldPrpduct = new PivotGridField("product", PivotArea.RowArea); fieldPrpduct.Caption = "Product"; fieldPrpduct.Visible = false; PivotGridField fieldContact = new PivotGridField("contact_name", PivotArea.FilterArea); fieldContact.Caption = "Contact"; fieldContact.Visible = true; PivotGridField fieldQty = new PivotGridField("qty", PivotArea.RowArea); fieldQty.Caption = "Qty"; fieldQty.Visible = false; PivotGridField fieldUnit_Price = new PivotGridField("unit_price", PivotArea.RowArea); fieldUnit_Price.Caption = "Unit Price"; fieldUnit_Price.Visible = false; PivotGridField fieldDiscount1 = new PivotGridField("Discount1", PivotArea.RowArea); fieldDiscount1.Caption = "Discount1"; fieldDiscount1.Visible = false; PivotGridField fieldDiscount2 = new PivotGridField("Discount2", PivotArea.DataArea); fieldDiscount2.Caption = "Discount2"; fieldDiscount2.Visible = true; PivotGridField fieldamount = new PivotGridField("amount", PivotArea.RowArea); fieldamount.Caption = "Total Amount"; fieldamount.Visible = false; PivotGridField fieldTypeOfContract = new PivotGridField("type_of_contract", PivotArea.DataArea); fieldTypeOfContract.Caption = "Type Of Contract"; fieldTypeOfContract.Visible = true; PivotGridField fieldStartiingDD = new PivotGridField("startiing_date_DD", PivotArea.RowArea); fieldStartiingDD.Caption = "STARTIING DD"; fieldStartiingDD.Visible = false; PivotGridField fieldStartiingMM = new PivotGridField("sstartiing_date_MM", PivotArea.RowArea); fieldStartiingMM.Caption = "STARTIING MM"; fieldStartiingMM.Visible = false; PivotGridField fieldStartiingYY = new PivotGridField("startiing_date_YY", PivotArea.DataArea); fieldStartiingYY.Caption = "STARTIING YY"; fieldStartiingYY.Visible = true; PivotGridField fieldExpiredDateDD = new PivotGridField("expire_date_DD", PivotArea.DataArea); fieldExpiredDateDD.Caption = "EXPIRE DD"; fieldExpiredDateDD.Visible = true; PivotGridField fieldExpiredDateMM = new PivotGridField("expire_date_MM", PivotArea.RowArea); fieldExpiredDateMM.Caption = "EXPIRE MM"; fieldExpiredDateMM.Visible = false; PivotGridField fieldExpiredDateYY = new PivotGridField("expire_date_YY", PivotArea.DataArea); fieldExpiredDateYY.Caption = "EXPIRE YY"; fieldExpiredDateYY.Visible = true; PivotGridField fieldDetail_Status = new PivotGridField("status_name", PivotArea.DataArea); fieldDetail_Status.Caption = "DETAIL_STATUS"; fieldDetail_Status.Visible = true; PivotGridField fielDetailDD = new PivotGridField("detail_date_DD", PivotArea.DataArea); fielDetailDD.Caption = "Detail DD"; fielDetailDD.Visible = true; PivotGridField fieldDetailMM = new PivotGridField("detail_date_MM", PivotArea.RowArea); fieldDetailMM.Caption = "Date MM"; fieldDetailMM.Visible = false; PivotGridField fieldDetailYY = new PivotGridField("detail_date_YY", PivotArea.DataArea); fieldDetailYY.Caption = "Detail YY "; fieldDetailYY.Visible = true; PivotGridField fieldPoNo = new PivotGridField("po_no", PivotArea.RowArea); fieldPoNo.Caption = "Po No"; fieldPoNo.Visible = false; PivotGridField fieldAmountPo = new PivotGridField("amount_no", PivotArea.DataArea); fieldAmountPo.Caption = "Amount Po"; fieldAmountPo.Visible = true; PivotGridField fielInvoiceDD = new PivotGridField("inv_date_DD", PivotArea.DataArea); fielInvoiceDD.Caption = "Invoice DD"; fielInvoiceDD.Visible = true; PivotGridField fieldInvoiceMM = new PivotGridField("inv_date_MM", PivotArea.RowArea); fieldInvoiceMM.Caption = "Invoice MM"; fieldInvoiceMM.Visible = false; PivotGridField fieldInvoiceYY = new PivotGridField("inv_date_YY", PivotArea.DataArea); fieldInvoiceYY.Caption = "Invoice YY "; fieldInvoiceYY.Visible = true; PivotGridField fieldInvNo = new PivotGridField("inv_no", PivotArea.RowArea); fieldInvNo.Caption = "Inv No"; fieldInvNo.Visible = false; PivotGridField fieldScheduleMM1 = new PivotGridField("schedule_from_MM", PivotArea.RowArea); fieldScheduleMM1.Caption = "Schedu MM"; fieldScheduleMM1.Visible = false; PivotGridField fieldScheduleYY1 = new PivotGridField("schedule_from_YY", PivotArea.DataArea); fieldScheduleYY1.Caption = "Schedu YY "; fieldScheduleYY1.Visible = true; PivotGridField fieldScheduleMM2 = new PivotGridField("schedule_to_MM", PivotArea.DataArea); fieldScheduleMM2.Caption = "Schedu MM"; fieldScheduleMM2.Visible = false; PivotGridField fieldScheduleYY2 = new PivotGridField("schedule_to_YY", PivotArea.DataArea); fieldScheduleYY2.Caption = "dSchedule YY "; fieldScheduleYY2.Visible = true; PivotGridField fieldNew_Contract_MM = new PivotGridField("new_contract_date_MM", PivotArea.DataArea); fieldNew_Contract_MM.Caption = "New Contract MM"; fieldNew_Contract_MM.Visible = false; PivotGridField fieldNew_Contract_YY = new PivotGridField("new_contract_date_YY", PivotArea.DataArea); fieldNew_Contract_YY.Caption = "New Contract YY"; fieldNew_Contract_YY.Visible = false; #endregion #region " Add fields to Pivot Control" // Add the fields to the control's field collection. pivotGridControl.Fields.Add(fieldContractNo); pivotGridControl.Fields.Add(fieldQuotationNo); pivotGridControl.Fields.Add(fieldIssueDate_Month); pivotGridControl.Fields.Add(fieldIssueDate_Year); pivotGridControl.Fields.Add(fieldCode); pivotGridControl.Fields.Add(fieldCustomerName); pivotGridControl.Fields.Add(fieldProject); pivotGridControl.Fields.Add(fieldPrpduct); pivotGridControl.Fields.Add(fieldContact); pivotGridControl.Fields.Add(fieldQty); pivotGridControl.Fields.Add(fieldUnit_Price); pivotGridControl.Fields.Add(fieldDiscount1); pivotGridControl.Fields.Add(fieldDiscount2); pivotGridControl.Fields.Add(fieldamount); pivotGridControl.Fields.Add(fieldTypeOfContract); pivotGridControl.Fields.Add(fieldStartiingDD); pivotGridControl.Fields.Add(fieldStartiingMM); pivotGridControl.Fields.Add(fieldStartiingYY); pivotGridControl.Fields.Add(fieldExpiredDateDD); pivotGridControl.Fields.Add(fieldExpiredDateMM); pivotGridControl.Fields.Add(fieldExpiredDateYY); pivotGridControl.Fields.Add(fieldDetail_Status); pivotGridControl.Fields.Add(fielDetailDD); pivotGridControl.Fields.Add(fieldDetailMM); pivotGridControl.Fields.Add(fieldDetailYY); pivotGridControl.Fields.Add(fieldPoNo); pivotGridControl.Fields.Add(fieldAmountPo); pivotGridControl.Fields.Add(fielInvoiceDD); pivotGridControl.Fields.Add(fieldInvoiceMM); pivotGridControl.Fields.Add(fieldInvoiceYY); pivotGridControl.Fields.Add(fieldInvNo); pivotGridControl.Fields.Add(fieldScheduleMM1); pivotGridControl.Fields.Add(fieldScheduleYY1); pivotGridControl.Fields.Add(fieldScheduleMM2); pivotGridControl.Fields.Add(fieldScheduleYY2); pivotGridControl.Fields.Add(fieldNew_Contract_MM); pivotGridControl.Fields.Add(fieldNew_Contract_YY); #endregion // Arrange the row fields within the Row Header Area. เลือกข้อมูลแถวที่แสดงตรงส่วนหัวของแถว fieldContractNo.AreaIndex = 0; fieldCustomerName.AreaIndex = 1; // Arrange the row fields within the Column Header Area.เลือกข้อมูลคอลัมที่แสดงตรงส่วนหัวของแถว fieldQuotationNo.AreaIndex = 0; fieldCode.AreaIndex = 1; fieldProject.AreaIndex = 2; fieldIssueDate_Month.AreaIndex = 3; fieldIssueDate_Year.AreaIndex = 4; fieldPoNo.AreaIndex = 5; } else { dsPivot = (DataSet)ViewState["dsPivot"]; } pivotGridControl.DataSource = dsPivot; pivotGridControl.DataBind(); } catch (Exception ex) { string strErrorMsg = SPlanetUtil.LogErrorCollect(ex); ScriptManager.RegisterStartupScript(this, GetType(), "myalert", "alert('" + strErrorMsg + "');", true); } }
public static string CheckLogin(string username, string password) { string status = "error"; try { var encode_password = SPlanetUtil.Encrypt(password); //md5 using (SqlConnection conn = new SqlConnection(SPlanetUtil.GetConnectionString())) { //Create array of Parameters List <SqlParameter> arrParm = new List <SqlParameter> { new SqlParameter("@username", SqlDbType.NChar, 20) { Value = username }, new SqlParameter("@password", SqlDbType.Char, 32) { Value = encode_password }, }; conn.Open(); //dsResult = SqlHelper.ExecuteDataset(conn, "sp_security_user_list", arrParm.ToArray()); using (DataSet dsResult = SqlHelper.ExecuteDataset(conn, "sp_security_user_list", arrParm.ToArray())) { if (dsResult.Tables.Count > 0) { DataRow objRow; objRow = (from t in dsResult.Tables[0].AsEnumerable() select t).FirstOrDefault(); if (objRow != null) { status = "success"; ConstantClass.SESSION_USER_GROUP_ID = objRow["group_id"].ToString(); ConstantClass.SESSION_USER_CODE = objRow["employee_code"].ToString(); //session =>ConstantClass.cs ConstantClass.SESSION_FIRST_NAME = objRow["first_name"].ToString(); ConstantClass.SESSION_LAST_NAME = objRow["last_name"].ToString(); ConstantClass.SESSION_USER_ID = objRow["id"].ToString(); ConstantClass.SESSION_PICTURE = objRow["cover_image"].ToString(); ConstantClass.SESSION_DEPARTMENT_NAME = objRow["department_name"].ToString(); ConstantClass.SESSION_DEPARTMENT_SERVICE_TYPE = objRow["department_service_type"].ToString(); } } } using (DataSet dsResult = SqlHelper.ExecuteDataset(conn, "sp_information_company")) { DataRow objRow; if (dsResult.Tables.Count > 0) { objRow = (from t in dsResult.Tables[0].AsEnumerable() select t).FirstOrDefault(); if (objRow != null) { ConstantClass.VAT = Convert.ToInt32(objRow["vat"]); } } } conn.Close(); } } catch (Exception ex) { string strErrorMsg = SPlanetUtil.LogErrorCollect(ex); } return(status); }
public static string Export_Quotation_Summary_Product(SummaryQuotationContractData[] dataSummaryQuotationContract) { var strDownloadUrl = ""; try { string strTemplatePath = "/Master/Template/"; string baseUrl = string.Empty; String strExcelPath = string.Empty; string strSummaryQUTemplateFile = "Template_QuotationSummaryReport-Contract.xlsx"; int intStartRow = 5; DataSet dsResult; string excelTemplate = Path.Combine(HttpContext.Current.Server.MapPath(strTemplatePath), strSummaryQUTemplateFile); FileInfo templateFile = new FileInfo(excelTemplate); ExcelPackage excel = new ExcelPackage(templateFile); var templateSheet = excel.Workbook.Worksheets["Sheet1"]; var workSheet = excel.Workbook.Worksheets.Copy("Sheet1", "SummaryQU"); workSheet.DeleteRow(intStartRow, 1048576 - intStartRow); var data = (from t in dataSummaryQuotationContract select t).FirstOrDefault(); if (data != null) { using (SqlConnection conn = new SqlConnection(SPlanetUtil.GetConnectionString())) { //Create array of Parameters List <SqlParameter> arrParm = new List <SqlParameter> { new SqlParameter("@customer_id", SqlDbType.Int) { Value = data.customer_id }, new SqlParameter("@customer_code", SqlDbType.VarChar, 50) { Value = data.customer_code }, new SqlParameter("@customer_group", SqlDbType.Int) { Value = data.customer_group }, new SqlParameter("@contract_no", SqlDbType.VarChar, 50) { Value = data.contract_no }, new SqlParameter("@type_of_contract_id", SqlDbType.Int) { Value = data.contract_type }, new SqlParameter("@quotation_no", SqlDbType.VarChar, 20) { Value = data.quotation_no }, new SqlParameter("@quotation_status", SqlDbType.VarChar, 2) { Value = data.quotation_status }, new SqlParameter("@quotation_date_from", SqlDbType.VarChar, 20) { Value = SPlanetUtil.Convert_ddmmyyyy_to_yyyymmdd(data.quotation_date_from) }, new SqlParameter("@quotation_date_to", SqlDbType.VarChar, 20) { Value = SPlanetUtil.Convert_ddmmyyyy_to_yyyymmdd(data.quotation_date_to) }, new SqlParameter("@po_date_from", SqlDbType.VarChar, 20) { Value = SPlanetUtil.Convert_ddmmyyyy_to_yyyymmdd(data.po_date_from) }, new SqlParameter("@po_date_to", SqlDbType.VarChar, 20) { Value = SPlanetUtil.Convert_ddmmyyyy_to_yyyymmdd(data.po_date_to) }, new SqlParameter("@starting_date_from", SqlDbType.VarChar, 20) { Value = SPlanetUtil.Convert_ddmmyyyy_to_yyyymmdd(data.Starting_date_from) }, new SqlParameter("@starting_date_to", SqlDbType.VarChar, 20) { Value = SPlanetUtil.Convert_ddmmyyyy_to_yyyymmdd(data.Starting_date_to) }, new SqlParameter("@expire_date_from", SqlDbType.VarChar, 20) { Value = SPlanetUtil.Convert_ddmmyyyy_to_yyyymmdd(data.expire_date_from) }, new SqlParameter("@expire_date_to", SqlDbType.VarChar, 20) { Value = SPlanetUtil.Convert_ddmmyyyy_to_yyyymmdd(data.expire_date_to) }, }; conn.Open(); dsResult = SqlHelper.ExecuteDataset(conn, "sp_report_quotation_summary_service_contract", arrParm.ToArray()); var i = 0; foreach (DataRow row in dsResult.Tables[0].Rows) { i++; workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Contract.NO].Value = i; workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Contract.CONTRACT_NO].Value = row["contract_no"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Contract.QUOTATION_No].Value = row["quotation_no"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Contract.ISSUE_DATE].Value = row["quotation_date_dd"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Contract.ISSUE_DATE_MM].Value = row["quotation_date_mm"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Contract.ISSUE_DATE_YY].Value = row["quotation_date_yy"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Contract.CUSTOMER_CODE].Value = row["customer_code"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Contract.CUSTOMER_NAME].Value = row["customer_name"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Contract.PROJECT_NAME].Value = row["project_name"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Contract.CONTACT].Value = row["attention"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Contract.QTY].Value = row["qty"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Contract.TYPE_OF_CONTRACT].Value = row["remark1"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Contract.STARTIING_DATE].Value = row["remark2"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Contract.QUSTATUS].Value = row["status_name"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Contract.UPDATE_DD].Value = row["updated_date_dd"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Contract.UPDATE_MM].Value = row["updated_date_mm"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Contract.UPDATE_YY].Value = row["updated_date_yy"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Contract.PO_NO].Value = row["po_no"].ToString(); // row["inv_no"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Contract.PO_DATE].Value = row["po_date"].ToString(); // row["po_no"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Contract.AMOUNTPO].Value = row["total_amount"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Contract.INVDATE].Value = row["inv_date"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Contract.INVOICE_NO].Value = row["inv_no"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Contract.REMARK].Value = row["remark_status"].ToString(); intStartRow++; } } } templateSheet = excel.Workbook.Worksheets["Sheet1"]; excel.Workbook.Worksheets.Delete(templateSheet); //strDownloadUrl = baseUrl + strTemplatePath + strProductTemplateFile; string pathTemp = Path.GetTempFileName(); excel.SaveAs(new FileInfo(pathTemp)); strDownloadUrl = string.Format("LocalPath={0}&FileName={1}", pathTemp, "QuotationSummaryReport-Contract.xlsx"); } catch (Exception ex) { string strErrorMsg = SPlanetUtil.LogErrorCollect(ex); } return(strDownloadUrl); }
protected void BindGrid(bool isForceRefreshData = false) { try { if (!Page.IsPostBack || isForceRefreshData) { string search = ""; if (Session["SEARCH"] != null) { search = Session["SEARCH"].ToString(); txtSearchBoxData.Value = search; } using (SqlConnection conn = new SqlConnection(SPlanetUtil.GetConnectionString())) { //Create array of Parameters List <SqlParameter> arrParm = new List <SqlParameter> { new SqlParameter("@search_name", SqlDbType.VarChar, 200) { Value = search }, new SqlParameter("@id", SqlDbType.Int) { Value = 0 } }; conn.Open(); dsResult = SqlHelper.ExecuteDataset(conn, "sp_supplier_list", arrParm.ToArray()); conn.Close(); /*int i = 0; * int rowId = 0; * if (Session["ROW_ID"] != null) * { * rowId = Convert.ToInt32(Session["ROW_ID"].ToString()); * // If -1, Find the most value * if (rowId == -1) * { * foreach (var row in dsResult.Tables[0].AsEnumerable()) * { * int id = Convert.ToInt32(row["id"]); * if (rowId < id) * { * rowId = id; * } * } * } * } * foreach (var row in dsResult.Tables[0].AsEnumerable()) * { * if (rowId == Convert.ToInt32(row["id"])) * { * int selectedRow = i; * int prevRow = Convert.ToInt32(Session["ROW"]); * int pageSize = gridView.SettingsPager.PageSize; * int pageIndex = (int)(selectedRow / pageSize); * int prevPageIndex = Convert.ToInt32(Session["PAGE"]); * if (prevRow == selectedRow) * { * Session["PAGE"] = prevPageIndex; * Session["ROW"] = prevPageIndex * pageSize; * } * else * { * Session["PAGE"] = pageIndex; * Session["ROW"] = selectedRow; * } * } * i++; * }*/ Session["SESSION_SUPPLIER_MASTER"] = dsResult; } } //Bind data into GridView gridView.DataSource = dsResult; gridView.FilterExpression = FilterBag.GetExpression(false); gridView.DataBind(); // Check page from session if (Session["ROW_ID"] != null) { int row = Convert.ToInt32(Session["ROW"]); gridView.FocusedRowIndex = row; } if (Session["PAGE"] != null) { int page = Convert.ToInt32(Session["PAGE"]); gridView.PageIndex = page; } if (!Page.IsPostBack && Session["COLUMN"] != null && Session["ORDER"] != null) { int order = Convert.ToInt32(Session["ORDER"]); if (order == 1) { ((GridViewDataColumn)gridView.Columns[Session["COLUMN"].ToString()]).SortAscending(); } else { ((GridViewDataColumn)gridView.Columns[Session["COLUMN"].ToString()]).SortDescending(); } } } catch (Exception ex) { string strErrorMsg = SPlanetUtil.LogErrorCollect(ex); ScriptManager.RegisterStartupScript(this, GetType(), "myalert", "alert('" + strErrorMsg + "');", true); } }
private void Export_QuotationSummary_Spare_Part() { string strTemplateFile = "Template_QuotationSummaryReport_Contract.xlsx"; int intStartRow = 7; int i = 1; DataSet dsResult; try { // Open Excel Application MyApp = new Excel.Application(); MyApp.Visible = false; strExcelPath = Server.MapPath(strTemplatePath); MyBook = MyApp.Workbooks.Open(strExcelPath + strTemplateFile); MySheet = (Excel.Worksheet)MyBook.Sheets[1]; using (SqlConnection conn = new SqlConnection(SPlanetUtil.GetConnectionString())) { //Create array of Parameters List <SqlParameter> arrParm = new List <SqlParameter> { new SqlParameter("@quotation_date_from", SqlDbType.VarChar, 20) { Value = "" }, new SqlParameter("@quotation_date_to", SqlDbType.VarChar, 20) { Value = "" } }; conn.Open(); dsResult = SqlHelper.ExecuteDataset(conn, "sp_report_quotation_summary_service_contract", arrParm.ToArray()); foreach (DataRow row in dsResult.Tables[0].Rows) { MySheet.Cells[intStartRow, Column_QuotationSummary_Spare_Part.NO] = intStartRow - i; MySheet.Cells[intStartRow, Column_QuotationSummary_Spare_Part.CONTRACT_NO] = row["contract_no"].ToString(); MySheet.Cells[intStartRow, Column_QuotationSummary_Spare_Part.QUOTATION_NO] = row["quotation_no"].ToString(); MySheet.Cells[intStartRow, Column_QuotationSummary_Spare_Part.ISSUE_DATE_DD] = row["issue_date_DD"].ToString(); MySheet.Cells[intStartRow, Column_QuotationSummary_Spare_Part.ISSUE_DATE_MM] = row["issue_date_MM"].ToString(); MySheet.Cells[intStartRow, Column_QuotationSummary_Spare_Part.ISSUE_DATE_YY] = row["issue_date_YY"].ToString(); MySheet.Cells[intStartRow, Column_QuotationSummary_Spare_Part.CODE] = row["customer_code"].ToString(); MySheet.Cells[intStartRow, Column_QuotationSummary_Spare_Part.CUSTOMER_NAME] = row["customer_name"].ToString(); MySheet.Cells[intStartRow, Column_QuotationSummary_Spare_Part.PROJECTY] = row["project_name"].ToString(); MySheet.Cells[intStartRow, Column_QuotationSummary_Spare_Part.PRODUCT] = row["product"].ToString(); MySheet.Cells[intStartRow, Column_QuotationSummary_Spare_Part.CONTACT] = row["contact_name"].ToString(); MySheet.Cells[intStartRow, Column_QuotationSummary_Spare_Part.QTY] = row["qty"].ToString(); MySheet.Cells[intStartRow, Column_QuotationSummary_Spare_Part.UNIT_PRICE] = row["unit_price"].ToString(); MySheet.Cells[intStartRow, Column_QuotationSummary_Spare_Part.DISCOUNT1] = row["Discount1"].ToString(); MySheet.Cells[intStartRow, Column_QuotationSummary_Spare_Part.DISCOUNT2] = row["Discount2"].ToString();; MySheet.Cells[intStartRow, Column_QuotationSummary_Spare_Part.TOTAL_AMOUNT] = row["amount"].ToString(); MySheet.Cells[intStartRow, Column_QuotationSummary_Spare_Part.Type_Of_Contract] = row["type_of_contract"].ToString(); MySheet.Cells[intStartRow, Column_QuotationSummary_Spare_Part.STARTIING_DD] = row["startiing_date_DD"].ToString(); MySheet.Cells[intStartRow, Column_QuotationSummary_Spare_Part.STARTIING_MM] = row["startiing_date_MM"].ToString(); MySheet.Cells[intStartRow, Column_QuotationSummary_Spare_Part.STARTIING_YY] = row["startiing_date_YY"].ToString(); MySheet.Cells[intStartRow, Column_QuotationSummary_Spare_Part.EXPIRE_DATE_DD] = row["expire_date_DD"].ToString(); MySheet.Cells[intStartRow, Column_QuotationSummary_Spare_Part.EXPIRE_DATE_MM] = row["expire_date_MM"].ToString(); MySheet.Cells[intStartRow, Column_QuotationSummary_Spare_Part.EXPIRE_DATE_YY] = row["expire_date_YY"].ToString(); MySheet.Cells[intStartRow, Column_QuotationSummary_Spare_Part.DETAIL_STATUS] = row["status_name"].ToString(); MySheet.Cells[intStartRow, Column_QuotationSummary_Spare_Part.DETAIL_DATE_DD] = row["detail_date_DD"].ToString(); MySheet.Cells[intStartRow, Column_QuotationSummary_Spare_Part.DETAIL_DATE_MM] = row["detail_date_MM"].ToString(); MySheet.Cells[intStartRow, Column_QuotationSummary_Spare_Part.DETAIL_DATE_YY] = row["detail_date_YY"].ToString(); MySheet.Cells[intStartRow, Column_QuotationSummary_Spare_Part.No_PO] = row["po_no"].ToString(); MySheet.Cells[intStartRow, Column_QuotationSummary_Spare_Part.AMOUNT_PO] = row["amount_no"].ToString(); MySheet.Cells[intStartRow, Column_QuotationSummary_Spare_Part.INVOICE_DATE_DD] = row["inv_date_DD"].ToString(); MySheet.Cells[intStartRow, Column_QuotationSummary_Spare_Part.INVOICE_DATE_MM] = row["inv_date_MM"].ToString(); MySheet.Cells[intStartRow, Column_QuotationSummary_Spare_Part.INVOICE_DATE_YY] = row["inv_date_YY"].ToString(); MySheet.Cells[intStartRow, Column_QuotationSummary_Spare_Part.INV_No] = row["inv_no"].ToString(); MySheet.Cells[intStartRow, Column_QuotationSummary_Spare_Part.SCHEDULE_MM1] = row["schedule_from_MM"].ToString(); MySheet.Cells[intStartRow, Column_QuotationSummary_Spare_Part.SCHEDULE_YY1] = row["schedule_from_YY"].ToString(); MySheet.Cells[intStartRow, Column_QuotationSummary_Spare_Part.SCHEDULE_MM2] = row["schedule_to_MM"].ToString(); MySheet.Cells[intStartRow, Column_QuotationSummary_Spare_Part.SCHEDULE_YY2] = row["schedule_to_YY"].ToString(); MySheet.Cells[intStartRow, Column_QuotationSummary_Spare_Part.NEW_CONTRACT_MM] = row["new_contract_date_MM"].ToString(); MySheet.Cells[intStartRow, Column_QuotationSummary_Spare_Part.NEW_CONTRACT_YY] = row["new_contract_date_YY"].ToString(); i++; intStartRow++; } } if (System.IO.File.Exists(strExcelPath + "Temp_quotation_summary_Contract.xlsx")) { System.IO.File.Delete(strExcelPath + "Temp_quotation_summary_Contract.xlsx"); } MyBook.SaveAs(strExcelPath + "Temp_quotation_summary_Contract.xlsx"); MyApp.Quit(); MyApp = null; string strDownloadUrl = baseUrl + strTemplatePath + "Temp_quotation_summary_Contract.xlsx"; Response.Redirect(strDownloadUrl); } catch (Exception ex) { string strErrorMsg = SPlanetUtil.LogErrorCollect(ex); } }
public static string Export_Quotation_Summary_Design(SummaryQuotationDesignData[] dataSummaryQuotationDesign) { var strDownloadUrl = ""; try { string strTemplatePath = "/Master/Template/"; string baseUrl = string.Empty; String strExcelPath = string.Empty; string strSummaryQUTemplateFile = "Template_QuotationSummaryReport-Design.xlsx"; int intStartRow = 5; DataSet dsResult; string excelTemplate = Path.Combine(HttpContext.Current.Server.MapPath(strTemplatePath), strSummaryQUTemplateFile); FileInfo templateFile = new FileInfo(excelTemplate); ExcelPackage excel = new ExcelPackage(templateFile); var templateSheet = excel.Workbook.Worksheets["Sheet1"]; var workSheet = excel.Workbook.Worksheets.Copy("Sheet1", "SummaryQU"); workSheet.DeleteRow(intStartRow, 1048576 - intStartRow); var data = (from t in dataSummaryQuotationDesign select t).FirstOrDefault(); if (data != null) { using (SqlConnection conn = new SqlConnection(SPlanetUtil.GetConnectionString())) { //Create array of Parameters List <SqlParameter> arrParm = new List <SqlParameter> { new SqlParameter("@customer_id", SqlDbType.Int) { Value = Convert.ToInt32(data.customer_id) }, new SqlParameter("@customer_group", SqlDbType.Int) { Value = Convert.ToInt32(data.customer_group) }, new SqlParameter("@employee_id", SqlDbType.Int) { Value = Convert.ToInt32(data.employee_id) }, new SqlParameter("@quotation_status", SqlDbType.VarChar, 2) { Value = data.quotation_status }, new SqlParameter("@quotation_date_from", SqlDbType.VarChar, 20) { Value = SPlanetUtil.Convert_ddmmyyyy_to_yyyymmdd(data.quotation_date_from) }, new SqlParameter("@quotation_date_to", SqlDbType.VarChar, 20) { Value = SPlanetUtil.Convert_ddmmyyyy_to_yyyymmdd(data.quotation_date_to) }, new SqlParameter("@po_date_from", SqlDbType.VarChar, 20) { Value = SPlanetUtil.Convert_ddmmyyyy_to_yyyymmdd(data.po_date_from) }, new SqlParameter("@po_date_to", SqlDbType.VarChar, 20) { Value = SPlanetUtil.Convert_ddmmyyyy_to_yyyymmdd(data.po_date_to) }, new SqlParameter("@quotation_no", SqlDbType.VarChar, 20) { Value = data.quotation_no }, new SqlParameter("@other", SqlDbType.VarChar, 200) { Value = data.other }, //quotation_no: quotation_no, //other: other, }; conn.Open(); dsResult = SqlHelper.ExecuteDataset(conn, "sp_report_quotation_summary_design", arrParm.ToArray()); var i = 2; foreach (DataRow row in dsResult.Tables[0].Rows) { workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Design.COUNT_CALL].Value = i - 1; workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Design.QUOTATION_NO].Value = row["quotation_no"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Design.ISSUE_DD].Value = row["quotation_date_dd"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Design.ISSUE_MM].Value = row["quotation_date_mm"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Design.ISSUE_YY].Value = row["quotation_date_yy"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Design.CUSTOMER_NAME].Value = row["customer_name"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Design.PROJECT_NAME].Value = row["project_name"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Design.SUbJECT].Value = row["quotation_subject"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Design.DESCRIPTION].Value = row["descirption"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Design.SALES].Value = row["created_by"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Design.PRICE_LIST].Value = row["total_amount"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Design.DISCOUNT].Value = row["total_discount"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Design.FINAL_PRICE].Value = row["grand_total"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Design.CONTACT_PERSON].Value = row["contact_name"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Design.QUSTATUS].Value = row["status_name"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Design.UPDATE_DD].Value = row["updated_date_dd"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Design.UPDATE_MM].Value = row["updated_date_mm"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Design.UPDATE_YY].Value = row["updated_date_yy"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Design.PO_NO].Value = row["po_no"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Design.PODATE].Value = row["po_date"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Design.INVOICE].Value = row["inv_no"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Design.TEL].Value = row["customer_tel"].ToString(); intStartRow++; i++; } } } templateSheet = excel.Workbook.Worksheets["Sheet1"]; excel.Workbook.Worksheets.Delete(templateSheet); //strDownloadUrl = baseUrl + strTemplatePath + strProductTemplateFile; string pathTemp = Path.GetTempFileName(); excel.SaveAs(new FileInfo(pathTemp)); strDownloadUrl = string.Format("LocalPath={0}&FileName={1}", pathTemp, "QuotationSummaryReport-Design.xlsx"); } catch (Exception ex) { string strErrorMsg = SPlanetUtil.LogErrorCollect(ex); } return(strDownloadUrl); }
public static string Export_Quotation_Summary_Product(SummaryQuotationProductData[] dataSummaryQuotationProduct) { string strDownloadUrl = string.Empty; try { string strTemplatePath = "/Master/Template/"; string baseUrl = string.Empty; String strExcelPath = string.Empty; string strSummaryQUTemplateFile = "Template_QuotationSummaryReport-Product.xlsx"; int intStartRow = 5; DataSet dsResult; string excelTemplate = Path.Combine(HttpContext.Current.Server.MapPath(strTemplatePath), strSummaryQUTemplateFile); FileInfo templateFile = new FileInfo(excelTemplate); ExcelPackage excel = new ExcelPackage(templateFile); var templateSheet = excel.Workbook.Worksheets["Sheet1"]; var workSheet = excel.Workbook.Worksheets.Copy("Sheet1", "SummaryQU"); workSheet.DeleteRow(5, 1048576 - 5); var data = (from t in dataSummaryQuotationProduct select t).FirstOrDefault(); using (SqlConnection conn = new SqlConnection(SPlanetUtil.GetConnectionString())) { //Create array of Parameters List <SqlParameter> arrParm = new List <SqlParameter> { new SqlParameter("@customer_id", SqlDbType.Int) { Value = Convert.ToInt32(data.customer_id) }, new SqlParameter("@customer_group", SqlDbType.Int) { Value = Convert.ToInt32(data.customer_group) }, new SqlParameter("@cat_id", SqlDbType.Int) { Value = Convert.ToInt32(data.cat_id) }, new SqlParameter("@employee_id", SqlDbType.Int) { Value = Convert.ToInt32(data.employee_id) }, new SqlParameter("@quotation_status", SqlDbType.VarChar, 2) { Value = data.quotation_status }, new SqlParameter("@quotation_date_from", SqlDbType.VarChar, 20) { Value = SPlanetUtil.Convert_ddmmyyyy_to_yyyymmdd(data.quotation_date_from) }, new SqlParameter("@quotation_date_to", SqlDbType.VarChar, 20) { Value = SPlanetUtil.Convert_ddmmyyyy_to_yyyymmdd(data.quotation_date_to) }, new SqlParameter("@model_list_air_compressor", SqlDbType.VarChar, 200) { Value = data.model_list_air_compressor }, new SqlParameter("@model_list_air_presure", SqlDbType.VarChar, 200) { Value = data.model_list_air_presure }, new SqlParameter("@model_list_air_dryer", SqlDbType.VarChar, 200) { Value = data.model_list_air_dryer }, new SqlParameter("@model_list_line_filter", SqlDbType.VarChar, 200) { Value = data.model_list_line_filter }, new SqlParameter("@model_list_mist_filter", SqlDbType.VarChar, 200) { Value = data.model_list_mist_filter }, new SqlParameter("@model_list_air_tank", SqlDbType.VarChar, 200) { Value = data.model_list_air_tank }, new SqlParameter("@model_list_other", SqlDbType.VarChar, 200) { Value = data.model_list_other }, new SqlParameter("@other", SqlDbType.VarChar, 200) { Value = data.other }, new SqlParameter("@po_date_from", SqlDbType.VarChar, 20) { Value = SPlanetUtil.Convert_ddmmyyyy_to_yyyymmdd(data.po_date_from) }, new SqlParameter("@po_date_to", SqlDbType.VarChar, 20) { Value = SPlanetUtil.Convert_ddmmyyyy_to_yyyymmdd(data.po_date_to) }, new SqlParameter("@quotation_no", SqlDbType.VarChar, 20) { Value = data.quotation_no }, }; conn.Open(); dsResult = SqlHelper.ExecuteDataset(conn, "sp_report_quotation_summary_product", arrParm.ToArray()); var i = 0; foreach (DataRow row in dsResult.Tables[0].Rows) { i++; templateSheet.SelectedRange["A5:XFA5"].Copy(workSheet.SelectedRange[intStartRow, 1]); //workSheet.Cells[intStartRow, 1].Value = i; //workSheet.Cells[intStartRow, 2].Value = Convert.IsDBNull(row["id"]) ? string.Empty : Convert.ToString(row["id"]); //workSheet.Cells[intStartRow, 3].Value = Convert.IsDBNull(row["product_no"]) ? string.Empty : Convert.ToString(row["product_no"]); //workSheet.Cells[intStartRow, 4].Value = Convert.IsDBNull(row["product_name_tha"]) ? string.Empty : Convert.ToString(row["product_name_tha"]); //workSheet.Cells[intStartRow, 5].Value = Convert.IsDBNull(row["unit_code"]) ? string.Empty : Convert.ToString(row["unit_code"]); //workSheet.Cells[intStartRow, 6].Value = Convert.IsDBNull(row["quantity"]) ? string.Empty : Convert.ToString(row["quantity"]); //workSheet.Cells[intStartRow, 7].Value = Convert.IsDBNull(row["quantity_reserve"]) ? string.Empty : Convert.ToString(row["quantity_reserve"]); //workSheet.Cells[intStartRow, 8].Value = 0; //workSheet.Cells[intStartRow, 9].Value = string.Empty; workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.COUNT_CALL].Value = i; workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.QUOTATION_NO].Value = row["quotation_no"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.QUOTATION_DATE_DD].Value = row["quotation_date_dd"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.QUOTATION_DATE_MM].Value = row["quotation_date_mm"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.QUOTATION_DATE_YY].Value = row["quotation_date_yy"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.CUSTOMER_NAME].Value = row["customer_name"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.PROJECT_NAME].Value = row["project_name"].ToString(); //workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.PRODUCT_NAME].Value = row["product_name"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.PROVINCE_NAME].Value = row["province_name"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.CUSTOMER_ADDRESS].Value = row["customer_address"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.CATEGORY_ALIAS_LIST].Value = row["cat_code"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.MODEL_LIST_AIR_COMPRESSOR].Value = row["model_list_air_compressor"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.MODEL_LIST_AIR_COMPRESSOR_PRESSURE].Value = row["model_list_air_compressor_pressure"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.MODEL_LIST_AIR_COMPRESSOR_QTY].Value = row["model_list_air_compressor_qty"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.MODEL_LIST_AIR_DRYER].Value = row["model_list_air_dryer"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.MODEL_LIST_AIR_DRYER_QTY].Value = row["model_list_air_dryer_qty"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.MODEL_LIST_LINE_FILTER].Value = row["model_list_line_filter"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.MODEL_LIST_LINE_FILTER_QTY].Value = row["model_list_line_filter_qty"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.MODEL_LIST_MIST_FILTER].Value = row["model_list_mist_filter"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.MODEL_LIST_MIST_FILTER_QTY].Value = row["model_list_mist_filter_qty"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.MODEL_LIST_AIR_TANK].Value = row["model_list_air_tank"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.MODEL_LIST_AIR_TANK_QTY].Value = row["model_list_air_tank_qty"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.MODEL_LIST_OTHER].Value = row["model_list_other"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.MODEL_LIST_OTHER_QTY].Value = row["model_list_other_qty"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.SALES_NAME].Value = row["sales_name"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.PRICE_LIST].Value = row["total_amount"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.TOTAL_DISCOUNT].Value = row["total_discount"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.PRICE].Value = row["grand_total"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.CONTACT_NAME].Value = row["contact_name"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.STATUS_NAME].Value = row["status_name"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.UPDATE_DATE_DD].Value = row["updated_date_dd"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.UPDATE_DATE_MM].Value = row["updated_date_mm"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.UPDATE_DATE_YY].Value = row["updated_date_yy"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.PO_NUMBER].Value = row["po_no"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.PO_AMOUNT].Value = row["amount_po"].ToString(); //workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.CUSTOMER_REQUEST_BY].Value = row["sent_by"].ToString(); //workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.CUSTOMER_REQUEST_DATE_DD].Value = row["customer_request_date"].ToString(); //workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.CUSTOMER_REQUEST_DATE_MM].Value = row["customer_request_month"].ToString(); //workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.CUSTOMER_REQUEST_DATE_YY].Value = row["customer_request_year"].ToString(); //workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.TEST_RUN_DATE_DD].Value = row["test_run_date"].ToString(); //workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.TEST_RUN_DATE_MM].Value = row["test_run_month"].ToString(); //workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.TEST_RUN_DATE_YY].Value = row["test_run_year"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.CUSTOMER_TEL].Value = row["customer_tel"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.CUSTOMER_FAX].Value = row["customer_fax"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.CUSTOMER_EMAIL].Value = row["customer_email"].ToString(); workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.REMARK].Value = row["remark"].ToString(); intStartRow++; } } templateSheet = excel.Workbook.Worksheets["Sheet1"]; excel.Workbook.Worksheets.Delete(templateSheet); //strDownloadUrl = baseUrl + strTemplatePath + strProductTemplateFile; string pathTemp = Path.GetTempFileName(); excel.SaveAs(new FileInfo(pathTemp)); strDownloadUrl = string.Format("LocalPath={0}&FileName={1}", pathTemp, "QuotationSummaryReport_Product.xlsx"); } catch (Exception ex) { string strErrorMsg = SPlanetUtil.LogErrorCollect(ex); } return(strDownloadUrl); }
protected void BindGrid(bool isForceRefreshData = false) { try { if (!Page.IsPostBack || isForceRefreshData) { string search = " "; if (Session["SEARCH"] != null) { search = Session["SEARCH"].ToString(); txtSearchBoxData.Value = search; } using (SqlConnection conn = new SqlConnection(SPlanetUtil.GetConnectionString())) { //Create array of Parameters List <SqlParameter> arrParm = new List <SqlParameter> { new SqlParameter("@search_name", SqlDbType.VarChar) { Value = search }, new SqlParameter("@id", SqlDbType.Int) { Value = 0 }, new SqlParameter("@parent_id", SqlDbType.Int) { Value = parentId }, new SqlParameter("@level_no", SqlDbType.Int) { Value = parentId > 0 ? 1 : 0 } }; conn.Open(); dsResult = SqlHelper.ExecuteDataset(conn, "sp_product_category_list", arrParm.ToArray()); Session["SESSION_PRODUCT_CATEGORY_MASTER"] = dsResult; if (dsResult != null) { /*int rowId = 0; * if (Session["ROW_ID"] != null) * { * rowId = Convert.ToInt32(Session["ROW_ID"].ToString()); * }*/ var row = (from t in dsResult.Tables[0].AsEnumerable() select t).ToList(); foreach (var data in row) { productCatDetail.Add(new ProductCategoryDetail() { id = Convert.ToInt32(data["id"]), cat_type = Convert.IsDBNull(data["cat_type"]) ? null : Convert.ToString(data["cat_type"]), cat_name_tha = Convert.IsDBNull(data["cat_name_tha"]) ? null : Convert.ToString(data["cat_name_tha"]), cat_name_eng = Convert.IsDBNull(data["cat_name_eng"]) ? null : Convert.ToString(data["cat_name_eng"]), parent_name_eng = Convert.IsDBNull(data["parent_name_eng"]) ? null : Convert.ToString(data["parent_name_eng"]), parent_name_tha = Convert.IsDBNull(data["parent_name_tha"]) ? null : Convert.ToString(data["parent_name_tha"]), parent_root_id = Convert.IsDBNull(data["parent_root_id"]) ? 0 : Convert.ToInt32(data["parent_root_id"]), parent_id = Convert.IsDBNull(data["parent_id"]) ? 0 : Convert.ToInt32(data["parent_id"]), level_no = Convert.IsDBNull(data["level_no"]) ? 0 : Convert.ToInt32(data["level_no"]), is_enable = Convert.IsDBNull(data["is_enable"]) ? false : Convert.ToBoolean(data["is_enable"]), }); /*if (rowId == productCatDetail[productCatDetail.Count - 1].id) * { * int selectedRow = productCatDetail.Count - 1; * int prevRow = Convert.ToInt32(Session["ROW"]); * int pageSize = gridView.SettingsPager.PageSize; * int pageIndex = (int)(selectedRow / pageSize); * int prevPageIndex = Convert.ToInt32(Session["PAGE"]); * if (prevRow == selectedRow) * { * Session["PAGE"] = prevPageIndex; * Session["ROW"] = prevPageIndex * pageSize; * } * else * { * Session["PAGE"] = pageIndex; * Session["ROW"] = selectedRow; * } * }*/ } } if (parentId > 0) { var returnData = new { parent_root_id = 0, parent_name_tha = string.Empty }; var dataProductCate = productCatDetail; if (dataProductCate != null) { returnData = (from t in dataProductCate where t.parent_id == Convert.ToInt32(parentId) select new { parent_root_id = t.parent_root_id, parent_name_tha = t.parent_name_tha }).FirstOrDefault(); } if (returnData != null) { var parent_root_id = returnData.parent_root_id; title.InnerText = returnData.parent_name_tha; btn_backPage.Attributes.Add("onclick", "btnback(" + parent_root_id + ")"); } else { } group_breadcrumb.Visible = true; add_NewItem.Visible = false; } HttpContext.Current.Session["SESSION_PRODUCT_CATEGORY_LIST"] = productCatDetail; } } //Bind data into GridView gridView.DataSource = dsResult; //gridView.FilterExpression = FilterBag.GetExpression(false); gridView.DataBind(); // Check page from session if (Session["ROW_ID"] != null) { int row = Convert.ToInt32(Session["ROW"]); gridView.FocusedRowIndex = row; } if (Session["PAGE"] != null) { int page = Convert.ToInt32(Session["PAGE"]); gridView.PageIndex = page; } if (!Page.IsPostBack && !string.IsNullOrEmpty(Session["COLUMN"].ToString()) && !string.IsNullOrEmpty(Session["ORDER"].ToString())) { int order = Convert.ToInt32(Session["ORDER"]); if (order == 1) { ((GridViewDataColumn)gridView.Columns[Session["COLUMN"].ToString()]).SortAscending(); } else { ((GridViewDataColumn)gridView.Columns[Session["COLUMN"].ToString()]).SortDescending(); } } } catch (Exception ex) { string strErrorMsg = SPlanetUtil.LogErrorCollect(ex); ScriptManager.RegisterStartupScript(this, GetType(), "myalert", "alert('" + strErrorMsg + "');", true); } }
public static string ExportAnnualServiceList(AnnualServiceListData[] annualServiceListData) { string strDownloadUrl = string.Empty; try { string strTemplatePath = "/Master/Template/"; string baseUrl = string.Empty; String strExcelPath = string.Empty; string strSummaryQUTemplateFile = "AnnualService-Template.xlsx"; int intStartRow = 2; DataSet dsResult; string excelTemplate = Path.Combine(HttpContext.Current.Server.MapPath(strTemplatePath), strSummaryQUTemplateFile); FileInfo templateFile = new FileInfo(excelTemplate); ExcelPackage excel = new ExcelPackage(templateFile); var templateSheet = excel.Workbook.Worksheets["Sheet1"]; var workSheet = excel.Workbook.Worksheets.Copy("Sheet1", "AnnualService"); workSheet.DeleteRow(5, 1048576 - 5); var data = (from t in annualServiceListData select t).FirstOrDefault(); using (SqlConnection conn = new SqlConnection(SPlanetUtil.GetConnectionString())) { //Create array of Parameters /*List<SqlParameter> arrParm = new List<SqlParameter> * { * new SqlParameter("@start_up_date_from", SqlDbType.VarChar,200) { Value = data.datepickerFrom }, * new SqlParameter("@start_up_date_to", SqlDbType.VarChar,200) { Value = data.datepickerTo }, * new SqlParameter("@starting_date_from", SqlDbType.VarChar,200) { Value = data.starttingdateFrom }, * new SqlParameter("@starting_date_to", SqlDbType.VarChar, 200) { Value = data.starttingdateTo }, * new SqlParameter("@expire_date_from", SqlDbType.VarChar,200) { Value = data.expiredateFrom }, * new SqlParameter("@expire_date_to", SqlDbType.VarChar, 200) { Value = data.expiredateTo }, * new SqlParameter("@schedule_date_from", SqlDbType.VarChar,200) { Value = data.scheduleFrom }, * new SqlParameter("@schedule_date_to", SqlDbType.VarChar, 200) { Value = data.scheduleTo }, * * };*/ List <SqlParameter> arrParm = new List <SqlParameter> { new SqlParameter("@start_up_date_from", SqlDbType.DateTime) { Value = (string.IsNullOrEmpty(data.datepickerFrom) ? (Object)DBNull.Value : DateTime.ParseExact(data.datepickerFrom, "dd/MM/yyyy", CultureInfo.InvariantCulture)) }, new SqlParameter("@start_up_date_to", SqlDbType.DateTime) { Value = (string.IsNullOrEmpty(data.datepickerTo) ? (Object)DBNull.Value : DateTime.ParseExact(data.datepickerTo, "dd/MM/yyyy", CultureInfo.InvariantCulture)) }, new SqlParameter("@starting_date_from", SqlDbType.DateTime) { Value = (string.IsNullOrEmpty(data.starttingdateFrom) ? (Object)DBNull.Value : DateTime.ParseExact(data.starttingdateFrom, "dd/MM/yyyy", CultureInfo.InvariantCulture)) }, new SqlParameter("@starting_date_to", SqlDbType.DateTime) { Value = (string.IsNullOrEmpty(data.starttingdateTo) ? (Object)DBNull.Value : DateTime.ParseExact(data.starttingdateTo, "dd/MM/yyyy", CultureInfo.InvariantCulture)) }, new SqlParameter("@expire_date_from", SqlDbType.DateTime) { Value = (string.IsNullOrEmpty(data.expiredateFrom) ? (Object)DBNull.Value : DateTime.ParseExact(data.expiredateFrom, "dd/MM/yyyy", CultureInfo.InvariantCulture)) }, new SqlParameter("@expire_date_to", SqlDbType.DateTime) { Value = (string.IsNullOrEmpty(data.expiredateTo) ? (Object)DBNull.Value : DateTime.ParseExact(data.expiredateTo, "dd/MM/yyyy", CultureInfo.InvariantCulture)) }, new SqlParameter("@schedule_date_from", SqlDbType.DateTime) { Value = (string.IsNullOrEmpty(data.scheduleFrom) ? (Object)DBNull.Value : DateTime.ParseExact(data.scheduleFrom, "dd/MM/yyyy", CultureInfo.InvariantCulture)) }, new SqlParameter("@schedule_date_to", SqlDbType.DateTime) { Value = (string.IsNullOrEmpty(data.scheduleTo) ? (Object)DBNull.Value : DateTime.ParseExact(data.scheduleTo, "dd/MM/yyyy", CultureInfo.InvariantCulture)) }, new SqlParameter("@customer_id", SqlDbType.Int) { Value = (string.IsNullOrEmpty(data.customerid) ? (Object)DBNull.Value : Convert.ToInt32(data.customerid)) }, new SqlParameter("@projecT_s", SqlDbType.VarChar, 200) { Value = (string.IsNullOrEmpty(data.project_search) ? (Object)DBNull.Value : data.project_search) }, }; conn.Open(); dsResult = SqlHelper.ExecuteDataset(conn, "sp_annual_service_list", arrParm.ToArray()); var i = 0; foreach (DataRow row in dsResult.Tables[0].Rows) { i++; templateSheet.SelectedRange["A5:XFA5"].Copy(workSheet.SelectedRange[intStartRow, 1]); workSheet.Cells[intStartRow, (int)Column_AnnualServiceList.COUNT_CALL].Value = i; workSheet.Cells[intStartRow, (int)Column_AnnualServiceList.CUSTOMER_NAME].Value = row["cust_name"].ToString(); workSheet.Cells[intStartRow, (int)Column_AnnualServiceList.PROJECT].Value = row["project"].ToString(); workSheet.Cells[intStartRow, (int)Column_AnnualServiceList.MODEL].Value = row["model"].ToString(); workSheet.Cells[intStartRow, (int)Column_AnnualServiceList.MFG].Value = row["mfg"].ToString(); workSheet.Cells[intStartRow, (int)Column_AnnualServiceList.SCHEDULE_DATE].Value = row["schedule_date"].ToString(); workSheet.Cells[intStartRow, (int)Column_AnnualServiceList.TEST_RUN_DATE].Value = row["test_run_date"].ToString(); workSheet.Cells[intStartRow, (int)Column_AnnualServiceList.STARTING_DATE].Value = row["starting_date"].ToString(); workSheet.Cells[intStartRow, (int)Column_AnnualServiceList.EXPIRE_DATE].Value = row["expire_date"].ToString(); workSheet.Cells[intStartRow, (int)Column_AnnualServiceList.LOCATION].Value = row["checking_location"].ToString(); workSheet.Cells[intStartRow, (int)Column_AnnualServiceList.REMARK].Value = row["checking_remark"].ToString(); intStartRow++; } } templateSheet = excel.Workbook.Worksheets["Sheet1"]; excel.Workbook.Worksheets.Delete(templateSheet); strDownloadUrl = baseUrl + strTemplatePath + "AnnualService.xlsx"; strDownloadUrl = Path.Combine(HttpContext.Current.Server.MapPath(strTemplatePath), "AnnualService.xlsx"); string pathTemp = Path.GetTempFileName(); excel.SaveAs(new FileInfo(strDownloadUrl)); //excel.SaveAs(new FileInfo(pathTemp)); strDownloadUrl = string.Format("LocalPath={0}&FileName={1}", strDownloadUrl, "AnnualService.xlsx"); } catch (Exception ex) { string strErrorMsg = SPlanetUtil.LogErrorCollect(ex); } return(strDownloadUrl); }
protected void gridView_CustomCallback(object sender, ASPxGridViewCustomCallbackEventArgs e) { //string param = e.Parameters.ToString(); //gridView.DataSource = productCatDetail; //gridView.DataBind(); try { using (SqlConnection conn = new SqlConnection(SPlanetUtil.GetConnectionString())) { Session["SEARCH"] = e.Parameters.ToString(); //Create array of Parameters List <SqlParameter> arrParm = new List <SqlParameter> { new SqlParameter("@search_name", SqlDbType.VarChar, 200) { Value = e.Parameters.ToString() }, new SqlParameter("@id", SqlDbType.Int) { Value = 0 }, new SqlParameter("@parent_id", SqlDbType.Int) { Value = parentId }, new SqlParameter("@level_no", SqlDbType.Int) { Value = parentId > 0 ? 1 : 0 } }; conn.Open(); dsResult = SqlHelper.ExecuteDataset(conn, "sp_product_category_list", arrParm.ToArray()); Session["SESSION_PRODUCT_CATEGORY_MASTER"] = dsResult; if (dsResult != null) { var row = (from t in dsResult.Tables[0].AsEnumerable() select t).ToList(); foreach (var data in row) { productCatDetail.Add(new ProductCategoryDetail() { id = Convert.ToInt32(data["id"]), cat_type = Convert.IsDBNull(data["cat_type"]) ? null : Convert.ToString(data["cat_type"]), cat_name_tha = Convert.IsDBNull(data["cat_name_tha"]) ? null : Convert.ToString(data["cat_name_tha"]), cat_name_eng = Convert.IsDBNull(data["cat_name_eng"]) ? null : Convert.ToString(data["cat_name_eng"]), parent_name_eng = Convert.IsDBNull(data["parent_name_eng"]) ? null : Convert.ToString(data["parent_name_eng"]), parent_name_tha = Convert.IsDBNull(data["parent_name_tha"]) ? null : Convert.ToString(data["parent_name_tha"]), parent_root_id = Convert.IsDBNull(data["parent_root_id"]) ? 0 : Convert.ToInt32(data["parent_root_id"]), parent_id = Convert.IsDBNull(data["parent_id"]) ? 0 : Convert.ToInt32(data["parent_id"]), level_no = Convert.IsDBNull(data["level_no"]) ? 0 : Convert.ToInt32(data["level_no"]), is_enable = Convert.IsDBNull(data["is_enable"]) ? false : Convert.ToBoolean(data["is_enable"]), }); } } if (parentId > 0) { var returnData = new { parent_root_id = 0, parent_name_tha = string.Empty }; var dataProductCate = productCatDetail; if (dataProductCate != null) { returnData = (from t in dataProductCate where t.parent_id == Convert.ToInt32(parentId) select new { parent_root_id = t.parent_root_id, parent_name_tha = t.parent_name_tha }).FirstOrDefault(); } if (returnData != null) { var parent_root_id = returnData.parent_root_id; title.InnerText = returnData.parent_name_tha; btn_backPage.Attributes.Add("onclick", "btnback(" + parent_root_id + ")"); } else { } group_breadcrumb.Visible = true; add_NewItem.Visible = false; } HttpContext.Current.Session["SESSION_PRODUCT_CATEGORY_LIST"] = productCatDetail; } //Bind data into GridView gridView.DataSource = dsResult; //gridView.FilterExpression = FilterBag.GetExpression(false); gridView.DataBind(); } catch (Exception ex) { string strErrorMsg = SPlanetUtil.LogErrorCollect(ex); ScriptManager.RegisterStartupScript(this, GetType(), "myalert", "alert('" + strErrorMsg + "');", true); } }
private void Pivot_Quotation_Summary_Spare_Part() { try { if (!Page.IsPostBack) { using (SqlConnection conn = new SqlConnection(SPlanetUtil.GetConnectionString())) { //Create array of Parameters List <SqlParameter> arrParm = new List <SqlParameter> { new SqlParameter("@quotation_date_from", SqlDbType.VarChar, 20) { Value = "" }, new SqlParameter("@quotation_date_to", SqlDbType.VarChar, 20) { Value = "" } }; conn.Open(); dsPivot = SqlHelper.ExecuteDataset(conn, "sp_report_quotation_summary_spare_part", arrParm.ToArray()); ViewState["dsPivot"] = dsPivot; conn.Close(); } pivotGridControl.RetrieveFields(PivotArea.FilterArea, false); #region "Create PivotGrid Field" PivotGridField fieldQuotationNo = new PivotGridField("quotation_no", PivotArea.RowArea); fieldQuotationNo.Caption = "Quotation No"; fieldQuotationNo.Visible = false; PivotGridField fieldQuotationDate_Month = new PivotGridField("quotation_date", PivotArea.ColumnArea); fieldQuotationDate_Month.Caption = "Quotation Date (Month)"; fieldQuotationDate_Month.Visible = true; //fieldQuotationDate_Month.GroupInterval = PivotGroupInterval.DateMonth; PivotGridField fieldQuotationDate_Quarter = new PivotGridField("quotation_date", PivotArea.ColumnArea); fieldQuotationDate_Quarter.Caption = "Quotation Date (Quarter)"; fieldQuotationDate_Quarter.Visible = false; //fieldQuotationDate_Quarter.GroupInterval = PivotGroupInterval.DateQuarterYear; PivotGridField fieldCustomerName = new PivotGridField("customer_name", PivotArea.RowArea); fieldCustomerName.Caption = "Customer Name"; fieldCustomerName.Visible = true; PivotGridField fieldH = new PivotGridField("h", PivotArea.RowArea); fieldH.Caption = "H"; fieldH.Visible = true; PivotGridField fieldModel = new PivotGridField("model", PivotArea.RowArea); fieldModel.Caption = "MODEL"; fieldModel.Visible = false; PivotGridField fieldMFG = new PivotGridField("mfg", PivotArea.FilterArea); fieldMFG.Caption = "MFG NO"; fieldMFG.Visible = true; PivotGridField fieldMachine = new PivotGridField("machine", PivotArea.RowArea); fieldMachine.Caption = "MACHIN NO"; fieldMachine.Visible = false; PivotGridField fieldPressure = new PivotGridField("pressure", PivotArea.RowArea); fieldPressure.Caption = "PRESSURE"; fieldPressure.Visible = false; PivotGridField fieldDetailOfPart = new PivotGridField("detail_of_part", PivotArea.DataArea); fieldDetailOfPart.Caption = "DETAIL OF PART"; fieldDetailOfPart.Visible = true; PivotGridField fieldDiscount1 = new PivotGridField("Discount1", PivotArea.RowArea); fieldDiscount1.Caption = "DISCOUNT1"; fieldDiscount1.Visible = false; PivotGridField fieldDiscount2 = new PivotGridField("Discount2", PivotArea.DataArea); fieldDiscount2.Caption = "Discount2"; fieldDiscount2.Visible = true; PivotGridField fieldamount = new PivotGridField("amount", PivotArea.RowArea); fieldamount.Caption = "AMOUNT"; fieldamount.Visible = false; PivotGridField fieldContactName = new PivotGridField("contact_name", PivotArea.DataArea); fieldContactName.Caption = "CONTACT"; fieldContactName.Visible = true; PivotGridField fieldQuStatus = new PivotGridField("qu_status", PivotArea.RowArea); fieldQuStatus.Caption = "DETAIL STATUS"; fieldQuStatus.Visible = false; PivotGridField fieldStatusDateDD = new PivotGridField("status_date_DD", PivotArea.DataArea); fieldStatusDateDD.Caption = "DETAIL DD"; fieldStatusDateDD.Visible = true; PivotGridField fieldStatusDateMM = new PivotGridField("status_date_MM", PivotArea.RowArea); fieldStatusDateMM.Caption = "DETAIL MM"; fieldStatusDateMM.Visible = false; PivotGridField fieldStatusDateYY = new PivotGridField("status_date_YY", PivotArea.DataArea); fieldStatusDateYY.Caption = "DETAIL YY"; fieldStatusDateYY.Visible = true; PivotGridField fieldPoNo = new PivotGridField("po_no", PivotArea.RowArea); fieldPoNo.Caption = "PO NO"; fieldPoNo.Visible = false; PivotGridField fieldAmountNo = new PivotGridField("amount_no", PivotArea.DataArea); fieldAmountNo.Caption = "AMOUNT NO"; fieldAmountNo.Visible = true; PivotGridField fieldSalesOrderDate = new PivotGridField("sales_order_date", PivotArea.RowArea); fieldSalesOrderDate.Caption = "SALE ORDER DATE"; fieldSalesOrderDate.Visible = false; PivotGridField fieldDeliveryDate = new PivotGridField("delivery_date", PivotArea.DataArea); fieldDeliveryDate.Caption = "DELIVERY DATE"; fieldDeliveryDate.Visible = false; PivotGridField fieldInvNo = new PivotGridField("inv_no", PivotArea.DataArea); fieldInvNo.Caption = "INV NO"; fieldInvNo.Visible = false; PivotGridField fieldAllStatus = new PivotGridField("all_status", PivotArea.DataArea); fieldAllStatus.Caption = "STATUS"; fieldAllStatus.Visible = true; #endregion #region " Add fields to Pivot Control" // Add the fields to the control's field collection. pivotGridControl.Fields.Add(fieldQuotationNo); pivotGridControl.Fields.Add(fieldQuotationDate_Month); pivotGridControl.Fields.Add(fieldQuotationDate_Quarter); pivotGridControl.Fields.Add(fieldCustomerName); pivotGridControl.Fields.Add(fieldH); pivotGridControl.Fields.Add(fieldModel); pivotGridControl.Fields.Add(fieldMFG); pivotGridControl.Fields.Add(fieldMachine); pivotGridControl.Fields.Add(fieldPressure); pivotGridControl.Fields.Add(fieldDetailOfPart); pivotGridControl.Fields.Add(fieldDiscount1); pivotGridControl.Fields.Add(fieldDiscount2); pivotGridControl.Fields.Add(fieldamount); pivotGridControl.Fields.Add(fieldContactName); pivotGridControl.Fields.Add(fieldQuStatus); pivotGridControl.Fields.Add(fieldStatusDateDD); pivotGridControl.Fields.Add(fieldStatusDateMM); pivotGridControl.Fields.Add(fieldStatusDateYY); pivotGridControl.Fields.Add(fieldPoNo); pivotGridControl.Fields.Add(fieldAmountNo); pivotGridControl.Fields.Add(fieldSalesOrderDate); pivotGridControl.Fields.Add(fieldDeliveryDate); pivotGridControl.Fields.Add(fieldInvNo); pivotGridControl.Fields.Add(fieldAllStatus); #endregion // Arrange the row fields within the Row Header Area. เลือกข้อมูลแถวที่แสดงตรงส่วนหัวของแถว fieldQuotationNo.AreaIndex = 0; fieldCustomerName.AreaIndex = 1; // Arrange the row fields within the Column Header Area.เลือกข้อมูลคอลัมที่แสดงตรงส่วนหัวของแถว fieldDetailOfPart.AreaIndex = 0; fieldamount.AreaIndex = 1; fieldQuStatus.AreaIndex = 2; fieldStatusDateDD.AreaIndex = 3; fieldStatusDateMM.AreaIndex = 4; fieldStatusDateYY.AreaIndex = 5; fieldPoNo.AreaIndex = 6; } else { dsPivot = (DataSet)ViewState["dsPivot"]; } pivotGridControl.DataSource = dsPivot; pivotGridControl.DataBind(); } catch (Exception ex) { string strErrorMsg = SPlanetUtil.LogErrorCollect(ex); ScriptManager.RegisterStartupScript(this, GetType(), "myalert", "alert('" + strErrorMsg + "');", true); } }