using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;


namespace HcgClientExport
{
  public  class DataControl
 {
        public string GetLogin(string userid, string password, Form1 f)
        {
            string sqlstr = @"select ROLEGUID FROM sys_user where LOGINUSER=@id and PWD=@pwd";

            MySql.Data.MySqlClient.MySqlParameter[] parameters = new MySql.Data.MySqlClient.MySqlParameter[2];
            parameters[0] = new MySql.Data.MySqlClient.MySqlParameter();
            parameters[0].ParameterName = "id";
            parameters[0].Value = userid;
            parameters[1] = new MySql.Data.MySqlClient.MySqlParameter();
            parameters[1].ParameterName = "pwd";
            parameters[1].Value = password;
            // DataSet ds = MySQLHelper.GetDataSet(sqlstr, CommandType.Text, parameters);
            try
            {
                //read = MySQLHelper.ExecuteReader(sqlstr, CommandType.Text, parameters);
                DataSet ds = MySQLHelper.GetDataSet(sqlstr, CommandType.Text, parameters);
                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    if (ds.Tables[0].Rows[0][0] != null && !ds.Tables[0].Rows[0][0].Equals(""))
                    {
                        sqlstr = @"select ROLEMENU FROM sys_role where guid=@id";

                        MySql.Data.MySqlClient.MySqlParameter[] parameters2 = new MySql.Data.MySqlClient.MySqlParameter[1];
                        parameters2[0] = new MySql.Data.MySqlClient.MySqlParameter();
                        parameters2[0].ParameterName = "id";
                        parameters2[0].Value = ds.Tables[0].Rows[0][0].ToString();
                        DataSet ds2 = MySQLHelper.GetDataSet(sqlstr, CommandType.Text, parameters2);
                        if (ds2.Tables[0].Rows[0][0] != null && !ds2.Tables[0].Rows[0][0].Equals(""))
                        {
                            string[] qxs = ds2.Tables[0].Rows[0][0].ToString().Split('-');
                            StringBuilder sb = new StringBuilder();
                            foreach (string each in qxs)
                            {
                                sb.Append("'" + each + "',");
                            }
                            sb.Length--;
                            sqlstr = "select MENUDIR FROM sys_menu where guid in (" + sb.ToString() + ")";
                            DataSet ds3 = MySQLHelper.GetDataSet(sqlstr);
                            for (int i = 0; i < ds3.Tables[0].Rows.Count; i++)
                            {
                                f.qx.Add(ds3.Tables[0].Rows[i][0].ToString());
                            }
                            if (ds3.Tables[0].Rows.Count > 0)
                            {
                                return "1";
                            }
                            return "您无权使用程序";

                        }
                        else
                        {
                            return "您无权使用程序";
                        }
                    }
                    else
                    {
                        return "您无权使用程序";
                    }
                }
                else
                {
                    return "账户密码错误";
                }
            }
            catch (Exception ex)
            {
                return "服务器连接失败";
            }
        }
        public DataTable GetKucInfo(string orgguid)
        {

            string sqlstr = @"SELECT
	                    (@rowNum :=@rowNum + 1) rownum,
	                    TA.GoodsCode 商品编码,
	                    TA.GoodsName 商品名称,
	                    TA.GoodsSpec 规格,
	                    TA.Manufacturer 生产厂家,
	                    TA.Lot 批号,
	                    TA.ExpireDate 有效期,
	                    TA.SUPPLIERNAME 供应商,
	                    TA.QTY 数量,
                        TA.CUBENAME 柜子
                    FROM
	                    (
		                    SELECT
			                    a.Goodsguid GoodsCode,
			                    a.GoodsName,
			                    concat(b.GoodsSpec,b.model) GoodsSpec,
			                    b.Producer Manufacturer,
			                    a.Lot,
			                    a.ExpireDate,
			                    a.SupplierName,
                                a.cubename,
			                    SUM(FLOOR(NUMBER)) QTY
		                    FROM
			                    mfcubestockdetail a,
			                    his_goods b
		                    WHERE
			                    a.billstate = 'Y'
                                and a.depotguid like @orgguid
		                    AND a.GoodsGUID = b.GUID
		                    GROUP BY
			                    a.Goodsguid,
			                    a.GoodsName,
			                    b.GoodsSpec,
			                    b.Producer,
			                    a.Lot,
			                    a.ExpireDate,
			                    a.SUPPLIERNAME,
                                a.cubename
                                
	                    ) TA,
	                    (SELECT(@rowNum := 0)) TC
                            Order by ta.goodscode,
	                             ta.GoodsName,
                                 ta.LOT,
                                 ta.ExpireDate,
                                 ta.cubename
                       ";

            MySql.Data.MySqlClient.MySqlParameter[] parameters = new MySql.Data.MySqlClient.MySqlParameter[1];
            parameters[0] = new MySql.Data.MySqlClient.MySqlParameter();
            parameters[0].ParameterName = "orgguid";
            if(!orgguid.Equals("-1"))
                parameters[0].Value = orgguid;
            else
                parameters[0].Value = "%%";
            DataSet ds = MySQLHelper.GetDataSet(sqlstr, CommandType.Text, parameters);
            return ds.Tables[0];
        }
        public List<int> kcsx(string orgid,string goodsid)
        {
            List<int> ls = new List<int>();
            string sqlstr = "select StockUpperLimit sx,StockLowerLimit xx FROM his_goods_bound where HisGoodsGUID=@gd and orgGUID=@orgguid";
            MySql.Data.MySqlClient.MySqlParameter[] parameters = new MySql.Data.MySqlClient.MySqlParameter[2];
            parameters[0] = new MySql.Data.MySqlClient.MySqlParameter();
            parameters[0].ParameterName = "gd";
            parameters[0].Value = goodsid;
            parameters[1] = new MySql.Data.MySqlClient.MySqlParameter();
            parameters[1].ParameterName = "orgguid";
            parameters[1].Value = orgid;
            DataSet ds = MySQLHelper.GetDataSet(sqlstr, CommandType.Text, parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                ls.Add(Int32.Parse( ds.Tables[0].Rows[0][0].ToString()));
                ls.Add(Int32.Parse(ds.Tables[0].Rows[0][1].ToString()));
                
            }
            else
            {
                ls.Add(0);
                ls.Add(0);
            }
            return ls;
        }
        public List<his_goods_buound> spxx(string orgid)
        {
            List<his_goods_buound> ls = new List<his_goods_buound>();
            string sqlstr = "select hg.GoodsName,HisGoodsGUID,hg.GoodsCode,hgd.StockUpperLimit,hgd.StockLowerLimit,hgd.orgGUID FROM his_goods hg,his_goods_bound hgd where hg.guid=hgd.HisGoodsGUID and orgGUID=@org";
            MySql.Data.MySqlClient.MySqlParameter[] parameters = new MySql.Data.MySqlClient.MySqlParameter[1];
            parameters[0] = new MySql.Data.MySqlClient.MySqlParameter();
            parameters[0].ParameterName = "org";
            parameters[0].Value = orgid;
            DataSet ds = MySQLHelper.GetDataSet(sqlstr, CommandType.Text, parameters);
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++) {
                his_goods_buound h = new his_goods_buound();
                h.goodsname = ds.Tables[0].Rows[0].ToString();
                h.HisGoodsGUID = ds.Tables[0].Rows[1].ToString();
                h.GoodsCode = ds.Tables[0].Rows[2].ToString();
                h.StockUpperLimit = ds.Tables[3].Rows[0].ToString();
                h.StockLowerLimit = ds.Tables[4].Rows[0].ToString();
                h.orgGUID = ds.Tables[0].Rows[5].ToString();
                ls.Add(h);
            }
            return ls;
        }
        public DataTable GetGoodsKucInfo(string orgguid)
        {
            string sqlstr = @"SELECT
	                    (@rowNum :=@rowNum + 1) rownum6,
	                    TA.GoodsCode 商品编码6,
	                    TA.GoodsName 商品名称6,
	                    TA.GoodsSpec 规格6,
	                    TA.Manufacturer 生产厂家6,
	                    TA.SUPPLIERNAME 供应商6,
	                    TA.QTY 数量6,
                        TA.CUBENAME 柜子6
                    FROM
	                    (
		                    SELECT
			                    a.Goodsguid GoodsCode,
			                    a.GoodsName,
			                    concat(b.GoodsSpec,b.model) GoodsSpec,
			                    b.Producer Manufacturer,
			                    a.SupplierName,
                                a.cubename,
			                    SUM(FLOOR(NUMBER)) QTY
		                    FROM
			                    mfcubestockdetail a,
			                    his_goods b
		                    WHERE
			                    a.billstate = 'Y'
                                  and a.depotguid like @orgguid
		                    AND a.GoodsGUID = b.GUID
		                    GROUP BY
			                    a.Goodsguid,
			                    a.GoodsName,
			                    b.GoodsSpec,
			                    b.Producer,
			                    a.SUPPLIERNAME,
                                a.cubename
                                
	                    ) TA,
	                    (SELECT(@rowNum := 0)) TC
                            Order by ta.goodscode,
	                             ta.GoodsName,
                                 ta.cubename
                       ";

            MySql.Data.MySqlClient.MySqlParameter[] parameters = new MySql.Data.MySqlClient.MySqlParameter[1];
            parameters[0] = new MySql.Data.MySqlClient.MySqlParameter();
            parameters[0].ParameterName = "orgguid";
            // parameters[0].Value = orgguid;
            if (!orgguid.Equals("-1"))
                parameters[0].Value = orgguid;
            else
                parameters[0].Value = "%%";
            DataSet ds = MySQLHelper.GetDataSet(sqlstr, CommandType.Text, parameters);
            return ds.Tables[0];
        }
        public DataTable Getbhxxinfo(string orgguid)
        {
            string sqlstr = @"SELECT
                     '0' rownumbh,
	                hg.GoodsName 商品名称bh,
	                hg.GoodsCode 商品编码bh,
	                concat_ws( '', hg.GoodsSpec, hg.model ) 规格型号bh,
	                hg.Producer 生产厂家bh ,
	                hgd.StockUpperLimit 库存上限bh,
                    '0' 数量bh ,
	                hgd.StockLowerLimit 库存下限bh,
                    '0' 建议补货bh 
                FROM
	                his_goods hg,
	                his_goods_bound hgd 
                WHERE
	                hg.guid = hgd.HisGoodsGUID and orgGUID=@org";

            MySql.Data.MySqlClient.MySqlParameter[] parameters = new MySql.Data.MySqlClient.MySqlParameter[1];
            parameters[0] = new MySql.Data.MySqlClient.MySqlParameter();
            parameters[0].ParameterName = "org";
            parameters[0].Value = orgguid;
            DataSet ds = MySQLHelper.GetDataSet(sqlstr, CommandType.Text, parameters);
            return ds.Tables[0];
        }
        public int Getgoodskccount(string org, string goodspec)
        {
            string sqlstr = @"select count(*) FROM mfcubestockdetail  where depotGuid=@org  and GoodsGUID=@goodspec";

            MySql.Data.MySqlClient.MySqlParameter[] parameters = new MySql.Data.MySqlClient.MySqlParameter[2];
            parameters[0] = new MySql.Data.MySqlClient.MySqlParameter();
            parameters[0].ParameterName = "org";
            parameters[0].Value = org;
            parameters[1] = new MySql.Data.MySqlClient.MySqlParameter();
            parameters[1].ParameterName = "goodspec";
            parameters[1].Value = goodspec;
            DataSet ds = MySQLHelper.GetDataSet(sqlstr, CommandType.Text, parameters);
            return int.Parse( ds.Tables[0].Rows[0][0].ToString());
        }
        public DataTable GetKucMxInfo(string orgguid)
        {
            string sqlstr = @"SELECT
	                    (@rowNum :=@rowNum + 1) rownum4,
	                    TA.GoodsCode 商品编码4,
	                    TA.GoodsName 商品名称4,
	                    TA.GoodsSpec 规格4,
	                    TA.Manufacturer 生产厂家4,
	                    TA.Lot 批号4,
	                    TA.ExpireDate 有效期4,
	                    TA.SUPPLIERNAME 供应商4,
	                    TA.QTY 数量4,
                        ta.labguid 高值码4,
                        ta.cubename 柜子4
                    FROM
	                    (
		                    SELECT
			                    a.Goodsguid GoodsCode,
			                    a.GoodsName,
			                    concat(b.GoodsSpec,b.model) GoodsSpec,
			                    b.Producer Manufacturer,
			                    a.Lot,
			                    a.ExpireDate,
			                    a.SupplierName,
			                    FLOOR(a.NUMBER) QTY,
                                a.labguid,
                                a.cubename
		                    FROM
			                    mfcubestockdetail a,
			                    his_goods b
		                    WHERE
			                    a.billstate = 'Y'
                                  and a.depotguid like @orgguid
		                    AND a.GoodsGUID = b.GUID
		                   
	                    ) TA,
	                    (SELECT(@rowNum := 0)) TC
                         Order by TA.GoodsCode ,
	                             TA.GoodsName ,
                                 TA.LOT,
                                 TA.ExpireDate,
                                 TA.CUBENAME,
                                 TA.LABGUID
                          
                                             ";

            MySql.Data.MySqlClient.MySqlParameter[] parameters = new MySql.Data.MySqlClient.MySqlParameter[1];
            parameters[0] = new MySql.Data.MySqlClient.MySqlParameter();
            parameters[0].ParameterName = "orgguid";
            // parameters[0].Value = orgguid;
            if (!orgguid.Equals("-1"))
                parameters[0].Value = orgguid;
            else
                parameters[0].Value = "%%";
            DataSet ds = MySQLHelper.GetDataSet(sqlstr, CommandType.Text, parameters);
            return ds.Tables[0];
        }
        public bool hfkc(string barcode)
        {
            
            string sql = " INSERT INTO mfcubestockdetail SELECT * FROM mfcubestockdetail_old where LabGuid= '"+ barcode + "'";
            if(MySQLHelper.ExecuteNonQuery(sql)<=0)
            {
                return false;
            }
            sql= "delete from trchargingdetail where LabGuid='"+barcode+"';";
            if (MySQLHelper.ExecuteNonQuery(sql) <= 0)
            {
                return false;
            }
            return true;
        }
        public string ChangePwd(string oldpwd,string newpwd,string uid)
        {
            if(!oldpwd.Equals("-1"))
            {
                string sql = "select * from sys_user where LOGINUSER=@uid and PWD=@pwd";
                MySql.Data.MySqlClient.MySqlParameter[] parameters = new MySql.Data.MySqlClient.MySqlParameter[2];
                parameters[0] = new MySql.Data.MySqlClient.MySqlParameter();
                parameters[0].ParameterName = "uid";
                parameters[0].Value = uid;
                parameters[1] = new MySql.Data.MySqlClient.MySqlParameter();
                parameters[1].ParameterName = "pwd";
                parameters[1].Value = oldpwd;
                DataSet ds = MySQLHelper.GetDataSet(sql, CommandType.Text, parameters);
                if(ds.Tables[0].Rows.Count<=0)
                {
                    return "旧密码错误！";

                }

                sql = "update sys_user set pwd=@pwd where LOGINUSER=@uid";
                MySql.Data.MySqlClient.MySqlParameter[] parameters2 = new MySql.Data.MySqlClient.MySqlParameter[2];
                parameters2[0] = new MySql.Data.MySqlClient.MySqlParameter();
                parameters2[0].ParameterName = "uid";
                parameters2[0].Value = uid;
                parameters2[1] = new MySql.Data.MySqlClient.MySqlParameter();
                parameters2[1].ParameterName = "pwd";
                parameters2[1].Value = newpwd;
                //ds = MySQLHelper.GetDataSet(sql, CommandType.Text, parameters);
                if (MySQLHelper.ExecuteNonQuery(sql,CommandType.Text,parameters2)<=0)
                {
                    return "修改失败！";
                }

            }
            else
            {
                string sql = "update sys_user set pwd='96e79218965eb72c92a549dd5a330112' where LOGINUSER=@uid";
                MySql.Data.MySqlClient.MySqlParameter[] parameters2 = new MySql.Data.MySqlClient.MySqlParameter[1];
                parameters2[0] = new MySql.Data.MySqlClient.MySqlParameter();
                parameters2[0].ParameterName = "uid";
                parameters2[0].Value = uid;
                //ds = MySQLHelper.GetDataSet(sql, CommandType.Text, parameters);
                if (MySQLHelper.ExecuteNonQuery(sql, CommandType.Text, parameters2) <= 0)
                {
                    return "初始化密码失败！";
                }
            }
            return "修改成功";
        }
        public DataTable Getxh(string orgguid)
        {
            string sqlstr = @"SELECT
	                    (@rowNum :=@rowNum + 1) rownum12,
	                    TA.GoodsCode 商品编码12,
	                    TA.GoodsName 商品名称12,
	                    TA.GoodsSpec 规格12,
	                    TA.Manufacturer 生产厂家12,
	                    TA.Lot 批号12,
	                    TA.ExpireDate 有效期12,
	                    TA.SUPPLIERNAME 供应商12,
                        ta.labguid 高值码12,
                        ta.cubename 柜子12,
                        ta.InputDate 消耗时间12,
                        '恢复库存' 操作12
                    FROM
	                    (
		                    SELECT
			                    a.Goodsguid GoodsCode,
			                    a.GoodsName,
			                    concat(b.GoodsSpec,b.model) GoodsSpec,
			                    b.Producer Manufacturer,
			                    a.Lot,
			                    a.ExpireDate,
			                    a.SupplierName,
                                a.labguid,
                                a.cubename,
                                tr.InputDate
		                    FROM
			                    mfcubestockdetail_old a,
			                    his_goods b,
                                trchargingdetail tr
		                    WHERE
			                     a.depotguid like @orgguid 
                                  and a.LabGuid=tr.LabGuid
		                    AND a.GoodsGUID = b.GUID
		                   
	                    ) TA,
	                    (SELECT(@rowNum := 0)) TC
                         Order by TA.InputDate,
                                 TA.GoodsCode ,
	                             TA.GoodsName ,
                                 TA.LOT,
                                 TA.ExpireDate,
                                 TA.CUBENAME,
                                 TA.LABGUID
                          
                                             ";

            MySql.Data.MySqlClient.MySqlParameter[] parameters = new MySql.Data.MySqlClient.MySqlParameter[1];
            parameters[0] = new MySql.Data.MySqlClient.MySqlParameter();
            parameters[0].ParameterName = "orgguid";
            // parameters[0].Value = orgguid;
            if (!orgguid.Equals("-1"))
                parameters[0].Value = orgguid;
            else
                parameters[0].Value = "%%";
            DataSet ds = MySQLHelper.GetDataSet(sqlstr, CommandType.Text, parameters);
            return ds.Tables[0];
        }
        public DataTable GetGoodsUseHzCurrentDayInfo(string orgguid, string begintime, string endtime)
        {
            string sqlstr = @"SELECT
	                    (@rowNum :=@rowNum + 1) rownum1,
	                    TA.GoodsCode 商品编码1,
	                    TA.GoodsName 商品名称1,
	                    TA.GoodsSpec 规格1,
	                    TA.Manufacturer 生产厂家1,
	                    TA.Lot 批号1,
	                    TA.ExpireDate 有效期1,
	                    TA.SUPPLIERNAME 供应商1,
	                    TA.QTY 数量1,
                        TA.OPERATERNAME 操作人1,
                        TA.INPUTDATE    操作时间1
                    FROM
	                    (
		                    SELECT
	                        a.Goodsguid GoodsCode,
	                        a.GoodsName,
	                        concat(b.GoodsSpec,b.model) GoodsSpec,
	                        b.Producer Manufacturer,
	                        a.Lot,
	                        a.ExpireDate,
	                        a.SupplierName,
	                        a.qty qty,
                            a.OPERATERNAME,
                            a.inputdate
                        FROM
	                        mfcubestockstatement a,
	                        his_goods b
                        WHERE a.type='2'
                         and backsts='N'
                           and a.depotguid like @orgguid
	                    and a.GoodsGUID = b.GUID
                        and a.inputdate between @begintime and @endtime ) TA,
	                    (SELECT(@rowNum := 0)) TC
                           Order by ta.goodscode,
	                             ta.GoodsName,
                                 ta.LOT,
                                 ta.ExpireDate

                                             ";

            MySql.Data.MySqlClient.MySqlParameter[] parameters = new MySql.Data.MySqlClient.MySqlParameter[3];
            parameters[0] = new MySql.Data.MySqlClient.MySqlParameter();
            parameters[0].ParameterName = "orgguid";
            // parameters[0].Value = orgguid;
            if (!orgguid.Equals("-1"))
                parameters[0].Value = orgguid;
            else
                parameters[0].Value = "%%";
            parameters[1] = new MySql.Data.MySqlClient.MySqlParameter();
            parameters[1].ParameterName = "begintime";
            parameters[1].DbType = DbType.DateTime;
            parameters[1].Value = begintime;

            parameters[2] = new MySql.Data.MySqlClient.MySqlParameter();
            parameters[2].ParameterName = "endtime";
            parameters[2].DbType = DbType.DateTime;
            parameters[2].Value = endtime;
            DataSet ds = MySQLHelper.GetDataSet(sqlstr, CommandType.Text, parameters);
            return ds.Tables[0];
        }

        public DataTable GetGoodsUseInfo(string orgguid, string begintime, string endtime)
        {
            string sqlstr = @"SELECT
	                    (@rowNum :=@rowNum + 1) rownum2,
	                    TA.GoodsCode 商品编码2,
	                    TA.GoodsName 商品名称2,
	                    TA.GoodsSpec 规格2,
	                    TA.Manufacturer 生产厂家2,
	                    TA.Lot 批号2,
	                    TA.ExpireDate 有效期2,
	                    TA.SUPPLIERNAME 供应商2,
	                    TA.QTY 数量2,
                        TA.OPERATERNAME 操作人2,
                        TA.INPUTDATE    操作时间2,
                        TA.barcode 高值码2
                    FROM
	                    (
		                    SELECT
	                        a.Goodsguid GoodsCode,
	                        a.GoodsName,
	                        concat(b.GoodsSpec,b.model) GoodsSpec,
	                        b.Producer Manufacturer,
	                        a.Lot,
	                        a.ExpireDate,
	                        a.SupplierName,
	                        1 qty, -- floor(a.qty) qty,
                            a.OPERATERNAME,
                            a.inputdate,
                            a.barcode
                        FROM
	                        mfcubestockstatement a,
	                        his_goods b
                        WHERE a.type='2'
                         and backsts='N'
                          and a.depotguid like @orgguid
	                    and a.GoodsGUID = b.GUID
                        and a.inputdate between @begintime and @endtime ) TA,
	                    (SELECT(@rowNum := 0)) TC
                         Order by ta.goodscode,
	                             ta.GoodsName,
                                 ta.LOT,
                                 ta.ExpireDate,

                                 ta.barcode
                                             ";

            MySql.Data.MySqlClient.MySqlParameter[] parameters = new MySql.Data.MySqlClient.MySqlParameter[3];
            parameters[0] = new MySql.Data.MySqlClient.MySqlParameter();
            parameters[0].ParameterName = "orgguid";
            // parameters[0].Value = orgguid;
            if (!orgguid.Equals("-1"))
                parameters[0].Value = orgguid;
            else
                parameters[0].Value = "%%";

            parameters[1] = new MySql.Data.MySqlClient.MySqlParameter();
            parameters[1].ParameterName = "begintime";
            parameters[1].DbType = DbType.DateTime;
            parameters[1].Value = begintime;

            parameters[2] = new MySql.Data.MySqlClient.MySqlParameter();
            parameters[2].ParameterName = "endtime";
            parameters[2].DbType = DbType.DateTime;
            parameters[2].Value = endtime;
            DataSet ds = MySQLHelper.GetDataSet(sqlstr, CommandType.Text, parameters);
            return ds.Tables[0];
        }

        public DataTable GetGoodsUseHzInfo(string orgguid, string begintime, string endtime)
        {
            string sqlstr = @"SELECT
	                    (@rowNum :=@rowNum + 1) rownum3,
	                    TA.GoodsCode 商品编码3,
	                    TA.GoodsName 商品名称3,
	                    TA.GoodsSpec 规格3,
	                    TA.Manufacturer 生产厂家3,
	                    TA.Lot 批号3,
	                    TA.ExpireDate 有效期3,
	                    TA.SUPPLIERNAME 供应商3,
	                    TA.QTY 数量3
                    FROM
	                    (
		                    SELECT
	                        a.Goodsguid GoodsCode,
	                        a.GoodsName,
	                        concat(b.GoodsSpec,b.model) GoodsSpec,
	                        b.Producer Manufacturer,
	                        a.Lot,
	                        a.ExpireDate,
	                        a.SupplierName,
	                        count(a.qty) qty
                        FROM
	                        mfcubestockstatement a,
	                        his_goods b
                        WHERE a.type='2'
                         and backsts='N'
                           and a.depotguid like @orgguid
	                    and a.GoodsGUID = b.GUID
                        and a.inputdate between @begintime and @endtime 
                            group by 
                            a.Goodsguid ,
	                        a.GoodsName,
	                        b.GoodsSpec,
	                        b.Producer ,
	                        a.Lot,
	                        a.ExpireDate,
	                        a.SupplierName,
                            a.qty
                    ) TA,
	                    (SELECT(@rowNum := 0)) TC
                      Order by ta.goodscode,
	                           ta.GoodsName,
                               ta.LOT,
                               ta.ExpireDate                     ";

            MySql.Data.MySqlClient.MySqlParameter[] parameters = new MySql.Data.MySqlClient.MySqlParameter[3];
            parameters[0] = new MySql.Data.MySqlClient.MySqlParameter();
            parameters[0].ParameterName = "orgguid";
            //parameters[0].Value = orgguid;
            if (!orgguid.Equals("-1"))
                parameters[0].Value = orgguid;
            else
                parameters[0].Value = "%%";
            parameters[1] = new MySql.Data.MySqlClient.MySqlParameter();
            parameters[1].ParameterName = "begintime";
            parameters[1].DbType = DbType.DateTime;
            parameters[1].Value = begintime;

            parameters[2] = new MySql.Data.MySqlClient.MySqlParameter();
            parameters[2].ParameterName = "endtime";
            parameters[2].DbType = DbType.DateTime;
            parameters[2].Value = endtime;
            DataSet ds = MySQLHelper.GetDataSet(sqlstr, CommandType.Text, parameters);
            return ds.Tables[0];
        }

        public DataTable GetExpiteKucMxInfo(string orgguid,string expiredate)
        {
            string sqlstr = @"SELECT
	                    (@rowNum :=@rowNum + 1) rownum5,
	                    TA.GoodsCode 商品编码5,
	                    TA.GoodsName 商品名称5,
	                    TA.GoodsSpec 规格5,
	                    TA.Manufacturer 生产厂家5,
	                    TA.Lot 批号5,
	                    TA.ExpireDate 有效期5,
	                    TA.SUPPLIERNAME 供应商5,
	                    TA.expire 效期5,
                        ta.labguid 高值码5,
                        ta.cubename 柜子5
                    FROM
	                    (
		                    SELECT
			                    a.Goodsguid GoodsCode,
			                    a.GoodsName,
			                    concat(b.GoodsSpec,b.model) GoodsSpec,
			                    b.Producer Manufacturer,
			                    a.Lot,
			                    a.ExpireDate,
			                    a.SupplierName,
			                    datediff( ExpireDate, curdate()) expire,
                                a.labguid,
                                a.cubename
		                    FROM
			                    mfcubestockdetail a,
			                    his_goods b
		                    WHERE
			                    a.billstate = 'Y'
                                and a.depotguid like @orgguid
		                    AND a.GoodsGUID = b.GUID
		                    and datediff( ExpireDate, curdate())<=@expire
	                    ) TA,
	                    (SELECT(@rowNum := 0)) TC
                         Order by TA.GoodsCode ,
	                             TA.GoodsName ,
                                 TA.LOT,
                                 TA.ExpireDate,
                                 TA.LABGUID
                          
                                             ";

            MySql.Data.MySqlClient.MySqlParameter[] parameters = new MySql.Data.MySqlClient.MySqlParameter[2];
            parameters[0] = new MySql.Data.MySqlClient.MySqlParameter();
            parameters[0].ParameterName = "orgguid";
            // parameters[0].Value = orgguid;
            if (!orgguid.Equals("-1"))
                parameters[0].Value = orgguid;
            else
                parameters[0].Value = "%%";
            parameters[1] = new MySql.Data.MySqlClient.MySqlParameter();
            parameters[1].ParameterName = "expire";
            parameters[1].Value = expiredate;
            DataSet ds = MySQLHelper.GetDataSet(sqlstr, CommandType.Text, parameters);
            return ds.Tables[0];
        }

        public DataTable GetGoodsRkInfo(string orgguid, string begintime, string endtime)
        {
            string sqlstr = @"SELECT
	                    (@rowNum :=@rowNum + 1) rownum7,
	                    TA.GoodsCode 商品编码7,
	                    TA.GoodsName 商品名称7,
	                    TA.GoodsSpec 规格7,
	                    TA.Manufacturer 生产厂家7,
	                    TA.Lot 批号7,
	                    TA.ExpireDate 有效期7,
	                    TA.SUPPLIERNAME 供应商7,
	                    TA.QTY 数量7,
                        TA.OPERATERNAME 操作人7,
                        TA.INPUTDATE    操作时间7,
                        TA.barcode 高值码7,
                        ta.cubename 柜子7
                    FROM
	                    (
		                    SELECT
	                        a.Goodsguid GoodsCode,
	                        a.GoodsName,
	                        concat(b.GoodsSpec,b.model) GoodsSpec,
	                        b.Producer Manufacturer,
	                        a.Lot,
	                        a.ExpireDate,
	                        a.SupplierName,
	                        1 qty, -- floor(a.qty) qty,
                            a.OPERATERNAME,
                            a.inputdate,
                            a.barcode,
                            a.cubename
                        FROM
	                        mfcubestockstatement a,
	                        his_goods b
                        WHERE a.type='1'
                          and a.depotguid like @orgguid
	                    and a.GoodsGUID = b.GUID
                        and a.inputdate between @begintime and @endtime ) TA,
	                    (SELECT(@rowNum := 0)) TC
                         Order by 
                                 ta.inputdate
                                             ";

            MySql.Data.MySqlClient.MySqlParameter[] parameters = new MySql.Data.MySqlClient.MySqlParameter[3];
            parameters[0] = new MySql.Data.MySqlClient.MySqlParameter();
            parameters[0].ParameterName = "orgguid";
            // parameters[0].Value = orgguid;
            if (!orgguid.Equals("-1"))
                parameters[0].Value = orgguid;
            else
                parameters[0].Value = "%%";
            parameters[1] = new MySql.Data.MySqlClient.MySqlParameter();
            parameters[1].ParameterName = "begintime";
            parameters[1].DbType = DbType.DateTime;
            parameters[1].Value = begintime;

            parameters[2] = new MySql.Data.MySqlClient.MySqlParameter();
            parameters[2].ParameterName = "endtime";
            parameters[2].DbType = DbType.DateTime;
            parameters[2].Value = endtime;
            DataSet ds = MySQLHelper.GetDataSet(sqlstr, CommandType.Text, parameters);
            return ds.Tables[0];
        }

        public DataTable GetGoodsPickInfo(string orgguid, string begintime, string endtime)
        {
            string sqlstr = @"SELECT
	                    (@rowNum :=@rowNum + 1) rownum8,
	                    TA.GoodsCode 商品编码8,
	                    TA.GoodsName 商品名称8,
	                    TA.GoodsSpec 规格8,
	                    TA.Manufacturer 生产厂家8,
	                    TA.Lot 批号8,
	                    TA.ExpireDate 有效期8,
	                    TA.SUPPLIERNAME 供应商8,
	                    TA.QTY 数量8,
                        TA.OPERATERNAME 操作人8,
                        TA.INPUTDATE    操作时间8,
                        TA.barcode 高值码8,
                        ta.cubename 柜子8
                    FROM
	                    (
		                    SELECT
	                        a.Goodsguid GoodsCode,
	                        a.GoodsName,
	                        concat(b.GoodsSpec,b.model) GoodsSpec,
	                        b.Producer Manufacturer,
	                        a.Lot,
	                        a.ExpireDate,
	                        a.SupplierName,
	                        1 qty, -- floor(a.qty) qty,
                            a.OPERATERNAME,
                            a.inputdate,
                            a.barcode,
                            a.cubename
                        FROM
	                        mfcubestockstatement a,
	                        his_goods b
                        WHERE a.type='2'
                           and a.depotguid like @orgguid
	                    and a.GoodsGUID = b.GUID
                        and a.inputdate between @begintime and @endtime ) TA,
	                    (SELECT(@rowNum := 0)) TC
                         Order by ta.inputdate
                                             ";

            MySql.Data.MySqlClient.MySqlParameter[] parameters = new MySql.Data.MySqlClient.MySqlParameter[3];
            parameters[0] = new MySql.Data.MySqlClient.MySqlParameter();
            parameters[0].ParameterName = "orgguid";
            //  parameters[0].Value = orgguid;
            if (!orgguid.Equals("-1"))
                parameters[0].Value = orgguid;
            else
                parameters[0].Value = "%%";
            parameters[1] = new MySql.Data.MySqlClient.MySqlParameter();
            parameters[1].ParameterName = "begintime";
            parameters[1].DbType = DbType.DateTime;
            parameters[1].Value = begintime;

            parameters[2] = new MySql.Data.MySqlClient.MySqlParameter();
            parameters[2].ParameterName = "endtime";
            parameters[2].DbType = DbType.DateTime;
            parameters[2].Value = endtime;
            DataSet ds = MySQLHelper.GetDataSet(sqlstr, CommandType.Text, parameters);
            return ds.Tables[0];
        }

        public DataTable GetGoodsBackInfo(string orgguid, string begintime, string endtime)
        {
            string sqlstr = @"SELECT
	                    (@rowNum :=@rowNum + 1) rownum9,
	                    TA.GoodsCode 商品编码9,
	                    TA.GoodsName 商品名称9,
	                    TA.GoodsSpec 规格9,
	                    TA.Manufacturer 生产厂家9,
	                    TA.Lot 批号9,
	                    TA.ExpireDate 有效期9,
	                    TA.SUPPLIERNAME 供应商9,
	                    TA.QTY 数量9,
                        TA.OPERATERNAME 操作人9,
                        TA.INPUTDATE    操作时间9,
                        TA.barcode 高值码9,
                        ta.cubename 柜子9
                    FROM
	                    (
		                    SELECT
	                        a.Goodsguid GoodsCode,
	                        a.GoodsName,
	                        concat(b.GoodsSpec,b.model) GoodsSpec,
	                        b.Producer Manufacturer,
	                        a.Lot,
	                        a.ExpireDate,
	                        a.SupplierName,
	                        1 qty, -- floor(a.qty) qty,
                            a.OPERATERNAME,
                            a.inputdate,
                            a.barcode,
                            a.cubename
                        FROM
	                        mfcubestockstatement a,
	                        his_goods b
                        WHERE a.type='6'
                          and a.depotguid like @orgguid
	                    and a.GoodsGUID = b.GUID
                        and a.inputdate between @begintime and @endtime ) TA,
	                    (SELECT(@rowNum := 0)) TC
                         Order by ta.inputdate
                                             ";

            MySql.Data.MySqlClient.MySqlParameter[] parameters = new MySql.Data.MySqlClient.MySqlParameter[3];
            parameters[0] = new MySql.Data.MySqlClient.MySqlParameter();
            parameters[0].ParameterName = "orgguid";
            //  parameters[0].Value = orgguid;
            if (!orgguid.Equals("-1"))
                parameters[0].Value = orgguid;
            else
                parameters[0].Value = "%%";
            parameters[1] = new MySql.Data.MySqlClient.MySqlParameter();
            parameters[1].ParameterName = "begintime";
            parameters[1].DbType = DbType.DateTime;
            parameters[1].Value = begintime;

            parameters[2] = new MySql.Data.MySqlClient.MySqlParameter();
            parameters[2].ParameterName = "endtime";
            parameters[2].DbType = DbType.DateTime;
            parameters[2].Value = endtime;
            DataSet ds = MySQLHelper.GetDataSet(sqlstr, CommandType.Text, parameters);
            return ds.Tables[0];
        }

    }
}