public void ExportLocationDetailXLS(string locationshidden, string itemshidden, bool isSapLocationhidden, bool isShowCSSupplierhidden) { DataTable locationArrayTable = new DataTable("LocationArrayTable"); locationArrayTable.Columns.Add("Field", typeof(string)); if (!string.IsNullOrEmpty(locationshidden)) { string loctions = locationshidden.Replace("\r\n", ","); loctions = loctions.Replace("\n", ","); string[] locationArr = loctions.Split(','); for (int i = 0; i < locationArr.Length; i++) { if (!string.IsNullOrWhiteSpace(locationArr[i]) && locationArr[i] != "null" && locationArr[i] != "NULL") { locationArrayTable.Rows.Add(locationArr[i]); } } } DataTable itemArrayTable = new DataTable("ItemArrayTable"); itemArrayTable.Columns.Add("Field", typeof(string)); if (!string.IsNullOrEmpty(itemshidden)) { string items = itemshidden.Replace("\r\n", ","); items = items.Replace("\n", ","); string[] itemArr = items.Split(','); for (int i = 0; i < itemArr.Length; i++) { if (!string.IsNullOrWhiteSpace(itemArr[i]) && itemArr[i] != "null" && itemArr[i] != "NULL") { itemArrayTable.Rows.Add(itemArr[i]); } } } SqlParameter[] parameters = new SqlParameter[10]; parameters[0] = new SqlParameter("@LocationArrayTable", System.Data.SqlDbType.Structured); parameters[0].Value = locationArrayTable; parameters[1] = new SqlParameter("@IsSapLocation", System.Data.SqlDbType.Bit); parameters[1].Value = isSapLocationhidden; parameters[2] = new SqlParameter("@ItemArrayTable", System.Data.SqlDbType.Structured); parameters[2].Value = itemArrayTable; parameters[3] = new SqlParameter("@IsShowCSSupplier", System.Data.SqlDbType.Bit); parameters[3].Value = isShowCSSupplierhidden; parameters[4] = new SqlParameter("@SortCloumn", System.Data.SqlDbType.VarChar,50); parameters[4].Value =string.Empty; parameters[5] = new SqlParameter("@SortRule", System.Data.SqlDbType.VarChar, 50); parameters[5].Value =string.Empty; parameters[6] = new SqlParameter("@PageSize", SqlDbType.Int); parameters[6].Value = 65530; parameters[7] = new SqlParameter("@Pager", SqlDbType.Int); parameters[7].Value = 1; parameters[8] = new SqlParameter("@UserId", SqlDbType.Int); parameters[8].Value = CurrentUser.Id; parameters[9] = new SqlParameter("@RowCount", System.Data.SqlDbType.VarChar, 50); parameters[9].Direction = ParameterDirection.Output; IList<LocationDetailView> locationDetailView = new List<LocationDetailView>(); try { DataSet dataSet = sqlDao.GetDatasetByStoredProcedure("USP_Search_LocationLotDet", parameters, false); //Item, Desc1, RefCode, Uom, Location, Qty, CSQty, QulifiedQty, InspectedQty, RejectedQty //Item, Desc1, RefCode, Uom, Location, Qty, CSSupplier, QulifiedQty, InspectedQty, RejectedQty if (dataSet.Tables[0] != null && dataSet.Tables[0].Rows.Count > 0) { foreach (DataRow row in dataSet.Tables[0].Rows) { //row.ItemArray[0].ToString() LocationDetailView lotDet = new LocationDetailView(); lotDet.Item = row.ItemArray[0].ToString(); lotDet.ItemDescription = row.ItemArray[1].ToString(); lotDet.RefrenceItemCode = row.ItemArray[2].ToString(); lotDet.Uom = row.ItemArray[3].ToString(); lotDet.Location = row.ItemArray[4].ToString(); lotDet.Qty = Convert.ToDecimal(row.ItemArray[5]); if (isShowCSSupplierhidden) { lotDet.suppliers = row.ItemArray[6].ToString(); } else { lotDet.ConsignmentQty = Convert.ToDecimal(row.ItemArray[6]); } lotDet.QualifyQty = Convert.ToDecimal(row.ItemArray[7]); lotDet.InspectQty = Convert.ToDecimal(row.ItemArray[8]); lotDet.RejectQty = Convert.ToDecimal(row.ItemArray[9]); locationDetailView.Add(lotDet); } } ExportToXLS<LocationDetailView>("ExportLocationDetailXLS", "xls", locationDetailView); } catch (BusinessException be) { SaveBusinessExceptionMessage(be); } catch (Exception ex) { if (ex.InnerException != null) { if (ex.InnerException.InnerException != null) { SaveErrorMessage(ex.InnerException.InnerException.Message); } else { SaveErrorMessage(ex.InnerException.Message); } } else { SaveErrorMessage(ex.Message); } } }
public ActionResult _AjaxCopyList(GridCommand command, LocationLotDetailSearchModel searchModel) { DataTable locationArrayTable = new DataTable("LocationArrayTable"); locationArrayTable.Columns.Add("Field", typeof(string)); if (!string.IsNullOrEmpty(searchModel.locations)) { string loctions = searchModel.locations.Replace("\r\n", ","); loctions = loctions.Replace("\n", ","); string[] locationArr = loctions.Split(','); for (int i = 0; i < locationArr.Length; i++) { if (!string.IsNullOrWhiteSpace(locationArr[i]) && locationArr[i] != "null" && locationArr[i] != "NULL") { locationArrayTable.Rows.Add(locationArr[i]); } } } DataTable itemArrayTable = new DataTable("ItemArrayTable"); itemArrayTable.Columns.Add("Field", typeof(string)); if (!string.IsNullOrEmpty(searchModel.items)) { string items = searchModel.items.Replace("\r\n", ","); items = items.Replace("\n", ","); string[] itemArr = items.Split(','); for (int i = 0; i < itemArr.Length; i++) { if (!string.IsNullOrWhiteSpace(itemArr[i]) && itemArr[i] != "null" && itemArr[i] != "NULL") { itemArrayTable.Rows.Add(itemArr[i]); } } } SqlParameter[] parameters = new SqlParameter[10]; parameters[0] = new SqlParameter("@LocationArrayTable", System.Data.SqlDbType.Structured); parameters[0].Value = locationArrayTable; parameters[1] = new SqlParameter("@IsSapLocation", System.Data.SqlDbType.Bit); parameters[1].Value = searchModel.IsSapLocation; parameters[2] = new SqlParameter("@ItemArrayTable", System.Data.SqlDbType.Structured); parameters[2].Value = itemArrayTable; parameters[3] = new SqlParameter("@IsShowCSSupplier", System.Data.SqlDbType.Bit); parameters[3].Value = searchModel.IsShowCSSupplier; parameters[4] = new SqlParameter("@SortCloumn", System.Data.SqlDbType.VarChar,50); parameters[4].Value =command.SortDescriptors.Count>0? command.SortDescriptors[0].Member:string.Empty; parameters[5] = new SqlParameter("@SortRule", System.Data.SqlDbType.VarChar, 50); parameters[5].Value = command.SortDescriptors.Count > 0 ? command.SortDescriptors[0].SortDirection == ListSortDirection.Descending ? "desc" : "asc":string.Empty; parameters[6] = new SqlParameter("@PageSize", SqlDbType.Int); parameters[6].Value = command.PageSize; parameters[7] = new SqlParameter("@Pager", SqlDbType.Int); parameters[7].Value = command.Page; parameters[8] = new SqlParameter("@UserId", SqlDbType.Int); parameters[8].Value = CurrentUser.Id; parameters[9] = new SqlParameter("@RowCount", System.Data.SqlDbType.VarChar, 50); parameters[9].Direction = ParameterDirection.Output; IList<LocationDetailView> locationDetailView = new List<LocationDetailView>(); try { DataSet dataSet = sqlDao.GetDatasetByStoredProcedure("USP_Search_LocationLotDet", parameters, false); //Item, Desc1, RefCode, Uom, Location, Qty, CSQty, QulifiedQty, InspectedQty, RejectedQty //Item, Desc1, RefCode, Uom, Location, Qty, CSSupplier, QulifiedQty, InspectedQty, RejectedQty if (dataSet.Tables[0] != null && dataSet.Tables[0].Rows.Count > 0) { foreach (DataRow row in dataSet.Tables[0].Rows) { //row.ItemArray[0].ToString() LocationDetailView lotDet = new LocationDetailView(); lotDet.Item = row.ItemArray[0].ToString(); lotDet.ItemDescription = row.ItemArray[1].ToString(); lotDet.RefrenceItemCode = row.ItemArray[2].ToString(); lotDet.Uom = row.ItemArray[3].ToString(); lotDet.Location = row.ItemArray[4].ToString(); lotDet.Qty = Convert.ToDecimal(row.ItemArray[5]); if (searchModel.IsShowCSSupplier) { lotDet.suppliers = row.ItemArray[6].ToString(); } else { lotDet.ConsignmentQty = Convert.ToDecimal(row.ItemArray[6]); } lotDet.QualifyQty = Convert.ToDecimal(row.ItemArray[7]); lotDet.InspectQty = Convert.ToDecimal(row.ItemArray[8]); lotDet.RejectQty = Convert.ToDecimal(row.ItemArray[9]); locationDetailView.Add(lotDet); } } } catch (BusinessException be) { SaveBusinessExceptionMessage(be); } catch (Exception ex) { if (ex.InnerException != null) { if (ex.InnerException.InnerException != null) { SaveErrorMessage(ex.InnerException.InnerException.Message); } else { SaveErrorMessage(ex.InnerException.Message); } } else { SaveErrorMessage(ex.Message); } } GridModel<LocationDetailView> gridModel = new GridModel<LocationDetailView>(); gridModel.Total = string.IsNullOrWhiteSpace(parameters[9].Value.ToString()) ? 0 : Convert.ToInt32(parameters[9].Value); gridModel.Data = locationDetailView; return PartialView(gridModel); }
public IList<LocationDetailView> _GetLocationDetailView(IList<string> itemCodes) { DataTable locationArrayTable = new DataTable("LocationArrayTable"); locationArrayTable.Columns.Add("Field", typeof(string)); DataTable itemArrayTable = new DataTable("ItemArrayTable"); itemArrayTable.Columns.Add("Field", typeof(string)); if (itemCodes != null && itemCodes.Count > 0) { for (int i = 0; i < itemCodes.Count; i++) { itemArrayTable.Rows.Add(itemCodes[i]); } } SqlParameter[] parameters = new SqlParameter[10]; parameters[0] = new SqlParameter("@LocationArrayTable", System.Data.SqlDbType.Structured); parameters[0].Value = locationArrayTable; parameters[1] = new SqlParameter("@IsSapLocation", System.Data.SqlDbType.Bit); parameters[1].Value = false; parameters[2] = new SqlParameter("@ItemArrayTable", System.Data.SqlDbType.Structured); parameters[2].Value = itemArrayTable; parameters[3] = new SqlParameter("@IsShowCSSupplier", System.Data.SqlDbType.Bit); parameters[3].Value = false; parameters[4] = new SqlParameter("@SortCloumn", System.Data.SqlDbType.VarChar, 50); parameters[4].Value = string.Empty; parameters[5] = new SqlParameter("@SortRule", System.Data.SqlDbType.VarChar, 50); parameters[5].Value =string.Empty; parameters[6] = new SqlParameter("@PageSize", SqlDbType.Int); parameters[6].Value = 10000; parameters[7] = new SqlParameter("@Pager", SqlDbType.Int); parameters[7].Value = 1; parameters[8] = new SqlParameter("@UserId", SqlDbType.Int); parameters[8].Value = CurrentUser.Id; parameters[9] = new SqlParameter("@RowCount", System.Data.SqlDbType.VarChar, 50); parameters[9].Direction = ParameterDirection.Output; IList<LocationDetailView> locationDetailView = new List<LocationDetailView>(); try { DataSet dataSet = sqlDao.GetDatasetByStoredProcedure("USP_Search_LocationLotDet", parameters, false); //Item, Desc1, RefCode, Uom, Location, Qty, CSQty, QulifiedQty, InspectedQty, RejectedQty //Item, Desc1, RefCode, Uom, Location, Qty, CSSupplier, QulifiedQty, InspectedQty, RejectedQty if (dataSet.Tables[0] != null && dataSet.Tables[0].Rows.Count > 0) { foreach (DataRow row in dataSet.Tables[0].Rows) { //row.ItemArray[0].ToString() LocationDetailView lotDet = new LocationDetailView(); lotDet.Item = row.ItemArray[0].ToString(); lotDet.ItemDescription = row.ItemArray[1].ToString(); lotDet.RefrenceItemCode = row.ItemArray[2].ToString(); lotDet.Uom = row.ItemArray[3].ToString(); lotDet.Location = row.ItemArray[4].ToString(); lotDet.Qty = Convert.ToDecimal(row.ItemArray[5]); //if (searchModel.IsShowCSSupplier) //{ // lotDet.suppliers = row.ItemArray[6].ToString(); //} //else //{ // lotDet.ConsignmentQty = Convert.ToDecimal(row.ItemArray[6]); //} lotDet.QualifyQty = Convert.ToDecimal(row.ItemArray[7]); lotDet.InspectQty = Convert.ToDecimal(row.ItemArray[8]); lotDet.RejectQty = Convert.ToDecimal(row.ItemArray[9]); locationDetailView.Add(lotDet); } } } catch (BusinessException be) { SaveBusinessExceptionMessage(be); } catch (Exception ex) { if (ex.InnerException != null) { if (ex.InnerException.InnerException != null) { SaveErrorMessage(ex.InnerException.InnerException.Message); } else { SaveErrorMessage(ex.InnerException.Message); } } else { SaveErrorMessage(ex.Message); } } return locationDetailView; }