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