/// <summary> /// 取得布種清單 /// </summary> /// <returns></returns> public IEnumerable <Fabric> GetFabricList() { string sqlCmd = "SELECT * FROM Fabric"; var result = DapperHelper.QueryCollection <Fabric>(AppSettingConfig.ConnectionString(), CommandType.Text, sqlCmd); return(result); }
public int UpdateProcessOrderColorDetailStatusByLastComplete(int orderFlowNo, IEnumerable <int> orderColorDetailNoList) { var sqlCmd = @" DECLARE @lastOrderFlowNo INT SELECT TOP 1 @lastOrderFlowNo = OrderFlowNo FROM ProcessOrderFlowDate WHERE OrderColorDetailNo = @OrderColorDetailNo ORDER BY OrderFlowNo DESC IF(@lastOrderFlowNo = @OrderFlowNo) begin UPDATE ProcessOrderColorDetail SET Status = 5, UpdateDate = GETDATE() WHERE OrderColorDetailNo IN @OrderColorDetailNoList end"; var parameter = new { OrderColorDetailNo = orderColorDetailNoList.First(), OrderFlowNo = orderFlowNo, OrderColorDetailNoList = orderColorDetailNoList }; var count = DapperHelper.Execute(AppSettingConfig.ConnectionString(), CommandType.Text, sqlCmd, parameter); return(count); }
/// <summary> /// 取得所有紗商的紗價 /// </summary> /// <returns></returns> public IEnumerable <MerchantYarnPrice> GetMerchantYarnPriceList() { string sqlCmd = @"SELECT * FROM YarnPrice"; var result = DapperHelper.QueryCollection <MerchantYarnPrice>(AppSettingConfig.ConnectionString(), CommandType.Text, sqlCmd); return(result); }
/// <summary> /// 新增加工程序 /// </summary> /// <param name="processSequenceDetails"></param> /// <returns></returns> public List <int> InsertProcessSequence(List <ProcessSequenceDetail> processSequenceDetails) { string sqlCmd = @" INSERT INTO [dbo].[ProcessSequence] ([FabricID] ,[FactoryID] ,[ProcessItem] ,[Loss] ,[WorkPay] ,[Order] ,[Group]) OUTPUT INSERTED.SequenceNo VALUES (@FabricID ,@FactoryID ,@ProcessItem ,@Loss ,@WorkPay ,@Order ,@Group)"; List <int> sequenceNoList = new List <int>(); foreach (var item in processSequenceDetails) { SqlParameter[] parameters = new SqlParameter[] { new SqlParameter("@FabricID", SqlDbType.Int) { Value = item.FabricID }, new SqlParameter("@FactoryID", SqlDbType.Int) { Value = item.FactoryID }, new SqlParameter("@ProcessItem", SqlDbType.TinyInt) { Value = item.ProcessItem }, new SqlParameter("@Loss", SqlDbType.Decimal) { Value = item.Loss }, new SqlParameter("@WorkPay", SqlDbType.SmallInt) { Value = item.WorkPay }, new SqlParameter("@Order", SqlDbType.TinyInt) { Value = item.Order }, new SqlParameter("@Group", SqlDbType.TinyInt) { Value = item.Group } }; sequenceNoList.Add(DapperHelper.Query <int>(AppSettingConfig.ConnectionString(), CommandType.Text, sqlCmd, parameters)); } return(sequenceNoList); }
public int UpdateProcessOrderFlowDate(List <ProcessOrderFlowFactoryName> processOrderFlow) { string sql = @"UPDATE ProcessOrderFlow SET InputDate = @InputDate,CompleteDate = @CompleteDate WHERE OrderDetailNo = @OrderDetailNo;"; var count = DapperHelper.Execute(AppSettingConfig.ConnectionString(), CommandType.Text, sql, processOrderFlow); return(count); }
/// <summary> /// 修改加工程序,可修改工繳,損耗,順序 /// </summary> /// <param name="sequenceNo"></param> /// <param name="loss"></param> /// <param name="workPay"></param> /// <param name="order"></param> /// <returns></returns> public int EditProcessSequence(int sequenceNo, decimal loss, int workPay, int order) { var sqlCmd = @"UPDATE [dbo].[ProcessSequence] SET [Loss] = @Loss ,[WorkPay] = @WorkPay ,[Order] = @Order WHERE SequenceNo=@SequenceNo"; SqlParameter[] parameters = new SqlParameter[] { new SqlParameter("@SequenceNo", SqlDbType.Int) { Value = sequenceNo }, new SqlParameter("@Loss", SqlDbType.Decimal) { Value = loss }, new SqlParameter("@WorkPay", SqlDbType.Int) { Value = workPay }, new SqlParameter("@Order", SqlDbType.Int) { Value = order } }; var result = DapperHelper.ExecuteParameter(AppSettingConfig.ConnectionString(), CommandType.Text, sqlCmd, parameters); return(result); }
/// <summary> /// 更新客戶布種單價 /// </summary> /// <returns></returns> public int UpdateCustomerTextilePrice(CustomerCheckBillSheet selectedCustomerCheckBillSheet, int updateCustomerPrice) { string sql = @"UPDATE dbo.CustomerTextilePrice SET Price = @Price WHERE AccountCustomerID = @AccountCustomerID AND AccountTextileID = @AccountTextileID"; SqlParameter[] parameters = new SqlParameter[] { new SqlParameter("@AccountCustomerID", SqlDbType.NChar) { Value = selectedCustomerCheckBillSheet.C_01 }, new SqlParameter("@AccountTextileID", SqlDbType.Int) { Value = selectedCustomerCheckBillSheet.AccountTextileID }, new SqlParameter("@Price", SqlDbType.Int) { Value = updateCustomerPrice } }; var result = DapperHelper.ExecuteParameter(AppSettingConfig.ConnectionString(), CommandType.Text, sql, parameters); return(result); }
/// <summary> /// 取得所有布種預設單價 /// </summary> /// <returns></returns> public IEnumerable <AccountTextile> GetAccountTextile() { string sql = @"SELECT * FROM AccountTextile;"; var result = DapperHelper.QueryCollection <AccountTextile>(AppSettingConfig.ConnectionString(), CommandType.Text, sql); return(result); }
/// <summary> /// 取得客戶名稱清單 /// </summary> /// <returns></returns> public IEnumerable <CustomerName> GetCustomerNameList() { var sqlCmd = "SELECT Name FROM Customer"; var result = DapperHelper.QueryCollection <CustomerName>(AppSettingConfig.ConnectionString(), CommandType.Text, sqlCmd); return(result); }
/// <summary> /// 新增紗規格 /// </summary> /// <param name="yarnSpecification"></param> /// <returns></returns> public int AddYarnSpecification(YarnSpecification yarnSpecification) { string sqlCmd = @"INSERT INTO [dbo].[YarnSpecification] ([Ingredient],[YarnCount],[Color]) VALUES (@Ingredient,@YarnCount,@Color)"; SqlParameter[] parameters = new SqlParameter[] { new SqlParameter("@Ingredient", SqlDbType.NVarChar) { Value = yarnSpecification.Ingredient }, new SqlParameter("@YarnCount", SqlDbType.NVarChar) { Value = yarnSpecification.YarnCount }, new SqlParameter("@Color", SqlDbType.NVarChar) { Value = yarnSpecification.Color } }; var result = DapperHelper.ExecuteParameter(AppSettingConfig.ConnectionString(), CommandType.Text, sqlCmd, parameters); return(result); }
public int NewProcessOrderFlow(ProcessOrderFlow processOrderFlow, IEnumerable <int> orderColorDetailNo) { var sqlCmd = @"INSERT INTO ProcessOrderFlow OUTPUT inserted.OrderDetailNo VALUES (@OrderNo,@FactoryID);"; SqlParameter[] parameter = new SqlParameter[] { new SqlParameter("@OrderNo", SqlDbType.Int) { Value = processOrderFlow.OrderNo }, new SqlParameter("@FactoryID", SqlDbType.Int) { Value = processOrderFlow.FactoryID } }; var orderDetailNo = DapperHelper.Query <int>(AppSettingConfig.ConnectionString(), CommandType.Text, sqlCmd, parameter); var cmd = @"INSERT INTO ProcessOrderFlowDate VALUES(@OrderColorDetailNo,@OrderDetailNo,null,null,GETDATE());"; var parameters = orderColorDetailNo.Select(s => new { OrderColorDetailNo = s, OrderDetailNo = orderDetailNo, }); var count = DapperHelper.Execute(AppSettingConfig.ConnectionString(), CommandType.Text, cmd, parameters); return(count); }
public int InsertProcessOrderFlowDate(List <ProcessOrderFlowDate> proecessOrderFlowDateList) { string sql = @"INSERT INTO ProcessOrderFlowDate VALUES (@OrderColorDetailNo,@OrderFlowNo,@InputDate,@CompleteDate,@UpdateDate);"; return(DapperHelper.Execute(AppSettingConfig.ConnectionString(), CommandType.Text, sql, proecessOrderFlowDateList)); }
/// <summary> /// 依據多個加工訂單顏色明細編號號取得工廠直送清單 /// </summary> /// <param name="processOrderColorDetailNo"></param> /// <returns></returns> public IEnumerable <FactoryShippingName> GetFactoryShippingNameList(IEnumerable <int> processOrderColorDetailNo) { var sqlCmd = @"select fs.ShippingNo,fs.OrderColorDetailNo,c.Name,fs.Quantity,fs.CreateDate,fs.ShippingDate from FactoryShipping fs inner join Customer c on c.CustomerID = fs.CustomerID where OrderColorDetailNo IN @OrderColorDetailNo"; var parameter = (new { OrderColorDetailNo = processOrderColorDetailNo }); return(DapperHelper.QueryCollection <FactoryShippingName, object>(AppSettingConfig.ConnectionString(), CommandType.Text, sqlCmd, parameter)); }
public int InsertProcessOrderFlow(List <ProcessOrderFlow> processOrderFlow) { string sql = @"INSERT INTO ProcessOrderFlow VALUES (@OrderNo,@FactoryID);"; var result = DapperHelper.Execute(AppSettingConfig.ConnectionString(), CommandType.Text, sql, processOrderFlow); return(result); }
/// <summary> /// 新增工廠清單 /// </summary> /// <param name="factory"></param> /// <returns></returns> public int InsertFactory(Factory factory) { var sqlCmd = @"INSERT INTO [dbo].[Factory] VALUES (@Name,@PhoneNumber,@CellPhone,@Fax,@Process,@Address,@Sort)"; var result = DapperHelper.Execute(AppSettingConfig.ConnectionString(), CommandType.Text, sqlCmd, factory); return(result); }
/// <summary> /// 取得工廠編號 /// </summary> /// <param name="FactoryName"></param> /// <returns></returns> public IEnumerable <FactoryIdentity> GetFactoryIdentiys(string[] FactoryNameString) { var sqlCmd = "SELECT FactoryID,Name FROM Factory WHERE Name IN @Name"; var parameter = (new { Name = FactoryNameString }); //var y = (new { Name = new[] { "強金利", "祥玉" } }); var result = DapperHelper.QueryCollection <FactoryIdentity, object>(AppSettingConfig.ConnectionString(), CommandType.Text, sqlCmd, parameter); return(result); }
/// <summary> /// 新增布種 /// </summary> /// <param name="fabric"></param> /// <returns></returns> public int AddFabric(Fabric fabric) { var sqlCmd = @"INSERT INTO [dbo].[Fabric] VALUES (@FabricName,@AverageUnitPrice,@AverageCost,@UpdateDate)"; var result = DapperHelper.Execute(AppSettingConfig.ConnectionString(), CommandType.Text, sqlCmd, fabric); return(result); }
/// <summary> /// 新增布種顏色的成分比例 /// </summary> /// <param name="colorNo"></param> /// <param name="list"></param> /// <returns></returns> public int InsertFabricIngredientProportions(IEnumerable <FabricProportion> fabricProportions) { var sqlCmd = @"INSERT INTO [dbo].[FabricProportion] ([ColorNo],[YarnPriceNo],[Proportion],[Group],[CreateDate]) VALUES (@ColorNo,@YarnPriceNo,@Proportion,@Group,@CreateDate)"; var result = DapperHelper.Execute(AppSettingConfig.ConnectionString(), CommandType.Text, sqlCmd, fabricProportions); return(result); }
/// <summary> /// 新增布種顏色 /// </summary> /// <param name="fabricColors"></param> public int AddFabricColorList(List <FabricColor> fabricColors) { string sqlCmd = @"INSERT INTO FabricColor (FabricID,Color) VALUES (@FabricID,@Color);"; int count = DapperHelper.Execute(AppSettingConfig.ConnectionString(), CommandType.Text, sqlCmd, fabricColors); return(count); }
/// <summary> /// 依據狀態取得加工訂單 /// </summary> /// <param name="Status"></param> /// <returns></returns> public IEnumerable <ProcessOrder> GetProcessOrderByStatus(List <ProcessOrderColorStatus> status) { var sqlCmd = @"select distinct po.OrderNo,po.OrderString,po.Fabric,po.Specification,po.ProcessItem,po.Precautions,po.Memo,po.HandFeel,po.CreateDate from ProcessOrder po left join ProcessOrderColorDetail pocd on po.OrderNo = pocd.OrderNo where Status in @Status"; var parameter = (new { Status = status }); var result = DapperHelper.QueryCollection <ProcessOrder, object>(AppSettingConfig.ConnectionString(), CommandType.Text, sqlCmd, parameter); return(result); }
/// <summary> /// 新增加工程序顏色對照 /// </summary> /// <param name="processSequenceColorMapping"></param> /// <returns></returns> public int InsertProcessSequenceColorMapping(IEnumerable <ProcessSequenceColorMapping> processSequenceColorMapping) { string sqlCmd = @" INSERT INTO [dbo].[ProcessSequenceColorMapping] ([ColorNo],[SequenceNo]) VALUES (@ColorNo,@SequenceNo)"; int count = DapperHelper.Execute(AppSettingConfig.ConnectionString(), CommandType.Text, sqlCmd, processSequenceColorMapping); return(count); }
/// <summary> /// 更新布種比例成分 /// </summary> /// <param name="fabricIngredientProportions"></param> /// <returns></returns> public int UpdateFabricProportion(List <FabricIngredientProportion> fabricIngredientProportions) { var sqlCmd = @"UPDATE [dbo].[FabricProportion] SET [YarnPriceNo] = @YarnPriceNo ,[Proportion] = @Proportion ,[UpdateDate] = GETDATE() WHERE ProportionNo=@ProportionNo"; var result = DapperHelper.Execute(AppSettingConfig.ConnectionString(), CommandType.Text, sqlCmd, fabricIngredientProportions); return(result); }
/// <summary> /// 以布種編號取得布種顏色 /// </summary> /// <param name="fabricIDList"></param> /// <returns></returns> public IEnumerable <FabricColor> GetFabricColorListByFabricID(IEnumerable <int> fabricIDList) { string sqlCmd = @"SELECT * FROM FabricColor WHERE FabricID IN @FabricID"; var parameter = new { FabricID = fabricIDList }; var result = DapperHelper.QueryCollection <FabricColor, object>(AppSettingConfig.ConnectionString(), CommandType.Text, sqlCmd, parameter); return(result); }
/// <summary> /// 取得紗規格清單 /// </summary> /// <returns></returns> public IEnumerable <YarnSpecification> GetYarnSpecificationList() { string sqlCmd = @"SELECT [YarnSpecificationNo] ,[Ingredient] ,[YarnCount] ,[Color] ,[CreateDate] FROM [YarnSpecification]"; var result = DapperHelper.QueryCollection <YarnSpecification>(AppSettingConfig.ConnectionString(), CommandType.Text, sqlCmd); return(result); }
/// <summary> /// 更新布種 /// </summary> /// <param name="fabric"></param> /// <returns></returns> public int EditFabric(Fabric fabric) { var sqlCmd = @"UPDATE [dbo].[Fabric] SET [FabricName] = @FabricName ,[AverageUnitPrice] = @AverageUnitPrice ,[AverageCost] = @AverageCost ,[UpdateDate] = @UpdateDate WHERE FabricID=@FabricID"; var result = DapperHelper.Execute(AppSettingConfig.ConnectionString(), CommandType.Text, sqlCmd, fabric); return(result); }
/// <summary> /// 取得加工訂單狀態 /// </summary> /// <returns></returns> public IEnumerable <ProcessOrderStatus> GetProcessOrderStatus() { string sql = @" SELECT PO.OrderNo,PO.OrderString,PO.Fabric,POCD.Color,POCD.Quantity,POCD.Status,POCD.UpdateDate,F.Name AS FactoryName,POF.OrderDetailNo FROM ProcessOrderColorDetail POCD INNER JOIN ProcessOrder PO ON PO.OrderNo = POCD.OrderNo INNER JOIN ProcessOrderFlowDate POFD ON POFD.OrderColorDetailNo = POCD.OrderColorDetailNo INNER JOIN ProcessOrderFlow POF ON POF.OrderDetailNo = POFD.OrderFlowNo INNER JOIN Factory F ON F.FactoryID = POF.FactoryID WHERE POCD.Status = 5 OR POCD.Status = 6 OR Status = 7 OR Status = 8 OR Status = 9"; return(DapperHelper.QueryCollection <ProcessOrderStatus>(AppSettingConfig.ConnectionString(), CommandType.Text, sql)); }
/// <summary> /// 以布種顏色編號取得顏色比例 /// </summary> /// <param name="fabricColorNoList"></param> /// <returns></returns> public IEnumerable <FabricProportion> GetFabricProportionByColorNo(List <int> fabricColorNoList) { string sqlCmd = @"SELECT * FROM FabricProportion WHERE ColorNo IN @ColorNo"; var parameter = new { ColorNo = fabricColorNoList }; var result = DapperHelper.QueryCollection <FabricProportion, object>(AppSettingConfig.ConnectionString(), CommandType.Text, sqlCmd, parameter); return(result); }
/// <summary> /// 修改工廠明細 /// </summary> /// <returns></returns> public int UpdateFactory(Factory factory) { var sqlCmd = @"UPDATE [dbo].[Factory] SET [PhoneNumber] = @PhoneNumber ,[CellPhone] = @CellPhone ,[Fax] = @Fax ,[Address] = @Address ,[Sort] = @Sort WHERE FactoryID=@FactoryID"; var result = DapperHelper.Execute(AppSettingConfig.ConnectionString(), CommandType.Text, sqlCmd, factory); return(result); }
public IEnumerable <ProcessOrderColorDetail> GetProcessOrderColorDetailByOrderNo(int orderNo) { var sqlCmd = "select * from ProcessOrderColorDetail WHERE OrderNo = @OrderNo"; SqlParameter[] parameters = new SqlParameter[] { new SqlParameter("@OrderNo", SqlDbType.Int) { Value = orderNo } }; return(DapperHelper.QueryCollection <ProcessOrderColorDetail>(AppSettingConfig.ConnectionString(), CommandType.Text, sqlCmd, parameters)); }
/// </summary> /// 取得新增或修改的加工訂單明細 /// </summary> /// <param name="dateTime"></param> /// <returns></returns> public IEnumerable <ProcessOrder> GetNewOrEditProcessOrder(DateTime dateTime, IEnumerable <int> orderNo) { var sqlCmd = @"SELECT DISTINCT PO.* FROM ProcessOrder PO LEFT JOIN ProcessOrderColorDetail POCD ON POCD.OrderNo = PO.OrderNo LEFT JOIN ProcessOrderFlowDate POFD ON POFD.OrderColorDetailNo = POCD.OrderColorDetailNo LEFT JOIN FactoryShipping FS ON FS.OrderColorDetailNo = POCD.OrderColorDetailNo WHERE PO.CreateDate > @DATE OR POCD.UpdateDate > @DATE OR POFD.UpdateDate > @DATE OR FS.CreateDate > @DATE OR PO.OrderNo IN @OrderNo"; var parameter = (new { DATE = dateTime, OrderNo = orderNo }); var result = DapperHelper.QueryCollection <ProcessOrder, object>(AppSettingConfig.ConnectionString(), CommandType.Text, sqlCmd, parameter); return(result); }